[Home] [Help]
PACKAGE BODY: APPS.FII_AP_INV_LINES_F_C
Source
1 Package Body FII_AP_INV_LINES_F_C AS
2 /* $Header: FIIAP09B.pls 120.31 2006/01/19 12:30:15 sgautam ship $ */
3 G_PUSH_DATE_RANGE1 Date:=Null;
4 G_PUSH_DATE_RANGE2 Date:=Null;
5 g_row_count Number:=0;
6 g_exception_msg varchar2(2000):=Null;
7 g_errbuf VARCHAR2(2000) := NULL;
8 g_retcode VARCHAR2(200) := NULL;
9 g_missing_rates Number:=0;
10 g_collect_er Varchar2(1); -- Added for iExpense Enhancement,12-DEC-02
11 g_acct_or_inv_date Number; -- Added for Currency Conversion Date Enhancement , 04-APR-03
12
13 -----------------------------------------------------------
14 -- PROCEDURE TRUNCATE_TABLE
15 -----------------------------------------------------------
16
17 PROCEDURE TRUNCATE_TABLE (table_name varchar2)
18 IS
19
20 l_fii_schema VARCHAR2(30);
21 l_stmt VARCHAR2(200);
22 l_status VARCHAR2(30);
23 l_industry VARCHAR2(30);
24
25 BEGIN
26 IF (FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, l_fii_schema)) THEN
27 l_stmt := 'TRUNCATE TABLE ' || l_fii_schema ||'.'||table_name;
28 EXECUTE IMMEDIATE l_stmt;
29 END IF;
30 edw_log.put_line(' ');
31 edw_log.put_line('Truncating '|| table_name||' table');
32
33
34 END;
35
36 -----------------------------------------------------------
37 -- PROCEDURE DELETE_STG
38 -----------------------------------------------------------
39
40 PROCEDURE DELETE_STG
41 IS
42
43 BEGIN
44 DELETE FII_AP_INV_LINES_FSTG
45 WHERE COLLECTION_STATUS = 'LOCAL READY'OR ( COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR COLLECTION_STATUS = 'INVALID CURRENCY')
46 AND INSTANCE = (SELECT INSTANCE_CODE
47 FROM EDW_LOCAL_INSTANCE);
48 END;
49
50 ------------------------------------------------------------
51 --PROCEDURE INSERT_MISSING_RATES_IN_TMP
52 -------------------------------------------------------------
53 --Identify records that have missing rates and insert them in a temp table
54
55 PROCEDURE INSERT_MISSING_RATES_IN_TMP
56 IS
57
58 BEGIN
59
60 -- --------------------------------------------------------------------------------------------------
61 -- The variable g_acct_or_inv_date is added in the below mentioned select statement.
62 -- The profile option stored in the global variable g_acct_or_inv_date
63 -- will be stored in the column Primary_Key5 . Modified for Currency Conversion Date Enhancement,25-APR-03
64 -----------------------------------------------------------------------------------------------------
65 -- Need to store invoice_line_number in the temp table. Change done for Inv Lines Uptake
66 -- See design doc on fol for more details
67 ------------------------------------------------------------------------------------------------------
68
69 INSERT INTO fii_ap_tmp_line_pk(
70 Primary_Key1,
71 Primary_Key2,
72 Primary_key4,
73 Primary_Key5 )
74 SELECT
75 TO_NUMBER(SUBSTR (INV_LINE_PK, 1, INSTR(INV_LINE_PK, '-' )-1)),
76 TO_NUMBER(SUBSTR (INV_LINE_PK, INSTR(INV_LINE_PK, '-')+1,INSTR(INV_LINE_PK, '-',1,2) -
77 (INSTR(INV_LINE_PK,'-')+1))) ,
78 TO_NUMBER(SUBSTR(INV_LINE_PK,INSTR('INV_LINE_PK','-',1,2)+1,INSTR(INV_LINE_PK,'-',1,3)-
79 (INSTR(INV_LINE_PK,'-',1,2)+1))),
80 g_acct_or_inv_date
81
82 FROM FII_AP_INV_LINES_FSTG fil
83
84 WHERE
85
86 fil.COLLECTION_STATUS = 'RATE NOT AVAILABLE'
87 OR
88 fil.COLLECTION_STATUS = 'INVALID CURRENCY';
89
90 IF (sql%rowcount > 0) THEN
91 g_retcode := 1;
92 g_missing_rates := 1;
93 END IF;
94 --Generates "Warning" message in the Status column of Concurrent Manager "Requests" table
95
96 edw_log.put_line(' ');
97 edw_log.put_line('INSERTING ' || to_char(sql%rowcount) || ' rows from staging table');
98 edw_log.put_line('g_retcode is '||g_retcode);
99 END;
100
101
102 --------------------------------------------------
103 --FUNCTION LOCAL_SAME_AS_REMOTE
104 ---------------------------------------------------
105
106 FUNCTION LOCAL_SAME_AS_REMOTE RETURN BOOLEAN
107 IS
108
109 l_instance1 Varchar2(100) :=Null;
110 l_instance2 Varchar2(100) :=Null;
111
112 BEGIN
113
114
115 SELECT instance_code
116 INTO l_instance1
117 FROM edw_local_instance;
118
119 SELECT instance_code
120 INTO l_instance2
121 FROM edw_local_instance@edw_apps_to_wh;
122
123 IF (l_instance1 = l_instance2) THEN
124 RETURN TRUE;
125 END IF;
126
127 RETURN FALSE;
128
129 EXCEPTION
130 WHEN NO_DATA_FOUND THEN
131
132 RETURN FALSE;
133
134 END;
135
136
137 --------------------------------------------------
138 --FUNCTION SET_STATUS_READY
139 ---------------------------------------------------
140
141 FUNCTION SET_STATUS_READY RETURN NUMBER
142 IS
143
144 BEGIN
145
146 UPDATE FII_AP_INV_LINES_FSTG
147 SET COLLECTION_STATUS = 'READY'
148 WHERE COLLECTION_STATUS = 'LOCAL READY'
149 AND INSTANCE = (SELECT INSTANCE_CODE
150 FROM EDW_LOCAL_INSTANCE);
151
152 RETURN(sql%rowcount);
153
154 EXCEPTION
155 WHEN OTHERS THEN
156 g_errbuf:=sqlerrm;
157 g_retcode:=sqlcode;
158 rollback;
159 RETURN(-1);
160
161 END;
162
163
164 -----------------------------------------------------------
165 --FUNCTION PUSH_TO_LOCAL
166 -----------------------------------------------------------
167
168 FUNCTION PUSH_TO_LOCAL RETURN NUMBER IS
169
170 l_mau number; -- minimum accountable unit of
171 -- global warehouse currency
172
173 L_MAU_NOT_AVAILABLE exception;
174
175 BEGIN
176
177 -- get minimum accountable unit of the warehouse currency;
178
179 l_mau := nvl( edw_currency.get_mau, 0.01 );
180
181 -- ------------------------------------------------
182 -- We set the COLLECTION_STATUS to 'LOCAL READY'.
183 -- In case of source=target, we need to separate
184 -- out the records in progress vs the records which
185 -- is ready to be picked up by collection enginee.
186 -- In our case, we consider the records to be in
187 -- progress until all the child processes have
188 -- completed successfully.
189 -- ------------------------------------------------
190 fii_flex_mapping.init_cache('FII_AP_INV_LINES_F');
191
192 Insert Into FII_AP_INV_LINES_FSTG(
193 APPROVAL_STATUS,
194 ACCOUNTING_DATE,
195 ACCOUNTING_DATE_FK,
196 ACCRUAL_POSTED_FLAG,
197 AMT_INCLUDES_TAX_FLAG,
198 ASSETS_TRACKING_FLAG,
199 AWT_FLAG,
200 AWT_GROUP_ID,
201 BASE_CURRENCY_CODE,
202 BATCH_ID,
203 CASH_JE_BATCH_ID,
204 CASH_POSTED_FLAG,
205 CATEGORY_ID,
206 CCID,
207 CREATION_DATE,
208 DUNS_FK,
209 EMPLOYEE_FK,
210 ENCUMBERED_FLAG,
211 EXCHANGE_DATE,
212 EXCHANGE_RATE,
213 EXCHANGE_RATE_TYPE,
214 EXCHANGE_RATE_VAR,
215 GEOGRAPHY_FK,
216 GL_ACCT10_FK,
217 GL_ACCT1_FK,
218 GL_ACCT2_FK,
219 GL_ACCT3_FK,
220 GL_ACCT4_FK,
221 GL_ACCT5_FK,
222 GL_ACCT6_FK,
223 GL_ACCT7_FK,
224 GL_ACCT8_FK,
225 GL_ACCT9_FK,
226 INCOME_TAX_REGION,
227 INSTANCE,
228 INSTANCE_FK,
229 INV_CURRENCY_FK,
230 INV_DATE,
231 INV_FK,
232 INV_LINE_AMT_B,
233 INV_LINE_AMT_G,
234 INV_LINE_AMT_T,
235 INV_LINE_COUNT,
236 INV_LINE_DESCRIPTION,
237 INV_LINE_PK,
238 INV_LINE_TYPE_FK,
239 INV_NUM,
240 INV_PRICE_VAR_AMT_B,
241 INV_PRICE_VAR_AMT_G,
242 INV_PRICE_VAR_AMT_T,
243 INV_SOURCE_FK,
244 INV_TYPE,
245 INV_UNIT_PRICE_B,
246 INV_UNIT_PRICE_G,
247 INV_UNIT_PRICE_T,
248 ITEM_DESCRIPTION,
249 ITEM_FK,
250 ITEM_ID,
251 LAST_UPDATE_DATE,
252 MATCH_LINE_AMT_B,
253 MATCH_LINE_AMT_G,
254 MATCH_LINE_AMT_T,
255 MATCH_LINE_COUNT,
256 MATCH_STATUS_FLAG,
257 ORG_FK,
258 PAYMENT_TERM_FK,
259 POSTED_AMT_B,
260 POSTED_AMT_G,
261 POSTED_AMT_T,
262 POSTED_FLAG,
263 PO_AMT_B,
264 PO_AMT_G,
265 PO_AMT_T,
266 PO_DISTRIBUTION_ID,
267 PO_NUMBER,
268 PO_UNIT_PRICE_B,
269 PO_UNIT_PRICE_G,
270 PO_UNIT_PRICE_T,
271 PROJECT_ID,
272 QTY_VAR_AMT_B,
273 QTY_VAR_AMT_G,
274 QTY_VAR_AMT_T,
275 QUANTITY_INVOICED_G,
276 QUANTITY_INVOICED_T,
277 SIC_CODE_FK,
278 SOB_FK,
279 SUPPLIER_FK,
280 SUPPLIER_SITE_ID,
281 TOTAL_VAR_AMT_B,
282 TOTAL_VAR_AMT_G,
283 TOTAL_VAR_AMT_T,
284 TYPE_1099,
285 UNMATCH_LINE_AMT_B,
286 UNMATCH_LINE_AMT_G,
287 UNMATCH_LINE_AMT_T,
288 UNMATCH_LINE_COUNT,
289 UNSPSC_FK,
290 UOM_G_FK,
291 UOM_T_FK,
292 USER_ATTRIBUTE1,
293 USER_ATTRIBUTE10,
294 USER_ATTRIBUTE2,
295 USER_ATTRIBUTE3,
296 USER_ATTRIBUTE4,
297 USER_ATTRIBUTE5,
298 USER_ATTRIBUTE6,
299 USER_ATTRIBUTE7,
300 USER_ATTRIBUTE8,
301 USER_ATTRIBUTE9,
302 USER_FK1,
303 USER_FK2,
304 USER_FK3,
305 USER_FK4,
306 USER_FK5,
307 USER_MEASURE1,
308 USER_MEASURE2,
309 USER_MEASURE3,
310 USER_MEASURE4,
311 USER_MEASURE5,
312 VAT_CODE,
313 OPERATION_CODE,
314 COLLECTION_STATUS,
315 DISCOUNT_AMT_T, --Added the following three columns for New Information Enhancement, nov/12/2002
316 DISCOUNT_AMT_B,
317 DISCOUNT_AMT_G,
318 PROJECT_FK, --Added the following four columns for New Information Enhancement, nov/26/2002
319 EXPENDITURE_TYPE,
320 VOUCHER_NUMBER,
321 DOC_SEQUENCE_VALUE,
322 TASK_ID, -- Addded for bug#2926033
323 RCV_TRANSACTION_ID) -- Added for bug#3116554
324 select
325 APPROVAL_STATUS,
326 ACCOUNTING_DATE,
327 NVL(ACCOUNTING_DATE_FK,'NA_EDW'),
328 ACCRUAL_POSTED_FLAG,
329 AMT_INCLUDES_TAX_FLAG,
330 ASSETS_TRACKING_FLAG,
331 AWT_FLAG,
332 AWT_GROUP_ID,
333 BASE_CURRENCY_CODE,
334 BATCH_ID,
335 CASH_JE_BATCH_ID,
336 CASH_POSTED_FLAG,
337 CATEGORY_ID,
338 CCID,
339 CREATION_DATE,
340 NVL(DUNS_FK,'NA_EDW'),
341 NVL(EMPLOYEE_FK,'NA_EDW'),
342 ENCUMBERED_FLAG,
343 EXCHANGE_DATE,
344 EXCHANGE_RATE,
345 EXCHANGE_RATE_TYPE,
346 EXCHANGE_RATE_VAR,
347 NVL(GEOGRAPHY_FK,'NA_EDW'),
348 NVL(GL_ACCT10_FK,'NA_EDW'),
349 NVL(GL_ACCT1_FK,'NA_EDW'),
350 NVL(GL_ACCT2_FK,'NA_EDW'),
351 NVL(GL_ACCT3_FK,'NA_EDW'),
352 NVL(GL_ACCT4_FK,'NA_EDW'),
353 NVL(GL_ACCT5_FK,'NA_EDW'),
354 NVL(GL_ACCT6_FK,'NA_EDW'),
355 NVL(GL_ACCT7_FK,'NA_EDW'),
356 NVL(GL_ACCT8_FK,'NA_EDW'),
357 NVL(GL_ACCT9_FK,'NA_EDW'),
358 INCOME_TAX_REGION,
359 INSTANCE,
360 NVL(INSTANCE_FK,'NA_EDW'),
361 NVL(INV_CURRENCY_FK,'NA_EDW'),
362 INV_DATE,
363 NVL(INV_FK,'NA_EDW'),
364 INV_LINE_AMT_B,
365 round((INV_LINE_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
366 INV_LINE_AMT_T,
367 INV_LINE_COUNT,
368 INV_LINE_DESCRIPTION,
369 INV_LINE_PK,
370 NVL(INV_LINE_TYPE_FK,'NA_EDW'),
371 INV_NUM,
372 INV_PRICE_VAR_AMT_B,
373 round((INV_PRICE_VAR_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
374 INV_PRICE_VAR_AMT_T,
375 NVL(INV_SOURCE_FK,'NA_EDW'),
376 INV_TYPE,
377 INV_UNIT_PRICE_B,
378 round((INV_UNIT_PRICE_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
379 INV_UNIT_PRICE_T,
380 ITEM_DESCRIPTION,
381 NVL(ITEM_FK,'NA_EDW'),
382 ITEM_ID,
383 LAST_UPDATE_DATE,
384 MATCH_LINE_AMT_B,
385 round((MATCH_LINE_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
386 MATCH_LINE_AMT_T,
387 MATCH_LINE_COUNT,
388 MATCH_STATUS_FLAG,
389 NVL(ORG_FK,'NA_EDW'),
390 NVL(PAYMENT_TERM_FK,'NA_EDW'),
391 POSTED_AMT_B,
392 round((POSTED_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
393 POSTED_AMT_T,
394 POSTED_FLAG,
395 PO_AMT_B,
396 round((PO_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
397 PO_AMT_T,
398 PO_DISTRIBUTION_ID,
399 PO_NUMBER,
400 PO_UNIT_PRICE_B,
401 round((PO_UNIT_PRICE_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
402 PO_UNIT_PRICE_T,
403 PROJECT_ID,
404 QTY_VAR_AMT_B,
405 round((QTY_VAR_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
406 QTY_VAR_AMT_T,
407 QUANTITY_INVOICED_G,
408 QUANTITY_INVOICED_T,
409 NVL(SIC_CODE_FK,'NA_EDW'),
410 NVL(SOB_FK,'NA_EDW'),
411 NVL(SUPPLIER_FK,'NA_EDW'),
412 SUPPLIER_SITE_ID,
413 TOTAL_VAR_AMT_B,
414 round((TOTAL_VAR_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
415 TOTAL_VAR_AMT_T,
416 TYPE_1099,
417 UNMATCH_LINE_AMT_B,
418 round((UNMATCH_LINE_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
419 UNMATCH_LINE_AMT_T,
420 UNMATCH_LINE_COUNT,
421 NVL(UNSPSC_FK,'NA_EDW'),
422 NVL(UOM_G_FK,'NA_EDW'),
423 NVL(UOM_T_FK,'NA_EDW'),
424 USER_ATTRIBUTE1,
425 USER_ATTRIBUTE10,
426 USER_ATTRIBUTE2,
427 USER_ATTRIBUTE3,
428 USER_ATTRIBUTE4,
429 USER_ATTRIBUTE5,
430 USER_ATTRIBUTE6,
431 USER_ATTRIBUTE7,
432 USER_ATTRIBUTE8,
433 USER_ATTRIBUTE9,
434 NVL(USER_FK1,'NA_EDW'),
435 NVL(USER_FK2,'NA_EDW'),
436 NVL(USER_FK3,'NA_EDW'),
437 NVL(USER_FK4,'NA_EDW'),
438 NVL(USER_FK5,'NA_EDW'),
442 set_of_books_id,
439 --USER_MEASURE1,
440 invoice_distribution_id,
441 --USER_MEASURE2,
443 -- USER_MEASURE3,
444 old_dist_line_number,
445 USER_MEASURE4,
446 USER_MEASURE5,
447 VAT_CODE,
448 NULL, -- OPERATION_CODE
449 decode(GLOBAL_CURRENCY_RATE,
450 NULL, 'RATE NOT AVAILABLE',
451 -1, 'RATE NOT AVAILABLE',
452 -2, 'RATE NOT AVAILABLE',
453 'LOCAL READY'),
454 0, --added the following three columns for New Information Enhancement, Nov/12/2002
455 0,
456 GLOBAL_CURRENCY_RATE,
457 PROJECT_FK, --Added the following four columns for New Information Enhancement, nov/26/2002
458 EXPENDITURE_TYPE,
459 VOUCHER_NUMBER,
460 DOC_SEQUENCE_VALUE,
461 TASK_ID, -- Added for bug#2926033
462 RCV_TRANSACTION_ID -- Added for bug#3116554
463 from FII_AP_INV_LINES_FCV;
464
465 fii_flex_mapping.free_mem_all;
466 edw_log.put_line('g_row_count');
467 edw_log.put_line(TO_CHAR(sql%rowcount));
468 RETURN(sql%rowcount);
469
470 EXCEPTION
471 WHEN OTHERS THEN
472 g_errbuf:=sqlerrm;
473 g_retcode:=sqlcode;
474 edw_log.put_line('inside exception of local push');
475 rollback;
476 RETURN(-1);
477
478 END;
479 -----------------------------------------------------------
480 -- PROCEDURE UPDATE_DISCOUNT_AMT
481 -- NEW PROCEDURE ESPECIALLY FOR CALCULATING THE DISTRIBUTED
482 -- DISCOUNT AMOUNT FOR MERILL LYNCH, NOV-12-2002
483 -- Modified by PHU on DEC-30-2002
484 -----------------------------------------------------------
485 PROCEDURE UPDATE_DISCOUNT_AMT IS
486 l_mau NUMBER;
487
488 cursor c_tmp is
489 select apk.PRIMARY_KEY_CHAR1 pk,
490 apk.PRIMARY_KEY4 amt_t,
491 apk.PRIMARY_KEY5 amt_b
492 from fii_ap_tmp_line_pk apk
493 where apk.SEQ_ID = -878;
494
495 l_temp_date DATE;
496 l_duration NUMBER;
497 l_count NUMBER:=0;
498
499 l_fii_schema VARCHAR2(30);
500 l_status VARCHAR2(30);
501 l_industry VARCHAR2(30);
502
503 BEGIN
504
505 edw_log.put_line('Updating credit amount information in local staging table');
506 edw_log.put_line('');
507
508 -- get minimum accountable unit of the warehouse currency;
509
510 l_mau := nvl( edw_currency.get_mau, 0.01);
511
512 -- truncate fii_ap_tmp_line_pk
513
514 TRUNCATE_TABLE ('fii_ap_tmp_line_pk');
515
516 -- populate fii_ap_tmp_line_pk from local staging
517
518 l_temp_date := sysdate;
519
520 insert into fii_ap_tmp_line_pk (
521 SEQ_ID,
522 PRIMARY_KEY1, --invoice_id
523 PRIMARY_KEY2, --old_dist_line_number
524 /* PRIMARY_KEY_CHAR2, ap_ae_lines_all.reference8 */
525 PRIMARY_KEY3, --invoice_distribution_id,
526 PRIMARY_KEY4, --set of books id
527 PRIMARY_KEY_CHAR1) --inv_line_pk
528
529 select
530 -919,
531 to_number(substr(fstg.inv_line_pk, 1, instr(fstg.inv_line_pk, '-') - 1)),
532 /* substr(fstg.inv_line_pk, instr(fstg.inv_line_pk, '-', 1, 1) + 1,
533 instr(fstg.inv_line_pk, '-', 1, 2) -
534 instr(fstg.inv_line_pk, '-', 1, 1) - 1), */
535 fstg.user_measure3, --old_dist_line_number
536 fstg.user_measure1, --invoice_distribution_id
537 fstg.user_measure2,
538 fstg.inv_line_pk
539 from fii_ap_inv_lines_fstg fstg
540 where fstg.collection_status = 'LOCAL READY';
541
542 l_duration := sysdate - l_temp_date;
543 edw_log.put_line ('Process Time for Insert into TMP: '||edw_log.duration(l_duration));
544
545 -- populate Discount Amounts into fii_ap_tmp_line_pk
546
547 l_temp_date := sysdate;
548
549
550
551 /* need to analyze the temp table */
552
553 IF (FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, l_fii_schema)) THEN
554 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_fii_schema,
555 TABNAME => 'FII_AP_TMP_LINE_PK') ;
556 END IF;
557
558 --bug 3012243: consider DR columns in calculating discount
559 -- bug 3381164 : added leading hint to imporve the performance
560 insert into fii_ap_tmp_line_pk (
561 SEQ_ID,
562 PRIMARY_KEY_CHAR1, --inv_line_pk
563 PRIMARY_KEY4, --discount_amt_t
564 PRIMARY_KEY5) --discount_amt_b
565 SELECT -878,
566 apk.PRIMARY_KEY_CHAR1,
567 sum (nvl (aphd.amount,0)),
568 sum(nvl(aphd.paid_base_amount,0))
569 FROM fii_ap_tmp_line_pk apk,
570 ap_invoice_payments_all aip,
571 ap_payment_hist_dists aphd,
572 ap_payment_history_all aph
573 WHERE apk.PRIMARY_KEY1 = aip.invoice_id
574 AND aip.invoice_payment_id = aphd.invoice_payment_id
575 AND aphd.PAY_DIST_LOOKUP_CODE = 'DISCOUNT'
576 AND aphd.invoice_distribution_id = apk.PRIMARY_KEY3
577 AND nvl(aph.historical_flag, 'N') = 'N'
578 AND APH.check_id = aip.check_id
579 AND aph.payment_history_id=aphd.payment_history_id
583 UNION
580 AND aphd.bank_curr_amount is null
581 AND aphd.cleared_base_amount is null
582 group by apk.primary_key_char1
584 SELECT -878,
585 apk.PRIMARY_KEY_CHAR1,
586 NVL(sum(xal.entered_cr), 0) - NVL(sum(xal.entered_dr), 0),
587 NVL(sum(NVL(xal.accounted_cr, xal.entered_cr)), 0) -
588 NVL(sum(NVL(xal.accounted_dr, xal.entered_dr)), 0)
589 FROM fii_ap_tmp_line_pk apk,
590 ap_invoice_payments_all aip,
591 -- ap_payment_history_all aph,
592 xla_ae_lines xal,
593 xla_ae_headers xah
594 WHERE apk.PRIMARY_KEY1 = aip.invoice_id
595 AND aip.invoice_payment_id = xal.Upg_Tax_Reference_ID3
596 AND apk.PRIMARY_KEY2 = xal.Upg_Tax_Reference_ID2
597 AND xal.accounting_class_code = 'DISCOUNT'
598 -- AND APH.check_id = aip.check_id
599 -- AND nvl(aph.historical_flag, 'N') = 'Y'
600 AND xal.application_id=200
601 AND xah.ae_header_id=xal.ae_header_id
602 AND xah.ledger_id = apk.primary_key4
603 group by apk.primary_key_char1;
604
605 l_duration := sysdate - l_temp_date;
606 edw_log.put_line ('Process Time for Insert into TMP w/ Discount: ' ||
607 edw_log.duration(l_duration));
608
609 -- update fii_ap_inv_lines_fstg from fii_ap_tmp_line_pk
610
611 l_temp_date := sysdate;
612
613 FOR v_tmp IN c_tmp LOOP
614
615 -- NOTE: discount_amt_g was populated with GLOBAL_CURRENCY_RATE previously
616
617 UPDATE /*+ ORDERED USE_NL (FSTG) */
618 fii_ap_inv_lines_fstg fstg
619 SET discount_amt_t = v_tmp.amt_t,
620 discount_amt_b = v_tmp.amt_b,
621 discount_amt_g = ROUND(v_tmp.amt_b * discount_amt_g /l_mau)*l_mau
622 WHERE fstg.inv_line_pk = v_tmp.pk;
623 l_count := l_count + 1;
624
625 END LOOP;
626
627 -- set DISCOUNT_AMT_G = 0 for those no discount records
628 update FII_AP_INV_LINES_FSTG
629 set DISCOUNT_AMT_G = 0
630 where DISCOUNT_AMT_B = 0;
631
632 l_duration := sysdate - l_temp_date;
633 edw_log.put_line ('Process Time for Update: ' || edw_log.duration(l_duration));
634 edw_log.put_line ('# of Updated Records: ' || l_count);
635
636 -- fii_util.stop_timer;
637 -- fii_util.print_timer('Duration');
638
639 EXCEPTION
640 WHEN OTHERS THEN
641 g_errbuf:=sqlerrm;
642 g_retcode:=sqlcode;
643 rollback;
644 raise;
645
646 END;
647
648
649 -----------------------------------------------------------
650 -- FUNCTION PUSH_REMOTE
651 -----------------------------------------------------------
652 FUNCTION PUSH_REMOTE RETURN NUMBER
653 IS
654
655 BEGIN
656
657 -- Bug 3716166. Added substrb to all the varchar2 columns.
658
659 Insert Into FII_AP_INV_LINES_FSTG@EDW_APPS_TO_WH(
660 APPROVAL_STATUS,
661 ACCOUNTING_DATE,
662 ACCOUNTING_DATE_FK,
663 ACCRUAL_POSTED_FLAG,
664 AMT_INCLUDES_TAX_FLAG,
665 ASSETS_TRACKING_FLAG,
666 AWT_FLAG,
667 AWT_GROUP_ID,
668 BASE_CURRENCY_CODE,
669 BATCH_ID,
670 CASH_JE_BATCH_ID,
671 CASH_POSTED_FLAG,
672 CATEGORY_ID,
673 CCID,
674 CREATION_DATE,
675 DUNS_FK,
676 EMPLOYEE_FK,
677 ENCUMBERED_FLAG,
678 EXCHANGE_DATE,
679 EXCHANGE_RATE,
680 EXCHANGE_RATE_TYPE,
681 EXCHANGE_RATE_VAR,
682 GEOGRAPHY_FK,
683 GL_ACCT10_FK,
684 GL_ACCT1_FK,
685 GL_ACCT2_FK,
686 GL_ACCT3_FK,
687 GL_ACCT4_FK,
688 GL_ACCT5_FK,
689 GL_ACCT6_FK,
690 GL_ACCT7_FK,
691 GL_ACCT8_FK,
692 GL_ACCT9_FK,
693 INCOME_TAX_REGION,
694 INSTANCE,
695 INSTANCE_FK,
696 INV_CURRENCY_FK,
697 INV_DATE,
698 INV_FK,
699 INV_LINE_AMT_B,
700 INV_LINE_AMT_G,
701 INV_LINE_AMT_T,
702 INV_LINE_COUNT,
703 INV_LINE_DESCRIPTION,
704 INV_LINE_PK,
705 INV_LINE_TYPE_FK,
706 INV_NUM,
707 INV_PRICE_VAR_AMT_B,
708 INV_PRICE_VAR_AMT_G,
709 INV_PRICE_VAR_AMT_T,
710 INV_SOURCE_FK,
711 INV_TYPE,
712 INV_UNIT_PRICE_B,
713 INV_UNIT_PRICE_G,
714 INV_UNIT_PRICE_T,
715 ITEM_DESCRIPTION,
716 ITEM_FK,
717 ITEM_ID,
718 LAST_UPDATE_DATE,
719 MATCH_LINE_AMT_B,
720 MATCH_LINE_AMT_G,
721 MATCH_LINE_AMT_T,
722 MATCH_LINE_COUNT,
723 MATCH_STATUS_FLAG,
724 ORG_FK,
725 PAYMENT_TERM_FK,
726 POSTED_AMT_B,
727 POSTED_AMT_G,
728 POSTED_AMT_T,
729 POSTED_FLAG,
730 PO_AMT_B,
731 PO_AMT_G,
732 PO_AMT_T,
733 PO_DISTRIBUTION_ID,
734 PO_NUMBER,
735 PO_UNIT_PRICE_B,
736 PO_UNIT_PRICE_G,
737 PO_UNIT_PRICE_T,
738 PROJECT_ID,
739 QTY_VAR_AMT_B,
740 QTY_VAR_AMT_G,
741 QTY_VAR_AMT_T,
742 QUANTITY_INVOICED_G,
746 SUPPLIER_FK,
743 QUANTITY_INVOICED_T,
744 SIC_CODE_FK,
745 SOB_FK,
747 SUPPLIER_SITE_ID,
748 TOTAL_VAR_AMT_B,
749 TOTAL_VAR_AMT_G,
750 TOTAL_VAR_AMT_T,
751 TYPE_1099,
752 UNMATCH_LINE_AMT_B,
753 UNMATCH_LINE_AMT_G,
754 UNMATCH_LINE_AMT_T,
755 UNMATCH_LINE_COUNT,
756 UNSPSC_FK,
757 UOM_G_FK,
758 UOM_T_FK,
759 USER_ATTRIBUTE1,
760 USER_ATTRIBUTE10,
761 USER_ATTRIBUTE2,
762 USER_ATTRIBUTE3,
763 USER_ATTRIBUTE4,
764 USER_ATTRIBUTE5,
765 USER_ATTRIBUTE6,
766 USER_ATTRIBUTE7,
767 USER_ATTRIBUTE8,
768 USER_ATTRIBUTE9,
769 USER_FK1,
770 USER_FK2,
771 USER_FK3,
772 USER_FK4,
773 USER_FK5,
774 USER_MEASURE1,
775 USER_MEASURE2,
776 USER_MEASURE3,
777 USER_MEASURE4,
778 USER_MEASURE5,
779 VAT_CODE,
780 OPERATION_CODE,
781 COLLECTION_STATUS,
782 DISCOUNT_AMT_T, --Added the following three columns for New Information Enhancement, nov/12/2002
783 DISCOUNT_AMT_B,
784 DISCOUNT_AMT_G,
785 PROJECT_FK, --Added the following four columns for New Information Enhancement, nov/26/2002
786 EXPENDITURE_TYPE,
787 VOUCHER_NUMBER,
788 DOC_SEQUENCE_VALUE,
789 TASK_ID, -- Added for bug#2926033
790 RCV_TRANSACTION_ID) -- Added for bug#3116554
791 select
792 substrb(APPROVAL_STATUS,1,25),
793 ACCOUNTING_DATE,
794 NVL(ACCOUNTING_DATE_FK,'NA_EDW'),
795 substrb(ACCRUAL_POSTED_FLAG, 1, 1),
796 substrb(AMT_INCLUDES_TAX_FLAG, 1, 1),
797 substrb(ASSETS_TRACKING_FLAG, 1, 1),
798 substrb(AWT_FLAG, 1, 1),
799 AWT_GROUP_ID,
800 substrb(BASE_CURRENCY_CODE,1,15),
801 BATCH_ID,
802 CASH_JE_BATCH_ID,
803 substrb(CASH_POSTED_FLAG,1,1),
804 CATEGORY_ID,
805 CCID,
806 CREATION_DATE,
807 NVL(DUNS_FK,'NA_EDW'),
808 NVL(EMPLOYEE_FK,'NA_EDW'),
809 substrb(ENCUMBERED_FLAG,1,1),
810 EXCHANGE_DATE,
811 EXCHANGE_RATE,
812 substrb(EXCHANGE_RATE_TYPE,1,30),
813 EXCHANGE_RATE_VAR,
814 NVL(GEOGRAPHY_FK,'NA_EDW'),
815 NVL(GL_ACCT10_FK,'NA_EDW'),
816 NVL(GL_ACCT1_FK,'NA_EDW'),
817 NVL(GL_ACCT2_FK,'NA_EDW'),
818 NVL(GL_ACCT3_FK,'NA_EDW'),
819 NVL(GL_ACCT4_FK,'NA_EDW'),
820 NVL(GL_ACCT5_FK,'NA_EDW'),
821 NVL(GL_ACCT6_FK,'NA_EDW'),
822 NVL(GL_ACCT7_FK,'NA_EDW'),
823 NVL(GL_ACCT8_FK,'NA_EDW'),
824 NVL(GL_ACCT9_FK,'NA_EDW'),
825 substrb(INCOME_TAX_REGION,1,10),
826 substrb(INSTANCE,1,40),
827 NVL(INSTANCE_FK,'NA_EDW'),
828 NVL(INV_CURRENCY_FK,'NA_EDW'),
829 INV_DATE,
830 NVL(INV_FK,'NA_EDW'),
831 INV_LINE_AMT_B,
832 INV_LINE_AMT_G,
833 INV_LINE_AMT_T,
834 INV_LINE_COUNT,
835 substrb(INV_LINE_DESCRIPTION,1,240),
836 substrb(INV_LINE_PK,1,120),
837 NVL(INV_LINE_TYPE_FK,'NA_EDW'),
838 substrb(INV_NUM, 1, 50),
839 INV_PRICE_VAR_AMT_B,
840 INV_PRICE_VAR_AMT_G,
841 INV_PRICE_VAR_AMT_T,
842 NVL(INV_SOURCE_FK,'NA_EDW'),
843 substrb(INV_TYPE,1,25),
844 INV_UNIT_PRICE_B,
845 INV_UNIT_PRICE_G,
846 INV_UNIT_PRICE_T,
847 substrb(ITEM_DESCRIPTION,1,240),
848 NVL(ITEM_FK,'NA_EDW'),
849 ITEM_ID,
850 LAST_UPDATE_DATE,
851 MATCH_LINE_AMT_B,
852 MATCH_LINE_AMT_G,
853 MATCH_LINE_AMT_T,
854 MATCH_LINE_COUNT,
855 substrb(MATCH_STATUS_FLAG,1,1),
856 NVL(ORG_FK,'NA_EDW'),
857 NVL(PAYMENT_TERM_FK,'NA_EDW'),
858 POSTED_AMT_B,
859 POSTED_AMT_G,
860 POSTED_AMT_T,
861 substrb(POSTED_FLAG,1,1),
862 PO_AMT_B,
863 PO_AMT_G,
864 PO_AMT_T,
865 PO_DISTRIBUTION_ID,
866 substrb(PO_NUMBER,1,20),
867 PO_UNIT_PRICE_B,
868 PO_UNIT_PRICE_G,
869 PO_UNIT_PRICE_T,
870 PROJECT_ID,
871 QTY_VAR_AMT_B,
872 QTY_VAR_AMT_G,
873 QTY_VAR_AMT_T,
874 QUANTITY_INVOICED_G,
875 QUANTITY_INVOICED_T,
876 NVL(SIC_CODE_FK,'NA_EDW'),
877 NVL(SOB_FK,'NA_EDW'),
878 NVL(SUPPLIER_FK,'NA_EDW'),
879 SUPPLIER_SITE_ID,
880 TOTAL_VAR_AMT_B,
881 TOTAL_VAR_AMT_G,
882 TOTAL_VAR_AMT_T,
883 substrb(TYPE_1099,1,10),
884 UNMATCH_LINE_AMT_B,
885 UNMATCH_LINE_AMT_G,
886 UNMATCH_LINE_AMT_T,
887 UNMATCH_LINE_COUNT,
888 NVL(UNSPSC_FK,'NA_EDW'),
889 NVL(UOM_G_FK,'NA_EDW'),
890 NVL(UOM_T_FK,'NA_EDW'),
891 USER_ATTRIBUTE1,
892 USER_ATTRIBUTE10,
893 USER_ATTRIBUTE2,
894 USER_ATTRIBUTE3,
895 USER_ATTRIBUTE4,
896 USER_ATTRIBUTE5,
897 USER_ATTRIBUTE6,
898 USER_ATTRIBUTE7,
899 USER_ATTRIBUTE8,
900 USER_ATTRIBUTE9,
901 NVL(USER_FK1,'NA_EDW'),
902 NVL(USER_FK2,'NA_EDW'),
906 USER_MEASURE1,
903 NVL(USER_FK3,'NA_EDW'),
904 NVL(USER_FK4,'NA_EDW'),
905 NVL(USER_FK5,'NA_EDW'),
907 USER_MEASURE2,
908 USER_MEASURE3,
909 USER_MEASURE4,
910 USER_MEASURE5,
911 substrb(VAT_CODE,1,15),
912 NULL, -- OPERATION_CODE
913 'READY',
914 NVL(DISCOUNT_AMT_T, 0), --added the following three columns for New Information Enhancement, nov/12/2002
915 NVL(DISCOUNT_AMT_B, 0),
916 NVL(DISCOUNT_AMT_G, 0),
917 PROJECT_FK, --Added the following four columns for New Information Enhancement, nov/26/2002
918 substrb(EXPENDITURE_TYPE,1,30),
919 substrb(VOUCHER_NUMBER,1,50),
920 DOC_SEQUENCE_VALUE,
921 TASK_ID, -- Addded for bug#2926033
922 RCV_TRANSACTION_ID -- Added for bug#3116554
923 from FII_AP_INV_LINES_FSTG
924 WHERE collection_status = 'LOCAL READY';
925 --ensures that only the records with collection status of local ready will be pushed to remote fstg
926
927 RETURN(sql%rowcount);
928
929 EXCEPTION
930 WHEN OTHERS THEN
931 g_errbuf:=sqlerrm;
932 g_retcode:=sqlcode;
933 rollback;
934 RETURN(-1);
935
936 END;
937
938
939 ---------------------------------------------------
940 -- PROCEDURE IDENTIFY_CHANGE
941 ---------------------------------------------------
942
943 PROCEDURE IDENTIFY_CHANGE(p_count OUT NOCOPY NUMBER)
944 IS
945
946 l_fii_schema VARCHAR2(30);
947 l_status VARCHAR2(30);
948 l_industry VARCHAR2(30);
949 l_stmt VARCHAR2(5000); -- Added for iExpense Enhancement,12-DEC-02
950 l_er_stmt VARCHAR2(100) := NULL; -- Added for iExpense Enhancement,12-DEC-02
951
952 BEGIN
953
954 p_count := 0;
955 --** Added for iExpense Enhancement,12-DEC-02
956 IF (g_collect_er <> 'Y') THEN
957 l_er_stmt := ' AND ai.invoice_type_lookup_code <> ''EXPENSE REPORT'' ';
958 END IF;
959 --**
960
961
962
963
964 --** Modified for iExpense Enhancement,12-DEC-02
965
966 -- --------------------------------------------------------------------------------------------------
967 -- The variable g_acct_or_inv_date is added in the below mentioned select statement.
968 -- The value of the profile option stored in the global variable g_acct_or_inv_date
969 -- will be stored in the column Primary_Key5 . Modified for Currency Conversion Date Enhancement, 4-APR-03
970 -----------------------------------------------------------------------------------------------------
971
972 l_stmt := ' INSERT INTO fii_ap_tmp_line_pk(
973 Primary_Key1,
974 Primary_Key2,
975 Primary_Key4,
976 Primary_Key5)
977 SELECT
978 aid.invoice_id,
979 aid.distribution_line_number,
980 aid.invoice_line_number,
981 :g_acct_or_inv_date
982 FROM ap_invoice_distributions_all aid,
983 ap_invoices_all ai,
984 ap_invoice_lines_all ail
985 WHERE aid.invoice_id = ai.invoice_id
986 AND aid.invoice_line_number=ail.line_number
987 AND ail.invoice_id=ai.invoice_id
988 AND aid.posted_flag=''Y''
989 -- for bug 2601797: AND ai.cancelled_date IS NULL
990 AND (aid.last_update_date between :g_push_date_range1 and :g_push_date_range2
991 OR ai.last_update_date between :g_push_date_range1 and :g_push_date_range2 )'||l_er_stmt||'
992 UNION
993 SELECT
994 aid.invoice_id,
995 aid.distribution_line_number,
996 aid.invoice_line_number,
997 :g_acct_or_inv_date
998 FROM ap_invoice_distributions_all aid,
999 ap_invoices_all ai,
1000 ap_invoice_lines_all ail,
1001 po_distributions_all pd,
1002 po_lines_all pl,
1003 po_headers_all ph,
1004 po_line_locations_all pll
1005 WHERE ( pl.last_update_date between :g_push_date_range1 and :g_push_date_range2
1006 or ph.last_update_date between :g_push_date_range1 and :g_push_date_range2
1007 or pll.last_update_date between :g_push_date_range1 and :g_push_date_range2)
1008 AND pl.po_line_id = pd.po_line_id
1009 AND pd.po_distribution_id = aid.po_distribution_id
1010 AND aid.invoice_id = ai.invoice_id
1011 AND aid.invoice_line_number=ail.line_number
1012 AND ail.invoice_id=ai.invoice_id
1013 AND aid.posted_flag=''Y''
1014 AND ph.po_header_id = pl.po_header_id
1015 AND pll.line_location_id = pd.line_location_id '||l_er_stmt||'
1016 UNION
1017 SELECT aid.invoice_id,
1018 aid.distribution_line_number,
1019 aid.invoice_line_number,
1020 :g_acct_or_inv_date
1021 FROM ap_invoice_distributions_all aid,
1022 ap_invoices_all ai,
1023 ap_invoice_payments_all aip,
1024 ap_payment_hist_dists aphd,
1025 ap_payment_history_all aph
1026 WHERE aid.invoice_id = ai.invoice_id
1027 AND aid.invoice_id = aip.invoice_id
1028 AND aid.posted_flag = ''Y''
1029 AND aip.invoice_payment_id = aphd.invoice_payment_id
1030 AND aphd.PAY_DIST_LOOKUP_CODE = ''DISCOUNT''
1031 AND aphd.invoice_distribution_id = aid.invoice_distribution_id
1032 AND nvl(aph.historical_flag, ''N'') = ''N''
1033 AND APH.check_id = aip.check_id
1037 AND aphd.last_update_date between :g_push_date_range1 and :g_push_date_range2 '||l_er_stmt||'
1034 AND aph.payment_history_id=aphd.payment_history_id
1035 AND aphd.bank_curr_amount is null
1036 AND aphd.cleared_base_amount is null
1038 UNION
1039 SELECT aid.invoice_id,
1040 aid.distribution_line_number,
1041 aid.invoice_line_number,
1042 :g_acct_or_inv_date
1043 FROM ap_invoice_distributions_all aid,
1044 ap_invoices_all ai,
1045 ap_invoice_payments_all aip,
1046 -- ap_payment_history_all aph,
1047 xla_ae_lines xal,
1048 xla_ae_headers xah
1049 WHERE aid.invoice_id = ai.invoice_id
1050 AND aid.invoice_id = aip.invoice_id
1051 AND aip.invoice_payment_id = xal.Upg_Tax_Reference_ID3
1052 AND aid.old_dist_line_number = xal.Upg_Tax_Reference_ID2
1053 AND xal.accounting_class_code = ''DISCOUNT''
1054 -- AND APH.check_id = aip.check_id
1055 --AND nvl(aph.historical_flag, ''N'') = ''Y''
1056 AND xal.last_update_date between :g_push_date_range1 and :g_push_date_range2
1057 AND xal.application_id=200
1058 AND xah.ae_header_id=xal.ae_header_id
1059 AND xah.ledger_id=aid.set_of_books_id '||l_er_stmt;
1060 --**
1061
1062 /* IF (FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, l_fii_schema)) THEN
1063 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_fii_schema,
1064 TABNAME => 'FII_TMP_PK') ;
1065 END IF; */
1066
1067
1068
1069 --** Added for iExpense Enhancement,12-DEC-02
1070 edw_log.debug_line('');
1071 edw_log.debug_line(l_stmt);
1072 execute immediate l_stmt using g_acct_or_inv_date,g_push_date_range1,g_push_date_range2,
1073 g_push_date_range1,g_push_date_range2,
1074 g_acct_or_inv_date,g_push_date_range1,g_push_date_range2,
1075 g_push_date_range1,g_push_date_range2,
1076 g_push_date_range1,g_push_date_range2,
1077 g_acct_or_inv_date,g_push_date_range1,g_push_date_range2,
1078 g_acct_or_inv_date,g_push_date_range1,g_push_date_range2;
1079
1080 p_count := sql%rowcount;
1081
1082
1083 edw_log.debug_line( 'NO OF ROWS CHANGED '||
1084 to_char(p_count));
1085
1086 IF (FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, l_fii_schema)) THEN
1087 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_fii_schema,
1088 TABNAME => 'FII_AP_TMP_LINE_PK') ;
1089 END IF;
1090 --**
1091
1092
1093
1094 EXCEPTION
1095 WHEN OTHERS THEN
1096 g_errbuf:=sqlerrm;
1097 g_retcode:=sqlcode;
1098
1099 rollback;
1100
1101 END;
1102
1103 PROCEDURE UPDATE_DIST_CCID IS
1104 cursor ccid_cursor is
1105 select distinct primary_key1 ccid, primary_key_char1 inv_line_pk
1106 from fii_ap_tmp_line_pk;
1107 BEGIN
1108 TRUNCATE_TABLE('fii_ap_tmp_line_pk');
1109
1110 insert into fii_ap_tmp_line_pk(Primary_key1,
1111 Primary_key_Char1)
1112 with accounting_class AS (SELECT distinct xaca.accounting_class_Code
1113 FROM xla_assignment_defns_B xad,
1114 xla_acct_class_assgns xaca
1115 WHERE XAD.Program_Code = 'PAYABLES EDW EXPENSES'
1116 AND XAD.Enabled_Flag = 'Y'
1117 AND XAD.Program_Code = XACA.Program_Code
1118 AND XAD.Assignment_Code = XACA.Assignment_Code)
1119 select /*+ parallel(fstg) parallel(xah) parallel(xte) parallel(xal) parallel(xdl) */ xal.code_combination_id,
1120 inv_line_pk
1121 from xla_ae_headers xah,
1122 xla_transaction_entities xte,
1123 xla_distribution_links xdl,
1124 xla_ae_lines xal,
1125 fii_ap_inv_lines_fstg fstg,
1126 accounting_class ac
1127 where xte.entity_code='AP_INVOICES'
1128 and xah.entity_id=xte.entity_id
1129 and xah.ae_header_id=xal.ae_header_id
1130 and xal.ae_header_id=xdl.ae_header_id
1131 and xal.ae_line_num=xdl.ae_line_num
1132 and xdl.source_distribution_id_num_1=fstg.user_measure1
1133 and xdl.Source_Distribution_Type IN ('AP_INV_DIST', 'AP_PMT_DIST', 'AP_PREPAY')
1134 and xal.application_id=200
1135 and xah.application_id=200
1136 and xte.application_id=200
1137 and xdl.application_id=200
1138 and xah.accounting_entry_status_code='F'
1139 and xal.accounting_class_code = ac.accounting_class_code
1140 and xah.ledger_id=xte.ledger_id
1141 and fstg.user_measure2=xah.ledger_id
1142 and xah.balance_type_code='A';
1143 -- user_measure2 will be populated with ledger_id in push_to_local procedure
1144 --user_measure1 will be populated with invoice_distribution_id in push_to_local
1145
1149 where fstg.inv_line_pk=l_ccid.inv_line_pk;
1146 FOR l_ccid IN ccid_cursor LOOP
1147 update fii_ap_inv_lines_fstg fstg
1148 set fstg.CCID = l_ccid.ccid
1150 END LOOP;
1151
1152
1153 END;
1154
1155
1156
1157
1158
1159 -- ---------------------------------
1160 -- PUBLIC PROCEDURES
1161 -- ---------------------------------
1162
1163 -----------------------------------------------------------
1164 -- PROCEDURE PUSH
1165 -----------------------------------------------------------
1166 Procedure Push(Errbuf in out NOCOPY Varchar2,
1167 Retcode in out NOCOPY Varchar2,
1168 p_from_date IN Varchar2,
1169 p_to_date IN Varchar2) IS
1170 l_fact_name Varchar2(30) :='FII_AP_INV_LINES_F' ;
1171 l_date1 Date:=Null;
1172 l_date2 Date:=Null;
1173 l_temp_date Date:=Null;
1174 l_row_count Number:=0;
1175 l_duration Number:=0;
1176 l_exception_msg Varchar2(2000):=Null;
1177 l_from_date Date:=Null;
1178 l_to_date Date:=Null;
1179 my_payment_currency Varchar2(2000):=NULL;
1180 my_inv_date Varchar2(2000) := NULL;
1181 my_collection_status Varchar2(2000):=NULL;
1182 -- -------------------------------------------
1183 -- Put any additional developer variables here
1184 -- -------------------------------------------
1185 l_push_local_failure EXCEPTION;
1186 l_push_remote_failure EXCEPTION;
1187 l_set_status_failure EXCEPTION;
1188 l_iden_change_failure EXCEPTION;
1189
1190
1191
1192 rows Number:=0;
1193 rows1 Number:=0;
1194 l_count NUMBER:=0; --bug3818907
1195
1196
1197 l_to_currency VARCHAR2(15); -- Added for Currency Conversion Date Enhancement , 4-APR-03
1198 l_msg VARCHAR2(120):=NULL; -- Added for Currency Conversion Date Enhancement , 18-APR-03
1199 l_set_completion_status BOOLEAN; --bug#3207823
1200
1201 ----------------------------------------------------------------------------------------------
1202 -- This cursor is for getting records where the CONVERSION_DATE (i.e. GL_DATE or INVOICE_DATE )
1203 -- is less than the sysdate i.e. in past. Added for Currency Conversion Date Enhancement , 4-APR-03
1204 ----------------------------------------------------------------------------------------------
1205 --bug#3303683 : BASE_CURRENCY_CODE should be printed in the o/p file
1206 -- since the exchange rate is calculated using
1207 -- BASE_CURRENCY_CODE
1208
1209 cursor c1 is select DISTINCT BASE_CURRENCY_CODE from_currency,
1210 Decode(g_acct_or_inv_date,
1211 1, ACCOUNTING_DATE,
1212 INV_DATE) CONVERSION_DATE,
1213 COLLECTION_STATUS
1214 From FII_AP_INV_LINES_FSTG
1215 where (COLLECTION_STATUS='RATE NOT AVAILABLE'
1216 OR COLLECTION_STATUS = 'INVALID CURRENCY')
1217 AND trunc(Decode(g_acct_or_inv_date,
1218 1, ACCOUNTING_DATE,
1219 INV_DATE)) <= trunc(sysdate);
1220
1221 -----------------------------------------------------------------------------------------------------
1222 -- This cursor is for getting records where the CONVERSION_DATE (i.e. GL_DATE or INVOICE_DATE )
1223 -- is greater than the syssdate i.e. in future. Added for Currency Conversion Date Enhancement , 4-APR-03
1224 -----------------------------------------------------------------------------------------------------
1225 --bug#3303683 : BASE_CURRENCY_CODE should be printed in the o/p file
1226 -- since the exchange rate is calculated using
1227 -- BASE_CURRENCY_CODE
1228
1229 cursor c2 is select DISTINCT BASE_CURRENCY_CODE FROM_CURRENCY,
1230 Decode(g_acct_or_inv_date,
1231 1, ACCOUNTING_DATE,
1232 INV_DATE) CONVERSION_DATE,
1233 COLLECTION_STATUS
1234 From FII_AP_INV_LINES_FSTG
1235 where (COLLECTION_STATUS='RATE NOT AVAILABLE'
1236 OR COLLECTION_STATUS = 'INVALID CURRENCY')
1237 AND trunc(Decode(g_acct_or_inv_date,
1238 1, ACCOUNTING_DATE,
1239 INV_DATE)) > trunc(sysdate);
1240
1241
1242 --Cursor declaration required to generate output file containing rows with above collection status
1243
1244 Begin
1245
1246 execute immediate 'alter session set global_names=false' ; --bug#3207823
1247
1248 Errbuf :=NULL;
1249 Retcode:=0;
1250 l_from_date :=to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS');
1251 l_to_date :=to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
1252 IF (Not EDW_COLLECTION_UTIL.setup(l_fact_name)) THEN
1253 errbuf := fnd_message.get;
1257 EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
1254 RAISE_APPLICATION_ERROR(-20000,'Error in SETUP: ' || errbuf);
1255 END IF;
1256 FII_AP_INV_LINES_F_C.g_push_date_range1 := nvl(l_from_date,
1258 FII_AP_INV_LINES_F_C.g_push_date_range2 := nvl(l_to_date,EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
1259 l_date1 := g_push_date_range1;
1260 l_date2 := g_push_date_range2;
1261 edw_log.put_line( 'The collection range is from '||
1262 to_char(l_date1,'MM/DD/YYYY HH24:MI:SS')||' to '||
1263 to_char(l_date2,'MM/DD/YYYY HH24:MI:SS'));
1264 edw_log.put_line(' ');
1265
1266
1267 --bug#3818907
1268 --Check whether missing rates table has data or not. If not then copy missing rates
1269 --from temp table to the missing rates table. This is required to avoid full refresh
1270 --of the fact after application of this patch.
1271 execute immediate 'select count(*) from FII_AP_LINE_MSNG_RATES' into l_count;
1272
1273 if (l_count=0) then
1274 insert into fii_ap_line_msng_rates(Primary_Key1,
1275 Primary_key2,
1276 Primary_key3,
1277 Primary_key4) /* Inv line Uptake */
1278 select Primary_key1,
1279 Primary_key2,
1280 Primary_Key5,
1281 Primary_Key4
1282 from fii_ap_tmp_line_pk;
1283 commit;
1284 else
1285
1286 TRUNCATE_TABLE('FII_AP_TMP_LINE_PK');--bug#3818907
1287
1288 --bug#3818907
1289 --move the missing rates related info. from the missing rates
1290 --table to the temp table for further processing.
1291 Insert into fii_ap_tmp_line_pk(Primary_Key1,
1292 Primary_Key2,
1293 Primary_Key5,
1294 Primary_Key4) /* Inv Line Uptake */
1295 select Primary_Key1,
1296 Primary_Key2,
1297 Primary_Key3,
1298 Primary_Key4
1299 from fii_ap_line_msng_rates;
1300 end if;
1301
1302
1303 -- ---------------------------------------------------------
1304 -- Fetch profile option value
1305 -- ---------------------------------------------------------
1306 g_collect_er := NVL(FND_PROFILE.value('FII_COLLECT_ER'),'N'); -- Added for iExpense Enhancement,12-DEC-02
1307
1308 ----------------------------------------------------------------------------------------------------------
1309 -- See whether to use accounting date or invoice date . Added for Currency Conversion Date Enhancement 4-APR-03
1310 ----------------------------------------------------------------------------------------------------------
1311 IF NVL(FND_PROFILE.value('FII_ACCT_OR_INV_DATE'),'N') = 'Y' THEN
1312 g_acct_or_inv_date := 1;
1313 ELSE
1314 g_acct_or_inv_date := 0;
1315 END IF;
1316
1317 -- --------------------------------------------------------
1318 -- 1. Clean up any records left from previous process in
1319 -- the local staging table.
1320 -- --------------------------------------------------------
1321 edw_log.put_line(' ');
1322 edw_log.put_line('Cleaning up unprocessed records left in local staging table');
1323 IF (NOT LOCAL_SAME_AS_REMOTE) THEN
1324 TRUNCATE_TABLE('FII_AP_INV_LINES_FSTG');
1325 ELSE
1326 DELETE_STG;
1327 END IF;
1328
1329
1330 -- --------------------------------------------------------
1331 -- 2. Identify Changed AP Invoice Lines record
1332 -- --------------------------------------------------------
1333 edw_log.put_line(' ');
1334 edw_log.put_line('Identifying changed AP Invoice Lines record');
1335 fii_util.start_timer;
1336 IDENTIFY_CHANGE(l_row_count);
1337 fii_util.stop_timer;
1338 fii_util.print_timer('Identified '||l_row_count||' changed records');
1339
1340 -- --------------------------------------------------------
1341 -- 3. Pushing data to local staging table
1342 -- --------------------------------------------------------
1343
1344 edw_log.put_line(' ');
1345 edw_log.put_line('Pushing data');
1346 fii_util.start_timer;
1347 g_row_count := PUSH_TO_LOCAL;
1348 fii_util.stop_timer;
1349 fii_util.print_timer('Process Time');
1350
1351 IF (g_row_count = -1) THEN
1352 RAISE L_push_local_failure;
1353 END IF;
1354
1355 edw_log.put_line('Inserted '||nvl(g_row_count,0)||
1356 ' rows into the local staging table');
1357 edw_log.put_line(' ');
1358
1359 -- --------------------------------------------------------
1360 -- 3.5 Populating the discount_amt_t, discount_amt_b,
1361 -- discount_amt_g columns in the local staging table
1362 -- add this procedure call for merill lynch, nov/12/2002
1363 -- --------------------------------------------------------
1364
1365 edw_log.put_line(' ');
1366 edw_log.put_line('Populate discount_amt columns');
1367 fii_util.start_timer;
1368
1369 UPDATE_DIST_CCID; -- added for SLA-AP Uptake
1370
1371 UPDATE_DISCOUNT_AMT;
1372 fii_util.stop_timer;
1373
1374
1375 -- --------------------------------------------------------
1376 -- 4. Delete all temp table records
1377 -- --------------------------------------------------------
1378 TRUNCATE_TABLE('fii_ap_tmp_line_pk');
1379
1380 -- ------------------------------------------------------------------------------------------------
1381 -- 4A. Insert missing rates from local fstg into tmp_pk table printing data to file
1382 -- ------------------------------------------------------------------------------------------------
1383
1384 INSERT_MISSING_RATES_IN_TMP;
1385
1386 ---------------------------------------------------------------------
1387 -- Read The Warehouse Currency
1388 ----------------------------------------------------------------------
1389 select /*+ FULL(SP) CACHE(SP) */
1390 warehouse_currency_code into l_to_currency
1391 from edw_local_system_parameters SP;
1392
1393 if (g_missing_rates >0) then
1394 --------------------------------------------------------------------
1395 -- Print Records where conversion date is in past
1396 -- Added for Currency Conversion Date Enhancement
1397 ---------------------------------------------------------------------
1398 /* FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ***Information for Missing Currency Conversion Rates*** ');
1399 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1400 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Following Section displays records where Conversion Dates are in Past.');
1401 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'To fix the issue , please enter rates for these Conversion Dates and re-collect the fact.');
1402 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1403 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'FROM CURRENCY TO CURRENCY CONVERSION DATE COLLECTION STATUS');
1404 */
1405
1406 FND_MESSAGE.SET_NAME('FII','FII_MISS_CONV_RATES');
1407 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ***'||fnd_message.get||'*** ');
1408 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1409 FND_MESSAGE.SET_NAME('FII','FII_PAST_CONV_RATES');
1410 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get);
1411 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1412 FND_MESSAGE.SET_NAME('FII','FII_FROM_CURRENCY');
1413 l_msg := l_msg||fnd_message.get||' ';
1414 FND_MESSAGE.SET_NAME('FII','FII_TO_CURRENCY');
1415 l_msg := l_msg||fnd_message.get||' ';
1416 FND_MESSAGE.SET_NAME('FII','FII_MISS_CONV_DATES');
1417 l_msg := l_msg||fnd_message.get||' ';
1418 FND_MESSAGE.SET_NAME('FII','FII_COLLECTION_STATUS');
1419 l_msg := l_msg||fnd_message.get;
1420 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_msg);
1421 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '------------- ----------- --------------- -----------------');
1422
1423 FOR c in c1 loop
1427
1424 my_payment_currency := c.FROM_CURRENCY;
1425 my_inv_date := c.CONVERSION_DATE;
1426 my_collection_status := c.COLLECTION_STATUS;
1428 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, my_payment_currency||
1429 ' '||l_to_currency||' '||my_inv_date||' '||my_collection_status);
1430
1431 end loop;
1432
1433 ------------------------------------------------------------------------------
1434 -- Print records where conversion date is in future
1435 -- Added for Currency Conversion Date Enhancement
1436 -------------------------------------------------------------------------------
1437 /* FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1438 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Following Section displays records where Conversion Dates are in Future.');
1439 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1440 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'FROM CURRENCY TO CURRENCY CONVERSION DATE COLLECTION STATUS');
1441 */
1442 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1443 FND_MESSAGE.SET_NAME('FII','FII_FUTURE_CONV_RATES');
1444 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get);
1445 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1446 l_msg := NULL;
1447 FND_MESSAGE.SET_NAME('FII','FII_FROM_CURRENCY');
1448 l_msg := l_msg||fnd_message.get||' ';
1449 FND_MESSAGE.SET_NAME('FII','FII_TO_CURRENCY');
1450 l_msg := l_msg||fnd_message.get||' ';
1451 FND_MESSAGE.SET_NAME('FII','FII_MISS_CONV_DATES');
1452 l_msg := l_msg||fnd_message.get||' ';
1453 FND_MESSAGE.SET_NAME('FII','FII_COLLECTION_STATUS');
1454 l_msg := l_msg||fnd_message.get;
1455 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_msg);
1456 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '------------- ----------- --------------- -----------------');
1457
1458 FOR d in c2 loop
1459 my_payment_currency := d.FROM_CURRENCY;
1460 my_inv_date := d.CONVERSION_DATE;
1461 my_collection_status := d.COLLECTION_STATUS;
1462
1463 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, my_payment_currency||
1464 ' '||l_to_currency||' '||my_inv_date||' '||my_collection_status);
1465
1466 end loop;
1467 end if;
1468
1469 -- ------------------------------------------------------------------------------------------------------------
1470 -- 4B. Delete records with missing rates from local staging table
1471 -- ------------------------------------------------------------------------------------------------------------
1472
1473 -- DELETE_STG;
1474
1475 -- --------------------------------------------------------
1476 -- 5. Pushing data to remote staging table
1477 -- --------------------------------------------------------
1478 IF (NOT LOCAL_SAME_AS_REMOTE) THEN
1479 -- -----------------------------------------------
1480 -- The target warehouse is not the same database
1481 -- as the source OLTP, which is the typical case.
1482 -- We move data from local to remote staging table
1483 -- and clean up local staging
1484 -- -----------------------------------------------
1485
1486 edw_log.put_line(' ');
1487 edw_log.put_line('Moving data from local staging table to remote staging table');
1488 fii_util.start_timer;
1489 g_row_count := PUSH_REMOTE;
1490 fii_util.stop_timer;
1491 fii_util.print_timer('Duration');
1492
1493 IF (g_row_count = -1) THEN RAISE l_push_remote_failure; END IF;
1494
1495 edw_log.put_line(' ');
1496 edw_log.put_line('Cleaning local staging table');
1497
1498 fii_util.start_timer;
1499 TRUNCATE_table('FII_AP_INV_LINES_FSTG');
1500 fii_util.stop_timer;
1501 fii_util.print_timer('Duration');
1502
1503 ELSE
1504 -- -----------------------------------------------
1505 -- The target warehouse is the same database
1506 -- as the source OLTP. We set the status of all our
1507 -- records status 'LOCAL READY' to 'READY'
1508 -- -----------------------------------------------
1509
1510 edw_log.put_line(' ');
1511 edw_log.put_line('Marking records in staging table with READY status');
1512
1513 fii_util.start_timer;
1514 g_row_count := SET_STATUS_READY;
1515 fii_util.stop_timer;
1516 fii_util.print_timer('Duration');
1517 COMMIT;
1518 DELETE_STG;
1519 IF (g_row_count = -1) THEN RAISE l_set_status_failure; END IF;
1520 END IF;
1521
1522 --bug#3818907
1523 --Clean up the old records from missing rates table and store the
1524 --latest records with missing rates from the current collection
1525 -- to the missing rates table from the temp table.
1526
1527 delete from fii_ap_line_msng_rates;
1528
1529 insert into fii_ap_line_msng_rates(Primary_Key1,
1530 Primary_Key2,
1531 Primary_Key3,
1532 Primary_Key4) /* Inv Lines Uptake */
1533 select Primary_Key1,
1534 Primary_Key2,
1535 Primary_Key5,
1536 Primary_Key4
1537 from fii_ap_tmp_line_pk;
1538
1539 -- -----------------------------------------------
1540 -- Successful. Commit and call
1541 -- wrapup to commit and insert messages into logs
1542 -- -----------------------------------------------
1543 edw_log.put_line(' ');
1544 edw_log.put_line('Inserted '||nvl(g_row_count,0)||
1545 ' rows into the staging table');
1546 edw_log.put_line(' ');
1547 -- COMMIT;
1548 edw_log.put_line(' ');
1549 edw_log.put_line('Inserted '||nvl(g_row_count,0)||
1550 ' rows into the staging table');
1551 edw_log.put_line(' ');
1552 Retcode := g_retcode;
1553
1554
1555
1556 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count,null,g_push_date_range1, g_push_date_range2);
1557 if (g_missing_rates >0) then
1558 edw_log.put_line ('Records with missing rates identified in source and not loaded to warehouse');
1559 end if;
1560 commit;
1561 --bug#3818907
1562 --Program is on the verge of completing successfully,so clean up
1563 -- the temp table
1564 begin
1565 TRUNCATE_TABLE('FII_AP_TMP_LINE_PK');
1566 exception
1567 when others then
1568 null;
1569 end;
1570
1571
1572 Exception
1573 WHEN L_IDEN_CHANGE_FAILURE THEN
1574 Errbuf:=g_errbuf;
1575 Retcode:=g_retcode;
1576 l_exception_msg := Retcode || ':' || Errbuf;
1577 edw_log.put_line('Identifying changed records have Failed');
1578 rollback;
1579 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,g_push_date_range1, g_push_date_range2);
1580 /* Set the completion status to error. bug#3207823 */
1581 l_set_completion_status:=FND_CONCURRENT.Set_Completion_Status(status=>'ERROR',message=>l_exception_msg);
1582 --raise;
1583 WHEN L_PUSH_LOCAL_FAILURE THEN
1584 Errbuf:=g_errbuf;
1585 Retcode:=g_retcode;
1586 l_exception_msg := Retcode || ':' || Errbuf;
1587 edw_log.put_line('Inserting into local staging have failed');
1588 rollback;
1589 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,g_push_date_range1, g_push_date_range2);
1590 /* Set the completion status to error. bug#3207823 */
1591 l_set_completion_status:=FND_CONCURRENT.Set_Completion_Status(status=>'ERROR',message=>l_exception_msg);
1592 --raise;
1593 WHEN L_PUSH_REMOTE_FAILURE THEN
1594 Errbuf:=g_errbuf;
1595 Retcode:=g_retcode;
1596 l_exception_msg := Retcode || ':' || Errbuf;
1597 edw_log.put_line('Data migration from local to remote staging have failed');
1598 rollback;
1599 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,g_push_date_range1, g_push_date_range2);
1600 /* Set the completion status to error. bug#3207823 */
1601 l_set_completion_status:=FND_CONCURRENT.Set_Completion_Status(status=>'ERROR',message=>l_exception_msg);
1602 --raise;
1603 WHEN L_SET_STATUS_FAILURE THEN
1604 Errbuf:=g_errbuf;
1605 Retcode:=g_retcode;
1606 l_exception_msg := Retcode || ':' || Errbuf;
1607 edw_log.put_line('Setting status to READY have failed');
1608 rollback;
1609 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,g_push_date_range1, g_push_date_range2);
1610 /* Set the completion status to error. bug#3207823 */
1611 l_set_completion_status:=FND_CONCURRENT.Set_Completion_Status(status=>'ERROR',message=>l_exception_msg);
1612 --raise;
1613 WHEN OTHERS THEN
1614 Errbuf:=sqlerrm;
1615 Retcode:=sqlcode;
1616 l_exception_msg := Retcode || ':' || Errbuf;
1617 rollback;
1618 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,g_push_date_range1, g_push_date_range2);
1619 /* Set the completion status to error. bug#3207823 */
1620 l_set_completion_status:=FND_CONCURRENT.Set_Completion_Status(status=>'ERROR',message=>l_exception_msg);
1621 --raise;
1622
1623 End;
1624 End FII_AP_INV_LINES_F_C;