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.BSC_UPDATE_UTIL dependencies on BSC_SYS_PERIODS

Line 3388: -- BSC_SYS_PERIODS_TL to improve performance

3384:
3385: BEGIN
3386: -- x_action = 1 || x_action = NULL
3387: -- Drop Indexes from calendar tables: BSC_DB_CALENDAR, BSC_DB_WEEK_MAPS,
3388: -- BSC_SYS_PERIODS_TL to improve performance
3389: -- Note: Indexes on BSC_DB_CALENDAR and BSC_DB_WEEK_MAPS tables were removed.
3390:
3391: --LOCKING: We are not going to drop indexes anymore. If we remove indexes
3392: -- we cannot load different calendars at the same time

Line 3394: -- IF NOT Drop_Index('BSC_SYS_PERIODS_TL_U1') THEN

3390:
3391: --LOCKING: We are not going to drop indexes anymore. If we remove indexes
3392: -- we cannot load different calendars at the same time
3393: --IF NVL(x_action, 1) = 1 THEN
3394: -- IF NOT Drop_Index('BSC_SYS_PERIODS_TL_U1') THEN
3395: -- RAISE e_unexpected_error;
3396: -- END IF;
3397: --END IF;
3398:

Line 3438: -- in BSC_SYS_PERIODS. In case a fiscal year dont have

3434: END IF;
3435:
3436: -- Verify custom periodicities
3437: -- It check that there are records for all fiscal years
3438: -- in BSC_SYS_PERIODS. In case a fiscal year dont have
3439: -- records, it generate them automatically taking the
3440: -- parameters of the current fiscal year.
3441: -- If there are no records in BSC_SYS_PERIODS for current
3442: -- fiscal year (i.e in year change process)

Line 3441: -- If there are no records in BSC_SYS_PERIODS for current

3437: -- It check that there are records for all fiscal years
3438: -- in BSC_SYS_PERIODS. In case a fiscal year dont have
3439: -- records, it generate them automatically taking the
3440: -- parameters of the current fiscal year.
3441: -- If there are no records in BSC_SYS_PERIODS for current
3442: -- fiscal year (i.e in year change process)
3443: -- then it look for the latest year which have records in
3444: -- BSC_SYS_PERIODS.
3445: -- After that it updates the corresponding column

Line 3444: -- BSC_SYS_PERIODS.

3440: -- parameters of the current fiscal year.
3441: -- If there are no records in BSC_SYS_PERIODS for current
3442: -- fiscal year (i.e in year change process)
3443: -- then it look for the latest year which have records in
3444: -- BSC_SYS_PERIODS.
3445: -- After that it updates the corresponding column
3446: -- in BSC_DB_CALENDAR table
3447:
3448: -- Fix bug#4063282: Need to process custom periodicities in order.

Line 3502: -- Populate BSC_SYS_PERIODS_TL

3498: IF NOT Populate_Bsc_Db_Week_Maps(x_calendar_id) THEN
3499: RAISE e_unexpected_error;
3500: END IF;
3501:
3502: -- Populate BSC_SYS_PERIODS_TL
3503: IF NOT Populate_Bsc_Sys_Periods_Tl(x_calendar_id) THEN
3504: RAISE e_unexpected_error;
3505: END IF;
3506: END IF;

Line 3503: IF NOT Populate_Bsc_Sys_Periods_Tl(x_calendar_id) THEN

3499: RAISE e_unexpected_error;
3500: END IF;
3501:
3502: -- Populate BSC_SYS_PERIODS_TL
3503: IF NOT Populate_Bsc_Sys_Periods_Tl(x_calendar_id) THEN
3504: RAISE e_unexpected_error;
3505: END IF;
3506: END IF;
3507:

