Cross Applications - Logic

[frederico_lapa]

Hi All!

I am trying to use a script for calculate a new record with the cost of goods (quantity * unit cost). I am using two applications: sales and prices_costs with the following dimensions:

- Sales (category,customer,entity,family,rptcurrency,salesaccount,salesorg,subfamily1,time)

- Prices_Costs (category,customer,entity,family,salesaccount,time)

Here it is my script logic (located in the default.LGF of sales application):

*LOOKUP PRICES_COSTS

*DIM COGSAMOUNT:SALESACCOUNT="COGSAMOUNT"

*DIM BUDGET:CATEGORY="BUDGET"

*DIM NA:ENTITY="NA"

*DIM NA:CUSTOMER="NA"

*ENDLOOKUP

*SKIP_DIM=RPTCURRENCY,SALESORG,SUBFAMILY1

*WHEN SALESACCOUNT

*IS "COGSAMOUNT"

REC(EXPRESSION=%VALUE%GET(SALESACCOUNT="COGSAMOUNT"))

*ENDWHEN

*COMMIT

But this is not working...

Any help is appreciated!

Regards, Fred

[Former Member · Accepted solution]

Hi Frederico,

Your lookup syntax is not quite right, and you may need to define your main or source data region, which must come from the application in which this logic is the default logic -- your sales app.

For the lookup, you only need to define a label (to the left of the colon) for those *DIM entries that are being referenced later on in a LOOKUP() inside the *REC. It's a little hard to explain, and you actually can use the same label for multiple *DIM lines. The example below should work.

*LOOKUP PRICES_COSTS

// don't you want to get the unit cost (not cogs amount) from the prices_costs app?
*DIM MyLabel:SALESACCOUNT="UnitCost" 

*DIM CATEGORY="BUDGET"
*DIM ENTITY="NA"
*DIM CUSTOMER="NA"
*ENDLOOKUP

// skip dim doesn't work in a lookup, only in a *destination_app
// it's not required here, although you may want or need to fix an *XDIM_MEMBER SalesOrg=MySalesOrg ??
// *SKIP_DIM=RPTCURRENCY,SALESORG,SUBFAMILY1


*WHEN SALESACCOUNT
// to multiply quantity * unit cost, you start with the quantity account from the main sales app
*IS "quantity"
*REC(EXPRESSION=%VALUE%*Lookup(MyLabel))

// the following will work equally well, and takes a few less seconds to type... =)
// *REC(Factor=Lookup(MyLabel))

*ENDWHEN

*COMMIT

Note that this code won't recalculate things in the sales app on the fly, if you change the unit cost in the prices_costs app. There are ways to do that, using *RUNLOGIC in the prices_costs default logic to call the sales app's default logic, but don't get involved in that complexity if you can avoid it.

Also note that you can only have 1 lookup in a commit section, so this limits your flexibility in case you have a really complex cogs situation (or lots of other things to do in your default logic, and you want to limit the number of commits). One way around that is to bring the unit cogs account into the main sales app, which may have both benefits and costs (pardon the pun) in terms of overall design. If all the data is in one app, you can get fancy with your XDIM_MEMBERSET to get the unit cost account into the source region, and then use a GET() rather than a LOOKUP() in your *REC statement, to use it in the calc.

Performance-wise, you might also consider limiting the account dim to exactly what you need, if this is the only logic in the *COMMIT block.

*XDIM_MEMBER SalesAccount =quantity

// lookup code goes here

*WHEN *
*IS *
*REC(Factor=Lookup(MyLabel))
*ENDWHEN

[frederico_lapa]

Dear Experts,

Thank you all.

Tim your answer was just perfect. Your code worked out!!

Best Regards,

Frederico

[Former Member]

I was working on using one App's data and record to another App... The only difference between your codes and mine is

1) I run as a package and specify the parameters there rather than using LOOKUP

2) I defined destination_app, the same grammar from the Help file...

*DESTINATION_APP = {app name}

*SKIP_DIM= {dimension name}[,{dimension name},u2026]

*ADD_DIM {dimension name}=[,{dimension name}=,u2026]

*RENAME_DIM {dimension name}=[,{dimension name}=,u2026]

Hope this helps...