[Home] [Help]
1366:
1367: Cursor c_stage_links(x_id IN NUMBER) IS
1368: SELECT distinct STG_LK.STAGE_LINK_ID, STG_LK.OBJECT_ID, STG_LK.SUBJECT_ID, STG_LK.RELATION_TYPE
1369: FROM
1370: AHL_STAGE_LINKS STG_LK
1371: WHERE ( STG_LK.OBJECT_ID = x_id OR STG_LK.SUBJECT_ID = x_id );
1372:
1373: l_stagelinks_rec c_stage_links%ROWTYPE;
1374:
1498: FOR l_stagelinks_rec IN c_stage_links(p_x_stages_tbl(i).STAGE_ID)
1499: LOOP
1500:
1501: --Delete stage rule
1502: delete from AHL_STAGE_LINKS where STAGE_LINK_ID = l_stagelinks_rec.STAGE_LINK_ID;
1503:
1504: UPDATE_AS_STAGE_RULE_DEL (
1505: p_api_version => p_api_version,
1506: p_init_msg_list => Fnd_Api.g_false,
1730: -- directly delete as we need to delete all stages for the visit
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
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
1740: WHERE VISIT_ID = p_visit_id);
1741:
1742: -- VWPE :: ER 12424063 :: PRAKKUM :: 05-MAY-2011 :: END
2094:
2095: -- For the passed stage id, get all its successive parent stages
2096: CURSOR c_get_parent_stages(c_stage_id IN NUMBER)
2097: IS
2098: select link.subject_id from ahl_stage_links link
2099: start with link.object_id = c_stage_id
2100: connect by prior link.subject_id = link.object_id;
2101:
2102: -- For the passed stage id, get all its successive child stages
2101:
2102: -- For the passed stage id, get all its successive child stages
2103: CURSOR c_get_child_stages(c_stage_id IN NUMBER)
2104: IS
2105: select link.object_id from ahl_stage_links link
2106: start with link.subject_id = c_stage_id
2107: connect by prior link.object_id = link.subject_id;
2108:
2109: --Get stage id for a given task
2466: BEGIN
2467: x_return_status:=FND_API.G_RET_STS_SUCCESS;
2468:
2469: SELECT COUNT(*) INTO l_counter
2470: FROM AHL_STAGE_LINKS A
2471: START WITH OBJECT_ID = P_CHILD_STAGE_ID
2472: CONNECT BY PRIOR OBJECT_ID =SUBJECT_ID;
2473: EXCEPTION
2474: WHEN l_cyclic_loop THEN
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:
2578:
2579: -- Get the passed stage parent id and all its successive parent stages
2580: CURSOR c_get_parent_stages(p_subject_id IN NUMBER, p_object_id IN NUMBER)
2581: IS
2582: SELECT link.subject_id FROM ahl_stage_links link
2583: START WITH link.object_id = p_object_id
2584: AND link.subject_id = p_subject_id
2585: CONNECT BY PRIOR link.subject_id = link.object_id;
2586:
2586:
2587: -- Get the passed stage child id and all its successive child stages
2588: CURSOR c_get_child_stages(p_subject_id IN NUMBER, p_object_id IN NUMBER)
2589: IS
2590: SELECT link.object_id FROM ahl_stage_links link
2591: START WITH link.subject_id = p_subject_id
2592: AND link.object_id = p_object_id
2593: CONNECT BY PRIOR link.object_id = link.subject_id;
2594:
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';
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'
2614: --Cursor to find duplicate rules
2615: CURSOR c_get_stage_is_dup (p_sub_id IN NUMBER,p_obj_id IN NUMBER)
2616: IS
2617: SELECT count(1) -- PRAKKUM :: 06/03/2011 :: VWPE :: ER 12424063 :: Fixed issue in getting parallel stage ids for a stage
2618: FROM ahl_stage_links link
2619: 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 ) ;
2620:
2621: --PRAKKUM :: VWPE :: ER 12424063 :: 31-MAY-2011 ::
2622: --Cursor to validate whether all rules are over planning stages or not
2722: l_object_id := p_stage_rule_rec.SUBJECT_STAGE_ID;
2723: ELSE
2724: l_object_id := p_stage_rule_rec.OBJECT_STAGE_ID;
2725: END IF;
2726: UPDATE ahl_stage_links
2727: SET object_id = stage_parallel_rules1.subject_id,
2728: subject_id = stage_parallel_rules1.object_id
2729: WHERE stage_link_id = stage_parallel_rules1.stage_link_id;
2730: IF l_object_id IS NOT NULL THEN
2743: l_object_id := p_stage_rule_rec.SUBJECT_STAGE_ID;
2744: ELSE
2745: l_object_id := p_stage_rule_rec.OBJECT_STAGE_ID;
2746: END IF;
2747: UPDATE ahl_stage_links
2748: SET object_id = stage_parallel_rules2.subject_id,
2749: subject_id = stage_parallel_rules2.object_id
2750: WHERE stage_link_id = stage_parallel_rules2.stage_link_id;
2751: IF l_object_id IS NOT NULL THEN
2758: P_PARENT_STAGE_NUMBER =>l_parent_stage_num
2759: );
2760: END IF;
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';
3415: L_DEBUG_KEY,
3416: '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);
3417: END IF;
3418:
3419: INSERT INTO AHL_STAGE_LINKS (
3420: STAGE_LINK_ID,
3421: OBJECT_ID,
3422: SUBJECT_ID,
3423: RELATION_TYPE,
3430: LAST_UPDATE_LOGIN
3431: )
3432: values
3433: (
3434: AHL_STAGE_LINKS_S.nextval,
3435: l_stage_rules_rec.object_id,
3436: l_stage_rules_rec.subject_id,
3437: l_stage_rules_rec.relation_type,
3438: 1,
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:
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:
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:
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:
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 ');
4074: --Cursor to fetch the stage number for the passed stage ID
4075: CURSOR c_stage_link_details(p_stage_id IN NUMBER)
4076: IS
4077: SELECT DISTINCT SUBJECT_ID, OBJECT_ID , RELATION_TYPE
4078: FROM AHL_STAGE_LINKS
4079: WHERE SUBJECT_ID =p_stage_id OR OBJECT_ID = p_stage_id;
4080:
4081: l_Stage_link_dets c_stage_link_details%RowType;
4082:
4100: SUBSTG.STAGE_ID SUB_STAGE_ID,
4101: SUBSTG.PLANNED_END_DATE SUB_PLANNED_END_DATE ,
4102: SUBSTG.VISIT_ID SUB_VISIT_ID
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
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:
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:
4355:
4356: -- To find stage link details
4357: CURSOR c_stage_link_dets (x_id IN NUMBER)
4358: IS
4359: SELECT * FROM AHL_STAGE_LINKS
4360: WHERE STAGE_LINK_ID = x_id;
4361:
4362: -- Cursor to fetch prev stage number
4363: CURSOR c_get_prev_stage_dets (p_stage_id IN NUMBER)
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
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
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:
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:
5725: l_parallel_stg_dets c_parallel_stages_dets%RowType;
5726:
5727: --To get next stage to passed stage id
5728: cursor c_get_after_stage(stg_id IN NUMBER) IS
5729: select object_ID object_ID FROM AHL_STAGE_LINKS WHERE RELATION_TYPE='BEFORE' and subject_ID=stg_id
5730: UNION
5731: select subject_ID object_ID FROM AHL_STAGE_LINKS WHERE RELATION_TYPE='AFTER' and object_ID=stg_id;
5732:
5733: --cursor to get invalid stages
5727: --To get next stage to passed stage id
5728: cursor c_get_after_stage(stg_id IN NUMBER) IS
5729: select object_ID object_ID FROM AHL_STAGE_LINKS WHERE RELATION_TYPE='BEFORE' and subject_ID=stg_id
5730: UNION
5731: select subject_ID object_ID FROM AHL_STAGE_LINKS WHERE RELATION_TYPE='AFTER' and object_ID=stg_id;
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
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