Loading

Tuesday, May 18, 2010

insert script for interface tables related to Payables open interface invoice import

Below insert script will insert the minimal information required for creating a invoice through Payables open interface import program.
Change the values used in the script as per the comments given. We have tested this script in R12.1.1 instance.

<< Inserting into AP_INVOICES_INTERFACE>>

INSERT
INTO ap_invoices_interface
  (
    invoice_id,
    invoice_type_lookup_code,
    invoice_date,
    vendor_id,
    vendor_site_id,
    invoice_amount,
    source,
    org_id
  )
  VALUES
  (
    AP_INVOICES_INTERFACE_S.NEXTVAL
   ,'STANDARD'
   ,SYSDATE --<invoice date>
   ,3003    --<vendor_id validated against po_vendors.vendor_id>,
   ,106     --<vendor_site_id validated against po_vendor_sites.vendor_site_id>,
   ,100     --<invoice_amount>
   ,'TEST' -- It can be retrived by following query
                  --(select lookup_code
                          --from ap_lookup_codes
                          --where lookup_type='SOURCE')
   ,823
  );
 

<< Inserting into AP_INVOICE_LINES_INTERFACE>>

INSERT
INTO ap_invoice_lines_interface
  (
    invoice_id,
    invoice_line_id,
    line_number,
    line_type_lookup_code,
    amount,
    accounting_date,
    dist_code_combination_id
  )
  VALUES
  (
    AP_INVOICES_INTERFACE_S.CURRVAL
   ,AP_INVOICE_LINES_INTERFACE_S.NEXTVAL
   ,1        --<some unique_number>,
   ,'ITEM'   --<ITEM>/<FREIGHT>/<TAX>
   ,100      --<line_amount> sum of line_amount should always be equal to invoice_amount,
   ,SYSDATE
   ,'612860' --'<account code>'
  );
COMMIT;
After inserting, run the “PAYABLES OPEN INTERFACE IMPORT” program to load the invoice.

1 Responses to “insert script for interface tables related to Payables open interface invoice import”

bharat ahuja said...
February 1, 2016 at 12:30 AM

Tax columns are not included


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.