[Home] [Help]
587: x_return_val OUT NOCOPY BOOLEAN) IS
588:
589: CURSOR l_ahl_relationship_csr(p_relationship_id IN NUMBER) IS
590: SELECT position_ref_code
591: FROM AHL_MC_RELATIONSHIPS
592: WHERE relationship_id = p_relationship_id
593: AND TRUNC(SYSDATE) >= TRUNC(NVL(active_start_date, SYSDATE))
594: AND TRUNC(SYSDATE) < TRUNC(NVL(active_end_date, SYSDATE+1));
595:
593: AND TRUNC(SYSDATE) >= TRUNC(NVL(active_start_date, SYSDATE))
594: AND TRUNC(SYSDATE) < TRUNC(NVL(active_end_date, SYSDATE+1));
595:
596:
597: l_position_ref_code ahl_mc_relationships.position_ref_code%TYPE DEFAULT NULL;
598:
599: l_return_val BOOLEAN DEFAULT TRUE;
600:
601: BEGIN
649: reln.quantity Posn_Qty,
650: reln.uom_code Posn_uom_code,
651: iasso.revision Itm_revision,
652: iasso.item_association_id
653: FROM ahl_mc_relationships reln, ahl_item_associations_b iasso
654: WHERE reln.item_group_id = iasso.item_group_id
655: AND reln.relationship_id = p_mc_relationship_id
656: AND iasso.inventory_item_id = p_Inventory_id
657: AND iasso.inventory_org_id = p_Organization_id
670: reln.quantity Posn_Qty,
671: reln.uom_code Posn_uom_code,
672: iasso.revision Itm_revision,
673: iasso.item_association_id
674: FROM ahl_mc_relationships reln, ahl_item_associations_b iasso
675: WHERE reln.item_group_id = iasso.item_group_id
676: AND reln.relationship_id = p_mc_relationship_id
677: AND iasso.inventory_item_id = p_Inventory_id
678: AND (iasso.revision IS NULL OR iasso.revision = p_revision)
685: -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 04-Dec-2007
686: -- Cursor to fetch the position lookup meaning for a given relationship id.
687: CURSOR get_pos_lkup_meaning(p_mc_relationship_id IN NUMBER) IS
688: SELECT fnd.lookup_code, fnd.meaning position_ref_meaning
689: FROM ahl_mc_relationships mcr, fnd_lookup_values_vl fnd
690: WHERE mcr.relationship_id = p_mc_relationship_id
691: AND mcr.position_ref_code = fnd.lookup_code
692: AND fnd.lookup_type = 'AHL_POSITION_REFERENCE';
693:
993:
994: -- Cursor to read the master config tree.
995: CURSOR ahl_relationships_csr (p_mc_relationship_id IN NUMBER) IS
996: SELECT relationship_id, position_ref_code, level
997: FROM ahl_mc_relationships
998: START WITH parent_relationship_id = p_mc_relationship_id
999: AND TRUNC(SYSDATE) >= TRUNC(NVL(active_start_date, SYSDATE))
1000: AND TRUNC(SYSDATE) < TRUNC(NVL(active_end_date, SYSDATE+1))
1001: CONNECT BY PRIOR relationship_id = parent_relationship_id
1316:
1317: -- Get all the positions associated to this item group.
1318: CURSOR get_associated_posns_csr(c_item_group_id IN NUMBER) IS
1319: SELECT relationship_id
1320: FROM ahl_mc_relationships rel, ahl_mc_headers_b hdr
1321: WHERE trunc(nvl(rel.active_end_date,sysdate+1)) > trunc(sysdate)
1322: AND trunc(nvl(rel.active_start_date,sysdate-1)) < trunc(sysdate)
1323: AND hdr.mc_header_id = rel.mc_header_id
1324: AND rel.item_group_id = c_item_group_id
1342: CURSOR chk_top_node_csr(c_relationship_id IN NUMBER,
1343: c_inventory_item_id IN NUMBER,
1344: c_inventory_org_id IN NUMBER) IS
1345: SELECT uc.csi_item_instance_id, uc.unit_config_header_id, uc.parent_uc_header_id, uc.unit_config_status_code
1346: FROM ahl_unit_config_headers uc, csi_item_instances csi, ahl_mc_relationships mc
1347: WHERE uc.csi_item_instance_id = csi.instance_id
1348: AND uc.master_config_id = mc.mc_header_id
1349: AND mc.relationship_id = c_relationship_id
1350: AND TRUNC(SYSDATE) < TRUNC(NVL(uc.active_end_date, SYSDATE+1))
2173: A.uom_code item_uom_code,
2174: A.revision item_revision,
2175: R.quantity position_quantity,
2176: R.uom_code position_uom_code
2177: FROM ahl_mc_relationships R,
2178: ahl_item_associations_b A
2179: WHERE R.item_group_id = A.item_group_id
2180: AND R.relationship_id = c_mc_relationship_id
2181: AND A.inventory_item_id = c_inventory_item_id
2311: --
2312: CURSOR get_top_rel_id (csi_id IN NUMBER)
2313: IS
2314: SELECT rel.relationship_id
2315: FROM AHL_MC_RELATIONSHIPS rel,
2316: AHL_UNIT_CONFIG_HEADERS uch
2317: WHERE rel.mc_header_id = uch.master_config_id
2318: AND rel.parent_relationship_id IS NULL
2319: AND uch.csi_item_instance_id = csi_id;
2630:
2631: -- Get all the positions associated to this item group.
2632: CURSOR c_get_associated_posns_csr(c_item_group_id NUMBER) IS
2633: SELECT relationship_id
2634: FROM AHL_MC_RELATIONSHIPS REL, AHL_MC_HEADERS_B HDR
2635: WHERE HDR.mc_header_id = REL.mc_header_id AND
2636: REL.item_group_id = c_item_group_id AND
2637: HDR.config_status_code NOT IN ('EXPIRED','CLOSED') AND
2638: TRUNC(NVL(REL.active_end_date,SYSDATE+1)) > TRUNC(SYSDATE) AND
2658: c_inventory_org_id NUMBER) IS
2659: SELECT UC.name,
2660: UC.unit_config_header_id
2661: FROM AHL_UNIT_CONFIG_HEADERS UC, CSI_ITEM_INSTANCES CSI,
2662: AHL_MC_RELATIONSHIPS MC
2663: WHERE UC.csi_item_instance_id = CSI.instance_id AND
2664: UC.master_config_id = MC.mc_header_id AND
2665: UC.parent_uc_header_id IS NULL AND
2666: MC.parent_relationship_id IS NULL AND
2886: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
2887:
2888: CURSOR get_root_mc_ig_csr (c_mc_header_id IN NUMBER) IS
2889: SELECT reln.relationship_id, reln.item_group_id
2890: FROM ahl_mc_relationships reln
2891: WHERE reln.mc_header_id = c_mc_header_id
2892: AND nvl(reln.active_start_date, sysdate - 1) <= sysdate
2893: AND nvl(reln.active_end_date, sysdate + 1) > sysdate
2894: AND reln.parent_relationship_id is null;
2902: AND NVL(ACTIVE_END_DATE, sysdate + 1) > sysdate;
2903:
2904: CURSOR get_mc_ig_csr(c_pos_ref IN VARCHAR2) IS
2905: SELECT reln.item_group_id
2906: FROM ahl_mc_relationships reln
2907: WHERE relationship_id = TO_NUMBER(c_pos_ref);
2908:
2909: CURSOR item_group_has_item_csr(c_item_group_id IN NUMBER) IS
2910: SELECT 1 from ahl_item_associations_b