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>';