Script Logic - Writing to Base members

[Former Member]

Hi Experts,

I really need help.

I need to create a script that looks at a customer parent, then write to each customer in that parent.

My code looks like this:

*XDIM_MEMBERSET TIME = 2015.JAN

*XDIM_MEMBERSET SCENARIO = SC_DUMMY

*XDIM_MEMBERSET ACCOUNT = PROD_LIST

*XDIM_MEMBERSET ACTIVITY = NO_ACT

*XDIM_MEMBERSET CHANNEL = CH_DUMMY

*XDIM_MEMBERSET CURRENCY = LC

*XDIM_MEMBERSET ORGANIZATION = PL_D

*XDIM_MEMBERSET DATASRC = TECH

*SELECT(%PRO%,"[ID]",PRODUCT,"[CALC]='N' AND [IS_DUMMY]='' AND [PR_FORMAT]='A' AND [PR_FORMAT]='B'")

*XDIM_MEMBERSET PRODUCT = %PRO%

*WHEN CUSTOMER

*IS PL0095001706_D

        *XDIM_MEMBERSET CUSTOMER = BAS(PL0095001706)

        *REC(EXPRESSION=%VALUE%)

*ENDWHEN

My problems are:

1. It does not write to the base members of PL0095001706, it finds them, but doesn't do anything to them.

2. We would prefer if the code could be more dynamic, thus not saying when the customer is PL0095001706_D then write to the base members of PL0095001706. Instead we want something in the line of:

*XDIM_MEMBERSET TIME = 2015.JAN

*XDIM_MEMBERSET SCENARIO = SC_DUMMY

*XDIM_MEMBERSET ACCOUNT = PROD_LIST

*XDIM_MEMBERSET ACTIVITY = NO_ACT

*XDIM_MEMBERSET CHANNEL = CH_DUMMY

*XDIM_MEMBERSET CURRENCY = LC

*XDIM_MEMBERSET ORGANIZATION = PL_D

*XDIM_MEMBERSET DATASRC = TECH

*SELECT(%PRO%,"[ID]",PRODUCT,"[CALC]='N' AND [IS_DUMMY]='' AND [PR_FORMAT]='A' AND [PR_FORMAT]='B'")

*XDIM_MEMBERSET PRODUCT = %PRO%

*WHEN CUSTOMER

*IS <> ""   

        *XDIM_MEMBERSET CUSTOMER = BAS([CUSTOMER])

        *SELECT(%CUST%,"[ID]",CUSTOMER,"[CALC]='N' AND [IS_DUMMY]=''")

        *XDIM_MEMBERSET CUSTOMER = %CUST%

        *REC(EXPRESSION=%VALUE%, CUSTOMER= %CUST%)

*ENDWHEN

*COMMIT

Unfortunately, this does not work, I want to look at all customers, get the base of the parent, and write to the children.

I should also mention that PL0095001706_D is a dummy customer, we created it purely to be able to post data to it (as you cannot post to a parent).

PL0095001706 is the customer parent of 5 customers.

We want the script to look at the customer, and write the exact same info from PL0095001706_D to the base members.

I hope this makes sense, I would be delighted with your response.

I have been busy with this for more than a week, and I can't seem to get it right.

Thanks,

Jaco de Kock

[former_member186338 · Accepted solution]

Hi Jaco,

Looks like you have to read

Please, answer as many questions as you can!

Anyway, let's assume that the user will select number of parent customers like PL0095001706 and store the list in %CUSTOMER_SET%

Then:

*FOR %PARENTCUST%=%CUSTOMER_SET% // PL0095001706,...

//*XDIM_MEMBERSET ... = ... // All XDIM's here!

*RUNALLOCATION

*FACTOR=1

*DIM CUSTOMER WHAT=%PARENTCUST%_D; WHERE=BAS(%PARENTCUST%)

*ENDALLOCATION // this statement will reset the scope!

*NEXT

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Wow! Thanks,

I will test this, if it works how we want it to then I will mark it as the correct answer.

I just want to make sure that if the value in the parent is 100 for example and there is 10 children from the parent, the children have a value of 100 as well.

Thanks a million!

Jaco

[Former Member · Accepted solution]

Hi Vadim,

Is there any way to exclude one specific customer from the list?

