OracleApps02: September 2018

API To Load Lookup Values

1) compile below pacage specification and body

CREATE OR REPLACE PACKAGE APPS.xxdm_po_lookup_values_blk_pkg
IS
PROCEDURE insert_row_p (p_meaning IN VARCHAR2,
p_lookuptype IN VARCHAR2,
p_lookupcode IN VARCHAR2,
p_tag IN VARCHAR2,
p_description IN VARCHAR2,
p_start_date_active IN DATE,
p_view_application_id IN NUMBER);
END xxdm_po_lookup_values_blk_pkg;
/

CREATE OR REPLACE PACKAGE BODY APPS.xxdm_po_lookup_values_blk_pkg
IS
PROCEDURE insert_row_p (p_meaning IN VARCHAR2,
p_lookuptype IN VARCHAR2,
p_lookupcode IN VARCHAR2,
p_tag IN VARCHAR2,
p_description IN VARCHAR2,
p_start_date_active IN DATE,
p_view_application_id IN NUMBER)
IS
/*
create table xxdm_po_lookup_values_blk_t (p_meaning varchar2(150),
p_lookuptype varchar2(150),
p_lookupcode varchar2(150),
p_tag varchar2(2),
p_description varchar2(240),
p_start_date_active date,
p_view_application_id number);
*/
i_rowid VARCHAR2 (100) := 0;
BEGIN
i_rowid := NULL;

fnd_lookup_values_pkg.insert_row (
x_rowid => i_rowid,
x_lookup_type => p_lookuptype,
x_security_group_id => 0,
x_view_application_id => p_view_application_id,
x_lookup_code => p_lookupcode,
x_tag => p_tag,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_enabled_flag => 'Y',
x_start_date_active => TO_DATE ('01-JAN-1950', 'DD-MON-YYYY'),
x_end_date_active => NULL,
x_territory_code => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_meaning => p_meaning,
x_description => p_description,
x_creation_date => SYSDATE,
x_created_by => fnd_global.user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => fnd_global.user_id,
x_last_update_login => fnd_global.login_id);
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line ('Error when Load for lookupcode '||p_lookupcode||'-'||SQLERRM);
END insert_row_p;
END xxdm_po_lookup_values_blk_pkg;
/

3) Load Excel table into table (XXDM_PO_LOOKUP_VALUES_BLK_T)

4) execute below query

declare
cursor c1 is select * from XXDM_PO_LOOKUP_VALUES_BLK_T;
begin
for i in c1 loop
xxdm_po_lookup_values_blk_pkg.insert_row_p (i.p_meaning,i.p_lookuptype,i.p_lookupcode,i.p_tag,i.p_description,null,201);
end loop;
commit;
end;

API To Load Flex Values

1) create table xxdm_load_flex_values_t (flex_value varchar2(150), description varchar2(240));

2) insert excel data into table

3) compile below procedure

CREATE OR REPLACE PROCEDURE APPS.xxdm_load_flex_values_p
IS
--create table xxdm_load_flex_values_t (flex_value varchar2(150), description varchar2(240));

CURSOR c1
IS
SELECT * FROM xxdm_load_flex_values_t ;

v_start_date_active DATE := SYSDATE;
v_who_type fnd_flex_loader_apis.who_type;
v_user_id NUMBER := fnd_global.user_id;
v_login_id NUMBER := fnd_global.login_id;
v_req_id NUMBER := fnd_global.conc_request_id;
-----------
BEGIN
--
DBMS_OUTPUT.put_line ('in begin');
v_who_type.created_by := v_user_id;
v_who_type.creation_date := SYSDATE;
v_who_type.last_updated_by := v_user_id;
v_who_type.last_update_date := SYSDATE;
v_who_type.last_update_login := v_login_id;
--
DBMS_OUTPUT.put_line ('after begin');

FOR i IN c1
LOOP
--
--api to load values
fnd_flex_values_pkg.load_row (
x_flex_value_set_name => 'DM_GROUP_OWNER',
x_parent_flex_value_low => NULL,
x_flex_value => TRIM (i.flex_value),
x_who => v_who_type,
x_enabled_flag => 'Y',
x_summary_flag => 'N',
x_start_date_active => null,
x_end_date_active => NULL,
x_parent_flex_value_high => NULL,
x_structured_hierarchy_level => NULL,
x_hierarchy_level => NULL,
x_compiled_value_attributes => NULL,
x_value_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL,
x_attribute21 => NULL,
x_attribute22 => NULL,
x_attribute23 => NULL,
x_attribute24 => NULL,
x_attribute25 => NULL,
x_attribute26 => NULL,
x_attribute27 => NULL,
x_attribute28 => NULL,
x_attribute29 => NULL,
x_attribute30 => NULL,
x_attribute31 => NULL,
x_attribute32 => NULL,
x_attribute33 => NULL,
x_attribute34 => NULL,
x_attribute35 => NULL,
x_attribute36 => NULL,
x_attribute37 => NULL,
x_attribute38 => NULL,
x_attribute39 => NULL,
x_attribute40 => NULL,
x_attribute41 => NULL,
x_attribute42 => NULL,
x_attribute43 => NULL,
x_attribute44 => NULL,
x_attribute45 => NULL,
x_attribute46 => NULL,
x_attribute47 => NULL,
x_attribute48 => NULL,
x_attribute49 => NULL,
x_attribute50 => NULL,
x_attribute_sort_order => NULL,
x_flex_value_meaning => TRIM (i.flex_value),
x_description => TRIM (i.description));
END LOOP;

