[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure insert_job_interface(p_tempId IN NUMBER,
x_groupId OUT NOCOPY NUMBER) IS BEGIN
select wip_job_schedule_interface_s.nextval
into x_groupID
from dual;
INSERT INTO WIP_JOB_SCHEDULE_INTERFACE(
creation_date,
created_by,
last_update_login,
last_update_date,
last_updated_by,
request_id,
program_application_id,
program_id,
program_update_date,
load_type,
group_id,
scheduling_method,
first_unit_start_date,
job_name,
description,
primary_item_id,
organization_id,
process_phase,
process_status,
rebuild_item_id,
rebuild_serial_number,
parent_wip_entity_id,
manual_rebuild_flag,
activity_type,
activity_cause,
activity_source,
priority,
owning_department,
tagout_required,
shutdown_type)
SELECT sysdate,
mmtt.created_by,
mmtt.last_update_login,
sysdate,
mmtt.last_updated_by,
mmtt.request_id,
mmtt.program_application_id,
mmtt.program_id,
mmtt.program_update_date,
wip_constants.CREATE_EAM_JOB,
x_groupID,
wip_constants.routing,
SYSDATE,
mmtt.rebuild_job_name,
msi.description,
mmtt.rebuild_activity_id,
mmtt.organization_id,
wip_constants.ML_VALIDATION,
wip_constants.pending,
mmtt.rebuild_item_id,
mmtt.rebuild_serial_number,
mmtt.transaction_source_id,
'N',
meaa.activity_type_code,
meaa.activity_cause_code,
meaa.activity_source_code,
to_number(meaa.priority_code),
meaa.owning_department_id,
meaa.tagging_required_flag,
to_number(meaa.shutdown_type_code)
FROM MTL_MATERIAL_TRANSACTIONS_TEMP mmtt,
mtl_system_items msi,
mtl_eam_asset_activities meaa
WHERE transaction_temp_id = p_tempId
and mmtt.rebuild_activity_id = msi.inventory_item_id (+)
and mmtt.organization_id = msi.organization_id (+)
and mmtt.rebuild_activity_id = meaa.asset_activity_id (+)
and mmtt.organization_id = meaa.organization_id (+)
and mmtt.rebuild_item_id = meaa.inventory_item_id (+)
and nvl(mmtt.rebuild_serial_number, '@@@') =
nvl(meaa.serial_number,'@@@'); /* Bug 3661984 */
end insert_job_interface;
procedure update_genealogy(p_tempId IN NUMBER,
x_retVal OUT NOCOPY VARCHAR2,
x_errMsg OUT NOCOPY VARCHAR2) IS
l_rebuildSerNum VARCHAR2(30);
select mmtt.rebuild_serial_number,
mmtt.rebuild_item_id,
mmtt.organization_id,
mmtt.transaction_date,
wdj.asset_number, --will need these eventually
wdj.asset_group_id
into l_rebuildSerNum,
l_rebuildItemID,
l_orgID,
l_txnDate,
l_assetNum,
l_assetGrpID
from mtl_material_transactions_temp mmtt,
wip_discrete_jobs wdj
where wdj.wip_entity_id = mmtt.transaction_source_id
and mmtt.transaction_temp_id = p_tempId;
/* Bug 3655393 - Should not call update_geneology if serial_status = 1
or defined but not used.Not handling NO_DATA_FOUND as we do not want
to call update_geneology, if there is no serial exists. Transaction
should error out if no MSN record for corresponding
rebuild_serial_number in mmtt*/
/*Bug 3655393 - reset l_serial_status to 0 so that in case no rebuid
serial number present also, update_geneology will be called*/
l_serial_status := 0;
select current_status
into l_serial_status
from mtl_serial_numbers
where serial_number = l_rebuildSerNum
and current_organization_id= l_orgID
and inventory_item_id = l_rebuildItemID;
wip_eam_genealogy_pvt.update_eam_genealogy(
p_api_version => 1.0,
p_object_type => 2, -- serial number
p_serial_number => l_rebuildSerNum,
p_inventory_item_id => l_rebuildItemID,
p_organization_id => l_orgID,
p_genealogy_type => 5, --asset/item releationship
p_end_date_active => l_txnDate,
x_return_status => x_retVal,
x_msg_count => l_msgCount,
x_msg_data => x_errMsg);
fnd_message.set_token('ERROR_TEXT', 'wip_rebuild.update_genealogy');
end update_genealogy;
SELECT mmtt.created_by,
mmtt.last_update_login,
mmtt.last_updated_by,
mmtt.request_id,
mmtt.program_application_id,
mmtt.program_id,
mmtt.program_update_date,
mmtt.rebuild_job_name,
msi.description,
mmtt.rebuild_activity_id,
mmtt.organization_id,
mmtt.rebuild_item_id,
mmtt.rebuild_serial_number,
mmtt.transaction_source_id,
msn.wip_accounting_class_code,
meaa.activity_type_code,
meaa.activity_cause_code,
to_number(meaa.priority_code) as priority_code,
meaa.owning_department_id,
tagging_required_flag,
to_number(shutdown_type_code) as shutdown_type_code
FROM MTL_MATERIAL_TRANSACTIONS_TEMP mmtt,
mtl_serial_numbers msn,
mtl_system_items msi,
mtl_eam_asset_activities meaa
WHERE transaction_temp_id = p_tempId
and mmtt.rebuild_activity_id = msi.inventory_item_id (+)
and mmtt.organization_id = msi.organization_id (+)
and mmtt.rebuild_activity_id = meaa.asset_activity_id (+)
and mmtt.organization_id = meaa.organization_id (+)
and mmtt.rebuild_item_id = meaa.inventory_item_id (+)
and nvl(mmtt.rebuild_serial_number, '@@@') =
nvl(meaa.serial_number, '@@@'); /* Bug 3661984 */
select gen_object_id into l_maintenance_object_id
from mtl_serial_numbers
where inventory_item_id = l_workorder.rebuild_item_id and serial_number = l_workorder.rebuild_serial_number and current_organization_id =l_workorder.organization_id;
SELECT inventory_item_id into l_maintenance_object_id
FROM mtl_system_items
WHERE inventory_item_id = l_workorder.rebuild_item_id and organization_id =l_workorder.organization_id;
select trim(substr(value, 1, DECODE( instr( value, ','), 0, length( value), instr( value, ',') -1 ) ) ) into l_output_dir FROM v$parameter WHERE name = 'utl_file_dir';
update_genealogy(p_tempId => p_tempId,
x_retVal => x_retVal,
x_errMsg => x_errMsg);
ROLLBACK TO REBUILD; --gen update failure