Oracle Applications & Oracle Cloud Fusion HCM : AP Invoice Interface

CREATE OR REPLACE PACKAGE APPS.XXTC_ap_inv AS
PROCEDURE Run_All ( p_errbuf IN OUT VARCHAR2
, p_retcode IN OUT VARCHAR2
, p_input_directory IN VARCHAR2
, p_input_filename IN VARCHAR2);--, p_debug IN VARCHAR2);
PROCEDURE ap_upload_interface ( p_errbuf IN OUT VARCHAR2
, p_retcode IN OUT VARCHAR2
, p_tenant_id IN VARCHAR2
, p_invoice_num IN VARCHAR2
--,p_debug in varchar2
);
END XXTC_ap_inv;
/
CREATE OR REPLACE PACKAGE BODY APPS.xxtc_ap_inv
AS
/***********************************************************************************************************************
* Type : Package Body *
* name : xxtc_ap_inv *
* Script name : xxtc_ap_inv.pkb *
* Purpose fPROCEDURE ap_upload_interface ( : Mri to OFI AP Invoice Interface *
* Version : 115.0 (Initial) *

* Created Date : *
* Version : 115.1 -- For CR47 to Create Credit Memo for - ve values
* Version : 115.2 -- For CR61 *
* Version : 115.3 -- Defect ID 665 Program is not showing error record in output file with error reason *
* Version : 115.4 -- For CR61 release 2 to write error desc into staging table added parameters for re-run *
************************************************************************************************************************
************************************************************************************************************************/
currentstatement VARCHAR2 (180) := '';
localreqid NUMBER := 0;
globalreqid NUMBER := fnd_global.conc_request_id;
sobid NUMBER := 0;
sobname VARCHAR2 (80) := '';
--orgid NUMBER := 0;
respid NUMBER := 0;
applid NUMBER := 0;
loginid NUMBER := 0;
userid NUMBER := -1;
l_seq NUMBER;
g_logfile VARCHAR2 (5);
g_outfile VARCHAR2 (5);
g_input_path VARCHAR2 (250);
g_input_file UTL_FILE.file_type;
g_output_file UTL_FILE.file_type;
g_fserverfldr VARCHAR2 (55);
g_lisfrecord VARCHAR2 (32767);
g_line_count NUMBER := 1;
g_ctl_rec_count NUMBER := 0;
g_ctl_batch_amount NUMBER := 0;
g_rec_count NUMBER := 0;
g_recnum NUMBER := 0;
g_def_source VARCHAR2 (35) := 'MRI';
g_invintgroupid NUMBER := 0;
g_inv_batch VARCHAR2 (35);
l_vendor NUMBER;
l_tax_code VARCHAR2 (25);
ln_org_id NUMBER;
l_batchname VARCHAR2 (50) := '';
req_status BOOLEAN;
v_phase VARCHAR2 (240);
v_status VARCHAR2 (240);
v_dev_phase VARCHAR2 (240);
v_dev_status VARCHAR2 (240);
v_message VARCHAR2 (240);
l_err_msg VARCHAR2 (2000);
l_profile_err_msg VARCHAR2 (2000);
l_err_flag VARCHAR2 (1); -- Version 115.2
l_profile_err_flag VARCHAR2 (1); -- Version 115.4
/*------------------------------------------------------------------------
|| Module :- Getheader amount
|| Description :- This procedure will get the header amount of the invoice
|| Parameters :-
||
||
*/------------------------------------------------------------------------------------
FUNCTION get_header_amount (p_inv_num IN VARCHAR2, p_sup_num IN VARCHAR2)
RETURN NUMBER
IS
l_amount NUMBER;
BEGIN
SELECT ROUND (SUM (line_amount), 2)
INTO l_amount
FROM xxtc_apinv_i_mri x
WHERE x.invoice_num = p_inv_num AND x.vendor_num = p_sup_num
GROUP BY invoice_num, vendor_num;
RETURN l_amount;
END get_header_amount;
--------------------------------------------------------------
/*------------------------------------------------------------------------
|| Module :- insert_supplier_invoices
|| Description :- This procedure will insert the supplier invoices to interface tables
|| Parameters :- Two Parameter Added ||
||
*/
------------------------------------------------------------------------------------
-- PROCEDURE insert_supplier_invoices
PROCEDURE insert_supplier_invoices(p_err_flag OUT VARCHAR2, p_tenant_id IN VARCHAR2,p_invoice_num IN VARCHAR2) -- added Parameters in Version 115.4
IS
l_vendor_id NUMBER;
l_vendor_num NUMBER;
l_vendor_name VARCHAR2 (240);
l_vendor_site NUMBER;
l_document_cat VARCHAR2 (20);
l_description VARCHAR2 (240);
l_payment_method VARCHAR2 (10);
l_pay_group VARCHAR2 (10);
l_pay_alone VARCHAR2 (1);
l_pay_terms VARCHAR2 (10);
l_pay_currency VARCHAR2 (5);
l_line_desc VARCHAR2 (50);
l_header_amt NUMBER;
l_termid NUMBER;
lv_account VARCHAR2 (10);
lv_cost_center VARCHAR2 (10);
lv_product VARCHAR2 (10);
lv_project VARCHAR2 (10);
lv_intercompany VARCHAR2 (10);
lv_cost_center_type VARCHAR2 (10);
l_segment_char VARCHAR2 (240);
l_tax_segment VARCHAR2 (240);
l_liability VARCHAR2 (30);
l_charge_account VARCHAR2 (10);
lv_tax_comp VARCHAR2 (3);
lv_tax_account NUMBER;
lv_tax_cc VARCHAR2 (5);
lv_tax_product VARCHAR2 (10);
lv_tax_project VARCHAR2 (10);
lv_tax_location VARCHAR2 (10);
lv_tax_intercompany VARCHAR2 (10);
lv_tax_cctype VARCHAR2 (10);
l_recovery_rate VARCHAR2 (3) := '';
l_group_id NUMBER;
l_nettable VARCHAR2 (3);
l_bank_info VARCHAR2 (50);
l_count NUMBER := 0;
l_p_charge_account NUMBER;
l_refund_account NUMBER;
l_v_charge_account NUMBER;
l_invoice_type VARCHAR2 (50); -- Version 115.1
l_variance_account VARCHAR2 (25); -- Version 115.2

