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_OPDTL_F_PKG dependencies on BIL_BI_OPDTL_STG

Line 435: FROM BIL_BI_OPDTL_STG

431: IF (G_Refresh_Flag <> 'Y') THEN
432: -- see if there is anything in stage to resume
433: SELECT count(1)
434: INTO l_count
435: FROM BIL_BI_OPDTL_STG
436: WHERE rownum < 2;
437:
438: IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
439: bil_bi_util_collection_pkg.writeLog(

Line 496: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_OPDTL_STG');

492:
493: ELSE
494: g_end_date := l_failure_date;
495: IF (l_valid_prod='N') THEN
496: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_OPDTL_STG');
497: END IF;
498: RAISE INVALID_SETUP;
499: END IF;
500: commit;

Line 504: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_OPDTL_STG');

500: commit;
501: END IF;
502: END IF;
503: END;
504: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_OPDTL_STG');
505: -----------------------------------------------------------------
506: -- If Truncate flag is 'Y' (Complete), then this program starts from the
507: -- beginning:
508: -- 1. Identify the lead_ids for which the process need to run.

Line 641: --Alter_table('BIL_BI_OPDTL_STG');

637: END IF;
638: END IF; -- IF (g_refresh_flag = 'Y')
639:
640: -- nologging
641: --Alter_table('BIL_BI_OPDTL_STG');
642: IF (g_refresh_flag = 'Y') THEN
643: l_stmt:='alter session set sort_area_size=100000000';
644: execute immediate l_stmt;
645:

Line 663: -- and invalid date ranges in staging table (BIL_BI_OPDTL_STG).

659:
660: -----------------------------------------------------------------
661: -- If all the child process completes successfully then Invoke
662: -- Summary_err_check routine to check for any Invalid currencies
663: -- and invalid date ranges in staging table (BIL_BI_OPDTL_STG).
664: -----------------------------------------------------------------
665: g_phase:= 'Summarization Error Check';
666: IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
667: bil_bi_util_collection_pkg.writeLog(

Line 685: bil_bi_util_collection_pkg.analyze_table('BIL_BI_OPDTL_STG',TRUE, 10, 'GLOBAL');

681: p_log_level => fnd_log.LEVEL_EVENT,
682: p_module => g_pkg || l_proc ,
683: p_msg => g_phase);
684: END IF;
685: bil_bi_util_collection_pkg.analyze_table('BIL_BI_OPDTL_STG',TRUE, 10, 'GLOBAL');
686:
687: IF ((g_load_flag = 'I') OR (g_refresh_flag = 'N')) THEN
688: Insert_Into_Sumry_Incr;
689: ELSE

Line 719: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_OPDTL_STG');

715: );
716: return;
717: ELSE
718: IF (l_valid_prod='N') THEN
719: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_OPDTL_STG');
720: END IF;
721: RAISE INVALID_SETUP;
722: END IF;
723: IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN

Line 784: FROM BIL_BI_OPDTL_STG stg

780: trunc( decode(stg.prim_conversion_rate,-3,
781: to_date('01/01/1999','MM/DD/RRRR'),least(sysdate, stg.EFFECTIVE_DATE))) TXN_DATE,
782: decode(sign(nvl(stg.prim_conversion_rate,-1)),-1,'P') prim_curr_type,
783: decode(sign(nvl(stg.CONVERSION_RATE_S,-1)),-1,'S') sec_curr_type
784: FROM BIL_BI_OPDTL_STG stg
785: WHERE ((stg.PRIM_CONVERSION_RATE < 0 OR stg.PRIM_CONVERSION_RATE is null )
786: OR (g_sec_currency IS NOT NULL AND (stg.CONVERSION_RATE_S < 0 OR stg.CONVERSION_RATE_S is null )))
787: AND effective_date<= add_months(trunc(g_program_start),24);
788:

Line 861: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_OPDTL_STG');

857: p_module => g_pkg || l_proc || ' proc_event',
858: p_msg => 'Program finished normally, truncating staging tables.',
859: p_force_log => TRUE);
860:
861: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_OPDTL_STG');
862: null;
863: ELSIF (g_phase = 'Other') THEN
864: bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_EVENT,
865: p_module => g_pkg || l_proc || ' proc_event',

Line 868: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_OPDTL_STG');

