How to balance allocation account

[former_member303406]

Hi,

Normally, the allocation account should balance with all opened GRPOs and returns.

Actually, in order to verify if the Allocation Account balance is

corresponding to all opened GRPOs and returns, we create a user query.

This query will list the row total (without tax) for all the opened

rows of GRPOs and returns. This seems to work fine.

But we noticed that in OINM table, there is a field called 'Allocation' that could help us to know the theoritical value in the allocation account (by doing a SUM of this field).

This field is affected in negative when a GRPO is created, which is fine. Then, the field is affected in positive when an A/P invoice is created, which is also fine since the allocation account is reset when an invoice is created based on a GRPO.

The only thing that doesn't work is when we close a GRPO without creating an invoice based on it (or a return without creating a credit memo based on it). Normally the allocation account is reset when doing this (with the journal entry). But in OINM, it is not... we should not see an amount anymore in the 'Allocation' field for the GRPO (or the Return) that is now closed .

Same thing fort he 'OINM.OpenAlloc' field... this field is not affected when closing a GRPO or a return and it should be... no ?

What is the purpose of those fields ?

And since we can't trust the contents of those fields, one of our customer is asking us what is the best way to ensure that the Allocation Account balance is equal to what it should be ?

The purpose of my question is to have a tool that will allow our customers to validate that the allocation account is okay... !! We need a tool that will show if the allocation account balance is what it should be... you understand my point ? There is no report for the allocation account...

We have a tool to verify the inventory G/L accounts... It's called "the inventory audit report". With this tool, we can compare the Inventory G/L account balance with the real inventory transactions.

So, we need a tool that will indicate to the customer that the receivings not invoiced is balancing with the allocation G/L account balance. Are you following me ?

Regards,

Luce

[Former Member · Accepted solution]

Query looks right. It can be a little bit simplified to:

SELECT 'Receipt' "Type", T0.TransID, T0.DocNum, T0.DocDate, T1.Rate, T1.ItemCode, T1.Quantity, T1.OpenQty AS OpencreQty, T1.Price,

( T1.Price * T1.opencreQty * (case when (T1.Rate = 0 OR T1.Rate IS NULL) then 1 else T1.Rate end) ) AS TotalPrice

FROM DBO.OPDN T0

INNER JOIN DBO.PDN1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN DBO.OITM T2 ON T1.ItemCode = T2.ItemCode AND T2.InvntItem = 'Y' and T1.LineStatus = 'O' AND T0.Canceled = 'N'

UNION ALL

SELECT 'Return', T0.TransID, T0.DocNum, T0.DocDate, T1.Rate , T1.ItemCode, -T1.Quantity, -T1.OpencreQty AS OpenQty, T1.Price,

(-T1.Price * T1.opencreQty*(Case when (T1.rate = 0 OR T1.rate IS NULL) then 1 else T1.Rate end)) AS TotalPrice

FROM DBO.ORPD T0

INNER JOIN DBO.RPD1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN DBO.OITM T2 ON T1.ItemCode = T2.ItemCode AND T2.InvntItem = 'Y' and T1.LineStatus = 'O' AND T0.Canceled = 'N'

Thanks,

Gordon

[former_member303406]

Hi Suda and Jeyakanthan,

Yes I know all that.

So, I should use this query to verify if the ACTUAL balance fits with what it should be ? THere is no other way of doing that ?

SELECT 'Receipt', OPDN.Canceled, OPDN.TransID, OPDN.DocStatus, OPDN.DocNum, OPDN.DocDate, PDN1.Rate, PDN1.ItemCode, PDN1.Quantity, PDN1.OpenQty AS OpencreQty, PDN1.Price,

( PDN1.Price * PDN1.opencreQty * (case when (PDN1.Rate = 0 OR PDN1.Rate IS NULL) then 1 else PDN1.Rate end) )AS TotalPrice

FROM OPDN OPDN

INNER JOIN PDN1 PDN1 ON OPDN.DocEntry = PDN1.DocEntry

INNER JOIN OITM OITM ON PDN1.ItemCode = OITM.ItemCode AND OITM.InvntItem = 'Y' and PDN1.LineStatus = 'O'

UNION ALL

SELECT 'Return', ORPD.Canceled, ORPD.TransID, ORPD.DocStatus, ORPD.DocNum, ORPD.DocDate, RPD1.Rate , rpd1.ItemCode, rpd1.Quantity, rpd1.OpencreQty AS OpenQty, RPD1.Price,

( rpd1.Price * rpd1.opencreQty (Case when (RPD1.rate = 0 OR RPD1.rate IS NULL) then 1 else rpd1.Rate end) ) -1 AS TotalPrice

FROM ORPD ORPD

INNER JOIN RPD1 RPD1 ON Orpd.DocEntry = RPD1.DocEntry

INNER JOIN OITM OITM ON RPD1.ItemCode = OITM.ItemCode AND OITM.InvntItem = 'Y' and RPD1.LineStatus = 'O'

Thanks,

Luce

[former_member187989]

As per help document

Allocation Acct

This clearing account is used as an offsetting account to the Stock account in Goods Receipt POs and A/P Credit Memos.

The balance of this G/L account reflects the total amount of opens Goods Receipt POs and Goods Returns related to items in this warehouse

Jeyakanthan

[former_member187989]

Good Question,Wonderful reply from Suda

Thanks

Jeyakanthan

[Former Member]

Hi,

I believe that allocation account balance field is only designed for B1 internal program use. If you need to utilize that field, you probably need to create a query report to filter out all manual closed documents.

Thanks,

Gordon

[former_member303406]

Hi Gordon,

Think.... probably... hum.. can someone confirm those information ?

Could you provide us a query / tool that will allow us to verify the integrity of the allocation account since the fields in OINM don't seem to be useful... ?

thanks,

Luce

[former_member583013]

Luce,

The Allocation Account is an account that tells you what your current liability is on the Goods you have received from the Vendor. This is to reflect the true position of the liabilities.

As you would have noticed, When an AP Invoice is created the allocation account is balanced to the extent of the AP Invoice amount. If the AP Invoice amount is the same as GRPO then the Allocation account is completed debited.

If you manually close a GRPO, it will hold a balance in the allocation account which reflects the value of the goods you have received for which you owe money. (liability)

Any balance on this account could be manually reconciled through a JE posting as Discounts Received / Returns, etc.

Creating a report for this can be complex depending on the extend of your transactions.

Suda