VIEW: APPS.BEN_CWB_REPT_STOCK_DETAIL_V

Source

View Text - Preformatted

SELECT nvl(prt.rt_strt_dt,ws_rt.rt_strt_dt) rt_strt_dt, max(pl.name) plan_name , max(ws_mgr_ppf.full_name) ws_mgr_full_name, max(emp_ppf.full_name) emp_full_name, max(emp_ppf.employee_number) empno, max(imm_mgr_ppf.full_name) imm_mgr_full_name, max(ws_mgr_ppf.first_name)||' '||max(ws_mgr_ppf.last_name)||' '||max(ws_mgr_ppf.suffix) ws_mgr_brief_name, max(emp_ppf.first_name)||' '||max(emp_ppf.last_name)||' '||max(emp_ppf.suffix) emp_brief_name , max(imm_mgr_ppf.first_name)||' '||max(imm_mgr_ppf.last_name)||' '||max(imm_mgr_ppf.suffix) imm_mgr_brief_name, max(emp_ppf.attribute1) person_attribute1, max(emp_ppf.attribute2) person_attribute2, max(emp_ppf.attribute3) person_attribute3, max(emp_ppf.attribute4) person_attribute4, max(emp_ppf.attribute5) person_attribute5, max(emp_ppf.attribute6) person_attribute6, max(emp_ppf.attribute7) person_attribute7, max(emp_ppf.attribute8) person_attribute8, max(emp_ppf.attribute9) person_attribute9, max(emp_ppf.attribute10) person_attribute10, sum(nvl(prt.rt_val,ws_rt.val)) ws_amount, max(nvl(nnmntry_lookup.meaning,pl.nip_pl_uom)) ws_amount_units, max(mgr_hrchy.lvl_num) levels_down, max(decode(emp_popl.comments,null,null,substr(emp_popl.comments,0,35))) comments, max(job_tl.name) job, max(job_def.segment1) job_segment1, max(job_def.segment2) job_segment2, max(job_def.segment3) job_segment3, max(job_def.segment4) job_segment4, max(job_def.segment5) job_segment5, max(grade_tl.name) grade, max(benutils.basis_to_plan_conversion (pl.pl_id,ws_rt.rt_strt_dt, (select ppp.proposed_salary_n from per_pay_proposals ppp where ppp.assignment_id = emp_popl.assignment_id and ppp.change_date = (select max(ppp2.change_date) from per_pay_proposals ppp2 where ppp2.assignment_id = emp_popl.assignment_id and ppp2.approved = 'Y' and ppp2.change_date < nvl(nvl(prt.rt_strt_dt,ws_rt.rt_strt_dt), sysdate))) ,asg.assignment_id)) base_salary, max(pl.nip_pl_uom) || ' ' || max(acty_ref_lookup.meaning) base_sal_curr_freq, max((select max(aei.aei_information1) from per_assignment_extra_info aei where aei.assignment_id = asg.assignment_id and aei.information_type = 'CWBRANK' and aei.aei_information2 = mgr_pil.person_id and nvl(aei.aei_information3,-1) = nvl(emp_chc.prtt_enrt_rslt_id,-1))) rank, max(emp_ppf.start_date) hire_date, max(org_tl.name) org_name, max(bg.legislation_code) country, max((Select max(pt.user_person_type) from per_person_types pt, per_person_type_usages_f ptu where pt.system_person_Type in ('EMP','EX_EMP','EMP_APL','EX_EMP_APL') and pt.person_type_id = ptu.person_type_id and ptu.person_id = emp_ppf.person_id and sysdate between ptu.effective_start_date and ptu.effective_end_date)) todays_employment_status, mgr_popl.enrt_perd_id, mgr_pil.person_id, mgr_hrchy.lvl_num from ben_pil_elctbl_chc_popl mgr_popl , ben_pil_elctbl_chc_popl emp_popl , ben_pil_elctbl_chc_popl imm_mgr_popl , ben_per_in_ler mgr_pil , ben_per_in_ler emp_pil , ben_per_in_ler imm_mgr_pil , ben_cwb_hrchy mgr_hrchy , ben_cwb_hrchy imm_mgr_hrchy , per_all_people_f ws_mgr_ppf , per_all_people_f emp_ppf , per_all_people_f imm_mgr_ppf , ben_elig_per_elctbl_chc emp_chc , ben_pl_f pl , ben_enrt_rt ws_rt , ben_prtt_rt_val prt , ben_acty_base_rt_f abr , per_all_assignments_f asg , per_business_groups_perf bg , per_jobs job , per_job_definitions job_def , per_grades grade , hr_organization_units org , hr_lookups nnmntry_lookup , hr_lookups acty_ref_lookup , per_jobs_tl job_tl , hr_all_organization_units_tl org_tl , per_grades_tl grade_tl where mgr_popl.per_in_ler_id = mgr_pil.per_in_ler_id and mgr_popl.pl_id = pl.pl_id and mgr_popl.pil_elctbl_chc_popl_id = mgr_hrchy.mgr_pil_elctbl_chc_popl_id and mgr_hrchy.lvl_num > 0 and mgr_hrchy.emp_pil_elctbl_chc_popl_id = emp_popl.pil_elctbl_chc_popl_id and emp_popl.ws_mgr_id = ws_mgr_ppf.person_id(+) and emp_popl.pil_elctbl_chc_popl_id = emp_chc.pil_elctbl_chc_popl_id and emp_chc.elig_per_elctbl_chc_id = ws_rt.elig_per_elctbl_chc_id and ws_rt.acty_typ_cd = 'CWBWS' and ws_rt.prtt_rt_val_id = prt.prtt_rt_val_id (+) and ws_rt.acty_base_rt_id = abr.acty_base_rt_id (+) and nnmntry_lookup.lookup_code (+) = abr.nnmntry_uom and nnmntry_lookup.lookup_type (+) = 'BEN_NNMNTRY_UOM' and emp_popl.per_in_ler_id = emp_pil.per_in_ler_id and emp_pil.per_in_ler_stat_cd in ('STRTD','PROCD') and emp_pil.person_id = emp_ppf.person_id and emp_popl.assignment_id = asg.assignment_id (+) and emp_chc.elig_flag = 'Y' and emp_chc.elctbl_flag = 'Y' and asg.job_id = job.job_id (+) and job.job_id = job_tl.job_id(+) and decode(job_tl.job_id, null, '1', job_tl.language) = decode(job_tl.job_id, null, '1', userenv('LANG')) and job.job_definition_id = job_def.job_definition_id (+) and asg.grade_id = grade.grade_id (+) and grade.grade_id = grade_tl.grade_id(+) and decode(grade_tl.grade_id,null,'1',grade_tl.language) = decode(grade_tl.grade_id,null,'1',userenv('LANG')) and asg.organization_id = org.organization_id (+) and org.organization_id = org_tl.organization_id(+) and decode(org_tl.organization_id, null,'1', org_tl.language) = decode(org_tl.organization_id, null, '1',userenv('LANG')) and emp_popl.pil_elctbl_chc_popl_id = imm_mgr_hrchy.emp_pil_elctbl_chc_popl_id and imm_mgr_hrchy.lvl_num = decode(mgr_hrchy.lvl_num , 1, 1, mgr_hrchy.lvl_num-1) and imm_mgr_hrchy.mgr_pil_elctbl_chc_popl_id = imm_mgr_popl.pil_elctbl_chc_popl_id and imm_mgr_popl.per_in_ler_id = imm_mgr_pil.per_in_ler_id and imm_mgr_pil.person_id = imm_mgr_ppf.person_id and acty_ref_lookup.lookup_code(+) = pl.nip_acty_ref_perd_cd and acty_ref_lookup.lookup_type(+) = 'BEN_ACTY_REF_PERD' and bg.business_group_id = emp_ppf.business_group_id and sysdate between pl.effective_start_date and pl.effective_end_date and sysdate between abr.effective_start_date and abr.effective_end_date and sysdate between asg.effective_start_date (+) and asg.effective_end_date(+) and sysdate between ws_mgr_ppf.effective_start_date (+) and ws_mgr_ppf.effective_end_date (+) and sysdate between emp_ppf.effective_start_date and emp_ppf.effective_end_date and sysdate between imm_mgr_ppf.effective_start_date and imm_mgr_ppf.effective_end_date group by emp_popl.pil_elctbl_chc_popl_id, pl.pl_id, prt.rt_strt_dt,ws_rt.rt_strt_dt , mgr_popl.enrt_perd_id, mgr_pil.person_id, mgr_hrchy.lvl_num order by rt_strt_dt desc , plan_name , emp_full_name

