Re: Varying GL accounts based upon purchase types

[Jacopo Cappellato]

I've moved this thread to from the user to the dev list.
I did some research on how to implement this (i.e. assign specific
invoice item types for purchase invoices for drop shipped items) and
maybe the easiest way is to define a new InvoiceItemType for each of the
existing ones with DS_ prefix (or similar).
The algoritm would be:
1) using the InvoiceItemTypeMap find the invoiceItemTypeId associated to
the orderItemTypeId (as is now)
2) if it is a drop shipment, add the prefix DS_: "DS_" + invoiceItemTypeId

What do you think?

Jacopo


Dave Tenerowicz wrote:

> # 2 is a good suggestion - I'll try that and let the ml know the
> results. Option 1 would not work because the same product could be drop
> shipped, or shipped from the company warehouse.
> -Dave
>
> Jacopo Cappellato wrote:
>> As a first shot I'd say that:
>>
>> 1) you can try to use GL mappings for ProductCategories (and group
>> your products in different product categories) or
>> 2) we have to generate invoice items for drop shipped products with
>> special invoiceItemTypes and use the Gl mappings for invoiceItemTypes
>> to route them to the right accounts
>>
>> Just my 2 cents
>>
>> Jacopo
>>
>> Dave Tenerowicz wrote:
>>> Does anyone have any experience setting up OfBiz accounting so that
>>> the default GL accounts used varies according to the type of Purchase
>>> (for example purchases to replenish stock, vs purchases that are drop
>>> shipped?)
>>>
>>> For example, if a PO/Purchase Invoice is for goods for stock, use one
>>> AP account and a corresponding cost of goods. However, if the
>>> PO/Purchase Invoice is for product that was drop shipped, then use a
>>> different AP account and a different purchase COG account. And
>>> finally, if a PO/Purchase Invoice is for trans shipped goods, use a
>>> different pair of accounts. (Trans ship means one company
>>> organization issues the PO, the goods are received at a warehouse
>>> owned by another internal organization and then shipped from that
>>> warehouse to the end customer)
>>>
>>> After an hour of experimentation, I cannot find a way to get the
>>> proper results. Has anyone solved a problem like this before?
>>>
>>
>>
>>
>

[BJ Freeman]

Thinking from a accounting perspective, you have LIFO or FIFO or average
cost.
So how would you handle a Drop ship different in each of these cases.
is there a need to differentiate a Drop shipped Item.
Also there are two types of Dropship, from what I am picking up.
You become the Dropshipper, and/or you are using a Dropshipper for your
orders.
There are different ways of accounting for COGS when you have some one
dropship to fill your orders than when you ship from inventory.

My reason for taking this approach is to see if we need to evaluate the
whole structure to see if we meet the criteria to allow evalatuation for
the above.

I thinks there needs to be a more definitive way to Identify those drop
shipped for a customer, and those that Dropship to a customer from
another source.

Maybe look as assigning Warehouses (facilities) that are the Suppier
that we dropship from, and then use that as a indicator if this item is
dropshipped and how.


Jacopo Cappellato sent the following on 1/25/2008 3:52 AM:

