[Home] [Help]
PACKAGE BODY: APPS.POA_EDW_SUP_PERF_F_SIZE
Source
1 PACKAGE BODY POA_EDW_SUP_PERF_F_SIZE AS
2 /*$Header: poaszspb.pls 120.0 2005/06/01 19:28:26 appldev noship $ */
3
4 PROCEDURE cnt_rows (p_from_date IN DATE,
5 p_to_date IN DATE,
6 p_num_rows OUT NOCOPY NUMBER) IS
7
8 BEGIN
9
10 -- dbms_output.enable(100000);
11
12 select count(*) into p_num_rows
13 from
14 po_line_locations_all pll,
15 po_lines_all pol,
16 po_headers_all poh
17 WHERE pll.approved_flag = 'Y'
18 AND pll.shipment_type in ('BLANKET', 'SCHEDULED', 'STANDARD')
19 AND pll.po_line_id = pol.po_line_id
20 AND pll.po_header_id = poh.po_header_id
21 and greatest(pol.last_update_date, poh.last_update_date,
22 pll.last_update_date)
23 between p_from_date and p_to_date;
24
25 -- dbms_output.put_line('The number of rows for supplier performance is: '
26 -- || to_char(p_num_rows));
27
28 EXCEPTION
29 WHEN OTHERS THEN p_num_rows := 0;
30 END;
31
32 -------------------------------------------------------
33
34 PROCEDURE est_row_len (p_from_date IN DATE,
35 p_to_date IN DATE,
36 p_avg_row_len OUT NOCOPY NUMBER)IS
37
38 x_date number := 7;
39 x_total number := 0;
40 x_constant number := 6;
41 x_float number := 11;
42 x_int number := 6;
43
44 x_SIC_CODE_FK NUMBER :=0;
45 x_UNSPSC_FK NUMBER :=0;
46 x_DUNS_FK NUMBER :=0;
47 x_EDW_UOM_FK NUMBER :=0;
48 x_EDW_BASE_UOM_FK NUMBER :=0;
49 x_SUPPLIER_SITE_FK NUMBER :=0;
50 x_INVOICE_DATE_FK NUMBER :=0;
51 x_LST_ACCPT_DATE_FK NUMBER :=0;
52 x_APPROVAL_DATE_FK NUMBER :=0;
53 x_PROMISED_DATE_FK NUMBER :=0;
54 x_NEED_BY_DATE_FK NUMBER :=0;
55 x_FIRST_REC_DATE_FK NUMBER :=0;
56 x_DATE_DIM_FK NUMBER :=0;
57 x_CREATION_DATE_FK NUMBER :=0;
58 x_SUPPLIER_ITEM_FK NUMBER :=0;
59 x_AP_TERMS_FK NUMBER :=0;
60 x_BUYER_FK NUMBER :=0;
61 x_SHIP_TO_ORG_FK NUMBER :=0;
62 x_PO_LINE_TYPE_FK NUMBER :=0;
63 x_PURCH_CLASS_FK NUMBER :=0;
64 x_PRICE_TYPE_FK NUMBER :=0;
65 x_CLOSED_CODE_FK NUMBER :=0;
66 x_ITEM_FK NUMBER :=0;
67 x_INSTANCE_FK NUMBER :=0;
68 x_SHIP_LOCATION_FK NUMBER :=0;
69 x_SUP_SITE_GEOG_FK NUMBER :=0;
70 x_TXN_CUR_CODE_FK NUMBER :=0;
71 x_TARGET_PRICE_T NUMBER :=0;
72 x_TARGET_PRICE_G NUMBER :=0;
73 x_SUP_PERF_PK NUMBER :=0;
74 x_RELEASE_NUM NUMBER :=0;
75 x_RCV_CLOSE_TOL NUMBER :=0;
76 x_QTY_SUBS_RECEIPT_B NUMBER :=0;
77 x_QTY_SHIPPED_B NUMBER :=0;
78 x_QTY_REJECTED_B NUMBER :=0;
79 x_QTY_RECEIVED_TOL NUMBER :=0;
80 x_QTY_RECEIVED_B NUMBER :=0;
81 x_QTY_PAST_DUE_B NUMBER :=0;
82 x_QTY_ORDERED_B NUMBER :=0;
83 x_QTY_ONTIME_ONDUE_B NUMBER :=0;
84 x_QTY_ONTIME_BEFDUE_B NUMBER :=0;
85 x_QTY_ONTIME_AFTDUE_B NUMBER :=0;
86 x_QTY_LATE_RECEIPT_B NUMBER :=0;
87 x_QTY_EARLY_RECEIPT_B NUMBER :=0;
88 x_QTY_DELIVERED_B NUMBER :=0;
89 x_QTY_CANCELLED_B NUMBER :=0;
90 x_QTY_ACCEPTED_B NUMBER :=0;
91 x_PRICE_T NUMBER :=0;
92 x_PRICE_G NUMBER :=0;
93 x_NUM_SUBS_RECEIPT NUMBER :=0;
94 x_NUM_RECEIPT_LINES NUMBER :=0;
95 x_NUM_ONTIME_ONDUE NUMBER :=0;
96 x_NUM_ONTIME_BEFDUE NUMBER :=0;
97 x_NUM_ONTIME_AFTDUE NUMBER :=0;
98 x_NUM_LATE_RECEIPT NUMBER :=0;
99 x_NUM_EARLY_RECEIPT NUMBER :=0;
100 x_NUM_DAYS_TO_INVOICE NUMBER :=0;
101 x_MARKET_PRICE_T NUMBER :=0;
102 x_MARKET_PRICE_G NUMBER :=0;
103 x_LIST_PRICE_T NUMBER :=0;
104 x_LIST_PRICE_G NUMBER :=0;
105 x_LAST_UPDATE_DATE NUMBER :=0;
106 x_IPV_T NUMBER :=0;
107 x_IPV_G NUMBER :=0;
108 x_DAYS_LATE_REC NUMBER :=0;
109 x_DAYS_EARLY_REC NUMBER :=0;
110 x_CREATION_DATE NUMBER :=0;
111 x_AMT_PURCHASED_T NUMBER :=0;
112 x_AMT_PURCHASED_G NUMBER :=0;
113 x_PO_NUMBER NUMBER :=0;
114 x_CONTRACT_NUM NUMBER :=0;
115
116
117 x_category_id NUMBER :=0;
118 x_item_revision NUMBER :=0;
119 x_item_description NUMBER :=0;
120 x_item_id NUMBER :=0;
121 x_org_id NUMBER :=0;
122
123
124 CURSOR c_1 IS
125 SELECT avg(nvl(vsize(vendor_product_num), 0)),
126 avg(nvl(vsize(item_id), 0)),
127 avg(nvl(vsize(ITEM_REVISION), 0)),
128 avg(nvl(vsize(item_description), 0)),
129 avg(nvl(vsize(category_id), 0)),
130 avg(nvl(vsize(price_type_lookup_code), 0)),
131 avg(nvl(vsize(contract_num), 0))
132 from po_lines_all
133 where last_update_date between
134 p_from_date and p_to_date;
135
136 CURSOR c_2 IS
137 SELECT avg(nvl(vsize(line_location_id), 0)),
138 avg(nvl(vsize(ship_to_organization_id), 0)),
139 avg(nvl(vsize(ship_to_location_id), 0)),
140 avg(nvl(vsize(closed_code), 0)),
141 avg(nvl(vsize(receive_close_tolerance), 0))
142 from po_line_locations_all
143 where last_update_date between
144 p_from_date and p_to_date;
145
146 CURSOR c_3 IS
147 SELECT avg(nvl(vsize(agent_id), 0)),
148 avg(nvl(vsize(vendor_site_id), 0)),
149 avg(nvl(vsize(org_id), 0)),
150 avg(nvl(vsize(terms_id), 0)),
151 avg(nvl(vsize(currency_code), 0)),
152 avg(nvl(vsize(segment1), 0))
153 from po_headers_all
154 where last_update_date between
155 p_from_date and p_to_date;
156
157 CURSOR c_4 IS
158 SELECT avg(nvl(vsize(uom_code), 0))
159 from mtl_units_of_measure
160 where last_update_date between
161 p_from_date and p_to_date;
162
163 CURSOR c_5 IS
164 SELECT avg(nvl(vsize(line_type), 0))
165 from po_line_types
166 where last_update_date between
167 p_from_date and p_to_date;
168
169 CURSOR c_6 IS
170 SELECT avg(nvl(vsize(release_num), 0))
171 from po_releases_all
172 where last_update_date between
173 p_from_date and p_to_date;
174
175 CURSOR c_7 IS
176 SELECT avg(nvl(vsize(inventory_organization_id), 0))
177 from FINANCIALS_SYSTEM_PARAMS_ALL
178 where last_update_date between
179 p_from_date and p_to_date;
180
181 CURSOR c_8 IS
182 SELECT avg(nvl(vsize(vendor_name), 0))
183 from PO_VENDORS
184 where last_update_date between
185 p_from_date and p_to_date;
186
187
188 BEGIN
189
190 -- dbms_output.enable(100000);
191
192 -- all date FKs
193
194 x_INVOICE_DATE_FK := x_date;
195 x_LST_ACCPT_DATE_FK := x_date;
196 x_APPROVAL_DATE_FK := x_date;
197 x_PROMISED_DATE_FK := x_date;
198 x_NEED_BY_DATE_FK := x_date;
199 x_FIRST_REC_DATE_FK := x_date;
200 x_DATE_DIM_FK := x_date;
201 x_CREATION_DATE_FK := x_date;
202
203 x_total := 3 + x_total
204 + ceil (x_INVOICE_DATE_FK + 1)
205 + ceil (x_LST_ACCPT_DATE_FK + 1)
206 + ceil (x_APPROVAL_DATE_FK + 1)
207 + ceil (x_PROMISED_DATE_FK + 1)
208 + ceil (x_NEED_BY_DATE_FK + 1)
209 + ceil (x_FIRST_REC_DATE_FK + 1)
210 + ceil (x_DATE_DIM_FK + 1)
211 + ceil (x_CREATION_DATE_FK + 1);
212
213 -- all calculated numbers
214
215 x_TARGET_PRICE_T := x_float;
216 x_TARGET_PRICE_G := x_float;
217 x_PRICE_G := x_float;
218 x_PRICE_T := x_float;
219 x_MARKET_PRICE_G := x_float;
220 x_MARKET_PRICE_T := x_float;
221 x_LIST_PRICE_T := x_float;
222 x_LIST_PRICE_G := x_float;
223 x_QTY_SUBS_RECEIPT_B := x_float;
224 x_QTY_SHIPPED_B := x_float;
225 x_QTY_REJECTED_B := x_float;
226 x_QTY_RECEIVED_TOL := x_float;
227 x_QTY_RECEIVED_B := x_float;
228 x_QTY_PAST_DUE_B := x_float;
229 x_QTY_ORDERED_B := x_float;
230 x_QTY_ONTIME_ONDUE_B := x_float;
231 x_QTY_ONTIME_BEFDUE_B := x_float;
232 x_QTY_ONTIME_AFTDUE_B := x_float;
233 x_QTY_LATE_RECEIPT_B := x_float;
234 x_QTY_EARLY_RECEIPT_B := x_float;
235 x_QTY_DELIVERED_B := x_float;
236 x_QTY_CANCELLED_B := x_float;
237 x_QTY_ACCEPTED_B := x_float;
238 x_AMT_PURCHASED_T := x_float;
239 x_AMT_PURCHASED_G := x_float;
240 x_IPV_T := x_float;
241 x_IPV_G := x_float;
242 x_DAYS_LATE_REC := x_float;
243 x_DAYS_EARLY_REC := x_float;
244 x_NUM_SUBS_RECEIPT := x_float;
245 x_NUM_RECEIPT_LINES := x_float;
246 x_NUM_ONTIME_ONDUE := x_float;
247 x_NUM_ONTIME_BEFDUE := x_float;
248 x_NUM_ONTIME_AFTDUE := x_float;
249 x_NUM_LATE_RECEIPT := x_float;
250 x_NUM_EARLY_RECEIPT := x_float;
251 x_NUM_DAYS_TO_INVOICE := x_float;
252
253 x_total := x_total
254 + ceil (x_TARGET_PRICE_T + 1)
255 + ceil (x_TARGET_PRICE_G + 1)
256 + ceil (x_PRICE_G + 1)
257 + ceil (x_PRICE_T + 1)
258 + ceil (x_MARKET_PRICE_G + 1)
259 + ceil (x_MARKET_PRICE_T + 1)
260 + ceil (x_LIST_PRICE_T + 1)
261 + ceil (x_LIST_PRICE_G + 1)
262 + ceil (x_QTY_SUBS_RECEIPT_B + 1)
263 + ceil (x_QTY_SHIPPED_B + 1)
264 + ceil (x_QTY_REJECTED_B + 1)
265 + ceil (x_QTY_RECEIVED_TOL + 1)
266 + ceil (x_QTY_RECEIVED_B + 1)
267 + ceil (x_QTY_PAST_DUE_B + 1)
268 + ceil (x_QTY_ORDERED_B + 1)
269 + ceil (x_QTY_ONTIME_ONDUE_B + 1)
270 + ceil (x_QTY_ONTIME_BEFDUE_B + 1)
271 + ceil (x_QTY_ONTIME_AFTDUE_B + 1)
272 + ceil (x_QTY_LATE_RECEIPT_B + 1)
273 + ceil (x_QTY_EARLY_RECEIPT_B + 1)
274 + ceil (x_QTY_DELIVERED_B + 1)
275 + ceil (x_QTY_CANCELLED_B + 1)
276 + ceil (x_QTY_ACCEPTED_B + 1)
277 + ceil (x_AMT_PURCHASED_T + 1)
278 + ceil (x_AMT_PURCHASED_G + 1)
279 + ceil (x_IPV_T + 1)
280 + ceil (x_IPV_G + 1)
281 + ceil (x_DAYS_LATE_REC + 1)
282 + ceil (x_DAYS_EARLY_REC + 1)
283 + ceil (x_NUM_SUBS_RECEIPT + 1)
284 + ceil (x_NUM_RECEIPT_LINES + 1)
285 + ceil (x_NUM_ONTIME_ONDUE + 1)
286 + ceil (x_NUM_ONTIME_BEFDUE + 1)
287 + ceil (x_NUM_ONTIME_AFTDUE + 1)
288 + ceil (x_NUM_LATE_RECEIPT + 1)
289 + ceil (x_NUM_EARLY_RECEIPT + 1)
290 + ceil (x_NUM_DAYS_TO_INVOICE + 1);
291
292 -------------------------------------------------------------
293
294 OPEN c_1;
295 FETCH c_1 INTO x_supplier_item_fk, x_ITEM_ID,
296 x_ITEM_REVISION, x_category_id, x_ITEM_DESCRIPTION,
297 x_PRICE_TYPE_FK, x_CONTRACT_NUM;
298 CLOSE c_1;
299
300 x_item_fk := x_ITEM_ID + x_ITEM_REVISION +
301 x_category_id + x_ITEM_DESCRIPTION;
302 x_edw_base_uom_fk := x_ITEM_ID;
303 x_edw_uom_fk := x_ITEM_ID;
304
305 x_total := x_total
306 + NVL (ceil(x_PRICE_TYPE_FK + 1), 0)
307 + NVL (ceil(x_CONTRACT_NUM + 1), 0)
308 + NVL (ceil(x_item_fk + 1), 0)
309 + NVL (ceil(x_edw_base_uom_fk + 1), 0)
310 + NVL (ceil(x_edw_uom_fk + 1), 0)
311 + NVL (ceil(x_SUPPLIER_ITEM_FK + 1), 0);
312
313 -----------------------------------------------
314
315
316 OPEN c_2;
317 FETCH c_2 INTO x_sup_perf_pk, x_SHIP_TO_ORG_FK,
318 x_SHIP_LOCATION_FK, x_closed_code_fk,
319 x_rcv_close_tol;
320 CLOSE c_2;
321
322 x_PURCH_CLASS_FK := x_sup_perf_pk;
323
324 x_total := x_total
325 + NVL (ceil(x_sup_perf_pk + 1), 0)
326 + NVL (ceil(x_PURCH_CLASS_FK + 1), 0)
327 + NVL (ceil(x_SHIP_TO_ORG_FK + 1), 0)
328 + NVL (ceil(x_SHIP_LOCATION_FK + 1), 0)
329 + NVL (ceil(x_CLOSED_CODE_FK + 1), 0)
330 + NVL (ceil(x_rcv_close_tol + 1), 0) ;
331
332
333 ---------------------------------------------------
334
335
336 OPEN c_3;
337 FETCH c_3 INTO
338 x_BUYER_FK, x_SUPPLIER_SITE_FK, x_org_id,
339 x_AP_TERMS_FK, x_TXN_CUR_CODE_FK, x_PO_NUMBER;
340 CLOSE c_3;
341
342 x_supplier_item_fk := x_SUPPLIER_SITE_FK;
343 x_SUPPLIER_SITE_FK := x_SUPPLIER_SITE_FK + x_org_id;
344 x_sup_site_geog_fk := x_SUPPLIER_SITE_FK;
345
346 x_total := x_total
347 + NVL (ceil(x_BUYER_FK + 1), 0)
348 + NVL (ceil(x_SUPPLIER_SITE_FK + 1), 0)
349 + NVL (ceil(x_sup_site_geog_FK + 1), 0)
350 + NVL (ceil(x_supplier_item_FK + 1), 0)
351 + NVL (ceil(x_AP_TERMS_FK + 1), 0)
352 + NVL (ceil(x_TXN_CUR_CODE_FK + 1), 0)
353 + NVL (ceil(x_PO_NUMBER + 1), 0);
354
355
356 --------------------------------------------------------
357
358 OPEN c_4;
359 FETCH c_4 INTO x_EDW_BASE_UOM_FK;
360 CLOSE c_4;
361
362 x_EDW_UOM_FK := x_EDW_BASE_UOM_FK;
363
364 x_total := x_total + NVL (ceil(x_EDW_BASE_UOM_FK + 1), 0)
365 + NVL (ceil(x_EDW_UOM_FK + 1), 0);
366
367 OPEN c_5;
368 FETCH c_5 INTO x_PO_LINE_TYPE_FK;
369 CLOSE c_5;
370 x_total := x_total + NVL (ceil(x_PO_LINE_TYPE_FK + 1), 0);
371
372 OPEN c_6;
373 FETCH c_6 INTO x_RELEASE_NUM;
374 CLOSE c_6;
375 x_total := x_total + NVL (ceil(x_RELEASE_NUM + 1), 0);
376
377 OPEN c_7;
378 FETCH c_7 INTO x_item_fk;
379 CLOSE c_7;
380 x_total := x_total + NVL (ceil(x_item_fk + 1), 0);
381
382 OPEN c_8;
383 FETCH c_8 INTO x_supplier_item_fk;
384 CLOSE c_8;
385 x_total := x_total + NVL (ceil(x_supplier_item_fk + 1), 0);
386
387 --------------------------------------------------------
388
389 -- dbms_output.put_line(' ');
390 -- dbms_output.put_line('The average row length for Supplier Performance is: '
391 -- || to_char(x_total));
392
393 p_avg_row_len := x_total;
394
395 EXCEPTION
396 WHEN OTHERS THEN p_avg_row_len := 0;
397 END; -- procedure est_row_len.
398
399 END;