[Home] [Help]
159: IF (FIIDIM_Debug) THEN
160: FII_UTIL.Write_Log ('Now start processing '|| 'Collector dimension');
161: END IF;
162:
163: --Secondly populate the table FII_COLLECTORS
164:
165: g_phase := 'populating FII_COLLECTORS table';
166:
167: FII_UTIL.truncate_table ('FII_COLLECTORS', 'FII', g_retcode);
161: END IF;
162:
163: --Secondly populate the table FII_COLLECTORS
164:
165: g_phase := 'populating FII_COLLECTORS table';
166:
167: FII_UTIL.truncate_table ('FII_COLLECTORS', 'FII', g_retcode);
168:
169: /* For transactions, the AR UI requires the account and site use to be specified. However, for receipts,
163: --Secondly populate the table FII_COLLECTORS
164:
165: g_phase := 'populating FII_COLLECTORS table';
166:
167: FII_UTIL.truncate_table ('FII_COLLECTORS', 'FII', g_retcode);
168:
169: /* For transactions, the AR UI requires the account and site use to be specified. However, for receipts,
170: it can be created with just the account information. Hence, in first sql, apart from picking up
171: non-null site_use_ids to get the collectors assigned at the site level, we do NVL(site_use_id, -2)
180: -- Added following statement for performance bug 5093270
181:
182: EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
183: --added last_update_date filter by vkazhipu for bug 5763652
184: INSERT /*+ APPEND PARALLEL(COLL) */ INTO FII_COLLECTORS COLL
185: (party_id,
186: cust_account_id,
187: site_use_id,
188: collector_id,
236: and uses.site_use_id = profs.site_use_id
237: AND profs.last_update_date <= g_collection_to_date);
238:
239: IF (FIIDIM_Debug) THEN
240: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows IN FII_COLLECTORS');
241: END IF;
242:
243: -- Statistics for FII_COLLECTORS will be anyway gathered via RSG so NOT gathering stats in the program
244:
239: IF (FIIDIM_Debug) THEN
240: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows IN FII_COLLECTORS');
241: END IF;
242:
243: -- Statistics for FII_COLLECTORS will be anyway gathered via RSG so NOT gathering stats in the program
244:
245: -- Since FII_COLLECTORS is used in MV query, we need to gather statistics for its MLOG as well
246:
247: g_phase := 'gather_table_stats for MLOG$_FII_COLLECTORS';
241: END IF;
242:
243: -- Statistics for FII_COLLECTORS will be anyway gathered via RSG so NOT gathering stats in the program
244:
245: -- Since FII_COLLECTORS is used in MV query, we need to gather statistics for its MLOG as well
246:
247: g_phase := 'gather_table_stats for MLOG$_FII_COLLECTORS';
248: FND_STATS.gather_table_stats
249: (ownname => g_schema_name,
243: -- Statistics for FII_COLLECTORS will be anyway gathered via RSG so NOT gathering stats in the program
244:
245: -- Since FII_COLLECTORS is used in MV query, we need to gather statistics for its MLOG as well
246:
247: g_phase := 'gather_table_stats for MLOG$_FII_COLLECTORS';
248: FND_STATS.gather_table_stats
249: (ownname => g_schema_name,
250: tabname => 'MLOG$_FII_COLLECTORS');
251:
246:
247: g_phase := 'gather_table_stats for MLOG$_FII_COLLECTORS';
248: FND_STATS.gather_table_stats
249: (ownname => g_schema_name,
250: tabname => 'MLOG$_FII_COLLECTORS');
251:
252: FND_CONCURRENT.Af_Commit;
253:
254: IF (FIIDIM_Debug) THEN
349: IF (FIIDIM_Debug) THEN
350: FII_UTIL.Write_Log (g_phase);
351: END IF;
352:
353: Delete from fii_collectors
354: where party_id in
355: (select from_entity_id
356: from hz_merge_party_history m,
357: hz_merge_dictionary d
425: l_last_start_date, l_last_end_date,
426: l_last_period_from, l_last_period_to_incr);
427:
428:
429: --BIS_COLLECTION_UTILITIES.get_last_refresh_dates('FII_COLLECTORS',
430: -- l_last_start_date,
431: -- l_last_end_date,
432: -- l_last_period_from,
433: -- l_last_period_to);
463:
464: -- Incremental Dimension Maintence
465: -- The sql in USING clause only considers the records changed after the
466: -- date of last initial/incremental run. The records thus obtained are
467: -- merged into the dimension table FII_COLLECTORS using MERGE command.
468: -- The changed records are updated whereas new records are inserted into FII_COLLECTORS.
469:
470: /* Bug 5019882. Records with cust_account_id = -2 may be seeded accounts and
471: we don't need to pick them up. So, added > 0 check to filter such records.. */
464: -- Incremental Dimension Maintence
465: -- The sql in USING clause only considers the records changed after the
466: -- date of last initial/incremental run. The records thus obtained are
467: -- merged into the dimension table FII_COLLECTORS using MERGE command.
468: -- The changed records are updated whereas new records are inserted into FII_COLLECTORS.
469:
470: /* Bug 5019882. Records with cust_account_id = -2 may be seeded accounts and
471: we don't need to pick them up. So, added > 0 check to filter such records.. */
472:
473: -- Added following statement for performance bug 5093270
474:
475: EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
476:
477: l_stmt := 'MERGE INTO FII_COLLECTORS dim
478: USING (SELECT NVL(prof.party_id,-2) party_id,
479: prof.cust_account_id cust_account_id,
480: NVL(site_use_id,-2) site_use_id,
481: prof.collector_id collector_id,
552:
553: EXECUTE IMMEDIATE l_stmt;
554:
555: IF (FIIDIM_Debug) THEN
556: FII_UTIL.Write_Log('Modified (Updation + Insertion) ' || SQL%ROWCOUNT || ' rows into FII_COLLECTORS');
557: END IF;
558:
559: -- Statistics for FII_COLLECTORS will be anyway gathered via RSG so NOT gathering stats in the program
560:
555: IF (FIIDIM_Debug) THEN
556: FII_UTIL.Write_Log('Modified (Updation + Insertion) ' || SQL%ROWCOUNT || ' rows into FII_COLLECTORS');
557: END IF;
558:
559: -- Statistics for FII_COLLECTORS will be anyway gathered via RSG so NOT gathering stats in the program
560:
561: -- From past experience, perf team has suggested not to analyze MLOG in incremental run.
562: -- So we will not be gathering stats for MLOG during incremental run.
563: