Update Vendor Site details through backend

DECLARE

l_msg_data VARCHAR2(20000);

l_return_status VARCHAR2(100);

l_msg_count NUMBER;

l_vendor_site_rec apps.ap_vendor_pub_pkg.r_vendor_site_rec_type;

l_location_id NUMBER;

l_object_version_number NUMBER;

l_message_int NUMBER;

l_vendor_site_id NUMBER;

p_api_version NUMBER;

p_init_msg_list VARCHAR2(200);

p_commit VARCHAR2(200);

p_validation_level NUMBER;

x_return_status VARCHAR2(200);

x_msg_count NUMBER;

x_msg_data VARCHAR2(200);

lr_vendor_rec apps.ap_vendor_pub_pkg.r_vendor_rec_type;

lr_existing_vendor_rec ap_suppliers%ROWTYPE;

l_msg VARCHAR2(200);

p_vendor_id NUMBER;

BEGIN

fnd_global.apps_initialize(50646, 55846, 7000);

FOR j IN (SELECT vendor_id, vendor_site_id,org_id

FROM ap_supplier_sites_all

WHERE 1=1

and org_id = 378

and vendor_site_id = 10860912) LOOP

mo_global.set_policy_context(‘S’,378);

mo_global.init(‘SQLAP’);

fnd_client_info.set_org_context(378);

l_return_status := NULL;

l_msg_count := NULL;

l_msg_data := NULL;

l_vendor_site_rec := NULL;

l_vendor_site_id := j.vendor_site_id;

l_vendor_site_rec.vendor_site_id := l_vendor_site_id;

— l_vendor_site_rec.pay_group_lookup_code := ‘XXPAYGRP’;

l_vendor_site_rec.address_line1 := ‘Dummy Address’;

ap_vendor_pub_pkg.update_vendor_site_public(p_api_version => 1,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data,

p_vendor_site_rec => l_vendor_site_rec,

p_vendor_site_id => l_vendor_site_id);

DBMS_OUTPUT.put_line(‘cv_ven_site.vendor_site_id’ || l_vendor_site_id);

DBMS_OUTPUT.put_line(‘cv_ven_site.status’ || l_return_status);

IF (l_return_status <> ‘S’) THEN

IF l_msg_count >= 1 THEN

FOR v_index IN 1 .. l_msg_count LOOP

fnd_msg_pub.get(p_msg_index => v_index,

p_encoded => ‘F’,

p_data => l_msg_data,

p_msg_index_out => l_message_int);

l_msg_data := ‘UPDATE_VENDOR_SITE ‘ ||

SUBSTR(l_msg_data, 1, 3900);

DBMS_OUTPUT.put_line(‘l_msg_data – ‘ || l_msg_data);

END LOOP;

END IF;

ELSE

DBMS_OUTPUT.put_line(‘The API call ended with SUCCESS status’);

commit;

END IF;

COMMIT;

END LOOP;

END;

What is DDL and What is DML in Oracle

What is DDL?

Data Definition Language helps you to define the database structure or schema. DDL commands help you to create the structure of the database and the other database objects. Its commands are auto-committed so, the changes are saved in the database permanently. The full form of DDL is Data Definition Language.

Commands for DDL

Five types of DDL commands are:

CREATE

CREATE statements is used to define the database structure schema:

Syntax:

CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);

For example:

Create database university;
Create table students;
Create view for_students;

DROP

Drops commands remove tables and databases from RDBMS.

Syntax:

DROP TABLE ;

For example:

Drop object_type object_name;
Drop database university;
Drop table student;

ALTER

Alters command allows you to alter the structure of the database.

Syntax:

To add a new column in the table

ALTER TABLE table_name ADD column_name COLUMN-definition;

To modify an existing column in the table:

ALTER TABLE MODIFY(COLUMN DEFINITION....);

For example:

Alter table guru99 add subject varchar;

TRUNCATE:

This command used to delete all the rows from the table and free the space containing the table.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE table students;

What is DML?

DML commands it to allow you to manage the data stored in the database, although DML commands are not auto-committed. Moreover, they are not permanent. So, It is possible to roll back the operation. The full form of DML is Data Manipulation Language.

Commands for DML

Here are some important DML commands:

INSERT
UPDATE
DELETE

INSERT:

This is a statement that is a SQL query. This command is used to insert data into the row of a table.

Syntax:

INSERT INTO TABLE_NAME  (col1, col2, col3,.... col N)  
VALUES (value1, value2, value3, .... valueN);  
Or 
INSERT INTO TABLE_NAME    
VALUES (value1, value2, value3, .... valueN);

For example:

INSERT INTO students (RollNo, FIrstName, LastName) VALUES ('60', 'Tom', 'Erichsen');