864: bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_EVENT,
865: p_module => g_pkg || l_proc || ' proc_event',
866: p_msg => 'Program finished with unhandled error, truncating staging tables.',
867: p_force_log => TRUE);
868: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_OPDTL_STG');
869: END IF;
870: l_sysdate := SYSDATE;
871: /*
872: Added commit before wrapup and setup procedures are called

Line 1045: SELECT lead_number, effective_date FROM BIL_BI_OPDTL_STG

1041: l_warn VARCHAR2(1);
1042: l_limit_date DATE;
1043:
1044: cursor c_date_range(p_date date) is
1045: SELECT lead_number, effective_date FROM BIL_BI_OPDTL_STG
1046: WHERE effective_date > p_date;
1047:
1048: cursor c_item_prod (p_date date) is
1049: SELECT lead_number FROM BIL_BI_OPDTL_STG

Line 1049: SELECT lead_number FROM BIL_BI_OPDTL_STG

1045: SELECT lead_number, effective_date FROM BIL_BI_OPDTL_STG
1046: WHERE effective_date > p_date;
1047:
1048: cursor c_item_prod (p_date date) is
1049: SELECT lead_number FROM BIL_BI_OPDTL_STG
1050: WHERE effective_date <= p_date
1051: and nvl(product_category_id,-999)=-999;
1052:
1053: l_proc VARCHAR2(100);

Line 1080: UPDATE /+ PARALLEL(stg)/ BIL_BI_OPDTL_STG stg

1076: p_msg => g_phase);
1077: END IF;
1078: IF g_sec_currency IS NOT NULL THEN
1079: IF (g_refresh_flag = 'Y') THEN
1080: UPDATE /*+ PARALLEL(stg)*/ BIL_BI_OPDTL_STG stg
1081: SET (stg.prim_conversion_rate,stg.CONVERSION_RATE_S)
1082: = (select exchange_rate, exchange_rate_s
1083: from BIL_BI_CURRENCY_RATE
1084: where currency_code = stg.txn_currency

Line 1089: UPDATE BIL_BI_OPDTL_STG stg

1085: and exchange_date = stg.effective_date)
1086: WHERE ((prim_conversion_rate < 0) OR prim_conversion_rate IS NULL)
1087: OR ((CONVERSION_RATE_S < 0) OR CONVERSION_RATE_S IS NULL);
1088: ELSE
1089: UPDATE BIL_BI_OPDTL_STG stg
1090: SET (stg.prim_conversion_rate,stg.CONVERSION_RATE_S)
1091: = (select exchange_rate, exchange_rate_s
1092: from BIL_BI_CURRENCY_RATE
1093: where currency_code = stg.txn_currency

Line 1100: UPDATE /+ PARALLEL(stg)/ BIL_BI_OPDTL_STG stg

1096: OR ((CONVERSION_RATE_S < 0) OR CONVERSION_RATE_S IS NULL);
1097: END IF;
1098: ELSE --g_sec_currency is null
1099: IF (g_refresh_flag = 'Y') THEN
1100: UPDATE /*+ PARALLEL(stg)*/ BIL_BI_OPDTL_STG stg
1101: SET stg.prim_conversion_rate =(select exchange_rate from BIL_BI_CURRENCY_RATE
1102: where currency_code = stg.txn_currency
1103: and exchange_date = stg.effective_date)
1104: WHERE ((prim_conversion_rate < 0) OR prim_conversion_rate IS NULL) ;

Line 1106: UPDATE BIL_BI_OPDTL_STG stg

1102: where currency_code = stg.txn_currency
1103: and exchange_date = stg.effective_date)
1104: WHERE ((prim_conversion_rate < 0) OR prim_conversion_rate IS NULL) ;
1105: ELSE
1106: UPDATE BIL_BI_OPDTL_STG stg
1107: SET stg.prim_conversion_rate =
1108: (select exchange_rate from BIL_BI_CURRENCY_RATE
1109: where currency_code = stg.txn_currency
1110: and exchange_date = stg.effective_date)

Line 1127: FROM BIL_BI_OPDTL_STG stg

1123: -- Check missing primary currency rates
1124: IF (g_refresh_flag = 'Y') THEN
1125: SELECT /*+ PARALLEL(stg)*/ count(1)
1126: INTO l_conv_rate_cnt
1127: FROM BIL_BI_OPDTL_STG stg
1128: WHERE ((prim_conversion_rate < 0 OR prim_conversion_rate IS NULL)
1129: OR (g_sec_currency IS NOT NULL AND (CONVERSION_RATE_S < 0 OR CONVERSION_RATE_S IS NULL)))
1130: AND stg.effective_date <= l_limit_date;
1131: ELSE