CURSOR c_main
IS
SELECT invoice_num, vendor_num, vendor_id
FROM xxtc_apinv_i_mri
WHERE line_num = 1
AND vendor_id = DECODE(p_tenant_id,'',vendor_id,p_tenant_id) -- Added
AND invoice_num = DECODE(p_invoice_num,'',invoice_num,p_invoice_num) --Added
AND status IS NULL; -- Added the condition Version 115.2
CURSOR c_head (p_inv_num IN VARCHAR2, p_sup_num IN VARCHAR2)
IS
SELECT invoice_num, invoice_date, vendor_num, invhead_desc,
inv_line_desc, line_amount, line_num, company_code, LOCATION,org_id
FROM xxtc_apinv_i_mri x
WHERE x.line_num = 1
AND x.invoice_num = p_inv_num
AND x.vendor_num = p_sup_num
AND status IS NULL; -- Added the condition version 115.4
CURSOR c_line (p_inv_num1 IN VARCHAR2, p_sup_num1 IN VARCHAR2)
IS
SELECT invoice_num, invoice_date, vendor_num, company_code, LOCATION,
invoice_type, inv_line_desc, line_amount, tax_code, line_num,org_id
FROM xxtc_apinv_i_mri x
WHERE x.invoice_num = p_inv_num1
AND x.vendor_num = p_sup_num1
AND status IS NULL; -- Added the condition version 115.4

BEGIN
l_profile_err_flag := 'N';
l_profile_err_msg := '';
BEGIN
SELECT fpov.profile_option_value
INTO l_p_charge_account
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov
WHERE fpo.profile_option_id = fpov.profile_option_id
AND fpo.profile_option_name = 'XXTC_MRI_COGS_INV_ACCOUNT';
EXCEPTION
WHEN OTHERS THEN
XXTC_CORE.LOG_LINE(' The default account for COGS invoice lines is not set up. Please ensure that the account is set up in the profile option XXTC MRI COGS Invoice Account');
l_profile_err_msg := l_profile_err_msg ||'-'||'The default account for COGS invoice lines is not set up. Please ensure that the account is set up in the profile option XXTC MRI COGS Invoice Account';
l_profile_err_flag := 'Y';
END;
BEGIN
SELECT fpov.profile_option_value
INTO l_refund_account
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov
WHERE fpo.profile_option_id = fpov.profile_option_id
AND fpo.profile_option_name =
'XXTC_MRI_REFUND_INV_ACCOUNT';
EXCEPTION
WHEN OTHERS THEN
XXTC_CORE.LOG_LINE(' The default account for REFUND invoice lines is not set up. Please ensure that the account is set up in the profile option XXTC ¿MRI REFUND Invoice Account');
l_profile_err_msg := l_profile_err_msg ||'-'||'The default account for REFUND invoice lines is not set up. Please ensure that the account is set up in the profile option XXTC ¿MRI REFUND Invoice Account';
l_profile_err_flag := 'Y';
END;
/* Added to get Variance Account in Version 115.2*/
BEGIN
SELECT fpov.profile_option_value
INTO l_variance_account
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov
WHERE fpo.profile_option_id = fpov.profile_option_id
AND fpo.profile_option_name = 'XXTC_MRI_COGS_VAR_ACCOUNT';
EXCEPTION
WHEN OTHERS THEN
XXTC_CORE.LOG_LINE(' The default account for COGS variance invoice lines is not set up. Please ensure that the account is set up in the profile option XXTC ¿MRI COGS variance Invoice Account');
l_profile_err_msg := l_profile_err_msg ||'-'||'The default account for COGS variance invoice lines is not set up. Please ensure that the account is set up in the profile option XXTC ¿MRI COGS variance Invoice Account';
l_profile_err_flag := 'Y';
END;
/* END of Version 115.2 */
p_err_flag := 'N'; -- version 115.2
FOR cv_main IN c_main
LOOP
l_err_flag := 'N'; -- Added Vesrion 115.2
FOR cv_head IN c_head (cv_main.invoice_num, cv_main.vendor_num)
LOOP

--vendor_num and name
BEGIN
SELECT segment1, vendor_name, vendor_id
INTO l_vendor_num, l_vendor_name, l_vendor_id
FROM po_vendors
WHERE attribute15 = cv_main.vendor_id
AND NVL (end_date_active, SYSDATE + 1) > SYSDATE
AND NVL (end_date_active, SYSDATE + 1) > SYSDATE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
-- xxtc_core.log_line
-- ( 'The supplier does not exist or is inactive in OFi for this MRI Tenant ID. Please ensure that the supplier is set up or re-activated before reprocessing the invoices'
-- || cv_main.vendor_id
-- );
l_err_msg := l_err_msg ||'-'||'The supplier does not exist or is inactive in OFi for this MRI Tenant ID. Please ensure that the supplier is set up or re-activated before reprocessing the invoices';
l_err_flag := 'Y';
END;
--vendor_site_id
BEGIN
SELECT vendor_site_id
INTO l_vendor_site
FROM po_vendor_sites
WHERE vendor_id = (SELECT vendor_id
FROM po_vendors
WHERE segment1 = cv_main.vendor_num)
AND vendor_site_code = 'MRI Tenant'
AND NVL (inactive_date, SYSDATE + 1) > SYSDATE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
-- xxtc_core.log_line
-- ( 'MRI Site doesnt exist for supplier:'
-- || cv_main.vendor_num
-- );
l_err_msg := l_err_msg ||'-'||'The supplier does not have an MRI Site. Please ensure that the MRI site is set up before reprocessing the invoices';
l_err_flag := 'Y';
END;
--document category
l_document_cat := cv_head.company_code || '_PURCHASE';
--Payment_terms
BEGIN
SELECT NAME, term_id
INTO l_pay_terms, l_termid
FROM ap_terms
WHERE term_id = (SELECT terms_id
FROM po_vendor_sites_all
WHERE vendor_site_id = l_vendor_site);
EXCEPTION
WHEN OTHERS
THEN
-- xxtc_core.log_line (SQLERRM || 'Error while fetching Terms');