UPDATE:

This command is used to update or modify the value of a column in the table.

Syntax:

UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]

For example:

UPDATE students    
SET FirstName = 'Jhon', LastName=' Wick' 
WHERE StudID = 3;

DELETE:

This command is used to remove one or more rows from a table.

Syntax:

DELETE FROM table_name [WHERE condition];

For example:

DELETE FROM students 
WHERE FirstName = 'Jhon';

Single Row Function and Group Function

Oracle Built in Functions

There are two types of functions in Oracle.
1) Single Row Functions: Single row or Scalar functions return a value for every row that is processed in a query.
2) Group Functions: These functions group the rows of data based on the values returned by the query. This is discussed in SQL GROUP Functions. The group functions are used to calculate aggregate values like total or average, which return just one total or one average value after processing a group of rows.

There are four types of single row functions. They are:
1) Numeric Functions: These are functions that accept numeric input and return numeric values.
2) Character or Text Functions: These are functions that accept character input and can return both character and number values.
3) Date Functions: These are functions that take values that are of datatype DATE as input and return values of datatype DATE, except for the MONTHS_BETWEEN function, which returns a number.
4) Conversion Functions: These are functions that help us to convert a value in one form to another form. For Example: a null value into an actual value, or a value from one datatype to another datatype like NVL, TO_CHAR, TO_NUMBER, TO_DATE etc.

You can combine more than one function together in an expression. This is known as nesting of functions.

What is a DUAL Table in Oracle?
This is a single row and single column dummy table provided by oracle. This is used to perform mathematical calculations without using a table.

Select * from DUAL

Output:

DUMMY
——-
X

Select 777 * 888 from Dual

Output:

777 * 888
———
689976

1) Numeric Functions:

Numeric functions are used to perform operations on numbers. They accept numeric values as input and return numeric values as output. Few of the Numeric functions are:

Function Name	Return Value
ABS (x)	Absolute value of the number ‘x‘
CEIL (x)	Integer value that is Greater than or equal to the number ‘x‘
FLOOR (x)	Integer value that is Less than or equal to the number ‘x‘
TRUNC (x, y)	Truncates value of number ‘x‘ up to ‘y‘ decimal places
ROUND (x, y)	Rounded off value of the number ‘x‘ up to the number ‘y‘ decimal places

The following examples explains the usage of the above numeric functions

Function Name	Examples	Return Value
ABS (x)	ABS (1) ABS (-1)	1 -1
CEIL (x)	CEIL (2.83) CEIL (2.49) CEIL (-1.6)	3 3 -1
FLOOR (x)	FLOOR (2.83) FLOOR (2.49) FLOOR (-1.6)	2 2 -2
TRUNC (x, y)	ROUND (125.456, 1) ROUND (125.456, 0) ROUND (124.456, -1)	125.4 125 120
ROUND (x, y)	TRUNC (140.234, 2) TRUNC (-54, 1) TRUNC (5.7) TRUNC (142, -1)	140.23 54 5 140

These functions can be used on database columns.

For Example: Let’s consider the product table used in sql joins. We can use ROUND to round off the unit_price to the nearest integer, if any product has prices in fraction.

SELECT ROUND (unit_price) FROM product;

2) Character or Text Functions:

Character or text functions are used to manipulate text strings. They accept strings or characters as input and can return both character and number values as output.

Few of the character or text functions are as given below:

Function Name	Return Value
LOWER (string_value)	All the letters in ‘string_value’ is converted to lowercase.
UPPER (string_value)	All the letters in ‘string_value’ is converted to uppercase.
INITCAP (string_value)	All the letters in ‘string_value’ is converted to mixed case.
LTRIM (string_value, trim_text)	All occurrences of ‘trim_text’ is removed from the left of ‘string_value’.
RTRIM (string_value, trim_text)	All occurrences of ‘trim_text’ is removed from the right of ‘string_value’ .
TRIM (trim_text FROM string_value)	All occurrences of ‘trim_text’ from the left and right of ‘string_value’ , ‘trim_text’ can also be only one character long .
SUBSTR (string_value, m, n)	Returns ‘n’ number of characters from ‘string_value’ starting from the ‘m’ position.
LENGTH (string_value)	Number of characters in ‘string_value’ in returned.
LPAD (string_value, n, pad_value)	Returns ‘string_value’ left-padded with ‘pad_value’ . The length of the whole string will be of ‘n’ characters.
RPAD (string_value, n, pad_value)	Returns ‘string_value’ right-padded with ‘pad_value’ . The length of the whole string will be of ‘n’ characters.

For Example, we can use the above UPPER() text function with the column value as follows.

