Period Close checking

=========Pending Chevk ==============


select * from mtl_txn_request_lines l,  mtl_txn_request_headers h
where l.LINE_ID=5949945
and l.HEADER_ID=h.HEADER_ID





 SELECT distinct --TRANSACTION_TYPE_ID,
 t.SUBINVENTORY_CODE, COUNT(*)
  --select *
  FROM MTL_MATERIAL_TRANSACTIONS_TEMP t
  where t.TRANSACTION_DATE<'01-SEP-2016'
  --and t.TRANSACTION_type_id = 35 --
  and t.TRANSACTION_DATE>='01-AUG-2016'  and  t.SUBINVENTORY_CODE not like 'D%'
  --and SUBINVENTORY_CODE like 'G10%'
  --and TRANSACTION_SOURCE_ID= 620388
  group by --TRANSACTION_TYPE_ID,
  t.SUBINVENTORY_CODE
  order by t.SUBINVENTORY_CODE desc
 
  ACCT_PERIOD_ID=139723
 
  select * from org_acct_periods where  ACCT_PERIOD_ID=139723
 
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
 

--Uncosted Material Transactions

SELECT /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */
*-- COUNT(*)
  FROM MTL_MATERIAL_TRANSACTIONS MMT
 WHERE ORGANIZATION_ID in
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
        -- WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>'
         )
   AND TRANSACTION_DATE <
       (SELECT distinct SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID in
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                -- WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>'
                 )
           AND PERIOD_NAME = :PERIOD_NAME)
   AND COSTED_FLAG IS NOT NULL

--Pending WIP Costing Transactions

SELECT COUNT(*)
  FROM WIP_COST_TXN_INTERFACE
 WHERE ORGANIZATION_ID in
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         --WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>'
         )
   AND TRANSACTION_DATE <
       (SELECT distinct 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)

--Uncosted WSM Transactions
    
SELECT *--COUNT(*)
  FROM WSM_SPLIT_MERGE_TRANSACTIONS
 WHERE ORGANIZATION_ID in
       (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)

--Uncosted WSM Interface

SELECT *--COUNT(*)
  FROM WSM_SPLIT_MERGE_TXN_INTERFACE
 WHERE ORGANIZATION_ID in
       (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)

--Pending Receiving Transactions

SELECT *--COUNT(*)
  FROM RCV_TRANSACTIONS_INTERFACE
 WHERE to_ORGANIZATION_ID in
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
        -- WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>'
         )
   AND TRANSACTION_DATE <
       (SELECT distinct SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID in
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                -- WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>'
                 )
           AND PERIOD_NAME = :PERIOD_NAME)
   AND DESTINATION_TYPE_CODE = 'INVENTORY'

--Pending Material Transactions

SELECT *--COUNT(*)
  FROM MTL_TRANSACTIONS_INTERFACE
 WHERE ORGANIZATION_ID in
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         --WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>'
         )
   AND TRANSACTION_DATE <
       (
       SELECT distinct SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID in
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 --WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>'
                 )
           AND PERIOD_NAME = 'Feb-16-17'--<PERIOD_NAME>'
           )
   AND PROCESS_FLAG <> 9


--Pending Shop Floor Move Transactions

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 = 'Feb-16-17')--<PERIOD_NAME>')

--Unprocessed Shipping Transactions


SELECT *--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 = 86
   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 TRUNC(WTS.ACTUAL_DEPARTURE_DATE) BETWEEN
       TO_DATE('01-FEB-2017 00:00:00', 'DD-MON-YYYY HH24:MI:SS') AND
       TO_DATE('28-FEB-2017 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
   AND WDL.PICK_UP_STOP_ID = WTS.STOP_ID