on 09-16-2014 12:00 PM
Dear Experts,
We are having BPC 7.5 NW
We are having a requirement where, we need to write a script logic which contained in different flows containing different material.
Example :
Opening value for inventory GL say 2001010 is 10000 with NA as a material class and F_100 (opening flow )
Now user will plan for various materials classes for its consumption and minimum stock balance under this same GL , example
F_130 F_410
CS-EM 500 20
CS-ED 500 20
with F_130 (deduction flow)
F_410 (minimum stock balance flow)
Now , I want to calculate closing for same GL with calculation as below :
First I will transfer the opening to a new flow with condition that only opening values which are above 0 should be transfer:
F_INV >= F_100
For this we have developed a script logic as below :
*XDIM_MEMBERSET CATEGORY=PLAN,ESTIMATED
*XDIM_MEMBERSET P_DATASRC=MANUAL
*XDIM_MEMBERSET P_ACCT = BAS(1010300000)
//*XDIM_MEMBERSET P_MATERIAL=BAS(All_MAT)
*XDIM_MEMBERSET P_EMP_TYPE=NA
*XDIM_MEMBERSET P_PROJECT=NA
*XDIM_MEMBERSET P_SERVICES=NA
//*XDIM_MEMBERSET P_CC=BAS(ABCD)
*XDIM_MEMBERSET P_ASSET=NA
*XDIM_MEMBERSET FLOW = F_100
//Inverse Flow calculation
*FOR %M1% = %TIME_SET%
*WHEN FLOW
*IS F_100
*REC(EXPRESSION = %VALUE% > 0 ? %VALUE%:0,FLOW = F_INV )
*ENDWHEN
*NEXT
*COMMIT
This transfer all the openings to F_INV flow with NA material class.
So in our case , since opening value is 10000, which is greater than 0 , F_INV will have 10000.
Now for closing the calculation is :
Closing = IF ( (Deduction + minimum stock balance - F_INV ) > 0 , Value , 0)
that means , logic should calculate as below
10000 - ((500+500)+(20+20)) = 8060
Now since this is above zero (0) , it should hit the closing flow.
We have developed below script logic :
*XDIM_MEMBERSET CATEGORY=PLAN,ESTIMATED
*XDIM_MEMBERSET P_DATASRC=MANUAL
*XDIM_MEMBERSET P_ACCT = BAS(1010300000)
*XDIM_MEMBERSET P_MATERIAL=BAS(All_MAT)
*XDIM_MEMBERSET P_EMP_TYPE=NA
*XDIM_MEMBERSET P_PROJECT=NA
*XDIM_MEMBERSET P_SERVICES=NA
*XDIM_MEMBERSET P_CC=BAS(ABCD)
*XDIM_MEMBERSET P_ASSET=NA
*XDIM_MEMBERSET TIME =%TIME_SET%
//addition Flow calculation
*WHEN P_MATERIAL
*IS BAS(ALL_MAT)
*WHEN FLOW
*IS F_130
*REC(EXPRESSION = %VALUE%+[FLOW].[F_410]-[FLOW].[F_INV] > 0 ? %VALUE%+[FLOW].[F_410]-[FLOW].[F_INV]:0,FLOW = F_120)
*IS F_410
*REC(EXPRESSION = %VALUE%+[FLOW].[F_130]-[FLOW].[F_INV] > 0 ? %VALUE%+[FLOW].[F_130]-[FLOW].[F_INV]:0,FLOW = F_120)
*IS F_INV
*REC(EXPRESSION = [FLOW].[F_130]+[FLOW].[F_410]-%VALUE% > 0 ? [FLOW].[F_130]+[FLOW].[F_410]-%VALUE%:0,FLOW = F_120)
*ENDWHEN
*ENDWHEN
But the logic is calculating the closing considering the F_INV flow as zero for all the material classes since it is zero.
How can we achieve this.
Thanks in advance.
Dear Vadim,
the script which you suggested for addition column value is not working.
My assumptions from your script.
From first section (//Preliminary calculation - sum F_130,F_410 for all materials) First we add the deduction+min. stock balance for all material into addition.
Lets take example that deduction+min. stock balance for all material is 400000.
Then as per second section (//Preliminary calculation - subtract F_INV for NA) we add the negative F_INV into the same addition value, i.e in our case the F_INV is 396000, so after after execution of second section, the value will be added to addition as 400000 + ( - 396000) which should be equal to 4000, but when executed, it is overwriting the value with -396000 resulting in wrong calculation.
Kindly assist.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
P.S. If you have F_INV value only in P_MATERIAL=NA you can combine first and second loop:
*XDIM_MEMBERSET P_MATERIAL=BAS(All_MAT)
*XDIM_MEMBERSET FLOW = F_130,F_410,F_INV
*WHEN FLOW
*IS F_INV
*REC(EXPRESSION=-%VALUE%,FLOW=F_120,P_MATERIAL=NA) //store "-F_INV"
*ELSE
*REC(EXPRESSION=%VALUE%,FLOW=F_120,P_MATERIAL=NA) //store sum of F_130,F_410
*ENDWHEN
Negative F_INV on NA will be accumulated with F_130,F_410 for All_MAT
Vadim
What's the issue?
First combined option is:
*XDIM_MEMBERSET CATEGORY=PLAN,ESTIMATED
*XDIM_MEMBERSET P_DATASRC=MANUAL
*XDIM_MEMBERSET P_ACCT = BAS(1010300000)
*XDIM_MEMBERSET P_EMP_TYPE=NA
*XDIM_MEMBERSET P_PROJECT=NA
*XDIM_MEMBERSET P_SERVICES=NA
*XDIM_MEMBERSET P_CC=BAS(ABCD)
*XDIM_MEMBERSET P_ASSET=NA
*XDIM_MEMBERSET TIME =%TIME_SET%
*XDIM_MEMBERSET P_MATERIAL=BAS(All_MAT)
//Preliminary calculation - sum F_130,F_410 for all materials
*XDIM_MEMBERSET FLOW = F_130,F_410
*WHEN P_MATERIAL
*IS * // BAS(All_MAT) - already scoped
*REC(EXPRESSION=%VALUE%,FLOW=F_120,P_MATERIAL=NA) //store sum of F_130,F_410
*ENDWHEN
//Preliminary calculation - subtract F_INV for NA
*XDIM_MEMBERSET P_MATERIAL=NA
*XDIM_MEMBERSET FLOW = F_INV
*WHEN P_MATERIAL
*IS * // NA - already scoped
*REC(EXPRESSION=[FLOW].[F_120]-%VALUE%,FLOW=F_120) //subtract F_INV
*ENDWHEN
//Final
*XDIM_MEMBERSET FLOW = F_120
*WHEN P_MATERIAL
*IS * // NA - already scoped
*REC(EXPRESSION=%VALUE%>0 ? %VALUE% : 0) //zero negative in F_120
*ENDWHEN
Second option requires understanding what data you have in F_INV!
Vadim
If NA is not in BAS(All_MAT) then the second code option will be:
*XDIM_MEMBERSET CATEGORY=PLAN,ESTIMATED
*XDIM_MEMBERSET P_DATASRC=MANUAL
*XDIM_MEMBERSET P_ACCT = BAS(1010300000)
*XDIM_MEMBERSET P_EMP_TYPE=NA
*XDIM_MEMBERSET P_PROJECT=NA
*XDIM_MEMBERSET P_SERVICES=NA
*XDIM_MEMBERSET P_CC=BAS(ABCD)
*XDIM_MEMBERSET P_ASSET=NA
*XDIM_MEMBERSET TIME =%TIME_SET%
//Preliminary calculation - sum F_130,F_410 for all materials and subtract F_INV
*XDIM_MEMBERSET P_MATERIAL=BAS(All_MAT)
*XDIM_ADDMEMBERSET P_MATERIAL=NA
*XDIM_MEMBERSET FLOW = F_130,F_410,F_INV
*WHEN FLOW
*IS F_130,F_410
*WHEN P_MATERIAL
*IS BAS(All_MAT)
*REC(EXPRESSION=%VALUE%,FLOW=F_120,P_MATERIAL=NA) //sum of F_130,F_410
*ENDWHEN
*IS F_INV
*WHEN P_MATERIAL
*IS NA
*REC(EXPRESSION=-%VALUE%,FLOW=F_120) //subtract F_INV
*ENDWHEN
*ENDWHEN //Accumulation in this loop will provide correct results!
//Final - zero negative in F_120
*XDIM_MEMBERSET FLOW = F_120
*XDIM_MEMBERSET P_MATERIAL=NA
*WHEN P_MATERIAL
*IS * // NA - already scoped
*REC(EXPRESSION=%VALUE%>0 ? %VALUE% : 0) //zero negative in F_120
*ENDWHEN
Vadim
IF NA is in BAS(All_MAT):
*XDIM_MEMBERSET CATEGORY=PLAN,ESTIMATED
*XDIM_MEMBERSET P_DATASRC=MANUAL
*XDIM_MEMBERSET P_ACCT = BAS(1010300000)
*XDIM_MEMBERSET P_EMP_TYPE=NA
*XDIM_MEMBERSET P_PROJECT=NA
*XDIM_MEMBERSET P_SERVICES=NA
*XDIM_MEMBERSET P_CC=BAS(ABCD)
*XDIM_MEMBERSET P_ASSET=NA
*XDIM_MEMBERSET TIME =%TIME_SET%
//Preliminary calculation - sum F_130,F_410 for all materials and subtract F_INV
*XDIM_MEMBERSET P_MATERIAL=BAS(All_MAT) //NA is also in scope
*XDIM_MEMBERSET FLOW = F_130,F_410,F_INV
*WHEN P_MATERIAL
*IS NA
*WHEN FLOW
*IS F_130,F_410
*REC(EXPRESSION=%VALUE%,FLOW=F_120) //sum of F_130,F_410 for NA
*ENDWHEN
*IS F_INV
*REC(EXPRESSION=-[FLOW].[F_120]-%VALUE%,FLOW=F_120) //subtract F_INV
*ENDWHEN
*ELSE // all other members of BAS(All_MAT) except NA
*WHEN FLOW
*IS F_130,F_410
*REC(EXPRESSION=%VALUE%,FLOW=F_120,P_MATERIAL=NA) //sum of F_130,F_410 for All
*ENDWHEN
*ENDWHEN
//Final
*XDIM_MEMBERSET FLOW = F_120
*XDIM_MEMBERSET P_MATERIAL=NA
*WHEN P_MATERIAL
*IS * // NA - already scoped
*REC(EXPRESSION=%VALUE%>0 ? %VALUE% : 0) //zero negative in F_120
*ENDWHEN
Vadim
Can you clarify what do you mean by "But the logic is calculating the closing considering the F_INV flow as zero for all the material classes since it is zero."??
Vadim
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Dear Vadim,
There were mistake while writing the query ,
I have rectified it in below :
Our closing for all balance sheet items is :
Closing = Opening + Addition - Deduction
Dear Experts,
We are having BPC 7.5 NW
We are having a requirement where, we need to write a script logic which contained in different flows containing different material.
Example :
Opening value for inventory GL say 2001010 is 10000 with NA as a material class and F_100 (opening flow )
Now user will plan for various materials classes for its consumption and minimum stock balance under this same GL , example
F_130 F_410
CS-EM 500 20
CS-ED 500 20
with F_130 (deduction flow)
F_410 (minimum stock balance flow)
Now , I want to calculate addition flow for same GL with calculation as below :
First I will transfer the opening to a new flow with condition that only opening values which are above 0 should be transfer:
F_INV >= F_100
For this we have developed a script logic as below :
*XDIM_MEMBERSET CATEGORY=PLAN,ESTIMATED
*XDIM_MEMBERSET P_DATASRC=MANUAL
*XDIM_MEMBERSET P_ACCT = BAS(1010300000)
//*XDIM_MEMBERSET P_MATERIAL=BAS(All_MAT)
*XDIM_MEMBERSET P_EMP_TYPE=NA
*XDIM_MEMBERSET P_PROJECT=NA
*XDIM_MEMBERSET P_SERVICES=NA
//*XDIM_MEMBERSET P_CC=BAS(ABCD)
*XDIM_MEMBERSET P_ASSET=NA
*XDIM_MEMBERSET FLOW = F_100
//Inverse Flow calculation
*FOR %M1% = %TIME_SET%
*WHEN FLOW
*IS F_100
*REC(EXPRESSION = %VALUE% > 0 ? %VALUE%:0,FLOW = F_INV )
*ENDWHEN
*NEXT
*COMMIT
This will transfer all the openings to F_INV flow with NA material class.
So in our case , since opening value is 10000, which is greater than 0 , F_INV will have 10000.
Now for addition the calculation is :
Addition = IF ( (Deduction + minimum stock balance - F_INV ) > 0 , Value , 0)
that means , logic should calculate as below
((500+500)+(20+20)) - 10000 = - 8060
Now since this is below zero (0) , zero (0) should hit the closing flow but it is hitting (500+20) in each material class wise.
We have developed below script logic :
*XDIM_MEMBERSET CATEGORY=PLAN,ESTIMATED
*XDIM_MEMBERSET P_DATASRC=MANUAL
*XDIM_MEMBERSET P_ACCT = BAS(1010300000)
*XDIM_MEMBERSET P_MATERIAL=BAS(All_MAT)
*XDIM_MEMBERSET P_EMP_TYPE=NA
*XDIM_MEMBERSET P_PROJECT=NA
*XDIM_MEMBERSET P_SERVICES=NA
*XDIM_MEMBERSET P_CC=BAS(ABCD)
*XDIM_MEMBERSET P_ASSET=NA
*XDIM_MEMBERSET TIME =%TIME_SET%
//addition Flow calculation
*WHEN P_MATERIAL
*IS BAS(ALL_MAT)
*WHEN FLOW
*IS F_130
*REC(EXPRESSION = %VALUE%+[FLOW].[F_410]-[FLOW].[F_INV] > 0 ? %VALUE%+[FLOW].[F_410]-[FLOW].[F_INV]:0,FLOW = F_120)
*IS F_410
*REC(EXPRESSION = %VALUE%+[FLOW].[F_130]-[FLOW].[F_INV] > 0 ? %VALUE%+[FLOW].[F_130]-[FLOW].[F_INV]:0,FLOW = F_120)
*IS F_INV
*REC(EXPRESSION = [FLOW].[F_130]+[FLOW].[F_410]-%VALUE% > 0 ? [FLOW].[F_130]+[FLOW].[F_410]-%VALUE%:0,FLOW = F_120)
*ENDWHEN
*ENDWHEN
But the logic is calculating the addition flow considering the F_INV flow as zero for all the material classes.
On your query :
The logic is calculating addition as below :
Suppose for GL2001010
Material F_100 F_130 F_410 F_INV F_120 F_999
NA 10000 10000 10000
CS-EM 500 20 520 20
CS-ED 500 20 520 20
But we want :
Material F_100 F_130 F_410 F_INV F_120 F_999
NA 10000 10000 10000
CS-EM 500 20 0 -500
CS-ED 500 20 0 -500
Other example with opening 0 :
Material F_100 F_130 F_410 F_INV F_120 F_999
NA 0 0 0
CS-EM 500 20 520 20
CS-ED 500 20 520 20
Other example with negative opening :
Material F_100 F_130 F_410 F_INV F_120 F_999
NA -500 0 -520
CS-EM 500 20 520 20
CS-ED 500 20 520 20
How can we achieve this.
Thanks in advance.
Ups, It's very hard to look on this long text, but I will try to simplify the question:
You have 3 parameters:
[FLOW].[F_130]
[FLOW].[F_410]
[FLOW].[F_INV]
And you want to check the expression: [FLOW].[F_410]+[FLOW].[F_130]-[FLOW].[F_INV] > 0 and if true - write the result to FLOW = F_120, if false - write zero to FLOW = F_120.
Is it correct?
Why do you marked question as answered?
Vadim
Like this:
*XDIM_MEMBERSET CATEGORY=PLAN,ESTIMATED
*XDIM_MEMBERSET P_DATASRC=MANUAL
*XDIM_MEMBERSET P_ACCT = BAS(1010300000)
*XDIM_MEMBERSET P_EMP_TYPE=NA
*XDIM_MEMBERSET P_PROJECT=NA
*XDIM_MEMBERSET P_SERVICES=NA
*XDIM_MEMBERSET P_CC=BAS(ABCD)
*XDIM_MEMBERSET P_ASSET=NA
*XDIM_MEMBERSET TIME =%TIME_SET%
*XDIM_MEMBERSET P_MATERIAL=BAS(All_MAT)
//Preliminary calculation - sum F_130,F_410 for all materials
*XDIM_MEMBERSET FLOW = F_130,F_410
*WHEN P_MATERIAL
*IS * // BAS(All_MAT) - already scoped
*REC(EXPRESSION=%VALUE%,FLOW=F_120,P_MATERIAL=NA) //store sum of F_130,F_410
*ENDWHEN
//Preliminary calculation - subtract F_INV for NA
*XDIM_MEMBERSET P_MATERIAL=NA
*XDIM_MEMBERSET FLOW = F_130
*WHEN P_MATERIAL
*IS * // NA - already scoped
*REC(EXPRESSION=-%VALUE%,FLOW=F_120) //subtract F_INV
*ENDWHEN
//Final
*XDIM_MEMBERSET FLOW = F_120
*WHEN P_MATERIAL
*IS * // NA - already scoped
*REC(EXPRESSION=%VALUE%>0 ? %VALUE% : 0) //zero negative in F_120
*ENDWHEN
Vadim
Dear Vadim,
In your second section :
/Preliminary calculation - subtract F_INV for NA
*XDIM_MEMBERSET P_MATERIAL=NA
*XDIM_MEMBERSET FLOW = F_130
*WHEN P_MATERIAL
*IS * // NA - already scoped
*REC(EXPRESSION=-%VALUE%,FLOW=F_120) //subtract F_INV
*ENDWHEN
Are your sure the scope is Flow = F_130
I guess it has to be F_INV
Yes, thank you vadim sir...
Its working very much fine.
One more query, how can I run logic for F_INV calculation in the same package.
Since in this scenario, I have to first transfer all my positive openings to F_INV, then only above calculation.
Is there any way , I can include the F_INV calculation in the same script.
since there are chances of user missing order of execution of package.
Yes for sure you can add before first preliminary calculation:
...
*XDIM_MEMBERSET TIME =%TIME_SET%
*XDIM_MEMBERSET P_MATERIAL=BAS(All_MAT)
//Calculate F_INV
*XDIM_MEMBERSET FLOW = F_100
*WHEN FLOW
*IS * //F_100 - scoped
*REC(EXPRESSION = %VALUE% > 0 ? %VALUE% : 0,FLOW = F_INV , P_MATERIAL=NA)
*ENDWHEN
//Preliminary calculation - sum F_130,F_410 for all materials
...
Don't use TIME loop - useless, and NEVER! use COMMIT with WHEN/ENDWHEN - useless and will reset the scope.
Vadim
P.S. It's better to close this discussion marking the correct answer!
I have tested 1 scenario , where opening value is negative , and so the minimum stock balance(F_410) value should be added to next period in order to consume(deduction flow) it, but for this I will have to update my logic for calculating the closing balances.
For example :
Opening -100
Deduction 2000
Minimum Stock balance 200
As per our previous logic
F_INV : 0
Addition : 2200
Closing : -100 + 2200 -2000 = 100
So we don't get the minimum stock ( 200) in next period.
I will post by closing balance calculation in a new thread.
Your inputs would be appreciated Sir!!
Thank you!!
User | Count |
---|---|
6 | |
5 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.