SELECT UPPER (product_name) FROM product;

The following examples explains the usage of the above character or text functions

Function Name	Examples	Return Value
LOWER(string_value)	LOWER(‘Good Morning’)	good morning
UPPER(string_value)	UPPER(‘Good Morning’)	GOOD MORNING
INITCAP(string_value)	INITCAP(‘GOOD MORNING’)	Good Morning
LTRIM(string_value, trim_text)	LTRIM (‘Good Morning’, ‘Good)	Morning
RTRIM (string_value, trim_text)	RTRIM (‘Good Morning’, ‘ Morning’)	Good
TRIM (trim_text FROM string_value)	TRIM (‘o’ FROM ‘Good Morning’)	Gd Mrning
SUBSTR (string_value, m, n)	SUBSTR (‘Good Morning’, 6, 7)	Morning
LENGTH (string_value)	LENGTH (‘Good Morning’)	12
LPAD (string_value, n, pad_value)	LPAD (‘Good’, 6, ‘*’)	**Good
RPAD (string_value, n, pad_value)	RPAD (‘Good’, 6, ‘*’)	Good**

3) Date Functions:

These are functions that take values that are of datatype DATE as input and return values of datatypes DATE, except for the MONTHS_BETWEEN function, which returns a number as output.

Few date functions are as given below.

Function Name	Return Value
ADD_MONTHS (date, n)	Returns a date value after adding ‘n’ months to the date ‘x’.
MONTHS_BETWEEN (x1, x2)	Returns the number of months between dates x1 and x2.
ROUND (x, date_format)	Returns the date ‘x’ rounded off to the nearest century, year, month, date, hour, minute, or second as specified by the ‘date_format’.
TRUNC (x, date_format)	Returns the date ‘x’ lesser than or equal to the nearest century, year, month, date, hour, minute, or second as specified by the ‘date_format’.
NEXT_DAY (x, week_day)	Returns the next date of the ‘week_day’ on or after the date ‘x’ occurs.
LAST_DAY (x)	It is used to determine the number of days remaining in a month from the date ‘x’ specified.
SYSDATE	Returns the systems current date and time.
NEW_TIME (x, zone1, zone2)	Returns the date and time in zone2 if date ‘x’ represents the time in zone1.

The below table provides the examples for the above functions

Function Name	Examples	Return Value
ADD_MONTHS ( )	ADD_MONTHS (’16-Sep-81′, 3)	16-Dec-81
MONTHS_BETWEEN( )	MONTHS_BETWEEN (’16-Sep-81′, ’16-Dec-81′)	3
NEXT_DAY( )	NEXT_DAY (’01-Jun-08′, ‘Wednesday’)	04-JUN-08
LAST_DAY( )	LAST_DAY (’01-Jun-08′)	30-Jun-08
NEW_TIME( )	NEW_TIME (’01-Jun-08′, ‘IST’, ‘EST’)	31-May-08

4) Conversion Functions:

These are functions that help us to convert a value in one form to another form. For Ex: a null value into an actual value, or a value from one datatype to another datatype like NVL, TO_CHAR, TO_NUMBER, TO_DATE.

Few of the conversion functions available in oracle are:

Function Name	Return Value
TO_CHAR (x [,y])	Converts Numeric and Date values to a character string value. It cannot be used for calculations since it is a string value.
TO_DATE (x [, date_format])	Converts a valid Numeric and Character values to a Date value. Date is formatted to the format specified by ‘date_format’.
NVL (x, y)	If ‘x’ is NULL, replace it with ‘y’. ‘x’ and ‘y’ must be of the same datatype.
DECODE (a, b, c, d, e, default_value)	Checks the value of ‘a’, if a = b, then returns ‘c’. If a = d, then returns ‘e’. Else, returns default_value.

The below table provides the examples for the above functions

Function Name	Examples	Return Value
TO_CHAR ()	TO_CHAR (3000, ‘$9999’) TO_CHAR (SYSDATE, ‘Day, Month YYYY’)	$3000 Monday, June 2008
TO_DATE ()	TO_DATE (’01-Jun-08′)	01-Jun-08
NVL ()	NVL (null, 1)	1

how to use DBMS_SCHEDULER in PL/SQL

Step1- Create 1 procedure in database.

