[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Get_Entl_rec_for_delete(
p_visit_id IN NUMBER,
x_warranty_entl_tbl OUT NOCOPY AHL_WARRANTY_ENTL_PVT.Warranty_Entl_Tbl_Type
);
SELECT * FROM AHL_VISITS_B
WHERE VISIT_ID = c_id
FOR UPDATE OF OBJECT_VERSION_NUMBER;
SELECT 'X'
FROM FND_CONCURRENT_REQUESTS FCR, AHL_VISITS_B AVB
WHERE FCR.REQUEST_ID IN ( AVB.CST_EST_REQUEST_ID, AVB.REQUEST_ID )
AND FCR.PHASE_CODE IN ('P', 'R')
AND AVB.VISIT_ID = c_id;
SELECT status_code
FROM ahl_visits_b
WHERE visit_id = c_visit_id;
SELECT DISTINCT STAGES.STAGE_ID ,
STAGES.STAGE_NAME
FROM AHL_VISIT_TASKS_B TASKS,
AHL_VWP_STAGES_VL STAGES
WHERE
TASKS.VISIT_ID= c_visit_id AND
STAGES.STAGE_ID=TASKS.STAGE_ID AND
TASKS.TASK_TYPE_CODE <> 'STAGE' AND --SKPATHAK :: stages not having any visit tasks need not be validated
NOT EXISTS (
SELECT STAGE_ID
FROM AHL_VISIT_STAGE_TYP_ASOC
WHERE STAGE_ID=STAGES.STAGE_ID);
UPDATE ahl_visits_b
SET REQUEST_ID = l_req_id,
OBJECT_VERSION_NUMBER = object_version_number + 1,-- PRAKKUM :: PIE :: 13-OCT-2010 ::
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE visit_id = p_visit_id;
SELECT visit_id INTO l_visit_id FROM AHL_VISITS_B WHERE visit_number = p_visit_number;
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
fnd_log.string(l_log_statement,L_DEBUG_KEY,'Before calling Update_Project');
Update_Project (
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_module_type => p_module_type,
p_visit_id => p_visit_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
fnd_log.string(l_log_statement,L_DEBUG_KEY,'After calling Update_Project - '||x_return_status);
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
SELECT T1.PROJECT_ID, T1.VISIT_NUMBER, T2.*
FROM AHL_VISITS_VL T1, AHL_VISIT_TASKS_VL T2
WHERE VISIT_TASK_ID = x_id
AND T1.VISIT_ID = T2.VISIT_ID;
UPDATE AHL_VISIT_TASKS_B
SET PROJECT_TASK_ID = l_task_id,
OBJECT_VERSION_NUMBER = object_version_number + 1,
--TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE VISIT_TASK_ID = p_visit_task_id;
PROCEDURE Delete_Task_to_Project(
p_visit_task_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
AS
-- Define local Variables
L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Task_to_Project';
SELECT * FROM Ahl_Visit_Tasks_VL
WHERE Visit_Task_ID = x_id;
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
SELECT PARENT.TASK_ID PARENT_TASK_ID, PARENT.PM_TASK_REFERENCE PARENT_TASK_REFERENCE
FROM PA_TASKS TASK, PA_TASKS PARENT
WHERE TASK.TASK_ID = c_project_task_id
AND PARENT.TASK_ID (+) = TASK.PARENT_TASK_ID;
fnd_log.string(l_log_statement,L_DEBUG_KEY,'Before calling PA_PROJECT_PUB.CHECK_DELETE_TASK_OK');
PA_PROJECT_PUB.CHECK_DELETE_TASK_OK
( p_api_version_number => 1
, p_init_msg_list => l_init_msg_list
, p_return_status => l_return_status
, p_msg_count => l_msg_count
, p_msg_data => l_msg_data
, p_project_id => c_visit_rec.PROJECT_ID
, p_pm_project_reference => c_visit_rec.visit_number
, p_task_id => c_task_rec.PROJECT_TASK_ID
, p_pm_task_reference => c_task_rec.VISIT_TASK_NUMBER
, p_delete_task_ok_flag => l_del_task_flag
);
'After calling PA_PROJECT_PUB.check_delete_task_ok. Return Status = ' ||
l_return_status || ', delete task flag = ' || l_del_task_flag);
'Errors from PA_PROJECT_PUB.check_delete_task_ok. Message count: ' ||
l_msg_count || ', message data: ' || l_msg_data);
'Before calling PA_PROJECT_PUB.delete_task.');
PA_PROJECT_PUB.DELETE_TASK
( p_api_version_number => 1
,p_commit => l_commit
,p_init_msg_list => l_init_msg_list
,p_msg_count => l_msg_count
,p_msg_data => l_msg_data
,p_return_status => l_return_status
,p_pm_product_code => G_PM_PRODUCT_CODE
,p_pm_project_reference => c_visit_rec.visit_number
,p_pa_project_id => c_visit_rec.PROJECT_ID
,p_pm_task_reference => c_task_rec.VISIT_TASK_NUMBER
,p_pa_task_id => c_task_rec.PROJECT_TASK_ID
,p_cascaded_delete_flag => 'N'
,p_project_id => l_project_id
,p_task_id => l_task_id
);
'After calling PA_PROJECT_PUB.delete_task. Return Status = ' ||
l_return_status);
'Errors from PA_PROJECT_PUB.delete_task. Message count: ' ||
l_msg_count || ', message data: ' || l_msg_data);
'Before calling PA_PROJECT_PUB.delete_task.');
PA_PROJECT_PUB.DELETE_TASK
( p_api_version_number => 1
,p_commit => l_commit
,p_init_msg_list => l_init_msg_list
,p_msg_count => l_msg_count
,p_msg_data => l_msg_data
,p_return_status => l_return_status
,p_pm_product_code => G_PM_PRODUCT_CODE
,p_pm_project_reference => c_visit_rec.visit_number
,p_pa_project_id => c_visit_rec.PROJECT_ID
,p_pm_task_reference => parent_project_task_rec.PARENT_TASK_REFERENCE
,p_pa_task_id => parent_project_task_rec.PARENT_TASK_ID
,p_cascaded_delete_flag => 'N'
,p_project_id => l_project_id
,p_task_id => l_task_id
);
'After calling PA_PROJECT_PUB.delete_task. Return Status = ' ||
l_return_status);
'Errors from PA_PROJECT_PUB.delete_task. Message count: ' ||
l_msg_count || ', message data: ' || l_msg_data);
END Delete_Task_to_Project;
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
SELECT count(*) FROM AHL_VISIT_TASKS_VL
WHERE VISIT_ID = x_id
AND NVL(status_code, 'Y') <> NVL ('DELETED', 'X') ;
SELECT * FROM AHL_VISIT_TASKS_VL
WHERE VISIT_ID = x_id
AND NVL(status_code, 'Y') <> NVL ('DELETED', 'X') ;
SELECT OBJECT_VERSION_NUMBER FROM AHL_VISIT_TASKS_VL
WHERE VISIT_ID = x_id AND VISIT_TASK_NUMBER = x_task_num;
SELECT default_cost_group_id
FROM mtl_parameters
WHERE organization_id = p_org_id;
SELECT ENABLED_FLAG FROM PA_PROJECT_STATUS_CONTROLS
WHERE PROJECT_STATUS_CODE LIKE c_project_status_code
AND ACTION_CODE LIKE 'NEW_TXNS';
SELECT * FROM (
SELECT SUBSTR(NVL(ar.route_no,avt.visit_task_name),1,20) task_name,
SUBSTR(NVL(ar.title,avt.visit_task_name),1,250) description,
avt.visit_task_name, avt.visit_task_number, avt.start_date_time task_start_date, avt.visit_task_id,
avt.end_date_time task_end_date, avt.task_type_code, avt.cost_parent_id, avt.service_type_code, avt.status_code -- PRAKKUM :: PIE :: 13-OCT-2010
, avt.repair_batch_name --PRAKKUM :: 11/06/2012 :: Bug 14068468
FROM ahl_routes_vl ar,ahl_visit_tasks_vl avt, ahl_mr_routes mrr
WHERE avt.visit_id = p_visit_id
AND NVL(avt.status_code,'Y') = 'PLANNING'
AND avt.task_type_code NOT IN ('SUMMARY','UNASSOCIATED','STAGE') ----SKPATHAK :: VWPE: ER:12424063 :: 13-JUN-2012 :: Need not create project task for a stage
AND avt.mr_route_id = mrr.mr_route_id (+)
AND mrr.route_id = ar.route_id (+)
UNION ALL
SELECT SUBSTR(NVL(amh.title,avt.visit_task_name),1,20) task_name, NVL(amh.title,avt.visit_task_name) description,
avt.visit_task_name, avt.visit_task_number, avt.start_date_time task_start_date, avt.visit_task_id,
avt.end_date_time task_end_date, avt.task_type_code, avt.cost_parent_id, avt.service_type_code, avt.status_code -- PRAKKUM :: PIE :: 13-OCT-2010
, avt.repair_batch_name --PRAKKUM :: 11/06/2012 :: Bug 14068468
FROM ahl_mr_headers_v amh,ahl_visit_tasks_vl avt
WHERE avt.visit_id = p_visit_id
AND NVL(avt.status_code,'Y') = 'PLANNING'
AND avt.task_type_code = 'SUMMARY'
AND avt.summary_task_flag = 'N'
AND avt.mr_id = amh.mr_header_id (+)
UNION ALL
SELECT SUBSTR(avt.visit_task_name,1,20) task_name, avt.visit_task_name description,
avt.visit_task_name, avt.visit_task_number, avt.start_date_time task_start_date, avt.visit_task_id,
avt.end_date_time task_end_date, avt.task_type_code, avt.cost_parent_id, avt.service_type_code, avt.status_code -- PRAKKUM :: PIE :: 13-OCT-2010
, avt.repair_batch_name --PRAKKUM :: 11/06/2012 :: Bug 14068468
FROM ahl_visit_tasks_vl avt
WHERE avt.visit_id = p_visit_id
AND NVL(avt.status_code,'Y') = 'PLANNING'
AND ((avt.task_type_code = 'SUMMARY' AND avt.summary_task_flag = 'Y')
OR (avt.task_type_code ='UNASSOCIATED'))
--ORDER BY 4;
SELECT pt.task_id,pt.project_id,pt.parent_task_id, pt.task_name
FROM PA_TASKS pt
WHERE
pt.project_id=x_prjId;
UPDATE AHL_VISITS_B
SET PROJECT_ID = l_project_out.pa_project_id,
OBJECT_VERSION_NUMBER = c_visit_rec.object_version_number + 1,
--TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE VISIT_ID = p_visit_id;
UPDATE AHL_VISIT_TASKS_B
SET PROJECT_TASK_ID = l_task_out(z).pa_task_id,
OBJECT_VERSION_NUMBER = l_obj_version + 1,
--TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE VISIT_ID = p_visit_id AND VISIT_TASK_NUMBER = l_task_in(z).PA_TASK_NUMBER;
PROCEDURE Update_Project(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
p_module_type IN VARCHAR2 := Null,
p_visit_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
AS
-- Define local Variables
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Project';
SELECT ppe.pm_source_reference, ppe1.pm_source_reference PARENT_TASK_REFERENCE, ppe.proj_element_id TASK_ID
, parent_p_tsks.task_id PARENT_TASK_ID, PPEV.DISPLAY_SEQUENCE
--SELECT ppe.pm_source_reference, ppe1.pm_source_reference PARENT_TASK_REFERENCE, ppe.proj_element_id
FROM pa_proj_element_versions ppev, pa_object_relationships por,
pa_proj_element_versions ppev1, pa_proj_elements ppe,
pa_proj_elements ppe1,
pa_tasks parent_p_tsks
WHERE ppev.element_version_id = por.object_id_to1
AND por.relationship_type = 'S' -- WORKPLAN STRUCTURE
AND por.object_id_from1 = ppev1.element_version_id
AND ppev.project_id = c_project_id
AND ppev1.project_id = c_project_id
AND ppe.project_id = ppev.project_id
AND ppe.proj_element_id = ppev.proj_element_id
AND ppe1.project_id = ppev1.project_id
AND ppe1.proj_element_id = ppev1.proj_element_id
AND parent_p_tsks.task_id (+) = ppe1.proj_element_id
ORDER BY ppev.DISPLAY_SEQUENCE;
SELECT CURR.PM_TASK_REFERENCE, P_CURR.PM_TASK_REFERENCE PARENT_TASK_REFERENCE,
CURR.TASK_ID -- PRAKKUM :: 24-NOV-2010 :: Bug 9370120 :: FP:12.0-12.2 :: Fetch project task id as well
, P_CURR.task_id PARENT_TASK_ID, PPEV.DISPLAY_SEQUENCE
FROM PA_TASKS CURR, PA_PROJ_ELEMENT_VERSIONS PPEV, PA_TASKS P_CURR
WHERE CURR.PROJECT_ID = c_project_id
AND CURR.TOP_TASK_ID <> c_top_task_id
AND PPEV.PROJ_ELEMENT_ID = CURR.TASK_ID
AND PPEV.PROJECT_ID = c_project_id
AND P_CURR.TASK_ID (+) = CURR.PARENT_TASK_ID
ORDER BY PPEV.DISPLAY_SEQUENCE;
SELECT CURR.TASK_ID TASK_ID, CURR.PARENT_TASK_ID PARENT_TASK_ID, PPEV.DISPLAY_SEQUENCE
FROM PA_TASKS CURR,
PA_PROJ_ELEMENT_VERSIONS PPEV
WHERE CURR.PROJECT_ID = c_project_id
AND CURR.TOP_TASK_ID not IN
(
SELECT DISTINCT TOP_TASK_ID
FROM AHL_VISIT_TASKS_VL VT, PA_PROJ_ELEMENT_VERSIONS PPEV, PA_TASKS PTSKS
WHERE PPEV.PROJECT_ID = c_project_id
AND VT.VISIT_ID = c_visit_id -- PRAKKUM :: 17-JAN-2013 :: FPBug 16481709 for BaseBug 14828418
AND PPEV.PROJ_ELEMENT_ID = VT.PROJECT_TASK_ID
AND PTSKS.TASK_ID = PPEV.PROJ_ELEMENT_ID
) /* Query to get all the top project tasks corresponding to visits */
AND PPEV.PROJ_ELEMENT_ID = CURR.TASK_ID
AND PPEV.PROJECT_ID = c_project_id
ORDER BY PPEV.DISPLAY_SEQUENCE;
SELECT distinct PTSKS.TOP_TASK_ID TOP_TASK_ID
FROM AHL_VISIT_TASKS_VL VT, PA_PROJ_ELEMENT_VERSIONS PPEV, PA_TASKS PTSKS
WHERE PPEV.PROJECT_ID = c_project_id
AND PPEV.PROJ_ELEMENT_ID = VT.PROJECT_TASK_ID
AND PTSKS.TASK_ID = PPEV.PROJ_ELEMENT_ID
AND VT.VISIT_ID = c_visit_id;
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
SELECT count(*) FROM AHL_VISIT_TASKS_VL
WHERE VISIT_ID = x_id
AND NVL(status_code, 'Y') <> NVL ('DELETED', 'X') ;
SELECT PPEV.DISPLAY_SEQUENCE, VT.*
FROM AHL_VISIT_TASKS_VL VT, PA_PROJ_ELEMENT_VERSIONS PPEV
WHERE VT.VISIT_ID = x_id
AND NVL(VT.status_code, 'Y') <> NVL ('DELETED', 'X')
AND PPEV.PROJ_ELEMENT_ID (+) = VT.PROJECT_TASK_ID;
SELECT TASK_ID, TASK_NUMBER
FROM PA_TASKS
WHERE PROJECT_ID = x_id;
SELECT CURR.TASK_ID, PARENT.PM_TASK_REFERENCE PARENT_TASK_REFERENCE
FROM PA_TASKS CURR, PA_TASKS PARENT
WHERE CURR.PROJECT_ID = c_project_id
AND CURR.TASK_NUMBER = c_task_number
AND PARENT.TASK_ID (+) = CURR.PARENT_TASK_ID;
SELECT PPEV.DISPLAY_SEQUENCE
FROM PA_PROJ_ELEMENT_VERSIONS PPEV
WHERE PPEV.PROJ_ELEMENT_ID = c_project_task_id;
SELECT 'x'
FROM pjm_project_parameters_v
WHERE project_id = x_proj_id
AND organization_id = x_org_id;
SELECT default_cost_group_id
FROM mtl_parameters
WHERE organization_id = p_org_id;
SELECT ppa.project_status_code
FROM ahl_visits_b avb, pa_projects_all ppa
WHERE avb.visit_id = x_id
AND avb.project_id = ppa.project_id;
SELECT * FROM (
SELECT SUBSTR(NVL(ar.route_no,avt.visit_task_name),1,20) task_name,
SUBSTR(NVL(ar.title,avt.visit_task_name),1,250) description,
avt.visit_task_name, avt.visit_task_number, avt.start_date_time task_start_date,
avt.end_date_time task_end_date, avt.project_Task_id project_task_id, ppev.display_sequence,
avt.task_type_code, avt.cost_parent_id, avt.service_type_code, avt.visit_task_id
, avt.repair_batch_name --PRAKKUM :: 11/06/2012 :: Bug 14068468
FROM ahl_routes_vl ar,ahl_visit_tasks_vl avt, ahl_mr_routes mrr, pa_proj_element_versions ppev
WHERE avt.visit_id = p_visit_id
--VWPE: ER:12424063 :: SKPATHAK :: 07-JUN-2012 :: Tasks should not be in deleted status
AND NVL(avt.status_code,'Y') <> 'DELETED'
AND avt.task_type_code NOT IN ('SUMMARY','UNASSOCIATED','STAGE') ----SKPATHAK :: VWPE: ER:12424063 :: 13-JUN-2012 :: Need not create project task for a stage
AND avt.mr_route_id = mrr.mr_route_id (+)
AND mrr.route_id = ar.route_id (+)
AND ppev.proj_element_id (+) = avt.project_task_id
UNION ALL
SELECT SUBSTR(NVL(amh.title,avt.visit_task_name),1,20) task_name, NVL(amh.title,avt.visit_task_name) description,
avt.visit_task_name, avt.visit_task_number, avt.start_date_time task_start_date,
avt.end_date_time task_end_date, avt.project_Task_id project_task_id, ppev.display_sequence,
avt.task_type_code, avt.cost_parent_id, avt.service_type_code, avt.visit_task_id
, avt.repair_batch_name --PRAKKUM :: 11/06/2012 :: Bug 14068468
FROM ahl_mr_headers_v amh,ahl_visit_tasks_vl avt, pa_proj_element_versions ppev
WHERE avt.visit_id = p_visit_id
--VWPE: ER:12424063 :: SKPATHAK :: 07-JUN-2012 :: Tasks should not be in deleted status
AND NVL(avt.status_code,'Y') <> 'DELETED'
AND avt.task_type_code = 'SUMMARY'
AND avt.summary_task_flag = 'N'
AND avt.mr_id = amh.mr_header_id (+)
AND ppev.proj_element_id (+) = avt.project_task_id
UNION ALL
SELECT SUBSTR(avt.visit_task_name,1,20) task_name, avt.visit_task_name description,
avt.visit_task_name, avt.visit_task_number, avt.start_date_time task_start_date,
avt.end_date_time task_end_date, avt.project_Task_id project_task_id, ppev.display_sequence,
avt.task_type_code, avt.cost_parent_id, avt.service_type_code, avt.visit_task_id
, avt.repair_batch_name --PRAKKUM :: 11/06/2012 :: Bug 14068468
FROM ahl_visit_tasks_vl avt, pa_proj_element_versions ppev
WHERE avt.visit_id = p_visit_id
--VWPE: ER:12424063 :: SKPATHAK :: 07-JUN-2012 :: Tasks should not be in deleted status
AND NVL(avt.status_code,'Y') <> 'DELETED'
AND ((avt.task_type_code = 'SUMMARY' AND avt.summary_task_flag = 'Y')
OR (avt.task_type_code ='UNASSOCIATED'))
AND ppev.proj_element_id (+) = avt.project_task_id
--ORDER BY 4;
SELECT segment1
from pa_projects_all
where project_id = c_project_id;
SELECT 'Y' FROM
AHL_VISITS_B vst
WHERE
visit_id = c_visit_id AND
EXISTS (
SELECT 1
FROM ahl_visit_tasks_b avt,
pa_tasks pt
WHERE avt.visit_id = vst.visit_id
AND avt.visit_id = c_visit_id
AND avt.repair_batch_name IS NOT NULL
and avt.project_task_id = pt.task_id
AND EXISTS (
SELECT 1
FROM mtl_material_transactions mmt
WHERE mmt.project_id = vst.PROJECT_ID
AND mmt.task_id = nvl(pt.task_id,mmt.task_id)
)
);
SAVEPOINT Update_project;
l_Project_rec.PA_PROJECT_ID := c_visit_rec.PROJECT_ID; -- Update the Parent Project
l_Project_rec.PA_PROJECT_ID := c_visit_rec.PROJECT_ID; -- Update the Parent Project
fnd_log.string(l_log_statement,L_DEBUG_KEY,'Before calling PA_PROJECT_PUB.UPDATE_PROJECT');
PA_PROJECT_PUB.UPDATE_PROJECT
(p_api_version_number => 1,
p_commit => l_commit,
p_init_msg_list => l_init_msg_list,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_workflow_started => l_workflow_started,
p_pm_product_code => G_PM_PRODUCT_CODE,
p_project_in => l_project_rec,
p_project_out => l_project_out,
p_key_members => l_key_members,
p_class_categories => l_class_categories,
p_tasks_in => l_task_in,
p_tasks_out => l_task_out,
p_pass_entire_structure => l_pass_entire_structure
); -- PRAKKUM :: FP:PIE :: 13-OCT-2010 ::
fnd_log.string(l_log_statement,L_DEBUG_KEY,'After calling PA_PROJECT_PUB.UPDATE_PROJECT - l_return_status = '||l_return_status);
fnd_log.string(l_log_statement,L_DEBUG_KEY,'Errors from PA_PROJECT_PUB.UPDATE_PROJECT - l_msg_count = '||l_msg_count);
UPDATE AHL_VISIT_TASKS_B SET
PROJECT_TASK_ID = c_task_proj_rec.task_id,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
--TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
/*B6436358 - sowsubra - Visit task number is of type number and task number in projects
is of type char. Hence the invalid number error. And so added a to_char function to convert
the visit task number to character*/
WHERE VISIT_ID = p_visit_id AND TO_CHAR(VISIT_TASK_NUMBER) = c_task_proj_rec.task_number
AND PROJECT_TASK_ID is NULL;
UPDATE AHL_VISIT_TASKS_B SET
PROJECT_TASK_ID = c_task_proj_rec.task_id,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
--TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
/*B6436358 - sowsubra - Visit task number is of type number and task number in projects
is of type char. Hence the invalid number error. And so added a to_char function to convert
the visit task number to character*/
WHERE VISIT_ID = p_visit_id
AND TO_CHAR(c_visit_rec.visit_number) || '-' || TO_CHAR(VISIT_TASK_NUMBER) = c_task_proj_rec.task_number
AND PROJECT_TASK_ID is NULL;
UPDATE AHL_VISITS_B
SET TOP_PROJECT_TASK_ID = l_top_task_id
WHERE VISIT_ID = p_visit_id;
ROLLBACK TO Update_Project;
ROLLBACK TO Update_Project;
ROLLBACK TO Update_Project;
p_procedure_name => 'Update_Project',
p_error_text => SUBSTR(SQLERRM,1,500));
END Update_Project;
PROCEDURE Delete_Project(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
p_module_type IN VARCHAR2 := Null,
p_visit_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2 )
AS
-- Define local Variables
L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Project';
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
SELECT count(*) FROM PJM_PROJECT_PARAMETERS
WHERE Project_ID = x_proj_id;
SAVEPOINT Delete_project;
fnd_log.string(l_log_statement,L_DEBUG_KEY,'Before calling : PA_PROJECT_PUB.CHECK_DELETE_PROJECT_OK');
PA_PROJECT_PUB.CHECK_DELETE_PROJECT_OK
( p_api_version_number => 1
, p_init_msg_list => l_init_msg_list
, p_return_status => l_return_status
, p_msg_count => l_msg_count
, p_msg_data => l_msg_data
, p_project_id => c_visit_rec.PROJECT_ID
, p_pm_project_reference => c_visit_rec.visit_number
, p_delete_project_ok_flag => l_del_proj_flag
);
fnd_log.string(l_log_statement,L_DEBUG_KEY,'After calling PA_PROJECT_PUB.CHECK_DELETE_PROJECT_OK - l_return_status = '||l_return_status);
fnd_log.string(l_log_statement,L_DEBUG_KEY,'Before calling PA_PROJECT_PUB.DELETE_PROJECT');
PA_PROJECT_PUB.DELETE_PROJECT
( p_api_version_number => 1
,p_commit => l_commit
,p_init_msg_list => l_init_msg_list
,p_msg_count => l_msg_count
,p_msg_data => l_msg_data
,p_return_status => l_return_status
,p_pm_product_code => G_PM_PRODUCT_CODE
,p_pm_project_reference => c_visit_rec.visit_number
,p_pa_project_id => c_visit_rec.PROJECT_ID
);
fnd_log.string(l_log_statement,L_DEBUG_KEY,'After calling PA_PROJECT_PUB.DELETE_PROJECT - l_return_status = '||l_return_status);
fnd_log.string(l_log_statement,L_DEBUG_KEY,'Errors from PA_PROJECT_PUB.DELETE_PROJECT API : '|| x_msg_count );
ROLLBACK TO Delete_Project;
ROLLBACK TO Delete_Project;
ROLLBACK TO Delete_Project;
p_procedure_name => 'Delete_Project',
p_error_text => SUBSTR(SQLERRM,1,500));
END Delete_Project;
SELECT mrh1.effective_from,mrh1.effective_to
FROM AHL_MR_HEADERS_APP_V mrh1, AHL_MR_ROUTES mrr
WHERE mrh1.mr_status_code = 'COMPLETE' AND
trunc(mrh1.effective_from) <= trunc(sysdate) AND
trunc(nvl(mrh1.effective_to,sysdate)) >= trunc(sysdate) AND
mrr.mr_route_id = c_id AND
mrh1.mr_header_id = mrr.mr_header_id AND
mrh1.version_number = (select max(version_number)
from AHL_MR_HEADERS_APP_V mrh2
where mrh2.title = mrh1.title
and mrh2.mr_status_code = 'COMPLETE'
and trunc(effective_from) <= trunc(sysdate) AND
trunc(nvl(effective_to,sysdate)) >= trunc(sysdate));
SELECT ra.*
FROM AHL_ROUTES_APP_V ra, AHL_MR_ROUTES mrr
WHERE ra.route_id = mrr.route_id AND
mrr.mr_route_id = c_id AND
ra.revision_status_code = 'COMPLETE';
SELECT mrh1.effective_from,mrh1.effective_to
FROM AHL_MR_HEADERS_APP_V mrh1, AHL_MR_ROUTES mrr
WHERE mrh1.mr_status_code = 'COMPLETE' AND
trunc(mrh1.effective_from) <= trunc(sysdate) AND
trunc(nvl(mrh1.effective_to,sysdate)) >= trunc(sysdate) AND
mrr.mr_route_id = c_id AND
mrh1.mr_header_id = mrr.mr_header_id AND
mrh1.version_number = (select max(version_number)
from AHL_MR_HEADERS_APP_V mrh2
where mrh2.title = mrh1.title
and mrh2.mr_status_code = 'COMPLETE'
and trunc(effective_from) <= trunc(sysdate) AND
trunc(nvl(effective_to,sysdate)) >= trunc(sysdate));
SELECT ra.*
FROM AHL_ROUTES_APP_V ra, AHL_MR_ROUTES mrr
WHERE ra.route_id = mrr.route_id AND
mrr.mr_route_id = c_id AND
ra.revision_status_code = 'COMPLETE';
SELECT AHL_VISITS_VL.*,AHL_VWP_VISITS_PVT.Is_Old_Visit(VISIT_ID) IS_OLD_VISIT FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id
AND NVL(STATUS_CODE,'X') <> 'DELETED';
SELECT COUNT(*) FROM AHL_DEPARTMENT_SHIFTS
WHERE DEPARTMENT_ID = x_id;
SELECT * FROM AHL_VISIT_TASKS_VL
WHERE VISIT_ID = x_visit_id
AND NVL(STATUS_CODE,'X') not in ('DELETED','RELEASED','CLOSED')
AND (TASK_TYPE_CODE <> 'SUMMARY' OR
(TASK_TYPE_CODE = 'SUMMARY' AND
MR_ID IS NOT NULL));
SELECT COUNT(*) FROM AHL_SIMULATION_PLANS_VL
WHERE SIMULATION_PLAN_ID = x_id
AND PRIMARY_PLAN_FLAG = 'Y';
SELECT T1.* FROM AHL_MR_HEADERS_APP_V T1, AHL_MR_ROUTES_V T2
WHERE T1.MR_HEADER_ID = T2.MR_HEADER_ID
AND T2.MR_ROUTE_ID = x_id;
SELECT T1.EFFECTIVE_FROM,T1.EFFECTIVE_TO
FROM AHL_MR_HEADERS_APP_V T1, AHL_MR_ROUTES_V T2
WHERE T1.MR_HEADER_ID = T2.MR_HEADER_ID
AND T2.MR_ROUTE_ID = x_id;
SELECT T1.EFFECTIVE_FROM,T1.EFFECTIVE_TO
FROM ahl_mr_headers_app_v T1, AHL_MR_ROUTES_V T2
WHERE T1.mr_status_code = 'COMPLETE' AND
trunc(T1.effective_from) <= trunc(sysdate) AND
trunc(nvl(T1.effective_to,sysdate)) >= trunc(sysdate) AND
T2.MR_ROUTE_ID = x_id AND
T1.MR_HEADER_ID = T2.MR_HEADER_ID AND
T1.version_number = (select max(version_number)
from ahl_mr_headers_app_v mr1
where mr1.title = T1.title
and mr1.mr_status_code = 'COMPLETE'
and trunc(effective_from) <= trunc(sysdate) AND
trunc(nvl(effective_to,sysdate)) >= trunc(sysdate));
SELECT T1.* FROM AHL_ROUTES_APP_V T1, AHL_MR_ROUTES_V T2
WHERE T1.ROUTE_ID = T2.ROUTE_ID
AND T2.MR_ROUTE_ID = x_id
AND T1.REVISION_STATUS_CODE = 'COMPLETE';
SELECT T1.START_DATE_ACTIVE,T1.END_DATE_ACTIVE
FROM AHL_ROUTES_APP_V T1, AHL_MR_ROUTES_V T2
WHERE T1.ROUTE_ID = T2.ROUTE_ID
AND T2.MR_ROUTE_ID = x_id
AND T1.REVISION_STATUS_CODE = 'COMPLETE';
SELECT VT.MR_ROUTE_ID
FROM AHL_VISIT_TASKS_B VT
WHERE VT.VISIT_ID = x_id
AND NVL(STATUS_CODE,'X') not in ('DELETED','RELEASED','CLOSED')
AND VT.MR_Route_ID IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM AHL_MR_ROUTES T1, AHL_ROUTES_APP_V T2, AHL_MR_HEADERS_APP_V B
WHERE T1.MR_ROUTE_ID = VT.MR_ROUTE_ID
AND T1.MR_HEADER_ID = B.MR_HEADER_ID
AND T1.ROUTE_ID = T2.ROUTE_ID
AND T2.REVISION_STATUS_CODE = 'COMPLETE');
SELECT VISIT_TASK_ID, VISIT_TASK_NUMBER FROM AHL_VISIT_TASKS_B
WHERE VISIT_ID = x_id
AND NVL(STATUS_CODE,'X') not in ('DELETED','RELEASED','CLOSED')
AND MR_Route_ID IS NULL
AND TASK_TYPE_CODE <> 'SUMMARY';
SELECT count(*) FROM Ahl_MR_Items_V T1, AHL_MR_ROUTES_APP_V T2
WHERE T1.Inventory_Item_ID = x_item_id
AND T1.MR_HEADER_ID = T2.MR_HEADER_ID
AND MR_ROUTE_ID = x_mr_route_id;
SELECT DEPARTMENT_ID FROM AHL_VISIT_TASKS_B
WHERE VISIT_ID = x_id
AND NVL(STATUS_CODE,'X') not in ('DELETED','RELEASED','CLOSED')
AND DEPARTMENT_ID IS NOT NULL;
SELECT DEPARTMENT_ID, VISIT_TASK_NUMBER FROM AHL_VISIT_TASKS_B
WHERE VISIT_ID = x_id
AND NVL(STATUS_CODE,'X') not in ('DELETED','RELEASED','CLOSED')
AND DEPARTMENT_ID IS NOT NULL;
SELECT INVENTORY_ITEM_ID, INSTANCE_NUMBER FROM CSI_ITEM_INSTANCES
WHERE instance_id = c_instance_id;
SELECT count(*)FROM CSI_ITEM_INSTANCES
WHERE Instance_Id = c_instance_id
AND Inventory_Item_Id = c_item_id
AND Inv_Master_Organization_Id = c_org_id
AND ACTIVE_START_DATE <= sysdate
AND (ACTIVE_END_DATE >= sysdate OR ACTIVE_END_DATE IS NULL);
SELECT name, AHL_UTIL_UC_PKG.GET_UC_STATUS_CODE(UNIT_CONFIG_HEADER_ID) uc_status
FROM ahl_unit_config_headers uc,
csi_item_instances csis
WHERE uc.csi_item_instance_id=csis.instance_id
AND (uc.active_end_date IS NULL OR uc.active_end_date > SYSDATE)
AND csis.instance_id = p_instance_id;
SELECT status_code FROM ahl_visit_tasks_b
WHERE visit_task_id = c_task_id;
Fnd_Message.SET_NAME('AHL','AHL_VWP_PRD_VST_NOT_PLANNING'); -- **** IMPortant uPDATE FOR PARTIALLY RELEASED
SELECT wip_entity_id
FROM AHL_WORKORDERS
WHERE visit_id = x_visit_id
AND VISIT_TASK_ID IS NULL
AND MASTER_WORKORDER_FLAG = 'Y';
SELECT visit_task_number, task_type_code, SERVICE_REQUEST_ID, MR_ID, originating_task_id
FROM AHL_VISIT_TASKS_B
WHERE visit_task_id = c_visit_task_id;
SELECT PARENT.visit_task_number, PARENT.visit_task_id
FROM AHL_VISIT_TASKS_B PARENT, AHL_VISIT_TASKS_B CHILD
WHERE PARENT.visit_task_id = CHILD.originating_task_id
AND CHILD.visit_task_id = c_visit_task_id;
SELECT 1 FROM AHL_WORKORDERS
WHERE VISIT_TASK_ID = c_visit_task_id;
SELECT wip_entity_id FROM AHL_WORKORDERS
WHERE VISIT_TASK_ID = c_visit_task_id;
SELECT visit_task_id,
visit_task_number,
NVL(originating_task_id, -1)
FROM AHL_VISIT_TASKS_B
WHERE visit_id = c_visit_id
AND task_type_code='SUMMARY'
AND mr_id IS NOT NULL
ORDER BY 3;*/
SELECT visit_task_id,
visit_task_number,
NVL(originating_task_id, -1)
FROM AHL_VISIT_TASKS_B
WHERE visit_id = c_visit_id
AND task_type_code='SUMMARY'
AND (mr_id IS NOT NULL OR unit_effectivity_id IS NOT NULL)
AND NVL(STATUS_CODE, 'X') <> 'DELETED'
-- SKPATHAK :: Bug 9444849 :: 19-MAR-2010
-- This condition added during bug #4075702 fix is not needed
-- since after opening this cursor we have a check if the visit task id fetched by this cursor already has a corresponding WO
-- so only for tasks in planning we are building the relationships
-- Also it is necessary to remove this condition to fix the bug 9444849, since the parent task can be implemented as well
--AND NVL(STATUS_CODE, 'X') = 'PLANNING' --Srini Bug #4075702
ORDER BY 3;
SELECT visit_task_number, visit_task_id
FROM AHL_VISIT_TASKS_B
WHERE visit_id = c_visit_id
AND originating_task_id = c_mr_task_id
AND task_type_code <> 'SUMMARY'
AND NVL(STATUS_CODE, 'X') = 'PLANNING' --Srini Bug #4075702
AND NVL(STATUS_CODE, 'X') <> 'DELETED';
SELECT visit_task_number, visit_task_id
FROM AHL_VISIT_TASKS_B
WHERE visit_id = c_visit_id
AND NVL(STATUS_CODE, 'X') = 'PLANNING'
AND task_type_code IN ('UNASSOCIATED','STAGE');
SELECT wip_entity_id
FROM AHL_WORKORDERS
WHERE VISIT_TASK_ID = c_visit_task_id
-- SKPATHAK :: Bug 9940275 :: 18-AUG-2010 :: Removed status '7' from below condtn
-- This cursor is used to check if a visit task has a corresponding WO and if not, WO relationship needs to be created
-- So even if the status is 7 (cancelled), cursor must fetch the result, since relationship need not be created in this case
AND STATUS_CODE <> '22';
SELECT wip_entity_id
FROM AHL_WORKORDERS
WHERE VISIT_TASK_ID = c_visit_task_id
AND STATUS_CODE NOT IN ('22','7');
SELECT wip_entity_id
FROM AHL_WORKORDERS
WHERE visit_id = c_visit_id
AND VISIT_TASK_ID IS NULL
AND MASTER_WORKORDER_FLAG = 'Y'
AND STATUS_CODE NOT IN ('7', '22');
SELECT PARENT.visit_task_number parent_task_number,
CHILD.visit_task_number child_task_number
FROM AHL_VISIT_TASKS_B PARENT,
AHL_VISIT_TASKS_B CHILD,
AHL_TASK_LINKS LINK
WHERE PARENT.visit_task_id = LINK.parent_task_id
AND CHILD.visit_task_id = LINK.visit_task_id
AND NVL(PARENT.STATUS_CODE,'X') = 'PLANNING'
AND NVL(CHILD.STATUS_CODE,'X') = 'PLANNING'
AND (PARENT.visit_task_id = c_visit_task_id
OR CHILD.visit_task_id = c_visit_task_id);
SELECT PARENT.visit_task_number parent_task_number,
CHILD.visit_task_number child_task_number
FROM AHL_VISIT_TASKS_B PARENT,
AHL_VISIT_TASKS_B CHILD,
AHL_TASK_LINKS LINK
WHERE PARENT.visit_task_id = LINK.parent_task_id
AND CHILD.visit_task_id = LINK.visit_task_id
AND NVL(PARENT.STATUS_CODE,'X') = 'PLANNING' --Srini Bug #4075702
AND PARENT.visit_id = c_visit_id
AND CHILD.visit_id = c_visit_id;
SELECT AHL_VISITS_VL.*, AHL_VWP_VISITS_PVT.Is_Old_Visit(VISIT_ID) IS_OLD_VISIT FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
SELECT * FROM AHL_VISIT_TASKS_VL
WHERE VISIT_ID = x_id
AND NVL(STATUS_CODE, 'X') = 'PLANNING'
AND (TASK_TYPE_CODE <> 'SUMMARY' OR
(TASK_TYPE_CODE = 'SUMMARY' AND
(MR_ID IS NOT NULL OR UNIT_EFFECTIVITY_ID IS NOT NULL)));
SELECT count(*) FROM AHL_VISIT_TASKS_VL
WHERE VISIT_ID = x_id
AND NVL(STATUS_CODE, 'X') = 'PLANNING'
AND (TASK_TYPE_CODE <> 'SUMMARY' OR
(TASK_TYPE_CODE = 'SUMMARY' AND
MR_ID IS NOT NULL OR UNIT_EFFECTIVITY_ID IS NOT NULL));
SELECT Route_Id FROM AHL_MR_ROUTES_V
WHERE MR_ROUTE_ID = x_id;
SELECT workorder_id, object_version_number, status_code
FROM AHL_WORKORDERS
WHERE VISIT_TASK_ID = x_id
AND STATUS_CODE not in ('22','7'); --(22-Deleted, 7-Cancelled)
SELECT wo.workorder_id, wo.object_version_number, wo.status_code,
WIP.SCHEDULED_START_DATE,
WIP.SCHEDULED_COMPLETION_DATE
FROM AHL_WORKORDERS WO,
WIP_DISCRETE_JOBS WIP
WHERE wo.VISIT_ID = x_visit_id
AND wo.VISIT_TASK_ID IS NULL
AND wo.MASTER_WORKORDER_FLAG = 'Y'
AND WIP.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND wo.STATUS_CODE not in ('22','7'); --(22-Deleted, 7-Cancelled)
SELECT wo.status_code
FROM AHL_WORKORDERS WO
WHERE wo.VISIT_ID = x_visit_id
AND wo.VISIT_TASK_ID IS NULL
AND wo.MASTER_WORKORDER_FLAG = 'Y'
AND wo.STATUS_CODE not in ('22','7'); --(22-Deleted, 7-Cancelled)
SELECT wo.status_code
FROM AHL_WORKORDERS WO,
AHL_VISIT_TASKS_B t
WHERE wo.VISIT_ID = x_orig_visit_id
AND wo.visit_task_id = t.visit_task_id
AND t.visit_task_number = (Select visit_task_number
from ahl_visit_tasks_b
where visit_task_id = x_visit_task_id)
AND wo.STATUS_CODE not in ('22','7'); --(22-Deleted, 7-Cancelled)
SELECT min(start_date_time), max(end_date_time)
--TCHIMIRA::19-FEB-2010::BUG 9384614
-- Use the base table instead of the vl view
FROM ahl_visit_tasks_b VST
START WITH visit_task_id = x_task_id
-- anraj changed coz the nvl on the RHS is not required
-- AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
AND NVL(VST.status_code, 'Y') <> 'DELETED'
CONNECT BY originating_task_id = PRIOR visit_task_id;
select t.visit_task_id
from ahl_visit_tasks_b t,
ahl_visits_b v
where v.visit_id = c_visit_id
and v.visit_id = t.visit_id
--and v.status_code = 'PARTIALLY RELEASED'
and v.status_code IN ('PLANNING', 'PARTIALLY RELEASED') -- PRAKKUM :: BUG Internal :: 19/01/2012 :: Task Time Adjustments
and t.start_date_time < SYSDATE
and t.status_code ='PLANNING'
and t.task_type_code <>'SUMMARY'
-- SKPATHAK :: ER: 9147951 :: 11-JAN-2010 :: Adjust task times only if past task start date is null
and t.past_task_start_date IS NULL
and (not exists (select 1 from ahl_task_links l0
where l0.parent_task_id = t.visit_task_id
or l0.visit_task_id = t.visit_task_id)
or t.visit_task_id in (select l1.parent_task_id from ahl_task_links l1
where not exists (select l2.visit_task_id from ahl_task_links l2
where l2.visit_task_id = l1.parent_task_id)));
SELECT inventory_item_id,instance_id
FROM ahl_visit_tasks_vl
WHERE visit_id = c_visit_id
AND NVL(status_code, 'Y') <> 'DELETED'
AND ROWNUM = 1;
SELECT status_code, close_date_time
FROM ahl_visits_b
WHERE visit_id = c_visit_id;
SELECT ar.route_no||'.'||substrb(ar.title,1,(240 - (lengthb(ar.route_no) + 1))) workorder_description
FROM ahl_routes_vl ar,ahl_visit_tasks_b avt, ahl_mr_routes mrr
WHERE avt.visit_task_id = p_task_id
AND nvl(avt.status_code,'Y') = 'PLANNING'
AND avt.mr_route_id = mrr.mr_route_id
AND mrr.route_id = ar.route_id;
SELECT wo.workorder_id, stage.stage_id, task.task_type_code
FROM ahl_visit_tasks_b task, ahl_vwp_stages_b stage, ahl_workorders wo
WHERE task.visit_id = c_visit_id
AND task.stage_id = stage.stage_id(+)
-- do not select the default stage task
AND ((task.task_type_code = 'STAGE' AND task.stage_id IS NOT NULL)
OR task.task_type_code = 'DUPLICATE')
-- SKPATHAK :: 07-JUN-2011 :: VWPE: ER:12424063 :: Consider partially implemented stages also
AND stage.stage_status_code IN ('PLANNING', 'PARTIALLY RELEASED')
AND task.visit_task_id = wo.visit_task_id;
SELECT VISIT_TASK_ID
FROM AHL_VISIT_TASKS_B
WHERE STAGE_ID IS NULL
AND TASK_TYPE_CODE = 'STAGE'
AND VISIT_ID = c_visit_id;
SELECT status_code FROM ahl_visit_tasks_b
WHERE visit_task_id = c_task_id;
SELECT INST.inventory_item_id, UC.csi_item_instance_id
FROM AHL_UNIT_CONFIG_HEADERS UC,
CSI_ITEM_INSTANCES INST
WHERE UC.unit_config_status_code = 'COMPLETE'
AND NVL(UC.active_end_date, SYSDATE+1) > SYSDATE
AND INST.instance_id = UC.csi_item_instance_id
AND rownum = 1;
SELECT serial_number
FROM CSI_ITEM_INSTANCES
WHERE instance_id = c_instance_id;
SELECT INST.inventory_item_id, INST.instance_id
FROM AHL_VISIT_TASKS_B TSK,
CSI_ITEM_INSTANCES INST
WHERE TSK.visit_id = c_visit_id
AND TSK.status_code NOT IN ('DELETED', 'CANCELLED', 'CLOSED')
AND TSK.instance_id = INST.instance_id
AND INST.serial_number IS NOT NULL
AND rownum = 1;
/*c_visit_rec fetch has been moved after the project details are updated for the visit. Hence
moved the cursor after integrate_to_projects*/
OPEN c_get_vst_status_and_date (p_visit_id);
/*moved the call to integrate to projects after task times gets updated
esp in case of visits that is partially implemented*/
idx := idx + 1;
DELETE FROM AHL_UMP_RESOURCE_REQMNTS
WHERE object_type = 'UE'
AND MAINTENANCE_REQMNT_ID IN (SELECT unit_effectivity_id FROM ahl_visit_tasks_b WHERE visit_id = p_visit_id);
DELETE FROM AHL_UMP_MATERIAL_REQMNTS
WHERE object_type = 'UE'
AND MAINTENANCE_REQMNT_ID IN (SELECT unit_effectivity_id FROM ahl_visit_tasks_b WHERE visit_id = p_visit_id);
DELETE FROM AHL_UMP_MAINT_REQMNTS
WHERE object_type = 'UE'
AND MAINTENANCE_REQMNT_ID IN (SELECT unit_effectivity_id FROM ahl_visit_tasks_b WHERE visit_id = p_visit_id);
* update visit master workorder instance with serialized instance in any of the Repair Batches in this
* Component Visit, otherwise let still there be UC instance in Visit master workorder.
*/
IF (l_is_comp_visit = 'Y') THEN
-- check whether any serialized instance is available in any of the Repair Batches for this Component Visit
OPEN get_serialized_inst_csr (p_visit_id);
/*The project start/end dates have to be updated with the workorder scheduled
start/end dates.*/
IF (l_log_statement >= l_log_current_level) THEN
For i IN l_prd_workorder_tbl.FIRST..l_prd_workorder_tbl.LAST
LOOP
fnd_log.string(l_log_statement,
L_DEBUG_KEY,
'WorkOrder Id ('||i||'): '||l_prd_workorder_tbl(i).workorder_id);
'Before calling Update_Project_Task_Times.');
Update_Project_Task_Times(p_prd_workorder_tbl => l_prd_workorder_tbl,
p_commit =>'F',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
'Errors from Update_Project_Task_Times. Message count: ' || x_msg_count);
'Returned Success from Update_Project_Task_Times');
'Update Visit -- Released');
UPDATE AHL_VWP_STAGES_B
SET STAGE_STATUS_CODE = 'RELEASED',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE STAGE_ID = l_task_to_cancel_rec.stage_id;
UPDATE AHL_VISITS_B
SET STATUS_CODE = 'RELEASED',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
--TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE VISIT_ID = p_visit_id;
UPDATE AHL_VISITS_B
SET STATUS_CODE = c_orig_visit_rec.STATUS_CODE,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
--TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE VISIT_ID = p_visit_id;
UPDATE AHL_VISIT_TASKS_B
SET STATUS_CODE = 'RELEASED',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
--TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE VISIT_ID = p_visit_id
AND STATUS_CODE IN ('PLANNING', 'PARTIALLY RELEASED')
--SKPATHAK :: VWPE ER-12424063 :: 09-JUN-2011 :: Update the status of stage and duplicate tasks to CANCELLED
AND TASK_TYPE_CODE NOT IN ('STAGE','DUPLICATE');
UPDATE AHL_VISIT_TASKS_B
SET STATUS_CODE = 'CANCELLED',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
--TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE VISIT_ID = p_visit_id
AND STATUS_CODE = 'PLANNING'
AND ((TASK_TYPE_CODE = 'STAGE' AND STAGE_ID IS NOT NULL)
OR TASK_TYPE_CODE = 'DUPLICATE');
UPDATE AHL_VISIT_TASKS_B
SET STATUS_CODE = 'RELEASED',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
--TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE VISIT_ID = p_visit_id
AND STATUS_CODE IN ('PLANNING', 'PARTIALLY RELEASED')
--SKPATHAK :: VWPE ER-12424063 :: 09-JUN-2011 :: Need not s update the status of stage and duplicate tasks to RELEASED
AND TASK_TYPE_CODE NOT IN ('STAGE','DUPLICATE')
AND VISIT_TASK_NUMBER in (Select VISIT_TASK_NUMBER
FROM ahl_visit_tasks_b
where visit_id = p_orig_visit_id
AND STATUS_CODE = 'RELEASED');
UPDATE AHL_VISIT_TASKS_B
SET STATUS_CODE = 'CANCELLED',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
--TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE VISIT_ID = p_visit_id
AND STATUS_CODE = 'PLANNING'
AND TASK_TYPE_CODE IN ('STAGE','DUPLICATE')
AND VISIT_TASK_NUMBER in (Select VISIT_TASK_NUMBER
FROM ahl_visit_tasks_b
where visit_id = p_orig_visit_id
AND STATUS_CODE = 'RELEASED');
'Before calling AHL_VWP_RULES_PVT.Update_Visit_Task_Flag.');
AHL_VWP_RULES_PVT.Update_Visit_Task_Flag
(p_visit_id => p_visit_id,
p_flag => 'N',
x_return_status => x_return_status);
'After calling AHL_VWP_RULES_PVT.Update_Visit_Task_Flag');
* There is no need to delete Stage materials for Component Visits
* as there won't be any Stage associated with them.
*/
IF (l_is_comp_visit = 'N') THEN
-- TCHIMIRA :: 28-JUN-2011 :: VWP: ER:12673125 :: START
-- We need to delete materials of default stage task when a visit is P2Ped
IF ( p_module_type <> 'CST' ) THEN
IF AHL_VWP_VISITS_PVT.Is_Old_Visit(p_visit_id) = 'N' THEN -- PRAKKUM :: VWPE: ER:12856858 :: 10-AUG-2011 :: Upgrade Changes
IF (l_log_statement >= l_log_current_level) THEN
fnd_log.string(l_log_statement,
L_DEBUG_KEY,
'Before calling AHL_LTP_REQST_MATRL_PVT.Delete_Default_Stage_Materials');
AHL_LTP_REQST_MATRL_PVT.Delete_Default_Stage_Materials (
p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_module_type => 'VST',
p_visit_task_id => l_def_stage_task_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
'After calling AHL_LTP_REQST_MATRL_PVT.Delete_Default_Stage_Materials. and return status is:'||l_return_status);
SELECT 'X'
FROM FND_CONCURRENT_REQUESTS FCR, AHL_VISITS_B AVB
WHERE FCR.REQUEST_ID IN ( AVB.CST_EST_REQUEST_ID, AVB.REQUEST_ID )
AND FCR.PHASE_CODE IN ('P', 'R')
AND AVB.VISIT_ID = c_id;
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id
-- TCHIMIRA :: Bug 8594339 :: 19-NOV-2009
-- Lock the visit record
FOR UPDATE OF OBJECT_VERSION_NUMBER;
SELECT mr.title
FROM ahl_visit_tasks_vl avt, ahl_unit_effectivities_b ue, ahl_mr_headers_b mr
WHERE avt.visit_id = x_id
AND NVL(avt.status_code, 'X') = 'PLANNING'
AND (avt.task_type_code <> 'SUMMARY' OR
(avt.task_type_code = 'SUMMARY' AND
(avt.mr_id IS NOT NULL OR avt.unit_effectivity_id IS NOT NULL)))
AND avt.unit_effectivity_id = ue.unit_effectivity_id
AND NVL(ue.status_code, 'X') = 'EXCEPTION'
AND ue.mr_header_id = mr.mr_header_id
AND rownum = 1;
SELECT DISTINCT STAGES.STAGE_ID ,
STAGES.STAGE_NAME
FROM AHL_VISIT_TASKS_B TASKS,
AHL_VWP_STAGES_VL STAGES
WHERE
TASKS.VISIT_ID= c_visit_id AND
STAGES.STAGE_ID=TASKS.STAGE_ID AND
TASKS.TASK_TYPE_CODE <> 'STAGE' AND --SKPATHAK :: stages not having any visit tasks need not be validated
NOT EXISTS (
SELECT STAGE_ID
FROM AHL_VISIT_STAGE_TYP_ASOC
WHERE STAGE_ID=STAGES.STAGE_ID);
Get_Entl_rec_for_Delete(
p_visit_id => p_visit_id,
x_warranty_entl_tbl => l_entl_rec_tbl);
fnd_log.string(l_log_statement,L_DEBUG_KEY, 'Before calling AHL_LTP_SIMUL_PLAN_PVT.delete_simul_visits');
/*AHL_LTP_SIMUL_PLAN_PVT.delete_simul_visits
(p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => l_commit,
p_validation_level => p_validation_level,
p_visit_id => p_visit_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
fnd_log.string(l_log_statement,L_DEBUG_KEY, 'After calling AHL_LTP_SIMUL_PLAN_PVT.delete_simul_visits - l_return_status : '||l_return_status);
fnd_log.string( l_log_statement,L_DEBUG_KEY,'Errors from delete_simul_visits: '||x_msg_count);
SELECT 1 FROM AHL_VISITS_B
WHERE VISIT_ID = x_id;
SELECT start_date_time,status_code FROM AHL_VISITS_B
WHERE VISIT_ID = x_id
AND NVL(STATUS_CODE,'X') IN ('PARTIALLY RELEASED', 'PLANNING');
SELECT status_code FROM ahl_visit_tasks_b
WHERE visit_task_id = c_task_id;
SELECT past_task_start_date INTO l_tasks_tbl(i).past_task_start_date FROM ahl_visit_tasks_b WHERE visit_task_id = l_tasks_tbl(i).visit_task_id;
'Before calling DELETE_SIMUL_VISITS for visit id: ' ||p_visit_id);
/* AHL_LTP_SIMUL_PLAN_PVT.delete_simul_visits
(p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => 'F',
p_validation_level => p_validation_level,
p_visit_id => p_visit_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
'After calling DELETE_SIMUL_VISITS for visit id ' ||p_visit_id||'. Return Status = '|| l_return_status);
'Errors from DELETE_SIMUL_TASKS. Message count: ' || x_msg_count);
SELECT mr_id,visit_id,visit_task_id,status_code,task_type_code,nvl(originating_task_id,0) as originating_task_id,
summary_task_flag, inventory_item_id,item_organization_id,visit_task_number,end_date_time
FROM ahl_visit_tasks_b
WHERE visit_task_id = x_vst_task_id
-- SKPATHAK :: 02-MAY-2011 :: VWPE:: Commented the below line since summary tasks may not be in planning
--AND NVL(status_code,'X') in ('PLANNING')
order by visit_task_id;
SELECT mr_id,visit_id,visit_task_id,status_code,task_type_code,nvl(originating_task_id,0) as originating_task_id,
summary_task_flag, inventory_item_id,item_organization_id,visit_task_number,end_date_time
FROM ahl_visit_tasks_b
WHERE visit_task_id = x_originating_tsk_id
AND NVL(status_code,'X') = 'PLANNING'
AND task_type_code = 'SUMMARY';
SELECT visit_task_id,nvl(originating_task_id,0) as originating_task_id,visit_task_number
FROM ahl_visit_tasks_b
WHERE visit_task_id = x_originating_tsk_id
AND NVL(status_code,'X') IN ( 'PARTIALLY RELEASED', 'RELEASED')
AND task_type_code = 'SUMMARY';
SELECT visit_task_id
FROM ahl_visit_tasks_b
WHERE originating_task_id = c_task_id
AND status_code = 'PLANNING';
SELECT wo.workorder_id, wo.status_code, wip.scheduled_start_date,wip.scheduled_completion_date
FROM ahl_visits_b v, ahl_workorders wo, wip_discrete_jobs wip
WHERE v.visit_id = x_visit_id
AND NVL(v.status_code,'X') = 'PARTIALLY RELEASED'
AND v.visit_id = wo.visit_id
AND wo.visit_task_id IS NULL
AND wo.master_workorder_flag = 'Y'
AND wip.wip_entity_id = wo.wip_entity_id
AND wo.STATUS_CODE not in ('22','7');
SELECT P.visit_task_number , P.visit_task_id
FROM ahl_visit_tasks_b P,
ahl_task_links L
WHERE P.visit_task_id = L.parent_task_id
AND L.visit_task_id = x_vst_task_id;
SELECT C.visit_task_number ,C.visit_task_id
FROM ahl_visit_tasks_b C,
ahl_task_links L
WHERE C.visit_task_id = L.visit_task_id
AND L.parent_task_id = x_vst_task_id;
select mr_id,visit_id,visit_task_id,status_code,task_type_code,nvl(originating_task_id,0) as originating_task_id,
summary_task_flag, inventory_item_id,item_organization_id,visit_task_number,end_date_time
FROM ahl_visit_tasks_b
-- SKPATHAK :: 02-MAY-2011 :: VWPE: ER:12424063 :: Summary tasks may be in partially implemented status
WHERE NVL(status_code,'X') in ('PLANNING','PARTIALLY RELEASED')
START WITH visit_task_id = x_vst_task_id
CONNECT BY originating_task_id = PRIOR visit_task_id
order by visit_id,visit_task_id,mr_id;
SELECT TASKS.STAGE_ID,
STAGES.STAGE_NAME,
STAGE_TYPES.STAGE_TYPE_CODE
FROM AHL_VISIT_TASKS_B TASKS,
AHL_VISIT_STAGE_TYP_ASOC STAGE_TYPES,
AHL_VWP_STAGES_VL STAGES
WHERE
TASKS.VISIT_TASK_ID= c_vst_task_id AND
TASKS.STAGE_ID=STAGE_TYPES.STAGE_ID(+) AND
TASKS.STAGE_ID = STAGES.STAGE_ID;
/*when the summary task is selected then all child tasks/child mr's for the summary tasks/MR will be pushed to prodn.
Fetches only summary tasks of MR's and not the manually added summary tasks.*/
IF (c_tsk_dtls_rec.task_type_code IN ('SUMMARY') AND c_tsk_dtls_rec.summary_task_flag = 'N') THEN
IF (x_tasks_tbl.COUNT > 0) THEN
FOR m IN x_tasks_tbl.FIRST..x_tasks_tbl.LAST
LOOP
IF x_tasks_tbl(m).visit_task_id = c_tsk_dtls_rec.visit_task_id THEN
parent_task_found_flag := TRUE;
/*for each planned/unplanned/summary task ensure that the parent MR/task is selected.If not then throw
an error message to the user asking him to select the parent MR*/
IF (c_tsk_dtls_rec.task_type_code IN ('PLANNED','UNPLANNED', 'SUMMARY')) AND (c_tsk_dtls_rec.originating_task_id <> 0) THEN
--loop back to the parent MR to ensure that the child tasks/MR is also selected
lp_originating_task_id := c_tsk_dtls_rec.originating_task_id;
IF NOT(parent_task_found_flag) THEN --if summary task is not selected then throw an error
x_msg_count := FND_MSG_PUB.count_msg;
'Select the summary task for the task: ' ||
c_tsk_dtls_rec.visit_task_number);
UPDATE ahl_visit_tasks_b
SET status_code = 'RELEASED',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE visit_task_id = lp_originating_task_id;
END LOOP; --for all selected tasks
ELSE --when parent dependency is found chk if the associated MR/unassociated tasks are also selected.
FOR j IN x_tasks_tbl.FIRST..x_tasks_tbl.LAST
LOOP
IF (x_tasks_tbl(j).visit_task_id = c_par_tech_dep_rec.visit_task_id) THEN
parent_task := TRUE;
IF NOT(parent_task) THEN --parent task not selected
x_msg_count := FND_MSG_PUB.count_msg;
' is technically dependent has not been selected.'||
'Please select the technically dependent tasks too');
END IF; --parent task not selected
ELSE --when child dependency is found chk if the associated MR/unassociated tasks are also selected.
FOR j IN x_tasks_tbl.FIRST..x_tasks_tbl.LAST
LOOP
IF (x_tasks_tbl(j).visit_task_id = c_ch_tech_dep_rec.visit_task_id) THEN
child_task := TRUE;
'Child tasks on which the task ' || x_tasks_tbl(i).visit_task_number || ' is technically dependent has not been selected.'||
'Please select the technically dependent tasks too');
END IF; --curr task not selected
SELECT 1 FROM ahl_workorders wo
WHERE wo.visit_id = x_visit_id
AND wo.visit_task_id IS NULL
AND wo.master_workorder_flag = 'Y';
SELECT wo.workorder_id, wo.status_code, wip.scheduled_start_date,wip.scheduled_completion_date,wo.object_version_number
FROM ahl_visits_b v, ahl_workorders wo, wip_discrete_jobs wip
WHERE v.visit_id = x_visit_id
AND v.visit_id = wo.visit_id
AND wo.visit_task_id IS NULL
AND wo.master_workorder_flag = 'Y'
AND wip.wip_entity_id = wo.wip_entity_id
AND wo.status_code not in ('22','7');
SELECT ahl_visits_vl.*,AHL_VWP_VISITS_PVT.Is_Old_Visit(VISIT_ID) IS_OLD_VISIT FROM ahl_visits_vl
WHERE visit_id = x_visit_id;
SELECT * FROM ahl_visit_tasks_vl
WHERE visit_task_id = x_visit_task_id;
SELECT 1 FROM ahl_workorders wo
WHERE wo.visit_id = x_visit_id
AND wo.visit_task_id = x_visit_task_id
AND wo.visit_task_id IS NOT NULL;
SELECT v.visit_task_id, wo.workorder_id, wo.status_code, wip.scheduled_start_date,wip.scheduled_completion_date,wo.object_version_number
FROM ahl_visit_tasks_b v, ahl_workorders wo, wip_discrete_jobs wip
WHERE v.visit_id = x_visit_id
AND v.visit_id = wo.visit_id
AND wo.visit_task_id IS NOT NULL
AND wo.visit_task_id = x_visit_task_id
AND wip.wip_entity_id = wo.wip_entity_id
AND wo.status_code not in ('22','7');
SELECT summary_task_flag FROM ahl_visit_tasks_b
WHERE visit_task_id = x_visit_task_id;
SELECT count(visit_task_id) FROM ahl_visit_tasks_b
WHERE visit_id = x_visit_id
AND NVL(status_code,'X') IN ('PLANNING');
SELECT min(start_date_time), max(end_date_time)
FROM ahl_visit_tasks_b VST
START WITH visit_task_id = x_task_id
AND NVL(VST.status_code, 'Y') <> 'DELETED'
CONNECT BY originating_task_id = PRIOR visit_task_id;
SELECT Route_Id FROM AHL_MR_ROUTES_V
WHERE MR_ROUTE_ID = x_id;
SELECT actual_start_date,actual_end_date
FROM ahl_workorders wo
WHERE wo.visit_id = x_visit_id
AND wo.visit_task_id IS NULL
AND wo.master_workorder_flag = 'Y';
SELECT inventory_item_id,instance_id
FROM ahl_visit_tasks_b
WHERE visit_task_id = c_task_id;
SELECT ar.route_no||'.'||substrb(ar.title,1,(240 - (lengthb(ar.route_no) + 1))) workorder_description
FROM ahl_routes_vl ar,ahl_visit_tasks_b avt, ahl_mr_routes mrr
WHERE avt.visit_task_id = p_task_id
and nvl(avt.status_code,'Y') = 'PLANNING'
and avt.mr_route_id = mrr.mr_route_id
and mrr.route_id = ar.route_id;
SELECT WDJ.WIP_ENTITY_ID,
AWO.WORKORDER_ID,
AWO.OBJECT_VERSION_NUMBER,
WDJ.SCHEDULED_START_DATE,
WDJ.SCHEDULED_COMPLETION_DATE
FROM AHL_WORKORDERS AWO,
WIP_DISCRETE_JOBS WDJ
WHERE WDJ.WIP_ENTITY_ID = AWO.WIP_ENTITY_ID
AND AWO.VISIT_TASK_ID = p_sr_task_id
AND AWO.MASTER_WORKORDER_FLAG = 'Y'
AND AWO.STATUS_CODE <> 17;
SELECT * FROM AHL_VISIT_TASKS_B vst
WHERE vst.TASK_TYPE_CODE = 'SUMMARY'
AND vst.MR_ID IS NULL
AND vst.SERVICE_REQUEST_ID =
(SELECT vst1.SERVICE_REQUEST_ID
FROM ahl_visit_tasks_b vst1
WHERE vst1.visit_task_id = p_sr_task_id);
SELECT 1 FROM ahl_visit_tasks_b
WHERE visit_id = p_visit_id
AND status_code = 'PLANNING'
-- SATRAJEN :: Bug 13930098 :: Visit stays at PARTIALLY IMPLEMENTED status :: Do NOT consider the default stage task :: July 2012
AND (TASK_TYPE_CODE <> 'STAGE'
OR (STAGE_ID IS NOT NULL AND TASK_TYPE_CODE = 'STAGE'));
SELECT 1
FROM ahl_visits_b vst, ahl_workorders wo, wip_discrete_jobs wdj
WHERE vst.visit_id = p_visit_id
AND wo.visit_id = vst.visit_id
AND wo.MASTER_WORKORDER_FLAG = 'Y'
AND wo.visit_task_id IS NULL
AND wdj.wip_entity_id = wo.wip_entity_id
AND vst.start_date_time = wdj.scheduled_start_date
AND vst.close_date_time = wdj.scheduled_completion_date;
SELECT repair_batch_name
FROM AHL_VISIT_TASKS_B
WHERE cost_parent_id IS NULL
START WITH visit_task_id = c_visit_task_id
CONNECT BY visit_task_id = Prior cost_parent_id;
SELECT 'X'
FROM AHL_VISIT_TASKS_B
WHERE cost_parent_id IS NOT NULL -- to filter repair batch task
AND status_code = 'PLANNING'
START WITH repair_batch_name = c_repair_batch_name
CONNECT BY cost_parent_id = Prior visit_task_id;
SELECT stage.stage_id
FROM ahl_vwp_stages_b stage
WHERE stage.visit_id = c_visit_id
AND EXISTS (SELECT 1
FROM ahl_visit_tasks_b task
WHERE stage.stage_id = task.stage_id
AND stage.stage_status_code = 'PLANNING'
AND task.task_type_code <> 'STAGE'
AND task.status_code = 'RELEASED');
SELECT task.task_type_code, wo.workorder_id
FROM ahl_visit_tasks_b task, ahl_workorders wo
WHERE task.visit_task_id = c_task_id
AND task.visit_task_id = wo.visit_task_id;
and the instance_id from the list of user selected tasks.This inventory_item_id/instance_id
will be used in the creation of master wo for the visit.*/
OPEN default_task_inst_dtls(p_tasks_tbl(p_tasks_tbl.FIRST).visit_task_id);
UPDATE ahl_visit_tasks_b
SET status_code = 'RELEASED',
--TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE OVN AND WHO COLUMNS
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE visit_task_id = p_tasks_tbl(i).visit_task_id;
/*The project start/end dates have to be updated with the workorder scheduled
start/end dates.*/
IF (l_log_statement >= l_log_current_level) THEN
fnd_log.string(l_log_statement,
L_DEBUG_KEY,
'Before calling Update_Project_Task_Times.');
Update_Project_Task_Times(
p_prd_workorder_tbl => l_prd_workorder_tbl,
p_commit =>'F',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
'Errors from Update_Project_Task_Times. Message count: ' || x_msg_count);
'Returned Success from Update_Project_Task_Times');
UPDATE ahl_visit_tasks_b
SET status_code = 'CANCELLED',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE visit_task_id = p_tasks_tbl(i).visit_task_id;
UPDATE ahl_visit_tasks_b
SET status_code = 'RELEASED',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE visit_task_id = p_tasks_tbl(i).visit_task_id;
UPDATE ahl_vwp_stages_b
SET stage_status_code = 'PARTIALLY RELEASED',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE stage_id = l_stage_rec.stage_id;
UPDATE AHL_VISIT_TASKS_B
SET status_code = 'PARTIALLY RELEASED',
object_version_number = object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = Fnd_Global.USER_ID,
last_update_login = Fnd_Global.LOGIN_ID
WHERE repair_batch_name = l_repair_batch_name;
UPDATE AHL_VISIT_TASKS_B
SET status_code = 'RELEASED',
object_version_number = object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = Fnd_Global.USER_ID,
last_update_login = Fnd_Global.LOGIN_ID
WHERE repair_batch_name = l_repair_batch_name;
/*Check if the user had selected all the tasks otherwise
update the visit as Partially released.*/
OPEN c_visit_has_planned_tasks(p_visit_id);
UPDATE ahl_visits_b
SET status_code = 'PARTIALLY RELEASED',
--TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE OVN AND WHO COLUMNS
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE visit_id = p_visit_id;
UPDATE ahl_visits_b
SET status_code = 'RELEASED',
any_task_chg_flag ='N',
--TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE OVN AND WHO COLUMNS
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE visit_id = p_visit_id;
SELECT visit_task_number,instance_id
FROM ahl_visit_tasks_vl
WHERE visit_id = p_visit_id
AND NVL(STATUS_CODE,'X') NOT IN ('DELETED','RELEASED','CLOSED')
AND TASK_TYPE_CODE <> 'SUMMARY';
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
SELECT workorder_id
FROM AHL_WORKORDERS
WHERE VISIT_TASK_ID = (select visit_task_id from ahl_visit_tasks_b
where visit_id = c_visit_id
and unit_effectivity_id = c_unit_effectivity_id
AND NVL(status_code, 'Y') <> 'DELETED'
and originating_task_id is null)
AND STATUS_CODE NOT IN ('7', '22');
SELECT
scheduled_start_date,
SCHEDULED_COMPLETION_DATE
FROM wip_discrete_jobs
WHERE wip_entity_id =
(
SELECT
wip_entity_id
FROM ahl_workorders
WHERE
master_workorder_flag = 'Y' AND
visit_task_id IS null AND
status_code not in (22,7) and
visit_id=x_visit_id
);
SELECT max(end_date_time)
FROM ahl_visit_tasks_b VST
where visit_id = x_visit_id
AND NVL(VST.status_code, 'Y') <> 'DELETED'
START WITH unit_effectivity_id = x_unit_effectivity_id
and originating_task_id is null
CONNECT BY originating_task_id = PRIOR visit_task_id;
select distinct t.visit_task_id
from ahl_visit_tasks_b t,
ahl_visits_b v
where v.visit_id = x_visit_id
and v.visit_id = t.visit_id
--and v.status_code = 'PARTIALLY RELEASED'
and v.status_code IN ('PLANNING', 'PARTIALLY RELEASED') -- PRAKKUM :: BUG Internal :: 19/01/2012 :: Task Time Adjustments
and t.start_date_time < SYSDATE
and t.status_code ='PLANNING'
and t.task_type_code <>'SUMMARY'
-- SKPATHAK :: ER: 9147951 :: 11-JAN-2010 :: Adjust task times only if past task start date is null
and t.past_task_start_date IS NULL
and (not exists (select 1 from ahl_task_links l0
where l0.parent_task_id = t.visit_task_id
or l0.visit_task_id = t.visit_task_id )
or t.visit_task_id in (select l1.parent_task_id from ahl_task_links l1
where not exists (select l2.visit_task_id from ahl_task_links l2
where l2.visit_task_id = l1.parent_task_id)))
/*NR-MR Changes - For SR's created from non-routines have the originating wo as their originating task, hence they are not null.*/
START WITH t.unit_effectivity_id = x_unit_effectivity_id
AND (( t.originating_task_id is null AND 'SR' <> p_module_type)
-- SKPATHAK :: Bug #9410052 :: 25-FEB-2010 :: Removed the condition "originating_task_id is not null"
OR ( 'SR' = p_module_type
and t.service_request_id is not null))
CONNECT BY t.originating_task_id = PRIOR visit_task_id;
SELECT DISTINCT STAGES.STAGE_ID ,
STAGES.STAGE_NAME
FROM AHL_VISIT_TASKS_B TASKS,
AHL_VWP_STAGES_VL STAGES
WHERE
TASKS.VISIT_ID= c_visit_id AND
TASKS.UNIT_EFFECTIVITY_ID = c_unit_effectivity_id AND
STAGES.STAGE_ID=TASKS.STAGE_ID AND
NOT EXISTS (
SELECT STAGE_ID
FROM AHL_VISIT_STAGE_TYP_ASOC
WHERE STAGE_ID=STAGES.STAGE_ID);
SELECT distinct PARENT.visit_task_number parent_task_number,
CHILD.visit_task_number child_task_number
FROM AHL_VISIT_TASKS_B PARENT,
AHL_VISIT_TASKS_B CHILD,
AHL_TASK_LINKS LINK
WHERE PARENT.visit_task_id = LINK.parent_task_id
AND CHILD.visit_task_id = LINK.visit_task_id
AND NVL(PARENT.STATUS_CODE,'X') = 'PLANNING' --Srini Bug #4075702
AND PARENT.visit_id = c_visit_id
AND CHILD.visit_id = c_visit_id
AND PARENT.visit_task_id in (select visit_task_id from ahl_visit_tasks_b
where visit_id = c_visit_id
/*NR-MR Changes - For SR's created from non-routines have the originating wo as their originating task, hence they are not null.*/
START WITH unit_effectivity_id = c_unit_effectivity_id
AND (( originating_task_id is null AND 'SR' <> p_module_type)
-- SKPATHAK :: Bug #9410052 :: 25-FEB-2010
-- Removed the condition "originating_task_id is not null"
OR ( 'SR' = p_module_type
and service_request_id is not null))
CONNECT BY originating_task_id = PRIOR visit_task_id)
AND CHILD.visit_task_id in ( select visit_task_id from ahl_visit_tasks_b
where visit_id = c_visit_id
/*NR-MR Changes - For SR's created from non-routines have the originating wo as their originating task, hence they are not null.*/
START WITH unit_effectivity_id = c_unit_effectivity_id
AND (( originating_task_id is null AND 'SR' <> p_module_type)
-- SKPATHAK :: Bug #9410052 :: 25-FEB-2010
-- Removed the condition "originating_task_id is not null"
OR ( 'SR' = p_module_type
and service_request_id is not null))
CONNECT BY originating_task_id = PRIOR visit_task_id);
SELECT distinct visit_task_id,
visit_task_number,
task_type_code,
service_request_id,
mr_id,
NVL(originating_task_id, -1)
FROM AHL_VISIT_TASKS_B
WHERE visit_id = c_visit_id
AND task_type_code='SUMMARY'
-- SKPATHAK :: Bug 9444849 :: 19-MAR-2010
-- This condition is not needed, since after opening this cursor we have a check if the visit task id fetched by this cursor
-- already has a corresponding WO. So only for tasks in planning we are building the relationships
-- Also it is necessary to remove this condition to fix the bug 9444849, since the parent task can be implemented as well
-- AND NVL(STATUS_CODE, 'X') = 'PLANNING'
/*NR-MR Changes - For SR's created from non-routines have the originating wo as their originating task, hence they are not null.*/
START WITH unit_effectivity_id = c_unit_effectivity_id
AND (( originating_task_id is null AND 'SR' <> p_module_type)
-- SKPATHAK :: Bug #9410052 :: 25-FEB-2010 :: Removed the condition "originating_task_id is not null"
OR ( 'SR' = p_module_type
and service_request_id is not null))
CONNECT BY originating_task_id = PRIOR visit_task_id
order by 2;
SELECT visit_task_number, visit_task_id
FROM AHL_VISIT_TASKS_B
WHERE visit_id = c_visit_id
AND originating_task_id = c_mr_task_id
AND task_type_code <> 'SUMMARY'
AND NVL(STATUS_CODE, 'X') = 'PLANNING';
SELECT wip_entity_id
FROM AHL_WORKORDERS
WHERE VISIT_TASK_ID = c_visit_task_id
-- SKPATHAK :: Bug 9940275 :: 18-AUG-2010 :: Removed status '7' from below condtn
-- This cursor is used to check if a visit task has a corresponding WO and if not, WO relationship needs to be created
-- So even if the status is 7 (cancelled), cursor must fetch the result, since relationship need not be created in this case
AND STATUS_CODE <> '22';
SELECT wip_entity_id
FROM AHL_WORKORDERS
WHERE VISIT_TASK_ID = c_visit_task_id
AND STATUS_CODE NOT IN ('7', '22');
SELECT wip_entity_id
FROM AHL_WORKORDERS
WHERE visit_id = c_visit_id
AND VISIT_TASK_ID IS NULL
AND MASTER_WORKORDER_FLAG = 'Y'
AND STATUS_CODE NOT IN ('7', '22');
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_visit_id;
SELECT distinct visit_task_id, originating_task_id, visit_id, unit_effectivity_id,
status_code, service_request_id, project_task_id, visit_task_number,
visit_task_name, description, object_version_number, task_type_code,
inventory_item_id, instance_id, mr_route_id, department_id, start_date_time,
end_date_time, past_task_start_date -- SKPATHAK :: ER: 9147951 :: 11-JAN-2010 :: Fetch past date too
FROM AHL_VISIT_TASKS_VL
WHERE VISIT_ID = x_visit_id
AND NVL( STATUS_CODE, 'X' ) = 'PLANNING'
/*NR-MR Changes*/
START WITH unit_effectivity_id = x_unit_effectivity_id
AND (( originating_task_id is null AND 'SR' <> p_module_type)
-- SKPATHAK :: Bug #9410052 :: 25-FEB-2010 :: Removed the condition "originating_task_id is not null"
OR ( 'SR' = p_module_type
and service_request_id is not null))
CONNECT BY originating_task_id = PRIOR visit_task_id;
SELECT count(distinct visit_task_id) FROM AHL_VISIT_TASKS_B
WHERE VISIT_ID = x_visit_id
AND NVL( STATUS_CODE, 'X' ) = 'PLANNING'
/*NR-MR Changes*/
START WITH unit_effectivity_id = x_unit_effectivity_id
AND (( originating_task_id is null AND 'SR' <> p_module_type)
-- SKPATHAK :: Bug #9410052 :: 25-FEB-2010 :: Removed the condition "originating_task_id is not null"
OR ( 'SR' = p_module_type
and service_request_id is not null))
CONNECT BY originating_task_id = PRIOR visit_task_id;
SELECT Route_Id FROM AHL_MR_ROUTES_V
WHERE MR_ROUTE_ID = x_id;
SELECT min(start_date_time), max(end_date_time)
--TCHIMIRA::19-FEB-2010::BUG 9384614
-- Use the base table instead of the vl view
FROM ahl_visit_tasks_b VST
START WITH visit_task_id = x_task_id
AND NVL(VST.status_code, 'Y') <> 'DELETED'
CONNECT BY originating_task_id = PRIOR visit_task_id;
SELECT 'x'
FROM ahl_visit_tasks_b
WHERE visit_id = x_visit_id
AND STATUS_CODE = 'PLANNING'
-- SKPATHAK :: Bug 13930098 :: 20-APR-2012 :: Do NOT consider the default stage task
AND (TASK_TYPE_CODE <> 'STAGE'
OR (STAGE_ID IS NOT NULL AND TASK_TYPE_CODE = 'STAGE'));
SELECT
scheduled_start_date,
SCHEDULED_COMPLETION_DATE
FROM wip_discrete_jobs
WHERE wip_entity_id =
(
SELECT
wip_entity_id
FROM ahl_workorders
WHERE
master_workorder_flag = 'Y' AND
visit_task_id IS null AND
status_code not in (22,7) and
visit_id=x_visit_id
);
SELECT ar.route_no||'.'||SUBSTRB(ar.title, 1, (240 - (LENGTHB(ar.route_no) + 1))) workorder_description
FROM ahl_routes_vl ar, ahl_visit_tasks_b avt,
ahl_mr_routes mrr
WHERE avt.visit_task_id = c_visit_task_id
AND NVL(avt.status_code, 'X') = 'PLANNING'
AND avt.mr_route_id = mrr.mr_route_id
AND mrr.route_id = ar.route_id;
SELECT wo.workorder_id, wo.object_version_number, wo.status_code,
WIP.SCHEDULED_START_DATE,
WIP.SCHEDULED_COMPLETION_DATE
FROM AHL_WORKORDERS WO,
WIP_DISCRETE_JOBS WIP
WHERE wo.VISIT_ID = c_visit_id
AND wo.VISIT_TASK_ID IS NULL
AND wo.MASTER_WORKORDER_FLAG = 'Y'
AND WIP.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND wo.STATUS_CODE not in ('22','7'); --(22-Deleted, 7-Cancelled)
SELECT repair_batch_name
FROM AHL_VISIT_TASKS_B
WHERE cost_parent_id IS NULL
START WITH visit_task_id = c_visit_task_id
CONNECT BY visit_task_id = Prior cost_parent_id;
SELECT 'X'
FROM AHL_VISIT_TASKS_B
WHERE cost_parent_id IS NOT NULL -- to filter repair batch task
AND status_code = 'PLANNING'
START WITH repair_batch_name = c_repair_batch_name
CONNECT BY cost_parent_id = Prior visit_task_id;
fnd_log.string(l_log_statement, l_debug_key, 'Before calling Update_Project_Task_Times.');
Update_Project_Task_Times(
p_prd_workorder_tbl => l_prd_workorder_tbl,
p_commit => 'F',
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
fnd_log.string(l_log_statement, l_debug_key, 'After calling Update_Project_Task_Times. x_return_status => '||x_return_status);
UPDATE AHL_VISIT_TASKS_B
SET STATUS_CODE = 'RELEASED',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
--TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE VISIT_ID = p_visit_id
AND VISIT_TASK_ID = c_task_rec.visit_task_id
AND STATUS_CODE = 'PLANNING';
UPDATE ahl_visits_b
SET status_code = 'RELEASED',
object_version_number = object_version_number + 1,
--TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE visit_id = c_visit_rec.visit_id;
fnd_log.string(l_log_statement,L_DEBUG_KEY,'Before AHL_VWP_RULES_PVT.Update_Visit_Task_Flag Call');
AHL_VWP_RULES_PVT.Update_Visit_Task_Flag
(p_visit_id => c_visit_rec.visit_id,
p_flag => 'N',
x_return_status => x_return_status);
fnd_log.string(l_log_statement,L_DEBUG_KEY,'After AHL_VWP_RULES_PVT.Update_Visit_Task_Flag Call');
UPDATE AHL_VISIT_TASKS_B
SET status_code = 'RELEASED',
object_version_number = object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = Fnd_Global.USER_ID,
last_update_login = Fnd_Global.LOGIN_ID
WHERE repair_batch_name = l_repair_batch_name;
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
SELECT distinct visit_task_id, originating_task_id, visit_id, unit_effectivity_id,
status_code, service_request_id, project_task_id, visit_task_number,
visit_task_name, description, object_version_number, start_date_time,
end_date_time, task_type_code, cost_parent_id, service_type_code --PRAKKUM :: PIE :: 13-OCT-2010 :: Added three more columns
FROM AHL_VISIT_TASKS_VL
WHERE VISIT_ID = x_visit_id
AND NVL( STATUS_CODE, 'X' ) = 'PLANNING'
START WITH unit_effectivity_id = x_unit_effectivity_id
AND (( originating_task_id is null AND 'SR' <> p_module_type)
-- SKPATHAK :: Bug #9410052 :: 25-FEB-2010 :: Removed the condition "originating_task_id is not null"
OR ( 'SR' = p_module_type
and service_request_id is not null))
CONNECT BY originating_task_id = PRIOR visit_task_id
ORDER by visit_task_number; --PRAKKUM :: PIE :: 13-OCT-2010 :: Added ORDER BY clause
SELECT visit_task_number, task_type_code from ahl_visit_tasks_b
where visit_task_id = c_visit_task_id;
SELECT pm_task_reference from pa_tasks
where task_id = c_top_project_task_id;
SELECT SUBSTR(NVL(ar.route_no, avt.visit_task_name), 1, 20) task_name,
SUBSTR(NVL(ar.title, avt.visit_task_name), 1, 250) description
FROM ahl_routes_vl ar, ahl_visit_tasks_vl avt,
ahl_mr_routes mrr
WHERE avt.visit_task_id = c_visit_task_id
AND NVL(avt.status_code, 'X') = 'PLANNING'
AND avt.task_type_code NOT IN ('SUMMARY', 'UNASSOCIATED')
AND avt.mr_route_id = mrr.mr_route_id (+)
AND mrr.route_id = ar.route_id (+)
UNION ALL
SELECT SUBSTR(NVL(amh.title, avt.visit_task_name), 1, 20) task_name,
NVL(amh.title, avt.visit_task_name) description
FROM ahl_mr_headers_v amh, ahl_visit_tasks_vl avt
WHERE avt.visit_task_id = c_visit_task_id
AND NVL(avt.status_code, 'X') = 'PLANNING'
AND avt.task_type_code = 'SUMMARY'
AND avt.summary_task_flag = 'N'
AND avt.mr_id = amh.mr_header_id (+);
UPDATE AHL_VISIT_TASKS_B SET PROJECT_TASK_ID = l_task_id,
OBJECT_VERSION_NUMBER = c_task_rec.object_version_number + 1,
--TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE VISIT_TASK_ID = c_task_rec.visit_task_id;
fnd_log.string(l_log_statement,L_DEBUG_KEY,'Updated AHL_VISIT_TASKS_B: c_task_rec.visit_task_number = ' || c_task_rec.visit_task_number ||
', c_task_rec.visit_task_id = ' || c_task_rec.visit_task_id ||
', c_task_rec.originating_task_id = ' || c_task_rec.originating_task_id ||
', c_task_rec.unit_effectivity_id = ' || c_task_rec.unit_effectivity_id ||
', c_task_rec.service_request_id = ' || c_task_rec.service_request_id);
UPDATE AHL_VISIT_TASKS_B SET PROJECT_TASK_ID = l_task_id,
OBJECT_VERSION_NUMBER = c_task_rec.object_version_number + 1
WHERE VISIT_TASK_ID = c_task_rec.visit_task_id;
fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY, 'Updated AHL_VISIT_TASKS_B: c_task_rec.visit_task_number = ' || c_task_rec.visit_task_number ||
', c_task_rec.visit_task_id = ' || c_task_rec.visit_task_id ||
', c_task_rec.originating_task_id = ' || c_task_rec.originating_task_id ||
', c_task_rec.unit_effectivity_id = ' || c_task_rec.unit_effectivity_id ||
', c_task_rec.service_request_id = ' || c_task_rec.service_request_id);
SELECT visit_task_id
FROM ahl_visit_tasks_b
WHERE visit_id = c_visit_id
AND NVL(status_code,'X') = 'PLANNING';
SELECT UNIQUE asmt1.scheduled_material_id,
asmt1.visit_id,
asmt1.visit_task_id,
asmt1.inventory_item_id,
asmt1.requested_quantity,
asmt1.scheduled_quantity,
asmt1.rt_oper_material_id,
asmt1.item_group_id
FROM ahl_schedule_materials asmt1,
ahl_Schedule_materials asmt2
WHERE asmt1.visit_id = asmt2.visit_id
AND asmt1.visit_task_id = asmt2.visit_task_id
AND asmt1.inventory_item_id = asmt2.inventory_item_id
AND NVL(asmt1.operation_code,'X') = NVL(asmt2.operation_code,'X')
AND asmt1.scheduled_material_id <> asmt2.scheduled_material_id
AND NVL(asmt1.status,'X') = 'ACTIVE'
AND NVL(asmt2.status,'X') = 'ACTIVE'
AND asmt1.visit_task_id = c_visit_task_id
AND asmt1.mc_header_id IS NULL
AND asmt2.mc_header_id IS NULL
ORDER BY asmt1.inventory_item_id;
SELECT reservation_id
FROM mtl_reservations
WHERE demand_source_line_detail = c_scheduled_material_id
AND external_source_code = 'AHL';
UPDATE ahl_schedule_materials
SET scheduled_quantity = NVL(scheduled_quantity,0) + NVL(l_material_reqrs_rec.scheduled_quantity,0),
item_group_id = NVL(item_group_id,l_material_reqrs_rec.item_group_id),
requested_quantity = NVL(requested_quantity,0) + NVL(l_material_reqrs_rec.requested_quantity,0)
WHERE scheduled_material_id = l_scheduled_matrial_id;
UPDATE ahl_Schedule_materials
SET status = 'DELETED',requested_quantity = 0
WHERE scheduled_material_id = l_material_reqrs_rec.scheduled_material_id;
PROCEDURE Update_Project_Task_Times
( p_prd_workorder_tbl IN AHL_PRD_WORKORDER_PVT.PRD_WORKORDER_TBL,
p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
AS
l_task_in_tbl PA_PROJECT_PUB.TASK_IN_TBL_TYPE;
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Project_Task_Times';
SELECT CURR.TASK_ID, CURR.DESCRIPTION, CURR.PM_TASK_REFERENCE, PARENT.PM_TASK_REFERENCE PARENT_TASK_REFERENCE, VTSK.VISIT_TASK_ID
, CURR.PARENT_TASK_ID PARENT_TASK_ID -- PRAKKUM :: 08-JAN-2013 :: FPBug 16481709 for BaseBug 14828418 : In reference of bug 9047048
FROM PA_TASKS CURR, PA_PROJ_ELEMENT_VERSIONS PPEV, PA_TASKS PARENT, AHL_VISIT_TASKS_B VTSK
WHERE CURR.PROJECT_ID = c_project_id
AND PPEV.PROJ_ELEMENT_ID = CURR.TASK_ID
AND PPEV.PROJECT_ID = c_project_id
AND PARENT.TASK_ID (+) = CURR.PARENT_TASK_ID
AND VTSK.PROJECT_TASK_ID (+) = CURR.TASK_ID
AND VTSK.VISIT_ID(+) = c_visit_id
ORDER BY PPEV.DISPLAY_SEQUENCE;
SELECT WDJ.SCHEDULED_START_DATE, WDJ.SCHEDULED_COMPLETION_DATE
FROM WIP_DISCRETE_JOBS WDJ, AHL_WORKORDERS WO
WHERE WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID AND
WO.WORKORDER_ID = p_wo_id;
SELECT AVT.PROJECT_TASK_ID, AVT.START_DATE_TIME, AVT.END_DATE_TIME,
PAT.DESCRIPTION -- Pass the old description back again
FROM AHL_VISIT_TASKS_B AVT, AHL_WORKORDERS WO,
PA_TASKS PAT
WHERE WO.VISIT_TASK_ID = AVT.VISIT_TASK_ID AND
WO.WORKORDER_ID = p_wo_id AND
PAT.TASK_ID (+) = AVT.PROJECT_TASK_ID;
SELECT av.visit_id, av.project_id, av.start_date_time, av.close_date_time, av.top_project_task_id
FROM ahl_workorders wo, ahl_visits_b av
WHERE WO.WORKORDER_ID = p_wo_id
AND wo.visit_id = av.visit_id;
SELECT start_date
from pa_projects_all
where project_id = c_project_id;
SAVEPOINT Update_Project_Task_Times;
'Before calling PA_PROJECT_PUB.update_project');
PA_PROJECT_PUB.UPDATE_PROJECT(
p_api_version_number => 1,
p_commit => p_commit,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_workflow_started => l_workflow_started,
p_pm_product_code => G_PM_PRODUCT_CODE,
p_project_in => l_project_rec,
p_project_out => l_project_out,
p_key_members => l_key_members,
p_class_categories => l_class_categories,
p_tasks_in => l_task_in_tbl,
-- Added by jaramana on 23-OCT-2009 for the WBS ER 8674208
p_pass_entire_structure => l_pass_entire_structure,
p_tasks_out => l_task_out);
'After calling PA_PROJECT_PUB.update_project. Return Status = ' || l_return_status);
'Errors from PA_PROJECT_PUB.update_project. Message count: ' ||
l_msg_count || ', message data: ' || l_msg_data);
ROLLBACK TO Update_Project_Task_Times;
ROLLBACK TO Update_Project_Task_Times;
ROLLBACK TO Update_Project_Task_Times;
p_procedure_name => 'Update_Project',
p_error_text => SUBSTR(SQLERRM,1,500));
END Update_Project_Task_Times;
SELECT Route_Id FROM AHL_MR_ROUTES_V
WHERE MR_ROUTE_ID = x_id;
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
SELECT * FROM AHL_VISIT_TASKS_VL
WHERE VISIT_TASK_ID = x_id ;
SELECT count(*) FROM AHL_WORKORDERS
WHERE VISIT_TASK_ID = x_id
AND STATUS_CODE not in ('22','7');
SELECT * FROM AHL_WORKORDERS
WHERE VISIT_TASK_ID = x_id
AND STATUS_CODE not in ('22','7');
SELECT workorder_id, object_version_number
FROM AHL_WORKORDERS
WHERE VISIT_ID = x_id
AND VISIT_TASK_ID IS NULL
AND MASTER_WORKORDER_FLAG = 'Y'
AND STATUS_CODE not in ('22','7');
SELECT min(start_date_time), max(end_date_time)
--TCHIMIRA::19-FEB-2010::BUG 9384614
-- Use the base table instead of the vl view
FROM ahl_visit_tasks_b VST
START WITH visit_task_id = p_task_id
AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
CONNECT BY originating_task_id = PRIOR visit_task_id;
SELECT 'x'
FROM ahl_visit_tasks_b
WHERE visit_id = x_visit_id
AND STATUS_CODE = 'PLANNING';
SELECT scheduled_start_date,
SCHEDULED_COMPLETION_DATE
FROM wip_discrete_jobs
WHERE wip_entity_id =
(
SELECT
wip_entity_id
FROM ahl_workorders
WHERE
master_workorder_flag = 'Y' AND
visit_task_id IS null AND
status_code not in (22,7) and
visit_id=x_visit_id
);
UPDATE AHL_VISIT_TASKS_B
SET STATUS_CODE = 'RELEASED',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
WHERE VISIT_TASK_ID = p_x_task_Tbl(i).visit_task_id
AND STATUS_CODE = 'PLANNING';
fnd_log.string(l_log_statement,L_DEBUG_KEY,'Updated visit to Released..');
UPDATE ahl_visits_b
SET status_code = 'RELEASED',
object_version_number = object_version_number + 1
WHERE visit_id = c_visit_rec.visit_id;
AHL_VWP_RULES_PVT.Update_Visit_Task_Flag
(p_visit_id => c_visit_rec.visit_id,
p_flag => 'N',
x_return_status => x_return_status);
PROCEDURE Update_Project_Task(
p_task_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Project_Task';
l_update_task_structure VARCHAR2(1) := 'N';
SELECT 'X'
FROM ahl_visit_tasks_b
WHERE visit_task_id = c_task_id
AND status_code <> 'DELETED';
SELECT avt.project_task_id, avt.task_type_code,
avt.cost_parent_id, pt.project_id, pt.top_task_id
FROM ahl_visit_tasks_b avt, pa_tasks pt
WHERE avt.project_task_id = pt.task_id
AND avt.visit_task_id = c_task_id ;
SELECT project_task_id, task_type_code
FROM ahl_visit_tasks_b
WHERE visit_task_id = cost_parent_task_id ;
SELECT parent_task_id
FROM pa_tasks
WHERE task_id = c_project_task_id;
SAVEPOINT Update_Project_Task;
PA_PROJECT_PUB.UPDATE_TASK(
p_api_version_number => 1,
p_commit => 'F',
p_init_msg_list => 'F',
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_pa_project_id => l_project_id,
p_pa_task_id => l_project_task_id,
p_pm_product_code => l_pm_product_code,
p_pa_parent_task_id => l_project_parent_task_id,
p_out_pa_task_id => l_out_pa_task_id,
p_out_pm_task_reference => l_out_pm_task_reference,
p_update_task_structure => l_update_task_structure);
'Errors from PA_PROJECT_PUB.update_project. Message count: ' ||
l_msg_count || ', message data: ' || l_msg_data);
ROLLBACK TO Update_Project_Task;
ROLLBACK TO Update_Project_Task;
ROLLBACK TO Update_Project_Task;
p_procedure_name => 'Update_Project',
p_error_text => SUBSTR(SQLERRM,1,500));
END Update_Project_Task;
SELECT project_id, project_template_id
FROM ahl_visits_b
WHERE visit_id = c_visit_id ;
SELECT parent_task_id
FROM pa_tasks
WHERE project_id = x_project_id ;*/
SELECT count(PTSK.parent_task_id)
FROM pa_tasks PTSK, AHL_VISIT_TASKS_B VTSK
WHERE
VTSK.PROJECT_TASK_ID = PTSK.TASK_ID
AND VTSK.VISIT_ID = p_visit_id
AND PTSK.project_id = c_project_id ;
SELECT count(*)
FROM pa_tasks
WHERE project_id = x_project_id ;
SELECT count(visit_task_id)
FROM ahl_visit_tasks_b
WHERE task_type_code = 'STAGE'
AND status_code <> 'DELETED'
AND visit_id = c_visit_id;
PROCEDURE Get_Entl_rec_for_delete(
p_visit_id IN NUMBER,
x_warranty_entl_tbl OUT NOCOPY AHL_WARRANTY_ENTL_PVT.Warranty_Entl_Tbl_Type
) IS
-- Standard variables
L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Entl_rec_for_Delete';
SELECT AWSE.WARRANTY_ENTITLEMENT_ID, AWSE.VISIT_TASK_ID
FROM AHL_WARRANTY_ENTITLEMENTS AWSE
WHERE AWSE.SR_INCIDENT_ID = c_sr_incident_id;
SELECT DISTINCT service_request_id
FROM ahl_visit_tasks_b
WHERE visit_id = c_visit_id;
l_entitlement_rec.operation_flag := AHL_WARRANTY_ENTL_PVT.G_OP_DELETE;
END Get_Entl_rec_for_Delete;
SELECT status_code, visit_number
FROM ahl_visits_b
WHERE visit_id = x_visit_id;
UPDATE AHL_VISITS_B
SET STATUS_CODE = 'PLANNING',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE VISIT_ID = p_visit_id;
SELECT wo.workorder_id, wo.status_code, wip.scheduled_start_date,wip.scheduled_completion_date,wo.object_version_number
FROM ahl_visits_b v, ahl_workorders wo, wip_discrete_jobs wip
WHERE v.visit_id = c_visit_id
AND v.visit_id = wo.visit_id
AND wo.visit_task_id IS NULL
AND wo.master_workorder_flag = 'Y'
AND wip.wip_entity_id = wo.wip_entity_id
AND wo.status_code not in ('22','7');
SELECT * FROM ahl_visits_vl
WHERE visit_id = c_visit_id;
SELECT wo.visit_task_id, wo.workorder_id, wo.status_code, wip.scheduled_start_date,wip.scheduled_completion_date,wo.object_version_number
FROM ahl_workorders wo, wip_discrete_jobs wip
WHERE wo.visit_id = c_visit_id
AND wo.visit_task_id = c_visit_task_id
AND wip.wip_entity_id = wo.wip_entity_id
AND wo.status_code not in ('22','7');
SELECT summary_task_flag FROM ahl_visit_tasks_b
WHERE visit_task_id = c_visit_task_id;
SELECT min(start_date_time), max(end_date_time)
FROM ahl_visit_tasks_b VST
START WITH visit_task_id = c_task_id
AND NVL(VST.status_code, 'Y') <> 'DELETED'
CONNECT BY originating_task_id = PRIOR visit_task_id;
SELECT Route_Id FROM AHL_MR_ROUTES_V
WHERE MR_ROUTE_ID = c_mr_route_id;
SELECT inventory_item_id,instance_id
FROM ahl_visit_tasks_b
WHERE visit_task_id = c_task_id;
SELECT ar.route_no||'.'||substrb(ar.title,1,(240 - (lengthb(ar.route_no) + 1))) workorder_description
FROM ahl_routes_vl ar,ahl_visit_tasks_b avt, ahl_mr_routes mrr
WHERE avt.visit_task_id = p_task_id
and nvl(avt.status_code,'Y') = 'PLANNING'
and avt.mr_route_id = mrr.mr_route_id
and mrr.route_id = ar.route_id;
SELECT 'PARTIALLY RELEASED' FROM ahl_visit_tasks_b
WHERE task_type_code IN ('PLANNED','UNPLANNED')
AND status_code = 'PLANNING'
START WITH visit_task_id = c_task_id
CONNECT BY PRIOR visit_task_id = originating_task_id;
SELECT 1 FROM ahl_visit_tasks_b
WHERE visit_id = p_visit_id
AND status_code = 'PLANNING'
-- SATRAJEN :: Bug 13930098 :: Visit stays at PARTIALLY IMPLEMENTED status :: Do NOT consider the default stage task :: July 2012
AND (TASK_TYPE_CODE <> 'STAGE'
OR (STAGE_ID IS NOT NULL AND TASK_TYPE_CODE = 'STAGE'));
SELECT 1
FROM ahl_visits_b vst, ahl_workorders wo, wip_discrete_jobs wdj
WHERE vst.visit_id = p_visit_id
AND wo.visit_id = vst.visit_id
AND wo.MASTER_WORKORDER_FLAG = 'Y'
AND wo.visit_task_id IS NULL
AND wdj.wip_entity_id = wo.wip_entity_id
AND vst.start_date_time = wdj.scheduled_start_date
AND vst.close_date_time = wdj.scheduled_completion_date;
SELECT * FROM
--For non-summary tasks
(SELECT * FROM ahl_visit_tasks_vl
WHERE stage_id = c_stage_id
AND task_type_code <> 'STAGE'
UNION ALL
--For summary tasks
SELECT DISTINCT * FROM ahl_visit_tasks_vl
WHERE task_type_code = 'SUMMARY'
START WITH visit_task_id IN (SELECT originating_task_id
FROM ahl_visit_tasks_b
WHERE stage_id = c_stage_id )
CONNECT BY PRIOR originating_task_id = visit_task_id)
WHERE status_code NOT IN ('CANCELLED','DELETED')
ORDER BY visit_task_number;
SELECT * FROM ahl_visit_tasks_b
WHERE stage_id = c_stage_id
AND status_code NOT IN ('CANCELLED','DELETED')
ORDER BY visit_task_number;
SELECT DISTINCT * FROM ahl_visit_tasks_b
WHERE task_type_code = 'SUMMARY'
START WITH visit_task_id IN (SELECT originating_task_id
FROM ahl_visit_tasks_b
WHERE stage_id = c_stage_id )
CONNECT BY PRIOR originating_task_id = visit_task_id
ORDER BY visit_task_number;
SELECT wo.workorder_id
FROM ahl_workorders wo
WHERE wo.visit_task_id = c_task_id;
select AHL_VWP_VISITS_PVT.Is_Old_Visit(p_visit_id) IS_OLD_VISIT FROM DUAL;
UPDATE ahl_vwp_stages_b
SET STAGE_STATUS_CODE = 'RELEASED',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE STAGE_ID = p_stages_tbl(i).stage_id;
'Before calling Update_Project_Task_Times.');
/*The project start/end dates have to be updated with the workorder scheduled
start/end dates.*/
Update_Project_Task_Times(
p_prd_workorder_tbl => l_prd_workorder_tbl,
p_commit =>'F',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
'Errors from Update_Project_Task_Times. Message count: ' || x_msg_count);
'Returned Success from Update_Project_Task_Times');
UPDATE ahl_visit_tasks_b
SET status_code = 'CANCELLED',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE visit_task_id = l_non_summ_task_rec.visit_task_id;
UPDATE ahl_visit_tasks_b
SET status_code = 'RELEASED',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE visit_task_id = l_non_summ_task_rec.visit_task_id;
UPDATE ahl_visit_tasks_b
SET status_code = l_chk_status_code,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE visit_task_id = l_summary_task_rec.visit_task_id;
/*Check if the user had selected all the tasks otherwise
update the visit as Partially released.*/
OPEN c_visit_has_planned_tasks(p_visit_id);
UPDATE ahl_visits_b
SET status_code = 'PARTIALLY RELEASED',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE visit_id = p_visit_id;
UPDATE ahl_visits_b
SET status_code = 'RELEASED',
any_task_chg_flag ='N',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE visit_id = p_visit_id;
SELECT 'X' FROM ahl_stage_links link
WHERE EXISTS (SELECT 'X' FROM ahl_vwp_stages_b WHERE stage_id = link.object_id and stage_status_code = 'PLANNING')
START WITH link.subject_id = p_subject_id
CONNECT BY PRIOR link.object_id = link.subject_id;
SELECT pt.task_id
FROM PA_TASKS pt
WHERE
pt.task_name LIKE c_task_trail_name /* Filter only project tasks whose name ends with -G_TRANS_TSK_TRAIL_NAME */
AND pt.project_id=c_project_id
AND pt.parent_task_id=c_repair_batch_task_id
AND NOT EXISTS (
select 1 from AHL_VISIT_TASKS_B tsk where tsk.visit_id = c_visit_id AND tsk.project_task_id = pt.task_id
);