[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT(*) FROM Ahl_MR_Items_V
WHERE Inventory_Item_ID = c_item_id AND MR_HEADER_ID = c_mr_header_id;
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = c_visit_id;
SELECT 'X'
FROM mtl_system_items_b mtl, csi_item_instances csi
WHERE csi.instance_id = c_instance_id
AND csi.inventory_item_id = mtl.inventory_item_id
AND NVL(csi.inv_organization_id, csi.inv_master_organization_id) = mtl.organization_id
AND mtl.serial_number_control_code = 1;
SELECT REL.RELATED_MR_HEADER_ID
FROM AHL_MR_HEADERS_B AMHB, AHL_MR_RELATIONSHIPS_APP_V REL
WHERE REL.MR_HEADER_ID = x_mr_id
AND REL.RELATED_MR_HEADER_ID = AMHB.MR_HEADER_ID
AND AMHB.MR_STATUS_CODE = 'COMPLETE'
AND AMHB.VERSION_NUMBER IN
( SELECT VERSION_NUMBER
FROM AHL_MR_HEADERS_B
WHERE TITLE = AMHB.TITLE
AND TRUNC(SYSDATE) BETWEEN TRUNC(nvl(EFFECTIVE_FROM, sysdate-1)) AND
TRUNC(NVL(EFFECTIVE_TO,SYSDATE+1))
AND MR_STATUS_CODE = 'COMPLETE');
SELECT Any_Task_Chg_Flag,visit_id
FROM Ahl_Visits_VL
WHERE VISIT_ID = p_visit_id;
SELECT AMHV.TITLE
FROM AHL_VISIT_TASKS_B AVTB, AHL_MR_HEADERS_APP_V AMHV
WHERE AVTB.MR_ID = AMHV.MR_HEADER_ID
AND AVTB.MR_Id in (select mr_header_id
from ahl_mr_headers_b
where title in
(select title from ahl_mr_headers_b where mr_header_id = x_mr_id))
AND AVTB.INSTANCE_ID = x_serial_id
AND AVTB.VISIT_ID = x_id
AND (AVTB.STATUS_CODE IS NULL OR AVTB.STATUS_CODE not in ('CANCELLED','DELETED'));
UPDATE ahl_visit_tasks_b SET task_type_code = 'UNPLANNED'
WHERE visit_id = l_visit_id
AND visit_task_id IN
(
SELECT visit_task_id
FROM ahl_visit_tasks_b
WHERE visit_id = l_visit_id
START WITH mr_id = l_mr_header_id AND originating_task_id IS NULL
/*B6452310 - sowsubra - after the implementation of same mr added muliple times, assume a planned
requirement is added followed by an unplanned requirement. Then here all the tasks should not be
made Unplanned, the newly added tasks for the unplanned requirement should only be made unplanned
and which can be uniquely identified by the UE id generated.*/
AND UNIT_EFFECTIVITY_ID = p_x_task_Rec.unit_effectivity_id
CONNECT BY cost_parent_id = PRIOR visit_task_id
)
AND TASK_TYPE_CODE = 'PLANNED';
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);
PROCEDURE Update_Unplanned_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_Unplanned_Task';
SELECT * FROM Ahl_Visit_Tasks_VL
WHERE VISIT_TASK_ID = x_id;
SELECT * FROM Ahl_Visits_VL
WHERE VISIT_ID = x_id;
SAVEPOINT Update_Unplanned_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_procedure,'ahl.plsql.', l_full_name ||':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_Unplanned_Task;
ROLLBACK TO Update_Unplanned_Task;
ROLLBACK TO Update_Unplanned_Task;
END Update_Unplanned_Task;
PROCEDURE Delete_Unplanned_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
-- local variables defined for the procedure
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Delete Unplanned Task';
SELECT * FROM Ahl_Visit_Tasks_VL
WHERE Visit_Task_ID = x_id;
SAVEPOINT Delete_Unplanned_Task;
fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', ' Before Call to 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
);
/* Commented by mpothuku on 02/25/05 as this is moved to ahl_vwp_tasks_pvt.Delete_Summary_Task
AHL_UMP_UNPLANNED_PVT.DELETE_UNIT_EFFECTIVITY(
P_API_VERSION => 1.0,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data,
P_UNIT_EFFECTIVITY_ID => c_task_rec.unit_effectivity_id
);
fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', ' Error Before Commit---> After Delete Summary task call');
ROLLBACK TO Delete_Unplanned_Task;
ROLLBACK TO Delete_Unplanned_Task;
ROLLBACK TO Delete_Unplanned_Task;
END Delete_Unplanned_Task;
SELECT *
FROM Ahl_Visit_Tasks_vl
WHERE Visit_Task_ID = p_task_rec.Visit_Task_ID;