[Home] [Help]
464:
465: -- To find on the basis of input unit effectivity the related information
466: CURSOR c_info(x_mr_header_id IN NUMBER, x_unit_id IN NUMBER) IS
467: SELECT AUEB.CSI_ITEM_INSTANCE_ID
468: FROM AHL_UNIT_EFFECTIVITIES_VL AUEB, CSI_ITEM_INSTANCES CSI
469: WHERE AUEB.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID
470: AND (AUEB.STATUS_CODE IS NULL OR AUEB.STATUS_CODE = 'INIT-DUE')
471: AND AUEB.UNIT_EFFECTIVITY_ID = x_unit_id AND AUEB.MR_HEADER_ID = x_mr_header_id;
472:
477: CURSOR c_relation (x_ue_id IN NUMBER) IS
478: SELECT AUR.RELATED_UE_ID "UNIT_ID"
479: FROM AHL_UE_RELATIONSHIPS AUR
480: START WITH AUR.UE_ID IN (SELECT AUEB.unit_effectivity_id
481: FROM AHL_UNIT_EFFECTIVITIES_VL AUEB
482: WHERE (AUEB.STATUS_CODE IS NULL OR AUEB.STATUS_CODE = 'INIT-DUE')
483: AND AUEB.unit_effectivity_id = x_ue_id
484: )
485: CONNECT BY PRIOR AUR.RELATED_UE_ID = AUR.UE_ID;
488: -- Added by sjayacha for Servie Request Integration
489: -- To check whether any child UE exists
490: CURSOR c_check_child_ue(p_ue_id IN NUMBER) IS
491: SELECT 'X'
492: FROM ahl_ue_relationships AUR, ahl_unit_effectivities_vl AUEB
493: WHERE AUR.ue_id = AUEB.unit_effectivity_id
494: AND (AUEB.status_code IS NULL OR AUEB.status_code = 'INIT-DUE')
495: AND AUR.ue_id = p_ue_id;
496:
497: -- To find MR Header Id for any related Sub Unit Effectivity Id
498: -- or for main Unit Effectivity Id
499: CURSOR c_header (x_unit_id IN NUMBER) IS
500: /*SELECT MR_HEADER_ID
501: FROM AHL_UNIT_EFFECTIVITIES_VL AUEB
502: WHERE (STATUS_CODE IS NULL OR STATUS_CODE IN ('INIT-DUE', 'DEFERRED'))
503: AND UNIT_EFFECTIVITY_ID = x_unit_id;*/
504: /* For Bug# 3152532 fix by shbhanda dated 02-Dec-03*/
505: SELECT AUEB.MR_HEADER_ID
502: WHERE (STATUS_CODE IS NULL OR STATUS_CODE IN ('INIT-DUE', 'DEFERRED'))
503: AND UNIT_EFFECTIVITY_ID = x_unit_id;*/
504: /* For Bug# 3152532 fix by shbhanda dated 02-Dec-03*/
505: SELECT AUEB.MR_HEADER_ID
506: FROM AHL_UNIT_EFFECTIVITIES_VL AUEB, AHL_MR_HEADERS_B AMHB
507: WHERE AUEB.MR_HEADER_ID = AMHB.MR_HEADER_ID
508: AND AMHB.MR_STATUS_CODE = 'COMPLETE'
509: AND AMHB.VERSION_NUMBER IN
510: ( SELECT MAX(VERSION_NUMBER)
553:
554: CURSOR c_unit_object_type(p_unit_id IN NUMBER)
555: IS
556: SELECT OBJECT_TYPE
557: FROM AHL_UNIT_EFFECTIVITIES_VL
558: WHERE UNIT_EFFECTIVITY_ID = p_unit_id;
559:
560: -- To find the Item Id, Inv Org Id and Serial Number
561: CURSOR c_item_info(p_unit_id IN NUMBER) IS
562: SELECT AUEB.CSI_ITEM_INSTANCE_ID,
563: AUEB.CS_INCIDENT_ID,
564: CSI.INV_MASTER_ORGANIZATION_ID,
565: CSI.INVENTORY_ITEM_ID
566: FROM AHL_UNIT_EFFECTIVITIES_VL AUEB, CSI_ITEM_INSTANCES CSI
567: WHERE AUEB.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID
568: AND (AUEB.STATUS_CODE IS NULL OR AUEB.STATUS_CODE = 'INIT-DUE')
569: AND AUEB.UNIT_EFFECTIVITY_ID = p_unit_id ;
570:
1032: )
1033: IS
1034: CURSOR c_header (x_unit_id IN NUMBER) IS
1035: SELECT aueb.mr_header_id
1036: FROM ahl_unit_effectivities_vl aueb, ahl_mr_headers_b amhb
1037: WHERE aueb.mr_header_id = amhb.mr_header_id
1038: AND amhb.mr_status_code = 'COMPLETE'
1039: AND amhb.version_number IN
1040: (SELECT MAX(version_number)
1051:
1052: -- To find on the basis of input unit effectivity the related information
1053: CURSOR c_info(x_mr_header_id IN NUMBER, x_unit_id IN NUMBER) IS
1054: SELECT aueb.csi_item_instance_id
1055: FROM ahl_unit_effectivities_vl aueb, csi_item_instances csi
1056: WHERE aueb.csi_item_instance_id = csi.instance_id
1057: AND (aueb.status_code IS NULL OR aueb.status_code = 'INIT-DUE')
1058: AND aueb.unit_effectivity_id = x_unit_id
1059: AND aueb.mr_header_id = x_mr_header_id;
1060:
1061: CURSOR c_relation (x_ue_id IN NUMBER) IS
1062: SELECT aur.related_ue_id
1063: FROM ahl_ue_relationships aur,
1064: ahl_unit_effectivities_vl aueb
1065: WHERE aur.ue_id = x_ue_id
1066: AND aur.ue_id = aueb.unit_effectivity_id
1067: AND (aueb.status_code IS NULL OR aueb.status_code = 'INIT-DUE');
1068: c_relation_rec c_relation%ROWTYPE;