Oracle Purchasing

Pay On Code:

Payment on Receipt also called Evaluated Receipt Settlement (ERS) enables the user to automatically create standard, unapproved invoices for payment of goods based on receipt transactions. 

Invoices are created using a combination of receipt and purchase order information, that eliminates duplicate manual data entry and ensures accurate and timely data processing. 

Prerequisite:

Supplier Site should be an active Pay Site and Purchasing Site.

Supplier Site should be setup as Pay on Receipt.

Supplier Invoice creation process:

 While creating receipt for a Purchase Order (PO), if the Pay on Code for PO supplier site is setup as "Receipt", Receiving Transaction Processor (RTP) will populate the INVOICE_STATUS_CODE as "PENDING" in RCV_TRANSACTIONS table otherwise it inserts NULL.

Then the concurrent program "Pay on Receipt Auto Invoice" picks up the receiving transactions with INVOICE_STATUS_CODE as PENDING and does the following actions: 

•      Update the INVOICE_STATUS_CODE to INVOICED in RCV_TRANSACTIONS table for RECEIVE transaction type
•      Insert records into AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE table
•      Launch "Payables Open Interface Import(PII)" program to create unapproved Payables Invoice

If PII error out during interface data validation, then the records stay in the interface with REJECTED status and the corresponding error message will be available in AP_INTERFACE_REJECTIONS table

        Irrespective of whether AP invoice is created or not, the INVOICE_STATUS_CODE will be updated to INVOICED in RCV_TRANSACTIONS table for RECEIVE transaction type.

Tables involved in Sourcing

1. PO_APPROVED_SUPPLIER_LIST

PO_APPROVED_SUPPLIER_LIST stores the relationship between an item or commodity; a supplier, distributor, or manufacturer; ship-to organizations; and approval/certification status.

2. PO_ASL_ATTRIBUTES

PO_ASL_ATTRIBUTES stores all information for the supplier/item/organization relationship defined in PO_APPROVED_SUPPLIER_LIST. This information is maintained separately to allow each organization to define its own attributes, even if using a Global ASL entry for the supplier/item relationship and approval status.

3. PO_ASL_DOCUMENTS

PO_ASL_DOCUMENTS stores sourcing references to supply agreements, blanket agreements, and catalog quotations associated with particular suppliers and items in PO_APPROVED_SUPPLIER_LIST.

4. PO_ASL_STATUSES

PO_ASL_STATUSES stores the user-defined approval/certification statuses for the ASLs.

5. PO_ASL_STATUS_RULES

PO_ASL_STATUS_RULES stores the business rules associated with each defined ASL status.

6. MRP_SOURCING_RULES

MRP_SOURCING_RULES stores sourcing rule names and descriptions. The Define Sourcing Rule form populates this table.

7. MRP_ASSIGNMENT_SETS

MRP_ASSIGNMENT_SETS stores information about Sourcing Assignment Sets. The Define Assignment Sets form populates this table.

8. MRP_SR_ASSIGNMENTS

MRP_SR_ASSIGNMENTS stores the assignment of sourcing rules or bills of distribution to items, organizations, category, or

at the global level. The Assign Sourcing Rules Form populates this table

.9. PO_ASL_ATTRIBUTES_VAL_V (view)

PO_ASL_ATTRIBUTES_VAL_V is a view used by the Create Documents workflow that is created from table PO_ASL_ATTRIBUTES and table PO_APPROVED_SUPPLIER_LIST

 

Oracle Inventory Queries

Query to find valid categories in a Category Set:

SELECT mc.concatenated_segments,mc.description
  FROM apps.mtl_category_set_valid_cats mcsvc
      ,apps.mtl_category_sets mcs
      ,apps.mtl_categories_kfv mc
 WHERE mcsvc.category_set_id = mcs.category_set_id
   AND mc.category_id = mcsvc.category_id
   AND mcs.category_set_name = '<category_set_name>';

Query to find manufacturer part number for an item:

SELECT mm.manufacturer_name,
       mp.mfg_part_num,
       mp.description,
       msi.segment1 inv_item,
       msi.description item_desc,
       mp.approval_status
  FROM apps.mtl_system_items msi,
       apps.mtl_mfg_part_numbers mp,
       apps.mtl_manufacturers mm,
       apps.org_organization_definitions o
WHERE mm.manufacturer_id = mp.manufacturer_id
  AND mp.inventory_item_id = msi.inventory_item_id
  AND mp.organization_id   = msi.organization_id
  AND msi.segment1 = '<item_number>'
  AND msi.organization_id = o.organization_id
  AND o.organization_code = '<org_code>';

