on 01-04-2015 10:36 AM
Hi
I need help on currency conversion
my requirement is Users enter Budget data based on selections ENTITY, and "LC(Local currency).
If they enter data in LC then corresponding ENTITY:CURRENCY is taken as Local currency and in Exchange Rate table corresponding exchange rate values need to convert.
Ex:
IN ENTITY DIMENSION
ENTITY : 2200, Currency: SGD
In Rate Model
Category inputcurrcy rateentity rateid signdata
BUDGET SGD CNY BUD 0.777
BUDGET SGD EUR BUD 1.7575
BUDGET SGD GLOBAL BUD 0.656
now user enters budget data in SGD then SGD value need to convert into CNY, EUR and GLOBAL.
this can be dynamic but not hardcoded
I wrote script logic, but it is not working while saving data, my input schedule script logic is in "DEFAULT.LGF"
I hardcoded for GLOBAL then it is working fine, now I want all currencies then script logic is not working
*SELECT(%RT%,"[ID]",INPUTCURRENCY, "[REPORTING]='Y'")
*LOOKUP RATE-BUD
*DIM CATEGORY=BUDGET
*DIM RATEID=BUD
*DIM SOURCECURR:INPUTCURRENCY=ENTITY.CURRENCY
//*DIM TIME=%TIME
*FOR %CUR%=%RT%
*DIM %CUR%:RATEENTITY="%CUR%"
*NEXT
*ENDLOOKUP
*WHEN CURRENCY
*IS LC
*WHEN CATEGORY
*IS BUDGET
*WHEN REVENUE
*IS ASP
*FOR %LOOP_CUR%=%RT%
*REC(EXPRESSION=%VALUE%
*LOOKUP(%CUR%),CURRENCY=%CUR%)
*NEXT
*ENDWHEN
*ENDWHEN
*ENDWHEN
I tried for BADI also but couldn't get, can anyone please help me in this regard for multiple currency conversion from one Local Currency
Regards
Silu
Hi Silu,
Can you please explain what does it mean "script is not working"?
There are number of strange things in your script:
1. Why do you need SOURCECURR in your LOOKUP if you don't refer to it anywhere? I'd think that line will be ignored when LOOKUP is called.
2. If you have *FOR %LOOP_CUR%=%RT% then LOOP_CUR should be used in *REC, not CUR.
Regards,
Gersh
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Gersh
Thank you, I corrected my code and executed it, but records are not converting based on exchange rates, the exchange rate values are not extracting into code, i debug the code in UJKT, please check below code and ujkt validation
UJKT says records generated, but currency conversion not happening, the same value what i entered in LC stored in all currencies without exchange rate calculation. *LOOKUP(LOOKRATE) is not getting value.
Script Logic
*SELECT(%RT%,"[ID]",INPUTCURRENCY, "[REPORTING]='Y'")
*LOOKUP RATE-BUD
*DIM CATEGORY=BUDGET
*DIM RATEID=BUD
*DIM INPUTCURRENCY=ENTITY.CURRENCY
//*DIM TIME=%TIME
*FOR %CUR%=%RT%
*DIM LOOKRATE:RATEENTITY=%CUR%
*NEXT
*ENDLOOKUP
*WHEN CURRENCY
*IS LC
*WHEN CATEGORY
*IS BUDGET
*WHEN REVENUE
*IS ASP
*FOR %CUR%=%RT%
*REC(EXPRESSION=%VALUE%*LOOKUP(LOOKRATE),CURRENCY=%CUR%)
*NEXT
*ENDWHEN //CLOSE CATEOGRY
*ENDWHEN // CLOSE CURRENCY
*ENDWHEN
UJKT Execution:
LGX:
*LOOKUP RATE-BUD
*DIM CATEGORY=BUDGET
*DIM RATEID=BUD
*DIM INPUTCURRENCY=ENTITY.CURRENCY
*DIM LOOKRATE:RATEENTITY=CNY
*DIM LOOKRATE:RATEENTITY=EUR
*DIM LOOKRATE:RATEENTITY=JPY
*DIM LOOKRATE:RATEENTITY=SGD
*ENDLOOKUP
*WHEN CURRENCY
*IS LC
*WHEN CATEGORY
*IS BUDGET
*WHEN REVENUE
*IS ASP
*REC(EXPRESSION=%VALUE%*LOOKUP(LOOKRATE),CURRENCY=CNY)
*REC(EXPRESSION=%VALUE%*LOOKUP(LOOKRATE),CURRENCY=EUR)
*REC(EXPRESSION=%VALUE%*LOOKUP(LOOKRATE),CURRENCY=JPY)
*REC(EXPRESSION=%VALUE%*LOOKUP(LOOKRATE),CURRENCY=SGD)
*ENDWHEN
*ENDWHEN
*ENDWHEN
LOG:
FILE:\ROOT\WEBFOLDERS\BIOSENSORS \ADMINAPP\SALESPLAN\TEST.LGF
USER:V-SREEVANI
APPSET:BIOSENSORS
APPLICATION:SALESPLAN
[INFO] GET_DIM_LIST(): I_APPL_ID="SALESPLAN", #dimensions=17
ACCOUNT,CATEGORY,COUNTRY,CURRENCY,DC,ENTITY,MATTYPE,MEASURES,PRODH1,PRODH2,PRODH3,PRODH4,REVENUE,SALESMATRIX,SALESREP,SEGMENT,TIME
#dim_memberset=0
REC :%VALUE%*LOOKUP(LOOKRATE)
REC :%VALUE%*LOOKUP(LOOKRATE)
REC :%VALUE%*LOOKUP(LOOKRATE)
REC :%VALUE%*LOOKUP(LOOKRATE)
CALCULATION BEGIN:
QUERY PROCESSING DATA
QUERY TIME : 2.00 ms. 6 RECORDS QUERIED OUT.
QUERY REFERENCE DATA
QUERY LOOKUP DATA FROM APPLICATION: RATE-BUD
QUERY TIME : 0.00 ms. 130 RECORDS QUERIED OUT.
CALCULATION TIME IN TOTAL :0.00 ms.
24 RECORDS ARE GENERATED.
CALCULATION END.
SCRIPT RUNNING TIME IN TOTAL:2.00 s.
Regards
Silu
Hi
anyone can help me in below script logic for currency conversion
User enter budget data for any currency and Saves data, the value need to convert into local currency based on ENTITY selected in the Template, below is script logic
Script logic working fine without error, but data is not converting.
I entered 102 value and saved data, the same value posted to different currencies without conversion, is my script logic wrong. please help me in this regard
*SELECT(%CUR%,"[CURRENCY]",ENTITY, ID=%ENTITY_SET%)
*SELECT(%RT%,"[ID]",INPUTCURRENCY, "[REPORTING]='Y'")
*LOOKUP RATE-BUD
*DIM CATEGORY="BUDGET"
*DIM RATEID="BUD"
*DIM RATEENTITY=%CUR% (this is Local currency based on ENTITY)
*DIM MEASURES="PERIODIC"
*FOR %LOOP_CUR%=%RT%
*DIM LOOKRATE:INPUTCURRENCY="%LOOP_CUR%" (this is currency selected by users to enter value)
*NEXT
*ENDLOOKUP
*WHEN CATEGORY
*IS BUDGET
*WHEN REVENUE
*IS ASP
*FOR %LOOP_CUR%=%RT%
*REC(EXPRESSION=%VALUE%/LOOKUP(LOOKRATE), CURRENCY=%LOOP_CUR%)
*NEXT
*ENDWHEN
*ENDWHEN
Regards
Silu
Hi Silu,
You got that error message because you made changes only to one part of the code and not to another. Please read carefully what I told you to do.
The part you missed is to replace LOOKRATE in LOOKUP with what you had initially
*FOR %CUR%=%RT%
*DIM %CUR%:RATEENTITY="%CUR%"
*NEXT
As I tried explaining to you ID in LOOKUP and parameter with which you call LOOKUP must be in sync. When you change only one part they go out of sync and you getting a syntax error.
Regards,
Gersh
Hi Gersh
Thank you, i did as per your suggestion in LOOKUP
but when i post record to currency in *REC statement, i'm getting error, is below statement correct or not
*FOR %LCUR%=%RT%
*REC(EXPRESSION=%VALUE%/LOOKUP(%LCUR%), CURRENCY=%LOOP_CUR%)
*NEXT
I'm receiving error as
VALIDATION_EXCEPTION:LINE 19 syntax error: " ReferenceError: LOOKUP is not defined"
I'm not familiar with script logic, please anyone help me in this regard
Regards
Silu (Sailaja)
Ups, I missed the post with UJKT results!
He don't understand the idea of LOOKUP:
1. LOOKUP label is defined for a single member or for the member derived from the property of the current member.
2. If you have to get value from multiple members - you have to use multiple labels.
3. Member ID can be used as a LOOKUP label or a part of LOOKUP label.
4. FOR/NEXT is used to create multiple LOOKUP labels.
5. In REC the LOOKUP is used with a required label - in case of multiple labels FOR/NEXT is used with REC.
6. Not sure but in case of multiple FOR/NEXT loops the loop variable have to be named differently.
The code after FOR/NEXT processing can be viewed as LGX in UJKT.
Vadim
Hi Gersh
Thank you for helping me, please find below script logic and do the needful.
If I use LOOKUP( %CUR%) in *REC then I'm getting error "Reference Error LOOKUP is not defined" ,
If I use just LOOKUP(CUR) in *REC I'm getting error as "LOOKUP ID CUR not found".
*SELECT(%LCUR%,"[CURRENCY]",ENTITY, ID=%ENTITY_SET%)
*SELECT(%RT%,"[ID]",INPUTCURRENCY, "[REPORTING]='Y'")
*LOOKUP RATE-BUD
*DIM CATEGORY="BUDGET"
*DIM RATEID="BUD"
*DIM RATEENTITY=%LCUR%
*DIM MEASURES="PERIODIC"
*FOR %CUR%=%RT%
*DIM %CUR%:RATEENTITY="%CUR%"
*NEXT
*ENDLOOKUP
*WHEN CATEGORY
*IS BUDGET
*WHEN REVENUE
*IS ASP
*FOR %CUR%=%RT%
*REC(EXPRESSION=%VALUE%/LOOKUP(%CUR%), CURRENCY=%CUR%)
*NEXT
*ENDWHEN
*ENDWHEN
Thank you
Regards
Silu
Hi Gersh
can you help me, this I need to fix asap.
Hi Vadim
you are not understanding my point, I gave my ENTITY code as E2100 in Select statement and did UJKT, if you can see above forum, you can get the details
I sent one screen shot that in *REC i'm getting error by giving LOOKUP(%CUR%)
*FOR %CUR%=%RT%
*REC(EXPRESSION=%VALUE%/LOOKUP(%CUR%), CURRENCY=%CUR%)
*NEXT
Regards
Silu
Hi
UJKT Result for testing taken two currencies CNY, EUR need to convert into SGD. No records generated
Code attached in screen shot
LOG: FILE:\ROOT\WEBFOLDERS\BIOSENSORS \ADMINAPP\SALESPLAN\TEST.LGF USER:V-SREEVANI APPSET:BIOSENSORS APPLICATION:SALESPLAN [INFO] GET_DIM_LIST(): I_APPL_ID="SALESPLAN", #dimensions=17 ACCOUNT,CATEGORY,COUNTRY,CURRENCY,DC,ENTITY,MATTYPE,MEASURES,PRODH1,PRODH2,PRODH3,PRODH4,REVENUE,SALESMATRIX,SALESREP,SEGMENT,TIME
#dim_memberset=0
REC :700*LOOKUP(VAR1)
CALCULATION BEGIN:
QUERY PROCESSING DATA
QUERY TIME : 2.00 ms. 0 RECORDS QUERIED OUT.
QUERY REFERENCE DATA
QUERY LOOKUP DATA FROM APPLICATION: RATE-BUD
QUERY TIME : 1.00 ms. 24 RECORDS QUERIED OUT.
CALCULATION TIME IN TOTAL :0.00 ms.
NO RECORDS GENERATED.
CALCULATION END.
[INFO] GET_DIM_LIST(): I_APPL_ID="SALESPLAN", #dimensions=17 ACCOUNT,CATEGORY,COUNTRY,CURRENCY,DC,ENTITY,MATTYPE,MEASURES,PRODH1,PRODH2,PRODH3,PRODH4,REVENUE,SALESMATRIX,SALESREP,SEGMENT,TIME
#dim_memberset=0
REC :800*LOOKUP(VAR2)
CALCULATION BEGIN: QUERY PROCESSING DATA
QUERY TIME : 1.00 ms. 0 RECORDS QUERIED OUT.
QUERY REFERENCE DATA
QUERY LOOKUP DATA FROM APPLICATION: RATE-BUD
QUERY TIME : 0.00 ms. 24 RECORDS QUERIED OUT.
CALCULATION TIME IN TOTAL :0.00 ms.
NO RECORDS GENERATED. CALCULATION END.
Regards
Silu
Hi
Earlier script logic getting error in *REC, so i changed it. I attached my Exchange rate values as JPG
this is minor problem to fix, but taking long time to resolve it, I'm not getting any help in forums to fix this issue, rotating round and round,
can any one help, this is urgent and need to fix as soon as possible
concept is User enter data in any currency (for ex: user can select currency CNY or EUR or JPY any currency) in Input Schedule, after saving data this currency value need to convert into Local currency (for ex: SGD) of corresponding Company code (E2100.
Ex: Exchange Rate Table data
INPUTCURRENCY LOCALCURRCY EXCHANGERATEVAlue
CNY SGD 0.2084
EUR SGD 1.6115
JPY SGD 0.0117
If user enters 500 in CNY currency then this should convert into SGD as 500/0.2084 = 2399.444 (WHEN CATEGORY IS BUDGET WHEN REVENUE IS ASP)
I'm trying lot to fix it, is there any one to give complete script logic code to fix this issue.
Regards
Silu
Hi
Finally I got Solution, I'm able to convert currencies from user entered currency value to Local currency of corresponding Company code
Ex: user entered in CNY currency for Comapny code E2200 and this value converted to SGD which is local currency of company code E2200
similarly for EUR
find attachment of my SALES PLANNING MODEL
but only thing it is overwriting with SGD so we need to add one more dimension to differentiate the records.
But Script logic i'm adding multiple variables in LOOKUP, instead of IT if there is a feasible solution that will be helpful for me
Regards
Silu
Hi
I wrote Script logic as I gave below and finally got solved, but it is all hard coded, can any one help me to make it as dynamic, i mean used multiple times WHEN for each currency
*SELECT(%LCUR%,"[CURRENCY]",ENTITY, ID=%ENTITY_SET%)
*LOOKUP RATE-BUD
*DIM CATEGORY=BUDGET
*DIM RATEID=BUD
*DIM SOURCECURR1:RATEENTITY=%LCUR%
*DIM SOURCECURR1:INPUTCURRENCY=CNY
*DIM SOURCECURR2:RATEENTITY=%LCUR%
*DIM SOURCECURR2:INPUTCURRENCY=EUR
*DIM SOURCECURR3:RATEENTITY=%LCUR%
*DIM SOURCECURR3:INPUTCURRENCY=HKD
*DIM SOURCECURR4:RATEENTITY=%LCUR%
*DIM SOURCECURR4:INPUTCURRENCY=JPY
*DIM SOURCECURR5:RATEENTITY=%LCUR%
*DIM SOURCECURR5:INPUTCURRENCY=SGD
*DIM SOURCECURR6:RATEENTITY=%LCUR%
*DIM SOURCECURR6:INPUTCURRENCY=USD
*DIM SOURCECURR7:RATEENTITY=GLOBAL
*DIM SOURCECURR7:INPUTCURRENCY=%LCUR%
*ENDLOOKUP
*WHEN CURRENCY
*IS CNY
*WHEN CATEGORY
*IS BUDGET
*WHEN REVENUE
*IS ASP
*REC(EXPRESSION=%VALUE%*LOOKUP(SOURCECURR1),CURRENCY=%LCUR%)
*ENDWHEN
*ENDWHEN
*ENDWHEN
*WHEN CURRENCY
*IS EUR
*WHEN CATEGORY
*IS BUDGET
*WHEN REVENUE
*IS ASP
*REC(EXPRESSION=%VALUE%*LOOKUP(SOURCECURR2),CURRENCY=%LCUR%)
*ENDWHEN
*ENDWHEN
*ENDWHEN
*WHEN CURRENCY
*IS HKD
*WHEN CATEGORY
*IS BUDGET
*WHEN REVENUE
*IS ASP
*REC(EXPRESSION=%VALUE%*LOOKUP(SOURCECURR3),CURRENCY=%LCUR%)
*ENDWHEN
*ENDWHEN
*ENDWHEN
*WHEN CURRENCY
*IS JPY
*WHEN CATEGORY
*IS BUDGET
*WHEN REVENUE
*IS ASP
*REC(EXPRESSION=%VALUE%*LOOKUP(SOURCECURR4),CURRENCY=%LCUR%)
*ENDWHEN
*ENDWHEN
*ENDWHEN
*WHEN CURRENCY
*IS SGD
*WHEN CATEGORY
*IS BUDGET
*WHEN REVENUE
*IS ASP
*REC(EXPRESSION=%VALUE%*LOOKUP(SOURCECURR5),CURRENCY=%LCUR%)
*ENDWHEN
*ENDWHEN
*ENDWHEN
*WHEN CURRENCY
*IS USD
*WHEN CATEGORY
*IS BUDGET
*WHEN REVENUE
*IS ASP
*REC(EXPRESSION=%VALUE%*LOOKUP(SOURCECURR6),CURRENCY=%LCUR%)
*ENDWHEN
*ENDWHEN
*ENDWHEN
*WHEN CURRENCY
*IS LC
*WHEN CATEGORY
*IS BUDGET
*WHEN REVENUE
*IS ASP
*REC(EXPRESSION=%VALUE%*LOOKUP(SOURCECURR7),CURRENCY=GC)
*ENDWHEN
*ENDWHEN
*ENDWHEN
*COMMIT
Regards
Silu
User | Count |
---|---|
16 | |
4 | |
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.