INV Period closing Queries R12




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;

Post a Comment