Query to Identify Transaction Source from MTL_TRANSACTIONS_TEMP

SELECT MMTT.TRANSACTION_SOURCE_ID,
CASE
WHEN MMTT.TRANSACTION_SOURCE_TYPE_ID = 1
THEN
(SELECT ‘RSH_NUMBER: ‘ || RCV.SHIPMENT_NUM
FROM APPS.RCV_SHIPMENT_HEADERS RCV, APPS.RCV_TRANSACTIONS RCVT
WHERE RCV.SHIPMENT_HEADER_ID = RCVT.SHIPMENT_HEADER_ID
AND RCVT.TRANSACTION_ID = MMTT.TRANSACTION_SOURCE_ID)
WHEN MMTT.TRANSACTION_SOURCE_TYPE_ID = 2
THEN
(SELECT ‘SO_NUMBER: ‘ || SEGMENT1
FROM APPS.MTL_SALES_ORDERS
WHERE SALES_ORDER_ID = MMTT.TRANSACTION_SOURCE_ID)
WHEN MMTT.TRANSACTION_SOURCE_TYPE_ID = 4
THEN
(SELECT ‘MO_NUMBER: ‘ || REQUEST_NUMBER
FROM APPS.MTL_TXN_REQUEST_HEADERS
WHERE REQUEST_NUMBER = TO_CHAR (MMTT.TRANSACTION_SOURCE_ID))
WHEN MMTT.TRANSACTION_SOURCE_TYPE_ID = 5
THEN
(SELECT ‘WIP_NUMBER: ‘ || WIP_ENTITY_NAME
FROM APPS.WIP_ENTITIES
WHERE WIP_ENTITY_ID = MMTT.TRANSACTION_SOURCE_ID)
WHEN MMTT.TRANSACTION_SOURCE_TYPE_ID = 7
THEN
(SELECT ‘INT_REQ_NUMBER: ‘ || RCV.SHIPMENT_NUM
FROM APPS.RCV_SHIPMENT_HEADERS RCV, APPS.RCV_TRANSACTIONS RCVT
WHERE RCV.SHIPMENT_HEADER_ID = RCVT.SHIPMENT_HEADER_ID
AND RCVT.TRANSACTION_ID = MMTT.TRANSACTION_SOURCE_ID)
WHEN MMTT.TRANSACTION_SOURCE_TYPE_ID = 8
THEN
(SELECT ‘INT_SO_NUMBER: ‘ || SEGMENT1
FROM APPS.MTL_SALES_ORDERS
WHERE SALES_ORDER_ID = MMTT.TRANSACTION_SOURCE_ID)
WHEN MMTT.TRANSACTION_SOURCE_TYPE_ID = 10
THEN
(SELECT ‘PHY_NAME: ‘ || PHYSICAL_INVENTORY_NAME
FROM APPS.MTL_PHYSICAL_INVENTORIES
WHERE PHYSICAL_INVENTORY_ID = MMTT.TRANSACTION_SOURCE_ID)
WHEN MMTT.TRANSACTION_SOURCE_TYPE_ID = 13
THEN
(SELECT ‘WIP_NUMBER: ‘ || WIP_ENTITY_NAME
FROM APPS.WIP_ENTITIES
WHERE WIP_ENTITY_ID = MMTT.TRANSACTION_SOURCE_ID)
ELSE
‘NULL’
END
SRC_NUMBER
FROM APPS.MTL_MATERIAL_TRANSACTIONS_TEMP MMTT;

FIND MOVE ORDER HISTORY:

SELECT MTL.LINE_ID, MTH.*
FROM APPS.MTL_TXN_REQUEST_HEADERS MTH,
APPS.MTL_TXN_REQUEST_LINES MTL
WHERE MTH.HEADER_ID = MTL.HEADER_ID
AND MTH.REQUEST_NUMBER IN ();

MOVE ORDER AND SHIPPING TRXNS LINK:

