[Home] [Help]
PACKAGE BODY: APPS.FII_AR_REVENUE_B_C
Source
1 PACKAGE BODY FII_AR_REVENUE_B_C AS
2 /* $Header: FIIAR18B.pls 120.96 2007/03/21 19:45:38 juding ship $ */
3
4 g_errbuf VARCHAR2(2000) := NULL;
5 g_retcode VARCHAR2(20) := NULL;
6 g_section VARCHAR2(20) := NULL;
7 g_sob_id NUMBER := NULL;
8 g_gl_from_date DATE;
9 g_gl_to_date DATE;
10 g_lud_from_date DATE := NULL;
11 g_lud_to_date DATE := NULL;
12 g_ccid_not_prepared BOOLEAN := TRUE;
13 g_fii_schema VARCHAR2(30);
14 g_tablespace VARCHAR2(30);
15 g_instance_code VARCHAR2(30);
16 g_mau_prim NUMBER;
17 g_mau_sec NUMBER;
18 g_worker_num NUMBER;
19 g_resume_flag varchar2(1);
20 g_rev_acct_changed BOOLEAN;
21 g_truncate_staging varchar2(1) := 'N';
22 g_truncate_id varchar2(1) := 'N';
23 g_fii_user_id NUMBER(15);
24 g_fii_login_id NUMBER(15);
25 g_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
26 g_global_start_date DATE := NULL;
27 -- haritha
28 g_program_type VARCHAR2(1);
29
30 ONE_SECOND CONSTANT NUMBER := 0.000011574; -- 1 second
31 INTERVAL CONSTANT NUMBER := 10; -- 10 days
32 MAX_LOOP CONSTANT NUMBER := 180; -- 180 loops = 180 minutes
33 LAST_PHASE CONSTANT NUMBER := 4;
34
35 G_TABLE_NOT_EXIST EXCEPTION;
36 G_PROCEDURE_FAILURE EXCEPTION;
37 G_NO_CHILD_PROCESS EXCEPTION;
38 G_CCID_FAILED EXCEPTION;
39 PRAGMA EXCEPTION_INIT(G_TABLE_NOT_EXIST, -942);
40 G_LOGIN_INFO_NOT_AVABLE EXCEPTION;
41
42 g_usage_code CONSTANT VARCHAR2(10) := 'DBI';
43 g_table_name VARCHAR2(50) := 'FII_AR_REVENUE_B';
44
45 g_program_code_R CONSTANT VARCHAR2(30) := 'RECEIVABLES REVENUE';
46 g_program_code_DR CONSTANT VARCHAR2(30) := 'RECEIVABLES DEF REVENUE';
47
48 g_non_upgraded_ledgers BOOLEAN := FALSE;
49
50 -- ---------------------------------
51 -- PRIVATE PROCEDURES AND FUNCTIONS
52 -- ---------------------------------
53
54 -- ---------------------------------------------------------------
55 -- PROCEDURE CHECK_XLA_CONVERSION_STATUS
56 -- ---------------------------------------------------------------
57 PROCEDURE CHECK_XLA_CONVERSION_STATUS IS
58 /*
59 -- FA
60 CURSOR c_non_upgraded_ledgers IS
61 SELECT DISTINCT
62 s.ledger_id,
63 s.name
64 FROM gl_period_statuses ps,
65 gl_ledgers_public_v s,
66 fa_deprn_periods dp,
67 fa_book_controls bc,
68 (SELECT DISTINCT slga.ledger_id
69 FROM fii_slg_assignments slga,
70 fii_source_ledger_groups fslg
71 WHERE slga.source_ledger_group_id = fslg.source_ledger_group_id
72 AND fslg.usage_code = g_usage_code) fset
73 WHERE s.ledger_id = fset.ledger_id
74 AND ps.application_id = 101
75 AND ps.set_of_books_id = fset.ledger_id
76 AND ps.end_date >= g_global_Start_Date
77 AND bc.set_of_books_id = fset.ledger_id
78 AND dp.book_type_code = bc.book_type_code
79 AND dp.period_name = ps.period_name
80 AND nvl(dp.xla_conversion_status, 'UA') <> 'UA';
81
82 -- AP
83 CURSOR c_non_upgraded_ledgers IS
84 SELECT DISTINCT
85 s.ledger_id,
86 s.name
87 FROM gl_period_statuses ps,
88 gl_ledgers_public_v s,
89 (SELECT DISTINCT slga.ledger_id
90 FROM fii_slg_assignments slga,
91 fii_source_ledger_groups fslg
92 WHERE slga.source_ledger_group_id = fslg.source_ledger_group_id
93 AND fslg.usage_code =g_usage_code) fset
94 WHERE s.ledger_id = fset.ledger_id
95 AND ps.application_id = 200
96 AND ps.set_of_books_id = fset.ledger_id
97 AND ps.end_date >= p_start_date
98 AND ps.migration_status_code <> 'U';
99 */
100 -- AR
101 CURSOR c_non_upgraded_ledgers IS
102 SELECT DISTINCT
103 s.ledger_id,
104 s.name
105 FROM gl_period_statuses ps,
106 gl_ledgers_public_v s,
107 (SELECT DISTINCT slga.ledger_id
108 FROM fii_slg_assignments slga,
109 fii_source_ledger_groups fslg
110 WHERE slga.source_ledger_group_id = fslg.source_ledger_group_id
111 AND fslg.usage_code = g_usage_code) fset
112 WHERE s.ledger_id = fset.ledger_id
113 AND ps.application_id = 222
114 AND ps.set_of_books_id = fset.ledger_id
115 AND ps.end_date >= g_global_Start_Date
116 AND ps.migration_status_code <> 'U';
117
118 BEGIN
119
120 if g_debug_flag = 'Y' then
121 FII_UTIL.put_line('Calling procedure: CHECK_XLA_CONVERSION_STATUS');
122 FII_UTIL.put_line('');
123 end if;
124
125 FOR ledger_record in c_non_upgraded_ledgers LOOP
126
127 g_non_upgraded_ledgers := TRUE;
128
129 FII_MESSAGE.write_log(
130 msg_name => 'FII_XLA_NON_UPGRADED_LEDGER',
131 token_num => 3,
132 t1 => 'PRODUCT',
133 v1 => 'Receivables',
134 t2 => 'LEDGER',
135 v2 => ledger_record.name,
136 t3 => 'START_DATE',
137 v3 => g_global_Start_Date);
138
139 END LOOP;
140
141 EXCEPTION
142
143 WHEN OTHERS THEN
144
145 g_retcode := -1;
146 g_errbuf := '
147 ---------------------------------
148 Error in Procedure: CHECK_XLA_CONVERSION_STATUS
149 Message: '||sqlerrm;
150 FII_UTIL.put_line(g_errbuf);
151 raise g_procedure_failure;
152
153 END CHECK_XLA_CONVERSION_STATUS;
154
155 ---------------------------------------------------
156 -- PROCEDURE DROP_TABLE
157 ---------------------------------------------------
158 PROCEDURE drop_table (p_table_name IN VARCHAR2) IS
159 l_stmt VARCHAR2(400);
160
161 BEGIN
162
163 l_stmt:='DROP TABLE '||g_fii_schema||'.'|| p_table_name;
164
165 if g_debug_flag = 'Y' then
166 fii_util.put_line('');
167 fii_util.put_line('Dropping temp table '||p_table_name);
168 fii_util.put_line(l_stmt);
169 end if;
170
171 EXECUTE IMMEDIATE l_stmt;
172
173 EXCEPTION
174
175 WHEN g_table_not_exist THEN
176 NULL; -- Oracle 942, table does not exist, no actions
177
178 WHEN OTHERS THEN
179 g_retcode := -2;
180 g_errbuf := '
181 ---------------------------------
182 Error in Procedure: DROP_TABLE
183 Message: ' || sqlerrm;
184 RAISE g_procedure_failure;
185
186 END drop_table;
187
188
189 ---------------------------------------------------
190 -- PROCEDURE TRUNCATE_TABLE
191 ---------------------------------------------------
192 PROCEDURE truncate_table (p_table_name IN VARCHAR2,
193 p_partition IN VARCHAR2 DEFAULT 'ALL') IS
194 l_stmt VARCHAR2(400);
195
196 BEGIN
197 -- DEBUG
198 -- return;
199 IF (p_partition = 'ALL') THEN
200 l_stmt := 'truncate table '||g_fii_schema||'.'||p_table_name;
201 ELSE
202 l_stmt := 'alter table '||g_fii_schema||'.'||p_table_name||
203 ' truncate partition '||p_partition;
204 END IF;
205
206 if g_debug_flag = 'Y' then
207 fii_util.put_line(' ');
208 fii_util.put_line(l_stmt);
209 end if;
210 execute immediate l_stmt;
211
212 EXCEPTION
213
214 WHEN g_table_not_exist THEN
215 NULL; -- Oracle 942, table does not exist, no actions
216
217 WHEN OTHERS THEN
218 g_retcode := -2;
219 g_errbuf := '
220 ---------------------------------
221 Error in Procedure: TRUNCATE_TABLE
222 Message: '||sqlerrm;
223 raise g_procedure_failure;
224 END truncate_table;
225
226 -----------------------------------------------------------------------
227 -- PROCEDURE CLEAN_UP
228 -----------------------------------------------------------------------
229 PROCEDURE Clean_Up IS
230
231
232 BEGIN
233 -- DEBUG
234 -- return;
235 if g_debug_flag = 'Y' then
236 FII_UTIL.put_line('Running procedure CLEAN_UP');
237 FII_UTIL.put_line('');
238 -- FII_UTIL.put_line('Truncating table FII_AR_REVENUE_JOBS');
239 end if;
240 truncate_table('fii_ar_revenue_jobs');
241
242 IF (g_truncate_id = 'Y') THEN
243 -- if g_debug_flag = 'Y' then
244 -- FII_UTIL.put_line('Truncating table FII_AR_REVENUE_ID');
245 -- end if;
246 truncate_table('fii_ar_revenue_id');
247 END IF;
248
249 IF (g_truncate_staging = 'Y') THEN
250 -- if g_debug_flag = 'Y' then
251 -- FII_UTIL.put_line('Truncating table FII_AR_REVENUE_STG');
252 -- end if;
253 truncate_table('fii_ar_revenue_stg');
254
255 END IF;
256
257
258 EXCEPTION
259 WHEN OTHERS Then
260 g_retcode:=-1;
261 g_errbuf := '
262 ---------------------------------
263 Error in Procedure: Clean_Up
264 Message: ' || sqlerrm;
265 RAISE g_procedure_failure;
266
267 END Clean_up;
268
269
270 ------------------------------------------------------
271 -- PROCEDURE Init
272 ------------------------------------------------------
273 PROCEDURE Init IS
274 l_status VARCHAR2(30);
275 l_industry VARCHAR2(30);
276 l_stmt VARCHAR2(50);
277 BEGIN
278
279 -----------------------------------------------
280 -- Do the necessary setups for logging and output
281 -----------------------------------------------
282 g_section := 'Section 20';
283
284 -- --------------------------------------------------------
285 -- Find the schema owner and tablespace
286 -- FII_AR_REVENUE_B is using
287 -- --------------------------------------------------------
288 g_section := 'Section 30';
289 IF(FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, g_fii_schema))
290 THEN NULL;
291 END IF;
292
293 g_section := 'Section 40';
294 -- Bug 4942753: Changed to select from dba_tables instead of all_tables
295 SELECT tablespace_name
296 INTO g_tablespace
297 FROM dba_tables
298 WHERE table_name = g_table_name
299 AND owner = g_fii_schema;
300
301 g_section := 'Section 50';
302 ------------------------------------------------------
303 -- get minimum accountable unit of the global currency
304 ------------------------------------------------------
305 g_mau_prim := NVL(FII_CURRENCY.GET_MAU_PRIMARY, 0.01 );
306 g_mau_sec := NVL(FII_CURRENCY.GET_MAU_SECONDARY, 0.01 );
307
308 g_section := 'Section 60';
309
310 g_fii_user_id := FND_GLOBAL.User_Id;
311 g_fii_login_id := FND_GLOBAL.Login_Id;
312
313 IF (g_fii_user_id IS NULL OR g_fii_login_id IS NULL) THEN
314 RAISE G_LOGIN_INFO_NOT_AVABLE;
315 END IF;
316
317 if g_debug_flag = 'Y' then
318 FII_UTIL.put_line('User ID: ' || g_fii_user_id || ' Login ID: ' || g_fii_login_id);
319 end if;
320
321 EXCEPTION
322 WHEN G_LOGIN_INFO_NOT_AVABLE THEN
323 FII_UTIL.put_line('Procedure INIT. Can not get User ID and/or Login ID, therefore program terminated.');
324 g_retcode := -1;
325 RAISE;
326
327 WHEN OTHERS THEN
328 g_retcode := -2;
329 g_errbuf := '
330 ---------------------------------
331 Error in Procedure: INIT
332 Section: '||g_section||'
333 Message: '||sqlerrm;
334 raise g_procedure_failure;
335
336 END Init;
337
338 -----------------------------------------------------------
339 -- PROCEDURE UNIQUE_CONST_RECORDS
340 -- this procedure creates temp table fii_ar_uni_con_rec to stores
341 -- the records which violate the
342 -- unique constraint condition on FII_AR_REVENUE_B.
343 -- The records in the table fii_ar_uni_con_rec may be investigated
344 -- to identify the reason for unique const violation.
345 -- The temp table fii_ar_uni_con_rec is dropped at the begining
346 -- of the subsequent Load / Update programs.
347 -----------------------------------------------------------
348
349 PROCEDURE UNIQUE_CONST_RECORDS IS
350 l_stmt VARCHAR2(400);
351 l_count NUMBER;
352
353
354 BEGIN
355
356
357 l_stmt:='
358 CREATE TABLE fii_ar_uni_con_rec
359 NOLOGGING storage (initial 4K next 16K MAXEXTENTS UNLIMITED) as
360 SELECT * FROM FII_AR_REVENUE_STG b
361 WHERE b.revenue_pk in
362 (SELECT b2.revenue_pk FROM FII_AR_REVENUE_STG b2
363 GROUP BY b2.revenue_pk
364 HAVING count(*)>1) ';
365
366 fii_util.put_line('');
367 fii_util.put_line('There are duplicate records in the staging table.
368 This needs to be fixed before inserting records in FII_AR_REVENUE_B.
369 Created temp table fii_ar_uni_con_rec which stores
370 records that violate unique constraint on FII_AR_REVENUE_B. Please investigate
371 this table for duplicate records.');
372 fii_util.put_line('');
373
374 if g_debug_flag = 'Y' then
375 fii_util.put_line(l_stmt);
376 fii_util.start_timer;
377 end if;
378
379 EXECUTE IMMEDIATE l_stmt;
380
381 if g_debug_flag = 'Y' then
382 fii_util.put_line(' Processed '||SQL%ROWCOUNT||' rows');
383 fii_util.stop_timer;
384 fii_util.print_timer('Duration');
385 end if;
386
387 COMMIT;
388
389
390 EXCEPTION
391
392 WHEN OTHERS THEN
393 g_retcode := -2;
394 g_errbuf := '
395 ---------------------------------
396 Error in Procedure: UNIQUE_CONST_RECORDS
397 Message: ' || sqlerrm;
398 RAISE g_procedure_failure;
399
400 END UNIQUE_CONST_RECORDS;
401
402 -----------------------------------------------------------
403 -- FUNCTION POPULATE_STG
404 -- inserting records into staging table
405 -----------------------------------------------------------
406 FUNCTION POPULATE_STG (
407 p_view_type_id NUMBER,
408 p_job_num NUMBER) RETURN NUMBER IS
409 l_stmt VARCHAR2(2000);
410 l_row_count NUMBER;
411 v_date DATE;
412 v_date1 DATE;
413
414 BEGIN
415
416 if g_debug_flag = 'Y' then
417 fii_util.put_line(' ');
418 fii_util.start_timer;
419 end if;
420
421
422 IF (p_view_type_id = 4) THEN
423
424 INSERT INTO FII_AR_REVENUE_STG (
425 REVENUE_PK,
426 GL_DATE_ID,
427 GL_DATE,
428 INVENTORY_ITEM_ID,
429 OPERATING_UNIT_ID,
430 COMPANY_ID,
431 COST_CENTER_ID,
432 INVOICE_NUMBER,
433 INVOICE_DATE,
434 ORDER_LINE_ID,
435 BILL_TO_PARTY_ID,
436 FUNCTIONAL_CURRENCY,
437 TRANSACTION_CURRENCY,
438 LEDGER_ID,
439 INVOICE_ID,
440 AMOUNT_T,
441 AMOUNT_B,
442 EXCHANGE_DATE,
443 TOP_MODEL_ITEM_ID,
444 ORGANIZATION_ID,
445 item_organization_id,
446 om_product_revenue_flag,
447 TRANSACTION_CLASS,
448 FIN_CATEGORY_ID,
449 INVOICE_LINE_ID,
450 SALES_CHANNEL,
451 ORDER_NUMBER,
452 POSTED_FLAG,
453 PRIM_CONVERSION_RATE,
454 SEC_CONVERSION_RATE,
455 PROD_CATEGORY_ID,
456 CHART_OF_ACCOUNTS_ID,
457 FIN_CAT_TYPE_CODE,
458 REV_BOOKED_DATE,
459 CHILD_ORDER_LINE_ID)
460 WITH ACCNT_CLASS AS (SELECT XAD.Ledger_ID,
461 XACA.Accounting_Class_Code,
462 decode(XAD.Program_Code,
463 g_program_code_R, 'R',
464 g_program_code_DR, 'DR',
465 NULL) Fin_Cat_Type_Code
466 FROM XLA_Assignment_Defns_B XAD,
467 XLA_Acct_Class_Assgns XACA
468 WHERE XAD.Program_Code in (g_program_code_R,
469 g_program_code_DR)
470 AND XAD.Enabled_Flag = 'Y'
471 AND XAD.Program_Code = XACA.Program_Code
472 AND XAD.Assignment_Code = XACA.Assignment_Code)
473 SELECT /*+ NO_EXPAND */
474 DISTINCT
475 'AR-'||ctl.customer_trx_line_id||'-'||
476 to_char(trunc(aeh.accounting_date),'YYYY/MM/DD')||'-'||
477 ael.code_combination_id REVENUE_PK,
478 to_number(to_char(trunc(aeh.accounting_date),'J')) GL_DATE_ID,
479 --Bug 3455965: use TRUNC for date
480 TRUNC(aeh.accounting_date) GL_DATE,
481 -- ctl_parent.inventory_item_id INVENTORY_ITEM_ID,
482 CASE
483 when (ctl_parent.line_type like 'LINE'
484 and ctl_parent.inventory_item_id = sl_child.inventory_item_id
485 and sl_child.ship_from_org_id IS NOT NULL )
486 THEN ctl_parent.inventory_item_id
487 when (ctl_parent.line_type like 'LINE' and ctl_parent.WAREHOUSE_ID IS NOT NULL)
488 THEN ctl_parent.inventory_item_id
489 ELSE
490 to_number(NULL)
491
492 END INVENTORY_ITEM_ID,
493 ct.org_id OPERATING_UNIT_ID,
494 ccdim.company_id COMPANY_ID,
495 ccdim.cost_center_id COST_CENTER_ID,
496 substrb(ct.trx_number,1,30) INVOICE_NUMBER,
497 trunc(ct.trx_date) INVOICE_DATE,
498 DECODE(ctl_parent.line_type, 'LINE',
499 DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
500 DECODE(ctl_parent.interface_line_context,
501 'ORDER ENTRY', sl_parent.line_id,
502 'INTERCOMPANY', sl_parent.line_id, to_number(NULL)),
503 to_number(NULL)),
504 to_number(NULL)) ORDER_LINE_ID,
505 bill_acct.party_id BILL_TO_PARTY_ID,
506 sob.currency_code FUNCTIONAL_CURRENCY,
507 nvl(ct.invoice_currency_code,sob.currency_code) TRANSACTION_CURRENCY,
508 ct.set_of_books_id SET_OF_BOOKS_ID,
509 ct.customer_trx_id INVOICE_ID,
510 sum( NVL(lnk.unrounded_entered_cr,0) -
511 NVL(lnk.unrounded_entered_dr,0) ) AMOUNT_T,
512 sum( NVL(lnk.unrounded_accounted_cr,0) -
513 NVL(lnk.unrounded_accounted_dr,0) ) AMOUNT_B,
514 trunc(aeh.accounting_date) EXCHANGE_DATE,
515 /* DECODE(ctl_parent.line_type, 'LINE',
516 DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
517 DECODE(ctl_parent.interface_line_context,
518 'ORDER ENTRY', sl_parent.inventory_item_id,
519 'INTERCOMPANY', sl_parent.inventory_item_id, to_number(NULL)),
520 to_number(NULL)),
521 to_number(NULL)) TOP_MODEL_ITEM_ID,
522 DECODE(ctl_parent.line_type, 'LINE',
523 DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
524 DECODE(ctl_parent.interface_line_context,
525 'ORDER ENTRY', sl_parent.ship_from_org_id,
526 'INTERCOMPANY', sl_parent.ship_from_org_id, to_number(NULL)),
527 to_number(NULL)),
528 to_number(NULL)) ORGANIZATION_ID,
529 DECODE(ctl_parent.line_type, 'LINE',
530 DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
531 DECODE(ctl_parent.interface_line_context,
532 'ORDER ENTRY', sl_child.ship_from_org_id,
533 'INTERCOMPANY', sl_child.ship_from_org_id, to_number(NULL)),
534 to_number(NULL)),
535 to_number(NULL)) item_organization_id, */
536 CASE
537 when ( ctl_parent.line_type like 'LINE'
538 and ctl_parent.inventory_item_id = sl_child.inventory_item_id
539 and sl_parent.ship_from_org_id IS NOT NULL)
540 THEN sl_parent.inventory_item_id
541 ELSE
542 to_number(NULL)
543 END TOP_MODEL_ITEM_ID,
544 DECODE(ctl_parent.line_type, 'LINE',
545 DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
546 sl_parent.ship_from_org_id, to_number(null)),
547 to_number(NULL) ) ORGANIZATION_ID,
548 DECODE(ctl_parent.line_type, 'LINE',
549 DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
550 sl_child.ship_from_org_id, ctl_parent.WAREHOUSE_ID ),
551 to_number(NULL)) item_organization_id,
552 decode(ctl_parent.interface_line_context, 'ORDER ENTRY',
553 decode(nvl( sl_child.item_type_code, 'X' ), 'SERVICE',
554 'N', 'Y'),
555 'N') om_product_revenue_flag,
556 decode(ctt.type,'GUAR','GUR',substrb(ctt.type,1,3)) TRANSACTION_CLASS,
557 ccdim.natural_account_id FIN_CATEGORY_ID,
558 ctl.customer_trx_line_id INVOICE_LINE_ID,
559 nvl(substrb(sh.sales_channel_code,1,30), '-1') SALES_CHANNEL,
560 substrb( DECODE(ctl_parent.interface_line_context,
561 'ORDER ENTRY',ctl_parent.interface_line_attribute1,
562 'INTERCOMPANY',ctl_parent.interface_line_attribute1,
563 ctl_parent.sales_order),1,30) ORDER_NUMBER,
564 'Y' POSTED_FLAG,
565 fii_currency.get_global_rate_primary(sob.currency_code,
566 trunc(least(trunc(aeh.accounting_date), sysdate))) PRIM_CONVERSION_RATE,
567 fii_currency.get_global_rate_secondary(sob.currency_code,
568 trunc(least(trunc(aeh.accounting_date), sysdate))) SEC_CONVERSION_RATE,
569 ccdim.prod_category_id PROD_CATEGORY_ID,
570 sob.chart_of_accounts_id CHART_OF_ACCOUNTS_ID,
571 -- ffcta.fin_cat_type_code FIN_CAT_TYPE_CODE,
572 AC.fin_cat_type_code FIN_CAT_TYPE_CODE,
573 decode(sh.booked_flag, 'Y', trunc(nvl(sl_child.order_firmed_date,
574 sh.booked_date)), to_date(null)) REV_BOOKED_DATE,
575 decode(ctl.interface_line_context, 'ORDER ENTRY',ctl.interface_line_attribute6,
576 null) CHILD_ORDER_LINE_ID
577 FROM
578 fii_ar_revenue_id fpk,
579 ra_customer_trx_lines_all ctl,
580 ra_customer_trx_all ct,
581 ra_cust_trx_types_all ctt,
582 ra_cust_trx_line_gl_dist_all ctlgd,
583 gl_code_combinations glcc,
584 fii_gl_ccid_dimensions ccdim,
585 fii_slg_assignments slga,
586 fii_source_ledger_groups fslg,
587 -- fii_fin_cat_type_assgns ffcta,
588 ra_customer_trx_lines_all ctl_parent,
589 gl_ledgers_public_v sob,
590 hz_cust_accounts bill_acct,
591 oe_order_lines_all sl_child,
592 oe_order_lines_all sl_parent,
593 oe_order_headers_all sh,
594 ACCNT_CLASS AC,
595 xla_ae_headers aeh,
596 xla_ae_lines ael,
597 xla_distribution_links lnk
598 WHERE fpk.view_type_id= 4
599 AND fpk.job_num = p_job_num
600 AND aeh.ae_header_id = fpk.primary_key1
601 AND aeh.application_id = 222
602 AND aeh.balance_type_code = 'A'
603 AND aeh.gl_transfer_status_code = 'Y'
604 AND ael.application_id = 222
605 AND aeh.ae_header_id = ael.ae_header_id
606 AND lnk.application_id = 222
607 AND ael.ae_header_id = lnk.ae_header_id
608 AND ael.ae_line_num = lnk.ae_line_num
609 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
610 AND lnk.source_distribution_id_num_1 = ctlgd.cust_trx_line_gl_dist_id
611 AND aeh.ledger_id = ctlgd.set_of_books_id
612 AND nvl(ctl.interface_line_context, 'xxx') <> 'PA INVOICES'
613 AND ct.customer_trx_id = ctl.customer_trx_id
614 AND ct.complete_flag = 'Y'
615 AND ctt.cust_trx_type_id(+) = ct.cust_trx_type_id
616 AND ctt.org_id (+) = ct.org_id
617 AND NVL(ctt.post_to_gl,'Y') = 'Y'
618 AND ctlgd.customer_trx_line_id = ctl.customer_trx_line_id
619 AND ctlgd.account_set_flag = 'N'
620 -- AND ctlgd.gl_posted_date IS NOT NULL
621 AND NVL(lnk.unrounded_entered_cr,0) - NVL(lnk.unrounded_entered_dr,0) <> 0
622 -- AND ctlgd.gl_date IS NOT NULL
623 AND glcc.code_combination_id = ael.code_combination_id
624 AND ccdim.code_combination_id = glcc.code_combination_id
625 AND ( slga.bal_seg_value_id = ccdim.company_id
626 OR slga.bal_seg_value_id = -1 )
627 AND slga.chart_of_accounts_id = ccdim.chart_of_accounts_id
628 AND slga.ledger_id = ctl.set_of_books_id
629 -- AND ffcta.fin_category_id = ccdim.natural_account_id
630 -- AND ffcta.fin_cat_type_code in ('R', 'DR')
631 AND ctl_parent.customer_trx_line_id =
632 nvl(ctl.previous_customer_trx_line_id,ctl.customer_trx_line_id)
633 AND sob.ledger_id = ct.set_of_books_id
634 AND bill_acct.cust_account_id(+) = ct.bill_to_customer_id
635 AND sl_child.line_id (+) =
636 case when (ctl_parent.interface_line_context in ('ORDER ENTRY', 'INTERCOMPANY')
637 and ltrim(ctl_parent.interface_line_attribute6, '0123456789') is NULL)
638 then to_number(ctl_parent.interface_line_attribute6)
639 else to_number(NULL) end
640 AND sh.header_id (+) = sl_child.header_id
641 AND sl_parent.line_id(+) = NVL(sl_child.top_model_line_id, sl_child.line_id)
642 AND slga.source_ledger_group_id = fslg.source_ledger_group_id
643 AND fslg.usage_code = g_usage_code
644 AND ael.accounting_class_code = AC.Accounting_Class_Code
645 AND ( aeh.ledger_id = AC.Ledger_ID OR AC.Ledger_ID IS NULL )
646 GROUP BY
647 ctl.customer_trx_line_id,
648 trunc(aeh.accounting_date),
649 ael.code_combination_id,
650 ctl_parent.inventory_item_id,
651 ct.org_id, --bug 3361888
652 ccdim.company_id,
653 ccdim.cost_center_id,
654 ct.trx_number,
655 ctl_parent.line_type,
656 ctl_parent.interface_line_context,
657 sl_child.item_type_code,
658 sl_child.inventory_item_id,
659 sl_parent.line_id,
660 bill_acct.party_id,
661 sob.currency_code,
662 ct.invoice_currency_code,
663 ct.set_of_books_id, --bug 3361888
664 ct.customer_trx_id,
665 ct.trx_date,
666 sysdate,
667 sl_parent.inventory_item_id,
668 sl_parent.ship_from_org_id,
669 sl_child.ship_from_org_id,
670 ctt.type,
671 ccdim.natural_account_id,
672 sh.sales_channel_code,
673 ctl_parent.interface_line_attribute1,
674 ctl_parent.sales_order,
675 ccdim.prod_category_id,
676 sob.chart_of_accounts_id,
677 -- ffcta.fin_cat_type_code,
678 AC.Fin_Cat_Type_Code,
679 ctl_parent.WAREHOUSE_ID,
680 decode(sh.booked_flag, 'Y', trunc(nvl(sl_child.order_firmed_date, sh.booked_date)), to_date(null)),
681 ctl.interface_line_context,
682 ctl.interface_line_attribute6;
683
684 ELSIF (p_view_type_id = 3) THEN
685
686 INSERT INTO FII_AR_REVENUE_STG (
687 REVENUE_PK,
688 GL_DATE_ID,
689 GL_DATE,
690 INVENTORY_ITEM_ID,
691 OPERATING_UNIT_ID,
692 --commented by ilavenil COMPANY_COST_CENTER_ORG_ID,
693 COMPANY_ID,
694 COST_CENTER_ID,
695 --above columns added by ilavenil
696 INVOICE_NUMBER,
697 INVOICE_DATE,
698 ORDER_LINE_ID,
699 BILL_TO_PARTY_ID,
700 FUNCTIONAL_CURRENCY,
701 TRANSACTION_CURRENCY,
702 LEDGER_ID,
703 INVOICE_ID,
704 AMOUNT_T,
705 AMOUNT_B,
706 EXCHANGE_DATE,
707 TOP_MODEL_ITEM_ID,
708 ORGANIZATION_ID,
709 item_organization_id,
710 om_product_revenue_flag,
711 TRANSACTION_CLASS,
712 FIN_CATEGORY_ID,
713 INVOICE_LINE_ID,
714 SALES_CHANNEL,
715 ORDER_NUMBER,
716 POSTED_FLAG,
717 PRIM_CONVERSION_RATE,
718 SEC_CONVERSION_RATE,
719 PROD_CATEGORY_ID,
720 CHART_OF_ACCOUNTS_ID,
721 FIN_CAT_TYPE_CODE,
722 REV_BOOKED_DATE,
723 CHILD_ORDER_LINE_ID)
724 WITH ACCNT_CLASS AS (SELECT XAD.Ledger_ID,
725 XACA.Accounting_Class_Code,
726 decode(XAD.Program_Code,
727 g_program_code_R, 'R',
728 g_program_code_DR, 'DR',
729 NULL) Fin_Cat_Type_Code
730 FROM XLA_Assignment_Defns_B XAD,
731 XLA_Acct_Class_Assgns XACA
732 WHERE XAD.Program_Code = g_program_code_R
733 AND XAD.Enabled_Flag = 'Y'
734 AND XAD.Program_Code = XACA.Program_Code
735 AND XAD.Assignment_Code = XACA.Assignment_Code)
736 SELECT /*+ NO_EXPAND */
737 DISTINCT
738 'ADJ-'||ad.line_id REVENUE_PK,
739 to_number(to_char(trunc(aeh.accounting_date),'J')) GL_DATE_ID,
740 --Bug 3455965: use TRUNC for date
741 TRUNC(aeh.accounting_date) GL_DATE,
742 -- ctl_parent.inventory_item_id INVENTORY_ITEM_ID,
743 /* CASE
744 when (ctl_parent.line_type like 'LINE'
745 and ctl_parent.inventory_item_id = sl_child.inventory_item_id
746 and sl_child.ship_from_org_id IS NOT NULL )
747 THEN ctl_parent.inventory_item_id
748 when (ctl_parent.line_type like 'LINE' and ctl_parent.WAREHOUSE_ID IS NOT NULL)
749 THEN ctl_parent.inventory_item_id
750 ELSE
751 to_number(NULL)
752
753 END INVENTORY_ITEM_ID, */
754 to_number (null) INVENTORY_ITEM_ID,
755 adj.org_id OPERATING_UNIT_ID,
756 --commented by ilavenil ccdim.company_cost_center_org_id COMPANY_COST_CENTER_ORG_ID,
757 ccdim.company_id COMPANY_ID,
758 ccdim.cost_center_id COST_CENTER_ID,
759 substrb(ct.trx_number,1,30) INVOICE_NUMBER,
760 trunc(ct.trx_date) INVOICE_DATE,
761 /* DECODE(ctl_parent.line_type, 'LINE',
762 DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
763 DECODE(ctl_parent.interface_line_context,
764 'ORDER ENTRY', sl_parent.line_id,
765 'INTERCOMPANY', sl_parent.line_id, to_number(NULL)),
766 to_number(NULL)),
767 to_number(NULL)) ORDER_LINE_ID, */
768 to_number(null) ORDER_LINE_ID,
769 bill_acct.party_id BILL_TO_PARTY_ID,
770 sob.currency_code FUNCTIONAL_CURRENCY,
771 nvl(ct.invoice_currency_code,sob.currency_code) TRANSACTION_CURRENCY,
772 aeh.ledger_id SET_OF_BOOKS_ID,
773 ct.customer_trx_id INVOICE_ID,
774 decode(gcc.account_type,'A',
775 sum( NVL(lnk.unrounded_entered_dr,0) -
776 NVL(lnk.unrounded_entered_cr,0) ),
777 sum( NVL(lnk.unrounded_entered_cr,0) -
778 NVL(lnk.unrounded_entered_dr,0) )
779 ) AMOUNT_T,
780 decode(gcc.account_type,'A',
781 sum( NVL(lnk.unrounded_accounted_dr,0) -
782 NVL(lnk.unrounded_accounted_cr,0) ),
783 sum( NVL(lnk.unrounded_accounted_cr,0) -
784 NVL(lnk.unrounded_accounted_dr,0) )
785 ) AMOUNT_B,
786 trunc(aeh.accounting_date) EXCHANGE_DATE,
787 /* DECODE(ctl_parent.line_type, 'LINE',
788 DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
789 DECODE(ctl_parent.interface_line_context,
790 'ORDER ENTRY', sl_parent.inventory_item_id,
791 'INTERCOMPANY', sl_parent.inventory_item_id, to_number(NULL)),
792 to_number(NULL)),
793 to_number(NULL)) TOP_MODEL_ITEM_ID,
794 DECODE(ctl_parent.line_type, 'LINE',
795 DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
796 DECODE(ctl_parent.interface_line_context,
797 'ORDER ENTRY', sl_parent.ship_from_org_id,
798 'INTERCOMPANY', sl_parent.ship_from_org_id, to_number(NULL)),
799 to_number(NULL)),
800 to_number(NULL)) ORGANIZATION_ID,
801 DECODE(ctl_parent.line_type, 'LINE',
802 DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
803 DECODE(ctl_parent.interface_line_context,
804 'ORDER ENTRY', sl_child.ship_from_org_id,
805 'INTERCOMPANY', sl_child.ship_from_org_id, to_number(NULL)),
806 to_number(NULL)),
807 to_number(NULL)) item_organization_id, */
808 /* CASE
809 when ( ctl_parent.line_type like 'LINE'
810 and ctl_parent.inventory_item_id = sl_child.inventory_item_id
811 and sl_parent.ship_from_org_id IS NOT NULL)
812 THEN sl_parent.inventory_item_id
813 ELSE
814 to_number(NULL)
815 END TOP_MODEL_ITEM_ID, */
816 to_number(null) TOP_MODEL_ITEM_ID,
817 /* DECODE(ctl_parent.line_type, 'LINE',
818 DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
819 sl_parent.ship_from_org_id, to_number(null)),
820 to_number(NULL) ) ORGANIZATION_ID, */
821 to_number(null) ORGANIZATION_ID,
822 /* DECODE(ctl_parent.line_type, 'LINE',
823 DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
824 sl_child.ship_from_org_id, ctl_parent.WAREHOUSE_ID ),
825 to_number(NULL)) item_organization_id, */
826 to_number(null) item_organization_id,
827 /* decode(ctl_parent.interface_line_context, 'ORDER ENTRY',
828 decode(nvl( sl_child.item_type_code, 'X' ), 'SERVICE',
829 'N', 'Y'),
830 'N') om_product_revenue_flag, */
831 'N' om_product_revenue_flag,
832 'ADJ' TRANSACTION_CLASS,
833 ccdim.natural_account_id FIN_CATEGORY_ID,
834 0 INVOICE_LINE_ID,
835 -- nvl(substrb(sh.sales_channel_code,1,30), '-1') SALES_CHANNEL,
836 '-1' SALES_CHANNEL,
837 /* substrb( DECODE(ctl_parent.interface_line_context,
838 'ORDER ENTRY',ctl_parent.interface_line_attribute1,
839 'INTERCOMPANY',ctl_parent.interface_line_attribute1,
840 ctl_parent.sales_order),1,30) ORDER_NUMBER, */
841 null ORDER_NUMBER,
842 'Y' POSTED_FLAG,
843 FII_CURRENCY.GET_GLOBAL_RATE_PRIMARY(sob.currency_code,
844 trunc(least(trunc(aeh.accounting_date), sysdate))) PRIM_CONVERSION_RATE,
845 FII_CURRENCY.GET_GLOBAL_RATE_SECONDARY(sob.currency_code,
846 trunc(least(trunc(aeh.accounting_date), sysdate))) SEC_CONVERSION_RATE,
847 ccdim.prod_category_id PROD_CATEGORY_ID,
848 sob.chart_of_accounts_id CHART_OF_ACCOUNTS_ID,
849 -- ffcta.fin_cat_type_code FIN_CAT_TYPE_CODE,
850 AC.fin_cat_type_code FIN_CAT_TYPE_CODE,
851 -- decode(sh.booked_flag, 'Y', trunc(sh.booked_date), to_date(null)) REV_BOOKED_DATE
852 to_date(null) REV_BOOKED_DATE,
853 null CHILD_ORDER_LINE_ID
854 FROM
855 fii_ar_revenue_id fpk,
856 ar_adjustments_all adj,
857 ar_distributions_all ad,
858 gl_code_combinations gcc,
859 fii_gl_ccid_dimensions ccdim,
860 fii_slg_assignments slga,
861 fii_source_ledger_groups fslg,
862 -- fii_fin_cat_type_assgns ffcta,
863 ra_customer_trx_all ct,
864 -- ra_customer_trx_lines_all ctl,
865 -- ra_customer_trx_lines_all ctl_parent,
866 -- oe_order_lines_all sl_child,
867 -- oe_order_headers_all sh,
868 -- oe_order_lines_all sl_parent,
869 gl_ledgers_public_v sob,
870 hz_cust_accounts bill_acct,
871 ACCNT_CLASS AC,
872 xla_ae_headers aeh,
873 xla_ae_lines ael,
874 xla_distribution_links lnk
875 WHERE fpk.view_type_id = 3
876 AND fpk.job_num = p_job_num
877 -- AND adj.adjustment_id = fpk.Primary_Key1
878 AND aeh.ae_header_id = fpk.primary_key1
879 AND aeh.application_id = 222
880 AND aeh.balance_type_code = 'A'
881 AND aeh.gl_transfer_status_code = 'Y'
882 AND ael.application_id = 222
883 AND aeh.ae_header_id = ael.ae_header_id
884 AND lnk.application_id = 222
885 AND ael.ae_header_id = lnk.ae_header_id
886 AND ael.ae_line_num = lnk.ae_line_num
887 AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
888 AND lnk.source_distribution_id_num_1 = ad.line_id
889 AND aeh.ledger_id = adj.set_of_books_id
890 AND NVL(adj.status, 'A') = 'A'
891 AND NVL(adj.postable,'Y') = 'Y'
892 -- AND adj.gl_posted_date IS NOT NULL
893 AND ad.source_id = adj.adjustment_id
894 AND ad.source_table = 'ADJ'
895 AND gcc.code_combination_id = ael.code_combination_id
896 AND ccdim.code_combination_id = gcc.code_combination_id
897 AND slga.chart_of_accounts_id = ccdim.chart_of_accounts_id
898 AND ( slga.bal_seg_value_id = ccdim.company_id
899 OR slga.bal_seg_value_id = -1 )
900 AND slga.ledger_id = aeh.ledger_id
901 -- AND ffcta.fin_category_id = ccdim.natural_account_id
902 -- AND ffcta.fin_cat_type_code = 'R'
903 AND ct.customer_trx_id = adj.customer_trx_id
904 AND nvl(ct.org_id, -999) = nvl(adj.org_id, -999)
905 AND ct.complete_flag = 'Y'
906 -- AND ctl.customer_trx_line_id (+) = nvl2(adj.customer_trx_line_id,0,0)
907 -- AND nvl(ctl.interface_line_context, 'xxx') <> 'PA INVOICES'
908 -- AND ctl_parent.customer_trx_line_id (+) =
909 -- nvl(ctl.previous_customer_trx_line_id,ctl.customer_trx_line_id)
910 -- AND sl_child.line_id (+) =
911 -- case when (ctl_parent.interface_line_context in ('ORDER ENTRY', 'INTERCOMPANY')
912 -- and ltrim(ctl_parent.interface_line_attribute6, '0123456789') is NULL)
913 -- then to_number(ctl_parent.interface_line_attribute6)
914 -- else to_number(NULL) end
915 -- AND sh.header_id (+) = sl_child.header_id
916 -- AND sl_parent.line_id(+) = NVL(sl_child.top_model_line_id, sl_child.line_id)
917 AND slga.source_ledger_group_id = fslg.source_ledger_group_id
918 AND fslg.usage_code = g_usage_code
919 AND sob.ledger_id = aeh.ledger_id
920 AND bill_acct.cust_account_id(+) = ct.bill_to_customer_id
921 AND ael.accounting_class_code = AC.Accounting_Class_Code
922 AND ( aeh.ledger_id = AC.Ledger_ID OR AC.Ledger_ID IS NULL )
923 group by
924 ad.line_id,
925 to_number(to_char(trunc(aeh.accounting_date),'J')),
926 TRUNC(aeh.accounting_date),
927 adj.org_id,
928 ccdim.company_id,
929 ccdim.cost_center_id,
930 substrb(ct.trx_number,1,30),
931 trunc(ct.trx_date),
932 bill_acct.party_id,
933 sob.currency_code,
934 nvl(ct.invoice_currency_code,sob.currency_code),
935 aeh.ledger_id,
936 ct.customer_trx_id,
937 gcc.account_type,
938 ccdim.natural_account_id,
939 trunc(least(trunc(aeh.accounting_date), sysdate)),
940 ccdim.prod_category_id,
941 sob.chart_of_accounts_id,
942 -- ffcta.fin_cat_type_code,
943 AC.Fin_Cat_Type_Code;
944
945 END IF;
946
947 l_row_count := SQL%ROWCOUNT;
948
949 if g_debug_flag = 'Y' then
950 fii_util.put_line('');
951 fii_util.put_line('Inserting records into staging table');
952 fii_util.put_line('Inserted '||l_row_count||' rows');
953 fii_util.stop_timer;
954 fii_util.print_timer('Duration');
955 end if;
956
957 commit;
958
959 RETURN(l_row_count);
960
961 EXCEPTION
962 WHEN OTHERS THEN
963 g_retcode := -2;
964 g_errbuf := '
965 ---------------------------------
966 Error in Procedure: POPULATE_STG
967 Message: '||sqlerrm;
968 raise g_procedure_failure;
969
970 END POPULATE_STG;
971
972 -----------------------------------------------------------
973 -- PROCEDURE AR_STG_BF
974 -- Inserting records into staging table
975 -- for DR before g_gl_from_date
976 -- Parameter p_view_type_id and p_job_num are kept for future needs
977 -----------------------------------------------------------
978 FUNCTION AR_STG_BF (
979 p_view_type_id NUMBER,
980 p_job_num NUMBER) RETURN NUMBER IS
981
982 l_row_count NUMBER;
983
984 BEGIN
985
986 if g_debug_flag = 'Y' then
987 fii_util.put_line('');
988 fii_util.put_line('Started inserting rows into staging table for def rev transactions prior to global start date.');
989 fii_util.start_timer;
990 end if;
991
992 IF (p_view_type_id = 4) THEN
993 -- Bug 4942753: Per Lester's suggestion, reordered the XLA tables
994 INSERT /*+ APPEND PARALLEL(F) */ INTO FII_AR_REVENUE_STG F (
995 REVENUE_PK,
996 GL_DATE_ID,
997 GL_DATE,
998 INVENTORY_ITEM_ID,
999 OPERATING_UNIT_ID,
1000 COMPANY_ID,
1001 COST_CENTER_ID,
1002 INVOICE_NUMBER,
1003 INVOICE_DATE,
1004 ORDER_LINE_ID,
1005 BILL_TO_PARTY_ID,
1006 FUNCTIONAL_CURRENCY,
1007 TRANSACTION_CURRENCY,
1008 LEDGER_ID,
1009 INVOICE_ID,
1010 AMOUNT_T,
1011 AMOUNT_B,
1012 EXCHANGE_DATE,
1013 TOP_MODEL_ITEM_ID,
1014 ORGANIZATION_ID,
1015 item_organization_id,
1016 om_product_revenue_flag,
1017 TRANSACTION_CLASS,
1018 FIN_CATEGORY_ID,
1019 INVOICE_LINE_ID,
1020 SALES_CHANNEL,
1021 ORDER_NUMBER,
1022 POSTED_FLAG,
1023 PRIM_CONVERSION_RATE,
1024 SEC_CONVERSION_RATE,
1025 PROD_CATEGORY_ID,
1026 CHART_OF_ACCOUNTS_ID,
1027 FIN_CAT_TYPE_CODE,
1028 REV_BOOKED_DATE,
1029 CHILD_ORDER_LINE_ID)
1030 WITH ACCNT_CLASS AS (SELECT XAD.Ledger_ID,
1031 XACA.Accounting_Class_Code,
1032 decode(XAD.Program_Code,
1033 g_program_code_R, 'R',
1034 g_program_code_DR, 'DR',
1035 NULL) Fin_Cat_Type_Code
1036 FROM XLA_Assignment_Defns_B XAD,
1037 XLA_Acct_Class_Assgns XACA
1038 WHERE XAD.Program_Code = g_program_code_DR
1039 AND XAD.Enabled_Flag = 'Y'
1040 AND XAD.Program_Code = XACA.Program_Code
1041 AND XAD.Assignment_Code = XACA.Assignment_Code)
1042 select /*+ ordered use_hash(X,sob,glcc) use_nl(ctl_parent,bill_acct,sl_child,sl_parent,sh)
1043 swap_join_inputs(sob) swap_join_inputs(glcc)
1044 parallel(X) parallel(Y) parallel(glcc) parallel(ctl_parent) parallel(sob)
1045 parallel(bill_acct) parallel(sl_child) parallel(sl_parent) parallel(sh) */
1046 DISTINCT 'AR-'||X.x_customer_trx_line_id||'-'||
1047 to_char(X.x_gl_date,'YYYY/MM/DD')||'-'|| X.x_code_combination_id REVENUE_PK,
1048 to_number(to_char(X.x_gl_date,'J')) GL_DATE_ID,
1049 TRUNC(X.x_gl_date) GL_DATE,
1050 CASE
1051 when (ctl_parent.line_type like 'LINE'
1052 and ctl_parent.inventory_item_id = sl_child.inventory_item_id
1053 and sl_child.ship_from_org_id IS NOT NULL )
1054 THEN ctl_parent.inventory_item_id
1055 when (ctl_parent.line_type like 'LINE'
1056 and ctl_parent.WAREHOUSE_ID IS NOT NULL)
1057 THEN ctl_parent.inventory_item_id
1058 ELSE
1059 to_number(NULL)
1060 END INVENTORY_ITEM_ID,
1061 X.x_org_id OPERATING_UNIT_ID,
1062 Y.y_company_id COMPANY_ID,
1063 Y.y_cost_center_id COST_CENTER_ID,
1064 substrb(X.x_trx_number,1,30) INVOICE_NUMBER,
1065 trunc(X.x_trx_date) INVOICE_DATE,
1066 DECODE(ctl_parent.line_type, 'LINE',
1067 DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
1068 DECODE(ctl_parent.interface_line_context,
1069 'ORDER ENTRY', sl_parent.line_id,
1070 'INTERCOMPANY', sl_parent.line_id,
1071 to_number(NULL)),
1072 to_number(NULL)),
1073 to_number(NULL)) ORDER_LINE_ID,
1074 bill_acct.party_id BILL_TO_PARTY_ID,
1075 sob.currency_code FUNCTIONAL_CURRENCY,
1076 nvl(X.x_invoice_currency_code,sob.currency_code) TRANSACTION_CURRENCY,
1077 X.x_ct_set_of_books_id SET_OF_BOOKS_ID,
1078 X.x_customer_trx_id INVOICE_ID,
1079 (X.x_amount) AMOUNT_T,
1080 (X.x_acctd_amount) AMOUNT_B,
1081 trunc(X.x_gl_date) EXCHANGE_DATE,
1082 CASE
1083 when (ctl_parent.line_type like 'LINE'
1084 and ctl_parent.inventory_item_id = sl_child.inventory_item_id
1085 and sl_parent.ship_from_org_id IS NOT NULL)
1086 THEN sl_parent.inventory_item_id
1087 ELSE
1088 to_number(NULL)
1089 END TOP_MODEL_ITEM_ID,
1090 DECODE(ctl_parent.line_type, 'LINE',
1091 DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
1092 sl_parent.ship_from_org_id, to_number(null)),
1093 to_number(NULL)) ORGANIZATION_ID,
1094 DECODE(ctl_parent.line_type, 'LINE',
1095 DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
1096 sl_child.ship_from_org_id, ctl_parent.WAREHOUSE_ID),
1097 to_number(NULL)) item_organization_id,
1098 decode(ctl_parent.interface_line_context, 'ORDER ENTRY',
1099 decode(nvl( sl_child.item_type_code, 'X' ), 'SERVICE',
1100 'N', 'Y'),
1101 'N') om_product_revenue_flag,
1102 decode(X.x_type,'GUAR','GUR',substrb(X.x_type,1,3)) TRANSACTION_CLASS,
1103 Y.y_natural_account_id FIN_CATEGORY_ID,
1104 X.x_customer_trx_line_id INVOICE_LINE_ID,
1105 nvl(substrb(sh.sales_channel_code,1,30), '-1') SALES_CHANNEL,
1106 substrb( DECODE(ctl_parent.interface_line_context,
1107 'ORDER ENTRY', ctl_parent.interface_line_attribute1,
1108 'INTERCOMPANY', ctl_parent.interface_line_attribute1,
1109 ctl_parent.sales_order),1,30) ORDER_NUMBER,
1110 'Y' POSTED_FLAG,
1111 -1 PRIM_CONVERSION_RATE,
1112 -1 SEC_CONVERSION_RATE,
1113 Y.y_prod_category_id PROD_CATEGORY_ID,
1114 sob.chart_of_accounts_id CHART_OF_ACCOUNTS_ID,
1115 -- Y.y_fin_cat_type_code FIN_CAT_TYPE_CODE,
1116 X.x_fin_cat_type_code FIN_CAT_TYPE_CODE,
1117 decode(sh.booked_flag, 'Y', trunc(nvl(sl_child.order_firmed_date,
1118 sh.booked_date)), to_date(null)) REV_BOOKED_DATE,
1119 X.x_child_order_line_id CHILD_ORDER_LINE_ID
1120 from (
1121 select /*+ no_merge cardinality(1000000) parallel(ccdim) parallel(slga) parallel(ffcta) parallel(fslg) */
1122 ccdim.code_combination_id y_code_combination_id,
1123 ccdim.company_id y_company_id,
1124 ccdim.cost_center_id y_cost_center_id,
1125 ccdim.natural_account_id y_natural_account_id,
1126 ccdim.prod_category_id y_prod_category_id,
1127 slga.ledger_id y_ledger_id
1128 -- ffcta.fin_cat_type_code y_fin_cat_type_code
1129 from fii_source_ledger_groups fslg,
1130 fii_slg_assignments slga,
1131 fii_gl_ccid_dimensions ccdim
1132 -- fii_fin_cat_type_assgns ffcta
1133 where (slga.bal_seg_value_id = ccdim.company_id
1134 OR slga.bal_seg_value_id = -1
1135 )
1136 AND slga.chart_of_accounts_id = ccdim.chart_of_accounts_id
1137 -- AND ffcta.fin_category_id = ccdim.natural_account_id
1138 AND slga.source_ledger_group_id = fslg.source_ledger_group_id
1139 AND fslg.usage_code = g_usage_code
1140 -- AND ffcta.fin_cat_type_code = 'DR'
1141 ) Y,
1142 (
1143 select /*+ no_merge cardinality(10000000) ordered full(fpk) use_hash(ctl,ct,ctt,ctlgd)
1144 parallel(fpk) parallel(ctl) parallel(ct) parallel(ctt) parallel(ctlgd) */
1145 ael.code_combination_id x_code_combination_id,
1146 trunc(aeh.accounting_date) x_gl_date,
1147 sum( NVL(lnk.unrounded_entered_cr,0) -
1148 NVL(lnk.unrounded_entered_dr,0) ) x_amount,
1149 sum( NVL(lnk.unrounded_accounted_cr,0) -
1150 NVL(lnk.unrounded_accounted_dr,0) ) x_acctd_amount,
1151 ctl.set_of_books_id x_set_of_books_id,
1152 ctl.previous_customer_trx_line_id x_prev_customer_trx_line_id,
1153 ctl.customer_trx_line_id x_customer_trx_line_id,
1154 ct.org_id x_org_id,
1155 ct.trx_number x_trx_number,
1156 ct.invoice_currency_code x_invoice_currency_code,
1157 ct.customer_trx_id x_customer_trx_id,
1158 ct.trx_date x_trx_date,
1159 ct.set_of_books_id x_ct_set_of_books_id,
1160 ct.bill_to_customer_id x_bill_to_customer_id,
1161 ctt.type x_type,
1162 decode(ctl.interface_line_context, 'ORDER ENTRY',ctl.interface_line_attribute6,
1163 null) x_child_order_line_id,
1164 AC.Fin_Cat_Type_Code x_fin_cat_type_code
1165 from fii_ar_revenue_id fpk,
1166 ra_customer_trx_lines_all ctl,
1167 ra_customer_trx_all ct,
1168 ra_cust_trx_types_all ctt,
1169 ra_cust_trx_line_gl_dist_all ctlgd,
1170 ACCNT_CLASS AC,
1171 xla_distribution_links lnk,
1172 xla_ae_lines ael,
1173 xla_ae_headers aeh
1174 WHERE fpk.view_type_id= 4
1175 AND fpk.job_num = p_job_num
1176 AND ctl.customer_trx_line_id = fpk.primary_key1
1177 AND nvl(ctl.interface_line_context, 'xxx') <> 'PA INVOICES'
1178 AND ct.customer_trx_id = ctl.customer_trx_id
1179 AND ct.complete_flag = 'Y'
1180 AND ctt.cust_trx_type_id(+) = ct.cust_trx_type_id
1181 AND ctt.org_id (+) = ct.org_id
1182 AND NVL(ctt.post_to_gl,'Y') = 'Y'
1183 AND ctlgd.customer_trx_line_id = ctl.customer_trx_line_id
1184 AND ctlgd.account_set_flag = 'N'
1185 AND NVL(lnk.unrounded_entered_cr,0) -
1186 NVL(lnk.unrounded_entered_dr,0) <> 0
1187 AND aeh.accounting_date < g_gl_from_date
1188 AND aeh.application_id = 222
1189 AND aeh.balance_type_code = 'A'
1190 AND aeh.gl_transfer_status_code = 'Y'
1191 AND ael.application_id = 222
1192 AND aeh.ae_header_id = ael.ae_header_id
1193 AND lnk.application_id = 222
1194 AND ael.ae_header_id = lnk.ae_header_id
1195 AND ael.ae_line_num = lnk.ae_line_num
1196 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
1197 AND lnk.source_distribution_id_num_1 = ctlgd.cust_trx_line_gl_dist_id
1198 AND aeh.ledger_id = ctlgd.set_of_books_id
1199 AND ael.accounting_class_code = AC.Accounting_Class_Code
1200 AND ( aeh.ledger_id = AC.Ledger_ID OR AC.Ledger_ID IS NULL )
1201 GROUP BY
1202 ael.code_combination_id,
1203 trunc(aeh.accounting_date),
1204 ctl.set_of_books_id,
1205 ctl.previous_customer_trx_line_id,
1206 ctl.customer_trx_line_id,
1207 ct.org_id,
1208 ct.trx_number,
1209 ct.invoice_currency_code,
1210 ct.customer_trx_id,
1211 ct.trx_date,
1212 ct.set_of_books_id,
1213 ct.bill_to_customer_id,
1214 ctt.type,
1215 ctl.interface_line_context,
1216 ctl.interface_line_attribute6,
1217 AC.Fin_Cat_Type_Code
1218 ) X,
1219 gl_ledgers_public_v sob,
1220 gl_code_combinations glcc,
1221 ra_customer_trx_lines_all ctl_parent,
1222 hz_cust_accounts bill_acct,
1223 oe_order_lines_all sl_child,
1224 oe_order_lines_all sl_parent,
1225 oe_order_headers_all sh
1226 WHERE Y.y_code_combination_id = X.x_code_combination_id
1227 AND Y.y_code_combination_id = glcc.code_combination_id
1228 AND Y.y_ledger_id = X.x_set_of_books_id
1229 AND ctl_parent.customer_trx_line_id = nvl(X.x_prev_customer_trx_line_id,X.x_customer_trx_line_id)
1230 AND sob.ledger_id = X.x_ct_set_of_books_id
1231 AND bill_acct.cust_account_id(+) = X.x_bill_to_customer_id
1232 AND sl_child.line_id (+) =
1233 case when (ctl_parent.interface_line_context in ('ORDER ENTRY', 'INTERCOMPANY')
1234 and ltrim(ctl_parent.interface_line_attribute6, '0123456789') is NULL)
1235 then to_number(ctl_parent.interface_line_attribute6)
1236 else to_number(NULL)
1237 end
1238 AND sh.header_id (+) = sl_child.header_id
1239 AND sl_parent.line_id(+) = NVL(sl_child.top_model_line_id, sl_child.line_id);
1240
1241 ELSIF (p_view_type_id = 3) THEN
1242
1243 NULL; -- No ADJ for DR
1244
1245 END IF;
1246
1247 l_row_count := SQL%ROWCOUNT;
1248
1249 if g_debug_flag = 'Y' then
1250 fii_util.put_line('');
1251 -- fii_util.put_line('Extracting Deferred Revenue transactions prior to global_start date to staging table');
1252 fii_util.put_line('Inserted '||l_row_count||' rows into staging table.');
1253 fii_util.stop_timer;
1254 fii_util.print_timer('Duration');
1255 end if;
1256
1257 commit;
1258
1259 RETURN(l_row_count);
1260
1261 EXCEPTION
1262 WHEN OTHERS THEN
1263 g_retcode := -2;
1264 g_errbuf := '
1265 ---------------------------------
1266 Error in Procedure: AR_STG_BF
1267 Message: '||sqlerrm;
1268 raise g_procedure_failure;
1269
1270 END AR_STG_BF;
1271
1272 -----------------------------------------------------------
1273 -- FUNCTION POPULATE_SUM
1274 -- inserting / updating records in base summary table
1275 -----------------------------------------------------------
1276 FUNCTION POPULATE_SUM RETURN NUMBER IS
1277 l_stmt VARCHAR2(1500);
1278 seq_id NUMBER :=0;
1279 l_row_count NUMBER;
1280 BEGIN
1281
1282 SELECT FII_AR_REVENUE_B_S.nextval INTO seq_id FROM dual;
1283
1284 if g_debug_flag = 'Y' then
1285 fii_util.put_line(' ');
1286 fii_util.put_line('Merging data into base summary table');
1287 fii_util.start_timer;
1288 fii_util.put_line('');
1289 end if;
1290
1291 MERGE INTO FII_AR_REVENUE_B f
1292 USING (SELECT /*+ cardinality(stg,1) */ * FROM FII_AR_REVENUE_STG stg
1293 WHERE prim_conversion_rate > 0
1294 OR sec_conversion_rate > 0) stg
1295 ON ( stg.revenue_pk = f.revenue_pk)
1296 WHEN MATCHED THEN
1297 UPDATE SET
1298 f.AMOUNT_T = stg.AMOUNT_T,
1299 f.AMOUNT_B = stg.AMOUNT_B,
1300 f.PRIM_AMOUNT_G = ROUND(stg.AMOUNT_B * NVL(stg.prim_conversion_rate, 1) /
1301 to_char(g_mau_prim)) * to_char(g_mau_prim),
1302 f.SEC_AMOUNT_G = ROUND(stg.AMOUNT_B * NVL(stg.sec_conversion_rate, 1) /
1303 to_char(g_mau_sec)) * to_char(g_mau_sec),
1304 f.UPDATE_SEQUENCE = seq_id,
1305 f.LAST_UPDATED_BY = g_fii_user_id,
1306 f.LAST_UPDATE_LOGIN = g_fii_login_id,
1307 f.LAST_UPDATE_DATE = SYSDATE
1308 WHEN NOT MATCHED THEN
1309 INSERT (
1310 f.REVENUE_PK,
1311 f.GL_DATE_ID,
1312 f.GL_DATE,
1313 f.INVENTORY_ITEM_ID,
1314 f.OPERATING_UNIT_ID,
1315 f.COMPANY_ID,
1316 f.COST_CENTER_ID,
1317 f.INVOICE_NUMBER,
1318 f.ORDER_LINE_ID,
1319 f.BILL_TO_PARTY_ID,
1320 f.FUNCTIONAL_CURRENCY,
1321 f.TRANSACTION_CURRENCY,
1322 f.LEDGER_ID,
1323 f.INVOICE_ID,
1324 f.AMOUNT_T,
1325 f.AMOUNT_B,
1326 f.PRIM_AMOUNT_G,
1327 f.SEC_AMOUNT_G,
1328 f.TOP_MODEL_ITEM_ID,
1329 f.ORGANIZATION_ID,
1330 f.item_organization_id,
1331 f.om_product_revenue_flag,
1332 f.TRANSACTION_CLASS,
1333 f.FIN_CATEGORY_ID,
1334 f.ORDER_NUMBER,
1335 f.SALES_CHANNEL,
1336 f.INVOICE_LINE_ID,
1337 f.LAST_UPDATE_DATE,
1338 f.CREATION_DATE,
1339 f.POSTED_FLAG,
1340 f.PROD_CATEGORY_ID,
1341 f.CHART_OF_ACCOUNTS_ID,
1342 f.UPDATE_SEQUENCE,
1343 f.LAST_UPDATED_BY,
1344 f.CREATED_BY,
1345 f.LAST_UPDATE_LOGIN,
1346 f.INVOICE_DATE,
1347 f.FIN_CAT_TYPE_CODE,
1348 f.REV_BOOKED_DATE,
1349 f.CHILD_ORDER_LINE_ID)
1350 VALUES (
1351 stg.REVENUE_PK,
1352 stg.GL_DATE_ID,
1353 stg.GL_DATE,
1354 stg.INVENTORY_ITEM_ID,
1355 stg.OPERATING_UNIT_ID,
1356 stg.COMPANY_ID,
1357 stg.COST_CENTER_ID,
1358 stg.INVOICE_NUMBER,
1359 stg.ORDER_LINE_ID,
1360 stg.BILL_TO_PARTY_ID,
1361 stg.FUNCTIONAL_CURRENCY,
1362 stg.TRANSACTION_CURRENCY,
1363 stg.LEDGER_ID,
1364 stg.INVOICE_ID,
1365 stg.AMOUNT_T,
1366 stg.AMOUNT_B,
1367 ROUND(stg.AMOUNT_B * NVL(stg.prim_conversion_rate, 1) /
1368 to_char(g_mau_prim)) * to_char(g_mau_prim),
1369 ROUND(stg.AMOUNT_B * NVL(stg.sec_conversion_rate, 1) /
1370 to_char(g_mau_sec)) * to_char(g_mau_sec),
1371 stg.TOP_MODEL_ITEM_ID,
1372 stg.ORGANIZATION_ID,
1373 stg.item_organization_id,
1374 stg.om_product_revenue_flag,
1375 stg.TRANSACTION_CLASS,
1376 stg.FIN_CATEGORY_ID,
1377 stg.ORDER_NUMBER,
1378 stg.SALES_CHANNEL,
1379 stg.INVOICE_LINE_ID,
1380 SYSDATE,
1381 SYSDATE,
1382 stg.POSTED_FLAG,
1383 stg.PROD_CATEGORY_ID,
1384 stg.CHART_OF_ACCOUNTS_ID,
1385 seq_id,
1386 g_fii_user_id,
1387 g_fii_user_id,
1388 g_fii_login_id,
1389 stg.invoice_date,
1390 stg.FIN_CAT_TYPE_CODE,
1391 stg.REV_BOOKED_DATE,
1392 stg.CHILD_ORDER_LINE_ID);
1393
1394
1395 l_row_count := SQL%ROWCOUNT;
1396 if g_debug_flag = 'Y' then
1397 fii_util.put_line('Processed '||l_row_count||' rows');
1398 fii_util.stop_timer;
1399 fii_util.print_timer('Duration');
1400 end if;
1401
1402 commit;
1403
1404 RETURN(l_row_count);
1405
1406 /* DELETE FROM FII_AR_REVENUE_B f
1407 WHERE f.UPDATE_SEQUENCE <> seq_id
1408 AND f.TRANSACTION_CLASS <> 'ADJ'
1409 AND f.INVOICE_LINE_ID IN (SELECT fpk.primary_key1 FROM FII_AR_REVENUE_ID fpk
1410 WHERE fpk.view_type_id = 4);
1411
1412
1413 COMMIT;
1414 */
1415 EXCEPTION
1416 WHEN OTHERS THEN
1417 g_retcode := -2;
1418 g_errbuf := '
1419 ---------------------------------
1420 Error in Procedure: POPULATE_SUM
1421 Message: '||sqlerrm;
1422 ROLLBACK;
1423 RAISE g_procedure_failure;
1424
1425 END POPULATE_SUM;
1426
1427
1428 -----------------------------------------------------------
1429 -- FUNCTION VERIFY_MISSING_RATES
1430 -----------------------------------------------------------
1431
1432 FUNCTION VERIFY_MISSING_RATES RETURN NUMBER IS
1433 l_stmt VARCHAR2(1000);
1434 l_miss_rates_prim NUMBER :=0;
1435 l_miss_rates_sec NUMBER :=0;
1436 l_miss_rates NUMBER := 0;
1437 l_ccid VARCHAR2(2000):=NULL;
1438 l_miss_ccid NUMBER :=0;
1439 l_miss_date NUMBER :=0;
1440 l_transaction_currency VARCHAR2(4):=NULL;
1441 l_exchange_date DATE := NULL;
1442 l_stg_count NUMBER :=0;
1443 l_prim_return NUMBER :=0;
1444 l_sec_return NUMBER :=0;
1445
1446 l_prim_currency_code VARCHAR2(30);
1447 l_sec_currency_code VARCHAR2(30);
1448 l_prim_rate_type VARCHAR2(30);
1449 l_sec_rate_type VARCHAR2(30);
1450 l_prim_rate_type_name VARCHAR2(30);
1451 l_sec_rate_type_name VARCHAR2(30);
1452
1453 -------------------------------------------------------
1454 -- Cursor declaration required to generate output file
1455 -- containing rows with MISSING CONVERSION RATES
1456 -------------------------------------------------------
1457 CURSOR c1 IS SELECT DISTINCT TRANSACTION_CURRENCY,
1458 decode( prim_conversion_rate,
1459 -3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
1460 trunc(least(EXCHANGE_DATE,sysdate))) EXCHANGE_DATE
1461 FROM FII_AR_REVENUE_STG
1462 WHERE prim_conversion_rate < 0;
1463
1464 CURSOR c2 IS SELECT DISTINCT TRANSACTION_CURRENCY,
1465 decode( sec_conversion_RATE,
1466 -3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
1467 trunc(least(EXCHANGE_DATE,sysdate))) EXCHANGE_DATE
1468 FROM FII_AR_REVENUE_STG
1469 WHERE sec_conversion_RATE < 0;
1470
1471 CURSOR c3 IS SELECT DISTINCT FUNCTIONAL_CURRENCY,
1472 decode( prim_conversion_rate,
1473 -3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
1474 trunc(least(TRX_DATE,sysdate))) TRX_DATE
1475 FROM FII_AR_REVENUE_RATES_TEMP
1476 WHERE prim_conversion_rate < 0;
1477
1478 CURSOR c4 IS SELECT DISTINCT FUNCTIONAL_CURRENCY,
1479 decode( sec_conversion_RATE,
1480 -3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
1481 trunc(least(TRX_DATE,sysdate))) TRX_DATE
1482 FROM FII_AR_REVENUE_RATES_TEMP
1483 WHERE sec_conversion_RATE < 0;
1484
1485 BEGIN
1486
1487 -----------------------------------------------------------
1488 -- we will check staging table if there's any missing rates.
1489 -- If yes, we will print out missing rate report and return
1490 -- -1. If there's no missing rate, then we will return 1.
1491 -----------------------------------------------------------
1492 if g_debug_flag = 'Y' then
1493 fii_util.put_line(' ');
1494 fii_util.put_timestamp;
1495 fii_util.put_line('Checking whether there are any missing exchange rates.');
1496 end if;
1497
1498
1499 IF g_program_type = 'L' THEN
1500
1501 -- Bug 4942753: Change to return 1 if any row exists
1502 BEGIN
1503 SELECT 1
1504 INTO l_miss_rates
1505 FROM FII_AR_REVENUE_RATES_TEMP
1506 WHERE ((prim_conversion_rate < 0) OR (sec_conversion_rate < 0))
1507 AND ROWNUM = 1;
1508 EXCEPTION
1509 WHEN NO_DATA_FOUND THEN
1510 l_miss_rates := 0;
1511 END;
1512
1513 ELSE
1514
1515 -- Bug 4942753: Change to return 1 if any row exists
1516 BEGIN
1517 SELECT 1
1518 INTO l_miss_rates
1519 FROM FII_AR_REVENUE_STG
1520 WHERE ((prim_conversion_rate < 0) OR (sec_conversion_rate < 0))
1521 AND ROWNUM = 1;
1522 EXCEPTION
1523 WHEN NO_DATA_FOUND THEN
1524 l_miss_rates := 0;
1525 END;
1526
1527 END IF;
1528
1529 ---------------------------------------------------
1530 -- Print out missing rates report
1531 ---------------------------------------------------
1532
1533 l_prim_currency_code := bis_common_parameters.get_currency_code;
1534 l_sec_currency_code := bis_common_parameters.get_secondary_currency_code;
1535 l_prim_rate_type := bis_common_parameters.get_rate_type;
1536 l_sec_rate_type := bis_common_parameters.get_secondary_rate_type;
1537
1538 begin
1539 select user_conversion_type into l_prim_rate_type_name
1540 from gl_daily_conversion_types
1541 where conversion_type = l_prim_rate_type;
1542
1543 if l_sec_rate_type is not null then
1544 select user_conversion_type into l_sec_rate_type_name
1545 from gl_daily_conversion_types
1546 where conversion_type = l_sec_rate_type;
1547 else
1548 l_sec_rate_type_name := null;
1549 end if;
1550 exception
1551 when others then
1552 fii_util.write_log(
1553 'Failed to convert rate_type to rate_type_name' );
1554 raise;
1555 end;
1556
1557 IF (l_miss_rates > 0) THEN
1558
1559 IF g_program_type = 'L' THEN
1560
1561 -- Bug 4942753: Change to return 1 if any row exists
1562 BEGIN
1563 SELECT 1
1564 INTO l_miss_rates_prim
1565 FROM FII_AR_REVENUE_RATES_TEMP
1566 WHERE prim_conversion_rate < 0
1567 AND ROWNUM = 1;
1568 EXCEPTION
1569 WHEN NO_DATA_FOUND THEN
1570 l_miss_rates_prim := 0;
1571 END;
1572
1573 -- Bug 4942753: Change to return 1 if any row exists
1574 BEGIN
1575 SELECT 1
1576 INTO l_miss_rates_sec
1577 FROM FII_AR_REVENUE_RATES_TEMP
1578 WHERE sec_conversion_rate < 0
1579 AND ROWNUM = 1;
1580 EXCEPTION
1581 WHEN NO_DATA_FOUND THEN
1582 l_miss_rates_sec := 0;
1583 END;
1584
1585 ELSE
1586
1587 -- Bug 4942753: Change to return 1 if any row exists
1588 BEGIN
1589 SELECT 1
1590 INTO l_miss_rates_prim
1591 FROM FII_AR_REVENUE_STG
1592 WHERE prim_conversion_rate < 0
1593 AND ROWNUM = 1;
1594 EXCEPTION
1595 WHEN NO_DATA_FOUND THEN
1596 l_miss_rates_prim := 0;
1597 END;
1598
1599 -- Bug 4942753: Change to return 1 if any row exists
1600 BEGIN
1601 SELECT 1
1602 INTO l_miss_rates_sec
1603 FROM FII_AR_REVENUE_STG
1604 WHERE sec_conversion_rate < 0
1605 AND ROWNUM = 1;
1606 EXCEPTION
1607 WHEN NO_DATA_FOUND THEN
1608 l_miss_rates_sec := 0;
1609 END;
1610
1611 END IF;
1612
1613 IF (l_miss_rates_prim > 0) THEN
1614 -- if g_debug_flag = 'Y' then
1615 fii_util.put_line(' ');
1616 fii_util.put_line('There are some missing prim exchange rates.');
1617 -- end if;
1618 bis_collection_utilities.writeMissingRateHeader;
1619
1620
1621 IF g_program_type = 'L' THEN
1622
1623 FOR rate_record in c3
1624 LOOP
1625 null;
1626 bis_collection_utilities.writeMissingRate(
1627 l_prim_rate_type_name,
1628 rate_record.functional_currency,
1629 l_prim_currency_code,
1630 rate_record.trx_date);
1631 END LOOP;
1632
1633 ELSE
1634
1635 FOR rate_record in c1
1636 LOOP
1637 null;
1638 bis_collection_utilities.writeMissingRate(
1639 l_prim_rate_type_name,
1640 rate_record.transaction_currency,
1641 l_prim_currency_code,
1642 rate_record.exchange_date);
1643 END LOOP;
1644
1645 END IF;
1646 END IF;
1647
1648 IF (l_miss_rates_sec > 0) THEN
1649 -- if g_debug_flag = 'Y' then
1650 fii_util.put_line(' ');
1651 fii_util.put_line('There are some missing sec conversion rates.');
1652 -- end if;
1653
1654 bis_collection_utilities.writeMissingRateHeader;
1655
1656 IF g_program_type = 'L' THEN
1657
1658 FOR rate_record in c4
1659 LOOP
1660 null;
1661 bis_collection_utilities.writeMissingRate(
1662 l_sec_rate_type_name,
1663 rate_record.functional_currency,
1664 l_sec_currency_code,
1665 rate_record.trx_date);
1666 END LOOP;
1667
1668 ELSE
1669
1670 FOR rate_record in c2
1671 LOOP
1672 null;
1673 bis_collection_utilities.writeMissingRate(
1674 l_sec_rate_type_name,
1675 rate_record.transaction_currency,
1676 l_sec_currency_code,
1677 rate_record.exchange_date);
1678 END LOOP;
1679
1680 END IF;
1681 END IF;
1682
1683 RETURN -1;
1684 ELSE
1685 RETURN 1;
1686 END IF;
1687
1688 EXCEPTION
1689
1690 WHEN OTHERS THEN
1691 g_retcode := -2;
1692 g_errbuf := '
1693 ---------------------------------
1694 Error in Procedure: VERIFY_MISSING_RATES
1695 Message: '||sqlerrm;
1696
1697 RAISE g_procedure_failure;
1698
1699 END VERIFY_MISSING_RATES;
1700
1701
1702 ---------------------------------------------------
1703 -- FUNCTION IDENTIFY_CHANGE
1704 -- view_type_id = 3 (AR ADJ)
1705 -- view_type_id = 4 (AR INV)
1706 -- only for Incremental update --
1707 ---------------------------------------------------
1708 FUNCTION IDENTIFY_CHANGE(
1709 p_type IN VARCHAR2) RETURN NUMBER IS
1710
1711 l_count NUMBER := 0;
1712 l_lud VARCHAR2(500) := NULL;
1713 l_sob VARCHAR2(200) := NULL;
1714 l_gl_from VARCHAR2(80);
1715 l_gl_to VARCHAR2(80);
1716 l_lud_from VARCHAR2(80);
1717 l_lud_to VARCHAR2(80);
1718 l_max_group_id NUMBER(15);
1719 l_stmt VARCHAR2(1500);
1720 l_where VARCHAR2(500) := NULL;
1721
1722 BEGIN
1723
1724 l_count := 0;
1725 g_section := 'Section 10';
1726 -- if g_debug_flag = 'Y' then
1727 -- fii_util.put_line('Identify Change for Revenue and Deferred Revenue records for view type '||p_type);
1728 -- end if;
1729
1730 -- --------------------------------------------
1731 -- Identify changed rows based on gl date and
1732 -- last update date
1733 -- --------------------------------------------
1734 -- l_gl_from := 'to_date('''||to_char(g_gl_from_date,'YYYY/MM/DD HH24:MI:SS')||
1735 -- ''',''YYYY/MM/DD HH24:MI:SS'')';
1736
1737 -- l_gl_to := 'to_date('''||to_char(g_gl_to_date,'YYYY/MM/DD HH24:MI:SS')||
1738 -- ''',''YYYY/MM/DD HH24:MI:SS'')';
1739
1740 -- l_lud_from := 'to_date('''||to_char(g_lud_from_date,'YYYY/MM/DD HH24:MI:SS')||
1741 -- ''',''YYYY/MM/DD HH24:MI:SS'')';
1742
1743 -- l_lud_to := 'to_date('''||to_char(g_lud_to_date,'YYYY/MM/DD HH24:MI:SS')||
1744 -- ''',''YYYY/MM/DD HH24:MI:SS'')';
1745
1746 l_gl_from := to_char(g_gl_from_date,'YYYY/MM/DD HH24:MI:SS');
1747
1748 l_gl_to := to_char(g_gl_to_date,'YYYY/MM/DD HH24:MI:SS');
1749
1750 l_lud_from := to_char(g_lud_from_date,'YYYY/MM/DD HH24:MI:SS');
1751
1752 l_lud_to := to_char(g_lud_to_date,'YYYY/MM/DD HH24:MI:SS');
1753
1754 select to_number(item_value) into l_max_group_id
1755 from FII_CHANGE_LOG
1756 where log_item = 'AR_MAX_GROUP_ID';
1757
1758 -- if g_debug_flag = 'Y' then
1759 -- fii_util.put_line ('l_gl_from: ' ||l_gl_from);
1760 -- fii_util.put_line ('l_gl_to: ' ||l_gl_to);
1761 -- end if;
1762
1763
1764 IF (p_type = 'AR ADJ') THEN
1765 /*
1766 -- ----------------------------------------
1767 -- For ar adjustments, find the list of
1768 -- adjustments which has been updated
1769 -- ----------------------------------------
1770
1771 l_stmt := '
1772 INSERT INTO FII_AR_REVENUE_ID (
1773 view_type_id,
1774 primary_key1)
1775 SELECT --+ ORDERED USE_NL(ADJ)
1776 7,
1777 adj.adjustment_id
1778 from ra_customer_trx_all t,
1779 ar_adjustments_all adj,
1780 ( select distinct ledger_id
1781 from fii_slg_assignments slga,
1782 fii_source_ledger_groups fslg
1783 where slga.source_ledger_group_id = fslg.source_ledger_group_id
1784 and fslg.usage_code = :a
1785 ) lidset
1786 WHERE t.complete_flag = ''Y''
1787 AND t.last_update_date BETWEEN to_date(:b,''YYYY/MM/DD HH24:MI:SS'') and to_date(:c,''YYYY/MM/DD HH24:MI:SS'')
1788 and t.customer_trx_id = adj.customer_trx_id
1789 and nvl(adj.status, ''A'') = ''A''
1790 and nvl(adj.postable,''Y'') = ''Y''
1791 and adj.amount <> 0
1792 and t.set_of_books_id = lidset.ledger_id';
1793
1794 if g_debug_flag = 'Y' then
1795 -- fii_util.put_line(' ');
1796 -- fii_util.put_line(l_stmt);
1797 fii_util.start_timer;
1798 end if;
1799 EXECUTE IMMEDIATE l_stmt using g_usage_code, l_lud_from, l_lud_to;
1800 l_count := SQL%ROWCOUNT;
1801 if g_debug_flag = 'Y' then
1802 -- fii_util.put_line(' ');
1803 fii_util.stop_timer;
1804 fii_util.print_timer('Duration');
1805 end if;
1806
1807
1808 l_stmt := '
1809 INSERT INTO FII_AR_REVENUE_ID (
1810 view_type_id,
1811 primary_key1)
1812 SELECT
1813 7,
1814 t.adjustment_id
1815 FROM ar_adjustments_all t,
1816 ( select distinct ledger_id
1817 from fii_slg_assignments slga,
1818 fii_source_ledger_groups fslg
1819 where slga.source_ledger_group_id = fslg.source_ledger_group_id
1820 AND fslg.usage_code = :a
1821 ) lidset
1822 WHERE t.gl_date BETWEEN to_date(:b,''YYYY/MM/DD HH24:MI:SS'') AND to_date(:c,''YYYY/MM/DD HH24:MI:SS'')
1823 AND t.last_update_date BETWEEN to_date(:d,''YYYY/MM/DD HH24:MI:SS'') and to_date(:e,''YYYY/MM/DD HH24:MI:SS'')
1824 AND NVL(t.status, ''A'') = ''A''
1825 AND NVL(t.postable,''Y'') = ''Y''
1826 AND t.amount <> 0
1827 AND t.set_of_books_id = lidset.ledger_id';
1828
1829 if g_debug_flag = 'Y' then
1830 -- fii_util.put_line(' ');
1831 -- fii_util.put_line(l_stmt);
1832 fii_util.start_timer;
1833 end if;
1834 EXECUTE IMMEDIATE l_stmt using g_usage_code, l_gl_from, l_gl_to, l_lud_from, l_lud_to;
1835 l_count := l_count + SQL%ROWCOUNT;
1836 if g_debug_flag = 'Y' then
1837 -- fii_util.put_line(' ');
1838 fii_util.stop_timer;
1839 fii_util.print_timer('Duration');
1840 end if;
1841 */ NULL;
1842
1843 /* ELSIF (p_type = 'AR INV') THEN
1844
1845 -- -----------------------------------------
1846 -- For ra_customer_trx_lines_all.
1847 -- -----------------------------------------
1848
1849 l_stmt := '
1850 INSERT INTO FII_AR_REVENUE_ID (
1851 view_type_id,
1852 primary_key1)
1853 SELECT
1854 8,
1855 ctl.customer_trx_line_id
1856 FROM ra_customer_trx_all ct,
1857 ra_customer_trx_lines_all ctl,
1858 ( select distinct ledger_id
1859 from fii_slg_assignments slga,
1860 fii_source_ledger_groups fslg
1861 where slga.source_ledger_group_id = fslg.source_ledger_group_id
1862 and fslg.usage_code = :a
1863 ) lidset
1864 WHERE ct.last_update_date between to_date(:b,''YYYY/MM/DD HH24:MI:SS'') and to_date(:c,''YYYY/MM/DD HH24:MI:SS'')
1865 AND ct.customer_trx_id = ctl.customer_trx_id
1866 AND ct.complete_flag = ''Y''
1867 AND ct.set_of_books_id = lidset.ledger_id
1868 UNION
1869 SELECT
1870 8,
1871 ct.customer_trx_line_id
1872 FROM ra_customer_trx_lines_all ct,
1873 ( select distinct ledger_id
1874 from fii_slg_assignments slga,
1875 fii_source_ledger_groups fslg
1876 where slga.source_ledger_group_id = fslg.source_ledger_group_id
1877 and fslg.usage_code = :d
1878 ) lidset
1879 WHERE nvl(ct.interface_line_context, ''xxx'') NOT IN (''PA INVOICES'')
1880 AND ct.last_update_date between to_date(:e,''YYYY/MM/DD HH24:MI:SS'') AND to_date(:f,''YYYY/MM/DD HH24:MI:SS'')
1881 AND ct.set_of_books_id = lidset.ledger_id';
1882
1883 if g_debug_flag = 'Y' then
1884 -- fii_util.put_line(' ');
1885 -- fii_util.put_line(l_stmt);
1886 fii_util.start_timer;
1887 end if;
1888 EXECUTE IMMEDIATE l_stmt using g_usage_code,l_lud_from, l_lud_to, g_usage_code, l_lud_from, l_lud_to;
1889 l_count := SQL%ROWCOUNT;
1890 if g_debug_flag = 'Y' then
1891 -- fii_util.put_line(' ');
1892 fii_util.stop_timer;
1893 fii_util.print_timer('Duration');
1894 end if;
1895 */
1896
1897 ELSIF (p_type = 'AR DL') THEN
1898 -- -----------------------------------------
1899 -- For ra_cust_trx_line_gl_dist_all
1900 -- -----------------------------------------
1901
1902 ---------------------------------------------
1903 -- For 'AR DL', if identifying future dated
1904 -- transactions, do not filter by last updated
1905 -- hours. We will scan only one month into
1906 -- the future. Future dated records too far
1907 -- into the future will be picked up gradually
1908 -- into the future. This is why we can't
1909 -- filter by last_updated hours.
1910 ----------------------------------------------
1911 IF (g_gl_to_date > SYSDATE) THEN
1912
1913 -- This logic has been moved to procedure MAIN
1914 -- g_gl_to_date := ADD_MONTHS(sysdate, 1);
1915 -- l_gl_to := to_char(g_gl_to_date, 'YYYY/MM/DD HH24:MI:SS');
1916
1917 l_stmt := 'INSERT INTO FII_AR_REVENUE_ID (
1918 view_type_id,
1919 primary_key1)
1920 SELECT /*+ INDEX(aeh, xla_ae_headers_N5) */
1921 dup.view_type_id,
1922 aeh.ae_header_id
1923 FROM xla_ae_headers aeh,
1924 ( select /*+ no_merge */ distinct ledger_id
1925 from fii_slg_assignments slga,
1926 fii_source_ledger_groups fslg
1927 where slga.source_ledger_group_id = fslg.source_ledger_group_id
1928 and fslg.usage_code = :m
1929 ) lidset,
1930 ( select 8 view_type_id from dual
1931 union all
1932 select 7 view_type_id from dual
1933 ) dup
1934 WHERE aeh.accounting_date BETWEEN to_date(:n,''YYYY/MM/DD HH24:MI:SS'') AND to_date(:o,''YYYY/MM/DD HH24:MI:SS'')
1935 AND aeh.application_id = 222
1936 AND aeh.balance_type_code = ''A''
1937 AND aeh.gl_transfer_status_code = ''Y''
1938 AND aeh.ledger_id = lidset.ledger_id';
1939
1940 if g_debug_flag = 'Y' then
1941 fii_util.put_line(' ');
1942 -- fii_util.put_line(l_stmt);
1943 fii_util.start_timer;
1944 end if;
1945 EXECUTE IMMEDIATE l_stmt using g_usage_code, l_gl_from, l_gl_to;
1946 l_count := SQL%ROWCOUNT;
1947 if g_debug_flag = 'Y' then
1948 -- fii_util.put_line(' ');
1949 fii_util.stop_timer;
1950 fii_util.print_timer('Duration');
1951 end if;
1952
1953 ELSE
1954 l_stmt := 'INSERT INTO FII_AR_REVENUE_ID (
1955 view_type_id,
1956 primary_key1)
1957 SELECT /*+ INDEX(aeh, xla_ae_headers_N5) */
1958 dup.view_type_id,
1959 aeh.ae_header_id
1960 FROM xla_ae_headers aeh,
1961 ( select distinct ledger_id
1962 from fii_slg_assignments slga,
1963 fii_source_ledger_groups fslg
1964 where slga.source_ledger_group_id = fslg.source_ledger_group_id
1965 and fslg.usage_code = :a
1966 ) lidset,
1967 ( select 8 view_type_id from dual
1968 union all
1969 select 7 view_type_id from dual
1970 ) dup
1971 WHERE aeh.accounting_date BETWEEN to_date(:b,''YYYY/MM/DD HH24:MI:SS'') AND to_date(:c,''YYYY/MM/DD HH24:MI:SS'')
1972 AND aeh.group_id > :m
1973 AND aeh.application_id = 222
1974 AND aeh.balance_type_code = ''A''
1975 AND aeh.gl_transfer_status_code = ''Y''
1976 AND aeh.ledger_id = lidset.ledger_id';
1977
1978 if g_debug_flag = 'Y' then
1979 -- fii_util.put_line(' ');
1980 -- fii_util.put_line(l_stmt);
1981 fii_util.start_timer;
1982 end if;
1983 EXECUTE IMMEDIATE l_stmt using g_usage_code, l_gl_from, l_gl_to, l_max_group_id;
1984 l_count := SQL%ROWCOUNT;
1985 if g_debug_flag = 'Y' then
1986 -- fii_util.put_line(' ');
1987 fii_util.stop_timer;
1988 fii_util.print_timer('Duration');
1989 end if;
1990
1991
1992 END IF;
1993
1994 /* if g_debug_flag = 'Y' then
1995 fii_util.put_line(' ');
1996 fii_util.put_line(l_stmt);
1997 fii_util.start_timer;
1998 end if;
1999 EXECUTE IMMEDIATE l_stmt;
2000 l_count := SQL%ROWCOUNT;
2001 if g_debug_flag = 'Y' then
2002 fii_util.put_line(' ');
2003 fii_util.stop_timer;
2004 fii_util.print_timer('Duration');
2005 end if; */
2006
2007 END IF;
2008
2009
2010 -- ------------------------------------------
2011 -- Commit for the child process to pick up
2012 -- -----------------------------------------
2013 COMMIT;
2014
2015 RETURN(l_count);
2016
2017 EXCEPTION
2018 WHEN OTHERS THEN
2019 g_retcode := -2;
2020 g_errbuf := '
2021 ---------------------------------
2022 Error in Procedure: IDENTIFY_CHANGE
2023 Parameter: p_type='||p_type||'
2024 l_gl_from='||l_gl_from||'
2025 l_gl_to='||l_gl_to||'
2026 l_lud_from='||l_lud_from||'
2027 l_lud_to='||l_lud_to||'
2028 -- g_sob_id='||g_sob_id||'
2029 Section: '||g_section||'
2030 Message: '||sqlerrm;
2031 RAISE g_procedure_failure;
2032
2033 END IDENTIFY_CHANGE;
2034
2035 ---------------------------------------------------
2036 -- FUNCTION IDENTIFY_CHANGE_INIT
2037 -- view_type_id = 3 (AR ADJ)
2038 -- view_type_id = 4 (AR INV)
2039 -- Only for Initial load --
2040 -- Must have: DR before g_gl_from_date
2041 -- Parameter p_type is kept for future needs
2042 ---------------------------------------------------
2043 FUNCTION IDENTIFY_CHANGE_INIT(
2044 p_type IN VARCHAR2) RETURN NUMBER IS
2045
2046 l_count NUMBER := 0;
2047 l_stmt VARCHAR2(5000);
2048 l_stmt2 VARCHAR2(1024);
2049 k_status VARCHAR2(30);
2050 k_industry VARCHAR2(30);
2051 k_ar_schema VARCHAR2(30);
2052
2053
2054
2055 BEGIN
2056 g_section := 'Section 10';
2057
2058 if g_debug_flag = 'Y' then
2059 fii_util.put_line('Running function IDENTIFY_CHANGE_INIT to identify deferred rev records prior to global start date for view type '||p_type);
2060 end if;
2061
2062 IF (p_type = 'AR ADJ') THEN
2063
2064 NULL; -- No ADJ for DR
2065
2066 ELSIF (p_type = 'AR INV') THEN
2067
2068 -- This table has been introduced in financals 115.9,
2069 -- but DBI6.1 can only assume dependency on financals 115.8
2070 --
2071
2072 IF(FND_INSTALLATION.GET_APP_INFO('AR', k_status, k_industry, k_ar_schema))
2073 THEN NULL;
2074 END IF;
2075
2076 -- Bug 4942753: Changed to select from dba_tables instead of all_tables
2077 -- and changed to return 1 if any row exists
2078 BEGIN
2079 select 1 into l_count from dba_tables
2080 where table_name = 'AR_DEFERRED_LINES_ALL'
2081 and owner = k_ar_schema
2082 and rownum = 1;
2083 EXCEPTION
2084 WHEN NO_DATA_FOUND THEN
2085 l_count := 0;
2086 END;
2087
2088 if l_count > 0 then
2089 l_stmt2 := '
2090 union all
2091 select /*+ parallel(trail) */
2092 CUSTOMER_TRX_LINE_ID lid
2093 from AR_DEFERRED_LINES_ALL trail';
2094 else
2095 -- Bug 4942753: Changed to select from dba_tables instead of all_tables
2096 -- and changed to return 1 if any row exists
2097 BEGIN
2098 select 1 into l_count from dba_tables
2099 where table_name = 'AR_RAMC_AUDIT_TRAIL'
2100 and owner = k_ar_schema
2101 and rownum = 1;
2102 EXCEPTION
2103 WHEN NO_DATA_FOUND THEN
2104 l_count := 0;
2105 END;
2106
2107 if l_count = 0 then
2108 l_stmt2 := '';
2109 else
2110 l_stmt2 := '
2111 union all
2112 select /*+ parallel(trail) full(trail) */
2113 CUSTOMER_TRX_LINE_ID lid
2114 from AR_RAMC_AUDIT_TRAIL trail';
2115 end if;
2116 end if;
2117
2118 l_stmt := '
2119 INSERT /*+ APPEND PARALLEL(F) */ INTO FII_AR_REVENUE_ID F
2120 (
2121 view_type_id,
2122 job_num,
2123 primary_key1
2124 )
2125 select /*+ no_merge parallel(z) */ distinct 4,
2126 1,
2127 lid
2128 from (
2129 select /*+ no_merge PARALLEL(A) */
2130 a.invoice_line_id lid
2131 from FII_AR_REVENUE_STG A
2132 union all
2133 select /*+ ordered parallel(b) parallel(y)
2134 pq_distibute(y, none, broadcast) */
2135 decode(y.a,
2136 1,
2137 b.from_cust_trx_line_id,
2138 b.to_cust_trx_line_id) lid
2139 from ( select /*+ no_merge */ 1 a, org_id
2140 from ar_system_parameters_all
2141 union all
2142 select 2 a, org_id
2143 from ar_system_parameters_all ) y,
2144 ar_revenue_adjustments_all b
2145 where y.org_id = b.org_id
2146 union all
2147 select /*+ PARALLEL(line) PARALLEL(rule) */
2148 line.CUSTOMER_TRX_LINE_ID lid
2149 from ra_customer_trx_lines_all line,
2150 RA_RULES rule
2151 where line.ACCOUNTING_RULE_ID = rule.RULE_ID
2152 and rule.DEFERRED_REVENUE_FLAG = ''Y''' ||l_stmt2|| '
2153 ) z
2154 where lid is not null ';
2155
2156 if g_debug_flag = 'Y' then
2157 fii_util.put_line('');
2158 -- fii_util.put_line(l_stmt);
2159 fii_util.start_timer;
2160 end if;
2161
2162 EXECUTE IMMEDIATE l_stmt;
2163 l_count := SQL%ROWCOUNT;
2164
2165 if g_debug_flag = 'Y' then
2166 fii_util.put_line('');
2167 fii_util.stop_timer;
2168 fii_util.print_timer('Duration');
2169 end if;
2170
2171 END IF;
2172
2173 COMMIT;
2174
2175 FND_STATS.gather_table_stats
2176 (ownname => g_fii_schema,
2177 tabname => 'FII_AR_REVENUE_ID');
2178
2179 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
2180
2181 fii_util.put_line('Altering session enable parallel DML after FND_STATS call, bug 4127183.');
2182
2183 RETURN(l_count);
2184
2185 EXCEPTION
2186 WHEN OTHERS THEN
2187 g_retcode := -2;
2188 g_errbuf := '
2189 ---------------------------------
2190 Error in Procedure: IDENTIFY_CHANGE_INIT
2191 Parameter: p_type='||p_type||'
2192 Section: '||g_section||'
2193 Message: '||sqlerrm;
2194 RAISE g_procedure_failure;
2195
2196 END IDENTIFY_CHANGE_INIT;
2197
2198 -----------------------------------------------------------
2199 -- FUNCTION CHECK_SLG_ASSIGNMENT
2200 -----------------------------------------------------------
2201 FUNCTION CHECK_SLG_ASSIGNMENT RETURN BOOLEAN IS
2202 l_result VARCHAR2(20);
2203 l_count1 number := 0;
2204 l_count2 number := 0;
2205 BEGIN
2206 -- g_section := 'section 10';
2207
2208 -- if g_debug_flag = 'Y' then
2209 -- fii_util.put_line(g_section);
2210 -- end if;
2211
2212 SELECT NVL(item_value, 'N')
2213 INTO l_result
2214 FROM fii_change_log
2215 WHERE log_item = 'AR_RESUMMARIZE';
2216
2217 IF l_result = 'Y' THEN
2218 -- Bug 4942753: Change to return 1 if any row exists
2219 BEGIN
2220 SELECT 1
2221 INTO l_count1
2222 FROM fii_ar_revenue_b
2223 WHERE ROWNUM = 1;
2224 EXCEPTION
2225 WHEN NO_DATA_FOUND THEN
2226 l_count1 := 0;
2227 END;
2228
2229 -- Bug 4942753: Change to return 1 if any row exists
2230 BEGIN
2231 SELECT 1
2232 INTO l_count2
2233 FROM fii_ar_revenue_stg
2234 WHERE ROWNUM = 1;
2235 EXCEPTION
2236 WHEN NO_DATA_FOUND THEN
2237 l_count2 := 0;
2238 END;
2239
2240 IF (l_count1 = 0 AND l_count2 = 0) then
2241 UPDATE fii_change_log
2242 SET item_value = 'N',
2243 last_update_date = SYSDATE,
2244 last_update_login = g_fii_login_id,
2245 last_updated_by = g_fii_user_id
2246 WHERE log_item = 'AR_RESUMMARIZE'
2247 AND item_value = 'Y';
2248
2249 COMMIT;
2250 RETURN FALSE;
2251 ELSE
2252 RETURN TRUE;
2253 END IF;
2254 END IF;
2255
2256 RETURN FALSE;
2257
2258 EXCEPTION
2259 WHEN NO_DATA_FOUND THEN
2260 RETURN FALSE;
2261
2262 WHEN OTHERS THEN
2263 g_retcode := -1;
2264 g_errbuf := '
2265 ---------------------------------
2266 Error in Procedure: CHECK_SLG_ASSIGNMENT
2267 Message: '||sqlerrm;
2268 RAISE g_procedure_failure;
2269
2270 END CHECK_SLG_ASSIGNMENT;
2271
2272 -----------------------------------------------------------
2273 -- FUNCTION CHECK_PRODUCT_ASSIGNMENT
2274 -----------------------------------------------------------
2275 FUNCTION CHECK_PRODUCT_ASSIGNMENT RETURN BOOLEAN IS
2276 l_result VARCHAR2(20);
2277 l_count1 number := 0;
2278 l_count2 number := 0;
2279 BEGIN
2280 -- g_section := 'section 10';
2281
2282 -- if g_debug_flag = 'Y' then
2283 -- fii_util.put_line(g_section);
2284 -- end if;
2285
2286 SELECT NVL(item_value, 'N')
2287 INTO l_result
2288 FROM fii_change_log
2289 WHERE log_item = 'AR_PROD_CHANGE';
2290
2291 IF l_result = 'Y' THEN
2292 -- Bug 4942753: Change to return 1 if any row exists
2293 BEGIN
2294 SELECT 1
2295 INTO l_count1
2296 FROM fii_ar_revenue_b
2297 WHERE ROWNUM = 1;
2298 EXCEPTION
2299 WHEN NO_DATA_FOUND THEN
2300 l_count1 := 0;
2301 END;
2302
2303 -- Bug 4942753: Change to return 1 if any row exists
2304 BEGIN
2305 SELECT 1
2306 INTO l_count2
2307 FROM fii_ar_revenue_stg
2308 WHERE ROWNUM = 1;
2309 EXCEPTION
2310 WHEN NO_DATA_FOUND THEN
2311 l_count2 := 0;
2312 END;
2313
2314 IF (l_count1 = 0 AND l_count2 = 0) then
2315 UPDATE fii_change_log
2316 SET item_value = 'N',
2317 last_update_date = SYSDATE,
2318 last_update_login = g_fii_login_id,
2319 last_updated_by = g_fii_user_id
2320 WHERE log_item = 'AR_PROD_CHANGE'
2321 AND item_value = 'Y';
2322
2323 COMMIT;
2324 RETURN FALSE;
2325 ELSE
2326 RETURN TRUE;
2327 END IF;
2328 END IF;
2329
2330 RETURN FALSE;
2331
2332 EXCEPTION
2333 WHEN NO_DATA_FOUND THEN
2334 RETURN FALSE;
2335
2336 WHEN OTHERS THEN
2337 g_retcode := -1;
2338 g_errbuf := '
2339 ---------------------------------
2340 Error in Procedure: CHECK_PRODUCT_ASSIGNMENT
2341 Message: '||sqlerrm;
2342 RAISE g_procedure_failure;
2343
2344 END CHECK_PRODUCT_ASSIGNMENT;
2345
2346 -----------------------------------------------------------
2347 -- PROCEDURE REGISTER_PREP_JOBS
2348 -----------------------------------------------------------
2349 PROCEDURE REGISTER_PREP_JOBS IS
2350 l_from_temp DATE :=Null;
2351 l_to_temp DATE :=Null;
2352 l_count NUMBER := 0;
2353
2354 BEGIN
2355
2356 -- ----------------------------------------------
2357 -- Registering first set of jobs. Includes
2358 -- identifying changed records as well as
2359 -- updating processing tables
2360 -- ----------------------------------------------
2361 g_section := 'Section 10';
2362 if g_debug_flag = 'Y' then
2363 fii_util.put_line(' ');
2364 fii_util.put_line('Registering jobs to handle preperation work');
2365 fii_util.start_timer;
2366 end if;
2367
2368 -- --------------------------------------------------
2369 -- Registering AR change identification as separate
2370 -- jobs broken out by time ranges due to volume
2371 -- --------------------------------------------------
2372 if g_debug_flag = 'Y' then
2373 fii_util.put_line('');
2374 fii_util.put_line('Registering jobs for AR INV, AR ADJ trans types: '||
2375 to_char(g_gl_from_date,'YYYY/MM/DD HH24:MI:SS')||' to '||
2376 to_char(g_gl_to_date,'YYYY/MM/DD HH24:MI:SS'));
2377 end if;
2378
2379
2380 -- haritha
2381 /* Register jobs to get future-dated transactions for 'AR INV' and
2382 'AR ADJ' */
2383 INSERT INTO FII_AR_REVENUE_JOBS (
2384 function,
2385 phase,
2386 priority,
2387 date_parameter1,
2388 date_parameter2,
2389 date_parameter3,
2390 date_parameter4,
2391 char_parameter1,
2392 status)
2393 SELECT
2394 'IDENTIFY_CHANGE',
2395 1,
2396 t.priority,
2397 g_gl_from_date,
2398 g_gl_to_date,
2399 g_lud_from_date,
2400 g_lud_to_date,
2401 t.data_type,
2402 'UNASSIGNED'
2403 FROM (SELECT 'AR INV' data_type, 1 priority FROM DUAL UNION ALL
2404 SELECT 'AR ADJ' data_type, 3 priority FROM DUAL) t;
2405
2406 l_count := l_count + 2;
2407
2408 COMMIT;
2409
2410 -- --------------------------------------------------
2411 -- Registering change identification for rest of
2412 -- other data types
2413 -- --------------------------------------------------
2414 g_section := 'Section 20';
2415
2416 if g_debug_flag = 'Y' then
2417 fii_util.put_line('');
2418 fii_util.put_line('Registering jobs for AR DL trans type: '||
2419 to_char(g_gl_from_date,'YYYY/MM/DD HH24:MI:SS')||' to '||
2420 to_char(g_gl_to_date,'YYYY/MM/DD HH24:MI:SS'));
2421 end if;
2422
2423 l_from_temp := g_gl_from_date;
2424 l_to_temp := least(sysdate,last_day(l_from_temp), l_from_temp+INTERVAL);
2425
2426 WHILE (l_from_temp <= sysdate )
2427 LOOP
2428
2429 INSERT INTO FII_AR_REVENUE_JOBS (
2430 function,
2431 phase,
2432 priority,
2433 date_parameter1,
2434 date_parameter2,
2435 date_parameter3,
2436 date_parameter4,
2437 char_parameter1,
2438 status)
2439 VALUES (
2440 'IDENTIFY_CHANGE',
2441 1,
2442 3,
2443 l_from_temp,
2444 l_to_temp,
2445 g_lud_from_date,
2446 g_lud_to_date,
2447 'AR DL',
2448 'UNASSIGNED');
2449
2450 l_from_temp := l_to_temp + ONE_SECOND;
2451 l_to_temp := least(sysdate,last_day(l_to_temp+1),l_to_temp+INTERVAL);
2452 l_count := l_count + 1;
2453 END LOOP;
2454
2455 COMMIT;
2456
2457 -- haritha
2458 /* Register jobs to get future-dated transactions for AR DL*/
2459
2460 INSERT INTO FII_AR_REVENUE_JOBS (
2461 function,
2462 phase,
2463 priority,
2464 date_parameter1,
2465 date_parameter2,
2466 date_parameter3,
2467 date_parameter4,
2468 char_parameter1,
2469 status)
2470 VALUES (
2471 'IDENTIFY_CHANGE',
2472 1,
2473 3,
2474 sysdate + ONE_SECOND,
2475 g_gl_to_date,
2476 g_lud_from_date,
2477 g_lud_to_date,
2478 'AR DL',
2479 'UNASSIGNED');
2480
2481 l_count := l_count + 1;
2482
2483
2484 if g_debug_flag = 'Y' then
2485 fii_util.put_line('');
2486 fii_util.put_line('Registered jobs for AR DL: '||l_count||' jobs covering '||
2487 to_char(g_gl_from_date,'YYYY/MM/DD HH24:MI:SS')||' to '||
2488 to_char(g_gl_to_date,'YYYY/MM/DD HH24:MI:SS'));
2489 end if;
2490
2491
2492 -- --------------------------------------------------
2493 -- Registering remaining miscellaneous jobs
2494 -- --------------------------------------------------
2495 g_section := 'Section 30';
2496 if g_debug_flag = 'Y' then
2497 fii_util.put_line('');
2498 fii_util.put_line('Registering remaining misc jobs ');
2499 end if;
2500
2501 INSERT INTO FII_AR_REVENUE_JOBS (
2502 function,
2503 phase,
2504 priority,
2505 date_parameter1,
2506 date_parameter2,
2507 status)
2508 SELECT
2509 function,
2510 phase,
2511 priority,
2512 g_gl_from_date,
2513 g_gl_to_date,
2514 'UNASSIGNED'
2515 FROM (select 'VERIFY_CCID_UP_TO_DATE' function, 2 phase, 1 priority from dual union all
2516 select 'REGISTER_EXTRACT_JOBS' function, 3 phase, 1 priority from dual) t;
2517 -- union all select 'DETECT_DELETED_INV' function, 4 phase, 2 priority from dual ) t;
2518
2519 l_count := l_count + 2;
2520
2521 if g_debug_flag = 'Y' then
2522 fii_util.put_line(' ');
2523 fii_util.put_line('Registered '||l_count||' jobs');
2524 fii_util.stop_timer;
2525 fii_util.print_timer('Duration');
2526 end if;
2527
2528
2529 EXCEPTION
2530 WHEN OTHERS THEN
2531 g_retcode := -2;
2532 g_errbuf := '
2533 ---------------------------------
2534 Error in Procedure: REGISTER_PREP_JOBS
2535 Section: '||g_section||'
2536 Message: '||sqlerrm;
2537 RAISE g_procedure_failure;
2538
2539 END REGISTER_PREP_JOBS;
2540
2541
2542
2543
2544 -----------------------------------------------------------
2545 -- PROCEDURE REGISTER_EXTRACT_JOBS
2546 -----------------------------------------------------------
2547 PROCEDURE REGISTER_EXTRACT_JOBS IS
2548 -- Constants
2549 BATCH_SIZE CONSTANT NUMBER := 2000000; -- double 1000000
2550 AR_ADJ_FACTOR CONSTANT NUMBER := 9; -- 9 joins * 0.14 ct:adj ratio *
2551 AR_FACTOR CONSTANT NUMBER := 377; -- 10 joins * 37.7 ct:ctlgd ratio
2552
2553 -- Variables
2554 TYPE Batch_Rec IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2555
2556 l_batch_size Batch_Rec;
2557 l_stmt VARCHAR2(200);
2558 l_count NUMBER;
2559 l_priority NUMBER;
2560 l_curr_batch NUMBER;
2561 l_curr_job_num NUMBER;
2562
2563 BEGIN
2564
2565 -- --------------------------------------
2566 -- Set batch sizes for each type of data
2567 -- --------------------------------------
2568 g_section := 'Section 10';
2569 l_batch_size(3) := TRUNC(BATCH_SIZE / AR_ADJ_FACTOR);
2570 l_batch_size(4) := TRUNC(BATCH_SIZE / AR_FACTOR);
2571
2572 -- ---------------------------------
2573 -- Loop to register the jobs
2574 -- Drop index to improve performance
2575 -- ---------------------------------
2576 g_section := 'Section 50';
2577 if g_debug_flag = 'Y' then
2578 fii_util.put_line('');
2579 fii_util.put_line('Register extraction jobs');
2580 end if;
2581
2582 l_curr_job_num := 1;
2583 FOR l_view_type_id in 3..4 LOOP
2584
2585 g_section := 'Section 60';
2586 if g_debug_flag = 'Y' then
2587 fii_util.start_timer;
2588 end if;
2589
2590
2591 insert into fii_ar_revenue_id (
2592 view_type_id,
2593 job_num,
2594 primary_key1)
2595 select
2596 l_view_type_id,
2597 l_curr_job_num + ceil(rownum / l_batch_size(l_view_type_id)) - 1,
2598 primary_key1
2599 from (select distinct
2600 primary_key1
2601 from fii_ar_revenue_id
2602 where view_type_id = l_view_type_id + 4) t;
2603
2604
2605 l_count := nvl(SQL%ROWCOUNT,0);
2606 if g_debug_flag = 'Y' then
2607 fii_util.put_line('');
2608 fii_util.put_timestamp;
2609 fii_util.put_line('Registered '||
2610 ceil(l_count / l_batch_size(l_view_type_id))||
2611 ' job(s) for view type '||l_view_type_id);
2612 fii_util.stop_timer;
2613 fii_util.print_timer('Duration');
2614 end if;
2615 commit;
2616
2617 WHILE (l_count > 0) LOOP
2618
2619 g_section := 'Section 70';
2620
2621 -- -----------------------------------
2622 -- priority set based on batch size
2623 -- -----------------------------------
2624 l_curr_batch := least(l_batch_size(l_view_type_id), l_count);
2625
2626 IF (l_curr_batch >= 0.75 * l_batch_size(l_view_type_id)) THEN
2627 l_priority := 1;
2628 ELSIF (l_curr_batch <= 0.25 * l_batch_size(l_view_type_id)) THEN
2629 l_priority := 3;
2630 ELSE
2631 l_priority := 2;
2632 END IF;
2633
2634 insert into FII_AR_REVENUE_JOBS (
2635 function,
2636 phase,
2637 priority,
2638 number_parameter1,
2639 number_parameter2,
2640 status)
2641 values (
2642 'POPULATE_STG',
2643 4,
2644 l_priority,
2645 l_view_type_id,
2646 l_curr_job_num,
2647 'UNASSIGNED' );
2648
2649 l_curr_job_num := l_curr_job_num + 1;
2650 l_count := l_count - l_curr_batch;
2651
2652 END LOOP;
2653 commit;
2654
2655 END LOOP;
2656
2657 return;
2658 EXCEPTION
2659 WHEN OTHERS THEN
2660 g_retcode := -2;
2661 g_errbuf := '
2662 ---------------------------------
2663 Error in Procedure: REGISTER_EXTRACT_JOBS
2664 Section: '||g_section||'
2665 Message: '||sqlerrm;
2666 raise g_procedure_failure;
2667
2668 END REGISTER_EXTRACT_JOBS;
2669
2670 ---------------------------------------------------------------
2671 -- PROCEDURE VERIFY_CCID_UP_TO_DATE
2672 ---------------------------------------------------------------
2673 PROCEDURE VERIFY_CCID_UP_TO_DATE IS
2674 l_errbuf VARCHAR2(1000);
2675 l_retcode VARCHAR2(100);
2676 l_request_id NUMBER;
2677 l_result BOOLEAN;
2678 l_phase VARCHAR2(500) := NULL;
2679 l_status VARCHAR2(500) := NULL;
2680 l_devphase VARCHAR2(500) := NULL;
2681 l_devstatus VARCHAR2(500) := NULL;
2682 l_message VARCHAR2(500) := NULL;
2683 l_dummy boolean;
2684 l_submit_failed EXCEPTION;
2685 l_call_status boolean;
2686
2687 BEGIN
2688 -- DEBUG
2689 -- return;
2690
2691 if g_debug_flag = 'Y' then
2692 FII_UTIL.put_line('Calling Procedure: VERIFY_CCID_UP_TO_DATE');
2693 FII_UTIL.put_line('');
2694 end if;
2695
2696 g_section := 'Section 10';
2697 -- if g_debug_flag = 'Y' then
2698 -- FII_UTIL.put_line(g_section);
2699 -- end if;
2700
2701 IF(FII_GL_CCID_C.NEW_CCID_IN_GL) THEN
2702 if g_debug_flag = 'Y' then
2703 FII_UTIL.put_line('CCID Dimension is not up to date, calling CCID Dimension update
2704 program');
2705 end if;
2706
2707 l_dummy := FND_REQUEST.SET_MODE(TRUE);
2708
2709 l_request_id := FND_REQUEST.SUBMIT_REQUEST('FII',
2710 'FII_GL_CCID_C',
2711 NULL, NULL, FALSE, 'I');
2712
2713 commit;
2714
2715 IF (l_request_id = 0) THEN
2716 rollback;
2717 g_retcode := -1;
2718 raise G_NO_CHILD_PROCESS;
2719 END IF;
2720
2721 g_section := 'Section 20';
2722
2723 l_call_status := FND_CONCURRENT.wait_for_request(l_request_id,
2724 30, -- interval 30 seconds
2725 3600, -- waiting max 1 hour
2726 l_phase,
2727 l_status,
2728 l_devphase,
2729 l_devstatus,
2730 l_message);
2731
2732 if g_debug_flag = 'Y' then
2733 FII_UTIL.put_line('devphase : ' || l_devphase || ' devstatus: ' || l_devstatus);
2734 end if;
2735
2736 IF (NVL(l_devphase='COMPLETE' AND l_devstatus='NORMAL', FALSE)) THEN
2737 if g_debug_flag = 'Y' then
2738 FII_UTIL.put_line('CCID Dimension populated successfully');
2739 end if;
2740 ELSE
2741 -- if g_debug_flag = 'Y' then
2742 FII_UTIL.put_line('CCID Dimension populated unsuccessfully');
2743 -- end if;
2744 raise G_CCID_FAILED;
2745 END IF;
2746
2747 ELSE
2748 if g_debug_flag = 'Y' then
2749 FII_UTIL.put_line('CCID Dimension is up to date');
2750 FII_UTIL.put_line('');
2751 end if;
2752 END IF;
2753
2754 Exception
2755 WHEN G_NO_CHILD_PROCESS THEN
2756 g_retcode := -1;
2757 FII_UTIL.put_line('
2758 ----------------------------
2759 Error in Procedure : VERIFY_CCID_UP_TO_DATE
2760 Phase: Submitting Child process to run CCID program');
2761 raise;
2762 WHEN G_CCID_FAILED THEN
2763 g_retcode := -1;
2764 FII_UTIL.put_line('
2765 ----------------------------
2766 Error in Procedure : VERIFY_CCID_UP_TO_DATE
2767 Phase: Running CCID program');
2768 raise;
2769 WHEN OTHERS Then
2770 g_retcode := -1;
2771 FII_UTIL.put_line('
2772 ----------------------------
2773 Error in Procedure : VERIFY_CCID_UP_TO_DATE
2774 Section: ' || g_section || '
2775 Message: '||sqlerrm);
2776 raise;
2777 END VERIFY_CCID_UP_TO_DATE;
2778
2779 ---------------------------------------------------
2780 -- FUNCTION LAUNCH_WORKER
2781 ---------------------------------------------------
2782
2783 FUNCTION LAUNCH_WORKER(p_worker_no NUMBER) RETURN NUMBER IS
2784 l_request_id NUMBER;
2785
2786
2787 BEGIN
2788 -- DEBUG
2789 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
2790 'FII',
2791 'FII_AR_REVENUE_B_C_SUBWORKER',
2792 NULL,
2793 NULL,
2794 FALSE, -- sub request,may need to set true
2795 p_worker_no);
2796
2797 IF (l_request_id = 0) THEN
2798 rollback;
2799 g_retcode := -2;
2800 g_errbuf := '
2801 ---------------------------------
2802 Error in Procedure: LAUNCH_WORKER
2803 Message: '||fnd_message.get;
2804 raise g_procedure_failure;
2805
2806 END IF;
2807
2808 RETURN l_request_id;
2809
2810 EXCEPTION
2811 WHEN OTHERS THEN
2812 rollback;
2813 g_retcode := -2;
2814 g_errbuf := '
2815 ---------------------------------
2816 Error in Procedure: LAUNCH_WORKER
2817 Message: '||sqlerrm;
2818 raise g_procedure_failure;
2819
2820 END LAUNCH_WORKER;
2821
2822
2823
2824 ---------------------------------------------------
2825 -- FUNCTION DETECT_DELETED_INV
2826 ---------------------------------------------------
2827 FUNCTION DETECT_DELETED_INV RETURN NUMBER IS
2828 l_gl_from VARCHAR2(80);
2829 l_gl_to VARCHAR2(80);
2830 l_stmt VARCHAR2(1500);
2831 l_count NUMBER;
2832 l_instance_fk_key NUMBER;
2833 BEGIN
2834
2835 drop_table('fii_ar_revenue_sum_del1');
2836 drop_table('fii_ar_revenue_sum_del2');
2837
2838 l_gl_from := 'to_date('''||to_char(g_gl_from_date,'YYYY/MM/DD HH24:MI:SS')||
2839 ''',''YYYY/MM/DD HH24:MI:SS'')';
2840 l_gl_to := 'to_date('''||to_char(g_gl_to_date,'YYYY/MM/DD HH24:MI:SS')||
2841 ''',''YYYY/MM/DD HH24:MI:SS'')';
2842
2843 g_section := 'Section 20';
2844 l_stmt:='
2845 CREATE TABLE '||g_fii_schema||'.fii_ar_revenue_sum_del1
2846 TABLESPACE '||g_tablespace||'
2847 NOLOGGING storage (initial 4K next 16K MAXEXTENTS UNLIMITED) as
2848 select distinct
2849 invoice_id
2850 from FII_AR_REVENUE_B
2851 where transaction_class <> ''ADJ'' ';
2852
2853 if g_debug_flag = 'Y' then
2854 fii_util.put_line('');
2855 fii_util.put_line('Process step 1');
2856 fii_util.start_timer;
2857 fii_util.put_line('');
2858 fii_util.put_line(l_stmt);
2859 end if;
2860 execute immediate l_stmt;
2861 if g_debug_flag = 'Y' then
2862 fii_util.put_line(' Processed '||SQL%ROWCOUNT||' rows');
2863 fii_util.stop_timer;
2864 fii_util.print_timer('Duration');
2865 end if;
2866 commit;
2867
2868 g_section := 'Section 30';
2869 l_stmt:='
2870 CREATE TABLE '||g_fii_schema||'.fii_ar_revenue_sum_del2
2871 TABLESPACE '||g_tablespace||'
2872 NOLOGGING storage (initial 4K next 16K MAXEXTENTS UNLIMITED) as
2873 select
2874 wh.invoice_id invoice_id
2875 from '||g_fii_schema||'.fii_ar_revenue_sum_del1 wh,
2876 ra_customer_trx_all trx
2877 where wh.invoice_id = trx.customer_trx_id (+)
2878 AND trx.customer_trx_id IS NULL ';
2879
2880 if g_debug_flag = 'Y' then
2881 fii_util.put_line('');
2882 fii_util.put_line('Process step 2');
2883 fii_util.start_timer;
2884 fii_util.put_line('');
2885 fii_util.put_line(l_stmt);
2886 fii_util.put_line('');
2887 end if;
2888 execute immediate l_stmt;
2889 if g_debug_flag = 'Y' then
2890 fii_util.put_line(' Processed '||SQL%ROWCOUNT||' rows');
2891 fii_util.stop_timer;
2892 fii_util.print_timer('Duration');
2893 end if;
2894 commit;
2895
2896
2897 g_section := 'Section 40';
2898 l_stmt:='
2899 delete from FII_AR_REVENUE_B
2900 where transaction_class <> ''ADJ''
2901 and invoice_id in (select invoice_id
2902 FROM '||g_fii_schema||'.fii_ar_revenue_sum_del2) ';
2903
2904 if g_debug_flag = 'Y' then
2905 fii_util.put_line('');
2906 fii_util.put_line('Process step 3');
2907 fii_util.start_timer;
2908 fii_util.put_line('');
2909 fii_util.put_line(l_stmt);
2910 fii_util.put_line('');
2911 end if;
2912 execute immediate l_stmt;
2913 l_count := SQL%ROWCOUNT;
2914 if g_debug_flag = 'Y' then
2915 fii_util.put_line('Identified '||l_count||' invoices deleted in transaction system');
2916 fii_util.stop_timer;
2917 fii_util.print_timer('Duration');
2918 end if;
2919 commit;
2920
2921 drop_table('fii_ar_revenue_sum_del1');
2922 drop_table('fii_ar_revenue_sum_del2');
2923
2924 return(l_count);
2925
2926 EXCEPTION
2927 WHEN OTHERS THEN
2928 rollback;
2929 g_retcode := -2;
2930 g_errbuf := '
2931 ---------------------------------
2932 Error in Procedure: DETECT_DELETED_INV
2933 Section: '||g_section||'
2934 Message: '||sqlerrm;
2935 raise g_procedure_failure;
2936
2937 END DETECT_DELETED_INV;
2938
2939
2940
2941 -- -------------------------------
2942 -- PROCEDURE DUPLICATE_RECORDS
2943 -- identifying and deleting duplicate records from the base summary table
2944 ---------------------------------
2945
2946
2947 -----------------------------------------------------------
2948 --PROCEDURE CHILD_SETUP
2949 -----------------------------------------------------------
2950 PROCEDURE CHILD_SETUP(p_object_name VARCHAR2) IS
2951 l_dir VARCHAR2(400);
2952 l_stmt VARCHAR2(100);
2953 BEGIN
2954
2955
2956 ------------------------------------------------------
2957 -- Set default directory in case if the profile option
2958 -- BIS_DEBUG_LOG_DIRECTORY is not set up
2959 ------------------------------------------------------
2960 l_dir:=FII_UTIL.get_utl_file_dir;
2961 -- DEBUG
2962 -- l_dir:='/sqlcom/log/olaptrw';
2963
2964 ----------------------------------------------------------------
2965 -- fii_util.initialize will get profile options FII_DEBUG_MODE
2966 -- and BIS_DEBUG_LOG_DIRECTORY and set up the directory where
2967 -- the log files and output files are written to
2968 ----------------------------------------------------------------
2969 fii_util.initialize(p_object_name||'.log',p_object_name||'.out',l_dir, 'FII_AR_REVENUE_B_C_SUBWORKER');
2970
2971 g_fii_user_id := FND_GLOBAL.User_Id;
2972 g_fii_login_id := FND_GLOBAL.Login_Id;
2973
2974 EXCEPTION
2975 WHEN OTHERS THEN
2976 rollback;
2977 g_retcode := -1;
2978 g_errbuf := '
2979 ---------------------------------
2980 Error in Procedure: CHILD_SETUP
2981 Message: '||sqlerrm;
2982 raise g_procedure_failure;
2983 END CHILD_SETUP;
2984
2985 ------------------------------------------------------------------------------
2986 -- FUNCTION REV_ACCTS_CHANGED
2987 -- Check whether there are any deletions to natural accounts assigned
2988 -- to 'R' or 'DR'.
2989 -- If yes, then give a message to truncate the base summary table and
2990 -- re-run the load program.
2991 ------------------------------------------------------------------------------
2992 FUNCTION REV_ACCTS_CHANGED RETURN BOOLEAN IS
2993 l_stmt varchar2(2000);
2994 l_change_1 number :=0;
2995
2996 BEGIN
2997 ---------------------------------------------------------
2998 -- to check whether there are any deletions to natural
2999 -- accounts assigned to 'Revenue'. Only when deletion
3000 -- happens do we ask the user to truncate Revenue summary
3001 -- and reload. If new accounts were added to 'Revenue'
3002 -- users do not need to truncate Revenue summary, this
3003 -- program will insert the new accounts into FII_AR_REV_ACCTS
3004 -- after it populates the Revenue summary table
3005 ---------------------------------------------------------
3006 g_section := 'Section 10';
3007 drop_table('fii_ar_rev_accts_temp');
3008
3009 if g_debug_flag = 'Y' then
3010 FII_UTIL.put_line('Creating temp table FII_AR_REV_ACCTS_TEMP');
3011 fii_util.start_timer;
3012 end if;
3013
3014 g_section := 'Section 20';
3015
3016 /*
3017 l_stmt:='
3018 CREATE TABLE '||g_fii_schema||'.fii_ar_rev_accts_temp
3019 TABLESPACE '||g_tablespace||'
3020 NOLOGGING STORAGE (INITIAL 4K NEXT 16K MAXEXTENTS UNLIMITED) AS
3021 SELECT fin_category_id cur_rev_acct_id,
3022 fin_cat_type_code cur_fin_cat_type_code
3023 FROM fii_fin_cat_type_assgns ffcta
3024 WHERE ffcta.fin_cat_type_code in (''R'', ''DR'') ';
3025 */
3026 l_stmt:='
3027 CREATE TABLE '||g_fii_schema||'.fii_ar_rev_accts_temp
3028 TABLESPACE '||g_tablespace||'
3029 NOLOGGING STORAGE (INITIAL 4K NEXT 16K MAXEXTENTS UNLIMITED) AS
3030 WITH ACCNT_CLASS AS (SELECT XAD.Ledger_ID,
3031 XACA.Accounting_Class_Code,
3032 decode(XAD.Program_Code,
3033 '''||g_program_code_R||''', ''R'',
3034 '''||g_program_code_DR||''', ''DR'',
3035 NULL) Fin_Cat_Type_Code
3036 FROM XLA_Assignment_Defns_B XAD,
3037 XLA_Acct_Class_Assgns XACA
3038 WHERE XAD.Program_Code in ('''||g_program_code_R||''',
3039 '''||g_program_code_DR||''')
3040 AND XAD.Enabled_Flag = ''Y''
3041 AND XAD.Program_Code = XACA.Program_Code
3042 AND XAD.Assignment_Code = XACA.Assignment_Code)
3043 SELECT * FROM ACCNT_CLASS ';
3044
3045 EXECUTE IMMEDIATE l_stmt;
3046
3047 if g_debug_flag = 'Y' then
3048 fii_util.stop_timer;
3049 fii_util.print_timer('Duration');
3050 end if;
3051
3052 g_section := 'Section 30';
3053
3054 /*
3055 l_stmt:= '
3056 SELECT COUNT(*)
3057 FROM fii_ar_rev_accts fra,
3058 '||g_fii_schema||'.fii_ar_rev_accts_temp temp
3059 WHERE fra.rev_acct_id = temp.cur_rev_acct_id(+)
3060 AND fra.fin_cat_type_code = temp.cur_fin_cat_type_code(+)
3061 AND temp.cur_rev_acct_id IS NULL ';
3062 */
3063 l_stmt:= '
3064 SELECT COUNT(*)
3065 FROM fii_ar_rev_accts fra
3066 WHERE NOT EXISTS (
3067 SELECT 1
3068 FROM '||g_fii_schema||'.fii_ar_rev_accts_temp temp
3069 WHERE ( fra.rev_acct_id = temp.Ledger_ID
3070 OR temp.Ledger_ID IS NULL )
3071 AND fra.rev_acct = temp.Accounting_Class_Code
3072 AND fra.fin_cat_type_code = temp.Fin_Cat_Type_Code
3073 ) ';
3074
3075 EXECUTE IMMEDIATE l_stmt INTO l_change_1;
3076
3077 IF l_change_1 > 0 THEN
3078 RETURN TRUE;
3079 ELSE
3080 RETURN FALSE;
3081 END IF;
3082
3083 EXCEPTION
3084 WHEN OTHERS THEN
3085 ROLLBACK;
3086 g_retcode := -2;
3087 g_errbuf := '
3088 ---------------------------------
3089 Error in Procedure: REV_ACCTS_CHANGED
3090 Section: '||g_section||'
3091 Message: '||sqlerrm;
3092 RAISE g_procedure_failure;
3093 END REV_ACCTS_CHANGED;
3094
3095 PROCEDURE AR_STG (p_sort_area_size IN NUMBER,
3096 p_hash_area_size IN NUMBER,
3097 p_parallel_query IN NUMBER) IS
3098
3099 l_stmt VARCHAR2(1000);
3100 l_section VARCHAR2(20) := NULL;
3101
3102 BEGIN
3103
3104 l_section := 'Section 10';
3105
3106 l_stmt := 'alter session set workarea_size_policy=manual';
3107 execute immediate l_stmt;
3108 l_stmt := 'alter session set sort_area_size=' || p_sort_area_size;
3109 execute immediate l_stmt;
3110 l_stmt := 'alter session set hash_area_size= ' ||p_hash_area_size;
3111 execute immediate l_stmt;
3112
3113
3114 l_section := 'Section 20';
3115
3116 if g_debug_flag = 'Y' then
3117 fii_util.put_line(' ');
3118 fii_util.put_line('Loading data into staging table');
3119 fii_util.start_timer;
3120 fii_util.put_line('');
3121 end if;
3122
3123 -- Bug 4942753: Per Lester's suggestion, reordered the XLA tables and
3124 -- add date filters on the transaction tables adj and ctlgd
3125 INSERT /*+ APPEND PARALLEL(F) */ INTO FII_AR_REVENUE_STG F (
3126 REVENUE_PK,
3127 GL_DATE_ID,
3128 GL_DATE,
3129 INVENTORY_ITEM_ID,
3130 OPERATING_UNIT_ID,
3131 COMPANY_ID,
3132 COST_CENTER_ID,
3133 INVOICE_NUMBER,
3134 INVOICE_DATE,
3135 ORDER_LINE_ID,
3136 BILL_TO_PARTY_ID,
3137 FUNCTIONAL_CURRENCY,
3138 TRANSACTION_CURRENCY,
3139 LEDGER_ID,
3140 INVOICE_ID,
3141 AMOUNT_T,
3142 AMOUNT_B,
3143 EXCHANGE_DATE,
3144 TOP_MODEL_ITEM_ID,
3145 ORGANIZATION_ID,
3146 item_organization_id,
3147 om_product_revenue_flag,
3148 TRANSACTION_CLASS,
3149 FIN_CATEGORY_ID,
3150 INVOICE_LINE_ID,
3151 SALES_CHANNEL,
3152 ORDER_NUMBER,
3153 POSTED_FLAG,
3154 PRIM_CONVERSION_RATE,
3155 SEC_CONVERSION_RATE,
3156 PROD_CATEGORY_ID,
3157 CHART_OF_ACCOUNTS_ID,
3158 FIN_CAT_TYPE_CODE,
3159 REV_BOOKED_DATE,
3160 CHILD_ORDER_LINE_ID)
3161 WITH ACCNT_CLASS AS (SELECT XAD.Ledger_ID,
3162 XACA.Accounting_Class_Code,
3163 decode(XAD.Program_Code,
3164 g_program_code_R, 'R',
3165 g_program_code_DR, 'DR',
3166 NULL) Fin_Cat_Type_Code
3167 FROM XLA_Assignment_Defns_B XAD,
3168 XLA_Acct_Class_Assgns XACA
3169 WHERE XAD.Program_Code in (g_program_code_R,
3170 g_program_code_DR)
3171 AND XAD.Enabled_Flag = 'Y'
3172 AND XAD.Program_Code = XACA.Program_Code
3173 AND XAD.Assignment_Code = XACA.Assignment_Code)
3174 SELECT /*+ ORDERED use_hash(v1,gcc,ccdim,slga,fslg,ctl_parent,bill_acct,ct,ctl)
3175 use_hash(ctl) use_nl(sob,ctt) pq_distribute(ct,hash,hash) */ DISTINCT
3176 decode(v1.transaction_class,'ADJ','ADJ-'||v1.REVENUE_PK,'AR-'||v1.REVENUE_PK||'-'||to_char(v1.gl_date,'YYYY/MM/DD')
3177 ||'-'||v1.code_combination_id) REVENUE_PK,
3178 to_number(to_char(v1.gl_date,'J')) GL_DATE_ID,
3179 --Bug 3455965: use TRUNC for date
3180 TRUNC(v1.gl_date) GL_DATE,
3181 -- ctl_parent.inventory_item_id INVENTORY_ITEM_ID,
3182 CASE
3183 when (ctl_parent.line_type like 'LINE'
3184 and ctl_parent.inventory_item_id = sl_child.inventory_item_id
3185 and sl_child.ship_from_org_id IS NOT NULL )
3186 THEN ctl_parent.inventory_item_id
3187 when (ctl_parent.line_type like 'LINE' and ctl_parent.WAREHOUSE_ID IS NOT NULL)
3188 THEN ctl_parent.inventory_item_id
3189 ELSE
3190 to_number(NULL)
3191
3192 END INVENTORY_ITEM_ID,
3193 --bug 3361888
3194 DECODE(v1.transaction_class, 'ADJ', v1.org_id, ct.org_id) OPERATING_UNIT_ID,
3195 ccdim.company_id COMPANY_ID,
3196 ccdim.cost_center_id COST_CENTER_ID,
3197 substrb(ct.trx_number,1,30) INVOICE_NUMBER,
3198 trunc(ct.trx_date) INVOICE_DATE,
3199 DECODE(ctl_parent.line_type, 'LINE', DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
3200 DECODE(ctl_parent.interface_line_context, 'ORDER ENTRY', sl_parent.line_id,
3201 'INTERCOMPANY', sl_parent.line_id, to_number(NULL)), to_number(NULL)),
3202 to_number(NULL)) ORDER_LINE_ID,
3203 bill_acct.party_id BILL_TO_PARTY_ID,
3204 sob.currency_code FUNCTIONAL_CURRENCY,
3205 nvl(ct.invoice_currency_code,sob.currency_code) TRANSACTION_CURRENCY,
3206 --bug 3361888
3207 DECODE(v1.transaction_class, 'ADJ', v1.set_of_books_id, ct.set_of_books_id)
3208 SET_OF_BOOKS_ID,
3209 ct.customer_trx_id INVOICE_ID,
3210 nvl2(v1.transaction_class,decode(gcc.account_type,'A', nvl(v1.amount_dr,0) - nvl(v1.amount_cr,0),
3211 nvl(v1.amount_cr,0) - nvl(v1.amount_dr,0)), AMOUNT_DR) AMOUNT_T,
3212 nvl2(v1.transaction_class,decode(gcc.account_type,'A', nvl(v1.acctd_amount_dr,0) - nvl(v1.acctd_amount_cr,0),
3213 nvl(v1.acctd_amount_cr,0) - nvl(v1.acctd_amount_dr,0)),
3214 ACCTD_AMOUNT_DR) AMOUNT_B,
3215 trunc(nvl2(v1.transaction_class,v1.gl_date,ct.trx_date)) EXCHANGE_DATE,
3216 /* DECODE(ctl_parent.line_type, 'LINE', DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
3217 DECODE(ctl_parent.interface_line_context, 'ORDER ENTRY', sl_parent.inventory_item_id,
3218 'INTERCOMPANY', sl_parent.inventory_item_id, to_number(NULL)), to_number(NULL)),
3219 to_number(NULL)) TOP_MODEL_ITEM_ID,
3220 DECODE(ctl_parent.line_type, 'LINE', DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
3221 DECODE(ctl_parent.interface_line_context, 'ORDER ENTRY', sl_parent.ship_from_org_id, 'INTERCOMPANY',
3222 sl_parent.ship_from_org_id, to_number(NULL)), to_number(NULL)),
3223 to_number(NULL)) ORGANIZATION_ID,
3224 DECODE(ctl_parent.line_type, 'LINE', DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
3225 DECODE(ctl_parent.interface_line_context, 'ORDER ENTRY', sl_child.ship_from_org_id, 'INTERCOMPANY',
3226 sl_child.ship_from_org_id, to_number(NULL)), to_number(NULL)),
3227 to_number(NULL)) item_organization_id, */
3228 CASE
3229 when ( ctl_parent.line_type like 'LINE'
3230 and ctl_parent.inventory_item_id = sl_child.inventory_item_id
3231 and sl_parent.ship_from_org_id IS NOT NULL)
3232 THEN sl_parent.inventory_item_id
3233 ELSE
3234 to_number(NULL)
3235 END TOP_MODEL_ITEM_ID,
3236 DECODE(ctl_parent.line_type, 'LINE',
3237 DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
3238 sl_parent.ship_from_org_id, to_number(null)),
3239 to_number(NULL) ) ORGANIZATION_ID,
3240 DECODE(ctl_parent.line_type, 'LINE',
3241 DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
3242 sl_child.ship_from_org_id, ctl_parent.WAREHOUSE_ID ),
3243 to_number(NULL)) item_organization_id,
3244 decode(ctl_parent.interface_line_context, 'ORDER ENTRY',
3245 decode(nvl( sl_child.item_type_code, 'X' ), 'SERVICE',
3246 'N', 'Y'),
3247 'N') om_product_revenue_flag,
3248 nvl(v1.transaction_class,decode(ctt.type,'GUAR','GUR',substrb(ctt.type,1,3))) TRANSACTION_CLASS,
3249 ccdim.natural_account_id FIN_CATEGORY_ID,
3250 nvl(v1.customer_trx_line_id,ctl.customer_trx_line_id) INVOICE_LINE_ID,
3251 nvl(substrb(sh.sales_channel_code,1,30), '-1') SALES_CHANNEL,
3252 substrb( DECODE(ctl_parent.interface_line_context, 'ORDER ENTRY',ctl_parent.interface_line_attribute1,
3253 'INTERCOMPANY', ctl_parent.interface_line_attribute1,
3254 ctl_parent.sales_order),1,30) ORDER_NUMBER,
3255 v1.POSTED_FLAG,
3256 -1 PRIM_CONVERSION_RATE,
3257 -1 SEC_CONVERSION_RATE,
3258 ccdim.prod_category_id PROD_CATEGORY_ID,
3259 sob.chart_of_accounts_id CHART_OF_ACCOUNTS_ID,
3260 -- ffcta.fin_cat_type_code FIN_CAT_TYPE_CODE,
3261 v1.fin_cat_type_code FIN_CAT_TYPE_CODE,
3262 decode(sh.booked_flag, 'Y', trunc(nvl(sl_child.order_firmed_date,
3263 sh.booked_date)), to_date(null)) REV_BOOKED_DATE,
3264 decode(ctl.interface_line_context, 'ORDER ENTRY',ctl.interface_line_attribute6,
3265 null) CHILD_ORDER_LINE_ID
3266 FROM (select /*+ PARALLEL(a) */ * from fii_source_ledger_groups a) fslg,
3267 (select /*+ PARALLEL(a) */ * from fii_slg_assignments a) slga,
3268 (select /*+ PARALLEL(a) */ * from fii_gl_ccid_dimensions a) ccdim,
3269 -- (select /*+ PARALLEL(a) */ * from fii_fin_cat_type_assgns a) ffcta,
3270 (select /*+ PARALLEL(a) */ * from gl_code_combinations a) gcc,
3271 (
3272 SELECT /*+ PARALLEL(adj) PARALLEL(ad) parallel(lidset)
3273 PARALLEL(AC) PARALLEL(lnk) PARALLEL(ael) PARALLEL(aeh) */
3274 ad.line_id REVENUE_PK,
3275 trunc(aeh.accounting_date) GL_DATE,
3276 adj.org_id,
3277 aeh.ledger_id SET_OF_BOOKS_ID,
3278 sum( NVL(lnk.unrounded_entered_dr,0) ) AMOUNT_DR,
3279 sum( NVL(lnk.unrounded_entered_cr,0) ) AMOUNT_CR,
3280 sum( NVL(lnk.unrounded_accounted_dr,0) ) ACCTD_AMOUNT_DR,
3281 sum( NVL(lnk.unrounded_accounted_cr,0) ) ACCTD_AMOUNT_CR,
3282 'ADJ' TRANSACTION_CLASS,
3283 0 customer_trx_line_id,
3284 adj.customer_trx_id,
3285 ael.code_combination_id,
3286 'Y' POSTED_FLAG,
3287 AC.Fin_Cat_Type_Code
3288 FROM ar_adjustments_all adj,
3289 ar_distributions_all ad,
3290 (
3291 select /*+ no_merge use_hash(slga,fslg) */ distinct ledger_id
3292 from fii_slg_assignments slga, fii_source_ledger_groups fslg
3293 where slga.source_ledger_group_id = fslg.source_ledger_group_id
3294 and fslg.usage_code = g_usage_code
3295 ) lidset,
3296 ACCNT_CLASS AC,
3297 xla_distribution_links lnk,
3298 xla_ae_lines ael,
3299 xla_ae_headers aeh
3300 WHERE aeh.accounting_date BETWEEN g_gl_from_date AND g_gl_to_date
3301 AND adj.gl_date BETWEEN g_gl_from_date AND g_gl_to_date
3302 AND NVL(adj.status, 'A') = 'A'
3303 AND NVL(adj.postable,'Y') = 'Y'
3304 -- AND adj.amount <> 0
3305 AND ad.source_id = adj.adjustment_id
3306 AND ad.source_table = 'ADJ'
3307 AND aeh.ledger_id = lidset.ledger_id
3308 AND aeh.application_id = 222
3309 AND aeh.balance_type_code = 'A'
3310 AND aeh.gl_transfer_status_code = 'Y'
3311 AND ael.application_id = 222
3312 AND aeh.ae_header_id = ael.ae_header_id
3313 AND lnk.application_id = 222
3314 AND ael.ae_header_id = lnk.ae_header_id
3315 AND ael.ae_line_num = lnk.ae_line_num
3316 AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
3317 AND lnk.source_distribution_id_num_1 = ad.line_id
3318 AND aeh.ledger_id = adj.set_of_books_id
3319 AND ael.accounting_class_code = AC.Accounting_Class_Code
3320 AND ( aeh.ledger_id = AC.Ledger_ID OR AC.Ledger_ID IS NULL )
3321 group by
3322 ad.line_id,
3323 trunc(aeh.accounting_date),
3324 adj.org_id,
3325 aeh.ledger_id,
3326 adj.customer_trx_id,
3327 ael.code_combination_id,
3328 AC.Fin_Cat_Type_Code
3329 UNION ALL
3330 SELECT /*+ PARALLEL(ctlgd) parallel(lidset)
3331 PARALLEL(AC) PARALLEL(lnk) PARALLEL(ael) PARALLEL(aeh) */
3332 ctlgd.customer_trx_line_id REVENUE_PK,
3333 trunc(aeh.accounting_date),
3334 to_number(null), -- ctlgd.org_id,
3335 to_number(null), -- ctlgd.set_of_books_id,
3336 sum( NVL(lnk.unrounded_entered_cr,0) - NVL(lnk.unrounded_entered_dr,0) ) AMOUNT_T,
3337 0,
3338 sum( NVL(lnk.unrounded_accounted_cr,0) - NVL(lnk.unrounded_accounted_dr,0) ) AMOUNT_B,
3339 0,
3340 NULL TRANSACTION_CLASS,
3341 ctlgd.customer_trx_line_id,
3342 NULL,
3343 ael.code_combination_id,
3344 'Y' POSTED_FLAG,
3345 AC.Fin_Cat_Type_Code
3346 FROM ra_cust_trx_line_gl_dist_all ctlgd,
3347 (
3348 select /*+ no_merge use_hash(slga,fslg) */ distinct ledger_id
3349 from fii_slg_assignments slga, fii_source_ledger_groups fslg
3350 where slga.source_ledger_group_id = fslg.source_ledger_group_id
3351 and fslg.usage_code = g_usage_code
3352 ) lidset,
3353 ACCNT_CLASS AC,
3354 xla_distribution_links lnk,
3355 xla_ae_lines ael,
3356 xla_ae_headers aeh
3357 WHERE aeh.accounting_date BETWEEN g_gl_from_date AND g_gl_to_date
3358 AND ctlgd.gl_date BETWEEN g_gl_from_date AND g_gl_to_date
3359 AND ctlgd.account_set_flag = 'N'
3360 AND NVL(lnk.unrounded_entered_cr,0) - NVL(lnk.unrounded_entered_dr,0) <> 0
3361 AND aeh.ledger_id = lidset.ledger_id
3362 AND ctlgd.customer_trx_line_id IS NOT NULL
3363 AND aeh.application_id = 222
3364 AND aeh.balance_type_code = 'A'
3365 AND aeh.gl_transfer_status_code = 'Y'
3366 AND ael.application_id = 222
3367 AND aeh.ae_header_id = ael.ae_header_id
3368 AND lnk.application_id = 222
3369 AND ael.ae_header_id = lnk.ae_header_id
3370 AND ael.ae_line_num = lnk.ae_line_num
3371 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
3372 AND lnk.source_distribution_id_num_1 = ctlgd.cust_trx_line_gl_dist_id
3373 AND aeh.ledger_id = ctlgd.set_of_books_id
3374 AND ael.accounting_class_code = AC.Accounting_Class_Code
3375 AND ( aeh.ledger_id = AC.Ledger_ID OR AC.Ledger_ID IS NULL )
3376 GROUP BY ctlgd.customer_trx_line_id,
3377 trunc(aeh.accounting_date),
3378 ael.code_combination_id,
3379 AC.Fin_Cat_Type_Code
3380 ) v1,
3381 (select /*+ PARALLEL(a) */ * from ra_customer_trx_lines_all a) ctl,
3382 (select /*+ PARALLEL(a) */ * from ra_customer_trx_all a) ct,
3383 --**bug 3437052: move sob 2 places down
3384 (select /*+ PARALLEL(a) */ * from gl_ledgers_public_v a) sob,
3385 (select /*+ PARALLEL(a) */ * from ra_cust_trx_types_all a) ctt,
3386 (select /*+ PARALLEL(a) */ * from hz_cust_accounts a) bill_acct ,
3387 (select /*+ PARALLEL(a) */ * from ra_customer_trx_lines_all a) ctl_parent,
3388 (select /*+ PARALLEL(a) */ * from oe_order_lines_all a) sl_child,
3389 (select /*+ PARALLEL(a) */ * from oe_order_headers_all a) sh,
3390 (select /*+ PARALLEL(a) */ * from oe_order_lines_all a) sl_parent
3391 WHERE ccdim.code_combination_id = gcc.code_combination_id
3392 AND slga.chart_of_accounts_id = ccdim.chart_of_accounts_id
3393 AND ( slga.bal_seg_value_id = ccdim.company_id
3394 OR slga.bal_seg_value_id = -1 )
3395 AND slga.ledger_id = DECODE(v1.transaction_class, 'ADJ', v1.set_of_books_id, ct.set_of_books_id)
3396 -- AND ffcta.fin_category_id = ccdim.natural_account_id
3397 -- AND ffcta.fin_cat_type_code in ('R', 'DR')
3398 AND ctl_parent.customer_trx_line_id (+) =
3399 nvl(ctl.previous_customer_trx_line_id,ctl.customer_trx_line_id)
3400 AND sl_child.line_iD (+) =
3401 case when (ctl_parent.interface_line_context in ('ORDER ENTRY', 'INTERCOMPANY')
3402 and ltrim(ctl_parent.interface_line_attribute6, '0123456789') is NULL)
3403 then to_number(ctl_parent.interface_line_attribute6)
3404 else to_number(NULL) end
3405 AND sh.header_id (+) = sl_child.header_id
3406 AND sl_parent.line_id(+) = NVL(sl_child.top_model_line_id, sl_child.line_id)
3407 --**bug 3361888
3408 AND sob.ledger_id = DECODE(v1.transaction_class, 'ADJ', v1.set_of_books_id, ct.set_of_books_id)
3409 AND gcc.code_combination_id = v1.code_combination_id
3410 AND bill_acct.cust_account_id(+) = ct.bill_to_customer_id
3411 AND ct.complete_flag = 'Y'
3412 AND nvl(ctl.interface_line_context, 'xxx') <> 'PA INVOICES'
3413 AND ctl.customer_trx_line_id (+) = v1.customer_trx_line_id
3414 AND ct.customer_trx_id = DECODE(v1.transaction_class,'ADJ',v1.customer_trx_id,ctl.customer_trx_id)
3415 AND nvl(ct.org_id, -999) = DECODE(v1.transaction_class,'ADJ',nvl(v1.org_id, -999),nvl(ct.org_id, -999))
3416 AND ctt.cust_trx_type_id(+) = ct.cust_trx_type_id
3417 AND ctt.org_id (+) = ct.org_id
3418 AND slga.source_ledger_group_id = fslg.source_ledger_group_id
3419 AND fslg.usage_code = g_usage_code
3420 AND NVL(ctt.post_to_gl,'Y') = 'Y';
3421
3422 if g_debug_flag = 'Y' then
3423 fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into staging table.');
3424 fii_util.stop_timer;
3425 fii_util.print_timer('Duration');
3426 end if;
3427
3428 commit;
3429
3430 EXCEPTION
3431
3432 WHEN OTHERS THEN
3433 g_retcode := -2;
3434 g_errbuf := '
3435 ---------------------------------
3436 Error in Procedure: AR_STG
3437 Section: '||l_section||'
3438 Message: '||sqlerrm;
3439 raise g_procedure_failure;
3440
3441 END AR_STG;
3442
3443 PROCEDURE AR_RATES IS
3444
3445 l_global_prim_curr_code VARCHAR2(30);
3446 l_global_sec_curr_code VARCHAR2(30);
3447 l_stmt VARCHAR2(200);
3448
3449 BEGIN
3450
3451 l_global_prim_curr_code := bis_common_parameters.get_currency_code;
3452 l_global_sec_curr_code := bis_common_parameters.get_secondary_currency_code;
3453
3454 if g_debug_flag = 'Y' then
3455 fii_util.put_line(' ');
3456 fii_util.put_line('Loading data into rates table');
3457 fii_util.start_timer;
3458 fii_util.put_line('');
3459 end if;
3460
3461
3462 insert into fii_ar_revenue_rates_temp
3463 (FUNCTIONAL_CURRENCY,
3464 TRX_DATE,
3465 PRIM_CONVERSION_RATE,
3466 SEC_CONVERSION_RATE)
3467 select cc functional_currency,
3468 dt trx_date,
3469 decode(cc, l_global_prim_curr_code, 1, FII_CURRENCY.GET_GLOBAL_RATE_PRIMARY (cc,least(dt, sysdate))) PRIM_CONVERSION_RATE,
3470 decode(cc, l_global_sec_curr_code, 1, FII_CURRENCY.GET_GLOBAL_RATE_SECONDARY(cc,least(dt, sysdate))) SEC_CONVERSION_RATE
3471 from (
3472 select /*+ no_merge parallel(stg) */ distinct
3473 FUNCTIONAL_CURRENCY cc,
3474 TRUNC(GL_DATE) dt
3475 from FII_AR_REVENUE_STG stg
3476 );
3477
3478 if g_debug_flag = 'Y' then
3479 fii_util.put_line('Processed '||SQL%ROWCOUNT||' rows into rates table');
3480 fii_util.stop_timer;
3481 fii_util.print_timer('Duration');
3482 end if;
3483
3484 EXCEPTION
3485
3486 WHEN OTHERS THEN
3487 g_retcode := -2;
3488 g_errbuf := '
3489 ---------------------------------
3490 Error in Procedure: AR_RATES
3491 Message: '||sqlerrm;
3492 raise g_procedure_failure;
3493
3494
3495 END AR_RATES;
3496
3497 PROCEDURE AR_SUMMARY (p_parallel_query IN NUMBER) IS
3498
3499 seq_id NUMBER := 0;
3500 l_stmt VARCHAR2(1000);
3501
3502
3503 BEGIN
3504
3505 if g_debug_flag = 'Y' then
3506 fii_util.put_line(' ');
3507 fii_util.put_line('Started loading data into base summary table');
3508 fii_util.start_timer;
3509 fii_util.put_line('');
3510 end if;
3511
3512 SELECT FII_AR_REVENUE_B_S.nextval INTO seq_id FROM dual;
3513
3514 INSERT /*+ APPEND PARALLEL(F) */ INTO FII_AR_REVENUE_B F (
3515 REVENUE_PK,
3516 GL_DATE_ID,
3517 GL_DATE,
3518 INVENTORY_ITEM_ID,
3519 OPERATING_UNIT_ID,
3520 --commented by ilavenil COMPANY_COST_CENTER_ORG_ID,
3521 COMPANY_ID,
3522 COST_CENTER_ID,
3523 --above columns added by ilavenil
3524 INVOICE_NUMBER,
3525 ORDER_LINE_ID,
3526 BILL_TO_PARTY_ID,
3527 FUNCTIONAL_CURRENCY,
3528 TRANSACTION_CURRENCY,
3529 LEDGER_ID,
3530 INVOICE_ID,
3531 AMOUNT_T,
3532 AMOUNT_B,
3533 PRIM_AMOUNT_G,
3534 SEC_AMOUNT_G,
3535 TOP_MODEL_ITEM_ID,
3536 ORGANIZATION_ID,
3537 item_organization_id,
3538 om_product_revenue_flag,
3539 TRANSACTION_CLASS,
3540 FIN_CATEGORY_ID,
3541 ORDER_NUMBER,
3542 SALES_CHANNEL,
3543 INVOICE_LINE_ID,
3544 LAST_UPDATE_DATE,
3545 CREATION_DATE,
3546 POSTED_FLAG,
3547 PROD_CATEGORY_ID,
3548 CHART_OF_ACCOUNTS_ID,
3549 UPDATE_SEQUENCE,
3550 LAST_UPDATED_BY,
3551 CREATED_BY,
3552 LAST_UPDATE_LOGIN,
3553 INVOICE_DATE,
3554 FIN_CAT_TYPE_CODE,
3555 REV_BOOKED_DATE,
3556 CHILD_ORDER_LINE_ID)
3557 SELECT /*+ ORDERED PARALLEL(stg) PARALLEL(rates) USE_HASH(stg, rates) */
3558 stg.REVENUE_PK,
3559 stg.GL_DATE_ID,
3560 stg.GL_DATE,
3561 stg.INVENTORY_ITEM_ID,
3562 stg.OPERATING_UNIT_ID,
3563 --commented by ilavenil stg.COMPANY_COST_CENTER_ORG_ID,
3564 stg.company_id COMPANY_ID,
3565 stg.cost_center_id COST_CENTER_ID,
3566 --above 2 columns added by ilavenil
3567 stg.INVOICE_NUMBER,
3568 stg.ORDER_LINE_ID,
3569 stg.BILL_TO_PARTY_ID,
3570 stg.FUNCTIONAL_CURRENCY,
3571 stg.TRANSACTION_CURRENCY,
3572 stg.LEDGER_ID,
3573 stg.INVOICE_ID,
3574 stg.AMOUNT_T,
3575 stg.AMOUNT_B,
3576 ROUND(stg.AMOUNT_B * NVL(rates.prim_conversion_rate, 1) /
3577 to_char(g_mau_prim)) * to_char(g_mau_prim),
3578 ROUND(stg.AMOUNT_B * NVL(rates.sec_conversion_rate, 1) /
3579 to_char(g_mau_sec)) * to_char(g_mau_sec),
3580 stg.TOP_MODEL_ITEM_ID,
3581 stg.ORGANIZATION_ID,
3582 stg.item_organization_id,
3583 stg.om_product_revenue_flag,
3584 stg.TRANSACTION_CLASS,
3585 stg.FIN_CATEGORY_ID,
3586 stg.ORDER_NUMBER,
3587 stg.SALES_CHANNEL,
3588 stg.INVOICE_LINE_ID,
3589 SYSDATE,
3590 SYSDATE,
3591 stg.POSTED_FLAG,
3592 stg.PROD_CATEGORY_ID,
3593 stg.CHART_OF_ACCOUNTS_ID,
3594 seq_id,
3595 g_fii_user_id,
3596 g_fii_user_id,
3597 g_fii_login_id,
3598 stg.invoice_date,
3599 stg.fin_cat_type_code,
3600 stg.REV_BOOKED_DATE,
3601 stg.CHILD_ORDER_LINE_ID
3602 FROM fii_ar_revenue_rates_temp rates, FII_AR_REVENUE_STG stg
3603 where TRUNC(stg.GL_DATE) = rates.trx_date
3604 and stg.functional_currency = rates.functional_currency;
3605
3606 if g_debug_flag = 'Y' then
3607 fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into base summary table.');
3608 fii_util.stop_timer;
3609 fii_util.print_timer('Duration');
3610 end if;
3611
3612 commit;
3613
3614 EXCEPTION
3615
3616 WHEN OTHERS THEN
3617 g_retcode := -2;
3618 g_errbuf := '
3619 ---------------------------------
3620 Error in Procedure: AR_SUMMARY
3621 Message: '||sqlerrm;
3622
3623 if instr(sqlerrm,'ORA-00001') > 0 then
3624 UNIQUE_CONST_RECORDS;
3625 end if;
3626
3627 raise g_procedure_failure;
3628
3629 END AR_SUMMARY;
3630
3631
3632 -----------------------------------------------------------
3633 -- FUNCTION CHECK_GLOBAL_VARIABLES
3634 -----------------------------------------------------------
3635 FUNCTION CHECK_GLOBAL_VARIABLES RETURN BOOLEAN IS
3636
3637 BEGIN
3638 -- g_section := 'section 10';
3639
3640 -- if g_debug_flag = 'Y' then
3641 -- fii_util.put_line(g_section);
3642 -- end if;
3643
3644 IF g_gl_from_date IS NULL THEN
3645 fii_util.put_line(' ');
3646 fii_util.put_line(
3647 'Function CHECK_GLOBAL_VARIABLES: g_gl_from_date is NULL');
3648 RETURN TRUE;
3649 ELSIF g_gl_to_date IS NULL THEN
3650 fii_util.put_line(' ');
3651 fii_util.put_line(
3652 'Function CHECK_GLOBAL_VARIABLES: g_gl_to_date is NULL');
3653 RETURN TRUE;
3654 ELSIF (g_gl_to_date < g_gl_from_date) THEN
3655 fii_util.put_line(' ');
3656 fii_util.put_line(
3657 'Function CHECK_GLOBAL_VARIABLES: g_gl_to_date < g_gl_from_date');
3658 RETURN TRUE;
3659 ELSIF g_program_type IS NULL THEN
3660 fii_util.put_line(' ');
3661 fii_util.put_line(
3662 'Function CHECK_GLOBAL_VARIABLES: g_program_type is NULL');
3663 RETURN TRUE;
3664 ELSIF g_program_type = 'I' THEN
3665 IF g_lud_from_date IS NULL THEN
3666 fii_util.put_line(' ');
3667 fii_util.put_line(
3668 'Function CHECK_GLOBAL_VARIABLES: g_lud_from_date is NULL');
3669 RETURN TRUE;
3670 ELSIF g_lud_to_date IS NULL THEN
3671 fii_util.put_line(' ');
3672 fii_util.put_line(
3673 'Function CHECK_GLOBAL_VARIABLES: g_lud_to_date is NULL');
3674 RETURN TRUE;
3675 ELSE
3676 RETURN FALSE;
3677 END IF;
3678 ELSE
3679 RETURN FALSE;
3680 END IF;
3681
3682
3683 EXCEPTION
3684
3685 WHEN OTHERS THEN
3686 g_retcode := -1;
3687 g_errbuf := '
3688 ---------------------------------
3689 Error in Procedure: CHECK_GLOBAL_VARIABLES
3690 Message: '||sqlerrm;
3691 RAISE g_procedure_failure;
3692
3693 END CHECK_GLOBAL_VARIABLES;
3694
3695 -----------------------------------------------------------
3696 -- FUNCTION UPDATE_GLOBAL_START_DATE_TBL
3697 -- Update table FII_GLOBAL_START_DATES if necessary:
3698 -- 1. The table does not have exactly one row
3699 -- OR
3700 -- 2. The table has exactly one row,
3701 -- but it is different from p_glbl_strt_dt
3702 -----------------------------------------------------------
3703 FUNCTION UPDATE_GLOBAL_START_DATE_TBL( p_glbl_strt_dt DATE ) RETURN NUMBER IS
3704 l_count NUMBER;
3705 l_glbl_strt_dt DATE;
3706 l_updated NUMBER;
3707 BEGIN
3708 l_updated := 0;
3709
3710 select count(*) into l_count
3711 from FII_GLOBAL_START_DATES;
3712
3713 if l_count = 1 then
3714 select GLOBAL_START_DATE into l_glbl_strt_dt
3715 from FII_GLOBAL_START_DATES;
3716
3717 if l_glbl_strt_dt <> p_glbl_strt_dt then
3718 update FII_GLOBAL_START_DATES
3719 set GLOBAL_START_DATE = p_glbl_strt_dt,
3720 LAST_UPDATE_DATE = sysdate,
3721 LAST_UPDATED_BY = g_fii_user_id,
3722 LAST_UPDATE_LOGIN = g_fii_login_id;
3723
3724 l_updated := 1;
3725 end if;
3726 else
3727 if l_count > 1 then
3728 TRUNCATE_TABLE('FII_GLOBAL_START_DATES');
3729 end if;
3730
3731 insert into FII_GLOBAL_START_DATES(
3732 GLOBAL_START_DATE,
3733 CREATION_DATE, CREATED_BY,
3734 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
3735 )
3736 values(
3737 p_glbl_strt_dt,
3738 sysdate, g_fii_user_id,
3739 sysdate, g_fii_user_id, g_fii_login_id
3740 );
3741
3742 l_updated := 1;
3743 end if;
3744
3745 return l_updated;
3746
3747 EXCEPTION
3748 WHEN OTHERS THEN
3749 ROLLBACK;
3750 g_retcode := -2;
3751 g_errbuf := '
3752 ---------------------------------
3753 Error in Procedure: UPDATE_GLOBAL_START_DATE_TBL
3754 Message: '||sqlerrm;
3755 RAISE g_procedure_failure;
3756 END UPDATE_GLOBAL_START_DATE_TBL;
3757
3758 -----------------------------------------------------------
3759 -- PROCEDURE MAIN
3760 -----------------------------------------------------------
3761 PROCEDURE MAIN(Errbuf IN OUT NOCOPY VARCHAR2,
3762 Retcode IN OUT NOCOPY VARCHAR2,
3763 p_sob_id IN NUMBER,
3764 p_gl_from_date IN VARCHAR2,
3765 p_gl_to_date IN VARCHAR2,
3766 p_no_worker IN NUMBER,
3767 p_program_type IN VARCHAR2,
3768 p_parallel_query IN NUMBER,
3769 p_sort_area_size IN NUMBER,
3770 p_hash_area_size IN NUMBER) IS
3771 l_count NUMBER := 0;
3772 l_dup NUMBER := 0;
3773 -- -------------------------------------------
3774 -- Put any additional developer variables here
3775 -- -------------------------------------------
3776 l_section VARCHAR2(200) := NULL;
3777 l_stmt varchar2(1000);
3778 l_stg_count NUMBER := 0;
3779
3780 -- Declaring local variables to initialize the dates for the
3781 -- incremental mode
3782 l_last_start_date DATE :=NULL;
3783 l_last_end_date DATE :=NULL;
3784 l_last_period_from DATE :=NULL;
3785 l_last_period_to1 DATE :=NULL;
3786 l_last_period_to2 DATE :=NULL;
3787 l_last_start_date1 DATE :=NULL;
3788 l_last_start_date2 DATE :=NULL;
3789 l_gl_from_date1 DATE :=NULL;
3790 l_gl_from_date2 DATE :=NULL;
3791
3792 TYPE WorkerList is table of NUMBER
3793 index by binary_integer;
3794 l_worker WorkerList;
3795
3796 l_global_param_list dbms_sql.varchar2_table;
3797
3798 l_slg_chg BOOLEAN;
3799 l_prd_chg BOOLEAN;
3800 l_dir VARCHAR2(150) := NULL;
3801
3802 BEGIN
3803
3804 Errbuf :=NULL;
3805 Retcode:=0;
3806
3807 l_section := 'M-Section 10';
3808
3809 g_program_type := p_program_type;
3810
3811 IF l_dir is null THEN
3812 l_dir := FII_UTIL.get_utl_file_dir;
3813 END IF;
3814
3815 ------------------------------------------------
3816 -- Initialize API will fetch the FII_DEBUG_MODE
3817 -- profile option and intialize g_debug variable
3818 -- accordingly. It will also read in profile
3819 -- option BIS_DEBUG_LOG_DIRECTORY to find out
3820 -- the log directory
3821 ------------------------------------------------
3822
3823 IF g_program_type = 'I' THEN
3824 fii_util.initialize('FII_AR_REV_SUM.log','FII_AR_REV_SUM.out',l_dir, 'FII_AR_REVENUE_B_I');
3825 ELSIF g_program_type = 'L' THEN
3826 fii_util.initialize('FII_AR_REV_SUM.log','FII_AR_REV_SUM.out',l_dir, 'FII_AR_REVENUE_B_L');
3827 END IF;
3828
3829 IF g_program_type = 'I' THEN
3830 IF (NOT BIS_COLLECTION_UTILITIES.setup('FII_AR_REVENUE_B_I')) THEN
3831 raise_application_error(-20000,errbuf);
3832 return;
3833 END IF;
3834 ELSIF g_program_type = 'L' THEN
3835 IF (NOT BIS_COLLECTION_UTILITIES.setup('FII_AR_REVENUE_B_L')) THEN
3836 raise_application_error(-20000,errbuf);
3837 return;
3838 END IF;
3839 END IF;
3840
3841 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
3842
3843 fii_util.put_line('Altering session enable parallel DML after BIS_COLLECTION_UTILITIES.setup call, bug 4127183.');
3844
3845 --------------------------------------------
3846 -- Initalization
3847 --------------------------------------------
3848 l_section := 'M-Section 12';
3849 IF g_debug_flag = 'Y' then
3850 fii_util.put_line(' ');
3851 fii_util.put_line('Calling INIT procedure');
3852 END IF;
3853 INIT;
3854
3855 ----------------------------------------------------
3856 -- drop table fii_ar_uni_con_rec. This table stores
3857 -- temporarily records that violated unique constraint
3858 -- condition when inserting records into FII_AR_REVENUE_B
3859 -- during previous Load /Update program. If the fii_ar_uni_con_rec doesnot
3860 -- exist, then nothing is done. The fii_ar_uni_con_rec will
3861 -- be created if there is any unique constraint violation
3862 -- during Load /Update program
3863 -----------------------------------------------------------
3864
3865 BEGIN
3866 IF g_debug_flag = 'Y' then
3867 fii_util.put_line('');
3868 fii_util.put_line('Dropped temp table fii_ar_uni_con_rec, if it is there.');
3869 END IF;
3870
3871
3872 EXECUTE IMMEDIATE 'DROP table fii_ar_uni_con_rec ';
3873
3874
3875 EXCEPTION
3876 WHEN OTHERS THEN
3877 null;
3878 END;
3879
3880 -----------------------------------------------------
3881 -- Calling BIS API to do common set ups
3882 -- If it returns false, then program should error out
3883 -----------------------------------------------------
3884 l_section := 'M-Section 14';
3885 l_global_param_list(1) := 'BIS_GLOBAL_START_DATE';
3886 l_global_param_list(2) := 'BIS_PRIMARY_CURRENCY_CODE';
3887 l_global_param_list(3) := 'BIS_PRIMARY_RATE_TYPE';
3888
3889 IF (NOT bis_common_parameters.check_global_parameters(l_global_param_list)) THEN
3890 FII_MESSAGE.write_log( msg_name => 'FII_BAD_GLOBAL_PARA',
3891 token_num => 0);
3892 FII_MESSAGE.write_output(msg_name => 'FII_BAD_GLOBAL_PARA',
3893 token_num => 0);
3894 retcode := -1;
3895 return;
3896 END IF;
3897
3898 g_global_start_date := bis_common_parameters.get_global_start_date;
3899
3900 IF p_program_type = 'L' THEN
3901 IF g_debug_flag = 'Y' then
3902 fii_util.put_line('');
3903 FII_UTIL.put_line('Running Initial Load, truncate staging and base summary tables.');
3904 END IF;
3905 -- DEBUG
3906 TRUNCATE_TABLE('FII_AR_REVENUE_STG');
3907 TRUNCATE_TABLE('FII_AR_REVENUE_B');
3908 TRUNCATE_TABLE('FII_AR_REV_ACCTS');
3909 BIS_COLLECTION_UTILITIES.DELETELOGFOROBJECT('FII_AR_REVENUE_B_I');
3910 BIS_COLLECTION_UTILITIES.DELETELOGFOROBJECT('FII_AR_REVENUE_B_L');
3911 COMMIT;
3912 END IF;
3913
3914 -------------------------------------------------------------
3915 -- When running in Initial mode, the default values of the
3916 -- parameters are defined in the concurrent program seed data
3917 -------------------------------------------------------------
3918 l_section := 'M-Section 16';
3919
3920
3921 IF g_debug_flag = 'Y' then
3922 fii_util.put_line('');
3923 FII_UTIL.put_line('Getting start date and end date for which records are to be collected in base summary table.');
3924 END IF;
3925
3926 IF p_program_type = 'L' THEN
3927
3928 g_gl_from_date := trunc(to_date(p_gl_from_date,'YYYY/MM/DD HH24:MI:SS'));
3929
3930 -- Set g_gl_to_date to at least 7 days in the future.
3931 g_gl_to_date := trunc(
3932 greatest( to_date(p_gl_to_date,'YYYY/MM/DD HH24:MI:SS'),
3933 sysdate + 7 ) ) + 1 - ONE_SECOND;
3934
3935 l_count := UPDATE_GLOBAL_START_DATE_TBL( g_gl_from_date );
3936 FND_STATS.gather_table_stats (ownname => g_fii_schema,
3937 tabname => 'FII_GLOBAL_START_DATES');
3938
3939 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
3940
3941 fii_util.put_line('Altering session enable parallel DML after FND_STATS call, bug 4127183.');
3942
3943 ELSE
3944
3945 -----------------------------------------------------------------
3946 -- When running in Incremental mode, the values of the parameters
3947 -- are derived
3948 -----------------------------------------------------------------
3949 BIS_COLLECTION_UTILITIES.get_last_refresh_dates('FII_AR_REVENUE_B_I',
3950 l_last_start_date1,
3951 l_last_end_date,
3952 l_last_period_from,
3953 l_last_period_to1);
3954 IF l_last_start_date1 IS NOT NULL THEN
3955 l_last_start_date := l_last_start_date1;
3956 SELECT trunc(min(stu.start_date))
3957 INTO l_gl_from_date1
3958 FROM gl_period_statuses stu,
3959 fii_slg_assignments slga,
3960 fii_source_ledger_groups fslg
3961 WHERE slga.ledger_id = stu.set_of_books_id
3962 AND slga.source_ledger_group_id = fslg.source_ledger_group_id
3963 AND fslg.usage_code = g_usage_code
3964 AND stu.application_id = 222
3965 AND (stu.closing_status = 'O' OR (stu.closing_status IN ('C', 'P')
3966 AND stu.last_update_date > l_last_start_date))
3967 AND stu.end_date >= g_global_start_date;
3968
3969
3970 -- g_gl_from_date := greatest(l_gl_from_date1,g_global_start_date);
3971 g_gl_from_date := nvl(
3972 greatest(l_gl_from_date1,g_global_start_date),
3973 sysdate + 7 );
3974
3975 -----------------------------------------------------------
3976 -- For general records, we will scan 10 years into the future
3977 -- For 'AR DL', we will scan only one month into the future. For
3978 -- future records, we will not filter by last update date
3979 -- because if a future dated records (May 2005) is entered
3980 -- now (June, 2003), we will not pick it up when we reach
3981 -- May, 2005. (This logic is also embedded in Identify_change
3982 -- function.
3983 --
3984 -- For any records, scan 7 days in the future.
3985 ------------------------------------------------------------
3986
3987 -- g_gl_to_date := ADD_MONTHS(g_gl_from_date, 120);
3988 g_gl_to_date := sysdate + 7;
3989
3990 g_lud_from_date := l_last_start_date - (1/24);
3991
3992 g_lud_to_date := sysdate;
3993
3994
3995 ELSIF l_last_start_date1 IS NULL THEN
3996 -- --------------------------------------------------------------------
3997 -- in case of first incemental update, get_last_refresh_dates for
3998 -- previous incremental updates will be NULL. So we look at the LOAD
3999 -- get_last_refresh_dates.
4000 -- The LOAD may be run on 7-Mar-2003 but the the records may be collected
4001 -- upto 31-DEC-2002. To get the records from 31-DEC-2002 we use the least
4002 -- of l_last_start_date2, l_last_period_to2
4003 -- --------------------------------------------------------------------------
4004 BIS_COLLECTION_UTILITIES.get_last_refresh_dates('FII_AR_REVENUE_B_L',
4005 l_last_start_date2,
4006 l_last_end_date,
4007 l_last_period_from,
4008 l_last_period_to2);
4009 l_last_start_date := LEAST (l_last_start_date2, l_last_period_to2);
4010 SELECT trunc(min(stu.start_date))
4011 INTO l_gl_from_date2
4012 FROM gl_period_statuses stu,
4013 fii_slg_assignments slga,
4014 fii_source_ledger_groups fslg
4015 WHERE slga.ledger_id = stu.set_of_books_id
4016 AND slga.source_ledger_group_id = fslg.source_ledger_group_id
4017 AND fslg.usage_code = g_usage_code
4018 AND stu.application_id = 222
4019 AND (stu.closing_status = 'O' OR (stu.closing_status IN ('C', 'P')
4020 AND stu.last_update_date > l_last_start_date))
4021 AND stu.end_date >= g_global_start_date;
4022
4023 -- g_gl_from_date := GREATEST(l_gl_from_date2,g_global_start_date);
4024 g_gl_from_date := nvl(
4025 GREATEST(l_gl_from_date2,g_global_start_date),
4026 sysdate + 7 );
4027
4028 -- g_gl_to_date := ADD_MONTHS(g_gl_from_date, 120);
4029 g_gl_to_date := sysdate + 7;
4030
4031 g_lud_from_date := l_last_start_date - (1/24);
4032
4033 g_lud_to_date := sysdate;
4034
4035
4036 END IF;
4037
4038 END IF;
4039
4040 -----------------------------------------------------------
4041 -- checking whether global variables : g_gl_from_date,
4042 -- g_gl_to_date, g_program_type are not null
4043 ----------------------------------------------------------
4044 IF g_debug_flag = 'Y' THEN
4045 fii_util.put_line(' ');
4046 fii_util.put_line('Checking that Global Variables are not null.');
4047 END IF;
4048
4049 IF (CHECK_GLOBAL_VARIABLES) THEN
4050 -- if g_debug_flag = 'Y' then
4051 fii_util.put_line(' ');
4052 fii_util.put_line('Function CHECK_GLOBAL_VARIABLES: One of the global variables like g_gl_from_date, g_gl_to_date,
4053 g_program_type, g_lud_from_date, g_lud_to_date is NULL. Hence program terminated');
4054 -- end if;
4055 retcode := -1;
4056 RETURN;
4057 END IF;
4058
4059 -------------------------------------------------
4060 -- Print out useful date range information
4061 -------------------------------------------------
4062 if g_debug_flag = 'Y' then
4063 fii_util.put_line(' ');
4064 fii_util.put_line('This program will collect data with GL dates between '||
4065 to_char(g_gl_from_date,'MM/DD/YYYY HH24:MI:SS')||' and '||
4066 to_char(g_gl_to_date,'MM/DD/YYYY HH24:MI:SS'));
4067 IF p_program_type = 'I' THEN
4068 fii_util.put_line('This program will collect data with Last update date range between '||
4069 to_char(g_lud_from_date,'MM/DD/YYYY HH24:MI:SS')||' and '||
4070 to_char(g_lud_to_date,'MM/DD/YYYY HH24:MI:SS'));
4071 end if;
4072 end if;
4073
4074 l_section := 'Verifying if all AR periods have been upgraded for XLA';
4075 if g_debug_flag = 'Y' then
4076 FII_UTIL.put_line(l_section);
4077 end if;
4078
4079 CHECK_XLA_CONVERSION_STATUS;
4080
4081 -- end in warning if any non-sla-upgraded data exists
4082 if (g_non_upgraded_ledgers) then
4083 retcode := 1;
4084 errbuf := 'Some AR periods have not been upgraded for XLA';
4085 end if;
4086
4087 ------------------------------------------
4088 -- Check setups only if we are running in
4089 -- Incremental Mode, p_program_type = 'I'
4090 ------------------------------------------
4091 IF (p_program_type = 'I') THEN
4092
4093 ---------------------------------------------
4094 -- Check if any set up got changed. If yes,
4095 -- then we need to truncate the summary table
4096 -- and then reload
4097 ---------------------------------------------
4098 l_section := 'M-Section 23';
4099
4100
4101 l_slg_chg := CHECK_SLG_ASSIGNMENT;
4102 l_prd_chg := CHECK_PRODUCT_ASSIGNMENT;
4103
4104 IF (l_slg_chg) THEN
4105
4106 --------------------------------------------
4107 -- Write out translated message to let user
4108 -- know they need to truncate the summary
4109 -- table first before loading
4110 --------------------------------------------
4111 fii_message.write_output(
4112 msg_name => 'FII_TRUNC_SUMMARY',
4113 token_num => 0);
4114
4115 FII_UTIL.put_line('Function CHECK_SLG_ASSIGNMENT: Source Ledger Group setup has changed. Please run the Request Set in the Initial mode to repopulate the summaries.');
4116
4117 END IF;
4118
4119
4120 IF (l_prd_chg) THEN
4121
4122 --------------------------------------------
4123 -- Write out translated message to let user
4124 -- know they need to truncate the summary
4125 -- table first before loading
4126 --------------------------------------------
4127 fii_message.write_output(
4128 msg_name => 'FII_TRUNC_SUMMARY_PRD',
4129 token_num => 0);
4130
4131 FII_UTIL.put_line('Function CHECK_PRODUCT_ASSIGNMENT: Product Assignment has changed. Please run the Request Set in the Initial mode to repopulate the summaries.');
4132
4133 END IF;
4134
4135 -- should fail the program if either slg or prd changed
4136 IF (l_slg_chg OR l_prd_chg) THEN
4137 retcode := -1;
4138 RETURN;
4139 END IF;
4140
4141 -----------------------------------------------------------
4142 -- checking whether there are any changes
4143 -- to natural account assigned to 'Revenue' financial item
4144 -----------------------------------------------------------
4145 l_section := 'M-Section 24';
4146
4147 if g_debug_flag = 'Y' then
4148 fii_util.put_line('');
4149 fii_util.put_line('Calling function REV_ACCTS_CHANGED.');
4150 -- fii_util.put_line(l_stmt);
4151 end if;
4152
4153 g_rev_acct_changed := REV_ACCTS_CHANGED;
4154
4155 IF (g_rev_acct_changed) THEN
4156
4157 --------------------------------------------
4158 -- Write out translated message to let user
4159 -- know they need to truncate the summary
4160 -- table first before loading
4161 --------------------------------------------
4162 fii_util.put_line('');
4163 fii_util.put_line('Function: REV_ACCTS_CHANGED.
4164 There has been a change in mapping for the Financial Categories Revenue
4165 and/or Deferred Revenue since the last Load / Update program.
4166 Please run the Request Set in the Initial mode to repopulate the summaries.');
4167
4168 errbuf := 'Error: Change in Revenue / Deferred Revenue Accounts.';
4169
4170
4171 fii_message.write_output(
4172 msg_name => 'FII_TRUNC_SUMMARY_REV_ACCTS',
4173 token_num => 0);
4174 retcode := -1;
4175 RETURN;
4176 END IF;
4177
4178 ELSIF (p_program_type = 'L') THEN
4179 ---------------------------------------------
4180 -- If running in Inital Load, then update
4181 -- change log to indicate that resummarization
4182 -- is not necessary since everything is
4183 -- going to be freshly loaded
4184 ---------------------------------------------
4185
4186 UPDATE fii_change_log
4187 SET item_value = 'N',
4188 last_update_date = SYSDATE,
4189 last_update_login = g_fii_login_id,
4190 last_updated_by = g_fii_user_id
4191 WHERE log_item = 'AR_RESUMMARIZE'
4192 AND item_value = 'Y';
4193
4194 UPDATE fii_change_log
4195 SET item_value = 'N',
4196 last_update_date = SYSDATE,
4197 last_update_login = g_fii_login_id,
4198 last_updated_by = g_fii_user_id
4199 WHERE log_item = 'AR_PROD_CHANGE'
4200 AND item_value = 'Y';
4201
4202 COMMIT;
4203
4204 END IF;
4205
4206 ----------------------------------------------------------
4207 -- Determine if we need to resume. If there are records
4208 -- in staging table, then that means there are records
4209 -- with missing exchange rate information left from the
4210 -- previous run. In this case, we will not process any
4211 -- more new records, we will only process records already
4212 -- in the staging table
4213 ----------------------------------------------------------
4214 if g_debug_flag = 'Y' then
4215 FII_UTIL.put_line('');
4216 FII_UTIL.put_line('Determining if we need to resume from previous run');
4217 FII_UTIL.put_line('');
4218 end if;
4219
4220 -- Bug 4942753: Change to return 1 if any row exists
4221 BEGIN
4222 SELECT 1
4223 INTO l_stg_count
4224 FROM fii_ar_revenue_stg
4225 WHERE rownum = 1;
4226 EXCEPTION
4227 WHEN NO_DATA_FOUND THEN
4228 l_stg_count := 0;
4229 END;
4230
4231 IF (l_stg_count > 0) THEN
4232 g_resume_flag := 'Y';
4233 if g_debug_flag = 'Y' then
4234 FII_UTIL.put_line('Resuming from previous run');
4235 FII_UTIL.put_line('');
4236 end if;
4237 ELSE
4238 g_resume_flag := 'N';
4239 if g_debug_flag = 'Y' then
4240 FII_UTIL.put_line('Not resuming from previous run. Starting with fresh collection');
4241 FII_UTIL.put_line('');
4242 end if;
4243 END IF;
4244
4245
4246 --------------------------------------------------------------
4247 -- If resume flag is 'N', then this program starts from the
4248 -- beginning and we insert records into staging table.
4249 -- If resume flag is 'Y', we update the exchange rates
4250 -- in the staging table (assuming users have provided the
4251 -- missing exchange rates)
4252 --------------------------------------------------------------
4253 IF(g_resume_flag = 'N') THEN
4254
4255 ----------------------------------------------------------
4256 -- This variable indicates that if exception occur, do
4257 -- we need to truncate the staging table.
4258 -- We are about to submit the child process which will
4259 -- insert records into staging table. If any exception
4260 -- occured during the child process run, the staging table
4261 -- should be truncated. After all child process are done
4262 -- inserting records into staging table, this flag will
4263 -- be set to 'N'.
4264 ----------------------------------------------------------
4265 g_truncate_staging := 'Y';
4266
4267 ----------------------------------------------------------
4268 -- This variable indicates that if exception occur, do
4269 -- we need to truncate the temporary Revenue_ID table.
4270 -- We need to truncate this table if the program starts
4271 -- fresh at the beginning.
4272 -- We will reset this variable to 'N' after we have
4273 -- populate it. We will not truncate it until next time
4274 -- when the program starts fresh (non-resume). We want
4275 -- to preserve this table for debugging purpose.
4276 ----------------------------------------------------------
4277 g_truncate_id := 'Y';
4278
4279 -- ------------------------------------------
4280 -- Register phase 1 and 2 jobs
4281 -- ------------------------------------------
4282 l_section := 'M-Section 30';
4283
4284 CLEAN_UP;
4285
4286 ---------------------------------------------------------
4287 -- After we do initial clean up, we will set this flag to
4288 -- 'N' to preserve the temporary Revenue ID table for
4289 -- debugging purpose
4290 ---------------------------------------------------------
4291 g_truncate_id := 'N';
4292
4293 l_section := 'M-Section 31';
4294
4295 IF p_program_type = 'L' THEN
4296
4297 AR_STG(p_sort_area_size, p_hash_area_size, p_parallel_query);
4298
4299 -- perf tune
4300 FND_STATS.gather_table_stats
4301 (ownname => g_fii_schema,
4302 tabname => 'FII_AR_REVENUE_STG');
4303
4304 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
4305
4306 fii_util.put_line('Altering session enable parallel DML after FND_STATS call, bug 4127183.');
4307
4308 -- Bug 4942753: Changed to return 1 if any row exists
4309 BEGIN
4310 /*
4311 select 1 into l_count
4312 from fii_fin_cat_type_assgns
4313 where fin_cat_type_code = 'DR'
4314 and rownum = 1;
4315 */
4316 WITH ACCNT_CLASS AS (SELECT XAD.Ledger_ID,
4317 XACA.Accounting_Class_Code,
4318 decode(XAD.Program_Code,
4319 g_program_code_R, 'R',
4320 g_program_code_DR, 'DR',
4321 NULL) Fin_Cat_Type_Code
4322 FROM XLA_Assignment_Defns_B XAD,
4323 XLA_Acct_Class_Assgns XACA
4324 WHERE XAD.Program_Code = g_program_code_DR
4325 AND XAD.Enabled_Flag = 'Y'
4326 AND XAD.Program_Code = XACA.Program_Code
4327 AND XAD.Assignment_Code = XACA.Assignment_Code)
4328 select 1 into l_count
4329 from ACCNT_CLASS
4330 where rownum = 1;
4331 EXCEPTION
4332 WHEN NO_DATA_FOUND THEN
4333 l_count := 0;
4334 END;
4335
4336 -- Only call these two functions when some financial category
4337 -- has been assigned type DR.
4338 if l_count > 0 then
4339 l_count := IDENTIFY_CHANGE_INIT( 'AR INV' );
4340 if g_debug_flag = 'Y' then
4341 fii_util.put_line('Function IDENTIFY_CHANGE_INIT identified '||l_count||' rows');
4342 end if;
4343
4344 l_count := AR_STG_BF( 4, 1 );
4345 if g_debug_flag = 'Y' then
4346 fii_util.put_line('Function AR_STG_BF collected '||l_count||' rows into staging table.');
4347 end if;
4348 end if;
4349
4350 AR_RATES;
4351
4352 ELSE
4353
4354 if g_debug_flag = 'Y' then
4355 fii_util.put_line(' ');
4356 fii_util.put_timestamp;
4357 end if;
4358 REGISTER_PREP_JOBS;
4359 COMMIT;
4360
4361 -- ------------------------------------------
4362 -- Launch workers
4363 -- ------------------------------------------
4364 l_section := 'M-Section 40';
4365 if g_debug_flag = 'Y' then
4366 fii_util.put_line(' ');
4367 fii_util.put_timestamp;
4368 fii_util.put_line('Launching '||p_no_worker||' workers');
4369 fii_util.start_timer;
4370 end if;
4371
4372 FOR i IN 1..p_no_worker LOOP
4373 l_worker(i) := LAUNCH_WORKER(i);
4374 if g_debug_flag = 'Y' then
4375 fii_util.put_line(' Worker '||i||' request id: '||
4376 l_worker(i));
4377 end if;
4378 END LOOP;
4379
4380 COMMIT;
4381
4382 -- ------------------------------------------
4383 -- Monitor workers
4384 -- ------------------------------------------
4385 l_section := 'M-Section 50';
4386 DECLARE
4387 l_unassigned_cnt NUMBER := 0;
4388 l_completed_cnt NUMBER := 0;
4389 l_wip_cnt NUMBER := 0;
4390 l_failed_cnt NUMBER := 0;
4391 l_tot_cnt NUMBER := 0;
4392 l_last_unassigned_cnt NUMBER := 0;
4393 l_last_completed_cnt NUMBER := 0;
4394 l_last_wip_cnt NUMBER := 0;
4395 l_cycle NUMBER := 0;
4396 BEGIN
4397 LOOP
4398
4399 SELECT NVL(sum(decode(status,'UNASSIGNED',1,0)),0),
4400 NVL(sum(decode(status,'COMPLETED',1,0)),0),
4401 NVL(sum(decode(status,'IN PROCESS',1,0)),0),
4402 NVL(sum(decode(status,'FAILED',1,0)),0),
4403 COUNT(*)
4404 INTO l_unassigned_cnt,
4405 l_completed_cnt,
4406 l_wip_cnt,
4407 l_failed_cnt,
4408 l_tot_cnt
4409 FROM FII_AR_REVENUE_JOBS;
4410
4411 if g_debug_flag = 'Y' then
4412 fii_util.put_line('Job status - Unassigned:'||l_unassigned_cnt||
4413 ' In Process:'||l_wip_cnt||
4414 ' Completed:'||l_completed_cnt||
4415 ' Failed:'||l_failed_cnt);
4416 end if;
4417
4418 IF (l_failed_cnt > 0) THEN
4419 g_retcode := -2;
4420 g_errbuf := '
4421 ---------------------------------
4422 Error in Procedure: MAIN
4423 Message: At least one of the workers have errored out';
4424 RAISE g_procedure_failure;
4425
4426 END IF;
4427
4428 IF (l_tot_cnt = l_completed_cnt) THEN
4429 EXIT;
4430 END IF;
4431
4432
4433 -- -----------------------
4434 -- Detect infinite loops
4435 -- -----------------------
4436 IF (l_unassigned_cnt = l_last_unassigned_cnt AND
4437 l_completed_cnt = l_last_completed_cnt AND
4438 l_wip_cnt = l_last_wip_cnt) THEN
4439 l_cycle := l_cycle + 1;
4440 ELSE
4441 l_cycle := 1;
4442 END IF;
4443
4444
4445 IF (l_cycle > MAX_LOOP) THEN
4446 g_retcode := -2;
4447 g_errbuf := '
4448 ---------------------------------
4449 Error in Procedure: MAIN
4450 Message: No progress have been made for '||MAX_LOOP||' minutes.
4451 Terminating';
4452
4453 raise g_procedure_failure;
4454 END IF;
4455
4456 -- -----------------------
4457 -- Sleep 60 Seconds
4458 -- -----------------------
4459 dbms_lock.sleep(60);
4460
4461 l_last_unassigned_cnt := l_unassigned_cnt;
4462 l_last_completed_cnt := l_completed_cnt;
4463 l_last_wip_cnt := l_wip_cnt;
4464
4465 END LOOP;
4466
4467 if g_debug_flag = 'Y' then
4468 fii_util.stop_timer;
4469 fii_util.print_timer('Duration');
4470 end if;
4471 END;
4472
4473 END IF;
4474
4475 ---------------------------------------------------------------
4476 -- If we are in resume mode, then we will fix the missing rates
4477 -- in the staging table before verifying if there's missing
4478 -- rates again
4479 ---------------------------------------------------------------
4480 ELSE /* If g_resume_flag is 'Y' */
4481
4482 ----------------------------------------------------------
4483 -- This variable indicates that if exception occur, do
4484 -- we need to truncate the staging table.
4485 -- When running in resume mode, we do not want to truncate
4486 -- staging table
4487 ----------------------------------------------------------
4488 g_truncate_staging := 'N';
4489
4490 if g_debug_flag = 'Y' then
4491 fii_util.put_line('Program running in resume mode. Fixing missing exchange rates in staging table');
4492 fii_util.put_line('');
4493
4494 FII_UTIL.start_timer;
4495 end if;
4496
4497 Update FII_AR_REVENUE_STG stg
4498 SET prim_conversion_rate =
4499 fii_currency.get_global_rate_primary(stg.functional_currency, least(stg.exchange_date, sysdate))
4500 WHERE stg.prim_conversion_rate < 0;
4501 -- commit;
4502 if g_debug_flag = 'Y' then
4503 FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records for primary currency rates in staging table');
4504 FII_UTIL.stop_timer;
4505 FII_UTIL.print_timer('Duration');
4506
4507 FII_UTIL.start_timer;
4508 end if;
4509 commit;
4510
4511 Update FII_AR_REVENUE_STG stg
4512 SET sec_conversion_rate =
4513 fii_currency.get_global_rate_secondary(stg.functional_currency, least(stg.exchange_date, sysdate))
4514 WHERE stg.sec_conversion_rate < 0;
4515 -- commit;
4516 if g_debug_flag = 'Y' then
4517 FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records for secondary currency rates in staging table');
4518 FII_UTIL.stop_timer;
4519 FII_UTIL.print_timer('Duration');
4520 end if;
4521 commit;
4522
4523
4524 END IF; -- IF (g_resume_flag = 'N')
4525
4526 if g_debug_flag = 'Y' then
4527 fii_util.put_line(' ');
4528 fii_util.put_line('Running function VERIFY_MISSING_RATES.');
4529 end if;
4530
4531 IF g_program_type = 'I' then
4532 if g_debug_flag = 'Y' then
4533 fii_util.put_line(' ');
4534 fii_util.put_timestamp;
4535 fii_util.put_line('Anayzing staging table before checking missing rates.');
4536 end if;
4537
4538 FND_STATS.gather_table_stats (ownname => g_fii_schema,
4539 tabname => 'FII_AR_REVENUE_STG');
4540
4541 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
4542
4543 fii_util.put_line('Altering session enable parallel DML after FND_STATS call, bug 4127183.');
4544
4545
4546 END IF;
4547
4548 l_count := VERIFY_MISSING_RATES;
4549
4550 ------------------------------------------------
4551 -- If there are missing rates reported, program
4552 -- will exit immediately with warning status
4553 ------------------------------------------------
4554 IF l_count = -1 THEN
4555 ----------------------------------------------------------
4556 -- This variable indicates that if exception occur, do
4557 -- we need to truncate the staging table.
4558 -- If we reach this stage, that means all the child worker
4559 -- has completed inserting all records into staging table
4560 -- any exception occuring from now do not require staging
4561 -- table to be truncated
4562 ----------------------------------------------------------
4563 g_truncate_staging := 'N';
4564
4565 CLEAN_UP;
4566
4567 g_retcode := -1;
4568 errbuf := fnd_message.get_string('FII', 'FII_MISS_EXCH_RATE_FOUND');
4569 Retcode := g_retcode;
4570 RETURN;
4571 END IF;
4572
4573 --------------------------------------------
4574 -- This part of code merges records from stg
4575 -- into base. We only execute it when running
4576 -- in 'I' mode.
4577 ---------------------------------------------
4578 if g_debug_flag = 'Y' then
4579 fii_util.put_line(' ');
4580 fii_util.put_line('Running procedure to populate base summary table');
4581 end if;
4582 IF (g_program_type = 'L') THEN
4583 AR_SUMMARY (p_parallel_query);
4584 ELSE
4585 -- to check for duplicate records in staging table during Update prog.
4586 if g_debug_flag = 'Y' then
4587 fii_util.put_line(' ');
4588 fii_util.put_line('Checking for duplicate records in staging table.');
4589 end if;
4590 SELECT count(*) INTO l_dup
4591 from (SELECT 1
4592 FROM FII_AR_REVENUE_STG b2
4593 GROUP BY b2.revenue_pk
4594 HAVING count(*)>1
4595 );
4596 IF l_dup > 0 THEN
4597 UNIQUE_CONST_RECORDS;
4598 g_retcode := -1;
4599 g_errbuf := 'Error: Duplicate Records in staging table';
4600 RAISE g_procedure_failure;
4601 END IF;
4602 l_count := POPULATE_SUM;
4603 END IF;
4604 -------------------------------------------------------------------
4605 -- After we have merged the records from the staging table into the
4606 -- base summary table, we can clean up the staging table when we
4607 -- call the CLEAN_UP procedure
4608 -------------------------------------------------------------------
4609 g_truncate_staging := 'Y';
4610
4611 ---------------------------------------------------------------
4612 -- after load is completed, insert into table fii_ar_rev_accts
4613 -- new accounts assigned to 'Revenue'.
4614 ---------------------------------------------------------------
4615 if g_debug_flag = 'Y' then
4616 fii_util.put_line(' ');
4617 fii_util.put_line('Inserting new revenue accounts in fii_ar_rev_accts ');
4618 fii_util.start_timer;
4619 end if;
4620
4621 /*
4622 IF (p_program_type = 'L') THEN
4623
4624 --------------------------------------------------
4625 -- Inserting natural accounts which are revenue
4626 -- related into FII_AR_REV_ACCTS table
4627 --------------------------------------------------
4628 INSERT INTO fii_ar_rev_accts (rev_acct_id, fin_cat_type_code)
4629 SELECT ffcta.fin_category_id, ffcta.fin_cat_type_code
4630 FROM fii_fin_cat_type_assgns ffcta
4631 WHERE ffcta.fin_cat_type_code in ('R', 'DR');
4632
4633 ELSE
4634
4635 l_stmt := '
4636 INSERT INTO fii_ar_rev_accts (rev_acct_id, fin_cat_type_code)
4637 SELECT temp.cur_rev_acct_id, temp.cur_fin_cat_type_code
4638 FROM '||g_fii_schema||'.fii_ar_rev_accts_temp temp,
4639 fii_ar_rev_accts fra
4640 WHERE temp.cur_rev_acct_id = fra.rev_acct_id(+)
4641 AND temp.cur_fin_cat_type_code = fra.fin_cat_type_code(+)
4642 AND fra.rev_acct_id IS NULL ';
4643
4644 if g_debug_flag = 'Y' then
4645 fii_util.put_line('');
4646 -- fii_util.put_line(l_stmt);
4647 end if;
4648
4649 EXECUTE IMMEDIATE l_stmt;
4650
4651 END IF;
4652 */
4653
4654 IF (p_program_type <> 'L') THEN
4655
4656 TRUNCATE_TABLE('FII_AR_REV_ACCTS');
4657
4658 END IF;
4659
4660 INSERT INTO fii_ar_rev_accts (rev_acct_id, rev_acct, fin_cat_type_code)
4661 WITH ACCNT_CLASS AS (SELECT XAD.Ledger_ID,
4662 XACA.Accounting_Class_Code,
4663 decode(XAD.Program_Code,
4664 g_program_code_R, 'R',
4665 g_program_code_DR, 'DR',
4666 NULL) Fin_Cat_Type_Code
4667 FROM XLA_Assignment_Defns_B XAD,
4668 XLA_Acct_Class_Assgns XACA
4669 WHERE XAD.Program_Code in (g_program_code_R,
4670 g_program_code_DR)
4671 AND XAD.Enabled_Flag = 'Y'
4672 AND XAD.Program_Code = XACA.Program_Code
4673 AND XAD.Assignment_Code = XACA.Assignment_Code)
4674 SELECT * FROM ACCNT_CLASS;
4675
4676 if g_debug_flag = 'Y' then
4677 fii_util.put_line(' ');
4678 fii_util.stop_timer;
4679 fii_util.print_timer('Duration');
4680 end if;
4681
4682 COMMIT;
4683
4684
4685 IF (p_program_type = 'I') THEN
4686 SELECT sum(rows_processed)
4687 INTO l_count
4688 FROM fii_ar_revenue_jobs
4689 WHERE function = 'POPULATE_STG';
4690
4691 if g_debug_flag = 'Y' then
4692 fii_util.put_line(' ');
4693 fii_util.put_line('Processed '||l_count||' row(s) into staging table.');
4694 end if;
4695 END IF;
4696
4697 /* SELECT sum(rows_processed)
4698 INTO l_count
4699 FROM fii_ar_revenue_jobs
4700 WHERE function = 'DETECT_DELETED_INV';
4701 if g_debug_flag = 'Y' then
4702 fii_util.put_line('Found '||l_count||' invoice(s) deleted');
4703 end if;
4704 */
4705 COMMIT;
4706
4707 CLEAN_UP;
4708
4709 ----------------------------------------------------------------
4710 -- Record AR_MAX_GROUP_ID in FII_CHANGE_LOG
4711 ----------------------------------------------------------------
4712
4713 MERGE INTO FII_CHANGE_LOG log
4714 USING ( SELECT 'AR_MAX_GROUP_ID' LOG_ITEM,
4715 to_char( nvl(max(group_id), -1) ) ITEM_VALUE
4716 FROM xla_ae_headers ) new
4717 ON ( new.LOG_ITEM = log.LOG_ITEM )
4718 WHEN MATCHED THEN
4719 UPDATE SET
4720 log.ITEM_VALUE = new.ITEM_VALUE,
4721 log.LAST_UPDATE_DATE = SYSDATE,
4722 log.LAST_UPDATE_LOGIN = g_fii_login_id,
4723 log.LAST_UPDATED_BY = g_fii_user_id
4724 WHEN NOT MATCHED THEN
4725 INSERT( LOG_ITEM,
4726 ITEM_VALUE,
4727 CREATION_DATE,
4728 CREATED_BY,
4729 LAST_UPDATE_DATE,
4730 LAST_UPDATE_LOGIN,
4731 LAST_UPDATED_BY )
4732 VALUES( new.LOG_ITEM,
4733 new.ITEM_VALUE,
4734 SYSDATE,
4735 g_fii_user_id,
4736 SYSDATE,
4737 g_fii_login_id,
4738 g_fii_user_id );
4739
4740 COMMIT;
4741
4742 ----------------------------------------------------------------
4743 -- Calling BIS API to record the range we collect. Only do this
4744 -- when we have a successful collection
4745 ----------------------------------------------------------------
4746
4747 BIS_COLLECTION_UTILITIES.wrapup(
4748 p_status => TRUE,
4749 p_period_from => g_gl_from_date,
4750 p_period_to => g_gl_to_date);
4751
4752
4753 -- ---------------------------------------------------------------------------
4754 -- END OF Collection , Developer Customizable Section
4755 -- ---------------------------------------------------------------------------
4756
4757 EXCEPTION
4758 WHEN G_PROCEDURE_FAILURE THEN
4759 Errbuf := g_errbuf;
4760 Retcode := g_retcode;
4761 fii_util.put_line(Errbuf);
4762
4763 --------------------------------
4764 -- Terminate the child processes
4765 --------------------------------
4766 UPDATE FII_AR_REVENUE_JOBS
4767 SET status = 'FAILED'
4768 WHERE rownum < 2;
4769
4770 COMMIT;
4771
4772 CLEAN_UP;
4773 drop_table('fii_ar_rev_accts_temp');
4774 WHEN OTHERS THEN
4775 Retcode:= -1;
4776 Errbuf := '
4777 ---------------------------------
4778 Error in Procedure: MAIN
4779 Section: '||l_section||'
4780 Message: '||sqlerrm;
4781 fii_util.put_line(Errbuf);
4782
4783 --------------------------------
4784 -- Terminate the child processes
4785 --------------------------------
4786 UPDATE FII_AR_REVENUE_JOBS
4787 SET status = 'FAILED'
4788 WHERE rownum < 2;
4789
4790 COMMIT;
4791
4792 CLEAN_UP;
4793 drop_table('fii_ar_rev_accts_temp');
4794 END MAIN;
4795
4796
4797 --------------------------------------------------
4798 -- PROCEDURE WORKER
4799 ---------------------------------------------------
4800 PROCEDURE WORKER(
4801 Errbuf IN OUT NOCOPY VARCHAR2,
4802 Retcode IN OUT NOCOPY VARCHAR2,
4803 p_worker_no IN NUMBER) IS
4804
4805 -- -------------------------------------------
4806 -- Put any additional developer variables here
4807 -- -------------------------------------------
4808 l_unassigned_cnt NUMBER := 0;
4809 l_failed_cnt NUMBER := 0;
4810 l_curr_unasgn_cnt NUMBER := 0;
4811 l_curr_comp_cnt NUMBER := 0;
4812 l_curr_tot_cnt NUMBER := 0;
4813 l_count NUMBER;
4814 l_curr_phase NUMBER;
4815 l_function FII_AR_REVENUE_JOBS.function%TYPE;
4816 l_num_parameter1 FII_AR_REVENUE_JOBS.number_parameter1%TYPE;
4817 l_num_parameter2 FII_AR_REVENUE_JOBS.number_parameter2%TYPE;
4818 l_char_parameter1 FII_AR_REVENUE_JOBS.char_parameter1%TYPE;
4819 l_char_parameter2 FII_AR_REVENUE_JOBS.char_parameter2%TYPE;
4820 l_section VARCHAR2(20) := NULL;
4821
4822 BEGIN
4823 Errbuf :=NULL;
4824 Retcode:=0;
4825
4826 l_section := 'W-Section 10';
4827 -- -----------------------------------------------
4828 -- Set up directory structure for child process
4829 -- -----------------------------------------------
4830 CHILD_SETUP('FII_AR_REVENUE_B_C_SUBWORKER'||p_worker_no);
4831
4832 if g_debug_flag = 'Y' then
4833 fii_util.put_line(' ');
4834 fii_util.put_timestamp;
4835 fii_util.put_line('Worker '||p_worker_no||' Starting');
4836 end if;
4837
4838 -- ------------------------------------------
4839 -- Initalization
4840 -- ------------------------------------------
4841 l_section := 'W-Section 20';
4842 if g_debug_flag = 'Y' then
4843 fii_util.put_line(' ');
4844 fii_util.put_line('Calling INIT procedure for worker.');
4845 end if;
4846 INIT;
4847
4848 g_worker_num := p_worker_no;
4849
4850
4851 -- ------------------------------------------
4852 -- Loop thru job list
4853 -- -----------------------------------------
4854 l_curr_phase := 1;
4855
4856 LOOP
4857
4858 l_section := 'W-Section 30';
4859 SELECT NVL(sum(decode(status,'UNASSIGNED',1,0)),0),
4860 NVL(sum(decode(status,'FAILED',1,0)),0),
4861 NVL(sum(decode(status,'UNASSIGNED',
4862 decode(phase, l_curr_phase, 1, 0), 0)),0),
4863 NVL(sum(decode(status,'COMPLETED',
4864 decode(phase, l_curr_phase, 1, 0), 0)),0),
4865 NVL(sum(decode(phase, l_curr_phase, 1, 0)),0)
4866 INTO l_unassigned_cnt,
4867 l_failed_cnt,
4868 l_curr_unasgn_cnt,
4869 l_curr_comp_cnt,
4870 l_curr_tot_cnt
4871 FROM FII_AR_REVENUE_JOBS;
4872
4873
4874 l_section := 'W-Section 40';
4875 IF (l_failed_cnt > 0) THEN
4876 -- if g_debug_flag = 'Y' then
4877 fii_util.put_line('');
4878 fii_util.put_line('Another worker have errored out. Stop processing.');
4879 -- end if;
4880 EXIT;
4881 END IF;
4882
4883 IF (l_curr_phase = LAST_PHASE AND
4884 l_unassigned_cnt = 0) THEN
4885 if g_debug_flag = 'Y' then
4886 fii_util.put_line('');
4887 fii_util.put_line('No more jobs left. Terminating.');
4888 end if;
4889 EXIT;
4890 END IF;
4891
4892
4893 IF (l_curr_unasgn_cnt > 0) THEN
4894
4895 l_section := 'W-Section 50';
4896 UPDATE FII_AR_REVENUE_JOBS
4897 SET status = 'IN PROCESS',
4898 start_time = sysdate,
4899 worker = g_worker_num
4900 WHERE status = 'UNASSIGNED'
4901 AND phase = l_curr_phase
4902 AND priority = (
4903 SELECT min(priority)
4904 FROM fii_ar_revenue_jobs
4905 WHERE status = 'UNASSIGNED'
4906 AND phase = l_curr_phase)
4907 AND rownum < 2;
4908
4909 l_count := SQL%ROWCOUNT;
4910
4911 COMMIT;
4912
4913 -- -----------------------------------
4914 -- There could be rare situations where
4915 -- between Section 30 and Section 50
4916 -- the unassigned job gets taken by
4917 -- another worker. So, if unassigned
4918 -- job no longer exist. Do nothing.
4919 -- -----------------------------------
4920 IF (l_count > 0) THEN
4921
4922 BEGIN
4923
4924 l_count := NULL;
4925 l_section := 'W-Section 60';
4926
4927 SELECT function,
4928 date_parameter1,
4929 date_parameter2,
4930 date_parameter3,
4931 date_parameter4,
4932 number_parameter1,
4933 number_parameter2,
4934 char_parameter1,
4935 char_parameter2
4936 INTO l_function,
4937 g_gl_from_date,
4938 g_gl_to_date,
4939 g_lud_from_date,
4940 g_lud_to_date,
4941 l_num_parameter1,
4942 l_num_parameter2,
4943 l_char_parameter1,
4944 l_char_parameter2
4945 FROM FII_AR_REVENUE_JOBS
4946 WHERE worker = p_worker_no
4947 AND status = 'IN PROCESS';
4948
4949
4950 l_section := 'W-Section 70';
4951 if g_debug_flag = 'Y' then
4952 fii_util.put_line('');
4953 fii_util.put_line('');
4954 fii_util.put_line('------------------------------------------------------');
4955 fii_util.put_timestamp;
4956 end if;
4957
4958 IF (l_function = 'IDENTIFY_CHANGE') THEN
4959
4960 if g_debug_flag = 'Y' then
4961 fii_util.put_line('Job1: Calling IDENTIFY_CHANGE function for Revenue and Deferred Revenue records.');
4962 fii_util.put_line('Parameters: type='||l_char_parameter1);
4963 fii_util.put_line(' gl from date='||
4964 to_char(g_gl_from_date,'YYYY/MM/DD HH24:MI:SS'));
4965 fii_util.put_line(' gl to date='||
4966 to_char(g_gl_to_date,'YYYY/MM/DD HH24:MI:SS'));
4967 fii_util.put_line(' last updated from date='||
4968 to_char(g_lud_from_date,'YYYY/MM/DD HH24:MI:SS'));
4969 fii_util.put_line(' last updated to date='||
4970 to_char(g_lud_to_date,'YYYY/MM/DD HH24:MI:SS'));
4971 end if;
4972
4973 l_count := IDENTIFY_CHANGE(l_char_parameter1);
4974 if g_debug_flag = 'Y' then
4975 fii_util.put_line('Identified '||l_count||' rows');
4976 end if;
4977 ELSIF (l_function = 'VERIFY_CCID_UP_TO_DATE') THEN
4978 if g_debug_flag = 'Y' then
4979 fii_util.put_line('Job2: Calling VERIFY_CCID_UP_TO_DATE procedure.');
4980 end if;
4981 VERIFY_CCID_UP_TO_DATE;
4982 ELSIF (l_function = 'REGISTER_EXTRACT_JOBS') THEN
4983 if g_debug_flag = 'Y' then
4984 fii_util.put_line('Job3: Calling REGISTER_EXTRACT_JOBS procedure.');
4985 end if;
4986 REGISTER_EXTRACT_JOBS;
4987 -- ELSIF (l_function = 'DETECT_DELETED_INV') THEN
4988 -- if g_debug_flag = 'Y' then
4989 -- fii_util.put_line('Job: Detect deleted invoices');
4990 -- end if;
4991 -- l_count := DETECT_DELETED_INV;
4992 ELSIF (l_function = 'POPULATE_STG') THEN
4993 if g_debug_flag = 'Y' then
4994 fii_util.put_line('Job4: Calling POPULATE_STG function');
4995 fii_util.put_line('Parameters: view type='||l_num_parameter1);
4996 fii_util.put_line(' jobs sequence='||l_num_parameter2);
4997 end if;
4998 l_count := POPULATE_STG(l_num_parameter1,l_num_parameter2);
4999 -- FND_STATS.gather_table_stats
5000 -- (ownname => g_fii_schema,
5001 -- tabname => 'FII_AR_REVENUE_STG');
5002 ELSE
5003 g_errbuf := '
5004 ---------------------------------
5005 Error in Procedure: WORKER
5006 Message: Job type incorrect:'||l_function;
5007 raise g_procedure_failure;
5008 END IF;
5009
5010 COMMIT;
5011
5012 l_section := 'W-Section 80';
5013 UPDATE FII_AR_REVENUE_JOBS
5014 SET status = 'COMPLETED',
5015 end_time = sysdate,
5016 rows_processed = l_count
5017 WHERE worker = p_worker_no
5018 AND status = 'IN PROCESS';
5019 COMMIT;
5020
5021 EXCEPTION
5022 WHEN OTHERS THEN
5023
5024 g_retcode := -2;
5025 UPDATE FII_AR_REVENUE_JOBS
5026 SET status = 'FAILED',
5027 end_time = sysdate
5028 WHERE worker = p_worker_no
5029 AND status = 'IN PROCESS';
5030 COMMIT;
5031 RAISE;
5032
5033 END;
5034 END IF;
5035
5036 ELSIF (l_curr_comp_cnt < l_curr_tot_cnt) THEN
5037 -- -----------------------
5038 -- Sleep 60 Seconds
5039 -- -----------------------
5040 l_section := 'W-Section 90';
5041 if g_debug_flag = 'Y' then
5042 fii_util.put_line('');
5043 fii_util.put_line('');
5044 fii_util.put_line('------------------------------------------------------');
5045 fii_util.put_timestamp;
5046 end if;
5047 dbms_lock.sleep(60);
5048
5049 ELSIF (l_curr_comp_cnt = l_curr_tot_cnt) THEN
5050
5051 l_section := 'W-Section 100';
5052 IF (l_curr_phase = LAST_PHASE) THEN
5053 EXIT;
5054 ELSE
5055 l_curr_phase := l_curr_phase + 1;
5056 END IF;
5057
5058 END IF;
5059
5060
5061 END LOOP;
5062
5063 if g_debug_flag = 'Y' then
5064 fii_util.put_timestamp;
5065 end if;
5066
5067 EXCEPTION
5068 WHEN G_PROCEDURE_FAILURE THEN
5069 Errbuf := g_errbuf;
5070 Retcode := g_retcode;
5071 fii_util.put_line(Errbuf);
5072
5073 WHEN OTHERS THEN
5074 Retcode:= -2;
5075 Errbuf := '
5076 ---------------------------------
5077 Error in Procedure: WORKER
5078 Section: '||l_section||'
5079 Message: '||sqlerrm;
5080 fii_util.put_line(Errbuf);
5081
5082 -------------------------------------------------
5083 -- Write out translated message to let user know
5084 -- the subworker completed with failure status
5085 -------------------------------------------------
5086 FII_MESSAGE.write_output(
5087 msg_name => 'FII_PROG_STATUS',
5088 token_num => 2,
5089 t1 => 'USER_PROG_NAME',
5090 v1 => 'Update Receivables Revenue Summary Subworker',
5091 t2 => 'STATUS',
5092 v2 => 'failed!');
5093
5094
5095 END WORKER;
5096
5097 END FII_AR_REVENUE_B_C;