WikiAdmin: 1 revision imported

2023-07-14T13:00:37Z

1 revision imported

← Older revision

Revision as of 13:00, 14 July 2023

Intra>Chanson: /* Sample Sequence */

2018-05-11T16:28:09Z

Sample Sequence

New page

[[CATEGORY: 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. <PRE>Select * from artloc</PRE> or <Select * from <PRE>ARTPLANT where ACTIVE_FLAG='Y'</PRE>

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.

<PRE>
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)
</PRE>

==Special Cases==

===ARTPRICE - GROUPS===
Select:
<PRE>
SELECT * FROM ARTPRICE
WHERE PRICE_TYPE='G' AND ACTIVE_FLAG='Y'
</PRE>
MODEL HEADER:
<PRE>
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');
</PRE>
Model
<PRE>
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}' );
</PRE>

===PRTPOP===
Select:
<PRE>
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
</PRE>

===PRTFORMFACT===
Select:
<PRE>
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
</PRE>

===PRTLINKS===
Select:
<PRE>
SELECT L.* FROM PRTLINKS L
WHERE EXISTS (SELECT * FROM PRTCOMPDEF CD WHERE CD.LINK_ID=L.LINK_ID AND CD.ACTIVE_FLAG='Y')
</PRE>

===CCTOPTN===
Select System:
<PRE>
SELECT * FROM CCTOPTN
WHERE
SECTION_NAME='SYSTEM'
AND OPTION_NAME NOT IN ('SYSTEMDATE','FOREGROUNDQUERIES','PREVIEWZOOM','PREVIEWFONTNAME')
</PRE>

Select Default:
<PRE>
SELECT * FROM CCTOPTN
WHERE
SECTION_NAME<>'SYSTEM'
AND USER_GROUP_ID='DEFAULT'
</PRE>

===Inactive GL in ARALCLS===
Select:
<PRE>
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)
</PRE>

Replicating Keystone Data with QueryGen - Revision history

WikiAdmin: 1 revision imported

Intra>Chanson: /* Sample Sequence */