Line 1134: FROM BIL_BI_OPDTL_STG stg

1130: AND stg.effective_date <= l_limit_date;
1131: ELSE
1132: SELECT count(1)
1133: INTO l_conv_rate_cnt
1134: FROM BIL_BI_OPDTL_STG stg
1135: WHERE ((prim_conversion_rate < 0 OR prim_conversion_rate IS NULL)
1136: OR (g_sec_currency IS NOT NULL AND (CONVERSION_RATE_S < 0 OR CONVERSION_RATE_S IS NULL)))
1137: AND stg.effective_date <= l_limit_date;
1138: END IF;

Line 1179: FROM BIL_BI_OPDTL_STG stg;

1175: INTO l_stg_min_txn_dt,
1176: l_stg_max_txn_dt,
1177: l_stg_min_eff_dt,
1178: l_stg_max_eff_dt
1179: FROM BIL_BI_OPDTL_STG stg;
1180: ELSE
1181: SELECT NVL(MIN(stg.TXN_DATE), G_Start_Date),
1182: NVL(Max(stg.TXN_DATE), G_End_Date),
1183: LEAST(NVL(MIN(stg.Effective_DATE), G_Start_Date), NVL(MIN(stg.close_date), G_Start_Date)),

Line 1189: FROM BIL_BI_OPDTL_STG stg;

1185: INTO l_stg_min_txn_dt,
1186: l_stg_max_txn_dt,
1187: l_stg_min_eff_dt,
1188: l_stg_max_eff_dt
1189: FROM BIL_BI_OPDTL_STG stg;
1190:
1191: END IF;
1192: IF (l_stg_min_txn_dt < l_stg_min_eff_dt) THEN
1193: l_stg_min_dt := l_stg_min_txn_dt;

Line 1287: --Delete BIL_BI_OPDTL_STG WHERE

1283: CLOSE c_item_prod;
1284:
1285: --Delete BIL_BI_OPDTL_F ???? too much cost here
1286:
1287: --Delete BIL_BI_OPDTL_STG WHERE
1288:
1289: --(item_id = -1 and nvl(product_category_id,-1)=-1) or valid_flag = 'F';
1290:
1291: /*

Line 1369: INSERT INTO BIL_BI_OPDTL_STG stg (

1365: p_msg => 'Close date collected is greater or equal to :'
1366: || TO_CHAR(l_start_date,'DD-MON-YYYY HH24:MI:SS') );
1367: END IF;
1368:
1369: INSERT INTO BIL_BI_OPDTL_STG stg (
1370: txn_date
1371: ,effective_date
1372: ,lead_id
1373: ,lead_line_id

Line 1590: INSERT /+ APPEND PARALLEL(stg)/ INTO BIL_BI_OPDTL_STG stg (

1586: p_msg => 'Start and End Dates for which Ids are collected:'||
1587: TO_CHAR(G_Start_Date,'DD-MON-YYYY HH24:MI:SS') ||
1588: ' and G_end_date:'|| TO_CHAR(G_end_date,'DD-MON-YYYY HH24:MI:SS'));
1589: END IF;
1590: INSERT /*+ APPEND PARALLEL(stg)*/ INTO BIL_BI_OPDTL_STG stg (
1591: txn_date
1592: ,effective_date
1593: ,lead_id
1594: ,lead_line_id

Line 1783: -- SELECT /+ index_ffs(stg,BIL_BI_OPDTL_STG_U1) / 1

1779:
1780:
1781: -- DELETE /*+ index_ffs(f,BIL_BI_OPDTL_F_U1) */ FROM BIL_BI_OPDTL_F f
1782: -- WHERE EXISTS(
1783: -- SELECT /*+ index_ffs(stg,BIL_BI_OPDTL_STG_U1) */ 1
1784: -- FROM BIL_BI_OPDTL_STG stg
1785: -- WHERE f.opty_id = stg.lead_id
1786: -- );
1787:

Line 1784: -- FROM BIL_BI_OPDTL_STG stg

1780:
1781: -- DELETE /*+ index_ffs(f,BIL_BI_OPDTL_F_U1) */ FROM BIL_BI_OPDTL_F f
1782: -- WHERE EXISTS(
1783: -- SELECT /*+ index_ffs(stg,BIL_BI_OPDTL_STG_U1) */ 1
1784: -- FROM BIL_BI_OPDTL_STG stg
1785: -- WHERE f.opty_id = stg.lead_id
1786: -- );
1787:
1788: DELETE FROM BIL_BI_OPDTL_F f

Line 1844: FROM BIL_BI_OPDTL_STG stg

1840: ,lead_line_id
1841: ,sales_credit_id
1842: ,to_number(to_char(close_date, 'J')) opty_effective_time_id
1843: ,opty_rank
1844: FROM BIL_BI_OPDTL_STG stg
1845: WHERE (nvl(product_category_id,-999)<> -999)
1846: GROUP BY
1847: lead_id
1848: ,lead_line_id

Line 2042: FROM BIL_BI_OPDTL_STG stg

2038: ,G_program_id
2039: ,sysdate
2040: ,lead_line_id
2041: ,sales_credit_id
2042: FROM BIL_BI_OPDTL_STG stg
2043: WHERE (nvl(product_category_id,-999)<> -999)
2044: AND effective_date <= l_limit_date
2045: GROUP BY
2046: lead_id

Line 2080: p_msg => 'Inserted '|| g_row_num ||' into BIL_BI_OPDTL_F table from BIL_BI_OPDTL_STG');

