[Home] [Help]
71:
72: -- Cursor to get the visit start date
73: CURSOR c_get_visit_start_date(c_visit_id IN NUMBER)
74: IS
75: SELECT start_date_time FROM AHL_VISITS_B WHERE visit_id = c_visit_id;
76:
77: -- Cursor to check whether the item in INV or WIP
78: CURSOR c_inst_not_in_inv_wip(c_instance_id IN NUMBER)
79: IS
697: BEGIN
698: l_have_visit := 0;
699: SELECT COUNT(visit_id)
700: INTO l_have_visit
701: FROM ahl_visits_b visit
702: WHERE visit.status_code <> 'CLOSED'
703: AND TRUNC(Get_Visit_End_Date(visit.visit_id)) BETWEEN p_from_dt AND p_to_dt
704: AND visit.item_instance_id IN
705: (SELECT object_id
736: l_RTS_visit := NULL;
737:
738: SELECT 1
739: INTO l_RTS_visit
740: FROM ahl_visits_b vst,
741: ahl_visit_tasks_b task
742: WHERE vst.visit_id = task.visit_id
743: and vst.visit_id=p_visit_id
744: AND (vst.ITEM_INSTANCE_ID = p_instance_id
816: and wo.wip_entity_id = wdj.wip_entity_id (+);
817:
818: CURSOR c_visit_close_date(p_visit_id NUMBER) IS
819: SELECT CLOSE_DATE_TIME
820: FROM AHL_VISITS_B
821: WHERE VISIT_ID = p_visit_id;
822:
823: l_end_date DATE := NULL;
824: BEGIN
863: CURSOR GET_VISIT_COUNT_CSR (ITEM_ID IN NUMBER, ORG_ID IN NUMBER, START_DATE IN DATE, END_DATE IN DATE, c_visit_state IN VARCHAR2) IS
864: SELECT COUNT(*) FROM (
865: SELECT distinct vst.visit_id, vts.instance_id
866: FROM
867: AHL_VISITS_B VST, AHL_VISIT_TASKS_B VTS
868: WHERE
869: vst.visit_id = vts.visit_id
870: AND ( (c_visit_state = 'PLANNED' and VST.STATUS_CODE IN ('DRAFT','PLANNING')) OR
871: (c_visit_state = 'SCHEDULED' and VST.STATUS_CODE IN ('RELEASED','PARTIALLY RELEASED')))
877: AND TRUNC(AHL_COMPLEX_MX_PVT.Get_Visit_End_Date(VST.VISIT_ID)) BETWEEN TRUNC(START_DATE) AND TRUNC(END_DATE)
878: union
879: SELECT distinct vst.visit_id, vst.item_instance_id
880: FROM
881: AHL_VISITS_B VST
882: WHERE
883: ( (c_visit_state = 'PLANNED' and VST.STATUS_CODE IN ('DRAFT','PLANNING')) OR
884: (c_visit_state = 'SCHEDULED' and VST.STATUS_CODE IN ('RELEASED','PARTIALLY RELEASED')))
885: AND VST.INVENTORY_ITEM_ID = ITEM_ID
923: CURSOR GET_VISIT_COUNT_CSR (ITEM_ID IN NUMBER, ORG_ID IN NUMBER, REQ_DATE IN DATE) IS
924: SELECT COUNT(*) FROM (
925: SELECT distinct vst.visit_id, vts.instance_id
926: FROM
927: AHL_VISITS_B VST, AHL_VISIT_TASKS_B VTS
928: WHERE
929: vst.visit_id = vts.visit_id
930: AND vst.status_code IN ('DRAFT' , 'PLANNING', 'PARTIALLY RELEASED', 'RELEASED')
931: AND VTS.INVENTORY_ITEM_ID = ITEM_ID
936: and LAST_DAY(AHL_COMPLEX_MX_PVT.Get_Visit_End_Date(VST.VISIT_ID)) = REQ_DATE
937: union
938: SELECT distinct vst.visit_id, vst.item_instance_id
939: FROM
940: AHL_VISITS_B VST
941: WHERE
942: vst.status_code IN ('DRAFT' , 'PLANNING', 'PARTIALLY RELEASED', 'RELEASED')
943: AND VST.INVENTORY_ITEM_ID = ITEM_ID
944: AND VST.ORGANIZATION_ID = ORG_ID
989: and cii.inv_locator_id = mil.inventory_location_id (+);
990:
991: CURSOR Get_Visit_Unit_Instance(c_visit_id NUMBER) IS
992: SELECT item_instance_id
993: FROM AHL_VISITS_B
994: WHERE visit_id = c_visit_id;
995:
996: CURSOR Get_Child_Instances(c_instance_id NUMBER) IS
997: SELECT object_id, subject_id, position_reference, level, relationship_id csi_ii_relationship_id,
1007:
1008: --Cursor to get Visit Id from Locator attributes
1009: CURSOR Get_Visit_From_Prj_Task(c_project_id IN NUMBER, c_project_task_id IN NUMBER) IS
1010: SELECT vst.visit_Id
1011: FROM ahl_visits_b vst, ahl_visit_tasks_b vts
1012: WHERE
1013: vst.project_id = c_project_id
1014: and vst.visit_id = vts.visit_id
1015: and vts.project_task_id = c_project_task_id
1017:
1018: --Cursor to get Visit Id from Job
1019: CURSOR Get_Visit_From_Job(c_wip_entity_id NUMBER) IS
1020: SELECT VST.Visit_Id
1021: FROM AHL_WORKORDERS WO, AHL_VISIT_TASKS_B VTS, AHL_VISITS_B VST
1022: WHERE
1023: WO.wip_entity_id = c_wip_entity_id
1024: AND VTS.visit_task_id = WO.visit_task_id
1025: AND VST.visit_id = VTS.visit_id;