[EBS.SQL.Note] Pending Transaction

About Oracle System Pending Transaction check SQL.
查看 Pending Transaction 畫面
Inventory Accounting Periods > Pending >






查看 Pending Transaction 畫面


Resolution Required

1.Unprocessed Material

SELECT COUNT(*) U1_COUNT 

FROM MTL_MATERIAL_TRANSACTIONS_TEMP 

WHERE TRANSACTION_DATE <= SYSDATE AND NVL(TRANSACTION_STATUS,0) <> 2

2.Uncosted Material

 SELECT U2.ORGANIZATION_ID, COUNT(*) U2_COUNT 

FROM MTL_MATERIAL_TRANSACTIONS U2 

WHERE U2.TRANSACTION_DATE <= SYSDATE AND U2.COSTED_FLAG IS NOT NULL 

GROUP BY U2.ORGANIZATION_ID

3.Pending WIP Transactions

SELECT COUNT(*) U3_COUNT 

FROM WIP_COST_TXN_INTERFACE 

WHERE TRANSACTION_DATE <= SYSDATE

4.Uncosted WSM

SELECT COUNT(*) U4_COUNT 

FROM WSM_SPLIT_MERGE_TRANSACTIONS 

WHERE COSTED <> 4 AND TRANSACTION_DATE <= SYSDATE

5.Pending WMS Interface

SELECT COUNT(*) U5_COUNT 

FROM WSM_SPLIT_MERGE_TXN_INTERFACE 

WHERE PROCESS_STATUS <> 4 AND TRANSACTION_DATE <= SYSDATE

Resolution Recommended

6.Pending Receiving

SELECT T.TO_ORGANIZATION_ID, COUNT(*) U6_COUNT 

FROM RCV_TRANSACTIONS_INTERFACE T 

WHERE T.TRANSACTION_DATE <= SYSDATE AND T.DESTINATION_TYPE_CODE = 'INVENTORY' 

GROUP BY T.TO_ORGANIZATION_ID

7.Pending Material

SELECT COUNT(*) U7_COUNT 

FROM MTL_TRANSACTIONS_INTERFACE 

WHERE TRANSACTION_DATE <= SYSDATE AND PROCESS_FLAG <> 9

8.Pending Shop Floor Move

SELECT COUNT(*) U8_COUNT 

FROM WIP_MOVE_TXN_INTERFACE 

WHERE TRANSACTION_DATE <= SYSDATE

Resolution Required / Recommended

9.Unprocessed Shipping Transactions

(Pending Transactions)

SELECT COUNT(*) U9_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 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 = 'Y' 

AND TRUNC(WTS.ACTUAL_DEPARTURE_DATE) BETWEEN SYSDATE-31 AND SYSDATE+1 

AND WDL.PICK_UP_STOP_ID = WTS.STOP_ID