eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.BEN_CWB_REP_COMP_DTL_V

Source

View Text - Preformatted


SELECT bcpiMgr.person_id MgrPersonId, bcgh.lvl_num LevelNum, decode(bpil.per_in_ler_stat_cd||to_char(bcpr.comp_posting_date), 'STRTD', dummy.mesg, to_char(nvl(bcpr.comp_posting_date,bcpr.lf_evt_ocrd_dt))) Status, nvl(bcpr.comp_posting_date,bcpr.lf_evt_ocrd_dt) TransactionDate, decode(dummy.profile, 'BN', bcpi.brief_name,'CN', bcpi.custom_name,bcpi.full_name) EmployeeName, bgTl.name BusinessGroup, bcpi.legislation_code Country, bcpdGrp.name CompPlan, bcpdGrp.pl_id CompPlanId, bcpdAct.name LocalPlan, bcpdAct.pl_id LocalPlanId, bcpdOpt.name ComponentName, bcpdOpt.oipl_id ComponentId, bcpr.ws_val CompAmt, decode(bcpdAbr.ws_nnmntry_uom,NULL,bcpr.currency,hr_general.decode_lookup ('BEN_NNMNTRY_UOM',bcpdAbr.ws_nnmntry_uom)) compuom, decode(bcpdAbr.ws_nnmntry_uom,NULL,(bcpr.ws_val / xchg.xchg_rate),to_number(NULL)) convertedamt, decode(bcpdAbr.ws_nnmntry_uom,NULL, bcpdgrp.pl_uom,NULL) conversionuom, round(DECODE (bcpdAbr.ws_nnmntry_uom, NULL, xchg.xchg_rate,to_number(NULL)),2) conversionexchgrate, bcpr.elig_sal_val EligibleSal, round((bcpr.ws_val/decode(bcpr.elig_sal_val,0,to_number(null),bcpr.elig_sal_val))*100,2) CompAmtPer, bcpi.base_salary BaseSal, initcap(bcpi.base_salary_frequency) BaseSalFreq, decode(bcpdAbr.ws_sub_acty_typ_cd,'ICM7', (bcpr.ws_val*bcpdAbr.pl_annulization_factor/bcpi.pay_annulization_factor)+bcpi.base_salary) NewSal, bcpr.misc1_val MiscRate1, bcpr.misc2_val MiscRate2, bcpr.misc3_val MiscRate3, bcpr.rec_val RecommendedVal, bcpr.stat_sal_val StatedSal, bcpr.tot_comp_val TotalComp, bcpr.oth_comp_val OtherComp, decode( bcpr.ws_mn_val, null, '', bcpr.ws_mn_val||' - '||bcpr.ws_mx_val) CompAmtLmts, decode( bcpr.rec_mn_val, null, '', bcpr.rec_mn_val||' - '||bcpr.rec_mx_val) RecRange, grdTl.name Grade, decode(bcpi.grd_min_val, null, '', bcpi.grd_min_val||' - '||bcpi.grd_max_val) GradeRange, bcpi.grd_mid_point GradeMidPoint, pjTl.name Job, pjd.segment1 JobSegment1, pjd.segment2 JobSegment2, pjd.segment3 JobSegment3, (SELECT TO_NUMBER (paei.aei_information1) FROM per_assignment_extra_info paei WHERE paei.assignment_id = bcpi.assignment_id AND paei.information_type = 'CWBRANK' AND paei.aei_information3 IS NULL AND paei.aei_information2 = bcpimgr.person_id ) ranking, (SELECT hlk.meaning FROM ben_transaction bt, hr_lookups hlk WHERE bt.transaction_id = bcpi.assignment_id AND bt.transaction_type = 'CWBPERF' || TO_CHAR (bcpdgrp.perf_revw_strt_dt, 'yyyy/mm/dd') || bcpdgrp.emp_interview_typ_cd AND hlk.lookup_type = 'PERFORMANCE_RATING' AND hlk.lookup_code = bt.attribute3 AND hlk.enabled_flag = 'Y' AND SYSDATE BETWEEN NVL (hlk.start_date_active, SYSDATE) AND NVL (hlk.end_date_active, SYSDATE) ) rating, bcpi.start_date HireDate, orgTl.name Organization, decode(dummy.profile, 'BN', bcpi.supervisor_brief_name,'CN', bcpi.supervisor_custom_name, bcpi.supervisor_full_name) WkshtMgr, bcpi.custom_segment1 CustomSeg1, bcpi.custom_segment2 CustomSeg2, bcpi.custom_segment3 CustomSeg3, bcpi.custom_segment4 CustomSeg4, bcpi.custom_segment5 CustomSeg5, bcpi.custom_segment6 CustomSeg6, bcpi.custom_segment7 CustomSeg7, bcpi.custom_segment8 CustomSeg8, bcpi.custom_segment9 CustomSeg9, bcpi.custom_segment10 CustomSeg10, bcpi.custom_segment11 CustomSeg11, bcpi.custom_segment12 CustomSeg12, bcpi.custom_segment13 CustomSeg13, bcpi.custom_segment14 CustomSeg14, bcpi.custom_segment15 CustomSeg15, bcpi.cpi_attribute1 CpiAttribute1, bcpi.cpi_attribute2 CpiAttribute2, bcpi.cpi_attribute3 CpiAttribute3, bcpi.cpi_attribute4 CpiAttribute4, bcpi.cpi_attribute5 CpiAttribute5, bcpi.full_name EmployeeFullName, bcpi.employee_number EmpNumber, bcpi.email_address Email, ben_cwb_utils.get_manager_name(bcpi.group_per_in_ler_id,1) Level1Mgr, ben_cwb_utils.get_manager_name(bcpi.group_per_in_ler_id,2) Level2Mgr, ben_cwb_utils.get_manager_name(bcpi.group_per_in_ler_id,3) Level3Mgr, ben_cwb_utils.get_manager_name(bcpi.group_per_in_ler_id,4) Level4Mgr, ben_cwb_utils.get_manager_name(bcpi.group_per_in_ler_id,5) Level5Mgr, ben_cwb_utils.get_manager_name(bcpi.group_per_in_ler_id,6) Level6Mgr, ben_cwb_utils.get_manager_name(bcpi.group_per_in_ler_id,7) Level7Mgr, ben_cwb_utils.get_manager_name(bcpi.group_per_in_ler_id,8) Level8Mgr, bcpi.custom_segment16 CustomSeg16, bcpi.custom_segment17 CustomSeg17, bcpi.custom_segment18 CustomSeg18, bcpi.custom_segment19 CustomSeg19, bcpi.custom_segment20 CustomSeg20, to_char(nvl(bcpdGrp.wthn_yr_start_dt, bcpdGrp.yr_perd_start_dt),'DD-Mon-YYYY') || ' - ' || to_char(nvl(bcpdGrp.wthn_yr_end_dt, bcpdGrp.yr_perd_end_dt),'DD-Mon-YYYY') PlanPeriod FROM ben_cwb_person_info bcpiMgr, ben_cwb_group_hrchy bcgh, ben_cwb_person_rates bcpr, ben_per_in_ler bpil, ben_cwb_person_info bcpi, ben_cwb_pl_dsgn bcpdAbr, ben_cwb_pl_dsgn bcpdGrp, ben_cwb_pl_dsgn bcpdAct, ben_cwb_pl_dsgn bcpdOpt, ben_cwb_xchg xchg, per_job_definitions pjd, per_jobs pj, per_jobs_tl pjTl, hr_all_organization_units_tl bgTl, hr_all_organization_units_tl orgTl, per_grades_tl grdTl, (SELECT ben_cwb_utils.get_profile('BEN_DISPLAY_EMPLOYEE_NAME') profile, fnd_message.get_string ('BEN', 'BEN_92789_TRANSACTION_PENDING') mesg FROM dual ) dummy WHERE bcpiMgr.group_per_in_ler_id = bcgh.mgr_per_in_ler_id AND bcpr.elig_flag = 'Y' AND bcpr.group_per_in_ler_id = bcgh.emp_per_in_ler_id AND xchg.group_pl_id = bcpr.group_pl_id AND xchg.lf_evt_ocrd_dt = bcpr.lf_evt_ocrd_dt AND xchg.currency = bcpr.currency AND bcpi.group_per_in_ler_id = bcgh.emp_per_in_ler_id AND bcpi.group_per_in_ler_id = bpil.per_in_ler_id AND nvl(bcpi.job_id,-1) = pj.job_id (+) AND pj.job_definition_id = pjd.job_definition_id (+) AND bcpi.job_id = pjTl.job_id (+) AND pjTl.language(+) = userenv('LANG') AND bcpi.job_id = pj.job_id(+) AND pj.job_definition_id = pjd.job_definition_id(+) AND bcpi.business_group_id = bgTl.organization_id (+) AND bgTl.language(+) = userenv('LANG') AND bcpi.organization_id = orgTl.organization_id (+) AND orgTl.language(+) = userenv('LANG') AND bcpi.grade_id = grdTl.grade_id (+) AND grdTl.language(+) = userenv('LANG') AND bcpdAbr.group_pl_id = bcpr.group_pl_id AND bcpdAbr.lf_evt_ocrd_dt = bcpr.lf_evt_ocrd_dt AND bcpdAbr.pl_id = bcpr.pl_id AND bcpdAbr.group_oipl_id = bcpr.group_oipl_id AND bcpdAbr.oipl_id = bcpr.oipl_id AND bcpdAbr.ws_abr_id IS NOT null AND bcpdgrp.pl_id = bcpr.group_pl_id AND bcpdgrp.lf_evt_ocrd_dt = bcpr.lf_evt_ocrd_dt AND bcpdgrp.oipl_id = -1 AND bcpdact.pl_id = bcpr.pl_id AND bcpdact.lf_evt_ocrd_dt = bcpr.lf_evt_ocrd_dt AND bcpdact.oipl_id = -1 AND bcpdopt.pl_id(+) = bcpr.group_pl_id AND bcpdopt.oipl_id(+) = bcpr.group_oipl_id AND bcpdopt.lf_evt_ocrd_dt(+) = bcpr.lf_evt_ocrd_dt AND bcpdopt.oipl_id(+) <> -1

