[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT status_code
FROM ahl_visits_b
WHERE visit_id = p_visit_id;
SELECT vt.visit_id,
vt.visit_task_id,
vt.estimated_price,
vt.actual_price,
vt.price_list_id,
vt.mr_route_id,
vt.mr_id,
vs.outside_party_flag,
vs.start_date_time,
vs.close_date_time,
ci.customer_id
FROM ahl_visit_tasks_b vt,
ahl_visits_b vs,
cs_incidents_all_b ci
WHERE vt.visit_task_id = C_VISIT_TASK_ID
AND vt.visit_id=vs.visit_id
AND vs.service_request_id = ci.incident_id(+)
AND NVL(vt.status_code, 'Y') <> NVL ('DELETED', 'X');
SELECT name
FROM qp_list_headers
WHERE list_header_id = C_LIST_HEADER_ID;
/* SELECT mh.billing_item_id,mh.billing_item, mh.title
FROM ahl_mr_routes_v mr, ahl_mr_headers_v mh
WHERE mr.mr_header_id = mh.mr_header_id
AND mr.mr_header_id =C_MR_ID;*/
SELECT mh.billing_item_id,mtl.CONCATENATED_SEGMENTS billing_item, mh.title
FROM ahl_mr_routes_v mr, AHL_MR_HEADERS_VL mh,MTL_SYSTEM_ITEMS_KFV mtl
WHERE mr.mr_header_id = mh.mr_header_id
AND mtl.INVENTORY_ITEM_ID= MH.BILLING_ITEM_ID
AND mr.mr_header_id = C_MR_ID
AND mh.APPLICATION_USG_CODE=RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE'))) ;
SELECT VISIT_TASK_NUMBER
FROM ahl_visit_tasks_b
where visit_task_id = p_task_id;
'Is Cst Struc Updated Flag: '|| l_cost_price_rec.Is_Cst_Struc_updated
);
IF(l_cost_price_rec.Is_Cst_Struc_updated = 'N') AND (l_cost_price_rec.workorder_id IS NOT NULL) THEN
--Log message
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
fnd_log.string
(
fnd_log.level_statement,
'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
'Inside Cost Struc Updated flag = N, Workorder Id: '|| l_cost_price_rec.workorder_id
);
PROCEDURE Update_Task_Cost_Details (
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_cost_price_rec IN AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
-- Get visit task details
CURSOR Get_visit_task_dtls_cur (c_visit_task_id IN NUMBER)
IS
SELECT vt.price_list_id,
vt.visit_id,
vt.visit_task_id,
vt.object_version_number,
vt.visit_task_number,
vt.visit_task_name
FROM ahl_visit_tasks_vl vt,
cs_incidents_all_b ci
WHERE visit_task_id = c_visit_task_id
AND NVL(vt.status_code, 'Y') <> NVL ('DELETED', 'X');
SELECT vs.visit_id,
vs.start_date_time,
vs.close_date_time,
vs.service_request_id,
ci.customer_id
FROM ahl_visits_vl vs,
cs_incidents_all_b ci
WHERE visit_id = c_visit_id
AND vs.service_request_id = ci.incident_id(+)
AND NVL(vs.status_code, 'Y') <> NVL ('DELETED', 'X');
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';
SELECT start_date_active,
end_date_active
FROM QP_LIST_HEADERS
WHERE list_header_id = c_price_list_id;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Task_Cost_Details';
'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Update_Task_Cost_Details.begin',
'At the start of PLSQL procedure'
);
SAVEPOINT Update_Task_Cost_Details;
'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_task_cost_details',
'Price List is not active on visit start date'
);
'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_task_cost_details',
'Price List is not active on visit end date'
);
'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_task_cost_details',
'Price List is not active on current todays date'
);
' Before Update Ahl Visit Tasks B Table, Price List Id: ' || l_cost_price_rec.price_list_id
);
' Before Update Ahl Visit Tasks B Table, Estimated Price: ' || l_cost_price_rec.estimated_price
);
' Before Update Ahl Visit Tasks B Table, Actual Price: ' || l_cost_price_rec.actual_price
);
' Before Update Ahl Visit Tasks B Table, Actual Cost: ' || l_cost_price_rec.actual_cost
);
UPDATE AHL_VISIT_TASKS_B SET
PRICE_LIST_ID = l_cost_price_rec.price_list_id,
ESTIMATED_PRICE = l_cost_price_rec.estimated_price,
ACTUAL_PRICE = l_cost_price_rec.actual_price,
ACTUAL_COST = l_cost_price_rec.actual_cost,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID,
OBJECT_VERSION_NUMBER = l_cost_price_rec.object_version_number + 1
WHERE VISIT_TASK_ID = l_cost_price_rec.visit_task_id;
'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Update_Task_Cost_Details.end',
'At the end of PLSQL procedure'
);
ROLLBACK TO Update_Task_Cost_Details;
ROLLBACK TO Update_Task_Cost_Details;
ROLLBACK TO Update_Task_Cost_Details;
p_procedure_name => 'Update_Task_Cost_Details',
p_error_text => SUBSTR(SQLERRM,1,500));
END Update_Task_Cost_Details;
SELECT vt.visit_id,
vt.visit_task_id,
vs.any_task_chg_flag
FROM ahl_visit_tasks_b vt,
ahl_visits_b vs
WHERE vt.visit_task_id = C_VISIT_TASK_ID
AND vt.visit_id = vs.visit_id
AND NVL(vt.status_code, 'Y') <> NVL ('DELETED', 'X');
'Is Cst Struc updated Flag: ' || l_cost_price_rec.Is_Cst_Struc_updated
);
p_x_cost_price_rec.Is_Cst_Struc_updated := l_cost_price_rec.Is_Cst_Struc_updated;
SELECT vs.visit_id,
vs.visit_number,
vs.actual_price visit_actual_price,
vs.estimated_price visit_estimated_price,
vs.object_version_number visit_object_version_number,
vs.organization_id,
vs.any_task_chg_flag,
nvl(vt.price_list_id, vs.price_list_id) price_list_id,
vt.visit_task_id,
vt.visit_task_number,
vt.object_version_number task_object_version_number,
vt.actual_price task_actual_price,
vt.estimated_price task_estimated_price,
vt.mr_id,
vt.task_type_code,
mr_route_id,
vt.originating_task_id,
vt.service_request_id,
cs.customer_id,
vt.start_date_time, --Post11510 cxcheng added
vt.end_date_time
FROM ahl_visits_vl vs,
ahl_visit_tasks_vl vt,
cs_incidents_all_b cs
WHERE vs.visit_id = vt.visit_id
AND vs.service_request_id = cs.incident_id(+)
AND vt.visit_task_id = C_VISIT_TASK_ID
AND NVL(vt.status_code, 'Y') <> NVL ('DELETED', 'X');
SELECT distinct(vt.visit_task_id) visit_task_id,
vt.object_version_number,
vt.visit_task_number,
vt.mr_id,
vt.mr_route_id,
vt.actual_price,
vt.estimated_price
FROM ahl_visit_tasks_b vt
WHERE visit_id = C_VISIT_ID
AND NVL(vt.status_code, 'Y') <> NVL ('DELETED', 'X')
START WITH visit_task_id = C_ORIG_TASK_ID
CONNECT BY PRIOR originating_task_id = visit_task_id;
p_x_cost_price_rec.Is_Cst_Struc_updated := l_cost_price_rec.Is_Cst_Struc_updated;
SELECT ROUTE_ID INTO l_route_id
FROM ahl_mr_routes_v
WHERE mr_route_id = l_visit_task_dtls_rec.mr_route_id;
UPDATE ahl_visit_tasks_b
SET actual_price = l_parent_task_rec.actual_price,
estimated_price = l_parent_task_rec.estimated_price,
object_version_number = l_parent_task_rec.object_version_number + 1
WHERE visit_task_id = l_parent_task_rec.visit_task_id;
UPDATE ahl_visit_tasks_b
SET actual_price = l_cost_price_rec.actual_price,
estimated_price = l_cost_price_rec.estimated_price,
object_version_number = l_parent_task_rec.object_version_number + 1
WHERE visit_task_id = l_parent_task_rec.visit_task_id;
-- Update Visit cost details with new values
UPDATE AHL_VISITS_B
SET actual_price = l_visit_task_dtls_rec.visit_actual_price,
estimated_price = l_visit_task_dtls_rec.visit_estimated_price,
object_version_number = l_visit_task_dtls_rec.visit_object_version_number + 1
WHERE visit_id = l_visit_task_dtls_rec.visit_id;
-- Update task cost details with new values
UPDATE AHL_VISIT_TASKS_B
SET actual_price = l_cost_price_rec.actual_price,
estimated_price = l_cost_price_rec.estimated_price,
object_version_number = l_visit_task_dtls_rec.task_object_version_number + 1
WHERE visit_task_id = l_visit_task_dtls_rec.visit_task_id;
SELECT status_code
FROM ahl_visits_b
WHERE visit_id = c_visit_id;
IF (l_cost_price_rec.Is_Cst_Struc_updated = 'N') AND (l_cost_price_rec.workorder_id IS NOT NULL) THEN
IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
fnd_log.string
(
fnd_log.level_procedure,
'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
'Before call to AHL_VWP_COST_PVT.calculate_task_cost'
);
Select visit_task_id,
task_type_code,
mr_id,
visit_id,
price_list_id,
service_request_id,
start_date_time,
end_date_time
from ahl_visit_tasks_vl
where visit_task_id=c_visit_task_id;
Select visit_id,
price_list_id,
outside_party_flag,
service_request_id,
organization_id,
any_task_chg_flag
from ahl_visits_vl
where visit_id=c_visit_id;
select Customer_id
from CS_INCIDENTS_ALL_B
where incident_id=c_sr_req_id;
Select mr_name,
mr_description,
task_number,
task_name
from AHL_SEARCH_VISIT_TASK_V
Where TASK_ID=C_VISIT_TASK_ID;
select mrb.title mr_name,
mrtl.description mr_description,
visit_task_number task_number,
visit_task_name task_name
from ahl_visit_tasks_vl tsk,
ahl_mr_headers_tl mrtl,
ahl_mr_headers_b mrb,
ahl_mr_routes mrr,
ahl_visits_vl avts
where tsk.mr_route_id = mrr.mr_route_id(+)
and mrr.mr_header_id = mrb.mr_header_id(+)
and mrb.mr_header_id = mrtl.mr_header_id (+)
and mrtl.language(+) = USERENV('LANG')
and nvl(tsk.status_code,'X') <> 'DELETED'
and avts.visit_id = tsk.visit_id
and avts.template_flag = 'N'
and tsk.task_type_code <> 'SUMMARY'
and visit_task_id=c_visit_task_id
UNION
select title mr_name,
mrh.description mr_description,
visit_task_number task_number,
visit_task_name task_name
from ahl_visit_tasks_vl tsk,
ahl_mr_headers_vl mrh,
AHL_VISITS_VL AVTS
where MRH.MR_HEADER_ID = TSK.MR_ID
AND NVL(TSK.STATUS_CODE,'X') <> 'DELETED'
AND AVTS.VISIT_ID = TSK.VISIT_ID
AND AVTS.TEMPLATE_FLAG = 'N'
AND VISIT_TASK_ID=C_VISIT_TASK_ID;
Select CONCATENATED_SEGMENTS,DESCRIPTION,INVENTORY_ORG_ID,organization_name
FROM AHL_MTL_ITEMS_OU_V
WHERE INVENTORY_ITEM_ID=C_ITEM_ID
AND INVENTORY_ORG_ID=C_ORG_ID;
SELECT mtl.CONCATENATED_SEGMENTS,
mtl.DESCRIPTION,
mtl.organization_id INVENTORY_ORG_ID,
hou.name organization_name
FROM mtl_system_items_kfv mtl,hr_organization_units hou,inv_organization_info_v org
WHERE mtl.organization_id = org.organization_id
AND hou.organization_id = org.organization_id
AND NVL (org.operating_unit, mo_global.get_current_org_id ()) =mo_global.get_current_org_id()
AND mtl.inventory_item_id=c_item_id
AND mtl.organization_id=c_org_id;
Select a.mr_route_id,a.mr_id,b.organization_id
From AHL_VISIT_TASKS_B a,ahl_visits_b b
Where a.visit_task_id=c_visit_task_id
and a.visit_id=b.visit_id;
Select mr_route_id,route_id
From ahl_mr_Routes_v
where mr_route_id=c_mr_route_id;