How to design flexible relationships between documents?

Hi,
I have an inventory application which also handles accounts payable, and
accounts receivables.
One of the problems I currently have is that I need to tie, in a flexible
way, different transaction types.
For example, I need to tie invoices to their "shipping-notifications", to
the original orders, etc...
A friend of mine was telling me about SAP and how there you can in a
flexible way define documents (or transactions) so that they can be tied up
to one or more documents of a given type.
I've been thinking about this, but I can't figure out how a design that
would allow this in a flexible way....
How does this work?
Thanks,
EdgardEdgard L. Riba wrote:
> Hi,
> I have an inventory application which also handles accounts payable, and
> accounts receivables.
> One of the problems I currently have is that I need to tie, in a flexible
> way, different transaction types.
> For example, I need to tie invoices to their "shipping-notifications", to
> the original orders, etc...
> A friend of mine was telling me about SAP and how there you can in a
> flexible way define documents (or transactions) so that they can be tied up
> to one or more documents of a given type.
> I've been thinking about this, but I can't figure out how a design that
> would allow this in a flexible way....
> How does this work?
> Thanks,
> Edgard
>
Can you not carry the original order number through to the shipping
notifications (i.e. "Order #xxxx has shipped"), then on to the invoice
(invoice header contains an invoice number and the original order number) ?|||Hi Tracy,
This was my original design, but the problem is that there are situations
where there are multiple orders on an invoice, or other types of transaction
relationship's, and this approach just doesn't cut it. For example,
there are other types of transactions where I want to 1) make a demand
forecast for all stores, 2) generate a consolidated purchase order, 3) Get 1
or more invoices for that purchase order, 4) send the merchandise to the
stores in one or more shipments, etc... The approach doesn't work in
something like this :-(
Apparently with SAP (and I imagine other ERP's would be the same) you can
define relationships between the transactions in a very flexible way:
Transaction A goes to multiple transactions B, which go to multiple
transactions C, etc...
I found this design quite interesting, and clever. I have searched on the
internet to see if there is information on how this is designed, but
couldn't find yet anything...
Thanks,
Edgard
"Tracy McKibben" <tracy@.realsqlguy.com> escribió en el mensaje
news:ez2ta7SmGHA.4100@.TK2MSFTNGP05.phx.gbl...
> Edgard L. Riba wrote:
>> Hi,
>> I have an inventory application which also handles accounts payable, and
>> accounts receivables.
>> One of the problems I currently have is that I need to tie, in a flexible
>> way, different transaction types.
>> For example, I need to tie invoices to their "shipping-notifications", to
>> the original orders, etc...
>> A friend of mine was telling me about SAP and how there you can in a
>> flexible way define documents (or transactions) so that they can be tied
>> up to one or more documents of a given type.
>> I've been thinking about this, but I can't figure out how a design that
>> would allow this in a flexible way....
>> How does this work?
>> Thanks,
>> Edgard
> Can you not carry the original order number through to the shipping
> notifications (i.e. "Order #xxxx has shipped"), then on to the invoice
> (invoice header contains an invoice number and the original order number)
> ?

How to design flexible relationships between documents?

Edgard L. Riba wrote:
> Hi,
> I have an inventory application which also handles accounts payable, and
> accounts receivables.
> One of the problems I currently have is that I need to tie, in a flexible
> way, different transaction types.
> For example, I need to tie invoices to their "shipping-notifications", to
> the original orders, etc...
> A friend of mine was telling me about SAP and how there you can in a
> flexible way define documents (or transactions) so that they can be tied u
p
> to one or more documents of a given type.
> I've been thinking about this, but I can't figure out how a design that
> would allow this in a flexible way....
> How does this work?
> Thanks,
> Edgard
>
Can you not carry the original order number through to the shipping
notifications (i.e. "Order #xxxx has shipped"), then on to the invoice
(invoice header contains an invoice number and the original order number) ?Hi Tracy,
This was my original design, but the problem is that there are situations
where there are multiple orders on an invoice, or other types of transaction
relationship's, and this approach just doesn't cut it. For example,
there are other types of transactions where I want to 1) make a demand
forecast for all stores, 2) generate a consolidated purchase order, 3) Get 1
or more invoices for that purchase order, 4) send the merchandise to the
stores in one or more shipments, etc... The approach doesn't work in
something like this :-(
Apparently with SAP (and I imagine other ERP's would be the same) you can
define relationships between the transactions in a very flexible way:
Transaction A goes to multiple transactions B, which go to multiple
transactions C, etc...
I found this design quite interesting, and clever. I have searched on the
internet to see if there is information on how this is designed, but
couldn't find yet anything...
Thanks,
Edgard
"Tracy McKibben" <tracy@.realsqlguy.com> escribi en el mensaje
news:ez2ta7SmGHA.4100@.TK2MSFTNGP05.phx.gbl...
> Edgard L. Riba wrote:
> Can you not carry the original order number through to the shipping
> notifications (i.e. "Order #xxxx has shipped"), then on to the invoice
> (invoice header contains an invoice number and the original order number)
> ?|||Hi Tracy,
This was my original design, but the problem is that there are situations
where there are multiple orders on an invoice, or other types of transaction
relationship's, and this approach just doesn't cut it. For example,
there are other types of transactions where I want to 1) make a demand
forecast for all stores, 2) generate a consolidated purchase order, 3) Get 1
or more invoices for that purchase order, 4) send the merchandise to the
stores in one or more shipments, etc... The approach doesn't work in
something like this :-(
Apparently with SAP (and I imagine other ERP's would be the same) you can
define relationships between the transactions in a very flexible way:
Transaction A goes to multiple transactions B, which go to multiple
transactions C, etc...
I found this design quite interesting, and clever. I have searched on the
internet to see if there is information on how this is designed, but
couldn't find yet anything...
Thanks,
Edgard
"Tracy McKibben" <tracy@.realsqlguy.com> escribi en el mensaje
news:ez2ta7SmGHA.4100@.TK2MSFTNGP05.phx.gbl...
> Edgard L. Riba wrote:
> Can you not carry the original order number through to the shipping
> notifications (i.e. "Order #xxxx has shipped"), then on to the invoice
> (invoice header contains an invoice number and the original order number)
> ?|||Hi,
I have an inventory application which also handles accounts payable, and
accounts receivables.
One of the problems I currently have is that I need to tie, in a flexible
way, different transaction types.
For example, I need to tie invoices to their "shipping-notifications", to
the original orders, etc...
A friend of mine was telling me about SAP and how there you can in a
flexible way define documents (or transactions) so that they can be tied up
to one or more documents of a given type.
I've been thinking about this, but I can't figure out how a design that
would allow this in a flexible way....
How does this work?
Thanks,
Edgard|||Edgard L. Riba wrote:
> Hi,
> I have an inventory application which also handles accounts payable, and
> accounts receivables.
> One of the problems I currently have is that I need to tie, in a flexible
> way, different transaction types.
> For example, I need to tie invoices to their "shipping-notifications", to
> the original orders, etc...
> A friend of mine was telling me about SAP and how there you can in a
> flexible way define documents (or transactions) so that they can be tied u
p
> to one or more documents of a given type.
> I've been thinking about this, but I can't figure out how a design that
> would allow this in a flexible way....
> How does this work?
> Thanks,
> Edgard
>
Can you not carry the original order number through to the shipping
notifications (i.e. "Order #xxxx has shipped"), then on to the invoice
(invoice header contains an invoice number and the original order number) ?

How to de-normalize one to many relationships?

I am a newbie to SSIS. I have been working through SQL Server 2005 Integration Services and am quite pleased with the book and with SSIS. That said, I am having trouble determining how to handle a challenge.

The business challenge is that we need to push new inventory items that we sell from our back end accounting system onto our web site. The challenge is that each item can show up in 1 or 100 categories. The silly web software wants us to write the item and the categories that item is associated with in one transaction / command.

Stated another way, when we push an item into the web store, we need to push the item and an array of categories in the same transaction.

This is my technical challenge, I cannot figure out how to select 100 records out of an item table in MS-SQL and then create an array of categories for each of those 100 items. (items belong to >1 categories)

I thought I could use two OLE DB sources where the main source was the item table and the 2nd source was the category table.

My best guess at this point is to use an OLE DB source for the item table. Then use the script component and hard code the read from the category list within the script component.

As a note, scalability is not really an issue, there would be no more than 10-20 items being pushed at any given time.

Any help would be GREATLY appriciated.

It sounds to me like you need two dataflows: the first to insert the new categories, and the second to lookup the new categories and insert the items. This can be done in a single transaction by setting RetainSameConnection=True on the connection manager for your destination connection, and placing Execute SQL Tasks before the first dataflow and after the second to execute BEGIN and COMMIT TRANSACTION statements respectively.
|||JayH - Thank you for the reply. My original post was not as clear as it should be.

First, our destination is actually a web service which we are accessing via a proxy / assembly. We call the assembly from a reference inside the script component. The goofy web service wants us to write the item record along with the categories to which the item belongs. The categories is basically an array that has to be declared.

Second, the categories are already defined on the web site. Each category has its own category ID. When we write the item record, we reference an array of category id so the web site software understands the relationship between the item and the category.

From a pseudo code perspective:
1) Select items from the item table.
2) Process each item one at a time.
3) For each item, find the associated category id's
4) Count the number of category id's for each item
5) Declare an array of category id's for each item using the count from #4 above
6) Populate the array with the category id's
7) Populate the "item record" with the category id array.
8) Call the web service via the assembly

