/* $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_vacancy_offer_actions_v
AS
SELECT offer.offer_id
      ,lookup.lookup_code action_code
      ,decode
         (lookup.lookup_code
         ,'IRC_PREVIEW_OFFER_LETTER'
         ,(SELECT meaning
           FROM   hr_lookups
           WHERE  lookup_type  = 'IRC_OFFER_ACTION'
           AND     lookup_code = 'IRC_TEMPLATE_OFFER_LETTER')
         ,lookup.meaning) as action_meaning
FROM   hr_lookups lookup
      ,irc_offers offer
      ,irc_offer_status_history history
WHERE lookup.lookup_type = 'IRC_OFFER_ACTION'
AND   history.offer_status_history_id =
        (SELECT max(offer_status_history_id)
         FROM   irc_offer_status_history
         WHERE  offer_id = offer.offer_id)
AND  (
       (offer.offer_status = 'SAVED' AND
        lookup.lookup_code IN
         ('IRC_OFFER_DELETE', 'IRC_TEMPLATE_OFFER_LETTER',
          'IRC_MAINTAIN_OFFER_ADDRESS','IRC_UPDATE_OFFER_DETAILS',
          'IRC_CLOSE_OFFER', 'IRC_HOLD_OFFER'))
     OR
       (offer.offer_status = 'PENDING' AND
        lookup.lookup_code IN
         ('IRC_VIEW_OFFER_NOTES'
         ,decode(offer.offer_version, 1, 'NULL', 'IRC_VIEW_OFFER_HISTORY')
         ,'IRC_PREVIEW_OFFER_LETTER', 'IRC_MAINTAIN_OFFER_ADDRESS', 'IRC_HOLD_OFFER'))
     OR
       (offer.offer_status = 'CORRECTION' AND
       lookup.lookup_code IN
         ('IRC_OFFER_DELETE','IRC_CLOSE_OFFER',
          'IRC_TEMPLATE_OFFER_LETTER', 'IRC_MAINTAIN_OFFER_ADDRESS',
          'IRC_VIEW_OFFER_NOTES'
          ,decode(offer.offer_version,1, 'NULL', 'IRC_VIEW_OFFER_HISTORY')
          ,'IRC_HOLD_OFFER'
          ,decode(fnd_global.user_id, offer.created_by, 'IRC_UPDATE_OFFER_DETAILS', 'NULL')))
     OR
      (offer.offer_status = 'APPROVED' AND
       lookup.lookup_code in
         ('IRC_UPDATE_OFFER_DETAILS', 'IRC_SEND_OFFER',
          'IRC_CLOSE_OFFER', 'IRC_VIEW_OFFER_NOTES'
          ,decode(offer.offer_version, 1, 'NULL', 'IRC_VIEW_OFFER_HISTORY'), 'IRC_HOLD_OFFER'
          ,decode(irc_utilities_pkg.is_function_allowed('IRC_UPDATE_APPROVED_OFFER'), 'TRUE', 'IRC_MAINTAIN_OFFER_ADDRESS','NULL')
          ,decode
            (irc_utilities_pkg.is_function_allowed('IRC_UPDATE_APPROVED_OFFER')
            ,'TRUE', decode(nvl((SELECT update_allowed
                                 FROM   irc_rec_team_members rec
                                 WHERE  rec.vacancy_id  = offer.vacancy_id
                                 AND    rec.person_id = fnd_global.employee_id)
                               ,'Y')
                            ,'Y', 'IRC_TEMPLATE_OFFER_LETTER'
                            ,'IRC_PREVIEW_OFFER_LETTER')
            ,'IRC_PREVIEW_OFFER_LETTER')))
     OR
      (offer.offer_status = 'EXTENDED' AND
       lookup.lookup_code IN
         ('IRC_UPDATE_OFFER_DETAILS',
          'IRC_EXTEND_OFFER_DURATION', 'IRC_CLOSE_OFFER',
          'IRC_ENTER_APL_RESP2OFFER', 'IRC_PREVIEW_OFFER_LETTER',
          'IRC_VIEW_OFFER_NOTES'
          ,decode(offer.offer_version, 1, 'NULL', 'IRC_VIEW_OFFER_HISTORY')
          ,'IRC_HOLD_OFFER', 'IRC_TRACK_OFFER_LETTER'))
     OR
     (offer.offer_status = 'PENDING_EXTENDED' AND
      lookup.lookup_code IN
        ('IRC_CLOSE_OFFER', 'IRC_ENTER_APL_RESP2OFFER',
        'IRC_PREVIEW_OFFER_LETTER', 'IRC_VIEW_OFFER_NOTES'
        ,decode(offer.offer_version, 1, 'NULL', 'IRC_VIEW_OFFER_HISTORY')
        ,'IRC_HOLD_OFFER', 'IRC_TRACK_OFFER_LETTER'))
     OR
     (offer.offer_status = 'CLOSED' AND
      lookup.lookup_code IN
        (decode((SELECT 1
                 FROM  per_all_assignments_f
                 WHERE assignment_id = offer.applicant_assignment_id
                 AND assignment_type = 'A'
                 AND trunc(sysdate)  < effective_end_date)
                ,1, 'IRC_UPDATE_OFFER_DETAILS'
                ,'NULL')
        ,'IRC_PREVIEW_OFFER_LETTER', 'IRC_VIEW_OFFER_NOTES'
        ,decode(offer.offer_version, 1, 'NULL', 'IRC_VIEW_OFFER_HISTORY')
        ,decode((SELECT 1
                 FROM   per_all_assignments_f
                 WHERE assignment_id = offer.applicant_assignment_id
                 AND assignment_type = 'A'
                 AND trunc(sysdate)  < effective_end_date)
                ,1, 'IRC_HOLD_OFFER'
                ,'NULL')
        ,decode(history.change_reason, 'EXPIRED', 'IRC_EXTEND_OFFER_DURATION','NULL')))
     OR
     (offer.offer_status = 'HOST' AND
     lookup.lookup_code IN
       ('IRC_VIEW_OFFER_NOTES'
       ,decode(offer.offer_version, 1, 'NULL', 'IRC_VIEW_OFFER_HISTORY')
       ,'IRC_UNHOLD_OFFER', 'IRC_PREVIEW_OFFER_LETTER'))
     )
AND (('Y' = nvl((SELECT update_allowed
                 FROM   irc_rec_team_members rec
                 WHERE  rec.vacancy_id  = offer.vacancy_id
                 AND    rec.person_id = fnd_global.employee_id)
               ,'Y')
     )
   OR
    ('N' = nvl((SELECT update_allowed
                FROM   irc_rec_team_members rec
                WHERE  rec.vacancy_id  = offer.vacancy_id
                AND    rec.person_id = fnd_global.employee_id)
              ,'Y')
     AND
     lookup.lookup_code IN
       ('IRC_PREVIEW_OFFER_LETTER'
       ,decode(offer.offer_version, 1, 'NULL','IRC_VIEW_OFFER_HISTORY')
       ,'IRC_VIEW_OFFER_NOTES'))
    )
/*AND EXISTS(SELECT null
           FROM   fnd_form_functions func
           WHERE  lookup.lookup_code = func.function_name
           AND EXISTS
             (SELECT function_id
              FROM   fnd_menu_entries menu
              WHERE  menu.function_id IS NOT NULL
              AND    func.function_id = menu.function_id
              CONNECT BY PRIOR menu.sub_menu_id = menu.menu_id
              START WITH menu.menu_id =
                (SELECT resp.menu_id
                 FROM   fnd_responsibility resp
                 WHERE  resp.responsibility_id = :6
                 AND resp.application_id  = 800))); */;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_VACANCY_OFFER_ACTIONS_V'
   ,p_description            => 'Applaud Vacancy Offer Actions');
END;
/

COMMIT;
EXIT;

