Wednesday 8 June 2016

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;
/

No comments:

Post a Comment