APPS.PER_AE_XDO_REPORT SQL Statements

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

Line: 18

    SELECT business_group_id
    FROM   per_business_groups
    WHERE  business_group_id = nvl(p_business_group_id, business_group_id)
    AND    legislation_code = 'AE';

Line: 24

    SELECT org.organization_id
           ,org.name
    FROM   hr_all_organization_units org
    WHERE  org.organization_id IN (SELECT pose.organization_id_child
                                   FROM   per_org_structure_elements pose
                                   CONNECT BY pose.organization_id_parent = PRIOR pose.organization_id_child
                                   AND    pose.org_structure_version_id = p_org_structure_version_id
                                   START WITH pose.organization_id_parent = nvl(c_business_group_id, l_parent_id)
                                   AND    pose.org_structure_version_id = p_org_structure_version_id
                                   UNION
                                   SELECT nvl(p_business_group_id, l_parent_id)
                                   FROM   DUAL)
    AND    p_org_structure_version_id IS NOT NULL
    UNION
    SELECT org.organization_id
           ,org.name
    FROM   hr_all_organization_units org
    WHERE  org.organization_id = NVL(c_business_group_id,org.organization_id)
    AND    org.business_group_id = c_business_group_id
    AND    p_org_structure_version_id IS NULL;

Line: 46

SELECT org.name
      ,people.employee_number
      ,people.full_name
      ,dei.dei_information1 visa_number
      ,dei.dei_information9 place_of_issue
      ,dei.date_to expiry_date
      ,assg.assignment_id assignment_id
FROM   per_all_assignments_f assg
      ,per_all_people_f     people
      ,hr_document_extra_info dei
      ,hr_document_types_tl hdtl
      ,hr_all_organization_units org
WHERE assg.person_id = people.person_id
AND     (l_date) BETWEEN assg.effective_start_date
		 AND   assg.effective_end_date
AND     (l_date) BETWEEN people.effective_start_date
		 AND   people.effective_end_date
AND    dei.person_id = people.person_id
AND    dei.document_type_id = hdtl.document_type_id
AND    hdtl.document_type = 'AE_VISA'
AND    hdtl.language = 'US'
AND    dei.dei_information_category = hdtl.document_type
AND    assg.organization_id = org.organization_id
AND    org.organization_id = c_organization_id
AND    org.business_group_id = c_business_group_id
--AND    org.organization_id = p_business_group_id
AND    dei.date_to
BETWEEN NVL(l_date,sysdate) AND
DECODE(p_units,'D',(NVL(l_date,sysdate)+p_expires_in),
               'W',(NVL(l_date,sysdate)+(p_expires_in*7)),
               'M',(add_months(NVL(l_date,sysdate),p_expires_in)),
	       'Y',(add_months(NVL(l_date,sysdate),(p_expires_in*12))))
ORDER BY org.name, expiry_date, full_name;

Line: 80

SELECT pjb.name
FROM   per_all_assignments_f paa,
       per_jobs pjb
WHERE  paa.assignment_id = p_assignment_id
AND    paa.job_id = pjb.job_id;

Line: 99

        SELECT fnd_date.canonical_to_date(p_date)
        INTO   l_date
        FROM   DUAL;

Line: 109

    INSERT INTO fnd_sessions (session_id, effective_date)
    VALUES (userenv('SESSIONID'), l_date);

Line: 118

    gxmltable.DELETE;

Line: 123

        SELECT distinct pose.organization_id_parent
        INTO   l_parent_id
        FROM   per_org_structure_elements pose
        WHERE  pose.org_structure_version_id = p_org_structure_version_id
        AND    pose.organization_id_parent NOT IN (SELECT pose1.organization_id_child
                                                   FROM   per_org_structure_elements pose1
                                                   WHERE  pose1.org_structure_version_id
                                                          = p_org_structure_version_id);

Line: 138

        SELECT name
        INTO   l_org_name
        FROM   hr_organization_units
        WHERE  organization_id = p_business_group_id;

Line: 149

        SELECT name
        INTO   l_structure_name
        FROM   per_organization_structures
        WHERE  organization_structure_id = p_org_structure_id;

Line: 160

        SELECT version_number
        INTO   l_version
        FROM   per_org_structure_versions
        WHERE  org_structure_version_id = p_org_structure_version_id;

Line: 171

        SELECT meaning
        INTO   l_units
        FROM   hr_lookups
        WHERE  lookup_type = 'QUALIFYING_UNITS'
        AND    lookup_code = p_units;

Line: 396

    SELECT business_group_id
    FROM   per_business_groups
    WHERE  business_group_id = nvl(p_business_group_id, business_group_id)
    AND    legislation_code = 'AE';

Line: 402

    SELECT org.organization_id
           ,org.name
    FROM   hr_all_organization_units org
    WHERE  org.organization_id IN (SELECT pose.organization_id_child
                                   FROM   per_org_structure_elements pose
                                   CONNECT BY pose.organization_id_parent = PRIOR pose.organization_id_child
                                   AND    pose.org_structure_version_id = p_org_structure_version_id
                                   START WITH pose.organization_id_parent = nvl(c_business_group_id, l_parent_id)
                                   AND    pose.org_structure_version_id = p_org_structure_version_id
                                   UNION
                                   SELECT nvl(c_business_group_id, l_parent_id)
                                   FROM   DUAL)
    AND    p_org_structure_version_id IS NOT NULL
    UNION
    SELECT org.organization_id
           ,org.name
    FROM   hr_all_organization_units org
    WHERE  org.organization_id = NVL(c_business_group_id,org.organization_id)
    AND    org.business_group_id = c_business_group_id
    AND    p_org_structure_version_id IS NULL;

Line: 424

SELECT org.name
      ,people.employee_number
      ,people.full_name
      ,dei.dei_information1 passport_number
      ,dei.issued_at place_of_issue
      ,dei.date_to expiry_date
      ,assg.assignment_id assignment_id
FROM   per_all_assignments_f assg
      ,per_all_people_f people
      ,hr_document_extra_info dei
      ,hr_document_types_tl hdtl
      ,hr_all_organization_units   org
WHERE assg.person_id = people.person_id
AND     (l_date) BETWEEN assg.effective_start_date
		 AND   assg.effective_end_date
AND     (l_date) BETWEEN people.effective_start_date
		 AND   people.effective_end_date
AND    dei.person_id = people.person_id
AND    dei.document_type_id = hdtl.document_type_id
AND    hdtl.document_type = 'AE_PASSPORT'
AND    hdtl.language = 'US'
AND    dei.dei_information_category = hdtl.document_type
AND    assg.organization_id = org.organization_id
AND    org.organization_id = c_organization_id
AND    org.business_group_id = c_business_group_id
--AND    org.organization_id = p_business_group_id
AND    dei.date_to
BETWEEN NVL(l_date,sysdate) AND
DECODE(p_units,'D',(NVL(l_date,sysdate)+p_expires_in),
               'W',(NVL(l_date,sysdate)+(p_expires_in*7)),
               'M',(add_months(NVL(l_date,sysdate),p_expires_in)),
	       'Y',(add_months(NVL(l_date,sysdate),(p_expires_in*12))))
ORDER BY org.name, expiry_date, full_name;

Line: 458

SELECT pjb.name
FROM   per_all_assignments_f paa,
       per_jobs pjb
WHERE  paa.assignment_id = p_assignment_id
AND    paa.job_id = pjb.job_id;

Line: 477

        SELECT fnd_date.canonical_to_date(p_date)
        INTO   l_date
        FROM   DUAL;

Line: 487

    INSERT INTO fnd_sessions (session_id, effective_date)
    VALUES (userenv('SESSIONID'), l_date);

Line: 496

    gxmltable.DELETE;

Line: 501

        SELECT distinct pose.organization_id_parent
        INTO   l_parent_id
        FROM   per_org_structure_elements pose
        WHERE  pose.org_structure_version_id = p_org_structure_version_id
        AND    pose.organization_id_parent NOT IN (SELECT pose1.organization_id_child
                                                   FROM   per_org_structure_elements pose1
                                                   WHERE  pose1.org_structure_version_id
                                                          = p_org_structure_version_id);

Line: 516

        SELECT name
        INTO   l_org_name
        FROM   hr_organization_units
        WHERE  organization_id = p_business_group_id;

Line: 527

        SELECT name
        INTO   l_structure_name
        FROM   per_organization_structures
        WHERE  organization_structure_id = p_org_structure_id;

Line: 538

        SELECT version_number
        INTO   l_version
        FROM   per_org_structure_versions
        WHERE  org_structure_version_id = p_org_structure_version_id;

Line: 549

        SELECT meaning
        INTO   l_units
        FROM   hr_lookups
        WHERE  lookup_type = 'QUALIFYING_UNITS'
        AND    lookup_code = p_units;

Line: 821

    SELECT business_group_id
    FROM   per_business_groups
    WHERE  business_group_id = nvl(p_business_group_id, business_group_id)
    AND    legislation_code = 'AE';

Line: 827

    SELECT org.organization_id
           ,org.name
    FROM   hr_all_organization_units org
    WHERE  org.organization_id IN (SELECT pose.organization_id_child
                                   FROM   per_org_structure_elements pose
                                   CONNECT BY pose.organization_id_parent = PRIOR pose.organization_id_child
                                   AND    pose.org_structure_version_id = p_org_structure_version_id
                                   START WITH pose.organization_id_parent = nvl(p_org_id, l_parent_id)
                                   AND    pose.org_structure_version_id = p_org_structure_version_id
                                   UNION
                                   SELECT nvl(p_org_id, l_parent_id)
                                   FROM   DUAL)
    AND    p_org_structure_version_id IS NOT NULL
    UNION
    SELECT org.organization_id
           ,org.name
    FROM   hr_all_organization_units org
    WHERE  org.organization_id = NVL(p_org_id,org.organization_id)
    AND    org.business_group_id = p_business_group_id
    AND    p_org_structure_version_id IS NULL;