INVOICE_INTERFACE_STATUS_CODE

The possible values for the column OE_ORDER_LINES_ALL.INVOICE_INTERFACE_STATUS_CODE are

• YES
• NOT_ELIGIBLE
• NULL

When the sales order line is created with workflow such as "Shipping Only", the workflow does not include invoice interface activity. Hence the line will never come to invoice interface activity and is not invoice eligible. In this case, the INVOICE_INTERFACE_STATUS_CODE will never be set and it will be NULL value only.

INVOICE_INTERFACE_STATUS_CODE is set to NOT_ELIGIBLE if any of the following conditions are met

1. The invoiceable item flag or invoice enabled flag for the item is set to 'No'. Order Management will first check for these item attributes in Ship From organization. If Ship From organization is null, then it checks in Item Validation organization and if still null then it checks in Item Master organization.
2. Internal order lines are not invoiceable. Internal orders are the orders between two organizations in a company
3. Included items are not  invoiceable. All mandatory items under a BOM are considered as included items. We can also check whether an item is included item or not from the item type field on order lines.
4. Service items with non-serviceable parent are not invoiceable. This means that the serviceable product flag for the parent item is set to 'N'.  If not set, it defaults to 'Y'.
5. Configuration items are not invoiceable

If the order line does not satisfy any of the 5 conditions mentioned above, then the line is invoice eligible and INVOICE_INTERFACE_STATUS_CODE is set to YES once the line is interfaced to Receivables.

FND Queries

Query to find scheduled requests:

SELECT CPTL.USER_CONCURRENT_PROGRAM_NAME,
       CR.DESCRIPTION,
       CR.REQUEST_DATE,
       CR.REQUESTED_BY,
       (SELECT USER_NAME 
          FROM APPS.FND_USER 
         WHERE USER_ID = CR.REQUESTED_BY) REQUEST_BY_USER,
       (SELECT RESPONSIBILITY_NAME
          FROM FND_RESPONSIBILITY_TL
         WHERE RESPONSIBILITY_ID = CR.RESPONSIBILITY_ID
           AND LANGUAGE = 'US'
       ) RESPONSIBILITY,
       CR.RESUBMIT_INTERVAL,
       CR.PHASE_CODE,
       CR.STATUS_CODE,
       CR.HOLD_FLAG,
       CR.REQUESTED_START_DATE,
       CR.REQUEST_ID,
       CR.ARGUMENT_TEXT
  FROM FND_CONCURRENT_REQUESTS CR, 
       FND_CONCURRENT_PROGRAMS_TL CPTL
 WHERE CR.STATUS_CODE IN ('Q', 'I') AND 
       CR.REQUESTED_START_DATE > SYSDATE AND
       CPTL.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID AND
       CPTL.LANGUAGE = 'US'
 ORDER BY CPTL.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_START_DATE DESC

Concurrent Request Status and Phase Codes

Concurrent Request Status Codes:

SELECT LOOKUP_CODE STATUS_CODE, MEANING
 FROM FND_LOOKUP_VALUES
 WHERE LOOKUP_TYPE = 'CP_STATUS_CODE'
   AND ENABLED_FLAG = 'Y'
   AND VIEW_APPLICATION_ID = 0
ORDER BY LOOKUP_CODE;

STATUS_CODE	MEANING
A	Waiting
B	Resuming
C	Normal
D	Cancelled
E	Error
G	Warning
H	On Hold
I	Normal
M	No Manager
P	Scheduled
Q	Standby
R	Normal
S	Suspended
T	Terminating
U	Disabled
W	Paused
X	Terminated
Z	Waiting

Concurrent Request Phase Codes:

SELECT LOOKUP_CODE PHASE_CODE, MEANING
 FROM FND_LOOKUP_VALUES
 WHERE LOOKUP_TYPE = 'CP_PHASE_CODE'
   AND ENABLED_FLAG = 'Y'
   AND VIEW_APPLICATION_ID = 0
ORDER BY PHASE_CODE;

STATUS_CODE	MEANING
C	Completed
I	Inactive
P	Pending
R	Running

Inventory Period Close

Pending transactions for inventory period close can be viewed from Inventory Accounting Periods form.

Navigation: Accounting Close Cycle > Inventory Accounting Periods

Select Open period and click on Pending button

