[Home] [Help]
PACKAGE BODY: APPS.POA_EDW_ALINES_F_SIZE
Source
1 PACKAGE BODY POA_EDW_ALINES_F_SIZE AS
2 /* $Header: poaszalb.pls 120.0 2005/06/01 19:22:13 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_lines_all pol,
15 po_headers_all poh
16 WHERE poh.type_lookup_code = 'BLANKET'
17 and poh.approved_flag = 'Y'
18 and poh.po_header_id = pol.po_header_id
19 and greatest(pol.last_update_date, poh.last_update_date)
20 between p_from_date and p_to_date;
21
22 -- dbms_output.put_line('The number of rows for agreement lines is: '
23 -- || to_char(p_num_rows));
24
25 EXCEPTION
26 WHEN OTHERS THEN p_num_rows := 0;
27 END;
28
29 -------------------------------------------------------
30
31 PROCEDURE est_row_len (p_from_date IN DATE,
32 p_to_date IN DATE,
33 p_avg_row_len OUT NOCOPY NUMBER) IS
34
35 x_date number := 7;
36 x_total number := 0;
37 x_constant number := 6;
38 x_float number := 11;
39 x_int number := 6;
40
41 x_ACCPT_DUE_DATE_FK NUMBER;
42 x_ACCPT_REQUIRED_FK NUMBER;
43 x_AGREE_LN_INST_PK NUMBER;
44 x_AMT_AGREED_G NUMBER;
45 x_AMT_AGREED_T NUMBER;
46 x_AMT_MIN_RELEASE_G NUMBER;
47 x_AMT_MIN_RELEASE_T NUMBER;
48 x_AMT_RELEASED_G NUMBER;
49 x_AMT_RELEASED_T NUMBER;
50 x_APPROVED_DATE_FK NUMBER;
51 x_APPROVED_FK NUMBER;
52 x_APPROVER_FK NUMBER;
53 x_AP_TERMS_FK NUMBER;
54 x_BASE_UOM_FK NUMBER;
55 x_BILL_LOCATION_FK NUMBER;
56 x_BUYER_FK NUMBER;
57 x_CANCELLED_FK NUMBER;
58 x_CANCEL_REASON NUMBER;
59 x_COMMENTS NUMBER;
60 x_CONFIRM_ORDER_FK NUMBER;
61 x_CONTRACT_EFFECTIVE_FK NUMBER;
62 x_CREATION_DATE NUMBER;
63 x_EDI_PROCESSED_FK NUMBER;
64 x_END_DATE_FK NUMBER;
65 x_FOB_FK NUMBER;
66 x_FREIGHT_TERMS_FK NUMBER;
67 x_FROZEN_FK NUMBER;
68 x_INSTANCE_FK NUMBER;
69 x_ITEM_DESCRIPTION NUMBER;
70 x_ITEM_ID NUMBER;
71 x_ITEM_REVISION_FK NUMBER;
72 x_LAST_UPDATE_DATE NUMBER;
73 x_LIST_PRICE_G NUMBER;
74 x_LIST_PRICE_T NUMBER;
75 x_LNE_CLOSED_FK NUMBER;
76 x_LNE_CREAT_DATE_FK NUMBER;
77 x_MARKET_PRICE_G NUMBER;
78 x_MARKET_PRICE_T NUMBER;
79 x_NEG_BY_PREPARE_FK NUMBER;
80 x_OPERATING_UNIT_FK NUMBER;
81 x_PO_CLOSED_FK NUMBER;
82 x_PO_CREATE_DATE_FK NUMBER;
83 x_PO_HEADER_ID NUMBER;
84 x_PO_LINE_ID NUMBER;
85 x_PO_LINE_TYPE_FK NUMBER;
86 x_PO_PRINT_DATE_FK NUMBER;
87 x_PO_SUPPLIER_NOTE NUMBER;
88 x_PO_TYPE_FK NUMBER;
89 x_PRICE_BREAK_FK NUMBER;
90 x_PRICE_LIMIT_G NUMBER;
91 x_PRICE_LIMIT_T NUMBER;
92 x_PRICE_TYPE_FK NUMBER;
93 x_QTY_AGREED_T NUMBER;
94 x_QTY_MAX_ORDER_T NUMBER;
95 x_QTY_MIN_ORDER_T NUMBER;
96 x_QTY_ORDERED_T NUMBER;
97 x_QTY_RELEASED_T NUMBER;
98 x_RECEIVER_NOTE NUMBER;
99 x_USER_HOLD_FK NUMBER;
100 x_REVISED_DATE_FK NUMBER;
101 x_SHIP_LOCATION_FK NUMBER;
102 x_SHIP_VIA_FK NUMBER;
103 x_START_DATE_FK NUMBER;
104 x_SUPPLIER_ITEM_NUM_FK NUMBER;
105 x_SUPPLIER_SITE_FK NUMBER;
106 x_SUPPLY_AGREE_FK NUMBER;
107 x_SUP_SITE_GEOG_FK NUMBER;
108 x_TXN_CUR_CODE_FK NUMBER;
109 x_TXN_CUR_DATE_FK NUMBER;
110 x_TXN_REASON_FK NUMBER;
111 x_TXN_UOM_FK NUMBER;
112 x_SIC_CODE_FK NUMBER;
113 x_UNSPSC_FK NUMBER;
114 x_DUNS_FK NUMBER;
115 x_UNIT_PRICE_G NUMBER;
116 x_UNIT_PRICE_T NUMBER;
117 x_TXN_CUR_RATE_TYPE NUMBER;
118 x_category_id NUMBER;
119
120 -------------------------------------------------------------
121
122 CURSOR c_1 IS
123 SELECT avg(nvl(vsize(agent_id), 0)),
124 avg(nvl(vsize(po_header_id), 0)),
125 avg(nvl(vsize(vendor_site_id), 0)),
126 avg(nvl(vsize(org_id), 0)),
127 avg(nvl(vsize(terms_id), 0)),
128 avg(nvl(vsize(closed_code), 0)),
129 avg(nvl(vsize(type_lookup_code), 0)),
130 avg(nvl(vsize(ship_via_lookup_code), 0)),
131 avg(nvl(vsize(fob_lookup_code), 0)),
132 avg(nvl(vsize(freight_terms_lookup_code), 0)),
133 avg(nvl(vsize(acceptance_required_flag), 0)),
134 avg(nvl(vsize(frozen_flag), 0)),
135 avg(nvl(vsize(bill_to_location_id), 0)),
136 avg(nvl(vsize(ship_to_location_id), 0)),
137 avg(nvl(vsize(vendor_site_id), 0)),
138 avg(nvl(vsize(currency_code), 0)),
139 avg(nvl(vsize(comments), 0)),
140 avg(nvl(vsize(note_to_receiver), 0)),
141 avg(nvl(vsize(note_to_vendor), 0)),
142 avg(nvl(vsize(approved_flag), 0)),
143 avg(nvl(vsize(user_hold_flag), 0)),
144 avg(nvl(vsize(confirming_order_flag), 0)),
145 avg(nvl(vsize(supply_agreement_flag), 0)),
146 avg(nvl(vsize(edi_processed_flag), 0))
147 from PO_HEADERS_ALL
148 where last_update_date between
149 p_from_date and p_to_date;
150
151 --------
152
153 CURSOR c_2 IS
154 SELECT avg(nvl(vsize(po_line_id), 0)),
155 avg(nvl(vsize(ITEM_REVISION), 0)),
156 avg(nvl(vsize(item_id), 0)),
157 avg(nvl(vsize(item_description), 0)),
158 avg(nvl(vsize(category_id), 0)),
159 avg(nvl(vsize(transaction_reason_code), 0)),
160 avg(nvl(vsize(price_type_lookup_code), 0)),
161 avg(nvl(vsize(price_break_lookup_code), 0)),
162 avg(nvl(vsize(negotiated_by_preparer_flag), 0)),
163 avg(nvl(vsize(cancel_flag), 0)),
164 avg(nvl(vsize(closed_flag), 0)),
165 avg(nvl(vsize(VENDOR_PRODUCT_NUM), 0)),
166 avg(nvl(vsize(po_header_id), 0)),
167 avg(nvl(vsize(po_line_id), 0)),
168 avg(nvl(vsize(note_to_vendor), 0)),
169 avg(nvl(vsize(cancel_reason), 0))
170 from PO_LINES_ALL
171 where last_update_date between
172 p_from_date and p_to_date;
173
174 --------
175
176 CURSOR c_3 IS
177 SELECT avg(nvl(vsize(line_type), 0))
178 from PO_LINE_TYPES
179 where last_update_date between
180 p_from_date and p_to_date;
181 --------
182
183 CURSOR c_4 IS
184 SELECT avg(nvl(vsize(vendor_name), 0))
185 from PO_VENDORS
186 where last_update_date between
187 p_from_date and p_to_date;
188
189
190 CURSOR c_5 IS
191 SELECT avg(nvl(vsize(inventory_organization_id), 0))
192 from FINANCIALS_SYSTEM_PARAMS_ALL
193 where last_update_date between
194 p_from_date and p_to_date;
195
196 CURSOR c_6 IS
197 SELECT avg(nvl(vsize(currency_code), 0))
198 from gl_sets_of_books
199 where last_update_date between
200 p_from_date and p_to_date;
201
202 CURSOR c_7 IS
203 SELECT avg(nvl(vsize(uom_code), 0))
204 from mtl_units_of_measure
205 where last_update_date between
206 p_from_date and p_to_date;
207
208 BEGIN
209
210 -- dbms_output.enable(100000);
211
212 -- all date FKs
213
214 x_ACCPT_DUE_DATE_FK := x_date;
215 x_APPROVED_DATE_FK := x_date;
216 x_END_DATE_FK := x_date;
217 x_LNE_CREAT_DATE_FK := x_date;
218 x_PO_CREATE_DATE_FK := x_date;
219 x_PO_PRINT_DATE_FK := x_date;
220 x_REVISED_DATE_FK := x_date;
221 x_START_DATE_FK := x_date;
222 x_TXN_CUR_DATE_FK := x_date;
223
224 x_total := 3 + x_total
225 + ceil (x_ACCPT_DUE_DATE_FK + 1)
226 + ceil (x_APPROVED_DATE_FK + 1)
227 + ceil (x_END_DATE_FK + 1)
228 + ceil (x_LNE_CREAT_DATE_FK + 1)
229 + ceil (x_PO_CREATE_DATE_FK + 1)
230 + ceil (x_PO_PRINT_DATE_FK + 1)
231 + ceil (x_REVISED_DATE_FK + 1)
232 + ceil (x_START_DATE_FK + 1)
233 + ceil (x_TXN_CUR_DATE_FK + 1);
234
235 -- all calculated numbers
236
237 x_qty_released_t := x_float;
238 x_qty_ordered_t := x_float;
239 x_qty_min_order_t := x_float;
240 x_qty_max_order_t := x_float;
241 x_qty_agreed_t := x_float;
242
243 x_amt_released_t := x_float;
244 x_amt_released_g := x_float;
245 x_amt_agreed_t := x_float;
246 x_amt_agreed_g := x_float;
247 x_amt_min_release_t := x_float;
248 x_amt_min_release_g := x_float;
249 x_amt_released_t := x_float;
250 x_amt_released_g := x_float;
251
252 x_market_price_t := x_float;
253 x_market_price_g := x_float;
254 x_price_limit_t := x_float;
255 x_price_limit_g := x_float;
256 x_list_price_t := x_float;
257 x_list_price_g := x_float;
258 x_unit_price_t := x_float;
259 x_unit_price_g := x_float;
260
261
262 x_total := x_total
263 + ceil(x_qty_released_t + 1)
264 + ceil(x_qty_ordered_t + 1)
265 + ceil(x_qty_min_order_t + 1)
266 + ceil(x_qty_max_order_t + 1)
267 + ceil(x_qty_agreed_t + 1)
268 + ceil(x_amt_released_t + 1)
269 + ceil(x_amt_released_g + 1)
270 + ceil(x_amt_agreed_t + 1)
271 + ceil(x_amt_agreed_g + 1)
272 + ceil(x_amt_min_release_t + 1)
273 + ceil(x_amt_min_release_g + 1)
274 + ceil(x_amt_released_t + 1)
275 + ceil(x_amt_released_g + 1)
276 + ceil(x_market_price_t + 1)
277 + ceil(x_market_price_g + 1)
278 + ceil(x_price_limit_t + 1)
279 + ceil(x_price_limit_g + 1)
280 + ceil(x_list_price_t + 1)
281 + ceil(x_list_price_g + 1)
282 + ceil(x_unit_price_t + 1)
283 + ceil(x_unit_price_g + 1);
284
285 -----------------------------------------------------
286
287
288 OPEN c_1;
289 FETCH c_1 INTO x_buyer_fk, x_PO_HEADER_ID, x_supplier_site_fk,
290 x_operating_unit_fk, x_ap_terms_fk, x_po_closed_fk,
291 x_po_type_fk, x_ship_via_fk, x_fob_fk, x_freight_terms_fk,
292 x_accpt_required_fk, x_frozen_fk, x_bill_location_fk,
293 x_ship_location_fk, x_sup_site_geog_fk, x_txn_cur_code_fk,
294 x_comments, x_receiver_note,
295 x_po_supplier_note, x_approved_fk, x_user_hold_fk,
296 x_confirm_order_fk, x_supply_agree_fk, x_edi_processed_fk;
297 CLOSE c_1;
298
299 x_approver_fk := x_buyer_fk;
300 x_supplier_item_num_fk := x_supplier_site_fk;
301 x_supplier_site_fk := x_supplier_site_fk + x_operating_unit_fk;
302 x_sup_site_geog_fk := x_sup_site_geog_fk + x_operating_unit_fk;
303
304
305 x_total := x_total
306 + NVL (ceil(x_buyer_fk + 1), 0)
307 + NVL (ceil(x_approver_fk + 1), 0)
308 + NVL (ceil(x_PO_HEADER_ID + 1), 0)
309 + NVL (ceil(x_supplier_site_fk + 1), 0)
310 + NVL (ceil(x_operating_unit_fk + 1), 0)
311 + NVL (ceil(x_ap_terms_fk + 1), 0)
312 + NVL (ceil(x_po_closed_fk + 1), 0)
313 + NVL (ceil(x_po_type_fk + 1), 0)
314 + NVL (ceil(x_ship_via_fk + 1), 0)
315 + NVL (ceil(x_fob_fk + 1), 0)
316 + NVL (ceil(x_freight_terms_fk + 1), 0)
317 + NVL (ceil(x_accpt_required_fk + 1), 0)
318 + NVL (ceil(x_frozen_fk + 1), 0)
319 + NVL (ceil(x_bill_location_fk + 1), 0)
320 + NVL (ceil(x_ship_location_fk + 1), 0)
321 + NVL (ceil(x_sup_site_geog_fk + 1), 0)
322 + NVL (ceil(x_txn_cur_code_fk + 1), 0)
323 + NVL (ceil(x_comments + 1), 0)
324 + NVL (ceil(x_receiver_note + 1), 0)
325 + NVL (ceil(x_po_supplier_note + 1), 0)
326 + NVL (ceil(x_approved_fk + 1), 0)
327 + NVL (ceil(x_user_hold_fk + 1), 0)
328 + NVL (ceil(x_confirm_order_fk + 1), 0)
329 + NVL (ceil(x_supply_agree_fk + 1), 0)
330 + NVL (ceil(x_edi_processed_fk + 1), 0)
331 + NVL (ceil(x_supplier_item_num_fk + 1), 0);
332
333 --------------------------------------------------------------------
334
335 OPEN c_2;
336 FETCH c_2 INTO x_agree_ln_inst_pk, x_item_revision_fk,
337 x_item_id, x_item_description, x_category_id,
338 x_txn_reason_fk, x_price_type_fk,
339 x_price_break_fk, x_neg_by_prepare_fk,
340 x_cancelled_fk, x_lne_closed_fk,
341 x_supplier_item_num_fk, x_po_header_id, x_po_line_id,
342 x_po_supplier_note, x_cancel_reason;
343 CLOSE c_2;
344
345 x_item_revision_fk := x_item_revision_fk + x_item_id +
346 x_item_description + x_category_id;
347 x_base_uom_fk := x_item_id;
348 x_txn_uom_fk := x_item_id;
349
350 x_total := x_total
351 + NVL (ceil(x_agree_ln_inst_pk + 1), 0)
352 + NVL (ceil(x_item_revision_fk + 1), 0)
353 + NVL (ceil(x_item_id + 1), 0)
354 + NVL (ceil(x_item_description + 1), 0)
355 + NVL (ceil(x_txn_reason_fk + 1), 0)
356 + NVL (ceil(x_price_type_fk + 1), 0)
357 + NVL (ceil(x_price_break_fk + 1), 0)
358 + NVL (ceil(x_neg_by_prepare_fk + 1), 0)
359 + NVL (ceil(x_cancelled_fk + 1), 0)
360 + NVL (ceil(x_lne_closed_fk + 1), 0)
361 + NVL (ceil(x_supplier_item_num_fk + 1), 0)
362 + NVL (ceil(x_po_header_id + 1), 0)
363 + NVL (ceil(x_po_line_id + 1), 0)
364 + NVL (ceil(x_po_supplier_note + 1), 0)
365 + NVL (ceil(x_cancel_reason + 1), 0)
366 + NVL (ceil(x_base_uom_fk + 1), 0)
367 + NVL (ceil(x_txn_uom_fk + 1), 0);
368
369 ---------------------------------------------------------------------
370
371 OPEN c_3;
372 FETCH c_3 INTO x_po_line_type_fk;
373 CLOSE c_3;
374
375 x_total := x_total + NVL (ceil(x_po_line_type_fk + 1), 0);
376
377
378
379 OPEN c_4;
380 FETCH c_4 INTO x_supplier_item_num_fk;
381 CLOSE c_4;
382
383 x_total := x_total + NVL (ceil(x_supplier_item_num_fk + 1), 0);
384
385
386
387 OPEN c_5;
388 FETCH c_5 INTO x_item_revision_fk;
389 CLOSE c_5;
390
391 x_total := x_total + NVL (ceil(x_item_revision_fk + 1), 0);
392
393
394 OPEN c_6;
395 FETCH c_6 INTO x_txn_cur_code_fk;
396 CLOSE c_6;
397
398 x_total := x_total + NVL (ceil(x_txn_cur_code_fk + 1), 0);
399
400
401 OPEN c_7;
402 FETCH c_7 INTO x_base_uom_fk;
403 CLOSE c_7;
404
405 x_txn_uom_fk := x_base_uom_fk;
406
407 x_total := x_total
408 + NVL (ceil(x_txn_uom_fk + 1), 0)
409 + NVL (ceil(x_base_uom_fk + 1), 0);
410 ------------------------------------------------------------------
411
412 -- dbms_output.put_line(' ');
413 -- dbms_output.put_line('The average row length for agreement lines is: '
414 -- || to_char(x_total));
415
416 p_avg_row_len := x_total;
417
418 EXCEPTION
419 WHEN OTHERS THEN p_avg_row_len := 0;
420 END; -- procedure est_row_len.
421
422 END;