CU_DUMMY does not have a parent, thus it fails with the error:

RUN_LOGIC:Member "CU_DUMMY_D" not exist

I tried some few options like:

*FOR %PARENTCUST%=%CUSTOMER_SET% AND ID <> CU_DUMMY

Any suggestions?

Thanks,

Jaco

[former_member186338 · Accepted solution]

"Is there any way to exclude one specific customer from the list?" - can you explain the details???

Howe you get CU_DUMMY_D in %CUSTOMER_SET%???

Please, answer

Vadim

[Former Member · Accepted solution]

Hi Vadim,

I used the logic as you as you explained:

We created the "Dummy Customers" to be able to plan on the hierarchy node...

We added a "_D" to every node in the original hierarchy.

I believe the code is looking for the Dummy Customer with the "_D" because of the this:

*DIM CUSTOMER WHAT=%PARENTCUST%_D; WHERE=BAS(%PARENTCUST%) - The _D is the problem because no dimension member exist for CU_Dummy_D.

I did not test the logic by entering a value for CU_Dummy, I used a customer that has children.

Cu_Dummy is included in the list because of:

FOR %PARENTCUST%=%CUSTOMER_SET%

Thus we are taking all customers in the customer set....

Hope this clarifies the situation. Would you like to see the entire code as used n the script file?

Thanks,

Jaco.

[former_member186338 · Accepted solution]

Sorry, I can't understand your explanation

Do you understand what I am asking:

Please, answer How To: Ask questions about Script Logic issues!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Vadim

[Former Member · Accepted solution]

Hi Vadim,

I hope this helps:

1.

2. I get a Runtime error?

3. We have 24 Parent customers. The business requirement is to plan on Node level, copying the data from the parent to children with a relationship 1:1. Meaning that if the Application Customers with a parent (PL0095001717) has a value of 1 000, then all of the children must have the same value.

I therefore created a hierarchy duplicate. I took all the same parent customers and added a “_D” at base level. Thus it is possible to plan for (PL0095001717_D). PL0095001706 is one of the 23 children of the parent PL0095001717.

I want the script logic to take the value of (PL0095001706_D) to copy to its children. The customer can be any customer, and the number of children can vary.

Other applications will not be effected, they will stay the same as is.

4. The purpose of the script:  The business requirement is to plan on Node level, copying the data from the parent to children. We do not want to use the default.lgf, there is so many calculations already taking place, including calculations and allocations. Therefore I have a VB code that calls the data manager package as soon as data is saved on that specific input sheet, it must only execute when it is on that input sheet.

5. My code:

*FOR %PARENTCUST%=%CUSTOMER_SET%

*XDIM_MEMBERSET TIME = 2015.JAN

*XDIM_MEMBERSET SCENARIO = SC_DUMMY

*XDIM_MEMBERSET ACCOUNT = PROD_LIST

*XDIM_MEMBERSET ACTIVITY = NO_ACT

*XDIM_MEMBERSET CHANNEL = CH_DUMMY

*XDIM_MEMBERSET CURRENCY = LC

*XDIM_MEMBERSET ORGANIZATION = PL_D

*XDIM_MEMBERSET DATASRC = TECH

*SELECT(%PRO%,"[ID]",PRODUCT,"[CALC]='N' AND [IS_DUMMY]='' AND [PR_FORMAT]='A' AND [PR_FORMAT]='B'") //Call all products that is not Dummy products.

*XDIM_MEMBERSET PRODUCT = %PRO%

*RUNALLOCATION

*FACTOR=1 //One to one relationship…. Same value must be used.

*DIM CUSTOMER WHAT=%PARENTCUST%_D; WHERE=BAS(%PARENTCUST%)

*ENDALLOCATION // this statement will reset the scope!

*NEXT

*COMMIT

6. I get this error when data is saved in BPC from the input sheet:

RUN_LOGIC:Member "CU_DUMMY_D" not exist.

Thus I would like to exclude the member CU_DUMMY from the scope, I don’t want it to look at CU_DUMMY_D.

7. User does not have to select anything, the VB code looks at what the current scope is of the input sheet, and sends it to the DM. Thus they don’t even get a popup for the run package. It just says the run package has started successfully.

