VIEW: APPS.PAYFV_ELEMENT_ENTRIES

Source

View Text - Preformatted

SELECT bgrT.name business_group_name ,peo.full_name person_name ,peo.order_name order_name ,petT.element_name element ,orgT.name organization_name ,DECODE(asg.job_id,NULL,NULL,pay_payfv_element_entries_pkg.get_job(asg.job_id)) job_name ,DECODE(asg.position_id,NULL,NULL,pay_payfv_element_entries_pkg.get_position(asg.position_id)) position_name ,DECODE(asg.grade_id,NULL,NULL,pay_payfv_element_entries_pkg.get_grade(asg.grade_id)) grade_name ,DECODE(asg.location_id,NULL,NULL,pay_payfv_element_entries_pkg.get_location(asg.location_id)) location_name ,DECODE(asg.pay_basis_id,NULL,NULL,pay_payfv_element_entries_pkg.get_pay_basis(asg.pay_basis_id)) pay_basis ,DECODE(asg.payroll_id,NULL,NULL,pay_payfv_element_entries_pkg.get_payroll(asg.payroll_id)) payroll_name ,hr_general.decode_lookup('ENTRY_TYPE',pee.entry_type) entry_type ,pivT.name input_value ,hr_general.decode_lookup('UNITS',piv.uom) unit_of_measure ,eev.screen_entry_value entry_value ,pee.effective_start_date ,pee.effective_end_date /* Ids */ ,asg.assignment_id ,asg.business_group_id ,asg.person_id ,pee.element_entry_id ,eev.element_entry_value_id ,pel.element_link_id ,pet.element_type_id ,asg.organization_id ,asg.job_id ,asg.position_id ,asg.grade_id ,asg.location_id ,asg.pay_basis_id ,asg.payroll_id ,eev.input_value_id ,pee.creation_date ,create_usr.user_name ,pee.last_update_date ,update_usr.user_name FROM pay_element_entries_f pee ,pay_input_values_f piv ,pay_input_values_f_tl pivT ,pay_element_entry_values_f eev ,pay_element_links_f pel ,pay_element_types_f pet ,pay_element_types_f_tl petT ,per_assignments_f asg ,per_people_f peo ,hr_all_organization_units_tl orgT ,hr_all_organization_units_tl bgrT ,fnd_user create_usr ,fnd_user update_usr WHERE pet.element_type_id = pel.element_type_id AND pet.element_type_id = petT.element_type_id AND petT.language = USERENV('LANG') AND piv.input_value_id = pivT.input_value_id AND pivT.language = USERENV('LANG') AND piv.input_value_id = eev.input_value_id AND eev.element_entry_id = pee.element_entry_id AND pee.element_link_id = pel.element_link_id AND pee.assignment_id = asg.assignment_id AND asg.person_id = peo.person_id AND asg.organization_id = orgT.organization_id AND orgT.language = USERENV('LANG') AND asg.business_group_id+0 = bgrT.organization_id AND bgrT.language = USERENV('LANG') AND pee.last_updated_by = update_usr.user_id(+) AND pee.created_by = create_usr.user_id(+) AND trunc(SYSDATE) BETWEEN pee.effective_start_date and pee.effective_end_date AND pee.effective_start_date BETWEEN pet.effective_start_date AND pet.effective_end_date AND pee.effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date AND pee.effective_start_date BETWEEN eev.effective_start_date AND eev.effective_end_date AND pee.effective_start_date BETWEEN pel.effective_start_date AND pel.effective_end_date AND pee.effective_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date AND pee.effective_start_date BETWEEN peo.effective_start_date AND peo.effective_end_date AND asg.business_group_id +0 = nvl(hr_general.get_business_group_id, asg.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, PEO.ORDER_NAME ORDER_NAME
, PETT.ELEMENT_NAME ELEMENT
, ORGT.NAME ORGANIZATION_NAME
, DECODE(ASG.JOB_ID
, NULL
, NULL
, PAY_PAYFV_ELEMENT_ENTRIES_PKG.GET_JOB(ASG.JOB_ID)) JOB_NAME
, DECODE(ASG.POSITION_ID
, NULL
, NULL
, PAY_PAYFV_ELEMENT_ENTRIES_PKG.GET_POSITION(ASG.POSITION_ID)) POSITION_NAME
, DECODE(ASG.GRADE_ID
, NULL
, NULL
, PAY_PAYFV_ELEMENT_ENTRIES_PKG.GET_GRADE(ASG.GRADE_ID)) GRADE_NAME
, DECODE(ASG.LOCATION_ID
, NULL
, NULL
, PAY_PAYFV_ELEMENT_ENTRIES_PKG.GET_LOCATION(ASG.LOCATION_ID)) LOCATION_NAME
, DECODE(ASG.PAY_BASIS_ID
, NULL
, NULL
, PAY_PAYFV_ELEMENT_ENTRIES_PKG.GET_PAY_BASIS(ASG.PAY_BASIS_ID)) PAY_BASIS
, DECODE(ASG.PAYROLL_ID
, NULL
, NULL
, PAY_PAYFV_ELEMENT_ENTRIES_PKG.GET_PAYROLL(ASG.PAYROLL_ID)) PAYROLL_NAME
, HR_GENERAL.DECODE_LOOKUP('ENTRY_TYPE'
, PEE.ENTRY_TYPE) ENTRY_TYPE
, PIVT.NAME INPUT_VALUE
, HR_GENERAL.DECODE_LOOKUP('UNITS'
, PIV.UOM) UNIT_OF_MEASURE
, EEV.SCREEN_ENTRY_VALUE ENTRY_VALUE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE /* IDS */
, ASG.ASSIGNMENT_ID
, ASG.BUSINESS_GROUP_ID
, ASG.PERSON_ID
, PEE.ELEMENT_ENTRY_ID
, EEV.ELEMENT_ENTRY_VALUE_ID
, PEL.ELEMENT_LINK_ID
, PET.ELEMENT_TYPE_ID
, ASG.ORGANIZATION_ID
, ASG.JOB_ID
, ASG.POSITION_ID
, ASG.GRADE_ID
, ASG.LOCATION_ID
, ASG.PAY_BASIS_ID
, ASG.PAYROLL_ID
, EEV.INPUT_VALUE_ID
, PEE.CREATION_DATE
, CREATE_USR.USER_NAME
, PEE.LAST_UPDATE_DATE
, UPDATE_USR.USER_NAME
FROM PAY_ELEMENT_ENTRIES_F PEE
, PAY_INPUT_VALUES_F PIV
, PAY_INPUT_VALUES_F_TL PIVT
, PAY_ELEMENT_ENTRY_VALUES_F EEV
, PAY_ELEMENT_LINKS_F PEL
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_TYPES_F_TL PETT
, PER_ASSIGNMENTS_F ASG
, PER_PEOPLE_F PEO
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, FND_USER CREATE_USR
, FND_USER UPDATE_USR
WHERE PET.ELEMENT_TYPE_ID = PEL.ELEMENT_TYPE_ID
AND PET.ELEMENT_TYPE_ID = PETT.ELEMENT_TYPE_ID
AND PETT.LANGUAGE = USERENV('LANG')
AND PIV.INPUT_VALUE_ID = PIVT.INPUT_VALUE_ID
AND PIVT.LANGUAGE = USERENV('LANG')
AND PIV.INPUT_VALUE_ID = EEV.INPUT_VALUE_ID
AND EEV.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND PEE.ELEMENT_LINK_ID = PEL.ELEMENT_LINK_ID
AND PEE.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND ASG.PERSON_ID = PEO.PERSON_ID
AND ASG.ORGANIZATION_ID = ORGT.ORGANIZATION_ID
AND ORGT.LANGUAGE = USERENV('LANG')
AND ASG.BUSINESS_GROUP_ID+0 = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND PEE.LAST_UPDATED_BY = UPDATE_USR.USER_ID(+)
AND PEE.CREATED_BY = CREATE_USR.USER_ID(+)
AND TRUNC(SYSDATE) BETWEEN PEE.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN EEV.EFFECTIVE_START_DATE
AND EEV.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN PEL.EFFECTIVE_START_DATE
AND PEL.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND ASG.BUSINESS_GROUP_ID +0 = NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID
, ASG.BUSINESS_GROUP_ID) WITH READ ONLY