2076: IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
2077: bil_bi_util_collection_pkg.writeLog(
2078: p_log_level => fnd_log.LEVEL_EVENT,
2079: p_module => g_pkg || l_proc ,
2080: p_msg => 'Inserted '|| g_row_num ||' into BIL_BI_OPDTL_F table from BIL_BI_OPDTL_STG');
2081: END IF;
2082:
2083: IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
2084: bil_bi_util_collection_pkg.writeLog(

Line 2201: FROM BIL_BI_OPDTL_STG stg

2197: ,lead_line_id
2198: ,sales_credit_id
2199: ,to_number(to_char(close_date, 'J'))
2200: ,opty_rank
2201: FROM BIL_BI_OPDTL_STG stg
2202: WHERE ( nvl(product_category_id,-999)<> -999)
2203: GROUP BY
2204: lead_id
2205: ,lead_line_id

Line 2240: p_msg => 'Inserted '||g_row_num||' into BIL_BI_OPDTL_F table from BIL_BI_OPDTL_STG');

2236: IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
2237: bil_bi_util_collection_pkg.writeLog(
2238: p_log_level => fnd_log.LEVEL_EVENT,
2239: p_module => g_pkg || l_proc ,
2240: p_msg => 'Inserted '||g_row_num||' into BIL_BI_OPDTL_F table from BIL_BI_OPDTL_STG');
2241: END IF;
2242: IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
2243: bil_bi_util_collection_pkg.writeLog(
2244: p_log_level => fnd_log.LEVEL_PROCEDURE,

Line 2456: FROM BIL_BI_OPDTL_STG stg

2452: trunc(least(sysdate,effective_date)))) rate_s
2453: FROM (SELECT /*+ parallel(stg) */ DISTINCT
2454: txn_currency ,
2455: effective_date
2456: FROM BIL_BI_OPDTL_STG stg
2457: )
2458: ) rates
2459: ON
2460: ( rates.txn_currency = sumry.currency_code

Line 2487: FROM BIL_BI_OPDTL_STG stg

2483: decode(txn_currency,g_sec_currency,1,fii_currency.get_global_rate_secondary(txn_currency,
2484: trunc(least(sysdate,effective_date)))) rate_s
2485: FROM (SELECT DISTINCT txn_currency,
2486: effective_date
2487: FROM BIL_BI_OPDTL_STG stg
2488: )
2489: ) rates
2490: ON
2491: ( rates.txn_currency = sumry.currency_code

Line 2521: FROM BIL_BI_OPDTL_STG stg

2517: trunc(least(sysdate,effective_date)))) rate
2518: FROM (SELECT /*+ parallel(stg) */ DISTINCT
2519: txn_currency ,
2520: effective_date
2521: FROM BIL_BI_OPDTL_STG stg
2522: )
2523: ) rates
2524: ON
2525: ( rates.txn_currency = sumry.currency_code

Line 2547: FROM BIL_BI_OPDTL_STG stg

2543: decode(txn_currency,g_prim_currency,1,fii_currency.get_global_rate_primary(txn_currency,
2544: trunc(least(sysdate,effective_date)))) rate
2545: FROM (SELECT DISTINCT txn_currency,
2546: effective_date
2547: FROM BIL_BI_OPDTL_STG stg
2548: )
2549: ) rates
2550: ON
2551: ( rates.txn_currency = sumry.currency_code