Oracle Apps Knowledge Sharing

Script to Create AP Invoice with Tax lines in Oracle Apps R12 (AP_INVOICES_INTERFACE, AP_INVOICE_LINES_INTERFACE)

In this post, we have given a sample script to insert data into payables open invoice import tables and submit payables open invoice import program.

Interface Tables:

AP_INVOICES_INTERFACE

AP_INVOICE_LINES_INTERFACE

Script:

SET SERVEROUTPUT ON;
DECLARE
  ln_strd_request_id  NUMBER;
  lv_group_id         NUMBER DEFAULT TO_NUMBER(TO_CHAR(SYSDATE,'HHMISS'));
  ln_line_group_num   NUMBER;
  lv_source           VARCHAR2(200) DEFAULT 'INVOICE IMPORT';
  ln_org_id           NUMBER DEFAULT 111;
  
  lv_tax_rate_code           VARCHAR2(1000);
  lv_tax_regime_code         VARCHAR2(1000);
  lv_tax                     VARCHAR2(1000);
  lv_tax_status_code         VARCHAR2(1000);
  lv_tax_jurisdiction_code   VARCHAR2(1000);
  lv_tax_rate                VARCHAR2(1000);
  

  PROCEDURE submit_payables_import(pin_org_id     IN NUMBER,
                                   piv_source     IN VARCHAR2,
                                   piv_group_id   IN VARCHAR2,
                                   pon_request_id OUT NUMBER
                                  ) 
  IS
    lv_request_id  NUMBER;
    lv_result      BOOLEAN;
    lv_phase1      VARCHAR2(100);
    lv_status1     VARCHAR2(100);
    lv_dev_phase1  VARCHAR2(100);
    lv_dev_status1 VARCHAR2(100);
    lv_message1    VARCHAR2(100);
    position_      NUMBER;
  BEGIN
    position_ := 500;
    -- Submit Payables Invoice Import program:
    lv_request_id := fnd_request.submit_request
                            (application => 'SQLAP',
                             program     => 'APXIIMPT',
                             description => NULL,
                             start_time  => NULL,
                             sub_request => NULL,
                             argument1   => pin_org_id,    --Operating Unit
                             argument2   => piv_source,    --Source
                             argument3   => piv_group_id,  --Group
                             argument4   => piv_group_id,  --Invoice Batch Name
                             argument5   => NULL,          --Hold Name
                             argument6   => NULL,          --Hold Reason
                             argument7   => NULL,          --GL Date
                             argument8   => 'N',           --Purge
                             argument9   => 'N',           --Trace Switch
                             argument10  => 'N',           --Debug Switch
                             argument11  => 'N',           --Summarize Report
                             argument12  => 1000,          --Commit Batch Size
                             argument13  => fnd_profile.VALUE('USER_ID'), --User ID
                             argument14  => fnd_profile.VALUE('LOGIN_ID') --Login ID
                            );
    COMMIT;
    position_ := 510;
    IF lv_request_id = 0 THEN
      dbms_output.put_line(' Failed to submit Process APXIIMPT.');
    ELSE
      lv_result := fnd_concurrent.wait_for_request(lv_request_id,
                                                   1,
                                                   0,
                                                   lv_phase1,
                                                   lv_status1,
                                                   lv_dev_phase1,
                                                   lv_dev_status1,
                                                   lv_message1);
    END IF;
    position_ := 520;
    IF NOT lv_result THEN
      dbms_output.put_line('No Status for the request Id: ' ||lv_request_id);
    ELSE
      dbms_output.put_line('The Req-Id of APXIIMPT Process is ' || lv_request_id);
    END IF;

    pon_request_id := lv_request_id;

  EXCEPTION
    WHEN OTHERS THEN
      pon_request_id := NVL(lv_request_id, 0);
      dbms_output.put_line('Others exception. Error' ||SQLERRM);
  END submit_payables_import;

