[Home] [Help]
191: -- SURRKUMA :: Service Bulletin :: 07-Jun-2011
192: -- Modified the cursor to fetch UC header id too, for getting the affected SB rule later
193: CURSOR get_mc_dtls_csr(c_instance_id IN NUMBER) IS
194: select UC.MASTER_CONFIG_ID, MC.MC_ID, MC.VERSION_NUMBER,UC.UNIT_CONFIG_HEADER_ID
195: from AHL_UNIT_CONFIG_HEADERS UC, AHL_MC_HEADERS_B MC
196: where UC.CSI_ITEM_INSTANCE_ID = c_instance_id AND
197: MC.MC_HEADER_ID = UC.MASTER_CONFIG_ID;
198:
199: CURSOR get_item_from_instance_csr(c_instance_id IN NUMBER) IS
907: p_x_disp_req_list IN OUT NOCOPY Route_Mtl_Req_Tbl_Type) IS
908:
909: CURSOR get_sub_unit_mc_csr(c_pos_instance_id IN NUMBER) IS
910: SELECT master_config_id
911: FROM AHL_UNIT_CONFIG_HEADERS
912: WHERE CSI_ITEM_INSTANCE_ID = c_pos_instance_id;
913:
914: CURSOR get_item_grp_for_mc_csr(c_mc_header_id IN NUMBER) IS
915: SELECT item_group_id
1701: MC_HEADER_ID = c_mc_header_id;
1702:
1703: CURSOR get_config_dtls_csr(c_unit_instance_id IN NUMBER) IS
1704: SELECT master_config_id
1705: FROM ahl_unit_config_headers
1706: WHERE CSI_ITEM_INSTANCE_ID = c_unit_instance_id AND
1707: NVL(ACTIVE_START_DATE, SYSDATE - 1) <= SYSDATE AND
1708: NVL(ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE;
1709:
1882: p_item_instance_id IN NUMBER) RETURN NUMBER IS
1883:
1884: CURSOR chk_inst_is_unit_csr IS
1885: SELECT 'x'
1886: FROM ahl_unit_config_headers
1887: WHERE csi_item_instance_id = p_item_instance_id
1888: AND nvl(active_end_date, SYSDATE+1) > SYSDATE
1889: AND nvl(active_start_date, SYSDATE) <= SYSDATE;
1890:
1891: CURSOR get_parent_instance_csr IS
1892: SELECT object_id
1893: FROM csi_ii_relationships
1894: WHERE object_id IN (SELECT csi_item_instance_id
1895: FROM ahl_unit_config_headers
1896: WHERE nvl(active_end_date, SYSDATE+1) > SYSDATE
1897: AND nvl(active_start_date, SYSDATE) <= SYSDATE)
1898: START WITH subject_id = p_item_instance_id
1899: AND relationship_type_code = 'COMPONENT-OF'
2022: FROM (
2023: SELECT SB.rule_id,
2024: SB.rule_sequence
2025: FROM ahl_sb_position_rules SB,
2026: ahl_unit_config_headers UC
2027: WHERE UC.unit_config_header_id = c_uc_header_id
2028: AND SB.mc_header_id = UC.master_config_id
2029: -- take only the rules which have MRs accomplished on the unit
2030: AND EXISTS (SELECT 'X'
2048: SELECT SB.rule_id,
2049: SB.rule_sequence
2050: FROM AHL_SB_POSITION_RULES SB,
2051: AHL_VISIT_TASKS_B VT,
2052: AHL_UNIT_CONFIG_HEADERS UC
2053: WHERE UC.unit_config_header_id = c_uc_header_id
2054: AND SB.mc_header_id = UC.master_config_id
2055: -- take only the rules which have MRs attached to the visit
2056: AND VT.visit_id = c_visit_id
2541:
2542: -- Cursor to get the UC header Id from the instance Id
2543: CURSOR get_uc_header_csr(c_instance_id IN NUMBER) IS
2544: SELECT unit_config_header_id uc_header_id
2545: FROM ahl_unit_config_headers
2546: WHERE csi_item_instance_id = c_instance_id;
2547:
2548: -- cursor to get applicable SB rules for the given unit and position
2549: -- NOTE::Fetch only once from this cursor. UNION ALL is fine as it will be fetched only once.
2555: FROM (
2556: SELECT SB.rule_id,
2557: SB.rule_sequence
2558: FROM ahl_sb_position_rules SB,
2559: ahl_unit_config_headers UC
2560: WHERE UC.unit_config_header_id = c_uc_header_id
2561: AND SB.mc_header_id = UC.master_config_id
2562: -- take only the rules which have MRs accomplished on the unit
2563: AND EXISTS (SELECT 'X'
2581: SELECT SB.rule_id,
2582: SB.rule_sequence
2583: FROM AHL_SB_POSITION_RULES SB,
2584: AHL_VISIT_TASKS_B VT,
2585: AHL_UNIT_CONFIG_HEADERS UC
2586: WHERE UC.unit_config_header_id = c_uc_header_id
2587: AND SB.mc_header_id = UC.master_config_id
2588: -- take only the rules which have MRs attached to the visit
2589: AND VT.visit_id = c_visit_id