Line: 849

SELECT people.employee_number
           ,people.full_name
           ,period.date_start
           ,job.name job_name
      	   ,cont.reference cont_reference
	   ,get_lookup_meaning('CONTRACT_TYPE',cont.type) cont_type
	   ,cont.ctr_information1 employment_status
	   ,fnd_date.canonical_to_date(cont.ctr_information2) expiry_date
    FROM   per_all_assignments_f      assg
           ,per_all_people_f          people
	   ,per_contracts             cont
           ,per_jobs                  job
           ,per_periods_of_service    period
           ,hr_all_organization_units org
WHERE assg.person_id = people.person_id
AND     (l_date) BETWEEN assg.effective_start_date
		 AND   assg.effective_end_date
AND     (l_date) BETWEEN people.effective_start_date
		 AND   people.effective_end_date
AND   assg.job_id = job.job_id(+)
AND   people.person_id = period.person_id
AND   assg.organization_id = org.organization_id
AND   org.business_group_id = p_business_group_id
AND   org.organization_id = rec_get_org_id.organization_id
AND   cont.person_id = people.person_id
AND   cont.ctr_information_category = 'AE'
AND   NVL(to_date(ctr_information2,'YYYY/MM/DD HH24:MI:SS'),
      DECODE(duration_units,'D',(active_start_date+duration),
                          'W',(active_start_date+(duration*7)),
                          'M',(add_months(active_start_date,duration)),
                          'Y',(add_months(active_start_date,(duration*12)))))
      BETWEEN NVL(l_date,sysdate)
      AND DECODE(p_units,'D',(NVL(l_date,sysdate)+p_expires_in),
                           'W',(NVL(l_date,sysdate)+(p_expires_in*7)),
                           'M',(add_months(NVL(l_date,sysdate),p_expires_in)),
                           'Y',(add_months(NVL(l_date,sysdate),(p_expires_in*12))))
ORDER BY  full_name, employee_number;

Line: 900

        SELECT fnd_date.canonical_to_date(p_date)
        INTO   l_date
        FROM   DUAL;

Line: 910

    INSERT INTO fnd_sessions (session_id, effective_date)
    VALUES (userenv('SESSIONID'), l_date);

Line: 919

    gxmltable.DELETE;

Line: 924

        SELECT distinct pose.organization_id_parent
        INTO   l_parent_id
        FROM   per_org_structure_elements pose
        WHERE  pose.org_structure_version_id = p_org_structure_version_id
        AND    pose.organization_id_parent NOT IN (SELECT pose1.organization_id_child
                                                   FROM   per_org_structure_elements pose1
                                                   WHERE  pose1.org_structure_version_id
                                                          = p_org_structure_version_id);

Line: 939

        SELECT name
        INTO   l_org_name
        FROM   hr_organization_units
        WHERE  organization_id =/* p_business_group_id*/ p_org_id ;

Line: 950

        SELECT name
        INTO   l_structure_name
        FROM   per_organization_structures
        WHERE  organization_structure_id = p_org_structure_id;

Line: 961

        SELECT version_number
        INTO   l_version
        FROM   per_org_structure_versions
        WHERE  org_structure_version_id = p_org_structure_version_id;

Line: 972

        SELECT meaning
        INTO   l_units
        FROM   hr_lookups
        WHERE  lookup_type = 'QUALIFYING_UNITS'
        AND    lookup_code = p_units;

Line: 1246

	select userenv('LANGUAGE') into g_nls_db_char from dual;

Line: 1278

      SELECT file_data
      INTO   p_pdf_blob
      FROM   fnd_lobs
      WHERE  file_id = (SELECT MAX(file_id)
                       FROM    fnd_lobs
                       WHERE   file_name like '%PER_VIS_ar_AE.pdf');

Line: 1285

      SELECT file_data
      INTO   p_pdf_blob
      FROM   fnd_lobs
      WHERE  file_id = (SELECT MAX(file_id)
                       FROM    fnd_lobs
                       WHERE   file_name like '%PER_PASS_ar_AE.pdf');

Line: 1292

      SELECT file_data
      INTO   p_pdf_blob
      FROM   fnd_lobs
      WHERE  file_id = (SELECT MAX(file_id)
                       FROM    fnd_lobs
                       WHERE   file_name like '%PER_CTR_ar_AE.rtf');

Line: 1309

    select meaning
    from   hr_lookups
    where  lookup_type = p_lookup_type
    and    lookup_code = p_lookup_code;