VIEW: APPS.PAY_PAYE_ELEMENT_ENTRIES_V2

Source

View Text - Preformatted

SELECT ASG.BUSINESS_GROUP_ID , PEO.PERSON_ID , PEO.FULL_NAME , PEO.NATIONAL_IDENTIFIER , ASG.ASSIGNMENT_ID , ASG.ASSIGNMENT_NUMBER , PAY.PAYROLL_ID , PAY.PAYROLL_NAME , ELE.ELEMENT_ENTRY_ID , ELE.EFFECTIVE_START_DATE , ELE.EFFECTIVE_END_DATE , ELE.COST_ALLOCATION_KEYFLEX_ID , ELE.UPDATING_ACTION_ID , ELE.ORIGINAL_ENTRY_ID , ELE.CREATOR_TYPE , ELE.COMMENT_ID , ELE.CREATOR_ID , ELE.REASON , ELE.SUBPRIORITY , ELE.DATE_EARNED , ELE.PERSONAL_PAYMENT_METHOD_ID , ELE.ATTRIBUTE_CATEGORY , ELE.ATTRIBUTE1 , ELE.ATTRIBUTE2 , ELE.ATTRIBUTE3 , ELE.ATTRIBUTE4 , ELE.ATTRIBUTE5 , ELE.ATTRIBUTE6 , ELE.ATTRIBUTE7 , ELE.ATTRIBUTE8 , ELE.ATTRIBUTE9 , ELE.ATTRIBUTE10 , ELE.ATTRIBUTE11 , ELE.ATTRIBUTE12 , ELE.ATTRIBUTE13 , ELE.ATTRIBUTE14 , ELE.ATTRIBUTE15 , ELE.ATTRIBUTE16 , ELE.ATTRIBUTE17 , ELE.ATTRIBUTE18 , ELE.ATTRIBUTE19 , ELE.ATTRIBUTE20 , MIN(DECODE(INV.NAME, 'Tax Code', EEV.INPUT_VALUE_ID, NULL)) TAX_CODE_ID , MIN(DECODE(INV.NAME, 'Tax Code', EEV.SCREEN_ENTRY_VALUE, NULL)) TAX_CODE_SV , MIN(DECODE(INV.NAME, 'Tax Basis', EEV.INPUT_VALUE_ID, NULL)) TAX_BASIS_ID , MIN(DECODE(INV.NAME, 'Tax Basis', EEV.SCREEN_ENTRY_VALUE, NULL)) TAX_BASIS_SV , MIN(DECODE(INV.NAME, 'Pay Previous', EEV.INPUT_VALUE_ID, NULL)) PAY_PREVIOUS_ID , MIN(DECODE(INV.NAME, 'Pay Previous', EEV.SCREEN_ENTRY_VALUE, NULL) ) PAY_PREVIOUS_SV , MIN(DECODE(INV.NAME, 'Tax Previous', EEV.INPUT_VALUE_ID, NULL)) TAX_PREVIOUS_ID , MIN(DECODE(INV.NAME, 'Tax Previous', EEV.SCREEN_ENTRY_VALUE, NULL) ) TAX_PREVIOUS_SV , MIN(DECODE(INV.NAME, 'Authority', EEV.INPUT_VALUE_ID, NULL)) AUTHORITY_ID , MIN(DECODE(INV.NAME, 'Authority', EEV.SCREEN_ENTRY_VALUE, NULL)) AUTHORITY_SV , MIN(DECODE(INV.NAME, 'Refundable', EEV.INPUT_VALUE_ID, NULL)) REFUNDABLE_ID , MIN(DECODE(INV.NAME, 'Refundable', EEV.SCREEN_ENTRY_VALUE, NULL)) REFUNDABLE_SV from per_people_f peo, pay_element_entry_values_f eev, pay_element_entries_f ele, pay_input_values_f inv, pay_element_links_f el, pay_element_types_f elt, fnd_sessions ses, per_assignments_f asg, pay_payrolls_f pay where ses.session_id = userenv('SESSIONID') and peo.person_id = asg.person_id and ses.effective_date between peo.effective_start_date and peo.effective_end_date and asg.assignment_id = ele.assignment_id and ses.effective_date between asg.effective_start_date and asg.effective_end_date and asg.payroll_id = pay.payroll_id and ses.effective_date between pay.effective_start_date and pay.effective_end_date and ele.element_entry_id = eev.element_entry_id and ses.effective_date between ele.effective_start_date and ele.effective_end_date and el.element_link_id + decode(ASG.assignment_id,null,0,0) = ele.element_link_id and ses.effective_date between el.effective_start_date and el.effective_end_date and elt.element_type_id = el.element_type_id and eev.input_value_id + 0 = inv.input_value_id and ses.effective_date between eev.effective_start_date and eev.effective_end_date and inv.element_type_id = elt.element_type_id + decode(ele.element_entry_id,null,0,0) and ses.effective_date between inv.effective_start_date and inv.effective_end_date and elt.element_name = 'PAYE Details' and ses.effective_date between elt.effective_start_date and elt.effective_end_date group by asg.business_group_id, peo.person_id, peo.full_name, peo.national_identifier, asg.assignment_id, asg.assignment_number, pay.payroll_id, pay.payroll_name, ele.element_entry_id, ele.effective_start_date, ele.effective_end_date, ele.cost_allocation_keyflex_id, ele.updating_action_id, ele.original_entry_id, ele.creator_type, ele.comment_id, ele.creator_id, ele.reason, ele.subpriority, ele.date_earned, ele.personal_payment_method_id, ele.attribute_category, ele.attribute1, ele.attribute2, ele.attribute3, ele.attribute4, ele.attribute5, ele.attribute6, ele.attribute7, ele.attribute8, ele.attribute9, ele.attribute10, ele.attribute11, ele.attribute12, ele.attribute13, ele.attribute14, ele.attribute15, ele.attribute16, ele.attribute17, ele.attribute18, ele.attribute19, ele.attribute20

