APPS.HR_ES_UTILITY SQL Statements

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

Line: 146

        SELECT substr(LTRIM(RTRIM(
              DECODE(p_last_name, NULL, '', ' ' || p_last_name)
              ||DECODE(p_per_information1, NULL,'',' ' || p_per_information1)
              ||DECODE(p_first_name,NULL, '', ', ' || p_first_name)
              )), 1, 240)
        INTO   l_full_name
        FROM   dual;

Line: 359

     SELECT 'Y'
     INTO   l_status
     FROM   sys.dual
     WHERE  exists(SELECT '1'
                   FROM   per_people_f pp
                   WHERE (p_person_id IS NULL OR p_person_id <> pp.person_id)
                   AND    p_identifier_value = pp.per_information3
                   AND    p_identifier_type = pp.per_information2
                   AND    pp.business_group_id  = p_business_group_id);

Line: 400

SELECT formula_id, effective_start_date
FROM   ff_formulas_f
WHERE  formula_name = 'ES_CIF_VALIDATION'
AND    business_group_id is null
AND    legislation_code = 'ES'
AND    sysdate BETWEEN effective_start_date AND  effective_end_date;

Line: 438

    select 1 from hr_lookups
    where lookup_type='ES_PROVINCE_CODES'
    and lookup_code=p_province_code;

Line: 466

SELECT formula_id, effective_start_date
FROM   ff_formulas_f
WHERE  formula_name = 'ES_CAC_VALIDATION'
AND    business_group_id is null
AND    legislation_code = 'ES'
AND    sysdate BETWEEN effective_start_date AND  effective_end_date;

Line: 507

    SELECT 'x'
    FROM   hr_organization_information hoi,hr_all_organization_units hou
    WHERE  hoi.org_information_context = 'ES_WORK_CENTER_DETAILS'
    AND    hou.organization_id = hoi.organization_id
    AND    org_information1 = p_org_info
    AND    hou.business_group_id = p_business_group_id
    AND    (p_org_info_id IS NULL OR hoi.org_information_id <> p_org_info_id)
    AND    p_effective_date <= nvl(hou.date_to,to_Date('4712/12/31','YYYY/MM/DD'))
    AND exists (select 1 from hr_organization_information hoi1
                where hoi1.org_information1 = 'ES_WORK_CENTER'
                and   hoi1.org_information_context = 'CLASS'
                and   hoi1.organization_id = hoi.organization_id
                and   hoi1.org_information2 = 'Y');

Line: 522

    SELECT 'x'
    FROM   hr_organization_information hoi,hr_all_organization_units hou
    WHERE  hoi.org_information_context = 'ES_STATUTORY_INFO'
    AND    hou.organization_id = hoi.organization_id
    AND    org_information8 = p_org_info
    AND    hou.business_group_id = p_business_group_id
    AND    (p_org_info_id IS NULL OR hoi.org_information_id <> p_org_info_id)
    AND    p_effective_date <= nvl(hou.date_to,to_Date('4712/12/31','YYYY/MM/DD'))
    AND exists (select 1 from hr_organization_information hoi1
                where hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
                and   hoi1.org_information_context = 'CLASS'
                and   hoi1.organization_id = hoi.organization_id
                and   hoi1.org_information2 = 'Y');

Line: 588

    SELECT 'x'
    FROM   hr_organization_information hoi,hr_all_organization_units hou
    WHERE  hoi.org_information_context in('ES_SS_PROVINCE_DETAILS','ES_SS_OFFICE_DETAILS')
    AND    hou.organization_id = hoi.organization_id
    AND    org_information1 = p_org_info
    AND    hou.business_group_id = p_business_group_id
    AND    (p_org_info_id IS NULL OR hoi.org_information_id <> p_org_info_id)
    AND    p_effective_date <= nvl(hou.date_to,to_Date('4712/12/31','YYYY/MM/DD'))
    AND exists (select 1 from hr_organization_information hoi1
                where hoi1.org_information1 in( 'ES_SS_OFFICE_INFO','ES_SS_PROVINCE_INFO')
                and   hoi1.org_information_context = 'CLASS'
                and   hoi1.organization_id = hoi.organization_id
                and   hoi1.org_information2 = 'Y');

Line: 627

    SELECT 'x'
    FROM   hr_organization_information hoi,hr_all_organization_units hou
    WHERE  hoi.org_information_context = 'ES_STATUTORY_INFO'
    AND    hou.organization_id = hoi.organization_id
    AND    org_information5 = p_org_info
    AND    hou.business_group_id = p_business_group_id
    AND    (p_org_info_id IS NULL OR hoi.org_information_id <> p_org_info_id)
    AND    p_effective_date <= nvl(hou.date_to,to_Date('4712/12/31','YYYY/MM/DD'))
    AND exists (select 1 from hr_organization_information hoi1
                where hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
                and   hoi1.org_information_context = 'CLASS'
                and   hoi1.organization_id = hoi.organization_id
                and   hoi1.org_information2 = 'Y');

Line: 661

SELECT 'x'
FROM   hr_organization_information hoi
      ,hr_all_organization_units   hou
WHERE  hoi.org_information_context = p_context
AND    hoi.org_information1    = p_org_information1
AND    hoi.organization_id     = hou.organization_id
AND    hou.business_group_id   = p_business_group_id
AND    p_effective_date  <= NVL(hou.date_to,to_Date('4712/12/31','YYYY/MM/DD'));

Line: 703

        SELECT 'Y'
        INTO   l_status
        FROM   sys.dual
        WHERE  exists(SELECT '1'
                      FROM   per_shared_types pp
                      WHERE  lookup_type = 'LEAV_REAS'
                      AND    system_type_cd = p_leaving_reason
                      AND    nvl(business_group_id,p_business_group_id)
                              = p_business_group_id
                      AND    information1 IS NOT NULL);

Line: 767

SELECT degree
FROM   per_disabilities_f d
WHERE  d.person_id = p_person_id
AND    p_session_date BETWEEN d.effective_start_date
                      AND     d.effective_end_date;

Line: 793

    select peevf.screen_entry_value
    from pay_element_entries_f peef
        ,pay_element_entry_values_f peevf
    where peef.assignment_id=p_assignment_id
    and   peef.element_type_id=p_element_type_id
    and   peevf.input_Value_id=p_input_value_id
    and   peevf.ELEMENT_ENTRY_ID=peef.element_entry_id
    and   p_effective_date between peef.effective_start_date and peef.effective_end_date
    and   p_effective_date between peevf.effective_start_date and peevf.effective_end_date;

Line: 823

    SELECT 'X'
    FROM   hr_lookups hll
    WHERE  hll.lookup_type  = p_lookup_type
    AND    hll.lookup_code  = p_entry_val
    AND    hll.enabled_flag = 'Y'
    AND    p_effective_date BETWEEN NVL(hll.start_date_active, p_effective_date)
                             AND     NVL(hll.end_date_active, p_effective_date);

Line: 1023

     SELECT description
     FROM   hr_lookups
     WHERE  lookup_type     = p_lookup_type
     AND    lookup_code     = p_lookup_code;