INV period closing Important Queries R12

1.UNPROCESSED MATERIAL
2.UNCOSTED MATERIAL
3.PENDING WIP COSTING
4.UNCOSTED WSM TRANSACTIONS
5.PENDING WSM INTERFACE
6.PENDING SHIPPING TRANSACTIONS
7.PENDING RECEIVING (Interface Errors)
8. PENDING MATERIAL
9.PENDING SHOP FLOOR MOVE
10. INCOMPLETE WORK ORDERS
11.FAILED CLOSE JOBS
12.COMPLETE JOBS
13. RECEIPT PENDING
14.PENDING CLOSE JOBS
15. RMA PENDING RECEIPTS
16.PENDING RECEIVING ACCOUNTING



1.UNPROCESSED MATERIAL
  ====================

select mtlp.organization_code
       ,count(*)
FROM   apps.mtl_material_transactions_temp mmtt,
       apps.mtl_transaction_lots_temp      mtlt,
       apps.mtl_serial_numbers_temp        msnt,
       apps.mtl_parameters                 mtlp,
       apps.org_acct_periods oap
WHERE  --period_name = upper('&period')
period_name = upper(:period)
AND    oap.acct_period_id  = mmtt.acct_period_id
and    mmtt.organization_id = mtlp.organization_id
and    mmtt.organization_id = oap.organization_id
and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
AND    nvl(transaction_status,0) <> 2
AND    (mtlt.transaction_temp_id (+)     = mmtt.transaction_temp_id
AND msnt.transaction_temp_id (+) = mmtt.transaction_temp_id)
group by mtlp.organization_code
ORDER BY mtlp.organization_code

2.UNCOSTED MATERIAL
  =================

select mtlp.organization_code
       ,count(*)
FROM   apps.mtl_material_transactions mmt,
       apps.mtl_parameters                 mtlp,
       apps.org_acct_periods oap
WHERE  --period_name = upper('&period')
period_name = upper(:period)
AND    oap.acct_period_id  = mmt.acct_period_id
and    mmt.organization_id = oap.organization_id
and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
and    mmt.organization_id = mtlp.organization_id
AND    COSTED_FLAG IS NOT NULL
group by mtlp.organization_code
ORDER BY mtlp.organization_code

3.PENDING WIP COSTING
  ===================

select mtlp.organization_code
       ,count(*)
FROM   apps.wip_cost_txn_interface wcti,
       apps.wip_txn_interface_errors wtie,
       apps.mtl_parameters                 mtlp,
       apps.org_acct_periods oap
WHERE  period_name = upper('&period')
AND    oap.acct_period_id  = wcti.acct_period_id
and    wcti.organization_id = oap.organization_id
and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
and    wcti.organization_id = mtlp.organization_id
AND    wtie.transaction_id (+) = wcti.transaction_id
  group by mtlp.organization_code
ORDER BY mtlp.organization_code

4.UNCOSTED WSM TRANSACTIONS
  =========================

select mtlp.organization_code
       ,count(*)
FROM   apps.WSM_SPLIT_MERGE_TRANSACTIONS wsmt,
       apps.mtl_parameters                 mtlp,
       apps.org_acct_periods oap
WHERE  --oap.period_name = upper('&period')
oap.period_name = upper(:period)
and    wsmt.organization_id = oap.organization_id
and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
AND    COSTED <> 4
and    trunc(transaction_date) < (trunc(to_date(oap.schedule_close_date))+1)
and    wsmt.organization_id = mtlp.organization_id
group by mtlp.organization_code
ORDER BY mtlp.organization_code

5.PENDING WSM INTERFACE
  =====================

select mtlp.organization_code
       ,count(*)
FROM   apps.WSM_SPLIT_MERGE_TXN_INTERFACE wsmt,
       apps.mtl_parameters                 mtlp,
       apps.org_acct_periods oap
WHERE  --oap.period_name = upper('&Period')
period_name = upper(:period)
and    wsmt.organization_id = oap.organization_id
and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
AND    process_status <> 4
and    trunc(transaction_date) < (trunc(to_date(oap.schedule_close_date))+1)
and    wsmt.organization_id = mtlp.organization_id
group by mtlp.organization_code
ORDER BY mtlp.organization_code

6.PENDING SHIPPING TRANSACTIONS
  =============================

