View: GHR_PD_COVERSHEET_V

Product:	GHR - US Federal Human Resources
Description:	Position Description Coversheet details
Implementation/DBA Data:	APPS.GHR_PD_COVERSHEET_V

View Text

SELECT PDI.POSITION_DESCRIPTION_ID
, PAR.PA_REQUEST_ID
, PDI.OPM_CERT_NUM
, PDI.FLSA
, PDI.FINANCIAL_STATEMENT
, PDI.SUBJECT_TO_IA_ACTION
, PDI.POSITION_STATUS
, PDI.POSITION_IS
, PDI.POSITION_SENSITIVITY
, PDI.COMPETITIVE_LEVEL
, PDI.PD_REMARKS
, PDI.POSITION_CLASS_STD
, PDI.CATEGORY
, PDI.CAREER_LADDER
, PDI.SUPERVISOR_NAME
, PDI.SUPERVISOR_TITLE
, PDI.SUPERVISOR_DATE
, PDI.MANAGER_NAME
, PDI.MANAGER_TITLE
, PDI.MANAGER_DATE
, PDI.CLASSIFIER_NAME
, PDI.CLASSIFIER_TITLE
, PDI.CLASSIFIER_DATE
, POEI.POSITION_ID
, PER.PERSON_ID
, HR_GENERAL.DECODE_LOOKUP('GHR_US_FLSA_CATEGORY'
, PDI.FLSA) D_FLSA
, HR_GENERAL.DECODE_LOOKUP('GHR_US_FINANCIAL_STATEMENT'
, PDI.FINANCIAL_STATEMENT) D_FINANCIAL_STATEMENT
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, PDI.SUBJECT_TO_IA_ACTION) D_SUBJECT_TO_IA_ACTION
, HR_GENERAL.DECODE_LOOKUP('GHR_US_POSITION_OCCUPIED'
, PDI.POSITION_STATUS) D_POSITION_STATUS
, HR_GENERAL.DECODE_LOOKUP('GHR_US_SUPERVISORY_STATUS'
, PDI.POSITION_IS) D_POSITION_IS
, HR_GENERAL.DECODE_LOOKUP('GHR_US_POSN_SENSITIVITY'
, PDI.POSITION_SENSITIVITY) D_POSITION_SENSITIVITY
, HR_GENERAL.DECODE_LOOKUP('GHR_US_COMP_LEVEL'
, PDI.COMPETITIVE_LEVEL) D_COMPETITIVE_LEVEL
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, PDI.CAREER_LADDER) D_CAREER_LADDER
, PER.FULL_NAME
, SUBSTR(GHR_PD_COVERSHEET_DATA.GET_FR_POSITION_GROUP(POEI.POSITION_ID
, 'GHR_US_POS_GRP1'
, 3)
, 1
, 80) EMPLOYING_OFFICE_LOC
, SUBSTR(GHR_PD_COVERSHEET_DATA.GET_FR_POSITION_GROUP(POEI.POSITION_ID
, 'GHR_US_POS_GRP1'
, 12)
, 1
, 80) ORG_TITLE
, SUBSTR(GHR_PD_COVERSHEET_DATA.GET_FR_POSITION_GROUP(POEI.POSITION_ID
, 'GHR_US_POS_GRP1'
, 5)
, 1
, 80) ORG_STRUCTURE_ID
, HR_GENERAL.DECODE_LOOKUP('GHR_US_SUBMISSION_REASON'
, GHR_PD_COVERSHEET_DATA.GET_GEN_EMP(PAR.PA_REQUEST_ID
, 3)) REASON_FOR_SUBMISSION
, HR_GENERAL.DECODE_LOOKUP('GHR_US_SERVICE'
, GHR_PD_COVERSHEET_DATA.GET_GEN_EMP(PAR.PA_REQUEST_ID
, 5)) SERVICE
, SUBSTR(GHR_PD_COVERSHEET_DATA.GET_GEN_EMP(PAR.PA_REQUEST_ID
, 4)
, 1
, 80) EXPLANATION
, SUBSTR(GHR_PD_COVERSHEET_DATA.GET_GEN_EMP(PAR.PA_REQUEST_ID
, 6)
, 1
, 80) AGENCY_USE
, PAR.DUTY_STATION_CODE DUTY_STATION
, NVL(PAR.TO_POSITION_NUMBER
, PAR.FROM_POSITION_NUMBER) || '.' || TO_CHAR(NVL(PAR.TO_POSITION_SEQ_NO
, PAR.FROM_POSITION_SEQ_NO)) AGENCY_POSITION_NUMBER
, PDI.ATTRIBUTE_CATEGORY
, PDI.ATTRIBUTE1
, PDI.ATTRIBUTE2
, PDI.ATTRIBUTE3
, PDI.ATTRIBUTE4
, PDI.ATTRIBUTE5
, PDI.ATTRIBUTE6
, PDI.ATTRIBUTE7
, PDI.ATTRIBUTE8
, PDI.ATTRIBUTE9
, PDI.ATTRIBUTE10
, PDI.ATTRIBUTE11
, PDI.ATTRIBUTE12
, PDI.ATTRIBUTE13
, PDI.ATTRIBUTE14
, PDI.ATTRIBUTE15
, PDI.ATTRIBUTE16
, PDI.ATTRIBUTE17
, PDI.ATTRIBUTE18
, PDI.ATTRIBUTE19
, PDI.ATTRIBUTE20
FROM GHR_POSITION_DESCRIPTIONS PDI
, PER_POSITION_EXTRA_INFO POEI
, HR_POSITIONS_X POS
, GHR_PA_REQUESTS PAR
, PER_PEOPLE_X PER
WHERE PDI.POSITION_DESCRIPTION_ID = TO_NUMBER(POEI.POEI_INFORMATION3)
AND POEI.INFORMATION_TYPE = 'GHR_US_POSITION_DESCRIPTION'
AND POEI.POSITION_ID = NVL(PAR.TO_POSITION_ID
, PAR.FROM_POSITION_ID)
AND POEI.POSITION_ID = POS.POSITION_ID
AND PAR.PERSON_ID = PER.PERSON_ID(+)
AND ( POS.BUSINESS_GROUP_ID = PDI.BUSINESS_GROUP_ID OR PDI.BUSINESS_GROUP_ID IS NULL)

Columns