[Home] [Help]
2645:
2646: -- Initializing API return code
2647: x_return_status := FND_API.G_RET_STS_SUCCESS;
2648:
2649: INSERT INTO msc_atp_sd_details_temp (
2650: ATP_Level,
2651: Order_line_id,
2652: Scenario_Id,
2653: Inventory_Item_Id,
3058: END Get_Mat_Avail_Pf_Ods_Dtls;
3059:
3060: /*--Get_Mat_Avail_Pf_Pds_Dtls-----------------------------------------------------------
3061: | o Called for unallocated Time Phased PF ATP with Details.
3062: | o The supply demand SQL inserts following in msc_atp_sd_details_temp table:
3063: | - Bucketed demands (origination type 51) for member item upto ATF from
3064: | msc_alloc_demands table.
3065: | - Bucketed demands for family after ATF from msc_alloc_demands table.
3066: | - Rollup supplies (order type 50) for member item upto ATF from
3067: | msc_alloc_supplies table.
3068: | - Rollup supplies for family after ATF from msc_alloc_supplies table.
3069: | o Other important differences from non PF SQLs are:
3070: | - Columns Pf_Display_Flag, Original_Demand_Quantity and Original_Demand_Date
3071: | in msc_atp_sd_details_temp table are populated for demands.
3072: | - Column Original_Supply_Demand_Type is populated for demands and supplies
3073: | and stores the supply demand type of parent supplies and demands.
3074: +-------------------------------------------------------------------------------------*/
3075: PROCEDURE Get_Mat_Avail_Pf_Pds_Dtls (
3096:
3097: -- Initializing API return code
3098: x_return_status := FND_API.G_RET_STS_SUCCESS;
3099:
3100: INSERT INTO msc_atp_sd_details_temp (
3101: ATP_Level,
3102: Order_line_id,
3103: Scenario_Id,
3104: Inventory_Item_Id,
4371:
4372: /*--Item_Alloc_Avail_Pf_Dtls-------------------------------------------------------------
4373: | o Called from Item_Alloc_Cum_Atp procedure for Time Phased Rule Based AATP
4374: | scenarios.
4375: | o The supply demand SQL inserts following in msc_atp_sd_details_temp table:
4376: | - Allocated Bucketed demands (origination type 51) for member item upto ATF
4377: | from msc_alloc_demands table.
4378: | - Allocated Bucketed demands for family after ATF from msc_alloc_demands table.
4379: | - Allocated Rollup supplies (order type 50) for member item upto ATF from
4380: | msc_alloc_supplies table.
4381: | - Allocated Rollup supplies for family after ATF from msc_alloc_supplies table.
4382: | o Other important differences from non PF SQLs are:
4383: | - Columns Pf_Display_Flag, Original_Demand_Quantity and Original_Demand_Date
4384: | in msc_atp_sd_details_temp table are populated for demands.
4385: | - Column Original_Supply_Demand_Type is populated for demands and supplies
4386: | and stores the supply demand type of parent supplies and demands.
4387: +--------------------------------------------------------------------------------------*/
4388: PROCEDURE Item_Alloc_Avail_Pf_Dtls (
4427:
4428: -- Initializing API return code
4429: x_return_status := FND_API.G_RET_STS_SUCCESS;
4430:
4431: INSERT INTO msc_atp_sd_details_temp (
4432: ATP_Level,
4433: Order_line_id,
4434: Scenario_Id,
4435: Inventory_Item_Id,
5559:
5560: -- Initializing API return code
5561: x_return_status := FND_API.G_RET_STS_SUCCESS;
5562:
5563: INSERT INTO msc_atp_sd_details_temp (
5564: ATP_Level,
5565: Order_line_id,
5566: Scenario_Id,
5567: Inventory_Item_Id,
7377: sum(allocated_quantity)
7378: FROM msc_alloc_demands
7379: WHERE parent_demand_id in
7380: (SELECT identifier3
7381: FROM msc_atp_sd_details_temp
7382: WHERE supply_demand_type = 1)
7383: AND plan_id = p_plan_id
7384: AND demand_class = nvl(p_demand_class, demand_class)
7385: GROUP BY
7384: AND demand_class = nvl(p_demand_class, demand_class)
7385: GROUP BY
7386: parent_demand_id;
7387:
7388: /* Now update original demand qtys in msc_atp_sd_details_temp table*/
7389: UPDATE msc_atp_sd_details_temp masddt
7390: SET masddt.original_demand_quantity =
7391: (select -1*mat.supply_demand_quantity
7392: from msc_alloc_temp mat
7385: GROUP BY
7386: parent_demand_id;
7387:
7388: /* Now update original demand qtys in msc_atp_sd_details_temp table*/
7389: UPDATE msc_atp_sd_details_temp masddt
7390: SET masddt.original_demand_quantity =
7391: (select -1*mat.supply_demand_quantity
7392: from msc_alloc_temp mat
7393: where mat.demand_id = masddt.identifier3)
7798:
7799: /*--Get_Period_Data_From_Sd_Temp--------------------------------------------
7800: | o This procedure is called from Item_Alloc_Cum_Atp and
7801: | Item_Pre_Allocated_Atp procedures to get the period data from
7802: | msc_atp_sd_details_temp table.
7803: | o Differences from the one in MSC_ATP_PROC:
7804: | - For Total_Demand_Quantity only demands with Pf_Display_Flag equal
7805: | to 1 are looked at. Demand quantity is picked from
7806: | Original_Demand_Quantity column.
7881: ,(DECODE(supply_demand_type, 2,
7882: allocated_quantity,
7883: 0)) supply_quantity
7884: ,allocated_quantity period_quantity
7885: FROM msc_atp_sd_details_temp
7886:
7887: UNION ALL
7888:
7889: SELECT
7902: ,original_demand_quantity demand_quantity
7903: ,0 bucketed_demand_quantity
7904: ,0 supply_quantity
7905: ,0 period_quantity
7906: FROM msc_atp_sd_details_temp
7907: WHERE supply_demand_type = 1
7908: AND pf_display_flag = 1
7909: AND trunc(supply_demand_date) <> trunc(original_demand_date) --Bug_3693892 added trunc
7910: ) SD_View
8039: allocated_quantity,
8040: 0) supply_quantity
8041: ,allocated_quantity period_quantity
8042: ,unallocated_quantity
8043: FROM msc_atp_sd_details_temp
8044:
8045: UNION ALL
8046:
8047: SELECT
8061: ,0 bucketed_demand_quantity
8062: ,0 supply_quantity
8063: ,0 period_quantity
8064: ,0 unallocated_quantity
8065: FROM msc_atp_sd_details_temp
8066: WHERE supply_demand_type = 1
8067: AND pf_display_flag = 1
8068: AND trunc(supply_demand_date) <> trunc(original_demand_date) --Bug_3693892 added trunc
8069: ) SD_View