Replicating Keystone Data with QueryGen

This covers the process of using QueryGen to selectively replicate data from one Keystone company to another. This process is useful when you need to copy a good portion but not all.

Querygen compared to other tools can take a little more work but gives more flexibility to select individual records.

Make it easy on yourself

Keep the data in Interbase

Do NOT export the data then import it. This is a waste of time and effort. It introduces data errors.

Let QueryGen do the hard work

Take advantage of the Make Selection Query and Build Query options.

QueryGen Process

Selection Query

90% of the time you simply make a 'Select * from' query. E.G.

Select * from artloc

or <Select * from

ARTPLANT where ACTIVE_FLAG='Y'

When you want to select some of the data use the MK SEL QRY option. This gives you a list of fields where you can delete the unwanted ones and generate a customized select query.

Build Query

The BLD QRY tool is your friend. In most cases you do not need to touch the default options for non-transaction tables (e.g. customers, products etc.)

Generators(Session Numbers)

See: QueryGen#Generators

Incrementors(Transactions and Line Numbers)

See: QueryGen#Incrementors

Get your data in Order

The trick is to find dependencies. You can't add products without plants, product classes, gl accounts etc. You can't add plants without locations. You can't add gl account without gl groups.

The general sequence:

• Categories,Classes
• Locations,Plants
• Commonly referred to master tables (GL Accounts)
• Common master tables
• Highly dependent tables (Pricing)
• Transactions

Sample Sequence

This is a sample import sequence. This list would be expanded if other modules such as PO or CM were involved.

ARTLOC
GLTACGRP
GLTACCT
GLTSEG
GLTFSDEF
ARTTXAUT
ARTTXLOC
ARTTXSTR
ARTPLANT (some default svc chg & def disc gl could be missing if inactive)
ARTEXCLS
ARTUMS  (Merge Method for autogenerated)
ARTPRCLS
ARPRGRP
ARTSLSPN
ARTTRCLS
ARTDRIV
ARTOWNER
ARTTRUCK
ARTCTCLS
ARTCUCLS
ARTPRICE
ARTARCLS
ARTCUST
DITEVENTTYPE
QTTREASONS
CCTOPTN (SECTION_NAME=SYSTEM), (USER DEFAULT, NOT SYSTEM)
APTPTCLS (merge)
APTTNCLS
APTTXAUT
APTVEND
PRTWKCOMPCLASS
PRTDEPT
PRTSTCODE
PRTFORMULA
PRTFORMFACT
PRTLINKS
PRTCOMPDEF
PRTEVENTYPE (Consider autogenerated)
PRTDEPTGLXREF
PRTEMPLOYEE
PRTEMPDEF (AutoCreated - Update Values)
PRTEMPFACT (AuotCreated - Update Values)

Special Cases

ARTPRICE - GROUPS

Select:

SELECT * FROM ARTPRICE
WHERE PRICE_TYPE='G' AND ACTIVE_FLAG='Y'

MODEL HEADER:

CREATE  TABLE TMP_SNO(SNO INTEGER);
INSERT INTO TMP_SNO (SNO)  SELECT session_no FROM CCP_SESSION_BEGIN('ADMIN', 'NEW SCRIPT', '127.0.0.1', '2.9.91.0');

Model

INSERT INTO ARTPRICE
( SESSION_NO, TRANS_NO, PRICE_TYPE, CUST_NO, GROUP_NO, PRICE_CODE, PRICE_ZONE, PRODUCT_CLASS, PRICE_PLANT_NO, PRODUCT_CODE, UNIT_OF_MEASURE, QTY_LEVEL, UNIT_PRICE, UNIT_PCT_DISC, IMPORTED_FLAG, MEMO, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG )  VALUES 
( (SELECT SNO FROM TMP_SNO), {TRANS_NO|INC=1}, '{PRICE_TYPE}', '{CUST_NO}', '{GROUP_NO}', '{PRICE_CODE}', '{PRICE_ZONE}', '{PRODUCT_CLASS}', '{PRICE_PLANT_NO}', '{PRODUCT_CODE}', '{UNIT_OF_MEASURE}', {QTY_LEVEL}, {UNIT_PRICE}, '{UNIT_PCT_DISC}', '{IMPORTED_FLAG}', '{MEMO}', CAST('TODAY' AS TIMESTAMP), 'HIT', '{ACTIVE_FLAG}'    );

PRTPOP

Select:

SELECT POP. * FROM PRTPOP POP
  INNER JOIN PRTFORMULA F   ON F.FORMULA_ID=POP.FORMULA_ID AND F.ACTIVE_FLAG='Y'
  ORDER BY F.FORMULA_ID,POP.SEQUENCE

PRTFORMFACT

Select:

SELECT FF. * FROM PRTFORMFACT FF
  INNER JOIN PRTFORMULA F   ON F.FORMULA_ID=FF.FORMULA_ID AND F.ACTIVE_FLAG='Y'
  ORDER BY F.FORMULA_ID  

PRTLINKS

Select:

SELECT L.* FROM PRTLINKS L
WHERE EXISTS (SELECT * FROM PRTCOMPDEF CD WHERE CD.LINK_ID=L.LINK_ID AND CD.ACTIVE_FLAG='Y')

CCTOPTN

Select System:

SELECT * FROM CCTOPTN
WHERE 
 SECTION_NAME='SYSTEM'
AND OPTION_NAME NOT IN ('SYSTEMDATE','FOREGROUNDQUERIES','PREVIEWZOOM','PREVIEWFONTNAME')

Select Default:

SELECT * FROM CCTOPTN
WHERE 
 SECTION_NAME<>'SYSTEM'
AND USER_GROUP_ID='DEFAULT'

Inactive GL in ARALCLS

Select:

SELECT A.* FROM GLTACCT A
 INNER JOIN ARTALCLS AC ON AC.ALLOW_GL=A.GL_ACCOUNT AND A.ACTIVE_FLAG='N'
 (use default build create to add accts go gltacct)