DBMS_OUTPUT.put_line ('after commit');
--
EXCEPTION
WHEN OTHERS
THEN
rollback;
DBMS_OUTPUT.put_line ('error is ' || SUBSTR (SQLERRM, 1, 1000));
END xxdm_load_flex_values_p;
/

4) execute below query

exec xxdm_load_flex_values_p;

Logic to run fast Query on Date parameters

select /*+ parallel(aa, 4) parallel(xx, 4) */ *

from XXDM_MEDNEXT_CLAIM_INV_V MDX ,

(select :P_FROM_DATE fromdate,:P_TO_DATE todate from dual )

where MDX.INVOICE_DATE >=xx.fromdate

and MDX.INVOICE_DATE <= xx.todate

Procedure Based Report

procedure xxdm_ar_inv_err_report_p(errbuf OUT VARCHAR2,retcode OUT VARCHAR2,p_group_id IN number)

cursor c1 is

select *

from xxdm_policy_error_log_t

where GROUP_ID=p_group_id;

cursor c2 is

select *

from XXDM_FALCON_AR_INV_INT_STG_T

where GROUP_ID=p_group_id;

begin

/*First line of XML data should be ‘<?xml version="1.0"?>’*/

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<?xml version="1.0"?>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<G1>');

for j in c1

loop

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<ACTIVITY_LOG>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<ERROR_LOG_ID>' || j.ERROR_LOG_ID || '</ERROR_LOG_ID>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<INSTANCE_ID>' || j.INSTANCE_ID || '</INSTANCE_ID>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<ERROR_MSG>' || j.ERROR_MSG || '</ERROR_MSG>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<ERROR_TYPE>' || j.ERROR_TYPE || '</ERROR_TYPE>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<CREATED_DATE>' || j.CREATED_DATE || '</CREATED_DATE>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<GROUP_ID>' || j.GROUP_ID || '</GROUP_ID>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '</ACTIVITY_LOG>');

end loop;

for i in c2

