View: PQH_CORPS_NATURE_HGRID_V

Product:	PQH - Public Sector HR
Description:	Corp Nature view
Implementation/DBA Data:	APPS.PQH_CORPS_NATURE_HGRID_V

View Text

SELECT 0 SOURCE_NODE_TYPE
, 1 DES_NODE_TYPE
, HGRID.LOOKUP_CODE DES_NODE_ID
, HGRID.MEANING DES_NODE_VALUE
, TO_CHAR(NULL) SOURCE_NODE_ID
, TO_CHAR(NULL) REGION_CODE
, TO_CHAR(NULL) NATURE_TYPE_CD
, TO_CHAR(NULL) NATURE
, TO_CHAR(NULL) FILERE_CD
, TO_CHAR(NULL) FILERE
FROM HR_LOOKUPS HGRID
WHERE LOOKUP_TYPE = 'PQH_CORPS_HGRID_NAME' UNION SELECT 1 SOURCE_NODE_TYPE
, 2 DES_NODE_TYPE
, LOOKUP_CODE DES_NODE_ID
, MEANING DES_NODE_VALUE
, 'NATURE_ROOT' SOURCE_NODE_ID
, TO_CHAR(NULL) REGION_CODE
, LOOKUP_CODE NATURE_TYPE_CD
, MEANING NATURE
, TO_CHAR(NULL) FILERE_CD
, TO_CHAR(NULL) FILERE
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'FR_PQH_ORG_CATEGORY' UNION SELECT 2 SOURCE_NODE_TYPE
, 3 DES_NODE_TYPE
, FIL.LOOKUP_CODE DES_NODE_ID
, FIL.MEANING DES_NODE_VALUE
, NAT.LOOKUP_CODE SOURCE_NODE_ID
, TO_CHAR(NULL) REGION_CODE
, NAT.LOOKUP_CODE NATURE_TYPE_CD
, NAT.MEANING NATURE
, FIL.LOOKUP_CODE FILERE_CD
, FIL.MEANING FILERE
FROM HR_LOOKUPS NAT
, HR_LOOKUPS FIL
WHERE NAT.LOOKUP_TYPE = 'FR_PQH_ORG_CATEGORY'
AND FIL.LOOKUP_TYPE = 'PQH_CORPS_FILERES' UNION SELECT 3 SOURCE_NODE_TYPE
, 4 DES_NODE_TYPE
, TO_CHAR(COR.CORPS_DEFINITION_ID) DES_NODE_ID
, NAME DES_NODE_VALUE
, CFIL.FILERE_CD SOURCE_NODE_ID
, 'PQH_CORPS_DEF_UPDATE_PAGE' REGION_CODE
, NAT.LOOKUP_CODE NATURE_TYPE_CD
, NAT.MEANING NATURE
, CFIL.FILERE_CD FILERE_CD
, CFIL.FILERE_NAME FILERE
FROM PQH_CORPS_DEFINITIONS COR
, HR_LOOKUPS NAT
, PQH_CORPS_FILERES_V CFIL
WHERE COR.CORPS_DEFINITION_ID = CFIL.CORPS_DEFINITION_ID
AND NAT.LOOKUP_CODE = COR.NATURE_TYPE_CD
AND NAT.LOOKUP_TYPE = 'FR_PQH_ORG_CATEGORY'
AND DECODE(HR_GENERAL.GET_XBG_PROFILE
, 'Y'
, BUSINESS_GROUP_ID
, HR_GENERAL.GET_BUSINESS_GROUP_ID) = BUSINESS_GROUP_ID UNION SELECT 4 SOURCE_NODE_TYPE
, 5 DES_NODE_TYPE
, CGR.GRADE_ID DES_NODE_ID
, CGR.GRADE_NAME DES_NODE_VALUE
, TO_CHAR(COR.CORPS_DEFINITION_ID) SOURCE_NODE_ID
, TO_CHAR(NULL) REGION_CODE
, COR.NATURE_TYPE_CD NATURE_TYPE_CD
, TO_CHAR(NULL) NATURE
, TO_CHAR(NULL) FILERE_CD
, TO_CHAR(NULL) FILERE
FROM PQH_CORPS_DEFINITIONS COR
, PQH_CORPS_GRADES_V CGR
WHERE COR.CORPS_DEFINITION_ID = CGR.CORPS_DEFINITION_ID UNION SELECT 5 SOURCE_NODE_TYPE
, 6 DES_NODE_TYPE
, TO_CHAR(SPS.STEP_ID) DES_NODE_ID
, SPT.SPINAL_POINT DES_NODE_VALUE
, CGR.GRADE_ID SOURCE_NODE_ID
, TO_CHAR(NULL) REGION_CODE
, COR.NATURE_TYPE_CD NATURE_TYPE_CD
, TO_CHAR(NULL) NATURE
, TO_CHAR(NULL) FILERE_CD
, TO_CHAR(NULL) FILERE
FROM PER_GRADE_SPINES_F GSP
, PER_SPINAL_POINT_STEPS_F SPS
, PER_SPINAL_POINTS SPT
, PQH_CORPS_GRADES_V CGR
, PQH_CORPS_DEFINITIONS COR
WHERE GSP.GRADE_SPINE_ID = SPS.GRADE_SPINE_ID
AND SPS.SPINAL_POINT_ID = SPT.SPINAL_POINT_ID
AND GSP.GRADE_ID = CGR.GRADE_ID
AND CGR.CORPS_DEFINITION_ID = COR.CORPS_DEFINITION_ID
AND TRUNC(SYSDATE) BETWEEN SPS.EFFECTIVE_START_DATE
AND NVL(SPS.EFFECTIVE_END_DATE
, HR_GENERAL.END_OF_TIME)
AND TRUNC(SYSDATE) BETWEEN GSP.EFFECTIVE_START_DATE
AND NVL(GSP.EFFECTIVE_END_DATE
, HR_GENERAL.END_OF_TIME)

Columns