Storing supplier provided account number

[Bob Morley]

My use case here is very similar to this thread --

http://ofbiz.135035.n4.nabble.com/Customer-number-from-suppliers-td142646.html#a142646

I think this thread would suggest the choices are ...

1) New field on PartyRelationship; something like "accountNumber" which would be similar to "positionTitle" in that it really is only applicable to some of the PartyRelationshipType sub-classes.

2) Formalize the sub-class by setting hasTable = "Y" on the type and creating a new "SupplierRel" entity that contains this "accountNumber".

3) Leverage the agreement model for this relationship.

What I was personally going to do was #2 from this list (before reading the thread) but after seeing positionTitle I am kind of leaning towards doing #1.  Since I have to do this for our solution I want to do it in an acceptable manner for inclusion into Ofbiz (providing we at least agree that this would be a good thing).

[BJ Freeman]

I added more to the contact mech
it is robust enough you just add types and reasons.



=========================
BJ Freeman
http://bjfreeman.elance.com
Strategic Power Office with Supplier Automation <http://www.businessesnetwork.com/automation/viewforum.php?f=93>
Specialtymarket.com <http://www.specialtymarket.com/>

Systems Integrator-- Glad to Assist

Chat  Y! messenger: bjfr33man
Linkedin
<http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro>


Bob Morley sent the following on 4/29/2010 1:42 PM:

> My use case here is very similar to this thread --
>
> http://ofbiz.135035.n4.nabble.com/Customer-number-from-suppliers-td142646.html#a142646
>
> I think this thread would suggest the choices are ...
>
> 1) New field on PartyRelationship; something like "accountNumber" which
> would be similar to "positionTitle" in that it really is only applicable to
> some of the PartyRelationshipType sub-classes.
>
> 2) Formalize the sub-class by setting hasTable = "Y" on the type and
> creating a new "SupplierRel" entity that contains this "accountNumber".
>
> 3) Leverage the agreement model for this relationship.
>
> What I was personally going to do was #2 from this list (before reading the
> thread) but after seeing positionTitle I am kind of leaning towards doing
> #1.  Since I have to do this for our solution I want to do it in an
> acceptable manner for inclusion into Ofbiz (providing we at least agree that
> this would be a good thing).

[Bob Morley]

Is this the ContactMechType of "INTERNAL_PARTYID" ?

On Apr 29, 2010, at 5:22 PM, BJ Freeman wrote:

> I added more to the contact mech
> it is robust enough you just add types and reasons.
>

[Adrian Crum]

My thoughts have changed a bit since that previous thread on the subject.

We have similar needs here with parties in other relationships. Our
dealers have dealer numbers and license numbers, our suppliers give us
numbers (the same as the previous thread), and the homeowners have a
customer number and other bits of information we have to maintain for them.

The solution I came up with here was to create a properties entity for
each party type. Each properties entity contains fields for the various
bits of information we need to track for each party type.

The reason I mentioned that is to point out that there can be more than
one piece of information you need to attach to a party. If those things
are added to the party relationship, then that entity might start to
look like a kludge. The same thing could happen if those fields are
added to the agreement entity.

So now I'm undecided. If I had to choose between Party Relationship or
Agreement, I would lean toward Agreement.

-Adrian


On 4/29/2010 1:42 PM, Bob Morley wrote:

>
> My use case here is very similar to this thread --
>
> http://ofbiz.135035.n4.nabble.com/Customer-number-from-suppliers-td142646.html#a142646
>
> I think this thread would suggest the choices are ...
>
> 1) New field on PartyRelationship; something like "accountNumber" which
> would be similar to "positionTitle" in that it really is only applicable to
> some of the PartyRelationshipType sub-classes.
>
> 2) Formalize the sub-class by setting hasTable = "Y" on the type and
> creating a new "SupplierRel" entity that contains this "accountNumber".
>
> 3) Leverage the agreement model for this relationship.
>
> What I was personally going to do was #2 from this list (before reading the
> thread) but after seeing positionTitle I am kind of leaning towards doing
> #1.  Since I have to do this for our solution I want to do it in an
> acceptable manner for inclusion into Ofbiz (providing we at least agree that
> this would be a good thing).

