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_COMP_HIST_DWLD_V

Source

View Text - Preformatted


SELECT mgr_pil.person_id mgr_person_id,mgr_pil.per_in_ler_id mgr_per_in_ler_id, q.ass_id assignment_id, q.emp_name, q.group_per_in_ler_id, q.group_plan_id, q.lf_event_ocrd_dt, q.fte, q.flag year, q.stock1, q.stock2, substr(q.pay_rate,0,instr(q.pay_rate,'&',1,1)-1) pay_rate_currency, substr(q.pay_rate,instr(q.pay_rate,'&',1,1)+1,instr(q.pay_rate,'&',1,2)-instr(q.pay_rate,'&',1,1)-1) pay_basis, substr(q.pay_rate,instr(q.pay_rate,'&',1,2)+1,instr(q.pay_rate,'&',1,3)-instr(q.pay_rate,'&',1,2)-1) pay_rate_amount, substr(q.grptyp,0,instr(q.grptyp,'%',1,1)-1) bonus, substr(q.grptyp,instr(q.grptyp,'%',1,1)+1,instr(q.grptyp,'%',1,2)-instr(q.grptyp,'%',1,1)-1) other_comp, substr(q.grptyp,instr(q.grptyp,'%',1,2)+1,instr(q.grptyp,'%',1,3)-instr(q.grptyp,'%',1,2)-1) commission, substr(q.grptyp,instr(q.grptyp,'%',1,3)+1) overtime from ben_per_in_ler mgr_pil, ben_cwb_group_hrchy hrchy, (SELECT ass_id, full_name emp_name, group_per_in_ler_id, group_plan_id, lf_event_ocrd_dt, ben_cwb_person_info_pkg.get_fte_factor(ass_id, flag) fte, to_char(flag, 'YYYY') flag, decode('BSH','BSH',nvl(( SELECT nvl(sum(decode (inp.uom, 'I', to_number(eev.screen_entry_value, '999999999999999999.99999999'), 'N', to_number(eev.screen_entry_value, '999999999999999999.99999999'), 'M', to_number(eev.screen_entry_value, '999999999999999999.99999999'), decode(eevGP.element_entry_value_id, eev.element_entry_value_id,to_number(null), decode(to_number(nvl(eevGP.screen_entry_value,'0'), '999999999999999999.99999999'), 0,to_number(null), to_number(eev.screen_entry_value, '999999999999999999.99999999') * to_number(eevGP.screen_entry_value, '999999999999999999.99999999'))), decode(decode(to_number(nvl(eev.screen_entry_value,'0'), '999999999999999999.99999999'), 0,null, decode(eevGP.element_entry_value_id, eev.element_entry_value_id,null, decode(to_number(nvl(eevGP.screen_entry_value,'0'), '999999999999999999.99999999'), 0,null, 1))), null,to_number(null), round(to_number(eev.screen_entry_value, '999999999999999999.99999999')/ to_number(eevGP.screen_entry_value, '999999999999999999.99999999'),0)) )), 0) Stock1 FROM pay_element_entries_f ee, pay_element_entry_values_f eev, pay_element_types_f elt, pay_input_values_f inp, pay_element_entry_values_f eevGP, ben_acty_base_rt_f abr WHERE ee.assignment_id = ass_id AND eev.element_entry_id = ee.element_entry_id AND eev.screen_entry_value is not null AND decode(translate(eev.screen_entry_value, 'A0123456789.,','a'), null,translate(eev.screen_entry_value, '0123456789.,','0123456789'), 0) <> 0 AND inp.input_value_id = eev.input_value_id AND elt.element_type_id = inp.element_type_id AND abr.element_type_id = elt.element_type_id AND abr.input_value_id = eev.input_value_id AND abr.sub_acty_typ_cd = 'ICM4' AND abr.ACTY_BASE_RT_ID = ( SELECT max(abr2.ACTY_BASE_RT_ID) FROM ben_acty_base_rt_f abr2 WHERE abr2.element_type_id = elt.element_type_id AND abr2.input_value_id = eev.input_value_id AND abr2.sub_acty_typ_cd = 'ICM4' AND sysdate BETWEEN abr2.effective_start_date AND abr2.effective_end_date ) AND sysdate BETWEEN abr.effective_start_date AND abr.effective_end_date AND eevGP.element_entry_value_id = (SELECT nvl(max(eevGr.element_entry_value_id),eev.element_entry_value_id) FROM pay_element_entry_values_f eevGr, BEN_EXTRA_INPUT_VALUES inpGr, ben_acty_base_rt_f abrGr /* bind asgn id */ WHERE eevGr.element_entry_id = ee.element_entry_id AND eevGr.screen_entry_value is not null AND inpGr.input_value_id = eevGr.input_value_id AND inpGr.RETURN_VAR_NAME = 'GRANT_PRICE' AND abrGr.input_value_id = inpGr.input_value_id AND abrGr.sub_acty_typ_cd = 'ICM4' AND abrGr.element_type_id = elt.element_type_id AND sysdate BETWEEN abrGr.effective_start_date AND abrGr.effective_end_date AND ee.effective_start_date BETWEEN eevGr.effective_start_date AND eevGr.effective_end_date AND eevGr.effective_start_date = eev.effective_start_date ) AND eevGP.effective_start_date = eev.effective_start_date AND ee.effective_start_date BETWEEN elt.effective_start_date AND elt.effective_end_date AND ee.effective_start_date BETWEEN inp.effective_start_date AND inp.effective_end_date AND ee.effective_start_date BETWEEN eev.effective_start_date AND eev.effective_end_date AND elt.processing_type = 'N' AND (inp.uom = 'M' OR inp.uom = 'I' OR inp.uom = 'N') AND to_char(ee.effective_end_date, 'YYYY') = to_char(flag, 'YYYY') GROUP BY elt.input_currency_code ), 0), '0') Stock1, decode('ESH', 'ESH', (SELECT nvl(sum(grant_shares - cancelled_shares),0) stock FROM ben_cwb_stock_optn_dtls WHERE person_id = pid AND to_char(grant_date, 'YYYY') = to_char(flag, 'YYYY') ), '0') stock2 , nvl(( SELECT pet.input_currency_code ||'&'||initcap(bases.pay_basis) ||'&'||nvl(round(ppp.proposed_salary_n, 2), 0) ||'&'||nvl(decode(decode(convcurr, '1', pet.input_currency_code, convcurr), pet.input_currency_code, '1', (select nvl((select decode(pl_uom, convcurr, 1/decode(xchg_rate, 0, null, xchg_rate), xchg_rate) from ben_cwb_xchg xc where xc.group_pl_id = group_plan_id and xc.lf_evt_ocrd_dt = lf_event_ocrd_dt and currency = decode(pl_uom, convcurr, pet.input_currency_code, convcurr) ), ( select(max(gl1.conversion_rate)) from gl_daily_rates gl1 where exists (select null from gl_daily_rates where (to_currency = convcurr) and (from_currency = pet.input_currency_code)) and (gl1.conversion_date = ( select max(gl2.conversion_date) from gl_daily_rates gl2 where gl2.conversion_date <= sysdate and gl2.conversion_rate is not null and gl2.from_currency = pet.input_currency_code and gl2.to_currency = convcurr and gl2.conversion_type = 'Corporate' )) and gl1.from_currency = pet.input_currency_code and gl1.to_currency = convcurr and gl1.conversion_type = 'Corporate' )) from dual)), 1) ||'&'||bases.pay_annualization_factor FROM per_pay_proposals ppp, per_all_assignments_f asg, per_pay_bases bases, pay_element_types_f pet, pay_input_values_f piv WHERE asg.assignment_id = ass_id AND asg.assignment_id = ppp.assignment_id AND ppp.approved = 'Y' AND bases.pay_basis_id = asg.pay_basis_id AND bases.input_value_id = piv.input_value_id AND ppp.change_date = (select max(ppp3.change_date) from per_pay_proposals ppp3 where ppp3.assignment_id = ppp.assignment_id and ppp3.change_date <= to_date('31/12/'||to_char(flag, 'YYYY'), 'DD/MM/YYYY') and ppp3.approved = 'Y') AND ppp.change_date BETWEEN piv.effective_start_date AND piv.effective_end_date AND piv.element_type_id = pet.element_type_id AND ppp.change_date BETWEEN pet.effective_start_date AND pet.effective_end_date AND (piv.uom = 'M' or piv.uom = 'I' or piv.uom = 'N') AND ppp.change_date BETWEEN asg.effective_start_date AND asg.effective_end_date ), 0) pay_rate, nvl((select nvl(max(col1), '0')||'%'||nvl(max(col2), '0')||'%'||nvl(max(col3), '0')||'%'||nvl(max(col4), '0') from ( SELECT ee.assignment_id ass_id_1, decode(abr.sub_acty_typ_cd, 'ICM', sum(nvl((to_number(eev.screen_entry_value, '999999999999999999.99999999')), 0)) , '0') col1 ,decode(abr.sub_acty_typ_cd, 'ICM5', sum(nvl((to_number(eev.screen_entry_value, '999999999999999999.99999999')), 0)) , '0') col2 ,decode(abr.sub_acty_typ_cd, 'ICM8', sum(nvl((to_number(eev.screen_entry_value, '999999999999999999.99999999')), 0)) , '0') col3 ,decode(abr.sub_acty_typ_cd, 'ICM9', sum(nvl((to_number(eev.screen_entry_value, '999999999999999999.99999999')), 0)) , '0') col4 ,to_char(ee.effective_end_date, 'YYYY') ee_end_dt FROM pay_element_entries_f ee, pay_element_entry_values_f eev, pay_element_types_f elt, pay_input_values_f inp, ben_acty_base_rt_f abr WHERE eev.element_entry_id = ee.element_entry_id AND eev.screen_entry_value is not null AND inp.input_value_id = eev.input_value_id AND elt.element_type_id = inp.element_type_id AND ee.effective_start_date BETWEEN elt.effective_start_date AND elt.effective_end_date AND eev.effective_start_date BETWEEN inp.effective_start_date AND inp.effective_end_date AND ee.effective_start_date BETWEEN eev.effective_start_date AND eev.effective_end_date AND elt.processing_type = 'N' AND (inp.uom = 'M' or inp.uom = 'I' or inp.uom = 'N') AND abr.element_type_id = elt.element_type_id AND abr.input_value_id = eev.input_value_id AND abr.sub_acty_typ_cd in ( 'ICM8', 'ICM9', 'ICM5', 'ICM') AND sysdate between abr.effective_start_date and abr.effective_end_date AND abr.nnmntry_uom is null AND abr.ACTY_BASE_RT_ID = ( select max(abr2.ACTY_BASE_RT_ID) from ben_acty_base_rt_f abr2 where abr2.element_type_id = elt.element_type_id AND abr2.input_value_id = eev.input_value_id AND abr2.sub_acty_typ_cd = abr.sub_acty_typ_cd AND sysdate between abr2.effective_start_date and abr2.effective_end_date ) group by ee.assignment_id,abr.sub_acty_typ_cd, elt.input_currency_code, abr.nnmntry_uom, to_char(ee.effective_end_date, 'YYYY') ) where ee_end_dt = to_char(flag, 'YYYY') and ass_id = ass_id_1), '0'||'%'||'0'||'%'||'0'||'%'||'0') grpTyp FROM ( SELECT to_number(null) bgid, null selectedBasis, tab2.ass_id ass_id, tab2.full_name, tab2.group_per_in_ler_id, tab2.group_pl_id group_plan_id, tab2.lf_evt_ocrd_dt lf_event_ocrd_dt, 1 convcurr, tab2.person_id pid, null empnum, tab.flag flag, null pl_uom FROM (select (trunc(add_months(add_months(sysdate, 12), -12*to_number(decode(fnd_profile.value('BEN_CWB_EMP_COMP_HIST_YEARS') ,-1,(rownum), (rownum-1)))), 'YEAR')) - 1 flag , group_pl_id, lf_evt_ocrd_dt from ben_cwb_person_info WHERE rownum <= decode(fnd_profile.value('BEN_CWB_EMP_COMP_HIST_YEARS'), -1, 1, fnd_profile.value('BEN_CWB_EMP_COMP_HIST_YEARS')) ) tab , (SELECT assignment_id ass_id, full_name, group_per_in_ler_id, group_pl_id, lf_evt_ocrd_dt, person_id FROM ben_cwb_person_info bcpi )tab2 ) order by flag desc ) q WHERE hrchy.mgr_per_in_ler_id = mgr_pil.per_in_ler_id AND hrchy.lvl_num BETWEEN 1 AND 9999 AND q.group_plan_id = mgr_pil.group_pl_id AND q.lf_event_ocrd_dt = mgr_pil.lf_evt_ocrd_dt AND q.group_per_in_ler_id = hrchy.emp_per_in_ler_id AND mgr_pil.per_in_ler_stat_cd <> 'BCKDT' order by q.emp_name,q.flag