Transactions under Resolution Required and Unprocessed Shipping Transactions zones must be resolved before an accounting period can be closed.

Transactions under Resolution Recommended will not prevent the closing of a period but these transactions cannot be resolved after an accounting period is closed.

Below are the queries to check for the pending transactions.

--Unprocessed Material Transactions

 SELECT COUNT(*)
   FROM MTL_MATERIAL_TRANSACTIONS_TEMP
  WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
       TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND 
                       PERIOD_NAME = 'PERIOD_NAME') AND
      NVL(TRANSACTION_STATUS, 0) != 2

--Uncosted Material Transactions

SELECT /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */
       COUNT(*)
  FROM MTL_MATERIAL_TRANSACTIONS MMT
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
       TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND 
               PERIOD_NAME = 'PERIOD_NAME') AND
       COSTED_FLAG IS NOT NULL

--Pending WIP Costing Transactions

SELECT COUNT(*)
  FROM WIP_COST_TXN_INTERFACE
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
       TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND 
                       PERIOD_NAME = 'PERIOD_NAME')

--Uncosted WSM Transactions
      
SELECT COUNT(*)
  FROM WSM_SPLIT_MERGE_TRANSACTIONS
WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
       TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND 
                       PERIOD_NAME = 'PERIOD_NAME')

--Uncosted WSM Interface

SELECT COUNT(*)
  FROM WSM_SPLIT_MERGE_TXN_INTERFACE
WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
       TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND 
                       PERIOD_NAME = 'PERIOD_NAME')

--Pending Receiving Transactions

SELECT COUNT(*)
  FROM RCV_TRANSACTIONS_INTERFACE
WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
       TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND 
                       PERIOD_NAME = 'PERIOD_NAME') AND
       DESTINATION_TYPE_CODE = 'INVENTORY'

--Pending Material Transactions

SELECT COUNT(*)
   FROM MTL_TRANSACTIONS_INTERFACE
  WHERE ORGANIZATION_ID =
        (SELECT ORGANIZATION_ID
           FROM APPS.ORG_ORGANIZATION_DEFINITIONS
          WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
        TRANSACTION_DATE <
        (SELECT SCHEDULE_CLOSE_DATE + 1
           FROM APPS.ORG_ACCT_PERIODS
          WHERE ORGANIZATION_ID =
                (SELECT ORGANIZATION_ID
                   FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                  WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
                        PERIOD_NAME = 'PERIOD_NAME') AND 
        PROCESS_FLAG != 9

--Pending Shop Floor Move Transactions

SELECT COUNT(*)
  FROM WIP_MOVE_TXN_INTERFACE
WHERE ORGANIZATION_ID =
        (SELECT ORGANIZATION_ID
           FROM APPS.ORG_ORGANIZATION_DEFINITIONS
          WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
        TRANSACTION_DATE <
        (SELECT SCHEDULE_CLOSE_DATE + 1
           FROM APPS.ORG_ACCT_PERIODS
          WHERE ORGANIZATION_ID =
                (SELECT ORGANIZATION_ID
                   FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                  WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
                        PERIOD_NAME = 'PERIOD_NAME')
      
--Unprocessed Shipping Transactions

SELECT COUNT(*)
  FROM WSH_DELIVERY_DETAILS     WDD,
       WSH_DELIVERY_ASSIGNMENTS WDA,
       WSH_NEW_DELIVERIES       WND,
       WSH_DELIVERY_LEGS        WDL,
       WSH_TRIP_STOPS           WTS
WHERE WDD.SOURCE_CODE = 'OE' AND WDD.RELEASED_STATUS = 'C' AND
       WDD.INV_INTERFACED_FLAG IN ('N', 'P') AND 
       WDD.ORGANIZATION_ID = ORGZ_ID AND
       WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND
       WND.DELIVERY_ID = WDA.DELIVERY_ID AND
       WND.STATUS_CODE IN ('CL', 'IT') AND
       WDL.DELIVERY_ID = WND.DELIVERY_ID AND
       WTS.PENDING_INTERFACE_FLAG IN ('Y', 'P') AND
       TRUNC(WTS.ACTUAL_DEPARTURE_DATE) BETWEEN
       TO_DATE('PERIOD_START_DATE 00:00:00', 'DD-MON-YYYY HH24:MI:SS') AND
       TO_DATE('PERIOD_END_DATE   23:59:59', 'DD-MON-YYYY HH24:MI:SS') AND
       WDL.PICK_UP_STOP_ID = WTS.STOP_ID