l_err_msg := l_err_msg ||'-'||'The supplier does not have valid Payment Terms. Please ensure that valid terms are set up';
l_err_flag := 'Y';

END;
--Description
IF cv_head.invhead_desc = 'FICE COGS'
THEN
l_description := cv_head.invhead_desc
|| ' - '
|| TO_CHAR (ADD_MONTHS (SYSDATE, -1),
'YYYY' || ',' || 'Mon' || ' DD '
)
|| cv_head.invoice_num;
ELSIF cv_head.invhead_desc = 'CASH TAKING'
THEN
l_description := cv_head.invhead_desc
|| '-'
|| TO_CHAR (ADD_MONTHS (SYSDATE, -1),
'YYYY' || ',' || 'Mon' || ' DD'
);
END IF;
-- Payment Method,pay_alone,payment Currency
SELECT payment_method_lookup_code, exclusive_payment_flag,
payment_currency_code
INTO l_payment_method, l_pay_alone,
l_pay_currency
FROM po_vendor_sites
WHERE vendor_site_id = l_vendor_site;
--Pay Group
l_pay_group := cv_head.company_code || '_MRI';
--latest interface group id using the AP_INVOICES_INTERFACE_S sequence
SELECT ap_invoices_interface_s.NEXTVAL
INTO l_group_id
FROM DUAL;
SELECT attribute6
INTO l_nettable
FROM po_vendor_sites
WHERE vendor_site_id = l_vendor_site;
xxtc_core.log_line ('l_nettable:' || l_nettable);
--Bank account info
BEGIN
xxtc_core.log_line ('Suppl num: ' ||cv_main.vendor_num);
xxtc_core.log_line ('Suppl Site: ' || l_vendor_site);
SELECT bank_account_num
INTO l_bank_info
FROM ap_bank_account_uses_v
WHERE NVL (end_date, SYSDATE + 1) > SYSDATE
AND account_type = 'SUPPLIER'
AND vendor_site_id = l_vendor_site
AND primary_flag = 'Y';
EXCEPTION
WHEN OTHERS
THEN
-- xxtc_core.log_line
-- ( SQLERRM
-- || 'Error while fetching Bank account info'
-- );

l_err_msg := l_err_msg ||'-'||'Error while fetching bank account for this Site ID';
l_err_flag := 'Y';
END;
--liability account defaulted from supplier
BEGIN
SELECT gll.segment2, gll.segment3, gll.segment4,
gll.segment5, gll.segment7, gll.segment8
INTO lv_account, lv_cost_center, lv_product,
lv_project, lv_intercompany, lv_cost_center_type
FROM po_vendor_sites_all pov, gl_code_combinations gll
WHERE gll.code_combination_id =
pov.accts_pay_code_combination_id
AND pov.vendor_id = l_vendor_id
AND pov.vendor_site_code LIKE 'MRI%Tenant%'
AND ( pov.inactive_date IS NULL
OR TRUNC (pov.inactive_date) >= TRUNC (SYSDATE)
);
l_segment_char := cv_head.company_code
|| '.'
|| lv_account
|| '.'
|| lv_cost_center
|| '.'
|| lv_product
|| '.'
|| lv_project
|| '.'
|| cv_head.LOCATION
|| '.'
|| lv_intercompany
|| '.'
|| lv_cost_center_type;
-- FND_FILE.PUT_LINE( FND_FILE.LOG,'Liability Account'|| l_segment_char);
l_liability :=
fnd_flex_ext.get_ccid
(application_short_name => 'SQLGL',
key_flex_code => 'GL#',
structure_number => 50269,
validation_date => TO_CHAR
(SYSDATE,
'YYYY/MM/DD HH24:MI:SS'
),
concatenated_segments => l_segment_char
);
IF l_liability = 0
THEN
-- fnd_file.put_line (fnd_file.LOG,
-- 'Fails to get liability account'
-- );

l_err_msg := l_err_msg ||'-'||'Error while fetching liability account for this Site ID';
l_err_flag := 'Y';
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Program fails to get liability account'
);
l_err_msg := l_err_msg ||'-'||'fails to get the liability account';
l_err_flag := 'Y';
END;
xxtc_core.log_line ('INSIDE INSERT LOOP ');
l_header_amt := get_header_amount (cv_main.invoice_num,
cv_main.vendor_num
);
xxtc_core.log_line ('l_header_amt := ' || l_header_amt);
IF (l_header_amt < 0) THEN -- Added IF Condition version 115.1
l_invoice_type := 'CREDIT';
ELSE
l_invoice_type := 'STANDARD';
END IF;
BEGIN
INSERT INTO ap.ap_invoices_interface
(invoice_id,
invoice_num, invoice_type_lookup_code,
invoice_date, vendor_id,
vendor_num, vendor_site_id, description,
invoice_amount, invoice_currency_code,
last_update_date, last_updated_by,
last_update_login, creation_date, created_by,
doc_category_code, pay_group_lookup_code,
attribute1, attribute3,
attribute4, SOURCE, GROUP_ID,
accts_pay_code_combination_id,
org_id --IR 181905
)
VALUES (ap_invoices_interface_s.NEXTVAL,
cv_main.invoice_num,
-- 'STANDARD', -- Commented Version 115.1
l_invoice_type, -- Added the column Version 115.1
TO_DATE (cv_head.invoice_date), l_vendor_id,
cv_head.vendor_num, l_vendor_site, l_description,
l_header_amt, 'CNY',
SYSDATE, userid,
loginid, SYSDATE, userid,
l_document_cat, l_pay_group,
cv_head.company_code || '_PURCHASE', l_nettable,
l_bank_info, g_def_source, l_group_id,
l_liability,
cv_head.org_id --IR 181905
);
EXCEPTION -- Exception Block added Version 115.2
WHEN OTHERS
THEN
xxtc_core.log_line
( 'Error while inserting into interface Headers: '
|| SQLERRM
);
xxtc_core.log_line
('Vendor Number: '||cv_main.vendor_num||'Invoice Number: '||cv_main.invoice_num
);

