[Home] [Help]
PACKAGE BODY: APPS.FII_AR_TPDUE_TBL_REFRESH
Source
1 PACKAGE BODY FII_AR_TPDUE_TBL_REFRESH AS
2 /*$Header: FIIARTPB.pls 120.1.12000000.1 2007/02/23 02:29:32 applrt ship $*/
3
4 g_phase VARCHAR2(80);
5 g_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
6 g_retcode VARCHAR2(20) := NULL;
7 g_fii_user_id NUMBER;
8 g_fii_login_id NUMBER;
9 g_fii_sysdate DATE;
10 g_schema_name VARCHAR2(120) := 'FII';
11 l_profile VARCHAR2(1);
12 g_self_msg VARCHAR2(50):= FND_MESSAGE.get_string('FII', 'FII_AR_SELF');
13 G_LOGIN_INFO_NOT_AVABLE EXCEPTION;
14
15 --We use a un-shipped profile option FII_TEST_SYSDATE ("FII: Test Sysdate")
16 --to reset different sysdate so that we can test for snapshot tables.
17 g_test_sysdate DATE := to_date(FND_PROFILE.value('FII_TEST_SYSDATE'), 'DD/MM/YYYY');
18
19 ----------------------------------------------------
20 -- PROCEDURE Initialize (private)
21 --
22 ----------------------------------------------------
23
24 PROCEDURE Initialize IS
25
26 l_count NUMBER(15) := 0;
27 l_dir VARCHAR2(160);
28 l_check NUMBER;
29
30 BEGIN
31
32 g_phase := 'Do set up for log file';
33 ----------------------------------------------
34 -- Do set up for log file
35 ----------------------------------------------
36
37 l_dir := fnd_profile.value('BIS_DEBUG_LOG_DIRECTORY');
38 ------------------------------------------------------
39 -- Set default directory in CASE if the profile option
40 -- BIS_DEBUG_LOG_DIRECTORY is not set up
41 ------------------------------------------------------
42 if l_dir is NULL THEN
43 l_dir := FII_UTIL.get_utl_file_dir;
44 end if;
45
46 ----------------------------------------------------------------
47 -- FII_UTIL.initialize will get profile options FII_DEBUG_MODE
48 -- AND BIS_DEBUG_LOG_DIRECTORY AND set up the directory WHERE
49 -- the log files AND output files are written to
50 ----------------------------------------------------------------
51 FII_UTIL.initialize('FII_AR_TPDUE_TBL_REFRESH.log',
52 'FII_AR_TPDUE_TBL_REFRESH.out',l_dir,
53 'FII_AR_TPDUE_TBL_REFRESH');
54
55 g_phase := 'Obtain FII schema name AND other info';
56
57 -- Obtain FII schema name
58 g_schema_name := FII_UTIL.get_schema_name ('FII');
59
60 -- Obtain user ID, login ID AND sysdate
61 g_fii_user_id := FND_GLOBAL.USER_ID;
62 g_fii_login_id := FND_GLOBAL.LOGIN_ID;
63
64 SELECT sysdate INTO g_fii_sysdate FROM dual;
65
66 g_phase := 'Check FII schema name AND other info';
67 -- If any of the above values is not set, error out
68 IF (g_fii_user_id is NULL OR g_fii_login_id is NULL) THEN
69 FII_UTIL.Write_Log ('>>> Failed Intialization (login info not available)');
70 RAISE G_LOGIN_INFO_NOT_AVABLE;
71 END IF;
72
73 -- Determine if process will be run in debug mode
74 IF g_debug_flag = 'Y' THEN
75 FII_UTIL.Write_Log ('Debug On');
76 ELSE
77 FII_UTIL.Write_Log ('Debug Off');
78 END IF;
79
80 IF g_debug_flag = 'Y' THEN
81 FII_UTIL.Write_Log ('Initialize: Now start processing... ');
82 End If;
83
84 Exception
85
86 When others THEN
87 FII_UTIL.Write_Log ('Unexpected error WHEN calling Initialize...');
88 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
89 FII_UTIL.Write_Log ('Error Message: '|| substr(sqlerrm,1,180));
90 RAISE;
91
92 END Initialize;
93
94
95 ----------------------------------------------------
96 -- PROCEDURE REFRESH_AR_TPDUE_BASE_F (private)
97 --
98 -- This procedure will (fully) refresh table FII_AR_TPDUE_BASE_F
99 -- FROM FII_AR_NET_REC_BASE_MV AND FII_AR_DISPUTES_BASE_MV
100 ----------------------------------------------------
101 PROCEDURE REFRESH_AR_TPDUE_BASE_F IS
102
103 l_this_date DATE;
104 l_pp_this_date DATE;
105 l_pq_this_date DATE;
106 l_ly_this_date DATE;
107 l_min_start_date DATE;
108
109 BEGIN
110 g_self_msg := FND_MESSAGE.get_string('FII', 'FII_AR_SELF');
111 g_phase := 'Entering FII_AR_TPDUE_BASE_F';
112 IF g_debug_flag = 'Y' THEN
113 FII_UTIL.Write_Log ('> Entering FII_AR_TPDUE_BASE_F');
114 FII_UTIL.start_timer();
115 END IF;
116
117
118 g_phase := 'Populate l_this_date FROM BIS_SYSTEM_DATE';
119
120 --We use a un-shipped profile option FII_TEST_SYSDATE ("FII: Test Sysdate")
121 --to reset different sysdate so that we can test for snapshot tables.
122 if g_test_sysdate is NULL THEN
123 SELECT trunc(CURRENT_DATE_ID) INTO l_this_date
124 FROM BIS_SYSTEM_DATE;
125 ELSE
126 l_this_date := g_test_sysdate;
127 end if;
128
129 -- SELECT MIN(start_date) INTO l_min_start_date
130 -- FROM fii_time_ent_period;
131 --------------------------------------------------
132
133 -- g_phase := 'Populate l_pp_this_date, l_pq_this_date, l_ly_this_date, l_this_date_gov';
134
135 /*
136 sysdate = 20-Jul-2006
137
138 l_pp_this_date = 19-Jun-2006
139 l_pq_this_date = 19-Apr-2006
140 l_ly_this_date = 20-Jul-2005
141 l_this_date_gov = 31-Jul-2006
142 */
143
144 /*
145 SELECT NVL(fii_time_api.ent_sd_pper_end(l_this_date),l_min_start_date),
146 NVL(fii_time_api.ent_sd_pqtr_end(l_this_date),l_min_start_date),
147 NVL(fii_time_api.ent_sd_lyr_end(l_this_date),l_min_start_date),
148 NVL( fii_time_api.ent_cper_end(l_this_date),l_min_start_date)
149
150 INTO l_pp_this_date,
151 l_pq_this_date,
152 l_ly_this_date,
153 l_this_date_gov
154
155 FROM DUAL;*/
156
157 IF g_debug_flag = 'Y' THEN
158 FII_UTIL.Write_Log ('>> l_this_date = ' || l_this_date);
159 END IF;
160
161 --Always do a full refresh for snapshot tables
162 g_phase := 'Truncate table FII_AR_TPDUE_BASE_F';
163 FII_UTIL.truncate_table ('FII_AR_TPDUE_BASE_F', 'FII', g_retcode);
164
165 g_phase := 'Starting to populate table FII_AR_TPDUE_BASE_F';
166 IF g_debug_flag = 'Y' THEN
167 FII_UTIL.Write_Log ('>> Starting to populate table FII_AR_TPDUE_BASE_F');
168 END IF;
169
170
171 --------------------------------------------------------------------------
172 --Insert data FROM fii_ar_net_rec_base_mv by joining
173 -- to fii_time_structures, fii_customer_hierarchies, hz_parties
174 --Here we calculate ITD amounts for date on which the job is run
175 --fii_customer_hierarchies and hz_parties is used to avoid
176 --costly GT table population while running the report
177 --------------------------------------------------------------------------
178
179 insert /*+ append */ INTO FII_AR_TPDUE_BASE_F
180 ( parent_party_id,
181 party_id,
182 collector_id,
183 org_id,
184 IS_LEAF_FLAG,
185 IS_SELF_FLAG,
186 VIEW_BY,
187 VIEWBY_CODE,
188 CUST_NEXT_LEVEL_PARTY_ID,
189 CUST_PARENT_PARTY_ID,
190 CUST_CHILD_PARTY_ID,
191 past_due_open_amount_func,
192 past_due_open_amount_prim,
193 past_due_open_amount_sec,
194 wtd_terms_out_open_num_func,
195 wtd_terms_out_open_num_prim,
196 wtd_terms_out_open_num_sec,
197 wtd_DDSO_due_num_func,
198 wtd_DDSO_due_num_prim,
199 wtd_DDSO_due_num_sec,
200 current_open_amount_func,
201 current_open_amount_prim,
202 current_open_amount_sec,
203 LAST_UPDATE_DATE,
204 LAST_UPDATED_BY,
205 CREATION_DATE,
206 CREATED_BY,
207 LAST_UPDATE_LOGIN)
208 SELECT parent_party_id,
209 party_id,
210 collector_id,
211 org_id,
212 is_leaf_flag,
213 is_self_flag,
214 VIEW_BY,
215 VIEWBY_CODE,
216 CUST_NEXT_LEVEL_PARTY_ID,
217 CUST_PARENT_PARTY_ID,
218 CUST_CHILD_PARTY_ID,
219 SUM(past_due_open_amount_func) past_due_open_amount_func,
220 SUM(past_due_open_amount_prim) past_due_open_amount_prim,
221 SUM(past_due_open_amount_sec) past_due_open_amount_sec,
222 SUM(wtd_terms_out_open_num_func) wtd_terms_out_open_num_func,
223 SUM(wtd_terms_out_open_num_prim) wtd_terms_out_open_num_prim,
224 SUM(wtd_terms_out_open_num_sec) wtd_terms_out_open_num_sec,
225 SUM(wtd_DDSO_due_num_func) wtd_DDSO_due_num_func,
226 SUM(wtd_DDSO_due_num_prim) wtd_DDSO_due_num_prim,
227 SUM(wtd_DDSO_due_num_sec) wtd_DDSO_due_num_sec,
228 SUM(current_open_amount_func) current_open_amount_func,
229 SUM(current_open_amount_prim) current_open_amount_prim,
230 SUM(current_open_amount_sec) current_open_amount_sec,
231 g_fii_sysdate,
232 g_fii_user_id,
233 g_fii_sysdate,
234 g_fii_user_id,
235 g_fii_login_id FROM (
236 SELECT
237 b.parent_party_id parent_party_id,
238 b.party_id party_id,
239 b.collector_id collector_id,
240 b.org_id org_id,
241 cust.next_level_is_leaf_flag is_leaf_flag,
242 case when cust.parent_party_id = hz.party_id
243 and cust.next_level_is_leaf_flag <> 'Y'
244 then 'Y'
245 else 'N' end is_self_flag,
246 case when cust.parent_party_id = hz.party_id
247 and cust.next_level_is_leaf_flag <> 'Y'
248 then hz.party_name ||g_self_msg
249 else hz.party_name end view_by,
250 hz.party_id viewby_code,
251 cust.next_level_party_id cust_next_level_party_id,
252 cust.parent_party_id cust_parent_party_id,
253 cust.child_party_id cust_child_party_id,
254 past_due_open_amount_func,
255 past_due_open_amount_prim,
256 past_due_open_amount_sec,
257 wtd_terms_out_open_num_func,
258 wtd_terms_out_open_num_prim,
259 wtd_terms_out_open_num_sec,
260 wtd_DDSO_due_num_func,
261 wtd_DDSO_due_num_prim,
262 wtd_DDSO_due_num_sec,
263 current_open_amount_func,
264 current_open_amount_prim,
265 current_open_amount_sec
266 FROM fii_time_structures cal,
267 fii_ar_net_rec_base_mv b,
268 FII_CUSTOMER_HIERARCHIES cust,
269 HZ_PARTIES hz
270 WHERE cal.report_date = l_this_date
271 AND cal.time_id = b.time_id
272 AND cal.period_type_id = b.period_type_id
273 AND bitand(cal.record_type_id, 512) = 512
274 AND cust.parent_party_id = b.parent_party_id
275 AND cust.child_party_id = b.party_id
276 AND cust.next_level_party_id = hz.party_id
277 AND b.gid = 1025
278 )
279 GROUP BY parent_party_id,
280 party_id,
281 collector_id,
282 org_id,
283 is_leaf_flag,
284 is_self_flag,
285 VIEW_BY,
286 VIEWBY_CODE,
287 CUST_NEXT_LEVEL_PARTY_ID,
288 CUST_PARENT_PARTY_ID,
289 CUST_CHILD_PARTY_ID;
290
291
292
293
294 IF g_debug_flag = 'Y' THEN
295 FII_UTIL.stop_timer();
296 FII_UTIL.Write_Log ('FII_AR_TPDUE_BASE_F from fii_ar_net_rec_base_mv has been populated successfully');
297 FII_UTIL.Write_Log ('Inserted ' || SQL%ROWCOUNT || ' rows');
298 FII_UTIL.print_timer();
299 END IF;
300 commit;
301 --------------------------------------------------------------------------
302 --Insert data FROM fii_ar_disputes_base_mv by joining
303 -- to fii_time_structures, fii_customer_hierarchies, hz_parties
304 --Here we calculate ITD amounts for date on which the job is run
305 --fii_customer_hierarchies and hz_parties is used to avoid
309
306 --costly GT table population while running the report
307 --------------------------------------------------------------------------
308
310 insert /*+ append */ INTO FII_AR_TPDUE_BASE_F
311 ( parent_party_id,
312 party_id,
313 collector_id,
314 org_id,
315 IS_LEAF_FLAG,
316 IS_SELF_FLAG,
317 VIEW_BY,
318 VIEWBY_CODE,
319 CUST_NEXT_LEVEL_PARTY_ID,
320 CUST_PARENT_PARTY_ID,
321 CUST_CHILD_PARTY_ID,
322 past_due_dispute_amount_func,
323 past_due_dispute_amount_prim,
324 past_due_dispute_amount_sec,
325 LAST_UPDATE_DATE,
326 LAST_UPDATED_BY,
327 CREATION_DATE,
328 CREATED_BY,
329 LAST_UPDATE_LOGIN)
330 SELECT parent_party_id,
331 party_id,
332 collector_id,
333 org_id,
334 is_leaf_flag,
335 is_self_flag,
336 VIEW_BY,
337 VIEWBY_CODE,
338 CUST_NEXT_LEVEL_PARTY_ID,
339 CUST_PARENT_PARTY_ID,
340 CUST_CHILD_PARTY_ID,
341 SUM(past_due_dispute_amount_func) past_due_dispute_amount_func,
342 SUM(past_due_dispute_amount_prim) past_due_dispute_amount_prim,
343 SUM(past_due_dispute_amount_sec) past_due_dispute_amount_sec,
344 g_fii_sysdate,
345 g_fii_user_id,
346 g_fii_sysdate,
347 g_fii_user_id,
348 g_fii_login_id FROM (
349 SELECT
350 b.parent_party_id parent_party_id,
351 b.party_id party_id,
352 b.collector_id collector_id,
353 b.org_id org_id,
354 cust.next_level_is_leaf_flag is_leaf_flag,
355 case when cust.parent_party_id = hz.party_id
356 and cust.next_level_is_leaf_flag <> 'Y'
357 then 'Y'
358 else 'N' end is_self_flag,
359 case when cust.parent_party_id = hz.party_id
360 and cust.next_level_is_leaf_flag <> 'Y'
361 then hz.party_name ||g_self_msg else hz.party_name end view_by,
362 hz.party_id viewby_code,
363 cust.next_level_party_id cust_next_level_party_id,
364 cust.parent_party_id cust_parent_party_id,
365 cust.child_party_id cust_child_party_id,
366 past_due_dispute_amount_func,
367 past_due_dispute_amount_prim,
368 past_due_dispute_amount_sec
369 FROM fii_time_structures cal,
370 fii_ar_disputes_base_mv b,
371 FII_CUSTOMER_HIERARCHIES cust,
372 HZ_PARTIES hz
373 WHERE cal.report_date = l_this_date
374 AND cal.time_id = b.time_id
375 AND cal.period_type_id = b.period_type_id
376 AND bitand(cal.record_type_id, 512) = 512
377 AND cust.parent_party_id = b.parent_party_id
378 AND cust.child_party_id = b.party_id
379 AND cust.next_level_party_id = hz.party_id
380 )
381 GROUP BY parent_party_id,
382 party_id,
383 collector_id,
384 org_id,
385 is_leaf_flag,
386 is_self_flag,
387 VIEW_BY,
388 VIEWBY_CODE,
389 CUST_NEXT_LEVEL_PARTY_ID,
390 CUST_PARENT_PARTY_ID,
391 CUST_CHILD_PARTY_ID;
392
393
394
395 IF g_debug_flag = 'Y' THEN
396 FII_UTIL.stop_timer();
397 FII_UTIL.Write_Log ('FII_AR_TPDUE_BASE_F has been populated from fii_ar_disputes_base_mv successfully');
398 FII_UTIL.Write_Log ('Inserted ' || SQL%ROWCOUNT || ' rows');
399 FII_UTIL.print_timer();
400 END IF;
401
402 g_phase := 'Gather table stats for FII_AR_TPDUE_BASE_F';
403 fnd_stats.gather_table_stats (ownname=>g_schema_name,
404 tabname=>'FII_AR_TPDUE_BASE_F');
405
406 g_phase := 'Commit the change';
407 commit;
408
409 IF g_debug_flag = 'Y' THEN
410 FII_UTIL.Write_Log ('< Leaving FII_AR_TPDUE_BASE_F');
411 FII_UTIL.Write_Log (' ');
412 END IF;
413
414 EXCEPTION
415 WHEN no_data_found THEN
416 FII_MESSAGE.write_log(
417 msg_name => 'Data Not Found',
418 token_num => 0);
419 raise;
420
421 WHEN OTHERS THEN
422 FII_UTIL.Write_Log ('Other error in REFRESH_AR_TPDUE_BASE_F ');
423 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
424 FII_UTIL.Write_Log ('-->'|| sqlcode ||':'|| substr(sqlerrm,1,180));
425 rollback;
426 raise;
427
428 END REFRESH_AR_TPDUE_BASE_F;
429
430
431 ----------------------------------------------------
432 -- PROCEDURE REFRESH_AR_TPDUE_AGRT_F (private)
433 --
434 -- This procedure will (fully) refresh table FII_AR_TPDUE_AGRT_F
435 -- FROM FII_AR_NET_REC_AGRT_MV AND FII_AR_DISPUTES_AGRT_MV
436 ----------------------------------------------------
437 PROCEDURE REFRESH_AR_TPDUE_AGRT_F IS
438
439 l_this_date DATE;
440 l_pp_this_date DATE;
441 l_pq_this_date DATE;
442 l_ly_this_date DATE;
443 l_min_start_date DATE;
444
445 BEGIN
446
447 g_phase := 'Entering FII_AR_TPDUE_AGRT_F';
448 IF g_debug_flag = 'Y' THEN
449 FII_UTIL.Write_Log ('> Entering FII_AR_TPDUE_AGRT_F');
450 FII_UTIL.start_timer();
451 END IF;
452
453
454 g_phase := 'Populate l_this_date FROM BIS_SYSTEM_DATE';
455
456 --We use a un-shipped profile option FII_TEST_SYSDATE ("FII: Test Sysdate")
457 --to reset different sysdate so that we can test for snapshot tables.
458 if g_test_sysdate is NULL THEN
459 SELECT trunc(CURRENT_DATE_ID) INTO l_this_date
460 FROM BIS_SYSTEM_DATE;
461 ELSE
462 l_this_date := g_test_sysdate;
463 end if;
464
465
466 /*
467 sysdate = 20-Jul-2006
468
469 l_pp_this_date = 19-Jun-2006
470 l_pq_this_date = 19-Apr-2006
471 l_ly_this_date = 20-Jul-2005
472 l_this_date_gov = 31-Jul-2006
473 */
474
475 /*
479 NVL( fii_time_api.ent_cper_end(l_this_date),l_min_start_date)
476 SELECT NVL(fii_time_api.ent_sd_pper_end(l_this_date),l_min_start_date),
477 NVL(fii_time_api.ent_sd_pqtr_end(l_this_date),l_min_start_date),
478 NVL(fii_time_api.ent_sd_lyr_end(l_this_date),l_min_start_date),
480
481 INTO l_pp_this_date,
482 l_pq_this_date,
483 l_ly_this_date,
484 l_this_date_gov
485
486 FROM DUAL;*/
487
488 IF g_debug_flag = 'Y' THEN
489 FII_UTIL.Write_Log ('>> l_this_date = ' || l_this_date);
490 END IF;
491
492 --Always do a full refresh for snapshot tables
493 g_phase := 'Truncate table FII_AR_TPDUE_AGRT_F';
494 FII_UTIL.truncate_table ('FII_AR_TPDUE_AGRT_F', 'FII', g_retcode);
495
496 g_phase := 'Starting to populate table FII_AR_TPDUE_AGRT_F';
497 IF g_debug_flag = 'Y' THEN
498 FII_UTIL.Write_Log ('>> Starting to populate table FII_AR_TPDUE_AGRT_F');
499 END IF;
500
501
502 --------------------------------------------------------------------------
503 --Insert data FROM fii_ar_net_rec_base_mv by joining
504 -- to fii_time_structures, fii_customer_hierarchies, hz_parties
505 --Here we calculate ITD amounts for date on which the job is run
506 --fii_customer_hierarchies and hz_parties is used to avoid
507 --costly GT table population while running the report
508 --------------------------------------------------------------------------
509
510 insert /*+ append */ INTO FII_AR_TPDUE_AGRT_F
511 ( parent_party_id,
512 party_id,
513 collector_id,
514 org_id,
515 IS_LEAF_FLAG,
516 IS_SELF_FLAG,
517 VIEW_BY,
518 VIEWBY_CODE,
519 CUST_NEXT_LEVEL_PARTY_ID,
520 CUST_PARENT_PARTY_ID,
521 CUST_CHILD_PARTY_ID,
522 past_due_open_amount_func,
523 past_due_open_amount_prim,
524 past_due_open_amount_sec,
525 wtd_terms_out_open_num_func,
526 wtd_terms_out_open_num_prim,
527 wtd_terms_out_open_num_sec,
528 wtd_DDSO_due_num_func,
529 wtd_DDSO_due_num_prim,
530 wtd_DDSO_due_num_sec,
531 current_open_amount_func,
532 current_open_amount_prim,
533 current_open_amount_sec,
534 LAST_UPDATE_DATE,
535 LAST_UPDATED_BY,
536 CREATION_DATE,
537 CREATED_BY,
538 LAST_UPDATE_LOGIN)
539 SELECT
540 parent_party_id,
541 party_id,
542 collector_id,
543 org_id,
544 IS_LEAF_FLAG,
545 IS_SELF_FLAG,
546 VIEW_BY,
547 VIEWBY_CODE,
548 CUST_NEXT_LEVEL_PARTY_ID,
549 CUST_PARENT_PARTY_ID,
550 CUST_CHILD_PARTY_ID,
551 SUM(past_due_open_amount_func) past_due_open_amount_func,
552 SUM(past_due_open_amount_prim) past_due_open_amount_prim,
553 SUM(past_due_open_amount_sec) past_due_open_amount_sec,
554 SUM(wtd_terms_out_open_num_func) wtd_terms_out_open_num_func,
555 SUM(wtd_terms_out_open_num_prim) wtd_terms_out_open_num_prim,
556 SUM(wtd_terms_out_open_num_sec) wtd_terms_out_open_num_sec,
557 SUM(wtd_DDSO_due_num_func) wtd_DDSO_due_num_func,
558 SUM(wtd_DDSO_due_num_prim) wtd_DDSO_due_num_prim,
559 SUM(wtd_DDSO_due_num_sec) wtd_DDSO_due_num_sec,
560 SUM(current_open_amount_func) current_open_amount_func,
561 SUM(current_open_amount_prim) current_open_amount_prim,
562 SUM(current_open_amount_sec) current_open_amount_sec,
563 g_fii_sysdate,
564 g_fii_user_id,
565 g_fii_sysdate,
566 g_fii_user_id,
567 g_fii_login_id FROM (
568 SELECT
569 b.parent_party_id,
570 b.party_id,
571 b.collector_id,
572 b.org_id,
573 cust.next_level_is_leaf_flag is_leaf_flag,
574 case when cust.parent_party_id = hz.party_id
575 and cust.next_level_is_leaf_flag <> 'Y'
576 then 'Y'
577 else 'N' end is_self_flag,
578 case when cust.parent_party_id = hz.party_id
579 and cust.next_level_is_leaf_flag <> 'Y'
580 then hz.party_name ||g_self_msg else hz.party_name end view_by,
581 hz.party_id viewby_code,
582 cust.next_level_party_id cust_next_level_party_id,
583 cust.parent_party_id cust_parent_party_id,
584 cust.child_party_id cust_child_party_id,
585 past_due_open_amount_func,
586 past_due_open_amount_prim,
587 past_due_open_amount_sec,
588 wtd_terms_out_open_num_func,
589 wtd_terms_out_open_num_prim,
590 wtd_terms_out_open_num_sec,
591 wtd_DDSO_due_num_func,
592 wtd_DDSO_due_num_prim,
593 wtd_DDSO_due_num_sec,
594 current_open_amount_func,
595 current_open_amount_prim,
596 current_open_amount_sec
597 FROM fii_time_structures cal,
598 fii_ar_net_rec_agrt_mv b,
599 FII_CUSTOMER_HIERARCHIES cust,
600 HZ_PARTIES hz
601 WHERE cal.report_date = l_this_date
602 AND cal.time_id = b.time_id
603 AND cal.period_type_id = b.period_type_id
604 AND bitand(cal.record_type_id, 512) = 512
605 AND cust.child_party_id = b.party_id
606 AND b.parent_party_id in (SELECT decode (cust1.next_level_is_leaf_flag,
607 'Y', cust1.parent_party_id, cust.child_party_id)
608 FROM fii_customer_hierarchies cust1
609 WHERE cust1.next_level_party_id = cust.child_party_id
610 AND cust1.child_party_id = cust.child_party_id
611 AND cust1.child_party_id <> cust1.parent_party_id)
612 AND cust.next_level_party_id = hz.party_id
613 AND b.gid = 1025
614 )
615 GROUP BY parent_party_id,
616 party_id,
617 collector_id,
618 org_id,
619 IS_LEAF_FLAG,
620 IS_SELF_FLAG,
621 VIEW_BY,
625 CUST_CHILD_PARTY_ID;
622 VIEWBY_CODE,
623 CUST_NEXT_LEVEL_PARTY_ID,
624 CUST_PARENT_PARTY_ID,
626
627
628
629 IF g_debug_flag = 'Y' THEN
630 FII_UTIL.stop_timer();
631 FII_UTIL.Write_Log ('FII_AR_TPDUE_AGRT_F from fii_ar_net_rec_agrt_mv has been populated successfully');
632 FII_UTIL.Write_Log ('Inserted ' || SQL%ROWCOUNT || ' rows');
633 FII_UTIL.print_timer();
634 END IF;
635 commit;
636 --------------------------------------------------------------------------
637 --Insert data FROM fii_ar_disputes_agrt_mv by joining
638 -- to fii_time_structures, fii_customer_hierarchies, hz_parties
639 --Here we calculate ITD amounts for date on which the job is run
640 --fii_customer_hierarchies and hz_parties is used to avoid
641 --costly GT table population while running the report
642 --------------------------------------------------------------------------
643
644
645 insert /*+ append */ INTO FII_AR_TPDUE_AGRT_F
646 ( parent_party_id,
647 party_id,
648 collector_id,
649 org_id,
650 IS_LEAF_FLAG,
651 IS_SELF_FLAG,
652 VIEW_BY,
653 VIEWBY_CODE,
654 CUST_NEXT_LEVEL_PARTY_ID,
655 CUST_PARENT_PARTY_ID,
656 CUST_CHILD_PARTY_ID,
657 past_due_dispute_amount_func,
658 past_due_dispute_amount_prim,
659 past_due_dispute_amount_sec,
660 LAST_UPDATE_DATE,
661 LAST_UPDATED_BY,
662 CREATION_DATE,
663 CREATED_BY,
664 LAST_UPDATE_LOGIN)
665 SELECT
666 parent_party_id,
667 party_id,
668 collector_id,
669 org_id,
670 IS_LEAF_FLAG,
671 IS_SELF_FLAG,
672 VIEW_BY,
673 VIEWBY_CODE,
674 CUST_NEXT_LEVEL_PARTY_ID,
675 CUST_PARENT_PARTY_ID,
676 CUST_CHILD_PARTY_ID,
677 SUM(past_due_dispute_amount_func) past_due_dispute_amount_func,
678 SUM(past_due_dispute_amount_prim) past_due_dispute_amount_prim,
679 SUM(past_due_dispute_amount_sec) past_due_dispute_amount_sec,
680 g_fii_sysdate,
681 g_fii_user_id,
682 g_fii_sysdate,
683 g_fii_user_id,
684 g_fii_login_id FROM(
685 SELECT
686 b.parent_party_id,
687 b.party_id,
688 b.collector_id,
689 b.org_id,
690 cust.next_level_is_leaf_flag is_leaf_flag,
691 case when cust.parent_party_id = hz.party_id
692 and cust.next_level_is_leaf_flag <> 'Y'
693 then 'Y'
694 else 'N' end is_self_flag,
695 case when cust.parent_party_id = hz.party_id
696 and cust.next_level_is_leaf_flag <> 'Y'
697 then hz.party_name ||g_self_msg else hz.party_name end view_by,
698 hz.party_id viewby_code,
699 cust.next_level_party_id cust_next_level_party_id,
700 cust.parent_party_id cust_parent_party_id,
701 cust.child_party_id cust_child_party_id,
702 past_due_dispute_amount_func past_due_dispute_amount_func,
703 past_due_dispute_amount_prim past_due_dispute_amount_prim,
704 past_due_dispute_amount_sec past_due_dispute_amount_sec
705 FROM fii_time_structures cal,
706 fii_ar_disputes_agrt_mv b,
707 FII_CUSTOMER_HIERARCHIES cust,
708 HZ_PARTIES hz
709 WHERE cal.report_date = l_this_date
710 AND cal.time_id = b.time_id
711 AND cal.period_type_id = b.period_type_id
712 AND bitand(cal.record_type_id, 512) = 512
713 AND cust.child_party_id = b.party_id
714 AND b.parent_party_id in (SELECT decode (cust1.next_level_is_leaf_flag,
715 'Y', cust1.parent_party_id, cust.child_party_id)
716 FROM fii_customer_hierarchies cust1
717 WHERE cust1.next_level_party_id = cust.child_party_id
718 AND cust1.child_party_id = cust.child_party_id
719 AND cust1.child_party_id <> cust1.parent_party_id)
720 AND cust.next_level_party_id = hz.party_id)
721 GROUP BY parent_party_id,
722 party_id,
723 collector_id,
724 org_id,
725 IS_LEAF_FLAG,
726 IS_SELF_FLAG,
727 VIEW_BY,
728 VIEWBY_CODE,
729 CUST_NEXT_LEVEL_PARTY_ID,
730 CUST_PARENT_PARTY_ID,
731 CUST_CHILD_PARTY_ID;
732
733
734 IF g_debug_flag = 'Y' THEN
735 FII_UTIL.stop_timer();
736 FII_UTIL.Write_Log ('FII_AR_TPDUE_AGRT_F has been populated from fii_ar_disputes_agrt_mv successfully');
737 FII_UTIL.Write_Log ('Inserted ' || SQL%ROWCOUNT || ' rows');
738 FII_UTIL.print_timer();
739 END IF;
740
741 g_phase := 'Gather table stats for FII_AR_TPDUE_AGRT_F';
742 fnd_stats.gather_table_stats (ownname=>g_schema_name,
743 tabname=>'FII_AR_TPDUE_AGRT_F');
744
745 g_phase := 'Commit the change';
746 commit;
747
748 IF g_debug_flag = 'Y' THEN
749 FII_UTIL.Write_Log ('< Leaving FII_AR_TPDUE_AGRT_F');
750 FII_UTIL.Write_Log (' ');
751 END IF;
752
753 EXCEPTION
754 WHEN no_data_found THEN
755 FII_MESSAGE.write_log(
756 msg_name => 'Data Not Found',
757 token_num => 0);
758 raise;
759
760 WHEN OTHERS THEN
761 FII_UTIL.Write_Log ('Other error in REFRESH_AR_TPDUE_AGRT_F ');
762 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
763 FII_UTIL.Write_Log ('-->'|| sqlcode ||':'|| substr(sqlerrm,1,180));
764 rollback;
765 raise;
766
767 END REFRESH_AR_TPDUE_AGRT_F;
768
769
770
771 ----------------------------------------------------------
772 -- PROCEDURE MAIN (public)
773 --
777 retcode IN OUT NOCOPY VARCHAR2) IS
774 -- This procedure will (fully) refresh all snapshot tables
775 ----------------------------------------------------------
776 PROCEDURE Main (errbuf IN OUT NOCOPY VARCHAR2,
778
779 ret_val BOOLEAN := FALSE;
780
781 Begin
782
783 g_phase := 'Entering Main';
784 IF g_debug_flag = 'Y' THEN
785 FII_UTIL.Write_Log ('Entering Main');
786 END IF;
787
788 g_phase := 'Calling Initialize';
789 IF g_debug_flag = 'Y' THEN
790 FII_UTIL.Write_Log ('Calling Initialize');
791 END IF;
792
793 Initialize;
794
795 g_phase := 'Populating FII_AR_TPDUE_BASE_F';
796 IF g_debug_flag = 'Y' THEN
797 FII_UTIL.Write_Log ('Populating FII_AR_TPDUE_BASE_F');
798 END IF;
799
800 REFRESH_AR_TPDUE_BASE_F;
801
802 g_phase := 'Populating FII_AR_TPDUE_AGRT_F';
803 IF g_debug_flag = 'Y' THEN
804 FII_UTIL.Write_Log ('Populating FII_AR_TPDUE_AGRT_F');
805 END IF;
806
807 REFRESH_AR_TPDUE_AGRT_F;
808
809
810 g_phase := 'Exiting after successful completion';
811 IF g_debug_flag = 'Y' THEN
812 FII_UTIL.Write_Log ('Exiting after successful completion');
813 END IF;
814
815
816 EXCEPTION
817
818 WHEN OTHERS THEN
819
820 FII_UTIL.Write_Log ('Other error in Main ');
821 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
822 FII_UTIL.Write_Log ('-->'|| sqlcode ||':'|| substr(sqlerrm,1,180));
823
824 FND_CONCURRENT.Af_Rollback;
825 retcode := sqlcode;
826 errbuf := sqlerrm;
827 ret_val := FND_CONCURRENT.Set_Completion_Status
828 (status => 'ERROR', message => substr(errbuf,1,180));
829
830 END Main;
831
832
833 END FII_AR_TPDUE_TBL_REFRESH;