SELECT entry.element_entry_id,
entry.assignment_id,
entry.effective_start_date,
entry.effective_end_date,
entry.cost_allocation_keyflex_id,
entry.updating_action_id,
entry.element_link_id,
entry.original_entry_id,
entry.creator_type,
entry.entry_type,
DECODE (entry.entry_type, 'D', 'Y', 'N'),
DECODE (entry.entry_type, 'S', 'Y', 'N'),
entry.comment_id,
entry.creator_id,
entry.reason,
hr_general.decode_lookup ('ELE_ENTRY_REASON', entry.reason),
entry.target_entry_id,
entry.attribute_category,
entry.attribute1,
entry.attribute2,
entry.attribute3,
entry.attribute4,
entry.attribute5,
entry.attribute6,
entry.attribute7,
entry.attribute8,
entry.attribute9,
entry.attribute10,
entry.attribute11,
entry.attribute12,
entry.attribute13,
entry.attribute14,
entry.attribute15,
entry.attribute16,
entry.attribute17,
entry.attribute18,
entry.attribute19,
entry.attribute20,
ELEMENT.element_type_id,
elementtl.element_name,
SUBSTR (ELEMENT.processing_type, 1, 1),
hr_general.decode_lookup ('PROCESSING_TYPE', ELEMENT.processing_type),
ELEMENT.processing_priority,
SUBSTR (ELEMENT.process_in_run_flag, 1, 1),
SUBSTR (ELEMENT.closed_for_entry_flag, 1, 1),
SUBSTR (ELEMENT.additional_entry_allowed_flag, 1, 1),
SUBSTR (ELEMENT.multiple_entries_allowed_flag, 1, 1),
ELEMENT.input_currency_code,
SUBSTR (pay_paywsmee_pkg.overridden (LINK.element_link_id,
entry.assignment_id,
sesh.effective_date
),
1,
1
),
SUBSTR (pay_paywsmee_pkg.adjusted (LINK.element_link_id,
entry.assignment_id,
sesh.effective_date
),
1,
1
),
SUBSTR (pay_paywsmee_pkg.processed (entry.element_entry_id,
entry.original_entry_id,
ELEMENT.processing_type,
entry.entry_type,
sesh.effective_date
),
1,
1
),
DECODE (NVL (entry_proc.source_asg_action_id, -1), -1, 'N', 'Y')
retroactive,
ELEMENT.classification_id,
SUBSTR (LINK.costable_type, 1, 1),
entry.subpriority,
benefit.contributions_used,
entry.creation_date,
entry.created_by,
entry.last_update_login,
entry.last_updated_by,
entry.last_update_date,
entry.date_earned,
pay_paywsmee_pkg.get_original_date_earned (entry.element_entry_id)
original_date_earned,
entry.personal_payment_method_id,
SUBSTR
(pay_paywsmee_pkg.personal_payment_method
(entry.personal_payment_method_id,
entry.assignment_id,
sesh.effective_date
),
1,
255
),
ELEMENT.third_party_pay_only_flag,
entry.ROWID row_id,
entry.entry_information_category,
entry.entry_information1,
entry.entry_information2,
entry.entry_information3,
entry.entry_information4,
entry.entry_information5,
entry.entry_information6,
entry.entry_information7,
entry.entry_information8,
entry.entry_information9,
entry.entry_information10,
entry.entry_information11,
entry.entry_information12,
entry.entry_information13,
entry.entry_information14,
entry.entry_information15,
entry.entry_information16,
entry.entry_information17,
entry.entry_information18,
entry.entry_information19,
entry.entry_information20,
entry.entry_information21,
entry.entry_information22,
entry.entry_information23,
entry.entry_information24,
entry.entry_information25,
entry.entry_information26,
entry.entry_information27,
entry.entry_information28,
entry.entry_information29,
entry.entry_information30,
-999999999999999
FROM pay_element_types_f_tl elementtl,
pay_element_types_f ELEMENT,
ben_benefit_classifications benefit,
pay_element_entries_f entry,
pay_element_links_f LINK,
pay_entry_process_details entry_proc,
fnd_sessions sesh /* TABLE JOINS */
WHERE ELEMENT.element_type_id = elementtl.element_type_id
AND elementtl.LANGUAGE = 'AR'
AND ELEMENT.element_type_id = LINK.element_type_id
AND ELEMENT.benefit_classification_id = benefit.benefit_classification_id(+)
AND entry.element_link_id = LINK.element_link_id
AND entry.element_entry_id = entry_proc.element_entry_id(+)
/* ONLY DISPLAY ENTRIES FOR CERTAIN SUB-SYSTEMS */
AND entry.creator_type IN
('H', /* MIX */
'P', /* BACKPAY */
'SP', /* SALARY PROPOSAL */
'F', /* OTHER */
'M', /* STATUTORY MATERNITY PAY */
'S', /* STATUTORY SICK PAY */
'A', /* ABSENCE */
'D', /* ADVANCE PAY */
'DF', /* ADVANCE PAY FORM */
'R', /* RETROPAY BY ACTION */
'EE',
/* RETROPAY/ELEMENT EE */
'RR',
/* RETROPAY/ELEMENT RR */
'AD',
/* ADVANCEPAY/ELEMENT AD */
'AE',
/* ADVANCEPAY/ELEMENT AE */
'PR', /* RETROPAY ELEMENT PR */
'NR', /* RETROPAY/ELEMENT NR */
'FL' /* FLSA ENTRY */
) /* ONLY DISPLAY ENTRIES OF CERTAIN TYPES */
AND entry.entry_type IN
('E', /* NORMAL ENTRY */ 'S', /* OVERRIDE */ 'D' /* ADDITIONAL */)
/* ONLY DISPLAY ENTRIES CURRENT AS OF EFFECTIVE DATE */
AND USERENV ('sessionid') = sesh.session_id
AND sesh.effective_date BETWEEN ELEMENT.effective_start_date
AND ELEMENT.effective_end_date
AND sesh.effective_date BETWEEN LINK.effective_start_date
AND LINK.effective_end_date
AND sesh.effective_date BETWEEN entry.effective_start_date
AND entry.effective_end_date
UNION ALL
SELECT DISTINCTentry.element_entry_id,
entry.assignment_id,
entry.effective_start_date,
entry.effective_end_date,
entry.cost_allocation_keyflex_id,
entry.updating_action_id,
entry.element_link_id,
entry.original_entry_id,
entry.creator_type,
entry.entry_type,
DECODE (entry.entry_type, 'D', 'Y', 'N'),
DECODE (entry.entry_type, 'S', 'Y', 'N'),
entry.comment_id,
entry.creator_id,
entry.reason,
hr_general.decode_lookup ('ELE_ENTRY_REASON', entry.reason),
entry.target_entry_id,
entry.attribute_category,
entry.attribute1,
entry.attribute2,
entry.attribute3,
entry.attribute4,
entry.attribute5,
entry.attribute6,
entry.attribute7,
entry.attribute8,
entry.attribute9,
entry.attribute10,
entry.attribute11,
entry.attribute12,
entry.attribute13,
entry.attribute14,
entry.attribute15,
entry.attribute16,
entry.attribute17,
entry.attribute18,
entry.attribute19,
entry.attribute20,
ELEMENT.element_type_id,
elementtl.element_name,
SUBSTR (ELEMENT.processing_type, 1, 1),
hr_general.decode_lookup ('PROCESSING_TYPE',
ELEMENT.processing_type
),
ELEMENT.processing_priority,
SUBSTR (ELEMENT.process_in_run_flag, 1, 1),
SUBSTR (ELEMENT.closed_for_entry_flag, 1, 1),
SUBSTR (ELEMENT.additional_entry_allowed_flag, 1, 1),
SUBSTR (ELEMENT.multiple_entries_allowed_flag, 1, 1),
ELEMENT.input_currency_code,
SUBSTR (pay_paywsmee_pkg.overridden (LINK.element_link_id,
entry.assignment_id,
sesh.effective_date
),
1,
1
),
SUBSTR (pay_paywsmee_pkg.adjusted (LINK.element_link_id,
entry.assignment_id,
sesh.effective_date
),
1,
1
),
SUBSTR (pay_paywsmee_pkg.processed (entry.element_entry_id,
entry.original_entry_id,
ELEMENT.processing_type,
entry.entry_type,
sesh.effective_date
),
1,
1
),
DECODE (NVL (entry_proc.source_asg_action_id, -1),
-1, 'N',
'Y'
) retroactive,
ELEMENT.classification_id,
SUBSTR (LINK.costable_type, 1, 1),
entry.subpriority,
benefit.contributions_used,
entry.creation_date,
entry.created_by,
entry.last_update_login,
entry.last_updated_by,
entry.last_update_date,
entry.date_earned,
pay_paywsmee_pkg.get_original_date_earned
(entry.element_entry_id)
original_date_earned,
entry.personal_payment_method_id,
SUBSTR
(pay_paywsmee_pkg.personal_payment_method
(entry.personal_payment_method_id,
entry.assignment_id,
sesh.effective_date
),
1,
255
),
ELEMENT.third_party_pay_only_flag,
entry.ROWID row_id,
entry.entry_information_category,
entry.entry_information1,
entry.entry_information2,
entry.entry_information3,
entry.entry_information4,
entry.entry_information5,
entry.entry_information6,
entry.entry_information7,
entry.entry_information8,
entry.entry_information9,
entry.entry_information10,
entry.entry_information11,
entry.entry_information12,
entry.entry_information13,
entry.entry_information14,
entry.entry_information15,
entry.entry_information16,
entry.entry_information17,
entry.entry_information18,
entry.entry_information19,
entry.entry_information20,
entry.entry_information21,
entry.entry_information22,
entry.entry_information23,
entry.entry_information24,
entry.entry_information25,
entry.entry_information26,
entry.entry_information27,
entry.entry_information28,
entry.entry_information29,
entry.entry_information30,
assact.assignment_action_id
FROM pay_element_types_f_tl elementtl,
pay_element_types_f ELEMENT,
ben_benefit_classifications benefit,
pay_element_entries_f entry,
pay_element_links_f LINK,
pay_entry_process_details entry_proc,
pay_assignment_actions assact,
pay_payroll_actions payact,
fnd_sessions sesh /* TABLE JOINS */
WHERE ELEMENT.element_type_id = elementtl.element_type_id
AND elementtl.LANGUAGE = USERENV ('LANG')
AND ELEMENT.element_type_id = LINK.element_type_id
AND ELEMENT.benefit_classification_id = benefit.benefit_classification_id(+)
AND entry.element_link_id = LINK.element_link_id
AND entry.element_entry_id = entry_proc.element_entry_id(+)
/* ONLY DISPLAY ENTRIES FOR CERTAIN SUB-SYSTEMS */
AND entry.creator_type IN
('H', /* MIX */
'P', /* BACKPAY */
'SP', /* SALARY PROPOSAL */
'F', /* OTHER */
'M', /* STATUTORY MATERNITY PAY */
'S', /* STATUTORY SICK PAY */
'A', /* ABSENCE */
'D', /* ADVANCE PAY */
'DF',
/* ADVANCE PAY FORM */
'R',
/* RETROPAY BY ACTION */
'EE',
/* RETROPAY/ELEMENT EE */
'RR',
/* RETROPAY/ELEMENT RR */
'AD',
/* ADVANCEPAY/ELEMENT AD */
'AE', /* ADVANCEPAY/ELEMENT AE */
'PR', /* RETROPAY ELEMENT PR */
'NR', /* RETROPAY/ELEMENT NR */
'FL' /* FLSA ENTRY */
)
/* ONLY DISPLAY ENTRIES OF CERTAIN TYPES */
AND entry.entry_type IN
('E', /* NORMAL ENTRY */ 'S', /* OVERRIDE */ 'D' /* ADDITIONAL */)
/* ONLY DISPLAY ENTRIES CURRENT AS OF EFFECTIVE DATE */
AND USERENV ('sessionid') = sesh.session_id
AND sesh.effective_date BETWEEN ELEMENT.effective_start_date
AND ELEMENT.effective_end_date
AND sesh.effective_date BETWEEN LINK.effective_start_date
AND LINK.effective_end_date
AND entry.assignment_id = assact.assignment_id
AND assact.payroll_action_id = payact.payroll_action_id
AND payact.date_earned BETWEEN ELEMENT.effective_start_date
AND ELEMENT.effective_end_date
AND payact.date_earned BETWEEN LINK.effective_start_date
AND LINK.effective_end_date
AND entry.effective_start_date <= payact.date_earned
AND entry.effective_end_date >=
DECODE
(ELEMENT.proration_group_id,
NULL, payact.date_earned,
pay_interpreter_pkg.prorate_start_date
(assact.assignment_action_id,
ELEMENT.proration_group_id
)
)
/
No comments:
Post a Comment