[Home] [Help]
411: IF (g_mode='INITIAL') THEN
412: bil_bi_util_collection_pkg.Truncate_table('BIL_BI_FST_DTL_F');
413: bil_bi_util_collection_pkg.Truncate_table('BIL_BI_FST_DTL_STG');
414: bil_bi_util_collection_pkg.Truncate_table('BIL_BI_PROCESSED_FST_ID');
415: bil_bi_util_collection_pkg.Truncate_table('BIL_BI_NEW_FST_ID');
416: BIS_COLLECTION_UTILITIES.deleteLogForObject (g_obj_name);
417: g_truncate_flag := TRUE;
418: COMMIT;
419: END IF;
529: SELECT
530: COUNT(*)
531: INTO
532: l_stg_cnt
533: FROM BIL_BI_NEW_FST_ID
534: WHERE ROWNUM < 2;
535:
536: IF (l_stg_cnt > 0) THEN
537: G_Resume_Flag := 'Y';
600: g_start_date,
601: g_end_date,
602: l_temp_collect_mode
603: FROM
604: BIL_BI_NEW_FST_ID;
605:
606: IF SUBSTR(l_temp_collect_mode,1,1) = 'Y' THEN
607: g_truncate_flag := TRUE;
608: ELSE
716: p_msg => g_phase);
717: END IF;
718: -- clean the table once resume is complete and commit
719: bil_bi_util_collection_pkg.truncate_table('BIL_BI_FST_DTL_STG');
720: bil_bi_util_collection_pkg.truncate_table('BIL_BI_NEW_FST_ID');
721: COMMIT;
722: -- end of new code
723: G_Resume_Flag := 'N';
724:
953: p_msg => g_phase);
954: END IF;
955:
956: /* truncate the new fst ids table after a successful collection */
957: bil_bi_util_collection_pkg.Truncate_table('BIL_BI_NEW_FST_ID');
958:
959:
960: COMMIT;
961:
1101: END IF;
1102:
1103:
1104:
1105: INSERT /*+ PARALLEL(nfst) */ INTO BIL_BI_NEW_FST_ID nfst
1106: (
1107: record_id,
1108: forecast_id,
1109: currency_code,
1141: );
1142: END IF;
1143:
1144:
1145: INSERT INTO BIL_BI_NEW_FST_ID
1146: (
1147: record_id,
1148: forecast_id,
1149: currency_code,
1183: IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1184: bil_bi_util_collection_pkg.writeLog(
1185: p_log_level => fnd_log.LEVEL_PROCEDURE,
1186: p_module => g_pkg || l_proc,
1187: p_msg => 'Inserted '||l_number_of_rows||' forecast IDs into BIL_BI_NEW_FST_ID');
1188: END IF;
1189:
1190: bil_bi_util_collection_pkg.analyze_table('BIL_BI_NEW_FST_ID', TRUE, 10, 'GLOBAL');
1191:
1186: p_module => g_pkg || l_proc,
1187: p_msg => 'Inserted '||l_number_of_rows||' forecast IDs into BIL_BI_NEW_FST_ID');
1188: END IF;
1189:
1190: bil_bi_util_collection_pkg.analyze_table('BIL_BI_NEW_FST_ID', TRUE, 10, 'GLOBAL');
1191:
1192: return(l_number_of_rows);
1193:
1194: IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1367: SELECT /*+ PARALLEL(nfi) */
1368: DISTINCT currency_code currency_code,
1369: TRUNC(submission_date) submission_date
1370: FROM
1371: bil_bi_new_fst_id nfi
1372: )
1373: ) rates
1374: ON
1375: (
1606: NULL opp_forecast_amt_p,
1607: NULL opp_forecast_amt_s
1608: FROM
1609: as_internal_forecasts aif,
1610: bil_bi_new_fst_id bnfi,
1611: as_fst_sales_categories asfc,
1612: bil_bi_currency_rate rates
1613: WHERE
1614: aif.forecast_id = bnfi.forecast_id
1657: apwsl.opp_forecast_amount*rates.exchange_rate_s opp_forecast_amt_s
1658: FROM
1659: as_internal_forecasts aif,
1660: as_prod_worksheet_lines apwsl,
1661: bil_bi_new_fst_id bnfi,
1662: bil_bi_currency_rate rates
1663: WHERE
1664: aif.forecast_id = bnfi.forecast_id
1665: AND aif.status_code = 'SUBMITTED'
1782: NVL(apwsl.opp_forecast_amount,0)*rates.exchange_rate_s opp_forecast_amt_s
1783: FROM
1784: as_internal_forecasts aif,
1785: as_prod_worksheet_lines apwsl,
1786: bil_bi_new_fst_id bnfi,
1787: bil_bi_currency_rate rates
1788: WHERE
1789: aif.forecast_id = bnfi.forecast_id
1790: AND aif.status_code = 'SUBMITTED'
3073: INTO g_conv_rate_cnt
3074: FROM bil_bi_currency_rate rates
3075: WHERE ((exchange_rate < 0 OR exchange_rate is NULL)
3076: OR (g_sec_currency IS NOT NULL AND (exchange_rate_s < 0 OR exchange_rate_s is NULL)))
3077: AND exchange_date IN (SELECT DISTINCT TRUNC(submission_date) FROM bil_bi_new_fst_id)
3078: AND rownum < 2;
3079: ELSE
3080: SELECT COUNT(1)
3081: INTO g_conv_rate_cnt
3081: INTO g_conv_rate_cnt
3082: FROM bil_bi_currency_rate
3083: WHERE ((exchange_rate < 0 OR exchange_rate is NULL)
3084: OR (g_sec_currency IS NOT NULL AND (exchange_rate_s < 0 OR exchange_rate_s is NULL)))
3085: AND exchange_date IN (SELECT DISTINCT TRUNC(submission_date) FROM bil_bi_new_fst_id)
3086: AND rownum < 2;
3087: END IF;
3088:
3089: IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
3135: SELECT MIN(nfi.submission_date),
3136: MAX(nfi.submission_date)
3137: INTO l_stg_min,
3138: l_stg_max
3139: FROM bil_bi_new_fst_id nfi;
3140:
3141: IF (l_stg_min IS NOT NULL AND l_stg_max IS NOT NULL) THEN
3142: FII_TIME_API.check_missing_date
3143: (
3178: INTO
3179: l_day_min,
3180: l_day_max
3181: FROM
3182: bil_bi_new_fst_id nfi,
3183: gl_periods glp
3184: WHERE
3185: glp.period_set_name = g_cal
3186: AND glp.period_name = nfi.period_name
3262: FROM
3263: bil_bi_currency_rate rate,
3264: fii_time_day fday
3265: WHERE
3266: rate.exchange_date IN (SELECT DISTINCT TRUNC(submission_date) FROM bil_bi_new_fst_id)
3267: AND rate.exchange_date = fday.report_date
3268: AND ((exchange_rate < 0 OR exchange_rate IS NULL)
3269: OR (g_sec_currency IS NOT NULL AND (exchange_rate_s < 0 OR exchange_rate_s IS NULL)));
3270: