[Home] [Help]
PACKAGE BODY: APPS.POA_EDW_RCV_TXNS_F_SIZE
Source
1 PACKAGE BODY POA_EDW_RCV_TXNS_F_SIZE AS
2 /*$Header: poaszrtb.pls 120.0 2005/06/01 17:20:20 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 BEGIN
8
9 -- dbms_output.enable(100000);
10
11 select count(*) into p_num_rows
12 from
13 PO_HEADERS_ALL POH,
14 PO_LINES_ALL POL,
15 PO_LINE_LOCATIONS_ALL PLL,
16 RCV_SHIPMENT_HEADERS RSH,
17 RCV_SHIPMENT_LINES RSL,
18 RCV_TRANSACTIONS RCV
19 WHERE
20 RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
21 AND RCV.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
22 AND RCV.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
23 AND PLL.PO_HEADER_ID = POH.PO_HEADER_ID
24 AND PLL.PO_LINE_ID = POL.PO_LINE_ID
25 and greatest(pol.last_update_date, poh.last_update_date,
26 pll.last_update_date, rsh.last_update_date,
27 rsl.last_update_date, rcv.last_update_date)
28 between p_from_date and p_to_date;
29
30 -- dbms_output.put_line('The number of rows for receiving txns is: '
31 -- || to_char(p_num_rows));
32
33 EXCEPTION
34 WHEN OTHERS THEN p_num_rows := 0;
35 END;
36
37 -------------------------------------------------------
38
39 PROCEDURE est_row_len (p_from_date IN DATE,
40 p_to_date IN DATE,
41 p_avg_row_len OUT NOCOPY NUMBER) IS
42
43 x_date number := 7;
44 x_total number := 0;
45 x_constant number := 6;
46 x_float number := 11;
47 x_int number := 6;
48
49 x_SIC_CODE_FK NUMBER := 0;
50 x_UNSPSC_FK NUMBER := 0;
51 x_DUNS_FK NUMBER := 0;
52 x_EDW_UOM_FK NUMBER := 0;
53 x_EDW_BASE_UOM_FK NUMBER := 0;
54 x_SUPPLIER_SITE_FK NUMBER := 0;
55 x_TXN_DATE_FK NUMBER := 0;
56 x_PARNT_TXN_DATE_FK NUMBER := 0;
57 x_SRC_CREAT_DATE_FK NUMBER := 0;
58 x_LST_ACCPT_DATE_FK NUMBER := 0;
59 x_NEED_BY_DATE_FK NUMBER := 0;
60 x_PROMISED_DATE_FK NUMBER := 0;
61 x_EXPCT_RCV_DATE_FK NUMBER := 0;
62 x_SHIPPED_TO_DATE_FK NUMBER := 0;
63 x_TXN_CREAT_FK NUMBER := 0;
64 x_SUPPLIER_ITEM_NUM_FK NUMBER := 0;
65 x_DELIVER_TO_FK NUMBER := 0;
66 x_BUYER_FK NUMBER := 0;
67 x_AP_TERMS_FK NUMBER := 0;
68 x_RCV_DEL_TO_ORG_FK NUMBER := 0;
69 x_INSPECT_QUAL_FK NUMBER := 0;
70 x_INSPECT_STATUS_FK NUMBER := 0;
71 x_FREIGHT_TERMS_FK NUMBER := 0;
72 x_PARNT_TXN_TYPE_FK NUMBER := 0;
73 x_SUBST_UNORD_FK NUMBER := 0;
74 x_RCV_ROUTING_FK NUMBER := 0;
75 x_DESTIN_TYPE_FK NUMBER := 0;
76 x_RECEIPT_SOURCE_FK NUMBER := 0;
77 x_PURCHASE_CLASS_CODE_FK NUMBER := 0;
78 x_TXN_REASON_FK NUMBER := 0;
79 x_USER_ENTERED_FK NUMBER := 0;
80 x_RECEIVE_EXCEP_FK NUMBER := 0;
81 x_TXN_TYPE_FK NUMBER := 0;
82 x_LOCATOR_FK NUMBER := 0;
83 x_PO_LINE_TYPE_FK NUMBER := 0;
84 x_ITEM_REVISION_FK NUMBER := 0;
85 x_INSTANCE_FK NUMBER := 0;
86 x_DELIV_LOCATION_FK NUMBER := 0;
87 x_RCV_LOCATION_FK NUMBER := 0;
88 x_SUP_SITE_GEOG_FK NUMBER := 0;
89 x_TXN_CUR_CODE_FK NUMBER := 0;
90 x_QTY_RETURN_TO_RECEIVING NUMBER := 0;
91 x_QTY_REJECT NUMBER := 0;
92 x_QTY_RECEIVED NUMBER := 0;
93 x_QTY_DELIVER NUMBER := 0;
94 x_QTY_ACCEPT NUMBER := 0;
95 x_PRICE_T NUMBER := 0;
96 x_PRICE_G NUMBER := 0;
97 x_NUM_DAYS_TO_FULL_DEL NUMBER := 0;
98 x_QTY_TXN_NET NUMBER := 0;
99 x_QTY_TXN NUMBER := 0;
100 x_QTY_TRANSFER NUMBER := 0;
101 x_QTY_RETURN_TO_VENDOR NUMBER := 0;
102 x_RCV_TXN_PK_KEY NUMBER := 0;
103 x_WAY_AIRBILL_NUM NUMBER := 0;
104 x_VENDOR_LOT_NUM NUMBER := 0;
105 x_PACKING_SLIP NUMBER := 0;
106 x_INVOICE_NUM NUMBER := 0;
107 x_BILL_OF_LADING NUMBER := 0;
108 x_RCV_TXN_PK NUMBER := 0;
109 x_TXN_COMMENTS NUMBER := 0;
110 x_SOURCE_TXN_NUMBER NUMBER := 0;
111 x_SHIP_HDR_COMMENTS NUMBER := 0;
112 x_SHIPMENT_NUM NUMBER := 0;
113 x_RMA_REFERENCE NUMBER := 0;
114 x_RECEIPT_NUM_INST NUMBER := 0;
115 x_LAST_UPDATE_DATE NUMBER := 0;
116 x_CREATION_DATE NUMBER := 0;
117
118 x_organization_id NUMBER := 0;
119 x_subinventory NUMBER := 0;
120 x_category_id NUMBER := 0;
121 X_ITEM_DESCRIPTION NUMBER := 0;
122 x_item_id NUMBER := 0;
123 -----------------------------------
124
125 CURSOR c_1 IS
126 SELECT avg(nvl(vsize(TRANSACTION_ID), 0)),
127 avg(nvl(vsize(TRANSACTION_TYPE), 0)),
128 avg(nvl(vsize(DESTINATION_TYPE_CODE), 0)),
129 avg(nvl(vsize(location_id), 0)),
130 avg(nvl(vsize(deliver_to_location_id), 0)),
131 avg(nvl(vsize(locator_id), 0)),
132 avg(nvl(vsize(organization_id), 0)),
133 avg(nvl(vsize(subinventory), 0)),
134 avg(nvl(vsize(DELIVER_TO_PERSON_ID), 0)),
135 avg(nvl(vsize(SUBSTITUTE_UNORDERED_CODE), 0)),
136 avg(nvl(vsize(INSPECTION_STATUS_CODE), 0)),
137 avg(nvl(vsize(INSPECTION_QUALITY_CODE), 0)),
138 avg(nvl(vsize(RECEIPT_EXCEPTION_FLAG), 0)),
139 avg(nvl(vsize(USER_ENTERED_FLAG), 0)),
140 avg(nvl(vsize(REASON_ID), 0)),
141 avg(nvl(vsize(VENDOR_LOT_NUM), 0)),
142 avg(nvl(vsize(RMA_REFERENCE), 0)),
143 avg(nvl(vsize(COMMENTS), 0))
144 from RCV_TRANSACTIONS
145 where last_update_date between
146 p_from_date and p_to_date;
147
148 CURSOR c_1A IS
149 SELECT avg(nvl(vsize(ITEM_REVISION), 0)),
150 avg(nvl(vsize(PACKING_SLIP), 0))
151 from RCV_SHIPMENT_LINES
152 where last_update_date between
153 p_from_date and p_to_date;
154
155 CURSOR c_1B IS
156 SELECT avg(nvl(vsize(RECEIPT_SOURCE_CODE), 0)),
157 avg(nvl(vsize(PAYMENT_TERMS_ID), 0)),
158 avg(nvl(vsize(FREIGHT_CARRIER_CODE), 0)),
159 avg(nvl(vsize(SHIPMENT_NUM), 0)),
160 avg(nvl(vsize(RECEIPT_NUM), 0)),
161 avg(nvl(vsize(COMMENTS), 0)),
162 avg(nvl(vsize(WAYBILL_AIRBILL_NUM), 0)),
163 avg(nvl(vsize(BILL_OF_LADING), 0))
164 from RCV_SHIPMENT_HEADERS
165 where last_update_date between
166 p_from_date and p_to_date;
167
168
169 CURSOR c_2 IS
170 SELECT avg(nvl(vsize(item_id), 0)),
171 avg(nvl(vsize(item_description), 0)),
172 avg(nvl(vsize(category_id), 0)),
173 avg(nvl(vsize(vendor_product_num), 0))
174 from po_lines_all
175 where last_update_date between
176 p_from_date and p_to_date;
177
178 CURSOR c_3 IS
179 SELECT avg(nvl(vsize(ship_to_organization_id), 0))
180 from po_line_locations_all
181 where last_update_date between
182 p_from_date and p_to_date;
183
184 CURSOR c_4 IS
185 SELECT avg(nvl(vsize(vendor_site_id), 0)),
186 avg(nvl(vsize(org_id), 0)),
187 avg(nvl(vsize(agent_id), 0)),
188 avg(nvl(vsize(currency_code), 0)),
189 avg(nvl(vsize(segment1), 0))
190 from po_headers_all
191 where last_update_date between
192 p_from_date and p_to_date;
193
194 CURSOR c_5 IS
195 SELECT avg(nvl(vsize(routing_name), 0))
196 from rcv_routing_headers;
197
198 CURSOR c_6 IS
199 SELECT avg(nvl(vsize(uom_code), 0))
200 from mtl_units_of_measure
201 where last_update_date between
202 p_from_date and p_to_date;
203
204 CURSOR c_7 IS
205 SELECT avg(nvl(vsize(line_type), 0))
206 from po_line_types
207 where last_update_date between
208 p_from_date and p_to_date;
209
210 CURSOR c_8 IS
211 SELECT avg(nvl(vsize(INVOICE_NUM), 0))
212 from AP_INVOICES_ALL
213 where last_update_date between
214 p_from_date and p_to_date;
215
216 CURSOR c_9 IS
217 SELECT avg(nvl(vsize(inventory_organization_id), 0))
218 from FINANCIALS_SYSTEM_PARAMS_ALL
219 where last_update_date between
220 p_from_date and p_to_date;
221
222 CURSOR c_10 IS
223 SELECT avg(nvl(vsize(vendor_name), 0))
224 from PO_VENDORS
225 where last_update_date between
226 p_from_date and p_to_date;
227
228
229 BEGIN
230
231 -- dbms_output.enable(100000);
232
233 -- all date FKs
234
235 x_TXN_DATE_FK := x_date;
236 x_PARNT_TXN_DATE_FK := x_date;
237 x_SRC_CREAT_DATE_FK := x_date;
238 x_LST_ACCPT_DATE_FK := x_date;
239 x_NEED_BY_DATE_FK := x_date;
240 x_PROMISED_DATE_FK := x_date;
241 x_EXPCT_RCV_DATE_FK := x_date;
242 x_SHIPPED_TO_DATE_FK := x_date;
243
244 x_total := 3 + x_total
245 + ceil (x_TXN_DATE_FK + 1)
246 + ceil (x_PARNT_TXN_DATE_FK + 1)
247 + ceil (x_SRC_CREAT_DATE_FK + 1)
248 + ceil (x_LST_ACCPT_DATE_FK + 1)
249 + ceil (x_NEED_BY_DATE_FK + 1)
250 + ceil (x_PROMISED_DATE_FK + 1)
251 + ceil (x_EXPCT_RCV_DATE_FK + 1)
252 + ceil (x_SHIPPED_TO_DATE_FK + 1);
253
254 -- all calculated numbers
255
256 x_QTY_RETURN_TO_RECEIVING := x_float;
257 x_QTY_REJECT := x_float;
258 x_QTY_RECEIVED := x_float;
259 x_QTY_DELIVER := x_float;
260 x_QTY_ACCEPT := x_float;
261 x_PRICE_T := x_float;
262 x_PRICE_G := x_float;
263 x_NUM_DAYS_TO_FULL_DEL := x_float;
264 x_QTY_TXN_NET := x_float;
265 x_QTY_TXN := x_float;
266 x_QTY_TRANSFER := x_float;
267 x_QTY_RETURN_TO_VENDOR := x_float;
268
269 x_total := x_total
270 + ceil (x_QTY_RETURN_TO_RECEIVING + 1)
271 + ceil (x_QTY_REJECT + 1)
272 + ceil (x_QTY_RECEIVED + 1)
273 + ceil (x_QTY_DELIVER + 1)
274 + ceil (x_QTY_ACCEPT + 1)
275 + ceil (x_PRICE_T + 1)
276 + ceil (x_PRICE_G + 1)
277 + ceil (x_NUM_DAYS_TO_FULL_DEL + 1)
278 + ceil (x_QTY_TXN_NET + 1)
279 + ceil (x_QTY_TXN + 1)
280 + ceil (x_QTY_TRANSFER + 1)
281 + ceil (x_QTY_RETURN_TO_VENDOR + 1);
282
283 -------------------------------------------------------------
284
285 OPEN c_1;
286 FETCH c_1 INTO x_RCV_TXN_PK, x_TXN_TYPE_FK,
287 x_DESTIN_TYPE_FK, x_RCV_LOCATION_FK, x_DELIV_LOCATION_FK,
288 x_LOCATOR_FK, x_organization_id, x_subinventory,
289 x_DELIVER_TO_FK, x_SUBST_UNORD_FK, x_INSPECT_STATUS_FK,
290 x_INSPECT_QUAL_FK, x_RECEIVE_EXCEP_FK, x_USER_ENTERED_FK,
291 x_TXN_REASON_FK, x_VENDOR_LOT_NUM, x_RMA_REFERENCE,
292 x_TXN_COMMENTS;
293 CLOSE c_1;
294
295 x_LOCATOR_FK := x_LOCATOR_FK + x_organization_id + x_subinventory;
296
297 x_total := x_total
298 + NVL (ceil(x_RCV_TXN_PK + 1), 0)
299 + NVL (ceil(x_TXN_TYPE_FK + 1), 0)
300 + NVL (ceil(x_DESTIN_TYPE_FK + 1), 0)
301 + NVL (ceil(x_RCV_LOCATION_FK + 1), 0)
302 + NVL (ceil(x_DELIV_LOCATION_FK + 1), 0)
303 + NVL (ceil(x_LOCATOR_FK + 1), 0)
304 + NVL (ceil(x_DELIVER_TO_FK + 1), 0)
305 + NVL (ceil(x_SUBST_UNORD_FK + 1), 0)
306 + NVL (ceil(x_INSPECT_STATUS_FK + 1), 0)
307 + NVL (ceil(x_INSPECT_QUAL_FK + 1), 0)
308 + NVL (ceil(x_RECEIVE_EXCEP_FK + 1), 0)
309 + NVL (ceil(x_USER_ENTERED_FK + 1), 0)
310 + NVL (ceil(x_TXN_REASON_FK + 1), 0)
311 + NVL (ceil(x_VENDOR_LOT_NUM + 1), 0)
312 + NVL (ceil(x_RMA_REFERENCE + 1), 0)
313 + NVL (ceil(x_TXN_COMMENTS + 1), 0);
314
315
316 OPEN c_1A;
317 FETCH c_1A INTO x_ITEM_REVISION_FK, x_PACKING_SLIP;
318 CLOSE c_1A;
319
320 x_total := x_total
321 + NVL (ceil(x_ITEM_REVISION_FK + 1), 0)
322 + NVL (ceil(x_PACKING_SLIP + 1), 0);
323
324
325 OPEN c_1B;
326 FETCH c_1B INTO x_RECEIPT_SOURCE_FK, x_AP_TERMS_FK,
327 x_FREIGHT_TERMS_FK, x_SHIPMENT_NUM, x_RECEIPT_NUM_INST,
328 x_SHIP_HDR_COMMENTS, x_WAY_AIRBILL_NUM, x_BILL_OF_LADING;
329 CLOSE c_1B;
330
331 x_total := x_total
332 + NVL (ceil(x_RECEIPT_SOURCE_FK + 1), 0)
333 + NVL (ceil(x_AP_TERMS_FK + 1), 0)
334 + NVL (ceil(x_FREIGHT_TERMS_FK + 1), 0)
335 + NVL (ceil(x_SHIPMENT_NUM + 1), 0)
336 + NVL (ceil(x_RECEIPT_NUM_INST + 1), 0)
337 + NVL (ceil(x_SHIP_HDR_COMMENTS + 1), 0)
338 + NVL (ceil(x_WAY_AIRBILL_NUM + 1), 0)
339 + NVL (ceil(x_BILL_OF_LADING + 1), 0);
340
341 --------------------------------------------------------
342
343 OPEN c_2;
344 FETCH c_2 INTO x_ITEM_ID, x_ITEM_DESCRIPTION,
345 x_category_id, x_supplier_item_num_fk;
346 CLOSE c_2;
347
348 x_ITEM_REVISION_FK := x_ITEM_ID + x_category_id +
349 x_ITEM_DESCRIPTION;
350 x_edw_base_uom_fk := x_ITEM_ID;
351 x_edw_uom_fk := x_ITEM_ID;
352
353 x_total := x_total
354 + NVL (ceil(x_ITEM_REVISION_FK + 1), 0)
355 + NVL (ceil(x_edw_base_uom_fk + 1), 0)
356 + NVL (ceil(x_edw_uom_fk + 1), 0)
357 + NVL (ceil(x_SUPPLIER_ITEM_NUM_FK + 1), 0);
358
359 -----------------------------------------------
360
361
362 OPEN c_3;
363 FETCH c_3 INTO x_RCV_DEL_TO_ORG_FK;
364 CLOSE c_3;
365
366 x_total := x_total + NVL (ceil(x_RCV_DEL_TO_ORG_FK + 1), 0);
367
368 ---------------------------------------------------
369
370
371 OPEN c_4;
372 FETCH c_4 INTO x_SUPPLIER_SITE_FK, x_organization_id,
373 x_BUYER_FK, x_TXN_CUR_CODE_FK, x_SOURCE_TXN_NUMBER;
374 CLOSE c_4;
375
376 x_supplier_item_num_fk := x_SUPPLIER_SITE_FK;
377 x_SUPPLIER_SITE_FK := x_SUPPLIER_SITE_FK + x_organization_id;
378 x_sup_site_geog_fk := x_SUPPLIER_SITE_FK;
379
380 x_total := x_total
381 + NVL (ceil(x_BUYER_FK + 1), 0)
382 + NVL (ceil(x_SUPPLIER_SITE_FK + 1), 0)
383 + NVL (ceil(x_sup_site_geog_FK + 1), 0)
384 + NVL (ceil(x_supplier_item_num_FK + 1), 0)
385 + NVL (ceil(x_TXN_CUR_CODE_FK + 1), 0)
386 + NVL (ceil(x_SOURCE_TXN_NUMBER + 1), 0);
387
388 --------------------------------------------------------
389
390 OPEN c_5;
391 FETCH c_5 INTO x_RCV_ROUTING_FK;
392 CLOSE c_5;
393 x_total := x_total + NVL (ceil(x_RCV_ROUTING_FK + 1), 0);
394
395 OPEN c_6;
396 FETCH c_6 INTO x_EDW_BASE_UOM_FK;
397 CLOSE c_6;
398
399 x_EDW_UOM_FK := x_EDW_BASE_UOM_FK;
400
401 x_total := x_total + NVL (ceil(x_EDW_BASE_UOM_FK + 1), 0)
402 + NVL (ceil(x_EDW_UOM_FK + 1), 0);
403
404 OPEN c_7;
405 FETCH c_7 INTO x_PO_LINE_TYPE_FK;
406 CLOSE c_7;
407 x_total := x_total + NVL (ceil(x_PO_LINE_TYPE_FK + 1), 0);
408
409 OPEN c_8;
410 FETCH c_8 INTO x_INVOICE_NUM;
411 CLOSE c_8;
412 x_total := x_total + NVL (ceil(x_INVOICE_NUM + 1), 0);
413
414 OPEN c_9;
415 FETCH c_9 INTO x_ITEM_REVISION_FK;
416 CLOSE c_9;
417 x_total := x_total + NVL (ceil(x_ITEM_REVISION_FK + 1), 0);
418
419 OPEN c_10;
420 FETCH c_10 INTO x_supplier_item_num_fk;
421 CLOSE c_10;
422 x_total := x_total + NVL (ceil(x_supplier_item_num_fk + 1), 0);
423
424 --------------------------------------------------------
425
426 -- dbms_output.put_line(' ');
427 -- dbms_output.put_line('The average row length for RCV from source tables is: '
428 -- || to_char(x_total));
429
430 p_avg_row_len := x_total;
431
432 EXCEPTION
433 WHEN OTHERS THEN p_avg_row_len := 0;
434 END; -- procedure est_row_len.
435
436 END;