[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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
) ;
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 = c_task_rec.object_version_number + 1
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;
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);
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 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
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')
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.end_date_time task_end_date
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.end_date_time task_end_date
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;
UPDATE AHL_VISITS_B
SET PROJECT_ID = l_project_out.pa_project_id,
OBJECT_VERSION_NUMBER = c_visit_rec.object_version_number + 1
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
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 * 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 TASK_ID, TASK_NUMBER
FROM PA_TASKS
WHERE PROJECT_ID = x_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 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
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')
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.end_date_time task_end_date, avt.project_Task_id project_task_id
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.end_date_time task_end_date, avt.project_Task_id project_task_id
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;
SAVEPOINT Update_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
);
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
/*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;
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 * 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 count(*) FROM AHL_VISIT_TASKS_VL
WHERE VISIT_ID = x_id
AND NVL(STATUS_CODE,'X') <> 'DELETED'
AND (TASK_TYPE_CODE <> 'SUMMARY' OR
(TASK_TYPE_CODE = 'SUMMARY' AND
MR_ID IS NOT NULL));
SELECT * FROM AHL_VISIT_TASKS_VL
WHERE VISIT_ID = x_visit_id
AND NVL(STATUS_CODE,'X') not in ('DELETED','RELEASED')
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')
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')
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')
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')
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;
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'
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' --Srini Bug #4075702
AND task_type_code='UNASSOCIATED';
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_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 * 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)
FROM ahl_visit_tasks_vl 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 t.start_date_time < SYSDATE
and t.status_code ='PLANNING'
and t.task_type_code <>'SUMMARY'
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||'.'||substr(ar.title,1,(240 - (length(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;
/*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;
/*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_VISITS_B
SET STATUS_CODE = 'RELEASED',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
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
WHERE VISIT_ID = p_visit_id;
UPDATE AHL_VISIT_TASKS_B
SET STATUS_CODE = 'RELEASED',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
WHERE VISIT_ID = p_visit_id
AND STATUS_CODE = 'PLANNING';
UPDATE AHL_VISIT_TASKS_B
SET STATUS_CODE = 'RELEASED',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
WHERE VISIT_ID = p_visit_id
AND STATUS_CODE = 'PLANNING'
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');
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
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');
'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
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') in ('PLANNING')
AND task_type_code = 'SUMMARY';
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
WHERE NVL(status_code,'X') in ('PLANNING')
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;
/*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);
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 * 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||'.'||substr(ar.title,1,(240 - (length(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';
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;
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'
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 = 'RELEASED'
WHERE visit_task_id = p_tasks_tbl(i).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'
WHERE visit_id = p_visit_id;
UPDATE ahl_visits_b
SET status_code = 'RELEASED',
any_task_chg_flag ='N'
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')
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 t.start_date_time < SYSDATE
and t.status_code ='PLANNING'
and t.task_type_code <>'SUMMARY'
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)
OR ( t.originating_task_id is not null AND 'SR' = p_module_type
and t.service_request_id is not null))
CONNECT BY t.originating_task_id = PRIOR visit_task_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)
OR ( originating_task_id is not null AND '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)
OR ( originating_task_id is not null AND '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'
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)
OR ( originating_task_id is not null AND '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
AND STATUS_CODE NOT IN ('7', '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 * 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)
OR ( originating_task_id is not null AND 'SR' = p_module_type
and service_request_id is not null))
CONNECT BY originating_task_id = PRIOR visit_task_id;
SELECT count(*) 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)
OR ( originating_task_id is not null AND '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)
FROM ahl_visit_tasks_vl 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';
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||'.'||SUBSTR(ar.title, 1, (240 - (LENGTH(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;
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
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
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');
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
SELECT * 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)
OR ( originating_task_id is not null AND 'SR' = p_module_type
and service_request_id is not null))
CONNECT BY originating_task_id = PRIOR visit_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
WHERE VISIT_TASK_ID = c_task_rec.visit_task_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
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 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 wo.workorder_id, av.visit_id, av.project_id, av.start_date_time, av.close_date_time
FROM ahl_workorders wo, ahl_visits_b av
WHERE WO.WORKORDER_ID = p_wo_id
AND wo.visit_id = av.visit_id
AND wo.visit_task_id IS NULL
AND wo.master_workorder_flag = 'Y';
'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,
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);
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)
FROM ahl_visit_tasks_vl 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);