AP INVOICE INTERFACE

This interface helps us to import vendor invoices into Oracle applications from external systems into Oracle Applications.


CREATE VIEW xxcns_ch_ap_open_v --Create a sample view with Data
AS 
  SELECT '12345'       vendor_code, 
         '30911111'    invoice_num, 
         'NANTONG'     site_code, 
         'STANDARD'    invoice_type_lookup_code, 
         '16-Apr-2019' invoice_date, 
         '30 days'     payment_term, 
         18000         amount 
  FROM   dual 
  UNION ALL 
    SELECT '67890, 
         '81822222', 
         'SUZHOU', 
         'STANDARD', 
         '27-Jul-2019', 
         '30 days', 
         38270.98 
  FROM   dual 
  UNION ALL

  SELECT '16171', 
         '13233333', 
         'ZHEJIANG', 
         'STANDARD', 
         '06-Jul-2019', 
         '30 days', 
         47695 
  FROM   dual;

BEGIN 
  FOR c1 IN (SELECT *
             FROM (SELECT b.vendor_id, 
                          a.*, 
                          (SELECT x.vendor_site_id 
                           FROM   ap_supplier_sites_all x 
                           WHERE  x.vendor_id = b.vendor_id 
                           AND  Substr(Upper(x.vendor_site_code), 1, 5) = Substr(Upper(Rtrim(Ltrim(a.site_code))), 1, 5) 
                           AND x.org_id = <<ORG_ID>>) vendor_site_id, 
                           <<ORG_ID>>                         ORG_ID, 
                           '31-JUL-2019'               GL_DATE, 
                           'CNY'                       CURRENCY, 
                           CASE WHEN A.invoice_type_lookup_code = 'STANDARD' THEN 254164 
                                ELSE NULL 
                           END CODE_COMBINATION_ID, 
                           'Opening - '||invoice_num description 
                       FROM   xxcns_ch_ap_open_v a, 
                              ap_suppliers b 
                       WHERE  a.vendor_code = b.segment1(+)) 
               WHERE  vendor_site_id IS NOT NULL) LOOP 
        INSERT INTO ap_invoices_interface 
                    (invoice_id, 
                     invoice_num, 
                     invoice_type_lookup_code, 
                     invoice_date, 
                     vendor_id, 
                     vendor_site_id, 
                     invoice_amount, 
                     invoice_currency_code, 
                     description, 
                     source, 
                     gl_date, 
                     org_id, 
                     group_id ---To restrict the import 
        ) 
        VALUES      ( ap_invoices_interface_s.NEXTVAL, 
                     c1.invoice_num, 
                     c1.invoice_type_lookup_code, 
                     c1.invoice_date, 
                     c1.vendor_id, 
                     c1.vendor_site_id, 
                     c1.amount, 
                     c1.currency, 
                     c1.description, 
                     'CHINA OPENING', 
                     c1.gl_date, 
                     c1.org_id, 
                     'MIGRATE' ); 

        INSERT INTO ap_invoice_lines_interface 
                    (invoice_id, 
                     invoice_line_id, 
                     line_number, 
                     line_type_lookup_code, 
                     amount, 
                     dist_code_combination_id, 
                     org_id, 
                     description) 
        VALUES     ( ap_invoices_interface_s.CURRVAL, 
                    ap_invoice_lines_interface_s.NEXTVAL, 
                    1, 
                    'ITEM', 
                    c1.amount, 
                    c1.code_combination_id, 
                    c1.org_id, 
                    c1.description ); 
    END LOOP; 

    COMMIT; 
END; 



Payables > Invoices > Entry > Open Interface Invoices to check the details of Invoice and Invoice Lines from the front end. If required you can do any modifications here. Alternatively you can use these forms to put invoice data in AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE tables.

Now, run the concurrent program “Payables Open Interface Import”  to submit a request for Invoice Import.