XSECH : PO Vendor Service Performance Analysis

with rec as(
select
/*+ materialize leading(x) push_pred(rct2)*/
x.organization_name organization,
x.organization_id organization_id,
to_char(rct2.trx_period,'YYYY') trx_year,
to_number(to_char(rct2.trx_period,'MM')) trx_month,
rct2.trx_period,
x.vendor vendor_name,
x.item_code,
x.item_description,
rct2.quantity_received_total,
rct2.quantity_received_early,
rct2.quantity_received_late,
rct2.quantity_received_on_time ,
rct2.quantity_received_very_late
from
(
select /*+ leading(org)*/
org.*,
papf.full_name buyer,
msi.segment1 item_code,
pol.item_description item_description,
poh.segment1 po_number,
pll.line_location_id parent_line_location_id,
poh.vendor_id vendor_id1,
promised_date_ promised_date,
cutoff_date_ cutoff_date,
pll.need_by_date,
pll.ship_to_location_id ship_to_location,
pll.ordered_
from
(
select
nvl(pll.promised_date,pll.need_by_date) - nvl(pll.days_early_receipt_allowed,0) promised_date_,
nvl(pll.promised_date,pll.need_by_date) + nvl(pll.days_late_receipt_allowed,0) cutoff_date_,
round ( ( pll.quantity - nvl ( pll.quantity_cancelled , 0 ) ) , :p_qty_precision ) ordered_,
pll.*
from
po_line_locations_all pll) pll,
(
select distinct
pov.vendor_name vendor,
pov.segment1 vendor_number,
pov.vendor_id parent_vendor_id,
org.name organization_name,
org.organization_id
from
po_vendors pov,
per_all_people_f papf,
po_lines_all pol,
po_headers_all poh,
hr_operating_units org
where
poh.po_header_id=pol.po_header_id and
poh.org_id=org.organization_id and
poh.org_id=pol.org_id and
poh.vendor_id=pov.vendor_id and
1=1 and
poh.agent_id=papf.person_id and
papf.business_group_id=(select nvl(max(fsp.business_group_id),0) from financials_system_params_all fsp where fsp.inventory_organization_id=org.organization_id ) and
trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and
decode(hr_security.view_all ,'Y' , 'TRUE', hr_security.show_record('PER_ALL_PEOPLE_F',papf.person_id, papf.person_type_id,papf.employee_number,papf.applicant_number,papf.npw_number ))='TRUE' and
decode(hr_general.get_xbg_profile,'Y', papf.business_group_id ,hr_general.get_business_group_id)=papf.business_group_id and
exists (select
'item exists'
from
po_line_locations_all pll,
mtl_system_items msi,
mtl_categories mca
where
pll.po_line_id=pol.po_line_id and
pol.item_id=msi.inventory_item_id(+) and
pol.category_id=mca.category_id and
nvl(msi.organization_id,org.organization_id)=org.organization_id and
pll.org_id=org.organization_id and
nvl(pll.cancel_flag,'N')='N' and
exists (
select 'receiving transaction for this PO'
from
rcv_transactions rct
where
rct.transaction_type='RECEIVE' and
rct.po_header_id=poh.po_header_id and
2=2)
)
) org,
po_lines_all pol,
po_headers_all poh,
po_vendors pov,
mtl_system_items msi,
mtl_categories mca,
per_all_people_f papf,
(select
row_number() over (partition by rct.po_line_location_id order by 1) rk,
rct.po_line_location_id line_location_id1,
rct.primary_unit_of_measure unit,
decode(rct.source_doc_quantity,0,0,rct.primary_quantity/rct.source_doc_quantity) conversion_rate
from
rcv_transactions rct
where
rct.transaction_type='RECEIVE' ) rct
where
pll.po_line_id=pol.po_line_id and
poh.org_id=org.organization_id and
poh.org_id=pol.org_id and
pll.org_id=pol.org_id and
pol.po_header_id=poh.po_header_id and
pol.item_id=msi.inventory_item_id(+) and
pol.category_id=mca.category_id and
nvl(msi.organization_id,org.organization_id)=org.organization_id and
poh.vendor_id=pov.vendor_id and
exists (select 'receiving transaction for this PO' from rcv_transactions rct where rct.transaction_type='RECEIVE' and rct.po_header_id=poh.po_header_id and rct.po_line_id=pol.po_line_id and rct.po_line_location_id=pll.line_location_id) and
poh.vendor_id=org.parent_vendor_id and
pll.shipment_type='STANDARD' and
poh.type_lookup_code='STANDARD' and
trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and
decode(hr_general.get_xbg_profile,'Y', papf.business_group_id ,hr_general.get_business_group_id)=papf.business_group_id and
papf.person_id=poh.agent_id and
pol.order_type_lookup_code in ('QUANTITY', 'AMOUNT') and
rct.line_location_id1=pll.line_location_id   and
rct.rk=1
union all
select /*+ leading(org)*/
org.*,
papf.full_name buyer,
msi.segment1 item_code,
pol.item_description item_description,
poh.segment1 po_number,
pll.line_location_id parent_line_location_id,
poh.vendor_id vendor_id1,
promised_date_ promised_date,
cutoff_date_ cutoff_date,
pll.need_by_date,
pll.ship_to_location_id ship_to_location,
pll.ordered_
from
(
select
nvl(pll.promised_date,pll.need_by_date) - nvl(pll.days_early_receipt_allowed,0) promised_date_,
nvl(pll.promised_date,pll.need_by_date) + nvl(pll.days_late_receipt_allowed,0) cutoff_date_,
round ( ( pll.quantity - nvl ( pll.quantity_cancelled , 0 ) ) , :p_qty_precision ) ordered_,
pll.*
from
po_line_locations_all pll) pll,
(
select distinct
pov.vendor_name vendor,
pov.segment1 vendor_number,
pov.vendor_id parent_vendor_id,
org.name organization_name,
org.organization_id
from
po_vendors pov,
per_all_people_f papf,
po_lines_all pol,
po_headers_all poh,
hr_operating_units org
where
poh.po_header_id=pol.po_header_id and
poh.org_id=org.organization_id and
poh.org_id=pol.org_id and
poh.vendor_id=pov.vendor_id and
1=1 and
poh.agent_id=papf.person_id and
papf.business_group_id=(select nvl(max(fsp.business_group_id),0) from financials_system_params_all fsp where fsp.inventory_organization_id=org.organization_id ) and
trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and
decode(hr_security.view_all ,'Y' , 'TRUE', hr_security.show_record('PER_ALL_PEOPLE_F',papf.person_id, papf.person_type_id,papf.employee_number,papf.applicant_number,papf.npw_number ))='TRUE' and
decode(hr_general.get_xbg_profile,'Y', papf.business_group_id ,hr_general.get_business_group_id)=papf.business_group_id and
exists (select
'item exists'
from
po_line_locations_all pll,
mtl_system_items msi,
mtl_categories mca
where
pll.po_line_id=pol.po_line_id and
pol.item_id=msi.inventory_item_id(+) and
pol.category_id=mca.category_id and
nvl(msi.organization_id,org.organization_id)=org.organization_id and
pll.org_id=org.organization_id and
nvl(pll.cancel_flag,'N')='N' and
exists (
select
'receiving transaction for this PO'
from
rcv_transactions rct
where
rct.transaction_type='RECEIVE' and
rct.po_header_id = poh.po_header_id and
2=2)
)
) org,
po_lines_all pol,
po_headers_all poh,
po_vendors pov,
po_releases por,
mtl_system_items msi,
mtl_categories mca,
per_all_people_f papf,
(select
row_number() over (partition by rct.po_line_location_id order by 1) rk,
rct.po_line_location_id line_location_id1,
rct.primary_unit_of_measure unit,
decode(rct.source_doc_quantity,0,0,rct.primary_quantity/rct.source_doc_quantity) conversion_rate
from
rcv_transactions rct
where
rct.transaction_type='RECEIVE' ) rct
where
pll.po_line_id=pol.po_line_id and
pol.po_header_id=poh.po_header_id and
por.po_header_id=poh.po_header_id and
pll.po_release_id=por.po_release_id and
pol.item_id=msi.inventory_item_id(+) and
pol.category_id=mca.category_id and
nvl(msi.organization_id,org.organization_id)=org.organization_id and
poh.vendor_id=pov.vendor_id and
exists (select
'receiving transaction for this PO'
from
rcv_transactions rct
where
rct.transaction_type='RECEIVE' and
rct.po_header_id=poh.po_header_id and
rct.po_line_id=pol.po_line_id and
rct.po_line_location_id=pll.line_location_id
) and
poh.vendor_id=org.parent_vendor_id and
poh.org_id=org.organization_id and
poh.org_id=pol.org_id and
poh.org_id=por.org_id and
pll.org_id=pol.org_id and
pll.shipment_type in ('BLANKET','SCHEDULED') and
poh.type_lookup_code in ('BLANKET','PLANNED') and
trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and
decode(hr_general.get_xbg_profile,'Y', papf.business_group_id, hr_general.get_business_group_id)=papf.business_group_id and
papf.person_id=poh.agent_id and
pol.order_type_lookup_code in ('QUANTITY', 'AMOUNT')  and
rct.line_location_id1=pll.line_location_id   and
rct.rk=1
) x,
(
select  /*+ leading(rct)*/
trunc(rct.transaction_date,'MM') trx_period,
rct.po_line_location_id line_location_id,
sum(round(nvl(rsl.quantity_received,0),:p_qty_precision)*decode(rct.primary_unit_of_measure,rct.unit_of_measure,1,rct.primary_quantity/rct.quantity)) quantity_received_total,
sum(decode(sign(trunc(pll.promised_date_)-trunc(rct.transaction_date)),+1, 1, 0) *
round(nvl(rsl.quantity_received,0),:p_qty_precision)*decode(rct.primary_unit_of_measure,rct.unit_of_measure,1,rct.primary_quantity/rct.quantity)) quantity_received_early,
sum(case when trunc(pll.cutoff_date_)-trunc(rct.transaction_date) between -1 and -4 then 1 else 0 end *
round(nvl(rsl.quantity_received,0),:p_qty_precision)*decode(rct.primary_unit_of_measure,rct.unit_of_measure,1,rct.primary_quantity/rct.quantity)) quantity_received_late,
sum(case when trunc(pll.cutoff_date_)-trunc(rct.transaction_date)<=-5 then 1 else 0 end *
round(nvl(rsl.quantity_received,0),:p_qty_precision)*decode(rct.primary_unit_of_measure,rct.unit_of_measure,1,rct.primary_quantity/rct.quantity)) quantity_received_very_late,
(sum(round(nvl(rsl.quantity_received , 0 ) , :p_qty_precision ) * decode ( rct.primary_unit_of_measure , rct.unit_of_measure , 1 , rct.primary_quantity / rct.quantity ) ) -
sum(decode(sign(trunc(pll.promised_date_)-trunc(rct.transaction_date)),+1, 1, 0) * round(nvl(rsl.quantity_received,0),:p_qty_precision)*decode(rct.primary_unit_of_measure,rct.unit_of_measure,1,rct.primary_quantity/rct.quantity)) -
sum(case when trunc(pll.cutoff_date_)-trunc(rct.transaction_date) between -1 and -4 then 1 else 0 end * round(nvl(rsl.quantity_received,0),:p_qty_precision)*decode(rct.primary_unit_of_measure,rct.unit_of_measure,1,rct.primary_quantity/rct.quantity)) -
sum(case when trunc(pll.cutoff_date_)-trunc(rct.transaction_date)<=-5 then 1 else 0 end * round(nvl(rsl.quantity_received,0),:p_qty_precision)*decode(rct.primary_unit_of_measure,rct.unit_of_measure,1,rct.primary_quantity/rct.quantity))
) quantity_received_on_time
from
(
select
nvl(pll.promised_date,pll.need_by_date) - nvl(pll.days_early_receipt_allowed,0) promised_date_,
nvl(pll.promised_date,pll.need_by_date) + nvl(pll.days_late_receipt_allowed,0) cutoff_date_,
pll.*
from
po_line_locations_all pll,
hr_operating_units org
where
pll.org_id=org.organization_id and
1=1 ) pll,
(select * from rcv_transactions rct
where
rct.transaction_type='RECEIVE' and
2=2 ) rct,
rcv_shipment_lines rsl
where
rct.po_line_location_id=pll.line_location_id and
rct.shipment_line_id=rsl.shipment_line_id 
group by trunc(rct.transaction_date,'MM'),rct.po_line_location_id
) rct2
where
x.parent_line_location_id=rct2.line_location_id
)
select 
rec.organization_id,
rec.trx_year,
rec.trx_month,
rec.trx_period,
null vendor_name,
null item_code,
sum(rec.quantity_received_late) late_cnt,
sum(rec.quantity_received_late)/sum(quantity_received_total) pct_late_cnt, 
sum(rec.quantity_received_very_late) very_late_cnt,
sum(rec.quantity_received_very_late)/sum(quantity_received_total) pct_very_late_cnt,
sum(rec.quantity_received_on_time) on_time_cnt,
sum(rec.quantity_received_on_time) /sum(quantity_received_total) pct_on_time_cnt,
sum(rec.quantity_received_early) early_cnt,
sum(rec.quantity_received_early)/sum(quantity_received_total) pct_early_cnt,
sum(quantity_received_total) total,
-1 rank_vendor,
-1 rank_item,
0.95 pct_target,
(sum(quantity_received_total)-sum(rec.quantity_received_late)-sum(rec.quantity_received_very_late))/sum(quantity_received_total) sum_late
from rec
group by
rec.organization_id,
rec.trx_year,
rec.trx_month,
rec.trx_period
union all
select
rec.organization_id,
rec.trx_year,
rec.trx_month,
rec.trx_period,
rec.vendor_name  vendor_name,
null item_code,
sum(rec.quantity_received_late) late_cnt,
sum(rec.quantity_received_late)/sum(quantity_received_total) pct_late_cnt,
sum(rec.quantity_received_very_late) very_late_cnt,
sum(rec.quantity_received_very_late)/sum(quantity_received_total) pct_very_late_cnt,
sum(rec.quantity_received_on_time) on_time_cnt,
sum(rec.quantity_received_on_time) /sum(quantity_received_total) pct_on_time_cnt,
sum(rec.quantity_received_early) early_cnt,
sum(rec.quantity_received_early)/sum(quantity_received_total) pct_early_cnt,
sum(quantity_received_total) total,
rec1.rk rank_vendor,
-1 rank_item,
0.95 pct_target,
(sum(quantity_received_total)-sum(rec.quantity_received_late)-sum(rec.quantity_received_very_late))/sum(quantity_received_total) sum_late
from
rec,
(select rownum rk,rec1.* from
(select
vendor_name,
trx_year,
sum(quantity_received_late)+sum(quantity_received_very_late) sum_late_item
from rec
group by vendor_name, trx_year
order by sum_late_item desc
) rec1 ) rec1
where
rec1.rk<=10 and
rec1.vendor_name=rec.vendor_name
group by
rec.organization_id,
rec.trx_year,
rec.trx_month,
rec.trx_period,
rec.vendor_name,
rec1.rk
union all
select
rec.organization_id,
rec.trx_year,
rec.trx_month,
rec.trx_period,
null  vendor_name,
rec.item_description item_code,
sum(rec.quantity_received_late) late_cnt,
sum(rec.quantity_received_late)/sum(quantity_received_total) pct_late_cnt,
sum(rec.quantity_received_very_late) very_late_cnt,
sum(rec.quantity_received_very_late)/sum(quantity_received_total) pct_very_late_cnt,
sum(rec.quantity_received_on_time) on_time_cnt,
sum(rec.quantity_received_on_time) /sum(quantity_received_total) pct_on_time_cnt,
sum(rec.quantity_received_early) early_cnt,
sum(rec.quantity_received_early)/sum(quantity_received_total) pct_early_cnt,
sum(quantity_received_total) total,
-1 rank_vendor,
rec1.rk rank_item,
0.95 pct_target,
(sum(quantity_received_total)-sum(rec.quantity_received_late)-sum(rec.quantity_received_very_late))/sum(quantity_received_total) sum_late
from
rec,
(select rownum rk,rec1.* from
(select
item_description,
trx_year,
sum(quantity_received_late)+sum(quantity_received_very_late) sum_late_item
from rec
group by item_description, trx_year
order by sum_late_item desc
) rec1 ) rec1
where
rec1.rk<=10 and
rec1.item_description=rec.item_description
group by
rec.organization_id,
rec.trx_year,
rec.trx_month,
rec.trx_period,
rec.item_description,
rec1.rk
order by
rank_item,
rank_vendor,
trx_month