[Home] [Help]
1157: ' WHERE forecast_designator = nvl(:p_fcst_desg, forecast_designator) ';
1158:
1159: ELSIF p_dest_table = MSD_COMMON_UTILITIES.MFG_FCST_FACT_TABLE THEN
1160:
1161: v_sql_stmt := ' UPDATE msd_mfg_forecast ' ||
1162: ' SET last_refresh_num = ' || p_new_refresh_num ||
1163: ', Action_code = ' || '''D''' ||
1164: ' WHERE Action_code = ''I'' and instance = ' ||
1165: p_instance_id || ' and forecast_designator = ' ||
1394: /******************* PROCEDURE *************************************************/
1395: /* The mfg_post_process first populate calendar dates into msd_st_time table with
1396: given instance. This populate_calendar_date procedure only populates working
1397: dates according to the calendar. We split raw entity by inserting all the sub
1398: entities into msd_mfg_forecast talbe and delete the original raw entity from
1399: that table. When we insert sub entities into msd_mfg_forecast table, we join
1400: this table with msd_st_time table and only select days which is in between
1401: forecast_date and rate_end_date of individual raw entity. This INSERT
1402: statement contains 3 SELECT statement according to raw entities bucket_type.
1395: /* The mfg_post_process first populate calendar dates into msd_st_time table with
1396: given instance. This populate_calendar_date procedure only populates working
1397: dates according to the calendar. We split raw entity by inserting all the sub
1398: entities into msd_mfg_forecast talbe and delete the original raw entity from
1399: that table. When we insert sub entities into msd_mfg_forecast table, we join
1400: this table with msd_st_time table and only select days which is in between
1401: forecast_date and rate_end_date of individual raw entity. This INSERT
1402: statement contains 3 SELECT statement according to raw entities bucket_type.
1403: We connect the results with UNION. Only one SELECT statement will be
1494: and rate_end_date. This will reduce the number of
1495: dates populate_calendar_dates will populate */
1496:
1497: SELECT min(forecast_date), max( nvl(rate_end_date, forecast_date)) INTO p_min_date, p_max_date
1498: FROM msd_mfg_forecast
1499: WHERE instance = p_instance AND
1500: created_by_refresh_num = p_new_refresh_num AND
1501: forecast_designator = nvl(p_designator,forecast_designator);
1502:
1507: value in rate_end_date, we have to update forecast_date separately */
1508:
1509: /* First check if there is any row to be updated or not */
1510: select count(*) into l_count
1511: from msd_mfg_forecast
1512: where rate_end_date is null and
1513: bucket_type = '1' and
1514: instance = p_instance and
1515: forecast_designator = nvl(p_designator, forecast_designator) and
1517: rownum < 2;
1518:
1519:
1520: IF (l_count > 0) THEN
1521: p_str := 'UPDATE msd_mfg_forecast f ' ||
1522: ' SET forecast_date = ' ||
1523: ' nvl((SELECT t.week_end_date FROM msd_st_time t ' ||
1524: ' WHERE f.forecast_date = t.day and t.instance = '||
1525: ''''|| -999 ||'''' ||'), f.forecast_date)
1533: END IF;
1534:
1535: /* First check if there is any row to be updated or not */
1536: select count(*) into l_count
1537: from msd_mfg_forecast
1538: where rate_end_date is null and
1539: bucket_type = '2' and
1540: instance = p_instance and
1541: forecast_designator = nvl(p_designator, forecast_designator) and
1542: rownum < 2;
1543:
1544: IF (l_count > 0) THEN
1545: /* Monthly Bucket */
1546: p_str := ' UPDATE msd_mfg_forecast f ' ||
1547: ' SET forecast_date = ' ||
1548: ' nvl((SELECT t.month_end_date FROM msd_st_time t
1549: WHERE f.forecast_date = t.day and t.instance = '||
1550: '''' || -999 ||'''' ||'), f.forecast_date)
1561:
1562: /* Find If there is any row needs to be exploded with Daily bucket.
1563: If so, delete those rows from fact table and cache them into arrary */
1564:
1565: DELETE FROM msd_mfg_forecast
1566: WHERE instance = p_instance AND
1567: forecast_designator = nvl(p_designator,forecast_designator) AND
1568: bucket_type = C_DAILY_BUCKET AND
1569: created_by_refresh_num = p_new_refresh_num AND
1587:
1588: /* Bulk INSERT cached rows with explosion, For Daily Bucket */
1589: IF (a_sr_item_pk.exists(1)) THEN
1590: FORALL i IN a_sr_item_pk.FIRST..a_sr_item_pk.LAST
1591: INSERT INTO msd_mfg_forecast(instance, forecast_designator, bucket_type,
1592: forecast_date, rate_end_date, original_quantity,
1593: current_quantity, creation_date, created_by,
1594: last_update_date, last_updated_by, last_update_login,
1595: sr_inv_org_pk, prd_level_id, sr_item_pk, sr_customer_pk,
1620: /* Find If there is any row needs to be exploded with Weekly bucket.
1621: If so, delete those rows from fact table and cache them into arrary */
1622: /* Also, rate_end_date can equal to forecast_date
1623: since we already update it */
1624: DELETE FROM msd_mfg_forecast
1625: WHERE instance = p_instance AND
1626: forecast_designator = nvl(p_designator,forecast_designator) AND
1627: bucket_type = C_WEEKLY_BUCKET AND
1628: created_by_refresh_num = p_new_refresh_num AND
1646:
1647: /* Bulk INSERT cached rows with explosion, For Weekly Bucket */
1648: IF (a_sr_item_pk.exists(1)) THEN
1649: FORALL i IN a_sr_item_pk.FIRST..a_sr_item_pk.LAST
1650: INSERT INTO msd_mfg_forecast(instance, forecast_designator, bucket_type,
1651: forecast_date, rate_end_date, original_quantity,
1652: current_quantity, creation_date, created_by,
1653: last_update_date, last_updated_by, last_update_login,
1654: sr_inv_org_pk, prd_level_id, sr_item_pk, sr_customer_pk,
1679: /* Find If there is any row needs to be exploded with Monthly bucket.
1680: If so, delete those rows from fact table and cache them into arrary */
1681: /* Also, rate_end_date can equal to forecast_date
1682: since we already update it */
1683: DELETE FROM msd_mfg_forecast
1684: WHERE instance = p_instance AND
1685: forecast_designator = nvl(p_designator,forecast_designator) AND
1686: bucket_type = C_MONTHLY_BUCKET AND
1687: created_by_refresh_num = p_new_refresh_num AND
1705:
1706: /* Bulk INSERT cached rows with explosion, For Monthly Bucket */
1707: IF (a_sr_item_pk.exists(1)) THEN
1708: FORALL i IN a_sr_item_pk.FIRST..a_sr_item_pk.LAST
1709: INSERT INTO msd_mfg_forecast(instance, forecast_designator, bucket_type,
1710: forecast_date, rate_end_date, original_quantity,
1711: current_quantity, creation_date, created_by,
1712: last_update_date, last_updated_by, last_update_login,
1713: sr_inv_org_pk, prd_level_id, sr_item_pk, sr_customer_pk,
1733: END IF;
1734:
1735: /* Bug 4729883 - Bring Weekly Manufacturing Forecast as Daily.
1736: For All rows with Bucket Type = 1 (weekly), set Bucket Type to 9 (daily) */
1737: update msd_mfg_forecast
1738: set bucket_type = C_DAILY_BUCKET
1739: WHERE instance = p_instance AND
1740: forecast_designator = nvl(p_designator,forecast_designator) AND
1741: bucket_type = C_WEEKLY_BUCKET AND
1902: END IF;
1903: CLOSE c_instance_info;
1904:
1905: /* Check whether there is anything to explode or not */
1906: SELECT count(*) INTO l_count FROM msd_mfg_forecast
1907: WHERE instance = p_instance AND
1908: forecast_designator = nvl(p_designator,forecast_designator) AND
1909: (bucket_type <> C_DAILY_BUCKET OR
1910: forecast_date <> nvl(rate_end_date, forecast_date)) and