View Text - HTML Formatted

SELECT BCPIMGR.PERSON_ID MGRPERSONID
, BCGH.LVL_NUM LEVELNUM
, DECODE(BPIL.PER_IN_LER_STAT_CD||TO_CHAR(BCPR.COMP_POSTING_DATE)
, 'STRTD'
, DUMMY.MESG
, TO_CHAR(NVL(BCPR.COMP_POSTING_DATE
, BCPR.LF_EVT_OCRD_DT))) STATUS
, NVL(BCPR.COMP_POSTING_DATE
, BCPR.LF_EVT_OCRD_DT) TRANSACTIONDATE
, DECODE(DUMMY.PROFILE
, 'BN'
, BCPI.BRIEF_NAME
, 'CN'
, BCPI.CUSTOM_NAME
, BCPI.FULL_NAME) EMPLOYEENAME
, BGTL.NAME BUSINESSGROUP
, BCPI.LEGISLATION_CODE COUNTRY
, BCPDGRP.NAME COMPPLAN
, BCPDGRP.PL_ID COMPPLANID
, BCPDACT.NAME LOCALPLAN
, BCPDACT.PL_ID LOCALPLANID
, BCPDOPT.NAME COMPONENTNAME
, BCPDOPT.OIPL_ID COMPONENTID
, BCPR.WS_VAL COMPAMT
, DECODE(BCPDABR.WS_NNMNTRY_UOM
, NULL
, BCPR.CURRENCY
, HR_GENERAL.DECODE_LOOKUP ('BEN_NNMNTRY_UOM'
, BCPDABR.WS_NNMNTRY_UOM)) COMPUOM
, DECODE(BCPDABR.WS_NNMNTRY_UOM
, NULL
, (BCPR.WS_VAL / XCHG.XCHG_RATE)
, TO_NUMBER(NULL)) CONVERTEDAMT
, DECODE(BCPDABR.WS_NNMNTRY_UOM
, NULL
, BCPDGRP.PL_UOM
, NULL) CONVERSIONUOM
, ROUND(DECODE (BCPDABR.WS_NNMNTRY_UOM
, NULL
, XCHG.XCHG_RATE
, TO_NUMBER(NULL))
, 2) CONVERSIONEXCHGRATE
, BCPR.ELIG_SAL_VAL ELIGIBLESAL
, ROUND((BCPR.WS_VAL/DECODE(BCPR.ELIG_SAL_VAL
, 0
, TO_NUMBER(NULL)
, BCPR.ELIG_SAL_VAL))*100
, 2) COMPAMTPER
, BCPI.BASE_SALARY BASESAL
, INITCAP(BCPI.BASE_SALARY_FREQUENCY) BASESALFREQ
, DECODE(BCPDABR.WS_SUB_ACTY_TYP_CD
, 'ICM7'
, (BCPR.WS_VAL*BCPDABR.PL_ANNULIZATION_FACTOR/BCPI.PAY_ANNULIZATION_FACTOR)+BCPI.BASE_SALARY) NEWSAL
, BCPR.MISC1_VAL MISCRATE1
, BCPR.MISC2_VAL MISCRATE2
, BCPR.MISC3_VAL MISCRATE3
, BCPR.REC_VAL RECOMMENDEDVAL
, BCPR.STAT_SAL_VAL STATEDSAL
, BCPR.TOT_COMP_VAL TOTALCOMP
, BCPR.OTH_COMP_VAL OTHERCOMP
, DECODE( BCPR.WS_MN_VAL
, NULL
, ''
, BCPR.WS_MN_VAL||' - '||BCPR.WS_MX_VAL) COMPAMTLMTS
, DECODE( BCPR.REC_MN_VAL
, NULL
, ''
, BCPR.REC_MN_VAL||' - '||BCPR.REC_MX_VAL) RECRANGE
, GRDTL.NAME GRADE
, DECODE(BCPI.GRD_MIN_VAL
, NULL
, ''
, BCPI.GRD_MIN_VAL||' - '||BCPI.GRD_MAX_VAL) GRADERANGE
, BCPI.GRD_MID_POINT GRADEMIDPOINT
, PJTL.NAME JOB
, PJD.SEGMENT1 JOBSEGMENT1
, PJD.SEGMENT2 JOBSEGMENT2
, PJD.SEGMENT3 JOBSEGMENT3
, (SELECT TO_NUMBER (PAEI.AEI_INFORMATION1)
FROM PER_ASSIGNMENT_EXTRA_INFO PAEI
WHERE PAEI.ASSIGNMENT_ID = BCPI.ASSIGNMENT_ID
AND PAEI.INFORMATION_TYPE = 'CWBRANK'
AND PAEI.AEI_INFORMATION3 IS NULL
AND PAEI.AEI_INFORMATION2 = BCPIMGR.PERSON_ID ) RANKING
, (SELECT HLK.MEANING
FROM BEN_TRANSACTION BT
, HR_LOOKUPS HLK
WHERE BT.TRANSACTION_ID = BCPI.ASSIGNMENT_ID
AND BT.TRANSACTION_TYPE = 'CWBPERF' || TO_CHAR (BCPDGRP.PERF_REVW_STRT_DT
, 'YYYY/MM/DD') || BCPDGRP.EMP_INTERVIEW_TYP_CD
AND HLK.LOOKUP_TYPE = 'PERFORMANCE_RATING'
AND HLK.LOOKUP_CODE = BT.ATTRIBUTE3
AND HLK.ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL (HLK.START_DATE_ACTIVE
, SYSDATE)
AND NVL (HLK.END_DATE_ACTIVE
, SYSDATE) ) RATING
, BCPI.START_DATE HIREDATE
, ORGTL.NAME ORGANIZATION
, DECODE(DUMMY.PROFILE
, 'BN'
, BCPI.SUPERVISOR_BRIEF_NAME
, 'CN'
, BCPI.SUPERVISOR_CUSTOM_NAME
, BCPI.SUPERVISOR_FULL_NAME) WKSHTMGR
, BCPI.CUSTOM_SEGMENT1 CUSTOMSEG1
, BCPI.CUSTOM_SEGMENT2 CUSTOMSEG2
, BCPI.CUSTOM_SEGMENT3 CUSTOMSEG3
, BCPI.CUSTOM_SEGMENT4 CUSTOMSEG4
, BCPI.CUSTOM_SEGMENT5 CUSTOMSEG5
, BCPI.CUSTOM_SEGMENT6 CUSTOMSEG6
, BCPI.CUSTOM_SEGMENT7 CUSTOMSEG7
, BCPI.CUSTOM_SEGMENT8 CUSTOMSEG8
, BCPI.CUSTOM_SEGMENT9 CUSTOMSEG9
, BCPI.CUSTOM_SEGMENT10 CUSTOMSEG10
, BCPI.CUSTOM_SEGMENT11 CUSTOMSEG11
, BCPI.CUSTOM_SEGMENT12 CUSTOMSEG12
, BCPI.CUSTOM_SEGMENT13 CUSTOMSEG13
, BCPI.CUSTOM_SEGMENT14 CUSTOMSEG14
, BCPI.CUSTOM_SEGMENT15 CUSTOMSEG15
, BCPI.CPI_ATTRIBUTE1 CPIATTRIBUTE1
, BCPI.CPI_ATTRIBUTE2 CPIATTRIBUTE2
, BCPI.CPI_ATTRIBUTE3 CPIATTRIBUTE3
, BCPI.CPI_ATTRIBUTE4 CPIATTRIBUTE4
, BCPI.CPI_ATTRIBUTE5 CPIATTRIBUTE5
, BCPI.FULL_NAME EMPLOYEEFULLNAME
, BCPI.EMPLOYEE_NUMBER EMPNUMBER
, BCPI.EMAIL_ADDRESS EMAIL
, BEN_CWB_UTILS.GET_MANAGER_NAME(BCPI.GROUP_PER_IN_LER_ID
, 1) LEVEL1MGR
, BEN_CWB_UTILS.GET_MANAGER_NAME(BCPI.GROUP_PER_IN_LER_ID
, 2) LEVEL2MGR
, BEN_CWB_UTILS.GET_MANAGER_NAME(BCPI.GROUP_PER_IN_LER_ID
, 3) LEVEL3MGR
, BEN_CWB_UTILS.GET_MANAGER_NAME(BCPI.GROUP_PER_IN_LER_ID
, 4) LEVEL4MGR
, BEN_CWB_UTILS.GET_MANAGER_NAME(BCPI.GROUP_PER_IN_LER_ID
, 5) LEVEL5MGR
, BEN_CWB_UTILS.GET_MANAGER_NAME(BCPI.GROUP_PER_IN_LER_ID
, 6) LEVEL6MGR
, BEN_CWB_UTILS.GET_MANAGER_NAME(BCPI.GROUP_PER_IN_LER_ID
, 7) LEVEL7MGR
, BEN_CWB_UTILS.GET_MANAGER_NAME(BCPI.GROUP_PER_IN_LER_ID
, 8) LEVEL8MGR
, BCPI.CUSTOM_SEGMENT16 CUSTOMSEG16
, BCPI.CUSTOM_SEGMENT17 CUSTOMSEG17
, BCPI.CUSTOM_SEGMENT18 CUSTOMSEG18
, BCPI.CUSTOM_SEGMENT19 CUSTOMSEG19
, BCPI.CUSTOM_SEGMENT20 CUSTOMSEG20
, TO_CHAR(NVL(BCPDGRP.WTHN_YR_START_DT
, BCPDGRP.YR_PERD_START_DT)
, 'DD-MON-YYYY') || ' - ' || TO_CHAR(NVL(BCPDGRP.WTHN_YR_END_DT
, BCPDGRP.YR_PERD_END_DT)
, 'DD-MON-YYYY') PLANPERIOD
FROM BEN_CWB_PERSON_INFO BCPIMGR
, BEN_CWB_GROUP_HRCHY BCGH
, BEN_CWB_PERSON_RATES BCPR
, BEN_PER_IN_LER BPIL
, BEN_CWB_PERSON_INFO BCPI
, BEN_CWB_PL_DSGN BCPDABR
, BEN_CWB_PL_DSGN BCPDGRP
, BEN_CWB_PL_DSGN BCPDACT
, BEN_CWB_PL_DSGN BCPDOPT
, BEN_CWB_XCHG XCHG
, PER_JOB_DEFINITIONS PJD
, PER_JOBS PJ
, PER_JOBS_TL PJTL
, HR_ALL_ORGANIZATION_UNITS_TL BGTL
, HR_ALL_ORGANIZATION_UNITS_TL ORGTL
, PER_GRADES_TL GRDTL
, (SELECT BEN_CWB_UTILS.GET_PROFILE('BEN_DISPLAY_EMPLOYEE_NAME') PROFILE
, FND_MESSAGE.GET_STRING ('BEN'
, 'BEN_92789_TRANSACTION_PENDING') MESG
FROM DUAL ) DUMMY
WHERE BCPIMGR.GROUP_PER_IN_LER_ID = BCGH.MGR_PER_IN_LER_ID
AND BCPR.ELIG_FLAG = 'Y'
AND BCPR.GROUP_PER_IN_LER_ID = BCGH.EMP_PER_IN_LER_ID
AND XCHG.GROUP_PL_ID = BCPR.GROUP_PL_ID
AND XCHG.LF_EVT_OCRD_DT = BCPR.LF_EVT_OCRD_DT
AND XCHG.CURRENCY = BCPR.CURRENCY
AND BCPI.GROUP_PER_IN_LER_ID = BCGH.EMP_PER_IN_LER_ID
AND BCPI.GROUP_PER_IN_LER_ID = BPIL.PER_IN_LER_ID
AND NVL(BCPI.JOB_ID
, -1) = PJ.JOB_ID (+)
AND PJ.JOB_DEFINITION_ID = PJD.JOB_DEFINITION_ID (+)
AND BCPI.JOB_ID = PJTL.JOB_ID (+)
AND PJTL.LANGUAGE(+) = USERENV('LANG')
AND BCPI.JOB_ID = PJ.JOB_ID(+)
AND PJ.JOB_DEFINITION_ID = PJD.JOB_DEFINITION_ID(+)
AND BCPI.BUSINESS_GROUP_ID = BGTL.ORGANIZATION_ID (+)
AND BGTL.LANGUAGE(+) = USERENV('LANG')
AND BCPI.ORGANIZATION_ID = ORGTL.ORGANIZATION_ID (+)
AND ORGTL.LANGUAGE(+) = USERENV('LANG')
AND BCPI.GRADE_ID = GRDTL.GRADE_ID (+)
AND GRDTL.LANGUAGE(+) = USERENV('LANG')
AND BCPDABR.GROUP_PL_ID = BCPR.GROUP_PL_ID
AND BCPDABR.LF_EVT_OCRD_DT = BCPR.LF_EVT_OCRD_DT
AND BCPDABR.PL_ID = BCPR.PL_ID
AND BCPDABR.GROUP_OIPL_ID = BCPR.GROUP_OIPL_ID
AND BCPDABR.OIPL_ID = BCPR.OIPL_ID
AND BCPDABR.WS_ABR_ID IS NOT NULL
AND BCPDGRP.PL_ID = BCPR.GROUP_PL_ID
AND BCPDGRP.LF_EVT_OCRD_DT = BCPR.LF_EVT_OCRD_DT
AND BCPDGRP.OIPL_ID = -1
AND BCPDACT.PL_ID = BCPR.PL_ID
AND BCPDACT.LF_EVT_OCRD_DT = BCPR.LF_EVT_OCRD_DT
AND BCPDACT.OIPL_ID = -1
AND BCPDOPT.PL_ID(+) = BCPR.GROUP_PL_ID
AND BCPDOPT.OIPL_ID(+) = BCPR.GROUP_OIPL_ID
AND BCPDOPT.LF_EVT_OCRD_DT(+) = BCPR.LF_EVT_OCRD_DT
AND BCPDOPT.OIPL_ID(+) <> -1