How To Use SQL Loader to Populate the Payables Open Interface Tables

The following information provides a basic example of a SQL Loader program along
with the information necessary to accurately populate the Open Interface tables to
load various types of invoices. Note that you will need to decide which type of invoice
you are trying to load and follow the appropriate example. For instance, you will choose
either a PO matched invoice OR a Project Related Invoice…not both. The reason
being that the account information is provided either by the Project information OR
the PO information - not both.

Please pay close attention to the information associated with the required fields for
the particular type of invoice you are trying to load into the Open Interface table.
In addition, there will be indications to choose either one OR the other field – not
both, as this will cause a problem during import of the data. We will discuss the
following 3 types of invoices:

1. Simple Invoice
2. PO Matched Invoice
3. Project Related Invoice

---

A. Example of a SQL*Load program used to populate an invoice in AP
Invoices Interface tables:

DECLARE
    p_invoice_id       NUMBER;
    i                           NUMBER;

BEGIN

    -- Get invoice_id --

    select AP_INVOICES_INTERFACE_S.nextval
    into p_invoice_id
    from dual;

    -- Insert an invoice header --

    insert into ap_invoices_interface
    (invoice_id,
     invoice_num,
     invoice_type_lookup_code,
     vendor_id,
     vendor_site_id,
     invoice_amount,
     invoice_currency_code,
     source,
     group_id)
    VALUES
    (p_invoice_id,
     'testcase1',
     'STANDARD',
     2,
     2,
     10,
     'USD',
     'INVOICE GATEWAY',
     'testcase1');

    -- Insert invoice line --

    for i in 1..10 loop

      INSERT into ap_invoice_lines_interface
      (invoice_id,
       invoice_line_id,
       line_number,
       line_type_lookup_code,
       amount,
       dist_code_combination_id)
      VALUES
      (p_invoice_id,
       AP_INVOICE_LINES_INTERFACE_S.nextval,
       i,
       'ITEM',
      1,
       12831);

    end loop;

 commit;

END;
 /

---

1. Simple Invoice:
 Note: You should not populate any PO or PA related columns for a simple
 invoice. Here are the tables and reference columns identified for use when
 populating data for a simple invoice:

AP_INVOICE_INTERFACE TABLE
============================

    Required Columns
    ==============
    INVOICE_ID
             (Populated from AP_INVOICES_INTERFACE_S.NEXTVAL)
    INVOICE_NUM
              (Must be unique to the supplier)
    VENDOR_ID or VENDOR_NUM or VENDOR_NAME
               (An active vendor. Validated against PO_VENDORS, you should only
                populate one of these columns for vendor info for the data consistence)
    VENDOR_SITE_ID or VENDOR_SITE_CODE
               (An active pay site. Validated against PO_VENDOR_SITES, you should
                only populate one of these columns for vendor site info for data consistency)
    INVOICE_AMOUNT
             (Positive amount for 'STANDARD' type, Negative amount for 'CREDIT' type)
    ORG_ID
              (Required in Multi-Org Environment. Validated against
               AP_SYSTEM_PARAMETERS.ORG_ID)
    SOURCE
              (Must be in SELECT LOOKUP_CODE FROM
               AP_LOOKUP_CODES WHERE LOOKUP_TYPE = 'SOURCE')
 

    Optional Columns – If you do not  populate them, then the value will be defaulted as indicated:
    =====================================================================
    INVOICE_DATE
            (Defaulted to SYSDATE)
    INVOICE_TYPE_LOOKUP_CODE
             (Defaulted to 'STANDARD')
    INVOICE_CURRENCY_CODE
             (Defaulted from PO_VENDOR_SITES.INVOICE_CURRENCY_CODE)
    TERMS_ID or TERMS_NAME
             (Defaulted from PO_VENDOR_SITES.TERMS_ID, if you populate it,
              should populate one of  them)
    DOC_CATEGORY_CODE
            (Only populated if using automatic voucher number)
    PAYMENT_METHOD_LOOKUP_CODE
            (Defaulted from PO_VENDOR_SITES.PAYMENT_METHOD_LOOKUP_CODE)
    PAY_GROUP_LOOKUP_CODE
            (Defaulted from PO_VENDOR_SITES.PAY_GROUP_LOOKUP_CODE)
    ACCTS_PAY_CODE_COMBINATION_ID
            (Defaulted from PO_VENDOR_SITES.ACCTS_PAY_CODE_COMBINAITON_ID)
    GROUP_ID
            (Group identifier. Suggested to use)
    STATUS
            (DO NOT POPULATE!!!)
    EXCHANGE_RATE_TYPE
            (Defaulted from AP_SYSTEM_PARAMETERS.DEFAULT_EXCHANGE_RATE_TYPE
             if the invoice is using foreign currency)
 

