[Home] [Help]
1825: /* 12 Jul 08: Modified for performance.
1826: CURSOR get_visit_mr_details_csr(c_instance_id NUMBER, c_visit_type_code VARCHAR2) IS
1827: SELECT A.mr_header_id, A.mr_effectivity_id, A.relationship_id, A.pc_node_id, A.inventory_item_id,
1828: cir.object_id, cir.subject_id, cir.position_reference
1829: FROM ahl_mr_effectivities A, ahl_mr_headers_app_v MR, ahl_mr_visit_types vis,
1830: (select cir2.object_id,
1831: cii2.instance_id subject_id,
1832: nvl(uc.master_config_id, cir2.position_reference) position_reference,
1833: 0 depth
1876: --cir.object_id,
1877: cir.subject_id,
1878: --cir.position_reference ,
1879: cir.depth
1880: FROM ahl_mr_headers_app_v MR, ahl_mr_visit_types vis, cir,
1881: ahl_mr_effectivities A
1882: WHERE MR.mr_header_id = A.mr_header_id
1883: AND A.mr_header_id = vis.mr_header_id
1884: AND vis.mr_visit_type_code = c_visit_type_code
1901: --cir.object_id,
1902: cir.subject_id,
1903: --cir.position_reference,
1904: cir.depth
1905: FROM ahl_mr_headers_app_v MR, ahl_mr_visit_types vis, ahl_mc_path_positions mcp,
1906: --ahl_mc_headers_b hdr, ahl_mc_relationships rel,
1907: cir, ahl_mr_effectivities A
1908: WHERE MR.mr_header_id = A.mr_header_id
1909: AND A.mr_header_id = vis.mr_header_id
1954: CONNECT BY object_id = PRIOR subject_id
1955: --AND relationship_type_code = 'COMPONENT-OF'
1956: --AND SYSDATE between trunc(nvl(active_start_date,sysdate))
1957: --AND trunc(NVL(active_end_date,sysdate+1))
1958: ) cir, csi_item_instances cii, ahl_mr_visit_types vis,
1959: ahl_mr_effectivities A
1960: WHERE A.mr_header_id = vis.mr_header_id
1961: AND vis.mr_visit_type_code = c_visit_type_code
1962: AND cir.subject_id = cii.instance_id
1988: WHERE cii.instance_id = ciea1.instance_id(+) AND ciea1.attribute_code(+) = 'AHL_MFG_DATE'
1989: AND ciea1.attribute_level(+) = 'GLOBAL') mfg_date,
1990: cii.serial_number --,
1991: --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
1992: FROM csi_item_instances cii,ahl_mr_visit_types vis,
1993: ahl_mr_effectivities A
1994: WHERE A.mr_header_id = vis.mr_header_id
1995: AND vis.mr_visit_type_code = c_visit_type_code
1996: AND cii.instance_id = c_instance_id
2021: WHERE ciea1.instance_id = cii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
2022: AND ciea1.attribute_level = 'GLOBAL') mfg_date,
2023: cii.serial_number --,
2024: --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
2025: FROM ahl_applicable_instances aai,csi_item_instances cii,ahl_mr_visit_types vis,
2026: ahl_mr_effectivities A
2027: WHERE A.mr_header_id = vis.mr_header_id
2028: AND vis.mr_visit_type_code = c_visit_type_code
2029: AND A.relationship_id IS NOT NULL
2070: -- check for path position based effectivities for visit type
2071: CURSOR relationship_vtype_csr(p_visit_type_code IN VARCHAR2) IS
2072: SELECT 'x' from dual
2073: WHERE exists (select 'x'
2074: from ahl_mr_effectivities mre, ahl_mr_visit_types vis, ahl_mr_headers_b mr
2075: where vis.mr_visit_type_code = p_visit_type_code
2076: and mre.mr_header_id = vis.mr_header_id
2077: and mre.mr_header_id = mr.mr_header_id
2078: and mre.relationship_id is not null
4294: AHL_UMP_UTIL_PKG.process_group_mrs;
4295:
4296: -- Delete visit types that do not match..
4297: DELETE AHL_APPLICABLE_MRS A
4298: WHERE NOT EXISTS (SELECT 'x' FROM AHL_MR_VISIT_TYPES
4299: WHERE MR_HEADER_ID=A.MR_HEADER_ID
4300: AND MR_VISIT_TYPE_CODE = P_VISIT_TYPE_CODE
4301: );
4302: