/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2015 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

CREATE OR REPLACE VIEW xxas_appl_other_job_appls_v 
AS
SELECT job.name as job_name
      ,pos.name as position_name
      ,vac.name as vacancy_name
      ,vac.vacancy_id
      ,org.name as organization_name
      ,loc.derived_locale as location_name
      ,ppf.person_id
      ,ast.external_status as status
      --ast.user_status,
      ,paf.creation_date as date_applied
FROM   per_all_assignments_f paf
      ,per_jobs_vl job
      ,per_positions_v pos
      ,per_assignment_status_types_v ast
      ,per_all_vacancies vac
      ,hr_all_organization_units_vl org
      ,hr_locations_all_vl loc
      ,per_all_people_f ppf
      ,per_all_people_f linkppf
WHERE  paf.assignment_type            = 'A'
AND    paf.vacancy_id                 = vac.vacancy_id
AND    paf.location_id                = loc.location_id(+)
AND    ast.assignment_status_type_id  =
        (SELECT assignment_status_type_id
         FROM   irc_assignment_statuses
         WHERE  assignment_id         = paf.assignment_id
         AND    assignment_status_id  =
                 (SELECT MAX(assignment_status_id)
                  FROM   irc_assignment_statuses
                  WHERE  assignment_id = paf.assignment_id
                 )
        )
AND    EXISTS
        (SELECT NULL
         FROM   per_people_f ppfs
         WHERE  ppfs.person_id = PPF.person_id
        )
AND    DECODE(hr_general.get_xbg_profile,'Y' ,vac.business_group_id, hr_general.get_business_group_id) = vac.business_group_id
AND    paf.job_id                                                                                      = job.job_id(+)
AND    paf.position_id                                                                                 = pos.position_id(+)
AND    paf.organization_id                                                                             = org.organization_id(+)
AND    linkppf.person_id                                                                               = paf.person_id
AND    ppf.party_id                                                                                    = linkppf.party_id
AND    TRUNC(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND    TRUNC(sysdate) BETWEEN linkppf.effective_start_date AND linkppf.effective_end_date
AND    (NOT EXISTS
        (SELECT NULL
         FROM   per_all_people_f nonemp
         WHERE  TRUNC(sysdate) BETWEEN nonemp.effective_start_date AND nonemp.effective_end_date
         AND    nonemp.party_id              = ppf.party_id
         AND    nonemp.current_employee_flag ='Y'
        )
        OR EXISTS
        (SELECT NULL
         FROM   per_all_people_f ppf3
               ,per_all_assignments_f eaa
               ,per_vacancies svc
         WHERE TRUNC(sysdate) BETWEEN ppf3.effective_start_date AND ppf3.effective_end_date
         AND   ppf.party_id        = ppf3.party_id
         AND   eaa.person_id       = ppf3.person_id
         AND   svc.vacancy_id      = eaa.vacancy_id
         AND   eaa.assignment_type = 'A'
        )
       )
AND    TRUNC(sysdate) BETWEEN paf.effective_start_date and paf.effective_end_date;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'xxas_appl_other_job_appls_v'
   ,p_description            => 'Applaud Person Applicant Other Job Applications');
END;
/
  
COMMIT;
EXIT;