[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Insert_Row
( p_eam_wo_comp_rec => p_eam_wo_comp_rec
, x_mesg_token_Tbl => l_mesg_token_tbl
, x_return_Status => l_return_status
);
PROCEDURE insert_row
(
p_eam_wo_comp_rec IN EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type
, x_return_status OUT NOCOPY VARCHAR2
, x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
)
IS
l_Mesg_Token_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Work Order Completeion processing insert row'); END IF;
INSERT INTO EAM_JOB_COMPLETION_TXNS (
transaction_id,
transaction_date,
transaction_type,
wip_entity_id,
organization_id,
parent_wip_entity_id,
reference,
reconciliation_code,
acct_period_id,
qa_collection_id,
actual_start_date,
actual_end_date,
actual_duration,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
completion_subinventory,
completion_locator_id,
lot_number,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
VALUES (
p_eam_wo_comp_rec.transaction_id,
p_eam_wo_comp_rec.transaction_date,
decode(p_eam_wo_comp_rec.transaction_type,EAM_PROCESS_WO_PVT.G_OPR_COMPLETE,1,EAM_PROCESS_WO_PVT.G_OPR_UNCOMPLETE,2),
p_eam_wo_comp_rec.wip_entity_id,
p_eam_wo_comp_rec.organization_id,
p_eam_wo_comp_rec.parent_wip_entity_id,
p_eam_wo_comp_rec.reference,
p_eam_wo_comp_rec.reconciliation_code,
p_eam_wo_comp_rec.acct_period_id,
p_eam_wo_comp_rec.qa_collection_id,
p_eam_wo_comp_rec.actual_start_date,
p_eam_wo_comp_rec.actual_end_date,
p_eam_wo_comp_rec.actual_duration,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.login_id,
p_eam_wo_comp_rec.request_id,
p_eam_wo_comp_rec.program_application_id,
p_eam_wo_comp_rec.program_id,
p_eam_wo_comp_rec.program_update_date,
p_eam_wo_comp_rec.completion_subinventory,
p_eam_wo_comp_rec.completion_locator_id,
p_eam_wo_comp_rec.lot_number,
p_eam_wo_comp_rec.attribute_category,
p_eam_wo_comp_rec.attribute1,
p_eam_wo_comp_rec.attribute2,
p_eam_wo_comp_rec.attribute3,
p_eam_wo_comp_rec.attribute4,
p_eam_wo_comp_rec.attribute5,
p_eam_wo_comp_rec.attribute6,
p_eam_wo_comp_rec.attribute7,
p_eam_wo_comp_rec.attribute8,
p_eam_wo_comp_rec.attribute9,
p_eam_wo_comp_rec.attribute10,
p_eam_wo_comp_rec.attribute11,
p_eam_wo_comp_rec.attribute12,
p_eam_wo_comp_rec.attribute13,
p_eam_wo_comp_rec.attribute14,
p_eam_wo_comp_rec.attribute15
);
EAM_ERROR_MESSAGE_PVT.Write_Debug('Work Order Completeion completed insert row');
EAM_ERROR_MESSAGE_PVT.Write_Debug('Work Order Completeion : insert row : inside exception : ' || SQLERRM);
, p_message_text => G_PKG_NAME ||' :Inserting Record ' || SQLERRM
, x_mesg_token_Tbl => x_mesg_token_tbl
);
END insert_row;
SELECT eam_asset_status_history_s.nextval
INTO i_asset_status_id
FROM dual;
UPDATE eam_asset_status_history
SET enable_flag = 'N'
, last_update_date = SYSDATE
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.login_id
WHERE organization_id = s_organization_id
AND wip_entity_id = s_wip_entity_id
AND operation_seq_num IS NULL
AND (enable_flag is NULL OR enable_flag = 'Y')
AND maintenance_object_type = s_maintenance_object_type
AND maintenance_object_id = s_maintenance_object_id;
INSERT INTO eam_asset_status_history
(asset_status_id,
organization_id,
asset_group_id,
asset_number,
start_date,
end_date,
wip_entity_id, -- Fix for Bug 3448770
maintenance_object_type,
maintenance_object_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
enable_flag) -- Enhancement Bug 3852846
VALUES (i_asset_status_id,
s_organization_id,
s_asset_group_id,
s_asset_number,
s_start_date,
s_end_date,
s_wip_entity_id, -- Fix for Bug 3448770
s_maintenance_object_type,
s_maintenance_object_id,
s_user_id,
sysdate,
s_user_id,
sysdate,
'Y'); -- Enhancement Bug 3852846
select mtl_material_transactions_s.nextval into i_transaction_header_id
from dual;
select nvl(revision_qty_control_code,1)
into l_revision_control_code
from mtl_system_items
where inventory_item_id = s_rebuild_item_id
and organization_id = s_org_id;
EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.process_lot_serial : before inv_trx_util_pub.insert_line_trx');
errCode := inv_trx_util_pub.insert_line_trx(
p_trx_hdr_id => i_transaction_header_id,
p_item_id => s_rebuild_item_id,
p_revision => i_revision,
p_org_id => s_org_id,
p_trx_action_id => i_transaction_action_id,
p_subinv_code => s_subinventory,
p_locator_id => s_locator_id,
p_trx_type_id => i_transaction_type_id,
p_trx_src_type_id => i_transaction_source_type_id,
p_trx_qty => i_transaction_quantity,
p_pri_qty => i_primary_quantity,
p_uom => item.primaryUOMCode,
p_date => sysdate,
p_user_id => s_user_id,
p_trx_src_id => s_wip_entity_id,
x_trx_tmp_id => i_transaction_temp_id,
x_proc_msg => errMsg);
EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.process_lot_serial : inv_trx_util_pub.insert_line_trx : ' || errMsg);
EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.process_lot_serial : after inv_trx_util_pub.insert_line_trx');
EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.process_lot_serial : before inv_trx_util_pub.insert_lot_trx');
errCode := inv_trx_util_pub.insert_lot_trx(
p_trx_tmp_id => i_transaction_temp_id,
p_user_id => s_user_id,
p_lot_number => s_lot_serial_tbl(i).lot_number,
p_trx_qty => l_transaction_quantity,
p_pri_qty => l_transaction_quantity,
x_ser_trx_id => i_serial_transaction_temp_id,
x_proc_msg => errMsg);
EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.process_lot_serial : after inv_trx_util_pub.insert_lot_trx');
EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.process_lot_serial : before inv_trx_util_pub.insert_ser_trx');
errCode := inv_trx_util_pub.insert_ser_trx(
p_trx_tmp_id => i_transaction_temp_id_s,
p_user_id => s_user_id,
p_fm_ser_num => s_lot_serial_tbl(i).serial_number,
p_to_ser_num => s_lot_serial_tbl(i).serial_number,
x_proc_msg => errMsg);
EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.process_lot_serial : after inv_trx_util_pub.insert_ser_trx');
FND_MSG_PUB.Delete_msg;
l_lot_serial_tbl.DELETE;
PROCEDURE update_row (
p_eam_wo_comp_rec IN EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type
, x_return_status OUT NOCOPY VARCHAR2
, x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
)
IS
l_asset_group_id NUMBER;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Work Order Completeion processing update_row'); END IF;
select parent_wip_entity_id,
asset_group_id,
asset_number,
primary_item_id,
manual_rebuild_flag,
rebuild_item_id,
rebuild_serial_number,
project_id,
task_id,
maintenance_object_source, -- added as part of bug #2774571 to check whether the work order is of 'EAM' or 'CMRO'
maintenance_object_type, -- Fix for Bug 3448770
maintenance_object_id
into i_parent_wip_entity_id,
i_asset_group_id,
i_asset_number,
i_asset_activity_id,
i_manual_rebuild_flag,
i_rebuild_item_id,
i_rebuild_serial_number,
i_project_id,
i_task_id,
i_maintenance_source_id, -- added as part of bug #2774571 to check whether the work order is of 'EAM' or 'CMRO'
i_maintenance_object_type, -- Fix for Bug 3448770
i_maintenance_object_id
from wip_discrete_jobs
where wip_entity_id = p_eam_wo_comp_rec.wip_entity_id;
select
completion_subinventory,
completion_locator_id,
lot_number
into l_cmpl_sub,
l_locator,
l_lot
from EAM_JOB_COMPLETION_TXNS
where wip_entity_id = p_eam_wo_comp_rec.wip_entity_id
and transaction_type = 1
and transaction_date = (select max(transaction_date) from EAM_JOB_COMPLETION_TXNS
where wip_entity_id = p_eam_wo_comp_rec.wip_entity_id
and transaction_type =1);
EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.update_row : Exception1 : '|| errMsg);
select system_status into i_status_type
from eam_wo_statuses_V
where status_id = p_eam_wo_comp_rec.user_status_id
and enabled_flag = 'Y';
SELECT NVL(asset_group_id,rebuild_item_id),
NVL(asset_number,rebuild_serial_number),
maintenance_object_type,
maintenance_object_id
INTO l_asset_group_id ,
l_asset_number,
i_maintenance_object_type,
i_maintenance_object_id
FROM WIP_DISCRETE_JOBS
WHERE wip_entity_id = p_eam_wo_comp_rec.wip_entity_id;
eam_meters_util.update_last_service_dates_wo(
p_wip_entity_id => p_eam_wo_comp_rec.wip_entity_id,
p_start_date => p_eam_wo_comp_rec.actual_start_date,
p_end_date => p_eam_wo_comp_rec.actual_end_date,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.update_row : Exception2 : '|| l_msg_data);
UPDATE WIP_DISCRETE_JOBS
SET last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id,
status_type = i_status_type,
date_completed = i_completion_date,
request_id = p_eam_wo_comp_rec.request_id,
program_application_id = p_eam_wo_comp_rec.program_application_id,
program_id = p_eam_wo_comp_rec.program_id,
program_update_date = p_eam_wo_comp_rec.program_update_date
WHERE wip_entity_id = p_eam_wo_comp_rec.wip_entity_id
AND organization_id = p_eam_wo_comp_rec.organization_id;
UPDATE EAM_WORK_ORDER_DETAILS
SET USER_DEFINED_STATUS_ID = p_eam_wo_comp_rec.user_status_id
WHERE wip_entity_id = p_eam_wo_comp_rec.wip_entity_id
AND organization_id = p_eam_wo_comp_rec.organization_id ;
-- Update wip_eam_work_requests table
UPDATE WIP_EAM_WORK_REQUESTS
SET work_request_status_id = i_work_request_status,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE wip_entity_id = p_eam_wo_comp_rec.wip_entity_id;
, p_message_text => G_PKG_NAME ||' :Inserting Record ' || SQLERRM
, x_mesg_token_Tbl => x_mesg_token_tbl
);
EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM_WO_COMP_UTILITY_PVT.update_row : Exception3 : '|| SQLERRM);
END update_row;