AP_INVOICE_LINES_INTERFACE TABLE
 ==================================

    Required Columns for LINE_TYPE_LOOKUP_CODE = 'ITEM'
    ================================================
    INVOICE_ID
           (Populated from AP_INVOICES_INTERFACE.INVOICE_ID)
    INVOICE_LINE_ID
           (Populated from AP_INVOICE_LINES_INTERFACE_S.NEXTVAL)
    LINE_NUMBER
           (A unique number  to the invoice)
    LINE_TYPE_LOOKUP_CODE
                ('ITEM')
    AMOUNT

    ACCOUNTING_DATE
             (Optional. Defaulted from INVOICE_DATE or SYSDATE)
   DIST_CODE_CONCATENATED or DIST_CODE_COMBINATION_ID
              (You should only populate one of them)
               DIST_CODE_CONCATENATED needs to meet security rules, no parent segment.
               DIST_CODE_COMBINATION_ID must be in
               SELECT CODE_COMBINATION_ID FROM GL_CODE_COMBINATIONS
               WHERE ACCOUNT_TYPE = 'E' AND ENABLED_FLAG = 'Y' AND
               SUMMARY_FLAG = 'N' AND SYSDATE BETWEEN NVL
               (START_DATE_ACTIVE, SYSDATE-1) AND NVL(END_DATE_ACTIVE,
               SYSDATE+1))

    Required /Optional Columns for LINE_TYPE_LOOKUP_CODE = 'TAX'
    =========================================================
    INVOICE_ID
           (Populated from AP_INVOICES_INTERFACE.INVOICE_ID)
    INVOICE_LINE_ID
           (Populated from AP_INVOICE_LINES_INTERFACE_S.NEXTVAL)
    LINE_NUMBER
           (A unique number to the invoice)
    LINE_TYPE_LOOKUP_CODE
            ('TAX')
    TAX_CODE or TAX_CODE_ID
            (Validated against AP_TAX_CODES, you should only populate one of them)
    AMOUNT

    ACCOUNTING_DATE
             (Optional. Defaulted from INVOICE_DATE or SYSDATE)
   DIST_CODE_CONCATENATED or DIST_CODE_COMBINATION_ID
             (Optional.  Defaulted from AP_TAX_CODES.TAX_CODE_COMBINATION_ID)
              You should only populate one of them if you need to populate it yourself.
              DIST_CODE_CONCATENATED needs to meet security rules, no parent segment.
              DIST_CODE_COMBINATION_ID must be in
              SELECT CODE_COMBINATION_ID FROM GL_CODE_COMBINATIONS
              WHERE ACCOUNT_TYPE = 'E' AND ENABLED_FLAG = 'Y' AND SUMMARY_FLAG = 'N'
              AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE-1) AND NVL(END_DATE_ACTIVE,
              SYSDATE+1))
 

  Required/Optional Columns for LINE_TYPE_LOOKUP_CODE = 'FREIGHT'
    ============================================================
    INVOICE_ID
         (Populated from AP_INVOICES_INTERFACE.INVOICE_ID)
    INVOICE_LINE_ID
         (Populated from AP_INVOICE_LINES_INTERFACE_S.NEXTVAL)
    LINE_NUMBER
         (A unique number to the invoice)
    LINE_TYPE_LOOKUP_CODE
         ('FREIGHT')
    AMOUNT

    ACCOUNTING_DATE
          (Optional. Defaulted from INVOICE_DATE or SYSDATE)
    DIST_CODE_CONCATENATED or DIST_CODE_COMBINATION_ID
          (Optional.  Defaulted from AP_SYSTEM_PARAMETERS.FREIGHT_CODE_COMBINATION_ID)
           You should only populate one of them if you need to populate it yourself.
           DIST_CODE_CONCATENATED needs to meet security rules, no parent segment.
           DIST_CODE_COMBINATION_ID must be in
          SELECT CODE_COMBINATION_ID FROM GL_CODE_COMBINATIONS
          WHERE ACCOUNT_TYPE = 'E' AND ENABLED_FLAG = 'Y' AND
          SUMMARY_FLAG = 'N' AND SYSDATE BETWEEN
         NVL(START_DATE_ACTIVE, SYSDATE-1) AND NVL(END_DATE_ACTIVE, SYSDATE+1))

---