View Text - HTML Formatted

SELECT MGR_PIL.PERSON_ID MGR_PERSON_ID
, MGR_PIL.PER_IN_LER_ID MGR_PER_IN_LER_ID
, Q.ASS_ID ASSIGNMENT_ID
, Q.EMP_NAME
, Q.GROUP_PER_IN_LER_ID
, Q.GROUP_PLAN_ID
, Q.LF_EVENT_OCRD_DT
, Q.FTE
, Q.FLAG YEAR
, Q.STOCK1
, Q.STOCK2
, SUBSTR(Q.PAY_RATE
, 0
, INSTR(Q.PAY_RATE
, '&'
, 1
, 1)-1) PAY_RATE_CURRENCY
, SUBSTR(Q.PAY_RATE
, INSTR(Q.PAY_RATE
, '&'
, 1
, 1)+1
, INSTR(Q.PAY_RATE
, '&'
, 1
, 2)-INSTR(Q.PAY_RATE
, '&'
, 1
, 1)-1) PAY_BASIS
, SUBSTR(Q.PAY_RATE
, INSTR(Q.PAY_RATE
, '&'
, 1
, 2)+1
, INSTR(Q.PAY_RATE
, '&'
, 1
, 3)-INSTR(Q.PAY_RATE
, '&'
, 1
, 2)-1) PAY_RATE_AMOUNT
, SUBSTR(Q.GRPTYP
, 0
, INSTR(Q.GRPTYP
, '%'
, 1
, 1)-1) BONUS
, SUBSTR(Q.GRPTYP
, INSTR(Q.GRPTYP
, '%'
, 1
, 1)+1
, INSTR(Q.GRPTYP
, '%'
, 1
, 2)-INSTR(Q.GRPTYP
, '%'
, 1
, 1)-1) OTHER_COMP
, SUBSTR(Q.GRPTYP
, INSTR(Q.GRPTYP
, '%'
, 1
, 2)+1
, INSTR(Q.GRPTYP
, '%'
, 1
, 3)-INSTR(Q.GRPTYP
, '%'
, 1
, 2)-1) COMMISSION
, SUBSTR(Q.GRPTYP
, INSTR(Q.GRPTYP
, '%'
, 1
, 3)+1) OVERTIME
FROM BEN_PER_IN_LER MGR_PIL
, BEN_CWB_GROUP_HRCHY HRCHY
, (SELECT ASS_ID
, FULL_NAME EMP_NAME
, GROUP_PER_IN_LER_ID
, GROUP_PLAN_ID
, LF_EVENT_OCRD_DT
, BEN_CWB_PERSON_INFO_PKG.GET_FTE_FACTOR(ASS_ID
, FLAG) FTE
, TO_CHAR(FLAG
, 'YYYY') FLAG
, DECODE('BSH'
, 'BSH'
, NVL(( SELECT NVL(SUM(DECODE (INP.UOM
, 'I'
, TO_NUMBER(EEV.SCREEN_ENTRY_VALUE
, '999999999999999999.99999999')
, 'N'
, TO_NUMBER(EEV.SCREEN_ENTRY_VALUE
, '999999999999999999.99999999')
, 'M'
, TO_NUMBER(EEV.SCREEN_ENTRY_VALUE
, '999999999999999999.99999999')
, DECODE(EEVGP.ELEMENT_ENTRY_VALUE_ID
, EEV.ELEMENT_ENTRY_VALUE_ID
, TO_NUMBER(NULL)
, DECODE(TO_NUMBER(NVL(EEVGP.SCREEN_ENTRY_VALUE
, '0')
, '999999999999999999.99999999')
, 0
, TO_NUMBER(NULL)
, TO_NUMBER(EEV.SCREEN_ENTRY_VALUE
, '999999999999999999.99999999') * TO_NUMBER(EEVGP.SCREEN_ENTRY_VALUE
, '999999999999999999.99999999')))
, DECODE(DECODE(TO_NUMBER(NVL(EEV.SCREEN_ENTRY_VALUE
, '0')
, '999999999999999999.99999999')
, 0
, NULL
, DECODE(EEVGP.ELEMENT_ENTRY_VALUE_ID
, EEV.ELEMENT_ENTRY_VALUE_ID
, NULL
, DECODE(TO_NUMBER(NVL(EEVGP.SCREEN_ENTRY_VALUE
, '0')
, '999999999999999999.99999999')
, 0
, NULL
, 1)))
, NULL
, TO_NUMBER(NULL)
, ROUND(TO_NUMBER(EEV.SCREEN_ENTRY_VALUE
, '999999999999999999.99999999')/ TO_NUMBER(EEVGP.SCREEN_ENTRY_VALUE
, '999999999999999999.99999999')
, 0)) ))
, 0) STOCK1
FROM PAY_ELEMENT_ENTRIES_F EE
, PAY_ELEMENT_ENTRY_VALUES_F EEV
, PAY_ELEMENT_TYPES_F ELT
, PAY_INPUT_VALUES_F INP
, PAY_ELEMENT_ENTRY_VALUES_F EEVGP
, BEN_ACTY_BASE_RT_F ABR
WHERE EE.ASSIGNMENT_ID = ASS_ID
AND EEV.ELEMENT_ENTRY_ID = EE.ELEMENT_ENTRY_ID
AND EEV.SCREEN_ENTRY_VALUE IS NOT NULL
AND DECODE(TRANSLATE(EEV.SCREEN_ENTRY_VALUE
, 'A0123456789.
, '
, 'A')
, NULL
, TRANSLATE(EEV.SCREEN_ENTRY_VALUE
, '0123456789.
, '
, '0123456789')
, 0) <> 0
AND INP.INPUT_VALUE_ID = EEV.INPUT_VALUE_ID
AND ELT.ELEMENT_TYPE_ID = INP.ELEMENT_TYPE_ID
AND ABR.ELEMENT_TYPE_ID = ELT.ELEMENT_TYPE_ID
AND ABR.INPUT_VALUE_ID = EEV.INPUT_VALUE_ID
AND ABR.SUB_ACTY_TYP_CD = 'ICM4'
AND ABR.ACTY_BASE_RT_ID = ( SELECT MAX(ABR2.ACTY_BASE_RT_ID)
FROM BEN_ACTY_BASE_RT_F ABR2
WHERE ABR2.ELEMENT_TYPE_ID = ELT.ELEMENT_TYPE_ID
AND ABR2.INPUT_VALUE_ID = EEV.INPUT_VALUE_ID
AND ABR2.SUB_ACTY_TYP_CD = 'ICM4'
AND SYSDATE BETWEEN ABR2.EFFECTIVE_START_DATE
AND ABR2.EFFECTIVE_END_DATE )
AND SYSDATE BETWEEN ABR.EFFECTIVE_START_DATE
AND ABR.EFFECTIVE_END_DATE
AND EEVGP.ELEMENT_ENTRY_VALUE_ID = (SELECT NVL(MAX(EEVGR.ELEMENT_ENTRY_VALUE_ID)
, EEV.ELEMENT_ENTRY_VALUE_ID)
FROM PAY_ELEMENT_ENTRY_VALUES_F EEVGR
, BEN_EXTRA_INPUT_VALUES INPGR
, BEN_ACTY_BASE_RT_F ABRGR /* BIND ASGN ID */
WHERE EEVGR.ELEMENT_ENTRY_ID = EE.ELEMENT_ENTRY_ID
AND EEVGR.SCREEN_ENTRY_VALUE IS NOT NULL
AND INPGR.INPUT_VALUE_ID = EEVGR.INPUT_VALUE_ID
AND INPGR.RETURN_VAR_NAME = 'GRANT_PRICE'
AND ABRGR.INPUT_VALUE_ID = INPGR.INPUT_VALUE_ID
AND ABRGR.SUB_ACTY_TYP_CD = 'ICM4'
AND ABRGR.ELEMENT_TYPE_ID = ELT.ELEMENT_TYPE_ID
AND SYSDATE BETWEEN ABRGR.EFFECTIVE_START_DATE
AND ABRGR.EFFECTIVE_END_DATE
AND EE.EFFECTIVE_START_DATE BETWEEN EEVGR.EFFECTIVE_START_DATE
AND EEVGR.EFFECTIVE_END_DATE
AND EEVGR.EFFECTIVE_START_DATE = EEV.EFFECTIVE_START_DATE )
AND EEVGP.EFFECTIVE_START_DATE = EEV.EFFECTIVE_START_DATE
AND EE.EFFECTIVE_START_DATE BETWEEN ELT.EFFECTIVE_START_DATE
AND ELT.EFFECTIVE_END_DATE
AND EE.EFFECTIVE_START_DATE BETWEEN INP.EFFECTIVE_START_DATE
AND INP.EFFECTIVE_END_DATE
AND EE.EFFECTIVE_START_DATE BETWEEN EEV.EFFECTIVE_START_DATE
AND EEV.EFFECTIVE_END_DATE
AND ELT.PROCESSING_TYPE = 'N'
AND (INP.UOM = 'M' OR INP.UOM = 'I' OR INP.UOM = 'N')
AND TO_CHAR(EE.EFFECTIVE_END_DATE
, 'YYYY') = TO_CHAR(FLAG
, 'YYYY') GROUP BY ELT.INPUT_CURRENCY_CODE )
, 0)
, '0') STOCK1
, DECODE('ESH'
, 'ESH'
, (SELECT NVL(SUM(GRANT_SHARES - CANCELLED_SHARES)
, 0) STOCK
FROM BEN_CWB_STOCK_OPTN_DTLS
WHERE PERSON_ID = PID
AND TO_CHAR(GRANT_DATE
, 'YYYY') = TO_CHAR(FLAG
, 'YYYY') )
, '0') STOCK2
, NVL(( SELECT PET.INPUT_CURRENCY_CODE ||'&'||INITCAP(BASES.PAY_BASIS) ||'&'||NVL(ROUND(PPP.PROPOSED_SALARY_N
, 2)
, 0) ||'&'||NVL(DECODE(DECODE(CONVCURR
, '1'
, PET.INPUT_CURRENCY_CODE
, CONVCURR)
, PET.INPUT_CURRENCY_CODE
, '1'
, (SELECT NVL((SELECT DECODE(PL_UOM
, CONVCURR
, 1/DECODE(XCHG_RATE
, 0
, NULL
, XCHG_RATE)
, XCHG_RATE)
FROM BEN_CWB_XCHG XC
WHERE XC.GROUP_PL_ID = GROUP_PLAN_ID
AND XC.LF_EVT_OCRD_DT = LF_EVENT_OCRD_DT
AND CURRENCY = DECODE(PL_UOM
, CONVCURR
, PET.INPUT_CURRENCY_CODE
, CONVCURR) )
, ( SELECT(MAX(GL1.CONVERSION_RATE))
FROM GL_DAILY_RATES GL1
WHERE EXISTS (SELECT NULL
FROM GL_DAILY_RATES
WHERE (TO_CURRENCY = CONVCURR)
AND (FROM_CURRENCY = PET.INPUT_CURRENCY_CODE))
AND (GL1.CONVERSION_DATE = ( SELECT MAX(GL2.CONVERSION_DATE)
FROM GL_DAILY_RATES GL2
WHERE GL2.CONVERSION_DATE <= SYSDATE
AND GL2.CONVERSION_RATE IS NOT NULL
AND GL2.FROM_CURRENCY = PET.INPUT_CURRENCY_CODE
AND GL2.TO_CURRENCY = CONVCURR
AND GL2.CONVERSION_TYPE = 'CORPORATE' ))
AND GL1.FROM_CURRENCY = PET.INPUT_CURRENCY_CODE
AND GL1.TO_CURRENCY = CONVCURR
AND GL1.CONVERSION_TYPE = 'CORPORATE' ))
FROM DUAL))
, 1) ||'&'||BASES.PAY_ANNUALIZATION_FACTOR
FROM PER_PAY_PROPOSALS PPP
, PER_ALL_ASSIGNMENTS_F ASG
, PER_PAY_BASES BASES
, PAY_ELEMENT_TYPES_F PET
, PAY_INPUT_VALUES_F PIV
WHERE ASG.ASSIGNMENT_ID = ASS_ID
AND ASG.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP.APPROVED = 'Y'
AND BASES.PAY_BASIS_ID = ASG.PAY_BASIS_ID
AND BASES.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PPP.CHANGE_DATE = (SELECT MAX(PPP3.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP3
WHERE PPP3.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP3.CHANGE_DATE <= TO_DATE('31/12/'||TO_CHAR(FLAG
, 'YYYY')
, 'DD/MM/YYYY')
AND PPP3.APPROVED = 'Y')
AND PPP.CHANGE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PPP.CHANGE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND (PIV.UOM = 'M' OR PIV.UOM = 'I' OR PIV.UOM = 'N')
AND PPP.CHANGE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE )
, 0) PAY_RATE
, NVL((SELECT NVL(MAX(COL1)
, '0')||'%'||NVL(MAX(COL2)
, '0')||'%'||NVL(MAX(COL3)
, '0')||'%'||NVL(MAX(COL4)
, '0')
FROM ( SELECT EE.ASSIGNMENT_ID ASS_ID_1
, DECODE(ABR.SUB_ACTY_TYP_CD
, 'ICM'
, SUM(NVL((TO_NUMBER(EEV.SCREEN_ENTRY_VALUE
, '999999999999999999.99999999'))
, 0))
, '0') COL1
, DECODE(ABR.SUB_ACTY_TYP_CD
, 'ICM5'
, SUM(NVL((TO_NUMBER(EEV.SCREEN_ENTRY_VALUE
, '999999999999999999.99999999'))
, 0))
, '0') COL2
, DECODE(ABR.SUB_ACTY_TYP_CD
, 'ICM8'
, SUM(NVL((TO_NUMBER(EEV.SCREEN_ENTRY_VALUE
, '999999999999999999.99999999'))
, 0))
, '0') COL3
, DECODE(ABR.SUB_ACTY_TYP_CD
, 'ICM9'
, SUM(NVL((TO_NUMBER(EEV.SCREEN_ENTRY_VALUE
, '999999999999999999.99999999'))
, 0))
, '0') COL4
, TO_CHAR(EE.EFFECTIVE_END_DATE
, 'YYYY') EE_END_DT
FROM PAY_ELEMENT_ENTRIES_F EE
, PAY_ELEMENT_ENTRY_VALUES_F EEV
, PAY_ELEMENT_TYPES_F ELT
, PAY_INPUT_VALUES_F INP
, BEN_ACTY_BASE_RT_F ABR
WHERE EEV.ELEMENT_ENTRY_ID = EE.ELEMENT_ENTRY_ID
AND EEV.SCREEN_ENTRY_VALUE IS NOT NULL
AND INP.INPUT_VALUE_ID = EEV.INPUT_VALUE_ID
AND ELT.ELEMENT_TYPE_ID = INP.ELEMENT_TYPE_ID
AND EE.EFFECTIVE_START_DATE BETWEEN ELT.EFFECTIVE_START_DATE
AND ELT.EFFECTIVE_END_DATE
AND EEV.EFFECTIVE_START_DATE BETWEEN INP.EFFECTIVE_START_DATE
AND INP.EFFECTIVE_END_DATE
AND EE.EFFECTIVE_START_DATE BETWEEN EEV.EFFECTIVE_START_DATE
AND EEV.EFFECTIVE_END_DATE
AND ELT.PROCESSING_TYPE = 'N'
AND (INP.UOM = 'M' OR INP.UOM = 'I' OR INP.UOM = 'N')
AND ABR.ELEMENT_TYPE_ID = ELT.ELEMENT_TYPE_ID
AND ABR.INPUT_VALUE_ID = EEV.INPUT_VALUE_ID
AND ABR.SUB_ACTY_TYP_CD IN ( 'ICM8'
, 'ICM9'
, 'ICM5'
, 'ICM')
AND SYSDATE BETWEEN ABR.EFFECTIVE_START_DATE
AND ABR.EFFECTIVE_END_DATE
AND ABR.NNMNTRY_UOM IS NULL
AND ABR.ACTY_BASE_RT_ID = ( SELECT MAX(ABR2.ACTY_BASE_RT_ID)
FROM BEN_ACTY_BASE_RT_F ABR2
WHERE ABR2.ELEMENT_TYPE_ID = ELT.ELEMENT_TYPE_ID
AND ABR2.INPUT_VALUE_ID = EEV.INPUT_VALUE_ID
AND ABR2.SUB_ACTY_TYP_CD = ABR.SUB_ACTY_TYP_CD
AND SYSDATE BETWEEN ABR2.EFFECTIVE_START_DATE
AND ABR2.EFFECTIVE_END_DATE ) GROUP BY EE.ASSIGNMENT_ID
, ABR.SUB_ACTY_TYP_CD
, ELT.INPUT_CURRENCY_CODE
, ABR.NNMNTRY_UOM
, TO_CHAR(EE.EFFECTIVE_END_DATE
, 'YYYY') )
WHERE EE_END_DT = TO_CHAR(FLAG
, 'YYYY')
AND ASS_ID = ASS_ID_1)
, '0'||'%'||'0'||'%'||'0'||'%'||'0') GRPTYP
FROM ( SELECT TO_NUMBER(NULL) BGID
, NULL SELECTEDBASIS
, TAB2.ASS_ID ASS_ID
, TAB2.FULL_NAME
, TAB2.GROUP_PER_IN_LER_ID
, TAB2.GROUP_PL_ID GROUP_PLAN_ID
, TAB2.LF_EVT_OCRD_DT LF_EVENT_OCRD_DT
, 1 CONVCURR
, TAB2.PERSON_ID PID
, NULL EMPNUM
, TAB.FLAG FLAG
, NULL PL_UOM
FROM (SELECT (TRUNC(ADD_MONTHS(ADD_MONTHS(SYSDATE
, 12)
, -12*TO_NUMBER(DECODE(FND_PROFILE.VALUE('BEN_CWB_EMP_COMP_HIST_YEARS')
, -1
, (ROWNUM)
, (ROWNUM-1))))
, 'YEAR')) - 1 FLAG
, GROUP_PL_ID
, LF_EVT_OCRD_DT
FROM BEN_CWB_PERSON_INFO
WHERE ROWNUM <= DECODE(FND_PROFILE.VALUE('BEN_CWB_EMP_COMP_HIST_YEARS')
, -1
, 1
, FND_PROFILE.VALUE('BEN_CWB_EMP_COMP_HIST_YEARS')) ) TAB
, (SELECT ASSIGNMENT_ID ASS_ID
, FULL_NAME
, GROUP_PER_IN_LER_ID
, GROUP_PL_ID
, LF_EVT_OCRD_DT
, PERSON_ID
FROM BEN_CWB_PERSON_INFO BCPI )TAB2 ) ORDER BY FLAG DESC ) Q
WHERE HRCHY.MGR_PER_IN_LER_ID = MGR_PIL.PER_IN_LER_ID
AND HRCHY.LVL_NUM BETWEEN 1
AND 9999
AND Q.GROUP_PLAN_ID = MGR_PIL.GROUP_PL_ID
AND Q.LF_EVENT_OCRD_DT = MGR_PIL.LF_EVT_OCRD_DT
AND Q.GROUP_PER_IN_LER_ID = HRCHY.EMP_PER_IN_LER_ID
AND MGR_PIL.PER_IN_LER_STAT_CD <> 'BCKDT' ORDER BY Q.EMP_NAME
, Q.FLAG