|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Q.
Hi all,
I want to display ap_invoices_interface table status='REJECTED' data we want display in log file
any one hlep please.its very urgent
we send my package and procedure also
CREATE OR REPLACE PACKAGE BODY APPS.xxiflic_ap_interface_pkg
AS
-- +===========================================================================+
-- | |
-- +===========================================================================+
-- | Name : XXIFLIC_AP_INTERFACE_PKG_BODY.sql |
-- | Project : AP Invoice Import Program |
-- | Application: |
-- | Inputs : |
-- | Outputs : |
-- | Database : |
-- | Description: |
-- | |
-- | |
-- | |
-- | |
-- | Related |
-- | Documents |
-- | |
-- |Version Date Author Comments |
-- |======= =========== ================= ================================= |
-- |1.0 Shashi Kumar Initial Version |
-- | |
-- +===========================================================================+
PROCEDURE ap_interface_proc(errbuf OUT VARCHAR2,retcode OUT VARCHAR2)
AS
--
-- Local Variable Declaration
--
ln_invoice_id_h NUMBER;
ln_invoice_id_l NUMBER;
ln_vendor_id po_vendors.vendor_id%TYPE;
ln_vendor_SITE_id po_vendor_SITES_all.vendor_SITE_id%TYPE;
ln_terms_id ap_terms.term_id%TYPE;
lc_terms_name ap_terms.name%TYPE;
lc_error_flag CHAR(1) := 'N';
ld_invoice_date VARCHAR2(25);--ap_invoices_all.invoice_date%Type;
ld_gl_date VARCHAR2(25);--ap_invoices_all.gl_date%Type;
lc_vendor_name po_vendors.vendor_name%TYPE;
lc_vendor_num po_vendors.segment1%TYPE;
lc_vendor_SITE_code po_vendor_SITES_all.VENDOR_SITE_CODE%TYPE;
lc_INVOICE_CURRENCY_CODE ap_invoices_all.INVOICE_CURRENCY_CODE%TYPE;
lc_exchange_rate VARCHAR2(50);--ap_invoices_all.exchange_rate%Type;
ln_code_combination_id NUMBER;
ln_ou NUMBER;
ln_set_of_books_id NUMBER;
ln_laibility_ccid NUMBER;
ln_invoice_amount NUMBER :=0;
lc_exchange_rate_type VARCHAR2(25);
LC_DESCRIPTION VARCHAR2(240);
lc_DIST_CODE_CONCATENATED VARCHAR2(60);
lc_prepayment_account VARCHAR2(60);
lc_attribute15 VARCHAR2(50);
lc_attribute15_ccid NUMBER :=0;
ln_inv_count NUMBER := 0;
ln_rej_count NUMBER := 0; --Added by Rambhupal
ln_line_count NUMBER := 0;
lc_err_msg VARCHAR2(3000);
LN_RECD_COUNT NUMBER := 0;
LN_PREPAY_AMT_1 NUMBER := 0;
LN_PREPAY_AMT_2 NUMBER := 0;
LN_PRE_APP_AMT NUMBER := 0;
LN_COA_ID NUMBER := 0;
LN_CCD_ID NUMBER := 0;
ln_line_amt NUMBER := 0;
ln_invoice_amt NUMBER;
l_PREPAY_DIST_NUM VARCHAR2(50):= NULL; ---to be fetched
l_LINE_TYPE_LOOKUP_CODE VARCHAR2(250):= NULL;---to be fetched
lv_inv_lukup varchar2(30);
ln_vendor_check NUMBER;
ln_orig_site_id NUMBER;
ln_vendor_site_check NUMBER;
--
-- Invoice Header Information getting
--
CURSOR c_header_inf
IS
SELECT DISTINCT INVOICE_NUMBER,SITE_ID,INVOICE_AMOUNT,gl_date
,INVOICE_DATE,INVOICE_TYPE,ORG_ID,voucher_number
,PREPAY_NUM,PREPAY_APPLY_AMOUNT,INVOICE_INCLUDES_PREPAY_FLAG,prepay_gl_date,PAY_GROUP_LOOKUP_CODE,
PAYMENT_METHOD_LOOKUP_CODE
FROM XXIFLIC_AP_INVOICE_STAGING
WHERE interface_status IS NULL;
-- Invoice Lines Information getting
--
CURSOR c_lines_inf(p_INVOICE_NUM VARCHAR2,p_VENDOR_SITE_ID NUMBER)
IS
SELECT DISTINCT *
FROM XXIFLIC_AP_INVOICE_STAGING
WHERE INVOICE_NUMBER = p_INVOICE_NUM
AND SITE_ID = p_VENDOR_SITE_ID
AND interface_status IS NULL;
--
BEGIN
delete from XXIFLIC_AP_INVOICE_STAGING
WHERE NVL(interface_status,'N') = 'E';
--
--
COMMIT;
--
BEGIN
------SOURCE SYSTEM 1 TnE_BusinessBilling_v@DG4ODBC -----------------------
insert into XXIFLIC_AP_INVOICE_STAGING(invoice_number
,invoice_type
,invoice_date
,site_id
,invoice_amount
,invoice_currency
,terms_name
,invoice_description
,payment_method_lookup_code
,pay_group_lookup_code
,gl_date
,org_id
,line_number
,line_type_lookup_code
,amount
,accounting_date
,line_description
,dist_code_concatenated
,voucher_number
,employee_number
,accounting_entry_id
,module_id
)
(select invoice_number,invoice_type,invoice_date,supplier_site,total_os_inv_amount,invoice_currency,payment_terms,invoice_header_description,payment_method,null,sysdate,83
,null,line_type,os_inv_line_amount,null,description,nvl(expense_account,prepayment_account),voucher_no,null,accounting_entry_id,module_id
from TnE_BusinessBilling_v@DG4ODBC xxtne
where accounting_entry_id not in (select accounting_entry_id from XXIFLIC_AP_INVOICE_STAGING xxai where xxai.module_id = xxtne.module_id));
--
commit;
--
------SOURCE SYSTEM 2 TnE_BusinessReimbursement_v@DG4ODBC -----------------------
insert into XXIFLIC_AP_INVOICE_STAGING(invoice_number
,invoice_type
,invoice_date
,site_id
,invoice_amount
,invoice_currency
,terms_name
,invoice_description
,payment_method_lookup_code
,pay_group_lookup_code
,gl_date
,org_id
,line_number
,line_type_lookup_code
,amount
,accounting_date
,line_description
,dist_code_concatenated
,voucher_number
,employee_number
,accounting_entry_id
,module_id
)
(select invoice_number,invoice_type,invoice_date,supplier_site,total_os_inv_amount,invoice_currency,payment_terms,invoice_header_description,payment_method,null,sysdate,83
,null,line_type,os_inv_line_amount,null,description,nvl(expense_account,prepayment_account),voucher_no,null,accounting_entry_id,module_id
from TnE_BusinessReimbursement_v@DG4ODBC xxtne
where accounting_entry_id not in (select accounting_entry_id from XXIFLIC_AP_INVOICE_STAGING xxai where xxai.module_id = xxtne.module_id));
--
COMMIT;
------SOURCE SYSTEM 3 TnE_VendorAdvance_v@DG4ODBC -----------------------
insert into XXIFLIC_AP_INVOICE_STAGING(invoice_number
,invoice_type
,invoice_date
,site_id
,invoice_amount
,invoice_currency
,terms_name
,invoice_description
,payment_method_lookup_code
,pay_group_lookup_code
,gl_date
,org_id
,line_number
,line_type_lookup_code
,amount
,accounting_date
,line_description
,dist_code_concatenated
,voucher_number
,employee_number
,accounting_entry_id
,module_id
)
(select invoice_number,invoice_type,invoice_date,supplier_site,total_os_inv_amount,invoice_currency,payment_terms,invoice_header_description,payment_method,null,sysdate,83
,null,line_type,os_inv_line_amount,null,description,nvl(expense_account,prepayment_account),voucher_no,null,accounting_entry_id,module_id
from TnE_VendorAdvance_v@DG4ODBC xxtne
where accounting_entry_id not in (select accounting_entry_id from XXIFLIC_AP_INVOICE_STAGING xxai where xxai.module_id = xxtne.module_id));
--
COMMIT;
------SOURCE SYSTEM 4 TnE_VendorInvoice_v@DG4ODBC -----------------------
insert into XXIFLIC_AP_INVOICE_STAGING(invoice_number
,invoice_type
,invoice_date
,site_id
,invoice_amount
,invoice_currency
,terms_name
,invoice_description
,payment_method_lookup_code
,pay_group_lookup_code
,gl_date
,org_id
,line_number
,line_type_lookup_code
,amount
,accounting_date
,line_description
,dist_code_concatenated
,voucher_number
,employee_number
,accounting_entry_id
,module_id
)
(select invoice_number,invoice_type,invoice_date,supplier_site,total_os_inv_amount,invoice_currency,payment_terms,invoice_header_description,payment_method,null,sysdate,83
,null,line_type,os_inv_line_amount,null,description,nvl(expense_account,prepayment_account),voucher_no,null,accounting_entry_id,module_id
from TnE_VendorInvoice_v@DG4ODBC xxtne
where accounting_entry_id not in (select accounting_entry_id from XXIFLIC_AP_INVOICE_STAGING xxai where xxai.module_id = xxtne.module_id));
--
COMMIT;
------SOURCE SYSTEM 5 TnE_VendorRegbilling_v@DG4ODBC -----------------------
insert into XXIFLIC_AP_INVOICE_STAGING(invoice_number
,invoice_type
,invoice_date
,site_id
,invoice_amount
,invoice_currency
,terms_name
,invoice_description
,payment_method_lookup_code
,pay_group_lookup_code
,gl_date
,org_id
,line_number
,line_type_lookup_code
,amount
,accounting_date
,line_description
,dist_code_concatenated
,voucher_number
,employee_number
,accounting_entry_id
,module_id
)
(select invoice_number,invoice_type,invoice_date,supplier_site,total_os_inv_amount,invoice_currency,payment_terms,invoice_header_description,payment_method,null,sysdate,83
,null,line_type,os_inv_line_amount,null,description,nvl(expense_account,prepayment_account),voucher_no,null,accounting_entry_id,module_id
from TnE_VendorRegbilling_v@DG4ODBC xxtne
where accounting_entry_id not in (select accounting_entry_id from XXIFLIC_AP_INVOICE_STAGING xxai where xxai.module_id = xxtne.module_id));
--
COMMIT;
exception
WHEN OTHERS THEN
lc_err_msg := 'EXCEPTION while inserting data into staging'||SQLERRM;
END;
------------
--
-- Updating Interface status flag message to NULL
--
FOR i IN c_header_inf
LOOP
--
-- Initializations
--
lc_error_flag := 'N';
lc_err_msg := NULL;
ln_invoice_amt := i.invoice_amount;
ln_vendor_site_check := 0;
--
-- Invoice Id fetching
--
SELECT ap_invoices_interface_s.NEXTVAL
INTO ln_invoice_id_h
FROM dual;
--
--
IF i.invoice_number is null then
lc_error_flag := 'Y';
lc_err_msg := lc_err_msg||'Invoice Number Can not be Null,';
END IF;
--
IF i.site_id is null then
lc_error_flag := 'Y';
lc_err_msg := lc_err_msg||'site id Can not be Null,';
END IF;
BEGIN
SELECT lookup_code
INTO lv_inv_lukup
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE LIKE 'INVOICE TYPE'
AND lookup_code = decode(trim(i.INVOICE_TYPE),'Standard Invoice','STANDARD',upper(i.INVOICE_TYPE));
EXCEPTION
when others then
lv_inv_lukup := NULL;
END;
IF lv_inv_lukup IS NULL THEN
lc_error_flag := 'Y';
lc_err_msg := lc_err_msg||'Invalid Invoice_type not be Null,';
END IF;
--
lv_inv_lukup := NULL;
BEGIN
SELECT lookup_code
INTO lv_inv_lukup
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE LIKE 'PAYMENT METHOD'
AND lookup_code = i.PAYMENT_METHOD_LOOKUP_CODE;
EXCEPTION
when others then
lv_inv_lukup := NULL;
END;
IF lv_inv_lukup IS NULL THEN
lc_error_flag := 'Y';
lc_err_msg := lc_err_msg||'Invalid payment method lookup code not be Null,';
END IF;
--
-- Org Id validation
IF i.org_id is null then
lc_error_flag := 'Y';
lc_err_msg := lc_err_msg||'Org Id Can not be Null,';
ELSE
BEGIN
SELECT organization_id
INTO ln_ou
FROM hr_operating_units
where organization_id = i.org_id;
exception
when others then
ln_ou := 0;
END;
--
IF (ln_ou = 0) OR (ln_ou is NULL) then
lc_error_flag := 'Y';
lc_err_msg := lc_err_msg||'Invalid Organization Id,';
END IF;
END IF;
--
--
-- Invoice Date fetching
--
IF lc_error_flag <> 'Y' THEN
BEGIN
SELECT distinct
invoice_date
,gl_date
,INVOICE_CURRENCY
,INVOICE_description
-- ,DIST_CODE_CONCATENATED
INTO ld_invoice_date
,ld_gl_date
,lc_INVOICE_CURRENCY_CODE
,lc_description
--,lc_DIST_CODE_CONCATENATED
FROM XXIFLIC_AP_INVOICE_STAGING
WHERE SITE_ID = i.SITE_ID
AND INVOICE_NUMBER = i.INVOICE_NUMBER
AND org_id = i.org_id
AND INTERFACE_STATUS IS NULL;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UPDATE XXIFLIC_AP_INVOICE_STAGING
SET interface_status = 'E',
error_message = error_message||'Invoice Data is Inconsistent'
WHERE SITE_ID = i.SITE_ID
AND INVOICE_NUMBER = i.INVOICE_NUMBER
AND org_id = i.org_id;
lc_error_flag := 'Y';
--
lc_err_msg := lc_err_msg||' Invoice Data is Inconsistent,';
WHEN OTHERS THEN
UPDATE XXIFLIC_AP_INVOICE_STAGING
SET interface_status = 'E',
error_message = error_message||'Error while fetching invoice data'
WHERE SITE_ID = i.SITE_ID
AND INVOICE_NUMBER = i.INVOICE_NUMBER
AND org_id = i.org_id;
lc_error_flag := 'Y';
lc_err_msg := lc_err_msg||' Error while fetching invoice data,';
END;
ELSE
UPDATE XXIFLIC_AP_INVOICE_STAGING
SET interface_status = 'E',
error_message = error_message||lc_err_msg
WHERE NVL(SITE_ID,1) = NVL(i.SITE_ID,1)
AND INVOICE_NUMBER = i.INVOICE_NUMBER
AND org_id = i.org_id;
commit;
END IF;
--
-- Vendor Id fetching
--
-----Site Id Check ----------
ln_vendor_check := 0;
ln_orig_site_id := 0;
--
BEGIN
SELECT PVS.vendor_site_id
INTO ln_vendor_check
FROM PO_VENDOR_SITES_ALL PVS
WHERE PVS.vendor_site_id = i.SITE_ID;
EXCEPTION
WHEN OTHERS THEN
ln_vendor_check := 0;
END;
--
IF ln_vendor_check = 0 THEN
--
ln_vendor_site_check:=0;
BEGIN
SELECT PVS.vendor_site_id
INTO ln_vendor_site_check
FROM PO_VENDORS PV
,PO_VENDOR_SITES_ALL PVS
WHERE PV.vendor_id = pvs.vendor_id
and pvs.vendor_site_code = 'OFFICE'
and pv.segment1 = to_char(i.SITE_ID)
and pvs.org_id = i.org_id;
EXCEPTION
WHEN OTHERS THEN
ln_vendor_site_check := 0;
END;
--
IF ln_vendor_site_check = 0 THEN
lc_error_flag := 'Y';
lc_err_msg := lc_err_msg||' Vendor not exists,';
ELSE
ln_orig_site_id := i.SITE_ID;
i.SITE_ID := ln_vendor_site_check;
END IF;
END IF;
--
IF lc_error_flag <> 'Y' THEN
BEGIN
SELECT pv.vendor_id
,pv.vendor_name
,segment1 vendor_num
INTO ln_vendor_id
,lc_vendor_name
,lc_vendor_num
FROM po_vendors PV
,po_vendor_sites_all povsa
WHERE pv.vendor_id = povsa.vendor_id
and povsa.vendor_site_id = i.site_id
and povsa.org_id = i.org_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UPDATE XXIFLIC_AP_INVOICE_STAGING
SET interface_status = 'E',
error_message = error_message||'Vendor not exists.'
WHERE SITE_ID = i.SITE_ID
AND INVOICE_NUMBER = i.INVOICE_NUMBER
AND org_id = i.org_id;
lc_error_flag := 'Y';
lc_err_msg := lc_err_msg||' Vendor not exists,';
WHEN OTHERS THEN
UPDATE XXIFLIC_AP_INVOICE_STAGING
SET interface_status = 'E',
error_message = error_message||'Error while fetching Vendor Id.'
WHERE SITE_ID = i.SITE_ID
AND INVOICE_NUMBER = i.INVOICE_NUMBER
AND org_id = i.org_id;
lc_error_flag := 'Y';
lc_err_msg := lc_err_msg||' Error while fetching Vendor Id,';
END;
END IF;
--
--
-- Vendor VENDOR_SITE_CODE Id fetching
--
IF lc_error_flag <> 'Y' THEN
BEGIN
SELECT POVSA.vendor_SITE_ID
,POVSA.vendor_SITE_CODE
INTO ln_vendor_SITE_id
,lc_vendor_SITE_code
FROM po_vendor_SITEs_all POVSA
, po_vendors PV
WHERE pv.vendor_id = povsa.vendor_id
and povsa.vendor_site_id = i.site_id
and povsa.org_id = i.org_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UPDATE XXIFLIC_AP_INVOICE_STAGING
SET interface_status = 'E',
error_message = error_message||'Vendor VENDOR_SITE_ID not exists.'
WHERE SITE_ID = i.SITE_ID
AND INVOICE_NUMBER = i.INVOICE_NUMBER
AND org_id = i.org_id;
lc_error_flag := 'Y';
lc_err_msg := lc_err_msg||' Vendor VENDOR_SITE_IDnot exists,';
WHEN OTHERS THEN
UPDATE XXIFLIC_AP_INVOICE_STAGING
SET interface_status = 'E',
error_message = error_message||'Error while fetching Vendor VENDOR_SITE_ID.'
WHERE SITE_ID = i.SITE_ID
AND INVOICE_NUMBER = i.INVOICE_NUMBER
AND org_id = i.org_id;
lc_error_flag := 'Y';
lc_err_msg := lc_err_msg||' Error while fetching Vendor VENDOR_SITE_ID,';
END;
END IF;
--
IF ln_vendor_site_check <> 0 THEN
i.SITE_ID := ln_orig_site_id;
END IF;
--
-- Term Id fetching
--
IF lc_error_flag <> 'Y' THEN
BEGIN
SELECT pvsa.terms_id
,apt.name
INTO ln_terms_id
,lc_terms_name
FROM po_vendor_SITEs_all PVSA
, ap_terms apt
WHERE PVSA.vendor_SITE_ID = i.SITE_ID
AND pvsa.terms_id = apt.term_id
AND org_id = ln_ou;
EXCEPTION
WHEN OTHERS THEN
ln_terms_id := NULL;
lc_err_msg := lc_err_msg||NULL;--' Error while fetching term id,';
END;
END IF;
IF lc_error_flag <> 'Y' THEN
ln_invoice_amount := 0;
ln_inv_count := ln_inv_count+1;
ln_rej_count := ln_rej_count+1; --Added by Srinivas
--
-- Inserting into AP_INVOICES_INTERFACE Table
--
IF NVL(lc_INVOICE_CURRENCY_CODE,'INR') = 'INR' THEN
ln_invoice_amount := i.invoice_amount;
lc_exchange_rate_type := NULL;
ELSE
ln_invoice_amount := NVL(i.invoice_amount,0);
lc_exchange_rate_type := 'User';
END IF;
BEGIN
INSERT INTO ap_invoices_interface(
invoice_id,
invoice_num,
INVOICE_TYPE_LOOKUP_CODE,
invoice_date,
po_number,
vendor_id,
vendor_num,
--vendor_name,
vendor_SITE_id,
vendor_SITE_code,
invoice_amount,
INVOICE_CURRENCY_CODE,
exchange_rate,
exchange_rate_type,
exchange_date,
terms_id,
terms_name,
description,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
source,
group_id,
gl_date,
--accts_pay_code_combination_id,
org_id,
DOC_CATEGORY_CODE,
--attribute15
PREPAY_NUM
,PREPAY_APPLY_AMOUNT
,INVOICE_INCLUDES_PREPAY_FLAG
,PREPAY_GL_DATE
,pay_group_lookup_code
,PREPAY_DIST_NUM
,attribute_category
,attribute1
-- ,attribute2
-- ,attribute3
-- ,attribute4
-- ,attribute5
,PAYMENT_METHOD_LOOKUP_CODE
) VALUES (
ln_invoice_id_h, -- invoice_id
i.INVOICE_NUMBER, -- invoice_num
decode(trim(i.INVOICE_TYPE),'Standard Invoice','STANDARD',upper(i.INVOICE_TYPE)), -- INVOICE_TYPE_LOOKUP_CODE_LOOKUP_CODE_LOOKUP_CODE_lookup_code
ld_invoice_date, -- invoice_date
NULL, -- po_number
ln_vendor_id, -- vendor_id
lc_vendor_num, -- vendor_num
--lc_vendor_name, -- vendor_name
ln_vendor_SITE_id, -- vendor_VENDOR_SITE_CODE_id
lc_vendor_SITE_code, -- vendor_VENDOR_SITE_CODE_code
ln_invoice_amount, -- invoice_amount
lc_INVOICE_CURRENCY_CODE, -- INVOICE_CURRENCY_CODE_code
lc_exchange_rate, -- exchange_rate
lc_exchange_rate_type, -- exchange_rate_type
NULL, -- exchange_date
NVL(ln_terms_id,10000), -- terms_id
lc_terms_name, -- terms_name
NVL(lc_description,NULL),-- description
SYSDATE, -- last_update_date
FND_GLOBAL.USER_ID, -- last_updated_by
FND_GLOBAL.USER_ID, -- last_update_login
SYSDATE, -- creation_date
FND_GLOBAL.USER_ID, -- created_by
'MIGRATION',--'MIGRATION_PAYABLE', -- source
NULL, -- group_id
i.gl_date,--gl_date
--ln_laibility_ccid, -- accts_pay_code_combination_id
NVL(i.ORG_ID,FND_GLOBAL.ORG_ID), -- org_id
decode(decode(trim(i.INVOICE_TYPE),'Standard Invoice','STANDARD',upper(i.INVOICE_TYPE)),'STANDARD','STD INV','PREPAYMENT','PREPAY INV','CREDIT','CRM INV','DBM INV'),
--lc_attribute15_ccid
i.prepay_num
,i.PREPAY_APPLY_AMOUNT
,i.INVOICE_INCLUDES_PREPAY_FLAG
,i.prepay_gl_date
,i.PAY_GROUP_LOOKUP_CODE
,l_PREPAY_DIST_NUM
,decode(i.VOUCHER_NUMBER,NULL,NULL,'Vendor Invoice Details')
,i.VOUCHER_NUMBER
--,i.attribute2
--,i.attribute3
--,i.attribute4
--,i.attribute5
,i.PAYMENT_METHOD_LOOKUP_CODE
);
EXCEPTION
WHEN OTHERS THEN
UPDATE XXIFLIC_AP_INVOICE_STAGING
SET interface_status = 'E',
error_message = error_message||'Error while inserting into AP_INVOICES_INTERFACE.'
WHERE SITE_ID = i.SITE_ID
AND INVOICE_NUMBER = i.INVOICE_NUMBER
AND org_id = i.org_id;
lc_error_flag := 'Y';
lc_err_msg := lc_err_msg||' Error while inserting into AP_INVOICES_INTERFACE,'||SQLERRM||'- '||ln_invoice_id_h;
END;
END IF;
--
-- Lines data inserting into ap_invoice_lines_interface
--
FOR j IN c_lines_inf(i.INVOICE_NUMBER,i.SITE_ID)
LOOP
--
-- Invoice Line Id fetching
--
IF lc_error_flag <> 'Y' THEN
--
lv_inv_lukup := NULL;
BEGIN
SELECT lookup_code
INTO lv_inv_lukup
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE LIKE 'INVOICE LINE TYPE'
AND lookup_code = upper(trim(j.line_type_lookup_code));
EXCEPTION
when others then
lv_inv_lukup := NULL;
END;
IF lv_inv_lukup IS NULL THEN
lc_error_flag := 'Y';
lc_err_msg := lc_err_msg||'Invalid Line Type lookup code not be Null,';
END IF;
BEGIN
SELECT gll.chart_of_accounts_id
INTO LN_COA_ID
FROM hr_operating_units hou
,gl_ledgers gll
WHERE hou.set_of_books_id = gll.ledger_id
AND hou.organization_id = i.ORG_ID;
EXCEPTION
WHEN OTHERS THEN
LN_COA_ID := NULL;
END;
--
BEGIN
SELECT fnd_flex_ext.get_ccid('SQLGL',
'GL#',
NVL(LN_COA_ID,50348),
fnd_date.date_to_canonical(SYSDATE),
j.DIST_CODE_CONCATENATED
)
INTO LN_CCD_ID
FROM DUAL;
-- COMMIT;
EXCEPTION
WHEN OTHERS THEN
LN_CCD_ID := NULL;
END;
--
IF LN_CCD_ID = 0 THEN
lc_error_flag := 'Y';
lc_err_msg := lc_err_msg||' Code Combination is Invalid,';
END IF;
--
IF i.INVOICE_TYPE = 'STANDARD' THEN
BEGIN
SELECT code_combination_id
INTO ln_code_combination_id
FROM gl_code_combinations_kfv
WHERE concatenated_segments = j.DIST_CODE_CONCATENATED;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UPDATE XXIFLIC_AP_INVOICE_STAGING
SET interface_status = 'E',
error_message = 'STANDARD Combination Id not exists'
WHERE SITE_ID = i.SITE_ID
AND INVOICE_NUMBER = i.INVOICE_NUMBER
AND org_id = i.org_id
AND DIST_CODE_CONCATENATED = j.DIST_CODE_CONCATENATED;
lc_error_flag := 'Y';
lc_err_msg := lc_err_msg||' Code Combination ID not Exist,';
WHEN OTHERS THEN
UPDATE XXIFLIC_AP_INVOICE_STAGING
SET interface_status = 'E',
error_message = 'Error while fetching STANDARD Combination.'
WHERE SITE_ID = i.SITE_ID
AND INVOICE_NUMBER = i.INVOICE_NUMBER
AND org_id = i.org_id
AND DIST_CODE_CONCATENATED = j.DIST_CODE_CONCATENATED;
lc_error_flag := 'Y';
lc_err_msg := lc_err_msg||' Error while fetching STANDARD Combination,';
END;
/*
ELSIF i.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT' THEN
BEGIN
SELECT code_combination_id
INTO ln_code_combination_id
FROM gl_code_combinations_kfv
WHERE concatenated_segments = lc_prepayment_account;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UPDATE XXIFLIC_AP_INVOICE_STAGING
SET interface_status = 'E',
error_message = 'PREPAYMENT Combination Id not exists'
WHERE VENDOR_NAME = i.VENDOR_NAME
AND INVOICE_NUM = i.INVOICE_NUM
AND VENDOR_SITE_CODE = i.VENDOR_SITE_CODE;
lc_error_flag := 'Y';
WHEN OTHERS THEN
UPDATE XXIFLIC_AP_INVOICE_STAGING
SET interface_status = 'E',
error_message = 'PREPAYMENT Error while fetching Combination.'
WHERE VENDOR_NAME = i.VENDOR_NAME
AND INVOICE_NUM = i.INVOICE_NUM
AND VENDOR_SITE_CODE = i.VENDOR_SITE_CODE;
lc_error_flag := 'Y';
END;
*/
ELSE
BEGIN
SELECT code_combination_id
INTO ln_code_combination_id
FROM gl_code_combinations_kfv
WHERE concatenated_segments = j.DIST_CODE_CONCATENATED;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UPDATE XXIFLIC_AP_INVOICE_STAGING
SET interface_status = 'E',
error_message = 'CREDIT Combination Id not exists'
WHERE SITE_ID = i.SITE_ID
AND INVOICE_NUMBER = i.INVOICE_NUMBER
AND org_id = i.org_id
AND DIST_CODE_CONCATENATED = j.DIST_CODE_CONCATENATED;
lc_error_flag := 'Y';
lc_err_msg := lc_err_msg||' Code Combination ID not Exist,';
WHEN OTHERS THEN
UPDATE XXIFLIC_AP_INVOICE_STAGING
SET interface_status = 'E',
error_message = 'Error while fetching CREDIT Combination.'
WHERE SITE_ID = i.SITE_ID
AND INVOICE_NUMBER = i.INVOICE_NUMBER
AND org_id = i.org_id
AND DIST_CODE_CONCATENATED = j.DIST_CODE_CONCATENATED;
lc_error_flag := 'Y';
lc_err_msg := lc_err_msg||' Error while fetching code Combination,';
END;
END IF;
END IF;
--
IF lc_error_flag <> 'Y' THEN
--ln_line_count := ln_line_count+1;
--
SELECT ap_invoice_lines_interface_s.NEXTVAL
INTO ln_invoice_id_l
FROM dual;
BEGIN
INSERT INTO ap_invoice_lines_interface(
INVOice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
line_group_number,
amount,
dist_code_combination_id,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
org_id,
description,
--ATTRIBUTE_CATEGORY,
ATTRIBUTE1
)
VALUES (
ln_invoice_id_h, -- INVOICE_ID
ln_invoice_id_l, -- INVOICE_LINE_ID
j.line_number, -- LINE_NUMBER
NVL(upper(trim(j.line_type_lookup_code)),'ITEM'), -- LINE_TYPE_LOOKUP_CODE
NULL, -- LINE_GROUP_NUMBER
--j.invoice_amount, -- AMOUNT
NVL(j.amount,ln_invoice_amount),
ln_code_combination_id,--DIST_CODE_COMBINATION_ID
FND_GLOBAL.USER_ID, -- last_updated_by
SYSDATE, -- last_update_date
FND_GLOBAL.USER_ID, -- last_update_login
FND_GLOBAL.USER_ID, -- created_by
SYSDATE, -- creation_date
NVL(j.org_id,FND_GLOBAL.ORG_ID), -- ORG_ID
NVL(j.line_description,'Migration Invoice'),
--J.DIST_ATTRIBUTE_CATEGORY,
J.EMPLOYEE_NUMBER
);
EXCEPTION
WHEN OTHERS THEN
UPDATE XXIFLIC_AP_INVOICE_STAGING
SET interface_status = 'E',
error_message = error_message||'Error while inserting into AP_INVOICE_LINES_INTERFACE.'
WHERE SITE_ID = i.SITE_ID
AND INVOICE_NUMBER = i.INVOICE_NUMBER
AND org_id = i.org_id
and nvl(line_number,1) = nvl(j.line_number,1);
--
-- Deleting header inf beacuse while inserting in lines data i m getting error
--
--DELETE FROM AP_INVOICES_INTERFACE WHERE invoice_id = ln_invoice_id_h;
lc_error_flag := 'Y';
lc_err_msg := lc_err_msg||'Error while inserting into AP_INVOICE_LINES_INTERFACE,'||SQLERRM;
END;
END IF;
END LOOP;--c_lines_inf
--
--
-- FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Inserted '||'- '||ln_line_count||' LINES OF INVOICE NUMBER '||i.INVOICE_NUM);
--
-- Update interface_status = P in ur staging table after inserting
-- into AP Invoices Interface table
--
ln_line_count := 0;
--
----check for unbalance Invoice Lines ------------
BEGIN
SELECT SUM(amount)
INTO ln_line_amt
FROM XXIFLIC_AP_INVOICE_STAGING
WHERE SITE_ID = i.SITE_ID
AND INVOICE_NUMBER = i.INVOICE_NUMBER
AND org_id = i.org_id;
--AND (INTERFACE_STATUS <> 'E' or (INTERFACE_STATUS is NULL));
EXCEPTION
WHEN OTHERS THEN
ln_line_amt := 0;
END;
--
IF ln_line_amt <> ln_invoice_amt THEN
rollback;
UPDATE XXIFLIC_AP_INVOICE_STAGING
SET interface_status = 'E'
,error_message = error_message||'Unbalnced Invoice Lines'
WHERE SITE_ID = i.SITE_ID
AND INVOICE_NUMBER = i.INVOICE_NUMBER
AND org_id = i.org_id;
--
lc_error_flag := 'Y';
lc_err_msg := lc_err_msg||' ,Unbalnced Invoice Lines' ;
COMMIT;
END IF;
--
IF lc_error_flag <> 'Y'
THEN
UPDATE XXIFLIC_AP_INVOICE_STAGING
SET interface_status = 'P'
WHERE SITE_ID = i.SITE_ID
AND INVOICE_NUMBER = i.INVOICE_NUMBER
AND org_id = i.org_id;
COMMIT;
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'Voucher Number - '||i.VOUCHER_NUMBER||' - '||lc_err_msg);
END IF;
-- Invoice and Line count --
END LOOP;--c_headers_inf
--
--
BEGIN
SELECT COUNT(*)
INTO ln_inv_count
FROM AP_INVOICES_INTERFACE aii
,ap_invoice_lines_interface ail
WHERE aii.invoice_id = ail.invoice_id
AND aii.SOURCE = 'MIGRATION'
AND aii.STATUS IS NULL;
EXCEPTION
WHEN OTHERS THEN
ln_inv_count := 0;
END;
--
DELETE FROM AP_INVOICES_INTERFACE
WHERE SOURCE = 'MIGRATION'
AND INVOICE_ID NOT IN (SELECT INVOICE_ID FROM AP_INVOICE_LINES_INTERFACE);
commit;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Total Invoice lines loaded'||' - '||ln_inv_count);
END ap_interface_proc;
END xxiflic_ap_interface_pkg;
/
|
Post answer
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|