transfer from periodic application to YTD application

[Former Member]

Hi,

Can anyone help me with the following:

A transfer of a budgetversion from the budgetapplication (periodic) to the finance (YTD) application

so it is for example:

from Budgetapp Category = BudgetV4

To finance app Category = Budget

Any help is welcome

[Former Member · Accepted solution]

Hi Benjanmin,

Below some example logic that does the trick.

*SELECT(%accountlist%, "[ID]", "ACCECOFYS","[link_to_consol_ic] <> 'Y'")

*xdim_memberset accecofys=%accountlist%
*xdim_memberset costcenter=<all>
*xdim_memberset datasrcS=<all>
*xdim_memberset PersEcofys=<all>
*xdim_memberset RGEcofys=<all>
*xdim_memberset groups=LC
*calculate_difference

*DESTINATION_APP =CONSOL
*SKIP_DIM = datasrcS
*SKIP_DIM = PersEcofys
*SKIP_DIM = RGEcofys

*RENAME_DIM AccEcofys = Account
*ADD_DIM Datasrc = dUpload
*ADD_DIM Intco = iNone
*ADD_DIM Finspec = sClosing

*when actbudfor 
*is "BUDGET"
    *WHEN accecofys.link_to_consol
    *IS <> ""
        *when time.monthnum
        *is "1"
           *REC(AccEcofys=AccEcofys.Link_TO_Consol)
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(1))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(2))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(3))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(4))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(5))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(6))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(7))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(8))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(9))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(10))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(11))
        *is "2"
           *REC(AccEcofys=AccEcofys.Link_TO_Consol)
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(1))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(2))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(3))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(4))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(5))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(6))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(7))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(8))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(9))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(10))
        *is "3"
           *REC(AccEcofys=AccEcofys.Link_TO_Consol)
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(1))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(2))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(3))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(4))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(5))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(6))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(7))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(8))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(9))
        *is "4"
           *REC(AccEcofys=AccEcofys.Link_TO_Consol)
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(1))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(2))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(3))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(4))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(5))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(6))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(7))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(8))
        *is "5"
           *REC(AccEcofys=AccEcofys.Link_TO_Consol)
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(1))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(2))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(3))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(4))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(5))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(6))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(7))
        *is "6"
           *REC(AccEcofys=AccEcofys.Link_TO_Consol)
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(1))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(2))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(3))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(4))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(5))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(6))
        *is "7"
           *REC(AccEcofys=AccEcofys.Link_TO_Consol)
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(1))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(2))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(3))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(4))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(5))
        *is "8"
           *REC(AccEcofys=AccEcofys.Link_TO_Consol)
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(1))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(2))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(3))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(4))
         *is "9"
           *REC(AccEcofys=AccEcofys.Link_TO_Consol)
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(1))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(2))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(3))
        *is "10"
           *REC(AccEcofys=AccEcofys.Link_TO_Consol)
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(1))
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(2))
        *is "11"
           *REC(AccEcofys=AccEcofys.Link_TO_Consol)
           *REC(AccEcofys=AccEcofys.Link_TO_Consol,time=next(1))
        *is "12"
           *REC(AccEcofys=AccEcofys.Link_TO_Consol)
        *endwhen
    *endwhen
*endwhen

*COMMIT

hope this helps,

Alwin

[Former Member]

First of all thank you for your answer

for the moment I have the following:

*FUNCTION CATEGORYDIM=CATEGORY

*FUNCTION TIMEDIM=TIME

*FUNCTION GROUPSDIM=GROUPS

*FUNCTION ENTITYDIM=ENTITY

*FUNCTION ACCOUNTDIM=ACCOUNT

*FUNCTION DATASRCDIM=DATASRC

*FUNCTION INTCODIM=INTCO

*FUNCTION ACCTDETAILDIM=ACCTDETAIL

*XDIM_MEMBERSET TIMEDIM=,%TIME_SET%

*XDIM_MEMBERSET GROUPSDIM = EUR

*XDIM_MEMBERSET INTCODIM = GLTOT

*XDIM_MEMBERSET ACCTDETAILDIM = F_CLO

*XDIM_MEMBERSET DATASRCDIM = INPUT

*DESTINATION_APP = FINANCE

*CLEAR_DESTINATION

*DESTINATION TIMEDIM=%TIME_SET%

*WHEN ACCOUNT.BUD_ACC

*IS <> ""

*REC(Factor=1,ACCOUNT=ACCOUNT.BUD_ACC,CATEGORY=BUDGET)

*ELSE

*ENDWHEN

*COMMIT

So this logic is runned in the budgetapp and the destination is Finance

the idea is that they choose the budgetversion they want to transfer in the package (this is always transferred to the BUDGET version of finance)

but it also needs a clear destination (if they for example transfer BUDGETV2 first and afterwards BUDGETV4 in the same period)

I know this is not yet finished but any help would be welcome

[Former Member]

You have to add the following call just below the other *destination line:

*destination categorydim=budget

And create a ssis package that requests a category dimension as a source. Link this package to this logic file and it should work.

Alwin

[Former Member]

thanks again,

I have already data now but I found two problems:

- some accounts are not filled in as they are parenths, how can I define that they have to send the data to the member specified in the bud_acc property (I tried it in the logic but seems to fail)

- if I have 10 in march and 20 in june (periodic) from the budget and I transfer it I have the same values but in YTD --> the 20 should be 30 YTD

*FUNCTION CATEGORYDIM=CATEGORY

*FUNCTION TIMEDIM=TIME

*FUNCTION GROUPSDIM=GROUPS

*FUNCTION ENTITYDIM=ENTITY

*FUNCTION ACCOUNTDIM=ACCOUNT

*FUNCTION DATASRCDIM=DATASRC

*FUNCTION INTCODIM=INTCO

*FUNCTION ACCTDETAILDIM=ACCTDETAIL

*FUNCTION SITESDIM=SITES

*XDIM_MEMBERSET TIMEDIM=%TIME_SET%

*XDIM_MEMBERSET GROUPSDIM = EUR

*XDIM_MEMBERSET INTCODIM = GLTOT

*XDIM_MEMBERSET ACCTDETAILDIM = F_CLO

*XDIM_MEMBERSET DATASRCDIM = INPUT

*XDIM_MEMBERSET ENTITYDIM=%ENTITY_SET%

*CLEAR_DESTINATION

*DESTINATION TIMEDIM=%TIME_SET%

*DESTINATION CATEGORYDIM=BUDGET

*DESTINATION ENTITYDIM=%ENTITY_SET%

*DESTINATION SITESDIM=S_NONE

*DESTINATION_APP = FINANCE

*WHEN ACCOUNT.TRANSFER

*IS <> ""

*REC(Factor=1,ACCOUNT=ACCOUNT.BUD_ACC,CATEGORY=BUDGET)

*ELSE

*ENDWHEN

*COMMIT

[Former Member]

Hi Benjamin,

To realize the accumulation of periodic values to ytd values you need something as I posted in my first reply;

test the current record for the monthnum property value and then post this record based on this value to the current period and all future periods of the current year in the ytd destination app.

I think that the best way to put a value of a calculated source account to a basemember destination account is to create a property in the source account dimension. Than fill all basemembers that roll up into the calculated source accounts with a value of the destination account.

For both questions see also my first reply where I handle both suggestions.

Alwin