-
Notifications
You must be signed in to change notification settings - Fork 68
Expand file tree
/
Copy pathPO Vendor Service Performance Analysis.sql
More file actions
183 lines (182 loc) · 11.7 KB
/
Copy pathPO Vendor Service Performance Analysis.sql
File metadata and controls
183 lines (182 loc) · 11.7 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
/*************************************************************************/
/* */
/* (c) 2010-2024 Enginatics GmbH */
/* www.enginatics.com */
/* */
/*************************************************************************/
-- Report Name: PO Vendor Service Performance Analysis
-- Description: Imported from BI Publisher
Application: Purchasing
Source: Vendor Service Performance Analysis Report (XML)
Short Name: POXSERPR_XML
DB package: PO_POXSERPR_XMLP_PKG
-- Excel Examle Output: https://www.enginatics.com/example/po-vendor-service-performance-analysis/
-- Library Link: https://www.enginatics.com/reports/po-vendor-service-performance-analysis/
-- Run Report: https://demo.enginatics.com/
select
gsb.name company,
hou.name operating_unit,
pov.vendor_name,
pov.segment1 vendor_number,
msiv.concatenated_segments item,
pol.item_description,
case when pha.type_lookup_code in ('BLANKET','PLANNED') then pha.segment1||'-'||por.release_num else pha.segment1 end po_number,
pll.shipment_num,
nvl(pll.promised_date,pll.need_by_date) promised_date,
nvl(pll.promised_date,pll.need_by_date)-nvl(pll.days_early_receipt_allowed,0) early_receipt_date,
nvl(pll.promised_date,pll.need_by_date)+nvl(pll.days_late_receipt_allowed,0) cutoff_date,
hla.location_code ship_to_location,
round(pll.quantity-nvl(pll.quantity_cancelled,0),:p_qty_precision) quantity_ordered,
round(nvl(pll.quantity_received,0),:p_qty_precision) quantity_received,
round(pll.quantity_rejected,:p_qty_precision) quantity_rejected,
round(pll.quantity-nvl(pll.quantity_cancelled,0)-nvl(pll.quantity_received,0),:p_qty_precision) quantity_open,
case when nvl(pll.quantity_received,0)<pll.quantity-nvl(pll.quantity_cancelled,0)
and nvl(pll.promised_date,pll.need_by_date)+nvl(pll.days_late_receipt_allowed,0)<trunc(sysdate)
then round(pll.quantity-nvl(pll.quantity_cancelled,0)-nvl(pll.quantity_received,0),:p_qty_precision)
end quantity_past_due,
(
select round(sum(decode(sign(trunc(nvl(pll.promised_date,pll.need_by_date)-nvl(pll.days_early_receipt_allowed,0))-trunc(rct.transaction_date)),1,1,0)*
nvl(rsl.quantity_received,0)*decode(rct.primary_unit_of_measure,rct.unit_of_measure,1,rct.primary_quantity/rct.quantity)),:p_qty_precision)
from rcv_transactions rct, rcv_shipment_lines rsl
where rct.po_line_location_id=pll.line_location_id and rct.transaction_type='RECEIVE' and rct.shipment_line_id=rsl.shipment_line_id
) quantity_early,
(
select round(sum(decode(sign(trunc(nvl(pll.promised_date,pll.need_by_date)+nvl(pll.days_late_receipt_allowed,0))-trunc(rct.transaction_date)),-1,1,0)*
nvl(rsl.quantity_received,0)*decode(rct.primary_unit_of_measure,rct.unit_of_measure,1,rct.primary_quantity/rct.quantity)),:p_qty_precision)
from rcv_transactions rct, rcv_shipment_lines rsl
where rct.po_line_location_id=pll.line_location_id and rct.transaction_type='RECEIVE' and rct.shipment_line_id=rsl.shipment_line_id
) quantity_late,
(
select round(sum(
case when trunc(rct.transaction_date) between trunc(nvl(pll.promised_date,pll.need_by_date)-nvl(pll.days_early_receipt_allowed,0))
and trunc(nvl(pll.promised_date,pll.need_by_date)+nvl(pll.days_late_receipt_allowed,0))
then nvl(rsl.quantity_received,0)*decode(rct.primary_unit_of_measure,rct.unit_of_measure,1,rct.primary_quantity/rct.quantity)
end),:p_qty_precision)
from rcv_transactions rct, rcv_shipment_lines rsl
where rct.po_line_location_id=pll.line_location_id and rct.transaction_type='RECEIVE' and rct.shipment_line_id=rsl.shipment_line_id
) quantity_on_time,
(
select round(sum(decode(pll.ship_to_location_id,rct.location_id,0,1)*
nvl(rsl.quantity_received,0)*decode(rct.primary_unit_of_measure,rct.unit_of_measure,1,rct.primary_quantity/rct.quantity)),:p_qty_precision)
from rcv_transactions rct, rcv_shipment_lines rsl
where rct.po_line_location_id=pll.line_location_id and rct.transaction_type='RECEIVE' and rct.shipment_line_id=rsl.shipment_line_id
) quantity_wrong_location,
round(pll.quantity_rejected*100/nullif(pll.quantity-nvl(pll.quantity_cancelled,0),0),:p_qty_precision) pct_quantity_rejected,
case when nvl(pll.quantity_received,0)<pll.quantity-nvl(pll.quantity_cancelled,0)
and nvl(pll.promised_date,pll.need_by_date)+nvl(pll.days_late_receipt_allowed,0)<trunc(sysdate)
then round((pll.quantity-nvl(pll.quantity_cancelled,0)-nvl(pll.quantity_received,0))*100/nullif(pll.quantity-nvl(pll.quantity_cancelled,0),0),:p_qty_precision)
end pct_quantity_past_due,
(
select round(sum(
case when trunc(rct.transaction_date) between trunc(nvl(pll.promised_date,pll.need_by_date)-nvl(pll.days_early_receipt_allowed,0))
and trunc(nvl(pll.promised_date,pll.need_by_date)+nvl(pll.days_late_receipt_allowed,0))
then nvl(rsl.quantity_received,0)*decode(rct.primary_unit_of_measure,rct.unit_of_measure,1,rct.primary_quantity/rct.quantity)
end)*100/nullif(nvl(pll.quantity_received,0),0),:p_qty_precision)
from rcv_transactions rct, rcv_shipment_lines rsl
where rct.po_line_location_id=pll.line_location_id and rct.transaction_type='RECEIVE' and rct.shipment_line_id=rsl.shipment_line_id
) pct_quantity_on_time,
(
select round(sum(decode(sign(trunc(nvl(pll.promised_date,pll.need_by_date)+nvl(pll.days_late_receipt_allowed,0))-trunc(rct.transaction_date)),-1,1,0)*
nvl(rsl.quantity_received,0)*decode(rct.primary_unit_of_measure,rct.unit_of_measure,1,rct.primary_quantity/rct.quantity))*100/nullif(nvl(pll.quantity_received,0),0),:p_qty_precision)
from rcv_transactions rct, rcv_shipment_lines rsl
where rct.po_line_location_id=pll.line_location_id and rct.transaction_type='RECEIVE' and rct.shipment_line_id=rsl.shipment_line_id
) pct_quantity_late,
(
select round(sum(decode(sign(trunc(nvl(pll.promised_date,pll.need_by_date)-nvl(pll.days_early_receipt_allowed,0))-trunc(rct.transaction_date)),1,1,0)*
nvl(rsl.quantity_received,0)*decode(rct.primary_unit_of_measure,rct.unit_of_measure,1,rct.primary_quantity/rct.quantity))*100/nullif(nvl(pll.quantity_received,0),0),:p_qty_precision)
from rcv_transactions rct, rcv_shipment_lines rsl
where rct.po_line_location_id=pll.line_location_id and rct.transaction_type='RECEIVE' and rct.shipment_line_id=rsl.shipment_line_id
) pct_quantity_early,
(
select round(sum(decode(pll.ship_to_location_id,rct.location_id,0,1)*
nvl(rsl.quantity_received,0)*decode(rct.primary_unit_of_measure,rct.unit_of_measure,1,rct.primary_quantity/rct.quantity))*100/nullif(nvl(pll.quantity_received,0),0),:p_qty_precision)
from rcv_transactions rct, rcv_shipment_lines rsl
where rct.po_line_location_id=pll.line_location_id and rct.transaction_type='RECEIVE' and rct.shipment_line_id=rsl.shipment_line_id
) pct_quantity_wrong_location,
(
select round(sum(decode(sign(trunc(nvl(pll.promised_date,pll.need_by_date)-nvl(pll.days_early_receipt_allowed,0))-trunc(rct.transaction_date)),1,
trunc(nvl(pll.promised_date,pll.need_by_date)-nvl(pll.days_early_receipt_allowed,0))-trunc(rct.transaction_date),0)*
nvl(rsl.quantity_received,0)*decode(rct.primary_unit_of_measure,rct.unit_of_measure,1,rct.primary_quantity/rct.quantity)),:p_qty_precision)
from rcv_transactions rct, rcv_shipment_lines rsl
where rct.po_line_location_id=pll.line_location_id and rct.transaction_type='RECEIVE' and rct.shipment_line_id=rsl.shipment_line_id
) days_early,
(
select round(sum(decode(sign(trunc(nvl(pll.promised_date,pll.need_by_date)+nvl(pll.days_late_receipt_allowed,0))-trunc(rct.transaction_date)),-1,
trunc(rct.transaction_date)-trunc(nvl(pll.promised_date,pll.need_by_date)+nvl(pll.days_late_receipt_allowed,0)),0)*
nvl(rsl.quantity_received,0)*decode(rct.primary_unit_of_measure,rct.unit_of_measure,1,rct.primary_quantity/rct.quantity)),:p_qty_precision)
from rcv_transactions rct, rcv_shipment_lines rsl
where rct.po_line_location_id=pll.line_location_id and rct.transaction_type='RECEIVE' and rct.shipment_line_id=rsl.shipment_line_id
) days_late,
(
select round(sum(decode(sign(trunc(nvl(pll.promised_date,pll.need_by_date)-nvl(pll.days_early_receipt_allowed,0))-trunc(rct.transaction_date)),1,
trunc(nvl(pll.promised_date,pll.need_by_date)-nvl(pll.days_early_receipt_allowed,0))-trunc(rct.transaction_date),0)*
nvl(rsl.quantity_received,0)*decode(rct.primary_unit_of_measure,rct.unit_of_measure,1,rct.primary_quantity/rct.quantity)),:p_qty_precision)+
round(sum(decode(sign(trunc(nvl(pll.promised_date,pll.need_by_date)+nvl(pll.days_late_receipt_allowed,0))-trunc(rct.transaction_date)),-1,
trunc(rct.transaction_date)-trunc(nvl(pll.promised_date,pll.need_by_date)+nvl(pll.days_late_receipt_allowed,0)),0)*
nvl(rsl.quantity_received,0)*decode(rct.primary_unit_of_measure,rct.unit_of_measure,1,rct.primary_quantity/rct.quantity)),:p_qty_precision)
from rcv_transactions rct, rcv_shipment_lines rsl
where rct.po_line_location_id=pll.line_location_id and rct.transaction_type='RECEIVE' and rct.shipment_line_id=rsl.shipment_line_id
) days_total_late_or_early,
(
select rct.primary_unit_of_measure
from rcv_transactions rct
where rct.po_line_location_id=pll.line_location_id and rct.transaction_type='RECEIVE' and rownum=1
) primary_uom,
(
select decode(rct.primary_unit_of_measure,rct.unit_of_measure,1,rct.primary_quantity/rct.quantity)
from rcv_transactions rct
where rct.po_line_location_id=pll.line_location_id and rct.transaction_type='RECEIVE' and rownum=1
) conversion_rate,
papf.full_name buyer,
decode(pll.shipment_type,'STANDARD',pha.creation_date,por.creation_date) creation_date,
pll.unit_meas_lookup_code uom,
gsb.currency_code,
fspa.inventory_organization_id organization_id,
mck.concatenated_segments category,
pll.line_location_id,
pll.po_line_id,
pha.po_header_id
from
gl_sets_of_books gsb,
financials_system_params_all fspa,
hr_operating_units hou,
ap_suppliers pov,
po_headers_all pha,
po_releases_all por,
po_lines_all pol,
po_line_locations_all pll,
mtl_system_items_vl msiv,
mtl_categories_b_kfv mck,
per_all_people_f papf,
hr_locations_all hla
where
1=1 and
gsb.set_of_books_id=fspa.set_of_books_id and
fspa.org_id=hou.organization_id and
pha.org_id=hou.organization_id and
pha.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) and
pha.vendor_id=pov.vendor_id and
pha.po_header_id=pol.po_header_id and
pol.po_line_id=pll.po_line_id and
pll.po_release_id=por.po_release_id(+) and
pol.item_id=msiv.inventory_item_id(+) and
fspa.inventory_organization_id=msiv.organization_id(+) and
pol.category_id=mck.category_id(+) and
pha.agent_id=papf.person_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
pll.ship_to_location_id=hla.location_id(+) and
nvl(pll.cancel_flag,'N')='N' and
pll.shipment_type in ('STANDARD','BLANKET','SCHEDULED') and
pha.type_lookup_code in ('STANDARD','BLANKET','PLANNED') and
pol.order_type_lookup_code in ('QUANTITY','AMOUNT') and
(pll.promised_date is not null or pll.need_by_date is not null) and
exists (select null from rcv_transactions rct where rct.transaction_type='RECEIVE' and rct.po_header_id=pha.po_header_id and rct.po_line_id=pol.po_line_id and rct.po_line_location_id=pll.line_location_id)
order by
pov.vendor_name,
pov.segment1,
msiv.concatenated_segments,
pol.item_description,
po_number,
pll.shipment_num