[Bob Morley]

Ya ... the one thing I wondered about was the Employment entity.  I  
believe PartyRelationshipType has it set as hasTable = "Y" but then it  
redundantly stores the role, party to/from as well as the from/thru  
dates.  I would think if Employment is a sub-class of  
PartyRelationship, it should only have its unique fields (I think two  
termination related fields).

I would think a "fundamental" type like supplier may have its own  
SupplierRel table, but things that can be very vertical specific  
(dealers / houseowners) might be something better stored in an  
Agreement.  Would seem to me to jive with other "supplier" entities  
that already exist in the system.

Another thought that occurred to me is that we could have a  
PartyRelationshipIdent entity (like Good & Party) where we could store  
all sorts of identification numbers on the relationship.  This would  
give a nice place for the employee's number, and the other  
identification numbers we are talking about.  Waddya think about that?


On Apr 29, 2010, at 5:28 PM, Adrian Crum wrote:

> My thoughts have changed a bit since that previous thread on the  
> subject.
>
> We have similar needs here with parties in other relationships. Our  
> dealers have dealer numbers and license numbers, our suppliers give  
> us numbers (the same as the previous thread), and the homeowners  
> have a customer number and other bits of information we have to  
> maintain for them.
>
> The solution I came up with here was to create a properties entity  
> for each party type. Each properties entity contains fields for the  
> various bits of information we need to track for each party type.
>
> The reason I mentioned that is to point out that there can be more  
> than one piece of information you need to attach to a party. If  
> those things are added to the party relationship, then that entity  
> might start to look like a kludge. The same thing could happen if  
> those fields are added to the agreement entity.
>
> So now I'm undecided. If I had to choose between Party Relationship  
> or Agreement, I would lean toward Agreement.
>
> -Adrian
>
>
> On 4/29/2010 1:42 PM, Bob Morley wrote:
>>
>> My use case here is very similar to this thread --
>>
>> http://ofbiz.135035.n4.nabble.com/Customer-number-from-suppliers-td142646.html#a142646
>>
>> I think this thread would suggest the choices are ...
>>
>> 1) New field on PartyRelationship; something like "accountNumber"  
>> which
>> would be similar to "positionTitle" in that it really is only  
>> applicable to
>> some of the PartyRelationshipType sub-classes.
>>
>> 2) Formalize the sub-class by setting hasTable = "Y" on the type and
>> creating a new "SupplierRel" entity that contains this  
>> "accountNumber".
>>
>> 3) Leverage the agreement model for this relationship.
>>
>> What I was personally going to do was #2 from this list (before  
>> reading the
>> thread) but after seeing positionTitle I am kind of leaning towards  
>> doing
>> #1.  Since I have to do this for our solution I want to do it in an
>> acceptable manner for inclusion into Ofbiz (providing we at least  
>> agree that
>> this would be a good thing).

[Adrian Crum]

We already have PartyIdentification and PartyIdentificationType. Maybe
those could be tied to PartyRelationship somehow.

-Adrian

On 4/29/2010 2:42 PM, Robert Morley wrote:

> Ya ... the one thing I wondered about was the Employment entity.  I
> believe PartyRelationshipType has it set as hasTable = "Y" but then it
> redundantly stores the role, party to/from as well as the from/thru
> dates. I would think if Employment is a sub-class of PartyRelationship,
> it should only have its unique fields (I think two termination related
> fields).
>
> I would think a "fundamental" type like supplier may have its own
> SupplierRel table, but things that can be very vertical specific
> (dealers / houseowners) might be something better stored in an
> Agreement. Would seem to me to jive with other "supplier" entities that
> already exist in the system.
>
> Another thought that occurred to me is that we could have a
> PartyRelationshipIdent entity (like Good & Party) where we could store
> all sorts of identification numbers on the relationship. This would give
> a nice place for the employee's number, and the other identification
> numbers we are talking about. Waddya think about that?
>
>
> On Apr 29, 2010, at 5:28 PM, Adrian Crum wrote:
>
>> My thoughts have changed a bit since that previous thread on the subject.
>>
>> We have similar needs here with parties in other relationships. Our
>> dealers have dealer numbers and license numbers, our suppliers give us
>> numbers (the same as the previous thread), and the homeowners have a
>> customer number and other bits of information we have to maintain for
>> them.
>>
>> The solution I came up with here was to create a properties entity for
>> each party type. Each properties entity contains fields for the
>> various bits of information we need to track for each party type.
>>
>> The reason I mentioned that is to point out that there can be more
>> than one piece of information you need to attach to a party. If those
>> things are added to the party relationship, then that entity might
>> start to look like a kludge. The same thing could happen if those
>> fields are added to the agreement entity.
>>
>> So now I'm undecided. If I had to choose between Party Relationship or
>> Agreement, I would lean toward Agreement.
>>
>> -Adrian
>>
>>
>> On 4/29/2010 1:42 PM, Bob Morley wrote:
>>>
>>> My use case here is very similar to this thread --
>>>
>>> http://ofbiz.135035.n4.nabble.com/Customer-number-from-suppliers-td142646.html#a142646
>>>
>>>
>>> I think this thread would suggest the choices are ...
>>>
>>> 1) New field on PartyRelationship; something like "accountNumber" which
>>> would be similar to "positionTitle" in that it really is only
>>> applicable to
>>> some of the PartyRelationshipType sub-classes.
>>>
>>> 2) Formalize the sub-class by setting hasTable = "Y" on the type and
>>> creating a new "SupplierRel" entity that contains this "accountNumber".
>>>
>>> 3) Leverage the agreement model for this relationship.
>>>
>>> What I was personally going to do was #2 from this list (before
>>> reading the
>>> thread) but after seeing positionTitle I am kind of leaning towards
>>> doing
>>> #1. Since I have to do this for our solution I want to do it in an
>>> acceptable manner for inclusion into Ofbiz (providing we at least
>>> agree that
>>> this would be a good thing).
>
>
>

[Bob Morley]

Ya perhaps ... but it just doesn't feel right.  The more thought I gave it the more I started to like the agreement approach.  On my drive home I asked myself if I could come up with an example of something that represents a relationship between two parties AND would have some sort of identify fields.  In the end I could not come up with anything ...  I think all of our examples are reasonably modeled as agreements and one could argue that the Employment entity in Ofbiz really should be an agreement as well.  DMR book Vol1 suggests it as a sub-class of Agreement in fig 4.13.

If PartyRelation is really just hooking up parties for (mostly) navigational purposes; then anything more than that seems to usually represent some sort of Agreement between the two parties.  In this way it seems Agreement is just a specialization of PartyRelationship.

I will do more research and come up with a more concrete proposal for review (if any changes are required at all).

Adrian Crum wrote

We already have PartyIdentification and PartyIdentificationType. Maybe
those could be tied to PartyRelationship somehow.

[Scott Gray-2]

I think Agreements are more like the terms of a relationship and can change over the course of that relationship.

I would tend to think of an account number as being more like relationship meta data that exists so long as the relationship exists and for that reason I would either add a generic field to PartyRelationship or otherwise add a new entity SupplierRelationship and one-to-one it with PartyRelationship.

Regards
Scott

HotWax Media
http://www.hotwaxmedia.com

On 30/04/2010, at 3:37 PM, Bob Morley wrote:

>
> Ya perhaps ... but it just doesn't feel right.  The more thought I gave it
> the more I started to like the agreement approach.  On my drive home I asked
> myself if I could come up with an example of something that represents a
> relationship between two parties AND would have some sort of identify
> fields.  In the end I could not come up with anything ...  I think all of
> our examples are reasonably modeled as agreements and one could argue that
> the Employment entity in Ofbiz really should be an agreement as well.  DMR
> book Vol1 suggests it as a sub-class of Agreement in fig 4.13.
>
> If PartyRelation is really just hooking up parties for (mostly) navigational
> purposes; then anything more than that seems to usually represent some sort
> of Agreement between the two parties.  In this way it seems Agreement is
> just a specialization of PartyRelationship.
>
> I will do more research and come up with a more concrete proposal for review
> (if any changes are required at all).
>
>
> Adrian Crum wrote:
>>
>> We already have PartyIdentification and PartyIdentificationType. Maybe
>> those could be tied to PartyRelationship somehow.
>>
>
> --
> View this message in context: http://ofbiz.135035.n4.nabble.com/Storing-supplier-provided-account-number-tp2076162p2076460.html
> Sent from the OFBiz - Dev mailing list archive at Nabble.com.

