[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
SELECT AUEB.CSI_ITEM_INSTANCE_ID
FROM AHL_UNIT_EFFECTIVITIES_VL AUEB, CSI_ITEM_INSTANCES CSI
WHERE AUEB.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID
AND (AUEB.STATUS_CODE IS NULL OR AUEB.STATUS_CODE = 'INIT-DUE')
AND AUEB.UNIT_EFFECTIVITY_ID = x_unit_id AND AUEB.MR_HEADER_ID = x_mr_header_id;
SELECT AUR.RELATED_UE_ID "UNIT_ID"
FROM AHL_UE_RELATIONSHIPS AUR
START WITH AUR.UE_ID IN (SELECT AUEB.unit_effectivity_id
FROM AHL_UNIT_EFFECTIVITIES_VL AUEB
WHERE (AUEB.STATUS_CODE IS NULL OR AUEB.STATUS_CODE = 'INIT-DUE')
AND AUEB.unit_effectivity_id = x_ue_id
)
CONNECT BY PRIOR AUR.RELATED_UE_ID = AUR.UE_ID;
SELECT 'X'
FROM ahl_ue_relationships AUR, ahl_unit_effectivities_vl AUEB
WHERE AUR.ue_id = AUEB.unit_effectivity_id
AND (AUEB.status_code IS NULL OR AUEB.status_code = 'INIT-DUE')
AND AUR.ue_id = p_ue_id;
/*SELECT MR_HEADER_ID
FROM AHL_UNIT_EFFECTIVITIES_VL AUEB
WHERE (STATUS_CODE IS NULL OR STATUS_CODE IN ('INIT-DUE', 'DEFERRED'))
AND UNIT_EFFECTIVITY_ID = x_unit_id;*/
SELECT AUEB.MR_HEADER_ID
FROM AHL_UNIT_EFFECTIVITIES_VL AUEB, AHL_MR_HEADERS_B AMHB
WHERE AUEB.MR_HEADER_ID = AMHB.MR_HEADER_ID
AND AMHB.MR_STATUS_CODE = 'COMPLETE'
AND AMHB.VERSION_NUMBER IN
( SELECT MAX(VERSION_NUMBER)
FROM AHL_MR_HEADERS_B
WHERE TITLE = AMHB.TITLE
AND TRUNC(SYSDATE)
BETWEEN TRUNC(EFFECTIVE_FROM)
AND TRUNC(NVL(EFFECTIVE_TO,SYSDATE+1))
AND MR_STATUS_CODE = 'COMPLETE'
)
AND (AUEB.STATUS_CODE IS NULL OR AUEB.STATUS_CODE = 'INIT-DUE')
AND AUEB.UNIT_EFFECTIVITY_ID = x_unit_id;
SELECT Any_Task_Chg_Flag, Visit_Id
FROM Ahl_Visits_VL
WHERE VISIT_ID = p_visit_id;
SELECT VISIT_NUMBER
FROM AHL_VISITS_B
WHERE VISIT_ID IN ( SELECT DISTINCT VISIT_ID
FROM AHL_VISIT_TASKS_B
WHERE Unit_Effectivity_Id = x_unit_id
)
and status_code not in ('CANCELLED','DELETED');
SELECT OBJECT_TYPE
FROM AHL_UNIT_EFFECTIVITIES_VL
WHERE UNIT_EFFECTIVITY_ID = p_unit_id;
SELECT AUEB.CSI_ITEM_INSTANCE_ID,
AUEB.CS_INCIDENT_ID,
CSI.INV_MASTER_ORGANIZATION_ID,
CSI.INVENTORY_ITEM_ID
FROM AHL_UNIT_EFFECTIVITIES_VL AUEB, CSI_ITEM_INSTANCES CSI
WHERE AUEB.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID
AND (AUEB.STATUS_CODE IS NULL OR AUEB.STATUS_CODE = 'INIT-DUE')
AND AUEB.UNIT_EFFECTIVITY_ID = p_unit_id ;
SELECT INCIDENT_ID,
INCIDENT_NUMBER,
OBJECT_VERSION_NUMBER
FROM CS_INCIDENTS_ALL_B
WHERE INCIDENT_ID=P_service_id;
select incident_status_id,
name
from cs_incident_statuses_tl
-- where name = 'Planned';
SELECT visit_task_id
FROM ahl_visit_tasks_b
WHERE visit_id = p_visit_id
AND unit_effectivity_id = p_ue_id
AND NVL(status_code,'Y') <> 'DELETED';
SELECT visit_task_id
FROM ahl_visit_tasks_b
WHERE visit_id = c_visit_id
AND unit_effectivity_id = c_ue_id
AND NVL(status_code, 'PLANNING') <> 'DELETED'
AND TASK_TYPE_CODE = 'SUMMARY';
/*It is possible to update the SR with more MR's added through backward flow.(Included the
condition p_module_type <> 'SR')*/
IF c_unit%FOUND AND p_module_type <> 'SR' THEN
CLOSE c_unit;
fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Before calling AHL_VWP_RULES_PVT.Insert_Tasks');
AHL_VWP_RULES_PVT.Insert_Tasks
(p_visit_id => l_visit_id,
p_unit_id => l_unit_effectivity_id,
p_serial_id => l_serial_id,
p_service_id => l_service_req_id,
p_dept_id => l_department_id,
p_item_id => l_item_id,
p_item_org_id => l_org_id,
p_mr_id => NULL,
p_mr_route_id => NULL,
/* NR-MR Changes - sowsubra - Make the originating workorder as the originating task of NR Summary task*/
p_parent_id => p_x_task_Rec.ORIGINATING_TASK_ID,
p_flag => 'Y',
-- Added by rnahata for Issue 105 - pass the qty for summary task created for the SR
p_quantity => p_x_task_Rec.quantity,
x_task_id => l_parent_task_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
fnd_log.string(fnd_log.level_statement,L_DEBUG, 'After AHL_VWP_RULES_PVT.Insert_Tasks for Planned Task');
fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Before calling AHL_VWP_RULES_PVT.Insert_Tasks for Planned Task');
AHL_VWP_RULES_PVT.Insert_Tasks
(p_visit_id => l_visit_id,
p_unit_id => l_unit_effectivity_id,
p_serial_id => l_serial_id,
p_service_id => l_service_req_id,
p_dept_id => l_department_id,
p_item_id => l_item_id,
p_item_org_id => l_org_id,
p_mr_id => null,
p_mr_route_id => NULL,
p_parent_id => l_parent_task_id,
p_flag => 'N',
/* Added by rnahata for Issue 105 - pass the qty as 0 for
the planned task created when there are no MR's associated to the SR*/
p_quantity => p_x_task_Rec.quantity,
x_task_id => l_task_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
fnd_log.string(fnd_log.level_statement,L_DEBUG, 'After AHL_VWP_RULES_PVT.Insert_Tasks for Planned Task - l_return_status : '|| l_return_status);
CS_SERVICEREQUEST_PUB.Update_ServiceRequest(
p_api_version => 3.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_request_id => l_incident_id,
--p_request_number => l_incident_number,
p_request_number => NUll,
p_audit_comments => Null,
p_object_version_number => l_object_version_number,
p_resp_appl_id => NULL,
p_resp_id => NULL,
p_last_updated_by => NULL,
p_last_update_login => NULL,
p_last_update_date => NULL,
p_service_request_rec => l_service_request_rec,
p_notes => l_notes_table,
p_contacts => l_contacts_table,
p_called_by_workflow => NULL,
p_workflow_process_id => NULL,
x_workflow_process_id => l_workflow_process_id,
x_interaction_id => l_interaction_id
);
fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Before CS_ServiceRequest_PUB.Update_Status ');
CS_ServiceRequest_PUB.Update_Status
(
p_api_version => 2.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_resp_appl_id => NULL,
p_resp_id => NULL,
p_user_id => NULL,
p_login_id => NULL,
p_status_id => 52,
p_closed_date => NULL,
p_audit_comments => NULL,
p_called_by_workflow => FND_API.G_FALSE,
p_workflow_process_id => NULL,
p_comments => NULL,
p_public_comment_flag => FND_API.G_FALSE,
p_validate_sr_closure => 'N',
p_auto_close_child_entities => 'N',
p_request_id => l_incident_id,
p_request_number => NULL,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_object_version_number => l_object_version_number,
p_status => NULL,
x_interaction_id => l_interaction_id
);
fnd_log.string(fnd_log.level_statement,L_DEBUG, 'After CS_ServiceRequest_PUB.Update_Status - Return Status - '||x_return_status );
AHL_VWP_RULES_PVT.update_visit_task_flag(
p_visit_id =>l_visit_csr_rec.visit_id,
p_flag =>'Y',
x_return_status =>x_return_status);
SELECT aueb.mr_header_id
FROM ahl_unit_effectivities_vl aueb, ahl_mr_headers_b amhb
WHERE aueb.mr_header_id = amhb.mr_header_id
AND amhb.mr_status_code = 'COMPLETE'
AND amhb.version_number IN
(SELECT MAX(version_number)
FROM ahl_mr_headers_b
WHERE title = amhb.title
AND TRUNC(SYSDATE)
BETWEEN TRUNC(effective_from)
AND TRUNC(NVL(effective_to,SYSDATE+1))
AND mr_status_code = 'COMPLETE'
)
AND (aueb.status_code IS NULL OR aueb.status_code = 'INIT-DUE')
AND aueb.unit_effectivity_id = x_unit_id;
SELECT aueb.csi_item_instance_id
FROM ahl_unit_effectivities_vl aueb, csi_item_instances csi
WHERE aueb.csi_item_instance_id = csi.instance_id
AND (aueb.status_code IS NULL OR aueb.status_code = 'INIT-DUE')
AND aueb.unit_effectivity_id = x_unit_id
AND aueb.mr_header_id = x_mr_header_id;
SELECT aur.related_ue_id
FROM ahl_ue_relationships aur,
ahl_unit_effectivities_vl aueb
WHERE aur.ue_id = x_ue_id
AND aur.ue_id = aueb.unit_effectivity_id
AND (aueb.status_code IS NULL OR aueb.status_code = 'INIT-DUE');
SELECT visit_task_id
FROM ahl_visit_tasks_b
WHERE visit_id = p_visit_id
AND unit_effectivity_id = p_ue_id
AND NVL(status_code,'Y') <> 'DELETED';
SELECT csi_item_instance_id FROM AHL_UNIT_EFFECTIVITIES_B
WHERE UNIT_EFFECTIVITY_ID = p_unit_effectivity;
SELECT csii.quantity, ue.csi_item_instance_id
FROM csi_item_instances csii, ahl_unit_effectivities_b ue
WHERE ue.unit_effectivity_id = p_unit_effectivity
AND csii.instance_id = ue.csi_item_instance_id;
PROCEDURE Update_Planned_Task (
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 := 'JSP',
p_x_task_rec IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Planned_Task';
SELECT * FROM Ahl_Visits_VL
WHERE VISIT_ID = x_id;
SELECT * FROM Ahl_Visit_Tasks_VL
WHERE VISIT_TASK_ID = x_id;
SAVEPOINT Update_Planned_Task;
p_validation_mode => Jtf_Plsql_Api.g_update,
x_return_status => l_return_status
);
AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_UPDATES(
P_API_VERSION => 1.0,
P_VISIT_ID => l_Task_rec.visit_id,
P_VISIT_TASK_ID => l_Task_rec.visit_task_id,
P_STAGE_NAME => L_task_rec.STAGE_NAME,
X_STAGE_ID => L_task_rec.STAGE_ID,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data );
fnd_log.string(fnd_log.level_statement,L_DEBUG,'Update');
Ahl_Visit_Tasks_Pkg.UPDATE_ROW (
X_VISIT_TASK_ID => l_task_rec.visit_task_id,
X_VISIT_TASK_NUMBER => c_task_rec.visit_task_number,
X_OBJECT_VERSION_NUMBER => l_task_rec.OBJECT_VERSION_NUMBER + 1,
X_VISIT_ID => l_task_rec.visit_id,
X_PROJECT_TASK_ID => c_task_rec.project_task_id,
X_COST_PARENT_ID => l_task_rec.cost_parent_id,
X_MR_ROUTE_ID => c_task_rec.mr_route_id,
X_MR_ID => c_task_rec.mr_id,
X_DURATION => c_task_rec.duration,
X_UNIT_EFFECTIVITY_ID => c_task_rec.unit_effectivity_id,
X_START_FROM_HOUR => l_task_rec.start_from_hour,
X_INVENTORY_ITEM_ID => c_task_rec.inventory_item_id,
X_ITEM_ORGANIZATION_ID => c_task_rec.item_organization_id,
X_INSTANCE_ID => c_Task_rec.instance_id,
X_PRIMARY_VISIT_TASK_ID => c_task_rec.primary_visit_task_id,
X_ORIGINATING_TASK_ID => l_task_rec.originating_task_id, --c_task_rec.originating_task_id,
X_SERVICE_REQUEST_ID => c_task_rec.service_request_id,
X_TASK_TYPE_CODE => l_task_rec.TASK_TYPE_CODE,
X_DEPARTMENT_ID => l_task_rec.DEPARTMENT_ID,
X_SUMMARY_TASK_FLAG => 'N',
X_PRICE_LIST_ID => c_task_rec.price_list_id,
X_STATUS_CODE => c_task_rec.status_code,
X_ESTIMATED_PRICE => c_task_rec.estimated_price,
X_ACTUAL_PRICE => c_task_rec.actual_price,
X_ACTUAL_COST => c_task_rec.actual_cost,
-- Changes for 11.5.10 by Senthil.
X_STAGE_ID => l_task_rec.STAGE_ID,
-- Added cxcheng POST11510--------------
X_START_DATE_TIME => NULL,
X_END_DATE_TIME => NULL,
X_ATTRIBUTE_CATEGORY => c_task_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => c_task_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => c_task_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => c_task_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => c_task_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => c_task_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => c_task_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => c_task_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => c_task_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => c_task_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => c_task_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => c_task_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => c_task_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => c_task_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => c_task_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => c_task_rec.ATTRIBUTE15,
X_VISIT_TASK_NAME => l_task_rec.visit_task_name,
X_DESCRIPTION => l_task_rec.description,
X_QUANTITY => c_task_rec.QUANTITY, -- Added by rnahata for Issue 105
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID );
AHL_VWP_RULES_PVT.Update_Visit_Task_Flag
(p_visit_id => l_task_rec.visit_id,
p_flag => 'Y',
x_return_status => x_return_status);
ROLLBACK TO Update_Planned_Task;
ROLLBACK TO Update_Planned_Task;
ROLLBACK TO Update_Planned_Task;
END Update_Planned_Task;
PROCEDURE Delete_Planned_Task (
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 := 'JSP',
p_visit_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_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Delete Planned Task';
SELECT * FROM Ahl_Visit_Tasks_VL
WHERE Visit_Task_ID = x_id;
SAVEPOINT Delete_Planned_Task;
fnd_log.string(fnd_log.level_statement,l_debug,'Before calling AHL_VWP_TASKS_PVT.Delete_Summary_Task');
AHL_VWP_TASKS_PVT.Delete_Summary_Task(
p_api_version => p_api_version,
p_init_msg_list => Fnd_Api.g_false,
p_commit => Fnd_Api.g_false,
p_validation_level => Fnd_Api.g_valid_level_full,
p_module_type => NULL,
p_Visit_Task_Id => l_origin_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
fnd_log.string(fnd_log.level_statement,l_debug,'After calling AHL_VWP_TASKS_PVT.Delete_Summary_Task : x_return_status - '||x_return_status);
ROLLBACK TO Delete_Planned_Task;
ROLLBACK TO Delete_Planned_Task;
ROLLBACK TO Delete_Planned_Task;
END Delete_Planned_Task;
SELECT *
FROM Ahl_Visit_Tasks_vl
WHERE Visit_Task_ID = p_task_rec.Visit_Task_ID;