In this article we will look into extracting the document types from an instance.
Document types vary by legislation. Oracle provides some seeded document types and we can create custom ones as well as per the requirement.
We can extract the document types using a SQL Query:
SELECT DISTINCT HDTT.DOCUMENT_TYPE AS "Document Type"
,(
SELECT DISTINCT hl.meaning
FROM fnd_common_lookups hl
WHERE hl.lookup_code = HDTB.CATEGORY_CODE
AND hl.lookup_type = 'DOCUMENT_CATEGORY'
) AS "Category Code"
,HDTB.SUB_CATEGORY_CODE
,CASE
WHEN HDTB.ACTIVE_INACTIVE_FLAG = 'Y'
THEN 'Active'
ELSE 'Inactive'
END AS "Status"
,NVL((
SELECT DISTINCT hl.meaning
FROM fnd_lookup_values hl
WHERE hl.lookup_code = 'ORA_' || HDTB.LEGISLATION_CODE
AND hl.lookup_type = 'ORA_HER_COUNTRY_CODE'
), 'All Countries') AS "Country"
,HDTB.MULTIPLE_OCCURENCES_FLAG
,HDTB.AUTHORIZATION_REQUIRED
,HDTB.DOCUMENT_NAME_REQUIRED
,HDTB.DOCUMENT_NUMBER_REQUIRED
,HDTB.DATE_FROM_REQUIRED
,HDTB.DATE_TO_REQUIRED
,HDTB.ISSUING_AUTHORITY_REQUIRED
,HDTB.ISSUED_DATE_REQUIRED
,HDTB.ISSUING_COUNTRY_REQUIRED
,HDTB.ISSUING_LOCATION_REQUIRED
,HDTB.COMMENTS_REQUIRED
,HDTB.PUBLISH_REQUIRED
,HDTB.SYSTEM_DOCUMENT_TYPE
FROM HR_DOCUMENT_TYPES_B HDTB
,HR_DOCUMENT_TYPES_TL HDTT
WHERE 1 = 1
AND HDTB.DOCUMENT_TYPE_ID = HDTT.DOCUMENT_TYPE_ID
