Skip to Content
Technical Articles
Author's profile photo Abhishek Hazra

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

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Jörg Brandeis
      Jörg Brandeis

      Hi Abhi,

      this is really a good substitution for the Function Module LAST_DAY_IN_PERIOD_GET. I have one small suggestion to improve it. When you make a function out of it you could integrate the logic even better into SELECT queries.

      But in a szenario for mass data, e.g. in the question of this blogpost  I would advise an other solution.

      Because the procedure is using imperative SQLScript and it is working on a record level. This is not the strength of SQL. The procedure has to be executed for each single line and the execution could not be parallized.

      I have good experience with precalculation of such logic, persist it in a table and JOIN it at runtime. This is described with more details in this comment: https://answers.sap.com/answers/13619099/view.html

      Regards,

      Jörg

       

      Author's profile photo Abhishek Hazra
      Abhishek Hazra
      Blog Post Author

      You're spot on Jörg & many thanks for responding with the improvement idea. For mass processing of data with different fiscal periods, this indeed isn't the optimal way of utilising the HANA SQL advantages. And your suggestion of using the ABAP FM to in a load to persist the data to another table & joining it in runtime would be way faster & closest solution to substitute the ABAP FM usage. I would vote for that 🙂

      However, about the DateTo & DateFrom attributes of 0FISCPER, I have something to add. Those were my first try too, but I could not see these attribute values updated by default in our system. Not sure if we need to do something manually, but that would be interesting to see too.

       

      Best Regards,
      Abhi