[Home] [Help]
74:
75: IF (p_plan_id = -1) THEN -- ods, put into ods tables
76:
77: IF PG_DEBUG in ('Y', 'C') THEN
78: msc_sch_wb.atp_debug('Add_Mat_Demand: ' || 'ODS based ATP, Add into MSC_SALES Orders');
79: msc_sch_wb.atp_debug('Add_Mat_Demand: ' || 'p_atp_rec.original_request_ship_date: ' ||p_atp_rec.original_request_ship_date);
80: END IF;
81:
82: INSERT INTO MSC_SALES_ORDERS(
78: msc_sch_wb.atp_debug('Add_Mat_Demand: ' || 'ODS based ATP, Add into MSC_SALES Orders');
79: msc_sch_wb.atp_debug('Add_Mat_Demand: ' || 'p_atp_rec.original_request_ship_date: ' ||p_atp_rec.original_request_ship_date);
80: END IF;
81:
82: INSERT INTO MSC_SALES_ORDERS(
83: DEMAND_ID,
84: SR_INSTANCE_ID,
85: INVENTORY_ITEM_ID,
86: ORGANIZATION_ID,
1331:
1332: --3720018, commented the deletion of Sales orders.
1333: -- Bug 1723284, implicit conversion of datatype was forcing it
1334: -- not to use index and do FTS.
1335: /*DELETE msc_sales_orders
1336: WHERE sr_instance_id = p_instance_id
1337: AND demand_source_line = to_char(p_identifier)
1338: AND decode(demand_source_type,100,demand_source_type,-1)
1339: =decode(p_demand_source_type,
1341: p_demand_source_type,
1342: -1); --CMRO*/
1343:
1344: --3720018, msc_sales_order is updated in case of ODS rescheduling
1345: Update msc_sales_orders
1346: set old_primary_uom_quantity = primary_uom_quantity,
1347: old_reservation_quantity = reservation_quantity,
1348: reservation_quantity = 0,
1349: Primary_uom_quantity = 0
1357: -1)
1358: returning demand_id, inventory_item_id, atp_refresh_number
1359: bulk collect into x_demand_id, x_inv_item_id, x_ods_atp_refresh_no;
1360:
1361: Update msc_sales_orders
1362: set atp_refresh_number = 10000000000 --3720018
1363: WHERE sr_instance_id = p_instance_id
1364: AND demand_source_line = to_char(p_identifier)
1365: AND organization_id = p_source_organization_Id ---7118988
1381: --s_cto_rearch: Now delete rows fpr CTO components
1382: IF p_ato_model_line_id is not null THEN
1383:
1384: --3720018, commented the deletion of Sales orders.
1385: /*DELETE msc_sales_orders
1386: where sr_instance_id = p_instance_id
1387: and ato_line_id = p_identifier
1388: AND decode(demand_source_type,100,demand_source_type,-1)
1389: =decode(p_demand_source_type,
1391: p_demand_source_type,
1392: -1); --CMRO*/
1393:
1394: --3720018, msc_sales_order is updated in case of ODS rescheduling
1395: Update msc_sales_orders
1396: set old_primary_uom_quantity = primary_uom_quantity,
1397: --old_refresh_number = refresh_number,
1398: Primary_uom_quantity = 0,
1399: old_reservation_quantity = reservation_quantity,
1414: IF PG_DEBUG in ('Y', 'C') THEN
1415: msc_sch_wb.atp_debug('Number of component lines updated := ' || SQL%ROWCOUNT);
1416: END IF;
1417:
1418: Update msc_sales_orders
1419: set atp_refresh_number = 10000000000 --3720018
1420: WHERE sr_instance_id = p_instance_id
1421: AND ato_line_id = p_identifier
1422: AND demand_source_line <> to_char(p_identifier)
2117: IF PG_DEBUG in ('Y', 'C') THEN
2118: msc_sch_wb.atp_debug('***** Begin Remove_Invalid_SD_Rec Procedure *****');
2119: END IF;
2120:
2121: -- for ods, just need to remove the record from msc_sales_orders
2122: -- initialize API return status to success
2123: x_return_status := FND_API.G_RET_STS_SUCCESS;
2124:
2125: -- rajjain 05/19/2003 bug 2959840
2432:
2433: ELSE -- IF c1.identifier2 <> -1 THEN
2434:
2435: IF PG_DEBUG in ('Y', 'C') THEN
2436: msc_sch_wb.atp_debug('Remove_Invalid_SD_Rec: ' || 'delete demand from msc_sales_orders, demand_id = '||
2437: c1.identifier3);
2438: msc_sch_wb.atp_debug('Remove_Invalid_SD_Rec: ' || 'demand_source_line := ' || to_char(c1.order_line_id));
2439: msc_sch_wb.atp_debug('Remove_Invalid_SD_Rec: ' || 'sr_instance_id := ' || c1.identifier1);
2440: END IF;
2438: msc_sch_wb.atp_debug('Remove_Invalid_SD_Rec: ' || 'demand_source_line := ' || to_char(c1.order_line_id));
2439: msc_sch_wb.atp_debug('Remove_Invalid_SD_Rec: ' || 'sr_instance_id := ' || c1.identifier1);
2440: END IF;
2441:
2442: DELETE from msc_sales_orders
2443: WHERE demand_id = c1.identifier3
2444: AND demand_source_line = to_char(c1.order_line_id)
2445: AND sr_instance_id = c1.identifier1
2446: -- for bug 2120698, need to get the date and quantity from here
2489:
2490:
2491: update /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ msc_atp_summary_so
2492: -- bug 2120698, use the date and quantity from
2493: -- the returned value of msc_sales_orders instead of
2494: -- pegging info. The reason is that for a ship set,
2495: -- we could update sd date to later date without
2496: -- changing the pegging.
2497: -- set sd_qty = (sd_qty - c1.supply_demand_quantity)
3234: -- ngoel, changed for performance reason, don't
3235: -- do anything in case p_supply_demand_date is NULL.
3236: IF p_supply_demand_date IS NOT NULL THEN
3237: -- For bug 2259824, move the demand to the end of day
3238: UPDATE MSC_SALES_ORDERS
3239: SET REQUIREMENT_DATE = TRUNC(p_supply_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
3240: REQUEST_DATE = TRUNC(p_request_arrival_date),--plan by request date
3241: SCHEDULE_ARRIVAL_DATE = TRUNC(p_sch_arrival_date) + MSC_ATP_PVT.G_END_OF_DAY, --plan by request date
3242: LATEST_ACCEPTABLE_DATE = p_lat_date,
3710:
3711: --3720018, reverting back incase, rescheduling fails
3712: IF (p_del_ods_demand_ids IS NOT NULL AND p_del_ods_demand_ids.count > 0) THEN
3713: FOR m in 1..p_del_ods_demand_ids.count LOOP
3714: Update msc_sales_orders
3715: set Primary_uom_quantity = MSC_ATP_UTILS.Truncate_Demand(Old_primary_uom_quantity), --5598066
3716: reservation_quantity = MSC_ATP_UTILS.Truncate_Demand(old_reservation_quantity), --5598066
3717: inventory_item_id = p_del_ods_inv_item_ids(m),
3718: atp_refresh_number = p_del_ods_atp_refresh_no(m)
3743:
3744: --3720018, reverting back in case, rescheduling fails
3745: IF (p_del_ods_cto_demand_ids IS NOT NULL AND p_del_ods_cto_demand_ids.count > 0) THEN
3746: FOR m in 1..p_del_ods_cto_demand_ids.count LOOP
3747: Update msc_sales_orders
3748: set Primary_uom_quantity = MSC_ATP_UTILS.Truncate_Demand(Old_primary_uom_quantity), --5598066
3749: inventory_item_id = p_del_ods_cto_inv_item_ids(m),
3750: reservation_quantity = MSC_ATP_UTILS.Truncate_Demand(old_reservation_quantity), -- 5598066
3751: atp_refresh_number = p_del_ods_cto_atp_refresh_no(m)
4024: (D.PRIMARY_UOM_QUANTITY-GREATEST(NVL(D.RESERVATION_QUANTITY,0),
4025: D.COMPLETED_QUANTITY)) sd_qty
4026: BULK COLLECT INTO l_instance_id, l_organization_id, l_inventory_item_id, l_demand_class, l_sd_date, l_sd_qty
4027: FROM
4028: MSC_SALES_ORDERS D,
4029: MSC_ATP_RULES R,
4030: MSC_SYSTEM_ITEMS I,
4031: MSC_TRADING_PARTNERS P,
4032: msc_calendar_dates C,
4782:
4783: END IF;
4784:
4785: -- Update PDS
4786: UPDATE MSC_SALES_ORDERS
4787: SET SCHEDULE_ARRIVAL_DATE = p_ship_arrival_date_rec.scheduled_arrival_date,
4788: LATEST_ACCEPTABLE_DATE = p_ship_arrival_date_rec.latest_acceptable_date,
4789: ORDER_DATE_TYPE_CODE = p_ship_arrival_date_rec.order_date_type,
4790: SHIP_SET_NAME = p_ship_arrival_date_rec.ship_set_name,
5575: (D.PRIMARY_UOM_QUANTITY-GREATEST(NVL(D.RESERVATION_QUANTITY,0),
5576: D.COMPLETED_QUANTITY)) sd_qty
5577: BULK COLLECT INTO l_instance_id, l_organization_id, l_inventory_item_id, l_demand_class, l_sd_date, l_sd_qty
5578: FROM
5579: MSC_SALES_ORDERS D,
5580: MSC_ATP_RULES R,
5581: MSC_SYSTEM_ITEMS I,
5582: MSC_TRADING_PARTNERS P,
5583: msc_calendar_dates C,