l_err_msg := l_err_msg ||'-'||'Error while inserting: '||SQLERRM;
l_err_flag := 'Y';
END;
-- Print The Report Body
/* xxtc_core.out_line ( cv_main.invoice_num
|| ' '
|| TO_DATE (cv_head.invoice_date)
|| ' '
|| cv_head.vendor_num
|| ' '
|| l_vendor_site
|| ' '
|| l_header_amt
);*/ -- Commented by Jakki Reddy

END LOOP;
IF l_err_flag = 'N' THEN --C1
FOR cv_line IN c_line (cv_main.invoice_num, cv_main.vendor_num)
LOOP
xxtc_core.log_line ('Inside Invoice lines insert');
l_segment_char := NULL;
l_charge_account := NULL;
l_line_desc := cv_line.inv_line_desc
|| ' - '
|| TO_CHAR (ADD_MONTHS (SYSDATE, -1),
'YYYY' || ', ' || 'Mon'
);
IF cv_line.tax_code IS NOT NULL
THEN
BEGIN
SELECT tax_recovery_rate
INTO l_recovery_rate
FROM ap_tax_codes
WHERE NAME = cv_line.tax_code;
xxtc_core.log_line ('Recovery Rate:' || l_recovery_rate);
EXCEPTION
WHEN OTHERS
THEN
-- xxtc_core.log_line
-- ( 'Error while fetching Recovery Rate:'
-- || SQLERRM
-- );
l_err_msg := l_err_msg ||'-'||'Error while fetching Tax Recovery Rate for the given Tax Code';
l_err_flag := 'Y';
END;
END IF;
--Charge Account
BEGIN
IF cv_line.inv_line_desc = 'FICE COGS'
AND cv_line.invoice_type = 'P'
THEN
l_charge_account:= l_p_charge_account;
l_segment_char := cv_line.company_code
|| '.'
|| l_charge_account
|| '.00000.000.000000.'
|| cv_line.LOCATION
|| '.000.0000';
/* UPDATE xxtc_apinv_i_mri
SET dist_account = l_segment_char
WHERE invoice_num = cv_line.invoice_num
-- AND invoice_date=cv_line.invoice_date
AND vendor_num = cv_line.vendor_num
AND invoice_type = 'P'
AND line_num = cv_line.line_num;*/
/* ELSIF cv_line.inv_line_desc = 'FICE COGS TAX'
AND cv_line.invoice_type = 'T'
THEN
SELECT fpov.profile_option_value
INTO l_charge_account
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov
WHERE fpo.profile_option_id = fpov.profile_option_id
AND fpo.profile_option_name = 'XXTC_MRI_COGS_INV_ACCOUNT';
l_segment_char := cv_line.company_code
|| '.'
|| '30150'
|| '.00000.000.000000.'
|| cv_line.LOCATION
|| '.000.0000';*/
/* UPDATE xxtc_apinv_i_mri
SET dist_account = l_segment_char
WHERE invoice_num = cv_line.invoice_num
-- AND invoice_date=cv_line.invoice_date
AND vendor_num = cv_line.vendor_num
AND company_code = cv_line.company_code
AND LOCATION = cv_line.LOCATION
AND invoice_type = cv_line.invoice_type
AND inv_line_desc = cv_line.inv_line_desc
AND line_amount = cv_line.line_amount
-- AND tax_code=cv_line.tax_code
AND line_num = cv_line.line_num;*/
ELSIF cv_line.inv_line_desc = 'FICE COGS VAR'
AND cv_line.invoice_type = 'V'
THEN
l_charge_account:= l_p_charge_account;
l_segment_char := cv_line.company_code
|| '.'
-- || '30150' -- Commented Version 115.2
|| l_variance_account -- Added Version 115.2
|| '.00000.000.000000.'
|| cv_line.LOCATION
|| '.000.0000';
/* UPDATE xxtc_apinv_i_mri
SET dist_account = l_segment_char
WHERE invoice_num = cv_line.invoice_num
-- AND invoice_date=cv_line.invoice_date
AND vendor_num = cv_line.vendor_num
AND company_code = cv_line.company_code
AND LOCATION = cv_line.LOCATION
AND invoice_type = cv_line.invoice_type
AND inv_line_desc = cv_line.inv_line_desc
AND line_amount = cv_line.line_amount
-- AND nvl(tax_code,'')=cv_line.tax_code
AND line_num = cv_line.line_num;*/
ELSIF cv_line.inv_line_desc = 'REFUND TENANT''S TAKING'
AND cv_line.invoice_type = 'P'
THEN
l_charge_account:= l_refund_account ;
l_segment_char := cv_line.company_code
|| '.'
|| l_charge_account
|| '.00000.000.000000.'
|| cv_line.LOCATION
|| '.000.0000';
fnd_file.put_line (fnd_file.LOG,
'l_segment_char: ' || l_segment_char
);
/* UPDATE xxtc_apinv_i_mri
SET dist_account = l_segment_char
WHERE invoice_num = cv_line.invoice_num
AND invoice_date = cv_line.invoice_date
AND vendor_num = cv_line.vendor_num
AND company_code = cv_line.company_code
AND LOCATION = cv_line.LOCATION;*/
END IF;
EXCEPTION
WHEN OTHERS
THEN
-- fnd_file.put_line (fnd_file.LOG,
-- 'Program fails to get Charge account'
-- );

l_err_msg := l_err_msg ||'-'||'Error while fectching Charge Account for this Site ID';
l_err_flag := 'Y';
END;
IF cv_line.invoice_type='T' THEN
BEGIN
SELECT gll.segment1, gll.segment2, gll.segment3,
gll.segment4, gll.segment5, gll.segment6,
gll.segment7, gll.segment8
INTO lv_tax_comp, lv_tax_account, lv_tax_cc,
lv_tax_product, lv_tax_project, lv_tax_location,
lv_tax_intercompany, lv_tax_cctype
FROM ap_tax_codes_all apc, gl_code_combinations gll
WHERE gll.code_combination_id = apc.tax_code_combination_id
AND apc.enabled_flag = 'Y'
AND ( apc.inactive_date IS NULL
OR TRUNC (apc.inactive_date) > TRUNC (SYSDATE)
)
AND NAME = cv_line.tax_code;
EXCEPTION
WHEN OTHERS
THEN
xxtc_core.log_line
( 'Error while fetching Tax accounting info: '
|| SQLERRM
);

