HANA script to substitute ABAP Function Module LAST_DAY_IN_PERIOD_GET

Hello There!

Pretext :

The more we lean towards AMDP based HANA transformations compared to good old ABAP these days, the more we feel the need of having substitutes of reusable standard ABAP function modules & programs. One such instance is LAST_DAY_IN_PERIOD_GET. While we can hope for a readymade HANA function or procedure developed & delivered by SAP with the content, we might already need to make some work arounds to keep the show going on. I have seen this question multiple times in several forums regarding how to replicate this ABAP function module into SQL script in HANA, so that we can consume this in AMDPs or from HANA. Through this blog post, I have tried to address this requirement to certain workable level as of now. The platforms I have used for these developments are HANA 2.0 SP05 & BW/4HANA 2.0.

Solution : 

The function module LAST_DAY_IN_PERIOD_GET basically returns the last date (E_DATE) within a fiscal period based on a combination of passed fiscal year variant(I_PERIV), fiscal year(I_GJAHR) & fiscal period(I_POPER). I have considered I_MONMIT to hold the default value (ie. 00) according to the case requirement I have, however a script modification is needed if any other value is needed to be passed on through I_MONMIT parameter.

Note : There can be different last date for same fiscal year & same period for different fiscal year variant. Fx. different PERIV values such as ‘K4’, ‘V3′,’24’ will have different E_DATE returned for same GJAHR & POPER. 

The calculations are mainly based on 2 standard tables, namely T009 & T009B. I have just tried to reuse the information available in these 2 tables in a reusable SQL procedure, which can be called from other HANA based framework within HANA or BW/4HANA.

Let’s get straight into the coding part. I have inserted the complete procedural script below, so it can be just plugged, played & tested in your systems 😉 (remember to replace <db_schema> with the schema name for your system (fx. SAPSR2 or SAPSR3 usually)).

create procedure "lastday_in_fiscper" 
( in
    v_year varchar(4),v_period varchar(3),v_variant varchar(2),
 out
    v_lastdat nvarchar(8)
 ) 
	language sqlscript
	sql security invoker 
	reads sql data as
begin

declare v_dat date;
declare v_xcal varchar(1);
declare v_clnt varchar(30);
declare v_month varchar(2);
declare v_day varchar(2);

select distinct mandt into v_clnt from <db_schema>.T000 where logsys <> '';
select xkale into v_xcal from <db_schema>.T009 where mandt = :v_clnt and periv = :v_variant;

if :v_xcal = 'X'
   then
   v_month := :v_period + 1;
   v_month := lpad(:v_month,2,'0');
   select
   case when :v_month <= 12 then add_days(:v_year||:v_month||'01', -1) 
   else :v_year||'1231' end into v_dat from dummy;
else
   select distinct to_varchar(:v_year + (-1)*reljr) into v_year from <db_schema>.T009B
   where periv = :v_variant and poper = :v_period and mandt = :v_clnt;
   select distinct bumon into v_month from <db_schema>.T009B 
   where periv = :v_variant and poper = :v_period and mandt = :v_clnt;
   select distinct butag into v_day from <db_schema>.T009B 
   where periv = :v_variant and poper = :v_period and mandt = :v_clnt;
   if :v_month||:v_day = '0229'
      then
      v_month := :v_month + 1;
      v_month := lpad(:v_month,2,'0');
      select add_days(:v_year||:v_month||'01', -1) into v_dat from dummy;
   else
      select :v_year||:v_month||:v_day into v_dat from dummy;    
   end if;
  
end if;

select to_varchar(:v_dat,'YYYYMMDD') into v_lastdat from dummy;

end;

Once this procedure is activated, it can be called from an AMDP within BW transformation or in other means. It can also be tested in HANA editor like following :

call "lastday_in_fiscper" ('2022','001','24',?);

And as you can see it matches with the output e_date showed in screenshot from the ABAP function module too. This procedure works & I have not found any fault in my test cases so far.

However, I would really appreciate if you are trying this solution out, please validate as much as you can. There might also be scopes for improvement in the script. Feel free to let me know here if you find any loophole, I would try to address it & it would help enriching other readers too. 🙂 Meanwhile like you, I will also wait for SAP to deliver ready-made work around solutions to such important reusuable ABAP FMs in HANA .
Until then, I hope this would ease up some of your quests.

Cheers,
Abhi