CREATE OR REPLACE PROCEDURE XXX_UPD_ASSEST_PRC (P_ASSET_NUMBER VARCHAR2)
AS
CURSOR cur_loc_update
IS
SELECT a.asset_number,
fa.asset_id,
‘IND CORP BOOK’ book_type_code,
a.from_site location_from,
a.to_site location_to,
fdh.UNITS_ASSIGNED units,
a.segment7,
a.segment1,
a.segment3,
a.from_segment3 from_segment3,
a.flag
FROM xxbackup.XXX_fa_loc_update2809 a,
apps.fa_distribution_history fdh,
apps.fa_additions_b fa,
apps.fa_locations fl
WHERE 1 = 1
AND a.asset_number = fa.asset_number
AND fa.asset_id = fdh.asset_id
AND fdh.location_id = fl.location_id
AND DATE_INEFFECTIVE IS NULL
AND fl.segment1 = a.from_site
and a.asset_number = P_ASSET_NUMBER;

CURSOR cur_unit_trans (
p_asset_id IN NUMBER,
p_book_type_code IN VARCHAR2,
p_location_from IN VARCHAR2,
p_location_to IN VARCHAR2,
p_units IN NUMBER,
p_segment7 IN VARCHAR2,
p_segment1 IN VARCHAR2,
p_from_segment3 IN VARCHAR2,
p_segment3 IN VARCHAR2)
IS
SELECT asset_id,
book_type_code,
distribution_id,
units_assigned,
code_combination_id dist_code_combination_id,
dist_account,
location_id,
site_id,
delta_units,
source_type
FROM (SELECT fdh.asset_id asset_id,
fdh.book_type_code,
distribution_id,
units_assigned,
fdh.code_combination_id,
gcc.concatenated_segments dist_account,
fl.location_id,
fl.segment1 site_id,
TO_NUMBER (p_units) * (-1) delta_units,
‘FA’ source_type
FROM apps.fa_distribution_history fdh,
apps.fa_locations fl,
apps.fa_additions_b fa,
apps.gl_code_combinations_kfv gcc
WHERE 1 = 1
AND fdh.asset_id = p_asset_id
AND book_type_code = p_book_type_code
AND date_ineffective IS NULL
AND fa.asset_id = fdh.asset_id
AND fdh.location_id = fl.location_id
AND fdh.code_combination_id = gcc.code_combination_id
AND fl.segment3 = p_from_segment3
and fl.segment1= p_location_from
UNION ALL
SELECT p_asset_id asset_id,
p_book_type_code book_type_code,
NULL,
0,
(apps.fnd_flex_ext.get_ccid (
‘SQLGL’,
‘GL#’,
101,
TRUNC (SYSDATE),
(SELECT NVL (RTRIM (LTRIM (p_segment1)),
gcci.segment1)
|| ‘.’
|| gcci.segment2
|| ‘.’
|| gcci.segment3
|| ‘.’
|| gcci.segment4
|| ‘.’
|| (p_location_to)
|| ‘.’
|| gcci.segment6
|| ‘.’
|| NVL ( (p_segment7), gcci.segment7)
FROM apps.gl_code_combinations gcci,
apps.fa_distribution_history fdhi
WHERE 1 = 1
AND fdhi.code_combination_id =
gcci.code_combination_id
AND fdhi.asset_id = p_asset_id
AND fdhi.book_type_code = p_book_type_code
AND fdhi.date_ineffective IS NULL
AND ROWNUM = 1))),
(SELECT NVL (p_segment1, gcci.segment1)
|| ‘.’
|| gcci.segment2
|| ‘.’
|| gcci.segment3
|| ‘.’
|| gcci.segment4
|| ‘.’
|| p_location_to
|| ‘.’
|| gcci.segment6
|| ‘.’
|| NVL (p_segment7, gcci.segment7)
— added nvl
FROM apps.gl_code_combinations gcci,
apps.fa_distribution_history fdhi
WHERE 1 = 1
AND fdhi.code_combination_id =
gcci.code_combination_id
AND fdhi.asset_id = p_asset_id
AND fdhi.book_type_code = p_book_type_code
AND fdhi.date_ineffective IS NULL
AND ROWNUM = 1),
(SELECT location_id
FROM apps.fa_locations fl
WHERE 1=1
AND fl.segment1 = p_location_to
AND fl.segment3= p_segment3
AND enabled_flag = ‘Y’
AND TRUNC (SYSDATE) BETWEEN NVL (
start_date_active,
TRUNC (SYSDATE))
AND NVL (
end_date_active,
TRUNC (SYSDATE)
+ 1)),
p_location_to site_id,
TO_NUMBER (p_units) delta_units,
‘New’ source_type
FROM apps.fa_locations fa
WHERE 1 = 1
AND fa.segment1 = p_location_to
AND fa.segment3= p_segment3
AND NVL (fa.end_date_active, TRUNC (SYSDATE) + 1) >=
TRUNC (SYSDATE)
AND NVL (fa.enabled_flag, ‘Y’) = ‘Y’);