l_err_msg := l_err_msg ||'-'||'Error while fetching Tax accounting info';
l_err_flag := 'Y';
END;
l_tax_segment := lv_tax_comp
|| '.'
|| lv_tax_account
|| '.'
|| lv_tax_cc
|| '.'
|| lv_tax_product
|| '.'
|| lv_tax_project
|| '.'
|| lv_tax_location
|| '.'
|| lv_tax_intercompany
|| '.'
|| lv_tax_cctype;
fnd_file.put_line (fnd_file.LOG,
'l_tax_segment: ' || l_tax_segment
);
END IF;
BEGIN
INSERT INTO ap.ap_invoice_lines_interface
(invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount, accounting_date,
description,
tax_code,
tax_recovery_rate, last_updated_by,
last_update_date, last_update_login, created_by,
creation_date,
dist_code_concatenated,
org_id --IR 181905
)
VALUES (ap_invoices_interface_s.CURRVAL,
ap_invoice_lines_interface_s.NEXTVAL,
cv_line.line_num,
DECODE (cv_line.invoice_type,
'P', 'ITEM',
'T', 'TAX',
'V', 'ITEM'
),
ROUND (cv_line.line_amount, 2), SYSDATE,
l_line_desc,
DECODE (cv_line.invoice_type,
'P', NULL,
'T', cv_line.tax_code,
'V', NULL
),
l_recovery_rate, '1',
SYSDATE, '1', '1',
SYSDATE,
DECODE (cv_line.invoice_type,
'P', l_segment_char,
'T', l_tax_segment,
'V', l_segment_char
),
cv_line.org_id --IR 181905
);
EXCEPTION
WHEN OTHERS
THEN
xxtc_core.log_line
( 'Error while inserting into interface lines: '
|| SQLERRM
);
xxtc_core.log_line
('Vendor Num: '||cv_line.vendor_num||' '||'Invoice Num: '||cv_line.invoice_num||'Line Num: '||cv_line.line_num
);

l_err_msg := l_err_msg ||'-'||'Error while inserting into interface lines: '||SQLERRM;
l_err_flag := 'Y';
-- l_err_flag := 'Y'; -- Added Version 115.2
-- l_err_msg := SQLERRM; -- Added Version 115.2

END;
/* l_count:= l_count +1;
if l_count=1000 then
commit;
l_count :=0;
end if; */ -- COmmented Version 115.2
END LOOP; -- C3
END IF;

IF l_profile_err_flag = 'Y' THEN /* Added in version 115.4 */
l_err_flag := 'Y';
l_err_msg := l_profile_err_msg||'-'||l_err_msg;
END IF;

IF l_err_flag = 'Y' THEN -- Added IF Block Version 115.2
ROLLBACK;
UPDATE xxtc_apinv_i_mri xai
SET status = 'ERROR',
error_message = l_err_msg
WHERE xai.vendor_num = cv_main.vendor_num
AND xai.invoice_num = cv_main.invoice_num;
COMMIT;
l_err_msg := '';
p_err_flag := 'Y';
ELSE
COMMIT;
UPDATE xxtc_apinv_i_mri xai --Added by Jakki Reddy version 115.4
SET status = 'PROCESS',
error_message = l_err_msg
WHERE xai.vendor_num = cv_main.vendor_num
AND xai.invoice_num = cv_main.invoice_num;
l_err_msg := '';
COMMIT;
END IF;
END LOOP; -- C_HDR
-- COMMIT; -- Commented Version 115.2
EXCEPTION
WHEN OTHERS
THEN
xxtc_core.log_line ( 'Insert supplier invoices completed in error'
|| SQLERRM
);
-- l_err_msg := l_err_msg ||'-'||'Insert supplier invoices completed in error: '||SQLERRM;
-- l_err_flag := 'Y';
ROLLBACK;
-- x_retcode :=1;
--- x_errbuf :='Invoices have errored out';
END insert_supplier_invoices;
/*------------------------------------------------------------------------
|| Module :- ap_upload_interface
|| Description :- This procedure
|| Parameters :-
||
||
*/------------------------------------------------------------------------------------
PROCEDURE ap_upload_interface (
p_errbuf IN OUT VARCHAR2,
p_retcode IN OUT VARCHAR2,
p_tenant_id IN VARCHAR2,
p_invoice_num IN VARCHAR2
--, p_debug IN VARCHAR2
) --Two parameters have been added
IS
l_count NUMBER := 0;
l_err_flag VARCHAR2(1);
v_invoice_num VARCHAR2(100);
v_vendor_id VARCHAR2(20);
v_status VARCHAR2(20);
v_error_message VARCHAR2(400);
CURSOR c_main
IS
SELECT invoice_num, vendor_id
FROM xxtc_apinv_i_mri
WHERE vendor_id = DECODE(p_tenant_id,'',vendor_id,p_tenant_id) --Added
AND invoice_num = DECODE(p_invoice_num,'',invoice_num,p_invoice_num) --Added
AND STATUS IS NULL -- Added Version 115.2
GROUP BY vendor_id, invoice_num;
x_count NUMBER := 0;

CURSOR c_sub (p_inv_num IN VARCHAR2, p_sup_num IN VARCHAR2)
IS
SELECT invoice_num, vendor_id, invoice_date, line_amount, tax_code,
invoice_type, company_code, LOCATION
FROM xxtc_apinv_i_mri x
WHERE x.invoice_num = p_inv_num AND x.vendor_id = p_sup_num AND status IS NULL;

/* CURSOR cur_clear_error --Added by Jakki Reddy version 115.4
IS
SELECT invoice_num,vendor_id,status,error_message
FROM xxtc_apinv_i_mri_all
WHERE status = 'ERROR';*/

