[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE UPDATE_STAGE_DATES (
p_visit_id IN NUMBER,-- PRAKKUM :: 13-FEB-2011 :: Bug 13711800
p_base_stage_id IN NUMBER,
p_visit_start_date IN DATE,
p_dept_id IN NUMBER,
p_valid_stages IN Stage_Details ,
p_x_stages_ovn_tbl IN OUT NOCOPY Visit_Stages_OVN_Tbl_Type,--VWPE 12730539:: PRAKKUM :: 08-JUL-2011
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
);
PROCEDURE UPDATE_SUCC_STG_START_DATES(
p_stage_id IN NUMBER,
p_visit_id IN NUMBER,
p_planned_end_date IN DATE,
p_x_stages_ovn_tbl IN OUT NOCOPY Visit_Stages_OVN_Tbl_Type,--VWPE 12730539:: PRAKKUM :: 08-JUL-2011
p_caller_id IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
);
PROCEDURE UPDATE_STAGE_DEPENDENCY_RULES (
p_stage_id IN NUMBER,
p_visit_id IN NUMBER,
p_x_stages_ovn_tbl IN OUT NOCOPY Visit_Stages_OVN_Tbl_Type,--VWPE 12730539:: PRAKKUM :: 08-JUL-2011
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
);
PROCEDURE UPDATE_STAGES_OVN_COUNT(
p_stage_id IN NUMBER,
p_updated_stage_id IN NUMBER,
p_x_stages_ovn_tbl IN OUT NOCOPY Visit_Stages_OVN_Tbl_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
);
SELECT START_DATE_TIME , department_id FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
select s.stage_id, s.stage_num, s.stage_name,
s.object_version_number, s.duration
from ahl_vwp_stages_vl s
where s.visit_id = c_visit_id
order by s.stage_num;
SELECT S.stage_id
,S.stage_num
,S.stage_name
,S.object_version_number
,S.duration
,S.attribute_category
,S.attribute1
,S.attribute2
,S.attribute3
,S.attribute4
,S.attribute5
,S.attribute6
,S.attribute7
,S.attribute8
,S.attribute9
,S.attribute10
,S.attribute11
,S.attribute12
,S.attribute13
,S.attribute14
,S.attribute15
FROM ahl_vwp_stages_vl s
WHERE s.visit_id = c_visit_id
ORDER BY s.stage_num;
select vt.stage_id,
--sum(s.duration) over(order by s.stage_num) CUMUL_DURATION,
--min(vt.start_date_time) start_date_time,
max(vt.end_date_time) end_date_time
from ahl_visit_tasks_b vt
where vt.stage_id = C_STAGE_ID
AND nvl(vt.status_code,'X') <> 'DELETED'
group by vt.stage_id;
SELECT Ahl_vwp_stages_B_S.NEXTVAL
FROM dual;
SELECT 1
FROM Ahl_vwp_stages_b
WHERE stage_id = x_id;
SELECT START_DATE_TIME , department_id, visit_type_code, AHL_VWP_VISITS_PVT.Is_Old_Visit(VISIT_ID) IS_OLD_VISIT
FROM AHL_VISITS_VL
WHERE VISIT_ID = vst_id;
SELECT 1
FROM AHL_VWP_STAGES_B
WHERE Visit_Id = V_ID AND stage_num = STG_NO;
AHL_DEBUG_PUB.Debug( l_full_name ||':Insert');
Ahl_VWP_Stages_Pkg.Insert_Row (
X_ROWID => l_rowid,
X_VISIT_ID => P_VISIT_ID,
X_STAGE_ID => l_stages_tbl(i).STAGE_ID,
X_STAGE_NUM => l_stages_tbl(i).Stage_Num,
X_STAGE_NAME => l_stages_tbl(i).Stage_Name,
X_DURATION => l_stages_tbl(i).Duration,
X_OBJECT_VERSION_NUMBER => l_stages_tbl(i).OBJECT_VERSION_NUMBER,
X_ATTRIBUTE_CATEGORY => l_stages_tbl(i).ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => l_stages_tbl(i).ATTRIBUTE1 ,
X_ATTRIBUTE2 => l_stages_tbl(i).ATTRIBUTE2 ,
X_ATTRIBUTE3 => l_stages_tbl(i).ATTRIBUTE3 ,
X_ATTRIBUTE4 => l_stages_tbl(i).ATTRIBUTE4 ,
X_ATTRIBUTE5 => l_stages_tbl(i).ATTRIBUTE5 ,
X_ATTRIBUTE6 => l_stages_tbl(i).ATTRIBUTE6 ,
X_ATTRIBUTE7 => l_stages_tbl(i).ATTRIBUTE7 ,
X_ATTRIBUTE8 => l_stages_tbl(i).ATTRIBUTE8 ,
X_ATTRIBUTE9 => l_stages_tbl(i).ATTRIBUTE9 ,
X_ATTRIBUTE10 => l_stages_tbl(i).ATTRIBUTE10 ,
X_ATTRIBUTE11 => l_stages_tbl(i).ATTRIBUTE11 ,
X_ATTRIBUTE12 => l_stages_tbl(i).ATTRIBUTE12 ,
X_ATTRIBUTE13 => l_stages_tbl(i).ATTRIBUTE13 ,
X_ATTRIBUTE14 => l_stages_tbl(i).ATTRIBUTE14 ,
X_ATTRIBUTE15 => l_stages_tbl(i).ATTRIBUTE15 ,
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,
X_STAGE_STATUS_CODE => l_stages_tbl(i).STAGE_STATUS_CODE,--PRAKKUM :: 15/03/2011 :: VWPE :: ER 12424063
X_PLANNED_START_DATE => l_stages_tbl(i).Stage_Planned_Start_Time, --PRAKKUM :: 15/03/2011 :: VWPE :: ER 12424063
X_PLANNED_END_DATE => l_stages_tbl(i).Stage_Planned_End_Time,--PRAKKUM :: 15/03/2011 :: VWPE :: ER 12424063
X_PREV_STAGE_NUM => l_stages_tbl(i).PREV_STAGE_NUM,--PRAKKUM :: 15/03/2011 :: VWPE :: ER 12424063
X_EARLIEST_START_DATE => l_stages_tbl(i).EARLIEST_START_DATE );--PRAKKUM :: 15/03/2011 :: VWPE :: ER 12424063
PROCEDURE Update_Stages (
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_id IN NUMBER,
p_x_stages_tbl IN OUT NOCOPY Visit_Stages_Tbl_Type,
p_x_stages_ovn_tbl IN OUT NOCOPY Visit_Stages_OVN_Tbl_Type,--VWPE 12730539:: PRAKKUM :: 08-JUL-2011
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
-- Define local Variables
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'Update Stages';
SELECT status_code FROM AHL_VISITS_B
WHERE VISIT_ID = C_VISIT_ID
AND STATUS_CODE IN ('DRAFT','PLANNING', 'PARTIALLY RELEASED', 'RELEASED' );
select 'x' from ahl_workorders_v
where visit_task_id in
(select DISTINCT VISIT_TASK_ID from AHL_VISIT_TASKS_B
where visit_id = C_VISIT_ID
and STAGE_ID IN (SELECT STAGE_ID FROM AHL_VWP_STAGES_B WHERE stage_num > C_STAGE_NUM
AND VISIT_ID = C_VISIT_ID))
and ( job_status_code =3 or firm_planned_flag = 1 );
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_id = c_stage_id
AND visit_id = c_visit_id);
SELECT START_DATE_TIME , department_id, AHL_VWP_VISITS_PVT.Is_Old_Visit(VISIT_ID) IS_OLD_VISIT
FROM AHL_VISITS_B
WHERE VISIT_ID = c_visit_id;--VWPE 12730539:: PRAKKUM :: 08-JUL-2011
SELECT past_task_start_date, stage_id FROM AHL_VISIT_TASKS_B
WHERE visit_id = c_visit_id
AND past_task_start_date IS NOT NULL;
select * from ahl_vwp_stages_vl where stage_id = c_stage_id;
l_is_planned_end_date_updated BOOLEAN default false;
l_is_earliest_date_updated BOOLEAN default false;
SAVEPOINT Update_Stages;
AHL_DEBUG_PUB.Debug( l_full_name ||':Insert');
l_is_planned_end_date_updated := false;--PRAKKUM :: 15/03/2011 :: VWPE :: ER 12424063
l_is_earliest_date_updated := false;--PRAKKUM :: 15/03/2011 :: VWPE :: ER 12424063
l_is_planned_end_date_updated := true;
l_is_earliest_date_updated := true;
Ahl_VWP_stages_Pkg.Update_Row (
X_VISIT_ID => P_VISIT_ID,
X_STAGE_ID => p_x_stages_tbl(i).STAGE_ID,
X_STAGE_NUM => p_x_stages_tbl(i).STAGE_NUM,
X_STAGE_NAME => p_x_stages_tbl(i).STAGE_NAME,
X_DURATION => p_x_stages_tbl(i).DURATION,
X_OBJECT_VERSION_NUMBER => p_x_stages_tbl(i).OBJECT_VERSION_NUMBER+1,
X_ATTRIBUTE_CATEGORY => p_x_stages_tbl(i).ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => p_x_stages_tbl(i).ATTRIBUTE1,
X_ATTRIBUTE2 => p_x_stages_tbl(i).ATTRIBUTE2,
X_ATTRIBUTE3 => p_x_stages_tbl(i).ATTRIBUTE3,
X_ATTRIBUTE4 => p_x_stages_tbl(i).ATTRIBUTE4,
X_ATTRIBUTE5 => p_x_stages_tbl(i).ATTRIBUTE5,
X_ATTRIBUTE6 => p_x_stages_tbl(i).ATTRIBUTE6,
X_ATTRIBUTE7 => p_x_stages_tbl(i).ATTRIBUTE7,
X_ATTRIBUTE8 => p_x_stages_tbl(i).ATTRIBUTE8,
X_ATTRIBUTE9 => p_x_stages_tbl(i).ATTRIBUTE9,
X_ATTRIBUTE10 => p_x_stages_tbl(i).ATTRIBUTE10,
X_ATTRIBUTE11 => p_x_stages_tbl(i).ATTRIBUTE11,
X_ATTRIBUTE12 => p_x_stages_tbl(i).ATTRIBUTE12,
X_ATTRIBUTE13 => p_x_stages_tbl(i).ATTRIBUTE13,
X_ATTRIBUTE14 => p_x_stages_tbl(i).ATTRIBUTE14,
X_ATTRIBUTE15 => p_x_stages_tbl(i).ATTRIBUTE15,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID,
X_STAGE_STATUS_CODE => p_x_stages_tbl(i).STAGE_STATUS_CODE, -- PRAKKUM :: 15/03/2011 :: VWPE :: ER 12424063
X_PLANNED_START_DATE => p_x_stages_tbl(i).Stage_Planned_Start_Time,-- PRAKKUM :: 15/03/2011 :: VWPE :: ER 12424063
X_PLANNED_END_DATE => p_x_stages_tbl(i).Stage_Planned_End_Time,-- PRAKKUM :: 15/03/2011 :: VWPE :: ER 12424063
X_PREV_STAGE_NUM => p_x_stages_tbl(i).PREV_STAGE_NUM,-- PRAKKUM :: 15/03/2011 :: VWPE :: ER 12424063
X_EARLIEST_START_DATE => p_x_stages_tbl(i).EARLIEST_START_DATE -- PRAKKUM :: 15/03/2011 :: VWPE :: ER 12424063
);
IF l_is_planned_end_date_updated THEN
UPDATE_SUCC_STG_START_DATES(
p_stage_id => p_x_stages_tbl(i).STAGE_ID,
p_visit_id => P_VISIT_ID,
p_planned_end_date => p_x_stages_tbl(i).Stage_Planned_End_Time,
p_x_stages_ovn_tbl => p_x_stages_ovn_tbl,--VWPE 12730539:: PRAKKUM :: 08-JUL-2011
p_caller_id => 'P',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_SUCC_STG_START_DATES - l_return_status : '||l_return_status);
IF l_is_earliest_date_updated THEN
UPDATE_STAGE_DEPENDENCY_RULES (
p_stage_id => p_x_stages_tbl(i).STAGE_ID,
p_visit_id => P_VISIT_ID,
p_x_stages_ovn_tbl => p_x_stages_ovn_tbl,--VWPE 12730539:: PRAKKUM :: 08-JUL-2011
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_STAGE_DEPENDENCY_RULES - l_return_status : '||l_return_status||' :p_x_stages_ovn_tbl cnt is '||p_x_stages_ovn_tbl.count);
ROLLBACK TO Update_Stages;
ROLLBACK TO Update_Stages;
ROLLBACK TO Update_Stages;
END Update_Stages;
PROCEDURE Delete_Stages (
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_id IN NUMBER,
p_x_stages_tbl IN OUT NOCOPY Visit_Stages_Tbl_Type, -- PRAKKUM :: 15/03/2011 :: VWPE :: ER 12424063
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
is
Cursor c_stage_links(x_id IN NUMBER) IS
SELECT distinct STG_LK.STAGE_LINK_ID, STG_LK.OBJECT_ID, STG_LK.SUBJECT_ID, STG_LK.RELATION_TYPE
FROM
AHL_STAGE_LINKS STG_LK
WHERE ( STG_LK.OBJECT_ID = x_id OR STG_LK.SUBJECT_ID = x_id );
select stage_name, duration, object_version_number
from AHL_VWP_STAGES_VL
where stage_id = c_stage_id;
SELECT AHL_VWP_VISITS_PVT.Is_Old_Visit(VISIT_ID) IS_OLD_VISIT
FROM AHL_VISITS_B
WHERE VISIT_ID = c_visit_id;
select count(1) as TASK_COUNT
from AHL_VISIT_TASKS_VL
where VISIT_ID = c_visit_id
AND TASK_TYPE_CODE <> 'STAGE'
AND STAGE_ID = c_stage_id;
select *
from AHL_VISIT_TASKS_VL
where VISIT_ID = c_visit_id
AND TASK_TYPE_CODE = 'STAGE'
AND STAGE_ID = c_stage_id;
L_API_NAME CONSTANT VARCHAR2(30) := 'Delete Stage';
SAVEPOINT Delete_Stages;
fnd_log.string(l_log_statement,L_DEBUG,p_x_stages_tbl(i).STAGE_ID||' stage id updated');
update AHL_VWP_STAGES_B set OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1,
DURATION = 0,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
where STAGE_ID = p_x_stages_tbl(i).STAGE_ID;
delete from AHL_STAGE_LINKS where STAGE_LINK_ID = l_stagelinks_rec.STAGE_LINK_ID;
UPDATE_AS_STAGE_RULE_DEL (
p_api_version => p_api_version,
p_init_msg_list => Fnd_Api.g_false,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_module_type => p_module_type,
p_object_id => l_stagelinks_rec.OBJECT_ID,
p_subject_id => l_stagelinks_rec.SUBJECT_ID,
p_relation_type => l_stagelinks_rec.RELATION_TYPE,
p_visit_id => p_visit_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
DELETE FROM AHL_VISIT_TASKS_TL WHERE VISIT_TASK_ID = l_stage_task_dets.VISIT_TASK_ID;
DELETE FROM AHL_VISIT_TASKS_B WHERE VISIT_TASK_ID = l_stage_task_dets.VISIT_TASK_ID;
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_module_type => p_module_type,
p_Visit_Task_Id => l_stage_task_dets.VISIT_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);
Ahl_VWP_stages_Pkg.DELETE_ROW ( X_STAGE_ID => p_x_stages_tbl(i).STAGE_ID);
fnd_log.string(l_log_statement,L_DEBUG,p_x_stages_tbl(i).STAGE_ID||' stage id updated');
update AHL_VISIT_TASKS_B set OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1,
STAGE_ID = null,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
where STAGE_ID = p_x_stages_tbl(i).STAGE_ID;
ROLLBACK TO Delete_Stages;
ROLLBACK TO Delete_Stages;
ROLLBACK TO Delete_Stages;
end Delete_Stages;
PROCEDURE Delete_All_Stages (
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_id IN 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_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'Delete Stages';
SAVEPOINT Delete_All_Stages;
DELETE FROM AHL_STAGE_LINKS
WHERE SUBJECT_ID IN (SELECT STAGE_ID FROM AHL_VWP_STAGES_B
WHERE VISIT_ID = p_visit_id);
DELETE FROM AHL_STAGE_LINKS
WHERE OBJECT_ID IN (SELECT STAGE_ID FROM AHL_VWP_STAGES_B
WHERE VISIT_ID = p_visit_id);
delete from AHL_VWP_STAGES_TL
where stage_id
in (select stage_id from ahl_vwp_stages_b
where visit_id = p_visit_id);
delete from AHL_VWP_STAGES_B
where visit_id = p_visit_id;
ROLLBACK TO Delete_All_Stages;
ROLLBACK TO Delete_All_Stages;
ROLLBACK TO Delete_All_Stages;
end Delete_All_Stages;
SELECT 'x' FROM AHL_VWP_STAGES_VL
WHERE VISIT_ID = C_VISIT_ID AND
STAGE_ID <> C_STAGE_ID AND
STAGE_NAME = C_STAGE_NAME;
select stage_name, duration, object_version_number
from AHL_VWP_STAGES_VL
where stage_id = c_stage_id;
select 'x' from ahl_visit_tasks_b
where stage_id = c_stage_id
and nvl(status_code,'X')<>'DELETED';
SELECT Ahl_vwp_stages_B_S.NEXTVAL
FROM dual;
SELECT 1
FROM Ahl_vwp_stages_b
WHERE stage_id = x_id;
PROCEDURE VALIDATE_STAGE_UPDATES(
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_default IN VARCHAR2 := Fnd_Api.G_TRUE,
p_module_type IN VARCHAR2 := NULL,
p_visit_id IN NUMBER,
p_visit_task_id IN NUMBER,
p_stage_name IN VARCHAR2 := NULL, -- defaulted as u may pass id or num
x_stage_id OUT NOCOPY NUMBER ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
is
L_STAGE_ID NUMBER;
L_API_NAME CONSTANT VARCHAR2(30) := 'VALIDATE_STAGE_UPDATES';
select link.parent_task_id from ahl_task_links link
start with link.visit_task_id = c_task_id
connect by prior link.parent_task_id = link.visit_task_id;
select link.visit_task_id from ahl_task_links link
start with link.parent_task_id = c_task_id
connect by prior link.visit_task_id = link.parent_task_id;
SELECT stage_id FROM ahl_visit_tasks_b
WHERE visit_task_id = c_task_id;
select link.subject_id from ahl_stage_links link
start with link.object_id = c_stage_id
connect by prior link.subject_id = link.object_id;
select link.object_id from ahl_stage_links link
start with link.subject_id = c_stage_id
connect by prior link.object_id = link.subject_id;
SELECT stage_id,stage_num FROM ahl_vwp_stages_vl
WHERE stage_name = c_stage_name
AND visit_id = c_visit_id;
end VALIDATE_STAGE_UPDATES;
select * from ahl_vwp_stages_vl where stage_id = c_stage_id;
SELECT Stage_Id INTO x_Stage_id
FROM AHL_VWP_STAGES_VL
WHERE Visit_Id = p_visit_id AND Stage_Name = p_Stage_Name;
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
SELECT COUNT(*) FROM AHL_DEPARTMENT_SHIFTS
WHERE DEPARTMENT_ID = x_id;
SELECT DEPARTMENT_ID FROM AHL_VISIT_TASKS_B WHERE VISIT_ID = x_id
AND NVL(STATUS_CODE,'X') <> 'DELETED' AND DEPARTMENT_ID IS NOT NULL;
SELECT DISTINCT(MR_Route_ID) "ROUTE_ID" FROM AHL_VISIT_TASKS_B TSK
WHERE VISIT_ID = x_id AND MR_Route_ID IS NOT NULL
AND NOT EXISTS
(SELECT DISTINCT(MR_Route_ID) "ROUTE_ID" FROM AHL_MR_ROUTES_V MR
where MR.mr_route_id =TSK.mr_route_id) and rownum=1;
SELECT 1 from dual WHERE exists(
SELECT visit_task_id from ahl_visit_tasks_b
Where department_id is not null
and visit_id = x_visit_id
and nvl(status_code,'X')<>'DELETED'
and department_id not in (select department_id from ahl_department_shifts)
);
SELECT COUNT(*) INTO l_counter
FROM AHL_STAGE_LINKS A
START WITH OBJECT_ID = P_CHILD_STAGE_ID
CONNECT BY PRIOR OBJECT_ID =SUBJECT_ID;
SELECT STAGE_NUM
FROM AHL_VWP_STAGES_B
WHERE stage_id = p_stage_id;
SELECT PREV_STAGE_NUM
FROM AHL_VWP_STAGES_B
WHERE stage_id = p_stage_id;
SELECT link.stage_link_id, link.relation_type, link.object_id, link.subject_id
FROM ahl_stage_links link, ahl_vwp_stages_b stage1, ahl_vwp_stages_b stage2
WHERE stage1.stage_id = p_stage_id
AND stage1.visit_id = stage2.visit_id
AND link.subject_id = stage2.stage_id;
SELECT 'X'
FROM ahl_task_links
WHERE parent_task_id IN (SELECT visit_task_id FROM ahl_visit_tasks_b WHERE stage_id = p_object_id)
START WITH visit_task_id IN (SELECT visit_task_id FROM ahl_visit_tasks_b WHERE stage_id = p_subject_id)
CONNECT BY PRIOR parent_task_id = visit_task_id;
SELECT 'X'
FROM ahl_visit_tasks_b
WHERE stage_id = p_stage_id
AND task_type_code <> 'STAGE';
SELECT link.subject_id FROM ahl_stage_links link
START WITH link.object_id = p_object_id
AND link.subject_id = p_subject_id
CONNECT BY PRIOR link.subject_id = link.object_id;
SELECT link.object_id FROM ahl_stage_links link
START WITH link.subject_id = p_subject_id
AND link.object_id = p_object_id
CONNECT BY PRIOR link.object_id = link.subject_id;
SELECT link.stage_link_id, link.object_id, link.subject_id, stage1.visit_id
FROM ahl_stage_links link, ahl_vwp_stages_b stage1, ahl_vwp_stages_b stage2
WHERE stage1.stage_id = c_stage_id
AND stage1.visit_id = stage2.visit_id
AND link.subject_id = stage2.stage_id
AND link.relation_type = 'PARALLEL';
SELECT link.stage_link_id, link.object_id, link.subject_id
FROM ahl_stage_links link, ahl_vwp_stages_b stage1, ahl_vwp_stages_b stage2
WHERE stage1.stage_id = c_stage_id
AND stage1.visit_id = stage2.visit_id
AND link.subject_id = stage2.stage_id
AND link.relation_type = 'PARALLEL'
AND link.stage_link_id <> c_link_id;
SELECT count(1) -- PRAKKUM :: 06/03/2011 :: VWPE :: ER 12424063 :: Fixed issue in getting parallel stage ids for a stage
FROM ahl_stage_links link
WHERE ( link.subject_id = p_sub_id AND link.object_id = p_obj_id ) OR ( link.subject_id = p_obj_id AND link.object_id = p_sub_id ) ;
select distinct STAGE_NUM from
ahl_vwp_stages_b where ( stage_id = p_sub_id OR stage_id = p_obj_id) and stage_status_code<>'PLANNING';
SAVEPOINT STAGE_RULE_UPDATE;
UPDATE ahl_stage_links
SET object_id = stage_parallel_rules1.subject_id,
subject_id = stage_parallel_rules1.object_id
WHERE stage_link_id = stage_parallel_rules1.stage_link_id;
UPDATE ahl_stage_links
SET object_id = stage_parallel_rules2.subject_id,
subject_id = stage_parallel_rules2.object_id
WHERE stage_link_id = stage_parallel_rules2.stage_link_id;
UPDATE ahl_stage_links
SET object_id = subject_id,
subject_id = object_id
WHERE subject_id IN (SELECT stage_id FROM ahl_vwp_stages_b WHERE visit_id = stage_parallel_rules1.visit_id)
AND relation_type = 'PARALLEL';
ROLLBACK TO STAGE_RULE_UPDATE;
SELECT visit_id, stage_name
FROM ahl_vwp_stages_vl
WHERE stage_id = p_stage_id;
SELECT visit_task_id
FROM ahl_visit_tasks_b
WHERE visit_id = p_visit_id
AND stage_type_code = p_stage_type_code
AND STATUS_CODE = 'PLANNING'
AND STAGE_ID IS NULL;
Ahl_VWP_Stages_Pkg.INSERT_ASSOC_ROW (
X_ROWID => l_rowid,
X_STAGE_TYPE_ASSOC_ID => p_stage_type_assoc_tbl(i).STAGE_TYPE_ASSOC_ID,
X_STAGE_ID => p_stage_type_assoc_tbl(i).STAGE_ID,
X_STAGE_TYPE_CODE => p_stage_type_assoc_tbl(i).STAGE_TYPE_CODE,
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_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);
fnd_log.string(l_log_statement,L_DEBUG_KEY,'After calling insert row. Created an association for stage - '||p_stage_type_assoc_tbl(i).STAGE_ID||' and stage type - '||p_stage_type_assoc_tbl(i).STAGE_TYPE_CODE);
' Before Calling AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_UPDATES ');
AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_UPDATES(
P_API_VERSION => 1.0,
P_VISIT_ID => l_visit_id,
P_VISIT_TASK_ID => l_tasks_rec.visit_task_id,
P_STAGE_NAME => l_stage_name,
X_STAGE_ID => l_stage_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data );
' After Calling VALIDATE_STAGE_UPDATES and return status is: '||l_return_status);
UPDATE AHL_VISIT_TASKS_B
SET STAGE_ID = p_stage_type_assoc_tbl(i).STAGE_ID
WHERE visit_task_id = l_tasks_rec.visit_task_id;
fnd_log.string(l_log_statement,L_DEBUG_KEY,'DML operation for the record is Delete. Deleting a record with assoc id : '||p_stage_type_assoc_tbl(i).STAGE_TYPE_ASSOC_ID);
UPDATE AHL_VISIT_TASKS_B
SET STAGE_ID = NULL
WHERE visit_id = l_visit_id
AND stage_type_code = p_stage_type_assoc_tbl(i).stage_type_code
AND status_code = 'PLANNING';
END IF; -- DML operation is delete
SELECT START_DATE_TIME , department_id
FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
SELECT subject_id
FROM AHL_VST_TYP_STAGE_LINKS
WHERE object_id = p_stage_id and relation_type = 'BEFORE' ;
SELECT stage_number
FROM ahl_visit_type_stages_b
WHERE visit_type_stage_id = p_stage_id;
SELECT distinct stg.visit_type_id, stg.duration, stg.visit_type_stage_id,stg.stage_number, stg.stage_name
FROM ahl_visit_type_stages_vl stg, ahl_visit_types_b vt, ahl_visits_b visit,
ahl_mc_headers_B mc, AHL_UNIT_CONFIG_HEADERS unit
WHERE vt.visit_type_code = p_visit_type_code
AND visit.visit_id = p_visit_id
AND visit.item_instance_id = unit.CSI_ITEM_INSTANCE_ID
AND vt.mc_id = mc.mc_id
AND unit.master_config_id = mc.mc_header_id
AND vt.STATUS_CODE = 'COMPLETE'
AND vt.visit_type_id = stg.visit_type_id;
SELECT stage_type_code
FROM ahl_vst_typ_stg_typ_asoc
WHERE visit_type_stage_id = p_visit_type_stage_id;
SELECT stage_obj.stage_id object_id, stage_sub.stage_id subject_id, links.relation_type
FROM ahl_vst_typ_stage_links links, ahl_visit_type_stages_b vt_obj, ahl_visit_type_stages_b vt_sub,
ahl_vwp_stages_b stage_obj, ahl_vwp_stages_b stage_sub
WHERE links.subject_id = p_visit_type_stage_id
AND links.subject_id = vt_sub.visit_type_stage_id
AND links.object_id = vt_obj.visit_type_stage_id
AND vt_sub.stage_number = stage_sub.stage_num
AND vt_obj.stage_number = stage_obj.stage_num
AND stage_obj.visit_id = p_visit_id
AND stage_sub.visit_id = p_visit_id;
SELECT visit_task_id
FROM ahl_visit_tasks_b
WHERE visit_id = p_visit_id
AND stage_type_code = p_stage_type_code
AND STATUS_CODE = 'PLANNING';
'Before inserting stages for visit_type_code '||p_visit_type_code||'Visit ID =' || P_VISIT_ID);
'Before calling table handler for inserting a record. stage ID is '||l_stage_id|| ' previous stage number is: '||l_prev_stage_num);
Ahl_VWP_Stages_Pkg.Insert_Row (
X_ROWID => l_rowid,
X_VISIT_ID => P_VISIT_ID,
X_STAGE_ID => l_stage_id,
X_STAGE_NUM => l_stages_rec.stage_number,
X_STAGE_NAME => l_stages_rec.stage_name,
X_DURATION => l_stages_rec.duration,
X_OBJECT_VERSION_NUMBER => 1,
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_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,
X_STAGE_STATUS_CODE => 'PLANNING',
X_PLANNED_START_DATE => NULL,
X_PLANNED_END_DATE => NULL,
X_PREV_STAGE_NUM => l_prev_stage_num,
X_EARLIEST_START_DATE => NULL );
'After calling table handler for inserting a record and before calling AHL_VWP_TASKS_PVT.Create_Stage_Tasks');
'Before inserting stage - stage type association for stage num '||l_stages_rec.stage_number);
INSERT INTO AHL_VISIT_STAGE_TYP_ASOC(
STAGE_TYPE_ASSOC_ID,
STAGE_ID,
STAGE_TYPE_CODE,
SECURITY_GROUP_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
values
(
AHL_VISIT_STAGE_TYP_ASOC_S.nextval,
l_stage_id,
l_stagetypes_rec.stage_type_code,
null,
SYSDATE,
Fnd_Global.USER_ID,
SYSDATE,
Fnd_Global.USER_ID,
Fnd_Global.LOGIN_ID);
'Before Calling AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_UPDATES ');
AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_UPDATES(
P_API_VERSION => 1.0,
P_VISIT_ID => P_VISIT_ID,
P_VISIT_TASK_ID => l_tasks_rec.visit_task_id,
P_STAGE_NAME => l_stages_rec.stage_name,
X_STAGE_ID => l_stg_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 and return status: '||l_return_status);
UPDATE AHL_VISIT_TASKS_B
SET STAGE_ID = l_stage_id
WHERE visit_task_id = l_tasks_rec.visit_task_id;
'Before calling inserting stage rules visit_type_stage_id= '||l_stages_rec.visit_type_stage_id||', visit_id'||p_visit_id);
'Before inserting stage rules l_stage_rules_rec.object_id and l_stage_rules_rec.subject_id= '||l_stage_rules_rec.object_id||' '||l_stage_rules_rec.subject_id);
INSERT INTO AHL_STAGE_LINKS (
STAGE_LINK_ID,
OBJECT_ID,
SUBJECT_ID,
RELATION_TYPE,
OBJECT_VERSION_NUMBER,
SECURITY_GROUP_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
values
(
AHL_STAGE_LINKS_S.nextval,
l_stage_rules_rec.object_id,
l_stage_rules_rec.subject_id,
l_stage_rules_rec.relation_type,
1,
null,
SYSDATE,
Fnd_Global.USER_ID,
SYSDATE,
Fnd_Global.USER_ID,
Fnd_Global.LOGIN_ID
);
'After inserting stage rules and before claling validate stage rules');
'Before calling UPDATE_STAGES_HIERARICHY and message count is :'||Fnd_Msg_Pub.count_msg);
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 FLOOR(nvl(MAX(stage_num),0)+1)
FROM AHL_VWP_STAGES_B
WHERE Visit_Id = p_visit_id;
SELECT distinct STAGE_LINK_ID,subject_ID,object_ID FROM
ahl_stage_links ,AHL_VWP_STAGES_B
WHERE RELATION_TYPE='PARALLEL'
AND ( ahl_stage_links.subject_id=AHL_VWP_STAGES_B.stage_id OR ahl_stage_links.object_id=AHL_VWP_STAGES_B.stage_id)
AND VISIT_ID =vst_id;
PROCEDURE UPDATE_STAGES_HIERARICHY (
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_id IN NUMBER,
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_STAGES_HIERARICHY';
SAVEPOINT UPDATE_STAGES_HIERARICHY;
ROLLBACK TO UPDATE_STAGES_HIERARICHY;
ROLLBACK TO UPDATE_STAGES_HIERARICHY;
ROLLBACK TO UPDATE_STAGES_HIERARICHY;
END UPDATE_STAGES_HIERARICHY;
PROCEDURE UPDATE_AS_STAGE_RULE_DEL (
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_object_id IN NUMBER,
p_subject_id IN NUMBER,
p_relation_type IN VARCHAR2,
p_visit_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
-- Procedure Description
--
-- If deletion of relation type is before then find and keep all parallel stages of object stage.
-- If deletion of relation type is parallel then find and keep all parallel stages of subject stage and object stage.
--
-- Update all this parallel stages start date to a before stage planned end date or visit start date.
--
-- Call PROCESS_STAGE_DATES which will update all successor stages in hierarichy to correct stage start dates.
/* cursor to get all parallel relations of a visit */
Cursor c_parallel_stages_dets(vst_id IN NUMBER) IS
SELECT distinct STAGE_LINK_ID,subject_ID,object_ID FROM
ahl_stage_links ,AHL_VWP_STAGES_B
WHERE RELATION_TYPE='PARALLEL'
AND ( ahl_stage_links.subject_id=AHL_VWP_STAGES_B.stage_id OR ahl_stage_links.object_id=AHL_VWP_STAGES_B.stage_id)
AND VISIT_ID =vst_id;
l_update_stage_id NUMBER; -- temporary variable to keep stage id
L_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_AS_STAGE_RULE_DEL';
SAVEPOINT UPDATE_AS_STAGE_RULE_DEL;
select max(START_DATE_TIME) into l_visit_start_date from AHL_VISITS_VL where VISIT_ID = p_visit_id;
update AHL_VWP_STAGES_B set object_version_number=object_version_number+1,
PREV_STAGE_NUM = NULL,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
where STAGE_ID = p_object_id;
fnd_log.string(l_log_statement,L_DEBUG,p_object_id||' stage id updated');
l_update_stage_id := parallel_stage_ids.FIRST;
WHILE l_update_stage_id IS NOT NULL
LOOP
IF (l_log_statement >= l_log_current_level) THEN
fnd_log.string(l_log_statement,L_DEBUG,'Before planned end date calc ');
SELECT max(PLANNED_END_DATE) into l_prev_stage_planned_end_date from AHL_VWP_STAGES_B,AHL_STAGE_LINKS
WHERE subject_ID = STAGE_ID AND RELATION_TYPE='BEFORE' AND object_ID=l_update_stage_id AND VISIT_ID=p_visit_id;
fnd_log.string(l_log_statement,L_DEBUG,'Update stage start date is '||l_prev_stage_planned_end_date||' for stage '||l_update_stage_id);
update AHL_VWP_STAGES_B set OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1,
PLANNED_START_DATE=l_prev_stage_planned_end_date,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
where STAGE_ID = l_update_stage_id;
fnd_log.string(l_log_statement,L_DEBUG,l_update_stage_id||' stage id updated');
l_update_stage_id := parallel_stage_ids.NEXT(l_update_stage_id);
ROLLBACK TO UPDATE_AS_STAGE_RULE_DEL;
ROLLBACK TO UPDATE_AS_STAGE_RULE_DEL;
ROLLBACK TO UPDATE_AS_STAGE_RULE_DEL;
END UPDATE_AS_STAGE_RULE_DEL;
PROCEDURE UPDATE_STAGE_DEPENDENCY_RULES (
p_stage_id IN NUMBER,
p_visit_id IN NUMBER,
p_x_stages_ovn_tbl IN OUT NOCOPY Visit_Stages_OVN_Tbl_Type,--VWPE 12730539:: PRAKKUM :: 08-JUL-2011
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
-- Procedure Description
--
-- Since earliest start date of stage is changed, on each rule the stage belongs do
--
-- Call PROCESS_STAGE_DATES which will update all successor stages in hierarichy to correct stage start dates.
--Cursor to fetch the stage number for the passed stage ID
CURSOR c_stage_link_details(p_stage_id IN NUMBER)
IS
SELECT DISTINCT SUBJECT_ID, OBJECT_ID , RELATION_TYPE
FROM AHL_STAGE_LINKS
WHERE SUBJECT_ID =p_stage_id OR OBJECT_ID = p_stage_id;
L_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_STAGE_DEPENDENCY_RULES';
SELECT
CASE WHEN SUBSTG.PLANNED_END_DATE <> OBJSTG.PLANNED_START_DATE THEN 'Y' ELSE 'N' END IS_USER_UPDATED, -- PRAKKUM :: 10/05/2012 :: Bug 13965577
SUBSTG.STAGE_ID SUB_STAGE_ID,
SUBSTG.PLANNED_END_DATE SUB_PLANNED_END_DATE ,
SUBSTG.VISIT_ID SUB_VISIT_ID
FROM (
SELECT SUBJECT_ID,OBJECT_ID FROM AHL_STAGE_LINKS
WHERE OBJECT_ID = obj_id AND RELATION_TYPE ='BEFORE'
) SRules,
AHL_VWP_STAGES_B SUBSTG,
AHL_VWP_STAGES_B OBJSTG
WHERE SUBSTG.STAGE_ID = SRules.SUBJECT_ID AND OBJSTG.STAGE_ID = SRules.OBJECT_ID;
SELECT distinct STAGE_LINK_ID,subject_ID,object_ID FROM
ahl_stage_links ,AHL_VWP_STAGES_B
WHERE RELATION_TYPE='PARALLEL'
AND ( ahl_stage_links.subject_id=AHL_VWP_STAGES_B.stage_id OR ahl_stage_links.object_id=AHL_VWP_STAGES_B.stage_id)
AND VISIT_ID =vst_id;
SELECT VST.VISIT_ID,STAGE_ID,
VST.department_id DEPT_ID, nvl(STG.duration,0) DURATION,
CASE WHEN nvl(VST.start_date_time,STG.earliest_start_date) >= nvl(STG.earliest_start_date,VST.start_date_time)
THEN VST.start_date_time ELSE STG.earliest_start_date END MAX_START_DATE
from AHL_VISITS_B VST, AHL_VWP_STAGES_B STG
WHERE VST.VISIT_ID = STG.VISIT_ID
AND VST.VISIT_ID = vst_id
AND STG.STAGE_ID = stg_id;
SAVEPOINT UPDATE_STAGE_DEPENDENCY_RULES;
UPDATE AHL_VWP_STAGES_B SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
PLANNED_START_DATE = l_stg_start_date_dets.MAX_START_DATE,
PLANNED_END_DATE = l_planned_end_date,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE STAGE_ID = p_stage_id;
fnd_log.string(l_log_statement,L_DEBUG,p_stage_id||' stage id updated');
IF c_get_user_upd_details%FOUND AND l_usr_upd_dets.IS_USER_UPDATED = 'Y' THEN
CLOSE c_get_user_upd_details;
fnd_log.string(l_log_statement,L_DEBUG,'IS_USER_UPDATED '||l_usr_upd_dets.IS_USER_UPDATED);
UPDATE_SUCC_STG_START_DATES(
p_stage_id => l_usr_upd_dets.SUB_STAGE_ID,
p_visit_id => l_usr_upd_dets.SUB_VISIT_ID,
p_planned_end_date => l_usr_upd_dets.SUB_PLANNED_END_DATE,
p_x_stages_ovn_tbl => p_x_stages_ovn_tbl,--VWPE 12730539:: PRAKKUM :: 08-JUL-2011
p_caller_id => 'P',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_SUCC_STG_START_DATES - l_return_status : '||l_return_status);
ROLLBACK TO UPDATE_STAGE_DEPENDENCY_RULES;
ROLLBACK TO UPDATE_STAGE_DEPENDENCY_RULES;
ROLLBACK TO UPDATE_STAGE_DEPENDENCY_RULES;
END UPDATE_STAGE_DEPENDENCY_RULES;
PROCEDURE UPDATE_STG_RUL_HIERARCHY (
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_stage_link_id IN NUMBER,
p_visit_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
-- Procedure Description
--
-- Update previous stage number and planned start date to previous stage end date or visit start date
--
-- Call PROCESS_STAGE_DATES which will update all successor stages in hierarichy to correct stage start dates.
--Cursor to fetch the stage number for the passed stage ID
CURSOR c_stage_details(p_stage_id IN NUMBER)
IS
SELECT STAGE_NUM , PLANNED_END_DATE
FROM AHL_VWP_STAGES_B
WHERE stage_id = p_stage_id;
SELECT * FROM AHL_STAGE_LINKS
WHERE STAGE_LINK_ID = x_id;
SELECT PREV_STAGE_NUM FROM AHL_VWP_STAGES_B
WHERE STAGE_ID = p_stage_id;
l_update_stage_id NUMBER;
L_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_STG_RUL_HIERARCHY';
SAVEPOINT UPDATE_STG_RUL_HIERARCHY;
l_update_stage_id := l_Stage_link_dets.object_ID;
l_update_stage_id := l_Stage_link_dets.subject_ID;
l_update_stage_id := l_Stage_link_dets.subject_ID;
fnd_log.string(l_log_statement,L_DEBUG,' For Stage ' || l_update_stage_id ||' Prev Stage Number is ' || l_prev_stage_id);
OPEN c_get_prev_stage_dets(l_update_stage_id);
/*update AHL_VWP_STAGES_B set object_version_number=object_version_number+1,
PLANNED_START_DATE=l_prev_stage_planned_end_date,
PREV_STAGE_NUM = l_stg_stage_number,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
where STAGE_ID = l_update_stage_id;
fnd_log.string(l_log_statement,L_DEBUG,l_update_stage_id||' stage id updated');
p_object_id => l_update_stage_id,
p_relation_type => l_Stage_link_dets.relation_type,
p_operation_flag => 'U' ,
p_x_stages_ovn_tbl => l_stages_ovn_tbl,--VWPE 12730539:: PRAKKUM :: 08-JUL-2011
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
UPDATE_SUCC_STG_START_DATES(
p_stage_id => l_prev_stage_id,
p_visit_id => p_visit_id,
p_planned_end_date => l_prev_stage_planned_end_date,
p_x_stages_ovn_tbl => l_stages_ovn_tbl,--VWPE 12730539:: PRAKKUM :: 08-JUL-2011
p_caller_id => 'R',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_SUCC_STG_START_DATES - l_return_status : '||l_return_status);
ROLLBACK TO UPDATE_STG_RUL_HIERARCHY;
ROLLBACK TO UPDATE_STG_RUL_HIERARCHY;
ROLLBACK TO UPDATE_STG_RUL_HIERARCHY;
END UPDATE_STG_RUL_HIERARCHY;
select rownum as INDEX_ID, STAGE_ID, DURATION, PLANNED_START_DATE,
--nvl(stg.earliest_start_date,vst.start_date_time) stage_start_date ,
case when nvl(stg.earliest_start_date,vst.start_date_time) > vst.start_date_time THEN
stg.earliest_start_date
ELSE
nvl(vst.start_date_time,stg.earliest_start_date)
END stage_start_date , -- PRAKKUM :: 08-JUL-2011 :: VWPE 12730539 :: Stage start date w.r.t visit start date
vst.department_id
from ahl_vwp_stages_b stg,
ahl_visits_vl vst
WHERE
vst.visit_id = stg.visit_id AND vst.VISIT_ID = x_id;
select DEPARTMENT_ID,START_DATE_TIME,STATUS_CODE,VISIT_TYPE_CODE,AHL_VWP_VISITS_PVT.Is_Old_Visit(VISIT_ID) IS_OLD_VISIT
, CASE WHEN UNIT_SCHEDULE_ID IS NOT NULL THEN 'Y' ELSE 'N' END IS_OPERATIONAL_VISIT
from ahl_visits_vl
WHERE VISIT_ID = x_id;-- PRAKKUM :: 13-FEB-2011 :: Bug 13711800
SELECT count(1) as cnt
FROM
AHL_STAGE_LINKS STG_LK,AHL_VWP_STAGES_B STG
WHERE ( STG.STAGE_ID = STG_LK.subject_ID OR STG.STAGE_ID = STG_LK.object_ID )
AND STG.VISIT_ID = p_visit_id;
SELECT STAGE_NUM FROM AHL_VWP_STAGES_B WHERE VISIT_ID =vst_id
AND
(( PLANNED_END_DATE IS NOT NULL AND earliest_start_date IS NOT NULL AND earliest_start_date>PLANNED_END_DATE )
OR
( PLANNED_START_DATE IS NOT NULL AND earliest_start_date IS NOT NULL AND earliest_start_date
UPDATE AHL_VWP_STAGES_B set object_version_number=object_version_number+1,
PLANNED_START_DATE=null,
PLANNED_END_DATE=null,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE VISIT_ID =p_visit_id;
UPDATE AHL_VWP_STAGES_B
set OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1,
PLANNED_START_DATE=l_visit_start_date,
PLANNED_END_DATE=Compute_Stage_End_Date(p_visit_id,
l_visit_limited_dets.IS_OPERATIONAL_VISIT,
l_visit_start_date, l_dept_id, DURATION ),-- PRAKKUM :: 23-FEB-2011 :: Bug 13711800 :: Fix for Operational Visits
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE VISIT_ID =p_visit_id;
SELECT distinct STG_LK.subject_ID, STG_LK.object_ID, STG_LK.RELATION_TYPE
BULK COLLECT INTO l_stages_links
FROM
AHL_STAGE_LINKS STG_LK,AHL_VWP_STAGES_B STG
WHERE ( STG.STAGE_ID = STG_LK.subject_ID OR STG.STAGE_ID = STG_LK.object_ID )
AND STG.VISIT_ID = p_visit_id;
fnd_log.string(l_log_statement,L_DEBUG,' Before call to update stage dates');
UPDATE_STAGE_DATES (
p_visit_id => p_visit_id,-- PRAKKUM :: 13-FEB-2011 :: Bug 13711800
p_base_stage_id => NULL,
P_visit_start_date => l_visit_start_date,
p_dept_id => l_dept_id,
p_valid_stages => l_valid_stages,
p_x_stages_ovn_tbl => p_x_stages_ovn_tbl,--VWPE 12730539:: PRAKKUM :: 08-JUL-2011
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 calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_STAGE_DATES - l_return_status : '||l_return_status);
fnd_log.string(l_log_statement,L_DEBUG,' After call to update stage dates');
fnd_log.string(l_log_statement,L_DEBUG,' Before call to update stage dates');
UPDATE_STAGE_DATES ( p_visit_id => p_visit_id,-- PRAKKUM :: 13-FEB-2011 :: Bug 13711800
p_base_stage_id => p_subject_id,
p_visit_start_date => l_visit_start_date,
p_dept_id => l_dept_id,
p_valid_stages => l_valid_stages,
p_x_stages_ovn_tbl => p_x_stages_ovn_tbl,--VWPE 12730539:: PRAKKUM :: 08-JUL-2011
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 calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_STAGE_DATES - l_return_status : '||l_return_status);
fnd_log.string(l_log_statement,L_DEBUG,'After call to update stage dates');
fnd_log.string(l_log_statement,L_DEBUG,'Before call to update stage dates');
UPDATE_STAGE_DATES ( p_visit_id => p_visit_id,-- PRAKKUM :: 13-FEB-2011 :: Bug 13711800
p_base_stage_id => p_object_id,
p_visit_start_date => l_visit_start_date,
p_dept_id => l_dept_id,
p_valid_stages => l_valid_stages,
p_x_stages_ovn_tbl => p_x_stages_ovn_tbl,--VWPE 12730539:: PRAKKUM :: 08-JUL-2011
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 calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_STAGE_DATES - l_return_status : '||l_return_status);
fnd_log.string(l_log_statement,L_DEBUG,'After call to update stage dates');
fnd_log.string(l_log_statement,L_DEBUG,'Before call to update stage dates');
UPDATE_STAGE_DATES ( p_visit_id => p_visit_id,-- PRAKKUM :: 13-FEB-2011 :: Bug 13711800
p_base_stage_id => p_object_id,
p_visit_start_date => l_visit_start_date,
p_dept_id => l_dept_id,
p_valid_stages => l_valid_stages,
p_x_stages_ovn_tbl => p_x_stages_ovn_tbl,--VWPE 12730539:: PRAKKUM :: 08-JUL-2011
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 calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_STAGE_DATES - l_return_status : '||l_return_status);
fnd_log.string(l_log_statement,L_DEBUG,'After call to update stage dates');
PROCEDURE UPDATE_STAGE_DATES (
p_visit_id IN NUMBER, -- PRAKKUM :: 13-FEB-2011 :: Bug 13711800
p_base_stage_id IN NUMBER,
p_visit_start_date IN DATE,
p_dept_id IN NUMBER,
p_valid_stages IN Stage_Details ,
p_x_stages_ovn_tbl IN OUT NOCOPY Visit_Stages_OVN_Tbl_Type,--VWPE 12730539:: PRAKKUM :: 08-JUL-2011
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_stage_id NUMBER;
L_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_STAGE_DATES';
UPDATE AHL_VWP_STAGES_B SET object_version_number=object_version_number+1,
PLANNED_START_DATE = l_stage_start_date,
PLANNED_END_DATE = l_stage_end_date,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE STAGE_ID = l_stage_id;
fnd_log.string(l_log_statement,L_DEBUG,l_stage_id||' stage id updated');
UPDATE_STAGES_OVN_COUNT(
p_stage_id => p_base_stage_id,
p_updated_stage_id => l_stage_id,
p_x_stages_ovn_tbl => p_x_stages_ovn_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_STAGES_OVN_COUNT - l_return_status : '||l_return_status);
END UPDATE_STAGE_DATES;
PROCEDURE UPDATE_SUCC_STG_START_DATES(
p_stage_id IN NUMBER,
p_visit_id IN NUMBER,
p_planned_end_date IN DATE,
p_x_stages_ovn_tbl IN OUT NOCOPY Visit_Stages_OVN_Tbl_Type,--VWPE 12730539:: PRAKKUM :: 08-JUL-2011
p_caller_id IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
-- To get all parallel stages under visit
Cursor c_parallel_stages_dets(vst_id IN NUMBER) IS
SELECT distinct STAGE_LINK_ID,subject_ID,object_ID FROM
ahl_stage_links ,AHL_VWP_STAGES_B
WHERE RELATION_TYPE='PARALLEL'
AND ( ahl_stage_links.subject_id=AHL_VWP_STAGES_B.stage_id OR ahl_stage_links.object_id=AHL_VWP_STAGES_B.stage_id)
AND VISIT_ID =vst_id;
select object_ID object_ID FROM AHL_STAGE_LINKS WHERE RELATION_TYPE='BEFORE' and subject_ID=stg_id
UNION
select subject_ID object_ID FROM AHL_STAGE_LINKS WHERE RELATION_TYPE='AFTER' and object_ID=stg_id;
SELECT STAGE_NUM,planned_start_date,planned_end_date
FROM AHL_VWP_STAGES_B
WHERE PLANNED_END_DATE IS NOT NULL
AND PLANNED_START_DATE IS NOT NULL
and PLANNED_END_DATE < PLANNED_START_DATE AND VISIT_ID =vst_id;
l_update_stage_id NUMBER;
L_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_SUCC_STG_START_DATES';
l_update_stage_id := parallel_stage_ids.FIRST;
WHILE l_update_stage_id IS NOT NULL
LOOP
parallelStagesInStr:=parallelStagesInStr||','||l_update_stage_id;
l_update_stage_id := parallel_stage_ids.NEXT(l_update_stage_id);
l_query_str :=' select MAX(stg.PLANNED_END_DATE) from ahl_stage_links, ahl_vwp_stages_b stg '||
' where stg.STAGE_ID = subject_id AND relation_type = ''BEFORE'' '||
' and object_id in (' || parallelStagesInStr ||')';
l_query_str :=' select '||
' MAX(stg.EARLIEST_START_DATE) MAX_EARLIEST_START_DATE from ahl_vwp_stages_b stg '||
' where stg.STAGE_ID in (' || parallelStagesInStr ||')';
l_query_str :=' select case when '||
' (MAX(stg.EARLIEST_START_DATE)>MAX(stg.PLANNED_START_DATE)) then MAX(stg.EARLIEST_START_DATE) '||
' else MAX(stg.PLANNED_START_DATE) end MIN_PLANNED_START_DATE from ahl_vwp_stages_b stg '||
' where stg.STAGE_ID in (' || parallelStagesInStr ||')';
l_query_str :=' UPDATE AHL_VWP_STAGES_B set object_version_number=object_version_number+1, '||
' PLANNED_START_DATE = :p_planned_end_date, '||
' LAST_UPDATE_DATE = SYSDATE, '||
' LAST_UPDATED_BY = Fnd_Global.USER_ID, '||
' LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID '||
' WHERE STAGE_ID IN ('||parallelStagesInStr||') '||
' AND PLANNED_START_DATE = :p_max_planned_start_date';
fnd_log.string(l_log_statement,L_DEBUG,parallelStagesInStr||' stage id updated');
UPDATE_STAGES_OVN_COUNT(
p_stage_id => p_stage_id,
p_updated_stage_id => l_ovn_p_stage_id,
p_x_stages_ovn_tbl => p_x_stages_ovn_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.UPDATE_STAGES_OVN_COUNT - l_return_status : '||l_return_status);
END UPDATE_SUCC_STG_START_DATES;
PROCEDURE UPDATE_STAGES_OVN_COUNT(
p_stage_id IN NUMBER,
p_updated_stage_id IN NUMBER,
p_x_stages_ovn_tbl IN OUT NOCOPY Visit_Stages_OVN_Tbl_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_table_size NUMBER :=0;
l_updated_version_no VARCHAR2(1) := 'N';
L_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_STAGES_OVN_COUNT';
/*IF p_stage_id = p_updated_stage_id THEN
fnd_log.string(l_log_procedure,L_DEBUG||'.end','Return');
RETURN; -- NO need to update
l_updated_version_no := 'N';
fnd_log.string(l_log_statement,L_DEBUG,' p_stage_id '||p_stage_id||' p_updated_stage_id '||p_updated_stage_id);
||' p_x_stages_ovn_tbl(i).UPDATED_OBJ_VERSION_NO_COUNT:'||p_x_stages_ovn_tbl(i).UPDATED_OBJ_VERSION_NO_COUNT);
IF p_x_stages_ovn_tbl(i).STAGE_ID = p_updated_stage_id THEN
p_x_stages_ovn_tbl(i).UPDATED_OBJ_VERSION_NO_COUNT := p_x_stages_ovn_tbl(i).UPDATED_OBJ_VERSION_NO_COUNT + 1;
l_updated_version_no := 'Y';
EXIT WHEN l_updated_version_no ='Y';
fnd_log.string(l_log_statement,L_DEBUG,' l_table_size '||l_table_size||' : l_updated_version_no '||l_updated_version_no);
IF l_updated_version_no = 'N' THEN
p_x_stages_ovn_tbl(l_table_size).STAGE_ID := p_updated_stage_id;
p_x_stages_ovn_tbl(l_table_size).UPDATED_OBJ_VERSION_NO_COUNT := 1; -- Initialized
END UPDATE_STAGES_OVN_COUNT;
SELECT stg.stage_id,stg.stage_num,stg.planned_end_date,vst.close_date_time
FROM ahl_visits_b vst,
ahl_vwp_stages_b stg
WHERE vst.visit_id = stg.visit_id
AND TRUNC(stg.planned_end_date,'MI') > TRUNC(nvl(vst.close_date_time,stg.planned_end_date),'MI')
AND vst.visit_id = c_visit_id
AND vst.status_code <> 'DRAFT'
ORDER BY stg.stage_num;
SELECT VISIT_ID, UNIT_SCHEDULE_ID FROM AHL_VISITS_B
WHERE VISIT_ID = C_VISIT_ID;