[Home] [Help]
592: x_return_val OUT NOCOPY BOOLEAN) IS
593:
594: CURSOR l_ahl_relationship_csr(p_relationship_id IN NUMBER) IS
595: SELECT position_ref_code
596: FROM AHL_MC_RELATIONSHIPS
597: WHERE relationship_id = p_relationship_id
598: AND TRUNC(SYSDATE) >= TRUNC(NVL(active_start_date, SYSDATE))
599: AND TRUNC(SYSDATE) < TRUNC(NVL(active_end_date, SYSDATE+1));
600:
598: AND TRUNC(SYSDATE) >= TRUNC(NVL(active_start_date, SYSDATE))
599: AND TRUNC(SYSDATE) < TRUNC(NVL(active_end_date, SYSDATE+1));
600:
601:
602: l_position_ref_code ahl_mc_relationships.position_ref_code%TYPE DEFAULT NULL;
603:
604: l_return_val BOOLEAN DEFAULT TRUE;
605:
606: BEGIN
654: reln.quantity Posn_Qty,
655: reln.uom_code Posn_uom_code,
656: iasso.revision Itm_revision,
657: iasso.item_association_id
658: FROM ahl_mc_relationships reln, ahl_item_associations_b iasso
659: WHERE reln.item_group_id = iasso.item_group_id
660: AND reln.relationship_id = p_mc_relationship_id
661: AND iasso.inventory_item_id = p_Inventory_id
662: AND iasso.inventory_org_id = p_Organization_id
675: reln.quantity Posn_Qty,
676: reln.uom_code Posn_uom_code,
677: iasso.revision Itm_revision,
678: iasso.item_association_id
679: FROM ahl_mc_relationships reln, ahl_item_associations_b iasso
680: WHERE reln.item_group_id = iasso.item_group_id
681: AND reln.relationship_id = p_mc_relationship_id
682: AND iasso.inventory_item_id = p_Inventory_id
683: AND (iasso.revision IS NULL OR iasso.revision = p_revision)
690: -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 04-Dec-2007
691: -- Cursor to fetch the position lookup meaning for a given relationship id.
692: CURSOR get_pos_lkup_meaning(p_mc_relationship_id IN NUMBER) IS
693: SELECT fnd.lookup_code, fnd.meaning position_ref_meaning
694: FROM ahl_mc_relationships mcr, fnd_lookup_values_vl fnd
695: WHERE mcr.relationship_id = p_mc_relationship_id
696: AND mcr.position_ref_code = fnd.lookup_code
697: AND fnd.lookup_type = 'AHL_POSITION_REFERENCE';
698:
998:
999: -- Cursor to read the master config tree.
1000: CURSOR ahl_relationships_csr (p_mc_relationship_id IN NUMBER) IS
1001: SELECT relationship_id, position_ref_code, level
1002: FROM ahl_mc_relationships
1003: START WITH parent_relationship_id = p_mc_relationship_id
1004: AND TRUNC(SYSDATE) >= TRUNC(NVL(active_start_date, SYSDATE))
1005: AND TRUNC(SYSDATE) < TRUNC(NVL(active_end_date, SYSDATE+1))
1006: CONNECT BY PRIOR relationship_id = parent_relationship_id
1321:
1322: -- Get all the positions associated to this item group.
1323: CURSOR get_associated_posns_csr(c_item_group_id IN NUMBER) IS
1324: SELECT relationship_id
1325: FROM ahl_mc_relationships rel, ahl_mc_headers_b hdr
1326: WHERE trunc(nvl(rel.active_end_date,sysdate+1)) > trunc(sysdate)
1327: AND trunc(nvl(rel.active_start_date,sysdate-1)) < trunc(sysdate)
1328: AND hdr.mc_header_id = rel.mc_header_id
1329: AND rel.item_group_id = c_item_group_id
1347: CURSOR chk_top_node_csr(c_relationship_id IN NUMBER,
1348: c_inventory_item_id IN NUMBER,
1349: c_inventory_org_id IN NUMBER) IS
1350: SELECT uc.csi_item_instance_id, uc.unit_config_header_id, uc.parent_uc_header_id, uc.unit_config_status_code
1351: FROM ahl_unit_config_headers uc, csi_item_instances csi, ahl_mc_relationships mc
1352: WHERE uc.csi_item_instance_id = csi.instance_id
1353: AND uc.master_config_id = mc.mc_header_id
1354: AND mc.relationship_id = c_relationship_id
1355: AND TRUNC(SYSDATE) < TRUNC(NVL(uc.active_end_date, SYSDATE+1))
2178: A.uom_code item_uom_code,
2179: A.revision item_revision,
2180: R.quantity position_quantity,
2181: R.uom_code position_uom_code
2182: FROM ahl_mc_relationships R,
2183: ahl_item_associations_b A
2184: WHERE R.item_group_id = A.item_group_id
2185: AND R.relationship_id = c_mc_relationship_id
2186: AND A.inventory_item_id = c_inventory_item_id
2316: --
2317: CURSOR get_top_rel_id (csi_id IN NUMBER)
2318: IS
2319: SELECT rel.relationship_id
2320: FROM AHL_MC_RELATIONSHIPS rel,
2321: AHL_UNIT_CONFIG_HEADERS uch
2322: WHERE rel.mc_header_id = uch.master_config_id
2323: AND rel.parent_relationship_id IS NULL
2324: AND uch.csi_item_instance_id = csi_id;
2635:
2636: -- Get all the positions associated to this item group.
2637: CURSOR c_get_associated_posns_csr(c_item_group_id NUMBER) IS
2638: SELECT relationship_id
2639: FROM AHL_MC_RELATIONSHIPS REL, AHL_MC_HEADERS_B HDR
2640: WHERE HDR.mc_header_id = REL.mc_header_id AND
2641: REL.item_group_id = c_item_group_id AND
2642: HDR.config_status_code NOT IN ('EXPIRED','CLOSED') AND
2643: TRUNC(NVL(REL.active_end_date,SYSDATE+1)) > TRUNC(SYSDATE) AND
2663: c_inventory_org_id NUMBER) IS
2664: SELECT UC.name,
2665: UC.unit_config_header_id
2666: FROM AHL_UNIT_CONFIG_HEADERS UC, CSI_ITEM_INSTANCES CSI,
2667: AHL_MC_RELATIONSHIPS MC
2668: WHERE UC.csi_item_instance_id = CSI.instance_id AND
2669: UC.master_config_id = MC.mc_header_id AND
2670: UC.parent_uc_header_id IS NULL AND
2671: MC.parent_relationship_id IS NULL AND
2891: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
2892:
2893: CURSOR get_root_mc_ig_csr (c_mc_header_id IN NUMBER) IS
2894: SELECT reln.relationship_id, reln.item_group_id
2895: FROM ahl_mc_relationships reln
2896: WHERE reln.mc_header_id = c_mc_header_id
2897: AND nvl(reln.active_start_date, sysdate - 1) <= sysdate
2898: AND nvl(reln.active_end_date, sysdate + 1) > sysdate
2899: AND reln.parent_relationship_id is null;
2907: AND NVL(ACTIVE_END_DATE, sysdate + 1) > sysdate;
2908:
2909: CURSOR get_mc_ig_csr(c_pos_ref IN VARCHAR2) IS
2910: SELECT reln.item_group_id
2911: FROM ahl_mc_relationships reln
2912: WHERE relationship_id = TO_NUMBER(c_pos_ref);
2913:
2914: CURSOR item_group_has_item_csr(c_item_group_id IN NUMBER) IS
2915: SELECT 1 from ahl_item_associations_b