CURSOR cur_errors --Added by Jakki Reddy version 115.4
IS
SELECT NVL(vendor_id,' ') MRI_Tenant_id, NVL(location,' ') Site_id, Invoice_date, NVL(Invoice_num,' ') Invoice_num,
NVL(Invoice_Type,' ') invoice_type,NVL(status,' ') status,NVL(error_message,' ') error_message
FROM xxtc.xxtc_apinv_i_mri_all
WHERE vendor_id = DECODE(p_tenant_id,'',vendor_id,p_tenant_id)
AND invoice_num = DECODE(p_invoice_num,'',invoice_num,p_invoice_num)
AND status = 'ERROR';
BEGIN
/* This block will be clean up the error information */
BEGIN --Added
UPDATE xxtc_apinv_i_mri
SET status = NULL , error_message = NULL
WHERE vendor_id = p_tenant_id
AND invoice_num = p_invoice_num;

EXCEPTION
WHEN NO_DATA_FOUND
THEN
xxtc_core.log_line(' No data to be clear the error info');
WHEN OTHERS
THEN
xxtc_core.log_line ( 'Error while clearing error status: '
|| SQLERRM
);
END;
BEGIN
UPDATE xxtc_apinv_i_mri a
SET vendor_num = (SELECT segment1
FROM po_vendors pv
WHERE pv.attribute15 = a.vendor_id);

xxtc_core.log_line('Rows updated: '||SQL%rowcount);
-- The below line commented by Anand LN as it thiws will appear for correct suppliers also

UPDATE xxtc_apinv_i_mri a
SET status='ERROR',
error_message= error_message||'-'||'The supplier does not exist or is inactive in OFi for this MRI Tenant ID. Please ensure that the supplier is set up or re-activated before reprocessing the invoices'
WHERE NOT EXISTS (SELECT segment1
FROM po_vendors pv
WHERE pv.attribute15 = a.vendor_id);
IF SQL%FOUND THEN -- Added IF Block Version 115.2
p_retcode := 1;
END IF;

