APPS.AHL_AVF_PRIM_VSTS_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 118

SELECT 'X'
FROM ahl_visits_b
WHERE visit_id = c_visit_id
AND nvl(locked_flag,'N') = 'N'
AND nvl(firmed_flag,'N') = 'N'
AND start_date_time IS NOT NULL
AND close_date_time IS NOT NULL;

Line: 128

SELECT 'X'
FROM ahl_autovst_snpsht_hdr
WHERE snapshot_id = c_snapshot_id;

Line: 134

SELECT item_instance_id, start_date_time, close_date_time,nvl(space_category_code,0)
FROM ahl_visits_b
WHERE visit_id = c_visit_id;

Line: 143

SELECT 'X'
FROM ahl_ue_relationships uer, ahl_visit_tasks_b vtk, ahl_unit_effectivities_b uet
WHERE uer.ue_id = c_ue_id
AND vtk.instance_id =  c_instance_id
AND vtk.visit_id = c_visit_id
AND uer.ue_id = uet.unit_effectivity_id
AND uet.mr_header_id = vtk.mr_id;

Line: 155

SELECT une.unit_effectivity_id, nvl(mrh.service_category_rank,0)
FROM ahl_unit_effectivities_b une, ahl_mr_headers_b mrh
WHERE une.csi_item_instance_id = c_instance_id
AND (une.status_code ='INIT-DUE' OR une.status_code IS NULL)
AND une.due_date is NOT NULL
AND (une.due_date between c_start_date AND c_end_date  or (une.earliest_due_date between c_start_date AND c_end_date OR une.latest_due_date between c_start_date AND c_end_date))
AND NOT EXISTS (SELECT 'X' FROM ahl_ue_relationships uer WHERE uer.related_ue_id = une.unit_effectivity_id)
AND une.mr_header_id = mrh.mr_header_id
AND NOT exists (SELECT 'X' FROM ahl_visit_tasks_b task, ahl_visits_b visit
                WHERE task.visit_id = visit.visit_id AND task.unit_effectivity_id = une.unit_effectivity_id
                AND task.status_code NOT IN ('CANCELLED', 'DELETED'));

Line: 169

SELECT from_date,to_date
FROM ahl_autovst_snpsht_hdr
WHERE snapshot_id = c_snapshot_id;

Line: 175

SELECT mc_id
FROM ahl_autovisit_hierarchy
WHERE autovisit_flag = 'Y'
AND snapshot_id = c_snapshot_id
AND hierarchy_type_code = L_MASTER_CODE;

Line: 185

SELECT une.unit_effectivity_id, une.due_date, une.latest_due_date, une.csi_item_instance_id, nvl(mrh.service_category_rank,0)
FROM ahl_unit_effectivities_b une, ahl_mr_headers_b mrh, ahl_unit_config_headers uch,ahl_mc_headers_b mc
WHERE mc.mc_id = c_mc_id
AND uch.master_config_id =  mc.mc_header_id --tchimira :: 21 May 2012 :: compare correct columns
AND une.csi_item_instance_id = uch.csi_item_instance_id
AND (une.status_code ='INIT-DUE' OR une.status_code IS NULL)
AND une.due_date IS NOT NULL
AND (une.due_date between c_start_date AND c_end_date  or (une.earliest_due_date between c_start_date AND c_end_date OR une.latest_due_date between c_start_date AND c_end_date))
AND NOT EXISTS (SELECT 'X' FROM ahl_ue_relationships uer WHERE uer.related_ue_id = une.unit_effectivity_id)
AND une.mr_header_id = mrh.mr_header_id
AND NOT exists (SELECT 'X' FROM ahl_visit_tasks_b task, ahl_visits_b visit
                WHERE task.visit_id = visit.visit_id AND task.unit_effectivity_id = une.unit_effectivity_id
                AND task.status_code NOT IN ('CANCELLED', 'DELETED'));

Line: 204

SELECT visit_id
FROM ahl_visits_b
WHERE status_code IN ('PLANNING','RELEASED','PARTIALLY RELEASED')
AND item_instance_id = c_instance_id
AND nvl(locked_flag,'N') = 'N'
AND nvl(firmed_flag,'N') = 'N'
AND (c_due_date between start_date_time AND close_date_time or c_lat_due_date between start_date_time AND close_date_time)
AND TO_NUMBER(nvl(space_category_code,0)) <= c_category
ORDER BY start_date_time;

