Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Script Logic For Calculating Closing and Opening

Dear Team,

We have developed a script logic for calculating Closing and Opening for all periods depending upon below calculations for all the Balance sheet items planning

F_100   Opening flow

F_120   Addition flow

F_130   Deduction flow

F_999   Closing Flow

F_999 = F_100 + F_120 - F_130

Script :

*XDIM_MEMBERSET P_ACCT = %P_ACCT_SET%

*SELECT(%F%, "[ID]", FLOW, "[ID]='F_100'")

*SELECT(%FF%,ID,FLOW,"ID='F_120'")

*SELECT(%FFF%,ID,FLOW,"ID='F_130'")

*XDIM_MEMBERSET FLOW = %F%,%FF%,%FFF%

*XDIM_MEMBERSET Category = PLAN,ESTIMATED

*FOR %T%=%TIME_SET%

*XDIM_MEMBERSET TIME=%T%

//FOR ASSET ACCOUNTS

*WHEN P_ACCT.ACCTYPE

*IS "AST"

*WHEN FLOW

*IS %FFF%

*REC(FACTOR=-1,FLOW=F_999,TIME=TMVL(0,%T%))

*REC(FACTOR=-1,FLOW=F_100,TIME=TMVL(1,%T%))

*IS %FF%

*REC(EXPRESSION=%VALUE%-[FLOW].[F_130]+[FLOW].[F_130],FLOW=F_999,TIME=TMVL(0,%T%))

*REC(EXPRESSION=%VALUE%-[FLOW].[F_130]+[FLOW].[F_130],FLOW=F_100,TIME=TMVL(1,%T%))

*ELSE

*REC(EXPRESSION=%VALUE%+[FLOW].[F_120]-[FLOW].[F_130]-([FLOW].[F_120]-[FLOW].[F_130])+[FLOW].[F_130]-+[FLOW].[F_130],FLOW=F_999,TIME=TMVL(0,%T%))

*REC(EXPRESSION=%VALUE%+[FLOW].[F_120]-[FLOW].[F_130]-([FLOW].[F_120]-[FLOW].[F_130])+[FLOW].[F_130]-+[FLOW].[F_130],FLOW=F_100,TIME=TMVL(1,%T%))

*ENDWHEN

//FOR LEQ LEQ ACCOUNTS

*IS "LEQ"

*WHEN FLOW

*IS %FFF%

*REC(FACTOR=1,FLOW=F_999,TIME=TMVL(0,%T%))

*REC(FACTOR=1,FLOW=F_100,TIME=TMVL(1,%T%))

*IS %FF%

*REC(EXPRESSION=-1*(%VALUE%-[FLOW].[F_130]+[FLOW].[F_130]),FLOW=F_999,TIME=TMVL(0,%T%))

*REC(EXPRESSION=-1*(%VALUE%-[FLOW].[F_130]+[FLOW].[F_130]),FLOW=F_100,TIME=TMVL(1,%T%))

*ELSE

*REC(EXPRESSION=-1*(%VALUE%+[FLOW].[F_120]-[FLOW].[F_130]-([FLOW].[F_120]-[FLOW].[F_130])+[FLOW].[F_130]-+[FLOW].[F_130]),FLOW=F_999,TIME=TMVL(0,%T%))

*REC(EXPRESSION=-1*(%VALUE%+[FLOW].[F_120]-[FLOW].[F_130]-([FLOW].[F_120]-[FLOW].[F_130])+[FLOW].[F_130]-+[FLOW].[F_130]),FLOW=F_100,TIME=TMVL(1,%T%))

*ENDWHEN

*ENDWHEN

*NEXT

Queries :

1)How can I simplify above script/ how can I simplify  since it is taking more time to execute?

2)How some GL's, refer Re: Script Logic on Agrregrate Values , I want to nulify the negative effect of opening value. ( As per client, they said there won't be negative value in the opening flow for Inventory GL anytime, but still for safe purpose we want to handle this case)

3)How to modify advance script in order to get hierarchy of GL account  and Time since it is very hectic to select base member all the time?

4)How can I stop script to write data for the last period closing value to opening value of next years first period.

   Ex. : When we run above script for year 2014, the closing value of 2014.DEC are written to opening value of 2015.JAN  which should be avoided.

Former Member
replied

Top most WHEN TIME.PERIOD is OK:

*XDIM_MEMBERSET P_ACCT = %P_ACCT_SET%

*SELECT(%F%, "[ID]", FLOW, "[ID]='F_100'")

