[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,
p_quantity IN NUMBER := NULL, -- Added by rnahata for Issue 105
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 substr(title,1,80), description /*Bug 5758813 - Fetch the description.*/
FROM ahl_unit_effectivities_v
WHERE unit_effectivity_id = c_unit_eff_id;
SELECT substr(ar.title,1,80), ar.remarks
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;
'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,
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 => p_stage_id,
-- Added cxcheng POST11510--------------
X_START_DATE_TIME => NULL,
X_END_DATE_TIME => NULL,
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';
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
)
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
);
'After inserting record into AHL_TASK_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;
'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,
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,
-- Added by rnahata for Issue 105 - pass the quantity for the simple tasks
p_quantity => p_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);
'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,
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;