@AbapCatalog.sqlViewName: 'DTFCONFIGSIM'
@EndUserText.label: 'Non-Persisted DateFunction Configuration Simulation'
@ObjectModel.usageType.serviceQuality: #P
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view DATEFUNC_CONFIG_SIMULATE
with parameters
P_DateFunctionType : datefunctiontype,
P_DateFunctionGranularity : datefunctiongranularity,
P_DateFunctionCalendarType : datefunctioncalendartype,
P_DateFunctionStartPoint : datefunctionstartpoint,
P_DateFunctionStartOffsetValue : datefunctionstartoffsetvalue,
P_DateFuncStartRelativeOffset : datefunctionstartreloffset,
P_DateFunctionStartOffsetUnit : datefunctionstartoffsetunit,
P_DateFuncStartRelOffsetUnit : datefunctionstartreloffsetunit,
P_DateFunctionStartYearOffset : datefunctionstartyearoffset,
P_DateFunctionStartFixedDate : datefunctionstartfixeddate,
P_DateFunctionEndPoint : datefunctionendpoint,
P_DateFunctionEndOffsetValue : datefunctionendoffsetvalue,
P_DateFunctionEndRelOffset : datefunctionendreloffset,
P_DateFunctionEndOffsetUnit : datefunctionendoffsetunit,
P_DateFuncEndRelOffsetUnit : datefunctionendreloffsetunit,
P_DateFunctionEndYearOffset : datefunctionendyearoffset,
P_DateFunctionEndFixedDate : datefunctionendfixeddate
as select from CURRENTCALENDARDATE( P_DateFunctionType: $parameters.P_DateFunctionType,
P_DateFunctionGranularity: $parameters.P_DateFunctionGranularity,
P_DateFunctionCalendarType: $parameters.P_DateFunctionCalendarType,
P_DateFunctionStartPoint: $parameters.P_DateFunctionStartPoint,
P_DateFunctionStartOffsetValue: $parameters.P_DateFunctionStartOffsetValue,
P_DateFuncStartRelativeOffset: $parameters.P_DateFuncStartRelativeOffset,
P_DateFunctionStartOffsetUnit: $parameters.P_DateFunctionStartOffsetUnit,
P_DateFuncStartRelOffsetUnit : $parameters.P_DateFuncStartRelOffsetUnit,
P_DateFunctionStartYearOffset: $parameters.P_DateFunctionStartYearOffset,
P_DateFunctionStartFixedDate: $parameters.P_DateFunctionStartFixedDate,
P_DateFunctionEndPoint: $parameters.P_DateFunctionEndPoint,
P_DateFunctionEndOffsetValue: $parameters.P_DateFunctionEndOffsetValue,
P_DateFunctionEndRelOffset: $parameters.P_DateFunctionEndRelOffset,
P_DateFunctionEndOffsetUnit: $parameters.P_DateFunctionEndOffsetUnit,
P_DateFuncEndRelOffsetUnit : $parameters.P_DateFuncEndRelOffsetUnit,
P_DateFunctionEndYearOffset: $parameters.P_DateFunctionEndYearOffset,
P_DateFunctionEndFixedDate: $parameters.P_DateFunctionEndFixedDate) as CurrDate
inner join P_Referencedates(P_DateFunctionValidityDate : $session.system_date) as CalDate on CalDate.CalendarDate = $session.system_date
inner join I_CalendarDate as LastYear on CalDate.LastYearDate = LastYear.CalendarDate
inner join I_CalendarDate as NextYear on CalDate.NextYearDate = NextYear.CalendarDate {
1 as ID,
DateFunctionType,
DateFunctionGranularity,
DateFunctionCalendarType,
DateFunctionStartPoint,
DateFunctionStartOffsetValue,
DateFuncStartRelativeOffset,
DateFunctionStartOffsetUnit,
DateFunctionStartRelOffsetUnit,
DateFunctionStartYearOffset,
DateFunctionStartFixedDate,
DateFunctionEndPoint,
DateFunctionEndOffsetValue,
DateFunctionEndRelOffset,
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