*SELECT(%FF%,ID,FLOW,"ID='F_120'")

*SELECT(%FFF%,ID,FLOW,"ID='F_130'")

*XDIM_MEMBERSET FLOW = %F%,%FF%,%FFF%

*XDIM_MEMBERSET Category = PLAN,ESTIMATED

*FOR %T%=%TIME_SET%

*XDIM_MEMBERSET TIME=%T%

*WHEN TIME.PERIOD

*IS DEC //don't write to next year - remove lines with TIME=TMVL(1,%T%)

*WHEN P_ACCT.ACCTYPE

*IS "AST"//FOR ASSET ACCOUNTS

*WHEN FLOW

*IS %FFF%

*REC(FACTOR=-1,FLOW=F_999)

*IS %FF%

*REC(EXPRESSION=%VALUE%-[FLOW].[F_130]+[FLOW].[F_130],FLOW=F_999)

*ELSE

*REC(EXPRESSION=%VALUE%+[FLOW].[F_120]-[FLOW].[F_130]-([FLOW].[F_120]-[FLOW].[F_130])+[FLOW].[F_130]-[FLOW].[F_130],FLOW=F_999)

*ENDWHEN

*IS "LEQ" //FOR LEQ LEQ ACCOUNTS

*WHEN FLOW

*IS %FFF%

*REC(FACTOR=1,FLOW=F_999)

*IS %FF%

*REC(EXPRESSION=-1*(%VALUE%-[FLOW].[F_130]+[FLOW].[F_130]),FLOW=F_999)

*ELSE

*REC(EXPRESSION=-1*(%VALUE%+[FLOW].[F_120]-[FLOW].[F_130]-([FLOW].[F_120]-[FLOW].[F_130])+[FLOW].[F_130]-[FLOW].[F_130]),FLOW=F_999)

*ENDWHEN

*ENDWHEN

*ELSE //TIME.PERIOD <> DEC - Normal processing

*WHEN P_ACCT.ACCTYPE

*IS "AST"//FOR ASSET ACCOUNTS

*WHEN FLOW

*IS %FFF%

*REC(FACTOR=-1,FLOW=F_999)

*REC(FACTOR=-1,FLOW=F_100,TIME=TMVL(1,%T%))

*IS %FF%

*REC(EXPRESSION=%VALUE%-[FLOW].[F_130]+[FLOW].[F_130],FLOW=F_999)

*REC(EXPRESSION=%VALUE%-[FLOW].[F_130]+[FLOW].[F_130],FLOW=F_100,TIME=TMVL(1,%T%))

*ELSE

*REC(EXPRESSION=%VALUE%+[FLOW].[F_120]-[FLOW].[F_130]-([FLOW].[F_120]-[FLOW].[F_130])+[FLOW].[F_130]-[FLOW].[F_130],FLOW=F_999)

*REC(EXPRESSION=%VALUE%+[FLOW].[F_120]-[FLOW].[F_130]-([FLOW].[F_120]-[FLOW].[F_130])+[FLOW].[F_130]-[FLOW].[F_130],FLOW=F_100,TIME=TMVL(1,%T%))

*ENDWHEN

*IS "LEQ" //FOR LEQ LEQ ACCOUNTS

*WHEN FLOW

*IS %FFF%

*REC(FACTOR=1,FLOW=F_999)

*REC(FACTOR=1,FLOW=F_100,TIME=TMVL(1,%T%))

*IS %FF%

*REC(EXPRESSION=-1*(%VALUE%-[FLOW].[F_130]+[FLOW].[F_130]),FLOW=F_999)

*REC(EXPRESSION=-1*(%VALUE%-[FLOW].[F_130]+[FLOW].[F_130]),FLOW=F_100,TIME=TMVL(1,%T%))

*ELSE

*REC(EXPRESSION=-1*(%VALUE%+[FLOW].[F_120]-[FLOW].[F_130]-([FLOW].[F_120]-[FLOW].[F_130])+[FLOW].[F_130]-[FLOW].[F_130]),FLOW=F_999)

*REC(EXPRESSION=-1*(%VALUE%+[FLOW].[F_120]-[FLOW].[F_130]-([FLOW].[F_120]-[FLOW].[F_130])+[FLOW].[F_130]-[FLOW].[F_130]),FLOW=F_100,TIME=TMVL(1,%T%))

*ENDWHEN

*ENDWHEN

*NEXT

Vadim

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question