[Home] [Help]
21: C_ZERO_APPROXIMATOR NUMBER := 0.000001;
22:
23: /* Currently the ATP Pegging Record Types will be defined local to this package. */
24: -- This record will hold the ATP simplified Pegging Data.
25: -- Corresponds to the table msc_atp_pegging
26: TYPE ATP_Simple_Peg_Typ is RECORD (
27: reference_item_id MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr(),
28: base_item_id MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr(),
29: inventory_item_id MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr(),
1849: END Create_Simple_Pegging;
1850:
1851: -- Bug 3344102 Procedure begin
1852: -- This procedure creates the ATP simplified pegging for allocated ATP case
1853: -- and stores it into the msc_atp_pegging table after plan run.
1854: PROCEDURE Create_Pre_Allocation_Reliefs (p_plan_id IN NUMBER,
1855: p_insert_table IN VARCHAR2,
1856: p_user_id IN NUMBER,
1857: p_sysdate IN DATE,
2806: END Get_Pegging_Data_Loop;
2807: -- End Bug 3750638 Move all the pegging releated SQLs into this procedure.
2808:
2809: -- This procedure is the main procedure that creates the ATP simplified pegging
2810: -- and stores it into the msc_atp_pegging table after plan run.
2811: PROCEDURE Generate_Simplified_Pegging(p_plan_id IN NUMBER,
2812: p_share_partition IN VARCHAR2,
2813: p_applsys_schema IN VARCHAR2,
2814: RETCODE OUT NoCopy NUMBER )
3048: RETCODE := G_WARNING;
3049: END IF;
3050: RETURN;
3051: */
3052: msc_util.msc_log('Partitions are not found in MSC_ATP_PEGGING. Launch request to create partitions');
3053: l_request_id := FND_REQUEST.SUBMIT_REQUEST(
3054: 'MSC',
3055: 'MSCSUPRT',
3056: NULL, -- description
3093: IF PG_DEBUG in ('Y', 'C') THEN
3094: msc_util.msc_log('before deleting old ATP pegging data');
3095: END IF;
3096:
3097: DELETE MSC_ATP_PEGGING where plan_id = p_plan_id;
3098:
3099: IF PG_DEBUG in ('Y', 'C') THEN
3100: msc_util.msc_log('After deleting old ATP pegging info');
3101: END IF;
3103: END IF;
3104:
3105: IF p_share_partition = 'Y' THEN
3106:
3107: l_temp_table := 'MSC_ATP_PEGGING';
3108: ELSE
3109:
3110: IF PG_DEBUG in ('Y', 'C') THEN
3111: msc_util.msc_log('not a shared plan partition, insert data into temp tables');
3121: INTO l_tbspace, l_ind_tbspace
3122: FROM all_tab_partitions t,
3123: all_part_indexes i
3124: WHERE t.table_owner = l_msc_schema
3125: AND t.table_name = 'MSC_ATP_PEGGING'
3126: AND t.partition_name = l_partition_name
3127: AND i.owner (+) = t.table_owner
3128: AND i.table_name (+) = t.table_name
3129: AND rownum = 1;
3136: --6113544
3137: l_insert_stmt := 'CREATE TABLE ' || l_temp_table
3138: || ' TABLESPACE ' || l_tbspace
3139: || ' PCTFREE 0 STORAGE(INITIAL 40K NEXT 5M PCTINCREASE 0)'
3140: || ' as select * from MSC_ATP_PEGGING where 1=2 ';
3141:
3142: /*
3143: l_insert_stmt := 'CREATE TABLE ' || l_temp_table || '(
3144: plan_id NUMBER NOT NULL,
4386:
4387: IF p_share_partition = 'Y' THEN
4388:
4389: IF PG_DEBUG in ('Y', 'C') THEN
4390: msc_util.msc_log('Analyze Plan partition for MSC_ATP_PEGGING');
4391: END IF;
4392: fnd_stats.gather_table_stats(ownname=>l_msc_schema,tabname=>'MSC_ATP_PEGGING',
4393: partname=>'ATP_PEGGING_999999',
4394: granularity=>'PARTITION',
4388:
4389: IF PG_DEBUG in ('Y', 'C') THEN
4390: msc_util.msc_log('Analyze Plan partition for MSC_ATP_PEGGING');
4391: END IF;
4392: fnd_stats.gather_table_stats(ownname=>l_msc_schema,tabname=>'MSC_ATP_PEGGING',
4393: partname=>'ATP_PEGGING_999999',
4394: granularity=>'PARTITION',
4395: percent =>10);
4396:
4494: statement => l_sql_stmt,
4495: object_name => l_temp_table );
4496: END ;
4497: IF PG_DEBUG in ('Y', 'C') THEN
4498: msc_util.msc_log('Gather Table Stats for MSC_ATP_PEGGING');
4499: END IF;
4500: --13021736 corrected the syntax.
4501: fnd_stats.gather_table_stats(ownname => l_msc_schema,
4502: tabname => 'MSC_ATP_PEGGING',
4498: msc_util.msc_log('Gather Table Stats for MSC_ATP_PEGGING');
4499: END IF;
4500: --13021736 corrected the syntax.
4501: fnd_stats.gather_table_stats(ownname => l_msc_schema,
4502: tabname => 'MSC_ATP_PEGGING',
4503: partname => 'ATP_PEGGING_' || to_char(l_plan_id),
4504: granularity => 'ALL'
4505: );
4506:
4508: IF PG_DEBUG in ('Y', 'C') THEN
4509: msc_util.msc_log('swap partition ');
4510: END IF;
4511:
4512: l_sql_stmt := 'ALTER TABLE msc_atp_pegging EXCHANGE PARTITION ' ||
4513: l_partition_name ||
4514: ' with table MSC_TEMP_ATP_PEGG_'|| to_char(l_plan_id) ||
4515: ' including indexes without validation';
4516:
4515: ' including indexes without validation';
4516:
4517: BEGIN
4518: IF PG_DEBUG in ('Y', 'C') THEN
4519: msc_util.msc_log('Before alter table msc_atp_pegging');
4520: END IF;
4521: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
4522: APPLICATION_SHORT_NAME => 'MSC',
4523: STATEMENT_TYPE => ad_ddl.alter_table,
4521: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
4522: APPLICATION_SHORT_NAME => 'MSC',
4523: STATEMENT_TYPE => ad_ddl.alter_table,
4524: STATEMENT => l_sql_stmt,
4525: OBJECT_NAME => 'MSC_ATP_PEGGING');
4526: END;
4527:
4528: END IF; -- p_share_partition = 'Y'
4529:
4595:
4596: msc_util.msc_log('Deleting Existing ATP Pegging data for plan : ' || p_plan_id);
4597:
4598: IF l_share_partition = 'Y' THEN
4599: DELETE from msc_atp_pegging
4600: where plan_id = p_plan_id;
4601:
4602: IF PG_DEBUG in ('Y', 'C') THEN
4603: msc_util.msc_log ('Post_Plan_Pegging: Number of rows deleted '|| SQL%ROWCOUNT);
4850: l_original_item_id,
4851: l_original_demand_date
4852: -- End CTO_PF_PRJ Impacts
4853: FROM
4854: msc_atp_pegging map,
4855: msc_demands d
4856: WHERE map.plan_id = p_plan_id
4857: --AND map.sr_instance_id = p_instance_id -- removed to support multiple instances in plan.
4858: AND map.sales_order_line_id in (p_identifier, p_config_line_id)
4866: -- AND (map.disposition_id, map.prev_pegging_id) IN
4867: AND (map.prev_pegging_id) IN
4868: (SELECT map2.pegging_id
4869: -- End Bug 3890723
4870: FROM msc_atp_pegging map2
4871: WHERE map2.plan_id = p_plan_id
4872: AND map2.sales_order_line_id in (p_identifier, p_config_line_id)
4873: AND DECODE(map2.demand_source_type,100,map2.demand_source_type,-1)
4874: =decode(p_demand_source_type,
5036: Decode(map.relief_type, 7, 1, NULL)
5037: -- pf_display_flag = 1 when offseting bucketed demand.
5038: -- NULL will be the default value.
5039: -- END CTO_PF_PRJ_2 Impacts
5040: FROM msc_atp_pegging map
5041: WHERE map.sr_instance_id = l_instance_id(i)
5042: AND map.plan_id = p_plan_id
5043: AND DECODE(map.demand_source_type,100,map.demand_source_type,-1)
5044: =decode(p_demand_source_type,
5174: Decode(map.relief_type, 7, 60, NULL),
5175: --Decode(map.relief_type, 7, 51, 60),
5176: 1 -- Always 1 for PF.
5177: -- END CTO_PF_PRJ_2 Impacts
5178: FROM msc_atp_pegging map
5179: WHERE map.sr_instance_id = l_instance_id(i)
5180: AND map.plan_id = p_plan_id
5181: AND DECODE(map.demand_source_type,100,map.demand_source_type,-1)
5182: =decode(p_demand_source_type,
5280:
5281: -- Bug 3344102 A variable/handle for processing reliefs/offsets.
5282: l_offset_type NUMBER := NULL;
5283:
5284: -- Bug 3381464 Array to track original supplies for updating msc_atp_pegging
5285: l_orig_supply_id MRP_ATP_PUB.Number_Arr := MRP_ATP_PUB.Number_Arr();
5286: -- Bug 3717618 Introduce a pegging_id array to track pegging
5287: -- For filtering out released/firmed supplies and pegging both are needed.
5288: l_pegging_id MRP_ATP_PUB.Number_Arr := MRP_ATP_PUB.Number_Arr();
5373: l_offset_supply_id,
5374: l_ship_date,
5375: l_dock_date
5376: FROM
5377: msc_atp_pegging map,
5378: msc_supplies s
5379: WHERE map.plan_id = p_plan_id
5380: --AND map.sr_instance_id = p_instance_id -- removed to support multiple instances in plan.
5381: AND DECODE(map.demand_source_type,100,map.demand_source_type,-1)
5389: -- End CTO_PF_PRJ_2 Impacts.
5390: -- Bug 3717618 Ensure that Firm or Released supplies are not included.
5391: AND (map.supply_id, map.pegging_id) NOT IN
5392: (SELECT supply_id, pegging_id
5393: FROM msc_atp_pegging mapeg1
5394: WHERE plan_id = p_plan_id
5395: AND DECODE(mapeg1.demand_source_type,100,mapeg1.demand_source_type,-1)
5396: =decode(p_demand_source_type, 100,
5397: p_demand_source_type, -1) --CMRO
5514: msc_sch_wb.atp_debug('Add_Offset_Supplies: Number of rows inserted '||
5515: SQL%ROWCOUNT);
5516: END IF;
5517:
5518: -- Bug 3381464 Update offset_supply_id in msc_atp_pegging.
5519: FORALL i in 1..l_del_rows
5520: UPDATE msc_atp_pegging map1
5521: SET offset_supply_id = x_supply_id(i)
5522: WHERE map1.plan_id = p_plan_id
5516: END IF;
5517:
5518: -- Bug 3381464 Update offset_supply_id in msc_atp_pegging.
5519: FORALL i in 1..l_del_rows
5520: UPDATE msc_atp_pegging map1
5521: SET offset_supply_id = x_supply_id(i)
5522: WHERE map1.plan_id = p_plan_id
5523: AND DECODE(map1.demand_source_type,100,map1.demand_source_type,-1)
5524: =decode(p_demand_source_type,
5592: l_user_id,
5593: l_sysdate,
5594: l_user_id,
5595: l_sysdate
5596: FROM msc_atp_pegging map
5597: WHERE map.sr_instance_id = l_instance_id(i)
5598: AND map.plan_id = p_plan_id
5599: AND DECODE(map.demand_source_type,100,map.demand_source_type,-1)
5600: =decode(p_demand_source_type,
5645:
5646: EXCEPTION
5647: WHEN OTHERS THEN
5648: BEGIN
5649: -- Bug 3381464 Update offset_supply_id in msc_atp_pegging.
5650: FORALL i in 1..l_del_rows
5651: UPDATE msc_atp_pegging map1
5652: SET offset_supply_id = NULL
5653: WHERE map1.plan_id = p_plan_id
5647: WHEN OTHERS THEN
5648: BEGIN
5649: -- Bug 3381464 Update offset_supply_id in msc_atp_pegging.
5650: FORALL i in 1..l_del_rows
5651: UPDATE msc_atp_pegging map1
5652: SET offset_supply_id = NULL
5653: WHERE map1.plan_id = p_plan_id
5654: AND DECODE(map1.demand_source_type,100,map1.demand_source_type,-1)
5655: =decode(p_demand_source_type,
5823: map.daily_relief_qty,
5824: decode(map.resource_id,-1,map.relief_quantity,to_number(null)) load_rate,
5825: REQ.assigned_units, -- 0 originally.
5826: REQ.std_op_code
5827: FROM msc_atp_pegging map,
5828: msc_resource_requirements REQ,
5829: -- Bug 3381464 -- Join to Pegging to obtain offset supply ids.
5830: msc_atp_pegging map2
5831: WHERE map.plan_id = p_plan_id
5826: REQ.std_op_code
5827: FROM msc_atp_pegging map,
5828: msc_resource_requirements REQ,
5829: -- Bug 3381464 -- Join to Pegging to obtain offset supply ids.
5830: msc_atp_pegging map2
5831: WHERE map.plan_id = p_plan_id
5832: --AND map.sr_instance_id = p_instance_id -- removed to support multiple instances in plan.
5833: AND DECODE(map.demand_source_type,100,map.demand_source_type,-1)
5834: =decode(p_demand_source_type,
6125: WHERE
6126: -- Bug 3629191 :All where clause except plan_id and demand_id are commmented
6127: /* sr_instance_id = p_instance_id
6128: (SELECT sr_instance_id
6129: FROM msc_atp_pegging
6130: WHERE plan_id = p_plan_ids(m)
6131: AND sales_order_line_id = p_identifiers(m)
6132: AND DECODE(demand_source_type,100,demand_source_type,-1)
6133: =decode(p_demand_source_type(m),
6168: WHERE
6169: -- Bug 3629191 :All where clause except plan_id and parent_demand_id are commmented
6170: /*sr_instance_id IN
6171: (SELECT sr_instance_id
6172: FROM msc_atp_pegging
6173: WHERE plan_id = p_plan_ids(m)
6174: AND sales_order_line_id = p_identifiers(m)
6175: AND DECODE(demand_source_type,100,demand_source_type,-1)
6176: =decode(p_demand_source_type(m),
6263: l_offset_type := 2;
6264: END IF;
6265: -- End CTO_PF_PRJ_2 Changes
6266:
6267: -- Bug 3381464 Update offset_supply_id in msc_atp_pegging.
6268: --Bug 3629191
6269: FORALL m in 1..p_del_supply_ids.COUNT
6270: UPDATE msc_atp_pegging map1
6271: SET offset_supply_id = NULL
6266:
6267: -- Bug 3381464 Update offset_supply_id in msc_atp_pegging.
6268: --Bug 3629191
6269: FORALL m in 1..p_del_supply_ids.COUNT
6270: UPDATE msc_atp_pegging map1
6271: SET offset_supply_id = NULL
6272: WHERE map1.plan_id = p_atp_peg_supplies_plan_ids(m)
6273: -- Bug 3629191: where clause on demand_source_type, sales_order_line_id
6274: -- and inventory_item_id are removed
6306: WHERE
6307: -- Bug 3629191 :All where clause except and transaction_id are commmented
6308: /* sr_instance_id = p_instance_id
6309: (SELECT sr_instance_id
6310: FROM msc_atp_pegging
6311: WHERE plan_id = p_plan_ids(m)
6312: AND sales_order_line_id = p_identifiers(m)
6313: AND DECODE(demand_source_type,100,demand_source_type,-1)
6314: =decode(p_demand_source_type(m),
6349: WHERE
6350: -- Bug 3629191 :All where clause except and parent_transaction_id are commmented
6351: /* sr_instance_id = p_instance_id
6352: (SELECT sr_instance_id
6353: FROM msc_atp_pegging
6354: WHERE plan_id = p_plan_ids(m)
6355: -- CTO_PF_PRJ_2 Changes Use Relief Type
6356: AND relief_type = l_offset_type
6357: -- End CTO_PF_PRJ_2 Changes
6435: WHERE
6436: -- Bug 3629191 :All where clause except and transaction_id are commmented
6437: /*sr_instance_id = p_instance_id
6438: (SELECT sr_instance_id
6439: FROM msc_atp_pegging
6440: WHERE plan_id = p_plan_ids(m)
6441: AND sales_order_line_id = p_identifiers(m)
6442: AND DECODE(demand_source_type,100,demand_source_type,-1)
6443: =decode(p_demand_source_type(m),
6585: -- First create the demand records for component members and component family items.
6586:
6587: IF (MSC_ATP_PVT.G_HYBRID_ALLOC_ATP = 'Y' ) THEN
6588:
6589: INSERT INTO msc_atp_pegging
6590: (reference_item_id, inventory_item_id, plan_id, sr_instance_id,
6591: organization_id, sales_order_line_id, demand_source_type, bom_item_type, --cmro
6592: transaction_date, demand_id, demand_quantity,
6593: disposition_id, demand_class, supply_id, supply_quantity,
6641: AND msi.inventory_item_id = alloc.inventory_item_id(+)
6642: ;
6643:
6644: ELSE
6645: INSERT INTO msc_atp_pegging
6646: (reference_item_id, inventory_item_id, plan_id, sr_instance_id,
6647: organization_id, sales_order_line_id, demand_source_type, bom_item_type, --cmro
6648: transaction_date, demand_id, demand_quantity,
6649: disposition_id, demand_class, supply_id, supply_quantity,
6692: SQL%ROWCOUNT);
6693: END IF;
6694:
6695: -- Now Create Rest of the Records.
6696: INSERT INTO msc_atp_pegging
6697: (reference_item_id, inventory_item_id, plan_id, sr_instance_id,
6698: organization_id, sales_order_line_id, demand_source_type, bom_item_type, --cmro
6699: transaction_date, demand_id, demand_quantity,
6700: disposition_id, demand_class, supply_id, supply_quantity,
6722: AND mapt.end_demand_id = p_end_demand_id
6723: -- Process everything except PF member and family demands.
6724: AND mapt.inventory_item_id NOT IN
6725: (SELECT NVL(original_item_id, inventory_item_id)
6726: FROM msc_atp_pegging
6727: WHERE reference_item_id = p_reference_item_id
6728: AND plan_id = p_plan_id
6729: AND sales_order_line_id = NVL(p_config_order_line_id, p_model_order_line_id)
6730: --AND demand_source_type = p_demand_source_type
6771: IS
6772:
6773: l_reference_item_id NUMBER;
6774: i NUMBER;
6775: atp_peg_rec msc_atp_pegging%ROWTYPE;
6776: my_sqlcode NUMBER;
6777: -- Bug 3334643 Track the plan_id
6778: l_plan_id NUMBER;
6779:
6807: -- CTO_PF_PRJ_2 Impacts
6808: IF (MSC_ATP_PVT.G_CTO_PF_ATP = 'Y') THEN
6809: l_insert_temp_table := 'MSC_ATP_PEG_TEMP';
6810: ELSE
6811: l_insert_temp_table := 'MSC_ATP_PEGGING';
6812: END IF;
6813:
6814: IF ((MSC_ATP_PVT.G_INV_CTP = 4) AND
6815: (MSC_ATP_PVT.G_ALLOCATED_ATP = 'Y') AND
6826:
6827: -- First Delete old pegging data.
6828: BEGIN
6829:
6830: DELETE from msc_atp_pegging
6831: WHERE plan_id = p_old_plan_id
6832: AND relief_type > 0
6833: AND sales_order_line_id in (NVL(p_config_order_line_id, -1), p_model_order_line_id)
6834: AND decode(demand_source_type,100,demand_source_type,-1)