Help on Script logic

[Former Member]

Hi,

I have two applications capex and finance and copy data from capex to finance .

Dimensions in finance : Account, Costctr,BU ,category ,RptCurrency, Time , DataSrc, ptype , did .

Dimensions in capex : Account, costctr,BU ,category ,RptCurrency, Time , DataSrcoth, Asset,Capvalue .

Asset & capvalue is a user-defined dimension in capex .

I want to copy the GL accounts in accounts dimension with property acctype = "AST" and finalvalue ( one member in capvalue dimension ) and datasrc = "input" from capex to to finance application.

I tried using the following script logic :

*DESTINATION_APP=finance

*SKIP_DIM=ASSET

*ADD_DIM ptype=NON_ptype

*ADD_DIM did=NON_did

*CLEAR_DESTINATION

*DESTINATION ACCOUNT="finalvalue"

*WHEN ACCOUNT

*IS "finalvalue"

*REC(EXPRESSION=%VALUE%,ACCOUNT="finalvalue",DATASRC="INPUT")

*ENDWHEN

*COMMIT

The above logic is not working .

Any inputs/suggestions on this will be helpful.

Thanks & Regards,

PSR

[Former Member · Accepted solution]

Hi ,

I changed to my code as below ..

*DESTINATION_APP=FINANCE

*SKIP_DIM=ASSET

*RENAME_DIM DATASRCOTH = DATASRC

*ADD_DIM PTYPE=NON_PTYPE

*ADD_DIM DID=NON_DID

*XDIM_MEMBERSET ACCOUNT = 60000 -

---

> SINGLE GL ACCOUNT

*XDIM_MEMBERSET DATASRCOTH = INPUT

*XDIM_MEMBERSET COSTCTR = R15000012 -

---

>SINGLE COSTCENTER

*XDIM_MEMBERSET BU = 101----

---

> SINGLE BU

*XDIM_MEMBERSET ASSET = B3101

*XDIM_MEMBERSET RPTCURRENCY = LC

*XDIM_MEMBERSET TIME =2011.TOTAL

*XDIM_MEMBERSET CAPVALUE = DEPRECIATION

*XDIM_MEMBERSET CATEGORY=BUDGET

*WHEN *

*IS *

*REC(FACTOR=1)

*ENDWHEN

*COMMIT

-

---

LOG :

Building sub-query 1

-

---

Query Type:0

Max members:

Region:

DIMENSION:ACCOUNT

60000

DIMENSION:ASSET

B3101

DIMENSION:BU

101

DIMENSION:CAPVALUE

DEPRECIATION

DIMENSION:CATEGORY

BUDGET

DIMENSION:COSTCTR

PCMC

DIMENSION:DATASRCOTH

INPUT

DIMENSION:RPTCURRENCY

LC

DIMENSION:TIME

2011.TOTAL

DIMENSION:ACCOUNT

60000

DIMENSION:DATASRCOTH

INPUT

DIMENSION:COSTCTR

R15000012

DIMENSION:BU

101

DIMENSION:ASSET

B3101

DIMENSION:RPTCURRENCY

LC

DIMENSION:TIME

2011.TOTAL

DIMENSION:CAPVALUE

DEPRECIATION

DIMENSION:CATEGORY

BUDGET

Loading TIME.TIMEID

Time to load properties:0.0 sec.

-

---

select ACCOUNT,DATASRCOTH,SIGNEDDATA

into #tblTempLogic_760789

from tblFactCapex

WHERE ACCOUNT=N'60000' AND ASSET=N'B3101' AND BU=N'101' AND CAPVALUE=N'DEPRECIATION' AND CATEGORY=N'BUDGET' AND COSTCTR=N'R15000012' AND DATASRCOTH=N'INPUT' AND RPTCURRENCY=N'LC' AND TIMEID=N'10000061'

insert into #tblTempLogic_760789 (ACCOUNT,DATASRCOTH,SIGNEDDATA)

select ACCOUNT,DATASRCOTH,SIGNEDDATA

from tblFactWBCapex

WHERE ACCOUNT=N'60000' AND ASSET=N'B3101' AND BU=N'101' AND CAPVALUE=N'DEPRECIATION' AND CATEGORY=N'BUDGET' AND COSTCTR=N'R15000012' AND DATASRCOTH=N'INPUT' AND RPTCURRENCY=N'LC' AND TIMEID=N'10000061'

and SOURCE = 0

insert into #tblTempLogic_760789 (ACCOUNT,DATASRCOTH,SIGNEDDATA)

select ACCOUNT,DATASRCOTH,SIGNEDDATA

from tblFAC2Capex

