[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT msv.VISIT_ID,
msv.VISIT_NAME,
msv.VISIT_DESC,
nvl(msv.VISIT_START_DATE,sysdate) VISIT_START_DATE,
nvl(msv.VISIT_END_DATE,sysdate) VISIT_END_DATE,
msv.ORGANIZATION_ID,
msv.VISIT_TYPE,
msv.VISIT_PRIORITY,
msv.VISIT_STATUS,
msv.VISIT_NUMBER,
msv.SR_INSTANCE_ID
FROM MSC_ST_VISITS msv
WHERE msv.deleted_flag = MSC_UTIL.SYS_NO
AND msv.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
SELECT msv.VISIT_ID,
msv.ORGANIZATION_ID,
msv.SR_INSTANCE_ID
FROM MSC_ST_VISITS msv
WHERE msv.deleted_flag = MSC_UTIL.SYS_YES
AND msv.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_VISITS',
MSC_CL_COLLECTION.v_instance_id,
-1);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_VISITS',
MSC_CL_COLLECTION.v_instance_id,
null,
v_sub_str);
'INSERT INTO '||lv_tbl
||'( VISIT_ID,'
||'VISIT_NAME,'
||'VISIT_DESC,'
||' VISIT_START_DATE,'
||'VISIT_END_DATE,'
||'ORGANIZATION_ID,'
||'VISIT_TYPE,'
||'VISIT_PRIORITY,'
||'VISIT_STATUS,'
||'VISIT_NUMBER,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID,'
||'LAST_UPDATE_DATE,'
||'LAST_UPDATED_BY,'
||'CREATION_DATE,'
||'CREATED_BY) '
||'SELECT msv.VISIT_ID,'
||'msv.VISIT_NAME,'
||'msv.VISIT_DESC,'
||'nvl(msv.VISIT_START_DATE,sysdate),'
||'nvl(msv.VISIT_END_DATE,sysdate),'
||'msv.ORGANIZATION_ID,'
||'msv.VISIT_TYPE,'
||'msv.VISIT_PRIORITY,'
||'msv.VISIT_STATUS,'
||'msv.VISIT_NUMBER,'
||':v_last_collection_id,'
||':v_instance_id,'
||':v_current_date,'
||':v_current_user,'
||':v_current_date,'
||':v_current_user'
||' FROM MSC_ST_VISITS msv '
||'WHERE msv.deleted_flag = '||MSC_UTIL.SYS_NO
||' AND msv.SR_INSTANCE_ID= '
||MSC_CL_COLLECTION.v_instance_id;
DELETE MSC_WO_MILESTONES
WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
AND VISIT_ID = c_rec.visit_id
AND ORGANIZATION_ID = c_rec.organization_id;
DELETE MSC_VISITS
WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
AND VISIT_ID = c_rec.visit_id
AND ORGANIZATION_ID = c_rec.organization_id;
UPDATE MSC_VISITS
SET
VISIT_DESC = c_rec.VISIT_DESC,
VISIT_NAME = c_rec.VISIT_NAME,
VISIT_START_DATE = c_rec.VISIT_START_DATE,
VISIT_END_DATE = c_rec.VISIT_END_DATE,
VISIT_TYPE = c_rec.VISIT_TYPE,
VISIT_PRIORITY = c_rec.VISIT_PRIORITY,
VISIT_STATUS = c_rec.VISIT_STATUS,
VISIT_NUMBER = c_rec.VISIT_NUMBER,
REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND VISIT_ID = c_rec.VISIT_ID
AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
'INSERT INTO '||lv_tbl
||'( VISIT_ID,'
||'VISIT_NAME,'
||'VISIT_DESC,'
||'VISIT_START_DATE,'
||'VISIT_END_DATE,'
||'ORGANIZATION_ID,'
||'VISIT_TYPE,'
||'VISIT_PRIORITY,'
||'VISIT_STATUS,'
||'VISIT_NUMBER,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID,'
||'LAST_UPDATE_DATE,'
||'LAST_UPDATED_BY,'
||'CREATION_DATE,'
||'CREATED_BY) '
||'values ( :VISIT_ID,'
||':VISIT_NAME,'
||':VISIT_DESC,'
||':VISIT_START_DATE,'
||':VISIT_END_DATE,'
||':ORGANIZATION_ID,'
||':VISIT_TYPE,'
||':VISIT_PRIORITY,'
||':VISIT_STATUS,'
||':VISIT_NUMBER,'
||':v_last_collection_id,'
||':v_instance_id,'
||':v_current_date,'
||':v_current_user,'
||':v_current_date,'
||':v_current_user)';
SELECT mswa.SUPPLY_ID,
mswa.VISIT_ID,
mswa.ORGANIZATION_ID,
mswa.SR_INSTANCE_ID
FROM MSC_ST_WO_ATTRIBUTES mswa
WHERE mswa.deleted_flag = MSC_UTIL.SYS_YES
AND mswa.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_ATTRIBUTES',
MSC_CL_COLLECTION.v_instance_id,
-1);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_ATTRIBUTES',
MSC_CL_COLLECTION.v_instance_id,
null,
v_sub_str);
'INSERT INTO '||lv_tbl
||'(SUPPLY_ID,'
||'PRODUCES_TO_STOCK, '
||'SERIAL_NUM, '
||'VISIT_ID, '
||'VISIT_NAME, '
||'PARAMETER1, '
||'PARAMETER2, '
||'PARAMETER3, '
||'PARAMETER4, '
||'PARAMETER5, '
||'PARAMETER6, '
||'PARAMETER7, '
||'PARAMETER8, '
||'PARAMETER9, '
||'MASTER_WO, '
||'PREV_MILESTONE, '
||'NEXT_MILESTONE, '
||'ORGANIZATION_ID,'
||'ITEM_ALTERNATES_EXIST, '
||'FLEET_HEADER_ID,'
||'PRODUCT_CLASSIFICATION, '
-- ||'Operating_Fleet, '/* remove the above line and uncomment this line for bug fix# 12428753 */
||'MAINTENANCE_REQT, '
||'WIP_ENTITY_ID, '
||'REFRESH_ID, '
||'SR_INSTANCE_ID, '
||'LAST_UPDATE_DATE, '
||'LAST_UPDATED_BY, '
||'CREATION_DATE, '
||'CREATED_BY) '
||'SELECT NVL(ms.TRANSACTION_ID,-1) SUPPLY_ID,'
||'mswa.PRODUCES_TO_STOCK, '
||'mswa.SERIAL_NUM, '
||'mswa.VISIT_ID, '
||'mswa.VISIT_NAME, '
||'mswa.PARAMETER1, '
||'mswa.PARAMETER2, '
||'mswa.PARAMETER3, '
||'mswa.PARAMETER4, '
||'mswa.PARAMETER5, '
||'mswa.PARAMETER6, '
||'mswa.PARAMETER7, '
||'mswa.PARAMETER8, '
||'mswa.PARAMETER9, '
||'mswa.MASTER_WO, '
||'mswa.PREV_MILESTONE, '
||'mswa.NEXT_MILESTONE, '
||'mswa.ORGANIZATION_ID, '
||'mswa.ITEM_ALTERNATES_EXIST, '
||'mswa.FLEET_HEADER_ID,'
||'mswa.PRODUCT_CLASSIFICATION, '
-- ||'mswa.Operating_Fleet, '/* remove the above line and uncomment this line for bug fix# 12428753 */
||'mswa.MAINTENANCE_REQT, '
||'mswa.WIP_ENTITY_ID, '
||':v_last_collection_id, '
||':v_instance_id, '
||':v_current_date, '
||':v_current_user, '
||':v_current_date, '
||':v_current_user '
||' FROM MSC_ST_WO_ATTRIBUTES mswa, '
|| lv_supplies_tbl||' ms'
||' WHERE mswa.DELETED_FLAG = '||MSC_UTIL.SYS_NO
||' AND mswa.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
||' AND ms.SR_INSTANCE_ID(+)= mswa.SR_INSTANCE_ID'
||' AND ms.ORGANIZATION_ID(+)= mswa.ORGANIZATION_ID'
||' AND ms.DISPOSITION_ID(+)= mswa.WIP_ENTITY_ID*2'
||' AND ms.MAINTENANCE_OBJECT_SOURCE = 2'
||' AND ms.ORDER_TYPE = 70' ;
DELETE MSC_WO_ATTRIBUTES
WHERE SR_INSTANCE_ID= c1.sr_instance_id
AND VISIT_ID = c1.visit_id
AND SUPPLY_ID = c1.supply_id
AND ORGANIZATION_ID = c1.organization_id;
lv_cursor_stmt:= 'SELECT NVL(ms.TRANSACTION_ID,-1) SUPPLY_ID,'
||'mswa.PRODUCES_TO_STOCK, '
||'mswa.SERIAL_NUM, '
||'mswa.VISIT_ID, '
||'mswa.VISIT_NAME, '
||'mswa.PARAMETER1, '
||'mswa.PARAMETER2, '
||'mswa.PARAMETER3, '
||'mswa.PARAMETER4, '
||'mswa.PARAMETER5, '
||'mswa.PARAMETER6, '
||'mswa.PARAMETER7, '
||'mswa.PARAMETER8, '
||'mswa.PARAMETER9, '
||'mswa.MASTER_WO, '
||'mswa.PREV_MILESTONE, '
||'mswa.NEXT_MILESTONE, '
||'mswa.ORGANIZATION_ID, '
||'mswa.ITEM_ALTERNATES_EXIST, '
||'mswa.PRODUCT_CLASSIFICATION, '
-- ||'mswa.Operating_Fleet, '/* remove the above line and uncomment this line for bug fix# 12428753 */
||'mswa.MAINTENANCE_REQT, '
||'mswa.WIP_ENTITY_ID '
||' FROM MSC_ST_WO_ATTRIBUTES mswa, '
|| lv_supplies_tbl||' ms'
||' WHERE mswa.DELETED_FLAG = '||MSC_UTIL.SYS_NO
||' AND mswa.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
||' AND ms.SR_INSTANCE_ID(+)= mswa.SR_INSTANCE_ID'
||' AND ms.ORGANIZATION_ID(+)= mswa.ORGANIZATION_ID'
||' AND ms.DISPOSITION_ID(+)= mswa.WIP_ENTITY_ID*2'
||' AND ms.MAINTENANCE_OBJECT_SOURCE = 2'
||' AND ms.ORDER_TYPE = 70' ;
UPDATE MSC_WO_ATTRIBUTES
SET
SERIAL_NUM = c_rec.SERIAL_NUM,
PRODUCES_TO_STOCK = c_rec.PRODUCES_TO_STOCK,
VISIT_ID = c_rec.VISIT_ID,
VISIT_NAME = c_rec.VISIT_NAME,
PARAMETER1 = c_rec.PARAMETER1,
PARAMETER2 = c_rec.PARAMETER2,
PARAMETER3 = c_rec.PARAMETER3,
PARAMETER4 = c_rec.PARAMETER4,
PARAMETER5 = c_rec.PARAMETER5,
PARAMETER6 = c_rec.PARAMETER6,
PARAMETER7 = c_rec.PARAMETER7,
PARAMETER8 = c_rec.PARAMETER8,
PARAMETER9 = c_rec.PARAMETER9,
MASTER_WO = c_rec.MASTER_WO,
PREV_MILESTONE = c_rec.PREV_MILESTONE,
NEXT_MILESTONE = c_rec.NEXT_MILESTONE,
ITEM_ALTERNATES_EXIST = c_rec.ITEM_ALTERNATES_EXIST,
PRODUCT_CLASSIFICATION = c_rec.PRODUCT_CLASSIFICATION,
-- Operating_Fleet = c_rec.Operating_Fleet,/* remove the above line and uncomment this line for bug fix# 12428753 */
MAINTENANCE_REQT = c_rec.MAINTENANCE_REQT,
WIP_ENTITY_ID = c_rec.WIP_ENTITY_ID,
REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND SUPPLY_ID = c_rec.SUPPLY_ID
AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
'INSERT INTO '||lv_tbl
||'(SUPPLY_ID,'
||'PRODUCES_TO_STOCK, '
||'SERIAL_NUM, '
||'VISIT_ID, '
||'VISIT_NAME, '
||'PARAMETER1, '
||'PARAMETER2, '
||'PARAMETER3, '
||'PARAMETER4, '
||'PARAMETER5, '
||'PARAMETER6, '
||'PARAMETER7, '
||'PARAMETER8, '
||'PARAMETER9, '
||'MASTER_WO, '
||'PREV_MILESTONE, '
||'NEXT_MILESTONE, '
||'ORGANIZATION_ID,'
||'ITEM_ALTERNATES_EXIST, '
||'PRODUCT_CLASSIFICATION, '
-- ||'Operating_Fleet, '/* remove the above line and uncomment this line for bug fix# 12428753 */
||'MAINTENANCE_REQT, '
||'WIP_ENTITY_ID, '
||'REFRESH_ID, '
||'SR_INSTANCE_ID, '
||'LAST_UPDATE_DATE, '
||'LAST_UPDATED_BY, '
||'CREATION_DATE, '
||'CREATED_BY) '
||'values ( :SUPPLY_ID,'
||':PRODUCES_TO_STOCK, '
||':SERIAL_NUM, '
||':VISIT_ID, '
||':VISIT_NAME, '
||':PARAMETER1, '
||':PARAMETER2, '
||':PARAMETER3, '
||':PARAMETER4, '
||':PARAMETER5, '
||':PARAMETER6, '
||':PARAMETER7, '
||':PARAMETER8, '
||':PARAMETER9, '
||':MASTER_WO, '
||':PREV_MILESTONE, '
||':NEXT_MILESTONE, '
||':ORGANIZATION_ID, '
||':ITEM_ALTERNATES_EXIST, '
||':PRODUCT_CLASSIFICATION, '
-- ||':Operating_Fleet, '/* remove the above line and uncomment this line for bug fix# 12428753 */
||':MAINTENANCE_REQT, '
||':WIP_ENTITY_ID, '
||':v_last_collection_id, '
||':v_instance_id, '
||':v_current_date, '
||':v_current_user, '
||':v_current_date, '
||':v_current_user) ';
SELECT mswbs.PARAMETER_NAME,
mswbs.DISPLAY_NAME,
mswbs.ORGANIZATION_ID,
mswbs.SR_INSTANCE_ID
FROM MSC_ST_WORK_BREAKDOWN_STRUCT mswbs
WHERE mswbs.deleted_flag = MSC_UTIL.SYS_NO
AND mswbs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
SELECT mswbs.PARAMETER_NAME,
mswbs.DISPLAY_NAME,
mswbs.ORGANIZATION_ID,
mswbs.SR_INSTANCE_ID
FROM MSC_ST_WORK_BREAKDOWN_STRUCT mswbs
WHERE mswbs.deleted_flag = MSC_UTIL.SYS_YES
AND mswbs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WORK_BREAKDOWN_STRUCT',
MSC_CL_COLLECTION.v_instance_id,
-1);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WORK_BREAKDOWN_STRUCT',
MSC_CL_COLLECTION.v_instance_id,
null,
v_sub_str);
'INSERT INTO '||lv_tbl
||'(PARAMETER_NAME, '
||' DISPLAY_NAME, '
||' ORGANIZATION_ID, '
||' REFRESH_ID, '
||' SR_INSTANCE_ID, '
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY) '
||'SELECT mswbs.PARAMETER_NAME,'
||'mswbs.DISPLAY_NAME, '
||'mswbs.ORGANIZATION_ID,'
||':v_last_collection_id, '
||':v_instance_id, '
||':v_current_date, '
||':v_current_user, '
||':v_current_date, '
||':v_current_user '
||' FROM MSC_ST_WORK_BREAKDOWN_STRUCT mswbs '
||'WHERE mswbs.deleted_flag ='|| MSC_UTIL.SYS_NO
||' AND mswbs.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id;
DELETE MSC_WORK_BREAKDOWN_STRUCT
WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
AND PARAMETER_NAME = c_rec.parameter_name
AND ORGANIZATION_ID = c_rec.organization_id;
UPDATE MSC_WORK_BREAKDOWN_STRUCT
SET DISPLAY_NAME = c_rec.DISPLAY_NAME,
REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID
AND PARAMETER_NAME = c_rec.PARAMETER_NAME;
'INSERT INTO '||lv_tbl
||'(PARAMETER_NAME, '
||' DISPLAY_NAME, '
||' ORGANIZATION_ID, '
||' REFRESH_ID, '
||' SR_INSTANCE_ID, '
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY) '
||'values (:PARAMETER_NAME,'
||':DISPLAY_NAME, '
||':ORGANIZATION_ID,'
||':v_last_collection_id, '
||':v_instance_id, '
||':v_current_date, '
||':v_current_user, '
||':v_current_date, '
||':v_current_user) ';
SELECT mswth.CURR_SUPPLY_ID,
mswth.NEXT_SUPPLY_ID,
mswth.ORGANIZATION_ID,
mswth.SR_INSTANCE_ID
FROM MSC_ST_WO_TASK_HIERARCHY mswth
WHERE mswth.deleted_flag = MSC_UTIL.SYS_YES
AND mswth.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_TASK_HIERARCHY',
MSC_CL_COLLECTION.v_instance_id, -1);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_TASK_HIERARCHY',
MSC_CL_COLLECTION.v_instance_id,
null,
v_sub_str);
'INSERT INTO '||lv_tbl
||'(CURR_SUPPLY_ID,'
||'NEXT_SUPPLY_ID, '
||'PRECEDENCE_CONSTRAINT, '
||'MIN_SEPARATION, '
||'MIN_SEP_TIME_UNIT, '
||'MAX_SEPARATION, '
||'MAX_SEP_TIME_UNIT, '
||'ORGANIZATION_ID, '
||'TASK_LINK_ID, '
||'CURRENT_WIP_ENTITY_ID, '
||'NEXT_WIP_ENTITY_ID, '
||'REFRESH_ID, '
||'SR_INSTANCE_ID, '
||'LAST_UPDATE_DATE, '
||'LAST_UPDATED_BY, '
||'CREATION_DATE, '
||'CREATED_BY) '
||'SELECT NVL(ms1.TRANSACTION_ID,-1) CURR_SUPPLY_ID, '
||'NVL(ms2.TRANSACTION_ID,-1) NEXT_SUPPLY_ID, '
||'mswth.PRECEDENCE_CONSTRAINT, '
||'mswth.MIN_SEPARATION, '
||'mswth.MIN_SEP_TIME_UNIT, '
||'mswth.MAX_SEPARATION, '
||'mswth.MAX_SEP_TIME_UNIT, '
||'mswth.ORGANIZATION_ID, '
||'mswth.TASK_LINK_ID, '
||'mswth.CURRENT_WIP_ENTITY_ID, '
||'mswth.NEXT_WIP_ENTITY_ID, '
||':v_last_collection_id, '
||':v_instance_id, '
||':v_current_date, '
||':v_current_user, '
||':v_current_date, '
||':v_current_user '
||' FROM MSC_ST_WO_TASK_HIERARCHY mswth, '
|| lv_supplies_tbl||' ms1,'
|| lv_supplies_tbl||' ms2'
||' WHERE mswth.deleted_flag = '|| MSC_UTIL.SYS_NO
||' AND mswth.SR_INSTANCE_ID= '
|| MSC_CL_COLLECTION.v_instance_id
||' AND ms1.SR_INSTANCE_ID(+)=mswth.SR_INSTANCE_ID'
||' AND ms2.SR_INSTANCE_ID(+)=mswth.SR_INSTANCE_ID'
||' AND ms1.ORGANIZATION_ID(+)=mswth.ORGANIZATION_ID'
||' AND ms2.ORGANIZATION_ID(+)=mswth.ORGANIZATION_ID'
||' AND ms1.DISPOSITION_ID(+)=mswth.CURRENT_WIP_ENTITY_ID*2'
||' AND ms2.DISPOSITION_ID(+)=mswth.NEXT_WIP_ENTITY_ID*2'
||' AND ms1.MAINTENANCE_OBJECT_SOURCE = 2'
||' AND ms1.MAINTENANCE_OBJECT_SOURCE = '
||' ms2.MAINTENANCE_OBJECT_SOURCE'
||' AND ms1.ORDER_TYPE = 70'
||' AND ms1.ORDER_TYPE = ms2.ORDER_TYPE' ;
DELETE MSC_WO_TASK_HIERARCHY
WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
AND CURR_SUPPLY_ID = c_rec.curr_supply_id
AND NEXT_SUPPLY_ID = c_rec.next_supply_id
AND ORGANIZATION_ID = c_rec.organization_id;
lv_cursor_stmt:= 'SELECT NVL(ms1.TRANSACTION_ID,-1) CURR_SUPPLY_ID, '
||'NVL(ms2.TRANSACTION_ID,-1) NEXT_SUPPLY_ID, '
||'mswth.PRECEDENCE_CONSTRAINT, '
||'mswth.MIN_SEPARATION, '
||'mswth.MIN_SEP_TIME_UNIT, '
||'mswth.MAX_SEPARATION, '
||'mswth.MAX_SEP_TIME_UNIT, '
||'mswth.ORGANIZATION_ID, '
||'mswth.TASK_LINK_ID, '
||'mswth.CURRENT_WIP_ENTITY_ID, '
||'mswth.NEXT_WIP_ENTITY_ID '
||' FROM MSC_ST_WO_TASK_HIERARCHY mswth, '
|| lv_supplies_tbl||' ms1,'
|| lv_supplies_tbl||' ms2'
||' WHERE mswth.deleted_flag = '|| MSC_UTIL.SYS_NO
||' AND mswth.SR_INSTANCE_ID= '
|| MSC_CL_COLLECTION.v_instance_id
||' AND ms1.SR_INSTANCE_ID(+)=mswth.SR_INSTANCE_ID'
||' AND ms2.SR_INSTANCE_ID(+)=mswth.SR_INSTANCE_ID'
||' AND ms1.ORGANIZATION_ID(+)=mswth.ORGANIZATION_ID'
||' AND ms2.ORGANIZATION_ID(+)=mswth.ORGANIZATION_ID'
||' AND ms1.DISPOSITION_ID(+)=mswth.CURRENT_WIP_ENTITY_ID*2'
||' AND ms2.DISPOSITION_ID(+)=mswth.NEXT_WIP_ENTITY_ID*2'
||' AND ms1.MAINTENANCE_OBJECT_SOURCE = 2'
||' AND ms1.MAINTENANCE_OBJECT_SOURCE = '
||' ms2.MAINTENANCE_OBJECT_SOURCE'
||' AND ms1.ORDER_TYPE = 70'
||' AND ms1.ORDER_TYPE = ms2.ORDER_TYPE' ;
UPDATE MSC_WO_TASK_HIERARCHY
SET
PRECEDENCE_CONSTRAINT = c_rec.PRECEDENCE_CONSTRAINT,
MIN_SEPARATION = c_rec.MIN_SEPARATION,
MIN_SEP_TIME_UNIT = c_rec.MIN_SEP_TIME_UNIT,
MAX_SEPARATION = c_rec.MAX_SEPARATION,
MAX_SEP_TIME_UNIT = c_rec.MAX_SEP_TIME_UNIT,
TASK_LINK_ID = c_rec.TASK_LINK_ID,
CURRENT_WIP_ENTITY_ID = c_rec.CURRENT_WIP_ENTITY_ID,
NEXT_WIP_ENTITY_ID = c_rec.NEXT_WIP_ENTITY_ID,
REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID
AND CURR_SUPPLY_ID = c_rec.CURR_SUPPLY_ID
AND NEXT_SUPPLY_ID = c_rec.NEXT_SUPPLY_ID;
'INSERT INTO '||lv_tbl
||'(CURR_SUPPLY_ID,'
||'NEXT_SUPPLY_ID, '
||'PRECEDENCE_CONSTRAINT, '
||'MIN_SEPARATION, '
||'MIN_SEP_TIME_UNIT, '
||'MAX_SEPARATION, '
||'MAX_SEP_TIME_UNIT, '
||'ORGANIZATION_ID, '
||'TASK_LINK_ID, '
||'CURRENT_WIP_ENTITY_ID, '
||'NEXT_WIP_ENTITY_ID, '
||'REFRESH_ID, '
||'SR_INSTANCE_ID, '
||'LAST_UPDATE_DATE, '
||'LAST_UPDATED_BY, '
||'CREATION_DATE, '
||'CREATED_BY) '
||'values (:CURR_SUPPLY_ID, '
||':NEXT_SUPPLY_ID, '
||':PRECEDENCE_CONSTRAINT, '
||':MIN_SEPARATION, '
||':MIN_SEP_TIME_UNIT, '
||':MAX_SEPARATION, '
||':MAX_SEP_TIME_UNIT, '
||':ORGANIZATION_ID, '
||':TASK_LINK_ID, '
||':CURRENT_WIP_ENTITY_ID, '
||':NEXT_WIP_ENTITY_ID, '
||':v_last_collection_id, '
||':v_instance_id, '
||':v_current_date, '
||':v_current_user, '
||':v_current_date, '
||':v_current_user) ';
SELECT mswor.SUPPLY_ID,
mswor.FROM_OP_SEQ_NUM,
mswor.FROM_OP_RES_SEQ_NUM,
mswor.TO_OP_SEQ_NUM,
mswor.TO_OP_RES_SEQ_NUM,
mswor.ORGANIZATION_ID,
mswor.SR_INSTANCE_ID
FROM MSC_ST_WO_OPERATION_REL mswor
WHERE mswor.deleted_flag = MSC_UTIL.SYS_YES
AND mswor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_OPERATION_REL',
MSC_CL_COLLECTION.v_instance_id,
-1);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_OPERATION_REL',
MSC_CL_COLLECTION.v_instance_id,
null,
v_sub_str);
'INSERT INTO '||lv_tbl
||'(SUPPLY_ID,'
||'PRECEDENCE_CONSTRAINT, '
||'MIN_SEPARATION, '
||'MIN_SEP_TIME_UNIT, '
||'MAX_SEPARATION, '
||'MAX_SEP_TIME_UNIT, '
||'FROM_OP_SEQ_NUM, '
||'FROM_OP_RES_SEQ_NUM, '
||'FROM_OP_DESC, '
||'TO_OP_SEQ_NUM, '
||'TO_OP_RES_SEQ_NUM, '
||'TO_OP_DESC, '
||'ORGANIZATION_ID, '
||'WORKORDER_ID, '
||'WIP_ENTITY_ID, '
||'REFRESH_ID, '
||'SR_INSTANCE_ID, '
||'LAST_UPDATE_DATE, '
||'LAST_UPDATED_BY, '
||'CREATION_DATE, '
||'CREATED_BY) '
||'SELECT NVL(ms.TRANSACTION_ID,-1) SUPPLY_ID,'
||'mswor.PRECEDENCE_CONSTRAINT, '
||'mswor.MIN_SEPARATION, '
||'mswor.MIN_SEP_TIME_UNIT, '
||'mswor.MAX_SEPARATION, '
||'mswor.MAX_SEP_TIME_UNIT, '
||'mswor.FROM_OP_SEQ_NUM, '
||'mswor.FROM_OP_RES_SEQ_NUM, '
||'mswor.FROM_OP_DESC, '
||'mswor.TO_OP_SEQ_NUM, '
||'mswor.TO_OP_RES_SEQ_NUM, '
||'mswor.TO_OP_DESC, '
||'mswor.ORGANIZATION_ID, '
||'mswor.WORKORDER_ID, '
||'mswor.WIP_ENTITY_ID, '
||':v_last_collection_id, '
||':v_instance_id, '
||':v_current_date, '
||':v_current_user, '
||':v_current_date, '
||':v_current_user '
||' FROM MSC_ST_WO_OPERATION_REL mswor,'
|| lv_supplies_tbl||' ms'
||' WHERE mswor.deleted_flag = '||MSC_UTIL.SYS_NO
||' AND mswor.SR_INSTANCE_ID= '
||MSC_CL_COLLECTION.v_instance_id
||' AND ms.SR_INSTANCE_ID(+)= mswor.SR_INSTANCE_ID'
||' AND ms.ORGANIZATION_ID(+)= mswor.ORGANIZATION_ID'
||' AND ms.DISPOSITION_ID(+)= mswor.WIP_ENTITY_ID*2'
||' AND ms.MAINTENANCE_OBJECT_SOURCE = 2'
||' AND ms.ORDER_TYPE = 70' ;
DELETE MSC_WO_OPERATION_REL
WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
AND SUPPLY_ID = c_rec.supply_id
AND FROM_OP_SEQ_NUM = c_rec.FROM_OP_SEQ_NUM
AND nvl(FROM_OP_RES_SEQ_NUM,-1) = nvl(c_rec.FROM_OP_RES_SEQ_NUM,-1)
AND TO_OP_SEQ_NUM = c_rec.TO_OP_SEQ_NUM
AND nvl(TO_OP_RES_SEQ_NUM,-1) = nvl(c_rec.TO_OP_RES_SEQ_NUM,-1)
AND ORGANIZATION_ID = c_rec.organization_id;
lv_cursor_stmt:= 'SELECT NVL(ms.TRANSACTION_ID,-1) SUPPLY_ID,'
||'mswor.PRECEDENCE_CONSTRAINT, '
||'mswor.MIN_SEPARATION, '
||'mswor.MIN_SEP_TIME_UNIT, '
||'mswor.MAX_SEPARATION, '
||'mswor.MAX_SEP_TIME_UNIT, '
||'mswor.FROM_OP_SEQ_NUM, '
||'mswor.FROM_OP_RES_SEQ_NUM, '
||'mswor.FROM_OP_DESC, '
||'mswor.TO_OP_SEQ_NUM, '
||'mswor.TO_OP_RES_SEQ_NUM, '
||'mswor.TO_OP_DESC, '
||'mswor.ORGANIZATION_ID, '
||'mswor.WORKORDER_ID, '
||'mswor.WIP_ENTITY_ID '
||' FROM MSC_ST_WO_OPERATION_REL mswor,'
|| lv_supplies_tbl||' ms'
||' WHERE mswor.deleted_flag = '||MSC_UTIL.SYS_NO
||' AND mswor.SR_INSTANCE_ID= '
||MSC_CL_COLLECTION.v_instance_id
||' AND ms.SR_INSTANCE_ID(+)= mswor.SR_INSTANCE_ID'
||' AND ms.ORGANIZATION_ID(+)= mswor.ORGANIZATION_ID'
||' AND ms.DISPOSITION_ID(+)= mswor.WIP_ENTITY_ID*2'
||' AND ms.MAINTENANCE_OBJECT_SOURCE = 2'
||' AND ms.ORDER_TYPE = 70' ;
UPDATE MSC_WO_OPERATION_REL
SET
PRECEDENCE_CONSTRAINT = c_rec.PRECEDENCE_CONSTRAINT,
FROM_OP_DESC = c_rec.FROM_OP_DESC,
TO_OP_DESC = c_rec.TO_OP_DESC,
MIN_SEPARATION = c_rec.MIN_SEPARATION,
MIN_SEP_TIME_UNIT = c_rec.MIN_SEP_TIME_UNIT,
MAX_SEPARATION = c_rec.MAX_SEPARATION,
MAX_SEP_TIME_UNIT = c_rec.MAX_SEP_TIME_UNIT,
WORKORDER_ID = c_rec.WORKORDER_ID,
WIP_ENTITY_ID = c_rec.WIP_ENTITY_ID,
REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID
AND SUPPLY_ID = c_rec.SUPPLY_ID
AND FROM_OP_SEQ_NUM = c_rec.FROM_OP_SEQ_NUM
AND TO_OP_SEQ_NUM = c_rec.TO_OP_SEQ_NUM
AND nvl(FROM_OP_RES_SEQ_NUM,-1)=nvl(c_rec.FROM_OP_RES_SEQ_NUM,-1)
AND nvl(TO_OP_RES_SEQ_NUM,-1) = nvl(c_rec.TO_OP_RES_SEQ_NUM,-1);
'INSERT INTO '||lv_tbl
||'(SUPPLY_ID,'
||'PRECEDENCE_CONSTRAINT, '
||'MIN_SEPARATION, '
||'MIN_SEP_TIME_UNIT, '
||'MAX_SEPARATION, '
||'MAX_SEP_TIME_UNIT, '
||'FROM_OP_SEQ_NUM, '
||'FROM_OP_RES_SEQ_NUM, '
||'FROM_OP_DESC, '
||'TO_OP_SEQ_NUM, '
||'TO_OP_RES_SEQ_NUM, '
||'TO_OP_DESC, '
||'ORGANIZATION_ID, '
||'WORKORDER_ID, '
||'WIP_ENTITY_ID, '
||'REFRESH_ID, '
||'SR_INSTANCE_ID, '
||'LAST_UPDATE_DATE, '
||'LAST_UPDATED_BY, '
||'CREATION_DATE, '
||'CREATED_BY) '
||'values (:SUPPLY_ID,'
||':PRECEDENCE_CONSTRAINT, '
||':MIN_SEPARATION, '
||':MIN_SEP_TIME_UNIT, '
||':MAX_SEPARATION, '
||':MAX_SEP_TIME_UNIT, '
||':FROM_OP_SEQ_NUM, '
||':FROM_OP_RES_SEQ_NUM, '
||':FROM_OP_DESC, '
||':TO_OP_SEQ_NUM, '
||':TO_OP_RES_SEQ_NUM, '
||':TO_OP_DESC, '
||':ORGANIZATION_ID, '
||':WORKORDER_ID, '
||':WIP_ENTITY_ID, '
||':v_last_collection_id, '
||':v_instance_id, '
||':v_current_date, '
||':v_current_user, '
||':v_current_date, '
||':v_current_user) ';
SELECT mswm.MILESTONE,
mswm.MILESTONE_DESC,
mswm.VISIT_ID,
mswm.ORGANIZATION_ID,
mswm.EARLIEST_START_DATE,
mswm.TARGET_COMPLETION_DATE,
mswm.SR_INSTANCE_ID
FROM MSC_ST_WO_MILESTONES mswm
WHERE mswm.deleted_flag = MSC_UTIL.SYS_NO
AND mswm.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
SELECT mswm.MILESTONE,
mswm.VISIT_ID,
mswm.ORGANIZATION_ID,
mswm.SR_INSTANCE_ID
FROM MSC_ST_WO_MILESTONES mswm
WHERE mswm.deleted_flag = MSC_UTIL.SYS_YES
AND mswm.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_MILESTONES',
MSC_CL_COLLECTION.v_instance_id,
-1);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_MILESTONES',
MSC_CL_COLLECTION.v_instance_id,
null,
v_sub_str);
'INSERT INTO '||lv_tbl
||'(MILESTONE, '
||'MILESTONE_DESC, '
||'VISIT_ID, '
||'ORGANIZATION_ID, '
||'EARLIEST_START_DATE, '
||'TARGET_COMPLETION_DATE, '
||'REFRESH_ID, '
||'SR_INSTANCE_ID, '
||'LAST_UPDATE_DATE, '
||'LAST_UPDATED_BY, '
||'CREATION_DATE, '
||'CREATED_BY) '
||'SELECT mswm.MILESTONE,'
||'mswm.MILESTONE_DESC, '
||'mswm.VISIT_ID, '
||'mswm.ORGANIZATION_ID, '
||'mswm.EARLIEST_START_DATE, '
||'mswm.TARGET_COMPLETION_DATE, '
||':v_last_collection_id, '
||':v_instance_id, '
||':v_current_date, '
||':v_current_user, '
||':v_current_date, '
||':v_current_user '
||' FROM MSC_ST_WO_MILESTONES mswm '
||' WHERE mswm.deleted_flag = '||MSC_UTIL.SYS_NO
||' AND mswm.SR_INSTANCE_ID= '
||MSC_CL_COLLECTION.v_instance_id;
DELETE MSC_WO_MILESTONES
WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
AND MILESTONE = c_rec.milestone
AND VISIT_ID = c_rec.visit_id
AND ORGANIZATION_ID = c_rec.organization_id;
UPDATE MSC_WO_MILESTONES
SET
MILESTONE_DESC = c_rec.MILESTONE_DESC,
EARLIEST_START_DATE = c_rec.EARLIEST_START_DATE,
TARGET_COMPLETION_DATE = c_rec.TARGET_COMPLETION_DATE,
REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND VISIT_ID = c_rec.VISIT_ID
AND MILESTONE = c_rec.MILESTONE
AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
'INSERT INTO '||lv_tbl
||'(MILESTONE, '
||'MILESTONE_DESC, '
||'VISIT_ID, '
||'ORGANIZATION_ID, '
||'EARLIEST_START_DATE, '
||'TARGET_COMPLETION_DATE, '
||'REFRESH_ID, '
||'SR_INSTANCE_ID, '
||'LAST_UPDATE_DATE, '
||'LAST_UPDATED_BY, '
||'CREATION_DATE, '
||'CREATED_BY) '
||'values (:MILESTONE,'
||':MILESTONE_DESC, '
||':VISIT_ID, '
||':ORGANIZATION_ID, '
||':EARLIEST_START_DATE, '
||':TARGET_COMPLETION_DATE, '
||':v_last_collection_id,'
||':v_instance_id,'
||':v_current_date,'
||':v_current_user,'
||':v_current_date,'
||':v_current_user)';
SELECT MIL1.INVENTORY_ITEM_ID PRIMARY_COMPONENT_ID,
MIL2.INVENTORY_ITEM_ID ALTERNATE_COMPONENT_ID,
sub_comp.RANK,
sub_comp.OP_SEQ_NUM,
sub_comp.RATIO,
sub_comp.PLAN_ID
FROM MSC_ST_WO_SUB_COMP sub_comp
, MSC_ITEM_ID_LID MIL1
, MSC_ITEM_ID_LID MIL2
WHERE sub_comp.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
AND MIL1.sr_instance_id = sub_comp.sr_instance_id
AND MIL1.SR_INVENTORY_ITEM_ID = sub_comp.PRIMARY_COMPONENT_ID
AND MIL2.sr_instance_id = sub_comp.sr_instance_id
AND MIL2.SR_INVENTORY_ITEM_ID = sub_comp.ALTERNATE_COMPONENT_ID;
SELECT TRANSACTION_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID
FROM MSC_ST_WO_SUB_COMP
WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_SUB_COMP',
MSC_CL_COLLECTION.v_instance_id,
-1);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_SUB_COMP',
MSC_CL_COLLECTION.v_instance_id,
null,
v_sub_str);
'INSERT INTO '||lv_tbl
||'(ALTERNATE_COMPONENT_ID,'
||'TRANSACTION_ID,'
||'OP_SEQ_NUM,'
||'ORGANIZATION_ID,'
||'PLAN_ID,'
||'PRIMARY_COMPONENT_ID,'
||'RANK,'
||'RATIO,'
||'REQUEST_ID,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID,'
||'LAST_UPDATE_DATE,'
||'LAST_UPDATED_BY,'
||'CREATION_DATE,'
||'CREATED_BY) '
||'SELECT mil2.inventory_item_id,'
||'NVL(ms.TRANSACTION_ID,-1) TRANSACTION_ID,'
||'msw.OP_SEQ_NUM,'
||'msw.ORGANIZATION_ID,'
||'-1 ,'
||'mil1.inventory_item_id,'
||'msw.RANK,'
||'msw.RATIO,'
||'msw.REQUEST_ID,'
||':v_last_collection_id,'
||':v_instance_id,'
||':v_current_date,'
||':v_current_user,'
||':v_current_date,'
||':v_current_user'
||' FROM MSC_ST_WO_SUB_COMP msw, '
||lv_supplies_tbl ||' ms, '
||' msc_item_id_lid MIL1,'
||' msc_item_id_lid MIL2 '
||'WHERE msw.SR_INSTANCE_ID= '
||MSC_CL_COLLECTION.v_instance_id
||' AND ms.plan_id = -1'
||' AND ms.SR_INSTANCE_ID = msw.SR_INSTANCE_ID'
||' AND ms.ORGANIZATION_ID = msw.ORGANIZATION_ID'
||' AND ms.DISPOSITION_ID = msw.WIP_ENTITY_ID'
||' AND ms.MAINTENANCE_OBJECT_SOURCE = 2'
||' AND ms.ORDER_TYPE = 70'
||' AND MIL1.sr_instance_id = msw.sr_instance_id'
||' AND MIL1.SR_INVENTORY_ITEM_ID = msw.PRIMARY_COMPONENT_ID'
||' AND MIL2.sr_instance_id = msw.sr_instance_id'
||' AND MIL2.SR_INVENTORY_ITEM_ID = msw.ALTERNATE_COMPONENT_ID';
'SELECT '
||' -1,'
||' MSC_SUPPLIES_S.NEXTVAL,'
||' md.DESIGNATOR_ID,'
||' ms.ORGANIZATION_ID,'
||' ms.NEW_WIP_START_DATE,'
||' ms.NEW_SCHEDULE_DATE,'
||' ''AGGR-'''||'||MSC_SUPPLIES_AGGR_WO_S.NEXTVAL,'-- CHECK FOR SEQ AND CHANGE THIS mnagilla
||' t1.INVENTORY_ITEM_ID,' -- mnagilla check for translation
||' ms.ORDER_TYPE,'
||' ms.COLL_ORDER_TYPE,'
||' ms.MAINTENANCE_OBJECT_SOURCE,'
||' ms.NEW_ORDER_QUANTITY,'
||' ms.to_be_exploded,'
||' ms.FIRM_PLANNED_TYPE,' --firm_planned_type
||' 17,'
||' ms.SR_INSTANCE_ID,'
||' ms.PRODUCT_CLASSIFICATION,'
||' ms.OPERATING_FLEET ,'
||' ms.MAINTENANCE_REQUIREMENT,'
||' ms.SOURCE_ITEM_ID,'
||' :v_last_collection_id,'
||' :v_current_date,'
||' :v_current_user,'
||' :v_current_date,'
||' :v_current_user '
||' FROM MSC_ITEM_ID_LID t1,'
||' MSC_ST_SUPPLIES ms,'
||' MSC_DESIGNATORS md'
||' WHERE t1.SR_INVENTORY_ITEM_ID= -1003'
||' AND t1.SR_INSTANCE_ID= ms.SR_INSTANCE_ID'
||' AND ms.DELETED_FLAG = '|| MSC_UTIL.SYS_NO
||' AND ms.SCHEDULE_DESIGNATOR_ID = md.SRC_SIM_FCST_ID'
||' AND md.SR_INSTANCE_ID= ms.SR_INSTANCE_ID'
||' AND md.DESIGNATOR_TYPE = 13'
||' AND md.organization_id = -23453'
||' AND ms.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
||' AND ms.ORDER_TYPE = 92'
||' AND ms.MAINTENANCE_OBJECT_SOURCE = 2'
;
'INSERT INTO '||lv_supplies_tbl
||' ( PLAN_ID,'
||' TRANSACTION_ID,'
||' SCHEDULE_DESIGNATOR_ID,'
||' ORGANIZATION_ID,'
||' NEW_WIP_START_DATE,'
||' NEW_SCHEDULE_DATE,'
||' ORDER_NUMBER,'
||' INVENTORY_ITEM_ID,'
||' ORDER_TYPE,'
||' COLL_ORDER_TYPE,'
||' MAINTENANCE_OBJECT_SOURCE,'
||' NEW_ORDER_QUANTITY,'
||' to_be_exploded,'
||' FIRM_PLANNED_TYPE, '
||' WIP_STATUS_CODE,'
||' SR_INSTANCE_ID, '
||' PRODUCT_CLASSIFICATION,'
||' OPERATING_FLEET ,'
||' MAINTENANCE_REQUIREMENT,'
||' SOURCE_ITEM_ID,'
||' REFRESH_NUMBER, '
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY) '
|| lv_cursor_stmt;
'The Insert statement is: ' || lv_sql_stmt);
'The number of rows inserted is: ' || SQL%ROWCOUNT);
'SELECT '
||' -1,'
||' MSC_DEMANDS_S.NEXTVAL,'
||' ms.TRANSACTION_ID DISPOSITION_ID ,'
||' substr(ms.ORDER_NUMBER, 1,62) ORDER_NUMBER, '
||' ms.SCHEDULE_DESIGNATOR_ID,'
||' md.ORGANIZATION_ID,'
||' t1.INVENTORY_ITEM_ID,'
||' ms.INVENTORY_ITEM_ID USING_ASSEMBLY_ITEM_ID,'
||' md.USING_REQUIREMENT_QUANTITY,'
||' md.USING_ASSEMBLY_DEMAND_DATE,'
||' md.SR_INSTANCE_ID,'
||' md.DEMAND_TYPE, '
||' md.ORIGINATION_TYPE,'
||' md.MAINTENANCE_OBJECT_SOURCE,'
||' 1,'
||' :v_last_collection_id,'
||' :v_current_date,'
||' :v_current_user,'
||' :v_current_date,'
||' :v_current_user '
||' FROM MSC_ITEM_ID_LID t1,'
||' MSC_DESIGNATORS md1 ,'
||' MSC_ST_DEMANDS md,'
|| lv_supplies_tbl||' ms'
||' WHERE ms.MAINTENANCE_OBJECT_SOURCE = 2'
--||' AND ms.DELETED_FLAG ='|| MSC_UTIL.SYS_NO
||' AND ms.ORDER_TYPE = 92 '
||' AND md.SR_INSTANCE_ID= '|| MSC_CL_COLLECTION.v_instance_id
||' AND t1.SR_INVENTORY_ITEM_ID= md.inventory_item_id '
||' AND t1.sr_instance_id= '|| MSC_CL_COLLECTION.v_instance_id
||' AND ms.SCHEDULE_DESIGNATOR_ID = md1.DESIGNATOR_ID'
||' AND md.MAINTENANCE_OBJECT_SOURCE = 2'
||' AND ms.SOURCE_ITEM_ID = md.USING_ASSEMBLY_ITEM_ID'
||' AND nvl(md.OPERATING_FLEET ,1)= nvl(ms.OPERATING_FLEET,1)'
||' AND md.MAINTENANCE_REQUIREMENT = ms.MAINTENANCE_REQUIREMENT'
||' AND md.ORIGINATION_TYPE = 92'
||' AND ms.PLAN_ID = -1'
||' AND ms.NEW_WIP_START_DATE = md.USING_ASSEMBLY_DEMAND_DATE'
||' AND ms.ORGANIZATION_ID = md.ORGANIZATION_ID'
||' AND md.SCHEDULE_DESIGNATOR_ID = md1.SRC_SIM_FCST_ID'
||' AND md.SR_INSTANCE_ID= md1.SR_INSTANCE_ID'
||' AND md.SR_INSTANCE_ID= ms.SR_INSTANCE_ID'
||' AND md1.DESIGNATOR_TYPE = 13'
||' AND md1.organization_id = -23453'
;
'INSERT INTO '||lv_tbl
||' ( PLAN_ID,'
||' DEMAND_ID,'
||' DISPOSITION_ID,'
||' ORDER_NUMBER,'
||' SCHEDULE_DESIGNATOR_ID,'
||' ORGANIZATION_ID,'
||' INVENTORY_ITEM_ID,'
||' USING_ASSEMBLY_ITEM_ID,'
||' USING_REQUIREMENT_QUANTITY,'
||' USING_ASSEMBLY_DEMAND_DATE, '
||' SR_INSTANCE_ID, '
||' DEMAND_TYPE, '
||' ORIGINATION_TYPE, '
||' MAINTENANCE_OBJECT_SOURCE,'
||' OP_SEQ_NUM,'
||' REFRESH_NUMBER, '
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY) '
|| lv_cursor_stmt ;
'SELECT '
||' -1, '
||' MSC_RESOURCE_REQUIREMENTS_S.NEXTVAL,'
||' ms.TRANSACTION_ID ,'
||' mrr.DEPARTMENT_ID,'
||' mrr.ORGANIZATION_ID,'
||' mrr.RESOURCE_ID,'
||' mrr.ASSIGNED_UNITS,'
||' mrr.OPERATION_HOURS_REQUIRED,'
||' mrr.TOUCH_TIME,'
||' mrr.UNADJUSTED_RESOURCE_HOURS,'
||' mrr.OPERATION_HOURS_REQUIRED,'
||' ms.NEW_WIP_START_DATE START_DATE,'
||' 92,'
||' mrr.SR_INSTANCE_ID,'
||' LAST_DAY(NEW_WIP_START_DATE) END_DATE,'
||' mrr.MAINTENANCE_OBJECT_SOURCE,'
||' 1,'
||' 1,'
||' 1,'
||' :v_last_collection_id,'
||' :v_current_date,'
||' :v_current_user,'
||' :v_current_date,'
||' :v_current_user '
||' FROM MSC_ST_RESOURCE_REQUIREMENTS mrr, '
||' MSC_DESIGNATORS md1 ,'
|| lv_supplies_tbl||' ms'
||' WHERE ms.MAINTENANCE_OBJECT_SOURCE = 2'
--||' AND mrr.DELETED_FLAG ='|| MSC_UTIL.SYS_NO
||' AND ms.ORDER_TYPE = 92'
||' AND mrr.MAINTENANCE_OBJECT_SOURCE = 2'
||' AND nvl(mrr.OPERATING_FLEET,1) = nvl(ms.OPERATING_FLEET,1)'
||' AND mrr.MAINTENANCE_REQUIREMENT = ms.MAINTENANCE_REQUIREMENT'
--||' AND ms.DELETED_FLAG ='|| MSC_UTIL.SYS_NO
||' AND ms.PLAN_ID = -1'
||' AND mrr.supply_type = 92'
||' AND ms.SCHEDULE_DESIGNATOR_ID = md1.DESIGNATOR_ID'
||' AND ms.SOURCE_ITEM_ID =mrr.INVENTORY_ITEM_ID'
||' AND ms.NEW_WIP_START_DATE = mrr.START_DATE'
||' AND ms.ORGANIZATION_ID = mrr.ORGANIZATION_ID'
||' AND mrr.SR_INSTANCE_ID='|| MSC_CL_COLLECTION.v_instance_id
||' AND mrr.SCHEDULE_DESIGNATOR_ID = md1.SRC_SIM_FCST_ID'
||' AND mrr.SR_INSTANCE_ID= md1.SR_INSTANCE_ID'
||' AND mrr.SR_INSTANCE_ID= ms.SR_INSTANCE_ID'
||' AND md1.DESIGNATOR_TYPE = 13'
||' AND md1.organization_id = -23453';
lv_sql_stmt:= 'INSERT INTO '||lv_tbl
||' ( PLAN_ID,'
||' TRANSACTION_ID,'
||' SUPPLY_ID,'
||' DEPARTMENT_ID,'
||' ORGANIZATION_ID,'
||' RESOURCE_ID,'
||' ASSIGNED_UNITS, '
||' RESOURCE_HOURS,'
||' TOUCH_TIME,'
||' UNADJUSTED_RESOURCE_HOURS,'
||' TOTAL_RESOURCE_HOURS,'
||' START_DATE,'
||' SUPPLY_TYPE,'
||' SR_INSTANCE_ID, '
||' END_DATE,'
||' MAINTENANCE_OBJECT_SOURCE,'
||' OPERATION_SEQ_NUM,'
||' RESOURCE_SEQ_NUM,'
||' SCHEDULE_FLAG,'
||' REFRESH_NUMBER, '
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY) '
|| lv_cursor_stmt ;
SELECT TRANSACTION_ID,
DISPOSITION_TYPE,
ORGANIZATION_ID,
RESERVED_QUANTITY,
SUPPLY_SOURCE_TYPE_ID,
SUPPLY_SOURCE_HEADER_ID,
DEMAND_SOURCE_LINE_ID,
RESERVATION_TYPE,
REQUIREMENT_DATE,
SUBINVENTORY,
TASK_ID,
PROJECT_ID
FROM MSC_ST_RESERVATIONS
WHERE deleted_flag = MSC_UTIL.SYS_NO
AND SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id;
SELECT TRANSACTION_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID
FROM MSC_ST_RESERVATIONS
WHERE deleted_flag = MSC_UTIL.SYS_YES
AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS',
MSC_CL_COLLECTION.v_instance_id,
-1);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS',
MSC_CL_COLLECTION.v_instance_id,
-1,
v_sub_str);
DELETE MSC_RESERVATIONS
WHERE TRANSACTION_ID = c_rec.TRANSACTION_ID
AND SR_INSTANCE_ID = c_rec.sr_instance_id
AND ORGANIZATION_ID = c_rec.organization_id
AND RESERVATION_TYPE in (15,16,17);
'INSERT INTO MSC_RESERVATIONS'
||'(TRANSACTION_ID,'
||'DISPOSITION_ID,'
||'DISPOSITION_TYPE,'
||'ORGANIZATION_ID,'
||'INVENTORY_ITEM_ID,'
||'RESERVATION_TYPE,'
||'REQUIREMENT_DATE,'
||'RESERVED_QUANTITY,'
||'RESERVATION_DATE,'
||'SUBINVENTORY,'
||'TASK_ID,'
||'PROJECT_ID,'
||'SUPPLY_SOURCE_TYPE_ID,'
||'SUPPLY_SOURCE_HEADER_ID,'
||'DEMAND_SOURCE_LINE_ID,'
||'NONNET_QUANTITY_RESERVED,'
||'PLAN_ID,'
||'REFRESH_NUMBER,'
||'SR_INSTANCE_ID,'
||'LAST_UPDATE_DATE,'
||'LAST_UPDATED_BY,'
||'CREATION_DATE,'
||'CREATED_BY) '
||'SELECT ms.TRANSACTION_ID,'
||'ms.DISPOSITION_ID,'
||'ms.DISPOSITION_TYPE,'
||'ms.ORGANIZATION_ID,'
||'mil.INVENTORY_ITEM_ID,'
||'ms.RESERVATION_TYPE,'
||'ms.REQUIREMENT_DATE,'
||'ms.RESERVED_QUANTITY,'
||'ms.REQUIREMENT_DATE,'
||'ms.SUBINVENTORY,'
||'ms.TASK_ID,'
||'ms.PROJECT_ID,'
||'ms.SUPPLY_SOURCE_TYPE_ID,'
||'ms.SUPPLY_SOURCE_HEADER_ID,'
||'ms.DEMAND_SOURCE_LINE_ID,'
||'0,'
||'-1,'
||':v_last_collection_id,'
||':v_instance_id,'
||':v_current_date,'
||':v_current_user,'
||':v_current_date,'
||':v_current_user'
||' FROM MSC_ST_RESERVATIONS ms,'
||' MSC_ITEM_ID_LID mil '
||' WHERE ms.SR_INSTANCE_ID= '
||MSC_CL_COLLECTION.v_instance_id
||' AND mil.sr_instance_id = ms.sr_instance_id'
||' AND mil.SR_INVENTORY_ITEM_ID = ms.INVENTORY_ITEM_ID';
UPDATE MSC_RESERVATIONS
SET
DISPOSITION_TYPE = c_rec.DISPOSITION_TYPE,
REQUIREMENT_DATE = c_rec.REQUIREMENT_DATE,
SUBINVENTORY = c_rec.SUBINVENTORY,
TASK_ID = c_rec.TASK_ID,
PROJECT_ID = c_rec.PROJECT_ID,
RESERVED_QUANTITY = c_rec.RESERVED_QUANTITY,
SUPPLY_SOURCE_TYPE_ID = c_rec.SUPPLY_SOURCE_TYPE_ID,
SUPPLY_SOURCE_HEADER_ID = c_rec.SUPPLY_SOURCE_HEADER_ID,
DEMAND_SOURCE_LINE_ID = c_rec.DEMAND_SOURCE_LINE_ID,
RESERVATION_TYPE = c_rec.RESERVATION_TYPE,
REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND TRANSACTION_ID = c_rec.TRANSACTION_ID
AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID
AND RESERVATION_TYPE in (15,16,17);
SELECT t1.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
t1.INVENTORY_ITEM_ID asset_item_id,
ms.visit_id,
ms.ORGANIZATION_ID,
ms.DISPOSITION_ID,
ms.ORDER_NUMBER,
ms.NEW_ORDER_QUANTITY,
ms.NEW_SCHEDULE_DATE,
ms.EXPECTED_SCRAP_QTY,
ms.QTY_SCRAPPED,
ms.QTY_COMPLETED,
ms.FIRM_PLANNED_TYPE,
ms.NEW_WIP_START_DATE,
ms.ORDER_TYPE,
ms.PROJECT_ID,
ms.TASK_ID,
ms.SCHEDULE_GROUP_ID,
ms.BUILD_SEQUENCE,
ms.LINE_ID,
ms.ALTERNATE_BOM_DESIGNATOR,
ms.ALTERNATE_ROUTING_DESIGNATOR,
ms.UNIT_NUMBER,
ms.WIP_STATUS_CODE,
ms.DEMAND_CLASS,
ms.COPRODUCTS_SUPPLY,
ms.REQUESTED_START_DATE,
ms.REQUESTED_COMPLETION_DATE,
ms.SCHEDULE_PRIORITY,
ms.ACTUAL_START_DATE,
ms.CFM_ROUTING_FLAG,
ms.WIP_START_QUANTITY,
ms.MAINTENANCE_OBJECT_SOURCE,
ms.DESCRIPTION,
ms.MAINTENANCE_OBJECT_TYPE,
ms.REFRESH_ID,
ms.SR_INSTANCE_ID
FROM MSC_ST_SUPPLIES ms,
MSC_ITEM_ID_LID t1
WHERE t1.SR_INVENTORY_ITEM_ID= ms.INVENTORY_ITEM_ID
AND t1.SR_INSTANCE_ID= ms.SR_INSTANCE_ID
AND ms.deleted_flag = MSC_UTIL.SYS_NO
AND ms.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND ms.order_type=90;
SELECT DISPOSITION_ID,
ORDER_TYPE,
ORGANIZATION_ID,
SR_INSTANCE_ID
FROM MSC_ST_SUPPLIES
WHERE deleted_flag = MSC_UTIL.SYS_YES
AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND order_type=90;
lv_cursor_stmt:= ' SELECT '
||' -1, MSC_SUPPLIES_S.NEXTVAL,'
||' t1.INVENTORY_ITEM_ID,'
||' t1.INVENTORY_ITEM_ID,'
||' ms.visit_id,'
||' ms.ORGANIZATION_ID,'
||' ms.DISPOSITION_ID,'
||' ms.ORDER_NUMBER,'
||' ms.NEW_ORDER_QUANTITY,'
||' ms.NEW_SCHEDULE_DATE,'
||' ms.EXPECTED_SCRAP_QTY,'
||' ms.QTY_SCRAPPED,'
||' ms.QTY_COMPLETED,'
||' ms.FIRM_PLANNED_TYPE,'
||' ms.NEW_WIP_START_DATE,'
||' ms.ORDER_TYPE,'
||' ms.PROJECT_ID,'
||' ms.TASK_ID,'
||' ms.SCHEDULE_GROUP_ID,'
||' ms.BUILD_SEQUENCE,'
||' ms.LINE_ID,'
||' ms.ALTERNATE_BOM_DESIGNATOR,'
||' ms.ALTERNATE_ROUTING_DESIGNATOR,'
||' ms.UNIT_NUMBER,'
||' ms.WIP_STATUS_CODE,'
||' ms.DEMAND_CLASS,'
||' ms.COPRODUCTS_SUPPLY,'
||' ms.REQUESTED_START_DATE,'
||' ms.REQUESTED_COMPLETION_DATE,'
||' ms.SCHEDULE_PRIORITY,'
||' ms.ACTUAL_START_DATE,'
||' ms.CFM_ROUTING_FLAG,'
||' ms.WIP_START_QUANTITY,'
||' ms.MAINTENANCE_OBJECT_SOURCE,'
||' ms.DESCRIPTION,'
||' ms.MAINTENANCE_OBJECT_TYPE,'
||' :v_last_collection_id,'
||' :v_instance_id,'
||' :v_current_date, '
||' :v_current_user, '
||' :v_current_date, '
||' :v_current_user '
||' FROM MSC_ITEM_ID_LID t1, '
||' MSC_ST_SUPPLIES ms '
||'WHERE t1.SR_INVENTORY_ITEM_ID= ms.INVENTORY_ITEM_ID '
||' AND t1.SR_INSTANCE_ID= ms.SR_INSTANCE_ID '
||' AND ms.deleted_flag = '||MSC_UTIL.SYS_NO
||' AND ms.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
||' AND ms.order_type=90';
'INSERT INTO '||lv_tbl
||' ( PLAN_ID, TRANSACTION_ID,'
||' INVENTORY_ITEM_ID,'
||' asset_item_id,'
||' visit_id,'
||' ORGANIZATION_ID,'
||' DISPOSITION_ID,'
||' ORDER_NUMBER,'
||' NEW_ORDER_QUANTITY,'
||' NEW_SCHEDULE_DATE,'
||' EXPECTED_SCRAP_QTY,'
||' QTY_SCRAPPED,'
||' QTY_COMPLETED,'
||' FIRM_PLANNED_TYPE,'
||' NEW_WIP_START_DATE,'
||' ORDER_TYPE,'
||' PROJECT_ID,'
||' TASK_ID,'
||' SCHEDULE_GROUP_ID,'
||' BUILD_SEQUENCE,'
||' LINE_ID,'
||' ALTERNATE_BOM_DESIGNATOR,'
||' ALTERNATE_ROUTING_DESIGNATOR,'
||' UNIT_NUMBER,'
||' WIP_STATUS_CODE,'
||' DEMAND_CLASS,'
||' COPRODUCTS_SUPPLY,'
||' REQUESTED_START_DATE,'
||' REQUESTED_COMPLETION_DATE,'
||' SCHEDULE_PRIORITY,'
||' ACTUAL_START_DATE,'
||' CFM_ROUTING_FLAG,'
||' WIP_START_QUANTITY,'
||' MAINTENANCE_OBJECT_SOURCE,'
||' DESCRIPTION,'
||' MAINTENANCE_OBJECT_TYPE,'
||' REFRESH_NUMBER,'
||' SR_INSTANCE_ID,'
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY) '||lv_cursor_stmt;
DELETE MSC_SUPPLIES
WHERE PLAN_ID= -1
AND SR_INSTANCE_ID= c_rec.sr_instance_id
AND DISPOSITION_ID= c_rec.DISPOSITION_ID
AND ORDER_TYPE= c_rec.ORDER_TYPE
AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
UPDATE MSC_SUPPLIES
SET
NEW_ORDER_QUANTITY = c_rec.NEW_ORDER_QUANTITY,
NEW_SCHEDULE_DATE = c_rec.NEW_SCHEDULE_DATE,
EXPECTED_SCRAP_QTY = c_rec.EXPECTED_SCRAP_QTY,
QTY_SCRAPPED = c_rec.QTY_SCRAPPED,
QTY_COMPLETED = c_rec.QTY_COMPLETED,
FIRM_PLANNED_TYPE = c_rec.FIRM_PLANNED_TYPE,
NEW_WIP_START_DATE = c_rec.NEW_WIP_START_DATE,
PROJECT_ID = c_rec.PROJECT_ID,
TASK_ID = c_rec.TASK_ID,
SCHEDULE_GROUP_ID = c_rec.SCHEDULE_GROUP_ID,
BUILD_SEQUENCE = c_rec.BUILD_SEQUENCE,
LINE_ID = c_rec.LINE_ID,
ALTERNATE_BOM_DESIGNATOR = c_rec.ALTERNATE_BOM_DESIGNATOR,
ALTERNATE_ROUTING_DESIGNATOR = c_rec.ALTERNATE_ROUTING_DESIGNATOR,
UNIT_NUMBER = c_rec.UNIT_NUMBER,
WIP_STATUS_CODE = c_rec.WIP_STATUS_CODE,
DEMAND_CLASS = c_rec.DEMAND_CLASS,
COPRODUCTS_SUPPLY = c_rec.COPRODUCTS_SUPPLY,
REQUESTED_START_DATE = c_rec.REQUESTED_START_DATE,
REQUESTED_COMPLETION_DATE = c_rec.REQUESTED_COMPLETION_DATE,
SCHEDULE_PRIORITY = c_rec.SCHEDULE_PRIORITY,
ACTUAL_START_DATE = c_rec.ACTUAL_START_DATE,
CFM_ROUTING_FLAG = c_rec.CFM_ROUTING_FLAG,
WIP_START_QUANTITY = c_rec.WIP_START_QUANTITY,
DESCRIPTION = c_rec.DESCRIPTION
WHERE PLAN_ID= -1
AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND DISPOSITION_ID= c_rec.DISPOSITION_ID
AND ORDER_TYPE= c_rec.ORDER_TYPE
AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
v_sql_stmt:='INSERT INTO '||lv_tbl
||' ( PLAN_ID,TRANSACTION_ID,'
||' INVENTORY_ITEM_ID,'
||' asset_item_id,'
||' visit_id,'
||' ORGANIZATION_ID,'
||' DISPOSITION_ID,'
||' ORDER_NUMBER,'
||' NEW_ORDER_QUANTITY,'
||' NEW_SCHEDULE_DATE,'
||' EXPECTED_SCRAP_QTY,'
||' QTY_SCRAPPED,'
||' QTY_COMPLETED,'
||' FIRM_PLANNED_TYPE,'
||' NEW_WIP_START_DATE,'
||' ORDER_TYPE,'
||' PROJECT_ID,'
||' TASK_ID,'
||' SCHEDULE_GROUP_ID,'
||' BUILD_SEQUENCE,'
||' LINE_ID,'
||' ALTERNATE_BOM_DESIGNATOR,'
||' ALTERNATE_ROUTING_DESIGNATOR,'
||' UNIT_NUMBER,'
||' WIP_STATUS_CODE,'
||' DEMAND_CLASS,'
||' COPRODUCTS_SUPPLY,'
||' REQUESTED_START_DATE,'
||' REQUESTED_COMPLETION_DATE,'
||' SCHEDULE_PRIORITY,'
||' ACTUAL_START_DATE,'
||' CFM_ROUTING_FLAG,'
||' WIP_START_QUANTITY,'
||' MAINTENANCE_OBJECT_SOURCE,'
||' DESCRIPTION,'
||' MAINTENANCE_OBJECT_TYPE,'
||' REFRESH_NUMBER,'
||' SR_INSTANCE_ID,'
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY) '
||' values ( -1,MSC_SUPPLIES_S.NEXTVAL,'
||' :v_INVENTORY_ITEM_ID,'
||' :v_asset_item_id,'
||' :v_visit_id,'
||' :v_ORGANIZATION_ID,'
||' :v_DISPOSITION_ID,'
||' :v_ORDER_NUMBER,'
||' :v_NEW_ORDER_QUANTITY,'
||' :v_NEW_SCHEDULE_DATE,'
||' :v_EXPECTED_SCRAP_QTY,'
||' :v_QTY_SCRAPPED,'
||' :v_QTY_COMPLETED,'
||' :v_FIRM_PLANNED_TYPE,'
||' :v_NEW_WIP_START_DATE,'
||' :v_ORDER_TYPE,'
||' :v_PROJECT_ID,'
||' :v_TASK_ID,'
||' :v_SCHEDULE_GROUP_ID,'
||' :v_BUILD_SEQUENCE,'
||' :v_LINE_ID,'
||' :v_ALTERNATE_BOM_DESIGNATOR,'
||' :v_ALTERNATE_ROUTING_DESIGNATOR,'
||' :v_UNIT_NUMBER,'
||' :v_WIP_STATUS_CODE,'
||' :v_DEMAND_CLASS,'
||' :v_COPRODUCTS_SUPPLY,'
||' :v_REQUESTED_START_DATE,'
||' :v_REQUESTED_COMPLETION_DATE,'
||' :v_SCHEDULE_PRIORITY,'
||' :v_ACTUAL_START_DATE,'
||' :v_CFM_ROUTING_FLAG,'
||' :v_WIP_START_QUANTITY,'
||' :v_MAINTENANCE_OBJECT_SOURCE,'
||' :v_DESCRIPTION,'
||' :v_MAINTENANCE_OBJECT_TYPE,'
||' :v_last_collection_id,'
||' :v_instance_id,'
||' :v_current_date, '
||' :v_current_user, '
||' :v_current_date, '
||' :v_current_user )';
'SELECT'
||' -1,'
||' MSC_SUPPLIES_S.NEXTVAL,'
||' ms.ORGANIZATION_ID,'
--||' ms.NEW_WIP_START_DATE,'
||' ms.NEW_SCHEDULE_DATE,'
||' ms.NEW_ORDER_QUANTITY,'
||' ms.ORDER_NUMBER,'
||' t1.INVENTORY_ITEM_ID,'
||' ms.ORDER_TYPE,'
||' ms.COLL_ORDER_TYPE,'
||' ms.FIRM_PLANNED_TYPE,' --firm_planned_type
||' tp.TP_ID,'
||' tps.TP_SITE_ID,'
||' ms.PRODUCES_TO_STOCK,'
||' ms.ITEM_TYPE_ID,'
||' ms.ITEM_TYPE_VALUE,'
||' ms.SR_INSTANCE_ID,'
||' :v_last_collection_id,'
||' :v_current_date,'
||' :v_current_user,'
||' :v_current_date,'
||' :v_current_user '
||' FROM MSC_ST_SUPPLIES ms,'
||' MSC_ITEM_ID_LID t1,'
||' MSC_TP_ID_LID tp,'
||' MSC_TP_SITE_ID_LID tps'
||' WHERE t1.SR_INVENTORY_ITEM_ID= ms.INVENTORY_ITEM_ID'
||' AND t1.SR_INSTANCE_ID= ms.SR_INSTANCE_ID'
||' AND ms.deleted_flag = ' || MSC_UTIL.SYS_NO
||' AND ms.SR_INSTANCE_ID= '|| MSC_CL_COLLECTION.v_instance_id
||' AND ms.order_type=74'
||' AND ms.COLL_ORDER_TYPE = 974'
||' AND tp.SR_TP_ID(+)= ms.SUPPLIER_ID'
||' AND tp.SR_INSTANCE_ID(+)= ms.SR_INSTANCE_ID'
||' AND tps.SR_TP_SITE_ID(+)= ms.SUPPLIER_SITE_ID'
||' AND tps.SR_INSTANCE_ID(+)= ms.SR_INSTANCE_ID' ;
'INSERT INTO '||lv_supplies_tbl
||' ( PLAN_ID,'
||' TRANSACTION_ID,'
||' ORGANIZATION_ID,'
--||' NEW_WIP_START_DATE,'
||' NEW_SCHEDULE_DATE,'
||' NEW_ORDER_QUANTITY,'
||' ORDER_NUMBER,'
||' INVENTORY_ITEM_ID,'
||' ORDER_TYPE,'
||' COLL_ORDER_TYPE,'
||' FIRM_PLANNED_TYPE, '
||' SUPPLIER_ID,'
||' SUPPLIER_SITE_ID,'
||' PRODUCES_TO_STOCK,'
||' ITEM_TYPE_ID,'
||' ITEM_TYPE_VALUE,'
||' SR_INSTANCE_ID, '
||' REFRESH_NUMBER, '
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY) '
|| lv_cursor_stmt;
'The Insert statement is: ' || lv_sql_stmt);
'The number of rows inserted is: ' || SQL%ROWCOUNT);