2. PO Matched Invoice:
You should not populate DIST_CODE_COMBINATION_ID, DIST_CODE_CONCATENATED
or any PA related column for PO matched invoices.

There are two ways to match an invoice to a Purchasing Order:
1. Matching to a PO shipment (MATCH_OPTION = ‘P’)
2. Matching to a PO receipt (MATCH_OPTION = ‘R’).

Here are the tables and reference columns identified for use when populating data for a PO matched invoice:

AP_INVOICE_INTERFACE TABLE
=============================

    Required Columns
    ==============
   INVOICE_ID
         (Populated from AP_INVOICES_INTERFACE_S.NEXTVAL)
    INVOICE_NUM
         (Must be unique to the supplier)
    PO_NUMBER
           (An approved, not cancelled, not closed or final closed PO. Validated against PO_HEADERS)
    INVOICE_AMOUNT

    SOURCE
          (Must be in SELECT LOOKUP_CODE FROM AP_LOOKUP_CODES WHERE LOOKUP_TYPE = 'SOURCE')
    ORG_ID
         (Required in Multi-Org Environment. Validated against AP_SYSTEM_PARAMETERS.ORG_ID)

   Optional Columns
   ==============
    INVOICE_DATE
         (Defaulted to SYSDATE)
    INVOICE_TYPE_LOOKUP_CODE
          (Defaulted to 'STANDARD')
    INVOICE_CURRENCY_CODE
          (Defaulted from PO_VENDOR_SITES.INVOICE_CURRENCY_CODE)
    EXCHANGE_RATE_TYPE
          (Defaulted from AP_SYSTEM_PARAMETERS.DEFAULT_EXCHANGE_RATE_TYPE)
    TERMS_ID or TERMS_NAME
          (Defaulted from PO_VENDOR_SITES.TERMS_ID, should only populate one of them)
    DOC_CATEGORY_CODE
          (Only populated if using automatic voucher number)
    PAYMENT_METHOD_LOOKUP_CODE
         (Defaulted from PO_VENDOR_SITES.PAYMENT_METHOD_LOOKUP_CODE)
    PAY_GROUP_LOOKUP_CODE
          (Defaulted from PO_VENDOR_SITES.PAY_GROUP_LOOKUP_CODE)
    ACCTS_PAY_CODE_COMBINATION_ID
          (Defaulted from PO_VENDOR_SITES.ACCTS_PAY_CODE_COMBINAITON_ID)
    GROUP_ID
         (Group identifier. Suggested to use)
    STATUS
          (DO NOT POPULATE !!!!)

  AP_INVOICE_LINES_INTERFACE TABLE
  ===================================

    Required Columns for PO Matched Lines
    =====================================
    INVOICE_ID
          ( Populated from AP_INVOICES_INTERFACE.INVOICE_ID)
    INVOICE_LINE_ID
         (Populated from AP_INVOICE_LINES_INTERFACE_S.NEXTVAL)
    LINE_NUMBER
         (A unique number to the invoice)
    LINE_TYPE_LOOKUP_CODE
         ('ITEM')
    AMOUNT
         (Should be QUANTITY_INVOICED * UNIT_PRICE)
 

   If MATCH_OPTION is 'P', then populate:
   ===============================
   RELEASE_NUM or PO_RELEASE_ID
        (For Blanket Release only, validated against PO_RELEASES)
   PO_NUMBER or PO_HEADER_ID
         (Validated against PO_HEADERS, should only populate one of them.)
   PO_LINE_NUMBER or PO_LINE_ID
         (Validated against PO_LINES, should only populate one of them.)
   PO_SHIPMENT_NUM or PO_LINE_LOCATION_ID
         (Validated against PO_LINE_LOCATIONS, should only populate one of them.)

    If MATCH_OPTION is 'R', then populate:
    ================================
    RECEIPT_NUMBER
         (Validated against RCV_SHIPMENT_HEADERS.RECEIPT_NUM)
    RCV_TRANSACTION_ID or PO_LINE_LOCATION_ID
         (Validated against RCV_TRANSACTIONS, should only populate one of them)

    Optional Columns for PO Matched Lines
    =====================================
    QUANTITY_INVOICED
         (Populated if different from PO shipment)
    UNIT_PRICE
         (Populated if different from PO shipment)
    MATCH_OPTION
         ('P' or 'R' or Defaulted from PO_VENDOR_SITES.MATCH_OPTION)
    ACCOUNTING_DATE
         (Defaulted from INVOICE_DATE or SYSDATE)
    FINAL_MATCH_FLAG
         (Populated 'Y' if it is final matching)
    INVENTORY_ITEM_ID
         (Validated against PO_LINES.INVENTORY_ITEM_ID)
    INVENTORY_DESCRIPTION
         (Validated against PO_LINES.INVENTORY_ITEM_DESCRIPTION)
    SHIP_TO_LOCATION_CODE
         (Populated if different from PO shipment)
    PRICE_CORRECTION_FLAG
         (Populated 'Y' if it is price correction)