BEGIN 
  dbms_output.put_line('Group id:'||lv_group_id);

  SELECT tax_rate_code,
         tax_regime_code,
         tax,
         tax_status_code,
         tax_jurisdiction_code,                   
         percentage_rate            
    INTO lv_tax_rate_code,
         lv_tax_regime_code,
         lv_tax,
         lv_tax_status_code,
         lv_tax_jurisdiction_code,
         lv_tax_rate
    FROM xxif_ou_country_vat_v
   WHERE tax_rate_code = '25%'
     AND organization_id = ln_org_id;

  INSERT
  INTO ap_invoices_interface
    (
    invoice_id,
    invoice_num,
    invoice_currency_code,
    invoice_type_lookup_code,
    exchange_rate_type,
    exchange_date,
    invoice_date,
    invoice_received_date,
    vendor_id,
    vendor_site_id,
    invoice_amount,
    source,
    org_id,
    group_id,
    --batch_id,
    taxation_country,
    calc_tax_during_import_flag
    )
  VALUES
    (
    AP_INVOICES_INTERFACE_S.NEXTVAL
   ,'4200094338'
   ,'USD'
   ,'STANDARD'
   ,'Corporate'
   ,SYSDATE
   ,SYSDATE --<invoice date>
   ,SYSDATE
   ,8656    --<vendor_id validated against po_vendors.vendor_id>,
   ,3663     --<vendor_site_id validated against po_vendor_sites.vendor_site_id>,
   ,1450.84  --<invoice_amount>
   ,lv_source -- It can be retrived by following query
              --(select lookup_code
                 --from ap_lookup_codes
                --where lookup_type='SOURCE')
   ,ln_org_id
   ,lv_group_id
  -- ,lv_group_id
   ,'SE'
   ,'N'
   );
  
  INSERT
  INTO ap_invoice_lines_interface
    (
    invoice_id,
    invoice_line_id,
    line_number,
    line_type_lookup_code,
    amount,
    accounting_date,
    line_group_number,
    amount_includes_tax_flag,
    dist_code_combination_id
    )
    VALUES
    (
    AP_INVOICES_INTERFACE_S.CURRVAL
   ,AP_INVOICE_LINES_INTERFACE_S.NEXTVAL
   ,1        --<some unique_number>,
   ,'ITEM'   --<ITEM>/<FREIGHT>/<TAX>
   ,1160.67     -- sum of line_amount should always be equal to invoice_amount,
   ,SYSDATE
   ,AP_INVOICE_LINES_INTERFACE_S.CURRVAL
   ,'N'
   ,'144825' --'<account code>'
    );
  
  ln_line_group_num :=   AP_INVOICE_LINES_INTERFACE_S.CURRVAL;
  
  INSERT
    INTO ap_invoice_lines_interface
    (
    invoice_id,
    invoice_line_id,
    line_number,
    line_type_lookup_code,
    amount,
    accounting_date,
    line_group_number,
    dist_code_combination_id,
    --tax_rate_id,
    tax_rate_code,
    tax_regime_code,
    tax,
    tax_status_code,
    tax_jurisdiction_code,                   
    tax_rate
    )
  VALUES
    (
    AP_INVOICES_INTERFACE_S.CURRVAL
   ,AP_INVOICE_LINES_INTERFACE_S.NEXTVAL
   ,2        --<some unique_number>,
   ,'TAX'   --<ITEM>/<FREIGHT>/<TAX>
   ,290.17      -- sum of line_amount should always be equal to invoice_amount,
   ,SYSDATE
   ,ln_line_group_num
   ,NULL --'<account code>'
  -- ,25 --> Tax_RATE_ID
   ,lv_tax_rate_code,
    lv_tax_regime_code,
    lv_tax,
    lv_tax_status_code,
    lv_tax_jurisdiction_code,
    lv_tax_rate
    );
  COMMIT;  

  
  fnd_global.apps_initialize(user_id      => 4484,
                             resp_id      => 90833, -- 'AP Admin'
                             resp_appl_id => 200
                            );
                            
  mo_global.init('SQLAP'); 
  fnd_client_info.set_org_context(ln_org_id);
  mo_global.set_policy_context ('S', ln_org_id);

  submit_payables_import(pin_org_id     => ln_org_id,
                         piv_source     => lv_source,
                         piv_group_id   => lv_group_id,
                         pon_request_id => ln_strd_request_id
                        );
  DBMS_OUTPUT.PUT_LINE('ln_strd_request_id :'||ln_strd_request_id);                          
END;