select to_char(pgilf.effective_start_Date,'DD/MM/YYYY') effective_start_Date,
fs.set_code grade_set,
pglft.NAME GradeLadderName,
( SELECT pldgt.name FROM per_legislative_data_groups_tl pldgt
WHERE pldgt.legislative_data_group_id = prf.legislative_data_group_id
)legislative_data_group,
pglf.GRADE_LADDER_GRP_CODE Grade_Ladder_Group,
decode(pglf.ACTIVE_STATUS,'A','Active','I','Inactive',pglf.ACTIVE_STATUS) ACTIVE_STATUS,
(select meaning
from fnd_lookup_values flv
where lookup_code = pglf.PROGRESSION_STYLE_CODE
and lookup_type = 'ORA_CMP_GSP_PROG_STYLE'
)ProgressionIncrement,
(select meaning
from fnd_lookup_values flv
where lookup_code = pglf.PROGRESSION_DATE_CODE
and lookup_type = 'ORA_CMP_GSP_PROG_DT'
)PROGRESSION_TRANSACTION_DATE,
(select meaning
from fnd_lookup_values flv
where lookup_code = PGLF.AUTO_PROGRESSION_CODE
and lookup_type = 'ORA_CMP_GSP_AUTO_APPROVE'
)Progression_Confirmationtype,
(select patl.ACTION_NAME
from PER_ACTIONS_TL patl
where pglf.PROG_ACTION_ID = patl.ACTION_ID
) Assignment_Action,
(select partl.ACTION_REASON
from PER_ACTION_REASONS_TL partl
where pglf.PROG_ACTION_REASON_ID = partl.ACTION_REASON_ID
) Assignment_Action_Reason,
pglf.UPDATE_SALARY_FLAG INCLUDE_SALARY_UPDATE,
prf.CURRENCY_CODE,
prf.RATE_FREQUENCY FREQUENCY,
prf.ANNUALIZATION_FACTOR,
(select meaning
from fnd_lookup_values flv
where lookup_code = pglf.SALARY_CALC_METHOD_CODE
and lookup_type = 'ORA_CMP_GSP_SAL_CALC_METH'
)SALARY_CALC_METHOD,
(select meaning
from fnd_lookup_values flv
where lookup_code = pglf.SALARY_CHANGE_DATE_CODE
and lookup_type = 'ORA_CMP_GSP_SAL_CHG_DT'
)SALARY_TRANSACTION_DATE,
(select meaning
from fnd_lookup_values flv
where lookup_code = PGLF.SALARY_ADJUSTMENT_TYPE_CODE
and lookup_type = 'ORA_CMP_GSP_SALARY_ADJ_TYPE'
)SalaryAdjustmentType,
(select meaning
from fnd_lookup_values flv
where lookup_code = pglf.RATE_CHANGE_DATE_CODE
and lookup_type = 'ORA_CMP_GSP_RATE_SYNC_DT'
)RATE_TRANSACTION_DATE,
(select meaning
from fnd_lookup_values flv
where lookup_code = PGLF.AUTO_SAL_CHANGE_CODE
and lookup_type = 'ORA_CMP_GSP_AUTO_APPROVE'
) Rate_Confirmationtype,
(select meaning
from fnd_lookup_values flv
where lookup_code = pglf.grade_type
and lookup_type = 'GRADE_TYPE'
)Grade_Type,
pg.NAME Grade_name,
pg.grade_code,
pgsf.name grade_step_name,
--prft.name Grade_rate_name,
prf.rate_type,
prv.value
,decode( pgs.sequence, (
select sequence
from PER_GRADE_STEPS_F pgsf
where pgsf.grade_step_id = pg.CEILING_STEP_ID
), 'Y','') CEILING_STEP
from PER_GRADES_IN_LADDERS_F pgilf
, PER_GRADES pg
, PER_GRADE_LADDERS_F_TL pglft
, fnd_setid_sets_vl fs
, PER_GRADE_LADDERS_F pglf
, PER_GRADE_STEPS_F PGS
, PER_GRADE_STEPS_F_TL PGSF
, PER_RATE_VALUES_F prv
, PER_RATES_F prf
--, PER_RATES_F_TL PRFT
where pgilf.GRADE_ID = pg.GRADE_ID
AND pgilf.GRADE_LADDER_ID = pglf.GRADE_LADDER_ID
--AND pglft.NAME = :P_GRADE_LADDER_NAME
AND fs.set_id = pg.set_id
and pglft.GRADE_LADDER_ID = pglf.GRADE_LADDER_ID(+)
AND PG.GRADE_ID = PGS.GRADE_ID
AND PGS.grade_step_id = PGSF.grade_step_id(+)
AND TRUNC(SYSDATE) BETWEEN PGSF.effective_start_date AND PGSF.effective_end_date
AND TRUNC(SYSDATE) BETWEEN PG.effective_start_date AND PG.effective_end_date
AND PGS.grade_step_id = PRV.rate_object_id
AND prv.rate_id=prf.rate_id
--AND prv.rate_id=prft.rate_id
ORDER BY pglft.NAME, PG.GRADE_CODE
No comments:
Post a Comment