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.

APPS.BIL_BI_PIPELINE_F_PKG dependencies on BIL_BI_PIPELINE_STG

Line 131: bil_bi_pipeline_stg stg

127: SELECT
128: DISTINCT txn_currency,
129: effective_date
130: FROM
131: bil_bi_pipeline_stg stg
132: )
133: ) rates
134: ON
135: (

Line 217: SELECT lead_number FROM bil_bi_pipeline_stg

213: -- l_prim_miss BOOLEAN :=FALSE;
214: -- l_sec_miss BOOLEAN :=FALSE;
215:
216: cursor c_item_prod is
217: SELECT lead_number FROM bil_bi_pipeline_stg
218: WHERE nvl(product_category_id,-1)=-1;
219:
220: l_proc VARCHAR2(100);
221: BEGIN

Line 238: UPDATE bil_bi_pipeline_stg stg

234: p_msg => 'Start of Procedure '|| l_proc);
235: END IF;
236:
237:
238: UPDATE bil_bi_pipeline_stg stg
239: SET (stg.prim_conversion_rate,conversion_rate_s) =
240: (
241: select
242: exchange_rate,exchange_rate_s

Line 273: FROM BIL_BI_PIPELINE_STG

269: --IF (g_refresh_flag <> 'Y') THEN
270:
271: SELECT count(1)
272: INTO l_conv_rate_cnt
273: FROM BIL_BI_PIPELINE_STG
274: WHERE (prim_conversion_rate < 0 OR prim_conversion_rate IS NULL)
275: OR (g_sec_currency IS NOT NULL and (conversion_rate_s < 0 OR conversion_rate_s IS NULL)) ;
276:
277: IF (l_conv_rate_cnt >0) THEN

Line 300: FROM BIL_BI_PIPELINE_STG stg;

296:
297: SELECT MIN(stg.SNAP_DATE), Max(stg.SNAP_DATE)
298: ,MIN(stg.Effective_DATE), Max(stg.Effective_DATE)
299: INTO l_stg_min_txn_dt, l_stg_max_txn_dt, l_stg_min_eff_dt, l_stg_max_eff_dt
300: FROM BIL_BI_PIPELINE_STG stg;
301:
302: IF (l_stg_min_txn_dt < l_stg_min_eff_dt) THEN
303: l_stg_min_dt := l_stg_min_txn_dt;
304: ELSE

Line 1362: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_PIPELINE_STG');

1358: );
1359: END IF;
1360: BIS_COLLECTION_UTILITIES.deleteLogForObject ('BIL_BI_PIPELINE_F');
1361: BIS_COLLECTION_UTILITIES.deleteLogForObject ('BIL_BI_PIPEC_F');
1362: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_PIPELINE_STG');
1363: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_PIPELINE_F');
1364: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_PIPEC_F');
1365: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_DENLOG_STG');
1366: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_TIME');

Line 1372: SELECT sum(cnt) INTO l_count FROM (select count(1) cnt from bil_bi_pipeline_stg

1368: ELSE
1369: ----------------------------------------------------
1370: -- CHECK FOR RESUME
1371: ----------------------------------------------------
1372: SELECT sum(cnt) INTO l_count FROM (select count(1) cnt from bil_bi_pipeline_stg
1373: union all select count(1) cnt from BIL_BI_DENLOG_STG)
1374: ;
1375: IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1376: bil_bi_util_collection_pkg.writeLog

Line 1408: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_PIPELINE_STG');

1404:
1405:
1406: -- rebuild baseline since stage table can be in any state
1407:
1408: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_PIPELINE_STG');
1409:
1410: --if gap is lessthan 3 days, we don't need to rebuild baseline
1411: -- since we will use 7.0.1 approach to insert into staging
1412:

Line 1448: bil_bi_util_collection_pkg.analyze_table('BIL_BI_PIPELINE_STG',TRUE, 10, 'GLOBAL');

1444: p_msg =>'Summary Error Check Successful!'
1445: );
1446: END IF;
1447:
1448: bil_bi_util_collection_pkg.analyze_table('BIL_BI_PIPELINE_STG',TRUE, 10, 'GLOBAL');
1449: bil_bi_util_collection_pkg.analyze_table('BIL_BI_DENLOG_STG',TRUE, 10, 'GLOBAL');
1450: bil_bi_util_collection_pkg.analyze_table('BIL_BI_OPDTL_DENLOG_TMP',TRUE, 10, 'GLOBAL');
1451:
1452:

