[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/*l_update_wip_job_flag NUMBER := 1;*/
SELECT -1 * cst_wip_cost_estimates_s.nextval
INTO l_estimation_group_id
FROM DUAL;
/* Select Maintenance organization id for this org. The work order will be created
in maintenance org only. That would mean that all wip table will store the
WO details against the maintenance organization id */
select maint_organization_id
into l_maint_organization_id
from mtl_parameters where organization_id = p_organization_id; /* Bug 5203079*/
UPDATE wip_discrete_jobs wdj -- job_option 1
SET wdj.estimation_status = l_estimation_group_id,
wdj.last_update_date = SYSDATE,
wdj.last_updated_by = l_user_id,
wdj.request_id = l_request_id
WHERE WDJ.organization_id = l_maint_organization_id
AND NVL(WDJ.estimation_status,1) <> 7
AND NVL(WDJ.estimation_status,1) > 0
AND WDJ.status_type IN (1,3,4,6,17)
AND p_job_option = 1
AND p_entity_type IN (1,6)
AND EXISTS ( SELECT 'X'
FROM wip_entities we
WHERE we.wip_entity_id = wdj.wip_entity_id
AND we.entity_type = p_entity_type
)RETURNING wdj.wip_entity_id BULK COLLECT INTO l_entity_id_tab;
UPDATE wip_discrete_jobs wdj -- job_option 2
SET wdj.estimation_status = l_estimation_group_id,
wdj.last_update_date = SYSDATE,
wdj.last_updated_by = l_user_id,
wdj.request_id = l_request_id
WHERE WDJ.organization_id = l_maint_organization_id
AND NVL(WDJ.estimation_status,1) <> 7
AND NVL(WDJ.estimation_status,1) > 0
AND WDJ.status_type IN (1,3,4,6,17)
AND p_job_option = 2
AND WDJ.wip_entity_id = p_wip_entity_id
RETURNING wdj.wip_entity_id BULK COLLECT INTO l_entity_id_tab;
UPDATE wip_discrete_jobs wdj -- option 3 entity_type 1, primary_item_id
SET wdj.estimation_status = l_estimation_group_id,
wdj.last_update_date = SYSDATE,
wdj.last_updated_by = l_user_id,
wdj.request_id = l_request_id
WHERE WDJ.organization_id = l_maint_organization_id
AND NVL(wdj.estimation_status,1) <> 7
AND NVL(wdj.estimation_status,1) > 0
AND WDJ.status_type IN (1,3,4,6,17)
AND p_job_option = 3
AND WDJ.primary_item_id = p_inventory_item_id
AND p_entity_type = 1
AND EXISTS ( SELECT 'X'
FROM wip_entities WE
WHERE WE.wip_entity_id = WDJ.wip_entity_id
AND WE.entity_type = p_entity_type
)
RETURNING wdj.wip_entity_id BULK COLLECT INTO l_entity_id_tab;
UPDATE wip_discrete_jobs wdj -- job_option 3 entity_type 6
SET wdj.estimation_status = l_estimation_group_id,
wdj.last_update_date = SYSDATE,
wdj.last_updated_by = l_user_id,
wdj.request_id = l_request_id
WHERE wdj.organization_id = l_maint_organization_id
AND NVL(wdj.estimation_status,1) <> 7
AND NVL(wdj.estimation_status,1) > 0
AND wdj.status_type IN (1,3,4,6,17)
AND p_job_option = 3
AND wdj.maintenance_object_id in
(select cii.instance_id
from csi_item_instances cii
where cii.instance_number = p_asset_number
AND cii.inventory_item_id = p_inventory_item_id
)
AND wdj.maintenance_object_type = 3
AND p_entity_type = 6
RETURNING wdj.wip_entity_id BULK COLLECT INTO l_entity_id_tab;
UPDATE wip_discrete_jobs wdj -- option 4
SET wdj.estimation_status = l_estimation_group_id,
wdj.last_update_date = SYSDATE,
wdj.last_updated_by = l_user_id,
wdj.request_id = l_request_id
WHERE wdj.organization_id = l_maint_organization_id
AND NVL(wdj.estimation_status,1) <> 7
AND NVL(wdj.estimation_status,1) > 0
AND wdj.status_type IN (1,3,4,6,17)
AND p_job_option = 4
AND wdj.owning_department = p_owning_department_id
AND p_entity_type = 6
RETURNING wdj.wip_entity_id BULK COLLECT INTO l_entity_id_tab;
||' Job Record(s) Updated with Group Id: '
||TO_CHAR(l_estimation_group_id));
/* Delete from the global temp table just to make sure it is empty */
DELETE FROM cst_eam_direct_items_temp;
CST_eamCost_PUB.Insert_tempEstimateDetails (
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_entity_id_tab => l_entity_id_tab
);
CST_EAMCOST_PUB.delete_eamperbal(
p_api_version => 1.0,
p_init_msg_list => FND_API.g_false,
p_entity_id_tab => l_entity_id_tab,
p_org_id => l_maint_organization_id,
p_type => 1,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
l_err_code := 'Error: CSTEAM_COST_PUB.delete_eamperbal()';
/* the following lines delete the rows for this wip entity ID from the table
CST_EAM_WO_ESTIMATE_DETAILS */
l_stmt_num := 32;
Delete from CST_EAM_WO_ESTIMATE_DETAILS
where wip_entity_id = l_entity_id_tab(l_index);
/* Added the call to Delete_eamBalAcct as part of eAM
Requirements Project - R12. The procedure deletes the
rows for this wip entity ID from the table
WIP_EAM_BALANCE_BY_ACCOUNTS */
l_stmt_num := 35;
CST_eamCost_PUB.Delete_eamBalAcct(
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_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_entity_id_tab => l_entity_id_tab,
p_org_id => l_maint_organization_id
) ;
l_err_code := 'Error: CST_EAMCOST_PUB.Delete_eamBalAcct()';
l_err_code := 'Error: CST_eamCost_PUB.Insert_tempEstimateDetails()';
UPDATE wip_discrete_jobs wdj
SET estimation_status = decode(estimation_status,9,8,7),
last_estimation_date = SYSDATE,
last_estimation_req_id = l_request_id,
last_update_date = SYSDATE
WHERE wdj.wip_entity_id = l_entity_id_tab(l_index);
UPDATE wip_discrete_jobs
SET estimation_status = 3,
last_update_date = SYSDATE,
last_estimation_date = SYSDATE,
last_estimation_req_id = l_request_id
WHERE wip_entity_id = l_entity_id_tab(l_index);
UPDATE wip_discrete_jobs
SET estimation_status = 1,
last_update_date = SYSDATE,
last_estimation_date = SYSDATE,
last_estimation_req_id = l_request_id
WHERE estimation_status = l_estimation_group_id
AND wip_entity_id = l_entity_id_tab(l_index);
CST_EAMCOST_PUB.delete_eamperbal(
p_api_version => 1.0,
p_init_msg_list => FND_API.g_false,
p_entity_id_tab => l_entity_id_tab,
p_org_id => p_organization_id,
p_type => 1,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
l_api_message := 'Error: CST_EAMCOST_PUB.delete_eamperbal()';
/* the following lines delete the rows for this wip entity ID from the table
CST_EAM_WO_ESTIMATE_DETAILS */
l_stmt_num := 30;
Delete from CST_EAM_WO_ESTIMATE_DETAILS
where wip_entity_id = l_entity_id_tab(1);
/* Added the call to Delete_eamBalAcct as part of eAM
Requirements Project - R12. The procedure deletes the
rows for this wip entity ID from the table
WIP_EAM_BALANCE_BY_ACCOUNTS */
l_stmt_num := 35;
CST_eamCost_PUB.Delete_eamBalAcct(
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_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_entity_id_tab => l_entity_id_tab,
p_org_id => p_organization_id
) ;
l_api_message := 'Error: CST_EAMCOST_PUB.Delete_eamBalAcct()';
DELETE FROM cst_eam_direct_items_temp;
CST_eamCost_PUB.Insert_tempEstimateDetails (
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_entity_id_tab => l_entity_id_tab
);
l_api_message := 'Error: CST_eamCost_PUB.Insert_tempEstimateDetails()';
UPDATE wip_discrete_jobs wdj
SET estimation_status = decode(estimation_status,9,8,7),
last_estimation_date = SYSDATE,
last_estimation_req_id = l_request_id,
last_update_date = SYSDATE
WHERE wdj.wip_entity_id = l_entity_id_tab(1);
UPDATE wip_discrete_jobs
SET estimation_status = 3,
last_update_date = SYSDATE,
last_estimation_date = SYSDATE,
last_estimation_req_id = l_request_id
WHERE wip_entity_id = l_entity_id_tab(1);
UPDATE wip_discrete_jobs
SET estimation_status = 3,
last_update_date = SYSDATE,
last_estimation_date = SYSDATE,
last_estimation_req_id = l_request_id
WHERE wip_entity_id = l_entity_id_tab(1);
UPDATE wip_discrete_jobs
SET estimation_status = 3,
last_update_date = SYSDATE,
last_estimation_date = SYSDATE,
last_estimation_req_id = l_request_id
WHERE wip_entity_id = l_entity_id_tab(1);