VIEW: APPS.PQH_GSP_ELECTABLE_CHOICE_V

Source

View Text - Preformatted

SELECT Elct.Elig_Per_Elctbl_Chc_id Elig_Per_Elctbl_Chc_id, Elct.Business_group_Id Business_group_id, Elct.Pgm_Id Grade_Ladder_Id, Ler.Typ_Cd Typ_Cd, Pgm.Name Grade_ladder_Name, Pgm.Pgm_Uom Currency_Code, pqh_gsp_utility.Get_Currency(Pgm.Pgm_Uom) Currency_name, Pil.Per_In_Ler_Stat_Cd Per_In_Ler_Stat_Cd, Elct.Pl_id Pl_id, Elct.Oipl_Id Oipl_Id, Pl.Mapping_Table_Pk_Id Grade_Id, Hr_general.Decode_Grade(Pl.Mapping_Table_Pk_Id) Grade_Name, pqh_gsp_utility.Get_Step_Dtls(Elct.Oipl_id, Elct.Enrt_Cvg_Strt_Dt, 'I', 'PROP') Step_Id, pqh_gsp_utility.Get_Step_Dtls(Elct.Oipl_id, Elct.Enrt_Cvg_Strt_Dt, 'N', 'PROP') Step_Name, Enrt_Cvg_Strt_Dt Progression_Date, Person.Person_id Person_Id, Person.First_Name First_Name, Person.Full_Name Full_Name, Person.Work_telephone Work_Phone, Round(pqh_gsp_utility.GET_EMP_LOS(Person.PERSON_ID, Elct.Enrt_Cvg_Strt_Dt),2) LOS, Assgt.Grade_ladder_Pgm_id Current_Grade_Ladder, Ben_Batch_Utils.GET_PGM_NAME(Assgt.Grade_ladder_Pgm_id, Assgt.BUSINESS_GROUP_ID, Elct.Enrt_Cvg_Strt_Dt) Current_Grade_Ladder_Name, Assgt.Grade_Id Current_Grade, Hr_general.Decode_Grade(Assgt.Grade_Id) Current_Grade_Name, pqh_gsp_utility.Get_Step_Dtls(Assgt.Assignment_id, Elct.Enrt_Cvg_Strt_Dt, 'I', 'CURR') Current_Step_Id, pqh_gsp_utility.Get_Step_Dtls(Assgt.Assignment_id, Elct.Enrt_Cvg_Strt_Dt, 'N', 'CURR') Current_Step_NAME, Assgt.Organization_Id Org, Hr_general.Decode_Organization(Assgt.Organization_Id) Org_name, Assgt.Position_Id Position, Hr_general.DECODE_POSITION_LATEST_NAME (Assgt.Position_Id) Position_Name, Assgt.Job_id Job, Hr_general.Decode_Job(Assgt.Job_id) Job_Name, Assgt.Location_id Location, Hr_General.Decode_Location(Assgt.Location_id) Location_Name, Supervisor_id Supervisor, Pqh_gsp_Utility.Get_Person_Name(Supervisor_id, Elct.Enrt_Cvg_Strt_Dt) Manager_Name, Person.Employee_Number Emp_Number, Person.Date_Of_Birth DOB, Person.Start_Date Hire_Date, Person.Original_Date_Of_Hire Orig_Hire_date, Person.Marital_Status Marital_Status, Person.Email_Address Email, Assgt.Assignment_Status_Type_Id Asgt_Status_Id, pqh_gsp_Utility.Get_Assgt_STATUS(Assgt.Assignment_Status_Type_Id) Asgt_Status , Assgt.Collective_Agreement_Id Collective_Agreement_Id, pqh_gsp_utility.GET_CAGR_NAME(Assgt.Collective_Agreement_Id) CAGR_Name, ler.typ_Cd Life_Event_type, Elct.DFLT_FLAG Dflt_Progression, pqh_gsp_utility.Get_Cur_Sal(Assgt.Assignment_id, Elct.Enrt_Cvg_Strt_Dt) Current_Sal, pqh_employee_salary.pgm_to_annual1(Pgm.ACTY_REF_PERD_CD ,Pgm.Pgm_Uom ,Rate.Val ,assgt.assignment_id , elct.enrt_cvg_strt_dt) Proposed_Sal, Rate.Rt_Strt_Dt Sal_Chg_Dt, Assgt.Assignment_Id Assignment_Id, Elct.Approval_Status_Cd Approval_Status_Cd, Pil.Per_In_Ler_id per_in_ler_id, Pil.LF_EVT_OCRD_DT Life_Event_Dt, Person.Last_Name Last_Name, Person.OFFICE_NUMBER OFFICE_NUMBER, elct.in_pndg_wkflow_flag in_pndg_wkflow_flag, elct.request_id request_id, pqh_rank_utility.get_total_score (elct.per_in_ler_id, elct.pgm_id, elct.pl_id ) total_score, pqh_rank_utility.get_rank ('S', elct.per_in_ler_id, elct.pgm_id, elct.pl_id ) system_rank, pqh_rank_utility.get_rank ('P',elct.per_in_ler_id, elct.pgm_id, elct.pl_id ) proposed_rank, pqh_rank_utility.get_ben_action_id (elct.per_in_ler_id, elct.pgm_id, elct.pl_id ) ben_action_id, Rate.Val Rate_Val, pqh_gsp_utility.Get_current_rate_value(Assgt.Assignment_id, Elct.Enrt_Cvg_Strt_Dt) Current_Rate_Value From Ben_Elig_Per_Elctbl_Chc Elct, Ben_Per_in_ler PIL, Ben_Ler_F LER, Ben_Enrt_Rt Rate, Per_People_F Person, Per_All_Assignments_F Assgt, Ben_Pgm_f Pgm, Ben_Pl_F Pl WHERE Elct.Per_in_ler_id = pil.Per_in_Ler_Id and Elct.Enrt_Cvg_Strt_Dt is Not NULL And Pil.Ler_Id = LER.Ler_Id And Nvl(Elct.Enrt_Cvg_Strt_Dt,Sysdate) Between Ler.Effective_start_Date and Ler.Effective_end_Date And ler.typ_Cd = 'GSP' And Pil.person_Id = Person.Person_Id AND elct.business_group_id = person.business_group_id And Rate.ELIG_PER_ELCTBL_CHC_ID(+) = Elct.ELIG_PER_ELCTBL_CHC_ID And Nvl(Elct.Enrt_Cvg_Strt_Dt,Sysdate) Between Person.Effective_start_Date and person.Effective_end_Date And Assgt.Person_id = Person.Person_id And Assgt.PRIMARY_FLAG = 'Y' And Nvl(Elct.Enrt_Cvg_Strt_Dt,Sysdate) Between Assgt.Effective_start_Date and Assgt.Effective_end_Date And Elct.Pgm_id = Pgm.Pgm_Id And Nvl(Elct.Enrt_Cvg_Strt_Dt,Sysdate) Between Pgm.Effective_start_Date and Pgm.Effective_end_Date And Elct.Pl_id = Pl.Pl_Id And Nvl(Elct.Enrt_Cvg_Strt_Dt,Sysdate) Between Pl.Effective_start_Date and Pl.Effective_end_Date And pl.mapping_table_name ='PER_GRADES' And assgt.assignment_type = 'E'

