VIEW: APPS.SSP_SSPWSMAT_V

Source

View Text - Preformatted

SELECT mat.maternity_id ,MAT.PERSON_ID ,MAT.DUE_DATE ,MAT.MPP_START_DATE ,decode(mat.leave_type,'PB',ssp_pab_pkg.expected_week_of_confinement(mat.due_date), 'AB',ssp_apab_pkg.expected_week_of_confinement(mat.due_date), ssp_smp_pkg.expected_week_of_confinement(mat.due_date)) ,decode(mat.leave_type,'AD',ssp_sap_pkg.earliest_app_start_date(mat.due_date), 'AB',ssp_apab_pkg.earliest_asppp_start_date(mat.actual_birth_date), 'AA',ssp_apad_pkg.earliest_asppp_start_date(mat.placement_date), ssp_smp_pkg.earliest_mpp_start_date(mat.due_date)) ,decode(mat.leave_type,'PA',ssp_pad_pkg.matching_week_of_adoption(mat.matching_date), 'PB',ssp_pab_pkg.qualifying_week(mat.due_date), 'AD',ssp_sap_pkg.matching_week_of_adoption(mat.matching_date), 'AB',ssp_apab_pkg.qualifying_week(mat.due_date), 'AA',ssp_apad_pkg.matching_week_of_adoption(mat.matching_date), ssp_smp_pkg.qualifying_week(mat.due_date)) ,decode(mat.leave_type,'PA',ssp_pad_pkg.continuous_employment_date(mat.matching_date), 'PB',ssp_pab_pkg.continuous_employment_date(mat.due_date), 'AD',ssp_sap_pkg.continuous_employment_date(mat.matching_date), 'AB',ssp_apab_pkg.continuous_employment_date(mat.due_date), 'AA',ssp_apad_pkg.continuous_employment_date(mat.matching_date), ssp_smp_pkg.continuous_employment_date(mat.due_date)) ,MAT.PAY_SMP_AS_LUMP_SUM ,MAT.SMP_MUST_BE_PAID_BY_DATE ,MAT.START_DATE_WITH_NEW_EMPLOYER ,MAT.START_DATE_MATERNITY_ALLOWANCE ,MAT.NOTIFICATION_OF_BIRTH_DATE ,MAT.LIVE_BIRTH_FLAG ,MAT.ACTUAL_BIRTH_DATE ,MAT.STATED_RETURN_DATE ,MAT.INTEND_TO_RETURN_FLAG ,MAT.UNFIT_FOR_SCHEDULED_RETURN ,MAT.OBJECT_VERSION_NUMBER ,MAT.ATTRIBUTE_CATEGORY ,MAT.ATTRIBUTE1 ,MAT.ATTRIBUTE2 ,MAT.ATTRIBUTE3 ,MAT.ATTRIBUTE4 ,MAT.ATTRIBUTE5 ,MAT.ATTRIBUTE6 ,MAT.ATTRIBUTE7 ,MAT.ATTRIBUTE8 ,MAT.ATTRIBUTE9 ,MAT.ATTRIBUTE10 ,MAT.ATTRIBUTE11 ,MAT.ATTRIBUTE12 ,MAT.ATTRIBUTE13 ,MAT.ATTRIBUTE14 ,MAT.ATTRIBUTE15 ,MAT.ATTRIBUTE16 ,MAT.ATTRIBUTE17 ,MAT.ATTRIBUTE18 ,MAT.ATTRIBUTE19 ,MAT.ATTRIBUTE20 ,MAT.CREATED_BY ,MAT.CREATION_DATE ,MAT.LAST_UPDATE_DATE ,MAT.LAST_UPDATED_BY ,MAT.LAST_UPDATE_LOGIN,NVL(MAT.LEAVE_TYPE,'MA'),MAT.MATCHING_DATE,MAT.PLACEMENT_DATE,MAT.DISRUPTED_PLACEMENT_DATE,MAT.MAT_INFORMATION_CATEGORY ,MAT.MAT_INFORMATION1 ,MAT.MAT_INFORMATION2 ,MAT.MAT_INFORMATION3 ,MAT.MAT_INFORMATION4,MAT.MAT_INFORMATION5 ,MAT.MAT_INFORMATION6 ,MAT.MAT_INFORMATION7 ,MAT.MAT_INFORMATION8 ,MAT.MAT_INFORMATION9 ,MAT.MAT_INFORMATION10 ,MAT.MAT_INFORMATION11 ,MAT.MAT_INFORMATION12 ,MAT.MAT_INFORMATION13 ,MAT.MAT_INFORMATION14 ,MAT.MAT_INFORMATION15 ,MAT.MAT_INFORMATION16 ,MAT.MAT_INFORMATION17 ,MAT.MAT_INFORMATION18 ,MAT.MAT_INFORMATION19 ,MAT.MAT_INFORMATION20 ,MAT.MAT_INFORMATION21 ,MAT.MAT_INFORMATION22 ,MAT.MAT_INFORMATION23 ,MAT.MAT_INFORMATION24 ,MAT.MAT_INFORMATION25 ,MAT.MAT_INFORMATION26 ,MAT.MAT_INFORMATION27 ,MAT.MAT_INFORMATION28 ,MAT.MAT_INFORMATION29 ,MAT.MAT_INFORMATION30 ,mat.PARTNER_STAT_PAY_START_DATE, mat.partner_return_to_work, mat.partner_death_date FROM SSP_MATERNITIES MAT