WHERE ACCOUNT=N'60000' AND ASSET=N'B3101' AND BU=N'101' AND CAPVALUE=N'DEPRECIATION' AND CATEGORY=N'BUDGET' AND COSTCTR=N'R15000012' AND DATASRCOTH=N'INPUT' AND RPTCURRENCY=N'LC' AND TIMEID=N'10000061'

select tmpTable.ACCOUNT,tmpTable.DATASRCOTH,sum(SIGNEDDATA) as SIGNEDDATA

from #tblTempLogic_760789 as tmpTable

group by tmpTable.ACCOUNT,tmpTable.DATASRCOTH

drop table #tblTempLogic_760789

-

---

Time to load Source data:0.0 sec.

No record to process

SIMULATED call 1 completed in 0.1 sec.

SIMULATED Run completed in 0.2 sec.

****************************************************************************************************

End time --->2:18:45 PM - Date:7/12/2010

[Former Member · Accepted solution]

Hi PSR,

First, please check in your CAPEX cube, whether you have any data for the combination, which you have specified in the XDIM statements.

Second, please change the REC statement to

*REC(FACTOR=2)

This is just to test the code. This should just double the amount.

Let us know what you see after the above 2 steps.

[Former Member · Accepted solution]

Hi Nilangan ,

for this combination data is there in Capex .

i changed as u specified ..after debug it shoes no records found .

-

---

*DESTINATION_APP=FINANCE

*SKIP_DIM=ASSET

*RENAME_DIM DATASRCOTH = DATASRC

*ADD_DIM PTYPE=NON_PTYPE

*ADD_DIM DID=NON_DID

*XDIM_MEMBERSET ACCOUNT = 60000

*XDIM_MEMBERSET DATASRCOTH = INPUT

*XDIM_MEMBERSET COSTCTR = R15000012

*XDIM_MEMBERSET BU = 101

*XDIM_MEMBERSET ASSET = B3101

*XDIM_MEMBERSET RPTCURRENCY = LC

*XDIM_MEMBERSET TIME =2011.TOTAL

*XDIM_MEMBERSET CAPVALUE = DEPRECIATION

*XDIM_MEMBERSET CATEGORY=BUDGET

*WHEN *

*IS *

*REC(FACTOR=2)

*ENDWHEN

*COMMIT

-

---

PLS LET ME KNOW ANY INPUTS .....

Regards,

PSr

[Former Member · Accepted solution]

Hi PSR,

Just for testing, can you please try the below, without destination_app

*XDIM_MEMBERSET ACCOUNT = 60000
*XDIM_MEMBERSET DATASRCOTH = INPUT
*XDIM_MEMBERSET COSTCTR = R15000012
*XDIM_MEMBERSET BU = 101
*XDIM_MEMBERSET ASSET = B3101
*XDIM_MEMBERSET RPTCURRENCY = LC
*XDIM_MEMBERSET TIME =2011.TOTAL
*XDIM_MEMBERSET CAPVALUE = DEPRECIATION 
*XDIM_MEMBERSET CATEGORY=BUDGET

*WHEN *
*IS *
   *REC(FACTOR=2)
*ENDWHEN

*COMMIT

Let me know how it works.

[Former Member · Accepted solution]

Hi Nilangan ,

i changed my code as u specified ,still i am facing same problem.

pls let me know any other approch .

Regards,

PSR

[Former Member · Accepted solution]

Hi PSR,

The code shared needs to be written in the CAPEX application. That piece of code should not have any other statement other than what I have mentioned. The code is supposed to fetch the records from the cube based on the selections and then multiply the values by 2.

If this is not happening, then that means there is no data for that particular selection in your cube.

*XDIM_MEMBERSET ACCOUNT = 60000
*XDIM_MEMBERSET DATASRCOTH = INPUT
*XDIM_MEMBERSET COSTCTR = R15000012
*XDIM_MEMBERSET BU = 101
*XDIM_MEMBERSET ASSET = B3101
*XDIM_MEMBERSET RPTCURRENCY = LC
*XDIM_MEMBERSET TIME =2011.TOTAL
*XDIM_MEMBERSET CAPVALUE = DEPRECIATION 
*XDIM_MEMBERSET CATEGORY=BUDGET
 
*WHEN *
*IS *
   *REC(FACTOR=2)
*ENDWHEN
 
*COMMIT

Please check the same.

[Former Member · Accepted solution]

Hi Nilangan ,

Thanks for reply .

Now i able excute the script and while debug the code it showing all records are transfered scussfully .

i optimiged the finance aplplication and login in the finance application..it showing 0 records .

any extra stpes need to perfomed ?

Regards,

PSR

[Former Member · Accepted solution]

Hi PSR,

I am not able to understand.

1. Optimize your application fully.

2. Check in your cube that you have some record for the given selection combination.

3. Run the script.

4. Optimize the application completely once again.

See how the data is getting populated. The previous records should get doubled.