Line 1486: from bil_bi_pipeline_stg;

1482:
1483:
1484: select count(1)
1485: into l_stg_count
1486: from bil_bi_pipeline_stg;
1487:
1488:
1489: IF l_stg_count > 0 THEN
1490: Summary_Err_Check_SmallGap(x_valid_curr => l_valid_curr,

Line 1508: bil_bi_util_collection_pkg.analyze_table('BIL_BI_PIPELINE_STG',TRUE, 10, 'GLOBAL');

1504: p_msg =>'Summary Error Check Successful!'
1505: );
1506: END IF;
1507:
1508: bil_bi_util_collection_pkg.analyze_table('BIL_BI_PIPELINE_STG',TRUE, 10, 'GLOBAL');
1509:
1510:
1511: --Insert the first day of the gap, since gap_fill will only insert from 2nd day on
1512: Ins_Into_CurrSum_SmGap(l_failure_from, l_week, l_period, l_qtr,

Line 1529: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_PIPELINE_STG');

1525: Gap_Fill(l_failure_from+1, l_failure_to, l_curr_coll_start, l_mode, l_small_gap);
1526:
1527:
1528: l_resume_flag := 'Y';
1529: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_PIPELINE_STG');
1530: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_DENLOG_STG');
1531: --execute immediate 'Truncate table BIL_BI_DENLOG_STG';
1532: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_OPDTL_DENLOG_TMP');
1533:

Line 1791: bil_bi_util_collection_pkg.analyze_table('BIL_BI_PIPELINE_STG',TRUE, 10, 'GLOBAL');

1787: -- build up the denlog_tmp table for the gap range
1788: -- l_date instead of l_date -1 is because denlog will check for time < l_date
1789: Build_Denlog(l_start_date+1, l_date, l_curr_coll_start, 'INIT');
1790:
1791: bil_bi_util_collection_pkg.analyze_table('BIL_BI_PIPELINE_STG',TRUE, 10, 'GLOBAL');
1792: bil_bi_util_collection_pkg.analyze_table('BIL_BI_DENLOG_STG',TRUE, 10, 'GLOBAL');
1793: bil_bi_util_collection_pkg.analyze_table('BIL_BI_OPDTL_DENLOG_TMP',TRUE, 10, 'GLOBAL');
1794:
1795: Populate_Currency_Rate('INIT');

Line 1945: bil_bi_util_collection_pkg.analyze_table('BIL_BI_PIPELINE_STG',TRUE, 10, 'GLOBAL');

1941:
1942:
1943: Build_Denlog(l_last_run+2, l_date, l_curr_coll_start, 'INCR');
1944:
1945: bil_bi_util_collection_pkg.analyze_table('BIL_BI_PIPELINE_STG',TRUE, 10, 'GLOBAL');
1946: bil_bi_util_collection_pkg.analyze_table('BIL_BI_DENLOG_STG',TRUE, 10, 'GLOBAL');
1947: bil_bi_util_collection_pkg.analyze_table('BIL_BI_OPDTL_DENLOG_TMP',TRUE, 10, 'GLOBAL');
1948:
1949:

Line 2195: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_PIPELINE_STG');

2191:
2192:
2193: -- clean up
2194:
2195: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_PIPELINE_STG');
2196: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_DENLOG_STG');
2197: --execute immediate 'Truncate table BIL_BI_DENLOG_STG';
2198: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_OPDTL_DENLOG_TMP');
2199: -- regular snapshot

Line 2833: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_PIPELINE_STG');

2829: retcode := -1;
2830: WHEN OTHERS Then
2831:
2832:
2833: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_PIPELINE_STG');
2834: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_DENLOG_STG');
2835: --execute immediate 'Truncate table BIL_BI_DENLOG_STG';
2836: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_OPDTL_DENLOG_TMP');
2837:

Line 2890: from bil_bi_pipeline_stg stg

2886: effective_date
2887: FROM
2888: (
2889: select /*+ parallel(stg) */ txn_currency, effective_date
2890: from bil_bi_pipeline_stg stg
2891: where effective_date >= g_global_start_date
2892: UNION ALL
2893: select /*+ parallel(stg) */ currency_code, decision_date
2894: from BIL_BI_DENLOG_STG stg

Line 2945: from bil_bi_pipeline_stg stg

2941: effective_date
2942: FROM
2943: (
2944: select txn_currency, effective_date
2945: from bil_bi_pipeline_stg stg
2946: where effective_date >= g_global_start_date
2947: UNION ALL
2948: select currency_code, decision_date
2949: from BIL_BI_DENLOG_STG stg

Line 3039: SELECT lead_number FROM bil_bi_pipeline_stg

3035: -- l_prim_miss BOOLEAN :=FALSE;
3036: -- l_sec_miss BOOLEAN :=FALSE;
3037:
3038: cursor c_item_prod is
3039: SELECT lead_number FROM bil_bi_pipeline_stg
3040: WHERE nvl(product_category_id,-1)=-1
3041: and effective_date >= g_global_start_date
3042: union all
3043: SELECT lead_number FROM BIL_BI_DENLOG_STG

Line 3066: UPDATE /+ parallel(stg) / bil_bi_pipeline_stg stg

3062: p_msg => 'Start of Procedure '|| l_proc);
3063: END IF;
3064:
3065: IF(p_mode = 'INIT') THEN
3066: UPDATE /*+ parallel(stg) */ bil_bi_pipeline_stg stg
3067: SET (stg.prim_conversion_rate,conversion_rate_s) =
3068: (
3069: select
3070: exchange_rate,exchange_rate_s

Line 3094: UPDATE bil_bi_pipeline_stg stg

3090: stg.prim_conversion_rate = curr_rate.exchange_rate,
3091: stg.conversion_rate_s = curr_rate.exchange_rate_s;
3092:
3093: ELSE
3094: UPDATE bil_bi_pipeline_stg stg
3095: SET (stg.prim_conversion_rate,conversion_rate_s) =
3096: (
3097: select
3098: exchange_rate,exchange_rate_s

Line 3136: select /+ parallel(stg) / count(1) cnt from BIL_BI_PIPELINE_STG stg

3132: IF(p_mode = 'INIT') THEN
3133: SELECT sum(cnt)
3134: INTO l_conv_rate_cnt
3135: FROM (
3136: select /*+ parallel(stg) */ count(1) cnt from BIL_BI_PIPELINE_STG stg
3137: WHERE ((prim_conversion_rate < 0 OR prim_conversion_rate IS NULL)
3138: OR (g_sec_currency IS NOT NULL and (conversion_rate_s < 0 OR conversion_rate_s IS NULL)))
3139: and effective_date >= g_global_start_date
3140: union all

Line 3150: select count(1) cnt from BIL_BI_PIPELINE_STG

3146: ELSE
3147: SELECT sum(cnt)
3148: INTO l_conv_rate_cnt
3149: FROM (
3150: select count(1) cnt from BIL_BI_PIPELINE_STG
3151: WHERE ((prim_conversion_rate < 0 OR prim_conversion_rate IS NULL)
3152: OR (g_sec_currency IS NOT NULL and (conversion_rate_s < 0 OR conversion_rate_s IS NULL)))
3153: and effective_date >= g_global_start_date
3154: union all

Line 3187: FROM (select /+ parallel(stg) stg/ snap_date , effective_date from BIL_BI_PIPELINE_STG stg

3183: IF(p_mode = 'INIT') THEN
3184: SELECT MIN(stg.SNAP_DATE), Max(stg.SNAP_DATE)
3185: ,MIN(stg.Effective_DATE), Max(stg.Effective_DATE)
3186: INTO l_stg_min_txn_dt, l_stg_max_txn_dt, l_stg_min_eff_dt, l_stg_max_eff_dt
3187: FROM (select /*+ parallel(stg) stg*/ snap_date , effective_date from BIL_BI_PIPELINE_STG stg
3188: where effective_date >= g_global_start_date
3189: union all
3190: select /*+ parallel(stg) stg*/last_update_date snap_date, decision_date effective_date from BIL_BI_DENLOG_STG stg
3191: where decision_date >= g_global_start_date) stg;

Line 3196: FROM (select snap_date , effective_date from BIL_BI_PIPELINE_STG

3192: ELSE
3193: SELECT MIN(stg.SNAP_DATE), Max(stg.SNAP_DATE)
3194: ,MIN(stg.Effective_DATE), Max(stg.Effective_DATE)
3195: INTO l_stg_min_txn_dt, l_stg_max_txn_dt, l_stg_min_eff_dt, l_stg_max_eff_dt
3196: FROM (select snap_date , effective_date from BIL_BI_PIPELINE_STG
3197: where effective_date >= g_global_start_date
3198: union all
3199: select last_update_date snap_date, decision_date effective_date from BIL_BI_DENLOG_STG
3200: where decision_date >= g_global_start_date) stg;

Line 3459: from BIL_BI_PIPELINE_STG

3455: least(sysdate, stg.effective_date)) ) txn_date,
3456: decode(sign(nvl(stg.prim_conversion_rate,-1)),-1,'P') prim_curr_type,
3457: decode(sign(nvl(stg.CONVERSION_RATE_S,-1)),-1,'S') sec_curr_type
3458: FROM ( select txn_currency , effective_date, prim_conversion_rate, CONVERSION_RATE_S
3459: from BIL_BI_PIPELINE_STG
3460: WHERE
3461: ((prim_conversion_rate < 0 OR prim_conversion_rate IS NULL )
3462: OR (g_sec_currency IS NOT NULL and (conversion_rate_s < 0 OR conversion_rate_s IS NULL)))
3463: and effective_date >= g_global_start_date

Line 3882: bil_bi_pipeline_stg stg,

3878: null prvyr_open_amt_qtr_s,
3879: null prvyr_open_amt_yr_s
3880:
3881: FROM
3882: bil_bi_pipeline_stg stg,
3883: fii_time_day time
3884: WHERE stg.effective_date = TIME.report_date
3885: AND forecast_rollup_flag = 'Y'
3886: AND TIME.report_date_julian >= p_min_date_id AND TIME.report_date_julian+0 <= p_max_date_id

Line 4220: bil_bi_pipeline_stg stg,

4216: WHEN time.ent_year_id = time1.ent_year_id and opp_open_status_flag = 'Y'
4217: THEN decode(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
4218: stg.sales_credit_amount*conversion_rate_s) ELSE NULL END)
4219: FROM
4220: bil_bi_pipeline_stg stg,
4221: fii_time_day time,
4222: fii_time_day time1
4223: WHERE
4224: stg.snap_date = time1.report_date

Line 4385: bil_bi_pipeline_stg stg,

4381: WHEN time.ent_year_id = time1.ent_year_id and opp_open_status_flag = 'Y'
4382: THEN decode(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
4383: stg.sales_credit_amount*conversion_rate_s) ELSE NULL END)
4384: FROM
4385: bil_bi_pipeline_stg stg,
4386: fii_time_day time,
4387: fii_time_day time1
4388: WHERE
4389: stg.snap_date = time1.report_date

Line 4422: p_msg => 'Inserted '|| g_row_num||' into BIL_BI_PIPELINE_F table from BIL_BI_PIPELINE_STG ');

4418: IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
4419: bil_bi_util_collection_pkg.writeLog(
4420: p_log_level => fnd_log.LEVEL_EVENT,
4421: p_module => g_pkg || l_proc ,
4422: p_msg => 'Inserted '|| g_row_num||' into BIL_BI_PIPELINE_F table from BIL_BI_PIPELINE_STG ');
4423: END IF;
4424:
4425:
4426:

Line 4502: INSERT /+ APPEND PARALLEL(stg) / INTO bil_bi_pipeline_stg stg(

4498: WHERE report_date = trunc(p_start_date);
4499:
4500:
4501:
4502: INSERT /*+ APPEND PARALLEL(stg) */ INTO bil_bi_pipeline_stg stg(
4503: line_id,
4504: credit_id,
4505: SALES_GROUP_ID,
4506: SALESREP_ID,

Line 5497: DELETE /+ parallel(stg1) / FROM bil_bi_pipeline_stg stg1

5493: IF (p_mode = 'INIT') THEN
5494:
5495: IF (p_small_gap = FALSE) THEN
5496: -- if gap bigger than 3 days, delet and insert into staging for current date
5497: DELETE /*+ parallel(stg1) */ FROM bil_bi_pipeline_stg stg1
5498: WHERE EXISTS (SELECT /*+ parallel(tmp) */ 1 FROM bil_bi_opdtl_denlog_tmp tmp
5499: WHERE tmp.lead_id = stg1.lead_id
5500: AND tmp.lead_line_id = stg1.line_id
5501: AND tmp.sales_credit_id = stg1.credit_id

Line 5524: -- DELETE /+parallel(stg) /FROM bil_bi_pipeline_stg stg

5520: END IF;
5521:
5522: IF(l_day > l_prev_year_end) THEN
5523: -- delete most of last year's data */
5524: -- DELETE /*+parallel(stg) */FROM bil_bi_pipeline_stg stg
5525: /* WHERE to_number(to_char(EFFECTIVE_DATE, 'J')) < l_min_date_id;
5526:
5527:
5528: IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN

Line 5548: INSERT /+ append / INTO bil_bi_pipeline_stg stg(

5544: */
5545:
5546: -- if gap bigger than 3 days, delete and insert into staging for current date
5547:
5548: INSERT /*+ append */ INTO bil_bi_pipeline_stg stg(
5549: line_id,
5550: credit_id,
5551: SALES_GROUP_ID,
5552: SALESREP_ID,

Line 5768: bil_bi_pipeline_stg stg,

5764: WHEN TIME.ent_year_id = l_year AND OPP_OPEN_STATUS_FLAG = 'Y'
5765: THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
5766: stg.sales_credit_amount*conversion_rate_s) ELSE NULL END )
5767: FROM
5768: bil_bi_pipeline_stg stg,
5769: fii_time_day time
5770:
5771: WHERE stg.effective_date = TIME.report_date
5772: AND forecast_rollup_flag = 'Y'

Line 5807: DELETE /+ index(stg1,BIL_BI_PIPELINE_STG_U1) / FROM bil_bi_pipeline_stg stg1

5803: END IF ; --INIT/INCR
5804:
5805: ELSE
5806: IF (p_small_gap = FALSE) THEN --gap bigger than 3 days
5807: DELETE /*+ index(stg1,BIL_BI_PIPELINE_STG_U1) */ FROM bil_bi_pipeline_stg stg1
5808: WHERE EXISTS (SELECT 1 FROM bil_bi_opdtl_denlog_tmp tmp
5809: WHERE tmp.lead_id = stg1.lead_id
5810: AND tmp.lead_line_id = stg1.line_id
5811:

Line 5832: DELETE FROM bil_bi_pipeline_stg stg

5828: END IF;
5829:
5830: IF(l_day > l_prev_year_end) THEN
5831: -- delete most of last year's data
5832: DELETE FROM bil_bi_pipeline_stg stg
5833: WHERE to_number(to_char(EFFECTIVE_DATE, 'J')) < l_min_date_id;
5834:
5835:
5836:

Line 5855: INSERT INTO bil_bi_pipeline_stg stg(

5851: */
5852:
5853:
5854:
5855: INSERT INTO bil_bi_pipeline_stg stg(
5856: line_id,
5857: credit_id,
5858: SALES_GROUP_ID,
5859:

Line 6074: bil_bi_pipeline_stg stg,

6070: WHEN TIME.ent_year_id = l_year AND OPP_OPEN_STATUS_FLAG = 'Y'
6071: THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
6072: stg.sales_credit_amount*conversion_rate_s) ELSE NULL END )
6073: FROM
6074: bil_bi_pipeline_stg stg,
6075: fii_time_day time
6076: WHERE stg.effective_date = TIME.report_date
6077:
6078: AND forecast_rollup_flag = 'Y'

Line 6187: INSERT /+ APPEND PARALLEL(stg) / INTO bil_bi_pipeline_stg stg(

6183: END IF;
6184:
6185: --Removed hints for FTS in select -- fix for bug 7595743
6186:
6187: INSERT /*+ APPEND PARALLEL(stg) */ INTO bil_bi_pipeline_stg stg(
6188: SALES_GROUP_ID,
6189: SALESREP_ID,
6190: LEAD_ID,
6191: WIN_PROBABILITY,

Line 6317: SELECT COUNT(1) INTO l_count FROM BIL_BI_PIPELINE_STG;

6313: ,maxlog.decision_date;
6314: commit;
6315:
6316:
6317: SELECT COUNT(1) INTO l_count FROM BIL_BI_PIPELINE_STG;
6318:
6319:
6320: IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
6321: bil_bi_util_collection_pkg.writeLog(

Line 6699: bil_bi_pipeline_stg stg,

6695: null prvyr_open_amt_qtr_s,
6696: null prvyr_open_amt_yr_s
6697:
6698: FROM
6699: bil_bi_pipeline_stg stg,
6700: fii_time_day time
6701: WHERE stg.effective_date = TIME.report_date
6702: AND forecast_rollup_flag = 'Y'
6703: AND TIME.report_date_julian>= p_min_date_id AND TIME.report_date_julian+0<=p_max_date_id