eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.FII_AR_RISK_PKG

Source


1 Package Body FII_AR_RISK_PKG AS
2 /* $Header: FIIAR09B.pls 120.2 2005/06/13 11:17:20 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 
18 
19 G_TABLE_NOT_EXIST      EXCEPTION;
20 G_SYNONYM_NOT_EXIST    EXCEPTION;
21 PRAGMA EXCEPTION_INIT(G_TABLE_NOT_EXIST, -942);
22 PRAGMA EXCEPTION_INIT(G_SYNONYM_NOT_EXIST, -1434);
23 
24 
25 -- ---------------------------------
26 -- PRIVATE PROCEDURES AND FUNCTIONS
27 -- ---------------------------------
28 
29 -------------------
30 -- PROCEDURE Init
31 -------------------
32 PROCEDURE Init is
33   l_stmt  		VARCHAR2(200);
34   l_status		VARCHAR2(30);
35   l_industry		VARCHAR2(30);
36   l_db_name1		VARCHAR2(30);
37   l_db_name2		VARCHAR2(30);
38   l_dummy		NUMBER := NULL;
39   i			NUMBER := 0;
40 
41   cursor source_instance is
42     select instance_code,
43 	   warehouse_to_instance_link
44     from   edw_source_instances;
45 
46 BEGIN
47 
48   -- --------------------------------------------------------
49   -- Find the schema owner and tablespace
50   -- FII_AR_RISK_INDICATOR is using
51   -- --------------------------------------------------------
52   IF(FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, g_fii_schema))
53   THEN NULL;
54   END IF;
55 
56   SELECT tablespace_name
57   INTO   g_tablespace
58   FROM   all_tables
59   WHERE  table_name = 'FII_AR_RISK_INDICATOR_F'
60   AND    owner = g_fii_schema;
61 
62 
63   -- ----------------------------
64   -- Find warehouse database name
65   -- ----------------------------
66   select  name
67   into	  l_db_name1
68   from    v$database;
69 
70   -- --------------------------------------------------------
71   -- Loop the complete set of instances
72   -- --------------------------------------------------------
73   FOR c in source_instance LOOP
74 
75     i := i + 1;
76     g_rec(i).instance_code := c.instance_code;
77     g_rec(i).db_link := c.warehouse_to_instance_link;
78 
79 
80     -- --------------------------------------------------
81     -- Check if we should load data from that instance
82     -- Currently, we just check if the fii_ar_oltp_open_inv_v
83     -- exist on the instance and has data.  This is a bit
84     -- cludgy but is a temporary solution until we build
85     -- the AR Installment Base Fact
86     -- --------------------------------------------------
87     BEGIN
88 
89       l_stmt := 'select sob_id from fii_ar_oltp_open_inv_v@'||
90               g_rec(i).db_link||' where rownum < 2';
91 
92 	if g_debug_flag = 'Y' then
93       		edw_log.debug_line('');
94       		edw_log.debug_line(l_stmt);
95         end if;
96       execute immediate l_stmt into l_dummy;
97 
98      IF l_dummy IS NOT NULL THEN
99 	g_rec(i).valid := TRUE;
100       ELSE
101 	g_rec(i).valid := FALSE;
102       END IF;
103 
104     exception when others then
105       g_rec(i).valid := FALSE;
106 
107     END;
108 
109 
110     -- ----------------------------
111     -- Check if same instance
112     -- ----------------------------
113     IF g_rec(i).valid THEN
114       l_stmt := 'select name '||
115 	    'from v$database@'||g_rec(i).db_link;
116 
117 	if g_debug_flag = 'Y' then
118       		edw_log.debug_line('');
119       		edw_log.debug_line(l_stmt);
120         end if;
121       execute immediate l_stmt into l_db_name2;
122 
123       IF (l_db_name1 = l_db_name2) THEN
124 	g_rec(i).same_inst := TRUE;
125       ELSE
126 	g_rec(i).same_inst := FALSE;
127       END IF;
128     END IF;
129 
130   END LOOP;
131 
132 end Init;
133 
134 
135 
136 ---------------------------------------------------
137 -- PROCEDURE DROP_TABLE
138 ---------------------------------------------------
139 procedure drop_table (p_table_name in varchar2) is
140   l_stmt varchar2(400);
141 Begin
142 
143   l_stmt:='drop table '||g_fii_schema||'.'||p_table_name;
144 
145   if g_debug_flag = 'Y' then
146   	edw_log.debug_line('');
147   	edw_log.debug_line(l_stmt);
148   end if;
149   execute immediate l_stmt;
150 
151   l_stmt:='drop synonym '||p_table_name;
152 
153   if g_debug_flag = 'Y' then
154   	edw_log.debug_line('');
155   	edw_log.debug_line(l_stmt);
156   end if;
157   execute immediate l_stmt;
158 
159 
160 Exception
161   WHEN G_TABLE_NOT_EXIST THEN
162     null;      -- Oracle 942, table does not exist, no actions
163   WHEN G_SYNONYM_NOT_EXIST THEN
164     null;      -- Oracle 1434, synonym not exist, no actions
165   WHEN OTHERS THEN
166     raise;
167 End Drop_Table;
168 
169 
170 
171 ---------------------------------------------------
172 -- PROCEDURE Extract_OLTP_OPEN_INV
173 ---------------------------------------------------
174 Procedure Extract_OLTP_OPEN_INV is
175   l_stmt varchar2(1000);
176 Begin
177 
178   -- ------------------------
179   -- Create the table needed
180   -- ------------------------
181   l_stmt := 'create table '||g_fii_schema||'.FII_AR_OLTP_OPEN_INV(
182 	instance_code		varchar2(30),
183 	org_id			number,
184 	sob_id			number,
185 	customer_site_id	number,
186 	receivable_g		number,
187 	receivable_b		number,
188 	receivable_t		number,
189 	unapp_receipt_g		number,
190 	unapp_receipt_b		number,
191 	unapp_receipt_t		number,
192 	functional_currency	varchar2(15),
193 	invoice_currency	varchar2(15),
194 	invoice_number		varchar2(30),
195 	installment_number 	number,
196 	invoice_date		date,
197 	due_date		date,
198 	type			varchar2(1))
199 	TABLESPACE '||g_tablespace||'
200 	NOLOGGING PCTFREE 5
201 	storage (INITIAL 4K NEXT 32K)';
202 
203     if g_debug_flag = 'Y' then
204     	edw_log.debug_line('');
205     	edw_log.debug_line(l_stmt);
206     end if;
207     execute immediate l_stmt;
208 
209 
210   -- ------------------------
211   -- Populate the table from
212   -- all the valid sources
213   -- ------------------------
214 
215   FOR i IN 1..g_rec.count LOOP
216 
217     IF (g_rec(i).valid) THEN
218       l_stmt := 'insert into '||g_fii_schema||'.FII_AR_OLTP_OPEN_INV T
219 	(instance_code,
220 	org_id,
221 	sob_id,
222 	customer_site_id,
223 	receivable_g,
224 	receivable_b,
225 	receivable_t,
226 	unapp_receipt_g,
227 	unapp_receipt_b,
228 	unapp_receipt_t,
229 	functional_currency,
230 	invoice_currency,
231 	invoice_number,
232 	installment_number,
233 	invoice_date,
234 	due_date,
235 	type)
236       select /*+ DRIVING_SITE(INV) */
237 	instance_code,
238 	org_id,
239 	sob_id,
240 	customer_site_id,
241 	receivable_g,
242 	receivable_b,
243 	receivable_t,
244 	unapp_receipt_g,
245 	unapp_receipt_b,
246 	unapp_receipt_t,
247 	functional_currency,
248 	invoice_currency,
249 	invoice_number,
250 	installment_number,
251 	invoice_date,
252 	due_date,
253 	type
254       from
255 	fii_ar_oltp_open_inv_v';
256 
257       IF (g_rec(i).same_inst) THEN
258         l_stmt := l_stmt||' CASH ';
259       ELSE
260         l_stmt := l_stmt||'@'||g_rec(i).db_link||' INV ';
261       END IF;
262 
263       if g_debug_flag = 'Y' then
264       	edw_log.debug_line('');
265       	edw_log.debug_line(l_stmt);
266       end if;
267 
268       execute immediate l_stmt;
269 
270      commit;
271 
272     END IF;  -- g_rec(i).valid
273 
274   END LOOP;
275 
276 End Extract_OLTP_OPEN_INV;
277 
278 
279 
280 --------------------------------------------------
281 -- PROCEDURE Populate_RISK_INDICATOR
282 ---------------------------------------------------
283 procedure Populate_RISK_INDICATOR is
284   l_stmt 	VARCHAR2(4000);
285 Begin
286 
287 
288   -- --------------------------------------
289   -- Populate open invoice installment fact
290   -- --------------------------------------
291   l_stmt := 'truncate table '||g_fii_schema||'.fii_ar_open_installment_f';
292 
293   if g_debug_flag = 'Y' then
294   	edw_log.debug_line('');
295   	edw_log.debug_line(l_stmt);
296   end if;
297   execute immediate l_stmt;
298 
299 
300   l_stmt := 'insert into fii_ar_open_installment_f (
301 	functional_currency_fk_key,
302 	set_of_books_fk_key,
303 	operating_unit_fk_key,
304 	customer_fk_key,
305 	receivable_g,
306 	receivable_b,
307 	receivable_t,
308 	unapp_receipt_g,
309 	unapp_receipt_b,
310 	unapp_receipt_t,
311 	functional_currency,
312 	invoice_currency,
313 	customer_name,
314 	invoice_number,
315 	installment_number,
316 	invoice_date,
317 	due_date,
318 	date_of_snapshot,
319 	age_bucket,
320 	type,
321 	creation_date,
322 	last_update_date)
323   select curr.crnc_currency_pk_key,
324 	 sob.fabk_fa_book_pk_key,
325 	 org.oper_operating_unit_pk_key,
326 	 cust.tprt_trade_partner_pk_key,
327 	 f.receivable_g,
328 	 f.receivable_b,
329 	 f.receivable_t,
330 	 f.unapp_receipt_g,
331 	 f.unapp_receipt_b,
332 	 f.unapp_receipt_t,
333 	 f.functional_currency,
334 	 f.invoice_currency,
335 	 cust.tprt_name,
336 	 f.invoice_number,
337 	 f.installment_number,
338 	 f.invoice_date,
339 	 f.due_date,
340 	 trunc(sysdate),
341 	 case when (f.due_date >= trunc(sysdate)) then 1
342               when (f.due_date between trunc(sysdate)-30 and trunc(sysdate)-1) then 2
343               when (f.due_date between trunc(sysdate)-60 and trunc(sysdate)-31) then 3
344               when (f.due_date between trunc(sysdate)-90 and trunc(sysdate)-61) then 4
345 	      else 5 end,
346 	 f.type,
347 	 trunc(sysdate),
348 	 trunc(sysdate)
349   from   '||g_fii_schema||'.fii_ar_oltp_open_inv f,
350 	 edw_gl_book_m		sob,
351 	 edw_currency_m		curr,
352 	 edw_organization_m	org,
353 	 edw_trd_partner_m	cust
354   where  sob.fabk_fa_book_pk = to_char(f.sob_id)||''-''||f.instance_code
355   and    curr.crnc_currency_pk_key > 0
356   and    sob.fabk_fa_book_pk_key >  0
357   and    org.oper_operating_unit_pk_key > 0
358   and    cust.tprt_trade_partner_pk_key > 0
359   and	 cust.tplo_tpartner_loc_pk = decode(f.customer_site_id, -1, ''NA_EDW'',
360 		to_char(f.customer_site_id)||''-''||f.instance_code||''-CUST_SITE_USE'' )
361   and    org.orga_organization_pk = to_char(f.org_id)||''-''||f.instance_code
362   and 	 curr.crnc_currency_pk = f.functional_currency' ;
363 
364   if g_debug_flag = 'Y' then
365   	edw_log.debug_line('');
366   	edw_log.debug_line(l_stmt);
367   end if;
368   execute immediate l_stmt;
369 
370   -- --------------------------------
371   -- Populate org level summary
372   -- --------------------------------
373   l_stmt := 'truncate table '||g_fii_schema||'.fii_ar_risk_indicator_f';
374   if g_debug_flag = 'Y' then
375   	edw_log.debug_line('');
376   	edw_log.debug_line(l_stmt);
377   end if;
378   execute immediate l_stmt;
379 
380   l_stmt := '
381   insert into fii_ar_risk_indicator_f (
382 	functional_currency_fk_key,
383 	set_of_books_fk_key,
384 	operating_unit_fk_key,
385 	operating_unit_name,
386 	ship_bklg_amt_b,
387 	ship_bklg_amt_g,
388 	dlqt_bklg_amt_b,
389 	dlqt_bklg_amt_g,
390 	open_rec_amt_b,
391 	open_rec_amt_g,
392 	pastdue_rec_amt_b,
393 	pastdue_rec_amt_g,
394 	date_of_snapshot,
395 	creation_date,
396 	last_update_date)
397   select
398 	a.functional_currency_fk_key,
399 	a.set_of_books_fk_key,
400 	a.operating_unit_fk_key,
401 	a.operating_unit_name,
402 	sum(a.ship_bklg_amt_b),
403 	sum(a.ship_bklg_amt_g),
404 	sum(a.dlqt_bklg_amt_b),
405 	sum(a.dlqt_bklg_amt_g),
406 	sum(open_rec_amt_b),
407 	sum(open_rec_amt_g),
408 	sum(pastdue_rec_amt_b),
409 	sum(pastdue_rec_amt_g),
410 	trunc(sysdate),
411 	trunc(sysdate),
412 	trunc(sysdate)
413   from ( select a.functional_currency_fk_key,
414 		a.set_of_books_fk_key,
415 		a.operating_unit_fk_key,
416 		b.name operating_unit_name,
417 		0 ship_bklg_amt_b,
418 		0 ship_bklg_amt_g,
419 		0 dlqt_bklg_amt_b,
420 		0 dlqt_bklg_amt_g,
421 		a.receivable_b open_rec_amt_b,
422 		a.receivable_g  open_rec_amt_g,
423 		decode(a.age_bucket, 1, 0, a.receivable_b) pastdue_rec_amt_b,
424 		decode(a.age_bucket, 1, 0, a.receivable_g) pastdue_rec_amt_g
425 	 from	fii_ar_open_installment_f a,
426 		edw_orga_oper_unit_ltc b
427 	 where  b.operating_unit_pk_key = a.operating_unit_fk_key
428      and    a.functional_currency_fk_key > 0
429      and    a.operating_unit_fk_key > 0
430      and    a.set_of_books_fk_key > 0
431 	 union all
432 	 select functional_currency_fk_key,
433 		set_of_books_fk_key,
434 		operating_unit_fk_key,
435 		operating_unit_name,
436 		ship_bklg_amt_b,
437 		ship_bklg_amt_g,
438 		dlqt_bklg_amt_b,
439 		dlqt_bklg_amt_g,
440 		0 open_rec_amt_b,
441 		0 open_rec_amt_g,
442 		0 pastdue_rec_amt_b,
443 		0 pastdue_rec_amt_g
444 	 from	isc_edw_backlog_sum1_f
445      where  functional_currency_fk_key > 0
446      and    set_of_books_fk_key > 0
447      and    operating_unit_fk_key > 0) a
448   group by	a.functional_currency_fk_key,
449 		a.set_of_books_fk_key,
450 		a.operating_unit_fk_key,
451 		a.operating_unit_name
452   order by 	a.set_of_books_fk_key,
453 		a.operating_unit_fk_key';
454 
455 
456   if g_debug_flag = 'Y' then
457   	edw_log.debug_line('');
458   	edw_log.debug_line(l_stmt);
459   end if;
460   execute immediate l_stmt;
461 
462 
463 End Populate_RISK_INDICATOR;
464 
465 
466 
467 
468 -- ---------------------------------
469 -- Public PROCEDURES AND FUNCTIONS
470 -- ---------------------------------
471 
472 --------------------------------------------------
473 -- PROCEDURE Refresh Summary
474 ---------------------------------------------------
475 procedure Refresh_Summary(Errbuf	IN OUT	NOCOPY VARCHAR2,
476 		          Retcode	IN OUT	NOCOPY VARCHAR2) IS
477 
478   l_errbuf	VARCHAR2(1000) := NULL;
479   l_retcode	VARCHAR2(200)  := NULL;
480   l_dir		VARCHAR2(400);
481   l_stmt	VARCHAR2(100);
482 BEGIN
483 
484   l_stmt := ' ALTER SESSION SET global_names = false';
485   EXECUTE IMMEDIATE l_stmt;
486 
487 -- DEBUG
488 --  IF (fnd_profile.value('EDW_DEBUG') = 'Y') THEN
489      edw_log.g_debug := TRUE;
490 --  END IF;
491 
492   l_dir:=fnd_profile.value('EDW_LOGFILE_DIR');
493   if l_dir is null then
494     l_dir:='/sqlcom/log';
495   end if;
496   if g_debug_flag = 'Y' then
497   	edw_log.put_names('FII_AR_RISK_INDICATOR_F.log','FII_AR_RISK_INDICATOR_F.out',l_dir);
498 
499 
500   	fii_util.put_timestamp;
501   	edw_log.put_line('Initialization');
502   	fii_util.start_timer;
503   end if;
504   Init;
505   Drop_table('FII_AR_OLTP_OPEN_INV');
506   if g_debug_flag = 'Y' then
507   	fii_util.stop_timer;
508   	fii_util.print_timer('Duration');
509 
510  	 fii_util.put_timestamp;
511   	edw_log.put_line('Summarzing OE backlog information');
512   	fii_util.start_timer;
513   end if;
514 -- DEBUG
515   isc_edw_backlog_sum1_f_c.Populate(l_errbuf, l_retcode);
516   if g_debug_flag = 'Y' then
517   	fii_util.stop_timer;
518   	fii_util.print_timer('Duration');
519 
520 
521   	edw_log.put_line('');
522   	edw_log.put_line('Extracting Open Invoice Installments');
523   	fii_util.start_timer;
524   end if;
525   Extract_OLTP_OPEN_INV;
526   if g_debug_flag = 'Y' then
527   	fii_util.stop_timer;
528   	fii_util.print_timer('Duration');
529 
530   	edw_log.put_line('');
531   	edw_log.put_line('Summarizing into AR Risk Summary Table');
532   	fii_util.start_timer;
533   end if;
534   Populate_RISK_INDICATOR;
535   if g_debug_flag = 'Y' then
536   	fii_util.stop_timer;
537   	fii_util.print_timer('Duration');
538   end if;
539 
540   Drop_table('FII_AR_OLTP_OPEN_INV');
541 
542 END Refresh_Summary;
543 
544 
545 
546 End FII_AR_RISK_PKG;