[Home] [Help]
72: -- End Bug 3805136
73: );
74:
75: -- This record will hold the Detailed ATP Pegging Data.
76: -- Corresponds to the table msc_atp_detail_peg_temp
77: TYPE ATP_Detail_Peg_Typ is RECORD (
78: reference_item_id MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr(),
79: base_item_id MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr(),
80: inventory_item_id MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr(),
1509:
1510: -- Bug 3362558
1511: -- Global forecasting situation, org_id is -1.
1512: -- CTO_PF_PRJ Query present below removed. The use of
1513: -- end_demand_id obviates the need for SQL that joins with msc_atp_detail_peg_temp.
1514: -- Changes here in relation to Bug 3362558 not needed anymore.
1515: -- End Bug 3362558
1516:
1517: -- Bug 3362499
2066:
2067: -- Now obtain the pegging data into Global Temporary Table in a Loop.
2068:
2069: -- Obtain the supplies info for the config item.
2070: INSERT INTO MSC_ATP_DETAIL_PEG_TEMP (
2071: reference_item_id, base_item_id,
2072: inventory_item_id,
2073: plan_id,
2074: sr_instance_id,
2196:
2197: -- Update Pegging data with End demand Id. Information
2198: -- CTO_PF_PRJ changes.
2199:
2200: UPDATE MSC_ATP_DETAIL_PEG_TEMP madpt -- outer table
2201: SET
2202: (end_demand_id
2203: ) =
2204: ( SELECT end_demand_id
2201: SET
2202: (end_demand_id
2203: ) =
2204: ( SELECT end_demand_id
2205: FROM msc_atp_detail_peg_temp madpti -- Inner table
2206: WHERE madpti.plan_id = madpt.plan_id
2207: AND madpti.sr_instance_id = madpt.sr_instance_id
2208: AND madpti.reference_item_id = madpt.inventory_item_id
2209: AND madpti.sales_order_line_id = madpt.sales_order_line_id
2227: END IF;
2228:
2229: -- Update Pegging data with demand Information
2230:
2231: UPDATE MSC_ATP_DETAIL_PEG_TEMP madpt
2232: SET
2233: (demand_id,
2234: demand_date,
2235: demand_quantity,
2254: d.origination_type,
2255: NVL(d.original_demand_id, d.demand_id) original_demand_id,
2256: decode(d.origination_type, 1,
2257: to_char(d.disposition_id), d.order_number)
2258: FROM --msc_atp_detail_peg_temp madpt,
2259: msc_full_pegging peg,
2260: msc_demands d
2261: WHERE madpt.plan_id = p_plan_id
2262: AND madpt.sr_instance_id = c_items_rec.sr_instance_id
2355: END LOOP;
2356:
2357: -- First obtain transfers if any.
2358: FORALL n_idx IN 1..l_multi_config_count
2359: INSERT INTO MSC_ATP_DETAIL_PEG_TEMP (
2360: reference_item_id, base_item_id,
2361: inventory_item_id,
2362: plan_id,
2363: sr_instance_id,
2425: d1.origination_type,
2426: -- Bug 3362558 use pegging's original demand_id
2427: NVL(d1.original_demand_id, adpt.original_demand_id) original_demand_id,
2428: -- Begin Bug 3319810
2429: -- Use pegging's supply data instead of msc_atp_detail_peg_temp.
2430: adpt.process_seq_id,
2431: --adpt.supply_id,
2432: --adpt.supply_date,
2433: --adpt.supply_quantity,
2447: peg1.end_pegging_id, peg1.pegging_id, peg1.prev_pegging_id,
2448: NULL fcst_organization_id, NULL forecast_qty,
2449: NULL consumed_qty, NULL overconsumption_qty
2450: FROM
2451: msc_atp_detail_peg_temp adpt,
2452: msc_full_pegging peg1 ,
2453: msc_demands d1,
2454: msc_system_items msi
2455: WHERE adpt.plan_id = p_plan_id
2469: AND adpt.end_pegging_id = L_Config_Sup.END_PEGGING_ID(n_idx)
2470: AND adpt.base_item_id = L_Config_Sup.BASE_ITEM_ID(n_idx)
2471: AND adpt.inventory_item_id <> adpt.reference_item_id
2472: -- End Bug 3750638
2473: -- Link up pegging with config_item info in msc_atp_detail_peg_temp.
2474: AND peg1.plan_id = adpt.plan_id
2475: AND peg1.sr_instance_id = adpt.sr_instance_id
2476: AND peg1.end_pegging_id = adpt.end_pegging_id
2477: AND peg1.prev_pegging_id = adpt.pegging_id
2503: END IF;
2504: -- Obtain the data for the components of the lower level configuration item(s)
2505: -- in the sales order.
2506: FORALL n_idx IN 1..l_multi_config_count
2507: INSERT INTO MSC_ATP_DETAIL_PEG_TEMP (
2508: reference_item_id, base_item_id,
2509: inventory_item_id,
2510: plan_id,
2511: sr_instance_id,
2573: d1.origination_type,
2574: -- Bug 3362558 use pegging's original demand_id
2575: NVL(d1.original_demand_id, adpt.original_demand_id) original_demand_id,
2576: -- Begin Bug 3319810
2577: -- Use pegging's supply data instead of msc_atp_detail_peg_temp.
2578: adpt.process_seq_id,
2579: --adpt.supply_id,
2580: --adpt.supply_date,
2581: --adpt.supply_quantity,
2595: peg1.end_pegging_id, peg1.pegging_id, peg1.prev_pegging_id,
2596: NULL fcst_organization_id, NULL forecast_qty,
2597: NULL consumed_qty, NULL overconsumption_qty
2598: FROM
2599: msc_atp_detail_peg_temp adpt,
2600: msc_full_pegging peg1 ,
2601: msc_demands d1,
2602: msc_system_items msi,
2603: msc_process_effectivity proc,
2619: AND adpt.end_pegging_id = L_Config_Sup.END_PEGGING_ID(n_idx)
2620: AND adpt.base_item_id = L_Config_Sup.BASE_ITEM_ID(n_idx)
2621: AND adpt.supply_date is not NULL
2622: -- End Bug 3750638
2623: -- Link up pegging with config_item info in msc_atp_detail_peg_temp.
2624: AND peg1.plan_id = adpt.plan_id
2625: AND peg1.sr_instance_id = adpt.sr_instance_id
2626: AND peg1.end_pegging_id = adpt.end_pegging_id
2627: AND peg1.prev_pegging_id = adpt.pegging_id
2674: END IF;
2675:
2676:
2677: -- update the supplies for the new demands
2678: UPDATE MSC_ATP_DETAIL_PEG_TEMP madpt
2679: SET
2680: ( process_seq_id, supply_id,
2681: supply_date,
2682: supply_quantity,
2707: 0)) -- 0 otherwise not released.
2708: -- End Bug 3717618
2709: )),
2710: 1) exclude_flag
2711: FROM --msc_atp_detail_peg_temp madpt,
2712: msc_full_pegging peg,
2713: msc_supplies SUP
2714: WHERE madpt.plan_id = p_plan_id
2715: AND madpt.sr_instance_id = c_items_rec.sr_instance_id
3349: -- CTO-PF
3350: atp_peg_det.atf_date,
3351: atp_peg_det.product_family_id
3352: FROM
3353: msc_atp_detail_peg_temp
3354: WHERE plan_id = p_plan_id
3355: AND sr_instance_id (+) = c_items_rec.sr_instance_id -- outer join to get all instances
3356: AND reference_item_id = c_items_rec.inventory_item_id
3357: AND sales_order_line_id = c_items_rec.sales_order_line_id