Line: 603

SELECT 'X'
FROM ahl_autovst_snpsht_hdr
WHERE snapshot_id = c_snapshot_id;

Line: 609

SELECT visit.visit_id, visit.object_version_number
FROM ahl_visits_b visit, ahl_unit_config_headers uch, ahl_autovisit_hierarchy avh, ahl_autovst_snpsht_hdr snap,ahl_mc_headers_b mc
WHERE visit.start_date_time IS NOT null AND visit.close_date_time IS NOT NULL
AND start_date_time >= snap.from_date
AND close_date_time <= snap.to_date
AND visit.status_code = L_STATUS_CODE
AND visit.item_instance_id IS NOT NULL
AND visit.item_instance_id = uch.csi_item_instance_id
AND nvl(visit.locked_flag,'N') = 'N'
AND nvl(visit.firmed_flag,'N') = 'N'
AND uch.master_config_id = mc.mc_header_id --tchimira :: 21 May 2012 :: bug 14082494
AND mc.mc_id = avh.mc_id
AND avh.snapshot_id = snap.snapshot_id
AND avh.autovisit_flag = 'Y'
AND avh.hierarchy_type_code = L_MASTER_CODE
AND snap.snapshot_id = c_snapshot_id;

Line: 694

                    fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Calling Delete_visit :: p_api_version--@>'||L_API_VERSION);

Line: 695

                    fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Calling Delete_visit :: p_init_msg_list--@>'||Fnd_Api.g_false);

Line: 696

                    fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Calling Delete_visit :: p_commit--@>'||Fnd_Api.g_false);

Line: 697

                    fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Calling Delete_visit :: p_validation_level--@>'||Fnd_Api.g_valid_level_full);

Line: 698

                    fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Calling Delete_visit :: p_visit_id--@>'||l_visit_id);

Line: 706

                AHL_VWP_VISITS_PVT.Delete_Visit(
                p_api_version             => L_API_VERSION,
                p_init_msg_list           => Fnd_Api.g_false,
                p_commit                  => Fnd_Api.g_false,
                p_validation_level        => Fnd_Api.g_valid_level_full,
                p_visit_id                => l_visit_id,
                x_return_status           => l_return_status,
                x_msg_count               => l_msg_count,
                x_msg_data                => l_msg_data);

Line: 718

                    fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Return Delete_visit :: x_return_status--@>'||l_return_status);

Line: 719

                    fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Return Delete_visit :: x_msg_count--@>'||l_msg_count);

Line: 720

                    fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Return Delete_visit :: x_msg_data--@>'||l_msg_data);

Line: 854

   SELECT MROrg.MR_ORGANIZATION_ID,
    MROrg.OPERATING_ORG_ID OPERATING_ORG_ID,
    MROrg.MAINTENANCE_ORG_ID,
    HRU.NAME MAINTENANCE_ORG_NAME,
    MROrg.MAINTENANCE_DEPT_ID,
    ADSV.DEPT_DESCRIPTION MAINTENANCE_DEPT_NAME
    FROM AHL_MR_HEADERS_B AMH,
    AHL_MR_ORGANIZATIONS MROrg,
    HR_ORGANIZATION_UNITS HRU,
    AHL_DEPARTMENT_SHIFTS_V ADSV
    WHERE MROrg.MR_TITLE          = AMH.TITLE
    AND MROrg.MAINTENANCE_ORG_ID  = HRU.ORGANIZATION_ID
    AND MROrg.MAINTENANCE_DEPT_ID = ADSV.DEPARTMENT_ID
    AND AMH.MR_HEADER_ID = c_mr_header_id
    AND ( MROrg.OPERATING_ORG_ID    = c_operating_org_id
      OR MROrg.OPERATING_ORG_ID IS NULL )
    order by MROrg.OPERATING_ORG_ID NULLS LAST;

Line: 876

    SELECT mr_header_id FROM ahl_mr_headers_vl
    WHERE mr_header_id = c_mr_header_id;

Line: 1086

SELECT visit_number
FROM ahl_visits_b
WHERE visit_id = c_visit_id;

Line: 1092