loop

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<EBS_LOG>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<TRX_HEADER_ID>' || i.TRX_HEADER_ID || '</TRX_HEADER_ID>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<TRX_NUMBER>' || i.TRX_NUMBER || '</TRX_NUMBER>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<OPERATING_UNIT_ID>' || i.OPERATING_UNIT_ID || '</OPERATING_UNIT_ID>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<SET_OF_BOOKS_ID>' || i.SET_OF_BOOKS_ID || '</SET_OF_BOOKS_ID>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<BATCH_SOURCE_NAME>' || i.BATCH_SOURCE_NAME || '</BATCH_SOURCE_NAME>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<BATCH_SOURCE_ID>' || i.BATCH_SOURCE_ID || '</BATCH_SOURCE_ID>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<SOURCE_TRANSACTION_NUMBER>' || i.SOURCE_TRANSACTION_NUMBER || '</SOURCE_TRANSACTION_NUMBER>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<TRANSACTION_TYPE>' || i.TRANSACTION_TYPE || '</TRANSACTION_TYPE>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<TRANSACTION_TYPE_ID>' || i.TRANSACTION_TYPE_ID || '</TRANSACTION_TYPE_ID>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<TRANSACTION_CLASS_TYPE>' || i.TRANSACTION_CLASS_TYPE || '</TRANSACTION_CLASS_TYPE>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<TRX_CLASS_ID>' || i.TRX_CLASS_ID || '</TRX_CLASS_ID>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<TRANSACTION_DATE>' || i.TRANSACTION_DATE || '</TRANSACTION_DATE>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<CUSTOMER_NAME>' || i.CUSTOMER_NAME || '</CUSTOMER_NAME>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<CUSTOMER_NUMBER>' || i.CUSTOMER_NUMBER || '</CUSTOMER_NUMBER>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<GL_DATE>' || i.GL_DATE || '</GL_DATE>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<TERMS_NAME>' || i.TERMS_NAME || '</TERMS_NAME>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<EXCHANGE_RATE_TYPE>' || i.EXCHANGE_RATE_TYPE || '</EXCHANGE_RATE_TYPE>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<EXCHANGE_DATE>' || i.EXCHANGE_DATE || '</EXCHANGE_DATE>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<EXCHANGE_RATE>' || i.EXCHANGE_RATE || '</EXCHANGE_RATE>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<LEGAL_ENTITY_NAME>' || i.LEGAL_ENTITY_NAME || '</LEGAL_ENTITY_NAME>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<REFERENCE_NUMBER>' || i.REFERENCE_NUMBER || '</REFERENCE_NUMBER>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<ATTRIBUTE_CATEGORY>' || i.ATTRIBUTE_CATEGORY || '</ATTRIBUTE_CATEGORY>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<POLICY_NUMBER>' || i.POLICY_NUMBER || '</POLICY_NUMBER>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<POLICY_EXTERNAL_REFERENCE>' || i.POLICY_EXTERNAL_REFERENCE || '</POLICY_EXTERNAL_REFERENCE>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<BILL_EFFECTIVE_DATE>' || i.BILL_EFFECTIVE_DATE || '</BILL_EFFECTIVE_DATE>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<BILL_EXPIRY_DATE>' || i.BILL_EXPIRY_DATE || '</BILL_EXPIRY_DATE>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<BILL_REF>' || i.BILL_REF || '</BILL_REF>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<BILL_PAYABLE_AMOUNT>' || i.BILL_PAYABLE_AMOUNT || '</BILL_PAYABLE_AMOUNT>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<REMINDER_LETTER_SENT>' || i.REMINDER_LETTER_SENT || '</REMINDER_LETTER_SENT>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<SALES_LOCATION>' || i.SALES_LOCATION || '</SALES_LOCATION>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<POLICY_GROUP>' || i.POLICY_GROUP || '</POLICY_GROUP>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<SOURCE_HEADER_ID>' || i.SOURCE_HEADER_ID || '</SOURCE_HEADER_ID>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<EVENT_TYPE>' || i.EVENT_TYPE || '</EVENT_TYPE>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<PRODUCT_CATEGORY>' || i.PRODUCT_CATEGORY || '</PRODUCT_CATEGORY>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<CUSTOMER_SITE_ADDRESS>' || i.CUSTOMER_SITE_ADDRESS || '</CUSTOMER_SITE_ADDRESS>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<SALES_PERSON>' || i.SALES_PERSON || '</SALES_PERSON>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<SALES_REP_ID>' || i.SALES_REP_ID || '</SALES_REP_ID>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<TRX_LINE_ID>' ||i.TRX_LINE_ID || '</TRX_LINE_ID>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<LINE_NUMBER>' || i.LINE_NUMBER || '</LINE_NUMBER>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<LINE_TYPE>' ||i.LINE_TYPE || '</LINE_TYPE>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<MEMO_LINE>' || i.MEMO_LINE || '</MEMO_LINE>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<MEMO_LINE_DESCRIPTION>' ||i.MEMO_LINE_DESCRIPTION || '</MEMO_LINE_DESCRIPTION>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<QUANTITY>' || i.QUANTITY || '</QUANTITY>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<UNIT_PRICE>' || i.UNIT_PRICE || '</UNIT_PRICE>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<AMOUNT>' || i.AMOUNT || '</AMOUNT>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<LINE_GL_DATE>' ||i.LINE_GL_DATE || '</LINE_GL_DATE>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<RULE_START_DATE>' || i.RULE_START_DATE || '</RULE_START_DATE>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<RULE_END_DATE>' ||i.RULE_END_DATE || '</RULE_END_DATE>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<LINE_PRODUCT_CATEGORY>' ||i.LINE_PRODUCT_CATEGORY || '</LINE_PRODUCT_CATEGORY>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<SOURCE_LINE_ID>' || i.SOURCE_LINE_ID || '</SOURCE_LINE_ID>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<TAX_LINK_LINE_ID>' ||i.TAX_LINK_LINE_ID || '</TAX_LINK_LINE_ID>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<TAX>' || i.TAX || '</TAX>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<TAX_RATE_CODE>' || i.TAX_RATE_CODE || '</TAX_RATE_CODE>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<TAX_AMOUNT>' ||i.TAX_AMOUNT || '</TAX_AMOUNT>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<LINE_CORELATION_ID>' || i.LINE_CORELATION_ID || '</LINE_CORELATION_ID>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<DISTRIBUTION_NUMBER>' || i.DISTRIBUTION_NUMBER || '</DISTRIBUTION_NUMBER>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<ACCOUNT_CLASS>' || i.ACCOUNT_CLASS || '</ACCOUNT_CLASS>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<DISTRIBUTION_PERCENT>' || i.DISTRIBUTION_PERCENT || '</DISTRIBUTION_PERCENT>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<DIST_AMOUNT>' || i.DIST_AMOUNT || '</DIST_AMOUNT>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<REV_ACCOUNT_COMBINATIONS>' || i.REV_ACCOUNT_COMBINATIONS || '</REV_ACCOUNT_COMBINATIONS>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<REC_ACCOUNT_COMBINATIONS>' || i.REC_ACCOUNT_COMBINATIONS || '</REC_ACCOUNT_COMBINATIONS>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<GROUP_ID>' || i.GROUP_ID || '</GROUP_ID>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<STATUS>' || i.STATUS || '</STATUS>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '<ERROR_MESSAGE>' ||i.ERROR_MESSAGE || '</ERROR_MESSAGE>');

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '</EBS_LOG>');

end loop;

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '</G1>');

end xxdm_ar_inv_err_report_p;