The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rowid
FROM msc_st_supplies
WHERE process_flag IN (MSC_CL_PRE_PROCESS.G_IN_PROCESS)
AND batch_id = p_batch_id
AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code;
SELECT rowid
FROM msc_st_supplies
WHERE NVL(disposition_id,MSC_CL_PRE_PROCESS.NULL_VALUE) = MSC_CL_PRE_PROCESS.NULL_VALUE
AND order_type =75
AND deleted_flag = MSC_CL_PRE_PROCESS.SYS_NO
AND process_flag = MSC_CL_PRE_PROCESS.G_IN_PROCESS
AND NVL(batch_id,MSC_CL_PRE_PROCESS.NULL_VALUE) =p_batch_id
AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code;
' UPDATE msc_st_supplies mss1'
||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE message_id < (SELECT MAX(message_id)'
||' FROM msc_st_supplies mss2'
||' WHERE mss2.sr_instance_code = mss1.sr_instance_code'
||' AND mss2.repair_number = mss1.repair_number '
||' AND mss2.order_type = mss1.order_type'
||' AND mss2.organization_code = mss1.organization_code'
||' AND mss2.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(mss2.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') ='||MSC_CL_PRE_PROCESS.NULL_VALUE||')'
||' AND mss1.order_type IN (75)'
||' AND mss1.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND mss1.sr_instance_code = :v_instance_code'
||' AND NVL(mss1.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE;
'UPDATE msc_st_supplies mss1 '
||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE EXISTS( SELECT 1 '
||' FROM msc_st_supplies mss2'
||' WHERE mss2.sr_instance_code = mss1.sr_instance_code'
||' AND mss2.repair_number = mss1.repair_number '
||' AND mss2.order_type = mss1.order_type'
||' AND mss2.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND mss2.organization_code = mss1.organization_code'
||' AND NVL(mss2.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
||' GROUP BY sr_instance_code,repair_number,organization_code, order_type '
||' HAVING COUNT(*) > 1)'
||' AND mss1.order_type IN (75)'
||' AND mss1.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND mss1.sr_instance_code = :v_instance_code'
||' AND NVL(mss1.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE;
||'DELETED_FLAG ||''~''||'
||'UOM_CODE ||''~''||'
||'CUSTOMER_PRODUCT_ID ||''~''||'
||'SR_REPAIR_TYPE_ID ||''~''||'
||'RO_STATUS_CODE ||''~''||'
||'ASSET_SERIAL_NUMBER ||''~''||'
||'SR_REPAIR_GROUP_ID ||''~''||'
||'SCHEDULE_PRIORITY ||''~''||'
||'RO_CREATION_DATE ||''~''||'
||'REPAIR_LEAD_TIME';
SELECT msc_st_batch_id_s.NEXTVAL
INTO lv_batch_id
FROM dual;
' UPDATE msc_st_supplies '
||' SET batch_id = :lv_batch_id'
||' WHERE process_flag IN ('||MSC_CL_PRE_PROCESS.G_IN_PROCESS||')'
||' AND order_type IN (75)'
||' AND sr_instance_code = :v_instance_code'
||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
||' AND rownum <= '||MSC_CL_PRE_PROCESS.v_batch_size;
UPDATE msc_st_supplies
SET st_transaction_id = msc_st_supplies_s.NEXTVAL,
refresh_id = MSC_CL_PRE_PROCESS.v_refresh_id,
last_update_date = MSC_CL_PRE_PROCESS.v_current_date,
last_updated_by = MSC_CL_PRE_PROCESS.v_current_user,
creation_date = MSC_CL_PRE_PROCESS.v_current_date,
created_by = MSC_CL_PRE_PROCESS.v_current_user
WHERE rowid = lb_rowid(j);
p_token_value1 => 'DELETED_FLAG',
p_token2 => 'DEFAULT_VALUE',
p_token_value2 => MSC_CL_PRE_PROCESS.SYS_NO);
' AND NVL(deleted_flag,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') '
||' NOT IN(1,2)';
p_col_name => 'DELETED_FLAG',
p_debug => MSC_CL_PRE_PROCESS.v_debug,
p_default_value => MSC_CL_PRE_PROCESS.SYS_NO);
'UPDATE msc_st_supplies '
||' SET error_text = '||''''||lv_message_text||''''||','
||' process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG
||' WHERE new_order_quantity is null '
||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = :lv_batch_id'
||' AND sr_instance_code = :v_instance_code';
'UPDATE msc_st_supplies '
||' SET error_text = '||''''||lv_message_text||''''||','
||' process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG
||' WHERE new_schedule_date is null'
||' AND ro_creation_date is null'
||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = :lv_batch_id'
||' AND sr_instance_code = :v_instance_code';
'UPDATE msc_st_supplies '
||' SET error_text = '||''''||lv_message_text||''''||','
||' process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG
||' WHERE repair_number is NULL'
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id'
||' AND sr_instance_code = :v_instance_code';
'UPDATE msc_st_supplies mss'
||' SET disposition_id = (SELECT local_id'
||' FROM msc_local_id_supply mls'
||' WHERE mls.char4 = mss.repair_number'
||' AND mls.char3 = mss.organization_code'
||' AND mls.char1 = mss.sr_instance_code'
||' AND mls.entity_name = ''REPAIR_NUMBER'' )'
||' WHERE process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = :lv_batch_id'
||' AND sr_instance_code = :v_instance_code';
p_error_code => 'MSC_PP_DELETE_FAIL',
p_message_text => lv_message_text,
p_error_text => lv_error_text);
'UPDATE msc_st_supplies '
||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE disposition_id is null '
||' AND deleted_flag ='||MSC_CL_PRE_PROCESS.SYS_YES
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id '
||' AND sr_instance_code =:v_instance_code';
' UPDATE MSC_ST_SUPPLIES mic'
||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE NOT EXISTS (SELECT 1 '
||' FROM msc_units_of_measure muom'
||' WHERE muom.uom_code = mic.uom_code'
||' UNION'
||' SELECT 1 FROM msc_st_units_of_measure msuom'
||' WHERE msuom.uom_code = mic.uom_code'
||' AND msuom.sr_instance_id = :v_instance_id'
||' AND msuom.process_flag = '||MSC_CL_PRE_PROCESS.G_VALID||')'
||' AND mic.sr_instance_code = :v_instance_code'
||' AND mic.batch_id = :lv_batch_id'
||' AND mic.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS ;
UPDATE msc_st_supplies
SET disposition_id = msc_st_iro_supply_s.NEXTVAL
WHERE rowid = lb_rowid(j);
INSERT INTO msc_local_id_supply
(local_id,
st_transaction_id,
instance_id,
entity_name,
data_source_type,
char1,
char3,
char4,
SOURCE_ORG_ID,
SOURCE_INVENTORY_ITEM_ID,
SOURCE_BILL_SEQUENCE_ID,
SOURCE_ROUTING_SEQUENCE_ID,
SOURCE_SCHEDULE_GROUP_ID,
SOURCE_WIP_ENTITY_ID,
last_update_date,
last_updated_by,
creation_date,
created_by)
SELECT
disposition_id,
st_transaction_id,
MSC_CL_PRE_PROCESS.v_instance_id,
'REPAIR_NUMBER',
data_source_type,
MSC_CL_PRE_PROCESS.v_instance_code,
organization_code ,
REPAIR_NUMBER,
SOURCE_ORG_ID,
SOURCE_INVENTORY_ITEM_ID,
SOURCE_BILL_SEQUENCE_ID,
SOURCE_ROUTING_SEQUENCE_ID,
SOURCE_SCHEDULE_GROUP_ID,
SOURCE_WIP_ENTITY_ID,
MSC_CL_PRE_PROCESS.v_current_date,
MSC_CL_PRE_PROCESS.v_current_user,
MSC_CL_PRE_PROCESS.v_current_date,
MSC_CL_PRE_PROCESS.v_current_user
FROM msc_st_supplies
WHERE rowid = lb_rowid(j);
SELECT rowid
FROM msc_st_demands
WHERE process_flag IN (MSC_CL_PRE_PROCESS.G_IN_PROCESS)
AND batch_id = p_batch_id
AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code;
'UPDATE msc_st_demands msd1 '
||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE EXISTS( SELECT 1 '
||' FROM msc_st_demands msd2'
||' WHERE msd2.sr_instance_code = msd1.sr_instance_code'
||' AND msd2.organization_code = msd1.organization_code'
||' AND msd2.wip_entity_name = msd1.wip_entity_name'
||' AND msd2.repair_number = msd1.repair_number'
||' AND msd2.operation_seq_num = msd1.operation_seq_num '
||' AND msd2.item_name = msd1.item_name '
||' AND msd2.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(msd2.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
||' GROUP BY sr_instance_code,organization_code,wip_entity_name,'
||' operation_seq_num,item_name,repair_number'
||' HAVING COUNT(*) > 1)'
||' AND msd1.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND msd1.origination_type =77'
||' AND msd1.ENTITY =''IRO'''
||' AND msd1.sr_instance_code = :v_instance_code'
||' AND msd1.message_id IS NULL';
||' DELETED_FLAG ||''~''||'
||' RO_STATUS_CODE ||''~''||'
||' QUANTITY_ISSUED ||''~''||'
||' COMPONENT_SCALING_TYPE ||''~''||'
||' COMPONENT_YIELD_FACTOR ||''~''||'
||' ITEM_TYPE_VALUE ' ;
SELECT msc_st_batch_id_s.NEXTVAL
INTO lv_batch_id
FROM dual;
' UPDATE msc_st_demands '
||' SET batch_id = :lv_batch_id'
||' WHERE process_flag IN ('||MSC_CL_PRE_PROCESS.G_IN_PROCESS||','||MSC_CL_PRE_PROCESS.G_ERROR_FLG||')'
||' AND sr_instance_code = :v_instance_code'
||' AND origination_type IN (77) '
||' AND ENTITY = ''IRO'' '
||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
||' AND rownum <= '||MSC_CL_PRE_PROCESS.v_batch_size;
UPDATE msc_st_demands
SET st_transaction_id = msc_st_demands_s.NEXTVAL,
refresh_id = MSC_CL_PRE_PROCESS.v_refresh_id,
last_update_date = MSC_CL_PRE_PROCESS.v_current_date,
last_updated_by = MSC_CL_PRE_PROCESS.v_current_user,
creation_date = MSC_CL_PRE_PROCESS.v_current_date,
created_by = MSC_CL_PRE_PROCESS.v_current_user
WHERE rowid = lb_rowid(j);
p_token_value1 => 'DELETED_FLAG',
p_token2 => 'DEFAULT_VALUE',
p_token_value2 => MSC_CL_PRE_PROCESS.SYS_NO);
' AND NVL(deleted_flag,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') '
||' NOT IN(1,2)';
p_col_name => 'DELETED_FLAG',
p_debug => MSC_CL_PRE_PROCESS.v_debug,
p_default_value => MSC_CL_PRE_PROCESS.SYS_NO);
'UPDATE msc_st_demands '
||' SET error_text = '||''''||lv_message_text||''''||','
||' process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG
||' WHERE (using_requirement_quantity IS NULL ' ||' OR using_assembly_demand_date IS NULL)'
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND origination_type IN (77)'
||' AND ENTITY = ''IRO'''
||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = :lv_batch_id'
||' AND sr_instance_code = :v_instance_code';
'UPDATE msc_st_demands '
||' SET error_text = '||''''||lv_message_text||''''||','
||' process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG
||' WHERE repair_number is NULL'
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id'
||' AND sr_instance_code = :v_instance_code';
'UPDATE msc_st_demands msd'
||' SET repair_line_id = (SELECT local_id'
||' FROM msc_local_id_supply mls'
||' WHERE mls.char4 = msd.wip_entity_name'
||' AND mls.char3 = msd.organization_code'
||' AND mls.char1 = msd.sr_instance_code'
||' AND mls.entity_name = ''REPAIR_NUMBER'' )'
||' WHERE process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = :lv_batch_id'
||' AND sr_instance_code = :v_instance_code';
p_error_code => 'MSC_PP_DELETE_FAIL',
p_message_text => lv_message_text,
p_error_text => lv_error_text);
'UPDATE msc_st_demands '
||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE repair_line_id is null '
||' AND deleted_flag ='||MSC_CL_PRE_PROCESS.SYS_YES
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id '
||' AND sr_instance_code =:v_instance_code';
'UPDATE msc_st_demands '
||' SET error_text = '||''''||lv_message_text||''''||','
||' process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG
||' WHERE nvl(ITEM_TYPE_VALUE,-1) not in (1,2)'
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id'
||' AND sr_instance_code = :v_instance_code';
SELECT rowid
FROM msc_st_supplies
WHERE order_type =86
AND process_flag IN (MSC_CL_PRE_PROCESS.G_IN_PROCESS,MSC_CL_PRE_PROCESS.G_ERROR_FLG)
AND NVL(batch_id,MSC_CL_PRE_PROCESS.NULL_VALUE)=p_batch_id
AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code;
SELECT rowid
FROM msc_st_supplies
WHERE NVL(wip_entity_id,MSC_CL_PRE_PROCESS.NULL_VALUE) = MSC_CL_PRE_PROCESS.NULL_VALUE
AND process_flag = MSC_CL_PRE_PROCESS.G_IN_PROCESS
AND NVL(batch_id,MSC_CL_PRE_PROCESS.NULL_VALUE) =p_batch_id
AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code;
SELECT max(rowid)
FROM msc_st_supplies
WHERE NVL(schedule_group_id,MSC_CL_PRE_PROCESS.NULL_VALUE) = MSC_CL_PRE_PROCESS.NULL_VALUE
AND deleted_flag = MSC_CL_PRE_PROCESS.SYS_NO
AND process_flag = MSC_CL_PRE_PROCESS.G_IN_PROCESS
AND NVL(batch_id,MSC_CL_PRE_PROCESS.NULL_VALUE) = p_batch_id
AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code
GROUP BY sr_instance_code,company_name,organization_code,schedule_group_name;
SELECT rowid
FROM msc_st_supplies
WHERE process_flag = MSC_CL_PRE_PROCESS.G_IN_PROCESS
AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code
AND batch_id = p_batch_id
AND NVL(JOB_OP_SEQ_NUM, MSC_CL_PRE_PROCESS.NULL_VALUE) = MSC_CL_PRE_PROCESS.NULL_VALUE
AND NVL(JOB_OP_SEQ_CODE, MSC_CL_PRE_PROCESS.NULL_CHAR) <> MSC_CL_PRE_PROCESS.NULL_CHAR
AND order_type = 86
AND deleted_flag = MSC_CL_PRE_PROCESS.SYS_NO;
'UPDATE msc_st_supplies mss1'
||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE message_id < (SELECT MAX(message_id)'
||' FROM msc_st_supplies mss2'
||' WHERE mss2.sr_instance_code'
||' = mss1.sr_instance_code'
||' AND NVL(mss2.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
||' NVL(mss1.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||')'
||' AND mss2.wip_entity_name = mss1.wip_entity_name '
||' AND mss2.order_type = mss1.order_type'
||' AND mss2.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND mss2.organization_code = mss1.organization_code'
||' AND NVL(mss2.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')<>'||MSC_CL_PRE_PROCESS.NULL_VALUE||')'
||' AND mss1.order_type =86'
||' AND mss1.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND mss1.sr_instance_code = :v_instance_code'
||' AND NVL(mss1.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') <> '||MSC_CL_PRE_PROCESS.NULL_VALUE;
'UPDATE msc_st_supplies mss1 '
||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE EXISTS( SELECT 1 '
||' FROM msc_st_supplies mss2'
||' WHERE mss2.sr_instance_code'
||' = mss1.sr_instance_code'
||' AND NVL(mss2.company_name, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||')= '
||' NVL(mss1.company_name, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||')'
||' AND mss2.wip_entity_name = mss1.wip_entity_name '
||' AND mss2.order_type = mss1.order_type'
||' AND mss2.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND mss2.organization_code = mss1.organization_code'
||' AND NVL(mss2.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
||' GROUP BY sr_instance_code,wip_entity_name,organization_code,company_name,'
||' order_type'
||' HAVING COUNT(*) > 1)'
||' AND mss1.order_type =86'
||' AND mss1.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND mss1.sr_instance_code = :v_instance_code'
||' AND NVL(mss1.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE;
||'DELETED_FLAG ||''~''||'
||'COMPANY_NAME ||''~''||'
||'ORDER_TYPE ||''~''||'
||'ORDER_NUMBER';
SELECT msc_st_batch_id_s.NEXTVAL
INTO lv_batch_id
FROM dual;
' UPDATE msc_st_supplies '
||' SET batch_id = :lv_batch_id'
||' WHERE process_flag IN ('||MSC_CL_PRE_PROCESS.G_IN_PROCESS||','||MSC_CL_PRE_PROCESS.G_ERROR_FLG||')'
||' AND order_type =86'
||' AND sr_instance_code = :v_instance_code'
||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
||' AND rownum <= '||MSC_CL_PRE_PROCESS.v_batch_size;
UPDATE msc_st_supplies
SET st_transaction_id = msc_st_supplies_s.NEXTVAL,
refresh_id = MSC_CL_PRE_PROCESS.v_refresh_id,
last_update_date = MSC_CL_PRE_PROCESS.v_current_date,
last_updated_by = MSC_CL_PRE_PROCESS.v_current_user,
creation_date = MSC_CL_PRE_PROCESS.v_current_date,
created_by = MSC_CL_PRE_PROCESS.v_current_user
WHERE rowid = lb_rowid(j);
p_token_value1 => 'DELETED_FLAG',
p_token2 => 'DEFAULT_VALUE',
p_token_value2 => MSC_CL_PRE_PROCESS.SYS_NO);
' AND NVL(deleted_flag,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') NOT IN(1,2)';
p_col_name => 'DELETED_FLAG',
p_debug => MSC_CL_PRE_PROCESS.v_debug,
p_default_value => MSC_CL_PRE_PROCESS.SYS_NO);
'UPDATE msc_st_supplies '
||' SET error_text = '||''''||lv_message_text||''''||','
||' process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG
||' WHERE (NVL(new_schedule_date,sysdate-36500) = sysdate-36500'
||' OR NVL(new_order_quantity,'||MSC_CL_PRE_PROCESS.NULL_VALUE|| ')= '||MSC_CL_PRE_PROCESS.NULL_VALUE||')'
||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = :lv_batch_id'
||' AND sr_instance_code = :v_instance_code';
'UPDATE msc_st_supplies '
||' SET error_text = '||''''||lv_message_text||''''||','
||' process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG
||' WHERE NVL(wip_entity_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' = '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id'
||' AND sr_instance_code = :v_instance_code';
||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO;
||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO;
||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO;
||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO;
'UPDATE msc_st_supplies'
||' SET order_number = wip_entity_name'
||' WHERE NVL(order_number,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||')'
||' = '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''
||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = :lv_batch_id'
||' AND sr_instance_code = :v_instance_code';
'UPDATE msc_st_supplies '
||' SET bill_name = item_name'
||' WHERE sr_instance_code = :v_instance_code'
||' AND order_type =86'
||' AND process_flag ='||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(bill_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' = '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''
||' AND NVL(item_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' <> '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''
||' AND batch_id = :lv_batch_id';
'UPDATE msc_st_supplies '
||' SET routing_name = item_name'
||' WHERE sr_instance_code = :v_instance_code'
||' AND order_type =86'
||' AND process_flag ='|| MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(routing_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' = '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''
||' AND NVL(item_name, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' <> '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''
||' AND batch_id = :lv_batch_id ';
'UPDATE msc_st_supplies mss'
||' SET schedule_group_id = (SELECT local_id'
||' FROM msc_local_id_supply mls'
||' WHERE mls.char4 = mss.schedule_group_name'
||' AND mls.char3 = mss.organization_code'
||' AND NVL(mls.char2, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
||' NVL(mss.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' AND mls.char1 = mss.sr_instance_code'
||' AND mls.entity_name = ''SCHEDULE_GROUP_ID'' ),'
||' line_id = (SELECT local_id'
||' FROM msc_local_id_setup mls'
||' WHERE mls.char4 = mss.line_code'
||' AND mls.char3 = mss.organization_code'
||' AND NVL(mls.char2, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
||' NVL(mss.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' AND mls.char1 = mss.sr_instance_code'
||' AND mls.entity_name = ''LINE_ID''),'
||' operation_seq_num = (SELECT number1'
||' FROM msc_local_id_setup mls'
||' WHERE mls.char5 = mss.operation_seq_code'
||' AND mls.char4 = mss.routing_name'
||' AND NVL(mls.char6, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
||' NVL(mss.alternate_routing_designator,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' AND mls.char3 = mss.organization_code'
||' AND mls.date1 = mss.effectivity_date'
||' AND NVL(mls.char2, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
||' NVL(mss.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' AND mls.char1 = mss.sr_instance_code'
||' AND mls.entity_name = ''OPERATION_SEQUENCE_ID'' )'
||' WHERE deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id'
||' AND sr_instance_code = :v_instance_code';
||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO;
'UPDATE msc_st_supplies mss'
||' SET wip_entity_id = (SELECT local_id'
||' FROM msc_local_id_supply mls'
||' WHERE mls.char4 = mss.wip_entity_name'
||' AND mls.char3 = mss.organization_code'
||' AND NVL(mls.char2, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
||' NVL(mss.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' AND mls.char1 = mss.sr_instance_code'
||' AND mls.entity_name = ''WIP_ENTITY_ID'' )'
||' WHERE process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = :lv_batch_id'
||' AND sr_instance_code = :v_instance_code';
p_error_code => 'MSC_PP_DELETE_FAIL',
p_message_text => lv_message_text,
p_error_text => lv_error_text);
'UPDATE msc_st_supplies '
||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE NVL(wip_entity_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
||' AND deleted_flag ='||MSC_CL_PRE_PROCESS.SYS_YES
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id '
||' AND sr_instance_code =:v_instance_code';
'UPDATE msc_st_supplies '
||' SET jump_op_seq_num = 50000'
||' WHERE NVL(jump_op_seq_code,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' = '||''''||50000||''''
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id '
||' AND order_type =86'
||' AND sr_instance_code =:v_instance_code';
'UPDATE msc_st_supplies mss'
||' SET jump_op_seq_num = (SELECT number1'
||' FROM msc_local_id_setup mls'
||' WHERE NVL(mls.char5,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
||' NVL(mss.jump_op_seq_code,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' AND mls.char4 = mss.routing_name'
||' AND NVL(mls.char6, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
||' NVL(mss.alternate_routing_designator,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' AND mls.char3 = mss.organization_code'
||' AND mls.date1 = mss.jump_op_effectivity_date'
||' AND NVL(mls.char2, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
||' NVL(mss.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' AND mls.char1 = mss.sr_instance_code'
||' AND mls.entity_name = ''OPERATION_SEQUENCE_ID'' )'
||' WHERE deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND jump_op_seq_num <> 50000 '
||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id'
||' AND sr_instance_code = :v_instance_code';
UPDATE msc_st_supplies
SET wip_entity_id = msc_st_wip_entity_id_s.NEXTVAL
WHERE rowid = lb_rowid(j);
INSERT INTO msc_local_id_supply
(local_id,
st_transaction_id,
instance_id,
entity_name,
data_source_type,
char1,
char2,
char3,
char4,
SOURCE_ORG_ID,
SOURCE_INVENTORY_ITEM_ID,
SOURCE_BILL_SEQUENCE_ID,
SOURCE_ROUTING_SEQUENCE_ID,
SOURCE_SCHEDULE_GROUP_ID,
SOURCE_WIP_ENTITY_ID,
last_update_date,
last_updated_by,
creation_date,
created_by)
SELECT
wip_entity_id,
st_transaction_id,
MSC_CL_PRE_PROCESS.v_instance_id,
'WIP_ENTITY_ID',
data_source_type,
MSC_CL_PRE_PROCESS.v_instance_code,
company_name,
organization_code ,
wip_entity_name,
SOURCE_ORG_ID,
SOURCE_INVENTORY_ITEM_ID,
SOURCE_BILL_SEQUENCE_ID,
SOURCE_ROUTING_SEQUENCE_ID,
SOURCE_SCHEDULE_GROUP_ID,
SOURCE_WIP_ENTITY_ID,
MSC_CL_PRE_PROCESS.v_current_date,
MSC_CL_PRE_PROCESS.v_current_user,
MSC_CL_PRE_PROCESS.v_current_date,
MSC_CL_PRE_PROCESS.v_current_user
FROM msc_st_supplies
WHERE rowid = lb_rowid(j);
UPDATE msc_st_supplies
SET schedule_group_id = msc_st_schedule_group_id_s.NEXTVAL
WHERE rowid = lb_rowid(j);
INSERT INTO msc_local_id_supply
(local_id,
st_transaction_id,
instance_id,
entity_name,
data_source_type,
char1,
char2,
char3,
char4,
SOURCE_ORG_ID,
SOURCE_INVENTORY_ITEM_ID,
SOURCE_BILL_SEQUENCE_ID,
SOURCE_ROUTING_SEQUENCE_ID,
SOURCE_SCHEDULE_GROUP_ID,
SOURCE_WIP_ENTITY_ID,
last_update_date,
last_updated_by,
creation_date,
created_by)
SELECT
schedule_group_id,
st_transaction_id,
MSC_CL_PRE_PROCESS.v_instance_id,
'SCHEDULE_GROUP_ID',
data_source_type,
MSC_CL_PRE_PROCESS.v_instance_code,
company_name,
organization_code ,
schedule_group_name,
SOURCE_ORG_ID,
SOURCE_INVENTORY_ITEM_ID,
SOURCE_BILL_SEQUENCE_ID,
SOURCE_ROUTING_SEQUENCE_ID,
SOURCE_SCHEDULE_GROUP_ID,
SOURCE_WIP_ENTITY_ID,
MSC_CL_PRE_PROCESS.v_current_date,
MSC_CL_PRE_PROCESS.v_current_user,
MSC_CL_PRE_PROCESS.v_current_date,
MSC_CL_PRE_PROCESS.v_current_user
FROM msc_st_supplies
WHERE rowid = lb_rowid(j);
UPDATE msc_st_supplies
SET disposition_id = wip_entity_id
WHERE process_flag = MSC_CL_PRE_PROCESS.G_IN_PROCESS
AND batch_id = lv_batch_id
AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code;
'UPDATE msc_st_supplies mss '
||' SET schedule_group_id = (SELECT local_id'
||' FROM msc_local_id_supply mls'
||' WHERE mls.char4 = mss.schedule_group_name'
||' AND mls.char3 = mss.organization_code'
||' AND NVL(mls.char2, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
||' NVL(mss.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' AND mls.char1 = mss.sr_instance_code'
||' AND mls.entity_name = ''SCHEDULE_GROUP_ID'' )'
||' WHERE deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(schedule_group_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id'
||' AND sr_instance_code = :v_instance_code';
UPDATE msc_st_supplies
SET job_op_seq_num =
to_number(decode(length(rtrim(job_op_seq_code,'0123456789')),
NULL,job_op_seq_code,'1'))
WHERE rowid = lb_rowid(j);
SELECT rowid
FROM msc_st_demands
WHERE process_flag IN (MSC_CL_PRE_PROCESS.G_IN_PROCESS,MSC_CL_PRE_PROCESS.G_ERROR_FLG)
AND origination_type =77
AND batch_id = p_batch_id
AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code
AND ENTITY='ERO';
SELECT max(rowid)
FROM msc_st_demands
WHERE process_flag = MSC_CL_PRE_PROCESS.G_IN_PROCESS
AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code
AND batch_id = p_batch_id
AND origination_type =77 -- Not for flow schedule
AND NVL(operation_seq_num,MSC_CL_PRE_PROCESS.NULL_VALUE) = MSC_CL_PRE_PROCESS.NULL_VALUE
AND deleted_flag = MSC_CL_PRE_PROCESS.SYS_NO
AND ENTITY='ERO'
GROUP BY sr_instance_code,company_name,organization_code,routing_name,
operation_seq_code,alternate_routing_designator,operation_effectivity_date;
'UPDATE msc_st_demands msd1'
||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE message_id < (SELECT MAX(message_id)'
||' FROM msc_st_demands msd2'
||' WHERE msd2.sr_instance_code '
||' = msd1.sr_instance_code '
||' AND msd2.organization_code '
||' = msd1.organization_code '
||' AND NVL(msd2.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' = NVL(msd1.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' AND msd2.wip_entity_name = msd1.wip_entity_name'
||' AND NVL(msd2.operation_seq_code,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' = NVL(msd1.operation_seq_code,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' AND msd2.item_name '
||' = msd1.item_name '
||' AND msd2.origination_type '
||' = msd1.origination_type'
||' AND msd2.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(msd2.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')<> '||MSC_CL_PRE_PROCESS.NULL_VALUE||')'
||' AND msd1.process_flag ='|| MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND msd1.origination_type =77 '
||' AND msd1.ENTITY =''ERO'''
||' AND msd1.sr_instance_code = :v_instance_code '
||' AND NVL(msd1.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')<> '||MSC_CL_PRE_PROCESS.NULL_VALUE;
'UPDATE msc_st_demands msd1'
||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE EXISTS( SELECT 1 '
||' FROM msc_st_demands msd2'
||' WHERE msd2.sr_instance_code '
||' = msd1.sr_instance_code '
||' AND msd2.organization_code '
||' = msd1.organization_code '
||' AND NVL(msd2.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' = NVL(msd1.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' AND msd2.wip_entity_name = msd1.wip_entity_name'
||' AND NVL(msd2.operation_seq_code,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' = NVL(msd1.operation_seq_code,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' AND msd2.item_name '
||' = msd1.item_name '
||' AND msd2.origination_type '
||' = msd1.origination_type'
||' AND msd2.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(msd2.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
||' GROUP BY sr_instance_code,organization_code,wip_entity_name,'
||' company_name,operation_seq_code,item_name,origination_type'
||' HAVING COUNT(*) > 1)'
||' AND msd1.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND msd1.origination_type =77'
||' AND msd1.ENTITY =''ERO'''
||' AND msd1.sr_instance_code = :v_instance_code'
||' AND NVL(msd1.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE;
||' DELETED_FLAG ||''~''||'
||' COMPANY_NAME ||''~''||'
||' DEMAND_TYPE' ;
SELECT msc_st_batch_id_s.NEXTVAL
INTO lv_batch_id
FROM DUAL;
' UPDATE msc_st_demands '
||' SET batch_id = :lv_batch_id'
||' WHERE process_flag IN ('||MSC_CL_PRE_PROCESS.G_IN_PROCESS||','||MSC_CL_PRE_PROCESS.G_ERROR_FLG||')'
||' AND sr_instance_code = :v_instance_code'
||' AND origination_type =77'
||' AND ENTITY =''ERO'''
||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
||' AND rownum <= '||MSC_CL_PRE_PROCESS.v_batch_size;
UPDATE msc_st_demands
SET st_transaction_id = msc_st_demands_s.NEXTVAL,
refresh_id = MSC_CL_PRE_PROCESS.v_refresh_id,
last_update_date = MSC_CL_PRE_PROCESS.v_current_date,
last_updated_by = MSC_CL_PRE_PROCESS.v_current_user,
creation_date = MSC_CL_PRE_PROCESS.v_current_date,
created_by = MSC_CL_PRE_PROCESS.v_current_user
WHERE rowid = lb_rowid(j);
p_token_value1 => 'DELETED_FLAG',
p_token2 => 'DEFAULT_VALUE',
p_token_value2 => MSC_CL_PRE_PROCESS.SYS_NO );
' AND NVL(deleted_flag,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') NOT IN(1,2)';
p_col_name => 'DELETED_FLAG',
p_debug => MSC_CL_PRE_PROCESS.v_debug,
p_default_value => MSC_CL_PRE_PROCESS.SYS_NO);
'UPDATE msc_st_demands msd'
||' SET wip_entity_id = ( SELECT local_id '
||' FROM msc_local_id_supply mlid'
||' WHERE mlid.char1 = msd.sr_instance_code'
||' AND NVL(mlid.char2,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' = NVL(msd.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' AND mlid.char3 = msd.organization_code'
||' AND mlid.char4 = msd.wip_entity_name'
||' AND mlid.entity_name = ''WIP_ENTITY_ID'' )'
||' WHERE origination_type =77 '
||' AND ENTITY=''ERO'''
||' AND process_flag ='||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code =:v_instance_code';
'UPDATE msc_st_demands '
||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE NVL(wip_entity_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') ='||MSC_CL_PRE_PROCESS.NULL_VALUE
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND origination_type =77'
||' AND ENTITY=''ERO'''
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :v_instance_code';
'UPDATE msc_st_demands '
||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE (NVL(using_requirement_quantity,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')='||MSC_CL_PRE_PROCESS.NULL_VALUE
||' OR NVL(using_assembly_demand_date,SYSDATE-36500) = SYSDATE-36500 )'
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND origination_type =77'
||' AND ENTITY=''ERO'''
||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :v_instance_code';
' UPDATE msc_st_demands'
||' SET using_assembly_item_id = inventory_item_id'
||' WHERE process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(using_assembly_item_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND origination_type =77'
||' AND ENTITY=''ERO'''
||' AND batch_id = :lv_batch_id'
||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
||' AND sr_instance_code = :v_instance_code';
' UPDATE msc_st_demands'
||' SET order_number = wip_entity_name'
||' WHERE process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND origination_type =77'
||' AND ENTITY=''ERO'''
||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :v_instance_code';
' UPDATE msc_st_demands'
||' SET disposition_id = wip_entity_id'
||' WHERE process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(wip_entity_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND origination_type =77'
||' AND ENTITY=''ERO'''
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :v_instance_code';
' UPDATE msc_st_demands'
||' SET mps_date_required = using_assembly_demand_date'
||' WHERE process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(mps_date_required,SYSDATE-36500) = SYSDATE-36500'
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND origination_type =77'
||' AND ENTITY=''ERO'''
||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :v_instance_code';
' UPDATE msc_st_demands'
||' SET routing_name = nvl(USING_ASSEMBLY_ITEM_NAME,item_name)' /* bug 3768813 */
||' WHERE process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND NVL(routing_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||')'
||' = '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND origination_type =77'
||' AND ENTITY=''ERO'''
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code = :v_instance_code';
'update msc_st_demands msd'
||' set operation_seq_num = '
||' (select operation_seq_num '
||' from msc_routing_operations mro '
||' where mro.routing_sequence_id = msd.routing_sequence_id and '
||' mro.effectivity_date = msd.operation_effectivity_date and '
||' mro.SR_INSTANCE_ID = '||MSC_CL_PRE_PROCESS.v_instance_id||' and '
||' mro.operation_seq_num = to_number(decode(length(rtrim(msd.operation_seq_code,''0123456789'')),'
||' NULL,msd.operation_seq_code,''1'')) and'
||' mro.plan_id = -1 and '
||' mro.operation_type = 1)'
||' WHERE sr_instance_code = :v_instance_code'
||' AND process_flag = '|| MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND batch_id = :lv_batch_id ';
'UPDATE msc_st_demands msd'
||' SET operation_seq_num= (SELECT number1'
||' FROM msc_local_id_setup mlis'
||' WHERE mlis.char1 = msd.sr_instance_code'
||' AND NVL(mlis.char2,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' = NVL(msd.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' AND mlis.char3 = msd.organization_code'
||' AND mlis.char4 = msd.routing_name'
||' AND mlis.char5 = msd.operation_seq_code'
||' AND NVL(mlis.char6,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' = NVL(msd.alternate_routing_designator,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' AND mlis.date1 = msd.operation_effectivity_date'
||' AND mlis.entity_name = ''OPERATION_SEQUENCE_ID'') '
||' WHERE sr_instance_code = :v_instance_code'
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND batch_id = :lv_batch_id'
||' AND operation_seq_num is null'; /* bug 3768813 */
p_error_code => 'MSC_PP_DELETE_FAIL',
p_message_text => lv_message_text,
p_error_text => lv_error_text);
'UPDATE msc_st_demands '
||' SET process_flag ='||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
||' error_text = '||''''||lv_message_text||''''
||' WHERE NVL(operation_seq_num,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
||' AND deleted_flag ='||MSC_CL_PRE_PROCESS.SYS_YES
||' AND origination_type =77'
||' AND ENTITY=''ERO'''
||' AND batch_id = :lv_batch_id'
||' AND sr_instance_code =:v_instance_code';
||' AND origination_type =77 AND deleted_flag ='||MSC_CL_PRE_PROCESS.SYS_NO
||' AND ENTITY=''ERO''' ;
||' AND origination_type =77 AND deleted_flag ='||MSC_CL_PRE_PROCESS.SYS_NO
||' AND ENTITY=''ERO''' ;
||' AND origination_type =77 AND deleted_flag ='||MSC_CL_PRE_PROCESS.SYS_NO
||' AND ENTITY=''ERO''' ;
UPDATE msc_st_demands
SET operation_seq_num =
to_number(decode(length(rtrim(operation_seq_code,'0123456789')),
NULL,operation_seq_code,'1'))
WHERE rowid = lb_rowid(j);
INSERT INTO msc_local_id_setup
(local_id,
st_transaction_id,
instance_id,
entity_name,
data_source_type,
char1,
char2,
char3,
char4,
char5,
char6,
number1,
date1,
SOURCE_ORGANIZATION_ID,
SOURCE_INVENTORY_ITEM_ID,
SOURCE_PROJECT_ID,
SOURCE_TASK_ID,
SOURCE_WIP_ENTITY_ID,
SOURCE_OPERATION_SEQ_NUM,
SOURCE_USING_ASSEMBLY_ID,
last_update_date,
last_updated_by,
creation_date,
created_by )
SELECT
msc_st_operation_sequence_id_s.NEXTVAL,
st_transaction_id,
MSC_CL_PRE_PROCESS.v_instance_id,
'OPERATION_SEQUENCE_ID',
data_source_type,
MSC_CL_PRE_PROCESS.v_instance_code,
company_name,
organization_code,
routing_name,
operation_seq_code,
alternate_routing_designator,
operation_seq_num,
operation_effectivity_date,
SOURCE_ORGANIZATION_ID,
SOURCE_INVENTORY_ITEM_ID,
SOURCE_PROJECT_ID,
SOURCE_TASK_ID,
SOURCE_WIP_ENTITY_ID,
SOURCE_OPERATION_SEQ_NUM,
SOURCE_USING_ASSEMBLY_ITEM_ID,
MSC_CL_PRE_PROCESS.v_current_date,
MSC_CL_PRE_PROCESS.v_current_user,
MSC_CL_PRE_PROCESS.v_current_date,
MSC_CL_PRE_PROCESS.v_current_user
FROM msc_st_demands
WHERE rowid = lb_rowid(j) ;
'UPDATE msc_st_demands msd'
||' SET operation_seq_num= (SELECT number1'
||' FROM msc_local_id_setup mlis'
||' WHERE mlis.char1 = msd.sr_instance_code'
||' AND NVL(mlis.char2,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' = NVL(msd.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' AND mlis.char3 = msd.organization_code'
||' AND mlis.char4 = msd.routing_name'
||' AND mlis.char5 = msd.operation_seq_code'
||' AND NVL(mlis.char6,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' = NVL(msd.alternate_routing_designator,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
||' AND mlis.date1 = msd.operation_effectivity_date'
||' AND mlis.entity_name = ''OPERATION_SEQUENCE_ID'') '
||' WHERE sr_instance_code = :v_instance_code'
||' AND NVL(operation_seq_num,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= '||MSC_CL_PRE_PROCESS.NULL_VALUE
||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
||' AND batch_id = :lv_batch_id';