l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_mesg_count NUMBER;
l_calling_fn VARCHAR2 (30);
l_msg_data VARCHAR2 (2000);
l_tbl_ct NUMBER;
l_trans_rec apps.fa_api_types.trans_rec_type;
l_asset_hdr_rec apps.fa_api_types.asset_hdr_rec_type;
l_asset_dist_tbl apps.fa_api_types.asset_dist_tbl_type;
l_mesg VARCHAR2 (2000) := NULL;
l_ccid VARCHAR2 (2000);
p_ret_msg VARCHAR2 (2000) := NULL;
— l_asset_id Number;

BEGIN

DBMS_output.put_line(‘Block execution :’|| to_char(sysdate,’DD-MON-YYYY HH24:MI:SS’));

FOR i_loc_update IN cur_loc_update
LOOP
DBMS_output.put_line(‘For ASSET_NUMBER :’);

  DBMS_output.put_line(i_loc_update.asset_number);    

  l_tbl_ct := 1;

 DBMS_output.put_line('Before CCID :'|| to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));

  BEGIN
     SELECT (apps.fnd_flex_ext.get_ccid (
                'SQLGL',
                'GL#',
                101,
                TRUNC (SYSDATE),
                (SELECT    NVL (i_loc_update.segment1, gcci.segment1)
                        || '.'
                        || gcci.segment2
                        || '.'
                        || gcci.segment3
                        || '.'
                        || gcci.segment4
                        || '.'
                        || i_loc_update.segment3
                        || '.'
                        || gcci.segment6
                        || '.'
                        || NVL (i_loc_update.segment7, gcci.segment7)
                   FROM apps.gl_code_combinations gcci,
                        apps.fa_distribution_history fdhi
                  WHERE     1 = 1
                        AND fdhi.code_combination_id =
                               gcci.code_combination_id
                        AND fdhi.asset_id = i_loc_update.asset_id
                        AND fdhi.book_type_code =
                               i_loc_update.book_type_code
                        AND fdhi.date_ineffective IS NULL
                        AND ROWNUM = 1)))
               ccid
       INTO l_ccid
       FROM DUAL;
  EXCEPTION
     WHEN OTHERS
     THEN
        apps.fnd_file.put_line (apps.fnd_file.LOG, 'l_ccid' || l_ccid);
  END;

   DBMS_output.put_line('After CCID :'|| to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));

  FOR trans_rec IN cur_unit_trans (i_loc_update.asset_id,
                                   i_loc_update.book_type_code,
                                   i_loc_update.location_from,
                                   i_loc_update.location_to,
                                   i_loc_update.units,
                                   i_loc_update.segment7,
                                   i_loc_update.segment1,
                                   i_loc_update.from_segment3,
                                   i_loc_update.segment3)
  LOOP

   DBMS_output.put_line('Inside trans loop and before if conditions  :'|| to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
     l_return_status := NULL;
     l_asset_hdr_rec.asset_id := trans_rec.asset_id;
     l_asset_hdr_rec.book_type_code := trans_rec.book_type_code;
     l_asset_dist_tbl (l_tbl_ct).distribution_id :=
        trans_rec.distribution_id;
     l_asset_dist_tbl (l_tbl_ct).distribution_id :=
        trans_rec.distribution_id;

     IF (trans_rec.distribution_id IS NOT NULL)
     THEN
        l_asset_dist_tbl (l_tbl_ct).transaction_units :=
           trans_rec.delta_units;
        l_asset_dist_tbl (l_tbl_ct).expense_ccid :=
           trans_rec.dist_code_combination_id;
        l_asset_dist_tbl (l_tbl_ct).location_ccid := trans_rec.location_id;
        l_asset_dist_tbl (l_tbl_ct).assigned_to := NULL;
     ELSE
        l_asset_dist_tbl (l_tbl_ct).transaction_units :=
           trans_rec.delta_units;
        l_asset_dist_tbl (l_tbl_ct).units_assigned :=
           trans_rec.delta_units;
        l_asset_dist_tbl (l_tbl_ct).expense_ccid :=
           trans_rec.dist_code_combination_id;
        l_asset_dist_tbl (l_tbl_ct).location_ccid := trans_rec.location_id;
        l_asset_dist_tbl (l_tbl_ct).assigned_to := NULL;
     END IF;


     l_tbl_ct := l_tbl_ct + 1;
     l_return_status := NULL;
     l_msg_count := NULL;
     l_msg_data := NULL;
  END LOOP;

   DBMS_output.put_line('After trans end loop :'|| to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));

    DBMS_output.put_line('Before API call :'|| to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));


  apps.fa_transfer_pub.do_transfer (
     p_api_version        => 1.0,
     p_init_msg_list      => apps.fnd_api.g_true,
     p_commit             => apps.fnd_api.g_false,
     p_validation_level   => apps.fnd_api.g_valid_level_full,
     p_calling_fn         => l_calling_fn,
     x_return_status      => l_return_status,
     x_msg_count          => l_msg_count,
     x_msg_data           => l_msg_data,
     px_trans_rec         => l_trans_rec,
     px_asset_hdr_rec     => l_asset_hdr_rec,
     px_asset_dist_tbl    => l_asset_dist_tbl);

 DBMS_output.put_line('After API call :'|| to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
 DBMS_output.put_line('l_return_status ::'|| l_return_status);
  --COMMIT;
  IF (NVL (l_return_status, 'X') <> apps.fnd_api.g_ret_sts_success)
  THEN
     l_mesg_count := apps.fnd_msg_pub.count_msg;

     IF l_mesg_count > 0
     THEN
        l_mesg :=
              CHR (10)
           || SUBSTR (
                 apps.fnd_msg_pub.get (apps.fnd_msg_pub.g_first,
                                       apps.fnd_api.g_false),
                 1,
                 250);



        FOR i IN 1 .. (l_mesg_count - 1)
        LOOP
           l_mesg :=
              SUBSTR (
                 apps.fnd_msg_pub.get (apps.fnd_msg_pub.g_next,
                                       apps.fnd_api.g_false),
                 1,
                 250);
        END LOOP;

        apps.fnd_msg_pub.delete_msg ();
     END IF;

     -----------------------------------end---------------------------------------------------------------------
     apps.fnd_msg_pub.count_and_get (p_count   => l_msg_count,
                                     p_data    => l_msg_data);

     p_ret_msg :=
        'Error while asset transfer - apps.fa_transfer_pub.do_transfer';
     DBMS_OUTPUT.put_line ('p_ret_msg ==> ' || p_ret_msg);

  ELSE
     p_ret_msg := NULL;

  END IF;

END LOOP;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
p_ret_msg := ‘Error while asset transfer.’ || SQLERRM;

  DBMS_OUTPUT.put_line ('p_ret_msg ==> ' || p_ret_msg);

END;
/

Step2-

DECLARE
CURSOR cur_loc_update
IS
SELECT DISTINCT a.asset_number
FROM xxbackup.XXX_fa_loc_update2809 a,
apps.fa_distribution_history fdh,
apps.fa_additions_b fa,
apps.fa_locations fl
WHERE 1 = 1
AND a.asset_number = fa.asset_number
AND fa.asset_id = fdh.asset_id
AND fdh.location_id = fl.location_id
AND DATE_INEFFECTIVE IS NULL
AND fl.segment1 = a.from_site;

BEGIN

DBMS_SCHEDULER.CREATE_PROGRAM(
    PROGRAM_NAME => 'XXX_FA_UPD_ASSET_PROGRAM'
    ,PROGRAM_TYPE => 'STORED_PROCEDURE'
    ,PROGRAM_ACTION => 'XXX_UPD_ASSEST_PRC'
    ,NUMBER_OF_ARGUMENTS => 1
    ,ENABLED =>  FALSE
    ,COMMENTS => 'my test program'
);

DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(
    PROGRAM_NAME => 'XXX_FA_UPD_ASSET_PROGRAM',
    argument_name     => 'P_ASSET_NUMBER',
    argument_position => 1,
    argument_type     => 'VARCHAR2',
    default_value     => ''
);

dbms_scheduler.enable (name => 'XXX_FA_UPD_ASSET_PROGRAM');

FOR i_loc_update IN cur_loc_update
LOOP

   DBMS_output.put_line('For ASSET_NUMBER :'|| i_loc_update.ASSET_NUMBER);

     DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'MY_TEST_JOB_'||i_loc_update.ASSET_NUMBER||'_5',
    PROGRAM_NAME => 'XXX_FA_UPD_ASSET_PROGRAM'
);