View Text - HTML Formatted

SELECT ELCT.ELIG_PER_ELCTBL_CHC_ID ELIG_PER_ELCTBL_CHC_ID
, ELCT.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, ELCT.PGM_ID GRADE_LADDER_ID
, LER.TYP_CD TYP_CD
, PGM.NAME GRADE_LADDER_NAME
, PGM.PGM_UOM CURRENCY_CODE
, PQH_GSP_UTILITY.GET_CURRENCY(PGM.PGM_UOM) CURRENCY_NAME
, PIL.PER_IN_LER_STAT_CD PER_IN_LER_STAT_CD
, ELCT.PL_ID PL_ID
, ELCT.OIPL_ID OIPL_ID
, PL.MAPPING_TABLE_PK_ID GRADE_ID
, HR_GENERAL.DECODE_GRADE(PL.MAPPING_TABLE_PK_ID) GRADE_NAME
, PQH_GSP_UTILITY.GET_STEP_DTLS(ELCT.OIPL_ID
, ELCT.ENRT_CVG_STRT_DT
, 'I'
, 'PROP') STEP_ID
, PQH_GSP_UTILITY.GET_STEP_DTLS(ELCT.OIPL_ID
, ELCT.ENRT_CVG_STRT_DT
, 'N'
, 'PROP') STEP_NAME
, ENRT_CVG_STRT_DT PROGRESSION_DATE
, PERSON.PERSON_ID PERSON_ID
, PERSON.FIRST_NAME FIRST_NAME
, PERSON.FULL_NAME FULL_NAME
, PERSON.WORK_TELEPHONE WORK_PHONE
, ROUND(PQH_GSP_UTILITY.GET_EMP_LOS(PERSON.PERSON_ID
, ELCT.ENRT_CVG_STRT_DT)
, 2) LOS
, ASSGT.GRADE_LADDER_PGM_ID CURRENT_GRADE_LADDER
, BEN_BATCH_UTILS.GET_PGM_NAME(ASSGT.GRADE_LADDER_PGM_ID
, ASSGT.BUSINESS_GROUP_ID
, ELCT.ENRT_CVG_STRT_DT) CURRENT_GRADE_LADDER_NAME
, ASSGT.GRADE_ID CURRENT_GRADE
, HR_GENERAL.DECODE_GRADE(ASSGT.GRADE_ID) CURRENT_GRADE_NAME
, PQH_GSP_UTILITY.GET_STEP_DTLS(ASSGT.ASSIGNMENT_ID
, ELCT.ENRT_CVG_STRT_DT
, 'I'
, 'CURR') CURRENT_STEP_ID
, PQH_GSP_UTILITY.GET_STEP_DTLS(ASSGT.ASSIGNMENT_ID
, ELCT.ENRT_CVG_STRT_DT
, 'N'
, 'CURR') CURRENT_STEP_NAME
, ASSGT.ORGANIZATION_ID ORG
, HR_GENERAL.DECODE_ORGANIZATION(ASSGT.ORGANIZATION_ID) ORG_NAME
, ASSGT.POSITION_ID POSITION
, HR_GENERAL.DECODE_POSITION_LATEST_NAME (ASSGT.POSITION_ID) POSITION_NAME
, ASSGT.JOB_ID JOB
, HR_GENERAL.DECODE_JOB(ASSGT.JOB_ID) JOB_NAME
, ASSGT.LOCATION_ID LOCATION
, HR_GENERAL.DECODE_LOCATION(ASSGT.LOCATION_ID) LOCATION_NAME
, SUPERVISOR_ID SUPERVISOR
, PQH_GSP_UTILITY.GET_PERSON_NAME(SUPERVISOR_ID
, ELCT.ENRT_CVG_STRT_DT) MANAGER_NAME
, PERSON.EMPLOYEE_NUMBER EMP_NUMBER
, PERSON.DATE_OF_BIRTH DOB
, PERSON.START_DATE HIRE_DATE
, PERSON.ORIGINAL_DATE_OF_HIRE ORIG_HIRE_DATE
, PERSON.MARITAL_STATUS MARITAL_STATUS
, PERSON.EMAIL_ADDRESS EMAIL
, ASSGT.ASSIGNMENT_STATUS_TYPE_ID ASGT_STATUS_ID
, PQH_GSP_UTILITY.GET_ASSGT_STATUS(ASSGT.ASSIGNMENT_STATUS_TYPE_ID) ASGT_STATUS
, ASSGT.COLLECTIVE_AGREEMENT_ID COLLECTIVE_AGREEMENT_ID
, PQH_GSP_UTILITY.GET_CAGR_NAME(ASSGT.COLLECTIVE_AGREEMENT_ID) CAGR_NAME
, LER.TYP_CD LIFE_EVENT_TYPE
, ELCT.DFLT_FLAG DFLT_PROGRESSION
, PQH_GSP_UTILITY.GET_CUR_SAL(ASSGT.ASSIGNMENT_ID
, ELCT.ENRT_CVG_STRT_DT) CURRENT_SAL
, PQH_EMPLOYEE_SALARY.PGM_TO_ANNUAL1(PGM.ACTY_REF_PERD_CD
, PGM.PGM_UOM
, RATE.VAL
, ASSGT.ASSIGNMENT_ID
, ELCT.ENRT_CVG_STRT_DT) PROPOSED_SAL
, RATE.RT_STRT_DT SAL_CHG_DT
, ASSGT.ASSIGNMENT_ID ASSIGNMENT_ID
, ELCT.APPROVAL_STATUS_CD APPROVAL_STATUS_CD
, PIL.PER_IN_LER_ID PER_IN_LER_ID
, PIL.LF_EVT_OCRD_DT LIFE_EVENT_DT
, PERSON.LAST_NAME LAST_NAME
, PERSON.OFFICE_NUMBER OFFICE_NUMBER
, ELCT.IN_PNDG_WKFLOW_FLAG IN_PNDG_WKFLOW_FLAG
, ELCT.REQUEST_ID REQUEST_ID
, PQH_RANK_UTILITY.GET_TOTAL_SCORE (ELCT.PER_IN_LER_ID
, ELCT.PGM_ID
, ELCT.PL_ID ) TOTAL_SCORE
, PQH_RANK_UTILITY.GET_RANK ('S'
, ELCT.PER_IN_LER_ID
, ELCT.PGM_ID
, ELCT.PL_ID ) SYSTEM_RANK
, PQH_RANK_UTILITY.GET_RANK ('P'
, ELCT.PER_IN_LER_ID
, ELCT.PGM_ID
, ELCT.PL_ID ) PROPOSED_RANK
, PQH_RANK_UTILITY.GET_BEN_ACTION_ID (ELCT.PER_IN_LER_ID
, ELCT.PGM_ID
, ELCT.PL_ID ) BEN_ACTION_ID
, RATE.VAL RATE_VAL
, PQH_GSP_UTILITY.GET_CURRENT_RATE_VALUE(ASSGT.ASSIGNMENT_ID
, ELCT.ENRT_CVG_STRT_DT) CURRENT_RATE_VALUE
FROM BEN_ELIG_PER_ELCTBL_CHC ELCT
, BEN_PER_IN_LER PIL
, BEN_LER_F LER
, BEN_ENRT_RT RATE
, PER_PEOPLE_F PERSON
, PER_ALL_ASSIGNMENTS_F ASSGT
, BEN_PGM_F PGM
, BEN_PL_F PL
WHERE ELCT.PER_IN_LER_ID = PIL.PER_IN_LER_ID
AND ELCT.ENRT_CVG_STRT_DT IS NOT NULL
AND PIL.LER_ID = LER.LER_ID
AND NVL(ELCT.ENRT_CVG_STRT_DT
, SYSDATE) BETWEEN LER.EFFECTIVE_START_DATE
AND LER.EFFECTIVE_END_DATE
AND LER.TYP_CD = 'GSP'
AND PIL.PERSON_ID = PERSON.PERSON_ID
AND ELCT.BUSINESS_GROUP_ID = PERSON.BUSINESS_GROUP_ID
AND RATE.ELIG_PER_ELCTBL_CHC_ID(+) = ELCT.ELIG_PER_ELCTBL_CHC_ID
AND NVL(ELCT.ENRT_CVG_STRT_DT
, SYSDATE) BETWEEN PERSON.EFFECTIVE_START_DATE
AND PERSON.EFFECTIVE_END_DATE
AND ASSGT.PERSON_ID = PERSON.PERSON_ID
AND ASSGT.PRIMARY_FLAG = 'Y'
AND NVL(ELCT.ENRT_CVG_STRT_DT
, SYSDATE) BETWEEN ASSGT.EFFECTIVE_START_DATE
AND ASSGT.EFFECTIVE_END_DATE
AND ELCT.PGM_ID = PGM.PGM_ID
AND NVL(ELCT.ENRT_CVG_STRT_DT
, SYSDATE) BETWEEN PGM.EFFECTIVE_START_DATE
AND PGM.EFFECTIVE_END_DATE
AND ELCT.PL_ID = PL.PL_ID
AND NVL(ELCT.ENRT_CVG_STRT_DT
, SYSDATE) BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND PL.MAPPING_TABLE_NAME ='PER_GRADES'
AND ASSGT.ASSIGNMENT_TYPE = 'E'