[Home] [Help]
PACKAGE BODY: APPS.POA_EDW_PO_DIST_F_C
Source
1 Package Body POA_EDW_PO_DIST_F_C AS
2 /* $Header: poafpdbb.pls 120.0 2005/06/02 03:03:20 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_PO_DIST_INC';
30 EXECUTE IMMEDIATE l_stmt;
31 END IF;
32
33 END;
34
35 -------------------------------------------------------------
36 -- PROCEDURE DELETE_INC
37 -------------------------------------------------------------
38
39 PROCEDURE DELETE_INC
40 IS
41
42 BEGIN
43
44 DELETE from poa_edw_po_dist_inc
45 WHERE batch_id <> 0;
46
47 END;
48
49 -------------------------------------------------------------
50 -- PROCEDURE INSERT_MISSING_RATES
51 -------------------------------------------------------------
52 --Identify records that have missing rates and insert them in a temp table
53
54 PROCEDURE INSERT_MISSING_RATES
55 IS
56 BEGIN
57 INSERT INTO poa_edw_po_dist_inc(primary_key,batch_id)
58 SELECT DESTRIBUTION_ID,0
59 FROM POA_EDW_PO_DIST_FSTG fstg
60 WHERE fstg.COLLECTION_STATUS = 'RATE NOT AVAILABLE'
61 OR fstg.COLLECTION_STATUS = 'INVALID CURRENCY';
62
63 IF (sql%rowcount > 0) THEN
64 g_retcode := 1;
65 END IF;
66
67 -- Generates "Warning" message in the Status column
68 -- of Concurrent Manager "Requests" table
69 edw_log.put_line(' ');
70 edw_log.put_line('INSERTING ' || to_char(sql%rowcount) ||
71 ' rows into poa_edw_po_dist_inc table');
72 END;
73
74 -----------------------------------------------------------
75 -- PROCEDURE DELETE_STG_MISSING_RATES
76 -----------------------------------------------------------
77 -- Procedure to remove rows from local staging table that have
78 -- collection status of either rate not available or invalid currency.
79 PROCEDURE DELETE_STG_MISSING_RATES
80 IS
81 BEGIN
82 -- DELETE FROM POA_EDW_PO_DIST_FSTG
83 -- WHERE COLLECTION_STATUS = 'RATE NOT AVAILABLE'
84 -- OR COLLECTION_STATUS = 'INVALID CURRENCY'
85 -- AND INSTANCE_FK = (SELECT INSTANCE_CODE
86 -- FROM EDW_LOCAL_INSTANCE);
87 RETURN;
88 END;
89
90 -----------------------------------------------------------
91 --PROCEDURE PUSH_TO_LOCAL
92 -----------------------------------------------------------
93
94 FUNCTION PUSH_TO_LOCAL(p_view_id NUMBER, p_seq_id NUMBER) RETURN NUMBER IS
95
96 l_temp_date DATE:=NULL;
97 l_duration NUMBER;
98 tmp_count NUMBER;
99 l_mau NUMBER; -- minimum accountable unit of global warehouse currency
100
101
102 BEGIN
103
104 /* Faustina's addition */
105
106 l_temp_date := sysdate;
107
108 IF (fnd_profile.value('POA_TRACE')='Y') THEN
109 dbms_session.set_sql_trace(TRUE);
110 END IF;
111
112 IF (fnd_profile.value('POA_DEBUG') = 'Y') THEN
113 poa_log.g_debug := TRUE;
114 END IF;
115
116 IF(fnd_profile.value('POA_POPULATE_SAVINGS_TXN') = 'Y') then
117 poa_savings_main.populate_savings(g_push_from_date, g_push_to_date+1, FALSE);
118 edw_log.put_line('Populated Savings table');
119 END IF;
120
121 l_duration := sysdate - l_temp_date;
122 edw_log.put_line('Process Time (populating saving table): '
123 || edw_log.duration(l_duration) || ', Current system time: ' || to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS'));
124 l_temp_date := sysdate;
125
126 /* End Faustina's Addition */
127
128
129 -- ------------------------------------------------
130 -- We set the COLLECTION_STATUS to 'LOCAL READY'.
131 -- In case of source=target, we need to separate
132 -- out the records in progress vs the records which
133 -- is ready to be picked up by collection enginee.
134 -- In our case, we consider the records to be in
135 -- progress until the push_to_local procedure for
136 -- all view types has completed successfully.
137 -- ------------------------------------------------
138
139 edw_log.put_line(' ');
140 edw_log.put_line('Start inserting to local staging table... ');
141
142 -- Prepare all initial variables for POA_EDW_VARIABLES_PKG
143
144 POA_EDW_VARIABLES_PKG.init;
145
146 --
147 -- To populate the columns for check_cut_date and invoice_received_date in the INC table.
148 --
149 -- No need to consider the order of records, just a massive update (seq_id is same).
150 --
151 -- This is used to replace the API calls "POA_EDW_SPEND_PKG.get_check_cut_date" and
152 -- "POA_EDW_SPEND_PKG.get_invoice_received_date" for performance improvement.
153 --
154
155 UPDATE poa_edw_po_dist_inc
156 SET (check_cut_date, invoice_received_date) =
157 (SELECT min(ack.check_date), min(ain.invoice_received_date)
158 FROM ap_invoice_distributions_all aid,
159 ap_invoice_payments_all aip,
160 ap_checks_all ack,
161 ap_invoices_all ain
162 WHERE aid.po_distribution_id = primary_key
163 AND aid.invoice_id = aip.invoice_id (+)
164 AND aip.check_id = ack.check_id (+)
165 AND aid.invoice_id = ain.invoice_id);
166 --------------------------------------------------------
167
168 -- get minimum accountable unit of the warehouse currency;
169
170 l_mau := nvl( edw_currency.get_mau, 0.01 );
171
172 Insert Into POA_EDW_PO_DIST_FSTG(
173 CHECK_CUT_DATE_FK,
174 INV_RECEIVED_DATE_FK,
175 INV_CREATION_DATE_FK,
176 GOODS_RECEIVED_DATE_FK,
177 DUNS_FK,
178 UNSPSC_FK,
179 SIC_CODE_FK,
180 APPRV_SUPPLIER_FK,
181 TASK_FK,
182 PO_CREATION_CYCLE_TIME,
183 ORDER_TO_PAY_CYCLE_TIME,
184 RECEIVE_TO_PAY_CYCL_TIME,
185 INV_CREATION_CYCLE_TIME,
186 INV_TO_PAY_CYCLE_TIME,
187 IPV_T,
188 IPV_G,
189 QTY_BILLED_B,
190 QTY_CANCELLED_B,
191 QTY_DELIVERED_B,
192 QTY_ORDERED_B,
193 ACCPT_DUE_DATE_FK,
194 ACCPT_REQUIRED_FK,
195 ACCRUED_FK,
196 AMT_BILLED_G,
197 AMT_BILLED_T,
198 AMT_CONTRACT_G,
199 AMT_CONTRACT_T,
200 AMT_LEAKAGE_G,
201 AMT_LEAKAGE_T,
202 AMT_NONCONTRACT_G,
203 AMT_NONCONTRACT_T,
204 AMT_PURCHASED_G,
205 AMT_PURCHASED_T,
206 APPROVER_FK,
207 AP_TERMS_FK,
208 BILL_LOCATION_FK,
209 BUYER_FK,
210 CONFIRM_ORDER_FK,
211 CONTRACT_NUM,
212 CONTRACT_TYPE_FK,
213 DELIVER_TO_FK,
214 DELIV_LOCATION_FK,
215 DESTIN_ORG_FK,
216 DESTIN_TYPE_FK,
217 DESTRIBUTION_ID,
218 DST_CREAT_DATE_FK,
219 DST_ENCUMB_FK,
220 EDI_PROCESSED_FK,
221 FOB_FK,
222 FREIGHT_TERMS_FK,
223 FROZEN_FK,
224 INSPECTION_REQ_FK,
225 INSTANCE_FK,
226 ITEM_DESCRIPTION,
227 ITEM_ID,
228 ITEM_FK,
229 LINE_LOCATION_ID,
230 LIST_PRC_UNIT_T,
231 LIST_PRC_UNIT_G,
232 LNE_CREAT_DATE_FK,
233 LNE_SUPPLIER_NOTE,
234 LST_ACCPT_DATE_FK,
235 MARKET_PRICE_T,
236 MARKET_PRICE_G,
237 NEED_BY_DATE_FK,
238 NEG_BY_PREPARE_FK,
239 ONLINE_REQ_FK,
240 PCARD_PROCESS_FK,
241 POTENTIAL_SVG_G,
242 POTENTIAL_SVG_T,
243 PO_ACCEPT_DATE_FK,
244 PO_APP_DATE_FK,
245 PO_COMMENTS,
246 PO_CREATE_DATE_FK,
247 PO_DIST_INST_PK,
248 PO_HEADER_ID,
249 PO_LINE_ID,
250 PO_LINE_TYPE_FK,
251 PO_NUMBER,
252 PO_RECEIVER_NOTE,
253 PO_RELEASE_ID,
254 PRICE_BREAK_FK,
255 PRICE_T,
256 PRICE_G,
257 PRICE_LIMIT_T,
258 PRICE_LIMIT_G,
259 PRICE_TYPE_FK,
260 PRINTED_DATE_FK,
261 PROMISED_DATE_FK,
262 PURCH_CLASS_FK,
263 RCV_ROUTING_FK,
264 RECEIPT_REQ_FK,
265 RELEASE_DATE_FK,
266 RELEASE_HOLD_FK,
267 RELEASE_NUM,
268 REQ_APPRV_DATE_FK,
269 REQ_CREAT_DATE_FK,
270 REVISED_DATE_FK,
271 REVISION_NUM,
272 SHIPMENT_TYPE_FK,
273 SHIP_LOCATION_FK,
274 SHIP_TO_ORG_FK,
275 SHIP_VIA_FK,
276 SHP_APPROVED_FK,
277 SHP_APP_DATE_FK,
278 SHP_CANCELLED_FK,
279 SHP_CANCEL_REASON,
280 SHP_CLOSED_FK,
281 SHP_CLOSED_REASON,
282 SHP_CREAT_DATE_FK,
283 SHP_SRC_SHIP_ID,
284 SHP_TAXABLE_FK,
285 SOB_FK,
286 SOURCE_DIST_ID,
287 SUB_RECEIPT_FK,
288 SUPPLIER_ITEM_FK,
289 SUPPLIER_NOTE,
290 SUPPLIER_SITE_FK,
291 SUP_SITE_GEOG_FK,
292 TXN_CUR_CODE_FK,
293 TXN_CUR_DATE_FK,
294 TXN_REASON_FK,
295 EDW_UOM_FK,
296 EDW_BASE_UOM_FK,
297 USER_ATTRIBUTE1,
298 USER_ATTRIBUTE2,
299 USER_ATTRIBUTE3,
300 USER_ATTRIBUTE4,
301 USER_ATTRIBUTE5,
302 USER_ATTRIBUTE6,
303 USER_ATTRIBUTE7,
304 USER_ATTRIBUTE8,
305 USER_ATTRIBUTE9,
306 USER_ATTRIBUTE10,
307 USER_ATTRIBUTE11,
308 USER_ATTRIBUTE12,
309 USER_ATTRIBUTE13,
310 USER_ATTRIBUTE14,
311 USER_ATTRIBUTE15,
312 USER_FK1,
313 USER_FK2,
314 USER_FK3,
315 USER_FK4,
316 USER_FK5,
317 USER_MEASURE1,
318 USER_MEASURE2,
319 USER_MEASURE3,
320 USER_MEASURE4,
321 USER_MEASURE5,
322 OPERATION_CODE,
323 COLLECTION_STATUS)
324 select
325 NVL(CHECK_CUT_DATE_FK,'NA_EDW'),
326 NVL(INV_RECEIVED_DATE_FK,'NA_EDW'),
327 NVL(INV_CREATION_DATE_FK,'NA_EDW'),
328 NVL(GOODS_RECEIVED_DATE_FK,'NA_EDW'),
329 NVL(DUNS_FK,'NA_EDW'),
330 NVL(UNSPSC_FK,'NA_EDW'),
331 NVL(SIC_CODE_FK,'NA_EDW'),
332 NVL(APPRV_SUPPLIER_FK,'NA_EDW'),
333 NVL(TASK_FK,'NA_EDW'),
334 PO_CREATION_CYCLE_TIME,
335 ORDER_TO_PAY_CYCLE_TIME,
336 RECEIVE_TO_PAY_CYCL_TIME,
337 INV_CREATION_CYCLE_TIME,
338 INV_TO_PAY_CYCLE_TIME,
339 IPV_T,
340 round(IPV_G / l_mau) * l_mau,
341 QTY_BILLED_B,
342 QTY_CANCELLED_B,
343 QTY_DELIVERED_B,
344 QTY_ORDERED_B,
345 NVL(ACCPT_DUE_DATE_FK,'NA_EDW'),
346 NVL(ACCPT_REQUIRED_FK,'NA_EDW'),
347 NVL(ACCRUED_FK,'NA_EDW'),
348 round(AMT_BILLED_G / l_mau) * l_mau,
349 AMT_BILLED_T,
350 round(AMT_CONTRACT_G / l_mau) * l_mau,
351 AMT_CONTRACT_T,
352 round(AMT_LEAKAGE_G / l_mau) * l_mau,
353 AMT_LEAKAGE_T,
354 round(AMT_NONCONTRACT_G / l_mau) * l_mau,
355 AMT_NONCONTRACT_T,
356 round(AMT_PURCHASED_G / l_mau) * l_mau,
357 AMT_PURCHASED_T,
358 NVL(APPROVER_FK,'NA_EDW'),
359 NVL(AP_TERMS_FK,'NA_EDW'),
360 NVL(BILL_LOCATION_FK,'NA_EDW'),
361 NVL(BUYER_FK,'NA_EDW'),
362 NVL(CONFIRM_ORDER_FK,'NA_EDW'),
363 CONTRACT_NUM,
364 NVL(CONTRACT_TYPE_FK,'NA_EDW'),
365 NVL(DELIVER_TO_FK,'NA_EDW'),
366 NVL(DELIV_LOCATION_FK,'NA_EDW'),
367 NVL(DESTIN_ORG_FK,'NA_EDW'),
368 NVL(DESTIN_TYPE_FK,'NA_EDW'),
369 DISTRIBUTION_ID,
370 NVL(DST_CREAT_DATE_FK,'NA_EDW'),
371 NVL(DST_ENCUMB_FK,'NA_EDW'),
372 NVL(EDI_PROCESSED_FK,'NA_EDW'),
373 NVL(FOB_FK,'NA_EDW'),
374 NVL(FREIGHT_TERMS_FK,'NA_EDW'),
375 NVL(FROZEN_FK,'NA_EDW'),
376 NVL(INSPECTION_REQ_FK,'NA_EDW'),
377 NVL(INSTANCE_FK,'NA_EDW'),
378 ITEM_DESCRIPTION,
379 ITEM_ID,
380 NVL(ITEM_FK,'NA_EDW'),
381 LINE_LOCATION_ID,
382 LIST_PRC_UNIT_T,
383 round(LIST_PRC_UNIT_G / l_mau) * l_mau,
384 NVL(LNE_CREAT_DATE_FK,'NA_EDW'),
385 LNE_SUPPLIER_NOTE,
386 NVL(LST_ACCPT_DATE_FK,'NA_EDW'),
387 MARKET_PRICE_T,
388 round(MARKET_PRICE_G / l_mau) * l_mau,
389 NVL(NEED_BY_DATE_FK,'NA_EDW'),
390 NVL(NEG_BY_PREPARE_FK,'NA_EDW'),
391 NVL(ONLINE_REQ_FK,'NA_EDW'),
392 NVL(PCARD_PROCESS_FK,'NA_EDW'),
393 round(POTENTIAL_SVG_G / l_mau) * l_mau,
394 POTENTIAL_SVG_T,
395 NVL(PO_ACCEPT_DATE_FK,'NA_EDW'),
396 NVL(PO_APP_DATE_FK,'NA_EDW'),
397 PO_COMMENTS,
398 NVL(PO_CREATE_DATE_FK,'NA_EDW'),
399 PO_DIST_INST_PK,
400 PO_HEADER_ID,
401 PO_LINE_ID,
402 NVL(PO_LINE_TYPE_FK,'NA_EDW'),
403 PO_NUMBER,
404 PO_RECEIVER_NOTE,
405 PO_RELEASE_ID,
406 NVL(PRICE_BREAK_FK,'NA_EDW'),
407 PRICE_T,
408 round(PRICE_G / l_mau) * l_mau,
409 PRICE_LIMIT_T,
410 round(PRICE_LIMIT_G / l_mau) * l_mau,
411 NVL(PRICE_TYPE_FK,'NA_EDW'),
412 NVL(PRINTED_DATE_FK,'NA_EDW'),
413 NVL(PROMISED_DATE_FK,'NA_EDW'),
414 NVL(PURCH_CLASS_FK, 'NA_EDW'),
415 NVL(RCV_ROUTING_FK,'NA_EDW'),
416 NVL(RECEIPT_REQ_FK,'NA_EDW'),
417 NVL(RELEASE_DATE_FK,'NA_EDW'),
418 NVL(RELEASE_HOLD_FK,'NA_EDW'),
419 RELEASE_NUM,
420 NVL(REQ_APPRV_DATE_FK,'NA_EDW'),
421 NVL(REQ_CREAT_DATE_FK,'NA_EDW'),
422 NVL(REVISED_DATE_FK,'NA_EDW'),
423 REVISION_NUM,
424 NVL(SHIPMENT_TYPE_FK,'NA_EDW'),
425 NVL(SHIP_LOCATION_FK,'NA_EDW'),
426 NVL(SHIP_TO_ORG_FK,'NA_EDW'),
427 NVL(SHIP_VIA_FK,'NA_EDW'),
428 NVL(SHP_APPROVED_FK,'NA_EDW'),
429 NVL(SHP_APP_DATE_FK,'NA_EDW'),
430 NVL(SHP_CANCELLED_FK,'NA_EDW'),
431 SHP_CANCEL_REASON,
432 NVL(SHP_CLOSED_FK,'NA_EDW'),
433 SHP_CLOSED_REASON,
434 NVL(SHP_CREAT_DATE_FK,'NA_EDW'),
435 SHP_SRC_SHIP_ID,
436 NVL(SHP_TAXABLE_FK,'NA_EDW'),
437 NVL(SOB_FK,'NA_EDW'),
438 SOURCE_DIST_ID,
439 NVL(SUB_RECEIPT_FK,'NA_EDW'),
440 NVL(SUPPLIER_ITEM_FK,'NA_EDW'),
441 SUPPLIER_NOTE,
442 NVL(SUPPLIER_SITE_FK,'NA_EDW'),
443 NVL(SUP_SITE_GEOG_FK,'NA_EDW'),
444 NVL(TXN_CUR_CODE_FK,'NA_EDW'),
445 NVL(TXN_CUR_DATE_FK,'NA_EDW'),
446 NVL(TXN_REASON_FK,'NA_EDW'),
447 NVL(EDW_UOM_FK,'NA_EDW'),
448 NVL(EDW_BASE_UOM_FK,'NA_EDW'),
449 USER_ATTRIBUTE1,
450 USER_ATTRIBUTE2,
451 USER_ATTRIBUTE3,
452 USER_ATTRIBUTE4,
453 USER_ATTRIBUTE5,
454 USER_ATTRIBUTE6,
455 USER_ATTRIBUTE7,
456 USER_ATTRIBUTE8,
457 USER_ATTRIBUTE9,
458 USER_ATTRIBUTE10,
459 USER_ATTRIBUTE11,
460 USER_ATTRIBUTE12,
461 USER_ATTRIBUTE13,
462 USER_ATTRIBUTE14,
463 USER_ATTRIBUTE15,
464 NVL(USER_FK1,'NA_EDW'),
465 NVL(USER_FK2,'NA_EDW'),
466 NVL(USER_FK3,'NA_EDW'),
467 NVL(USER_FK4,'NA_EDW'),
468 NVL(USER_FK5,'NA_EDW'),
469 USER_MEASURE1,
470 USER_MEASURE2,
471 USER_MEASURE3,
472 USER_MEASURE4,
473 USER_MEASURE5,
474 NULL, -- OPERATION_CODE
475 collection_status
476 from POA_EDW_PO_DISTRIBUTIONS_FCV
477 WHERE view_id = p_view_id
478 AND seq_id = p_seq_id;
479
480 tmp_count := sql%rowcount;
481
482 l_duration := sysdate - l_temp_date;
483 edw_log.put_line('Process Time (inserting to local staging table): '
484 || edw_log.duration(l_duration) || ', Current system time: ' || to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS'));
485 edw_log.put_line(' ');
486
487 RETURN (tmp_count);
488
489 EXCEPTION
490 WHEN OTHERS THEN
491 g_errbuf:=sqlerrm;
492 g_retcode:=sqlcode;
493 RETURN(-1);
494
495 END;
496
497 -----------------------------------------------------------
498 -- PROCEDURE DELETE_DUPLICATES
499 -----------------------------------------------------------
500
501 PROCEDURE DELETE_DUPLICATES IS
502
503 -- Cursor to delete duplicates
504 CURSOR Dup_Rec IS
505 SELECT primary_key
506 FROM poa_edw_po_dist_inc
507 ORDER BY primary_key
508 FOR UPDATE;
509
510 v_prev_id NUMBER;
511 v_cur_id NUMBER;
512
513 BEGIN
514 OPEN Dup_Rec;
515
516 LOOP
517
518 FETCH Dup_Rec INTO v_cur_id;
519 exit when Dup_Rec % NOTFOUND;
520
521 -- Check if the PK already exists
522 IF (v_prev_id = v_cur_id) THEN
523 DELETE FROM poa_edw_po_dist_inc
524 WHERE CURRENT OF Dup_Rec;
525 ELSE
526 v_prev_id := v_cur_id;
527 END IF;
528 END LOOP;
529
530 close Dup_Rec;
531 EXCEPTION
532 WHEN OTHERS THEN
533 IF Dup_Rec%ISOPEN THEN
534 close Dup_Rec;
535 END IF;
536 END;
537
538 ---------------------------------------------------
539 -- FUNCTION IDENTIFY_CHANGE1
540 ---------------------------------------------------
541
542 FUNCTION IDENTIFY_CHANGE1 (p_view_id IN NUMBER,
543 p_count OUT NOCOPY NUMBER)
544 RETURN NUMBER
545 IS
546
547 l_seq_id NUMBER := -1;
548 l_batch_size NUMBER := fnd_profile.value('POA_COLLECTION_BATCH_SIZE');
549 TYPE plsqltable IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
550 l_primary_key plsqltable;
551 l_batch_id plsqltable;
552 l_count NUMBER;
553 l_poa_schema VARCHAR2(30);
554 l_status VARCHAR2(30);
555 l_industry VARCHAR2(30);
556
557 CURSOR v_changed_rows(g_push_from_date date,
558 g_push_to_date date,
559 p_batch_size number) IS
560 SELECT po_distribution_id, ceil(rownum/p_batch_size)
561 FROM
562 (SELECT pod.PO_DISTRIBUTION_ID, pol.item_id, pod.creation_date
563 FROM po_lines_all pol,
564 po_line_locations_all pll,
565 po_headers_all poh,
566 po_distributions_all pod
567 WHERE pod.line_location_id = pll.line_location_id
568 and pod.po_line_id = pol.po_line_id
569 and pod.po_header_id = poh.po_header_id
570 and pll.shipment_type = 'STANDARD'
571 and pll.approved_flag = 'Y'
572 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
573 and greatest(pol.last_update_date, pll.last_update_date,
574 poh.last_update_date, pod.last_update_date, nvl(pod.program_update_date, pod.last_update_date))
575 between g_push_from_date and g_push_to_date
576 UNION ALL
577 SELECT pod.PO_DISTRIBUTION_ID, pol.item_id, pod.creation_date
578 FROM po_lines_all pol,
579 po_line_locations_all pll,
580 po_headers_all poh,
581 po_releases_all por,
582 po_distributions_all pod
583 WHERE pod.line_location_id = pll.line_location_id
584 and pod.po_release_id = por.po_release_id
585 and pod.po_line_id = pol.po_line_id
586 and pod.po_header_id = poh.po_header_id
587 and pll.shipment_type in ('BLANKET', 'SCHEDULED')
588 and pll.approved_flag = 'Y'
589 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
590 and greatest(pol.last_update_date,pll.last_update_date,
591 poh.last_update_date,por.last_update_date,pod.last_update_date, nvl(pod.program_update_date, pod.last_update_date))
592 between g_push_from_date and g_push_to_date)
593 order by item_id, creation_date;
594
595 BEGIN
596
597 p_count := 0;
598 select poa_edw_po_dist_inc_s.nextval into l_seq_id from dual;
599
600 /** Update the seq_id for records that had missing currency rates in
601 the earlier PUSH. We need to repush these records again
602 **/
603
604 UPDATE poa_edw_po_dist_inc
605 SET seq_id = l_seq_id
606 WHERE seq_id IS NULL;
607
608 p_count := sql%rowcount;
609 edw_log.put_line( 'Updated ' || p_count || ' records');
610
611 open v_changed_rows(g_push_from_date, g_push_to_date, l_batch_size);
612 loop
613 fetch v_changed_rows bulk collect into
614 l_primary_key, l_batch_id limit l_batch_size;
615 l_count := l_primary_key.count;
616 forall i in 1..l_count
617 INSERT into poa_edw_po_dist_inc(primary_key, seq_id, batch_id)
618 values(l_primary_key(i), l_seq_id, l_batch_id(i));
619 p_count := p_count + l_count;
620 EXIT WHEN l_count < l_batch_size;
621 end loop;
622 close v_changed_rows;
623
624 COMMIT;
625 IF (FND_INSTALLATION.GET_APP_INFO('POA', l_status, l_industry, l_poa_schema)) THEN
626 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_poa_schema, TABNAME => 'POA_EDW_PO_DIST_INC') ;
627 END IF;
628
629 RETURN(l_seq_id);
630
631 EXCEPTION
632 WHEN OTHERS THEN
633 g_errbuf:=sqlerrm;
634 g_retcode:=sqlcode;
635 RETURN(-1);
636
637 END;
638
639
640 -- ---------------------------------
641 -- PUBLIC PROCEDURES
642 -- ---------------------------------
643
644 -----------------------------------------------------------
645 -- PROCEDURE PUSH
646 -----------------------------------------------------------
647
648 PROCEDURE PUSH(Errbuf in out NOCOPY Varchar2,
649 Retcode in out NOCOPY Varchar2,
650 p_from_date IN Varchar2,
651 p_to_date IN Varchar2) IS
652
653
654 l_fact_name Varchar2(30) :='POA_EDW_PO_DIST_F';
655 l_staging_table Varchar2(30) :='POA_EDW_PO_DIST_FSTG';
656 l_exception_msg Varchar2(2000):=Null;
657 l_from_date Date:=Null;
658 l_to_date Date:=Null;
659 l_seq_id1 NUMBER := -1;
660 l_row_count NUMBER := 0;
661 l_row_count1 NUMBER := 0;
662
663 l_push_local_failure EXCEPTION;
664 l_iden_change_failure EXCEPTION;
665 my_payment_currency Varchar2(2000):=NULL;
666 my_rate_date Varchar2(2000) := NULL;
667 my_collection_status Varchar2(2000):=NULL;
668
669 -- Cursor to get Missing rates
670 CURSOR Invalid_Rates IS
671 SELECT DISTINCT NVL(pod.rate_date, pod.creation_date) Rate_Date,
672 decode(poh.rate_type,
673 'User',gsob.currency_code,
674 NVL(poh.currency_code,
675 gsob.currency_code)) From_Currency,
676 fstg.Collection_Status
677 FROM POA_EDW_PO_DIST_FSTG fstg,
678 po_distributions_all pod,
679 po_headers_all poh,
680 gl_sets_of_books gsob
681 WHERE (fstg.COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR
682 fstg.COLLECTION_STATUS = 'INVALID CURRENCY')
683 AND fstg.DESTRIBUTION_ID = pod.po_distribution_id
684 AND fstg.PO_HEADER_ID = poh.po_header_id
685 AND pod.set_of_books_id = gsob.set_of_books_id
686 AND nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
687
688 -- -------------------------------------------
689 -- Put any additional developer variables here
690 -- -------------------------------------------
691
692 BEGIN
693
694 Errbuf :=NULL;
695 Retcode:=0;
696
697 l_from_date :=to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS');
698 l_to_date :=to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
699
700 IF (Not EDW_COLLECTION_UTIL.setup(l_fact_name, l_staging_table,
701 l_staging_table, l_exception_msg)) THEN
702 errbuf := fnd_message.get;
703 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
704 END IF;
705
706 -- --------------------------------------------
707 -- Taking care of cases where the input from/to
708 -- date is NULL.
709 -- --------------------------------------------
710 g_push_from_date := nvl(l_from_date,
711 EDW_COLLECTION_UTIL.G_local_last_push_start_date -
712 EDW_COLLECTION_UTIL.g_offset);
713
714 g_push_to_date := nvl(l_to_date,
715 EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
716
717 edw_log.put_line( 'The collection range is from '||
718 to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
719 to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS'));
720 edw_log.put_line(' ');
721
722 -- --------------------------------------------
723 -- Deleting the incremental table
724 -- --------------------------------------------
725
726 edw_log.put_line('Deleting incremental table...');
727 edw_log.put_line('System time at start of deletion of inc. table ' || to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS'));
728 DELETE_INC;
729 edw_log.put_line('System time at end of deletion of inc. table ' || to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS'));
730 edw_log.put_line('Incremental table deleted');
731
732 -- --------------------------------------------
733 -- Identify Change
734 -- --------------------------------------------
735 edw_log.put_line(' ');
736 edw_log.put_line('Identifying changes...');
737 edw_log.put_line('System time at start of identify change ' || to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS'));
738 l_seq_id1 := IDENTIFY_CHANGE1(1,l_row_count);
739 edw_log.put_line('System time at end of identify change ' || to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS'));
740
741 if (l_seq_id1 = -1) THEN
742 RAISE l_iden_change_failure;
743 end if;
744 edw_log.put_line('Identified '||l_row_count||' changed records');
745
746 -- -------------------------------------------
747 -- Delete delicates in the Inc Table
748 -- --------------------------------------------
749 edw_log.put_line(' ');
750 edw_log.put_line('Deleting duplicate records from inc. table...');
751 edw_log.put_line('System time at start of delete duplicates ' || to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS'));
752 DELETE_DUPLICATES;
753 edw_log.put_line('System time at end of delete duplicates ' || to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS'));
754 edw_log.put_line('Duplicate records deleted in Inc Table');
755
756 -- --------------------------------------------
757 -- Push to local staging table for view type 1
758 -- --------------------------------------------
759
760 edw_log.put_line(' ');
761 edw_log.put_line('Inserting into local staging table for view type 1');
762 edw_log.put_line('System time at start of push to local ' || to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS'));
763 l_row_count1 := PUSH_TO_LOCAL(1, l_seq_id1);
764 edw_log.put_line('System time at end of push to local ' || to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS'));
765
766 IF (l_row_count1 = -1) THEN RAISE L_push_local_failure; END IF;
767
768 edw_log.put_line('Inserted ' || nvl(l_row_count1,0) ||
769 ' rows into the local staging table for view type 1');
770 edw_log.put_line(' ');
771
772 g_row_count := g_row_count + nvl(l_row_count1,0);
773
774 -- --------------------------------------------
775 -- Delete all incremental tables' record
776 -- --------------------------------------------
777 edw_log.put_line(' ');
778 edw_log.put_line('Truncating incremental table...');
779 edw_log.put_line('System time at start of truncate inc ' || to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS'));
780 TRUNCATE_INC;
781 edw_log.put_line('System time at end of truncate inc ' || to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS'));
782 edw_log.put_line('truncated Increment Table');
783
784 -- --------------------------------------------
785 -- Insert Missing Rates from Local Staging Into Inc Tables
786 -- to repush them next time
787 -- --------------------------------------------
788 edw_log.put_line(' ');
789 edw_log.put_line('Inserting missing rates...');
790 edw_log.put_line('System time at start of insert missing rates ' || to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS'));
791 INSERT_MISSING_RATES;
792 edw_log.put_line('System time at end of insert missing rates ' || to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS'));
793 edw_log.put_line(' ');
794 edw_log.put_line('Checked records for Missing Rates');
795 edw_log.put_line(' ');
796
797 OPEN Invalid_Rates;
798 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
799 'FROM CURRENCY CONVERSION DATE COLLECTION STATUS');
800 loop
801 FETCH Invalid_Rates INTO my_rate_date, my_payment_currency,
802 my_collection_status;
803 exit when Invalid_Rates % NOTFOUND;
804 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, my_payment_currency ||
805 ' '|| my_rate_date ||
806 ' '|| my_collection_status);
807 end loop;
808
809 close Invalid_Rates;
810
811 edw_log.put_line(' ');
812 edw_log.put_line('Report created for records with Missing Rates');
813 edw_log.put_line(' ');
814
815 -- --------------------------------------------
816 -- Delete records with missing rates from local staging table
817 -- --------------------------------------------
818 edw_log.put_line(' ');
819 edw_log.put_line('Deleting missing rates from local staging table...');
820 edw_log.put_line('System time at start of delete stg missing rates ' || to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS'));
821 DELETE_STG_MISSING_RATES;
822 edw_log.put_line('System time at end of delete stg missing rates ' || to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS'));
823
824 -- --------------------------------------------
825 -- No exception raised so far. Call wrapup to transport
826 -- data to target database, and insert messages into logs
827 -- -----------------------------------------------
828 edw_log.put_line(' ');
829 edw_log.put_line('Inserted '||nvl(g_row_count,0)||
830 ' rows into the staging table');
831 edw_log.put_line(' ');
832
833 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, l_exception_msg,
834 g_push_from_date, g_push_to_date);
835
836
837 -- ---------------------------------------------------------------------------
838 -- END OF Collection , Developer Customizable Section
839 -- ---------------------------------------------------------------------------
840
841 EXCEPTION
842
843 WHEN L_PUSH_LOCAL_FAILURE THEN
844 Errbuf:=g_errbuf;
845 Retcode:=g_retcode;
846
847 if (Invalid_Rates%ISOPEN) THEN
848 close Invalid_Rates;
849 end if;
850
851 l_exception_msg := Retcode || ':' || Errbuf;
852 rollback; -- Rollback insert into local staging
853 edw_log.put_line('Inserting into local staging have failed');
854 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
855 g_push_from_date, g_push_to_date);
856 raise;
857
858 WHEN L_IDEN_CHANGE_FAILURE THEN
859 Errbuf:=g_errbuf;
860 Retcode:=g_retcode;
861
862 if (Invalid_Rates%ISOPEN) THEN
863 close Invalid_Rates;
864 end if;
865
866 l_exception_msg := Retcode || ':' || Errbuf;
867 rollback;
868 edw_log.put_line('Identifying changed records have Failed');
869 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
870 g_push_from_date, g_push_to_date);
871 raise;
872
873 WHEN OTHERS THEN
874 Errbuf:=g_errbuf;
875 Retcode:=g_retcode;
876
877 if (Invalid_Rates%ISOPEN) THEN
878 close Invalid_Rates;
879 end if;
880
881 l_exception_msg := Retcode || ':' || Errbuf;
882 rollback;
883 edw_log.put_line('Other errors');
884 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
885 g_push_from_date, g_push_to_date);
886 raise;
887
888 END;
889
890 End POA_EDW_PO_DIST_F_C;