cancel
Showing results for 
Search instead for 
Did you mean: 

SEM - checking validity periods in FOX.

Former Member
0 Kudos

Hi,

I need to include a check function before data is written into the cube.

EXIT function is not meeting my needs.

So, I need to check this using FOX.

Has any one done this before?

Users enter CAL QUARTER & VALID FROM & VALID TO dates & I need to check if VALID DATES are falling in the same quarter or not.

Info objects that we are dealing with are 0CALQUARTER, 0CRM_VALIDF & 0CRM_VALIDT.

if users enter 3/2005 & 7/1/2005 & 9/30/2005, no message.

if users enter 3/2005 & 8/1/2005 & 12/31/2005, message needed to change valid to date.

if users enter 3/2005 & 6/1/2005 & 7/31/2005, message needed to change valid from date.

Appreciate any help.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

You should create a key figure with date,use this key figure for the from and to,and it will allow you to change the date.

Former Member
0 Kudos

JW,

thanks for your reply.

We are already GO LIVE so not sure how to deal with DATES as key figures.

Isn't it complicated to change all levels, packages, functions, FOX code & USER exits to deal with this change in data modeling?

Appreciate any suggestions on this.

Answers (4)

Answers (4)

former_member93896
Active Contributor
0 Kudos

Hello Hari,

well, why not do it in FOX? Here's some FOX that validates calendar day against calendar quarter. You can adapt it for your two date characteristics. "Fields to be changed" should only be "Key figure".

Note: You must have a current release that supports the SUBSTR expression and the string type!

Regards,

Marc

SAP NetWeaver RIG


DATA CALDAY     TYPE 0CALDAY.
DATA CALQUARTER TYPE 0CALQUARTER.
DATA QYEAR      TYPE STRING.
DATA QQUARTER   TYPE STRING.
DATA DYEAR      TYPE STRING.
DATA DMONTH     TYPE STRING.
DATA ERRORCODE  TYPE I.

CALQUARTER = OBJV().
CALDAY     = OBJV().

MESSAGE I001(UPF) WITH
  'CALQUARTER=' CALQUARTER
  'CALDAY='     CALDAY.

QYEAR    = SUBSTR(CALQUARTER,0,4).
QQUARTER = SUBSTR(CALQUARTER,4,1).
DYEAR    = SUBSTR(CALDAY,0,4).
DMONTH   = SUBSTR(CALDAY,4,2).

ERRORCODE = 0.
IF QYEAR <> DYEAR.
  ERRORCODE = 1.
ELSE.
  IF QQUARTER = '1' AND DMONTH > '03'.
    ERRORCODE = 2.
  ELSEIF QQUARTER = '2' AND
    ( DMONTH < '04' OR DMONTH > '06' ).
    ERRORCODE = 2.
  ELSEIF QQUARTER = '3' AND
    ( DMONTH < '07' OR DMONTH > '09' ).
    ERRORCODE = 2.
  ELSEIF QQUARTER = '4' AND DMONTH < '10'.
    ERRORCODE = 2.
  ENDIF.
ENDIF.

IF ERRORCODE = 1.
  MESSAGE E001(UPF) WITH 'YEAR IS NOT CORRECT'.
ELSEIF ERRORCODE = 2.
  MESSAGE E001(UPF) WITH 'MONTH IS NOT CORRECT'.
ENDIF.

IF ERRORCODE > 0.
  MESSAGE E001(UPF) WITH 'PLEASE CHANGE DATE'.
ENDIF.

Former Member
0 Kudos

Hi Marc,

Is substring (SUBSTR) new as of BW-BPS 3.5? How about concatenation since I could have used it on several projects?

Thanks,

Mary

Former Member
0 Kudos

Marc,

thanks for your reply & it's good to see you back on SDN.

We are still on 3.x version which is not allowing me to use (SUBSTR) function.

I've developed similar code & just posting the same incase if it helps any one working on 3.x versions. (Ofcourse, credit goes to you as you helped me to develop part of the code 6 months back, if I remember correctly)

now, a major debate is going on among our users.