dbms_scheduler.set_job_argument_value(
    job_name          => 'MY_TEST_JOB_'||i_loc_update.ASSET_NUMBER||'_5',
    argument_position => 1,
    argument_value    => i_loc_update.ASSET_NUMBER);


DBMS_SCHEDULER.RUN_JOB(
    JOB_NAME            => 'MY_TEST_JOB_'||i_loc_update.ASSET_NUMBER||'_5',
    USE_CURRENT_SESSION => FALSE);

END LOOP;

END;

Call Oracle form through OAF personalization (Button) with form parameter

Step1- Set OAF personalization profile.

Profile Name	Value
FND: Personalization Region Link Enabled	Yes
Personalize Self-Service Defn	Yes
Disable Self-Service Personal	No

Step2- Create New Item(Button) on OAF page

Step3- Enter mandatory fields

Step4- Destination URI field value-

form:<responsibilityApplicationShortName>:<responsibilityKey>:<securityGroupKey>:<FORM_NAME>:param1={#<PARAMETER1>}

Ex– form:FND:FNDWF_USER_WEB:STANDARD:ATCPOINDDUPPRDTLS:param1={#RequisitionHeaderId}

Step5- Save it

Go to Notification page and click on “Project PR Details” button, It will open oracle form and display values based on parameter.

Steps to create a XML Publisher Report

1] Add the “Xml Publisher Administrator” Responsibility to the user through the front end.
2] Create the Report(Data Model or we can say the .Rdf file) using Oracle Report Builder.
3] Set the user parameter as p_conc_request_id.
4] Add the default values to the Before Report and After Report triggers(not mandatory)
5] Ftp the Report to the Cust_Top/Report/Us.
6] Open the Oracle E-Business Suite then go to Sysadmin>Concurrent>Program>Executables, Here we have to create one executable file for that Rdf.
7] Then go to Sysadmin>Concurrent>Program>Define, Here we have to make a Concurrent Program for that Executable. Make sure that the output format must be XML.
8] Goto the Sysadmin>Security>Responsibility>Define. Query for the Xml Publisher Administrator. See the Request Group attached to this. Attach the Concurrent Program to this Request Group.
9] Design the template in Ms Word(Using the .Rtf file).
10] Goto responsibility XML PUBLISHER ADMINISTRATOR. Then Goto HOME>DATA DEFINITION>CREATE DATA DEFINITION and create a new data definition. Make sure that your Data Definition’s Code should be same as Concurrent Program’s Short Name used by you to create the Rdf file.
11] Now go to Xml publisher administrator>Home>Template. Create a new template with template type=’Rtf’. Then upload the RTF File by browsing the path.
12] Now go to the Responsibility and run the request.

