[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(),
1511:
1512: -- Bug 3362558
1513: -- Global forecasting situation, org_id is -1.
1514: -- CTO_PF_PRJ Query present below removed. The use of
1515: -- end_demand_id obviates the need for SQL that joins with msc_atp_detail_peg_temp.
1516: -- Changes here in relation to Bug 3362558 not needed anymore.
1517: -- End Bug 3362558
1518:
1519: -- Bug 3362499
2068:
2069: -- Now obtain the pegging data into Global Temporary Table in a Loop.
2070:
2071: -- Obtain the supplies info for the config item.
2072: INSERT INTO MSC_ATP_DETAIL_PEG_TEMP (
2073: reference_item_id, base_item_id,
2074: inventory_item_id,
2075: plan_id,
2076: sr_instance_id,
2198:
2199: -- Update Pegging data with End demand Id. Information
2200: -- CTO_PF_PRJ changes.
2201:
2202: UPDATE MSC_ATP_DETAIL_PEG_TEMP madpt -- outer table
2203: SET
2204: (end_demand_id
2205: ) =
2206: ( SELECT end_demand_id
2203: SET
2204: (end_demand_id
2205: ) =
2206: ( SELECT end_demand_id
2207: FROM msc_atp_detail_peg_temp madpti -- Inner table
2208: WHERE madpti.plan_id = madpt.plan_id
2209: AND madpti.sr_instance_id = madpt.sr_instance_id
2210: AND madpti.reference_item_id = madpt.inventory_item_id
2211: AND madpti.sales_order_line_id = madpt.sales_order_line_id
2229: END IF;
2230:
2231: -- Update Pegging data with demand Information
2232:
2233: UPDATE MSC_ATP_DETAIL_PEG_TEMP madpt
2234: SET
2235: (demand_id,
2236: demand_date,
2237: demand_quantity,
2256: d.origination_type,
2257: NVL(d.original_demand_id, d.demand_id) original_demand_id,
2258: decode(d.origination_type, 1,
2259: to_char(d.disposition_id), d.order_number)
2260: FROM --msc_atp_detail_peg_temp madpt,
2261: msc_full_pegging peg,
2262: msc_demands d
2263: WHERE madpt.plan_id = p_plan_id
2264: AND madpt.sr_instance_id = c_items_rec.sr_instance_id
2357: END LOOP;
2358:
2359: -- First obtain transfers if any.
2360: FORALL n_idx IN 1..l_multi_config_count
2361: INSERT INTO MSC_ATP_DETAIL_PEG_TEMP (
2362: reference_item_id, base_item_id,
2363: inventory_item_id,
2364: plan_id,
2365: sr_instance_id,
2427: d1.origination_type,
2428: -- Bug 3362558 use pegging's original demand_id
2429: NVL(d1.original_demand_id, adpt.original_demand_id) original_demand_id,
2430: -- Begin Bug 3319810
2431: -- Use pegging's supply data instead of msc_atp_detail_peg_temp.
2432: adpt.process_seq_id,
2433: --adpt.supply_id,
2434: --adpt.supply_date,
2435: --adpt.supply_quantity,
2449: peg1.end_pegging_id, peg1.pegging_id, peg1.prev_pegging_id,
2450: NULL fcst_organization_id, NULL forecast_qty,
2451: NULL consumed_qty, NULL overconsumption_qty
2452: FROM
2453: msc_atp_detail_peg_temp adpt,
2454: msc_full_pegging peg1 ,
2455: msc_demands d1,
2456: msc_system_items msi
2457: WHERE adpt.plan_id = p_plan_id
2471: AND adpt.end_pegging_id = L_Config_Sup.END_PEGGING_ID(n_idx)
2472: AND adpt.base_item_id = L_Config_Sup.BASE_ITEM_ID(n_idx)
2473: AND adpt.inventory_item_id <> adpt.reference_item_id
2474: -- End Bug 3750638
2475: -- Link up pegging with config_item info in msc_atp_detail_peg_temp.
2476: AND peg1.plan_id = adpt.plan_id
2477: AND peg1.sr_instance_id = adpt.sr_instance_id
2478: AND peg1.end_pegging_id = adpt.end_pegging_id
2479: AND peg1.prev_pegging_id = adpt.pegging_id
2505: END IF;
2506: -- Obtain the data for the components of the lower level configuration item(s)
2507: -- in the sales order.
2508: FORALL n_idx IN 1..l_multi_config_count
2509: INSERT INTO MSC_ATP_DETAIL_PEG_TEMP (
2510: reference_item_id, base_item_id,
2511: inventory_item_id,
2512: plan_id,
2513: sr_instance_id,
2575: d1.origination_type,
2576: -- Bug 3362558 use pegging's original demand_id
2577: NVL(d1.original_demand_id, adpt.original_demand_id) original_demand_id,
2578: -- Begin Bug 3319810
2579: -- Use pegging's supply data instead of msc_atp_detail_peg_temp.
2580: adpt.process_seq_id,
2581: --adpt.supply_id,
2582: --adpt.supply_date,
2583: --adpt.supply_quantity,
2597: peg1.end_pegging_id, peg1.pegging_id, peg1.prev_pegging_id,
2598: NULL fcst_organization_id, NULL forecast_qty,
2599: NULL consumed_qty, NULL overconsumption_qty
2600: FROM
2601: msc_atp_detail_peg_temp adpt,
2602: msc_full_pegging peg1 ,
2603: msc_demands d1,
2604: msc_system_items msi,
2605: msc_process_effectivity proc,
2621: AND adpt.end_pegging_id = L_Config_Sup.END_PEGGING_ID(n_idx)
2622: AND adpt.base_item_id = L_Config_Sup.BASE_ITEM_ID(n_idx)
2623: AND adpt.supply_date is not NULL
2624: -- End Bug 3750638
2625: -- Link up pegging with config_item info in msc_atp_detail_peg_temp.
2626: AND peg1.plan_id = adpt.plan_id
2627: AND peg1.sr_instance_id = adpt.sr_instance_id
2628: AND peg1.end_pegging_id = adpt.end_pegging_id
2629: AND peg1.prev_pegging_id = adpt.pegging_id
2676: END IF;
2677:
2678:
2679: -- update the supplies for the new demands
2680: UPDATE MSC_ATP_DETAIL_PEG_TEMP madpt
2681: SET
2682: ( process_seq_id, supply_id,
2683: supply_date,
2684: supply_quantity,
2709: 0)) -- 0 otherwise not released.
2710: -- End Bug 3717618
2711: )),
2712: 1) exclude_flag
2713: FROM --msc_atp_detail_peg_temp madpt,
2714: msc_full_pegging peg,
2715: msc_supplies SUP
2716: WHERE madpt.plan_id = p_plan_id
2717: AND madpt.sr_instance_id = c_items_rec.sr_instance_id
3351: -- CTO-PF
3352: atp_peg_det.atf_date,
3353: atp_peg_det.product_family_id
3354: FROM
3355: msc_atp_detail_peg_temp
3356: WHERE plan_id = p_plan_id
3357: AND sr_instance_id (+) = c_items_rec.sr_instance_id -- outer join to get all instances
3358: AND reference_item_id = c_items_rec.inventory_item_id
3359: AND sales_order_line_id = c_items_rec.sales_order_line_id