[Home] [Help]
PACKAGE BODY: APPS.POA_EDW_CONTRACT_F_C
Source
1 Package Body POA_EDW_CONTRACT_F_C AS
2 /* $Header: poafpctb.pls 120.1 2005/06/13 12:58:20 sriswami noship $ */
3 G_PUSH_DATE_RANGE1 Date:=Null;
4 G_PUSH_DATE_RANGE2 Date:=Null;
5 g_row_count Number:=0;
6
7 g_errbuf VARCHAR2(2000) := NULL;
8 g_retcode VARCHAR2(200) := NULL;
9
10 -- ---------------------------------
11 -- PRIVATE PROCEDURES AND FUNCTIONS
12 -- ---------------------------------
13
14 -----------------------------------------------------------
15 -- PROCEDURE TRUNCATE_INC
16 -----------------------------------------------------------
17
18 PROCEDURE TRUNCATE_INC IS
19
20 l_poa_schema VARCHAR2(30);
21 l_stmt VARCHAR2(200);
22 l_status VARCHAR2(30);
23 l_industry VARCHAR2(30);
24
25 BEGIN
26
27 IF (FND_INSTALLATION.GET_APP_INFO('POA', l_status, l_industry, l_poa_schema)) THEN
28 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_EDW_CONTRACT_INC';
29 EXECUTE IMMEDIATE l_stmt;
30 END IF;
31
32 END;
33
34 -------------------------------------------------------------
35 -- PROCEDURE INSERT_MISSING_RATES
36 -------------------------------------------------------------
37 --Identify records that have missing rates and insert them in a temp table
38
39 PROCEDURE INSERT_MISSING_RATES
40 IS
41 BEGIN
42 INSERT INTO poa_edw_contract_inc(primary_key)
43 SELECT po_header_id
44 FROM POA_EDW_CONTRACT_FSTG
45 where COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR
46 COLLECTION_STATUS = 'INVALID CURRENCY';
47
48 IF (sql%rowcount > 0) THEN
49 g_retcode := 1;
50 END IF;
51
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_contract_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_CONTRACT_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 --PROCEDURE PUSH_TO_LOCAL
78 -----------------------------------------------------------
79
80 FUNCTION PUSH_TO_LOCAL(p_view_id NUMBER, p_seq_id NUMBER) RETURN NUMBER IS
81
82 l_duration NUMBER;
83 l_temp_date DATE:=NULL;
84 l_rows_inserted Number:=0;
85
86 BEGIN
87
88 -- ------------------------------------------------
89 -- We set the COLLECTION_STATUS to 'LOCAL READY'.
90 -- In case of source=target, we need to separate
91 -- out the records in progress vs the records which
92 -- is ready to be picked up by collection enginee.
93 -- In our case, we consider the records to be in
94 -- progress until the push_to_local procedure for
95 -- all view types has completed successfully.
96 -- ------------------------------------------------
97
98 edw_log.put_line(' ');
99 edw_log.put_line('Pushing data to local staging table...');
100
101 l_temp_date := sysdate;
102 Insert Into POA_EDW_CONTRACT_FSTG(
103 DUNS_FK,
104 SIC_CODE_FK,
105 CONTRACT_NUM,
106 ACCPT_DUE_DATE_FK,
107 ACCPT_REQUIRED_FK,
108 AMT_AGREED_G,
109 AMT_AGREED_T,
110 AMT_LIMIT_G,
111 AMT_LIMIT_T,
112 AMT_MIN_RELEASE_G,
113 AMT_MIN_RELEASE_T,
114 AMT_RELEASED_G,
115 AMT_RELEASED_T,
116 APPROVED_DATE_FK,
117 APPROVED_FK,
118 APPROVER_FK,
119 AP_TERMS_FK,
120 BILL_LOCATION_FK,
121 BUYER_FK,
122 CANCELLED_FK,
123 CLOSED_FK,
124 COMMENTS,
125 CONFIRM_ORDER_FK,
126 CONTRACT_EFFECTIVE_FK,
127 CONTRACT_PK,
128 CREATION_DATE_FK,
129 EDI_PROCESSED_FK,
130 END_DATE_FK,
131 FOB_FK,
132 FREIGHT_TERMS_FK,
133 FROZEN_FK,
134 INSTANCE_FK,
135 NUM_DAYS_APP_SEND_TO_ACCPT,
136 NUM_DAYS_APP_TO_SEND,
137 NUM_DAYS_CREATE_TO_APP,
138 OPERATING_UNIT_FK,
139 PO_HEADER_ID,
140 PO_TYPE_FK,
141 PRINTED_DATE_FK,
142 RECEIVER_NOTE,
143 REVISED_DATE_FK,
144 REVISION_NUM,
145 SHIP_LOCATION_FK,
146 SHIP_VIA_FK,
147 START_DATE_FK,
148 SUPPLIER_NOTE,
149 SUPPLIER_SITE_FK,
150 SUP_SITE_GEOG_FK,
151 TXN_CUR_CODE_FK,
152 TXN_CUR_DATE_FK,
153 TXN_CUR_RATE,
154 TXN_CUR_RATE_TYPE,
155 USER_ATTRIBUTE1,
156 USER_ATTRIBUTE2,
157 USER_ATTRIBUTE3,
158 USER_ATTRIBUTE4,
159 USER_ATTRIBUTE5,
160 USER_ATTRIBUTE6,
161 USER_ATTRIBUTE7,
162 USER_ATTRIBUTE8,
163 USER_ATTRIBUTE9,
164 USER_ATTRIBUTE10,
165 USER_ATTRIBUTE11,
166 USER_ATTRIBUTE12,
167 USER_ATTRIBUTE13,
168 USER_ATTRIBUTE14,
169 USER_ATTRIBUTE15,
170 USER_FK1,
171 USER_FK2,
172 USER_FK3,
173 USER_FK4,
174 USER_FK5,
175 USER_HOLD_FK,
176 USER_MEASURE1,
177 USER_MEASURE2,
178 USER_MEASURE3,
179 USER_MEASURE4,
180 USER_MEASURE5,
181 OPERATION_CODE,
182 COLLECTION_STATUS)
183 select
184 NVL(DUNS_FK, 'NA_EDW'),
185 NVL(SIC_CODE_FK, 'NA_EDW'),
186 CONTRACT_NUM,
187 NVL(ACCPT_DUE_DATE_FK,'NA_EDW'),
188 NVL(ACCPT_REQUIRED_FK,'NA_EDW'),
189 AMT_AGREED_G,
190 AMT_AGREED_T,
191 AMT_LIMIT_G,
192 AMT_LIMIT_T,
193 AMT_MIN_RELEASE_G,
194 AMT_MIN_RELEASE_T,
195 AMT_RELEASED_G,
196 AMT_RELEASED_T,
197 NVL(APPROVED_DATE_FK,'NA_EDW'),
198 NVL(APPROVED_FK,'NA_EDW'),
199 NVL(APPROVER_FK,'NA_EDW'),
200 NVL(AP_TERMS_FK,'NA_EDW'),
201 NVL(BILL_LOCATION_FK,'NA_EDW'),
202 NVL(BUYER_FK,'NA_EDW'),
203 NVL(CANCELLED_FK,'NA_EDW'),
204 NVL(CLOSED_FK,'NA_EDW'),
205 COMMENTS,
206 NVL(CONFIRM_ORDER_FK,'NA_EDW'),
207 NVL(CONTRACT_EFFECTIVE_FK,'NA_EDW'),
208 CONTRACT_PK,
209 NVL(CREATION_DATE_FK,'NA_EDW'),
210 NVL(EDI_PROCESSED_FK,'NA_EDW'),
211 NVL(END_DATE_FK,'NA_EDW'),
212 NVL(FOB_FK,'NA_EDW'),
213 NVL(FREIGHT_TERMS_FK,'NA_EDW'),
214 NVL(FROZEN_FK,'NA_EDW'),
215 NVL(INSTANCE_FK,'NA_EDW'),
216 NUM_DAYS_APP_SEND_TO_ACCPT,
217 NUM_DAYS_APP_TO_SEND,
218 NUM_DAYS_CREATE_TO_APP,
219 NVL(OPERATING_UNIT_FK,'NA_EDW'),
220 PO_HEADER_ID,
221 NVL(PO_TYPE_FK,'NA_EDW'),
222 NVL(PRINTED_DATE_FK,'NA_EDW'),
223 RECEIVER_NOTE,
224 NVL(REVISED_DATE_FK,'NA_EDW'),
225 REVISION_NUM,
226 NVL(SHIP_LOCATION_FK,'NA_EDW'),
227 NVL(SHIP_VIA_FK,'NA_EDW'),
228 NVL(START_DATE_FK,'NA_EDW'),
229 SUPPLIER_NOTE,
230 NVL(SUPPLIER_SITE_FK,'NA_EDW'),
231 NVL(SUP_SITE_GEOG_FK,'NA_EDW'),
232 NVL(TXN_CUR_CODE_FK,'NA_EDW'),
233 NVL(TXN_CUR_DATE_FK,'NA_EDW'),
234 TXN_CUR_RATE,
235 TXN_CUR_RATE_TYPE,
236 USER_ATTRIBUTE1,
237 USER_ATTRIBUTE2,
238 USER_ATTRIBUTE3,
239 USER_ATTRIBUTE4,
240 USER_ATTRIBUTE5,
241 USER_ATTRIBUTE6,
242 USER_ATTRIBUTE7,
243 USER_ATTRIBUTE8,
244 USER_ATTRIBUTE9,
245 USER_ATTRIBUTE10,
246 USER_ATTRIBUTE11,
247 USER_ATTRIBUTE12,
248 USER_ATTRIBUTE13,
249 USER_ATTRIBUTE14,
250 USER_ATTRIBUTE15,
251 NVL(USER_FK1,'NA_EDW'),
252 NVL(USER_FK2,'NA_EDW'),
253 NVL(USER_FK3,'NA_EDW'),
254 NVL(USER_FK4,'NA_EDW'),
255 NVL(USER_FK5,'NA_EDW'),
256 NVL(USER_HOLD_FK,'NA_EDW'),
257 USER_MEASURE1,
258 USER_MEASURE2,
259 USER_MEASURE3,
260 USER_MEASURE4,
261 USER_MEASURE5,
262 NULL, -- OPERATION_CODE
263 collection_status
264 from POA_EDW_CONTRACT_AGRMNTS_FCV
265 WHERE view_id = p_view_id
266 AND seq_id = p_seq_id;
267
268 l_rows_inserted := sql%rowcount;
269 l_duration := sysdate - l_temp_date;
270
271 edw_log.put_line('...Inserted ' || to_char(nvl(l_rows_inserted,0)) ||
272 ' rows into the local staging table');
273 edw_log.put_line('Process Time: ' || edw_log.duration(l_duration));
274 edw_log.put_line(' ');
275
276 RETURN (l_rows_inserted);
277
278 EXCEPTION
279 WHEN OTHERS THEN
280 g_errbuf := sqlerrm;
281 g_retcode := sqlcode;
282 RETURN(-1);
283
284 END;
285
286
287 -----------------------------------------------------------
288 -- PROCEDURE DELETE_DUPLICATES
289 -----------------------------------------------------------
290
291 PROCEDURE DELETE_DUPLICATES IS
292
293 -- Cursor to delete duplicates
294 CURSOR Dup_Rec IS
295 SELECT primary_key
296 FROM poa_edw_contract_inc
297 ORDER BY primary_key
298 FOR UPDATE;
299
300 v_prev_id NUMBER;
301 v_cur_id NUMBER;
302
303 BEGIN
304 OPEN Dup_Rec;
305 LOOP
306
307 FETCH Dup_Rec INTO v_cur_id;
308 exit when Dup_Rec % NOTFOUND;
309
310 -- Check if the PK already exists
311 IF (v_prev_id = v_cur_id) THEN
312 DELETE FROM poa_edw_contract_inc
313 WHERE CURRENT OF Dup_Rec;
314 ELSE
315 v_prev_id := v_cur_id;
316 END IF;
317 END LOOP;
318
319 close Dup_Rec;
320 EXCEPTION
321 WHEN OTHERS THEN
322 IF Dup_Rec%ISOPEN THEN
323 close Dup_Rec;
324 END IF;
325 END;
326
327
328 ---------------------------------------------------
329 -- FUNCTION IDENTIFY_CHANGE1
330 ---------------------------------------------------
331
332 FUNCTION IDENTIFY_CHANGE1 (p_view_id IN NUMBER,
333 p_count OUT NOCOPY NUMBER) RETURN NUMBER IS
334
335 l_seq_id NUMBER := -1;
336 l_poa_schema VARCHAR2(30);
337 l_status VARCHAR2(30);
338 l_industry VARCHAR2(30);
339
340 BEGIN
341
342 p_count := 0;
343 select poa_edw_contract_inc_s.nextval into l_seq_id from dual;
344
345 /** Update the seq_id for records that had missing currency rates in
346 the earlier PUSH. We need to repush these records again
347 **/
348
349 UPDATE poa_edw_contract_inc
350 SET seq_id = l_seq_id
351 WHERE seq_id IS NULL;
352
353 p_count := sql%rowcount;
354 edw_log.put_line( 'Updated ' || p_count || ' records');
355
356 INSERT INTO poa_edw_contract_inc(primary_key, seq_id)
357 SELECT po_header_id, l_seq_id
358 FROM PO_HEADERS_ALL
359 WHERE type_lookup_code in ('CONTRACT', 'BLANKET')
360 AND approved_flag = 'Y'
361 AND last_update_date between g_push_date_range1 and g_push_date_range2;
362
363 p_count := p_count + sql%rowcount;
364
365 RETURN (l_seq_id);
366
367 EXCEPTION
368 WHEN OTHERS THEN
369 g_errbuf:=sqlerrm;
370 g_retcode:=sqlcode;
371 RETURN(-1);
372
373 END;
374
375 -- ---------------------------------
376 -- PUBLIC PROCEDURES
377 -- ---------------------------------
378
379 -----------------------------------------------------------
380 -- PROCEDURE PUSH
381 -----------------------------------------------------------
382
383 Procedure Push(Errbuf in out NOCOPY Varchar2,
384 Retcode in out NOCOPY Varchar2,
385 p_from_date IN Varchar2,
386 p_to_date IN Varchar2) IS
387
388 l_fact_name Varchar2(30) := 'POA_EDW_CONTRACT_F';
389 l_staging_table Varchar2(30) := 'POA_EDW_CONTRACT_FSTG';
390
391 -- -------------------------------------------
392 -- Put any additional developer variables here
393 -- -------------------------------------------
394
395 l_temp_date DATE:=NULL;
396 l_duration NUMBER:=0;
397 l_exception_msg VARCHAR2(2000):=NULL;
398 l_seq_id NUMBER := -1;
399 l_row_count NUMBER := 0;
400 l_row_count1 NUMBER := 0;
401
402 l_push_local_failure EXCEPTION;
403 l_iden_change_failure EXCEPTION;
404
405 l_from_date date;
406 l_to_date date;
407
408 my_payment_currency Varchar2(2000):=NULL;
409 my_rate_date Varchar2(2000) := NULL;
410 my_collection_status Varchar2(2000):=NULL;
411
412 -- Cursor to get Missing rates
413 CURSOR Invalid_Rates IS
414 SELECT DISTINCT NVL(poh.rate_date, poh.creation_date) Rate_Date,
415 decode(poh.rate_type,
416 'User',gsob.currency_code,
417 NVL(poh.currency_code,
418 gsob.currency_code)) From_Currency,
419 fstg.Collection_Status
420 FROM POA_EDW_CONTRACT_FSTG fstg,
421 PO_HEADERS_ALL POH,
422 GL_SETS_OF_BOOKS GSOB,
423 FINANCIALS_SYSTEM_PARAMS_ALL FSP
424 where (fstg.COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR
425 fstg.COLLECTION_STATUS = 'INVALID CURRENCY')
426 AND fstg.PO_HEADER_ID = POH.PO_HEADER_ID
427 AND nvl(POH.ORG_ID, -999) = nvl(FSP.ORG_ID, -999)
428 AND FSP.set_of_books_id = GSOB.set_of_books_id;
429
430 Begin
431 Errbuf :=NULL;
432 Retcode:=0;
433
434 l_from_date := to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
435 l_to_date := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
436
437 IF (Not EDW_COLLECTION_UTIL.setup(l_fact_name, l_staging_table,
438 l_staging_table, l_exception_msg)) THEN
439 errbuf := fnd_message.get;
440 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
441 END IF;
442
443 g_push_date_range1 := nvl(l_from_date,
444 EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
445 g_push_date_range2 := nvl(l_to_date,EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
446
447 edw_log.put_line( 'The collection range is from '||
448 to_char(g_push_date_range1, 'MM/DD/YYYY HH24:MI:SS')||' to '||
449 to_char(g_push_date_range2, 'MM/DD/YYYY HH24:MI:SS'));
450 edw_log.put_line(' ');
451
452 l_temp_date := sysdate;
453
454 -- --------------------------------------------
455 -- Identify Change
456 -- --------------------------------------------
457 edw_log.put_line(' ');
458 edw_log.put_line('Identifying changes...');
459 l_seq_id := IDENTIFY_CHANGE1 (1, l_row_count);
460
461 if (l_seq_id = -1) THEN
462 RAISE l_iden_change_failure;
463 end if;
464 edw_log.put_line('Identified ' || l_row_count || ' changed records');
465
466 -- -------------------------------------------
467 -- Delete delicates in the Inc Table
468 -- --------------------------------------------
469 DELETE_DUPLICATES;
470 edw_log.put_line('Duplicate records deleted in Inc Table');
471
472 -- --------------------------------------------
473 -- Push to local staging table for view type 1
474 -- --------------------------------------------
475
476 edw_log.put_line(' ');
477 edw_log.put_line('Inserting into local staging table for view type 1');
478 l_row_count1 := PUSH_TO_LOCAL(1, l_seq_id);
479
480 IF (l_row_count1 = -1) THEN RAISE L_push_local_failure; END IF;
481
482 edw_log.put_line('Inserted '|| nvl(l_row_count1, 0) ||
483 ' rows into the local staging table for view type 1');
484 edw_log.put_line(' ');
485
486 -- --------------------------------------------
487 -- Delete all incremental tables' record
488 -- --------------------------------------------
489
490 TRUNCATE_INC;
491
492 edw_log.put_line(' ');
493 edw_log.put_line('truncated Increment Table');
494 edw_log.put_line(' ');
495
496 -- --------------------------------------------
497 -- Insert Missing Rates from Local Staging Into Inc Tables
498 -- to repush them next time
499 -- --------------------------------------------
500 INSERT_MISSING_RATES;
501
502 edw_log.put_line(' ');
503 edw_log.put_line('Checked records for Missing Rates');
504 edw_log.put_line(' ');
505
506 OPEN Invalid_Rates;
507 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
508 'FROM CURRENCY CONVERSION DATE COLLECTION STATUS');
509 loop
510 FETCH Invalid_Rates INTO my_rate_date, my_payment_currency,
511 my_collection_status;
512 exit when Invalid_Rates % NOTFOUND;
513 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, my_payment_currency ||
514 ' '|| my_rate_date ||
515 ' '|| my_collection_status);
516 end loop;
517
518 close Invalid_Rates;
519
520 edw_log.put_line(' ');
521 edw_log.put_line('Report created for records with Missing Rates');
522 edw_log.put_line(' ');
523
524 -- --------------------------------------------
525 -- Delete records with missing rates from local staging table
526 -- --------------------------------------------
527 DELETE_STG_MISSING_RATES;
528
529 -- --------------------------------------------
530 -- No exception raised so far. Call wrapup to transport
531 -- data to target database, and insert messages into logs
532 -- -----------------------------------------------
533 g_row_count := g_row_count + l_row_count1;
534 edw_log.put_line(' ');
535 edw_log.put_line('Inserted '||nvl(g_row_count,0)||
536 ' rows into the staging table');
537 l_duration := sysdate - l_temp_date;
538 edw_log.put_line(' ');
539 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
540 edw_log.put_line(' ');
541
542 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count,
543 P_PERIOD_START => g_push_date_range1,
544 P_PERIOD_END => g_push_date_range2);
545
546 EXCEPTION
547
548 WHEN L_PUSH_LOCAL_FAILURE THEN
549 Errbuf:=g_errbuf;
550 Retcode:=g_retcode;
551
552 if (Invalid_Rates%ISOPEN) THEN
553 close Invalid_Rates;
554 end if;
555
556 l_exception_msg := Retcode || ':' || Errbuf;
557 rollback; -- Rollback insert into local staging
558 edw_log.put_line('Inserting into local staging have failed');
559 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
560 g_push_date_range1, g_push_date_range2);
561 raise;
562
563 WHEN L_IDEN_CHANGE_FAILURE THEN
564 Errbuf:=g_errbuf;
565 Retcode:=g_retcode;
566
567 if (Invalid_Rates%ISOPEN) THEN
568 close Invalid_Rates;
569 end if;
570
571 l_exception_msg := Retcode || ':' || Errbuf;
572 TRUNCATE_INC;
573 edw_log.put_line('Identifying changed records have Failed');
574 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
575 g_push_date_range1, g_push_date_range2);
576 raise;
577
578 WHEN OTHERS THEN
579 Errbuf:=g_errbuf;
580 Retcode:=g_retcode;
581
582 if (Invalid_Rates%ISOPEN) THEN
583 close Invalid_Rates;
584 end if;
585
586 l_exception_msg := Retcode || ':' || Errbuf;
587 rollback;
588 edw_log.put_line('Other errors');
589 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
590 g_push_date_range1, g_push_date_range2);
591 raise;
592
593 End;
594 End POA_EDW_CONTRACT_F_C;