• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:June 22, 2022
  • Reading time:2 mins read
You are currently viewing How to extract document types for all legislations?
How to extract document types for all legislations?

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
If you like the content, please follow us on LinkedInFacebook, and Twitter to get updated with the latest content.