The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT workorder_id,wip_entity_id, visit_id
FROM ahl_workorders
WHERE visit_id = C_VISIT_ID AND VISIT_TASK_ID IS NULL
AND master_workorder_flag = 'Y'
AND STATUS_CODE NOT IN ('22','7');
SELECT visit_task_id,cost_parent_id,level,
task_type_code,originating_task_id
FROM ahl_visit_tasks_b
WHERE visit_id = C_VISIT_ID
AND NVL(status_code,'Y') <> 'DELETED'
START WITH cost_parent_id IS NULL
-- SKPATHAK :: Bug 13574991 :: 16-FEB-2012 :: FPed all fixes on R121 codeline
--SKPATHAK :: 18-APR-2011 :: Bug 12339739 :: Added the below condition to start with clause so as to improve performance
AND visit_id = C_VISIT_ID
CONNECT BY PRIOR visit_task_id = cost_parent_id
ORDER BY LEVEL;
SELECT workorder_id,wip_entity_id,visit_task_id
FROM ahl_workorders
WHERE visit_task_id = C_VISIT_TASK_ID
-- AND STATUS_CODE NOT IN ('22','7');
ORDER BY LAST_UPDATE_DATE DESC;
'Before Calling Insert Cst Wo Hierarchy ' ||l_cst_job_tbl.count);
Insert_Cst_Wo_Hierarchy (
p_cst_job_tbl => l_cst_job_tbl,
p_commit => p_commit,
x_session_id => x_cost_session_id,
x_return_status => l_return_status);
'After Calling Insert cost workorder hierarchy, Return Status = ' || l_return_status ||
'Session id :' ||x_cost_session_id);
'Errors from Insert cost workorder hierarchy' || x_msg_count);
SELECT wip_entity_id
FROM AHL_WORKORDERS
WHERE visit_id = c_visit_id
AND visit_task_id IS NULL
AND STATUS_CODE NOT IN ('22','7')
AND master_workorder_flag = 'Y';
SELECT parent_object_id,
child_object_id,
level
FROM WIP_SCHED_RELATIONSHIPS
WHERE parent_object_type_id = 1
AND child_object_type_id = 1
AND not exists (select 1 from ahl_workorders awo1
where awo1.wip_entity_id = parent_object_id
and ( awo1.status_code ='22'
OR (awo1.status_code ='7'
AND awo1.LAST_UPDATE_DATE <> (select MAX(LAST_UPDATE_DATE)
from ahl_workorders awo2
where visit_task_id = awo1.visit_task_id)))
)
AND not exists (select 1 from ahl_workorders awo1
where awo1.wip_entity_id = child_object_id
and ( awo1.status_code ='22'
OR (awo1.status_code ='7'
AND awo1.LAST_UPDATE_DATE <> (select MAX(LAST_UPDATE_DATE)
from ahl_workorders awo2
where visit_task_id = awo1.visit_task_id)))
)
START WITH parent_object_id = c_wip_entity_id
AND relationship_type = 1
CONNECT BY parent_object_id = PRIOR child_object_id
AND relationship_type = 1
ORDER BY level;
'Before Calling Insert_Cst_Wo_Hierarchy');
Insert_Cst_Wo_Hierarchy
(
p_cst_job_tbl => l_cst_job_tbl,
p_commit => FND_API.G_FALSE,
x_session_id => x_MR_session_id,
x_return_status => l_return_status
);
'After Calling Insert_Cst_Wo_Hierarchy, Return Status = ' || l_return_status);
'Errors from Insert cost workorder hierarchy' || x_msg_count);
PROCEDURE Insert_Cst_Wo_Hierarchy (
p_cst_job_tbl IN Cst_Job_Tbl,
p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
x_session_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Cst_Wo_Hierarchy';
SELECT MTL_EAM_ASSET_ACTIVITIES_S.nextval INTO l_group_id
FROM DUAL;
'Call INSERT CST_EAM_HIERARCHY_SNAPSHOT ');
INSERT INTO CST_EAM_HIERARCHY_SNAPSHOT(
GROUP_ID,
OBJECT_ID,
OBJECT_TYPE,
PARENT_OBJECT_ID,
PARENT_OBJECT_TYPE ,
LEVEL_NUM,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
LAST_UPDATE_LOGIN)
VALUES
(
l_group_id,
l_cst_job_tbl(i).object_id,
l_cst_job_tbl(i).object_type,
l_cst_job_tbl(i).parent_object_id,
l_cst_job_tbl(i).parent_object_type,
l_cst_job_tbl(i).level_num,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
null,
l_cst_job_tbl(i).program_application_id,
fnd_global.login_id
);
END Insert_Cst_Wo_Hierarchy;
SELECT AWO.WIP_ENTITY_ID
FROM AHL_WORKORDERS AWO
WHERE AWO.VISIT_ID = x_id
AND VISIT_TASK_ID IS NULL
AND AWO.STATUS_CODE NOT IN ('22','7')
AND AWO.MASTER_WORKORDER_FLAG = 'Y';
SELECT AVTB.VISIT_TASK_ID, AWV.WORKORDER_ID
FROM AHL_VISIT_TASKS_B AVTB, AHL_WORKORDERS AWV
WHERE AVTB.VISIT_TASK_ID = AWV.VISIT_TASK_ID
AND AVTB.VISIT_ID = x_id
AND NVL(AVTB.status_code, 'Y') <> NVL ('DELETED', 'X')
-- AND AWV.STATUS_CODE NOT IN ('22','7')
AND AWV.STATUS_CODE <> '22'
AND not (AVTB.MR_ID is Null and AVTB.task_type_code='SUMMARY')
ORDER BY AWV.LAST_UPDATE_DATE DESC;
SELECT * FROM AHL_OSP_ORDER_LINES
WHERE WORKORDER_ID = x_id;
SELECT * FROM AHL_VISIT_TASKS_B
WHERE VISIT_TASK_ID = x_id;
SELECT AWO.WIP_ENTITY_ID
FROM AHL_WORKORDERS AWO
WHERE AWO.VISIT_TASK_ID = x_id
-- AND AWO.STATUS_CODE NOT IN ('22','7')
AND AWO.STATUS_CODE <>'22'
AND AWO.MASTER_WORKORDER_FLAG = 'Y'
ORDER BY LAST_UPDATE_DATE DESC;
SELECT visit_task_id
FROM ahl_visit_tasks_b
WHERE mr_id is not null
START WITH visit_task_id = x_id
CONNECT BY PRIOR visit_task_id = originating_task_id;
SELECT AWV.WORKORDER_ID
FROM AHL_WORKORDERS AWV
WHERE AWV.VISIT_TASK_ID = x_id
-- AND AWO.STATUS_CODE NOT IN ('22','7')
AND AWV.STATUS_CODE <>'22'
ORDER BY LAST_UPDATE_DATE DESC;
SELECT * FROM AHL_OSP_ORDER_LINES
WHERE WORKORDER_ID = x_id;
SELECT * FROM AHL_VISIT_TASKS_B
WHERE VISIT_TASK_ID = x_id;
SELECT AWV.WORKORDER_ID, AWV.wip_entity_id
FROM AHL_WORKORDERS AWV
WHERE AWV.VISIT_TASK_ID = x_id
-- AND AWV.STATUS_CODE NOT IN ('22','7')
AND AWV.STATUS_CODE <>'22'
ORDER BY LAST_UPDATE_DATE DESC;
SELECT * FROM AHL_OSP_ORDER_LINES
WHERE WORKORDER_ID = x_id;
SELECT visit_task_id
FROM ahl_visit_tasks_b
START WITH visit_task_id = x_id
CONNECT BY PRIOR visit_task_id = cost_parent_id;
SELECT * FROM AHL_VISIT_TASKS_B
WHERE VISIT_TASK_ID = x_id;
SELECT AWO.WIP_ENTITY_ID, AWO.WORKORDER_ID
FROM AHL_WORKORDERS AWO
WHERE AWO.VISIT_TASK_ID = x_id
-- AND AWO.STATUS_CODE NOT IN ('22','7')
AND AWO.STATUS_CODE <>'22'
AND AWO.MASTER_WORKORDER_FLAG = 'N'
ORDER BY LAST_UPDATE_DATE DESC;
SELECT * FROM AHL_OSP_ORDER_LINES
WHERE WORKORDER_ID = x_id;
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
SELECT visit_id FROM AHL_VISIT_TASKS_VL
WHERE VISIT_TASK_ID = x_T_id;
SELECT WORKORDER_ID FROM AHL_WORKORDERS
WHERE VISIT_ID = x_id
AND VISIT_TASK_ID IS NULL
AND STATUS_CODE NOT IN ('22','7')
AND MASTER_WORKORDER_FLAG = 'Y';
l_cost_price_rec.Is_Cst_Struc_updated := 'Y';
l_cost_price_rec.Is_Cst_Struc_updated := 'N';
p_x_cost_price_rec.Is_Cst_Struc_updated := l_cost_price_rec.Is_Cst_Struc_updated;
'Is_Cst_Struc_updated flag: ' || p_x_cost_price_rec.Is_Cst_Struc_updated ||
'Work order ID: ' || p_x_cost_price_rec.workorder_id );
SELECT * FROM AHL_VISIT_TASKS_VL
WHERE VISIT_TASK_ID = x_T_id;
l_cost_price_rec.Is_Cst_Struc_updated := 'N';
p_x_cost_price_rec.Is_Cst_Struc_updated := l_cost_price_rec.Is_Cst_Struc_updated;
/*SELECT OBJECT_ID, SUM (ACTUAL_COST) ACTUAL_COST,
SUM (ESTIMATED_COST) ESTIMATED_COST
FROM AHL_VWP_ROLLUP_COSTS_V
WHERE GROUP_ID = x_session_id
AND OBJECT_ID = x_id
AND PROGRAM_APPLICATION_ID = x_prg_id
GROUP BY OBJECT_ID; */
SELECT *
FROM AHL_VWP_ROLLUP_COSTS_V
WHERE GROUP_ID = x_session_id
AND OBJECT_ID = x_id
AND PROGRAM_APPLICATION_ID = x_prg_id;
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
SELECT wip_entity_id
FROM ahl_workorders
WHERE visit_id = x_id AND STATUS_CODE NOT IN ('22','12');
'Before Calling CST_EAMCOST_PUB.Delete_eamPerBal' );
CST_EAMCOST_PUB.Delete_eamPerBal (
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,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_entity_id_tab => l_entity_id_tab,
p_org_id => visit_rec.organization_id,
p_type => 1);
'After Calling CST_EAMCOST_PUB.Delete_eamPerBal, Return Status = ' || l_return_status );
'Errors from CST_EAMCOST_PUB.Delete_eamPerBal API -COST SESSION' || l_msg_count );
'Errors from CST_EAMCOST_PUB.Delete_eamPerBal API -COST SESSION' || l_msg_count );