Intercompany AP inovice creation

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;
/
  • June 8, 2016 | 21 views