Apa khabar (that's "how are you" in Malay in case you're wondering)? Hope you've enjoyed reading my maiden blogpost (Reverse Mapping using Hierarchy-Driven SmartLists). Keeping to the theme, thought I'd share another use of a Hierarchy-Driver Smartlist; this time in an allocation process.
More often than not, you'll find yourself building some sort of an allocation logic for your planning application and as you know, there's more than one way to skin a cat. My latest go-to method is using @XWRITE in conjunction with a Hierarchy-Driven Smartlist. Not only does it help in addressing the ever-annoying block creation issue (we had our fair share of rants on this matter don't we, Pete?), using a SmartList provides the ability to avoid any kind of hard-coding in the business rules.
Interested? Read on...
Background
- Entity
- Profit Centre
- Function Code
- Account
- Allocation %
The UV_Assessment_Cycle (in the POV) allows the client to maintain multiple cycles (or tiers) of allocation while the rows provides the ability to have multiple lines of allocation per cycle. For instance, L001 defines a 100% allocation of cost
- From
- Entity: 4650
- Profit Centre: RAM RNY
- Function Code: 590 - COMPUTER SYSTEMS
- Account: OH2
- To
- Entity: 4550
- Profit Centre: MGR RAM
- Function Code: 590 - COMPUTER SYSTEMS
- Account: Overheads Pooling
Having this mapping table frees us from having to hard-code our allocation business rules.
Define a hierarchy-driven smartlist for each of the following:
- Sender/Receiver Entity
- Sender/Receiver Profit Centre
- Sender/Receiver Function Code
- Sender Account
- Receiver Account
Accounts / Measures
Create the following accounts or measures and attach the above smartlists:
- Sender_Entity - SL_Ent
- Sender_Account - Cycle_Sender_Acc
- Sender_Profit_Centre - SL_PC
- Sender_Function_Code - SL_FC
- Receiver_Entity - SL_Ent
- Receiver_Account - Cycle_Receiver_Acc
- Receiver_Profit_Centre - SL_PC
- Receiver_Function_Code - SL_FC
Business Rule
The business rule will cycle through all the Assessment Cycles and Assessment Cycle Lines to the post a negative entry to the sender Entity/Profit Centre/Function Code/Account combinations and posts a positive entry to the receiver combinations. A sample of the results is illustrated in the table below:
This is the full allocation code:
Say what? Okay, okay. Let's examine the section where we used the Hierarchy-Driven Smartists to first create the required block and then post the allocated numbers. We'll start with the posting to the source combinations:
Notice that the rule uses the XWRITE function to post the value from the Sender Entity, Profit Centre, Function Code, and SenderAccount combination (section 1) to the Sender Entity, Profit Centre, Function Code, and Receiver Account combination (section 2). What's cool about this is that the XWRITE will first create the block for the Sender Entity, Profit Centre, Function Code, and Receiver Account combination if it does not exist before the posting happens.
Also note that we have used the method of concatenating "HSP_ID_" with the value from the Hierarchy-Driven SmartList to resolve to the intended member.
Here's the code snippet for the receiving entry and with similar method used when pushing the Sender Entry:
The last part of the rule applies the allocation ratio to both the sender and receiver entries and flips the sign for the sender entry:
Recap
This solution allows users to define their allocation mapping without having to rely on a hard-coded business rule (all thanks to the Hierarchy-Driven Smartlists). The rule will cycle through 2 dimensions (i.e. the Assessment Cycle and the Assessment Cycle lines) when allocating costs between sender and receiver Entity, Profit Centre, Function Code, and Account combinations. An example of the allocation result is illustrated in the table below:
Hope you have enjoyed this blog and stay tuned for my next blogpost. Till then, terima kasih!
No comments:
Post a Comment