[Home] [Help]
1: package body MSC_SDA_PKG as
2: /* $Header: MSCSDAPB.pls 120.48.12010000.2 2008/06/03 05:39:14 pabram ship $ */
3:
4: --constants
5: c_field_seperator constant varchar2(5) := msc_sda_utils.c_field_seperator;
1530: ---------------------------
1531: cursor sd_snapshot_cur is
1532: select
1533: maq.row_index,
1534: msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag, ms.source_organization_id) row_type,
1535: msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag, ms.source_organization_id) offset,
1536: msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date) new_date,
1537: msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date) old_date,
1538: sum(decode(msi.base_item_id,
1531: cursor sd_snapshot_cur is
1532: select
1533: maq.row_index,
1534: msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag, ms.source_organization_id) row_type,
1535: msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag, ms.source_organization_id) offset,
1536: msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date) new_date,
1537: msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date) old_date,
1538: sum(decode(msi.base_item_id,
1539: null, decode(ms.disposition_status_type,
1532: select
1533: maq.row_index,
1534: msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag, ms.source_organization_id) row_type,
1535: msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag, ms.source_organization_id) offset,
1536: msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date) new_date,
1537: msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date) old_date,
1538: sum(decode(msi.base_item_id,
1539: null, decode(ms.disposition_status_type,
1540: 2, 0,
1533: maq.row_index,
1534: msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag, ms.source_organization_id) row_type,
1535: msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag, ms.source_organization_id) offset,
1536: msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date) new_date,
1537: msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date) old_date,
1538: sum(decode(msi.base_item_id,
1539: null, decode(ms.disposition_status_type,
1540: 2, 0,
1541: decode(ms.last_unit_completion_date,
1574: and mfq3.query_id = g_plan_bkts_query_id
1575: and ( trunc(ms.new_schedule_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
1576: group by
1577: maq.row_index,
1578: msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag, ms.source_organization_id),
1579: msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag, ms.source_organization_id),
1580: msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date),
1581: msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date)
1582: union all
1575: and ( trunc(ms.new_schedule_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
1576: group by
1577: maq.row_index,
1578: msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag, ms.source_organization_id),
1579: msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag, ms.source_organization_id),
1580: msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date),
1581: msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date)
1582: union all
1583: select
1576: group by
1577: maq.row_index,
1578: msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag, ms.source_organization_id),
1579: msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag, ms.source_organization_id),
1580: msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date),
1581: msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date)
1582: union all
1583: select
1584: maq.row_index,
1577: maq.row_index,
1578: msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag, ms.source_organization_id),
1579: msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag, ms.source_organization_id),
1580: msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date),
1581: msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date)
1582: union all
1583: select
1584: maq.row_index,
1585: msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag, md.disposition_id, mio.organization_type) row_type,
1581: msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date)
1582: union all
1583: select
1584: maq.row_index,
1585: msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag, md.disposition_id, mio.organization_type) row_type,
1586: msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag, md.disposition_id, mio.organization_type) offset,
1587: msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date) new_date,
1588: msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date) old_date,
1589: sum(decode(md.assembly_demand_comp_date,
1582: union all
1583: select
1584: maq.row_index,
1585: msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag, md.disposition_id, mio.organization_type) row_type,
1586: msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag, md.disposition_id, mio.organization_type) offset,
1587: msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date) new_date,
1588: msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date) old_date,
1589: sum(decode(md.assembly_demand_comp_date,
1590: null, decode(md.origination_type,
1583: select
1584: maq.row_index,
1585: msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag, md.disposition_id, mio.organization_type) row_type,
1586: msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag, md.disposition_id, mio.organization_type) offset,
1587: msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date) new_date,
1588: msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date) old_date,
1589: sum(decode(md.assembly_demand_comp_date,
1590: null, decode(md.origination_type,
1591: 29,(nvl(md.probability,1)* md.using_requirement_quantity),
1584: maq.row_index,
1585: msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag, md.disposition_id, mio.organization_type) row_type,
1586: msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag, md.disposition_id, mio.organization_type) offset,
1587: msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date) new_date,
1588: msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date) old_date,
1589: sum(decode(md.assembly_demand_comp_date,
1590: null, decode(md.origination_type,
1591: 29,(nvl(md.probability,1)* md.using_requirement_quantity),
1592: 31, 0,
1636: and md.sr_instance_id = mio.sr_instance_id
1637: and md.organization_id = mio.organization_id
1638: group by
1639: maq.row_index,
1640: msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag, md.disposition_id, mio.organization_type),
1641: msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag, md.disposition_id, mio.organization_type),
1642: msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date),
1643: msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date)
1644: union all
1637: and md.organization_id = mio.organization_id
1638: group by
1639: maq.row_index,
1640: msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag, md.disposition_id, mio.organization_type),
1641: msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag, md.disposition_id, mio.organization_type),
1642: msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date),
1643: msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date)
1644: union all
1645: select
1638: group by
1639: maq.row_index,
1640: msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag, md.disposition_id, mio.organization_type),
1641: msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag, md.disposition_id, mio.organization_type),
1642: msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date),
1643: msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date)
1644: union all
1645: select
1646: maq.row_index,
1639: maq.row_index,
1640: msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag, md.disposition_id, mio.organization_type),
1641: msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag, md.disposition_id, mio.organization_type),
1642: msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date),
1643: msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date)
1644: union all
1645: select
1646: maq.row_index,
1647: c_max_level row_type,
1798: cursor fcst_snapshot_cur is
1799: --for region based demands
1800: select
1801: maq.row_index,
1802: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
1803: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) row_type,
1804: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1805: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) offset,
1806: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
1800: select
1801: maq.row_index,
1802: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
1803: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) row_type,
1804: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1805: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) offset,
1806: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
1807: md.using_assembly_demand_date) new_date,
1808: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
1802: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
1803: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) row_type,
1804: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1805: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) offset,
1806: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
1807: md.using_assembly_demand_date) new_date,
1808: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
1809: md.using_assembly_demand_date) old_date,
1810: sum(decode(md.assembly_demand_comp_date,
1804: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1805: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) offset,
1806: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
1807: md.using_assembly_demand_date) new_date,
1808: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
1809: md.using_assembly_demand_date) old_date,
1810: sum(decode(md.assembly_demand_comp_date,
1811: null, decode(md.origination_type,
1812: 29,(nvl(md.probability,1)* md.using_requirement_quantity),
1866: )))
1867: or (nvl(maq.region_id,-1) <> -1 and md.organization_id <> -1) )
1868: group by
1869: maq.row_index,
1870: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
1871: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
1872: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1873: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
1874: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date),
1868: group by
1869: maq.row_index,
1870: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
1871: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
1872: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1873: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
1874: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date),
1875: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date)
1876: union all
1870: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
1871: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
1872: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1873: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
1874: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date),
1875: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date)
1876: union all
1877: --for global based demands
1878: select
1871: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
1872: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1873: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
1874: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date),
1875: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date)
1876: union all
1877: --for global based demands
1878: select
1879: maq.row_index,
1876: union all
1877: --for global based demands
1878: select
1879: maq.row_index,
1880: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
1881: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) row_type,
1882: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1883: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) offset,
1884: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
1878: select
1879: maq.row_index,
1880: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
1881: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) row_type,
1882: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1883: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) offset,
1884: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
1885: md.using_assembly_demand_date) new_date,
1886: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
1880: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
1881: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) row_type,
1882: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1883: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) offset,
1884: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
1885: md.using_assembly_demand_date) new_date,
1886: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
1887: md.using_assembly_demand_date) old_date,
1888: sum(decode(md.assembly_demand_comp_date,
1882: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1883: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) offset,
1884: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
1885: md.using_assembly_demand_date) new_date,
1886: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
1887: md.using_assembly_demand_date) old_date,
1888: sum(decode(md.assembly_demand_comp_date,
1889: null, decode(md.origination_type,
1890: 29,(nvl(md.probability,1)* md.using_requirement_quantity),
1942: and md2.organization_id = -1
1943: and md2.inventory_item_id = md.inventory_item_id)) )
1944: group by
1945: maq.row_index,
1946: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
1947: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
1948: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1949: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
1950: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date),
1944: group by
1945: maq.row_index,
1946: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
1947: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
1948: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1949: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
1950: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date),
1951: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date)
1952: union all
1946: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
1947: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
1948: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1949: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
1950: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date),
1951: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date)
1952: union all
1953: --for local based demands
1954: select
1947: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
1948: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1949: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
1950: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date),
1951: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date)
1952: union all
1953: --for local based demands
1954: select
1955: maq.row_index,
1952: union all
1953: --for local based demands
1954: select
1955: maq.row_index,
1956: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
1957: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) row_type,
1958: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1959: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) offset,
1960: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
1954: select
1955: maq.row_index,
1956: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
1957: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) row_type,
1958: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1959: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) offset,
1960: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
1961: md.using_assembly_demand_date) new_date,
1962: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
1956: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
1957: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) row_type,
1958: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1959: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) offset,
1960: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
1961: md.using_assembly_demand_date) new_date,
1962: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
1963: md.using_assembly_demand_date) old_date,
1964: sum(decode(md.assembly_demand_comp_date,
1958: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1959: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) offset,
1960: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
1961: md.using_assembly_demand_date) new_date,
1962: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
1963: md.using_assembly_demand_date) old_date,
1964: sum(decode(md.assembly_demand_comp_date,
1965: null, decode(md.origination_type,
1966: 29,(nvl(md.probability,1)* md.using_requirement_quantity),
2018: and md2.organization_id = -1
2019: and md2.inventory_item_id = md.inventory_item_id)) )
2020: group by
2021: maq.row_index,
2022: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
2023: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
2024: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
2025: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
2026: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date),
2020: group by
2021: maq.row_index,
2022: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
2023: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
2024: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
2025: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
2026: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date),
2027: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date)
2028: union all
2022: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
2023: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
2024: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
2025: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
2026: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date),
2027: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date)
2028: union all
2029: --for region based demands - consumption
2030: select
2023: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
2024: msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
2025: md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
2026: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date),
2027: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date)
2028: union all
2029: --for region based demands - consumption
2030: select
2031: maq.row_index,
2030: select
2031: maq.row_index,
2032: c_drow2_consm_qty row_type,
2033: c_row2_consumed_fcst offset,
2034: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) new_date,
2035: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) old_date,
2036: sum(mfu.consumed_qty) new_quantity,
2037: sum(mfu.overconsumption_qty) old_quantity
2038: from
2031: maq.row_index,
2032: c_drow2_consm_qty row_type,
2033: c_row2_consumed_fcst offset,
2034: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) new_date,
2035: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) old_date,
2036: sum(mfu.consumed_qty) new_quantity,
2037: sum(mfu.overconsumption_qty) old_quantity
2038: from
2039: msc_demands md,
2081: group by
2082: maq.row_index,
2083: c_drow2_consm_qty,
2084: c_row2_consumed_fcst,
2085: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date),
2086: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date)
2087: union all
2088: --for global based demands - consumption
2089: select
2082: maq.row_index,
2083: c_drow2_consm_qty,
2084: c_row2_consumed_fcst,
2085: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date),
2086: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date)
2087: union all
2088: --for global based demands - consumption
2089: select
2090: maq.row_index,
2089: select
2090: maq.row_index,
2091: c_drow2_consm_qty row_type,
2092: c_row2_consumed_fcst offset,
2093: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) new_date,
2094: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) old_date,
2095: sum(mfu.consumed_qty) new_quantity,
2096: sum(mfu.overconsumption_qty) old_quantity
2097: from
2090: maq.row_index,
2091: c_drow2_consm_qty row_type,
2092: c_row2_consumed_fcst offset,
2093: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) new_date,
2094: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) old_date,
2095: sum(mfu.consumed_qty) new_quantity,
2096: sum(mfu.overconsumption_qty) old_quantity
2097: from
2098: msc_demands md,
2138: group by
2139: maq.row_index,
2140: c_drow2_consm_qty,
2141: c_row2_consumed_fcst,
2142: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date),
2143: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date)
2144: union all
2145: --for local based demands - consumption
2146: select
2139: maq.row_index,
2140: c_drow2_consm_qty,
2141: c_row2_consumed_fcst,
2142: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date),
2143: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date)
2144: union all
2145: --for local based demands - consumption
2146: select
2147: maq.row_index,
2146: select
2147: maq.row_index,
2148: c_drow2_consm_qty row_type,
2149: c_row2_consumed_fcst offset,
2150: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) new_date,
2151: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) old_date,
2152: sum(mfu.consumed_qty) new_quantity,
2153: sum(mfu.overconsumption_qty) old_quantity
2154: from
2147: maq.row_index,
2148: c_drow2_consm_qty row_type,
2149: c_row2_consumed_fcst offset,
2150: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) new_date,
2151: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) old_date,
2152: sum(mfu.consumed_qty) new_quantity,
2153: sum(mfu.overconsumption_qty) old_quantity
2154: from
2155: msc_demands md,
2195: group by
2196: maq.row_index,
2197: c_drow2_consm_qty,
2198: c_row2_consumed_fcst,
2199: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date),
2200: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date)
2201: union all
2202: --supplies
2203: select
2196: maq.row_index,
2197: c_drow2_consm_qty,
2198: c_row2_consumed_fcst,
2199: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date),
2200: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date)
2201: union all
2202: --supplies
2203: select
2204: maq.row_index,
2201: union all
2202: --supplies
2203: select
2204: maq.row_index,
2205: msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag,
2206: c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value) row_type,
2207: msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag,
2208: c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value) offset,
2209: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
2203: select
2204: maq.row_index,
2205: msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag,
2206: c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value) row_type,
2207: msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag,
2208: c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value) offset,
2209: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
2210: ms.new_schedule_date) new_date,
2211: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
2205: msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag,
2206: c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value) row_type,
2207: msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag,
2208: c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value) offset,
2209: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
2210: ms.new_schedule_date) new_date,
2211: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
2212: ms.new_schedule_date) old_date,
2213: sum(decode(msi.base_item_id,
2207: msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag,
2208: c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value) offset,
2209: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
2210: ms.new_schedule_date) new_date,
2211: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
2212: ms.new_schedule_date) old_date,
2213: sum(decode(msi.base_item_id,
2214: null, decode(ms.disposition_status_type,
2215: 2, 0,
2254: c_sup2_rtns_dmd_schd,
2255: c_sup2_rtns_bestfit_fcst)
2256: group by
2257: maq.row_index,
2258: msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag,
2259: c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value),
2260: msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag,
2261: c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value),
2262: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date),
2256: group by
2257: maq.row_index,
2258: msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag,
2259: c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value),
2260: msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag,
2261: c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value),
2262: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date),
2263: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date)
2264: order by 1;
2258: msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag,
2259: c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value),
2260: msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag,
2261: c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value),
2262: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date),
2263: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date)
2264: order by 1;
2265:
2266: cursor hist_min_max_dates is
2259: c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value),
2260: msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag,
2261: c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value),
2262: msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date),
2263: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date)
2264: order by 1;
2265:
2266: cursor hist_min_max_dates is
2267: select min(date1), max(date2)
2288: --ui needs to look at msd views to get this information
2289: cursor hist_snapshot_cur is
2290: select
2291: maq.row_index,
2292: msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_row_type_flag) row_type,
2293: msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_offset_flag) offset,
2294: msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, mmhv.anchor_date) new_date,
2295: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, mmhv.anchor_date) old_date,
2296: sum(mmhv.quantity) new_quantity,
2289: cursor hist_snapshot_cur is
2290: select
2291: maq.row_index,
2292: msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_row_type_flag) row_type,
2293: msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_offset_flag) offset,
2294: msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, mmhv.anchor_date) new_date,
2295: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, mmhv.anchor_date) old_date,
2296: sum(mmhv.quantity) new_quantity,
2297: 0 old_quantity
2290: select
2291: maq.row_index,
2292: msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_row_type_flag) row_type,
2293: msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_offset_flag) offset,
2294: msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, mmhv.anchor_date) new_date,
2295: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, mmhv.anchor_date) old_date,
2296: sum(mmhv.quantity) new_quantity,
2297: 0 old_quantity
2298: from
2291: maq.row_index,
2292: msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_row_type_flag) row_type,
2293: msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_offset_flag) offset,
2294: msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, mmhv.anchor_date) new_date,
2295: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, mmhv.anchor_date) old_date,
2296: sum(mmhv.quantity) new_quantity,
2297: 0 old_quantity
2298: from
2299: msc_msd_history_v mmhv,
2317: and mfq3.query_id = g_hist_cal_query_id
2318: and trunc(mmhv.anchor_date) between mfq3.date1 and mfq3.date2
2319: group by
2320: maq.row_index,
2321: msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_row_type_flag),
2322: msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_offset_flag),
2323: msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, mmhv.anchor_date),
2324: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, mmhv.anchor_date)
2325: order by 1;
2318: and trunc(mmhv.anchor_date) between mfq3.date1 and mfq3.date2
2319: group by
2320: maq.row_index,
2321: msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_row_type_flag),
2322: msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_offset_flag),
2323: msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, mmhv.anchor_date),
2324: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, mmhv.anchor_date)
2325: order by 1;
2326:
2319: group by
2320: maq.row_index,
2321: msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_row_type_flag),
2322: msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_offset_flag),
2323: msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, mmhv.anchor_date),
2324: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, mmhv.anchor_date)
2325: order by 1;
2326:
2327: /*
2320: maq.row_index,
2321: msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_row_type_flag),
2322: msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_offset_flag),
2323: msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, mmhv.anchor_date),
2324: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, mmhv.anchor_date)
2325: order by 1;
2326:
2327: /*
2328: cursor hist_snapshot_cur is
2327: /*
2328: cursor hist_snapshot_cur is
2329: select
2330: maq.row_index,
2331: msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag) row_type,
2332: msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag) offset,
2333: msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, ms.new_schedule_date) new_date,
2334: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, ms.new_schedule_date) old_date,
2335: sum(ms.new_order_quantity) new_quantity,
2328: cursor hist_snapshot_cur is
2329: select
2330: maq.row_index,
2331: msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag) row_type,
2332: msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag) offset,
2333: msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, ms.new_schedule_date) new_date,
2334: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, ms.new_schedule_date) old_date,
2335: sum(ms.new_order_quantity) new_quantity,
2336: 0 old_quantity
2329: select
2330: maq.row_index,
2331: msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag) row_type,
2332: msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag) offset,
2333: msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, ms.new_schedule_date) new_date,
2334: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, ms.new_schedule_date) old_date,
2335: sum(ms.new_order_quantity) new_quantity,
2336: 0 old_quantity
2337: from
2330: maq.row_index,
2331: msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag) row_type,
2332: msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag) offset,
2333: msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, ms.new_schedule_date) new_date,
2334: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, ms.new_schedule_date) old_date,
2335: sum(ms.new_order_quantity) new_quantity,
2336: 0 old_quantity
2337: from
2338: msc_supplies ms,
2358: and trunc(ms.new_schedule_date) between mfq3.date1 and mfq3.date2
2359: and ms.order_type = c_returns_hist
2360: group by
2361: maq.row_index,
2362: msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag),
2363: msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag),
2364: msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, ms.new_schedule_date),
2365: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, ms.new_schedule_date)
2366: union all
2359: and ms.order_type = c_returns_hist
2360: group by
2361: maq.row_index,
2362: msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag),
2363: msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag),
2364: msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, ms.new_schedule_date),
2365: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, ms.new_schedule_date)
2366: union all
2367: select
2360: group by
2361: maq.row_index,
2362: msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag),
2363: msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag),
2364: msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, ms.new_schedule_date),
2365: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, ms.new_schedule_date)
2366: union all
2367: select
2368: maq.row_index,
2361: maq.row_index,
2362: msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag),
2363: msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag),
2364: msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, ms.new_schedule_date),
2365: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, ms.new_schedule_date)
2366: union all
2367: select
2368: maq.row_index,
2369: msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag) row_type,
2365: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, ms.new_schedule_date)
2366: union all
2367: select
2368: maq.row_index,
2369: msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag) row_type,
2370: msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag) offset,
2371: msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, md.using_assembly_demand_date) new_date,
2372: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, md.using_assembly_demand_date) old_date,
2373: sum(md.using_requirement_quantity) new_quantity,
2366: union all
2367: select
2368: maq.row_index,
2369: msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag) row_type,
2370: msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag) offset,
2371: msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, md.using_assembly_demand_date) new_date,
2372: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, md.using_assembly_demand_date) old_date,
2373: sum(md.using_requirement_quantity) new_quantity,
2374: 0 old_quantity
2367: select
2368: maq.row_index,
2369: msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag) row_type,
2370: msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag) offset,
2371: msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, md.using_assembly_demand_date) new_date,
2372: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, md.using_assembly_demand_date) old_date,
2373: sum(md.using_requirement_quantity) new_quantity,
2374: 0 old_quantity
2375: from
2368: maq.row_index,
2369: msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag) row_type,
2370: msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag) offset,
2371: msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, md.using_assembly_demand_date) new_date,
2372: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, md.using_assembly_demand_date) old_date,
2373: sum(md.using_requirement_quantity) new_quantity,
2374: 0 old_quantity
2375: from
2376: msc_demands md,
2396: and trunc(md.using_assembly_demand_date) between mfq3.date1 and mfq3.date2
2397: and md.origination_type = c_dmd_hist
2398: group by
2399: maq.row_index,
2400: msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag),
2401: msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag),
2402: msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, md.using_assembly_demand_date),
2403: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, md.using_assembly_demand_date)
2404: order by 1;
2397: and md.origination_type = c_dmd_hist
2398: group by
2399: maq.row_index,
2400: msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag),
2401: msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag),
2402: msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, md.using_assembly_demand_date),
2403: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, md.using_assembly_demand_date)
2404: order by 1;
2405: */
2398: group by
2399: maq.row_index,
2400: msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag),
2401: msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag),
2402: msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, md.using_assembly_demand_date),
2403: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, md.using_assembly_demand_date)
2404: order by 1;
2405: */
2406: /*
2399: maq.row_index,
2400: msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag),
2401: msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag),
2402: msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, md.using_assembly_demand_date),
2403: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, md.using_assembly_demand_date)
2404: order by 1;
2405: */
2406: /*
2407: QUERY_ID NUMBER1 NUMBER2 NUMBER3
3723: begin
3724: l_query_id := msc_sda_utils.flushSupersessionChain(p_plan, p_item);
3725: return l_query_id;
3726: end populateSupersessionChain;
3727: end MSC_SDA_PKG;