[Adam Heath-2]

Scott Gray wrote:
> I think Agreements are more like the terms of a relationship and can change over the course of that relationship.
>
> I would tend to think of an account number as being more like relationship meta data that exists so long as the relationship exists and for that reason I would either add a generic field to PartyRelationship or otherwise add a new entity SupplierRelationship and one-to-one it with PartyRelationship.

I like the latter, SupplierRelationship, as there could be an
unbounded set of SupplierIdentificationType and
SupplierIdentificationPurpose fields.

Type has IDENTIER as a parent, then SECURITY_CARD, EMPLOYEE_ID as
children.

And, in that vein, SECURITY_CARD could very well change over time too,
separate from the parent PartyRelationship.

[Scott Gray-2]

On 1/05/2010, at 3:52 AM, Adam Heath wrote:

> Scott Gray wrote:
>> I think Agreements are more like the terms of a relationship and can change over the course of that relationship.
>>
>> I would tend to think of an account number as being more like relationship meta data that exists so long as the relationship exists and for that reason I would either add a generic field to PartyRelationship or otherwise add a new entity SupplierRelationship and one-to-one it with PartyRelationship.
>
> I like the latter, SupplierRelationship, as there could be an
> unbounded set of SupplierIdentificationType and
> SupplierIdentificationPurpose fields.

That wasn't what I was suggesting, I was describing SupplierRelationship as being an extension to PartyRelationship in the same way that Person or PartyGroup extends Party.

> Type has IDENTIER as a parent, then SECURITY_CARD, EMPLOYEE_ID as
> children.
>
> And, in that vein, SECURITY_CARD could very well change over time too,
> separate from the parent PartyRelationship.

I'm not sure how I would handle something like a security card but IMO the Employment entity should be an extension to PartyRelationship, I mean it already shares the same primary key fields.

Regards
Scott

[Bob Morley]

On Apr 30, 2010, at 6:06 PM, Scott Gray wrote:

> On 1/05/2010, at 3:52 AM, Adam Heath wrote:
>
>> Scott Gray wrote:
>>> I think Agreements are more like the terms of a relationship and  
>>> can change over the course of that relationship.
>>>
>>> I would tend to think of an account number as being more like  
>>> relationship meta data that exists so long as the relationship  
>>> exists and for that reason I would either add a generic field to  
>>> PartyRelationship or otherwise add a new entity  
>>> SupplierRelationship and one-to-one it with PartyRelationship.
>>
>> I like the latter, SupplierRelationship, as there could be an
>> unbounded set of SupplierIdentificationType and
>> SupplierIdentificationPurpose fields.
>
> That wasn't what I was suggesting, I was describing  
> SupplierRelationship as being an extension to PartyRelationship in  
> the same way that Person or PartyGroup extends Party.
>

Yes I think this is the solution that Adrian has original suggested a  
couple of years back and is the same solution I had thought about  
implementing when I started this thread.  I think the difficulty here  
comes in the fact that the data model book has sub-classes for both  
PartyRelationship and Agreement for this purpose (Supplier  
Relationship and Purchase Agreement respectively from page 43 and 149  
of The Data Model Resource Book volume 1).  There are also sub-classes  
for both for "Employeement" named "Employment" and "Employment  
Agreement".

The book suggests that Agreement is involved in a single  
PartyRelationship (but the relationship may be associated with a  
collection of Agreements).  Ofbiz does not model this association, but  
I think it probably should.  A scan of the forum for anything on this  
topic did not produce any results.  Again the book suggests the  
PartyRelationship is an informal relationship where the agreement is  
created "when a formal agreement is established between these parties  
and a contract is drawn stipulating certain terms of this  
agreement ...".

