VIEW: APPS.SSP_SAP_ENTRIES_V

Source

View Text - Preformatted

SELECT ent.assignment_id ,asg.person_id ,decode (elt.element_name,'Statutory Adoption Pay', 'N','Y') ,fnd_date.canonical_to_date (min (decode (piv.name, 'Week Commencing', eev.screen_entry_value,null))) , fnd_date.canonical_to_date(min (decode (piv.name, 'Week Commencing', eev.screen_entry_value,null))) + 6 ,fnd_number.canonical_to_number (min (decode (piv.name, 'Amount', eev.screen_entry_value,null))) ,min (decode (piv.name, 'Rate', eev.screen_entry_value, null)) ,fnd_number.canonical_to_number (min (decode (piv.name, 'Recoverable Amount', eev.screen_entry_value,null))) ,ent.element_entry_id ,ent.effective_start_date ,ent.effective_end_date ,ent.creator_id ,min (decode (piv.name, 'Week Commencing', eev.rowid, null)) FROM pay_element_entry_values_f eev ,pay_element_entries_f ent ,pay_input_values_f piv ,pay_element_links_f pel ,pay_element_types_f elt ,per_assignments_f asg where elt.element_type_id = pel.element_type_id and pel.element_link_id = ent.element_link_id and ent.creator_type='M' and eev.element_entry_id = ent.element_entry_id and eev.input_value_id = piv.input_value_id and eev.effective_start_date between elt.effective_start_date and elt.effective_end_date and eev.effective_start_date between piv.effective_start_date and piv.effective_end_date and eev.effective_start_date between pel.effective_start_date and pel.effective_end_date and asg.assignment_id = ent.assignment_id and eev.effective_start_date between asg.effective_start_date and asg.effective_end_date group by ent.assignment_id, asg.person_id, decode (elt.element_name,'Statutory Adoption Pay', 'N','Y'), ent.element_entry_id, ent.effective_start_date, ent.effective_end_date, ent.creator_id

View Text - HTML Formatted

SELECT ENT.ASSIGNMENT_ID
, ASG.PERSON_ID
, DECODE (ELT.ELEMENT_NAME
, 'STATUTORY ADOPTION PAY'
, 'N'
, 'Y')
, FND_DATE.CANONICAL_TO_DATE (MIN (DECODE (PIV.NAME
, 'WEEK COMMENCING'
, EEV.SCREEN_ENTRY_VALUE
, NULL)))
, FND_DATE.CANONICAL_TO_DATE(MIN (DECODE (PIV.NAME
, 'WEEK COMMENCING'
, EEV.SCREEN_ENTRY_VALUE
, NULL))) + 6
, FND_NUMBER.CANONICAL_TO_NUMBER (MIN (DECODE (PIV.NAME
, 'AMOUNT'
, EEV.SCREEN_ENTRY_VALUE
, NULL)))
, MIN (DECODE (PIV.NAME
, 'RATE'
, EEV.SCREEN_ENTRY_VALUE
, NULL))
, FND_NUMBER.CANONICAL_TO_NUMBER (MIN (DECODE (PIV.NAME
, 'RECOVERABLE AMOUNT'
, EEV.SCREEN_ENTRY_VALUE
, NULL)))
, ENT.ELEMENT_ENTRY_ID
, ENT.EFFECTIVE_START_DATE
, ENT.EFFECTIVE_END_DATE
, ENT.CREATOR_ID
, MIN (DECODE (PIV.NAME
, 'WEEK COMMENCING'
, EEV.ROWID
, NULL))
FROM PAY_ELEMENT_ENTRY_VALUES_F EEV
, PAY_ELEMENT_ENTRIES_F ENT
, PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_LINKS_F PEL
, PAY_ELEMENT_TYPES_F ELT
, PER_ASSIGNMENTS_F ASG
WHERE ELT.ELEMENT_TYPE_ID = PEL.ELEMENT_TYPE_ID
AND PEL.ELEMENT_LINK_ID = ENT.ELEMENT_LINK_ID
AND ENT.CREATOR_TYPE='M'
AND EEV.ELEMENT_ENTRY_ID = ENT.ELEMENT_ENTRY_ID
AND EEV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND EEV.EFFECTIVE_START_DATE BETWEEN ELT.EFFECTIVE_START_DATE
AND ELT.EFFECTIVE_END_DATE
AND EEV.EFFECTIVE_START_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND EEV.EFFECTIVE_START_DATE BETWEEN PEL.EFFECTIVE_START_DATE
AND PEL.EFFECTIVE_END_DATE
AND ASG.ASSIGNMENT_ID = ENT.ASSIGNMENT_ID
AND EEV.EFFECTIVE_START_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE GROUP BY ENT.ASSIGNMENT_ID
, ASG.PERSON_ID
, DECODE (ELT.ELEMENT_NAME
, 'STATUTORY ADOPTION PAY'
, 'N'
, 'Y')
, ENT.ELEMENT_ENTRY_ID
, ENT.EFFECTIVE_START_DATE
, ENT.EFFECTIVE_END_DATE
, ENT.CREATOR_ID