Setting the Password for PDF File sent through XML Publisher

Open the rtf for which you want to set password and do the following things

1) Open the .rtf
2) Go to File – > Properties
Create a new custom property
a) Name : xdo-pdf-open-password
Type : text
Value : Either Hard Code the value / or get the value for xml data
b) Name : xdo-pdf-security
Type : text
Value : true

Note : property name should always start with xdo- .

Query to get details of XML Report

SELECT fndcptl.USER_CONCURRENT_PROGRAM_NAME UserConcurrentProgram,
fndcp.CONCURRENT_PROGRAM_NAME ConcurrentProgramShortName,
fndex.EXECUTION_FILE_NAME ExecutionFileName,
datadefinitiontl.DATA_SOURCE_NAME DataDefinitionName,
datadefinition.DATA_SOURCE_CODE DataDefinitionCode,
templatestl.TEMPLATE_NAME TemplateName,
templates.TEMPLATE_CODE TemplateCode,
lobs.FILE_NAME FileName
FROM fnd_executables fndex,
fnd_concurrent_programs fndcp,
fnd_concurrent_programs_tl fndcptl,
xdo_ds_definitions_b datadefinition,
xdo_ds_definitions_tl datadefinitiontl,
xdo_templates_b templates,
xdo_templates_tl templatestl,
xdo_lobs lobs
WHERE fndex.EXECUTABLE_ID = fndcp.EXECUTABLE_ID
AND fndcp.CONCURRENT_PROGRAM_ID = fndcptl.CONCURRENT_PROGRAM_ID
AND fndcp.CONCURRENT_PROGRAM_NAME = datadefinition.DATA_SOURCE_CODE
AND datadefinition.DATA_SOURCE_CODE =
datadefinitiontl.DATA_SOURCE_CODE
AND datadefinitiontl.DATA_SOURCE_CODE = templates.DATA_SOURCE_CODE
AND templates.TEMPLATE_CODE = templatestl.TEMPLATE_CODE
AND templatestl.TEMPLATE_CODE = lobs.LOB_CODE
AND lobs.LOB_TYPE = ‘TEMPLATE_SOURCE’
AND fndex.EXECUTABLE_NAME = ‘XXAJ_SALES_ORDER_REPORT’

Benefits of Oracle XML Publisher

» Document Management Solution well integrated with Oracle Applications.
» A solution which authors, manages and delivers the Business documents.
» Meets the Business Requirements like corporate identity.
» Removes the Complexity
» Reduces Maintenance Cost
» Reduces Total Cost
» One Template -> Multiple Outputs -> PDF,HTML,EXCEL,RTF
» Business Consultants can use familiar desktop tools like Word, PDF, etc.
» Extract one time ->-> Publish multiple times

