eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AHL_VWP_VISITS_STAGES_PVT dependencies on AHL_VWP_STAGES_B

Line 518: SELECT Ahl_vwp_stages_B_S.NEXTVAL

514:
515: l_isValidationFailed BOOLEAN DEFAULT false;
516:
517: CURSOR c_seq IS
518: SELECT Ahl_vwp_stages_B_S.NEXTVAL
519: FROM dual;
520:
521: -- To find whether id already exists
522: CURSOR c_id_exists (x_id IN NUMBER) IS

Line 524: FROM Ahl_vwp_stages_b

520:
521: -- To find whether id already exists
522: CURSOR c_id_exists (x_id IN NUMBER) IS
523: SELECT 1
524: FROM Ahl_vwp_stages_b
525: WHERE stage_id = x_id;
526:
527: -- To find whether id already exists
528: CURSOR c_visit (vst_id IN NUMBER) IS

Line 540: FROM AHL_VWP_STAGES_B

536:
537: --
538: CURSOR c_stagenum_exists(V_ID IN NUMBER, STG_NO IN NUMBER) IS
539: SELECT 1
540: FROM AHL_VWP_STAGES_B
541: WHERE Visit_Id = V_ID AND stage_num = STG_NO;
542:
543: l_return_status VARCHAR2(1);-- PRAKKUM :: 12/05/2011 :: VWPE :: ER 12424063
544:

