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;

/