[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
EXECUTE IMMEDIATE 'SELECT plan_id FROM ' ||
msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'SUPPLY_PLAN') ||
' WHERE supply_plan_id = ' || p_member_id
INTO x_plan_id;
EXECUTE IMMEDIATE 'SELECT plan_type FROM ' ||
msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'SUPPLY_PLAN') ||
' WHERE supply_plan_id = ' || p_member_id
INTO x_plan_type;
EXECUTE IMMEDIATE 'DELETE FROM ' || x_table_name;
EXECUTE IMMEDIATE 'DELETE FROM ' || x_table_name;
SELECT
series_name,
series_type,
identifier,
custom_view_name,
ps_view_name,
stg_series_col_name
FROM
msd_dem_series
WHERE
series_id = p_series_id;
SELECT
series_id
FROM msd_dem_series
WHERE series_id IN (112, 113);
select curr_start_date
from msc_plans
where plan_id = p_plan_id;
/***** 3. UPDATE START DATES AND END DATES OF THE PURGE PLAN DATA *****/
OPEN c_plan_start_date;
l_sql := 'select datet from '|| g_schema ||'.inputs where datet > '''||l_plan_start_date||''' and rownum = 1 order by datet asc';
l_sql := 'select max(datet) from '||g_schema||'.inputs ';
l_sql := 'select id from '||g_schema||'.transfer_query where query_name = ''Purge Plan Data''';
l_sql := 'select id from '||g_schema||'.transfer_query where query_name = ''Purge Resource Data''';
SELECT
series_id
FROM
msd_dem_series
WHERE
series_id IN (101, 102, 103, 104, 105, 106, 107, 108, 109, 110);
/* Delete non-saleable items from the staging table BIIO_OTHER_PLAN_DATA */
x_sql := ' DELETE FROM ' || g_schema || '.BIIO_OTHER_PLAN_DATA bopd '
|| ' WHERE NOT EXISTS ( SELECT 1 '
|| ' FROM ' || g_schema || '.t_ep_item tei, '
|| g_schema || '.mdp_matrix mm '
|| ' WHERE tei.item = bopd.level2 '
|| ' AND mm.t_ep_item_ep_id = tei.t_ep_item_ep_id '
|| ' AND mm.is_fictive = 0 '
|| ' AND rownum < 2 ) '
|| ' AND avail_sup_std_cap IS NULL '
|| ' AND required_sup_cap IS NULL ';
/* Updated non-supplier series to NULL for non-saleable:buy:critical items from the staging table BIIO_OTHER_PLAN_DATA */
x_sql := ' UPDATE ' || g_schema || '.BIIO_OTHER_PLAN_DATA bopd '
|| ' SET bopd.constrained_fcst = NULL, '
|| ' bopd.prod_plan = NULL, '
|| ' bopd.safety_stk = NULL, '
|| ' bopd.beginning_on_hand = NULL, '
|| ' bopd.dependent_demand = NULL, '
|| ' bopd.planned_shipments = NULL '
|| ' WHERE NOT EXISTS ( SELECT 1 '
|| ' FROM ' || g_schema || '.t_ep_item tei, '
|| g_schema || '.mdp_matrix mm '
|| ' WHERE tei.item = bopd.level2 '
|| ' AND mm.t_ep_item_ep_id = tei.t_ep_item_ep_id '
|| ' AND mm.is_fictive = 0 '
|| ' AND rownum < 2 ) '
|| ' AND ( avail_sup_std_cap IS NOT NULL '
|| ' OR required_sup_cap IS NOT NULL ) ';
EXECUTE IMMEDIATE 'DELETE FROM msd_dem_dates';
x_sql := 'SELECT time_bucket, first_day_of_week, aggregation_method ' ||
' FROM ' || x_dm_table ||
' WHERE dm_or_template = 2 ' ||
' AND is_active = 1 ';
log_debug ('Lowest Time Bucket - Day : Time data not inserted into source msd_dem_dates');
log_debug ('Inserting time data into msd_dem_dates');
x_sql := 'INSERT INTO msd_dem_dates' ||
' (datet, num_of_days, start_date, end_date, last_update_date, last_updated_by, creation_date, created_by, last_update_login) ' ||
' SELECT datet, num_of_days, ' || x_start_date || x_end_date ||
' sysdate, :1, sysdate, :2, :3 ' ||
' FROM ' || x_source_time_table;
l_stmt := 'select input_name from msc_plan_sched_v where plan_id= ' ||p_plan_id ;
l_sql := 'select scenario_status_id from '|| g_schema||'.supply_plan where plan_id = '||p_plan_id;
l_sql := 'select scenario_status_code from '|| g_schema||'.scenario_status where scenario_status_id = '||l_scenario_status_id;
l_sql := 'insert into ' || g_schema||'.biio_supply_plans(supply_plan_code,
supply_plan_desc,
scenario_status_code,
plan_id,
method_status,
demand_schedules,
plan_type,
start_date,
end_date,
last_imported)
VALUES ('''
||x_plan_code ||''','
||''''||x_plan_desc ||''','
||''||l_scenario_status_code ||','
||''||p_plan_id ||','
||''''|| NULL ||''','
||''''||x_dem_sched ||''','
||''''||x_plan_type ||''','
||''''||p_plan_start_date ||''','
||''''||p_end_date ||''','
||''''||sysdate ||''')' ;
l_sql := 'insert into ' || g_schema||'.biio_supply_plans_pop(level_member,
from_date,
until_date,
filter_level,
level_order,
filter_member)
values ('''
||x_plan_code ||''','
||''''||p_plan_start_date ||''','
||''''||sysdate ||''','
||''''|| 'ITEM'||''','
||''||'2' ||','
||''''||'0' ||''')' ;
l_sql := 'insert into ' || g_schema||'.biio_supply_plans_pop(level_member,
from_date,
until_date,
filter_level,
level_order,
filter_member)
values ('''
||x_plan_code ||''','
||''''||p_plan_start_date ||''','
||''''||sysdate ||''','
||''''|| 'DEMAND CLASS'||''','
||''||'2' ||','
||''''||'0' ||''')' ;
l_sql := 'insert into ' || g_schema||'.biio_supply_plans_pop(level_member,
from_date,
until_date,
filter_level,
level_order,
filter_member)
values ('''
||x_plan_code ||''','
||''''||p_plan_start_date ||''','
||''''||sysdate ||''','
||''''|| 'ORGANIZATION'||''','
||''||'1' ||','
||''''||'0' ||''')' ;
l_sql := 'insert into ' || g_schema||'.biio_supply_plans_pop(level_member,
from_date,
until_date,
filter_level,
level_order,
filter_member)
values ('''
||x_plan_code ||''','
||''''||p_plan_start_date ||''','
||''''||sysdate ||''','
||''''|| 'SITE'||''','
||''||'1' ||','
||''''||'0' ||''')' ;
l_sql := 'insert into ' || g_schema||'.biio_supply_plans_pop(level_member,
from_date,
until_date,
filter_level,
level_order,
filter_member)
values ('''
||x_plan_code ||''','
||''''||p_plan_start_date ||''','
||''''||sysdate ||''','
||''''|| 'SALES CHANNEL'||''','
||''||'1' ||','
||''''||'0' ||''')' ;
select sr_instance_id, compile_designator, plan_type, curr_start_date, cutoff_date
from msc_plans
where plan_id = p_plan_id;
select compile_designator, curr_start_date, cutoff_date, sop_enabled, plan_type
from msc_plans
where plan_id=p_plan_id;
l_stmt := 'select plan_id, compile_designator, curr_start_date, cutoff_date, sop_enabled, plan_type
from msc_plans
where sop_enabled = 1 ';
|| ' ( SELECT supply_plan_code FROM '
|| g_schema || '.supply_plan )';
msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_DELETE_STMT', l_instance_id);
msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_DELETE_STMT', l_instance_id);
l_stmt := 'select input_name from msc_plan_sched_v where plan_id= ' ||p_plan_id ;
l_stmt := ' SELECT spd.from_date FROM ' || g_schema || '.supply_plan sp, '
|| g_schema || '.supply_plan_dates spd '
|| ' WHERE sp.plan_id = ' || p_plan_id
|| ' AND spd.supply_plan_id = sp.supply_plan_id ';
select sr_instance_id, compile_designator, plan_type, curr_start_date, cutoff_date
from msc_plans
where plan_id = p_plan_id;
l_stmt := ' SELECT spd.from_date FROM ' || g_schema || '.supply_plan sp, '
|| g_schema || '.supply_plan_dates spd '
|| ' WHERE sp.plan_id = ' || p_plan_id
|| ' AND spd.supply_plan_id = sp.supply_plan_id ';
select sr_instance_id
from msc_plans
where plan_id = p_plan_id;
/* Inserting an entry into Integ_Status table
that loading of plan is running. */
l_sql := 'Insert into integ_status(user_name, process, stage, status, info, status_date) values (''DMTRA_TEMPLATE'',
''LOAD_PLAN_DATA'',
''LOAD_PLAN_DATA'',
''RUNNING'', '
||''''|| ' ' ||''','
||''''||sysdate ||''')' ;
l_sql := 'select plan_id from supply_plan
where supply_plan_id = ' ||p_member_id;
/* Delete all data (if any) from the staging table */
x_small_sql := 'TRUNCATE TABLE ' || g_schema || '.BIIO_ITEM_COST';
/* Delete all data (if any) from the ERR staging table */
x_small_sql := 'TRUNCATE TABLE ' || g_schema || '.BIIO_ITEM_COST_ERR';
v_sql := 'select schema_id from wf_schemas where schema_name = ''Download Plan Scenario Data'' ';
v_sql := 'select nvl((select ''Running'' from wf_process_log ' ||
'where schema_id = :1 and step_id <> ''Wait'' ' ||
'and status not in(0,-1,-2) and rownum = 1), ''Not Running'') from dual';
SELECT owner
FROM dba_objects
WHERE owner = owner
AND object_type = 'TABLE'
AND object_name = 'MDP_MATRIX'
ORDER BY created desc;
msd_dem_common_utilities.log_message ('An Error occured in API call MSD_DEM_COLLECT_RETURN_HISTORY.COLLECT_RETURN_HISTORY_DATA,while inserting Total Backlog Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message ('Exception while inserting Total Backlog Data in the table BIIIO_SCI_BACKLOG - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message ('Warning Text for Total Backlog Insertion is - ' || l_errbuff1);
l_sql_stmnt := ' update '||x_dest_table||' bsb '
||' set level3 = ( select mtp.partner_name '
||' from msc_trading_partners mtp, '
||' msc_tp_id_lid mtil '
||' where mtil.sr_tp_id = bsb.level3_sr_pk '
||' and mtil.sr_instance_id = (select instance_id '
||' from msc_apps_instances mai '
||' where mai.instance_code = substr(bsb.level2,1,instr(bsb.level2,'':'')-1) '
||' ) '
||' and mtil.partner_type = 2 '
||' and mtil.tp_id = mtp.partner_id '
||' )';
l_sql_stmnt := ' UPDATE ' || x_dest_table
|| ' SET level3 = ''' || msd_dem_sr_util.get_null_code || ''' '
|| ' WHERE level3 IS NULL ';
/* update dmtra_template.BIIO_SCI_BACKLOG bsb
set level3 = ( select mtp.partner_name
from msc_trading_partners mtp,
msc_tp_id_lid mtil
where mtil.sr_tp_id = bsb.level3_sr_pk
and mtil.sr_instance_id = (select instance_id
from msc_apps_instances mai
where mai.instance_code = substr(bsb.level2,1,instr(bsb.level2,':')-1)
)
and mtil.partner_type = 2
and mtil.tp_id = mtp.partner_id
); */
msd_dem_common_utilities.log_message ('An Error occured in API call MSD_DEM_COLLECT_RETURN_HISTORY.COLLECT_RETURN_HISTORY_DATA,while inserting On Hand Inventory Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message ('Exception while inserting ON-Hand Inventory Data in the table BIIIO_SCI - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message ('Warning Text for On-Hand Inventory Insertion is - ' || l_errbuff1);