[Home] [Help]
1317:
1318:
1319:
1320: /******************* PROCEDURE *************************************************/
1321: /* The mfg_post_process first populate calendar dates into msd_st_time table with
1322: given instance. This populate_calendar_date procedure only populates working
1323: dates according to the calendar. We split raw entity by inserting all the sub
1324: entities into msd_mfg_forecast talbe and delete the original raw entity from
1325: that table. When we insert sub entities into msd_mfg_forecast table, we join
1322: given instance. This populate_calendar_date procedure only populates working
1323: dates according to the calendar. We split raw entity by inserting all the sub
1324: entities into msd_mfg_forecast talbe and delete the original raw entity from
1325: that table. When we insert sub entities into msd_mfg_forecast table, we join
1326: this table with msd_st_time table and only select days which is in between
1327: forecast_date and rate_end_date of individual raw entity. This INSERT
1328: statement contains 3 SELECT statement according to raw entities bucket_type.
1329: We connect the results with UNION. Only one SELECT statement will be
1330: executed due to different bucket_type condition.
1409: p_temp_start_date DATE;
1410: p_temp_end_date DATE;
1411:
1412: CURSOR c_temp_end_date IS
1413: select month_end_date from msd_st_time
1414: where instance = '-999' and day <= p_temp_end_date
1415: order by day desc;
1416:
1417:
1445:
1446: IF (l_count > 0) THEN
1447: p_str := 'UPDATE msd_mfg_forecast f ' ||
1448: ' SET forecast_date = ' ||
1449: ' nvl((SELECT t.week_end_date FROM msd_st_time t ' ||
1450: ' WHERE f.forecast_date = t.day and t.instance = '||
1451: ''''|| -999 ||'''' ||'), f.forecast_date)
1452: WHERE f.rate_end_date is NULL and
1453: f.bucket_type = ' || '''' || 1 ||''''|| ' and
1470: IF (l_count > 0) THEN
1471: /* Monthly Bucket */
1472: p_str := ' UPDATE msd_mfg_forecast f ' ||
1473: ' SET forecast_date = ' ||
1474: ' nvl((SELECT t.month_end_date FROM msd_st_time t
1475: WHERE f.forecast_date = t.day and t.instance = '||
1476: '''' || -999 ||'''' ||'), f.forecast_date)
1477: WHERE f.rate_end_date is NULL and
1478: f.bucket_type = ' || '''' || 2 ||''''|| ' and
1532: a_sr_user_defined1_pk(i), a_sr_user_defined2_pk(i),
1533: a_sr_demand_class_pk(i),
1534: p_new_refresh_num, p_new_refresh_num, 'I'
1535: FROM
1536: msd_st_time t
1537: WHERE
1538: t.day between a_forecast_date(i) and a_rate_end_date(i) and
1539: t.instance = '-999';
1540:
1592: a_sr_demand_class_pk(i),
1593: p_new_refresh_num, p_new_refresh_num, 'I'
1594: FROM
1595: (select distinct week_start_date, week_end_date
1596: from msd_st_time
1597: where instance = '-999') t
1598: WHERE
1599: t.week_start_date between a_forecast_date(i) and a_rate_end_date(i) or t.week_end_date between a_forecast_date(i) and a_rate_end_date(i);
1600: END IF;
1651: a_sr_demand_class_pk(i),
1652: p_new_refresh_num, p_new_refresh_num, 'I'
1653: FROM
1654: (select distinct month_start_date, month_end_date
1655: from msd_st_time
1656: where instance = '-999') t
1657: WHERE
1658: t.month_start_date between a_forecast_date(i) and a_rate_end_date(i);
1659: END IF;
1678: END MFG_POST_PROCESS;
1679:
1680:
1681: /************************************ PROCEDURE ****************************************/
1682: /* This procedure will populate all the working dates into msd_st_time table.
1683: From msd_sr_mfg_time_v,
1684: In WHERE clause, the condition, bcd.seq_num IS NOT NULL, guaranteed the we
1685: only insert working date into the table, since holiday and weekends will have
1686: NULL for seq_num.
1695:
1696: BEGIN
1697: p_cal_type := 10;
1698:
1699: p_str := 'INSERT INTO msd_st_time(instance, calendar_code,
1700: calendar_type,seq_num, month_start_date,
1701: month_end_date, week_start_date,
1702: week_end_date, day,last_update_date,
1703: last_updated_by, creation_date, created_by,
1770: l_where := 2;
1771: EXECUTE IMMEDIATE l_str INTO l_cal_code USING l_org_id;
1772:
1773: /* DWK Delete existing calendar before populating the current one */
1774: DELETE msd_st_time WHERE instance = '-999';
1775:
1776: /* Populate calendar dates. We will delete all these data
1777: after we finish exploiting entities */
1778: populate_calendar_dates(l_cal_code, l_min_date, l_max_date, l_dblink);