[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_sql_stmt := 'SELECT nvl(sum(tq.id), 0) '
|| ' FROM msd_dem_transfer_query tq '
|| ' WHERE tq.query_name = ''' || x_data_profile_name || ''''
|| ' AND msd_dem_upload_forecast.is_valid_scenario(tq.id) = 1 ';
x_sql_stmt := 'SELECT nvl(sum(scenario_id), 0) '
|| ' FROM msd_dp_scenarios mds '
|| ' WHERE mds.demand_plan_id = ' || MSD_DEM_UPLOAD_FORECAST.C_DEMAND_PLAN_ID
|| ' AND mds.scenario_name = ''' || x_new_plan_name || '''';
/* Create/Update an entry for the Plan Name in the table msd_dp_scenarios */
IF (x_scenario_id = 0) THEN
SELECT MSD_DP_SCENARIOS_S.nextval
INTO x_scenario_id
FROM DUAL;
INSERT INTO msd_dp_scenarios (
demand_plan_id,
scenario_id,
scenario_name,
forecast_based_on,
sc_type,
error_type,
associate_parameter,
last_update_date,
last_updated_by,
creation_date,
created_by )
VALUES (
MSD_DEM_UPLOAD_FORECAST.C_DEMAND_PLAN_ID,
x_scenario_id,
x_new_plan_name,
substrb(x_data_profile_name,1,30),
msd_dem_upload_forecast.is_global_scenario(x_data_profile_id),
msd_dem_upload_forecast.get_error_type(x_data_profile_id),
C_ASSOCIATE_PARAMETER,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID);
UPDATE msd_dp_scenarios
SET
forecast_based_on = substrb(x_data_profile_name,1,30),
sc_type = msd_dem_upload_forecast.is_global_scenario(x_data_profile_id),
error_type = msd_dem_upload_forecast.get_error_type(x_data_profile_id),
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID
WHERE scenario_id = x_scenario_id;
DELETE FROM msd_dp_scenario_output_levels
WHERE scenario_id = x_scenario_id;
x_sql := 'DELETE /*+ PARALLEL (msd_dp_scn_entries_denorm,' || x_parallelism_degree
|| ') */ FROM msd_dp_scn_entries_denorm'
|| ' WHERE scenario_id =' || x_scenario_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 )
SELECT
MSD_DEM_UPLOAD_FORECAST.C_DEMAND_PLAN_ID,
x_scenario_id,
to_number(flv.lookup_code),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID
FROM
msd_dem_transfer_query tq,
msd_dem_transfer_query_levels tql,
msd_dem_group_tables gt,
fnd_lookup_values_vl flv
WHERE
tq.id = x_data_profile_id
AND tql.id = tq.id
AND gt.group_table_id = tql.level_id
AND flv.lookup_type = 'MSD_DEM_LEVELS'
AND to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
flv.meaning,
1,
'group_table_id')) = gt.group_table_id;
x_sql := 'INSERT /*+ PARALLEL (msd_dp_scn_entries_denorm,' || x_parallelism_degree ||
') */ INTO MSD_DP_SCN_ENTRIES_DENORM (' ||
'DEMAND_PLAN_ID, '||
'SCENARIO_ID, '||
'DEMAND_ID, '||
'BUCKET_TYPE, '||
'START_TIME, '||
'END_TIME, '||
'SR_INSTANCE_ID, '||
'SR_ORGANIZATION_ID, '||
'SR_INVENTORY_ITEM_ID, '||
'SR_SHIP_TO_LOC_ID, '||
'SR_CUSTOMER_ID, '||
'SR_ZONE_ID, '||
'DEMAND_CLASS, '||
'INVENTORY_ITEM_ID, '||
'DP_UOM_CODE, '||
'ASCP_UOM_CODE, '||
'UNIT_PRICE, '||
'QUANTITY, '||
'ERROR_TYPE, '||
'FORECAST_ERROR, '||
'PRIORITY, '||
'PF_NAME, '||
'REQUEST_ID,' ||
'MAPE_IN_SAMPLE, '||
'MAPE_OUT_SAMPLE, '||
'FORECAST_VOLATILITY, '||
'AVG_DEMAND, '||
'CREATION_DATE, '||
'CREATED_BY, '||
'LAST_UPDATE_LOGIN )'||
'SELECT ' ||
'entries.DEMAND_PLAN_ID, ' ||
x_scenario_id ||
',entries.DEMAND_ID, ' ||
'entries.BUCKET_TYPE, ' ||
'entries.START_TIME, ' ||
'entries.END_TIME, ' ||
'entries.SR_INSTANCE_ID, ' ||
'entries.SR_ORGANIZATION_ID, ' ||
'entries.SR_INVENTORY_ITEM_ID, ' ||
'entries.SR_SHIP_TO_LOC_ID, ' ||
'entries.SR_CUSTOMER_ID, ' ||
'entries.SR_ZONE_ID, ' ||
'entries.DEMAND_CLASS, ' ||
'entries.INVENTORY_ITEM_ID, ' ||
'entries.DP_UOM_CODE, ' ||
'entries.ASCP_UOM_CODE, ' ||
'entries.UNIT_PRICE, ' ||
'entries.QUANTITY, ' ||
'entries.ERROR_TYPE, ' ||
'entries.FORECAST_ERROR, ' ||
'entries.PRIORITY, ' ||
'entries.PF_NAME, ' ||
'entries.REQUEST_ID, ' ||
'entries.MAPE_IN_SAMPLE, ' ||
'entries.MAPE_OUT_SAMPLE, ' ||
'entries.FORECAST_VOLATILITY, ' ||
'entries.AVG_DEMAND, ' ||
'entries.CREATION_DATE, ' ||
'entries.CREATED_BY, ' ||
'entries.LAST_UPDATE_LOGIN ' ||
'from ' ||
' (select '||
'DEMAND_PLAN_ID, ' ||
'SCENARIO_ID, ' ||
'DEMAND_ID, ' ||
'BUCKET_TYPE, ' ||
'START_TIME, ' ||
'END_TIME, ' ||
'SR_INSTANCE_ID, ' ||
'SR_ORGANIZATION_ID, ' ||
'SR_INVENTORY_ITEM_ID, ' ||
'SR_SHIP_TO_LOC_ID, ' ||
'SR_CUSTOMER_ID, ' ||
'SR_ZONE_ID, ' ||
'DEMAND_CLASS, ' ||
'INVENTORY_ITEM_ID, ' ||
'DP_UOM_CODE, ' ||
'ASCP_UOM_CODE, ' ||
'UNIT_PRICE, ' ||
'QUANTITY, ' ||
'ERROR_TYPE, ' ||
'FORECAST_ERROR, ' ||
'PRIORITY, ' ||
'PF_NAME, ' ||
'REQUEST_ID, ' ||
'MAPE_IN_SAMPLE, ' ||
'MAPE_OUT_SAMPLE, ' ||
'FORECAST_VOLATILITY, ' ||
'AVG_DEMAND, ' ||
'CREATION_DATE, ' ||
'CREATED_BY, ' ||
'LAST_UPDATE_LOGIN ' ||
'FROM msd_dp_scn_entries_denorm ' ||
' WHERE scenario_id = ' || MSD_DEM_UPLOAD_FORECAST.C_SCENARIO_ID_OFFSET || '+ ' || x_data_profile_id || ') entries';
x_sql := 'DELETE /*+ PARALLEL (msd_dp_scn_entries_denorm,' || x_parallelism_degree
|| ') */ FROM msd_dp_scn_entries_denorm'
|| ' WHERE scenario_id =' || MSD_DEM_UPLOAD_FORECAST.C_SCENARIO_ID_OFFSET || '+ ' || x_data_profile_id;
* Update the scenario id in the MSD_DP_SCN_ENTRIES_DENORM table to the Scenario Id generated
* for the given Plan Name.
*
* Return Values -
* The procedure returns a status. The possible return statuses are:
* SUCCESS, ERROR, INVALID_DATA_PROFILE
*
*/
PROCEDURE ASSIGN_PLAN_NAME_TO_FORECAST (
status OUT NOCOPY VARCHAR2,
NewPlanName IN VARCHAR2,
DataProfileName IN VARCHAR2,
ArchiveFlag IN NUMBER)
IS
x_errbuf VARCHAR2(1000) := NULL;
EXECUTE IMMEDIATE ' SELECT supply_plan_id '
|| ' FROM ' || fnd_profile.value('MSD_DEM_SCHEMA') || '.supply_plan sp'
|| ' WHERE sp.plan_id = ' || PlanId
INTO x_member_id;
SELECT object_name into x_mview
FROM dba_objects
WHERE owner = x_schema
AND object_type = 'MATERIALIZED VIEW'
AND object_name = upper(mviewname)
ORDER BY created DESC;
* Procedure Name - DELETE_PLAN
* This procedure deletes the given assigned plan name
*
* 1. If the plan_name is assocaited to supply plans and p_delete_assoc is set NO
* then ERROR out with message
*
* 2. If the plan_name is assocaited to supply plans and p_delete_assoc is set YES
* (OR)
* If the plan_name is assocaited NOT to any supply plan
* then delete all the data for the plan_name from
* msd_dp_scenarios, msd_dp_scenario_output_levels,
* msd_dp_scn_entries_denorm, msc_plan_schedules tables
*
*/
PROCEDURE DELETE_PLAN(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_plan_scn_id IN NUMBER,
p_delete_assoc IN NUMBER )
IS
x_sql_stmt VARCHAR2(500) := NULL;
msd_dem_common_utilities.log_debug ('Entering: msd_ws_dem_rename_forecast.delete_plan - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message (' ' || rpad('Delete Associations', 30) || ' - ' || to_char(p_delete_assoc));
x_sql_stmt := 'SELECT nvl(sum(scenario_id), 0) '
|| ' FROM msd_dp_scenarios mds '
|| ' WHERE associate_parameter = ''Assign Plan Name'' '
|| ' AND mds.demand_plan_id = ' || MSD_DEM_UPLOAD_FORECAST.C_DEMAND_PLAN_ID
|| ' AND mds.scenario_id = ' || p_plan_scn_id ;
x_sql_stmt := 'SELECT nvl(sum(1),0) FROM msc_plan_schedules WHERE input_schedule_id = ' || x_scenario_id ;
msd_dem_common_utilities.log_debug ('p_delete_assoc - ' || p_delete_assoc);
IF (x_attached_flag <> 0 AND p_delete_assoc = C_NO) THEN -- scenario_id is attached, delete_assoc is NO
retcode := -1;
errbuf := 'There are associations for this plan, hence the plan cannot be deleted.';
msd_dem_common_utilities.log_message ('ERROR(1): msd_ws_dem_rename_forecast.delete_plan - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message ('There are associations for this plan, hence the plan cannot be deleted.');
msd_dem_common_utilities.log_message ('If you still want to delete, rerun program with parameter "Delete Associations to Supply Plans" set to yes');
ELSIF (x_attached_flag = 0 OR (x_attached_flag <> 0 AND p_delete_assoc = C_YES) ) THEN -- scenario_id is NOT attached OR scenario_id is attached, delete_assoc is YES
/* msd_dp_scenarios */
x_del_stmt := 'DELETE FROM msd_dp_scenarios WHERE scenario_id = ' || x_scenario_id ;
msd_dem_common_utilities.log_debug ('Rows Deleted - ' || x_count );
x_del_stmt := 'DELETE FROM msd_dp_scenario_output_levels WHERE scenario_id = ' || x_scenario_id ;
msd_dem_common_utilities.log_debug ('Rows Deleted - ' || x_count );
x_del_stmt := 'DELETE FROM msd_dp_scn_entries_denorm WHERE scenario_id = ' || x_scenario_id ;
msd_dem_common_utilities.log_debug ('Rows Deleted - ' || x_count );
IF (x_attached_flag <> 0 AND p_delete_assoc = C_YES) THEN
/* msc_plan_schedules */
x_del_stmt := 'DELETE FROM msc_plan_schedules WHERE input_schedule_id = ' || x_scenario_id ;
msd_dem_common_utilities.log_debug ('Rows Deleted - ' || x_count );
msd_dem_common_utilities.log_debug ('Exiting: msd_ws_dem_rename_forecast.delete_plan - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message ('Exception: msd_ws_dem_rename_forecast.delete_plan - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
END DELETE_PLAN;