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;
No comments:
Post a Comment