[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| DESCRIPTION : This function is called for inserting the errored records |
| into the error table for the severities warning and error. |
+==========================================================================*/
FUNCTION LOG_ERROR
(p_table_name VARCHAR2,
p_instance_code VARCHAR2,
p_row CLOB,
p_severity NUMBER DEFAULT G_SEV_ERROR,
p_error_text OUT NOCOPY VARCHAR2,
p_message_text VARCHAR2 DEFAULT NULL,
p_batch_id NUMBER DEFAULT NULL,
p_where_str VARCHAR2 DEFAULT NULL,
p_col_name VARCHAR2 DEFAULT NULL,
p_default_value VARCHAR2 DEFAULT NULL,
p_debug BOOLEAN DEFAULT FALSE,
p_propagated VARCHAR2 DEFAULT 'N')
RETURN NUMBER IS
lv_sql_stmt VARCHAR2(5000);
'INSERT INTO msc_errors'
||'( error_id,'
||' transaction_id,'
||' message_id,'
||' instance_code,'
||' table_name,'
||' propagated,'
||' source,'
||' rrow,'
||' severity,'
||' message_sent,'
||' last_update_date,'
||' last_updated_by,'
||' creation_date,'
||' created_by,'
||' last_update_login,'
||' request_id,'
||' program_application_id,'
||' program_id,'
||' program_update_date,'
||' error_text)'
||' SELECT'
||' msc_errors_s.NEXTVAL,'
||' st_transaction_id,'
||' message_id,'
|| ''''||p_instance_code||''''||','
|| ''''||p_table_name||''''||','
|| ''''||p_propagated||''''||','
||' data_source_type,'
|| p_row||','
|| p_severity||','
|| SYS_NO||','
||' last_update_date,'
||' last_updated_by,'
||' creation_date,'
||' created_by,'
||' last_update_login,'
||' request_id,'
||' program_application_id,'
||' program_id,'
||' program_update_date,'
|| lv_message_text
||' FROM '
|| p_table_name
||' WHERE sr_instance_code = :p_instance_code'
||' AND (('||p_severity||'='||G_WARNING
||' AND process_flag = '||G_IN_PROCESS||')'
||' OR ('||p_severity ||'='||G_SEV_ERROR
||' AND process_flag = '||G_ERROR||'))'
|| lv_where_str;
'UPDATE '||p_table_name
||' SET '||p_col_name|| ' = '||p_default_value
||' WHERE sr_instance_code = :p_instance_code'
||' AND process_flag = '||G_IN_PROCESS
|| lv_where_str;
| else the company_id is updated with -1. |
| |
| p_table_name - Name of the table for whose column the id derived. |
| (eg., msc_st_supplies). |
| p_company_name - Name of the column whose id is derived. |
| (eg., company_name) |
| p_company_id - Name of the column which stores the id. |
| (eg., company_id) |
| p_instance_code - Instance_code column name |
| p_default_value - default value(-1) will be used if the instance_type |
| is other than 5(ie.,SCE) |
| p_instance_type - Type of the instance |
| (eg., 4 - SCE ) |
| p_message_text - Pre-defined message text. |
| p_severity - Severity fo the error(1.Warning and 2. Error |
| p_error_text - This communicates the error message to the calling |
| function, if any. |
| p_row - Concatenated column names of the table. This is |
| used for error logging in case of child tables. |
+==========================================================================*/
FUNCTION DERIVE_COMPANY_ID
(p_table_name VARCHAR2,
p_company_name VARCHAR2,
p_company_id VARCHAR2,
p_instance_code VARCHAR2,
p_error_text OUT NOCOPY VARCHAR2,
p_default_value NUMBER DEFAULT -1,
p_instance_type NUMBER DEFAULT 3 ,
p_batch_id NUMBER DEFAULT NULL_VALUE,
p_severity NUMBER DEFAULT 0 ,
p_message_text VARCHAR2 DEFAULT NULL,
p_debug BOOLEAN DEFAULT FALSE,
p_row LONG DEFAULT NULL)
RETURN NUMBER IS
lv_sql_stmt VARCHAR2(5000);
'UPDATE '|| p_table_name ||' t1 '
||' SET '||p_company_id ||'= -1 '
||' WHERE exists( SELECT 1 '
||' FROM msc_companies mc '
||' WHERE mc.company_name = nvl(t1.'||p_company_name ||' ,:lv_my_company) '
||' AND mc.company_id = 1 '
||' AND NVL(mc.disable_date,sysdate+1) > sysdate) '
||' AND t1.process_flag ='|| G_IN_PROCESS
||' AND t1.sr_instance_code = :p_instance_code'
||' AND NVL(t1.batch_id,'||''''||NULL_CHAR||''''||') = NVL(:p_batch_id,'||''''||NULL_VALUE||''''||')';
'UPDATE '|| p_table_name ||' t1 '
||' SET '||p_company_id ||'= (SELECT local_id from MSC_LOCAL_ID_SETUP'
||' WHERE char1 = t1.sr_instance_code'
||' and NVL(char3,'||''''||NULL_CHAR||''''||') = NVL(t1.'||p_company_name||','||''''||NULL_CHAR||''''||')'
||' and entity_name = '||''''||'SR_TP_ID'||''''
||' and number1 in (1,2) '
||' and rownum = 1) ' -- we need the first occurence of sr_tp_id
||' WHERE t1.'||p_company_id ||' IS NULL'
||' AND t1.process_flag ='|| G_IN_PROCESS
||' AND t1.sr_instance_code = :p_instance_code'
||' AND NVL(t1.batch_id ,'||''''||NULL_CHAR||''''||') = NVL(:p_batch_id,'||''''||NULL_CHAR||''''||')';
'UPDATE '|| p_table_name ||' t1 '
||' SET '||p_company_id ||'= (SELECT local_id from MSC_LOCAL_ID_SETUP'
||' WHERE char1 = t1.sr_instance_code'
||' and NVL(char3,'||''''||NULL_CHAR||''''||') = NVL(t1.'||p_company_name||','||''''||NULL_CHAR||''''||')'
||' and entity_name = '||''''||'SR_TP_ID'||''''
||' and number1 in (1,2) '
||' and rownum = 1) ' -- we need the first occurence of sr_tp_id
||' WHERE t1.'||p_company_id ||' IS NULL'
||' AND t1.process_flag ='|| G_IN_PROCESS
||' AND t1.sr_instance_code = :p_instance_code'
||' AND NVL(t1.batch_id ,'||''''||NULL_CHAR||''''||') = NVL(:p_batch_id,'||''''||NULL_CHAR||''''||')'
||' AND t1.partner_type =3';
'UPDATE '||p_table_name ||' t1 '
||' SET error_text ='||''''||p_message_text||''''||','
||' process_flag = '||g_error
||' WHERE NVL(t1.'||p_company_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
select company_name into p_my_company
from msc_companies
where company_id = 1;
'UPDATE '||p_table_name ||' t1'
||' SET '||p_item_col_id
||' = (SELECT distinct local_id'
||' FROM msc_local_id_item t2'
||' WHERE t2.char1 = t1.sr_instance_code '
||' AND NVL(t2.char2, '||''''||NULL_CHAR||''''||')='
||' NVL(t1.company_name,'||''''||NULL_CHAR||''''||')'
||' AND t2.char3 = t1.organization_code'
||' AND t2.char4 = t1.'||p_item_col_name
||' AND t2.entity_name = ''SR_INVENTORY_ITEM_ID'' '
||' AND t2.instance_id = :p_instance_id)'
||' WHERE sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND process_flag = '||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET '||p_item_col_id
||' = (SELECT distinct local_id'
||' FROM msc_local_id_item t2'
||' WHERE t2.char1 = t1.sr_instance_code '
||' AND NVL(t2.char2, '||''''||NULL_CHAR||''''||')='
||' NVL(t1.company_name,'||''''||NULL_CHAR||''''||')'
||' AND t2.char4 = t1.'||p_item_col_name
||' AND t2.entity_name = ''SR_INVENTORY_ITEM_ID'' '
||' AND t2.instance_id = :p_instance_id)'
||' WHERE sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND process_flag = '||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET '||p_item_col_id
||' = (SELECT distinct mil.sr_inventory_item_id'
||' FROM msc_item_id_lid mil, msc_system_items t2'
||' WHERE mil.sr_instance_id = t2.sr_instance_id'
||' AND mil.inventory_item_id = t2.inventory_item_id'
||' AND t2.item_name = t1.'||p_item_col_name
||' AND t2.sr_instance_id = :p_instance_id'
||' AND t2.organization_id = t1.organization_id'
||' AND t2.plan_id = -1)'
||' WHERE sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND process_flag = '||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||g_error
||' WHERE NVL(t1.'||p_item_col_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||g_error
||' WHERE NVL(t1.'||p_item_col_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND NVL(t1.'||p_item_col_name||','||''''||NULL_CHAR||''''||') '
||' <> '||''''||NULL_CHAR||''''
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
'UPDATE '||p_table_name
||' SET '||p_org_partner_id
||' = (SELECT local_id'
||' FROM msc_local_id_setup '
||' WHERE char1 = sr_instance_code'
||' AND NVL(char2, '||''''||NULL_CHAR||''''||')='
||' NVL(company_name,'||''''||NULL_CHAR||''''||')'
||' AND char3 = '||p_org_partner_name
||' AND number1 = '||p_partner_type
||lv_where_str2
||' AND entity_name = ''SR_TP_ID'' )'
||' WHERE sr_instance_code = :p_instance_code'
||' AND NVL('||p_org_partner_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
||' AND process_flag = '||G_IN_PROCESS
|| lv_where_str1;
'UPDATE '||p_table_name
||' SET '||p_org_partner_id
||' = (SELECT local_id'
||' FROM msc_local_id_setup '
||' WHERE char1 = sr_instance_code'
||' AND char3 = '||p_org_partner_name
||' AND number1 = '||p_partner_type
||lv_where_str2
||' AND entity_name = ''SR_TP_ID'' )'
||' WHERE sr_instance_code = :p_instance_code'
||' AND NVL('||p_org_partner_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
||' AND process_flag = '||G_IN_PROCESS
|| lv_where_str1;
'UPDATE '||p_table_name ||' t1'
||' SET '||p_org_partner_id
||' = (SELECT sr_tp_id'
||' FROM msc_trading_partners mtp'
||' WHERE mtp.partner_type = '||p_partner_type
||' AND mtp.organization_code = '||''''||p_instance_code||''''||'||'':''||'||'t1.'||p_org_partner_name
||' AND mtp.sr_instance_id = '||v_instance_id||')'
||' WHERE sr_instance_code = :p_instance_code'
||' AND NVL('||p_org_partner_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
||' AND process_flag = '||G_IN_PROCESS
|| lv_where_str1;
'UPDATE '||p_table_name ||' t1'
||' SET '||p_org_partner_id
||' = (SELECT max(mtil.sr_tp_id)'
||' FROM msc_tp_id_lid mtil, msc_trading_partners mtp'
||' WHERE mtil.partner_type = '||p_partner_type
||' AND mtil.sr_instance_id = '||v_instance_id
||' AND mtil.tp_id = mtp.partner_id'
||' AND mtp.partner_name = t1.'||p_org_partner_name
||' AND mtp.partner_type = '||p_partner_type||')'
||' WHERE sr_instance_code = :p_instance_code'
||' AND NVL('||p_org_partner_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
||' AND process_flag = '||G_IN_PROCESS
|| lv_where_str1;
'UPDATE '||p_table_name
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||g_error
||' WHERE NVL('||p_org_partner_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
||' AND process_flag = '||G_IN_PROCESS
|| lv_where_str1;
'UPDATE '||p_table_name
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||G_ERROR
||' WHERE NVL('||p_org_partner_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND NVL('||p_org_partner_name||','||''''||NULL_CHAR||''''||')'
||' <> '||''''||NULL_CHAR||''''
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND process_flag = '||G_IN_PROCESS
|| lv_where_str1;
'UPDATE '||p_table_name
||' SET '||p_partner_site_id
||' = (SELECT local_id'
||' FROM msc_local_id_setup '
||' WHERE char1 = sr_instance_code'
||' AND NVL(char2, '||''''||NULL_CHAR||''''||')='
||' NVL(company_name,'||''''||NULL_CHAR||''''||')'
||' AND char3 = '||p_partner_name
||' AND char4 = '||p_partner_site_code
||' AND number1 = '||p_partner_type
||lv_where_str3
||' AND entity_name = ''SR_TP_SITE_ID'' )'
||' WHERE sr_instance_code = :p_instance_code'
||' AND NVL('||p_partner_site_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND process_flag = '||G_IN_PROCESS
|| lv_where_str1;
'UPDATE '||p_table_name ||' t1'
||' SET '||p_partner_site_id
||' = (SELECT mtsil.sr_tp_site_id'
||' FROM msc_tp_site_id_lid mtsil, msc_trading_partner_sites mtps, msc_trading_partners mtp'
||' WHERE mtsil.partner_type = '||p_partner_type
||' AND mtsil.sr_instance_id = '||v_instance_id
||' AND mtsil.tp_site_id = mtps.partner_site_id'
||' AND mtps.partner_id = mtp.partner_id'
||' AND mtp.partner_type = '||p_partner_type
||' AND mtp.partner_name = t1.'||p_partner_name
||' AND mtps.partner_type = '||p_partner_type;
'UPDATE '||p_table_name
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||g_error
||' WHERE NVL('||p_partner_site_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
||' AND process_flag = '||G_IN_PROCESS
|| lv_where_str1 ;
'UPDATE '||p_table_name
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||G_ERROR
||' WHERE NVL('||p_partner_site_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND NVL('||p_partner_site_code||','||''''||NULL_CHAR||''''||') '
||' <> '||''''||NULL_CHAR||''''
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
||' AND process_flag = '||G_IN_PROCESS
|| lv_where_str1;
'UPDATE '||p_table_name ||' t1'
||' SET '||p_proj_task_col_id
||' = (SELECT local_id'
||' FROM msc_local_id_misc t2'
||' WHERE t2.char1 = t1.sr_instance_code '
||' AND NVL(t2.char2, '||''''||NULL_CHAR||''''||')='
||' NVL(t1.company_name,'||''''||NULL_CHAR||''''||')'
/* ||' AND t2.char3 = t1.organization_code '*/
||' AND t2.char4 = t1.'||p_proj_col_name;
'UPDATE '||p_table_name ||' t1'
||' SET '||p_proj_task_col_id
||' = (SELECT project_id'
||' FROM msc_projects t2'
||' WHERE t2.project_number = t1.'||p_proj_col_name
||' AND t2.sr_instance_id ='||v_instance_id
||' AND t2.plan_id = -1 '
||' AND t2.organization_id = t1.organization_id)'
||' WHERE sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
||' AND process_flag = '||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET '||p_proj_task_col_id
||' = (SELECT task_id'
||' FROM msc_project_tasks t2'
||' WHERE t2.project_id = t1.project_id '
||' AND t2.sr_instance_id = '||v_instance_id
||' AND t2.plan_id = -1 '
||' AND t2.task_number = t1.'||p_task_col_name
||' AND t2.organization_id = t1.organization_id)'
||' WHERE sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
||' AND process_flag = '||G_IN_PROCESS;
'UPDATE '||p_table_name
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||G_ERROR
||' WHERE NVL('||p_proj_task_col_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND process_flag = '||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||g_error
||' WHERE NOT EXISTS (SELECT 1'
||' FROM msc_units_of_measure muom'
||' WHERE muom.uom_code = t1.'||p_uom_col_name
||' UNION'
||' SELECT 1 FROM msc_st_units_of_measure msuom'
||' WHERE msuom.uom_code = t1.'||p_uom_col_name
||' AND msuom.sr_instance_id = :v_instance_id'
||' AND msuom.process_flag = '||G_VALID||' )'
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||g_error
||' WHERE NOT EXISTS (SELECT 1'
||' FROM msc_units_of_measure muom'
||' WHERE muom.uom_code = t1.'||p_uom_col_name
||' UNION'
||' SELECT 1 FROM msc_st_units_of_measure msuom'
||' WHERE msuom.uom_code = t1.'||p_uom_col_name
||' AND msuom.sr_instance_id = :v_instance_id'
||' AND msuom.process_flag = '||G_VALID||' )'
||' AND NVL(t1.'||p_uom_col_name||','||''''||NULL_CHAR||''''||') '
||' <> '||''''||NULL_CHAR||''''
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||g_error
||' WHERE NOT EXISTS (SELECT 1'
||' FROM msc_demand_classes mdc'
||' WHERE mdc.demand_class = t1.'||p_dmd_class_column
||' AND mdc.sr_instance_id = :v_instance_id'
||' UNION'
||' SELECT 1 FROM msc_st_demand_classes mstd'
||' WHERE mstd.demand_class = t1.'||p_dmd_class_column
||' AND mstd.sr_instance_id = :v_instance_id'
||' AND mstd.process_flag = '||G_VALID||' )'
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||g_error
||' WHERE NOT EXISTS (SELECT 1'
||' FROM msc_demand_classes mdc'
||' WHERE mdc.demand_class = t1.'||p_dmd_class_column
||' AND mdc.sr_instance_id = :v_instance_id'
||' UNION'
||' SELECT 1 FROM msc_st_demand_classes mstd'
||' WHERE mstd.demand_class = t1.'||p_dmd_class_column
||' AND mstd.sr_instance_id = :v_instance_id'
||' AND mstd.process_flag = '||G_VALID||' )'
||' AND NVL(t1.'||p_dmd_class_column||','||''''||NULL_CHAR||''''||') '
||' <> '||''''||NULL_CHAR||''''
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET '||p_bom_col_id
||' = (SELECT local_id'
||' FROM msc_local_id_setup t2'
||' WHERE t2.char1 = t1.sr_instance_code'
||' AND NVL(t2.char2,'||''''||NULL_CHAR||''''||') '
||' = NVL(t1.company_name,'||''''||NULL_CHAR||''''||') '
||' AND t2.char3 = t1.organization_code'
||' AND NVL(t2.char6,'||''''||NULL_CHAR||''''||') '
||' = NVL( t1.alternate_bom_designator,'||''''||NULL_CHAR||''''||') '
||' AND t2.char4 = t1.'||p_bom_col_name
||' AND t2.entity_name = ''BILL_SEQUENCE_ID'')'
||' WHERE sr_instance_code = :p_instance_code'
||' AND batch_id = :p_batch_id'
||' AND process_flag = '||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET '||p_bom_col_id
||' = (SELECT bill_sequence_id'
||' FROM msc_boms t2, msc_item_id_lid mil'
||' WHERE t2.plan_id = -1'
||' AND t2.organization_id = t1.organization_id'
||' AND t2.sr_instance_id = '||v_instance_id
||' AND t2.assembly_item_id = mil.inventory_item_id'
||' AND mil.sr_inventory_item_id = t1.inventory_item_id'
||' AND mil.sr_instance_id = '||v_instance_id
||' AND NVL(t2.alternate_bom_designator,'||''''||NULL_CHAR||''''||') '
||' = NVL( t1.alternate_bom_designator,'||''''||NULL_CHAR||''''||')) '
||' WHERE sr_instance_code = :p_instance_code'
||' AND batch_id = :p_batch_id'
||' AND process_flag = '||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||G_ERROR
||' WHERE NVL(t1.'||p_bom_col_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND sr_instance_code = :p_instance_code'
||' AND batch_id = :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||G_ERROR
||' WHERE NVL(t1.'||p_bom_col_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND NVL(t1.'||p_bom_col_name||','||''''||NULL_CHAR||''''||')'
||' <> '||''''||NULL_CHAR||''''
||' AND sr_instance_code = :p_instance_code'
||' AND batch_id = :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET '||p_rtg_col_id
||' = (SELECT local_id'
||' FROM msc_local_id_setup t2'
||' WHERE t2.char1 = t1.sr_instance_code'
||' AND NVL(t2.char2,'||''''||NULL_CHAR||''''||') '
||' = NVL(t1.company_name,'||''''||NULL_CHAR||''''||') '
||' AND t2.char3 = t1.organization_code'
||' AND NVL(t2.char6,'||''''||NULL_CHAR||''''||') '
||' = NVL( t1.alternate_routing_designator,'||''''||NULL_CHAR||''''||') '
||' AND t2.char4 = t1.'||p_rtg_col_name
||' AND t2.entity_name = ''ROUTING_SEQUENCE_ID'')'
||' WHERE sr_instance_code = :p_instance_code'
||' AND batch_id = :p_batch_id'
||' AND process_flag = '||G_IN_PROCESS
|| p_where_str;
'UPDATE '||p_table_name ||' t1'
||' SET '||p_rtg_col_id
||' = (SELECT routing_sequence_id'
||' FROM msc_routings t2, msc_item_id_lid mil'
||' WHERE t2.plan_id = -1'
||' AND t2.organization_id = t1.organization_id'
||' AND t2.sr_instance_id = '||v_instance_id
||' AND t2.assembly_item_id = mil.inventory_item_id'
||' AND mil.sr_inventory_item_id = t1.'||p_item_id
||' AND mil.sr_instance_id = '||v_instance_id
||' AND NVL(t2.alternate_routing_designator,'||''''||NULL_CHAR||''''||') '
||' = NVL( t1.alternate_routing_designator,'||''''||NULL_CHAR||''''||')) '
||' WHERE sr_instance_code = :p_instance_code'
||' AND batch_id = :p_batch_id'
||' AND process_flag = '||G_IN_PROCESS
|| p_where_str;
'UPDATE '||p_table_name ||' t1'
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||G_ERROR
||' WHERE NVL(t1.'||p_rtg_col_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND sr_instance_code = :p_instance_code'
||' AND batch_id = :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET p_message_text = '||''''||p_message_text||''''||','
||' process_flag = '||g_error
||' WHERE NVL(t1.'||p_rtg_col_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND NVL(t1.'||p_rtg_col_name||', '||''''||NULL_CHAR||''''||')'
||' <> '||''''||NULL_CHAR||''''
||' AND sr_instance_code = :p_instance_code'
||' AND batch_id = :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
'UPDATE '||p_table_name
||' SET process_flag = '||G_VALID||','
|| p_instance_id_col||' = :p_instance_id'
||' WHERE process_flag = '||G_IN_PROCESS
||' AND sr_instance_code = :p_instance_code'
|| lv_where_str;
'UPDATE '||p_table_name
||' SET process_flag = ' ||G_IN_PROCESS ||','
|| p_instance_id_col||' = 0' ||','
||' WHERE nvl(process_flag,'||G_NEW ||') = '||G_NEW
||' AND sr_instance_code = :p_instance_code'
|| lv_where_str;
' UPDATE '||p_table_name||' t1'
||' SET '||p_level_id_col
||' = NVL((SELECT level_id'
||' FROM msd_levels t2'
||' WHERE t2.level_name = t1.'||p_level_name_col
||' AND t2.PLAN_TYPE IS NULL ),' --bug 4443782
|| NULL_VALUE ||')'
||' WHERE sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND NVL(t1.'||p_level_name_col||','||''''||NULL_CHAR||''''||') '
||' <> '||''''||NULL_CHAR||''''
||' AND process_flag = '||G_IN_PROCESS ;
'UPDATE '||p_table_name ||' t1'
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||G_ERROR
||' WHERE NVL(t1.'||p_level_id_col||','||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||g_error
||' WHERE NVL(t1.'||p_level_id_col||','||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' AND NVL(t1.'||p_level_name_col||','||''''||NULL_CHAR||''''||') '
||' <> '||''''||NULL_CHAR||''''
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET '||p_level_pk_col
||' = (SELECT local_id'
||' FROM msd_local_id_setup t2'
||' WHERE t2.char1 = t1.sr_instance_code'
||' AND t2.char2 = t1.'||p_level_val_col
||' AND t2.level_id = t1.'||p_level_id_col||')'
||' WHERE sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND NVL(t1.'||p_level_pk_col||','||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' AND process_flag = '||G_IN_PROCESS ;
'UPDATE '||p_table_name ||' t1'
||' SET '||p_pk_col_name
||' = NVL((SELECT sr_level_pk '
||' FROM msd_level_values t2'
||' WHERE t2.level_value '
||' = t1.'||p_column_name
||' AND t2.level_id = :p_level_id '
||' AND t2.instance = :p_instance_id'
||' AND rownum = 1'
||' UNION'
||' SELECT sr_level_pk'
||' FROM msd_st_level_values t3'
||' WHERE t3.level_value '
||' = t1.'||p_column_name
||' AND t3.level_id = :p_level_id '
||' AND t3.instance = :p_instance_id '
||' AND NOT EXISTS (SELECT sr_level_pk '
||' FROM msd_level_values t4'
||' WHERE t4.level_value '
||' = t1.'||p_column_name
||' AND t4.level_id = :p_level_id '
||' AND t4.instance = :p_instance_id)'
||' AND rownum = 1),'
|| p_pk_col_name||')' --This change is made to ensure that - not updating the sr_level_pk with any dummy value (like '-23453') when the value for the level value is not provided.
||' WHERE sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND process_flag = '||G_IN_PROCESS ;
'UPDATE '||p_table_name ||' t1'
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||G_ERROR
||' WHERE NVL(t1.'||p_pk_col_name||','||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||G_ERROR
||' WHERE NVL(t1.'||p_pk_col_name||','||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' AND NVL(t1.'||p_column_name||','||''''||NULL_CHAR||''''||') '
||' <> '||''''||NULL_CHAR||''''
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET '||p_level_pk_col
||' = NVL((SELECT sr_level_pk '
||' FROM msd_level_values t2'
||' WHERE t2.level_value '
||' = t1.'||p_level_val_col
||' AND t2.level_id = (SELECT level_id'
||' FROM msd_levels t5 '
||' WHERE t5.level_name '
||' = t1.'||p_level_name_col
||' AND t5.PLAN_TYPE IS NULL )'--bug 4443782
||' AND t2.instance = :p_instance_id'
||' AND rownum = 1'
||' UNION'
||' SELECT sr_level_pk'
||' FROM msd_st_level_values t3'
||' WHERE t3.level_value '
||' = t1.'||p_level_val_col
||' AND t3.level_id = (SELECT level_id'
||' FROM msd_levels t4'
||' WHERE t4.level_name '
||' = t1.'||p_level_name_col
||' AND t4.PLAN_TYPE IS NULL )'--bug 4443782
||' AND t3.instance = :p_instance_id'
||' AND NOT EXISTS ( SELECT sr_level_pk'
||' FROM msd_level_values t2'
||' WHERE t2.level_value '
||' = t1.'||p_level_val_col
||' AND t2.level_id = (SELECT level_id'
||' FROM msd_levels t5 '
||' WHERE t5.level_name '
||' = t1.'||p_level_name_col
||' AND t5.PLAN_TYPE IS NULL )'--bug 4443782
||' AND t2.instance = :p_instance_id )'
||' AND rownum=1 ),'
|| NULL_VALUE||')'
||' WHERE sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND NVL(t1.'||p_level_val_col||','||''''||NULL_CHAR||''''||') '
||' <> '||''''||NULL_CHAR||''''
||' AND process_flag = '||G_IN_PROCESS ;
'UPDATE '||p_table_name ||' t1'
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||G_ERROR
||' WHERE NVL(t1.'||p_level_pk_col||','||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||G_ERROR
||' WHERE NVL(t1.'||p_level_pk_col||','||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' AND NVL(t1.'||p_level_val_col||','||''''||NULL_CHAR||''''||') '
||' <> '||''''||NULL_CHAR||''''
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
SELECT instance_code
FROM msc_apps_instances mai
WHERE mai.default_flag = 'Y'
AND company_id = (SELECT company_id
FROM msc_trading_partners mtp
WHERE mtp.partner_id = p_trading_partner_id);
PROCEDURE retn_delete_flag
(p_syncind IN VARCHAR2,
p_return OUT NOCOPY NUMBER) IS
lv_return number;
SELECT DECODE(NVL(UPPER(p_syncind),'A'),'D',1,2)
INTO p_return
FROM dual;
END retn_delete_flag ;
SELECT DECODE(NVL(UPPER(p_schedule_type),'MDS'),'MDS',1,'MPS',2,'FORECAST',6,
'SUPPLY FORECAST',2,'DEMAND_FORECAST',6,1)
INTO p_schid
FROM dual;
'SELECT CHAR4 '
||' FROM MSC_LOCAL_ID_SETUP '
||' WHERE entity_name = :entity_id'
||' AND local_id = :local_id';
'SELECT CHAR4 '
||' FROM MSC_LOCAL_ID_ITEM '
||' WHERE entity_name = :entity_id'
||' AND local_id = :local_id';
'SELECT CHAR4 '
||' FROM MSC_LOCAL_ID_MISC'
||' WHERE entity_name = :entity_id'
||' AND local_id = :local_id';
'SELECT CHAR5 '
||' FROM MSC_LOCAL_ID_MISC'
||' WHERE entity_name = :entity_id'
||' AND local_id = :local_id';
'SELECT CHAR4 '
||' FROM MSC_LOCAL_ID_SETUP '
||' WHERE entity_name = :entity_id'
||' AND local_id = :local_id';
'SELECT CHAR4 '
||' FROM MSC_LOCAL_ID_SETUP '
||' WHERE entity_name = :entity_id'
||' AND local_id = :local_id';
'SELECT CHAR4 '
||' FROM MSC_LOCAL_ID_SETUP '
||' WHERE entity_name = :entity_id'
||' AND local_id = :local_id';
'SELECT CHAR5 '
||' FROM MSC_LOCAL_ID_SETUP '
||' WHERE entity_name = :entity_id'
||' AND local_id = :local_id';
'SELECT CHAR6 '
||' FROM MSC_LOCAL_ID_SETUP '
||' WHERE entity_name = :entity_id'
||' AND local_id = :local_id';
'SELECT CHAR4 '
||' FROM MSC_LOCAL_ID_SETUP '
||' WHERE entity_name = :entity_id'
||' AND local_id = :local_id';
'SELECT NUMBER1 '
||'FROM MSC_LOCAL_ID_SUPPLY '
||'WHERE entity_name = :entity_id '
||'AND local_id = :local_id ';
'SELECT CHAR5 '
||'FROM MSC_LOCAL_ID_SUPPLY '
||'WHERE entity_name = :entity_id '
||'AND local_id = :local_id ';
'SELECT CHAR4 '
||'FROM MSC_LOCAL_ID_SUPPLY '
||'WHERE entity_name = :entity_id '
||'AND local_id = :local_id ';
'SELECT organization_code '
||'FROM msc_trading_partners mtp '
||'WHERE mtp.sr_instance_id = :instance_id '
||'AND mtp.sr_tp_id = :org_id '
||'AND mtp.partner_type = 3 ';
'SELECT partner_name '
||' FROM msc_trading_partners mtp '
||' WHERE mtp.partner_id = :partner_id ';
'SELECT mtp.partner_name '
||' FROM msc_trading_partners mtp, '
||' msc_tp_id_lid mtp_lid '
||' WHERE mtp.partner_id = mtp_lid.tp_id'
||' AND mtp_lid.sr_instance_id = :instance_id'
||' AND mtp_lid.sr_tp_id = :sr_tp_id '
||' AND mtp_lid.partner_type = :partner_type ';
'SELECT tp_site_code '
||'FROM MSC_TRADING_PARTNER_SITES mtps '
||'WHERE mtps.partner_site_id = :partner_site ';
' SELECT mtps.tp_site_code '
||' FROM MSC_TRADING_PARTNER_SITES mtps,'
||' MSC_TP_SITE_ID_LID mtps_lid'
||' WHERE mtps.partner_site_id = mtps_lid.sr_tp_site_id'
||' AND mtps_lid.sr_instance_id = :instance_id '
||' AND mtps_lid.sr_tp_site_id = :partner_site '
||' AND mtps_lid.partner_type = :partner_type ';
'SELECT designator '
||'FROM msc_designators '
||'WHERE designator_id = :designator_id ';
'SELECT instance_code '
||'FROM msc_apps_instances '
||'WHERE instance_id = :instance_id ';
'SELECT project_number '
||'FROM msc_projects '
||'WHERE sr_instance_id = :instance_id '
||'AND organization_id = :org_id '
||'AND project_id = :project_id '
||'AND plan_id = :plan_id ';
'SELECT task_number '
||' FROM msc_project_tasks '
||' WHERE sr_instance_id = :instance_id '
||' AND organization_id = :org_id '
||' AND project_id = :project_id '
||' AND task_id = :task_id '
||' AND plan_id = :plan_id ';
'SELECT wip_entity_name '
||'FROM msc_supplies '
||'WHERE sr_instance_id = :instance_id '
||'AND organization_id = :org_id '
||'AND disposition_id = :wip_entity_id '
||'AND order_type IN (3,7) '
||'AND plan_id = :plan_id ';
'UPDATE '||p_table_name ||' t1'
||' SET '||p_grp_col_id
||' = (SELECT local_id'
||' FROM msc_local_id_setup t2'
||' WHERE t2.char1 = t1.'||p_grp_col_name
||' AND t2.entity_name = ''GROUP_ID'' )'
||' WHERE sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND process_flag = '||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||g_error
||' WHERE NVL(t1.'||p_grp_col_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||g_error
||' WHERE NVL(t1.'||p_grp_col_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND NVL(t1.'||p_grp_col_name||','||''''||NULL_CHAR||''''||') '
||' <> '||''''||NULL_CHAR||''''
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
'SELECT ITEM_NAME '
||'FROM MSC_SYSTEM_ITEMS '
||'WHERE sr_instance_id = :instance_id '
||'AND organization_id = :org_id '
||'AND sr_inventory_item_id = :item_id '
||'AND plan_id = :plan_id ';
'SELECT DECODE(SUBSTR(UPPER(NVL(:p_yes_no_code,''N'')),1,1),''Y'',1,2)'
||' FROM dual';
select COUNT(*) into lv_cnt
FROM FND_USER_RESP_GROUPS
WHERE user_id IN (select USER_ID from MSC_COMPANY_USERS where user_id = p_user_id AND company_id = 1)
AND responsibility_id = (SELECT responsibility_id FROM FND_RESPONSIBILITY WHERE responsibility_key = 'MSCX_SC_ADMIN_FULL')
AND SYSDATE between start_date and decode(end_date,NULL,SYSDATE) ;
insert into msd_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 )
values(
p_instance_id,
p_calendar_type_id,
p_calendar_code,
v_seq_num,
p_year,
p_year_description,
p_year_start_date,
p_year_end_date,
p_quarter,
p_quarter_description,
p_quarter_start_date,
p_quarter_end_date,
p_month,
p_month_description,
p_month_start_date,
p_month_end_date,
p_week,
p_week_description,
p_week_start_date,
p_week_end_date,
p_week_start_date + lv_num_of_days,
p_week_start_date + lv_num_of_days,
sysdate,
FND_GLOBAL.USER_ID ,
sysdate,
FND_GLOBAL.USER_ID ,
FND_GLOBAL.USER_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 )
values(
p_instance_id,
p_calendar_type_id,
p_calendar_code,
v_seq_num,
p_year,
p_year_description,
p_year_start_date,
p_year_end_date,
p_quarter,
p_quarter_description,
p_quarter_start_date,
p_quarter_end_date,
p_month,
p_month_description,
p_month_start_date,
p_month_end_date,
p_week,
p_week_description,
p_week_start_date,
p_week_end_date,
p_week_start_date + lv_num_of_days,
p_week_start_date + lv_num_of_days,
sysdate,
FND_GLOBAL.USER_ID ,
sysdate,
FND_GLOBAL.USER_ID ,
FND_GLOBAL.USER_ID
) ;
'UPDATE '||p_table_name ||' t1'
||' SET '||p_level_pk_col
||' = (SELECT sr_inventory_item_id '
||' FROM msc_system_items t2'
||' WHERE t2.sr_instance_id = :p_instance_id'
||' AND t2.item_name = t1.'||p_level_val_col
||' AND t2.plan_id = -1 '
||' AND t2.bom_item_type <> 5 '
||' AND rownum = 1 )'
||' WHERE sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND NVL(t1.'||p_level_pk_col||','||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' AND level_id = 1 '
||' AND process_flag = '||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET '||p_level_pk_col
||' = (SELECT sr_inventory_item_id '
||' FROM msc_system_items t2'
||' WHERE t2.sr_instance_id = :p_instance_id'
||' AND t2.item_name = t1.'||p_level_val_col
||' AND t2.plan_id = -1 '
||' AND t2.bom_item_type = 5 '
||' AND rownum = 1 )'
||' WHERE sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND NVL(t1.'||p_level_pk_col||','||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' AND level_id = 3 '
||' AND process_flag = '||G_IN_PROCESS ;
'UPDATE '||p_table_name ||' t1'
||' SET '||p_level_pk_col
||' = (SELECT sr_category_id '
||' FROM msc_item_categories t2'
||' WHERE t2.sr_instance_id = :p_instance_id'
||' AND t2.category_name = t1.'||p_level_val_col
||' AND t2.category_set_id = ( select mcs.category_set_id '
||' from msc_category_sets mcs, msd_setup_parameters msp '
||' where msp.parameter_name = ''MSD_CATEGORY_SET_NAME_LEGACY'' '
||' and msp.instance_id = :p_instance_id '
||' and msp.parameter_value = mcs.category_set_name ) '
/* ||' AND t2.category_set_id = ( select parameter_value '
||' from msd_setup_parameters '
||' where instance_id = :p_instance_id '
||' and parameter_name = ''MSD_CATEGORY_SET_NAME'' )' */
||' AND rownum = 1 )'
||' WHERE sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND NVL(t1.'||p_level_pk_col||','||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' AND level_id = 2 '
||' AND process_flag = '||G_IN_PROCESS ;
'UPDATE '||p_table_name ||' t1'
||' SET '||p_level_pk_col
||' = (SELECT sr_tp_id '
||' FROM msc_trading_partners t2 '
||' WHERE t2.sr_instance_id = :p_instance_id '
||' AND substr(t2.partner_name,instr(t2.partner_name,'':'')+1,length(t2.partner_name)) = t1.'||p_level_val_col
||' AND t2.partner_type = 3 '
||' AND nvl(t2.company_id,-1) = -1 '
||' AND rownum = 1 ) '
||' WHERE sr_instance_code = :p_instance_code '
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND NVL(t1.'||p_level_pk_col||','||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' AND level_id = 7 '
||' AND process_flag = '||G_IN_PROCESS ;
'UPDATE '||p_table_name ||' t1'
||' SET '||p_level_pk_col
||' = (SELECT t2.sr_tp_site_id '
||' FROM msc_tp_site_id_lid t2,msc_trading_partner_sites t3,msc_trading_partners t4 '
||' WHERE t4.partner_type = 2 '
||' AND t4.partner_name = substr(t1.'||p_level_val_col||',1,instr(t1.'||p_level_val_col||','':'')-1) '
||' AND nvl(t4.company_id,-1) = -1 '
||' AND t3.partner_id = t4.partner_id '
||' AND t3.location = substr(t1.'||p_level_val_col||',instr(t1.'||p_level_val_col||','':'')+1,length(t1.'||p_level_val_col||')) '
||' AND t3.tp_site_code = ''SHIP_TO'' '
||' AND t2.tp_site_id = t3.partner_site_id '
||' AND t2.partner_type = 2 '
||' AND t2.sr_instance_id = :p_instance_id'
||' AND nvl(t2.sr_company_id,-1) = -1 '
||' AND rownum = 1 )'
||' WHERE sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND NVL(t1.'||p_level_pk_col||','||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' AND level_id = 11 '
||' AND process_flag = '||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET '||p_level_pk_col
||' = (SELECT t2.sr_tp_id '
||' FROM msc_tp_id_lid t2,msc_trading_partners t3'
||' WHERE t2.sr_instance_id = :p_instance_id'
||' AND t3.partner_name = t1.'||p_level_val_col
||' AND t3.partner_type = 2 '
||' AND nvl(t3.company_id,-1) = -1 '
||' AND t2.partner_type = 2 '
||' AND t2.tp_id = t3.partner_id '
||' AND nvl(t2.sr_company_id,-1) = -1 '
||' AND rownum = 1 )'
||' WHERE sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND NVL(t1.'||p_level_pk_col||','||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' AND level_id = 15 '
||' AND process_flag = '||G_IN_PROCESS ;
'UPDATE '||p_table_name ||' t1'
||' SET '||p_level_pk_col
||' = (SELECT demand_class '
||' FROM msc_demand_classes t2'
||' WHERE t2.sr_instance_id = :p_instance_id'
||' AND t2.meaning = t1.'||p_level_val_col
||' AND rownum = 1 )'
||' WHERE sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND NVL(t1.'||p_level_pk_col||','||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' AND level_id = 34 '
||' AND process_flag = '||G_IN_PROCESS ;
'UPDATE '||p_table_name ||' t1'
||' SET '||p_level_pk_col
||' = (SELECT t2.region_id '
||' FROM msc_regions t2'
||' WHERE t2.sr_instance_id = :p_instance_id'
||' AND t2.zone = t1.'||p_level_val_col
||' AND t2.region_type = 10'
||' AND rownum = 1 )'
||' WHERE sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND NVL(t1.'||p_level_pk_col||','||''''||NULL_CHAR||''''||') '
||' = '||''''||NULL_CHAR||''''
||' AND level_id = 42 '
||' AND process_flag = '||G_IN_PROCESS ;
'UPDATE '|| p_table_name ||' msrr'
||' SET '||p_resource_col_id || '= (SELECT local_id '
||' FROM msc_local_id_setup mlis'
||' WHERE mlis.char1 = msrr.sr_instance_code'
||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
||' = NVL(msrr.company_name,'||''''||NULL_CHAR||''''||') '
||' AND mlis.char3 = msrr.organization_code'
||' AND mlis.char4 = msrr.'||p_resource_col_name
||' AND mlis.entity_name = ''RESOURCE_ID'''
||' AND mlis.instance_id ='||v_instance_id||' )'
||' WHERE msrr.sr_instance_code = :p_instance_code'
||' AND msrr.deleted_flag = '||SYS_NO
||' AND msrr.process_flag ='|| G_IN_PROCESS
||' AND msrr.batch_id = :p_batch_id'
|| p_where_str;
'update '|| p_table_name ||' msrr'
||' set '||p_resource_col_id || '= (select RESOURCE_ID '
||' from msc_department_resources mdr '
||' where mdr.ORGANIZATION_ID = msrr.ORGANIZATION_ID and '
||' mdr.SR_INSTANCE_ID = '||v_instance_id||' and '
||' mdr.department_code = msrr.'||p_department_col_name||' and '
||' mdr.RESOURCE_CODE = msrr.'||p_resource_col_name||' and mdr.plan_id = -1 and rownum = 1 )'
||' WHERE msrr.sr_instance_code = :p_instance_code'
||' AND msrr.deleted_flag = '||SYS_NO
||' AND msrr.process_flag ='|| G_IN_PROCESS
||' AND msrr.batch_id = :p_batch_id'
|| p_where_str;
'UPDATE '||p_table_name ||' t1'
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||g_error
||' WHERE NVL(t1.'||p_resource_col_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||g_error
||' WHERE NVL(t1.'||p_resource_col_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND NVL(t1.'||p_resource_col_name||','||''''||NULL_CHAR||''''||') '
||' <> '||''''||NULL_CHAR||''''
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
'UPDATE '|| p_table_name ||' msrr'
||' SET '||p_department_col_id || '= (SELECT local_id '
||' FROM msc_local_id_setup mlis'
||' WHERE mlis.char1 = msrr.sr_instance_code'
||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
||' = NVL(msrr.company_name,'||''''||NULL_CHAR||''''||') '
||' AND mlis.char3 = msrr.organization_code'
||' AND mlis.char4 = msrr.'||p_department_col_name
||' AND mlis.entity_name = ''DEPARTMENT_ID'''
||' AND mlis.instance_id ='||v_instance_id||' )'
||' WHERE msrr.sr_instance_code = :p_instance_code'
||' AND msrr.process_flag ='|| G_IN_PROCESS
||' AND NVL(msrr.'||p_department_col_id||','||NULL_VALUE||') <> -1'
||' AND msrr.batch_id = :p_batch_id';
'update '|| p_table_name ||' msrr'
||' set '||p_department_col_id || '= (select department_ID '
||' from msc_department_resources mdr '
||' where mdr.ORGANIZATION_ID = msrr.ORGANIZATION_ID and '
||' mdr.SR_INSTANCE_ID = '||v_instance_id||' and '
||' mdr.department_code = msrr.'||p_department_col_name||' and '
||' mdr.RESOURCE_CODE = msrr.'||p_resource_col_name||' and mdr.plan_id = -1 and rownum = 1 )'
||' WHERE msrr.sr_instance_code = :p_instance_code'
||' AND msrr.process_flag ='|| G_IN_PROCESS
||' AND NVL(msrr.'||p_department_col_id||','||NULL_VALUE||') <> -1'
||' AND msrr.batch_id = :p_batch_id';
'UPDATE '||p_table_name ||' t1'
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||g_error
||' WHERE NVL(t1.'||p_department_col_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||g_error
||' WHERE NVL(t1.'||p_department_col_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND NVL(t1.'||p_department_col_name||','||''''||NULL_CHAR||''''||') '
||' <> '||''''||NULL_CHAR||''''
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
'update '||p_table_name||' msrr'
||' set ' ||p_operation_seq_num||' = to_number(decode(length(rtrim('||p_operation_seq_code||',''0123456789'')),'
||' NULL,'||p_operation_seq_code||',''1'')),'
||p_sequence_col_id||' = (select operation_sequence_id '
||' from msc_routing_operations mro '
||' where mro.routing_sequence_id = msrr.'||p_routing_sequence_id||' and '
||' mro.effectivity_date = msrr.'||p_op_effectivity_date||' and '
||' mro.operation_seq_num = to_number(decode(length(rtrim(msrr.'||p_operation_seq_code||',''0123456789'')),'
||' NULL,msrr.'||p_operation_seq_code||',''1'')) and'
||' mro.SR_INSTANCE_ID = '||v_instance_id ||' and mro.plan_id = -1 and mro.operation_type = 1)'
||' WHERE sr_instance_code = :p_instance_code'
||' AND process_flag ='||G_IN_PROCESS
||' AND batch_id = :p_batch_id'
||' AND msrr.'||p_sequence_col_id||' IS NULL';
'UPDATE '||p_table_name||' msrr'
||' SET '||p_sequence_col_id||'= (SELECT local_id'
||' FROM msc_local_id_setup mlis'
||' WHERE mlis.char1 = msrr.sr_instance_code'
||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
||' = NVL(msrr.company_name,'||''''||NULL_CHAR||''''||') '
||' AND mlis.char3 = msrr.organization_code'
||' AND mlis.char4 = msrr.'||p_routing_col_name
||' AND mlis.char5 = msrr.'||p_operation_seq_code
||' AND NVL(mlis.char6,'||''''||NULL_CHAR||''''||') '
||' = NVL(msrr.alternate_routing_designator,'||''''||NULL_CHAR||''''||')'
||' AND mlis.date1 = msrr.'||p_op_effectivity_date
||' AND mlis.entity_name = ''OPERATION_SEQUENCE_ID'' '
||' AND mlis.instance_id = '||v_instance_id||')'
||' WHERE sr_instance_code = :p_instance_code'
||' AND process_flag ='||G_IN_PROCESS
||' AND batch_id = :lp_batch_id';
'UPDATE '||p_table_name ||' t1'
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||g_error
||' WHERE NVL(t1.'||p_sequence_col_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
'UPDATE '||p_table_name ||' t1'
||' SET error_text = '||''''||p_message_text||''''||','
||' process_flag = '||g_error
||' WHERE NVL(t1.'||p_sequence_col_id||','||NULL_VALUE||') = '||NULL_VALUE
||' AND NVL(t1.'||p_operation_seq_code||','||''''||NULL_CHAR||''''||') '
||' <> '||''''||NULL_CHAR||''''
||' AND sr_instance_code = :p_instance_code'
||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
||' AND process_flag = ' ||G_IN_PROCESS;
select decode(upper(x),'TOTAL_BACKLOG','MSD_TOTAL_BACKLOG','PASTDUE_BACKLOG','MSD_PASTDUE_BACKLOG'
,'PRODUCTION_PLAN','MSD_PRODUCTION_PLAN','ACTUAL_PRODUCTION','MSD_ACTUAL_PRODUCTION','ONHAND_INVENTORY','MSD_ONHAND_INVENTORY'
,'SUPPLY_PLANS','MSD_SUPPLY_PLANS','CONSTRAINED_FORECAST','MSD_CONSTRAINED_FORECAST','SAFETY_STOCKS','MSD_SAFETY_STOCKS'
,'AVAIL_RESOURCE_CAPACITY','MSD_AVAIL_RESOURCE_CAPACITY','AVAIL_SUPPLIER_CAPACITY','MSD_AVAIL_SUPPLIER_CAPACITY'
,'WORK_IN_PROCESS','MSD_WORK_IN_PROCESS',x) into stream_name from dual;