Using SQL to Populate the Payables Open Interface Tables

Using SQL to Populate the Payables Open Interface Tables 

A. Example of a SQL 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_INVOICES_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_COMBINATION_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_INVOICES_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 for you. Here are the reference columns which are required for
populating a project related invoice:

AP_INVOICES_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) 

Check Locks and Kill the Sessions

These select statements will help identify the locking session so it can be killed.

NOTE: killing the session will cause immediate disconnect from the database, uncommitted data will be lost.

-- 1) show all locks

SQL> SELECT SS.SID, SS.SERIAL#,
OBJ.OBJECT_NAME,
MODULE,TO_CHAR(LOGON_TIME ,'DD-MON:HH24:MI')
FROM V$SESSION SS,
V$LOCKED_OBJECT LOBJ,
ALL_OBJECTS OBJ
WHERE LOBJ.OBJECT_ID= OBJ.OBJECT_ID
AND SS.SID= LOBJ.SESSION_ID
ORDER BY OBJ.OBJECT_NAME ;

-- 2) Identify locks on specific object

SQL> COLUMN OWNER FORMAT A20
COLUMN USERNAME FORMAT A20
COLUMN OBJECT_OWNER FORMAT A20
COLUMN OBJECT_NAME FORMAT A30
COLUMN LOCKED_MODE FORMAT A15
SELECT B.SESSION_ID AS SID,
NVL(B.ORACLE_USERNAME, '(oracle)') AS USERNAME,
A.OWNER AS OBJECT_OWNER,
A.OBJECT_NAME,
DECODE(B.LOCKED_MODE, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
B.LOCKED_MODE) LOCKED_MODE,
B.OS_USER_NAME
FROM DBA_OBJECTS A,
V$LOCKED_OBJECT B
WHERE A.OBJECT_ID = B.OBJECT_ID
AND A.OBJECT_NAME = 'MTL_TXN_REQUEST_LINES' -- change table name as appropriate
ORDER BY 1, 2, 3, 4;

-- 3) Get session id (SID) and session serial number

SQL> SELECT L.SID,S.SERIAL#,S.USERNAME,S.PROGRAM,
DECODE(L.TYPE,'RW','RW - Row Wait Enqueue',
'TM','TM - DML Enqueue',
'TX','TX - Trans Enqueue',
'UL','UL - User',L.TYPE||'System') RES,
T.NAME TAB,U.NAME OWNER,
L.ID1,L.ID2,
DECODE(L.LMODE,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Shr Row Excl',
6,'Exclusive',NULL) LMODE,
DECODE(L.REQUEST,1,'No Lock',
2,'Row Share',
3,'Row Excl',
4,'Share',
5,'Shr Row Excl',
6,'Exclusive',NULL) REQUEST
FROM V$LOCK L, V$SESSION S,
SYS.USER$ U,SYS.OBJ$ T
WHERE L.SID = S.SID
AND S.TYPE != 'BACKGROUND'
AND T.OBJ# = L.ID1
AND U.USER# = T.OWNER#
AND T.NAME LIKE 'MTL%';  -- change table name as appropriate
Use the above information to kill the lock associated with the stuck transaction.

In this specific example, it was the MO transaction (i.e. object = MTL_TXN_REQUEST_LINES)
Locate the SID & Serial then end/kill the lock from SQL*Plus 

SQL> alter system kill session 'SID, SERIAL';

Purchase Order from Requisition Number

This is the query to find out the purchase order number from requisition number

select  distinct pha.segment1 from po_headers_all pha,po_distributions_all pda,po_req_distributions_all rda,

po_requisition_headers_all rha,po_requisition_lines_all rla where

pha.po_header_id=pda.po_header_id and

pda.req_distribution_id=rda.distribution_id and

rda.requisition_line_id=rla.requisition_line_id and

rla.requisition_header_id=rha.requisition_header_id and

rha.segment1='&Requisition'

You can also find out the requisition number from purchase order number by using query given below

select distinct rha.segment1 from po_requisition_headers_all rha,po_requisition_lines_all rla,

po_req_distributions_all rda,po_distributions_all pda,po_headers_all pha where

rha.requisition_header_id=rla.requisition_header_id and

rla.requisition_line_id=rda.requisition_line_id and

rda.distribution_id=pda.req_distribution_id and

pda.po_header_id=pha.po_header_id and

pha.segment1='&PO_Number'