[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'WIP_ENTITY_TYPE',
error_explanation = l_errMsg
where transaction_header_id = p_txnHdrID
and process_flag = 1
and transaction_source_type_id = 5
and wip_entity_type <> wip_constants.eam
and ( rebuild_item_id is not null
or rebuild_serial_number is not null
or rebuild_job_name is not null
or rebuild_activity_id is not null);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'INVENTORY_ITEM_ID',
error_explanation = l_errMsg
where transaction_header_id = p_txnHdrID
and process_flag = 1
and transaction_source_type_id = 5
and rebuild_item_id is not null
and not exists (select 1
from mtl_system_items msi
where msi.inventory_item_id = mti.inventory_item_id
and msi.organization_id = mti.organization_id
and msi.eam_item_type = 3);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'REBUILD_ITEM_ID',
error_explanation = l_errMsg
where transaction_header_id = p_txnHdrID
and process_flag = 1
and transaction_source_type_id = 5
and rebuild_item_id is null
and ( rebuild_serial_number is not null
or rebuild_job_name is not null
or rebuild_activity_id is not null);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'REBUILD_ITEM_ID',
error_explanation = l_errMsg
where transaction_header_id = p_txnHdrID
and process_flag = 1
and transaction_source_type_id = 5
and rebuild_item_id is not null
and not exists(select 1
from mtl_system_items msi, mtl_parameters mp
where mti.rebuild_item_id = msi.inventory_item_id
and msi.organization_id = mp.organization_id
and mp.maint_organization_id = mti.organization_id
and msi.eam_item_type = 3
);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'REBUILD_ACTIVITY_ID',
error_explanation = l_errMsg
where transaction_header_id = p_txnHdrID
and process_flag = 1
and transaction_source_type_id = 5
and rebuild_activity_id is not null
and not exists(select 1
from mtl_system_items msi
where mti.rebuild_activity_id = msi.inventory_item_id
and mti.organization_id = msi.organization_id
and msi.eam_item_type = 2);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'TRANSACTION_TYPE_ID',
error_explanation = l_errMsg
where transaction_header_id = p_txnHdrID
and process_flag = 1
and transaction_source_type_id = 5
and transaction_action_id <> wip_constants.isscomp_action
and rebuild_item_id is not null;
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'PRIMARY_QUANTITY',
error_explanation = l_errMsg
where transaction_header_id = p_txnHdrID
and process_flag = 1
and transaction_source_type_id = 5
and abs(primary_quantity) <> 1
and exists (select 1
from mtl_system_items msi
where msi.inventory_item_id = mti.inventory_item_id
and msi.organization_id = mti.organization_id
and msi.eam_item_type = 3);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'REBUILD_JOB_NAME',
error_explanation = l_errMsg
where transaction_header_id = p_txnHdrID
and process_flag = 1
and transaction_source_type_id = 5
and rebuild_item_id is not null
and rebuild_job_name is not null
and exists (select 1
from wip_entities we
where mti.rebuild_job_name = we.wip_entity_name
and mti.organization_id = we.organization_id);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'REBUILD_ACTIVITY_ID',
error_explanation = l_errMsg
where transaction_header_id = p_txnHdrID
and process_flag = 1
and transaction_source_type_id = 5
and rebuild_item_id is not null
and rebuild_activity_id is not null
and not exists (select 1
from mtl_eam_asset_activities meaa, csi_item_instances cii, eam_org_maint_defaults eomd
where mti.rebuild_activity_id = meaa.asset_activity_id
and meaa.activity_association_id = eomd.object_id
and eomd.object_type = 60
and eomd.organization_id = mti.organization_id
and cii.inventory_item_id = mti.rebuild_item_id
and cii.serial_number = mti.rebuild_serial_number
and meaa.maintenance_object_id = cii.instance_id
and meaa.maintenance_object_type = 3
and nvl(meaa.start_date_active, mti.transaction_date - 1) <= mti.transaction_date
and nvl(meaa.end_date_active, mti.transaction_date + 1) >= mti.transaction_date);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'REBUILD_ITEM_ID',
error_explanation = l_errMsg
where transaction_header_id = p_txnHdrID
and process_flag = 1
and transaction_source_type_id = 5
and rebuild_item_id is not null
and rebuild_serial_number is not null
and not exists(select 1
from mtl_system_items msi, mtl_parameters mp
where mti.rebuild_item_id = msi.inventory_item_id
and mti.organization_id = mp.maint_organization_id
and mp.organization_id = msi.organization_id
and msi.serial_number_control_code in (wip_constants.full_sn,
wip_constants.dyn_rcv_sn));
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'REBUILD_ITEM_ID',
error_explanation = l_errMsg
where transaction_header_id = p_txnHdrID
and process_flag = 1
and transaction_source_type_id = 5
and rebuild_item_id is not null
and rebuild_serial_number is not null
and not exists(select 1 --subquery verifies rebuild sn is predefined or (issued out/in stores and in the asset's genealogy)
from mtl_serial_numbers msn
where mti.rebuild_item_id = msn.inventory_item_id
and mti.rebuild_serial_number = msn.serial_number
and ( msn.current_status = 1 --defined not used
or ( msn.current_status in (3,4) --issued out or in stores
and exists(select 1
from wip_discrete_jobs wdj, mtl_object_genealogy mog,
csi_item_instances cii, mtl_serial_numbers msn_parent
where wdj.maintenance_object_id = cii.instance_id
and wdj.maintenance_object_type = 3
and wdj.wip_entity_id = mti.transaction_source_id
and cii.inventory_item_id = msn_parent.inventory_item_id
and cii.serial_number = msn_parent.serial_number
and msn_parent.gen_object_id = mog.parent_object_id --work order's gen_object_id
and msn.gen_object_id = mog.object_id --rebuild item's gen_object_id
and mog.start_date_active <= mti.transaction_date
and (mog.end_date_active is null or mog.end_date_active >= mti.transaction_date)))));
select fm_serial_number
bulk collect into l_serialNumTbl
from mtl_serial_numbers_temp
where transaction_temp_id = p_compRec.txnTmpID;
select fm_serial_number
bulk collect into l_serialNumTbl
from mtl_serial_numbers_temp
where transaction_temp_id = (select serial_transaction_temp_id
from mtl_transaction_lots_temp
where transaction_temp_id = p_compRec.txnTmpID);
select wdj.maintenance_object_id, wdj.maintenance_object_type,
wdj.maintenance_object_source, msn.gen_object_id
into l_maintObjID, l_maintObjType, l_maintObjSrc, l_maintGenObjID
from wip_discrete_jobs wdj, csi_item_instances cii, mtl_serial_numbers msn
where wdj.wip_entity_id = p_compRec.wipEntityID
and wdj.maintenance_object_type = 3
and wdj.maintenance_object_id = cii.instance_id
and msn.serial_number (+) = cii.serial_number --Modified outer join for bug 6892336
and msn.inventory_item_id (+) = cii.inventory_item_id; --Modified outer join for bug 6892336
wip_logger.log('calling update gen', l_returnStatus);
wip_eam_genealogy_pvt.update_eam_genealogy(p_api_version => 1.0,
p_object_type => 2, /* serial number */
p_serial_number => l_serialNumTbl(i),
p_inventory_item_id => p_compRec.itemID,
p_organization_id => p_compRec.orgID,
p_genealogy_type => 5, /* asset item relationship*/
p_end_date_active => p_compRec.txnDate,
x_return_status => x_returnStatus,
x_msg_count => l_msgCount,
x_msg_data => l_msgData);