[Home] [Help]
PACKAGE BODY: APPS.FII_AP_INV_DISTRIBUTIONS_B_C
Source
1 PACKAGE BODY FII_AP_INV_DISTRIBUTIONS_B_C AS
2 /* $Header: FIIAP20B.pls 120.10 2006/08/08 17:36:59 vkazhipu noship $ */
3
4 g_fii_schema VARCHAR2(30);
5 -- g_tablespace VARCHAR2(50) := NULL;
6 g_expense_ccid_count NUMBER := 0;
7 g_errbuf VARCHAR2(2000) := NULL;
8 g_retcode VARCHAR2(200) := NULL;
9 g_exception_msg VARCHAR2(4000) := NULL;
10 g_today DATE;
11 g_start_date DATE;
12 g_end_date DATE;
13 g_mode VARCHAR2(20) := NULL;
14 g_prim_currency VARCHAR2(15) := NULL;
15 g_sec_currency VARCHAR2(15) := NULL;
16 g_warehouse_rate_type VARCHAR2(30) := NULL;
17 g_state VARCHAR2(200);
18 g_phase VARCHAR2(200);
19 g_ap_application_id NUMBER;
20 g_gl_application_id NUMBER;
21 g_has_lud BOOLEAN := FALSE;
22 MAX_LOOP CONSTANT NUMBER := 180;
23 interval_size CONSTANT NUMBER := 1000;
24 G_TABLE_NOT_EXIST EXCEPTION;
25 PRAGMA EXCEPTION_INIT(G_TABLE_NOT_EXIST, -942);
26 G_CCID_FAILED EXCEPTION;
27 G_PROCEDURE_FAILURE EXCEPTION;
28 G_NO_CHILD_PROCESS EXCEPTION;
29 G_LOGIN_INFO_NOT_AVABLE EXCEPTION;
30 G_IMP_NOT_SET EXCEPTION;
31 G_MISSING_RATES EXCEPTION;
32 G_NEED_SECONDARY_INFO EXCEPTION;
33 G_RUN_INIT EXCEPTION;
34
35 G_LUD_TO_DATE DATE;
36 g_prim_rate_type VARCHAR2(30);
37 g_sec_rate_type VARCHAR2(30);
38 g_prim_rate_type_name VARCHAR2(30);
39 g_sec_rate_type_name VARCHAR2(30);
40 g_lud_from_date DATE;
41 g_fix_rates varchar(1);
42 g_section VARCHAR2(20) := NULL;
43 g_num NUMBER;
44 g_primary_mau NUMBER;
45 g_secondary_mau NUMBER;
46 g_worker_num NUMBER;
47 g_fii_user_id NUMBER(15);
48 g_fii_login_id NUMBER(15);
49 g_truncate_staging VARCHAR2(1) := 'N';
50 g_truncate_id VARCHAR2(1) := 'N';
51 g_truncate_rates VARCHAR2(1) := 'N';
52 g_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
53 g_exp_imp_prof_flag VARCHAR2(1) := NVL(FND_PROFILE.value('FII_AP_DBI_EXP_IMP'), 'N');
54 g_oper_imp_prof_flag VARCHAR2(1) := NVL(FND_PROFILE.value('FII_AP_DBI_IMP'), 'N');
55 ONE_SECOND CONSTANT NUMBER := 0.000011574; -- 1 second
56
57 g_program_type VARCHAR2(1);
58
59 g_usage_code CONSTANT VARCHAR2(10) := 'DBI'; --CHAR will fail join
60 -- g_table_name VARCHAR2(50) := 'FII_AP_INV_B';
61
62 g_timestamp1 DATE;
63 g_timestamp2 DATE;
64 g_timestamp3 DATE;
65 g_timestamp4 DATE;
66
67 g_act_part1 NUMBER;
68 g_act_part2 NUMBER;
69 g_old_act_part1 NUMBER;
70 g_old_act_part2 NUMBER;
71
72 g_ap_schema VARCHAR2(30) := 'AP';
73 ---------------------------------------------------
74 -- PROCEDURE TRUNCATE_TABLE
75 ---------------------------------------------------
76
77 PROCEDURE Truncate_table (p_table_name VARCHAR2) IS
78 l_stmt VARCHAR2(100);
79 BEGIN
80 l_stmt := 'TRUNCATE table '||g_fii_schema||'.'||p_table_name;
81 if g_debug_flag = 'Y' then
82 FII_UTIL.put_line('g_fii_schema '||g_fii_schema);
83 FII_UTIL.put_line('');
84 FII_UTIL.put_line(l_stmt);
85 end if;
86 EXECUTE IMMEDIATE l_stmt;
87
88 EXCEPTION
89 WHEN G_TABLE_NOT_EXIST THEN
90 null; -- Oracle 942, table does not exist, no actions
91 WHEN OTHERS THEN
92 g_errbuf := 'Error in Procedure: TRUNCATE_TABLE Message: '||sqlerrm;
93 RAISE;
94 END Truncate_Table;
95
96 ---------------------------------------------------
97 -- PROCEDURE DROP_TABLE
98 ---------------------------------------------------
99 PROCEDURE drop_table (p_table_name in varchar2) is
100 l_stmt varchar2(400);
101 BEGIN
102 l_stmt:='DROP table '||g_fii_schema||'.'||p_table_name;
103 if g_debug_flag = 'Y' then
104 FII_UTIL.put_line('');
105 FII_UTIL.put_line(l_stmt);
106 end if;
107 EXECUTE IMMEDIATE l_stmt;
108
109 EXCEPTION
110 WHEN G_TABLE_NOT_EXIST THEN
111 null; -- Oracle 942, table does not exist, no actions
112 WHEN OTHERS THEN
113 g_errbuf := 'Error in Procedure: DROP_TABLE Message: '||sqlerrm;
114 RAISE;
115 END Drop_Table;
116
117 -----------------------------------------------------------------------
118 -- PROCEDURE CLEAN_UP
119 -----------------------------------------------------------------------
120 PROCEDURE Clean_Up IS
121
122
123 BEGIN
124 g_state := 'Inside the procedure CLEAN_UP';
125
126 if g_debug_flag = 'Y' then
127 FII_UTIL.put_line('Calling procedure CLEAN_UP');
128 FII_UTIL.put_line('');
129
130 FII_UTIL.put_line('Truncate table FII_AP_SUM_WORK_JOBS');
131 end if;
132 truncate_table('FII_AP_SUM_WORK_JOBS');
133
134 IF (g_truncate_id = 'Y') THEN
135 if g_debug_flag = 'Y' then
136 FII_UTIL.put_line('Truncate table FII_AP_INV_ID');
137 end if;
138 truncate_table('FII_AP_INV_ID');
139 END IF;
140
141 IF (g_truncate_staging = 'Y') THEN
142 if g_debug_flag = 'Y' then
143 FII_UTIL.put_line('Truncate table FII_AP_INV_DIST_T');
144 end if;
145 truncate_table('FII_AP_INV_DIST_T');
146 END IF;
147
148 -- haritha
149 IF (g_truncate_rates = 'Y') THEN
150 if g_debug_flag = 'Y' then
151 FII_UTIL.put_line('Truncate table FII_AP_INV_RATES_TEMP');
152 end if;
153 truncate_table('FII_AP_INV_RATES_TEMP');
154 END IF;
155
156
157
158 EXCEPTION
159 WHEN OTHERS Then
160 g_retcode:=-1;
161 g_errbuf := '
162 ---------------------------------
163 Error in Procedure: Clean_Up
164 Message: ' || sqlerrm;
165 RAISE g_procedure_failure;
166
167 END Clean_up;
168
169
170 ---------------------------------------------------
171 -- FUNCTION CHECK_IF_SET_UP_CHANGE
172 ---------------------------------------------------
173 FUNCTION CHECK_IF_SET_UP_CHANGE RETURN VARCHAR2 IS
174 l_result VARCHAR2(10);
175 l_count1 number :=0 ;
176 l_count2 number :=0 ;
177
178 BEGIN
179 g_state := 'Check if Source Ledger Group set up has changed';
180 if g_debug_flag = 'Y' then
181 FII_UTIL.put_line('');
182 FII_UTIL.put_line( 'Check if Source Ledger Group set up has changed');
183 end if;
184
185 SELECT DECODE(item_value, 'Y', 'TRUE', 'FALSE')
186 INTO l_result
187 FROM fii_change_log
188 WHERE log_item = 'AP_RESUMMARIZE';
189
190 IF l_result = 'TRUE' THEN
191
192 BEGIN
193 SELECT 1
194 INTO l_count1
195 FROM fii_ap_inv_dist_f
196 WHERE ROWNUM = 1;
197 EXCEPTION
198 WHEN NO_DATA_FOUND THEN l_count1 := 0;
199 END;
200
201 BEGIN
202 SELECT 1
203 INTO l_count2
204 FROM fii_ap_inv_dist_t
205 WHERE ROWNUM = 1;
206 EXCEPTION
207 WHEN NO_DATA_FOUND THEN l_count2 := 0;
208 END;
209
210 IF (l_count1 = 0 AND l_count2 = 0) then
211 UPDATE fii_change_log
212 SET item_value = 'N',
213 last_update_date = SYSDATE,
214 last_update_login = g_fii_login_id,
215 last_updated_by = g_fii_user_id
216 WHERE log_item = 'AP_RESUMMARIZE';
217
218 COMMIT;
219
220 l_result := 'FALSE';
221 END IF;
222
223 END IF;
224
225 RETURN l_result;
226
227 EXCEPTION
228 WHEN NO_DATA_FOUND THEN
229 RETURN 'FALSE';
230 WHEN OTHERS THEN
231 g_retcode := -1;
232 g_errbuf := '
233 -----------------------------
234 Error occured in Function: CHECK_IF_SET_UP_CHANGE
235 Message: ' || sqlerrm;
236 RAISE;
237 END CHECK_IF_SET_UP_CHANGE;
238
239
240 --------------------------------------------------
241 -- PROCEDURE POPULATE_INV_ID_TEMP
242 ---------------------------------------------------
243 FUNCTION POPULATE_INV_ID_TEMP RETURN NUMBER IS
244 l_count NUMBER;
245 BEGIN
246
247 --------------------------------------------------------------------------------
248 -- For Incremental mode we will get the list of invoices from the log table and
249 -- insert into the temp table.
250 --------------------------------------------------------------------------------
251 g_state := 'Inserting records into FII_AP_INV_ID table from AP log table';
252 IF g_debug_flag = 'Y' then
253 FII_UTIL.put_line('');
254 FII_UTIL.put_line(g_state);
255 fii_util.start_timer;
256 fii_util.put_line('');
257 END IF;
258
259 INSERT INTO FII_AP_INV_ID (INVOICE_ID, SEQUENCE_ID)
260 SELECT
261 invoice_id INVOICE_ID,
262 rownum SEQUENCE_ID
263 FROM
264 (SELECT distinct Invoice_ID
265 FROM (SELECT Key_Value1 INVOICE_ID
266 FROM ap_dbi_log log
267 WHERE Table_Name IN ('AP_INVOICES', 'AP_INVOICE_DISTRIBUTIONS')
268 AND Creation_Date >= g_timestamp2
269 AND Creation_Date < g_timestamp1
270 UNION
271 SELECT Key_Value1_ID Invoice_ID
272 FROM FII_AP_DBI_Log_Exp_T
273 WHERE Table_Name IN ('AP_INVOICES', 'AP_INVOICE_DISTRIBUTIONS')));
274
275 l_count := SQL%ROWCOUNT;
276
277 IF g_debug_flag = 'Y' then
278 FII_UTIL.stop_timer;
279 FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT||' records in FII_AP_INV_ID');
280 FII_UTIL.print_timer('Duration');
281 END IF;
282
283 RETURN l_count;
284 EXCEPTION
285 WHEN OTHERS THEN
286 g_errbuf:=sqlerrm;
287 g_retcode:= -1;
288 g_exception_msg := g_retcode || ':' || g_errbuf;
289 FII_UTIL.put_line('Error occured while ' || g_state);
290 FII_UTIL.put_line(g_exception_msg);
291 RAISE;
292 END POPULATE_INV_ID_TEMP;
293
294 -----------------------------------------------------------
295 -- PROCEDURE POPULATE_AP_SUM_STG
296 -----------------------------------------------------------
297 PROCEDURE POPULATE_AP_SUM_STG (p_start_range IN number,
298 p_end_range IN number) is
299 l_stmt VARCHAR2(1000);
300 BEGIN
301 g_state := 'Populating FII_AP_INV_DIST_T from FII_AP_INV_ID table';
302 if g_debug_flag = 'Y' then
303 FII_UTIL.put_line(g_state);
304 FII_UTIL.start_timer;
305 end if;
306
307 INSERT INTO FII_AP_INV_DIST_T
308 (ACCOUNT_DATE,
309 INV_CURRENCY_CODE,
310 INVOICE_ID,
311 INVOICE_DISTRIBUTION_ID,
312 AMOUNT_B,
313 PO_MATCHED_FLAG,
314 SOURCE,
315 INV_DIST_CREATED_BY,
316 SUPPLIER_SITE_ID,
317 INV_DIST_CREATION_DATE,
318 SUPPLIER_ID,
319 INVOICE_TYPE,
320 COMPANY_ID,
321 COST_CENTER_ID,
322 PO_DISTRIBUTION_ID,
323 PRIM_CONVERSION_RATE,
324 SEC_CONVERSION_RATE,
325 POSTED_FLAG,
326 APPROVED_FLAG,
327 ORG_ID,
328 LINE_TYPE_LOOKUP_CODE,
329 INVENTORY_ITEM_ID,
330 PURCHASING_CATEGORY_ID,
331 ITEM_DESCRIPTION)
332 SELECT /*+ ORDERED USE_NL(temp ai aid gsob glcc)*/
333 trunc(aid.accounting_date) ACCOUNT_DATE,
334 GSOB.Currency_Code INV_CURRENCY_CODE,
335 aid.invoice_id INVOICE_ID,
336 aid.invoice_distribution_id INVOICE_DISTRIBUTION_ID,
337 NVL(aid.base_amount, aid.amount) AMOUNT_B,
338 decode(aid.po_distribution_id, Null, 'N', 'Y') PO_MATCHED_FLAG,
339 ai.source SOURCE,
340 nvl(fnd.EMPLOYEE_ID,-1) INV_DIST_CREATED_BY,
341 NVL(ai.vendor_site_id, -1) SUPPLIER_SITE_ID,
342 trunc(aid.creation_date) INV_DIST_CREATION_DATE,
343 ai.vendor_id SUPPLIER_ID,
344 ai.invoice_type_lookup_code INVOICE_TYPE,
345 glcc.company_id,
346 glcc.cost_center_id,
347 aid.po_distribution_id,
348 decode(gsob.currency_code, g_prim_currency, 1,
349 fii_currency.get_global_rate_primary( gsob.currency_code,
350 trunc(least(aid.accounting_date,sysdate)))) PRIM_CONVERSION_RATE,
351 decode(gsob.currency_code, g_sec_currency, 1,
352 fii_currency.get_global_rate_secondary( gsob.currency_code,
353 trunc(least(aid.accounting_date,sysdate)))) SEC_CONVERSION_RATE,
354 NVL(aid.posted_flag, 'N') POSTED_FLAG,
355 NVL(aid.match_status_flag, 'N') APPROVED_FLAG,
356 NVL(ai.org_id, -1) ORG_ID,
357 aid.LINE_TYPE_LOOKUP_CODE,
358 AIL.Inventory_Item_ID,
359 AIL.Purchasing_Category_ID,
360 AIL.Item_Description
361 FROM fii_ap_inv_id temp,
362 ap_invoices_all ai,
363 ap_invoice_lines_all ail,
364 ap_invoice_distributions_all aid,
365 gl_ledgers_public_v gsob,
366 fii_gl_ccid_dimensions glcc,
367 fnd_user fnd,
368 fii_slg_assignments slga,
369 fii_source_ledger_groups fslg
370 WHERE ai.invoice_id = temp.invoice_id
371 AND ai.invoice_id = aid.invoice_id
372 and ai.invoice_id = ail.invoice_id
373 AND AID.Invoice_ID = AIL.Invoice_ID
374 AND AID.Invoice_Line_Number = AIL.Line_Number
375 AND aid.set_of_books_id = gsob.ledger_id
376 AND aid.dist_code_combination_id=glcc.code_combination_id
377 AND glcc.chart_of_accounts_id = slga.chart_of_accounts_id
378 AND ( glcc.company_id = slga.bal_seg_value_id
379 OR slga.bal_seg_value_id = -1 )
380 AND aid.set_of_books_id = slga.ledger_id
381 AND slga.source_ledger_group_id = fslg.source_ledger_group_id
382 AND fslg.usage_code = g_usage_code
383 AND fnd.user_id = nvl(aid.created_by, ai.cancelled_by)
384 AND temp.sequence_id >= p_start_range
385 AND temp.sequence_id <= p_end_range
386 AND aid.accounting_date >= g_start_date;
387
388 if g_debug_flag = 'Y' then
389 FII_UTIL.put_line('Inserted '||sql%rowcount||' rows INTO FII_AP_INV_DIST_T table');
390 FII_UTIL.stop_timer;
391 FII_UTIL.print_timer('Duration');
392 end if;
393
394 COMMIT;
395
396 ------------------------------------------------------------------
397 -- Account_date and creation_date are truncated so that multiple
398 -- records with same date are not inserted into the staging table,
399 -- which causes problem with merge statement
400 ------------------------------------------------------------------
401
402 EXCEPTION
403 WHEN OTHERS THEN
404 g_errbuf:=sqlerrm;
405 g_retcode:= -1;
406 g_exception_msg := g_retcode || ':' || g_errbuf;
407 FII_UTIL.put_line('Error occured while ' || g_state);
408 FII_UTIL.put_line(g_exception_msg);
409 RAISE;
410
411 END POPULATE_AP_SUM_STG;
412
413 -----------------------------------------------------------
414 -- PROCEDURE REGISTER_JOBS
415 -----------------------------------------------------------
416 PROCEDURE REGISTER_JOBS IS
417 l_max_number NUMBER;
418 l_start_number NUMBER;
419 l_end_number NUMBER;
420 l_count NUMBER := 0;
421 BEGIN
422
423 g_phase := 'Register jobs for workers';
424
425 if g_debug_flag = 'Y' then
426 FII_UTIL.put_line('Register jobs for workers');
427 end if;
428
429 SELECT max(sequence_id), min(sequence_id)
430 INTO l_max_number, l_start_number
431 FROM FII_AP_INV_ID;
432
433 -----------------------------------------
434 --SELECT min and max sequence IDs from your ID temp table
435 -----------------------------------------
436
437 WHILE (l_start_number < (l_max_number + 1))
438 LOOP
439 l_end_number:= l_start_number + INTERVAL_SIZE;
440
441 INSERT INTO FII_AP_SUM_WORK_JOBS (
442 start_range,
443 end_range,
444 worker_number,
445 status)
446 VALUES (
447 l_start_number,
448 least(l_end_number, l_max_number),
449 0,
450 'UNASSIGNED');
451
452 l_count := l_count + 1;
453 l_start_number := least(l_end_number, l_max_number) + 1;
454 END LOOP;
455
456 if g_debug_flag = 'Y' then
457 FII_UTIL.put_line('Inserted ' || l_count || ' jobs into FII_AP_SUM_WORK_JOBS table');
458 end if;
459
460 EXCEPTION
461 WHEN OTHERS THEN
462 g_retcode := -2;
463 g_errbuf := '
464 ---------------------------------
465 Error in Procedure: REGISTER_JOBS
466 Phase: '||g_phase||'
467 Message: '||sqlerrm;
468 RAISE g_procedure_failure;
469
470 END REGISTER_JOBS;
471
472 ---------------------------------------------------
473 -- FUNCTION LAUNCH_WORKER
474 ---------------------------------------------------
475 -- p_worker_no is the worker number of this particular worker
476
477 FUNCTION LAUNCH_WORKER(p_worker_no NUMBER) RETURN NUMBER IS
478 l_request_id NUMBER;
479 BEGIN
480
481 g_state := 'Inside Launch Worker procedure for worker ' || p_worker_no;
482 if g_debug_flag = 'Y' then
483 FII_UTIL.put_line(g_state);
484 end if;
485
486
487 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
488 'FII',
489 'FII_AP_INV_DIST_B_SUBWORKER',
490 NULL,
491 NULL,
492 FALSE,
493 p_worker_no);
494
495 -- This is the concurrent executable of the subworker.
496
497 IF (l_request_id = 0) THEN
498 rollback;
499 g_retcode := -2;
500 g_errbuf := '
501 ---------------------------------
502 Error in Procedure: LAUNCH_WORKER
503 Message: '||fnd_message.get;
504 RAISE G_NO_CHILD_PROCESS;
505
506 END IF;
507
508 RETURN l_request_id;
509
510 EXCEPTION
511 WHEN G_NO_CHILD_PROCESS THEN
512 g_retcode := -1;
513 FII_UTIL.put_line('No child process launched');
514 raise;
515 WHEN OTHERS THEN
516 rollback;
517 g_retcode := -2;
518 g_errbuf := '
519 ---------------------------------
520 Error in Procedure: LAUNCH_WORKER
521 Message: '||sqlerrm;
522 RAISE g_procedure_failure;
523
524 END LAUNCH_WORKER;
525
526 -----------------------------------------------------------
527 -- FUNCTION VERIFY_MISSING_RATES
528 -----------------------------------------------------------
529 FUNCTION VERIFY_MISSING_RATES (p_program_type IN VARCHAR2) return number IS
530 l_stmt VARCHAR2(1000);
531 l_miss_rates_prim number :=0;
532 l_miss_rates_sec number :=0;
533 l_payment_currency Varchar2(2000):=NULL;
534 l_actg_date Varchar2(2000) := NULL;
535
536 --------------------------------------------------------
537 -- Cursor declaration required to generate output file
538 -- containing rows with MISSING CONVERSION RATES
539 --------------------------------------------------------
540 CURSOR prim_MissingRate IS
541 SELECT DISTINCT INV_CURRENCY_CODE from_currency, decode(prim_conversion_rate,-3,
542 to_date('01/01/1999','MM/DD/RRRR'), LEAST(ACCOUNT_DATE, sysdate)) actg_dt
543 FROM FII_AP_INV_DIST_T stg
544 WHERE stg.prim_conversion_rate < 0 ;
545
546 CURSOR sec_MissingRate IS
547 SELECT DISTINCT INV_CURRENCY_CODE from_currency, decode(sec_conversion_rate,-3,
548 to_date('01/01/1999','MM/DD/RRRR'),LEAST(ACCOUNT_DATE, sysdate)) actg_dt
549 FROM FII_AP_INV_DIST_T stg
550 WHERE stg.sec_conversion_rate < 0 ;
551
552 CURSOR prim_MissingRate_L IS
553 SELECT DISTINCT FUNCTIONAL_CURRENCY from_currency, decode(prim_conversion_rate,-3,
554 to_date('01/01/1999','MM/DD/RRRR'), LEAST(TRX_DATE, sysdate)) actg_dt
555 FROM FII_AP_INV_RATES_TEMP rates
556 WHERE rates.prim_conversion_rate < 0 ;
557
558 CURSOR sec_MissingRate_L IS
559 SELECT DISTINCT FUNCTIONAL_CURRENCY from_currency, decode(sec_conversion_rate,-3,
560 to_date('01/01/1999','MM/DD/RRRR'), LEAST(TRX_DATE, sysdate)) actg_dt
561 FROM FII_AP_INV_RATES_TEMP rates
562 WHERE rates.sec_conversion_rate < 0 ;
563
564 BEGIN
565 g_state := 'Checking to see which additional rates need to be defined, if any';
566
567 IF p_program_type = 'L' THEN
568
569 BEGIN
570 SELECT 1 INTO l_miss_rates_prim FROM FII_AP_INV_RATES_TEMP rates WHERE rates.prim_conversion_rate < 0;
571 EXCEPTION
572 WHEN NO_DATA_FOUND THEN l_miss_rates_prim := 0;
573 END;
574
575 BEGIN
576 SELECT 1 INTO l_miss_rates_sec FROM FII_AP_INV_RATES_TEMP rates WHERE rates.sec_conversion_rate < 0;
577 EXCEPTION
578 WHEN NO_DATA_FOUND THEN l_miss_rates_sec := 0;
579 END;
580
581
582 ELSE
583
584 BEGIN
585 SELECT 1 INTO l_miss_rates_prim FROM FII_AP_INV_DIST_T stg WHERE stg.prim_conversion_rate < 0;
586 EXCEPTION
587 WHEN NO_DATA_FOUND THEN l_miss_rates_prim := 0;
588 END;
589
590 BEGIN
591 SELECT 1 INTO l_miss_rates_sec FROM FII_AP_INV_DIST_T stg WHERE stg.sec_conversion_rate < 0;
592 EXCEPTION
593 WHEN NO_DATA_FOUND THEN l_miss_rates_sec := 0;
594 END;
595
596
597 END IF;
598
599 --------------------------------------------------------
600 -- Print out translated messages to let user know there
601 -- are missing exchange rate information
602 --------------------------------------------------------
603 IF (l_miss_rates_prim > 0 OR
604 l_miss_rates_sec > 0) THEN
605 FII_MESSAGE.write_output(
606 msg_name => 'BIS_DBI_CURR_PARTIAL_LOAD',
607 token_num => 0);
608 END IF;
609
610 --------------------------------------------------------
611 -- Print out missing rates report
612 --------------------------------------------------------
613
614 /* Start : Mofified code as part for Bug 4219468 */
615
616 IF (l_miss_rates_prim > 0) OR
617 (l_miss_rates_sec > 0) THEN
618
619 BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
620
621 IF p_program_type = 'L' THEN
622
623 FOR rate_record in prim_MissingRate_L
624 LOOP
625 BIS_COLLECTION_UTILITIES.writeMissingRate(
626 g_prim_rate_type_name,
627 rate_record.from_currency,
628 g_prim_currency,
629 rate_record.actg_dt);
630 END LOOP;
631
632 FOR rate_record in sec_MissingRate_L
633 LOOP
634 BIS_COLLECTION_UTILITIES.writeMissingRate(
635 g_sec_rate_type_name,
636 rate_record.from_currency,
637 g_sec_currency,
638 rate_record.actg_dt);
639 END LOOP;
640
641 ELSE
642
643 FOR rate_record in prim_MissingRate
644 LOOP
645 BIS_COLLECTION_UTILITIES.writeMissingRate(
646 g_prim_rate_type_name,
647 rate_record.from_currency,
648 g_prim_currency,
649 rate_record.actg_dt);
650 END LOOP;
651
652 FOR rate_record in sec_MissingRate
653 LOOP
654 BIS_COLLECTION_UTILITIES.writeMissingRate(
655 g_sec_rate_type_name,
656 rate_record.from_currency,
657 g_sec_currency,
658 rate_record.actg_dt);
659 END LOOP;
660
661 END IF;
662 RETURN -1;
663 ELSE
664 RETURN 1;
665 END IF;
666
667 /* End : Modified code as part for Bug 4219468 */
668
669
670 EXCEPTION
671 WHEN OTHERS THEN
672 g_errbuf:=sqlerrm;
673 g_retcode:= -1;
674 g_exception_msg := g_retcode || ':' || g_errbuf;
675 FII_UTIL.put_line('Error occured while ' || g_state);
676 FII_UTIL.put_line(g_exception_msg);
677 RAISE;
678 END VERIFY_MISSING_RATES;
679
680 -----------------------------------------------------------
681 -- PROCEDURE POPULATE_AP_BASE_SUM
682 -----------------------------------------------------------
683 PROCEDURE POPULATE_AP_BASE_SUM IS
684 l_stmt VARCHAR2(1000);
685 seq_id NUMBER :=0;
686 BEGIN
687 SELECT FII_AP_INV_DIST_F_S.nextval INTO seq_id FROM dual;
688
689 g_state := 'Populating FII_AP_INV_DIST_F FROM FII_AP_INV_DIST_T table';
690
691 if g_debug_flag = 'Y' then
692 FII_UTIL.put_line('');
693 FII_UTIL.put_line('-------------------------------------------------');
694 FII_UTIL.put_line(g_state);
695 end if;
696
697
698 -- haritha.
699 -- Removed the where condition to check for invoice_id and other attributes
700 -- of a distribution. Instead added the invoice_distribution_id condition.
701 -- Also added the additional columns for insertion.
702
703 MERGE /*+ use_nl(bsum) */ INTO FII_AP_INV_DIST_F bsum
704 USING (SELECT *
705 FROM FII_AP_INV_DIST_T
706 WHERE (prim_conversion_rate > 0 OR sec_conversion_rate > 0)
707 ) stg
708 ON (bsum.INVOICE_DISTRIBUTION_ID = stg.INVOICE_DISTRIBUTION_ID)
709 WHEN MATCHED THEN UPDATE SET bsum.account_date = stg.account_date,
710 bsum.Inv_Currency_Code = stg.Inv_Currency_Code,
711 bsum.amount_b = stg.amount_b,
712 bsum.PO_Matched_Flag = stg.PO_Matched_Flag,
713 bsum.Source = stg.Source,
714 bsum.supplier_site_id = stg.supplier_site_id,
715 bsum.supplier_id = stg.supplier_id,
716 bsum.Invoice_Type = stg.Invoice_Type,
717 bsum.company_id = stg.company_id,
718 bsum.cost_center_id = stg.cost_center_id,
719 bsum.po_distribution_id = stg.po_distribution_id,
720 bsum.prim_amount_g = round((stg.amount_b*stg.prim_conversion_rate)/to_number(g_primary_mau))*
721 to_number(g_primary_mau),
722 bsum.sec_amount_g = round((stg.amount_b*stg.sec_conversion_rate)/to_number(g_secondary_mau))*
723 to_number(g_secondary_mau),
724 bsum.UPDATE_SEQUENCE = seq_id,
725 bsum.Posted_Flag = stg.Posted_Flag,
726 bsum.approved_flag = stg.approved_flag,
727 bsum.Org_ID = stg.Org_ID,
728 bsum.LAST_UPDATE_DATE = sysdate,
729 bsum.LAST_UPDATED_BY = g_fii_user_id,
730 bsum.LAST_UPDATE_LOGIN = g_fii_login_id,
731 bsum.dist_count = 1,
732 bsum.LINE_TYPE_LOOKUP_CODE = stg.LINE_TYPE_LOOKUP_CODE,
733 bsum.Inventory_Item_ID = stg.Inventory_Item_ID,
734 bsum.Purchasing_Category_ID = stg.Purchasing_Category_ID,
735 bsum.Item_Description = stg.Item_Description
736 WHEN NOT MATCHED THEN INSERT(bsum.ACCOUNT_DATE,
737 bsum.Inv_Currency_Code,
738 bsum.Invoice_ID,
739 bsum.AMOUNT_B,
740 bsum.PO_MATCHED_FLAG,
741 bsum.SOURCE,
742 bsum.INV_DIST_CREATED_BY,
743 bsum.SUPPLIER_SITE_ID,
744 bsum.INV_DIST_CREATION_DATE,
745 bsum.SUPPLIER_ID,
746 bsum.INVOICE_TYPE,
747 bsum.COMPANY_ID,
748 bsum.COST_CENTER_ID,
749 bsum.PO_DISTRIBUTION_ID,
750 bsum.Invoice_Distribution_ID,
751 bsum.PRIM_AMOUNT_G,
752 bsum.SEC_AMOUNT_G,
753 bsum.UPDATE_SEQUENCE,
754 bsum.Posted_Flag,
755 bsum.APPROVED_FLAG,
756 bsum.ORG_ID,
757 bsum.last_update_date,
758 bsum.last_updated_by,
759 bsum.creation_date,
760 bsum.created_by,
761 bsum.last_update_login,
762 bsum.dist_count,
763 bsum.LINE_TYPE_LOOKUP_CODE,
764 bsum.Inventory_Item_ID,
765 bsum.Purchasing_Category_ID,
766 bsum.Item_Description)
767 VALUES
768 (stg.ACCOUNT_DATE,
769 stg.Inv_Currency_Code,
770 stg.Invoice_ID,
771 stg.AMOUNT_B,
772 stg.PO_MATCHED_FLAG,
773 stg.SOURCE,
774 stg.INV_DIST_CREATED_BY,
775 stg.SUPPLIER_SITE_ID,
776 stg.INV_DIST_CREATION_DATE,
777 stg.SUPPLIER_ID,
778 stg.INVOICE_TYPE,
779 stg.COMPANY_ID,
780 stg.COST_CENTER_ID,
781 stg.PO_DISTRIBUTION_ID,
782 stg.Invoice_Distribution_ID,
783 round((stg.amount_b*stg.prim_conversion_rate)/to_number(g_primary_mau))*to_number(g_primary_mau),
784 round((stg.amount_b*stg.sec_conversion_rate)/to_number(g_secondary_mau))*to_number(g_secondary_mau),
785 seq_id,
786 stg.Posted_Flag,
787 stg.APPROVED_FLAG,
788 stg.org_id,
789 sysdate,
790 g_fii_user_id,
791 sysdate,
792 g_fii_user_id,
793 g_fii_login_id,
794 1,
795 stg.LINE_TYPE_LOOKUP_CODE,
796 stg.Inventory_Item_ID,
797 stg.Purchasing_Category_ID,
798 stg.Item_Description);
799
800 if g_debug_flag = 'Y' then
801 FII_UTIL.put_line('Merged ' || SQL%ROWCOUNT || ' records into FII_AP_INV_DIST_F');
802 FII_UTIL.put_line('');
803 end if;
804
805 DELETE FROM FII_AP_INV_DIST_F bsum
806 WHERE (bsum.UPDATE_SEQUENCE <> seq_id OR bsum.posted_flag = 'Y')
807 AND bsum.invoice_id IN (SELECT id.invoice_id FROM FII_AP_INV_ID id);
808
809 if g_debug_flag = 'Y' then
810 FII_UTIL.put_line('Deleted ' || SQL%ROWCOUNT || ' records from FII_AP_INV_DIST_F');
811 FII_UTIL.put_line('');
812 end if;
813
814 FOR i IN 0..31 LOOP --i represents the partition of ap_dbi_log.
815
816 IF g_timestamp3 + 30 >= g_timestamp1 AND g_oper_imp_prof_flag = 'Y' THEN --Copy records into Operations log table.
817
818 g_state := 'Copying records from partition ' || i || ' into FII_AP_DBI_LOG_PS_T.';
819
820 INSERT INTO FII_AP_DBI_LOG_PS_T(
821 Table_Name,
822 Operation_Flag,
823 Key_Value1_ID,
824 Key_Value2_ID,
825 Created_By,
826 Last_Updated_By,
827 Last_Update_Login,
828 Creation_Date,
829 Last_Update_Date)
830 SELECT Table_Name,
831 Operation_Flag,
832 Key_Value1,
833 Key_Value2,
834 Created_By,
835 Last_Updated_By,
836 Last_Update_Login,
837 Creation_Date,
838 Last_Update_Date
839 FROM AP_DBI_LOG
840 WHERE Partition_ID = i
841 AND Creation_Date >= g_timestamp2
842 AND Creation_Date < g_timestamp1;
843 END IF;
844
845 IF NOT (i = g_act_part1 OR i = g_act_part2) THEN --This is a non-active partition.
846 EXECUTE IMMEDIATE 'ALTER TABLE ' || g_ap_schema || '.AP_DBI_LOG TRUNCATE PARTITION P' || to_char(i);
847 END IF;
848
849 END LOOP;
850
851 TRUNCATE_TABLE('FII_AP_DBI_LOG_EXP_T');
852
853 COMMIT;
854
855 EXCEPTION
856 WHEN OTHERS THEN
857 g_errbuf:=sqlerrm;
858 g_retcode:= -1;
859 g_exception_msg := g_retcode || ':' || g_errbuf;
860 FII_UTIL.put_line('Error occured while ' || g_state);
861 FII_UTIL.put_line(g_exception_msg);
862 RAISE;
863
864 END POPULATE_AP_BASE_SUM;
865
866 -----------------------------------------------------------
867 --PROCEDURE CHILD_SETUP
868 -----------------------------------------------------------
869 PROCEDURE CHILD_SETUP(p_object_name VARCHAR2) IS
870 l_dir VARCHAR2(400);
871 l_stmt VARCHAR2(100);
872 BEGIN
873 g_state := 'Inside the procedure CHILD_SETUP';
874 -- l_stmt := ' ALTER SESSION SET global_names = false';
875 -- EXECUTE IMMEDIATE l_stmt;
876
877
878 ------------------------------------------------------
879 -- Set default directory in case if the profile option
880 -- BIS_DEBUG_LOG_DIRECTORY is not set up
881 ------------------------------------------------------
882 l_dir:=FII_UTIL.get_utl_file_dir;
883
884 ----------------------------------------------------------------
885 -- fii_util.initialize will get profile options FII_DEBUG_MODE
886 -- and BIS_DEBUG_LOG_DIRECTORY and set up the directory where
887 -- the log files and output files are written to
888 ----------------------------------------------------------------
889 FII_UTIL.initialize(p_object_name||'.log',p_object_name||'.out',l_dir,'FII_AP_INV_DISTRIBUTIONS_B_Worker');
890
891
892 g_fii_user_id := FND_GLOBAL.User_Id;
893 g_fii_login_id := FND_GLOBAL.Login_Id;
894
895 EXCEPTION
896 WHEN OTHERS THEN
897 rollback;
898 g_retcode := -2;
899 g_errbuf := ' ---------------------------------
900 Error in Procedure: CHILD_SETUP Message: '||sqlerrm;
901 RAISE g_procedure_failure;
902 END CHILD_SETUP;
903
904 -------------------
905 -- PROCEDURE Init
906 -------------------
907 PROCEDURE Init is
908 l_status VARCHAR2(30);
909 l_industry VARCHAR2(30);
910 l_stmt VARCHAR2(50);
911 BEGIN
912 g_state := 'Initializing the global variables';
913
914 -- --------------------------------------------------------
915 -- Find the schema owner (AP)
916 -- --------------------------------------------------------
917
918 g_ap_schema := FII_UTIL.get_schema_name('SQLAP');
919 if g_debug_flag = 'Y' then
920 FII_UTIL.put_line('g_ap_schema is '||g_ap_schema);
921 end if;
922
923 -- --------------------------------------------------------
924 -- Find the schema owner and tablespace
925 -- FII_AP_INV_B is using
926 -- --------------------------------------------------------
927 g_section := 'Section 20';
928 if g_debug_flag = 'Y' then
929 FII_UTIL.put_line('Section 20');
930 end if;
931
932 IF(FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, g_fii_schema))
933 THEN NULL;
934 if g_debug_flag = 'Y' then
935 FII_UTIL.put_line('g_fii_schema is '||g_fii_schema);
936 end if;
937 END IF;
938
939 g_section := 'Section 30';
940 if g_debug_flag = 'Y' then
941 FII_UTIL.put_line('Section 30');
942 end if;
943
944 /* Commenting out as unncessary. Query affects performance.
945
946 g_phase := 'Find FII tablespace';
947
948 SELECT tablespace_name
949 INTO g_tablespace
950 FROM all_tables
951 WHERE table_name = g_table_name
952 AND owner = g_fii_schema;
953
954 if g_debug_flag = 'Y' then
955 FII_UTIL.put_line('g_tablespace is '||g_tablespace);
956 end if;
957 g_section := 'Section 35';
958 if g_debug_flag = 'Y' then
959 FII_UTIL.put_line('Section 35');
960 end if;
961 */
962 -- --------------------------------------------------------
963 -- get minimum accountable unit of the warehouse currency
964 -- --------------------------------------------------------
965 g_phase := 'Find currency information';
966
967 g_primary_mau := nvl(fii_currency.get_mau_primary, 0.01 );
968 g_secondary_mau:= nvl(fii_currency.get_mau_secondary, 0.01);
969
970 g_prim_currency := bis_common_parameters.get_currency_code;
971 g_sec_currency := bis_common_parameters.get_secondary_currency_code;
972
973 g_phase := 'Find User ID and User Login';
974
975 g_fii_user_id := FND_GLOBAL.User_Id;
976 g_fii_login_id := FND_GLOBAL.Login_Id;
977 g_prim_rate_type := bis_common_parameters.get_rate_type;
978 g_sec_rate_type := bis_common_parameters.get_secondary_rate_type;
979
980 if ((g_sec_currency IS NULL and g_sec_rate_type IS NOT NULL) OR
981 (g_sec_currency IS NOT NULL and g_sec_rate_type IS NULL)) THEN
982 RAISE G_NEED_SECONDARY_INFO;
983 END IF;
984
985 begin
986 g_phase := 'Convert rate_type to rate_type_name';
987
988 select user_conversion_type into g_prim_rate_type_name
989 from gl_daily_conversion_types
990 where conversion_type = g_prim_rate_type;
991
992 if g_sec_rate_type is not null then
993 select user_conversion_type into g_sec_rate_type_name
994 from gl_daily_conversion_types
995 where conversion_type = g_sec_rate_type;
996 else
997 g_sec_rate_type_name := null;
998 end if;
999 exception
1000 when others then
1001 fii_util.write_log(
1002 'Failed to convert rate_type to rate_type_name' );
1003 raise;
1004 end;
1005
1006 IF (g_fii_user_id IS NULL OR g_fii_login_id IS NULL) THEN
1007 RAISE G_LOGIN_INFO_NOT_AVABLE;
1008 END IF;
1009
1010 if g_debug_flag = 'Y' then
1011 FII_UTIL.put_line('User ID: ' || g_fii_user_id || ' Login ID: ' || g_fii_login_id);
1012 end if;
1013
1014 EXCEPTION
1015 WHEN G_LOGIN_INFO_NOT_AVABLE THEN
1016
1017 g_retcode := -1;
1018 g_errbuf := 'Can not get User ID and Login ID, program exit';
1019 RAISE;
1020 WHEN G_NEED_SECONDARY_INFO THEN
1021 g_retcode := -1;
1022 g_errbuf := fnd_message.get_string('FII', 'FII_AP_SEC_MISS');
1023 RAISE;
1024 WHEN OTHERS THEN
1025 g_retcode := -1;
1026 g_errbuf := '
1027 ---------------------------------
1028 Error in Procedure: INIT
1029 Section: '||g_section||'
1030 Phase: '||g_phase||'
1031 Message: '||sqlerrm;
1032 RAISE g_procedure_failure;
1033
1034 END Init;
1035
1036 ---------------------------------------------------------------
1037 -- PROCEDURE VERIFY_CCID_UP_TO_DATE
1038 ---------------------------------------------------------------
1039 PROCEDURE VERIFY_CCID_UP_TO_DATE IS
1040 l_errbuf VARCHAR2(1000);
1041 l_retcode VARCHAR2(100);
1042 l_request_id NUMBER;
1043 l_result BOOLEAN;
1044 l_phase VARCHAR2(500) := NULL;
1045 l_status VARCHAR2(500) := NULL;
1046 l_devphase VARCHAR2(500) := NULL;
1047 l_devstatus VARCHAR2(500) := NULL;
1048 l_message VARCHAR2(500) := NULL;
1049 BEGIN
1050 g_state := 'Calling Procedure: VERIFY_CCID_UP_TO_DATE';
1051 if g_debug_flag = 'Y' then
1052 FII_UTIL.put_line('Calling Procedure: VERIFY_CCID_UP_TO_DATE');
1053 FII_UTIL.put_line('');
1054 end if;
1055
1056 g_phase := 'Verifying if CCID Dimension is up to date';
1057 if g_debug_flag = 'Y' then
1058 FII_UTIL.put_line(g_phase);
1059 end if;
1060
1061 IF(FII_GL_CCID_C.NEW_CCID_IN_GL) THEN
1062 if g_debug_flag = 'Y' then
1063 FII_UTIL.put_line('CCID Dimension is not up to date, calling CCID Dimension update
1064 program');
1065 end if;
1066 g_phase := 'Calling CCID Dimension update program';
1067 l_request_id := FND_REQUEST.SUBMIT_REQUEST('FII',
1068 'FII_GL_CCID_C',
1069 NULL, NULL, FALSE, 'I');
1070 IF (l_request_id = 0) THEN
1071 rollback;
1072 g_retcode := -1;
1073 raise G_NO_CHILD_PROCESS;
1074 END IF;
1075
1076 COMMIT;
1077
1078 l_result := FND_CONCURRENT.wait_for_request(l_request_id,
1079 5,
1080 600,
1081 l_phase,
1082 l_status,
1083 l_devphase,
1084 l_devstatus,
1085 l_message);
1086
1087 IF l_result THEN
1088 if g_debug_flag = 'Y' then
1089 FII_UTIL.put_line('CCID Dimension populated successfully');
1090 end if;
1091 ELSE
1092 FII_UTIL.put_line('CCID Dimension populated unsuccessfully');
1093 raise G_CCID_FAILED;
1094 END IF;
1095
1096 ELSE
1097 if g_debug_flag = 'Y' then
1098 FII_UTIL.put_line('CCID Dimension is up to date');
1099 FII_UTIL.put_line('');
1100 end if;
1101 END IF;
1102
1103 Exception
1104 WHEN G_NO_CHILD_PROCESS THEN
1105 g_retcode := -1;
1106 g_errbuf := '
1107 ----------------------------
1108 Error in Procedure : VERIFY_CCID_UP_TO_DATE
1109 Phase: Submitting Child process to run CCID program';
1110 raise;
1111 WHEN G_CCID_FAILED THEN
1112 g_retcode := -1;
1113 g_errbuf := '
1114 ----------------------------
1115 Error in Procedure : VERIFY_CCID_UP_TO_DATE
1116 Phase: Running CCID program';
1117 raise;
1118 WHEN OTHERS Then
1119 g_retcode := -1;
1120 g_errbuf := '
1121 ----------------------------
1122 Error in Procedure : VERIFY_CCID_UP_TO_DATE
1123 Phase: ' || g_phase || '
1124 Message: '||sqlerrm;
1125 raise;
1126 END VERIFY_CCID_UP_TO_DATE;
1127
1128 ---------------------------------
1129 -- PROCEDURE INSERT_INTO_STG
1130 ---------------------------------
1131
1132 PROCEDURE INSERT_INTO_STG (p_parallel_query IN NUMBER,
1133 p_sort_area_size IN NUMBER,
1134 p_hash_area_size IN NUMBER) IS
1135
1136 l_stmt VARCHAR2(1000);
1137
1138 BEGIN
1139
1140 l_stmt := 'alter session set hash_area_size = '||p_hash_area_size;
1141 execute immediate l_stmt;
1142 l_stmt := 'alter session set sort_area_size= '|| p_sort_area_size;
1143 execute immediate l_stmt;
1144
1145 g_state := 'Loading data into staging table';
1146 if g_debug_flag = 'Y' then
1147 fii_util.put_line(' ');
1148 fii_util.put_line('Loading data into staging table');
1149 fii_util.start_timer;
1150 fii_util.put_line('');
1151 end if;
1152
1153
1154 INSERT /*+ APPEND PARALLEL(F) */ INTO FII_AP_INV_DIST_T F
1155 (ACCOUNT_DATE,
1156 INV_CURRENCY_CODE,
1157 INVOICE_ID,
1158 AMOUNT_B,
1159 PO_MATCHED_FLAG,
1160 SOURCE,
1161 INV_DIST_CREATED_BY,
1162 SUPPLIER_SITE_ID,
1163 INV_DIST_CREATION_DATE,
1164 SUPPLIER_ID,
1165 INVOICE_TYPE,
1166 COMPANY_ID,
1167 COST_CENTER_ID,
1168 PRIM_CONVERSION_RATE,
1169 SEC_CONVERSION_RATE,
1170 Posted_Flag,
1171 Approved_Flag,
1172 ORG_ID,
1173 INVOICE_DISTRIBUTION_ID,
1174 PO_DISTRIBUTION_ID,
1175 LINE_TYPE_LOOKUP_CODE,
1176 INVENTORY_ITEM_ID,
1177 PURCHASING_CATEGORY_ID,
1178 ITEM_DESCRIPTION)
1179 select /*+ leading(v) no_expand full(aid) use_hash(aid, ai, fnd)
1180 parallel(ai) parallel(fnd) parallel(aid)
1181 pq_distribute(aid hash, hash) pq_distribute(ai hash, hash)
1182 pq_distribute(fnd hash, hash) */
1183 nvl(trunc(aid.accounting_date),trunc(aid.accounting_date)) account_date,
1184 V.Currency_Code Inv_Currency_Code,
1185 aid.invoice_id invoice_id,
1186 nvl(aid.base_amount, aid.amount) amount_b,
1187 nvl2(aid.po_distribution_id, 'Y', 'N') po_matched_flag,
1188 ai.source source,
1189 nvl(fnd.employee_id, -1) inv_dist_created_by,
1190 nvl(ai.vendor_site_id, -1) supplier_site_id,
1191 trunc(aid.creation_date) inv_dist_creation_date,
1192 ai.vendor_id supplier_id,
1193 ai.invoice_type_lookup_code invoice_type,
1194 v.company_id company_id,
1195 v.cost_center_id cost_center_id,
1196 -1 prim_conversion_rate,
1197 -1 sec_conversion_rate,
1198 nvl(aid.posted_flag, 'N') posted_flag,
1199 nvl(aid.match_status_flag, 'N') approved_flag,
1200 nvl(ai.org_id, -1) org_id,
1201 aid.invoice_distribution_id invoice_distribution_id,
1202 aid.po_distribution_id po_distribution_id,
1203 aid.line_type_lookup_code line_type_lookup_code,
1204 ail.Inventory_Item_ID Inventory_Item_ID,
1205 ail.Purchasing_Category_ID Purchasing_Category_ID,
1206 ail.Item_Description Item_Description
1207 from (
1208 select /*+ no_merge no_expand parallel(glcc) */
1209 gsob.ledger_id, glcc.code_combination_id,
1210 gsob.currency_code, glcc.natural_account_id,
1211 glcc.company_id, glcc.cost_center_id,
1212 gsob.chart_of_accounts_id, glcc.user_dim1_id,
1213 glcc.user_dim2_id
1214 from fii_source_ledger_groups fslg, fii_slg_assignments slga,
1215 gl_ledgers_public_v gsob, fii_gl_ccid_dimensions glcc
1216 where slga.chart_of_accounts_id = glcc.chart_of_accounts_id
1217 and slga.bal_seg_value_id in (glcc.company_id, -1)
1218 and slga.source_ledger_group_id = fslg.source_ledger_group_id
1219 and slga.ledger_id = gsob.ledger_id
1220 and fslg.usage_code = g_usage_code) v,
1221 ap_invoice_distributions_all aid,
1222 ap_invoice_lines_all ail,
1223 ap_invoices_all ai,
1224 fnd_user fnd
1225 where ai.invoice_id = aid.invoice_id
1226 and ai.invoice_id = ail.invoice_id
1227 AND AID.Invoice_ID = AIL.Invoice_ID
1228 AND AID.Invoice_Line_Number = AIL.Line_Number
1229 and nvl(aid.posted_flag, 'N') = 'N'
1230 and fnd.user_id = nvl(aid.created_by, ai.cancelled_by)
1231 and aid.set_of_books_id = v.ledger_id
1232 and aid.dist_code_combination_id = v.code_combination_id
1233 and aid.accounting_date >= g_start_date;
1234
1235
1236 if g_debug_flag = 'Y' then
1237 fii_util.put_line('Processed '||SQL%ROWCOUNT||' rows');
1238 fii_util.stop_timer;
1239 fii_util.print_timer('Duration');
1240 end if;
1241
1242 commit;
1243
1244 END INSERT_INTO_STG;
1245
1246 ------------------------------------
1247 ---- PROCEDURE INSERT_RATES
1248 ------------------------------------
1249
1250 PROCEDURE INSERT_RATES IS
1251
1252 BEGIN
1253 g_state := 'Loading data into rates table';
1254 if g_debug_flag = 'Y' then
1255 fii_util.put_line(' ');
1256 fii_util.put_line('Loading data into rates table');
1257 fii_util.start_timer;
1258 fii_util.put_line('');
1259 end if;
1260
1261 --modified by ilavenil to handle future dated transaction. The change is usage of least(...,sysdate)
1262 insert into fii_ap_inv_rates_temp
1263 (FUNCTIONAL_CURRENCY,
1264 TRX_DATE,
1265 PRIM_CONVERSION_RATE,
1266 SEC_CONVERSION_RATE)
1267 select cc functional_currency,
1268 dt trx_date,
1269 decode(cc, g_prim_currency, 1, FII_CURRENCY.GET_GLOBAL_RATE_PRIMARY (cc,least(dt,sysdate))) PRIM_CONVERSION_RATE,
1270 decode(cc, g_sec_currency, 1, FII_CURRENCY.GET_GLOBAL_RATE_SECONDARY(cc,least(dt,sysdate))) SEC_CONVERSION_RATE
1271 from (
1272 select /*+ no_merge */ distinct
1273 inv_currency_code cc,
1274 account_date dt
1275 from FII_AP_INV_DIST_T
1276 );
1277
1278 if g_debug_flag = 'Y' then
1279 fii_util.put_line('Processed '||SQL%ROWCOUNT||' rows');
1280 fii_util.stop_timer;
1281 fii_util.print_timer('Duration');
1282 end if;
1283
1284 END INSERT_RATES;
1285
1286 ---------------------------------------
1287 ----- PROCEDURE INSERT_INTO_SUMMARY
1288 ---------------------------------------
1289
1290 PROCEDURE INSERT_INTO_SUMMARY (p_parallel_query IN NUMBER) IS
1291
1292 l_stmt VARCHAR2(50);
1293 seq_id NUMBER := 0;
1294
1295 BEGIN
1296 g_state := 'Loading data into base summary table';
1297 if g_debug_flag = 'Y' then
1298 fii_util.put_line(' ');
1299 fii_util.put_line('Loading data into base summary table');
1300 fii_util.start_timer;
1301 fii_util.put_line('');
1302 end if;
1303
1304 SELECT FII_AP_INV_DIST_F_S.nextval INTO seq_id FROM dual;
1305
1306
1307 INSERT /*+ APPEND PARALLEL(F) */ INTO FII_AP_INV_DIST_F F (
1308 ACCOUNT_DATE,
1309 INV_CURRENCY_CODE,
1310 INVOICE_ID,
1311 INVOICE_DISTRIBUTION_ID,
1312 AMOUNT_B,
1313 PO_MATCHED_FLAG,
1314 SOURCE,
1315 INV_DIST_CREATED_BY,
1316 SUPPLIER_SITE_ID,
1317 INV_DIST_CREATION_DATE,
1318 SUPPLIER_ID,
1319 INVOICE_TYPE,
1320 COMPANY_ID,
1321 COST_CENTER_ID,
1322 PO_DISTRIBUTION_ID,
1323 PRIM_AMOUNT_G,
1324 SEC_AMOUNT_G,
1325 UPDATE_SEQUENCE,
1326 POSTED_FLAG,
1327 APPROVED_FLAG,
1328 ORG_ID,
1329 LAST_UPDATE_DATE,
1330 LAST_UPDATED_BY,
1331 CREATION_DATE,
1332 CREATED_BY,
1333 LAST_UPDATE_LOGIN,
1334 DIST_COUNT,
1335 LINE_TYPE_LOOKUP_CODE,
1336 INVENTORY_ITEM_ID,
1337 PURCHASING_CATEGORY_ID,
1338 ITEM_DESCRIPTION)
1339 SELECT /*+ PARALLEL(stg) PARALLEL(rates) */
1340 stg.account_date,
1341 stg.Inv_Currency_Code,
1342 stg.invoice_id,
1343 stg.invoice_distribution_id,
1344 stg.amount_b amount_b,
1345 stg.po_matched_flag,
1346 stg.source,
1347 stg.inv_dist_created_by,
1348 stg.supplier_site_id,
1349 stg.inv_dist_creation_date,
1350 stg.supplier_id,
1351 stg.invoice_type,
1352 stg.company_id,
1353 stg.cost_center_id,
1354 stg.po_distribution_id,
1355 round((stg.amount_b*rates.prim_conversion_rate)/to_number(g_primary_mau))*to_number(g_primary_mau),
1356 round((stg.amount_b*rates.sec_conversion_rate)/to_number(g_secondary_mau))*to_number(g_secondary_mau),
1357 seq_id,
1358 stg.Posted_Flag,
1359 stg.approved_flag,
1360 stg.org_id,
1361 sysdate,
1362 g_fii_user_id,
1363 sysdate,
1364 g_fii_login_id,
1365 g_fii_login_id,
1366 1,
1367 stg.LINE_TYPE_LOOKUP_CODE,
1368 stg.Inventory_Item_ID,
1369 stg.Purchasing_Category_ID,
1370 stg.Item_Description
1371 FROM FII_AP_INV_DIST_T stg, fii_ap_inv_rates_temp rates
1372 where stg.account_date = rates.trx_date
1373 and stg.inv_currency_code = rates.functional_currency;
1374
1375 if g_debug_flag = 'Y' then
1376 fii_util.put_line('Processed '||SQL%ROWCOUNT||' rows');
1377 fii_util.stop_timer;
1378 fii_util.print_timer('Duration');
1379 end if;
1380
1381 commit;
1382
1383 IF g_oper_imp_prof_flag = 'Y' THEN
1384 FOR i IN 0..31 LOOP --i represents the partition of ap_dbi_log.
1385
1386 IF g_timestamp3 + 30 >= g_timestamp1 THEN --Copy records into Operations log table.
1387 INSERT INTO FII_AP_DBI_LOG_PS_T(
1388 Table_Name,
1389 Operation_Flag,
1390 Key_Value1_ID,
1391 Key_Value2_ID,
1392 Created_By,
1393 Last_Updated_By,
1394 Last_Update_Login,
1395 Creation_Date,
1396 Last_Update_Date)
1397 SELECT Table_Name,
1398 Operation_Flag,
1399 Key_Value1,
1400 Key_Value2,
1401 Created_By,
1402 Last_Updated_By,
1403 Last_Update_Login,
1404 Creation_Date,
1405 Last_Update_Date
1406 FROM AP_DBI_LOG
1407 WHERE Partition_ID = i
1408 AND Creation_Date >= g_timestamp2
1409 AND Creation_Date < g_timestamp1;
1410 END IF;
1411
1412 IF NOT (i = g_act_part1 OR i = g_act_part2) THEN --This is a non-active partition.
1413 EXECUTE IMMEDIATE 'ALTER TABLE ' || g_ap_schema || '.AP_DBI_LOG TRUNCATE PARTITION P' || to_char(i);
1414 END IF;
1415
1416 END LOOP;
1417
1418 END IF;
1419
1420 TRUNCATE_TABLE('FII_AP_DBI_LOG_EXP_T');
1421
1422 COMMIT;
1423
1424 END INSERT_INTO_SUMMARY;
1425 -- ------------------------------------------------------------
1426 -- Public Functions and Procedures
1427 -- ------------------------------------------------------------
1428
1429 -- Procedure
1430 -- Collect()
1431 -- Purpose
1432 -- This Collect routine Handles all functions involved in the AP summarization
1433 -- and populating FII base summary table and cleaning.
1434
1435 -----------------------------------------------------------
1436 -- PROCEDURE Collect
1437 -----------------------------------------------------------
1438 PROCEDURE Collect(Errbuf IN OUT NOCOPY VARCHAR2,
1439 Retcode IN OUT NOCOPY VARCHAR2,
1440 p_from_date IN VARCHAR2,
1441 p_to_date IN VARCHAR2,
1442 p_no_worker IN NUMBER,
1443 p_program_type IN VARCHAR2,
1444 p_parallel_query IN NUMBER,
1445 p_hash_area_size IN NUMBER,
1446 p_sort_area_size IN NUMBER
1447 ) IS
1448 l_status VARCHAR2(30);
1449 l_industry VARCHAR2(30);
1450 l_stmt VARCHAR2(4000);
1451 l_dir VARCHAR2(400);
1452
1453 l_start_date DATE;
1454 l_end_date DATE;
1455
1456 l_start_date_t DATE := NULL;
1457 l_end_date_t DATE := NULL;
1458 l_period_from DATE := NULL;
1459 l_period_to DATE := NULL;
1460 l_start_date_temp DATE := NULL;
1461
1462
1463 TYPE WorkerList is table of NUMBER index by binary_integer;
1464 l_worker WorkerList;
1465
1466 l_global_param_list dbms_sql.varchar2_table;
1467
1468 l_new_inv_id_count NUMBER;
1469
1470 -- Declaring local variables to populate the start date
1471 -- and end date for incremental mode.
1472 l_last_start_date DATE;
1473 l_last_end_date DATE;
1474 l_last_period_from DATE;
1475 l_last_period_to DATE;
1476 l_lud_hours NUMBER;
1477
1478 l_rowcount NUMBER;
1479
1480 BEGIN
1481 g_state := 'Inside the procedure COLLECT';
1482
1483 retcode := 0;
1484
1485 g_program_type := p_program_type;
1486
1487 -- l_stmt := ' ALTER SESSION SET global_names = false';
1488 -- EXECUTE IMMEDIATE l_stmt;
1489
1490 ------------------------------------------------------
1491 -- Set default directory in case if the profile option
1492 -- BIS_DEBUG_LOG_DIRECTORY is not set up
1493 ------------------------------------------------------
1494
1495 l_dir:=FII_UTIL.get_utl_file_dir;
1496
1497 ----------------------------------------------------------------
1498 -- FII_UTIL.initialize will get profile options FII_DEBUG_MODE
1499 -- and BIS_DEBUG_LOG_DIRECTORY and set up the directory where
1500 -- the log files and output files are written to
1501 ----------------------------------------------------------------
1502 IF g_program_type = 'L' THEN
1503 FII_UTIL.initialize('FII_AP_INV_DISTRIBUTIONS_F.log','FII_AP_INV_DISTRIBUTIONS_F.out',l_dir,'FII_AP_INV_DISTRIBUTIONS_B_L');
1504 ELSE
1505 FII_UTIL.initialize('FII_AP_INV_DISTRIBUTIONS_F.log','FII_AP_INV_DISTRIBUTIONS_F.out',l_dir, 'FII_AP_INV_DISTRIBUTIONS_B_I');
1506 END IF;
1507
1508 -------------------------------------------------------------
1509 -- Check if FII: DBI Payables Expenses Implementation profile
1510 -- is turned on. If yes, continue, otherwise, error out. User
1511 -- need to turn on this profile option before running this program
1512 ---------------------------------------------------------------
1513 IF g_exp_imp_prof_flag = 'N' THEN
1514 g_state := 'Checking Implementation profile option';
1515 FII_MESSAGE.write_log(
1516 msg_name => 'FII_AP_DBI_EXP_IMP',
1517 token_num => 0);
1518 g_retcode := -2;
1519 g_errbuf := 'FII: DBI Payables Expenses Implementation profile option is not turned on';
1520 RAISE G_IMP_NOT_SET;
1521 END IF;
1522
1523 -----------------------------------------------------
1524 -- Calling BIS API to do common set ups
1525 -- If it returns false, then program should error
1526 -- out
1527 -----------------------------------------------------
1528 l_global_param_list(1) := 'BIS_GLOBAL_START_DATE';
1529 l_global_param_list(2) := 'BIS_PRIMARY_CURRENCY_CODE';
1530 l_global_param_list(3) := 'BIS_PRIMARY_RATE_TYPE';
1531 IF (NOT bis_common_parameters.check_global_parameters(l_global_param_list)) THEN
1532 FII_UTIL.put_line(fnd_message.get_string('FII', 'FII_BAD_GLOBAL_PARA'));
1533 retcode := -1;
1534 return;
1535 END IF;
1536
1537
1538 -- ------------------------------------------
1539 -- Initalize other variables
1540 -- ------------------------------------------
1541 if g_debug_flag = 'Y' then
1542 FII_UTIL.put_line(' ');
1543 FII_UTIL.put_line('-------------------------------------------------');
1544 FII_UTIL.put_line('Initialization');
1545 end if;
1546 INIT;
1547 if g_debug_flag = 'Y' then
1548 FII_UTIL.put_line('-------------------------------------------------');
1549 FII_UTIL.put_line(' ');
1550 end if;
1551
1552 IF p_program_type = 'L' THEN
1553 g_state := 'Running Initial Load, truncate staging and base summary table.';
1554 IF g_debug_flag = 'Y' then
1555 FII_UTIL.put_line('Running Initial Load, truncate staging and base summary table.');
1556 END IF;
1557 TRUNCATE_TABLE('FII_AP_INV_DIST_T');
1558 TRUNCATE_TABLE('FII_AP_INV_DIST_F');
1559 COMMIT;
1560 END IF;
1561
1562 -- Load and Increment programs should record the dates seperately
1563 -- so that the dates are derived correctly for incremental mode.
1564 g_state := 'Calling BIS_COLLECTION_UTILITIES.setup';
1565 IF p_program_type = 'L' THEN
1566 IF (NOT BIS_COLLECTION_UTILITIES.setup('FII_AP_INV_DISTRIBUTIONS_B_L')) THEN
1567 raise_application_error(-20000,errbuf);
1568 return;
1569 END IF;
1570 ELSE
1571 IF (NOT BIS_COLLECTION_UTILITIES.setup('FII_AP_INV_DISTRIBUTIONS_B_I')) THEN
1572 raise_application_error(-20000,errbuf);
1573 return;
1574 END IF;
1575 END IF;
1576
1577 ------------------------------------------
1578 -- Check setups only if we are running in
1579 -- Incremental Mode, p_program_type = 'I'
1580 -------------------------------------------
1581 IF (p_program_type = 'I') THEN
1582 ---------------------------------------------
1583 -- Check if any set up got changed. If yes,
1584 -- then we need to truncate the summary table
1585 -- and then reload
1586 ---------------------------------------------
1587 IF(CHECK_IF_SET_UP_CHANGE = 'TRUE') THEN
1588 FII_MESSAGE.write_output(msg_name => 'FII_TRUNC_SUMMARY', token_num => 0);
1589
1590 FII_UTIL.put_line(fnd_message.get_string('FII', 'FII_TRUNC_SUMMARY'));
1591 retcode := -1;
1592 RETURN;
1593 END IF;
1594 ELSIF (p_program_type = 'L') THEN
1595 ---------------------------------------------
1596 -- If running in Inital Load, then update
1597 -- change log to indicate that resummarization
1598 -- is not necessary since everything is
1599 -- going to be freshly loaded
1600 ---------------------------------------------
1601 g_state := 'Updating change log';
1602 UPDATE fii_change_log
1603 SET item_value = 'N',
1604 last_update_date = SYSDATE,
1605 last_update_login = g_fii_login_id,
1606 last_updated_by = g_fii_user_id
1607 WHERE log_item = 'AP_RESUMMARIZE';
1608
1609 COMMIT;
1610 END IF;
1611
1612 g_today := sysdate;
1613
1614 -- haritha
1615 -- For Incremental mode we no longer need to select the invoices based
1616 -- on the start and end date. The invoices will be selected from the log
1617 -- table. Hence removed the logic to populate start and end dates for the
1618 -- incremental mode.
1619
1620 IF p_program_type = 'L' THEN
1621
1622 -------------------------------------------------------------
1623 -- When running in Initial mode, the default values of the
1624 -- parameters are defined in the concurrent program seed data
1625 -- We will always collect up to sysdate to make sure no records
1626 -- are missed from the collection
1627 -------------------------------------------------------------
1628 l_start_date := trunc(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'));
1629 l_end_date := sysdate + 1 - ONE_SECOND;
1630
1631 g_start_date := l_start_date;
1632 g_end_date := l_end_date;
1633
1634 END IF;
1635 if g_debug_flag = 'Y' then
1636 FII_UTIL.put_line(' ');
1637 FII_UTIL.put_line('-------------------------------------------------');
1638 FII_UTIL.put_line('The date range of collection is from ' || to_char(g_start_date, 'MM/DD/YYYY HH24:MI:SS') || '.');
1639 FII_UTIL.put_line('-------------------------------------------------');
1640 FII_UTIL.put_line(' ');
1641 end if;
1642
1643 --------------------------------------------------------------------
1644 -- Checking to see if there's any record in the staging table.
1645 -- If yes, then that means the previous load failed due to missing
1646 -- exchange rates. In this case, we run in resume mode.
1647 -- If no, then program will insert records into staging table
1648 --------------------------------------------------------------------
1649 BEGIN
1650 SELECT 1 INTO g_num FROM fii_ap_inv_dist_t where rownum = 1;
1651 EXCEPTION
1652 WHEN NO_DATA_FOUND THEN g_num := 0;
1653 END;
1654
1655 IF (g_num > 0) THEN
1656 g_fix_rates := 'Y';
1657 ELSE
1658 g_fix_rates := 'N';
1659 END IF;
1660
1661 ---------------------------------------------------
1662 -- If not running in resume mode, then insert new
1663 -- records into staging table
1664 -- If running in resume mode, then fix the exchange
1665 -- rates in the staging table, then verify once
1666 -- again if there are missing exchange rates
1667 ---------------------------------------------------
1668 IF (g_fix_rates = 'N') THEN
1669
1670 ----------------------------------------------------------
1671 -- This variable indicates that if exception occur, do
1672 -- we need to truncate the staging table.
1673 -- We are about to submit the child process which will
1674 -- insert records into staging table. If any exception
1675 -- occured during the child process run, the staging table
1676 -- should be truncated. After all child process are done
1677 -- inserting records into staging table, this flag will
1678 -- be set to 'N'.
1679 ----------------------------------------------------------
1680 g_truncate_staging := 'Y';
1681
1682 ----------------------------------------------------------
1683 -- This variable indicates that if exception occur, do
1684 -- we need to truncate the temporary Revenue_ID table.
1685 -- We need to truncate this table if the program starts
1686 -- fresh at the beginning.
1687 -- We will reset this variable to 'N' after we have
1688 -- populate it. We will not truncate it until next time
1689 -- when the program starts fresh (non-resume). We want
1690 -- to preserve this table for debugging purpose.
1691 ----------------------------------------------------------
1692 g_truncate_id := 'Y';
1693
1694 g_truncate_rates := 'Y';
1695
1696 ----------------------------------------------------------
1697 -- Call CLEAN_UP to clean up processing tables before
1698 -- start
1699 ----------------------------------------------------------
1700 CLEAN_UP;
1701
1702 ---------------------------------------------------------
1703 -- After we do initial clean up, we will set this flag to
1704 -- 'N' to preserve the temporary Revenue ID table for
1705 -- debugging purpose
1706 ---------------------------------------------------------
1707 g_truncate_id := 'N';
1708
1709 ------------------------------------------------------------
1710 --Get timestamps used to maintain ap_dbi_log.
1711 --g_timestamp1 - current timestamp.
1712 --g_timestamp2 - last Payables Operation/Expenses load.
1713 --g_timestamp3 - last Payables Operations load, if implemented.
1714 --g_timestamp4 - last Payables Expenses load.
1715 -------------------------------------------------------------
1716 g_state := 'Defining timestamps to maintain ap_dbi_log.';
1717 IF g_debug_flag = 'Y' then
1718 FII_UTIL.put_line('');
1719 FII_UTIL.put_line(g_state);
1720 fii_util.put_line('');
1721 END IF;
1722
1723 g_timestamp1 := BIS_COLLECTION_UTILITIES.G_Start_Date;
1724
1725 BIS_COLLECTION_UTILITIES.get_last_refresh_dates('FII_AP_INV_DISTRIBUTIONS_B_L',
1726 l_start_date_t, l_end_date_t,
1727 l_period_from, l_period_to);
1728
1729 BIS_COLLECTION_UTILITIES.get_last_refresh_dates('FII_AP_INV_DISTRIBUTIONS_B_I',
1730 l_start_date_temp, l_end_date_t,
1731 l_period_from, l_period_to);
1732
1733 g_timestamp2 := GREATEST(NVL(l_start_date_t, BIS_COMMON_PARAMETERS.Get_Global_Start_Date),
1734 NVL(l_start_date_temp, BIS_COMMON_PARAMETERS.Get_Global_Start_Date));
1735 g_timestamp4 := g_timestamp2;
1736
1737 IF g_oper_imp_prof_flag = 'Y' THEN
1738
1739 BIS_COLLECTION_UTILITIES.get_last_refresh_dates('FII_AP_INV_SUM_INIT',
1740 l_start_date_t, l_end_date_t,
1741 l_period_from, l_period_to);
1742
1743 BIS_COLLECTION_UTILITIES.get_last_refresh_dates('FII_AP_INV_SUM_INC',
1744 l_start_date_temp, l_end_date_t,
1745 l_period_from, l_period_to);
1746
1747 g_timestamp3 := GREATEST(NVL(l_start_date_t, BIS_COMMON_PARAMETERS.Get_Global_Start_Date),
1748 NVL(l_start_date_temp, BIS_COMMON_PARAMETERS.Get_Global_Start_Date));
1749
1750 g_timestamp2 := GREATEST(g_timestamp2, g_timestamp3);
1751 END IF;
1752
1753
1754 g_act_part1 := MOD(TO_NUMBER(TO_CHAR(TRUNC(g_timestamp1), 'J')), 32);
1755 g_act_part2 := MOD(TO_NUMBER(TO_CHAR(TRUNC(g_timestamp1+1), 'J')), 32);
1756
1757 g_old_act_part1 := MOD(TO_NUMBER(TO_CHAR(TRUNC(g_timestamp2), 'J')), 32);
1758 g_old_act_part2 := MOD(TO_NUMBER(TO_CHAR(TRUNC(g_timestamp2+1), 'J')), 32);
1759
1760 if g_debug_flag = 'Y' then
1761
1762 FII_UTIL.put_line('Current Load Timestamp is: ' || to_char(g_timestamp1, 'YYYY/MM/DD HH24:MI:SS'));
1763 FII_UTIL.put_line('Previous Payables Load Timestamp is: ' || to_char(g_timestamp2, 'YYYY/MM/DD HH24:MI:SS'));
1764 FII_UTIL.put_line('Previous Payables Operations Load Timestamp is: ' || to_char(g_timestamp3, 'YYYY/MM/DD HH24:MI:SS'));
1765 FII_UTIL.put_line('Previous Payables Expenses Load Timestamp is: ' || to_char(g_timestamp4, 'YYYY/MM/DD HH24:MI:SS'));
1766 end if;
1767
1768
1769 -------------------------------------------------
1770 -- For Incremental Load, Populate Invoice IDs of the qualified invoices
1771 -- into an Invoice ID temp table
1772 -------------------------------------------------
1773 IF g_program_type = 'I' THEN
1774
1775 IF g_timestamp4 + 30 < g_timestamp1 THEN
1776 g_errbuf := fnd_message.get_string('FII', 'FII_AP_RUN_INIT');
1777 RAISE G_RUN_INIT;
1778 END IF;
1779
1780 if g_debug_flag = 'Y' then
1781 FII_UTIL.put_line('Populating Invoice ID table');
1782 FII_UTIL.start_timer;
1783 end if;
1784 l_new_inv_id_count := POPULATE_INV_ID_TEMP;
1785 if g_debug_flag = 'Y' then
1786 FII_UTIL.stop_timer;
1787 FII_UTIL.print_timer('Duration');
1788 end if;
1789
1790 --------------------------------------------------------
1791 -- POPULATE_INV_ID_TEMP will identify the new invoices
1792 -- which need to be processed based on the user entered
1793 -- date range. If there are no new invoice to process
1794 -- the program will exit immediately with complete
1795 -- successful status
1796 --------------------------------------------------------
1797 IF (l_new_inv_id_count = 0) THEN
1798
1799 if g_debug_flag = 'Y' then
1800 FII_UTIL.put_line('No Invoice to Process, exit.');
1801 end if;
1802
1803 ----------------------------------------------------------------
1804 -- Calling BIS API to record the range we collect. Only do this
1805 -- when we have a successful collection
1806 ----------------------------------------------------------------
1807 BIS_COLLECTION_UTILITIES.wrapup(
1808 p_status => TRUE,
1809 p_period_from => g_start_date,
1810 p_period_to => g_end_date);
1811
1812 g_retcode := 0;
1813
1814
1815 RETURN;
1816 END IF;
1817 END IF;
1818 ----------------------------------------------------------------
1819 -- After the new invoices are identified, we need to call the
1820 -- CCID API to make sure that the CCID dimension is up to date.
1821 -- The reason we call this API after we have identified the
1822 -- new invoices instead of calling this API at the beginning of
1823 -- the programs is because that it is possible that after we
1824 -- called the API, new CCIDs can be created, and
1825 -- then we will pull this new invoice in the POPULATE_INV_ID_TEMP
1826 -- If CCID dimension is not up to date, VERIFY_CCID_UP_TO_DATE
1827 -- will also call the CCID Dimension load program to update
1828 -- CCID dimension.
1829 ----------------------------------------------------------------
1830 g_phase := 'Verifying if CCID Dimension is up to date';
1831 if g_debug_flag = 'Y' then
1832 FII_UTIL.put_line(g_phase);
1833 end if;
1834
1835 VERIFY_CCID_UP_TO_DATE;
1836
1837 IF p_program_type = 'L' THEN
1838
1839 IF g_oper_imp_prof_flag = 'N' THEN
1840 g_state := 'Truncating AP_DBI_LOG.';
1841 EXECUTE IMMEDIATE('TRUNCATE TABLE ' || g_ap_schema || '.AP_DBI_LOG');
1842 END IF;
1843
1844
1845 INSERT_INTO_STG (NVL(p_parallel_query, 1),
1846 NVL(p_sort_area_size, 200000000),
1847 NVL(p_hash_area_size, 200000000));
1848 INSERT_RATES;
1849
1850 FND_STATS.GATHER_TABLE_STATS(OWNNAME => 'FII', TABNAME => 'FII_AP_INV_DIST_T', PERCENT=> 5);
1851
1852 ELSE
1853
1854 --------------------------------------------
1855 -- Register jobs
1856 --------------------------------------------
1857 if g_debug_flag = 'Y' then
1858 FII_UTIL.put_line(' ');
1859 FII_UTIL.put_line('Populating Jobs Table');
1860 FII_UTIL.put_timestamp;
1861 end if;
1862 REGISTER_JOBS;
1863 COMMIT;
1864 --------------------------------------------------------
1865 -- Launch worker
1866 --------------------------------------------------------
1867 if g_debug_flag = 'Y' then
1868 FII_UTIL.put_line('Launching Workers');
1869 end if;
1870 FOR i IN 1..p_no_worker
1871 LOOP /* p_no_worker is the parameter user submitted
1872 to specify how many workers they want to
1873 submit */
1874 l_worker(i) := LAUNCH_WORKER(i);
1875 if g_debug_flag = 'Y' then
1876 FII_UTIL.put_line(' Worker '||i||' request id: '||l_worker(i));
1877 end if;
1878 END LOOP;
1879
1880 COMMIT;
1881
1882 --------------------------------------------
1883 -- Monitor workers
1884 -- ------------------------------------------
1885 g_state := 'Monitoring workers';
1886 DECLARE
1887 l_unassigned_cnt NUMBER := 0;
1888 l_completed_cnt NUMBER := 0;
1889 l_wip_cnt NUMBER := 0;
1890 l_failed_cnt NUMBER := 0;
1891 l_tot_cnt NUMBER := 0;
1892 l_last_unassigned_cnt NUMBER := 0;
1893 l_last_completed_cnt NUMBER := 0;
1894 l_last_wip_cnt NUMBER := 0;
1895 l_cycle NUMBER := 0;
1896 BEGIN LOOP
1897
1898 SELECT nvl(sum(decode(status,'UNASSIGNED',1,0)),0),
1899 nvl(sum(decode(status,'COMPLETED',1,0)),0),
1900 nvl(sum(decode(status,'IN PROCESS',1,0)),0),
1901 nvl(sum(decode(status,'FAILED',1,0)),0),
1902 count(*)
1903 INTO l_unassigned_cnt,
1904 l_completed_cnt,
1905 l_wip_cnt,
1906 l_failed_cnt,
1907 l_tot_cnt
1908 FROM FII_AP_SUM_WORK_JOBS;
1909
1910 if g_debug_flag = 'Y' then
1911 FII_UTIL.put_line('Job status - Unassigned:'||l_unassigned_cnt||
1912 ' In Process:'||l_wip_cnt||
1913 ' Completed:'||l_completed_cnt||
1914 ' Failed:'||l_failed_cnt);
1915 end if;
1916
1917 IF (l_failed_cnt > 0) THEN
1918 g_retcode := -2;
1919 g_errbuf := ' Error in Main Procedure: Message: At least one of the workers have errored out';
1920 RAISE g_procedure_failure;
1921
1922 END IF;
1923
1924 ----------------------------------------------
1925 -- IF the number of complete count equals to
1926 -- the total count, then that means all workers
1927 -- have completed. Then we can exit the loop
1928 ----------------------------------------------
1929 IF (l_tot_cnt = l_completed_cnt) THEN
1930 if g_debug_flag = 'Y' then
1931 FII_UTIL.put_line('Job status - Total: '|| l_tot_cnt);
1932 end if;
1933 EXIT;
1934 END IF;
1935
1936 -------------------------
1937 -- Detect infinite loops
1938 -------------------------
1939 IF (l_unassigned_cnt = l_last_unassigned_cnt AND
1940 l_completed_cnt = l_last_completed_cnt AND
1941 l_wip_cnt = l_last_wip_cnt) THEN
1942 l_cycle := l_cycle + 1;
1943 ELSE
1944 l_cycle := 1;
1945 END IF;
1946
1947 ----------------------------------------
1948 -- MAX_LOOP is a global variable you set.
1949 -- It represents the number of minutes
1950 -- you want to wait for each worker to
1951 -- complete. We can set it to 30 minutes
1952 -- for now
1953 ----------------------------------------
1954
1955 IF (l_cycle > MAX_LOOP) THEN
1956 g_retcode := -2;
1957 g_errbuf := ' Error in Main Procedure: Message: No progress have been made for '||MAX_LOOP||' minutes. Terminating.';
1958
1959 RAISE g_procedure_failure;
1960 END IF;
1961
1962 -------------------------
1963 -- Sleep 60 Seconds
1964 -------------------------
1965 dbms_lock.sleep(60);
1966
1967 l_last_unassigned_cnt := l_unassigned_cnt;
1968 l_last_completed_cnt := l_completed_cnt;
1969 l_last_wip_cnt := l_wip_cnt;
1970
1971 END LOOP;
1972
1973 if g_debug_flag = 'Y' then
1974 FII_UTIL.stop_timer;
1975 FII_UTIL.print_timer('Duration');
1976 end if;
1977 END;
1978
1979 END IF;
1980
1981 ----------------------------------------------
1982 -- Else, we are running in resume mode
1983 ----------------------------------------------
1984 ELSE
1985
1986 ----------------------------------------------------------
1987 -- This variable indicates that if exception occur, do
1988 -- we need to truncate the staging table.
1989 -- When running in resume mode, we do not want to truncate
1990 -- staging table
1991 ----------------------------------------------------------
1992 g_truncate_staging := 'N';
1993
1994 g_state := 'Updating records with missing primary rates in FII_AP_INV_DIST_T table';
1995 if g_debug_flag = 'Y' then
1996 FII_UTIL.put_line('');
1997 FII_UTIL.put_line('---------------------------------------------------------------------');
1998 FII_UTIL.put_line(g_state);
1999 FII_UTIL.start_timer;
2000 end if;
2001
2002 --modified by ilavenil to handle future dated transaction. The change is usage of least(...,sysdate)
2003 UPDATE FII_AP_INV_DIST_T stg
2004 SET stg.PRIM_CONVERSION_RATE = DECODE(stg.inv_currency_code, g_prim_currency, 1,
2005 fii_currency.get_global_rate_primary(stg.inv_currency_code,
2006 least(stg.account_date,sysdate)))
2007 WHERE stg.PRIM_CONVERSION_RATE < 0;
2008
2009 l_rowcount := SQL%ROWCOUNT;
2010 if g_debug_flag = 'Y' then
2011 FII_UTIL.put_line('');
2012 FII_UTIL.put_line('Updated ' || l_rowcount || ' records for primary conversion rate');
2013 FII_UTIL.stop_timer;
2014 FII_UTIL.print_timer('Duration');
2015
2016 g_state := 'Updating records with missing secondary rates in FII_AP_INV_DIST_T table';
2017 FII_UTIL.put_line('');
2018 FII_UTIL.put_line('---------------------------------------------------------------------');
2019 FII_UTIL.put_line(g_state);
2020 FII_UTIL.start_timer;
2021 end if;
2022
2023 --modified by ilavenil to handle future dated transaction. The change is usage of least(...,sysdate)
2024 UPDATE FII_AP_INV_DIST_T stg
2025 SET stg.SEC_CONVERSION_RATE = decode(stg.inv_currency_code, g_sec_currency, 1,
2026 fii_currency.get_global_rate_secondary(stg.inv_currency_code,
2027 least( stg.account_date,sysdate)))
2028 WHERE stg.SEC_CONVERSION_RATE < 0;
2029
2030 l_rowcount := SQL%ROWCOUNT;
2031 if g_debug_flag = 'Y' then
2032 FII_UTIL.put_line('');
2033 FII_UTIL.put_line('Updated ' || l_rowcount || ' records for secondary conversion rate');
2034 FII_UTIL.stop_timer;
2035 FII_UTIL.print_timer('Duration');
2036 end if;
2037
2038 COMMIT;
2039
2040 END IF; /* IF (g_fix_rates = 'N') */
2041
2042 -----------------------------------------------------------------------
2043 -- Checking to see if the missing rate records exists in the staging
2044 -- table. If yes, program will exit with warning status. All records
2045 -- would remain in staging table, no records would be loaded into
2046 -- summary table.
2047 -- If no, then all records will be loaded into summary table
2048 -----------------------------------------------------------------------
2049
2050 IF (VERIFY_MISSING_RATES(p_program_type) = -1) THEN
2051 ----------------------------------------------------------
2052 -- This variable indicates that if exception occur, do
2053 -- we need to truncate the staging table.
2054 -- If we reach this stage, that means all the child worker
2055 -- has completed inserting all records into staging table
2056 -- any exception occuring from now do not require staging
2057 -- table to be truncated
2058 ----------------------------------------------------------
2059 g_truncate_staging := 'N';
2060
2061 CLEAN_UP;
2062
2063 g_retcode := -1;
2064 Retcode := g_retcode;
2065
2066 g_state := 'Verifying Missing Rates';
2067 g_errbuf := fnd_message.get_string('FII', 'FII_MISS_EXCH_RATE_FOUND');
2068 RAISE G_MISSING_RATES;
2069
2070 -----------------------------------------------------------------------
2071 -- If there are no missing exchange rate records, then we will insert
2072 -- records from the staging table into the summary table
2073 -----------------------------------------------------------------------
2074 ELSE
2075 if g_debug_flag = 'Y' then
2076 FII_UTIL.start_timer;
2077 end if;
2078
2079 IF p_program_type = 'L' THEN
2080 ------------------------------------------------------------
2081 -- Initial Mode. This program will also update AP_DBI_LOG
2082 ------------------------------------------------------------
2083 INSERT_INTO_SUMMARY(p_parallel_query);
2084 ELSE
2085 ------------------------------------------------------------
2086 -- Incremental Mode. This program will also clean up AP_DBI_LOG
2087 ------------------------------------------------------------
2088 POPULATE_AP_BASE_SUM;
2089 END IF;
2090
2091 if g_debug_flag = 'Y' then
2092 FII_UTIL.stop_timer;
2093 end if;
2094
2095 -------------------------------------------------------------------
2096 -- After we have merged the records from the staging table into the
2097 -- base summary table, we can clean up the staging table when we
2098 -- call the CLEAN_UP procedure
2099 -------------------------------------------------------------------
2100 g_truncate_staging := 'Y';
2101
2102 CLEAN_UP;
2103
2104 ----------------------------------------------------------------
2105 -- Calling BIS API to record the range we collect. Only do this
2106 -- when we have a successful collection
2107 ----------------------------------------------------------------
2108 g_state := 'Calling BIS_COLLECTION_UTILITIES.wrapup';
2109 BIS_COLLECTION_UTILITIES.wrapup(
2110 p_status => TRUE,
2111 p_period_from => g_start_date,
2112 p_period_to => g_end_date);
2113
2114 g_retcode := 0;
2115
2116 END IF; /* IF (VERIFY_MISSING_RATES = -1) */
2117
2118 if g_debug_flag = 'Y' then
2119 FII_UTIL.put_line('return code is ' || retcode);
2120 end if;
2121 Retcode := g_retcode;
2122
2123
2124 EXCEPTION
2125 WHEN G_IMP_NOT_SET THEN
2126 retcode:=g_retcode;
2127 g_exception_msg := g_retcode || ':' || g_errbuf;
2128 FII_UTIL.put_line('Error occured while ' || g_state);
2129 FII_UTIL.put_line(g_exception_msg);
2130
2131 WHEN G_MISSING_RATES THEN
2132 retcode := g_retcode;
2133 g_exception_msg := g_retcode || ':' || g_errbuf;
2134 FII_UTIL.put_line('Error occured while ' || g_state);
2135 FII_UTIL.put_line(g_exception_msg);
2136
2137 WHEN OTHERS THEN
2138 g_errbuf:=g_errbuf;
2139 g_retcode:= -1;
2140 retcode:=g_retcode;
2141 g_exception_msg := g_retcode || ':' || g_errbuf;
2142 FII_UTIL.put_line('Error occured while ' || g_state);
2143 FII_UTIL.put_line(g_exception_msg);
2144
2145 ---------------------------------------------------------------
2146 -- Truncating the staging table so the next time the program
2147 -- is ran, the program will start from beginning. If we leave
2148 -- records in staging table, this program would run in resume
2149 -- mode. We purposely don't want to truncate FII_AP_INV_ID
2150 -- because we can use it for debugging purpose if the program
2151 -- error out. FII_AP_INV_ID always gets truncated at the start
2152 -- of the program.
2153 ---------------------------------------------------------------
2154 Truncate_table('FII_AP_INV_DIST_T');
2155
2156 Truncate_table('FII_AP_SUM_WORK_JOBS');
2157
2158 RAISE;
2159 END Collect;
2160
2161 --------------------------------------------------
2162 -- PROCEDURE WORKER
2163 ---------------------------------------------------
2164 PROCEDURE WORKER(
2165 Errbuf IN OUT NOCOPY VARCHAR2,
2166 Retcode IN OUT NOCOPY VARCHAR2,
2167 p_worker_no IN NUMBER) IS
2168
2169 -- -------------------------------------------
2170 -- Put any additional developer variables here
2171 -- -------------------------------------------
2172 l_unassigned_cnt NUMBER := 0;
2173 l_failed_cnt NUMBER := 0;
2174 l_curr_unasgn_cnt NUMBER := 0;
2175 l_curr_comp_cnt NUMBER := 0;
2176 l_curr_tot_cnt NUMBER := 0;
2177 l_count NUMBER;
2178 l_start_range NUMBER;
2179 l_end_range NUMBER;
2180 l_last_start_date DATE;
2181 l_last_end_date DATE;
2182 l_last_period_from DATE;
2183 l_last_period_to DATE;
2184 BEGIN
2185 Errbuf :=NULL;
2186 Retcode:=0;
2187
2188 -- -----------------------------------------------
2189 -- Set up directory structure for child process
2190 -- because child process do not call setup routine
2191 -- from EDWCORE
2192 -- -----------------------------------------------
2193 CHILD_SETUP('FII_AP_INV_DISTRIBUTIONS_B_SUBWORKER'||p_worker_no);
2194
2195 BIS_COLLECTION_UTILITIES.get_last_refresh_dates('FII_AP_INV_DISTRIBUTIONS_B_L', l_last_start_date,
2196 l_last_end_date, l_last_period_from,
2197 l_last_period_to);
2198 g_start_date := nvl(l_last_period_from, bis_common_parameters.get_global_start_date);
2199
2200
2201 if g_debug_flag = 'Y' then
2202 FII_UTIL.put_line(' ');
2203 FII_UTIL.put_timestamp;
2204 FII_UTIL.put_line('Worker '||p_worker_no||' Starting');
2205 end if;
2206
2207 -- ------------------------------------------
2208 -- Initalization
2209 -- ------------------------------------------
2210
2211 if g_debug_flag = 'Y' then
2212 FII_UTIL.put_line(' ');
2213 FII_UTIL.put_line('Initialization');
2214 end if;
2215 INIT;
2216
2217 --g_worker_num := p_worker_no;
2218
2219 -- ------------------------------------------
2220 -- Loop thru job list
2221 -- -----------------------------------------
2222 g_state := 'Loop thru job list';
2223 LOOP
2224
2225 SELECT nvl(sum(decode(status,'UNASSIGNED',1,0)),0),
2226 nvl(sum(decode(status,'FAILED',1,0)),0),
2227 nvl(sum(decode(status,'UNASSIGNED',1, 0)),0),
2228 nvl(sum(decode(status,'COMPLETED', 1, 0)),0),
2229 count(*)
2230 INTO l_unassigned_cnt,
2231 l_failed_cnt,
2232 l_curr_unasgn_cnt,
2233 l_curr_comp_cnt,
2234 l_curr_tot_cnt
2235 FROM FII_AP_SUM_WORK_JOBS;
2236
2237 IF (l_failed_cnt > 0) THEN
2238 if g_debug_flag = 'Y' then
2239 FII_UTIL.put_line('');
2240 FII_UTIL.put_line('Another worker have errored out. Stop processing.');
2241 end if;
2242 EXIT;
2243 ELSIF (l_unassigned_cnt = 0) THEN
2244 if g_debug_flag = 'Y' then
2245 FII_UTIL.put_line('');
2246 FII_UTIL.put_line('No more jobs left. Terminating.');
2247 end if;
2248 EXIT;
2249 ELSIF (l_curr_comp_cnt = l_curr_tot_cnt) THEN
2250 if g_debug_flag = 'Y' then
2251 FII_UTIL.put_line('');
2252 FII_UTIL.put_line('All jobs completed, no more job. Terminating');
2253 end if;
2254 EXIT;
2255 ELSIF (l_curr_unasgn_cnt > 0) THEN
2256 UPDATE FII_AP_SUM_WORK_JOBS
2257 SET status = 'IN PROCESS',
2258 worker_number = p_worker_no
2259 WHERE status = 'UNASSIGNED'
2260 AND rownum < 2;
2261
2262 l_count := sql%rowcount;
2263 END IF;
2264 COMMIT;
2265
2266 -- -----------------------------------
2267 -- There could be rare situations where
2268 -- between Section 30 and Section 50
2269 -- the unassigned job gets taken by
2270 -- another worker. So, if unassigned
2271 -- job no longer exist. Do nothing.
2272 -- -----------------------------------
2273 IF (l_count > 0) THEN
2274 BEGIN
2275 SELECT start_range,
2276 end_range
2277 INTO l_start_range,
2278 l_end_range
2279 FROM FII_AP_SUM_WORK_JOBS jobs
2280 WHERE jobs.worker_number = p_worker_no
2281 AND jobs.status = 'IN PROCESS';
2282
2283 ---------------------------------------------------------
2284 --Do summarization using the start_range and end_range
2285 ---------------------------------------------------------
2286 if g_debug_flag = 'Y' then
2287 FII_UTIL.start_timer;
2288 end if;
2289 POPULATE_AP_SUM_STG (l_start_range, l_end_range);
2290 if g_debug_flag = 'Y' then
2291 FII_UTIL.stop_timer;
2292 FII_UTIL.print_timer('Duration');
2293 end if;
2294
2295 UPDATE FII_AP_SUM_WORK_JOBS jobs
2296 SET jobs.status = 'COMPLETED'
2297 WHERE jobs.status = 'IN PROCESS'
2298 AND jobs.worker_number = p_worker_no;
2299
2300 COMMIT;
2301
2302 EXCEPTION
2303 WHEN OTHERS THEN
2304 g_retcode := -1;
2305
2306 UPDATE FII_AP_SUM_WORK_JOBS
2307 SET status = 'FAILED'
2308 WHERE worker_number = p_worker_no
2309 AND status = 'IN PROCESS';
2310
2311 COMMIT;
2312 Raise;
2313 END;
2314 END IF; /* IF (l_count > 0) */
2315 END LOOP;
2316
2317 EXCEPTION
2318 WHEN OTHERS THEN
2319 Retcode:= -1;
2320 Errbuf := '
2321 ---------------------------------
2322 Error in Procedure: WORKER
2323 Message: '||sqlerrm;
2324 FII_UTIL.put_line(Errbuf);
2325
2326 -------------------------------------------
2327 -- Update the WORKER_JOBS table to indicate
2328 -- this job has failed
2329 -------------------------------------------
2330 UPDATE FII_AP_SUM_WORK_JOBS
2331 SET status = 'FAILED'
2332 WHERE worker_number = p_worker_no
2333 AND status = 'IN PROCESS';
2334
2335 COMMIT;
2336
2337
2338 END WORKER;
2339 END FII_AP_INV_DISTRIBUTIONS_B_C;