[Home] [Help]
1: PACKAGE BODY MSC_ATP_PEG AS
2: /* $Header: MSCAPEGB.pls 120.2 2007/12/12 10:18:41 sbnaik ship $ */
3:
4: MAXVALUE CONSTANT NUMBER := 999999;
5:
3:
4: MAXVALUE CONSTANT NUMBER := 999999;
5:
6: PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('MSC_ATP_DEBUG'), 'N');
7: G_PKG_NAME CONSTANT VARCHAR2(30) := 'MSC_ATP_PEG';
8:
9: -- Bug 3344102 Allocation related global variables.
10: G_ALLOC_ATP VARCHAR2(1);
11: G_CLASS_HRCHY NUMBER;
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(),
1847: END Create_Simple_Pegging;
1848:
1849: -- Bug 3344102 Procedure begin
1850: -- This procedure creates the ATP simplified pegging for allocated ATP case
1851: -- and stores it into the msc_atp_pegging table after plan run.
1852: PROCEDURE Create_Pre_Allocation_Reliefs (p_plan_id IN NUMBER,
1853: p_insert_table IN VARCHAR2,
1854: p_user_id IN NUMBER,
1855: p_sysdate IN DATE,
1908: mapt.relief_quantity ,
1909: mapt.pegging_id, mapt.prev_pegging_id, mapt.end_pegging_id,
1910: :p_user_id, :p_sysdate, :p_user_id, :p_sysdate,
1911: mv.partner_id, mv.partner_site_id customer_site_id
1912: FROM msc_atp_peg_temp mapt, msc_item_hierarchy_mv mv
1913: WHERE mapt.plan_id = :p_plan_id
1914: AND mapt.relief_type = 3
1915: AND mapt.inventory_item_id = mv.inventory_item_id(+)
1916: AND mapt.organization_id = mv.organization_id (+)
1962: mapt.relief_quantity ,
1963: mapt.pegging_id, mapt.prev_pegging_id, mapt.end_pegging_id,
1964: :p_user_id, :p_sysdate, :p_user_id, :p_sysdate,
1965: mv.partner_id, mv.partner_site_id customer_site_id
1966: FROM msc_atp_peg_temp mapt, msc_item_hierarchy_mv mv
1967: WHERE mapt.plan_id = :p_plan_id
1968: AND mapt.relief_type = 2
1969: AND mapt.inventory_item_id = mv.inventory_item_id(+)
1970: AND mapt.organization_id = mv.organization_id (+)
2804: END Get_Pegging_Data_Loop;
2805: -- End Bug 3750638 Move all the pegging releated SQLs into this procedure.
2806:
2807: -- This procedure is the main procedure that creates the ATP simplified pegging
2808: -- and stores it into the msc_atp_pegging table after plan run.
2809: PROCEDURE Generate_Simplified_Pegging(p_plan_id IN NUMBER,
2810: p_share_partition IN VARCHAR2,
2811: p_applsys_schema IN VARCHAR2,
2812: RETCODE OUT NoCopy NUMBER )
2975: -- Second set the table names.
2976:
2977: -- Bug 3344102
2978: -- Set the Global temporary table name.
2979: l_global_temp_table := 'MSC_ATP_PEG_TEMP';
2980: -- End Bug 3344102
2981:
2982: FOR i in 1..atp_simple_peg_tab.count LOOP
2983:
3046: RETCODE := G_WARNING;
3047: END IF;
3048: RETURN;
3049: */
3050: msc_util.msc_log('Partitions are not found in MSC_ATP_PEGGING. Launch request to create partitions');
3051: l_request_id := FND_REQUEST.SUBMIT_REQUEST(
3052: 'MSC',
3053: 'MSCSUPRT',
3054: NULL, -- description
3091: IF PG_DEBUG in ('Y', 'C') THEN
3092: msc_util.msc_log('before deleting old ATP pegging data');
3093: END IF;
3094:
3095: DELETE MSC_ATP_PEGGING where plan_id = p_plan_id;
3096:
3097: IF PG_DEBUG in ('Y', 'C') THEN
3098: msc_util.msc_log('After deleting old ATP pegging info');
3099: END IF;
3101: END IF;
3102:
3103: IF p_share_partition = 'Y' THEN
3104:
3105: l_temp_table := 'MSC_ATP_PEGGING';
3106: ELSE
3107:
3108: IF PG_DEBUG in ('Y', 'C') THEN
3109: msc_util.msc_log('not a shared plan partition, insert data into temp tables');
3119: INTO l_tbspace, l_ind_tbspace
3120: FROM all_tab_partitions t,
3121: all_part_indexes i
3122: WHERE t.table_owner = l_msc_schema
3123: AND t.table_name = 'MSC_ATP_PEGGING'
3124: AND t.partition_name = l_partition_name
3125: AND i.owner (+) = t.table_owner
3126: AND i.table_name (+) = t.table_name
3127: AND rownum = 1;
3134: --6113544
3135: l_insert_stmt := 'CREATE TABLE ' || l_temp_table
3136: || ' TABLESPACE ' || l_tbspace
3137: || ' PCTFREE 0 STORAGE(INITIAL 40K NEXT 5M PCTINCREASE 0)'
3138: || ' as select * from MSC_ATP_PEGGING where 1=2 ';
3139:
3140: /*
3141: l_insert_stmt := 'CREATE TABLE ' || l_temp_table || '(
3142: plan_id NUMBER NOT NULL,
4319: creation_date,
4320: last_updated_by,
4321: last_update_date
4322: FROM
4323: msc_atp_peg_temp
4324: WHERE plan_id = :p_plan_id
4325: AND end_demand_id = :l_end_demand_id
4326: AND relief_type in (2, 3) ';
4327:
4343: -- End Bug 3344102 Secod insert the data into the main table.
4344: */
4345:
4346: END LOOP; -- End config_items loop
4347: -- insert into MSC_ATP_PEG_TEMP_NEW select * from MSC_ATP_PEG_TEMP; --MSC_ATP_PEG_TEMP
4348: -- End Bug 3344102 Call Creation of ATP Pegging Data for Allocated ATP case.
4349: IF ((G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1)
4350: AND l_time_phased_atp = 'N') THEN
4351: Create_Pre_Allocation_Reliefs ( p_plan_id,
4384:
4385: IF p_share_partition = 'Y' THEN
4386:
4387: IF PG_DEBUG in ('Y', 'C') THEN
4388: msc_util.msc_log('Analyze Plan partition for MSC_ATP_PEGGING');
4389: END IF;
4390: fnd_stats.gather_table_stats(ownname=>'MSC',tabname=>'MSC_ATP_PEGGING',
4391: partname=>'ATP_PEGGING_999999',
4392: granularity=>'PARTITION',
4386:
4387: IF PG_DEBUG in ('Y', 'C') THEN
4388: msc_util.msc_log('Analyze Plan partition for MSC_ATP_PEGGING');
4389: END IF;
4390: fnd_stats.gather_table_stats(ownname=>'MSC',tabname=>'MSC_ATP_PEGGING',
4391: partname=>'ATP_PEGGING_999999',
4392: granularity=>'PARTITION',
4393: percent =>10);
4394:
4492: statement => l_sql_stmt,
4493: object_name => l_temp_table );
4494: END ;
4495: IF PG_DEBUG in ('Y', 'C') THEN
4496: msc_util.msc_log('Gather Table Stats for MSC_ATP_PEGGING');
4497: END IF;
4498:
4499: fnd_stats.gather_table_stats('MSC', 'ATP_PEGGING' || to_char(l_plan_id),
4500: granularity => 'ALL');
4502: IF PG_DEBUG in ('Y', 'C') THEN
4503: msc_util.msc_log('swap partition ');
4504: END IF;
4505:
4506: l_sql_stmt := 'ALTER TABLE msc_atp_pegging EXCHANGE PARTITION ' ||
4507: l_partition_name ||
4508: ' with table MSC_TEMP_ATP_PEGG_'|| to_char(l_plan_id) ||
4509: ' including indexes without validation';
4510:
4509: ' including indexes without validation';
4510:
4511: BEGIN
4512: IF PG_DEBUG in ('Y', 'C') THEN
4513: msc_util.msc_log('Before alter table msc_atp_pegging');
4514: END IF;
4515: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
4516: APPLICATION_SHORT_NAME => 'MSC',
4517: STATEMENT_TYPE => ad_ddl.alter_table,
4515: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
4516: APPLICATION_SHORT_NAME => 'MSC',
4517: STATEMENT_TYPE => ad_ddl.alter_table,
4518: STATEMENT => l_sql_stmt,
4519: OBJECT_NAME => 'MSC_ATP_PEGGING');
4520: END;
4521:
4522: END IF; -- p_share_partition = 'Y'
4523:
4589:
4590: msc_util.msc_log('Deleting Existing ATP Pegging data for plan : ' || p_plan_id);
4591:
4592: IF l_share_partition = 'Y' THEN
4593: DELETE from msc_atp_pegging
4594: where plan_id = p_plan_id;
4595:
4596: IF PG_DEBUG in ('Y', 'C') THEN
4597: msc_util.msc_log ('Post_Plan_Pegging: Number of rows deleted '|| SQL%ROWCOUNT);
4639:
4640: msc_util.msc_log('Calling Generate_Simplified_Pegging ' );
4641: END IF;
4642:
4643: MSC_ATP_PEG.Generate_Simplified_Pegging(p_plan_id, l_share_partition,
4644: l_applsys_schema, l_ret_code);
4645: IF PG_DEBUG in ('Y', 'C') THEN
4646: msc_util.msc_log('After Call to Generate_Simplified_Pegging ' );
4647: msc_util.msc_log('Return Code is := ' || l_ret_code);
4836: l_original_item_id,
4837: l_original_demand_date
4838: -- End CTO_PF_PRJ Impacts
4839: FROM
4840: msc_atp_pegging map,
4841: msc_demands d
4842: WHERE map.plan_id = p_plan_id
4843: --AND map.sr_instance_id = p_instance_id -- removed to support multiple instances in plan.
4844: AND map.sales_order_line_id in (p_identifier, p_config_line_id)
4852: -- AND (map.disposition_id, map.prev_pegging_id) IN
4853: AND (map.prev_pegging_id) IN
4854: (SELECT map2.pegging_id
4855: -- End Bug 3890723
4856: FROM msc_atp_pegging map2
4857: WHERE map2.plan_id = p_plan_id
4858: AND map2.sales_order_line_id in (p_identifier, p_config_line_id)
4859: AND DECODE(map2.demand_source_type,100,map2.demand_source_type,-1)
4860: =decode(p_demand_source_type,
5022: Decode(map.relief_type, 7, 1, NULL)
5023: -- pf_display_flag = 1 when offseting bucketed demand.
5024: -- NULL will be the default value.
5025: -- END CTO_PF_PRJ_2 Impacts
5026: FROM msc_atp_pegging map
5027: WHERE map.sr_instance_id = l_instance_id(i)
5028: AND map.plan_id = p_plan_id
5029: AND DECODE(map.demand_source_type,100,map.demand_source_type,-1)
5030: =decode(p_demand_source_type,
5160: Decode(map.relief_type, 7, 60, NULL),
5161: --Decode(map.relief_type, 7, 51, 60),
5162: 1 -- Always 1 for PF.
5163: -- END CTO_PF_PRJ_2 Impacts
5164: FROM msc_atp_pegging map
5165: WHERE map.sr_instance_id = l_instance_id(i)
5166: AND map.plan_id = p_plan_id
5167: AND DECODE(map.demand_source_type,100,map.demand_source_type,-1)
5168: =decode(p_demand_source_type,
5266:
5267: -- Bug 3344102 A variable/handle for processing reliefs/offsets.
5268: l_offset_type NUMBER := NULL;
5269:
5270: -- Bug 3381464 Array to track original supplies for updating msc_atp_pegging
5271: l_orig_supply_id MRP_ATP_PUB.Number_Arr := MRP_ATP_PUB.Number_Arr();
5272: -- Bug 3717618 Introduce a pegging_id array to track pegging
5273: -- For filtering out released/firmed supplies and pegging both are needed.
5274: l_pegging_id MRP_ATP_PUB.Number_Arr := MRP_ATP_PUB.Number_Arr();
5359: l_offset_supply_id,
5360: l_ship_date,
5361: l_dock_date
5362: FROM
5363: msc_atp_pegging map,
5364: msc_supplies s
5365: WHERE map.plan_id = p_plan_id
5366: --AND map.sr_instance_id = p_instance_id -- removed to support multiple instances in plan.
5367: AND DECODE(map.demand_source_type,100,map.demand_source_type,-1)
5375: -- End CTO_PF_PRJ_2 Impacts.
5376: -- Bug 3717618 Ensure that Firm or Released supplies are not included.
5377: AND (map.supply_id, map.pegging_id) NOT IN
5378: (SELECT supply_id, pegging_id
5379: FROM msc_atp_pegging mapeg1
5380: WHERE plan_id = p_plan_id
5381: AND DECODE(mapeg1.demand_source_type,100,mapeg1.demand_source_type,-1)
5382: =decode(p_demand_source_type, 100,
5383: p_demand_source_type, -1) --CMRO
5500: msc_sch_wb.atp_debug('Add_Offset_Supplies: Number of rows inserted '||
5501: SQL%ROWCOUNT);
5502: END IF;
5503:
5504: -- Bug 3381464 Update offset_supply_id in msc_atp_pegging.
5505: FORALL i in 1..l_del_rows
5506: UPDATE msc_atp_pegging map1
5507: SET offset_supply_id = x_supply_id(i)
5508: WHERE map1.plan_id = p_plan_id
5502: END IF;
5503:
5504: -- Bug 3381464 Update offset_supply_id in msc_atp_pegging.
5505: FORALL i in 1..l_del_rows
5506: UPDATE msc_atp_pegging map1
5507: SET offset_supply_id = x_supply_id(i)
5508: WHERE map1.plan_id = p_plan_id
5509: AND DECODE(map1.demand_source_type,100,map1.demand_source_type,-1)
5510: =decode(p_demand_source_type,
5578: l_user_id,
5579: l_sysdate,
5580: l_user_id,
5581: l_sysdate
5582: FROM msc_atp_pegging map
5583: WHERE map.sr_instance_id = l_instance_id(i)
5584: AND map.plan_id = p_plan_id
5585: AND DECODE(map.demand_source_type,100,map.demand_source_type,-1)
5586: =decode(p_demand_source_type,
5631:
5632: EXCEPTION
5633: WHEN OTHERS THEN
5634: BEGIN
5635: -- Bug 3381464 Update offset_supply_id in msc_atp_pegging.
5636: FORALL i in 1..l_del_rows
5637: UPDATE msc_atp_pegging map1
5638: SET offset_supply_id = NULL
5639: WHERE map1.plan_id = p_plan_id
5633: WHEN OTHERS THEN
5634: BEGIN
5635: -- Bug 3381464 Update offset_supply_id in msc_atp_pegging.
5636: FORALL i in 1..l_del_rows
5637: UPDATE msc_atp_pegging map1
5638: SET offset_supply_id = NULL
5639: WHERE map1.plan_id = p_plan_id
5640: AND DECODE(map1.demand_source_type,100,map1.demand_source_type,-1)
5641: =decode(p_demand_source_type,
5809: map.daily_relief_qty,
5810: decode(map.resource_id,-1,map.relief_quantity,to_number(null)) load_rate,
5811: REQ.assigned_units, -- 0 originally.
5812: REQ.std_op_code
5813: FROM msc_atp_pegging map,
5814: msc_resource_requirements REQ,
5815: -- Bug 3381464 -- Join to Pegging to obtain offset supply ids.
5816: msc_atp_pegging map2
5817: WHERE map.plan_id = p_plan_id
5812: REQ.std_op_code
5813: FROM msc_atp_pegging map,
5814: msc_resource_requirements REQ,
5815: -- Bug 3381464 -- Join to Pegging to obtain offset supply ids.
5816: msc_atp_pegging map2
5817: WHERE map.plan_id = p_plan_id
5818: --AND map.sr_instance_id = p_instance_id -- removed to support multiple instances in plan.
5819: AND DECODE(map.demand_source_type,100,map.demand_source_type,-1)
5820: =decode(p_demand_source_type,
6111: WHERE
6112: -- Bug 3629191 :All where clause except plan_id and demand_id are commmented
6113: /* sr_instance_id = p_instance_id
6114: (SELECT sr_instance_id
6115: FROM msc_atp_pegging
6116: WHERE plan_id = p_plan_ids(m)
6117: AND sales_order_line_id = p_identifiers(m)
6118: AND DECODE(demand_source_type,100,demand_source_type,-1)
6119: =decode(p_demand_source_type(m),
6154: WHERE
6155: -- Bug 3629191 :All where clause except plan_id and parent_demand_id are commmented
6156: /*sr_instance_id IN
6157: (SELECT sr_instance_id
6158: FROM msc_atp_pegging
6159: WHERE plan_id = p_plan_ids(m)
6160: AND sales_order_line_id = p_identifiers(m)
6161: AND DECODE(demand_source_type,100,demand_source_type,-1)
6162: =decode(p_demand_source_type(m),
6249: l_offset_type := 2;
6250: END IF;
6251: -- End CTO_PF_PRJ_2 Changes
6252:
6253: -- Bug 3381464 Update offset_supply_id in msc_atp_pegging.
6254: --Bug 3629191
6255: FORALL m in 1..p_del_supply_ids.COUNT
6256: UPDATE msc_atp_pegging map1
6257: SET offset_supply_id = NULL
6252:
6253: -- Bug 3381464 Update offset_supply_id in msc_atp_pegging.
6254: --Bug 3629191
6255: FORALL m in 1..p_del_supply_ids.COUNT
6256: UPDATE msc_atp_pegging map1
6257: SET offset_supply_id = NULL
6258: WHERE map1.plan_id = p_atp_peg_supplies_plan_ids(m)
6259: -- Bug 3629191: where clause on demand_source_type, sales_order_line_id
6260: -- and inventory_item_id are removed
6292: WHERE
6293: -- Bug 3629191 :All where clause except and transaction_id are commmented
6294: /* sr_instance_id = p_instance_id
6295: (SELECT sr_instance_id
6296: FROM msc_atp_pegging
6297: WHERE plan_id = p_plan_ids(m)
6298: AND sales_order_line_id = p_identifiers(m)
6299: AND DECODE(demand_source_type,100,demand_source_type,-1)
6300: =decode(p_demand_source_type(m),
6335: WHERE
6336: -- Bug 3629191 :All where clause except and parent_transaction_id are commmented
6337: /* sr_instance_id = p_instance_id
6338: (SELECT sr_instance_id
6339: FROM msc_atp_pegging
6340: WHERE plan_id = p_plan_ids(m)
6341: -- CTO_PF_PRJ_2 Changes Use Relief Type
6342: AND relief_type = l_offset_type
6343: -- End CTO_PF_PRJ_2 Changes
6421: WHERE
6422: -- Bug 3629191 :All where clause except and transaction_id are commmented
6423: /*sr_instance_id = p_instance_id
6424: (SELECT sr_instance_id
6425: FROM msc_atp_pegging
6426: WHERE plan_id = p_plan_ids(m)
6427: AND sales_order_line_id = p_identifiers(m)
6428: AND DECODE(demand_source_type,100,demand_source_type,-1)
6429: =decode(p_demand_source_type(m),
6568: l_dmd_offset_typ := 3;
6569: END IF;
6570: -- First create the demand records for component members and component family items.
6571:
6572: INSERT INTO msc_atp_pegging
6573: (reference_item_id, inventory_item_id, plan_id, sr_instance_id,
6574: organization_id, sales_order_line_id, demand_source_type, bom_item_type, --cmro
6575: transaction_date, demand_id, demand_quantity,
6576: disposition_id, demand_class, supply_id, supply_quantity,
6593: mapt.pegging_id, mapt.prev_pegging_id, mapt.end_pegging_id, mapt.end_demand_id,
6594: mapt.created_by, mapt.creation_date, mapt.last_updated_by, mapt.last_update_date,
6595: mapt.inventory_item_id, mapt.transaction_date,
6596: alocd.customer_id, alocd.ship_to_site_id
6597: FROM msc_atp_peg_temp mapt, msc_alloc_demands alocd,
6598: msc_system_items msi
6599: WHERE mapt.reference_item_id = p_reference_item_id
6600: AND mapt.plan_id = p_plan_id
6601: AND mapt.sales_order_line_id = NVL(p_config_order_line_id, p_model_order_line_id)
6617: SQL%ROWCOUNT);
6618: END IF;
6619:
6620: -- Now Create Rest of the Records.
6621: INSERT INTO msc_atp_pegging
6622: (reference_item_id, inventory_item_id, plan_id, sr_instance_id,
6623: organization_id, sales_order_line_id, demand_source_type, bom_item_type, --cmro
6624: transaction_date, demand_id, demand_quantity,
6625: disposition_id, demand_class, supply_id, supply_quantity,
6638: relief_type, relief_quantity, daily_relief_qty,
6639: pegging_id, prev_pegging_id, end_pegging_id, end_demand_id,
6640: created_by, creation_date, last_updated_by, last_update_date,
6641: customer_id, customer_site_id
6642: FROM msc_atp_peg_temp mapt
6643: WHERE mapt.reference_item_id = p_reference_item_id
6644: AND mapt.plan_id = p_plan_id
6645: AND mapt.sales_order_line_id = NVL(p_config_order_line_id, p_model_order_line_id)
6646: --AND mapt.demand_source_type = p_demand_source_type
6647: AND mapt.end_demand_id = p_end_demand_id
6648: -- Process everything except PF member and family demands.
6649: AND mapt.inventory_item_id NOT IN
6650: (SELECT NVL(original_item_id, inventory_item_id)
6651: FROM msc_atp_pegging
6652: WHERE reference_item_id = p_reference_item_id
6653: AND plan_id = p_plan_id
6654: AND sales_order_line_id = NVL(p_config_order_line_id, p_model_order_line_id)
6655: --AND demand_source_type = p_demand_source_type
6696: IS
6697:
6698: l_reference_item_id NUMBER;
6699: i NUMBER;
6700: atp_peg_rec msc_atp_pegging%ROWTYPE;
6701: my_sqlcode NUMBER;
6702: -- Bug 3334643 Track the plan_id
6703: l_plan_id NUMBER;
6704:
6729: x_return_status := FND_API.G_RET_STS_SUCCESS;
6730:
6731: -- CTO_PF_PRJ_2 Impacts
6732: IF (MSC_ATP_PVT.G_CTO_PF_ATP = 'Y') THEN
6733: l_insert_temp_table := 'MSC_ATP_PEG_TEMP';
6734: ELSE
6735: l_insert_temp_table := 'MSC_ATP_PEGGING';
6736: END IF;
6737:
6731: -- CTO_PF_PRJ_2 Impacts
6732: IF (MSC_ATP_PVT.G_CTO_PF_ATP = 'Y') THEN
6733: l_insert_temp_table := 'MSC_ATP_PEG_TEMP';
6734: ELSE
6735: l_insert_temp_table := 'MSC_ATP_PEGGING';
6736: END IF;
6737:
6738: IF ((MSC_ATP_PVT.G_INV_CTP = 4) AND
6739: (MSC_ATP_PVT.G_ALLOCATED_ATP = 'Y') AND
6750:
6751: -- First Delete old pegging data.
6752: BEGIN
6753:
6754: DELETE from msc_atp_pegging
6755: WHERE plan_id = p_old_plan_id
6756: AND relief_type > 0
6757: AND sales_order_line_id in (NVL(p_config_order_line_id, -1), p_model_order_line_id)
6758: AND decode(demand_source_type,100,demand_source_type,-1)
7094: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7095:
7096: END Create_Atp_Pegging;
7097:
7098: END MSC_ATP_PEG;