[Home] [Help]
512: END IF;
513: END IF;
514:
515: IF l_update_mem_bd = 'Y' THEN
516: update msc_alloc_demands
517: set allocated_quantity = nvl(l_mem_bd_qty, allocated_quantity),
518: demand_quantity = nvl(p_new_demand_date_qty, demand_quantity)
519: where parent_demand_id = p_parent_demand_id
520: --bug3693892 added trunc
522: and plan_id = p_plan_id;
523: END IF;
524:
525: IF l_update_pf_bd = 'Y' THEN
526: update msc_alloc_demands
527: set allocated_quantity = allocated_quantity - l_pf_bd_decrement_qty,
528: demand_quantity = nvl(p_new_demand_date_qty, demand_quantity)
529: where parent_demand_id = p_parent_demand_id
530: --bug3693892 added trunc
530: --bug3693892 added trunc
531: and trunc(demand_date) > p_atf_date
532: and plan_id = p_plan_id;
533: ELSIF l_update_pf_bd = 'D' THEN
534: delete msc_alloc_demands
535: where parent_demand_id = p_parent_demand_id
536: --bug3693892 added trunc
537: and trunc(demand_date) > p_atf_date
538: and plan_id = p_plan_id;
622: IF nvl(l_bucketed_demands_rec.mem_bd_qty, 0) = 0 THEN
623: IF PG_DEBUG in ('Y', 'C') THEN
624: msc_sch_wb.atp_debug('Increment_Bucketed_Demands_Qty: Deleting member item bucketed demand...');
625: END IF;
626: delete msc_alloc_demands
627: where parent_demand_id = p_parent_demand_id
628: and inventory_item_id = p_atp_rec.request_item_id
629: and plan_id = p_plan_id;
630: ELSIF l_bucketed_demands_rec.mem_bd_qty > 0 THEN
627: where parent_demand_id = p_parent_demand_id
628: and inventory_item_id = p_atp_rec.request_item_id
629: and plan_id = p_plan_id;
630: ELSIF l_bucketed_demands_rec.mem_bd_qty > 0 THEN
631: update msc_alloc_demands
632: set allocated_quantity = l_bucketed_demands_rec.mem_bd_qty,
633: demand_quantity = l_req_qty,
634: --bug3697365 added timestamp also
635: demand_date = trunc(l_bucketed_demands_rec.mem_bd_date) + MSC_ATP_PVT.G_END_OF_DAY,
667: IF nvl(l_bucketed_demands_rec.pf_bd_qty,0) = 0 THEN
668: IF PG_DEBUG in ('Y', 'C') THEN
669: msc_sch_wb.atp_debug('Increment_Bucketed_Demands_Qty: Deleting family item bucketed demand...');
670: END IF;
671: delete msc_alloc_demands
672: where parent_demand_id = p_parent_demand_id
673: and inventory_item_id = p_atp_rec.inventory_item_id
674: and plan_id = p_plan_id;
675: ELSIF l_bucketed_demands_rec.pf_bd_qty > 0 THEN
672: where parent_demand_id = p_parent_demand_id
673: and inventory_item_id = p_atp_rec.inventory_item_id
674: and plan_id = p_plan_id;
675: ELSIF l_bucketed_demands_rec.pf_bd_qty > 0 THEN
676: update msc_alloc_demands
677: set allocated_quantity = l_bucketed_demands_rec.pf_bd_qty,
678: demand_quantity = l_req_qty,
679: --bug3697365 added timestamp also
680: demand_date = trunc(l_bucketed_demands_rec.pf_bd_date) + MSC_ATP_PVT.G_END_OF_DAY,
784: IF PG_DEBUG in ('Y', 'C') THEN
785: msc_sch_wb.atp_debug('Move_PF_Bucketed_Demands: No need to update qtys');
786: END IF;
787: ELSIF p_old_demand_date <= p_atf_date THEN
788: update msc_alloc_demands
789: set allocated_quantity = p_demand_qty,
790: demand_quantity = p_demand_qty
791: where parent_demand_id = p_parent_demand_id
792: and demand_date <= p_atf_date
799: msc_sch_wb.atp_debug('* Family Item BD Qty = ' || (p_demand_qty - nvl(p_atf_date_qty, 0)));
800: msc_sch_wb.atp_debug('*********************************************');
801: END IF;
802: ELSE
803: update msc_alloc_demands
804: set allocated_quantity = nvl(p_atf_date_qty, 0),
805: demand_quantity = p_demand_qty
806: where parent_demand_id = p_parent_demand_id
807: and demand_date <= p_atf_date
806: where parent_demand_id = p_parent_demand_id
807: and demand_date <= p_atf_date
808: and plan_id = p_plan_id;
809:
810: update msc_alloc_demands
811: set allocated_quantity = p_demand_qty - nvl(p_atf_date_qty, 0),
812: demand_quantity = p_demand_qty
813: where parent_demand_id = p_parent_demand_id
814: and demand_date > p_atf_date
887: msc_sch_wb.atp_debug('*************************************');
888: END IF;
889:
890: IF l_update_mem_bd = 'Y' THEN
891: update msc_alloc_demands
892: --bug3697365 added timestamp also
893: set demand_date = trunc(l_mem_bd_date) + MSC_ATP_PVT.G_END_OF_DAY,
894: allocated_quantity = l_mem_bd_qty,
895: demand_quantity = p_demand_qty,
928: END IF;
929: END IF;
930:
931: IF l_update_pf_bd = 'Y' THEN
932: update msc_alloc_demands
933: --bug3697365 added timestamp also
934: set demand_date = trunc(l_pf_bd_date) + MSC_ATP_PVT.G_END_OF_DAY,
935: allocated_quantity = l_pf_bd_qty,
936: demand_quantity = p_demand_qty,
1049: msc_sch_wb.atp_debug('*************************************');
1050: END IF;
1051:
1052: IF l_update_mem_bd = 'Y' THEN
1053: update msc_alloc_demands
1054: --bug3697365 added timestamp also
1055: set demand_date = trunc(l_mem_bd_date) + MSC_ATP_PVT.G_END_OF_DAY,
1056: allocated_quantity = l_mem_bd_qty,
1057: demand_quantity = p_demand_qty,
1089: return;
1090: END IF;
1091: END IF;
1092: ELSIF l_update_mem_bd = 'D' THEN
1093: delete msc_alloc_demands
1094: where parent_demand_id = p_parent_demand_id
1095: --bug3693892 added trunc
1096: and trunc(demand_date) <= p_atf_date
1097: and plan_id = p_plan_id;
1097: and plan_id = p_plan_id;
1098: END IF;
1099:
1100: IF l_update_pf_bd = 'Y' THEN
1101: update msc_alloc_demands
1102: --bug3697365 added timestamp also
1103: set demand_date = trunc(l_pf_bd_date) + MSC_ATP_PVT.G_END_OF_DAY,
1104: allocated_quantity = l_pf_bd_qty,
1105: demand_quantity = p_demand_qty,
1173: -- Initializing API return code
1174: x_return_status := FND_API.G_RET_STS_SUCCESS;
1175:
1176: IF (p_old_demand_date < p_atf_date) and (p_new_demand_date <= p_atf_date) THEN
1177: update msc_alloc_demands
1178: --bug3697365 added timestamp also
1179: set demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1180: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY
1181: where parent_demand_id = p_parent_demand_id
1182: --bug3693892 added trunc
1183: and trunc(demand_date) <= p_atf_date
1184: and plan_id = p_plan_id;
1185: ELSIF (p_old_demand_date < p_atf_date) and (p_new_demand_date > p_atf_date) THEN
1186: update msc_alloc_demands
1187: --bug3693892 added trunc
1188: set demand_date = trunc(p_atf_date) + MSC_ATP_PVT.G_END_OF_DAY,
1189: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1190: Pf_Display_Flag = null
1192: --bug3693892 added trunc
1193: and trunc(demand_date) <= p_atf_date
1194: and plan_id = p_plan_id;
1195:
1196: update msc_alloc_demands
1197: --bug3693892 added trunc
1198: set demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1199: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1200: Pf_Display_Flag = 1
1202: --bug3693892 added trunc
1203: and trunc(demand_date) > p_atf_date
1204: and plan_id = p_plan_id;
1205: ELSIF (p_old_demand_date > p_atf_date) and (p_new_demand_date > p_atf_date) THEN
1206: update msc_alloc_demands
1207: --bug3693892 added trunc
1208: set demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1209: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY
1210: where parent_demand_id = p_parent_demand_id
1226:
1227: END Move_PF_Bd_Dates;
1228:
1229: /*--Insert_Bucketed_Demand--------------------------------------------------
1230: | o This procedure inserts bucketed demand in msc_alloc_demands table
1231: | with origination type 51 (ATP Bucketed Demand).
1232: +-------------------------------------------------------------------------*/
1233: PROCEDURE Insert_Bucketed_Demand(
1234: p_atp_rec IN MRP_ATP_PVT.AtpRec,
1251:
1252: -- Initializing API return code
1253: x_return_status := FND_API.G_RET_STS_SUCCESS;
1254:
1255: INSERT INTO MSC_ALLOC_DEMANDS(
1256: PLAN_ID,
1257: INVENTORY_ITEM_ID,
1258: ORIGINAL_ITEM_ID,
1259: USING_ASSEMBLY_ITEM_ID,
1349: SELECT allocated_quantity,
1350: demand_date
1351: INTO p_bucketed_demands_rec.mem_bd_qty,
1352: p_bucketed_demands_rec.mem_bd_date
1353: FROM msc_alloc_demands
1354: WHERE plan_id = p_plan_id
1355: AND parent_demand_id = p_parent_demand_id
1356: AND inventory_item_id = p_bucketed_demands_rec.mem_item_id
1357: ;
1370: SELECT allocated_quantity,
1371: demand_date
1372: INTO p_bucketed_demands_rec.pf_bd_qty,
1373: p_bucketed_demands_rec.pf_bd_date
1374: FROM msc_alloc_demands
1375: WHERE plan_id = p_plan_id
1376: AND parent_demand_id = p_parent_demand_id
1377: AND inventory_item_id = p_bucketed_demands_rec.pf_item_id
1378: ;
2413: /*--Get_Mat_Avail_Pf_Pds_Summ----------------------------------------------------
2414: | o Called for unallocated time phased PF atp for PDS summary
2415: | o Differences from non summary SQL are :
2416: | - Additional union with MSC_ATP_SUMMARY_SD
2417: | - Decode in quantity in SQL on msc_alloc_demands to consider unscheduled
2418: | orders as dummy supplies
2419: | - Additional join with MSC_PLANS in the SQLs on supplies and demands to
2420: | filter records based on refresh number
2421: | - Filter on allocated_quantity=0 and origination_type=51 removed in the
2464: SELECT TRUNC(AD.DEMAND_DATE) SD_DATE,
2465: decode(AD.ALLOCATED_QUANTITY, -- Consider unscheduled orders as dummy supplies
2466: 0, OLD_ALLOCATED_QUANTITY, -- For summary enhancement
2467: -1 * AD.ALLOCATED_QUANTITY) SD_QTY
2468: FROM MSC_ALLOC_DEMANDS AD,
2469: MSC_SYSTEM_ITEMS I,
2470: MSC_PLANS P -- For summary enhancement
2471: WHERE I.SR_INVENTORY_ITEM_ID in (p_sr_member_id, p_sr_family_id)
2472: AND I.ORGANIZATION_ID = p_org_id
2519: /*--Get_Mat_Avail_Pf_Pds---------------------------------------------------------
2520: | o Called for unallocated Time Phased PF ATP
2521: | o The supply demand SQL in this procedure gets following:
2522: | - Bucketed demands (origination type 51) for member item upto ATF from
2523: | msc_alloc_demands table.
2524: | - Bucketed demands for family after ATF from msc_alloc_demands table.
2525: | - Rollup supplies (order type 50) for member item upto ATF from
2526: | msc_alloc_supplies table.
2527: | - Rollup supplies for family after ATF from msc_alloc_supplies table.
2520: | o Called for unallocated Time Phased PF ATP
2521: | o The supply demand SQL in this procedure gets following:
2522: | - Bucketed demands (origination type 51) for member item upto ATF from
2523: | msc_alloc_demands table.
2524: | - Bucketed demands for family after ATF from msc_alloc_demands table.
2525: | - Rollup supplies (order type 50) for member item upto ATF from
2526: | msc_alloc_supplies table.
2527: | - Rollup supplies for family after ATF from msc_alloc_supplies table.
2528: +------------------------------------------------------------------------------*/
2551: FROM (
2552: --bug3700564 added trunc
2553: SELECT TRUNC(AD.DEMAND_DATE) SD_DATE,
2554: -1 * AD.ALLOCATED_QUANTITY SD_QTY
2555: FROM MSC_ALLOC_DEMANDS AD,
2556: MSC_SYSTEM_ITEMS I
2557: WHERE I.SR_INVENTORY_ITEM_ID in (p_sr_member_id, p_sr_family_id)
2558: AND I.ORGANIZATION_ID = p_org_id
2559: AND I.SR_INSTANCE_ID = p_instance_id
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.
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:
3175: I2.Item_Name --bug3579625
3176:
3177: FROM MSC_SYSTEM_ITEMS I,
3178: MSC_SYSTEM_ITEMS I2, --bug3579625
3179: MSC_ALLOC_DEMANDS AD,
3180: MSC_TRADING_PARTNERS MTP,--bug3263368
3181: MSC_TRADING_PARTNER_SITES MTPS --bug3263368
3182: WHERE I.SR_INVENTORY_ITEM_ID in (p_sr_member_id, p_sr_family_id)
3183: AND I.ORGANIZATION_ID = p_org_id
3412: | o Called from Item_Alloc_Cum_Atp procedure for Rule based Allocated
3413: | Time Phased PF ATP.
3414: | o The supply demand SQL in this procedure gets following:
3415: | - Allocated Bucketed demands (origination type 51) for member item
3416: | upto ATF from msc_alloc_demands table.
3417: | - Allocated Bucketed demands for family after ATF from msc_alloc_demands
3418: | table.
3419: | - Allocated Rollup supplies (order type 50) for member item upto ATF
3420: | from msc_alloc_supplies table.
3413: | Time Phased PF ATP.
3414: | o The supply demand SQL in this procedure gets following:
3415: | - Allocated Bucketed demands (origination type 51) for member item
3416: | upto ATF from msc_alloc_demands table.
3417: | - Allocated Bucketed demands for family after ATF from msc_alloc_demands
3418: | table.
3419: | - Allocated Rollup supplies (order type 50) for member item upto ATF
3420: | from msc_alloc_supplies table.
3421: | - Allocated Rollup supplies for family after ATF from msc_alloc_supplies
3769: p_family_id)), p_org_id, p_instance_id,
3770: trunc(AD.Demand_Date),p_level_id, NULL)))),
3771: p_demand_class,
3772: p_level_id),0)) SD_QTY --4365873
3773: FROM MSC_ALLOC_DEMANDS AD
3774: WHERE AD.PLAN_ID = p_plan_id
3775: AND AD.SR_INSTANCE_ID = p_instance_id
3776: AND AD.INVENTORY_ITEM_ID in (p_member_id,p_family_id)
3777: AND AD.ORGANIZATION_ID = p_org_id
4235: p_family_id)), p_org_id, p_instance_id,
4236: trunc(AD.Demand_Date),p_level_id, NULL)))),
4237: p_demand_class,
4238: p_level_id),0)) SD_QTY --4365873
4239: FROM MSC_ALLOC_DEMANDS AD
4240: WHERE AD.PLAN_ID = p_plan_id
4241: AND AD.SR_INSTANCE_ID = p_instance_id
4242: AND AD.INVENTORY_ITEM_ID in (p_member_id,p_family_id)
4243: AND AD.ORGANIZATION_ID = p_org_id
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.
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:
5124: MTP.PARTNER_NAME, --bug3263368
5125: AD.DEMAND_CLASS, --bug3263368
5126: AD.REQUEST_DATE, --bug3263368
5127: I.Item_Name -- bug3579625
5128: FROM MSC_ALLOC_DEMANDS AD,
5129: MSC_TRADING_PARTNERS MTP,--bug3263368
5130: MSC_TRADING_PARTNER_SITES MTPS, --bug3263368
5131: MSC_SYSTEM_ITEMS I --bug3579625
5132: WHERE AD.PLAN_ID = p_plan_id
5354: x_atp_qtys
5355: FROM (
5356: SELECT TRUNC(AD.DEMAND_DATE) SD_DATE,
5357: -1 * AD.ALLOCATED_QUANTITY SD_QTY
5358: FROM MSC_ALLOC_DEMANDS AD
5359: WHERE AD.PLAN_ID = p_plan_id
5360: AND AD.SR_INSTANCE_ID = p_instance_id
5361: AND AD.INVENTORY_ITEM_ID in (p_member_id, p_family_id)
5362: AND AD.ORIGINATION_TYPE <> 52 -- ATP Bucketed Demand
5446: SELECT TRUNC(AD.DEMAND_DATE) SD_DATE,
5447: decode(AD.ALLOCATED_QUANTITY, -- Consider unscheduled orders as dummy supplies
5448: 0, OLD_ALLOCATED_QUANTITY,-- For summary enhancement
5449: -1 * AD.ALLOCATED_QUANTITY) SD_QTY
5450: FROM MSC_ALLOC_DEMANDS AD,
5451: MSC_PLANS P -- For summary enhancement
5452: WHERE AD.PLAN_ID = p_plan_id
5453: AND AD.SR_INSTANCE_ID = p_instance_id
5454: AND AD.INVENTORY_ITEM_ID in (p_member_id, p_family_id)
5632: MTP.PARTNER_NAME, --bug3263368
5633: AD.DEMAND_CLASS, --bug3263368
5634: AD.REQUEST_DATE, --bug3263368
5635: I.Item_Name --bug3579625
5636: FROM MSC_ALLOC_DEMANDS AD,
5637: MSC_TRADING_PARTNERS MTP,--bug3263368
5638: MSC_TRADING_PARTNER_SITES MTPS, --bug3263368
5639: MSC_SYSTEM_ITEMS I --bug3579625
5640: WHERE AD.PLAN_ID = p_plan_id
5795: (
5796: SELECT TRUNC(AD.DEMAND_DATE) SD_DATE,
5797: -1 * AD.ALLOCATED_QUANTITY SD_QTY,
5798: AD.DEMAND_CLASS
5799: FROM MSC_ALLOC_DEMANDS AD
5800: WHERE AD.PLAN_ID = p_plan_id
5801: AND AD.SR_INSTANCE_ID = p_instance_id
5802: AND AD.INVENTORY_ITEM_ID in (p_member_id, p_family_id)
5803: AND AD.ORGANIZATION_ID = p_org_id
5904: decode(AD.ALLOCATED_QUANTITY, -- Consider unscheduled orders as dummy supplies
5905: 0, nvl(OLD_ALLOCATED_QUANTITY,0), --4658238 -- For summary enhancement
5906: -1 * AD.ALLOCATED_QUANTITY) SD_QTY,
5907: AD.DEMAND_CLASS
5908: FROM MSC_ALLOC_DEMANDS AD,
5909: MSC_PLANS P -- For summary enhancement
5910: WHERE AD.PLAN_ID = p_plan_id
5911: AND AD.SR_INSTANCE_ID = p_instance_id
5912: AND AD.INVENTORY_ITEM_ID in (p_member_id, p_family_id)
6064: MTP.PARTNER_NAME col28, --bug3263368
6065: AD.DEMAND_CLASS col29, --bug3263368
6066: AD.REQUEST_DATE col30 --bug3263368
6067: FROM
6068: MSC_ALLOC_DEMANDS AD,
6069: MSC_ALLOC_TEMP TEMP,
6070: MSC_TRADING_PARTNERS MTP,--bug3263368
6071: MSC_TRADING_PARTNER_SITES MTPS, --bug3263368
6072: MSC_SYSTEM_ITEMS msi --bug3671294
6624: MTP.PARTNER_NAME col29, --bug3263368
6625: AD.DEMAND_CLASS col30, --bug3263368
6626: AD.REQUEST_DATE col31 --bug3263368
6627: FROM
6628: MSC_ALLOC_DEMANDS AD,
6629: MSC_ALLOC_TEMP TEMP,
6630: MSC_TRADING_PARTNERS MTP,--bug3263368
6631: MSC_TRADING_PARTNER_SITES MTPS, --bug3263368
6632: MSC_SYSTEM_ITEMS msi --bug3671294
7135: MTP.PARTNER_NAME col34, --bug3263368
7136: AD.DEMAND_CLASS col35, --bug3263368
7137: AD.REQUEST_DATE col36 --bug3263368
7138: FROM
7139: MSC_ALLOC_DEMANDS AD,
7140: MSC_ALLOC_HIERARCHY_TEMP TEMP,
7141: MSC_TRADING_PARTNERS MTP,--bug3263368
7142: MSC_TRADING_PARTNER_SITES MTPS, --bug3263368
7143: MSC_SYSTEM_ITEMS msi --bug3671294
7314: )
7315: (SELECT demand_class,
7316: parent_demand_id,
7317: sum(allocated_quantity)
7318: FROM msc_alloc_demands
7319: WHERE (demand_class, parent_demand_id) in
7320: (SELECT demand_class,
7321: identifier3
7322: FROM mrp_atp_details_temp
7348: supply_demand_quantity
7349: )
7350: SELECT parent_demand_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)
9368: l_alloc_temp_table := 'MSC_ALLOC_TEMP_' || to_char(p_plan_id);
9369:
9370: msc_util.msc_log('temp table : ' || l_alloc_temp_table);
9371:
9372: /* Create temp table in tablespace of MSC_ALLOC_DEMANDS*/
9373: SELECT t.tablespace_name, NVL(i.def_tablespace_name, t.tablespace_name)
9374: INTO l_tbspace, l_ind_tbspace
9375: FROM all_tab_partitions t,
9376: all_part_indexes i
9374: INTO l_tbspace, l_ind_tbspace
9375: FROM all_tab_partitions t,
9376: all_part_indexes i
9377: WHERE t.table_owner = l_msc_schema
9378: AND t.table_name = 'MSC_ALLOC_DEMANDS'
9379: AND t.partition_name = 'ALLOC_DEMANDS_' || to_char(l_plan_id)
9380: AND i.owner (+) = t.table_owner
9381: AND i.table_name (+) = t.table_name
9382: AND rownum = 1;
9517:
9518: -- first delete the existing data from tables
9519: msc_util.msc_log('before deleteing data from the table');
9520:
9521: DELETE MSC_ALLOC_DEMANDS where plan_id = p_plan_id;
9522: msc_util.msc_log('After deleting data from MSC_ALLOC_DEMANDS table');
9523:
9524: DELETE MSC_ALLOC_SUPPLIES where plan_id = p_plan_id;
9525: msc_util.msc_log('After deleting data from MSC_ALLOC_SUPPLIES table');
9518: -- first delete the existing data from tables
9519: msc_util.msc_log('before deleteing data from the table');
9520:
9521: DELETE MSC_ALLOC_DEMANDS where plan_id = p_plan_id;
9522: msc_util.msc_log('After deleting data from MSC_ALLOC_DEMANDS table');
9523:
9524: DELETE MSC_ALLOC_SUPPLIES where plan_id = p_plan_id;
9525: msc_util.msc_log('After deleting data from MSC_ALLOC_SUPPLIES table');
9526:
9563: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
9564: msc_util.msc_log('After executing the cursor');
9565:
9566: msc_util.msc_log('rows processed: ' || rows_processed);
9567: msc_util.msc_log('after inserting item data into MSC_ALLOC_DEMANDS tables');
9568:
9569: /*--------------------------------------------------------------------------
9570: | <<<<<<<<<<<<<<<<<<<<<<< Begin Supplies SQL1 >>>>>>>>>>>>>>>>>>>>>>>>>>>
9571: +-------------------------------------------------------------------------*/
9608:
9609: msc_util.msc_log('rows processed: ' || rows_processed);
9610: msc_util.msc_log('after inserting item data into MSC_ALLOC_SUPPLIES tables');
9611:
9612: msc_util.msc_log('Analyze Plan partition for MSC_ALLOC_DEMANDS');
9613: fnd_stats.gather_table_stats(ownname=>'MSC',tabname=>'MSC_ALLOC_DEMANDS',
9614: partname=>'ALLOC_DEMANDS_999999',
9615: granularity=>'PARTITION',
9616: percent =>10);
9609: msc_util.msc_log('rows processed: ' || rows_processed);
9610: msc_util.msc_log('after inserting item data into MSC_ALLOC_SUPPLIES tables');
9611:
9612: msc_util.msc_log('Analyze Plan partition for MSC_ALLOC_DEMANDS');
9613: fnd_stats.gather_table_stats(ownname=>'MSC',tabname=>'MSC_ALLOC_DEMANDS',
9614: partname=>'ALLOC_DEMANDS_999999',
9615: granularity=>'PARTITION',
9616: percent =>10);
9617:
9634: INTO l_tbspace, l_ind_tbspace
9635: FROM all_tab_partitions t,
9636: all_part_indexes i
9637: WHERE t.table_owner = l_msc_schema
9638: AND t.table_name = 'MSC_ALLOC_DEMANDS'
9639: AND t.partition_name = 'ALLOC_DEMANDS_' || to_char(l_plan_id)
9640: AND i.owner (+) = t.table_owner
9641: AND i.table_name (+) = t.table_name
9642: AND rownum = 1;
9648: --bug 6113544
9649: l_insert_stmt := 'CREATE TABLE ' || l_temp_table
9650: || ' TABLESPACE ' || l_tbspace
9651: || ' PCTFREE 0 STORAGE(INITIAL 40K NEXT 5M PCTINCREASE 0)'
9652: || ' as select * from MSC_ALLOC_DEMANDS where 1=2 ';
9653:
9654: /*
9655: l_insert_stmt := 'CREATE TABLE ' || l_temp_table || '(
9656: PLAN_ID NUMBER NOT NULL,
10004:
10005: msc_util.msc_log('swap partition for demands');
10006: l_partition_name := 'ALLOC_DEMANDS_' || to_char(l_plan_id);
10007:
10008: msc_util.msc_log('Partition name for msc_alloc_demands table : ' || l_partition_name);
10009:
10010: -- swap partiton for supplies and demand part
10011:
10012: l_sql_stmt := 'ALTER TABLE msc_alloc_demands EXCHANGE PARTITION ' || l_partition_name ||
10008: msc_util.msc_log('Partition name for msc_alloc_demands table : ' || l_partition_name);
10009:
10010: -- swap partiton for supplies and demand part
10011:
10012: l_sql_stmt := 'ALTER TABLE msc_alloc_demands EXCHANGE PARTITION ' || l_partition_name ||
10013: ' with table MSC_TEMP_ALLOC_DEM_'|| to_char(l_plan_id) ||
10014: ' including indexes without validation';
10015:
10016: BEGIN
10013: ' with table MSC_TEMP_ALLOC_DEM_'|| to_char(l_plan_id) ||
10014: ' including indexes without validation';
10015:
10016: BEGIN
10017: msc_util.msc_log('Before alter table msc_alloc_demands');
10018: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
10019: APPLICATION_SHORT_NAME => 'MSC',
10020: STATEMENT_TYPE => ad_ddl.alter_table,
10021: STATEMENT => l_sql_stmt,
10018: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
10019: APPLICATION_SHORT_NAME => 'MSC',
10020: STATEMENT_TYPE => ad_ddl.alter_table,
10021: STATEMENT => l_sql_stmt,
10022: OBJECT_NAME => 'MSC_ALLOC_DEMANDS');
10023: END;
10024:
10025: msc_util.msc_log('swap partition for supplies');
10026: l_partition_name := 'ALLOC_SUPPLIES_' || to_char(l_plan_id);
10050:
10051: msc_util.msc_log('After procedure clean_temp_tables');
10052: /* forecast at PF changes end*/
10053:
10054: /* Call Update_Pf_Display_Flags to update Pf_Display_Flag in msc_alloc_demands*/
10055: Update_Pf_Display_Flag(p_plan_id, l_return_status);
10056:
10057: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
10058: IF PG_DEBUG in ('Y', 'C') THEN
10137: x_return_status := FND_API.G_RET_STS_SUCCESS;
10138:
10139: IF p_share_partition = 'Y' THEN
10140: x_sql_stmt := '
10141: INSERT INTO MSC_ALLOC_DEMANDS(';
10142: ELSE
10143: x_sql_stmt := '
10144: INSERT INTO ' || p_temp_table || '(';
10145: END IF;
10805: x_return_status := FND_API.G_RET_STS_SUCCESS;
10806:
10807: IF p_share_partition = 'Y' THEN
10808: x_sql_stmt := '
10809: INSERT INTO MSC_ALLOC_DEMANDS(';
10810: ELSE
10811: x_sql_stmt := '
10812: INSERT INTO ' || p_temp_table || '(';
10813: END IF;
11427: x_return_status := FND_API.G_RET_STS_SUCCESS;
11428:
11429: IF p_share_partition = 'Y' THEN
11430: x_sql_stmt := '
11431: INSERT INTO MSC_ALLOC_DEMANDS(';
11432: ELSE
11433: x_sql_stmt := '
11434: INSERT INTO ' || p_temp_table || '(';
11435: END IF;
12147: x_return_status := FND_API.G_RET_STS_SUCCESS;
12148:
12149: -- Performance tuning pending
12150: /*
12151: UPDATE MSC_ALLOC_DEMANDS AD
12152: SET Pf_Display_Flag = 1
12153: WHERE AD.plan_id = p_plan_id
12154: --AND AD.allocated_quantity = Demand_Quantity
12155: AND AD.pf_display_flag is NULL
12154: --AND AD.allocated_quantity = Demand_Quantity
12155: AND AD.pf_display_flag is NULL
12156: AND (AD.parent_demand_id, AD.demand_class, 1) in
12157: (SELECT AD2.parent_demand_id, AD2.demand_class, count(*)
12158: FROM MSC_ALLOC_DEMANDS AD2
12159: WHERE AD2.plan_id = p_plan_id
12160: GROUP BY AD2.parent_demand_id, AD2.demand_class
12161: )
12162: AND EXISTS (SELECT 1
12167: AND I.plan_id = AD.plan_id
12168: AND I.aggregate_time_fence_date is not null);
12169: */
12170: --5631956 Modified SQL tuned for better performance.
12171: UPDATE MSC_ALLOC_DEMANDS AD
12172: SET Pf_Display_Flag = 1
12173: WHERE AD.plan_id = p_plan_id
12174: AND AD.pf_display_flag is NULL
12175: AND EXISTS (SELECT 1
12172: SET Pf_Display_Flag = 1
12173: WHERE AD.plan_id = p_plan_id
12174: AND AD.pf_display_flag is NULL
12175: AND EXISTS (SELECT 1
12176: FROM MSC_ALLOC_DEMANDS AD2
12177: WHERE AD2.plan_id = p_plan_id
12178: AND AD.parent_demand_id = AD2.parent_demand_id
12179: AND AD.demand_class = AD2.demand_class
12180: GROUP BY AD2.parent_demand_id, AD2.demand_class