View: SSP_SMP_ENTRIES_V

Product:	SSP - SSP
Description:	Shows element entries for Statutory Maternity Pay (SMP).
Implementation/DBA Data:	APPS.SSP_SMP_ENTRIES_V

View Text

SELECT ENT.ASSIGNMENT_ID
, ASG.PERSON_ID
, DECODE (ELT.ELEMENT_NAME
, 'STATUTORY MATERNITY PAY'
, 'N'
, 'Y')
, FND_DATE.CANONICAL_TO_DATE (MIN (DECODE (PIV.NAME
, 'WEEK COMMENCING'
, EEV.SCREEN_ENTRY_VALUE
, NULL)))
, NEXT_DAY (FND_DATE.CANONICAL_TO_DATE(MIN (DECODE (PIV.NAME
, 'WEEK COMMENCING'
, EEV.SCREEN_ENTRY_VALUE
, NULL)))
, 'SATURDAY')
, 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 MATERNITY PAY'
, 'N'
, 'Y')
, ENT.ELEMENT_ENTRY_ID
, ENT.EFFECTIVE_START_DATE
, ENT.EFFECTIVE_END_DATE
, ENT.CREATOR_ID

Columns