VIEW: APPS.GHR_PA_REQUESTS_V1

Source

View Text - Preformatted

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

View Text - HTML Formatted

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