View: HRFV_GRADE_RATE_ASGS_BG

Product:	PER - Human Resources
Description:	Business view template from which the flexfield view is generated.
Implementation/DBA Data:	APPS.HRFV_GRADE_RATE_ASGS_BG

View Text

SELECT PEO.FULL_NAME PERSON_NAME
, GRD.NAME GRADE_NAME
, PPB.NAME SALARY_BASIS
, PPB.RATE_BASIS GRADE_RATE_BASIS
, RAT.NAME GRADE_RATE_NAME
, GRL.MAXIMUM GRADE_MAXIMUM
, GRL.MID_VALUE GRADE_MID_VALUE
, GRL.MINIMUM GRADE_MINIMUM
, GRL.VALUE GRADE_RATE_VALUE
, PPB.GRADE_ANNUALIZATION_FACTOR GRADE_ANNUALIZATION_FACTOR
, HR_BIS.BIS_DECODE_LOOKUP('UNITS'
, RAT.RATE_UOM) UNIT_OF_MEASURE
, PEO.EFFECTIVE_START_DATE PERSON_START_DATE
, PEO.EFFECTIVE_END_DATE PERSON_END_DATE
, ASG.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE
, ASG.EFFECTIVE_END_DATE ASSIGNMENT_END_DATE
, GRL.EFFECTIVE_START_DATE GRADE_RULE_START_DATE
, GRL.EFFECTIVE_END_DATE GRADE_RULE_END_DATE
, '_DF:PAY:PAY_RATES:RAT'
, ASG.PERSON_ID PERSON_ID
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG.ORGANIZATION_ID ORGANIZATION_ID
, ASG.JOB_ID JOB_ID
, ASG.POSITION_ID POSITION_ID
, ASG.GRADE_ID GRADE_ID
, ASG.LOCATION_ID LOCATION_ID
, GRL.GRADE_RULE_ID GRADE_RULE_ID
, ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, GRL.RATE_ID RATE_ID
, PPB.PAY_BASIS_ID PAY_BASIS_ID
FROM PAY_GRADE_RULES_F GRL
, PAY_RATES RAT
, PER_GRADES GRD
, PER_ASSIGNMENTS_F ASG
, PER_ALL_PEOPLE_F PEO
, PER_PAY_BASES PPB
WHERE PEO.PERSON_ID = ASG.PERSON_ID
AND ASG.PAY_BASIS_ID = PPB.PAY_BASIS_ID
AND ASG.GRADE_ID = GRL.GRADE_OR_SPINAL_POINT_ID
AND ASG.GRADE_ID = GRD.GRADE_ID
AND GRL.RATE_ID = RAT.RATE_ID
AND GRL.RATE_ID = NVL(PPB.RATE_ID
, GRL.RATE_ID)
AND GRL.RATE_TYPE = 'G'
AND ASG.ASSIGNMENT_TYPE = 'E'
AND ( (ASG.EFFECTIVE_START_DATE <= PEO.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE >= PEO.EFFECTIVE_START_DATE) OR (ASG.EFFECTIVE_START_DATE <= PEO.EFFECTIVE_END_DATE
AND ASG.EFFECTIVE_END_DATE >= PEO.EFFECTIVE_END_DATE) OR (ASG.EFFECTIVE_START_DATE >= PEO.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE <= PEO.EFFECTIVE_END_DATE) )
AND ( (GRL.EFFECTIVE_START_DATE <= ASG.EFFECTIVE_START_DATE
AND GRL.EFFECTIVE_END_DATE >= ASG.EFFECTIVE_START_DATE) OR (GRL.EFFECTIVE_START_DATE <= ASG.EFFECTIVE_END_DATE
AND GRL.EFFECTIVE_END_DATE >= ASG.EFFECTIVE_END_DATE) OR (GRL.EFFECTIVE_START_DATE >= ASG.EFFECTIVE_START_DATE
AND GRL.EFFECTIVE_END_DATE <= ASG.EFFECTIVE_END_DATE) )
AND ASG.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASG.BUSINESS_GROUP_ID) UNION ALL SELECT PEO.FULL_NAME PERSON_NAME
, GRD.NAME GRADE_NAME
, PPB.NAME SALARY_BASIS
, PPB.RATE_BASIS GRADE_RATE_BASIS
, TO_CHAR(NULL) GRADE_RATE_NAME
, TO_CHAR(NULL) GRADE_MAXIMUM
, TO_CHAR(NULL) GRADE_MID_VALUE
, TO_CHAR(NULL) GRADE_MINIMUM
, TO_CHAR(NULL) GRADE_RATE_VALUE
, PPB.GRADE_ANNUALIZATION_FACTOR GRADE_ANNUALIZATION_FACTOR
, TO_CHAR(NULL) UNIT_OF_MEASURE
, PEO.EFFECTIVE_START_DATE PERSON_START_DATE
, PEO.EFFECTIVE_END_DATE PERSON_END_DATE
, ASG.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE
, ASG.EFFECTIVE_END_DATE ASSIGNMENT_END_DATE
, ASG.EFFECTIVE_START_DATE GRADE_RULE_START_DATE
, ASG.EFFECTIVE_END_DATE GRADE_RULE_END_DATE
, '_DF:_DUMMY:PAY:PAY_RATES:RAT'
, ASG.PERSON_ID PERSON_ID
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG.ORGANIZATION_ID ORGANIZATION_ID
, ASG.JOB_ID JOB_ID
, ASG.POSITION_ID POSITION_ID
, ASG.GRADE_ID GRADE_ID
, ASG.LOCATION_ID LOCATION_ID
, TO_NUMBER(NULL) GRADE_RULE_ID
, ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TO_NUMBER(NULL) RATE_ID
, PPB.PAY_BASIS_ID PAY_BASIS_ID
FROM PER_GRADES GRD
, PER_ASSIGNMENTS_F ASG
, PER_ALL_PEOPLE_F PEO
, PER_PAY_BASES PPB
WHERE PEO.PERSON_ID = ASG.PERSON_ID
AND ASG.PAY_BASIS_ID = PPB.PAY_BASIS_ID
AND ASG.GRADE_ID = GRD.GRADE_ID
AND NOT EXISTS (SELECT 1
FROM PAY_GRADE_RULES_F GRL
WHERE GRL.GRADE_OR_SPINAL_POINT_ID = ASG.GRADE_ID
AND ASG.EFFECTIVE_START_DATE BETWEEN GRL.EFFECTIVE_START_DATE
AND GRL.EFFECTIVE_END_DATE)
AND ASG.ASSIGNMENT_TYPE = 'E'
AND ( (ASG.EFFECTIVE_START_DATE <= PEO.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE >= PEO.EFFECTIVE_START_DATE) OR (ASG.EFFECTIVE_START_DATE <= PEO.EFFECTIVE_END_DATE
AND ASG.EFFECTIVE_END_DATE >= PEO.EFFECTIVE_END_DATE) OR (ASG.EFFECTIVE_START_DATE >= PEO.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE <= PEO.EFFECTIVE_END_DATE) )
AND ASG.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASG.BUSINESS_GROUP_ID) WITH READ ONLY

Columns