[Home] [Help]
631: update msc_alloc_demands
632: set allocated_quantity = l_bucketed_demands_rec.mem_bd_qty,
633: demand_quantity = l_req_qty,
634: --bug3697365 added timestamp also
635: demand_date = trunc(l_bucketed_demands_rec.mem_bd_date) + MSC_ATP_PVT.G_END_OF_DAY,
636: original_demand_date = trunc(l_req_date) + MSC_ATP_PVT.G_END_OF_DAY,
637: Pf_Display_Flag = l_bucketed_demands_rec.mem_display_flag
638: where parent_demand_id = p_parent_demand_id
639: and inventory_item_id = p_atp_rec.request_item_id
632: set allocated_quantity = l_bucketed_demands_rec.mem_bd_qty,
633: demand_quantity = l_req_qty,
634: --bug3697365 added timestamp also
635: demand_date = trunc(l_bucketed_demands_rec.mem_bd_date) + MSC_ATP_PVT.G_END_OF_DAY,
636: original_demand_date = trunc(l_req_date) + MSC_ATP_PVT.G_END_OF_DAY,
637: Pf_Display_Flag = l_bucketed_demands_rec.mem_display_flag
638: where parent_demand_id = p_parent_demand_id
639: and inventory_item_id = p_atp_rec.request_item_id
640: and plan_id = p_plan_id;
676: update msc_alloc_demands
677: set allocated_quantity = l_bucketed_demands_rec.pf_bd_qty,
678: demand_quantity = l_req_qty,
679: --bug3697365 added timestamp also
680: demand_date = trunc(l_bucketed_demands_rec.pf_bd_date) + MSC_ATP_PVT.G_END_OF_DAY,
681: original_demand_date = trunc(l_req_date) + MSC_ATP_PVT.G_END_OF_DAY,
682: Pf_Display_Flag = l_bucketed_demands_rec.pf_display_flag
683: where parent_demand_id = p_parent_demand_id
684: and inventory_item_id = p_atp_rec.inventory_item_id
677: set allocated_quantity = l_bucketed_demands_rec.pf_bd_qty,
678: demand_quantity = l_req_qty,
679: --bug3697365 added timestamp also
680: demand_date = trunc(l_bucketed_demands_rec.pf_bd_date) + MSC_ATP_PVT.G_END_OF_DAY,
681: original_demand_date = trunc(l_req_date) + MSC_ATP_PVT.G_END_OF_DAY,
682: Pf_Display_Flag = l_bucketed_demands_rec.pf_display_flag
683: where parent_demand_id = p_parent_demand_id
684: and inventory_item_id = p_atp_rec.inventory_item_id
685: and plan_id = p_plan_id;
889:
890: IF l_update_mem_bd = 'Y' THEN
891: update msc_alloc_demands
892: --bug3697365 added timestamp also
893: set demand_date = trunc(l_mem_bd_date) + MSC_ATP_PVT.G_END_OF_DAY,
894: allocated_quantity = l_mem_bd_qty,
895: demand_quantity = p_demand_qty,
896: pf_display_flag = l_mem_display_flag,
897: --bug3697365 added timestamp also
894: allocated_quantity = l_mem_bd_qty,
895: demand_quantity = p_demand_qty,
896: pf_display_flag = l_mem_display_flag,
897: --bug3697365 added timestamp also
898: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY
899: where parent_demand_id = p_parent_demand_id
900: --bug3693892 added trunc
901: and trunc(demand_date) <= p_atf_date
902: and plan_id = p_plan_id;
930:
931: IF l_update_pf_bd = 'Y' THEN
932: update msc_alloc_demands
933: --bug3697365 added timestamp also
934: set demand_date = trunc(l_pf_bd_date) + MSC_ATP_PVT.G_END_OF_DAY,
935: allocated_quantity = l_pf_bd_qty,
936: demand_quantity = p_demand_qty,
937: pf_display_flag = l_pf_display_flag,
938: --bug3697365 added timestamp also
935: allocated_quantity = l_pf_bd_qty,
936: demand_quantity = p_demand_qty,
937: pf_display_flag = l_pf_display_flag,
938: --bug3697365 added timestamp also
939: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY
940: where parent_demand_id = p_parent_demand_id
941: --bug3693892 added trunc
942: and trunc(demand_date) > p_atf_date
943: and plan_id = p_plan_id;
1051:
1052: IF l_update_mem_bd = 'Y' THEN
1053: update msc_alloc_demands
1054: --bug3697365 added timestamp also
1055: set demand_date = trunc(l_mem_bd_date) + MSC_ATP_PVT.G_END_OF_DAY,
1056: allocated_quantity = l_mem_bd_qty,
1057: demand_quantity = p_demand_qty,
1058: pf_display_flag = l_mem_display_flag,
1059: --bug3697365 added timestamp also
1056: allocated_quantity = l_mem_bd_qty,
1057: demand_quantity = p_demand_qty,
1058: pf_display_flag = l_mem_display_flag,
1059: --bug3697365 added timestamp also
1060: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY
1061: where parent_demand_id = p_parent_demand_id
1062: --bug3693892 added trunc
1063: and trunc(demand_date) <= p_atf_date
1064: and plan_id = p_plan_id;
1099:
1100: IF l_update_pf_bd = 'Y' THEN
1101: update msc_alloc_demands
1102: --bug3697365 added timestamp also
1103: set demand_date = trunc(l_pf_bd_date) + MSC_ATP_PVT.G_END_OF_DAY,
1104: allocated_quantity = l_pf_bd_qty,
1105: demand_quantity = p_demand_qty,
1106: pf_display_flag = l_pf_display_flag,
1107: --bug3697365 added timestamp also
1104: allocated_quantity = l_pf_bd_qty,
1105: demand_quantity = p_demand_qty,
1106: pf_display_flag = l_pf_display_flag,
1107: --bug3697365 added timestamp also
1108: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY
1109: where parent_demand_id = p_parent_demand_id
1110: --bug3693892 added trunc
1111: and trunc(demand_date) > p_atf_date
1112: and plan_id = p_plan_id;
1175:
1176: IF (p_old_demand_date < p_atf_date) and (p_new_demand_date <= p_atf_date) THEN
1177: update msc_alloc_demands
1178: --bug3697365 added timestamp also
1179: set demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1180: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY
1181: where parent_demand_id = p_parent_demand_id
1182: --bug3693892 added trunc
1183: and trunc(demand_date) <= p_atf_date
1176: IF (p_old_demand_date < p_atf_date) and (p_new_demand_date <= p_atf_date) THEN
1177: update msc_alloc_demands
1178: --bug3697365 added timestamp also
1179: set demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1180: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY
1181: where parent_demand_id = p_parent_demand_id
1182: --bug3693892 added trunc
1183: and trunc(demand_date) <= p_atf_date
1184: and plan_id = p_plan_id;
1184: and plan_id = p_plan_id;
1185: ELSIF (p_old_demand_date < p_atf_date) and (p_new_demand_date > p_atf_date) THEN
1186: update msc_alloc_demands
1187: --bug3693892 added trunc
1188: set demand_date = trunc(p_atf_date) + MSC_ATP_PVT.G_END_OF_DAY,
1189: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1190: Pf_Display_Flag = null
1191: where parent_demand_id = p_parent_demand_id
1192: --bug3693892 added trunc
1185: ELSIF (p_old_demand_date < p_atf_date) and (p_new_demand_date > p_atf_date) THEN
1186: update msc_alloc_demands
1187: --bug3693892 added trunc
1188: set demand_date = trunc(p_atf_date) + MSC_ATP_PVT.G_END_OF_DAY,
1189: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1190: Pf_Display_Flag = null
1191: where parent_demand_id = p_parent_demand_id
1192: --bug3693892 added trunc
1193: and trunc(demand_date) <= p_atf_date
1194: and plan_id = p_plan_id;
1195:
1196: update msc_alloc_demands
1197: --bug3693892 added trunc
1198: set demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1199: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1200: Pf_Display_Flag = 1
1201: where parent_demand_id = p_parent_demand_id
1202: --bug3693892 added trunc
1195:
1196: update msc_alloc_demands
1197: --bug3693892 added trunc
1198: set demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1199: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1200: Pf_Display_Flag = 1
1201: where parent_demand_id = p_parent_demand_id
1202: --bug3693892 added trunc
1203: and trunc(demand_date) > p_atf_date
1204: and plan_id = p_plan_id;
1205: ELSIF (p_old_demand_date > p_atf_date) and (p_new_demand_date > p_atf_date) THEN
1206: update msc_alloc_demands
1207: --bug3693892 added trunc
1208: set demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1209: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY
1210: where parent_demand_id = p_parent_demand_id
1211: --bug3693892 added trunc
1212: and trunc(demand_date) > p_atf_date
1205: ELSIF (p_old_demand_date > p_atf_date) and (p_new_demand_date > p_atf_date) THEN
1206: update msc_alloc_demands
1207: --bug3693892 added trunc
1208: set demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1209: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY
1210: where parent_demand_id = p_parent_demand_id
1211: --bug3693892 added trunc
1212: and trunc(demand_date) > p_atf_date
1213: and plan_id = p_plan_id;
1288: p_atp_rec.organization_id,
1289: p_atp_rec.instance_id,
1290: nvl(p_atp_rec.demand_class, -1),
1291: --bug3697365 added timestamp also
1292: TRUNC(p_bucketed_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1293: TRUNC(p_atp_rec.requested_ship_date) + MSC_ATP_PVT.G_END_OF_DAY,
1294: p_parent_demand_id,
1295: p_bucketed_demand_qty,
1296: p_atp_rec.quantity_ordered,
1289: p_atp_rec.instance_id,
1290: nvl(p_atp_rec.demand_class, -1),
1291: --bug3697365 added timestamp also
1292: TRUNC(p_bucketed_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1293: TRUNC(p_atp_rec.requested_ship_date) + MSC_ATP_PVT.G_END_OF_DAY,
1294: p_parent_demand_id,
1295: p_bucketed_demand_qty,
1296: p_atp_rec.quantity_ordered,
1297: 51, -- ATP Bucketed Demand
1300: p_atp_rec.order_number),
1301: p_atp_rec.demand_source_line,
1302: p_atp_rec.demand_source_type,--cmro
1303: p_display_flag,
1304: decode(p_atp_rec.origination_type, 6, MSC_ATP_PVT.G_PARTNER_ID,
1305: 30, MSC_ATP_PVT.G_PARTNER_ID,
1306: null),
1307: decode(p_atp_rec.origination_type, 6, MSC_ATP_PVT.G_PARTNER_SITE_ID,
1308: 30, MSC_ATP_PVT.G_PARTNER_SITE_ID,
1301: p_atp_rec.demand_source_line,
1302: p_atp_rec.demand_source_type,--cmro
1303: p_display_flag,
1304: decode(p_atp_rec.origination_type, 6, MSC_ATP_PVT.G_PARTNER_ID,
1305: 30, MSC_ATP_PVT.G_PARTNER_ID,
1306: null),
1307: decode(p_atp_rec.origination_type, 6, MSC_ATP_PVT.G_PARTNER_SITE_ID,
1308: 30, MSC_ATP_PVT.G_PARTNER_SITE_ID,
1309: null),
1303: p_display_flag,
1304: decode(p_atp_rec.origination_type, 6, MSC_ATP_PVT.G_PARTNER_ID,
1305: 30, MSC_ATP_PVT.G_PARTNER_ID,
1306: null),
1307: decode(p_atp_rec.origination_type, 6, MSC_ATP_PVT.G_PARTNER_SITE_ID,
1308: 30, MSC_ATP_PVT.G_PARTNER_SITE_ID,
1309: null),
1310: p_refresh_number,
1311: G_USER_ID,
1304: decode(p_atp_rec.origination_type, 6, MSC_ATP_PVT.G_PARTNER_ID,
1305: 30, MSC_ATP_PVT.G_PARTNER_ID,
1306: null),
1307: decode(p_atp_rec.origination_type, 6, MSC_ATP_PVT.G_PARTNER_SITE_ID,
1308: 30, MSC_ATP_PVT.G_PARTNER_SITE_ID,
1309: null),
1310: p_refresh_number,
1311: G_USER_ID,
1312: l_sysdate,
1466: G_USER_ID,
1467: l_sysdate,
1468: G_USER_ID,
1469: l_sysdate,
1470: NVL(MSC_ATP_PVT.G_PARTNER_ID,-1), -- Bug 3558125
1471: NVL(MSC_ATP_PVT.G_PARTNER_SITE_ID,-1) -- Bug 3558125
1472: );
1473: IF PG_DEBUG in ('Y', 'C') THEN
1474: msc_sch_wb.atp_debug('*********** Rollup Supply **********');
1467: l_sysdate,
1468: G_USER_ID,
1469: l_sysdate,
1470: NVL(MSC_ATP_PVT.G_PARTNER_ID,-1), -- Bug 3558125
1471: NVL(MSC_ATP_PVT.G_PARTNER_SITE_ID,-1) -- Bug 3558125
1472: );
1473: IF PG_DEBUG in ('Y', 'C') THEN
1474: msc_sch_wb.atp_debug('*********** Rollup Supply **********');
1475: msc_sch_wb.atp_debug('* Add rollup supply for member item ');
1517: G_USER_ID,
1518: l_sysdate,
1519: G_USER_ID,
1520: l_sysdate,
1521: NVL(MSC_ATP_PVT.G_PARTNER_ID,-1), -- Bug 3558125
1522: NVL(MSC_ATP_PVT.G_PARTNER_SITE_ID,-1) -- Bug 3558125
1523: );
1524: IF PG_DEBUG in ('Y', 'C') THEN
1525: msc_sch_wb.atp_debug('*********** Rollup Supply **********');
1518: l_sysdate,
1519: G_USER_ID,
1520: l_sysdate,
1521: NVL(MSC_ATP_PVT.G_PARTNER_ID,-1), -- Bug 3558125
1522: NVL(MSC_ATP_PVT.G_PARTNER_SITE_ID,-1) -- Bug 3558125
1523: );
1524: IF PG_DEBUG in ('Y', 'C') THEN
1525: msc_sch_wb.atp_debug('*********** Rollup Supply **********');
1526: msc_sch_wb.atp_debug('* Add rollup supply for family item ');
1709: msc_supplies_s.nextval, p_mem_stealing_quantity, 50, p_identifier,p_demand_source_type, 1,--cmro
1710: p_mem_stealing_quantity, p_mem_item_id, 46,
1711: G_USER_ID, l_sysdate, G_USER_ID, l_sysdate, p_stolen_demand_class, p_ato_model_line_id, p_refresh_number,
1712: --bug3684383
1713: p_order_number,MSC_ATP_PVT.G_PARTNER_ID,MSC_ATP_PVT.G_PARTNER_SITE_ID) -- For summary enhancement
1714: RETURNING parent_transaction_id INTO p_transaction_id; --bug3555084
1715:
1716: l_rows_proc := SQL%ROWCOUNT;
1717:
1734: p_transaction_id, -1 * p_mem_stealing_quantity, 50, p_identifier,p_demand_source_type, 1,---cmro
1735: -1 * p_mem_stealing_quantity, p_mem_item_id, 47,
1736: G_USER_ID, l_sysdate, G_USER_ID, l_sysdate, p_stealing_demand_class, p_ato_model_line_id, p_refresh_number,
1737: --bug3684383
1738: p_order_number,MSC_ATP_PVT.G_PARTNER_ID,MSC_ATP_PVT.G_PARTNER_SITE_ID); -- For summary enhancement
1739:
1740: l_rows_proc := l_rows_proc + SQL%ROWCOUNT;
1741: END IF;
1742:
1761: p_transaction_id, p_pf_stealing_quantity, 50, p_identifier,p_demand_source_type, 1,--cmro
1762: p_pf_stealing_quantity, p_mem_item_id, 46,
1763: G_USER_ID, l_sysdate, G_USER_ID, l_sysdate, p_stolen_demand_class, p_refresh_number, -- For summary enhancement
1764: --bug3684383
1765: p_order_number,MSC_ATP_PVT.G_PARTNER_ID,MSC_ATP_PVT.G_PARTNER_SITE_ID);
1766:
1767: l_rows_proc := l_rows_proc + SQL%ROWCOUNT;
1768:
1769: ELSE --bug3555084 start
1781: msc_supplies_s.nextval, p_pf_stealing_quantity, 50, p_identifier,p_demand_source_type, 1,--cmro
1782: p_pf_stealing_quantity, p_mem_item_id, 46,
1783: G_USER_ID, l_sysdate, G_USER_ID, l_sysdate, p_stolen_demand_class, p_refresh_number, -- For summary enhancement
1784: --bug3684383
1785: p_order_number,MSC_ATP_PVT.G_PARTNER_ID,MSC_ATP_PVT.G_PARTNER_SITE_ID)
1786:
1787: RETURNING parent_transaction_id INTO p_transaction_id;
1788:
1789: l_rows_proc := l_rows_proc + SQL%ROWCOUNT;
1808: p_transaction_id, -1 * p_pf_stealing_quantity, 50, p_identifier,p_demand_source_type, 1,--cmro
1809: -1 * p_pf_stealing_quantity, p_mem_item_id, 47,
1810: G_USER_ID, l_sysdate, G_USER_ID, l_sysdate, p_stealing_demand_class, p_refresh_number, -- For summary enhancement
1811: --bug3684383
1812: p_order_number,MSC_ATP_PVT.G_PARTNER_ID,MSC_ATP_PVT.G_PARTNER_SITE_ID);
1813:
1814: l_rows_proc := l_rows_proc + SQL%ROWCOUNT;
1815: END IF;
1816:
1864:
1865: -- Initializing API return code
1866: x_return_status := FND_API.G_RET_STS_SUCCESS;
1867:
1868: IF MSC_ATP_PVT.G_INV_CTP = 5 THEN
1869: -- ODS atp
1870: IF p_summary_flag = 'Y' THEN
1871: -- summary ODS atp
1872: Get_Mat_Avail_Pf_Ods_Summ(
1999:
2000: -- Initializing API return code
2001: x_return_status := FND_API.G_RET_STS_SUCCESS;
2002:
2003: IF MSC_ATP_PVT.G_INV_CTP = 5 THEN
2004: -- ODS atp
2005: Get_Mat_Avail_Pf_Ods_Dtls(
2006: p_item_id,
2007: p_request_item_id,
2260: AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_SALES_ORDERS,2,2,-1)
2261: AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_INTERNAL_ORDERS,2,8,-1)
2262: AND D.PRIMARY_UOM_QUANTITY > GREATEST(NVL(D.RESERVATION_QUANTITY,0),
2263: D.COMPLETED_QUANTITY)
2264: AND DECODE(MSC_ATP_PVT.G_APPS_VER,3,D.COMPLETED_QUANTITY,0) = 0 -- 2300767
2265: AND (D.SUBINVENTORY IS NULL OR D.SUBINVENTORY IN
2266: (SELECT S.SUB_INVENTORY_CODE
2267: FROM MSC_SUB_INVENTORIES S
2268: WHERE S.ORGANIZATION_ID=D.ORGANIZATION_ID
2832: AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_SALES_ORDERS,2,2,-1)
2833: AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_INTERNAL_ORDERS,2,8,-1)
2834: AND D.PRIMARY_UOM_QUANTITY > GREATEST(NVL(D.RESERVATION_QUANTITY,0),
2835: D.COMPLETED_QUANTITY)
2836: AND DECODE(MSC_ATP_PVT.G_APPS_VER,3,D.COMPLETED_QUANTITY,0) = 0 -- 2300767
2837: AND (D.SUBINVENTORY IS NULL OR D.SUBINVENTORY IN
2838: (SELECT S.SUB_INVENTORY_CODE
2839: FROM MSC_SUB_INVENTORIES S
2840: WHERE S.ORGANIZATION_ID=D.ORGANIZATION_ID
3306: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: p_instance_id: ' || p_instance_id);
3307: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: p_org_id: ' || p_org_id);
3308: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: p_demand_class: ' || p_demand_class);
3309: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: p_atf_date: ' || p_atf_date);
3310: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: G_ALLOCATION_METHOD: ' || MSC_ATP_PVT.G_ALLOCATION_METHOD);
3311: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: G_HIERARCHY_PROFILE: ' || MSC_AATP_PVT.G_HIERARCHY_PROFILE);
3312: END IF;
3313:
3314: -- Initializing API return code
3313:
3314: -- Initializing API return code
3315: x_return_status := FND_API.G_RET_STS_SUCCESS;
3316:
3317: IF MSC_ATP_PVT.G_ALLOCATION_METHOD = 1 THEN
3318: /* Demand priority allocated ATP
3319: Here we always use allocation rule from family inside/outside ATF
3320: */
3321: IF MSC_AATP_PVT.G_HIERARCHY_PROFILE = 1 THEN
3318: /* Demand priority allocated ATP
3319: Here we always use allocation rule from family inside/outside ATF
3320: */
3321: IF MSC_AATP_PVT.G_HIERARCHY_PROFILE = 1 THEN
3322: MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF := 'N';
3323: MSC_ATP_PVT.G_PF_RULE_OUTSIDE_ATF := 'Y';
3324: END IF;
3325: ELSE
3326: /* Rule based allocated ATP
3319: Here we always use allocation rule from family inside/outside ATF
3320: */
3321: IF MSC_AATP_PVT.G_HIERARCHY_PROFILE = 1 THEN
3322: MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF := 'N';
3323: MSC_ATP_PVT.G_PF_RULE_OUTSIDE_ATF := 'Y';
3324: END IF;
3325: ELSE
3326: /* Rule based allocated ATP
3327: Here we support following scenarios:
3350: AND level_id = -1
3351: AND rownum = 1;
3352:
3353: IF l_alloc_percent is not null THEN
3354: MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF := 'Y';
3355: ELSE
3356: MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF := 'N';
3357: END IF;
3358: EXCEPTION
3352:
3353: IF l_alloc_percent is not null THEN
3354: MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF := 'Y';
3355: ELSE
3356: MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF := 'N';
3357: END IF;
3358: EXCEPTION
3359: WHEN NO_DATA_FOUND THEN
3360: IF PG_DEBUG in ('Y', 'C') THEN
3359: WHEN NO_DATA_FOUND THEN
3360: IF PG_DEBUG in ('Y', 'C') THEN
3361: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: ' || 'Alloc Rule not found at member level');
3362: END IF;
3363: MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF := 'N';
3364: END;
3365: ELSE
3366: BEGIN
3367: SELECT allocation_percent
3374: AND level_id <> -1
3375: AND rownum = 1;
3376:
3377: IF l_alloc_percent is not null THEN
3378: MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF := 'Y';
3379: ELSE
3380: MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF := 'N';
3381: END IF;
3382: EXCEPTION
3376:
3377: IF l_alloc_percent is not null THEN
3378: MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF := 'Y';
3379: ELSE
3380: MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF := 'N';
3381: END IF;
3382: EXCEPTION
3383: WHEN NO_DATA_FOUND THEN
3384: IF PG_DEBUG in ('Y', 'C') THEN
3383: WHEN NO_DATA_FOUND THEN
3384: IF PG_DEBUG in ('Y', 'C') THEN
3385: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: ' || 'Alloc Rule not found for member within ATF');
3386: END IF;
3387: MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF := 'N';
3388: END;
3389: END IF;
3390:
3391: IF PG_DEBUG in ('Y', 'C') THEN
3390:
3391: IF PG_DEBUG in ('Y', 'C') THEN
3392: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: ' || 'Always use rule for family item outside ATF');
3393: END IF;
3394: MSC_ATP_PVT.G_PF_RULE_OUTSIDE_ATF := 'Y';
3395: END IF;
3396:
3397: IF PG_DEBUG in ('Y', 'C') THEN
3398: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: ' || 'G_MEM_RULE_WITHIN_ATF = ' || MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF);
3394: MSC_ATP_PVT.G_PF_RULE_OUTSIDE_ATF := 'Y';
3395: END IF;
3396:
3397: IF PG_DEBUG in ('Y', 'C') THEN
3398: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: ' || 'G_MEM_RULE_WITHIN_ATF = ' || MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF);
3399: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: ' || 'G_PF_RULE_OUTSIDE_ATF = ' || MSC_ATP_PVT.G_PF_RULE_OUTSIDE_ATF);
3400: END IF;
3401:
3402: EXCEPTION
3395: END IF;
3396:
3397: IF PG_DEBUG in ('Y', 'C') THEN
3398: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: ' || 'G_MEM_RULE_WITHIN_ATF = ' || MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF);
3399: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: ' || 'G_PF_RULE_OUTSIDE_ATF = ' || MSC_ATP_PVT.G_PF_RULE_OUTSIDE_ATF);
3400: END IF;
3401:
3402: EXCEPTION
3403: WHEN OTHERS THEN
3468: DECODE(p_demand_class, '-1',
3469: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3470: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3471: 1, p_family_id,
3472: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3473: 'Y', AD.Original_Item_Id,
3474: p_family_id)), p_org_id,
3475: p_instance_id, trunc(AD.Demand_Date),
3476: p_level_id, AD.DEMAND_CLASS),
3479: DECODE(p_demand_class, '-1',
3480: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3481: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3482: 1, p_family_id,
3483: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3484: 'Y', AD.Original_Item_Id,
3485: p_family_id)), p_org_id,
3486: p_instance_id, trunc(AD.Demand_Date),
3487: p_level_id, AD.DEMAND_CLASS),
3490: DECODE(p_demand_class, '-1',
3491: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3492: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3493: 1, p_family_id,
3494: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3495: 'Y', AD.Original_Item_Id,
3496: p_family_id)), p_org_id,
3497: p_instance_id, trunc(AD.Demand_Date),
3498: p_level_id, AD.DEMAND_CLASS),
3502: DECODE(p_demand_class, '-1',
3503: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3504: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3505: 1, p_family_id,
3506: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3507: 'Y', AD.Original_Item_Id,
3508: p_family_id)), p_org_id,
3509: p_instance_id, trunc(AD.Demand_Date),
3510: p_level_id, AD.DEMAND_CLASS),
3513: DECODE(p_demand_class, '-1',
3514: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3515: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3516: 1, p_family_id,
3517: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3518: 'Y', AD.Original_Item_Id,
3519: p_family_id)), p_org_id,
3520: p_instance_id, trunc(AD.Demand_Date),
3521: p_level_id, AD.DEMAND_CLASS),
3524: DECODE(p_demand_class, '-1',
3525: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3526: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3527: 1, p_family_id,
3528: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3529: 'Y', AD.Original_Item_Id,
3530: p_family_id)), p_org_id,
3531: p_instance_id, trunc(AD.Demand_Date),
3532: p_level_id, AD.DEMAND_CLASS),
3535: DECODE(p_demand_class, '-1',
3536: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3537: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3538: 1, p_family_id,
3539: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3540: 'Y', AD.Original_Item_Id,
3541: p_family_id)), p_org_id,
3542: p_instance_id, trunc(AD.Demand_Date),
3543: p_level_id, AD.DEMAND_CLASS),
3548: 6, decode(AD.SOURCE_ORGANIZATION_ID,
3549: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3550: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3551: 1, p_family_id,
3552: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3553: 'Y', AD.Original_Item_Id,
3554: p_family_id)),
3555: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3556: p_level_id, NULL),
3556: p_level_id, NULL),
3557: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3558: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3559: 1, p_family_id,
3560: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3561: 'Y', AD.Original_Item_Id,
3562: p_family_id)),
3563: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3564: p_level_id, NULL),
3564: p_level_id, NULL),
3565: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3566: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3567: 1, p_family_id,
3568: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3569: 'Y', AD.Original_Item_Id,
3570: p_family_id)),
3571: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3572: p_level_id, NULL),
3574: 30, decode(AD.SOURCE_ORGANIZATION_ID,
3575: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3576: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3577: 1, p_family_id,
3578: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3579: 'Y', AD.Original_Item_Id,
3580: p_family_id)),
3581: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3582: p_level_id, NULL),
3582: p_level_id, NULL),
3583: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3584: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3585: 1, p_family_id,
3586: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3587: 'Y', AD.Original_Item_Id,
3588: p_family_id)),
3589: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3590: p_level_id, NULL),
3590: p_level_id, NULL),
3591: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3592: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3593: 1, p_family_id,
3594: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3595: 'Y', AD.Original_Item_Id,
3596: p_family_id)),
3597: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3598: p_level_id, NULL),
3600: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3601: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
3602: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3603: 1, p_family_id,
3604: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3605: 'Y', AD.Original_Item_Id,
3606: p_family_id)), p_org_id, p_instance_id,
3607: trunc(AD.Demand_Date),p_level_id, NULL)))),
3608: p_demand_class, 1,
3616: -23453, null,
3617: AD.SOURCE_ORGANIZATION_ID),
3618: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3619: 1, p_family_id,
3620: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3621: 'Y', AD.Original_Item_Id,
3622: p_family_id)),
3623: p_org_id,
3624: p_instance_id,
3630: DECODE(p_demand_class, '-1',
3631: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3632: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3633: 1, p_family_id,
3634: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3635: 'Y', AD.Original_Item_Id,
3636: p_family_id)), p_org_id,
3637: p_instance_id, trunc(AD.Demand_Date),
3638: p_level_id, AD.DEMAND_CLASS),
3641: DECODE(p_demand_class, '-1',
3642: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3643: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3644: 1, p_family_id,
3645: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3646: 'Y', AD.Original_Item_Id,
3647: p_family_id)), p_org_id,
3648: p_instance_id, trunc(AD.Demand_Date),
3649: p_level_id, AD.DEMAND_CLASS),
3652: DECODE(p_demand_class, '-1',
3653: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3654: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3655: 1, p_family_id,
3656: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3657: 'Y', AD.Original_Item_Id,
3658: p_family_id)), p_org_id,
3659: p_instance_id, trunc(AD.Demand_Date),
3660: p_level_id, AD.DEMAND_CLASS),
3665: DECODE(p_demand_class, '-1',
3666: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3667: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3668: 1, p_family_id,
3669: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3670: 'Y', AD.Original_Item_Id,
3671: p_family_id)), p_org_id,
3672: p_instance_id, trunc(AD.Demand_Date),
3673: p_level_id, AD.DEMAND_CLASS),
3676: DECODE(p_demand_class, '-1',
3677: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3678: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3679: 1, p_family_id,
3680: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3681: 'Y', AD.Original_Item_Id,
3682: p_family_id)), p_org_id,
3683: p_instance_id, trunc(AD.Demand_Date),
3684: p_level_id, AD.DEMAND_CLASS),
3687: DECODE(p_demand_class, '-1',
3688: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3689: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3690: 1, p_family_id,
3691: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3692: 'Y', AD.Original_Item_Id,
3693: p_family_id)), p_org_id,
3694: p_instance_id, trunc(AD.Demand_Date),
3695: p_level_id, AD.DEMAND_CLASS),
3699: DECODE(p_demand_class, '-1',
3700: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3701: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3702: 1, p_family_id,
3703: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3704: 'Y', AD.Original_Item_Id,
3705: p_family_id)), p_org_id,
3706: p_instance_id, trunc(AD.Demand_Date),
3707: p_level_id, AD.DEMAND_CLASS),
3712: 6, decode(AD.SOURCE_ORGANIZATION_ID,
3713: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3714: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3715: 1, p_family_id,
3716: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3717: 'Y', AD.Original_Item_Id,
3718: p_family_id)),
3719: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3720: p_level_id, NULL),
3720: p_level_id, NULL),
3721: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3722: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3723: 1, p_family_id,
3724: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3725: 'Y', AD.Original_Item_Id,
3726: p_family_id)),
3727: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3728: p_level_id, NULL),
3728: p_level_id, NULL),
3729: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3730: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3731: 1, p_family_id,
3732: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3733: 'Y', AD.Original_Item_Id,
3734: p_family_id)),
3735: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3736: p_level_id, NULL),
3738: 30, decode(AD.SOURCE_ORGANIZATION_ID,
3739: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3740: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3741: 1, p_family_id,
3742: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3743: 'Y', AD.Original_Item_Id,
3744: p_family_id)),
3745: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3746: p_level_id, NULL),
3746: p_level_id, NULL),
3747: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3748: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3749: 1, p_family_id,
3750: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3751: 'Y', AD.Original_Item_Id,
3752: p_family_id)),
3753: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3754: p_level_id, NULL),
3754: p_level_id, NULL),
3755: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3756: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3757: 1, p_family_id,
3758: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3759: 'Y', AD.Original_Item_Id,
3760: p_family_id)),
3761: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3762: p_level_id, NULL),
3763: p_demand_class),
3764: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
3765: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3766: 1, p_family_id,
3767: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3768: 'Y', AD.Original_Item_Id,
3769: p_family_id)), p_org_id, p_instance_id,
3770: trunc(AD.Demand_Date),p_level_id, NULL)))),
3771: p_demand_class,
3790: null,
3791: null,
3792: Decode(sign(trunc(SA.Supply_Date) - p_atf_date),
3793: 1, p_family_id,
3794: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3795: 'Y', SA.Original_Item_Id,
3796: p_family_id)),
3797: p_org_id,
3798: p_instance_id,
3806: SA.CUSTOMER_ID,
3807: SA.SHIP_TO_SITE_ID,
3808: Decode(sign(trunc(SA.Supply_Date) - p_atf_date),
3809: 1, p_family_id,
3810: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3811: 'Y', SA.Original_Item_Id,
3812: p_family_id)),
3813: p_org_id,
3814: p_instance_id,
3820: /*NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
3821: p_instance_id,
3822: Decode(sign(trunc(SA.Supply_Date) - p_atf_date),
3823: 1, p_family_id,
3824: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3825: 'Y', SA.Original_Item_Id,
3826: p_family_id)),
3827: p_org_id,
3828: null,
3835: /*DECODE(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
3836: p_instance_id,
3837: Decode(sign(trunc(SA.Supply_Date) - p_atf_date),
3838: 1, p_family_id,
3839: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3840: 'Y', SA.Original_Item_Id,
3841: p_family_id)),
3842: p_org_id,
3843: null,
3934: DECODE(p_demand_class, '-1',
3935: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3936: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3937: 1, p_family_id,
3938: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3939: 'Y', AD.Original_Item_Id,
3940: p_family_id)), p_org_id,
3941: p_instance_id, trunc(AD.Demand_Date),
3942: p_level_id, AD.DEMAND_CLASS),
3945: DECODE(p_demand_class, '-1',
3946: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3947: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3948: 1, p_family_id,
3949: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3950: 'Y', AD.Original_Item_Id,
3951: p_family_id)), p_org_id,
3952: p_instance_id, trunc(AD.Demand_Date),
3953: p_level_id, AD.DEMAND_CLASS),
3956: DECODE(p_demand_class, '-1',
3957: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3958: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3959: 1, p_family_id,
3960: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3961: 'Y', AD.Original_Item_Id,
3962: p_family_id)), p_org_id,
3963: p_instance_id, trunc(AD.Demand_Date),
3964: p_level_id, AD.DEMAND_CLASS),
3968: DECODE(p_demand_class, '-1',
3969: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3970: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3971: 1, p_family_id,
3972: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3973: 'Y', AD.Original_Item_Id,
3974: p_family_id)), p_org_id,
3975: p_instance_id, trunc(AD.Demand_Date),
3976: p_level_id, AD.DEMAND_CLASS),
3979: DECODE(p_demand_class, '-1',
3980: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3981: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3982: 1, p_family_id,
3983: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3984: 'Y', AD.Original_Item_Id,
3985: p_family_id)), p_org_id,
3986: p_instance_id, trunc(AD.Demand_Date),
3987: p_level_id, AD.DEMAND_CLASS),
3990: DECODE(p_demand_class, '-1',
3991: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3992: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3993: 1, p_family_id,
3994: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3995: 'Y', AD.Original_Item_Id,
3996: p_family_id)), p_org_id,
3997: p_instance_id, trunc(AD.Demand_Date),
3998: p_level_id, AD.DEMAND_CLASS),
4001: DECODE(p_demand_class, '-1',
4002: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4003: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4004: 1, p_family_id,
4005: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4006: 'Y', AD.Original_Item_Id,
4007: p_family_id)), p_org_id,
4008: p_instance_id, trunc(AD.Demand_Date),
4009: p_level_id, AD.DEMAND_CLASS),
4014: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4015: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4016: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4017: 1, p_family_id,
4018: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4019: 'Y', AD.Original_Item_Id,
4020: p_family_id)),
4021: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4022: p_level_id, NULL),
4022: p_level_id, NULL),
4023: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4024: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4025: 1, p_family_id,
4026: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4027: 'Y', AD.Original_Item_Id,
4028: p_family_id)),
4029: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4030: p_level_id, NULL),
4030: p_level_id, NULL),
4031: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4032: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4033: 1, p_family_id,
4034: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4035: 'Y', AD.Original_Item_Id,
4036: p_family_id)),
4037: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4038: p_level_id, NULL),
4040: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4041: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4042: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4043: 1, p_family_id,
4044: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4045: 'Y', AD.Original_Item_Id,
4046: p_family_id)),
4047: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4048: p_level_id, NULL),
4048: p_level_id, NULL),
4049: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4050: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4051: 1, p_family_id,
4052: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4053: 'Y', AD.Original_Item_Id,
4054: p_family_id)),
4055: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4056: p_level_id, NULL),
4056: p_level_id, NULL),
4057: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4058: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4059: 1, p_family_id,
4060: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4061: 'Y', AD.Original_Item_Id,
4062: p_family_id)),
4063: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4064: p_level_id, NULL),
4066: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4067: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
4068: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4069: 1, p_family_id,
4070: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4071: 'Y', AD.Original_Item_Id,
4072: p_family_id)), p_org_id, p_instance_id,
4073: trunc(AD.Demand_Date),p_level_id, NULL)))),
4074: p_demand_class, 1,
4081: -23453, null,
4082: AD.SOURCE_ORGANIZATION_ID),
4083: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4084: 1, p_family_id,
4085: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4086: 'Y', AD.Original_Item_Id,
4087: p_family_id)),
4088: p_org_id,
4089: p_instance_id,
4095: DECODE(p_demand_class, '-1',
4096: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4097: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4098: 1, p_family_id,
4099: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4100: 'Y', AD.Original_Item_Id,
4101: p_family_id)), p_org_id,
4102: p_instance_id, trunc(AD.Demand_Date),
4103: p_level_id, AD.DEMAND_CLASS),
4106: DECODE(p_demand_class, '-1',
4107: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4108: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4109: 1, p_family_id,
4110: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4111: 'Y', AD.Original_Item_Id,
4112: p_family_id)), p_org_id,
4113: p_instance_id, trunc(AD.Demand_Date),
4114: p_level_id, AD.DEMAND_CLASS),
4117: DECODE(p_demand_class, '-1',
4118: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4119: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4120: 1, p_family_id,
4121: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4122: 'Y', AD.Original_Item_Id,
4123: p_family_id)), p_org_id,
4124: p_instance_id, trunc(AD.Demand_Date),
4125: p_level_id, AD.DEMAND_CLASS),
4130: DECODE(p_demand_class, '-1',
4131: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4132: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4133: 1, p_family_id,
4134: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4135: 'Y', AD.Original_Item_Id,
4136: p_family_id)), p_org_id,
4137: p_instance_id, trunc(AD.Demand_Date),
4138: p_level_id, AD.DEMAND_CLASS),
4141: DECODE(p_demand_class, '-1',
4142: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4143: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4144: 1, p_family_id,
4145: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4146: 'Y', AD.Original_Item_Id,
4147: p_family_id)), p_org_id,
4148: p_instance_id, trunc(AD.Demand_Date),
4149: p_level_id, AD.DEMAND_CLASS),
4152: DECODE(p_demand_class, '-1',
4153: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4154: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4155: 1, p_family_id,
4156: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4157: 'Y', AD.Original_Item_Id,
4158: p_family_id)), p_org_id,
4159: p_instance_id, trunc(AD.Demand_Date),
4160: p_level_id, AD.DEMAND_CLASS),
4164: DECODE(p_demand_class, '-1',
4165: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4166: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4167: 1, p_family_id,
4168: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4169: 'Y', AD.Original_Item_Id,
4170: p_family_id)), p_org_id,
4171: p_instance_id, trunc(AD.Demand_Date),
4172: p_level_id, AD.DEMAND_CLASS),
4177: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4178: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4179: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4180: 1, p_family_id,
4181: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4182: 'Y', AD.Original_Item_Id,
4183: p_family_id)),
4184: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4185: p_level_id, NULL),
4185: p_level_id, NULL),
4186: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4187: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4188: 1, p_family_id,
4189: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4190: 'Y', AD.Original_Item_Id,
4191: p_family_id)),
4192: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4193: p_level_id, NULL),
4193: p_level_id, NULL),
4194: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4195: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4196: 1, p_family_id,
4197: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4198: 'Y', AD.Original_Item_Id,
4199: p_family_id)),
4200: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4201: p_level_id, NULL),
4203: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4204: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4205: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4206: 1, p_family_id,
4207: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4208: 'Y', AD.Original_Item_Id,
4209: p_family_id)),
4210: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4211: p_level_id, NULL),
4211: p_level_id, NULL),
4212: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4213: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4214: 1, p_family_id,
4215: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4216: 'Y', AD.Original_Item_Id,
4217: p_family_id)),
4218: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4219: p_level_id, NULL),
4219: p_level_id, NULL),
4220: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4221: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4222: 1, p_family_id,
4223: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4224: 'Y', AD.Original_Item_Id,
4225: p_family_id)),
4226: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4227: p_level_id, NULL),
4229: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4230: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
4231: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4232: 1, p_family_id,
4233: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4234: 'Y', AD.Original_Item_Id,
4235: p_family_id)), p_org_id, p_instance_id,
4236: trunc(AD.Demand_Date),p_level_id, NULL)))),
4237: p_demand_class,
4257: null,
4258: null,
4259: Decode(sign(trunc(SA.Supply_Date) - p_atf_date),
4260: 1, p_family_id,
4261: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4262: 'Y', SA.Original_Item_Id,
4263: p_family_id)),
4264: p_org_id,
4265: p_instance_id,
4273: sa.CUSTOMER_ID,
4274: sa.SHIP_TO_SITE_ID,
4275: Decode(sign(trunc(sa.Supply_Date) - p_atf_date),
4276: 1, p_family_id,
4277: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4278: 'Y', sa.Original_Item_Id,
4279: p_family_id)),
4280: p_org_id,
4281: p_instance_id,
4287: /*NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
4288: p_instance_id,
4289: Decode(sign(trunc(sa.Supply_Date) - p_atf_date),
4290: 1, p_family_id,
4291: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4292: 'Y', sa.Original_Item_Id,
4293: p_family_id)),
4294: p_org_id,
4295: null,
4302: /*DECODE(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
4303: p_instance_id,
4304: Decode(sign(trunc(sa.Supply_Date) - p_atf_date),
4305: 1, p_family_id,
4306: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4307: 'Y', sa.Original_Item_Id,
4308: p_family_id)),
4309: p_org_id,
4310: null,
4485: DECODE(p_demand_class, '-1',
4486: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4487: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4488: 1, p_family_id,
4489: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4490: 'Y', AD.Original_Item_Id,
4491: p_family_id)), p_org_id,
4492: p_instance_id, trunc(AD.Demand_Date),
4493: p_level_id, AD.DEMAND_CLASS),
4496: DECODE(p_demand_class, '-1',
4497: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4498: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4499: 1, p_family_id,
4500: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4501: 'Y', AD.Original_Item_Id,
4502: p_family_id)), p_org_id,
4503: p_instance_id, trunc(AD.Demand_Date),
4504: p_level_id, AD.DEMAND_CLASS),
4507: DECODE(p_demand_class, '-1',
4508: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4509: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4510: 1, p_family_id,
4511: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4512: 'Y', AD.Original_Item_Id,
4513: p_family_id)), p_org_id,
4514: p_instance_id, trunc(AD.Demand_Date),
4515: p_level_id, AD.DEMAND_CLASS),
4519: DECODE(p_demand_class, '-1',
4520: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4521: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4522: 1, p_family_id,
4523: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4524: 'Y', AD.Original_Item_Id,
4525: p_family_id)), p_org_id,
4526: p_instance_id, trunc(AD.Demand_Date),
4527: p_level_id, AD.DEMAND_CLASS),
4530: DECODE(p_demand_class, '-1',
4531: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4532: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4533: 1, p_family_id,
4534: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4535: 'Y', AD.Original_Item_Id,
4536: p_family_id)), p_org_id,
4537: p_instance_id, trunc(AD.Demand_Date),
4538: p_level_id, AD.DEMAND_CLASS),
4541: DECODE(p_demand_class, '-1',
4542: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4543: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4544: 1, p_family_id,
4545: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4546: 'Y', AD.Original_Item_Id,
4547: p_family_id)), p_org_id,
4548: p_instance_id, trunc(AD.Demand_Date),
4549: p_level_id, AD.DEMAND_CLASS),
4552: DECODE(p_demand_class, '-1',
4553: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4554: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4555: 1, p_family_id,
4556: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4557: 'Y', AD.Original_Item_Id,
4558: p_family_id)), p_org_id,
4559: p_instance_id, trunc(AD.Demand_Date),
4560: p_level_id, AD.DEMAND_CLASS),
4565: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4566: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4567: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4568: 1, p_family_id,
4569: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4570: 'Y', AD.Original_Item_Id,
4571: p_family_id)),
4572: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4573: p_level_id, NULL),
4573: p_level_id, NULL),
4574: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4575: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4576: 1, p_family_id,
4577: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4578: 'Y', AD.Original_Item_Id,
4579: p_family_id)),
4580: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4581: p_level_id, NULL),
4581: p_level_id, NULL),
4582: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4583: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4584: 1, p_family_id,
4585: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4586: 'Y', AD.Original_Item_Id,
4587: p_family_id)),
4588: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4589: p_level_id, NULL),
4591: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4592: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4593: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4594: 1, p_family_id,
4595: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4596: 'Y', AD.Original_Item_Id,
4597: p_family_id)),
4598: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4599: p_level_id, NULL),
4599: p_level_id, NULL),
4600: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4601: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4602: 1, p_family_id,
4603: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4604: 'Y', AD.Original_Item_Id,
4605: p_family_id)),
4606: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4607: p_level_id, NULL),
4607: p_level_id, NULL),
4608: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4609: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4610: 1, p_family_id,
4611: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4612: 'Y', AD.Original_Item_Id,
4613: p_family_id)),
4614: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4615: p_level_id, NULL),
4617: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4618: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
4619: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4620: 1, p_family_id,
4621: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4622: 'Y', AD.Original_Item_Id,
4623: p_family_id)), p_org_id, p_instance_id,
4624: trunc(AD.Demand_Date),p_level_id, NULL)))),
4625: p_demand_class, 1,
4633: -23453, null,
4634: AD.SOURCE_ORGANIZATION_ID),
4635: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4636: 1, p_family_id,
4637: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4638: 'Y', AD.Original_Item_Id,
4639: p_family_id)),
4640: p_org_id,
4641: p_instance_id,
4647: DECODE(p_demand_class, '-1',
4648: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4649: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4650: 1, p_family_id,
4651: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4652: 'Y', AD.Original_Item_Id,
4653: p_family_id)), p_org_id,
4654: p_instance_id, trunc(AD.Demand_Date),
4655: p_level_id, AD.DEMAND_CLASS),
4658: DECODE(p_demand_class, '-1',
4659: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4660: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4661: 1, p_family_id,
4662: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4663: 'Y', AD.Original_Item_Id,
4664: p_family_id)), p_org_id,
4665: p_instance_id, trunc(AD.Demand_Date),
4666: p_level_id, AD.DEMAND_CLASS),
4669: DECODE(p_demand_class, '-1',
4670: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4671: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4672: 1, p_family_id,
4673: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4674: 'Y', AD.Original_Item_Id,
4675: p_family_id)), p_org_id,
4676: p_instance_id, trunc(AD.Demand_Date),
4677: p_level_id, AD.DEMAND_CLASS),
4682: DECODE(p_demand_class, '-1',
4683: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4684: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4685: 1, p_family_id,
4686: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4687: 'Y', AD.Original_Item_Id,
4688: p_family_id)), p_org_id,
4689: p_instance_id, trunc(AD.Demand_Date),
4690: p_level_id, AD.DEMAND_CLASS),
4693: DECODE(p_demand_class, '-1',
4694: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4695: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4696: 1, p_family_id,
4697: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4698: 'Y', AD.Original_Item_Id,
4699: p_family_id)), p_org_id,
4700: p_instance_id, trunc(AD.Demand_Date),
4701: p_level_id, AD.DEMAND_CLASS),
4704: DECODE(p_demand_class, '-1',
4705: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4706: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4707: 1, p_family_id,
4708: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4709: 'Y', AD.Original_Item_Id,
4710: p_family_id)), p_org_id,
4711: p_instance_id, trunc(AD.Demand_Date),
4712: p_level_id, AD.DEMAND_CLASS),
4716: DECODE(p_demand_class, '-1',
4717: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4718: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4719: 1, p_family_id,
4720: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4721: 'Y', AD.Original_Item_Id,
4722: p_family_id)), p_org_id,
4723: p_instance_id, trunc(AD.Demand_Date),
4724: p_level_id, AD.DEMAND_CLASS),
4729: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4730: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4731: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4732: 1, p_family_id,
4733: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4734: 'Y', AD.Original_Item_Id,
4735: p_family_id)),
4736: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4737: p_level_id, NULL),
4737: p_level_id, NULL),
4738: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4739: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4740: 1, p_family_id,
4741: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4742: 'Y', AD.Original_Item_Id,
4743: p_family_id)),
4744: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4745: p_level_id, NULL),
4745: p_level_id, NULL),
4746: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4747: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4748: 1, p_family_id,
4749: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4750: 'Y', AD.Original_Item_Id,
4751: p_family_id)),
4752: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4753: p_level_id, NULL),
4755: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4756: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4757: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4758: 1, p_family_id,
4759: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4760: 'Y', AD.Original_Item_Id,
4761: p_family_id)),
4762: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4763: p_level_id, NULL),
4763: p_level_id, NULL),
4764: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4765: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4766: 1, p_family_id,
4767: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4768: 'Y', AD.Original_Item_Id,
4769: p_family_id)),
4770: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4771: p_level_id, NULL),
4771: p_level_id, NULL),
4772: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4773: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4774: 1, p_family_id,
4775: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4776: 'Y', AD.Original_Item_Id,
4777: p_family_id)),
4778: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4779: p_level_id, NULL),
4781: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4782: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
4783: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4784: 1, p_family_id,
4785: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4786: 'Y', AD.Original_Item_Id,
4787: p_family_id)), p_org_id, p_instance_id,
4788: trunc(AD.Demand_Date),p_level_id, NULL)))),
4789: p_demand_class,
4810: DECODE(p_demand_class, '-1',
4811: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4812: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4813: 1, p_family_id,
4814: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4815: 'Y', AD.Original_Item_Id,
4816: p_family_id)), p_org_id,
4817: p_instance_id, trunc(AD.Demand_Date),
4818: p_level_id, AD.DEMAND_CLASS),
4821: DECODE(p_demand_class, '-1',
4822: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4823: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4824: 1, p_family_id,
4825: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4826: 'Y', AD.Original_Item_Id,
4827: p_family_id)), p_org_id,
4828: p_instance_id, trunc(AD.Demand_Date),
4829: p_level_id, AD.DEMAND_CLASS),
4832: DECODE(p_demand_class, '-1',
4833: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4834: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4835: 1, p_family_id,
4836: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4837: 'Y', AD.Original_Item_Id,
4838: p_family_id)), p_org_id,
4839: p_instance_id, trunc(AD.Demand_Date),
4840: p_level_id, AD.DEMAND_CLASS),
4844: DECODE(p_demand_class, '-1',
4845: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4846: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4847: 1, p_family_id,
4848: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4849: 'Y', AD.Original_Item_Id,
4850: p_family_id)), p_org_id,
4851: p_instance_id, trunc(AD.Demand_Date),
4852: p_level_id, AD.DEMAND_CLASS),
4855: DECODE(p_demand_class, '-1',
4856: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4857: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4858: 1, p_family_id,
4859: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4860: 'Y', AD.Original_Item_Id,
4861: p_family_id)), p_org_id,
4862: p_instance_id, trunc(AD.Demand_Date),
4863: p_level_id, AD.DEMAND_CLASS),
4866: DECODE(p_demand_class, '-1',
4867: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4868: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4869: 1, p_family_id,
4870: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4871: 'Y', AD.Original_Item_Id,
4872: p_family_id)), p_org_id,
4873: p_instance_id, trunc(AD.Demand_Date),
4874: p_level_id, AD.DEMAND_CLASS),
4877: DECODE(p_demand_class, '-1',
4878: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4879: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4880: 1, p_family_id,
4881: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4882: 'Y', AD.Original_Item_Id,
4883: p_family_id)), p_org_id,
4884: p_instance_id, trunc(AD.Demand_Date),
4885: p_level_id, AD.DEMAND_CLASS),
4890: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4891: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4892: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4893: 1, p_family_id,
4894: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4895: 'Y', AD.Original_Item_Id,
4896: p_family_id)),
4897: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4898: p_level_id, NULL),
4898: p_level_id, NULL),
4899: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4900: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4901: 1, p_family_id,
4902: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4903: 'Y', AD.Original_Item_Id,
4904: p_family_id)),
4905: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4906: p_level_id, NULL),
4906: p_level_id, NULL),
4907: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4908: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4909: 1, p_family_id,
4910: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4911: 'Y', AD.Original_Item_Id,
4912: p_family_id)),
4913: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4914: p_level_id, NULL),
4916: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4917: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4918: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4919: 1, p_family_id,
4920: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4921: 'Y', AD.Original_Item_Id,
4922: p_family_id)),
4923: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4924: p_level_id, NULL),
4924: p_level_id, NULL),
4925: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4926: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4927: 1, p_family_id,
4928: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4929: 'Y', AD.Original_Item_Id,
4930: p_family_id)),
4931: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4932: p_level_id, NULL),
4932: p_level_id, NULL),
4933: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4934: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4935: 1, p_family_id,
4936: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4937: 'Y', AD.Original_Item_Id,
4938: p_family_id)),
4939: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4940: p_level_id, NULL),
4942: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4943: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
4944: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4945: 1, p_family_id,
4946: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4947: 'Y', AD.Original_Item_Id,
4948: p_family_id)), p_org_id, p_instance_id,
4949: trunc(AD.Demand_Date),p_level_id, NULL)))),
4950: p_demand_class, 1,
4956: -23453, null,
4957: AD.SOURCE_ORGANIZATION_ID),
4958: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4959: 1, p_family_id,
4960: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4961: 'Y', AD.Original_Item_Id,
4962: p_family_id)),
4963: p_org_id,
4964: p_instance_id,
4970: DECODE(p_demand_class, '-1',
4971: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4972: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4973: 1, p_family_id,
4974: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4975: 'Y', AD.Original_Item_Id,
4976: p_family_id)), p_org_id,
4977: p_instance_id, trunc(AD.Demand_Date),
4978: p_level_id, AD.DEMAND_CLASS),
4981: DECODE(p_demand_class, '-1',
4982: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4983: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4984: 1, p_family_id,
4985: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4986: 'Y', AD.Original_Item_Id,
4987: p_family_id)), p_org_id,
4988: p_instance_id, trunc(AD.Demand_Date),
4989: p_level_id, AD.DEMAND_CLASS),
4992: DECODE(p_demand_class, '-1',
4993: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4994: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4995: 1, p_family_id,
4996: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4997: 'Y', AD.Original_Item_Id,
4998: p_family_id)), p_org_id,
4999: p_instance_id, trunc(AD.Demand_Date),
5000: p_level_id, AD.DEMAND_CLASS),
5005: DECODE(p_demand_class, '-1',
5006: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5007: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5008: 1, p_family_id,
5009: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5010: 'Y', AD.Original_Item_Id,
5011: p_family_id)), p_org_id,
5012: p_instance_id, trunc(AD.Demand_Date),
5013: p_level_id, AD.DEMAND_CLASS),
5016: DECODE(p_demand_class, '-1',
5017: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5018: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5019: 1, p_family_id,
5020: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5021: 'Y', AD.Original_Item_Id,
5022: p_family_id)), p_org_id,
5023: p_instance_id, trunc(AD.Demand_Date),
5024: p_level_id, AD.DEMAND_CLASS),
5027: DECODE(p_demand_class, '-1',
5028: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5029: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5030: 1, p_family_id,
5031: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5032: 'Y', AD.Original_Item_Id,
5033: p_family_id)), p_org_id,
5034: p_instance_id, trunc(AD.Demand_Date),
5035: p_level_id, AD.DEMAND_CLASS),
5039: DECODE(p_demand_class, '-1',
5040: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5041: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5042: 1, p_family_id,
5043: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5044: 'Y', AD.Original_Item_Id,
5045: p_family_id)), p_org_id,
5046: p_instance_id, trunc(AD.Demand_Date),
5047: p_level_id, AD.DEMAND_CLASS),
5052: 6, decode(AD.SOURCE_ORGANIZATION_ID,
5053: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5054: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5055: 1, p_family_id,
5056: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5057: 'Y', AD.Original_Item_Id,
5058: p_family_id)),
5059: p_org_id, p_instance_id, trunc(AD.Demand_Date),
5060: p_level_id, NULL),
5060: p_level_id, NULL),
5061: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5062: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5063: 1, p_family_id,
5064: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5065: 'Y', AD.Original_Item_Id,
5066: p_family_id)),
5067: p_org_id, p_instance_id, trunc(AD.Demand_Date),
5068: p_level_id, NULL),
5068: p_level_id, NULL),
5069: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5070: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5071: 1, p_family_id,
5072: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5073: 'Y', AD.Original_Item_Id,
5074: p_family_id)),
5075: p_org_id, p_instance_id, trunc(AD.Demand_Date),
5076: p_level_id, NULL),
5078: 30, decode(AD.SOURCE_ORGANIZATION_ID,
5079: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5080: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5081: 1, p_family_id,
5082: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5083: 'Y', AD.Original_Item_Id,
5084: p_family_id)),
5085: p_org_id, p_instance_id, trunc(AD.Demand_Date),
5086: p_level_id, NULL),
5086: p_level_id, NULL),
5087: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5088: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5089: 1, p_family_id,
5090: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5091: 'Y', AD.Original_Item_Id,
5092: p_family_id)),
5093: p_org_id, p_instance_id, trunc(AD.Demand_Date),
5094: p_level_id, NULL),
5094: p_level_id, NULL),
5095: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5096: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5097: 1, p_family_id,
5098: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5099: 'Y', AD.Original_Item_Id,
5100: p_family_id)),
5101: p_org_id, p_instance_id, trunc(AD.Demand_Date),
5102: p_level_id, NULL),
5104: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5105: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
5106: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5107: 1, p_family_id,
5108: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5109: 'Y', AD.Original_Item_Id,
5110: p_family_id)), p_org_id, p_instance_id,
5111: trunc(AD.Demand_Date),p_level_id, NULL)))),
5112: p_demand_class,
5177: null,
5178: null,
5179: Decode(sign(trunc(CSA.Supply_Date) - p_atf_date),
5180: 1, p_family_id,
5181: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5182: 'Y', CSA.Original_Item_Id,
5183: p_family_id)),
5184: p_org_id,
5185: p_instance_id,
5193: CSA.CUSTOMER_ID,
5194: CSA.SHIP_TO_SITE_ID,
5195: Decode(sign(trunc(CSA.Supply_Date) - p_atf_date),
5196: 1, p_family_id,
5197: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5198: 'Y', CSA.Original_Item_Id,
5199: p_family_id)),
5200: p_org_id,
5201: p_instance_id,
5207: /*NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
5208: p_instance_id,
5209: Decode(sign(trunc(CSA.Supply_Date) - p_atf_date),
5210: 1, p_family_id,
5211: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5212: 'Y', CSA.Original_Item_Id,
5213: p_family_id)),
5214: p_org_id,
5215: null,
5222: /*DECODE(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
5223: p_instance_id,
5224: Decode(sign(trunc(CSA.Supply_Date) - p_atf_date),
5225: 1, p_family_id,
5226: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5227: 'Y', CSA.Original_Item_Id,
5228: p_family_id)),
5229: p_org_id,
5230: null,
6189: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6190: null, null,
6191: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6192: 1, p_family_id,
6193: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6194: 'Y', AD.Original_Item_Id,
6195: p_family_id)),
6196: p_org_id,
6197: p_instance_id, trunc(AD.DEMAND_DATE),
6202: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6203: null, null,
6204: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6205: 1, p_family_id,
6206: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6207: 'Y', AD.Original_Item_Id,
6208: p_family_id)),
6209: p_org_id,
6210: p_instance_id, trunc(AD.DEMAND_DATE),
6215: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6216: null, null,
6217: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6218: 1, p_family_id,
6219: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6220: 'Y', AD.Original_Item_Id,
6221: p_family_id)),
6222: p_org_id,
6223: p_instance_id, trunc(AD.DEMAND_DATE),
6229: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6230: null, null,
6231: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6232: 1, p_family_id,
6233: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6234: 'Y', AD.Original_Item_Id,
6235: p_family_id)),
6236: p_org_id,
6237: p_instance_id, trunc(AD.DEMAND_DATE),
6242: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6243: null, null,
6244: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6245: 1, p_family_id,
6246: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6247: 'Y', AD.Original_Item_Id,
6248: p_family_id)),
6249: p_org_id,
6250: p_instance_id, trunc(AD.DEMAND_DATE),
6255: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6256: null, null,
6257: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6258: 1, p_family_id,
6259: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6260: 'Y', AD.Original_Item_Id,
6261: p_family_id)),
6262: p_org_id,
6263: p_instance_id, trunc(AD.DEMAND_DATE),
6268: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6269: null, null,
6270: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6271: 1, p_family_id,
6272: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6273: 'Y', AD.Original_Item_Id,
6274: p_family_id)),
6275: p_org_id,
6276: p_instance_id, trunc(AD.DEMAND_DATE),
6285: -23453, null,
6286: AD.SOURCE_ORGANIZATION_ID),
6287: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6288: 1, p_family_id,
6289: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6290: 'Y', AD.Original_Item_Id,
6291: p_family_id)),
6292: p_org_id,
6293: p_instance_id,
6299: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6300: null, null,
6301: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6302: 1, p_family_id,
6303: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6304: 'Y', AD.Original_Item_Id,
6305: p_family_id)),
6306: p_org_id,
6307: p_instance_id, trunc(AD.DEMAND_DATE),
6312: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6313: null, null,
6314: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6315: 1, p_family_id,
6316: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6317: 'Y', AD.Original_Item_Id,
6318: p_family_id)),
6319: p_org_id,
6320: p_instance_id, trunc(AD.DEMAND_DATE),
6325: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6326: null, null,
6327: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6328: 1, p_family_id,
6329: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6330: 'Y', AD.Original_Item_Id,
6331: p_family_id)),
6332: p_org_id,
6333: p_instance_id, trunc(AD.DEMAND_DATE),
6339: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6340: null, null,
6341: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6342: 1, p_family_id,
6343: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6344: 'Y', AD.Original_Item_Id,
6345: p_family_id)),
6346: p_org_id,
6347: p_instance_id, trunc(AD.DEMAND_DATE),
6352: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6353: null, null,
6354: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6355: 1, p_family_id,
6356: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6357: 'Y', AD.Original_Item_Id,
6358: p_family_id)),
6359: p_org_id,
6360: p_instance_id, trunc(AD.DEMAND_DATE),
6365: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6366: null, null,
6367: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6368: 1, p_family_id,
6369: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6370: 'Y', AD.Original_Item_Id,
6371: p_family_id)),
6372: p_org_id,
6373: p_instance_id, trunc(AD.DEMAND_DATE),
6378: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6379: null, null,
6380: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6381: 1, p_family_id,
6382: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6383: 'Y', AD.Original_Item_Id,
6384: p_family_id)),
6385: p_org_id,
6386: p_instance_id, trunc(AD.DEMAND_DATE),
6417: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6418: null, null,
6419: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6420: 1, p_family_id,
6421: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6422: 'Y', AD.Original_Item_Id,
6423: p_family_id)),
6424: p_org_id,
6425: p_instance_id, trunc(AD.DEMAND_DATE),
6430: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6431: null, null,
6432: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6433: 1, p_family_id,
6434: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6435: 'Y', AD.Original_Item_Id,
6436: p_family_id)),
6437: p_org_id,
6438: p_instance_id, trunc(AD.DEMAND_DATE),
6443: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6444: null, null,
6445: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6446: 1, p_family_id,
6447: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6448: 'Y', AD.Original_Item_Id,
6449: p_family_id)),
6450: p_org_id,
6451: p_instance_id, trunc(AD.DEMAND_DATE),
6457: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6458: null, null,
6459: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6460: 1, p_family_id,
6461: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6462: 'Y', AD.Original_Item_Id,
6463: p_family_id)),
6464: p_org_id,
6465: p_instance_id, trunc(AD.DEMAND_DATE),
6470: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6471: null, null,
6472: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6473: 1, p_family_id,
6474: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6475: 'Y', AD.Original_Item_Id,
6476: p_family_id)),
6477: p_org_id,
6478: p_instance_id, trunc(AD.DEMAND_DATE),
6483: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6484: null, null,
6485: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6486: 1, p_family_id,
6487: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6488: 'Y', AD.Original_Item_Id,
6489: p_family_id)),
6490: p_org_id,
6491: p_instance_id, trunc(AD.DEMAND_DATE),
6496: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6497: null, null,
6498: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6499: 1, p_family_id,
6500: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6501: 'Y', AD.Original_Item_Id,
6502: p_family_id)),
6503: p_org_id,
6504: p_instance_id, trunc(AD.DEMAND_DATE),
6513: -23453, null,
6514: AD.SOURCE_ORGANIZATION_ID),
6515: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6516: 1, p_family_id,
6517: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6518: 'Y', AD.Original_Item_Id,
6519: p_family_id)),
6520: p_org_id,
6521: p_instance_id,
6527: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6528: null, null,
6529: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6530: 1, p_family_id,
6531: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6532: 'Y', AD.Original_Item_Id,
6533: p_family_id)),
6534: p_org_id,
6535: p_instance_id, trunc(AD.DEMAND_DATE),
6540: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6541: null, null,
6542: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6543: 1, p_family_id,
6544: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6545: 'Y', AD.Original_Item_Id,
6546: p_family_id)),
6547: p_org_id,
6548: p_instance_id, trunc(AD.DEMAND_DATE),
6553: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6554: null, null,
6555: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6556: 1, p_family_id,
6557: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6558: 'Y', AD.Original_Item_Id,
6559: p_family_id)),
6560: p_org_id,
6561: p_instance_id, trunc(AD.DEMAND_DATE),
6567: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6568: null, null,
6569: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6570: 1, p_family_id,
6571: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6572: 'Y', AD.Original_Item_Id,
6573: p_family_id)),
6574: p_org_id,
6575: p_instance_id, trunc(AD.DEMAND_DATE),
6580: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6581: null, null,
6582: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6583: 1, p_family_id,
6584: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6585: 'Y', AD.Original_Item_Id,
6586: p_family_id)),
6587: p_org_id,
6588: p_instance_id, trunc(AD.DEMAND_DATE),
6593: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6594: null, null,
6595: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6596: 1, p_family_id,
6597: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6598: 'Y', AD.Original_Item_Id,
6599: p_family_id)),
6600: p_org_id,
6601: p_instance_id, trunc(AD.DEMAND_DATE),
6606: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6607: null, null,
6608: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6609: 1, p_family_id,
6610: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6611: 'Y', AD.Original_Item_Id,
6612: p_family_id)),
6613: p_org_id,
6614: p_instance_id, trunc(AD.DEMAND_DATE),
6667: null,
6668: null,
6669: Decode(sign(trunc(SA.Supply_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6670: 1, p_family_id,
6671: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6672: 'Y', SA.Original_Item_Id,
6673: p_family_id)),
6674: p_org_id,
6675: p_instance_id,
6683: NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
6684: p_instance_id,
6685: Decode(sign(trunc(SA.Supply_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6686: 1, p_family_id,
6687: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6688: 'Y', SA.Original_Item_Id,
6689: p_family_id)),
6690: p_org_id,
6691: null,
6696: DECODE(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
6697: p_instance_id,
6698: Decode(sign(trunc(SA.Supply_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6699: 1, p_family_id,
6700: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6701: 'Y', SA.Original_Item_Id,
6702: p_family_id)),
6703: p_org_id,
6704: null,
6788: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6789: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6790: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6791: 1, p_family_id,
6792: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6793: 'Y', AD.Original_Item_Id,
6794: p_family_id)),
6795: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6796: p_level_id, NULL),
6797: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6798: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6799: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6800: 1, p_family_id,
6801: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6802: 'Y', AD.Original_Item_Id,
6803: p_family_id)),
6804: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6805: p_level_id, NULL),
6806: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6807: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6808: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6809: 1, p_family_id,
6810: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6811: 'Y', AD.Original_Item_Id,
6812: p_family_id)),
6813: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6814: p_level_id, NULL),
6817: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6818: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6819: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6820: 1, p_family_id,
6821: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6822: 'Y', AD.Original_Item_Id,
6823: p_family_id)),
6824: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6825: p_level_id, NULL),
6826: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6827: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6828: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6829: 1, p_family_id,
6830: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6831: 'Y', AD.Original_Item_Id,
6832: p_family_id)),
6833: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6834: p_level_id, NULL),
6835: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6836: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6837: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6838: 1, p_family_id,
6839: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6840: 'Y', AD.Original_Item_Id,
6841: p_family_id)),
6842: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6843: p_level_id, NULL),
6845: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6846: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6847: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6848: 1, p_family_id,
6849: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6850: 'Y', AD.Original_Item_Id,
6851: p_family_id)),
6852: p_org_id, p_instance_id,
6853: TRUNC(AD.ORIGINAL_DEMAND_DATE),
6861: -23453, null,
6862: AD.SOURCE_ORGANIZATION_ID),
6863: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6864: 1, p_family_id,
6865: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6866: 'Y', AD.Original_Item_Id,
6867: p_family_id)),
6868: p_org_id,
6869: p_instance_id,
6875: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6876: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6877: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6878: 1, p_family_id,
6879: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6880: 'Y', AD.Original_Item_Id,
6881: p_family_id)),
6882: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6883: p_level_id, NULL),
6884: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6885: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6886: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6887: 1, p_family_id,
6888: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6889: 'Y', AD.Original_Item_Id,
6890: p_family_id)),
6891: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6892: p_level_id, NULL),
6893: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6894: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6895: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6896: 1, p_family_id,
6897: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6898: 'Y', AD.Original_Item_Id,
6899: p_family_id)),
6900: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6901: p_level_id, NULL),
6904: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6905: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6906: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6907: 1, p_family_id,
6908: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6909: 'Y', AD.Original_Item_Id,
6910: p_family_id)),
6911: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6912: p_level_id, NULL),
6913: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6914: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6915: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6916: 1, p_family_id,
6917: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6918: 'Y', AD.Original_Item_Id,
6919: p_family_id)),
6920: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6921: p_level_id, NULL),
6922: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6923: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6924: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6925: 1, p_family_id,
6926: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6927: 'Y', AD.Original_Item_Id,
6928: p_family_id)),
6929: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6930: p_level_id, NULL),
6932: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6933: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6934: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6935: 1, p_family_id,
6936: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6937: 'Y', AD.Original_Item_Id,
6938: p_family_id)),
6939: p_org_id, p_instance_id,
6940: TRUNC(AD.ORIGINAL_DEMAND_DATE),
6974: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6975: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6976: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6977: 1, p_family_id,
6978: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6979: 'Y', AD.Original_Item_Id,
6980: p_family_id)),
6981: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6982: p_level_id, NULL),
6983: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6984: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6985: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6986: 1, p_family_id,
6987: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6988: 'Y', AD.Original_Item_Id,
6989: p_family_id)),
6990: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6991: p_level_id, NULL),
6992: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6993: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6994: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6995: 1, p_family_id,
6996: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6997: 'Y', AD.Original_Item_Id,
6998: p_family_id)),
6999: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7000: p_level_id, NULL),
7003: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7004: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7005: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7006: 1, p_family_id,
7007: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7008: 'Y', AD.Original_Item_Id,
7009: p_family_id)),
7010: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7011: p_level_id, NULL),
7012: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7013: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7014: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7015: 1, p_family_id,
7016: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7017: 'Y', AD.Original_Item_Id,
7018: p_family_id)),
7019: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7020: p_level_id, NULL),
7021: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7022: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7023: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7024: 1, p_family_id,
7025: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7026: 'Y', AD.Original_Item_Id,
7027: p_family_id)),
7028: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7029: p_level_id, NULL),
7031: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7032: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7033: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7034: 1, p_family_id,
7035: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7036: 'Y', AD.Original_Item_Id,
7037: p_family_id)),
7038: p_org_id, p_instance_id,
7039: TRUNC(AD.ORIGINAL_DEMAND_DATE),
7047: -23453, null,
7048: AD.SOURCE_ORGANIZATION_ID),
7049: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7050: 1, p_family_id,
7051: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7052: 'Y', AD.Original_Item_Id,
7053: p_family_id)),
7054: p_org_id,
7055: p_instance_id,
7061: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7062: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7063: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7064: 1, p_family_id,
7065: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7066: 'Y', AD.Original_Item_Id,
7067: p_family_id)),
7068: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7069: p_level_id, NULL),
7070: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7071: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7072: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7073: 1, p_family_id,
7074: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7075: 'Y', AD.Original_Item_Id,
7076: p_family_id)),
7077: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7078: p_level_id, NULL),
7079: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7080: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7081: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7082: 1, p_family_id,
7083: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7084: 'Y', AD.Original_Item_Id,
7085: p_family_id)),
7086: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7087: p_level_id, NULL),
7090: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7091: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7092: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7093: 1, p_family_id,
7094: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7095: 'Y', AD.Original_Item_Id,
7096: p_family_id)),
7097: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7098: p_level_id, NULL),
7099: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7100: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7101: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7102: 1, p_family_id,
7103: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7104: 'Y', AD.Original_Item_Id,
7105: p_family_id)),
7106: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7107: p_level_id, NULL),
7108: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7109: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7110: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7111: 1, p_family_id,
7112: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7113: 'Y', AD.Original_Item_Id,
7114: p_family_id)),
7115: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7116: p_level_id, NULL),
7118: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7119: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7120: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7121: 1, p_family_id,
7122: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7123: 'Y', AD.Original_Item_Id,
7124: p_family_id)),
7125: p_org_id, p_instance_id,
7126: TRUNC(AD.ORIGINAL_DEMAND_DATE),
7175: SA.CUSTOMER_ID,
7176: SA.SHIP_TO_SITE_ID,
7177: Decode(sign(trunc(SA.Supply_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7178: 1, p_family_id,
7179: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7180: 'Y', SA.Original_Item_Id,
7181: p_family_id)),
7182: p_org_id,
7183: p_instance_id,
7190: NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
7191: p_instance_id,
7192: Decode(sign(trunc(SA.Supply_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7193: 1, p_family_id,
7194: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7195: 'Y', SA.Original_Item_Id,
7196: p_family_id)),
7197: p_org_id,
7198: null,
7202: DECODE(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
7203: p_instance_id,
7204: Decode(sign(trunc(SA.Supply_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7205: 1, p_family_id,
7206: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7207: 'Y', SA.Original_Item_Id,
7208: p_family_id)),
7209: p_org_id,
7210: null,
8766: p_demand_class,
8767: p_parent_plan_id --bug3510475
8768: );
8769:
8770: IF (MSC_ATP_PVT.G_PLAN_INFO_REC.plan_id = -300) THEN
8771: IF PG_DEBUG in ('Y', 'C') THEN
8772: msc_sch_wb.atp_debug('Get_PF_Plan_Info: ' || 'ATP Downtime');
8773: END IF;
8774:
8772: msc_sch_wb.atp_debug('Get_PF_Plan_Info: ' || 'ATP Downtime');
8773: END IF;
8774:
8775: x_return_status := FND_API.G_RET_STS_ERROR;
8776: MSC_ATP_PVT.G_DOWNTIME_HIT := 'Y';
8777: p_error_code := MSC_ATP_PVT.PLAN_DOWN_TIME;
8778: RAISE NO_DATA_FOUND;
8779: END IF;
8780:
8773: END IF;
8774:
8775: x_return_status := FND_API.G_RET_STS_ERROR;
8776: MSC_ATP_PVT.G_DOWNTIME_HIT := 'Y';
8777: p_error_code := MSC_ATP_PVT.PLAN_DOWN_TIME;
8778: RAISE NO_DATA_FOUND;
8779: END IF;
8780:
8781: IF PG_DEBUG in ('Y', 'C') THEN
8778: RAISE NO_DATA_FOUND;
8779: END IF;
8780:
8781: IF PG_DEBUG in ('Y', 'C') THEN
8782: msc_sch_wb.atp_debug('Get_PF_Plan_Info: ' || 'MSC_ATP_PVT.G_PLAN_INFO_REC.plan_id = '||MSC_ATP_PVT.G_PLAN_INFO_REC.plan_id);
8783: END IF;
8784:
8785: -- ATP4drp begin
8786: IF NVL(MSC_ATP_PVT.G_PLAN_INFO_REC.plan_type, 1) = 5 THEN
8782: msc_sch_wb.atp_debug('Get_PF_Plan_Info: ' || 'MSC_ATP_PVT.G_PLAN_INFO_REC.plan_id = '||MSC_ATP_PVT.G_PLAN_INFO_REC.plan_id);
8783: END IF;
8784:
8785: -- ATP4drp begin
8786: IF NVL(MSC_ATP_PVT.G_PLAN_INFO_REC.plan_type, 1) = 5 THEN
8787: IF PG_DEBUG in ('Y', 'C') THEN
8788: msc_sch_wb.atp_debug('----- ATP4drp Specific Debug Messages -----');
8789: msc_sch_wb.atp_debug('Get_PF_Plan_Info: ' || 'PF and Allocated ATP not applicable for DRP plans');
8790: msc_sch_wb.atp_debug('Get_PF_Plan_Info: ' || 'ATF date will not be obtained.');
8797: p_atf_date := MSC_ATP_PF.Get_Atf_Date(
8798: p_instance_id,
8799: p_member_item_id,
8800: p_org_id,
8801: MSC_ATP_PVT.G_PLAN_INFO_REC.plan_id
8802: );
8803:
8804: IF PG_DEBUG in ('Y', 'C') THEN
8805: msc_sch_wb.atp_debug('Get_PF_Plan_Info: ' || 'ATF Date = '||p_atf_date);
8824: p_demand_class,
8825: p_parent_plan_id --bug3510475
8826: );
8827:
8828: IF (MSC_ATP_PVT.G_PLAN_INFO_REC.plan_id = -300) THEN
8829: IF PG_DEBUG in ('Y', 'C') THEN
8830: msc_sch_wb.atp_debug('Get_PF_Plan_Info: ' || 'ATP Downtime');
8831: END IF;
8832:
8830: msc_sch_wb.atp_debug('Get_PF_Plan_Info: ' || 'ATP Downtime');
8831: END IF;
8832:
8833: x_return_status := FND_API.G_RET_STS_ERROR;
8834: MSC_ATP_PVT.G_DOWNTIME_HIT := 'Y';
8835: p_error_code := MSC_ATP_PVT.PLAN_DOWN_TIME;
8836: RAISE NO_DATA_FOUND;
8837: END IF;
8838: END IF;
8831: END IF;
8832:
8833: x_return_status := FND_API.G_RET_STS_ERROR;
8834: MSC_ATP_PVT.G_DOWNTIME_HIT := 'Y';
8835: p_error_code := MSC_ATP_PVT.PLAN_DOWN_TIME;
8836: RAISE NO_DATA_FOUND;
8837: END IF;
8838: END IF;
8839: ELSE
10129: msc_util.msc_log('Prepare_Demands_Stmt: ' || 'p_excess_supply_by_dc : ' || p_excess_supply_by_dc);
10130: msc_util.msc_log('Prepare_Demands_Stmt: ' || 'p_temp_table : ' || p_temp_table);
10131: msc_util.msc_log('Prepare_Demands_Stmt: ' || 'p_alloc_temp_table : ' || p_alloc_temp_table);
10132: msc_util.msc_log('Prepare_Demands_Stmt: ' || 'p_parallel_degree : ' || p_parallel_degree);
10133: msc_util.msc_log('Prepare_Demands_Stmt: ' || 'MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF : ' || MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF);
10134: END IF;
10135:
10136: -- Initializing API return code
10137: x_return_status := FND_API.G_RET_STS_SUCCESS;
10215: NULL, :def_num,
10216: mat.demand_class) demand_class,
10217: decode(msi.aggregate_time_fence_date,
10218: -- Bug 3574164. DMD_SATISFIED_TIME changed to PLANNED_SHIP_DATE.
10219: NULL, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10220: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10221: NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))),
10222: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
10223: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10219: NULL, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10220: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10221: NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))),
10222: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
10223: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10224: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10225: NVL(D.SCHEDULE_SHIP_DATE,
10226: D.USING_ASSEMBLY_DEMAND_DATE)))
10227: - msi.aggregate_time_fence_date),
10224: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10225: NVL(D.SCHEDULE_SHIP_DATE,
10226: D.USING_ASSEMBLY_DEMAND_DATE)))
10227: - msi.aggregate_time_fence_date),
10228: 1, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10229: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10230: NVL(D.SCHEDULE_SHIP_DATE,
10231: D.USING_ASSEMBLY_DEMAND_DATE))),
10232: msi.aggregate_time_fence_date+1),
10229: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10230: NVL(D.SCHEDULE_SHIP_DATE,
10231: D.USING_ASSEMBLY_DEMAND_DATE))),
10232: msi.aggregate_time_fence_date+1),
10233: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10234: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10235: NVL(D.SCHEDULE_SHIP_DATE,
10236: D.USING_ASSEMBLY_DEMAND_DATE)))
10237: - msi.aggregate_time_fence_date),
10235: NVL(D.SCHEDULE_SHIP_DATE,
10236: D.USING_ASSEMBLY_DEMAND_DATE)))
10237: - msi.aggregate_time_fence_date),
10238: 1, msi.aggregate_time_fence_date,
10239: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10240: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10241: NVL(D.SCHEDULE_SHIP_DATE,
10242: D.USING_ASSEMBLY_DEMAND_DATE)))))) demand_date,
10243: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10239: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10240: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10241: NVL(D.SCHEDULE_SHIP_DATE,
10242: D.USING_ASSEMBLY_DEMAND_DATE)))))) demand_date,
10243: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10244: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10245: NVL(D.SCHEDULE_SHIP_DATE,
10246: D.USING_ASSEMBLY_DEMAND_DATE))) original_demand_date,
10247: decode(d.origination_type, 4, d.daily_demand_rate,
10257: d.origination_type original_origination_type,
10258: decode(msi.aggregate_time_fence_date,
10259: NULL, NULL,
10260: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
10261: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10262: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10263: NVL(D.SCHEDULE_SHIP_DATE,
10264: D.USING_ASSEMBLY_DEMAND_DATE)))
10265: - msi.aggregate_time_fence_date),
10264: D.USING_ASSEMBLY_DEMAND_DATE)))
10265: - msi.aggregate_time_fence_date),
10266: 1, 1,
10267: NULL),
10268: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10269: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10270: NVL(D.SCHEDULE_SHIP_DATE,
10271: D.USING_ASSEMBLY_DEMAND_DATE)))
10272: - msi.aggregate_time_fence_date),
10379: peg1.organization_id,
10380: peg1.sr_instance_id,
10381: d.demand_class demand_class,
10382: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
10383: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10384: -- Bug 3574164. DMD_SATISFIED_TIME changed to PLANNED_SHIP_DATE.
10385: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10386: NVL(D.SCHEDULE_SHIP_DATE,
10387: D.USING_ASSEMBLY_DEMAND_DATE)))
10385: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10386: NVL(D.SCHEDULE_SHIP_DATE,
10387: D.USING_ASSEMBLY_DEMAND_DATE)))
10388: - msi.aggregate_time_fence_date),
10389: 1, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10390: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10391: NVL(D.SCHEDULE_SHIP_DATE,
10392: D.USING_ASSEMBLY_DEMAND_DATE))),
10393: msi.aggregate_time_fence_date+1),
10390: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10391: NVL(D.SCHEDULE_SHIP_DATE,
10392: D.USING_ASSEMBLY_DEMAND_DATE))),
10393: msi.aggregate_time_fence_date+1),
10394: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10395: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10396: NVL(D.SCHEDULE_SHIP_DATE,
10397: D.USING_ASSEMBLY_DEMAND_DATE)))
10398: - msi.aggregate_time_fence_date),
10396: NVL(D.SCHEDULE_SHIP_DATE,
10397: D.USING_ASSEMBLY_DEMAND_DATE)))
10398: - msi.aggregate_time_fence_date),
10399: 1, msi.aggregate_time_fence_date,
10400: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10401: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10402: NVL(D.SCHEDULE_SHIP_DATE,
10403: D.USING_ASSEMBLY_DEMAND_DATE))))) demand_date,
10404: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10400: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10401: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10402: NVL(D.SCHEDULE_SHIP_DATE,
10403: D.USING_ASSEMBLY_DEMAND_DATE))))) demand_date,
10404: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10405: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10406: NVL(D.SCHEDULE_SHIP_DATE,
10407: D.USING_ASSEMBLY_DEMAND_DATE))) original_demand_date,
10408: MIN(decode(d.origination_type, 4, d.daily_demand_rate,
10411: d.demand_id,
10412: 51 origination_type, -- ATP Bucketed Demand
10413: d.origination_type original_origination_type,
10414: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
10415: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10416: -- Bug 3574164. DMD_SATISFIED_TIME changed to PLANNED_SHIP_DATE.
10417: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10418: NVL(D.SCHEDULE_SHIP_DATE,
10419: D.USING_ASSEMBLY_DEMAND_DATE)))
10419: D.USING_ASSEMBLY_DEMAND_DATE)))
10420: - msi.aggregate_time_fence_date),
10421: 1, 1,
10422: NULL),
10423: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10424: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10425: NVL(D.SCHEDULE_SHIP_DATE,
10426: D.USING_ASSEMBLY_DEMAND_DATE)))
10427: - msi.aggregate_time_fence_date),
10470: peg1.organization_id,
10471: peg1.sr_instance_id,
10472: d.demand_class,
10473: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
10474: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10475: -- Bug 3574164. DMD_SATISFIED_TIME changed to PLANNED_SHIP_DATE.
10476: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10477: NVL(D.SCHEDULE_SHIP_DATE,
10478: D.USING_ASSEMBLY_DEMAND_DATE)))
10476: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10477: NVL(D.SCHEDULE_SHIP_DATE,
10478: D.USING_ASSEMBLY_DEMAND_DATE)))
10479: - msi.aggregate_time_fence_date),
10480: 1, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10481: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10482: NVL(D.SCHEDULE_SHIP_DATE,
10483: D.USING_ASSEMBLY_DEMAND_DATE))),
10484: msi.aggregate_time_fence_date+1),
10481: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10482: NVL(D.SCHEDULE_SHIP_DATE,
10483: D.USING_ASSEMBLY_DEMAND_DATE))),
10484: msi.aggregate_time_fence_date+1),
10485: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10486: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10487: NVL(D.SCHEDULE_SHIP_DATE,
10488: D.USING_ASSEMBLY_DEMAND_DATE))) - msi.aggregate_time_fence_date),
10489: 1, msi.aggregate_time_fence_date,
10486: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10487: NVL(D.SCHEDULE_SHIP_DATE,
10488: D.USING_ASSEMBLY_DEMAND_DATE))) - msi.aggregate_time_fence_date),
10489: 1, msi.aggregate_time_fence_date,
10490: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10491: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10492: NVL(D.SCHEDULE_SHIP_DATE,
10493: D.USING_ASSEMBLY_DEMAND_DATE))))),
10494: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10490: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10491: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10492: NVL(D.SCHEDULE_SHIP_DATE,
10493: D.USING_ASSEMBLY_DEMAND_DATE))))),
10494: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10495: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10496: NVL(D.SCHEDULE_SHIP_DATE,
10497: D.USING_ASSEMBLY_DEMAND_DATE))),
10498: d.demand_id,
10498: d.demand_id,
10499: 51,
10500: d.origination_type,
10501: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
10502: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10503: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10504: NVL(D.SCHEDULE_SHIP_DATE,
10505: D.USING_ASSEMBLY_DEMAND_DATE)))
10506: - msi.aggregate_time_fence_date),
10505: D.USING_ASSEMBLY_DEMAND_DATE)))
10506: - msi.aggregate_time_fence_date),
10507: 1, 1,
10508: NULL),
10509: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10510: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10511: NVL(D.SCHEDULE_SHIP_DATE,
10512: D.USING_ASSEMBLY_DEMAND_DATE)))
10513: - msi.aggregate_time_fence_date),
10893: peg1.organization_id,
10894: peg1.sr_instance_id,
10895: NVL(s.demand_class, :def_num) demand_class,
10896: decode(msi.aggregate_time_fence_date,
10897: NULL, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10898: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10899: NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))),
10900: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
10901: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10897: NULL, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10898: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10899: NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))),
10900: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
10901: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10902: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10903: NVL(D.SCHEDULE_SHIP_DATE,
10904: D.USING_ASSEMBLY_DEMAND_DATE)))
10905: - msi.aggregate_time_fence_date),
10902: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10903: NVL(D.SCHEDULE_SHIP_DATE,
10904: D.USING_ASSEMBLY_DEMAND_DATE)))
10905: - msi.aggregate_time_fence_date),
10906: 1, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10907: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10908: NVL(D.SCHEDULE_SHIP_DATE,
10909: D.USING_ASSEMBLY_DEMAND_DATE))),
10910: msi.aggregate_time_fence_date+1),
10907: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10908: NVL(D.SCHEDULE_SHIP_DATE,
10909: D.USING_ASSEMBLY_DEMAND_DATE))),
10910: msi.aggregate_time_fence_date+1),
10911: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10912: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10913: NVL(D.SCHEDULE_SHIP_DATE,
10914: D.USING_ASSEMBLY_DEMAND_DATE)))
10915: - msi.aggregate_time_fence_date),
10913: NVL(D.SCHEDULE_SHIP_DATE,
10914: D.USING_ASSEMBLY_DEMAND_DATE)))
10915: - msi.aggregate_time_fence_date),
10916: 1, msi.aggregate_time_fence_date,
10917: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10918: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10919: NVL(D.SCHEDULE_SHIP_DATE,
10920: D.USING_ASSEMBLY_DEMAND_DATE)))))) demand_date,
10921: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10917: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10918: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10919: NVL(D.SCHEDULE_SHIP_DATE,
10920: D.USING_ASSEMBLY_DEMAND_DATE)))))) demand_date,
10921: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10922: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10923: NVL(D.SCHEDULE_SHIP_DATE,
10924: D.USING_ASSEMBLY_DEMAND_DATE))) original_demand_date,
10925: decode(d.origination_type, 4, d.daily_demand_rate,
10935: d.origination_type original_origination_type,
10936: decode(msi.aggregate_time_fence_date,
10937: NULL, NULL,
10938: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
10939: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10940: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10941: NVL(D.SCHEDULE_SHIP_DATE,
10942: D.USING_ASSEMBLY_DEMAND_DATE)))
10943: - msi.aggregate_time_fence_date),
10942: D.USING_ASSEMBLY_DEMAND_DATE)))
10943: - msi.aggregate_time_fence_date),
10944: 1, 1,
10945: NULL),
10946: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10947: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10948: NVL(D.SCHEDULE_SHIP_DATE,
10949: D.USING_ASSEMBLY_DEMAND_DATE)))
10950: - msi.aggregate_time_fence_date),
11058: peg1.organization_id,
11059: peg1.sr_instance_id,
11060: :def_num demand_class,
11061: decode(msi.aggregate_time_fence_date,
11062: NULL,trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11063: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11064: NVL(D.SCHEDULE_SHIP_DATE,
11065: D.USING_ASSEMBLY_DEMAND_DATE))),
11066: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11063: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11064: NVL(D.SCHEDULE_SHIP_DATE,
11065: D.USING_ASSEMBLY_DEMAND_DATE))),
11066: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11067: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11068: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11069: NVL(D.SCHEDULE_SHIP_DATE,
11070: D.USING_ASSEMBLY_DEMAND_DATE)))
11071: - msi.aggregate_time_fence_date),
11068: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11069: NVL(D.SCHEDULE_SHIP_DATE,
11070: D.USING_ASSEMBLY_DEMAND_DATE)))
11071: - msi.aggregate_time_fence_date),
11072: 1,trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11073: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11074: NVL(D.SCHEDULE_SHIP_DATE,
11075: D.USING_ASSEMBLY_DEMAND_DATE))),
11076: msi.aggregate_time_fence_date+1),
11073: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11074: NVL(D.SCHEDULE_SHIP_DATE,
11075: D.USING_ASSEMBLY_DEMAND_DATE))),
11076: msi.aggregate_time_fence_date+1),
11077: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11078: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11079: NVL(D.SCHEDULE_SHIP_DATE,
11080: D.USING_ASSEMBLY_DEMAND_DATE)))
11081: - msi.aggregate_time_fence_date),
11079: NVL(D.SCHEDULE_SHIP_DATE,
11080: D.USING_ASSEMBLY_DEMAND_DATE)))
11081: - msi.aggregate_time_fence_date),
11082: 1, msi.aggregate_time_fence_date,
11083: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11084: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11085: NVL(D.SCHEDULE_SHIP_DATE,
11086: D.USING_ASSEMBLY_DEMAND_DATE)))))) demand_date,
11087: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11083: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11084: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11085: NVL(D.SCHEDULE_SHIP_DATE,
11086: D.USING_ASSEMBLY_DEMAND_DATE)))))) demand_date,
11087: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11088: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11089: NVL(D.SCHEDULE_SHIP_DATE,
11090: D.USING_ASSEMBLY_DEMAND_DATE))) original_demand_date,
11091: MIN(decode(d.origination_type, 4, d.daily_demand_rate,
11101: d.origination_type original_origination_type,
11102: decode(msi.aggregate_time_fence_date,
11103: NULL, NULL,
11104: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11105: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11106: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11107: NVL(D.SCHEDULE_SHIP_DATE,
11108: D.USING_ASSEMBLY_DEMAND_DATE)))
11109: - msi.aggregate_time_fence_date),
11108: D.USING_ASSEMBLY_DEMAND_DATE)))
11109: - msi.aggregate_time_fence_date),
11110: 1, 1,
11111: NULL),
11112: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11113: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11114: NVL(D.SCHEDULE_SHIP_DATE,
11115: D.USING_ASSEMBLY_DEMAND_DATE)))
11116: - msi.aggregate_time_fence_date),
11158: peg1.organization_id,
11159: peg1.sr_instance_id,
11160: :def_num,
11161: decode(msi.aggregate_time_fence_date,
11162: NULL, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11163: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11164: NVL(D.SCHEDULE_SHIP_DATE,
11165: D.USING_ASSEMBLY_DEMAND_DATE))),
11166: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11163: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11164: NVL(D.SCHEDULE_SHIP_DATE,
11165: D.USING_ASSEMBLY_DEMAND_DATE))),
11166: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11167: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11168: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11169: NVL(D.SCHEDULE_SHIP_DATE,
11170: D.USING_ASSEMBLY_DEMAND_DATE)))
11171: - msi.aggregate_time_fence_date),
11168: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11169: NVL(D.SCHEDULE_SHIP_DATE,
11170: D.USING_ASSEMBLY_DEMAND_DATE)))
11171: - msi.aggregate_time_fence_date),
11172: 1, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11173: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11174: NVL(D.SCHEDULE_SHIP_DATE,
11175: D.USING_ASSEMBLY_DEMAND_DATE))),
11176: msi.aggregate_time_fence_date+1),
11173: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11174: NVL(D.SCHEDULE_SHIP_DATE,
11175: D.USING_ASSEMBLY_DEMAND_DATE))),
11176: msi.aggregate_time_fence_date+1),
11177: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11178: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11179: NVL(D.SCHEDULE_SHIP_DATE,
11180: D.USING_ASSEMBLY_DEMAND_DATE)))
11181: - msi.aggregate_time_fence_date),
11179: NVL(D.SCHEDULE_SHIP_DATE,
11180: D.USING_ASSEMBLY_DEMAND_DATE)))
11181: - msi.aggregate_time_fence_date),
11182: 1, msi.aggregate_time_fence_date,
11183: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11184: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11185: NVL(D.SCHEDULE_SHIP_DATE,
11186: D.USING_ASSEMBLY_DEMAND_DATE)))))),
11187: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11183: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11184: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11185: NVL(D.SCHEDULE_SHIP_DATE,
11186: D.USING_ASSEMBLY_DEMAND_DATE)))))),
11187: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11188: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11189: NVL(D.SCHEDULE_SHIP_DATE,
11190: D.USING_ASSEMBLY_DEMAND_DATE))),
11191: d.demand_id,
11194: d.origination_type,
11195: decode(msi.aggregate_time_fence_date,
11196: NULL, NULL,
11197: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11198: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11199: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11200: NVL(D.SCHEDULE_SHIP_DATE,
11201: D.USING_ASSEMBLY_DEMAND_DATE)))
11202: - msi.aggregate_time_fence_date),
11201: D.USING_ASSEMBLY_DEMAND_DATE)))
11202: - msi.aggregate_time_fence_date),
11203: 1, 1,
11204: NULL),
11205: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11206: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11207: NVL(D.SCHEDULE_SHIP_DATE,
11208: D.USING_ASSEMBLY_DEMAND_DATE)))
11209: - msi.aggregate_time_fence_date),
11258: peg1.organization_id,
11259: peg1.sr_instance_id,
11260: d.demand_class demand_class,
11261: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11262: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11263: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11264: NVL(D.SCHEDULE_SHIP_DATE,
11265: D.USING_ASSEMBLY_DEMAND_DATE)))
11266: - msi.aggregate_time_fence_date),
11263: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11264: NVL(D.SCHEDULE_SHIP_DATE,
11265: D.USING_ASSEMBLY_DEMAND_DATE)))
11266: - msi.aggregate_time_fence_date),
11267: 1, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11268: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11269: NVL(D.SCHEDULE_SHIP_DATE,
11270: D.USING_ASSEMBLY_DEMAND_DATE))),
11271: msi.aggregate_time_fence_date+1),
11268: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11269: NVL(D.SCHEDULE_SHIP_DATE,
11270: D.USING_ASSEMBLY_DEMAND_DATE))),
11271: msi.aggregate_time_fence_date+1),
11272: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11273: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11274: NVL(D.SCHEDULE_SHIP_DATE,
11275: D.USING_ASSEMBLY_DEMAND_DATE)))
11276: - msi.aggregate_time_fence_date),
11274: NVL(D.SCHEDULE_SHIP_DATE,
11275: D.USING_ASSEMBLY_DEMAND_DATE)))
11276: - msi.aggregate_time_fence_date),
11277: 1, msi.aggregate_time_fence_date,
11278: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11279: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11280: NVL(D.SCHEDULE_SHIP_DATE,
11281: D.USING_ASSEMBLY_DEMAND_DATE))))) demand_date,
11282: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11278: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11279: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11280: NVL(D.SCHEDULE_SHIP_DATE,
11281: D.USING_ASSEMBLY_DEMAND_DATE))))) demand_date,
11282: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11283: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11284: NVL(D.SCHEDULE_SHIP_DATE,
11285: D.USING_ASSEMBLY_DEMAND_DATE))) original_demand_date,
11286: MIN(decode(d.origination_type, 4, d.daily_demand_rate,
11289: d.demand_id,
11290: 51 origination_type, -- ATP Bucketed Demand
11291: d.origination_type original_origination_type,
11292: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11293: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11294: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11295: NVL(D.SCHEDULE_SHIP_DATE,
11296: D.USING_ASSEMBLY_DEMAND_DATE)))
11297: - msi.aggregate_time_fence_date),
11296: D.USING_ASSEMBLY_DEMAND_DATE)))
11297: - msi.aggregate_time_fence_date),
11298: 1, 1,
11299: NULL),
11300: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11301: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11302: NVL(D.SCHEDULE_SHIP_DATE,
11303: D.USING_ASSEMBLY_DEMAND_DATE)))
11304: - msi.aggregate_time_fence_date),
11342: peg1.organization_id,
11343: peg1.sr_instance_id,
11344: d.demand_class,
11345: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11346: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11347: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11348: NVL(D.SCHEDULE_SHIP_DATE,
11349: D.USING_ASSEMBLY_DEMAND_DATE)))
11350: - msi.aggregate_time_fence_date),
11347: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11348: NVL(D.SCHEDULE_SHIP_DATE,
11349: D.USING_ASSEMBLY_DEMAND_DATE)))
11350: - msi.aggregate_time_fence_date),
11351: 1, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11352: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11353: NVL(D.SCHEDULE_SHIP_DATE,
11354: D.USING_ASSEMBLY_DEMAND_DATE))),
11355: msi.aggregate_time_fence_date+1),
11352: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11353: NVL(D.SCHEDULE_SHIP_DATE,
11354: D.USING_ASSEMBLY_DEMAND_DATE))),
11355: msi.aggregate_time_fence_date+1),
11356: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11357: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11358: NVL(D.SCHEDULE_SHIP_DATE,
11359: D.USING_ASSEMBLY_DEMAND_DATE))) - msi.aggregate_time_fence_date),
11360: 1, msi.aggregate_time_fence_date,
11357: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11358: NVL(D.SCHEDULE_SHIP_DATE,
11359: D.USING_ASSEMBLY_DEMAND_DATE))) - msi.aggregate_time_fence_date),
11360: 1, msi.aggregate_time_fence_date,
11361: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11362: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11363: NVL(D.SCHEDULE_SHIP_DATE,
11364: D.USING_ASSEMBLY_DEMAND_DATE))))),
11365: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11361: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11362: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11363: NVL(D.SCHEDULE_SHIP_DATE,
11364: D.USING_ASSEMBLY_DEMAND_DATE))))),
11365: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11366: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11367: NVL(D.SCHEDULE_SHIP_DATE,
11368: D.USING_ASSEMBLY_DEMAND_DATE))),
11369: d.demand_id,
11369: d.demand_id,
11370: 51,
11371: d.origination_type,
11372: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11373: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11374: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11375: NVL(D.SCHEDULE_SHIP_DATE,
11376: D.USING_ASSEMBLY_DEMAND_DATE)))
11377: - msi.aggregate_time_fence_date),
11376: D.USING_ASSEMBLY_DEMAND_DATE)))
11377: - msi.aggregate_time_fence_date),
11378: 1, 1,
11379: NULL),
11380: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11381: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11382: NVL(D.SCHEDULE_SHIP_DATE,
11383: D.USING_ASSEMBLY_DEMAND_DATE)))
11384: - msi.aggregate_time_fence_date),
11503: peg2.organization_id,
11504: peg2.sr_instance_id,
11505: NVL(d1.demand_class, :def_num) demand_class,
11506: decode(msi.aggregate_time_fence_date,
11507: NULL, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11508: 2, NVL(d2.PLANNED_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE),
11509: NVL(d2.SCHEDULE_SHIP_DATE,
11510: d2.USING_ASSEMBLY_DEMAND_DATE))),
11511: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11508: 2, NVL(d2.PLANNED_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE),
11509: NVL(d2.SCHEDULE_SHIP_DATE,
11510: d2.USING_ASSEMBLY_DEMAND_DATE))),
11511: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11512: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11513: 2, NVL(d2.PLANNED_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE),
11514: NVL(d2.SCHEDULE_SHIP_DATE,
11515: d2.USING_ASSEMBLY_DEMAND_DATE))) - msi.aggregate_time_fence_date),
11516: 1, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11512: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11513: 2, NVL(d2.PLANNED_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE),
11514: NVL(d2.SCHEDULE_SHIP_DATE,
11515: d2.USING_ASSEMBLY_DEMAND_DATE))) - msi.aggregate_time_fence_date),
11516: 1, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11517: 2, NVL(d2.PLANNED_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE),
11518: NVL(d2.SCHEDULE_SHIP_DATE,
11519: d2.USING_ASSEMBLY_DEMAND_DATE))),
11520: msi.aggregate_time_fence_date+1),
11517: 2, NVL(d2.PLANNED_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE),
11518: NVL(d2.SCHEDULE_SHIP_DATE,
11519: d2.USING_ASSEMBLY_DEMAND_DATE))),
11520: msi.aggregate_time_fence_date+1),
11521: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11522: 2, NVL(d2.PLANNED_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE),
11523: NVL(d2.SCHEDULE_SHIP_DATE,
11524: d2.USING_ASSEMBLY_DEMAND_DATE)))
11525: - msi.aggregate_time_fence_date),
11523: NVL(d2.SCHEDULE_SHIP_DATE,
11524: d2.USING_ASSEMBLY_DEMAND_DATE)))
11525: - msi.aggregate_time_fence_date),
11526: 1, msi.aggregate_time_fence_date,
11527: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11528: 2, NVL(d2.PLANNED_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE),
11529: NVL(d2.SCHEDULE_SHIP_DATE,
11530: d2.USING_ASSEMBLY_DEMAND_DATE)))))) demand_date,
11531: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11527: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11528: 2, NVL(d2.PLANNED_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE),
11529: NVL(d2.SCHEDULE_SHIP_DATE,
11530: d2.USING_ASSEMBLY_DEMAND_DATE)))))) demand_date,
11531: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11532: 2, NVL(d2.PLANNED_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE),
11533: NVL(d2.SCHEDULE_SHIP_DATE,
11534: d2.USING_ASSEMBLY_DEMAND_DATE))) original_demand_date,
11535: decode(d2.origination_type, 4, d2.daily_demand_rate,
11542: d2.demand_id parent_demand_id,
11543: 51 origination_type, -- ATP Bucketed Demand
11544: d2.origination_type original_origination_type,
11545: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11546: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11547: 2, NVL(d2.PLANNED_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE),
11548: NVL(d2.SCHEDULE_SHIP_DATE,
11549: d2.USING_ASSEMBLY_DEMAND_DATE)))
11550: - msi.aggregate_time_fence_date),
11549: d2.USING_ASSEMBLY_DEMAND_DATE)))
11550: - msi.aggregate_time_fence_date),
11551: 1, 1,
11552: NULL),
11553: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11554: 2, NVL(d2.PLANNED_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE),
11555: NVL(d2.SCHEDULE_SHIP_DATE,
11556: d2.USING_ASSEMBLY_DEMAND_DATE)))
11557: - msi.aggregate_time_fence_date),