The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT owner
FROM dba_objects
WHERE owner = owner
AND object_type = 'TABLE'
AND object_name = 'MDP_MATRIX'
ORDER BY created DESC;
SELECT demand_plan_id
FROM msd_demand_plans
WHERE demand_plan_id = 5555555;
SELECT scenario_id
FROM msd_dp_scenarios
WHERE scenario_name LIKE
(SELECT DISTINCT(scenario_name)
FROM msd_dp_scenario_entries
WHERE scenario_id = -23453)
;
SELECT DISTINCT(scenario_name)
FROM msd_dp_scenario_entries
WHERE scenario_id = -23453;
SELECT COUNT(*)
FROM msd_dp_scenario_entries
WHERE demand_plan_id = 5555555
AND scenario_id = l_scenario_id;
SELECT * from MSC_ITEM_FAILURE_RATES
where process_flag = 'E';
x_rows_deleted NUMBER := 0;
lv_sql_stmt := ' UPDATE ' || msd_schema_name || '.MSD_DEM_INS_BASE_HISTORY t1'
|| ' SET t1.level1_sr_pk = ( SELECT t2.sr_inventory_item_id '
|| ' FROM msc_system_items t2 '
|| ' WHERE t2.sr_instance_id = :p_instance_id '
|| ' AND t2.item_name = t1.level1 '
|| ' AND rownum = 1) ';
lv_sql_stmt := ' UPDATE ' || msd_schema_name
|| '.MSD_DEM_FLD_SER_USG_HISTORY t1'
|| ' SET t1.level1_sr_pk = ( SELECT distinct(t2.sr_inventory_item_id) '
|| ' FROM msc_system_items t2 '
|| ' WHERE t2.sr_instance_id = :p_instance_id '
|| ' AND t2.item_name = t1.level1 '
|| ' AND rownum = 1) ' ;
lv_sql_stmt1 := ' UPDATE ' || msd_schema_name
|| '.MSD_DEM_FLD_SER_USG_HISTORY t1'
|| ' SET t1.level2_sr_pk = ( SELECT t2.region_id '
|| ' FROM msc_regions t2 '
|| ' where t2.sr_instance_id = :p_instance_id '
|| ' AND t2.zone = t1.level2 '
|| ' AND t2.zone_usage = 1)' ;
lv_sql_stmt := ' UPDATE '
|| msd_schema_name
|| '.MSD_DEM_DPT_REP_USG_HISTORY t1'
|| ' SET t1.level1_sr_pk = ( SELECT distinct(t2.sr_inventory_item_id) '
|| ' FROM msc_system_items t2 '
|| ' WHERE t2.sr_instance_id = :p_instance_id '
|| ' AND t2.item_name = t1.level1) ';
lv_sql_stmt1 := ' UPDATE '
|| msd_schema_name
|| '.MSD_DEM_DPT_REP_USG_HISTORY t1'
|| ' SET t1.level2_sr_pk = ( SELECT t2.sr_tp_id '
|| ' FROM msc_trading_partners t2 '
|| ' where t2.sr_instance_id = :p_instance_id '
|| ' AND t2.organization_code = t1.level2) ';
lv_sql_stmt := ' UPDATE '
|| msd_schema_name
|| '.MSD_DEM_SRP_RETURN_HISTORY t1'
|| ' SET t1.level1_sr_pk = ( SELECT distinct(t2.sr_inventory_item_id) '
|| ' FROM msc_system_items t2 '
|| ' WHERE t2.sr_instance_id = :p_instance_id '
|| ' AND t2.item_name = t1.level1 '
|| ' AND rownum = 1) ';
lv_sql_stmt1 := ' UPDATE '
|| msd_schema_name || '.MSD_DEM_SRP_RETURN_HISTORY t1'
|| ' SET t1.level2_sr_pk = ( SELECT t2.region_id '
|| ' FROM msc_regions t2 '
|| ' where t2.sr_instance_id = :p_instance_id '
|| ' AND t2.zone = t1.level2 '
|| ' AND t2.zone_usage = 1)';
lv_sql_stmt := 'UPDATE ' || msc_schema_name
|| '.MSC_ITEM_FAILURE_RATES t1'
|| ' SET t1.using_assembly_id = nvl((select distinct(t2.inventory_item_id)'
|| ' FROM msc_system_items t2 '
|| ' where t2.sr_instance_id = :p_instance_id '
|| ' and t2.item_name = t1.using_assembly_name),-55555) ';
lv_sql_stmt1 := 'UPDATE ' || msc_schema_name
|| '.MSC_ITEM_FAILURE_RATES t1'
|| ' SET t1.inventory_item_id = nvl((select distinct(t2.inventory_item_id) '
|| ' FROM msc_system_items t2 '
|| ' where t2.sr_instance_id = :p_instance_id '
|| ' and t2.item_name = t1.item_name),-55555) ';
lv_sql_stmt2 := ' UPDATE ' || msc_schema_name
|| '.MSC_ITEM_FAILURE_RATES '
|| ' SET FAILURE_RATE = 1'
|| ' where failure_rate > 1'
|| ' and process_flag = ''N'' ';
lv_sql_stmt3 := ' UPDATE ' || msc_schema_name
|| '.MSC_ITEM_FAILURE_RATES '
|| ' SET FAILURE_RATE = 0'
|| ' where failure_rate < 0'
|| ' and process_flag = ''N'' ';
lv_sql_stmt4 := ' UPDATE ' || msc_schema_name
|| '.MSC_ITEM_FAILURE_RATES '
|| ' SET PROCESS_FLAG = ''P'''
|| ' where PROCESS_FLAG = ''N''';
lv_sql_stmt5 := ' UPDATE ' || msc_schema_name
|| '.MSC_ITEM_FAILURE_RATES '
|| ' SET PROCESS_FLAG = ''E'''
|| ' where PROCESS_FLAG = ''N'''
|| ' and (USING_ASSEMBLY_ID = -55555 or INVENTORY_ITEM_ID = -55555) ';
lv_sql_stmt6 := 'DELETE FROM ' || msc_schema_name
|| '.MSC_ITEM_FAILURE_RATES '
|| 'WHERE PROCESS_FLAG = ''E''';
INSERT
INTO msd_demand_plans(demand_plan_id, organization_id, demand_plan_name, last_update_date, last_updated_by, creation_date, created_by, sr_instance_id, use_org_specific_bom_flag)
VALUES(5555555, -23453, 'SRP DUMMY PLAN', trunc(sysdate), fnd_global.user_id, trunc(sysdate), fnd_global.user_id, -23453, 'N');
lv_sql_stmt := 'DELETE FROM MSD_DP_SCENARIO_ENTRIES WHERE demand_plan_id = 5555555 and scenario_id = :l_scenario_id';
UPDATE MSD_DP_SCENARIO_ENTRIES SET SCENARIO_ID = l_scenario_id where scenario_name = l_scenario_name
and demand_plan_id = 5555555 and scenario_id = -23453;
lv_sql_stmt := 'INSERT INTO MSD_DP_SCENARIOS(demand_plan_id,scenario_id,scenario_name, '
|| 'last_update_date,last_updated_by,creation_date,created_by)'
|| 'values(5555555,MSD_DP_SCENARIOS_S.nextval,' || bind_var
|| 'trunc(sysdate),fnd_global.user_id,trunc(sysdate),fnd_global.user_id)';
INSERT INTO MSD_DP_SCENARIO_OUTPUT_LEVELS(demand_plan_id,scenario_id,level_id,last_update_date,last_updated_by,creation_date,created_by)
values(5555555,MSD_DP_SCENARIOS_S.CURRVAL,1,trunc(sysdate),fnd_global.user_id,trunc(sysdate),fnd_global.user_id);
lv_sql_stmt1 := 'UPDATE MSD_DP_SCENARIO_ENTRIES SET SCENARIO_ID = MSD_DP_SCENARIOS_S.CURRVAL' || ' WHERE SCENARIO_NAME = :l_scenario_name ';
lv_sql_stmt2 := 'DELETE FROM MSD_DP_SCN_ENTRIES_DENORM ' || 'WHERE SCENARIO_ID = :l_scenario_id ' || 'AND DEMAND_PLAN_ID = 5555555';
INSERT
INTO msd_dp_scn_entries_denorm(demand_plan_id, scenario_id, demand_id, sr_inventory_item_id, sr_organization_id, start_time, end_time, quantity, creation_date, created_by)
(SELECT mdse.demand_plan_id,
mdse.scenario_id,
mdse.entry_id,
msi.sr_inventory_item_id,
mtp.sr_tp_id,
mdse.time_lvl_val_from,
mdse.time_lvl_val_to,
mdse.total_quantity,
mdse.creation_date,
mdse.created_by
FROM msd_dp_scenario_entries mdse,
msc_trading_partners mtp,
msc_system_items msi
WHERE mdse.demand_plan_id = 5555555
AND mdse.scenario_id = l_scenario_id
AND mdse.organization_lvl_val = mtp.organization_code
AND mdse.product_lvl_val = msi.item_name
AND mtp.sr_instance_id = p_instance_id
AND mtp.partner_type = 3
AND mtp.sr_tp_id = msi.organization_id
AND msi.sr_instance_id = p_instance_id
AND msi.plan_id = -1)
;
DELETE FROM msd_dp_scenario_entries mdse
WHERE mdse.scenario_id = l_scenario_id
AND mdse.organization_lvl_val IN
(SELECT organization_code
FROM msc_trading_partners
WHERE sr_instance_id = p_instance_id
AND partner_type = 3
AND organization_code LIKE mdse.organization_lvl_val
AND rownum = 1)
AND mdse.product_lvl_val IN
(SELECT item_name
FROM msc_system_items
WHERE sr_instance_id = p_instance_id
AND item_name LIKE mdse.product_lvl_val
AND rownum = 1)
;
x_sql := 'SELECT sum(a) from (select 1 a FROM ' || x_table_name
|| ' GROUP BY LEVEL1_SR_PK, LEVEL2_SR_PK, SDATE '
|| ' HAVING count(1) > 1)';
x_sql := 'DELETE FROM ' || x_table_name
|| ' WHERE rowid NOT IN (SELECT max(rowid) FROM ' || x_table_name
|| ' GROUP BY LEVEL1_SR_PK, LEVEL2_SR_PK, SDATE ) ';
x_rows_deleted := SQL%ROWCOUNT;
msd_dem_common_utilities.log_message ('Duplicate rows deleted - ' || x_rows_deleted);