[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT max(mwjsi.WIP_ENTITY_ID)
,mwjsi.ORGANIZATION_ID
,max(mwjsi.MAINTENANCE_OBJECT_SOURCE)
,mwjdi.INVENTORY_ITEM_ID_NEW
,sum(mwjdi.REQUIRED_QUANTITY)
,max(mwjdi.uom_code)
,mwjsi.first_unit_start_date
FROM MSC_WIP_JOB_SCHEDULE_INTERFACE mwjsi
,msc_wip_job_dtls_interface mwjdi
WHERE mwjsi.group_id = p_group_id
AND MAINTENANCE_OBJECT_SOURCE = 2
and mwjsi.group_id = mwjdi.group_id
GROUP BY mwjsi.ORGANIZATION_ID,
mwjdi.INVENTORY_ITEM_ID_NEW,
mwjsi.first_unit_start_date;
lv_sql_stmt := ' SELECT max(mwjsi.WIP_ENTITY_ID) '
||' ,mwjsi.ORGANIZATION_ID'
||' ,max(mwjsi.MAINTENANCE_OBJECT_SOURCE)'
||' ,mwjsi.PRIMARY_ITEM_ID'
||' ,sum(mwjsi.net_QUANTITY)' --12779383
||' ,max(mwjsi.uom_code)'
||' ,mwjsi.last_unit_completion_date' --12779383
||' FROM MSC_WIP_JOB_SCHEDULE_INTERFACE'||g_dblink||' mwjsi'
||' WHERE mwjsi.group_id = :GROUP_ID'--|| p_group_id
||' AND MAINTENANCE_OBJECT_SOURCE = 2 '
||' GROUP BY mwjsi.ORGANIZATION_ID, '
||' mwjsi.PRIMARY_ITEM_ID, '
||' mwjsi.last_unit_completion_date ';
If not ,we will do a insertion of new record */
FOR y IN 1..REL_WO_CUR_TBL.Count LOOP
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,
' Processing Job/Org : ' || REL_WO_CUR_TBL(y).WIP_ENTITY_ID ||'/'||
REL_WO_CUR_TBL(y).ORGANIZATION_ID );
UPDATE AHL_REQ_SUPPLY_SCHEDULE
set quantity = quantity + REL_WO_CUR_TBL(y).start_quantity
,object_version_number = object_version_number + 1
where
inventory_item_id = REL_WO_CUR_TBL(y).PRIMARY_ITEM_ID
and organization_id = REL_WO_CUR_TBL(y).organization_id
and requested_date = REL_WO_CUR_TBL(y).first_unit_start_date;
'Insert a new record into AHL_REQ_SUPPLY_SCHEDULE');
INSERT INTO AHL_REQ_SUPPLY_SCHEDULE(
inventory_item_id,
organization_id,
requested_date,
quantity,
uom_code,
ascp_plan_date,
source_application,
object_version_number,
last_update_date,
last_updated_by,
creation_date,
created_by)
VALUES
(REL_WO_CUR_TBL(y).PRIMARY_ITEM_ID,
REL_WO_CUR_TBL(y).organization_id ,
REL_WO_CUR_TBL(y).first_unit_start_date ,
REL_WO_CUR_TBL(y).START_QUANTITY,
REL_WO_CUR_TBL(y).uom_code ,
P_PLAN_COMPLETION_DATE,
'MSC' ,
1 ,
--The object_version_number needs to be set to 1 while inserting
--and incremented by 1 while updating
--This is use on the CMRO side to take care of concurrency
SYSDATE,
-1,
SYSDATE,
-1);
log_output ('Inserted new record into AHL_REQ_SUPPLY_SCHEDULE');
log_output ('Updated AHL_REQ_SUPPLY_SCHEDULE, as above combination exists');
lv_update_stmt VARCHAR2(2000) ;
lv_update_stmt := 'UPDATE MSC_WIP_JOB_SCHEDULE_INTERFACE'||g_dblink||
' SET WIP_ENTITY_ID = WIP_ENTITY_ID/2'||
' WHERE GROUP_ID = '|| P_GROUP_ID
||' AND MAINTENANCE_OBJECT_SOURCE IN (1,2)'
||' AND GROUP_ID < 0 '
||' AND sr_instance_id ='|| P_SR_INSTANCE_ID;
'lv_update_stmt is '||lv_update_stmt);
EXECUTE IMMEDIATE lv_update_stmt;
lv_update_stmt := 'UPDATE MSC_WIP_JOB_DTLS_INTERFACE'||g_dblink||
' SET WIP_ENTITY_ID = WIP_ENTITY_ID/2,'||
' DEPARTMENT_ID = DEPARTMENT_ID/2,'||
' RESOURCE_ID_NEW = RESOURCE_ID_NEW/2,'||
' RESOURCE_INSTANCE_ID = RESOURCE_INSTANCE_ID/2'||
' WHERE GROUP_ID = '||P_GROUP_ID
||' AND GROUP_ID < 0 '
||' AND sr_instance_id ='|| P_SR_INSTANCE_ID;
/*bug 12674323 -- added resource_instance_id in the above update stmt*/
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
'lv_update_stmt is '||lv_update_stmt);
EXECUTE IMMEDIATE lv_update_stmt;
lv_cur_stmt := ' SELECT DISTINCT WIP_ENTITY_ID ,ORGANIZATION_ID '
||' FROM MSC_WIP_JOB_SCHEDULE_INTERFACE'||g_dblink
||' WHERE group_id = '||g_group_id
||' AND MAINTENANCE_OBJECT_SOURCE=2'
||' AND WIP_ENTITY_ID is not null'
||' AND sr_instance_id ='|| P_SR_INSTANCE_ID;
lv_cur_stmt_eam := ' SELECT DISTINCT WIP_ENTITY_ID ,ORGANIZATION_ID '
||' FROM MSC_WIP_JOB_SCHEDULE_INTERFACE'||g_dblink
||' WHERE group_id = '||g_group_id
||' AND MAINTENANCE_OBJECT_SOURCE=1'
||' AND WIP_ENTITY_ID is not null';
lv_sql_stmt := 'DELETE msc_wip_job_schedule_interface'||g_dblink
||' where sr_instance_id ='|| P_SR_INSTANCE_ID
||' and nvl(group_id,-1) = nvl('||g_group_id||',-1)'
||' and maintenance_object_source in (1,2)'
||' and group_id <0 ';
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'After delete on msc_wip_job_schedule_interface' );
lv_sql_stmt := 'DELETE msc_wip_job_dtls_interface'||g_dblink
||' where sr_instance_id =' || P_SR_INSTANCE_ID
||' and nvl(group_id,-1) = nvl('||g_group_id||',-1)'
||' and group_id < 0';
v_updated_by number;
select ltrim(rtrim(value)) into g_output_dir
from (select value from v$parameter2 where name='utl_file_dir'
order by rownum desc)
where rownum <2;
' AHL_MATERIALS_GRP.UPDATE_WO_MATERIALS'||
'with wip_entity_id ='||V_WIP_ENTITY_ID);
lv_sql_stmt := 'select wjdi.inventory_item_id_old PRIMARY_ITEM_ID, '||
' wjdi.inventory_item_id_new ALTERNATE_ITEM_ID, '||
' null PRIMARY_ITEM_QUANTITY, '||
' null PRIMARY_ITEM_UOM_CODE, '||
' null ALT_ITEM_QUANTITY, '||
' null ALT_ITEM_UOM_CODE, '||
' null REQUESTED_DATE, '||
' wjdi.operation_seq_num OPERATION_SEQUENCE, '||
' null SCHEDULED_MATERIAL_ID, '||
' null PRIORITY '||
' from MSC_WIP_JOB_SCHEDULE_INTERFACE wjsi, MSC_WIP_JOB_DTLS_INTERFACE wjdi '||
' where wjsi.header_id = wjdi.parent_header_id'||
' and wjsi.group_id = wjdi.group_id'||
' and wjsi.wip_entity_id = wjdi.wip_entity_id'||
' and wjsi.sr_instance_id = wjdi.sr_instance_id'||
' and wjsi.organization_id = wjdi.organization_id'||
' and wjdi.inventory_item_id_new is not null'||
' and wjsi.wip_entity_id= :V_WIP_ENTITY_ID'||
' and wjsi.sr_instance_id ='|| g_sr_instance_id ||
' and wjsi.group_id = '||g_group_id ||
' and wjdi.load_type = 5 ' ;
/*new api -- UPDATE_WO_MATERIALS*/
AHL_MATERIALS_GRP.UPDATE_WO_MATERIALS
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_x_return_status_ahl2,
x_msg_count => l_x_msg_count_ahl2,
x_msg_data => l_x_msg_data_ahl2,
p_wip_entity_id => V_WIP_ENTITY_ID,
p_alt_item_tbl => l_alt_item_tbl);
' AHL_MATERIALS_GRP.UPDATE_WO_MATERIALS'||
' returns SUCCESS ');
'AHL_MATERIALS_GRP.UPDATE_WO_MATERIALS '||
'FAILED');
' AHL_LTP_MATERIALS_GRP.Update_mtl_resv_dates'||
' did not return success ');
v_updated_by number;
select ltrim(rtrim(value)) into g_output_dir
from (select value from v$parameter2 where name='utl_file_dir'
order by rownum desc)
where rownum <2;
L_JOB_STMT := 'SELECT '
||' ''MSC'' SOURCE_CODE '
||' ,WJSI.SOURCE_LINE_ID '
||' ,WJSI.ORGANIZATION_ID '
||' ,WJSI.STATUS_TYPE '
||' ,WJSI.FIRST_UNIT_START_DATE '
--||' ,WJSI.PRIMARY_ITEM_ID REBUILD_ITEM_ID '
||' ,WJSI.BOM_REVISION_DATE '
||' ,WJSI.ROUTING_REVISION_DATE '
||' ,WDJ.CLASS_CODE '
||' ,WJSI.JOB_NAME '
||' ,WJSI.FIRM_PLANNED_FLAG '
||' ,WJSI.ALTERNATE_ROUTING_DESIGNATOR '
||' ,WJSI.ALTERNATE_BOM_DESIGNATOR '
||' ,WJSI.START_QUANTITY '
||' ,WJSI.WIP_ENTITY_ID '
||' ,WJSI.SCHEDULE_GROUP_ID '
||' ,WJSI.PROJECT_ID '
||' ,WJSI.TASK_ID '
--||' ,WJSI.START_QUANTITY '
||' ,WJSI.END_ITEM_UNIT_NUMBER '
||' ,WJSI.HEADER_ID '
||' ,WJSI.LAST_UNIT_COMPLETION_DATE '
||' ,WDJ.ASSET_NUMBER '
||' ,WDJ.ASSET_GROUP_ID '
||' ,WDJ.MAINTENANCE_OBJECT_ID '
||' ,WDJ.MAINTENANCE_OBJECT_TYPE '
||' ,WDJ.MAINTENANCE_OBJECT_SOURCE '
||' ,WDJ.DATE_RELEASED '
||' ,WDJ.OWNING_DEPARTMENT '
||' FROM WIP_DISCRETE_JOBS WDJ , '
||' MSC_WIP_JOB_SCHEDULE_INTERFACE'||g_dblink||' WJSI '
||' WHERE WJSI.WIP_ENTITY_ID = :V_WIP_ENTITY_ID'
||' AND WJSI.ORGANIZATION_ID = :V_ORGANIZATION_ID'
||' AND WJSI.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID '
||' AND WJSI.ORGANIZATION_ID = WDJ.ORGANIZATION_ID '
||' AND WJSI.GROUP_ID = :GROUP_ID' ;
L_EAM_WO_TBL.delete(L_EAM_WO_TBL.FIRST,L_EAM_WO_TBL.last);
L_EAM_WO_TBL(y).TRANSACTION_TYPE := EAM_PROCESS_WO_PVT.G_OPR_UPDATE ;
lv_stmt := 'SELECT'
||' WJDI.PARENT_HEADER_ID '
||' , WJDI.WIP_ENTITY_ID WIP_ENTITY_ID '
||' ,WJDI.ORGANIZATION_ID'
||' ,WJDI.OPERATION_SEQ_NUM'
||' ,WJDI.DEPARTMENT_ID DEPARTMENT_ID'
||' ,WJDI.DESCRIPTION'
||' ,WJDI.MINIMUM_TRANSFER_QUANTITY'
||' ,WJDI.COUNT_POINT_TYPE'
||' ,WJDI.BACKFLUSH_FLAG'
||' ,WJDI.FIRST_UNIT_START_DATE START_DATE'
||' ,WJDI.LAST_UNIT_COMPLETION_DATE COMPLETION_DATE'
||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||g_dblink ||' WJDI '
||' WHERE'
||' WJDI.WIP_ENTITY_ID = :V_WIP_ENTITY_ID'
||' AND WJDI.ORGANIZATION_ID = :V_ORGANIZATION_ID'
||' AND WJDI.GROUP_ID = :G_GROUP_ID'
||' AND WJDI.LOAD_TYPE = 3 ';
L_EAM_OP_TBL.delete(L_EAM_OP_TBL.FIRST,L_EAM_OP_TBL.last);
L_EAM_OP_TBL(y).TRANSACTION_TYPE := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
select shutdown_type
, attribute_category
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, long_description
INTO
L_EAM_OP_TBL(y).shutdown_type
,L_EAM_OP_TBL(y).attribute_category
,L_EAM_OP_TBL(y).ATTRIBUTE1
,L_EAM_OP_TBL(y).ATTRIBUTE2
,L_EAM_OP_TBL(y).ATTRIBUTE3
,L_EAM_OP_TBL(y).ATTRIBUTE4
,L_EAM_OP_TBL(y).ATTRIBUTE5
,L_EAM_OP_TBL(y).ATTRIBUTE6
,L_EAM_OP_TBL(y).ATTRIBUTE7
,L_EAM_OP_TBL(y).ATTRIBUTE8
,L_EAM_OP_TBL(y).ATTRIBUTE9
,L_EAM_OP_TBL(y).ATTRIBUTE10
,L_EAM_OP_TBL(y).ATTRIBUTE11
,L_EAM_OP_TBL(y).ATTRIBUTE12
,L_EAM_OP_TBL(y).ATTRIBUTE13
,L_EAM_OP_TBL(y).ATTRIBUTE14
,L_EAM_OP_TBL(y).ATTRIBUTE15
,L_EAM_OP_TBL(y).long_description
from wip_operations wo
where
wo.wip_entity_id = OP_CUR_TBL(y).WIP_ENTITY_ID
and wo.OPERATION_SEQ_NUM = OP_CUR_TBL(y).OPERATION_SEQ_NUM
and wo.organization_id = OP_CUR_TBL(y).ORGANIZATION_ID
and wo.REPETITIVE_SCHEDULE_ID is NULL;
lv_stmt2 := ' SELECT '
||' WJDI.PARENT_HEADER_ID '
||' ,WJDI.BATCH_ID '
||' ,WJDI.WIP_ENTITY_ID WIP_ENTITY_ID '
||' ,WJDI.ORGANIZATION_ID '
||' ,WJDI.OPERATION_SEQ_NUM '
||' ,WJDI.RESOURCE_SEQ_NUM '
||' ,WJDI.RESOURCE_ID_NEW RESOURCE_ID_NEW '
||' ,WJDI.BASIS_TYPE '
||' ,WJDI.USAGE_RATE_OR_AMOUNT '
||' ,WJDI.SCHEDULED_FLAG '
||' ,WJDI.ASSIGNED_UNITS '
||' ,WJDI.AUTOCHARGE_TYPE '
||' ,WJDI.START_DATE '
||' ,WJDI.COMPLETION_DATE '
||' ,WJDI.DEPARTMENT_ID DEPARTMENT_ID '
||' ,WJDI.FIRM_FLAG '
||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||g_dblink||' WJDI '
||' WHERE'
||' WJDI.WIP_ENTITY_ID = :V_WIP_ENTITY_ID '
||' AND WJDI.ORGANIZATION_ID = :V_ORGANIZATION_ID '
||' AND WJDI.GROUP_ID = :G_GROUP_ID'
||' AND WJDI.LOAD_TYPE = 1 ' ;
L_EAM_RES_TBL.delete(L_EAM_RES_TBL.FIRST,L_EAM_RES_TBL.last);
L_EAM_RES_TBL(y).TRANSACTION_TYPE := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
lv_stmt2 := ' SELECT '
||' WJDI.PARENT_HEADER_ID '
||' ,WJDI.BATCH_ID '
||' ,WJDI.WIP_ENTITY_ID WIP_ENTITY_ID '
||' ,WJDI.ORGANIZATION_ID '
||' ,WJDI.OPERATION_SEQ_NUM '
||' ,WJDI.INVENTORY_ITEM_ID_NEW '
||' ,WJDI.DEPARTMENT_ID DEPARTMENT_ID '
||' ,WJDI.WIP_SUPPLY_TYPE '
||' ,WJDI.DATE_REQUIRED '
||' ,WJDI.REQUIRED_QUANTITY '
||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||g_dblink||' WJDI '
||' WHERE'
||' WJDI.WIP_ENTITY_ID = :V_WIP_ENTITY_ID '
||' AND WJDI.ORGANIZATION_ID = :V_ORGANIZATION_ID '
||' AND WJDI.GROUP_ID = :G_GROUP_ID'
||' AND WJDI.LOAD_TYPE = 2 ' ;
L_EAM_MAT_TBL.delete(L_EAM_MAT_TBL.FIRST,L_EAM_MAT_TBL.last);
L_EAM_MAT_TBL(y).TRANSACTION_TYPE := EAM_PROCESS_WO_PVT.G_OPR_UPDATE ;
lv_stmt2 := ' SELECT '
||' WJDI.PARENT_HEADER_ID '
||' ,WJDI.BATCH_ID '
||' ,WJDI.WIP_ENTITY_ID WIP_ENTITY_ID '
||' ,WJDI.ORGANIZATION_ID '
||' ,WJDI.OPERATION_SEQ_NUM '
||' ,WJDI.INVENTORY_ITEM_ID_OLD '
||' ,WJDI.INVENTORY_ITEM_ID_NEW '
||' ,WJDI.DEPARTMENT_ID DEPARTMENT_ID '
||' ,WJDI.WIP_SUPPLY_TYPE '
||' ,WJDI.DATE_REQUIRED '
||' ,WJDI.REQUIRED_QUANTITY '
||' ,Null TRANSACTION_TYPE'
||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||g_dblink||' WJDI '
||' WHERE'
||' WJDI.WIP_ENTITY_ID = :V_WIP_ENTITY_ID '
||' AND WJDI.ORGANIZATION_ID = :V_ORGANIZATION_ID '
||' AND WJDI.GROUP_ID = :G_GROUP_ID'
||' AND WJDI.LOAD_TYPE = 5 ' ;
L_EAM_MAT_TBL.delete(L_EAM_MAT_TBL.FIRST,L_EAM_MAT_TBL.last);
END IF; -- we can't use this as data would be inserted into this table above
MAT_SUB_CUR_TBL(x).TRANSACTION_TYPE := EAM_PROCESS_WO_PVT.G_OPR_DELETE;
lv_stmt2 := 'SELECT '
||' WJDI.PARENT_HEADER_ID '
||' ,WJDI.BATCH_ID, '
||' WJDI.WIP_ENTITY_ID WIP_ENTITY_ID , '
||' WJDI.ORGANIZATION_ID, '
||' WJDI.OPERATION_SEQ_NUM, '
||' WJDI.RESOURCE_SEQ_NUM, '
||' WORI.INSTANCE_ID, ' --check if this is correct or not- this is prob not sr_instance_id
||' WORI.SERIAL_NUMBER, '
||' WJDI.START_DATE, '
||' WJDI.COMPLETION_DATE '
||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||g_dblink||' WJDI,'
||' WIP_OP_RESOURCE_INSTANCES WORI'
||' WHERE '
||' WJDI.WIP_ENTITY_ID = :V_WIP_ENTITY_ID '
||' AND WJDI.ORGANIZATION_ID = :V_ORGANIZATION_ID '
||' AND WJDI.GROUP_ID = :G_GROUP_ID'
||' AND WJDI.LOAD_TYPE = 8'
||' AND WJDI.ORGANIZATION_ID = WORI.ORGANIZATION_ID'
||' AND WJDI.WIP_ENTITY_ID = WORI.WIP_ENTITY_ID'
||' AND WJDI.OPERATION_SEQ_NUM = WORI.OPERATION_SEQ_NUM'
||' AND WJDI.RESOURCE_SEQ_NUM = WORI.RESOURCE_SEQ_NUM';
L_EAM_RES_INST_TBL(y).TRANSACTION_TYPE := EAM_PROCESS_WO_PVT.G_OPR_UPDATE ;
lv_stmt2 := 'SELECT '
||' WJDI.PARENT_HEADER_ID, '
||' WJDI.BATCH_ID, '
||' WJDI.WIP_ENTITY_ID WIP_ENTITY_ID , '
||' WJDI.ORGANIZATION_ID, '
||' WJDI.OPERATION_SEQ_NUM, '
||' WJDI.RESOURCE_SEQ_NUM, '
||' WJDI.START_DATE, '
||' WJDI.COMPLETION_DATE, '
||' WJDI.ASSIGNED_UNITS, '
||' WJDI.RESOURCE_INSTANCE_ID, '
||' WJDI.SERIAL_NUMBER '
||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||g_dblink||' WJDI'
||' WHERE'
||' WJDI.WIP_ENTITY_ID = :V_WIP_ENTITY_ID '
||' AND WJDI.ORGANIZATION_ID = :V_ORGANIZATION_ID '
||' AND WJDI.GROUP_ID = :G_GROUP_ID'
||' AND WJDI.LOAD_TYPE = 4';
L_EAM_RES_USAGE_TBL(y).TRANSACTION_TYPE := EAM_PROCESS_WO_PVT.G_OPR_UPDATE ;
lv_stmt2 := 'INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE'||g_dblink||'('
||' GROUP_ID '
||' ,OPERATION_SEQ_NUM '
||' ,DEPARTMENT_ID '
||' ,LAST_UPDATE_DATE '
||' ,LAST_UPDATED_BY '
||' ,CREATION_DATE '
||' ,CREATED_BY '
||' ,LAST_UPDATE_LOGIN '
||' ,DESCRIPTION '
||' ,STANDARD_OPERATION_ID '
||' ,FIRST_UNIT_START_DATE '
||' ,FIRST_UNIT_COMPLETION_DATE '
||' ,LAST_UNIT_START_DATE '
||' ,LAST_UNIT_COMPLETION_DATE '
||' ,COUNT_POINT_TYPE '
||' ,BACKFLUSH_FLAG '
||' ,MINIMUM_TRANSFER_QUANTITY '
||' ,WIP_ENTITY_ID '
||' ,ORGANIZATION_ID '
||' ,SCHEDULED_QUANTITY '
||' ,LOAD_TYPE '
-- , Col to identify this insert
-- , FIND IF ANY OTHER REQUIRED COLUMN MISSING AND HOW TO DERIVE SUCH DATA
||' )SELECT '
||' :g_group_id '
||' ,WO.OPERATION_SEQ_NUM '
||' ,WO.DEPARTMENT_ID '
||' ,SYSDATE '
||' ,-1 ' -- do we need to change this?
||' ,SYSDATE '
||' ,-1 '
||' ,-1 '
||' ,WO.DESCRIPTION '
||' ,WO.STANDARD_OPERATION_ID '
||' ,:P_JOB_START_DATE '
||' ,:P_JOB_START_DATE '
||' ,:P_JOB_START_DATE '
||' ,:P_JOB_START_DATE '
||' ,WO.COUNT_POINT_TYPE '
||' ,WO.BACKFLUSH_FLAG '
||' ,WO.MINIMUM_TRANSFER_QUANTITY '
||' ,WO.WIP_ENTITY_ID '
||' ,WO.ORGANIZATION_ID '
||' ,WO.SCHEDULED_QUANTITY '
||' ,3 '
||' FROM WIP_OPERATIONS WO '
||' WHERE WO.WIP_ENTITY_ID = :V_WIP_ENTITY_ID'
||' AND WO.ORGANIZATION_ID = :V_ORGANIZATION_ID'
||' AND WO.OPERATION_SEQ_NUM NOT IN '
||' (select operation_seq_num '
||' from msc_wip_job_dtls_interface'||g_dblink||' mwjdi'
||' where mwjdi.group_id = :g_group_id '
||' and mwjdi.wip_entity_id = :V_WIP_ENTITY_ID'
||' and mwjdi.organization_id = :V_ORGANIZATION_ID'
||' and mwjdi.load_type = 3)';
lv_stmt2 := 'INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE'||g_dblink||'('
||' GROUP_ID '
||' ,BATCH_ID '
||' ,WIP_ENTITY_ID '
||' ,ORGANIZATION_ID '
||' ,OPERATION_SEQ_NUM '
||' ,RESOURCE_SEQ_NUM '
||' ,RESOURCE_ID_NEW '
||' ,BASIS_TYPE '
||' ,USAGE_RATE_OR_AMOUNT '
||' ,SCHEDULED_FLAG '
||' ,ASSIGNED_UNITS '
||' ,AUTOCHARGE_TYPE '
||' ,START_DATE '
||' ,COMPLETION_DATE '
||' ,DEPARTMENT_ID '
||' ,LOAD_TYPE '
||' ,LAST_UPDATE_DATE '
||' ,LAST_UPDATED_BY '
||' ,CREATION_DATE '
||' ,CREATED_BY '
||' ,LAST_UPDATE_LOGIN) '
||' SELECT '
||' :g_group_id '
||' ,wor.BATCH_ID '
||' ,wor.WIP_ENTITY_ID '
||' ,wor.ORGANIZATION_ID '
||' ,wor.OPERATION_SEQ_NUM '
||' ,wor.RESOURCE_SEQ_NUM '
||' ,wor.RESOURCE_ID '
||' ,wor.BASIS_TYPE '
||' ,wor.USAGE_RATE_OR_AMOUNT '
||' ,wor.SCHEDULED_FLAG '
||' ,wor.ASSIGNED_UNITS '
||' ,wor.AUTOCHARGE_TYPE '
||' ,:P_JOB_START_DATE '
||' ,:P_JOB_START_DATE '
||' ,wor.DEPARTMENT_ID '
||' ,1 '
||' ,SYSDATE '
||' ,-1 '
||' ,SYSDATE '
||' ,-1 '
||' ,-1 '
||' FROM WIP_OPERATION_RESOURCES wor'
||' where '
||' wor.wip_entity_id = :V_WIP_ENTITY_ID '
||' and wor.organization_id = :V_ORGANIZATION_ID'
||' and (wor.operation_seq_num,wor.resource_seq_num) '
||' not in '
||' (select operation_seq_num,resource_seq_num '
||' from msc_wip_job_dtls_interface'||g_dblink||' mwjdi'
||' where mwjdi.group_id = :g_group_id '
||' and mwjdi.wip_entity_id = :V_WIP_ENTITY_ID'
||' and mwjdi.organization_id = :V_ORGANIZATION_ID'
||' and mwjdi.load_type = 1)';
lv_stmt2 := 'INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE'||g_dblink||'('
||' GROUP_ID '
||' ,BATCH_ID '
||' ,WIP_ENTITY_ID '
||' ,ORGANIZATION_ID '
||' ,OPERATION_SEQ_NUM '
||' ,INVENTORY_ITEM_ID_NEW '
||' ,DEPARTMENT_ID '
||' ,WIP_SUPPLY_TYPE '
||' ,DATE_REQUIRED '
||' ,REQUIRED_QUANTITY '
||' ,LOAD_TYPE '
||' ,LAST_UPDATE_DATE '
||' ,LAST_UPDATED_BY '
||' ,CREATION_DATE '
||' ,CREATED_BY '
||' ,LAST_UPDATE_LOGIN) '
||' SELECT '
||' :g_group_id '
||' ,1 ' -- No col wro.BATCH_ID derive it
||' ,wro.wip_entity_id '
||' ,wro.ORGANIZATION_ID '
||' ,wro.operation_seq_num '
||' ,wro.inventory_item_id '
||' ,wro.department_id '
||' ,wro.wip_supply_type '
||' ,:P_JOB_START_DATE '
||' ,wro.required_quantity '
||' ,2 '
||' ,SYSDATE '
||' ,-1 '
||' ,SYSDATE '
||' ,-1 '
||' ,-1 '
||' FROM WIP_REQUIREMENT_OPERATIONS wro '
||' where '
||' wro.wip_entity_id = :V_WIP_ENTITY_ID '
||' and wro.organization_id = :V_ORGANIZATION_ID'
||' AND ( wro.OPERATION_SEQ_NUM, '
||' wro.inventory_item_id ) NOT IN '
||' (select operation_seq_num, '
||' inventory_item_id_new '
||' from msc_wip_job_dtls_interface'||g_dblink||' mwjdi'
||' where mwjdi.group_id = :g_group_id '
||' and mwjdi.wip_entity_id = :V_WIP_ENTITY_ID'
||' and mwjdi.organization_id = :V_ORGANIZATION_ID'
||' and mwjdi.load_type = 2)';