> I've moved this thread to from the user to the dev list.
> I did some research on how to implement this (i.e. assign specific
> invoice item types for purchase invoices for drop shipped items) and
> maybe the easiest way is to define a new InvoiceItemType for each of the
> existing ones with DS_ prefix (or similar).
> The algoritm would be:
> 1) using the InvoiceItemTypeMap find the invoiceItemTypeId associated to
> the orderItemTypeId (as is now)
> 2) if it is a drop shipment, add the prefix DS_: "DS_" + invoiceItemTypeId
>
> What do you think?
>
> Jacopo
>
>
> Dave Tenerowicz wrote:
>> # 2 is a good suggestion - I'll try that and let the ml know the
>> results. Option 1 would not work because the same product could be
>> drop shipped, or shipped from the company warehouse.
>> -Dave
>>
>> Jacopo Cappellato wrote:
>>> As a first shot I'd say that:
>>>
>>> 1) you can try to use GL mappings for ProductCategories (and group
>>> your products in different product categories) or
>>> 2) we have to generate invoice items for drop shipped products with
>>> special invoiceItemTypes and use the Gl mappings for invoiceItemTypes
>>> to route them to the right accounts
>>>
>>> Just my 2 cents
>>>
>>> Jacopo
>>>
>>> Dave Tenerowicz wrote:
>>>> Does anyone have any experience setting up OfBiz accounting so that
>>>> the default GL accounts used varies according to the type of
>>>> Purchase (for example purchases to replenish stock, vs purchases
>>>> that are drop shipped?)
>>>>
>>>> For example, if a PO/Purchase Invoice is for goods for stock, use
>>>> one AP account and a corresponding cost of goods. However, if the
>>>> PO/Purchase Invoice is for product that was drop shipped, then use a
>>>> different AP account and a different purchase COG account. And
>>>> finally, if a PO/Purchase Invoice is for trans shipped goods, use a
>>>> different pair of accounts. (Trans ship means one company
>>>> organization issues the PO, the goods are received at a warehouse
>>>> owned by another internal organization and then shipped from that
>>>> warehouse to the end customer)
>>>>
>>>> After an hour of experimentation, I cannot find a way to get the
>>>> proper results. Has anyone solved a problem like this before?
>>>>
>>>
>>>
>>>
>>
>
>
>
>

[BJ Freeman]

the other way is to link to the supplier of that item to see if they are
a dropshipper

BJ Freeman sent the following on 1/25/2008 4:20 AM:

> Thinking from a accounting perspective, you have LIFO or FIFO or average
> cost.
> So how would you handle a Drop ship different in each of these cases.
> is there a need to differentiate a Drop shipped Item.
> Also there are two types of Dropship, from what I am picking up.
> You become the Dropshipper, and/or you are using a Dropshipper for your
> orders.
> There are different ways of accounting for COGS when you have some one
> dropship to fill your orders than when you ship from inventory.
>
> My reason for taking this approach is to see if we need to evaluate the
> whole structure to see if we meet the criteria to allow evalatuation for
> the above.
>
> I thinks there needs to be a more definitive way to Identify those drop
> shipped for a customer, and those that Dropship to a customer from
> another source.
>
> Maybe look as assigning Warehouses (facilities) that are the Suppier
> that we dropship from, and then use that as a indicator if this item is
> dropshipped and how.
>
>
> Jacopo Cappellato sent the following on 1/25/2008 3:52 AM:
>> I've moved this thread to from the user to the dev list.
>> I did some research on how to implement this (i.e. assign specific
>> invoice item types for purchase invoices for drop shipped items) and
>> maybe the easiest way is to define a new InvoiceItemType for each of the
>> existing ones with DS_ prefix (or similar).
>> The algoritm would be:
>> 1) using the InvoiceItemTypeMap find the invoiceItemTypeId associated to
>> the orderItemTypeId (as is now)
>> 2) if it is a drop shipment, add the prefix DS_: "DS_" + invoiceItemTypeId
>>
>> What do you think?
>>
>> Jacopo
>>
>>
>> Dave Tenerowicz wrote:
>>> # 2 is a good suggestion - I'll try that and let the ml know the
>>> results. Option 1 would not work because the same product could be
>>> drop shipped, or shipped from the company warehouse.
>>> -Dave
>>>
>>> Jacopo Cappellato wrote:
>>>> As a first shot I'd say that:
>>>>
>>>> 1) you can try to use GL mappings for ProductCategories (and group
>>>> your products in different product categories) or
>>>> 2) we have to generate invoice items for drop shipped products with
>>>> special invoiceItemTypes and use the Gl mappings for invoiceItemTypes
>>>> to route them to the right accounts
>>>>
>>>> Just my 2 cents
>>>>
>>>> Jacopo
>>>>
>>>> Dave Tenerowicz wrote:
>>>>> Does anyone have any experience setting up OfBiz accounting so that
>>>>> the default GL accounts used varies according to the type of
>>>>> Purchase (for example purchases to replenish stock, vs purchases
>>>>> that are drop shipped?)
>>>>>
>>>>> For example, if a PO/Purchase Invoice is for goods for stock, use
>>>>> one AP account and a corresponding cost of goods. However, if the
>>>>> PO/Purchase Invoice is for product that was drop shipped, then use a
>>>>> different AP account and a different purchase COG account. And
>>>>> finally, if a PO/Purchase Invoice is for trans shipped goods, use a
>>>>> different pair of accounts. (Trans ship means one company
>>>>> organization issues the PO, the goods are received at a warehouse
>>>>> owned by another internal organization and then shipped from that
>>>>> warehouse to the end customer)
>>>>>
>>>>> After an hour of experimentation, I cannot find a way to get the
>>>>> proper results. Has anyone solved a problem like this before?
>>>>>
>>>>
>>>>
>>
>>
>>
>
>
>
>