SELECT MC_ID
FROM ahl_autovisit_hierarchy
WHERE HIERARCHY_TYPE_CODE = 'MASTER_CONFIGURATION'
AND SNAPSHOT_ID = c_snapshot_id
AND AUTOVISIT_FLAG = 'Y'
ORDER BY SEQUENCE;

Line: 1101

SELECT PROGRAM_TYPE_CODE
FROM ahl_autovisit_hierarchy
WHERE HIERARCHY_TYPE_CODE = 'PROGRAM'
AND SNAPSHOT_ID = c_snapshot_id
AND AUTOVISIT_FLAG = 'Y'
ORDER BY SEQUENCE;

Line: 1110

SELECT PROGRAM_SUBTYPE_CODE
FROM ahl_autovisit_hierarchy
WHERE HIERARCHY_TYPE_CODE = 'SUBTYPE'
AND PROGRAM_TYPE_CODE = c_prog_typ_code
AND SNAPSHOT_ID = c_snapshot_id
AND AUTOVISIT_FLAG = 'Y'
ORDER BY SEQUENCE;

Line: 1120

SELECT count(PROGRAM_SUBTYPE_CODE)
FROM ahl_autovisit_hierarchy
WHERE HIERARCHY_TYPE_CODE = 'SUBTYPE'
AND PROGRAM_TYPE_CODE = c_prog_typ_code
AND SNAPSHOT_ID = c_snapshot_id
AND AUTOVISIT_FLAG = 'Y';

Line: 1130

select from_date,to_date
from ahl_autovst_snpsht_hdr
where snapshot_id = c_snapshot_id;

Line: 1142

SELECT ue.unit_effectivity_id, ue.due_date, ue.csi_item_instance_id, mr.mr_header_id,
       mr_vtyp.mr_visit_type_code, vtyp.estimated_duration
FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b mr, ahl_mr_visit_types_app_v mr_vtyp, ahl_visit_types_b vtyp,
AHL_UNIT_CONFIG_HEADERS unit, ahl_mc_headers_b mc
WHERE ue.csi_item_instance_id = unit.csi_item_instance_id
AND unit.master_config_id = mc.mc_header_id
AND unit.unit_config_status_code NOT IN ('QUARANTINE', 'EXPIRED')
AND mc.mc_id = c_mc_id
AND ue.mr_header_id = mr.mr_header_id
AND mr.program_type_code = c_pt_code
AND (mr.program_subtype_code = c_pst_code
     OR c_pst_code IS NULL)
AND mr.implement_status_code IN ('MANDATORY', 'OPTIONAL_IMPLEMENT')
AND mr.mr_header_id = mr_vtyp.mr_header_id
AND mr_vtyp.mr_visit_type_code = vtyp.visit_type_code
AND vtyp.mc_id = c_mc_id
AND vtyp.status_code = 'COMPLETE'
AND NOT EXISTS (SELECT 'X' from ahl_visit_tasks_b task
                WHERE task.unit_effectivity_id = ue.unit_effectivity_id
                AND task.status_code NOT IN ('CANCELLED', 'DELETED') )
AND ue.due_date BETWEEN c_start_date AND c_end_date
AND NVL(ue.earliest_due_date, c_end_date) BETWEEN c_start_date AND c_end_date
AND NVL(ue.latest_due_date, c_end_date) BETWEEN c_start_date AND c_end_date;

Line: 1168

SELECT fleet.operating_org_id
FROM ahl_fleet_headers_b fleet, ahl_unit_effectivities_b ue
WHERE fleet.fleet_header_id = ue.fleet_header_id
and ue.unit_effectivity_id = c_ue_id;

Line: 1352

               SELECT NAME INTO l_visit_rec.UNIT_NAME FROM AHL_UNIT_CONFIG_HEADERS
               WHERE csi_item_instance_id= l_primary_ue_rec.csi_item_instance_id;

Line: 1624

               SELECT NAME INTO l_visit_rec.UNIT_NAME FROM AHL_UNIT_CONFIG_HEADERS
               WHERE csi_item_instance_id= l_primary_ue_rec.csi_item_instance_id;

Line: 1913

   SELECT create_visit_flag, add_planned_reqs_flag, cancel_visit_flag
   INTO l_create_visit_flag, l_add_planned_reqs_flag, l_cancel_visit_flag
   FROM ahl_autovst_snpsht_hdr WHERE snapshot_id = p_snapshot_id;