EXCEPTION
WHEN OTHERS
THEN
xxtc_core.log_line ( 'Error while updating vendor Number: '
|| SQLERRM
);
p_retcode := 1;
END;
-- xxtc_core.out_line ('Updating staging table,step 1');
FOR cv_main IN c_main
LOOP
/* BEGIN
SELECT segment1
INTO l_vendor
FROM po_vendors
WHERE attribute15 = cv_main.vendor_id;
UPDATE xxtc_apinv_i_mri
SET vendor_num = l_vendor
WHERE vendor_id = cv_main.vendor_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
xxtc_core.out_line ('Supplier doesnt exist in OFI');
END;*/
x_count := 0;
FOR cv_sub IN c_sub (cv_main.invoice_num, cv_main.vendor_id)
LOOP
x_count := x_count + 1;
-- xxtc_core.OUT_LINE('Updating staging table,step 2');
UPDATE xxtc_apinv_i_mri
SET line_num = x_count
WHERE invoice_num = cv_sub.invoice_num
AND vendor_id = cv_sub.vendor_id
-- AND invoice_date = cv_sub.invoice_date
AND line_amount = cv_sub.line_amount;
END LOOP;
FOR cv_sub IN c_sub (cv_main.invoice_num, cv_main.vendor_id)
LOOP
-- xxtc_core.OUT_LINE(sql%rowcount||'Updating staging table,step 3');
IF cv_sub.tax_code IS NOT NULL
THEN
l_tax_code := cv_sub.company_code
|| '_'
|| cv_sub.LOCATION
|| '_NT_'
|| SUBSTR (cv_sub.tax_code, 4, 3);
END IF;
xxtc_core.log_line ( 'l_tax_code: '
|| l_tax_code
|| 'sysdate '
|| SYSDATE
);
IF cv_sub.invoice_type = 'T'
THEN
BEGIN
UPDATE xxtc_apinv_i_mri
SET tax_code = l_tax_code --'B66_0VAT'--a
WHERE invoice_num = cv_main.invoice_num
AND vendor_id = cv_main.vendor_id
AND invoice_type = 'T';
UPDATE xxtc_apinv_i_mri
SET tax_code = NULL
WHERE invoice_num = cv_main.invoice_num
AND vendor_id = cv_main.vendor_id
AND invoice_type = 'P';
EXCEPTION
WHEN OTHERS
THEN
xxtc_core.out_line ( 'Error while updating tax_code '
|| SQLERRM
);
END;
END IF;
l_count:= l_count +1;
-- IF l_count=1000 THEN -- commented by Jakki reddy
-- COMMIT;
-- l_count :=0;
-- END IF;
COMMIT;
END LOOP; --C2
END LOOP;
-- xxtc_core.OUT_LINE('Updated staging table');
BEGIN
-- insert_supplier_invoices;
insert_supplier_invoices(l_err_flag,p_tenant_id,p_invoice_num); -- Parameter added Version 115.2
IF l_err_flag = 'Y' THEN -- Added IF Block Version 115.2
p_retcode := 1;
END IF;
EXCEPTION
WHEN OTHERS
THEN
xxtc_core.log_line ('Error in insert_supplier_invoices');
END;
COMMIT;
/* xxtc_core.out_line(' ------------------------------------------------------------------------------------');
xxtc_core.out_line(' --------------------- Error details report Table -----------------------------');
xxtc_core.out_line(' ------------------------------------------------------------------------------------');
xxtc_core.out_line('Invoice_num MRI_Cus_Num Status Error Message');
xxtc_core.out_line(' ------------------------------------------------------------------------------------');
BEGIN
FOR ref_error IN i_error
LOOP
SELECT RPAD(ref_error.Invoice_num,25,' '),
RPAD(LPAD(ref_error.vendor_id,5,0),15,' '),
RPAD(ref_error.status,10,' '),
ref_error.error_message
INTO v_invoice_num,v_vendor_id,v_status,v_error_message
FROM DUAL;
xxtc_core.out_line(v_invoice_num||'|'||v_vendor_id||'|'||v_status||'|'||v_error_message);
p_retcode := 1;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
xxtc_core.log_line (SQLERRM || 'Error while printing report output for error data');
END;
*/
--Error report printing started
BEGIN -- Added this block by Jakki Reddy version 115.3
xxtc_core.out_line(' ');
xxtc_core.out_line('Error Details Report');
xxtc_core.out_line('==================== ');
xxtc_core.out_line(' ');
xxtc_core.out_line(RPAD('+',24,'-')||RPAD('+',26,'-') ||RPAD('+',17,'-') ||RPAD('+',21,'-')|| RPAD('+',3,'-')||RPAD('+',8,'-')||RPAD('+',201,'-') || '+');
xxtc_core.out_line
(
'|'
|| RPAD ('MRI National Tenant ID', 23, ' ')
|| '|'
|| RPAD ('OFI Customer Site Number', 25, ' ')
|| '|'
|| RPAD ('MRI Invoice Date', 16, ' ')
|| '|'
|| RPAD ('MRI Invoice Number', 20, ' ')
|| '|'
|| RPAD ('MRI Invoice Type', 2, ' ')
|| '|'
|| RPAD ('Error Status', 7, ' ')
|| '|'
|| RPAD ('Error Message', 200, ' ')
|| '|'
);
xxtc_core.out_line(RPAD('+',24,'-') ||RPAD('+',26,'-')||RPAD('+',17,'-') ||RPAD('+',21,'-')|| RPAD('+',3,'-')||RPAD('+',8,'-')||RPAD('+',201,'-') || '+');
FOR c_errors IN cur_errors
LOOP
xxtc_core.out_line
(
'|'
|| RPAD (c_errors.mri_tenant_id, 23, ' ')
|| '|'
|| RPAD (c_errors.site_id, 25, ' ')
|| '|'
|| RPAD (c_errors.invoice_date, 16, ' ')
|| '|'
|| RPAD (c_errors.invoice_num, 20, ' ')
|| '|'
|| RPAD (c_errors.invoice_type, 2, ' ')
|| '|'
|| RPAD (c_errors.status, 7, ' ')
|| '|'
|| RPAD (c_errors.error_message, 200, ' ')
|| '|'
);
END LOOP;
xxtc_core.out_line(RPAD('+',24,'-') ||RPAD('+',26,'-')||RPAD('+',17,'-') ||RPAD('+',21,'-')|| RPAD('+',3,'-')||RPAD('+',8,'-')||RPAD('+',201,'-') || '+');
xxtc_core.out_line(' ');
EXCEPTION
WHEN OTHERS
THEN
xxtc_core.log_line ( 'Error while writing the error detail report'
|| SQLERRM
);
END; -- printing ended
EXCEPTION
WHEN OTHERS
THEN
xxtc_core.log_line (SQLERRM || 'Error while updating staging table');
END;
/*------------------------------------------------------------------------
|| Module :- Run_all
|| Description :- This procedure
|| Parameters :-p_input_directory name and file which need to be processes
||
||
*/------------------------------------------------------------------------------------
PROCEDURE run_all (
p_errbuf IN OUT VARCHAR2,
p_retcode IN OUT VARCHAR2,
p_input_directory IN VARCHAR2,
p_input_filename IN VARCHAR2
)
-- , p_debug IN VARCHAR2)
IS
CURSOR c_file (cp_input_directory IN VARCHAR2)
IS
SELECT file_name, ROWID
FROM xxtc_interface_filenames
WHERE interface_directory = cp_input_directory
ORDER BY file_name;
r_file c_file%ROWTYPE;
ln_request_id NUMBER;
lv_request_id NUMBER;
l_req_invval_id NUMBER;
l_batch_id NUMBER := '';
lv_wfr_phase VARCHAR2 (100) := '';
lv_wfr_status VARCHAR2 (100) := '';
lv_wfr_dev_phase VARCHAR2 (100) := '';
lv_wfr_dev_status VARCHAR2 (100) := '';
lv_wfr_message VARCHAR2 (1000) := '';
lb_req_status BOOLEAN;
l_use_batch VARCHAR2 (3);
BEGIN
ln_org_id := fnd_global.org_id;
xxtc_core.log_line ('org' || ln_org_id);
userid := fnd_profile.VALUE ('USER_ID');
loginid := fnd_global.login_id;
respid := fnd_global.resp_id;
sobid := fnd_profile.VALUE ('GL_SET_OF_BKS_ID');
-----------------------------------------------------------------------------
-- Upload file names and Write Report header
-----------------------------------------------------------------------------
xxtc_core.log_line ('ReportHeader: ');
xxtc_core.out_line (' ');
xxtc_core.out_line ('-------------------------------------------');
xxtc_core.out_line ( 'Processing started at '
|| TO_CHAR (SYSDATE, 'DD-MON-YY HH24:MI')
);
xxtc_core.out_line ('-------------------------------------------');
xxtc_core.out_line ('Parameters:');
xxtc_core.out_line (' ');
-- Print Report Header
xxtc_core.out_line (' ');
xxtc_core.out_line
( ' MRI Invoice Import File Listing Report '
|| 'Report '
|| TO_CHAR (SYSDATE, 'DD-MON-YY HH:MI:SS')
);
xxtc_core.out_line (' ');
xxtc_core.out_line (' MRI Invoice Files Available to be imported');
xxtc_core.out_line
('------------------------------------------------------------------------------------------------------------------'
);
-- WRITE(g_outfile,' ');
g_line_count := g_line_count + 16;
xxtc_core.upload_file_names ('MRI AP Invoice Run',
p_input_directory,
p_input_filename
);
xxtc_core.log_line ('Files Upleaded');
BEGIN
-- execute immediate 'truncate table xxtc.xxtc_apinv_i_mri_all'; -- Commented Version 115.2
-- EXECUTE IMMEDIATE 'DELETE FROM xxtc.xxtc_apinv_i_mri_all
-- WHERE STATUS IS NULL'; -- Added Version 115.2
EXECUTE IMMEDIATE 'DELETE FROM xxtc.xxtc_apinv_i_mri_all
WHERE STATUS = ''PROCESS'''; -- Added Version 115.4
EXCEPTION
WHEN OTHERS THEN
xxtc_core.log_line('Error while truncating');
END;
COMMIT;
-----------------------------------------------------------------------------
-- Loop for all input files
-----------------------------------------------------------------------------
FOR r_file IN c_file (p_input_directory)
LOOP
xxtc_core.out_line (' Input file: ' || r_file.file_name);
---------------------------------------------------------------------------
-- Submit request XXTC - MRI AP Invoice Loader
---------------------------------------------------------------------------
ln_request_id := fnd_request.submit_request ('XXTC',
'XXTCMRIAPINVL',
'',
'',
FALSE,
p_input_directory,
r_file.file_name
);
END LOOP;
-- report_line( '. XXTC - MRI AutoInvoice Loader Request ID: ' || TO_CHAR (ln_request_id) );
IF ln_request_id > 0
THEN
-- commit must be before wait !!!
COMMIT;
-- Waits for the request completion
req_status :=
fnd_concurrent.wait_for_request
(ln_request_id -- request_id
,
30 -- interval (number of seconds to sleep)
,
3600 -- max_wait (in seconds)
-- Output arguments
,
v_phase -- phase
,
v_status -- status
,
v_dev_phase -- dev_phase
,
v_dev_status -- dev_status
,
v_message -- message
);
END IF;
lv_request_id := fnd_request.submit_request ('XXTC',
'XXTCMRIAPINVU',
'',
'',
FALSE,
'',
''
--,p_debug
);
IF lv_request_id > 0
THEN
-- commit must be before wait !!!
COMMIT;
-- Waits for the request completion
req_status :=
fnd_concurrent.wait_for_request
(lv_request_id -- request_id
,
30 -- interval (number of seconds to sleep)
,
3600 -- max_wait (in seconds)
-- Output arguments
,
v_phase -- phase
,
v_status -- status
,
v_dev_phase -- dev_phase
,
v_dev_status -- dev_status
,
v_message -- message
);
END IF;
IF v_dev_phase = 'COMPLETE' AND v_dev_status IN ('NORMAL', 'WARNING')
THEN
l_use_batch := fnd_profile.VALUE ('AP_USE_INV_BATCH_CONTROLS');
xxtc_core.log_line ('AP_USE_INV_BATCH_CONTROLS:' || l_use_batch);
IF l_use_batch = 'Y'
THEN
SELECT xxtc_mri_inv_batch_s.NEXTVAL
INTO l_seq
FROM DUAL;
l_batchname := 'MRI' || TO_CHAR (SYSDATE, 'YYYYMMDD') || l_seq;
END IF;
--Submit Payables Open Import Program
BEGIN
xxtc_core.log_line
('Submitting request to run the Standard AP Import Process (APXIIMPT)'
);
localreqid := fnd_request.submit_request ('SQLAP',
'APXIIMPT',
'',
'',
FALSE,
'MRI',
'',
l_batchname,
'',
'',
'',
'Y',
'N',
'Y',
'Y',
'1000',
'SYSADMIN',
--TO_CHAR(UserID),
CHR (0),
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
''
);
xxtc_core.log_line ('Payable Open Import Reqid: ' || localreqid);
EXCEPTION
WHEN OTHERS
THEN
xxtc_core.log_line
( 'Error while submitting payables import'
|| SQLERRM
);
xxtc_core.log_line ('l_batchname: ' || l_batchname);
END;
IF localreqid > 0
THEN
-- commit must be before wait !!!
COMMIT;
-- Waits for the request completion
req_status :=
fnd_concurrent.wait_for_request
(localreqid -- request_id
,
30 -- interval (number of seconds to sleep)
,
3600 -- max_wait (in seconds)
-- Output arguments
,
v_phase -- phase
,
v_status -- status
,
v_dev_phase -- dev_phase
,
v_dev_status -- dev_status
,
v_message -- message
);
COMMIT;
xxtc_core.log_line ( 'v_phase:'
|| v_phase
|| 'v_status'
|| v_status
|| 'v_dev_phase'
|| v_dev_phase
|| 'v_dev_status'
|| v_dev_status
);
IF l_use_batch = 'Y'
THEN
BEGIN
SELECT batch_id
INTO l_batch_id
FROM ap_batches_v
WHERE batch_name = l_batchname;
xxtc_core.log_line ('Batch iD: ' || l_batch_id);
EXCEPTION
WHEN OTHERS
THEN
xxtc_core.log_line ('Error fetching batch id: '
|| SQLERRM
);
END;
END IF;
IF l_batch_id IS NOT NULL THEN --IR 181573
l_req_invval_id := fnd_request.submit_request ('SQLAP',
'APPRVL',
'',
'',
FALSE,
'ALL',
l_batch_id,
'',
'',
'',
'',
'',
'',
sobid,
'N',
ln_org_id,
1000
);
xxtc_core.log_line ('invoice validation req id' || l_req_invval_id);
IF l_req_invval_id > 0
THEN
-- commit must be before wait !!!
COMMIT;
-- Waits for the request completion
req_status :=
fnd_concurrent.wait_for_request
(l_req_invval_id -- request_id
,
30 -- interval (number of seconds to sleep)
,
3600 -- max_wait (in seconds)
-- Output arguments
,
v_phase -- phase
,
v_status -- status
,
v_dev_phase -- dev_phase
,
v_dev_status -- dev_status
,
v_message -- message
);
END IF;
END IF; -- IR 181573
END IF;
END IF;
COMMIT;
xxtc_core.log_line ('ReportFooter:');
xxtc_core.out_line (' ');
xxtc_core.out_line (' ');
xxtc_core.out_line ('-------------------------------------------');
xxtc_core.out_line
('MRI Invoice Import Processing completed successfully');
xxtc_core.out_line (' ');
xxtc_core.out_line (' *** End of Report ***');
EXCEPTION
WHEN OTHERS
THEN
xxtc_core.out_line (SQLERRM);
END;
END xxtc_ap_inv;
/

Oracle Applications & Oracle Cloud Fusion HCM

Oracle Application and Cloud HCM

Monday, 9 January 2017

AP Invoice Interface

No comments :

Contact us