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