[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
894: msc_sch_wb.atp_debug('*************************************');
895: END IF;
896:
897: IF l_update_mem_bd = 'Y' THEN
898: update msc_alloc_demands
899: --bug3697365 added timestamp also
900: set demand_date = trunc(l_mem_bd_date) + MSC_ATP_PVT.G_END_OF_DAY,
901: allocated_quantity = l_mem_bd_qty,
902: demand_quantity = p_demand_qty,
935: END IF;
936: END IF;
937:
938: IF l_update_pf_bd = 'Y' THEN
939: update msc_alloc_demands
940: --bug3697365 added timestamp also
941: set demand_date = trunc(l_pf_bd_date) + MSC_ATP_PVT.G_END_OF_DAY,
942: allocated_quantity = l_pf_bd_qty,
943: demand_quantity = p_demand_qty,
1056: msc_sch_wb.atp_debug('*************************************');
1057: END IF;
1058:
1059: IF l_update_mem_bd = 'Y' THEN
1060: update msc_alloc_demands
1061: --bug3697365 added timestamp also
1062: set demand_date = trunc(l_mem_bd_date) + MSC_ATP_PVT.G_END_OF_DAY,
1063: allocated_quantity = l_mem_bd_qty,
1064: demand_quantity = p_demand_qty,
1096: return;
1097: END IF;
1098: END IF;
1099: ELSIF l_update_mem_bd = 'D' THEN
1100: delete msc_alloc_demands
1101: where parent_demand_id = p_parent_demand_id
1102: --bug3693892 added trunc
1103: and trunc(demand_date) <= p_atf_date
1104: and plan_id = p_plan_id;
1104: and plan_id = p_plan_id;
1105: END IF;
1106:
1107: IF l_update_pf_bd = 'Y' THEN
1108: update msc_alloc_demands
1109: --bug3697365 added timestamp also
1110: set demand_date = trunc(l_pf_bd_date) + MSC_ATP_PVT.G_END_OF_DAY,
1111: allocated_quantity = l_pf_bd_qty,
1112: demand_quantity = p_demand_qty,
1180: -- Initializing API return code
1181: x_return_status := FND_API.G_RET_STS_SUCCESS;
1182:
1183: IF (p_old_demand_date < p_atf_date) and (p_new_demand_date <= p_atf_date) THEN
1184: update msc_alloc_demands
1185: --bug3697365 added timestamp also
1186: set demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1187: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY
1188: where parent_demand_id = p_parent_demand_id
1189: --bug3693892 added trunc
1190: and trunc(demand_date) <= p_atf_date
1191: and plan_id = p_plan_id;
1192: ELSIF (p_old_demand_date < p_atf_date) and (p_new_demand_date > p_atf_date) THEN
1193: update msc_alloc_demands
1194: --bug3693892 added trunc
1195: set demand_date = trunc(p_atf_date) + MSC_ATP_PVT.G_END_OF_DAY,
1196: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1197: Pf_Display_Flag = null
1199: --bug3693892 added trunc
1200: and trunc(demand_date) <= p_atf_date
1201: and plan_id = p_plan_id;
1202:
1203: update msc_alloc_demands
1204: --bug3693892 added trunc
1205: set demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1206: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1207: Pf_Display_Flag = 1
1209: --bug3693892 added trunc
1210: and trunc(demand_date) > p_atf_date
1211: and plan_id = p_plan_id;
1212: ELSIF (p_old_demand_date > p_atf_date) and (p_new_demand_date > p_atf_date) THEN
1213: update msc_alloc_demands
1214: --bug3693892 added trunc
1215: set demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY,
1216: original_demand_date = trunc(p_new_demand_date) + MSC_ATP_PVT.G_END_OF_DAY
1217: where parent_demand_id = p_parent_demand_id
1233:
1234: END Move_PF_Bd_Dates;
1235:
1236: /*--Insert_Bucketed_Demand--------------------------------------------------
1237: | o This procedure inserts bucketed demand in msc_alloc_demands table
1238: | with origination type 51 (ATP Bucketed Demand).
1239: +-------------------------------------------------------------------------*/
1240: PROCEDURE Insert_Bucketed_Demand(
1241: p_atp_rec IN MRP_ATP_PVT.AtpRec,
1258:
1259: -- Initializing API return code
1260: x_return_status := FND_API.G_RET_STS_SUCCESS;
1261:
1262: INSERT INTO MSC_ALLOC_DEMANDS(
1263: PLAN_ID,
1264: INVENTORY_ITEM_ID,
1265: ORIGINAL_ITEM_ID,
1266: USING_ASSEMBLY_ITEM_ID,
1356: SELECT allocated_quantity,
1357: demand_date
1358: INTO p_bucketed_demands_rec.mem_bd_qty,
1359: p_bucketed_demands_rec.mem_bd_date
1360: FROM msc_alloc_demands
1361: WHERE plan_id = p_plan_id
1362: AND parent_demand_id = p_parent_demand_id
1363: AND inventory_item_id = p_bucketed_demands_rec.mem_item_id
1364: ;
1377: SELECT allocated_quantity,
1378: demand_date
1379: INTO p_bucketed_demands_rec.pf_bd_qty,
1380: p_bucketed_demands_rec.pf_bd_date
1381: FROM msc_alloc_demands
1382: WHERE plan_id = p_plan_id
1383: AND parent_demand_id = p_parent_demand_id
1384: AND inventory_item_id = p_bucketed_demands_rec.pf_item_id
1385: ;
2425: /*--Get_Mat_Avail_Pf_Pds_Summ----------------------------------------------------
2426: | o Called for unallocated time phased PF atp for PDS summary
2427: | o Differences from non summary SQL are :
2428: | - Additional union with MSC_ATP_SUMMARY_SD
2429: | - Decode in quantity in SQL on msc_alloc_demands to consider unscheduled
2430: | orders as dummy supplies
2431: | - Additional join with MSC_PLANS in the SQLs on supplies and demands to
2432: | filter records based on refresh number
2433: | - Filter on allocated_quantity=0 and origination_type=51 removed in the
2476: SELECT TRUNC(AD.DEMAND_DATE) SD_DATE,
2477: decode(AD.ALLOCATED_QUANTITY, -- Consider unscheduled orders as dummy supplies
2478: 0, OLD_ALLOCATED_QUANTITY, -- For summary enhancement
2479: -1 * AD.ALLOCATED_QUANTITY) SD_QTY
2480: FROM MSC_ALLOC_DEMANDS AD,
2481: MSC_SYSTEM_ITEMS I,
2482: MSC_PLANS P -- For summary enhancement
2483: WHERE I.SR_INVENTORY_ITEM_ID in (p_sr_member_id, p_sr_family_id)
2484: AND I.ORGANIZATION_ID = p_org_id
2531: /*--Get_Mat_Avail_Pf_Pds---------------------------------------------------------
2532: | o Called for unallocated Time Phased PF ATP
2533: | o The supply demand SQL in this procedure gets following:
2534: | - Bucketed demands (origination type 51) for member item upto ATF from
2535: | msc_alloc_demands table.
2536: | - Bucketed demands for family after ATF from msc_alloc_demands table.
2537: | - Rollup supplies (order type 50) for member item upto ATF from
2538: | msc_alloc_supplies table.
2539: | - Rollup supplies for family after ATF from msc_alloc_supplies table.
2532: | o Called for unallocated Time Phased PF ATP
2533: | o The supply demand SQL in this procedure gets following:
2534: | - Bucketed demands (origination type 51) for member item upto ATF from
2535: | msc_alloc_demands table.
2536: | - Bucketed demands for family after ATF from msc_alloc_demands table.
2537: | - Rollup supplies (order type 50) for member item upto ATF from
2538: | msc_alloc_supplies table.
2539: | - Rollup supplies for family after ATF from msc_alloc_supplies table.
2540: +------------------------------------------------------------------------------*/
2563: FROM (
2564: --bug3700564 added trunc
2565: SELECT TRUNC(AD.DEMAND_DATE) SD_DATE,
2566: -1 * AD.ALLOCATED_QUANTITY SD_QTY
2567: FROM MSC_ALLOC_DEMANDS AD,
2568: MSC_SYSTEM_ITEMS I
2569: WHERE I.SR_INVENTORY_ITEM_ID in (p_sr_member_id, p_sr_family_id)
2570: AND I.ORGANIZATION_ID = p_org_id
2571: AND I.SR_INSTANCE_ID = p_instance_id
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.
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:
3192: I2.Item_Name --bug3579625
3193:
3194: FROM MSC_SYSTEM_ITEMS I,
3195: MSC_SYSTEM_ITEMS I2, --bug3579625
3196: MSC_ALLOC_DEMANDS AD,
3197: MSC_TRADING_PARTNERS MTP,--bug3263368
3198: MSC_TRADING_PARTNER_SITES MTPS --bug3263368
3199: WHERE I.SR_INVENTORY_ITEM_ID in (p_sr_member_id, p_sr_family_id)
3200: AND I.ORGANIZATION_ID = p_org_id
3429: | o Called from Item_Alloc_Cum_Atp procedure for Rule based Allocated
3430: | Time Phased PF ATP.
3431: | o The supply demand SQL in this procedure gets following:
3432: | - Allocated Bucketed demands (origination type 51) for member item
3433: | upto ATF from msc_alloc_demands table.
3434: | - Allocated Bucketed demands for family after ATF from msc_alloc_demands
3435: | table.
3436: | - Allocated Rollup supplies (order type 50) for member item upto ATF
3437: | from msc_alloc_supplies table.
3430: | Time Phased PF ATP.
3431: | o The supply demand SQL in this procedure gets following:
3432: | - Allocated Bucketed demands (origination type 51) for member item
3433: | upto ATF from msc_alloc_demands table.
3434: | - Allocated Bucketed demands for family after ATF from msc_alloc_demands
3435: | table.
3436: | - Allocated Rollup supplies (order type 50) for member item upto ATF
3437: | from msc_alloc_supplies table.
3438: | - Allocated Rollup supplies for family after ATF from msc_alloc_supplies
3786: p_family_id)), p_org_id, p_instance_id,
3787: trunc(AD.Demand_Date),p_level_id, NULL)))),
3788: p_demand_class,
3789: p_level_id),0)) SD_QTY --4365873
3790: FROM MSC_ALLOC_DEMANDS AD
3791: WHERE AD.PLAN_ID = p_plan_id
3792: AND AD.SR_INSTANCE_ID = p_instance_id
3793: AND AD.INVENTORY_ITEM_ID in (p_member_id,p_family_id)
3794: AND AD.ORGANIZATION_ID = p_org_id
4255: p_family_id)), p_org_id, p_instance_id,
4256: trunc(AD.Demand_Date),p_level_id, NULL)))),
4257: p_demand_class,
4258: p_level_id),0)) SD_QTY --4365873
4259: FROM MSC_ALLOC_DEMANDS AD
4260: WHERE AD.PLAN_ID = p_plan_id
4261: AND AD.SR_INSTANCE_ID = p_instance_id
4262: AND AD.INVENTORY_ITEM_ID in (p_member_id,p_family_id)
4263: AND AD.ORGANIZATION_ID = p_org_id
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.
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:
5147: MTP.PARTNER_NAME, --bug3263368
5148: AD.DEMAND_CLASS, --bug3263368
5149: AD.REQUEST_DATE, --bug3263368
5150: I.Item_Name -- bug3579625
5151: FROM MSC_ALLOC_DEMANDS AD,
5152: MSC_TRADING_PARTNERS MTP,--bug3263368
5153: MSC_TRADING_PARTNER_SITES MTPS, --bug3263368
5154: MSC_SYSTEM_ITEMS I --bug3579625
5155: WHERE AD.PLAN_ID = p_plan_id
5380: x_atp_qtys
5381: FROM (
5382: SELECT TRUNC(AD.DEMAND_DATE) SD_DATE,
5383: -1 * AD.ALLOCATED_QUANTITY SD_QTY
5384: FROM MSC_ALLOC_DEMANDS AD
5385: WHERE AD.PLAN_ID = p_plan_id
5386: AND AD.SR_INSTANCE_ID = p_instance_id
5387: AND AD.INVENTORY_ITEM_ID in (p_member_id, p_family_id)
5388: AND AD.ORIGINATION_TYPE <> 52 -- ATP Bucketed Demand
5472: SELECT TRUNC(AD.DEMAND_DATE) SD_DATE,
5473: decode(AD.ALLOCATED_QUANTITY, -- Consider unscheduled orders as dummy supplies
5474: 0, OLD_ALLOCATED_QUANTITY,-- For summary enhancement
5475: -1 * AD.ALLOCATED_QUANTITY) SD_QTY
5476: FROM MSC_ALLOC_DEMANDS AD,
5477: MSC_PLANS P -- For summary enhancement
5478: WHERE AD.PLAN_ID = p_plan_id
5479: AND AD.SR_INSTANCE_ID = p_instance_id
5480: AND AD.INVENTORY_ITEM_ID in (p_member_id, p_family_id)
5658: MTP.PARTNER_NAME, --bug3263368
5659: AD.DEMAND_CLASS, --bug3263368
5660: AD.REQUEST_DATE, --bug3263368
5661: I.Item_Name --bug3579625
5662: FROM MSC_ALLOC_DEMANDS AD,
5663: MSC_TRADING_PARTNERS MTP,--bug3263368
5664: MSC_TRADING_PARTNER_SITES MTPS, --bug3263368
5665: MSC_SYSTEM_ITEMS I --bug3579625
5666: WHERE AD.PLAN_ID = p_plan_id
5821: (
5822: SELECT TRUNC(AD.DEMAND_DATE) SD_DATE,
5823: -1 * AD.ALLOCATED_QUANTITY SD_QTY,
5824: AD.DEMAND_CLASS
5825: FROM MSC_ALLOC_DEMANDS AD
5826: WHERE AD.PLAN_ID = p_plan_id
5827: AND AD.SR_INSTANCE_ID = p_instance_id
5828: AND AD.INVENTORY_ITEM_ID in (p_member_id, p_family_id)
5829: AND AD.ORGANIZATION_ID = p_org_id
5930: decode(AD.ALLOCATED_QUANTITY, -- Consider unscheduled orders as dummy supplies
5931: 0, nvl(OLD_ALLOCATED_QUANTITY,0), --4658238 -- For summary enhancement
5932: -1 * AD.ALLOCATED_QUANTITY) SD_QTY,
5933: AD.DEMAND_CLASS
5934: FROM MSC_ALLOC_DEMANDS AD,
5935: MSC_PLANS P -- For summary enhancement
5936: WHERE AD.PLAN_ID = p_plan_id
5937: AND AD.SR_INSTANCE_ID = p_instance_id
5938: AND AD.INVENTORY_ITEM_ID in (p_member_id, p_family_id)
6090: MTP.PARTNER_NAME col28, --bug3263368
6091: AD.DEMAND_CLASS col29, --bug3263368
6092: AD.REQUEST_DATE col30 --bug3263368
6093: FROM
6094: MSC_ALLOC_DEMANDS AD,
6095: MSC_ALLOC_TEMP TEMP,
6096: MSC_TRADING_PARTNERS MTP,--bug3263368
6097: MSC_TRADING_PARTNER_SITES MTPS, --bug3263368
6098: MSC_SYSTEM_ITEMS msi --bug3671294
6650: MTP.PARTNER_NAME col29, --bug3263368
6651: AD.DEMAND_CLASS col30, --bug3263368
6652: AD.REQUEST_DATE col31 --bug3263368
6653: FROM
6654: MSC_ALLOC_DEMANDS AD,
6655: MSC_ALLOC_TEMP TEMP,
6656: MSC_TRADING_PARTNERS MTP,--bug3263368
6657: MSC_TRADING_PARTNER_SITES MTPS, --bug3263368
6658: MSC_SYSTEM_ITEMS msi --bug3671294
7161: MTP.PARTNER_NAME col34, --bug3263368
7162: AD.DEMAND_CLASS col35, --bug3263368
7163: AD.REQUEST_DATE col36 --bug3263368
7164: FROM
7165: MSC_ALLOC_DEMANDS AD,
7166: MSC_ALLOC_HIERARCHY_TEMP TEMP,
7167: MSC_TRADING_PARTNERS MTP,--bug3263368
7168: MSC_TRADING_PARTNER_SITES MTPS, --bug3263368
7169: MSC_SYSTEM_ITEMS msi --bug3671294
7340: )
7341: (SELECT demand_class,
7342: parent_demand_id,
7343: sum(allocated_quantity)
7344: FROM msc_alloc_demands
7345: WHERE (demand_class, parent_demand_id) in
7346: (SELECT demand_class,
7347: identifier3
7348: FROM mrp_atp_details_temp
7374: supply_demand_quantity
7375: )
7376: SELECT parent_demand_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)
9394: l_alloc_temp_table := 'MSC_ALLOC_TEMP_' || to_char(p_plan_id);
9395:
9396: msc_util.msc_log('temp table : ' || l_alloc_temp_table);
9397:
9398: /* Create temp table in tablespace of MSC_ALLOC_DEMANDS*/
9399: SELECT t.tablespace_name, NVL(i.def_tablespace_name, t.tablespace_name)
9400: INTO l_tbspace, l_ind_tbspace
9401: FROM all_tab_partitions t,
9402: all_part_indexes i
9400: INTO l_tbspace, l_ind_tbspace
9401: FROM all_tab_partitions t,
9402: all_part_indexes i
9403: WHERE t.table_owner = l_msc_schema
9404: AND t.table_name = 'MSC_ALLOC_DEMANDS'
9405: AND t.partition_name = 'ALLOC_DEMANDS_' || to_char(l_plan_id)
9406: AND i.owner (+) = t.table_owner
9407: AND i.table_name (+) = t.table_name
9408: AND rownum = 1;
9543:
9544: -- first delete the existing data from tables
9545: msc_util.msc_log('before deleteing data from the table');
9546:
9547: DELETE MSC_ALLOC_DEMANDS where plan_id = p_plan_id;
9548: msc_util.msc_log('After deleting data from MSC_ALLOC_DEMANDS table');
9549:
9550: DELETE MSC_ALLOC_SUPPLIES where plan_id = p_plan_id;
9551: msc_util.msc_log('After deleting data from MSC_ALLOC_SUPPLIES table');
9544: -- first delete the existing data from tables
9545: msc_util.msc_log('before deleteing data from the table');
9546:
9547: DELETE MSC_ALLOC_DEMANDS where plan_id = p_plan_id;
9548: msc_util.msc_log('After deleting data from MSC_ALLOC_DEMANDS table');
9549:
9550: DELETE MSC_ALLOC_SUPPLIES where plan_id = p_plan_id;
9551: msc_util.msc_log('After deleting data from MSC_ALLOC_SUPPLIES table');
9552:
9589: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
9590: msc_util.msc_log('After executing the cursor');
9591:
9592: msc_util.msc_log('rows processed: ' || rows_processed);
9593: msc_util.msc_log('after inserting item data into MSC_ALLOC_DEMANDS tables');
9594:
9595: /*--------------------------------------------------------------------------
9596: | <<<<<<<<<<<<<<<<<<<<<<< Begin Supplies SQL1 >>>>>>>>>>>>>>>>>>>>>>>>>>>
9597: +-------------------------------------------------------------------------*/
9634:
9635: msc_util.msc_log('rows processed: ' || rows_processed);
9636: msc_util.msc_log('after inserting item data into MSC_ALLOC_SUPPLIES tables');
9637:
9638: msc_util.msc_log('Analyze Plan partition for MSC_ALLOC_DEMANDS');
9639: fnd_stats.gather_table_stats(ownname=>l_msc_schema,tabname=>'MSC_ALLOC_DEMANDS',
9640: partname=>'ALLOC_DEMANDS_999999',
9641: granularity=>'PARTITION',
9642: percent =>10);
9635: msc_util.msc_log('rows processed: ' || rows_processed);
9636: msc_util.msc_log('after inserting item data into MSC_ALLOC_SUPPLIES tables');
9637:
9638: msc_util.msc_log('Analyze Plan partition for MSC_ALLOC_DEMANDS');
9639: fnd_stats.gather_table_stats(ownname=>l_msc_schema,tabname=>'MSC_ALLOC_DEMANDS',
9640: partname=>'ALLOC_DEMANDS_999999',
9641: granularity=>'PARTITION',
9642: percent =>10);
9643:
9660: INTO l_tbspace, l_ind_tbspace
9661: FROM all_tab_partitions t,
9662: all_part_indexes i
9663: WHERE t.table_owner = l_msc_schema
9664: AND t.table_name = 'MSC_ALLOC_DEMANDS'
9665: AND t.partition_name = 'ALLOC_DEMANDS_' || to_char(l_plan_id)
9666: AND i.owner (+) = t.table_owner
9667: AND i.table_name (+) = t.table_name
9668: AND rownum = 1;
9674: --bug 6113544
9675: l_insert_stmt := 'CREATE TABLE ' || l_temp_table
9676: || ' TABLESPACE ' || l_tbspace
9677: || ' PCTFREE 0 STORAGE(INITIAL 40K NEXT 5M PCTINCREASE 0)'
9678: || ' as select * from MSC_ALLOC_DEMANDS where 1=2 ';
9679:
9680: /*
9681: l_insert_stmt := 'CREATE TABLE ' || l_temp_table || '(
9682: PLAN_ID NUMBER NOT NULL,
10030:
10031: msc_util.msc_log('swap partition for demands');
10032: l_partition_name := 'ALLOC_DEMANDS_' || to_char(l_plan_id);
10033:
10034: msc_util.msc_log('Partition name for msc_alloc_demands table : ' || l_partition_name);
10035:
10036: -- swap partiton for supplies and demand part
10037:
10038: l_sql_stmt := 'ALTER TABLE msc_alloc_demands EXCHANGE PARTITION ' || l_partition_name ||
10034: msc_util.msc_log('Partition name for msc_alloc_demands table : ' || l_partition_name);
10035:
10036: -- swap partiton for supplies and demand part
10037:
10038: l_sql_stmt := 'ALTER TABLE msc_alloc_demands EXCHANGE PARTITION ' || l_partition_name ||
10039: ' with table MSC_TEMP_ALLOC_DEM_'|| to_char(l_plan_id) ||
10040: ' including indexes without validation';
10041:
10042: BEGIN
10039: ' with table MSC_TEMP_ALLOC_DEM_'|| to_char(l_plan_id) ||
10040: ' including indexes without validation';
10041:
10042: BEGIN
10043: msc_util.msc_log('Before alter table msc_alloc_demands');
10044: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
10045: APPLICATION_SHORT_NAME => 'MSC',
10046: STATEMENT_TYPE => ad_ddl.alter_table,
10047: STATEMENT => l_sql_stmt,
10044: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
10045: APPLICATION_SHORT_NAME => 'MSC',
10046: STATEMENT_TYPE => ad_ddl.alter_table,
10047: STATEMENT => l_sql_stmt,
10048: OBJECT_NAME => 'MSC_ALLOC_DEMANDS');
10049: END;
10050:
10051: msc_util.msc_log('swap partition for supplies');
10052: l_partition_name := 'ALLOC_SUPPLIES_' || to_char(l_plan_id);
10076:
10077: msc_util.msc_log('After procedure clean_temp_tables');
10078: /* forecast at PF changes end*/
10079:
10080: /* Call Update_Pf_Display_Flags to update Pf_Display_Flag in msc_alloc_demands*/
10081: Update_Pf_Display_Flag(p_plan_id, l_return_status);
10082:
10083: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
10084: IF PG_DEBUG in ('Y', 'C') THEN
10163: x_return_status := FND_API.G_RET_STS_SUCCESS;
10164:
10165: IF p_share_partition = 'Y' THEN
10166: x_sql_stmt := '
10167: INSERT INTO MSC_ALLOC_DEMANDS(';
10168: ELSE
10169: x_sql_stmt := '
10170: INSERT INTO ' || p_temp_table || '(';
10171: END IF;
10831: x_return_status := FND_API.G_RET_STS_SUCCESS;
10832:
10833: IF p_share_partition = 'Y' THEN
10834: x_sql_stmt := '
10835: INSERT INTO MSC_ALLOC_DEMANDS(';
10836: ELSE
10837: x_sql_stmt := '
10838: INSERT INTO ' || p_temp_table || '(';
10839: END IF;
11453: x_return_status := FND_API.G_RET_STS_SUCCESS;
11454:
11455: IF p_share_partition = 'Y' THEN
11456: x_sql_stmt := '
11457: INSERT INTO MSC_ALLOC_DEMANDS(';
11458: ELSE
11459: x_sql_stmt := '
11460: INSERT INTO ' || p_temp_table || '(';
11461: END IF;
12173: x_return_status := FND_API.G_RET_STS_SUCCESS;
12174:
12175: -- Performance tuning pending
12176: /*
12177: UPDATE MSC_ALLOC_DEMANDS AD
12178: SET Pf_Display_Flag = 1
12179: WHERE AD.plan_id = p_plan_id
12180: --AND AD.allocated_quantity = Demand_Quantity
12181: AND AD.pf_display_flag is NULL
12180: --AND AD.allocated_quantity = Demand_Quantity
12181: AND AD.pf_display_flag is NULL
12182: AND (AD.parent_demand_id, AD.demand_class, 1) in
12183: (SELECT AD2.parent_demand_id, AD2.demand_class, count(*)
12184: FROM MSC_ALLOC_DEMANDS AD2
12185: WHERE AD2.plan_id = p_plan_id
12186: GROUP BY AD2.parent_demand_id, AD2.demand_class
12187: )
12188: AND EXISTS (SELECT 1
12193: AND I.plan_id = AD.plan_id
12194: AND I.aggregate_time_fence_date is not null);
12195: */
12196: --5631956 Modified SQL tuned for better performance.
12197: UPDATE MSC_ALLOC_DEMANDS AD
12198: SET Pf_Display_Flag = 1
12199: WHERE AD.plan_id = p_plan_id
12200: AND AD.pf_display_flag is NULL
12201: AND EXISTS (SELECT 1
12198: SET Pf_Display_Flag = 1
12199: WHERE AD.plan_id = p_plan_id
12200: AND AD.pf_display_flag is NULL
12201: AND EXISTS (SELECT 1
12202: FROM MSC_ALLOC_DEMANDS AD2
12203: WHERE AD2.plan_id = p_plan_id
12204: AND AD.parent_demand_id = AD2.parent_demand_id
12205: AND AD.demand_class = AD2.demand_class
12206: GROUP BY AD2.parent_demand_id, AD2.demand_class