View: GHR_PA_REQUESTS_V1

Product:	GHR - US Federal Human Resources
Description:	This view holds the RPA and NPA information.
Implementation/DBA Data:	APPS.GHR_PA_REQUESTS_V1

View Text

SELECT CORR.EFFECTIVE_DATE EFFECTIVE_DATE
, CORR.NOA_CODE NOA_CODE
, CORR.ROW_NUM ROW_NUM
, CORR.HIERARCHY_LEVEL HIERARCHY_LEVEL
, CORR.PA_REQUEST_ID PA_REQUEST_ID
, CORR.ROUTING_GROUP_ID ROUTING_GROUP_ID
, CORR.PA_NOTIFICATION_ID PA_NOTIFICATION_ID
, CORR.APPROVAL_DATE APPROVAL_DATE
, CORR.NOA_FAMILY_CODE NOA_FAMILY_CODE
, CORR.NOA_ID NOA_ID
, CORR.NOA_DESC NOA_DESC
, CORR.PERSON_ID PERSON_ID
, CORR.REQUEST_NUMBER REQUEST_NUMBER
, CORR.APPROVING_OFFICIAL_WORK_TITLE APPROVING_OFFICIAL_WORK_TITLE
, CORR.FROM_ORGANIZATION_NAME FROM_ORGANIZATION_NAME
, CORR.TO_ORGANIZATION_NAME TO_ORGANIZATION_NAME
, CORR.WHICH_NOA WHICH_NOA
, CORR.ROW_ID ROW_ID
, CORR.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, CORR.ACTION_TYPE ACTION_TYPE
, CORR.PERSONNEL_OFFICE_ID PERSONNEL_OFFICE_ID
, CORR.ALTERED_PA_REQUEST_ID ALTERED_PA_REQUEST_ID
, CORR.FIRST_NOA_CANCEL_OR_CORRECT FIRST_NOA_CANCEL_OR_CORRECT
, CORR.STATUS STATUS
, CORR.FIRST_NOA_PA_REQUEST_ID FIRST_NOA_PA_REQUEST_ID
, CORR.INDENT INDENT
, CORR.CREATED_BY CREATED_BY
, CORR.CREATION_DATE CREATION_DATE
, CORR.LAST_UPDATED_BY LAST_UPDATED_BY
, CORR.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CORR.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, RGP.NAME ROUTING_GROUP_NAME
, FAM.NAME NOA_FAMILY_NAME
, NOA.ORDER_OF_PROCESSING ORDER_OF_PROCESSING
FROM ( SELECT EFFECTIVE_DATE
, DECODE(FIRST_NOA_CODE
, '002'
, SECOND_NOA_CODE
, '001'
, SECOND_NOA_CODE
, FIRST_NOA_CODE) NOA_CODE
, ROWNUM ROW_NUM
, LEVEL HIERARCHY_LEVEL
, PA_REQUEST_ID
, PAR.ROUTING_GROUP_ID
, PA_NOTIFICATION_ID
, APPROVAL_DATE
, PAR.NOA_FAMILY_CODE
, DECODE(FIRST_NOA_CODE
, '002'
, SECOND_NOA_ID
, '001'
, SECOND_NOA_ID
, FIRST_NOA_ID) NOA_ID
, DECODE(FIRST_NOA_CODE
, '002'
, SECOND_NOA_DESC
, '001'
, SECOND_NOA_DESC
, FIRST_NOA_DESC) NOA_DESC
, PAR.PERSON_ID
, REQUEST_NUMBER
, APPROVING_OFFICIAL_WORK_TITLE
, FROM_POSITION_ORG_LINE1 FROM_ORGANIZATION_NAME
, TO_POSITION_ORG_LINE1 TO_ORGANIZATION_NAME
, 1 WHICH_NOA
, PAR.ROWID ROW_ID
, PAR.OBJECT_VERSION_NUMBER
, DECODE(PA_NOTIFICATION_ID
, NULL
, 'ROUTED'
, 'PROCESSED' ) ACTION_TYPE
, PERSONNEL_OFFICE_ID
, ALTERED_PA_REQUEST_ID
, FIRST_NOA_CANCEL_OR_CORRECT
, PAR.STATUS
, FIRST_NOA_PA_REQUEST_ID
, DECODE(LEVEL
, 1
, '-'
, ' -') INDENT
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
FROM GHR_PA_REQUESTS PAR
WHERE (LEVEL = 1
AND PA_NOTIFICATION_ID IS NOT NULL) OR (LEVEL > 1
AND ( NVL(STATUS
, 'CANCELED') != 'CANCELED'
AND NVL(SECOND_NOA_CANCEL_OR_CORRECT
, 'NULL') != 'CANCEL'
AND FIRST_NOA_CODE != '001' ) ) START WITH ALTERED_PA_REQUEST_ID IS NULL
AND NVL(FIRST_NOA_CANCEL_OR_CORRECT
, 'NULL') != 'CANCEL'
AND FIRST_NOA_CANC_PA_REQUEST_ID IS NULL CONNECT BY PRIOR PA_REQUEST_ID = ALTERED_PA_REQUEST_ID
AND PRIOR DECODE(FIRST_NOA_CODE
, '002'
, SECOND_NOA_CODE
, '001'
, SECOND_NOA_CODE
, FIRST_NOA_CODE) = SECOND_NOA_CODE UNION ALL SELECT EFFECTIVE_DATE
, SECOND_NOA_CODE
, ROWNUM
, LEVEL
, PA_REQUEST_ID
, PAR.ROUTING_GROUP_ID
, PA_NOTIFICATION_ID
, APPROVAL_DATE
, PAR.NOA_FAMILY_CODE
, SECOND_NOA_ID
, SECOND_NOA_DESC
, PAR.PERSON_ID
, REQUEST_NUMBER
, APPROVING_OFFICIAL_WORK_TITLE
, FROM_POSITION_ORG_LINE1
, TO_POSITION_ORG_LINE1
, 2 WHICH_NOA
, PAR.ROWID
, PAR.OBJECT_VERSION_NUMBER
, DECODE(PA_NOTIFICATION_ID
, NULL
, 'ROUTED'
, 'PROCESSED')
, PERSONNEL_OFFICE_ID
, ALTERED_PA_REQUEST_ID
, SECOND_NOA_CANCEL_OR_CORRECT
, PAR.STATUS
, SECOND_NOA_PA_REQUEST_ID
, DECODE(LEVEL
, 1
, '-'
, ' -')
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
FROM GHR_PA_REQUESTS PAR
WHERE (LEVEL = 1
AND PA_NOTIFICATION_ID IS NOT NULL) OR (LEVEL > 1
AND ( NVL(STATUS
, 'CANCELED') != 'CANCELED'
AND NVL(SECOND_NOA_CANCEL_OR_CORRECT
, 'NULL') != 'CANCEL'
AND FIRST_NOA_CODE != '001' ) ) START WITH ALTERED_PA_REQUEST_ID IS NULL
AND NVL(SECOND_NOA_CANCEL_OR_CORRECT
, 'NULL') != 'CANCEL'
AND SECOND_NOA_CODE IS NOT NULL
AND SECOND_NOA_CANC_PA_REQUEST_ID IS NULL CONNECT BY PRIOR PA_REQUEST_ID = ALTERED_PA_REQUEST_ID
AND PRIOR SECOND_NOA_CODE = SECOND_NOA_CODE ) CORR
, GHR_FAMILIES FAM
, GHR_ROUTING_GROUPS RGP
, GHR_NATURE_OF_ACTIONS NOA
WHERE CORR.NOA_FAMILY_CODE = FAM.NOA_FAMILY_CODE
AND CORR.ROUTING_GROUP_ID = RGP.ROUTING_GROUP_ID
AND CORR.NOA_ID = NOA.NATURE_OF_ACTION_ID

Columns