[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select company_name into p_my_company
from msc_companies
where company_id = 1;
' INSERT INTO MSD_ST_LEVEL_VALUES '
||' (INSTANCE , '
||' LEVEL_ID, '
||' LEVEL_VALUE, '
||' SR_LEVEL_PK, '
||' LEVEL_VALUE_DESC, '
||' ATTRIBUTE1, '
||' ATTRIBUTE2, '
||' ATTRIBUTE3, '
||' ATTRIBUTE4, '
||' ATTRIBUTE5, '
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY, '
||' LAST_UPDATE_LOGIN, '
||' REQUEST_ID, '
||' PROGRAM_APPLICATION_ID, '
||' PROGRAM_ID, '
||' PROGRAM_UPDATE_DATE ) '
||' SELECT DISTINCT'
||' sr_instance_id,'
||' DECODE(partner_type,3,7,2,15),'
||' partner_name, '
||' sr_tp_id, '
||' DECODE(partner_type,3,substr(organization_code,instr(organization_code,'':'')+1,length(organization_code))||'''||':'||'''||substr(partner_name,instr(partner_name,'':'')+1,length(partner_name)),2,'''',''''), '
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' last_update_date,'
||' last_updated_by,'
||' creation_date,'
||' created_by, '
||' last_update_login, '
||' request_id , '
||' program_application_id, '
||' program_id , '
||' program_update_date '
||' FROM msc_st_trading_partners'
||' WHERE sr_instance_id = :p_instance_id'
||' AND process_flag = '||G_VALID
||' AND batch_id = :p_batch_id'
||' AND partner_type IN (2,3)';
' INSERT INTO MSD_ST_ORG_CALENDARS '
||' (INSTANCE , '
||' SR_ORG_PK, '
||' CALENDAR_TYPE, '
||' CALENDAR_CODE, '
||' CREATION_DATE, '
||' CREATED_BY, '
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' LAST_UPDATE_LOGIN, '
||' REQUEST_ID, '
||' PROGRAM_APPLICATION_ID, '
||' PROGRAM_ID, '
||' PROGRAM_UPDATE_DATE ) '
||' SELECT DISTINCT'
||' sr_instance_id,'
||' sr_tp_id,'
|| G_MFG_CAL||','
||' calendar_code, '
||' creation_date,'
||' created_by, '
||' last_update_date,'
||' last_updated_by,'
||' last_update_login, '
||' request_id , '
||' program_application_id, '
||' program_id , '
||' program_update_date '
||' FROM msc_st_trading_partners mstp'
||' WHERE EXISTS ( SELECT 1'
||' FROM msd_st_time mst '
||' WHERE mstp.calendar_code=mst.calendar_code '
||' AND mst.calendar_type='||G_MFG_CAL
||' AND mstp.sr_instance_code=mst.sr_instance_code'
||' UNION '
||' SELECT 1'
||' FROM msd_time mt'
||' WHERE mstp.calendar_code=mt.calendar_code'
||' AND mt.calendar_type='||G_MFG_CAL
||' AND mstp.sr_instance_id=mt.instance)'
||' AND mstp.sr_instance_id = :p_instance_id'
||' AND mstp.process_flag = '||G_VALID
||' AND mstp.batch_id = :p_batch_id'
||' AND mstp.partner_type=3 ';
select count(*) from MSD_LEVEL_VALUES
where LEVEL_ID = 2
and instance = p_instance
and SR_LEVEL_PK = to_char(v_null_pk); --Using global variable v_null_pk insteda of hardcoding -777 at multiple places.
select local_id from msd_local_id_setup
where char1 = p_instance_code
-- and char2 = 'All Products'
and level_id = 28;
select instance_code
from msc_apps_instances
where instance_id=p_instance_id;
lv_other_desc varchar2(240) := NULL; --Adding this to insert level value - 'Others'
lv_all_prd_desc varchar2(240) := NULL; --Adding this to insert level value - 'All Products'
' INSERT INTO MSD_ST_LEVEL_VALUES '
||' (INSTANCE , '
||' LEVEL_ID, '
||' LEVEL_VALUE, '
||' SR_LEVEL_PK, '
||' LEVEL_VALUE_DESC, '
||' ATTRIBUTE1, '
||' ATTRIBUTE2, '
||' ATTRIBUTE3, '
||' ATTRIBUTE4, '
||' ATTRIBUTE5, '
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY, '
||' LAST_UPDATE_LOGIN, '
||' REQUEST_ID, '
||' PROGRAM_APPLICATION_ID, '
||' PROGRAM_ID, '
||' PROGRAM_UPDATE_DATE ) '
||' SELECT '
||' sr_instance_id,'
||' DECODE(bom_item_type,5,3,1,1,1),'
||' item_name, '
||' sr_inventory_item_id, '
||' mssi.DESCRIPTION, '
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' mssi.last_update_date,'
||' mssi.last_updated_by,'
||' mssi.creation_date,'
||' mssi.created_by ,'
||' mssi.last_update_login, '
||' mssi.request_id , '
||' mssi.program_application_id, '
||' mssi.program_id , '
||' mssi.program_update_date '
||' FROM msc_st_system_items mssi'
||' WHERE mssi.rowid = ( select max(mssi1.rowid) '
||' from msc_st_system_items mssi1,msc_local_id_item lid '
||' WHERE mssi1.sr_instance_code = lid.char1'
||' AND NVL(mssi1.company_name, '||''''||-1||''''||') = '
||' NVL(lid.char2,'||''''||-1||''''||') '
||' AND mssi1.organization_code = lid.char3'
||' AND mssi1.item_name = lid.char4'
||' AND mssi1.process_flag = '||G_VALID
||' AND ((mssi1.mrp_planning_code <> 6) OR (mssi1.pick_components_flag =''Y'')) '
||' AND (('||v_plan_per_profile||'= 4) OR (mssi1.ato_forecast_control <> 3))'
||' AND mssi1.sr_instance_id = :p_instance_id'
||' AND lid.entity_name = ''SR_INVENTORY_ITEM_ID'''
||' AND mssi.item_name = mssi1.item_name '
||' group by mssi1.item_name )'
||' AND mssi.process_flag = '||G_VALID
||' AND mssi.batch_id = :p_batch_id'
||' AND mssi.sr_instance_id = :p_instance_id';
' INSERT INTO MSD_ST_LEVEL_ORG_ASSCNS '
||' (INSTANCE , '
||' LEVEL_ID, '
||' LEVEL_VALUE, '
||' SR_LEVEL_PK, '
||' ORG_LEVEL_ID, '
||' ORG_LEVEL_VALUE, '
||' ORG_SR_LEVEL_PK, '
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY, '
||' LAST_UPDATE_LOGIN, '
||' REQUEST_ID, '
||' PROGRAM_APPLICATION_ID, '
||' PROGRAM_ID, '
||' PROGRAM_UPDATE_DATE ) '
||' SELECT '
||' mssi.sr_instance_id,'
||' 1,'
||' mssi.item_name, '
||' mssi.sr_inventory_item_id, '
||' 7,'
||' NULL,'
||' mssi.organization_id,'
||' mssi.last_update_date,'
||' mssi.last_updated_by,'
||' mssi.creation_date,'
||' mssi.created_by ,'
||' mssi.last_update_login, '
||' mssi.request_id , '
||' mssi.program_application_id, '
||' mssi.program_id , '
||' mssi.program_update_date '
||' FROM msc_st_system_items mssi'
||' WHERE mssi.bom_item_type <> 5 ' -- excluding the Product Families
||' AND mssi.process_flag = '||G_VALID
||' AND mssi.batch_id = :p_batch_id'
||' AND mssi.sr_instance_id = :p_instance_id';
'INSERT INTO MSD_ST_ITEM_LIST_PRICE '
||' (INSTANCE ,'
||' ITEM ,'
||' LIST_PRICE ,'
||' AVG_DISCOUNT ,'
||' BASE_UOM ,'
||' SR_ITEM_PK ,'
||' ITEM_TYPE_ID ,'
||' FORECAST_TYPE_ID ,'
||' SR_INSTANCE_CODE, '
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY, '
||' LAST_UPDATE_LOGIN, '
||' REQUEST_ID, '
||' PROGRAM_APPLICATION_ID, '
||' PROGRAM_ID, '
||' PROGRAM_UPDATE_DATE ) '
||' Select '
||' SR_INSTANCE_ID ,'
||' ITEM_NAME ,'
||' LIST_PRICE ,'
||' AVERAGE_DISCOUNT ,'
||' UOM_CODE ,'
||' SR_INVENTORY_ITEM_ID ,'
||' BOM_ITEM_TYPE ,'
||' ATO_FORECAST_CONTROL ,'
||' SR_INSTANCE_CODE,'
||' mssi.last_update_date,'
||' mssi.last_updated_by,'
||' mssi.creation_date,'
||' mssi.created_by ,'
||' mssi.last_update_login, '
||' mssi.request_id , '
||' mssi.program_application_id, '
||' mssi.program_id , '
||' mssi.program_update_date '
||' FROM msc_st_system_items mssi'
||' WHERE mssi.rowid = ( select max(mssi1.rowid) '
||' FROM msc_st_system_items mssi1,msc_local_id_item lid '
||' WHERE mssi1.sr_instance_code = lid.char1'
||' AND NVL(mssi1.company_name, '||''''||-1||''''||') = '
||' NVL(lid.char2,'||''''||-1||''''||') '
||' AND mssi1.organization_code = lid.char3'
||' AND mssi1.item_name = lid.char4'
||' AND mssi1.process_flag = '||G_VALID
||' AND ((mssi1.mrp_planning_code <> 6) OR (mssi1.pick_components_flag =''Y'')) '
||' AND (('||v_plan_per_profile||'= 4) OR (mssi1.ato_forecast_control <> 3))'
||' AND mssi1.sr_instance_id = :p_instance_id'
||' AND lid.entity_name = ''SR_INVENTORY_ITEM_ID'''
||' AND mssi.item_name = mssi1.item_name '
||' group by mssi1.item_name )'
||' AND mssi.process_flag = '||G_VALID
||' AND mssi.batch_id = :p_batch_id'
||' AND mssi.sr_instance_id = :p_instance_id';
INSERT INTO MSD_ST_LEVEL_VALUES
(INSTANCE ,
LEVEL_ID,
LEVEL_VALUE,
SR_LEVEL_PK,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE )
VALUES
(p_instance_id,
2,
lv_other_desc, --Using the value fetched from function - msd_sr_util.get_null_desc
to_char(v_null_pk), --Using global variable v_null_pk insteda of hardcoding -777 at multiple places.
NULL,
NULL,
NULL,
NULL,
NULL,
sysdate,
-1,
sysdate,
-1,
-1,
-1,
-1,
-1,
sysdate);
lv_all_prd_pk := msd_sr_util.get_all_prd_pk; -- Inserting 'All Products' records with sr_level_pk using function msd_sr_util.get_all_prd_pk(lv_all_prd_pk= -1)
INSERT INTO msd_local_id_setup
(local_id,
instance_id,
level_id,
char1,
char2,
last_update_date,
last_updated_by,
creation_date,
created_by )
VALUES
(lv_all_prd_pk, --Using the value lv_all_prd_pk fetched above.
p_instance_id,
28,
lv_instance_code,
lv_all_prd_desc, --Using the value fetched from function - msd_sr_util.get_all_prd_desc
sysdate,
-1,
sysdate,
-1);
INSERT INTO MSD_ST_LEVEL_VALUES
(INSTANCE ,
LEVEL_ID,
LEVEL_VALUE,
SR_LEVEL_PK,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE )
VALUES
(p_instance_id,
28,
lv_all_prd_desc, --Using the value fetched from function - msd_sr_util.get_all_prd_desc
lv_all_prd_pk, --Inserting lv_all_prd_pk(-1) as sr_level_pk instead of '-7771'.
NULL,
NULL,
NULL,
NULL,
NULL,
sysdate,
-1,
sysdate,
-1,
-1,
-1,
-1,
-1,
sysdate);
INSERT INTO MSD_ST_LEVEL_ASSOCIATIONS
(INSTANCE ,
LEVEL_ID,
SR_LEVEL_PK,
PARENT_LEVEL_ID,
SR_PARENT_LEVEL_PK,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE )
VALUES
(p_instance_id,
2,
to_char(v_null_pk), --Using global variable v_null_pk insteda of hardcoding -777 at multiple places.
28,
lv_all_prd_pk, --Renamed 'lv_sr_level_pk' to 'lv_all_prd_pk'.
NULL,
NULL,
NULL,
NULL,
NULL,
sysdate,
-1,
sysdate,
-1,
-1,
-1,
-1,
-1,
sysdate);
' INSERT INTO MSD_ST_LEVEL_ASSOCIATIONS'
||' (INSTANCE , '
||' LEVEL_ID, '
||' SR_LEVEL_PK, '
||' PARENT_LEVEL_ID,'
||' SR_PARENT_LEVEL_PK,'
||' ATTRIBUTE1, '
||' ATTRIBUTE2, '
||' ATTRIBUTE3, '
||' ATTRIBUTE4, '
||' ATTRIBUTE5, '
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY, '
||' LAST_UPDATE_LOGIN, '
||' REQUEST_ID, '
||' PROGRAM_APPLICATION_ID, '
||' PROGRAM_ID, '
||' PROGRAM_UPDATE_DATE ) '
||' SELECT DISTINCT'
||' mssi.sr_instance_id,'
||' 1 ,'
||' mssi.sr_inventory_item_id, '
||' 2 ,'
|| to_char(v_null_pk)||',' --Using global variable v_null_pk insteda of hardcoding -777 at multiple places.
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' mssi.last_update_date,'
||' mssi.last_updated_by,'
||' mssi.creation_date,'
||' mssi.created_by, '
||' mssi.last_update_login, '
||' mssi.request_id , '
||' mssi.program_application_id, '
||' mssi.program_id , '
||' mssi.program_update_date '
||' FROM msc_st_system_items mssi'
||' WHERE mssi.rowid = ( select max(mssi1.rowid) '
||' from msc_st_system_items mssi1,msc_local_id_item lid '
||' WHERE mssi1.sr_instance_code = lid.char1'
||' AND NVL(mssi1.company_name, '||''''||-1||''''||') = '
||' NVL(lid.char2,'||''''||-1||''''||') '
||' AND mssi1.organization_code = lid.char3'
||' AND mssi1.item_name = lid.char4'
||' AND mssi1.process_flag = '||G_VALID
||' AND ((mssi1.mrp_planning_code <> 6) OR (mssi1.pick_components_flag =''Y'')) '
||' AND (('||v_plan_per_profile||'= 4) OR (mssi1.ato_forecast_control <> 3))'
||' AND mssi1.sr_instance_id = :p_instance_id'
||' AND lid.entity_name = ''SR_INVENTORY_ITEM_ID'''
||' AND mssi.item_name = mssi1.item_name '
||' group by mssi1.item_name )'
||' AND mssi.process_flag = '||G_VALID
||' AND mssi.batch_id = :p_batch_id'
||' AND mssi.sr_instance_id = :p_instance_id'
||' AND NOT EXISTS ( select 1'
||' from msc_st_system_items mssi2, msd_setup_parameters par'
||' where mssi.item_name =mssi2.item_name'
||' and mssi2.organization_code = par.PARAMETER_VALUE'
||' and ((mssi2.mrp_planning_code <> 6) OR (mssi2.pick_components_flag =''Y''))'
||' and (('||v_plan_per_profile||'= 4) OR (mssi2.ato_forecast_control <> 3))'
||' and mssi2.process_flag = '||G_VALID
||' and mssi2.sr_instance_id = :p_instance_id'
||' and par.parameter_name = ''MSD_MASTER_ORG_LEGACY'''
||' and par.instance_id = mssi2.sr_instance_id'
||' UNION'
||' select 1'
||' from msc_system_items msi, msd_setup_parameters par'
||' where mssi.item_name = msi.item_name'
||' and msi.organization_code = par.PARAMETER_VALUE'
||' and ((msi.mrp_planning_code <> 6) OR (msi.pick_components_flag =''Y''))'
||' and (('||v_plan_per_profile||'= 4) OR (msi.ato_forecast_control <> 3))'
||' and msi.sr_instance_id = :p_instance_id'
||' and msi.plan_id = -1'
||' and par.parameter_name = ''MSD_MASTER_ORG_LEGACY'''
||' and par.instance_id = msi.sr_instance_id ) ';
' INSERT INTO MSD_ST_LEVEL_VALUES '
||' (INSTANCE , '
||' LEVEL_ID, '
||' LEVEL_VALUE, '
||' SR_LEVEL_PK, '
||' LEVEL_VALUE_DESC, '
||' ATTRIBUTE1, '
||' ATTRIBUTE2, '
||' ATTRIBUTE3, '
||' ATTRIBUTE4, '
||' ATTRIBUTE5, '
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY, '
||' LAST_UPDATE_LOGIN, '
||' REQUEST_ID, '
||' PROGRAM_APPLICATION_ID, '
||' PROGRAM_ID, '
||' PROGRAM_UPDATE_DATE ) '
||' SELECT DISTINCT'
||' mic.sr_instance_id,'
||' 2 ,'
||' mic.category_name, '
||' mic.sr_category_id, '
||' mic.DESCRIPTION, '
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' mic.last_update_date,'
||' mic.last_updated_by,'
||' mic.creation_date,'
||' mic.created_by, '
||' mic.last_update_login, '
||' mic.request_id , '
||' mic.program_application_id, '
||' mic.program_id , '
||' mic.program_update_date '
||' FROM msc_st_item_categories mic,msc_st_system_items mssi'
||' WHERE mic.sr_instance_id = :p_instance_id'
||' AND mic.process_flag = '||G_VALID
||' AND mic.batch_id = :p_batch_id'
||' AND mic.organization_code = ( SELECT '
||' parameter_value from msd_setup_parameters'
||' WHERE parameter_name = ''MSD_MASTER_ORG_LEGACY'''
||' AND instance_id = mic.sr_instance_id)'
||' AND mic.category_set_name = ( SELECT '
||' parameter_value from msd_setup_parameters'
||' WHERE parameter_name = ''MSD_CATEGORY_SET_NAME_LEGACY'' '
||' AND instance_id = mic.sr_instance_id)'
||' AND (('||v_plan_per_profile||'= 4) OR (mssi.ato_forecast_control <> 3))'
||' AND mssi.mrp_planning_code <> 6'
||' AND mic.sr_instance_id =mssi.sr_instance_id'
||' AND mic.organization_id= mssi.organization_id'
||' AND mic.item_name = mssi.item_name'
||' UNION '
||' SELECT DISTINCT'
||' mic.sr_instance_id,'
||' 2 ,'
||' mic.category_name, '
||' mic.sr_category_id, '
||' mic.DESCRIPTION, '
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' mic.last_update_date,'
||' mic.last_updated_by,'
||' mic.creation_date,'
||' mic.created_by, '
||' mic.last_update_login, '
||' mic.request_id , '
||' mic.program_application_id, '
||' mic.program_id , '
||' mic.program_update_date '
||' FROM msc_st_item_categories mic,msc_system_items msi'
||' WHERE mic.sr_instance_id = :p_instance_id'
||' AND mic.process_flag = '||G_VALID
||' AND mic.batch_id = :p_batch_id'
||' AND mic.organization_code = ( SELECT '
||' parameter_value from msd_setup_parameters'
||' WHERE parameter_name = ''MSD_MASTER_ORG_LEGACY'''
||' AND instance_id = mic.sr_instance_id)'
||' AND mic.category_set_name = ( SELECT '
||' parameter_value from msd_setup_parameters'
||' WHERE parameter_name = ''MSD_CATEGORY_SET_NAME_LEGACY'' '
||' AND instance_id = mic.sr_instance_id)'
||' AND (('||v_plan_per_profile||'= 4) OR (msi.ato_forecast_control <> 3))'
||' AND msi.mrp_planning_code <> 6'
||' AND mic.sr_instance_id =msi.sr_instance_id'
||' AND mic.organization_id= msi.organization_id'
||' AND mic.item_name = msi.item_name'
||' AND msi.plan_id = -1';
' INSERT INTO MSD_ST_LEVEL_ASSOCIATIONS'
||' (INSTANCE , '
||' LEVEL_ID, '
||' SR_LEVEL_PK, '
||' PARENT_LEVEL_ID,'
||' SR_PARENT_LEVEL_PK,'
||' ATTRIBUTE1, '
||' ATTRIBUTE2, '
||' ATTRIBUTE3, '
||' ATTRIBUTE4, '
||' ATTRIBUTE5, '
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY, '
||' LAST_UPDATE_LOGIN, '
||' REQUEST_ID, '
||' PROGRAM_APPLICATION_ID, '
||' PROGRAM_ID, '
||' PROGRAM_UPDATE_DATE ) '
||' SELECT DISTINCT'
||' mic.sr_instance_id,'
||' 1 ,'
||' mic.inventory_item_id, '
||' 2 ,'
||' mic.sr_category_id,'
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' mic.last_update_date,'
||' mic.last_updated_by,'
||' mic.creation_date,'
||' mic.created_by, '
||' mic.last_update_login, '
||' mic.request_id , '
||' mic.program_application_id, '
||' mic.program_id , '
||' mic.program_update_date '
||' FROM msc_st_item_categories mic,msc_st_system_items mssi'
||' WHERE mic.sr_instance_id = :p_instance_id'
||' AND mic.process_flag = '||G_VALID
||' AND mic.batch_id = :p_batch_id'
||' AND mic.organization_code = ( SELECT '
||' parameter_value from msd_setup_parameters'
||' WHERE parameter_name = ''MSD_MASTER_ORG_LEGACY'''
||' AND instance_id = mic.sr_instance_id)'
||' AND mic.category_set_name = ( SELECT '
||' parameter_value from msd_setup_parameters'
||' WHERE parameter_name = ''MSD_CATEGORY_SET_NAME_LEGACY'' '
||' AND instance_id = mic.sr_instance_id)'
||' AND (('||v_plan_per_profile||'= 4) OR (mssi.ato_forecast_control <> 3))'
||' AND mssi.mrp_planning_code <> 6'
||' AND mic.sr_instance_id =mssi.sr_instance_id'
||' AND mic.organization_id= mssi.organization_id'
||' AND mic.item_name = mssi.item_name'
||' UNION '
||' SELECT DISTINCT'
||' mic.sr_instance_id,'
||' 1 ,'
||' mic.inventory_item_id, '
||' 2 ,'
||' mic.sr_category_id,'
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' mic.last_update_date,'
||' mic.last_updated_by,'
||' mic.creation_date,'
||' mic.created_by, '
||' mic.last_update_login, '
||' mic.request_id , '
||' mic.program_application_id, '
||' mic.program_id , '
||' mic.program_update_date '
||' FROM msc_st_item_categories mic,msc_system_items msi'
||' WHERE mic.sr_instance_id = :p_instance_id'
||' AND mic.process_flag = '||G_VALID
||' AND mic.batch_id = :p_batch_id'
||' AND mic.organization_code = ( SELECT '
||' parameter_value from msd_setup_parameters'
||' WHERE parameter_name = ''MSD_MASTER_ORG_LEGACY'''
||' AND instance_id = mic.sr_instance_id)'
||' AND mic.category_set_name = ( SELECT '
||' parameter_value from msd_setup_parameters'
||' WHERE parameter_name = ''MSD_CATEGORY_SET_NAME_LEGACY'' '
||' AND instance_id = mic.sr_instance_id)'
||' AND (('||v_plan_per_profile||'= 4) OR (msi.ato_forecast_control <> 3))'
||' AND msi.mrp_planning_code <> 6'
||' AND mic.sr_instance_id =msi.sr_instance_id'
||' AND mic.organization_id= msi.organization_id'
||' AND mic.item_name = msi.item_name'
||' AND msi.plan_id = -1';
cursor c1 is select count(*) from MSD_HIERARCHY_LEVELS
where LEVEL_ID = 11
AND PARENT_LEVEL_ID = 15 ;
' INSERT INTO MSD_ST_LEVEL_VALUES '
||' (INSTANCE , '
||' LEVEL_ID, '
||' LEVEL_VALUE, '
||' SR_LEVEL_PK, '
||' ATTRIBUTE1, '
||' ATTRIBUTE2, '
||' ATTRIBUTE3, '
||' ATTRIBUTE4, '
||' ATTRIBUTE5, '
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY, '
||' LAST_UPDATE_LOGIN, '
||' REQUEST_ID, '
||' PROGRAM_APPLICATION_ID, '
||' PROGRAM_ID, '
||' PROGRAM_UPDATE_DATE ) '
||' SELECT DISTINCT'
||' sr_instance_id,'
||' 11 ,'
||' tp_site_code, '
||' sr_tp_site_id, '
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' last_update_date,'
||' last_updated_by,'
||' creation_date,'
||' created_by , '
||' last_update_login, '
||' request_id , '
||' program_application_id, '
||' program_id , '
||' program_update_date '
||' FROM msc_st_trading_partner_sites'
||' WHERE partner_type = 2' -- customer site
||' AND process_flag = '||G_VALID
||' AND sr_instance_id = :p_instance_id'
||' AND batch_id = :p_batch_id';
' INSERT INTO MSD_ST_LEVEL_ASSOCIATIONS'
||' (INSTANCE , '
||' LEVEL_ID, '
||' SR_LEVEL_PK, '
||' PARENT_LEVEL_ID,'
||' SR_PARENT_LEVEL_PK,'
||' ATTRIBUTE1, '
||' ATTRIBUTE2, '
||' ATTRIBUTE3, '
||' ATTRIBUTE4, '
||' ATTRIBUTE5, '
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY, '
||' LAST_UPDATE_LOGIN, '
||' REQUEST_ID, '
||' PROGRAM_APPLICATION_ID, '
||' PROGRAM_ID, '
||' PROGRAM_UPDATE_DATE ) '
||' SELECT DISTINCT'
||' sr_instance_id,'
||' 11 ,'
||' sr_tp_site_id,'
||' 15 ,'
||' sr_tp_id, '
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' last_update_date,'
||' last_updated_by,'
||' creation_date,'
||' created_by, '
||' last_update_login, '
||' request_id , '
||' program_application_id, '
||' program_id , '
||' program_update_date '
||' FROM msc_st_trading_partner_sites tps'
||' WHERE sr_instance_id = :p_instance_id'
||' AND partner_type = 2'
||' AND process_flag = '||G_VALID
||' AND batch_id = :p_batch_id';
select local_id from msd_local_id_setup
where char1 = p_instance_code
-- and char2 = 'All Demand Classes'
and level_id = 40;
lv_all_dcs_desc varchar2(240) := NULL; --Adding this to insert level value - 'All Demand Classes'
' INSERT INTO MSD_ST_LEVEL_VALUES '
||' (INSTANCE , '
||' LEVEL_ID, '
||' LEVEL_VALUE, '
||' SR_LEVEL_PK, '
||' LEVEL_VALUE_DESC, '
||' ATTRIBUTE1, '
||' ATTRIBUTE2, '
||' ATTRIBUTE3, '
||' ATTRIBUTE4, '
||' ATTRIBUTE5, '
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY, '
||' LAST_UPDATE_LOGIN, '
||' REQUEST_ID, '
||' PROGRAM_APPLICATION_ID, '
||' PROGRAM_ID, '
||' PROGRAM_UPDATE_DATE ) '
||' SELECT '
||' sr_instance_id,'
||' 34 ,'
||' meaning, '
||' demand_class, '
||' DESCRIPTION, '
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' last_update_date,'
||' last_updated_by,'
||' creation_date,'
||' created_by, '
||' last_update_login, '
||' request_id , '
||' program_application_id, '
||' program_id , '
||' program_update_date '
||' FROM msc_st_demand_classes '
||' WHERE sr_instance_id = :p_instance_id'
||' AND process_flag = '||G_VALID
||' AND batch_id = :p_batch_id';
lv_all_dcs_pk := msd_sr_util.get_all_dcs_pk; -- Insert 'All Demand Classes' records with sr_level_pk as -6
INSERT INTO msd_local_id_setup -- insert into msd_local_id_setup
(local_id,
instance_id,
level_id,
char1,
char2,
last_update_date,
last_updated_by,
creation_date,
created_by )
VALUES
(lv_all_dcs_pk, --Renamed 'lv_sr_level_pk' to 'lv_all_dcs_pk'.
p_instance_id,
40,
p_instance_code,
lv_all_dcs_desc, --Using the value fetched from function - msd_sr_util.get_all_dcs_desc
sysdate,
-1,
sysdate,
-1);
INSERT INTO MSD_ST_LEVEL_VALUES --insert into msd_st_level_values
(INSTANCE ,
LEVEL_ID,
LEVEL_VALUE,
SR_LEVEL_PK,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE )
VALUES
(p_instance_id,
40,
lv_all_dcs_desc, --Using the value fetched from function - msd_sr_util.get_all_dcs_desc
lv_all_dcs_pk, --Renamed 'lv_sr_level_pk' to 'lv_all_dcs_pk'.
NULL,
NULL,
NULL,
NULL,
NULL,
sysdate,
-1,
sysdate,
-1,
-1,
-1,
-1,
-1,
sysdate);
' INSERT INTO MSD_ST_LEVEL_ASSOCIATIONS'
||' (INSTANCE , '
||' LEVEL_ID, '
||' SR_LEVEL_PK, '
||' PARENT_LEVEL_ID,'
||' SR_PARENT_LEVEL_PK,'
||' ATTRIBUTE1, '
||' ATTRIBUTE2, '
||' ATTRIBUTE3, '
||' ATTRIBUTE4, '
||' ATTRIBUTE5, '
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY, '
||' LAST_UPDATE_LOGIN, '
||' REQUEST_ID, '
||' PROGRAM_APPLICATION_ID, '
||' PROGRAM_ID, '
||' PROGRAM_UPDATE_DATE ) '
||' SELECT DISTINCT'
||' sr_instance_id,'
||' 34 ,'
||' demand_class, '
||' 40 ,'
||' :lv_all_dcs_pk, ' --Renamed 'lv_sr_level_pk' to 'lv_all_dcs_pk'.
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' last_update_date,'
||' last_updated_by,'
||' creation_date,'
||' created_by, '
||' last_update_login, '
||' request_id , '
||' program_application_id, '
||' program_id , '
||' program_update_date '
||' FROM msc_st_demand_classes '
||' WHERE sr_instance_id = :p_instance_id'
||' AND process_flag = '||G_VALID
||' AND batch_id = :p_batch_id';
SELECT rowid
FROM msd_st_level_values
WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
AND sr_instance_code = p_instance_code
AND batch_id = p_batch_id;
SELECT rowid
FROM msd_st_level_values
WHERE process_flag = G_IN_PROCESS
AND sr_instance_code = p_instance_code
AND batch_id = p_batch_id
AND NVL(sr_level_pk,NULL_CHAR) = NULL_CHAR ;
SELECT instance_type
FROM msc_apps_instances
WHERE instance_id=p_instance_id;
SELECT parameter_value
FROM msd_setup_parameters
WHERE parameter_name = 'MSD_MASTER_ORG'
AND instance_id = p_instance_id;
SELECT level_value
FROM msd_st_level_values
WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
AND sr_instance_code = p_instance_code
AND level_id = 1
AND batch_id = p_batch_id;
UPDATE msd_st_level_values
SET st_transaction_id = msd_st_level_values_s.NEXTVAL,
last_update_date = lv_current_date,
last_updated_by = lv_current_user,
creation_date = lv_current_date,
created_by = lv_current_user
WHERE rowid = lb_rowid(j);
'UPDATE msd_st_level_values'
||' SET process_flag = '||G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE level_id IN (1,2,3,7,11,15,34)'
||' AND process_flag = '||G_IN_PROCESS
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :p_instance_code';
'UPDATE msd_st_level_values'
||' SET process_flag = '||G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE NVL(level_value, '||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' AND batch_id = :lv_batch_id'
||' AND process_flag = '||G_IN_PROCESS
||' AND sr_instance_code = :p_instance_code';
INSERT INTO MSD_ST_ITEM_LIST_PRICE
(INSTANCE ,
ITEM ,
LIST_PRICE ,
AVG_DISCOUNT ,
BASE_UOM ,
SR_ITEM_PK ,
ITEM_TYPE_ID ,
FORECAST_TYPE_ID ,
SR_INSTANCE_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE )
SELECT
SR_INSTANCE_ID ,
ITEM_NAME ,
LIST_PRICE ,
AVERAGE_DISCOUNT ,
UOM_CODE ,
SR_INVENTORY_ITEM_ID ,
BOM_ITEM_TYPE ,
ATO_FORECAST_CONTROL ,
p_instance_code,
last_update_date,
last_updated_by,
creation_date,
created_by ,
last_update_login,
request_id ,
program_application_id,
program_id ,
program_update_date
FROM MSC_SYSTEM_ITEMS
WHERE item_name = lb_level_value(j)
AND sr_instance_id = p_instance_id
AND plan_id = -1
AND organization_id = (select mtp.sr_tp_id
from msc_trading_partners mtp,msd_setup_parameters msp
where msp.parameter_name ='MSD_MASTER_ORG_LEGACY'
and msp.instance_id = p_instance_id
and msp.parameter_value = substr(mtp.organization_code,instr(mtp.organization_code,':')+1,length(mtp.organization_code))
and nvl(mtp.company_id,-1) = -1
and mtp.sr_instance_id = msp.instance_id);
UPDATE msd_st_level_values
SET sr_level_pk = msd_common_utilities.get_sr_level_pk(p_instance_id,p_instance_code)
WHERE rowid = lb_rowid(j);
UPDATE msd_st_level_values
SET sr_level_pk = lb_sr_level_pk(j)
WHERE rowid = lb_rowid(j);
INSERT INTO msd_local_id_setup
(local_id,
instance_id,
level_id,
data_source_type,
char1,
char2,
last_update_date,
last_updated_by,
creation_date,
created_by )
SELECT
TO_NUMBER(sr_level_pk),
p_instance_id,
level_id,
data_source_type,
p_instance_code,
level_value,
last_update_date,
last_updated_by,
creation_date,
created_by
FROM msd_st_level_values
WHERE rowid = lb_rowid(j);
SELECT rowid
FROM msd_st_level_associations
WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
AND sr_instance_code = p_instance_code
AND batch_id = p_batch_id;
UPDATE msd_st_level_associations
SET st_transaction_id = msd_st_level_associations_s.NEXTVAL,
last_update_date = lv_current_date,
last_updated_by = lv_current_user,
creation_date = lv_current_date,
created_by = lv_current_user
WHERE rowid = lb_rowid(j);
'UPDATE msd_st_level_associations mla'
||' SET process_flag ='||G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE NOT EXISTS ( SELECT 1'
||' FROM msd_hierarchy_levels mhl'
||' WHERE mla.level_id = mhl.level_id'
||' AND mla.parent_level_id = mhl.parent_level_id)'
||' AND process_flag ='|| G_IN_PROCESS
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :p_instance_code';
lv_other_desc varchar2(240) := NULL; --Adding this to insert level value - 'Others'
lv_all_dcs_desc varchar2(240) := NULL; --Adding this to insert level value - 'All Demand Classes'
SELECT instance_type
FROM msc_apps_instances
WHERE instance_id=p_instance_id;
SELECT rowid
FROM msd_st_booking_data
WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
AND sr_instance_code = p_instance_code
AND batch_id = p_batch_id;
SELECT rowid
FROM msd_st_booking_data
WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
AND nvl(DEMAND_CLASS_LVL_VAL,'-1') = '-1'
AND sr_instance_code = p_instance_code
AND batch_id = p_batch_id;
select count(*) from MSD_LEVEL_VALUES
where LEVEL_ID = 34
and instance = p_instance
and SR_LEVEL_PK = to_char(v_null_pk); --Using global variable v_null_pk insteda of hardcoding -777 at multiple places.
select local_id from msd_local_id_setup
where char1 = p_instance_code
-- and char2 = 'All Demand Classes'
and level_id = 40;
UPDATE msd_st_booking_data
SET st_transaction_id = msd_st_booking_data_s.NEXTVAL,
last_update_date = lv_current_date,
last_updated_by = lv_current_user,
creation_date = lv_current_date,
created_by = lv_current_user
WHERE rowid = lb_rowid(j);
UPDATE msd_st_booking_data
SET DEMAND_CLASS_LVL_VAL = lv_other_desc --Using the value fetched from function - msd_sr_util.get_null_desc
WHERE rowid = lb_rowid1(j);
INSERT INTO MSD_ST_LEVEL_VALUES
(INSTANCE ,
LEVEL_ID,
LEVEL_VALUE,
SR_LEVEL_PK,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE )
VALUES
(p_instance_id,
34,
lv_other_desc, --Using the value fetched from function - msd_sr_util.get_null_desc
to_char(v_null_pk), --Using global variable v_null_pk insteda of hardcoding -777 at multiple places.
NULL,
NULL,
NULL,
NULL,
NULL,
sysdate,
-1,
sysdate,
-1,
-1,
-1,
-1,
-1,
sysdate);
INSERT INTO msd_local_id_setup -- insert into msd_local_id_setup
(local_id,
instance_id,
level_id,
char1,
char2,
last_update_date,
last_updated_by,
creation_date,
created_by )
VALUES
(lv_all_dcs_pk, --Renamed 'lv_sr_level_pk' to 'lv_all_dcs_pk'.
p_instance_id,
40,
p_instance_code,
lv_all_dcs_desc, --Using the value fetched from function - msd_sr_util.get_all_dcs_desc
sysdate,
-1,
sysdate,
-1);
INSERT INTO MSD_ST_LEVEL_VALUES --Inserting into MSD_ST_LEVEL_VALUES
(INSTANCE ,
LEVEL_ID,
LEVEL_VALUE,
SR_LEVEL_PK,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE )
VALUES
(p_instance_id,
40,
lv_all_dcs_desc, --Using the value fetched from function - msd_sr_util.get_all_dcs_desc
lv_all_dcs_pk, --Renamed 'lv_sr_level_pk' to 'lv_all_dcs_pk'.
NULL,
NULL,
NULL,
NULL,
NULL,
sysdate,
-1,
sysdate,
-1,
-1,
-1,
-1,
-1,
sysdate);
INSERT INTO MSD_ST_LEVEL_ASSOCIATIONS
(INSTANCE ,
LEVEL_ID,
SR_LEVEL_PK,
PARENT_LEVEL_ID,
SR_PARENT_LEVEL_PK,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE )
VALUES
(p_instance_id,
34,
to_char(v_null_pk), --Using global variable v_null_pk insteda of hardcoding -777 at multiple places.
40,
lv_all_dcs_pk, --Renamed 'lv_sr_level_pk' to 'lv_all_dcs_pk'.
NULL,
NULL,
NULL,
NULL,
NULL,
sysdate,
-1,
sysdate,
-1,
-1,
-1,
-1,
-1,
sysdate);
UPDATE msd_st_booking_data mbd
SET process_flag = G_ERROR_FLG,
error_text = lv_message_text
WHERE NOT EXISTS(SELECT 1
FROM msc_local_id_setup mls
WHERE mls.char1 = mbd.sr_instance_code
AND nvl(mls.char2,lv_my_company) = lv_my_company
AND mls.char3 = mbd.customer
AND mls.char4 = mbd.ship_to_loc
AND mls.number1 = 2 --customer
AND mls.entity_name = 'SR_TP_SITE_ID')
AND mbd.process_flag = G_IN_PROCESS
AND mbd.sr_instance_code = p_instance_code
AND mbd.batch_id = p_batch_id;
'UPDATE msd_st_booking_data '
||' SET ORIGINAL_ITEM =ITEM '
||' WHERE ORIGINAL_ITEM IS NULL '
||' AND process_flag ='|| G_IN_PROCESS
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :p_instance_code';
UPDATE msd_st_booking_data mbd
SET process_flag = G_ERROR_FLG,
error_text = lv_message_text
WHERE NOT EXISTS(SELECT 1
FROM msc_st_system_items msi
WHERE msi.sr_instance_id = p_instance_id
AND nvl(msi.company_name,lv_my_company) = lv_my_company
AND msi.organization_id = mbd.sr_inv_org_pk
AND msi.item_name = mbd.item
AND ((v_plan_per_profile = 4) OR (msi.ato_forecast_control <> 3)) --forecast control - none
AND ((msi.mrp_planning_code <> 6 ) OR (msi.pick_components_flag='Y' )) --Not planned item
UNION
SELECT 1
FROM msc_system_items mls
WHERE mls.sr_instance_id = p_instance_id
AND mls.organization_id = mbd.sr_inv_org_pk
AND mls.item_name = mbd.item
AND mls.plan_id = -1
AND ((v_plan_per_profile = 4) OR (mls.ato_forecast_control <> 3)) --forecast control - none
AND ((mls.mrp_planning_code <> 6 ) OR (mls.pick_components_flag='Y' ))) --Not planned item
AND mbd.process_flag = G_IN_PROCESS
AND mbd.sr_instance_code = p_instance_code
AND mbd.batch_id = p_batch_id;
UPDATE msd_st_booking_data mbd
SET process_flag = G_ERROR_FLG,
error_text = lv_message_text
WHERE NOT EXISTS(SELECT 1
FROM msd_st_level_associations msla
WHERE msla.level_id = 11
AND msla.parent_level_id = 15
AND mbd.sr_customer_pk = msla.sr_parent_level_pk
AND mbd.sr_ship_to_loc_pk = msla.sr_level_pk
AND msla.instance = p_instance_id
UNION ALL
SELECT 1
FROM msd_level_associations mla
WHERE mla.level_id = 11
AND mla.parent_level_id = 15
AND mbd.sr_customer_pk = mla.sr_parent_level_pk
AND mbd.sr_ship_to_loc_pk = mla.sr_level_pk
AND mla.instance = p_instance_id)
AND mbd.process_flag = G_IN_PROCESS
AND mbd.sr_instance_code = p_instance_code
AND mbd.batch_id = p_batch_id;
'UPDATE msd_st_booking_data '
||' SET process_flag ='||G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE NVL(booked_date,(sysdate-36500)) = (sysdate-36500)'
||' AND process_flag ='|| G_IN_PROCESS
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :p_instance_code';
lv_other_desc varchar2(240) := NULL; --Adding this to insert level value - 'Others'
lv_all_dcs_desc varchar2(240) := NULL; --Adding this to insert level value - 'All Demand Classes'
SELECT instance_type
FROM msc_apps_instances
WHERE instance_id=p_instance_id;
SELECT rowid
FROM msd_st_shipment_data
WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
AND sr_instance_code = p_instance_code
AND batch_id = p_batch_id;
SELECT rowid
FROM msd_st_shipment_data
WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
AND nvl(DEMAND_CLASS_LVL_VAL,'-1') = '-1'
AND sr_instance_code = p_instance_code
AND batch_id = p_batch_id;
select count(*) from MSD_LEVEL_VALUES
where LEVEL_ID = 34
and instance = p_instance
and SR_LEVEL_PK = to_char(v_null_pk); --Using global variable v_null_pk insteda of hardcoding -777 at multiple places.
select local_id from msd_local_id_setup
where char1 = p_instance_code
-- and char2 = 'All Demand Classes'
and level_id = 40;
UPDATE msd_st_shipment_data
SET st_transaction_id = msd_st_shipment_data_s.NEXTVAL,
last_update_date = lv_current_date,
last_updated_by = lv_current_user,
creation_date = lv_current_date,
created_by = lv_current_user
WHERE rowid = lb_rowid(j);
UPDATE msd_st_shipment_data
SET DEMAND_CLASS_LVL_VAL = lv_other_desc --Using the value fetched from function - msd_sr_util.get_null_desc
WHERE rowid = lb_rowid1(j);
INSERT INTO MSD_ST_LEVEL_VALUES
(INSTANCE ,
LEVEL_ID,
LEVEL_VALUE,
SR_LEVEL_PK,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE )
VALUES
(p_instance_id,
34,
lv_other_desc, --Using the value fetched from function - msd_sr_util.get_null_desc
to_char(v_null_pk), --Using global variable v_null_pk insteda of hardcoding -777 at multiple places.
NULL,
NULL,
NULL,
NULL,
NULL,
sysdate,
-1,
sysdate,
-1,
-1,
-1,
-1,
-1,
sysdate);
INSERT INTO msd_local_id_setup -- insert into msd_local_id_setup
(local_id,
instance_id,
level_id,
char1,
char2,
last_update_date,
last_updated_by,
creation_date,
created_by )
VALUES
(lv_all_dcs_pk, --Renamed 'lv_sr_level_pk' to 'lv_all_dcs_pk'.
p_instance_id,
40,
p_instance_code,
lv_all_dcs_desc, --Using the value fetched from function - msd_sr_util.get_all_dcs_desc
sysdate,
-1,
sysdate,
-1);
INSERT INTO MSD_ST_LEVEL_VALUES --Inserting into MSD_ST_LEVEL_VALUES
(INSTANCE ,
LEVEL_ID,
LEVEL_VALUE,
SR_LEVEL_PK,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE )
VALUES
(p_instance_id,
40,
lv_all_dcs_desc, --Using the value fetched from function - msd_sr_util.get_all_dcs_desc
lv_all_dcs_pk, --Renamed 'lv_sr_level_pk' to 'lv_all_dcs_pk'.
NULL,
NULL,
NULL,
NULL,
NULL,
sysdate,
-1,
sysdate,
-1,
-1,
-1,
-1,
-1,
sysdate);
INSERT INTO MSD_ST_LEVEL_ASSOCIATIONS
(INSTANCE ,
LEVEL_ID,
SR_LEVEL_PK,
PARENT_LEVEL_ID,
SR_PARENT_LEVEL_PK,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE )
VALUES
(p_instance_id,
34,
to_char(v_null_pk), --Using global variable v_null_pk insteda of hardcoding -777 at multiple places.
40,
lv_all_dcs_pk, --Renamed 'lv_sr_level_pk' to 'lv_all_dcs_pk'.
NULL,
NULL,
NULL,
NULL,
NULL,
sysdate,
-1,
sysdate,
-1,
-1,
-1,
-1,
-1,
sysdate);
UPDATE msd_st_shipment_data msd
SET process_flag = G_ERROR_FLG,
error_text = lv_message_text
WHERE NOT EXISTS(SELECT 1
FROM msc_local_id_setup mls
WHERE mls.char1 = msd.sr_instance_code
AND nvl(mls.char2,lv_my_company) = lv_my_company
AND mls.char3 = msd.customer
AND mls.char4 = msd.ship_to_loc
AND mls.number1 = 2 --customer
AND mls.entity_name = 'SR_TP_SITE_ID')
AND msd.process_flag = G_IN_PROCESS
AND msd.sr_instance_code = p_instance_code
AND msd.batch_id = p_batch_id;
'UPDATE MSD_ST_SHIPMENT_DATA '
||' SET ORIGINAL_ITEM =ITEM '
||' WHERE ORIGINAL_ITEM IS NULL '
||' AND process_flag ='|| G_IN_PROCESS
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :p_instance_code';
UPDATE msd_st_shipment_data msd
SET process_flag = G_ERROR_FLG,
error_text = lv_message_text
WHERE NOT EXISTS(SELECT 1
FROM msc_st_system_items msi
WHERE msi.sr_instance_id = p_instance_id
AND nvl(msi.company_name,lv_my_company) = lv_my_company
AND msi.organization_id = msd.sr_inv_org_pk
AND msi.item_name = msd.item
AND ((v_plan_per_profile = 4) OR (msi.ato_forecast_control <> 3)) --forecast control - none
AND ((msi.mrp_planning_code <> 6 ) OR (msi.pick_components_flag='Y' )) --Not planned item
UNION
SELECT 1
FROM msc_system_items mls
WHERE mls.sr_instance_id = p_instance_id
AND mls.organization_id = msd.sr_inv_org_pk
AND mls.item_name = msd.item
AND mls.plan_id = -1
AND ((v_plan_per_profile = 4) OR (mls.ato_forecast_control <> 3)) --forecast control - none
AND ((mls.mrp_planning_code <> 6 ) OR (mls.pick_components_flag='Y' )) ) --Not planned item
AND msd.process_flag = G_IN_PROCESS
AND msd.sr_instance_code = p_instance_code
AND msd.batch_id = p_batch_id;
UPDATE msd_st_shipment_data msd
SET process_flag = G_ERROR_FLG,
error_text = lv_message_text
WHERE NOT EXISTS(SELECT 1
FROM msd_st_level_associations msla
WHERE msla.level_id = 11
AND msla.parent_level_id = 15
AND msd.sr_customer_pk = msla.sr_parent_level_pk
AND msd.sr_ship_to_loc_pk = msla.sr_level_pk
AND msla.instance = p_instance_id
UNION ALL
SELECT 1
FROM msd_level_associations mla
WHERE mla.level_id = 11
AND mla.parent_level_id = 15
AND msd.sr_customer_pk = mla.sr_parent_level_pk
AND msd.sr_ship_to_loc_pk = mla.sr_level_pk
AND mla.instance = p_instance_id)
AND msd.process_flag = G_IN_PROCESS
AND msd.sr_instance_code = p_instance_code
AND msd.batch_id = p_batch_id;
'UPDATE msd_st_shipment_data '
||' SET process_flag ='||G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE NVL(shipped_date,(sysdate-36500)) = (sysdate-36500)'
||' AND process_flag ='|| G_IN_PROCESS
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :p_instance_code';
SELECT rowid
FROM msd_st_mfg_forecast
WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
AND sr_instance_code = p_instance_code
AND batch_id = p_batch_id;
SELECT instance_type
FROM msc_apps_instances
WHERE instance_id=p_instance_id;
lv_other_desc varchar2(240) := NULL; --Adding this to insert level value - 'Others' -Bug 3749959
lv_all_dcs_desc varchar2(240) := NULL; --Adding this to insert level value - 'All Demand Classes' -Bug 3749959
SELECT rowid
FROM msd_st_mfg_forecast
WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
AND nvl(DEMAND_CLASS_LVL_VAL,'-1') = '-1'
AND sr_instance_code = p_instance_code
AND batch_id = p_batch_id;
select count(*) from MSD_LEVEL_VALUES
where LEVEL_ID = 34
and instance = p_instance
and SR_LEVEL_PK = to_char(v_null_pk);
select local_id from msd_local_id_setup
where char1 = p_instance_code
-- and char2 = 'All Demand Classes'
and level_id = 40;
UPDATE msd_st_mfg_forecast
SET st_transaction_id = msd_st_mfg_forecast_s.NEXTVAL,
last_update_date = lv_current_date,
last_updated_by = lv_current_user,
creation_date = lv_current_date,
created_by = lv_current_user
WHERE rowid = lb_rowid(j);
FORALL j IN lb_rowid1.FIRST..lb_rowid1.LAST -- Update the demand class level value column with 'Other' as Demand Class Level Value.
UPDATE msd_st_mfg_forecast
SET DEMAND_CLASS_LVL_VAL = lv_other_desc --Using the value fetched from function - msd_sr_util.get_null_desc
WHERE rowid = lb_rowid1(j);
INSERT INTO MSD_ST_LEVEL_VALUES --Inserting the level value 'Others' at 'Demand Class' level in MSD_ST_LEVEL_VALUES.
(INSTANCE ,
LEVEL_ID,
LEVEL_VALUE,
SR_LEVEL_PK,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE )
VALUES
(p_instance_id,
34,
lv_other_desc, --Using the value fetched from function - msd_sr_util.get_null_desc
to_char(v_null_pk),
NULL,
NULL,
NULL,
NULL,
NULL,
sysdate,
-1,
sysdate,
-1,
-1,
-1,
-1,
-1,
sysdate);
INSERT INTO msd_local_id_setup --Inserting the level value 'All Demand Classes' in MSD_LOCAL_ID_SETUP.
(local_id,
instance_id,
level_id,
char1,
char2,
last_update_date,
last_updated_by,
creation_date,
created_by )
VALUES
(lv_all_dcs_pk,
p_instance_id,
40,
p_instance_code,
lv_all_dcs_desc, --Using the value fetched from function - msd_sr_util.get_all_dcs_desc
sysdate,
-1,
sysdate,
-1);
INSERT INTO MSD_ST_LEVEL_VALUES --Inserting the level value 'All Demand Classes' in MSD_ST_LEVEL_VALUES.
(INSTANCE ,
LEVEL_ID,
LEVEL_VALUE,
SR_LEVEL_PK,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE )
VALUES
(p_instance_id,
40,
lv_all_dcs_desc, --Using the value fetched from function - msd_sr_util.get_all_dcs_desc
lv_all_dcs_pk,
NULL,
NULL,
NULL,
NULL,
NULL,
sysdate,
-1,
sysdate,
-1,
-1,
-1,
-1,
-1,
sysdate);
INSERT INTO MSD_ST_LEVEL_ASSOCIATIONS
(INSTANCE ,
LEVEL_ID,
SR_LEVEL_PK,
PARENT_LEVEL_ID,
SR_PARENT_LEVEL_PK,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE )
VALUES
(p_instance_id,
34,
to_char(v_null_pk),
40,
lv_all_dcs_pk,
NULL,
NULL,
NULL,
NULL,
NULL,
sysdate,
-1,
sysdate,
-1,
-1,
-1,
-1,
-1,
sysdate);
'UPDATE msd_st_mfg_forecast'
||' SET process_flag = '||G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE NVL(forecast_designator, '||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' AND batch_id = :lv_batch_id'
||' AND process_flag = '||G_IN_PROCESS
||' AND sr_instance_code = :p_instance_code';
'UPDATE msd_st_mfg_forecast '
||' SET process_flag ='||G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE NVL(forecast_date,(sysdate-36500)) = (sysdate-36500)'
||' AND process_flag ='|| G_IN_PROCESS
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :p_instance_code';
'UPDATE msd_st_mfg_forecast'
||' SET process_flag = '||G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE NVL(original_quantity,'||NULL_VALUE||')= '||NULL_VALUE
||' AND process_flag = '||G_IN_PROCESS
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :p_instance_code';
' UPDATE msd_st_mfg_forecast'
||' SET current_quantity = original_quantity'
||' WHERE NVL(current_quantity,'||NULL_VALUE||')= '||NULL_VALUE
||' AND process_flag = '||G_IN_PROCESS
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :p_instance_code';
' UPDATE msd_st_mfg_forecast'
||' SET bucket_type = '||G_BUCKET_TYPE
||' WHERE NVL(bucket_type,'||NULL_VALUE||') NOT IN(1,2,3)'
||' AND process_flag = '||G_IN_PROCESS
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :p_instance_code';
UPDATE msd_st_mfg_forecast mmf
SET process_flag = G_ERROR_FLG,
error_text = lv_message_text
WHERE NOT EXISTS(SELECT 1
FROM msc_st_system_items msi
WHERE msi.sr_instance_id = p_instance_id
AND nvl(msi.company_name,lv_my_company) = lv_my_company
AND msi.organization_id = mmf.sr_inv_org_pk
AND msi.item_name = mmf.item
AND ((v_plan_per_profile = 4) OR (msi.ato_forecast_control <> 3)) --forecast control - none
AND ((msi.mrp_planning_code <> 6 ) OR (msi.pick_components_flag='Y' )) --Not planned item
UNION
SELECT 1
FROM msc_system_items mls
WHERE mls.sr_instance_id = p_instance_id
AND mls.organization_id = mmf.sr_inv_org_pk
AND mls.item_name = mmf.item
AND mls.plan_id = -1
AND ((v_plan_per_profile = 4) OR (mls.ato_forecast_control <> 3)) --forecast control - none
AND ((mls.mrp_planning_code <> 6 ) OR (mls.pick_components_flag='Y' )) ) --Not planned item
AND mmf.process_flag = G_IN_PROCESS
AND mmf.sr_instance_code = p_instance_code
AND mmf.batch_id = p_batch_id;
| DESCRIPTION : This function insert records for msd_st_time |
| for manufacturing calendar |
+==========================================================================*/
PROCEDURE LOAD_MFG_TIME (ERRBUF OUT NOCOPY VARCHAR,
RETCODE OUT NOCOPY NUMBER,
p_instance_id IN NUMBER,
p_calendar_code IN VARCHAR)
IS
lv_sql_stmt VARCHAR2(4000);
select max(day),min(day)
from msd_st_time
where calendar_code=p_calendar_code
and instance=p_instance_id;
' INSERT INTO MSD_ST_TIME'
||' (INSTANCE , '
||' CALENDAR_TYPE, '
||' CALENDAR_CODE, '
||' SEQ_NUM, '
||' YEAR, '
||' YEAR_DESCRIPTION, '
||' YEAR_START_DATE, '
||' YEAR_END_DATE, '
||' QUARTER, '
||' QUARTER_DESCRIPTION, '
||' QUARTER_START_DATE,'
||' QUARTER_END_DATE,'
||' MONTH,'
||' MONTH_DESCRIPTION,'
||' MONTH_START_DATE,'
||' MONTH_END_DATE,'
||' WEEK ,'
||' WEEK_DESCRIPTION ,'
||' WEEK_START_DATE,'
||' WEEK_END_DATE,'
||' DAY,'
||' DAY_DESCRIPTION,'
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY, '
||' LAST_UPDATE_LOGIN, '
||' REQUEST_ID, '
||' PROGRAM_APPLICATION_ID, '
||' PROGRAM_ID, '
||' PROGRAM_UPDATE_DATE ) '
||' SELECT'
||' mcd.sr_instance_id,'
|| G_MFG_CAL||','
||' mcd.calendar_code, '
||' mcd.seq_num, '
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' NULL,'
||' mps.period_name'||'||''-''||'||'to_char(mps.period_start_date,''YYYY''),'
||' mps.period_name'||'||''-''||'||'to_char(mps.period_start_date,''YYYY''),'
||' mps.period_start_date,'
||' DECODE(SIGN(mps.next_date-mps.period_start_date),1,(mps.next_date-1),'||'NULL'||'),'
||' ''Week''||'||'mcws.seq_num ,'
||' ''Week''||'||'mcws.seq_num ,'
||' mcws.week_start_date ,'
||' DECODE(SIGN(mcws.next_date-mcws.week_start_date),1,(mcws.next_date-1),'||'NULL'||'),'
||' mcd.calendar_date,'
||' mcd.calendar_date,'
||' mcd.last_update_date,'
||' mcd.last_updated_by,'
||' mcd.creation_date,'
||' mcd.created_by, '
||' mcd.last_update_login, '
||' mcd.request_id , '
||' mcd.program_application_id, '
||' mcd.program_id , '
||' mcd.program_update_date '
||' FROM msc_period_start_dates mps,'
||' msc_calendar_dates mcd,'
||' msc_cal_week_start_dates mcws'
||' WHERE mcd.sr_instance_id = :p_instance_id'
||' AND mcd.calendar_code = :p_calendar_code'
||' AND mcd.seq_num IS NOT NULL'
||' AND mcd.exception_set_id = -1'
||' AND mcws.calendar_code = mcd.calendar_code'
||' AND mcws.sr_instance_id = mcd.sr_instance_id'
||' AND mcws.exception_set_id = mcd.exception_set_id'
||' AND mcd.calendar_date BETWEEN mcws.week_start_date '
||' AND DECODE(SIGN(mcws.next_date- mcws.week_start_date),1,'
||' (mcws.next_date-1),'||'NULL'||')'
||' AND mps.calendar_code = mcd.calendar_code'
||' AND mps.sr_instance_id = mcd.sr_instance_id'
||' AND mps.exception_set_id = mcd.exception_set_id'
||' AND mcd.calendar_date BETWEEN mps.period_start_date '
||' AND DECODE(SIGN(mps.next_date- mps.period_start_date),1,'
||' (mps.next_date-1),'||'NULL'||')' ;
SELECT rowid
FROM msd_st_time
WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
AND calendar_type = G_FISCAL_CAL
AND sr_instance_code = p_instance_code
AND batch_id = p_batch_id;
SELECT distinct calendar_code FROM msd_st_time
WHERE calendar_type = G_FISCAL_CAL
AND instance = p_instance_id
AND process_flag = G_VALID;
'UPDATE msd_st_time'
||' SET process_flag = '||G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE calendar_type not in ('||G_COMPOSITE_CAL||','||G_FISCAL_CAL||')'
||' AND process_flag = '||G_IN_PROCESS
||' AND sr_instance_code = :p_instance_code';
SELECT msd_st_batch_id_s.NEXTVAL
INTO lv_batch_id
FROM DUAL;
'UPDATE msd_st_time'
||' SET batch_id = :lv_batch_id '
||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
||' AND sr_instance_code = :lv_instance_code'
||' AND calendar_type ='||G_FISCAL_CAL
||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE
||' AND rownum <= '||lv_batch_size;
UPDATE msd_st_time
SET st_transaction_id = msd_st_time_s.NEXTVAL,
last_update_date = lv_current_date,
last_updated_by = lv_current_user,
creation_date = lv_current_date,
created_by = lv_current_user
WHERE rowid = lb_rowid(j);
'UPDATE msd_st_time'
||' SET process_flag = '||G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE calendar_type = '||G_FISCAL_CAL
||' AND ( year IS NULL'
||' OR year_description IS NULL'
||' OR year_start_date IS NULL'
||' OR year_end_date IS NULL'
||' OR quarter IS NULL'
||' OR quarter_description IS NULL'
||' OR quarter_start_date IS NULL'
||' OR quarter_end_date IS NULL'
||' OR month IS NULL'
||' OR month_description IS NULL'
||' OR month_start_date IS NULL'
||' OR month_end_date IS NULL)'
||' AND process_flag = '||G_IN_PROCESS
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :p_instance_code';
'UPDATE msd_st_time'
||' SET process_flag = '||G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE calendar_type = '||G_FISCAL_CAL
||' AND (year_start_date >= year_end_date'
||' OR quarter_start_date >= quarter_end_date'
||' OR month_start_date >= month_end_date)'
||' AND process_flag = '||G_IN_PROCESS
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :p_instance_code';
-- Inserting all the errored out records into MSC_ERRORS:
lv_return := MSC_ST_UTIL.LOG_ERROR
(p_table_name => 'MSD_ST_TIME',
p_instance_code => p_instance_code,
p_row => lv_column_names,
p_severity => G_SEV_ERROR,
p_error_text => lv_error_text,
p_message_text => NULL,
p_batch_id => lv_batch_id);
' SELECT min(year_start_date),'
||' max(year_end_date)'
||' FROM msd_st_time'
||' WHERE calendar_code = :calendar_code'
||' AND calendar_type ='||G_FISCAL_CAL
||' AND instance = '||p_instance_id ;
lv_sql_stmt := 'SELECT ' ||
' calendar_code, ' ||
' year, ' ||
' year_description, ' ||
' year_start_date, ' ||
' year_end_date, ' ||
' quarter, ' ||
' quarter_description, ' ||
' quarter_start_date, ' ||
' quarter_end_date, ' ||
' month, ' ||
' month_description, ' ||
' month_start_Date, ' ||
' month_end_date ' ||
' from msd_st_time' ||
' where calendar_code = NVL(''' ||rec1.calendar_code || ''', calendar_code)'||
' and calendar_type='||G_FISCAL_CAL||
' and instance= '||p_instance_id||
' order by month_start_date';
' DELETE from MSD_ST_TIME '
||' WHERE calendar_code = :calendar_code'
||' AND process_flag = '||G_VALID
||' AND calendar_type = '||G_FISCAL_CAL
||' AND sr_instance_code = :lv_instance_code ';
SELECT rowid
FROM msd_st_time
WHERE process_flag IN (G_IN_PROCESS)
AND calendar_type = G_COMPOSITE_CAL
AND sr_instance_code = p_instance_code
AND batch_id = p_batch_id;
SELECT distinct calendar_code FROM msd_st_time
WHERE calendar_type = G_COMPOSITE_CAL
AND instance = p_instance_id
AND process_flag = G_VALID;
'UPDATE msd_st_time'
||' SET process_flag = '||G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE calendar_type not in ('||G_COMPOSITE_CAL||','||G_FISCAL_CAL||')'
||' AND process_flag = '||G_IN_PROCESS
||' AND sr_instance_code = :p_instance_code';
SELECT msd_st_batch_id_s.NEXTVAL
INTO lv_batch_id
FROM DUAL;
'UPDATE msd_st_time'
||' SET batch_id = :lv_batch_id '
||' WHERE process_flag IN ('||G_IN_PROCESS||')'
||' AND sr_instance_code = :lv_instance_code'
||' AND calendar_type ='||G_COMPOSITE_CAL
||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE
||' AND rownum <= '||lv_batch_size;
UPDATE msd_st_time
SET st_transaction_id = msd_st_time_s.NEXTVAL,
last_update_date = lv_current_date,
last_updated_by = lv_current_user,
creation_date = lv_current_date,
created_by = lv_current_user
WHERE rowid = lb_rowid(j);
'UPDATE msd_st_time'
||' SET process_flag = '||G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE calendar_type = '||G_COMPOSITE_CAL
||' AND ( year IS NULL'
||' OR year_description IS NULL'
||' OR year_start_date IS NULL'
||' OR year_end_date IS NULL'
||' OR quarter IS NULL'
||' OR quarter_description IS NULL'
||' OR quarter_start_date IS NULL'
||' OR quarter_end_date IS NULL'
||' OR month IS NULL'
||' OR month_description IS NULL'
||' OR month_start_date IS NULL'
||' OR month_end_date IS NULL'
||' OR week IS NULL'
||' OR week_description IS NULL'
||' OR week_start_date IS NULL'
||' OR week_end_date IS NULL )'
||' AND process_flag = '||G_IN_PROCESS
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :p_instance_code';
'UPDATE msd_st_time'
||' SET process_flag = '||G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE calendar_type = '||G_COMPOSITE_CAL
||' AND (year_start_date > year_end_date'
||' OR quarter_start_date > quarter_end_date'
||' OR month_start_date > month_end_date'
||' OR week_start_date > week_end_date)'
||' AND process_flag = '||G_IN_PROCESS
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :p_instance_code';
-- Inserting all the errored out records into MSC_ERRORS:
lv_return := MSC_ST_UTIL.LOG_ERROR
(p_table_name => 'MSD_ST_TIME',
p_instance_code => p_instance_code,
p_row => lv_column_names,
p_severity => G_SEV_ERROR,
p_error_text => lv_error_text,
p_message_text => NULL,
p_batch_id => lv_batch_id);
' SELECT min(year_start_date),'
||' max(year_end_date)'
||' FROM msd_st_time'
||' WHERE calendar_code = :calendar_code'
||' AND calendar_type ='||G_COMPOSITE_CAL
||' AND instance = '||p_instance_id ;
lv_sql_stmt := 'SELECT ' ||
' calendar_code, ' ||
' year, ' ||
' year_description, ' ||
' year_start_date, ' ||
' year_end_date, ' ||
' quarter, ' ||
' quarter_description, ' ||
' quarter_start_date, ' ||
' quarter_end_date, ' ||
' month, ' ||
' month_description, ' ||
' month_start_Date, ' ||
' month_end_date, ' ||
' week, '||
' week_description, '||
' week_start_date, '||
' week_end_date ' ||
' from msd_st_time' ||
' where calendar_code = NVL(''' ||rec1.calendar_code || ''', calendar_code)'||
' and calendar_type='||G_COMPOSITE_CAL ||
' and instance= '||p_instance_id||
' order by week_start_date';
' DELETE from MSD_ST_TIME '
||' WHERE calendar_code = :calendar_code'
||' AND process_flag = '||G_VALID
||' AND calendar_type = '||G_COMPOSITE_CAL
||' AND sr_instance_code = :lv_instance_code ';
SELECT rowid
FROM msd_st_currency_conversions
WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
AND sr_instance_code = p_instance_code
AND batch_id = p_batch_id;
UPDATE msd_st_currency_conversions
SET st_transaction_id = msd_st_currency_conversions_s.NEXTVAL,
last_update_date = lv_current_date,
last_updated_by = lv_current_user,
creation_date = lv_current_date,
created_by = lv_current_user
WHERE rowid = lb_rowid(j);
'UPDATE msd_st_currency_conversions'
||' SET process_flag = '||G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE (NVL(from_currency, '||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' OR NVL(to_currency, '||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' OR NVL(conversion_date,(sysdate-36500)) = (sysdate-36500)'
||' OR NVL(conversion_rate,'||NULL_VALUE||') = '||NULL_VALUE||')'
||' AND process_flag = '||G_IN_PROCESS
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :p_instance_code';
SELECT rowid
FROM msd_st_cs_data
WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
AND sr_instance_code = p_instance_code
AND batch_id = p_batch_id;
SELECT instance_type
FROM msc_apps_instances
WHERE instance_id=p_instance_id;
||' DELETE_FLAG ||''~''||'
||' ATTRIBUTE_2_VAL ||''~''||'
||' ATTRIBUTE_4 ||''~''||'
||' ATTRIBUTE_6_VAL ||''~''||'
||' ATTRIBUTE_8 ||''~''||'
||' ATTRIBUTE_10_VAL ||''~''||'
||' ATTRIBUTE_12 ||''~''||'
||' ATTRIBUTE_14_VAL ||''~''||'
||' ATTRIBUTE_16 ||''~''||'
||' ATTRIBUTE_18_VAL ||''~''||'
||' ATTRIBUTE_20 ||''~''||'
||' ATTRIBUTE_22_VAL ||''~''||'
||' ATTRIBUTE_24 ||''~''||'
||' ATTRIBUTE_26_VAL ||''~''||'
||' ATTRIBUTE_28 ||''~''||'
||' ATTRIBUTE_30_VAL ||''~''||'
||' ATTRIBUTE_32 ||''~''||'
||' ATTRIBUTE_34 ||''~''||'
||' ATTRIBUTE_41 ||''~''||'
||' ATTRIBUTE_42 ||''~''||'
||' ATTRIBUTE_43 ||''~''||'
||' ATTRIBUTE_44 ||''~''||'
||' ATTRIBUTE_45 ';
UPDATE msd_st_cs_data
SET st_transaction_id = cs_st_data_id,
last_update_date = lv_current_date,
last_updated_by = lv_current_user,
creation_date = lv_current_date,
created_by = lv_current_user
WHERE rowid = lb_rowid(j);
UPDATE msd_st_cs_data
SET st_transaction_id = msd_st_cs_data_s.NEXTVAL,
cs_st_data_id = msd_st_cs_data_s.CURRVAL, -- SEQUENCE
last_update_date = lv_current_date,
last_updated_by = lv_current_user,
creation_date = lv_current_date,
created_by = lv_current_user
WHERE rowid = lb_rowid(j);
' UPDATE msd_st_cs_data mscd'
||' SET cs_definition_id = NVL(( SELECT cs_definition_id'
||' FROM msd_cs_definitions mcd'
||' WHERE mscd.cs_definition_name = mcd.name),'||NULL_VALUE||')'
||' WHERE process_flag = '||G_IN_PROCESS
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :p_instance_code';
'UPDATE msd_st_cs_data'
||' SET process_flag = '||G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE NVL(cs_definition_id,'||NULL_VALUE||') = '||NULL_VALUE
||' AND batch_id = :lv_batch_id'
||' AND process_flag = '||G_IN_PROCESS
||' AND sr_instance_code = :p_instance_code';
UPDATE msd_st_cs_data
SET attribute_45 = attribute_43
WHERE cs_definition_name IN ('MSD_CMRO_FIRM_MTL_REQUIREMENT','MSD_CMRO_UNPLANNED_HISTORY','MSD_CMRO_PLANNED_HISTORY')
AND batch_id = lv_batch_id
AND process_flag = G_IN_PROCESS
AND sr_instance_code = p_instance_code;
'UPDATE msd_st_cs_data'
||' SET process_flag = '||G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE NVL(cs_name, '||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' AND batch_id = :lv_batch_id'
||' AND process_flag = '||G_IN_PROCESS
||' AND sr_instance_code = :p_instance_code';
' UPDATE msd_st_cs_data mscd'
||' SET attribute_14 = 1'
||' WHERE attribute_16 is not null'
||' AND process_flag = '||G_IN_PROCESS
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :p_instance_code';
UPDATE msd_st_cs_data mcd
SET process_flag = G_ERROR_FLG,
error_text = lv_message_text
WHERE NOT EXISTS(SELECT 1
FROM msc_st_system_items msi
WHERE msi.sr_instance_id = p_instance_id
AND nvl(msi.company_name,lv_my_company) = lv_my_company
AND msi.organization_id = mcd.attribute_11
AND msi.item_name = mcd.attribute_4
AND ((v_plan_per_profile = 4) OR (msi.ato_forecast_control <> 3)) --forecast control - none
AND ((msi.mrp_planning_code <> 6 ) OR (msi.pick_components_flag='Y' )) --Not planned item
UNION
SELECT 1
FROM msc_system_items mls
WHERE mls.sr_instance_id = p_instance_id
AND mls.organization_id = mcd.attribute_11
AND mls.item_name = mcd.attribute_4
AND mls.plan_id = -1
AND ((v_plan_per_profile = 4) OR (mls.ato_forecast_control <> 3)) --forecast control - none
AND ((mls.mrp_planning_code <> 6 ) OR (mls.pick_components_flag='Y' )) ) --Not planned item
AND mcd.attribute_2 = 1
AND mcd.attribute_10 = 7
AND mcd.process_flag = G_IN_PROCESS
AND mcd.sr_instance_code = p_instance_code
AND mcd.batch_id = p_batch_id;
SELECT rowid
FROM msd_st_price_list
WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
AND sr_instance_code = p_instance_code
AND batch_id = p_batch_id;
SELECT instance_type
FROM msc_apps_instances
WHERE instance_id=p_instance_id;
UPDATE msd_st_price_list
SET st_transaction_id = msd_st_price_list_s.NEXTVAL,
last_update_date = lv_current_date,
last_updated_by = lv_current_user,
creation_date = lv_current_date,
created_by = lv_current_user
WHERE rowid = lb_rowid(j);
UPDATE msd_st_price_list mspl
SET process_flag = G_ERROR_FLG,
error_text = lv_message_text
WHERE NOT EXISTS(SELECT 1
FROM msc_st_units_of_measure msum
--WHERE msum.sr_instance_id = p_instance_id
WHERE msum.sr_instance_code = p_instance_code
AND msum.process_flag = G_VALID
AND msum.uom_code = mspl.price_list_uom
UNION
SELECT 1
FROM msc_units_of_measure mum
WHERE mum.sr_instance_id = p_instance_id
AND mum.uom_code = mspl.price_list_uom )
AND mspl.process_flag = G_IN_PROCESS
AND mspl.sr_instance_code = p_instance_code
AND mspl.batch_id = p_batch_id;
UPDATE msd_st_price_list mpl
SET process_flag = G_ERROR_FLG,
error_text = lv_message_text
WHERE NOT EXISTS(SELECT 1
FROM msc_st_system_items msi
WHERE msi.sr_instance_id = p_instance_id
AND nvl(msi.company_name,lv_my_company) = lv_my_company
AND msi.organization_id = mpl.sr_organization_lvl_pk
AND msi.item_name = mpl.sr_product_lvl_val
AND ((v_plan_per_profile = 4) OR (msi.ato_forecast_control <> 3)) --forecast control - none
AND ((msi.mrp_planning_code <> 6 ) OR (msi.pick_components_flag='Y' )) --Not planned item
UNION
SELECT 1
FROM msc_system_items mls
WHERE mls.sr_instance_id = p_instance_id
AND mls.organization_id = mpl.sr_organization_lvl_pk
AND mls.item_name = mpl.sr_product_lvl_val
AND mls.plan_id = -1
AND ((v_plan_per_profile = 4) OR (mls.ato_forecast_control <> 3)) --forecast control - none
AND ((mls.mrp_planning_code <> 6 ) OR (mls.pick_components_flag='Y' )) ) --Not planned item
AND mpl.product_lvl_id = 1
AND mpl.organization_lvl_id = 7
AND mpl.process_flag = G_IN_PROCESS
AND mpl.sr_instance_code = p_instance_code
AND mpl.batch_id = p_batch_id;
SELECT rowid
FROM msd_st_uom_conversions
WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
AND sr_instance_code = p_instance_code
AND batch_id = p_batch_id;
UPDATE msd_st_uom_conversions
SET st_transaction_id = msd_st_uom_conversions_s.NEXTVAL,
last_update_date = lv_current_date,
last_updated_by = lv_current_user,
creation_date = lv_current_date,
created_by = lv_current_user
WHERE rowid = lb_rowid(j);
'UPDATE msd_st_uom_conversions'
||' SET process_flag = '||G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE (NVL(from_uom_class, '||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' OR NVL(to_uom_class, '||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' OR NVL(from_uom_code, '||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' OR NVL(to_uom_code, '||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' OR NVL(conversion_rate,'||NULL_VALUE||') = '||NULL_VALUE||')'
||' AND process_flag = '||G_IN_PROCESS
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :p_instance_code';
SELECT rowid
FROM msd_st_setup_parameters
WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
AND sr_instance_code = p_instance_code ;
SELECT instance,
parameter_name,
parameter_value
FROM msd_st_setup_parameters
WHERE instance = p_instance_id ;
UPDATE msd_st_setup_parameters
SET st_transaction_id = msd_st_setup_parameters_s.NEXTVAL,
last_update_date = lv_current_date,
last_updated_by = lv_current_user,
creation_date = lv_current_date,
created_by = lv_current_user
WHERE rowid = lb_rowid(j);
' UPDATE msd_st_setup_parameters'
||' SET process_flag = '||G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE (NVL(parameter_name, '||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' OR NVL(parameter_value, '||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''||')'
||' AND process_flag = '||G_IN_PROCESS
||' AND sr_instance_code = :p_instance_code';
UPDATE msd_setup_parameters
SET parameter_value = c_rec.parameter_value
WHERE parameter_name = c_rec.parameter_name
AND instance_id = c_rec.instance ;
DELETE FROM msd_setup_parameters
WHERE PARAMETER_NAME = c_rec.parameter_name;
INSERT INTO MSD_SETUP_PARAMETERS
( INSTANCE_ID,
PARAMETER_NAME,
PARAMETER_VALUE )
VALUES
( c_rec.instance,
c_rec.parameter_name,
c_rec.parameter_value );
DELETE FROM MSD_ST_SETUP_PARAMETERS
WHERE instance = c_rec.instance
AND parameter_name = c_rec.parameter_name
AND parameter_value = c_rec.parameter_value;
SELECT rowid
FROM msd_st_item_relationships
WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
AND sr_instance_code = p_instance_code
AND batch_id = p_batch_id;
SELECT msd_st_batch_id_s.NEXTVAL
INTO lv_batch_id
FROM DUAL;
'UPDATE msd_st_item_relationships'
||' SET batch_id = :lv_batch_id '
||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
||' AND sr_instance_code = :lv_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE
||' AND rownum <= '||lv_batch_size;
UPDATE msd_st_item_relationships
SET st_transaction_id = msd_st_item_relationships_s.NEXTVAL,
last_update_date = lv_current_date,
last_updated_by = lv_current_user,
creation_date = lv_current_date,
created_by = lv_current_user
WHERE rowid = lb_rowid(j);
'UPDATE msd_st_item_relationships t1'
||' SET t1.relationship_type_id = 8 ' -- Relationship Type - Superseded
||' WHERE t1.process_flag ='|| G_IN_PROCESS
||' AND t1.batch_id = :lv_batch_id'
||' AND t1.sr_instance_code = :p_instance_code';
'UPDATE msd_st_item_relationships t1'
||' SET t1.relationship_type_id = ( select lookup_code '
||' from mfg_lookups t2 '
||' where t1.relationship_type = t2.meaning '
||' and t2.lookup_type =''MTL_RELATIONSHIP_TYPES'' )'
||' WHERE t1.process_flag ='|| G_IN_PROCESS
||' AND t1.batch_id = :lv_batch_id'
||' AND t1.sr_instance_code = :p_instance_code';
'UPDATE msd_st_item_relationships '
||' SET process_flag ='||G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE NVL(RELATIONSHIP_TYPE_ID,'||NULL_VALUE||') = '||NULL_VALUE
||' AND process_flag ='|| G_IN_PROCESS
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :p_instance_code';
-- Inserting all the errored out records into MSC_ERRORS:
lv_return := MSC_ST_UTIL.LOG_ERROR
(p_table_name => 'MSD_ST_ITEM_RELATIONSHIPS',
p_instance_code => p_instance_code,
p_row => lv_column_names,
p_severity => G_SEV_ERROR,
p_error_text => lv_error_text,
p_message_text => NULL,
p_batch_id => lv_batch_id);
SELECT instance_type
FROM msc_apps_instances
WHERE instance_id=p_instance_id;
SELECT rowid
FROM msd_st_level_org_asscns
WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
AND sr_instance_code = p_instance_code
AND batch_id = p_batch_id;
SELECT msd_st_batch_id_s.NEXTVAL
INTO lv_batch_id
FROM DUAL;
'UPDATE msd_st_level_org_asscns'
||' SET batch_id = :lv_batch_id '
||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
||' AND sr_instance_code = :lv_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE
||' AND rownum <= '||lv_batch_size;
UPDATE msd_st_level_org_asscns
SET st_transaction_id = msd_st_level_org_asscns_s.NEXTVAL,
last_update_date = lv_current_date,
last_updated_by = lv_current_user,
creation_date = lv_current_date,
created_by = lv_current_user
WHERE rowid = lb_rowid(j);
UPDATE msd_st_level_org_asscns mloa
SET process_flag = G_ERROR_FLG,
error_text = lv_message_text
WHERE mloa.process_flag = G_IN_PROCESS
AND mloa.sr_instance_code = p_instance_code
AND mloa.batch_id = lv_batch_id
AND ( mloa.org_level_id <> 8 OR mloa.level_id not in (18,11) );
UPDATE msd_st_level_org_asscns mloa
SET process_flag = G_ERROR_FLG,
error_text = lv_message_text
WHERE mloa.process_flag = G_IN_PROCESS
AND mloa.sr_instance_code = p_instance_code
AND mloa.batch_id = lv_batch_id
AND ( mloa.org_level_id <> 8 OR mloa.level_id not in (18,11) )
AND ( mloa.org_level_id <> 7 OR mloa.level_id <> 1 );
-- Inserting all the errored out records into MSC_ERRORS:
lv_return := MSC_ST_UTIL.LOG_ERROR
(p_table_name => 'MSD_ST_LEVEL_ORG_ASSCNS',
p_instance_code => p_instance_code,
p_row => lv_column_names,
p_severity => G_SEV_ERROR,
p_error_text => lv_error_text,
p_message_text => NULL,
p_batch_id => lv_batch_id);
PROCEDURE LAUNCH_DELETE_DUPLICATES (ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY NUMBER,
p_instance_id IN NUMBER)
IS
lv_error_text VARCHAR2(250);
delete from msd_st_level_values m1
where rowid<>(select max(rowid) from msd_st_level_values m2
where m2.level_id= m1.level_id and m2.instance = m1.instance
and m2.sr_level_pk = m1.sr_level_pk )
and m1.instance=p_instance_id ;
msc_st_util.log_message('****no of row deleted*****' || SQL%ROWCOUNT);
delete from msd_st_level_associations m1
where rowid<> (select max(rowid)
from msd_st_level_associations m2
where m2.level_id= m1.level_id
and m2.instance = m1.instance
and m2.sr_level_pk = m1.sr_level_pk
and m2.PARENT_LEVEL_ID=m1.PARENT_LEVEL_ID
and m2.SR_PARENT_LEVEL_PK = m1.SR_PARENT_LEVEL_PK
)
and m1.instance=p_instance_id ;
msc_st_util.log_message('****no of row deleted*****' || SQL%ROWCOUNT);
lv_error_text := substr('LAUNCH_DELETE_DUPLICATES '||'('||v_sql_stmt||')'|| SQLERRM, 1, 240);
END LAUNCH_DELETE_DUPLICATES;
SELECT distinct cs_name,cs_definition_id
FROM msd_st_cs_data
WHERE attribute_1 = to_char(p_instance_id)
AND request_id = p_request_id; --Bug 3002566