Here in this scenario Legacy data dump into my staging table from other platform. Here you can consider other platform means any other technology other than Oracle Apps.
Staging Table Creation:
/*------------------------------------------------------------
Header Columns:
------------------------------------------------------------*/
CREATE TABLE XXXX.XXXX_TRX_HEADERS_INTERFACE
/* +====================================================================+
| |
| |
|$Id: $ |
| |
|Description : XXXX_TRX_HEADERS_INTERFACE |
| This table will have AR Invoice Header Legacy |
| Data |
| |
|Change History: |
|--------------- |
|Version Date Author Remarks |
|------- ---------- -------------- --------------------------------|
|1.0 XX XX Initial Version |
| |
+====================================================================+ */
(RECORD_ID NUMBER
,CREATION_DATE DATE
,RECORD_STATUS VARCHAR2(1)
,ERROR_MSG VARCHAR2(2000)
,BATCH_ID NUMBER
,BATCH_SOURCE VARCHAR2(100)
,TRX_COMMENTS VARCHAR2(2000)
,ORIG_SYSTEM_BILL_CUSTOMER_ID NUMBER
,ORIG_SYSTEM_BILL_ADDRESS_ID NUMBER
,ORIG_SYSTEM_SHIP_CUSTOMER_ID NUMBER
,ORIG_SYSTEM_SHIP_ADDRESS_ID NUMBER
,CUSTOMER_BANK_ACCOUNT_NAME VARCHAR2(80)
,CUST_TRX_TYPE_NAME VARCHAR2(20)
,TRX_NUMBER VARCHAR2(20)
,LAST_UPDATE_DATE DATE
,CUSTOMER_TRX_ID NUMBER
,DOCUMENT_NUMBER NUMBER
,TRX_DATE DATE
,GL_DATE DATE
,ORG_ID NUMBER
,TERM_NAME VARCHAR2(15)
,INVOICING_RULE_NAME VARCHAR2(30)
,ACCOUNTING_RULE_DURATION NUMBER
,PREVIOUSE_CUST_TRX_ID NUMBER
,PRIMARY_SALESREP_NUMBER VARCHAR2(30)
,ATTRIBUTE_CATEGORY VARCHAR2(30)
,ATTRIBUTE1 VARCHAR2(150)
,ATTRIBUTE2 VARCHAR2(150)
,ATTRIBUTE3 VARCHAR2(150)
,ATTRIBUTE4 VARCHAR2(150)
,ATTRIBUTE5 VARCHAR2(150)
,ATTRIBUTE6 VARCHAR2(150)
,ATTRIBUTE7 VARCHAR2(150)
,ATTRIBUTE8 VARCHAR2(150)
,ATTRIBUTE9 VARCHAR2(150)
,ATTRIBUTE10 VARCHAR2(150)
,ATTRIBUTE11 VARCHAR2(150)
,ATTRIBUTE12 VARCHAR2(150)
,ATTRIBUTE13 VARCHAR2(150)
,ATTRIBUTE14 VARCHAR2(150)
,ATTRIBUTE15 VARCHAR2(150)
,COMMENTS VARCHAR2(240)
,REASON VARCHAR2(2000)
);
/
CREATE OR REPLACE PUBLIC SYNONYM XXXX_TRX_HEADERS_INTERFACE
FOR XXIMSF.XXXX_TRX_HEADERS_INTERFACE;
/
/*------------------------------------------------------------
Line Columns:
------------------------------------------------------------*/
CREATE TABLE XXXX.XXXX_TRX_LINES_INTERFACE
/* +====================================================================+
| |
| |
|$Id: $ |
| |
|Description : XXXX_TRX_LINES_INTERFACE |
| This table will have AR Invoice Lines Legacy |
| Data |
| |
|Change History: |
|--------------- |
|Version Date Author Remarks |
|------- ---------- -------------- --------------------------------|
|1.0 XX XX Initial Version |
| |
+====================================================================+ */
(RECORD_ID NUMBER
,LINE_NUMBER NUMBER
,RECORD_STATUS VARCHAR2(1)
,ERROR_MSG VARCHAR2(2000)
,LINE_TYPE VARCHAR2(20)
,DESCRIPTION VARCHAR2(240)
,QUANTITY NUMBER
,UNIT_SELLING_PRICE NUMBER
,UNIT_STANDARD_PRICE NUMBER
,UOM_CODE VARCHAR2(3)
,UOM_NAME VARCHAR2(25)
,CURRENCY_CODE VARCHAR2(15)
,AMOUNT NUMBER
,CONVERSION_TYPE VARCHAR2(30)
,CONVERSION_DATE DATE
,CONVERSION_RATE NUMBER
,INTERFACE_LINE_CONTEXT VARCHAR2(30)
,INTERFACE_LINE_ATTRIBUTE1 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE2 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE3 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE4 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE5 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE6 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE7 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE8 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE9 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE10 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE11 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE12 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE13 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE14 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE15 VARCHAR2(150)
,RULE_START_DATE DATE
,RULE_END_DATE DATE
,LINK_TO_LINE_ID NUMBER
,CREDIT_METHOD_FOR_ACCT_RULE VARCHAR2(30)
,CREDIT_METHOD_FOR_INSTALLMENTS VARCHAR2(30)
,MEMO_LINE_NAME VARCHAR2(50)
,COMMENTS VARCHAR2(240)
,COGS_AMOUNT NUMBER
,COGS_PERCENTAGE NUMBER
,COGS_GL_FLAG VARCHAR2(1)
,TAX_REGIME_CODE VARCHAR2(30)
,TAX_TYPE VARCHAR2(30)
,TAX_JURISDICATION VARCHAR2(50)
,TAX_REG_STATUS VARCHAR2(50)
,TAX_RATE_CODES VARCHAR2(50)
,CREDIT_ALLOCATION_RULE VARCHAR2(100)
,UNIT_PRICE NUMBER
,UOM VARCHAR2(30)
,SUPPLY_DATE DATE
,TAX_AMOUNT NUMBER
,TAX_GL_CODE_COMBINATION VARCHAR2(500)
,REV_GL_CODE_COMBINATION VARCHAR2(500)
,TAXABLE_AMOUNT NUMBER
,REASON VARCHAR2(2000)
,COGS_TAX_REG_STATUS VARCHAR2(30)
,COGS_TAX_RATE_CODES VARCHAR2(50)
,COGS_TAX_AMOUNT NUMBER
,ORA_TRX_DATE DATE
,ORA_GL_DATE DATE
,ORA_CREATION_DATE DATE
,ITEM_CODE VARCHAR2(240)
,TAX_STATUS_CODE VARCHAR2(240)
,TAX_ONLY_ORIGINAL_INVOICE VARCHAR2(240)
,TAX_ONLY_INVOICE_DATE DATE
,TAX_ONLY_INVOICE_AMOUNT NUMBER
,PRODUCT_TYPE VARCHAR2(240)
);
/
CREATE OR REPLACE PUBLIC SYNONYM XXXX_TRX_LINES_INTERFACE
FOR XXIMSF.XXXX_TRX_LINES_INTERFACE;
/
Here above tables purpose of creating it will hold the data till validation and periodically data will be flush out from above tables.
Now creating the AR Invoices with Multiple lines and separate Tax Lines:
SET SERVEROUTPUT ON;
DECLARE
/* Local Variables declaration */
/* API Output Variables */
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
/* Record Type Variables */
l_batch_source_rec ar_invoice_api_pub.batch_source_rec_type;
l_trx_header_tbl ar_invoice_api_pub.trx_header_tbl_type;
l_trx_lines_tbl ar_invoice_api_pub.trx_line_tbl_type;
l_trx_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type;
l_trx_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type;
l_trx_contingencies_tbl_type ar_invoice_api_pub.trx_contingencies_tbl_type;
/* Local Variables */
l_batch_id NUMBER;
l_cnt NUMBER := 0;
l_cntt NUMBER:= 1;
v_sr_id VARCHAR2(240);
v_sr_number VARCHAR2(240);
v_tax_regime_code VARCHAR2(240);
v_tax_class_code VARCHAR2(240);
ln_CUSTOMER_ID NUMBER;
l_receipt_id NUMBER;
lh_trx_type_id NUMBER;
l_customer_trx_id NUMBER;
l_user_id NUMBER;
l_resp_id NUMBER;
l_resp_appl_id NUMBER;
ln_line_no NUMBER;
lh_term_id NUMBER;
lh_bill_to_customer_id NUMBER;
lh_bill_to_site_use_id NUMBER;
ll_inventory_item_id NUMBER;
l_rec_cnt NUMBER:=1;
l_err_data VARCHAR2(2000);
lc_cnt NUMBER := 0;
ln_inv_item_cnt NUMBER := 0;
lc_error_msg VARCHAR2(2000);
CURSOR c_trx_headers
IS
(SELECT xxhdr.*
FROM xxxx.xxxx_trx_headers_interface xxhdr,
xxxx.xxxx_trx_lines_interface xxlines
WHERE 1 = 1
AND xxhdr.record_id = xxlines.record_id
AND xxhdr.record_status IN ('N','D','T','S')
AND xxhdr.error_msg is null
AND xxlines.interface_line_attribute1 NOT IN (SELECT
interface_line_attribute1
FROM ra_customer_trx_lines_all
WHERE 1 = 1) );
CURSOR c_trx_lines (P_record_id NUMBER)
IS
(SELECT *
FROM xxxx.xxxx_trx_lines_interface
WHERE 1 = 1
AND record_status IN ('N','D','T','S')
AND error_msg is null
AND record_id = P_record_id );
BEGIN
BEGIN
/* Assigning Global Profiles */
BEGIN
fnd_global.apps_initialize(12344, 58413, 222);
apps.mo_global.set_policy_context ('S', 455);
/*Fnd_Global.APPS_INITIALIZE (Fnd_Global.user_id,
Fnd_Global.RESP_ID,
Fnd_Global.RESP_APPL_ID);
apps.mo_global.set_policy_context ('S', Fnd_Global.org_id);*/
END;
DELETE FROM ar_trx_errors_gt;
FOR rec_hdr in c_trx_headers
LOOP /* Main Loop Start here */
BEGIN
SELECT cust_trx_type_id
INTO lh_trx_type_id
FROM ra_cust_trx_types
WHERE name = rec_hdr.cust_trx_type_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('No Data Found for - Customer TRX Type '||' : '||rec_hdr.cust_trx_type_name);
lc_error_msg := 'No Data Found for - Customer TRX Type '||' : '||rec_hdr.cust_trx_type_name ;
WHEN OTHERS THEN
dbms_output.put_line('Undefined Error for - Customer TRX Type'||' '||SQLERRM);
lc_error_msg := 'Undefined Error for - Customer TRX Type'||' '||SQLERRM ;
END;
BEGIN
SELECT
hca.cust_account_id,
hcsua.site_use_id
INTO
lh_bill_to_customer_id,
lh_bill_to_site_use_id
FROM
hz_cust_accounts hca,
hz_cust_acct_sites hcasa,
hz_cust_site_uses hcsua,
apps.hz_parties hp
WHERE 1 = 1
AND hca.cust_account_id = hcasa.cust_account_id
AND hcsua.SITE_USE_CODE = 'BILL_TO'
AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
AND hca.party_id = hp.party_id
AND hcsua.PRIMARY_FLAG = 'Y'
AND hp.party_name IN( SELECT customer_name
FROM ar_customers
WHERE customer_id = rec_hdr.orig_system_bill_customer_id); --'321 EVENTS DMCC';
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('No Data Found for - Customer Account and Customer Bill-To Site Use '||' : '||rec_hdr.orig_system_bill_customer_id);
lc_error_msg := 'No Data Found for - Customer Account and Customer Bill-To Site Use '||' : '||rec_hdr.orig_system_bill_customer_id ;
WHEN OTHERS THEN
dbms_output.put_line('Undefined Error for - Customer Account and Customer Bill-To Site Use '||' : '||rec_hdr.orig_system_bill_customer_id||' : '||SQLERRM);
lc_error_msg := 'Undefined Error for - Customer Account and Customer Bill-To Site Use '||' : '||rec_hdr.orig_system_bill_customer_id||' : '||SQLERRM ;
END;
BEGIN
SELECT batch_source_id
INTO l_batch_source_rec.batch_source_id
FROM ra_batch_sources
WHERE 1 = 1
AND name = 'MANUAL-OTHER';
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('No Data Found for - Batch Source '||' : '||'MANUAL-OTHER');
lc_error_msg := 'No Data Found for - Batch Source '||' : '||'MANUAL-OTHER' ;
WHEN OTHERS THEN
dbms_output.put_line('Undefined Error for - Batch Source '||' : '||'MANUAL-OTHER'||' : '||SQLERRM);
lc_error_msg := 'Undefined Error for - Batch Source '||' : '||'MANUAL-OTHER'||' : '||SQLERRM ;
END;
/* Get the Term Id based on Term name */
BEGIN
SELECT term_id
INTO lh_term_id
FROM RA_TERMS_TL
WHERE 1 = 1
AND name = rec_hdr.term_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('No Data Found for - Term '||' : '||rec_hdr.term_name);
lc_error_msg := 'No Data Found for - Term '||' : '||rec_hdr.term_name ;
WHEN OTHERS THEN
dbms_output.put_line('Undefined Error for - Term '||' : '||rec_hdr.term_name||' : '||SQLERRM);
lc_error_msg := 'Undefined Error for - Term '||' : '||rec_hdr.term_name||' : '||SQLERRM ;
END;
/* Assigning Header Legacy data with API Record variables */
l_trx_header_tbl(1).trx_header_id := rec_hdr.record_id;
l_trx_header_tbl(1).cust_trx_type_id := lh_trx_type_id;
l_trx_header_tbl(1).bill_to_customer_id := lh_bill_to_customer_id ;
l_trx_header_tbl(1).bill_to_site_use_id := lh_bill_to_site_use_id ;
l_trx_header_tbl(1).trx_date := rec_hdr.trx_date;
l_trx_header_tbl(1).gl_date := rec_hdr.gl_date;
l_trx_header_tbl(1).org_id := rec_hdr.org_id;
l_trx_header_tbl(1).term_id := lh_term_id;
l_trx_header_tbl(1).attribute_category := rec_hdr.attribute_category ;
l_trx_header_tbl(1).attribute1 := rec_hdr.attribute1 ;
l_trx_header_tbl(1).attribute2 := rec_hdr.attribute2 ;
l_trx_header_tbl(1).attribute3 := rec_hdr.attribute3 ;
l_trx_header_tbl(1).attribute4 := rec_hdr.attribute4 ;
l_trx_header_tbl(1).attribute5 := rec_hdr.attribute5 ;
l_trx_header_tbl(1).attribute6 := rec_hdr.attribute6 ;
l_trx_header_tbl(1).attribute7 := rec_hdr.attribute7 ;
l_trx_header_tbl(1).attribute8 := rec_hdr.attribute8 ;
l_trx_header_tbl(1).attribute9 := rec_hdr.attribute9 ;
l_trx_header_tbl(1).attribute10 := rec_hdr.attribute10 ;
l_trx_header_tbl(1).attribute11 := rec_hdr.attribute11 ;
l_trx_header_tbl(1).attribute12 := rec_hdr.attribute12 ;
l_trx_header_tbl(1).attribute13 := rec_hdr.attribute13 ;
l_trx_header_tbl(1).attribute14 := rec_hdr.attribute14 ;
l_trx_header_tbl(1).attribute15 := rec_hdr.attribute15 ;
l_trx_header_tbl(1).comments := rec_hdr.comments ;
l_trx_header_tbl(1).document_creation_date := rec_hdr.creation_date ;
dbms_output.put_line(' Before Nested Loop : '||''||l_customer_trx_id||''||SQLERRM);
/* Lines Loop Starts here */
FOR rec_line IN c_trx_lines (rec_hdr.record_id)
LOOP
IF rec_line.line_type = 'TAX' THEN
l_cnt := l_cnt + 1;
l_trx_lines_tbl(l_cnt).trx_header_id := rec_line.record_id;
l_trx_lines_tbl(l_cnt).trx_line_id := rec_hdr.record_id + rec_line.line_number; --1 ;
l_trx_lines_tbl(l_cnt).description := rec_line.description ;
l_trx_lines_tbl(l_cnt).line_number := rec_line.line_number ;
-- l_trx_lines_tbl(l_cnt).inventory_item_id := ll_inventory_item_id ;
-- l_trx_lines_tbl(l_cnt).quantity_invoiced := rec_line.quantity ;
-- l_trx_lines_tbl(l_cnt).unit_selling_price := rec_line.unit_selling_price ;
l_trx_lines_tbl(l_cnt).line_type := rec_line.line_type;
-- l_trx_lines_tbl(l_cnt).unit_standard_price := rec_line.unit_standard_price;
--l_trx_lines_tbl(l_cnt).uom_code := rec_line.uom_code;
l_trx_lines_tbl(l_cnt).amount := (rec_line.quantity * rec_line.unit_selling_price );
--l_trx_lines_tbl(l_cnt).tax_regime_code := rec_line.tax_regime_code;
--l_trx_lines_tbl(l_cnt).tax_status_code := rec_line.tax_status_code;
l_trx_lines_tbl(l_cnt).rule_start_date := rec_line.rule_start_date;
l_trx_lines_tbl(l_cnt).rule_end_date := rec_line.rule_end_date;
l_trx_lines_tbl(l_cnt).product_type := rec_line.product_type;
l_trx_lines_tbl(l_cnt).reason_code := rec_line.reason;
l_trx_lines_tbl(l_cnt).unit_standard_price := rec_line.unit_price;
l_trx_lines_tbl(l_cnt).interface_line_context := rec_line.interface_line_context ;
l_trx_lines_tbl(l_cnt).interface_line_attribute1 := rec_line.interface_line_attribute1 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute2 := rec_line.interface_line_attribute2 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute3 := rec_line.interface_line_attribute3 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute4 := rec_line.interface_line_attribute4 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute5 := rec_line.interface_line_attribute5 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute6 := rec_line.interface_line_attribute6 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute7 := rec_line.interface_line_attribute7 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute8 := rec_line.interface_line_attribute8 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute9 := rec_line.interface_line_attribute9 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute10 := rec_line.interface_line_attribute10 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute11 := rec_line.interface_line_attribute11 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute12 := rec_line.interface_line_attribute12 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute13 := rec_line.interface_line_attribute13 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute14 := rec_line.interface_line_attribute14 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute15 := rec_line.interface_line_attribute15 ;
ELSIF rec_line.line_type = 'LINE' THEN
l_cnt := l_cnt + 1;
lc_cnt := lc_cnt + 1;
dbms_output.put_line(' In Nested Loop : '||' customer trx id : '||l_customer_trx_id||' : '||SQLERRM);
BEGIN
SELECT COUNT(inventory_item_id)
INTO ln_inv_item_cnt
FROM mtl_system_items_b
WHERE 1 = 1
AND segment1 = rec_line.item_code;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ln_inv_item_cnt := 0 ;
dbms_output.put_line('Inventory Item not found: '||' Item Code : '||rec_line.item_code);
lc_error_msg := 'Inventory Item not found: '||' Item Code : '||rec_line.item_code ;
WHEN OTHERS THEN
ln_inv_item_cnt := 0 ;
dbms_output.put_line('Inventory Item not found: '||' Item Code : '||rec_line.item_code||' : '||SQLERRM);
lc_error_msg := 'Inventory Item not found: '||' Item Code : '||rec_line.item_code||' : '||SQLERRM ;
END;
IF
ln_inv_item_cnt >0 THEN
BEGIN
SELECT inventory_item_id
INTO ll_inventory_item_id
FROM mtl_system_items_b
WHERE 1 = 1
AND segment1 = rec_line.item_code
AND enabled_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
ll_inventory_item_id := NULL;
lc_error_msg := 'Inventory Item Not Enabled '||' : '||'Item Code :'||rec_line.item_code ;
dbms_output.put_line('Inventory Item Not Enabled '||' : '||'Item Code :'||rec_line.item_code);
WHEN OTHERS THEN
dbms_output.put_line('Undefined Error for - Inventory Item Code '||' : '||SQLERRM);
lc_error_msg := 'Undefined Error for - Inventory Item Code '||' : '||SQLERRM ;
END;
END IF;
l_trx_lines_tbl(l_cnt).trx_header_id := rec_line.record_id;
l_trx_lines_tbl(l_cnt).trx_line_id := rec_hdr.record_id + l_cnt;
l_trx_lines_tbl(l_cnt).description := rec_line.description ;
l_trx_lines_tbl(l_cnt).line_number := rec_line.line_number ;
l_trx_lines_tbl(l_cnt).inventory_item_id := ll_inventory_item_id ;
l_trx_lines_tbl(l_cnt).quantity_invoiced := rec_line.quantity ;
l_trx_lines_tbl(l_cnt).unit_selling_price := rec_line.unit_selling_price ;
l_trx_lines_tbl(l_cnt).line_type := rec_line.line_type;
l_trx_lines_tbl(l_cnt).unit_standard_price := rec_line.unit_standard_price;
l_trx_lines_tbl(l_cnt).uom_code := rec_line.uom_code;
l_trx_lines_tbl(l_cnt).amount := (rec_line.quantity * rec_line.unit_selling_price );
l_trx_lines_tbl(l_cnt).rule_start_date := rec_line.rule_start_date;
l_trx_lines_tbl(l_cnt).rule_end_date := rec_line.rule_end_date;
l_trx_lines_tbl(l_cnt).product_type := rec_line.product_type;
l_trx_lines_tbl(l_cnt).reason_code := rec_line.reason;
l_trx_lines_tbl(l_cnt).unit_standard_price := rec_line.unit_price;
l_trx_lines_tbl(l_cnt).interface_line_context := rec_line.interface_line_context ;
l_trx_lines_tbl(l_cnt).interface_line_attribute1 := rec_line.interface_line_attribute1 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute2 := rec_line.interface_line_attribute2 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute3 := rec_line.interface_line_attribute3 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute4 := rec_line.interface_line_attribute4 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute5 := rec_line.interface_line_attribute5 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute6 := rec_line.interface_line_attribute6 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute7 := rec_line.interface_line_attribute7 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute8 := rec_line.interface_line_attribute8 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute9 := rec_line.interface_line_attribute9 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute10 := rec_line.interface_line_attribute10 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute11 := rec_line.interface_line_attribute11 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute12 := rec_line.interface_line_attribute12 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute13 := rec_line.interface_line_attribute13 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute14 := rec_line.interface_line_attribute14 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute15 := rec_line.interface_line_attribute15 ;
l_trx_lines_tbl(l_cnt).taxable_flag := 'N';
/* TAX Lines Starts here */
l_cntt := l_cnt + 1 ;
dbms_output.put_line(' Start Tax Lines Count : '||' Lines Count : '||l_cnt||' Tax Lines Count : '||l_cntt||' lc_cnt : '||lc_cnt);
l_trx_lines_tbl(l_cntt).trx_line_id := rec_hdr.record_id + l_cntt;
l_trx_lines_tbl(l_cntt).trx_header_id := rec_line.record_id;
l_trx_lines_tbl(l_cntt).line_number := rec_line.line_number ;
l_trx_lines_tbl(l_cntt).description := 'Test Tax';
l_trx_lines_tbl(l_cntt).amount := 100;
l_trx_lines_tbl(l_cntt).line_type := 'TAX';
l_trx_lines_tbl(l_cntt).tax_rate_code := 'AE_VAT_AR_09';
l_trx_lines_tbl(l_cntt).tax_status_code := 'AE_VAT_STANDARD';
l_trx_lines_tbl(l_cntt).link_to_trx_line_id := rec_hdr.record_id + l_cnt;
l_trx_lines_tbl(l_cntt).tax_regime_code := 'AE_VAT';
l_trx_lines_tbl(l_cntt).tax := 'AE_VAT';
-- l_trx_lines_tbl(2).taxable_flag := 'Y';
dbms_output.put_line(' End Tax Lines Count : '||' Lines Count : '||l_cnt||' Tax Lines Count : '||l_cntt||''||SQLERRM);
l_cnt := l_cnt + 1;
/* TAX Lines Ends here */
END IF;
END LOOP; /* Lines Loop Ends here */
dbms_output.put_line(' After Nested loop end : '||' '||l_cnt);
/* API Calling to Create AR Single Invoices with Multiple Lines and Tax Line for each Invoice Line */
AR_INVOICE_API_PUB.create_single_invoice(
p_api_version => 1.0,
p_batch_source_rec => l_batch_source_rec,
p_trx_header_tbl => l_trx_header_tbl,
p_trx_lines_tbl => l_trx_lines_tbl,
p_trx_dist_tbl => l_trx_dist_tbl,
p_trx_salescredits_tbl => l_trx_salescredits_tbl,
p_trx_contingencies_tbl => l_trx_contingencies_tbl_type,
x_customer_trx_id => l_customer_trx_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
dbms_output.put_line('********************************************');
dbms_output.put_line(' Am in API Calling End : '||'Customer Trx ID : '||l_customer_trx_id||' Oracle Message : '||SQLERRM||' API MSG : '||l_msg_data);
dbms_output.put_line(' API Return Status : '||''||l_return_status||' : '||SQLERRM);
dbms_output.put_line(' Record Count before addition : '||''||l_cnt);
l_err_data := l_msg_data||''||SQLERRM;
IF l_return_status = 'S' AND l_customer_trx_id is not null THEN
UPDATE XXIMSF_TRX_HEADERS_INTERFACE
SET record_status = 'S', error_msg = 'Records Inserted into Base Tables '||' = Created Customer TRX ID ='||l_customer_trx_id
WHERE record_id = rec_hdr.record_id;
UPDATE XXXX_TRX_LINES_INTERFACE
SET record_status = 'S', error_msg = 'Records Inserted into Base Tables '||' = Created Customer TRX ID ='||l_customer_trx_id
WHERE record_id = rec_hdr.record_id;
ELSE
UPDATE XXXX_TRX_HEADERS_INTERFACE
SET record_status = 'E', error_msg = 'Records Not Inserted into Base Tables'||' Validation Errors '||lc_error_msg||' '||l_err_data
WHERE record_id = rec_hdr.record_id;
UPDATE XXXX_TRX_LINES_INTERFACE
SET record_status = 'E', error_msg = 'Records Not Inserted into Base Tables'||' Validation Errors '||lc_error_msg||' '||l_err_data
WHERE record_id = rec_hdr.record_id;
END IF;
END LOOP; /* Main Loop Ends here */
commit;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('No Data Found for - in Main Loop '||' '||SQLERRM);
WHEN OTHERS THEN
dbms_output.put_line('Undefined Error - in Main Loop '||''||SQLERRM);
END;
END;
=============== Thanks for referring my blog, keep view my blog will get latest updates. ========================
Staging Table Creation:
/*------------------------------------------------------------
Header Columns:
------------------------------------------------------------*/
CREATE TABLE XXXX.XXXX_TRX_HEADERS_INTERFACE
/* +====================================================================+
| |
| |
|$Id: $ |
| |
|Description : XXXX_TRX_HEADERS_INTERFACE |
| This table will have AR Invoice Header Legacy |
| Data |
| |
|Change History: |
|--------------- |
|Version Date Author Remarks |
|------- ---------- -------------- --------------------------------|
|1.0 XX XX Initial Version |
| |
+====================================================================+ */
(RECORD_ID NUMBER
,CREATION_DATE DATE
,RECORD_STATUS VARCHAR2(1)
,ERROR_MSG VARCHAR2(2000)
,BATCH_ID NUMBER
,BATCH_SOURCE VARCHAR2(100)
,TRX_COMMENTS VARCHAR2(2000)
,ORIG_SYSTEM_BILL_CUSTOMER_ID NUMBER
,ORIG_SYSTEM_BILL_ADDRESS_ID NUMBER
,ORIG_SYSTEM_SHIP_CUSTOMER_ID NUMBER
,ORIG_SYSTEM_SHIP_ADDRESS_ID NUMBER
,CUSTOMER_BANK_ACCOUNT_NAME VARCHAR2(80)
,CUST_TRX_TYPE_NAME VARCHAR2(20)
,TRX_NUMBER VARCHAR2(20)
,LAST_UPDATE_DATE DATE
,CUSTOMER_TRX_ID NUMBER
,DOCUMENT_NUMBER NUMBER
,TRX_DATE DATE
,GL_DATE DATE
,ORG_ID NUMBER
,TERM_NAME VARCHAR2(15)
,INVOICING_RULE_NAME VARCHAR2(30)
,ACCOUNTING_RULE_DURATION NUMBER
,PREVIOUSE_CUST_TRX_ID NUMBER
,PRIMARY_SALESREP_NUMBER VARCHAR2(30)
,ATTRIBUTE_CATEGORY VARCHAR2(30)
,ATTRIBUTE1 VARCHAR2(150)
,ATTRIBUTE2 VARCHAR2(150)
,ATTRIBUTE3 VARCHAR2(150)
,ATTRIBUTE4 VARCHAR2(150)
,ATTRIBUTE5 VARCHAR2(150)
,ATTRIBUTE6 VARCHAR2(150)
,ATTRIBUTE7 VARCHAR2(150)
,ATTRIBUTE8 VARCHAR2(150)
,ATTRIBUTE9 VARCHAR2(150)
,ATTRIBUTE10 VARCHAR2(150)
,ATTRIBUTE11 VARCHAR2(150)
,ATTRIBUTE12 VARCHAR2(150)
,ATTRIBUTE13 VARCHAR2(150)
,ATTRIBUTE14 VARCHAR2(150)
,ATTRIBUTE15 VARCHAR2(150)
,COMMENTS VARCHAR2(240)
,REASON VARCHAR2(2000)
);
/
CREATE OR REPLACE PUBLIC SYNONYM XXXX_TRX_HEADERS_INTERFACE
FOR XXIMSF.XXXX_TRX_HEADERS_INTERFACE;
/
/*------------------------------------------------------------
Line Columns:
------------------------------------------------------------*/
CREATE TABLE XXXX.XXXX_TRX_LINES_INTERFACE
/* +====================================================================+
| |
| |
|$Id: $ |
| |
|Description : XXXX_TRX_LINES_INTERFACE |
| This table will have AR Invoice Lines Legacy |
| Data |
| |
|Change History: |
|--------------- |
|Version Date Author Remarks |
|------- ---------- -------------- --------------------------------|
|1.0 XX XX Initial Version |
| |
+====================================================================+ */
(RECORD_ID NUMBER
,LINE_NUMBER NUMBER
,RECORD_STATUS VARCHAR2(1)
,ERROR_MSG VARCHAR2(2000)
,LINE_TYPE VARCHAR2(20)
,DESCRIPTION VARCHAR2(240)
,QUANTITY NUMBER
,UNIT_SELLING_PRICE NUMBER
,UNIT_STANDARD_PRICE NUMBER
,UOM_CODE VARCHAR2(3)
,UOM_NAME VARCHAR2(25)
,CURRENCY_CODE VARCHAR2(15)
,AMOUNT NUMBER
,CONVERSION_TYPE VARCHAR2(30)
,CONVERSION_DATE DATE
,CONVERSION_RATE NUMBER
,INTERFACE_LINE_CONTEXT VARCHAR2(30)
,INTERFACE_LINE_ATTRIBUTE1 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE2 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE3 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE4 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE5 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE6 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE7 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE8 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE9 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE10 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE11 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE12 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE13 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE14 VARCHAR2(150)
,INTERFACE_LINE_ATTRIBUTE15 VARCHAR2(150)
,RULE_START_DATE DATE
,RULE_END_DATE DATE
,LINK_TO_LINE_ID NUMBER
,CREDIT_METHOD_FOR_ACCT_RULE VARCHAR2(30)
,CREDIT_METHOD_FOR_INSTALLMENTS VARCHAR2(30)
,MEMO_LINE_NAME VARCHAR2(50)
,COMMENTS VARCHAR2(240)
,COGS_AMOUNT NUMBER
,COGS_PERCENTAGE NUMBER
,COGS_GL_FLAG VARCHAR2(1)
,TAX_REGIME_CODE VARCHAR2(30)
,TAX_TYPE VARCHAR2(30)
,TAX_JURISDICATION VARCHAR2(50)
,TAX_REG_STATUS VARCHAR2(50)
,TAX_RATE_CODES VARCHAR2(50)
,CREDIT_ALLOCATION_RULE VARCHAR2(100)
,UNIT_PRICE NUMBER
,UOM VARCHAR2(30)
,SUPPLY_DATE DATE
,TAX_AMOUNT NUMBER
,TAX_GL_CODE_COMBINATION VARCHAR2(500)
,REV_GL_CODE_COMBINATION VARCHAR2(500)
,TAXABLE_AMOUNT NUMBER
,REASON VARCHAR2(2000)
,COGS_TAX_REG_STATUS VARCHAR2(30)
,COGS_TAX_RATE_CODES VARCHAR2(50)
,COGS_TAX_AMOUNT NUMBER
,ORA_TRX_DATE DATE
,ORA_GL_DATE DATE
,ORA_CREATION_DATE DATE
,ITEM_CODE VARCHAR2(240)
,TAX_STATUS_CODE VARCHAR2(240)
,TAX_ONLY_ORIGINAL_INVOICE VARCHAR2(240)
,TAX_ONLY_INVOICE_DATE DATE
,TAX_ONLY_INVOICE_AMOUNT NUMBER
,PRODUCT_TYPE VARCHAR2(240)
);
/
CREATE OR REPLACE PUBLIC SYNONYM XXXX_TRX_LINES_INTERFACE
FOR XXIMSF.XXXX_TRX_LINES_INTERFACE;
/
Here above tables purpose of creating it will hold the data till validation and periodically data will be flush out from above tables.
Now creating the AR Invoices with Multiple lines and separate Tax Lines:
SET SERVEROUTPUT ON;
DECLARE
/* Local Variables declaration */
/* API Output Variables */
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
/* Record Type Variables */
l_batch_source_rec ar_invoice_api_pub.batch_source_rec_type;
l_trx_header_tbl ar_invoice_api_pub.trx_header_tbl_type;
l_trx_lines_tbl ar_invoice_api_pub.trx_line_tbl_type;
l_trx_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type;
l_trx_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type;
l_trx_contingencies_tbl_type ar_invoice_api_pub.trx_contingencies_tbl_type;
/* Local Variables */
l_batch_id NUMBER;
l_cnt NUMBER := 0;
l_cntt NUMBER:= 1;
v_sr_id VARCHAR2(240);
v_sr_number VARCHAR2(240);
v_tax_regime_code VARCHAR2(240);
v_tax_class_code VARCHAR2(240);
ln_CUSTOMER_ID NUMBER;
l_receipt_id NUMBER;
lh_trx_type_id NUMBER;
l_customer_trx_id NUMBER;
l_user_id NUMBER;
l_resp_id NUMBER;
l_resp_appl_id NUMBER;
ln_line_no NUMBER;
lh_term_id NUMBER;
lh_bill_to_customer_id NUMBER;
lh_bill_to_site_use_id NUMBER;
ll_inventory_item_id NUMBER;
l_rec_cnt NUMBER:=1;
l_err_data VARCHAR2(2000);
lc_cnt NUMBER := 0;
ln_inv_item_cnt NUMBER := 0;
lc_error_msg VARCHAR2(2000);
CURSOR c_trx_headers
IS
(SELECT xxhdr.*
FROM xxxx.xxxx_trx_headers_interface xxhdr,
xxxx.xxxx_trx_lines_interface xxlines
WHERE 1 = 1
AND xxhdr.record_id = xxlines.record_id
AND xxhdr.record_status IN ('N','D','T','S')
AND xxhdr.error_msg is null
AND xxlines.interface_line_attribute1 NOT IN (SELECT
interface_line_attribute1
FROM ra_customer_trx_lines_all
WHERE 1 = 1) );
CURSOR c_trx_lines (P_record_id NUMBER)
IS
(SELECT *
FROM xxxx.xxxx_trx_lines_interface
WHERE 1 = 1
AND record_status IN ('N','D','T','S')
AND error_msg is null
AND record_id = P_record_id );
BEGIN
BEGIN
/* Assigning Global Profiles */
BEGIN
fnd_global.apps_initialize(12344, 58413, 222);
apps.mo_global.set_policy_context ('S', 455);
/*Fnd_Global.APPS_INITIALIZE (Fnd_Global.user_id,
Fnd_Global.RESP_ID,
Fnd_Global.RESP_APPL_ID);
apps.mo_global.set_policy_context ('S', Fnd_Global.org_id);*/
END;
DELETE FROM ar_trx_errors_gt;
FOR rec_hdr in c_trx_headers
LOOP /* Main Loop Start here */
BEGIN
SELECT cust_trx_type_id
INTO lh_trx_type_id
FROM ra_cust_trx_types
WHERE name = rec_hdr.cust_trx_type_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('No Data Found for - Customer TRX Type '||' : '||rec_hdr.cust_trx_type_name);
lc_error_msg := 'No Data Found for - Customer TRX Type '||' : '||rec_hdr.cust_trx_type_name ;
WHEN OTHERS THEN
dbms_output.put_line('Undefined Error for - Customer TRX Type'||' '||SQLERRM);
lc_error_msg := 'Undefined Error for - Customer TRX Type'||' '||SQLERRM ;
END;
BEGIN
SELECT
hca.cust_account_id,
hcsua.site_use_id
INTO
lh_bill_to_customer_id,
lh_bill_to_site_use_id
FROM
hz_cust_accounts hca,
hz_cust_acct_sites hcasa,
hz_cust_site_uses hcsua,
apps.hz_parties hp
WHERE 1 = 1
AND hca.cust_account_id = hcasa.cust_account_id
AND hcsua.SITE_USE_CODE = 'BILL_TO'
AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
AND hca.party_id = hp.party_id
AND hcsua.PRIMARY_FLAG = 'Y'
AND hp.party_name IN( SELECT customer_name
FROM ar_customers
WHERE customer_id = rec_hdr.orig_system_bill_customer_id); --'321 EVENTS DMCC';
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('No Data Found for - Customer Account and Customer Bill-To Site Use '||' : '||rec_hdr.orig_system_bill_customer_id);
lc_error_msg := 'No Data Found for - Customer Account and Customer Bill-To Site Use '||' : '||rec_hdr.orig_system_bill_customer_id ;
WHEN OTHERS THEN
dbms_output.put_line('Undefined Error for - Customer Account and Customer Bill-To Site Use '||' : '||rec_hdr.orig_system_bill_customer_id||' : '||SQLERRM);
lc_error_msg := 'Undefined Error for - Customer Account and Customer Bill-To Site Use '||' : '||rec_hdr.orig_system_bill_customer_id||' : '||SQLERRM ;
END;
BEGIN
SELECT batch_source_id
INTO l_batch_source_rec.batch_source_id
FROM ra_batch_sources
WHERE 1 = 1
AND name = 'MANUAL-OTHER';
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('No Data Found for - Batch Source '||' : '||'MANUAL-OTHER');
lc_error_msg := 'No Data Found for - Batch Source '||' : '||'MANUAL-OTHER' ;
WHEN OTHERS THEN
dbms_output.put_line('Undefined Error for - Batch Source '||' : '||'MANUAL-OTHER'||' : '||SQLERRM);
lc_error_msg := 'Undefined Error for - Batch Source '||' : '||'MANUAL-OTHER'||' : '||SQLERRM ;
END;
/* Get the Term Id based on Term name */
BEGIN
SELECT term_id
INTO lh_term_id
FROM RA_TERMS_TL
WHERE 1 = 1
AND name = rec_hdr.term_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('No Data Found for - Term '||' : '||rec_hdr.term_name);
lc_error_msg := 'No Data Found for - Term '||' : '||rec_hdr.term_name ;
WHEN OTHERS THEN
dbms_output.put_line('Undefined Error for - Term '||' : '||rec_hdr.term_name||' : '||SQLERRM);
lc_error_msg := 'Undefined Error for - Term '||' : '||rec_hdr.term_name||' : '||SQLERRM ;
END;
/* Assigning Header Legacy data with API Record variables */
l_trx_header_tbl(1).trx_header_id := rec_hdr.record_id;
l_trx_header_tbl(1).cust_trx_type_id := lh_trx_type_id;
l_trx_header_tbl(1).bill_to_customer_id := lh_bill_to_customer_id ;
l_trx_header_tbl(1).bill_to_site_use_id := lh_bill_to_site_use_id ;
l_trx_header_tbl(1).trx_date := rec_hdr.trx_date;
l_trx_header_tbl(1).gl_date := rec_hdr.gl_date;
l_trx_header_tbl(1).org_id := rec_hdr.org_id;
l_trx_header_tbl(1).term_id := lh_term_id;
l_trx_header_tbl(1).attribute_category := rec_hdr.attribute_category ;
l_trx_header_tbl(1).attribute1 := rec_hdr.attribute1 ;
l_trx_header_tbl(1).attribute2 := rec_hdr.attribute2 ;
l_trx_header_tbl(1).attribute3 := rec_hdr.attribute3 ;
l_trx_header_tbl(1).attribute4 := rec_hdr.attribute4 ;
l_trx_header_tbl(1).attribute5 := rec_hdr.attribute5 ;
l_trx_header_tbl(1).attribute6 := rec_hdr.attribute6 ;
l_trx_header_tbl(1).attribute7 := rec_hdr.attribute7 ;
l_trx_header_tbl(1).attribute8 := rec_hdr.attribute8 ;
l_trx_header_tbl(1).attribute9 := rec_hdr.attribute9 ;
l_trx_header_tbl(1).attribute10 := rec_hdr.attribute10 ;
l_trx_header_tbl(1).attribute11 := rec_hdr.attribute11 ;
l_trx_header_tbl(1).attribute12 := rec_hdr.attribute12 ;
l_trx_header_tbl(1).attribute13 := rec_hdr.attribute13 ;
l_trx_header_tbl(1).attribute14 := rec_hdr.attribute14 ;
l_trx_header_tbl(1).attribute15 := rec_hdr.attribute15 ;
l_trx_header_tbl(1).comments := rec_hdr.comments ;
l_trx_header_tbl(1).document_creation_date := rec_hdr.creation_date ;
dbms_output.put_line(' Before Nested Loop : '||''||l_customer_trx_id||''||SQLERRM);
/* Lines Loop Starts here */
FOR rec_line IN c_trx_lines (rec_hdr.record_id)
LOOP
IF rec_line.line_type = 'TAX' THEN
l_cnt := l_cnt + 1;
l_trx_lines_tbl(l_cnt).trx_header_id := rec_line.record_id;
l_trx_lines_tbl(l_cnt).trx_line_id := rec_hdr.record_id + rec_line.line_number; --1 ;
l_trx_lines_tbl(l_cnt).description := rec_line.description ;
l_trx_lines_tbl(l_cnt).line_number := rec_line.line_number ;
-- l_trx_lines_tbl(l_cnt).inventory_item_id := ll_inventory_item_id ;
-- l_trx_lines_tbl(l_cnt).quantity_invoiced := rec_line.quantity ;
-- l_trx_lines_tbl(l_cnt).unit_selling_price := rec_line.unit_selling_price ;
l_trx_lines_tbl(l_cnt).line_type := rec_line.line_type;
-- l_trx_lines_tbl(l_cnt).unit_standard_price := rec_line.unit_standard_price;
--l_trx_lines_tbl(l_cnt).uom_code := rec_line.uom_code;
l_trx_lines_tbl(l_cnt).amount := (rec_line.quantity * rec_line.unit_selling_price );
--l_trx_lines_tbl(l_cnt).tax_regime_code := rec_line.tax_regime_code;
--l_trx_lines_tbl(l_cnt).tax_status_code := rec_line.tax_status_code;
l_trx_lines_tbl(l_cnt).rule_start_date := rec_line.rule_start_date;
l_trx_lines_tbl(l_cnt).rule_end_date := rec_line.rule_end_date;
l_trx_lines_tbl(l_cnt).product_type := rec_line.product_type;
l_trx_lines_tbl(l_cnt).reason_code := rec_line.reason;
l_trx_lines_tbl(l_cnt).unit_standard_price := rec_line.unit_price;
l_trx_lines_tbl(l_cnt).interface_line_context := rec_line.interface_line_context ;
l_trx_lines_tbl(l_cnt).interface_line_attribute1 := rec_line.interface_line_attribute1 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute2 := rec_line.interface_line_attribute2 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute3 := rec_line.interface_line_attribute3 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute4 := rec_line.interface_line_attribute4 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute5 := rec_line.interface_line_attribute5 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute6 := rec_line.interface_line_attribute6 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute7 := rec_line.interface_line_attribute7 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute8 := rec_line.interface_line_attribute8 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute9 := rec_line.interface_line_attribute9 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute10 := rec_line.interface_line_attribute10 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute11 := rec_line.interface_line_attribute11 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute12 := rec_line.interface_line_attribute12 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute13 := rec_line.interface_line_attribute13 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute14 := rec_line.interface_line_attribute14 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute15 := rec_line.interface_line_attribute15 ;
ELSIF rec_line.line_type = 'LINE' THEN
l_cnt := l_cnt + 1;
lc_cnt := lc_cnt + 1;
dbms_output.put_line(' In Nested Loop : '||' customer trx id : '||l_customer_trx_id||' : '||SQLERRM);
BEGIN
SELECT COUNT(inventory_item_id)
INTO ln_inv_item_cnt
FROM mtl_system_items_b
WHERE 1 = 1
AND segment1 = rec_line.item_code;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ln_inv_item_cnt := 0 ;
dbms_output.put_line('Inventory Item not found: '||' Item Code : '||rec_line.item_code);
lc_error_msg := 'Inventory Item not found: '||' Item Code : '||rec_line.item_code ;
WHEN OTHERS THEN
ln_inv_item_cnt := 0 ;
dbms_output.put_line('Inventory Item not found: '||' Item Code : '||rec_line.item_code||' : '||SQLERRM);
lc_error_msg := 'Inventory Item not found: '||' Item Code : '||rec_line.item_code||' : '||SQLERRM ;
END;
IF
ln_inv_item_cnt >0 THEN
BEGIN
SELECT inventory_item_id
INTO ll_inventory_item_id
FROM mtl_system_items_b
WHERE 1 = 1
AND segment1 = rec_line.item_code
AND enabled_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
ll_inventory_item_id := NULL;
lc_error_msg := 'Inventory Item Not Enabled '||' : '||'Item Code :'||rec_line.item_code ;
dbms_output.put_line('Inventory Item Not Enabled '||' : '||'Item Code :'||rec_line.item_code);
WHEN OTHERS THEN
dbms_output.put_line('Undefined Error for - Inventory Item Code '||' : '||SQLERRM);
lc_error_msg := 'Undefined Error for - Inventory Item Code '||' : '||SQLERRM ;
END;
END IF;
l_trx_lines_tbl(l_cnt).trx_header_id := rec_line.record_id;
l_trx_lines_tbl(l_cnt).trx_line_id := rec_hdr.record_id + l_cnt;
l_trx_lines_tbl(l_cnt).description := rec_line.description ;
l_trx_lines_tbl(l_cnt).line_number := rec_line.line_number ;
l_trx_lines_tbl(l_cnt).inventory_item_id := ll_inventory_item_id ;
l_trx_lines_tbl(l_cnt).quantity_invoiced := rec_line.quantity ;
l_trx_lines_tbl(l_cnt).unit_selling_price := rec_line.unit_selling_price ;
l_trx_lines_tbl(l_cnt).line_type := rec_line.line_type;
l_trx_lines_tbl(l_cnt).unit_standard_price := rec_line.unit_standard_price;
l_trx_lines_tbl(l_cnt).uom_code := rec_line.uom_code;
l_trx_lines_tbl(l_cnt).amount := (rec_line.quantity * rec_line.unit_selling_price );
l_trx_lines_tbl(l_cnt).rule_start_date := rec_line.rule_start_date;
l_trx_lines_tbl(l_cnt).rule_end_date := rec_line.rule_end_date;
l_trx_lines_tbl(l_cnt).product_type := rec_line.product_type;
l_trx_lines_tbl(l_cnt).reason_code := rec_line.reason;
l_trx_lines_tbl(l_cnt).unit_standard_price := rec_line.unit_price;
l_trx_lines_tbl(l_cnt).interface_line_context := rec_line.interface_line_context ;
l_trx_lines_tbl(l_cnt).interface_line_attribute1 := rec_line.interface_line_attribute1 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute2 := rec_line.interface_line_attribute2 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute3 := rec_line.interface_line_attribute3 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute4 := rec_line.interface_line_attribute4 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute5 := rec_line.interface_line_attribute5 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute6 := rec_line.interface_line_attribute6 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute7 := rec_line.interface_line_attribute7 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute8 := rec_line.interface_line_attribute8 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute9 := rec_line.interface_line_attribute9 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute10 := rec_line.interface_line_attribute10 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute11 := rec_line.interface_line_attribute11 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute12 := rec_line.interface_line_attribute12 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute13 := rec_line.interface_line_attribute13 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute14 := rec_line.interface_line_attribute14 ;
l_trx_lines_tbl(l_cnt).interface_line_attribute15 := rec_line.interface_line_attribute15 ;
l_trx_lines_tbl(l_cnt).taxable_flag := 'N';
/* TAX Lines Starts here */
l_cntt := l_cnt + 1 ;
dbms_output.put_line(' Start Tax Lines Count : '||' Lines Count : '||l_cnt||' Tax Lines Count : '||l_cntt||' lc_cnt : '||lc_cnt);
l_trx_lines_tbl(l_cntt).trx_line_id := rec_hdr.record_id + l_cntt;
l_trx_lines_tbl(l_cntt).trx_header_id := rec_line.record_id;
l_trx_lines_tbl(l_cntt).line_number := rec_line.line_number ;
l_trx_lines_tbl(l_cntt).description := 'Test Tax';
l_trx_lines_tbl(l_cntt).amount := 100;
l_trx_lines_tbl(l_cntt).line_type := 'TAX';
l_trx_lines_tbl(l_cntt).tax_rate_code := 'AE_VAT_AR_09';
l_trx_lines_tbl(l_cntt).tax_status_code := 'AE_VAT_STANDARD';
l_trx_lines_tbl(l_cntt).link_to_trx_line_id := rec_hdr.record_id + l_cnt;
l_trx_lines_tbl(l_cntt).tax_regime_code := 'AE_VAT';
l_trx_lines_tbl(l_cntt).tax := 'AE_VAT';
-- l_trx_lines_tbl(2).taxable_flag := 'Y';
dbms_output.put_line(' End Tax Lines Count : '||' Lines Count : '||l_cnt||' Tax Lines Count : '||l_cntt||''||SQLERRM);
l_cnt := l_cnt + 1;
/* TAX Lines Ends here */
END IF;
END LOOP; /* Lines Loop Ends here */
dbms_output.put_line(' After Nested loop end : '||' '||l_cnt);
/* API Calling to Create AR Single Invoices with Multiple Lines and Tax Line for each Invoice Line */
AR_INVOICE_API_PUB.create_single_invoice(
p_api_version => 1.0,
p_batch_source_rec => l_batch_source_rec,
p_trx_header_tbl => l_trx_header_tbl,
p_trx_lines_tbl => l_trx_lines_tbl,
p_trx_dist_tbl => l_trx_dist_tbl,
p_trx_salescredits_tbl => l_trx_salescredits_tbl,
p_trx_contingencies_tbl => l_trx_contingencies_tbl_type,
x_customer_trx_id => l_customer_trx_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
dbms_output.put_line('********************************************');
dbms_output.put_line(' Am in API Calling End : '||'Customer Trx ID : '||l_customer_trx_id||' Oracle Message : '||SQLERRM||' API MSG : '||l_msg_data);
dbms_output.put_line(' API Return Status : '||''||l_return_status||' : '||SQLERRM);
dbms_output.put_line(' Record Count before addition : '||''||l_cnt);
l_err_data := l_msg_data||''||SQLERRM;
IF l_return_status = 'S' AND l_customer_trx_id is not null THEN
UPDATE XXIMSF_TRX_HEADERS_INTERFACE
SET record_status = 'S', error_msg = 'Records Inserted into Base Tables '||' = Created Customer TRX ID ='||l_customer_trx_id
WHERE record_id = rec_hdr.record_id;
UPDATE XXXX_TRX_LINES_INTERFACE
SET record_status = 'S', error_msg = 'Records Inserted into Base Tables '||' = Created Customer TRX ID ='||l_customer_trx_id
WHERE record_id = rec_hdr.record_id;
ELSE
UPDATE XXXX_TRX_HEADERS_INTERFACE
SET record_status = 'E', error_msg = 'Records Not Inserted into Base Tables'||' Validation Errors '||lc_error_msg||' '||l_err_data
WHERE record_id = rec_hdr.record_id;
UPDATE XXXX_TRX_LINES_INTERFACE
SET record_status = 'E', error_msg = 'Records Not Inserted into Base Tables'||' Validation Errors '||lc_error_msg||' '||l_err_data
WHERE record_id = rec_hdr.record_id;
END IF;
END LOOP; /* Main Loop Ends here */
commit;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('No Data Found for - in Main Loop '||' '||SQLERRM);
WHEN OTHERS THEN
dbms_output.put_line('Undefined Error - in Main Loop '||''||SQLERRM);
END;
END;
=============== Thanks for referring my blog, keep view my blog will get latest updates. ========================
No comments:
Post a Comment