Function to get the correct end of month for SAP dates

[former_member455990]

Hi All,

This function is used to get the correct end of month for SAP dates.
As  one of a SAP table  has populated the date like 20190931, to correct these type of incorrect month end dates by using the the below function.

1. I will pass the SAP input date through parameter as input for the function.


2. I will convert that int date(YYYYMMDD) to varchar(10).


3. Then I will split YYYYMMDD to YYYY,MM,DD. using substring function(substr('YYYYMMDD,'1,4) as year,substr('YYYYMMDD',5,2) month, substr('YYYYMMDD',7,2) Day.


4. I will con-cat substr('YYYYMMDD,'1,4) ,,substr('YYYYMMDD',5,2),01 as and convert YYYYMMDD(ex:20190901) to date./


5. I will use last day  to get the last day of month


6. I will get the day after calculating last day of month.


7. Will Use decode function like given date day> the calculated last day of month then I will concat the exact month end day to  substr('YYYYMMDD,'1,4) ,substr('YYYYMMDD',5,2), else populate the same input.


8. i am converting the return type to int, because the most of sap YYYYMMDD formats are in int only.

Variables,parameters and datatypes:

{

$P_IP_DT int,
$LV_OP_DT varchar(10)
$LV_SAP_DATE_CHAR varchar(10);

Script of the function:
$LV_SAP_DATE_CHAR=cast($P_IP_DT,'varchar(10)');
$LV_OP_DT=decode(substr($LV_SAP_DATE_CHAR,7,2)>
day_in_month(last_date(to_date(cast(substr($LV_SAP_DATE_CHAR,1,4) ,'varchar(10)')||cast(substr( $LV_SAP_DATE_CHAR,5,2) ,'varchar(10)') ||'01','YYYYMMDD'))),
cast(substr($LV_SAP_DATE_CHAR,1,4) ,'varchar(10)')||cast(substr( $LV_SAP_DATE_CHAR,5,2) ,'varchar(10)') ||
cast(day_in_month(last_date(to_date(cast(substr($LV_SAP_DATE_CHAR,1,4) ,'varchar(10)')||cast(substr( $LV_SAP_DATE_CHAR,5,2) ,'varchar(10)') ||'01','YYYYMMDD'))),'varchar(10)')
,$LV_SAP_DATE_CHAR);
Return cast($LV_OP_DT,'int');

}

 

Function in BODS



 

 

Example:

In MATDOC table there is a VFDAT field has date format in YYYYMMDD, and is in integer.

We are using this field to calculate the date difference between current day and VFDAT should be less than 30 for our requirement. We got the date from SAP for VFDAT for one of the incremental flow as 20210931.

So the job failed due to date-time value out of range. We faced this situation multiple times. so We plan to update and correct month end date for any incorrect month end values in VFDAT. if its correct then it will use the same value, what ever we are getting it from MATDOC,If its incorrect then we will populate the correct month end date

Created  test job to see the results of the function output:

I have taken some incorrect month end,correct month end and month starting values as an example to show the function output.

Job:



Script in the Job:

{

$v_sap_ip_dt int:

$v_sap_ip_dt=20190931;
print('incorrect month end sap date $v_sap_ip_dt:'||$v_sap_ip_dt);

print(CF_SAPEOMTOEOM($v_sap_ip_dt));

$v_sap_ip_dt=20190901;
print('correct month start sap date $v_sap_ip_dt:'||$v_sap_ip_dt);

print(CF_SAPEOMTOEOM($v_sap_ip_dt));

$v_sap_ip_dt=20190731;
print('correct month end sap date $v_sap_ip_dt:'||$v_sap_ip_dt);

print(CF_SAPEOMTOEOM($v_sap_ip_dt));

}

 

Output for the function for the above examples:

• The below trace log we can see the incorrect month end value of 20190931 will be correct and written as 20190930.


• For correct month starting date value 20190901 will be same as given 20190901.


• For correct month end date value 20190731 will be same as given 20190731.

 

• 

We can use this function to correct in correct month end dates to correct month end dates for sap dates which are in YYYYMMDD format.