The following lines contain the word 'select', 'insert', 'update' or 'delete':
lv_insert_stmt VARCHAR2(10240);
/* 2201791 - select substr(order_number,1,62) since order_number is
defined as varchar(62) in msc_demands table */
lv_del_stmt :=
'Select mshr.repair_line_id,
T1.Inventory_item_id ,
mshr.Organization_id ,
mshr.Origination_type ,
mshr.SR_INSTANCE_ID
From MSC_ST_DEMANDS mshr ,
msc_item_id_lid t1
Where mshr.sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
||' And mshr.ro_status_code=''C''
And mshr.origination_type= 77
AND t1.SR_INVENTORY_ITEM_ID(+)= mshr.inventory_item_id
AND t1.sr_instance_id(+)= mshr.sr_instance_id ';
lv_del_stmt2 := 'SELECT
mshr.REPAIR_LINE_ID,
mshr.SR_INSTANCE_ID,
t1.INVENTORY_ITEM_ID,
mshr.ORGANIZATION_ID,
mshr.OPERATION_SEQ_NUM,
mshr.ORIGINATION_TYPE,
mshr.WIP_ENTITY_ID,
mshr.wip_entity_name
FROM MSC_ST_DEMANDS mshr,
msc_item_id_lid t1
WHERE mshr.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
||' AND mshr.DELETED_FLAG= '||MSC_UTIL.SYS_YES
||' AND mshr.ORIGINATION_TYPE=77
AND t1.SR_INVENTORY_ITEM_ID(+)= mshr.inventory_item_id
AND t1.sr_instance_id(+)= mshr.sr_instance_id ';
'SELECT'
||' -1, MSC_DEMANDS_S.nextval, '
||' NVL(ms.TRANSACTION_ID,-1) DISPOSITION_ID,'
||' t1.INVENTORY_ITEM_ID,'
||' msdd.ORGANIZATION_ID,'
||' t2.INVENTORY_ITEM_ID USING_ASSEMBLY_ITEM_ID,'
||' nvl(msdd.USING_ASSEMBLY_DEMAND_DATE,ms.new_schedule_date),'
||' msdd.USING_REQUIREMENT_QUANTITY,'
||' msdd.QUANTITY_PER_ASSEMBLY,'
||' msdd.QUANTITY_ISSUED,'
||' msdd.ASSEMBLY_DEMAND_COMP_DATE,'
||' msdd.DEMAND_TYPE,'
||' msdd.ORIGINATION_TYPE,'
||' msdd.SOURCE_ORGANIZATION_ID,'
||' msdd.RESERVATION_ID,'
||' msdd.OPERATION_SEQ_NUM,'
||' msdd.DEMAND_CLASS,'
||' msdd.REPETITIVE_SCHEDULE_ID,'
||' msdd.SR_INSTANCE_ID,'
||' msdd.PROJECT_ID,'
||' msdd.TASK_ID,'
||' msdd.PLANNING_GROUP,'
||' msdd.END_ITEM_UNIT_NUMBER, '
||' REPLACE(REPLACE(substr(msdd.ORDER_NUMBER,1,62),:v_chr10),:v_chr13) ORDER_NUMBER,'
||' REPAIR_LINE_ID ,'
||' msdd.WIP_ENTITY_ID,'
||' msdd.WIP_ENTITY_NAME,'
||' msdd.WIP_STATUS_CODE,'
||' msdd.WIP_SUPPLY_TYPE,'
||' t3.inventory_item_id ASSET_ITEM_ID,' /* ds change change*/
||' msdd.ASSET_SERIAL_NUMBER,' /* ds change change*/
||' msdd.COMPONENT_SCALING_TYPE,' /* Discrete Mfg Enahancements Bug 4492736 */
||' msdd.COMPONENT_YIELD_FACTOR,' /* Discrete Mfg Enahancements Bug 4479743 */
|| lv_data_sql_stmt
||' :v_last_collection_id,'
||' :v_current_date,'
||' :v_current_user,'
||' :v_current_date,'
||' :v_current_user '
||' FROM MSC_ITEM_ID_LID t1,'
||' MSC_ITEM_ID_LID t2,'
||' MSC_ITEM_ID_LID t3,'
|| lv_supplies_tbl||' ms,'
||' MSC_ST_DEMANDS msdd'
||' WHERE msdd.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
||' AND msdd.ORIGINATION_TYPE = 77' /* 50 eam demand: ds change change SRP Change 5909379*/
||' AND msdd.DELETED_FLAG= '||MSC_UTIL.SYS_NO
||' AND t1.SR_INVENTORY_ITEM_ID= msdd.inventory_item_id'
||' AND t1.sr_instance_id= msdd.SR_INSTANCE_ID'
||' AND t2.SR_INVENTORY_ITEM_ID= msdd.using_assembly_item_id'
||' AND t2.sr_instance_id= msdd.SR_INSTANCE_ID'
||' AND t3.SR_INVENTORY_ITEM_ID (+)= msdd.ASSET_ITEM_ID'
||' AND t3.sr_instance_id (+) = msdd.SR_INSTANCE_ID'
||' AND ms.sr_instance_id= msdd.SR_INSTANCE_ID'
||' AND ms.ORGANIZATION_ID= msdd.ORGANIZATION_ID'
||' AND ms.DISPOSITION_ID= msdd.repair_line_id '
||' AND ms.plan_id=-1'
||' AND ms.ORDER_TYPE= 75'; /* ds change change*/
'INSERT /*+ APPEND */ INTO '||lv_tbl
||'( PLAN_ID,'
||' DEMAND_ID,'
||' DISPOSITION_ID,'
||' INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' USING_ASSEMBLY_ITEM_ID,'
||' USING_ASSEMBLY_DEMAND_DATE,'
||' USING_REQUIREMENT_QUANTITY,'
||' QUANTITY_PER_ASSEMBLY,'
||' ISSUED_QUANTITY,'
||' ASSEMBLY_DEMAND_COMP_DATE,'
||' DEMAND_TYPE,'
||' ORIGINATION_TYPE,'
||' SOURCE_ORGANIZATION_ID,'
||' RESERVATION_ID,'
||' OP_SEQ_NUM,'
||' DEMAND_CLASS,'
||' REPETITIVE_SCHEDULE_ID,'
||' SR_INSTANCE_ID,'
||' PROJECT_ID,'
||' TASK_ID,'
||' PLANNING_GROUP,'
||' UNIT_NUMBER,'
||' ORDER_NUMBER,'
||' REPAIR_LINE_ID,'
||' WIP_ENTITY_ID,'
||' WIP_ENTITY_NAME,'
||' WIP_STATUS_CODE,'
||' WIP_SUPPLY_TYPE,'
||' ASSET_ITEM_ID,'
||' ASSET_SERIAL_NUMBER,'
||' COMPONENT_SCALING_TYPE,'
||' COMPONENT_YIELD_FACTOR,'
|| lv_sel_sql_stmt
||' REFRESH_NUMBER,'
||' LAST_UPDATE_DATE,'
||' LAST_UPDATED_BY,'
||' CREATION_DATE,'
||' CREATED_BY)'
|| lv_cursor_stmt;
Delete from msc_demands
WHERE PLAN_ID= -1
AND SR_INSTANCE_ID= lv_SR_INSTANCE_ID
AND ORIGINATION_TYPE= lv_ORIGINATION_TYPE
AND REPAIR_LINE_ID= lv_REPAIR_LINE_ID
AND ORGANIZATION_ID = lv_ORGANIZATION_ID
AND INVENTORY_ITEM_ID= NVL(lv_INVENTORY_ITEM_ID,INVENTORY_ITEM_ID);
Delete from msc_demands
WHERE PLAN_ID= -1
AND SR_INSTANCE_ID= lv_SR_INSTANCE_ID
AND ORIGINATION_TYPE= lv_ORIGINATION_TYPE
AND REPAIR_LINE_ID= lv_REPAIR_LINE_ID
AND OP_SEQ_NUM= NVL(lv_OPERATION_SEQ_NUM,OP_SEQ_NUM)
AND ORGANIZATION_ID = lv_ORGANIZATION_ID
AND INVENTORY_ITEM_ID= NVL(lv_INVENTORY_ITEM_ID,INVENTORY_ITEM_ID)
AND WIP_ENTITY_NAME = lv_WIP_ENTITY_NAME ;
Delete from msc_demands
WHERE PLAN_ID= -1
AND SR_INSTANCE_ID= lv_SR_INSTANCE_ID
AND ORIGINATION_TYPE= lv_ORIGINATION_TYPE
AND REPAIR_LINE_ID= lv_REPAIR_LINE_ID
AND OP_SEQ_NUM= NVL(lv_OPERATION_SEQ_NUM,OP_SEQ_NUM)
AND ORGANIZATION_ID = lv_ORGANIZATION_ID
AND INVENTORY_ITEM_ID= NVL(lv_INVENTORY_ITEM_ID,INVENTORY_ITEM_ID)
AND WIP_ENTITY_ID = lv_WIP_ENTITY_ID ;
/* Opening The cursor ... Perofrom Update ... If not found the n inser ... row operation */
OPEN c2 FOR lv_cursor_stmt USING MSC_CL_COLLECTION.v_chr10,
MSC_CL_COLLECTION.v_chr13,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user;
Update MSC_DEMANDS
Set
USING_ASSEMBLY_ITEM_ID = lv_USING_ASSEMBLY_ITEM_ID ,
USING_ASSEMBLY_DEMAND_DATE = lv_USING_ASSEMBLY_DEMAND_DATE ,
USING_REQUIREMENT_QUANTITY = lv_USING_REQUIREMENT_QUANTITY ,
QUANTITY_PER_ASSEMBLY = lv_QUANTITY_PER_ASSEMBLY ,
ISSUED_QUANTITY = lv_QUANTITY_ISSUED ,
ASSEMBLY_DEMAND_COMP_DATE = lv_ASSEMBLY_DEMAND_COMP_DATE ,
DEMAND_TYPE = lv_DEMAND_TYPE ,
SOURCE_ORGANIZATION_ID = lv_SOURCE_ORGANIZATION_ID ,
RESERVATION_ID = lv_RESERVATION_ID ,
DEMAND_CLASS = lv_DEMAND_CLASS ,
REPETITIVE_SCHEDULE_ID = lv_REPETITIVE_SCHEDULE_ID ,
PROJECT_ID = lv_PROJECT_ID ,
TASK_ID = lv_TASK_ID ,
PLANNING_GROUP = lv_PLANNING_GROUP ,
ORDER_NUMBER = lv_ORDER_NUMBER ,
WIP_STATUS_CODE = lv_WIP_STATUS_CODE ,
WIP_SUPPLY_TYPE = lv_WIP_SUPPLY_TYPE ,
ASSET_ITEM_ID = lv_ASSET_ITEM_ID ,
ASSET_SERIAL_NUMBER = lv_ASSET_SERIAL_NUMBER ,
COMPONENT_SCALING_TYPE = lv_COMPONENT_SCALING_TYPE ,
COMPONENT_YIELD_FACTOR = lv_COMPONENT_YIELD_FACTOR
WHERE PLAN_ID= -1
AND SR_INSTANCE_ID= lv_SR_INSTANCE_ID
AND ORIGINATION_TYPE= lv_ORIGINATION_TYPE
AND DISPOSITION_ID= lv_DISPOSITION_ID
AND OP_SEQ_NUM= lv_OPERATION_SEQ_NUM
AND ORGANIZATION_ID = lv_ORGANIZATION_ID
AND INVENTORY_ITEM_ID= lv_INVENTORY_ITEM_ID
AND WIP_ENTITY_NAME = lv_WIP_ENTITY_NAME
AND ITEM_TYPE_VALUE = lv_ITEM_TYPE_VALUE;
Update MSC_DEMANDS
Set
USING_ASSEMBLY_ITEM_ID = lv_USING_ASSEMBLY_ITEM_ID ,
USING_ASSEMBLY_DEMAND_DATE = lv_USING_ASSEMBLY_DEMAND_DATE ,
USING_REQUIREMENT_QUANTITY = lv_USING_REQUIREMENT_QUANTITY ,
QUANTITY_PER_ASSEMBLY = lv_QUANTITY_PER_ASSEMBLY ,
ISSUED_QUANTITY = lv_QUANTITY_ISSUED ,
ASSEMBLY_DEMAND_COMP_DATE = lv_ASSEMBLY_DEMAND_COMP_DATE ,
DEMAND_TYPE = lv_DEMAND_TYPE ,
SOURCE_ORGANIZATION_ID = lv_SOURCE_ORGANIZATION_ID ,
RESERVATION_ID = lv_RESERVATION_ID ,
DEMAND_CLASS = lv_DEMAND_CLASS ,
REPETITIVE_SCHEDULE_ID = lv_REPETITIVE_SCHEDULE_ID ,
PROJECT_ID = lv_PROJECT_ID ,
TASK_ID = lv_TASK_ID ,
PLANNING_GROUP = lv_PLANNING_GROUP ,
ORDER_NUMBER = lv_ORDER_NUMBER ,
WIP_STATUS_CODE = lv_WIP_STATUS_CODE ,
WIP_SUPPLY_TYPE = lv_WIP_SUPPLY_TYPE ,
ASSET_ITEM_ID = lv_ASSET_ITEM_ID ,
ASSET_SERIAL_NUMBER = lv_ASSET_SERIAL_NUMBER ,
COMPONENT_SCALING_TYPE = lv_COMPONENT_SCALING_TYPE ,
COMPONENT_YIELD_FACTOR = lv_COMPONENT_YIELD_FACTOR
WHERE PLAN_ID= -1
AND SR_INSTANCE_ID= lv_SR_INSTANCE_ID
AND ORIGINATION_TYPE= lv_ORIGINATION_TYPE
AND DISPOSITION_ID= lv_DISPOSITION_ID
AND OP_SEQ_NUM= lv_OPERATION_SEQ_NUM
AND ORGANIZATION_ID = lv_ORGANIZATION_ID
AND INVENTORY_ITEM_ID= lv_INVENTORY_ITEM_ID
AND WIP_ENTITY_ID = lv_WIP_ENTITY_ID ;
lv_insert_stmt:=
'INSERT INTO '||lv_tbl
||'( PLAN_ID,'
||' DEMAND_ID,'
||' INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' USING_ASSEMBLY_ITEM_ID,'
||' USING_ASSEMBLY_DEMAND_DATE,'
||' USING_REQUIREMENT_QUANTITY,'
||' QUANTITY_PER_ASSEMBLY,'
||' ISSUED_QUANTITY,'
||' ASSEMBLY_DEMAND_COMP_DATE,'
||' DEMAND_TYPE,'
||' ORIGINATION_TYPE,'
||' SOURCE_ORGANIZATION_ID,'
||' DISPOSITION_ID,'
||' RESERVATION_ID,'
||' OP_SEQ_NUM,'
||' DEMAND_CLASS,'
||' SR_INSTANCE_ID,'
||' PROJECT_ID,'
||' TASK_ID,'
||' PLANNING_GROUP,'
||' UNIT_NUMBER,'
||' ORDER_NUMBER,'
||' REPAIR_LINE_ID,'
||' WIP_ENTITY_ID,'
||' WIP_ENTITY_NAME,'
||' WIP_STATUS_CODE,'
||' WIP_SUPPLY_TYPE,'
||' REPETITIVE_SCHEDULE_ID,'
||' ASSET_ITEM_ID,'
||' ASSET_SERIAL_NUMBER,'
||' COMPONENT_SCALING_TYPE,'
||' COMPONENT_YIELD_FACTOR,'
||' ITEM_TYPE_ID,'
||' ITEM_TYPE_VALUE,'
||' REFRESH_NUMBER,'
||' LAST_UPDATE_DATE,'
||' LAST_UPDATED_BY,'
||' CREATION_DATE,'
||' CREATED_BY)'
||'VALUES'
||'( -1,'
||' MSC_DEMANDS_S.nextval,'
||' :INVENTORY_ITEM_ID,'
||' :ORGANIZATION_ID,'
||' :USING_ASSEMBLY_ITEM_ID,'
||' :USING_ASSEMBLY_DEMAND_DATE,'
||' :USING_REQUIREMENT_QUANTITY,'
||' :QUANTITY_PER_ASSEMBLY,'
||' :ISSUED_QUANTITY,'
||' :ASSEMBLY_DEMAND_COMP_DATE,'
||' :DEMAND_TYPE,'
||' :ORIGINATION_TYPE,'
||' :SOURCE_ORGANIZATION_ID,'
||' :DISPOSITION_ID,'
||' :RESERVATION_ID,'
||' :OPERATION_SEQ_NUM,'
||' :DEMAND_CLASS,'
||' :SR_INSTANCE_ID,'
||' :PROJECT_ID,'
||' :TASK_ID,'
||' :PLANNING_GROUP,'
||' :END_ITEM_UNIT_NUMBER, '
||' :ORDER_NUMBER,'
||' :REPAIR_LINE_ID,'
||' :WIP_ENTITY_ID,'
||' :WIP_ENTITY_NAME,'
||' :WIP_STATUS_CODE,'
||' :WIP_SUPPLY_TYPE,'
||' :REPETITIVE_SCHEDULE_ID,'
||' :ASSET_ITEM_ID,'
||' :ASSET_SERIAL_NUMBER,'
||' :COMPONENT_SCALING_TYPE,'
||' :COMPONENT_YIELD_FACTOR,'
||' :ITEM_TYPE_ID,'
||' :ITEM_TYPE_VALUE,'
||' :v_last_collection_id,'
||' :v_current_date,'
||' :v_current_user,'
||' :v_current_date,'
||' :v_current_user )';
EXECUTE IMMEDIATE lv_insert_stmt
USING
lv_INVENTORY_ITEM_ID,
lv_ORGANIZATION_ID,
lv_USING_ASSEMBLY_ITEM_ID,
lv_USING_ASSEMBLY_DEMAND_DATE,
lv_USING_REQUIREMENT_QUANTITY,
lv_QUANTITY_PER_ASSEMBLY,
lv_QUANTITY_ISSUED,
lv_ASSEMBLY_DEMAND_COMP_DATE,
lv_DEMAND_TYPE,
lv_ORIGINATION_TYPE,
lv_SOURCE_ORGANIZATION_ID,
lv_DISPOSITION_ID,
lv_RESERVATION_ID,
lv_OPERATION_SEQ_NUM,
lv_DEMAND_CLASS,
lv_SR_INSTANCE_ID,
lv_PROJECT_ID,
lv_TASK_ID,
lv_PLANNING_GROUP,
lv_END_ITEM_UNIT_NUMBER,
lv_ORDER_NUMBER,
lv_REPAIR_LINE_ID,
lv_WIP_ENTITY_ID,
lv_WIP_ENTITY_NAME,
lv_WIP_STATUS_CODE,
lv_WIP_SUPPLY_TYPE,
lv_REPETITIVE_SCHEDULE_ID,
lv_ASSET_ITEM_ID,
lv_ASSET_SERIAL_NUMBER,
lv_COMPONENT_SCALING_TYPE,
lv_COMPONENT_YIELD_FACTOR,
lv_ITEM_TYPE_ID,
lv_ITEM_TYPE_VALUE,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user;
SELECT msd.WIP_ENTITY_ID,
msd.OPERATION_SEQ_NUM,
t1.INVENTORY_ITEM_ID,
msd.ORIGINATION_TYPE,
msd.SR_INSTANCE_ID,
msd.ORGANIZATION_ID
FROM MSC_ITEM_ID_LID t1,
MSC_ST_DEMANDS msd
WHERE msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND msd.ORIGINATION_TYPE = 77
AND msd.DELETED_FLAG= MSC_UTIL.SYS_YES
AND t1.SR_INVENTORY_ITEM_ID(+)= msd.inventory_item_id
AND t1.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id;*/
lv_DELETED_FLAG NUMBER;
lv_del_stmt := 'SELECT msd1.WIP_ENTITY_ID,
msd1.OPERATION_SEQ_NUM,
t1.INVENTORY_ITEM_ID,
msd1.ORIGINATION_TYPE,
msd1.SR_INSTANCE_ID,
msd1.ORGANIZATION_ID
FROM MSC_ITEM_ID_LID t1,
MSC_ST_DEMANDS msd1
WHERE msd1.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
||' AND msd1.ORIGINATION_TYPE = 77
AND msd1.DELETED_FLAG= '|| MSC_UTIL.SYS_YES
||' AND t1.SR_INVENTORY_ITEM_ID(+)= msd1.inventory_item_id
AND t1.sr_instance_id(+)= '||MSC_CL_COLLECTION.v_instance_id ;
UPDATE MSC_DEMANDS
SET USING_REQUIREMENT_QUANTITY= 0,
DAILY_DEMAND_RATE= 0,
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 PLAN_ID= -1
AND SR_INSTANCE_ID= lv_SR_INSTANCE_ID
AND ORIGINATION_TYPE= lv_ORIGINATION_TYPE
AND WIP_ENTITY_ID= lv_WIP_ENTITY_ID
AND OP_SEQ_NUM= NVL(lv_OPERATION_SEQ_NUM,OP_SEQ_NUM)
AND ORGANIZATION_ID = lv_ORGANIZATION_ID
AND INVENTORY_ITEM_ID= NVL(lv_INVENTORY_ITEM_ID,INVENTORY_ITEM_ID);
'SELECT'
||' -1, MSC_DEMANDS_S.nextval, '
||' NVL(ms.TRANSACTION_ID,-1) DISPOSITION_ID,'
||' t1.INVENTORY_ITEM_ID,'
||' msd.ORGANIZATION_ID,'
||' t2.INVENTORY_ITEM_ID USING_ASSEMBLY_ITEM_ID,'
||' msd.USING_ASSEMBLY_DEMAND_DATE,'
||' msd.USING_REQUIREMENT_QUANTITY,'
||' msd.QUANTITY_PER_ASSEMBLY,'
||' msd.QUANTITY_ISSUED,'
||' msd.ASSEMBLY_DEMAND_COMP_DATE,'
||' msd.DEMAND_TYPE,'
||' msd.ORIGINATION_TYPE,'
||' msd.SOURCE_ORGANIZATION_ID,'
||' msd.RESERVATION_ID,'
||' msd.OPERATION_SEQ_NUM,'
||' msd.DEMAND_CLASS,'
||' msd.REPETITIVE_SCHEDULE_ID,'
||' msd.SR_INSTANCE_ID,'
||' msd.PROJECT_ID,'
||' msd.TASK_ID,'
||' msd.PLANNING_GROUP,'
||' msd.END_ITEM_UNIT_NUMBER, '
||' REPLACE(REPLACE(substr(msd.ORDER_NUMBER,1,62),:v_chr10),:v_chr13) ORDER_NUMBER,'
||' msd.WIP_ENTITY_ID,'
||' msd.WIP_STATUS_CODE,'
||' msd.WIP_SUPPLY_TYPE,'
||' t3.inventory_item_id ASSET_ITEM_ID,' /* ds change change*/
||' msd.ASSET_SERIAL_NUMBER,' /* ds change change*/
||' msd.COMPONENT_SCALING_TYPE,' /* Discrete Mfg Enahancements Bug 4492736 */
||' msd.COMPONENT_YIELD_FACTOR,' /* Discrete Mfg Enahancements Bug 4479743 */
|| lv_data_sql_stmt
||' :v_last_collection_id,'
||' :v_current_date,'
||' :v_current_user,'
||' :v_current_date,'
||' :v_current_user '
||' FROM MSC_ITEM_ID_LID t1,'
||' MSC_ITEM_ID_LID t2,'
||' MSC_ITEM_ID_LID t3,'
|| lv_supplies_tbl||' ms,'
||' MSC_ST_DEMANDS msd'
||' WHERE msd.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
||' AND msd.ORIGINATION_TYPE = 77' /* 50 eam demand: ds change change SRP Change 5909379*/
||' AND msd.DELETED_FLAG= '||MSC_UTIL.SYS_NO
||' AND t1.SR_INVENTORY_ITEM_ID= msd.inventory_item_id'
||' AND t1.sr_instance_id= msd.SR_INSTANCE_ID'
||' AND t2.SR_INVENTORY_ITEM_ID= msd.using_assembly_item_id'
||' AND t2.sr_instance_id= msd.SR_INSTANCE_ID'
||' AND t3.SR_INVENTORY_ITEM_ID (+)= msd.ASSET_ITEM_ID'
||' AND t3.sr_instance_id (+) = msd.SR_INSTANCE_ID'
||' AND ms.sr_instance_id= msd.SR_INSTANCE_ID'
||' AND ms.ORGANIZATION_ID= msd.ORGANIZATION_ID'
||' AND ms.DISPOSITION_ID= msd.wip_entity_id '
||' AND ms.plan_id=-1'
||' AND ms.ORDER_TYPE= 86'; /* ds change change*/
'INSERT /*+ APPEND */ INTO '||lv_tbl
||'( PLAN_ID,'
||' DEMAND_ID,'
||' DISPOSITION_ID,'
||' INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' USING_ASSEMBLY_ITEM_ID,'
||' USING_ASSEMBLY_DEMAND_DATE,'
||' USING_REQUIREMENT_QUANTITY,'
||' QUANTITY_PER_ASSEMBLY,'
||' ISSUED_QUANTITY,'
||' ASSEMBLY_DEMAND_COMP_DATE,'
||' DEMAND_TYPE,'
||' ORIGINATION_TYPE,'
||' SOURCE_ORGANIZATION_ID,'
||' RESERVATION_ID,'
||' OP_SEQ_NUM,'
||' DEMAND_CLASS,'
||' REPETITIVE_SCHEDULE_ID,'
||' SR_INSTANCE_ID,'
||' PROJECT_ID,'
||' TASK_ID,'
||' PLANNING_GROUP,'
||' UNIT_NUMBER,'
||' ORDER_NUMBER,'
||' WIP_ENTITY_ID,'
||' WIP_STATUS_CODE,'
||' WIP_SUPPLY_TYPE,'
||' ASSET_ITEM_ID,'
||' ASSET_SERIAL_NUMBER,'
||' COMPONENT_SCALING_TYPE,'
||' COMPONENT_YIELD_FACTOR,'
|| lv_sel_sql_stmt
||' REFRESH_NUMBER,'
||' LAST_UPDATE_DATE,'
||' LAST_UPDATED_BY,'
||' CREATION_DATE,'
||' CREATED_BY)'
|| lv_cursor_stmt;
'INSERT INTO '||lv_tbl
||'( PLAN_ID,'
||' DEMAND_ID,'
||' INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' USING_ASSEMBLY_ITEM_ID,'
||' USING_ASSEMBLY_DEMAND_DATE,'
||' USING_REQUIREMENT_QUANTITY,'
||' QUANTITY_PER_ASSEMBLY,'
||' ISSUED_QUANTITY,'
||' ASSEMBLY_DEMAND_COMP_DATE,'
||' DEMAND_TYPE,'
||' ORIGINATION_TYPE,'
||' SOURCE_ORGANIZATION_ID,'
||' DISPOSITION_ID,'
||' RESERVATION_ID,'
||' OP_SEQ_NUM,'
||' DEMAND_CLASS,'
||' SR_INSTANCE_ID,'
||' PROJECT_ID,'
||' TASK_ID,'
||' PLANNING_GROUP,'
||' UNIT_NUMBER,'
||' ORDER_NUMBER,'
||' WIP_ENTITY_ID,'
||' WIP_STATUS_CODE,'
||' WIP_SUPPLY_TYPE,'
||' REPETITIVE_SCHEDULE_ID,'
||' ASSET_ITEM_ID,' /* ds change change*/
||' ASSET_SERIAL_NUMBER,' /* ds change change*/
||' COMPONENT_SCALING_TYPE,' /* Discrete Mfg Enahancements Bug 4492736 */
||' COMPONENT_YIELD_FACTOR,' /* Discrete Mfg Enahancements Bug 4492743 */
||' ITEM_TYPE_ID,'
||' ITEM_TYPE_VALUE,'
||' REFRESH_NUMBER,'
||' LAST_UPDATE_DATE,'
||' LAST_UPDATED_BY,'
||' CREATION_DATE,'
||' CREATED_BY)'
||'VALUES'
||'( -1,'
||' MSC_DEMANDS_S.nextval,'
||' :INVENTORY_ITEM_ID,'
||' :ORGANIZATION_ID,'
||' :USING_ASSEMBLY_ITEM_ID,'
||' :USING_ASSEMBLY_DEMAND_DATE,'
||' :USING_REQUIREMENT_QUANTITY,'
||' :QUANTITY_PER_ASSEMBLY,'
||' :ISSUED_QUANTITY,'
||' :ASSEMBLY_DEMAND_COMP_DATE,'
||' :DEMAND_TYPE,'
||' :ORIGINATION_TYPE,'
||' :SOURCE_ORGANIZATION_ID,'
||' :DISPOSITION_ID,'
||' :RESERVATION_ID,'
||' :OPERATION_SEQ_NUM,'
||' :DEMAND_CLASS,'
||' :SR_INSTANCE_ID,'
||' :PROJECT_ID,'
||' :TASK_ID,'
||' :PLANNING_GROUP,'
||' :END_ITEM_UNIT_NUMBER, '
||' :ORDER_NUMBER,'
||' :WIP_ENTITY_ID,'
||' :WIP_STATUS_CODE,'
||' :WIP_SUPPLY_TYPE,'
||' :REPETITIVE_SCHEDULE_ID,'
||' :ASSET_ITEM_ID,' /* ds change change*/
||' :ASSET_SERIAL_NUMBER,' /* ds change change*/
||' :COMPONENT_SCALING_TYPE,' /* Discrete Mfg Enahancements Bug 4492736 */
||' :COMPONENT_YIELD_FACTOR,' /* Discrete Mfg Enahancements Bug 4492743 */
||' :ITEM_TYPE_ID,'
||' :ITEM_TYPE_VALUE,'
||' :v_last_collection_id,'
||' :v_current_date,'
||' :v_current_user,'
||' :v_current_date,'
||' :v_current_user )';
if the bulk insert above failed for whatever reason */
lv_cursor_stmt:=
'SELECT'
||' NVL(ms.TRANSACTION_ID,-1) DISPOSITION_ID,'
||' t1.INVENTORY_ITEM_ID,'
||' msd.ORGANIZATION_ID,'
||' t2.INVENTORY_ITEM_ID USING_ASSEMBLY_ITEM_ID,'
||' msd.USING_ASSEMBLY_DEMAND_DATE,'
||' msd.USING_REQUIREMENT_QUANTITY,'
||' msd.QUANTITY_PER_ASSEMBLY,'
||' msd.QUANTITY_ISSUED,'
||' msd.ASSEMBLY_DEMAND_COMP_DATE,'
||' msd.DEMAND_TYPE,'
||' msd.ORIGINATION_TYPE,'
||' msd.SOURCE_ORGANIZATION_ID,'
||' msd.RESERVATION_ID,'
||' msd.OPERATION_SEQ_NUM,'
||' msd.DEMAND_CLASS,'
||' msd.REPETITIVE_SCHEDULE_ID,'
||' msd.SR_INSTANCE_ID,'
||' msd.PROJECT_ID,'
||' msd.TASK_ID,'
||' msd.PLANNING_GROUP,'
||' msd.END_ITEM_UNIT_NUMBER, '
||' REPLACE(REPLACE(substr(msd.ORDER_NUMBER,1,62),:v_chr10),:v_chr13) ORDER_NUMBER,'
||' msd.WIP_ENTITY_ID,'
||' msd.WIP_STATUS_CODE,'
||' msd.WIP_SUPPLY_TYPE,'
||' msd.DELETED_FLAG,'
||' t3.inventory_item_id ASSET_ITEM_ID,' /* ds change change*/
||' msd.ASSET_SERIAL_NUMBER,' /* ds change change*/
||' msd.COMPONENT_SCALING_TYPE,' /* Discrete Mfg Enahancements Bug 4492736 */
||' msd.COMPONENT_YIELD_FACTOR' /* Discrete Mfg Enahancements Bug 4492743 */
||' FROM MSC_ITEM_ID_LID t1,'
||' MSC_ITEM_ID_LID t2,'
||' MSC_ITEM_ID_LID t3,'
|| lv_supplies_tbl||' ms,'
||' MSC_ST_DEMANDS msd'
||' WHERE msd.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
||' AND msd.ORIGINATION_TYPE = 77' /* 50 eam demand: ds change change*/
||' AND msd.DELETED_FLAG= '||MSC_UTIL.SYS_NO
||' AND t1.SR_INVENTORY_ITEM_ID= msd.inventory_item_id'
||' AND t1.sr_instance_id= msd.SR_INSTANCE_ID'
||' AND t2.SR_INVENTORY_ITEM_ID= msd.using_assembly_item_id'
||' AND t2.sr_instance_id= msd.SR_INSTANCE_ID'
||' AND t3.SR_INVENTORY_ITEM_ID (+)= msd.ASSET_ITEM_ID'
||' AND t3.sr_instance_id (+) = msd.SR_INSTANCE_ID'
||' AND ms.sr_instance_id= msd.SR_INSTANCE_ID'
||' AND ms.ORGANIZATION_ID= msd.ORGANIZATION_ID'
||' AND ms.DISPOSITION_ID= msd.WIP_ENTITY_ID'
||' AND ms.plan_id=-1'
||' AND ms.ORDER_TYPE= 86' /* ds change change*/
||' order by msd.SOURCE_WIP_ENTITY_ID, msd.SOURCE_INVENTORY_ITEM_ID,msd.SOURCE_ORGANIZATION_ID,msd.ORIGINATION_TYPE';
lv_DELETED_FLAG,
lv_ASSET_ITEM_ID, /* ds change change */
lv_ASSET_SERIAL_NUMBER, /* ds change change */
lv_COMPONENT_SCALING_TYPE, /* Discrete Mfg Enahancements Bug 4492736 */
lv_COMPONENT_YIELD_FACTOR; /* Discrete Mfg Enahancements Bug 4492743 */
UPDATE MSC_DEMANDS
SET
OLD_USING_REQUIREMENT_QUANTITY= USING_REQUIREMENT_QUANTITY,
OLD_USING_ASSEMBLY_DEMAND_DATE= USING_ASSEMBLY_DEMAND_DATE,
OLD_ASSEMBLY_DEMAND_COMP_DATE= ASSEMBLY_DEMAND_COMP_DATE,
USING_ASSEMBLY_ITEM_ID= lv_USING_ASSEMBLY_ITEM_ID,
USING_ASSEMBLY_DEMAND_DATE= lv_USING_ASSEMBLY_DEMAND_DATE,
USING_REQUIREMENT_QUANTITY= lv_USING_REQUIREMENT_QUANTITY,
ASSEMBLY_DEMAND_COMP_DATE= lv_ASSEMBLY_DEMAND_COMP_DATE,
DEMAND_TYPE= lv_DEMAND_TYPE,
SOURCE_ORGANIZATION_ID= lv_SOURCE_ORGANIZATION_ID,
RESERVATION_ID= lv_RESERVATION_ID,
DEMAND_CLASS= lv_DEMAND_CLASS,
PROJECT_ID= lv_PROJECT_ID,
TASK_ID= lv_TASK_ID,
PLANNING_GROUP= lv_PLANNING_GROUP,
UNIT_NUMBER= lv_END_ITEM_UNIT_NUMBER,
ORDER_NUMBER= lv_ORDER_NUMBER,
WIP_STATUS_CODE= lv_WIP_STATUS_CODE,
WIP_SUPPLY_TYPE= lv_WIP_SUPPLY_TYPE,
DISPOSITION_ID= lv_DISPOSITION_ID,
COMPONENT_SCALING_TYPE = lv_COMPONENT_SCALING_TYPE, /* Discrete Mfg Enahancements Bug 4492736 */
COMPONENT_YIELD_FACTOR = lv_COMPONENT_YIELD_FACTOR, /* Discrete Mfg Enahancements Bug 4492743 */
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 PLAN_ID= -1
AND SR_INSTANCE_ID= lv_SR_INSTANCE_ID
AND ORIGINATION_TYPE= lv_ORIGINATION_TYPE
AND ORGANIZATION_ID= lv_ORGANIZATION_ID
AND WIP_ENTITY_ID= lv_WIP_ENTITY_ID
AND OP_SEQ_NUM= lv_OPERATION_SEQ_NUM
AND INVENTORY_ITEM_ID= lv_INVENTORY_ITEM_ID ;
IF ( lv_DELETED_FLAG<> MSC_UTIL.SYS_YES ) AND ( lv_ORIGINATION_TYPE= 77)
AND SQL%NOTFOUND THEN
EXECUTE IMMEDIATE lv_sql_stmt
USING
lv_INVENTORY_ITEM_ID,
lv_ORGANIZATION_ID,
lv_USING_ASSEMBLY_ITEM_ID,
lv_USING_ASSEMBLY_DEMAND_DATE,
lv_USING_REQUIREMENT_QUANTITY,
lv_QUANTITY_PER_ASSEMBLY,
lv_QUANTITY_ISSUED,
lv_ASSEMBLY_DEMAND_COMP_DATE,
lv_DEMAND_TYPE,
lv_ORIGINATION_TYPE,
lv_SOURCE_ORGANIZATION_ID,
lv_DISPOSITION_ID,
lv_RESERVATION_ID,
lv_OPERATION_SEQ_NUM,
lv_DEMAND_CLASS,
lv_SR_INSTANCE_ID,
lv_PROJECT_ID,
lv_TASK_ID,
lv_PLANNING_GROUP,
lv_END_ITEM_UNIT_NUMBER,
lv_ORDER_NUMBER,
lv_WIP_ENTITY_ID,
lv_WIP_STATUS_CODE,
lv_WIP_SUPPLY_TYPE,
lv_REPETITIVE_SCHEDULE_ID,
lv_ASSET_ITEM_ID, /* ds change change */
lv_ASSET_SERIAL_NUMBER, /* ds changechange */
lv_COMPONENT_SCALING_TYPE, /* Discrete Mfg Enahancements Bug 4492736 */
lv_COMPONENT_YIELD_FACTOR, /* Discrete Mfg Enahancements Bug 4492743 */
lv_item_type_id,
lv_item_type_value,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user;