[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
INTO x_is_present
FROM MSD_DEM_ENTITY_QUERIES
WHERE entity_name = p_entity_name;
EXECUTE IMMEDIATE 'DELETE FROM ' || x_dem_schema || '.BIIO_CTO_LEVEL';
EXECUTE IMMEDIATE 'DELETE FROM ' || x_dem_schema || '.BIIO_CTO_CHILD';
EXECUTE IMMEDIATE 'DELETE FROM ' || x_dem_schema || '.T_SRC_SALES_TMPL'
|| ' WHERE ebs_base_model_sr_pk IS NOT NULL '
|| ' AND to_char(ebs_base_model_sr_pk) <> component_code ';
|| to_char(SQL%ROWCOUNT) || ' rows deleted from T_SRC_SALES_TMPL');
msd_dem_common_utilities.log_message ('Update the site codes to descriptive format');
msd_dem_update_level_codes.update_code(errbuf ,
retcode,
p_sr_instance_id,
'SITE',
msd_dem_common_utilities.get_lookup_value ('MSD_DEM_DM_STAGING_TABLES', 'SALES_STAGING_TABLE'),
'DM_SITE_CODE',
'EBS_SITE_SR_PK');
msd_dem_update_level_codes.convert_site_code(
errbuf,
retcode,
p_sr_instance_id,
'SITE',
x_dem_schema || '.BIIO_CTO_DATA',
'LEVEL5',
1);
msd_dem_common_utilities.log_message ('Update the site codes to descriptive format');
msd_dem_update_level_codes.update_code(errbuf ,
retcode,
p_sr_instance_id,
'SITE',
msd_dem_common_utilities.get_lookup_value ('MSD_DEM_DM_STAGING_TABLES', 'SALES_STAGING_TABLE'),
'DM_SITE_CODE',
'EBS_SITE_SR_PK');
SELECT USER INTO x_curr_user FROM DUAL;
msd_dem_common_utilities.log_debug ('Inserting base models in T_SRC_SALES_TMPL to MSD_DEM_MODEL_BOM_COMPONENTS');
x_sql := 'INSERT /*+ APPEND NOLOGGING */ INTO MSD_DEM_MODEL_BOM_COMPONENTS '
|| ' ( ID, SR_INSTANCE_ID, SR_ORGANIZATION_ID, BASE_MODEL_ID, PARENT_ITEM_ID, COMPONENT_ITEM_ID, IS_BASE_MODEL, OPTIONAL_FLAG, '
|| ' CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, COMPONENT_CODE, PARENT_COMPONENT_CODE ';
x_sql := x_sql || ' ) SELECT '
|| to_char(x_iterator) || ' , '
|| to_char(p_sr_instance_id) || ' , '
|| ' iv.ebs_org_sr_pk, '
|| ' msi.inventory_item_id, '
|| ' msi.inventory_item_id, '
|| ' msi.inventory_item_id, '
|| ' 1, '
|| ' 2, '
|| 'sysdate, '
|| to_char(fnd_global.user_id) || ' , '
|| 'sysdate, '
|| to_char(fnd_global.user_id) || ' , '
|| to_char(fnd_global.login_id) || ' , '
|| ' msi.sr_inventory_item_id ' || ' , '
|| ' msi.inventory_item_id ';
x_sql := x_sql || ' FROM (SELECT tsst.ebs_org_sr_pk, tsst.ebs_item_sr_pk '
|| ' FROM ' || x_dem_schema || '.T_SRC_SALES_TMPL tsst '
|| ' WHERE tsst.ebs_base_model_sr_pk IS NOT NULL ';
|| ' SELECT '
|| to_char(x_iterator) || ' , '
|| to_char(p_sr_instance_id) || ' , '
|| ' msi.organization_id, '
|| ' msi.inventory_item_id, '
|| ' msi.inventory_item_id, '
|| ' msi.inventory_item_id, '
|| ' 1, '
|| ' 2, '
|| 'sysdate, '
|| to_char(fnd_global.user_id) || ' , '
|| 'sysdate, '
|| to_char(fnd_global.user_id) || ' , '
|| to_char(fnd_global.login_id) || ' , '
|| ' msi.sr_inventory_item_id ' || ' , '
|| ' msi.inventory_item_id ';
msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_total_num_rows));
x_sql := 'INSERT /*+ APPEND NOLOGGING */ INTO MSD_DEM_MODEL_BOM_COMPONENTS '
|| ' ( ID, SR_INSTANCE_ID, SR_ORGANIZATION_ID, BASE_MODEL_ID, TOP_ATO_MODEL_ID, PARENT_ITEM_ID, COMPONENT_ITEM_ID, IS_BASE_MODEL, '
|| ' EFFECTIVITY_DATE, DISABLE_DATE, PLANNING_FACTOR, PLNG_PCT_EXISTING, OPTIONAL_FLAG, '
|| ' CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, COMPONENT_CODE, PARENT_COMPONENT_CODE ';
x_sql := x_sql || ') SELECT :bvar1, SR_INSTANCE_ID, SR_ORGANIZATION_ID, BASE_MODEL_ID, min(TOP_ATO_MODEL_ID), PARENT_ITEM_ID, COMPONENT_ITEM_ID, '
|| 'min(IS_BASE_MODEL), min(EFFECTIVITY_DATE), max(DISABLE_DATE), min(PLANNING_FACTOR), min(PLNG_PCT_EXISTING), min(OPTIONAL_FLAG), '
|| 'sysdate CREATION_DATE, '
|| to_char(fnd_global.user_id) || ' CREATED_BY, '
|| 'sysdate LAST_UPDATE_DATE, '
|| to_char(fnd_global.user_id) || ' LAST_UPDATED_BY, '
|| to_char(fnd_global.login_id) || ' LAST_UPDATE_LOGIN, '
|| ' COMPONENT_CODE, PARENT_COMPONENT_CODE ';
x_sql := x_sql || ' FROM ( SELECT '
--|| ' :bvar1, '
|| ' mbc.sr_instance_id, '
|| ' dem.sr_organization_id, '
|| ' dem.base_model_id, '
-- Bug#13716090
|| ' decode (mbc.using_assembly_id, '
|| ' dem.component_item_id, nvl(dem.top_ato_model_id, '
|| ' case '
|| ' when pitem.mrp_planning_code <> 6 '
|| ' and pitem.bom_item_type = 1 '
|| ' then dem.component_item_id '
|| ' else to_number(null) '
|| ' end '
|| ' ) '
|| ' ) top_ato_model_id, '
|| ' mbc.using_assembly_id parent_item_id, '
|| ' mbc.inventory_item_id component_item_id, '
|| ' decode (citem.bom_item_type, '
|| ' 4, '
|| ' decode(citem.pick_components_flag, '
|| ' ''Y'', '
|| ' 2, '
|| ' 3), '
|| ' 2) is_base_model, '
|| ' mbc.effectivity_date, '
|| ' decode (dem.disable_date, null, mbc.disable_date, '
|| ' decode(mbc.disable_date, null, dem.disable_date, least(dem.disable_date, mbc.disable_date))) disable_date, '
|| ' mbc.planning_factor, '
|| ' decode(mbc.usage_quantity/decode(mbc.usage_quantity, '
|| ' null,1, '
|| ' 0,1, '
|| ' abs(mbc.usage_quantity)), '
|| ' 1, '
|| ' (mbc.usage_quantity * mbc.Component_Yield_Factor), '
|| ' (mbc.usage_quantity / mbc.Component_Yield_Factor)) '
|| ' * msd_dem_common_utilities.uom_conv(citem.sr_instance_id,citem.uom_code,citem.inventory_item_id) plng_pct_existing, '
|| ' mbc.optional_component optional_flag, '
|| ' dem.component_code || ''-'' || citem.sr_inventory_item_id component_code ';
SELECT validation_org_id INTO x_validation_org_id
FROM msc_apps_instances
WHERE instance_id = p_sr_instance_id;
x_sql1 := 'UPDATE MSD_DEM_MODEL_BOM_COMPONENTS a'
|| ' SET is_base_model = 3 '
|| ' WHERE EXISTS ( SELECT 1 FROM MSD_DEM_MODEL_BOM_COMPONENTS b '
|| ' WHERE b.id < :bvar1 '
|| ' AND b.sr_instance_id = a.sr_instance_id '
|| ' AND b.sr_organization_id = a.sr_organization_id '
|| ' AND b.base_model_id = a.base_model_id '
|| ' AND b.component_item_id = a.component_item_id ) '
|| ' AND a.id = :bvar2 ';
msd_dem_common_utilities.log_debug ('Build Query to delete duplicate Option Class-Option');
x_sql2 := 'DELETE FROM MSD_DEM_MODEL_BOM_COMPONENTS a'
|| ' WHERE EXISTS ( SELECT 1 FROM MSD_DEM_MODEL_BOM_COMPONENTS b '
|| ' WHERE b.id < :bvar1 '
|| ' AND b.sr_instance_id = a.sr_instance_id '
|| ' AND b.sr_organization_id = a.sr_organization_id '
|| ' AND b.base_model_id = a.base_model_id '
|| ' AND b.parent_item_id = a.parent_item_id '
|| ' AND b.component_item_id = a.component_item_id ) '
|| ' AND a.id = :bvar2 ';
msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_num_rows));
msd_dem_common_utilities.log_debug ('Number of rows deleted - ' || to_char(x_num_rows));
msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
/* Update the component_code column in t_src_sales_tmpl with component_code_s */
x_sql1 := 'UPDATE ' || x_dem_schema || '.T_SRC_SALES_TMPL tsst '
|| ' SET component_code = (SELECT component_code_s FROM msd_dem_model_bom_components mbc '
|| ' WHERE mbc.component_code = tsst.component_code AND rownum < 2 ) ';
/* Update the component_code column in t_src_sales_tmpl with component_code_s */
x_sql1 := 'UPDATE ' || x_dem_schema || '.T_SRC_SALES_TMPL tsst '
|| ' SET component_code = (SELECT component_code_s FROM msd_dem_model_bom_components mbc '
|| ' WHERE mbc.component_code_legacy = tsst.component_code_legacy AND rownum < 2 ) ';
/* Update the component_code column in t_src_sales_tmpl with component_code_s */
x_sql1 := 'UPDATE ' || x_dem_schema || '.T_SRC_SALES_TMPL tsst '
|| ' SET component_code = (SELECT component_code FROM msd_dem_model_bom_components mbc '
|| ' WHERE mbc.component_code_legacy = tsst.component_code_legacy AND rownum < 2 ) ';
msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
x_sql3 := 'Insert into ' || x_dem_schema || '.T_SRC_SALES_TMPL_ERR (select tsst.*, 6, sysdate, ''INVALID dependent demand record. NOT a VALID item under the BOM.'' from '
|| x_dem_schema || '.T_SRC_SALES_TMPL tsst where tsst.component_code is null and tsst.component_code_legacy is not null) ' ;
msd_dem_common_utilities.log_debug('Inserting INVALID dependent demand records into T_SRC_SALES_TMPL_ERR');
msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_num_rows));
/* Delete all INVALID dependent demand records from t_src_sales_tmpl */ -- Bug#10374484
x_sql3 := 'Delete from ' || x_dem_schema || '.T_SRC_SALES_TMPL where component_code is null and component_code_legacy is not null' ;
msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_num_rows));
INSERT /*+ APPEND NOLOGGING */ INTO MSD_DEM_CTO_BOM
(CTO_CODE, CTO_CHILD_CODE, CTO_PARENT_CODE, BASE_MODEL_CODE, PARENT_ITEM_CODE, OPTION_CODE, ORG_CODE,
EFFECTIVITY_DATE, DISABLE_DATE, PLNG_PCT_EXISTING, CTO_TYPE,
BASE_MODEL_ID, TOP_ATO_MODEL_ID, PARENT_ITEM_ID, OPTION_ID,
BASE_MODEL_SR_ID, PARENT_ITEM_SR_ID, OPTION_SR_ID,
COMPONENT_QTY, OPTIONAL_FLAG, ATO_FORECAST_CONTROL,
CTO_START_DATE, CTO_END_DATE)
SELECT
CTO_CODE, CTO_CHILD_CODE, CTO_PARENT_CODE, BASE_MODEL_CODE, PARENT_ITEM_CODE, OPTION_CODE, ORG_CODE,
EFFECTIVITY_DATE, DISABLE_DATE, PLNG_PCT_EXISTING, CTO_TYPE,
BASE_MODEL_ID, TOP_ATO_MODEL_ID, PARENT_ITEM_ID, OPTION_ID,
BASE_MODEL_SR_ID, PARENT_ITEM_SR_ID, OPTION_SR_ID,
COMPONENT_QTY, OPTIONAL_FLAG, ATO_FORECAST_CONTROL,
msd_dem_common_utilities.get_cto_effective_date(effectivity_date, 1) CTO_START_DATE,
msd_dem_common_utilities.get_cto_effective_date(disable_date, 2) + x_add_days CTO_END_DATE
FROM msd_dem_model_bom_components_v;
msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_num_rows));
' select mdcb.cto_code, tecd.from_date ' ||
' from MSD_DEM_CTO_BOM mdcb, '||x_dem_schema|| '.t_ep_cto tec, ' ||x_dem_schema|| '.t_ep_cto_dates tecd ' ||
' where tec.t_ep_cto_code = mdcb.cto_code ' ||
' and tecd.t_ep_cto_id = tec.t_ep_cto_id ' ||
' and tecd.from_date < mdcb.cto_start_date ) b '||
' on ( ' ||
' a.cto_code = b.cto_code ) ' ||
' when matched then ' ||
' update set ' ||
' a.cto_start_date = b.from_date';
* This procedure deletes all data from CTO 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 CTO related profile options have been changed which
* result in changes to the bom structure brought into Demantra.
*
* Parameters -
* p_complete_refresh - If 1, then all data from CTO GL tables are deleted
* - If 2, do nothing.
*/
PROCEDURE PURGE_CTO_GL_DATA (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_complete_refresh IN NUMBER )
IS
x_schema VARCHAR2(100) := NULL;
msd_dem_common_utilities.log_message ('Table to be deleted - T_EP_CTO');
msd_dem_common_utilities.log_message ('Table to be deleted - T_EP_CTO_BASE_MODEL');
msd_dem_common_utilities.log_message ('Table to be deleted - T_EP_CTO_CHILD');
x_sql := 'DELETE FROM ' || x_schema || '.' || 'T_EP_CTO' || ' WHERE t_ep_cto_id <> 0 ';
x_sql := 'DELETE FROM ' || x_schema || '.' || 'T_EP_CTO_BASE_MODEL' || ' WHERE t_ep_cto_base_model_id <> 0 ';
x_sql := 'DELETE FROM ' || x_schema || '.' || 'T_EP_CTO_CHILD' || ' WHERE t_ep_cto_child_id <> 0 ';
msd_dem_common_utilities.log_message ('CTO Data deleted successfully.');