[Home] [Help]
2633:
2634: -- Initializing API return code
2635: x_return_status := FND_API.G_RET_STS_SUCCESS;
2636:
2637: INSERT INTO msc_atp_sd_details_temp (
2638: ATP_Level,
2639: Order_line_id,
2640: Scenario_Id,
2641: Inventory_Item_Id,
3041: END Get_Mat_Avail_Pf_Ods_Dtls;
3042:
3043: /*--Get_Mat_Avail_Pf_Pds_Dtls-----------------------------------------------------------
3044: | o Called for unallocated Time Phased PF ATP with Details.
3045: | o The supply demand SQL inserts following in msc_atp_sd_details_temp table:
3046: | - Bucketed demands (origination type 51) for member item upto ATF from
3047: | msc_alloc_demands table.
3048: | - Bucketed demands for family after ATF from msc_alloc_demands table.
3049: | - Rollup supplies (order type 50) for member item upto ATF from
3050: | msc_alloc_supplies table.
3051: | - Rollup supplies for family after ATF from msc_alloc_supplies table.
3052: | o Other important differences from non PF SQLs are:
3053: | - Columns Pf_Display_Flag, Original_Demand_Quantity and Original_Demand_Date
3054: | in msc_atp_sd_details_temp table are populated for demands.
3055: | - Column Original_Supply_Demand_Type is populated for demands and supplies
3056: | and stores the supply demand type of parent supplies and demands.
3057: +-------------------------------------------------------------------------------------*/
3058: PROCEDURE Get_Mat_Avail_Pf_Pds_Dtls (
3079:
3080: -- Initializing API return code
3081: x_return_status := FND_API.G_RET_STS_SUCCESS;
3082:
3083: INSERT INTO msc_atp_sd_details_temp (
3084: ATP_Level,
3085: Order_line_id,
3086: Scenario_Id,
3087: Inventory_Item_Id,
4348:
4349: /*--Item_Alloc_Avail_Pf_Dtls-------------------------------------------------------------
4350: | o Called from Item_Alloc_Cum_Atp procedure for Time Phased Rule Based AATP
4351: | scenarios.
4352: | o The supply demand SQL inserts following in msc_atp_sd_details_temp table:
4353: | - Allocated Bucketed demands (origination type 51) for member item upto ATF
4354: | from msc_alloc_demands table.
4355: | - Allocated Bucketed demands for family after ATF from msc_alloc_demands table.
4356: | - Allocated Rollup supplies (order type 50) for member item upto ATF from
4357: | msc_alloc_supplies table.
4358: | - Allocated Rollup supplies for family after ATF from msc_alloc_supplies table.
4359: | o Other important differences from non PF SQLs are:
4360: | - Columns Pf_Display_Flag, Original_Demand_Quantity and Original_Demand_Date
4361: | in msc_atp_sd_details_temp table are populated for demands.
4362: | - Column Original_Supply_Demand_Type is populated for demands and supplies
4363: | and stores the supply demand type of parent supplies and demands.
4364: +--------------------------------------------------------------------------------------*/
4365: PROCEDURE Item_Alloc_Avail_Pf_Dtls (
4404:
4405: -- Initializing API return code
4406: x_return_status := FND_API.G_RET_STS_SUCCESS;
4407:
4408: INSERT INTO msc_atp_sd_details_temp (
4409: ATP_Level,
4410: Order_line_id,
4411: Scenario_Id,
4412: Inventory_Item_Id,
5533:
5534: -- Initializing API return code
5535: x_return_status := FND_API.G_RET_STS_SUCCESS;
5536:
5537: INSERT INTO msc_atp_sd_details_temp (
5538: ATP_Level,
5539: Order_line_id,
5540: Scenario_Id,
5541: Inventory_Item_Id,
7351: sum(allocated_quantity)
7352: FROM msc_alloc_demands
7353: WHERE parent_demand_id in
7354: (SELECT identifier3
7355: FROM msc_atp_sd_details_temp
7356: WHERE supply_demand_type = 1)
7357: AND plan_id = p_plan_id
7358: AND demand_class = nvl(p_demand_class, demand_class)
7359: GROUP BY
7358: AND demand_class = nvl(p_demand_class, demand_class)
7359: GROUP BY
7360: parent_demand_id;
7361:
7362: /* Now update original demand qtys in msc_atp_sd_details_temp table*/
7363: UPDATE msc_atp_sd_details_temp masddt
7364: SET masddt.original_demand_quantity =
7365: (select -1*mat.supply_demand_quantity
7366: from msc_alloc_temp mat
7359: GROUP BY
7360: parent_demand_id;
7361:
7362: /* Now update original demand qtys in msc_atp_sd_details_temp table*/
7363: UPDATE msc_atp_sd_details_temp masddt
7364: SET masddt.original_demand_quantity =
7365: (select -1*mat.supply_demand_quantity
7366: from msc_alloc_temp mat
7367: where mat.demand_id = masddt.identifier3)
7772:
7773: /*--Get_Period_Data_From_Sd_Temp--------------------------------------------
7774: | o This procedure is called from Item_Alloc_Cum_Atp and
7775: | Item_Pre_Allocated_Atp procedures to get the period data from
7776: | msc_atp_sd_details_temp table.
7777: | o Differences from the one in MSC_ATP_PROC:
7778: | - For Total_Demand_Quantity only demands with Pf_Display_Flag equal
7779: | to 1 are looked at. Demand quantity is picked from
7780: | Original_Demand_Quantity column.
7855: ,(DECODE(supply_demand_type, 2,
7856: allocated_quantity,
7857: 0)) supply_quantity
7858: ,allocated_quantity period_quantity
7859: FROM msc_atp_sd_details_temp
7860:
7861: UNION ALL
7862:
7863: SELECT
7876: ,original_demand_quantity demand_quantity
7877: ,0 bucketed_demand_quantity
7878: ,0 supply_quantity
7879: ,0 period_quantity
7880: FROM msc_atp_sd_details_temp
7881: WHERE supply_demand_type = 1
7882: AND pf_display_flag = 1
7883: AND trunc(supply_demand_date) <> trunc(original_demand_date) --Bug_3693892 added trunc
7884: ) SD_View
8013: allocated_quantity,
8014: 0) supply_quantity
8015: ,allocated_quantity period_quantity
8016: ,unallocated_quantity
8017: FROM msc_atp_sd_details_temp
8018:
8019: UNION ALL
8020:
8021: SELECT
8035: ,0 bucketed_demand_quantity
8036: ,0 supply_quantity
8037: ,0 period_quantity
8038: ,0 unallocated_quantity
8039: FROM msc_atp_sd_details_temp
8040: WHERE supply_demand_type = 1
8041: AND pf_display_flag = 1
8042: AND trunc(supply_demand_date) <> trunc(original_demand_date) --Bug_3693892 added trunc
8043: ) SD_View