[Home] [Help]
1435: /* 12 Jul 08: Modified for performance.
1436: CURSOR get_visit_mr_details_csr(c_instance_id NUMBER, c_visit_type_code VARCHAR2) IS
1437: SELECT A.mr_header_id, A.mr_effectivity_id, A.relationship_id, A.pc_node_id, A.inventory_item_id,
1438: cir.object_id, cir.subject_id, cir.position_reference
1439: FROM ahl_mr_effectivities A, ahl_mr_headers_app_v MR, ahl_mr_visit_types vis,
1440: (select cir2.object_id,
1441: cii2.instance_id subject_id,
1442: nvl(uc.master_config_id, cir2.position_reference) position_reference,
1443: 0 depth
1486: --cir.object_id,
1487: cir.subject_id,
1488: --cir.position_reference ,
1489: cir.depth
1490: FROM ahl_mr_headers_app_v MR, ahl_mr_visit_types vis, cir,
1491: ahl_mr_effectivities A
1492: WHERE MR.mr_header_id = A.mr_header_id
1493: AND A.mr_header_id = vis.mr_header_id
1494: AND vis.mr_visit_type_code = c_visit_type_code
1511: --cir.object_id,
1512: cir.subject_id,
1513: --cir.position_reference,
1514: cir.depth
1515: FROM ahl_mr_headers_app_v MR, ahl_mr_visit_types vis, ahl_mc_path_positions mcp,
1516: --ahl_mc_headers_b hdr, ahl_mc_relationships rel,
1517: cir, ahl_mr_effectivities A
1518: WHERE MR.mr_header_id = A.mr_header_id
1519: AND A.mr_header_id = vis.mr_header_id
1560: CONNECT BY object_id = PRIOR subject_id
1561: AND relationship_type_code = 'COMPONENT-OF'
1562: AND SYSDATE between trunc(nvl(active_start_date,sysdate))
1563: AND trunc(NVL(active_end_date,sysdate+1))
1564: ) cir, csi_item_instances cii, ahl_mr_visit_types vis,
1565: ahl_mr_effectivities A
1566: WHERE A.mr_header_id = vis.mr_header_id
1567: AND vis.mr_visit_type_code = c_visit_type_code
1568: AND cir.subject_id = cii.instance_id
1592: WHERE cii.instance_id = ciea1.instance_id(+) AND ciea1.attribute_code(+) = 'AHL_MFG_DATE'
1593: AND ciea1.attribute_level(+) = 'GLOBAL') mfg_date,
1594: cii.serial_number --,
1595: --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
1596: FROM csi_item_instances cii,ahl_mr_visit_types vis,
1597: ahl_mr_effectivities A
1598: WHERE A.mr_header_id = vis.mr_header_id
1599: AND vis.mr_visit_type_code = c_visit_type_code
1600: AND cii.instance_id = c_instance_id
1623: WHERE ciea1.instance_id = cii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
1624: AND ciea1.attribute_level = 'GLOBAL') mfg_date,
1625: cii.serial_number --,
1626: --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
1627: FROM ahl_applicable_instances aai,csi_item_instances cii,ahl_mr_visit_types vis,
1628: ahl_mr_effectivities A
1629: WHERE A.mr_header_id = vis.mr_header_id
1630: AND vis.mr_visit_type_code = c_visit_type_code
1631: AND A.relationship_id IS NOT NULL
1669: -- check for path position based effectivities for visit type
1670: CURSOR relationship_vtype_csr(p_visit_type_code IN VARCHAR2) IS
1671: SELECT 'x' from dual
1672: WHERE exists (select 'x'
1673: from ahl_mr_effectivities mre, ahl_mr_visit_types vis
1674: where vis.mr_visit_type_code = p_visit_type_code
1675: and mre.mr_header_id = vis.mr_header_id
1676: and mre.relationship_id is not null
1677: and exists (SELECT 'x' from ahl_mr_headers_app_v MR
3330: AHL_UMP_UTIL_PKG.process_group_mrs;
3331:
3332: -- Delete visit types that do not match..
3333: DELETE AHL_APPLICABLE_MRS A
3334: WHERE NOT EXISTS (SELECT 'x' FROM AHL_MR_VISIT_TYPES
3335: WHERE MR_HEADER_ID=A.MR_HEADER_ID
3336: AND MR_VISIT_TYPE_CODE = P_VISIT_TYPE_CODE
3337: );
3338: