Intercompany AP Invoice Creation
Overview
When procurement happens between
inter-organizations, AP invoice needs to be created from receiver organization.
This AP invoice creation should be automated and fully matched with the receipt
created through ASN (Advanced Shipment Note). The below script creates AP
invoice by considering all automatic PO receipt created through ASN. For AP invoice
matching, quantity should pickup from PO receipt and price should picked up
from AR invoice from receivers org. Price and charges considered as a unit
price.
Business Rules
- · Alternative sites should have receiver inventory org code mapping
- · Receipt should have created through ASN process.
- · Manual receipts will not be considered.
- · AR invoice should be created from sender to get the unit price.
- · By considering all the above scenarios, AP invoice will be created by considering the quantity from PO receipt and Unit price from AR invoice from sender org.
SCRIPT
CREATE OR REPLACE PACKAGE APPS.xxeur_ap_invoices_pkg
AUTHID CURRENT_USER
IS
gn_request_id
NUMBER :=
FND_GLOBAL.CONC_REQUEST_ID;
gn_prog_appl_id
NUMBER :=
FND_GLOBAL.PROG_APPL_ID;
gn_resp_appl_id
NUMBER :=
FND_GLOBAL.RESP_APPL_ID;
gn_resp_id
NUMBER := FND_GLOBAL.RESP_ID;
gn_program_id
NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
gn_user_id
NUMBER := FND_GLOBAL.USER_ID;
gn_login_id
NUMBER :=
FND_GLOBAL.LOGIN_ID;
gn_org_id
NUMBER := FND_GLOBAL.ORG_ID;
gn_msg_count
NUMBER := 0;
gc_request_flag
VARCHAR2(1) :='N';
gc_stage VARCHAR2(1000);
gd_sysdate
DATE :=SYSDATE;
gl_date DATE;
PROCEDURE main(
errbuf OUT VARCHAR2
,retcode OUT NUMBER
,p_vendor_id IN
NUMBER
,p_vendor_site_id IN
NUMBER
,p_receipt_num IN
VARCHAR2
,p_gl_date IN
VARCHAR2
);
PROCEDURE load_interface(p_vendor_id IN NUMBER
,p_vendor_site_id IN NUMBER
,p_receipt_num IN VARCHAR2);
PROCEDURE run_apinvoice_import;
END xxeur_ap_invoices_pkg;
/
CREATE OR REPLACE PACKAGE BODY
APPS.xxeur_ap_invoices_pkg
IS
PROCEDURE main(
errbuf OUT VARCHAR2
,retcode OUT NUMBER
,p_vendor_id IN NUMBER
,p_vendor_site_id IN NUMBER
,p_receipt_num IN VARCHAR2
,p_gl_date IN VARCHAR2
)
IS
BEGIN
gc_stage :=
'Calling Main procedure to Load Interface';
gl_date :=
TO_DATE(p_gl_date,'YYYY/MM/DD HH24:MI:SS'); -- Modified by Anand on 18-Nov-2015
for date issue
load_interface(p_vendor_id,p_vendor_site_id ,p_receipt_num);
--
run_ApInvoice_import;
gc_stage :=
'Calling Main procedure After Load Interface';
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.log, gc_stage||'-'||SQLERRM ||',
'||SQLCODE);
fnd_file.put_line (fnd_file.log,'
--------- Procedure MAIN Exit ---------');
fnd_file.put_line (fnd_file.log,RPAD(' ',80,' '));
END;
PROCEDURE load_interface(p_vendor_id IN NUMBER
,p_vendor_site_id IN NUMBER
,p_receipt_num IN VARCHAR2)
AS
lc_error_flag VARCHAR2
(3);
lc_error_msg VARCHAR2
(4000);
ln_invoice_id NUMBER;
lc_invoice_num
VARCHAR2(50);
ln_count NUMBER;
ln_header_cnt NUMBER;
ln_lines_cnt NUMBER;
l_org_id NUMBER;
ln_rcpt_lines NUMBER;
ln_ar_lines NUMBER;
ln_invoice_line_id NUMBER;
CURSOR
lcu_ap_inv
IS
SELECT
rct.trx_number invoice_num,
phs.org_id org_id,
rct.customer_trx_id customer_trx_id,
rct.trx_date invoice_date,
aps.vendor_id vendor_id,
rsh.receipt_num receipt_number,
phs.po_header_id po_header_id,
phs.segment1 po_number,
aps.segment1 vendor_num,
apsa.vendor_site_id vendor_site_id,
apsa.vendor_site_code vendor_site_code,
rsh.shipment_header_id shipment_header_id,
apsr.amount_due_original invoice_amount,
(rsl.quantity_received * ctl.unit_selling_price) line_amount,
rsl.item_id item_id,
ctl.description item_description,
rt.currency_code invoice_currency_code,
ctl.line_number line_number,
aps.terms_id terms_id,
(SELECT
ieppm.payment_method_code
FROM
iby_external_payees_all iepa,
iby_ext_party_pmt_mthds
ieppm
WHERE
apsa.vendor_site_id = iepa.supplier_site_id
AND
iepa.ext_payee_id = ieppm.ext_pmt_party_id
AND
ieppm.primary_flag = 'Y') payment_method,
rsh.shipment_num shipment_num
,ctl.unit_selling_price
FROM
ap_suppliers aps,
ap_supplier_sites_all apsa,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
rcv_transactions rt,
po_headers_all
phs,
oe_order_headers_all ooh,
oe_order_lines_all ool,
ra_customer_trx_lines_all ctl,
ra_customer_trx_all rct,
ar_payment_schedules_all apsr
WHERE aps.vendor_type_lookup_code = 'INTERCOMPANY'
AND
aps.vendor_id = apsa.vendor_id
AND
aps.vendor_id = NVL (P_vendor_id, aps.vendor_id)
AND
apsa.vendor_site_id = NVL (P_vendor_site_id, apsa.vendor_site_id)
AND
apsa.vendor_site_id = rsh.vendor_site_id
AND
aps.vendor_id = rsh.vendor_id
AND
rsh.receipt_num = NVL (P_receipt_num, rsh.receipt_num)
AND
rsl.shipment_header_id = rsh.shipment_header_id
AND
rt.shipment_header_id = rsh.shipment_header_id
AND
rt.shipment_line_id = rsl.shipment_line_id
AND
rt.transaction_type = 'RECEIVE'
AND
phs.po_header_id = rsl.po_header_id
AND
ooh.cust_po_number = phs.segment1
AND
ool.header_id = ooh.header_id
AND ctl.sales_order=TO_CHAR(ooh.order_number)
AND ctl.interface_line_attribute6
= ool.line_id
AND
ctl.interface_line_attribute3 = SUBSTR (rsh.shipment_num, 1, (INSTR
(rsh.shipment_num, '-') - 1))
AND
ctl.interface_line_context = 'ORDER ENTRY'
AND
rct.customer_trx_id = ctl.customer_trx_id
AND
rct.org_id = ctl.org_id
AND
ctl.line_type = 'LINE'
AND apsr.customer_trx_id
= rct.customer_trx_id
AND
apsr.org_id = rct.org_id
AND (SELECT
primary_vendor_item
FROM
po_approved_supplier_list
WHERE
item_id = rsl.item_id
AND
vendor_id = aps.vendor_id
AND
vendor_site_id = apsa.vendor_site_id
AND
using_organization_id=rt.organization_id) =
(SELECT
msi.segment1
FROM
mtl_system_items_b msi
WHERE
msi.inventory_item_id = ctl.inventory_item_id
AND
msi.organization_id = ctl.interface_line_attribute10)
AND NOT
EXISTS (
SELECT
1
FROM
ap_invoice_lines_all apl,
ap_invoices_all apa
WHERE
apa.invoice_id = apl.invoice_id
AND
apl.rcv_transaction_id = rt.transaction_id
AND
apa.vendor_id = rt.vendor_id
AND
apa.vendor_site_id = rt.vendor_site_id
AND
rt.po_line_id = apl.po_line_id
AND
rt.po_header_id = apl.po_header_id
AND
rt.po_line_location_id = apl.po_line_location_id)
AND
rsl.shipment_line_status_code = 'FULLY RECEIVED'
UNION
SELECT
TRX.trx_number invoice_num,
phs.org_id org_id,
TRX.customer_trx_id customer_trx_id,
TRX.trx_date invoice_date,
aps.vendor_id vendor_id,
rsh.receipt_num receipt_number,
phs.po_header_id po_header_id,
phs.segment1 po_number,
aps.segment1 vendor_num,
apsa.vendor_site_id vendor_site_id,
apsa.vendor_site_code vendor_site_code,
rsh.shipment_header_id shipment_header_id,
apsr.amount_due_original
invoice_amount,
(rsl.quantity_received * TRX_LINE.unit_selling_price) line_amount,
rsl.item_id item_id,
TRX_LINE.description item_description,
rt.currency_code invoice_currency_code,
TRX_LINE.line_number line_number,
aps.terms_id terms_id,
(SELECT
ieppm.payment_method_code
FROM
iby_external_payees_all iepa,
iby_ext_party_pmt_mthds ieppm
WHERE
apsa.vendor_site_id = iepa.supplier_site_id
AND
iepa.ext_payee_id = ieppm.ext_pmt_party_id
AND
ieppm.primary_flag = 'Y') payment_method,
rsh.shipment_num shipment_num
,TRX_LINE.unit_selling_price
from rcv_shipment_headers
RSH
,rcv_shipment_lines RSL
,rcv_transactions RT
,po_headers_all PHS
,ap_supplier_sites_all APSA
,ap_suppliers APS
,po_approved_supplier_list ASL
,mtl_system_items_b MSI
,oe_order_headers_all OOH
,oe_order_lines_all OOL
,ra_customer_trx_lines_all TRX_LINE
,ra_customer_trx_all TRX
,ar_payment_schedules_all APSR
where
RSL.shipment_header_id =
RSH.shipment_header_id
AND
RSH.receipt_num = NVL
(P_receipt_num, RSH.receipt_num)
AND
RT.shipment_header_id =
RSH.shipment_header_id
AND
RT.shipment_line_id =
RSL.shipment_line_id
AND
RT.transaction_type = 'RECEIVE'
AND
PHS.po_header_id =
RSL.po_header_id
AND
APSA.vendor_site_id = NVL
(P_vendor_site_id, APSA.vendor_site_id)
AND
APSA.vendor_site_id =
RSH.vendor_site_id
AND
APS.vendor_type_lookup_code = 'INTERCOMPANY'
AND
APS.vendor_id = APSA.vendor_id
AND
APS.vendor_id = NVL
(P_vendor_id, APS.vendor_id)
AND
APS.vendor_id =
RSH.vendor_id
AND
RSL.item_id = ASL.item_id
AND
ASL.vendor_id =
APS.vendor_id
AND
ASL.vendor_site_id = APSA.vendor_site_id
--AND
ASL.using_organization_id =
RT.organization_id
AND
ASL.primary_vendor_item =
MSI.segment1
AND
MSI.organization_id =
OOL.ship_from_org_id
AND
MSI.inventory_item_id =
OOL.inventory_item_id
AND
OOH.cust_po_number =
PHS.segment1
AND
OOL.header_id =
OOH.header_id
AND TO_CHAR
(OOH.order_number) =
TRX_LINE.sales_order
AND
OOL.line_id =
TRX_LINE.interface_line_attribute6
AND
TRX_LINE.interface_line_context = 'ORDER
ENTRY'
AND
TRX_LINE.line_type = 'LINE'
AND
TRX_LINE.customer_trx_id =
TRX.customer_trx_id
AND
TRX.org_id =
TRX_LINE.org_id
AND
apsr.customer_trx_id = TRX.customer_trx_id
AND apsr.org_id
= TRX.org_id
AND NOT
EXISTS (
SELECT
1
FROM
ap_invoice_lines_all apl,
ap_invoices_all apa
WHERE
apa.invoice_id = apl.invoice_id
AND
apl.rcv_transaction_id = rt.transaction_id
AND apa.vendor_id = rt.vendor_id
AND
apa.vendor_site_id = rt.vendor_site_id
AND
rt.po_line_id = apl.po_line_id
AND
rt.po_header_id = apl.po_header_id
AND
rt.po_line_location_id = apl.po_line_location_id)
AND
rsl.shipment_line_status_code = 'FULLY RECEIVED'
ORDER BY line_number ASC;
BEGIN
gc_stage
:= 'Fetching ap invoice details records';
fnd_file.put_line (APPS.fnd_file.LOG, gc_stage);
gn_msg_count :=0;
ln_invoice_id := 0;
ln_invoice_line_id :=0;
FOR lcr_ap_inv in lcu_ap_inv
LOOP
ln_ar_lines :=0;
ln_rcpt_lines :=0;
BEGIN
SELECT
COUNT(1)
INTO ln_ar_lines
FROM ra_customer_trx_lines_all CTL
WHERE
CTL.customer_trx_id = lcr_ap_inv.customer_trx_id
AND CTL.line_type = 'LINE';
EXCEPTION
WHEN
OTHERS
THEN
fnd_file.put_line(fnd_file.LOG, 'Error while fetching Transaction
count:- '||ln_ar_lines
|| SQLERRM);
END;
BEGIN
SELECT
COUNT(1)
INTO ln_rcpt_lines
FROM rcv_shipment_lines RSL
WHERE
RSL.shipment_header_id = lcr_ap_inv.shipment_header_id
AND RSL.shipment_line_status_code ='FULLY
RECEIVED';
EXCEPTION
WHEN
OTHERS
THEN
fnd_file.put_line(fnd_file.LOG, 'Error while fetching Receipt Lines
count:- '||ln_rcpt_lines
||
SQLERRM);
END;
lc_error_flag:='S';
fnd_file.put_line(fnd_file.LOG,'INSERTING AP HEADER INTERFACE');
fnd_file.put_line(fnd_file.LOG,(RPAD('*', 80, '*' )));
IF
ln_ar_lines = ln_rcpt_lines THEN
IF
lcr_ap_inv.line_number = 1 THEN
SELECT
ap_invoices_interface_s.NEXTVAL
INTO
ln_invoice_id
FROM DUAL;
INSERT
INTO AP_INVOICES_INTERFACE
(invoice_id
,invoice_num
,invoice_date
,vendor_id
,vendor_num
,vendor_site_id
,vendor_site_code
,invoice_currency_code
,invoice_amount
,org_id
,source
,gl_date
,created_by
,creation_date
,last_updated_by
,last_update_date
,terms_id
,payment_method_code
)
VALUES (
ln_invoice_id
,lcr_ap_inv.invoice_num
,lcr_ap_inv.invoice_date
,lcr_ap_inv.vendor_id
,lcr_ap_inv.vendor_num
,lcr_ap_inv.vendor_site_id
,lcr_ap_inv.vendor_site_code
,lcr_ap_inv.invoice_currency_code
,lcr_ap_inv.invoice_amount
,lcr_ap_inv.org_id
,'XXINTERCOMPANY'
,NVL(gl_date,TRUNC(SYSDATE))
,gn_user_id
,SYSDATE
,gn_user_id---last_updated_by
,SYSDATE---last_update_date
,lcr_ap_inv.terms_id
,lcr_ap_inv.payment_method
);
fnd_file.put_line(fnd_file.LOG,(RPAD('-', 80, '-' )));
fnd_file.put_line(fnd_file.LOG,'AR LINES:-'||ln_rcpt_lines||'-'||' |
Receipt Lines:-'||ln_rcpt_lines);
fnd_file.put_line (fnd_file.log, 'Invoice Id :-'||ln_invoice_id||' |
Invoice Num :-'||lcr_ap_inv.invoice_num
||'-'||' |
Line Num:-'||lcr_ap_inv.line_number);
END IF;
SELECT
ap_invoice_lines_interface_s.NEXTVAL
INTO
ln_invoice_line_id
FROM DUAL;
INSERT INTO
AP_INVOICE_LINES_INTERFACE
(invoice_id
,invoice_line_id
,line_type_lookup_code
,amount
,receipt_number
,po_number
,po_header_id
--
,po_line_id
--,po_line_location_id
--
,po_distribution_id
,inventory_item_id
--,item_description
,org_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,unit_price
)
VALUES (ln_invoice_id
,ln_invoice_line_id
,'ITEM'
,lcr_ap_inv.line_amount
,lcr_ap_inv.receipt_number
,lcr_ap_inv.po_number
,lcr_ap_inv.po_header_id
--
,lcr_ap_inv.po_line_id
--
,lcr_ap_inv.line_location_id
--
,lcr_ap_inv.po_distribution_id
,lcr_ap_inv.item_id
--
,lcr_ap_inv.item_description
,lcr_ap_inv.org_id---lcr_ap_inv_ln.org_id
,gn_user_id--created_by
,SYSDATE
,gn_user_id---last_updated_by
,SYSDATE---last_update_date
,lcr_ap_inv.unit_selling_price
);
COMMIT;
END IF;
fnd_file.put_line (fnd_file.log, 'Invoice Id :-' ||ln_invoice_id||'-'||'
| Invoice Line Id:-'
||ln_invoice_line_id||'-'||' | Receipt Num
:-'||lcr_ap_inv.receipt_number
||'-'||' |
Line Num:-'||lcr_ap_inv.line_number);
gn_msg_count := gn_msg_count + 1;
END LOOP;
fnd_file.put_line (fnd_file.log, 'Completed AP invoice lines :-');
fnd_file.put_line (fnd_file.log, ' ');
fnd_file.put_line(fnd_file.log,'Number of records : '|| gn_msg_count );
-- Calling Invoice Import Standard Program
BEGIN
SELECT COUNT
(*)
INTO
ln_header_cnt
FROM
ap_invoices_interface
WHERE
source='XXINTERCOMPANY';
SELECT COUNT
(*)
INTO
ln_lines_cnt
FROM
ap_invoice_lines_interface;
fnd_file.put_line(fnd_file.log,'ln_header_cnt : '|| ln_header_cnt );
fnd_file.put_line(fnd_file.log,'ln_lines_cnt : '|| ln_lines_cnt );
IF
ln_header_cnt > 0
THEN
fnd_file.put_line(fnd_file.LOG, 'Submitting PAYABLES OPEN INTERFACE
IMPORT standard program.' );
run_apinvoice_import;
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,'Error while calling the Invoice Import Standard Program :
RUN_APINVOICE_IMPORT');
END;
END;
PROCEDURE run_apinvoice_import
IS
v_boolean BOOLEAN;
l_request_id NUMBER;
BEGIN
fnd_global.apps_initialize (user_id => gn_user_id,
resp_id => gn_resp_id,
resp_appl_id =>
gn_resp_appl_id );
-- Execute
AP Invoice import
BEGIN
l_request_id
:=
fnd_request.submit_request
(application => 'SQLAP',
program => 'APXIIMPT',
description => 'INVOICE
IMPORT',
start_time => SYSDATE,
sub_request => FALSE,
argument1 => gn_org_id,
-- org_id
argument2 =>
'XXINTERCOMPANY',----'MANUAL INVOICE ENTRY',
-- Invoice
source
argument3 => '',
-- group_id
(make sure the records have the same group id in the interface tables)
argument4 => 'AP invoice
creation based on PO receipts',
-- Batch Name
argument5 => '',
-- Hold name
incase you want to put the invoices on automatic hold after import
argument6 => '',
-- Hold
Raeson
argument7 => '',
-- GL Date
argument8 => 'N',
--Purge
argument9 => 'N',
--Trace
Switch
argument10 => 'Y',
--Debug
Switch
argument11 => 'Y',
--Summarize
Report for Audit report
argument12 => '' --User ID
--argument13
=>'', --Login ID
-- argument14
=>'' --Commit Batch
Size
);
--COMMIT;
fnd_file.put_line(fnd_file.output, 'Invoice import Program submitting
for Org_id:'
|| gn_org_id
|| ' Request
id :-'
||
l_request_id );
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.output,'Error while processing Invoice import
Program. Check request id : ' || l_request_id );
END;
--- END LOOP;
END
run_apinvoice_import;
END xxeur_ap_invoices_pkg;
/
No comments:
Post a Comment