miércoles, 12 de agosto de 2015

Scripts para localizar transacciones pendientes

Las transacciones pendientes para un cierre de un período de inventario se pueden ver en la pantalla de Períodos Contables de InventarioSeleccione un período Abierto y haga clic en el botón Pendiente



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

1 comentario:

  1. 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.

    ResponderEliminar