FOX code is raising messages & at the same time, layout is taking incorrect entries as well & freezing the row with no option of changing the incorrect entry(meaning, users can't change the incorrect entry rather they have to delete & re-enter again).

Is the way it works? Is there any way of avoiding this? Is there any way of changing directly "VALID DATES" right after the error message?

Attribue Char relationship gives an option of changing the incorrect attribute.

Can we not do something in this case to change the Dates?

Appreciate any help.

Mary,

Can we use concatenation in FOX?

<Code>

DATA CM TYPE 0CALMONTH.

DATA CM_END TYPE 0CALMONTH.

DATA CQ TYPE 0CALQUARTER.

DATA CQ_TEMP TYPE 0CALQUARTER.

DATA DATE_S TYPE D.

DATA DATE_E TYPE D.

DATA ACT_S TYPE 0CRM_VALIDF.

DATA ACT_E TYPE 0CRM_VALIDT.

DATA MAT TYPE 0MATERIAL.

FOREACH MAT, CQ, ACT_S, ACT_E.

DO.

IF CQ = '20001'.

IF ACT_S < DATE_S.

MESSAGE E001 (ZTEST_SEM) WITH ACT_S CQ_TEMP MAT .

ELSEIF ACT_S > DATE_E.

MESSAGE E001 (ZTEST_SEM) WITH ACT_S CQ_TEMP MAT .

ENDIF.

EXIT.

ELSE.

CQ = TMVL( CQ, -1 ).

CM = TMVL( CM, +3 ).

CM_END = TMVL( CM, +2).

DATE_S = C2DATE(CM,S).

DATE_E = C2DATE(CM_END,E).

ENDIF.

ENDDO.

ENDIF.

ENDFOR.

former_member195980
Active Participant
0 Kudos

We upgraded from 3.1 to 4.0 and the substring function for fox is available no. But i still did not find anyway to concatenate!!

Former Member
0 Kudos

Hi Hari,

I have same requirement to generate the error message when the effective date that we collect using the key figure is not falling in the planning cycle. Following example might help you to understand my question better.

As part of headcount planning process we collect the various actions types and effective date of action, for the employees. Fox formulas will use this date for various calculation and monthly salary allocation. Here I want to restrict the users from entering action date beyond plan origin.lets say they are planning for year 2006. I need to restrict the users being entering any date not falling in year 2006 so once they enter not valid date, I want to provide a massage saying that data is not valid and needs to be changed.

Please provide me with some idea on coding this.

Thanks,

Raj.

Former Member
0 Kudos

Hi Hari,

I have same requirement to generate the error message when the effective date that we collect using the key figure is not falling in the planning cycle. Following example might help you to understand my question better.

As part of headcount planning process we collect the various actions types and effective date of action, for the employees. Fox formulas will use this date for various calculation and monthly salary allocation. Here I want to restrict the users from entering action date beyond plan origin.lets say they are planning for year 2006. I need to restrict the users being entering any date not falling in year 2006 so once they enter not valid date, I want to provide a massage saying that data is not valid and needs to be changed.

Please provide me with some idea on coding this.

Thanks,

Raj.

former_member93896
Active Contributor
0 Kudos

Mary,

Yes, SUBSTR is included in BW 3.5 (and therefore SEM 4.0 as well).

CONCAT will be available in the next release with some other nice FOX enhancements

Regards

Marc

SAP NetWeaver RIG

Former Member
0 Kudos

Hi Marc,

I did see substr now in BW-BPS 3.5 and lookig forward to CONCAT and other nice FOX enhancement for BI Integrated Planning 7.0

Thanks,

Mary

Former Member
0 Kudos

if that is the case you better use the charateristic rela.

question: where the data come from for the date of from and to when the user initially get into the layout?

If the user need to input the date for the layout,you can use the charateristic relationship like Mary suggested. you can use the user exit type charateristic relationship. after user input the date and save the data, if the date is not right,you can use the delete function to delete the incorrectly record and input it again.

Former Member
0 Kudos

NS

Message was edited by: Nimmi S

Message was edited by: Nimmi S

Former Member
0 Kudos

Hari,

I think it may be better to do a characteristic relationship exit to derive the from and to dates...

Mary

Former Member
0 Kudos

Mary,

thanks for your reply.

I don't want to derive VALID Dates from CAL QUARTER rather I want to validate user entered valid dates.

system should inform user & GIVE A CHANCE TO CHANGE INCORRECT CHARACTERISTIC, If user enters incorrect date.

EXIT function reads the data only if it is written into the buffer.

Once it is written into the buffer, even if EXIT function gives a message, user can't change the date as it is a characteristic & the line gets freezed with a different color ( at this point, data hasn't yet been saved, but, user can't change characteristic values but keyfigures can be changed).

I've developed a FOX function & included this into folder with funciton attributes "execute function before layout display" & raising a message for incorrect entries.

even this is not meeting my needs.

It's not writing into cube & giving error message (this is fine).

But, user is not able to change incorrect characteristic value after the error message as the row gets freezing status with only option of changing key figure values but not characteristic values (invalid dates in this case).

What options do I left with?

Is the only option to use "delete row" with a trash can icon. We prefer not to teach our sales users to use this function.

Appreciate any other ideas.

Former Member
0 Kudos

Hari,

unless you have different company or different countries where calquarter corresponds to different date periods under different conditions, the validity date of a calquarter never changes and thus it would be more efficient for a characteristic relationship to derive the valid from date and valid to date.

You need to exclude the valid from date and valid to date from your planning level and have a characteristic relationship exit where source is 0calquarter and target is the valid from data and valid to date.

Mary

Former Member
0 Kudos

Mary,

I agree with you on your explanation of excluding valid dates from the level.

we also create partial quotes meaning dates always doesn't start with 1st day of quarter & end with last day of quarter but also start & end with other dates in the same quarter.

for example, we have a need to create a quote in Q3 2005 with VALID FROM 8/15/2005 & VALID TO 9/15/2005.

so, Q3 2005 quotes doesn't always start with 7/1/2005 & end with 9/30/2005.

for the reason, derivation doesn't meet our needs.

moreover, sometimes we see that system is taking user input with a blank value for CALQUARTER.

in this case, we can't derive the dates right?

you have any suggestions on how I can deal this issue.

Appreciate any other ideas.

Former Member
0 Kudos

Also, you might run into an issue if your quote can cross quarters i.e. 9/15/2005 - 10/15/2005 since it belongs to quarters...