IBM SG24-6526-00 manual

4.Run the set of SQL commands that are detailed in the following SQL example. They cause triggers to be fired so that the System21 data will be synchronized into the VendorConnect database. You can find the SQL in the SQL to sync dbs on initial load.txt file in the SQL folder.

5.If the amount of data to be synchronized is large, you may need to edit the SQL to sub-select the data. After messages in the MQSeries queues subside, you can run the next sub-select.

Here is an example of SQL to be run for company Z1 and suppliers GS02 and FS03:

Planners:

update pmp06 set cono06 = 'Z1' where cono06 = 'Z1'

Suppliers and Receiving Addresses: update plp05 set cono05 = 'Z1' where cono05 = 'Z1' and

supn05 in ('GS02','FS03', 'PMSUP')

Note: Remember to include the “dummy” receiving address supplier ID in the SUPN05 list, usually ‘PMSUP’, because this file holds both supplier and receiving address details (see “System21 data setup” on page 87).

Purchase Order Headers:

update pmp02 set cono02 = 'Z1' where cono02 = 'Z1' and dtlc02 = 9999999 and

ordn02 in

(select distinct ordn03

from pmp03 where cono03 = 'Z1' and vndr03 in ('GS02','FS03') and qtos03 > 0 group by ordn03)

Purchase Order Lines:

update pmp03 set cono03 = 'Z1' where cono03 = 'Z1' and dtlc03 = 9999999 and

bosn03 <> 99999 and ordn03 in

(select distinct ordn03

from pmp03 where cono03 = 'Z1' and vndr03 in ('GS02','FS03') and qtos03 > 0 group by ordn03)

Purchase Order Transactions-Expected Receipts, Actual Receipts and Returns: update pmp09 set cono09 = 'Z1'

where cono09 = 'Z1' and ordn09 in

(select distinct ordn03

from pmp03 where cono03 = 'Z1' and vndr03 in ('GS02','FS03') and qtos03 > 0 group by ordn03)

104Geac System21 commerce.connect: Implementation on the iSeries Server