INV Period close check for open or in error transactions
Description
SELECT 'UNPROCESSED MATERIAL TXNS TEMP' "TYPE OF ISSUE", COUNT(*) "UNPROCESSED COUNT", GP.PERIOD_NAME, MP.ORGANIZATION_CODE FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT, APPS.MTL_PARAMETERS MP, APPS.GL_PERIODS GP WHERE 1=1 AND NVL(MMTT.TRANSACTION_STATUS, 0) <> 2 AND MP.ORGANIZATION_ID = MMTT.ORGANIZATION_ID AND TRUNC(NVL(MMTT.TRANSACTION_DATE, SYSDATE)) BETWEEN GP.START_DATE AND GP.END_DATE AND GP.ADJUSTMENT_PERIOD_FLAG = 'N' GROUP BY GP.PERIOD_NAME, MP.ORGANIZATION_CODE UNION SELECT 'UNCOSTED MATERIAL TXNS' "TYPE OF ISSUE", COUNT(*), GP.PERIOD_NAME, MP.ORGANIZATION_CODE FROM MTL_MATERIAL_TRANSACTIONS MMT, APPS.MTL_PARAMETERS MP, APPS.GL_PERIODS GP WHERE 1=1 AND COSTED_FLAG IS NOT NULL AND MP.ORGANIZATION_ID = MMT.ORGANIZATION_ID AND TRUNC(NVL(MMT.TRANSACTION_DATE, SYSDATE)) BETWEEN GP.START_DATE AND GP.END_DATE AND GP.ADJUSTMENT_PERIOD_FLAG = 'N' GROUP BY GP.PERIOD_NAME, MP.ORGANIZATION_CODE UNION SELECT 'PENDING WIP TXNS' "TYPE OF ISSUE", COUNT(*) "UNPROCESSED COUNT", GP.PERIOD_NAME, MP.ORGANIZATION_CODE FROM WIP_COST_TXN_INTERFACE WCTI, APPS.MTL_PARAMETERS MP, APPS.GL_PERIODS GP WHERE 1=1 AND MP.ORGANIZATION_ID = WCTI.ORGANIZATION_ID AND TRUNC(NVL(WCTI.TRANSACTION_DATE, SYSDATE)) BETWEEN GP.START_DATE AND GP.END_DATE AND GP.ADJUSTMENT_PERIOD_FLAG = 'N' GROUP BY GP.PERIOD_NAME, MP.ORGANIZATION_CODE UNION SELECT 'UNCOSTED LOT JOBS' "TYPE OF ISSUE", COUNT(*) "UNPROCESSED COUNT", GP.PERIOD_NAME, MP.ORGANIZATION_CODE FROM WSM_SPLIT_MERGE_TRANSACTIONS WSMW, -- PENDING WSM - LOT BASED JOBS UNCOSTED (DOC ID 1967981.1) APPS.MTL_PARAMETERS MP, APPS.GL_PERIODS GP WHERE 1=1 AND COSTED <> 4 AND MP.ORGANIZATION_ID = WSMW.ORGANIZATION_ID AND TRUNC(NVL(WSMW.TRANSACTION_DATE, SYSDATE)) BETWEEN GP.START_DATE AND GP.END_DATE --AND GP.ADJUSTMENT_PERIOD_FLAG = 'N' GROUP BY GP.PERIOD_NAME, MP.ORGANIZATION_CODE UNION SELECT 'PENDING WSM INTERFACE' "TYPE OF ISSUE", COUNT(*) "UNPROCESSED COUNT", GP.PERIOD_NAME, MP.ORGANIZATION_CODE FROM WSM_SPLIT_MERGE_TXN_INTERFACE WSMTI, APPS.MTL_PARAMETERS MP, APPS.GL_PERIODS GP WHERE 1=1 AND PROCESS_STATUS <> 4 AND MP.ORGANIZATION_ID = WSMTI.ORGANIZATION_ID AND TRUNC(NVL(WSMTI.TRANSACTION_DATE, SYSDATE)) BETWEEN GP.START_DATE AND GP.END_DATE AND GP.ADJUSTMENT_PERIOD_FLAG = 'N' GROUP BY GP.PERIOD_NAME, MP.ORGANIZATION_CODE UNION SELECT 'PENDING LCM INTERFACE' "TYPE OF ISSUE", COUNT(*) "UNPROCESSED COUNT", GP.PERIOD_NAME, MP.ORGANIZATION_CODE FROM CST_LC_ADJ_INTERFACE CLAI, APPS.MTL_PARAMETERS MP, APPS.GL_PERIODS GP WHERE 1=1 AND MP.ORGANIZATION_ID = CLAI.ORGANIZATION_ID AND TRUNC(NVL(CLAI.TRANSACTION_DATE, SYSDATE)) BETWEEN GP.START_DATE AND GP.END_DATE AND GP.ADJUSTMENT_PERIOD_FLAG = 'N' GROUP BY GP.PERIOD_NAME, MP.ORGANIZATION_CODE UNION SELECT 'PENDING RECEIVING RFIX' "TYPE OF ISSUE", COUNT(*) "UNPROCESSED COUNT", GP.PERIOD_NAME, MP.ORGANIZATION_CODE FROM RCV_TRANSACTIONS_INTERFACE RTI, APPS.MTL_PARAMETERS MP, APPS.GL_PERIODS GP WHERE 1=1 AND DESTINATION_TYPE_CODE = 'INVENTORY' AND MP.ORGANIZATION_ID = RTI.TO_ORGANIZATION_ID AND TRUNC(NVL(RTI.TRANSACTION_DATE, SYSDATE)) BETWEEN GP.START_DATE AND GP.END_DATE AND GP.ADJUSTMENT_PERIOD_FLAG = 'N' GROUP BY GP.PERIOD_NAME, MP.ORGANIZATION_CODE UNION SELECT 'PENDING MATERIAL' "TYPE OF ISSUE", COUNT(*) "UNPROCESSED COUNT", GP.PERIOD_NAME, MP.ORGANIZATION_CODE FROM MTL_TRANSACTIONS_INTERFACE MTI, APPS.MTL_PARAMETERS MP, APPS.GL_PERIODS GP WHERE 1=1 AND PROCESS_FLAG <> 9 AND MP.ORGANIZATION_ID = MTI.ORGANIZATION_ID AND TRUNC(NVL(MTI.TRANSACTION_DATE, SYSDATE)) BETWEEN GP.START_DATE AND GP.END_DATE AND GP.ADJUSTMENT_PERIOD_FLAG = 'N' GROUP BY GP.PERIOD_NAME, MP.ORGANIZATION_CODE UNION SELECT 'PENDING SHOP FLOOR MOVE' "TYPE OF ISSUE", COUNT(*) "UNPROCESSED COUNT", GP.PERIOD_NAME, MP.ORGANIZATION_CODE FROM WIP_MOVE_TXN_INTERFACE WMTI, APPS.MTL_PARAMETERS MP, APPS.GL_PERIODS GP WHERE 1=1 AND MP.ORGANIZATION_ID = WMTI.ORGANIZATION_ID AND TRUNC(NVL(WMTI.TRANSACTION_DATE, SYSDATE)) BETWEEN GP.START_DATE AND GP.END_DATE AND GP.ADJUSTMENT_PERIOD_FLAG = 'N' GROUP BY GP.PERIOD_NAME, MP.ORGANIZATION_CODE UNION SELECT 'UNPROC SHIPPING TXNS' "TYPE OF ISSUE", COUNT(*) "UNPROCESSED COUNT", GP.PERIOD_NAME, MP.ORGANIZATION_CODE FROM WSH_DELIVERY_DETAILS WDD, WSH_DELIVERY_ASSIGNMENTS WDA, WSH_NEW_DELIVERIES WND, WSH_DELIVERY_LEGS WDL, WSH_TRIP_STOPS WTS, APPS.MTL_PARAMETERS MP, APPS.GL_PERIODS GP WHERE 1=1 AND 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 WDL.PICK_UP_STOP_ID = WTS.STOP_ID AND MP.ORGANIZATION_ID = WDD.ORGANIZATION_ID AND TRUNC(NVL(WTS.ACTUAL_DEPARTURE_DATE, SYSDATE)) BETWEEN GP.START_DATE AND GP.END_DATE AND GP.ADJUSTMENT_PERIOD_FLAG = 'N' GROUP BY GP.PERIOD_NAME, MP.ORGANIZATION_CODE UNION SELECT 'INCOMPLETE WORK ORDERS' "TYPE OF ISSUE", COUNT(*) "UNPROCESSED COUNT", GP.PERIOD_NAME, MP.ORGANIZATION_CODE FROM WIP_DISCRETE_JOBS WDJ, WIP_ENTITIES WE, APPS.MTL_PARAMETERS MP, APPS.GL_PERIODS GP WHERE 1=1 AND WDJ.STATUS_TYPE = 3 AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID AND WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID AND WE.ENTITY_TYPE = 6 AND MP.ORGANIZATION_ID = WE.ORGANIZATION_ID AND TRUNC(NVL(WDJ.DATE_COMPLETED, SYSDATE)) BETWEEN GP.START_DATE AND GP.END_DATE AND GP.ADJUSTMENT_PERIOD_FLAG = 'N' GROUP BY GP.PERIOD_NAME, MP.ORGANIZATION_CODE |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Organization Code |
|
LOV | |
Period From |
|
LOV | |
Period To |
|
LOV |