[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_sql := 'SELECT tql.lorder ' ||
' FROM ' || x_table_name || ' tql, ';
x_sql := 'SELECT cf.computed_name ' ||
' FROM ' || x_table_name || ' tqs, ';
x_sql := 'SELECT tg_res, months_number, inputs_column, is_default ' ||
' FROM ' || x_tgroup_res ||
' WHERE tg_res_id = ' || p_time_res;
x_sql := 'SELECT time_bucket, aggregation_method ' ||
' FROM ' || x_dm_wiz_dm_def ||
' WHERE dm_or_template = 2 ' ||
' AND is_active = 1 ' ||
' AND rownum < 2 ';
p_time_from_clause := ' (SELECT min(datet) start_time, max(datet) end_time ' ||
' FROM ' || x_inputs || ' GROUP BY ' || x_inputs_column ||
' ) inp ';
x_sql := 'SELECT count(*) FROM ' || x_inputs || ' WHERE ' || x_inputs_column || ' = 1 ';
SELECT min(instance_id)
FROM msc_apps_instances
WHERE instance_type <> 3
AND validation_org_id IS NOT NULL;
SELECT mtil.sr_tp_id
INTO x_sr_customer_pk
FROM
msc_tp_id_lid mtil
WHERE
mtil.sr_cust_account_number = x_account_number
and mtil.sr_instance_id = p_sr_instance_id;
SELECT mr.region_id
INTO x_sr_zone_pk
FROM msc_regions mr
WHERE
mr.zone = x_zone
AND mr.sr_instance_id = p_sr_instance_id;
x_select_clause VARCHAR2(3000) := NULL;
x_insert_clause VARCHAR2(1000) := NULL;
x_sql := 'SELECT id, presentation_type, view_name, ' ||
' time_res_id, unit_id, index_id, data_scale, ' ||
' integration_type, export_type, last_export_date ' ||
' FROM ' || x_table_name ||
' WHERE upper(query_name) = ''' || x_export_data_profile || '''';
x_sql := 'SELECT count(1) FROM all_views ' ||
' WHERE owner = upper(''' || x_schema || ''')' ||
' AND view_name = upper(''' || x_view_name || ''')';
x_select_clause := ' SELECT ' || x_demand_plan_id || ' , ' ||
x_scenario_id || ' , ' ||
' rownum - 1 , ';
x_select_clause := x_select_clause || x_bucket_type || ' , '
|| x_start_time || ' , '
|| x_end_time || ' , ';
x_select_clause := x_select_clause || ' msi.sr_instance_id, ';
x_select_clause := x_select_clause || x_sr_organization_id || ' , ' ||
' msi.sr_inventory_item_id, ';
x_select_clause := x_select_clause || x_sr_ship_to_loc_id || ' , ' ||
x_sr_customer_id || ' , ' ||
x_sr_zone_id || ' , ';
x_select_clause := x_select_clause || x_sr_demand_class || ' , ' ||
' msi.inventory_item_id, ';
x_select_clause := x_select_clause || '''' || x_uom_code || ''', ' ||
' msi.uom_code, ';
x_select_clause := x_select_clause || ' msi.list_price * ((100 - msi.average_discount)/100), ';
x_select_clause := x_select_clause || x_quantity || ' , ';
x_select_clause := x_select_clause || ' NULL , NULL , ';
x_select_clause := x_select_clause || '''' || x_error_type || ''' , exp.' || x_error_column || ' , ';
x_select_clause := x_select_clause || ' NULL , ';
x_select_clause := x_select_clause || ' exp.' || x_demand_priority_column || ' , ';
x_select_clause := x_select_clause || ' sysdate, ' ||
' FND_GLOBAL.USER_ID, ' ||
' FND_GLOBAL.LOGIN_ID ';
x_insert_clause := 'INSERT 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, ' ||
' CREATION_DATE, ' ||
' CREATED_BY, ' ||
' LAST_UPDATE_LOGIN )';
x_large_sql := x_insert_clause || x_select_clause || x_from_clause || x_where_clause;
/* Delete all data in the denorm for the export data profile */
DELETE FROM MSD_DP_SCN_ENTRIES_DENORM
WHERE demand_plan_id = x_demand_plan_id
AND scenario_id = x_scenario_id;
/* Insert forecast data into denorm table */
EXECUTE IMMEDIATE x_large_sql;