select mtlp.organization_code
       ,count(*)
from   apps.wsh_delivery_details     wdd,
       apps.wsh_delivery_assignments wda,
       apps.wsh_new_deliveries       wnd,
       apps.wsh_delivery_legs        wdl,
       apps.wsh_trip_stops           wts,
       apps.mtl_parameters                 mtlp,
       apps.org_acct_periods oap
where  wdd.source_code         = 'OE'
and    wdd.released_status     = 'C'
and    wdd.inv_interfaced_flag in ('N' ,'P')
--and    oap.period_name = upper('&Period')
and    OAP.period_name = upper(:period)
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    wdd.organization_id = mtlp.organization_id
and    wdd.organization_id = oap.organization_id
and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
and    trunc(wts.actual_departure_date) between to_date(oap.period_start_date)
and    to_date(oap.schedule_close_date)
and    wdl.pick_up_stop_id     = wts.stop_id
group by mtlp.organization_code
ORDER BY mtlp.organization_code

7.PENDING RECEIVING (Interface Errors)
  ====================================

select mtlp.organization_code
       ,count(*)
FROM   apps.rcv_transactions_interface rcv,
       apps.mtl_parameters                 mtlp,
       apps.org_acct_periods oap
WHERE--  oap.period_name = upper('&Period')
oap.period_name = upper(:period)
and    rcv.to_organization_id = oap.organization_id
and    mtlp.organization_id = oap.organization_id
and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
and    trunc(transaction_date) < (trunc(to_date(oap.schedule_close_date))+1)
and destination_type_code = 'INVENTORY'
group by mtlp.organization_code
ORDER BY mtlp.organization_code

8. PENDING MATERIAL
   ================

select mtlp.organization_code
       ,count(*)
from   apps.mtl_transactions_interface     mti,
       apps.mtl_serial_numbers_interface   msni,
       apps.mtl_transaction_lots_interface mtli,
       apps.mtl_parameters                 mtlp,
       apps.org_acct_periods oap
where  oap.period_name = upper(:period)
and    mti.organization_id = oap.organization_id
and    mtlp.organization_id = oap.organization_id
and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
AND    (mti.ACCT_PERIOD_ID  = oap.acct_period_id
        OR (mti.ACCT_PERIOD_ID IS NULL
       AND TRUNC(TRANSACTION_DATE) < (trunc(to_date(oap.schedule_close_date))+1)))
AND    mti.PROCESS_FLAG <> 9
AND    (mtli.transaction_interface_id (+)     = mti.transaction_interface_id
        AND msni.transaction_interface_id (+) = mti.transaction_interface_id)
group  BY mtlp.organization_code
ORDER BY mtlp.organization_code


9.PENDING SHOP FLOOR MOVE
  =======================

select mtlp.organization_code
       ,count(*)
from   apps.wip_move_txn_interface   wmti,
       apps.wip_txn_interface_errors wtie,
       apps.mtl_parameters                 mtlp,
       apps.org_acct_periods oap
where  --oap.period_name = upper('&Period')
oap.period_name = upper(:period)
and    wmti.organization_id = oap.organization_id
and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
and    mtlp.organization_id = wmti.organization_id
AND   (wmti.ACCT_PERIOD_ID  = oap.acct_period_id
        OR (wmti.ACCT_PERIOD_ID IS NULL
AND TRUNC(TRANSACTION_DATE) < (TRUNC(to_date(oap.schedule_close_date))+ 1)))
AND   wtie.transaction_id (+) = wmti.transaction_id
group  BY mtlp.organization_code
ORDER BY mtlp.organization_code


10. INCOMPLETE WORK ORDERS
    ======================
