APPS.AHL_UA_UNIT_SCHEDULES_PVT SQL Statements

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

Line: 143

    select
        u.unit_config_header_id uc_header_id,
        u.name uc_name
    from
        ahl_unit_config_headers u,
        mtl_system_items_kfv i,
        csi_item_instances c
    where
	--priyan Bug# 5303188
	--ahl_util_uc_pkg.get_uc_status_code (u.unit_config_header_id) IN ('COMPLETE', 'INCOMPLETE') and
	-- fix for bug number 5528416
	ahl_util_uc_pkg.get_uc_status_code (u.unit_config_header_id) NOT IN ('DRAFT', 'EXPIRED') and
        --u.unit_config_status_code in ('COMPLETE', 'INCOMPLETE') and
        u.csi_item_instance_id = c.instance_id and
        c.inventory_item_id = i.inventory_item_id and
        c.last_vld_organization_id = i.organization_id and
        upper(u.name) like upper(nvl(p_unit_schedules_search.unit_name, '%')) and
        upper(i.concatenated_segments) like upper(nvl(p_unit_schedules_search.item_number, '%')) and
        upper(c.serial_number) like upper(nvl(p_unit_schedules_search.serial_number, '%'))
        order by uc_name desc;

Line: 165

        select uc_header_id, uc_name
        from ahl_unit_config_headers_v
        where upper(uc_name) like nvl(upper(p_unit_schedules_search.unit_name), '%')
        and upper(item_number) like nvl(upper(p_unit_schedules_search.item_number), '%')
        and upper(serial_number) like nvl(upper(p_unit_schedules_search.serial_number), '%')
        and uc_status_code IN ('COMPLETE', 'INCOMPLETE')
    */

BEGIN
    -- Standard start of API savepoint
    SAVEPOINT Search_Unit_Schedules_SP;

Line: 610

            SELECT NVL(MAX(SRCH_UNIT_SCHEDULE_ID), 0) + 1 INTO l_temp_srch_sched_id FROM AHL_SRCH_UNIT_SCHEDULES;

Line: 625

            INSERT INTO AHL_SRCH_UNIT_SCHEDULES
            (
                SRCH_UNIT_SCHEDULE_ID,
                UNIT_CONFIG_HEADER_ID,
                UNIT_SCHEDULE_TYPE,
                UNIT_SCHEDULE_ID,
                VISIT_ID,
                VISIT_STATUS_CODE,
                VISIT_IS_ORG_VALID,
                VISIT_NAME,
                START_TIME,
                END_TIME,
                DEPARTMENT_ID,
                ORGANIZATION_ID
            )
            VALUES
            (
                l_temp_srch_sched_id,
                l_unit_config_id,
                l_temp_sched_type,
                l_temp_unit_sched_id,
                l_temp_visit_id,
                l_temp_visit_status,
                l_temp_visit_is_org_valid,
                l_temp_visit_name,
                l_temp_start_time,
                l_temp_end_time,
                l_temp_dept_id,
                l_temp_org_id
            );

Line: 785

    SELECT
         U.NAME,
         I.CONCATENATED_SEGMENTS ITEM_NUMBER,
         C.SERIAL_NUMBER
    FROM
        AHL_UNIT_CONFIG_HEADERS U,
        CSI_ITEM_INSTANCES C,
        MTL_SYSTEM_ITEMS_KFV I
    WHERE
        U.CSI_ITEM_INSTANCE_ID = C.INSTANCE_ID
        AND C.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
        AND C.LAST_VLD_ORGANIZATION_ID = I.ORGANIZATION_ID
        AND UNIT_CONFIG_HEADER_ID = P_UNIT_CONFIG_HEADER_ID;

Line: 799

    SELECT name, item_number, serial_number
    FROM ahl_unit_header_details_v
    WHERE unit_config_header_id = p_unit_config_header_id;

Line: 808

    SELECT
        AVTB.VISIT_NUMBER,
        AVTT.VISIT_NAME,
        AVTB.ORGANIZATION_ID ORG_ID,
        MP.ORGANIZATION_CODE ORG_CODE,
        AVTB.DEPARTMENT_ID DEPT_ID,
        BDPT.DEPARTMENT_CODE DEPT_CODE,
        FLVT.MEANING VISIT_TYPE_MEAN,
        AVTB.STATUS_CODE,
        FNVS.MEANING STATUS_MEAN
    FROM
        AHL_VISITS_B AVTB,
        AHL_VISITS_TL AVTT,
        MTL_PARAMETERS MP,
        BOM_DEPARTMENTS BDPT,
        FND_LOOKUP_VALUES_VL FLVT,
        FND_LOOKUP_VALUES_VL FNVS
    WHERE
        AVTB.VISIT_ID = P_VISIT_ID
        AND AVTB.VISIT_ID = AVTT.VISIT_ID
        AND AVTT.LANGUAGE = USERENV('LANG')
        AND AVTB.ORGANIZATION_ID = MP.ORGANIZATION_ID(+)
        AND AVTB.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+)
        AND FLVT.LOOKUP_TYPE(+) = 'AHL_PLANNING_VISIT_TYPE'
        AND FLVT.LOOKUP_CODE(+) = AVTB.VISIT_TYPE_CODE
        AND FNVS.LOOKUP_TYPE(+) = 'AHL_VWP_VISIT_STATUS'
        AND FNVS.LOOKUP_CODE(+) = AVTB.STATUS_CODE;

Line: 836

    SELECT visit_number, visit_name, org_id, org_code, dept_id, dept_code,
    visit_type_mean, status_code, status_mean --,unit_schedule_id
    FROM ahl_visit_details_v
    WHERE visit_id = p_visit_id;

Line: 1339

		    -- update header to tell that there is a MO
		    p_x_MEvent_Header_Rec.HAS_MOPPORTUNITY    := FND_API.G_TRUE;

Line: 2053

    select 'x'
    from ahl_unit_config_headers
    where unit_config_header_id = p_unit_config_id
    --priyan Bug # 5303188
    --and ahl_util_uc_pkg.get_uc_status_code (p_unit_config_id) IN ('COMPLETE', 'INCOMPLETE');