[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT item_instance_id
FROM ahl_visits_b
WHERE visit_id = p_visit_id;
SELECT subject_id
FROM csi_ii_relationships
WHERE subject_id = c_instance_id
START WITH object_id = c_top_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY object_id = PRIOR subject_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
/* SELECT concatenated_segments, inventory_item_id, organization_id
INTO x_item_name, x_item_id, x_org_id
FROM MTL_SYSTEM_ITEMS_KFV
WHERE concatenated_segments = p_item_name AND organization_id = p_org_id
AND organization_id IN (Select DISTINCT m.master_organization_id
FROM org_organization_definitions org, mtl_parameters m
WHERE org.organization_id = m.organization_id
AND NVL(org.operating_unit, mo_global.get_current_org_id())
= mo_global.get_current_org_id()
);*/
SELECT concatenated_segments, inventory_item_id, organization_id
INTO x_item_name, x_item_id, x_org_id
FROM MTL_SYSTEM_ITEMS_KFV
WHERE concatenated_segments = p_item_name
AND organization_id = p_org_id
AND organization_id IN
(SELECT DISTINCT m.master_organization_id
FROM INV_ORGANIZATION_INFO_V org,
mtl_parameters m
WHERE org.organization_id = m.organization_id
AND NVL(org.operating_unit,mo_global.get_current_org_id()) = mo_global.get_current_org_id()
) ;
/* SELECT concatenated_segments, inventory_item_id, organization_id
INTO x_item_name, x_item_id, x_org_id
FROM MTL_SYSTEM_ITEMS_KFV
WHERE inventory_item_id = p_item_id AND organization_id = p_org_id
AND organization_id IN (Select DISTINCT m.master_organization_id
FROM org_organization_definitions org, mtl_parameters m
WHERE org.organization_id = m.organization_id
AND NVL(org.operating_unit, mo_global.get_current_org_id())
= mo_global.get_current_org_id()
); */
SELECT concatenated_segments, inventory_item_id, organization_id
INTO x_item_name, x_item_id, x_org_id
FROM MTL_SYSTEM_ITEMS_KFV
WHERE inventory_item_id = p_item_id AND organization_id = p_org_id
AND organization_id IN (Select DISTINCT m.master_organization_id
FROM inv_organization_info_v org,
mtl_parameters m
WHERE org.organization_id = m.organization_id
AND NVL(org.operating_unit,mo_global.get_current_org_id())
= mo_global.get_current_org_id()
) ;
SELECT project_id
FROM PA_PROJECTS
WHERE name = p_proj_temp_name
AND TEMPLATE_FLAG = 'Y';
( SELECT organization_id
FROM INV_ORGANIZATION_INFO_V
WHERE NVL(operating_unit,mo_global.get_current_org_id()) =
mo_global.get_current_org_id()
);
SELECT project_id
FROM PA_PROJECTS
WHERE name = p_proj_temp_name
AND TEMPLATE_FLAG = 'Y'
AND carrying_out_organization_id IN (SELECT organization_id
FROM org_organization_definitions
WHERE NVL(operating_unit, mo_global.get_current_org_id()) =
mo_global.get_current_org_id());
SELECT Instance_Id INTO x_serial_id
FROM CSI_ITEM_INSTANCES
WHERE Instance_Id = p_serial_id AND Inventory_Item_Id = p_item_id AND Inv_Master_Organization_Id = p_org_id
AND ACTIVE_START_DATE <= sysdate AND (ACTIVE_END_DATE >= sysdate OR ACTIVE_END_DATE IS NULL);
SELECT Instance_Id INTO x_serial_id
FROM CSI_ITEM_INSTANCES
WHERE Serial_Number = p_serial_number AND Inventory_Item_Id = p_item_id AND Inv_Master_Organization_Id = p_org_id
AND ACTIVE_START_DATE <= sysdate AND (ACTIVE_END_DATE >= sysdate OR ACTIVE_END_DATE IS NULL);
SELECT department_id INTO x_department_id
FROM BOM_DEPARTMENTS
WHERE organization_id = p_organization_id
AND department_id = p_department_id;
SELECT department_id INTO x_department_id
FROM BOM_DEPARTMENTS
WHERE organization_id = p_organization_id
AND description = p_dept_name;
SELECT hou.organization_id
INTO x_organization_id
FROM hr_organization_units hou, mtl_parameters MP
WHERE hou.organization_id = mp.organization_id
AND hou.organization_id = p_organization_id
AND hou.organization_id IN
(SELECT organization_id
FROM INV_ORGANIZATION_INFO_V
WHERE hou.organization_id = mp.organization_id
AND NVL(operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id())
AND MP.EAM_enabled_flag='Y';
SELECT hou.organization_id
INTO x_organization_id
FROM hr_organization_units hou, mtl_parameters MP
WHERE hou.organization_id = mp.organization_id
AND hou.Name = p_org_name
AND hou.organization_id IN
(SELECT organization_id
FROM INV_ORGANIZATION_INFO_V
WHERE hou.organization_id = mp.organization_id
AND NVL(operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id())
AND MP.EAM_enabled_flag='Y';
SELECT a.Incident_Id INTO x_service_id
FROM CS_INCIDENTS_ALL_B a, CS_INCIDENT_TYPES_VL CIT
WHERE a.incident_type_id = cit.incident_type_id
AND cit.INCIDENT_SUBTYPE = 'INC'
AND cit.CMRO_FLAG = 'Y'
AND Incident_Id = p_service_id;
SELECT a.Incident_Id INTO x_service_id
FROM CS_INCIDENTS_ALL_B a, CS_INCIDENT_TYPES_VL CIT
WHERE a.incident_type_id = cit.incident_type_id
AND cit.INCIDENT_SUBTYPE = 'INC'
AND cit.CMRO_FLAG = 'Y'
AND Incident_Number = p_service_number;
SELECT SIMULATION_PLAN_ID FROM
AHL_VISITS_VL WHERE VISIT_ID = x_id;
SELECT 'x'
FROM ahl_simulation_plans_vl ASP
WHERE primary_plan_flag = 'Y'
AND EXISTS ( SELECT 1
FROM ahl_visits_b
WHERE visit_id = x_id
AND NVL(simulation_plan_id,-99) = ASP.simulation_plan_id);
SELECT SIMULATION_PLAN_ID INTO l_simulation_plan_id
FROM AHL_SIMULATION_PLANS_VL WHERE primary_plan_flag = 'Y';
SELECT Visit_Task_Id INTO x_visit_task_id
FROM AHL_VISIT_TASKS_B
WHERE Visit_Task_Id = p_visit_task_id AND Visit_Id = p_visit_id AND status_code <> 'DELETED';
SELECT Visit_Task_Id INTO x_visit_task_id
FROM AHL_VISIT_TASKS_B
WHERE Visit_Task_Number = p_visit_task_number AND Visit_Id = p_visit_id AND status_code <> 'DELETED';
SELECT Lookup_Code INTO x_lookup_code
FROM FND_LOOKUP_VALUES_VL
WHERE Lookup_Type = p_lookup_type
AND Lookup_Code = p_lookup_code
AND enabled_flag = 'Y' --sowsubra FP:Bug#5758829
AND SYSDATE BETWEEN nvl(start_date_active,sysdate) --Sowmya Bug#5715342
AND NVL(end_date_active,SYSDATE);
SELECT Lookup_Code INTO x_lookup_code
FROM FND_LOOKUP_VALUES_VL
WHERE Lookup_Type = p_lookup_type
AND Meaning = p_meaning
AND enabled_flag = 'Y' --sowsubra FP:Bug#5758829
AND SYSDATE BETWEEN nvl(start_date_active,sysdate) --sowsubra FP:Bug#5758829
AND NVL(end_date_active,SYSDATE);
SELECT RESPONSIBILITY_ID
FROM FND_RESPONSIBILITY_VL
WHERE RESPONSIBILITY_KEY LIKE 'PROJECT_BILLING_SUPER_USER';
SELECT RESPONSIBILITY_ID
FROM FND_USER_RESP_GROUPS
WHERE USER_ID = Fnd_Global.USER_ID AND RESPONSIBILITY_ID = x_resp_id;
SELECT csi_item_instance_id
FROM ahl_unit_config_headers
WHERE name = p_unit_name AND unit_config_status_code = 'COMPLETE'
AND (active_end_date is null or active_end_date > sysdate);
SELECT Inventory_Item_Id, Inv_Master_Organization_Id
INTO x_Item_Id, x_Item_Org_Id
FROM CSI_ITEM_INSTANCES
WHERE Instance_Id = x_instance_id;
PROCEDURE Insert_Tasks (
p_visit_id IN NUMBER,
p_unit_id IN NUMBER,
p_serial_id IN NUMBER,
p_service_id IN NUMBER,
p_dept_id IN NUMBER,
p_item_id IN NUMBER,
p_item_org_id IN NUMBER,
p_mr_id IN NUMBER,
p_mr_route_id IN NUMBER,
p_parent_id IN NUMBER,
p_flag IN VARCHAR2,
p_stage_id IN NUMBER := NULL,
-- SKPATHAK :: ER: 9147951 :: 11-JAN-2010
-- Added past start and end dates
p_past_task_start_date IN DATE := NULL,
p_past_task_end_date IN DATE := NULL,
p_quantity IN NUMBER := NULL, -- Added by rnahata for Issue 105
-- SKPATHAK :: Bug 8343599 :: 14-APR-2009
p_task_start_date IN DATE := NULL,
-- AVIKUKUM :: FP:PIE ::13-OCT-2010 :: Service Type code added
p_service_type_code IN VARCHAR2 := NULL,
x_task_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
-- Define local variables
L_API_NAME CONSTANT VARCHAR2(30) := 'Insert_Tasks';
SELECT title, description /*Bug 5758813- Fetch the description of the MR*/
FROM ahl_mr_headers_vl
WHERE mr_header_id = c_mr_id;
SELECT substrb(title,1,80), description /*Bug 5758813 - Fetch the description.*/
FROM ahl_unit_effectivities_v
WHERE unit_effectivity_id = c_unit_eff_id;
SELECT substrb(ar.title,1,80), ar.remarks,
-- SKPATHAK :: 02-MAY-2011 :: VWPE :: Added below two more columns
mrr.stage_type_code, ar.return_to_supply_flag
FROM ahl_routes_vl ar, ahl_mr_routes mrr
WHERE mrr.mr_route_id = c_mr_route_id
AND mrr.route_id = ar.route_id;
SELECT stage.stage_id
FROM ahl_mr_routes mr, ahl_visit_stage_typ_asoc assoc, ahl_vwp_stages_b stage
WHERE assoc.stage_id = stage.stage_id
AND stage.visit_id = c_visit_id
AND assoc.stage_type_code = mr.stage_type_code
AND mr.mr_route_id = c_mr_route_id
AND stage.stage_status_code <> 'RELEASED';
SELECT mr_id
FROM ahl_visit_tasks_b
WHERE visit_id = c_visit_id
AND service_request_id IS NULL
START WITH visit_task_id = c_parent_id
CONNECT BY PRIOR originating_task_id = visit_task_id;
SELECT AHL_VWP_VISITS_PVT.Is_Old_Visit(vst_id) IS_OLD_VISIT
FROM DUAL;
SELECT unit_effectivity_id FROM Ahl_Unit_effectivities_b
WHERE unit_effectivity_id = x_id
FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
SELECT start_date_time
FROM ahl_visits_b
WHERE visit_id = c_visit_id;
'Error in Insert_Tasks. Serial Id missing.');
'Before calling AHL_VISIT_TASKS_PKG.Insert_Row.');
Ahl_Visit_Tasks_Pkg.Insert_Row (
X_ROWID => l_rowid,
X_VISIT_TASK_ID => l_task_ID ,
X_VISIT_TASK_NUMBER => l_task_number,
X_OBJECT_VERSION_NUMBER => 1,
X_VISIT_ID => p_visit_id,
X_PROJECT_TASK_ID => NULL,
X_COST_PARENT_ID => p_parent_id,
X_MR_ROUTE_ID => p_mr_route_id,
X_MR_ID => p_mr_id,
X_DURATION => NULL,
X_UNIT_EFFECTIVITY_ID => p_unit_id,
X_START_FROM_HOUR => NULL,
X_INVENTORY_ITEM_ID => p_item_id,
X_ITEM_ORGANIZATION_ID => p_item_org_id,
X_INSTANCE_ID => p_serial_id,
X_PRIMARY_VISIT_TASK_ID => NULL,
X_ORIGINATING_TASK_ID => p_parent_id,
X_SERVICE_REQUEST_ID => p_service_id,
X_TASK_TYPE_CODE => l_type,
-- AVIKUKUM :: FP:PIE ::13-OCT-2010 :: Service Type code added
X_SERVICE_TYPE_CODE => p_service_type_code,
-- SKPATHAK :: 02-MAY-2011 :: VWPE :: Added below two more parameters
X_STAGE_TYPE_CODE => l_stage_type_code,
X_RETURN_TO_SUPPLY_FLAG => l_return_to_supply_flag,
--VWP ER-12424063:: tchimira :: 10-MAY -2011
X_ORIGINATING_MR_HEADER_ID => l_originating_mr_header_id,
X_DEPARTMENT_ID => p_dept_id,
X_SUMMARY_TASK_FLAG => 'N',
X_PRICE_LIST_ID => NULL,
X_STATUS_CODE => 'PLANNING',
X_ESTIMATED_PRICE => NULL,
X_ACTUAL_PRICE => NULL,
X_ACTUAL_COST => NULL,
X_STAGE_ID => l_stage_id,
-- Added cxcheng POST11510--------------
X_START_DATE_TIME => p_past_task_start_date,
X_END_DATE_TIME => p_past_task_end_date,
X_PAST_TASK_START_DATE => p_past_task_start_date,
X_PAST_TASK_END_DATE => p_past_task_end_date,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_VISIT_TASK_NAME => l_name, --Bug 5758813
X_DESCRIPTION => l_description, --Bug 5758813
-- Added by rnahata for Issue 105
X_QUANTITY => p_quantity,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => Fnd_Global.USER_ID,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID);
'After calling AHL_VISIT_TASKS_PKG.Insert_Row.');
END Insert_Tasks;
SELECT MR_HEADER_ID, RELATED_MR_HEADER_ID
FROM AHL_MR_RELATIONSHIPS
START WITH MR_HEADER_ID = (
SELECT MR_HEADER_ID
FROM AHL_MR_HEADERS_APP_V
WHERE MR_HEADER_ID = x_MR_id)
CONNECT BY PRIOR RELATED_MR_HEADER_ID = MR_HEADER_ID;
SELECT Ahl_Visit_Tasks_B_S.NEXTVAL
FROM dual;
SELECT 1
FROM Ahl_Visit_Tasks_VL
WHERE Visit_Task_id = c_id;
SELECT MAX(visit_task_number)
FROM Ahl_Visit_Tasks_B
WHERE Visit_Id = p_visit_id;
SELECT COUNT(*) FROM AHL_MR_ROUTE_SEQUENCES_APP_V
WHERE MR_ROUTE_ID = x_route_id;
SELECT MR_ROUTE_ID, RELATED_MR_ROUTE_ID FROM AHL_MR_ROUTE_SEQUENCES_APP_V
WHERE MR_ROUTE_ID = x_route_id;
SELECT VISIT_TASK_ID FROM AHL_VISIT_TASKS_VL
WHERE MR_ROUTE_ID = x_route_id AND INSTANCE_ID = x_serial_id
AND VISIT_ID = x_id AND TASK_TYPE_CODE = x_type
AND nvl(STATUS_CODE,'x') <> 'DELETED'
AND UNIT_EFFECTIVITY_ID = c_ue_id;
SELECT T1.MR_ROUTE_ID
FROM AHL_MR_ROUTES_V T1,
AHL_ROUTES_B T2
WHERE T1.MR_HEADER_ID = x_mr_id
AND T1.ROUTE_ID = T2.ROUTE_ID
AND T2.REVISION_STATUS_CODE = 'COMPLETE'
-- Added as of Bug# 3562914
-- By shbhanda 04/22/2004
AND T1.ROUTE_REVISION_NUMBER
IN ( SELECT MAX(T3.ROUTE_REVISION_NUMBER)
FROM AHL_MR_ROUTES_V T3
WHERE T3.MR_HEADER_ID = x_mr_id
AND T3.ROUTE_NUMBER = T1.ROUTE_NUMBER
GROUP BY T3.ROUTE_NUMBER
);
SELECT ahl_task_links_s.nextval INTO l_task_link_id FROM DUAL;
'Before inserting record into AHL_TASK_LINKS');
INSERT INTO AHL_TASK_LINKS
(
TASK_LINK_ID,OBJECT_VERSION_NUMBER, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, VISIT_TASK_ID, PARENT_TASK_ID,
ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,PRECEDENCE_CONSTRAINT
)
VALUES
(
l_TASK_LINK_ID, 1,sysdate, fnd_global.user_id, sysdate,
fnd_global.user_id, fnd_global.user_id, l_child_task_id, l_parent_task_id,
l_task_rec.ATTRIBUTE_CATEGORY, l_task_rec.ATTRIBUTE1, l_task_rec.ATTRIBUTE2,
l_task_rec.ATTRIBUTE3, l_task_rec.ATTRIBUTE4, l_task_rec.ATTRIBUTE5,
l_task_rec.ATTRIBUTE6, l_task_rec.ATTRIBUTE7, l_task_rec.ATTRIBUTE8,
l_task_rec.ATTRIBUTE9, l_task_rec.ATTRIBUTE10,l_task_rec.ATTRIBUTE11,
l_task_rec.ATTRIBUTE12, l_task_rec.ATTRIBUTE13, l_task_rec.ATTRIBUTE14,
l_task_rec.ATTRIBUTE15, l_prec_constraint
);
'After inserting record into AHL_TASK_LINKS and before calling VALIDATE_VWP_LINKS');
SELECT T1.MR_ROUTE_ID,
-- Added for 11.5.10 Changes done by Senthil.
T1.STAGE
FROM AHL_MR_ROUTES_V T1, AHL_ROUTES_B T2
WHERE T1.MR_HEADER_ID = x_mr_id
AND T1.ROUTE_ID = T2.ROUTE_ID
AND T2.REVISION_STATUS_CODE = 'COMPLETE'
-- Added as of Bug# 3562914
-- By shbhanda 04/22/2004
AND T1.ROUTE_REVISION_NUMBER
IN (SELECT MAX(T3.ROUTE_REVISION_NUMBER)
FROM AHL_MR_ROUTES_V T3
WHERE T3.MR_HEADER_ID = x_mr_id
AND T3.ROUTE_NUMBER = T1.ROUTE_NUMBER
GROUP BY T3.ROUTE_NUMBER
);
SELECT Visit_Id, Visit_Task_id
FROM AHL_VISIT_TASKS_B
WHERE MR_Route_Id = x_mroute_id
AND Instance_Id = x_serial_id
AND Unit_Effectivity_Id = x_unit_id
AND (STATUS_CODE IS NULL OR STATUS_CODE <> 'DELETED');
SELECT 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.CSI_ITEM_INSTANCE_ID = x_serial_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 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 = 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 <> 'DELETED');
SELECT stage_id,
stage_name
FROM ahl_vwp_stages_vl
WHERE stage_num = p_stage_number
AND visit_id = p_visit_id;
SELECT TITLE
FROM ahl_mr_headers_b
WHERE mr_header_id = p_mr_id;
SELECT visit_task_id,
start_date_time,
end_date_time
FROM ahl_visit_tasks_b
WHERE visit_task_id = p_visit_task_id;
SELECT AHL_VWP_VISITS_PVT.Is_Old_Visit(vst_id) IS_OLD_VISIT
FROM DUAL;
'Before calling INSERT_TASKS for Summary Task. Task Id = ' || l_task_id);
INSERT_TASKS
(p_visit_id => l_visit_id,
p_unit_id => l_unit_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 => l_MR_id,
p_mr_route_id => NULL,
p_parent_id => l_parent_MR_Id,
p_flag => 'Y',
P_STAGE_ID => NULL,
-- Added by rnahata for Issue 105 - pass the quantity for summary task
p_quantity => p_quantity,
-- SKPATHAK :: Bug 8343599 :: 14-APR-2009
p_task_start_date => p_task_start_date,
-- AVIKUKUM :: FP:PIE ::13-OCT-2010 :: Service Type code added
p_service_type_code => p_service_type_code,
x_task_id => l_task_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
'After calling INSERT_TASKS for Summary Task. Task Id = ' || l_task_id ||
'. Return Status = ' || l_return_status);
'Errors from INSERT_TASKS. Message count: ' ||
l_msg_count || ', message data: ' || l_msg_data);
SELECT T1.MR_ROUTE_ID, T1.STAGE
BULK COLLECT INTO MR_Route_Tbl,l_Stage_num_Tbl
FROM AHL_MR_ROUTES_V T1, AHL_ROUTES_B T2
WHERE T1.MR_HEADER_ID = l_MR_Id
AND T1.ROUTE_ID = T2.ROUTE_ID
AND T2.revision_status_code = 'COMPLETE'
AND T1.ROUTE_REVISION_NUMBER IN
(SELECT MAX(T3.ROUTE_REVISION_NUMBER)
FROM AHL_MR_ROUTES_V T3
WHERE T3.MR_HEADER_ID = l_MR_Id
AND T3.ROUTE_NUMBER = T1.ROUTE_NUMBER
GROUP BY T3.ROUTE_NUMBER
);
'Before calling INSERT_TASKS for Simple Task');
INSERT_TASKS
(p_visit_id => l_visit_id,
p_unit_id => l_unit_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 => l_MR_Id,
p_MR_Route_id => l_MR_route_id,
p_parent_id => l_parent_task_id,
p_flag => 'N',
P_STAGE_ID => l_stage_id,
-- SKPATHAK :: ER: 9147951 :: 11-JAN-2010 :: Pass past dates too
p_past_task_start_date => p_past_task_start_date,
p_past_task_end_date => p_past_task_end_date,
-- Added by rnahata for Issue 105 - pass the quantity for the simple tasks
p_quantity => p_quantity,
-- SKPATHAK :: Bug 8343599 :: 14-APR-2009
p_task_start_date => p_task_start_date,
-- AVIKUKUM :: FP:PIE ::13-OCT-2010 :: Service Type code added
p_service_type_code => p_service_type_code,
x_task_id => l_task_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
'After calling INSERT_TASKS for Simple Task. Task Id = ' || l_task_id ||
'. Visit ID = ' || l_visit_id);
'Errors from INSERT_TASKS. Message count: ' ||
l_msg_count || ', message data: ' || l_msg_data);
SELECT VISIT_TASK_ID FROM AHL_VISIT_TASKS_B
WHERE VISIT_ID = c_id
START WITH COST_PARENT_ID = c_cost_id
CONNECT BY PRIOR VISIT_TASK_ID = COST_PARENT_ID;
SELECT VISIT_TASK_ID FROM AHL_VISIT_TASKS_B
WHERE VISIT_ID = x_id
AND NVL(STATUS_CODE,'X') <> 'DELETED'
START WITH ORIGINATING_TASK_ID = x_org_id
CONNECT BY PRIOR VISIT_TASK_ID = ORIGINATING_TASK_ID;
PROCEDURE Update_Visit_Task_Flag
(p_visit_id IN NUMBER,
p_flag IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
-- Define local variables
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Visit_Task_Flag';
UPDATE AHL_VISITS_B
SET 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,
ANY_TASK_CHG_FLAG = p_flag
WHERE VISIT_ID = p_visit_id;
END Update_Visit_Task_Flag;
SELECT service_request_id FROM ahl_visits_b
WHERE visit_id = p_visit_id;
SELECT customer_id FROM CS_INCIDENTS_ALL_B
WHERE incident_id = p_service_request_id;
SELECT qlhv.list_header_id
FROM qp_list_headers_vl qlhv, FINANCIALS_SYSTEM_PARAMETERS FSP, qp_qualifiers qpq, GL_SETS_OF_BOOKS GSB
WHERE FSP.set_of_books_id = GSB.set_of_books_id
AND qlhv.list_type_code = 'PRL'
AND qlhv.currency_code = gsb.currency_code
AND UPPER(qlhv.name) like UPPER(p_price_list_name)
AND qpq.QUALIFIER_ATTR_VALUE = p_customer_id
AND qpq.list_header_id=qlhv.list_header_id
AND qpq.qualifier_context = 'CUSTOMER'
AND qpq.qualifier_attribute = 'QUALIFIER_ATTRIBUTE16'
UNION
SELECT qlhv.list_header_id
FROM qp_list_headers_vl qlhv,oe_agreements oa, qp_qualifiers qpq, FINANCIALS_SYSTEM_PARAMETERS FSP, GL_SETS_OF_BOOKS GSB
WHERE FSP.set_of_books_id = GSB.set_of_books_id
AND ((oa.price_list_id = qlhv.list_header_id AND qlhv.list_type_code
IN('PRL', 'AGR')) OR qlhv.list_type_code = 'PRL')
AND qlhv.currency_code = gsb.currency_code
AND UPPER(qlhv.name) like UPPER(p_price_list_name)
AND qpq.QUALIFIER_ATTR_VALUE = p_customer_id
AND qpq.list_header_id=qlhv.list_header_id
AND qpq.qualifier_context = 'CUSTOMER'
AND qpq.qualifier_attribute = 'QUALIFIER_ATTRIBUTE16';*/
SELECT qlhv.list_header_id
from qp_list_headers_vl qlhv, qp_qualifiers qpq
where qlhv.list_type_code = 'PRL'
and upper(qlhv.name) like upper(p_price_list_name)
and qpq.QUALIFIER_ATTR_VALUE = p_customer_id
and qpq.list_header_id=qlhv.list_header_id
and qpq.qualifier_context = 'CUSTOMER'
and qpq.qualifier_attribute = 'QUALIFIER_ATTRIBUTE16';
PROCEDURE Update_Cost_Origin_Task
(p_visit_task_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
-- Define local variables
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Cost_Origin_Task';
SELECT Visit_Task_Id, Object_Version_Number
FROM Ahl_Visit_Tasks_VL
WHERE ORIGINATING_TASK_ID = x_task_id;
SELECT Visit_Task_Id, Object_Version_Number
FROM Ahl_Visit_Tasks_VL
WHERE COST_PARENT_ID = x_task_id;
SELECT cost_parent_id
FROM AHL_VISIT_TASKS_VL
WHERE visit_task_id = x_task_id;
'update origin');
UPDATE AHL_VISIT_TASKS_B SET ORIGINATING_TASK_ID = NULL,
OBJECT_VERSION_NUMBER = c_origin_rec.object_version_number + 1
WHERE VISIT_TASK_ID = c_origin_rec.visit_task_id;
'update parent');
UPDATE AHL_VISIT_TASKS_B SET COST_PARENT_ID = l_parent_id,
OBJECT_VERSION_NUMBER = c_parent_rec.object_version_number + 1
WHERE VISIT_TASK_ID = c_parent_rec.visit_task_id;
END Update_Cost_Origin_Task;
SELECT visit_task_id, originating_task_id
FROM AHL_VISIT_TASKS_B
WHERE VISIT_ID = p_visit_id
AND VISIT_TASK_ID = p_tsk_id
AND (STATUS_CODE IS NULL OR STATUS_CODE <> 'DELETED');
SELECT VST.organization_id FROM AHL_VISITS_B VST
WHERE VST.visit_id = p_visit_id;
SELECT currency_code
FROM cst_acct_info_v COD, GL_SETS_OF_BOOKS GSOB
WHERE COD.Organization_Id = p_org_id
AND LEDGER_ID = GSOB.SET_OF_BOOKS_ID
AND NVL(operating_unit, mo_global.get_current_org_id())= mo_global.get_current_org_id();
/*SELECT currency_code
-- into x_currency_code
FROM CST_ORGANIZATION_DEFINITIONS COD --,AHL_VISITS_B VST
WHERE --VST.visit_id = p_visit_id AND
--COD.Organization_Id = VST.organization_id
COD.Organization_Id = p_org_id
AND NVL(operating_unit, mo_global.get_current_org_id())
= mo_global.get_current_org_id();*/
SELECT AWO.STATUS_CODE, FLV.MEANING
FROM AHL_WORKORDERS AWO, FND_LOOKUP_VALUES_VL FLV
WHERE AWO.VISIT_ID = x_id
AND AWO.STATUS_CODE <> 7 AND AWO.STATUS_CODE <> 22
AND FLV.LOOKUP_TYPE(+) = 'AHL_JOB_STATUS'
AND AWO.STATUS_CODE = FLV.LOOKUP_CODE(+)
AND AWO.MASTER_WORKORDER_FLAG = 'Y'
AND AWO.VISIT_TASK_ID IS NULL;
SELECT AWO.STATUS_CODE, FLV.MEANING
FROM AHL_WORKORDERS AWO, FND_LOOKUP_VALUES_VL FLV
WHERE AWO.VISIT_TASK_ID = x_id
AND AWO.STATUS_CODE <> 7 AND AWO.STATUS_CODE <> 22
AND FLV.LOOKUP_TYPE(+) = 'AHL_JOB_STATUS'
AND AWO.STATUS_CODE = FLV.LOOKUP_CODE(+);
SELECT 'x'
FROM AHL_DEPARTMENT_SHIFTS
WHERE DEPARTMENT_ID = P_DEPT_ID;
SELECT visit_task_id
FROM ahl_visit_tasks_b
WHERE visit_task_number = c_cost_parent_number
AND visit_id = c_visit_id;
SELECT start_date_time, end_date_time
FROM ahl_visit_tasks_b
WHERE task_type_code<>'SUMMARY'
START WITH visit_task_id = c_id
CONNECT BY PRIOR cost_parent_id = visit_task_id;
SELECT start_date_time, end_date_time
FROM ahl_visit_tasks_b
WHERE task_type_code<>'SUMMARY'
AND visit_task_id <> c_id
START WITH visit_task_id = c_id
CONNECT BY PRIOR visit_task_id = cost_parent_id;
SELECT START_DATE_TIME , department_id FROM AHL_VISITS_B
WHERE VISIT_ID = c_visit_id;
SELECT sum(duration)
FROM AHL_VWP_STAGES_VL
WHERE visit_id = c_visit_id
AND stage_num < (select stage_num
from AHL_VWP_STAGES_VL
WHERE stage_name = c_stage_name
AND visit_id = c_visit_id);
SELECT lookup_code
FROM pa_lookups
WHERE lookup_type = 'SERVICE TYPE'
AND enabled_flag = 'Y'
AND meaning = c_service_type
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE-1))
AND TRUNC(NVL(end_date_active,SYSDATE));
SELECT 'X'
FROM pa_lookups
WHERE lookup_type = 'SERVICE TYPE'
AND enabled_flag = 'Y'
AND lookup_code = c_service_type_code
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE-1))
AND TRUNC(NVL(end_date_active,SYSDATE));
SELECT object_type
FROM ahl_unit_effectivities_b
WHERE unit_effectivity_id = c_unit_effectivity_id;
SELECT TASK_TYPE_CODE
FROM ahl_visit_tasks_b
WHERE VISIT_TASK_ID = c_task_id;
SELECT AMRH.WARRANTY_TEMPLATE_ID
FROM AHL_VISIT_TASKS_VL ATSK, AHL_MR_HEADERS_B AMRH
WHERE ATSK.MR_ID = AMRH.MR_HEADER_ID(+)
AND ATSK.VISIT_TASK_ID = c_task_id;
SELECT ARV.WARRANTY_TEMPLATE_ID
FROM AHL_VISIT_TASKS_VL ATSK,
AHL_MR_ROUTES_V AMRR,
AHL_ROUTES_B ARV
WHERE ATSK.MR_ROUTE_ID = AMRR.MR_ROUTE_ID(+)
AND AMRR.ROUTE_ID = ARV.ROUTE_ID (+)
AND ATSK.VISIT_TASK_ID = c_task_id;
SELECT AWCV.WARRANTY_CONTRACT_ID
FROM AHL_WARRANTY_CONTRACTS_VL AWCV
WHERE AWCV.CONTRACT_STATUS_CODE = 'ACTIVE'
AND AWCV.ITEM_INSTANCE_ID = c_instance_id
AND AWCV.WARRANTY_TEMPLATE_ID = c_warranty_templ_id;
SELECT AWE.WARRANTY_ENTITLEMENT_ID,AWE.OBJECT_VERSION_NUMBER
FROM AHL_WARRANTY_ENTITLEMENTS AWE ,AHL_VISIT_TASKS_B ATSK
WHERE ATSK.SERVICE_REQUEST_ID = AWE.SR_INCIDENT_ID
AND ATSK.TASK_TYPE_CODE = 'PLANNED'
AND ATSK.MR_ROUTE_ID IS NULL
AND AWE.VISIT_TASK_ID IS NULL
AND AWE.SR_INCIDENT_ID = c_sr_incident_id;
SELECT 'X'
FROM AHL_VISIT_TASKS_B
WHERE TASK_TYPE_CODE = 'PLANNED'
AND MR_ROUTE_ID IS NULL
AND VISIT_TASK_ID = c_task_id;
l_entitlement_rec.operation_flag := AHL_WARRANTY_ENTL_PVT.G_OP_UPDATE;
PROCEDURE Get_Warranty_Rec_for_Update(
p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type,
x_warranty_entl_tbl OUT NOCOPY AHL_WARRANTY_ENTL_PVT.Warranty_Entl_Tbl_Type
) IS
-- Standard variables
L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Warranty_Rec_for_Update';
SELECT TASK_TYPE_CODE
FROM ahl_visit_tasks_b
WHERE VISIT_TASK_ID = c_task_id;
SELECT AWCB.WARRANTY_CONTRACT_ID, AWCB.CONTRACT_NUMBER
, AWSE.ENTITLEMENT_STATUS_CODE, AWSE.OBJECT_VERSION_NUMBER
FROM AHL_WARRANTY_ENTITLEMENTS AWSE, AHL_WARRANTY_CONTRACTS_B AWCB
WHERE AWSE.WARRANTY_CONTRACT_ID = AWCB.WARRANTY_CONTRACT_ID(+)
AND AWSE.VISIT_TASK_ID = c_task_id;
SELECT WARRANTY_CONTRACT_ID
FROM AHL_WARRANTY_CONTRACTS_B
WHERE CONTRACT_STATUS_CODE = 'ACTIVE'
AND CONTRACT_NUMBER = c_contract_num;
SELECT WARRANTY_ENTITLEMENT_ID, OBJECT_VERSION_NUMBER
FROM AHL_WARRANTY_ENTITLEMENTS
WHERE VISIT_TASK_ID = c_task_id;
SELECT VISIT_TASK_ID
FROM AHL_VISIT_TASKS_B
WHERE TASK_TYPE_CODE <> 'SUMMARY'
AND ORIGINATING_TASK_ID = c_task_id;
SELECT 'X'
FROM AHL_VISIT_TASKS_B
WHERE TASK_TYPE_CODE = 'PLANNED'
AND MR_ROUTE_ID IS NULL
AND VISIT_TASK_ID = c_task_id;
IF(p_task_rec.warranty_entitlement_id is not null) THEN -- Entitlements Record Already Exists UPDATE
l_entitlement_rec.warranty_entitlement_id := p_task_rec.warranty_entitlement_id;
l_entitlement_rec.operation_flag := AHL_WARRANTY_ENTL_PVT.G_OP_UPDATE;
IF(c_entitlements_exists%FOUND) THEN -- if exists then set update flag else create flag / store it in OUT param
x_warranty_entl_tbl(i).warranty_entitlement_id := l_warranty_entl_id;
x_warranty_entl_tbl(i).operation_flag := AHL_WARRANTY_ENTL_PVT.G_OP_UPDATE;
END Get_Warranty_Rec_for_Update;
SELECT task1.visit_task_id, stage.STAGE_ID, stage.STAGE_NAME
FROM AHL_VWP_STAGES_VL stage, AHL_VISIT_TASKS_B task1
WHERE task1.stage_id = stage.stage_id
AND task1.visit_task_id IN ( SELECT task.visit_task_id
FROM AHL_VISIT_TASKS_B task
WHERE task.task_type_code IN ('PLANNED','UNPLANNED')
AND task.status_code = 'PLANNING'
START WITH task.visit_task_id = p_task_id
CONNECT BY PRIOR task.visit_task_id = task.originating_task_id)
ORDER BY visit_task_id;
SELECT stage_id
FROM ahl_vwp_stages_b
WHERE visit_id = p_visit_id
AND stage_status_code <> 'RELEASED';
SELECT visit_task_id, visit_task_number
FROM ahl_visit_tasks_b
WHERE stage_id = p_stage_id
AND task_type_code IN ('PLANNED', 'UNPLANNED')
AND status_code NOT IN ('DELETED','RELEASED');
SELECT route.route_no,
route.route_type_code,
kfv.concatenated_segments,
route.process_code
FROM ahl_visit_tasks_b tsk, ahl_routes_b route, ahl_routes_b_kfv kfv, ahl_mr_routes mrt
WHERE tsk.mr_route_id = mrt.mr_route_id
AND mrt.route_id = route.route_id
AND tsk.visit_task_id = p_task_id
AND kfv.route_id = route.route_id;
SELECT rule_id
FROM ahl_wbs_rules
WHERE NVL(PRE_ROUTE_NUMBER,'X') = NVL2(PRE_ROUTE_NUMBER,p_route_no, 'X')
AND NVL(PRE_ROUTE_TYPE_CODE,'X') = NVL2(PRE_ROUTE_TYPE_CODE,p_route_type, 'X')
AND NVL(PRE_SYSTEM_CODE,'X') = NVL2(PRE_SYSTEM_CODE,p_system_code, 'X')
AND NVL(PRE_PROCESS_CODE,'X') = NVL2(PRE_PROCESS_CODE,p_process_code, 'X');
SELECT POST_ROUTE_NUMBER, POST_ROUTE_TYPE_CODE, POST_SYSTEM_CODE, POST_PROCESS_CODE
FROM ahl_wbs_rules
WHERE RULE_ID = p_rule_id;
SELECT tsk.visit_task_id, tsk.visit_task_number
FROM ahl_visit_tasks_b tsk, ahl_routes_b route, ahl_routes_b_kfv kfv, ahl_mr_routes assoc
WHERE kfv.route_id = route.route_id
AND NVL2(p_route_no, route.ROUTE_NO,'X') = NVL(p_route_no, 'X')
AND NVL2(p_route_type,route.ROUTE_TYPE_CODE,'X') = NVL(p_route_type, 'X')
AND NVL2(p_system_code,kfv.CONCATENATED_SEGMENTS,'X') = NVL(p_system_code, 'X')
AND NVL2(p_process_code,route.PROCESS_CODE,'X') = NVL(p_process_code, 'X')
AND tsk.mr_route_id = assoc.mr_route_id
AND route.route_id = assoc.route_id
AND tsk.stage_id = p_stage_id
AND tsk.status_code <> 'DELETED';
SELECT 'X' FROM ahl_task_links
WHERE visit_task_id = p_visit_task_id
AND parent_task_id = p_parent_task_id;
'Before calling AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_UPDATES l_stage_rec.visit_task_id= '|| l_stage_rec.visit_task_id);
AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_UPDATES(
P_API_VERSION => 1.0,
P_VISIT_ID => p_visit_id,
P_VISIT_TASK_ID => l_stage_rec.visit_task_id,
P_STAGE_NAME => l_stage_rec.STAGE_NAME,
X_STAGE_ID => l_stage_rec.STAGE_ID,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data );
'After calling AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_UPDATES: Return Status = ' || l_return_status );
'in HTD api; Before calling UPDATE_STAGES_HIERARICHY');
AHL_VWP_VISITS_STAGES_PVT.UPDATE_STAGES_HIERARICHY (
p_api_version => 1.0,
p_init_msg_list => Fnd_Api.g_false,
p_commit => Fnd_Api.g_false,
p_validation_level => Fnd_Api.g_valid_level_full,
p_module_type => 'JSP',
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 UPDATE_STAGES_HIERARICHY and return status is:'||l_return_status);
SELECT ATSK.INSTANCE_ID,
ATSK.OBJECT_VERSION_NUMBER,
ATSK.SERVICE_REQUEST_ID,
ASWE.WARRANTY_ENTITLEMENT_ID,
AWCB.CONTRACT_NUMBER,
LKUP1.MEANING ENTITLEMENT_STATUS,
ASWE.ENTITLEMENT_STATUS_CODE,
AWCL.CLAIM_NAME,
LKUP2.MEANING CLAIM_STATUS,
AWCL.CLAIM_STATUS_CODE,
AHL_WARRANTY_ENTL_PVT.IS_TASK_WARRANTY_AVAILABLE(ATSK.VISIT_TASK_ID) TASK_WARRANTY,
AHL_WARRANTY_CONTRACTS_PVT.IS_INSTANCE_WARRANTY_AVAILABLE(ATSK.INSTANCE_ID) INSTANCE_WARRANTY,
'Y' WARRANTY_NOT_APPL_FLAG
FROM AHL_VISIT_TASKS_B ATSK,
AHL_WARRANTY_ENTITLEMENTS ASWE,
AHL_WARRANTY_CLAIMS_B AWCL,
AHL_WARRANTY_CONTRACTS_B AWCB,
FND_LOOKUP_VALUES_VL LKUP1,
FND_LOOKUP_VALUES_VL LKUP2
WHERE ATSK.VISIT_TASK_ID = ASWE.VISIT_TASK_ID(+)
AND ASWE.WARRANTY_CONTRACT_ID = AWCB.WARRANTY_CONTRACT_ID(+)
AND ASWE.WARRANTY_CLAIM_ID = AWCL.WARRANTY_CLAIM_ID(+)
AND LKUP1.LOOKUP_TYPE(+) = 'AHL_WARRANTY_ENTLMNT_STATUS'
AND lkup1.lookup_code(+) = ASWE.entitlement_status_code
AND LKUP2.LOOKUP_TYPE(+) = 'AHL_WARRANTY_CLAIM_STATUS'
AND LKUP2.LOOKUP_CODE(+) = AWCL.CLAIM_STATUS_CODE
AND NVL(ATSK.STATUS_CODE,'X') <> 'DELETED'
AND ATSK.VISIT_TASK_ID = p_task_id;
AHL_VWP_RULES_PVT.Get_Warranty_Rec_for_Update(
p_task_rec => l_task_tbl(i),
x_warranty_entl_tbl => l_entl_rec_tbl);
SELECT visit_id,
visit_task_number,
status_code,
task_type_code,
instance_id,
return_to_supply_flag current_rts
FROM ahl_visit_tasks_b
WHERE visit_task_id = c_visit_task_id;
SELECT count(visit_task_id) rts_count
FROM ahl_visit_tasks_b
WHERE (status_code = 'PLANNING' OR status_code = 'RELEASED')
AND return_to_supply_flag = 'Y'
AND visit_id = c_visit_id
AND visit_task_id <> c_visit_task_id
AND instance_id = c_instance_id;
SELECT wip_entity_id FROM ahl_workorders
WHERE status_code IN ('1', '3', '6', '19', '20', '17')
--unreleased, released, on-hold, parts hold and pending QA approval(open workorders)
AND visit_task_id IN
(SELECT vt.visit_task_id FROM AHL_VISIT_TASKS_B vt
WHERE vt.cost_parent_id IS NOT NULL
AND NVL(vt.return_to_supply_flag,'N') = 'Y'
AND vt.instance_id = p_instance_id
START WITH vt.visit_task_id = p_rpr_batch_task_id
CONNECT BY PRIOR vt.visit_task_id = vt.cost_parent_id
);
SELECT repair_batch_name, visit_task_id FROM ahl_visit_tasks_b WHERE
repair_batch_name IS NOT NULL
START WITH visit_task_id = p_task_id_csr
CONNECT BY PRIOR cost_parent_id = visit_task_id;
UPDATE AHL_VISIT_TASKS_B
SET 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,
return_to_supply_flag = 'Y'
WHERE visit_task_id = p_task_tbl_type(i).visit_task_id
AND object_version_number = p_task_tbl_type(i).object_version_number;
SELECT visit_task_number,
status_code,
task_type_code,
return_to_supply_flag current_rts
FROM ahl_visit_tasks_b
WHERE visit_task_id = c_visit_task_id;
UPDATE AHL_VISIT_TASKS_B
SET 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,
return_to_supply_flag = 'N'
WHERE visit_task_id = p_task_tbl_type(i).visit_task_id
AND object_version_number = p_task_tbl_type(i).object_version_number;
SELECT CSIS.instance_number
FROM ahl_visit_tasks_b ATSK,
csi_item_instances CSIS
WHERE ATSK.instance_id = CSIS.instance_id
AND ATSK.status_code = 'PLANNING'
AND ATSK.return_to_supply_flag = 'Y'
AND ATSK.visit_id = c_visit_id
GROUP BY CSIS.instance_number
HAVING COUNT(CSIS.instance_number) > 1;
l_query_str := ' SELECT CSIS.instance_number ' ||
' FROM ahl_visit_tasks_b ATSK, ' ||
' csi_item_instances CSIS ' ||
' WHERE ATSK.instance_id = CSIS.instance_id ' ||
' AND ATSK.return_to_supply_flag = ''Y'' ' ||
' AND ATSK.status_code = ''PLANNING'' ' ||
' AND ATSK.visit_task_id IN (' || taskStr || ') ' ||
' GROUP BY CSIS.instance_number ' ||
' HAVING COUNT(CSIS.instance_number) > 1 ';
SELECT 'X'
FROM AHL_VISIT_TASKS_B TSK
WHERE TSK.VISIT_TASK_ID = c_task_id
AND TSK.STATUS_CODE = 'PLANNING'
AND TSK.TASK_TYPE_CODE IN ('PLANNED', 'UNPLANNED');
SELECT 'X' FROM AHL_TASK_LINKS
WHERE parent_task_id = c_task_id
OR visit_task_id = c_task_id;
SELECT 'X'
FROM AHL_VISIT_TASKS_B TSK,
AHL_ROUTES_B ROUTE,
AHL_MR_ROUTES AMRR
WHERE TSK.VISIT_TASK_ID = c_task_id
AND NVL(TSK.RETURN_TO_SUPPLY_FLAG, 'X') <> 'Y'
AND AMRR.MR_ROUTE_ID = TSK.MR_ROUTE_ID
AND AMRR.ROUTE_ID = ROUTE.ROUTE_ID
AND NVL(ROUTE.DUPLICATE_FLAG, 'X') = 'Y';
SELECT 'X'
FROM AHL_VISIT_TASKS_B TSK1,
AHL_ROUTES_B ROUTE,
AHL_MR_ROUTES AMRR1
WHERE TSK1.VISIT_TASK_ID = c_task_id
AND TSK1.STATUS_CODE = 'PLANNING'
AND TSK1.TASK_TYPE_CODE IN ('PLANNED', 'UNPLANNED')
AND TSK1.MR_ROUTE_ID = AMRR1.MR_ROUTE_ID
AND AMRR1.ROUTE_ID = ROUTE.ROUTE_ID
AND ROUTE.DUPLICATE_FLAG = 'Y'
AND EXISTS
(SELECT 'X'
FROM AHL_VISIT_TASKS_B TSK2,
AHL_MR_ROUTES AMRR2
WHERE TSK2.VISIT_ID = TSK1.VISIT_ID
AND TSK2.STATUS_CODE IN ('RELEASED', 'PLANNING')
AND TSK2.MR_ROUTE_ID = AMRR2.MR_ROUTE_ID
AND AMRR2.ROUTE_ID = ROUTE.ROUTE_ID
AND TSK2.VISIT_TASK_ID <> TSK1.VISIT_TASK_ID
AND TSK2.TASK_TYPE_CODE IN ('PLANNED', 'UNPLANNED')
);
SELECT 'X'
FROM AHL_VISIT_TASKS_B TSK
WHERE TSK.VISIT_ID IN
(SELECT VISIT_ID FROM AHL_VISIT_TASKS_B
WHERE VISIT_TASK_ID = c_task_id)
AND TSK.VISIT_TASK_ID <> c_task_id
AND TSK.STATUS_CODE IN ('PLANNING', 'RELEASED')
AND TSK.TASK_TYPE_CODE IN ('PLANNED', 'UNPLANNED')
START WITH TSK.VISIT_TASK_ID IN
(SELECT TSK1.VISIT_TASK_ID
FROM AHL_VISIT_TASKS_B TSK1
WHERE TSK1.ORIGINATING_TASK_ID IS NULL
START WITH TSK1.VISIT_TASK_ID = c_task_id
CONNECT BY PRIOR TSK1.ORIGINATING_TASK_ID = TSK1.VISIT_TASK_ID
)
CONNECT BY PRIOR TSK.VISIT_TASK_ID = TSK.ORIGINATING_TASK_ID;
SELECT TSK.OBJECT_VERSION_NUMBER
FROM AHL_VISIT_TASKS_B TSK
WHERE TSK.VISIT_TASK_ID = c_task_id
AND TSK.STATUS_CODE <> 'DELETED';
Fnd_Message.SET_NAME('AHL','AHL_VWP_TASK_DELETED'); -- // The task has been deleted.
UPDATE AHL_VISIT_TASKS_B
SET TASK_TYPE_CODE = 'DUPLICATE' ,
LAST_UPDATE_DATE = SYSDATE ,
LAST_UPDATED_BY = Fnd_Global.USER_ID ,
OBJECT_VERSION_NUMBER = p_task_rec_tbl(i).object_version_number + 1,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE VISIT_TASK_ID = p_task_rec_tbl(i).Visit_Task_Id;
SELECT 'X'
FROM AHL_VISIT_TASKS_B TSK
WHERE TSK.VISIT_TASK_ID = c_task_id --cursor param
AND TSK.STATUS_CODE = 'PLANNING'
AND TSK.TASK_TYPE_CODE = 'DUPLICATE';
SELECT 'X'
FROM AHL_VISIT_TASKS_B TSK
WHERE TSK.STATUS_CODE = 'RELEASED'
START WITH TSK.VISIT_TASK_ID = (SELECT ORIGINATING_TASK_ID
FROM AHL_VISIT_TASKS_B
WHERE VISIT_TASK_ID = c_task_id)
CONNECT BY PRIOR TSK.VISIT_TASK_ID = TSK.ORIGINATING_TASK_ID;
CURSOR update_visit_task_csr (c_task_id NUMBER)
IS
SELECT TSK.OBJECT_VERSION_NUMBER,
TSK.SERVICE_REQUEST_ID,
AUEF.MANUALLY_PLANNED_FLAG
FROM AHL_VISIT_TASKS_B TSK,
AHL_UNIT_EFFECTIVITIES_B AUEF
WHERE TSK.VISIT_TASK_ID = c_task_id
AND TSK.UNIT_EFFECTIVITY_ID = AUEF.UNIT_EFFECTIVITY_ID;
update_task_rec update_visit_task_csr%ROWTYPE;
OPEN update_visit_task_csr(p_task_rec_tbl(i).Visit_Task_Id);
FETCH update_visit_task_csr INTO update_task_rec;
IF update_visit_task_csr%NOTFOUND THEN
CLOSE update_visit_task_csr;
IF ((update_task_rec.MANUALLY_PLANNED_FLAG is NULL) OR (update_task_rec.MANUALLY_PLANNED_FLAG = 'N')) THEN
l_task_type_code := 'PLANNED';
ELSIF ((update_task_rec.MANUALLY_PLANNED_FLAG = 'Y') AND (update_task_rec.SERVICE_REQUEST_ID is not NULL)) THEN
l_task_type_code := 'PLANNED'; --NR Task
ELSIF ((update_task_rec.MANUALLY_PLANNED_FLAG = 'Y') AND (update_task_rec.SERVICE_REQUEST_ID is NULL)) THEN
l_task_type_code := 'UNPLANNED';
IF (update_task_rec.object_version_number > p_task_rec_tbl(i).object_version_number) THEN
Fnd_Message.SET_NAME('AHL','AHL_TASK_RECORD_CHANGED'); -- // Record has been changed
UPDATE AHL_VISIT_TASKS_B
SET TASK_TYPE_CODE = l_task_type_code ,
LAST_UPDATE_DATE = SYSDATE ,
LAST_UPDATED_BY = Fnd_Global.USER_ID ,
OBJECT_VERSION_NUMBER = p_task_rec_tbl(i).object_version_number + 1,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE VISIT_TASK_ID = p_task_rec_tbl(i).Visit_Task_Id;
SELECT visit_task_id, visit_id
FROM ahl_visit_tasks_b
WHERE unit_effectivity_id = c_UE_id
AND status_code IN ( 'PLANNING', 'PARTIALLY RELEASED')
AND task_type_code = 'SUMMARY';
SELECT 'N'
FROM ahl_visits_b visit, ahl_visit_tasks_b task
WHERE visit.visit_id = c_visit_id
AND visit.visit_id = task.visit_id
AND task.status_code = 'PLANNING';
fnd_log.string(l_log_statement,L_DEBUG,'Before call to Delete_Task');
AHL_VWP_TASKS_PVT.Delete_Task (
p_api_version => p_api_version,
p_init_msg_list => Fnd_Api.g_false,
p_commit => Fnd_Api.g_false,
p_Visit_Task_Id => l_summary_task_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,'After call to Delete_Task: l_return_status:'||l_return_status||' l_msg_count:'||l_msg_count);
UPDATE ahl_visits_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_id = l_visit_id;