SELECT   mtlp.organization_code,count(*)
    FROM apps.wip_discrete_jobs wdj, apps.wip_entities we, apps.org_acct_periods oap,apps.mtl_parameters mtlp
   WHERE
     mtlp.organization_id = oap.organization_id
     and wdj.organization_id = oap.organization_id
     and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
     and oap.period_name = upper(:period)
     AND wdj.scheduled_completion_date < (trunc(to_date(oap.schedule_close_date))+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
group by mtlp.organization_code
ORDER BY mtlp.organization_code

11.FAILED CLOSE JOBS
   =================

SELECT   mtlp.organization_code,count(*) COUNT10
    FROM apps.wip_discrete_jobs wdj, apps.wip_entities we, apps.org_acct_periods oap,apps.mtl_parameters mtlp
   WHERE
         mtlp.organization_id = oap.organization_id
     and wdj.organization_id = oap.organization_id
    and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
     and oap.period_name = upper(:period)
     --AND wdj.scheduled_completion_date < (trunc(to_date(oap.schedule_close_date))+1)
     AND wdj.status_type = 15
     AND wdj.wip_entity_id = we.wip_entity_id
     AND wdj.organization_id = we.organization_id    
group by mtlp.organization_code
ORDER BY mtlp.organization_code

12.COMPLETE JOBS
   =============

SELECT   mtlp.organization_code,count(*) COUNT11
    FROM apps.wip_discrete_jobs wdj, apps.wip_entities we, apps.org_acct_periods oap,apps.mtl_parameters mtlp
   WHERE
         mtlp.organization_id = oap.organization_id
     and wdj.organization_id = oap.organization_id
    and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
     and oap.period_name = upper(:period)
     AND wdj.scheduled_completion_date < (trunc(to_date(oap.schedule_close_date))+1)
     AND wdj.status_type = 4
     AND wdj.wip_entity_id = we.wip_entity_id
     AND wdj.organization_id = we.organization_id    
group by mtlp.organization_code
ORDER BY mtlp.organization_code

13. RECEIPT PENDING
    ===============
 
SELECT mtlp.organization_code,  count(*) count12
   FROM apps.rcv_transactions rt,
              apps.org_acct_periods oap,
              apps.mtl_parameters mtlp,
              po.rcv_shipment_headers rsh,
              po.rcv_shipment_lines rsl
  WHERE
              rt.TRANSACTION_ID IN
                   (SELECT   rs.RCV_TRANSACTION_ID
                       FROM   apps.rcv_supply rs
                     WHERE   rs.QUANTITY > 0
                           AND rs.to_organization_id =rt.organization_id)
           AND TRUNC (rsh.creation_date) < (trunc(to_date(oap.schedule_close_date))+1)
           AND rt.SHIPMENT_LINE_ID = rsl.shipment_line_id
           AND rsh.shipment_header_id = rsl.shipment_header_id
           and mtlp.organization_id = oap.organization_id
          and    oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
           and rt.organization_id = oap.organization_id
           and oap.period_name = upper(:period)
           AND NVL (rsl.po_header_id, 0) = NVL (rt.po_header_id, 0)
           AND NVL (rsl.po_line_id, 0) = NVL (rt.po_line_id, 0)
           AND NVL (rsl.po_release_id, 0) = NVL (rt.po_release_id, 0)
           group by mtlp.organization_code
           order by mtlp.organization_code

14.PENDING CLOSE JOBS
   ==================
SELECT   mtlp.organization_code,count(*) COUNT14
    FROM apps.wip_discrete_jobs wdj, apps.wip_entities we, apps.org_acct_periods oap,apps.mtl_parameters mtlp
   WHERE
         mtlp.organization_id = oap.organization_id
     and wdj.organization_id = oap.organization_id
     and oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
     and oap.period_name = upper(:period)
     AND wdj.scheduled_completion_date < (trunc(to_date(oap.schedule_close_date))+1)
     AND wdj.status_type = 14
     AND wdj.wip_entity_id = we.wip_entity_id
     AND wdj.organization_id = we.organization_id    
group by mtlp.organization_code
ORDER BY mtlp.organization_code

15. RMA PENDING RECEIPTS
    ====================

SELECT Q1.organization_code,COUNT(*) count FROM
(SELECT    oooha.sold_to_org_id party, ooola.line_id,ooola.ship_from_org_id,
         mtlp.organization_code,
         ooola.header_id, arc.customer_name, oooha.order_number rma_no,
         oooha.ordered_date rma_dt,
          oooha.cust_po_number,
         ooola.ordered_item_id,
          ooola.ordered_quantity,
         ooola.order_quantity_uom,
                           (  TO_DATE (SYSDATE, 'DD-MM-RRRR')
          - TO_DATE (ooola.creation_date, 'DD-MM-RRRR')
         ) no_of_days,
         oooha.order_type_id, ooola.flow_status_code,
         ooola.line_category_code
    FROM ont.oe_order_lines_all ooola,
         ont.oe_order_headers_all oooha,        
         ar_customers arc,
         hz_cust_accounts hca,                --Changed by Saugata on 1/7/2010
       --  inv.mtl_system_items_b imsi,        
         ont.oe_transaction_types_tl oottt,
         apps.org_acct_periods oap,apps.mtl_parameters mtlp
   WHERE --oooha.org_id = :p_org_id
         oooha.ship_from_org_id = nvl(:p_organization_id,oooha.ship_from_org_id)
     AND ooola.ship_from_org_id = oooha.ship_from_org_id
     AND oooha.header_id = ooola.header_id
   --  AND ooola.header_id not in (select prsl.oe_order_header_id from po.rcv_shipment_headers prsh,
     --                             po.rcv_shipment_lines prsl
       --                           where ooola.header_id = prsl.oe_order_header_id
         --                         AND  prsl.shipment_header_id = prsh.shipment_header_id)
     --AND ooola.ordered_item_id = imsi.inventory_item_id
     --AND imsi.organization_id = oooha.ship_from_org_id
     AND oooha.sold_to_org_id = arc.customer_id
     AND hca.cust_account_id = arc.customer_id  --Added by Saugata on 1/7/2010
     --AND hca.party_id BETWEEN NVL (:p_start_party, hca.party_id)
       --                   AND NVL (:p_end_party, hca.party_id)
     AND oooha.order_type_id = oottt.transaction_type_id
     --AND oottt.NAME = NVL (:p_order_type, oottt.NAME)
     AND (  TO_DATE (SYSDATE, 'DD-MM-RRRR') - TO_DATE (ooola.creation_date, 'DD-MM-RRRR')
         ) > 0 -- NVL (:p_ageing_days, 0)
     and mtlp.organization_id = oap.organization_id
     and ooola.ship_from_org_id = oap.organization_id
     and oap.organization_id  = nvl(:p_organization_id,oap.organization_id)
     and oap.period_name = upper(:period)
     AND ooola.creation_date < (trunc(to_date(oap.schedule_close_date))+1)  
     AND ooola.line_category_code = 'RETURN'                    
     AND ooola.flow_status_code IN ('ENTERED', 'AWAITING_RETURN')) Q1
     WHERE NO_OF_DAYS > 45
     GROUP BY organization_code

16.PENDING RECEIVING ACCOUNTING
   ============================

select ood.organization_code,COUNT(*) mcount16
                            --ood.organization_code UNIT, rsh.receipt_num RECEIPT_NUMBER, trunc(rsh.creation_date) receipt_date, trunc(jrt.transaction_date) transaction_date, pv.vendor_name SUPPLIER_NAME, pvsa.vendor_site_code SUPPLIER_SITE,
                            --jrt.organization_id, jrt.inventory_item_id,
                            --msi.segment1 item_code, msi.description, jrt.quantity
                    from apps.jai_rcv_transactions jrt,
                        apps.org_organization_definitions ood,
                        apps.rcv_shipment_headers rsh,
                        apps.rcv_shipment_lines rsl,
                        apps.po_vendors pv,
                        apps.po_vendor_sites_all pvsa,
                        apps.mtl_system_items_b msi
                    where 1=1
                      and msi.organization_id=rsh.ship_to_org_id
                      and msi.inventory_item_id=rsl.item_id
                      and jrt.shipment_line_id=rsl.shipment_line_id
                      and jrt.shipment_line_id = (select distinct shipment_line_id
                                from apps.jai_rcv_line_taxes jrlt
                               where jrlt.shipment_line_id=jrt.shipment_line_id)
                      and rsh.vendor_site_id=pvsa.vendor_site_id
                      and rsh.vendor_id=pv.vendor_id
                      and jrt.shipment_header_id=rsh.shipment_header_id
                      and jrt.organization_id=ood.organization_id
                      AND ood.organization_id = NVL(:P_ORGANIZATION_ID,ood.organization_id)
                    --and jrt.organization_id=89
                      and jrt.transaction_date>='01-APR-2014'
                      and jrt.transaction_type IN ('DELIVER' ,'RECEIVE')
                      and  jrt.process_message is null
                    GROUP BY organization_code;