Line 3513: -- IF NOT Create_Unique_Index('BSC_SYS_PERIODS_TL',

3509: -- Enable indexes
3510: --LOCKING: We are not going to drop indexes anymore. If we remove indexes
3511: -- we cannot load different calendars at the same time
3512: --IF NVL(x_action, 3) = 3 THEN
3513: -- IF NOT Create_Unique_Index('BSC_SYS_PERIODS_TL',
3514: -- 'BSC_SYS_PERIODS_TL_U1',
3515: -- 'YEAR, PERIODICITY_ID, PERIOD_ID, MONTH, LANGUAGE',
3516: -- BSC_APPS.other_index_tbs_type) THEN
3517: -- RAISE e_unexpected_error;

Line 3514: -- 'BSC_SYS_PERIODS_TL_U1',

3510: --LOCKING: We are not going to drop indexes anymore. If we remove indexes
3511: -- we cannot load different calendars at the same time
3512: --IF NVL(x_action, 3) = 3 THEN
3513: -- IF NOT Create_Unique_Index('BSC_SYS_PERIODS_TL',
3514: -- 'BSC_SYS_PERIODS_TL_U1',
3515: -- 'YEAR, PERIODICITY_ID, PERIOD_ID, MONTH, LANGUAGE',
3516: -- BSC_APPS.other_index_tbs_type) THEN
3517: -- RAISE e_unexpected_error;
3518: -- END IF;

Line 4301: | FUNCTION Populate_Bsc_Sys_Periods_Tl

4297: END Populate_Bsc_Db_Week_Maps;
4298:
4299:
4300: /*===========================================================================+
4301: | FUNCTION Populate_Bsc_Sys_Periods_Tl
4302: +============================================================================*/
4303: FUNCTION Populate_Bsc_Sys_Periods_Tl(
4304: x_calendar_id IN NUMBER
4305: ) RETURN BOOLEAN IS

Line 4303: FUNCTION Populate_Bsc_Sys_Periods_Tl(

4299:
4300: /*===========================================================================+
4301: | FUNCTION Populate_Bsc_Sys_Periods_Tl
4302: +============================================================================*/
4303: FUNCTION Populate_Bsc_Sys_Periods_Tl(
4304: x_calendar_id IN NUMBER
4305: ) RETURN BOOLEAN IS
4306:
4307: h_sql VARCHAR2(32700);

Line 4324: -- Delete all rows from bsc_sys_periods_tl

4320: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
4321: h_num_bind_vars NUMBER;
4322:
4323: BEGIN
4324: -- Delete all rows from bsc_sys_periods_tl
4325: DELETE FROM BSC_SYS_PERIODS_TL
4326: WHERE PERIODICITY_ID IN (
4327: SELECT PERIODICITY_ID
4328: FROM BSC_SYS_PERIODICITIES

Line 4325: DELETE FROM BSC_SYS_PERIODS_TL

4321: h_num_bind_vars NUMBER;
4322:
4323: BEGIN
4324: -- Delete all rows from bsc_sys_periods_tl
4325: DELETE FROM BSC_SYS_PERIODS_TL
4326: WHERE PERIODICITY_ID IN (
4327: SELECT PERIODICITY_ID
4328: FROM BSC_SYS_PERIODICITIES
4329: WHERE CALENDAR_ID = x_calendar_id);

Line 4334: INSERT INTO BSC_SYS_PERIODS_TL (YEAR, PERIODICITY_ID, PERIOD_ID, MONTH,

4330:
4331:
4332: -- A. LABELS FOR PREDEFINED PERIODICITIES
4333: -- Insert the records
4334: INSERT INTO BSC_SYS_PERIODS_TL (YEAR, PERIODICITY_ID, PERIOD_ID, MONTH,
4335: LANGUAGE, SOURCE_LANG, NAME, SHORT_NAME)
4336: SELECT
4337: CA.YEAR,
4338: CA.PERIODICITY_ID,

Line 4478: h_sql := 'INSERT INTO BSC_SYS_PERIODS_TL (

4474: --OPEN c_custom_pers FOR c_custom_pers_sql USING x_calendar_id, 0;
4475: OPEN c_custom_pers(x_calendar_id, 0);
4476: FETCH c_custom_pers INTO h_periodicity_id, h_db_column_name;
4477: WHILE c_custom_pers%FOUND LOOP
4478: h_sql := 'INSERT INTO BSC_SYS_PERIODS_TL (
4479: YEAR,
4480: PERIODICITY_ID,
4481: PERIOD_ID,
4482: MONTH,

Line 4530: x_source => 'BSC_UPDATE_UTIL.Populate_Bsc_Sys_Periods_Tl');

4526: EXCEPTION
4527: WHEN OTHERS THEN
4528: BSC_MESSAGE.Add(
4529: x_message => SQLERRM,
4530: x_source => 'BSC_UPDATE_UTIL.Populate_Bsc_Sys_Periods_Tl');
4531: RETURN FALSE;
4532:
4533: END Populate_Bsc_Sys_Periods_Tl;
4534:

Line 4533: END Populate_Bsc_Sys_Periods_Tl;

4529: x_message => SQLERRM,
4530: x_source => 'BSC_UPDATE_UTIL.Populate_Bsc_Sys_Periods_Tl');
4531: RETURN FALSE;
4532:
4533: END Populate_Bsc_Sys_Periods_Tl;
4534:
4535:
4536: /*===========================================================================+
4537: | PROCEDURE Populate_Calendar_Tables

Line 5077: -- Labels are in BSC_SYS_PERIODS_TL

5073: WHERE indicator = x_indicator;
5074:
5075: IF h_edw_flag = 0 THEN
5076: -- BSC Kpi => BSC Periodicity
5077: -- Labels are in BSC_SYS_PERIODS_TL
5078:
5079: h_sql := ' UPDATE
5080: BSC_KPI_DEFAULTS_TL D
5081: SET

Line 5090: BSC_SYS_PERIODS_TL L

5086: (SELECT
5087: K.PERIODICITY_ID||''-''||L.NAME
5088: FROM
5089: BSC_KPI_PERIODICITIES KP,
5090: BSC_SYS_PERIODS_TL L
5091: WHERE
5092: K.INDICATOR = KP.INDICATOR AND
5093: K.PERIODICITY_ID = KP.PERIODICITY_ID AND
5094: C.FISCAL_YEAR = L.YEAR AND

Line 5462: FROM bsc_db_calendar c, bsc_sys_periods p

5458: h_cursor t_cursor;
5459:
5460: CURSOR c_missing_years (p_calendar_id NUMBER, p_periodicity_id NUMBER) IS
5461: SELECT DISTINCT c.year
5462: FROM bsc_db_calendar c, bsc_sys_periods p
5463: WHERE c.calendar_id = p_calendar_id AND p_periodicity_id = p.periodicity_id (+) AND
5464: c.year = p.year (+) AND p.year IS NULL;
5465:
5466: h_year NUMBER;

Line 5470: FROM bsc_sys_periods

5466: h_year NUMBER;
5467:
5468: CURSOR c_check_fy (p_periodicity_id NUMBER, p_calendar_id NUMBER) IS
5469: SELECT DISTINCT year
5470: FROM bsc_sys_periods
5471: WHERE periodicity_id = p_periodicity_id AND
5472: year = (SELECT fiscal_year
5473: FROM bsc_sys_calendars_b
5474: WHERE calendar_id = p_calendar_id);

Line 5492: from bsc_sys_periods p1, bsc_sys_periods p2

5488: h_sql VARCHAR2(32000);
5489:
5490: CURSOR c_feb_issue (p_periodicity_id NUMBER, p_year NUMBER, p_in1 NUMBER, p_in2 NUMBER) IS
5491: select p2.period_id, p2.start_date - p1.end_date as issue_type
5492: from bsc_sys_periods p1, bsc_sys_periods p2
5493: where p1.periodicity_id = p2.periodicity_id and
5494: p1.year = p2.year and p1.period_id = p2.period_id - 1 and
5495: p1.periodicity_id = p_periodicity_id and p1.year = p_year and
5496: p2.start_date - p1.end_date IN (p_in1, p_in2);

Line 5503: from bsc_sys_periods

5499: h_bad_period NUMBER;
5500:
5501: CURSOR c_fix_overlap_period (p_num1 NUMBER, p_periodicity_id NUMBER, p_year NUMBER, p_num2 NUMBER, p_num3 NUMBER) IS
5502: select period_id, abs(p_num1 - period_id) as distance
5503: from bsc_sys_periods
5504: where periodicity_id = p_periodicity_id and year = p_year and
5505: end_date - start_date > p_num2
5506: order by abs(p_num3 - period_id), period_id;
5507:

Line 5515: -- Check whether the current fical year exists in BSC_SYS_PERIODS

5511: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
5512: h_num_bind_vars NUMBER;
5513:
5514: BEGIN
5515: -- Check whether the current fical year exists in BSC_SYS_PERIODS
5516: -- If it exists, then it will be used as the model year.
5517: --OPEN c_check_fy FOR c_check_fy_sql USING x_periodicity_id, x_calendar_id;
5518: OPEN c_check_fy(x_periodicity_id, x_calendar_id);
5519: FETCH c_check_fy INTO h_model_year;

Line 5534: -- Use the current fiscal year if it exists in BSC_SYS_PERIODS

5530: OPEN c_missing_years(x_calendar_id, x_periodicity_id);
5531: FETCH c_missing_years INTO h_year;
5532: WHILE c_missing_years%FOUND LOOP
5533: -- Get the year to take as a model.
5534: -- Use the current fiscal year if it exists in BSC_SYS_PERIODS
5535: -- Otherwise, use the latest year for which there are records in BSC_SYS_PERIODS
5536: IF h_model_year = 0 THEN
5537: -- The current fiscal year does not exists in BSC_SYS_PERIODS, then
5538: -- we get the latest year.

Line 5535: -- Otherwise, use the latest year for which there are records in BSC_SYS_PERIODS

5531: FETCH c_missing_years INTO h_year;
5532: WHILE c_missing_years%FOUND LOOP
5533: -- Get the year to take as a model.
5534: -- Use the current fiscal year if it exists in BSC_SYS_PERIODS
5535: -- Otherwise, use the latest year for which there are records in BSC_SYS_PERIODS
5536: IF h_model_year = 0 THEN
5537: -- The current fiscal year does not exists in BSC_SYS_PERIODS, then
5538: -- we get the latest year.
5539: /*

Line 5537: -- The current fiscal year does not exists in BSC_SYS_PERIODS, then

5533: -- Get the year to take as a model.
5534: -- Use the current fiscal year if it exists in BSC_SYS_PERIODS
5535: -- Otherwise, use the latest year for which there are records in BSC_SYS_PERIODS
5536: IF h_model_year = 0 THEN
5537: -- The current fiscal year does not exists in BSC_SYS_PERIODS, then
5538: -- we get the latest year.
5539: /*
5540: h_sql := 'SELECT MAX(year)'||
5541: ' FROM bsc_sys_periods'||

Line 5541: ' FROM bsc_sys_periods'||

5537: -- The current fiscal year does not exists in BSC_SYS_PERIODS, then
5538: -- we get the latest year.
5539: /*
5540: h_sql := 'SELECT MAX(year)'||
5541: ' FROM bsc_sys_periods'||
5542: ' WHERE periodicity_id = :1';
5543: OPEN h_cursor FOR h_sql USING x_periodicity_id;
5544: FETCH h_cursor INTO h_model_year;
5545: CLOSE h_cursor;

Line 5549: FROM bsc_sys_periods

5545: CLOSE h_cursor;
5546: */
5547: SELECT MAX(year)
5548: INTO h_model_year
5549: FROM bsc_sys_periods
5550: WHERE periodicity_id = x_periodicity_id;
5551: END IF;
5552:
5553: IF x_custom_code = 1 THEN

Line 5556: h_sql := 'INSERT INTO bsc_sys_periods (

5552:
5553: IF x_custom_code = 1 THEN
5554: -- Based on range of dates --> Use start_date and end_date
5555: /*
5556: h_sql := 'INSERT INTO bsc_sys_periods (
5557: periodicity_id,
5558: year,
5559: period_id,
5560: start_date,

Line 5583: bsc_sys_periods p

5579: p.last_updated_by,
5580: sysdate,
5581: p.last_update_login
5582: FROM
5583: bsc_sys_periods p
5584: WHERE
5585: periodicity_id = :6 AND
5586: year = :7';
5587:

Line 5599: INSERT INTO bsc_sys_periods (

5595: h_bind_vars_values(7) := h_model_year;
5596: h_num_bind_vars := 7;
5597: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
5598: */
5599: INSERT INTO bsc_sys_periods (
5600: periodicity_id,
5601: year,
5602: period_id,
5603: start_date,

Line 5626: bsc_sys_periods p

5622: p.last_updated_by,
5623: sysdate,
5624: p.last_update_login
5625: FROM
5626: bsc_sys_periods p
5627: WHERE
5628: periodicity_id = x_periodicity_id AND
5629: year = h_model_year;
5630:

Line 5686: h_sql := 'update bsc_sys_periods'||

5682: FETCH c_fix_overlap_period INTO h_fix_overlap_period, h_distance;
5683: IF c_fix_overlap_period%FOUND THEN
5684: IF h_fix_overlap_period < h_bad_period THEN
5685: /*
5686: h_sql := 'update bsc_sys_periods'||
5687: ' set'||
5688: ' start_date = DECODE(period_id, :1, start_date, start_date-1),'||
5689: ' end_date = end_date-1'||
5690: ' where'||

Line 5705: update bsc_sys_periods

5701: h_bind_vars_values(5) := h_fix_overlap_period;
5702: h_num_bind_vars := 5;
5703: Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
5704: */
5705: update bsc_sys_periods
5706: set start_date = DECODE(period_id, h_fix_overlap_period, start_date, start_date-1),
5707: end_date = end_date-1
5708: where periodicity_id = x_periodicity_id and
5709: year = h_year and

Line 5715: h_sql := 'update bsc_sys_periods'||

5711: period_id >= h_fix_overlap_period;
5712:
5713: ELSE
5714: /*
5715: h_sql := 'update bsc_sys_periods'||
5716: ' set'||
5717: ' start_date = start_date+1,'||
5718: ' end_date = DECODE(period_id, :1, end_date, end_date+1)'||
5719: ' where'||

Line 5734: update bsc_sys_periods

5730: h_bind_vars_values(5) := h_bad_period;
5731: h_num_bind_vars := 5;
5732: Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
5733: */
5734: update bsc_sys_periods
5735: set start_date = start_date+1,
5736: end_date = DECODE(period_id, h_fix_overlap_period, end_date, end_date+1)
5737: where periodicity_id = x_periodicity_id and
5738: year = h_year and

Line 5750: h_sql := 'update bsc_sys_periods'||

5746:
5747: IF h_issue_type = 2 THEN
5748: --Hole
5749: /*
5750: h_sql := 'update bsc_sys_periods'||
5751: ' set start_date = start_date - 1'||
5752: ' where periodicity_id = :1 and'||
5753: ' year = :2 and period_id = :3';
5754:

Line 5762: update bsc_sys_periods

5758: h_bind_vars_values(3) := h_bad_period;
5759: h_num_bind_vars := 3;
5760: Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
5761: */
5762: update bsc_sys_periods
5763: set start_date = start_date - 1
5764: where periodicity_id = x_periodicity_id and
5765: year = h_year and
5766: period_id = h_bad_period;

Line 5774: h_sql := 'INSERT INTO bsc_sys_periods (

5770:
5771: ELSE
5772: -- Based on other periodicity --> Use start_period and end_period
5773: /*
5774: h_sql := 'INSERT INTO bsc_sys_periods (
5775: periodicity_id,
5776: year,
5777: period_id,
5778: start_date,

Line 5801: bsc_sys_periods p

5797: p.last_updated_by,
5798: sysdate,
5799: p.last_update_login
5800: FROM
5801: bsc_sys_periods p
5802: WHERE
5803: periodicity_id = :2 AND
5804: year = :3';
5805:

Line 5813: INSERT INTO bsc_sys_periods (

5809: h_bind_vars_values(3) := h_model_year;
5810: h_num_bind_vars := 3;
5811: Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
5812: */
5813: INSERT INTO bsc_sys_periods (
5814: periodicity_id,
5815: year,
5816: period_id,
5817: start_date,

Line 5840: bsc_sys_periods p

5836: p.last_updated_by,
5837: sysdate,
5838: p.last_update_login
5839: FROM
5840: bsc_sys_periods p
5841: WHERE
5842: periodicity_id = x_periodicity_id AND
5843: year = h_model_year;
5844:

Line 5861: bsc_sys_periods p

5857: SET '||h_db_column_name||' = (
5858: SELECT
5859: p.period_id
5860: FROM
5861: bsc_sys_periods p
5862: WHERE
5863: p.periodicity_id = :1 AND
5864: p.year = d.year AND
5865: TO_DATE(d.calendar_year||''-''||d.calendar_month||''-''||d.calendar_day, ''YYYY-MM-DD'')

Line 5893: bsc_sys_periods p

5889: SET '||h_db_column_name||' = (
5890: SELECT
5891: p.period_id
5892: FROM
5893: bsc_sys_periods p
5894: WHERE
5895: p.periodicity_id = :1 AND
5896: p.year = d.year AND
5897: d.'||h_db_source_column_name||' BETWEEN p.start_period AND p.end_period