For a supplier I think it is desirable to have an informal  
PartyRelationship between the Internal Organization and the Party that  
will be supplying products.  We may also (optionally) have a formal  
agreement that dictates pricing and other terms (like net 30, etc).  
However, the life of this section agreement may be much shorter than  
the life of the relationship to the supplier.  And it is very likely  
that the supplier supplied "customerNumber" or "accountNumber" would  
live across many agreements.

As a result, I would like to get started on this new entity  
"SupplierRel" and will provide a patch soon.

[Bob Morley]

On May 3, 2010, at 3:09 PM, Robert Morley wrote:

>>
>> That wasn't what I was suggesting, I was describing  
>> SupplierRelationship as being an extension to PartyRelationship in  
>> the same way that Person or PartyGroup extends Party.
>>
>
>
> Yes I think this is the solution that Adrian has original suggested  
> a couple of years back and is the same solution I had thought about  
> implementing when I started this thread.  I think the difficulty  
> here comes in the fact that the data model book has sub-classes for  
> both PartyRelationship and Agreement for this purpose (Supplier  
> Relationship and Purchase Agreement respectively from page 43 and  
> 149 of The Data Model Resource Book volume 1).  There are also sub-
> classes for both for "Employeement" named "Employment" and  
> "Employment Agreement".
>
> The book suggests that Agreement is involved in a single  
> PartyRelationship (but the relationship may be associated with a  
> collection of Agreements).  Ofbiz does not model this association,  
> but I think it probably should.  A scan of the forum for anything on  
> this topic did not produce any results.  Again the book suggests the  
> PartyRelationship is an informal relationship where the agreement is  
> created "when a formal agreement is established between these  
> parties and a contract is drawn stipulating certain terms of this  
> agreement ...".
>
> For a supplier I think it is desirable to have an informal  
> PartyRelationship between the Internal Organization and the Party  
> that will be supplying products.  We may also (optionally) have a  
> formal agreement that dictates pricing and other terms (like net 30,  
> etc).  However, the life of this section agreement may be much  
> shorter than the life of the relationship to the supplier.  And it  
> is very likely that the supplier supplied "customerNumber" or  
> "accountNumber" would live across many agreements.
>
> As a result, I would like to get started on this new entity  
> "SupplierRel" and will provide a patch soon.

Argh!  Ok now I understand why "Employment" which is a logic sub-class  
of PartyRelationship was not coded as such.  The issue is that there  
is not a specific single primary key for PartyRelationship -- its  
primary keys are partyto, partyfrom, roleto, roleform, and fromDate.  
As a result, without duplicating all of those keys you can not recall  
create the Employment entity like we do with other "inherited"  
entities like Party / Person & PartyGroup.

So the options are to add  "partyRelationshipId" to the entity and the  
changes that would involve (including about a 100 seeded  
PartyRelations), duplicate the fields in SupplierRel (like Employment  
does now), or fall back to putting the field directly on  
PartyRelationship itself.

Community direction requested ... :)

[Scott Gray-2]

On 4/05/2010, at 7:34 AM, Robert Morley wrote:

