[Home] [Help]
PACKAGE BODY: APPS.FII_AR_CASH_PKG
Source
1 Package Body FII_AR_CASH_PKG AS
2 /* $Header: FIIAR08B.pls 120.2 2004/01/16 06:08:55 sgautam noship $ */
3
4
5 TYPE Instance_Rec IS RECORD (
6 instance_code VARCHAR2(30),
7 db_link VARCHAR2(128),
8 same_inst BOOLEAN,
9 valid BOOLEAN);
10
11 TYPE Instance_Tab IS TABLE OF Instance_Rec INDEX BY BINARY_INTEGER;
12
13 g_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('EDW_DEBUG'), 'N');
14 g_rec Instance_Tab;
15 g_fii_schema VARCHAR2(30);
16 g_tablespace VARCHAR2(30);
17 g_start_date DATE;
18 g_end_date DATE;
19 g_ar_rev_installed BOOLEAN;
20
21
22 G_TABLE_NOT_EXIST EXCEPTION;
23 G_SYNONYM_NOT_EXIST EXCEPTION;
24 PRAGMA EXCEPTION_INIT(G_TABLE_NOT_EXIST, -942);
25 PRAGMA EXCEPTION_INIT(G_SYNONYM_NOT_EXIST, -1434);
26
27
28 -- ---------------------------------
29 -- PRIVATE PROCEDURES AND FUNCTIONS
30 -- ---------------------------------
31
32 -------------------
33 -- PROCEDURE Init
34 -------------------
35 PROCEDURE Init is
36 l_stmt VARCHAR2(200);
37 l_status VARCHAR2(30);
38 l_industry VARCHAR2(30);
39 l_db_name1 VARCHAR2(30);
40 l_db_name2 VARCHAR2(30);
41 l_dummy NUMBER := NULL;
42 i NUMBER := 0;
43
44 cursor source_instance is
45 select instance_code,
46 warehouse_to_instance_link
47 from edw_source_instances;
48
49 BEGIN
50
51 -- --------------------------------------------------------
52 -- Find the schema owner and tablespace
53 -- FII_AR_OPERATIONS_SUMMARY is using
54 -- --------------------------------------------------------
55 IF(FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, g_fii_schema))
56 THEN NULL;
57 END IF;
58
59 SELECT tablespace_name
60 INTO g_tablespace
61 FROM all_tables
62 WHERE table_name = 'FII_AR_OPERATIONS_SUMMARY'
63 AND owner = g_fii_schema;
64
65 g_start_date := fii_time_wh_api.get_fqtr_start;
66 g_end_date := fii_time_wh_api.get_fqtr_end;
67
68 -- DEBUG
69 -- g_start_date := '01-JAN-1998';
70 -- g_end_date := '01-APR-1998';
71
72
73 -- ----------------------------
74 -- Findwarehouse database name
75 -- ----------------------------
76 select name
77 into l_db_name1
78 from v$database;
79
80 -- --------------------------------------------------------
81 -- Only if the customer is Oracle IT, then we will use
82 -- Cons Revenue. Otherwise, we will use AR Revenue
83 -- --------------------------------------------------------
84 IF (l_db_name1 = 'EDWAP' or
85 l_db_name1 = 'EDWAT' or
86 l_db_name1 = 'EDWAS') THEN
87 g_ar_rev_installed := FALSE;
88 ELSE
89 g_ar_rev_installed := TRUE;
90 END IF;
91
92 -- DEBUG
93 -- g_ar_rev_installed := FALSE;
94
95
96 -- --------------------------------------------------------
97 -- Loop the complete set of instances
98 -- --------------------------------------------------------
99 FOR c in source_instance LOOP
100
101 i := i + 1;
102 g_rec(i).instance_code := c.instance_code;
103 g_rec(i).db_link := c.warehouse_to_instance_link;
104
105
106 -- --------------------------------------------------
107 -- Check if we should load data from that instance
108 -- Currently, we just check if the fii_ar_oltp_cash_v
109 -- exist on the instance and has data. This is a bit
110 -- cludgy but is a temporary solution until we build
111 -- the AR Cash Base Fact
112 -- --------------------------------------------------
113 BEGIN
114
115 l_stmt := 'select sob_id from fii_ar_oltp_cash_v@'||
116 g_rec(i).db_link||' where rownum < 2';
117
118 if g_debug_flag = 'Y' then
119 edw_log.debug_line('');
120 edw_log.debug_line(l_stmt);
121 end if;
122 execute immediate l_stmt into l_dummy;
123
124 IF l_dummy IS NOT NULL THEN
125 g_rec(i).valid := TRUE;
126 ELSE
127 g_rec(i).valid := FALSE;
128 END IF;
129
130 exception when others then
131 g_rec(i).valid := FALSE;
132
133 END;
134
135
136 -- ----------------------------
137 -- Check if same instance
138 -- ----------------------------
139 IF g_rec(i).valid THEN
140 l_stmt := 'select name '||
141 'from v$database@'||g_rec(i).db_link;
142
143 if g_debug_flag = 'Y' then
144 edw_log.debug_line('');
145 edw_log.debug_line(l_stmt);
146 end if;
147
148 execute immediate l_stmt into l_db_name2;
149
150 IF (l_db_name1 = l_db_name2) THEN
151 g_rec(i).same_inst := TRUE;
152 ELSE
153 g_rec(i).same_inst := FALSE;
154 END IF;
155 END IF;
156
157 END LOOP;
158
159 end Init;
160
161
162
163 ---------------------------------------------------
164 -- PROCEDURE DROP_TABLE
165 ---------------------------------------------------
166 procedure drop_table (p_table_name in varchar2) is
167 l_stmt varchar2(400);
168 Begin
169
170 l_stmt:='drop table '||g_fii_schema||'.'||p_table_name;
171
172 if g_debug_flag = 'Y' then
173 edw_log.debug_line('');
174 edw_log.debug_line(l_stmt);
175 end if;
176 execute immediate l_stmt;
177
178 l_stmt:='drop synonym '||p_table_name;
179
180 if g_debug_flag = 'Y' then
181 edw_log.debug_line('');
182 edw_log.debug_line(l_stmt);
183 end if;
184
185 execute immediate l_stmt;
186
187
188 Exception
189 WHEN G_TABLE_NOT_EXIST THEN
190 null; -- Oracle 942, table does not exist, no actions
191 WHEN G_SYNONYM_NOT_EXIST THEN
192 null; -- Oracle 1434, synonym not exist, no actions
193 WHEN OTHERS THEN
194 raise;
195 End Drop_Table;
196
197
198
199 ---------------------------------------------------
200 -- PROCEDURE Extract_OLTP_CASH
201 ---------------------------------------------------
202 Procedure Extract_OLTP_CASH is
203 l_stmt varchar2(1000);
204 Begin
205
206 -- ------------------------
207 -- Create the table needed
208 -- ------------------------
209 l_stmt := 'create table '||g_fii_schema||'.FII_AR_OLTP_CASH(
210 instance_code varchar2(30),
211 org_id number,
212 sob_id number,
213 customer_id number,
214 period_set varchar2(15),
215 period_type varchar2(15),
216 calendar_day date,
217 functional_currency varchar2(15),
218 cash_b number,
219 cash_g number,
220 receipt_cnt number)
221 TABLESPACE '||g_tablespace||'
222 PCTFREE 5
223 storage (INITIAL 4K NEXT 32K)';
224
225 if g_debug_flag = 'Y' then
226 edw_log.debug_line('');
227 edw_log.debug_line(l_stmt);
228 end if;
229 execute immediate l_stmt;
230
231
232 -- ------------------------
233 -- Populate the table from
234 -- all the valid sources
235 -- ------------------------
236
237 FOR i IN 1..g_rec.count LOOP
238
239 IF (g_rec(i).valid) THEN
240 l_stmt := 'insert into '||g_fii_schema||'.FII_AR_OLTP_CASH T
241 (instance_code,
242 org_id,
243 sob_id,
244 customer_id,
245 period_set,
246 period_type,
247 calendar_day,
248 functional_currency,
249 cash_b,
250 cash_g,
251 receipt_cnt)
252 select /*+ DRIVING_SITE(CASH) */
253 instance_code,
254 org_id,
255 sob_id,
256 customer_id,
257 period_set,
258 period_type,
259 trunc(calendar_day),
260 functional_currency,
261 sum(cash_b),
262 sum(cash_g),
263 count(distinct(cash_receipt_id))
264 from
265 fii_ar_oltp_cash_v';
266
267 IF (g_rec(i).same_inst) THEN
268 l_stmt := l_stmt||' CASH ';
269 ELSE
270 l_stmt := l_stmt||'@'||g_rec(i).db_link||' CASH ';
271 END IF;
272
273 l_stmt := l_stmt ||'
274 where calendar_day >= to_date('''||to_char(g_start_date,'DD-MM-YYYY')||''', ''DD-MM-YYYY'')
275 and calendar_day < to_date('''||to_char(g_end_date,'DD-MM-YYYY')||''', ''DD-MM-YYYY'')
276 group by
277 instance_code,
278 org_id,
279 sob_id,
280 customer_id,
281 period_set,
282 period_type,
283 trunc(calendar_day),
284 functional_currency';
285
286 if g_debug_flag = 'Y' then
287 edw_log.debug_line('');
288 edw_log.debug_line(l_stmt);
289 end if;
290 execute immediate l_stmt;
291
292 commit;
293
294 END IF; -- g_rec(i).valid
295
296 END LOOP;
297
298 End Extract_OLTP_CASH;
299
300
301
302 ---------------------------------------------------
303 -- PROCEDURE Extract_EDW_AR_INV_REV
304 ---------------------------------------------------
305 procedure Extract_AR_INV_REV is
306 l_stmt varchar2(2000);
307 Begin
308
309
310 -- -------------------------
311 -- Create the tables needed
312 -- -------------------------
313 l_stmt := 'create table '||g_fii_schema||'.FII_AR_EDW_REV(
314 set_of_books_fk_key NUMBER,
315 operating_unit_fk_key NUMBER,
316 org_fk_key NUMBER,
317 customer_fk_key NUMBER,
318 functional_currency VARCHAR2(15),
319 calendar_day_fk_key NUMBER,
320 calendar_date DATE,
321 amt_invoiced_ar_b NUMBER,
322 amt_invoiced_ar_g NUMBER,
323 amt_rev_earned_b NUMBER,
324 amt_rev_earned_g NUMBER,
325 inv_created_cnt NUMBER,
326 inv_revrec_cnt NUMBER)
327 TABLESPACE '||g_tablespace||'
328 PCTFREE 5
329 storage (INITIAL 4K NEXT 32K)';
330
331 if g_debug_flag = 'Y' then
332 edw_log.debug_line('');
333 edw_log.debug_line(l_stmt);
334 end if;
335 execute immediate l_stmt;
336
337 l_stmt := 'create table '||g_fii_schema||'.FII_AR_OLTP_INV(
338 instance_code varchar2(30),
339 org_id number,
340 sob_id number,
341 customer_id number,
342 period_set varchar2(15),
343 period_type varchar2(15),
344 calendar_day date,
345 functional_currency varchar2(15),
346 inv_b number,
347 inv_g number,
348 inv_created_cnt number)
349 TABLESPACE '||g_tablespace||'
350 PCTFREE 5
351 storage (INITIAL 4K NEXT 32K)';
352
353 if g_debug_flag = 'Y' then
354 edw_log.debug_line('');
355 edw_log.debug_line(l_stmt);
356 end if;
357 execute immediate l_stmt;
358
359
360
361 IF (g_ar_rev_installed) THEN
362
363 -- -----------------------------
364 -- Populate Revenue and Invoice
365 -- info from AR Revenue Fact
366 -- -----------------------------
367
368 l_stmt := 'insert into '||g_fii_schema||'.FII_AR_EDW_REV T (
369 set_of_books_fk_key,
370 operating_unit_fk_key,
371 org_fk_key,
372 customer_fk_key,
373 functional_currency,
374 calendar_day_fk_key,
375 calendar_date,
376 amt_invoiced_ar_b,
377 amt_invoiced_ar_g,
378 amt_rev_earned_b,
379 amt_rev_earned_g,
380 inv_created_cnt,
381 inv_revrec_cnt)
382 select
383 f.set_of_books_fk_key,
384 org.oper_operating_unit_pk_key,
385 f.organization_fk_key,
386 cust.tprt_trade_partner_pk_key,
387 curr.crnc_currency,
388 f.gl_date_fk_key,
389 trunc(f.gl_date),
390 sum(decode(f.account_class, ''REC'', f.amt_b, 0)),
391 sum(decode(f.account_class, ''REC'', f.amt_g, 0)),
392 sum(decode(f.account_class, ''REV'', f.amt_b, 0)),
393 sum(decode(f.account_class, ''REV'', f.amt_g, 0)),
394 count(distinct(decode(f.account_class, ''REC'', invoice_id, to_number(null)))),
395 count(distinct(decode(f.account_class, ''REV'', invoice_id, to_number(null))))
396 from fii_ar_trx_dist_f f,
397 edw_organization_m org,
398 edw_trd_partner_m cust,
399 edw_currency_m curr
400 where f.gl_date >= to_date('''||to_char(g_start_date,'DD-MM-YYYY')||''', ''DD-MM-YYYY'')
401 and f.gl_date < to_date('''||to_char(g_end_date,'DD-MM-YYYY')||''', ''DD-MM-YYYY'')
402 and f.account_class in (''REV'', ''REC'')
403 and curr.crnc_currency_pk_key = f.functional_currency_fk_key
404 and cust.tplo_tpartner_loc_pk_key = f.bill_to_customer_fk_key
405 and org.orga_organization_pk_key = f.organization_fk_key
406 group by
407 f.set_of_books_fk_key,
408 org.oper_operating_unit_pk_key,
409 f.organization_fk_key,
410 cust.tprt_trade_partner_pk_key,
411 curr.crnc_currency,
412 f.gl_date_fk_key,
413 trunc(f.gl_date)';
414
415 if g_debug_flag = 'Y' then
416 edw_log.debug_line('');
417 edw_log.debug_line(l_stmt);
418 end if;
419 execute immediate l_stmt;
420
421
422 ELSE -- Consolidated Revenue Implementation
423
424 -- -----------------------------
425 -- Populate Revenue from Cons Rev
426 -- -----------------------------
427
428 l_stmt := 'insert into '||g_fii_schema||'.FII_AR_EDW_REV T (
429 set_of_books_fk_key,
430 operating_unit_fk_key,
431 org_fk_key,
432 customer_fk_key,
433 functional_currency,
434 calendar_day_fk_key,
435 calendar_date,
436 amt_invoiced_ar_b,
437 amt_invoiced_ar_g,
438 amt_rev_earned_b,
439 amt_rev_earned_g,
440 inv_created_cnt,
441 inv_revrec_cnt )
442 select
443 f.gl_set_of_books_fk_key,
444 org.oper_operating_unit_pk_key,
445 f.organization_fk_key,
446 cust.tprt_trade_partner_pk_key,
447 curr.crnc_currency,
448 f.gl_period_fk_key,
449 t.cday_calendar_date,
450 0,
451 0,
452 sum(f.amt_b),
453 sum(f.amt_g),
454 0,
455 count(distinct(f.ar_doc_num_fk_key))
456 from fii_e_revenue_f f,
457 edw_time_m t,
458 edw_currency_m curr,
459 edw_rev_source_m src,
460 edw_organization_m org,
461 edw_gl_acct3_m acct,
462 edw_trd_partner_m cust
463 where t.cday_cal_day_pk_key = f.gl_period_fk_key
464 and src.source_rev_src_pk_key = f.revenue_source_fk_key
465 and src.source_rev_src_pk in (''AR ADJ'', ''AR REV'')
466 and acct.l1_pk_key = f.gl_acct3_fk_key
470 and f.base_currency_fk_key = curr.crnc_currency_pk_key
467 and acct.l1_type = ''Revenue''
468 and t.cday_calendar_date >= to_date('''||to_char(g_start_date,'DD-MM-YYYY')||''', ''DD-MM-YYYY'')
469 and t.cday_calendar_date < to_date('''||to_char(g_end_date,'DD-MM-YYYY')||''', ''DD-MM-YYYY'')
471 and org.orga_organization_pk_key = f.organization_fk_key
472 and cust.tplo_tpartner_loc_pk_key = f.bill_to_customer_fk_key
473 group by
474 f.gl_set_of_books_fk_key,
475 org.oper_operating_unit_pk_key,
476 f.organization_fk_key,
477 cust.tprt_trade_partner_pk_key,
478 curr.crnc_currency,
479 f.gl_period_fk_key,
480 t.cday_calendar_date';
481
482 if g_debug_flag = 'Y' then
483 edw_log.debug_line('');
484 edw_log.debug_line(l_stmt);
485 end if;
486 execute immediate l_stmt;
487
488
489 -- -------------------------------
490 -- Populate Invoice info from OLTP
491 -- -------------------------------
492
493 FOR i IN 1..g_rec.count LOOP
494
495 IF (g_rec(i).valid) THEN
496 l_stmt := 'insert into '||g_fii_schema||'.FII_AR_OLTP_INV T (
497 instance_code,
498 org_id,
499 sob_id,
500 customer_id,
501 period_set,
502 period_type,
503 calendar_day,
504 functional_currency,
505 inv_b,
506 inv_g,
507 inv_created_cnt)
508 select /*+ DRIVING_SITE(INV) */
509 instance_code,
510 org_id,
511 sob_id,
512 customer_trx_id,
513 period_set,
514 period_type,
515 trunc(calendar_day),
516 functional_currency,
517 sum(inv_b),
518 sum(inv_g),
519 count(distinct(customer_trx_id))
520 from
521 fii_ar_oltp_inv_v';
522
523 IF (g_rec(i).same_inst) THEN
524 l_stmt := l_stmt||' INV ';
525 ELSE
526 l_stmt := l_stmt||'@'||g_rec(i).db_link||' INV ';
527 END IF;
528
529 l_stmt := l_stmt ||'
530 where calendar_day >= to_date('''||to_char(g_start_date,'DD-MM-YYYY')||''', ''DD-MM-YYYY'')
531 and calendar_day < to_date('''||to_char(g_end_date,'DD-MM-YYYY')||''', ''DD-MM-YYYY'')
532 group by
533 instance_code,
534 org_id,
535 sob_id,
536 customer_trx_id,
537 period_set,
538 period_type,
539 trunc(calendar_day),
540 functional_currency';
541
542 if g_debug_flag = 'Y' then
543 edw_log.debug_line('');
544 edw_log.debug_line(l_stmt);
545 end if;
546 execute immediate l_stmt;
547
548 commit;
549
550 END IF; -- (g_rec(i).valid
551
552 END LOOP;
553
554 END IF; -- g_ar_rev_installed
555
556
557 end Extract_AR_INV_REV;
558
559
560 --------------------------------------------------
561 -- PROCEDURE Populate_AR_OPER_SUMMARY
562 ---------------------------------------------------
563 procedure Populate_AR_OPER_SUMMARY is
564 l_stmt VARCHAR2(32767);
565 Begin
566
567
568 -- --------------------------------
569 -- Populate customer level summary
570 -- --------------------------------
571 l_stmt := 'truncate table '||g_fii_schema||'.fii_ar_oper_cust_summary_f';
572
573 if g_debug_flag = 'Y' then
574 edw_log.debug_line('');
575 edw_log.debug_line(l_stmt);
576 end if;
577 execute immediate l_stmt;
578
579
580 l_stmt := 'insert into fii_ar_oper_cust_summary_f (
581 set_of_books_fk_key,
582 operating_unit_fk_key,
583 org_fk_key,
584 customer_fk_key,
585 functional_currency,
586 calendar_day_fk_key,
587 calendar_date,
588 amt_invoiced_ar_b,
589 amt_invoiced_ar_g,
590 amt_rev_earned_b,
591 amt_rev_earned_g,
592 amt_cash_received_b,
593 amt_cash_received_g,
594 inv_created_cnt,
595 inv_revrec_cnt,
596 receipt_cnt,
597 last_update_date,
598 creation_date)
599 select a.set_of_books_fk_key,
600 a.operating_unit_fk_key,
601 a.org_fk_key,
602 a.customer_fk_key,
603 a.functional_currency,
604 a.calendar_day_fk_key,
605 a.calendar_date,
606 sum(a.amt_invoiced_ar_b) amt_invoiced_ar_b,
607 sum(a.amt_invoiced_ar_g) amt_invoiced_ar_g,
608 sum(a.amt_rev_earned_b) amt_rev_earned_b,
609 sum(a.amt_rev_earned_g) amt_rev_earned_g,
610 sum(a.amt_cash_received_b) amt_cash_received_b,
611 sum(a.amt_cash_received_g) amt_cash_received_g,
612 sum(a.inv_created_cnt) inv_created_cnt,
613 sum(a.inv_revrec_cnt) inv_revrec_cnt,
614 sum(a.receipt_cnt) receipt_cnt,
615 sysdate, sysdate
616 from ( select set_of_books_fk_key,
617 operating_unit_fk_key,
618 org_fk_key,
619 customer_fk_key,
620 functional_currency,
621 calendar_day_fk_key,
622 calendar_date,
623 amt_invoiced_ar_b,
624 amt_invoiced_ar_g,
625 amt_rev_earned_b,
626 amt_rev_earned_g,
627 0 amt_cash_received_b,
628 0 amt_cash_received_g,
629 inv_created_cnt,
630 inv_revrec_cnt,
631 0 receipt_cnt
632 from '||g_fii_schema||'.fii_ar_edw_rev
633 where set_of_books_fk_key > 0
637 and calendar_day_fk_key > 0
634 and operating_unit_fk_key > 0
635 and org_fk_key > 0
636 and customer_fk_key > 0
638 union all';
639
640 IF (g_ar_rev_installed) THEN
641
642 l_stmt := l_stmt||'
643 select sob.fabk_fa_book_pk_key,
644 org.oper_operating_unit_pk_key,
645 org.orga_organization_pk_key,
646 cust.tprt_trade_partner_pk_key,
647 f.functional_currency,
648 t.cday_cal_day_pk_key,
649 f.calendar_day,
650 0 amt_invoiced_ar_b,
651 0 amt_invoiced_ar_g,
652 0 amt_rev_earned_b,
653 0 amt_rev_earned_g,
654 f.cash_b amt_cash_received_b,
655 f.cash_g amt_cash_received_g,
656 0 inv_created_cnt,
657 0 inv_revrec_cnt,
658 f.receipt_cnt
659 from '||g_fii_schema||'.fii_ar_oltp_cash f,
660 edw_time_m t,
661 edw_gl_book_m sob,
662 edw_organization_m org,
663 edw_trd_partner_m cust
664 where sob.fabk_fa_book_pk = to_char(f.sob_id)||''-''||f.instance_code
665 and sob.fabk_fa_book_pk_key > 0
666 and org.oper_operating_unit_pk_key > 0
667 and org.orga_organization_pk_key > 0
668 and cust.tprt_trade_partner_pk_key >= 0 /* bug 3290436 */
669 and t.cday_cal_day_pk_key > 0
670 and t.cday_cal_day_pk = to_char(f.calendar_day,''DD-MM-YYYY'')||''-''||
671 f.period_set||''-''||period_type||''-''||f.instance_code||''-CD''
672 and cust.tplo_tpartner_loc_pk = decode(f.customer_id, -1, ''NA_EDW'',
673 to_char(f.customer_id)||''-''||f.instance_code||''-CUST_ACCT-TPRT'' )
674 and org.orga_organization_pk = to_char(f.org_id)||''-''||f.instance_code';
675 ELSE
676
677 l_stmt := l_stmt||'
678 select sob.fabk_fa_book_pk_key,
679 org.oper_operating_unit_pk_key,
680 org.orga_organization_pk_key,
681 cust.tprt_trade_partner_pk_key,
682 f.functional_currency,
683 t.cday_cal_day_pk_key,
684 f.calendar_day,
685 0 amt_invoiced_ar_b,
686 0 amt_invoiced_ar_g,
687 0 amt_rev_earned_b,
688 0 amt_rev_earned_g,
689 f.cash_b amt_cash_received_b,
690 f.cash_g amt_cash_received_g,
691 0 inv_created_cnt,
692 0 inv_revrec_cnt,
693 f.receipt_cnt
694 from '||g_fii_schema||'.fii_ar_oltp_cash f,
695 edw_time_m t,
696 edw_gl_book_m sob,
697 edw_organization_m org,
698 edw_trd_partner_m cust
699 where sob.fabk_fa_book_pk = to_char(f.sob_id)||''-''||f.instance_code
700 and sob.fabk_fa_book_pk_key > 0
701 and org.oper_operating_unit_pk_key > 0
702 and org.orga_organization_pk_key > 0
703 and cust.tprt_trade_partner_pk_key >= 0 /* bug 3290436 */
704 and t.cday_cal_day_pk_key > 0
705 and t.cday_cal_day_pk = to_char(f.calendar_day,''DD-MM-YYYY'')||''-''||
706 f.period_set||''-''||period_type||''-''||f.instance_code||''-CD''
707 and cust.tplo_tpartner_loc_pk = decode(f.customer_id, -1, ''NA_EDW'',
708 to_char(f.customer_id)||''-''||f.instance_code||''-CUST_ACCT-TPRT'' )
709 and org.orga_organization_pk = to_char(f.org_id)||''-''||f.instance_code';
710
711 END IF;
712
713 l_stmt := l_stmt||'
714 union all
715 select sob.fabk_fa_book_pk_key,
716 org.oper_operating_unit_pk_key,
717 org.orga_organization_pk_key,
718 cust.tprt_trade_partner_pk_key,
719 f.functional_currency,
720 t.cday_cal_day_pk_key,
721 f.calendar_day,
722 f.inv_b amt_invoiced_ar_b,
723 f.inv_g amt_invoiced_ar_g,
724 0 amt_rev_earned_b,
725 0 amt_rev_earned_g,
726 0 amt_cash_received_b,
727 0 amt_cash_received_g,
728 f.inv_created_cnt,
729 0 inv_revrec_cnt,
730 0 receipt_count
731 from '||g_fii_schema||'.fii_ar_oltp_inv f,
732 edw_time_m t,
733 edw_gl_book_m sob,
734 edw_organization_m org,
735 edw_trd_partner_m cust
736 where sob.fabk_fa_book_pk = to_char(f.sob_id)||''-''||f.instance_code
737 and sob.fabk_fa_book_pk_key > 0
738 and org.oper_operating_unit_pk_key > 0
739 and org.orga_organization_pk_key > 0
740 and cust.tprt_trade_partner_pk_key > 0
741 and t.cday_cal_day_pk_key > 0
742 and t.cday_cal_day_pk = to_char(f.calendar_day,''DD-MM-YYYY'')||''-''||
743 f.period_set||''-''||period_type||''-''||f.instance_code||''-CD''
744 and cust.tplo_tpartner_loc_pk = decode(f.customer_id, -1, ''NA_EDW'',
745 to_char(f.customer_id)||''-''||f.instance_code||''-CUST_ACCT-TPRT'' )
746 and org.orga_organization_pk = to_char(f.org_id)||''-''||f.instance_code) a
747 group by a.set_of_books_fk_key,
748 a.operating_unit_fk_key,
749 a.org_fk_key,
750 a.customer_fk_key,
751 a.functional_currency,
752 a.calendar_day_fk_key,
753 a.calendar_date
754 order by a.set_of_books_fk_key, a.operating_unit_fk_key, a.calendar_date';
755
756 if g_debug_flag = 'Y' then
757 edw_log.debug_line('');
758 edw_log.debug_line(l_stmt);
759 end if;
760 execute immediate l_stmt;
761
762 -- --------------------------------
763 -- Populate org level summary
767 PCTFREE 5
764 -- --------------------------------
765 l_stmt := 'create table '||g_fii_schema||'.fii_ar_operations_summary_new
766 TABLESPACE '||g_tablespace||'
768 STORAGE (INITIAL 4K NEXT 64K) AS
769 select a.set_of_books_fk_key,
770 a.operating_unit_fk_key,
771 a.functional_currency,
772 a.calendar_day_fk_key,
773 a.calendar_date,
774 sum(a.amt_booked_b) amt_booked_b,
775 sum(a.amt_booked_g) amt_booked_g,
776 sum(a.amt_shipped_b) amt_shipped_b,
777 sum(a.amt_shipped_g) amt_shipped_g,
778 sum(a.amt_invoiced_oe_b) amt_invoiced_oe_b,
779 sum(a.amt_invoiced_oe_g) amt_invoiced_oe_g,
780 sum(a.amt_invoiced_ar_b) amt_invoiced_ar_b,
781 sum(a.amt_invoiced_ar_g) amt_invoiced_ar_g,
782 sum(a.amt_rev_earned_b) amt_rev_earned_b,
783 sum(a.amt_rev_earned_g) amt_rev_earned_g,
784 sum(a.amt_cash_received_b) amt_cash_received_b,
785 sum(a.amt_cash_received_g) amt_cash_received_g,
786 sum(a.order_count) order_count
787 from ( select set_of_books_fk_key set_of_books_fk_key,
788 operating_unit_fk_key operating_unit_fk_key,
789 functional_currency functional_currency,
790 calendar_day_fk_key calendar_day_fk_key,
791 calendar_date calendar_date,
792 amt_booked_b amt_booked_b,
793 amt_booked_g amt_booked_g,
794 amt_shipped_b amt_shipped_b,
795 amt_shipped_g amt_shipped_g,
796 amt_invoiced_oe_b amt_invoiced_oe_b,
797 amt_invoiced_oe_g amt_invoiced_oe_g,
798 0 amt_invoiced_ar_b,
799 0 amt_invoiced_ar_g,
800 0 amt_rev_earned_b,
801 0 amt_rev_earned_g,
802 0 amt_cash_received_b,
803 0 amt_cash_received_g,
804 order_count order_count
805 from fii_ar_operations_summary
806 union all
807 select set_of_books_fk_key,
808 org_fk_key,
809 functional_currency,
810 calendar_day_fk_key,
811 calendar_date,
812 0 amt_booked_b,
813 0 amt_booked_g,
814 0 amt_shipped_b,
815 0 amt_shipped_g,
816 0 amt_invoiced_oe_b,
817 0 amt_invoiced_oe_g,
818 amt_invoiced_ar_b,
819 amt_invoiced_ar_g,
820 amt_rev_earned_b,
821 amt_rev_earned_g,
822 amt_cash_received_b,
823 amt_cash_received_g,
824 0 order_count
825 from fii_ar_oper_cust_summary_f) a
826 group by a.set_of_books_fk_key,
827 a.operating_unit_fk_key,
828 a.functional_currency,
829 a.calendar_day_fk_key,
830 a.calendar_date
831 order by a.set_of_books_fk_key,
832 a.operating_unit_fk_key,
833 a.calendar_date';
834
835
836 if g_debug_flag = 'Y' then
837 edw_log.debug_line('');
838 edw_log.debug_line(l_stmt);
839 end if;
840 execute immediate l_stmt;
841
842 l_stmt := 'truncate table '||g_fii_schema||'.fii_ar_operations_summary';
843 if g_debug_flag = 'Y' then
844 edw_log.debug_line('');
845 edw_log.debug_line(l_stmt);
846 end if;
847 execute immediate l_stmt;
848
849 l_stmt := '
850 insert into fii_ar_operations_summary T (
851 set_of_books_fk_key,
852 operating_unit_fk_key,
853 calendar_day_fk_key,
854 calendar_date,
855 functional_currency,
856 creation_date,
857 last_update_date,
858 amt_booked_b,
859 amt_booked_g,
860 amt_shipped_b,
861 amt_shipped_g,
862 amt_invoiced_oe_b,
863 amt_invoiced_oe_g,
864 amt_invoiced_ar_b,
865 amt_invoiced_ar_g,
866 amt_rev_earned_b,
867 amt_rev_earned_g,
868 amt_cash_received_b,
869 amt_cash_received_g,
870 order_count)
871 select
872 set_of_books_fk_key,
873 operating_unit_fk_key,
874 calendar_day_fk_key,
875 calendar_date,
876 functional_currency,
877 sysdate,
878 sysdate,
879 amt_booked_b,
880 amt_booked_g,
881 amt_shipped_b,
882 amt_shipped_g,
883 amt_invoiced_oe_b,
884 amt_invoiced_oe_g,
885 amt_invoiced_ar_b,
886 amt_invoiced_ar_g,
887 amt_rev_earned_b,
888 amt_rev_earned_g,
889 amt_cash_received_b,
890 amt_cash_received_g,
891 order_count
892 from '||g_fii_schema||'.fii_ar_operations_summary_new S
893 where set_of_books_fk_key > 0
894 and operating_unit_fk_key > 0
895 and calendar_day_fk_key > 0';
896
897 if g_debug_flag = 'Y' then
898 edw_log.debug_line('');
899 edw_log.debug_line(l_stmt);
900 end if;
901 execute immediate l_stmt;
902
903
904 End Populate_AR_OPER_SUMMARY;
905
906
907 -- ---------------------------------
908 -- Public PROCEDURES AND FUNCTIONS
909 -- ---------------------------------
910
911 --------------------------------------------------
912 -- PROCEDURE Populate_AR_OPER_SUMMARY
913 ---------------------------------------------------
914 procedure Refresh_Summary(Errbuf IN OUT NOCOPY VARCHAR2,
915 Retcode IN OUT NOCOPY VARCHAR2) IS
916
917 l_errbuf VARCHAR2(1000) := NULL;
918 l_retcode VARCHAR2(200) := NULL;
919 l_dir VARCHAR2(400);
920 l_stmt VARCHAR2(100);
921 BEGIN
922
923 l_stmt := ' ALTER SESSION SET global_names = false';
927 IF (fnd_profile.value('EDW_DEBUG') = 'Y') THEN
924 EXECUTE IMMEDIATE l_stmt;
925
926 -- DEBUG
928 edw_log.g_debug := TRUE;
929 END IF;
930
931 l_dir:=fnd_profile.value('EDW_LOGFILE_DIR');
932 if l_dir is null then
933 l_dir:='/sqlcom/log';
934 end if;
935 if g_debug_flag = 'Y' then
936 edw_log.put_names('FII_AR_OPER_SUMMARY.log','FII_AR_OPER_SUMMARY.out',l_dir);
937
938
939 fii_util.put_timestamp;
940 edw_log.put_line('Initialization');
941 fii_util.start_timer;
942 end if;
943 Init;
944 Drop_table('FII_AR_EDW_REV');
945 Drop_table('FII_AR_OLTP_CASH');
946 Drop_table('FII_AR_OLTP_INV');
947 Drop_table('FII_AR_OPERATIONS_SUMMARY_NEW');
948 if g_debug_flag = 'Y' then
949 fii_util.stop_timer;
950 fii_util.print_timer('Duration');
951
952
953 edw_log.put_line('');
954 edw_log.put_line('Extracting Cash information');
955
956 end if;
957 Extract_OLTP_CASH;
958 if g_debug_flag = 'Y' then
959 fii_util.stop_timer;
960 fii_util.print_timer('Duration');
961
962 edw_log.put_line('');
963 edw_log.put_line('Extracting Revenue and Invoice information');
964 fii_util.start_timer;
965 end if;
966 Extract_AR_INV_REV;
967 if g_debug_flag = 'Y' then
968 fii_util.stop_timer;
969 fii_util.print_timer('Duration');
970
971 edw_log.put_line('');
972 edw_log.put_line('Merging information into AR Operation Summary Table');
973 fii_util.start_timer;
974 end if;
975 Populate_AR_OPER_SUMMARY;
976 if g_debug_flag = 'Y' then
977 fii_util.stop_timer;
978 fii_util.print_timer('Duration');
979
980 end if;
981 Drop_table('FII_AR_EDW_REV');
982 Drop_table('FII_AR_OLTP_CASH');
983 Drop_table('FII_AR_OLTP_INV');
984 Drop_table('FII_AR_OPERATIONS_SUMMARY_NEW');
985
986 END Refresh_Summary;
987
988
989
990 End FII_AR_CASH_PKG;