[Home] [Help]
PACKAGE BODY: APPS.FII_AR_TRX_DIST_F_C
Source
1 Package Body FII_AR_TRX_DIST_F_C AS
2 /* $Header: FIIAR06B.pls 120.8 2006/01/18 11:32:42 sgautam noship $ */
3
4 g_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('EDW_DEBUG'), 'N');
5 g_errbuf VARCHAR2(2000) := NULL;
6 g_retcode VARCHAR2(200) := NULL;
7 g_row_count NUMBER:=0;
8 g_push_from_date DATE := NULL;
9 g_push_to_date DATE := NULL;
10 g_seq_id NUMBER:=0;
11 g_missing_rates Number:=0;
12 g_acct_or_inv_date NUMBER; -- Added for for Currency Conversion Date Enhancement
13 G_TABLE_NOT_EXIST EXCEPTION;
14 PRAGMA EXCEPTION_INIT(G_TABLE_NOT_EXIST, -942);
15
16 -- ---------------------------------
17 -- PRIVATE PROCEDURES AND FUNCTIONS
18 -- ---------------------------------
19
20 ---------------------------------------------------
21 -- PROCEDURE DROP_TABLE
22 ---------------------------------------------------
23 procedure drop_table (p_table_name in varchar2) is
24 l_stmt varchar2(400);
25 Begin
26
27 l_stmt:='drop table '|| p_table_name;
28
29 if g_debug_flag = 'Y' then
30 edw_log.put_line('');
31 edw_log.put_line('Dropping temp table '||p_table_name);
32 edw_log.debug_line('Going to execute statement:');
33 edw_log.debug_line(l_stmt);
34 end if;
35
36 execute immediate l_stmt;
37
38 Exception
39 WHEN G_TABLE_NOT_EXIST THEN
40 null; -- Oracle 942, table does not exist, no actions
41 WHEN OTHERS THEN
42 g_errbuf:=sqlerrm;
43 g_retcode:=sqlcode;
44 if g_debug_flag = 'Y' then
45 edw_log.put_line('Error in drop_table procedure');
46 end if;
47 raise;
48 End;
49
50
51 ------------------------------------------------------------
52 --PROCEDURE INSERT_INTO_MISSING_RATES
53 -------------------------------------------------------------
54 --Identify records that have missing rates and insert them in a temp table
55
56 PROCEDURE INSERT_INTO_MISSING_RATES
57 IS
58
59 BEGIN
60 INSERT INTO fii_ar_trx_msng_rt(
61 Primary_Key1,
62 Primary_Key2,
63 Primary_Key3 -- SLA Uptake
64 )
65 SELECT
66 TO_NUMBER(decode(substr(INVOICE_PK,1,2), 'D-',INVOICE_DIST_ID,
67 'OD',INVOICE_DIST_ID,
68 'OC',INVOICE_DIST_ID,
69 'R-',INVOICE_DIST_ID, NULL)),
70 TO_NUMBER(decode(substr(INVOICE_PK,1,2), 'A-',INVOICE_ID, NULL)),
71 fat.account_id -- SLA Uptake
72
73 FROM FII_AR_TRX_DIST_FSTG fat
74
75 WHERE
76 fat.COLLECTION_STATUS in ('RATE NOT AVAILABLE', 'INVALID CURRENCY');
77
78 IF (sql%rowcount > 0) THEN
79 g_retcode := 1;
80 g_missing_rates := 1;
81 END IF;
82 -- Generates "Warning" message in the Status column of Concurrent Manager "Requests" table
83
84 if g_debug_flag = 'Y' then
85 edw_log.put_line(' ');
86 edw_log.put_line('INSERTING ' || to_char(sql%rowcount) || ' rows into fii_ar_trx_msng_rt temp table');
87 edw_log.put_line('g_retcode is '||g_retcode);
88 edw_log.put_line('g_missing_rates is '||g_missing_rates);
89 end if;
90 END;
91
92 ---------------------------------------------------
93 -- PROCEDURE CREATE_ITEM_ORG_TEMP
94 ---------------------------------------------------
95 procedure create_item_org_temp(table_name IN VARCHAR2) is
96 l_stmt varchar2(15000);
97 l_stage varchar2(30);
98
99 begin
100
101 drop_table(table_name);
102
103 -- --------------------------------------------------------
104 -- Had to remove the parallel sub clause to avoid ora 7260
105 -- --------------------------------------------------------
106 l_stage:='Creating ';
107
108 l_stmt:= 'create table '|| table_name ||' storage (initial 5M next 1M pctincrease 0)
109 parallel (degree 4) '||
110 'as select rev.item_fk INVENTORY_ITEM_ID, ' ||
111 ' NULL ORDER_LINE_ID, ' ||
112 ' DECODE(mi.inventory_item_id, NULL, ''NA_EDW'', ' ||
113 ' mi.inventory_item_id || ''-'' || to_char(max(mi.organization_id)) || ' ||
114 ' ''-'' || rev.instance || ''-IORG'') ITEM_FK, ' ||
115 ' NULL UOM_FK ' ||
116 'FROM FII_AR_TRX_DIST_Fstg rev, mtl_system_items_b mi ' ||
117 'WHERE to_number(rev.item_fk) = mi.inventory_item_id (+) ' ||
118 'AND rev.item_fk not like ''%-%'' ' ||
119 'AND rev.item_fk <> ''NA_EDW'' ' ||
120 'GROUP BY rev.item_fk, mi.inventory_item_id, rev.instance ' ||
121 'UNION ' ||
122 'SELECT NULL INVENTORY_ITEM_ID, ' ||
123 ' to_char(ood.line_id) ORDER_LINE_ID, ' ||
124 ' decode(msi.inventory_item_id, NULL, ''NA_EDW'', ' ||
125 ' msi.inventory_item_id||''-''||iwm.mtl_organization_id||''-''|| ' ||
126 ' rev.instance||''-IORG'') ITEM_FK, ' ||
127 ' edw_util.get_edw_base_uom(msi.primary_uom_code, ' ||
128 ' msi.inventory_item_id) UOM_FK ' ||
129 'FROM FII_AR_TRX_DIST_Fstg rev, op_ordr_dtl ood, ic_whse_mst iwm, ' ||
130 ' ic_item_mst iim, mtl_system_items msi ' ||
131 'WHERE rev.interface_line_context = ''GEMMS OP'' ' ||
132 'AND rev.item_fk like ''OPM-%'' ' ||
133 'AND substr(rev.order_line_id,1,instr(rev.order_line_id,''-'',1)-1)= ' ||
134 'to_char(ood.line_id) ' ||
135 'AND ood.item_id = iim.item_id ' ||
136 'AND ood.from_whse = iwm.whse_code ' ||
137 'AND iim.item_no = msi.segment1 ' ||
138 'AND iwm.mtl_organization_id = msi.organization_id';
139
140 if g_debug_flag = 'Y' then
141 edw_log.put_line('');
142 edw_log.put_line('Creating temp table '||table_name);
143 edw_log.debug_line('Going to execute statement:');
144 edw_log.debug_line(l_stmt);
145 end if;
146
147 execute immediate l_stmt;
148 commit;
149
150 l_stage:='Creating index for ';
151 l_stmt:='Create index ' || table_name || '_u' || ' on ' || table_name ||
152 '(inventory_item_id)';
153
154 if g_debug_flag = 'Y' then
155 edw_log.put_line('');
156 edw_log.put_line('Creating index ' || table_name || '_u');
157 edw_log.debug_line('Going to execute statement: ');
158 edw_log.debug_line(l_stmt);
159 end if;
160 execute immediate l_stmt;
161 if g_debug_flag = 'Y' then
162 edw_log.debug_line('Create index ' || table_name || '_u successfully');
163 end if;
164
165 l_stage:='Creating index for ';
166 l_stmt:='Create index ' || table_name || '_u2' || ' on ' || table_name ||
167 '(order_line_id)';
168
169 if g_debug_flag = 'Y' then
170 edw_log.put_line('');
171 edw_log.put_line('Creating index ' || table_name || '_u2');
172 edw_log.debug_line('Going to execute statement: ');
173 edw_log.debug_line(l_stmt);
174 end if;
175 execute immediate l_stmt;
176 if g_debug_flag = 'Y' then
177 edw_log.debug_line('Create index ' || table_name || '_u2 successfully');
178 end if;
179
180 commit;
181
182 exception
183 when others then
184 if g_debug_flag = 'Y' then
185 edw_log.put_line('error: '|| l_stage || table_name ||' table.');
186 edw_log.put_line('Dropping table ' || table_name);
187 end if;
188 drop_table(table_name);
189 commit;
190 raise;
191 end;
192
193 ---------------------------------------------------
194 -- PROCEDURE UPDATE_ITEM_FK
195 ---------------------------------------------------
196 FUNCTION update_item_fk RETURN NUMBER is
197 l_stmt VARCHAR2(6000);
198 l_row NUMBER;
199 l_table_name VARCHAR2(40) := 'FII_AR_DL_TEMP_TABLE';
200 Begin
201
202 create_item_org_temp(l_table_name);
203 if g_debug_flag = 'Y' then
204 edw_log.debug_line('Ready to Update Item FK' );
205 end if;
206 l_stmt:= 'UPDATE FII_AR_TRX_DIST_FSTG rev ' ||
207 'SET (rev.uom_fk, rev.item_fk, rev.parent_item_fk) = ' ||
208 ' (SELECT DECODE(rev.interface_line_context, ''GEMMS OP'', ' ||
209 ' NVL(temp.uom_fk, ''NA_EDW''), rev.uom_fk) UOM_FK, ' ||
210 'NVL(temp.item_fk, ''NA_EDW'') ITEM_FK, ' ||
211 'NVL(temp.item_fk, ''NA_EDW'') PARENT_ITEM_FK ' ||
212 'FROM ' || l_table_name || ' temp ' ||
213 'WHERE (temp.inventory_item_id = rev.item_fk ' ||
214 'OR (substr(rev.order_line_id,1,instr(rev.order_line_id,''-'',1)-1)= ' ||
215 ' temp.order_line_id ' ||
216 ' AND rev.interface_line_context = ''GEMMS OP''))) ' ||
217 'WHERE rev.item_fk <> ''NA_EDW'' ' ||
218 'AND rev.collection_status = ''LOCAL READY'' ' ||
219 'AND INSTANCE = (SELECT INSTANCE_CODE FROM EDW_LOCAL_INSTANCE) ' ||
220 'AND rev.item_fk NOT LIKE ''%-IORG'' ';
221
222
223 if g_debug_flag = 'Y' then
224 edw_log.put_line('');
225 edw_log.put_line('Updating item_fk');
226 edw_log.debug_line('Going to execute statement: ');
227 edw_log.debug_line(l_stmt);
228 end if;
229
230 execute immediate l_stmt;
231 l_row := SQL%ROWCOUNT;
232
233 drop_table(l_table_name);
234
235 return(l_row);
236
237 exception
238 WHEN OTHERS THEN
239 g_errbuf:=sqlerrm;
240 g_retcode:=sqlcode;
241 drop_table(l_table_name);
242 return(-1);
243 end;
244
245 ---------------------------------------------------
246 -- FUNCTION WAIT_FOR_REQUEST
247 ---------------------------------------------------
248
249 FUNCTION WAIT_FOR_REQUEST(p_request_id IN NUMBER,
250 p_dev_phase OUT NOCOPY VARCHAR2,
251 p_dev_status OUT NOCOPY VARCHAR2
252 ) RETURN BOOLEAN
253 IS
254
255 l_phase VARCHAR2(30);
256 l_status VARCHAR2(30);
257 l_message VARCHAR2(30);
258
259 BEGIN
260
261 RETURN FND_CONCURRENT.WAIT_FOR_REQUEST
262 ( p_request_id,
263 10,
264 3600 * 10, -- 10 hour, need to change later
265 l_phase,
266 l_status,
267 p_dev_phase,
268 p_dev_status,
269 l_message);
270
271
272 END;
273
274
275 ---------------------------------------------------
276 -- FUNCTION SUBMIT_REQUEST
277 ---------------------------------------------------
278
279 FUNCTION SUBMIT_REQUEST(p_view_type VARCHAR2,
280 p_req_id NUMBER) RETURN NUMBER
281 IS
282
283 l_request_id NUMBER;
284
285 BEGIN
286
287 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
288 'FII',
289 'FII_AR_TRX_DIST_F_WORKER',
290 NULL,
291 NULL,
292 FALSE, -- sub request,may need to set true
293 to_char(g_push_from_date,'YYYY/MM/DD HH24:MI:SS'),
294 to_char(g_push_to_date,'YYYY/MM/DD HH24:MI:SS'),
295 p_view_type,
296 to_char(p_req_id));
297
298 IF (l_request_id = 0) THEN
299 g_errbuf:=sqlerrm;
300 g_retcode:=sqlcode;
301 RETURN (-1); -- request failed
302 END IF;
303
304 RETURN l_request_id;
305
306 END SUBMIT_REQUEST;
307
308
309 -----------------------------------------------------------
310 -- PROCEDURE TRUNCATE_TABLE
311 -----------------------------------------------------------
312
313 PROCEDURE TRUNCATE_TABLE (table_name varchar2)
314 IS
315
316 l_fii_schema VARCHAR2(30);
317 l_stmt VARCHAR2(200);
318 l_status VARCHAR2(30);
319 l_industry VARCHAR2(30);
320
321 BEGIN
322
323 IF (FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, l_fii_schema)) THEN
324 l_stmt := 'TRUNCATE TABLE ' || l_fii_schema ||'.'||table_name;
325 EXECUTE IMMEDIATE l_stmt;
326 END IF;
327 if g_debug_flag = 'Y' then
328 edw_log.put_line(' ');
329 edw_log.put_line('Truncating '|| table_name||' table');
330 end if;
331
332 END;
333
334
335 -----------------------------------------------------------
336 -- PROCEDURE DELETE_STG
337 -----------------------------------------------------------
338
339 PROCEDURE DELETE_STG
340 IS
341
342 BEGIN
343
344 DELETE FII_AR_TRX_DIST_FSTG
345 WHERE COLLECTION_STATUS = 'LOCAL READY' OR (COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR COLLECTION_STATUS = 'INVALID CURRENCY')
346 AND INSTANCE = (SELECT INSTANCE_CODE
347 FROM EDW_LOCAL_INSTANCE);
348
349 END;
350
351 --------------------------------------------------
352 --FUNCTION LOCAL_SAME_AS_REMOTE
353 ---------------------------------------------------
354
355 FUNCTION LOCAL_SAME_AS_REMOTE RETURN BOOLEAN
356 IS
357
358 l_instance1 Varchar2(100) :=Null;
359 l_instance2 Varchar2(100) :=Null;
360
361 BEGIN
362
363
364 SELECT instance_code
365 INTO l_instance1
366 FROM edw_local_instance;
367
368 SELECT instance_code
369 INTO l_instance2
370 FROM edw_local_instance@edw_apps_to_wh;
371
372 IF (l_instance1 = l_instance2) THEN
373 RETURN TRUE;
374 END IF;
375
376 RETURN FALSE;
377
378 EXCEPTION
379 WHEN NO_DATA_FOUND THEN
380
381 RETURN FALSE;
382
383 END;
384
385
386 --------------------------------------------------
387 --PROCEDURE SET_STATUS_READY
388 ---------------------------------------------------
389
390 FUNCTION SET_STATUS_READY RETURN NUMBER
391 IS
392
393 BEGIN
394
395 UPDATE FII_AR_TRX_DIST_FSTG
396 SET COLLECTION_STATUS = 'READY'
397 WHERE COLLECTION_STATUS = 'LOCAL READY'
398 AND INSTANCE = (SELECT INSTANCE_CODE
399 FROM EDW_LOCAL_INSTANCE);
400
401 RETURN(sql%rowcount);
402
403 EXCEPTION
404 WHEN OTHERS THEN
405 g_errbuf:=sqlerrm;
406 g_retcode:=sqlcode;
407 RETURN(-1);
408
409 END;
410
411
412 -----------------------------------------------------------
413 --PROCEDURE PUSH_TO_LOCAL
414 -----------------------------------------------------------
415
416 FUNCTION PUSH_TO_LOCAL(p_view_type VARCHAR2) RETURN NUMBER IS
417 l_mau NUMBER;
418
419
420 BEGIN
421
422 l_mau := nvl(edw_currency.get_mau, 0.01 );
423
424 -- ------------------------------------------------
425 -- We set the COLLECTION_STATUS to 'LOCAL READY'.
426 -- In case of source=target, we need to separate
427 -- out the records in progress vs the records which
428 -- is ready to be picked up by collection enginee.
429 -- In our case, we consider the records to be in
430 -- progress until all the child processes have
431 -- completed successfully.
432 -- ------------------------------------------------
433
434 Insert Into FII_AR_TRX_DIST_FSTG(
435 ACCOUNT_ID,
436 ACCOUNT_CLASS,
437 ACCOUNT_TYPE,
438 AGREEMENT_NAME,
439 AGREEMENT_TYPE,
440 AMT_B,
441 AMT_G,
445 CAMPAIGN_ACTL_FK,
442 AMT_T,
443 BILL_TO_CUSTOMER_FK,
444 BILL_TO_SITE_FK,
446 CAMPAIGN_INIT_FK,
447 CAMPAIGN_STATUS_ACTL_FK,
448 CAMPAIGN_STATUS_INIT_FK,
449 CELL_ACTL_FK,
450 CELL_INIT_FK,
451 COMPANY_CC_ORG_FK,
452 END_USER_CUSTOMER_FK,
453 EVENT_OFFER_ACTL_FK,
454 EVENT_OFFER_INIT_FK,
455 EVENT_OFFER_REG_FK,
456 EXCHANGE_DATE,
457 EXCHANGE_RATE,
458 EXCHANGE_RATE_TYPE,
459 FUNCTIONAL_CURRENCY_FK,
460 GL_ACCT1_FK,
461 GL_ACCT2_FK,
462 GL_ACCT3_FK,
463 GL_ACCT4_FK,
464 GL_ACCT5_FK,
465 GL_ACCT6_FK,
466 GL_ACCT7_FK,
467 GL_ACCT8_FK,
468 GL_ACCT9_FK,
469 GL_ACCT10_FK,
470 GL_DATE,
471 GL_DATE_FK,
472 INSTANCE,
473 INSTANCE_FK,
474 INTERFACE_LINE_CONTEXT,
475 INTERNAL_FLAG,
476 INVOICE_DATE,
477 INVOICE_DATE_FK,
478 INVOICE_DIST_ID,
479 INVOICE_ID,
480 INVOICE_LINE_ID,
481 INVOICE_LINE_NUMBER,
482 INVOICE_LINE_MEMO,
483 INVOICE_NUMBER,
484 INVOICE_REASON,
485 INVOICE_SOURCE_NAME,
486 ITEM_FK,
487 QTY_CREDITED,
488 INVOICE_LINE_QTY,
489 ORDER_LINE_QTY,
490 LINE_TYPE,
491 MARKET_SEGMENT_FK,
492 MEDIA_ACTL_FK,
493 MEDIA_INIT_FK,
494 OFFER_ACTL_FK,
495 OFFER_INIT_FK,
496 ORDER_LINE_ID,
497 ORGANIZATION_FK,
498 ORIGINAL_INVOICE_ID,
499 ORIGINAL_INVOICE_LINE_ID,
500 ORIGINAL_INVOICE_NUM,
501 ORIGINAL_INVOICE_LINE_NUM,
502 PARENT_ITEM_FK,
503 PAYMENT_TERM_FK,
504 prim_salesrep_fk,
505 PRIM_SALESRESOURCE_FK,
506 PROCESS_TYPE,
507 PROJECT_FK,
508 RESELLER_CUSTOMER_FK,
509 INVOICE_PK,
510 SALES_ORDER_DATE_FK,
511 SALES_ORDER_LINE_NUMBER,
512 SALES_ORDER_NUMBER,
513 SALES_ORDER_SOURCE,
514 SALESCHANNEL_FK,
515 salesrep_fk,
516 SALESRESOURCE_FK,
517 SET_OF_BOOKS_FK,
518 SHIP_TO_CUSTOMER_FK,
519 SHIP_TO_SITE_FK,
520 SIC_CODE_FK,
521 SO_LINE_SELLING_PRICE,
522 SOLD_TO_CUSTOMER_FK,
523 SOLD_TO_SITE_FK,
524 SOURCE_LIST_FK,
525 TRANSACTION_CURRENCY_FK,
526 UNIT_SELLING_PRICE,
527 UOM_FK,
528 GL_POSTED_DATE,
529 TRANSACTION_STATUS,
530 TRANSACTION_CLASS,
531 USER_ATTRIBUTE1,
532 USER_ATTRIBUTE2,
533 USER_ATTRIBUTE3,
534 USER_ATTRIBUTE4,
535 USER_ATTRIBUTE5,
536 USER_ATTRIBUTE6,
537 USER_ATTRIBUTE7,
538 USER_ATTRIBUTE8,
539 USER_ATTRIBUTE9,
540 USER_ATTRIBUTE10,
541 USER_ATTRIBUTE11,
542 USER_ATTRIBUTE12,
543 USER_ATTRIBUTE13,
544 USER_ATTRIBUTE14,
545 USER_ATTRIBUTE15,
546 USER_ATTRIBUTE16,
547 USER_ATTRIBUTE17,
548 USER_ATTRIBUTE18,
549 USER_ATTRIBUTE19,
550 USER_ATTRIBUTE20,
551 USER_ATTRIBUTE21,
552 USER_ATTRIBUTE22,
553 USER_ATTRIBUTE23,
554 USER_ATTRIBUTE24,
555 USER_ATTRIBUTE25,
556 USER_FK1,
557 USER_FK2,
558 USER_FK3,
559 USER_FK4,
560 USER_FK5,
561 USER_MEASURE1,
562 USER_MEASURE2,
563 USER_MEASURE3,
564 USER_MEASURE4,
565 USER_MEASURE5,
566 CREATION_DATE,
567 LAST_UPDATE_DATE,
568 OPERATION_CODE,
569 COLLECTION_STATUS)
570 SELECT
571 ACCOUNT_ID,
572 ACCOUNT_CLASS,
573 ACCOUNT_TYPE,
574 AGREEMENT_NAME,
575 AGREEMENT_TYPE,
576 AMT_B,
577 round(( AMT_B * GLOBAL_CURRENCY_RATE)/l_mau) * l_mau,
578 AMT_T,
579 BILL_TO_CUSTOMER_FK,
580 BILL_TO_SITE_FK,
581 CAMPAIGN_ACTL_FK,
582 CAMPAIGN_INIT_FK,
583 CAMPAIGN_STATUS_ACTL_FK,
584 CAMPAIGN_STATUS_INIT_FK,
585 CELL_ACTL_FK,
586 CELL_INIT_FK,
587 'NA_EDW', -- COMPANY_CC_ORG_FK,
588 END_USER_CUSTOMER_FK,
589 EVENT_OFFER_ACTL_FK,
590 EVENT_OFFER_INIT_FK,
591 EVENT_OFFER_REG_FK,
592 EXCHANGE_DATE,
593 EXCHANGE_RATE,
594 EXCHANGE_RATE_TYPE,
595 FUNCTIONAL_CURRENCY_FK,
596 GL_ACCT1_FK,
597 GL_ACCT2_FK,
598 GL_ACCT3_FK,
599 GL_ACCT4_FK,
600 GL_ACCT5_FK,
601 GL_ACCT6_FK,
602 GL_ACCT7_FK,
603 GL_ACCT8_FK,
604 GL_ACCT9_FK,
605 GL_ACCT10_FK,
606 GL_DATE,
607 GL_DATE_FK,
608 INSTANCE,
609 INSTANCE_FK,
610 INTERFACE_LINE_CONTEXT,
611 INTERNAL_FLAG,
612 INVOICE_DATE,
613 INVOICE_DATE_FK,
614 INVOICE_DIST_ID,
615 INVOICE_ID,
616 INVOICE_LINE_ID,
617 INVOICE_LINE_NUMBER,
618 INVOICE_LINE_MEMO,
619 INVOICE_NUMBER,
620 INVOICE_REASON,
621 INVOICE_SOURCE_NAME,
622 ITEM_FK,
623 QTY_CREDITED_NC * UOM_CONV_RATE,
624 INVOICE_LINE_QTY_NC * UOM_CONV_RATE,
628 MEDIA_ACTL_FK,
625 ORDER_LINE_QTY_NC * UOM_CONV_RATE,
626 LINE_TYPE,
627 MARKET_SEGMENT_FK,
629 MEDIA_INIT_FK,
630 OFFER_ACTL_FK,
631 OFFER_INIT_FK,
632 ORDER_LINE_ID,
633 ORGANIZATION_FK,
634 ORIGINAL_INVOICE_ID,
635 ORIGINAL_INVOICE_LINE_ID,
636 ORIGINAL_INVOICE_NUM,
637 ORIGINAL_INVOICE_LINE_NUM,
638 PARENT_ITEM_FK,
639 PAYMENT_TERM_FK,
640 prim_salesresource_fk, -- 'NA_EDW',
641 'NA_EDW', -- PRIM_SALESRESOURCE_FK,
642 PROCESS_TYPE,
643 PROJECT_FK,
644 RESELLER_CUSTOMER_FK,
645 INVOICE_PK,
646 SALES_ORDER_DATE_FK,
647 SALES_ORDER_LINE_NUMBER,
648 SALES_ORDER_NUMBER,
649 SALES_ORDER_SOURCE,
650 SALESCHANNEL_FK,
651 salesresource_fk, -- 'NA_EDW',
652 'NA_EDW', -- SALESRESOURCE_FK,
653 SET_OF_BOOKS_FK,
654 SHIP_TO_CUSTOMER_FK,
655 SHIP_TO_SITE_FK,
656 SIC_CODE_FK,
657 SO_LINE_SELLING_PRICE,
658 SOLD_TO_CUSTOMER_FK,
659 SOLD_TO_SITE_FK,
660 SOURCE_LIST_FK,
661 TRANSACTION_CURRENCY_FK,
662 UNIT_SELLING_PRICE,
663 UOM_FK,
664 GL_POSTED_DATE,
665 TRANSACTION_STATUS,
666 TRANSACTION_CLASS,
667 USER_ATTRIBUTE1,
668 USER_ATTRIBUTE2,
669 USER_ATTRIBUTE3,
670 USER_ATTRIBUTE4,
671 USER_ATTRIBUTE5,
672 USER_ATTRIBUTE6,
673 USER_ATTRIBUTE7,
674 USER_ATTRIBUTE8,
675 USER_ATTRIBUTE9,
676 USER_ATTRIBUTE10,
677 USER_ATTRIBUTE11,
678 USER_ATTRIBUTE12,
679 USER_ATTRIBUTE13,
680 USER_ATTRIBUTE14,
681 USER_ATTRIBUTE15,
682 USER_ATTRIBUTE16,
683 USER_ATTRIBUTE17,
684 USER_ATTRIBUTE18,
685 USER_ATTRIBUTE19,
686 USER_ATTRIBUTE20,
687 USER_ATTRIBUTE21,
688 USER_ATTRIBUTE22,
689 USER_ATTRIBUTE23,
690 USER_ATTRIBUTE24,
691 USER_ATTRIBUTE25,
692 USER_FK1,
693 USER_FK2,
694 USER_FK3,
695 USER_FK4,
696 USER_FK5,
697 USER_MEASURE1,
698 USER_MEASURE2,
699 USER_MEASURE3,
700 USER_MEASURE4,
701 USER_MEASURE5,
702 sysdate,
703 sysdate,
704 NULL, -- OPERATION_CODE
705 decode(invoice_id,'NO_INV_ID','INVOICE_ID UNAVAILABLE',
706 decode(invoice_line_id,'NO_INV_LIN_ID','INVOICE_LINE_ID UNAVAILABLE',
707 decode(original_invoice_id,'NO_OR_INV_ID','ORIGINAL_INVOICE_ID UNAVAILABLE',
708 decode(original_invoice_line_id,'NO_OR_INV_LIN_ID','ORIGINAL_INVOICE_LINE_ID UNAVAILABLE',
709 decode(GLOBAL_CURRENCY_RATE,-1,'RATE NOT AVAILABLE',
710 -2,'INVALID CURRENCY','LOCAL READY')))))
711 FROM FII_AR_TRX_DIST_FCV
712 WHERE view_type = p_view_type
713 AND seq_id = g_seq_id;
714
715 RETURN(sql%rowcount);
716
717 EXCEPTION
718 WHEN OTHERS THEN
719 g_errbuf:=sqlerrm;
720 g_retcode:=sqlcode;
721 RETURN(-1);
722
723 END;
724
725
726 -----------------------------------------------------------
727 -- FUNCTION PUSH_REMOTE
728 -----------------------------------------------------------
729 FUNCTION PUSH_REMOTE RETURN NUMBER
730 IS
731
732 BEGIN
733
734 INSERT INTO FII_AR_TRX_DIST_FSTG@EDW_APPS_TO_WH(
735 ACCOUNT_ID,
736 ACCOUNT_CLASS,
737 ACCOUNT_TYPE,
738 AGREEMENT_NAME,
739 AGREEMENT_TYPE,
740 AMT_B,
741 AMT_G,
742 AMT_T,
743 BILL_TO_CUSTOMER_FK,
744 BILL_TO_SITE_FK,
745 CAMPAIGN_ACTL_FK,
746 CAMPAIGN_INIT_FK,
747 CAMPAIGN_STATUS_ACTL_FK,
748 CAMPAIGN_STATUS_INIT_FK,
749 CELL_ACTL_FK,
750 CELL_INIT_FK,
751 COMPANY_CC_ORG_FK,
752 END_USER_CUSTOMER_FK,
753 EVENT_OFFER_ACTL_FK,
754 EVENT_OFFER_INIT_FK,
755 EVENT_OFFER_REG_FK,
756 EXCHANGE_DATE,
757 EXCHANGE_RATE,
758 EXCHANGE_RATE_TYPE,
759 FUNCTIONAL_CURRENCY_FK,
760 GL_ACCT1_FK,
761 GL_ACCT2_FK,
762 GL_ACCT3_FK,
763 GL_ACCT4_FK,
764 GL_ACCT5_FK,
765 GL_ACCT6_FK,
766 GL_ACCT7_FK,
767 GL_ACCT8_FK,
768 GL_ACCT9_FK,
769 GL_ACCT10_FK,
770 GL_DATE,
771 GL_DATE_FK,
772 INSTANCE,
773 INSTANCE_FK,
774 INTERFACE_LINE_CONTEXT,
775 INTERNAL_FLAG,
776 INVOICE_DATE,
777 INVOICE_DATE_FK,
778 INVOICE_DIST_ID,
779 INVOICE_ID,
780 INVOICE_LINE_ID,
781 INVOICE_LINE_NUMBER,
782 INVOICE_LINE_MEMO,
783 INVOICE_NUMBER,
784 INVOICE_REASON,
785 INVOICE_SOURCE_NAME,
786 ITEM_FK,
787 QTY_CREDITED,
788 INVOICE_LINE_QTY,
789 ORDER_LINE_QTY,
790 LINE_TYPE,
791 MARKET_SEGMENT_FK,
792 MEDIA_ACTL_FK,
793 MEDIA_INIT_FK,
794 OFFER_ACTL_FK,
795 OFFER_INIT_FK,
799 ORIGINAL_INVOICE_LINE_ID,
796 ORDER_LINE_ID,
797 ORGANIZATION_FK,
798 ORIGINAL_INVOICE_ID,
800 ORIGINAL_INVOICE_NUM,
801 ORIGINAL_INVOICE_LINE_NUM,
802 PARENT_ITEM_FK,
803 PAYMENT_TERM_FK,
804 prim_salesrep_fk,
805 PRIM_SALESRESOURCE_FK,
806 PROCESS_TYPE,
807 PROJECT_FK,
808 RESELLER_CUSTOMER_FK,
809 INVOICE_PK,
810 SALES_ORDER_DATE_FK,
811 SALES_ORDER_LINE_NUMBER,
812 SALES_ORDER_NUMBER,
813 SALES_ORDER_SOURCE,
814 SALESCHANNEL_FK,
815 salesrep_fk,
816 SALESRESOURCE_FK,
817 SET_OF_BOOKS_FK,
818 SHIP_TO_CUSTOMER_FK,
819 SHIP_TO_SITE_FK,
820 SIC_CODE_FK,
821 SO_LINE_SELLING_PRICE,
822 SOLD_TO_CUSTOMER_FK,
823 SOLD_TO_SITE_FK,
824 SOURCE_LIST_FK,
825 TRANSACTION_CURRENCY_FK,
826 UNIT_SELLING_PRICE,
827 UOM_FK,
828 GL_POSTED_DATE,
829 TRANSACTION_STATUS,
830 TRANSACTION_CLASS,
831 USER_ATTRIBUTE1,
832 USER_ATTRIBUTE2,
833 USER_ATTRIBUTE3,
834 USER_ATTRIBUTE4,
835 USER_ATTRIBUTE5,
836 USER_ATTRIBUTE6,
837 USER_ATTRIBUTE7,
838 USER_ATTRIBUTE8,
839 USER_ATTRIBUTE9,
840 USER_ATTRIBUTE10,
841 USER_ATTRIBUTE11,
842 USER_ATTRIBUTE12,
843 USER_ATTRIBUTE13,
844 USER_ATTRIBUTE14,
845 USER_ATTRIBUTE15,
846 USER_ATTRIBUTE16,
847 USER_ATTRIBUTE17,
848 USER_ATTRIBUTE18,
849 USER_ATTRIBUTE19,
850 USER_ATTRIBUTE20,
851 USER_ATTRIBUTE21,
852 USER_ATTRIBUTE22,
853 USER_ATTRIBUTE23,
854 USER_ATTRIBUTE24,
855 USER_ATTRIBUTE25,
856 USER_FK1,
857 USER_FK2,
858 USER_FK3,
859 USER_FK4,
860 USER_FK5,
861 USER_MEASURE1,
862 USER_MEASURE2,
863 USER_MEASURE3,
864 USER_MEASURE4,
865 USER_MEASURE5,
866 CREATION_DATE,
867 LAST_UPDATE_DATE,
868 OPERATION_CODE,
869 COLLECTION_STATUS)
870 SELECT
871 ACCOUNT_ID,
872 substrb(ACCOUNT_CLASS,1,3),
873 substrb(ACCOUNT_TYPE,1,4),
874 substrb(AGREEMENT_NAME,1,30),
875 substrb(AGREEMENT_TYPE,1,30),
876 AMT_B,
877 AMT_G,
878 AMT_T,
879 BILL_TO_CUSTOMER_FK,
880 BILL_TO_SITE_FK,
881 CAMPAIGN_ACTL_FK,
882 CAMPAIGN_INIT_FK,
883 CAMPAIGN_STATUS_ACTL_FK,
884 CAMPAIGN_STATUS_INIT_FK,
885 CELL_ACTL_FK,
886 CELL_INIT_FK,
887 COMPANY_CC_ORG_FK,
888 END_USER_CUSTOMER_FK,
889 EVENT_OFFER_ACTL_FK,
890 EVENT_OFFER_INIT_FK,
891 EVENT_OFFER_REG_FK,
892 EXCHANGE_DATE,
893 EXCHANGE_RATE,
894 substrb(EXCHANGE_RATE_TYPE,1,30),
895 FUNCTIONAL_CURRENCY_FK,
896 GL_ACCT1_FK,
897 GL_ACCT2_FK,
898 GL_ACCT3_FK,
899 GL_ACCT4_FK,
900 GL_ACCT5_FK,
901 GL_ACCT6_FK,
902 GL_ACCT7_FK,
903 GL_ACCT8_FK,
904 GL_ACCT9_FK,
905 GL_ACCT10_FK,
906 GL_DATE,
907 GL_DATE_FK,
908 substrb(INSTANCE,1,40),
909 INSTANCE_FK,
910 substrb(INTERFACE_LINE_CONTEXT,1,30),
911 substrb(INTERNAL_FLAG,1,3),
912 INVOICE_DATE,
913 INVOICE_DATE_FK,
914 substrb(INVOICE_DIST_ID,1,25),
915 substrb(INVOICE_ID,1,25),
916 substrb(INVOICE_LINE_ID,1,25),
917 substrb(INVOICE_LINE_NUMBER,1,10),
918 substrb(INVOICE_LINE_MEMO,1,15),
919 substrb(INVOICE_NUMBER,1,30),
920 substrb(INVOICE_REASON,1,30),
921 substrb(INVOICE_SOURCE_NAME,1,50),
922 ITEM_FK,
923 QTY_CREDITED,
924 INVOICE_LINE_QTY,
925 ORDER_LINE_QTY,
926 substrb(LINE_TYPE,1,3),
927 MARKET_SEGMENT_FK,
928 MEDIA_ACTL_FK,
929 MEDIA_INIT_FK,
930 OFFER_ACTL_FK,
931 OFFER_INIT_FK,
932 substrb(ORDER_LINE_ID,1,50),
933 ORGANIZATION_FK,
934 substrb(ORIGINAL_INVOICE_ID,1,15),
935 substrb(ORIGINAL_INVOICE_LINE_ID,1,25),
936 substrb(ORIGINAL_INVOICE_NUM,1,30),
937 substrb(ORIGINAL_INVOICE_LINE_NUM,1,10),
938 PARENT_ITEM_FK,
939 PAYMENT_TERM_FK,
940 prim_salesrep_fk,
941 PRIM_SALESRESOURCE_FK,
942 substrb(PROCESS_TYPE,1,1),
943 PROJECT_FK,
944 RESELLER_CUSTOMER_FK,
945 substrb(INVOICE_PK,1,120),
946 SALES_ORDER_DATE_FK,
947 substrb(SALES_ORDER_LINE_NUMBER,1,30),
948 substrb(SALES_ORDER_NUMBER,1,30),
949 substrb(SALES_ORDER_SOURCE,1,50),
950 SALESCHANNEL_FK,
951 salesrep_fk,
952 SALESRESOURCE_FK,
953 SET_OF_BOOKS_FK,
954 SHIP_TO_CUSTOMER_FK,
955 SHIP_TO_SITE_FK,
956 SIC_CODE_FK,
957 SO_LINE_SELLING_PRICE,
961 TRANSACTION_CURRENCY_FK,
958 SOLD_TO_CUSTOMER_FK,
959 SOLD_TO_SITE_FK,
960 SOURCE_LIST_FK,
962 UNIT_SELLING_PRICE,
963 UOM_FK,
964 GL_POSTED_DATE,
965 substrb(TRANSACTION_STATUS,1,1),
966 substrb(TRANSACTION_CLASS,1,3),
967 USER_ATTRIBUTE1,
968 USER_ATTRIBUTE2,
969 USER_ATTRIBUTE3,
970 USER_ATTRIBUTE4,
971 USER_ATTRIBUTE5,
972 USER_ATTRIBUTE6,
973 USER_ATTRIBUTE7,
974 USER_ATTRIBUTE8,
975 USER_ATTRIBUTE9,
976 USER_ATTRIBUTE10,
977 USER_ATTRIBUTE11,
978 USER_ATTRIBUTE12,
979 USER_ATTRIBUTE13,
980 USER_ATTRIBUTE14,
981 USER_ATTRIBUTE15,
982 USER_ATTRIBUTE16,
983 USER_ATTRIBUTE17,
984 USER_ATTRIBUTE18,
985 USER_ATTRIBUTE19,
986 USER_ATTRIBUTE20,
987 USER_ATTRIBUTE21,
988 USER_ATTRIBUTE22,
989 USER_ATTRIBUTE23,
990 USER_ATTRIBUTE24,
991 USER_ATTRIBUTE25,
992 USER_FK1,
993 USER_FK2,
994 USER_FK3,
995 USER_FK4,
996 USER_FK5,
997 USER_MEASURE1,
998 USER_MEASURE2,
999 USER_MEASURE3,
1000 USER_MEASURE4,
1001 USER_MEASURE5,
1002 sysdate,
1003 sysdate,
1004 substrb(OPERATION_CODE,1,30),
1005 'READY'
1006 FROM FII_AR_TRX_DIST_FSTG
1007 WHERE collection_status = 'LOCAL READY';
1008 --ensures that only the records with collection status of local ready
1009 --will be pushed to remote fstg
1010
1011 RETURN(sql%rowcount);
1012
1013 EXCEPTION
1014 WHEN OTHERS THEN
1015 g_errbuf:=sqlerrm;
1016 g_retcode:=sqlcode;
1017 RETURN(-1);
1018
1019 END;
1020
1021 ---------------------------------------------------
1022 -- FUNCTION IDENTIFY_CHANGE
1023 ---------------------------------------------------
1024
1025 FUNCTION IDENTIFY_CHANGE(p_mode IN VARCHAR2,
1026 p_count OUT NOCOPY NUMBER,
1027 p_parent_seq_id IN NUMBER DEFAULT -1) RETURN NUMBER
1028 IS
1029
1030 l_seq_id NUMBER := -1;
1031 l_fii_schema VARCHAR2(30);
1032 l_status VARCHAR2(30);
1033 l_industry VARCHAR2(30);
1034
1035 BEGIN
1036
1037 p_count := 0;
1038 select fii_tmp_pk_s.nextval into l_seq_id from dual;
1039
1040 -- --------------------------------------------
1041 -- Populate rowid into fii_tmp_pk table based
1042 -- on last update date
1043 -- --------------------------------------------
1044 IF (p_mode = 'AR INVL') THEN
1045
1046 -- -----------------------------------------
1047 -- For ra_customer_trx_lines_all
1048 -- -----------------------------------------
1049 Insert into fii_tmp_pk(
1050 SEQ_ID,
1051 primary_key1,
1052 primary_key_char5)
1053 select /*+ PARALLEL(CT,4) */
1054 l_seq_id,
1055 ctlx.customer_trx_line_id,
1056 p_mode
1057 from ra_customer_trx_all ct,
1058 ra_customer_trx_lines_all ctlx
1059 where ct.last_update_date between g_push_from_date and g_push_to_date
1060 and ct.complete_flag = 'Y'
1061 and ct.customer_trx_id = ctlx.customer_trx_id
1062 and exists (select 1 from ra_cust_trx_line_gl_dist_all ctlgd
1063 where ctlgd.customer_trx_line_id=ctlx.customer_trx_line_id
1064 and ctlgd.posting_control_id <> -3)
1065 --added exists clause for SLA Uptake
1066
1067 UNION
1068 select /*+ PARALLEL(CTLX,4) */
1069 l_seq_id,
1070 ctlx.customer_trx_line_id,
1071 p_mode
1072 from ra_customer_trx_lines_all ctlx
1073 where ctlx.last_update_date between g_push_from_date and g_push_to_date
1074 and exists (select 1 from ra_cust_trx_line_gl_dist_all ctlgd
1075 where ctlgd.customer_trx_line_id=ctlx.customer_trx_line_id
1076 and ctlgd.posting_control_id <> -3);
1077 --added exists clause for SLA Uptake
1078
1079
1080 ELSIF (p_mode = 'AR DL') THEN
1081 -- -----------------------------------------
1082 -- For ra_cust_trx_line_gl_dist_all
1083 -- -----------------------------------------
1084 -- --------------------------------------------------------------------------------------------------
1085 -- The variable g_acct_or_inv_date is added in the below mentioned select statement.
1086 -- The profile option stored in the global variable g_acct_or_inv_date
1087 -- will be stored in the column Primary_Key5 . Modified for Currency Conversion Date Enhancement, 14-APR-03
1088 -----------------------------------------------------------------------------------------------------
1089
1090 Insert into fii_tmp_pk(
1091 SEQ_ID,
1092 Primary_Key1,
1093 primary_key_char5,
1094 primary_key5,
1095 primary_key4) --ccid
1096 select l_seq_id,
1097 ctlgd.cust_trx_line_gl_dist_id,
1098 p_mode,
1099 g_acct_or_inv_date,
1100 xal.code_combination_id
1101 from ra_cust_trx_line_gl_dist_all ctlgd,
1102 xla_ae_headers xah,
1106 and ctlgd.account_set_flag = 'N'
1103 xla_ae_lines xal,
1104 xla_distribution_links xdl
1105 where ctlgd.last_update_date between g_push_from_date and g_push_to_date
1107 and xah.application_id=222
1108 and xal.application_id=222
1109 and xdl.application_id=222
1110 and xah.ae_header_id=xal.ae_header_id
1111 and xal.ae_line_num=xdl.ae_line_num
1112 and xal.ae_header_id=xdl.ae_header_id
1113 and xdl.SOURCE_DISTRIBUTION_ID_NUM_1=ctlgd.cust_trx_line_gl_dist_id
1114 and xdl.source_distribution_type='RA_CUST_TRX_LINE_GL_DIST_ALL'
1115 and xah.ledger_id=ctlgd.set_of_books_id
1116 and xah.balance_type_code='A'
1117
1118 UNION
1119 select /*+ INDEX (CTLGD RA_CUST_TRX_LINE_GL_DIST_N1) */
1120 l_seq_id,
1121 ctlgd.cust_trx_line_gl_dist_id,
1122 p_mode,
1123 g_acct_or_inv_date,
1124 xal.code_combination_id
1125 from fii_tmp_pk ftr,
1126 ra_cust_trx_line_gl_dist_all ctlgd,
1127 xla_ae_headers xah,
1128 xla_ae_lines xal,
1129 xla_distribution_links xdl
1130 where ftr.seq_id = p_parent_seq_id
1131 and ftr.primary_key1 = ctlgd.customer_trx_line_id
1132 and ctlgd.account_set_flag = 'N'
1133 and xah.application_id=222
1134 and xal.application_id=222
1135 and xdl.application_id=222
1136 and xah.ae_header_id=xal.ae_header_id
1137 and xal.ae_line_num=xdl.ae_line_num
1138 and xal.ae_header_id=xdl.ae_header_id
1139 and xdl.SOURCE_DISTRIBUTION_ID_NUM_1=ctlgd.cust_trx_line_gl_dist_id
1140 and xdl.source_distribution_type='RA_CUST_TRX_LINE_GL_DIST_ALL'
1141 and xah.ledger_id=ctlgd.set_of_books_id
1142 and xah.balance_type_code='A'
1143 UNION
1144 select l_seq_id,
1145 primary_key1,
1146 p_mode,
1147 g_acct_or_inv_date,
1148 primary_key3 --ccid
1149 from fii_ar_trx_msng_rt;
1150
1151
1152
1153 ELSIF (p_mode = 'AR ADJ') THEN
1154 -- ----------------------------------------
1155 -- For ar_adjustments_all
1156 -- ----------------------------------------
1157
1158 Insert into fii_tmp_pk(
1159 SEQ_ID,
1160 primary_key1,
1161 primary_key_char5 ,
1162 primary_key4) --ccid
1163 select
1164 distinct l_seq_id,
1165 adj.adjustment_id,
1166 p_mode,
1167 xal.code_combination_id
1168 from ar_adjustments_all adj,
1169 xla_ae_headers xah,
1170 xla_ae_lines xal,
1171 xla_distribution_links xdl,
1172 ar_distributions_all ad
1173 where adj.last_update_date between g_push_from_date and g_push_to_date
1174 and nvl(adj.status, 'A') = 'A'
1175 and nvl(adj.postable,'Y') = 'Y'
1176 and adj.amount <> 0
1177 and xah.application_id=222
1178 and xal.application_id=222
1179 and xdl.application_id=222
1180 and xah.ae_header_id=xal.ae_header_id
1181 and xal.ae_line_num=xdl.ae_line_num
1182 and xal.ae_header_id=xdl.ae_header_id
1183 and xdl.SOURCE_DISTRIBUTION_ID_NUM_1=ad.line_id
1184 and source_distribution_type='AR_DISTRIBUTIONS_ALL'
1185 and ad.source_id=adj.adjustment_id
1186 and ad.source_table='ADJ'
1187 and xah.ledger_id=adj.set_of_books_id
1188 and xah.balance_type_code='A'
1189
1190 UNION
1191 select /*+ ORDERED
1192 PARALLEL(CT,4)
1193 INDEX(ADJ AR_ADJUSTMENTS_N2) */
1194 distinct l_seq_id,
1195 adj.adjustment_id,
1196 p_mode,
1197 xal.code_combination_id
1198 from ra_customer_trx_all ct,
1199 ar_adjustments_all adj,
1200 xla_ae_headers xah,
1201 xla_ae_lines xal,
1202 xla_distribution_links xdl,
1203 ar_distributions_all ad
1204 where ct.last_update_date between g_push_from_date and g_push_to_date
1205 and ct.complete_flag = 'Y'
1206 and ct.customer_trx_id = adj.customer_trx_id
1207 and nvl(adj.status, 'A') = 'A'
1208 and nvl(adj.postable,'Y') = 'Y'
1209 and adj.amount <> 0
1210 and xah.application_id=222
1211 and xal.application_id=222
1212 and xdl.application_id=222
1213 and xah.ae_header_id=xal.ae_header_id
1214 and xal.ae_line_num=xdl.ae_line_num
1215 and xal.ae_header_id=xdl.ae_header_id
1216 and xdl.SOURCE_DISTRIBUTION_ID_NUM_1=ad.line_id
1217 and source_distribution_type='AR_DISTRIBUTIONS_ALL'
1218 and ad.source_id=adj.adjustment_id
1219 and ad.source_table='ADJ'
1220 and xah.ledger_id=adj.set_of_books_id
1221 and xah.balance_type_code='A'
1222
1223 UNION
1224 select l_seq_id,
1225 primary_key2,
1226 p_mode ,
1227 primary_key3 --ccid
1228 from fii_ar_trx_msng_rt;
1229
1230
1231 END IF;
1232
1233 p_count := sql%rowcount;
1234
1235 IF (FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, l_fii_schema)) THEN
1236 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_fii_schema,
1237 TABNAME => 'FII_TMP_PK');
1238 END IF;
1242 -- -----------------------------------------
1239
1240 -- ------------------------------------------
1241 -- Commit for the child process to pick up
1243 Commit;
1244
1245
1246 RETURN(l_seq_id);
1247
1248 EXCEPTION
1249 WHEN OTHERS THEN
1250 g_errbuf:=sqlerrm;
1251 g_retcode:=sqlcode;
1252 RETURN(-1);
1253
1254 END;
1255
1256 -----------------------------------------------------------
1257 --PROCEDURE CHILD_SETUP
1258 -----------------------------------------------------------
1259
1260 PROCEDURE CHILD_SETUP(p_object_name VARCHAR2) IS
1261 l_dir VARCHAR2(400);
1262 l_stmt varchar2(200);
1263
1264
1265 BEGIN
1266
1267 l_stmt := 'ALTER SESSION SET GLOBAL_NAMES = FALSE' ;
1268 execute immediate l_stmt;
1269
1270 /* IF (fnd_profile.value('EDW_TRACE')='Y') THEN
1271 dbms_session.set_sql_trace(TRUE);
1272 END IF; */ -- Commented for bug 3304365
1273
1274 IF (fnd_profile.value('EDW_DEBUG') = 'Y') THEN
1275 edw_log.g_debug := TRUE;
1276 ENd IF;
1277
1278 l_dir:=fnd_profile.value('EDW_LOGFILE_DIR');
1279 if l_dir is null then
1280 l_dir:='/sqlcom/log';
1281 end if;
1282 if g_debug_flag = 'Y' then
1283 edw_log.put_names(p_object_name||'.log',p_object_name||'.out',l_dir);
1284 end if;
1285
1286 END;
1287
1288
1289 -- ---------------------------------
1290 -- PUBLIC PROCEDURES
1291 -- ---------------------------------
1292
1293 -----------------------------------------------------------
1294 -- PROCEDURE PUSH
1295 -----------------------------------------------------------
1296
1297 PROCEDURE PUSH(Errbuf in out NOCOPY Varchar2,
1298 Retcode in out NOCOPY Varchar2,
1299 p_from_date IN Varchar2,
1300 p_to_date IN Varchar2,
1301 p_mode IN Varchar2,
1302 p_seq_id IN Varchar2) IS
1303
1304 l_fact_name Varchar2(30) :='FII_AR_TRX_DIST_F';
1305 l_exception_msg Varchar2(2400):=Null;
1306 l_from_date Date:=Null;
1307 l_to_date Date:=Null;
1308 l_seq_id_line NUMBER := -1;
1309 l_seq_id_dist_line NUMBER := -1;
1310 l_seq_id_adjust_line NUMBER := -1;
1311 l_row_count NUMBER := 0;
1312
1313 l_request_id1 NUMBER;
1314 l_request_id2 NUMBER;
1315 l_request_id3 NUMBER;
1316 l_request_id4 NUMBER;
1317 l_request_id5 NUMBER;
1318 l_request_id6 NUMBER;
1319 l_request_id7 NUMBER;
1320 l_request_id8 NUMBER;
1321
1322
1323 l_call_status1 BOOLEAN;
1324 l_call_status2 BOOLEAN;
1325 l_call_status3 BOOLEAN;
1326 l_call_status4 BOOLEAN;
1327 l_call_status5 BOOLEAN;
1328 l_call_status6 BOOLEAN;
1329 l_call_status7 BOOLEAN;
1330 l_call_status8 BOOLEAN;
1331
1332 l_dev_status1 VARCHAR2(30);
1333 l_dev_status2 VARCHAR2(30);
1334 l_dev_status3 VARCHAR2(30);
1335 l_dev_status4 VARCHAR2(30);
1336 l_dev_status5 VARCHAR2(30);
1337 l_dev_status6 VARCHAR2(30);
1338 l_dev_status7 VARCHAR2(30);
1339 l_dev_status8 VARCHAR2(30);
1340
1341 l_dev_phase1 VARCHAR2(30);
1342 l_dev_phase2 VARCHAR2(30);
1343 l_dev_phase3 VARCHAR2(30);
1344 l_dev_phase4 VARCHAR2(30);
1345 l_dev_phase5 VARCHAR2(30);
1346 l_dev_phase6 VARCHAR2(30);
1347 l_dev_phase7 VARCHAR2(30);
1348 l_dev_phase8 VARCHAR2(30);
1349
1350
1351 l_launch_req_failure EXCEPTION;
1352 l_child_req_failure EXCEPTION;
1353 l_push_local_failure EXCEPTION;
1354 l_push_remote_failure EXCEPTION;
1355 l_set_status_failure EXCEPTION;
1356 l_iden_change_failure EXCEPTION;
1357 l_item_fk_failure EXCEPTION;
1358
1359 -- -------------------------------------------
1360 -- Put any additional developer variables here
1361 -- -------------------------------------------
1362 l_stmt varchar2(100);
1363 my_payment_currency Varchar2(2000):=NULL;
1364 my_inv_date Varchar2(2000) := NULL;
1365 my_collection_status Varchar2(2000):=NULL;
1366 temp Number;
1367
1368 l_to_currency VARCHAR2(15); -- Added for Currency Conversion Date Enhancement , 14-APR-03
1369 l_msg VARCHAR2(120):=NULL; -- Added for Currency Conversion Date Enhancement , 18-APR-03
1370 l_set_completion_status BOOLEAN; -- Added for bug#3077413
1371
1372 ----------------------------------------------------------------------------------------------
1373 -- This cursor is for getting records where the CONVERSION_DATE (i.e. GL_DATE or INVOICE_DATE )
1374 -- is less than the sysdate i.e. in past. Added for Currency Conversion Date Enhancement , 14-APR-03
1375 ----------------------------------------------------------------------------------------------
1376
1377
1378 cursor miss_curr_past is select DISTINCT FUNCTIONAL_CURRENCY_FK FROM_CURRENCY,
1382 From FII_AR_TRX_DIST_FSTG
1379 DECODE(substr(invoice_pk,1,2),'A-',GL_DATE,
1380 DECODE(g_acct_or_inv_date,1,GL_DATE,INVOICE_DATE)) CONVERSION_DATE,
1381 COLLECTION_STATUS
1383 where (COLLECTION_STATUS='RATE NOT AVAILABLE'
1384 OR COLLECTION_STATUS = 'INVALID CURRENCY')
1385 AND trunc(DECODE(substr(invoice_pk,1,2),'A-',GL_DATE,
1386 DECODE(g_acct_or_inv_date,1, GL_DATE,INVOICE_DATE))) <= trunc(sysdate);
1387
1388 ----------------------------------------------------------------------------------------------
1389 -- This cursor is for getting records where the CONVERSION_DATE (i.e. GL_DATE or INVOICE_DATE )
1390 -- is greater than the sysdate i.e. in future.Added for Currency Conversion Date Enhancement ,14-APR-03
1391 ----------------------------------------------------------------------------------------------
1392
1393
1394 cursor miss_curr_future is select DISTINCT FUNCTIONAL_CURRENCY_FK FROM_CURRENCY,
1395 DECODE(substr(invoice_pk,1,2),'A-',GL_DATE,
1396 DECODE(g_acct_or_inv_date,1,GL_DATE,INVOICE_DATE)) CONVERSION_DATE,
1397 COLLECTION_STATUS
1398 From FII_AR_TRX_DIST_FSTG
1399 where (COLLECTION_STATUS='RATE NOT AVAILABLE'
1400 OR COLLECTION_STATUS = 'INVALID CURRENCY')
1401 AND trunc(DECODE(substr(invoice_pk,1,2),'A-',GL_DATE,
1402 DECODE(g_acct_or_inv_date,1, GL_DATE,INVOICE_DATE))) > trunc(sysdate);
1403
1404 --Cursor declartion required to generate output file
1405
1406 BEGIN
1407
1408 execute immediate 'alter session set global_names=false' ; --bug#3124326
1409
1410 Errbuf :=NULL;
1411 Retcode:=0;
1412
1413 l_from_date :=to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS');
1414 l_to_date :=to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
1415 g_seq_id := to_number(p_seq_id);
1416 g_push_from_date := l_from_date;
1417 g_push_to_date := l_to_date;
1418
1419 -----------------------------------------------------------------
1420 -- See whether to use accounting date or invoice date
1421 -----------------------------------------------------------------
1422 IF NVL(FND_PROFILE.value('FII_ACCT_OR_INV_DATE'),'N') = 'Y' THEN
1423 g_acct_or_inv_date := 1;
1424 ELSE
1425 g_acct_or_inv_date := 0;
1426 END IF;
1427
1428
1429 -- -------------------------------------------
1430 -- Turn on parallel insert/dml for the session
1431 -- -------------------------------------------
1432 COMMIT;
1433 l_stmt:='ALTER SESSION ENABLE PARALLEL DML';
1434 execute immediate l_stmt;
1435
1436 IF (p_mode = 'INIT') THEN
1437 -- --------------------------------------
1438 -- Running as parent monitoring process
1439 -- --------------------------------------
1440
1441 IF (Not EDW_COLLECTION_UTIL.setup(l_fact_name)) THEN
1442 errbuf := fnd_message.get;
1443 RAISE_APPLICATION_ERROR(-20000,'Error in SETUP: ' || errbuf);
1444 END IF;
1445
1446 -- --------------------------------------------
1447 -- Taking care of cases where the input from/to
1448 -- date is NULL. Note, this is necessary only
1449 -- the parent process, ie p_mode = 'INIT'
1450 -- --------------------------------------------
1451 FII_AR_TRX_DIST_F_C.g_push_from_date := nvl(l_from_date,
1452 EDW_COLLECTION_UTIL.G_local_last_push_start_date -
1453 EDW_COLLECTION_UTIL.g_offset);
1454
1455 FII_AR_TRX_DIST_F_C.g_push_to_date := nvl(l_to_date,
1456 EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
1457
1458 if g_debug_flag = 'Y' then
1459 edw_log.put_line( 'The collection range is from '||
1460 to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
1461 to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS'));
1462 edw_log.put_line(' ');
1463 end if;
1464
1465 IF (NOT LOCAL_SAME_AS_REMOTE) THEN
1466 TRUNCATE_TABLE('FII_AR_TRX_DIST_FSTG');
1467 ELSE
1468 DELETE_STG;
1469 END IF;
1470
1471 -- -----------------------------------------------
1472 -- launching parallel request to push data for AR
1473 -- -----------------------------------------------
1474
1475 if g_debug_flag = 'Y' then
1476 edw_log.put_line(' ');
1477 end if;
1478
1479 l_request_id8 := SUBMIT_REQUEST('AR', -1);
1480 if g_debug_flag = 'Y' then
1481 edw_log.put_line('Submitted following parallel request to push '||
1482 'AR revenue transactions '||l_request_id8);
1483 end if;
1484
1485
1486 IF (l_request_id8 = -1) THEN
1487 RAISE l_launch_req_failure;
1488 END IF;
1489
1490 commit;
1491
1492 -- -----------------------------------------------
1493 -- launching parallel request to push data AR ADJ
1494 -- -----------------------------------------------
1495
1496 if g_debug_flag = 'Y' then
1497 edw_log.put_line(' ');
1498 end if;
1499
1500 l_request_id3 := SUBMIT_REQUEST('AR ADJ',l_seq_id_adjust_line);
1501 if g_debug_flag = 'Y' then
1502 edw_log.put_line('Submitted following parallel request to push '||
1506 IF (l_request_id3 = -1) THEN
1503 'AR adjustments: '||l_request_id3);
1504 end if;
1505
1507 RAISE l_launch_req_failure;
1508 END IF;
1509
1510 commit;
1511
1512 -- -----------------------------------------------
1513 -- launching parallel request to detect deleted invoices
1514 -- -----------------------------------------------
1515
1516 if g_debug_flag = 'Y' then
1517 edw_log.put_line(' ');
1518 end if;
1519
1520 l_request_id4 := SUBMIT_REQUEST('DELET',-1);
1521 if g_debug_flag = 'Y' then
1522 edw_log.put_line('Submitted following parallel request to detect '||
1523 'deleted invoices: '||l_request_id4);
1524 end if;
1525
1526 IF (l_request_id4 = -1) THEN
1527 RAISE l_launch_req_failure;
1528 END IF;
1529
1530 commit;
1531
1532 -- -------------------------------
1533 -- -------------------------------
1534 -- Waiting for requests to finish
1535 -- -------------------------------
1536 if g_debug_flag = 'Y' then
1537 edw_log.put_line(' ');
1538 edw_log.put_line('Waiting for child requests to finish');
1539
1540 fii_util.start_timer;
1541 end if;
1542
1543 l_call_status8 := WAIT_FOR_REQUEST(l_request_id8, l_dev_phase8,
1544 l_dev_status8);
1545 l_call_status3 := WAIT_FOR_REQUEST(l_request_id3, l_dev_phase3,
1546 l_dev_status3);
1547 l_call_status4 := WAIT_FOR_REQUEST(l_request_id4, l_dev_phase4,
1548 l_dev_status4);
1549
1550
1551 if g_debug_flag = 'Y' then
1552 edw_log.put_line(' ');
1553 edw_log.put_line('All child requests have finished');
1554 fii_util.stop_timer;
1555 fii_util.print_timer('Duration');
1556
1557 edw_log.put_line('Before call to truncate_table msng_rt');
1558 end if;
1559 TRUNCATE_TABLE('fii_ar_trx_msng_rt');
1560 --select count(*)into temp from fii_ar_trx_msng_rt;
1561 --edw_log.put_line('Rows in msng rt table after truncate'|| to_char(temp));
1562 INSERT_INTO_MISSING_RATES;
1563
1564 ----------------------------------------------------------------------------------------------------------
1565 -- Read the warehouse currency. Added for Currency Conversion Enhancement 14-APR-03
1566 ----------------------------------------------------------------------------------------------------------
1567 select /*+ FULL(SP) CACHE(SP) */
1568 warehouse_currency_code into l_to_currency
1569 from edw_local_system_parameters SP;
1570
1571 --edw_log.put_line('g_missing_rates is '||g_missing_rates);
1572 if (g_missing_rates >0) then Retcode:=g_retcode;
1573
1574 --------------------------------------------------------------------
1575 -- Print Records where conversion date is in past
1576 ---------------------------------------------------------------------
1577 /* FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ***Information for Missing Currency Conversion Rates*** ');
1578 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1579 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Following Section displays records where Conversion Dates are in Past.');
1580 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'To fix the issue , please enter rates for these Conversion Dates and re-collect the fact.');
1581 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1582
1583 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'FROM CURRENCY TO CURRENCY CONVERSION DATE COLLECTION STATUS');
1584 */
1585
1586 FND_MESSAGE.SET_NAME('FII','FII_MISS_CONV_RATES');
1587 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ***'||fnd_message.get||'*** ');
1588 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1589 FND_MESSAGE.SET_NAME('FII','FII_PAST_CONV_RATES');
1590 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get);
1591 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1592 FND_MESSAGE.SET_NAME('FII','FII_FROM_CURRENCY');
1593 l_msg := l_msg||fnd_message.get||' ';
1594 FND_MESSAGE.SET_NAME('FII','FII_TO_CURRENCY');
1595 l_msg := l_msg||fnd_message.get||' ';
1596 FND_MESSAGE.SET_NAME('FII','FII_MISS_CONV_DATES');
1597 l_msg := l_msg||fnd_message.get||' ';
1598 FND_MESSAGE.SET_NAME('FII','FII_COLLECTION_STATUS');
1599 l_msg := l_msg||fnd_message.get;
1600 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_msg);
1601 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '------------- ----------- --------------- -----------------');
1602
1603 FOR c in miss_curr_past loop
1604 my_payment_currency := c.FROM_CURRENCY;
1605 my_inv_date := c.CONVERSION_DATE;
1606 my_collection_status := c.COLLECTION_STATUS;
1607
1608 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, my_payment_currency||
1609 ' '||l_to_currency||' '||my_inv_date||' '||my_collection_status);
1610
1611 if g_debug_flag = 'Y' then
1612 edw_log.put_line('Inside cursor for loop');
1613 end if;
1614 end loop;
1615
1616 ------------------------------------------------------------------------------
1617 -- Print records where conversion date is in future
1621 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1618 -------------------------------------------------------------------------------
1619 /* FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1620 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Following Section displays records where Conversion Dates are in Future.');
1622 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'FROM CURRENCY TO CURRENCY CONVERSION DATE COLLECTION STATUS');
1623 */
1624 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1625 FND_MESSAGE.SET_NAME('FII','FII_FUTURE_CONV_RATES');
1626 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get);
1627 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1628 l_msg := NULL;
1629 FND_MESSAGE.SET_NAME('FII','FII_FROM_CURRENCY');
1630 l_msg := l_msg||fnd_message.get||' ';
1631 FND_MESSAGE.SET_NAME('FII','FII_TO_CURRENCY');
1632 l_msg := l_msg||fnd_message.get||' ';
1633 FND_MESSAGE.SET_NAME('FII','FII_MISS_CONV_DATES');
1634 l_msg := l_msg||fnd_message.get||' ';
1635 FND_MESSAGE.SET_NAME('FII','FII_COLLECTION_STATUS');
1636 l_msg := l_msg||fnd_message.get;
1637 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_msg);
1638 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '------------- ----------- --------------- -----------------');
1639
1640 FOR d in miss_curr_future loop
1641 my_payment_currency := d.FROM_CURRENCY;
1642 my_inv_date := d.CONVERSION_DATE;
1643 my_collection_status := d.COLLECTION_STATUS;
1644
1645 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, my_payment_currency||
1646 ' '||l_to_currency||' '||my_inv_date||' '||my_collection_status);
1647
1648 if g_debug_flag = 'Y' then
1649 edw_log.put_line('Inside cursor for loop1');
1650 end if;
1651 end loop;
1652
1653
1654 end if;
1655
1656 -- -------------------------------
1657 -- check request status
1658 -- -------------------------------
1659 /* Even if the completion status is WARNING then also the code should behave the same as
1660 for completion status NORMAL. bug#3052562 */
1661
1662 IF (NVL(l_dev_phase8='COMPLETE' AND (l_dev_status8 = 'NORMAL' OR l_dev_status8 = 'WARNING') AND
1663 l_dev_phase3='COMPLETE' AND (l_dev_status3 = 'NORMAL' OR l_dev_status3 = 'WARNING') AND
1664 l_dev_phase4='COMPLETE' AND (l_dev_status4 = 'NORMAL' OR l_dev_status4 = 'WARNING'), FALSE)) THEN
1665
1666 -- --------------------------------
1667 -- Update the item foreign keys
1668 -- Note, in update_item_fk, we run ddl
1669 -- which results in a commit.
1670 -- --------------------------------
1671 if g_debug_flag = 'Y' then
1672 edw_log.put_line(' ');
1673 edw_log.put_line('Updating Item foreign key with proper values');
1674 edw_log.put_line('in the local staging table');
1675
1676 fii_util.start_timer;
1677 end if;
1678 g_row_count := update_item_fk;
1679 if g_debug_flag = 'Y' then
1680 fii_util.stop_timer;
1681 fii_util.print_timer('Duration');
1682 end if;
1683
1684 IF (g_row_count = -1) THEN RAISE l_item_fk_failure; END IF;
1685
1686 if g_debug_flag = 'Y' then
1687 edw_log.put_line('Updated '||g_row_count||' records');
1688 end if;
1689
1690
1691
1692 IF (NOT LOCAL_SAME_AS_REMOTE) THEN
1693 -- -----------------------------------------------
1694 -- The target warehouse is not the same database
1695 -- as the source OLTP, which is the typical case.
1696 -- We move data from local to remote staging table
1697 -- and clean up local staging
1698 -- -----------------------------------------------
1699
1700 if g_debug_flag = 'Y' then
1701 edw_log.put_line(' ');
1702 edw_log.put_line('Moving data from local staging table to remote staging table');
1703 fii_util.start_timer;
1704 end if;
1705 g_row_count := PUSH_REMOTE;
1706 if g_debug_flag = 'Y' then
1707 fii_util.stop_timer;
1708 fii_util.print_timer('Duration');
1709 end if;
1710
1711 IF (g_row_count = -1) THEN RAISE l_push_remote_failure; END IF;
1712 if g_debug_flag = 'Y' then
1713 edw_log.put_line(' ');
1714 edw_log.put_line('Cleaning local staging table');
1715
1716 fii_util.start_timer;
1717 end if;
1718 TRUNCATE_TABLE('FII_AR_TRX_DIST_FSTG');
1719 if g_debug_flag = 'Y' then
1720 fii_util.stop_timer;
1721 fii_util.print_timer('Duration');
1722 end if;
1723
1724 ELSE
1725 -- -----------------------------------------------
1726 -- The target warehouse is the same database
1727 -- as the source OLTP. We set the status of all our
1728 -- records status 'LOCAL READY' to 'READY'
1729 -- -----------------------------------------------
1730
1731 if g_debug_flag = 'Y' then
1732 edw_log.put_line(' ');
1733 edw_log.put_line('Marking records in staging table with READY status');
1734 fii_util.start_timer;
1735 end if;
1736 -- Bug 4689098. Moved the call to SET_STATUS_READY out of the if statement
1737 -- so that it gets called even when debug mode is set to No
1738 g_row_count := SET_STATUS_READY;
1739 if g_debug_flag = 'Y' then
1740 fii_util.stop_timer;
1741 fii_util.print_timer('Duration');
1742 end if;
1743
1744 IF (g_row_count = -1) THEN RAISE l_set_status_failure; END IF;
1745 END IF;
1746
1747 ELSE
1748
1749 RAISE l_child_req_failure;
1750
1751 END IF;
1752
1753 DELETE_STG;
1754
1755 -- -----------------------------------------------
1756 -- No exception raised so far. Successful. Call
1757 -- wrapup to commit and insert messages into logs
1758 -- -----------------------------------------------
1759 if g_debug_flag = 'Y' then
1760 edw_log.put_line(' ');
1761 edw_log.put_line('Inserted '||nvl(g_row_count,0)||
1762 ' rows into the staging table');
1763 edw_log.put_line(' ');
1764 end if;
1765 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, l_exception_msg, g_push_from_date, g_push_to_date);
1766
1767
1768 ELSIF (p_mode = 'AR') THEN
1769
1770 -- -----------------------------------------------
1771 -- We do this for child process because child
1772 -- process do not call setup routine from EDWCORE
1773 -- -----------------------------------------------
1774 CHILD_SETUP(l_fact_name||'_'||p_mode);
1775
1776 -- --------------------------------------------
1777 -- Identify Change for AR Invoice Lines and
1778 -- launching parallel request to push data
1779 -- --------------------------------------------
1780 if g_debug_flag = 'Y' then
1781 edw_log.put_line(' ');
1782 edw_log.put_line('Identifying changed AR invoice lines');
1783 fii_util.start_timer;
1784 end if;
1785 l_seq_id_line := IDENTIFY_CHANGE('AR INVL',l_row_count);
1786 if g_debug_flag = 'Y' then
1787 fii_util.stop_timer;
1788 fii_util.print_timer('Identified '||l_row_count||' changed records in');
1789 end if;
1790
1791 if (l_seq_id_line = -1) THEN
1792 RAISE l_iden_change_failure;
1793 end if;
1794
1795 l_request_id2 := SUBMIT_REQUEST('AR INVL',l_seq_id_line);
1796 if g_debug_flag = 'Y' then
1797 edw_log.put_line('Submitted following parallel request to push '||
1798 'AR invoice lines: '||l_request_id2);
1799 end if;
1800
1801 l_request_id7 := SUBMIT_REQUEST('AR OE INVL',l_seq_id_line);
1802 if g_debug_flag = 'Y' then
1803 edw_log.put_line('Submitted following parallel request to push '||
1804 'AR invoice lines (OE related invoices): '||
1805 l_request_id7);
1806 end if;
1807
1808 IF (l_request_id2 = -1 OR l_request_id7 = -1) THEN
1809 RAISE l_launch_req_failure;
1810 END IF;
1811
1812 -- --------------------------------------------
1813 -- Identify Change for AR Invoice Distributions
1814 -- and launching parallel request to push data
1815 -- --------------------------------------------
1816 if g_debug_flag = 'Y' then
1817 edw_log.put_line(' ');
1818 edw_log.put_line('Identifying changed AR invoice distribution');
1819 fii_util.start_timer;
1820 end if;
1821 l_seq_id_dist_line := IDENTIFY_CHANGE('AR DL', l_row_count,l_seq_id_line);
1822 fii_util.stop_timer;
1823 fii_util.print_timer('Identified '||l_row_count||' changed records in');
1824
1825 if (l_seq_id_dist_line = -1) THEN
1826 RAISE l_iden_change_failure;
1827 end if;
1828
1829 l_request_id1 := SUBMIT_REQUEST('AR DL',l_seq_id_dist_line);
1830 if g_debug_flag = 'Y' then
1831 edw_log.put_line('Submitted following parallel request to push '||
1832 'AR invoice details: '||l_request_id1);
1833 end if;
1834
1835 l_request_id6 := SUBMIT_REQUEST('AR OE DL',l_seq_id_dist_line);
1836 if g_debug_flag = 'Y' then
1837 edw_log.put_line('Submitted following parallel request to push '||
1838 'AR invoice details (OE related invoices): '||
1839 l_request_id6);
1840 end if;
1841
1842 IF (l_request_id1 = -1 OR l_request_id6 = -1) THEN
1843 RAISE l_launch_req_failure;
1844 END IF;
1845
1846 commit;
1847
1848 -- -------------------------------
1849 -- Waiting for AR requests to finish
1850 -- -------------------------------
1851 if g_debug_flag = 'Y' then
1852 edw_log.put_line(' ');
1853 edw_log.put_line('Waiting for AR child requests to finish');
1854
1855 fii_util.start_timer;
1856 end if;
1857
1861 l_dev_status2);
1858 l_call_status1 := WAIT_FOR_REQUEST(l_request_id1, l_dev_phase1,
1859 l_dev_status1);
1860 l_call_status2 := WAIT_FOR_REQUEST(l_request_id2, l_dev_phase2,
1862 l_call_status6 := WAIT_FOR_REQUEST(l_request_id6, l_dev_phase6,
1863 l_dev_status6);
1864 l_call_status7 := WAIT_FOR_REQUEST(l_request_id7, l_dev_phase7,
1865 l_dev_status7);
1866
1867 if g_debug_flag = 'Y' then
1868 edw_log.put_line(' ');
1869 edw_log.put_line('All child requests have finished');
1870 fii_util.stop_timer;
1871 fii_util.print_timer('Duration');
1872 end if;
1873
1874
1875
1876 -- -------------------------------------------------------------
1877 -- Delete all temp tables' record
1878 -- -------------------------------------------------------------
1879
1880 if g_debug_flag = 'Y' then
1881 fii_util.start_timer;
1882 edw_log.put_line(' ');
1883 edw_log.put_line('Cleaning tmp table');
1884 end if;
1885
1886 delete fii_tmp_pk
1887 where seq_id IN ( l_seq_id_line,
1888 l_seq_id_dist_line);
1889 commit;
1890
1891 if g_debug_flag = 'Y' then
1892 fii_util.stop_timer;
1893 fii_util.print_timer('Duration');
1894 end if;
1895
1896 -- -------------------------------
1897 -- check request status
1898 -- -------------------------------
1899 /* Even if the completion status is WARNING then also the code should behave the same as
1900 for completion status NORMAL. bug#3052562 */
1901 IF NOT(NVL(l_dev_phase1='COMPLETE' AND (l_dev_status1 = 'NORMAL' OR l_dev_status1 = 'WARNING') AND
1902 l_dev_phase2='COMPLETE' AND (l_dev_status2 = 'NORMAL' OR l_dev_status2 = 'WARNING') AND
1903 l_dev_phase6='COMPLETE' AND (l_dev_status6 = 'NORMAL' OR l_dev_status6 = 'WARNING') AND
1904 l_dev_phase7='COMPLETE' AND (l_dev_status7 = 'NORMAL' OR l_dev_status7 = 'WARNING'), FALSE)) THEN
1905
1906 RAISE l_child_req_failure;
1907
1908 END IF;
1909
1910
1911
1912 ELSE
1913 -- --------------------------------------
1914 -- p_mode <> 'INIT'
1915 -- Running as a child process
1916 -- --------------------------------------
1917 CHILD_SETUP(l_fact_name||'_'||p_mode);
1918
1919
1920
1921 -- -----------------------------------------------
1922 -- We do this for child process because child
1923 -- process do not call setup routine from EDWCORE
1924 -- -----------------------------------------------
1925
1926
1927 -- -----------------------------------------------
1928 -- Initialize the cache in flex api
1929 -- -----------------------------------------------
1930
1931
1932 IF (p_mode = 'AR ADJ') THEN
1933
1934 -- --------------------------------------------
1935 -- Identify Changed Records
1936 -- --------------------------------------------
1937
1938 if g_debug_flag = 'Y' then
1939 edw_log.put_line(' ');
1940
1941 edw_log.put_line(p_mode);
1942
1943 edw_log.put_line( 'The collection range is from '||
1944 to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
1945 to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS'));
1946
1947
1948 edw_log.put_line('Identifying changed records');
1949 fii_util.start_timer;
1950 end if;
1951 g_seq_id := IDENTIFY_CHANGE(p_mode,l_row_count);
1952 if g_debug_flag = 'Y' then
1953 fii_util.stop_timer;
1954 fii_util.print_timer('Identified '||l_row_count||' changed records in');
1955 end if;
1956
1957 if (g_seq_id = -1) THEN
1958 RAISE l_iden_change_failure;
1959 end if;
1960
1961 END IF;
1962
1963 if g_debug_flag = 'Y' then
1964 edw_log.put_line(' ');
1965 edw_log.put_line('Pushing to local staging table');
1966 fii_util.start_timer;
1967 end if;
1968 g_row_count := PUSH_TO_LOCAL(p_mode);
1969 if g_debug_flag = 'Y' then
1970 fii_util.stop_timer;
1971 fii_util.print_timer('Duration');
1972 end if;
1973
1974 IF (g_row_count = -1) THEN RAISE L_push_local_failure; END IF;
1975
1976 IF (p_mode = 'AR ADJ') THEN
1977
1978 -- --------------------------------------------
1979 -- Delete all temp tables' record
1980 -- --------------------------------------------
1981 if g_debug_flag = 'Y' then
1982 fii_util.start_timer;
1983 edw_log.put_line(' ');
1984 edw_log.put_line('Cleaning tmp table');
1985 end if;
1986
1987 delete fii_tmp_pk
1988 where seq_id = g_seq_id;
1989 commit;
1990
1991 fii_util.stop_timer;
1992 fii_util.print_timer('Duration');
1993
1994 END IF;
1995 if g_debug_flag = 'Y' then
1999 end if;
1996 edw_log.put_line('Inserted '||nvl(g_row_count,0)||
1997 ' rows into the local staging table');
1998 edw_log.put_line(' ');
2000
2001 -- ---------------------------------------------------
2002 -- Commit records into local staging table. Needed
2003 -- because we don't call wrapup for child process
2004 -- ---------------------------------------------------
2005 COMMIT;
2006 -- --------------------------------------
2007 -- p_mode <> 'INIT'
2008 -- Running as a child process
2009 -- --------------------------------------
2010
2011 IF (p_mode = 'DELET') THEN
2012
2013 if g_debug_flag = 'Y' then
2014 edw_log.put_line(' ');
2015 edw_log.put_line(p_mode);
2016
2017 fii_util.put_timestamp;
2018 edw_log.put_line('We are detecting invoices deleting from AR but are in warehouse');
2019 edw_log.put_line(' ');
2020 fii_util.put_timestamp;
2021 edw_log.put_line('Clean and set up environment');
2022 end if;
2023 l_stmt := 'Begin FII_AR_TRX_DIST_F_D.Init@EDW_APPS_TO_WH (edw_instance.get_code); End; ';
2024 execute immediate l_stmt ;
2025 if g_debug_flag = 'Y' then
2026 edw_log.put_line('Dropping OLTP temp table ');
2027 end if;
2028 l_stmt := 'Begin FII_AR_TRX_DIST_F_D.Drop_Table@EDW_APPS_TO_WH(''FII_AR_OLTP_TMP_TRX_ID''); End; ';
2029 execute immediate l_stmt;
2030 if g_debug_flag = 'Y' then
2031 edw_log.put_line('Dropping EDW temp table ');
2032 end if;
2033 l_stmt := 'Begin FII_AR_TRX_DIST_F_D.Drop_Table@EDW_APPS_TO_WH(''FII_AR_EDW_TMP_TRX_ID''); End; ';
2034 execute immediate l_stmt;
2035 l_stmt := 'Begin FII_AR_TRX_DIST_F_D.Drop_Table@EDW_APPS_TO_WH(''FII_AR_EDW_EXTRA_ID''); End; ';
2036 execute immediate l_stmt;
2037 if g_debug_flag = 'Y' then
2038 edw_log.put_line(' ');
2039 fii_util.put_timestamp;
2040 edw_log.put_line('Generate list of invoices in AR subledger');
2041 fii_util.start_timer;
2042 end if;
2043 l_stmt := 'Begin FII_AR_TRX_DIST_F_D.Create_OLTP_TRX_TMP_TABLE@EDW_APPS_TO_WH; End; ';
2044 execute immediate l_stmt;
2045 commit;
2046 l_stmt := 'Begin FII_AR_TRX_DIST_F_D.Populate_OLTP_TRX_TMP_TABLE@EDW_APPS_TO_WH; End; ';
2047 execute immediate l_stmt;
2048 commit;
2049 if g_debug_flag = 'Y' then
2050 fii_util.stop_timer;
2051 fii_util.print_timer('Duration');
2052
2053 edw_log.put_line(' ');
2054 fii_util.put_timestamp;
2055 edw_log.put_line('Generate list of invoices in EDW');
2056 fii_util.start_timer;
2057 end if;
2058 l_stmt := 'Begin FII_AR_TRX_DIST_F_D.Create_EDW_TRX_TMP_TABLE@EDW_APPS_TO_WH; End; ';
2059 execute immediate l_stmt;
2060 commit;
2061 if g_debug_flag = 'Y' then
2062 fii_util.stop_timer;
2063 fii_util.print_timer('Duration');
2064
2065 edw_log.put_line(' ');
2066 fii_util.put_timestamp;
2067 edw_log.put_line('Finding extra invoices in EDW which should be deleted');
2068 fii_util.start_timer;
2069 end if;
2070 l_stmt := 'Begin FII_AR_TRX_DIST_F_D.Find_Extra_Trx_EDW@EDW_APPS_TO_WH; End; ';
2071 execute immediate l_stmt;
2072 commit;
2073 l_stmt := 'Begin FII_AR_TRX_DIST_F_D.Count_Extra_Trx_EDW@EDW_APPS_TO_WH(:g_row_count); End; ';
2074 execute immediate l_stmt using out g_row_count;
2075 commit;
2076 if g_debug_flag = 'Y' then
2077 edw_log.put_line('EDW has '||g_row_count||' extra invoices not found in OLTP');
2078 fii_util.stop_timer;
2079 fii_util.print_timer('Duration');
2080
2081 edw_log.put_line(' ');
2082 fii_util.put_timestamp;
2083 edw_log.put_line('Inserting into staging area');
2084 fii_util.start_timer;
2085 end if;
2086 l_stmt := 'Begin FII_AR_TRX_DIST_F_D.Insert_Staging@EDW_APPS_TO_WH(:g_row_count); End; ';
2087 execute immediate l_stmt using out g_row_count;
2088 commit;
2089 if g_debug_flag = 'Y' then
2090 edw_log.put_line('Inserting '||g_row_count||' records marked for deletion');
2091 fii_util.stop_timer;
2092 fii_util.print_timer('Duration');
2093 end if;
2094
2095 If (g_row_count = -1) THEN RAISE L_child_req_failure; End if;
2096
2097 l_stmt := 'Begin FII_AR_TRX_DIST_F_D.Drop_Table@EDW_APPS_TO_WH(''FII_AR_OLTP_TMP_TRX_ID''); End; ';
2098 execute immediate l_stmt;
2099 l_stmt := 'Begin FII_AR_TRX_DIST_F_D.Drop_Table@EDW_APPS_TO_WH(''FII_AR_EDW_TMP_TRX_ID''); End; ';
2100 execute immediate l_stmt;
2101 l_stmt := 'Begin FII_AR_TRX_DIST_F_D.Drop_Table@EDW_APPS_TO_WH(''FII_AR_EDW_EXTRA_ID''); End; ';
2102 execute immediate l_stmt;
2103 fii_util.put_timestamp;
2104
2105
2106 END IF;
2107 END IF;
2108
2109
2110
2111 -- ---------------------------------------------------------------------------
2112 -- END OF Collection , Developer Customizable Section
2113 -- ---------------------------------------------------------------------------
2114
2115 EXCEPTION
2116
2117 WHEN L_LAUNCH_REQ_FAILURE THEN
2118 Errbuf:=g_errbuf;
2119 Retcode:=g_retcode;
2120 l_exception_msg := Retcode || ':' || Errbuf;
2121 rollback; -- rollback any submitted requests
2125 l_seq_id_adjust_line);
2122 delete fii_tmp_pk -- clean out fii_tmp_pk table
2123 where seq_id IN ( l_seq_id_line,
2124 l_seq_id_dist_line,
2126 commit;
2127 if g_debug_flag = 'Y' then
2128 edw_log.put_line('Failure when launching child requests');
2129 end if;
2130 /* Added the if condition. Wrapup should only be called by the
2131 main process not by the child processes. Bug#3077413 */
2132 if ( p_mode='INIT' ) then
2133 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg, g_push_from_date, g_push_to_date);
2134 else
2135 edw_log.put_line('Failure occurred in mode : '||p_mode);
2136 end if;
2137
2138 l_set_completion_status:=FND_CONCURRENT.Set_Completion_Status(status=>'ERROR',message=>l_exception_msg);
2139 -- raise; /* commented out for bug#3052562 */
2140
2141 WHEN L_CHILD_REQ_FAILURE THEN
2142 Errbuf:=g_errbuf;
2143 Retcode:=g_retcode;
2144 l_exception_msg := Retcode || ':' || Errbuf;
2145 DELETE_STG; -- Cleanup local staging table
2146 if g_debug_flag = 'Y' then
2147 edw_log.put_line('One of the child requests have failed');
2148 end if;
2149 /* Added the if condition. Wrapup should only be called by the
2150 main process not by the child processes. Bug#3077413 */
2151 if ( p_mode='INIT' ) then
2152 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg, g_push_from_date, g_push_to_date);
2153 else
2154 edw_log.put_line('Failure occurred in mode : '||p_mode);
2155 end if;
2156 /* Set the completion status to error. bug#3207823 */
2157 l_set_completion_status:=FND_CONCURRENT.Set_Completion_Status(status=>'ERROR',message=>l_exception_msg);
2158 -- raise; /* commented out for bug#3052562 */
2159
2160 WHEN L_PUSH_LOCAL_FAILURE THEN
2161 Errbuf:=g_errbuf;
2162 Retcode:=g_retcode;
2163 l_exception_msg := Retcode || ':' || Errbuf;
2164 rollback; -- Rollback insert into local staging
2165 delete fii_tmp_pk -- clean out fii_tmp_pk table
2166 where seq_id = g_seq_id;
2167 commit;
2168 if g_debug_flag = 'Y' then
2169 edw_log.put_line('Inserting into local staging have failed');
2170 end if;
2171 /* Added the if condition. Wrapup should only be called by the
2172 main process not by the child processes. Bug#3077413 */
2173 if ( p_mode='INIT' ) then
2174 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg, g_push_from_date, g_push_to_date);
2175 else
2176 edw_log.put_line('Failure occurred in mode : '||p_mode);
2177 end if;
2178 /* Set the completion status to error. bug#3207823 */
2179 l_set_completion_status:=FND_CONCURRENT.Set_Completion_Status(status=>'ERROR',message=>l_exception_msg);
2180 -- raise; /* commented out for bug#3052562 */
2181
2182 WHEN L_PUSH_REMOTE_FAILURE THEN
2183 Errbuf:=g_errbuf;
2184 Retcode:=g_retcode;
2185 l_exception_msg := Retcode || ':' || Errbuf;
2186 rollback; -- rollback any insert into remote site
2187 TRUNCATE_TABLE('FII_AR_TRX_DIST_FSTG'); -- Cleanup local staging table
2188 if g_debug_flag = 'Y' then
2189 edw_log.put_line('Data migration from local to remote staging have failed');
2190 end if;
2191 /* Added the if condition. Wrapup should only be called by the
2192 main process not by the child processes. Bug#3077413 */
2193 if ( p_mode='INIT' ) then
2194 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg, g_push_from_date, g_push_to_date);
2195 else
2196 edw_log.put_line('Failure occurred in mode : '||p_mode);
2197 end if;
2198 /* Set the completion status to error. bug#3207823 */
2199 l_set_completion_status:=FND_CONCURRENT.Set_Completion_Status(status=>'ERROR',message=>l_exception_msg);
2200 -- raise; /* commented out for bug#3052562 */
2201
2202 WHEN L_SET_STATUS_FAILURE THEN
2203 Errbuf:=g_errbuf;
2204 Retcode:=g_retcode;
2205 l_exception_msg := Retcode || ':' || Errbuf;
2206 rollback; -- Rollback the status to 'LOCAL READY'
2207 DELETE_STG; -- Delete records in staging with status 'LOCAL READY'
2208 commit;
2209 if g_debug_flag = 'Y' then
2210 edw_log.put_line('Setting status to READY have failed');
2211 end if;
2212 /* Added the if condition. Wrapup should only be called by the
2213 main process not by the child processes. Bug#3077413 */
2214 if ( p_mode='INIT' ) then
2215 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg, g_push_from_date, g_push_to_date);
2216 else
2217 edw_log.put_line('Failure occurred in mode : '||p_mode);
2218 end if;
2219 /* Set the completion status to error. bug#3207823 */
2220 l_set_completion_status:=FND_CONCURRENT.Set_Completion_Status(status=>'ERROR',message=>l_exception_msg);
2221 -- raise; /* commented out for bug#3052562 */
2222
2223 WHEN L_IDEN_CHANGE_FAILURE THEN
2224 Errbuf:=g_errbuf;
2225 Retcode:=g_retcode;
2226 l_exception_msg := Retcode || ':' || Errbuf;
2227 delete fii_tmp_pk
2228 where seq_id IN ( l_seq_id_line,
2229 l_seq_id_dist_line,
2230 l_seq_id_adjust_line,
2231 g_seq_id);
2232 commit;
2233 if g_debug_flag = 'Y' then
2234 edw_log.put_line('Identifying changed records have Failed');
2235 end if;
2236 /* Added the if condition. Wrapup should only be called by the
2237 main process not by the child processes. Bug#3077413 */
2238 if ( p_mode='INIT' ) then
2239 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg, g_push_from_date, g_push_to_date);
2240 else
2241 edw_log.put_line('Failure occurred in mode : '||p_mode);
2242 end if;
2243 /* Set the completion status to error. bug#3207823 */
2244 l_set_completion_status:=FND_CONCURRENT.Set_Completion_Status(status=>'ERROR',message=>l_exception_msg);
2245 -- raise; /* commented out for bug#3052562 */
2246
2247 WHEN L_ITEM_FK_FAILURE THEN
2248 Errbuf:=g_errbuf;
2249 Retcode:=g_retcode;
2250 l_exception_msg := Retcode || ':' || Errbuf;
2251 DELETE_STG; -- Delete records in staging with status 'LOCAL READY'
2252 if g_debug_flag = 'Y' then
2253 edw_log.put_line('Error updating item foreign key');
2254 end if;
2255 /* Added the if condition. Wrapup should only be called by the
2256 main process not by the child processes. Bug#3077413 */
2257 if ( p_mode='INIT' ) then
2258 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg, g_push_from_date, g_push_to_date);
2259 else
2260 edw_log.put_line('Failure occurred in mode : '||p_mode);
2261 end if;
2262 /* Set the completion status to error. bug#3207823 */
2263 l_set_completion_status:=FND_CONCURRENT.Set_Completion_Status(status=>'ERROR',message=>l_exception_msg);
2264 -- raise; /* commented out for bug#3052562 */
2265
2266
2267 WHEN OTHERS THEN
2268 Errbuf:=g_errbuf;
2269 Retcode:=g_retcode;
2270 l_exception_msg := Retcode || ':' || Errbuf;
2271 rollback;
2272 delete fii_tmp_pk
2273 where seq_id IN ( l_seq_id_line,
2274 l_seq_id_dist_line,
2275 l_seq_id_adjust_line,
2276 g_seq_id);
2277 commit;
2278 if g_debug_flag = 'Y' then
2279 edw_log.put_line('Other errors');
2280 end if;
2281 /* Added the if condition. Wrapup should only be called by the
2282 main process not by the child processes. Bug#3077413 */
2283 if ( p_mode='INIT' ) then
2284 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg, g_push_from_date, g_push_to_date);
2285 else
2286 edw_log.put_line('Failure occurred in mode : '||p_mode);
2287 end if;
2288 /* Set the completion status to error. bug#3207823 */
2289 l_set_completion_status:=FND_CONCURRENT.Set_Completion_Status(status=>'ERROR',message=>l_exception_msg);
2290 -- raise; /* commented out for bug#3052562 */
2291
2292 END;
2293
2294 END FII_AR_TRX_DIST_F_C;