104 Geac System21 commerce.connect: Implementation on the iSeries Server
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')
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)
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).
