[Home] [Help]
290:
291: IF (MSC_ATP_PVT.G_SUMMARY_FLAG = 'Y') THEN
292: IF PG_DEBUG in ('Y', 'C') THEN
293: msc_sch_wb.atp_debug('Add_Mat_Demand: ' || 'Insert into summary table');
294: msc_sch_wb.atp_debug('Add_Mat_Demand: ' || ' update msc_atp_summary_so');
295: END IF;
296: -- use hint to do index search
297: MSC_ATP_PROC.SHOW_SUMMARY_QUANTITY(p_atp_rec.instance_id,
298: -1,
305: null,
306: p_dc_flag,
307: p_atp_rec.demand_class,
308: 1);
309: UPDATE /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ msc_atp_summary_so
310: set sd_qty = sd_qty + p_atp_rec.quantity_ordered
311: where inventory_item_id = p_atp_rec.inventory_item_id
312: and organization_id = p_atp_rec.organization_id
313: and sr_instance_id = p_atp_rec.instance_id
315: and demand_class = Decode(p_dc_flag, 1, NVL(p_atp_rec.demand_class, '@@@'),'@@@');
316:
317: IF SQL%NOTFOUND THEN
318: IF PG_DEBUG in ('Y', 'C') THEN
319: msc_sch_wb.atp_debug('Add_Mat_Demand: ' || ' insert into msc_atp_summary_so');
320: END IF;
321: BEGIN
322: --- use hint to do index search
323: insert /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ into msc_atp_summary_so
319: msc_sch_wb.atp_debug('Add_Mat_Demand: ' || ' insert into msc_atp_summary_so');
320: END IF;
321: BEGIN
322: --- use hint to do index search
323: insert /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ into msc_atp_summary_so
324: (plan_id,
325: inventory_item_id,
326: organization_id,
327: sr_instance_id,
362: p_dc_flag,
363: p_atp_rec.demand_class,
364: 1);
365:
366: UPDATE /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ msc_atp_summary_so
367: set sd_qty = sd_qty + p_atp_rec.quantity_ordered
368: where inventory_item_id = p_atp_rec.inventory_item_id
369: and organization_id = p_atp_rec.organization_id
370: and sr_instance_id = p_atp_rec.instance_id
2644: l_demand_class,
2645: 1);
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
3464: l_demand_class,
3465: 1);
3466:
3467:
3468: UPDATE /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ MSC_ATP_SUMMARY_SO
3469: set sd_qty = (sd_qty - l_sd_qty)
3470: where sr_instance_id = p_instance_id and
3471: inventory_item_id = l_inventory_item_id and
3472: organization_id = l_org_id and
3492: p_dc_flag,
3493: l_demand_class,
3494: 1);
3495:
3496: update /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ msc_atp_summary_so
3497: set sd_qty = sd_qty + l_sd_qty
3498: where sr_instance_id = p_instance_id and
3499: inventory_item_id = l_inventory_item_id and
3500: organization_id = l_org_id and
3506: IF PG_DEBUG in ('Y', 'C') THEN
3507: msc_sch_wb.atp_debug('Update_SD_Date: ' || 'insert qty on new date');
3508: END IF;
3509: BEGIN
3510: insert /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ into msc_atp_summary_so
3511: (plan_id,
3512: inventory_item_id,
3513: organization_id,
3514: sr_instance_id,
3547: l_demand_class,
3548: 1);
3549:
3550:
3551: update /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ msc_atp_summary_so
3552: set sd_qty = sd_qty + l_sd_qty
3553: where inventory_item_id = l_inventory_item_id
3554: and sr_instance_id = p_instance_id
3555: and organization_id = l_org_id
4291: msc_sch_wb.atp_debug('DELETE_SUMMARY_ROW: ' || 'l_sd_qty := ' || l_sd_qty(i));
4292: msc_sch_wb.atp_debug('DELETE_SUMMARY_ROW: ' || 'l_instance_id := ' || l_instance_id(i));
4293: END IF;
4294:
4295: update /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ msc_atp_summary_so
4296: set sd_qty = (sd_qty - l_sd_qty(i))
4297: where inventory_item_id = l_inventory_item_id(i)
4298: and sr_instance_id = l_instance_id(i)
4299: and organization_id = l_organization_id(i)
4304: IF (SQL%NOTFOUND) THEN
4305: msc_sch_wb.atp_debug('DELETE_SUMMARY_ROW: update failed, now try insert');
4306: --- Insert the new record
4307: BEGIN
4308: INSERT INTO MSC_ATP_SUMMARY_SO
4309: (plan_id,
4310: sr_instance_id,
4311: organization_id,
4312: inventory_item_id,
4334: -- If a record has already been inserted by another process
4335: -- If insert fails then update.
4336: WHEN DUP_VAL_ON_INDEX THEN
4337: -- Update the record.
4338: update /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ msc_atp_summary_so
4339: set sd_qty = (sd_qty - l_sd_qty(i)),
4340: last_update_date = l_sysdate,
4341: last_updated_by = l_user_id
4342: where inventory_item_id = l_inventory_item_id(i)
5859: msc_sch_wb.atp_debug('UNDO_DELETE_SUMMARY_ROW: ' || 'l_sd_qty := ' || l_sd_qty(i));
5860: msc_sch_wb.atp_debug('UNDO_DELETE_SUMMARY_ROW: ' || 'l_instance_id := ' || l_instance_id(i));
5861: END IF;
5862:
5863: update /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ msc_atp_summary_so
5864: set sd_qty = (sd_qty + l_sd_qty(i))
5865: where inventory_item_id = l_inventory_item_id(i)
5866: and sr_instance_id = l_instance_id(i)
5867: and organization_id = l_organization_id(i)
5873: IF (SQL%NOTFOUND) THEN
5874: msc_sch_wb.atp_debug('DELETE_SUMMARY_ROW: update failed, now try insert');
5875: --- Insert the new record
5876: BEGIN
5877: INSERT INTO MSC_ATP_SUMMARY_SO
5878: (plan_id,
5879: sr_instance_id,
5880: organization_id,
5881: inventory_item_id,
5902: -- If a record has already been inserted by another process
5903: -- If insert fails then update.
5904: WHEN DUP_VAL_ON_INDEX THEN
5905: -- Update the record.
5906: update /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ msc_atp_summary_so
5907: set sd_qty = (sd_qty + l_sd_qty(i)),
5908: last_update_date = l_sysdate,
5909: last_updated_by = l_user_id
5910: where inventory_item_id = l_inventory_item_id(i)