[Home] [Help]
PACKAGE BODY: APPS.POA_SUPPERF_POPULATE_PKG
Source
1 PACKAGE BODY POA_SUPPERF_POPULATE_PKG AS
2 /* $Header: POASPPPB.pls 115.11 2004/03/04 13:14:31 sriswami ship $ */
3
4 /* populate_fact_table
5 * -------------------
6 * Main procedure to extract neccessary facts from po base tables and
7 * insert into poa_bis_supplier_performance. The data is only extracted if
8 * the last_update_date of po_line_locations_all is between the date
9 * parameters.
10 * Delete the corresponding record in the fact table before inserting
11 * the updated one.
12 */
13 PROCEDURE populate_fact_table(p_start_date IN DATE, p_end_date IN DATE)
14 IS
15
16 CURSOR C_EXTRACT IS
17 SELECT pll.ship_to_location_id,
18 pll.ship_to_organization_id,
19 poh.org_id,
20 pol.item_id,
21 pol.category_id,
22 poh.vendor_id,
23 poh.vendor_site_id,
24 poh.agent_id,
25 pll.line_location_id,
26 pll.quantity,
27 pll.quantity_cancelled,
28 pll.quantity_billed,
29 pll.quantity_rejected,
30 pll.price_override,
31 pll.creation_date,
32 pll.cancel_flag,
33 pll.closed_code,
34 pll.days_early_receipt_allowed,
35 pll.days_late_receipt_allowed,
36 NVL(pll.promised_date, pll.need_by_date) expected_date,
37 gls.currency_code,
38 nvl(poh.rate_type, 'Corporate') rate_type,
39 nvl(poh.rate_date, pll.creation_date) rate_date,
40 poh.rate,
41 pol.unit_meas_lookup_code, /* FPI */
42 por.consigned_consumption_flag por_consigned_consumption_flag,
43 /* FPI */ poh.consigned_consumption_flag poh_consigned_consumption_flag,
44 pll.consigned_flag, /* FPI */
45 poh.shipping_control /* FPJ */
46 FROM po_line_locations_all pll,
47 po_lines_all pol,
48 po_headers_all poh,
49 financials_system_params_all fsp,
50 gl_sets_of_books gls,
51 po_releases_all por /* FPI */
52 WHERE pll.po_line_id = pol.po_line_id
53 AND pll.po_header_id = poh.po_header_id
54 AND NVL(poh.org_id, -999) = NVL(fsp.org_id, -999)
55 AND gls.set_of_books_id = fsp.set_of_books_id
56 AND pll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
57 AND pol.matching_basis = 'QUANTITY'
58 AND NVL(pll.approved_flag, 'N') = 'Y'
59 AND pol.item_id IS NOT NULL
60 AND pll.po_release_id = por.po_release_id(+) /* FPI */
61 AND poa_supperf_api_pkg.get_last_trx_date(pll.line_location_id)
62 BETWEEN p_start_date AND p_end_date;
63
64 v_c_info C_EXTRACT%ROWTYPE;
65 v_quantity_rejected NUMBER;
66 v_primary_uom VARCHAR2(25);
67 v_uom_rate NUMBER;
68 v_quantity_purchased NUMBER;
69 v_total_amount NUMBER;
70 v_receipt_date DATE;
71 v_avg_price NUMBER;
72 v_num_receipts NUMBER;
73 v_quantity_received NUMBER;
74 v_quantity_received_late NUMBER;
75 v_quantity_received_early NUMBER;
76 v_quantity_past_due NUMBER;
77 v_date_dimension DATE;
78 v_purchase_price NUMBER; /* FPI */
79 v_quantity_ordered NUMBER; /* FPI */
80 v_shipment_expected_date DATE; /* FPI */
81 x_progress VARCHAR2(10);
82
83 BEGIN
84
85 POA_LOG.debug_line('populate_fact_table: entered');
86 POA_LOG.debug_line(' ');
87
88 OPEN C_EXTRACT;
89 LOOP
90 BEGIN
91 x_progress := '001';
92 FETCH C_EXTRACT INTO v_c_info;
93 EXIT WHEN C_EXTRACT%NOTFOUND;
94
95 x_progress := '002';
96
97 v_date_dimension := NVL(v_c_info.expected_date,
98 v_c_info.creation_date);
99
100
101 v_primary_uom := poa_supperf_api_pkg.get_primary_uom(
102 v_c_info.item_id,
103 v_c_info.org_id
104 );
105
106 x_progress := '003';
107 v_uom_rate := inv_convert.inv_um_convert(
108 v_c_info.item_id,
109 5,
110 1, NULL, NULL,
111 v_c_info.unit_meas_lookup_code,
112 v_primary_uom
113 );
114
115
116 x_progress := '004';
117 v_avg_price := poa_supperf_api_pkg.get_avg_price(
118 v_c_info.line_location_id,
119 v_c_info.price_override
120 );
121 x_progress := '005';
122
123 if (v_c_info.poh_consigned_consumption_flag='Y'/* FPI */
124 OR v_c_info.por_consigned_consumption_flag='Y')
125 then
126 x_progress := '006';
127 v_receipt_date :=null;
128 v_quantity_received :=null;
129 v_quantity_rejected :=null ;
130 v_num_receipts := null;
131 v_quantity_past_due := null;
132 v_shipment_expected_date := null;
133 x_progress := '007';
134 else
135 x_progress := '008';
136 v_receipt_date :=
137 poa_supperf_api_pkg.get_receipt_date(
138 v_c_info.line_location_id
139 );
140
141 x_progress := '009';
142 v_quantity_received :=
143 poa_supperf_api_pkg.get_rcv_txn_qty(
144 v_c_info.line_location_id,
145 'RECEIVE'
146 );
147
148 x_progress := '010';
149 v_quantity_rejected :=
150 poa_supperf_api_pkg.get_rcv_txn_qty(
151 v_c_info.line_location_id,
152 'REJECT'
153 );
154 -- should watch out for null receipts
155 v_num_receipts := poa_supperf_api_pkg.get_num_receipts(
156 v_c_info.line_location_id
157 );
158
159 x_progress := '011';
160 if (v_c_info.shipping_control='BUYER') then
161 v_quantity_past_due := 0;
162 else
163 v_quantity_past_due :=
164 poa_supperf_api_pkg.get_quantity_past_due(
165 (v_c_info.quantity -
166 v_c_info.quantity_cancelled) *
167 v_uom_rate,
168 v_quantity_received,
169 v_c_info.expected_date,
170 v_c_info.days_late_receipt_allowed
171 );
172 end if;
173
174 v_shipment_expected_date := v_c_info.expected_date;
175 end if;
176
177 if (v_c_info.consigned_flag = 'Y') then
178 x_progress := '012';
179
180 v_purchase_price := null;
181 v_quantity_ordered := null;
182 v_quantity_purchased := null;
183 v_total_amount := null;
184
185 x_progress := '013';
186 else
187 x_progress := '014';
188 v_purchase_price := v_avg_price / v_uom_rate;
189 v_quantity_ordered := v_c_info.quantity * v_uom_rate;
190
191 x_progress := '015';
192 v_quantity_purchased :=
193 poa_supperf_api_pkg.get_quantity_purchased(
194 v_c_info.quantity * v_uom_rate,
195 v_c_info.quantity_billed * v_uom_rate,
196 v_c_info.quantity_cancelled * v_uom_rate,
197 nvl(v_quantity_received,
198 poa_supperf_api_pkg.get_rcv_txn_qty(
199 v_c_info.line_location_id,
200 'RECEIVE'
201 )),
202 v_c_info.cancel_flag,
203 v_c_info.closed_code
204 );
205
206 x_progress := '016';
207 v_total_amount :=
208 poa_supperf_api_pkg.get_total_amount(
209 v_c_info.line_location_id,
210 v_c_info.cancel_flag,
211 v_c_info.closed_code,
212 v_c_info.price_override
213 );
214 end if;
215
216 x_progress := '017';
217
218
219
220 --
221 -- Calculate quantity received early, late and past-due.
222 -- If there's no receipt yet, set the quantity early, late,
223 -- to null and handle those in the reports.
224 --
225 IF v_num_receipts = 0
226 THEN
227 v_quantity_received_late := NULL;
228 v_quantity_received_early := NULL;
229 ELSE
230 x_progress := '018';
231
232 if (v_c_info.poh_consigned_consumption_flag='Y'/* FPI */
233 OR v_c_info.por_consigned_consumption_flag='Y')
234 then
235 v_quantity_received_late := null;
236 v_quantity_received_early :=null;
237 elsif (v_c_info.shipping_control = 'BUYER' ) then
238 v_quantity_received_late := 0;
239 v_quantity_received_early := 0;
240 else
241 x_progress := '019';
242 v_quantity_received_late :=
243 poa_supperf_api_pkg.get_quantity_late(
244 v_c_info.line_location_id,
245 v_c_info.expected_date,
246 v_c_info.days_late_receipt_allowed
247 );
248 x_progress := '020';
249 v_quantity_received_early :=
250 poa_supperf_api_pkg.get_quantity_early(
251 v_c_info.line_location_id,
252 v_c_info.expected_date,
253 v_c_info.days_early_receipt_allowed
254 );
255 end if;
256
257 END IF;
258
259 x_progress := '021';
260
261 --
262 -- Delete coresponding row in the fact table if it exists,
263 -- then insert row.
264 --
265 x_progress := '022';
266 delete_row(v_c_info.line_location_id);
267
268 x_progress := '023';
269 insert_row(v_c_info.line_location_id,
270 v_c_info.ship_to_location_id,
271 v_c_info.ship_to_organization_id,
272 v_c_info.org_id,
273 v_c_info.item_id,
274 v_c_info.category_id,
275 v_c_info.vendor_id,
276 v_c_info.vendor_site_id,
277 v_c_info.agent_id,
278 v_date_dimension,
279 v_quantity_purchased,
280 /* FPI */ v_purchase_price,
281 v_primary_uom,
282 v_c_info.currency_code,
283 v_c_info.rate_type,
284 v_c_info.rate_date,
285 v_c_info.rate,
286 /* FPI */ v_quantity_ordered,
287 v_quantity_received,
288 v_quantity_rejected,
289 v_total_amount,
290 v_num_receipts,
291 v_quantity_received_late,
292 v_quantity_received_early,
293 v_quantity_past_due,
294 v_receipt_date,
295 /* FPI */ v_shipment_expected_date,
296 trunc(v_date_dimension, 'MONTH'),
297 trunc(v_date_dimension, 'Q'),
298 trunc(v_date_dimension, 'YYYY'),
299 fnd_global.user_id,
300 sysdate,
301 sysdate,
302 fnd_global.user_id,
303 fnd_global.login_id,
304 fnd_global.conc_request_id,
305 fnd_global.prog_appl_id,
306 fnd_global.conc_program_id,
307 sysdate
308 );
309 EXCEPTION
310 WHEN OTHERS THEN
311 POA_LOG.put_line('populate_fact_table: ' || x_progress
312 || ' ' || sqlerrm);
313 POA_LOG.put_line(' ');
314
315 POA_LOG.debug_line('populate_fact_table: line_location_id - ' ||
316 to_char(v_c_info.line_location_id));
317 POA_LOG.debug_line(' ');
318 END;
319 END LOOP;
320
321 CLOSE C_EXTRACT;
322
323 EXCEPTION
324 WHEN OTHERS THEN
325 POA_LOG.put_line('populate_fact_table: ' || x_progress
326 || ' ' || sqlerrm);
327 POA_LOG.put_line(' ');
328
329 POA_LOG.debug_line('populate_fact_table: line_location_id - ' ||
330 to_char(v_c_info.line_location_id));
331 POA_LOG.debug_line(' ');
332
333 RAISE;
334
335 END populate_fact_table;
336
337
338
339
340
341 /* delete_row
342 * ----------
343 * This procedure deletes a record from poa_bis_supplier_performace fact
344 * table for the given shipment.
345 */
346 PROCEDURE delete_row(p_line_location_id NUMBER)
347 IS
348 x_progress VARCHAR2(3);
349 BEGIN
350 x_progress := '001';
351
352 DELETE FROM poa_bis_supplier_performance
353 WHERE po_shipment_id = p_line_location_id;
354
355 EXCEPTION
356 WHEN OTHERS THEN
357 POA_LOG.put_line('delete_row: ' || x_progress
358 || ' ' || sqlerrm);
359 POA_LOG.put_line(' ');
360 RAISE;
361
362 END delete_row;
363
364
365
366
367 /* insert_row
368 * ----------
369 * This procedure simply inserts a single record into the supplier
370 * performance fact table.
371 */
372 PROCEDURE insert_row(p_shipment_id NUMBER,
373 p_ship_to_location_id NUMBER,
374 p_ship_to_organization_id NUMBER,
375 p_org_id NUMBER,
376 p_item_id NUMBER,
377 p_category_id NUMBER,
378 p_supplier_id NUMBER,
379 p_supplier_site_id NUMBER,
380 p_buyer_id NUMBER,
381 p_date_dimension DATE,
382 p_quantity_purchased NUMBER,
383 p_purchase_price NUMBER,
384 p_primary_uom VARCHAR2,
385 p_currency_code VARCHAR2,
386 p_rate_type VARCHAR2,
387 p_rate_date DATE,
388 p_rate NUMBER,
389 p_quantity_ordered NUMBER,
390 p_quantity_received NUMBER,
391 p_quantity_rejected NUMBER,
392 p_amount NUMBER,
393 p_number_of_receipts NUMBER,
394 p_quantity_received_late NUMBER,
395 p_quantity_received_early NUMBER,
396 p_quantity_past_due NUMBER,
397 p_first_receipt_date DATE,
398 p_shipment_expected_date DATE,
399 p_month_bucket DATE,
400 p_quarter_bucket DATE,
401 p_year_bucket DATE,
402 p_created_by NUMBER,
403 p_creation_date DATE,
404 p_last_update_date DATE,
405 p_last_updated_by NUMBER,
406 p_last_update_login NUMBER,
407 p_request_id NUMBER,
408 p_program_application_id NUMBER,
409 p_program_id NUMBER,
410 p_program_update_date DATE)
411 IS
412 x_progress VARCHAR2(3);
413 BEGIN
414 x_progress := '001';
415
416 INSERT INTO poa_bis_supplier_performance (
417 PO_SHIPMENT_ID ,
418 SHIP_TO_LOCATION_ID ,
419 SHIP_TO_ORGANIZATION_ID ,
420 ORG_ID ,
421 ITEM_ID ,
422 category_id,
423 SUPPLIER_ID ,
424 SUPPLIER_SITE_ID ,
425 BUYER_ID ,
426 DATE_DIMENSION ,
427 QUANTITY_PURCHASED ,
428 PURCHASE_PRICE ,
429 PRIMARY_UOM ,
430 CURRENCY_CODE ,
431 RATE_TYPE ,
432 RATE_DATE ,
433 RATE ,
434 QUANTITY_ORDERED ,
435 QUANTITY_RECEIVED ,
436 QUANTITY_REJECTED ,
437 AMOUNT ,
438 NUMBER_OF_RECEIPTS ,
439 QUANTITY_RECEIVED_LATE ,
440 QUANTITY_RECEIVED_EARLY ,
441 QUANTITY_PAST_DUE ,
442 FIRST_RECEIPT_DATE ,
443 SHIPMENT_EXPECTED_DATE ,
444 MONTH_BUCKET ,
445 QUARTER_BUCKET ,
446 YEAR_BUCKET ,
447 CREATED_BY ,
448 CREATION_DATE ,
449 LAST_UPDATE_DATE ,
450 LAST_UPDATED_BY ,
451 LAST_UPDATE_LOGIN ,
452 REQUEST_ID ,
453 PROGRAM_APPLICATION_ID ,
454 PROGRAM_ID ,
455 PROGRAM_UPDATE_DATE
456 )
457 VALUES
458 (
459 p_shipment_id,
460 p_ship_to_location_id,
461 p_ship_to_organization_id,
462 p_org_id,
463 p_item_id,
464 p_category_id,
465 p_supplier_id,
466 p_supplier_site_id,
467 p_buyer_id,
468 p_date_dimension,
469 p_quantity_purchased,
470 p_purchase_price,
471 p_primary_uom,
472 p_currency_code,
473 p_rate_type,
474 p_rate_date,
475 p_rate,
476 p_quantity_ordered,
477 p_quantity_received,
478 p_quantity_rejected,
479 p_amount,
480 p_number_of_receipts,
481 p_quantity_received_late,
482 p_quantity_received_early,
483 p_quantity_past_due,
484 p_first_receipt_date,
485 p_shipment_expected_date,
486 p_month_bucket,
487 p_quarter_bucket,
488 p_year_bucket,
489 p_created_by,
490 p_creation_date,
491 p_last_update_date,
492 p_last_updated_by,
493 p_last_update_login,
494 p_request_id,
495 p_program_application_id,
496 p_program_id,
497 p_program_update_date
498 );
499
500 EXCEPTION
501 WHEN OTHERS THEN
502 POA_LOG.put_line('insert_row: ' || x_progress
503 || ' ' || sqlerrm);
504 POA_LOG.put_line(' ');
505 RAISE;
506
507 END insert_row;
508
509 END POA_SUPPERF_POPULATE_PKG;
510
511
512