Line 895: and STAGE_ID IN (SELECT STAGE_ID FROM AHL_VWP_STAGES_B WHERE stage_num > C_STAGE_NUM

891: select 'x' from ahl_workorders_v
892: where visit_task_id in
893: (select DISTINCT VISIT_TASK_ID from AHL_VISIT_TASKS_B
894: where visit_id = C_VISIT_ID
895: and STAGE_ID IN (SELECT STAGE_ID FROM AHL_VWP_STAGES_B WHERE stage_num > C_STAGE_NUM
896: AND VISIT_ID = C_VISIT_ID))
897: and ( job_status_code =3 or firm_planned_flag = 1 );
898: */
899:

Line 1491: update AHL_VWP_STAGES_B set OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1,

1487: --Set duration to zero, calling UPDATE_AS_STAGE_RULE_DEL next to this statement will not consider duration into account
1488: IF (l_log_statement >= l_log_current_level) THEN
1489: fnd_log.string(l_log_statement,L_DEBUG,p_x_stages_tbl(i).STAGE_ID||' stage id updated');
1490: END IF;
1491: update AHL_VWP_STAGES_B set OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1,
1492: DURATION = 0,
1493: LAST_UPDATE_DATE = SYSDATE,
1494: LAST_UPDATED_BY = Fnd_Global.USER_ID,
1495: LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID

Line 1735: WHERE SUBJECT_ID IN (SELECT STAGE_ID FROM AHL_VWP_STAGES_B

1731:
1732: -- VWPE :: ER 12424063 :: PRAKKUM :: 05-MAY-2011 :: START
1733:
1734: DELETE FROM AHL_STAGE_LINKS
1735: WHERE SUBJECT_ID IN (SELECT STAGE_ID FROM AHL_VWP_STAGES_B
1736: WHERE VISIT_ID = p_visit_id);
1737:
1738: DELETE FROM AHL_STAGE_LINKS
1739: WHERE OBJECT_ID IN (SELECT STAGE_ID FROM AHL_VWP_STAGES_B

Line 1739: WHERE OBJECT_ID IN (SELECT STAGE_ID FROM AHL_VWP_STAGES_B

1735: WHERE SUBJECT_ID IN (SELECT STAGE_ID FROM AHL_VWP_STAGES_B
1736: WHERE VISIT_ID = p_visit_id);
1737:
1738: DELETE FROM AHL_STAGE_LINKS
1739: WHERE OBJECT_ID IN (SELECT STAGE_ID FROM AHL_VWP_STAGES_B
1740: WHERE VISIT_ID = p_visit_id);
1741:
1742: -- VWPE :: ER 12424063 :: PRAKKUM :: 05-MAY-2011 :: END
1743:

Line 1746: in (select stage_id from ahl_vwp_stages_b

1742: -- VWPE :: ER 12424063 :: PRAKKUM :: 05-MAY-2011 :: END
1743:
1744: delete from AHL_VWP_STAGES_TL
1745: where stage_id
1746: in (select stage_id from ahl_vwp_stages_b
1747: where visit_id = p_visit_id);
1748:
1749: delete from AHL_VWP_STAGES_B
1750: where visit_id = p_visit_id;

Line 1749: delete from AHL_VWP_STAGES_B

1745: where stage_id
1746: in (select stage_id from ahl_vwp_stages_b
1747: where visit_id = p_visit_id);
1748:
1749: delete from AHL_VWP_STAGES_B
1750: where visit_id = p_visit_id;
1751:
1752: -- directly delete as we need to delete all stages for the visit
1753:

Line 2002: SELECT Ahl_vwp_stages_B_S.NEXTVAL

1998: IS
1999:
2000: -- To find the next id value from visit sequence
2001: CURSOR c_seq IS
2002: SELECT Ahl_vwp_stages_B_S.NEXTVAL
2003: FROM dual;
2004:
2005: -- To find whether id already exists
2006: CURSOR c_id_exists (x_id IN NUMBER) IS

Line 2008: FROM Ahl_vwp_stages_b

2004:
2005: -- To find whether id already exists
2006: CURSOR c_id_exists (x_id IN NUMBER) IS
2007: SELECT 1
2008: FROM Ahl_vwp_stages_b
2009: WHERE stage_id = x_id;
2010:
2011: x_stage_Id NUMBER;
2012: l_dummy NUMBER;

Line 2545: FROM AHL_VWP_STAGES_B

2541: --Cursor to fetch the stage number for the passed stage ID
2542: CURSOR c_stage_number(p_stage_id IN NUMBER)
2543: IS
2544: SELECT STAGE_NUM
2545: FROM AHL_VWP_STAGES_B
2546: WHERE stage_id = p_stage_id;
2547:
2548: --Cursor to fetch the previous stage number for the passed stage ID
2549: CURSOR c_prev_stage_num(p_stage_id IN NUMBER)

Line 2552: FROM AHL_VWP_STAGES_B

2548: --Cursor to fetch the previous stage number for the passed stage ID
2549: CURSOR c_prev_stage_num(p_stage_id IN NUMBER)
2550: IS
2551: SELECT PREV_STAGE_NUM
2552: FROM AHL_VWP_STAGES_B
2553: WHERE stage_id = p_stage_id;
2554:
2555: --Cursor to get all the stage rules of the stage in a visit
2556: CURSOR c_get_stage_rules (p_stage_id IN NUMBER)

Line 2559: FROM ahl_stage_links link, ahl_vwp_stages_b stage1, ahl_vwp_stages_b stage2

2555: --Cursor to get all the stage rules of the stage in a visit
2556: CURSOR c_get_stage_rules (p_stage_id IN NUMBER)
2557: IS
2558: SELECT link.stage_link_id, link.relation_type, link.object_id, link.subject_id
2559: FROM ahl_stage_links link, ahl_vwp_stages_b stage1, ahl_vwp_stages_b stage2
2560: WHERE stage1.stage_id = p_stage_id
2561: AND stage1.visit_id = stage2.visit_id
2562: AND link.subject_id = stage2.stage_id;
2563:

Line 2598: FROM ahl_stage_links link, ahl_vwp_stages_b stage1, ahl_vwp_stages_b stage2

2594:
2595: -- Cursor to get all the parallel stage rules
2596: CURSOR c_get_parallel_stage_rules1 (c_stage_id IN NUMBER) IS
2597: SELECT link.stage_link_id, link.object_id, link.subject_id, stage1.visit_id
2598: FROM ahl_stage_links link, ahl_vwp_stages_b stage1, ahl_vwp_stages_b stage2
2599: WHERE stage1.stage_id = c_stage_id
2600: AND stage1.visit_id = stage2.visit_id
2601: AND link.subject_id = stage2.stage_id
2602: AND link.relation_type = 'PARALLEL';

Line 2606: FROM ahl_stage_links link, ahl_vwp_stages_b stage1, ahl_vwp_stages_b stage2

2602: AND link.relation_type = 'PARALLEL';
2603:
2604: CURSOR c_get_parallel_stage_rules2 (c_stage_id IN NUMBER, c_link_id IN NUMBER) IS
2605: SELECT link.stage_link_id, link.object_id, link.subject_id
2606: FROM ahl_stage_links link, ahl_vwp_stages_b stage1, ahl_vwp_stages_b stage2
2607: WHERE stage1.stage_id = c_stage_id
2608: AND stage1.visit_id = stage2.visit_id
2609: AND link.subject_id = stage2.stage_id
2610: AND link.relation_type = 'PARALLEL'

Line 2626: ahl_vwp_stages_b where ( stage_id = p_sub_id OR stage_id = p_obj_id) and stage_status_code<>'PLANNING';

2622: --Cursor to validate whether all rules are over planning stages or not
2623: CURSOR c_get_invalid_rule_stages (p_sub_id IN NUMBER,p_obj_id IN NUMBER)
2624: IS
2625: select distinct STAGE_NUM from
2626: ahl_vwp_stages_b where ( stage_id = p_sub_id OR stage_id = p_obj_id) and stage_status_code<>'PLANNING';
2627:
2628: l_invalid_stage_rules_rec c_get_invalid_rule_stages%RowType;
2629:
2630:

Line 2765: WHERE subject_id IN (SELECT stage_id FROM ahl_vwp_stages_b WHERE visit_id = stage_parallel_rules1.visit_id)

2761: END LOOP;--Loop through cursor c_get_parallel_stage_rules2
2762: UPDATE ahl_stage_links
2763: SET object_id = subject_id,
2764: subject_id = object_id
2765: WHERE subject_id IN (SELECT stage_id FROM ahl_vwp_stages_b WHERE visit_id = stage_parallel_rules1.visit_id)
2766: AND relation_type = 'PARALLEL';
2767: END LOOP; -- Loop through curosr c_get_parallel_stage_rules1
2768:
2769: ROLLBACK TO STAGE_RULE_UPDATE;

Line 3177: ahl_vwp_stages_b stage_obj, ahl_vwp_stages_b stage_sub

3173: --To fetch all the stage rules of a stage and get the visit subject and object stage ids corresponding to visit type subject and object stage ids
3174: CURSOR get_stage_rules (p_visit_type_stage_id IN NUMBER, p_visit_id IN NUMBER) IS
3175: SELECT stage_obj.stage_id object_id, stage_sub.stage_id subject_id, links.relation_type
3176: FROM ahl_vst_typ_stage_links links, ahl_visit_type_stages_b vt_obj, ahl_visit_type_stages_b vt_sub,
3177: ahl_vwp_stages_b stage_obj, ahl_vwp_stages_b stage_sub
3178: WHERE links.subject_id = p_visit_type_stage_id
3179: AND links.subject_id = vt_sub.visit_type_stage_id
3180: AND links.object_id = vt_obj.visit_type_stage_id
3181: AND vt_sub.stage_number = stage_sub.stage_num

Line 3573: FROM AHL_VWP_STAGES_B

3569: IS
3570: -- To find out the maximum task number value in the visit
3571: CURSOR c_stage_number IS
3572: SELECT FLOOR(nvl(MAX(stage_num),0)+1)
3573: FROM AHL_VWP_STAGES_B
3574: WHERE Visit_Id = p_visit_id;
3575:
3576: x_Visit_Stage_Number NUMBER;
3577: L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Visit_Stage_Number';

Line 3620: ahl_stage_links ,AHL_VWP_STAGES_B

3616:
3617: /* cursor to get all parallel relations of a visit */
3618: Cursor c_parallel_stages_dets(vst_id IN NUMBER) IS
3619: SELECT distinct STAGE_LINK_ID,subject_ID,object_ID FROM
3620: ahl_stage_links ,AHL_VWP_STAGES_B
3621: WHERE RELATION_TYPE='PARALLEL'
3622: AND ( ahl_stage_links.subject_id=AHL_VWP_STAGES_B.stage_id OR ahl_stage_links.object_id=AHL_VWP_STAGES_B.stage_id)
3623: AND VISIT_ID =vst_id;
3624:

Line 3622: AND ( ahl_stage_links.subject_id=AHL_VWP_STAGES_B.stage_id OR ahl_stage_links.object_id=AHL_VWP_STAGES_B.stage_id)

3618: Cursor c_parallel_stages_dets(vst_id IN NUMBER) IS
3619: SELECT distinct STAGE_LINK_ID,subject_ID,object_ID FROM
3620: ahl_stage_links ,AHL_VWP_STAGES_B
3621: WHERE RELATION_TYPE='PARALLEL'
3622: AND ( ahl_stage_links.subject_id=AHL_VWP_STAGES_B.stage_id OR ahl_stage_links.object_id=AHL_VWP_STAGES_B.stage_id)
3623: AND VISIT_ID =vst_id;
3624:
3625: l_parallel_stg_dets c_parallel_stages_dets%RowType;
3626:

Line 3846: ahl_stage_links ,AHL_VWP_STAGES_B

3842:
3843: /* cursor to get all parallel relations of a visit */
3844: Cursor c_parallel_stages_dets(vst_id IN NUMBER) IS
3845: SELECT distinct STAGE_LINK_ID,subject_ID,object_ID FROM
3846: ahl_stage_links ,AHL_VWP_STAGES_B
3847: WHERE RELATION_TYPE='PARALLEL'
3848: AND ( ahl_stage_links.subject_id=AHL_VWP_STAGES_B.stage_id OR ahl_stage_links.object_id=AHL_VWP_STAGES_B.stage_id)
3849: AND VISIT_ID =vst_id;
3850:

Line 3848: AND ( ahl_stage_links.subject_id=AHL_VWP_STAGES_B.stage_id OR ahl_stage_links.object_id=AHL_VWP_STAGES_B.stage_id)

3844: Cursor c_parallel_stages_dets(vst_id IN NUMBER) IS
3845: SELECT distinct STAGE_LINK_ID,subject_ID,object_ID FROM
3846: ahl_stage_links ,AHL_VWP_STAGES_B
3847: WHERE RELATION_TYPE='PARALLEL'
3848: AND ( ahl_stage_links.subject_id=AHL_VWP_STAGES_B.stage_id OR ahl_stage_links.object_id=AHL_VWP_STAGES_B.stage_id)
3849: AND VISIT_ID =vst_id;
3850:
3851: l_parallel_stg_dets c_parallel_stages_dets%RowType;
3852:

Line 3899: update AHL_VWP_STAGES_B set object_version_number=object_version_number+1,

3895: select max(START_DATE_TIME) into l_visit_start_date from AHL_VISITS_VL where VISIT_ID = p_visit_id;
3896: IF p_relation_type = 'BEFORE' THEN -- BEFORE
3897:
3898: --Update prev_stage_num to null as current relation is going to be deleted.
3899: update AHL_VWP_STAGES_B set object_version_number=object_version_number+1,
3900: PREV_STAGE_NUM = NULL,
3901: LAST_UPDATE_DATE = SYSDATE,
3902: LAST_UPDATED_BY = Fnd_Global.USER_ID,
3903: LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID

Line 3942: SELECT max(PLANNED_END_DATE) into l_prev_stage_planned_end_date from AHL_VWP_STAGES_B,AHL_STAGE_LINKS

3938: fnd_log.string(l_log_statement,L_DEBUG,'Before planned end date calc ');
3939: END IF;
3940:
3941: --Get previous stage planned end date
3942: SELECT max(PLANNED_END_DATE) into l_prev_stage_planned_end_date from AHL_VWP_STAGES_B,AHL_STAGE_LINKS
3943: WHERE subject_ID = STAGE_ID AND RELATION_TYPE='BEFORE' AND object_ID=l_update_stage_id AND VISIT_ID=p_visit_id;
3944:
3945: IF (l_log_statement >= l_log_current_level) THEN
3946: fnd_log.string(l_log_statement,L_DEBUG,'After planned end date calc ');

Line 3958: update AHL_VWP_STAGES_B set OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1,

3954: 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);
3955: END IF;
3956:
3957: -- update stage start date to previous stage planned end date
3958: update AHL_VWP_STAGES_B set OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1,
3959: PLANNED_START_DATE=l_prev_stage_planned_end_date,
3960: LAST_UPDATE_DATE = SYSDATE,
3961: LAST_UPDATED_BY = Fnd_Global.USER_ID,
3962: LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID

Line 4107: AHL_VWP_STAGES_B SUBSTG,

4103: FROM (
4104: SELECT SUBJECT_ID,OBJECT_ID FROM AHL_STAGE_LINKS
4105: WHERE OBJECT_ID = obj_id AND RELATION_TYPE ='BEFORE'
4106: ) SRules,
4107: AHL_VWP_STAGES_B SUBSTG,
4108: AHL_VWP_STAGES_B OBJSTG
4109: WHERE SUBSTG.STAGE_ID = SRules.SUBJECT_ID AND OBJSTG.STAGE_ID = SRules.OBJECT_ID;
4110:
4111: -- To get all parallel stages under visit

Line 4108: AHL_VWP_STAGES_B OBJSTG

4104: SELECT SUBJECT_ID,OBJECT_ID FROM AHL_STAGE_LINKS
4105: WHERE OBJECT_ID = obj_id AND RELATION_TYPE ='BEFORE'
4106: ) SRules,
4107: AHL_VWP_STAGES_B SUBSTG,
4108: AHL_VWP_STAGES_B OBJSTG
4109: WHERE SUBSTG.STAGE_ID = SRules.SUBJECT_ID AND OBJSTG.STAGE_ID = SRules.OBJECT_ID;
4110:
4111: -- To get all parallel stages under visit
4112: Cursor c_parallel_stages_dets(vst_id IN NUMBER) IS

Line 4114: ahl_stage_links ,AHL_VWP_STAGES_B

4110:
4111: -- To get all parallel stages under visit
4112: Cursor c_parallel_stages_dets(vst_id IN NUMBER) IS
4113: SELECT distinct STAGE_LINK_ID,subject_ID,object_ID FROM
4114: ahl_stage_links ,AHL_VWP_STAGES_B
4115: WHERE RELATION_TYPE='PARALLEL'
4116: AND ( ahl_stage_links.subject_id=AHL_VWP_STAGES_B.stage_id OR ahl_stage_links.object_id=AHL_VWP_STAGES_B.stage_id)
4117: AND VISIT_ID =vst_id;
4118:

Line 4116: AND ( ahl_stage_links.subject_id=AHL_VWP_STAGES_B.stage_id OR ahl_stage_links.object_id=AHL_VWP_STAGES_B.stage_id)

4112: Cursor c_parallel_stages_dets(vst_id IN NUMBER) IS
4113: SELECT distinct STAGE_LINK_ID,subject_ID,object_ID FROM
4114: ahl_stage_links ,AHL_VWP_STAGES_B
4115: WHERE RELATION_TYPE='PARALLEL'
4116: AND ( ahl_stage_links.subject_id=AHL_VWP_STAGES_B.stage_id OR ahl_stage_links.object_id=AHL_VWP_STAGES_B.stage_id)
4117: AND VISIT_ID =vst_id;
4118:
4119: l_parallel_stg_dets c_parallel_stages_dets%RowType;
4120:

Line 4128: from AHL_VISITS_B VST, AHL_VWP_STAGES_B STG

4124: SELECT VST.VISIT_ID,STAGE_ID,
4125: VST.department_id DEPT_ID, nvl(STG.duration,0) DURATION,
4126: CASE WHEN nvl(VST.start_date_time,STG.earliest_start_date) >= nvl(STG.earliest_start_date,VST.start_date_time)
4127: THEN VST.start_date_time ELSE STG.earliest_start_date END MAX_START_DATE
4128: from AHL_VISITS_B VST, AHL_VWP_STAGES_B STG
4129: WHERE VST.VISIT_ID = STG.VISIT_ID
4130: AND VST.VISIT_ID = vst_id
4131: AND STG.STAGE_ID = stg_id;
4132:

Line 4202: UPDATE AHL_VWP_STAGES_B SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,

4198:
4199: -- PRAKKUM :: 23-FEB-2011 :: Bug 13711800 :: Fix for Operational Visits
4200: l_planned_end_date := Compute_Stage_End_Date(p_visit_id, null, l_stg_start_date_dets.MAX_START_DATE, l_stg_start_date_dets.DEPT_ID, l_stg_start_date_dets.DURATION);
4201:
4202: UPDATE AHL_VWP_STAGES_B SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
4203: PLANNED_START_DATE = l_stg_start_date_dets.MAX_START_DATE,
4204: PLANNED_END_DATE = l_planned_end_date,
4205: LAST_UPDATE_DATE = SYSDATE,
4206: LAST_UPDATED_BY = Fnd_Global.USER_ID,

Line 4353: FROM AHL_VWP_STAGES_B

4349: --Cursor to fetch the stage number for the passed stage ID
4350: CURSOR c_stage_details(p_stage_id IN NUMBER)
4351: IS
4352: SELECT STAGE_NUM , PLANNED_END_DATE
4353: FROM AHL_VWP_STAGES_B
4354: WHERE stage_id = p_stage_id;
4355:
4356: -- To find stage link details
4357: CURSOR c_stage_link_dets (x_id IN NUMBER)

Line 4365: SELECT PREV_STAGE_NUM FROM AHL_VWP_STAGES_B

4361:
4362: -- Cursor to fetch prev stage number
4363: CURSOR c_get_prev_stage_dets (p_stage_id IN NUMBER)
4364: IS
4365: SELECT PREV_STAGE_NUM FROM AHL_VWP_STAGES_B
4366: WHERE STAGE_ID = p_stage_id;
4367:
4368: l_prev_stg_num NUMBER;
4369:

Line 4473: /*update AHL_VWP_STAGES_B set object_version_number=object_version_number+1,

4469: END IF;
4470:
4471: --IF l_prev_stg_num is NULL THEN
4472:
4473: /*update AHL_VWP_STAGES_B set object_version_number=object_version_number+1,
4474: PLANNED_START_DATE=l_prev_stage_planned_end_date,
4475: PREV_STAGE_NUM = l_stg_stage_number,
4476: LAST_UPDATE_DATE = SYSDATE,
4477: LAST_UPDATED_BY = Fnd_Global.USER_ID,

Line 4666: from ahl_vwp_stages_b stg,

4662: ELSE
4663: nvl(vst.start_date_time,stg.earliest_start_date)
4664: END stage_start_date , -- PRAKKUM :: 08-JUL-2011 :: VWPE 12730539 :: Stage start date w.r.t visit start date
4665: vst.department_id
4666: from ahl_vwp_stages_b stg,
4667: ahl_visits_vl vst
4668: WHERE
4669: vst.visit_id = stg.visit_id AND vst.VISIT_ID = x_id;
4670:

Line 4685: AHL_STAGE_LINKS STG_LK,AHL_VWP_STAGES_B STG

4681: cursor c_stage_rules_count (p_visit_id IN NUMBER)
4682: IS
4683: SELECT count(1) as cnt
4684: FROM
4685: AHL_STAGE_LINKS STG_LK,AHL_VWP_STAGES_B STG
4686: WHERE ( STG.STAGE_ID = STG_LK.subject_ID OR STG.STAGE_ID = STG_LK.object_ID )
4687: AND STG.VISIT_ID = p_visit_id;
4688: /*
4689: --cursor to find stages whose earliest start date not wintinh stage planned start date and end dates

Line 4691: SELECT STAGE_NUM FROM AHL_VWP_STAGES_B WHERE VISIT_ID =vst_id

4687: AND STG.VISIT_ID = p_visit_id;
4688: /*
4689: --cursor to find stages whose earliest start date not wintinh stage planned start date and end dates
4690: cursor c_get_invalid_stage_numbers(vst_id IN NUMBER) IS
4691: SELECT STAGE_NUM FROM AHL_VWP_STAGES_B WHERE VISIT_ID =vst_id
4692: AND
4693: (( PLANNED_END_DATE IS NOT NULL AND earliest_start_date IS NOT NULL AND earliest_start_date>PLANNED_END_DATE )
4694: OR
4695: ( PLANNED_START_DATE IS NOT NULL AND earliest_start_date IS NOT NULL AND earliest_start_date

Line 4753: UPDATE AHL_VWP_STAGES_B set object_version_number=object_version_number+1,

4749:
4750: IF l_visit_limited_dets.status_code='DRAFT' AND (l_dept_id IS NULL OR l_visit_start_date IS NULL OR (l_visit_limited_dets.visit_type_code IS NULL AND l_visit_limited_dets.IS_OLD_VISIT <> 'Y')) THEN
4751:
4752: -- On a draft visit if anything above seems null then stop processing and make all date calculations to null
4753: UPDATE AHL_VWP_STAGES_B set object_version_number=object_version_number+1,
4754: PLANNED_START_DATE=null,
4755: PLANNED_END_DATE=null,
4756: LAST_UPDATE_DATE = SYSDATE,
4757: LAST_UPDATED_BY = Fnd_Global.USER_ID,

Line 4803: UPDATE AHL_VWP_STAGES_B

4799: END IF;
4800:
4801: IF l_relations_count IS NULL OR l_relations_count=0 THEN
4802: --Update stages start date to null and return , since there is no rules defined between any of stages
4803: UPDATE AHL_VWP_STAGES_B
4804: set OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1,
4805: PLANNED_START_DATE=l_visit_start_date,
4806: PLANNED_END_DATE=Compute_Stage_End_Date(p_visit_id,
4807: l_visit_limited_dets.IS_OPERATIONAL_VISIT,

Line 4852: AHL_STAGE_LINKS STG_LK,AHL_VWP_STAGES_B STG

4848:
4849: SELECT distinct STG_LK.subject_ID, STG_LK.object_ID, STG_LK.RELATION_TYPE
4850: BULK COLLECT INTO l_stages_links
4851: FROM
4852: AHL_STAGE_LINKS STG_LK,AHL_VWP_STAGES_B STG
4853: WHERE ( STG.STAGE_ID = STG_LK.subject_ID OR STG.STAGE_ID = STG_LK.object_ID )
4854: AND STG.VISIT_ID = p_visit_id;
4855:
4856: IF (l_log_statement >= l_log_current_level) THEN

Line 5626: UPDATE AHL_VWP_STAGES_B SET object_version_number=object_version_number+1,

5622: fnd_log.string(l_log_statement,L_DEBUG,'SD '||l_stage_start_date||' ED '||l_stage_end_date ||' for stage id '||l_stage_id);
5623: END IF;
5624:
5625: -- Stage dates are updated
5626: UPDATE AHL_VWP_STAGES_B SET object_version_number=object_version_number+1,
5627: PLANNED_START_DATE = l_stage_start_date,
5628: PLANNED_END_DATE = l_stage_end_date,
5629: LAST_UPDATE_DATE = SYSDATE,
5630: LAST_UPDATED_BY = Fnd_Global.USER_ID,

Line 5720: ahl_stage_links ,AHL_VWP_STAGES_B

5716:
5717: -- To get all parallel stages under visit
5718: Cursor c_parallel_stages_dets(vst_id IN NUMBER) IS
5719: SELECT distinct STAGE_LINK_ID,subject_ID,object_ID FROM
5720: ahl_stage_links ,AHL_VWP_STAGES_B
5721: WHERE RELATION_TYPE='PARALLEL'
5722: AND ( ahl_stage_links.subject_id=AHL_VWP_STAGES_B.stage_id OR ahl_stage_links.object_id=AHL_VWP_STAGES_B.stage_id)
5723: AND VISIT_ID =vst_id;
5724:

Line 5722: AND ( ahl_stage_links.subject_id=AHL_VWP_STAGES_B.stage_id OR ahl_stage_links.object_id=AHL_VWP_STAGES_B.stage_id)

5718: Cursor c_parallel_stages_dets(vst_id IN NUMBER) IS
5719: SELECT distinct STAGE_LINK_ID,subject_ID,object_ID FROM
5720: ahl_stage_links ,AHL_VWP_STAGES_B
5721: WHERE RELATION_TYPE='PARALLEL'
5722: AND ( ahl_stage_links.subject_id=AHL_VWP_STAGES_B.stage_id OR ahl_stage_links.object_id=AHL_VWP_STAGES_B.stage_id)
5723: AND VISIT_ID =vst_id;
5724:
5725: l_parallel_stg_dets c_parallel_stages_dets%RowType;
5726:

Line 5736: FROM AHL_VWP_STAGES_B

5732:
5733: --cursor to get invalid stages
5734: cursor c_get_invalid_stage_numbers(vst_id IN NUMBER) IS
5735: SELECT STAGE_NUM,planned_start_date,planned_end_date
5736: FROM AHL_VWP_STAGES_B
5737: WHERE PLANNED_END_DATE IS NOT NULL
5738: AND PLANNED_START_DATE IS NOT NULL
5739: and PLANNED_END_DATE < PLANNED_START_DATE AND VISIT_ID =vst_id;
5740:

Line 5804: l_query_str :=' select MAX(stg.PLANNED_END_DATE) from ahl_stage_links, ahl_vwp_stages_b stg '||

5800: fnd_log.string(l_log_statement,L_DEBUG,'parallelStagesInStr is :' ||parallelStagesInStr );
5801: END IF;
5802:
5803: -- Get max planned end date from all parallel stage before stages
5804: l_query_str :=' select MAX(stg.PLANNED_END_DATE) from ahl_stage_links, ahl_vwp_stages_b stg '||
5805: ' where stg.STAGE_ID = subject_id AND relation_type = ''BEFORE'' '||
5806: ' and object_id in (' || parallelStagesInStr ||')';
5807:
5808: IF (l_log_statement >= l_log_current_level) THEN

Line 5821: ' MAX(stg.EARLIEST_START_DATE) MAX_EARLIEST_START_DATE from ahl_vwp_stages_b stg '||

5817:
5818: -- PRAKKUM :: 09/05/2012 :: Bug 13965577
5819: -- Get max earliest start date from all parallel stages
5820: l_query_str :=' select '||
5821: ' MAX(stg.EARLIEST_START_DATE) MAX_EARLIEST_START_DATE from ahl_vwp_stages_b stg '||
5822: ' where stg.STAGE_ID in (' || parallelStagesInStr ||')';
5823:
5824: IF (l_log_statement >= l_log_current_level) THEN
5825: fnd_log.string(l_log_statement,L_DEBUG,'l_query_str is :' ||l_query_str);

Line 5837: ' else MAX(stg.PLANNED_START_DATE) end MIN_PLANNED_START_DATE from ahl_vwp_stages_b stg '||

5833:
5834: -- Get max planned start date from all parallel stages considering if earliest start date exists
5835: l_query_str :=' select case when '||
5836: ' (MAX(stg.EARLIEST_START_DATE)>MAX(stg.PLANNED_START_DATE)) then MAX(stg.EARLIEST_START_DATE) '||
5837: ' else MAX(stg.PLANNED_START_DATE) end MIN_PLANNED_START_DATE from ahl_vwp_stages_b stg '||
5838: ' where stg.STAGE_ID in (' || parallelStagesInStr ||')';
5839:
5840: IF (l_log_statement >= l_log_current_level) THEN
5841: fnd_log.string(l_log_statement,L_DEBUG,'l_query_str is :' ||l_query_str);

Line 5880: l_query_str :=' UPDATE AHL_VWP_STAGES_B set object_version_number=object_version_number+1, '||

5876: fnd_log.string(l_log_statement,L_DEBUG,'l_planned_end_date is :' ||TO_CHAR(l_planned_end_date,'DD-MON-YYYY HH24:MI:SS') );
5877: END IF;
5878:
5879:
5880: l_query_str :=' UPDATE AHL_VWP_STAGES_B set object_version_number=object_version_number+1, '||
5881: ' PLANNED_START_DATE = :p_planned_end_date, '||
5882: ' LAST_UPDATE_DATE = SYSDATE, '||
5883: ' LAST_UPDATED_BY = Fnd_Global.USER_ID, '||
5884: ' LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID '||

Line 6186: ahl_vwp_stages_b stg

6182: --To get invalid stages whose end date falls beyond visit end date
6183: CURSOR get_invalid_stages(c_visit_id IN NUMBER) IS
6184: SELECT stg.stage_id,stg.stage_num,stg.planned_end_date,vst.close_date_time
6185: FROM ahl_visits_b vst,
6186: ahl_vwp_stages_b stg
6187: WHERE vst.visit_id = stg.visit_id
6188: AND TRUNC(stg.planned_end_date,'MI') > TRUNC(nvl(vst.close_date_time,stg.planned_end_date),'MI')
6189: AND vst.visit_id = c_visit_id
6190: AND vst.status_code <> 'DRAFT'