Aquellas Transacciones bajo Solución Requerida y Transacciones de Envió no Procesadas deben ser resueltos antes de cerrar el período contable. Aquellas Transacciones en Solución Recomendada no evitará el cierre de un período, pero estas operaciones no se podrán resolver después de que un período contable este cerrado.
Los siguientes Scripts nos podrán ayudar a localizar cuantos registros tenemos pendientes antes de cerrar un periodo contable dentro del modulo de Inventarios.
--Transacciones de Materiales
no Procesadas
SELECT COUNT(*)
FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE ORGANIZATION_ID =
(SELECT ORGANIZATION_ID
FROM APPS.ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_CODE = '&ORGANIZATION_CODE' AND
TRANSACTION_DATE <
(SELECT SCHEDULE_CLOSE_DATE + 1
FROM APPS.ORG_ACCT_PERIODS
WHERE ORGANIZATION_ID =
(SELECT ORGANIZATION_ID
FROM APPS.ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_CODE = '&ORGANIZATION_CODE')
AND PERIOD_NAME = '&PERIOD_NAME'))
AND NVL(TRANSACTION_STATUS, 0) <> 2
--Transacciones de Materiales
Sin costear
SELECT /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */
COUNT(*)
FROM MTL_MATERIAL_TRANSACTIONS MMT
WHERE ORGANIZATION_ID =
(SELECT ORGANIZATION_ID
FROM APPS.ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_CODE = '&ORGANIZATION_CODE')
AND TRANSACTION_DATE <
(SELECT SCHEDULE_CLOSE_DATE + 1
FROM APPS.ORG_ACCT_PERIODS
WHERE ORGANIZATION_ID =
(SELECT ORGANIZATION_ID
FROM APPS.ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_CODE = '&ORGANIZATION_CODE')
AND PERIOD_NAME = '&PERIOD_NAME')
AND COSTED_FLAG IS NOT NULL
--Transacciones
WIP Sin costear
SELECT COUNT(*)
FROM WIP_COST_TXN_INTERFACE
WHERE ORGANIZATION_ID =
(SELECT ORGANIZATION_ID
FROM APPS.ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_CODE = '&ORGANIZATION_CODE')
AND TRANSACTION_DATE <
(SELECT SCHEDULE_CLOSE_DATE + 1
FROM APPS.ORG_ACCT_PERIODS
WHERE ORGANIZATION_ID =
(SELECT ORGANIZATION_ID
FROM APPS.ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_CODE ='&ORGANIZATION_CODE')
AND PERIOD_NAME = '&PERIOD_NAME')
--Transacciones
WSM Sin costear
SELECT COUNT(*)
FROM WSM_SPLIT_MERGE_TRANSACTIONS
WHERE ORGANIZATION_ID =
(SELECT ORGANIZATION_ID
FROM APPS.ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_CODE = '&ORGANIZATION_CODE')
AND TRANSACTION_DATE <
(SELECT SCHEDULE_CLOSE_DATE + 1
FROM APPS.ORG_ACCT_PERIODS
WHERE ORGANIZATION_ID =
(SELECT ORGANIZATION_ID
FROM APPS.ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_CODE = '&ORGANIZATION_CODE')
AND PERIOD_NAME = '&PERIOD_NAME')
--Transacciones
Interface WSM Sin costear
SELECT COUNT(*)
FROM WSM_SPLIT_MERGE_TXN_INTERFACE
WHERE ORGANIZATION_ID =
(SELECT ORGANIZATION_ID
FROM APPS.ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_CODE = '&ORGANIZATION_CODE')
AND TRANSACTION_DATE <
(SELECT SCHEDULE_CLOSE_DATE + 1
FROM APPS.ORG_ACCT_PERIODS
WHERE ORGANIZATION_ID =
(SELECT ORGANIZATION_ID
FROM APPS.ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_CODE = '&ORGANIZATION_CODE')
AND PERIOD_NAME = '&PERIOD_NAME')
--Transacciones de Recepcion
Pendientes
SELECT COUNT(*)
FROM RCV_TRANSACTIONS_INTERFACE
WHERE TO_ORGANIZATION_ID =
(SELECT ORGANIZATION_ID
FROM APPS.ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_CODE = '&ORGANIZATION_CODE')
AND TRANSACTION_DATE <
(SELECT SCHEDULE_CLOSE_DATE + 1
FROM APPS.ORG_ACCT_PERIODS
WHERE ORGANIZATION_ID =
(SELECT ORGANIZATION_ID
FROM APPS.ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_CODE = '&ORGANIZATION_CODE')
AND PERIOD_NAME = '&PERIOD_NAME')
AND DESTINATION_TYPE_CODE = 'INVENTORY'
--Transacciones de Materiales
Pendientes
SELECT COUNT(*)
FROM MTL_TRANSACTIONS_INTERFACE
WHERE ORGANIZATION_ID =
(SELECT ORGANIZATION_ID
FROM APPS.ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_CODE = '&ORGANIZATION_CODE')
AND TRANSACTION_DATE <
(SELECT SCHEDULE_CLOSE_DATE + 1
FROM APPS.ORG_ACCT_PERIODS
WHERE ORGANIZATION_ID =
(SELECT ORGANIZATION_ID
FROM APPS.ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_CODE = '&ORGANIZATION_CODE')
AND PERIOD_NAME = '&PERIOD_NAME')
AND PROCESS_FLAG <> 9
--Transacciones Movimiento de
Planta Pendientes
SELECT COUNT(*)
FROM WIP_MOVE_TXN_INTERFACE
WHERE ORGANIZATION_ID =
(SELECT ORGANIZATION_ID
FROM APPS.ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_CODE = '&ORGANIZATION_CODE')
AND TRANSACTION_DATE <
(SELECT SCHEDULE_CLOSE_DATE + 1
FROM APPS.ORG_ACCT_PERIODS
WHERE ORGANIZATION_ID =
(SELECT ORGANIZATION_ID
FROM APPS.ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_CODE = '&ORGANIZATION_CODE')
AND PERIOD_NAME = '&PERIOD_NAME')
--Transacciones de envios no
Procesados
SELECT WTS.ACTUAL_DEPARTURE_DATE--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 WDD.ORGANIZATION_ID = (SELECT ORGANIZATION_ID
FROM APPS.ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_CODE = '&ORGANIZATION_CODE')
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 IN ('Y', 'P')
AND TO_CHAR(TRUNC(WTS.ACTUAL_DEPARTURE_DATE),'MON-YY')='&PERIOD_NAME'
AND WDL.PICK_UP_STOP_ID = WTS.STOP_ID
Hola Gustavo, excelente material y conocimiento que compartes en tu Blog. Quería preguntarte si conoces de algún documento o Link en el que se mencionen las nuevas funcionalidades o características del módulo de inventarios de R12 respecto a R11. Gracias.
ResponderEliminarGracias te recomiendo este blog http://oraclemasterminds.blogspot.pe/
Eliminar