[David E Jones]

The best way to do this would be to use the parent type pattern and  
have a parent type for all of the InvoiceItemType Ids that are used  
for drop shipped order, and that is easy to apply to other things as  
well (and code can be written pretty easily to look at it).

-David


On Jan 25, 2008, at 4:52 AM, Jacopo Cappellato wrote:

> I've moved this thread to from the user to the dev list.
> I did some research on how to implement this (i.e. assign specific  
> invoice item types for purchase invoices for drop shipped items) and  
> maybe the easiest way is to define a new InvoiceItemType for each of  
> the existing ones with DS_ prefix (or similar).
> The algoritm would be:
> 1) using the InvoiceItemTypeMap find the invoiceItemTypeId  
> associated to the orderItemTypeId (as is now)
> 2) if it is a drop shipment, add the prefix DS_: "DS_" +  
> invoiceItemTypeId
>
> What do you think?
>
> Jacopo
>
>
> Dave Tenerowicz wrote:
>> # 2 is a good suggestion - I'll try that and let the ml know the  
>> results. Option 1 would not work because the same product could be  
>> drop shipped, or shipped from the company warehouse.
>> -Dave
>> Jacopo Cappellato wrote:
>>> As a first shot I'd say that:
>>>
>>> 1) you can try to use GL mappings for ProductCategories (and group  
>>> your products in different product categories) or
>>> 2) we have to generate invoice items for drop shipped products  
>>> with special invoiceItemTypes and use the Gl mappings for  
>>> invoiceItemTypes to route them to the right accounts
>>>
>>> Just my 2 cents
>>>
>>> Jacopo
>>>
>>> Dave Tenerowicz wrote:
>>>> Does anyone have any experience setting up OfBiz accounting so  
>>>> that the default GL accounts used varies according to the type of  
>>>> Purchase (for example purchases to replenish stock, vs purchases  
>>>> that are drop shipped?)
>>>>
>>>> For example, if a PO/Purchase Invoice is for goods for stock, use  
>>>> one AP account and a corresponding cost of goods. However, if the  
>>>> PO/Purchase Invoice is for product that was drop shipped, then  
>>>> use a different AP account and a different purchase COG account.  
>>>> And finally, if a PO/Purchase Invoice is for trans shipped goods,  
>>>> use a different pair of accounts. (Trans ship means one company  
>>>> organization issues the PO, the goods are received at a warehouse  
>>>> owned by another internal organization and then shipped from that  
>>>> warehouse to the end customer)
>>>>
>>>> After an hour of experimentation, I cannot find a way to get the  
>>>> proper results. Has anyone solved a problem like this before?
>>>>
>>>
>>>
>>>
>

[Jacopo Cappellato]

David,

I think I understand what you mean, but following your suggestion will
require to define special types for orderItemTypeId as well (and maybe
it is a good idea); and then create a mapping between the (drop
shipment) orderItemTypeId and the (drop shipment) invoiceItemTypeId.
This means definitely a cleaner approach but also a lot of new types and
type mappings (for orders, invoices, returns...).

What is the best way to go?

Jacopo

David E Jones wrote:

>
> The best way to do this would be to use the parent type pattern and have
> a parent type for all of the InvoiceItemType Ids that are used for drop
> shipped order, and that is easy to apply to other things as well (and
> code can be written pretty easily to look at it).
>
> -David
>
>
> On Jan 25, 2008, at 4:52 AM, Jacopo Cappellato wrote:
>
>> I've moved this thread to from the user to the dev list.
>> I did some research on how to implement this (i.e. assign specific
>> invoice item types for purchase invoices for drop shipped items) and
>> maybe the easiest way is to define a new InvoiceItemType for each of
>> the existing ones with DS_ prefix (or similar).
>> The algoritm would be:
>> 1) using the InvoiceItemTypeMap find the invoiceItemTypeId associated
>> to the orderItemTypeId (as is now)
>> 2) if it is a drop shipment, add the prefix DS_: "DS_" +
>> invoiceItemTypeId
>>
>> What do you think?
>>
>> Jacopo
>>
>>
>> Dave Tenerowicz wrote:
>>> # 2 is a good suggestion - I'll try that and let the ml know the
>>> results. Option 1 would not work because the same product could be
>>> drop shipped, or shipped from the company warehouse.
>>> -Dave
>>> Jacopo Cappellato wrote:
>>>> As a first shot I'd say that:
>>>>
>>>> 1) you can try to use GL mappings for ProductCategories (and group
>>>> your products in different product categories) or
>>>> 2) we have to generate invoice items for drop shipped products with
>>>> special invoiceItemTypes and use the Gl mappings for
>>>> invoiceItemTypes to route them to the right accounts
>>>>
>>>> Just my 2 cents
>>>>
>>>> Jacopo
>>>>
>>>> Dave Tenerowicz wrote:
>>>>> Does anyone have any experience setting up OfBiz accounting so that
>>>>> the default GL accounts used varies according to the type of
>>>>> Purchase (for example purchases to replenish stock, vs purchases
>>>>> that are drop shipped?)
>>>>>
>>>>> For example, if a PO/Purchase Invoice is for goods for stock, use
>>>>> one AP account and a corresponding cost of goods. However, if the
>>>>> PO/Purchase Invoice is for product that was drop shipped, then use
>>>>> a different AP account and a different purchase COG account. And
>>>>> finally, if a PO/Purchase Invoice is for trans shipped goods, use a
>>>>> different pair of accounts. (Trans ship means one company
>>>>> organization issues the PO, the goods are received at a warehouse
>>>>> owned by another internal organization and then shipped from that
>>>>> warehouse to the end customer)
>>>>>
>>>>> After an hour of experimentation, I cannot find a way to get the
>>>>> proper results. Has anyone solved a problem like this before?
>>>>>
>>>>
>>>>
>>>>
>>

[David E Jones]

I'm starting to wonder if doing a different account for drop-shipping  
is best done through an InvoiceItemType, from an OrderItemType, etc.

On the other hand, maybe that is best and going back to the  
OrderItemType is maybe a good idea because at the time of ordering...  
well wait.

Actually it's not at order time that drop-ship would be selected, but  
at shipment time. Or actually, maybe not. That might depend on the  
organization as some may actually do the drop-shipping allocation  
during inventory reservation, and then it would be when the order is  
placed. If it was after the order placing we could still change the  
OrderItem.orderItemTypeId to be the drop-ship variation on the  
previous orderItemTypeId.

So, maybe looking at the orderItemTypeId is the best approach. I was  
going to suggest looking at the shipmentItemTypeId somehow, but that  
is a bad idea because for drop-shipped fulfillment the company may not  
have Shipment records... so that's not a good direction to think in.

I guess coming back to the beginning: the way you described it looks  
good Jacopo, and hopefully what I've written clarifies that (as  
opposed to obscuring it ;) ).

-David


On Jan 25, 2008, at 2:01 PM, Jacopo Cappellato wrote:

> David,
>
> I think I understand what you mean, but following your suggestion  
> will require to define special types for orderItemTypeId as well  
> (and maybe it is a good idea); and then create a mapping between the  
> (drop shipment) orderItemTypeId and the (drop shipment)  
> invoiceItemTypeId.
> This means definitely a cleaner approach but also a lot of new types  
> and type mappings (for orders, invoices, returns...).
>
> What is the best way to go?
>
> Jacopo
>
> David E Jones wrote:
>> The best way to do this would be to use the parent type pattern and  
>> have a parent type for all of the InvoiceItemType Ids that are used  
>> for drop shipped order, and that is easy to apply to other things  
>> as well (and code can be written pretty easily to look at it).
>> -David
>> On Jan 25, 2008, at 4:52 AM, Jacopo Cappellato wrote:
>>> I've moved this thread to from the user to the dev list.
>>> I did some research on how to implement this (i.e. assign specific  
>>> invoice item types for purchase invoices for drop shipped items)  
>>> and maybe the easiest way is to define a new InvoiceItemType for  
>>> each of the existing ones with DS_ prefix (or similar).
>>> The algoritm would be:
>>> 1) using the InvoiceItemTypeMap find the invoiceItemTypeId  
>>> associated to the orderItemTypeId (as is now)
>>> 2) if it is a drop shipment, add the prefix DS_: "DS_" +  
>>> invoiceItemTypeId
>>>
>>> What do you think?
>>>
>>> Jacopo
>>>
>>>
>>> Dave Tenerowicz wrote:
>>>> # 2 is a good suggestion - I'll try that and let the ml know the  
>>>> results. Option 1 would not work because the same product could  
>>>> be drop shipped, or shipped from the company warehouse.
>>>> -Dave
>>>> Jacopo Cappellato wrote:
>>>>> As a first shot I'd say that:
>>>>>
>>>>> 1) you can try to use GL mappings for ProductCategories (and  
>>>>> group your products in different product categories) or
>>>>> 2) we have to generate invoice items for drop shipped products  
>>>>> with special invoiceItemTypes and use the Gl mappings for  
>>>>> invoiceItemTypes to route them to the right accounts
>>>>>
>>>>> Just my 2 cents
>>>>>
>>>>> Jacopo
>>>>>
>>>>> Dave Tenerowicz wrote:
>>>>>> Does anyone have any experience setting up OfBiz accounting so  
>>>>>> that the default GL accounts used varies according to the type  
>>>>>> of Purchase (for example purchases to replenish stock, vs  
>>>>>> purchases that are drop shipped?)
>>>>>>
>>>>>> For example, if a PO/Purchase Invoice is for goods for stock,  
>>>>>> use one AP account and a corresponding cost of goods. However,  
>>>>>> if the PO/Purchase Invoice is for product that was drop  
>>>>>> shipped, then use a different AP account and a different  
>>>>>> purchase COG account. And finally, if a PO/Purchase Invoice is  
>>>>>> for trans shipped goods, use a different pair of accounts.  
>>>>>> (Trans ship means one company organization issues the PO, the  
>>>>>> goods are received at a warehouse owned by another internal  
>>>>>> organization and then shipped from that warehouse to the end  
>>>>>> customer)
>>>>>>
>>>>>> After an hour of experimentation, I cannot find a way to get  
>>>>>> the proper results. Has anyone solved a problem like this before?
>>>>>>
>>>>>
>>>>>
>>>>>
>>>
>

[Jacopo Cappellato]

Thinking more about this...

in the sales order the "drop shipment" method is defined at ship group
level (by setting the OrderItemShipGroup.supplierPartyId field); the
same order item could be split into different ship groups, and only some
of them could be for drop shipments.
What if we don't add new orderItemTypes for drop shipments and leave
things as they are now at the order level?
Then, when we create invoice items for order items, we could try to look
at the ship group we are considering in the invoice; if it is a "drop
ship" group then we could convert the invoiceItemTypeId to a
"drop-ship"-specific variant (maybe using a naming convention as I
suggested in my first message).

Hmmm... this solution is similar to the original one, but I have the
feeling I'm missing something important.

Jacopo


David E Jones wrote:

>
> I'm starting to wonder if doing a different account for drop-shipping is
> best done through an InvoiceItemType, from an OrderItemType, etc.
>
> On the other hand, maybe that is best and going back to the
> OrderItemType is maybe a good idea because at the time of ordering...
> well wait.
>
> Actually it's not at order time that drop-ship would be selected, but at
> shipment time. Or actually, maybe not. That might depend on the
> organization as some may actually do the drop-shipping allocation during
> inventory reservation, and then it would be when the order is placed. If
> it was after the order placing we could still change the
> OrderItem.orderItemTypeId to be the drop-ship variation on the previous
> orderItemTypeId.
>
> So, maybe looking at the orderItemTypeId is the best approach. I was
> going to suggest looking at the shipmentItemTypeId somehow, but that is
> a bad idea because for drop-shipped fulfillment the company may not have
> Shipment records... so that's not a good direction to think in.
>
> I guess coming back to the beginning: the way you described it looks
> good Jacopo, and hopefully what I've written clarifies that (as opposed
> to obscuring it ;) ).
>
> -David
>
>
> On Jan 25, 2008, at 2:01 PM, Jacopo Cappellato wrote:
>
>> David,
>>
>> I think I understand what you mean, but following your suggestion will
>> require to define special types for orderItemTypeId as well (and maybe
>> it is a good idea); and then create a mapping between the (drop
>> shipment) orderItemTypeId and the (drop shipment) invoiceItemTypeId.
>> This means definitely a cleaner approach but also a lot of new types
>> and type mappings (for orders, invoices, returns...).
>>
>> What is the best way to go?
>>
>> Jacopo
>>
>> David E Jones wrote:
>>> The best way to do this would be to use the parent type pattern and
>>> have a parent type for all of the InvoiceItemType Ids that are used
>>> for drop shipped order, and that is easy to apply to other things as
>>> well (and code can be written pretty easily to look at it).
>>> -David
>>> On Jan 25, 2008, at 4:52 AM, Jacopo Cappellato wrote:
>>>> I've moved this thread to from the user to the dev list.
>>>> I did some research on how to implement this (i.e. assign specific
>>>> invoice item types for purchase invoices for drop shipped items) and
>>>> maybe the easiest way is to define a new InvoiceItemType for each of
>>>> the existing ones with DS_ prefix (or similar).
>>>> The algoritm would be:
>>>> 1) using the InvoiceItemTypeMap find the invoiceItemTypeId
>>>> associated to the orderItemTypeId (as is now)
>>>> 2) if it is a drop shipment, add the prefix DS_: "DS_" +
>>>> invoiceItemTypeId
>>>>
>>>> What do you think?
>>>>
>>>> Jacopo
>>>>
>>>>
>>>> Dave Tenerowicz wrote:
>>>>> # 2 is a good suggestion - I'll try that and let the ml know the
>>>>> results. Option 1 would not work because the same product could be
>>>>> drop shipped, or shipped from the company warehouse.
>>>>> -Dave
>>>>> Jacopo Cappellato wrote:
>>>>>> As a first shot I'd say that:
>>>>>>
>>>>>> 1) you can try to use GL mappings for ProductCategories (and group
>>>>>> your products in different product categories) or
>>>>>> 2) we have to generate invoice items for drop shipped products
>>>>>> with special invoiceItemTypes and use the Gl mappings for
>>>>>> invoiceItemTypes to route them to the right accounts
>>>>>>
>>>>>> Just my 2 cents
>>>>>>
>>>>>> Jacopo
>>>>>>
>>>>>> Dave Tenerowicz wrote:
>>>>>>> Does anyone have any experience setting up OfBiz accounting so
>>>>>>> that the default GL accounts used varies according to the type of
>>>>>>> Purchase (for example purchases to replenish stock, vs purchases
>>>>>>> that are drop shipped?)
>>>>>>>
>>>>>>> For example, if a PO/Purchase Invoice is for goods for stock, use
>>>>>>> one AP account and a corresponding cost of goods. However, if the
>>>>>>> PO/Purchase Invoice is for product that was drop shipped, then
>>>>>>> use a different AP account and a different purchase COG account.
>>>>>>> And finally, if a PO/Purchase Invoice is for trans shipped goods,
>>>>>>> use a different pair of accounts. (Trans ship means one company
>>>>>>> organization issues the PO, the goods are received at a warehouse
>>>>>>> owned by another internal organization and then shipped from that
>>>>>>> warehouse to the end customer)
>>>>>>>
>>>>>>> After an hour of experimentation, I cannot find a way to get the
>>>>>>> proper results. Has anyone solved a problem like this before?
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>
>>