[Home] [Help]
PACKAGE BODY: APPS.POA_EDW_SUP_PERF_F_C
Source
1 Package Body POA_EDW_SUP_PERF_F_C AS
2 /* $Header: poafpspb.pls 120.0 2005/06/02 02:02:22 appldev noship $ */
3 g_push_from_date Date:=Null;
4 g_push_to_date Date:=Null;
5 g_row_count Number:=0;
6 g_errbuf VARCHAR2(2000) := NULL;
7 g_retcode VARCHAR2(200) := NULL;
8 g_seq_id NUMBER:=0;
9
10 -- ---------------------------------
11 -- PRIVATE PROCEDURES AND FUNCTIONS
12 -- ---------------------------------
13
14 -----------------------------------------------------------
15 -- PROCEDURE TRUNCATE_INC
16 -----------------------------------------------------------
17
18 PROCEDURE TRUNCATE_INC
19 IS
20
21 l_poa_schema VARCHAR2(30);
22 l_stmt VARCHAR2(200);
23 l_status VARCHAR2(30);
24 l_industry VARCHAR2(30);
25
26 BEGIN
27
28 IF (FND_INSTALLATION.GET_APP_INFO('POA', l_status, l_industry, l_poa_schema)) THEN
29 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema ||'.POA_EDW_SUP_PERF_INC';
30 EXECUTE IMMEDIATE l_stmt;
31 END IF;
32
33 END;
34
35
36 -------------------------------------------------------------
37 -- PROCEDURE INSERT_MISSING_RATES
38 -------------------------------------------------------------
39 --Identify records that have missing rates and insert them in a temp table
40
41 PROCEDURE INSERT_MISSING_RATES
42 IS
43 BEGIN
44 INSERT INTO poa_edw_sup_perf_inc(primary_key)
45 SELECT TO_NUMBER(SUBSTR(sup_perf_pk, 1, INSTR(sup_perf_pk, '-' )-1))
46 FROM POA_EDW_SUP_PERF_FSTG
47 where COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR
48 COLLECTION_STATUS = 'INVALID CURRENCY';
49 IF (sql%rowcount > 0) THEN
50 g_retcode := 1;
51 END IF;
52
53 -- Generates "Warning" message in the Status column
54 -- of Concurrent Manager "Requests" table
55 edw_log.put_line(' ');
56 edw_log.put_line('INSERTING ' || to_char(sql%rowcount) ||
57 ' rows into poa_edw_sup_perf_inc table');
58 END;
59
60 -----------------------------------------------------------
61 -- PROCEDURE DELETE_STG_MISSING_RATES
62 -----------------------------------------------------------
63 -- Procedure to remove rows from local staging table that have
64 -- collection status of either rate not available or invalid currency.
65 PROCEDURE DELETE_STG_MISSING_RATES
66 IS
67 BEGIN
68 DELETE FROM POA_EDW_SUP_PERF_FSTG
69 WHERE COLLECTION_STATUS = 'RATE NOT AVAILABLE'
70 OR COLLECTION_STATUS = 'INVALID CURRENCY'
71 AND INSTANCE_FK = (SELECT INSTANCE_CODE
72 FROM EDW_LOCAL_INSTANCE);
73 END;
74
75
76
77 -----------------------------------------------------------
78 --PROCEDURE PUSH_TO_LOCAL
79 -----------------------------------------------------------
80
81 FUNCTION PUSH_TO_LOCAL(p_view_id NUMBER, p_seq_id NUMBER) RETURN NUMBER IS
82
83 BEGIN
84
85 -- ------------------------------------------------
86 -- We set the COLLECTION_STATUS to 'LOCAL READY'.
87 -- In case of source=target, we need to separate
88 -- out the records in progress vs the records which
89 -- is ready to be picked up by collection enginee.
90 -- In our case, we consider the records to be in
91 -- progress until the push_to_local procedure for
92 -- all view types has completed successfully.
93 -- ------------------------------------------------
94
95 /* Get the profile option for calculating the best_price (target_price).
96 This could have been done inside the API poa_edw_supperf.find_best_price,
97 but it would violate the PRAGMA restriction and the API colud not compile.
98
99 Instead, we check the profile option here in the PUSH.
100 When the value is 'Yes' (the first part of the IF statment), then we SELECT
101 target_price from the source view which then calls the (expensive) API
102 poa_edw_supperf.find_best_price.
103 Otherwise, when the value is 'No' (the second part of the IF statment),
104 we populate target_price by NULL, the API won't be called in the
105 fact source view */
106
107
108 IF(fnd_profile.value('POA_TARGET_PRICE_TXN') = 'Y') then
109
110 edw_log.put_line('***The best price is calculated for target prices***');
111
112 Insert Into POA_EDW_SUP_PERF_FSTG(
113 DUNS_FK,
114 UNSPSC_FK,
115 SIC_CODE_FK,
116 AMT_PURCHASED_G,
117 AMT_PURCHASED_T,
118 APPROVAL_DATE_FK,
119 AP_TERMS_FK,
120 BUYER_FK,
121 CLOSED_CODE_FK,
122 CONTRACT_NUM,
123 CREATION_DATE_FK,
124 DATE_DIM_FK,
125 DAYS_EARLY_REC,
126 DAYS_LATE_REC,
127 EDW_BASE_UOM_FK,
128 EDW_UOM_FK,
129 FIRST_REC_DATE_FK,
130 INSTANCE_FK,
131 INVOICE_DATE_FK,
132 IPV_G,
133 IPV_T,
134 ITEM_FK,
135 LIST_PRICE_G,
136 LIST_PRICE_T,
137 LST_ACCPT_DATE_FK,
138 MARKET_PRICE_G,
139 MARKET_PRICE_T,
140 NEED_BY_DATE_FK,
141 NUM_DAYS_TO_INVOICE,
142 NUM_EARLY_RECEIPT,
143 NUM_LATE_RECEIPT,
144 NUM_ONTIME_AFTDUE,
145 NUM_ONTIME_BEFDUE,
146 NUM_ONTIME_ONDUE,
147 NUM_RECEIPT_LINES,
148 NUM_SUBS_RECEIPT,
149 PO_LINE_TYPE_FK,
150 PO_NUMBER,
151 PRICE_G,
152 PRICE_T,
153 PRICE_TYPE_FK,
154 PROMISED_DATE_FK,
155 PURCH_CLASS_FK,
156 QTY_ACCEPTED_B,
157 QTY_CANCELLED_B,
158 QTY_DELIVERED_B,
159 QTY_EARLY_RECEIPT_B,
160 QTY_LATE_RECEIPT_B,
161 QTY_ONTIME_AFTDUE_B,
162 QTY_ONTIME_BEFDUE_B,
163 QTY_ONTIME_ONDUE_B,
164 QTY_ORDERED_B,
165 QTY_PAST_DUE_B,
166 QTY_RECEIVED_B,
167 QTY_RECEIVED_TOL,
168 QTY_REJECTED_B,
169 QTY_SHIPPED_B,
170 QTY_SUBS_RECEIPT_B,
171 RCV_CLOSE_TOL,
172 RELEASE_NUM,
173 SHIP_LOCATION_FK,
174 SHIP_TO_ORG_FK,
175 SUPPLIER_ITEM_FK,
176 SUPPLIER_SITE_FK,
177 SUP_PERF_PK,
178 SUP_SITE_GEOG_FK,
179 TARGET_PRICE_G,
180 TARGET_PRICE_T,
181 TXN_CUR_CODE_FK,
182 USER_ATTRIBUTE1,
183 USER_ATTRIBUTE2,
184 USER_ATTRIBUTE3,
185 USER_ATTRIBUTE4,
186 USER_ATTRIBUTE5,
187 USER_ATTRIBUTE6,
188 USER_ATTRIBUTE7,
189 USER_ATTRIBUTE8,
190 USER_ATTRIBUTE9,
191 USER_ATTRIBUTE10,
192 USER_ATTRIBUTE11,
193 USER_ATTRIBUTE12,
194 USER_ATTRIBUTE13,
195 USER_ATTRIBUTE14,
196 USER_ATTRIBUTE15,
197 USER_FK1,
198 USER_FK2,
199 USER_FK3,
200 USER_FK4,
201 USER_FK5,
202 USER_MEASURE1,
203 USER_MEASURE2,
204 USER_MEASURE3,
205 USER_MEASURE4,
206 USER_MEASURE5,
207 OPERATION_CODE,
208 COLLECTION_STATUS)
209 select
210 NVL(DUNS_FK, 'NA_EDW'),
211 NVL(UNSPSC_FK, 'NA_EDW'),
212 NVL(SIC_CODE_FK, 'NA_EDW'),
213 AMT_PURCHASED_G,
214 AMT_PURCHASED_T,
215 NVL(APPROVAL_DATE_FK,'NA_EDW'),
216 NVL(AP_TERMS_FK,'NA_EDW'),
217 NVL(BUYER_FK,'NA_EDW'),
218 NVL(CLOSED_CODE_FK,'NA_EDW'),
219 CONTRACT_NUM,
220 NVL(CREATION_DATE_FK,'NA_EDW'),
221 NVL(DATE_DIM_FK,'NA_EDW'),
222 DAYS_EARLY_REC,
223 DAYS_LATE_REC,
224 NVL(EDW_BASE_UOM_FK,'NA_EDW'),
225 NVL(EDW_UOM_FK,'NA_EDW'),
226 NVL(FIRST_REC_DATE_FK,'NA_EDW'),
227 NVL(INSTANCE_FK,'NA_EDW'),
228 NVL(INVOICE_DATE_FK,'NA_EDW'),
229 IPV_G,
230 IPV_T,
231 NVL(ITEM_FK,'NA_EDW'),
232 LIST_PRICE_G,
233 LIST_PRICE_T,
234 NVL(LST_ACCPT_DATE_FK,'NA_EDW'),
235 MARKET_PRICE_G,
236 MARKET_PRICE_T,
237 NVL(NEED_BY_DATE_FK,'NA_EDW'),
238 NUM_DAYS_TO_INVOICE,
239 NUM_EARLY_RECEIPT,
240 NUM_LATE_RECEIPT,
241 NUM_ONTIME_AFTDUE,
242 NUM_ONTIME_BEFDUE,
243 NUM_ONTIME_ONDUE,
244 NUM_RECEIPT_LINES,
245 NUM_SUBS_RECEIPT,
246 NVL(PO_LINE_TYPE_FK,'NA_EDW'),
247 PO_NUMBER,
248 PRICE_G,
249 PRICE_T,
250 NVL(PRICE_TYPE_FK,'NA_EDW'),
251 NVL(PROMISED_DATE_FK,'NA_EDW'),
252 NVL(PURCH_CLASS_FK,'NA_EDW'),
253 QTY_ACCEPTED_B,
254 QTY_CANCELLED_B,
255 QTY_DELIVERED_B,
256 QTY_EARLY_RECEIPT_B,
257 QTY_LATE_RECEIPT_B,
258 QTY_ONTIME_AFTDUE_B,
259 QTY_ONTIME_BEFDUE_B,
260 QTY_ONTIME_ONDUE_B,
261 QTY_ORDERED_B,
262 QTY_PAST_DUE_B,
263 QTY_RECEIVED_B,
264 QTY_RECEIVED_TOL,
265 QTY_REJECTED_B,
266 QTY_SHIPPED_B,
267 QTY_SUBS_RECEIPT_B,
268 RCV_CLOSE_TOL,
269 RELEASE_NUM,
270 NVL(SHIP_LOCATION_FK,'NA_EDW'),
271 NVL(SHIP_TO_ORG_FK,'NA_EDW'),
272 NVL(SUPPLIER_ITEM_FK,'NA_EDW'),
273 NVL(SUPPLIER_SITE_FK,'NA_EDW'),
274 SUP_PERF_PK,
275 NVL(SUP_SITE_GEOG_FK,'NA_EDW'),
276 TARGET_PRICE_G,
277 TARGET_PRICE_T,
278 NVL(TXN_CUR_CODE_FK,'NA_EDW'),
279 USER_ATTRIBUTE1,
280 USER_ATTRIBUTE2,
281 USER_ATTRIBUTE3,
282 USER_ATTRIBUTE4,
283 USER_ATTRIBUTE5,
284 USER_ATTRIBUTE6,
285 USER_ATTRIBUTE7,
286 USER_ATTRIBUTE8,
287 USER_ATTRIBUTE9,
288 USER_ATTRIBUTE10,
289 USER_ATTRIBUTE11,
290 USER_ATTRIBUTE12,
291 USER_ATTRIBUTE13,
292 USER_ATTRIBUTE14,
293 USER_ATTRIBUTE15,
294 NVL(USER_FK1,'NA_EDW'),
295 NVL(USER_FK2,'NA_EDW'),
296 NVL(USER_FK3,'NA_EDW'),
297 NVL(USER_FK4,'NA_EDW'),
298 NVL(USER_FK5,'NA_EDW'),
299 USER_MEASURE1,
300 USER_MEASURE2,
301 USER_MEASURE3,
302 USER_MEASURE4,
303 USER_MEASURE5,
304 NULL, -- OPERATION_CODE
305 COLLECTION_STATUS
306 FROM POA_EDW_SUPPLIER_PERFORM_FCV
307 WHERE view_id = p_view_id
308 AND seq_id = p_seq_id;
309
310 ELSE
311
312 edw_log.put_line('***The target prices are set to NULL***');
313
314 Insert Into POA_EDW_SUP_PERF_FSTG(
315 DUNS_FK,
316 UNSPSC_FK,
317 SIC_CODE_FK,
318 AMT_PURCHASED_G,
319 AMT_PURCHASED_T,
320 APPROVAL_DATE_FK,
321 AP_TERMS_FK,
322 BUYER_FK,
323 CLOSED_CODE_FK,
324 CONTRACT_NUM,
325 CREATION_DATE_FK,
326 DATE_DIM_FK,
327 DAYS_EARLY_REC,
328 DAYS_LATE_REC,
329 EDW_BASE_UOM_FK,
330 EDW_UOM_FK,
331 FIRST_REC_DATE_FK,
332 INSTANCE_FK,
333 INVOICE_DATE_FK,
334 IPV_G,
335 IPV_T,
336 ITEM_FK,
337 LIST_PRICE_G,
338 LIST_PRICE_T,
339 LST_ACCPT_DATE_FK,
340 MARKET_PRICE_G,
341 MARKET_PRICE_T,
342 NEED_BY_DATE_FK,
343 NUM_DAYS_TO_INVOICE,
344 NUM_EARLY_RECEIPT,
345 NUM_LATE_RECEIPT,
346 NUM_ONTIME_AFTDUE,
347 NUM_ONTIME_BEFDUE,
348 NUM_ONTIME_ONDUE,
349 NUM_RECEIPT_LINES,
350 NUM_SUBS_RECEIPT,
351 PO_LINE_TYPE_FK,
352 PO_NUMBER,
353 PRICE_G,
354 PRICE_T,
355 PRICE_TYPE_FK,
356 PROMISED_DATE_FK,
357 PURCH_CLASS_FK,
358 QTY_ACCEPTED_B,
359 QTY_CANCELLED_B,
360 QTY_DELIVERED_B,
361 QTY_EARLY_RECEIPT_B,
362 QTY_LATE_RECEIPT_B,
363 QTY_ONTIME_AFTDUE_B,
364 QTY_ONTIME_BEFDUE_B,
365 QTY_ONTIME_ONDUE_B,
366 QTY_ORDERED_B,
367 QTY_PAST_DUE_B,
368 QTY_RECEIVED_B,
369 QTY_RECEIVED_TOL,
370 QTY_REJECTED_B,
371 QTY_SHIPPED_B,
372 QTY_SUBS_RECEIPT_B,
373 RCV_CLOSE_TOL,
374 RELEASE_NUM,
375 SHIP_LOCATION_FK,
376 SHIP_TO_ORG_FK,
377 SUPPLIER_ITEM_FK,
378 SUPPLIER_SITE_FK,
379 SUP_PERF_PK,
380 SUP_SITE_GEOG_FK,
381 TARGET_PRICE_G,
382 TARGET_PRICE_T,
383 TXN_CUR_CODE_FK,
384 USER_ATTRIBUTE1,
385 USER_ATTRIBUTE2,
386 USER_ATTRIBUTE3,
387 USER_ATTRIBUTE4,
388 USER_ATTRIBUTE5,
389 USER_ATTRIBUTE6,
390 USER_ATTRIBUTE7,
391 USER_ATTRIBUTE8,
392 USER_ATTRIBUTE9,
393 USER_ATTRIBUTE10,
394 USER_ATTRIBUTE11,
395 USER_ATTRIBUTE12,
396 USER_ATTRIBUTE13,
397 USER_ATTRIBUTE14,
398 USER_ATTRIBUTE15,
399 USER_FK1,
400 USER_FK2,
401 USER_FK3,
402 USER_FK4,
403 USER_FK5,
404 USER_MEASURE1,
405 USER_MEASURE2,
406 USER_MEASURE3,
407 USER_MEASURE4,
408 USER_MEASURE5,
409 OPERATION_CODE,
410 COLLECTION_STATUS)
411 select
412 NVL(DUNS_FK, 'NA_EDW'),
413 NVL(UNSPSC_FK, 'NA_EDW'),
414 NVL(SIC_CODE_FK, 'NA_EDW'),
415 AMT_PURCHASED_G,
416 AMT_PURCHASED_T,
417 NVL(APPROVAL_DATE_FK,'NA_EDW'),
418 NVL(AP_TERMS_FK,'NA_EDW'),
419 NVL(BUYER_FK,'NA_EDW'),
420 NVL(CLOSED_CODE_FK,'NA_EDW'),
421 CONTRACT_NUM,
422 NVL(CREATION_DATE_FK,'NA_EDW'),
423 NVL(DATE_DIM_FK,'NA_EDW'),
424 DAYS_EARLY_REC,
425 DAYS_LATE_REC,
426 NVL(EDW_BASE_UOM_FK,'NA_EDW'),
427 NVL(EDW_UOM_FK,'NA_EDW'),
428 NVL(FIRST_REC_DATE_FK,'NA_EDW'),
429 NVL(INSTANCE_FK,'NA_EDW'),
430 NVL(INVOICE_DATE_FK,'NA_EDW'),
431 IPV_G,
432 IPV_T,
433 NVL(ITEM_FK,'NA_EDW'),
434 LIST_PRICE_G,
435 LIST_PRICE_T,
436 NVL(LST_ACCPT_DATE_FK,'NA_EDW'),
437 MARKET_PRICE_G,
438 MARKET_PRICE_T,
439 NVL(NEED_BY_DATE_FK,'NA_EDW'),
440 NUM_DAYS_TO_INVOICE,
441 NUM_EARLY_RECEIPT,
442 NUM_LATE_RECEIPT,
443 NUM_ONTIME_AFTDUE,
444 NUM_ONTIME_BEFDUE,
445 NUM_ONTIME_ONDUE,
446 NUM_RECEIPT_LINES,
447 NUM_SUBS_RECEIPT,
448 NVL(PO_LINE_TYPE_FK,'NA_EDW'),
449 PO_NUMBER,
450 PRICE_G,
451 PRICE_T,
452 NVL(PRICE_TYPE_FK,'NA_EDW'),
453 NVL(PROMISED_DATE_FK,'NA_EDW'),
454 NVL(PURCH_CLASS_FK,'NA_EDW'),
455 QTY_ACCEPTED_B,
456 QTY_CANCELLED_B,
457 QTY_DELIVERED_B,
458 QTY_EARLY_RECEIPT_B,
459 QTY_LATE_RECEIPT_B,
460 QTY_ONTIME_AFTDUE_B,
461 QTY_ONTIME_BEFDUE_B,
462 QTY_ONTIME_ONDUE_B,
463 QTY_ORDERED_B,
464 QTY_PAST_DUE_B,
465 QTY_RECEIVED_B,
466 QTY_RECEIVED_TOL,
467 QTY_REJECTED_B,
468 QTY_SHIPPED_B,
469 QTY_SUBS_RECEIPT_B,
470 RCV_CLOSE_TOL,
471 RELEASE_NUM,
472 NVL(SHIP_LOCATION_FK,'NA_EDW'),
473 NVL(SHIP_TO_ORG_FK,'NA_EDW'),
474 NVL(SUPPLIER_ITEM_FK,'NA_EDW'),
475 NVL(SUPPLIER_SITE_FK,'NA_EDW'),
476 SUP_PERF_PK,
477 NVL(SUP_SITE_GEOG_FK,'NA_EDW'),
478 to_number(NULL), --TARGET_PRICE_G ,
479 to_number(NULL), --TARGET_PRICE_T,
480 NVL(TXN_CUR_CODE_FK,'NA_EDW'),
481 USER_ATTRIBUTE1,
482 USER_ATTRIBUTE2,
483 USER_ATTRIBUTE3,
484 USER_ATTRIBUTE4,
485 USER_ATTRIBUTE5,
486 USER_ATTRIBUTE6,
487 USER_ATTRIBUTE7,
488 USER_ATTRIBUTE8,
489 USER_ATTRIBUTE9,
490 USER_ATTRIBUTE10,
491 USER_ATTRIBUTE11,
492 USER_ATTRIBUTE12,
493 USER_ATTRIBUTE13,
494 USER_ATTRIBUTE14,
495 USER_ATTRIBUTE15,
496 NVL(USER_FK1,'NA_EDW'),
497 NVL(USER_FK2,'NA_EDW'),
498 NVL(USER_FK3,'NA_EDW'),
499 NVL(USER_FK4,'NA_EDW'),
500 NVL(USER_FK5,'NA_EDW'),
501 USER_MEASURE1,
502 USER_MEASURE2,
503 USER_MEASURE3,
504 USER_MEASURE4,
505 USER_MEASURE5,
506 NULL, -- OPERATION_CODE
507 COLLECTION_STATUS
508 FROM POA_EDW_SUPPLIER_PERFORM_FCV
509 WHERE view_id = p_view_id
510 AND seq_id = p_seq_id;
511
512 END IF;
513
514 RETURN(sql%rowcount);
515
516
517 EXCEPTION
518 WHEN OTHERS THEN
519 g_errbuf:=sqlerrm;
520 g_retcode:=sqlcode;
521 RETURN(-1);
522
523 END;
524
525
526 -----------------------------------------------------------
527 -- PROCEDURE DELETE_DUPLICATES
528 -----------------------------------------------------------
529
530 PROCEDURE DELETE_DUPLICATES IS
531
532 -- Cursor to delete duplicates
533 CURSOR Dup_Rec IS
534 SELECT primary_key
535 FROM poa_edw_sup_perf_inc
536 ORDER BY primary_key
537 FOR UPDATE;
538
539 v_prev_id NUMBER;
540 v_cur_id NUMBER;
541
542 BEGIN
543 OPEN Dup_Rec;
544
545 LOOP
546
547 FETCH Dup_Rec INTO v_cur_id;
548 exit when Dup_Rec % NOTFOUND;
549
550 -- Check if the PK already exists
551 IF (v_prev_id = v_cur_id) THEN
552 DELETE FROM poa_edw_sup_perf_inc
553 WHERE CURRENT OF Dup_Rec;
554 ELSE
555 v_prev_id := v_cur_id;
556 END IF;
557 END LOOP;
558
559 close Dup_Rec;
560 EXCEPTION
561 WHEN OTHERS THEN
562 IF Dup_Rec%ISOPEN THEN
563 close Dup_Rec;
564 END IF;
565 END;
566
567 -----------------------------------------------------------
568 -- FUNCTION INSERT_RCPT
569 -----------------------------------------------------------
570
571 FUNCTION INSERT_RCPT(p_seq_id IN NUMBER)
572 RETURN NUMBER
573 IS
574
575 l_count NUMBER;
576 BEGIN
577
578 insert into poa_edw_sup_perf_inc(primary_key, seq_id)
579 select rcv.po_line_location_id, p_seq_id
580 from rcv_transactions rcv, po_line_locations_all pll
581 where rcv.po_line_location_id = pll.line_location_id
582 and rcv.last_update_date between g_push_from_date and
583 g_push_to_date
584 group by rcv.po_line_location_id
585 having max(rcv.last_update_date) between g_push_from_date
586 and g_push_to_date;
587
588 l_count := sql%rowcount;
589
590 return l_count;
591
592 EXCEPTION
593 WHEN OTHERS THEN
594 g_errbuf:=sqlerrm;
595 g_retcode:=sqlcode;
596 RETURN(-1);
597
598 END;
599
600
601 ---------------------------------------------------
602 -- FUNCTION IDENTIFY_CHANGE1
603 ---------------------------------------------------
604
605 FUNCTION IDENTIFY_CHANGE1(p_view_id IN NUMBER,
606 p_count OUT NOCOPY NUMBER)
607 RETURN NUMBER
608 IS
609
610 l_seq_id NUMBER := -1;
611
612 BEGIN
613
614 p_count := 0;
615 select poa_edw_sup_perf_inc_s.nextval into l_seq_id from dual;
616
617 /** Update the seq_id for records that had missing currency rates in
618 the earlier PUSH. We need to repush these records again
619 **/
620
621 UPDATE poa_edw_sup_perf_inc
622 SET seq_id = l_seq_id
623 WHERE seq_id IS NULL;
624
625 p_count := sql%rowcount;
626 edw_log.put_line( 'Updated ' || p_count || ' records');
627
628 INSERT
629 into poa_edw_sup_perf_inc(primary_key, seq_id)
630 SELECT pll.line_location_id, l_seq_id
631 FROM po_lines_all pol,
632 po_line_locations_all pll,
633 po_headers_all poh
634 WHERE poh.po_header_id = pll.po_header_id
635 AND pol.po_line_id = pll.po_line_id
636 AND (greatest(pol.last_update_date,pll.last_update_date,
637 poh.last_update_date)
638 between g_push_from_date and g_push_to_date
639 OR nvl(pll.promised_date, pll.need_by_date) +
640 nvl(pll.days_late_receipt_allowed, 0)
641 between g_push_from_date and g_push_to_date);
642
643 p_count := p_count + sql%rowcount;
644
645 Commit;
646
647 RETURN(l_seq_id);
648
649 EXCEPTION
650 WHEN OTHERS THEN
651 g_errbuf:=sqlerrm;
652 g_retcode:=sqlcode;
653 RETURN(-1);
654
655 END;
656
657
658 -- ---------------------------------
659 -- PUBLIC PROCEDURES
660 -- ---------------------------------
661
662 -----------------------------------------------------------
663 -- PROCEDURE PUSH
664 -----------------------------------------------------------
665
666 PROCEDURE PUSH(Errbuf in out NOCOPY Varchar2,
667 Retcode in out NOCOPY Varchar2,
668 p_from_date IN Varchar2,
669 p_to_date IN Varchar2) IS
670
671
672 l_fact_name Varchar2(30) :='POA_EDW_SUP_PERF_F';
673 l_staging_table Varchar2(30) :='POA_EDW_SUP_PERF_FSTG';
674 l_exception_msg Varchar2(2000):=Null;
675 l_from_date Date:=Null;
676 l_to_date Date:=Null;
677 l_seq_id1 NUMBER := -1;
678 l_row_count NUMBER := 0;
679 l_row_count1 NUMBER := 0;
680 l_no_rcpt NUMBER;
681
682 l_push_local_failure EXCEPTION;
683 l_iden_change_failure EXCEPTION;
684 l_insert_rcpt_failure EXCEPTION;
685
686 -- -------------------------------------------
687 -- Put any additional developer variables here
688 -- -------------------------------------------
689 my_payment_currency Varchar2(2000):=NULL;
690 my_rate_date Varchar2(2000) := NULL;
691 my_collection_status Varchar2(2000):=NULL;
692
693 -- Cursor to get Missing rates
694 CURSOR Invalid_Rates IS
695 SELECT DISTINCT NVL(poh.rate_date, pll.creation_date) Rate_Date,
696 decode(poh.rate_type,
697 'User',gsob.currency_code,
698 NVL(poh.currency_code,
699 gsob.currency_code)) From_Currency,
700 fstg.Collection_Status
701 FROM (select TO_NUMBER(SUBSTR(sup_perf_pk, 1,
702 INSTR(sup_perf_pk, '-' )-1))
703 Line_location_id,
704 Collection_Status
705 from POA_EDW_SUP_PERF_FSTG
706 where COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR
707 COLLECTION_STATUS = 'INVALID CURRENCY') fstg,
708 po_line_locations_all pll,
709 PO_HEADERS_ALL POH,
710 GL_SETS_OF_BOOKS GSOB,
711 FINANCIALS_SYSTEM_PARAMS_ALL FSP
712 WHERE fstg.Line_location_id = pll.line_location_id
713 AND PLL.PO_HEADER_ID = POH.PO_HEADER_ID
714 AND NVL(fsp.org_id, -999) = NVL(pll.org_id, -999)
715 AND FSP.set_of_books_id = GSOB.set_of_books_id;
716
717 BEGIN
718
719 Errbuf :=NULL;
720 Retcode:=0;
721
722 l_from_date :=to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS');
723 l_to_date :=to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
724
725 IF (Not EDW_COLLECTION_UTIL.setup(l_fact_name, l_staging_table,
726 l_staging_table, l_exception_msg)) THEN
727 errbuf := fnd_message.get;
728 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
729 END IF;
730
731 -- --------------------------------------------
732 -- Taking care of cases where the input from/to
733 -- date is NULL.
734 -- --------------------------------------------
735 g_push_from_date := nvl(l_from_date,
736 EDW_COLLECTION_UTIL.G_local_last_push_start_date -
737 EDW_COLLECTION_UTIL.g_offset);
738
739 g_push_to_date := nvl(l_to_date,
740 EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
741
742 edw_log.put_line( 'The collection range is from '||
743 to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
744 to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS'));
745 edw_log.put_line(' ');
746
747
748 -- --------------------------------------------
749 -- Identify Change
750 -- --------------------------------------------
751 edw_log.put_line(' ');
752 edw_log.put_line('Identifying changes...');
753 l_seq_id1 := IDENTIFY_CHANGE1(1,l_row_count);
754
755 if (l_seq_id1 = -1) THEN
756 RAISE l_iden_change_failure;
757 end if;
758 edw_log.put_line('Identified '||l_row_count||' changed records');
759
760 -- -------------------------------------------------------------
761 -- Identify line locations for which receipts have been modified
762 -- -------------------------------------------------------------
763 edw_log.put_line('Calling insert_rcpt...');
764 l_no_rcpt := INSERT_RCPT(l_seq_id1);
765
766 if (l_no_rcpt = -1) THEN
767 RAISE l_insert_rcpt_failure;
768 end if;
769 edw_log.put_line('Inserted ' || l_no_rcpt || ' records');
770
771 -- -------------------------------------------
772 -- Delete delicates in the Inc Table
773 -- --------------------------------------------
774 DELETE_DUPLICATES;
775 edw_log.put_line('Duplicate records deleted in Inc Table');
776
777 -- --------------------------------------------
778 -- Push to local staging table for view type 1
779 -- --------------------------------------------
780
781 edw_log.put_line(' ');
782 edw_log.put_line('Inserting into local staging table for view type 1');
783 l_row_count1 := PUSH_TO_LOCAL(1, l_seq_id1);
784
785 IF (l_row_count1 = -1) THEN RAISE L_push_local_failure; END IF;
786
787 edw_log.put_line('Inserted '||nvl(l_row_count1,0)||
788 ' rows into the local staging table for view type 1');
789 edw_log.put_line(' ');
790
791 g_row_count := g_row_count + nvl(l_row_count1,0);
792
793 -- --------------------------------------------
794 -- Delete all incremental tables record
795 -- --------------------------------------------
796
797 TRUNCATE_INC;
798
799 -- --------------------------------------------
800 -- Insert Missing Rates from Local Staging Into Inc Tables
801 -- to repush them next time
802 -- --------------------------------------------
803 INSERT_MISSING_RATES;
804
805 OPEN Invalid_Rates;
806 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
807 'FROM CURRENCY CONVERSION DATE COLLECTION STATUS');
808 loop
809 FETCH Invalid_Rates INTO my_rate_date, my_payment_currency,
810 my_collection_status;
811 exit when Invalid_Rates % NOTFOUND;
812 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, my_payment_currency ||
813 ' '|| my_rate_date ||
814 ' '|| my_collection_status);
815 end loop;
816
817 close Invalid_Rates;
818
819 edw_log.put_line(' ');
820 edw_log.put_line('Report created for records with Missing Rates');
821 edw_log.put_line(' ');
822
823 -- --------------------------------------------
824 -- Delete records with missing rates from local staging table
825 -- --------------------------------------------
826 DELETE_STG_MISSING_RATES;
827
828 -- --------------------------------------------
829 -- No exception raised so far. Call wrapup to transport
830 -- data to target database, and insert messages into logs
831 -- -----------------------------------------------
832 edw_log.put_line(' ');
833 edw_log.put_line('Inserted '||nvl(g_row_count,0)||
834 ' rows into the staging table');
835 edw_log.put_line(' ');
836
837 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, l_exception_msg,
838 g_push_from_date, g_push_to_date);
839
840
841 -- ---------------------------------------------------------------------------
842 -- END OF Collection , Developer Customizable Section
843 -- ---------------------------------------------------------------------------
844
845 EXCEPTION
846
847 WHEN L_PUSH_LOCAL_FAILURE THEN
848 Errbuf:=g_errbuf;
849 Retcode:=g_retcode;
850
851 if (Invalid_Rates%ISOPEN) THEN
852 close Invalid_Rates;
853 end if;
854
855 l_exception_msg := Retcode || ':' || Errbuf;
856 rollback; -- Rollback insert into local staging
857 edw_log.put_line('Inserting into local staging have failed');
858 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
859 g_push_from_date, g_push_to_date);
860 raise;
861
862 WHEN L_IDEN_CHANGE_FAILURE THEN
863 Errbuf:=g_errbuf;
864 Retcode:=g_retcode;
865
866 if (Invalid_Rates%ISOPEN) THEN
867 close Invalid_Rates;
868 end if;
869
870 l_exception_msg := Retcode || ':' || Errbuf;
871 TRUNCATE_INC;
872 edw_log.put_line('Identifying changed records have Failed');
873 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
874 g_push_from_date, g_push_to_date);
875 raise;
876
877 WHEN L_INSERT_RCPT_FAILURE THEN
878 Errbuf:=g_errbuf;
879 Retcode:=g_retcode;
880
881 if (Invalid_Rates%ISOPEN) THEN
882 close Invalid_Rates;
883 end if;
884
885 l_exception_msg := Retcode || ':' || Errbuf;
886 rollback;
887 edw_log.put_line('Insert_rcpt has failed');
888 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
889 g_push_from_date, g_push_to_date);
890 raise;
891
892 WHEN OTHERS THEN
893 Errbuf:=g_errbuf;
894 Retcode:=g_retcode;
895
896 if (Invalid_Rates%ISOPEN) THEN
897 close Invalid_Rates;
898 end if;
899
900 l_exception_msg := Retcode || ':' || Errbuf;
901 rollback;
902 edw_log.put_line('Other errors');
903 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
904 g_push_from_date, g_push_to_date);
905 raise;
906
907 END;
908
909 End POA_EDW_SUP_PERF_F_C;