SELECT * FROM APPS.WSH_DELIVERABLES_V WHERE MOVE_ORDER_LINE_ID IN (SELECT MTL.LINE_ID
FROM APPS.MTL_TXN_REQUEST_HEADERS MTH,
APPS.MTL_TXN_REQUEST_LINES MTL
WHERE MTH.HEADER_ID = MTL.HEADER_ID
AND MTH.REQUEST_NUMBER IN ();

Uncosted Material Transactions Errors

ERROR MESSAGE: CSTPACIN.COST_INV_TXN:CSTPACDP.insert_account (10)
ORA-01654: unable to extend index INV.MTL_TRANSACTION_ACCOUNTS_N7 by 16 in tablespace APPS_TS_TX_IDX

SELECT transaction_id,
costed_flag,
actual_cost,
transaction_cost,
new_cost
FROM APPS.MTL_MATERIAL_TRANSACTIONS MMT
WHERE ERROR_CODE = ‘No Error’;

Orders Shipped but not Interfaced with INV/OM

SELECT WDD.SOURCE_HEADER_ID,
OOH.ORDER_NUMBER,
WND.DELIVERY_ID,
WND.NAME,
WDD.DELIVERY_DETAIL_ID,
WDL.PICK_UP_STOP_ID,
WDD.INV_INTERFACED_FLAG,
WDD.OE_INTERFACED_FLAG,
WND.STATUS_CODE,
WDD.RELEASED_STATUS
FROM APPS.WSH_DELIVERY_DETAILS WDD,
APPS.WSH_DELIVERY_ASSIGNMENTS WDA,
APPS.WSH_NEW_DELIVERIES WND,
APPS.WSH_DELIVERY_LEGS WDL,
APPS.WSH_TRIP_STOPS WTS,
APPS.OE_ORDER_HEADERS_ALL OOH,
APPS.OE_ORDER_LINES_ALL OOL
WHERE WDD.SOURCE_CODE = ‘OE’
AND WDD.RELEASED_STATUS = ‘C’
AND WDD.INV_INTERFACED_FLAG IN (‘N’, ‘P’)
AND WDD.ORGANIZATION_ID = &ORGANIZATION_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 TRUNC (WTS.ACTUAL_DEPARTURE_DATE) BETWEEN ‘&period_start_date’
AND ‘&period_end_date’
AND WDL.PICK_UP_STOP_ID = WTS.STOP_ID
AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID
AND WDD.SOURCE_LINE_ID = OOL.LINE_ID;

Uncosted Material Transactions

SELECT MMT.TRANSACTION_ID,
MIF.ITEM_NUMBER,
MMT.INVENTORY_ITEM_ID,
MMT.TRANSACTION_DATE,
MMT.TRANSACTION_QUANTITY,
MMT.PRIMARY_QUANTITY,
MMT.TRANSACTION_UOM,
MTT.TRANSACTION_TYPE_NAME,
MMT.TRANSACTION_TYPE_ID,
MMT.SUBINVENTORY_CODE,
MMT.LOCATOR_ID,
MMT.REVISION,
MMT.COSTED_FLAG,
MMT.COST_GROUP_ID,
MMT.TRANSACTION_GROUP_ID,
MMT.TRANSACTION_SET_ID,
MMT.LAST_UPDATE_DATE,
MMT.TRANSACTION_ACTION_ID,
MMT.COMPLETION_TRANSACTION_ID,
MTST.TRANSACTION_SOURCE_TYPE_NAME,
MMT.TRANSACTION_SOURCE_TYPE_ID,
MMT.TRANSACTION_SOURCE_ID,
MMT.TRANSACTION_SOURCE_NAME,
MMT.SOURCE_CODE,
MMT.SOURCE_LINE_ID,
MMT.REQUEST_ID,
MMT.TRANSFER_TRANSACTION_ID,
MMT.TRANSFER_ORGANIZATION_ID,
MP.ORGANIZATION_CODE TRANSFER_ORGANIZATION_CODE,
MMT.TRANSFER_SUBINVENTORY,
MMT.ERROR_CODE,
MMT.ERROR_EXPLANATION
FROM APPS.MTL_MATERIAL_TRANSACTIONS MMT,
APPS.MTL_ITEM_FLEXFIELDS MIF,
APPS.MTL_TRANSACTION_TYPES MTT,
APPS.MTL_TXN_SOURCE_TYPES MTST,
APPS.MTL_PARAMETERS MP
WHERE MMT.ORGANIZATION_ID = :I_ORG_ID
AND MMT.TRANSACTION_DATE <= :I_PERIOD_END_DATE
AND MMT.COSTED_FLAG IN (‘N’,’E’)
AND MMT.INVENTORY_ITEM_ID = MIF.INVENTORY_ITEM_ID (+)
AND MMT.ORGANIZATION_ID = MIF.ORGANIZATION_ID (+)
AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID (+)
AND MMT.TRANSACTION_SOURCE_TYPE_ID = MTST.TRANSACTION_SOURCE_TYPE_ID(+)
AND MMT.TRANSFER_ORGANIZATION_ID = MP.ORGANIZATION_ID (+)
ORDER BY MMT.TRANSACTION_DATE, MMT.TRANSACTION_ID;

Inventory Pending Receiving Transactions

SELECT RTI.INTERFACE_TRANSACTION_ID,
RTI.HEADER_INTERFACE_ID,
MIF.ITEM_NUMBER,
RTI.ITEM_ID,
RTI.GROUP_ID,
RTI.TRANSACTION_TYPE,
RTI.TRANSACTION_DATE,
RTI.PROCESSING_STATUS_CODE,
RTI.PROCESSING_MODE_CODE,
RTI.TRANSACTION_STATUS_CODE,
RTI.QUANTITY,
RTI.UNIT_OF_MEASURE,
RTI.AUTO_TRANSACT_CODE,
RTI.RECEIPT_SOURCE_CODE,
RTI.DESTINATION_TYPE_CODE,
RTI.SOURCE_DOCUMENT_CODE,
RTI.CURRENCY_CODE,
RTI.DOCUMENT_NUM,
RTI.SHIP_TO_LOCATION_ID,
HL.LOCATION_CODE,
RTI.PARENT_TRANSACTION_ID,
RTI.PO_HEADER_ID,
RTI.PO_LINE_ID,
RTI.PO_RELEASE_ID,
POR.RELEASE_NUM,
POH.SEGMENT1,
RTI.VENDOR_ID,
RTI.VENDOR_SITE_ID,
RTI.OE_ORDER_HEADER_ID,
RTI.OE_ORDER_LINE_ID,
RTI.VALIDATION_FLAG,
RTI.SUBINVENTORY,
POL.LINE_NUM,
PIE.COLUMN_NAME,
PIE.ERROR_MESSAGE
FROM APPS.RCV_TRANSACTIONS_INTERFACE RTI,
APPS.PO_INTERFACE_ERRORS PIE,
APPS.MTL_ITEM_FLEXFIELDS MIF,
APPS.PO_HEADERS_ALL POH,
APPS.PO_LINES_ALL POL,
APPS.PO_RELEASES_ALL POR,
APPS.HR_LOCATIONS_ALL HL
WHERE TO_ORGANIZATION_ID IN (‘ ‘)
AND TRANSACTION_DATE <= :I_PERIOD_END_DATE AND DESTINATION_TYPE_CODE IN (‘INVENTORY’, ‘SHOP FLOOR’) AND RTI.PO_HEADER_ID = POH.PO_HEADER_ID(+) AND RTI.PO_LINE_ID = POL.PO_LINE_ID(+) AND RTI.PO_RELEASE_ID = POR.PO_RELEASE_ID(+) AND RTI.TO_ORGANIZATION_ID = MIF.ORGANIZATION_ID (+) AND RTI.ITEM_ID = MIF.INVENTORY_ITEM_ID (+) AND RTI.INTERFACE_TRANSACTION_ID = PIE.INTERFACE_TRANSACTION_ID(+) AND RTI.SHIP_TO_LOCATION_ID = HL.LOCATION_ID (+) AND TRUNC(RTI.TRANSACTION_DATE) >= TRUNC(SYSDATE -30)
ORDER BY RTI.TRANSACTION_DATE, RTI.INTERFACE_TRANSACTION_ID;

Inventory Pending Shipping Transactions (INV Period Close Exceptions)

SELECT M.SOURCE_HEADER_ID, M.SOURCE_LINE_ID, M.CREATION_DATE, MSI.SEGMENT1 ITEM_NUMBER, M.ORGANIZATION_CODE, M.TRANSACTION_QUANTITY, M.TRANSACTION_UOM,M.SUBINVENTORY_CODE, MIL.SEGMENT1||’.’||MIL.SEGMENT2||’.’||MIL.SEGMENT3||’.’||MIL.SEGMENT4 LOCATOR_NAME,
M.TRANSACTION_SOURCE_ID,M.TRANSACTION_REFERENCE, M.TRANSFER_SUBINVENTORY, M.TRANSFER_LOCATOR, M.TRANSFER_ORGANIZATION_CODE,
M.SHIPMENT_NUMBER, M.ERROR_EXPLANATION, M.ERROR_CODE , M.TRANSACTION_INTERFACE_ID, M.TRANSACTION_HEADER_ID, M.SOURCE_CODE, M.PROCESS_FLAG_DESC, M.TRANSACTION_DATE, M.TRANSACTION_TYPE_NAME, M.DISTRIBUTION_ACCOUNT_ID, M.SOURCE_PROJECT_ID, M.SOURCE_TASK_ID, M.EXPENDITURE_TYPE
FROM APPS.MTL_TRANSACTIONS_INTERFACE_V M,
APPS.MTL_SYSTEM_ITEMS_B MSI,
APPS.MTL_ITEM_LOCATIONS MIL
WHERE M.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND M.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND M.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID
AND M.ORGANIZATION_ID = MIL.ORGANIZATION_ID AND M.PROCESS_FLAG_DESC <> ‘Yes’
AND TRUNC(M.CREATION_DATE) > TRUNC(SYSDATE -30)
ORDER BY M.SOURCE_HEADER_ID DESC;

SQL Query to Verify Inventory Item Images

SELECT DISTINCT MSI.SEGMENT1 ITEM_NUMBER, MSI.DESCRIPTION, MSI.INVENTORY_ITEM_STATUS_CODE ITEM_STATUS, AV.PICTURE, AV.THUMBNAIL_IMAGE–, moq.transaction_quantity
FROM APPS.MTL_SYSTEM_ITEMS_B MSI,
–apps.MTL_onhand_quantities moq,
APPS.ICX_CAT_ATTRIBUTE_VALUES AV
WHERE –msi.inventory_item_id = moq.inventory_item_id(+)
–msi.organization_id = moq.organization_id
MSI.INVENTORY_ITEM_ID = AV.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = ‘ ‘
AND MSI.INVENTORY_ITEM_STATUS_CODE = ‘Active’
AND AV.PICTURE IS NULL
AND STOCK_ENABLED_FLAG = ‘Y’
–and msi.item_type = ‘NS’
ORDER BY MSI.SEGMENT1 DESC;

Inventory On Hand Quantity

SELECT MSI.SEGMENT1, MOQ.TRANSACTION_QUANTITY, MOQ.SUBINVENTORY_CODE, MIL.SEGMENT1||’.’||MIL.SEGMENT2||’.’||MIL.SEGMENT3||’.’||MIL.SEGMENT4 ITEM_LOCATOR_NAME
FROM APPS.MTL_SYSTEM_ITEMS_B MSI,
APPS.MTL_ONHAND_QUANTITIES MOQ,
APPS.MTL_ITEM_LOCATIONS MIL
WHERE MSI.INVENTORY_ITEM_ID = MOQ.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MOQ.ORGANIZATION_ID
AND MOQ.INVENTORY_ITEM_ID = MIL.INVENTORY_ITEM_ID
AND MOQ.ORGANIZATION_ID = MIL.ORGANIZATION_ID
AND MOQ.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID
AND MSI.INVENTORY_ITEM_ID IN ();

Inventory Transactions Open Interface Errors (SQL)

SELECT M.SOURCE_HEADER_ID, M.SOURCE_LINE_ID, M.CREATION_DATE, MSI.SEGMENT1,
M.ORGANIZATION_CODE, M.TRANSACTION_QUANTITY, M.TRANSACTION_UOM,
M.SUBINVENTORY_CODE,
MIL.SEGMENT1||’.’||MIL.SEGMENT2||’.’||MIL.SEGMENT3||’.’||MIL.SEGMENT4 LOCATOR_NAME,
M.TRANSACTION_SOURCE_ID,M.TRANSACTION_REFERENCE, M.TRANSFER_SUBINVENTORY,
M.TRANSFER_LOCATOR, M.TRANSFER_ORGANIZATION_CODE,
M.SHIPMENT_NUMBER, M.ERROR_EXPLANATION, M.ERROR_CODE
FROM APPS.MTL_TRANSACTIONS_INTERFACE_V M,
APPS.MTL_SYSTEM_ITEMS_B MSI,
APPS.MTL_ITEM_LOCATIONS MIL
WHERE M.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND M.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND M.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID
AND M.ORGANIZATION_ID = MIL.ORGANIZATION_ID
AND M.PROCESS_FLAG_DESC <> ‘Yes’
AND TRUNC(M.CREATION_DATE) > TRUNC(SYSDATE -30)
ORDER BY M.SOURCE_HEADER_ID DESC;

Purchase Order Interfaces

PO INBOUND INTERFACE

Interface Tables:

PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE
PO_INTERFACE_ERRORS where interface_type = ‘PO_DOCS_OPEN_INTERFACE’ (Error Table)

Base Tables:

PO_HEADERS_ALL
PO_LINES_ALL
PO_DISTRIBUTIONS_ALL
PO_LINE_LOCATIONS_ALL

Concurrent Programs:

Import Standard Purchase Orders.
Import Price Catalogs(To import Catalog Quotations, Standard Quotations, and Blanket Purchase Agreements)

REQUISITIONS INBOUND INTERFACE

Interface Tables:

PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
PO_INTERFACE_ERRORS where interface_type =’REQIMPORT’ (Error Table)

Base Tables:

PO_REQUISITIONS_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL

Concurrent Programs:

Requisition Import