Script Logic For Calculating Closing and Opening

[Former Member]

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 , 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_member186338 · Accepted solution]

1) About script:

This script has to be run for all months of some year... Then it's better to just select year in DM prompt - COMBOBOX with the list of years is fine. The result of user selection will be transferred to the variable like $CURYEAR$.

Then in the script:

*FOR %T%=$CURYEAR$.JAN,$CURYEAR$.FEM,$CURYEAR$.MAR...$CURYEAR$.DEC

You can't avoid FOR/NEXT because you have to process periods in proper order (JAN->DEC)

Remove from the code useless: ",TIME=TMVL(0,%T%)"

Try to change logic the way I used in the previous answer (instead of formulas just accumulate with + or -)

2) Not clear what do you want!

3) For TIME - see 1) For accounts - depends on what you want to select (may be just bas() or something)

4) You have to add additional WHEN/IS condition:

*WHEN TIME.PERIOD

*IS DEC

and remove here write to TIME=TMVL(1,%T%)

Vadim

[Former Member · Accepted solution]

thank you Sir!!

For the 4) reply, where I need to apply this When loop which you recommended in order to restrict the logic to write the data for JAN period of next year..

thanks!!

[former_member186338 · Accepted solution]

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

[Former Member · Accepted solution]

Dear Vadim,

I updated the code as below and it executes properly, but when i  look at the data, it only get executed for JAN and FEB month, i.e, I get  closing for JAN , FEB, and opening for FEB and MAR.

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 TIME.PERIOD

*IS JAN,FEB,MAR.APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV

*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

*ENDWHEN

//FOR LEQ LEQ ACCOUNTS

*IS "LEQ"

*WHEN TIME.PERIOD

*IS JAN,FEB,MAR.APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV

*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

*ENDWHEN

//FOR ASSET ACCOUNTS

*WHEN P_ACCT.ACCTYPE

*IS "AST"

*WHEN TIME.PERIOD

*IS DEC

*WHEN FLOW

*IS %FFF%

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

*IS %FF%

*REC(EXPRESSION=%VALUE%-[FLOW].[F_130]+[FLOW].[F_130],FLOW=F_999,TIME=TMVL(0,%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%))

*ENDWHEN

*ENDWHEN

//FOR LEQ LEQ ACCOUNTS

*IS "LEQ"

*WHEN TIME.PERIOD

*IS JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV

*WHEN FLOW

*IS %FFF%

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

*IS %FF%

*REC(EXPRESSION=-1*(%VALUE%-[FLOW].[F_130]+[FLOW].[F_130]),FLOW=F_999,TIME=TMVL(0,%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%))

*ENDWHEN

*ENDWHEN

*ENDWHEN

*NEXT

*COMMIT

[former_member186338 · Accepted solution]

And what is your %TIME_SET%???

Vadim

[former_member186338 · Accepted solution]

And sorry, please use my code without useless:

*IS JAN,FEB,MAR.APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV

TIME=TMVL(0,%T%)

*COMMIT

Vadim

[Former Member · Accepted solution]

thank you so much sir..learning a lot from you !!

I would really appreciate your inputs on my other critical post

thank you once again!!