[Home] [Help]
146:
147: IF (MSC_ATP_PVT.G_SUMMARY_FLAG = 'Y') THEN
148: IF PG_DEBUG in ('Y', 'C') THEN
149: msc_sch_wb.atp_debug('Add_Mat_Demand: ' || 'Insert into summary table');
150: msc_sch_wb.atp_debug('Add_Mat_Demand: ' || ' update msc_atp_summary_so');
151: END IF;
152: -- use hint to do index search
153: MSC_ATP_PROC.SHOW_SUMMARY_QUANTITY(p_atp_rec.instance_id,
154: -1,
161: null,
162: p_dc_flag,
163: p_atp_rec.demand_class,
164: 1);
165: UPDATE /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ msc_atp_summary_so
166: set sd_qty = sd_qty + p_atp_rec.quantity_ordered
167: where inventory_item_id = p_atp_rec.inventory_item_id
168: and organization_id = p_atp_rec.organization_id
169: and sr_instance_id = p_atp_rec.instance_id
171: and demand_class = Decode(p_dc_flag, 1, NVL(p_atp_rec.demand_class, '@@@'),'@@@');
172:
173: IF SQL%NOTFOUND THEN
174: IF PG_DEBUG in ('Y', 'C') THEN
175: msc_sch_wb.atp_debug('Add_Mat_Demand: ' || ' insert into msc_atp_summary_so');
176: END IF;
177: BEGIN
178: --- use hint to do index search
179: insert /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ into msc_atp_summary_so
175: msc_sch_wb.atp_debug('Add_Mat_Demand: ' || ' insert into msc_atp_summary_so');
176: END IF;
177: BEGIN
178: --- use hint to do index search
179: insert /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ into msc_atp_summary_so
180: (plan_id,
181: inventory_item_id,
182: organization_id,
183: sr_instance_id,
218: p_dc_flag,
219: p_atp_rec.demand_class,
220: 1);
221:
222: UPDATE /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ msc_atp_summary_so
223: set sd_qty = sd_qty + p_atp_rec.quantity_ordered
224: where inventory_item_id = p_atp_rec.inventory_item_id
225: and organization_id = p_atp_rec.organization_id
226: and sr_instance_id = p_atp_rec.instance_id
2487: l_demand_class,
2488: 1);
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
3297: l_demand_class,
3298: 1);
3299:
3300:
3301: UPDATE /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ MSC_ATP_SUMMARY_SO
3302: set sd_qty = (sd_qty - l_sd_qty)
3303: where sr_instance_id = p_instance_id and
3304: inventory_item_id = l_inventory_item_id and
3305: organization_id = l_org_id and
3325: p_dc_flag,
3326: l_demand_class,
3327: 1);
3328:
3329: update /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ msc_atp_summary_so
3330: set sd_qty = sd_qty + l_sd_qty
3331: where sr_instance_id = p_instance_id and
3332: inventory_item_id = l_inventory_item_id and
3333: organization_id = l_org_id and
3339: IF PG_DEBUG in ('Y', 'C') THEN
3340: msc_sch_wb.atp_debug('Update_SD_Date: ' || 'insert qty on new date');
3341: END IF;
3342: BEGIN
3343: insert /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ into msc_atp_summary_so
3344: (plan_id,
3345: inventory_item_id,
3346: organization_id,
3347: sr_instance_id,
3380: l_demand_class,
3381: 1);
3382:
3383:
3384: update /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ msc_atp_summary_so
3385: set sd_qty = sd_qty + l_sd_qty
3386: where inventory_item_id = l_inventory_item_id
3387: and sr_instance_id = p_instance_id
3388: and organization_id = l_org_id
4109: msc_sch_wb.atp_debug('DELETE_SUMMARY_ROW: ' || 'l_sd_qty := ' || l_sd_qty(i));
4110: msc_sch_wb.atp_debug('DELETE_SUMMARY_ROW: ' || 'l_instance_id := ' || l_instance_id(i));
4111: END IF;
4112:
4113: update /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ msc_atp_summary_so
4114: set sd_qty = (sd_qty - l_sd_qty(i))
4115: where inventory_item_id = l_inventory_item_id(i)
4116: and sr_instance_id = l_instance_id(i)
4117: and organization_id = l_organization_id(i)
4122: IF (SQL%NOTFOUND) THEN
4123: msc_sch_wb.atp_debug('DELETE_SUMMARY_ROW: update failed, now try insert');
4124: --- Insert the new record
4125: BEGIN
4126: INSERT INTO MSC_ATP_SUMMARY_SO
4127: (plan_id,
4128: sr_instance_id,
4129: organization_id,
4130: inventory_item_id,
4152: -- If a record has already been inserted by another process
4153: -- If insert fails then update.
4154: WHEN DUP_VAL_ON_INDEX THEN
4155: -- Update the record.
4156: update /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ msc_atp_summary_so
4157: set sd_qty = (sd_qty - l_sd_qty(i)),
4158: last_update_date = l_sysdate,
4159: last_updated_by = l_user_id
4160: where inventory_item_id = l_inventory_item_id(i)
5655: msc_sch_wb.atp_debug('UNDO_DELETE_SUMMARY_ROW: ' || 'l_sd_qty := ' || l_sd_qty(i));
5656: msc_sch_wb.atp_debug('UNDO_DELETE_SUMMARY_ROW: ' || 'l_instance_id := ' || l_instance_id(i));
5657: END IF;
5658:
5659: update /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ msc_atp_summary_so
5660: set sd_qty = (sd_qty + l_sd_qty(i))
5661: where inventory_item_id = l_inventory_item_id(i)
5662: and sr_instance_id = l_instance_id(i)
5663: and organization_id = l_organization_id(i)
5669: IF (SQL%NOTFOUND) THEN
5670: msc_sch_wb.atp_debug('DELETE_SUMMARY_ROW: update failed, now try insert');
5671: --- Insert the new record
5672: BEGIN
5673: INSERT INTO MSC_ATP_SUMMARY_SO
5674: (plan_id,
5675: sr_instance_id,
5676: organization_id,
5677: inventory_item_id,
5698: -- If a record has already been inserted by another process
5699: -- If insert fails then update.
5700: WHEN DUP_VAL_ON_INDEX THEN
5701: -- Update the record.
5702: update /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ msc_atp_summary_so
5703: set sd_qty = (sd_qty + l_sd_qty(i)),
5704: last_update_date = l_sysdate,
5705: last_updated_by = l_user_id
5706: where inventory_item_id = l_inventory_item_id(i)