Yes, the web service I am forced to use is a bit goofy but I cannot change it. :-(

Thank you for any thoughts...
|||Okay, I have a better picture now. I'm still not sure about where the item/category relationship is coming from, but if you can get the rows in an order like the following, then you should be able to create the array pretty easily in script.

Item 1 Category 1
Item 1 Category 2
Item 1 Category 3
Item 2 Category 3
Item 2 Category 2

If the data enters the script sorted by Item, then the script can evaluate each row and build an array of Categories for each item. When the Item changes from the previous row, you know you have all the Categories and you can make your web service call. The last row condition would need special handling and for that you should override the FinishOutputs method in your script so you can call the web service for the last Item.

Let me know if that sounds closer to what you're looking for.
|||JayH,

Yes, you have done a better job of explaining my issue that I have. I believe I comprehend the concept.

I am working on your concept now.

Be back later today. With me luck!

I am so remedial in SSIS...

How to Delete Records that are Linked with Relationships

Hello,
I am writing to ask if someone can tell me what the
command is to delete rows in an SQL 2000 database that are
linked through a foreign key relationship.
For example, I have a row in a "Persons" table that has a
primary key "Person ID". "Person ID" is then a foreign
key in two other tables. I would like to be able to
delete a person row from the "Persons" table and then
automatically have all associated rows based on
that "Person ID" in the other two tables deleted.
Thanks in advance!
MikeIn the design for the Persons table, open the relationship and make sure
'cascade delete' is on. This should do what you're asking...
Hope this helps...
"Mike Rogan" <mrogan@.carolinawebdev.com> wrote in message
news:046101c35559$bd142450$a401280a@.phx.gbl...
> Hello,
> I am writing to ask if someone can tell me what the
> command is to delete rows in an SQL 2000 database that are
> linked through a foreign key relationship.
> For example, I have a row in a "Persons" table that has a
> primary key "Person ID". "Person ID" is then a foreign
> key in two other tables. I would like to be able to
> delete a person row from the "Persons" table and then
> automatically have all associated rows based on
> that "Person ID" in the other two tables deleted.
> Thanks in advance!
> Mike