@AbapCatalog.sqlViewName: 'PGCALDF'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.usageType.serviceQuality: #P
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.viewType: #CONSUMPTION
@VDM.private: true
define view P_GregorianCalDateFunction
with parameters
P_Language : sylangu,
P_DateFunctionValidityDate :sydats
as select from I_Datefunction as DynamicDate
inner join I_CalendarDate as CurrDate on CurrDate.CalendarDate = :P_DateFunctionValidityDate
inner join P_Referencedates(P_DateFunctionValidityDate : $parameters.P_DateFunctionValidityDate) as CalDate on CalDate.CalendarDate = :P_DateFunctionValidityDate
inner join I_CalendarDate as LastYear on CalDate.LastYearDate = LastYear.CalendarDate
inner join I_CalendarDate as NextYear on CalDate.NextYearDate = NextYear.CalendarDate
{
key DateFunction,
_DateFunctionText[1: Language = $parameters.P_Language].DateFunctionName as DateFunctionName,
_DateFunctionText[1: Language = $parameters.P_Language].DateFunctionDescription as DateFunctionDescription,
DateFunctionType,
DateFunctionGranularity,
DateFunctionCalendarType,
DateFunctionStartPoint,
DateFunctionStartOffsetValue,
DateFuncStartRelativeOffset,
DateFunctionStartOffsetUnit,
DateFunctionStartRelOffsetUnit,
DateFunctionStartYearOffset,
DateFunctionStartFixedDate,
DateFunctionEndPoint,
DateFunctionEndOffsetValue,
DateFunctionEndRelativeOffset,
DateFunctionEndOffsetUnit,
DateFunctionEndRelOffsetUnit,
DateFunctionEndYearOffset,
DateFunctionEndFixedDate,
case // First performing the calculation for Start
when DateFunctionStartPoint = '02' then CurrDate.CalendarDate // Today
when DateFunctionStartPoint = '03' then dats_add_days(CalDate.CalendarDate, 1 , 'FAIL' ) // Tomorrow
when DateFunctionStartPoint = '01' then dats_add_days(CalDate.CalendarDate, -1 , 'FAIL' ) // Yesterday
when DateFunctionStartPoint = '04' or DateFunctionStartPoint = '08' then case DateFunctionStartRelOffsetUnit // First Day of
// when 'DAY' then '' // not necessary
when '02' then case DateFuncStartRelativeOffset // Week
when '02' then case DateFunctionStartYearOffset // Current
when '01' then LastYear.FirstDayOfWeekDate // Previous
when '03' then NextYear.FirstDayOfWeekDate // Next
else CurrDate.FirstDayOfWeekDate end
when '01' then case DateFunctionStartYearOffset
when '01' then dats_add_days( LastYear.FirstDayOfWeekDate, -7, 'FAIL')
when '03' then dats_add_days( NextYear.FirstDayOfWeekDate, -7, 'FAIL')
else dats_add_days( CurrDate.FirstDayOfWeekDate, -7, 'FAIL') end
when '03' then case DateFunctionStartYearOffset
when '01' then dats_add_days( LastYear.FirstDayOfWeekDate, 7, 'FAIL')
when '03' then dats_add_days( NextYear.FirstDayOfWeekDate, 7, 'FAIL')
else dats_add_days( CurrDate.FirstDayOfWeekDate, 7, 'FAIL') end // next week current year end
when '04' then case DateFunctionStartYearOffset
when '01' then dats_add_days( LastYear.FirstDayOfWeekDate, ( (cast(LastYear.CalendarWeek as int2) - 1) * -7 ), 'FAIL') //first day of first week previous year
when '03' then dats_add_days( NextYear.FirstDayOfWeekDate, ( (cast(NextYear.CalendarWeek as int2) - 1) * -7 ), 'FAIL') //first day of first week next year
else dats_add_days( CurrDate.FirstDayOfWeekDate, ( (cast(CurrDate.CalendarWeek as int2) - 1) * -7 ), 'FAIL') end
when '05' then case DateFunctionStartYearOffset
when '01' then dats_add_days( CurrDate.FirstDayOfWeekDate, ( (cast(CurrDate.CalendarWeek as int2) ) * -7 ) , 'FAIL')
when '03' then dats_add_days( cast( concat(NextYear.CalendarYear , '1231' ) as abap.dats) , (mod((cast( NextYear.WeekDay as abap.int2) + mod( dats_days_between( NextYear.CalendarDate , cast( concat( NextYear.CalendarYear, '1231') as abap.dats ) ), 7)), 7 ) * -1) + 1, 'FAIL' ) //first day of last week next year
else dats_add_days( NextYear.FirstDayOfWeekDate, ( (cast(NextYear.CalendarWeek as int2) ) * -7 ), 'FAIL') end
else CurrDate.CalendarDate end
when '03' then case DateFuncStartRelativeOffset // Month
when '02' then case DateFunctionStartYearOffset
when '01' then LastYear.FirstDayOfMonthDate
when '03' then NextYear.FirstDayOfMonthDate
else CurrDate.FirstDayOfMonthDate end
when '01' then case DateFunctionStartYearOffset
when '01' then dats_add_months( CurrDate.FirstDayOfMonthDate, -13, 'FAIL')
when '03' then dats_add_months( CurrDate.FirstDayOfMonthDate, 11, 'FAIL')
else dats_add_months( CurrDate.FirstDayOfMonthDate, -1, 'FAIL') end
when '03' then case DateFunctionStartYearOffset
when '01' then dats_add_months( CurrDate.FirstDayOfMonthDate, -11, 'FAIL')
when '03' then dats_add_months( CurrDate.FirstDayOfMonthDate, 13, 'FAIL')
else dats_add_months( CurrDate.FirstDayOfMonthDate, 1, 'FAIL') end
when '04' then case DateFunctionStartYearOffset
when '01' then dats_add_months( LastYear.FirstDayOfMonthDate, ( cast(LastYear.CalendarMonth as abap.int4) -1 )* -1, 'FAIL')
when '03' then dats_add_months( NextYear.FirstDayOfMonthDate, ( cast(NextYear.CalendarMonth as abap.int4) - 1 ) * -1, 'FAIL')
else dats_add_months( CurrDate.FirstDayOfMonthDate, (cast(CurrDate.CalendarMonth as abap.int4) - 1 ) * -1, 'FAIL') end
when '05' then case DateFunctionStartYearOffset
when '01' then dats_add_months( LastYear.FirstDayOfMonthDate, (12 - cast(LastYear.CalendarMonth as abap.int4) ), 'FAIL')
when '03' then dats_add_months( NextYear.FirstDayOfMonthDate, (12 - cast(NextYear.CalendarMonth as abap.int4) ), 'FAIL')
else dats_add_months( CurrDate.FirstDayOfMonthDate, (12 - cast(CurrDate.CalendarMonth as abap.int4) ), 'FAIL') end
else CurrDate.CalendarDate end
when '04' then case DateFuncStartRelativeOffset // Quarter
when '02' then case DateFunctionStartYearOffset
when '01' then case LastYear.CalendarQuarter
when '1' then cast( ( concat( LastYear.CalendarYear , '0101' ) ) as abap.dats)
when '2' then cast( ( concat( LastYear.CalendarYear , '0401' ) ) as abap.dats)
when '3' then cast( ( concat( LastYear.CalendarYear , '0701' ) ) as abap.dats)
else cast( ( concat( LastYear.CalendarYear , '1001' ) ) as abap.dats) end
when '03' then case NextYear.CalendarQuarter
when '1' then cast( ( concat( NextYear.CalendarYear , '0101' ) ) as abap.dats)
when '2' then cast( ( concat( NextYear.CalendarYear , '0401' ) ) as abap.dats)
when '3' then cast( ( concat( NextYear.CalendarYear , '0701' ) ) as abap.dats)
else cast( ( concat( NextYear.CalendarYear , '1001' ) ) as abap.dats) end
else case CurrDate.CalendarQuarter
when '1' then cast( ( concat( CurrDate.CalendarYear , '0101' ) ) as abap.dats)
when '2' then cast( ( concat( CurrDate.CalendarYear , '0401' ) ) as abap.dats)
when '3' then cast( ( concat( CurrDate.CalendarYear , '0701' ) ) as abap.dats)
else cast( ( concat( CurrDate.CalendarYear , '1001' ) ) as abap.dats) end
end
when '01' then case DateFunctionStartYearOffset
when '01' then case LastYear.CalendarQuarter
when '2' then cast( ( concat( LastYear.CalendarYear , '0101' ) ) as abap.dats)
when '3' then cast( ( concat( LastYear.CalendarYear , '0401' ) ) as abap.dats)
when '4' then cast( ( concat( LastYear.CalendarYear , '0701' ) ) as abap.dats)
else cast( concat( substring( dats_add_months( LastYear.CalendarDate , -12 , 'FAIL'), 1, 4) , '1001' ) as abap.dats) end
when '03' then case NextYear.CalendarQuarter
when '2' then cast( ( concat( NextYear.CalendarYear , '0101' ) ) as abap.dats)
when '3' then cast( ( concat( NextYear.CalendarYear , '0401') ) as abap.dats)
when '4' then cast( ( concat( NextYear.CalendarYear , '0701') ) as abap.dats)
else cast( ( concat( CurrDate.CalendarYear , '1001') ) as abap.dats) end
else case CurrDate.CalendarQuarter
when '2' then cast( ( concat( CurrDate.CalendarYear , '0101' ) ) as abap.dats)
when '3' then cast( ( concat( CurrDate.CalendarYear , '0401') ) as abap.dats)
when '4' then cast( ( concat( CurrDate.CalendarYear , '0701') ) as abap.dats)
else cast( concat( LastYear.CalendarYear , '1001' ) as abap.dats ) end
end
when '03' then case DateFunctionStartYearOffset
when '01' then case LastYear.CalendarQuarter
when '1' then cast( ( concat( LastYear.CalendarYear , '0401' ) ) as abap.dats)
when '2' then cast( ( concat( LastYear.CalendarYear , '0701' ) ) as abap.dats)
when '3' then cast( ( concat( LastYear.CalendarYear , '1001' ) ) as abap.dats)
else cast( concat( CurrDate.CalendarYear , '0101' ) as abap.dats ) end
when '03' then case NextYear.CalendarQuarter
when '1' then cast( ( concat( NextYear.CalendarYear , '0401' ) ) as abap.dats)
when '2' then cast( ( concat( NextYear.CalendarYear , '0701' ) ) as abap.dats)
when '3' then cast( ( concat( NextYear.CalendarYear , '1001' ) ) as abap.dats)
else cast( concat( substring( dats_add_months( NextYear.CalendarDate , 12 , 'FAIL') , 1, 4) , '0101' ) as abap.dats ) end
else case CurrDate.CalendarQuarter
when '1' then cast( ( concat( CurrDate.CalendarYear , '0401' ) ) as abap.dats)
when '2' then cast( ( concat( CurrDate.CalendarYear , '0701' ) ) as abap.dats)
when '3' then cast( ( concat( CurrDate.CalendarYear , '1001' ) ) as abap.dats)
else cast( concat( NextYear.CalendarYear , '0101' ) as abap.dats ) end
end
when '04' then case DateFunctionStartYearOffset
when '01' then cast( ( concat( LastYear.CalendarYear , '0101' ) ) as abap.dats)
when '03' then cast( ( concat( NextYear.CalendarYear , '0101' ) ) as abap.dats)
else cast( ( concat( CurrDate.CalendarYear , '0101' ) ) as abap.dats) end
when '05' then case DateFunctionStartYearOffset
when '01' then cast( ( concat( LastYear.CalendarYear , '1201' ) ) as abap.dats)
when '03' then cast( ( concat( NextYear.CalendarYear , '1201' ) ) as abap.dats)
else cast( ( concat( CurrDate.CalendarYear , '1201' ) ) as abap.dats) end
else CurrDate.CalendarDate end
// when 'YEAR' then '' // Year has separate field relativeyear
when '05' then case DateFunctionStartYearOffset //first day of [current/previous/next] year
when '01' then dats_add_months( LastYear.FirstDayOfMonthDate, ( cast(LastYear.CalendarMonth as abap.int4) -1 )* -1, 'FAIL')
when '03' then dats_add_months( NextYear.FirstDayOfMonthDate, ( cast(NextYear.CalendarMonth as abap.int4) - 1 ) * -1, 'FAIL')
else dats_add_months( CurrDate.FirstDayOfMonthDate, (cast(CurrDate.CalendarMonth as abap.int4) - 1 ) * -1, 'FAIL') end
else CurrDate.CalendarDate end
when DateFunctionStartPoint = '05' or DateFunctionStartPoint = '09' then case DateFunctionStartRelOffsetUnit // Last Day of
// when 'DAY' then '' // not necessary
when '02' then case DateFuncStartRelativeOffset
when '02' then case DateFunctionStartYearOffset
when '01' then dats_add_days( LastYear.FirstDayOfWeekDate , 6 , 'FAIL' )
when '03' then dats_add_days( NextYear.FirstDayOfWeekDate , 6 , 'FAIL' )
else dats_add_days( CurrDate.FirstDayOfWeekDate , 6 , 'FAIL' ) end
when '01' then case DateFunctionStartYearOffset
when '01' then dats_add_days( LastYear.FirstDayOfWeekDate , -1 , 'FAIL') //last day previous week previous year
when '03' then dats_add_days( NextYear.FirstDayOfWeekDate , -1 , 'FAIL')
else dats_add_days( CurrDate.FirstDayOfWeekDate , -1 , 'FAIL') end
when '03' then case DateFunctionStartYearOffset
when '01' then dats_add_days( LastYear.FirstDayOfWeekDate , ((2 * 7 )- 1), 'FAIL')
when '03' then dats_add_days( NextYear.FirstDayOfWeekDate , ((2 * 7 )- 1), 'FAIL') //last day of next week next year
else dats_add_days( CurrDate.FirstDayOfWeekDate , ((2 * 7 )- 1), 'FAIL') end
when '04' then case DateFunctionStartYearOffset
when '01' then dats_add_days( LastYear.FirstDayOfWeekDate, ( cast( LastYear.CalendarWeek as abap.int2 ) -1 ) * -7 , 'FAIL' )
when '03' then dats_add_days( NextYear.FirstDayOfWeekDate, ( cast( NextYear.CalendarWeek as abap.int2 ) -1 ) * -7 , 'FAIL' )
else dats_add_days( CurrDate.FirstDayOfWeekDate, ( cast( CurrDate.CalendarWeek as abap.int2 ) -1 ) * -7 , 'FAIL' ) end
when '05' then case DateFunctionStartYearOffset
when '01' then dats_add_days( CurrDate.FirstDayOfWeekDate, ( (cast(CurrDate.CalendarWeek as int2) - 1) * -7 ) - 1 , 'FAIL')
when '03' then dats_add_days( cast( concat(CurrDate.CalendarYear , '1231' ) as abap.dats) , 8 - mod((cast( CurrDate.WeekDay as abap.int2) + mod( dats_days_between( CurrDate.CalendarDate , cast( concat( CurrDate.CalendarYear, '1231') as abap.dats ) ), 7)), 7 ) , 'FAIL' ) //last day last week next year
else dats_add_days( NextYear.FirstDayOfWeekDate, ( (cast(NextYear.CalendarWeek as int2) - 1) * -7 ) - 1 , 'FAIL') end
else CurrDate.CalendarDate end
when '03' then case DateFuncStartRelativeOffset
when '02' then case DateFunctionStartYearOffset
when '01' then dats_add_days( ( dats_add_months( LastYear.FirstDayOfMonthDate , 1 , 'FAIL' )) , -1 , 'FAIL' )
when '03' then dats_add_days( ( dats_add_months( NextYear.FirstDayOfMonthDate , 1 , 'FAIL' )) , -1 , 'FAIL' )
else dats_add_days( ( dats_add_months( CurrDate.FirstDayOfMonthDate , 1 , 'FAIL' )) , -1 , 'FAIL' ) end //last day of current month current year
when '01' then case DateFunctionStartYearOffset
when '01' then dats_add_days( LastYear.FirstDayOfMonthDate , -1 , 'FAIL')
when '03' then dats_add_days( NextYear.FirstDayOfMonthDate , -1 , 'FAIL')
else dats_add_days( CurrDate.FirstDayOfMonthDate , -1 , 'FAIL') end
when '03' then case DateFunctionStartYearOffset
when '01' then dats_add_days((dats_add_months( LastYear.FirstDayOfMonthDate , 2 , 'FAIL')) , -1 , 'FAIL')
when '03' then dats_add_days((dats_add_months( NextYear.FirstDayOfMonthDate , 2 , 'FAIL')) , -1 , 'FAIL')
else dats_add_days((dats_add_months( CurrDate.FirstDayOfMonthDate , 2 , 'FAIL')) , -1 , 'FAIL') end // last day of next month current year
when '04' then case DateFunctionStartYearOffset
when '01' then dats_add_days((dats_add_months( LastYear.FirstDayOfMonthDate, (cast(LastYear.CalendarMonth as abap.int4) - 2) * -1, 'FAIL')), -1 , 'FAIL' )
when '03' then dats_add_days((dats_add_months( NextYear.FirstDayOfMonthDate, (cast(NextYear.CalendarMonth as abap.int4) - 2) * -1, 'FAIL')), -1 , 'FAIL' ) // last day of first month next year
else dats_add_days((dats_add_months( CurrDate.FirstDayOfMonthDate, (cast(CurrDate.CalendarMonth as abap.int4) - 2) * -1, 'FAIL')), -1 , 'FAIL' ) end //last day first month current year end
when '05' then case DateFunctionStartYearOffset
when '01' then dats_add_days((dats_add_months( CurrDate.FirstDayOfMonthDate, (cast(CurrDate.CalendarMonth as abap.int4) - 1) * -1, 'FAIL')), -1 , 'FAIL' )
when '03' then dats_add_days( dats_add_months(NextYear.FirstDayOfMonthDate, (13 - cast( NextYear.CalendarMonth as abap.int4)), 'FAIL') , -1 , 'FAIL' )
else dats_add_days((dats_add_months( NextYear.FirstDayOfMonthDate, (cast(NextYear.CalendarMonth as abap.int4) - 1) * -1, 'FAIL')), -1 , 'FAIL' ) end
else CurrDate.CalendarDate end
when '04' then case DateFuncStartRelativeOffset
when '02' then case DateFunctionStartYearOffset
when '03' then case LastYear.CalendarQuarter // last day current quarter previous year
when '1' then cast( ( concat( LastYear.CalendarYear , '0331' ) ) as abap.dats)
when '2' then cast( ( concat( LastYear.CalendarYear , '0630' ) ) as abap.dats)
when '3' then cast( ( concat( LastYear.CalendarYear , '0930' ) ) as abap.dats)
else cast( ( concat( LastYear.CalendarYear , '1231' ) ) as abap.dats) end
when '03' then case NextYear.CalendarQuarter //last day current quarter next year
when '1' then cast( ( concat( NextYear.CalendarYear , '0331' ) ) as abap.dats)
when '2' then cast( ( concat( NextYear.CalendarYear , '0630' ) ) as abap.dats)
when '3' then cast( ( concat( NextYear.CalendarYear , '0930' ) ) as abap.dats)
else cast( ( concat( NextYear.CalendarYear , '1231' ) ) as abap.dats) end
else case CurrDate.CalendarQuarter // last day current quarter current year
when '1' then cast( ( concat( CurrDate.CalendarYear , '0331' ) ) as abap.dats)
when '2' then cast( ( concat( CurrDate.CalendarYear , '0630' ) ) as abap.dats)
when '3' then cast( ( concat( CurrDate.CalendarYear , '0930' ) ) as abap.dats)
else cast( ( concat( CurrDate.CalendarYear , '1231' ) ) as abap.dats) end
end
when '01' then case DateFunctionStartYearOffset
when '01' then case LastYear.CalendarQuarter // Previous
when '2' then cast( ( concat( LastYear.CalendarYear , '0331' ) ) as abap.dats)
when '3' then cast( ( concat( LastYear.CalendarYear , '0630' ) ) as abap.dats)
when '4' then cast( ( concat( LastYear.CalendarYear , '0930' ) ) as abap.dats)
else cast( concat( substring( dats_add_months( LastYear.CalendarDate , -12 , 'FAIL'), 1, 4) , '1231' ) as abap.dats) end
when '03' then case NextYear.CalendarQuarter // Next
when '2' then cast( ( concat( NextYear.CalendarYear , '0331' ) ) as abap.dats)
when '3' then cast( ( concat( NextYear.CalendarYear , '0630') ) as abap.dats)
when '4' then cast( ( concat( NextYear.CalendarYear , '0930') ) as abap.dats)
else cast( ( concat( CurrDate.CalendarYear , '1231') ) as abap.dats) end
else case CurrDate.CalendarQuarter //last Day of previous quarter current year
when '2' then cast( ( concat( CurrDate.CalendarYear , '0331' ) ) as abap.dats)
when '3' then cast( ( concat( CurrDate.CalendarYear , '0630') ) as abap.dats)
when '4' then cast( ( concat( CurrDate.CalendarYear , '0930') ) as abap.dats)
else cast( concat( LastYear.CalendarYear , '1231' ) as abap.dats ) end
end
when '03' then case DateFunctionStartYearOffset
when '01' then case LastYear.CalendarQuarter // last day next quarter previous year
when '1' then cast( ( concat( LastYear.CalendarYear , '0630' ) ) as abap.dats)
when '2' then cast( ( concat( LastYear.CalendarYear , '0930' ) ) as abap.dats)
when '3' then cast( ( concat( LastYear.CalendarYear , '1231' ) ) as abap.dats)
else cast( concat( CurrDate.CalendarYear , '0331' ) as abap.dats ) end
when '03' then case NextYear.CalendarQuarter //last day next quarter next year
when '1' then cast( ( concat( NextYear.CalendarYear , '0630' ) ) as abap.dats)
when '2' then cast( ( concat( NextYear.CalendarYear , '0930' ) ) as abap.dats)
when '3' then cast( ( concat( NextYear.CalendarYear , '1231' ) ) as abap.dats)
else cast( concat( substring( dats_add_months( NextYear.CalendarDate , 12 , 'FAIL') , 1, 4) , '0331' ) as abap.dats ) end
else case CurrDate.CalendarQuarter // last day next quarter current year
when '1' then cast( ( concat( CurrDate.CalendarYear , '0630' ) ) as abap.dats)
when '2' then cast( ( concat( CurrDate.CalendarYear , '0930' ) ) as abap.dats)
when '3' then cast( ( concat( CurrDate.CalendarYear , '1231' ) ) as abap.dats)
else cast( concat( NextYear.CalendarYear , '0331' ) as abap.dats ) end // last day next quarter current year
end
when '04' then case DateFunctionStartYearOffset
when '01' then cast( ( concat( LastYear.CalendarYear , '0131' ) ) as abap.dats)
when '03' then cast( ( concat( NextYear.CalendarYear , '0131' ) ) as abap.dats)
else cast( ( concat( CurrDate.CalendarYear , '0131' ) ) as abap.dats) end
when '05' then case DateFunctionStartYearOffset
when '01' then cast( ( concat( LastYear.CalendarYear , '1231' ) ) as abap.dats)
when '03' then cast( ( concat( NextYear.CalendarYear , '1231' ) ) as abap.dats)
else cast( ( concat( CurrDate.CalendarYear , '1231' ) ) as abap.dats) end
else CurrDate.CalendarDate end
// when 'YEAR' then '' // Year has separate field referenceyear
when '05' then case DateFunctionStartYearOffset
when '01' then dats_add_days((dats_add_months( CurrDate.FirstDayOfMonthDate, (cast(CurrDate.CalendarMonth as abap.int4) - 1) * -1, 'FAIL')), -1 , 'FAIL' )
when '03' then dats_add_days( dats_add_months(NextYear.FirstDayOfMonthDate, (13 - cast( NextYear.CalendarMonth as abap.int4)), 'FAIL') , -1 , 'FAIL' )
else dats_add_days((dats_add_months( NextYear.FirstDayOfMonthDate, (cast(NextYear.CalendarMonth as abap.int4) - 1) * -1, 'FAIL')), -1 , 'FAIL' ) end
else CurrDate.CalendarDate end
when DateFunctionStartPoint = '06' then case DateFunctionStartOffsetUnit // From Today
when '01' then dats_add_days( CurrDate.CalendarDate, DateFunctionStartOffsetValue, 'FAIL')
when '02' then dats_add_days( CurrDate.CalendarDate, DateFunctionStartOffsetValue * 7, 'FAIL')
when '03' then dats_add_months( CurrDate.CalendarDate, DateFunctionStartOffsetValue, 'FAIL')
when '04' then dats_add_months( CurrDate.CalendarDate, DateFunctionStartOffsetValue * 3, 'FAIL')
when '05' then dats_add_months( CurrDate.CalendarDate, DateFunctionStartOffsetValue * 12, 'FAIL')
else CurrDate.CalendarDate end
when DateFunctionStartPoint = '07' then DateFunctionStartFixedDate // Fixed
else CurrDate.CalendarDate end as DateFunctionStartPointDate,
//Reference Date Calculation
case DateFunctionType
when '02' then // Calculate when it is range. For Single return current date
case
when DateFunctionEndPoint = '02' then CurrDate.CalendarDate
when DateFunctionEndPoint = '03' then dats_add_days(CalDate.CalendarDate, 1 , 'FAIL' )
when DateFunctionEndPoint = '01' then dats_add_days(CalDate.CalendarDate, -1 , 'FAIL' )
when DateFunctionEndPoint = '04' or DateFunctionEndPoint = '08' then case DateFunctionEndRelOffsetUnit
// when 'DAY' then '' // not necessary
when '02' then case DateFunctionEndRelativeOffset
when '02' then case DateFunctionEndYearOffset
when '01' then LastYear.FirstDayOfWeekDate
when '03' then NextYear.FirstDayOfWeekDate
else CurrDate.FirstDayOfWeekDate end
when '01' then case DateFunctionEndYearOffset
when '01' then dats_add_days( LastYear.FirstDayOfWeekDate, -7, 'FAIL')
when '03' then dats_add_days( NextYear.FirstDayOfWeekDate, -7, 'FAIL')
else dats_add_days( CurrDate.FirstDayOfWeekDate, -7, 'FAIL') end
when '03' then case DateFunctionEndYearOffset
when '01' then dats_add_days( LastYear.FirstDayOfWeekDate, 7, 'FAIL')
when '03' then dats_add_days( NextYear.FirstDayOfWeekDate, 7, 'FAIL')
else dats_add_days( CurrDate.FirstDayOfWeekDate, 7, 'FAIL') end // next week current year end
when '04' then case DateFunctionEndYearOffset
when '01' then dats_add_days( LastYear.FirstDayOfWeekDate, ( (cast(LastYear.CalendarWeek as int2) - 1) * -7 ), 'FAIL') //first day of first week previous year
when '03' then dats_add_days( NextYear.FirstDayOfWeekDate, ( (cast(NextYear.CalendarWeek as int2) - 1) * -7 ), 'FAIL') //first day of first week next year
else dats_add_days( CurrDate.FirstDayOfWeekDate, ( (cast(CurrDate.CalendarWeek as int2) - 1) * -7 ), 'FAIL') end
when '05' then case DateFunctionEndYearOffset
when '01' then dats_add_days( CurrDate.FirstDayOfWeekDate, ( (cast(CurrDate.CalendarWeek as int2) ) * -7 ) , 'FAIL')
when '03' then dats_add_days( cast( concat(NextYear.CalendarYear , '1231' ) as abap.dats) , (mod((cast( NextYear.WeekDay as abap.int2) + mod( dats_days_between( NextYear.CalendarDate , cast( concat( NextYear.CalendarYear, '1231') as abap.dats ) ), 7)), 7 ) * -1) + 1, 'FAIL' ) //first day of last week next year
else dats_add_days( NextYear.FirstDayOfWeekDate, ( (cast(NextYear.CalendarWeek as int2) ) * -7 ), 'FAIL') end
else CurrDate.CalendarDate end
when '03' then case DateFunctionEndRelativeOffset
when '02' then case DateFunctionEndYearOffset
when '01' then LastYear.FirstDayOfMonthDate
when '03' then NextYear.FirstDayOfMonthDate
else CurrDate.FirstDayOfMonthDate end
when '01' then case DateFunctionEndYearOffset
when '01' then dats_add_months( CurrDate.FirstDayOfMonthDate, -13, 'FAIL')
when '03' then dats_add_months( CurrDate.FirstDayOfMonthDate, 11, 'FAIL')
else dats_add_months( CurrDate.FirstDayOfMonthDate, -1, 'FAIL') end
when '03' then case DateFunctionEndYearOffset
when '01' then dats_add_months( CurrDate.FirstDayOfMonthDate, -11, 'FAIL')
when '03' then dats_add_months( CurrDate.FirstDayOfMonthDate, 13, 'FAIL')
else dats_add_months( CurrDate.FirstDayOfMonthDate, 1, 'FAIL') end
when '04' then case DateFunctionEndYearOffset
when '01' then dats_add_months( LastYear.FirstDayOfMonthDate, ( cast(LastYear.CalendarMonth as abap.int4) -1 )* -1, 'FAIL')
when '03' then dats_add_months( NextYear.FirstDayOfMonthDate, ( cast(NextYear.CalendarMonth as abap.int4) - 1 ) * -1, 'FAIL')
else dats_add_months( CurrDate.FirstDayOfMonthDate, (cast(CurrDate.CalendarMonth as abap.int4) - 1 ) * -1, 'FAIL') end
when '05' then case DateFunctionEndYearOffset
when '01' then dats_add_months( LastYear.FirstDayOfMonthDate, (12 - cast(LastYear.CalendarMonth as abap.int4) ), 'FAIL')
when '03' then dats_add_months( NextYear.FirstDayOfMonthDate, (12 - cast(NextYear.CalendarMonth as abap.int4) ), 'FAIL')
else dats_add_months( CurrDate.FirstDayOfMonthDate, (12 - cast(CurrDate.CalendarMonth as abap.int4) ), 'FAIL') end
else CurrDate.CalendarDate end
when '04' then case DateFunctionEndRelativeOffset
when '02' then case DateFunctionEndYearOffset
when '01' then case LastYear.CalendarQuarter
when '1' then cast( ( concat( LastYear.CalendarYear , '0101' ) ) as abap.dats)
when '2' then cast( ( concat( LastYear.CalendarYear , '0401' ) ) as abap.dats)
when '3' then cast( ( concat( LastYear.CalendarYear , '0701' ) ) as abap.dats)
else cast( ( concat( LastYear.CalendarYear , '1001' ) ) as abap.dats) end
when '03' then case NextYear.CalendarQuarter
when '1' then cast( ( concat( NextYear.CalendarYear , '0101' ) ) as abap.dats)
when '2' then cast( ( concat( NextYear.CalendarYear , '0401' ) ) as abap.dats)
when '3' then cast( ( concat( NextYear.CalendarYear , '0701' ) ) as abap.dats)
else cast( ( concat( NextYear.CalendarYear , '1001' ) ) as abap.dats) end
else case CurrDate.CalendarQuarter
when '1' then cast( ( concat( CurrDate.CalendarYear , '0101' ) ) as abap.dats)
when '2' then cast( ( concat( CurrDate.CalendarYear , '0401' ) ) as abap.dats)
when '3' then cast( ( concat( CurrDate.CalendarYear , '0701' ) ) as abap.dats)
else cast( ( concat( CurrDate.CalendarYear , '1001' ) ) as abap.dats) end
end
when '01' then case DateFunctionEndYearOffset
when '01' then case LastYear.CalendarQuarter
when '2' then cast( ( concat( LastYear.CalendarYear , '0101' ) ) as abap.dats)
when '3' then cast( ( concat( LastYear.CalendarYear , '0401' ) ) as abap.dats)
when '4' then cast( ( concat( LastYear.CalendarYear , '0701' ) ) as abap.dats)
else cast( concat( substring( dats_add_months( LastYear.CalendarDate , -12 , 'FAIL'), 1, 4) , '1001' ) as abap.dats) end
when '03' then case NextYear.CalendarQuarter
when '2' then cast( ( concat( NextYear.CalendarYear , '0101' ) ) as abap.dats)
when '3' then cast( ( concat( NextYear.CalendarYear , '0401') ) as abap.dats)
when '4' then cast( ( concat( NextYear.CalendarYear , '0701') ) as abap.dats)
else cast( ( concat( CurrDate.CalendarYear , '1001') ) as abap.dats) end
else case CurrDate.CalendarQuarter
when '2' then cast( ( concat( CurrDate.CalendarYear , '0101' ) ) as abap.dats)
when '3' then cast( ( concat( CurrDate.CalendarYear , '0401') ) as abap.dats)
when '4' then cast( ( concat( CurrDate.CalendarYear , '0701') ) as abap.dats)
else cast( concat( LastYear.CalendarYear , '1001' ) as abap.dats ) end
end
when '03' then case DateFunctionEndYearOffset
when '01' then case LastYear.CalendarQuarter
when '1' then cast( ( concat( LastYear.CalendarYear , '0401' ) ) as abap.dats)
when '2' then cast( ( concat( LastYear.CalendarYear , '0701' ) ) as abap.dats)
when '3' then cast( ( concat( LastYear.CalendarYear , '1001' ) ) as abap.dats)
else cast( concat( CurrDate.CalendarYear , '0101' ) as abap.dats ) end
when '01' then case NextYear.CalendarQuarter
when '1' then cast( ( concat( NextYear.CalendarYear , '0401' ) ) as abap.dats)
when '2' then cast( ( concat( NextYear.CalendarYear , '0701' ) ) as abap.dats)
when '3' then cast( ( concat( NextYear.CalendarYear , '1001' ) ) as abap.dats)
else cast( concat( substring( dats_add_months( NextYear.CalendarDate , 12 , 'FAIL') , 1, 4) , '0101' ) as abap.dats ) end
else case CurrDate.CalendarQuarter
when '1' then cast( ( concat( CurrDate.CalendarYear , '0401' ) ) as abap.dats)
when '2' then cast( ( concat( CurrDate.CalendarYear , '0701' ) ) as abap.dats)
when '3' then cast( ( concat( CurrDate.CalendarYear , '1001' ) ) as abap.dats)
else cast( concat( NextYear.CalendarYear , '0101' ) as abap.dats ) end
end
when '04' then case DateFunctionEndYearOffset
when '01' then cast( ( concat( LastYear.CalendarYear , '0101' ) ) as abap.dats)
when '03' then cast( ( concat( NextYear.CalendarYear , '0101' ) ) as abap.dats)
else cast( ( concat( CurrDate.CalendarYear , '0101' ) ) as abap.dats) end
when '05' then case DateFunctionEndYearOffset
when '01' then cast( ( concat( LastYear.CalendarYear , '1201' ) ) as abap.dats)
when '03' then cast( ( concat( NextYear.CalendarYear , '1201' ) ) as abap.dats)
else cast( ( concat( CurrDate.CalendarYear , '1201' ) ) as abap.dats) end
else CurrDate.CalendarDate end
// when 'YEAR' then '' // Year has separate field referenceyear
when '05' then case DateFunctionEndYearOffset
when '01' then dats_add_months( LastYear.FirstDayOfMonthDate, ( cast(LastYear.CalendarMonth as abap.int4) -1 )* -1, 'FAIL')
when '03' then dats_add_months( NextYear.FirstDayOfMonthDate, ( cast(NextYear.CalendarMonth as abap.int4) - 1 ) * -1, 'FAIL')
else dats_add_months( CurrDate.FirstDayOfMonthDate, (cast(CurrDate.CalendarMonth as abap.int4) - 1 ) * -1, 'FAIL') end
else CurrDate.CalendarDate end
when DateFunctionEndPoint = '05' or DateFunctionEndPoint = '09' then case DateFunctionEndRelOffsetUnit
// when 'DAY' then '' // not necessary
when '02' then case DateFunctionEndRelativeOffset
when '02' then case DateFunctionEndYearOffset
when '01' then dats_add_days( LastYear.FirstDayOfWeekDate , 6 , 'FAIL' )
when '03' then dats_add_days( NextYear.FirstDayOfWeekDate , 6 , 'FAIL' )
else dats_add_days( CurrDate.FirstDayOfWeekDate , 6 , 'FAIL' ) end
when '01' then case DateFunctionEndYearOffset
when '01' then dats_add_days( LastYear.FirstDayOfWeekDate , -1 , 'FAIL') //last day previous week previous year
when '03' then dats_add_days( NextYear.FirstDayOfWeekDate , -1 , 'FAIL')
else dats_add_days( CurrDate.FirstDayOfWeekDate , -1 , 'FAIL') end
when '03' then case DateFunctionEndYearOffset
when '01' then dats_add_days( LastYear.FirstDayOfWeekDate , ((2 * 7 )- 1), 'FAIL')
when '03' then dats_add_days( NextYear.FirstDayOfWeekDate , ((2 * 7 )- 1), 'FAIL') //last day of next week next year
else dats_add_days( CurrDate.FirstDayOfWeekDate , ((2 * 7 )- 1), 'FAIL') end
when '04' then case DateFunctionEndYearOffset
when '01' then dats_add_days( LastYear.FirstDayOfWeekDate, ( cast( LastYear.CalendarWeek as abap.int2 ) -1 ) * -7 , 'FAIL' )
when '03' then dats_add_days( NextYear.FirstDayOfWeekDate, ( cast( NextYear.CalendarWeek as abap.int2 ) -1 ) * -7 , 'FAIL' )
else dats_add_days( CurrDate.FirstDayOfWeekDate, ( cast( CurrDate.CalendarWeek as abap.int2 ) -1 ) * -7 , 'FAIL' ) end
when '05' then case DateFunctionEndYearOffset
when '01' then dats_add_days( CurrDate.FirstDayOfWeekDate, ( (cast(CurrDate.CalendarWeek as int2) - 1) * -7 ) - 1 , 'FAIL')
when '03' then dats_add_days( cast( concat(CurrDate.CalendarYear , '1231' ) as abap.dats) , 8 - mod((cast( CurrDate.WeekDay as abap.int2) + mod( dats_days_between( CurrDate.CalendarDate , cast( concat( CurrDate.CalendarYear, '1231') as abap.dats ) ), 7)), 7 ) , 'FAIL' ) //last day last week next year
else dats_add_days( NextYear.FirstDayOfWeekDate, ( (cast(NextYear.CalendarWeek as int2) - 1) * -7 ) - 1 , 'FAIL') end
else CurrDate.CalendarDate end
when '03' then case DateFunctionEndRelativeOffset
when '02' then case DateFunctionEndYearOffset
when '01' then dats_add_days( ( dats_add_months( LastYear.FirstDayOfMonthDate , 1 , 'FAIL' )) , -1 , 'FAIL' )
when '03' then dats_add_days( ( dats_add_months( NextYear.FirstDayOfMonthDate , 1 , 'FAIL' )) , -1 , 'FAIL' )
else dats_add_days( ( dats_add_months( CurrDate.FirstDayOfMonthDate , 1 , 'FAIL' )) , -1 , 'FAIL' ) end //last day of current month current year
when '01' then case DateFunctionEndYearOffset
when '01' then dats_add_days( LastYear.FirstDayOfMonthDate , -1 , 'FAIL')
when '03' then dats_add_days( NextYear.FirstDayOfMonthDate , -1 , 'FAIL')
else dats_add_days( CurrDate.FirstDayOfMonthDate , -1 , 'FAIL') end
when '03' then case DateFunctionEndYearOffset
when '01' then dats_add_days((dats_add_months( LastYear.FirstDayOfMonthDate , 2 , 'FAIL')) , -1 , 'FAIL')
when '03' then dats_add_days((dats_add_months( NextYear.FirstDayOfMonthDate , 2 , 'FAIL')) , -1 , 'FAIL')
else dats_add_days((dats_add_months( CurrDate.FirstDayOfMonthDate , 2 , 'FAIL')) , -1 , 'FAIL') end // last day of next month current year
when '04' then case DateFunctionEndYearOffset
when '01' then dats_add_days((dats_add_months( LastYear.FirstDayOfMonthDate, (cast(LastYear.CalendarMonth as abap.int4) - 2) * -1, 'FAIL')), -1 , 'FAIL' )
when '03' then dats_add_days((dats_add_months( NextYear.FirstDayOfMonthDate, (cast(NextYear.CalendarMonth as abap.int4) - 2) * -1, 'FAIL')), -1 , 'FAIL' ) // last day of first month next year
else dats_add_days((dats_add_months( CurrDate.FirstDayOfMonthDate, (cast(CurrDate.CalendarMonth as abap.int4) - 2) * -1, 'FAIL')), -1 , 'FAIL' ) end //last day first month current year end
when '05' then case DateFunctionEndYearOffset
when '01' then dats_add_days((dats_add_months( CurrDate.FirstDayOfMonthDate, (cast(CurrDate.CalendarMonth as abap.int4) - 1) * -1, 'FAIL')), -1 , 'FAIL' )
when '03' then dats_add_days( dats_add_months(NextYear.FirstDayOfMonthDate, (13 - cast( NextYear.CalendarMonth as abap.int4)), 'FAIL') , -1 , 'FAIL' )
else dats_add_days((dats_add_months( NextYear.FirstDayOfMonthDate, (cast(NextYear.CalendarMonth as abap.int4) - 1) * -1, 'FAIL')), -1 , 'FAIL' ) end
else CurrDate.CalendarDate end
when '04' then case DateFunctionEndRelativeOffset
when '02' then case DateFunctionEndYearOffset
when '03' then case LastYear.CalendarQuarter // last day current quarter previous year
when '1' then cast( ( concat( LastYear.CalendarYear , '0331' ) ) as abap.dats)
when '2' then cast( ( concat( LastYear.CalendarYear , '0630' ) ) as abap.dats)
when '3' then cast( ( concat( LastYear.CalendarYear , '0930' ) ) as abap.dats)
else cast( ( concat( LastYear.CalendarYear , '1231' ) ) as abap.dats) end
when '03' then case NextYear.CalendarQuarter //last day current quarter next year
when '1' then cast( ( concat( NextYear.CalendarYear , '0331' ) ) as abap.dats)
when '2' then cast( ( concat( NextYear.CalendarYear , '0630' ) ) as abap.dats)
when '3' then cast( ( concat( NextYear.CalendarYear , '0930' ) ) as abap.dats)
else cast( ( concat( NextYear.CalendarYear , '1231' ) ) as abap.dats) end
else case CurrDate.CalendarQuarter // last day current quarter current year
when '1' then cast( ( concat( CurrDate.CalendarYear , '0331' ) ) as abap.dats)
when '2' then cast( ( concat( CurrDate.CalendarYear , '0630' ) ) as abap.dats)
when '3' then cast( ( concat( CurrDate.CalendarYear , '0930' ) ) as abap.dats)
else cast( ( concat( CurrDate.CalendarYear , '1231' ) ) as abap.dats) end
end
when '01' then case DateFunctionEndYearOffset
when '01' then case LastYear.CalendarQuarter
when '2' then cast( ( concat( LastYear.CalendarYear , '0331' ) ) as abap.dats)
when '3' then cast( ( concat( LastYear.CalendarYear , '0630' ) ) as abap.dats)
when '4' then cast( ( concat( LastYear.CalendarYear , '0930' ) ) as abap.dats)
else cast( concat( substring( dats_add_months( LastYear.CalendarDate , -12 , 'FAIL'), 1, 4) , '1231' ) as abap.dats) end
when '03' then case NextYear.CalendarQuarter
when '2' then cast( ( concat( NextYear.CalendarYear , '0331' ) ) as abap.dats)
when '3' then cast( ( concat( NextYear.CalendarYear , '0630') ) as abap.dats)
when '4' then cast( ( concat( NextYear.CalendarYear , '0930') ) as abap.dats)
else cast( ( concat( CurrDate.CalendarYear , '1231') ) as abap.dats) end
else case CurrDate.CalendarQuarter //last Day of previous quarter current year
when '2' then cast( ( concat( CurrDate.CalendarYear , '0331' ) ) as abap.dats)
when '3' then cast( ( concat( CurrDate.CalendarYear , '0630') ) as abap.dats)
when '4' then cast( ( concat( CurrDate.CalendarYear , '0930') ) as abap.dats)
else cast( concat( LastYear.CalendarYear , '1231' ) as abap.dats ) end
end
when '03' then case DateFunctionEndYearOffset
when '01' then case LastYear.CalendarQuarter // last day next quarter previous year
when '1' then cast( ( concat( LastYear.CalendarYear , '0630' ) ) as abap.dats)
when '2' then cast( ( concat( LastYear.CalendarYear , '0930' ) ) as abap.dats)
when '3' then cast( ( concat( LastYear.CalendarYear , '1231' ) ) as abap.dats)
else cast( concat( CurrDate.CalendarYear , '0331' ) as abap.dats ) end
when '03' then case NextYear.CalendarQuarter //last day next quarter next year
when '1' then cast( ( concat( NextYear.CalendarYear , '0630' ) ) as abap.dats)
when '2' then cast( ( concat( NextYear.CalendarYear , '0930' ) ) as abap.dats)
when '3' then cast( ( concat( NextYear.CalendarYear , '1231' ) ) as abap.dats)
else cast( concat( substring( dats_add_months( NextYear.CalendarDate , 12 , 'FAIL') , 1, 4) , '0331' ) as abap.dats ) end
else case CurrDate.CalendarQuarter // last day next quarter current year
when '1' then cast( ( concat( CurrDate.CalendarYear , '0630' ) ) as abap.dats)
when '2' then cast( ( concat( CurrDate.CalendarYear , '0930' ) ) as abap.dats)
when '3' then cast( ( concat( CurrDate.CalendarYear , '1231' ) ) as abap.dats)
else cast( concat( NextYear.CalendarYear , '0331' ) as abap.dats ) end // last day next quarter current year
end
when '04' then case DateFunctionEndYearOffset
when '01' then cast( ( concat( LastYear.CalendarYear , '0131' ) ) as abap.dats)
when '03' then cast( ( concat( NextYear.CalendarYear , '0131' ) ) as abap.dats)
else cast( ( concat( CurrDate.CalendarYear , '0131' ) ) as abap.dats) end
when '05' then case DateFunctionEndYearOffset
when '01' then cast( ( concat( LastYear.CalendarYear , '1231' ) ) as abap.dats)
when '03' then cast( ( concat( NextYear.CalendarYear , '1231' ) ) as abap.dats)
else cast( ( concat( CurrDate.CalendarYear , '1231' ) ) as abap.dats) end
else CurrDate.CalendarDate end
// when 'YEAR' then '' // Year has separate field referenceyear
when '05' then case DateFunctionEndYearOffset
when '01' then dats_add_days((dats_add_months( CurrDate.FirstDayOfMonthDate, (cast(CurrDate.CalendarMonth as abap.int4) - 1) * -1, 'FAIL')), -1 , 'FAIL' )
when '03' then dats_add_days( dats_add_months(NextYear.FirstDayOfMonthDate, (13 - cast( NextYear.CalendarMonth as abap.int4)), 'FAIL') , -1 , 'FAIL' )
else dats_add_days((dats_add_months( NextYear.FirstDayOfMonthDate, (cast(NextYear.CalendarMonth as abap.int4) - 1) * -1, 'FAIL')), -1 , 'FAIL' ) end
else CurrDate.CalendarDate end
when DateFunctionEndPoint = '06' then case DateFunctionEndOffsetUnit
when '01' then dats_add_days( CurrDate.CalendarDate, DateFunctionEndOffsetValue, 'FAIL')
when '02' then dats_add_days( CurrDate.CalendarDate, DateFunctionEndOffsetValue * 7, 'FAIL')
when '03' then dats_add_months( CurrDate.CalendarDate, DateFunctionEndOffsetValue, 'FAIL')
when '04' then dats_add_months( CurrDate.CalendarDate, DateFunctionEndOffsetValue * 3, 'FAIL')
when '05' then dats_add_months( CurrDate.CalendarDate, DateFunctionEndOffsetValue * 12, 'FAIL')
else CurrDate.CalendarDate end
when DateFunctionEndPoint = '07' then DateFunctionEndFixedDate
else CurrDate.CalendarDate end
else CurrDate.CalendarDate end as DateFunctionEndPointDate
} where DynamicDate.DateFunctionCalendarType = '01'