>
> On May 3, 2010, at 3:09 PM, Robert Morley wrote:
>
>>>
>>> That wasn't what I was suggesting, I was describing SupplierRelationship as being an extension to PartyRelationship in the same way that Person or PartyGroup extends Party.
>>>
>>
>>
>> Yes I think this is the solution that Adrian has original suggested a couple of years back and is the same solution I had thought about implementing when I started this thread.  I think the difficulty here comes in the fact that the data model book has sub-classes for both PartyRelationship and Agreement for this purpose (Supplier Relationship and Purchase Agreement respectively from page 43 and 149 of The Data Model Resource Book volume 1).  There are also sub-classes for both for "Employeement" named "Employment" and "Employment Agreement".
>>
>> The book suggests that Agreement is involved in a single PartyRelationship (but the relationship may be associated with a collection of Agreements).  Ofbiz does not model this association, but I think it probably should.  A scan of the forum for anything on this topic did not produce any results.  Again the book suggests the PartyRelationship is an informal relationship where the agreement is created "when a formal agreement is established between these parties and a contract is drawn stipulating certain terms of this agreement ...".
>>
>> For a supplier I think it is desirable to have an informal PartyRelationship between the Internal Organization and the Party that will be supplying products.  We may also (optionally) have a formal agreement that dictates pricing and other terms (like net 30, etc).  However, the life of this section agreement may be much shorter than the life of the relationship to the supplier.  And it is very likely that the supplier supplied "customerNumber" or "accountNumber" would live across many agreements.
>>
>> As a result, I would like to get started on this new entity "SupplierRel" and will provide a patch soon.
>
> Argh!  Ok now I understand why "Employment" which is a logic sub-class of PartyRelationship was not coded as such.  The issue is that there is not a specific single primary key for PartyRelationship -- its primary keys are partyto, partyfrom, roleto, roleform, and fromDate.  As a result, without duplicating all of those keys you can not recall create the Employment entity like we do with other "inherited" entities like Party / Person & PartyGroup.
>
> So the options are to add  "partyRelationshipId" to the entity and the changes that would involve (including about a 100 seeded PartyRelations), duplicate the fields in SupplierRel (like Employment does now), or fall back to putting the field directly on PartyRelationship itself.
>
> Community direction requested ... :)

I think you missed the second half of my earlier email:
> I'm not sure how I would handle something like a security card but IMO the Employment entity should be an extension to PartyRelationship, I mean it already shares the same primary key fields.

Employment already has the same PK fields as PartyRelationship.

[Bob Morley]

Scott Gray-2 wrote

I think you missed the second half of my earlier email:
> I'm not sure how I would handle something like a security card but IMO the Employment entity should be an extension to PartyRelationship, I mean it already shares the same primary key fields.

Employment already has the same PK fields as PartyRelationship.

Yep; I had definitely glossed over that.  In my head I was thinking that PartyRelationship had a partyRelationshipId that could be added to Employment and Employment could have those other fields removed.  But you are quite correct, we could simply use the existing primary key fields.

Would there be any thought to doing the former?  I don't think I want to bite that off to be honest, but one could create the entity such that it has a single primary key and the others would make up a unique index.  Personally, I don't know if that is a good idea but it would reduce redundancy in the sub-classes.

[Scott Gray-2]

On 4/05/2010, at 2:22 PM, Bob Morley wrote:

> Scott Gray-2 wrote:
>>
>> I think you missed the second half of my earlier email:
>>> I'm not sure how I would handle something like a security card but IMO
>>> the Employment entity should be an extension to PartyRelationship, I mean
>>> it already shares the same primary key fields.
>>
>> Employment already has the same PK fields as PartyRelationship.
>>
>
> Yep; I had definitely glossed over that.  In my head I was thinking that
> PartyRelationship had a partyRelationshipId that could be added to
> Employment and Employment could have those other fields removed.  But you
> are quite correct, we could simply use the existing primary key fields.
>
> Would there be any thought to doing the former?  I don't think I want to
> bite that off to be honest, but one could create the entity such that it has
> a single primary key and the others would make up a unique index.
> Personally, I don't know if that is a good idea but it would reduce
> redundancy in the sub-classes.

Personally I like using natural PKs whenever possible and the number of them doesn't really bother me with all the tools we have for auto-setting fields.

It would be interesting to see if there is some way to smarten up the entity engine so that these sub-types could be more deeply integrated.  I have no idea at the moment what form that might take though.

Regards
Scott

[Bob Morley]

On May 4, 2010, at 12:59 AM, Scott Gray wrote:

>
> Personally I like using natural PKs whenever possible and the number  
> of them doesn't really bother me with all the tools we have for auto-
> setting fields.
>
> It would be interesting to see if there is some way to smarten up  
> the entity engine so that these sub-types could be more deeply  
> integrated.  I have no idea at the moment what form that might take  
> though.
>

FYI -- Patch created as part of JIRA OFBIZ-3764.