8. I get this error in UJKT:

UJK_VALIDATION_EXCEPTION:Dimension set:"CUSTOMER" not assigned in Data Manager

9. After saving the data to PL0095001706_D, the children is empty due to the error in script, so I don’t believe a screenshot is essential.

10. No advanced DM scripts. It is just the Allocation I am trying to run.

Hope this is everything you need.

I appreciate your help regarding this matter.

Jaco.

[former_member186338 · Accepted solution]

Hi Jaco,

Please, don't try to do everything simultaneously including VBA code etc...

First debug in UJKT!

"UJK_VALIDATION_EXCEPTION:Dimension set:"CUSTOMER" not assigned in Data Manager" means that you have serious issues with the script.

Please provide UJKT screenshots! I am not sure that you are executing UJKT correctly.

Vadim

P.S. Also, please launch in UJKT only 2 lines:

*SELECT(%PRO%,"[ID]",PRODUCT,"[CALC]='N' AND [IS_DUMMY]='' AND [PR_FORMAT]='A' AND [PR_FORMAT]='B'"

*XDIM_MEMBERSET PRODUCT = %PRO%

and show the result

[former_member200327 · Accepted solution]

Hi Jaco,

Why your VBA code put CU_DUMMY in selection list?

Gersh

[Former Member · Accepted solution]

Hi

No we don't, the vba just calls the package with the selections set to the input schedule.

Regards,

Jaco

[Former Member · Accepted solution]

Hi Vadim,

I am only debugging in UJKT, I am not running it from the input sheet, I would like to test it before I move to the input sheet.

Here is the screenshot from UJKT:

Secondly, you said I must just run the product selection in UJKT:

The product Selection works perfectly, I still do not understand the error :"CUSTOMER" not assigned in Data Manager, what does that mean. How can it be assigned? What do I need to change?

[former_member186338 · Accepted solution]

Unfortunately on the first screenshot you show only part of the screen, without contents of Data Region!

Please show it

Vadim

P.S. Don't use *COMMIT - absolutely useless!

[Former Member · Accepted solution]

Vadim, I left that section blank?

I tried this:

*XDIM_MEMBERSET TIME = 2015.JAN

*XDIM_MEMBERSET SCENARIO = SC_DUMMY

*XDIM_MEMBERSET ACCOUNT = PROD_LIST

*XDIM_MEMBERSET ACTIVITY = NO_ACT

*XDIM_MEMBERSET CHANNEL = CH_DUMMY

*XDIM_MEMBERSET CURRENCY = LC

*XDIM_MEMBERSET ORGANIZATION = PL_D

*XDIM_MEMBERSET DATASRC = TECH

*SELECT(%PRO%,"[ID]",PRODUCT,"[CALC]='N' AND [IS_DUMMY]='' AND [PR_FORMAT]='A' AND [PR_FORMAT]='B'")

*XDIM_MEMBERSET PRODUCT = %PRO%

*WHEN CUSTOMER

*IS PL0095001706_D

        *REC(FACTOR=1, CUSTOMER=PL0094000000)

*IS PL0095001708_D

        *REC(FACTOR=1, CUSTOMER=PL0094000002)

*IS PL0095001709_D

        *REC(FACTOR=1, CUSTOMER=PL0094000001)

        *REC(FACTOR=1, CUSTOMER=PL0094000003)

        *REC(FACTOR=1, CUSTOMER=PL0094000004)

*IS PL0095001711_D

        *REC(FACTOR=1, CUSTOMER=PL0094000005)

*IS PL0095001712_D

        *REC(FACTOR=1, CUSTOMER=PL0094000006)

*IS PL0095001713_D

        *REC(FACTOR=1, CUSTOMER=PL0094000007)

        *REC(FACTOR=1, CUSTOMER=PL0094000090)

*IS PL0095001714_D

        *REC(FACTOR=1, CUSTOMER=PL0094000008)

*IS PL0095001715_D

        *REC(FACTOR=1, CUSTOMER=PL0094000009)

*IS PL0095001716_D

        *REC(FACTOR=1, CUSTOMER=PL0094000010)

*IS PL0095001717_D

        *REC(FACTOR=1, CUSTOMER=PL0094000011)

        *REC(FACTOR=1, CUSTOMER=PL0094000061)

        *REC(FACTOR=1, CUSTOMER=PL0094000062)

        *REC(FACTOR=1, CUSTOMER=PL0094000063)

        *REC(FACTOR=1, CUSTOMER=PL0094000064)

        *REC(FACTOR=1, CUSTOMER=PL0094000065)

        *REC(FACTOR=1, CUSTOMER=PL0094000066)

        *REC(FACTOR=1, CUSTOMER=PL0094000067)

        *REC(FACTOR=1, CUSTOMER=PL0094000068)

        *REC(FACTOR=1, CUSTOMER=PL0094000069)

        *REC(FACTOR=1, CUSTOMER=PL0094000070)

        *REC(FACTOR=1, CUSTOMER=PL0094000071)

        *REC(FACTOR=1, CUSTOMER=PL0094000072)

        *REC(FACTOR=1, CUSTOMER=PL0094000073)

        *REC(FACTOR=1, CUSTOMER=PL0094000074)

        *REC(FACTOR=1, CUSTOMER=PL0094000075)

        *REC(FACTOR=1, CUSTOMER=PL0094000076)

        *REC(FACTOR=1, CUSTOMER=PL0094000077)

        *REC(FACTOR=1, CUSTOMER=PL0094000078)

        *REC(FACTOR=1, CUSTOMER=PL0094000079)

        *REC(FACTOR=1, CUSTOMER=PL0094000095)

        *REC(FACTOR=1, CUSTOMER=PL0094000176)

        *REC(FACTOR=1, CUSTOMER=PL0094000177)

*IS PL0095001718_D

        *REC(FACTOR=1, CUSTOMER=PL0094000012)

*IS PL0095001719_D

        *REC(FACTOR=1, CUSTOMER=PL0094000013)

*IS PL0095001720_D

        *REC(FACTOR=1, CUSTOMER=PL0094000014)

*IS PL0095001721_D

        *REC(FACTOR=1, CUSTOMER=PL0094000015)

        *REC(FACTOR=1, CUSTOMER=PL0094000016)

        *REC(FACTOR=1, CUSTOMER=PL0094000055)

        *REC(FACTOR=1, CUSTOMER=PL0094000060)

*IS PL0095001722_D

        *REC(FACTOR=1, CUSTOMER=PL0094000018)

*IS PL0095001723_D

        *REC(FACTOR=1, CUSTOMER=PL0094000020)

*IS PL0095001724_D

        *REC(FACTOR=1, CUSTOMER=PL0094000022)

*IS PL0095001725_D

        *REC(FACTOR=1, CUSTOMER=PL0094000023)

        *REC(FACTOR=1, CUSTOMER=PL0094000024)

        *REC(FACTOR=1, CUSTOMER=PL0094000025)

        *REC(FACTOR=1, CUSTOMER=PL0094000026)

        *REC(FACTOR=1, CUSTOMER=PL0094000027)

        *REC(FACTOR=1, CUSTOMER=PL0094000028)

        *REC(FACTOR=1, CUSTOMER=PL0094000029)

*IS PL0095001726_D

        *REC(FACTOR=1, CUSTOMER=PL0094000030)

*IS PL0095001727_D

        *REC(FACTOR=1, CUSTOMER=PL0094000031)

*IS PL0095001728_D

        *REC(FACTOR=1, CUSTOMER=PL0094000032)

*IS PL0095001736_D

        *REC(FACTOR=1, CUSTOMER=PL0094000033)

*IS PL0095001859_D

        *REC(FACTOR=1, CUSTOMER=PL0094000037)

*IS PL0095001875_D

        *REC(FACTOR=1, CUSTOMER=PL0094000035)

*IS PL0095001880_D

        *REC(FACTOR=1, CUSTOMER=PL0094000038)

*IS PL0095001881_D

        *REC(FACTOR=1, CUSTOMER=PL0094000039)

*IS PL0095001882_D

        *REC(FACTOR=1, CUSTOMER=PL0094000040)

*IS PL0095001883_D

        *REC(FACTOR=1, CUSTOMER=PL0094000041)

*IS PL0095001884_D

        *REC(FACTOR=1, CUSTOMER=PL0094000042)

*IS PL0095001885_D

        *REC(FACTOR=1, CUSTOMER=PL0094000043)

*IS PL0095001886_D

        *REC(FACTOR=1, CUSTOMER=PL0094000044)

*IS PL0095001887_D

        *REC(FACTOR=1, CUSTOMER=PL0094000045)

*IS PL0095001888_D

        *REC(FACTOR=1, CUSTOMER=PL0094000046)

*IS PL0095001891_D

        *REC(FACTOR=1, CUSTOMER=PL0094000049)

*IS PL0095001964_D

        *REC(FACTOR=1, CUSTOMER=PL0094000050)

*IS PL0095001966_D

        *REC(FACTOR=1, CUSTOMER=PL0094000051)

*IS PL0095001967_D

        *REC(FACTOR=1, CUSTOMER=PL0094000085)

*IS PL0095001985_D

        *REC(FACTOR=1, CUSTOMER=PL0094000052)

        *REC(FACTOR=1, CUSTOMER=PL0094000056)

        *REC(FACTOR=1, CUSTOMER=PL0094000057)

        *REC(FACTOR=1, CUSTOMER=PL0094000058)

        *REC(FACTOR=1, CUSTOMER=PL0094000059)

*IS PL0095002025_D

        *REC(FACTOR=1, CUSTOMER=PL0094000080)

*IS PL0095002058_D

        *REC(FACTOR=1, CUSTOMER=PL0094000091)

*IS PL0095004092_D

        *REC(FACTOR=1, CUSTOMER=PL0094000172)

*IS PL0095004093_D

        *REC(FACTOR=1, CUSTOMER=PL0094000173)

*IS PL0095004094_D

        *REC(FACTOR=1, CUSTOMER=PL0094000174)

*IS PL0095004095_D

        *REC(FACTOR=1, CUSTOMER=PL0094000175)

*ENDWHEN

*COMMIT

With the blank Data Region it still executes… And it works perfectly; it does exactly what I want it to do. The problem with this script is, it will take maintenance. If a new customer group is introduced, this must be included in the script.

The logic above is the last resort for this to work.

[former_member186338 · Accepted solution]

Hi Jaco,

"Vadim, I left that section blank?" - and this is the issue!

In Data Region you have to define members of dimensions the same way the user will be prompted. Or you have to explain the logic how to SELECT the required CUSTOMER parent member list.

Sample:

Data Region:

CUSTOMER=PL0095001706,PL0095001708,PL0095001709

Script:

//SELECT has to be placed at the beginning

*SELECT(%PRO%,"[ID]",PRODUCT,"[CALC]='N' AND [IS_DUMMY]='' AND [PR_FORMAT]='A' AND [PR_FORMAT]='B'") //Call all products that is not Dummy products.

*FOR %PARENTCUST%=%CUSTOMER_SET%  //%CUSTOMER_SET% will come from Data Region

*XDIM_MEMBERSET TIME = 2015.JAN

*XDIM_MEMBERSET SCENARIO = SC_DUMMY

*XDIM_MEMBERSET ACCOUNT = PROD_LIST

*XDIM_MEMBERSET ACTIVITY = NO_ACT

*XDIM_MEMBERSET CHANNEL = CH_DUMMY

*XDIM_MEMBERSET CURRENCY = LC

*XDIM_MEMBERSET ORGANIZATION = PL_D

*XDIM_MEMBERSET DATASRC = TECH

*XDIM_MEMBERSET PRODUCT = %PRO%

*RUNALLOCATION

*FACTOR=1 //One to one relationship…. Same value must be used.

*DIM CUSTOMER WHAT=%PARENTCUST%_D; WHERE=BAS(%PARENTCUST%)

*ENDALLOCATION // this statement will reset the scope!

*NEXT

Vadim

[former_member200327 · Accepted solution]

Hi Jaco,

If DM Package doesn't specify Customer Dimension it runs on ALL, including CU_DUMMY. So, if you want it to run on changed only Customers you need to put this logic in DEFAULT.LGF, not DM Package.

For the UJKT to work you have to specify on which Customers it should run.

Regards,

Gersh

[Former Member · Accepted solution]

Hi Vadim,

Thanks a million!

It worked perfectly.

Thanks for the help!

Jaco de Kock