Paul Foxworthy wrote:
Hi David,
Many thanks for your comments.
My responses inline....
Cheers
Paul Foxworthy
On Apr 2, 2011, at 5:41 AM, Paul Foxworthy wrote:
Status History
---------------------
I can see the sense in having one repository for all change tracking. We
just need to bear in mind there are more use cases for using the information
in the repository than “auditing”. Auditing is a security activity, and it
will be security professionals looking at a history of who did what. Looking
at a status history could well be a business activity with no security
dimension to it. So viewing the history of changes to an order’s status
might be something a manager would do.
We would need to structure permissions so that people who need some of the
information for business purposes can use it even though they are not
security professionals. The other thing to observe is that status history is
currently *on* by default in Ofbiz, and auditing is *off* by default.
The "audit log" is a pretty general term and does not imply restricted
access to the information. Control of access to any database information is
up to the application developer, and nothing about the term "audit log"
prevents that.
Cool. Do you think it would be a good idea to have limited auditing enabled
from the beginning in a demo/starter system, the rough equivalent of the
current status history?
IMO, this could be interesting as a POC demo. Could be used on Example entity
only for instance.
QuantityBreak
---------------------
No, don’t get rid of the QuantityBreak entity, and it should be used as part
of pricing. Many organisations will have standard QuantityBreaks that apply
to a wide range of products. An organization might want to offer various
agreed prices to customers, but use the same quantity breaks consistently.
It should be possible to add a new price for a product without having to
define quantity breaks, with the risk that the newly entered quantity breaks
are inconsistent with standard organisation policy.
Maybe this wasn't clear... the goal is to get rid of the QuantityBreak
entity and use the quantity fields directly. This significantly simplifies
both the data model and the code. In theory what you say is correct, that
quantity breaks could be maintained separately, but that hasn't happened in
any of the OFBiz UIs, and in fact I've never seen anything like that
actually used (I've seen them built, but then abandoned because for the user
they are cumbersome).
If an organization wants a policy of specific quantity breaks they can
certainly implement that and design the user interface for it, even with the
data model designed this way.
QuantityBreak is also used for shipping, and quantity breaks for shipping
are often in terms of weight. I think that's sensible, and the entity should
not be removed altogether.
I'm not quite suire I get clearly which were the 2 quantity fields you spoke about David, I guess it's for pricing and shipping?
Then, I tend to agree with Paul here about keeping the QuantityBreak Entity even if we could refactor the code to use the 2 fields
(did not look into code and I don't remember OTTOMH). They could still be useful in some specific cases.
For product pricing as opposed to shipping, how about keeping QuantityBreak,
but only to support UIs? Remove the relationship between QuantityBreak and
product pricing. What does everyone think?
Yes that sounds good to me. The best way would be to provide a patch in a Jira. If it's not used OOTB (Paul's assertion, to be
confirmed) then I think we don't need to provide a way for data migration
https://cwiki.apache.org/confluence/display/OFBTECH/Revisions+Requiring+Data+Migration
Purchase and Sales Agreements
-----------------------------------------------
- the AgreementItem has the currency for prices. The currency should be on
individual AgreementProductAppls. It should be possible to have agreed
prices in various currencies for a product
- AgreementProductAppl should have its own from and thru dates. A particular
price might have a different timespan than the Agreement or (in the case of
a purchase agreement) the SupplierProduct. You could imagine an agreement
spanning a year with a list of special prices, each of which is available
for one month
This is a good point, it would be good to have prices in multiple
currencies in the same agreement, I'm modifying the AgreementItemProduct
entity in the Mantle data model to support that and in general be more like
the ProductPrice entity.
So this is for Mantle work only?
On second thought, after looking at the model, I like them (currency,
from/thru dates) on the AgreementItem better since it can apply to multiple
things and agreements may be better organized if price lists for different
currencies and date ranges are under different items (and the actual data is
a lot cleaner).
I think, as a general proposition, a price should have a currency. It's
meaningless without. AgreementItem is an organising principle for components
of an overall agreement, and it should be up to the user to define agreement
items in whatever way makes sense to them. If a company using Ofbiz prices
their products in many different currencies, what you've suggested fragments
product prices into many different agreement items. The currency that a
product happens to be priced in is not necessarily the most important
organising principle for agreed prices.
Similarly, from and thru dates might be useful on individual
AgreementProductAppls. Think about the situation where we have an agreement
for a year with a special-of the-month, a special agreed price for a
different product each month. Does that on its own justify twelve
AgreementItems?
I have no problem with an AgreementItem having *default* from date, thru
date and currency. When we enter an AgreementProductAppl for the
AgreementItem, the defaults obtained from the AgreementItem should be
visible, but they should be editable.
This is not an easy choice. It looks logical to have the currency with the price value, but on the other hand the currency may apply
to many AgreementProductAppl. Having them editable sounds good to me, as ever words are good code is better => patch in Jira
- There’s no way at present to have a quantity break associated with an
agreed price for a sales agreement. Quantity breaks are currently within the
SupplierProduct entity, so are available for purchase agreements, but not
sales agreements.
Actually, that's not quite true. The current AgreementProductAppl model is
cumbersome, but this last point IS supported (ie agreement-based sales
pricing with quantity breaks) in both the OFBiz data model and the sales
order code.
From what I've found:
- At
https://fisheye6.atlassian.com/browse/ofbiz/trunk/applications/product/src/org/ofbiz/product/price/PriceServices.java?hb=true#to247,
I think the product pricing code just looks for the first
AgreementProductAppl with no regard for quantities.
- As far as I can tell the QuantityBreak entity is only currently used for
shipping calculations, not pricing.
- The ProductPriceRule entity can hold a number of ProductPriceCond
conditions, including a test for the party and for the quantity, but
ProductPriceRules aren't related to Agreements, and they are only used for
sales, not purchases.
I assume you're talking about the QuantityBreak entity being used for
agreement-based sales pricing. Can you point me to where in the code that
happens?
In the data model I'm working on this is simplified and much more clear. See
the "AgreementItemProduct" entity here:
https://github.com/jonesde/mantle/blob/master/mantle-udm/entity/PartyEntities.xml
SupplierProduct
-----------------------
- As David says, a five-attribute primary key is unwieldy. It should be
replaced with a simple ID.
Agree also, but I don't know what it would mean in term of change... I begin to better understand David's decision. It's not only
about framework depencies on application but also about data model refactoring...
Agreed. Here is my current take on the SupplierProduct entity:
https://github.com/jonesde/mantle/blob/master/mantle-udm/entity/ProductInventoryEntities.xml
- SupplierProduct has a minimumQuantity attribute, so must be read in
combination with other SupplierProducts. SupplierProduct should *not* have
any information on quantity breaks. There should be just one instance of
SupplierProduct to record that a product can be obtained from a supplier. A
SupplierProduct should have associated QuantityBreaks and ProductPrices, as
described in the Data Model Resource Book.
- lastPrice in SupplierProduct is used both for the last price we obtained
the product, and an agreed price. These are two separate things. Given the
lastPrice might have been determined by an agreement that has expired, and
by a quantity that we are not ordering this time, it's not very useful. It
ought to be possible to determine the most recent prices we paid at various
quantity breaks for a given product from a given supplier
Actually in OFBiz the "lastPrice" is meant to be just that, the last known
price from the supplier. It isn't meant to represent any sort of purchase
agreement. The Agreement* entities should be used for that (though I don't
know off the top of my head if that is current supported in the OFBiz
purchase order code).
Agreement entities are not currently supported for purchase pricing. See
https://fisheye6.atlassian.com/browse/ofbiz/trunk/applications/product/src/org/ofbiz/product/price/PriceServices.java?hb=true#to1323.
What's provided in the data model and what Ofbiz code currently does
are
not always the same thing. lastPrice is used for purchase prices, and it
takes no account of quantities.
Re your SupplierProduct in Mantle: it has a price and minimumQuantity
attributes. Doesn't that mean that if a supplier offers different prices for
different quantities, there must be two or more SupplierProducts? Wouldn't
it be better to have two different entities, one to record that a product
can be obtained from a supplier, and a separate one for prices at various
quantities? There would be a one-to-many between the former and the latter.
TaxAuthorityRateProduct
-------------------------------------
- This entity is used to determine tax on shipping and promotional pricing
adjustments, when these are not products. There should be a separate entity
for tax on all order adjustments, which will determine tax based on the
adjustment type rather than on the product category
On this subject, at r1070830 I have committed Paul's patch with a simple information on screen to handle the shipping/promotion case
(only one line). Keeping the curent data model, ideally a js script would enforce this case.
Yes, agreed. In the Mantle UDM I changed the name to
"TaxAuthorityRateSales":
https://github.com/jonesde/mantle/blob/master/mantle-udm/entity/AccountingOtherEntities.xml
- The ProductStore is used to determine the tax payable. That’s fine for
sales, but makes no sense for purchases. Really, the ProductStore is a proxy
for the geo of the seller. TaxAuthorityRateProduct should be revised to list
the geo of the seller rather than the ProductStore, then the same rules
could be used by purchases as well as sales. For a sale, the ProductStore
geo would be used as the geo of the seller.
I'm not sure what you mean about the geo of the seller thingy. The geo used
on TaxAuthorityRateProduct is the taxAuthorityGeoId where the customer is
shipping to (or billing to) a geo where there is a PartyTaxAuthInfo (or
Mantle TaxAuthorityParty) record where the seller organization has
isNexus=Y.
I'm talking about the "buyer" and the "seller" because, for VAT/GST taxes,
we need to support tax on purchases as well as sales. For a purchase, we're
the buyer and the supplier is the seller. For a sale, the customer is the
buyer, we're the seller.
TARP *should* be used for both purchases and sales, because what it's really
capturing are tax rules we care about, and they don't change just because
we're buying something instead of selling it. Governments define taxes in
terms of the geographical location of the buyer and the seller, and of
course a government doesn't know or care about our product stores.
TARP can manage rules based on the geographical location of the buyer, but
only has the product store for the seller. That's OK for sales, but breaks
down for purchases. I think TARP should have attributes for the geographical
location of both the buyer and the seller. In effect, I think the product
store is a proxy for the geographical location of the seller. I'm happy to
leave the product store in TARP if people think it's useful.
While we're talking about TARP, I am unsure whether it should also have
flags to say if the tax should be applied to purchases, sales or both, or if
the tax type should drive that, so a tax type of SALES_TAX will be taken to
apply only to sales, and a tax type of VAT_TAX will apply to both purchases
and sales.
I see VAT taxes as a cascade down to Governments. So you don't have to care about the seller, he has to care about you. I don't see
the point of handling VAT for purchase, since you will not generate the invoice for the purchase, the seller will. What is is needed
for, to have an idea about the costs? Then maybe we should think about creating a Cost Center as we have a Profit Center with the
ProductStore...
Jacques
As for the productStoreId field, it should only be used as an extra
constraint on the "rule" record, and should be fine if null (from the model
perspective, the code may not currently behave that way).
David E Jones-4 wrote:
I'm writing this to start a thread to discuss:
If you could change ANYTHING in the OFBiz data model, what would it be?
To kick this off here are some ideas I've compiled that have come up over
the years (many based on feedback from people on this mailing list), or
that I thought of recently will working on this topic. You can see them
below...
-David
========================================================
- Rename *Role entities to *Party
- Remove *Attribute and *TypeAttr entities (not generally a good practice
to use)
- Remove all status history (*Status) and just using audit on the
statusId
field
- ProductPrice add quantity breaks, change PK to single field sequenced
- Get rid of QuantityBreak and use the two simple fields instead in each
place used
- Get rid of ProdCatalog*, rework it to ProductStoreCategory, ie directly
associate to the store
- Change OrderItemShipGrpInvRes to InventoryReservation, change PK to
single field sequenced
- Change Quote to be other types of Order
- Make Return more like Invoice (ie no adjustment, just use items for
everything)
- Instead of OrderItemType, OrderAdjustmentType, InvoiceItemType,
ReturnItemType just use Shared ItemType everywhere
- PartyRelationship simplify (single sequenced ID)
- Make prefixes consistent (no suffixes), ie toPartyId instead of
partyIdTo, etc
- Change all relationships to PartyRole to be type one-nofk; use plain
one
for Party and RoleType
- Move most *Type entities to Enumeration values (update seed data,
referring
entities, remove *Type) (after this remove all remaining hasTable
fields)
- Rework PhysicalInventory and InventoryVariance to simplify, reduce
dependency on InventoryItem
- Review use of Appl, Assoc (look at book, make sure consistent)
- Add PartyIdentification entity, get rid of various fields and other
entities (maybe even PartyTaxAuthority...)
- Change GoodIdentification to ProductIdentification
- Combine PartyContactMechPurpose and PartyContactMech (remove, only use
entity with purpose, name PartyContactMech)
- Get rid of NoteData, put noteText very-long field directly on various
*Note entities
- Review all entities with large PKs (esp 4+ fields)
- Review and do something with all createOn/By lastUpdateOn/By fields,
maybe add an auditing flag for the entity instead of just for a field
- CustRequest to Request
- FinAccount to FinancialAccount
- Invoice handle recurrence? (was using RecurrenceInfo)
- Get rid of optional ProductFeature concept (conf/etc products much
better model)
- Instead of various *Term entities and mapping, just use OrderTerm
everywhere
- Cleanup/reorg ShipmentCostEstimate, CarrierShipmentMethod, etc, etc
- Agreement - make price list easier/cleaner
- Remove all createdDate, createdByUserLogin, lastModifiedDate,
lastModifiedByUserLogin fields (use framework defaults, audit-log)
- Product clean up: move dimenstions to new entity, remove content
fields,
etc
- Trim down big entities like Product, WorkEffort, etc; for groups of
similar fields use a more normalized structure
--
View this message in context:
http://ofbiz.135035.n4.nabble.com/Discussion-Potential-Data-Model-Improvements-tp3390620p3422000.html
Sent from the OFBiz - Dev mailing list archive at Nabble.com.
