CREATE OR REPLACE PACKAGE APPS.AR_INVOICE_CONV_PKG AS
PROCEDURE MAIN_PROC (ERRBUF OUT VARCHAR,RETCODE OUT NUMBER);
END;
/
CREATE OR REPLACE PACKAGE BODY APPS.AR_INVOICE_CONV_PKG AS
PROCEDURE MAIN_PROC (ERRBUF OUT VARCHAR,RETCODE OUT NUMBER)
IS
CURSOR c1 IS
SELECT * FROM ZN_AR_INVOICE_STG WHERE RECORD_STATUS='NEW';
/*SELECT *
FROM ZN_AR_INVOICE_STG
WHERE record_status = 'NEW'
and RECORD_NUM between 6812 and 9000; */
x_err_msg VARCHAR2(2000):=NULL;
p_g_term_name varchar2(100);
x_customer_id NUMBER(15);
p_g_cust_account_id VARCHAR2(240);
p_g_cust_acct_site_id VARCHAR2(240);
p_g_cust_acct_site_id1 VARCHAR2(240);
p_g_address_id NUMBER(15);
p_g_address_id1 NUMBER(15);
x_debug_point NUMBER(10):=0;
x_name VARCHAR2(50);
p_g_cust_trx_type_id NUMBER(15);
x_application_id NUMBER(15);
p_g_currency_code VARCHAR2(20);
x_disable_date DATE;
p_g_uom_name VARCHAR2(25);
p_g_memo_line_id NUMBER(15);
p_record_status VARCHAR2(15);
p_g_term_id NUMBER(15);
x_meaning VARCHAR2(240);
x_lookup_code VARCHAR2(240);
x_func_curr VARCHAR2(30);
p_g_conversion_type VARCHAR2(30);
p_g_conversion_rate NUMBER;
p_set_of_books_id NUMBER(15);
p_org_id NUMBER(15);
x_org_id NUMBER(15);
x_cust_account_id NUMBER;
x_cust_acct_site_id NUMBER;
x_primary_salesrep_id NUMBER := -3;
x_sales_credit_type_id NUMBER := 1;
x_sales_credit_split NUMBER := 100;
l_DOC_SEQUENCE_ID NUMBER:=0;
BEGIN
SELECT FND_PROFILE.VALUE('org_id') INTO p_org_id FROM DUAL;
-- SELECT FND_PROFILE.VALUE('GL_SET_OF_BKS_ID') INTO p_set_of_books_id FROM DUAL;
FOR vc1 IN c1 LOOP
BEGIN
-- BEGIN
-- select DOC_SEQUENCE_ID
-- INTO l_DOC_SEQUENCE_ID
-- from fnd_doc_sequence_assignments--DOC_SEQUENCE_ID
-- where category_code = vc1.CUST_TRX_TYPE--'DIST INVOICE'
-- and END_DATE IS NULL;
-- END;
p_record_status := vc1.record_status;
-----======validation == Check if the Operating Unit is valid====-----
-------------------------------------------------------------------------------
x_debug_point := 1;
IF vc1.OPERATING_UNIT IS NULL THEN
p_record_status:='FAIL';
x_err_msg := x_err_msg || '\' ||'Operating unit cannot be NULL';
ELSE -- vc1.OPERATING_UNIT IS NOT NULL THEN
BEGIN
SELECT organization_id , SET_OF_BOOKS_ID
INTO x_org_id, p_set_of_books_id
FROM hr_operating_units
-- WHERE UPPER(NAME) = UPPER(sit_rec.OPERATING_UNIT);
WHERE NAME = vc1.OPERATING_UNIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_record_status:='FAIL';
x_err_msg := x_err_msg || '\' ||'Operating Unit does not exist';
fnd_file.put_line (fnd_file.LOG, x_err_msg);
WHEN OTHERS THEN
p_record_status:='FAIL';
x_err_msg := x_err_msg || '\' ||'Operating Unit Validation failed';
fnd_file.put_line (fnd_file.LOG, x_err_msg);
END;
END IF;
-----======validation == Check if the Customer name is valid====-----
-------------------------------------------------------------------------------
BEGIN
x_debug_point := 3; --this is 3rd column validation of the staging table
IF vc1.BILL_TO_CUSTOMER IS NOT NULL THEN
BEGIN
SELECT a.party_id
INTO x_customer_id
FROM hz_parties a,
hz_cust_accounts_all b
WHERE UPPER((RTRIM(a.party_name))) =UPPER((RTRIM(vc1.BILL_TO_CUSTOMER)))
and a.party_id=b.party_id
--AND ( PROGRAM_APPLICATION_ID=222 OR APPLICATION_ID in (660,222) OR
-- (PROGRAM_APPLICATION_ID IS NULL AND APPLICATION_ID IS NULL ))
-- AND PARTY_TYPE='ORGANIZATION'----Added for Testing
AND a.status='A'
and rownum = 1;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Success : at column: ' ||x_debug_point); --##
EXCEPTION
/*************************************************************************************************************************
WHEN TOO_MANY_ROWS THEN
--FND_FILE.PUT_LINE(FND_FILE.LOG,'Success : at column: ' ||x_debug_point);
xxar_cust_number:= vc1.customer_number;
IF xxar_cust_number IS NULL THEN
x_err_msg := x_err_msg ||'customer number should be given as too many customers have the same name';
ELSE
SELECT party_id
INTO x_customer_id
FROM hz_parties
WHERE UPPER(party_name) = UPPER(vc1.customer_name)
AND party_number = vc1.customer_number
AND status='A';
SELECT cust_acct_site_id, cust_account_id
INTO p_g_cust_acct_site_id, p_g_cust_account_id
FROM hz_cust_acct_sites_all hcasa
WHERE hcasa.orig_system_reference = vc1.customer_number
AND hcasa.status = 'A'
AND NVL(hcasa.bill_to_flag,'N') <> 'N'
AND org_id=p_org_id; --fnd_profile.value('org_id');
FND_FILE.PUT_LINE(FND_FILE.LOG,'Success : at column: ' ||x_debug_point);
END IF;
*************************************************************************************************************************/
WHEN NO_DATA_FOUND THEN
p_record_status:='FAIL';
x_err_msg := x_err_msg || 'Customer name provided does not exist in Oracle eBS. ' ;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error is :'||x_err_msg);
WHEN OTHERS THEN
p_record_status:='FAIL';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||SQLERRM);
END;
ELSE
p_record_status:='FAIL';
x_err_msg := x_err_msg|| 'CUSTOMER NAME CANNOT BE NULL';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'CUSTOMER NAME CANNOT BE NULL');
END IF;
END;
----=====2.validation Check if the Customer Site name provided is valid====---------------
------------------------------------------------------------------------------------------
BEGIN
x_debug_point := 5; --this is 5th column validation of staging table
-- IF vc1.bill_to_customer IS NOT NULL THEN
BEGIN
SELECT hcasa.cust_acct_site_id, hl.location_id
INTO p_g_cust_acct_site_id, p_g_address_id
FROM hz_locations hl,
hz_party_sites hps,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua
WHERE hl.location_id = hps.location_id
AND hps.party_site_id = hcasa.party_site_id
AND hps.party_id = hca.party_id
AND hca.cust_account_id = hcasa.cust_account_id
AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
AND hcsua.site_use_code = 'BILL_TO'
AND hca.status = 'A'
AND hcasa.status = 'A'
AND hcsua.status = 'A'
and hcsua.org_id = x_org_id
AND hps.party_id=x_customer_id;
-- AND upper(hcsua.location) = upper(vc1.SITE_NAME);
--SHIP TO
SELECT hcasa.cust_acct_site_id, hl.location_id
INTO p_g_cust_acct_site_id1, p_g_address_id1
FROM hz_locations hl,
hz_party_sites hps,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua
WHERE hl.location_id = hps.location_id
AND hps.party_site_id = hcasa.party_site_id
AND hps.party_id = hca.party_id
AND hca.cust_account_id = hcasa.cust_account_id
AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
AND hcsua.site_use_code = 'SHIP_TO'
AND hca.status = 'A'
AND hcsua.status = 'A'
and hcsua.org_id = x_org_id
AND hps.party_id=x_customer_id;
-- AND upper(hcsua.location) = upper(vc1.SITE_NAME);
/******************************************************************************
SELECT ra.address_id
INTO p_g_address_id
FROM ra_addresses_all ra,
ra_customers rc,
ra_site_uses_all rsu
WHERE ra.customer_id = rc.customer_id
AND rsu.address_id = ra.address_id
AND rsu.site_use_code = 'BILL_TO'
AND UPPER(rc.customer_name) =UPPER(vc1.customer_name)
AND ra.country=vc1.customer_site_name
AND rc.status='A'
AND ra.org_id= p_org_id --fnd_profile.value('org_id')
AND ra.bill_to_Flag='Y';
******************************************************************************/
FND_FILE.PUT_LINE(FND_FILE.LOG,'Success at column: ' ||x_debug_point); --##
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_record_status:='FAIL';
x_err_msg := x_err_msg || 'Customer Site Name provided does not exist in Oracle eBS.';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||x_err_msg);
WHEN OTHERS THEN
p_record_status:='FAIL';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||x_customer_id||'-'||SQLERRM);
END;
-- ELSE
-- p_record_status:='FAIL';
-- x_err_msg := x_err_msg|| 'CUSTOMER SITE CANNOT BE NULL';
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'CUSTOMER SITE CANNOT BE NULL');
-- END IF;
END;
-- Customer Account Validation
BEGIN
/* select ca.cust_account_id
Into x_cust_account_id
From hz_cust_accounts ca, hz_parties c
Where c.party_id = ca.party_id
and c.status = 'A'
and ca.status = 'A'
and c.party_name = vc1.BILL_TO_CUSTOMER; */
select customer_id Into x_cust_account_id
from ar_customers
where customer_name = vc1.BILL_TO_CUSTOMER
and status = 'A'
and rownum = 1;
EXCEPTION
WHEN OTHERS THEN
p_record_status:='FAIL';
x_err_msg := x_err_msg|| 'Not a valid Customer '|| vc1.BILL_TO_CUSTOMER;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Not a valid Customer Number -> '|| vc1.BILL_TO_CUSTOMER);
END;
-- Customer Primary Bill to Site Address Validation
BEGIN
select sa.cust_acct_site_id
Into x_cust_acct_site_id
From hz_cust_accounts ca, hz_cust_acct_sites_all sa
where ca.cust_account_id = sa.cust_account_id
and ca.status = 'A'
and sa.status = 'A'
and sa.bill_to_flag = 'P'
and sa.org_id = x_org_id
and ca.cust_account_id in x_cust_account_id;
EXCEPTION
WHEN OTHERS THEN
p_record_status:='FAIL';
x_err_msg := x_err_msg|| 'No valid Bill To Site for Customer '|| vc1.BILL_TO_CUSTOMER;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Customer ' || vc1.BILL_TO_CUSTOMER || ' does not have a valid Primary Bill to Site Address');
END;
-------====validation Check whether the GL Date falls within an Open period=====----------
----------------------------------------------------------------------------------------------------
BEGIN
x_debug_point := 7; --this is 7th column validation of staging table
FND_FILE.PUT_LINE(FND_FILE.LOG,'GLDATE'||vc1.GL_DATE);
SELECT application_id
INTO x_application_id
FROM gl_period_statuses
WHERE application_id = (SELECT application_id FROM fnd_application WHERE product_code='AR')
AND set_of_books_id = p_set_of_books_id --FND_PROFILE.VALUE('GL_SET_OF_BKS_ID')
AND TO_DATE(vc1.GL_DATE, 'DD-MON-RRRR') BETWEEN NVL(start_date,TRUNC(SYSDATE)) AND NVL(end_date,TRUNC(SYSDATE))
AND closing_status IN ('O','F')
AND adjustment_period_flag != 'Y';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Success at column: ' ||x_debug_point); --##
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_record_status:='FAIL';
x_err_msg := x_err_msg || 'GL Date' ||vc1.GL_DATE|| 'does not fall under an Open Period of Oracle eBS.';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||x_err_msg);
WHEN OTHERS THEN
p_record_status:='FAIL';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||SQLERRM);
END;
------======validation Check if the Transaction Type provided is valid=======----------------------------------
----===============================================================================================================
BEGIN
x_debug_point := 9; --this is the 9th column validation of the staging table
IF vc1.CUST_TRX_TYPE IS NOT NULL THEN
BEGIN
SELECT cust_trx_type_id
INTO p_g_cust_trx_type_id
FROM ra_cust_trx_types_all
WHERE UPPER(name) =UPPER(vc1.CUST_TRX_TYPE)
AND UPPER(TYPE)=UPPER(vc1.INV_TYPE)
AND TRUNC(SYSDATE) BETWEEN start_date AND NVL(end_date,TRUNC(SYSDATE))
AND org_id = x_org_id; --fnd_profile.value('org_id')
--AND set_of_books_id =p_set_of_books_id --FND_PROFILE.VALUE('GL_SET_OF_BKS_ID')
--AND status = 'A';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Success at column: ' ||x_debug_point);
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_record_status:='FAIL';
x_err_msg := x_err_msg || UPPER(vc1.CUST_TRX_TYPE)||' is not valid Transaction Type in Oracle eBS.';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error is :'||x_err_msg);
WHEN OTHERS THEN
p_record_status:='FAIL';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||SQLERRM);
END;
ELSE
p_record_status :='FAIL';
x_err_msg := x_err_msg || 'CUST_TRX_TYPE CANNOT BE NULL';
FND_FILE.PUT_LINE(FND_FILE.LOG,'CUST_TRX_TYPE CANNOT BE NULL');
END IF;
END;
------------====VALIDATE THE INVOICE AMOUNT ...-----=======================================
--------------------------------------------------------------------------------------------------------------------
BEGIN
x_debug_point := 11; --this is the 11th column validation of the staging table
IF vc1.AMOUNT IS NULL THEN
p_record_status :='FAIL';
x_err_msg := x_err_msg || 'INVOICE AMOUNT CANNOT BE NULL';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVOICE AMOUNT CANNOT BE NULL');
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'Success at column: ' ||x_debug_point);
END IF;
EXCEPTION
WHEN OTHERS THEN
p_record_status :='FAIL';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||SQLERRM);
END;
------------=====================GET FUNCTIONAL CURRENCY==============================------------------------------
--------------------------------------------------------------------------------------------------------------------
BEGIN
SELECT currency_code
INTO x_func_curr
FROM apps.gl_sets_of_books
WHERE SET_OF_BOOKS_ID = p_set_of_books_id; --FND_PROFILE.VALUE('GL_SET_OF_BKS_ID')
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'functional currency is null at :');
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||SQLERRM);
END;
IF UPPER(x_func_curr) = UPPER(vc1.CURRENCY_CODE) THEN
p_g_conversion_type := 'User';
p_g_conversion_rate := 1;
ELSE
BEGIN
--SELECT fnd_profile.value('AR: Default Exchange Rate')
--INTO p_g_conversion_type
--FROM dual;
-- p_g_conversion_rate := NULL;
p_g_conversion_type := 'User';----------------------Added
p_g_conversion_rate:=vc1.EXCHANGE_RATE;---------------------Added
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_g_conversion_type := '1000';
p_g_conversion_rate := NULL;
WHEN OTHERS THEN
p_g_conversion_type := '1000';
p_g_conversion_rate := NULL;
END;
--p_g_conversion_type := 'UN Rate';
--p_g_conversion_rate := NULL;
END IF;
------========validate Check if the Currency Code provided is valid======------------------------
------------------------------------------------------------------------------------------------
BEGIN
x_debug_point := 10; --this is the 10th column validation of the staging table
SELECT currency_code
INTO p_g_currency_code
FROM fnd_currencies
WHERE currency_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active,TRUNC(SYSDATE)) AND NVL(end_date_active,TRUNC(SYSDATE))
AND UPPER(currency_code) = UPPER(vc1.CURRENCY_CODE);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Success at column: ' ||x_debug_point); --##
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_record_status :='FAIL';
x_err_msg := x_err_msg ||UPPER(vc1.CURRENCY_CODE)||' is not a valid Currency in Oracle eBS.';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||x_err_msg);
WHEN OTHERS THEN
p_record_status :='FAIL';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||SQLERRM||x_debug_point);
END;
---------======validate 1.f. Check if the Batch Source Name provided is valid======----------------------------
---------------------------------------------------------------------------------------------------------------
BEGIN
x_debug_point := 8; --this is the 8th column validation of the staging table
IF vc1.BATCH_SOURCE_NAME IS NOT NULL THEN
BEGIN
SELECT name
INTO x_name
FROM ra_batch_sources_all
WHERE batch_source_type = 'FOREIGN'
AND UPPER(name) =UPPER(vc1.BATCH_SOURCE_NAME)
AND status = 'A'
AND TRUNC(SYSDATE) BETWEEN start_date AND NVL(end_date,TRUNC(SYSDATE))
AND org_id = x_org_id; --fnd_profile.value('org_id')
FND_FILE.PUT_LINE(FND_FILE.LOG,'Success at column: ' ||x_debug_point);
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_record_status :='FAIL';
x_err_msg := x_err_msg ||UPPER(vc1.BATCH_SOURCE_NAME)||'is not a valid Transaction Source in Oracle eBS.';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||x_err_msg);
WHEN OTHERS THEN
p_record_status :='FAIL';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||SQLERRM);
END;
ELSE
p_record_status :='FAIL';
x_err_msg := x_err_msg ||' Transaction Source CANNOT BE NULL';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,' Transaction Source CANNOT BE NULL');
END IF;
END;
---------======validate . Check if the Payment Term provided is valid======----------------------------
---------------------------------------------------------------------------------------------------------------
BEGIN
x_debug_point:=12;
IF vc1.TERM_ID IS NOT NULL THEN
BEGIN
SELECT a.term_id,a.name
INTO p_g_term_id,p_g_term_name
FROM ra_terms_vl a
WHERE UPPER(a.term_id) =UPPER(vc1.TERM_ID)
AND TRUNC(SYSDATE) BETWEEN a.start_date_active AND NVL(a.end_date_active, SYSDATE);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Success at column: ' ||x_debug_point);
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_record_status :='FAIL';
x_err_msg := x_err_msg ||UPPER(vc1.TERM_id)|| 'is not a valid Payment Term in Oracle eBS.';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||x_err_msg);
WHEN OTHERS THEN
p_record_status :='FAIL';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||SQLERRM);
END;
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'Success at column: ' ||x_debug_point);
END IF;
END;
-- If Transaction Type is Credit Memo, make Payment Term NULL
-------------------------------Commented--------------------------
IF vc1.AMOUNT < 0 and vc1.INV_TYPE = 'CM' THEN
p_g_term_name := NULL;
p_g_term_id := NULL;
END IF;
------------------------------------------------------------------
---------======validate . Check if the REASON CODE provided is valid======----------------------------
---------------------------------------------------------------------------------------------------------------
/************************************************************************************************************************
BEGIN
x_debug_point:=20;
IF vc1.reason_code IS NOT NULL THEN
BEGIN
SELECT meaning
INTO x_meaning
FROM fnd_lookup_values_vl
WHERE lookup_code LIKE 'REASON_TYPE'
AND TRUNC(SYSDATE) BETWEEN start_date_active AND NVL(end_date_active,TRUNC(SYSDATE))
AND ENABLED_FLAG = 'Y';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Success at column: ' ||x_debug_point);
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_record_status :='FAIL';
x_err_msg := x_err_msg ||UPPER(vc1.reason_code)|| 'is not a valid Payment Term in Oracle eBS.';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||x_err_msg);
WHEN OTHERS THEN
p_record_status :='FAIL';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||SQLERRM);
END;
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'Success at column: ' ||x_debug_point);
END IF;
END;
------=======validate Check if the UOM code provided is valid==========-----------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
BEGIN
x_debug_point:=27; --this is the 27th column validation of the staging table
SELECT disable_date,unit_of_measure
INTO x_disable_date,p_g_uom_name
FROM mtl_units_of_measure
WHERE uom_code=vc1.uom_code
AND LANGUAGE='US';
IF(x_disable_date IS NOT NULL OR x_disable_date < TRUNC(SYSDATE)) THEN
p_record_status :='FAIL';
x_err_msg := x_err_msg || UPPER(vc1.uom_code)||'is not a valid Unit Of Measure in Oracle eBS.';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||x_err_msg);
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'Success at column: ' ||x_debug_point);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_record_status :='FAIL';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||x_debug_point);
WHEN OTHERS THEN
p_record_status :='FAIL';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||SQLERRM||SQLCODE);
END;
************************************************************************************************************************/
---------==========validate Check if the Description provided is a valid Memo line======--------------------------------
------------------------------------------------------------------------------------------------------------------------
BEGIN
x_debug_point:=26; --this is the 26th column validation of the staging table
SELECT amlb.memo_line_id
INTO p_g_memo_line_id
FROM ar_memo_lines_all_b amlb,
ar_memo_lines_all_tl amlt
WHERE amlb.memo_line_id=amlt.memo_line_id
AND UPPER(amlt.name) = UPPER(vc1.DESCRIPTION)
AND TRUNC(SYSDATE) BETWEEN start_date AND NVL(end_date,TRUNC(SYSDATE))
AND LANGUAGE='US'
AND amlt.org_id= x_org_id --fnd_profile.value('org_id')
AND amlb.set_of_books_id=p_set_of_books_id; --FND_PROFILE.VALUE('GL_SET_OF_BKS_ID')
FND_FILE.PUT_LINE(FND_FILE.LOG,'Success at column: ' ||x_debug_point);
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_record_status :='FAIL';
x_err_msg := x_err_msg ||UPPER(vc1.DESCRIPTION)||'Is not a valid Memo Line defined in Oracle eBS.';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||x_err_msg);
WHEN OTHERS THEN
p_record_status :='FAIL';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||SQLERRM);
END;
---------==========validate Check if the Invoice Date null==============================--------------------------------
------------------------------------------------------------------------------------------------------------------------
BEGIN
x_debug_point := 6; --this is the 6th column validation of the staging table
IF VC1.TRX_DATE IS NULL THEN
p_record_status :='FAIL';
x_err_msg := x_err_msg || 'INVOICE DATE CANNOT BE NULL';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVOICE DATE CANNOT BE NULL');
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'Success at column: ' ||x_debug_point);
END IF;
EXCEPTION
WHEN OTHERS THEN
p_record_status :='FAIL';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||SQLERRM);
END;
---------==========validate Check if the LINE QUANTITY null=============================--------------------------------
------------------------------------------------------------------------------------------------------------------------
/****************************************************************************************************************************
BEGIN
x_debug_point := 28; --this is the 28th column validation of the staging table
IF VC1.line_quantity IS NULL OR vc1.line_quantity = 0 THEN
p_record_status :='FAIL';
x_err_msg := x_err_msg || 'LINE QUANTITY CANNOT BE NULL or zero';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'LINE QUANTITY CANNOT BE NULL or zero');
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'Success at column: ' ||x_debug_point);
END IF;
EXCEPTION
WHEN OTHERS THEN
p_record_status :='FAIL';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||SQLERRM);
END;
---------==========validate Check if the Unit Selling Price null========================--------------------------------
------------------------------------------------------------------------------------------------------------------------
BEGIN
x_debug_point := 29; --this is the 29th column validation of the staging table
IF VC1.unit_selling_price IS NULL OR vc1.unit_selling_price = 0 THEN
p_record_status :='FAIL';
x_err_msg := x_err_msg || 'Unit Selling Price CANNOT BE NULL or zero';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Unit Selling Price CANNOT BE NULL or zero');
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'Success at column: ' ||x_debug_point);
END IF;
EXCEPTION
WHEN OTHERS THEN
p_record_status :='FAIL';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||SQLERRM);
END;
****************************************************************************************************************************/
---------==========validate Check if the LINE AMOUNT null========================---------------------------------------
------------------------------------------------------------------------------------------------------------------------
BEGIN
x_debug_point := 30; --this is the 30th column validation of the staging table
IF VC1.AMOUNT IS NULL OR vc1.AMOUNT = 0 THEN
p_record_status :='FAIL';
x_err_msg := x_err_msg || 'LINE AMOUNT CANNOT BE NULL or zero';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'LINE AMOUNT CANNOT BE NULL or zero');
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'Success at column: ' ||x_debug_point);
END IF;
EXCEPTION
WHEN OTHERS THEN
p_record_status :='FAIL';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Fail at column: ' ||x_debug_point);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||SQLERRM);
END;
---------========== Validation for Interface_line_attributes based on transaction source========================---------
------------------------------------------------------------------------------------------------------------------------
---------===========Now Insert records into RA_INTERFACE_LINES_ALL table and UPDATE Staging table======================
------------------------------------------------------------------------------------------------------------------------
BEGIN
IF p_record_status='NEW' THEN
INSERT INTO RA_INTERFACE_LINES_ALL(orig_system_bill_customer_id --customer_id
,ORIG_SYSTEM_SHIP_CUSTOMER_ID
,orig_system_bill_address_id --address_id
,ORIG_SYSTEM_SHIP_ADDRESS_ID
-- ,orig_system_bill_customer_ref --cust_account_id
-- ,orig_system_bill_address_ref --cust_acct_site_id
,trx_number --invoice_number
--,attribute1
,term_name --Payment_term
,trx_date --invoice date
,gl_date --Accounting_Date
,batch_source_name --batch_source_name
,cust_trx_type_name --transaction_type
,cust_trx_type_id --customer transaction type id
,amount --Invoice_Amount
,currency_code --Currency_Code
,term_id --Payment_Term_Id
,line_type --Line_Type
--,quantity
--,uom_code --uom_code
,description --line_description
-- ,reason_code --Reason_Code
,interface_line_context
,interface_line_attribute1 --interface_attribute1
,interface_line_attribute2 --interface_attribute2
,interface_line_attribute3 --interface_attribute3
,orig_system_batch_name --Batch Number
,conversion_type
,conversion_rate
,conversion_date
-- ,unit_selling_price
,memo_line_name
,memo_line_id
,creation_date
,last_update_date
,created_by
,last_updated_by
,org_id
,set_of_books_id
,primary_salesrep_id
-- ,document_number_sequence_id ---------26th Nov,2008
)
VALUES( x_cust_account_id, -- x_customer_id
x_cust_account_id
,p_g_cust_acct_site_id -- x_cust_acct_site_id --,p_g_address_id
--,_g_cust_account_id1
,p_g_cust_acct_site_id1
,vc1.TRX_NO
,p_g_term_name
,TO_DATE(vc1.TRX_DATE, 'DD-MON-RRRR')
,TO_DATE(vc1.GL_DATE , 'DD-MON-RRRR')
,vc1.BATCH_SOURCE_NAME
,vc1.CUST_TRX_TYPE
,p_g_cust_trx_type_id
,to_number(to_char(vc1.AMOUNT,'999999999D99'))
,p_g_currency_code
,p_g_term_id
,'LINE'--vc1.LINE_TYPE
--,vc1.line_quantity
--,vc1.uom_code
,vc1.DESCRIPTION
-- ,vc1.Reason_Code
,vc1.BATCH_SOURCE_NAME -- ,UPPER(vc1.transaction_source)
,vc1.TRX_NO -- ,vc1.interface_line_attribute1
,'LINE'--vc1.LINE_TYPE -- ,vc1.interface_line_attribute2
,vc1.RECORD_NUM --'1' -- ,vc1.interface_line_attribute3
,vc1.BATCH_SOURCE_NAME -- orig_system_batch_name
,p_g_conversion_type
,p_g_conversion_rate
-- ,NULL
,SYSDATE -- conversion_date
-- ,vc1.unit_selling_price
,vc1.DESCRIPTION
,p_g_memo_line_id
,SYSDATE
,SYSDATE
,FND_PROFILE.VALUE('USER_ID')
,FND_PROFILE.VALUE('USER_ID')
,x_org_id
,p_set_of_books_id
,x_primary_salesrep_id
-- ,l_DOC_SEQUENCE_ID---------Nov,2008
);
INSERT INTO ra_interface_salescredits_all
(org_id
,interface_line_context
,salesrep_id
,sales_credit_type_id
,sales_credit_amount_split
-- ,sales_credit_percent_split
,interface_line_attribute1
,interface_line_attribute2
,interface_line_attribute3
,created_by
,last_updated_by
,creation_date
,last_update_date
)
VALUES
( x_org_id
,vc1.BATCH_SOURCE_NAME -- l_int_line_context
,x_primary_salesrep_id
,x_sales_credit_type_id
,to_number(to_char(vc1.AMOUNT,'999999999D99')) -- x_sales_credit_split
,vc1.TRX_NO -- interface_line_attribute1
,vc1.LINE_TYPE -- interface_line_attribute2
,'1' -- interface_line_attribute3
,FND_PROFILE.VALUE('USER_ID')
,FND_PROFILE.VALUE('USER_ID')
,SYSDATE
,SYSDATE
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Successful insertion into AR_INTERFACE_LINES_ALL table: ' ); --##
p_record_status :='PASS';
UPDATE ZN_AR_INVOICE_STG
SET record_status =p_record_status
WHERE TRX_NO = vc1.TRX_NO
AND UPPER(BILL_TO_CUSTOMER)=UPPER(vc1.BILL_TO_CUSTOMER);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Successful Updation ofStaging Table: ');
ELSE
UPDATE ZN_AR_INVOICE_STG
SET record_status =p_record_status,
error_code=x_err_msg
WHERE TRX_NO = vc1.TRX_NO
AND UPPER(BILL_TO_CUSTOMER)=UPPER(vc1.BILL_TO_CUSTOMER);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Successful Updation offailed records into Staging Table: ');
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert into interface table or update of staging table Failed: ' );
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||SQLERRM||' ,'||vc1.TRX_NO);
END;
EXCEPTION
WHEN OTHERS THEN
p_record_status :='FAIL';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||SQLERRM||' ,'||vc1.TRX_NO);
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error :'||SQLERRM);
END;
END AR_INVOICE_CONV_PKG;
/
No comments:
Post a Comment