[Home] [Help]
PACKAGE BODY: APPS.POA_EDW_PO_DIST_F_SIZE
Source
1 PACKAGE BODY POA_EDW_PO_DIST_F_SIZE AS
2 /*$Header: poaszpdb.pls 120.0 2005/06/01 19:49:27 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_distributions_all pod,
15 po_line_locations_all pll,
16 po_lines_all pol,
17 po_headers_all poh
18 WHERE pll.shipment_type in ('BLANKET', 'SCHEDULED', 'STANDARD')
19 and pll.approved_flag = 'Y'
20 and pll.line_location_id = pod.line_location_id
21 and pod.po_line_id = pol.po_line_id
22 and pod.po_header_id = poh.po_header_id
23 and greatest(pol.last_update_date, poh.last_update_date,
24 pll.last_update_date, pod.last_update_date)
25 between p_from_date and p_to_date;
26
27 -- dbms_output.put_line('The number of rows for PO distribution is: '
28 -- || to_char(p_num_rows));
29
30 EXCEPTION
31 WHEN OTHERS THEN p_num_rows := 0;
32 END;
33
34 -------------------------------------------------------
35
36 PROCEDURE est_row_len (p_from_date IN DATE,
37 p_to_date IN DATE,
38 p_avg_row_len OUT NOCOPY NUMBER) IS
39
40 x_date number := 7;
41 x_total number := 0;
42 x_constant number := 6;
43 x_float number := 11;
44 x_int number := 6;
45
46 x_ACCPT_DUE_DATE_FK NUMBER;
47 x_ACCPT_REQUIRED_FK NUMBER;
48 x_ACCRUED_FK NUMBER;
49 x_AMT_BILLED_G NUMBER;
50 x_AMT_BILLED_T NUMBER;
51 x_AMT_CONTRACT_G NUMBER;
52 x_AMT_CONTRACT_T NUMBER;
53 x_AMT_LEAKAGE_G NUMBER;
54 x_AMT_LEAKAGE_T NUMBER;
55 x_AMT_NONCONTRACT_G NUMBER;
56 x_AMT_NONCONTRACT_T NUMBER;
57 x_AMT_PURCHASED_G NUMBER;
58 x_AMT_PURCHASED_T NUMBER;
59 x_APPROVER_FK NUMBER;
60 x_AP_TERMS_FK NUMBER;
61 x_BILL_LOCATION_FK NUMBER;
62 x_BUYER_FK NUMBER;
63 x_COLLECTION_STATUS NUMBER;
64 x_CONFIRM_ORDER_FK NUMBER;
65 x_CONTRACT_NUM NUMBER;
66 x_CONTRACT_TYPE_FK NUMBER;
67 x_DELIVER_TO_FK NUMBER;
68 x_DELIV_LOCATION_FK NUMBER;
69 x_DESTIN_ORG_FK NUMBER;
70 x_DESTIN_TYPE_FK NUMBER;
71 x_DISTRIBUTION_ID NUMBER;
72 x_DST_CREAT_DATE_FK NUMBER;
73 x_DST_ENCUMB_FK NUMBER;
74 x_EDI_PROCESSED_FK NUMBER;
75 x_ERROR_CODE NUMBER;
76 x_FOB_FK NUMBER;
77 x_FREIGHT_TERMS_FK NUMBER;
78 x_FROZEN_FK NUMBER;
79 x_INSPECTION_REQ_FK NUMBER;
80 x_INSTANCE_FK NUMBER;
81 x_ITEM_DESCRIPTION NUMBER;
82 x_ITEM_ID NUMBER;
83 x_ITEM_FK NUMBER;
84 x_LINE_LOCATION_ID NUMBER;
85 x_LIST_PRC_UNIT_G NUMBER;
86 x_LIST_PRC_UNIT_T NUMBER;
87 x_LNE_CREAT_DATE_FK NUMBER;
88 x_LNE_SUPPLIER_NOTE NUMBER;
89 x_LST_ACCPT_DATE_FK NUMBER;
90 x_MARKET_PRICE_G NUMBER;
91 x_MARKET_PRICE_T NUMBER;
92 x_NEED_BY_DATE_FK NUMBER;
93 x_NEG_BY_PREPARE_FK NUMBER;
94 x_ONLINE_REQ_FK NUMBER;
95 x_OPERATION_CODE NUMBER;
96 x_PCARD_PROCESS_FK NUMBER;
97 x_POTENTIAL_SVG_G NUMBER;
98 x_POTENTIAL_SVG_T NUMBER;
99 x_PO_ACCEPT_DATE_FK NUMBER;
100 x_PO_APP_DATE_FK NUMBER;
101 x_PO_COMMENTS NUMBER;
102 x_PO_CREATE_DATE_FK NUMBER;
103 x_PO_DIST_INST_PK NUMBER;
104 x_PO_HEADER_ID NUMBER;
105 x_PO_LINE_ID NUMBER;
106 x_PO_LINE_TYPE_FK NUMBER;
107 x_PO_NUMBER NUMBER;
108 x_PO_RECEIVER_NOTE NUMBER;
109 x_PO_RELEASE_ID NUMBER;
110 x_PRICE_BREAK_FK NUMBER;
111 x_PRICE_G NUMBER;
112 x_PRICE_T NUMBER;
113 x_PRICE_LIMIT_G NUMBER;
114 x_PRICE_LIMIT_T NUMBER;
115 x_PRICE_TYPE_FK NUMBER;
116 x_PRINTED_DATE_FK NUMBER;
117 x_PROMISED_DATE_FK NUMBER;
118 x_PURCH_CLASS_FK NUMBER;
119 x_QTY_BILLED_B NUMBER;
120 x_QTY_CANCELLED_B NUMBER;
121 x_QTY_DELIVERED_B NUMBER;
122 x_QTY_ORDERED_B NUMBER;
123 x_RCV_ROUTING_FK NUMBER;
124 x_RECEIPT_REQ_FK NUMBER;
125 x_RELEASE_DATE_FK NUMBER;
126 x_RELEASE_HOLD_FK NUMBER;
127 x_RELEASE_NUM NUMBER;
128 x_REQUEST_ID NUMBER;
129 x_REQ_APPRV_DATE_FK NUMBER;
130 x_REQ_CREAT_DATE_FK NUMBER;
131 x_REVISED_DATE_FK NUMBER;
132 x_REVISION_NUM NUMBER;
133 x_ROW_ID NUMBER;
134 x_SHIPMENT_TYPE_FK NUMBER;
135 x_SHIP_LOCATION_FK NUMBER;
136 x_SHIP_TO_ORG_FK NUMBER;
137 x_SHIP_VIA_FK NUMBER;
138 x_SHP_APPROVED_FK NUMBER;
139 x_SHP_APP_DATE_FK NUMBER;
140 x_SHP_CANCELLED_FK NUMBER;
141 x_SHP_CANCEL_REASON NUMBER;
142 x_SHP_CLOSED_FK NUMBER;
143 x_SHP_CLOSED_REASON NUMBER;
144 x_SHP_CREAT_DATE_FK NUMBER;
145 x_SHP_SRC_SHIP_ID NUMBER;
146 x_SHP_TAXABLE_FK NUMBER;
147 x_SOB_FK NUMBER;
148 x_SOURCE_DIST_ID NUMBER;
149 x_SUB_RECEIPT_FK NUMBER;
150 x_SUPPLIER_ITEM_FK NUMBER;
151 x_SUPPLIER_NOTE NUMBER;
152 x_SUPPLIER_SITE_FK NUMBER;
153 x_SUP_SITE_GEOG_FK NUMBER;
154 x_TXN_CUR_CODE_FK NUMBER;
155 x_TXN_CUR_DATE_FK NUMBER;
156 x_TXN_REASON_FK NUMBER;
157 x_EDW_UOM_FK NUMBER;
158 x_EDW_BASE_UOM_FK NUMBER;
159 x_IPV_G NUMBER;
160 x_IPV_T NUMBER;
161 x_INV_TO_PAY_CYCLE_TIME NUMBER;
162 x_INV_CREATION_CYCLE_TIME NUMBER;
163 x_RECEIVE_TO_PAY_CYCL_TIME NUMBER;
164 x_ORDER_TO_PAY_CYCLE_TIME NUMBER;
165 x_PO_CREATION_CYCLE_TIME NUMBER;
166 x_TASK_FK NUMBER;
167 x_PROJECT_FK NUMBER;
168 x_APPRV_SUPPLIER_FK NUMBER;
169 x_GOODS_RECEIVED_DATE_FK NUMBER;
170 x_INV_CREATION_DATE_FK NUMBER;
171 x_INV_RECEIVED_DATE_FK NUMBER;
172 x_CHECK_CUT_DATE_FK NUMBER;
173 x_SIC_CODE_FK NUMBER;
174 x_UNSPSC_FK NUMBER;
175 x_DUNS_FK NUMBER;
176
177 x_category_id NUMBER;
178 x_item_revision NUMBER;
179 x_org_id NUMBER;
180
181
182 CURSOR c_1 IS
183 SELECT avg(nvl(vsize(po_distribution_id), 0)),
184 avg(nvl(vsize(deliver_to_person_id), 0)),
185 avg(nvl(vsize(destination_organization_id), 0)),
186 avg(nvl(vsize(set_of_books_id), 0)),
187 avg(nvl(vsize(deliver_to_location_id), 0)),
188 avg(nvl(vsize(task_id), 0)),
189 avg(nvl(vsize(project_id), 0)),
190 avg(nvl(vsize(destination_type_code), 0)),
191 avg(nvl(vsize(accrued_flag), 0)),
192 avg(nvl(vsize(encumbered_flag), 0)),
193 avg(nvl(vsize(req_distribution_id), 0)),
194 avg(nvl(vsize(line_location_id), 0)),
195 avg(nvl(vsize(po_header_id), 0)),
196 avg(nvl(vsize(po_line_id), 0)),
197 avg(nvl(vsize(po_release_id), 0)),
198 avg(nvl(vsize(source_distribution_id), 0))
199 from po_distributions_all
200 where last_update_date between
201 p_from_date and p_to_date;
202
203 CURSOR c_2 IS
204 SELECT avg(nvl(vsize(vendor_product_num), 0)),
205 avg(nvl(vsize(item_id), 0)),
206 avg(nvl(vsize(ITEM_REVISION), 0)),
207 avg(nvl(vsize(category_id), 0)),
208 avg(nvl(vsize(transaction_reason_code), 0)),
209 avg(nvl(vsize(price_type_lookup_code), 0)),
210 avg(nvl(vsize(price_break_lookup_code), 0)),
211 avg(nvl(vsize(negotiated_by_preparer_flag), 0)),
212 avg(nvl(vsize(item_description), 0)),
213 avg(nvl(vsize(note_to_vendor), 0)),
214 avg(nvl(vsize(contract_num), 0))
215 from po_lines_all
216 where last_update_date between
217 p_from_date and p_to_date;
218
219 CURSOR c_3 IS
220 SELECT avg(nvl(vsize(line_location_id), 0)),
221 avg(nvl(vsize(ship_to_organization_id), 0)),
222 avg(nvl(vsize(ship_to_location_id), 0)),
223 avg(nvl(vsize(shipment_type), 0)),
224 avg(nvl(vsize(closed_code), 0)),
225 avg(nvl(vsize(allow_substitute_receipts_flag), 0)),
226 avg(nvl(vsize(approved_flag), 0)),
227 avg(nvl(vsize(cancel_flag), 0)),
228 avg(nvl(vsize(inspection_required_flag), 0)),
229 avg(nvl(vsize(receipt_required_flag), 0)),
230 avg(nvl(vsize(taxable_flag), 0)),
231 avg(nvl(vsize(cancel_reason), 0)),
232 avg(nvl(vsize(closed_reason), 0)),
233 avg(nvl(vsize(source_shipment_id), 0))
234 from po_line_locations_all
235 where last_update_date between
236 p_from_date and p_to_date;
237
238 CURSOR c_4 IS
239 SELECT avg(nvl(vsize(agent_id), 0)),
240 avg(nvl(vsize(vendor_site_id), 0)),
241 avg(nvl(vsize(org_id), 0)),
242 avg(nvl(vsize(bill_to_location_id), 0)),
243 avg(nvl(vsize(terms_id), 0)),
244 avg(nvl(vsize(ship_via_lookup_code), 0)),
245 avg(nvl(vsize(fob_lookup_code), 0)),
246 avg(nvl(vsize(freight_terms_lookup_code), 0)),
247 avg(nvl(vsize(acceptance_required_flag), 0)),
248 avg(nvl(vsize(frozen_flag), 0)),
249 avg(nvl(vsize(user_hold_flag), 0)),
250 avg(nvl(vsize(confirming_order_flag), 0)),
251 avg(nvl(vsize(edi_processed_flag), 0)),
252 avg(nvl(vsize(pcard_id), 0)),
253 avg(nvl(vsize(currency_code), 0)),
254 avg(nvl(vsize(note_to_vendor), 0)),
255 avg(nvl(vsize(comments), 0)),
256 avg(nvl(vsize(note_to_receiver), 0)),
257 avg(nvl(vsize(revision_num), 0)),
258 avg(nvl(vsize(segment1), 0))
259 from po_headers_all
260 where last_update_date between
261 p_from_date and p_to_date;
262
263 CURSOR c_5 IS
264 SELECT avg(nvl(vsize(routing_name), 0))
265 from rcv_routing_headers;
266
267 CURSOR c_6 IS
268 SELECT avg(nvl(vsize(uom_code), 0))
269 from mtl_units_of_measure
270 where last_update_date between
271 p_from_date and p_to_date;
272
273 CURSOR c_7 IS
274 SELECT avg(nvl(vsize(line_type), 0))
275 from po_line_types
276 where last_update_date between
277 p_from_date and p_to_date;
278
279 CURSOR c_8 IS
280 SELECT avg(nvl(vsize(release_num), 0))
281 from po_releases_all
282 where last_update_date between
283 p_from_date and p_to_date;
284
285 CURSOR c_9 IS
286 SELECT avg(nvl(vsize(inventory_organization_id), 0))
287 from FINANCIALS_SYSTEM_PARAMS_ALL
288 where last_update_date between
289 p_from_date and p_to_date;
290
291 CURSOR c_10 IS
292 SELECT avg(nvl(vsize(vendor_name), 0))
293 from PO_VENDORS
294 where last_update_date between
295 p_from_date and p_to_date;
296
297
298 BEGIN
299
300 -- dbms_output.enable(100000);
301
302 -- all date FKs
303
304 x_ACCPT_DUE_DATE_FK := x_date;
305 x_DST_CREAT_DATE_FK := x_date;
306 x_LNE_CREAT_DATE_FK := x_date;
307 x_LST_ACCPT_DATE_FK := x_date;
308 x_NEED_BY_DATE_FK := x_date;
309 x_PO_ACCEPT_DATE_FK := x_date;
310 x_PO_APP_DATE_FK := x_date;
311 x_PO_CREATE_DATE_FK := x_date;
312 x_PRINTED_DATE_FK := x_date;
313 x_PROMISED_DATE_FK := x_date;
314 x_RELEASE_DATE_FK := x_date;
315 x_REQ_APPRV_DATE_FK := x_date;
316 x_REQ_CREAT_DATE_FK := x_date;
317 x_REVISED_DATE_FK := x_date;
318 x_SHP_APP_DATE_FK := x_date;
319 x_SHP_CREAT_DATE_FK := x_date;
320 x_TXN_CUR_DATE_FK := x_date;
321 x_GOODS_RECEIVED_DATE_FK := x_date;
322 x_INV_CREATION_DATE_FK := x_date;
323 x_INV_RECEIVED_DATE_FK := x_date;
324 x_CHECK_CUT_DATE_FK := x_date;
325
326 x_total := 3 + x_total
327 + ceil (x_ACCPT_DUE_DATE_FK + 1)
328 + ceil (x_DST_CREAT_DATE_FK + 1)
329 + ceil (x_LNE_CREAT_DATE_FK + 1)
330 + ceil (x_LST_ACCPT_DATE_FK + 1)
331 + ceil (x_NEED_BY_DATE_FK + 1)
332 + ceil (x_PO_ACCEPT_DATE_FK + 1)
333 + ceil (x_PO_APP_DATE_FK + 1)
334 + ceil (x_PO_CREATE_DATE_FK + 1)
335 + ceil (x_PRINTED_DATE_FK + 1)
336 + ceil (x_PROMISED_DATE_FK + 1)
337 + ceil (x_RELEASE_DATE_FK + 1)
338 + ceil (x_REQ_APPRV_DATE_FK + 1)
339 + ceil (x_REQ_CREAT_DATE_FK + 1)
340 + ceil (x_REVISED_DATE_FK + 1)
341 + ceil (x_SHP_APP_DATE_FK + 1)
342 + ceil (x_SHP_CREAT_DATE_FK + 1)
343 + ceil (x_TXN_CUR_DATE_FK + 1)
344 + ceil (x_GOODS_RECEIVED_DATE_FK + 1)
345 + ceil (x_INV_CREATION_DATE_FK + 1)
346 + ceil (x_INV_RECEIVED_DATE_FK + 1)
347 + ceil (x_CHECK_CUT_DATE_FK + 1);
348
349 -- all calculated numbers
350
351 x_AMT_BILLED_G := x_float;
352 x_AMT_BILLED_T := x_float;
353 x_AMT_CONTRACT_G := x_float;
354 x_AMT_CONTRACT_T := x_float;
355 x_AMT_LEAKAGE_G := x_float;
356 x_AMT_LEAKAGE_T := x_float;
357 x_AMT_NONCONTRACT_G := x_float;
358 x_AMT_NONCONTRACT_T := x_float;
359 x_AMT_PURCHASED_G := x_float;
360 x_AMT_PURCHASED_T := x_float;
361 x_QTY_BILLED_B := x_float;
362 x_QTY_CANCELLED_B := x_float;
363 x_QTY_DELIVERED_B := x_float;
364 x_QTY_ORDERED_B := x_float;
365 x_MARKET_PRICE_G := x_float;
366 x_MARKET_PRICE_T := x_float;
367 x_LIST_PRC_UNIT_G := x_float;
368 x_LIST_PRC_UNIT_T := x_float;
369 x_POTENTIAL_SVG_G := x_float;
370 x_POTENTIAL_SVG_T := x_float;
371 x_PRICE_G := x_float;
372 x_PRICE_T := x_float;
373 x_PRICE_LIMIT_G := x_float;
374 x_PRICE_LIMIT_T := x_float;
375 x_IPV_G := x_float;
376 x_IPV_T := x_float;
377
378 x_INV_TO_PAY_CYCLE_TIME := x_float;
379 x_INV_CREATION_CYCLE_TIME := x_float;
380 x_RECEIVE_TO_PAY_CYCL_TIME := x_float;
381 x_ORDER_TO_PAY_CYCLE_TIME := x_float;
382 x_PO_CREATION_CYCLE_TIME := x_float;
383
384 x_total := x_total
385 + ceil (x_AMT_BILLED_G + 1)
386 + ceil (x_AMT_BILLED_T + 1)
387 + ceil (x_AMT_CONTRACT_G + 1)
388 + ceil (x_AMT_CONTRACT_T + 1)
389 + ceil (x_AMT_LEAKAGE_G + 1)
390 + ceil (x_AMT_LEAKAGE_T + 1)
391 + ceil (x_AMT_NONCONTRACT_G + 1)
392 + ceil (x_AMT_NONCONTRACT_T + 1)
393 + ceil (x_AMT_PURCHASED_G + 1)
394 + ceil (x_AMT_PURCHASED_T + 1)
395 + ceil (x_QTY_BILLED_B + 1)
396 + ceil (x_QTY_CANCELLED_B + 1)
397 + ceil (x_QTY_DELIVERED_B + 1)
398 + ceil (x_QTY_ORDERED_B + 1)
399 + ceil (x_MARKET_PRICE_G + 1)
400 + ceil (x_MARKET_PRICE_T + 1)
401 + ceil (x_LIST_PRC_UNIT_G + 1)
402 + ceil (x_LIST_PRC_UNIT_T + 1)
403 + ceil (x_POTENTIAL_SVG_G + 1)
404 + ceil (x_POTENTIAL_SVG_T + 1)
405 + ceil (x_PRICE_G + 1)
406 + ceil (x_PRICE_T + 1)
407 + ceil (x_PRICE_LIMIT_G + 1)
408 + ceil (x_PRICE_LIMIT_T + 1)
409 + ceil (x_IPV_G + 1)
410 + ceil (x_IPV_T + 1)
411 + ceil (x_INV_TO_PAY_CYCLE_TIME + 1)
412 + ceil (x_INV_CREATION_CYCLE_TIME + 1)
413 + ceil (x_RECEIVE_TO_PAY_CYCL_TIME + 1)
414 + ceil (x_ORDER_TO_PAY_CYCLE_TIME + 1)
415 + ceil (x_PO_CREATION_CYCLE_TIME + 1);
416
417
418 -------------------------------------------------------------
419
420 OPEN c_1;
421 FETCH c_1 INTO x_PO_DIST_INST_PK, x_DELIVER_TO_FK, x_DESTIN_ORG_FK,
422 x_SOB_FK, x_DELIV_LOCATION_FK, x_TASK_FK, x_PROJECT_FK,
423 x_DESTIN_TYPE_FK , x_ACCRUED_FK, x_DST_ENCUMB_FK, x_ONLINE_REQ_FK,
424 x_LINE_LOCATION_ID, x_PO_HEADER_ID, x_PO_LINE_ID,
425 x_PO_RELEASE_ID, x_SOURCE_DIST_ID;
426 CLOSE c_1;
427
428 x_APPRV_SUPPLIER_FK := x_PO_DIST_INST_PK;
429 x_DISTRIBUTION_ID := x_PO_DIST_INST_PK;
430
431 x_total := x_total
432 + NVL (ceil(x_PO_DIST_INST_PK + 1), 0)
433 + NVL (ceil(x_DELIVER_TO_FK + 1), 0)
434 + NVL (ceil(x_DESTIN_ORG_FK + 1), 0)
435 + NVL (ceil(x_SOB_FK + 1), 0)
436 + NVL (ceil(x_DELIV_LOCATION_FK + 1), 0)
437 + NVL (ceil(x_TASK_FK + 1), 0)
438 + NVL (ceil(x_PROJECT_FK + 1), 0)
439 + NVL (ceil(x_DESTIN_TYPE_FK + 1), 0)
440 + NVL (ceil(x_ACCRUED_FK + 1), 0)
441 + NVL (ceil(x_DST_ENCUMB_FK + 1), 0)
442 + NVL (ceil(x_ONLINE_REQ_FK + 1), 0)
443 + NVL (ceil(x_LINE_LOCATION_ID + 1), 0)
444 + NVL (ceil(x_PO_HEADER_ID + 1), 0)
445 + NVL (ceil(x_PO_LINE_ID + 1), 0)
446 + NVL (ceil(x_PO_RELEASE_ID + 1), 0)
447 + NVL (ceil(x_SOURCE_DIST_ID + 1), 0)
448 + NVL (ceil(x_APPRV_SUPPLIER_FK + 1), 0)
449 + NVL (ceil(x_DISTRIBUTION_ID + 1), 0);
450
451 --------------------------------------------------------
452
453 OPEN c_2;
454 FETCH c_2 INTO x_supplier_item_fk, x_ITEM_ID,
455 x_ITEM_REVISION, x_category_id, x_TXN_REASON_FK,
456 x_PRICE_TYPE_FK, x_PRICE_BREAK_FK,
457 x_NEG_BY_PREPARE_FK, x_ITEM_DESCRIPTION,
458 x_LNE_SUPPLIER_NOTE, x_CONTRACT_NUM;
459 CLOSE c_2;
460
461 x_item_fk := x_ITEM_ID + x_ITEM_REVISION +
462 x_category_id + x_ITEM_DESCRIPTION;
463 x_edw_base_uom_fk := x_ITEM_ID;
464 x_edw_uom_fk := x_ITEM_ID;
465 x_contract_type_fk := x_CONTRACT_NUM;
466
467 x_total := x_total
468 + NVL (ceil(x_ITEM_ID + 1), 0)
469 + NVL (ceil(x_TXN_REASON_FK + 1), 0)
470 + NVL (ceil(x_PRICE_TYPE_FK + 1), 0)
471 + NVL (ceil(x_PRICE_BREAK_FK + 1), 0)
472 + NVL (ceil(x_NEG_BY_PREPARE_FK + 1), 0)
473 + NVL (ceil(x_ITEM_DESCRIPTION + 1), 0)
474 + NVL (ceil(x_LNE_SUPPLIER_NOTE + 1), 0)
475 + NVL (ceil(x_CONTRACT_NUM + 1), 0)
476 + NVL (ceil(x_item_fk + 1), 0)
477 + NVL (ceil(x_edw_base_uom_fk + 1), 0)
478 + NVL (ceil(x_edw_uom_fk + 1), 0)
479 + NVL (ceil(x_SUPPLIER_ITEM_FK + 1), 0)
480 + NVL (ceil(x_contract_type_fk + 1), 0);
481
482 -----------------------------------------------
483
484
485 OPEN c_3;
486 FETCH c_3 INTO x_PURCH_CLASS_FK, x_SHIP_TO_ORG_FK,
487 x_SHIP_LOCATION_FK, x_SHIPMENT_TYPE_FK, x_SHP_CLOSED_FK,
488 x_SUB_RECEIPT_FK, x_SHP_APPROVED_FK, x_SHP_CANCELLED_FK,
489 x_INSPECTION_REQ_FK, x_RECEIPT_REQ_FK, x_SHP_TAXABLE_FK,
490 x_SHP_CANCEL_REASON, x_SHP_CLOSED_REASON, x_SHP_SRC_SHIP_ID;
491 CLOSE c_3;
492
493
494 x_total := x_total
495 + NVL (ceil(x_PURCH_CLASS_FK + 1), 0)
496 + NVL (ceil(x_SHIP_TO_ORG_FK + 1), 0)
497 + NVL (ceil(x_SHIP_LOCATION_FK + 1), 0)
498 + NVL (ceil(x_SHIPMENT_TYPE_FK + 1), 0)
499 + NVL (ceil(x_SHP_CLOSED_FK + 1), 0)
500 + NVL (ceil(x_SUB_RECEIPT_FK + 1), 0)
501 + NVL (ceil(x_SHP_APPROVED_FK + 1), 0)
502 + NVL (ceil(x_SHP_CANCELLED_FK + 1), 0)
503 + NVL (ceil(x_INSPECTION_REQ_FK + 1), 0)
504 + NVL (ceil(x_RECEIPT_REQ_FK + 1), 0)
505 + NVL (ceil(x_SHP_TAXABLE_FK + 1), 0)
506 + NVL (ceil(x_SHP_CANCEL_REASON + 1), 0)
507 + NVL (ceil(x_SHP_CLOSED_REASON + 1), 0)
508 + NVL (ceil(x_SHP_SRC_SHIP_ID + 1), 0);
509
510 ---------------------------------------------------
511
512
513 OPEN c_4;
514 FETCH c_4 INTO
515 x_BUYER_FK, x_SUPPLIER_SITE_FK, x_org_id, x_bill_location_fk,
516 x_AP_TERMS_FK, x_SHIP_VIA_FK, x_FOB_FK, x_FREIGHT_TERMS_FK,
517 x_ACCPT_REQUIRED_FK, x_FROZEN_FK, x_RELEASE_HOLD_FK,
518 x_CONFIRM_ORDER_FK, x_EDI_PROCESSED_FK, x_PCARD_PROCESS_FK,
519 x_TXN_CUR_CODE_FK, x_SUPPLIER_NOTE, x_PO_COMMENTS,
520 x_PO_RECEIVER_NOTE, x_REVISION_NUM, x_PO_NUMBER;
521 CLOSE c_4;
522
523 x_supplier_item_fk := x_SUPPLIER_SITE_FK;
524 x_SUPPLIER_SITE_FK := x_SUPPLIER_SITE_FK + x_org_id;
525 x_sup_site_geog_fk := x_SUPPLIER_SITE_FK;
526 x_APPROVER_FK := x_BUYER_FK;
527
528 x_total := x_total
529 + NVL (ceil(x_BUYER_FK + 1), 0)
530 + NVL (ceil(x_APPROVER_FK + 1), 0)
531 + NVL (ceil(x_SUPPLIER_SITE_FK + 1), 0)
532 + NVL (ceil(x_sup_site_geog_FK + 1), 0)
533 + NVL (ceil(x_supplier_item_FK + 1), 0)
534 + NVL (ceil(x_bill_location_FK + 1), 0)
535 + NVL (ceil(x_AP_TERMS_FK + 1), 0)
536 + NVL (ceil(x_SHIP_VIA_FK + 1), 0)
537 + NVL (ceil(x_FOB_FK + 1), 0)
538 + NVL (ceil(x_FREIGHT_TERMS_FK + 1), 0)
539 + NVL (ceil(x_ACCPT_REQUIRED_FK + 1), 0)
540 + NVL (ceil(x_FROZEN_FK + 1), 0)
541 + NVL (ceil(x_RELEASE_HOLD_FK + 1), 0)
542 + NVL (ceil(x_CONFIRM_ORDER_FK + 1), 0)
543 + NVL (ceil(x_EDI_PROCESSED_FK + 1), 0)
544 + NVL (ceil(x_PCARD_PROCESS_FK + 1), 0)
545 + NVL (ceil(x_TXN_CUR_CODE_FK + 1), 0)
546 + NVL (ceil(x_SUPPLIER_NOTE + 1), 0)
547 + NVL (ceil(x_PO_COMMENTS + 1), 0)
548 + NVL (ceil(x_PO_RECEIVER_NOTE + 1), 0)
549 + NVL (ceil(x_REVISION_NUM + 1), 0)
550 + NVL (ceil(x_PO_NUMBER + 1), 0);
551
552
553 --------------------------------------------------------
554
555 OPEN c_5;
556 FETCH c_5 INTO x_RCV_ROUTING_FK;
557 CLOSE c_5;
558 x_total := x_total + NVL (ceil(x_RCV_ROUTING_FK + 1), 0);
559
560 OPEN c_6;
561 FETCH c_6 INTO x_EDW_BASE_UOM_FK;
562 CLOSE c_6;
563
564 x_EDW_UOM_FK := x_EDW_BASE_UOM_FK;
565
566 x_total := x_total + NVL (ceil(x_EDW_BASE_UOM_FK + 1), 0)
567 + NVL (ceil(x_EDW_UOM_FK + 1), 0);
568
569 OPEN c_7;
570 FETCH c_7 INTO x_PO_LINE_TYPE_FK;
571 CLOSE c_7;
572 x_total := x_total + NVL (ceil(x_PO_LINE_TYPE_FK + 1), 0);
573
574 OPEN c_8;
575 FETCH c_8 INTO x_RELEASE_NUM;
576 CLOSE c_8;
577 x_total := x_total + NVL (ceil(x_RELEASE_NUM + 1), 0);
578
579 OPEN c_9;
580 FETCH c_9 INTO x_item_fk;
581 CLOSE c_9;
582 x_total := x_total + NVL (ceil(x_item_fk + 1), 0);
583
584 OPEN c_10;
585 FETCH c_10 INTO x_supplier_item_fk;
586 CLOSE c_10;
587 x_total := x_total + NVL (ceil(x_supplier_item_fk + 1), 0);
588
589 --------------------------------------------------------
590
591 -- dbms_output.put_line(' ');
592 -- dbms_output.put_line('The average row length for PO distribution is: '
593 -- || to_char(x_total));
594
595 p_avg_row_len := x_total;
596
597 EXCEPTION
598 WHEN OTHERS THEN p_avg_row_len := 0;
599 END; -- procedure est_row_len
600
601 END;