[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;
896:
897: IF l_update_mem_bd = 'Y' THEN
898: update msc_alloc_demands
899: --bug3697365 added timestamp also
900: set demand_date = trunc(l_mem_bd_date) + MSC_ATP_PVT.G_END_OF_DAY,
901: allocated_quantity = l_mem_bd_qty,
902: demand_quantity = p_demand_qty,
903: pf_display_flag = l_mem_display_flag,
904: --bug3697365 added timestamp also
901: allocated_quantity = l_mem_bd_qty,
902: demand_quantity = p_demand_qty,
903: pf_display_flag = l_mem_display_flag,
904: --bug3697365 added timestamp also
905: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY
906: where parent_demand_id = p_parent_demand_id
907: --bug3693892 added trunc
908: and trunc(demand_date) <= p_atf_date
909: and plan_id = p_plan_id;
937:
938: IF l_update_pf_bd = 'Y' THEN
939: update msc_alloc_demands
940: --bug3697365 added timestamp also
941: set demand_date = trunc(l_pf_bd_date) + MSC_ATP_PVT.G_END_OF_DAY,
942: allocated_quantity = l_pf_bd_qty,
943: demand_quantity = p_demand_qty,
944: pf_display_flag = l_pf_display_flag,
945: --bug3697365 added timestamp also
942: allocated_quantity = l_pf_bd_qty,
943: demand_quantity = p_demand_qty,
944: pf_display_flag = l_pf_display_flag,
945: --bug3697365 added timestamp also
946: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY
947: where parent_demand_id = p_parent_demand_id
948: --bug3693892 added trunc
949: and trunc(demand_date) > p_atf_date
950: and plan_id = p_plan_id;
1058:
1059: IF l_update_mem_bd = 'Y' THEN
1060: update msc_alloc_demands
1061: --bug3697365 added timestamp also
1062: set demand_date = trunc(l_mem_bd_date) + MSC_ATP_PVT.G_END_OF_DAY,
1063: allocated_quantity = l_mem_bd_qty,
1064: demand_quantity = p_demand_qty,
1065: pf_display_flag = l_mem_display_flag,
1066: --bug3697365 added timestamp also
1063: allocated_quantity = l_mem_bd_qty,
1064: demand_quantity = p_demand_qty,
1065: pf_display_flag = l_mem_display_flag,
1066: --bug3697365 added timestamp also
1067: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY
1068: where parent_demand_id = p_parent_demand_id
1069: --bug3693892 added trunc
1070: and trunc(demand_date) <= p_atf_date
1071: and plan_id = p_plan_id;
1106:
1107: IF l_update_pf_bd = 'Y' THEN
1108: update msc_alloc_demands
1109: --bug3697365 added timestamp also
1110: set demand_date = trunc(l_pf_bd_date) + MSC_ATP_PVT.G_END_OF_DAY,
1111: allocated_quantity = l_pf_bd_qty,
1112: demand_quantity = p_demand_qty,
1113: pf_display_flag = l_pf_display_flag,
1114: --bug3697365 added timestamp also
1111: allocated_quantity = l_pf_bd_qty,
1112: demand_quantity = p_demand_qty,
1113: pf_display_flag = l_pf_display_flag,
1114: --bug3697365 added timestamp also
1115: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY
1116: where parent_demand_id = p_parent_demand_id
1117: --bug3693892 added trunc
1118: and trunc(demand_date) > p_atf_date
1119: and plan_id = p_plan_id;
1182:
1183: IF (p_old_demand_date < p_atf_date) and (p_new_demand_date <= p_atf_date) THEN
1184: update msc_alloc_demands
1185: --bug3697365 added timestamp also
1186: set demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1187: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY
1188: where parent_demand_id = p_parent_demand_id
1189: --bug3693892 added trunc
1190: and trunc(demand_date) <= p_atf_date
1183: IF (p_old_demand_date < p_atf_date) and (p_new_demand_date <= p_atf_date) THEN
1184: update msc_alloc_demands
1185: --bug3697365 added timestamp also
1186: set demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1187: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY
1188: where parent_demand_id = p_parent_demand_id
1189: --bug3693892 added trunc
1190: and trunc(demand_date) <= p_atf_date
1191: and plan_id = p_plan_id;
1191: and plan_id = p_plan_id;
1192: ELSIF (p_old_demand_date < p_atf_date) and (p_new_demand_date > p_atf_date) THEN
1193: update msc_alloc_demands
1194: --bug3693892 added trunc
1195: set demand_date = trunc(p_atf_date) + MSC_ATP_PVT.G_END_OF_DAY,
1196: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1197: Pf_Display_Flag = null
1198: where parent_demand_id = p_parent_demand_id
1199: --bug3693892 added trunc
1192: ELSIF (p_old_demand_date < p_atf_date) and (p_new_demand_date > p_atf_date) THEN
1193: update msc_alloc_demands
1194: --bug3693892 added trunc
1195: set demand_date = trunc(p_atf_date) + MSC_ATP_PVT.G_END_OF_DAY,
1196: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1197: Pf_Display_Flag = null
1198: where parent_demand_id = p_parent_demand_id
1199: --bug3693892 added trunc
1200: and trunc(demand_date) <= p_atf_date
1201: and plan_id = p_plan_id;
1202:
1203: update msc_alloc_demands
1204: --bug3693892 added trunc
1205: set demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1206: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1207: Pf_Display_Flag = 1
1208: where parent_demand_id = p_parent_demand_id
1209: --bug3693892 added trunc
1202:
1203: update msc_alloc_demands
1204: --bug3693892 added trunc
1205: set demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1206: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1207: Pf_Display_Flag = 1
1208: where parent_demand_id = p_parent_demand_id
1209: --bug3693892 added trunc
1210: and trunc(demand_date) > p_atf_date
1211: and plan_id = p_plan_id;
1212: ELSIF (p_old_demand_date > p_atf_date) and (p_new_demand_date > p_atf_date) THEN
1213: update msc_alloc_demands
1214: --bug3693892 added trunc
1215: set demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1216: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY
1217: where parent_demand_id = p_parent_demand_id
1218: --bug3693892 added trunc
1219: and trunc(demand_date) > p_atf_date
1212: ELSIF (p_old_demand_date > p_atf_date) and (p_new_demand_date > p_atf_date) THEN
1213: update msc_alloc_demands
1214: --bug3693892 added trunc
1215: set demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1216: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY
1217: where parent_demand_id = p_parent_demand_id
1218: --bug3693892 added trunc
1219: and trunc(demand_date) > p_atf_date
1220: and plan_id = p_plan_id;
1295: p_atp_rec.organization_id,
1296: p_atp_rec.instance_id,
1297: nvl(p_atp_rec.demand_class, -1),
1298: --bug3697365 added timestamp also
1299: TRUNC(p_bucketed_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1300: TRUNC(p_atp_rec.requested_ship_date) + MSC_ATP_PVT.G_END_OF_DAY,
1301: p_parent_demand_id,
1302: p_bucketed_demand_qty,
1303: p_atp_rec.quantity_ordered,
1296: p_atp_rec.instance_id,
1297: nvl(p_atp_rec.demand_class, -1),
1298: --bug3697365 added timestamp also
1299: TRUNC(p_bucketed_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1300: TRUNC(p_atp_rec.requested_ship_date) + MSC_ATP_PVT.G_END_OF_DAY,
1301: p_parent_demand_id,
1302: p_bucketed_demand_qty,
1303: p_atp_rec.quantity_ordered,
1304: 51, -- ATP Bucketed Demand
1307: p_atp_rec.order_number),
1308: p_atp_rec.demand_source_line,
1309: p_atp_rec.demand_source_type,--cmro
1310: p_display_flag,
1311: decode(p_atp_rec.origination_type, 6, MSC_ATP_PVT.G_PARTNER_ID,
1312: 30, MSC_ATP_PVT.G_PARTNER_ID,
1313: null),
1314: decode(p_atp_rec.origination_type, 6, MSC_ATP_PVT.G_PARTNER_SITE_ID,
1315: 30, MSC_ATP_PVT.G_PARTNER_SITE_ID,
1308: p_atp_rec.demand_source_line,
1309: p_atp_rec.demand_source_type,--cmro
1310: p_display_flag,
1311: decode(p_atp_rec.origination_type, 6, MSC_ATP_PVT.G_PARTNER_ID,
1312: 30, MSC_ATP_PVT.G_PARTNER_ID,
1313: null),
1314: decode(p_atp_rec.origination_type, 6, MSC_ATP_PVT.G_PARTNER_SITE_ID,
1315: 30, MSC_ATP_PVT.G_PARTNER_SITE_ID,
1316: null),
1310: p_display_flag,
1311: decode(p_atp_rec.origination_type, 6, MSC_ATP_PVT.G_PARTNER_ID,
1312: 30, MSC_ATP_PVT.G_PARTNER_ID,
1313: null),
1314: decode(p_atp_rec.origination_type, 6, MSC_ATP_PVT.G_PARTNER_SITE_ID,
1315: 30, MSC_ATP_PVT.G_PARTNER_SITE_ID,
1316: null),
1317: p_refresh_number,
1318: G_USER_ID,
1311: decode(p_atp_rec.origination_type, 6, MSC_ATP_PVT.G_PARTNER_ID,
1312: 30, MSC_ATP_PVT.G_PARTNER_ID,
1313: null),
1314: decode(p_atp_rec.origination_type, 6, MSC_ATP_PVT.G_PARTNER_SITE_ID,
1315: 30, MSC_ATP_PVT.G_PARTNER_SITE_ID,
1316: null),
1317: p_refresh_number,
1318: G_USER_ID,
1319: l_sysdate,
1473: G_USER_ID,
1474: l_sysdate,
1475: G_USER_ID,
1476: l_sysdate,
1477: NVL(MSC_ATP_PVT.G_PARTNER_ID,-1), -- Bug 3558125
1478: NVL(MSC_ATP_PVT.G_PARTNER_SITE_ID,-1) -- Bug 3558125
1479: );
1480: IF PG_DEBUG in ('Y', 'C') THEN
1481: msc_sch_wb.atp_debug('*********** Rollup Supply **********');
1474: l_sysdate,
1475: G_USER_ID,
1476: l_sysdate,
1477: NVL(MSC_ATP_PVT.G_PARTNER_ID,-1), -- Bug 3558125
1478: NVL(MSC_ATP_PVT.G_PARTNER_SITE_ID,-1) -- Bug 3558125
1479: );
1480: IF PG_DEBUG in ('Y', 'C') THEN
1481: msc_sch_wb.atp_debug('*********** Rollup Supply **********');
1482: msc_sch_wb.atp_debug('* Add rollup supply for member item ');
1524: G_USER_ID,
1525: l_sysdate,
1526: G_USER_ID,
1527: l_sysdate,
1528: NVL(MSC_ATP_PVT.G_PARTNER_ID,-1), -- Bug 3558125
1529: NVL(MSC_ATP_PVT.G_PARTNER_SITE_ID,-1) -- Bug 3558125
1530: );
1531: IF PG_DEBUG in ('Y', 'C') THEN
1532: msc_sch_wb.atp_debug('*********** Rollup Supply **********');
1525: l_sysdate,
1526: G_USER_ID,
1527: l_sysdate,
1528: NVL(MSC_ATP_PVT.G_PARTNER_ID,-1), -- Bug 3558125
1529: NVL(MSC_ATP_PVT.G_PARTNER_SITE_ID,-1) -- Bug 3558125
1530: );
1531: IF PG_DEBUG in ('Y', 'C') THEN
1532: msc_sch_wb.atp_debug('*********** Rollup Supply **********');
1533: msc_sch_wb.atp_debug('* Add rollup supply for family item ');
1716: msc_supplies_s.nextval, p_mem_stealing_quantity, 50, p_identifier,p_demand_source_type, 1,--cmro
1717: p_mem_stealing_quantity, p_mem_item_id, 46,
1718: G_USER_ID, l_sysdate, G_USER_ID, l_sysdate, p_stolen_demand_class, p_ato_model_line_id, p_refresh_number,
1719: --bug3684383
1720: p_order_number,MSC_ATP_PVT.G_PARTNER_ID,MSC_ATP_PVT.G_PARTNER_SITE_ID) -- For summary enhancement
1721: RETURNING parent_transaction_id INTO p_transaction_id; --bug3555084
1722:
1723: l_rows_proc := SQL%ROWCOUNT;
1724:
1741: p_transaction_id, -1 * p_mem_stealing_quantity, 50, p_identifier,p_demand_source_type, 1,---cmro
1742: -1 * p_mem_stealing_quantity, p_mem_item_id, 47,
1743: G_USER_ID, l_sysdate, G_USER_ID, l_sysdate, p_stealing_demand_class, p_ato_model_line_id, p_refresh_number,
1744: --bug3684383
1745: p_order_number,MSC_ATP_PVT.G_PARTNER_ID,MSC_ATP_PVT.G_PARTNER_SITE_ID); -- For summary enhancement
1746:
1747: l_rows_proc := l_rows_proc + SQL%ROWCOUNT;
1748: END IF;
1749:
1768: p_transaction_id, p_pf_stealing_quantity, 50, p_identifier,p_demand_source_type, 1,--cmro
1769: p_pf_stealing_quantity, p_mem_item_id, 46,
1770: G_USER_ID, l_sysdate, G_USER_ID, l_sysdate, p_stolen_demand_class, p_refresh_number, -- For summary enhancement
1771: --bug3684383
1772: p_order_number,MSC_ATP_PVT.G_PARTNER_ID,MSC_ATP_PVT.G_PARTNER_SITE_ID);
1773:
1774: l_rows_proc := l_rows_proc + SQL%ROWCOUNT;
1775:
1776: ELSE --bug3555084 start
1788: msc_supplies_s.nextval, p_pf_stealing_quantity, 50, p_identifier,p_demand_source_type, 1,--cmro
1789: p_pf_stealing_quantity, p_mem_item_id, 46,
1790: G_USER_ID, l_sysdate, G_USER_ID, l_sysdate, p_stolen_demand_class, p_refresh_number, -- For summary enhancement
1791: --bug3684383
1792: p_order_number,MSC_ATP_PVT.G_PARTNER_ID,MSC_ATP_PVT.G_PARTNER_SITE_ID)
1793:
1794: RETURNING parent_transaction_id INTO p_transaction_id;
1795:
1796: l_rows_proc := l_rows_proc + SQL%ROWCOUNT;
1815: p_transaction_id, -1 * p_pf_stealing_quantity, 50, p_identifier,p_demand_source_type, 1,--cmro
1816: -1 * p_pf_stealing_quantity, p_mem_item_id, 47,
1817: G_USER_ID, l_sysdate, G_USER_ID, l_sysdate, p_stealing_demand_class, p_refresh_number, -- For summary enhancement
1818: --bug3684383
1819: p_order_number,MSC_ATP_PVT.G_PARTNER_ID,MSC_ATP_PVT.G_PARTNER_SITE_ID);
1820:
1821: l_rows_proc := l_rows_proc + SQL%ROWCOUNT;
1822: END IF;
1823:
1871:
1872: -- Initializing API return code
1873: x_return_status := FND_API.G_RET_STS_SUCCESS;
1874:
1875: IF MSC_ATP_PVT.G_INV_CTP = 5 THEN
1876: -- ODS atp
1877: IF p_summary_flag = 'Y' THEN
1878: -- summary ODS atp
1879: Get_Mat_Avail_Pf_Ods_Summ(
2006:
2007: -- Initializing API return code
2008: x_return_status := FND_API.G_RET_STS_SUCCESS;
2009:
2010: IF MSC_ATP_PVT.G_INV_CTP = 5 THEN
2011: -- ODS atp
2012: Get_Mat_Avail_Pf_Ods_Dtls(
2013: p_item_id,
2014: p_request_item_id,
2267: AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_SALES_ORDERS,2,2,-1)
2268: AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_INTERNAL_ORDERS,2,8,-1)
2269: AND D.PRIMARY_UOM_QUANTITY > GREATEST(NVL(D.RESERVATION_QUANTITY,0),
2270: D.COMPLETED_QUANTITY)
2271: AND DECODE(MSC_ATP_PVT.G_APPS_VER,3,D.COMPLETED_QUANTITY,0) = 0 -- 2300767
2272: AND (D.SUBINVENTORY IS NULL OR D.SUBINVENTORY IN
2273: (SELECT S.SUB_INVENTORY_CODE
2274: FROM MSC_SUB_INVENTORIES S
2275: WHERE S.ORGANIZATION_ID=D.ORGANIZATION_ID
2844: AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_SALES_ORDERS,2,2,-1)
2845: AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_INTERNAL_ORDERS,2,8,-1)
2846: AND D.PRIMARY_UOM_QUANTITY > GREATEST(NVL(D.RESERVATION_QUANTITY,0),
2847: D.COMPLETED_QUANTITY)
2848: AND DECODE(MSC_ATP_PVT.G_APPS_VER,3,D.COMPLETED_QUANTITY,0) = 0 -- 2300767
2849: AND (D.SUBINVENTORY IS NULL OR D.SUBINVENTORY IN
2850: (SELECT S.SUB_INVENTORY_CODE
2851: FROM MSC_SUB_INVENTORIES S
2852: WHERE S.ORGANIZATION_ID=D.ORGANIZATION_ID
3323: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: p_instance_id: ' || p_instance_id);
3324: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: p_org_id: ' || p_org_id);
3325: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: p_demand_class: ' || p_demand_class);
3326: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: p_atf_date: ' || p_atf_date);
3327: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: G_ALLOCATION_METHOD: ' || MSC_ATP_PVT.G_ALLOCATION_METHOD);
3328: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: G_HIERARCHY_PROFILE: ' || MSC_AATP_PVT.G_HIERARCHY_PROFILE);
3329: END IF;
3330:
3331: -- Initializing API return code
3330:
3331: -- Initializing API return code
3332: x_return_status := FND_API.G_RET_STS_SUCCESS;
3333:
3334: IF MSC_ATP_PVT.G_ALLOCATION_METHOD = 1 THEN
3335: /* Demand priority allocated ATP
3336: Here we always use allocation rule from family inside/outside ATF
3337: */
3338: IF MSC_AATP_PVT.G_HIERARCHY_PROFILE = 1 THEN
3335: /* Demand priority allocated ATP
3336: Here we always use allocation rule from family inside/outside ATF
3337: */
3338: IF MSC_AATP_PVT.G_HIERARCHY_PROFILE = 1 THEN
3339: MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF := 'N';
3340: MSC_ATP_PVT.G_PF_RULE_OUTSIDE_ATF := 'Y';
3341: END IF;
3342: ELSE
3343: /* Rule based allocated ATP
3336: Here we always use allocation rule from family inside/outside ATF
3337: */
3338: IF MSC_AATP_PVT.G_HIERARCHY_PROFILE = 1 THEN
3339: MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF := 'N';
3340: MSC_ATP_PVT.G_PF_RULE_OUTSIDE_ATF := 'Y';
3341: END IF;
3342: ELSE
3343: /* Rule based allocated ATP
3344: Here we support following scenarios:
3367: AND level_id = -1
3368: AND rownum = 1;
3369:
3370: IF l_alloc_percent is not null THEN
3371: MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF := 'Y';
3372: ELSE
3373: MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF := 'N';
3374: END IF;
3375: EXCEPTION
3369:
3370: IF l_alloc_percent is not null THEN
3371: MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF := 'Y';
3372: ELSE
3373: MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF := 'N';
3374: END IF;
3375: EXCEPTION
3376: WHEN NO_DATA_FOUND THEN
3377: IF PG_DEBUG in ('Y', 'C') THEN
3376: WHEN NO_DATA_FOUND THEN
3377: IF PG_DEBUG in ('Y', 'C') THEN
3378: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: ' || 'Alloc Rule not found at member level');
3379: END IF;
3380: MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF := 'N';
3381: END;
3382: ELSE
3383: BEGIN
3384: SELECT allocation_percent
3391: AND level_id <> -1
3392: AND rownum = 1;
3393:
3394: IF l_alloc_percent is not null THEN
3395: MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF := 'Y';
3396: ELSE
3397: MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF := 'N';
3398: END IF;
3399: EXCEPTION
3393:
3394: IF l_alloc_percent is not null THEN
3395: MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF := 'Y';
3396: ELSE
3397: MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF := 'N';
3398: END IF;
3399: EXCEPTION
3400: WHEN NO_DATA_FOUND THEN
3401: IF PG_DEBUG in ('Y', 'C') THEN
3400: WHEN NO_DATA_FOUND THEN
3401: IF PG_DEBUG in ('Y', 'C') THEN
3402: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: ' || 'Alloc Rule not found for member within ATF');
3403: END IF;
3404: MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF := 'N';
3405: END;
3406: END IF;
3407:
3408: IF PG_DEBUG in ('Y', 'C') THEN
3407:
3408: IF PG_DEBUG in ('Y', 'C') THEN
3409: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: ' || 'Always use rule for family item outside ATF');
3410: END IF;
3411: MSC_ATP_PVT.G_PF_RULE_OUTSIDE_ATF := 'Y';
3412: END IF;
3413:
3414: IF PG_DEBUG in ('Y', 'C') THEN
3415: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: ' || 'G_MEM_RULE_WITHIN_ATF = ' || MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF);
3411: MSC_ATP_PVT.G_PF_RULE_OUTSIDE_ATF := 'Y';
3412: END IF;
3413:
3414: IF PG_DEBUG in ('Y', 'C') THEN
3415: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: ' || 'G_MEM_RULE_WITHIN_ATF = ' || MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF);
3416: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: ' || 'G_PF_RULE_OUTSIDE_ATF = ' || MSC_ATP_PVT.G_PF_RULE_OUTSIDE_ATF);
3417: END IF;
3418:
3419: EXCEPTION
3412: END IF;
3413:
3414: IF PG_DEBUG in ('Y', 'C') THEN
3415: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: ' || 'G_MEM_RULE_WITHIN_ATF = ' || MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF);
3416: msc_sch_wb.atp_debug('Set_Alloc_Rule_Variables: ' || 'G_PF_RULE_OUTSIDE_ATF = ' || MSC_ATP_PVT.G_PF_RULE_OUTSIDE_ATF);
3417: END IF;
3418:
3419: EXCEPTION
3420: WHEN OTHERS THEN
3485: DECODE(p_demand_class, '-1',
3486: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3487: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3488: 1, p_family_id,
3489: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3490: 'Y', AD.Original_Item_Id,
3491: p_family_id)), p_org_id,
3492: p_instance_id, trunc(AD.Demand_Date),
3493: p_level_id, AD.DEMAND_CLASS),
3496: DECODE(p_demand_class, '-1',
3497: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3498: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3499: 1, p_family_id,
3500: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3501: 'Y', AD.Original_Item_Id,
3502: p_family_id)), p_org_id,
3503: p_instance_id, trunc(AD.Demand_Date),
3504: p_level_id, AD.DEMAND_CLASS),
3507: DECODE(p_demand_class, '-1',
3508: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3509: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3510: 1, p_family_id,
3511: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3512: 'Y', AD.Original_Item_Id,
3513: p_family_id)), p_org_id,
3514: p_instance_id, trunc(AD.Demand_Date),
3515: p_level_id, AD.DEMAND_CLASS),
3519: DECODE(p_demand_class, '-1',
3520: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3521: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3522: 1, p_family_id,
3523: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3524: 'Y', AD.Original_Item_Id,
3525: p_family_id)), p_org_id,
3526: p_instance_id, trunc(AD.Demand_Date),
3527: p_level_id, AD.DEMAND_CLASS),
3530: DECODE(p_demand_class, '-1',
3531: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3532: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3533: 1, p_family_id,
3534: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3535: 'Y', AD.Original_Item_Id,
3536: p_family_id)), p_org_id,
3537: p_instance_id, trunc(AD.Demand_Date),
3538: p_level_id, AD.DEMAND_CLASS),
3541: DECODE(p_demand_class, '-1',
3542: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3543: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3544: 1, p_family_id,
3545: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3546: 'Y', AD.Original_Item_Id,
3547: p_family_id)), p_org_id,
3548: p_instance_id, trunc(AD.Demand_Date),
3549: p_level_id, AD.DEMAND_CLASS),
3552: DECODE(p_demand_class, '-1',
3553: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3554: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3555: 1, p_family_id,
3556: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3557: 'Y', AD.Original_Item_Id,
3558: p_family_id)), p_org_id,
3559: p_instance_id, trunc(AD.Demand_Date),
3560: p_level_id, AD.DEMAND_CLASS),
3565: 6, decode(AD.SOURCE_ORGANIZATION_ID,
3566: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3567: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3568: 1, p_family_id,
3569: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3570: 'Y', AD.Original_Item_Id,
3571: p_family_id)),
3572: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3573: p_level_id, NULL),
3573: p_level_id, NULL),
3574: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3575: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3576: 1, p_family_id,
3577: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3578: 'Y', AD.Original_Item_Id,
3579: p_family_id)),
3580: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3581: p_level_id, NULL),
3581: p_level_id, NULL),
3582: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3583: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3584: 1, p_family_id,
3585: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3586: 'Y', AD.Original_Item_Id,
3587: p_family_id)),
3588: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3589: p_level_id, NULL),
3591: 30, decode(AD.SOURCE_ORGANIZATION_ID,
3592: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3593: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3594: 1, p_family_id,
3595: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3596: 'Y', AD.Original_Item_Id,
3597: p_family_id)),
3598: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3599: p_level_id, NULL),
3599: p_level_id, NULL),
3600: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3601: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3602: 1, p_family_id,
3603: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3604: 'Y', AD.Original_Item_Id,
3605: p_family_id)),
3606: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3607: p_level_id, NULL),
3607: p_level_id, NULL),
3608: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3609: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3610: 1, p_family_id,
3611: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3612: 'Y', AD.Original_Item_Id,
3613: p_family_id)),
3614: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3615: p_level_id, NULL),
3617: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3618: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
3619: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3620: 1, p_family_id,
3621: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3622: 'Y', AD.Original_Item_Id,
3623: p_family_id)), p_org_id, p_instance_id,
3624: trunc(AD.Demand_Date),p_level_id, NULL)))),
3625: p_demand_class, 1,
3633: -23453, null,
3634: AD.SOURCE_ORGANIZATION_ID),
3635: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3636: 1, p_family_id,
3637: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3638: 'Y', AD.Original_Item_Id,
3639: p_family_id)),
3640: p_org_id,
3641: p_instance_id,
3647: DECODE(p_demand_class, '-1',
3648: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3649: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3650: 1, p_family_id,
3651: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3652: 'Y', AD.Original_Item_Id,
3653: p_family_id)), p_org_id,
3654: p_instance_id, trunc(AD.Demand_Date),
3655: p_level_id, AD.DEMAND_CLASS),
3658: DECODE(p_demand_class, '-1',
3659: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3660: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3661: 1, p_family_id,
3662: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3663: 'Y', AD.Original_Item_Id,
3664: p_family_id)), p_org_id,
3665: p_instance_id, trunc(AD.Demand_Date),
3666: p_level_id, AD.DEMAND_CLASS),
3669: DECODE(p_demand_class, '-1',
3670: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3671: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3672: 1, p_family_id,
3673: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3674: 'Y', AD.Original_Item_Id,
3675: p_family_id)), p_org_id,
3676: p_instance_id, trunc(AD.Demand_Date),
3677: p_level_id, AD.DEMAND_CLASS),
3682: DECODE(p_demand_class, '-1',
3683: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3684: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3685: 1, p_family_id,
3686: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3687: 'Y', AD.Original_Item_Id,
3688: p_family_id)), p_org_id,
3689: p_instance_id, trunc(AD.Demand_Date),
3690: p_level_id, AD.DEMAND_CLASS),
3693: DECODE(p_demand_class, '-1',
3694: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3695: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3696: 1, p_family_id,
3697: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3698: 'Y', AD.Original_Item_Id,
3699: p_family_id)), p_org_id,
3700: p_instance_id, trunc(AD.Demand_Date),
3701: p_level_id, AD.DEMAND_CLASS),
3704: DECODE(p_demand_class, '-1',
3705: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3706: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3707: 1, p_family_id,
3708: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3709: 'Y', AD.Original_Item_Id,
3710: p_family_id)), p_org_id,
3711: p_instance_id, trunc(AD.Demand_Date),
3712: p_level_id, AD.DEMAND_CLASS),
3716: DECODE(p_demand_class, '-1',
3717: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3718: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3719: 1, p_family_id,
3720: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3721: 'Y', AD.Original_Item_Id,
3722: p_family_id)), p_org_id,
3723: p_instance_id, trunc(AD.Demand_Date),
3724: p_level_id, AD.DEMAND_CLASS),
3729: 6, decode(AD.SOURCE_ORGANIZATION_ID,
3730: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3731: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3732: 1, p_family_id,
3733: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3734: 'Y', AD.Original_Item_Id,
3735: p_family_id)),
3736: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3737: p_level_id, NULL),
3737: p_level_id, NULL),
3738: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3739: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3740: 1, p_family_id,
3741: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3742: 'Y', AD.Original_Item_Id,
3743: p_family_id)),
3744: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3745: p_level_id, NULL),
3745: p_level_id, NULL),
3746: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3747: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3748: 1, p_family_id,
3749: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3750: 'Y', AD.Original_Item_Id,
3751: p_family_id)),
3752: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3753: p_level_id, NULL),
3755: 30, decode(AD.SOURCE_ORGANIZATION_ID,
3756: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3757: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3758: 1, p_family_id,
3759: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3760: 'Y', AD.Original_Item_Id,
3761: p_family_id)),
3762: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3763: p_level_id, NULL),
3763: p_level_id, NULL),
3764: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3765: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, 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)),
3770: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3771: p_level_id, NULL),
3771: p_level_id, NULL),
3772: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3773: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3774: 1, p_family_id,
3775: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3776: 'Y', AD.Original_Item_Id,
3777: p_family_id)),
3778: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3779: p_level_id, NULL),
3780: p_demand_class),
3781: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
3782: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3783: 1, p_family_id,
3784: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3785: 'Y', AD.Original_Item_Id,
3786: p_family_id)), p_org_id, p_instance_id,
3787: trunc(AD.Demand_Date),p_level_id, NULL)))),
3788: p_demand_class,
3807: null,
3808: null,
3809: Decode(sign(trunc(SA.Supply_Date) - p_atf_date),
3810: 1, p_family_id,
3811: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3812: 'Y', SA.Original_Item_Id,
3813: p_family_id)),
3814: p_org_id,
3815: p_instance_id,
3823: SA.CUSTOMER_ID,
3824: SA.SHIP_TO_SITE_ID,
3825: Decode(sign(trunc(SA.Supply_Date) - p_atf_date),
3826: 1, p_family_id,
3827: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3828: 'Y', SA.Original_Item_Id,
3829: p_family_id)),
3830: p_org_id,
3831: p_instance_id,
3837: /*NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
3838: p_instance_id,
3839: Decode(sign(trunc(SA.Supply_Date) - p_atf_date),
3840: 1, p_family_id,
3841: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3842: 'Y', SA.Original_Item_Id,
3843: p_family_id)),
3844: p_org_id,
3845: null,
3852: /*DECODE(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
3853: p_instance_id,
3854: Decode(sign(trunc(SA.Supply_Date) - p_atf_date),
3855: 1, p_family_id,
3856: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3857: 'Y', SA.Original_Item_Id,
3858: p_family_id)),
3859: p_org_id,
3860: null,
3876: TRUNC(SA.SUPPLY_DATE))
3877: AND TRUNC(SA.SUPPLY_DATE) < NVL(p_itf, TRUNC(SA.SUPPLY_DATE) + 1)
3878: --4365874
3879: --5220274 if the rule is assigned to family only then use family id.
3880: AND Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3881: 'N', p_family_id,
3882: SA.INVENTORY_ITEM_ID) = MIHM.INVENTORY_ITEM_ID(+)
3883: AND SA.SR_INSTANCE_ID = MIHM.SR_INSTANCE_ID (+)
3884: AND SA.ORGANIZATION_ID = MIHM.ORGANIZATION_ID (+)
3954: DECODE(p_demand_class, '-1',
3955: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3956: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3957: 1, p_family_id,
3958: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3959: 'Y', AD.Original_Item_Id,
3960: p_family_id)), p_org_id,
3961: p_instance_id, trunc(AD.Demand_Date),
3962: p_level_id, AD.DEMAND_CLASS),
3965: DECODE(p_demand_class, '-1',
3966: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3967: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3968: 1, p_family_id,
3969: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3970: 'Y', AD.Original_Item_Id,
3971: p_family_id)), p_org_id,
3972: p_instance_id, trunc(AD.Demand_Date),
3973: p_level_id, AD.DEMAND_CLASS),
3976: DECODE(p_demand_class, '-1',
3977: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3978: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3979: 1, p_family_id,
3980: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3981: 'Y', AD.Original_Item_Id,
3982: p_family_id)), p_org_id,
3983: p_instance_id, trunc(AD.Demand_Date),
3984: p_level_id, AD.DEMAND_CLASS),
3988: DECODE(p_demand_class, '-1',
3989: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3990: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3991: 1, p_family_id,
3992: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3993: 'Y', AD.Original_Item_Id,
3994: p_family_id)), p_org_id,
3995: p_instance_id, trunc(AD.Demand_Date),
3996: p_level_id, AD.DEMAND_CLASS),
3999: DECODE(p_demand_class, '-1',
4000: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4001: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4002: 1, p_family_id,
4003: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4004: 'Y', AD.Original_Item_Id,
4005: p_family_id)), p_org_id,
4006: p_instance_id, trunc(AD.Demand_Date),
4007: p_level_id, AD.DEMAND_CLASS),
4010: DECODE(p_demand_class, '-1',
4011: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4012: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4013: 1, p_family_id,
4014: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4015: 'Y', AD.Original_Item_Id,
4016: p_family_id)), p_org_id,
4017: p_instance_id, trunc(AD.Demand_Date),
4018: p_level_id, AD.DEMAND_CLASS),
4021: DECODE(p_demand_class, '-1',
4022: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4023: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4024: 1, p_family_id,
4025: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4026: 'Y', AD.Original_Item_Id,
4027: p_family_id)), p_org_id,
4028: p_instance_id, trunc(AD.Demand_Date),
4029: p_level_id, AD.DEMAND_CLASS),
4034: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4035: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4036: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4037: 1, p_family_id,
4038: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4039: 'Y', AD.Original_Item_Id,
4040: p_family_id)),
4041: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4042: p_level_id, NULL),
4042: p_level_id, NULL),
4043: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4044: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4045: 1, p_family_id,
4046: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4047: 'Y', AD.Original_Item_Id,
4048: p_family_id)),
4049: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4050: p_level_id, NULL),
4050: p_level_id, NULL),
4051: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4052: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4053: 1, p_family_id,
4054: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4055: 'Y', AD.Original_Item_Id,
4056: p_family_id)),
4057: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4058: p_level_id, NULL),
4060: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4061: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4062: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4063: 1, p_family_id,
4064: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4065: 'Y', AD.Original_Item_Id,
4066: p_family_id)),
4067: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4068: p_level_id, NULL),
4068: p_level_id, NULL),
4069: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4070: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4071: 1, p_family_id,
4072: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4073: 'Y', AD.Original_Item_Id,
4074: p_family_id)),
4075: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4076: p_level_id, NULL),
4076: p_level_id, NULL),
4077: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4078: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4079: 1, p_family_id,
4080: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4081: 'Y', AD.Original_Item_Id,
4082: p_family_id)),
4083: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4084: p_level_id, NULL),
4086: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4087: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
4088: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4089: 1, p_family_id,
4090: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4091: 'Y', AD.Original_Item_Id,
4092: p_family_id)), p_org_id, p_instance_id,
4093: trunc(AD.Demand_Date),p_level_id, NULL)))),
4094: p_demand_class, 1,
4101: -23453, null,
4102: AD.SOURCE_ORGANIZATION_ID),
4103: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4104: 1, p_family_id,
4105: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4106: 'Y', AD.Original_Item_Id,
4107: p_family_id)),
4108: p_org_id,
4109: p_instance_id,
4115: DECODE(p_demand_class, '-1',
4116: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4117: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4118: 1, p_family_id,
4119: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4120: 'Y', AD.Original_Item_Id,
4121: p_family_id)), p_org_id,
4122: p_instance_id, trunc(AD.Demand_Date),
4123: p_level_id, AD.DEMAND_CLASS),
4126: DECODE(p_demand_class, '-1',
4127: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4128: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4129: 1, p_family_id,
4130: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4131: 'Y', AD.Original_Item_Id,
4132: p_family_id)), p_org_id,
4133: p_instance_id, trunc(AD.Demand_Date),
4134: p_level_id, AD.DEMAND_CLASS),
4137: DECODE(p_demand_class, '-1',
4138: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4139: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4140: 1, p_family_id,
4141: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4142: 'Y', AD.Original_Item_Id,
4143: p_family_id)), p_org_id,
4144: p_instance_id, trunc(AD.Demand_Date),
4145: p_level_id, AD.DEMAND_CLASS),
4150: DECODE(p_demand_class, '-1',
4151: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4152: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4153: 1, p_family_id,
4154: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4155: 'Y', AD.Original_Item_Id,
4156: p_family_id)), p_org_id,
4157: p_instance_id, trunc(AD.Demand_Date),
4158: p_level_id, AD.DEMAND_CLASS),
4161: DECODE(p_demand_class, '-1',
4162: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4163: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4164: 1, p_family_id,
4165: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4166: 'Y', AD.Original_Item_Id,
4167: p_family_id)), p_org_id,
4168: p_instance_id, trunc(AD.Demand_Date),
4169: p_level_id, AD.DEMAND_CLASS),
4172: DECODE(p_demand_class, '-1',
4173: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4174: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4175: 1, p_family_id,
4176: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4177: 'Y', AD.Original_Item_Id,
4178: p_family_id)), p_org_id,
4179: p_instance_id, trunc(AD.Demand_Date),
4180: p_level_id, AD.DEMAND_CLASS),
4184: DECODE(p_demand_class, '-1',
4185: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4186: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4187: 1, p_family_id,
4188: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4189: 'Y', AD.Original_Item_Id,
4190: p_family_id)), p_org_id,
4191: p_instance_id, trunc(AD.Demand_Date),
4192: p_level_id, AD.DEMAND_CLASS),
4197: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4198: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4199: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4200: 1, p_family_id,
4201: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4202: 'Y', AD.Original_Item_Id,
4203: p_family_id)),
4204: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4205: p_level_id, NULL),
4205: p_level_id, NULL),
4206: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4207: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4208: 1, p_family_id,
4209: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4210: 'Y', AD.Original_Item_Id,
4211: p_family_id)),
4212: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4213: p_level_id, NULL),
4213: p_level_id, NULL),
4214: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4215: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4216: 1, p_family_id,
4217: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4218: 'Y', AD.Original_Item_Id,
4219: p_family_id)),
4220: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4221: p_level_id, NULL),
4223: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4224: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4225: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4226: 1, p_family_id,
4227: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4228: 'Y', AD.Original_Item_Id,
4229: p_family_id)),
4230: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4231: p_level_id, NULL),
4231: p_level_id, NULL),
4232: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4233: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4234: 1, p_family_id,
4235: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4236: 'Y', AD.Original_Item_Id,
4237: p_family_id)),
4238: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4239: p_level_id, NULL),
4239: p_level_id, NULL),
4240: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4241: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4242: 1, p_family_id,
4243: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4244: 'Y', AD.Original_Item_Id,
4245: p_family_id)),
4246: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4247: p_level_id, NULL),
4249: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4250: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
4251: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4252: 1, p_family_id,
4253: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4254: 'Y', AD.Original_Item_Id,
4255: p_family_id)), p_org_id, p_instance_id,
4256: trunc(AD.Demand_Date),p_level_id, NULL)))),
4257: p_demand_class,
4277: null,
4278: null,
4279: Decode(sign(trunc(SA.Supply_Date) - p_atf_date),
4280: 1, p_family_id,
4281: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4282: 'Y', SA.Original_Item_Id,
4283: p_family_id)),
4284: p_org_id,
4285: p_instance_id,
4293: sa.CUSTOMER_ID,
4294: sa.SHIP_TO_SITE_ID,
4295: Decode(sign(trunc(sa.Supply_Date) - p_atf_date),
4296: 1, p_family_id,
4297: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4298: 'Y', sa.Original_Item_Id,
4299: p_family_id)),
4300: p_org_id,
4301: p_instance_id,
4307: /*NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
4308: p_instance_id,
4309: Decode(sign(trunc(sa.Supply_Date) - p_atf_date),
4310: 1, p_family_id,
4311: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4312: 'Y', sa.Original_Item_Id,
4313: p_family_id)),
4314: p_org_id,
4315: null,
4322: /*DECODE(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
4323: p_instance_id,
4324: Decode(sign(trunc(sa.Supply_Date) - p_atf_date),
4325: 1, p_family_id,
4326: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4327: 'Y', sa.Original_Item_Id,
4328: p_family_id)),
4329: p_org_id,
4330: null,
4346: TRUNC(SA.SUPPLY_DATE))
4347: AND TRUNC(SA.SUPPLY_DATE) < NVL(p_itf, TRUNC(SA.SUPPLY_DATE) + 1)
4348: --4365874
4349: --5220274 if the rule is assigned to family only then use family id.
4350: AND Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4351: 'N', p_family_id,
4352: sa.INVENTORY_ITEM_ID) = MIHM.INVENTORY_ITEM_ID(+)
4353: AND sa.SR_INSTANCE_ID = MIHM.SR_INSTANCE_ID (+)
4354: AND sa.ORGANIZATION_ID = MIHM.ORGANIZATION_ID (+)
4508: DECODE(p_demand_class, '-1',
4509: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4510: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4511: 1, p_family_id,
4512: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4513: 'Y', AD.Original_Item_Id,
4514: p_family_id)), p_org_id,
4515: p_instance_id, trunc(AD.Demand_Date),
4516: 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),
4542: DECODE(p_demand_class, '-1',
4543: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4544: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4545: 1, p_family_id,
4546: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4547: 'Y', AD.Original_Item_Id,
4548: p_family_id)), p_org_id,
4549: p_instance_id, trunc(AD.Demand_Date),
4550: p_level_id, AD.DEMAND_CLASS),
4553: DECODE(p_demand_class, '-1',
4554: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4555: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4556: 1, p_family_id,
4557: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4558: 'Y', AD.Original_Item_Id,
4559: p_family_id)), p_org_id,
4560: p_instance_id, trunc(AD.Demand_Date),
4561: p_level_id, AD.DEMAND_CLASS),
4564: DECODE(p_demand_class, '-1',
4565: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4566: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4567: 1, p_family_id,
4568: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4569: 'Y', AD.Original_Item_Id,
4570: p_family_id)), p_org_id,
4571: p_instance_id, trunc(AD.Demand_Date),
4572: p_level_id, AD.DEMAND_CLASS),
4575: DECODE(p_demand_class, '-1',
4576: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4577: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4578: 1, p_family_id,
4579: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4580: 'Y', AD.Original_Item_Id,
4581: p_family_id)), p_org_id,
4582: p_instance_id, trunc(AD.Demand_Date),
4583: p_level_id, AD.DEMAND_CLASS),
4588: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4589: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4590: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4591: 1, p_family_id,
4592: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4593: 'Y', AD.Original_Item_Id,
4594: p_family_id)),
4595: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4596: p_level_id, NULL),
4596: p_level_id, NULL),
4597: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4598: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4599: 1, p_family_id,
4600: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4601: 'Y', AD.Original_Item_Id,
4602: p_family_id)),
4603: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4604: p_level_id, NULL),
4604: p_level_id, NULL),
4605: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4606: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4607: 1, p_family_id,
4608: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4609: 'Y', AD.Original_Item_Id,
4610: p_family_id)),
4611: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4612: p_level_id, NULL),
4614: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4615: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4616: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4617: 1, p_family_id,
4618: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4619: 'Y', AD.Original_Item_Id,
4620: p_family_id)),
4621: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4622: p_level_id, NULL),
4622: p_level_id, NULL),
4623: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4624: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4625: 1, p_family_id,
4626: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4627: 'Y', AD.Original_Item_Id,
4628: p_family_id)),
4629: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4630: p_level_id, NULL),
4630: p_level_id, NULL),
4631: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4632: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4633: 1, p_family_id,
4634: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4635: 'Y', AD.Original_Item_Id,
4636: p_family_id)),
4637: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4638: p_level_id, NULL),
4640: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4641: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
4642: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4643: 1, p_family_id,
4644: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4645: 'Y', AD.Original_Item_Id,
4646: p_family_id)), p_org_id, p_instance_id,
4647: trunc(AD.Demand_Date),p_level_id, NULL)))),
4648: p_demand_class, 1,
4656: -23453, null,
4657: AD.SOURCE_ORGANIZATION_ID),
4658: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4659: 1, p_family_id,
4660: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4661: 'Y', AD.Original_Item_Id,
4662: p_family_id)),
4663: p_org_id,
4664: p_instance_id,
4670: DECODE(p_demand_class, '-1',
4671: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4672: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4673: 1, p_family_id,
4674: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4675: 'Y', AD.Original_Item_Id,
4676: p_family_id)), p_org_id,
4677: p_instance_id, trunc(AD.Demand_Date),
4678: p_level_id, AD.DEMAND_CLASS),
4681: DECODE(p_demand_class, '-1',
4682: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4683: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4684: 1, p_family_id,
4685: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4686: 'Y', AD.Original_Item_Id,
4687: p_family_id)), p_org_id,
4688: p_instance_id, trunc(AD.Demand_Date),
4689: p_level_id, AD.DEMAND_CLASS),
4692: DECODE(p_demand_class, '-1',
4693: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4694: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4695: 1, p_family_id,
4696: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4697: 'Y', AD.Original_Item_Id,
4698: p_family_id)), p_org_id,
4699: p_instance_id, trunc(AD.Demand_Date),
4700: p_level_id, AD.DEMAND_CLASS),
4705: DECODE(p_demand_class, '-1',
4706: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4707: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4708: 1, p_family_id,
4709: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4710: 'Y', AD.Original_Item_Id,
4711: p_family_id)), p_org_id,
4712: p_instance_id, trunc(AD.Demand_Date),
4713: 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),
4727: DECODE(p_demand_class, '-1',
4728: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4729: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4730: 1, p_family_id,
4731: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4732: 'Y', AD.Original_Item_Id,
4733: p_family_id)), p_org_id,
4734: p_instance_id, trunc(AD.Demand_Date),
4735: p_level_id, AD.DEMAND_CLASS),
4739: DECODE(p_demand_class, '-1',
4740: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4741: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4742: 1, p_family_id,
4743: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4744: 'Y', AD.Original_Item_Id,
4745: p_family_id)), p_org_id,
4746: p_instance_id, trunc(AD.Demand_Date),
4747: p_level_id, AD.DEMAND_CLASS),
4752: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4753: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4754: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4755: 1, p_family_id,
4756: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4757: 'Y', AD.Original_Item_Id,
4758: p_family_id)),
4759: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4760: p_level_id, NULL),
4760: p_level_id, NULL),
4761: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4762: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4763: 1, p_family_id,
4764: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4765: 'Y', AD.Original_Item_Id,
4766: p_family_id)),
4767: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4768: p_level_id, NULL),
4768: p_level_id, NULL),
4769: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4770: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4771: 1, p_family_id,
4772: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4773: 'Y', AD.Original_Item_Id,
4774: p_family_id)),
4775: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4776: p_level_id, NULL),
4778: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4779: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4780: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4781: 1, p_family_id,
4782: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4783: 'Y', AD.Original_Item_Id,
4784: p_family_id)),
4785: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4786: p_level_id, NULL),
4786: p_level_id, NULL),
4787: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4788: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4789: 1, p_family_id,
4790: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4791: 'Y', AD.Original_Item_Id,
4792: p_family_id)),
4793: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4794: p_level_id, NULL),
4794: p_level_id, NULL),
4795: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4796: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4797: 1, p_family_id,
4798: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4799: 'Y', AD.Original_Item_Id,
4800: p_family_id)),
4801: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4802: p_level_id, NULL),
4804: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4805: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
4806: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4807: 1, p_family_id,
4808: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4809: 'Y', AD.Original_Item_Id,
4810: p_family_id)), p_org_id, p_instance_id,
4811: trunc(AD.Demand_Date),p_level_id, NULL)))),
4812: p_demand_class,
4833: DECODE(p_demand_class, '-1',
4834: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4835: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4836: 1, p_family_id,
4837: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4838: 'Y', AD.Original_Item_Id,
4839: p_family_id)), p_org_id,
4840: p_instance_id, trunc(AD.Demand_Date),
4841: 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),
4867: DECODE(p_demand_class, '-1',
4868: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4869: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4870: 1, p_family_id,
4871: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4872: 'Y', AD.Original_Item_Id,
4873: p_family_id)), p_org_id,
4874: p_instance_id, trunc(AD.Demand_Date),
4875: p_level_id, AD.DEMAND_CLASS),
4878: DECODE(p_demand_class, '-1',
4879: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4880: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4881: 1, p_family_id,
4882: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4883: 'Y', AD.Original_Item_Id,
4884: p_family_id)), p_org_id,
4885: p_instance_id, trunc(AD.Demand_Date),
4886: p_level_id, AD.DEMAND_CLASS),
4889: DECODE(p_demand_class, '-1',
4890: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4891: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4892: 1, p_family_id,
4893: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4894: 'Y', AD.Original_Item_Id,
4895: p_family_id)), p_org_id,
4896: p_instance_id, trunc(AD.Demand_Date),
4897: p_level_id, AD.DEMAND_CLASS),
4900: DECODE(p_demand_class, '-1',
4901: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4902: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4903: 1, p_family_id,
4904: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4905: 'Y', AD.Original_Item_Id,
4906: p_family_id)), p_org_id,
4907: p_instance_id, trunc(AD.Demand_Date),
4908: p_level_id, AD.DEMAND_CLASS),
4913: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4914: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4915: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4916: 1, p_family_id,
4917: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4918: 'Y', AD.Original_Item_Id,
4919: p_family_id)),
4920: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4921: p_level_id, NULL),
4921: p_level_id, NULL),
4922: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4923: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4924: 1, p_family_id,
4925: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4926: 'Y', AD.Original_Item_Id,
4927: p_family_id)),
4928: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4929: p_level_id, NULL),
4929: p_level_id, NULL),
4930: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4931: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4932: 1, p_family_id,
4933: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4934: 'Y', AD.Original_Item_Id,
4935: p_family_id)),
4936: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4937: p_level_id, NULL),
4939: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4940: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4941: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4942: 1, p_family_id,
4943: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4944: 'Y', AD.Original_Item_Id,
4945: p_family_id)),
4946: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4947: p_level_id, NULL),
4947: p_level_id, NULL),
4948: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4949: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4950: 1, p_family_id,
4951: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4952: 'Y', AD.Original_Item_Id,
4953: p_family_id)),
4954: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4955: p_level_id, NULL),
4955: p_level_id, NULL),
4956: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4957: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4958: 1, p_family_id,
4959: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4960: 'Y', AD.Original_Item_Id,
4961: p_family_id)),
4962: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4963: p_level_id, NULL),
4965: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4966: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
4967: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4968: 1, p_family_id,
4969: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4970: 'Y', AD.Original_Item_Id,
4971: p_family_id)), p_org_id, p_instance_id,
4972: trunc(AD.Demand_Date),p_level_id, NULL)))),
4973: p_demand_class, 1,
4979: -23453, null,
4980: AD.SOURCE_ORGANIZATION_ID),
4981: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4982: 1, p_family_id,
4983: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4984: 'Y', AD.Original_Item_Id,
4985: p_family_id)),
4986: p_org_id,
4987: p_instance_id,
4993: DECODE(p_demand_class, '-1',
4994: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4995: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4996: 1, p_family_id,
4997: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4998: 'Y', AD.Original_Item_Id,
4999: p_family_id)), p_org_id,
5000: p_instance_id, trunc(AD.Demand_Date),
5001: p_level_id, AD.DEMAND_CLASS),
5004: DECODE(p_demand_class, '-1',
5005: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5006: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5007: 1, p_family_id,
5008: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5009: 'Y', AD.Original_Item_Id,
5010: p_family_id)), p_org_id,
5011: p_instance_id, trunc(AD.Demand_Date),
5012: p_level_id, AD.DEMAND_CLASS),
5015: DECODE(p_demand_class, '-1',
5016: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5017: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5018: 1, p_family_id,
5019: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5020: 'Y', AD.Original_Item_Id,
5021: p_family_id)), p_org_id,
5022: p_instance_id, trunc(AD.Demand_Date),
5023: p_level_id, AD.DEMAND_CLASS),
5028: DECODE(p_demand_class, '-1',
5029: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5030: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5031: 1, p_family_id,
5032: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5033: 'Y', AD.Original_Item_Id,
5034: p_family_id)), p_org_id,
5035: p_instance_id, trunc(AD.Demand_Date),
5036: 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),
5050: DECODE(p_demand_class, '-1',
5051: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5052: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5053: 1, p_family_id,
5054: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5055: 'Y', AD.Original_Item_Id,
5056: p_family_id)), p_org_id,
5057: p_instance_id, trunc(AD.Demand_Date),
5058: p_level_id, AD.DEMAND_CLASS),
5062: DECODE(p_demand_class, '-1',
5063: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5064: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5065: 1, p_family_id,
5066: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5067: 'Y', AD.Original_Item_Id,
5068: p_family_id)), p_org_id,
5069: p_instance_id, trunc(AD.Demand_Date),
5070: p_level_id, AD.DEMAND_CLASS),
5075: 6, decode(AD.SOURCE_ORGANIZATION_ID,
5076: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5077: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5078: 1, p_family_id,
5079: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5080: 'Y', AD.Original_Item_Id,
5081: p_family_id)),
5082: p_org_id, p_instance_id, trunc(AD.Demand_Date),
5083: p_level_id, NULL),
5083: p_level_id, NULL),
5084: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5085: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5086: 1, p_family_id,
5087: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5088: 'Y', AD.Original_Item_Id,
5089: p_family_id)),
5090: p_org_id, p_instance_id, trunc(AD.Demand_Date),
5091: p_level_id, NULL),
5091: p_level_id, NULL),
5092: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5093: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5094: 1, p_family_id,
5095: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5096: 'Y', AD.Original_Item_Id,
5097: p_family_id)),
5098: p_org_id, p_instance_id, trunc(AD.Demand_Date),
5099: p_level_id, NULL),
5101: 30, decode(AD.SOURCE_ORGANIZATION_ID,
5102: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5103: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5104: 1, p_family_id,
5105: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5106: 'Y', AD.Original_Item_Id,
5107: p_family_id)),
5108: p_org_id, p_instance_id, trunc(AD.Demand_Date),
5109: p_level_id, NULL),
5109: p_level_id, NULL),
5110: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5111: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5112: 1, p_family_id,
5113: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5114: 'Y', AD.Original_Item_Id,
5115: p_family_id)),
5116: p_org_id, p_instance_id, trunc(AD.Demand_Date),
5117: p_level_id, NULL),
5117: p_level_id, NULL),
5118: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5119: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5120: 1, p_family_id,
5121: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5122: 'Y', AD.Original_Item_Id,
5123: p_family_id)),
5124: p_org_id, p_instance_id, trunc(AD.Demand_Date),
5125: p_level_id, NULL),
5127: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5128: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
5129: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5130: 1, p_family_id,
5131: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5132: 'Y', AD.Original_Item_Id,
5133: p_family_id)), p_org_id, p_instance_id,
5134: trunc(AD.Demand_Date),p_level_id, NULL)))),
5135: p_demand_class,
5200: null,
5201: null,
5202: Decode(sign(trunc(CSA.Supply_Date) - p_atf_date),
5203: 1, p_family_id,
5204: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5205: 'Y', CSA.Original_Item_Id,
5206: p_family_id)),
5207: p_org_id,
5208: p_instance_id,
5216: CSA.CUSTOMER_ID,
5217: CSA.SHIP_TO_SITE_ID,
5218: Decode(sign(trunc(CSA.Supply_Date) - p_atf_date),
5219: 1, p_family_id,
5220: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5221: 'Y', CSA.Original_Item_Id,
5222: p_family_id)),
5223: p_org_id,
5224: p_instance_id,
5230: /*NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
5231: p_instance_id,
5232: Decode(sign(trunc(CSA.Supply_Date) - p_atf_date),
5233: 1, p_family_id,
5234: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5235: 'Y', CSA.Original_Item_Id,
5236: p_family_id)),
5237: p_org_id,
5238: null,
5245: /*DECODE(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
5246: p_instance_id,
5247: Decode(sign(trunc(CSA.Supply_Date) - p_atf_date),
5248: 1, p_family_id,
5249: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5250: 'Y', CSA.Original_Item_Id,
5251: p_family_id)),
5252: p_org_id,
5253: null,
5322: MSC_ITEM_HIERARCHY_MV MIHM
5323: WHERE
5324: --4365873
5325: --5220274 if the rule is assigned to family only then use family id.
5326: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5327: 'N', p_family_id,
5328: CSA.INVENTORY_ITEM_ID) = MIHM.INVENTORY_ITEM_ID(+)
5329: AND CSA.SR_INSTANCE_ID = MIHM.SR_INSTANCE_ID (+)
5330: AND CSA.ORGANIZATION_ID = MIHM.ORGANIZATION_ID (+)
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),
6228: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6229: null, null,
6230: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6231: 1, p_family_id,
6232: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6233: 'Y', AD.Original_Item_Id,
6234: p_family_id)),
6235: p_org_id,
6236: p_instance_id, trunc(AD.DEMAND_DATE),
6241: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6242: null, null,
6243: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6244: 1, p_family_id,
6245: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6246: 'Y', AD.Original_Item_Id,
6247: p_family_id)),
6248: p_org_id,
6249: 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),
6281: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6282: null, null,
6283: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6284: 1, p_family_id,
6285: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6286: 'Y', AD.Original_Item_Id,
6287: p_family_id)),
6288: p_org_id,
6289: p_instance_id, trunc(AD.DEMAND_DATE),
6294: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6295: null, null,
6296: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6297: 1, p_family_id,
6298: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6299: 'Y', AD.Original_Item_Id,
6300: p_family_id)),
6301: p_org_id,
6302: p_instance_id, trunc(AD.DEMAND_DATE),
6311: -23453, null,
6312: AD.SOURCE_ORGANIZATION_ID),
6313: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6314: 1, p_family_id,
6315: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6316: 'Y', AD.Original_Item_Id,
6317: p_family_id)),
6318: p_org_id,
6319: p_instance_id,
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),
6338: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6339: null, null,
6340: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6341: 1, p_family_id,
6342: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6343: 'Y', AD.Original_Item_Id,
6344: p_family_id)),
6345: p_org_id,
6346: p_instance_id, trunc(AD.DEMAND_DATE),
6351: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6352: null, null,
6353: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6354: 1, p_family_id,
6355: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6356: 'Y', AD.Original_Item_Id,
6357: p_family_id)),
6358: p_org_id,
6359: 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),
6391: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6392: null, null,
6393: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6394: 1, p_family_id,
6395: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6396: 'Y', AD.Original_Item_Id,
6397: p_family_id)),
6398: p_org_id,
6399: p_instance_id, trunc(AD.DEMAND_DATE),
6404: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6405: null, null,
6406: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6407: 1, p_family_id,
6408: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6409: 'Y', AD.Original_Item_Id,
6410: p_family_id)),
6411: p_org_id,
6412: 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),
6456: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6457: null, null,
6458: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6459: 1, p_family_id,
6460: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6461: 'Y', AD.Original_Item_Id,
6462: p_family_id)),
6463: p_org_id,
6464: p_instance_id, trunc(AD.DEMAND_DATE),
6469: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6470: null, null,
6471: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6472: 1, p_family_id,
6473: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6474: 'Y', AD.Original_Item_Id,
6475: p_family_id)),
6476: p_org_id,
6477: 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),
6509: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6510: null, null,
6511: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6512: 1, p_family_id,
6513: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6514: 'Y', AD.Original_Item_Id,
6515: p_family_id)),
6516: p_org_id,
6517: p_instance_id, trunc(AD.DEMAND_DATE),
6522: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6523: null, null,
6524: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6525: 1, p_family_id,
6526: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6527: 'Y', AD.Original_Item_Id,
6528: p_family_id)),
6529: p_org_id,
6530: p_instance_id, trunc(AD.DEMAND_DATE),
6539: -23453, null,
6540: AD.SOURCE_ORGANIZATION_ID),
6541: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6542: 1, p_family_id,
6543: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6544: 'Y', AD.Original_Item_Id,
6545: p_family_id)),
6546: p_org_id,
6547: p_instance_id,
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),
6566: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6567: null, null,
6568: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6569: 1, p_family_id,
6570: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6571: 'Y', AD.Original_Item_Id,
6572: p_family_id)),
6573: p_org_id,
6574: p_instance_id, trunc(AD.DEMAND_DATE),
6579: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6580: null, null,
6581: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6582: 1, p_family_id,
6583: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6584: 'Y', AD.Original_Item_Id,
6585: p_family_id)),
6586: p_org_id,
6587: 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),
6619: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6620: null, null,
6621: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6622: 1, p_family_id,
6623: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6624: 'Y', AD.Original_Item_Id,
6625: p_family_id)),
6626: p_org_id,
6627: p_instance_id, trunc(AD.DEMAND_DATE),
6632: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6633: null, null,
6634: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6635: 1, p_family_id,
6636: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6637: 'Y', AD.Original_Item_Id,
6638: p_family_id)),
6639: p_org_id,
6640: p_instance_id, trunc(AD.DEMAND_DATE),
6693: null,
6694: null,
6695: Decode(sign(trunc(SA.Supply_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6696: 1, p_family_id,
6697: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6698: 'Y', SA.Original_Item_Id,
6699: p_family_id)),
6700: p_org_id,
6701: p_instance_id,
6709: NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
6710: p_instance_id,
6711: Decode(sign(trunc(SA.Supply_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6712: 1, p_family_id,
6713: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6714: 'Y', SA.Original_Item_Id,
6715: p_family_id)),
6716: p_org_id,
6717: null,
6722: DECODE(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
6723: p_instance_id,
6724: Decode(sign(trunc(SA.Supply_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6725: 1, p_family_id,
6726: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6727: 'Y', SA.Original_Item_Id,
6728: p_family_id)),
6729: p_org_id,
6730: null,
6814: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6815: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6816: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6817: 1, p_family_id,
6818: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6819: 'Y', AD.Original_Item_Id,
6820: p_family_id)),
6821: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6822: p_level_id, NULL),
6823: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6824: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6825: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6826: 1, p_family_id,
6827: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6828: 'Y', AD.Original_Item_Id,
6829: p_family_id)),
6830: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6831: p_level_id, NULL),
6832: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6833: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6834: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6835: 1, p_family_id,
6836: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6837: 'Y', AD.Original_Item_Id,
6838: p_family_id)),
6839: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6840: p_level_id, NULL),
6843: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6844: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6845: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6846: 1, p_family_id,
6847: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6848: 'Y', AD.Original_Item_Id,
6849: p_family_id)),
6850: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6851: p_level_id, NULL),
6852: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6853: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6854: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6855: 1, p_family_id,
6856: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6857: 'Y', AD.Original_Item_Id,
6858: p_family_id)),
6859: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6860: p_level_id, NULL),
6861: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6862: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_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, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6869: p_level_id, NULL),
6871: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6872: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6873: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6874: 1, p_family_id,
6875: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6876: 'Y', AD.Original_Item_Id,
6877: p_family_id)),
6878: p_org_id, p_instance_id,
6879: TRUNC(AD.ORIGINAL_DEMAND_DATE),
6887: -23453, null,
6888: AD.SOURCE_ORGANIZATION_ID),
6889: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6890: 1, p_family_id,
6891: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6892: 'Y', AD.Original_Item_Id,
6893: p_family_id)),
6894: p_org_id,
6895: p_instance_id,
6901: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6902: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6903: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6904: 1, p_family_id,
6905: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6906: 'Y', AD.Original_Item_Id,
6907: p_family_id)),
6908: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6909: p_level_id, NULL),
6910: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6911: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6912: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6913: 1, p_family_id,
6914: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6915: 'Y', AD.Original_Item_Id,
6916: p_family_id)),
6917: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6918: p_level_id, NULL),
6919: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6920: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6921: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6922: 1, p_family_id,
6923: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6924: 'Y', AD.Original_Item_Id,
6925: p_family_id)),
6926: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6927: p_level_id, NULL),
6930: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6931: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6932: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6933: 1, p_family_id,
6934: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6935: 'Y', AD.Original_Item_Id,
6936: p_family_id)),
6937: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6938: p_level_id, NULL),
6939: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6940: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6941: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6942: 1, p_family_id,
6943: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6944: 'Y', AD.Original_Item_Id,
6945: p_family_id)),
6946: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6947: p_level_id, NULL),
6948: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6949: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6950: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6951: 1, p_family_id,
6952: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6953: 'Y', AD.Original_Item_Id,
6954: p_family_id)),
6955: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6956: p_level_id, NULL),
6958: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6959: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6960: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6961: 1, p_family_id,
6962: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6963: 'Y', AD.Original_Item_Id,
6964: p_family_id)),
6965: p_org_id, p_instance_id,
6966: TRUNC(AD.ORIGINAL_DEMAND_DATE),
7000: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7001: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7002: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7003: 1, p_family_id,
7004: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7005: 'Y', AD.Original_Item_Id,
7006: p_family_id)),
7007: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7008: p_level_id, NULL),
7009: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7010: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7011: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7012: 1, p_family_id,
7013: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7014: 'Y', AD.Original_Item_Id,
7015: p_family_id)),
7016: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7017: p_level_id, NULL),
7018: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7019: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7020: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7021: 1, p_family_id,
7022: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7023: 'Y', AD.Original_Item_Id,
7024: p_family_id)),
7025: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7026: p_level_id, NULL),
7029: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7030: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7031: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7032: 1, p_family_id,
7033: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7034: 'Y', AD.Original_Item_Id,
7035: p_family_id)),
7036: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7037: p_level_id, NULL),
7038: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7039: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7040: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7041: 1, p_family_id,
7042: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7043: 'Y', AD.Original_Item_Id,
7044: p_family_id)),
7045: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7046: p_level_id, NULL),
7047: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7048: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_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, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7055: p_level_id, NULL),
7057: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7058: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7059: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7060: 1, p_family_id,
7061: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7062: 'Y', AD.Original_Item_Id,
7063: p_family_id)),
7064: p_org_id, p_instance_id,
7065: TRUNC(AD.ORIGINAL_DEMAND_DATE),
7073: -23453, null,
7074: AD.SOURCE_ORGANIZATION_ID),
7075: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7076: 1, p_family_id,
7077: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7078: 'Y', AD.Original_Item_Id,
7079: p_family_id)),
7080: p_org_id,
7081: p_instance_id,
7087: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7088: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7089: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7090: 1, p_family_id,
7091: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7092: 'Y', AD.Original_Item_Id,
7093: p_family_id)),
7094: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7095: p_level_id, NULL),
7096: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7097: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7098: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7099: 1, p_family_id,
7100: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7101: 'Y', AD.Original_Item_Id,
7102: p_family_id)),
7103: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7104: p_level_id, NULL),
7105: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7106: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7107: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7108: 1, p_family_id,
7109: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7110: 'Y', AD.Original_Item_Id,
7111: p_family_id)),
7112: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7113: p_level_id, NULL),
7116: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7117: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7118: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7119: 1, p_family_id,
7120: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7121: 'Y', AD.Original_Item_Id,
7122: p_family_id)),
7123: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7124: p_level_id, NULL),
7125: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7126: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7127: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7128: 1, p_family_id,
7129: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7130: 'Y', AD.Original_Item_Id,
7131: p_family_id)),
7132: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7133: p_level_id, NULL),
7134: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7135: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7136: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7137: 1, p_family_id,
7138: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7139: 'Y', AD.Original_Item_Id,
7140: p_family_id)),
7141: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7142: p_level_id, NULL),
7144: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7145: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7146: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7147: 1, p_family_id,
7148: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7149: 'Y', AD.Original_Item_Id,
7150: p_family_id)),
7151: p_org_id, p_instance_id,
7152: TRUNC(AD.ORIGINAL_DEMAND_DATE),
7201: SA.CUSTOMER_ID,
7202: SA.SHIP_TO_SITE_ID,
7203: Decode(sign(trunc(SA.Supply_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7204: 1, p_family_id,
7205: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7206: 'Y', SA.Original_Item_Id,
7207: p_family_id)),
7208: p_org_id,
7209: p_instance_id,
7216: NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
7217: p_instance_id,
7218: Decode(sign(trunc(SA.Supply_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7219: 1, p_family_id,
7220: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7221: 'Y', SA.Original_Item_Id,
7222: p_family_id)),
7223: p_org_id,
7224: null,
7228: DECODE(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
7229: p_instance_id,
7230: Decode(sign(trunc(SA.Supply_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7231: 1, p_family_id,
7232: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7233: 'Y', SA.Original_Item_Id,
7234: p_family_id)),
7235: p_org_id,
7236: null,
8792: p_demand_class,
8793: p_parent_plan_id --bug3510475
8794: );
8795:
8796: IF (MSC_ATP_PVT.G_PLAN_INFO_REC.plan_id = -300) THEN
8797: IF PG_DEBUG in ('Y', 'C') THEN
8798: msc_sch_wb.atp_debug('Get_PF_Plan_Info: ' || 'ATP Downtime');
8799: END IF;
8800:
8798: msc_sch_wb.atp_debug('Get_PF_Plan_Info: ' || 'ATP Downtime');
8799: END IF;
8800:
8801: x_return_status := FND_API.G_RET_STS_ERROR;
8802: MSC_ATP_PVT.G_DOWNTIME_HIT := 'Y';
8803: p_error_code := MSC_ATP_PVT.PLAN_DOWN_TIME;
8804: RAISE NO_DATA_FOUND;
8805: END IF;
8806:
8799: END IF;
8800:
8801: x_return_status := FND_API.G_RET_STS_ERROR;
8802: MSC_ATP_PVT.G_DOWNTIME_HIT := 'Y';
8803: p_error_code := MSC_ATP_PVT.PLAN_DOWN_TIME;
8804: RAISE NO_DATA_FOUND;
8805: END IF;
8806:
8807: IF PG_DEBUG in ('Y', 'C') THEN
8804: RAISE NO_DATA_FOUND;
8805: END IF;
8806:
8807: IF PG_DEBUG in ('Y', 'C') THEN
8808: 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);
8809: END IF;
8810:
8811: -- ATP4drp begin
8812: IF NVL(MSC_ATP_PVT.G_PLAN_INFO_REC.plan_type, 1) = 5 THEN
8808: 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);
8809: END IF;
8810:
8811: -- ATP4drp begin
8812: IF NVL(MSC_ATP_PVT.G_PLAN_INFO_REC.plan_type, 1) = 5 THEN
8813: IF PG_DEBUG in ('Y', 'C') THEN
8814: msc_sch_wb.atp_debug('----- ATP4drp Specific Debug Messages -----');
8815: msc_sch_wb.atp_debug('Get_PF_Plan_Info: ' || 'PF and Allocated ATP not applicable for DRP plans');
8816: msc_sch_wb.atp_debug('Get_PF_Plan_Info: ' || 'ATF date will not be obtained.');
8823: p_atf_date := MSC_ATP_PF.Get_Atf_Date(
8824: p_instance_id,
8825: p_member_item_id,
8826: p_org_id,
8827: MSC_ATP_PVT.G_PLAN_INFO_REC.plan_id
8828: );
8829:
8830: IF PG_DEBUG in ('Y', 'C') THEN
8831: msc_sch_wb.atp_debug('Get_PF_Plan_Info: ' || 'ATF Date = '||p_atf_date);
8850: p_demand_class,
8851: p_parent_plan_id --bug3510475
8852: );
8853:
8854: IF (MSC_ATP_PVT.G_PLAN_INFO_REC.plan_id = -300) THEN
8855: IF PG_DEBUG in ('Y', 'C') THEN
8856: msc_sch_wb.atp_debug('Get_PF_Plan_Info: ' || 'ATP Downtime');
8857: END IF;
8858:
8856: msc_sch_wb.atp_debug('Get_PF_Plan_Info: ' || 'ATP Downtime');
8857: END IF;
8858:
8859: x_return_status := FND_API.G_RET_STS_ERROR;
8860: MSC_ATP_PVT.G_DOWNTIME_HIT := 'Y';
8861: p_error_code := MSC_ATP_PVT.PLAN_DOWN_TIME;
8862: RAISE NO_DATA_FOUND;
8863: END IF;
8864: END IF;
8857: END IF;
8858:
8859: x_return_status := FND_API.G_RET_STS_ERROR;
8860: MSC_ATP_PVT.G_DOWNTIME_HIT := 'Y';
8861: p_error_code := MSC_ATP_PVT.PLAN_DOWN_TIME;
8862: RAISE NO_DATA_FOUND;
8863: END IF;
8864: END IF;
8865: ELSE
10155: msc_util.msc_log('Prepare_Demands_Stmt: ' || 'p_excess_supply_by_dc : ' || p_excess_supply_by_dc);
10156: msc_util.msc_log('Prepare_Demands_Stmt: ' || 'p_temp_table : ' || p_temp_table);
10157: msc_util.msc_log('Prepare_Demands_Stmt: ' || 'p_alloc_temp_table : ' || p_alloc_temp_table);
10158: msc_util.msc_log('Prepare_Demands_Stmt: ' || 'p_parallel_degree : ' || p_parallel_degree);
10159: msc_util.msc_log('Prepare_Demands_Stmt: ' || 'MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF : ' || MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF);
10160: END IF;
10161:
10162: -- Initializing API return code
10163: x_return_status := FND_API.G_RET_STS_SUCCESS;
10241: NULL, :def_num,
10242: mat.demand_class) demand_class,
10243: decode(msi.aggregate_time_fence_date,
10244: -- Bug 3574164. DMD_SATISFIED_TIME changed to PLANNED_SHIP_DATE.
10245: NULL, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10246: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10247: NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))),
10248: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
10249: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10245: NULL, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10246: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10247: NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))),
10248: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
10249: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10250: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10251: NVL(D.SCHEDULE_SHIP_DATE,
10252: D.USING_ASSEMBLY_DEMAND_DATE)))
10253: - msi.aggregate_time_fence_date),
10250: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10251: NVL(D.SCHEDULE_SHIP_DATE,
10252: D.USING_ASSEMBLY_DEMAND_DATE)))
10253: - msi.aggregate_time_fence_date),
10254: 1, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10255: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10256: NVL(D.SCHEDULE_SHIP_DATE,
10257: D.USING_ASSEMBLY_DEMAND_DATE))),
10258: msi.aggregate_time_fence_date+1),
10255: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10256: NVL(D.SCHEDULE_SHIP_DATE,
10257: D.USING_ASSEMBLY_DEMAND_DATE))),
10258: msi.aggregate_time_fence_date+1),
10259: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10260: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10261: NVL(D.SCHEDULE_SHIP_DATE,
10262: D.USING_ASSEMBLY_DEMAND_DATE)))
10263: - msi.aggregate_time_fence_date),
10261: NVL(D.SCHEDULE_SHIP_DATE,
10262: D.USING_ASSEMBLY_DEMAND_DATE)))
10263: - msi.aggregate_time_fence_date),
10264: 1, msi.aggregate_time_fence_date,
10265: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10266: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10267: NVL(D.SCHEDULE_SHIP_DATE,
10268: D.USING_ASSEMBLY_DEMAND_DATE)))))) demand_date,
10269: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10265: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10266: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10267: NVL(D.SCHEDULE_SHIP_DATE,
10268: D.USING_ASSEMBLY_DEMAND_DATE)))))) demand_date,
10269: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10270: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10271: NVL(D.SCHEDULE_SHIP_DATE,
10272: D.USING_ASSEMBLY_DEMAND_DATE))) original_demand_date,
10273: decode(d.origination_type, 4, d.daily_demand_rate,
10283: d.origination_type original_origination_type,
10284: decode(msi.aggregate_time_fence_date,
10285: NULL, NULL,
10286: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
10287: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10288: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10289: NVL(D.SCHEDULE_SHIP_DATE,
10290: D.USING_ASSEMBLY_DEMAND_DATE)))
10291: - msi.aggregate_time_fence_date),
10290: D.USING_ASSEMBLY_DEMAND_DATE)))
10291: - msi.aggregate_time_fence_date),
10292: 1, 1,
10293: NULL),
10294: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10295: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10296: NVL(D.SCHEDULE_SHIP_DATE,
10297: D.USING_ASSEMBLY_DEMAND_DATE)))
10298: - msi.aggregate_time_fence_date),
10405: peg1.organization_id,
10406: peg1.sr_instance_id,
10407: d.demand_class demand_class,
10408: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
10409: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10410: -- Bug 3574164. DMD_SATISFIED_TIME changed to PLANNED_SHIP_DATE.
10411: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10412: NVL(D.SCHEDULE_SHIP_DATE,
10413: D.USING_ASSEMBLY_DEMAND_DATE)))
10411: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10412: NVL(D.SCHEDULE_SHIP_DATE,
10413: D.USING_ASSEMBLY_DEMAND_DATE)))
10414: - msi.aggregate_time_fence_date),
10415: 1, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10416: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10417: NVL(D.SCHEDULE_SHIP_DATE,
10418: D.USING_ASSEMBLY_DEMAND_DATE))),
10419: msi.aggregate_time_fence_date+1),
10416: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10417: NVL(D.SCHEDULE_SHIP_DATE,
10418: D.USING_ASSEMBLY_DEMAND_DATE))),
10419: msi.aggregate_time_fence_date+1),
10420: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10421: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10422: NVL(D.SCHEDULE_SHIP_DATE,
10423: D.USING_ASSEMBLY_DEMAND_DATE)))
10424: - msi.aggregate_time_fence_date),
10422: NVL(D.SCHEDULE_SHIP_DATE,
10423: D.USING_ASSEMBLY_DEMAND_DATE)))
10424: - msi.aggregate_time_fence_date),
10425: 1, msi.aggregate_time_fence_date,
10426: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10427: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10428: NVL(D.SCHEDULE_SHIP_DATE,
10429: D.USING_ASSEMBLY_DEMAND_DATE))))) demand_date,
10430: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10426: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10427: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10428: NVL(D.SCHEDULE_SHIP_DATE,
10429: D.USING_ASSEMBLY_DEMAND_DATE))))) demand_date,
10430: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10431: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10432: NVL(D.SCHEDULE_SHIP_DATE,
10433: D.USING_ASSEMBLY_DEMAND_DATE))) original_demand_date,
10434: MIN(decode(d.origination_type, 4, d.daily_demand_rate,
10437: d.demand_id,
10438: 51 origination_type, -- ATP Bucketed Demand
10439: d.origination_type original_origination_type,
10440: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
10441: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10442: -- Bug 3574164. DMD_SATISFIED_TIME changed to PLANNED_SHIP_DATE.
10443: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10444: NVL(D.SCHEDULE_SHIP_DATE,
10445: D.USING_ASSEMBLY_DEMAND_DATE)))
10445: D.USING_ASSEMBLY_DEMAND_DATE)))
10446: - msi.aggregate_time_fence_date),
10447: 1, 1,
10448: NULL),
10449: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10450: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10451: NVL(D.SCHEDULE_SHIP_DATE,
10452: D.USING_ASSEMBLY_DEMAND_DATE)))
10453: - msi.aggregate_time_fence_date),
10496: peg1.organization_id,
10497: peg1.sr_instance_id,
10498: d.demand_class,
10499: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
10500: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10501: -- Bug 3574164. DMD_SATISFIED_TIME changed to PLANNED_SHIP_DATE.
10502: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10503: NVL(D.SCHEDULE_SHIP_DATE,
10504: D.USING_ASSEMBLY_DEMAND_DATE)))
10502: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10503: NVL(D.SCHEDULE_SHIP_DATE,
10504: D.USING_ASSEMBLY_DEMAND_DATE)))
10505: - msi.aggregate_time_fence_date),
10506: 1, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10507: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10508: NVL(D.SCHEDULE_SHIP_DATE,
10509: D.USING_ASSEMBLY_DEMAND_DATE))),
10510: msi.aggregate_time_fence_date+1),
10507: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10508: NVL(D.SCHEDULE_SHIP_DATE,
10509: D.USING_ASSEMBLY_DEMAND_DATE))),
10510: msi.aggregate_time_fence_date+1),
10511: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10512: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10513: NVL(D.SCHEDULE_SHIP_DATE,
10514: D.USING_ASSEMBLY_DEMAND_DATE))) - msi.aggregate_time_fence_date),
10515: 1, msi.aggregate_time_fence_date,
10512: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10513: NVL(D.SCHEDULE_SHIP_DATE,
10514: D.USING_ASSEMBLY_DEMAND_DATE))) - msi.aggregate_time_fence_date),
10515: 1, msi.aggregate_time_fence_date,
10516: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10517: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10518: NVL(D.SCHEDULE_SHIP_DATE,
10519: D.USING_ASSEMBLY_DEMAND_DATE))))),
10520: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10516: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10517: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10518: NVL(D.SCHEDULE_SHIP_DATE,
10519: D.USING_ASSEMBLY_DEMAND_DATE))))),
10520: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10521: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10522: NVL(D.SCHEDULE_SHIP_DATE,
10523: D.USING_ASSEMBLY_DEMAND_DATE))),
10524: d.demand_id,
10524: d.demand_id,
10525: 51,
10526: d.origination_type,
10527: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
10528: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10529: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10530: NVL(D.SCHEDULE_SHIP_DATE,
10531: D.USING_ASSEMBLY_DEMAND_DATE)))
10532: - msi.aggregate_time_fence_date),
10531: D.USING_ASSEMBLY_DEMAND_DATE)))
10532: - msi.aggregate_time_fence_date),
10533: 1, 1,
10534: NULL),
10535: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10536: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10537: NVL(D.SCHEDULE_SHIP_DATE,
10538: D.USING_ASSEMBLY_DEMAND_DATE)))
10539: - msi.aggregate_time_fence_date),
10919: peg1.organization_id,
10920: peg1.sr_instance_id,
10921: NVL(s.demand_class, :def_num) demand_class,
10922: decode(msi.aggregate_time_fence_date,
10923: NULL, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10924: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10925: NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))),
10926: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
10927: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10923: NULL, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10924: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10925: NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))),
10926: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
10927: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10928: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10929: NVL(D.SCHEDULE_SHIP_DATE,
10930: D.USING_ASSEMBLY_DEMAND_DATE)))
10931: - msi.aggregate_time_fence_date),
10928: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10929: NVL(D.SCHEDULE_SHIP_DATE,
10930: D.USING_ASSEMBLY_DEMAND_DATE)))
10931: - msi.aggregate_time_fence_date),
10932: 1, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10933: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10934: NVL(D.SCHEDULE_SHIP_DATE,
10935: D.USING_ASSEMBLY_DEMAND_DATE))),
10936: msi.aggregate_time_fence_date+1),
10933: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10934: NVL(D.SCHEDULE_SHIP_DATE,
10935: D.USING_ASSEMBLY_DEMAND_DATE))),
10936: msi.aggregate_time_fence_date+1),
10937: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10938: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10939: NVL(D.SCHEDULE_SHIP_DATE,
10940: D.USING_ASSEMBLY_DEMAND_DATE)))
10941: - msi.aggregate_time_fence_date),
10939: NVL(D.SCHEDULE_SHIP_DATE,
10940: D.USING_ASSEMBLY_DEMAND_DATE)))
10941: - msi.aggregate_time_fence_date),
10942: 1, msi.aggregate_time_fence_date,
10943: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10944: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10945: NVL(D.SCHEDULE_SHIP_DATE,
10946: D.USING_ASSEMBLY_DEMAND_DATE)))))) demand_date,
10947: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10943: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10944: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10945: NVL(D.SCHEDULE_SHIP_DATE,
10946: D.USING_ASSEMBLY_DEMAND_DATE)))))) demand_date,
10947: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10948: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10949: NVL(D.SCHEDULE_SHIP_DATE,
10950: D.USING_ASSEMBLY_DEMAND_DATE))) original_demand_date,
10951: decode(d.origination_type, 4, d.daily_demand_rate,
10961: d.origination_type original_origination_type,
10962: decode(msi.aggregate_time_fence_date,
10963: NULL, NULL,
10964: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
10965: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10966: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10967: NVL(D.SCHEDULE_SHIP_DATE,
10968: D.USING_ASSEMBLY_DEMAND_DATE)))
10969: - msi.aggregate_time_fence_date),
10968: D.USING_ASSEMBLY_DEMAND_DATE)))
10969: - msi.aggregate_time_fence_date),
10970: 1, 1,
10971: NULL),
10972: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
10973: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
10974: NVL(D.SCHEDULE_SHIP_DATE,
10975: D.USING_ASSEMBLY_DEMAND_DATE)))
10976: - msi.aggregate_time_fence_date),
11084: peg1.organization_id,
11085: peg1.sr_instance_id,
11086: :def_num demand_class,
11087: decode(msi.aggregate_time_fence_date,
11088: NULL,trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11089: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11090: NVL(D.SCHEDULE_SHIP_DATE,
11091: D.USING_ASSEMBLY_DEMAND_DATE))),
11092: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11089: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11090: NVL(D.SCHEDULE_SHIP_DATE,
11091: D.USING_ASSEMBLY_DEMAND_DATE))),
11092: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11093: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11094: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11095: NVL(D.SCHEDULE_SHIP_DATE,
11096: D.USING_ASSEMBLY_DEMAND_DATE)))
11097: - msi.aggregate_time_fence_date),
11094: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11095: NVL(D.SCHEDULE_SHIP_DATE,
11096: D.USING_ASSEMBLY_DEMAND_DATE)))
11097: - msi.aggregate_time_fence_date),
11098: 1,trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11099: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11100: NVL(D.SCHEDULE_SHIP_DATE,
11101: D.USING_ASSEMBLY_DEMAND_DATE))),
11102: msi.aggregate_time_fence_date+1),
11099: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11100: NVL(D.SCHEDULE_SHIP_DATE,
11101: D.USING_ASSEMBLY_DEMAND_DATE))),
11102: msi.aggregate_time_fence_date+1),
11103: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11104: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11105: NVL(D.SCHEDULE_SHIP_DATE,
11106: D.USING_ASSEMBLY_DEMAND_DATE)))
11107: - msi.aggregate_time_fence_date),
11105: NVL(D.SCHEDULE_SHIP_DATE,
11106: D.USING_ASSEMBLY_DEMAND_DATE)))
11107: - msi.aggregate_time_fence_date),
11108: 1, msi.aggregate_time_fence_date,
11109: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11110: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11111: NVL(D.SCHEDULE_SHIP_DATE,
11112: D.USING_ASSEMBLY_DEMAND_DATE)))))) demand_date,
11113: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11109: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11110: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11111: NVL(D.SCHEDULE_SHIP_DATE,
11112: D.USING_ASSEMBLY_DEMAND_DATE)))))) demand_date,
11113: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11114: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11115: NVL(D.SCHEDULE_SHIP_DATE,
11116: D.USING_ASSEMBLY_DEMAND_DATE))) original_demand_date,
11117: MIN(decode(d.origination_type, 4, d.daily_demand_rate,
11127: d.origination_type original_origination_type,
11128: decode(msi.aggregate_time_fence_date,
11129: NULL, NULL,
11130: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11131: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11132: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11133: NVL(D.SCHEDULE_SHIP_DATE,
11134: D.USING_ASSEMBLY_DEMAND_DATE)))
11135: - msi.aggregate_time_fence_date),
11134: D.USING_ASSEMBLY_DEMAND_DATE)))
11135: - msi.aggregate_time_fence_date),
11136: 1, 1,
11137: NULL),
11138: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11139: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11140: NVL(D.SCHEDULE_SHIP_DATE,
11141: D.USING_ASSEMBLY_DEMAND_DATE)))
11142: - msi.aggregate_time_fence_date),
11184: peg1.organization_id,
11185: peg1.sr_instance_id,
11186: :def_num,
11187: decode(msi.aggregate_time_fence_date,
11188: NULL, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11189: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11190: NVL(D.SCHEDULE_SHIP_DATE,
11191: D.USING_ASSEMBLY_DEMAND_DATE))),
11192: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11189: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11190: NVL(D.SCHEDULE_SHIP_DATE,
11191: D.USING_ASSEMBLY_DEMAND_DATE))),
11192: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11193: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11194: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11195: NVL(D.SCHEDULE_SHIP_DATE,
11196: D.USING_ASSEMBLY_DEMAND_DATE)))
11197: - msi.aggregate_time_fence_date),
11194: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11195: NVL(D.SCHEDULE_SHIP_DATE,
11196: D.USING_ASSEMBLY_DEMAND_DATE)))
11197: - msi.aggregate_time_fence_date),
11198: 1, 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+1),
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+1),
11203: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11204: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11205: NVL(D.SCHEDULE_SHIP_DATE,
11206: D.USING_ASSEMBLY_DEMAND_DATE)))
11207: - msi.aggregate_time_fence_date),
11205: NVL(D.SCHEDULE_SHIP_DATE,
11206: D.USING_ASSEMBLY_DEMAND_DATE)))
11207: - msi.aggregate_time_fence_date),
11208: 1, msi.aggregate_time_fence_date,
11209: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11210: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11211: NVL(D.SCHEDULE_SHIP_DATE,
11212: D.USING_ASSEMBLY_DEMAND_DATE)))))),
11213: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11209: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11210: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11211: NVL(D.SCHEDULE_SHIP_DATE,
11212: D.USING_ASSEMBLY_DEMAND_DATE)))))),
11213: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11214: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11215: NVL(D.SCHEDULE_SHIP_DATE,
11216: D.USING_ASSEMBLY_DEMAND_DATE))),
11217: d.demand_id,
11220: d.origination_type,
11221: decode(msi.aggregate_time_fence_date,
11222: NULL, NULL,
11223: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11224: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11225: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11226: NVL(D.SCHEDULE_SHIP_DATE,
11227: D.USING_ASSEMBLY_DEMAND_DATE)))
11228: - msi.aggregate_time_fence_date),
11227: D.USING_ASSEMBLY_DEMAND_DATE)))
11228: - msi.aggregate_time_fence_date),
11229: 1, 1,
11230: NULL),
11231: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11232: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11233: NVL(D.SCHEDULE_SHIP_DATE,
11234: D.USING_ASSEMBLY_DEMAND_DATE)))
11235: - msi.aggregate_time_fence_date),
11284: peg1.organization_id,
11285: peg1.sr_instance_id,
11286: d.demand_class demand_class,
11287: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11288: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11289: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11290: NVL(D.SCHEDULE_SHIP_DATE,
11291: D.USING_ASSEMBLY_DEMAND_DATE)))
11292: - msi.aggregate_time_fence_date),
11289: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11290: NVL(D.SCHEDULE_SHIP_DATE,
11291: D.USING_ASSEMBLY_DEMAND_DATE)))
11292: - msi.aggregate_time_fence_date),
11293: 1, 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+1),
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+1),
11298: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11299: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11300: NVL(D.SCHEDULE_SHIP_DATE,
11301: D.USING_ASSEMBLY_DEMAND_DATE)))
11302: - msi.aggregate_time_fence_date),
11300: NVL(D.SCHEDULE_SHIP_DATE,
11301: D.USING_ASSEMBLY_DEMAND_DATE)))
11302: - msi.aggregate_time_fence_date),
11303: 1, msi.aggregate_time_fence_date,
11304: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11305: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11306: NVL(D.SCHEDULE_SHIP_DATE,
11307: D.USING_ASSEMBLY_DEMAND_DATE))))) demand_date,
11308: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11304: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11305: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11306: NVL(D.SCHEDULE_SHIP_DATE,
11307: D.USING_ASSEMBLY_DEMAND_DATE))))) demand_date,
11308: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11309: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11310: NVL(D.SCHEDULE_SHIP_DATE,
11311: D.USING_ASSEMBLY_DEMAND_DATE))) original_demand_date,
11312: MIN(decode(d.origination_type, 4, d.daily_demand_rate,
11315: d.demand_id,
11316: 51 origination_type, -- ATP Bucketed Demand
11317: d.origination_type original_origination_type,
11318: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11319: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11320: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11321: NVL(D.SCHEDULE_SHIP_DATE,
11322: D.USING_ASSEMBLY_DEMAND_DATE)))
11323: - msi.aggregate_time_fence_date),
11322: D.USING_ASSEMBLY_DEMAND_DATE)))
11323: - msi.aggregate_time_fence_date),
11324: 1, 1,
11325: NULL),
11326: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11327: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11328: NVL(D.SCHEDULE_SHIP_DATE,
11329: D.USING_ASSEMBLY_DEMAND_DATE)))
11330: - msi.aggregate_time_fence_date),
11368: peg1.organization_id,
11369: peg1.sr_instance_id,
11370: d.demand_class,
11371: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11372: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11373: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11374: NVL(D.SCHEDULE_SHIP_DATE,
11375: D.USING_ASSEMBLY_DEMAND_DATE)))
11376: - msi.aggregate_time_fence_date),
11373: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11374: NVL(D.SCHEDULE_SHIP_DATE,
11375: D.USING_ASSEMBLY_DEMAND_DATE)))
11376: - msi.aggregate_time_fence_date),
11377: 1, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11378: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11379: NVL(D.SCHEDULE_SHIP_DATE,
11380: D.USING_ASSEMBLY_DEMAND_DATE))),
11381: msi.aggregate_time_fence_date+1),
11378: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11379: NVL(D.SCHEDULE_SHIP_DATE,
11380: D.USING_ASSEMBLY_DEMAND_DATE))),
11381: msi.aggregate_time_fence_date+1),
11382: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11383: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11384: NVL(D.SCHEDULE_SHIP_DATE,
11385: D.USING_ASSEMBLY_DEMAND_DATE))) - msi.aggregate_time_fence_date),
11386: 1, msi.aggregate_time_fence_date,
11383: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11384: NVL(D.SCHEDULE_SHIP_DATE,
11385: D.USING_ASSEMBLY_DEMAND_DATE))) - msi.aggregate_time_fence_date),
11386: 1, msi.aggregate_time_fence_date,
11387: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11388: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11389: NVL(D.SCHEDULE_SHIP_DATE,
11390: D.USING_ASSEMBLY_DEMAND_DATE))))),
11391: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11387: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11388: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11389: NVL(D.SCHEDULE_SHIP_DATE,
11390: D.USING_ASSEMBLY_DEMAND_DATE))))),
11391: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11392: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11393: NVL(D.SCHEDULE_SHIP_DATE,
11394: D.USING_ASSEMBLY_DEMAND_DATE))),
11395: d.demand_id,
11395: d.demand_id,
11396: 51,
11397: d.origination_type,
11398: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11399: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11400: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11401: NVL(D.SCHEDULE_SHIP_DATE,
11402: D.USING_ASSEMBLY_DEMAND_DATE)))
11403: - msi.aggregate_time_fence_date),
11402: D.USING_ASSEMBLY_DEMAND_DATE)))
11403: - msi.aggregate_time_fence_date),
11404: 1, 1,
11405: NULL),
11406: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11407: 2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
11408: NVL(D.SCHEDULE_SHIP_DATE,
11409: D.USING_ASSEMBLY_DEMAND_DATE)))
11410: - msi.aggregate_time_fence_date),
11529: peg2.organization_id,
11530: peg2.sr_instance_id,
11531: NVL(d1.demand_class, :def_num) demand_class,
11532: decode(msi.aggregate_time_fence_date,
11533: NULL, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11534: 2, NVL(d2.PLANNED_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE),
11535: NVL(d2.SCHEDULE_SHIP_DATE,
11536: d2.USING_ASSEMBLY_DEMAND_DATE))),
11537: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11534: 2, NVL(d2.PLANNED_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE),
11535: NVL(d2.SCHEDULE_SHIP_DATE,
11536: d2.USING_ASSEMBLY_DEMAND_DATE))),
11537: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11538: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11539: 2, NVL(d2.PLANNED_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE),
11540: NVL(d2.SCHEDULE_SHIP_DATE,
11541: d2.USING_ASSEMBLY_DEMAND_DATE))) - msi.aggregate_time_fence_date),
11542: 1, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11538: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11539: 2, NVL(d2.PLANNED_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE),
11540: NVL(d2.SCHEDULE_SHIP_DATE,
11541: d2.USING_ASSEMBLY_DEMAND_DATE))) - msi.aggregate_time_fence_date),
11542: 1, trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11543: 2, NVL(d2.PLANNED_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE),
11544: NVL(d2.SCHEDULE_SHIP_DATE,
11545: d2.USING_ASSEMBLY_DEMAND_DATE))),
11546: msi.aggregate_time_fence_date+1),
11543: 2, NVL(d2.PLANNED_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE),
11544: NVL(d2.SCHEDULE_SHIP_DATE,
11545: d2.USING_ASSEMBLY_DEMAND_DATE))),
11546: msi.aggregate_time_fence_date+1),
11547: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11548: 2, NVL(d2.PLANNED_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE),
11549: NVL(d2.SCHEDULE_SHIP_DATE,
11550: d2.USING_ASSEMBLY_DEMAND_DATE)))
11551: - msi.aggregate_time_fence_date),
11549: NVL(d2.SCHEDULE_SHIP_DATE,
11550: d2.USING_ASSEMBLY_DEMAND_DATE)))
11551: - msi.aggregate_time_fence_date),
11552: 1, msi.aggregate_time_fence_date,
11553: 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)))))) demand_date,
11557: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11553: 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)))))) demand_date,
11557: trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11558: 2, NVL(d2.PLANNED_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE),
11559: NVL(d2.SCHEDULE_SHIP_DATE,
11560: d2.USING_ASSEMBLY_DEMAND_DATE))) original_demand_date,
11561: decode(d2.origination_type, 4, d2.daily_demand_rate,
11568: d2.demand_id parent_demand_id,
11569: 51 origination_type, -- ATP Bucketed Demand
11570: d2.origination_type original_origination_type,
11571: decode(sign(trunc(s.new_schedule_date) - msi.aggregate_time_fence_date),
11572: 1, decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11573: 2, NVL(d2.PLANNED_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE),
11574: NVL(d2.SCHEDULE_SHIP_DATE,
11575: d2.USING_ASSEMBLY_DEMAND_DATE)))
11576: - msi.aggregate_time_fence_date),
11575: d2.USING_ASSEMBLY_DEMAND_DATE)))
11576: - msi.aggregate_time_fence_date),
11577: 1, 1,
11578: NULL),
11579: decode(sign(trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
11580: 2, NVL(d2.PLANNED_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE),
11581: NVL(d2.SCHEDULE_SHIP_DATE,
11582: d2.USING_ASSEMBLY_DEMAND_DATE)))
11583: - msi.aggregate_time_fence_date),