Inventory Interfaces

Item Inbound Interface

Interface Tables:

  • MTL_SYSTEM_ITEMS_INTERFACE
  • MTL_ITEM_REVISIONS_INTERFACE
  • MTL_ITEM_CATEGORIES_INTERFACE
  • MTL_INTERFACE_ERRORS (Error Table)

Base Tables:

  • MTL_SYSTEM_ITEMS_B
  • MTL_ITEM_REVISIONS_B
  • MTL_CATEGORIES_B
  • MTL_CATEGORY_SETS_B

Concurrent Programs:

  • Import Items

Accounts Payables Interfaces

INVOICE INTERFACE

Interface Tables:

  • AP_INVOICES_INTERFACE
  • AP_INVOICE_LINES_INTERFACE
  • AP_INTERFACE_REJECTIONS (Error Table)

Base Tables:

  • AP_INVOICES_ALL
  • AP_INVOICE_LINES_ALL
  • AP_INVOICE_DISTRIBUTIONS_ALL

Concurrent Program:

  • Payables Open Interface Import

SUPPLIER INTERFACE:

Interface tables:

  • AP_SUPPLIERS_INT
  • AP_SUPPLIER_SITES_INT
  • AP_SUP_SITE_CONTACT_INT
  • AP_SUPPLIER_INT_REJECTIONS (Error table for Supplier, Site and Contact inbound based on parent_table column)

Base Tables:

  • PO_VENDORS
  • PO_VENDOR_SITES_ALL
  • PO_VENDOR_CONTACTS
  • AP_SUPPLIERS
  • AP_SUPPLIER_SITES_ALL
  • AP_SUPPLIER_CONTACTS

Concurrent programs:

  • Supplier Open Interface Import
  • Supplier Sites Open Interface Import
  • Supplier Site Contacts Open Interface Import

Request Set:

  • Supplier Open Interface Request Set (Contain all 3 above mentioned concurrent programs)

Enable Diagnostics>>Examine

Enable Oracle apps Diagnostics-> Examine

Step 1

Navigate to System Administrator responsibility> Profile> System>

Step 2

Enter profile name: Utilities:Diagnostics

Enter Application User for whom you want to enable Diagnostics-> Examine

Step 3

Give Yes at User level and Save the Changes.

You can set Yes at Site level also if you want to enable this option for all Oracle application users

Step 4

Navigate to System Administrator responsibility > Profile > System >

Enter profile name: Hide Diagnostics menu entry

Enter Application User for whom you do not want to hide Diagnostics menu entry

Query Linking Internal Requisition And Internal Sales Order Details

SELECT PRH.SEGMENT1 REQ_NUMBER,
PRH.CREATION_DATE,
PPF.FULL_NAME REQUESTOR,
HL.LOCATION_CODE,
OOD.ORGANIZATION_CODE DESTINATION_ORG_CODE,
OOD.ORGANIZATION_NAME DESTINATION_ORG_NAME,
OOD1.ORGANIZATION_CODE SOURCE_ORG_CODE,
OOD1.ORGANIZATION_NAME SOURCE_ORG_NAME,
PRH.AUTHORIZATION_STATUS IR_STATUS,
(SELECT ACTION_DATE
FROM APPS.PO_ACTION_HISTORY
WHERE OBJECT_TYPE_CODE = ‘REQUISITION’
AND ACTION_CODE = ‘APPROVE’
AND EMPLOYEE_ID = PRH.PREPARER_ID
AND OBJECT_ID = PRH.REQUISITION_HEADER_ID)
REQ_APPROVED_DATE,
MSI.SEGMENT1 ITEM_CODE,
MSI.DESCRIPTION ITEM_DESCRIPTION,
PRL.QUANTITY,
PRL.UNIT_MEAS_LOOKUP_CODE,
OOH.ORDER_NUMBER INTERNAL_SO_NUMBER,
OOH.ORDERED_DATE SALE_ORDER_DATE,
(SELECT DISTINCT REQUEST_NUMBER
FROM APPS.WSH_DELIVERY_DETAILS WDD,
APPS.MTL_TXN_REQUEST_LINES MTRL,
APPS.MTL_TXN_REQUEST_HEADERS MTRH
WHERE WDD.SOURCE_HEADER_ID = OOH.HEADER_ID
AND WDD.MOVE_ORDER_LINE_ID = MTRL.LINE_ID
AND MTRL.HEADER_ID = MTRH.HEADER_ID)
MOVE_ORDER_REF,
(SELECT MTRH.CREATION_DATE
FROM APPS.WSH_DELIVERY_DETAILS WDD,
APPS.MTL_TXN_REQUEST_LINES MTRL,
APPS.MTL_TXN_REQUEST_HEADERS MTRH
WHERE WDD.SOURCE_HEADER_ID = OOH.HEADER_ID
AND WDD.MOVE_ORDER_LINE_ID = MTRL.LINE_ID
AND MTRL.HEADER_ID = MTRH.HEADER_ID
AND ROWNUM = 1)
TRANSACT_MOVE_ORDER_DATE,
(SELECT GLOBAL_ATTRIBUTE16
FROM APPS.WSH_NEW_DELIVERIES WND,
APPS.WSH_DELIVERY_ASSIGNMENTS WDA,
APPS.WSH_DELIVERY_DETAILS WDD
WHERE WDD.SOURCE_HEADER_ID = OOH.HEADER_ID
AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID = WND.DELIVERY_ID
AND ROWNUM = 1)
ACTUAL_DISPATCH_DATE,
(SELECT RSH.RECEIPT_NUM
FROM APPS.RCV_TRANSACTIONS RCV, APPS.RCV_SHIPMENT_HEADERS RSH
WHERE RCV.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID
AND RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND ROWNUM = 1)
DESTINATION_ORG_MRN_NUMBER,
(SELECT RSH.CREATION_DATE
FROM APPS.RCV_TRANSACTIONS RCV, APPS.RCV_SHIPMENT_HEADERS RSH
WHERE RCV.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID
AND RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND ROWNUM = 1)
DESTINATION_ORG_MRN_DATE
FROM APPS.PO_REQUISITION_HEADERS_ALL PRH,
APPS.PO_REQUISITION_LINES_ALL PRL,
APPS.PER_ALL_PEOPLE_F PPF,
APPS.HR_LOCATIONS HL,
APPS.ORG_ORGANIZATION_DEFINITIONS OOD,
APPS.ORG_ORGANIZATION_DEFINITIONS OOD1,
APPS.MTL_SYSTEM_ITEMS_B MSI,
APPS.OE_ORDER_HEADERS_ALL OOH
WHERE 1 = 1
AND PRH.SEGMENT1 = ‘&REQ_NUMBER’
AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND PRL.TO_PERSON_ID = PPF.PERSON_ID
AND PPF.EFFECTIVE_END_DATE > SYSDATE
AND PRL.DELIVER_TO_LOCATION_ID = HL.LOCATION_ID(+)
AND PRL.DESTINATION_ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND PRL.SOURCE_ORGANIZATION_ID = OOD1.ORGANIZATION_ID
AND PRL.ITEM_ID = MSI.INVENTORY_ITEM_ID
AND PRL.SOURCE_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND PRH.REQUISITION_HEADER_ID = OOH.SOURCE_DOCUMENT_ID;