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.HRFV_ACCRUAL_VALUES

Source

View Text - Preformatted


SELECT bgrt.name business_group_name ,peo.full_name person_name ,pap.accrual_plan_name accrual_plan_name ,decode( dummy.type, 1, per_views_pkg.per_get_net_accrual(ass.assignment_id, TRUNC(sysdate), pap.accrual_plan_id, null), per_views_pkg.per_get_accrual(ass.assignment_id, TRUNC(sysdate) ,pap.accrual_plan_id,null) ) accrual_value ,hr_bis.bis_decode_lookup('HOURS_OR_DAYS' ,pap.accrual_units_of_measure) accrual_units ,ass.assignment_number assignment_number ,decode( dummy.type, 1, hr_bis.bis_decode_lookup( 'NAME_TRANSLATIONS', 'PTO_NET_ENTITLEMENT' ), hr_bis.bis_decode_lookup( 'NAME_TRANSLATIONS', 'PTO_ACCRUED' ) ) accrual_component ,decode( dummy.type, 1, 3, 2 ) order_number ,pap.accrual_plan_id accrual_plan_id ,pee.assignment_id assignment_id ,ass.business_group_id business_group_id FROM (select 1 type from dual union select 2 type from dual ) dummy ,hr_all_organization_units_tl bgrt ,per_people_x peo ,per_assignments_x ass ,pay_element_entries_x pee ,pay_element_links_x pel ,pay_element_types_x pet ,pay_accrual_plans pap WHERE pap.accrual_plan_element_type_id = pet.element_type_id AND pet.element_type_id = pel.element_type_id AND pel.element_link_id = pee.element_link_id AND pee.assignment_id = ass.assignment_id AND ass.person_id = peo.person_id AND TRUNC(sysdate) >= (SELECT MIN(ptp2.start_date) FROM per_time_periods ptp2 WHERE ptp2.payroll_id = ass.payroll_id AND ptp2.start_date >= (SELECT MIN(pee1.effective_start_date) FROM pay_element_entries_f pee1 WHERE pee1.element_entry_id = pee.element_entry_id)) AND ass.business_group_id = bgrt.organization_id AND bgrt.language = userenv('LANG') AND ass.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,ass.business_group_id) UNION SELECT bgrt.name business_group_name ,peo.full_name person_name ,pap1.accrual_plan_name accrual_plan_name ,decode( pet1.element_name, pap1.accrual_plan_name || ' ' || hr_bis.decode_lang_lookup(pet1TL.source_lang, 'NAME_TRANSLATIONS', 'PTO_CO' ), per_views_pkg.per_get_element_accrual(ass.assignment_id, TRUNC(sysdate), piv1.input_value_id, pap1.accrual_plan_id, pap1.accrual_category ), per_views_pkg.per_get_element_accrual(ass.assignment_id, TRUNC(sysdate), piv1.input_value_id, pap1.accrual_plan_id, pap1.accrual_category ) * -1 ) accrual_value ,hr_bis.bis_decode_lookup('HOURS_OR_DAYS' ,pap1.accrual_units_of_measure) accrual_units ,ass.assignment_number assignment_number ,decode( pet1.element_name, pap1.accrual_plan_name || ' ' || hr_bis.decode_lang_lookup(pet1TL.source_lang, 'NAME_TRANSLATIONS', 'PTO_CO' ), hr_bis.bis_decode_lookup( 'NAME_TRANSLATIONS', 'PTO_CO' ), hr_bis.bis_decode_lookup( 'NAME_TRANSLATIONS', 'PTO_TAKEN' ) ) accrual_component ,1 order_number ,pap1.accrual_plan_id accrual_plan_id ,pee.assignment_id assignment_id ,ass.business_group_id business_group_id FROM hr_all_organization_units_tl bgrt ,per_people_x peo ,per_assignments_x ass ,pay_input_values_x piv1 ,pay_element_types_x pet1 ,pay_element_entries_x pee ,pay_net_calculation_rules ncr1 ,pay_element_links_x pel ,pay_element_types_x pet ,pay_accrual_plans pap1 ,pay_element_types_f_tl pet1TL WHERE pap1.accrual_plan_element_type_id = pet.element_type_id AND pet.element_type_id = pel.element_type_id AND pel.element_link_id = pee.element_link_id AND pee.assignment_id = ass.assignment_id AND ass.person_id = peo.person_id AND TRUNC(sysdate) >= (SELECT MIN(ptp2.start_date) FROM per_time_periods ptp2 WHERE ptp2.payroll_id = ass.payroll_id AND ptp2.start_date >= (SELECT MIN(pee1.effective_start_date) FROM pay_element_entries_f pee1 WHERE pee1.element_entry_id = pee.element_entry_id )) AND pet1.effective_start_date = (SELECT MAX(pet2.effective_start_date) FROM pay_element_types_f pet2 WHERE pet2.effective_start_date <= TRUNC(sysdate) AND pet2.element_type_id = pet1.element_type_id ) AND pet1.element_type_id = piv1.element_type_id AND pet1.element_type_id = pet1TL.element_type_id AND pet1TL.language = USERENV('LANG') AND ncr1.input_value_id = piv1.input_value_id AND pap1.accrual_plan_id = ncr1.accrual_plan_id AND ass.business_group_id = bgrt.organization_id AND bgrt.language = userenv('LANG') AND ass.business_group_id = NVL(hr_bis.get_sec_profile_bg_id, ass.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, PAP.ACCRUAL_PLAN_NAME ACCRUAL_PLAN_NAME
, DECODE( DUMMY.TYPE
, 1
, PER_VIEWS_PKG.PER_GET_NET_ACCRUAL(ASS.ASSIGNMENT_ID
, TRUNC(SYSDATE)
, PAP.ACCRUAL_PLAN_ID
, NULL)
, PER_VIEWS_PKG.PER_GET_ACCRUAL(ASS.ASSIGNMENT_ID
, TRUNC(SYSDATE)
, PAP.ACCRUAL_PLAN_ID
, NULL) ) ACCRUAL_VALUE
, HR_BIS.BIS_DECODE_LOOKUP('HOURS_OR_DAYS'
, PAP.ACCRUAL_UNITS_OF_MEASURE) ACCRUAL_UNITS
, ASS.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, DECODE( DUMMY.TYPE
, 1
, HR_BIS.BIS_DECODE_LOOKUP( 'NAME_TRANSLATIONS'
, 'PTO_NET_ENTITLEMENT' )
, HR_BIS.BIS_DECODE_LOOKUP( 'NAME_TRANSLATIONS'
, 'PTO_ACCRUED' ) ) ACCRUAL_COMPONENT
, DECODE( DUMMY.TYPE
, 1
, 3
, 2 ) ORDER_NUMBER
, PAP.ACCRUAL_PLAN_ID ACCRUAL_PLAN_ID
, PEE.ASSIGNMENT_ID ASSIGNMENT_ID
, ASS.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
FROM (SELECT 1 TYPE
FROM DUAL UNION SELECT 2 TYPE
FROM DUAL ) DUMMY
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_PEOPLE_X PEO
, PER_ASSIGNMENTS_X ASS
, PAY_ELEMENT_ENTRIES_X PEE
, PAY_ELEMENT_LINKS_X PEL
, PAY_ELEMENT_TYPES_X PET
, PAY_ACCRUAL_PLANS PAP
WHERE PAP.ACCRUAL_PLAN_ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PET.ELEMENT_TYPE_ID = PEL.ELEMENT_TYPE_ID
AND PEL.ELEMENT_LINK_ID = PEE.ELEMENT_LINK_ID
AND PEE.ASSIGNMENT_ID = ASS.ASSIGNMENT_ID
AND ASS.PERSON_ID = PEO.PERSON_ID
AND TRUNC(SYSDATE) >= (SELECT MIN(PTP2.START_DATE)
FROM PER_TIME_PERIODS PTP2
WHERE PTP2.PAYROLL_ID = ASS.PAYROLL_ID
AND PTP2.START_DATE >= (SELECT MIN(PEE1.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F PEE1
WHERE PEE1.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID))
AND ASS.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND ASS.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASS.BUSINESS_GROUP_ID) UNION SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, PAP1.ACCRUAL_PLAN_NAME ACCRUAL_PLAN_NAME
, DECODE( PET1.ELEMENT_NAME
, PAP1.ACCRUAL_PLAN_NAME || ' ' || HR_BIS.DECODE_LANG_LOOKUP(PET1TL.SOURCE_LANG
, 'NAME_TRANSLATIONS'
, 'PTO_CO' )
, PER_VIEWS_PKG.PER_GET_ELEMENT_ACCRUAL(ASS.ASSIGNMENT_ID
, TRUNC(SYSDATE)
, PIV1.INPUT_VALUE_ID
, PAP1.ACCRUAL_PLAN_ID
, PAP1.ACCRUAL_CATEGORY )
, PER_VIEWS_PKG.PER_GET_ELEMENT_ACCRUAL(ASS.ASSIGNMENT_ID
, TRUNC(SYSDATE)
, PIV1.INPUT_VALUE_ID
, PAP1.ACCRUAL_PLAN_ID
, PAP1.ACCRUAL_CATEGORY ) * -1 ) ACCRUAL_VALUE
, HR_BIS.BIS_DECODE_LOOKUP('HOURS_OR_DAYS'
, PAP1.ACCRUAL_UNITS_OF_MEASURE) ACCRUAL_UNITS
, ASS.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, DECODE( PET1.ELEMENT_NAME
, PAP1.ACCRUAL_PLAN_NAME || ' ' || HR_BIS.DECODE_LANG_LOOKUP(PET1TL.SOURCE_LANG
, 'NAME_TRANSLATIONS'
, 'PTO_CO' )
, HR_BIS.BIS_DECODE_LOOKUP( 'NAME_TRANSLATIONS'
, 'PTO_CO' )
, HR_BIS.BIS_DECODE_LOOKUP( 'NAME_TRANSLATIONS'
, 'PTO_TAKEN' ) ) ACCRUAL_COMPONENT
, 1 ORDER_NUMBER
, PAP1.ACCRUAL_PLAN_ID ACCRUAL_PLAN_ID
, PEE.ASSIGNMENT_ID ASSIGNMENT_ID
, ASS.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_PEOPLE_X PEO
, PER_ASSIGNMENTS_X ASS
, PAY_INPUT_VALUES_X PIV1
, PAY_ELEMENT_TYPES_X PET1
, PAY_ELEMENT_ENTRIES_X PEE
, PAY_NET_CALCULATION_RULES NCR1
, PAY_ELEMENT_LINKS_X PEL
, PAY_ELEMENT_TYPES_X PET
, PAY_ACCRUAL_PLANS PAP1
, PAY_ELEMENT_TYPES_F_TL PET1TL
WHERE PAP1.ACCRUAL_PLAN_ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PET.ELEMENT_TYPE_ID = PEL.ELEMENT_TYPE_ID
AND PEL.ELEMENT_LINK_ID = PEE.ELEMENT_LINK_ID
AND PEE.ASSIGNMENT_ID = ASS.ASSIGNMENT_ID
AND ASS.PERSON_ID = PEO.PERSON_ID
AND TRUNC(SYSDATE) >= (SELECT MIN(PTP2.START_DATE)
FROM PER_TIME_PERIODS PTP2
WHERE PTP2.PAYROLL_ID = ASS.PAYROLL_ID
AND PTP2.START_DATE >= (SELECT MIN(PEE1.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F PEE1
WHERE PEE1.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID ))
AND PET1.EFFECTIVE_START_DATE = (SELECT MAX(PET2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_TYPES_F PET2
WHERE PET2.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND PET2.ELEMENT_TYPE_ID = PET1.ELEMENT_TYPE_ID )
AND PET1.ELEMENT_TYPE_ID = PIV1.ELEMENT_TYPE_ID
AND PET1.ELEMENT_TYPE_ID = PET1TL.ELEMENT_TYPE_ID
AND PET1TL.LANGUAGE = USERENV('LANG')
AND NCR1.INPUT_VALUE_ID = PIV1.INPUT_VALUE_ID
AND PAP1.ACCRUAL_PLAN_ID = NCR1.ACCRUAL_PLAN_ID
AND ASS.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND ASS.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASS.BUSINESS_GROUP_ID) WITH READ ONLY