/* $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_document_types_v
AS
 SELECT hdt.document_type,
  hdt.description,
  hrl.meaning CATEGORY_NAME,
  hrl.lookup_code CATEGORY_CODE,
  hdt.document_type_id,
  pst.shared_type_name SUB_CATEGORY_NAME,
  pst.shared_type_code SUB_CATEGORY_CODE,
  NVL(HDT.AUTHORIZATION_REQUIRED,'N') AUTHORIZATION_REQUIRED,
  hdt.system_document_type,
  hdt.legislation_code
FROM per_shared_types_vl pst,
  hr_lookups hrl,
  hr_document_types_v hdt
WHERE pst.lookup_type (+)    = 'DOCUMENT_CATEGORY'
AND pst.shared_type_code (+) = hdt.sub_category_code
AND pst.system_type_cd (+)   = hdt.category_code
AND hrl.lookup_type          = 'DOCUMENT_CATEGORY'
AND hrl.lookup_code          = hdt.category_code
AND EXISTS
  (SELECT '1'
  FROM per_info_type_security_v pits
  WHERE pits.info_type_table_name = 'HR_DOCUMENT_TYPES_V'
  AND pits.information_type       = hdt.system_document_type
  /*AND pits.responsibility_id      = fnd_profile.value('RESP_ID')
  AND pits.application_id         = fnd_profile.value('RESP_APPL_ID')*/
  );
/
COMMIT;
EXIT;