View Text - HTML Formatted

SELECT ASG.BUSINESS_GROUP_ID
, PEO.PERSON_ID
, PEO.FULL_NAME
, PEO.NATIONAL_IDENTIFIER
, ASG.ASSIGNMENT_ID
, ASG.ASSIGNMENT_NUMBER
, PAY.PAYROLL_ID
, PAY.PAYROLL_NAME
, ELE.ELEMENT_ENTRY_ID
, ELE.EFFECTIVE_START_DATE
, ELE.EFFECTIVE_END_DATE
, ELE.COST_ALLOCATION_KEYFLEX_ID
, ELE.UPDATING_ACTION_ID
, ELE.ORIGINAL_ENTRY_ID
, ELE.CREATOR_TYPE
, ELE.COMMENT_ID
, ELE.CREATOR_ID
, ELE.REASON
, ELE.SUBPRIORITY
, ELE.DATE_EARNED
, ELE.PERSONAL_PAYMENT_METHOD_ID
, ELE.ATTRIBUTE_CATEGORY
, ELE.ATTRIBUTE1
, ELE.ATTRIBUTE2
, ELE.ATTRIBUTE3
, ELE.ATTRIBUTE4
, ELE.ATTRIBUTE5
, ELE.ATTRIBUTE6
, ELE.ATTRIBUTE7
, ELE.ATTRIBUTE8
, ELE.ATTRIBUTE9
, ELE.ATTRIBUTE10
, ELE.ATTRIBUTE11
, ELE.ATTRIBUTE12
, ELE.ATTRIBUTE13
, ELE.ATTRIBUTE14
, ELE.ATTRIBUTE15
, ELE.ATTRIBUTE16
, ELE.ATTRIBUTE17
, ELE.ATTRIBUTE18
, ELE.ATTRIBUTE19
, ELE.ATTRIBUTE20
, MIN(DECODE(INV.NAME
, 'TAX CODE'
, EEV.INPUT_VALUE_ID
, NULL)) TAX_CODE_ID
, MIN(DECODE(INV.NAME
, 'TAX CODE'
, EEV.SCREEN_ENTRY_VALUE
, NULL)) TAX_CODE_SV
, MIN(DECODE(INV.NAME
, 'TAX BASIS'
, EEV.INPUT_VALUE_ID
, NULL)) TAX_BASIS_ID
, MIN(DECODE(INV.NAME
, 'TAX BASIS'
, EEV.SCREEN_ENTRY_VALUE
, NULL)) TAX_BASIS_SV
, MIN(DECODE(INV.NAME
, 'PAY PREVIOUS'
, EEV.INPUT_VALUE_ID
, NULL)) PAY_PREVIOUS_ID
, MIN(DECODE(INV.NAME
, 'PAY PREVIOUS'
, EEV.SCREEN_ENTRY_VALUE
, NULL) ) PAY_PREVIOUS_SV
, MIN(DECODE(INV.NAME
, 'TAX PREVIOUS'
, EEV.INPUT_VALUE_ID
, NULL)) TAX_PREVIOUS_ID
, MIN(DECODE(INV.NAME
, 'TAX PREVIOUS'
, EEV.SCREEN_ENTRY_VALUE
, NULL) ) TAX_PREVIOUS_SV
, MIN(DECODE(INV.NAME
, 'AUTHORITY'
, EEV.INPUT_VALUE_ID
, NULL)) AUTHORITY_ID
, MIN(DECODE(INV.NAME
, 'AUTHORITY'
, EEV.SCREEN_ENTRY_VALUE
, NULL)) AUTHORITY_SV
, MIN(DECODE(INV.NAME
, 'REFUNDABLE'
, EEV.INPUT_VALUE_ID
, NULL)) REFUNDABLE_ID
, MIN(DECODE(INV.NAME
, 'REFUNDABLE'
, EEV.SCREEN_ENTRY_VALUE
, NULL)) REFUNDABLE_SV
FROM PER_PEOPLE_F PEO
, PAY_ELEMENT_ENTRY_VALUES_F EEV
, PAY_ELEMENT_ENTRIES_F ELE
, PAY_INPUT_VALUES_F INV
, PAY_ELEMENT_LINKS_F EL
, PAY_ELEMENT_TYPES_F ELT
, FND_SESSIONS SES
, PER_ASSIGNMENTS_F ASG
, PAY_PAYROLLS_F PAY
WHERE SES.SESSION_ID = USERENV('SESSIONID')
AND PEO.PERSON_ID = ASG.PERSON_ID
AND SES.EFFECTIVE_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND ASG.ASSIGNMENT_ID = ELE.ASSIGNMENT_ID
AND SES.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND ASG.PAYROLL_ID = PAY.PAYROLL_ID
AND SES.EFFECTIVE_DATE BETWEEN PAY.EFFECTIVE_START_DATE
AND PAY.EFFECTIVE_END_DATE
AND ELE.ELEMENT_ENTRY_ID = EEV.ELEMENT_ENTRY_ID
AND SES.EFFECTIVE_DATE BETWEEN ELE.EFFECTIVE_START_DATE
AND ELE.EFFECTIVE_END_DATE
AND EL.ELEMENT_LINK_ID + DECODE(ASG.ASSIGNMENT_ID
, NULL
, 0
, 0) = ELE.ELEMENT_LINK_ID
AND SES.EFFECTIVE_DATE BETWEEN EL.EFFECTIVE_START_DATE
AND EL.EFFECTIVE_END_DATE
AND ELT.ELEMENT_TYPE_ID = EL.ELEMENT_TYPE_ID
AND EEV.INPUT_VALUE_ID + 0 = INV.INPUT_VALUE_ID
AND SES.EFFECTIVE_DATE BETWEEN EEV.EFFECTIVE_START_DATE
AND EEV.EFFECTIVE_END_DATE
AND INV.ELEMENT_TYPE_ID = ELT.ELEMENT_TYPE_ID + DECODE(ELE.ELEMENT_ENTRY_ID
, NULL
, 0
, 0)
AND SES.EFFECTIVE_DATE BETWEEN INV.EFFECTIVE_START_DATE
AND INV.EFFECTIVE_END_DATE
AND ELT.ELEMENT_NAME = 'PAYE DETAILS'
AND SES.EFFECTIVE_DATE BETWEEN ELT.EFFECTIVE_START_DATE
AND ELT.EFFECTIVE_END_DATE GROUP BY ASG.BUSINESS_GROUP_ID
, PEO.PERSON_ID
, PEO.FULL_NAME
, PEO.NATIONAL_IDENTIFIER
, ASG.ASSIGNMENT_ID
, ASG.ASSIGNMENT_NUMBER
, PAY.PAYROLL_ID
, PAY.PAYROLL_NAME
, ELE.ELEMENT_ENTRY_ID
, ELE.EFFECTIVE_START_DATE
, ELE.EFFECTIVE_END_DATE
, ELE.COST_ALLOCATION_KEYFLEX_ID
, ELE.UPDATING_ACTION_ID
, ELE.ORIGINAL_ENTRY_ID
, ELE.CREATOR_TYPE
, ELE.COMMENT_ID
, ELE.CREATOR_ID
, ELE.REASON
, ELE.SUBPRIORITY
, ELE.DATE_EARNED
, ELE.PERSONAL_PAYMENT_METHOD_ID
, ELE.ATTRIBUTE_CATEGORY
, ELE.ATTRIBUTE1
, ELE.ATTRIBUTE2
, ELE.ATTRIBUTE3
, ELE.ATTRIBUTE4
, ELE.ATTRIBUTE5
, ELE.ATTRIBUTE6
, ELE.ATTRIBUTE7
, ELE.ATTRIBUTE8
, ELE.ATTRIBUTE9
, ELE.ATTRIBUTE10
, ELE.ATTRIBUTE11
, ELE.ATTRIBUTE12
, ELE.ATTRIBUTE13
, ELE.ATTRIBUTE14
, ELE.ATTRIBUTE15
, ELE.ATTRIBUTE16
, ELE.ATTRIBUTE17
, ELE.ATTRIBUTE18
, ELE.ATTRIBUTE19
, ELE.ATTRIBUTE20