[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* data from the source instance and inserts into the
* sales staging table.
*/
PROCEDURE COLLECT_SERIES_DATA (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_series_id IN NUMBER,
p_dest_table IN VARCHAR2,
p_sr_instance_id IN NUMBER,
p_collection_method IN NUMBER,
p_from_date IN DATE,
p_to_date IN DATE,
p_sim_plan_name IN VARCHAR2 DEFAULT '')
IS
/*** CURSORS ***/
CURSOR c_get_series_info
IS
SELECT
series_name, identifier, stg_series_col_name, custom_view_name
FROM msd_dem_series
WHERE series_id = p_series_id AND series_type = 1;
/* Insert history data into staging table */
EXECUTE IMMEDIATE x_large_sql;
/* This procedure will set purge option for selected series and date range in the purge data profiles */
PROCEDURE SET_PURGE_PROFILES(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_usage_data IN NUMBER,
p_install_base IN NUMBER,
p_shipment_data IN NUMBER,
p_from_date IN DATE,
p_to_date IN DATE,
p_ff_load IN NUMBER
)
IS
x_profile_id number;
x_sql := 'select datet from '|| g_schema ||'.inputs where datet >= :1 and rownum = 1 order by datet asc';
x_sql := 'select datet from '|| g_schema ||'.inputs where datet <= :1 and rownum = 1 order by datet desc';
msd_dem_common_utilities.log_message ('For the selected series, the old data will be purged from ''' || x_from_date ||''' to '''||x_to_date ||'''');
msd_dem_common_utilities.log_debug ('For the selected series, the old data will be purged from ''' || x_from_date ||''' to '''||x_to_date ||'''');
x_sql := 'select query_name, table_name from '|| g_schema || '.transfer_query where id = ' || x_profile_id;
x_sql := 'insert into '|| g_schema ||'.'||x_table_name||'(sdate, level1)'||
' select '''||x_from_date||''', teo.organization from '|| g_schema||'.t_ep_organization teo '||
' where teo.organization in ' ||
' (SELECT distinct organization_code FROM
MSD_DEM_APP_INSTANCE_ORGS)';
x_sql := 'select query_name, table_name from '|| g_schema || '.transfer_query where id = ' || x_profile_id;
x_sql := 'insert into '|| g_schema ||'.'||x_table_name||'(sdate, level1)'||
' select '''||x_from_date||''', tesbm.t_ep_spf_base_model_code from '|| g_schema||'.t_ep_spf_base_model tesbm '||
' where tesbm.t_ep_spf_base_model_code in ' ||
' (SELECT distinct ebs_base_model_code FROM MSD_SPF_MODEL_ORGS)';
x_sql := 'select filter_id from ' || g_schema || '.transfer_query_filters ' ||
' where id = ' || x_profile_id || ' and level_id = ' || x_lvl_org_id || '';
x_sql := 'delete from ' || g_schema || '.transfer_query_filter_m where filter_id = ' || x_org_filter_id;
x_sql := 'insert into '||g_schema|| '.transfer_query_filter_m (filter_id, member_id) ' ||
' select ' ||x_org_filter_id|| ', teo.t_ep_organization_ep_id from ' ||g_schema||'.t_ep_organization teo ' ||
' where teo.organization in ';
x_sql := x_sql || ' (select distinct dm_org_code from MSD_SPF_ST_SPARE_USAGE)';
x_sql := x_sql || ' (select distinct organization_code from MSD_DEM_APP_INSTANCE_ORGS)';
var_sql := 'SELECT phase_code, status_code FROM fnd_concurrent_requests' || p_db_link || ' WHERE request_id = :req_id';
g_req_table.DELETE;
g_req_table.DELETE;
x_sql := 'INSERT /*+ APPEND */ INTO ' || C_SPARE_USAGE_HISTORY_TABLE || ' NOLOGGING '
|| ' (USAGE_HIST_TYPE, EBS_BASE_MODEL_SR_PK, EBS_BASE_MODEL_CODE, DM_ITEM_SR_PK, DM_ITEM_CODE, '
|| ' DM_ORG_SR_PK, DM_ORG_CODE, USAGE_DATE, QUANTITY, '
|| ' LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY ) '
|| ' SELECT ''SHIPMENTS'', -23453, tsst.ebs_base_model_code, -23453, tsst.dm_item_code, -23453, tsst.dm_org_code, '
|| ' tsst.sales_date, tsst.ebs_ship_hist_ship_qty_sd, sysdate, -1, sysdate, -1 '
|| ' FROM ' || g_schema || '.t_src_sales_tmpl tsst ';
g_req_table.DELETE;
g_req_table.DELETE;
x_sql := 'select to_number(replace(version,''.'','''')) from ' || x_dem_schema || '.version_details' ;
g_req_table.DELETE;
g_req_table.DELETE;
var_sql := 'select min(sdate), max(sdate) from '||g_schema||'.biio_spf_data';
var_req_table.DELETE;
var_sql := 'INSERT /*+ APPEND */ INTO ' || g_schema || '.BIIO_SPF_SPARES_HIST_DATA NOLOGGING '
|| ' (SDATE, LEVEL1, LEVEL2, SPF_ACTUAL_QUANTITY_DEP) '
|| ' SELECT mdd.datet, tsst.dm_item_code, tsst.dm_org_code, sum(tsst.ebs_ship_hist_ship_qty_sd) '
|| ' FROM ' || g_schema || '.t_src_sales_tmpl tsst, '
|| ' msd_dem_dates mdd '
|| ' WHERE tsst.sales_date BETWEEN mdd.start_date AND mdd.end_date '
|| ' GROUP BY tsst.dm_item_code, tsst.dm_org_code, mdd.datet';
var_req_table.DELETE;
/* 3 - Atleast One series should be selected for collection */
IF (p_shipment_data = G_NO AND p_usage_data = G_NO AND p_install_base = G_NO AND p_sales_forecast = G_NO)
THEN
retcode := -1;
errbuf := 'No series selected for collection. Please select atleast one series for collection.';
errbuf := 'For Collection Method - Complete, the date range filters should not be specified. If you want to run collection for a specific date range please select Net-change mode';
var_sql := 'SELECT user_name, password FROM ' || g_schema || '.user_id WHERE user_id = ' || to_char(var_user_id);
var_sql := 'SELECT utl_http.request(''' || var_url || '/WorkflowServer?action=run_proc&user=' || var_user_name || '&password=' || var_user_password
|| '&schema=' || replace(var_wf_schema_name, ' ', '%20') ||'&sync=no'') FROM dual';
msd_dem_common_utilities.log_debug('Deleteing data from table ' || var_table_name);
EXECUTE IMMEDIATE 'DELETE FROM ' || var_table_name;
msd_dem_common_utilities.log_debug('Deleteing data from table ' || var_table_name);
EXECUTE IMMEDIATE 'DELETE FROM ' || var_table_name;
msd_dem_common_utilities.log_debug('Deleteing data from table ' || var_table_name);
EXECUTE IMMEDIATE 'DELETE FROM ' || var_table_name;
msd_dem_common_utilities.log_debug('Deleteing data from table ' || var_table_name);
EXECUTE IMMEDIATE 'DELETE FROM ' || var_table_name;
msd_dem_common_utilities.log_debug('Deleteing data from table ' || var_table_name);
EXECUTE IMMEDIATE 'DELETE FROM ' || var_table_name;
SELECT
series_name,
series_type,
identifier,
custom_view_name,
ps_view_name
FROM msd_dem_series
WHERE series_id = p_series_id;
SELECT start_date
INTO var_from_date_dt
FROM msd_dem_dates
WHERE fnd_date.canonical_to_date(p_from_date) BETWEEN start_date AND end_date;
SELECT end_date
INTO var_to_date_dt
FROM msd_dem_dates
WHERE fnd_date.canonical_to_date(p_to_date) BETWEEN start_date AND end_date;
SELECT 1
INTO var_is_present
FROM msd_dem_entity_queries
WHERE entity_name = p_entity_name;
x_sql := 'select to_number(parameter_value) from msd_dem_setup_parameters ' ||
' where parameter_name like ''MSD_SPF_MASTER_ORG''';
x_sql := 'delete from MSD_SPF_ITEM_SUPERSESSION';
x_sql := 'select mi.item_name ' ||
' from msc_item_substitutes mis, msc_items mi ' ||
' where mis.plan_id = -1 and mis.sr_instance_id = ' || p_sr_instance_id ||
' and mis.relationship_type = 8 ' ||
' and exists (select 1 from msc_item_categories mic where mic.organization_id = ' || x_spf_master_org ||
' and mic.sr_instance_id = ' || p_sr_instance_id || ' and mic.inventory_item_id = mis.higher_item_id and mic.category_set_id = ' || x_category_set_id || ' ) ' ||
' and exists (select 1 from msc_item_categories mic where mic.organization_id = ' || x_spf_master_org ||
' and mic.sr_instance_id = ' || p_sr_instance_id || ' and mic.inventory_item_id = mis.lower_item_id and mic.category_set_id = ' || x_category_set_id || ' ) ' ||
' and mi.inventory_item_id = mis.lower_item_id group by mi.item_name having count(*) > 1';
x_sql := 'INSERT INTO MSD_SPF_ITEM_SUPERSESSION (sequence_id, chain_id, inventory_item_id, superseeded_item_id, effective_date, disable_date, latest_rev) ' ||
' (select ' || x_run_seq || ', rownum, higher_item_id, lower_item_id, effective_date, disable_date, higher_item_id ' ||
' from msc_item_substitutes mis '||
' where sr_instance_id = ' || p_sr_instance_id ||
' and plan_id = -1 and relationship_type = 8 ' ||
' and not exists (select 1 from msc_item_substitutes misx where misx.sr_instance_id = mis.sr_instance_id and misx.lower_item_id = mis.higher_item_id and misx.plan_id = -1 and misx.relationship_type = 8)' ||
' and exists (select 1 from msc_item_categories mic where mic.organization_id = ' || x_spf_master_org || ' and mic.sr_instance_id = ' || p_sr_instance_id || -- Bug#12675064
' and mic.inventory_item_id = mis.higher_item_id and mic.category_set_id = ' || x_category_set_id || ' ))' ; -- Bug#12675064
x_sql := 'INSERT INTO MSD_SPF_ITEM_SUPERSESSION (sequence_id, chain_id, inventory_item_id, superseeded_item_id, effective_date, disable_date, latest_rev) ' ||
' (select ' || x_run_seq || ', sc.chain_id, mis.higher_item_id, mis.lower_item_id, mis.effective_date, ' ||
' nvl(mis.disable_date, case when mis.effective_date < sc.effective_date then (sc.effective_date - 1) else mis.effective_date end), sc.latest_rev ' ||
' from msd_spf_item_supersession sc, msc_item_substitutes mis '||
' where sc.sequence_id = '|| (x_run_seq - 1) ||
' and mis.sr_instance_id = ' || p_sr_instance_id ||
' and mis.plan_id = -1 and mis.relationship_type = 8 ' ||
' and sc.superseeded_item_id = mis.higher_item_id ' ||
' and exists (select 1 from msc_item_categories mic where mic.organization_id = ' || x_spf_master_org || ' and mic.sr_instance_id = ' || p_sr_instance_id || -- Bug#12675064
' and mic.inventory_item_id = mis.higher_item_id and mic.category_set_id = ' || x_category_set_id || ' ))'; -- Bug#12675064
x_sql := 'INSERT INTO MSD_SPF_ITEM_SUPERSESSION (chain_id, sequence_id, inventory_item_id, disable_date, latest_rev) ' ||
' (select sc.chain_id, sc.sequence_id + 1, sc.superseeded_item_id, sc.effective_date - 1, sc.latest_rev ' ||
' from msd_spf_item_supersession sc, '||
' (select chain_id, max(sequence_id) max_seq from MSD_SPF_ITEM_SUPERSESSION group by chain_id) scg' ||
' where sc.chain_id = scg.chain_id' ||
' and sc.sequence_id = scg.max_seq' ||
' and exists (select 1 from msc_item_categories mic where mic.organization_id = ' || x_spf_master_org || ' and mic.sr_instance_id = ' || p_sr_instance_id || -- Bug#12675064
' and mic.inventory_item_id = sc.superseeded_item_id and mic.category_set_id = ' || x_category_set_id || ' ))'; -- Bug#12675064
/* Update sr_item_pk values */
msd_dem_common_utilities.log_debug('Update sr_item_pk values');
x_sql := 'UPDATE MSD_SPF_ITEM_SUPERSESSION mdsis '||
' set mdsis.sr_inventory_item_id = (' ||
' select sr_inventory_item_id from msc_item_id_lid ' ||
' where inventory_item_id = mdsis.inventory_item_id ' ||
' and sr_instance_id = ' || p_sr_instance_id || ' ) ';
/* Update active in future flag for all eligible records - use this flag for all spare revs which are to go into demantra */
/* - flag is 'Yes' for spares at end of chain or having effective date range in future */
/* - also set this for spares already in demantra in case the latest revision has changed and needs to be updated in demantra also */
x_sql := 'UPDATE MSD_SPF_ITEM_SUPERSESSION msis'||
' set msis.active_in_future = 1 ' ||
-- effectivity end date is in future or is not specified for last spare revision
' where (msis.disable_date is not null and msis.disable_date > sysdate) '||
' or (msis.disable_date is null and msis.sequence_id = 1) '||
' or exists (select 1 ' ||
' from ' || g_schema || '.t_ep_spf tes, ' || g_schema || '.t_ep_spf_latest_rev teslr, msc_items mi ' ||
' where tes.t_ep_spf_code like ''' || p_sr_instance_id || '::%-'' || msis.sr_inventory_item_id ' ||
' and teslr.t_ep_spf_latest_rev_id = tes.t_ep_spf_latest_rev_id ' ||
' and mi.inventory_item_id = msis.latest_rev ' ||
' and mi.item_name <> teslr.t_ep_spf_latest_rev_code ) ';
/* check if Sales Forecast download is selected */
if( p_load_sales_fcst = G_NO ) then
msd_dem_common_utilities.log_message('Load Sales Forecast not selected. Exiting.');
msd_dem_common_utilities.log_debug('Load Sales Forecast not selected. Exiting.');
x_sql := 'select user_name, password from ' || g_schema ||'.user_id where user_id = ' || x_user_id;
x_sql := 'select utl_http.request(''' || x_dem_url || '/WorkflowServer?action=run_proc&user='||x_user_name||'&password='||x_password||'&schema='|| replace(x_schema_name, ' ', '%20') ||'&sync=yes'') from dual';
x_sql := 'INSERT INTO ' || x_sales_fcst_stg ||
'(LEVEL1, SPF_CONSENSUS_FORE_IN, SDATE)' ||
'(SELECT LEVEL1, FCST_CONSENSUS, SDATE' ||
' FROM '|| g_schema || '.BIEO_DM_SPF_FCST_V)';
msd_dem_common_utilities.log_debug('Inserted ' || SQL%ROWCOUNT || ' rows into staging table.');
/* check if Sales Forecast download is selected */
if( p_spare_usg_hist = G_NO ) then
msd_dem_common_utilities.log_message('Load Spare Usage History not selected. Exiting.');
msd_dem_common_utilities.log_debug('Load Spare Usage History not selected. Exiting.');
/* msd_dem_common_utilities.log_debug ('Begin delete from staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
x_sql := 'DELETE FROM ' || x_dest_table;
msd_dem_common_utilities.log_debug ('End delete from sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
x_sql := 'DELETE FROM ' || x_dest_table;
msd_dem_common_utilities.log_debug ('End delete from sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));*/
x_sql := 'select to_number(parameter_value) from msd_dem_setup_parameters ' ||
' where parameter_name like ''MSD_SPF_MASTER_ORG''';
x_sql := 'select trunc(max(max_date), ''dd'') from ( '||
' select max(sdate) max_date from ' || nvl(p_custom_ibuc_hist_view, 'MSD_SPF_INSTALL_BASE_UC_V') ||
' where 1 = ' || p_install_base ||
case when (p_install_base = G_YES and x_to_date is not null)
then ' and sdate <= to_date(''' || to_char(x_to_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'')'
else '' end ||
' union ' ||
' select max(usage_date) max_date from MSD_SPF_ST_SPARE_USAGE '||
' union ' ||
' select to_date(' || g_schema || '.get_max_date, param.pval) max_date from ' ||
g_schema || '.db_params param where param.pname = ''nls_date_format'' )';
x_sql := 'select pval from ' || g_schema || '.sys_params where lower(pname) = ''spf_history_periods''';
x_sql := 'select init_params_table_name from ' || g_schema || '.engine_profiles where application_id = ''' || p_eng_profile_app_id || '''';
x_sql := 'select value_float from ' || g_schema || '.' || x_eng_params_table || ' where lower(pname) = ''lead''';
x_sql := 'select value_float from ' || g_schema || '.init_params_0 where lower(pname) = ''lead''';
x_sql := 'INSERT into MSD_SPF_MFG_BOM_TEMP ' ||
' (EBS_BASE_MODEL_SR_PK, DM_ITEM_SR_PK, EFFECTIVE_DATE, DISABLE_DATE, RETAIN_RECORD, SEQUENCE_ID) '||
' (select ebs_base_model_sr_pk, ebs_base_model_sr_pk, :1, :2, 1, ' || x_run_seq ||
' from (select distinct ebs_base_model_sr_pk from msd_spf_model_orgs) )';
msd_dem_common_utilities.log_debug(SQL%ROWCOUNT || ' records inserted (FG-FG-dates)');
x_sql := 'INSERT into MSD_SPF_MFG_BOM_TEMP ' ||
' (EBS_BASE_MODEL_SR_PK, DM_ITEM_SR_PK, EFFECTIVE_DATE, DISABLE_DATE, RETAIN_RECORD, SEQUENCE_ID) ' ||
' (select tbom.ebs_base_model_sr_pk base, msis.sr_inventory_item_id spare, mbc.effectivity_date, ' ||
' mbc.disable_date, 0 retain_record, ' || x_run_seq || ' sequence_id ' ||
' from msd_spf_mfg_bom_temp tbom, msc_system_items msib, msc_boms mb, msc_bom_components mbc, msc_system_items msis ' ||
' where tbom.sequence_id = ' || (x_run_seq - 1) ||
' and msib.plan_id = -1 and msib.sr_instance_id = ' || p_sr_instance_id ||
' and msib.organization_id = ' || x_spf_master_org || ' and msib.sr_inventory_item_id = tbom.dm_item_sr_pk ' ||
' and mb.plan_id = -1 and mb.organization_id = ' || x_spf_master_org || ' and mb.sr_instance_id = ' || p_sr_instance_id ||
' and mb.assembly_item_id = msib.inventory_item_id and mbc.plan_id = -1 and mbc.sr_instance_id = mb.sr_instance_id ' ||
' and mbc.bill_sequence_id = mb.bill_sequence_id and msis.plan_id = -1 and msis.sr_instance_id = mbc.sr_instance_id ' ||
' and msis.organization_id = mbc.organization_id and mbc.inventory_item_id = msis.inventory_item_id) ';
x_sql := 'DELETE from MSD_SPF_MFG_BOM_TEMP tbom where retain_record <> 1 ' ||
' and not exists (select 1 from msc_system_items msi, msc_item_categories mic ' ||
' where msi.plan_id = -1 and ' || ' msi.sr_instance_id = '|| p_sr_instance_id ||
' and msi.organization_id = ' || x_spf_master_org || ' and msi.sr_inventory_item_id = dm_item_sr_pk ' ||
' and mic.organization_id = msi.organization_id and mic.sr_instance_id = msi.sr_instance_id and msi.inventory_item_id = mic.inventory_item_id ' ||
' and mic.category_set_id = ' || x_category_set_id || ')';
' select ' || p_sr_instance_id || ' sr_instance_id, ebs_base_model_sr_pk, dm_item_sr_pk, ' ||
' trunc(min(effective_date), ''dd'') effective_date, ' ||
' trunc(max(disable_date), ''dd'') disable_date, ' ||
' min(chain_id) chain_id, min(latest_rev) latest_rev '||
' from ( '||
' select tbom.ebs_base_model_sr_pk, tbom.dm_item_sr_pk, ' ||
' least(nvl(msis.effective_date, tbom.effective_date), tbom.effective_date) effective_date, ' ||
' greatest(msis.disable_date, nvl(tbom.disable_date, msis.disable_date)) disable_date, ' ||
' msis.chain_id, msis.latest_rev '||
' from msd_spf_mfg_bom_temp tbom, msd_spf_item_supersession msis '||
' where tbom.dm_item_sr_pk = msis.sr_inventory_item_id(+) ' ||
' union all ' ||
' select tbom.ebs_base_model_sr_pk, mdsisx.sr_inventory_item_id dm_item_sr_pk, ' ||
' mdsisx.effective_date, mdsisx.disable_date, mdsisx.chain_id, mdsisx.latest_rev ' ||
' from MSD_SPF_MFG_BOM_TEMP tbom, msd_spf_item_supersession mdsis, msd_spf_item_supersession mdsisx '||
' where tbom.dm_item_sr_pk = mdsis.sr_inventory_item_id and mdsis.chain_id = mdsisx.chain_id '||
' and mdsisx.active_in_future = 1) ' ||
' group by ebs_base_model_sr_pk, dm_item_sr_pk ) b ' ||
' on ( ' ||
' a.sr_instance_id = b.sr_instance_id ' ||
' and a.ebs_base_model_sr_pk = b.ebs_base_model_sr_pk ' ||
' and a.dm_item_sr_pk = b.dm_item_sr_pk ) ' ||
' when not matched then ' ||
' insert (SR_INSTANCE_ID,EBS_BASE_MODEL_SR_PK,DM_ITEM_SR_PK,LATEST_REV,CHAIN_ID,EFFECTIVE_DATE,DISABLE_DATE) ' ||
' values (b.SR_INSTANCE_ID,b.EBS_BASE_MODEL_SR_PK,b.DM_ITEM_SR_PK,b.LATEST_REV,b.CHAIN_ID,b.EFFECTIVE_DATE,b.DISABLE_DATE) ' ||
' when matched then ' ||
' update set ' ||
' effective_date = least(a.effective_date, nvl(b.effective_date, a.effective_date)), ' ||
' disable_date = greatest(a.disable_date, nvl(b.disable_date, a.disable_date))' ;
' select msbc.sr_instance_id, msbc.ebs_base_model_sr_pk, msbc.dm_item_sr_pk, ' ||
' least(nvl(msbc.effective_date, tesd.from_date), tesd.from_date) from_date, ' ||
' case when (tesd.until_date > :1 and msbc.disable_date > :2) then msbc.disable_date ' ||
' else greatest(nvl(msbc.disable_date, tesd.until_date), tesd.until_date) ' ||
' end until_date ' || --bug#10124698, preserve only past dates in demantra (wrt max_sales_date)
' from msd_spf_model_bom_components msbc, '||g_schema|| '.t_ep_spf tes, ' ||g_schema|| '.t_ep_spf_child tesc, ' ||g_schema|| '.t_ep_spf_latest_rev teslr, ' ||g_schema|| '.t_ep_spf_dates tesd ' ||
' where msbc.ebs_base_model_sr_pk <> msbc.dm_item_sr_pk ' ||
' and tes.t_ep_spf_code = msbc.sr_instance_id || ''::'' || msbc.ebs_base_model_sr_pk || ''-'' || msbc.dm_item_sr_pk ' ||
' and (( msbc.latest_rev is null '||
' and tesc.t_ep_spf_child_id = tes.t_ep_spf_child_id ' ||
' and teslr.t_ep_spf_latest_rev_id = tes.t_ep_spf_latest_rev_id ' ||
' and tesc.t_ep_spf_child_code = teslr.t_ep_spf_latest_rev_code)' ||
' or (tesc.t_ep_spf_child_id = tes.t_ep_spf_child_id ' ||
' and teslr.t_ep_spf_latest_rev_id = tes.t_ep_spf_latest_rev_id ' ||
' and tesc.t_ep_spf_child_code <> teslr.t_ep_spf_latest_rev_code) ) ' ||
' and tes.t_ep_spf_id = tesd.t_ep_spf_id ' ||
' and tesd.from_date <= :3 ' || --bug#10124698, preserve only past dates in demantra (wrt max_sales_date)
' and (nvl(msbc.effective_date, tesd.from_date) > tesd.from_date or nvl(msbc.disable_date, tesd.until_date) < tesd.until_date ) ) b '||
' on ( ' ||
' a.sr_instance_id = b.sr_instance_id ' ||
' and a.ebs_base_model_sr_pk = b.ebs_base_model_sr_pk ' ||
' and a.dm_item_sr_pk = b.dm_item_sr_pk ) ' ||
' when matched then ' ||
' update set ' ||
' a.effective_date = b.from_date, ' ||
' a.disable_date = b.until_date ';
x_sql := 'UPDATE MSD_SPF_MODEL_BOM_COMPONENTS mbc ' ||
' SET ' ||
' mbc.disable_date = null ' ||
' where mbc.latest_rev is null ' ||
' or mbc.latest_rev = (select inventory_item_id from msc_item_id_lid miil ' ||
' where miil.sr_instance_id = mbc.sr_instance_id ' ||
' and miil.sr_inventory_item_id = mbc.dm_item_sr_pk) ';
x_sql := 'UPDATE MSD_SPF_MODEL_BOM_COMPONENTS ' ||
' SET ' ||
' effective_date = greatest(nvl(effective_date, :1), :2), ' ||
' disable_date = least(nvl(disable_date, :3), :4) ' ||
' where nvl(effective_date, :5) < :6 or nvl(disable_date, :7) > :8 ';
' select msbc.sr_instance_id, msbc.ebs_base_model_sr_pk, msbc.dm_item_sr_pk, ' ||
' least(nvl(msbc.effective_date, tesd.from_date), tesd.from_date) from_date, ' ||
' case when (tesc.t_ep_spf_child_code = teslr.t_ep_spf_latest_rev_code) then msbc.disable_date ' ||
' when (tesd.until_date > :1 and msbc.disable_date > :2) then msbc.disable_date ' ||
' else greatest(nvl(msbc.disable_date, tesd.until_date), tesd.until_date) ' ||
' end until_date ' || --bug#10124698, preserve only past dates in demantra (wrt max_sales_date)
' from msd_spf_model_bom_components msbc, '||g_schema|| '.t_ep_spf tes, ' ||g_schema|| '.t_ep_spf_child tesc, ' ||g_schema|| '.t_ep_spf_latest_rev teslr, ' ||g_schema|| '.t_ep_spf_dates tesd ' ||
' where tes.t_ep_spf_code = msbc.sr_instance_id || ''::'' || msbc.ebs_base_model_sr_pk || ''-'' || msbc.dm_item_sr_pk ' ||
' and tesc.t_ep_spf_child_id = tes.t_ep_spf_child_id ' ||
' and teslr.t_ep_spf_latest_rev_id = tes.t_ep_spf_latest_rev_id ' ||
' and tes.t_ep_spf_id = tesd.t_ep_spf_id ' ||
' and tesd.from_date <= :3 ' || --bug#10124698, preserve only past dates in demantra (wrt max_sales_date)
' and (nvl(msbc.effective_date, tesd.from_date) > tesd.from_date or nvl(msbc.disable_date, tesd.until_date) < tesd.until_date ) ) b '||
' on ( ' ||
' a.sr_instance_id = b.sr_instance_id ' ||
' and a.ebs_base_model_sr_pk = b.ebs_base_model_sr_pk ' ||
' and a.dm_item_sr_pk = b.dm_item_sr_pk ) ' ||
' when matched then ' ||
' update set ' ||
' a.effective_date = b.from_date, ' ||
' a.disable_date = b.until_date ';
x_sql := 'UPDATE ' ||
' msd_spf_model_bom_components '||
' set effective_date = ( '||
' select inp.start_date from msd_dem_dates inp '||
' where effective_date between inp.start_date and inp.end_date) ' ||
' where effective_date > :1 ';
x_sql := 'UPDATE ' ||
' msd_spf_model_bom_components '||
' set disable_date = nvl( '||
' (select greatest(inp.start_date-1,effective_date) from msd_dem_dates inp '||
' where disable_date between inp.start_date and inp.end_date-1), ' ||
' disable_date) '||
' where disable_date > :1 ';
' (select msmo.ebs_base_model_code ebs_base_model_code, msmo.ebs_base_model_sr_pk, '||
' teo.organization dm_org_code, mtp.sr_tp_id dm_org_sr_pk ' ||
' from (select distinct ebs_base_model_code, ebs_base_model_sr_pk from msd_spf_model_orgs) msmo, ' ||
' ' || g_schema || '.t_ep_spf_base_model tesbm, ' || g_schema || '.t_ep_spf tes, ' ||
' ' || g_schema || '.t_ep_spf_levels tesl, ' || g_schema || '.t_ep_spf_members tesm, ' ||
' ' || g_schema || '.t_ep_organization teo, ' || g_schema || '.t_ep_spf_demand_type tesdt, '||
' msc_trading_partners mtp ' ||
' where tesbm.t_ep_spf_base_model_code = msmo.ebs_base_model_code ' ||
' and tes.t_ep_spf_base_model_id = tesbm.t_ep_spf_base_model_id ' ||
' and tesdt.t_ep_spf_demand_type_code = ''SPF-BM'' ' ||
' and tes.t_ep_spf_demand_type_id = tesdt.t_ep_spf_demand_type_id ' ||
' and tesl.t_ep_spf_id = tes.t_ep_spf_id and tesl.level_id = ' || x_lvl_org_id ||
' and tesm.filter_id = tesl.filter_id ' ||
' and tesm.member_id = teo.t_ep_organization_ep_id ' ||
' and mtp.sr_instance_id = ' || p_sr_instance_id ||
' and mtp.organization_code = teo.organization ' ||
' and mtp.partner_type = 3 ) b ' || -- Bug#12675064
' on ' ||
' ( a.ebs_base_model_sr_pk = b.ebs_base_model_sr_pk ' ||
' and a.ebs_base_model_code = b.ebs_base_model_code ' ||
' and a.dm_org_sr_pk = b.dm_org_sr_pk and a.dm_org_code = b.dm_org_code) ' ||
' when not matched then ' ||
' insert (ebs_base_model_sr_pk, ebs_base_model_code, dm_org_sr_pk, dm_org_code) ' ||
' values (b.ebs_base_model_sr_pk, b.ebs_base_model_code, b.dm_org_sr_pk, b.dm_org_code)';
SELECT ebs_base_model_code, dm_item_code, dm_org_code, usage_date, error_text
FROM msd_spf_st_spare_usage
WHERE process_flag = -1
AND rownum < 10000;
SELECT to_number(parameter_value)
INTO var_spf_master_org
FROM msd_dem_setup_parameters
WHERE parameter_name = 'MSD_SPF_MASTER_ORG';
SELECT count(1) INTO var_total_rows FROM msd_spf_st_spare_usage;
msd_dem_common_utilities.log_message ('Update Source Organization Id...');
msd_dem_common_utilities.log_debug('Update DM_ORG_SR_PK Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
UPDATE msd_spf_st_spare_usage su
SET dm_org_sr_pk = NVL((SELECT sr_tp_id FROM msc_trading_partners mtp
WHERE mtp.sr_instance_id = p_sr_instance_id
AND mtp.partner_type = 3
AND mtp.organization_code = su.dm_org_code), su.dm_org_sr_pk);
msd_dem_common_utilities.log_debug('Update DM_ORG_SR_PK Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message ('Rows updated - ' || to_char(var_row_count));
msd_dem_common_utilities.log_message ('Update Source Inventory Item Id for Service Parts...');
msd_dem_common_utilities.log_debug('Update DM_ITEM_SR_PK Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
UPDATE msd_spf_st_spare_usage su
SET dm_item_sr_pk = NVL((SELECT sr_inventory_item_id FROM msc_system_items msi
WHERE msi.plan_id = -1
AND msi.sr_instance_id = p_sr_instance_id
AND msi.organization_id = su.dm_org_sr_pk
AND msi.item_name = su.dm_item_code), su.dm_item_sr_pk)
WHERE dm_org_sr_pk > 0;
msd_dem_common_utilities.log_debug('Update DM_ITEM_SR_PK Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message ('Rows updated - ' || to_char(var_row_count));
msd_dem_common_utilities.log_message ('Update Source Inventory Item Id for Finished Goods...');
msd_dem_common_utilities.log_debug('Update EBS_BASE_MODEL_SR_PK Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
UPDATE msd_spf_st_spare_usage su
SET ebs_base_model_sr_pk = NVL((SELECT sr_inventory_item_id FROM msc_system_items msi
WHERE msi.plan_id = -1
AND msi.sr_instance_id = p_sr_instance_id
AND msi.organization_id = var_spf_master_org -- Bug#12675064
AND msi.item_name = su.ebs_base_model_code), su.ebs_base_model_sr_pk)
WHERE dm_org_sr_pk > 0;
msd_dem_common_utilities.log_debug('Update EBS_BASE_MODEL_SR_PK Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message ('Rows updated - ' || to_char(var_row_count));
/* Update Process Flag and Error Text for bad rows */
msd_dem_common_utilities.log_message('------------------------------------Check Invalid Rows - Begin--------------------------------------');
msd_dem_common_utilities.log_debug('Update Bad Rows Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
UPDATE msd_spf_st_spare_usage
SET process_flag = -1,
error_text = 'Unable to find key(s) for - '
|| decode (ebs_base_model_sr_pk, -23453, ' EBS_BASE_MODEL_CODE = ' || ebs_base_model_code, '')
|| decode (dm_item_sr_pk, -23453, ' DM_ITEM_CODE = ' || dm_item_code, '')
|| decode (dm_org_sr_pk, -23453, ' DM_ORG_CODE = ' || dm_org_code, '')
WHERE ebs_base_model_sr_pk < 0
OR dm_item_sr_pk < 0
OR dm_org_sr_pk < 0;
msd_dem_common_utilities.log_debug('Update Bad Rows Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_debug('Update Bad Rows Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
UPDATE msd_spf_st_spare_usage su
SET process_flag = -1,
error_text = 'Spare not assigned to Spare Cat Set ' || x_category_set_id || ', for - '
|| ' DM_ITEM_CODE = ' || dm_item_code || ''
WHERE (process_flag is null or process_flag <> -1)
and not exists (select 1 from msc_system_items msi, msc_item_categories mic -- Bug#12675064
where msi.sr_inventory_item_id = su.dm_item_sr_pk and msi.plan_id = -1 and
msi.sr_instance_id = p_sr_instance_id and msi.organization_id = su.dm_org_sr_pk
and mic.organization_id = var_spf_master_org -- Bug#12675064
and mic.sr_instance_id = msi.sr_instance_id
and mic.inventory_item_id = msi.inventory_item_id -- Bug#12675064
and mic.category_set_id = x_category_set_id );
msd_dem_common_utilities.log_debug('Update Bad Rows Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message ('Following rows are invalid and will be deleted from the table - ');
msd_dem_common_utilities.log_debug('Delete Bad Rows Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
DELETE FROM msd_spf_st_spare_usage WHERE process_flag = -1;
msd_dem_common_utilities.log_debug('Delete Bad Rows Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message('Rows Deleted - ' || to_char(var_row_count));
msd_dem_common_utilities.log_debug('Update Rows Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
UPDATE msd_spf_st_spare_usage
SET process_flag = 0;
msd_dem_common_utilities.log_debug('Update Rows Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_debug('Aggregate and Insert Rows Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
INSERT /*+ APPEND */ INTO MSD_SPF_ST_SPARE_USAGE NOLOGGING
(USAGE_HIST_TYPE, EBS_BASE_MODEL_CODE, DM_ITEM_CODE, DM_ORG_CODE, USAGE_DATE, QUANTITY, -- Bug#12675064
EBS_BASE_MODEL_SR_PK, DM_ITEM_SR_PK, DM_ORG_SR_PK,
LAST_UPDATE_LOGIN, LAST_UPDATE_DATE, CREATION_DATE, LAST_UPDATED_BY, CREATED_BY)
SELECT su.usage_hist_type, su.ebs_base_model_code, su.dm_item_code, su.dm_org_code, mdd.datet, sum(su.quantity), -- Bug#12675064
su.ebs_base_model_sr_pk, su.dm_item_sr_pk, su.dm_org_sr_pk,
FND_GLOBAL.LOGIN_ID, systimestamp, systimestamp, FND_GLOBAL.USER_ID, FND_GLOBAL.USER_ID
FROM
msd_spf_st_spare_usage su,
msd_dem_dates mdd
WHERE
su.usage_date BETWEEN mdd.start_date AND mdd.end_date
GROUP BY su.usage_hist_type, su.ebs_base_model_sr_pk, su.dm_item_sr_pk, su.dm_org_sr_pk, mdd.datet, -- Bug#12675064
su.ebs_base_model_code, su.dm_item_code, su.dm_org_code;
msd_dem_common_utilities.log_debug('Aggregate and Insert End Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message('Rows (at base time level) Inserted - ' || to_char(var_row_count));
msd_dem_common_utilities.log_debug('Daily Rows Delete Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
DELETE FROM msd_spf_st_spare_usage
WHERE process_flag =0;
msd_dem_common_utilities.log_debug('Daily Rows Delete Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message('Rows (at day level) Deleted - ' || to_char(var_row_count));
x_sql := 'select datet from '|| g_schema ||'.inputs where datet >= :1 and rownum = 1 order by datet asc';
x_sql := 'select datet from '|| g_schema ||'.inputs where datet <= :1 and rownum = 1 order by datet desc';
msd_dem_common_utilities.log_message ('For the selected series, the old data will be purged from ''' || x_from_date ||''' to '''||x_to_date ||'''');
msd_dem_common_utilities.log_debug ('For the selected series, the old data will be purged from ''' || x_from_date ||''' to '''||x_to_date ||'''');
x_sql := 'select query_name, table_name from '|| g_schema || '.transfer_query where id = ' || x_profile_id;
x_sql := 'insert into '|| g_schema ||'.'||x_table_name||'(sdate, level1)'||
' select '''||x_from_date||''', teag.asset_group from '|| g_schema||'.t_ep_asset_group teag '||
' where teag.asset_group in ' ||
' (SELECT distinct asset_group FROM MSD_SPF_CMRO_BOM)';
x_sql := 'insert into '|| g_schema ||'.'||x_table_name||'(sdate, level1)'||
' select '''||x_from_date||''', teag.asset_group from '|| g_schema||'.t_ep_asset_group teag '||
' where teag.asset_group in ' ||
' (SELECT distinct level1 FROM ' || G_FLEET_DATA_DEST_TABLE || ')';
x_sql := 'select query_name, table_name from '|| g_schema || '.transfer_query where id = ' || x_profile_id;
x_sql := 'insert into '|| g_schema ||'.'||x_table_name||'(sdate, level1)'||
' select '''||x_from_date||''', tesbm.t_ep_spf_base_model_code from '|| g_schema||'.t_ep_spf_base_model tesbm '||
' where tesbm.t_ep_spf_base_model_code in ' ||
' (SELECT distinct base_model_code FROM MSD_SPF_CMRO_BOM)';
x_sql := 'select filter_id from ' || g_schema || '.transfer_query_filters ' ||
' where id = ' || x_profile_id || ' and level_id = ' || x_lvl_org_id || '';
x_sql := 'delete from ' || g_schema || '.transfer_query_filter_m where filter_id = ' || x_org_filter_id;
x_sql := 'insert into '||g_schema|| '.transfer_query_filter_m (filter_id, member_id) ' ||
' select ' ||x_org_filter_id|| ', teo.t_ep_organization_ep_id from ' ||g_schema||'.t_ep_organization teo ' ||
' where teo.organization in ' ||
' (select distinct organization_code from MSD_SPF_CMRO_BOM)';
x_sql := 'select query_name, table_name from '|| g_schema || '.transfer_query where id = ' || x_profile_id;
x_sql := 'insert into '|| g_schema ||'.'||x_table_name||'(sdate, level1)'||
' select '''||x_from_date||''', teag.asset_group from '|| g_schema||'.t_ep_asset_group teag '||
' where teag.asset_group in ' ||
' (SELECT distinct level1 FROM ' || G_FLEET_DATA_DEST_TABLE || ')';
g_req_table.DELETE;
g_req_table.DELETE;
x_sql := 'select parameter_value from msd_dem_setup_parameters where parameter_name = ''MSD_DEM_MASTER_ORG''';
x_sql := 'select parameter_value from msd_dem_setup_parameters where parameter_name = ''MSD_DEM_CATEGORY_SET_NAME''';
g_req_table.DELETE;
x_sql := 'select trunc(max(max_date), ''dd'') from ( '||
' select max(sdate) max_date from ' || g_schema || '.BIIO_SPF_CMRO_DATA '||
' union ' ||
' select to_date(' || g_schema || '.get_max_date, param.pval) max_date from ' ||
g_schema || '.db_params param where param.pname = ''nls_date_format'' )';
x_sql := 'select pval from ' || g_schema || '.sys_params where lower(pname) = ''spf_history_periods''';
x_sql := 'select value_float from ' || g_schema || '.init_params_0 where lower(pname) = ''lead''';
g_req_table.DELETE;
x_sql := 'UPDATE ' || g_schema || '.BIIO_SPF_POPULATION ' ||
' SET FROM_DATE = :1' ||
' WHERE FROM_DATE < :2';
g_req_table.DELETE;
g_req_table.DELETE;
x_sql := 'UPDATE ' || g_schema || '.sys_params ' ||
' set pval = ''' || p_sim_plan_name || '''' ||
' where pname = ''CMRO_Fleet_Simulation_Plan'' ';
g_req_table.DELETE;
IF (g_req_table.COUNT <> 0) THEN g_req_table.DELETE; END IF;
x_sql := 'select PARAMETER_VALUE from MSD_DEM_SETUP_PARAMETERS where PARAMETER_NAME = ''MSD_DEM_CATEGORY_SET_NAME''';
x_sql := 'select PARAMETER_VALUE from MSD_DEM_SETUP_PARAMETERS where PARAMETER_NAME = ''MSD_DEM_MASTER_ORG''';
IF (g_req_table.COUNT <> 0) THEN g_req_table.DELETE; END IF;
* 3. Update columns in t_src_sales_tmpl table
* 4. Submit "Collect Level Types" conc program for items
* 5. Submit "Collect Level Types" conc program for locations
* 6. Check requests status
* 7. Update columns in t_src_item_tmpl table
* 8. Submit "Launch Demantra Workflow" concurrent program
*
* ------------ PARAMETERS LIST ----------------
* p_hist_series : Name of the history series to be collected
* p_auto_run_download : Whether to run/launch the demantra workflow or not
* p_synchronous : Whether CP to in sync with demantra workflow or not
* p_check_interval : Check the status of the workflow for every (in seconds)
* p_time_out : Stop the status check of the workflow after (in minutes)
* --------------------------------------------
*/
PROCEDURE collect_nmp_hist_data (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_sr_instance_id IN NUMBER,
p_collection_group IN VARCHAR2,
p_collection_method IN NUMBER,
p_hidden_param1 IN VARCHAR2,
p_date_range_type IN NUMBER,
p_collection_window IN NUMBER,
p_from_date IN VARCHAR2,
p_to_date IN VARCHAR2,
p_hist_series IN VARCHAR2,
p_auto_run_download IN NUMBER ,
p_synchronous IN NUMBER DEFAULT G_NO,
p_check_interval IN NUMBER DEFAULT 60,
p_time_out IN NUMBER DEFAULT 1440)
IS
x_errbuf VARCHAR2(200) := NULL;
IF (g_req_table.COUNT <> 0) THEN g_req_table.DELETE; END IF;
/*------- START - updates to t_src_sales_tmpl table -------*/
l_sql := 'UPDATE ' || x_stg_table || ' SET ' || 'ACTUAL_QTY = ' || x_hist_col || ', '
|| 'DM_SITE_CODE = 0, EBS_SITE_SR_PK = -777, '
|| 'EBS_DEMAND_CLASS_CODE = 0, EBS_DEMAND_CLASS_SR_PK = -777, '
|| 'EBS_SALES_CHANNEL_CODE = 0, EBS_SALES_CHANNEL_SR_PK = -777 ';
msd_dem_common_utilities.log_debug ('Update stmt - ' || l_sql);
/*------- END - updates to t_src_sales_tmpl table -------*/
/*------- START - Submit "Collect Level Types" conc program to load T_SRC_ITEM_TMPL -------*/
msd_dem_common_utilities.log_debug (' ');
IF (g_req_table.COUNT <> 0) THEN g_req_table.DELETE; END IF;
/*------- START - updates to t_src_item_tmpl table -------*/
l_sql := 'UPDATE ' || x_item_stg_table || ' SET'
|| ' master_item_code = dm_item_code,'
|| ' master_item_desc = dm_item_desc,'
|| ' t_ep_I_ATT_10 = ''' ||CS_DEM_PRODUCT || '''' ; --bug#14694463 kkhatri
msd_dem_common_utilities.log_debug ('Update stmt 3 - ' || l_sql);
/*------- END - updates to t_src_item_tmpl table -------*/
/* START - Submit "Launch Demantra Workflow" concurrent program */
IF (p_auto_run_download = G_YES)
THEN
BEGIN
var_request_id := null;
g_req_table.DELETE;
x_sql := 'select parameter_value from msd_dem_setup_parameters where parameter_name like ''MSD_DEM_SCHEMA''';
IF (g_req_table.COUNT <> 0) THEN g_req_table.DELETE; END IF;
g_req_table.DELETE;
g_req_table.DELETE;
* This procedure deletes all data from SPF GL Tables. This should only be run by
* an admin user. The user must make sure that the Demantra AS is down before running
* the procedure.
* The procedure is used when the SPF related profile options have been changed which
* result in changes to the bom structure brought into Demantra.
*/
PROCEDURE PURGE_SPF_GL_DATA (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2)
IS
x_schema VARCHAR2(100) := NULL;
x_sql := 'DELETE FROM ' || x_schema || '.t_ep_spf WHERE t_ep_spf_id <> 0 ';
x_sql := 'DELETE FROM ' || x_schema || '.t_ep_spf_base_model WHERE t_ep_spf_base_model_id <> 0 ';
x_sql := 'DELETE FROM ' || x_schema || '.t_ep_spf_child WHERE t_ep_spf_child_id <> 0 ';
x_sql := 'DELETE FROM ' || x_schema || '.t_ep_spf_latest_rev WHERE t_ep_spf_latest_rev_id <> 0 ';
x_sql := 'DELETE FROM ' || x_schema || '.t_ep_spf_visit_type WHERE t_ep_spf_visit_type_id <> 0 ';
x_sql := 'DELETE FROM ' || x_schema || '.t_ep_spf_visit_stage_type WHERE t_ep_spf_visit_stage_type_id <> 0 ';
msd_dem_common_utilities.log_message('SPF GL Data deleted successfully');