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