GL Distribution : apply to a selection of accounts

[Nicolas Micoud]

Hi,

We have the following use case.
For 3 BPartners and 7 accounts, we need to use GL Distribution.
And each distribution will be 100% to another account.

Eg: Any Sales account for Joe Block should go to account 55555 ; any sales account for C&W should go to 55556, ...

Current solution is to have too many records in the table, which will be a pain to maintain.

My idea was to change the current Account_ID field by a MultipleSelectionTable one.

At the end, there would be only one distribution per BPartner.

I found this pending ticket https://idempiere.atlassian.net/browse/IDEMPIERE-4248

 ; which should do the job, but is not really user friendly - accountants will never write SQL :) but it has the advantage is that it can be used for any dimension.

Question is: what would be the best solution:

#1 : implement ticket submitted by Marco Longo

#2 : change the type of AccountID (or add another field ?) - Not sure it makes sense to change other fields

#3 : implement #1 & #2 as it give a lot of versatility

wdyt?

Thanks,

Nicolas

[Carlos Antonio Ruiz Gomez]

> Eg: Any Sales account for Joe Block should go to account 55555 ; any
sales account for C&W should go to 55556, ...

This request is probably an indicator that the accountant doesn't get
the concept of the iDempiere dimensions.

Also Hierarchy can help here, if you have the three Business Partners
associated in hierarchy to some Summary BP.

But, I'm not accountant, so I could be totally wrong about that.

You said:
> accountants will never write SQL :)

And they MUST not, no end-user must be allowed to write SQL within
iDempiere, that's the reason of marking fields as advanced and making
all SQL related fields just accessible for Advanced roles (the
implementor or IT).

If an accountant (or any end-user) can configure an SQL field you would
be opening a security hole there, exploiting SQL you can get sensitive
information easily.


Now, for your question:

Marco's implementation looks good, but that means it NEEDS to be done by
the implementor or IT people.

Making Account multi, sounds like a more user friendly solution, but the
development could be difficult.  And people then would ask why the other
dimensions are not multi.


Another approach could be to use another dimension for that.
Another approach that I use, when the remapping of accounts is too big
for GL Distribution, is easier to write a few lines of an EventHandler
for that.


Anyways, if you think both are feasible, I think both can add value, so
I would vote for #3 :-D
[ But voting is cheap, writing the code is what you must take into
account, it must not become too complex ]

Regards,

Carlos Ruiz

[Nicolas Micoud]

Hi Carlos,

I think we can convince internal users to use accounting dimensions, but we won't be able to convince French certified public accountants and auditors as they want to have posting done that way. "Sad but true" :)

Our first idea was to use events, but then we thought that would be better to avoid adding another specific and would be better to use standard functionnality.

And of course, the SQL field will be set as Advanced, I was joking when writing they will never write SQL (because they don't have knowledge and because the field won't be shown)

We are still in the process of thinking about the implementation, so my question was to ask if it would be ok to include that in trunk.

I'm not sure all dimensions need to be multiple, but to be consistent, would be better to add new fields for all, as you suggest. I'm happy with that (at the end, is just some copy-paste).

But, AFAIR, that won't be possible to transform current columns to MultipleSelection as they cannot end with "_ID".
So new columns will be needed.
That means we will need to deprecate current ones and migrate data to new ones.
So I guess, a migration note will be required.

If you're happy with that, I'll update my analysis accordingly and work on it in a few months.

Regards,

Nicolas

[Heng Sin Low]

I'm not sure whether that's sufficient for your requirement but a more restricted version of  https://idempiere.atlassian.net/browse/IDEMPIERE-4248 can be implemented as context logic.

Using the example in the ticket:

@M_Product_ID.Description@='X' | @C_BPartner.Description@='Z'

--
You received this message because you are subscribed to the Google Groups "iDempiere" group.
To unsubscribe from this group and stop receiving emails from it, send an email to idempiere+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/idempiere/869ca0e5-a870-4142-a61e-e4385541e239n%40googlegroups.com.

[Nicolas Micoud]

I see this ticket too, but as it was flagged as duplicate I didn't look in detail.

If the changes I suggest are ok, I'll have a deeper look when starting to work on it.

Thanks,

[Steven Sackett]

Hi Nicolas,

Would it be possible to use the sub-account fields to record the sales data in specific account+sub account?  you may be able to convince the accountants you are not using another accounting dimension ;-)

Carlos said "Also Hierarchy can help here, if you have the three Business Partners associated in hierarchy to some Summary BP.  But, I'm not accountant, so I could be totally wrong about that."  .... many accountants like to extract data to manipulate accounting fact information in spreadsheets and other reporting tools and if you use Summary BPs  it then requires sql  (or complex 'user columns') to include Summary BPs in the exported data.

If work is done to improve GL Distribution there are other improvements that could be made ... for example a distribution could be allowed to sum to zero (as well as the existing 100%).  If you were a retailer of cars then each sale could also debit 'warranty provision expense' and credit 'warranty provision liability'  and sales to large chain stores/supermarkets could also allow debit to 'large chain store marketing allowance expense' and credit  'large chain store marketing allowance liability' ... these are quite common scenarios in my experience. 

regards

Steven

[Nicolas Micoud]

Hi Steven,

The constraint is "legal", the generated posting must be "definitive",  and accounts must appear in the same column, so I agree sub accounts would work "technically", but can't be an option in my use case.

-> No extra manipulation allowed here.

Regards,

Nicolas