[Home] [Help]
79:
80: IF (p_plan_id = -1) THEN -- ods, put into ods tables
81:
82: IF PG_DEBUG in ('Y', 'C') THEN
83: msc_sch_wb.atp_debug('Add_Mat_Demand: ' || 'ODS based ATP, Add into MSC_SALES Orders');
84: msc_sch_wb.atp_debug('Add_Mat_Demand: ' || 'p_atp_rec.original_request_ship_date: ' ||p_atp_rec.original_request_ship_date);
85: END IF;
86: --bug#12973673 -- enable the triple checkin for MSCDATPB.pls file
87: /* Design : read the patch_level in fnd_product_installations.
106: ELSIF (lv_apps_ver IS NOT NULL AND ( lv_apps_ver = 'R12.MSC.B' or lv_apps_ver = 'R12.MSC.C')) then
107: l_is_rel_120 := FALSE;
108: ELSE
109: BEGIN
110: l_stmt := 'select row_type from msc_sales_orders where 2=1';
111: EXECUTE IMMEDIATE l_stmt;
112: EXCEPTION
113: WHEN others THEN
114: IF SQLCODE IN (-00904) THEN
113: WHEN others THEN
114: IF SQLCODE IN (-00904) THEN
115: l_is_rel_120 := true;
116: IF PG_DEBUG in ('Y', 'C') THEN
117: msc_sch_wb.atp_debug('Add_Mat_Demand: ' || 'Row_type column doesnot exists in msc_sales_orders. So release is MSC120');
118: END IF;
119: END IF;
120: END;
121: END IF;
124: msc_sch_wb.atp_debug('Add_Mat_Demand: ' || 'Source Instance Version : lv_apps_ver : ' || lv_apps_ver);
125: END IF;
126:
127: IF (l_is_rel_120) THEN
128: INSERT INTO MSC_SALES_ORDERS(
129: DEMAND_ID,
130: SR_INSTANCE_ID,
131: INVENTORY_ITEM_ID,
132: ORGANIZATION_ID,
189: p_atp_rec.arrival_set_name --bug3263368
190: )
191: RETURNING DEMAND_ID INTO x_demand_id;
192: ELSE
193: lv_sql_stmt := 'INSERT INTO MSC_SALES_ORDERS('
194: ||' DEMAND_ID,'
195: ||' SR_INSTANCE_ID,'
196: ||' INVENTORY_ITEM_ID,'
197: ||' ORGANIZATION_ID,'
1484:
1485: --3720018, commented the deletion of Sales orders.
1486: -- Bug 1723284, implicit conversion of datatype was forcing it
1487: -- not to use index and do FTS.
1488: /*DELETE msc_sales_orders
1489: WHERE sr_instance_id = p_instance_id
1490: AND demand_source_line = to_char(p_identifier)
1491: AND decode(demand_source_type,100,demand_source_type,-1)
1492: =decode(p_demand_source_type,
1494: p_demand_source_type,
1495: -1); --CMRO*/
1496:
1497: --3720018, msc_sales_order is updated in case of ODS rescheduling
1498: Update msc_sales_orders
1499: set old_primary_uom_quantity = primary_uom_quantity,
1500: old_reservation_quantity = reservation_quantity,
1501: reservation_quantity = 0,
1502: Primary_uom_quantity = 0
1510: -1)
1511: returning demand_id, inventory_item_id, atp_refresh_number
1512: bulk collect into x_demand_id, x_inv_item_id, x_ods_atp_refresh_no;
1513:
1514: Update msc_sales_orders
1515: set atp_refresh_number = 10000000000 --3720018
1516: WHERE sr_instance_id = p_instance_id
1517: AND demand_source_line = to_char(p_identifier)
1518: AND organization_id = p_source_organization_Id ---7118988
1534: --s_cto_rearch: Now delete rows fpr CTO components
1535: IF p_ato_model_line_id is not null THEN
1536:
1537: --3720018, commented the deletion of Sales orders.
1538: /*DELETE msc_sales_orders
1539: where sr_instance_id = p_instance_id
1540: and ato_line_id = p_identifier
1541: AND decode(demand_source_type,100,demand_source_type,-1)
1542: =decode(p_demand_source_type,
1544: p_demand_source_type,
1545: -1); --CMRO*/
1546:
1547: --3720018, msc_sales_order is updated in case of ODS rescheduling
1548: Update msc_sales_orders
1549: set old_primary_uom_quantity = primary_uom_quantity,
1550: --old_refresh_number = refresh_number,
1551: Primary_uom_quantity = 0,
1552: old_reservation_quantity = reservation_quantity,
1567: IF PG_DEBUG in ('Y', 'C') THEN
1568: msc_sch_wb.atp_debug('Number of component lines updated := ' || SQL%ROWCOUNT);
1569: END IF;
1570:
1571: Update msc_sales_orders
1572: set atp_refresh_number = 10000000000 --3720018
1573: WHERE sr_instance_id = p_instance_id
1574: AND ato_line_id = p_identifier
1575: AND demand_source_line <> to_char(p_identifier)
2274: IF PG_DEBUG in ('Y', 'C') THEN
2275: msc_sch_wb.atp_debug('***** Begin Remove_Invalid_SD_Rec Procedure *****');
2276: END IF;
2277:
2278: -- for ods, just need to remove the record from msc_sales_orders
2279: -- initialize API return status to success
2280: x_return_status := FND_API.G_RET_STS_SUCCESS;
2281:
2282: -- rajjain 05/19/2003 bug 2959840
2589:
2590: ELSE -- IF c1.identifier2 <> -1 THEN
2591:
2592: IF PG_DEBUG in ('Y', 'C') THEN
2593: msc_sch_wb.atp_debug('Remove_Invalid_SD_Rec: ' || 'delete demand from msc_sales_orders, demand_id = '||
2594: c1.identifier3);
2595: msc_sch_wb.atp_debug('Remove_Invalid_SD_Rec: ' || 'demand_source_line := ' || to_char(c1.order_line_id));
2596: msc_sch_wb.atp_debug('Remove_Invalid_SD_Rec: ' || 'sr_instance_id := ' || c1.identifier1);
2597: END IF;
2595: msc_sch_wb.atp_debug('Remove_Invalid_SD_Rec: ' || 'demand_source_line := ' || to_char(c1.order_line_id));
2596: msc_sch_wb.atp_debug('Remove_Invalid_SD_Rec: ' || 'sr_instance_id := ' || c1.identifier1);
2597: END IF;
2598:
2599: DELETE from msc_sales_orders
2600: WHERE demand_id = c1.identifier3
2601: AND demand_source_line = to_char(c1.order_line_id)
2602: AND sr_instance_id = c1.identifier1
2603: -- for bug 2120698, need to get the date and quantity from here
2646:
2647:
2648: update /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ msc_atp_summary_so
2649: -- bug 2120698, use the date and quantity from
2650: -- the returned value of msc_sales_orders instead of
2651: -- pegging info. The reason is that for a ship set,
2652: -- we could update sd date to later date without
2653: -- changing the pegging.
2654: -- set sd_qty = (sd_qty - c1.supply_demand_quantity)
3401: -- ngoel, changed for performance reason, don't
3402: -- do anything in case p_supply_demand_date is NULL.
3403: IF p_supply_demand_date IS NOT NULL THEN
3404: -- For bug 2259824, move the demand to the end of day
3405: UPDATE MSC_SALES_ORDERS
3406: SET REQUIREMENT_DATE = TRUNC(p_supply_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
3407: REQUEST_DATE = TRUNC(p_request_arrival_date),--plan by request date
3408: SCHEDULE_ARRIVAL_DATE = TRUNC(p_sch_arrival_date) + MSC_ATP_PVT.G_END_OF_DAY, --plan by request date
3409: LATEST_ACCEPTABLE_DATE = p_lat_date,
3885:
3886: --3720018, reverting back incase, rescheduling fails
3887: IF (p_del_ods_demand_ids IS NOT NULL AND p_del_ods_demand_ids.count > 0) THEN
3888: FOR m in 1..p_del_ods_demand_ids.count LOOP
3889: Update msc_sales_orders
3890: set Primary_uom_quantity = MSC_ATP_UTILS.Truncate_Demand(Old_primary_uom_quantity), --5598066
3891: reservation_quantity = MSC_ATP_UTILS.Truncate_Demand(old_reservation_quantity), --5598066
3892: inventory_item_id = p_del_ods_inv_item_ids(m),
3893: atp_refresh_number = p_del_ods_atp_refresh_no(m)
3918:
3919: --3720018, reverting back in case, rescheduling fails
3920: IF (p_del_ods_cto_demand_ids IS NOT NULL AND p_del_ods_cto_demand_ids.count > 0) THEN
3921: FOR m in 1..p_del_ods_cto_demand_ids.count LOOP
3922: Update msc_sales_orders
3923: set Primary_uom_quantity = MSC_ATP_UTILS.Truncate_Demand(Old_primary_uom_quantity), --5598066
3924: inventory_item_id = p_del_ods_cto_inv_item_ids(m),
3925: reservation_quantity = MSC_ATP_UTILS.Truncate_Demand(old_reservation_quantity), -- 5598066
3926: atp_refresh_number = p_del_ods_cto_atp_refresh_no(m)
4206: (D.PRIMARY_UOM_QUANTITY-GREATEST(NVL(D.RESERVATION_QUANTITY,0),
4207: D.COMPLETED_QUANTITY)) sd_qty
4208: BULK COLLECT INTO l_instance_id, l_organization_id, l_inventory_item_id, l_demand_class, l_sd_date, l_sd_qty
4209: FROM
4210: MSC_SALES_ORDERS D,
4211: MSC_ATP_RULES R,
4212: MSC_SYSTEM_ITEMS I,
4213: MSC_TRADING_PARTNERS P,
4214: msc_calendar_dates C,
4964:
4965: END IF;
4966:
4967: -- Update PDS
4968: UPDATE MSC_SALES_ORDERS
4969: SET SCHEDULE_ARRIVAL_DATE = p_ship_arrival_date_rec.scheduled_arrival_date,
4970: LATEST_ACCEPTABLE_DATE = p_ship_arrival_date_rec.latest_acceptable_date,
4971: ORDER_DATE_TYPE_CODE = p_ship_arrival_date_rec.order_date_type,
4972: SHIP_SET_NAME = p_ship_arrival_date_rec.ship_set_name,
5779: (D.PRIMARY_UOM_QUANTITY-GREATEST(NVL(D.RESERVATION_QUANTITY,0),
5780: D.COMPLETED_QUANTITY)) sd_qty
5781: BULK COLLECT INTO l_instance_id, l_organization_id, l_inventory_item_id, l_demand_class, l_sd_date, l_sd_qty
5782: FROM
5783: MSC_SALES_ORDERS D,
5784: MSC_ATP_RULES R,
5785: MSC_SYSTEM_ITEMS I,
5786: MSC_TRADING_PARTNERS P,
5787: msc_calendar_dates C,
5960: msc_sch_wb.atp_debug('Hide_SD_Rec: ' || 'Reset all the global variables ');
5961: msc_sch_wb.atp_debug('Hide_SD_Rec: ' || 'p_identifier= ' || p_identifier);
5962: END IF;
5963:
5964: -- for ods, just need to remove the record from msc_sales_orders
5965: -- initialize API return status to success
5966: x_return_status := FND_API.G_RET_STS_SUCCESS;
5967: MSC_ATP_PVT.G_ALLOCATION_METHOD := NVL(FND_PROFILE.VALUE('MSC_ALLOCATION_METHOD'),2);
5968: MSC_ATP_PVT.G_ALLOCATED_ATP := NVL(FND_PROFILE.value('MSC_ALLOCATED_ATP'),'N');
6305: IF PG_DEBUG in ('Y', 'C') THEN
6306: msc_sch_wb.atp_debug('***** Begin Restore_SD_Rec Procedure *****');
6307: END IF;
6308:
6309: -- for ods, just need to remove the record from msc_sales_orders
6310: -- initialize API return status to success
6311: x_return_status := FND_API.G_RET_STS_SUCCESS;
6312: MSC_ATP_PVT.G_ALLOCATION_METHOD := NVL(FND_PROFILE.VALUE('MSC_ALLOCATION_METHOD'),2);
6313: MSC_ATP_PVT.G_ALLOCATED_ATP := NVL(FND_PROFILE.value('MSC_ALLOCATED_ATP'),'N');