VIEW: APPS.SSP_SSP_ENTRIES_V

Source

View Text - Preformatted

SELECT decode (elt.element_name, 'Statutory Sick Pay','N','Y') ,ele.assignment_id ,paf.person_id ,ele.element_link_id ,ele.element_entry_id ,ele.target_entry_id ,fnd_date.canonical_to_date (min(DECODE (PIV.NAME ,'From', EEV.SCREEN_ENTRY_VALUE, NULL))) ,fnd_date.canonical_to_date (min(DECODE (PIV.NAME ,'To', EEV.SCREEN_ENTRY_VALUE, NULL))) ,fnd_number.canonical_to_number (min(DECODE (PIV.NAME ,'Amount', EEV.SCREEN_ENTRY_VALUE,NULL))) ,fnd_number.canonical_to_number (min(DECODE (PIV.NAME ,'Rate', EEV.SCREEN_ENTRY_VALUE,NULL))) ,fnd_number.canonical_to_number (min(DECODE (PIV.NAME ,'Qualifying days', EEV.SCREEN_ENTRY_VALUE,NULL))) ,fnd_number.canonical_to_number (min(DECODE (PIV.NAME ,'SSP days due', EEV.SCREEN_ENTRY_VALUE,NULL))) ,fnd_number.canonical_to_number (min(DECODE (PIV.NAME ,'Withheld days', EEV.SCREEN_ENTRY_VALUE,NULL))) ,fnd_number.canonical_to_number (min(DECODE (PIV.NAME ,'SSP weeks', EEV.SCREEN_ENTRY_VALUE,NULL))) ,ele.creator_id ,ele.effective_start_date ,ele.effective_end_date ,min (decode (piv.name, 'From', piv.rowid, null)) row_id FROM pay_element_entry_values_f eev ,pay_element_entries_f ele ,pay_input_values_f piv ,pay_element_types_f elt ,per_assignments_f paf where ele.creator_type = 'S' and ele.element_entry_id = eev.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 elt.element_name in ('SSP Corrections', 'Statutory Sick Pay') and paf.assignment_id = ele.assignment_id and eev.effective_start_date between paf.effective_start_date and paf.effective_end_date and exists (select 1 from pay_element_links_f pel where elt.element_type_id = pel.element_type_id and pel.element_link_id = ele.element_link_id and eev.effective_start_date between pel.effective_start_date and pel.effective_end_date) group by decode (elt.element_name, 'Statutory Sick Pay', 'N', 'Y'), ele.assignment_id, paf.person_id, ele.element_entry_id, ele.effective_start_date, ele.effective_end_date, ele.creator_id, ele.element_link_id, ele.target_entry_id

View Text - HTML Formatted

SELECT DECODE (ELT.ELEMENT_NAME
, 'STATUTORY SICK PAY'
, 'N'
, 'Y')
, ELE.ASSIGNMENT_ID
, PAF.PERSON_ID
, ELE.ELEMENT_LINK_ID
, ELE.ELEMENT_ENTRY_ID
, ELE.TARGET_ENTRY_ID
, FND_DATE.CANONICAL_TO_DATE (MIN(DECODE (PIV.NAME
, 'FROM'
, EEV.SCREEN_ENTRY_VALUE
, NULL)))
, FND_DATE.CANONICAL_TO_DATE (MIN(DECODE (PIV.NAME
, 'TO'
, EEV.SCREEN_ENTRY_VALUE
, NULL)))
, FND_NUMBER.CANONICAL_TO_NUMBER (MIN(DECODE (PIV.NAME
, 'AMOUNT'
, EEV.SCREEN_ENTRY_VALUE
, NULL)))
, FND_NUMBER.CANONICAL_TO_NUMBER (MIN(DECODE (PIV.NAME
, 'RATE'
, EEV.SCREEN_ENTRY_VALUE
, NULL)))
, FND_NUMBER.CANONICAL_TO_NUMBER (MIN(DECODE (PIV.NAME
, 'QUALIFYING DAYS'
, EEV.SCREEN_ENTRY_VALUE
, NULL)))
, FND_NUMBER.CANONICAL_TO_NUMBER (MIN(DECODE (PIV.NAME
, 'SSP DAYS DUE'
, EEV.SCREEN_ENTRY_VALUE
, NULL)))
, FND_NUMBER.CANONICAL_TO_NUMBER (MIN(DECODE (PIV.NAME
, 'WITHHELD DAYS'
, EEV.SCREEN_ENTRY_VALUE
, NULL)))
, FND_NUMBER.CANONICAL_TO_NUMBER (MIN(DECODE (PIV.NAME
, 'SSP WEEKS'
, EEV.SCREEN_ENTRY_VALUE
, NULL)))
, ELE.CREATOR_ID
, ELE.EFFECTIVE_START_DATE
, ELE.EFFECTIVE_END_DATE
, MIN (DECODE (PIV.NAME
, 'FROM'
, PIV.ROWID
, NULL)) ROW_ID
FROM PAY_ELEMENT_ENTRY_VALUES_F EEV
, PAY_ELEMENT_ENTRIES_F ELE
, PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_TYPES_F ELT
, PER_ASSIGNMENTS_F PAF
WHERE ELE.CREATOR_TYPE = 'S'
AND ELE.ELEMENT_ENTRY_ID = EEV.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 ELT.ELEMENT_NAME IN ('SSP CORRECTIONS'
, 'STATUTORY SICK PAY')
AND PAF.ASSIGNMENT_ID = ELE.ASSIGNMENT_ID
AND EEV.EFFECTIVE_START_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND EXISTS (SELECT 1
FROM PAY_ELEMENT_LINKS_F PEL
WHERE ELT.ELEMENT_TYPE_ID = PEL.ELEMENT_TYPE_ID
AND PEL.ELEMENT_LINK_ID = ELE.ELEMENT_LINK_ID
AND EEV.EFFECTIVE_START_DATE BETWEEN PEL.EFFECTIVE_START_DATE
AND PEL.EFFECTIVE_END_DATE) GROUP BY DECODE (ELT.ELEMENT_NAME
, 'STATUTORY SICK PAY'
, 'N'
, 'Y')
, ELE.ASSIGNMENT_ID
, PAF.PERSON_ID
, ELE.ELEMENT_ENTRY_ID
, ELE.EFFECTIVE_START_DATE
, ELE.EFFECTIVE_END_DATE
, ELE.CREATOR_ID
, ELE.ELEMENT_LINK_ID
, ELE.TARGET_ENTRY_ID