---

3. Project Related Invoices
You should not populate DIST_CODE_COMBINATION_ID,
DIST_CODE_CONCATENATED or any PO related column for project related invoices.
 

 You need to populate
 PROJECT_ID
 TASK_ID
 EXPENDITURE_TYPE
 EXPENDITURE_ITEM_DATE
 EXPENDITURE_ORGANIZATION_ID
 PA_QUANTITY

Payables Open Invoice Import will pass this project information data into the Project Account Generator
to generate an account foy you. Here are the reference columns which are required for
populating a project related invoice:

AP_INVOICE_INTERFACE TABLE
  ============================
    Required Columns
    ==============
    INVOICE_ID
         (Populated from AP_INVOICES_INTERFACE_S.NEXTVAL)
    INVOICE_NUM
         (Must be unique to the supplier)
    VENDOR_ID or VENDOR_NUM or VENDOR_NAME
         (An active vendor. Validated against PO_VENDORS, should only populate one of them)
    VENDOR_SITE_ID or VENDOR_SITE_CODE
         (An active pay site. Validated against PO_VENDOR_SITES, should only populate one of them)
    INVOICE_AMOUNT
         (Positive amount)
    ORG_ID      (Required in Multi-Org Environment. Validated against AP_SYSTEM_PARAMETERS.ORG_ID)
    SOURCE
          (Must be in SELECT LOOKUP_CODE FROM AP_LOOKUP_CODES WHERE LOOKUP_TYPE = 'SOURCE')

Optional Columns
    ================
    INVOICE_DATE
         (Defaulted to SYSDATE)
    INVOICE_TYPE_LOOKUP_CODE
         (Defaulted to 'STANDARD')
    INVOICE_CURRENCY_CODE
         (Defaulted from PO_VENDOR_SITES.INVOICE_CURRENCY_CODE)
    EXCHANGE_RATE_TYPE
         (Defaulted from AP_SYSTEM_PARAMETERS.DEFAULT_EXCHANGE_RATE_TYPE)
    TERMS_ID or TERMS_NAME
         (Defaulted from PO_VENDOR_SITES.TERMS_ID)
    DOC_CATEGORY_CODE
         (Only populated if using automatic voucher number)
    PAYMENT_METHOD_LOOKUP_CODE
         (Defaulted from PO_VENDOR_SITES.PAYMENT_METHOD_LOOKUP_CODE)
    PAY_GROUP_LOOKUP_CODE
         (Defaulted from PO_VENDOR_SITES.PAY_GROUP_LOOKUP_CODE)
    ACCTS_PAY_CODE_COMBINATION_ID
         (Defaulted from PO_VENDOR_SITES.ACCTS_PAY_CODE_COMBINAITON_ID)
    GROUP_ID
         (Group identifier. Suggest to use it)
    STATUS
         (DO NOT POPULATE !!!)
 

  AP_INVOICE_LINES_INTERFACE TABLE
  ===================================

    Required Columns for project related lines
    ===============================
    INVOICE_ID
         (Populated from AP_INVOICES_INTERFACE.INVOICE_ID)
    INVOICE_LINE_ID
         (Populated from AP_INVOICE_LINES_INTERFACE_S.NEXTVAL)
    LINE_NUMBER
         (A unique number to the invoice)
    LINE_TYPE_LOOKUP_CODE
         ('ITEM')
    AMOUNT
    PROJECT_ID
         (Validated against PA_PROJECTS.PROJECT_ID)
    TASK_ID
         (Validated against PA_TASKS.TASK_ID)
    EXPENDITURE_TYPE
         (Validated against PA_EXPENDITURE_TYPES.EXPENDITURE_TYPE)
    EXPENDITURE_ITEM_DATE
         (Needs to be between the task start date and end date)
    EXPENDITURE_ORGANIZATION_ID
         (Validated against PA_EXP_ORGS_IT.ORGANIZATION_ID)
    PA_QUANTITY

    Optional Columns for project related lines
    ==========================================
    ACCOUNTING_DATE
          (Defaulted from Invoice Date or SYSDATE)
    PA_ADDITION_FLAG
         ('Y' if the distribution has been transferred into Oracle Projects)