View Text - HTML Formatted

SELECT MAT.MATERNITY_ID
, MAT.PERSON_ID
, MAT.DUE_DATE
, MAT.MPP_START_DATE
, DECODE(MAT.LEAVE_TYPE
, 'PB'
, SSP_PAB_PKG.EXPECTED_WEEK_OF_CONFINEMENT(MAT.DUE_DATE)
, 'AB'
, SSP_APAB_PKG.EXPECTED_WEEK_OF_CONFINEMENT(MAT.DUE_DATE)
, SSP_SMP_PKG.EXPECTED_WEEK_OF_CONFINEMENT(MAT.DUE_DATE))
, DECODE(MAT.LEAVE_TYPE
, 'AD'
, SSP_SAP_PKG.EARLIEST_APP_START_DATE(MAT.DUE_DATE)
, 'AB'
, SSP_APAB_PKG.EARLIEST_ASPPP_START_DATE(MAT.ACTUAL_BIRTH_DATE)
, 'AA'
, SSP_APAD_PKG.EARLIEST_ASPPP_START_DATE(MAT.PLACEMENT_DATE)
, SSP_SMP_PKG.EARLIEST_MPP_START_DATE(MAT.DUE_DATE))
, DECODE(MAT.LEAVE_TYPE
, 'PA'
, SSP_PAD_PKG.MATCHING_WEEK_OF_ADOPTION(MAT.MATCHING_DATE)
, 'PB'
, SSP_PAB_PKG.QUALIFYING_WEEK(MAT.DUE_DATE)
, 'AD'
, SSP_SAP_PKG.MATCHING_WEEK_OF_ADOPTION(MAT.MATCHING_DATE)
, 'AB'
, SSP_APAB_PKG.QUALIFYING_WEEK(MAT.DUE_DATE)
, 'AA'
, SSP_APAD_PKG.MATCHING_WEEK_OF_ADOPTION(MAT.MATCHING_DATE)
, SSP_SMP_PKG.QUALIFYING_WEEK(MAT.DUE_DATE))
, DECODE(MAT.LEAVE_TYPE
, 'PA'
, SSP_PAD_PKG.CONTINUOUS_EMPLOYMENT_DATE(MAT.MATCHING_DATE)
, 'PB'
, SSP_PAB_PKG.CONTINUOUS_EMPLOYMENT_DATE(MAT.DUE_DATE)
, 'AD'
, SSP_SAP_PKG.CONTINUOUS_EMPLOYMENT_DATE(MAT.MATCHING_DATE)
, 'AB'
, SSP_APAB_PKG.CONTINUOUS_EMPLOYMENT_DATE(MAT.DUE_DATE)
, 'AA'
, SSP_APAD_PKG.CONTINUOUS_EMPLOYMENT_DATE(MAT.MATCHING_DATE)
, SSP_SMP_PKG.CONTINUOUS_EMPLOYMENT_DATE(MAT.DUE_DATE))
, MAT.PAY_SMP_AS_LUMP_SUM
, MAT.SMP_MUST_BE_PAID_BY_DATE
, MAT.START_DATE_WITH_NEW_EMPLOYER
, MAT.START_DATE_MATERNITY_ALLOWANCE
, MAT.NOTIFICATION_OF_BIRTH_DATE
, MAT.LIVE_BIRTH_FLAG
, MAT.ACTUAL_BIRTH_DATE
, MAT.STATED_RETURN_DATE
, MAT.INTEND_TO_RETURN_FLAG
, MAT.UNFIT_FOR_SCHEDULED_RETURN
, MAT.OBJECT_VERSION_NUMBER
, MAT.ATTRIBUTE_CATEGORY
, MAT.ATTRIBUTE1
, MAT.ATTRIBUTE2
, MAT.ATTRIBUTE3
, MAT.ATTRIBUTE4
, MAT.ATTRIBUTE5
, MAT.ATTRIBUTE6
, MAT.ATTRIBUTE7
, MAT.ATTRIBUTE8
, MAT.ATTRIBUTE9
, MAT.ATTRIBUTE10
, MAT.ATTRIBUTE11
, MAT.ATTRIBUTE12
, MAT.ATTRIBUTE13
, MAT.ATTRIBUTE14
, MAT.ATTRIBUTE15
, MAT.ATTRIBUTE16
, MAT.ATTRIBUTE17
, MAT.ATTRIBUTE18
, MAT.ATTRIBUTE19
, MAT.ATTRIBUTE20
, MAT.CREATED_BY
, MAT.CREATION_DATE
, MAT.LAST_UPDATE_DATE
, MAT.LAST_UPDATED_BY
, MAT.LAST_UPDATE_LOGIN
, NVL(MAT.LEAVE_TYPE
, 'MA')
, MAT.MATCHING_DATE
, MAT.PLACEMENT_DATE
, MAT.DISRUPTED_PLACEMENT_DATE
, MAT.MAT_INFORMATION_CATEGORY
, MAT.MAT_INFORMATION1
, MAT.MAT_INFORMATION2
, MAT.MAT_INFORMATION3
, MAT.MAT_INFORMATION4
, MAT.MAT_INFORMATION5
, MAT.MAT_INFORMATION6
, MAT.MAT_INFORMATION7
, MAT.MAT_INFORMATION8
, MAT.MAT_INFORMATION9
, MAT.MAT_INFORMATION10
, MAT.MAT_INFORMATION11
, MAT.MAT_INFORMATION12
, MAT.MAT_INFORMATION13
, MAT.MAT_INFORMATION14
, MAT.MAT_INFORMATION15
, MAT.MAT_INFORMATION16
, MAT.MAT_INFORMATION17
, MAT.MAT_INFORMATION18
, MAT.MAT_INFORMATION19
, MAT.MAT_INFORMATION20
, MAT.MAT_INFORMATION21
, MAT.MAT_INFORMATION22
, MAT.MAT_INFORMATION23
, MAT.MAT_INFORMATION24
, MAT.MAT_INFORMATION25
, MAT.MAT_INFORMATION26
, MAT.MAT_INFORMATION27
, MAT.MAT_INFORMATION28
, MAT.MAT_INFORMATION29
, MAT.MAT_INFORMATION30
, MAT.PARTNER_STAT_PAY_START_DATE
, MAT.PARTNER_RETURN_TO_WORK
, MAT.PARTNER_DEATH_DATE
FROM SSP_MATERNITIES MAT