View Text - HTML Formatted

SELECT NVL(PRT.RT_STRT_DT
, WS_RT.RT_STRT_DT) RT_STRT_DT
, MAX(PL.NAME) PLAN_NAME
, MAX(WS_MGR_PPF.FULL_NAME) WS_MGR_FULL_NAME
, MAX(EMP_PPF.FULL_NAME) EMP_FULL_NAME
, MAX(EMP_PPF.EMPLOYEE_NUMBER) EMPNO
, MAX(IMM_MGR_PPF.FULL_NAME) IMM_MGR_FULL_NAME
, MAX(WS_MGR_PPF.FIRST_NAME)||' '||MAX(WS_MGR_PPF.LAST_NAME)||' '||MAX(WS_MGR_PPF.SUFFIX) WS_MGR_BRIEF_NAME
, MAX(EMP_PPF.FIRST_NAME)||' '||MAX(EMP_PPF.LAST_NAME)||' '||MAX(EMP_PPF.SUFFIX) EMP_BRIEF_NAME
, MAX(IMM_MGR_PPF.FIRST_NAME)||' '||MAX(IMM_MGR_PPF.LAST_NAME)||' '||MAX(IMM_MGR_PPF.SUFFIX) IMM_MGR_BRIEF_NAME
, MAX(EMP_PPF.ATTRIBUTE1) PERSON_ATTRIBUTE1
, MAX(EMP_PPF.ATTRIBUTE2) PERSON_ATTRIBUTE2
, MAX(EMP_PPF.ATTRIBUTE3) PERSON_ATTRIBUTE3
, MAX(EMP_PPF.ATTRIBUTE4) PERSON_ATTRIBUTE4
, MAX(EMP_PPF.ATTRIBUTE5) PERSON_ATTRIBUTE5
, MAX(EMP_PPF.ATTRIBUTE6) PERSON_ATTRIBUTE6
, MAX(EMP_PPF.ATTRIBUTE7) PERSON_ATTRIBUTE7
, MAX(EMP_PPF.ATTRIBUTE8) PERSON_ATTRIBUTE8
, MAX(EMP_PPF.ATTRIBUTE9) PERSON_ATTRIBUTE9
, MAX(EMP_PPF.ATTRIBUTE10) PERSON_ATTRIBUTE10
, SUM(NVL(PRT.RT_VAL
, WS_RT.VAL)) WS_AMOUNT
, MAX(NVL(NNMNTRY_LOOKUP.MEANING
, PL.NIP_PL_UOM)) WS_AMOUNT_UNITS
, MAX(MGR_HRCHY.LVL_NUM) LEVELS_DOWN
, MAX(DECODE(EMP_POPL.COMMENTS
, NULL
, NULL
, SUBSTR(EMP_POPL.COMMENTS
, 0
, 35))) COMMENTS
, MAX(JOB_TL.NAME) JOB
, MAX(JOB_DEF.SEGMENT1) JOB_SEGMENT1
, MAX(JOB_DEF.SEGMENT2) JOB_SEGMENT2
, MAX(JOB_DEF.SEGMENT3) JOB_SEGMENT3
, MAX(JOB_DEF.SEGMENT4) JOB_SEGMENT4
, MAX(JOB_DEF.SEGMENT5) JOB_SEGMENT5
, MAX(GRADE_TL.NAME) GRADE
, MAX(BENUTILS.BASIS_TO_PLAN_CONVERSION (PL.PL_ID
, WS_RT.RT_STRT_DT
, (SELECT PPP.PROPOSED_SALARY_N
FROM PER_PAY_PROPOSALS PPP
WHERE PPP.ASSIGNMENT_ID = EMP_POPL.ASSIGNMENT_ID
AND PPP.CHANGE_DATE = (SELECT MAX(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = EMP_POPL.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE < NVL(NVL(PRT.RT_STRT_DT
, WS_RT.RT_STRT_DT)
, SYSDATE)))
, ASG.ASSIGNMENT_ID)) BASE_SALARY
, MAX(PL.NIP_PL_UOM) || ' ' || MAX(ACTY_REF_LOOKUP.MEANING) BASE_SAL_CURR_FREQ
, MAX((SELECT MAX(AEI.AEI_INFORMATION1)
FROM PER_ASSIGNMENT_EXTRA_INFO AEI
WHERE AEI.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND AEI.INFORMATION_TYPE = 'CWBRANK'
AND AEI.AEI_INFORMATION2 = MGR_PIL.PERSON_ID
AND NVL(AEI.AEI_INFORMATION3
, -1) = NVL(EMP_CHC.PRTT_ENRT_RSLT_ID
, -1))) RANK
, MAX(EMP_PPF.START_DATE) HIRE_DATE
, MAX(ORG_TL.NAME) ORG_NAME
, MAX(BG.LEGISLATION_CODE) COUNTRY
, MAX((SELECT MAX(PT.USER_PERSON_TYPE)
FROM PER_PERSON_TYPES PT
, PER_PERSON_TYPE_USAGES_F PTU
WHERE PT.SYSTEM_PERSON_TYPE IN ('EMP'
, 'EX_EMP'
, 'EMP_APL'
, 'EX_EMP_APL')
AND PT.PERSON_TYPE_ID = PTU.PERSON_TYPE_ID
AND PTU.PERSON_ID = EMP_PPF.PERSON_ID
AND SYSDATE BETWEEN PTU.EFFECTIVE_START_DATE
AND PTU.EFFECTIVE_END_DATE)) TODAYS_EMPLOYMENT_STATUS
, MGR_POPL.ENRT_PERD_ID
, MGR_PIL.PERSON_ID
, MGR_HRCHY.LVL_NUM
FROM BEN_PIL_ELCTBL_CHC_POPL MGR_POPL
, BEN_PIL_ELCTBL_CHC_POPL EMP_POPL
, BEN_PIL_ELCTBL_CHC_POPL IMM_MGR_POPL
, BEN_PER_IN_LER MGR_PIL
, BEN_PER_IN_LER EMP_PIL
, BEN_PER_IN_LER IMM_MGR_PIL
, BEN_CWB_HRCHY MGR_HRCHY
, BEN_CWB_HRCHY IMM_MGR_HRCHY
, PER_ALL_PEOPLE_F WS_MGR_PPF
, PER_ALL_PEOPLE_F EMP_PPF
, PER_ALL_PEOPLE_F IMM_MGR_PPF
, BEN_ELIG_PER_ELCTBL_CHC EMP_CHC
, BEN_PL_F PL
, BEN_ENRT_RT WS_RT
, BEN_PRTT_RT_VAL PRT
, BEN_ACTY_BASE_RT_F ABR
, PER_ALL_ASSIGNMENTS_F ASG
, PER_BUSINESS_GROUPS_PERF BG
, PER_JOBS JOB
, PER_JOB_DEFINITIONS JOB_DEF
, PER_GRADES GRADE
, HR_ORGANIZATION_UNITS ORG
, HR_LOOKUPS NNMNTRY_LOOKUP
, HR_LOOKUPS ACTY_REF_LOOKUP
, PER_JOBS_TL JOB_TL
, HR_ALL_ORGANIZATION_UNITS_TL ORG_TL
, PER_GRADES_TL GRADE_TL
WHERE MGR_POPL.PER_IN_LER_ID = MGR_PIL.PER_IN_LER_ID
AND MGR_POPL.PL_ID = PL.PL_ID
AND MGR_POPL.PIL_ELCTBL_CHC_POPL_ID = MGR_HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND MGR_HRCHY.LVL_NUM > 0
AND MGR_HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID = EMP_POPL.PIL_ELCTBL_CHC_POPL_ID
AND EMP_POPL.WS_MGR_ID = WS_MGR_PPF.PERSON_ID(+)
AND EMP_POPL.PIL_ELCTBL_CHC_POPL_ID = EMP_CHC.PIL_ELCTBL_CHC_POPL_ID
AND EMP_CHC.ELIG_PER_ELCTBL_CHC_ID = WS_RT.ELIG_PER_ELCTBL_CHC_ID
AND WS_RT.ACTY_TYP_CD = 'CWBWS'
AND WS_RT.PRTT_RT_VAL_ID = PRT.PRTT_RT_VAL_ID (+)
AND WS_RT.ACTY_BASE_RT_ID = ABR.ACTY_BASE_RT_ID (+)
AND NNMNTRY_LOOKUP.LOOKUP_CODE (+) = ABR.NNMNTRY_UOM
AND NNMNTRY_LOOKUP.LOOKUP_TYPE (+) = 'BEN_NNMNTRY_UOM'
AND EMP_POPL.PER_IN_LER_ID = EMP_PIL.PER_IN_LER_ID
AND EMP_PIL.PER_IN_LER_STAT_CD IN ('STRTD'
, 'PROCD')
AND EMP_PIL.PERSON_ID = EMP_PPF.PERSON_ID
AND EMP_POPL.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID (+)
AND EMP_CHC.ELIG_FLAG = 'Y'
AND EMP_CHC.ELCTBL_FLAG = 'Y'
AND ASG.JOB_ID = JOB.JOB_ID (+)
AND JOB.JOB_ID = JOB_TL.JOB_ID(+)
AND DECODE(JOB_TL.JOB_ID
, NULL
, '1'
, JOB_TL.LANGUAGE) = DECODE(JOB_TL.JOB_ID
, NULL
, '1'
, USERENV('LANG'))
AND JOB.JOB_DEFINITION_ID = JOB_DEF.JOB_DEFINITION_ID (+)
AND ASG.GRADE_ID = GRADE.GRADE_ID (+)
AND GRADE.GRADE_ID = GRADE_TL.GRADE_ID(+)
AND DECODE(GRADE_TL.GRADE_ID
, NULL
, '1'
, GRADE_TL.LANGUAGE) = DECODE(GRADE_TL.GRADE_ID
, NULL
, '1'
, USERENV('LANG'))
AND ASG.ORGANIZATION_ID = ORG.ORGANIZATION_ID (+)
AND ORG.ORGANIZATION_ID = ORG_TL.ORGANIZATION_ID(+)
AND DECODE(ORG_TL.ORGANIZATION_ID
, NULL
, '1'
, ORG_TL.LANGUAGE) = DECODE(ORG_TL.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND EMP_POPL.PIL_ELCTBL_CHC_POPL_ID = IMM_MGR_HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND IMM_MGR_HRCHY.LVL_NUM = DECODE(MGR_HRCHY.LVL_NUM
, 1
, 1
, MGR_HRCHY.LVL_NUM-1)
AND IMM_MGR_HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID = IMM_MGR_POPL.PIL_ELCTBL_CHC_POPL_ID
AND IMM_MGR_POPL.PER_IN_LER_ID = IMM_MGR_PIL.PER_IN_LER_ID
AND IMM_MGR_PIL.PERSON_ID = IMM_MGR_PPF.PERSON_ID
AND ACTY_REF_LOOKUP.LOOKUP_CODE(+) = PL.NIP_ACTY_REF_PERD_CD
AND ACTY_REF_LOOKUP.LOOKUP_TYPE(+) = 'BEN_ACTY_REF_PERD'
AND BG.BUSINESS_GROUP_ID = EMP_PPF.BUSINESS_GROUP_ID
AND SYSDATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN ABR.EFFECTIVE_START_DATE
AND ABR.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN ASG.EFFECTIVE_START_DATE (+)
AND ASG.EFFECTIVE_END_DATE(+)
AND SYSDATE BETWEEN WS_MGR_PPF.EFFECTIVE_START_DATE (+)
AND WS_MGR_PPF.EFFECTIVE_END_DATE (+)
AND SYSDATE BETWEEN EMP_PPF.EFFECTIVE_START_DATE
AND EMP_PPF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN IMM_MGR_PPF.EFFECTIVE_START_DATE
AND IMM_MGR_PPF.EFFECTIVE_END_DATE GROUP BY EMP_POPL.PIL_ELCTBL_CHC_POPL_ID
, PL.PL_ID
, PRT.RT_STRT_DT
, WS_RT.RT_STRT_DT
, MGR_POPL.ENRT_PERD_ID
, MGR_PIL.PERSON_ID
, MGR_HRCHY.LVL_NUM ORDER BY RT_STRT_DT DESC
, PLAN_NAME
, EMP_FULL_NAME