Developing XML Publisher Report – using Data Source as PL/SQL Stored Procedure

Background:

Developing sample XML Publisher Report with Executable Method as ‘PL/SQL Stored Procedure’

Prerequisite for the below Example:
1. Create a table
CREATE TABLE demo_products
( product_code NUMBER,
product_name VARCHAR2 (100));
2. Insert Values
INSERT INTO demo_products
VALUES (569, ‘Oracle Cost Management’);
3. Issue Commit

1. Create a Package Spec & Body with a single Procedure
Spec:

CREATE OR REPLACE PACKAGE APPS.MY_PACKAGE
AS
PROCEDURE REPORT (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_product_id IN NUMBER);
END MY_PACKAGE;
/
Body:
CREATE OR REPLACE PACKAGE BODY APPS.MY_PACKAGE
AS
PROCEDURE REPORT (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_product_id IN NUMBER)
IS
l_qryCtx DBMS_XMLGEN.ctxHandle;
l_query VARCHAR2 (32000);
l_length NUMBER (10);
l_xmlstr VARCHAR2 (32000);
l_offset NUMBER (10) := 32000;
l_retrieved NUMBER (10) := 0;
l_result CLOB;
l_no_rows NUMBER;
BEGIN
l_query := ‘SELECT product_code, product_name
FROM demo_products
WHERE product_code = ‘ || p_product_id;

l_qryCtx := DBMS_XMLGEN.newContext (l_query);

— set rowset tag to PRODUCTS and row tag to PRO_DETAILS
DBMS_XMLGEN.setRowSetTag (l_qryCtx, ‘PRODUCTS’);
DBMS_XMLGEN.setRowTag (l_qryCtx, ‘PRO_DETAILS’);

— now get the result
l_result := DBMS_XMLGEN.getXML (l_qryCtx);
l_no_rows := DBMS_XMLGEN.GETNUMROWSPROCESSED (l_qryCtx);
FND_FILE.put_line (FND_FILE.LOG, ‘No of rows processed= ‘ || l_no_rows);

l_length := NVL (DBMS_LOB.getlength (l_result), 0);
FND_FILE.put_line (FND_FILE.LOG, ‘Length= ‘ || l_length);

LOOP
EXIT WHEN l_length = l_retrieved;

IF (l_length – l_retrieved) < 32000
THEN
SELECT SUBSTR (l_result, l_retrieved + 1) INTO l_xmlstr FROM DUAL;

l_retrieved := l_length;
fnd_file.put_line (fnd_file.output, l_xmlstr);
ELSE
SELECT SUBSTR (l_result, l_retrieved + 1, l_offset)
INTO l_xmlstr
FROM DUAL;

l_retrieved := l_retrieved + l_offset;
fnd_file.put_line (fnd_file.output, l_xmlstr);
END IF;
END LOOP;

DBMS_XMLGEN.closeContext (l_qryCtx);
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, SQLERRM);
raise_application_error (-20001, ‘Error in procedure MY_PACKAGE.report’);
END REPORT;
END MY_PACKAGE;
/

2. Define Executable
Navigation: Application Developer > Concurrent > Executable

Provide Executable, Short Name, Application, Description & also
Execution Method: PL/SQL Stored Procedure
Execution File Name: MY_PACKAGE.REPORT

3. Define Concurrent Program
Navigation: Application Developer > Concurrent > Program

Provide Program, Short name, Application, Description & also
– Executable Name as defined in the above step

– Output Format should be XML
– Define a Parameter p_product_id
– Associate Concurrent Program to the Request Group.

4. Create Data Definition
Navigation: XML Publisher Administrator -> Data Definitions -> Create Data Definition
The code should be exactly same as concurrent program short name.

5. Create Template. Register Template with the XML Publisher
Navigation: XML Publisher Administrator -> Templates -> Create Template

6. Run the Concurrent Program to see the output

Oracle Apps Technical

Sajal Agarwal

Update Vendor Site details through backend

What is DDL and What is DML in Oracle

What is DDL?

Commands for DDL

CREATE

DROP

ALTER

TRUNCATE:

What is DML?

Commands for DML

INSERT:

UPDATE:

DELETE:

Single Row Function and Group Function

Oracle Built in Functions

1) Numeric Functions:

2) Character or Text Functions:

3) Date Functions:

4) Conversion Functions:

how to use DBMS_SCHEDULER in PL/SQL

Call Oracle form through OAF personalization (Button) with form parameter

Steps to create a XML Publisher Report

Setting the Password for PDF File sent through XML Publisher

Query to get details of XML Report

Benefits of Oracle XML Publisher

Developing XML Publisher Report – using Data Source as PL/SQL Stored Procedure