[Home] [Help]
1083: ' WHERE forecast_designator = nvl(:p_fcst_desg, forecast_designator) ';
1084:
1085: ELSIF p_dest_table = MSD_COMMON_UTILITIES.MFG_FCST_FACT_TABLE THEN
1086:
1087: v_sql_stmt := ' UPDATE msd_mfg_forecast ' ||
1088: ' SET last_refresh_num = ' || p_new_refresh_num ||
1089: ', Action_code = ' || '''D''' ||
1090: ' WHERE Action_code = ''I'' and instance = ' ||
1091: p_instance_id || ' and forecast_designator = ' ||
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
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.
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
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
1420: and rate_end_date. This will reduce the number of
1421: dates populate_calendar_dates will populate */
1422:
1423: SELECT min(forecast_date), max( nvl(rate_end_date, forecast_date)) INTO p_min_date, p_max_date
1424: FROM msd_mfg_forecast
1425: WHERE instance = p_instance AND
1426: created_by_refresh_num = p_new_refresh_num AND
1427: forecast_designator = nvl(p_designator,forecast_designator);
1428:
1433: value in rate_end_date, we have to update forecast_date separately */
1434:
1435: /* First check if there is any row to be updated or not */
1436: select count(*) into l_count
1437: from msd_mfg_forecast
1438: where rate_end_date is null and
1439: bucket_type = '1' and
1440: instance = p_instance and
1441: forecast_designator = nvl(p_designator, forecast_designator) and
1443: rownum < 2;
1444:
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)
1459: END IF;
1460:
1461: /* First check if there is any row to be updated or not */
1462: select count(*) into l_count
1463: from msd_mfg_forecast
1464: where rate_end_date is null and
1465: bucket_type = '2' and
1466: instance = p_instance and
1467: forecast_designator = nvl(p_designator, forecast_designator) and
1468: rownum < 2;
1469:
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)
1487:
1488: /* Find If there is any row needs to be exploded with Daily bucket.
1489: If so, delete those rows from fact table and cache them into arrary */
1490:
1491: DELETE FROM msd_mfg_forecast
1492: WHERE instance = p_instance AND
1493: forecast_designator = nvl(p_designator,forecast_designator) AND
1494: bucket_type = C_DAILY_BUCKET AND
1495: created_by_refresh_num = p_new_refresh_num AND
1513:
1514: /* Bulk INSERT cached rows with explosion, For Daily Bucket */
1515: IF (a_sr_item_pk.exists(1)) THEN
1516: FORALL i IN a_sr_item_pk.FIRST..a_sr_item_pk.LAST
1517: INSERT INTO msd_mfg_forecast(instance, forecast_designator, bucket_type,
1518: forecast_date, rate_end_date, original_quantity,
1519: current_quantity, creation_date, created_by,
1520: last_update_date, last_updated_by, last_update_login,
1521: sr_inv_org_pk, prd_level_id, sr_item_pk, sr_customer_pk,
1546: /* Find If there is any row needs to be exploded with Weekly bucket.
1547: If so, delete those rows from fact table and cache them into arrary */
1548: /* Also, rate_end_date can equal to forecast_date
1549: since we already update it */
1550: DELETE FROM msd_mfg_forecast
1551: WHERE instance = p_instance AND
1552: forecast_designator = nvl(p_designator,forecast_designator) AND
1553: bucket_type = C_WEEKLY_BUCKET AND
1554: created_by_refresh_num = p_new_refresh_num AND
1572:
1573: /* Bulk INSERT cached rows with explosion, For Weekly Bucket */
1574: IF (a_sr_item_pk.exists(1)) THEN
1575: FORALL i IN a_sr_item_pk.FIRST..a_sr_item_pk.LAST
1576: INSERT INTO msd_mfg_forecast(instance, forecast_designator, bucket_type,
1577: forecast_date, rate_end_date, original_quantity,
1578: current_quantity, creation_date, created_by,
1579: last_update_date, last_updated_by, last_update_login,
1580: sr_inv_org_pk, prd_level_id, sr_item_pk, sr_customer_pk,
1605: /* Find If there is any row needs to be exploded with Monthly bucket.
1606: If so, delete those rows from fact table and cache them into arrary */
1607: /* Also, rate_end_date can equal to forecast_date
1608: since we already update it */
1609: DELETE FROM msd_mfg_forecast
1610: WHERE instance = p_instance AND
1611: forecast_designator = nvl(p_designator,forecast_designator) AND
1612: bucket_type = C_MONTHLY_BUCKET AND
1613: created_by_refresh_num = p_new_refresh_num AND
1631:
1632: /* Bulk INSERT cached rows with explosion, For Monthly Bucket */
1633: IF (a_sr_item_pk.exists(1)) THEN
1634: FORALL i IN a_sr_item_pk.FIRST..a_sr_item_pk.LAST
1635: INSERT INTO msd_mfg_forecast(instance, forecast_designator, bucket_type,
1636: forecast_date, rate_end_date, original_quantity,
1637: current_quantity, creation_date, created_by,
1638: last_update_date, last_updated_by, last_update_login,
1639: sr_inv_org_pk, prd_level_id, sr_item_pk, sr_customer_pk,
1659: END IF;
1660:
1661: /* Bug 4729883 - Bring Weekly Manufacturing Forecast as Daily.
1662: For All rows with Bucket Type = 1 (weekly), set Bucket Type to 9 (daily) */
1663: update msd_mfg_forecast
1664: set bucket_type = C_DAILY_BUCKET
1665: WHERE instance = p_instance AND
1666: forecast_designator = nvl(p_designator,forecast_designator) AND
1667: bucket_type = C_WEEKLY_BUCKET AND
1828: END IF;
1829: CLOSE c_instance_info;
1830:
1831: /* Check whether there is anything to explode or not */
1832: SELECT count(*) INTO l_count FROM msd_mfg_forecast
1833: WHERE instance = p_instance AND
1834: forecast_designator = nvl(p_designator,forecast_designator) AND
1835: (bucket_type <> C_DAILY_BUCKET OR
1836: forecast_date <> nvl(rate_end_date, forecast_date)) and