[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT enable_workflow
INTO l_workflow_enabled
FROM EAM_ENABLE_WORKFLOW
WHERE MAINTENANCE_OBJECT_SOURCE =p_maint_obj_source;
SELECT eam_wo_workflow_enabled
INTO l_workflow_enabled
FROM WIP_EAM_PARAMETERS
WHERE organization_id =p_organization_id;
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;
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);
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);
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);
FND_MSG_PUB.Delete_msg;
l_lot_serial_tbl.DELETE;
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');
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 wip_entity_id, organization_id, rebuild_item_id,
rebuild_serial_number, parent_wip_entity_id, asset_number,
asset_group_id, manual_rebuild_flag, primary_item_id, status_type,
completion_subinventory, completion_locator_id, lot_number,
project_id, task_id
FROM wip_discrete_jobs
WHERE wip_entity_id = P_WIP_ENTITY_ID
FOR UPDATE OF status_type NOWAIT;
FND_MESSAGE.Set_Name('EAM', 'FORM_RECORD_DELETED');
i_program_update_date DATE;
/* --replaced with select statement for bug #2414513.
| -- Cursor to hold all child jobs information
| cursor child_jobs_cursor(c_wip_entity_id NUMBER) is
| select we.wip_entity_name, wdj.status_type
| from wip_discrete_jobs wdj, wip_entities we
| where wdj.wip_entity_id = we.wip_entity_id
| and wdj.parent_wip_entity_id = c_wip_entity_id
| and wdj.manual_rebuild_flag = 'Y';
select eam_job_completion_txns_s.nextval into i_transaction_id from dual;
i_program_update_date := sysdate;
i_program_update_date := null;
select wdj.parent_wip_entity_id,
wdj.asset_group_id,
wdj.asset_number,
wdj.primary_item_id,
wdj.manual_rebuild_flag,
wdj.rebuild_item_id,
wdj.rebuild_serial_number,
wdj.project_id,
wdj.task_id,
wdj.organization_id,
wdj.maintenance_object_source, -- added as part of bug #2774571 to check whether the work order is of 'EAM' or 'CMRO'
wdj.maintenance_object_type, -- Fix for Bug 3448770
wdj.maintenance_object_id,
wdj.status_type,
ewod.user_defined_status_id,
ewod.workflow_type,
we.wip_entity_name
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_org_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,
l_old_system_status,
l_old_eam_status,
l_workflow_type,
l_wip_entity_name
from wip_discrete_jobs wdj,eam_work_order_details ewod,wip_entities we
where wdj.wip_entity_id = x_wip_entity_id
AND wdj.wip_entity_id = ewod.wip_entity_id(+)
AND wdj.wip_entity_id = we.wip_entity_id;
SELECT '1'
INTO network_child_job_var
FROM dual
WHERE EXISTS (SELECT '1'
FROM wip_discrete_jobs
WHERE wip_entity_id IN
(
SELECT DISTINCT child_object_id
FROM eam_wo_relationships
WHERE parent_relationship_type =1
START WITH parent_object_id = x_wip_entity_id AND parent_relationship_type = 1
CONNECT BY parent_object_id = prior child_object_id AND parent_relationship_type = 1
)
AND status_type NOT IN (WIP_CONSTANTS.COMP_CHRG,
WIP_CONSTANTS.COMP_NOCHRG,WIP_CONSTANTS.CLOSED,WIP_CONSTANTS.CANCELLED, WIP_CONSTANTS.PEND_CLOSE)
);
SELECT '1'
INTO sibling_parent_job_var
FROM dual
WHERE EXISTS (SELECT '1'
FROM wip_discrete_jobs
WHERE wip_entity_id IN
(
SELECT DISTINCT parent_object_id
FROM eam_wo_relationships
WHERE parent_relationship_type =2 and
child_object_id = x_wip_entity_id
)
AND status_type NOT IN (WIP_CONSTANTS.COMP_CHRG,
WIP_CONSTANTS.COMP_NOCHRG,WIP_CONSTANTS.CLOSED)
);
UPDATE EAM_WORK_ORDER_DETAILS
SET user_defined_status_id=3,
pending_flag='Y',
last_update_date=SYSDATE,
last_update_login=FND_GLOBAL.login_id,
last_updated_by=FND_GLOBAL.user_id
WHERE wip_entity_id= x_wip_entity_id;
SELECT NVL((SUM(system_estimated_mat_cost) + SUM(system_estimated_lab_cost) + SUM(system_estimated_eqp_cost)),0)
INTO l_cost_estimate
FROM WIP_EAM_PERIOD_BALANCES
WHERE wip_entity_id =x_wip_entity_id;
SELECT EAM_WORKFLOW_EVENT_S.NEXTVAL
INTO l_wf_event_seq
FROM DUAL;
INSERT INTO EAM_WO_WORKFLOWS
(WIP_ENTITY_ID,WF_ITEM_TYPE,WF_ITEM_KEY,LAST_UPDATE_DATE,LAST_UPDATED_BY,
CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN)
VALUES
(x_wip_entity_id,l_workflow_name,l_event_key,SYSDATE,FND_GLOBAL.user_id,
SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id
);
l_parameter_list.DELETE;
IF(i_maintenance_source_id =1) THEN --update text index for EAM workorders
EAM_TEXT_UTIL.PROCESS_WO_EVENT
(
p_event => 'UPDATE',
p_wip_entity_id =>x_wip_entity_id,
p_organization_id =>i_org_id,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.user_id,
p_last_update_login =>FND_GLOBAL.login_id
);
SELECT '1'
INTO child_job_var
FROM dual
WHERE EXISTS (SELECT '1'
FROM wip_discrete_jobs wdj, wip_entities we
WHERE wdj.wip_entity_id = we.wip_entity_id
AND wdj.parent_wip_entity_id = x_wip_entity_id
AND wdj.manual_rebuild_flag = 'Y'
AND wdj.status_type NOT IN (WIP_CONSTANTS.COMP_CHRG,
WIP_CONSTANTS.COMP_NOCHRG,WIP_CONSTANTS.CLOSED));
select status_type into i_parent_status_type
from wip_discrete_jobs
where wip_entity_id = i_parent_wip_entity_id;
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 = i_org_id
AND wip_entity_id = x_wip_entity_id
AND operation_seq_num IS NULL
AND (enable_flag = 'Y' OR enable_flag IS null);
eam_pm_utils.update_pm_when_uncomplete(i_org_id, x_wip_entity_id);
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,
asset_group_id,
asset_number,
asset_activity_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 (i_transaction_id,
x_transaction_date,
x_transaction_type,
x_wip_entity_id,
i_org_id,
i_parent_wip_entity_id,
x_reference,
x_reconcil_code,
i_acct_period_id,
x_qa_collection_id,
i_asset_group_id,
i_asset_number,
i_asset_activity_id,
x_actual_start_date,
x_actual_end_date,
x_actual_duration,
x_user_id,
sysdate,
x_user_id,
sysdate,
x_user_id,
x_request_id,
x_application_id,
x_program_id,
i_program_update_date,
l_subinventory,
l_locator,
l_lot_number,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15
);
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,
asset_group_id,
asset_number,
asset_activity_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 (i_transaction_id,
x_transaction_date,
x_transaction_type,
x_wip_entity_id,
i_org_id,
i_parent_wip_entity_id,
x_reference,
x_reconcil_code,
i_acct_period_id,
x_qa_collection_id,
i_rebuild_item_id, -- changed from asset_group_id to rebuild_item_id
i_rebuild_serial_number, -- changed from asset_serial_number to rebuild_serial_number
i_asset_activity_id,
x_actual_start_date,
x_actual_end_date,
x_actual_duration,
x_user_id,
sysdate,
x_user_id,
sysdate,
x_user_id,
x_request_id,
x_application_id,
x_program_id,
i_program_update_date,
l_subinventory,
l_locator,
l_lot_number,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15
);
end if; -- end insert check
update wip_discrete_jobs
set last_update_date = sysdate,
last_updated_by = x_user_id,
last_update_login = x_user_id,
status_type = i_status_type,
date_completed = i_completion_date,
request_id = x_request_id,
program_application_id = x_application_id,
program_id = x_program_id,
program_update_date = i_program_update_date
where wip_entity_id = x_wip_entity_id and
organization_id = i_org_id;
UPDATE EAM_WORK_ORDER_DETAILS
SET last_update_date = sysdate,
last_updated_by = x_user_id,
last_update_login = x_user_id,
user_defined_status_id=i_status_type
WHERE wip_entity_id = x_wip_entity_id;
update wip_eam_work_requests
set work_request_status_id = i_work_request_status,
last_update_date = sysdate,
last_updated_by = x_user_id,
last_update_login = fnd_global.login_id
where wip_entity_id = x_wip_entity_id;
SELECT EAM_WORKFLOW_EVENT_S.NEXTVAL
INTO l_wf_event_seq
FROM DUAL;
l_parameter_list.DELETE;
IF(i_maintenance_source_id =1) THEN --update text index for EAM workorders
EAM_TEXT_UTIL.PROCESS_WO_EVENT
(
p_event => 'UPDATE',
p_wip_entity_id =>x_wip_entity_id,
p_organization_id =>i_org_id,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.user_id,
p_last_update_login =>FND_GLOBAL.login_id
);