KeystoneIntranet - User contributions [en]

A/P Balancing Queries

2025-12-29T19:16:34Z

Chanson: /* Irregular Use of A/P account in A/P system */

==A/P Raw Balance==
<PRE>
select sum(gross_amount-paid_amount-disc_taken) ap_apsys,
-(select sum(trx_amount) from glttrx where gl_account=:ap_account) ap_glsys,
sum(gross_amount-paid_amount-disc_taken)+(select sum(trx_amount) from glttrx where gl_account=:ap_account) difference
from aptinvhd
</PRE>

==A/P Transactions not from A/P system==
<PRE>
select session_no, gl_account, trx_date, trx_amount, last_change_datetime, description_1, source_app, source_table from glttrx
where gl_account=:ap_account and source_app<>'P'
and imported_flag='N' and trx_date>=:begdate order by trx_date desc
</PRE>

==Irregular Use of A/P account in A/P system==

===Non-AP Tranasctions from AP Account===
<PRE>
select session_no,trans_no,gl_account,trx_date,trx_amount,last_change_datetime,description_1,
source_app,source_table,source_TRANS_TYPE from glttrx
where source_app='P' and source_trans_type not in ('IP','CP','HP','VP','HX','HY')
and gl_account=:ap_account
</PRE>
Updated 12/29/2025 to add HX and HY to exclude list.

===AP Tranasctions from Non-AP Account===
<PRE>
select session_no,trans_no,gl_account,trx_date,trx_amount,last_change_datetime,description_1,
source_app,source_table,source_TRANS_TYPE from glttrx
where source_app='P' and source_trans_type in ('IP','CP','HP','VP','HX','HY')
and gl_account<>:ap_account
</PRE>
12/29/2025

==A/P Crossover Query==
<PRE>
Select 'A',session_no,g.trans_no gl_trans,i.trans_no ap_inv_trans,vend_id,invoice_no,invoice_date,g.trx_date gldate,i.gross_amount,
(select user_id from cctsessn cs where cs.session_no=i.session_no) chg_user,
g.last_change_datetime enter_date
from aptinvhd i
inner join glttrx g on
(g.source_session_no=i.session_no and g.source_trans_no=i.trans_no
and g.gl_account=:ap_account and g.trx_date<=:crossdate)
where i.invoice_date>:crossdate
union
Select 'B',session_no,g.trans_no gl_trans,i.trans_no ap_inv_trans,vend_id,invoice_no,invoice_date,g.trx_date gldate,i.gross_amount,
(select user_id from cctsessn cs where cs.session_no=i.session_no) chg_user,
g.last_change_datetime enter_date
from aptinvhd i
inner join glttrx g on
(g.source_session_no=i.session_no and g.source_trans_no=i.trans_no
and g.gl_account=:ap_account and g.trx_date>:crossdate)
where i.invoice_date<=:crossdate
</PRE>

==Invoices Paid before Invoice Date ==
<PRE>select c.trx_date,AI.INVOICE_DATE AP_HD_INV_DATE,ac.* from aptchkdt ac
inner join cmttrx c on c.session_no=ac.session_no and c.trans_no=ac.trans_no
INNER JOIN APTINVHD AI ON AC.AP_SESSION_NO=AI.SESSION_NO AND AC.AP_TRANS_NO=AI.TRANS_NO
where (ac.invoice_date>:Crossover_Date) and (c.trx_date<=:Crossover_Date)
</PRE>

==Invoice Gross Not Equal G/L Detail "IP" transactions==
<PRE>
select (select sum (trx_amount) from glttrx tx where tx.source_session_no=ih.session_no and tx.source_trans_no=ih.trans_no and source_trans_type='IP') "GL Amount",
IH.* from aptinvhd ih
where invoice_date > :"Begin Date"
and gross_amount+
(select sum (trx_amount) from glttrx tx where tx.source_session_no=ih.session_no and tx.source_trans_no=ih.trans_no and source_trans_type='IP') <>0
</PRE>

==Generic G/L Payments = Invoice Paid (W.I.P. Work needed)==
<PRE>
select
TX.GL_ACCOUNT,TX.TRX_AMOUNT,
cd.*,ih.paid_amount,ih.*
from aptinvhd ih
inner join aptchkdt cd on cd.ap_session_no=ih.session_no and cd.ap_trans_no=ih.trans_no
inner join glttrx tx on tx.source_session_no=cd.session_no and tx.source_trans_no=cd.trans_no and tx.source_line_no=cd.line_no and source_trans_type='CP'
where ih.invoice_date > :"Begin Date"
AND PAID_THIS_CHECK<>PAID_AMOUNT
</pre>

A/P Balancing Queries

2025-12-29T18:52:24Z

Chanson: /* Irregular Use of A/P account in A/P system */

==A/P Raw Balance==
<PRE>
select sum(gross_amount-paid_amount-disc_taken) ap_apsys,
-(select sum(trx_amount) from glttrx where gl_account=:ap_account) ap_glsys,
sum(gross_amount-paid_amount-disc_taken)+(select sum(trx_amount) from glttrx where gl_account=:ap_account) difference
from aptinvhd
</PRE>

==A/P Transactions not from A/P system==
<PRE>
select session_no, gl_account, trx_date, trx_amount, last_change_datetime, description_1, source_app, source_table from glttrx
where gl_account=:ap_account and source_app<>'P'
and imported_flag='N' and trx_date>=:begdate order by trx_date desc
</PRE>

==Irregular Use of A/P account in A/P system==
<PRE>
===Non-AP Tranasctions from AP Account===
select session_no,trans_no,gl_account,trx_date,trx_amount,last_change_datetime,description_1,
source_app,source_table,source_TRANS_TYPE from glttrx
where source_app='P' and source_trans_type not in ('IP','CP','HP','VP','HX','HY')
and gl_account=:ap_account
</PRE>
Updated 12/29/2025 to add HX and HY to exclude list.

===AP Tranasctions from Non-AP Account===
<PRE>
select session_no,trans_no,gl_account,trx_date,trx_amount,last_change_datetime,description_1,
source_app,source_table,source_TRANS_TYPE from glttrx
where source_app='P' and source_trans_type in ('IP','CP','HP','VP','HX','HY')
and gl_account<>:ap_account
</PRE>
12/29/2025

==A/P Crossover Query==
<PRE>
Select 'A',session_no,g.trans_no gl_trans,i.trans_no ap_inv_trans,vend_id,invoice_no,invoice_date,g.trx_date gldate,i.gross_amount,
(select user_id from cctsessn cs where cs.session_no=i.session_no) chg_user,
g.last_change_datetime enter_date
from aptinvhd i
inner join glttrx g on
(g.source_session_no=i.session_no and g.source_trans_no=i.trans_no
and g.gl_account=:ap_account and g.trx_date<=:crossdate)
where i.invoice_date>:crossdate
union
Select 'B',session_no,g.trans_no gl_trans,i.trans_no ap_inv_trans,vend_id,invoice_no,invoice_date,g.trx_date gldate,i.gross_amount,
(select user_id from cctsessn cs where cs.session_no=i.session_no) chg_user,
g.last_change_datetime enter_date
from aptinvhd i
inner join glttrx g on
(g.source_session_no=i.session_no and g.source_trans_no=i.trans_no
and g.gl_account=:ap_account and g.trx_date>:crossdate)
where i.invoice_date<=:crossdate
</PRE>

==Invoices Paid before Invoice Date ==
<PRE>select c.trx_date,AI.INVOICE_DATE AP_HD_INV_DATE,ac.* from aptchkdt ac
inner join cmttrx c on c.session_no=ac.session_no and c.trans_no=ac.trans_no
INNER JOIN APTINVHD AI ON AC.AP_SESSION_NO=AI.SESSION_NO AND AC.AP_TRANS_NO=AI.TRANS_NO
where (ac.invoice_date>:Crossover_Date) and (c.trx_date<=:Crossover_Date)
</PRE>

==Invoice Gross Not Equal G/L Detail "IP" transactions==
<PRE>
select (select sum (trx_amount) from glttrx tx where tx.source_session_no=ih.session_no and tx.source_trans_no=ih.trans_no and source_trans_type='IP') "GL Amount",
IH.* from aptinvhd ih
where invoice_date > :"Begin Date"
and gross_amount+
(select sum (trx_amount) from glttrx tx where tx.source_session_no=ih.session_no and tx.source_trans_no=ih.trans_no and source_trans_type='IP') <>0
</PRE>

==Generic G/L Payments = Invoice Paid (W.I.P. Work needed)==
<PRE>
select
TX.GL_ACCOUNT,TX.TRX_AMOUNT,
cd.*,ih.paid_amount,ih.*
from aptinvhd ih
inner join aptchkdt cd on cd.ap_session_no=ih.session_no and cd.ap_trans_no=ih.trans_no
inner join glttrx tx on tx.source_session_no=cd.session_no and tx.source_trans_no=cd.trans_no and tx.source_line_no=cd.line_no and source_trans_type='CP'
where ih.invoice_date > :"Begin Date"
AND PAID_THIS_CHECK<>PAID_AMOUNT
</pre>

A/P Balancing Queries

2025-12-29T17:17:34Z

Chanson: /* Irregular Use of A/P account in A/P system */

==A/P Raw Balance==
<PRE>
select sum(gross_amount-paid_amount-disc_taken) ap_apsys,
-(select sum(trx_amount) from glttrx where gl_account=:ap_account) ap_glsys,
sum(gross_amount-paid_amount-disc_taken)+(select sum(trx_amount) from glttrx where gl_account=:ap_account) difference
from aptinvhd
</PRE>

==A/P Transactions not from A/P system==
<PRE>
select session_no, gl_account, trx_date, trx_amount, last_change_datetime, description_1, source_app, source_table from glttrx
where gl_account=:ap_account and source_app<>'P'
and imported_flag='N' and trx_date>=:begdate order by trx_date desc
</PRE>

==Irregular Use of A/P account in A/P system==
<PRE>
select session_no,trans_no,gl_account,trx_date,trx_amount,last_change_datetime,description_1,
source_app,source_table,source_TRANS_TYPE from glttrx
where source_app='P' and source_trans_type not in ('IP','CP','HP','VP','HX','HY')
and gl_account=:ap_account
</PRE>

Updated 12/29/2025 to add HX and HY to exclude list.

==A/P Crossover Query==
<PRE>
Select 'A',session_no,g.trans_no gl_trans,i.trans_no ap_inv_trans,vend_id,invoice_no,invoice_date,g.trx_date gldate,i.gross_amount,
(select user_id from cctsessn cs where cs.session_no=i.session_no) chg_user,
g.last_change_datetime enter_date
from aptinvhd i
inner join glttrx g on
(g.source_session_no=i.session_no and g.source_trans_no=i.trans_no
and g.gl_account=:ap_account and g.trx_date<=:crossdate)
where i.invoice_date>:crossdate
union
Select 'B',session_no,g.trans_no gl_trans,i.trans_no ap_inv_trans,vend_id,invoice_no,invoice_date,g.trx_date gldate,i.gross_amount,
(select user_id from cctsessn cs where cs.session_no=i.session_no) chg_user,
g.last_change_datetime enter_date
from aptinvhd i
inner join glttrx g on
(g.source_session_no=i.session_no and g.source_trans_no=i.trans_no
and g.gl_account=:ap_account and g.trx_date>:crossdate)
where i.invoice_date<=:crossdate
</PRE>

==Invoices Paid before Invoice Date ==
<PRE>select c.trx_date,AI.INVOICE_DATE AP_HD_INV_DATE,ac.* from aptchkdt ac
inner join cmttrx c on c.session_no=ac.session_no and c.trans_no=ac.trans_no
INNER JOIN APTINVHD AI ON AC.AP_SESSION_NO=AI.SESSION_NO AND AC.AP_TRANS_NO=AI.TRANS_NO
where (ac.invoice_date>:Crossover_Date) and (c.trx_date<=:Crossover_Date)
</PRE>

==Invoice Gross Not Equal G/L Detail "IP" transactions==
<PRE>
select (select sum (trx_amount) from glttrx tx where tx.source_session_no=ih.session_no and tx.source_trans_no=ih.trans_no and source_trans_type='IP') "GL Amount",
IH.* from aptinvhd ih
where invoice_date > :"Begin Date"
and gross_amount+
(select sum (trx_amount) from glttrx tx where tx.source_session_no=ih.session_no and tx.source_trans_no=ih.trans_no and source_trans_type='IP') <>0
</PRE>

==Generic G/L Payments = Invoice Paid (W.I.P. Work needed)==
<PRE>
select
TX.GL_ACCOUNT,TX.TRX_AMOUNT,
cd.*,ih.paid_amount,ih.*
from aptinvhd ih
inner join aptchkdt cd on cd.ap_session_no=ih.session_no and cd.ap_trans_no=ih.trans_no
inner join glttrx tx on tx.source_session_no=cd.session_no and tx.source_trans_no=cd.trans_no and tx.source_line_no=cd.line_no and source_trans_type='CP'
where ih.invoice_date > :"Begin Date"
AND PAID_THIS_CHECK<>PAID_AMOUNT
</pre>

Main Page

2025-01-30T14:29:12Z

Chanson:

{| border="0" cellspacing="0" cellpadding="5" align="right"
|[[Tags & Formatting Guide ]]
|-
|[[GH Wiki Standards Guide ]]
|-
|[[More Wiki Info]]
|}
__NOTOC__
 

Online Support & Documentation System 

{| {{table}}
| align="center" style="background:#f0f0f0;"|'''Announcements'''
| align="center" style="background:#f0f0f0;"|'''What's New'''
|-
|
:[[New Keystone Releases]] 4.7.13 - 1/23/2025
:[[Support Alerts]]
:[https://givenhansco.sharepoint.com/:x:/s/supportteam/EVr7IrPNSghNruEGW9odUvcB-MeaRucs3z9tJBJH1RIKJw?e=PE3iQR Keystone 5.x Internal Build Testing Spreadsheet]
:[https://givenhansco.sharepoint.com/:x:/s/supportteam/ET5kQCpCQuBCu46qHGtYwgABmovrGxOC8jOlUtpVITGmng?e=VMY2iN Keystone 4.x Internal Build Testing Spreadsheet]
:[https://onedrive.live.com/redir.aspx?cid=c163067a39df96c2&resid=C163067A39DF96C2!4481&parId=C163067A39DF96C2!4480&authkey=!AqcheqeKn8ris7g&Bpub=SDX.SkyDrive&Bsrc=Share&ref=button Keystone 3.x Internal Build Testing Spreadsheet]
:[https://1drv.ms/x/s!AklOmdIh80nxrCJJA_D6IVxpE4RL Development Tasks Spreadsheet]
:[https://ghwebdev.azurewebsites.net Keystone Online Dev Site]
:[https://givenhansco.sharepoint.com/Lists/Share%20Calendars/Color%20Calendar.aspx Company SharePoint Calendar]
|
:[[iSolved Keystone Integration]] Jan. 2025
:[[Support_Alerts#Dispatch_Schedule_order_shows_the_wrong_Qty_Delivered_-_12/5/2024 |Dispatch Schedule order shows the wrong Qty Delivered]] 12/5/24
:[[Support_Alerts#Tickets_may_be_associated_with_the_wrong_invoice_using_Print_Invoices_in_Keystone_4.7_and_4.7.1_-_10/10/2024 |Tickets associated with wrong invoice in Keystone 4.7/4.7.1]] 10/10/24
:[[Do This after updating number of licensed InterBase users]] 2/29/24
:[[Backup/Restore Issues#Before_Restoring_a_company_database,_check_this!_(version_4.0.1+) |Before Restoring a company backup, check this!]] 1/11/22
:[[Install/Update Issues#Keystone_database_upgrade_fails_.28version_4.0.1.2B.29 |Improved handling for database upgrade failures in Keystone 4.0.1+]] 10/27/21
:[[Microsoft Edge Chromium required for Keystone 4]] 08/22/21
:[[Configure InterBase ODBC Access]] 03/05/21
:[[AR: Fix Leading/Trailing Spaces in Jobs|Fix Leading/Trailing Spaces in Jobs]] (09/23/20)
:[[AR: Update Sales Costs from Product Standard Cost|Update Sales Costs from Product Standard Cost]] 11/4/19
:[[Configuring Keystone for E-Ticketing]] 10/04/18
:[[Fixing a Double AR void Invoice]] 10/03/18
|}

= Keystone =
{|border="0" cellspacing="5" cellpadding="5" align="center"
|[[Accounts Receivable And Billing Info |AR]]
|[[Inventory Module|Inv]]
|[[Accounts Payable System|A/P]]
|[[General Ledger System|G/L]]
|[[Payroll System|PR]]
|[[Cash Management System|Cash]]
|[[Job Cost System|JC]]
|[[Keystone Ticket Printing System|Ticketing]]
|[[Keystone Dispatch System |Dispatch]]
|[[Keystone Document Imaging |Doc Img]]
|[[Keystone On Demand | On Demand (mobile)]]
|}

== Keystone Quick Links ==

===Common System Topics===
:[[Keystone REST Server (KServer)]]
:[[Keystone Company Database Backup Utility]]
:[[QueryGen]]
:[[Keystone Security]]
:[[Keystone Constants]] Decodes tables lookup values e.g. payroll run types, cash man. trans types. (Updated 01/06/23)
:[[Keystone Installation and Configuration]]
:[[Command Import]]

===Common Software Topics===
:[[Export Tickets to Accounting]] (Including EXF)
:[http://keystonehelp.compucrete.com Customer Help Website]
:[[A/R and Billing: Training Outline]]
:[[DeReconcile Inventory]] Undo 1 or more Inv. Recon sessions. (7/24/13)
:[[Payroll: Federal & State Electronic Filing Guides]]

===External/Third Party Devices===
:[[Device Server and Device Aggregator]]
:[[Device Aggregator/Device Server Issues]]
:[[Batch Control Devices]]
:[[GPS/Status Devices]]
:[[Notification Devices]]
:[[HaulHub (Tickets to State DOT)]]

===Interbase Database===
:[[InterBase]]
:[[Interbase System Table Queries ( RDB$ )]]
:[[Database Tips]] (Restore Empty DB)
:[[InterBase Issues]] 
:[[InterBase#Installation_on_64-bit_Windows_10_Creators_Update_.28Build_1703.29_and_higher | Interbase Install Fix for Windows 10 Creators]] (07/17/17)
:[[Database Corruption]] Using '''GHIBDataPump'''
:[[Configure InterBase ODBC Access]]
:[[Do This after updating number of licensed InterBase users]]

===Windows===
:[[Windows 7 and Keystone]]
:[[Keystone 2.5 Changes]] '''with new directory chart'''

===Keystone Software Info===
====Accounting====
:[[General Ledger: Financial Statements with Bigger Print]]
:[[DeReconcile Inventory]] Undo 1 or more Inv. Recon sessions. (7/24/13)
:[[Sales Tax Handling in Keystone 2.3]] (Includes Sales Tax Collection) 10/15/09 10/13/17
:[[Updating the Base Product Code for 1.8 System]] - Methods for setting up Base Product Codes
:[[Time Calculation Queries]] - Demonstration query for time calculation 5/19/08
:[[Zone History Analysis]] - Analyze Zone Times for Dispatch - 5/15/08

====Forms====
:[[Keystone Forms]]
:[[Sample Forms]]
:[[Keystone Form Colors]] The Color De-Coder Ring. (Yes we support the colors blanched almond and lemon chiffon!)
:[[Payroll: Form Versions]] Now supports '''*SUM''' to total all defs in a check group. (07/07/17)

:[[Invoice Form Fields]] (also includes sample adding ticket image to form)
:[[Ticket Form Fields]]
:[[Quote Form Fields]] 10/10/19
:[[Plant Cash Receipt Form Fields]] 03/07/18
:[[PO Form Fields]] 03/07/2018

:[[AP Check Form Fields]] 12/14/2022

====Config====
:[[Company Database Connection Pooling]]
:[[iSolved Keystone Integration]]
:[[Keystone Constants]] Decodes tables lookup values e.g. payroll run types, cash man. trans types.
:[[Keystone Experimental Features]]
:[[Keystone Security]] (Wiki Page)
:[[Keystone Telemetry]]

===Documents===
:[[Media:Web Update.pdf]]
:[[Keystone Accounting Installer Guidelines]]

===Development Etc===
:[[Online Users Conference 2011]] - 3/2/2011
:[[Keystone 3.x Development]]
:[[Development Topics]]
:[[Development How To]]

==Batch Controls Importing==
:[[Batch Control Ticket Interfaces]]
:[[Batch Ticket Import]]

==GPS==
*[[GPS System Setup]] Modern GPS System Notes
*[[GPS System Diagnostics]] Diagnostic information for GPS systems

== Keystone Tools ==
:[[Query Export]]
::[[Most Excellent Queries]]
:[[Keystone Email Setup]]
:[[Datascope System | DataScope]]
::[[DataScope Examples]] * [[DataScope G/L Finanacial Statements]]

== Tools for Supporting Keystone ==
:[[QueryGen]]
:[[Importing Accounting Data]]
:[[Communications Tools]]
:[[Keystone Support Backup]] Company Database Backup utility for support

== Troubleshooting ==
:[[Application Server Diagnostics]]
:[[Install/Update Issues]] 
:[[Backup/Restore Issues]] 
:[[Keystone System Cache]] 
:[[Error Messages and Meanings]] 
:[[Restoring the Empty Database Template]] 
:[[Disk Space Issues]] 
:[[PC/Network Support]] 
:[[Remote Desktop Tips]]
:[[Performance Issues]]

= General Info =
:[[Windows Utilities]]
:[http://technet.microsoft.com/en-us/windows/jj728757 Windows Shortcut Keys (TechNet)]

Main Page

2025-01-30T14:28:51Z

Chanson:

{| border="0" cellspacing="0" cellpadding="5" align="right"
|[[Tags & Formatting Guide ]]
|-
|[[GH Wiki Standards Guide ]]
|-
|[[More Wiki Info]]
|}
__NOTOC__
 

Online Support & Documentation System 

{| {{table}}
| align="center" style="background:#f0f0f0;"|'''Announcements'''
| align="center" style="background:#f0f0f0;"|'''What's New'''
|-
|
:[[New Keystone Releases]] 4.7.13 - 1/23/2025
:[[Support Alerts]]
:[https://givenhansco.sharepoint.com/:x:/s/supportteam/EVr7IrPNSghNruEGW9odUvcB-MeaRucs3z9tJBJH1RIKJw?e=PE3iQR Keystone 5.x Internal Build Testing Spreadsheet]
:[https://givenhansco.sharepoint.com/:x:/s/supportteam/ET5kQCpCQuBCu46qHGtYwgABmovrGxOC8jOlUtpVITGmng?e=VMY2iN Keystone 4.x Internal Build Testing Spreadsheet]
:[https://onedrive.live.com/redir.aspx?cid=c163067a39df96c2&resid=C163067A39DF96C2!4481&parId=C163067A39DF96C2!4480&authkey=!AqcheqeKn8ris7g&Bpub=SDX.SkyDrive&Bsrc=Share&ref=button Keystone 3.x Internal Build Testing Spreadsheet]
:[https://1drv.ms/x/s!AklOmdIh80nxrCJJA_D6IVxpE4RL Development Tasks Spreadsheet]
:[https://ghwebdev.azurewebsites.net Keystone Online Dev Site]
:[https://givenhansco.sharepoint.com/Lists/Share%20Calendars/Color%20Calendar.aspx Company SharePoint Calendar]
|
::[[iSolved Keystone Integration]] Jan. 2025
:[[Support_Alerts#Dispatch_Schedule_order_shows_the_wrong_Qty_Delivered_-_12/5/2024 |Dispatch Schedule order shows the wrong Qty Delivered]] 12/5/24
:[[Support_Alerts#Tickets_may_be_associated_with_the_wrong_invoice_using_Print_Invoices_in_Keystone_4.7_and_4.7.1_-_10/10/2024 |Tickets associated with wrong invoice in Keystone 4.7/4.7.1]] 10/10/24
:[[Do This after updating number of licensed InterBase users]] 2/29/24
:[[Backup/Restore Issues#Before_Restoring_a_company_database,_check_this!_(version_4.0.1+) |Before Restoring a company backup, check this!]] 1/11/22
:[[Install/Update Issues#Keystone_database_upgrade_fails_.28version_4.0.1.2B.29 |Improved handling for database upgrade failures in Keystone 4.0.1+]] 10/27/21
:[[Microsoft Edge Chromium required for Keystone 4]] 08/22/21
:[[Configure InterBase ODBC Access]] 03/05/21
:[[AR: Fix Leading/Trailing Spaces in Jobs|Fix Leading/Trailing Spaces in Jobs]] (09/23/20)
:[[AR: Update Sales Costs from Product Standard Cost|Update Sales Costs from Product Standard Cost]] 11/4/19
:[[Configuring Keystone for E-Ticketing]] 10/04/18
:[[Fixing a Double AR void Invoice]] 10/03/18
|}

= Keystone =
{|border="0" cellspacing="5" cellpadding="5" align="center"
|[[Accounts Receivable And Billing Info |AR]]
|[[Inventory Module|Inv]]
|[[Accounts Payable System|A/P]]
|[[General Ledger System|G/L]]
|[[Payroll System|PR]]
|[[Cash Management System|Cash]]
|[[Job Cost System|JC]]
|[[Keystone Ticket Printing System|Ticketing]]
|[[Keystone Dispatch System |Dispatch]]
|[[Keystone Document Imaging |Doc Img]]
|[[Keystone On Demand | On Demand (mobile)]]
|}

== Keystone Quick Links ==

===Common System Topics===
:[[Keystone REST Server (KServer)]]
:[[Keystone Company Database Backup Utility]]
:[[QueryGen]]
:[[Keystone Security]]
:[[Keystone Constants]] Decodes tables lookup values e.g. payroll run types, cash man. trans types. (Updated 01/06/23)
:[[Keystone Installation and Configuration]]
:[[Command Import]]

===Common Software Topics===
:[[Export Tickets to Accounting]] (Including EXF)
:[http://keystonehelp.compucrete.com Customer Help Website]
:[[A/R and Billing: Training Outline]]
:[[DeReconcile Inventory]] Undo 1 or more Inv. Recon sessions. (7/24/13)
:[[Payroll: Federal & State Electronic Filing Guides]]

===External/Third Party Devices===
:[[Device Server and Device Aggregator]]
:[[Device Aggregator/Device Server Issues]]
:[[Batch Control Devices]]
:[[GPS/Status Devices]]
:[[Notification Devices]]
:[[HaulHub (Tickets to State DOT)]]

===Interbase Database===
:[[InterBase]]
:[[Interbase System Table Queries ( RDB$ )]]
:[[Database Tips]] (Restore Empty DB)
:[[InterBase Issues]] 
:[[InterBase#Installation_on_64-bit_Windows_10_Creators_Update_.28Build_1703.29_and_higher | Interbase Install Fix for Windows 10 Creators]] (07/17/17)
:[[Database Corruption]] Using '''GHIBDataPump'''
:[[Configure InterBase ODBC Access]]
:[[Do This after updating number of licensed InterBase users]]

===Windows===
:[[Windows 7 and Keystone]]
:[[Keystone 2.5 Changes]] '''with new directory chart'''

===Keystone Software Info===
====Accounting====
:[[General Ledger: Financial Statements with Bigger Print]]
:[[DeReconcile Inventory]] Undo 1 or more Inv. Recon sessions. (7/24/13)
:[[Sales Tax Handling in Keystone 2.3]] (Includes Sales Tax Collection) 10/15/09 10/13/17
:[[Updating the Base Product Code for 1.8 System]] - Methods for setting up Base Product Codes
:[[Time Calculation Queries]] - Demonstration query for time calculation 5/19/08
:[[Zone History Analysis]] - Analyze Zone Times for Dispatch - 5/15/08

====Forms====
:[[Keystone Forms]]
:[[Sample Forms]]
:[[Keystone Form Colors]] The Color De-Coder Ring. (Yes we support the colors blanched almond and lemon chiffon!)
:[[Payroll: Form Versions]] Now supports '''*SUM''' to total all defs in a check group. (07/07/17)

:[[Invoice Form Fields]] (also includes sample adding ticket image to form)
:[[Ticket Form Fields]]
:[[Quote Form Fields]] 10/10/19
:[[Plant Cash Receipt Form Fields]] 03/07/18
:[[PO Form Fields]] 03/07/2018

:[[AP Check Form Fields]] 12/14/2022

====Config====
:[[Company Database Connection Pooling]]
:[[iSolved Keystone Integration]]
:[[Keystone Constants]] Decodes tables lookup values e.g. payroll run types, cash man. trans types.
:[[Keystone Experimental Features]]
:[[Keystone Security]] (Wiki Page)
:[[Keystone Telemetry]]

===Documents===
:[[Media:Web Update.pdf]]
:[[Keystone Accounting Installer Guidelines]]

===Development Etc===
:[[Online Users Conference 2011]] - 3/2/2011
:[[Keystone 3.x Development]]
:[[Development Topics]]
:[[Development How To]]

==Batch Controls Importing==
:[[Batch Control Ticket Interfaces]]
:[[Batch Ticket Import]]

==GPS==
*[[GPS System Setup]] Modern GPS System Notes
*[[GPS System Diagnostics]] Diagnostic information for GPS systems

== Keystone Tools ==
:[[Query Export]]
::[[Most Excellent Queries]]
:[[Keystone Email Setup]]
:[[Datascope System | DataScope]]
::[[DataScope Examples]] * [[DataScope G/L Finanacial Statements]]

== Tools for Supporting Keystone ==
:[[QueryGen]]
:[[Importing Accounting Data]]
:[[Communications Tools]]
:[[Keystone Support Backup]] Company Database Backup utility for support

== Troubleshooting ==
:[[Application Server Diagnostics]]
:[[Install/Update Issues]] 
:[[Backup/Restore Issues]] 
:[[Keystone System Cache]] 
:[[Error Messages and Meanings]] 
:[[Restoring the Empty Database Template]] 
:[[Disk Space Issues]] 
:[[PC/Network Support]] 
:[[Remote Desktop Tips]]
:[[Performance Issues]]

= General Info =
:[[Windows Utilities]]
:[http://technet.microsoft.com/en-us/windows/jj728757 Windows Shortcut Keys (TechNet)]

DataScope: Match G/L Activity to Cash Posting Detail by Session / Date

2025-01-02T22:13:08Z

Chanson: /* Verify "CC" transactions againste change in cash: */

Diagnose Cash Posting vs. G/L postings:

==Verify "CC" transactions againste change in cash:==
THIS NEEDS WORK!!!

===DataScope Method===
<PRE>
select 'G' SRC,SESSION_NO,TRX_DATE,-TRX_AMOUNT AMOUNT from glttrx
where source_app='R' and source_trans_type='CC'
and trx_date between '1/1/24' and '12/31/24'
UNION
SELECT 'C',D.SESSION_NO,H.POST_DATE,D.CREDIT_AMOUNT-D.ALLOW_AMOUNT-D.DISC_AMOUNT AMOUNT
FROM ARTCSHDT D
INNER JOIN ARTCSHHD H ON H.SESSION_NO=D.SESSION_NO AND H.TRANS_NO=D.TRANS_NO
where post_date between '1/1/24' and '12/31/24'
</PRE>

===Query Method===
<pre>
with aaa (Src,SesNo,TrxDate,TrxAmt) as
(
select 'G' SRC,SESSION_NO,TRX_DATE,-TRX_AMOUNT AMOUNT from glttrx
where source_app='R' and source_trans_type='CC'
and trx_date between '1/1/24' and '12/31/24'
UNION
SELECT 'C',D.SESSION_NO,H.POST_DATE,D.CREDIT_AMOUNT-D.ALLOW_AMOUNT-D.DISC_AMOUNT AMOUNT
FROM ARTCSHDT D
INNER JOIN ARTCSHHD H ON H.SESSION_NO=D.SESSION_NO AND H.TRANS_NO=D.TRANS_NO
where post_date between '1/1/24' and '12/31/24'
)
select sesno,trxdate,sum(trxamt) Total from aaa
group by sesno,trxdate
having sum(trxamt)<>0
</pre>

==Verify "CR" transactions against change in receivables:==

===DataScope Method:===
All sessions are included. Use datascope to total the G and C and look for non zero amounts.
<PRE>
select 'G' SRC,SESSION_NO,TRX_DATE,TRX_AMOUNT AMOUNT from glttrx
where source_app='R' and source_trans_type IN ('CR','PR','CV')
and trx_date between '1/1/24' and '12/31/24'
UNION
SELECT 'C',D.SESSION_NO,H.POST_DATE,D.CREDIT_AMOUNT AMOUNT
FROM ARTCSHDT D
INNER JOIN ARTCSHHD H ON H.SESSION_NO=D.SESSION_NO AND H.TRANS_NO=D.TRANS_NO
where post_date between '1/1/24' and '12/31/24'
</PRE>

'''Data:''' Amount 
'''Row:''' Trx_Date,Session_no 
'''Column:''' SRC

Look for Grand Total column with a non-zero amount.

===Query Method:===
Finds only the mismatched items. Use modern IB that support derived (with) queries:
<pre>
with aaa (Src,SesNo,TrxDate,TrxAmt) as
(
select 'G' SRC,SESSION_NO,TRX_DATE,TRX_AMOUNT AMOUNT from glttrx
where source_app='R' and source_trans_type IN ('CR','PR','CV')
and trx_date between '7/1/24' and '12/31/24'
UNION
SELECT 'C',D.SESSION_NO,H.POST_DATE,D.CREDIT_AMOUNT AMOUNT
FROM ARTCSHDT D
INNER JOIN ARTCSHHD H ON H.SESSION_NO=D.SESSION_NO AND H.TRANS_NO=D.TRANS_NO
where post_date between '7/1/24' and '12/31/24'
)
select sesno,trxdate,sum(trxamt) Total from aaa
group by sesno,trxdate
having sum(trxamt)<>0
</pre>

DataScope: Match G/L Activity to Cash Posting Detail by Session / Date

2025-01-02T22:11:35Z

Chanson:

Diagnose Cash Posting vs. G/L postings:

==Verify "CC" transactions againste change in cash:==

===DataScope Method===
<PRE>
select 'G' SRC,SESSION_NO,TRX_DATE,-TRX_AMOUNT AMOUNT from glttrx
where source_app='R' and source_trans_type='CC'
and trx_date between '1/1/24' and '12/31/24'
UNION
SELECT 'C',D.SESSION_NO,H.POST_DATE,D.CREDIT_AMOUNT-D.ALLOW_AMOUNT-D.DISC_AMOUNT AMOUNT
FROM ARTCSHDT D
INNER JOIN ARTCSHHD H ON H.SESSION_NO=D.SESSION_NO AND H.TRANS_NO=D.TRANS_NO
where post_date between '1/1/24' and '12/31/24'
</PRE>

===Query Method===
<pre>
with aaa (Src,SesNo,TrxDate,TrxAmt) as
(
select 'G' SRC,SESSION_NO,TRX_DATE,-TRX_AMOUNT AMOUNT from glttrx
where source_app='R' and source_trans_type='CC'
and trx_date between '1/1/24' and '12/31/24'
UNION
SELECT 'C',D.SESSION_NO,H.POST_DATE,D.CREDIT_AMOUNT-D.ALLOW_AMOUNT-D.DISC_AMOUNT AMOUNT
FROM ARTCSHDT D
INNER JOIN ARTCSHHD H ON H.SESSION_NO=D.SESSION_NO AND H.TRANS_NO=D.TRANS_NO
where post_date between '1/1/24' and '12/31/24'
)
select sesno,trxdate,sum(trxamt) Total from aaa
group by sesno,trxdate
having sum(trxamt)<>0
</pre>

==Verify "CR" transactions against change in receivables:==

===DataScope Method:===
All sessions are included. Use datascope to total the G and C and look for non zero amounts.
<PRE>
select 'G' SRC,SESSION_NO,TRX_DATE,TRX_AMOUNT AMOUNT from glttrx
where source_app='R' and source_trans_type IN ('CR','PR','CV')
and trx_date between '1/1/24' and '12/31/24'
UNION
SELECT 'C',D.SESSION_NO,H.POST_DATE,D.CREDIT_AMOUNT AMOUNT
FROM ARTCSHDT D
INNER JOIN ARTCSHHD H ON H.SESSION_NO=D.SESSION_NO AND H.TRANS_NO=D.TRANS_NO
where post_date between '1/1/24' and '12/31/24'
</PRE>

'''Data:''' Amount 
'''Row:''' Trx_Date,Session_no 
'''Column:''' SRC

Look for Grand Total column with a non-zero amount.

===Query Method:===
Finds only the mismatched items. Use modern IB that support derived (with) queries:
<pre>
with aaa (Src,SesNo,TrxDate,TrxAmt) as
(
select 'G' SRC,SESSION_NO,TRX_DATE,TRX_AMOUNT AMOUNT from glttrx
where source_app='R' and source_trans_type IN ('CR','PR','CV')
and trx_date between '7/1/24' and '12/31/24'
UNION
SELECT 'C',D.SESSION_NO,H.POST_DATE,D.CREDIT_AMOUNT AMOUNT
FROM ARTCSHDT D
INNER JOIN ARTCSHHD H ON H.SESSION_NO=D.SESSION_NO AND H.TRANS_NO=D.TRANS_NO
where post_date between '7/1/24' and '12/31/24'
)
select sesno,trxdate,sum(trxamt) Total from aaa
group by sesno,trxdate
having sum(trxamt)<>0
</pre>

DataScope: Match G/L Activity to Cash Posting Detail by Session / Date

2025-01-02T22:10:00Z

Chanson: /* Verify "CC" transactions againste change in cash: */

Diagnose Cash Posting vs. G/L postings:

==Verify "CC" transactions againste change in cash:==
<PRE>
select 'G' SRC,SESSION_NO,TRX_DATE,-TRX_AMOUNT AMOUNT from glttrx
where source_app='R' and source_trans_type='CC'
and trx_date between '1/1/24' and '12/31/24'
UNION
SELECT 'C',D.SESSION_NO,H.POST_DATE,D.CREDIT_AMOUNT-D.ALLOW_AMOUNT-D.DISC_AMOUNT AMOUNT
FROM ARTCSHDT D
INNER JOIN ARTCSHHD H ON H.SESSION_NO=D.SESSION_NO AND H.TRANS_NO=D.TRANS_NO
where post_date between '1/1/24' and '12/31/24'
</PRE>

==Verify "CR" transactions againste change in receivables:==

===DataScope Method:===
All sessions are included. Use datascope to total the G and C and look for non zero amounts.
<PRE>
select 'G' SRC,SESSION_NO,TRX_DATE,TRX_AMOUNT AMOUNT from glttrx
where source_app='R' and source_trans_type IN ('CR','PR','CV')
and trx_date between '1/1/24' and '12/31/24'
UNION
SELECT 'C',D.SESSION_NO,H.POST_DATE,D.CREDIT_AMOUNT AMOUNT
FROM ARTCSHDT D
INNER JOIN ARTCSHHD H ON H.SESSION_NO=D.SESSION_NO AND H.TRANS_NO=D.TRANS_NO
where post_date between '1/1/24' and '12/31/24'
</PRE>

'''Data:''' Amount 
'''Row:''' Trx_Date,Session_no 
'''Column:''' SRC

Look for Grand Total column with a non-zero amount.

===Query Method:===
Finds only the mismatched items. Use modern IB that support derived (with) queries:
<pre>
with aaa (Src,SesNo,TrxDate,TrxAmt) as
(
select 'G' SRC,SESSION_NO,TRX_DATE,TRX_AMOUNT AMOUNT from glttrx
where source_app='R' and source_trans_type IN ('CR','PR','CV')
and trx_date between '7/1/24' and '12/31/24'
UNION
SELECT 'C',D.SESSION_NO,H.POST_DATE,D.CREDIT_AMOUNT AMOUNT
FROM ARTCSHDT D
INNER JOIN ARTCSHHD H ON H.SESSION_NO=D.SESSION_NO AND H.TRANS_NO=D.TRANS_NO
where post_date between '7/1/24' and '12/31/24'
)
select sesno,trxdate,sum(trxamt) Total from aaa
group by sesno,trxdate
having sum(trxamt)<>0
</pre>

DataScope: Match G/L Activity to Cash Posting Detail by Session / Date

2025-01-02T22:08:29Z

Chanson:

Diagnose Cash Posting vs. G/L postings:

==Verify "CC" transactions againste change in cash:==
<PRE>
select 'G' SRC,SESSION_NO,TRX_DATE,-TRX_AMOUNT AMOUNT from glttrx
where source_app='R' and source_trans_type='CC'
UNION
SELECT 'C',D.SESSION_NO,H.POST_DATE,D.CREDIT_AMOUNT-D.ALLOW_AMOUNT-D.DISC_AMOUNT AMOUNT
FROM ARTCSHDT D
INNER JOIN ARTCSHHD H ON H.SESSION_NO=D.SESSION_NO AND H.TRANS_NO=D.TRANS_NO
</PRE>

==Verify "CR" transactions againste change in receivables:==

===DataScope Method:===
All sessions are included. Use datascope to total the G and C and look for non zero amounts.
<PRE>
select 'G' SRC,SESSION_NO,TRX_DATE,TRX_AMOUNT AMOUNT from glttrx
where source_app='R' and source_trans_type IN ('CR','PR','CV')
and trx_date between '1/1/24' and '12/31/24'
UNION
SELECT 'C',D.SESSION_NO,H.POST_DATE,D.CREDIT_AMOUNT AMOUNT
FROM ARTCSHDT D
INNER JOIN ARTCSHHD H ON H.SESSION_NO=D.SESSION_NO AND H.TRANS_NO=D.TRANS_NO
where post_date between '1/1/24' and '12/31/24'
</PRE>

'''Data:''' Amount 
'''Row:''' Trx_Date,Session_no 
'''Column:''' SRC

Look for Grand Total column with a non-zero amount.

===Query Method:===
Finds only the mismatched items. Use modern IB that support derived (with) queries:
<pre>
with aaa (Src,SesNo,TrxDate,TrxAmt) as
(
select 'G' SRC,SESSION_NO,TRX_DATE,TRX_AMOUNT AMOUNT from glttrx
where source_app='R' and source_trans_type IN ('CR','PR','CV')
and trx_date between '7/1/24' and '12/31/24'
UNION
SELECT 'C',D.SESSION_NO,H.POST_DATE,D.CREDIT_AMOUNT AMOUNT
FROM ARTCSHDT D
INNER JOIN ARTCSHHD H ON H.SESSION_NO=D.SESSION_NO AND H.TRANS_NO=D.TRANS_NO
where post_date between '7/1/24' and '12/31/24'
)
select sesno,trxdate,sum(trxamt) Total from aaa
group by sesno,trxdate
having sum(trxamt)<>0
</pre>

DataScope: Match G/L Activity to Cash Posting Detail by Session / Date

2025-01-02T22:07:49Z

Chanson:

Diagnose Cash Posting vs. G/L postings:

==Verify "CC" transactions againste change in cash:==
<PRE>
select 'G' SRC,SESSION_NO,TRX_DATE,-TRX_AMOUNT AMOUNT from glttrx
where source_app='R' and source_trans_type='CC'
UNION
SELECT 'C',D.SESSION_NO,H.POST_DATE,D.CREDIT_AMOUNT-D.ALLOW_AMOUNT-D.DISC_AMOUNT AMOUNT
FROM ARTCSHDT D
INNER JOIN ARTCSHHD H ON H.SESSION_NO=D.SESSION_NO AND H.TRANS_NO=D.TRANS_NO
</PRE>

==Verify "CR" transactions againste change in receivables:==

DataScope Method:
All sessions are included. Use datascope to total the G and C and look for non zero amounts.
<PRE>
select 'G' SRC,SESSION_NO,TRX_DATE,TRX_AMOUNT AMOUNT from glttrx
where source_app='R' and source_trans_type IN ('CR','PR','CV')
and trx_date between '1/1/24' and '12/31/24'
UNION
SELECT 'C',D.SESSION_NO,H.POST_DATE,D.CREDIT_AMOUNT AMOUNT
FROM ARTCSHDT D
INNER JOIN ARTCSHHD H ON H.SESSION_NO=D.SESSION_NO AND H.TRANS_NO=D.TRANS_NO
where post_date between '1/1/24' and '12/31/24'
</PRE>

'''Data:''' Amount 
'''Row:''' Trx_Date,Session_no 
'''Column:''' SRC

Query Method:
Finds only the mismatched items. Use modern IB that support derived (with) queries:
<pre>
with aaa (Src,SesNo,TrxDate,TrxAmt) as
(
select 'G' SRC,SESSION_NO,TRX_DATE,TRX_AMOUNT AMOUNT from glttrx
where source_app='R' and source_trans_type IN ('CR','PR','CV')
and trx_date between '7/1/24' and '12/31/24'
UNION
SELECT 'C',D.SESSION_NO,H.POST_DATE,D.CREDIT_AMOUNT AMOUNT
FROM ARTCSHDT D
INNER JOIN ARTCSHHD H ON H.SESSION_NO=D.SESSION_NO AND H.TRANS_NO=D.TRANS_NO
where post_date between '7/1/24' and '12/31/24'
)
select sesno,trxdate,sum(trxamt) Total from aaa
group by sesno,trxdate
having sum(trxamt)<>0
</pre>

Look for Grand Total column with a non-zero amount.

DataScope: Match G/L Activity to Cash Posting Detail by Session / Date

2025-01-02T20:03:17Z

Chanson:

Diagnose Cash Posting vs. G/L postings:

Verify "CC" transactions againste change in cash:
<PRE>
select 'G' SRC,SESSION_NO,TRX_DATE,-TRX_AMOUNT AMOUNT from glttrx
where source_app='R' and source_trans_type='CC'
UNION
SELECT 'C',D.SESSION_NO,H.POST_DATE,D.CREDIT_AMOUNT-D.ALLOW_AMOUNT-D.DISC_AMOUNT AMOUNT
FROM ARTCSHDT D
INNER JOIN ARTCSHHD H ON H.SESSION_NO=D.SESSION_NO AND H.TRANS_NO=D.TRANS_NO
</PRE>

Verify "CR" transactions againste change in receivables:
<PRE>
select 'G' SRC,SESSION_NO,TRX_DATE,TRX_AMOUNT AMOUNT from glttrx
where source_app='R' and source_trans_type IN ('CR','PR','CV')
and trx_date between '1/1/24' and '12/31/24'
UNION
SELECT 'C',D.SESSION_NO,H.POST_DATE,D.CREDIT_AMOUNT AMOUNT
FROM ARTCSHDT D
INNER JOIN ARTCSHHD H ON H.SESSION_NO=D.SESSION_NO AND H.TRANS_NO=D.TRANS_NO
where post_date between '1/1/24' and '12/31/24'
</PRE>

'''Data:''' Amount 
'''Row:''' Trx_Date,Session_no 
'''Column:''' SRC

Look for Grand Total column with a non-zero amount.

Sitenotice

2024-09-03T12:29:25Z

Chanson:

Scale Setup

2024-08-27T19:40:32Z

Chanson: /* View Scale Output */

=Tools=

*HyperTerminal (for viewing and capturing scale output)
*[https://notepad-plus-plus.org/ Notepad++] (for decoding special control characters in scale output)

=Procedure=

===Setup Keystone Device Server===

On the PC connected to the scale, either Keystone must be installed (with the Device Server service running) or the Keystone Device Server must be manually installed (for example if the PC uses Remote Desktop to run Keystone.) If you need to manually install Keystone Device Server, use the following procedure:
#Create a folder in the root of the C: drive called "CCDeviceServer"
#Copy the CCDeviceServer.exe from a PC at the site that has Keystone installed (located in C:\Program Files (x86)\Keystone\Bin) into the C:\CCDeviceServer folder
#Run a Command Prompt as Administrator
#Type: C:\CCDeviceServer\CCDeviceServer.exe /install
#Start the "Keystone Device Server" service

===Configure Keystone Device Server===

#Open a web browser
#Navigate to "http://127.0.0.1:6021"
#Click "Configuration"
#Setup a unique Device Server ID (DS followed by plant number is usually a good choice)
#Enter the WAN IP address of the main server in the Aggregator IP Address field
#Click "Submit"
[[File:DeviceServerRemoteConfigA.PNG|480px]]

===Capture Scale Output===

#Using HyperTerminal, connect to the scale
#Once the scale is reading properly, select Transfer->Capture Text, select a filename to save the scale output and click Start
#Let the scale read for about 10 seconds (it is useful to have some weight on the scale if possible)
#Select Transfer->Capture Text->Stop
[[File:HyperTerminalScale.PNG]]

===View Scale Output===

#Open the file in Notepad++
#Select View->Show Symbol->Show All Characters (this will allow you to see any special characters in the scale output)
[[File:NotepadPlusPlusScale.PNG]]

===Determine End of Line and Record Length===

#On a complete scale data line, hit the END key and note the "Col" number in the status bar
#Add the number of end of line characters to the value from step #1 (for example, is you see CRLF you would add 2)
#This number is the RECL device parameter
#[Keystone 4.3.6+] If there are end of line characters after the cursor, these will become the EOLHEX device parameter (convert to hex)

===Determine Check Code===

#Look for one or more characters that are repeated on each line of the scale output (preferably before the weight)
#Note the starting position of the repeated characters, this will become the CHECKCOL device parameter
#Note the characters themselves, this will become the CHECKCODE or CHECKCODEHEX device parameter (if the check code contains special control codes, use the chart below to determine the hex values for the characters)
[[File:ASCIIControlCodes.png]]

===Determine Weight===

#Look for the weight (if the weight is zero or a low number, allow for at least 6 digits)
#Note the starting position of the weight, this will become the WEIGHTCOL device parameter
#Note the length of the weight (usually 6), this will become the WEIGHTLEN device parameter

===Configure Unit of Measure===

#The default U/M for scales is LB (pounds.)
#If the scale sends weight in a different U/M, set the UNITOFMEASURE device parameter to the U/M that the scale is sending.

===Determine Overcapacity Indicator===

(Keystone 4.3.9.5+)
#Consult the scale documentation to determine which character(s)/code(s) indicate an over-capacity condition
#Note the starting position of this indicator, this will become the OVERCAPCOL device parameter
#Note the characters themselves, this will become the OVERCAPCODE or OVERCAPCODEHEX device parameter (if the over-capacity code contains special control codes, use the chart in the "Determine Check Code" section to determine the hex values for the characters)

===Update Keystone Device Information===

#Open Device Maintenance
#Select or create the scale device
#Enter the scale parameters in the Device Configuration tab (sample below)
[[File:DeviceMaintenanceScale.PNG]]

===Start/Restart Services===

#On the main server, start or restart the Device Aggregator
#Wait a few seconds, then start or restart the Device Server
#On the PC connected to the scale, start or restart the Device Server

=Old outdated information=

Sample scale settings using classic device server (pre aggregator):
<PRE>
Start Device server on computer that will be connected to the scale:
Start/Run "c:\program files\GivenHansco\cc\bin\ccdevsv" /i /l
Login as ADMIN
Open Device Maint. off of the System Maint Menu
Create new device:
Device ID e.g.: SCALE01
Description: e.g. SMITHFIELD SCALE
type: SCALE
Dev Server ID: Leave Blank (Used for Device Server/Aggregator)
In Device Aggregator Box:
ip address -- if stand alone 127.0.0.1
port 6000

Copy and paste the sample device config settings

COMPort=1
BaudRate=9600
DataBits=8
StopBits=1
Parity=N
CheckCode=LB
CheckCol=9
WeightCol=2
WeightLen=6
RECL=17
UnitOfMeasure=LB

modify baud, databits, stopbits, parity to match what scale guy tells you.

Make sure scale is connected
F5 - test
Automatic
F2 TEST

hopefully it finds it
</PRE>

Scale Setup

2024-08-27T19:40:04Z

Chanson: /* Update Keystone Device Information */

=Tools=

*HyperTerminal (for viewing and capturing scale output)
*[https://notepad-plus-plus.org/ Notepad++] (for decoding special control characters in scale output)

=Procedure=

===Setup Keystone Device Server===

On the PC connected to the scale, either Keystone must be installed (with the Device Server service running) or the Keystone Device Server must be manually installed (for example if the PC uses Remote Desktop to run Keystone.) If you need to manually install Keystone Device Server, use the following procedure:
#Create a folder in the root of the C: drive called "CCDeviceServer"
#Copy the CCDeviceServer.exe from a PC at the site that has Keystone installed (located in C:\Program Files (x86)\Keystone\Bin) into the C:\CCDeviceServer folder
#Run a Command Prompt as Administrator
#Type: C:\CCDeviceServer\CCDeviceServer.exe /install
#Start the "Keystone Device Server" service

===Configure Keystone Device Server===

#Open a web browser
#Navigate to "http://127.0.0.1:6021"
#Click "Configuration"
#Setup a unique Device Server ID (DS followed by plant number is usually a good choice)
#Enter the WAN IP address of the main server in the Aggregator IP Address field
#Click "Submit"
[[File:DeviceServerRemoteConfigA.PNG|480px]]

===Capture Scale Output===

#Using HyperTerminal, connect to the scale
#Once the scale is reading properly, select Transfer->Capture Text, select a filename to save the scale output and click Start
#Let the scale read for about 10 seconds (it is useful to have some weight on the scale if possible)
#Select Transfer->Capture Text->Stop
[[File:HyperTerminalScale.PNG]]

===View Scale Output===

#Open the file in Notepad++
#Select View->Show Symbol->Show All Characters (this will allow you to see any special characters in the scale output)
[[File:NotepadPlusPlusScale.PNG|480px]]

===Determine End of Line and Record Length===

#On a complete scale data line, hit the END key and note the "Col" number in the status bar
#Add the number of end of line characters to the value from step #1 (for example, is you see CRLF you would add 2)
#This number is the RECL device parameter
#[Keystone 4.3.6+] If there are end of line characters after the cursor, these will become the EOLHEX device parameter (convert to hex)

===Determine Check Code===

#Look for one or more characters that are repeated on each line of the scale output (preferably before the weight)
#Note the starting position of the repeated characters, this will become the CHECKCOL device parameter
#Note the characters themselves, this will become the CHECKCODE or CHECKCODEHEX device parameter (if the check code contains special control codes, use the chart below to determine the hex values for the characters)
[[File:ASCIIControlCodes.png]]

===Determine Weight===

#Look for the weight (if the weight is zero or a low number, allow for at least 6 digits)
#Note the starting position of the weight, this will become the WEIGHTCOL device parameter
#Note the length of the weight (usually 6), this will become the WEIGHTLEN device parameter

===Configure Unit of Measure===

#The default U/M for scales is LB (pounds.)
#If the scale sends weight in a different U/M, set the UNITOFMEASURE device parameter to the U/M that the scale is sending.

===Determine Overcapacity Indicator===

(Keystone 4.3.9.5+)
#Consult the scale documentation to determine which character(s)/code(s) indicate an over-capacity condition
#Note the starting position of this indicator, this will become the OVERCAPCOL device parameter
#Note the characters themselves, this will become the OVERCAPCODE or OVERCAPCODEHEX device parameter (if the over-capacity code contains special control codes, use the chart in the "Determine Check Code" section to determine the hex values for the characters)

===Update Keystone Device Information===

#Open Device Maintenance
#Select or create the scale device
#Enter the scale parameters in the Device Configuration tab (sample below)
[[File:DeviceMaintenanceScale.PNG]]

===Start/Restart Services===

#On the main server, start or restart the Device Aggregator
#Wait a few seconds, then start or restart the Device Server
#On the PC connected to the scale, start or restart the Device Server

=Old outdated information=

Sample scale settings using classic device server (pre aggregator):
<PRE>
Start Device server on computer that will be connected to the scale:
Start/Run "c:\program files\GivenHansco\cc\bin\ccdevsv" /i /l
Login as ADMIN
Open Device Maint. off of the System Maint Menu
Create new device:
Device ID e.g.: SCALE01
Description: e.g. SMITHFIELD SCALE
type: SCALE
Dev Server ID: Leave Blank (Used for Device Server/Aggregator)
In Device Aggregator Box:
ip address -- if stand alone 127.0.0.1
port 6000

Copy and paste the sample device config settings

COMPort=1
BaudRate=9600
DataBits=8
StopBits=1
Parity=N
CheckCode=LB
CheckCol=9
WeightCol=2
WeightLen=6
RECL=17
UnitOfMeasure=LB

modify baud, databits, stopbits, parity to match what scale guy tells you.

Make sure scale is connected
F5 - test
Automatic
F2 TEST

hopefully it finds it
</PRE>

Scale Setup

2024-08-27T19:39:39Z

Chanson:

=Tools=

*HyperTerminal (for viewing and capturing scale output)
*[https://notepad-plus-plus.org/ Notepad++] (for decoding special control characters in scale output)

=Procedure=

===Setup Keystone Device Server===

On the PC connected to the scale, either Keystone must be installed (with the Device Server service running) or the Keystone Device Server must be manually installed (for example if the PC uses Remote Desktop to run Keystone.) If you need to manually install Keystone Device Server, use the following procedure:
#Create a folder in the root of the C: drive called "CCDeviceServer"
#Copy the CCDeviceServer.exe from a PC at the site that has Keystone installed (located in C:\Program Files (x86)\Keystone\Bin) into the C:\CCDeviceServer folder
#Run a Command Prompt as Administrator
#Type: C:\CCDeviceServer\CCDeviceServer.exe /install
#Start the "Keystone Device Server" service

===Configure Keystone Device Server===

#Open a web browser
#Navigate to "http://127.0.0.1:6021"
#Click "Configuration"
#Setup a unique Device Server ID (DS followed by plant number is usually a good choice)
#Enter the WAN IP address of the main server in the Aggregator IP Address field
#Click "Submit"
[[File:DeviceServerRemoteConfigA.PNG|480px]]

===Capture Scale Output===

#Using HyperTerminal, connect to the scale
#Once the scale is reading properly, select Transfer->Capture Text, select a filename to save the scale output and click Start
#Let the scale read for about 10 seconds (it is useful to have some weight on the scale if possible)
#Select Transfer->Capture Text->Stop
[[File:HyperTerminalScale.PNG]]

===View Scale Output===

#Open the file in Notepad++
#Select View->Show Symbol->Show All Characters (this will allow you to see any special characters in the scale output)
[[File:NotepadPlusPlusScale.PNG|480px]]

===Determine End of Line and Record Length===

#On a complete scale data line, hit the END key and note the "Col" number in the status bar
#Add the number of end of line characters to the value from step #1 (for example, is you see CRLF you would add 2)
#This number is the RECL device parameter
#[Keystone 4.3.6+] If there are end of line characters after the cursor, these will become the EOLHEX device parameter (convert to hex)

===Determine Check Code===

#Look for one or more characters that are repeated on each line of the scale output (preferably before the weight)
#Note the starting position of the repeated characters, this will become the CHECKCOL device parameter
#Note the characters themselves, this will become the CHECKCODE or CHECKCODEHEX device parameter (if the check code contains special control codes, use the chart below to determine the hex values for the characters)
[[File:ASCIIControlCodes.png]]

===Determine Weight===

#Look for the weight (if the weight is zero or a low number, allow for at least 6 digits)
#Note the starting position of the weight, this will become the WEIGHTCOL device parameter
#Note the length of the weight (usually 6), this will become the WEIGHTLEN device parameter

===Configure Unit of Measure===

#The default U/M for scales is LB (pounds.)
#If the scale sends weight in a different U/M, set the UNITOFMEASURE device parameter to the U/M that the scale is sending.

===Determine Overcapacity Indicator===

(Keystone 4.3.9.5+)
#Consult the scale documentation to determine which character(s)/code(s) indicate an over-capacity condition
#Note the starting position of this indicator, this will become the OVERCAPCOL device parameter
#Note the characters themselves, this will become the OVERCAPCODE or OVERCAPCODEHEX device parameter (if the over-capacity code contains special control codes, use the chart in the "Determine Check Code" section to determine the hex values for the characters)

===Update Keystone Device Information===

#Open Device Maintenance
#Select or create the scale device
#Enter the scale parameters in the Device Configuration tab (sample below)
[[File:DeviceMaintenanceScale.PNG|480px]]

===Start/Restart Services===

#On the main server, start or restart the Device Aggregator
#Wait a few seconds, then start or restart the Device Server
#On the PC connected to the scale, start or restart the Device Server

=Old outdated information=

Sample scale settings using classic device server (pre aggregator):
<PRE>
Start Device server on computer that will be connected to the scale:
Start/Run "c:\program files\GivenHansco\cc\bin\ccdevsv" /i /l
Login as ADMIN
Open Device Maint. off of the System Maint Menu
Create new device:
Device ID e.g.: SCALE01
Description: e.g. SMITHFIELD SCALE
type: SCALE
Dev Server ID: Leave Blank (Used for Device Server/Aggregator)
In Device Aggregator Box:
ip address -- if stand alone 127.0.0.1
port 6000

Copy and paste the sample device config settings

COMPort=1
BaudRate=9600
DataBits=8
StopBits=1
Parity=N
CheckCode=LB
CheckCol=9
WeightCol=2
WeightLen=6
RECL=17
UnitOfMeasure=LB

modify baud, databits, stopbits, parity to match what scale guy tells you.

Make sure scale is connected
F5 - test
Automatic
F2 TEST

hopefully it finds it
</PRE>

Scale Setup

2024-08-27T19:33:36Z

Chanson:

=Tools=

*HyperTerminal (for viewing and capturing scale output)
*[https://notepad-plus-plus.org/ Notepad++] (for decoding special control characters in scale output)

=Procedure=

===Setup Keystone Device Server===

On the PC connected to the scale, either Keystone must be installed (with the Device Server service running) or the Keystone Device Server must be manually installed (for example if the PC uses Remote Desktop to run Keystone.) If you need to manually install Keystone Device Server, use the following procedure:
#Create a folder in the root of the C: drive called "CCDeviceServer"
#Copy the CCDeviceServer.exe from a PC at the site that has Keystone installed (located in C:\Program Files (x86)\Keystone\Bin) into the C:\CCDeviceServer folder
#Run a Command Prompt as Administrator
#Type: C:\CCDeviceServer\CCDeviceServer.exe /install
#Start the "Keystone Device Server" service

===Configure Keystone Device Server===

#Open a web browser
#Navigate to "http://127.0.0.1:6021"
#Click "Configuration"
#Setup a unique Device Server ID (DS followed by plant number is usually a good choice)
#Enter the WAN IP address of the main server in the Aggregator IP Address field
#Click "Submit"
[[File:DeviceServerRemoteConfigA.PNG|480px]]

===Capture Scale Output===

#Using HyperTerminal, connect to the scale
#Once the scale is reading properly, select Transfer->Capture Text, select a filename to save the scale output and click Start
#Let the scale read for about 10 seconds (it is useful to have some weight on the scale if possible)
#Select Transfer->Capture Text->Stop
[[File:HyperTerminalScale.PNG|480px]]

===View Scale Output===

#Open the file in Notepad++
#Select View->Show Symbol->Show All Characters (this will allow you to see any special characters in the scale output)
[[File:NotepadPlusPlusScale.PNG|480px]]

===Determine End of Line and Record Length===

#On a complete scale data line, hit the END key and note the "Col" number in the status bar
#Add the number of end of line characters to the value from step #1 (for example, is you see CRLF you would add 2)
#This number is the RECL device parameter
#[Keystone 4.3.6+] If there are end of line characters after the cursor, these will become the EOLHEX device parameter (convert to hex)

===Determine Check Code===

#Look for one or more characters that are repeated on each line of the scale output (preferably before the weight)
#Note the starting position of the repeated characters, this will become the CHECKCOL device parameter
#Note the characters themselves, this will become the CHECKCODE or CHECKCODEHEX device parameter (if the check code contains special control codes, use the chart below to determine the hex values for the characters)
[[File:ASCIIControlCodes.png|720px]]

===Determine Weight===

#Look for the weight (if the weight is zero or a low number, allow for at least 6 digits)
#Note the starting position of the weight, this will become the WEIGHTCOL device parameter
#Note the length of the weight (usually 6), this will become the WEIGHTLEN device parameter

===Configure Unit of Measure===

#The default U/M for scales is LB (pounds.)
#If the scale sends weight in a different U/M, set the UNITOFMEASURE device parameter to the U/M that the scale is sending.

===Determine Overcapacity Indicator===

(Keystone 4.3.9.5+)
#Consult the scale documentation to determine which character(s)/code(s) indicate an over-capacity condition
#Note the starting position of this indicator, this will become the OVERCAPCOL device parameter
#Note the characters themselves, this will become the OVERCAPCODE or OVERCAPCODEHEX device parameter (if the over-capacity code contains special control codes, use the chart in the "Determine Check Code" section to determine the hex values for the characters)

===Update Keystone Device Information===

#Open Device Maintenance
#Select or create the scale device
#Enter the scale parameters in the Device Configuration tab (sample below)
[[File:DeviceMaintenanceScale.PNG|480px]]

===Start/Restart Services===

#On the main server, start or restart the Device Aggregator
#Wait a few seconds, then start or restart the Device Server
#On the PC connected to the scale, start or restart the Device Server

=Old outdated information=

Sample scale settings using classic device server (pre aggregator):
<PRE>
Start Device server on computer that will be connected to the scale:
Start/Run "c:\program files\GivenHansco\cc\bin\ccdevsv" /i /l
Login as ADMIN
Open Device Maint. off of the System Maint Menu
Create new device:
Device ID e.g.: SCALE01
Description: e.g. SMITHFIELD SCALE
type: SCALE
Dev Server ID: Leave Blank (Used for Device Server/Aggregator)
In Device Aggregator Box:
ip address -- if stand alone 127.0.0.1
port 6000

Copy and paste the sample device config settings

COMPort=1
BaudRate=9600
DataBits=8
StopBits=1
Parity=N
CheckCode=LB
CheckCol=9
WeightCol=2
WeightLen=6
RECL=17
UnitOfMeasure=LB

modify baud, databits, stopbits, parity to match what scale guy tells you.

Make sure scale is connected
F5 - test
Automatic
F2 TEST

hopefully it finds it
</PRE>

File:DeviceServerRemoteConfigA.PNG

2024-08-27T19:33:24Z

Chanson: File uploaded with MsUpload

File uploaded with MsUpload

QueryGen Case: Add missing item to all Jobs

2024-03-27T15:34:36Z

Chanson:

Sometimes you want to add an detail item to all jobs.

In this case, we will add the item ENVIRO.01 (adjusted to plant) to all jobs wher it is missing. In the query below we select all jobs and identify which do NOT have a product like ENVIRO.
Artjob as d1 represents the detail line that should exist if ENVIRO.XX is in the job detail.
Artjob as d0 is used to find a valid plant number on a product. (you can't count on the job header).

<pre>
select h.session_no,h.trans_no,h.cust_no,c.name, h.job_po_loc, h.start_date, h.stop_date,coalesce(min(p0.plant_no),'1A') Plant,h.active_flag,h.job_tax_code from artjobhd h
inner join artcust c on c.cust_no=h.cust_no
left outer join artjobdt d0 ON d0.session_no=h.session_no and d0.trans_no=h.trans_no
left outer join artprod p0 on p0.product_code=d0.product_code
left outer join artjobdt d1 ON d1.session_no=h.session_no and d1.trans_no=h.trans_no AND D1.PRODUCT_CODE LIKE 'ENVIRO.%'
where (d1.product_code is null)
and h.active_flag='Y' AND C.ACTIVE_FLAG='Y'
group by h.session_no,h.trans_no, h.cust_no,c.name, h.job_po_loc, h.start_date, h.stop_date,h.active_flag,h.job_tax_code
</pre>

Adjust this query to your case:
*Change 'ENVIRO.%' to the product you wish to work with.
*Change the job selection to your situation. (START_DATE, STOP_DATE etc.)

Then use the following QG model Update Query:
<pre>
INSERT INTO
artjobdt
( SESSION_NO, TRANS_NO, CUST_NO, JOB_PO_LOC, LINE_NO, PRICE_CODE, PRODUCT_CODE, UNIT_PRICE, QTY_ORDERED, TAX_CODE, QTY_PER_LOAD, UNIT_OF_MEASURE, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER ) VALUES
( {SESSION_NO}, {TRANS_NO}, '{CUST_NO}', '{JOB_PO_LOC}', 99, 'P', 'ENVIRO.{PLANT}', 55, 0, '{JOB_TAX_CODE}', 0, '/L', 'ENVIRONMENTAL FEE PER LOAD', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT' );
</pre>

Adjust the model update query:
*Line No: 99 must not exist. If multiptle products being processed change for each product.
*Price: Here it's 55. NOTE: you can come back and fix the price later.
*Product Code: Change ENVIRO to yoru product
*Unit of Measure: Match Product
*Desription: Match Product

QueryGen Case: Add missing item to all Jobs

2024-03-27T15:34:09Z

Chanson: Created page with "Sometimes you want to add an detail item to all jobs. In this case, we will add the item ENVIRO.01 (adjusted to plant) to all jobs wher it is missing. In the query below we select all jobs and identify which do NOT have a product like ENVIRO. Artjob as d1 represents the detail line that should exist if ENVIRO.XX is in the job detail. Artjob as d0 is used to find a valid plant number on a product. (you can't count on the job header). <pre> select h.session_no,h.trans_..."

Sometimes you want to add an detail item to all jobs.

In this case, we will add the item ENVIRO.01 (adjusted to plant) to all jobs wher it is missing. In the query below we select all jobs and identify which do NOT have a product like ENVIRO.
Artjob as d1 represents the detail line that should exist if ENVIRO.XX is in the job detail.
Artjob as d0 is used to find a valid plant number on a product. (you can't count on the job header).

<pre>
select h.session_no,h.trans_no,h.cust_no,c.name, h.job_po_loc, h.start_date, h.stop_date,coalesce(min(p0.plant_no),'1A') Plant,h.active_flag,h.job_tax_code from artjobhd h
inner join artcust c on c.cust_no=h.cust_no
left outer join artjobdt d0 ON d0.session_no=h.session_no and d0.trans_no=h.trans_no
left outer join artprod p0 on p0.product_code=d0.product_code
left outer join artjobdt d1 ON d1.session_no=h.session_no and d1.trans_no=h.trans_no AND D1.PRODUCT_CODE LIKE 'ENVIRO.%'
where (d1.product_code is null)
and h.active_flag='Y' AND C.ACTIVE_FLAG='Y'
group by h.session_no,h.trans_no, h.cust_no,c.name, h.job_po_loc, h.start_date, h.stop_date,h.active_flag,h.job_tax_code
</pre>

Adjust this query to your case:
*Change 'ENVIRO.%' to the product you wish to work with.
*Change the job selection to your situation. (START_DATE, STOP_DATE etc.)

Then use the following QG model Update Query:
<pre>
INSERT INTO
artjobdt
( SESSION_NO, TRANS_NO, CUST_NO, JOB_PO_LOC, LINE_NO, PRICE_CODE, PRODUCT_CODE, UNIT_PRICE, QTY_ORDERED, TAX_CODE, QTY_PER_LOAD, UNIT_OF_MEASURE, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER ) VALUES
( {SESSION_NO}, {TRANS_NO}, '{CUST_NO}', '{JOB_PO_LOC}', 99, 'P', 'ENVIRO.{PLANT}', 55, 0, '{JOB_TAX_CODE}', 0, '/L', 'ENVIRONMENTAL FEE PER LOAD', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT' );
<pre>

Adjust the model update query:
*Line No: 99 must not exist. If multiptle products being processed change for each product.
*Price: Here it's 55. NOTE: you can come back and fix the price later.
*Product Code: Change ENVIRO to yoru product
*Unit of Measure: Match Product
*Desription: Match Product
</pre>

QueryGen

2024-03-27T15:24:40Z

Chanson: /* QueryGen Cases */

[[CATEGORY: QueryGen]]

==QueryGen Overview==
QueryGen is intended to create queries for the inserting and updating of databases. It is really a text generator that repeats for every record of a dataset replacing text from a template with data values. It can be used for much more than just creating queries - such as lists, .csv formatted text etc.

When Creating data for Keystone, make sure to review [[Keystone Data Manipulation Guidelines]] for creating and updating data by query.

===The QueryGen Screen===
*'''DB Name''' Database Name/ Connection Information used for connection.
*'''DB Kind''' Selection of database type (Interbase, FireBird, ODBC, MSSQL, SQLite, Access, Excel)
*'''Go''' Connects and runs Query. If active connection, the connection is reset.
*'''Qry''' Run query without reconnecting. (This will include uncommitted data others can't see and exclude changes from other users since the connection was established).
*'''X''' Disonnects current connection. Useful for unlocking a data source to make changes.
*'''CON''' Connect Only

*'''Selection Query''' Query to select the source data.
The following 3 items create the model for the Result query:
*'''Header''' This query is the first item in the Result query. It only gets produced once.
*'''Update Query''' This is the primary model query that is repeated for each record. See expanded explanation of the query creation process.
*'''Closing Query''' This query is the Last item in the Result query. It only gets produced once.
*'''Repeat Every''' Used for a statement you only want ever x# of lines. Good to specify '''COMMIT''' statement every so often on a monster query.

*'''Results''' The resulting query that joins together the source data and the three model queries.

==Connection Options==

===Interbase / Firebird===
Querygen is first and foremost a good tool to pull data from Interbase:

[[File:QG_IB_Open.PNG]]

DB Kind: '''Interbase''' or '''Firebird'''

*For Local File enter the path and file name of the database (e.g.: c:\program files\givenhansco\cc\cust\data\ccw001.ib)
*For Remote Files, enter the ipaddress (or computer name) followed by a colon, then the path and file name of the database (e.g. 192.168.0.1:c:\ccgh\data\ccw001.ib)

Query:
*Create a basic query selecting all necessary fields.

===Microsoft Access===
:DB Kind: '''Access'''
:Source DB: Filename of the file

Query:
*Select from the table within your access database. (Select * from customers)

===Microsoft Excel===
Starting with QG2016 this is a simplified way to get to excel files. It uses ODBC but allows you to just enter the file name and not setup an ODBC connection.
:DB Kind: '''Excel'''
:Source DB: Filename of the file

For OFFICE 365 A Special Driver is Required: [https://www.microsoft.com/en-us/download/details.aspx?id=54920 Microsoft Office ODBC Driver] This seems to work best with 64 bit querygen.

[[File:QG Excel Open.PNG]]

Query:
*Select from named range or sheet.
**Select * from [Sheet1$]
**Select * from MyTable
See Excel help on naming a range - this feature can be found on the Formula tab.

===ODBC===

====Example ODBC====

#Setup ODBC data Source (Administrtive Tools>Data Sources (ODBC). Each driver/connection type has variations
[[Image:Qg odbc1.jpg]]
#Enter the ODBC name as the Source Path
#Select from the desired table
[[Image:Qg openODBCa.jpg]]

====Excel with ODBC Alias====
Excel Spreadsheet. (NOTE: QG2016 eliminates the need of setting up an ODBC connection for each spreadsheet. See "Excel" section above)
#Set up ODBC datasource using Excel driver. The spreadsheet dearborn.xls is named the the datasource dearborn1
#The data to be queried must be a Named Range in excel.
##Open the spreadsheet
##Highlight the range the data is in
##Select Insert>Name>Define from the menu and name the range (in this example called “test”)
##Multiple names can be Named within a spreadsheet.
##The Source Path is the ODBC name
#The Source Query references the Named Range in the spreadsheet

Sample query that selects Sheet 2
<PRE>
select * from [sheet2$]
</PRE>

====Text File with ODBC====
'''Note:''' While importing Text via ODBC can be convenient, it also can be very unpredictable. Automated handling of fields can result in unexpected changes to source data.

This has the advantage of selecting the flat text file from the Source Query line (no need to set up a separate ODBC data source for each file). The directory of the text files is specified in the ODBC Data source. The File deliniation is set in the ODBC data source. The default is csv as in this example. It can be changed to Tab Delimited. If changed to Fixed, you need to define all of the columns in the ODBC data source.
#Set up ODBC data source designating the directory or the text files are to be put.
[[Image:QG ODBC text.jpg]]
#The Source Path is the ODBC name (e.g. TEXT)
#The Source Query references the text file in the designated directory.
[[Image:QG OpenText.jpg]]

===LOCAL (Query other queries)===
(starting Sept. 2016)

LOCAL allows you to query up to 4 separate query results which each can come from a different data source. These 4 queries are setup on the tabs LOC1 to LOC4. Each LOC tab has the same capability to connect to different data sources such as an Interbase, EXCEL or ODBC. Each local query is referred to as LQ1 through LQ4.

Examples of what you can do with "LOCAL":
*Compare values in 2 databases - to create inserts where data is missing from a target
*Compare values in 2 databases - to create updates where there are differences
*Combine data from a live database and an external Excel Sheet
*Compare data from 2 different systems (e.g. GPS & Dispatch)

====Example: Query GPS and Dispatch databases to match up trucks====

'''Set LOC1:'''
Set DB Source to '''''Fleet'''''

Connection type to '''''ODBC'''''

Query: <PRE>Select * FROM fleet.dbo.Trucks</PRE>

'''Set LOC2:'''
Normal IB connection:
Query: <PRE>SELECT * FROM arttruck</PRE>

'''Set Standard:'''
Set DB kind to '''''Local'''''

'''Sample queries:'''
<PRE>
select * from LQ1
</PRE>
<PRE>
SELECT * FROM LQ2
</PRE>

'''See all fields from both tables where trucks exist in both databases (and truck active in dispatch)'''
<PRE>
Select LQ1.*,LQ2.* FROM LQ2
INNER JOIN LQ1 ON LQ1.TRUCK=LQ2.TRUCK_NO
WHERE LQ2.ACTIVE_FLAG='Y'
</PRE>

'''List all trucks in dispatch (LQ2) that are not in GPS (LQ1)'''
<PRE>
Select LQ2.* FROM LQ2
WHERE NOT EXISTS
(SELECT * FROM LQ1 WHERE LQ1.TRUCK=LQ2.TRUCK_NO)
AND LQ2.ACTIVE_FLAG='Y'
</PRE>

===Azure SQL===

Azure SQL appears almost the same as standard MSSQL database. This example uses the Microsoft SQL Native driver. Login to Azure and to open the firewall for the client IP address and to derive the required connection values.

<PRE>
DriverID=MSSQL
Server=<Azure Server ID> (this will start with "tcp:")
Database=<azure database name>
User_Name=<user name>
Password=<Password>
Encrypt=Yes
MetaDefSchema=dbo
MetaDefCatalog=<Catalog Name> (likely the same as database name)
</PRE>

== Formatting Options ==
*'''STRLEN''' - specifies the maximum number of characters to use from the import field. In the example below, it will select no more than two characters from the A1 field. <CODE>UPDATE ARTPROD SET PRODUCT_CLASS='{A1|STRLEN=2}'</CODE>
*'''TRIM''' - (QG2018Nov) Remove blanks before and after the field. <CODE>UPDATE ARTPROD SET PRODUCT_CLASS='{A1|TRIM=0}'</CODE>
*'''ADD''' - Adds a specified value to the field. In the example below, it sets TRANS_NO to a value imported from T_NO incremented by 1000. <CODE>UPDATE ARTINV SET TRANS_NO='{T_NO|ADD=1000}'</CODE>
*'''DATETIME''' - specifies that the data should be in Interbase's DATETIME format. This is a necessity for any time-stamp fields. In the example below, it will set CHANGED field into DATETIME format for the field in ARTCUST. <CODE>UPDATE ARTCUST SET LAST_CHANGE_DATE='{CHANGED|DATETIME=0}'</CODE>
*DECODEDATE - Decode some odd date formats.
*'''DEFAULT''' - sets a default value if none is available from the import. In the example below, it will take the credit terms from the TERMS field in the import, and if none is specified, set it to a default value of 30. <CODE>UPDATE ARTCUST SET CREDIT_TERMS='{TERMS|DEFAULT=30}'</CODE>
*'''FORMATINT''' - Format an integer using the Delphi Format command. See: [http://www.delphibasics.co.uk/RTL.asp?Name=Format Delphi Format Command (delphibasics.com)]
Example - the format command %.5d means left fill the number with 0s. In the case below the text value 1 appears right before the number. If you had a trans_no of 243 it would be formatted as 00243. With the 1 appearing right before you would get 100243. This is useful if you want to add transactions to an existing session based on data already in that session. <CODE>{SESSION_NO},1{TRANS_NO|FORMATINT=%.5d}</CODE>
*'''FORMATFLT''' - Format a floating point number using the Format command. See: [http://www.delphibasics.co.uk/RTL.asp?Name=FormatFloat Delphi Format Command (delphibasics.com)]
Essentially for any non-integer you can apply formatting.
*'''NULL''' - specifies to replace the value with NULL if the value of the imported field matches what is specified. In the eaxmple below, if the GL field contains a 0, it will instead set it to null in the COGS_GL field. <CODE>UPDATE ARTPROD SET COGS_GL='{GL|NULL=0}'</CODE>
*'''INC''' - (QG2014) An automatically Incrementing Value that starts at 1 and increase by 1. Useful for transaction numbers. <CODE>Insert Into ... '{A1|INC=0}'</CODE>

==Incrementors and Generators==

Incrementors: QG has incrementors that create incremental values in the generated query. The INC function, the COUNTER function and INCA through INCZ can all be used in the model query. These will create integer values in the query. There is no dependency on the database being targeted.

Generators: Databases such as Interbase have Generators that create incremental values at the time the query is run. These should be used when the queries are creating data that needs to fall in line with existing sequence values in the database. (Think "Session No." and "Seq. No")

===Incrementors===

The QG INC syntax is as follows ''{FIELD_NAME|INC=1}'' The field name is required and must match an existing field from the input data but it does nothing. The "=1" indicates to increase the incementor value by 1. You can also use "INC=0" to repeat a value. To Reset the value of an incrementor to 1 use "INC=R"

The values "INCA" through "INCZ" work exactly the same.

In this simplified example these values in the Update (model) query:
<PRE>
{TRANS_NO|INC=1},{LINE_NO|INCA=R},
{TRANS_NO|INC=0},{LINE_NO|INCA=1},
{TRANS_NO|INC=0},{LINE_NO|INCA=1},
</PRE>

result an output like:
<PRE>
1,1,
1,2,
1,3,

2,1,
2,2,
2,3,

3,1,
3,2,
3,3,
</PRE>

===Generators===
Generators are database dependent. In Interbase the Generator syntax looks like ''GEN_ID(GEN_CCSESSION,1)'' where "GEN_CCSESION" references a specific generator in the system and the value 1 reference the value to increment the generator by.

When each record will use a new generated value - simply put the generator in the Update (Model) Query:
<PRE>
INSERT INTO CCTCONT
( RECKEY, ENTITY_TYPE, ENTITY_ID, TITLE, NAME ... LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( GEN_ID(GEN_CCCONTKEY,1), '{ENTITY_TYPE}', '{ENTITY_ID}', '{TITLE}', '{NAME}' ... 'HIT', '{ACTIVE_FLAG}' );
</PRE>

====Reusing the same Generator Value====
When you need to use the same Generator Value for each transaction you don't want the generator creating a new value, so you can't use the above method.

In some cases you can get the last value of a generator by calling GEN_ID with the Generator increment set to 0, e.g. ''GEN_ID(GEN_CCSESSION,0)''. This is only safe when there is no possibility of the generator being updated from another source during the update process (e.g. you are working on a system stand-alone).

A safer option to store the value in a temporary table. In Keystone it's a best practice to use the table '''TMP_SNO'''. If this doesn't exist, create it with: <CODE>CREATE TABLE TMP_SNO(SNO INTEGER);</CODE>

If the table exists you need to clear it: <CODE>DELETE FROM TMP_SNO;</CODE>

In theory you could just store the session no: <CODE>INSERT INTO X1 (XVAL) VALUES (GEN_ID(GEN_CCSESSION,1));</CODE>. This method doesn't leave any info in the cctsessn table.

To create one session Number and record an entry in CCTSESSN enter the following for the "Header Query."
<PRE>INSERT INTO TMP_SNO (SNO) SELECT session_no FROM CCP_SESSION_BEGIN('ADMIN', '<MY SCRIPT>', '127.0.0.1', '3.3.3.9');</PRE>
(This procedure was created in Keystone 3.0)



Once you've created this value in TMP_SNO, you can use <CODE>(SELECT SNO FROM TMP_SNO)</CODE> for the value in the Update (model) query. See examples below...

===Sample of Putting together Generators and Incrementors===
In a situation where you are inserting Master and Detail records, and you are dealing with line numbers a mix of Incrementors and Generators can be used.

For example, creating a PRTNETPAYHST record and several PRTEMPDEFHST records. Assume the following spreadsheet is being used as source data. For each record in the sheet we will want to create one PRTNETPAYHST record and a separate PRTEMPDEFHST record for each of the data columns. There should be one session number for all of the records. There should be one transaction number for each input record that ties the single PRTNETPAYHST record to the multiple PRTEMPDEFHST records and there should be line numbers starting at one for the PRTEMPDEFHST records - these should restart each time there is a new input record.
[[File:QG_Example_Excel_Payimp.PNG]]

For all of the records we want to create we want 1 session Number. Create the following for the "Header Query."

<PRE>CREATE TABLE TMP_SNO(SNO INTEGER);
INSERT INTO TMP_SNO (SNO) SELECT session_no FROM CCP_SESSION_BEGIN('ADMIN', 'Insert Emp Balances', '127.0.0.1', '3.3.3');</PRE>

The Header query will process just once no matter how many input records there are.

Here's a sample of the Update (Model) query for both PRTNETPAYHST and multiple PRTEMPDEFHST.
<div class="toccolours mw-collapsible">
<PRE>
/* Employee {EMPLOYEE} */
INSERT INTO prtnetpayhst (SESSION_NO, TRANS_NO, RUN_NO, EMP_ID, CHECK_SEQ, CHECK_DATE, CHECK_NO, WEEK_END_DATE, NET_PAY, PAYFREQ, DED_WEEK, DED_PERIODS, TAX_PERIODS, REC_TYPE, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER ) VALUES (
(SELECT SNO FROM TMP_SNO), {EMP_ID|INC=1}, NULL, '{EMP_ID}', 1, '{TRX_DATE}', NULL, NULL, '0', NULL, NULL, NULL, NULL, 'A', 'N', CAST('TODAY' AS TIMESTAMP), 'HIT' );
INSERT INTO prtempdefhst (SESSION_NO, TRANS_NO, LINE_NO, EMP_ID, DEF_ID, DB_GL_ACCOUNT, CR_GL_ACCOUNT, TRX_DATE, TRX_TYPE, DEF_VALUE, OVERRIDE_FLAG, RUN_NO) VALUES (
(SELECT SNO FROM TMP_SNO), {EMP_ID|INC=0}, {EMP_ID|INCA=R}, '{EMP_ID}', 'FEDGRS', NULL, NULL, '{TRX_DATE}', 'S', '{FEDGRS}', 'Y', NULL );
INSERT INTO prtempdefhst (SESSION_NO, TRANS_NO, LINE_NO, EMP_ID, DEF_ID, DB_GL_ACCOUNT, CR_GL_ACCOUNT, TRX_DATE, TRX_TYPE, DEF_VALUE, OVERRIDE_FLAG, RUN_NO) VALUES (
(SELECT SNO FROM TMP_SNO), {EMP_ID|INC=0}, {EMP_ID|INCA=1}, '{EMP_ID}', 'FEDTAX', NULL, NULL, '{TRX_DATE}', 'S', '{FEDTAX}', 'Y', NULL );
INSERT INTO prtempdefhst (SESSION_NO, TRANS_NO, LINE_NO, EMP_ID, DEF_ID, DB_GL_ACCOUNT, CR_GL_ACCOUNT, TRX_DATE, TRX_TYPE, DEF_VALUE, OVERRIDE_FLAG, RUN_NO) VALUES (
(SELECT SNO FROM TMP_SNO), {EMP_ID|INC=0}, {EMP_ID|INCA=1}, '{EMP_ID}', 'SSGROSS', NULL, NULL, '{TRX_DATE}', 'S', '{SS_GROSS}', 'Y', NULL );
INSERT INTO prtempdefhst (SESSION_NO, TRANS_NO, LINE_NO, EMP_ID, DEF_ID, DB_GL_ACCOUNT, CR_GL_ACCOUNT, TRX_DATE, TRX_TYPE, DEF_VALUE, OVERRIDE_FLAG, RUN_NO) VALUES (
(SELECT SNO FROM TMP_SNO), {EMP_ID|INC=0}, {EMP_ID|INCA=1}, '{EMP_ID}', 'SSTAX', NULL, NULL, '{TRX_DATE}', 'S', '{SS_TAX}', 'Y', NULL );
INSERT INTO prtempdefhst (SESSION_NO, TRANS_NO, LINE_NO, EMP_ID, DEF_ID, DB_GL_ACCOUNT, CR_GL_ACCOUNT, TRX_DATE, TRX_TYPE, DEF_VALUE, OVERRIDE_FLAG, RUN_NO) VALUES (
(SELECT SNO FROM TMP_SNO), {EMP_ID|INC=0}, {EMP_ID|INCA=1}, '{EMP_ID}', 'MCGROSS', NULL, NULL, '{TRX_DATE}', 'S', '{MC_GROSS}', 'Y', NULL );
INSERT INTO prtempdefhst (SESSION_NO, TRANS_NO, LINE_NO, EMP_ID, DEF_ID, DB_GL_ACCOUNT, CR_GL_ACCOUNT, TRX_DATE, TRX_TYPE, DEF_VALUE, OVERRIDE_FLAG, RUN_NO) VALUES (
(SELECT SNO FROM TMP_SNO), {EMP_ID|INC=0}, {EMP_ID|INCA=1}, '{EMP_ID}', 'MCTAX', NULL, NULL, '{TRX_DATE}', 'S', '{MC_TAX}', 'Y', NULL );
</PRE>
</DIV>

Closing query:
<PRE>
UPDATE CCTSESSN SET STATUS_FLAG='I', STOP_DATETIME=CAST('NOW' AS TIMESTAMP) WHERE SESSION_NO=(SELECT SNO FROM TMP_SNO);
COMMIT; DROP TABLE TMP_SNO;
</PRE>

*Using ''Select SNO from TMP_SNO'' sets every record to use the one session_no created by the Header Query.
*Alternatively we could have used''GEN_ID(GEN_CCSESSION,0)'' for the session number so long as nothing else will impact the generator value during processing.
*Using {EMP_ID|INC=1} creates a new transaction number for the PRTNETPAYHST transaction - this happens once per input record. Because each following transaction uses {EMP_ID|INC=0} the repeat the value.
*Using {EMP_ID|INCA=R} for the LINE_NO value for the first PRTEMPDEFHST line forces the value if the incrementor INCA to 1. Using {EMP_ID|INCA=1} sets it to 2 through 6 for the next 5 lines. The pattern of 1 through 6 recurs for each input record.

==Conditional Inserts (Merging Data)==

Sometimes you want to insert data only if it doesn't exist, such as when merging 2 sets of g/l accounts where some overlap exists. Since the Interbase INSERT statement has no where clause, we can accomplish this by creating our insert statement using special select statement.

In the example below, we use the artloc table (where location=0) because we can count on it returning the values just once, and add the additional conditions appropriate for the situation. If the condition is not bet then there will be no records returned from the select query therefor an insert will not be attempted.

Example of a condition insert into A/R Allowance class:
<PRE>
INSERT INTO ARTALCLS
( CLASS_NO, DESCRIPTION, ALLOW_GL, ALLOW_INCLUDES_TAX, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG)
SELECT 'B', 'Bad Debt', '6290.1.00', 'P', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'
from artloc where loc_id=0 and not EXISTS (SELECT 'AAA' FROM ARTALCLS WHERE CLASS_NO= 'B' );
</PRE>
If a record with CLASS_NO = 'B' exists then no insert is attempted.

===Using QG Query Builder to create a merge query===

In QG 2016 the Query Builder can be used to simplify the process of creating Interbase Conditional Insert statements. In this example we will create a merge insert for Customer Classes.

First Create your simple select:
[[File:QG_Example_Merge_1.PNG]]

Click Build Query
[[File:QG_Example_Merge_2.PNG]]

Set the Update (Model) Insert Template to:
<PRE>
INSERT INTO ~TableName~
( ~FieldLabels~)
SELECT
~FieldValues~
from artloc where loc_id=0 and not EXISTS (SELECT 'A' FROM ~TableName~ WHERE ID= '{ID}' );
</PRE>

[[File:QG_Example_Merge_3.PNG]]

Additionally you need to change the value "ID" to represent the actual key values of the target table.

Optionally you can override values on the field list. Here "IMPORTED_FLAG" is modified to 'Y' rather than pulling the value from the source table.

The Resulting insert script (abbreviated):
<PRE>
INSERT INTO ARTCUCLS
( CLASS_NO, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG)
SELECT 'C', 'COD Customer', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'
from artloc where loc_id=0 and not EXISTS (SELECT 'A' FROM ARTCUCLS WHERE CLASS_NO= 'C' );
INSERT INTO ARTCUCLS
( CLASS_NO, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG)
SELECT 'CC', 'Commercial Contractor', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'
from artloc where loc_id=0 and not EXISTS (SELECT 'A' FROM ARTCUCLS WHERE CLASS_NO= 'CC' );
</PRE>

===More Samples===
Merging CC Options:
<PRE>
INSERT INTO cctoptn
( USER_GROUP_ID, SECTION_NAME, OPTION_NAME, OPTION_VALUE, LAST_CHANGE_DATETIME, LAST_CHANGE_USER)
SELECT '{USER_GROUP_ID}', '{SECTION_NAME}', '{OPTION_NAME}', '{OPTION_VALUE}', CAST('TODAY' AS TIMESTAMP), 'HIT'
from artloc where loc_id=0 and not EXISTS
(SELECT 'A' FROM cctoptn WHERE USER_GROUP_ID= '{USER_GROUP_ID}' AND SECTION_NAME= '{SECTION_NAME}' AND OPTION_NAME= '{OPTION_NAME}' );
</PRE>
In this example we test for three key fields when determining if the record exists.

Inserting GL Groups
<PRE>
INSERT INTO gltacgrp (
GROUP_ID, HEAD_DESCRIPTION, TOTAL_DESCRIPTION, LINE_DESCRIPTION, PARENT_GROUP_ID, SPECIAL_FUNCTION, DETAIL_LEVEL, SEQUENCE, ALLOC_RULE_ID, SECURITY_CLASS, DR_CR_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG )
SELECT
'{GROUP_ID}', '{HEAD_DESCRIPTION}', '{TOTAL_DESCRIPTION}', '{LINE_DESCRIPTION}', '{PARENT_GROUP_ID}', '{SPECIAL_FUNCTION}', '{DETAIL_LEVEL}', '{SEQUENCE}', '{ALLOC_RULE_ID}', '{SECURITY_CLASS}', '{DR_CR_FLAG}', '{LAST_CHANGE_DATETIME|DATETIME=0}', '{LAST_CHANGE_USER}', '{ACTIVE_FLAG}'
from artloc where loc_id=0 and not EXISTS (SELECT 'A' FROM GLTACGRP WHERE GROUP_ID= '{GROUP_ID}' );
</PRE>
This will insert only the groups that do not exist.

=QueryGen Cases=
*[[QueryGen Case: Fix Standard Cost]]
*[[QueryGen Case: Create Inventory Transactions From Structure]]
*[[QueryGen Case: Replicate Tickets]] e.g. copying tickets from one database to another.
*[[Replicating Keystone Data with QueryGen]]
*[[QueryGen Case: Create Tax Records From Invoices]]
*[[QueryGen Case: Replicating Forms]]
*[[QueryGen Case: Import Keystone Batch Mixes]]
*[[QueryGen Case: Security Work]]
*[[QueryGen Case: Generate Security Reports]]
*[[QueryGen Case: Move Sales to Plant Tickets]]
*[[QueryGen Case: Add missing item to all Jobs]]

Microsoft SQL Server Query Features

2024-02-27T16:25:04Z

Chanson: /* iif */

===trim===
Commands: trim, ltrim, rtrim
The trim commands will trim spaces from the beginning and end of strings.

In older MS SQL you must use ltrim and rtrim together to trim both ends of a string...
<code>select rtrim(ltrim(address1)) from Customer</code>

Modern MS SQL allows for just trim...
<code>select trim(address1) from Customer</code>

===Concat===
Use concat instead of the "||" characters used in Interbase to concatenate.

Example...
<code>CONCAT('Acct Cat Code: ',RTRIM(LTRIM(ACCT_CAT_CODE))) USER_2,</code>

===replace===

Replace singele quote with 2 single quotes:
<code>replace(rtrim(ltrim(NAME)),'''','''''') "DRIVER_NAME",</code>

===left, right===
===case===
===iif===
Similar to using a case statement, iif gives allows you 2 responses to an if condition..

<code>iif(rtrim(LTRIM(INACTIVE_CODE))='00','Y','N') active_flag,</code>

===windowing functions===

===rank===

===derived queries===
Derived queries, fequently seen as a "with" statement... allow for querying queries.

Microsoft SQL Server Query Features

2024-02-27T16:24:12Z

Chanson: /* concat */

===trim===
Commands: trim, ltrim, rtrim
The trim commands will trim spaces from the beginning and end of strings.

In older MS SQL you must use ltrim and rtrim together to trim both ends of a string...
<code>select rtrim(ltrim(address1)) from Customer</code>

Modern MS SQL allows for just trim...
<code>select trim(address1) from Customer</code>

===Concat===
Use concat instead of the "||" characters used in Interbase to concatenate.

Example...
<code>CONCAT('Acct Cat Code: ',RTRIM(LTRIM(ACCT_CAT_CODE))) USER_2,</code>

===replace===

Replace singele quote with 2 single quotes:
<code>replace(rtrim(ltrim(NAME)),'''','''''') "DRIVER_NAME",</code>

===left, right===
===case===
===iif===

===windowing functions===

===rank===

===derived queries===
Derived queries, fequently seen as a "with" statement... allow for querying queries.

Microsoft SQL Server Query Features

2024-02-27T16:20:58Z

Chanson: /* trim */

Microsoft SQL Server Query Features

2024-02-27T16:18:27Z

Chanson: Created page with "===trim=== ltrim rtrim select trim(address1) from Customer ===concat=== ===replace=== Replace singele quote with 2 single quotes: <code>replace(rtrim(ltrim(NAME)),'''','''''') "DRIVER_NAME",</code> ===left, right=== ===case=== ===iif=== ===windowing functions=== ===rank=== ===derived queries=== Derived queries, fequently seen as a "with" statement... allow for querying queries."

===trim===
ltrim rtrim

select trim(address1) from Customer

===concat===

===replace===

Replace singele quote with 2 single quotes:
<code>replace(rtrim(ltrim(NAME)),'''','''''') "DRIVER_NAME",</code>

===left, right===
===case===
===iif===

===windowing functions===

===rank===

===derived queries===
Derived queries, fequently seen as a "with" statement... allow for querying queries.

Command Import: Hedger Work Notes

2024-02-27T15:48:24Z

Chanson: /* =Price Groups */

2022DEC19
===Customer Class===
<pre>
select
ltrim(rtrim(sales_anl_code)) class_no,
ltrim(rtrim(descry)) description, *
from sanl

==
INSERT INTO artcucls
( CLASS_NO, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{CLASS_NO}', '{DESCRIPTION|LEN=35}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );
==
</pre>
===Price Groups===
<pre>

SELECT
RTRIM(LTRIM(replace(PRICE_CAT,'.',''))) GROUP_NO,
RTRIM(LTRIM(DESCR)) DESCRIPTION,
PRCC.* FROM prcc
==
INSERT INTO ARTPRGRP
( GROUP_NO, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG, MEMO ) VALUES
( '{GROUP_NO}', '{DESCRIPTION}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' , 'CMD PRICE CAT:{PRICE_CAT|TRIM=0}' );

</pre>
===GL===
<pre>
LQ1: (LIST OF NUMERIC PLANTS IN CMD)
SELECT
ISNUMERIC(plant_code),p.*
FROM plnt p
where isnumeric (plant_code)=1

LQ2: (MODEL G/L)
select * from gltacct
where segment_4 like '150'

PRIMARY:
select lq1.plant_code,lq2.* from lq2
inner join lq1 on 1=1
ORDER BY 1,2

INSERT INTO
gltacct
( GL_ACCOUNT, DESCRIPTION, GL_GROUP, SEQUENCE, ACCOUNT_CLASS, COMPANY_NO, DIVISION_NO, ALLOC_RULE_ID, NRMCA_ACCOUNT, ACCOUNT_TYPE, DR_CR_FLAG, INTRA_COMPANY, JC_REQUIRED, SECURITY_CLASS, IMPORTED_FLAG, VALID_FROM_DATE, VALID_TO_DATE, NOTES_1, NOTES_2, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{segment_1}-00-9999{PLANT_CODE}-{PLANT_CODE}', '{DESCRIPTION}', '{GL_GROUP}', {SEQUENCE}, '{ACCOUNT_CLASS}', '{COMPANY_NO}', '{DIVISION_NO}', '{ALLOC_RULE_ID}', '{NRMCA_ACCOUNT}', '{ACCOUNT_TYPE}', '{DR_CR_FLAG}', '{INTRA_COMPANY}', '{JC_REQUIRED}', '{SECURITY_CLASS}', '{IMPORTED_FLAG}', '{VALID_FROM_DATE}', '{VALID_TO_DATE}', '{NOTES_1}', '{NOTES_2}', CAST('TODAY' AS TIMESTAMP), 'HIT', '{ACTIVE_FLAG}' );

= gl 000 =

select * from gltacct
where segment_4=000

INSERT INTO GLTACCT
( GL_ACCOUNT, DESCRIPTION, GL_GROUP, SEQUENCE, ACCOUNT_CLASS, COMPANY_NO, DIVISION_NO, ALLOC_RULE_ID, NRMCA_ACCOUNT, ACCOUNT_TYPE, DR_CR_FLAG, INTRA_COMPANY, JC_REQUIRED, SECURITY_CLASS, IMPORTED_FLAG, VALID_FROM_DATE, VALID_TO_DATE, NOTES_1, NOTES_2, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{SEGMENT_1}-00-9999{SEGMENT_4}-{SEGMENT_4}', '{DESCRIPTION}', '{GL_GROUP}', {SEQUENCE}, '{ACCOUNT_CLASS}', '{COMPANY_NO}', '{DIVISION_NO}', '{ALLOC_RULE_ID}', '{NRMCA_ACCOUNT}', '{ACCOUNT_TYPE}', '{DR_CR_FLAG}', '{INTRA_COMPANY}', '{JC_REQUIRED}', '{SECURITY_CLASS}', '{IMPORTED_FLAG}', '{VALID_FROM_DATE}', '{VALID_TO_DATE}', '{NOTES_1}', '{NOTES_2}', CAST('TODAY' AS TIMESTAMP), 'HIT', '{ACTIVE_FLAG}' );

TODO - MAP SEGMENT 3 BY PLANT FROM 9999###
==========
</pre>
===Plant===
<pre>
SELECT
p.*
FROM plnt p
where isnumeric (plant_code)=1

INSERT INTO ARTPLANT ( PLANT_NO, SYS_LOC, PLANT_NAME, ADDRESS_1, ADDRESS_2, PHONE_NO, PLANT_TYPE, PLANT_SCREEN_ID, DIVISION_NO,PLANT_WASH_MINLD, JOB_WASH_MINLD,
BATCH_CAPACITY,
MEMO, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{plant_code|trim=0}', 0, '{name}', '{addr_line_1}', '{addr_line_2}', '{PHONE_Num}', 'D', '{short_name}', null, {PLANT_WASHDOWN_TIME}, {JOB_WASHDOWN_TIME},
{MAX_BATCH_SIZE},
'{LOC_CODE|trim=0}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

TODO: GL MASK - DEFAULT G/L ACCOUNTS
</pre>
===Tax===
(when Flat in Keystone and MultiLevel in CMD)
Tax Research
<pre>
select j.tax_code,a.descr,sum(prim_rate_curr_pct), sum(1) ct
from taxj j
inner join taxc a on a.tax_code=j.tax_code
inner join taxl l on j.tax_loc=l.tax_loc and l.tax_auth=j.tax_auth
group by j.tax_code,a.descr;

select *
from taxj j
inner join taxc a on a.tax_code=j.tax_code
inner join taxl l on j.tax_loc=l.tax_loc and l.tax_auth=j.tax_auth
order by j.tax_code,a.descr;

select * from taxj order by tax_code,tax_auth,tax_loc;
select * from taxc;
select * from taxl;

</pre>
Confirmed FLAT OK

ARTTXLOC/AUT/STR:
<pre>
select j.tax_code,a.descr,sum(prim_rate_curr_pct) TAX_RATE, sum(1) ct
from taxj j
inner join taxc a on a.tax_code=j.tax_code
inner join taxl l on j.tax_loc=l.tax_loc and l.tax_auth=j.tax_auth
group by j.tax_code,a.descr;

INSERT INTO ARTTXLOC
( TAX_LOCALITY, LINE_NO, DESCRIPTION, TAX_COL, LOC_LEVEL, TAX_RATE, CALC_METHOD, EFFECT_DATE, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{TAX_CODE}', 1, '{DESCR}', 1, 1, {TAX_RATE}, 1, '01/01/1900', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

INSERT INTO ARTTXAUT
( TAX_AUTHORITY, DESCRIPTION, INCREASE_DISC_FLAG, DISC_INCLUDES_TAX, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{TAX_CODE}', '{DESCR}', 'N', 'Y', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

INSERT INTO
ARTTXSTR
( SESSION_NO, TRANS_NO, TAX_AUTHORITY, TAX_LOCALITY, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( 0, {TAX_RATE|INC=1}, '{TAX_CODE}', '{TAX_CODE}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

</pre>
===Unit of Measure===
ARTUMS (This finds CMD U/M that are actually used but not in Keystone)
<pre>
LQ1:
SELECT * FROM aRTUMS

LQ2:
SELECT
LTRIM(UPPER(ABBR)) UM,
UOMS,* FROM UOMS

LQ3: -- This finds the uoms used for importable items (no need to import baggage)
SELECT DISTINCT (order_uom) UMX FROM IMST UNION
SELECT DISTINCT (price_uom) FROM IMST UNION
SELECT DISTINCT (invy_uom) FROM IMST UNION
SELECT DISTINCT (purch_uom) FROM IMST UNION
SELECT DISTINCT (batch_uom) FROM IMST UNION
SELECT DISTINCT (rpt_uom) FROM IMST UNION
SELECT DISTINCT (order_dosage_qty_uom) FROM IMST

SELECT:
SELECT lq2.* FROM LQ2
INNER JOIN LQ3 ON LQ2.UOM=LQ3.UMX
LEFT OUTER JOIN LQ1
ON LQ2.UM=LQ1.UNIT_OF_MEASURE
WHERE UNIT_OF_MEASURE IS NULL

INSERT INTO ARTUMS
( UNIT_OF_MEASURE, EXPANDED_UMS, DESCRIPTION, UMS_TYPE, UMS_SYSTEM, SUBTOTAL, SYSTEM_FLAG, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{UM}', '{UOM}', '{DESCR}', 'U', 'E', 'N', 'N', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

-- Set UofM Expanded
SELECT lq1.*,lq2.* FROM LQ2
INNER JOIN LQ3 ON LQ2.UOM=LQ3.UMX
LEFT OUTER JOIN LQ1
ON cast(LQ2.UM as text)=cast(LQ1.UNIT_OF_MEASURE as text)
WHERE (UNIT_OF_MEASURE IS not NULL)

update ARTUMS set EXPANDED_UMS={uom} where unit_of_measure='{UM}';

Note - the expanded UofM stored for potential linking on imports.

TODO: Clear expanded at roll out - it will print on reports.

=== artprcls
Straight rip from Alt Company

===artslspn

SELECT
ltrim(empl_code) CLASS_NO,
ltrim(empl_code) USER_1,
rtrim(LTRIM(NAME)) DESCRIPTION,
LTRIM(PHONE_NUM) PHONE_NO,
LTRIM(ASSGN_PLANT_CODE) USER_2
FROM empl
where slsmn_flag='True'

INSERT INTO artslspn
( CLASS_NO, EMPLOYEE_NO, DESCRIPTION, PHONE_NO, USER_2 ) VALUES
( SUBSTR(100+{CLASS_NO|INC=1},2,3), '{USER_1}', '{DESCRIPTION}', '{PHONE_NO}', '{USER_2}' );
========
=== arttruck
select
rtrim(ltrim(truck_code)) "TRUCK_NO",
rtrim(ltrim(DESCR)) "DESCR",
truc.* from truc
ORDER BY UPDATE_DATE DESC

INSERT INTO ARTTRUCK
( TRUCK_NO, TARE_WEIGHT, LAST_TARE_DATE, HOME_PLANT_NO, PLATE_NUMBER, EXPIRATION_DATE, ACTIVE_FLAG, memo ) VALUES
( '{TRUCK_CODE|TRIM=0}', {TARE_WGT|null=0}, '{TARE_DATE}', '{FROM_PLANT_CODE|TRIM=0}', '{LIC_NUM}', '{expir_date_1}', 'Y' ,
'Cmd Import mm/yy/dddd
Description: {DESCR}
Owner: {OWNER_NAME}
Trk Type: {truck_TYPE}
Inactive: {INACTIVE_FLAG}
Max Ld Sz: {MAX_LOAD_SIZE}
Driver Emp: {assgn_driv_empl_code|TRIM=0}'
); /* ------------------------------------ */
========
=== ARTDRIV
SELECT
rtrim(ltrim(empl_code)) "DRIVER_CODE", -- 12/11
rtrim(ltrim(NAME)) "DRIVER_NAME",
EMPL.* from empl
where driv_flag = 1
order by update_date desc

INSERT INTO artdriv
( DRIVER_CODE, DRIVER_NAME, PHONE_NO, HOME_PLANT_NO, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG,memo ) VALUES
( '{empl_CODE|trim=0}', '{NAME}', '{PHONE_Num}', '{assgn_plant_code}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y',
'Imported mm/dd/yyyy
Inactive Date: {Inactive_date}
Inactive code: {inactive_code}
' );
========
=== ARTPROD

--t22
(01/05/22 simplified and cleaned up)
SELECT
CONCAT(RTRIM(LTRIM(IMST.ITEM_CODE)),'.',
PLNT.PLANT_CODE) PRODUCT_CODE_ORIG,
UPPER(CONCAT( RTRIM(LTRIM(IMST.ITEM_CODE)) ,'.',PLNT.PLANT_CODE)) "PRODUCT_CODE",
UPPER(RTRIM(LTRIM(IMST.ITEM_CODE))) "BASE_PRODUCT", -- 12/15 ADJ . TO -
RTRIM(lTRIM(ILOC.LOC_CODE)) LOC_CODE,
rtrim(LTRIM(PLNT.PLANT_CODE)) PLANT_NO,
coalesce(max_load_size,0) mxldsz,
RTRIM(LTRIM(IMST.ITEM_CODE)) ITEM_CODE,UPPER(IU.ABBR) INVUM,UPPER(PU.ABBR) PRICEUM,UPPER(PU.ABBR) BATCH_UMS,
CASE INVY_FLAG WHEN 'True' then 'Y' else 'N' END STOCKED,
CASE imst.TAXBLE_CODE WHEN '1' then 'Y' else 'N' END TAX_CODE_1,
LTRIM(imst.ITEM_CAT) ITEM_CAT,
case RTRIM(Ltrim(imst.item_cat)) -- icat to product class translation
WHEN '1' THEN 'C5'
WHEN '3' THEN 'X2'
WHEN '4' THEN 'X3'
WHEN '5' THEN 'X5'
WHEN '6' THEN 'Y1'
WHEN '7' THEN 'Y5'
WHEN '8' THEN 'Y4'
WHEN '9' THEN 'A4'
WHEN '10' THEN '23'
WHEN '11' THEN 'Y3'
WHEN '12' THEN 'Y6'
WHEN '15' THEN 'P1'
WHEN '18' THEN 'P2'
WHEN '21' THEN 'X1'
WHEN '23' THEN '23'
WHEN '24' THEN 'X4'
WHEN '1.1' THEN 'C5'
WHEN '1.2' THEN 'C5'
WHEN '1.3' THEN 'C5'
WHEN '1.4' THEN 'C5'
WHEN '1.5' THEN 'C5'
WHEN '1.6' THEN 'C5'
WHEN '1.7' THEN 'C5'
WHEN '1.8' THEN 'C5'
WHEN '1.9' THEN 'C5'
WHEN '13.1' THEN 'R1'
WHEN '13.2' THEN 'R2'
WHEN '13.6' THEN 'T1'
WHEN '13.8' THEN 'D1'
WHEN '20.1' THEN 'P1'
WHEN '1.10' THEN 'C5'
elSe 'Z1' END PRODUCT_CLASS,
COALESCE (IMST.PCT_AIR,0) PCT_AIR,
COALESCE (IMST.WATER_CEM_RATIO,0) WATER_CEM_RATIO,
--IIF( LEN(REPLACE(imst.DESCR, '''', '~' ))<=35, REPLACE(imst.DESCR, '''', '~' ),REPLACE(imst.SHORT_DESCR, '''', '~' )) DESCRIPTION,
replace(replace(replace(REPLACE(imst.DESCR, '''', '~' ),' ',' '),' ',' '),' ',' ') DESCR2,
iif(resale_flag=1,'Y','N') "SELLABLE_FLAG",
COALESCE(iloc.curr_std_cost,0) CURR_STD_COST,
prev_std_cost,
cost_ext_code,
prev_cost_ext_code,
std_cost_effect_date,
curr_opt_cost,
prev_opt_cost,
opt_cost_effect_date
,imst.short_descr
,iloc.inactive_code
,imst.tax_rate_code
,imst.NON_TAX_RSN_CODE
,imst.descr
--,IMST.*
FROM IMST
INNER JOIN ILOC ON ILOC.ITEM_CODE=IMST.ITEM_CODE
INNER JOIN PLNT ON PLNT.LOC_CODE=ILOC.LOC_CODE
LEFT OUTER JOIN DBO.UOMS IU ON IU.UOM=IMST.INVY_UOM
LEFT OUTER JOIN DBO.UOMS PU ON PU.UOM=IMST.PRICE_UOM
LEFT OUTER JOIN DBO.UOMS BU ON BU.UOM=IMST.BATCH_UOM
where isnumeric (plnt.plant_code)=1

INSERT INTO
ARTPROD
( PRODUCT_CODE, UNIT_OF_MEASURE, DESCRIPTION_1, DESCRIPTION_2, LOCATION, STOCKED, TAX_CODE, TAX_CODE_2, TAX_CODE_3, BASE_PRODUCT, PRODUCT_CLASS, PLANT_NO, PRODUCT_TYPE, INGREDIENT_TYPE, EXPORT_FLAG, ROLLUP_FLAG, SELLABLE_FLAG, SEND_TO_BATCH, UPC_CODE, VENDOR_NO, VENDOR_PART_NO, LIST_PRICE, SPECIFIC_GRAVITY, PERCENT_AIR, MIX_WC_RATIO, MIX_YIELD, MIXER_TIME_SEC, MAX_LOAD_SIZE, STD_PCT_DISC, AUTO_QTY, DEFAULT_SLUMP, PURCHASE_UMS, ENTRY_UMS, MIX_UMS, BATCH_UMS, NOTES_1, NOTES_2, NOTES_3, NOTES_4, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG, MEMO ) VALUES
( '{PRODUCT_CODE}', '{PRICEUM}', '{DESCR2|STRLEN=35}', '{SHORT_DESCR}', NULL, '{STOCKED}', '{TAX_CODE_1}', 'Y', 'Y', '{ITEM_CODE}', '{PRODUCT_CLASS}', '{PLANT_NO}', 'X', NULL, 'N', 'N', 'Y', 'N', NULL, NULL, NULL, 0, 0, {PCT_AIR}, {WATER_CEM_RATIO}, 0 , 0, {MXLDSZ}, NULL, 'N', 0, NULL, NULL, NULL, '{BATCH_UMS}', '{ITEM_CAT}', 'LOC: {LOC_CODE} / INACTIVE: {INACTIVE_CODE}', '{TAX_RATE_CODE}/{NON_TAX_RSN_CODE}', '{DESCR}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'
, '***Import Notes 01/03/2023 ***' );

todo:
Prices
Apply Product Class Updates
Update Product, Ingredient Types / G/L from product classes.

To update missing products do main query in LQ1 and use:

select LQ1.* from lq1
LEFT OUTER JOIN LQ2 ON LQ2.PRODUCT_CODE = LQ1.PRODUCT_CODE
WHERE LQ2.PRODUCT_CODE IS NULL
LIMIT 5000;
(5000 at a time for processing efficiency)

-- FIX (01/04/2023 - unneeded since reprocessed products)

ALTER TRIGGER argprod_bupdate INACTIVE;
ALTER TRIGGER argprod_Aupdate INACTIVE;

select PRODUCT_CODE,upper(BASE_PRODUCT) BASE_PRODUCT,PLANT_NO from artPROD
WHERE PRODUCT_CODE <> upper(BASE_PRODUCT)||'.'||PLANT_NO
ORDER BY PLANT_NO,PRODUCT_CODE

UPDATE ARTPROD SET PRODUCT_CODE='{BASE_PRODUCT}.{PLANT_NO}' WHERE upper(BASE_PRODUCT) = '{BASE_PRODUCT}' AND PLANT_NO='{PLANT_NO}';

ALTER TRIGGER argprod_bupdate ACTIVE;
ALTER TRIGGER argprod_Aupdate ACTIVE;

=======
=== ARTCTCLS

SELECT ltrim(rtrim(terms_code)) terms_code
,descry
,disc_amt_1
,disc_amt_2
,disc_day_1
,disc_day_2
,due_day_1
,due_day_2
,short_descr
,terms_type
,per_uom
,update_date
FROM CMDPROD_ARK.dbo.trms

INSERT INTO ARTCTCLS
( CLASS_NO, DESCRIPTION, DUE_BY, DUE_BY_PIVOT, DUE_IN, DISC_PCT, DISC_BY, DISC_BY_PIVOT, DISC_IN, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{TERMS_CODE}', '{DESCR}', NULL, NULL, NULL, {DISC_AMT_1}, NULL, NULL, {DISC_DAY_1}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

NOTE: Not 100% clear on how to match days -manually edit.
========
=== ARTALCLS (FROM alt company)
select aa.* ,
a.segment_1,a.segment_2,a.segment_3,a.segment_4
from artalcls aa
left outer join gltacct a on a.gl_account = aa.allow_gl

INSERT INTO ARTALCLS
( CLASS_NO, DESCRIPTION, ALLOW_GL, ALLOW_INCLUDES_TAX, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{CLASS_NO}', '{DESCRIPTION}', '{SEGMENT_1}-00-9999{SEGMENT_4}-{SEGMENT_4}', '{ALLOW_INCLUDES_TAX}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', '{ACTIVE_FLAG}' );

========
=== ARTCUST

select
LTRIM(RTRIM(cust.CUST_CODE)) CUST_NO,
left(ltrim(rtrim(CUST.NAME)),30) name,
left(CUST.ADDR_LINE_1,30) addr_line_1,
left(CUST.ADDR_LINE_2,30) addr_line_2,
left(replace(addr_city,',',''),16) CITY,
replace(LEFT(rtrim(ltrim(ADDR_STATE)),2),'M0','MO') STATE,
PHONE_NUM_1,PHONE_NUM_2,PHONE_NUM_3,
LTRIM(RTRIM(ADDR_POSTCD)) ZIP,cust.tax_code "TAX_AUTHORITY",
--cast(cust.tax_code as integer) txcd_int,
--left(LTRIM(RTRIM(taxj.tax_loc)),3) TAX_AUTHORITY,
LTRIM(RTRIM(CC_TERMS_CODE)) TERMS,
LTRIM(RTRIM(UPPER(CC_SALES_ANL_CODE))) CUCLS,
rtrim(LTRIM(CC_SLSMN_EMPL_CODE)) SLS_EMP,
rtrim(LTRIM(CC_PRICE_CAT)) PRICE_GROUP,
rtrim(LTRIM(CC_PRICE_PLANT_CODE) ) PRICE_PLANT,
CASE TAXBLE_CODE WHEN '1' then '' else CONCAT('TAX EXMPT RSN:', LTRIM(NON_TAX_RSN_CODE) ) END TAX_RSN,
case isnumeric(CC_SALES_ANL_CODE)
WHEN 0 THEN LTRIM(CC_SALES_ANL_CODE) ELSE RIGHT(CAST(100+CAST(CC_SALES_ANL_CODE AS INTEGER) AS CHAR(3)),2) END CUST_CLASS,
rtrim(LTRIM(INACTIVE_CODE)) INACTIVE_CODE,
CASE LTRIM(RTRIM(TAXBLE_CODE)) WHEN 1 THEN 'Y' ELSE 'N' END TAX_CODE,
CONCAT('Acct Cat Code: ',RTRIM(LTRIM(ACCT_CAT_CODE))) USER_2,
LEFT(CONTCT_NAME,30) CONTACT,
LEFT(SORT_NAME,30) SEQUENCE_CODE,
acct_cat_code,SETUP_DATE
from cust
--left outer join taxj on cast(taxj.tax_code as integer)=cast(cust.tax_code as integer)
order by cust_no

INSERT INTO artcust
( CUST_NO, SALESPERSON, CUST_CLASS, TERMS_CLASS, PRICE_GROUP, DIVISION_NO, PHONE_NO, FAX_NO, PAGER_NO, MOBILE_NO, EMAIL, URL, START_DATE, TAX_CODE, TAX_CODE_2, TAX_CODE_3, COD_FLAG, NAME, ADDRESS_1, ADDRESS_2, CITY, STATE, ZIP, USER_1, USER_2, NOTES_1, TAX_AUTHORITY, CONTACT, SEQUENCE_CODE, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{CUST_NO}', (SELECT CLASS_NO FROM ARTSLSPN WHERE EMPLOYEE_NO='{SLS_EMP}') , '{CUCLS}', '{TERMS}', '{PRICE_GROUP}' , NULL, '{PHONE_NUM_1}', NULL, NULL, '{PHONE_NUM_3}', NULL, NULL, '{SETUP_DATE}', '{TAX_CODE}', 'Y', 'Y', 'N', '{NAME|STRLEN=30}', '{ADDR_LINE_1|STRLEN=30}', '{ADDR_LINE_2|STRLEN=30}', '{CITY}', '{STATE}', '{ZIP}', 'Sls Emp: {SLS_EMP|trim=0}', '{USER_2}', '{TAX_RSN}', '{TAX_AUTHORITY}', '{CONTACT}', '{SEQUENCE_CODE}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

=====
== LIST PRICE ==
Price cat 1 as list price (54986 prices are cat 1)

SELECT
concat(upper(rtrim(LTRIM(IPRC.ITEM_CODE))) , '.' , rtrim(ltrim(plnt.plant_code))) "PRODUCT_CODE",
upper(rtrim(LTRIM(IPRC.ITEM_CODE))) BASE_PRODUCT,
rtrim(ltrim(plnt.plant_code)) PLANT_NO,
PRICE UNIT_PRICE,1 CT,
IPRC.*
FROM IPRC
INNER JOIN PLNT ON plnt.LOC_CODE=iprc.LOC_CODE AND ISNUMERIC(PLNT.plant_code)>0
INNER JOIN ILOC ON ILOC.item_code=IPRC.item_code AND ILOC.loc_code=iprc.loc_code
INNER JOIN IMST ON IMST.item_code=ILOC.item_code
WHERE RTRIM(LTRIM(iprc.PRICE_CAT)) = '1' and price <> 0
ORDER BY BASE_PRODUCT,PLANT_NO,PRICE_CAT,effect_date

UpdATE ARTPROD SET list_PRICE={PRICE} WHERE product_code='{BASE_PRODUCT}.{PLANT_NO}';

-- Local to make manageable chunks and update only mismatches...
LQ2:
select lq2.*,LQ1.* from lq1
LEFT OUTER JOIN LQ2 ON LQ2.PRODUCT_CODE = LQ1.PRODUCT_CODE
WHERE LQ2.LIST_PRICE <> LQ1.PRICE

select lq2.*,LQ1.* from lq1
LEFT OUTER JOIN LQ2 ON LQ2.PRODUCT_CODE = LQ1.PRODUCT_CODE
WHERE LQ2.LIST_PRICE <> LQ1.PRICE
limt 10000 offset 20000 -- 20000- 29999

========
=== artprice - group prices plant specific

SELECT
concat(upper(rtrim(LTRIM(IPRC.ITEM_CODE))) , '.' , rtrim(ltrim(plnt.plant_code))) "PRODUCT_CODE",
upper(rtrim(LTRIM(IPRC.ITEM_CODE))) BASE_PRODUCT,
rtrim(ltrim(plnt.plant_code)) PLANT_NO,
rtrim(ltrim(iprc.price_cat)) GROUP_NO,
UPPER(PU.ABBR) UNIT_OF_MEASURE,
PRICE UNIT_PRICE,1 CT,
IPRC.*
FROM IPRC
INNER JOIN PLNT ON plnt.LOC_CODE=iprc.LOC_CODE AND ISNUMERIC(PLNT.plant_code)>0
INNER JOIN ILOC ON ILOC.item_code=IPRC.item_code AND ILOC.loc_code=iprc.loc_code
INNER JOIN IMST ON IMST.item_code=ILOC.item_code
LEFT OUTER JOIN UOMS PU ON PU.UOM=IMST.PRICE_UOM
WHERE RTRIM(LTRIM(iprc.PRICE_CAT)) > '1' and price <> 0
ORDER BY BASE_PRODUCT,PLANT_NO,PRICE_CAT,effect_date

INSERT INTO ARTPRICE
( SESSION_NO, TRANS_NO, PRICE_TYPE, CUST_NO, GROUP_NO, PRICE_CODE, PRODUCT_CODE, UNIT_OF_MEASURE, UNIT_PRICE, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( 1, {GROUP_NO|INC=1}, 'G', NULL,'{GROUP_NO}', 'P', '{BASE_PRODUCT}.{PLANT_NO}', '{UNIT_OF_MEASURE}', {UNIT_PRICE}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

-- 13 records
========

No Customer Price (CPRD)

========
ARTSTR WIP

select -- TOP (100)
1 SESSION_NO,1 TRANS_NO,SORT_LINE_NUM SEQUENCE_CODE,
PLNT.LOC_CODE "LOC_CODE",
(PLNT.PLANT_CODE) PLANT_NO,
LTRIM(RTRIM(ICST.ITEM_CODE)) ASSY_PRODUCT_CODE,
LTRIM(RTRIM(CONST_ITEM_CODE)) COMP_PRODUCT_CODE,
UPPER(LTRIM(CU.ABBR)) COMP_UMS,
QTY QTY_ASSEMBLY, ILC.ITEM_CODE INGITEMLOC ,IMC.ITEM_CODE INGITEM
from icst
LEFT OUTER JOIN UOMS CU ON CU.UOM=ICST.QTY_UOM
INNER JOIN PLNT ON ICST.LOC_CODE=PLNT.LOC_CODE AND ISNUMERIC(PLNT.PLANT_CODE)>0
INNER join iloc on iloc.item_code=icst.item_code AND ILOC.LOC_CODE=ICST.LOC_CODE
inner join imst on imst.item_code=iloc.item_code
LEFT OUTER JOIN ILOC ILC ON ILC.ITEM_CODE=ICST.CONST_ITEM_CODE AND ILC.LOC_CODE=ICST.LOC_CODE
LEFT OUTER JOIN IMST IMC ON IMC.ITEM_CODE=ICST.CONST_ITEM_CODE

INSERT INTO ARTPRSTR
( SESSION_NO, TRANS_NO, SEQUENCE_CODE, ASSY_PRODUCT_CODE, COMP_PRODUCT_CODE, COMP_UMS, QTY_ASSEMBLY, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( {SESSION_NO}, {TRANS_NO|INC=1}, {SEQUENCE_CODE}, '{ASSY_PRODUCT_CODE}.{PLANT_NO}', '{COMP_PRODUCT_CODE}.{PLANT_NO}', '{COMP_UMS}', {QTY_ASSEMBLY}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

note: left outer allows missing items to go through - 5 components for plant 314 added.
(CMD does not have integrity - so structure for invalid products permitted)

======================================================
jobs jobs jobs

Mismatch Items - no ILOC:

select
proj.cust_code
,prjp.proj_code,CUST.NAME "CustName",plnt.plant_code plant,(prjp.prod_code) ProjectProduct
,prjp.price
from prjp
INNER JOIN CUST ON CUST.CUST_CODE=PRjp.CUST_CODE
INNER JOIN PROJ ON PROJ.PROJ_CODE=PRJP.PROJ_CODE AND PROJ.CUST_CODE=PRJP.CUST_CODE
inner join plnt on plnt.plant_code = PROJ.Cc_PRICE_PLANT_CODE
left outer JOIN ILOC ON ILOC.ITEM_CODE=PRJP.PROD_CODE and PLNT.LOC_CODE=ILOC.LOC_CODE
WHERE proj.INACTIVE_CODE=0
AND COALESCE(proj.EXPIR_DATE,'12/31/2099' ) >'1/1/23'
and iloc.item_code is null and price_plant_code <>'#'
order by proj.cust_code,prjp.proj_code,cust.name,proj.expir_date,plnt.plant_code,prjp.prod_code

0== job header query ==

with pj as(
select
proj.cust_code
,prjp.proj_code,CUST.NAME "CustName"
from prjp
INNER JOIN CUST ON CUST.CUST_CODE=PRjp.CUST_CODE
INNER JOIN PROJ ON PROJ.PROJ_CODE=PRJP.PROJ_CODE AND PROJ.CUST_CODE=PRJP.CUST_CODE
--inner join plnt on plnt.plant_code = PROJ.Cc_PRICE_PLANT_CODE
--INNER JOIN ILOC ON ILOC.ITEM_CODE=PRJP.PROD_CODE and PLNT.LOC_CODE=ILOC.LOC_CODE
inner join plnt on plnt.plant_code = PROJ.Cc_PRICE_PLANT_CODE
left outer JOIN ILOC ON ILOC.ITEM_CODE=PRJP.PROD_CODE and PLNT.LOC_CODE=ILOC.LOC_CODE
--left outer join imst on imst.item_cat=prjp.prod_code and price_plant_code='#'
WHERE proj.INACTIVE_CODE=0
AND COALESCE(proj.EXPIR_DATE,'12/31/2099' ) >'1/1/23'
group by prjp.proj_code,proj.cust_code,cust.name,proj.expir_date
)
SELECT
rtrim(LTRIM(PROJ.CUST_CODE)) CUST_NO,PROJ.EXPIR_DATE,
Left(rtrim(LTRIM(PROJ.PROJ_code)),18) JOB_PO_LOC,
PROJ_NAME,PO,
Left(rtrim(LTRIM(PROJ_name)),18) batch_job_code,
rtrim(LTRIM(PROJ_NAME)) DELV_INST_1,
Left(LTRIM(proj.tax_code),3) TAX_code,
--'IMP' TAX_AUTHORITY,
CA_TERMS_CODE,PROJ.CA_PRICE_PLANT_CODE,
LTRIM(CA_TERMS_CODE) TERMS_CLASS,
--LTRIM(CA_PRICE_PLANT_CODE) PLANT_NO,
Left(rtrim(LTRIM(PO)) ,20) CUSTOMER_PO,
LTRIM(UPPER(PROJ.CA_SALES_ANL_CODE)) CUCLS,
'' SHIP_TO_1,
--Left(RTRIM(LTRIM(ship_name)),30) SHIP_TO_1 ,
Left(RTRIM(LTRIM(PROJ.ship_addr_line_1)),30) SHIP_TO_2 ,
Left(RTRIM(LTRIM(ship_addr_line_2)),30) SHIP_TO_3 ,
CASE LTRIM(TAXBLE_CODE) WHEN 1 THEN 'Y' ELSE 'N' END JOB_TAX_CODE,
Left(concat(SHIP_CITY,' ',SHIP_STATE,' ',SHIP_POSTCD),30) SHIP_TO_4,
Taxj.tax_loc "NOTES_1",
PROJ.EXPIR_DATE "NOTES_2",
--USE_FOR_PROD_LINE_CODE "NOTES_2",
LTRIM(NON_TAX_RSN_CODE) "CUSTOM_1",
LTRIM(PROJ.CA_SLSMN_EMPL_CODE) "CUSTOM_2",
Left(RTRIM(LTRIM(CONTCT_NAME)),30) "CONTACT",
Left(RTRIM(LTRIM(PHONE_NUM_1)),30) PHONE_NO,
Left(RTRIM(LTRIM(PROJ_NAME)),30) "INVOICE_DESC"
,ship_addr_line_1,ship_addr_line_2,
PHONE_NUM_1,PHONE_NUM_2,PHONE_NUM_3,CONTct_NAME,custname
FROM proj
inner join pj on pj.proj_code=proj.proj_code and pj.cust_code=proj.cust_code
INNER JOIN TAXJ ON TAXJ.TAX_CODE=proj.TAX_CODE and taxj.tax_auth=1
where inactive_code = 0
and coalesce (PROJ.expir_date,'12/31/2099')>'1/1/2023'
ORDER BY CUST_NO,PROJ_NAME

------

INSERT INTO ARTJOBHD
( SESSION_NO, TRANS_NO, CUST_NO, JOB_PO_LOC, BATCH_JOB_CODE , TERMS_CLASS, TAX_AUTHORITY, INVOICE_DESC, NOTES_1, NOTES_2, JOB_TAX_CODE, JOB_TAX_CODE_2, JOB_TAX_CODE_3, PLANT_NO, SHIP_TO_1, SHIP_TO_2, SHIP_TO_3, SHIP_TO_4, PHONE_NO, CONTACT, CUSTOMER_PO, CUSTOM_1, CUSTOM_2, COD_FLAG, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG, MEMO ) VALUES
( 1, {CUST_NO|INC=1}, '{CUST_NO}', '{JOB_PO_LOC}', '{batch_job_code}', NULL, '{TAX_AUTHORITY}', '{INVOICE_DESC}', '{NOTES_1}', '{NOTES_2}', '{JOB_TAX_CODE}', 'Y', 'Y', NULL, '{SHIP_TO_1}', '{SHIP_TO_2}', '{SHIP_TO_3}', '{SHIP_TO_4}', '{PHONE_NO}', '{CONTACT}', '{CUSTOMER_PO}', '{CUSTOM_1}', '{CUSTOM_2}', 'N', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' ,
'***Import Notes Jan 2023***
PROJ NAME: {PROJ_NAME}
PO: {PO}
CONTACT: {CONTct_NAME}
PHONE1: {phone_num_1}
PHONE2: {phone_num_2}
PHONE3: {phone_num_3}
ship_addr_line_1 {ship_addr_line_1}
ship_addr_line_2 {ship_addr_line_2}
expir_date: {expir_date}
*****************************************
' ); -- {custname}

NOTE: plant ok?
Uses numeric job code as JOB ID -- this should be updated on ib side.

== job detail query ==

WITH JDT AS
(
select --top 100
proj.cust_code,
RTRIM(LTRIM(PRJP.PROJ_CODE)) JOB_PO_LOC,
RTRIM(LTRIM(PRJP.CUST_CODE)) CUST_NO,
RTRIM(LTRIM(PRJP.PROD_CODE)) PRODUCT_CODE,
RTRIM(LTRIM(Cc_PRICE_PLANT_CODE)) PLANT_code,
PRJP.INTRNL_LINE_NUM LINE_NO,
(SELECT UPPER(ABBR) FROM UOMS WHERE UOMS.UOM = PRJP.PRICE_UOM) UNIT_OF_MEASURE,
left(RTRIM(LTRIM(PROD_DESCR)),35) DESCRIPTION,
PRICE UNIT_PRICE,prjp.effect_date
,PRJP.PROD_CODE
,LAG(PROD_CODE,1,'__') OVER (partition by PRJP.CUST_CODE,PRJP.PROJ_CODE,Cc_PRICE_PLANT_CODE
order by PRJP.CUST_CODE,PRJP.PROJ_CODE,PRJP.PROD_CODE,Cc_PRICE_PLANT_CODE,PRJP.EFFECT_DATE DESC) PREVPROD
,iif(iloc.loc_code is null,(select min(loc_code) from iloc where iloc.item_code=imst.item_code and iloc.inactive_code=0),iloc.loc_code) altclocode
from prjp
INNER JOIN PROJ ON PROJ.PROJ_CODE=PRJP.PROJ_CODE AND PROJ.CUST_CODE=PRJP.CUST_CODE
inner join plnt on plnt.plant_code = Cc_PRICE_PLANT_CODE
left outer JOIN ILOC ON ILOC.ITEM_CODE=PRJP.PROD_CODE and PLNT.LOC_CODE=ILOC.LOC_CODE
inner join imst on imst.item_code=prjp.prod_code
WHERE proj.INACTIVE_CODE=0
AND COALESCE(proj.EXPIR_DATE,'12/31/2099' ) >'1/1/23'
)
SELECT JDT.*,plnt.plant_code "PLANT_NO"
FROM JDT
inner join cust on cust.cust_code=jdt.cust_code
inner join plnt on plnt.loc_code=jdt.altclocode AND ISNUMERIC(PLNT.PLANT_CODE)=1
WHERE PREVPROD<>PROD_CODE

INSERT INTO ARTJOBDT
( SESSION_NO, TRANS_NO, JOB_PO_LOC, CUST_NO, PRODUCT_CODE, LINE_NO, UNIT_OF_MEASURE, DESCRIPTION, UNIT_PRICE, TAX_CODE, TAX_CODE_2, TAX_CODE_3, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER ) VALUES
( 1, (SELECT TRANS_NO FROM ARTJOBHD WHERE JOB_PO_LOC='{JOB_PO_LOC}' AND CUST_NO='{CUST_NO}' )
, '{JOB_PO_LOC}', '{CUST_NO}', '{PRODUCT_CODE}.{PLANT_NO}', {LINE_NO}, '{UNIT_OF_MEASURE}', '{DESCRIPTION}', {UNIT_PRICE}, 'Y', 'Y', 'Y', 'Y', CAST('TODAY' AS TIMESTAMP), 'Y' );

NOTES: Not loving the plant mismatches on detail
If primary plant not specified - takes the lowest value.

------
ZONE
------

SELECT * FROM ZONE

INSERT INTO artzone
( ZONE, DESCRIPTION, PLANT_NO, ZONE_REGION, DEF_TAX_AUTHORITY, IMPORTED_FLAG, MEMO, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{ZONE_CODE|TRIM=0}', '{DESCR|STRLEN=35}', NULL, NULL, '{TAX_CODE}', 'Y', 'Imported 01/17/2023', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

--------------
CONTACT
------
SELECT
LTRIM(RTRIM(CCON.CUST_CODE)) ENTITY_ID,
LTRIM(RTRIM(NAME)) NAME,
LTRIM(RTRIM(PHONE_NUM_1)) PHONE_NO,
LTRIM(RTRIM(PHONE_NUM_2)) CELL_NO,
LTRIM(RTRIM(PHONE_NUM_2)) FAX_NO,
LTRIM(RTRIM(JOB_TITLE)) TITLE,
ltrim(rtrim(email_addr)) EMAIL,
* FROM CCON
INNER JOIN CTCT ON CTCT.CONTCT_CODE=CCON.CONTCT_CODE
---
INSERT INTO cctcont
( RECKEY, ENTITY_TYPE, ENTITY_ID, NAME, PHONE_NO, CELL_NO, FAX_NO, TITLE,EMAIL, EMAIL_OPT_1, EMAIL_OPT_2, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( GEN_ID(GEN_CCCONTKEY,1), 'C', '{ENTITY_ID}', '{NAME|STRLEN=30}', '{PHONE_NO|STRLEN=30}', '{CELL_NO|STRLEN=30}', '{FAX_NO|STRLEN=30}', '{TITLE}', '{EMAIL|STRLEN=80}', 'Y', 'Y', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );
---------

contacts from ONBASE...

Lq3:
SELECT
--RTRIM(rd.distprocessnum) dist_proc_num,
--RTRIM(dp.distprocessname) dist_proc_name,
--rd.recipientnum OBRecipientNO,
--RTRIM(rd.distributionflags) dist_flags,
RTRIM(replace(rd.idkeywords,'!','')) 'CustomerNO',
RTRIM(sr.recipientname) 'CustomerName',
RTRIM(sr.email) 'Email',
RTRIM(sr.faxnum) 'FaxNO'
FROM hsi.recipientxdist rd
INNER JOIN hsi.stmtrecipient sr ON rd.recipientnum = sr.recipientnum LEFT OUTER JOIN hsi.distprocess dp ON rd.distprocessnum = dp.distprocessnum
ORDER BY rd.distprocessnum, sr.recipientname, rd.distributionflags
lq2:
select cust_no,name from artcust c

Primary:
select * from lq3
left outer join lq2 on lq2.cust_no=lq3.CustomerNO
where lq2.cust_no is not null
order by cust_no

INSERT INTO cctcont
( RECKEY, ENTITY_TYPE, ENTITY_ID, NAME, PHONE_NO, CELL_NO, FAX_NO, TITLE,EMAIL, EMAIL_OPT_1, EMAIL_OPT_2, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( GEN_ID(GEN_CCCONTKEY,1), 'C', '{CUST_NO}', 'Accounts Payable', null, null, '{FAXNO|STRLEN=30}', null, '{EMAIL|STRLEN=80}', 'Y', 'Y', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

--------
Contacts Merge (If email from cmd and ob - use the cmd)

SELECT ENTITY_ID,UPPER( EMAIL) EMAIL ,COUNT(*),MIN(NAME),MAX(NAME),MIN(RECKEY) ORIGKEY,MAX(RECKEY) NEWKEY FROM CCTCONT
WHERE EMAIL IS NOT NULL
GROUP BY 1,2
HAVING COUNT(*)>1

(export a csv)

update cctcont set email_opt_1='Y' ,email_opt_2='Y' WHERE RECKEY = {ORIGKEY}; /* {EMAIL} */
DELETE FROM CCTCONT WHERE RECKEY={NEWKEY};

----

contacts deleted all where email null and cell_no null

------------ Even MOre Onbase contacts

select lq1.*,lq2.*,1 ct,lq3.contemail from lq2
left outer join lq1 on lq1.cname=lq2.rname
left outer join lq3 on lq3.cust_no=lq1.cust_code and lq2.email=lq3.contemail
where not (cust_code is null);

select ltrim(rtrim(cust_code)) cust_code,upper(name) cname from cust;

select upper(recipientname) rname,upper(ltrim(rtrim(email))) email,1 rcpct from hsi.stmtrecipient
where coalesce(email,'')<>'';

SELECT CUST_NO,c.NAME "CustomerName",
c.CONTACT "CustomerContact",cc.cell_no,cc.phone_no
,CC.NAME "ContName",upper(cc.email) "ContEmail",
email_opt_1 "EmailInv",email_opt_2 "EmailStmt"
FROM ARTCUST C
INNER JOIN CCTCONT CC ON CC.ENTITY_ID=C.CUST_NO
where cc.email is not null
order by c.cust_no,cc.reckey

INSERT INTO cctcont
( RECKEY, ENTITY_TYPE, ENTITY_ID, NAME, PHONE_NO, CELL_NO, FAX_NO, TITLE,EMAIL, EMAIL_OPT_1, EMAIL_OPT_2, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( GEN_ID(GEN_CCCONTKEY,1), 'C', '{CUST_code}', 'Accounts Payable', null, null, null, null, '{EMAIL|STRLEN=80}', 'Y', 'Y', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

----

Update Prods From Class:

SELECT product_code,PRODUCT_CLASS,SELLABLE_FLAG,PRODUCT_TYPE,INGREDIENT_TYPE, (SELECT PC.STD_SELLABLE_FLAG FROM ARTPRCLS PC WHERE PC.CLASS_NO=P.PRODUCT_CLASS) STD_SELLABLE_FLAG,
(SELECT PC.STD_PRODUCT_TYPE FROM ARTPRCLS PC WHERE PC.CLASS_NO=P.PRODUCT_CLASS) STD_PRODUCT_TYPE,
(SELECT PC.STD_ingredient_TYPE FROM ARTPRCLS PC WHERE PC.CLASS_NO=P.PRODUCT_CLASS) STD_ingredient_TYPE
FROM ARTPROD P

update artprod p
SET SELLABLE_FLAG='{STD_SELLABLE_FLAG}',
INGREDIENT_TYPE='{STD_INGREDIENT_TYPE}',
PRODUCT_TYPE='{STD_PRODUCT_TYPE}'
where product_code='{PRODUCT_CODE}';

------
in mssql: {set cust inactive}

select
CONCAT( 'UPDATE ARTCUST C SET ACTIVE_FLAG=''N'' WHERE CUST_NO = ''',RTRIM(LTRIM(CUST_CODE)),''';')
from cust
where inactive_code <> 00

===
Update sales g/l accounts by plant
(Setup Plants with default g/l!!!!)

with ga (act,s1,s2,s3,s4,actnm) as
(select gl_account,segment_1,segment_2,segment_3,segment_4,max(description)
from gltacct a
where segment_4 between '300' and '998'
group by gl_account,segment_1,segment_2,segment_3,segment_4
)
,pr (pact,ps1,ps2,ps3,ps4,plt) as
(select GL_ACCOUNT ,segment_1,segment_2,segment_3,segment_4,p.plant_no from artprod p
inner join gltacct a on a.gl_account=p.sales_gl
group by gl_account,segment_1,segment_2,segment_3,segment_4,plant_no)
select * from ga
inner join pr on pr.ps1=ga.s1 and pr.ps2=ga.s2 and pr.plt=ga.s4
order by s1,s4

update artprod p set sales_gl = '{act}' WHERE sales_gl='{pact}' and plant_no = '{plt}';

NOTE: This could be done more generically by taking part of acct from artprcls and some from the g/l pattern

</pre>

Command Import: Hedger Work Notes

2024-02-27T15:45:18Z

Chanson:

2022DEC19
===Customer Class===
<pre>
select
ltrim(rtrim(sales_anl_code)) class_no,
ltrim(rtrim(descry)) description, *
from sanl

==
INSERT INTO artcucls
( CLASS_NO, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{CLASS_NO}', '{DESCRIPTION|LEN=35}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );
==
</pre>
===Price Groups==
<pre>

SELECT
RTRIM(LTRIM(replace(PRICE_CAT,'.',''))) GROUP_NO,
RTRIM(LTRIM(DESCR)) DESCRIPTION,
PRCC.* FROM prcc
==
INSERT INTO ARTPRGRP
( GROUP_NO, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG, MEMO ) VALUES
( '{GROUP_NO}', '{DESCRIPTION}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' , 'CMD PRICE CAT:{PRICE_CAT|TRIM=0}' );

</pre>
===GL===

===Plants===
<pre>
LQ1: (LIST OF NUMERIC PLANTS IN CMD)
SELECT
ISNUMERIC(plant_code),p.*
FROM plnt p
where isnumeric (plant_code)=1

LQ2: (MODEL G/L)
select * from gltacct
where segment_4 like '150'

PRIMARY:
select lq1.plant_code,lq2.* from lq2
inner join lq1 on 1=1
ORDER BY 1,2

INSERT INTO
gltacct
( GL_ACCOUNT, DESCRIPTION, GL_GROUP, SEQUENCE, ACCOUNT_CLASS, COMPANY_NO, DIVISION_NO, ALLOC_RULE_ID, NRMCA_ACCOUNT, ACCOUNT_TYPE, DR_CR_FLAG, INTRA_COMPANY, JC_REQUIRED, SECURITY_CLASS, IMPORTED_FLAG, VALID_FROM_DATE, VALID_TO_DATE, NOTES_1, NOTES_2, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{segment_1}-00-9999{PLANT_CODE}-{PLANT_CODE}', '{DESCRIPTION}', '{GL_GROUP}', {SEQUENCE}, '{ACCOUNT_CLASS}', '{COMPANY_NO}', '{DIVISION_NO}', '{ALLOC_RULE_ID}', '{NRMCA_ACCOUNT}', '{ACCOUNT_TYPE}', '{DR_CR_FLAG}', '{INTRA_COMPANY}', '{JC_REQUIRED}', '{SECURITY_CLASS}', '{IMPORTED_FLAG}', '{VALID_FROM_DATE}', '{VALID_TO_DATE}', '{NOTES_1}', '{NOTES_2}', CAST('TODAY' AS TIMESTAMP), 'HIT', '{ACTIVE_FLAG}' );

= gl 000 =

select * from gltacct
where segment_4=000

INSERT INTO GLTACCT
( GL_ACCOUNT, DESCRIPTION, GL_GROUP, SEQUENCE, ACCOUNT_CLASS, COMPANY_NO, DIVISION_NO, ALLOC_RULE_ID, NRMCA_ACCOUNT, ACCOUNT_TYPE, DR_CR_FLAG, INTRA_COMPANY, JC_REQUIRED, SECURITY_CLASS, IMPORTED_FLAG, VALID_FROM_DATE, VALID_TO_DATE, NOTES_1, NOTES_2, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{SEGMENT_1}-00-9999{SEGMENT_4}-{SEGMENT_4}', '{DESCRIPTION}', '{GL_GROUP}', {SEQUENCE}, '{ACCOUNT_CLASS}', '{COMPANY_NO}', '{DIVISION_NO}', '{ALLOC_RULE_ID}', '{NRMCA_ACCOUNT}', '{ACCOUNT_TYPE}', '{DR_CR_FLAG}', '{INTRA_COMPANY}', '{JC_REQUIRED}', '{SECURITY_CLASS}', '{IMPORTED_FLAG}', '{VALID_FROM_DATE}', '{VALID_TO_DATE}', '{NOTES_1}', '{NOTES_2}', CAST('TODAY' AS TIMESTAMP), 'HIT', '{ACTIVE_FLAG}' );

TODO - MAP SEGMENT 3 BY PLANT FROM 9999###
==========
=== ARTPLANT
SELECT
p.*
FROM plnt p
where isnumeric (plant_code)=1

INSERT INTO ARTPLANT ( PLANT_NO, SYS_LOC, PLANT_NAME, ADDRESS_1, ADDRESS_2, PHONE_NO, PLANT_TYPE, PLANT_SCREEN_ID, DIVISION_NO,PLANT_WASH_MINLD, JOB_WASH_MINLD,
BATCH_CAPACITY,
MEMO, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{plant_code|trim=0}', 0, '{name}', '{addr_line_1}', '{addr_line_2}', '{PHONE_Num}', 'D', '{short_name}', null, {PLANT_WASHDOWN_TIME}, {JOB_WASHDOWN_TIME},
{MAX_BATCH_SIZE},
'{LOC_CODE|trim=0}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

TODO: GL MASK - DEFAULT G/L ACCOUNTS

===TAX (when Flat in Keystone and MultiLevel in CMD)

Tax Research

select j.tax_code,a.descr,sum(prim_rate_curr_pct), sum(1) ct
from taxj j
inner join taxc a on a.tax_code=j.tax_code
inner join taxl l on j.tax_loc=l.tax_loc and l.tax_auth=j.tax_auth
group by j.tax_code,a.descr;

select *
from taxj j
inner join taxc a on a.tax_code=j.tax_code
inner join taxl l on j.tax_loc=l.tax_loc and l.tax_auth=j.tax_auth
order by j.tax_code,a.descr;

select * from taxj order by tax_code,tax_auth,tax_loc;
select * from taxc;
select * from taxl;

Confirmed FLAT OK

==ARTTXLOC/AUT/STR
select j.tax_code,a.descr,sum(prim_rate_curr_pct) TAX_RATE, sum(1) ct
from taxj j
inner join taxc a on a.tax_code=j.tax_code
inner join taxl l on j.tax_loc=l.tax_loc and l.tax_auth=j.tax_auth
group by j.tax_code,a.descr;

INSERT INTO ARTTXLOC
( TAX_LOCALITY, LINE_NO, DESCRIPTION, TAX_COL, LOC_LEVEL, TAX_RATE, CALC_METHOD, EFFECT_DATE, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{TAX_CODE}', 1, '{DESCR}', 1, 1, {TAX_RATE}, 1, '01/01/1900', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

INSERT INTO ARTTXAUT
( TAX_AUTHORITY, DESCRIPTION, INCREASE_DISC_FLAG, DISC_INCLUDES_TAX, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{TAX_CODE}', '{DESCR}', 'N', 'Y', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

INSERT INTO
ARTTXSTR
( SESSION_NO, TRANS_NO, TAX_AUTHORITY, TAX_LOCALITY, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( 0, {TAX_RATE|INC=1}, '{TAX_CODE}', '{TAX_CODE}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

==ARTUMS (This finds CMD U/M that are actually used but not in Keystone)

LQ1:
SELECT * FROM aRTUMS

LQ2:
SELECT
LTRIM(UPPER(ABBR)) UM,
UOMS,* FROM UOMS

LQ3: -- This finds the uoms used for importable items (no need to import baggage)
SELECT DISTINCT (order_uom) UMX FROM IMST UNION
SELECT DISTINCT (price_uom) FROM IMST UNION
SELECT DISTINCT (invy_uom) FROM IMST UNION
SELECT DISTINCT (purch_uom) FROM IMST UNION
SELECT DISTINCT (batch_uom) FROM IMST UNION
SELECT DISTINCT (rpt_uom) FROM IMST UNION
SELECT DISTINCT (order_dosage_qty_uom) FROM IMST

SELECT:
SELECT lq2.* FROM LQ2
INNER JOIN LQ3 ON LQ2.UOM=LQ3.UMX
LEFT OUTER JOIN LQ1
ON LQ2.UM=LQ1.UNIT_OF_MEASURE
WHERE UNIT_OF_MEASURE IS NULL

INSERT INTO ARTUMS
( UNIT_OF_MEASURE, EXPANDED_UMS, DESCRIPTION, UMS_TYPE, UMS_SYSTEM, SUBTOTAL, SYSTEM_FLAG, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{UM}', '{UOM}', '{DESCR}', 'U', 'E', 'N', 'N', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

-- Set UofM Expanded
SELECT lq1.*,lq2.* FROM LQ2
INNER JOIN LQ3 ON LQ2.UOM=LQ3.UMX
LEFT OUTER JOIN LQ1
ON cast(LQ2.UM as text)=cast(LQ1.UNIT_OF_MEASURE as text)
WHERE (UNIT_OF_MEASURE IS not NULL)

update ARTUMS set EXPANDED_UMS={uom} where unit_of_measure='{UM}';

Note - the expanded UofM stored for potential linking on imports.

TODO: Clear expanded at roll out - it will print on reports.

=== artprcls
Straight rip from Alt Company

===artslspn

SELECT
ltrim(empl_code) CLASS_NO,
ltrim(empl_code) USER_1,
rtrim(LTRIM(NAME)) DESCRIPTION,
LTRIM(PHONE_NUM) PHONE_NO,
LTRIM(ASSGN_PLANT_CODE) USER_2
FROM empl
where slsmn_flag='True'

INSERT INTO artslspn
( CLASS_NO, EMPLOYEE_NO, DESCRIPTION, PHONE_NO, USER_2 ) VALUES
( SUBSTR(100+{CLASS_NO|INC=1},2,3), '{USER_1}', '{DESCRIPTION}', '{PHONE_NO}', '{USER_2}' );
========
=== arttruck
select
rtrim(ltrim(truck_code)) "TRUCK_NO",
rtrim(ltrim(DESCR)) "DESCR",
truc.* from truc
ORDER BY UPDATE_DATE DESC

INSERT INTO ARTTRUCK
( TRUCK_NO, TARE_WEIGHT, LAST_TARE_DATE, HOME_PLANT_NO, PLATE_NUMBER, EXPIRATION_DATE, ACTIVE_FLAG, memo ) VALUES
( '{TRUCK_CODE|TRIM=0}', {TARE_WGT|null=0}, '{TARE_DATE}', '{FROM_PLANT_CODE|TRIM=0}', '{LIC_NUM}', '{expir_date_1}', 'Y' ,
'Cmd Import mm/yy/dddd
Description: {DESCR}
Owner: {OWNER_NAME}
Trk Type: {truck_TYPE}
Inactive: {INACTIVE_FLAG}
Max Ld Sz: {MAX_LOAD_SIZE}
Driver Emp: {assgn_driv_empl_code|TRIM=0}'
); /* ------------------------------------ */
========
=== ARTDRIV
SELECT
rtrim(ltrim(empl_code)) "DRIVER_CODE", -- 12/11
rtrim(ltrim(NAME)) "DRIVER_NAME",
EMPL.* from empl
where driv_flag = 1
order by update_date desc

INSERT INTO artdriv
( DRIVER_CODE, DRIVER_NAME, PHONE_NO, HOME_PLANT_NO, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG,memo ) VALUES
( '{empl_CODE|trim=0}', '{NAME}', '{PHONE_Num}', '{assgn_plant_code}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y',
'Imported mm/dd/yyyy
Inactive Date: {Inactive_date}
Inactive code: {inactive_code}
' );
========
=== ARTPROD

--t22
(01/05/22 simplified and cleaned up)
SELECT
CONCAT(RTRIM(LTRIM(IMST.ITEM_CODE)),'.',
PLNT.PLANT_CODE) PRODUCT_CODE_ORIG,
UPPER(CONCAT( RTRIM(LTRIM(IMST.ITEM_CODE)) ,'.',PLNT.PLANT_CODE)) "PRODUCT_CODE",
UPPER(RTRIM(LTRIM(IMST.ITEM_CODE))) "BASE_PRODUCT", -- 12/15 ADJ . TO -
RTRIM(lTRIM(ILOC.LOC_CODE)) LOC_CODE,
rtrim(LTRIM(PLNT.PLANT_CODE)) PLANT_NO,
coalesce(max_load_size,0) mxldsz,
RTRIM(LTRIM(IMST.ITEM_CODE)) ITEM_CODE,UPPER(IU.ABBR) INVUM,UPPER(PU.ABBR) PRICEUM,UPPER(PU.ABBR) BATCH_UMS,
CASE INVY_FLAG WHEN 'True' then 'Y' else 'N' END STOCKED,
CASE imst.TAXBLE_CODE WHEN '1' then 'Y' else 'N' END TAX_CODE_1,
LTRIM(imst.ITEM_CAT) ITEM_CAT,
case RTRIM(Ltrim(imst.item_cat)) -- icat to product class translation
WHEN '1' THEN 'C5'
WHEN '3' THEN 'X2'
WHEN '4' THEN 'X3'
WHEN '5' THEN 'X5'
WHEN '6' THEN 'Y1'
WHEN '7' THEN 'Y5'
WHEN '8' THEN 'Y4'
WHEN '9' THEN 'A4'
WHEN '10' THEN '23'
WHEN '11' THEN 'Y3'
WHEN '12' THEN 'Y6'
WHEN '15' THEN 'P1'
WHEN '18' THEN 'P2'
WHEN '21' THEN 'X1'
WHEN '23' THEN '23'
WHEN '24' THEN 'X4'
WHEN '1.1' THEN 'C5'
WHEN '1.2' THEN 'C5'
WHEN '1.3' THEN 'C5'
WHEN '1.4' THEN 'C5'
WHEN '1.5' THEN 'C5'
WHEN '1.6' THEN 'C5'
WHEN '1.7' THEN 'C5'
WHEN '1.8' THEN 'C5'
WHEN '1.9' THEN 'C5'
WHEN '13.1' THEN 'R1'
WHEN '13.2' THEN 'R2'
WHEN '13.6' THEN 'T1'
WHEN '13.8' THEN 'D1'
WHEN '20.1' THEN 'P1'
WHEN '1.10' THEN 'C5'
elSe 'Z1' END PRODUCT_CLASS,
COALESCE (IMST.PCT_AIR,0) PCT_AIR,
COALESCE (IMST.WATER_CEM_RATIO,0) WATER_CEM_RATIO,
--IIF( LEN(REPLACE(imst.DESCR, '''', '~' ))<=35, REPLACE(imst.DESCR, '''', '~' ),REPLACE(imst.SHORT_DESCR, '''', '~' )) DESCRIPTION,
replace(replace(replace(REPLACE(imst.DESCR, '''', '~' ),' ',' '),' ',' '),' ',' ') DESCR2,
iif(resale_flag=1,'Y','N') "SELLABLE_FLAG",
COALESCE(iloc.curr_std_cost,0) CURR_STD_COST,
prev_std_cost,
cost_ext_code,
prev_cost_ext_code,
std_cost_effect_date,
curr_opt_cost,
prev_opt_cost,
opt_cost_effect_date
,imst.short_descr
,iloc.inactive_code
,imst.tax_rate_code
,imst.NON_TAX_RSN_CODE
,imst.descr
--,IMST.*
FROM IMST
INNER JOIN ILOC ON ILOC.ITEM_CODE=IMST.ITEM_CODE
INNER JOIN PLNT ON PLNT.LOC_CODE=ILOC.LOC_CODE
LEFT OUTER JOIN DBO.UOMS IU ON IU.UOM=IMST.INVY_UOM
LEFT OUTER JOIN DBO.UOMS PU ON PU.UOM=IMST.PRICE_UOM
LEFT OUTER JOIN DBO.UOMS BU ON BU.UOM=IMST.BATCH_UOM
where isnumeric (plnt.plant_code)=1

INSERT INTO
ARTPROD
( PRODUCT_CODE, UNIT_OF_MEASURE, DESCRIPTION_1, DESCRIPTION_2, LOCATION, STOCKED, TAX_CODE, TAX_CODE_2, TAX_CODE_3, BASE_PRODUCT, PRODUCT_CLASS, PLANT_NO, PRODUCT_TYPE, INGREDIENT_TYPE, EXPORT_FLAG, ROLLUP_FLAG, SELLABLE_FLAG, SEND_TO_BATCH, UPC_CODE, VENDOR_NO, VENDOR_PART_NO, LIST_PRICE, SPECIFIC_GRAVITY, PERCENT_AIR, MIX_WC_RATIO, MIX_YIELD, MIXER_TIME_SEC, MAX_LOAD_SIZE, STD_PCT_DISC, AUTO_QTY, DEFAULT_SLUMP, PURCHASE_UMS, ENTRY_UMS, MIX_UMS, BATCH_UMS, NOTES_1, NOTES_2, NOTES_3, NOTES_4, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG, MEMO ) VALUES
( '{PRODUCT_CODE}', '{PRICEUM}', '{DESCR2|STRLEN=35}', '{SHORT_DESCR}', NULL, '{STOCKED}', '{TAX_CODE_1}', 'Y', 'Y', '{ITEM_CODE}', '{PRODUCT_CLASS}', '{PLANT_NO}', 'X', NULL, 'N', 'N', 'Y', 'N', NULL, NULL, NULL, 0, 0, {PCT_AIR}, {WATER_CEM_RATIO}, 0 , 0, {MXLDSZ}, NULL, 'N', 0, NULL, NULL, NULL, '{BATCH_UMS}', '{ITEM_CAT}', 'LOC: {LOC_CODE} / INACTIVE: {INACTIVE_CODE}', '{TAX_RATE_CODE}/{NON_TAX_RSN_CODE}', '{DESCR}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'
, '***Import Notes 01/03/2023 ***' );

todo:
Prices
Apply Product Class Updates
Update Product, Ingredient Types / G/L from product classes.

To update missing products do main query in LQ1 and use:

select LQ1.* from lq1
LEFT OUTER JOIN LQ2 ON LQ2.PRODUCT_CODE = LQ1.PRODUCT_CODE
WHERE LQ2.PRODUCT_CODE IS NULL
LIMIT 5000;
(5000 at a time for processing efficiency)

-- FIX (01/04/2023 - unneeded since reprocessed products)

ALTER TRIGGER argprod_bupdate INACTIVE;
ALTER TRIGGER argprod_Aupdate INACTIVE;

select PRODUCT_CODE,upper(BASE_PRODUCT) BASE_PRODUCT,PLANT_NO from artPROD
WHERE PRODUCT_CODE <> upper(BASE_PRODUCT)||'.'||PLANT_NO
ORDER BY PLANT_NO,PRODUCT_CODE

UPDATE ARTPROD SET PRODUCT_CODE='{BASE_PRODUCT}.{PLANT_NO}' WHERE upper(BASE_PRODUCT) = '{BASE_PRODUCT}' AND PLANT_NO='{PLANT_NO}';

ALTER TRIGGER argprod_bupdate ACTIVE;
ALTER TRIGGER argprod_Aupdate ACTIVE;

=======
=== ARTCTCLS

SELECT ltrim(rtrim(terms_code)) terms_code
,descry
,disc_amt_1
,disc_amt_2
,disc_day_1
,disc_day_2
,due_day_1
,due_day_2
,short_descr
,terms_type
,per_uom
,update_date
FROM CMDPROD_ARK.dbo.trms

INSERT INTO ARTCTCLS
( CLASS_NO, DESCRIPTION, DUE_BY, DUE_BY_PIVOT, DUE_IN, DISC_PCT, DISC_BY, DISC_BY_PIVOT, DISC_IN, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{TERMS_CODE}', '{DESCR}', NULL, NULL, NULL, {DISC_AMT_1}, NULL, NULL, {DISC_DAY_1}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

NOTE: Not 100% clear on how to match days -manually edit.
========
=== ARTALCLS (FROM alt company)
select aa.* ,
a.segment_1,a.segment_2,a.segment_3,a.segment_4
from artalcls aa
left outer join gltacct a on a.gl_account = aa.allow_gl

INSERT INTO ARTALCLS
( CLASS_NO, DESCRIPTION, ALLOW_GL, ALLOW_INCLUDES_TAX, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{CLASS_NO}', '{DESCRIPTION}', '{SEGMENT_1}-00-9999{SEGMENT_4}-{SEGMENT_4}', '{ALLOW_INCLUDES_TAX}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', '{ACTIVE_FLAG}' );

========
=== ARTCUST

select
LTRIM(RTRIM(cust.CUST_CODE)) CUST_NO,
left(ltrim(rtrim(CUST.NAME)),30) name,
left(CUST.ADDR_LINE_1,30) addr_line_1,
left(CUST.ADDR_LINE_2,30) addr_line_2,
left(replace(addr_city,',',''),16) CITY,
replace(LEFT(rtrim(ltrim(ADDR_STATE)),2),'M0','MO') STATE,
PHONE_NUM_1,PHONE_NUM_2,PHONE_NUM_3,
LTRIM(RTRIM(ADDR_POSTCD)) ZIP,cust.tax_code "TAX_AUTHORITY",
--cast(cust.tax_code as integer) txcd_int,
--left(LTRIM(RTRIM(taxj.tax_loc)),3) TAX_AUTHORITY,
LTRIM(RTRIM(CC_TERMS_CODE)) TERMS,
LTRIM(RTRIM(UPPER(CC_SALES_ANL_CODE))) CUCLS,
rtrim(LTRIM(CC_SLSMN_EMPL_CODE)) SLS_EMP,
rtrim(LTRIM(CC_PRICE_CAT)) PRICE_GROUP,
rtrim(LTRIM(CC_PRICE_PLANT_CODE) ) PRICE_PLANT,
CASE TAXBLE_CODE WHEN '1' then '' else CONCAT('TAX EXMPT RSN:', LTRIM(NON_TAX_RSN_CODE) ) END TAX_RSN,
case isnumeric(CC_SALES_ANL_CODE)
WHEN 0 THEN LTRIM(CC_SALES_ANL_CODE) ELSE RIGHT(CAST(100+CAST(CC_SALES_ANL_CODE AS INTEGER) AS CHAR(3)),2) END CUST_CLASS,
rtrim(LTRIM(INACTIVE_CODE)) INACTIVE_CODE,
CASE LTRIM(RTRIM(TAXBLE_CODE)) WHEN 1 THEN 'Y' ELSE 'N' END TAX_CODE,
CONCAT('Acct Cat Code: ',RTRIM(LTRIM(ACCT_CAT_CODE))) USER_2,
LEFT(CONTCT_NAME,30) CONTACT,
LEFT(SORT_NAME,30) SEQUENCE_CODE,
acct_cat_code,SETUP_DATE
from cust
--left outer join taxj on cast(taxj.tax_code as integer)=cast(cust.tax_code as integer)
order by cust_no

INSERT INTO artcust
( CUST_NO, SALESPERSON, CUST_CLASS, TERMS_CLASS, PRICE_GROUP, DIVISION_NO, PHONE_NO, FAX_NO, PAGER_NO, MOBILE_NO, EMAIL, URL, START_DATE, TAX_CODE, TAX_CODE_2, TAX_CODE_3, COD_FLAG, NAME, ADDRESS_1, ADDRESS_2, CITY, STATE, ZIP, USER_1, USER_2, NOTES_1, TAX_AUTHORITY, CONTACT, SEQUENCE_CODE, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{CUST_NO}', (SELECT CLASS_NO FROM ARTSLSPN WHERE EMPLOYEE_NO='{SLS_EMP}') , '{CUCLS}', '{TERMS}', '{PRICE_GROUP}' , NULL, '{PHONE_NUM_1}', NULL, NULL, '{PHONE_NUM_3}', NULL, NULL, '{SETUP_DATE}', '{TAX_CODE}', 'Y', 'Y', 'N', '{NAME|STRLEN=30}', '{ADDR_LINE_1|STRLEN=30}', '{ADDR_LINE_2|STRLEN=30}', '{CITY}', '{STATE}', '{ZIP}', 'Sls Emp: {SLS_EMP|trim=0}', '{USER_2}', '{TAX_RSN}', '{TAX_AUTHORITY}', '{CONTACT}', '{SEQUENCE_CODE}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

=====
== LIST PRICE ==
Price cat 1 as list price (54986 prices are cat 1)

SELECT
concat(upper(rtrim(LTRIM(IPRC.ITEM_CODE))) , '.' , rtrim(ltrim(plnt.plant_code))) "PRODUCT_CODE",
upper(rtrim(LTRIM(IPRC.ITEM_CODE))) BASE_PRODUCT,
rtrim(ltrim(plnt.plant_code)) PLANT_NO,
PRICE UNIT_PRICE,1 CT,
IPRC.*
FROM IPRC
INNER JOIN PLNT ON plnt.LOC_CODE=iprc.LOC_CODE AND ISNUMERIC(PLNT.plant_code)>0
INNER JOIN ILOC ON ILOC.item_code=IPRC.item_code AND ILOC.loc_code=iprc.loc_code
INNER JOIN IMST ON IMST.item_code=ILOC.item_code
WHERE RTRIM(LTRIM(iprc.PRICE_CAT)) = '1' and price <> 0
ORDER BY BASE_PRODUCT,PLANT_NO,PRICE_CAT,effect_date

UpdATE ARTPROD SET list_PRICE={PRICE} WHERE product_code='{BASE_PRODUCT}.{PLANT_NO}';

-- Local to make manageable chunks and update only mismatches...
LQ2:
select lq2.*,LQ1.* from lq1
LEFT OUTER JOIN LQ2 ON LQ2.PRODUCT_CODE = LQ1.PRODUCT_CODE
WHERE LQ2.LIST_PRICE <> LQ1.PRICE

select lq2.*,LQ1.* from lq1
LEFT OUTER JOIN LQ2 ON LQ2.PRODUCT_CODE = LQ1.PRODUCT_CODE
WHERE LQ2.LIST_PRICE <> LQ1.PRICE
limt 10000 offset 20000 -- 20000- 29999

========
=== artprice - group prices plant specific

SELECT
concat(upper(rtrim(LTRIM(IPRC.ITEM_CODE))) , '.' , rtrim(ltrim(plnt.plant_code))) "PRODUCT_CODE",
upper(rtrim(LTRIM(IPRC.ITEM_CODE))) BASE_PRODUCT,
rtrim(ltrim(plnt.plant_code)) PLANT_NO,
rtrim(ltrim(iprc.price_cat)) GROUP_NO,
UPPER(PU.ABBR) UNIT_OF_MEASURE,
PRICE UNIT_PRICE,1 CT,
IPRC.*
FROM IPRC
INNER JOIN PLNT ON plnt.LOC_CODE=iprc.LOC_CODE AND ISNUMERIC(PLNT.plant_code)>0
INNER JOIN ILOC ON ILOC.item_code=IPRC.item_code AND ILOC.loc_code=iprc.loc_code
INNER JOIN IMST ON IMST.item_code=ILOC.item_code
LEFT OUTER JOIN UOMS PU ON PU.UOM=IMST.PRICE_UOM
WHERE RTRIM(LTRIM(iprc.PRICE_CAT)) > '1' and price <> 0
ORDER BY BASE_PRODUCT,PLANT_NO,PRICE_CAT,effect_date

INSERT INTO ARTPRICE
( SESSION_NO, TRANS_NO, PRICE_TYPE, CUST_NO, GROUP_NO, PRICE_CODE, PRODUCT_CODE, UNIT_OF_MEASURE, UNIT_PRICE, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( 1, {GROUP_NO|INC=1}, 'G', NULL,'{GROUP_NO}', 'P', '{BASE_PRODUCT}.{PLANT_NO}', '{UNIT_OF_MEASURE}', {UNIT_PRICE}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

-- 13 records
========

No Customer Price (CPRD)

========
ARTSTR WIP

select -- TOP (100)
1 SESSION_NO,1 TRANS_NO,SORT_LINE_NUM SEQUENCE_CODE,
PLNT.LOC_CODE "LOC_CODE",
(PLNT.PLANT_CODE) PLANT_NO,
LTRIM(RTRIM(ICST.ITEM_CODE)) ASSY_PRODUCT_CODE,
LTRIM(RTRIM(CONST_ITEM_CODE)) COMP_PRODUCT_CODE,
UPPER(LTRIM(CU.ABBR)) COMP_UMS,
QTY QTY_ASSEMBLY, ILC.ITEM_CODE INGITEMLOC ,IMC.ITEM_CODE INGITEM
from icst
LEFT OUTER JOIN UOMS CU ON CU.UOM=ICST.QTY_UOM
INNER JOIN PLNT ON ICST.LOC_CODE=PLNT.LOC_CODE AND ISNUMERIC(PLNT.PLANT_CODE)>0
INNER join iloc on iloc.item_code=icst.item_code AND ILOC.LOC_CODE=ICST.LOC_CODE
inner join imst on imst.item_code=iloc.item_code
LEFT OUTER JOIN ILOC ILC ON ILC.ITEM_CODE=ICST.CONST_ITEM_CODE AND ILC.LOC_CODE=ICST.LOC_CODE
LEFT OUTER JOIN IMST IMC ON IMC.ITEM_CODE=ICST.CONST_ITEM_CODE

INSERT INTO ARTPRSTR
( SESSION_NO, TRANS_NO, SEQUENCE_CODE, ASSY_PRODUCT_CODE, COMP_PRODUCT_CODE, COMP_UMS, QTY_ASSEMBLY, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( {SESSION_NO}, {TRANS_NO|INC=1}, {SEQUENCE_CODE}, '{ASSY_PRODUCT_CODE}.{PLANT_NO}', '{COMP_PRODUCT_CODE}.{PLANT_NO}', '{COMP_UMS}', {QTY_ASSEMBLY}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

note: left outer allows missing items to go through - 5 components for plant 314 added.
(CMD does not have integrity - so structure for invalid products permitted)

======================================================
jobs jobs jobs

Mismatch Items - no ILOC:

select
proj.cust_code
,prjp.proj_code,CUST.NAME "CustName",plnt.plant_code plant,(prjp.prod_code) ProjectProduct
,prjp.price
from prjp
INNER JOIN CUST ON CUST.CUST_CODE=PRjp.CUST_CODE
INNER JOIN PROJ ON PROJ.PROJ_CODE=PRJP.PROJ_CODE AND PROJ.CUST_CODE=PRJP.CUST_CODE
inner join plnt on plnt.plant_code = PROJ.Cc_PRICE_PLANT_CODE
left outer JOIN ILOC ON ILOC.ITEM_CODE=PRJP.PROD_CODE and PLNT.LOC_CODE=ILOC.LOC_CODE
WHERE proj.INACTIVE_CODE=0
AND COALESCE(proj.EXPIR_DATE,'12/31/2099' ) >'1/1/23'
and iloc.item_code is null and price_plant_code <>'#'
order by proj.cust_code,prjp.proj_code,cust.name,proj.expir_date,plnt.plant_code,prjp.prod_code

0== job header query ==

with pj as(
select
proj.cust_code
,prjp.proj_code,CUST.NAME "CustName"
from prjp
INNER JOIN CUST ON CUST.CUST_CODE=PRjp.CUST_CODE
INNER JOIN PROJ ON PROJ.PROJ_CODE=PRJP.PROJ_CODE AND PROJ.CUST_CODE=PRJP.CUST_CODE
--inner join plnt on plnt.plant_code = PROJ.Cc_PRICE_PLANT_CODE
--INNER JOIN ILOC ON ILOC.ITEM_CODE=PRJP.PROD_CODE and PLNT.LOC_CODE=ILOC.LOC_CODE
inner join plnt on plnt.plant_code = PROJ.Cc_PRICE_PLANT_CODE
left outer JOIN ILOC ON ILOC.ITEM_CODE=PRJP.PROD_CODE and PLNT.LOC_CODE=ILOC.LOC_CODE
--left outer join imst on imst.item_cat=prjp.prod_code and price_plant_code='#'
WHERE proj.INACTIVE_CODE=0
AND COALESCE(proj.EXPIR_DATE,'12/31/2099' ) >'1/1/23'
group by prjp.proj_code,proj.cust_code,cust.name,proj.expir_date
)
SELECT
rtrim(LTRIM(PROJ.CUST_CODE)) CUST_NO,PROJ.EXPIR_DATE,
Left(rtrim(LTRIM(PROJ.PROJ_code)),18) JOB_PO_LOC,
PROJ_NAME,PO,
Left(rtrim(LTRIM(PROJ_name)),18) batch_job_code,
rtrim(LTRIM(PROJ_NAME)) DELV_INST_1,
Left(LTRIM(proj.tax_code),3) TAX_code,
--'IMP' TAX_AUTHORITY,
CA_TERMS_CODE,PROJ.CA_PRICE_PLANT_CODE,
LTRIM(CA_TERMS_CODE) TERMS_CLASS,
--LTRIM(CA_PRICE_PLANT_CODE) PLANT_NO,
Left(rtrim(LTRIM(PO)) ,20) CUSTOMER_PO,
LTRIM(UPPER(PROJ.CA_SALES_ANL_CODE)) CUCLS,
'' SHIP_TO_1,
--Left(RTRIM(LTRIM(ship_name)),30) SHIP_TO_1 ,
Left(RTRIM(LTRIM(PROJ.ship_addr_line_1)),30) SHIP_TO_2 ,
Left(RTRIM(LTRIM(ship_addr_line_2)),30) SHIP_TO_3 ,
CASE LTRIM(TAXBLE_CODE) WHEN 1 THEN 'Y' ELSE 'N' END JOB_TAX_CODE,
Left(concat(SHIP_CITY,' ',SHIP_STATE,' ',SHIP_POSTCD),30) SHIP_TO_4,
Taxj.tax_loc "NOTES_1",
PROJ.EXPIR_DATE "NOTES_2",
--USE_FOR_PROD_LINE_CODE "NOTES_2",
LTRIM(NON_TAX_RSN_CODE) "CUSTOM_1",
LTRIM(PROJ.CA_SLSMN_EMPL_CODE) "CUSTOM_2",
Left(RTRIM(LTRIM(CONTCT_NAME)),30) "CONTACT",
Left(RTRIM(LTRIM(PHONE_NUM_1)),30) PHONE_NO,
Left(RTRIM(LTRIM(PROJ_NAME)),30) "INVOICE_DESC"
,ship_addr_line_1,ship_addr_line_2,
PHONE_NUM_1,PHONE_NUM_2,PHONE_NUM_3,CONTct_NAME,custname
FROM proj
inner join pj on pj.proj_code=proj.proj_code and pj.cust_code=proj.cust_code
INNER JOIN TAXJ ON TAXJ.TAX_CODE=proj.TAX_CODE and taxj.tax_auth=1
where inactive_code = 0
and coalesce (PROJ.expir_date,'12/31/2099')>'1/1/2023'
ORDER BY CUST_NO,PROJ_NAME

------

INSERT INTO ARTJOBHD
( SESSION_NO, TRANS_NO, CUST_NO, JOB_PO_LOC, BATCH_JOB_CODE , TERMS_CLASS, TAX_AUTHORITY, INVOICE_DESC, NOTES_1, NOTES_2, JOB_TAX_CODE, JOB_TAX_CODE_2, JOB_TAX_CODE_3, PLANT_NO, SHIP_TO_1, SHIP_TO_2, SHIP_TO_3, SHIP_TO_4, PHONE_NO, CONTACT, CUSTOMER_PO, CUSTOM_1, CUSTOM_2, COD_FLAG, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG, MEMO ) VALUES
( 1, {CUST_NO|INC=1}, '{CUST_NO}', '{JOB_PO_LOC}', '{batch_job_code}', NULL, '{TAX_AUTHORITY}', '{INVOICE_DESC}', '{NOTES_1}', '{NOTES_2}', '{JOB_TAX_CODE}', 'Y', 'Y', NULL, '{SHIP_TO_1}', '{SHIP_TO_2}', '{SHIP_TO_3}', '{SHIP_TO_4}', '{PHONE_NO}', '{CONTACT}', '{CUSTOMER_PO}', '{CUSTOM_1}', '{CUSTOM_2}', 'N', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' ,
'***Import Notes Jan 2023***
PROJ NAME: {PROJ_NAME}
PO: {PO}
CONTACT: {CONTct_NAME}
PHONE1: {phone_num_1}
PHONE2: {phone_num_2}
PHONE3: {phone_num_3}
ship_addr_line_1 {ship_addr_line_1}
ship_addr_line_2 {ship_addr_line_2}
expir_date: {expir_date}
*****************************************
' ); -- {custname}

NOTE: plant ok?
Uses numeric job code as JOB ID -- this should be updated on ib side.

== job detail query ==

WITH JDT AS
(
select --top 100
proj.cust_code,
RTRIM(LTRIM(PRJP.PROJ_CODE)) JOB_PO_LOC,
RTRIM(LTRIM(PRJP.CUST_CODE)) CUST_NO,
RTRIM(LTRIM(PRJP.PROD_CODE)) PRODUCT_CODE,
RTRIM(LTRIM(Cc_PRICE_PLANT_CODE)) PLANT_code,
PRJP.INTRNL_LINE_NUM LINE_NO,
(SELECT UPPER(ABBR) FROM UOMS WHERE UOMS.UOM = PRJP.PRICE_UOM) UNIT_OF_MEASURE,
left(RTRIM(LTRIM(PROD_DESCR)),35) DESCRIPTION,
PRICE UNIT_PRICE,prjp.effect_date
,PRJP.PROD_CODE
,LAG(PROD_CODE,1,'__') OVER (partition by PRJP.CUST_CODE,PRJP.PROJ_CODE,Cc_PRICE_PLANT_CODE
order by PRJP.CUST_CODE,PRJP.PROJ_CODE,PRJP.PROD_CODE,Cc_PRICE_PLANT_CODE,PRJP.EFFECT_DATE DESC) PREVPROD
,iif(iloc.loc_code is null,(select min(loc_code) from iloc where iloc.item_code=imst.item_code and iloc.inactive_code=0),iloc.loc_code) altclocode
from prjp
INNER JOIN PROJ ON PROJ.PROJ_CODE=PRJP.PROJ_CODE AND PROJ.CUST_CODE=PRJP.CUST_CODE
inner join plnt on plnt.plant_code = Cc_PRICE_PLANT_CODE
left outer JOIN ILOC ON ILOC.ITEM_CODE=PRJP.PROD_CODE and PLNT.LOC_CODE=ILOC.LOC_CODE
inner join imst on imst.item_code=prjp.prod_code
WHERE proj.INACTIVE_CODE=0
AND COALESCE(proj.EXPIR_DATE,'12/31/2099' ) >'1/1/23'
)
SELECT JDT.*,plnt.plant_code "PLANT_NO"
FROM JDT
inner join cust on cust.cust_code=jdt.cust_code
inner join plnt on plnt.loc_code=jdt.altclocode AND ISNUMERIC(PLNT.PLANT_CODE)=1
WHERE PREVPROD<>PROD_CODE

INSERT INTO ARTJOBDT
( SESSION_NO, TRANS_NO, JOB_PO_LOC, CUST_NO, PRODUCT_CODE, LINE_NO, UNIT_OF_MEASURE, DESCRIPTION, UNIT_PRICE, TAX_CODE, TAX_CODE_2, TAX_CODE_3, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER ) VALUES
( 1, (SELECT TRANS_NO FROM ARTJOBHD WHERE JOB_PO_LOC='{JOB_PO_LOC}' AND CUST_NO='{CUST_NO}' )
, '{JOB_PO_LOC}', '{CUST_NO}', '{PRODUCT_CODE}.{PLANT_NO}', {LINE_NO}, '{UNIT_OF_MEASURE}', '{DESCRIPTION}', {UNIT_PRICE}, 'Y', 'Y', 'Y', 'Y', CAST('TODAY' AS TIMESTAMP), 'Y' );

NOTES: Not loving the plant mismatches on detail
If primary plant not specified - takes the lowest value.

------
ZONE
------

SELECT * FROM ZONE

INSERT INTO artzone
( ZONE, DESCRIPTION, PLANT_NO, ZONE_REGION, DEF_TAX_AUTHORITY, IMPORTED_FLAG, MEMO, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{ZONE_CODE|TRIM=0}', '{DESCR|STRLEN=35}', NULL, NULL, '{TAX_CODE}', 'Y', 'Imported 01/17/2023', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

--------------
CONTACT
------
SELECT
LTRIM(RTRIM(CCON.CUST_CODE)) ENTITY_ID,
LTRIM(RTRIM(NAME)) NAME,
LTRIM(RTRIM(PHONE_NUM_1)) PHONE_NO,
LTRIM(RTRIM(PHONE_NUM_2)) CELL_NO,
LTRIM(RTRIM(PHONE_NUM_2)) FAX_NO,
LTRIM(RTRIM(JOB_TITLE)) TITLE,
ltrim(rtrim(email_addr)) EMAIL,
* FROM CCON
INNER JOIN CTCT ON CTCT.CONTCT_CODE=CCON.CONTCT_CODE
---
INSERT INTO cctcont
( RECKEY, ENTITY_TYPE, ENTITY_ID, NAME, PHONE_NO, CELL_NO, FAX_NO, TITLE,EMAIL, EMAIL_OPT_1, EMAIL_OPT_2, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( GEN_ID(GEN_CCCONTKEY,1), 'C', '{ENTITY_ID}', '{NAME|STRLEN=30}', '{PHONE_NO|STRLEN=30}', '{CELL_NO|STRLEN=30}', '{FAX_NO|STRLEN=30}', '{TITLE}', '{EMAIL|STRLEN=80}', 'Y', 'Y', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );
---------

contacts from ONBASE...

Lq3:
SELECT
--RTRIM(rd.distprocessnum) dist_proc_num,
--RTRIM(dp.distprocessname) dist_proc_name,
--rd.recipientnum OBRecipientNO,
--RTRIM(rd.distributionflags) dist_flags,
RTRIM(replace(rd.idkeywords,'!','')) 'CustomerNO',
RTRIM(sr.recipientname) 'CustomerName',
RTRIM(sr.email) 'Email',
RTRIM(sr.faxnum) 'FaxNO'
FROM hsi.recipientxdist rd
INNER JOIN hsi.stmtrecipient sr ON rd.recipientnum = sr.recipientnum LEFT OUTER JOIN hsi.distprocess dp ON rd.distprocessnum = dp.distprocessnum
ORDER BY rd.distprocessnum, sr.recipientname, rd.distributionflags
lq2:
select cust_no,name from artcust c

Primary:
select * from lq3
left outer join lq2 on lq2.cust_no=lq3.CustomerNO
where lq2.cust_no is not null
order by cust_no

INSERT INTO cctcont
( RECKEY, ENTITY_TYPE, ENTITY_ID, NAME, PHONE_NO, CELL_NO, FAX_NO, TITLE,EMAIL, EMAIL_OPT_1, EMAIL_OPT_2, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( GEN_ID(GEN_CCCONTKEY,1), 'C', '{CUST_NO}', 'Accounts Payable', null, null, '{FAXNO|STRLEN=30}', null, '{EMAIL|STRLEN=80}', 'Y', 'Y', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

--------
Contacts Merge (If email from cmd and ob - use the cmd)

SELECT ENTITY_ID,UPPER( EMAIL) EMAIL ,COUNT(*),MIN(NAME),MAX(NAME),MIN(RECKEY) ORIGKEY,MAX(RECKEY) NEWKEY FROM CCTCONT
WHERE EMAIL IS NOT NULL
GROUP BY 1,2
HAVING COUNT(*)>1

(export a csv)

update cctcont set email_opt_1='Y' ,email_opt_2='Y' WHERE RECKEY = {ORIGKEY}; /* {EMAIL} */
DELETE FROM CCTCONT WHERE RECKEY={NEWKEY};

----

contacts deleted all where email null and cell_no null

------------ Even MOre Onbase contacts

select lq1.*,lq2.*,1 ct,lq3.contemail from lq2
left outer join lq1 on lq1.cname=lq2.rname
left outer join lq3 on lq3.cust_no=lq1.cust_code and lq2.email=lq3.contemail
where not (cust_code is null);

select ltrim(rtrim(cust_code)) cust_code,upper(name) cname from cust;

select upper(recipientname) rname,upper(ltrim(rtrim(email))) email,1 rcpct from hsi.stmtrecipient
where coalesce(email,'')<>'';

SELECT CUST_NO,c.NAME "CustomerName",
c.CONTACT "CustomerContact",cc.cell_no,cc.phone_no
,CC.NAME "ContName",upper(cc.email) "ContEmail",
email_opt_1 "EmailInv",email_opt_2 "EmailStmt"
FROM ARTCUST C
INNER JOIN CCTCONT CC ON CC.ENTITY_ID=C.CUST_NO
where cc.email is not null
order by c.cust_no,cc.reckey

INSERT INTO cctcont
( RECKEY, ENTITY_TYPE, ENTITY_ID, NAME, PHONE_NO, CELL_NO, FAX_NO, TITLE,EMAIL, EMAIL_OPT_1, EMAIL_OPT_2, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( GEN_ID(GEN_CCCONTKEY,1), 'C', '{CUST_code}', 'Accounts Payable', null, null, null, null, '{EMAIL|STRLEN=80}', 'Y', 'Y', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

----

Update Prods From Class:

SELECT product_code,PRODUCT_CLASS,SELLABLE_FLAG,PRODUCT_TYPE,INGREDIENT_TYPE, (SELECT PC.STD_SELLABLE_FLAG FROM ARTPRCLS PC WHERE PC.CLASS_NO=P.PRODUCT_CLASS) STD_SELLABLE_FLAG,
(SELECT PC.STD_PRODUCT_TYPE FROM ARTPRCLS PC WHERE PC.CLASS_NO=P.PRODUCT_CLASS) STD_PRODUCT_TYPE,
(SELECT PC.STD_ingredient_TYPE FROM ARTPRCLS PC WHERE PC.CLASS_NO=P.PRODUCT_CLASS) STD_ingredient_TYPE
FROM ARTPROD P

update artprod p
SET SELLABLE_FLAG='{STD_SELLABLE_FLAG}',
INGREDIENT_TYPE='{STD_INGREDIENT_TYPE}',
PRODUCT_TYPE='{STD_PRODUCT_TYPE}'
where product_code='{PRODUCT_CODE}';

------
in mssql: {set cust inactive}

select
CONCAT( 'UPDATE ARTCUST C SET ACTIVE_FLAG=''N'' WHERE CUST_NO = ''',RTRIM(LTRIM(CUST_CODE)),''';')
from cust
where inactive_code <> 00

===
Update sales g/l accounts by plant
(Setup Plants with default g/l!!!!)

with ga (act,s1,s2,s3,s4,actnm) as
(select gl_account,segment_1,segment_2,segment_3,segment_4,max(description)
from gltacct a
where segment_4 between '300' and '998'
group by gl_account,segment_1,segment_2,segment_3,segment_4
)
,pr (pact,ps1,ps2,ps3,ps4,plt) as
(select GL_ACCOUNT ,segment_1,segment_2,segment_3,segment_4,p.plant_no from artprod p
inner join gltacct a on a.gl_account=p.sales_gl
group by gl_account,segment_1,segment_2,segment_3,segment_4,plant_no)
select * from ga
inner join pr on pr.ps1=ga.s1 and pr.ps2=ga.s2 and pr.plt=ga.s4
order by s1,s4

update artprod p set sales_gl = '{act}' WHERE sales_gl='{pact}' and plant_no = '{plt}';

NOTE: This could be done more generically by taking part of acct from artprcls and some from the g/l pattern

</pre>

Command Import

2024-02-27T13:25:27Z

Chanson: /* MS SQL to know */

There is not ONE way to import Command Data - each situation is unique.

===Know This First===
What you need to know before importing Command's Data
*There is NO data integrity.
:*Expect sub tables, prices etc. to have records that do have matching main tables. (e.g. prices for customers that don't exist).
*Alpha fields must be trimmed - especially key fields. Expect both left and right padding on Customer and product codes.
:MS SQL hasn't had the "TRIM" statement for long, so most often you must select as:
:<code>LTRIM(RTRIM((PRJP.PROJ_CODE)) JOB_PO_LOC,</code>
:in some cases, such as Oracle you only have trim and must select as:
:<code>TRIM(PRJP.PROJ_CODE) JOB_PO_LOC,</code>
*Numeric Fields may be null. Use coalesce... <code>COALESCE(Finc_Chrg_amt,0) Finc_chrg_amt</code>
*Field IDs are longer - especially Tax Authorities, sales people and classes.
*Keep a list of mappings when setting up the class type fields to use in case statements...
<pre>
case TERMS
WHEN '15N' THEN '15'
WHEN 'N20' THEN '20'
...
WHEN 'CAS' THEN 'CA' ELSE '' END CUST_TERMS</pre>
*Some records, especially prices, have what appear to be duplicates. You need special queries that pull just the latest version of the record.
*Start with Small Tables and Build up (Like any Import)

===MS SQL to know===
[[Microsoft SQL Server Query Features]]
*ltrim,rtrim
*replace
*left, right
*case
*iif
*rank
*with (derived queries)

===Import Examples===

Here are some samples used in the past...
:[[Examples by Table]]
:[[Command Import: Hedger Work Notes]]
:[[Command Import: Rain MO Work Notes]]

===Command Tables===

*'''ACCT:''' GL Accounts
*'''ARTB:''' Aging
*'''CCON:''' Contact Connection
*'''COMP:''' Company
*'''CPRD:''' Customer Prices
*'''CTCT:''' Contact
*'''CUST:''' Customer Prices
*'''EMPL:''' Employee - Sales and Drivers
*'''ICAT:''' Item (Product)
*'''ICST:''' Component Structure
*'''ILOC:''' Product by Location (Similar to Plant)
*'''IMST:''' Products
*'''IPRC:''' Prices - by Level (Group)
*'''LOCN:''' Locations (Similar to Plant)
*'''PLNT:''' Plant
*'''PRCC:''' Price Group
*'''PRJP:''' Project Prices
*'''PROJ:''' Project
*'''SANL:''' Customer Class
*'''TAXC:''' Tax (Similar to Keystone Tax Auth)
*'''TAXJ:''' Tax Structure
*'''TAXL:''' Tax Locality (not 1 to 1 with Keystone)
*'''TRMS:''' Terms
*'''TRUC:''' Trucks
*'''UOMS:''' Unit of Meausure
*'''USGE:''' Useage

Command Import

2024-02-27T13:23:13Z

Chanson: /* MS SQL to know */

There is not ONE way to import Command Data - each situation is unique.

===Know This First===
What you need to know before importing Command's Data
*There is NO data integrity.
:*Expect sub tables, prices etc. to have records that do have matching main tables. (e.g. prices for customers that don't exist).
*Alpha fields must be trimmed - especially key fields. Expect both left and right padding on Customer and product codes.
:MS SQL hasn't had the "TRIM" statement for long, so most often you must select as:
:<code>LTRIM(RTRIM((PRJP.PROJ_CODE)) JOB_PO_LOC,</code>
:in some cases, such as Oracle you only have trim and must select as:
:<code>TRIM(PRJP.PROJ_CODE) JOB_PO_LOC,</code>
*Numeric Fields may be null. Use coalesce... <code>COALESCE(Finc_Chrg_amt,0) Finc_chrg_amt</code>
*Field IDs are longer - especially Tax Authorities, sales people and classes.
*Keep a list of mappings when setting up the class type fields to use in case statements...
<pre>
case TERMS
WHEN '15N' THEN '15'
WHEN 'N20' THEN '20'
...
WHEN 'CAS' THEN 'CA' ELSE '' END CUST_TERMS</pre>
*Some records, especially prices, have what appear to be duplicates. You need special queries that pull just the latest version of the record.
*Start with Small Tables and Build up (Like any Import)

===MS SQL to know===
*ltrim,rtrim
*replace
*left, right
*case
*iif
*rank
*with (derived queries)

===Import Examples===

Here are some samples used in the past...
:[[Examples by Table]]
:[[Command Import: Hedger Work Notes]]
:[[Command Import: Rain MO Work Notes]]

===Command Tables===

*'''ACCT:''' GL Accounts
*'''ARTB:''' Aging
*'''CCON:''' Contact Connection
*'''COMP:''' Company
*'''CPRD:''' Customer Prices
*'''CTCT:''' Contact
*'''CUST:''' Customer Prices
*'''EMPL:''' Employee - Sales and Drivers
*'''ICAT:''' Item (Product)
*'''ICST:''' Component Structure
*'''ILOC:''' Product by Location (Similar to Plant)
*'''IMST:''' Products
*'''IPRC:''' Prices - by Level (Group)
*'''LOCN:''' Locations (Similar to Plant)
*'''PLNT:''' Plant
*'''PRCC:''' Price Group
*'''PRJP:''' Project Prices
*'''PROJ:''' Project
*'''SANL:''' Customer Class
*'''TAXC:''' Tax (Similar to Keystone Tax Auth)
*'''TAXJ:''' Tax Structure
*'''TAXL:''' Tax Locality (not 1 to 1 with Keystone)
*'''TRMS:''' Terms
*'''TRUC:''' Trucks
*'''UOMS:''' Unit of Meausure
*'''USGE:''' Useage

Examples by Table

2024-02-26T16:54:02Z

Chanson: /* Job (proj) */

===GL Accounts===
ACCT is the source in CMD but it's not high quality data.

===Company (Divisions)===
COMP will provide company list. This might be separate databases or separate divisions within a database.

===Plants (Locn/Plnt)===
Locations and plants are similar but different. Multiple plants may have on location.

*LOCN: Locations
*PLNT: Plant

Prices and items are normally setup by location. For example you may have location 01 with plants 1A and 1B. The product tables (IMST/ILOC) would only have a reference to location 01 where Keystone will have 1A and 1B.

===Drivers (empl)===
<pre>
with driv as (
SELECT --the core query
rtrim(ltrim(empl_code)) "DRIVER_CODE",
replace(rtrim(ltrim(NAME)),'''','''''') "DRIVER_NAME",
rtrim(ltrim(empl.phone_num)) Phone_no,
rtrim(ltrim(empl.assgn_plant_code)) Home_plant_no,
--EMPL.*,
''',''' QCQ,''',' q1,'=''' q2 -- these are helpers for the concat query
from empl
where driv_flag = 1
and inactive_code='00'
)
select -- this creates insert from the core query
concat('INSERT INTO artdriv ( DRIVER_CODE, DRIVER_NAME, PHONE_NO, HOME_PLANT_NO, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG,memo ) VALUES
( ''',driver_code,qcq,DRIVER_NAME,qcq,Phone_no,qcq,Home_plant_no,qcq ,'Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y',qcq,'Imported from Cmd'');' ),
driv.* from driv
</pre>

===Salespeople (empl)===
<pre>
with cs as
( select distinct(ltrim(rtrim(cust.cc_slsmn_empl_code))) custslsmn from cust where inactive_code='00' ), -- LIst of slsmn in active customers
slsemp as -- all sls employees
(SELECT
rtrim(ltrim(empl.empl_code)) empno,
rtrim(ltrim(empl.empl_code)) USER_1,
rtrim(LTRIM(empl.NAME)) DESCRIPTION,
rtrim(LTRIM(empl.PHONE_NUM)) PHONE_NO,
rtrim(LTRIM(empl.ASSGN_PLANT_CODE)) USER_2
FROM empl
where slsmn_flag='True')
,slpn as
(
select cs.*,slsemp.*,
case custslsmn
WHEN '182' THEN 'NJ'
WHEN '322' THEN 'RM'
WHEN 'S15' THEN '15'
---...
WHEN 'SALES' THEN 'SA'
else custslsmn
end class_no,''',''' qcq
from cs
INNER join slsemp on slsemp.empno=cs.custslsmn --only include slspn that are in active customers
) select
concat ( 'INSERT INTO artslspn ( CLASS_NO, EMPLOYEE_NO, DESCRIPTION, PHONE_NO, USER_2 ) VALUES (''',class_no,qcq,empno,qcq,DESCRIPTION,qcq,PHONE_NO,qcq,USER_2,''');'),
slpn.* from slpn
order by custslsmn
</pre>

===Taxes===
This is somewhat similar to Keystone setup, but not quite the same. Terminlogy is quite different:
Command - Keystone
Tax Code - Tax Authority
Tax Authority - Tax Level (e.g. 1=state, 2=County, 3=City)

CMD is always setup multi-level whereas Keystone customer often want to flatten the taxes to single level. This is a key up-front question explaining the pros/cons of each direction.

*'''TAXC:''' Tax (Similar to Keystone Tax Auth)
*'''TAXJ:''' Tax Structure
*'''TAXL:''' Tax Locality (not 1 to 1 with Keystone)

====Tax Locality====

<pre>
with ttl (tax_loc,tax_auth,descr,rate) as
(select -- Core query (can be used with QG)
cast(tl.tax_loc as integer),cast(tl.tax_auth as integer),
replace(replace(ltrim(rtrim(descr)),'(City of','('),'( ','('),
max(prim_rate_curr_pct) from taxl tl
inner join taxj tj on tl.tax_loc=tj.tax_loc and tl.tax_auth=tj.tax_auth
group by tl.tax_loc,tl.tax_auth,descr
)
select
concat('INSERT INTO ARTTXLOC
( TAX_LOCALITY, LINE_NO, DESCRIPTION, TAX_COL, LOC_LEVEL, TAX_RATE, CALC_METHOD, EFFECT_DATE, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( ''',100*tax_auth + tax_loc,''', ',tax_auth,',''',left(descr,35),''',1 ,',tax_auth,',',rate,', 1, ''01/01/1900'', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y'' );'),
ttl.* from ttl
order by tax_auth,tax_loc;
</pre>

====Tax Authority====
For the tax authority, we just need the code and the description...
<pre>
with tc as
(select ltrim(rtrim(tax_code)) "tax_code",
replace(replace(ltrim(rtrim(descr)),'(City of','('),'( ','(') "descr"
from taxc)
select
concat('INSERT INTO ARTTXAUT ( TAX_AUTHORITY, DESCRIPTION, INCREASE_DISC_FLAG, DISC_INCLUDES_TAX, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( ''',tax_code,''' , ''',left(descr,35),''', ''N'', ''Y'', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y'' );',''),
tc.* from tc;
</pre>

===Price Group Headers===
You need to import the Price Group (Category in CMD) before customers. Import the actual group prices after products are setup.
'''PRCC'''

Sample query:
<pre>
with aaa (clsno,descr) as
(
select -- core query that can be used with qg
ltrim(rtrim(price_cat)),
ltrim(rtrim(descry)) description
from prcc
)
select -- outer query
concat('INSERT INTO ARTPRGRP ( GROUP_NO, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG) VALUES
( ''',clsno,''',''',left(descr,35),''', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y'' );')
,clsno "group", descry description
from aaa
</pre>

Price Cat codes tend to be short and do not need to be mapped coming into Keystone.

===Dispatch Useage Class===
*'''USGE:''' Useage

===Customer Class (sanl)===
<pre>
with sac (clsno,descr) as
(
select
case ltrim(rtrim(sales_anl_code))
when 'NSF' THEN 'NS'
when 'CNA' THEN 'CN'
when 'C27' THEN '27'
when 'C29' THEN '29'
when 'C34' THEN '34'
when 'C53' THEN '53'
else ltrim(rtrim(sales_anl_code)) end
"class_no",
ltrim(rtrim(descry)) description
from sanl
)
select
concat('
INSERT INTO artcucls
( CLASS_NO, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( ''',clsno,''',''',left(descr,35),''', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y'' );
'),
ltrim(rtrim(clsno)) "class_no",
ltrim(rtrim(descry)) description, *
from sac
</pre>

===Customer===
*'''CUST:''' Customer

State needs to be mapped...
<pre>
select top (10000)
LTRIM(RTRIM(cust.CUST_CODE)) CUST_NO,
RTRIM(replace(left(ltrim(rtrim(CUST.NAME)),30),'''','''''')) name,
replace(left(LTRIM(RTRIM(CUST.ADDR_LINE_1)),30),'''','''''') address_1,
replace(left(LTRIM(RTRIM(CUST.ADDR_LINE_2)),30),'''','''''') address_2,
left(replace(ltrim(rtrim(addr_city)),',',''),16) CITY,
replace(LEFT(rtrim(ltrim(ADDR_STATE)),2),'M0','MO') STATE,
addr_state,
ltrim(rtrim(PHONE_NUM_1)) phone_no,PHONE_NUM_2,PHONE_NUM_3,
LTRIM(RTRIM(ADDR_POSTCD)) ZIP,ltrim(rtrim(cust.tax_code)) "TAX_AUTHORITY",
--cast(cust.tax_code as integer) txcd_int,
--left(LTRIM(RTRIM(taxj.tax_loc)),3) TAX_AUTHORITY,
case ltrim(rtrim(cc_terms_code))
when '10N' THEN '1N'
WHEN '15T' THEN '5T'
WHEN '2.5' THEN '25'
ELSE LTRIM(RTRIM(cc_TERMS_CODE)) END terms_class, --folsom mapping
LTRIM(RTRIM(CC_TERMS_CODE)) TERMS,
LTRIM(RTRIM(UPPER(CC_SALES_ANL_CODE))) CUCLS, -- unmapped
case ltrim(rtrim(cc_sales_anl_code)) --use cust class import mapping
when 'NSF' THEN 'NS'
-- ...
when 'C53' THEN '53'
else ltrim(rtrim(cc_sales_anl_code)) end "cust_class",
rtrim(LTRIM(CC_SLSMN_EMPL_CODE)) SLS_EMP,
case rtrim(LTRIM(CC_SLSMN_EMPL_CODE))
WHEN '182' THEN 'NJ' -- same mapping as slspn import
--...
WHEN 'SALES' THEN 'SA'
else rtrim(LTRIM(CC_SLSMN_EMPL_CODE)) end salesperson,
rtrim(LTRIM(CC_PRICE_CAT)) PRICE_GROUP,
rtrim(LTRIM(CC_PRICE_PLANT_CODE) ) PRICE_PLANT,
CASE TAXBLE_CODE WHEN '1' then '' else CONCAT('TAX EXMPT RSN:', LTRIM(NON_TAX_RSN_CODE) ) END TAX_RSN,
rtrim(LTRIM(INACTIVE_CODE)) INACTIVE_CODE,
iif(rtrim(LTRIM(INACTIVE_CODE))='00','Y','N') active_flag,
CASE LTRIM(RTRIM(TAXBLE_CODE)) WHEN 1 THEN 'Y' ELSE 'N' END TAX_CODE,
CONCAT('Acct Cat Code: ',RTRIM(LTRIM(ACCT_CAT_CODE))) USER_2,
replace(LEFT(rtrim(ltrim(CONTCT_NAME)),30),'''','''''')CONTACT,
LEFT(replace(LTRIM(RTRIM(SORT_NAME)),'''',''),30) SEQUENCE_CODE,
acct_cat_code
--,cast(coalesce(SETUP_DATE,'1/1/1900') as date) start_date
,convert(varchar(10), setup_date, 101) start_date,
ltrim(rtrim(cust.tax_id_code)) Tax_id
from cust
</pre>

===Product Class (icat)===

A good product class table is essential for a Command conversion.

You will mostly likely need to map long item cat codes to product class codes. Instead of making them like the CMD item cat codes, so focus on making a well organized Keystone list and then have the customer help you mapping CMD item categories to Keystone classes.

Separate the sellable ingredient items for admixes, colors etc. from the non-sellable ingredient codes.
When creating product classes, set the defaults for sellable, product type and ingredient type. Then use the following script to update:

<pre>
update artprod p set sellable_flag =(select std_sellable_flag from artprcls c where c.class_no=p.product_class);
update artprod p set product_type =(select std_product_type from artprcls c where c.class_no=p.product_class);
update artprod p set ingredient_type =(select std_ingredient_type from artprcls c where c.class_no=p.product_class);
</pre>

Setup default sales G/L and make sure the plants are properly masked. You can use a variant of this script to update yoru sales g/l accounts...

<pre>
UPDATE ARTPROD P
SET p.SALES_GL=
(select substr(std_sales_gl,1,8) from artprcls pc where pc.class_no=p.product_class)||
(select substr(sales_gl_plant_mask,9,19) from artplant pl where pl.plant_no=p.plant_no)
where (select substr(std_sales_gl,1,8) from artprcls pc where pc.class_no=p.product_class) is not null
and p.product_class IN ('AA','BB','CC') -- Optionally only do this for select classes
</pre>

===Products===
Products are stored in 2 tables, imst and iloc.
*'''IMST:''' Product "Base". One record for base product that stores the category (class) description etc.
*'''ILOC:''' Product by Location

These need to be combined to create keystone products.

===Component Structure (icst)===
CMD dispatch mixes rarely are worth importing. Verify with customer, but expect to import directly from batch controls.

===Credit Terms (TRMS)===
You can import term codes and descriptions, but the meaning does not map from system to system.

<pre>
WITH T AS (
SELECT
case ltrim(rtrim(terms_code)) -- setup mapping for terms longer than 2 char. Save for customer import etc.
when '10N' THEN '1N'
WHEN '15T' THEN '5T'
WHEN '2.5' THEN '25'
ELSE LTRIM(RTRIM(TERMS_CODE)) END CLASS_NO
,LEFT(LTRIM(RTRIM(descr)),30) DESCRIPTION
FROM dbo.trms
)
SELECT
CONCAT('iNSERT INTO ARTCTCLS (CLASS_NO,DESCRIPTION) VALUES (''',CLASS_NO,''',''',DESCRIPTION,''');'),
* FROM T
</pre>

===Trucks===
Trucks

===Unit of Meausure (uoms)===
The key field for Unit of Measure is typically a long integer.

===Customer Prices (CPRD)===

*A plant of # means all plants. On import try to match this to a Keystone Base Price Plant. (If all plants point to 01 - import price to 01 if possible)

===List and Group Prices (IPRC)===
The table IPRC stores Category (cmd)/Group Prices and the equivelent of List prices. Category "#" is represents "List" in Keystone.
*Many times, what appears to be a Group(cat) is in fact used as a list price. You can preview the iprc data to the customer to determine this before the install. You can also import into Keystone and then convert a group to list prices.
*Often, a large amount of junk pricing is in this table. Preview to customer to determine if large amounts of pricing should be omitted.
*Where in Keystone, we often base pricing on X$ off list for a group or customer, in Command each price ends up as a record. If possible, find groups (price cats) that can be skipped and replaced with a price off list. (this may only apply to the mix items in a group/price cat)
*Expect redundant records that tneed to be processed by effect date. (Use of RANK or similar SQL tricks can correct for this)
*Expect Prices for items that may or may not exist.
*A plant of # means all plants. On import try to match this to a Keystone Base Price Plant. (If all plants point to 01 - import price to 01 if possible)

===Jobs===

====Job (proj)====
*Project Code and Name vs JOB PO Loc. Code is very cryptic. Name is very long. Best solution: - BatchJobCode as ProjCode. Import Proj Name into Invoice Desc..Import proj code into JobPoLoc but update with a cleaned up verion of the code.
*Base selection on Expir_date, Setup_date and the inactive_code of projects, customers, iloc and imst.
<pre>
SELECT -- PROJ HEAD
rtrim(LTRIM(PROJ.CUST_CODE)) CUST_NO,
proj.setup_date,
PROJ.EXPIR_DATE,
Left(rtrim(LTRIM(PROJ.PROJ_code)),18) JOB_PO_LOC,
PROJ_NAME,PO,
Left(rtrim(LTRIM(PROJ_name)),18) batch_job_code,
rtrim(LTRIM(PROJ_NAME)) DELV_INST_1,
Left(LTRIM(proj.tax_code),3) TAX_authority,
proj.Cc_TERMS_CODE,PROJ.Cc_PRICE_PLANT_CODE,
LTRIM(proj.Cc_TERMS_CODE) TERMS_CLASS,
rtrim(LTRIM(proj.Cc_PRICE_PLANT_CODE)) PLANT_NO,
Left(rtrim(LTRIM(PO)) ,20) CUSTOMER_PO,
LTRIM(UPPER(PROJ.Cc_SALES_ANL_CODE)) CUCLS,
-- ship_name or ship addr 2 in proj -- if ship_name avail. shift ship to addrs
--Left(RTRIM(LTRIM(ship_name)),30) SHIP_TO_1 ,
Left(RTRIM(LTRIM(PROJ.ship_addr_line_1)),30) SHIP_TO_1 ,
--Left(RTRIM(LTRIM(ship_addr_line_2)),30) SHIP_TO_3 ,
Left(concat(SHIP_CITY,' ',SHIP_STATE,' ',SHIP_POSTCD),30) SHIP_TO_2,
'' ship_to_3,''ship_to_4,
CASE LTRIM(proj.TAXBLE_CODE) WHEN 1 THEN 'Y' ELSE 'N' END JOB_TAX_CODE,
Left(concat(SHIP_CITY,' ',SHIP_STATE,' ',SHIP_POSTCD),30) SHIP_TO_4,
Taxj.tax_loc "NOTES_1",
PROJ.EXPIR_DATE "NOTES_2",
LTRIM(PROJ.Cc_SLSMN_EMPL_CODE) "CUSTOM_2",
case proj.cc_slsmn_empl_code --slspn mapping
WHEN '182' THEN 'NJ'
-- ...
WHEN 'S16' THEN '16'
WHEN 'SALES' THEN 'SA'
else null
end salesperson,
case proj.inactive_code when '00' then 'Y' ELSE 'N' END "JOB_ACTIVE_FLAG",
Left(RTRIM(LTRIM(proj.CONTCT_NAME)),30) "CONTACT",
Left(RTRIM(LTRIM(proj.PHONE_NUM_1)),30) PHONE_NO,
Left(RTRIM(LTRIM(PROJ_NAME)),30) "INVOICE_DESC"
,ship_name,ship_addr_line_1,ship_addr_line_2,
proj.PHONE_NUM_1,proj.PHONE_NUM_2,proj.PHONE_NUM_3,proj.Contct_NAME
,Ltrim(rtrim(PRJo.state_job_code)) state_job_code -- special case e.g. California AB219
FROM proj
inner join cust on cust.cust_code=proj.cust_code
INNER JOIN TAXJ ON TAXJ.TAX_CODE=proj.TAX_CODE and taxj.tax_auth=1
INNER JOIN PRJO ON PRJO.PROJ_CODE=PROJ.proj_code
where proj.inactive_code = '00' -- only active projects
and cust.inactive_code='00' -- only active custs
--and coalesce (PROJ.expir_date,'12/31/2099')>'1/1/2024' --(This would include jobs with no expir_date)
and (proj.expir_date > '1/1/2024'
or proj.setup_date > '5/1/23') -- includes all jobs starting after specified date
</pre>

====Job Detail (prjp)====
*Tie this selection to the core query used for PROJ
*Plant # means all plants. On import try to match this to a Keystone Base Price Plant. (If all plants point to 01 - import price to 01 if possible)
*Plant number not cleanly in detail table. You can attempt to use the plant info in the project table. But then the next problem...
*The products from job detail will have mismatches with imported products. You will see prices for product (base) and plant combos that don't exist and thus are not imported into Keystone. Either these get skipped or you need clever rerouting selection queries to find valid product.
*Multiple price records per Customer/Project/Product. You need to use special query features such as rank to sort this out.

====Price Escalation (ppre)====
*Price Escalation - the price in prjp may not apply if the customer uses escalation.

===Conctacts===
*'''CCON:''' Contact Connection
*'''CTCT:''' Contact

===Zone===
<pre>
SELECT
CONCAT('iNSERT INTO ARTzone (zone,DESCRIPTION,def_tax_authority) VALUES (''',rtrim(ltrim(zone_code)),
''',''',left(rtrim(ltrim(DESCR)),35),
''',''',rtrim(ltrim(tax_code)),''');'),
zone.*
FROM ZONE
</pre>

===A/R Invoice===
AR may or may not come from Command system. This needs to be researched.
*Invoice Numbers may be numeric or Alpha. When Alpha - Invoice Number=0, JobPoLoc = Alpha Inovice Number.
*Create Preview outputs - DataScopeX is good for this.
*Establish a Hard Cutoff Date

ARTB
:*Curr_Bal_amt key field

Examples by Table

2024-02-26T14:44:07Z

Chanson: /* Customer */

===GL Accounts===
ACCT is the source in CMD but it's not high quality data.

===Company (Divisions)===
COMP will provide company list. This might be separate databases or separate divisions within a database.

===Plants (Locn/Plnt)===
Locations and plants are similar but different. Multiple plants may have on location.

*LOCN: Locations
*PLNT: Plant

Prices and items are normally setup by location. For example you may have location 01 with plants 1A and 1B. The product tables (IMST/ILOC) would only have a reference to location 01 where Keystone will have 1A and 1B.

===Drivers (empl)===
<pre>
with driv as (
SELECT --the core query
rtrim(ltrim(empl_code)) "DRIVER_CODE",
replace(rtrim(ltrim(NAME)),'''','''''') "DRIVER_NAME",
rtrim(ltrim(empl.phone_num)) Phone_no,
rtrim(ltrim(empl.assgn_plant_code)) Home_plant_no,
--EMPL.*,
''',''' QCQ,''',' q1,'=''' q2 -- these are helpers for the concat query
from empl
where driv_flag = 1
and inactive_code='00'
)
select -- this creates insert from the core query
concat('INSERT INTO artdriv ( DRIVER_CODE, DRIVER_NAME, PHONE_NO, HOME_PLANT_NO, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG,memo ) VALUES
( ''',driver_code,qcq,DRIVER_NAME,qcq,Phone_no,qcq,Home_plant_no,qcq ,'Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y',qcq,'Imported from Cmd'');' ),
driv.* from driv
</pre>

===Salespeople (empl)===
<pre>
with cs as
( select distinct(ltrim(rtrim(cust.cc_slsmn_empl_code))) custslsmn from cust where inactive_code='00' ), -- LIst of slsmn in active customers
slsemp as -- all sls employees
(SELECT
rtrim(ltrim(empl.empl_code)) empno,
rtrim(ltrim(empl.empl_code)) USER_1,
rtrim(LTRIM(empl.NAME)) DESCRIPTION,
rtrim(LTRIM(empl.PHONE_NUM)) PHONE_NO,
rtrim(LTRIM(empl.ASSGN_PLANT_CODE)) USER_2
FROM empl
where slsmn_flag='True')
,slpn as
(
select cs.*,slsemp.*,
case custslsmn
WHEN '182' THEN 'NJ'
WHEN '322' THEN 'RM'
WHEN 'S15' THEN '15'
---...
WHEN 'SALES' THEN 'SA'
else custslsmn
end class_no,''',''' qcq
from cs
INNER join slsemp on slsemp.empno=cs.custslsmn --only include slspn that are in active customers
) select
concat ( 'INSERT INTO artslspn ( CLASS_NO, EMPLOYEE_NO, DESCRIPTION, PHONE_NO, USER_2 ) VALUES (''',class_no,qcq,empno,qcq,DESCRIPTION,qcq,PHONE_NO,qcq,USER_2,''');'),
slpn.* from slpn
order by custslsmn
</pre>

===Taxes===
This is somewhat similar to Keystone setup, but not quite the same. Terminlogy is quite different:
Command - Keystone
Tax Code - Tax Authority
Tax Authority - Tax Level (e.g. 1=state, 2=County, 3=City)

CMD is always setup multi-level whereas Keystone customer often want to flatten the taxes to single level. This is a key up-front question explaining the pros/cons of each direction.

*'''TAXC:''' Tax (Similar to Keystone Tax Auth)
*'''TAXJ:''' Tax Structure
*'''TAXL:''' Tax Locality (not 1 to 1 with Keystone)

====Tax Locality====

<pre>
with ttl (tax_loc,tax_auth,descr,rate) as
(select -- Core query (can be used with QG)
cast(tl.tax_loc as integer),cast(tl.tax_auth as integer),
replace(replace(ltrim(rtrim(descr)),'(City of','('),'( ','('),
max(prim_rate_curr_pct) from taxl tl
inner join taxj tj on tl.tax_loc=tj.tax_loc and tl.tax_auth=tj.tax_auth
group by tl.tax_loc,tl.tax_auth,descr
)
select
concat('INSERT INTO ARTTXLOC
( TAX_LOCALITY, LINE_NO, DESCRIPTION, TAX_COL, LOC_LEVEL, TAX_RATE, CALC_METHOD, EFFECT_DATE, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( ''',100*tax_auth + tax_loc,''', ',tax_auth,',''',left(descr,35),''',1 ,',tax_auth,',',rate,', 1, ''01/01/1900'', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y'' );'),
ttl.* from ttl
order by tax_auth,tax_loc;
</pre>

====Tax Authority====
For the tax authority, we just need the code and the description...
<pre>
with tc as
(select ltrim(rtrim(tax_code)) "tax_code",
replace(replace(ltrim(rtrim(descr)),'(City of','('),'( ','(') "descr"
from taxc)
select
concat('INSERT INTO ARTTXAUT ( TAX_AUTHORITY, DESCRIPTION, INCREASE_DISC_FLAG, DISC_INCLUDES_TAX, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( ''',tax_code,''' , ''',left(descr,35),''', ''N'', ''Y'', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y'' );',''),
tc.* from tc;
</pre>

===Price Group Headers===
You need to import the Price Group (Category in CMD) before customers. Import the actual group prices after products are setup.
'''PRCC'''

Sample query:
<pre>
with aaa (clsno,descr) as
(
select -- core query that can be used with qg
ltrim(rtrim(price_cat)),
ltrim(rtrim(descry)) description
from prcc
)
select -- outer query
concat('INSERT INTO ARTPRGRP ( GROUP_NO, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG) VALUES
( ''',clsno,''',''',left(descr,35),''', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y'' );')
,clsno "group", descry description
from aaa
</pre>

Price Cat codes tend to be short and do not need to be mapped coming into Keystone.

===Dispatch Useage Class===
*'''USGE:''' Useage

===Customer Class (sanl)===
<pre>
with sac (clsno,descr) as
(
select
case ltrim(rtrim(sales_anl_code))
when 'NSF' THEN 'NS'
when 'CNA' THEN 'CN'
when 'C27' THEN '27'
when 'C29' THEN '29'
when 'C34' THEN '34'
when 'C53' THEN '53'
else ltrim(rtrim(sales_anl_code)) end
"class_no",
ltrim(rtrim(descry)) description
from sanl
)
select
concat('
INSERT INTO artcucls
( CLASS_NO, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( ''',clsno,''',''',left(descr,35),''', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y'' );
'),
ltrim(rtrim(clsno)) "class_no",
ltrim(rtrim(descry)) description, *
from sac
</pre>

===Customer===
*'''CUST:''' Customer

State needs to be mapped...
<pre>
select top (10000)
LTRIM(RTRIM(cust.CUST_CODE)) CUST_NO,
RTRIM(replace(left(ltrim(rtrim(CUST.NAME)),30),'''','''''')) name,
replace(left(LTRIM(RTRIM(CUST.ADDR_LINE_1)),30),'''','''''') address_1,
replace(left(LTRIM(RTRIM(CUST.ADDR_LINE_2)),30),'''','''''') address_2,
left(replace(ltrim(rtrim(addr_city)),',',''),16) CITY,
replace(LEFT(rtrim(ltrim(ADDR_STATE)),2),'M0','MO') STATE,
addr_state,
ltrim(rtrim(PHONE_NUM_1)) phone_no,PHONE_NUM_2,PHONE_NUM_3,
LTRIM(RTRIM(ADDR_POSTCD)) ZIP,ltrim(rtrim(cust.tax_code)) "TAX_AUTHORITY",
--cast(cust.tax_code as integer) txcd_int,
--left(LTRIM(RTRIM(taxj.tax_loc)),3) TAX_AUTHORITY,
case ltrim(rtrim(cc_terms_code))
when '10N' THEN '1N'
WHEN '15T' THEN '5T'
WHEN '2.5' THEN '25'
ELSE LTRIM(RTRIM(cc_TERMS_CODE)) END terms_class, --folsom mapping
LTRIM(RTRIM(CC_TERMS_CODE)) TERMS,
LTRIM(RTRIM(UPPER(CC_SALES_ANL_CODE))) CUCLS, -- unmapped
case ltrim(rtrim(cc_sales_anl_code)) --use cust class import mapping
when 'NSF' THEN 'NS'
-- ...
when 'C53' THEN '53'
else ltrim(rtrim(cc_sales_anl_code)) end "cust_class",
rtrim(LTRIM(CC_SLSMN_EMPL_CODE)) SLS_EMP,
case rtrim(LTRIM(CC_SLSMN_EMPL_CODE))
WHEN '182' THEN 'NJ' -- same mapping as slspn import
--...
WHEN 'SALES' THEN 'SA'
else rtrim(LTRIM(CC_SLSMN_EMPL_CODE)) end salesperson,
rtrim(LTRIM(CC_PRICE_CAT)) PRICE_GROUP,
rtrim(LTRIM(CC_PRICE_PLANT_CODE) ) PRICE_PLANT,
CASE TAXBLE_CODE WHEN '1' then '' else CONCAT('TAX EXMPT RSN:', LTRIM(NON_TAX_RSN_CODE) ) END TAX_RSN,
rtrim(LTRIM(INACTIVE_CODE)) INACTIVE_CODE,
iif(rtrim(LTRIM(INACTIVE_CODE))='00','Y','N') active_flag,
CASE LTRIM(RTRIM(TAXBLE_CODE)) WHEN 1 THEN 'Y' ELSE 'N' END TAX_CODE,
CONCAT('Acct Cat Code: ',RTRIM(LTRIM(ACCT_CAT_CODE))) USER_2,
replace(LEFT(rtrim(ltrim(CONTCT_NAME)),30),'''','''''')CONTACT,
LEFT(replace(LTRIM(RTRIM(SORT_NAME)),'''',''),30) SEQUENCE_CODE,
acct_cat_code
--,cast(coalesce(SETUP_DATE,'1/1/1900') as date) start_date
,convert(varchar(10), setup_date, 101) start_date,
ltrim(rtrim(cust.tax_id_code)) Tax_id
from cust
</pre>

===Product Class (icat)===

A good product class table is essential for a Command conversion.

You will mostly likely need to map long item cat codes to product class codes. Instead of making them like the CMD item cat codes, so focus on making a well organized Keystone list and then have the customer help you mapping CMD item categories to Keystone classes.

Separate the sellable ingredient items for admixes, colors etc. from the non-sellable ingredient codes.
When creating product classes, set the defaults for sellable, product type and ingredient type. Then use the following script to update:

<pre>
update artprod p set sellable_flag =(select std_sellable_flag from artprcls c where c.class_no=p.product_class);
update artprod p set product_type =(select std_product_type from artprcls c where c.class_no=p.product_class);
update artprod p set ingredient_type =(select std_ingredient_type from artprcls c where c.class_no=p.product_class);
</pre>

Setup default sales G/L and make sure the plants are properly masked. You can use a variant of this script to update yoru sales g/l accounts...

<pre>
UPDATE ARTPROD P
SET p.SALES_GL=
(select substr(std_sales_gl,1,8) from artprcls pc where pc.class_no=p.product_class)||
(select substr(sales_gl_plant_mask,9,19) from artplant pl where pl.plant_no=p.plant_no)
where (select substr(std_sales_gl,1,8) from artprcls pc where pc.class_no=p.product_class) is not null
and p.product_class IN ('AA','BB','CC') -- Optionally only do this for select classes
</pre>

===Products===
Products are stored in 2 tables, imst and iloc.
*'''IMST:''' Product "Base". One record for base product that stores the category (class) description etc.
*'''ILOC:''' Product by Location

These need to be combined to create keystone products.

===Component Structure (icst)===
CMD dispatch mixes rarely are worth importing. Verify with customer, but expect to import directly from batch controls.

===Credit Terms (TRMS)===
You can import term codes and descriptions, but the meaning does not map from system to system.

<pre>
WITH T AS (
SELECT
case ltrim(rtrim(terms_code)) -- setup mapping for terms longer than 2 char. Save for customer import etc.
when '10N' THEN '1N'
WHEN '15T' THEN '5T'
WHEN '2.5' THEN '25'
ELSE LTRIM(RTRIM(TERMS_CODE)) END CLASS_NO
,LEFT(LTRIM(RTRIM(descr)),30) DESCRIPTION
FROM dbo.trms
)
SELECT
CONCAT('iNSERT INTO ARTCTCLS (CLASS_NO,DESCRIPTION) VALUES (''',CLASS_NO,''',''',DESCRIPTION,''');'),
* FROM T
</pre>

===Trucks===
Trucks

===Unit of Meausure (uoms)===
The key field for Unit of Measure is typically a long integer.

===Customer Prices (CPRD)===

*A plant of # means all plants. On import try to match this to a Keystone Base Price Plant. (If all plants point to 01 - import price to 01 if possible)

===List and Group Prices (IPRC)===
The table IPRC stores Category (cmd)/Group Prices and the equivelent of List prices. Category "#" is represents "List" in Keystone.
*Many times, what appears to be a Group(cat) is in fact used as a list price. You can preview the iprc data to the customer to determine this before the install. You can also import into Keystone and then convert a group to list prices.
*Often, a large amount of junk pricing is in this table. Preview to customer to determine if large amounts of pricing should be omitted.
*Where in Keystone, we often base pricing on X$ off list for a group or customer, in Command each price ends up as a record. If possible, find groups (price cats) that can be skipped and replaced with a price off list. (this may only apply to the mix items in a group/price cat)
*Expect redundant records that tneed to be processed by effect date. (Use of RANK or similar SQL tricks can correct for this)
*Expect Prices for items that may or may not exist.
*A plant of # means all plants. On import try to match this to a Keystone Base Price Plant. (If all plants point to 01 - import price to 01 if possible)

===Jobs===

====Job (proj)====
*Project Code and Name vs JOB PO Loc. Code is very cryptic. Name is very long. Best solution: - BatchJobCode as ProjCode. Import Proj Name into Invoice Desc..Import proj code into JobPoLoc but update with a cleaned up verion of the code.
*Base selection on Expir_date, Setup_date and the inactive_code of projects, customers, iloc and imst.

====Job Detail (prjp)====
*Tie this selection to the core query used for PROJ
*Plant # means all plants. On import try to match this to a Keystone Base Price Plant. (If all plants point to 01 - import price to 01 if possible)
*Plant number not cleanly in detail table. You can attempt to use the plant info in the project table. But then the next problem...
*The products from job detail will have mismatches with imported products. You will see prices for product (base) and plant combos that don't exist and thus are not imported into Keystone. Either these get skipped or you need clever rerouting selection queries to find valid product.
*Multiple price records per Customer/Project/Product. You need to use special query features such as rank to sort this out.

====Price Escalation (ppre)====
*Price Escalation - the price in prjp may not apply if the customer uses escalation.

===Conctacts===
*'''CCON:''' Contact Connection
*'''CTCT:''' Contact

===Zone===
<pre>
SELECT
CONCAT('iNSERT INTO ARTzone (zone,DESCRIPTION,def_tax_authority) VALUES (''',rtrim(ltrim(zone_code)),
''',''',left(rtrim(ltrim(DESCR)),35),
''',''',rtrim(ltrim(tax_code)),''');'),
zone.*
FROM ZONE
</pre>

===A/R Invoice===
AR may or may not come from Command system. This needs to be researched.
*Invoice Numbers may be numeric or Alpha. When Alpha - Invoice Number=0, JobPoLoc = Alpha Inovice Number.
*Create Preview outputs - DataScopeX is good for this.
*Establish a Hard Cutoff Date

ARTB
:*Curr_Bal_amt key field

Examples by Table

2024-02-26T13:58:24Z

Chanson: /* Customer */

===GL Accounts===
ACCT is the source in CMD but it's not high quality data.

===Company (Divisions)===
COMP will provide company list. This might be separate databases or separate divisions within a database.

===Plants (Locn/Plnt)===
Locations and plants are similar but different. Multiple plants may have on location.

*LOCN: Locations
*PLNT: Plant

Prices and items are normally setup by location. For example you may have location 01 with plants 1A and 1B. The product tables (IMST/ILOC) would only have a reference to location 01 where Keystone will have 1A and 1B.

===Drivers (empl)===
<pre>
with driv as (
SELECT --the core query
rtrim(ltrim(empl_code)) "DRIVER_CODE",
replace(rtrim(ltrim(NAME)),'''','''''') "DRIVER_NAME",
rtrim(ltrim(empl.phone_num)) Phone_no,
rtrim(ltrim(empl.assgn_plant_code)) Home_plant_no,
--EMPL.*,
''',''' QCQ,''',' q1,'=''' q2 -- these are helpers for the concat query
from empl
where driv_flag = 1
and inactive_code='00'
)
select -- this creates insert from the core query
concat('INSERT INTO artdriv ( DRIVER_CODE, DRIVER_NAME, PHONE_NO, HOME_PLANT_NO, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG,memo ) VALUES
( ''',driver_code,qcq,DRIVER_NAME,qcq,Phone_no,qcq,Home_plant_no,qcq ,'Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y',qcq,'Imported from Cmd'');' ),
driv.* from driv
</pre>

===Salespeople (empl)===
<pre>
with cs as
( select distinct(ltrim(rtrim(cust.cc_slsmn_empl_code))) custslsmn from cust where inactive_code='00' ), -- LIst of slsmn in active customers
slsemp as -- all sls employees
(SELECT
rtrim(ltrim(empl.empl_code)) empno,
rtrim(ltrim(empl.empl_code)) USER_1,
rtrim(LTRIM(empl.NAME)) DESCRIPTION,
rtrim(LTRIM(empl.PHONE_NUM)) PHONE_NO,
rtrim(LTRIM(empl.ASSGN_PLANT_CODE)) USER_2
FROM empl
where slsmn_flag='True')
,slpn as
(
select cs.*,slsemp.*,
case custslsmn
WHEN '182' THEN 'NJ'
WHEN '322' THEN 'RM'
WHEN 'S15' THEN '15'
---...
WHEN 'SALES' THEN 'SA'
else custslsmn
end class_no,''',''' qcq
from cs
INNER join slsemp on slsemp.empno=cs.custslsmn --only include slspn that are in active customers
) select
concat ( 'INSERT INTO artslspn ( CLASS_NO, EMPLOYEE_NO, DESCRIPTION, PHONE_NO, USER_2 ) VALUES (''',class_no,qcq,empno,qcq,DESCRIPTION,qcq,PHONE_NO,qcq,USER_2,''');'),
slpn.* from slpn
order by custslsmn
</pre>

===Taxes===
This is somewhat similar to Keystone setup, but not quite the same. Terminlogy is quite different:
Command - Keystone
Tax Code - Tax Authority
Tax Authority - Tax Level (e.g. 1=state, 2=County, 3=City)

CMD is always setup multi-level whereas Keystone customer often want to flatten the taxes to single level. This is a key up-front question explaining the pros/cons of each direction.

*'''TAXC:''' Tax (Similar to Keystone Tax Auth)
*'''TAXJ:''' Tax Structure
*'''TAXL:''' Tax Locality (not 1 to 1 with Keystone)

====Tax Locality====

<pre>
with ttl (tax_loc,tax_auth,descr,rate) as
(select -- Core query (can be used with QG)
cast(tl.tax_loc as integer),cast(tl.tax_auth as integer),
replace(replace(ltrim(rtrim(descr)),'(City of','('),'( ','('),
max(prim_rate_curr_pct) from taxl tl
inner join taxj tj on tl.tax_loc=tj.tax_loc and tl.tax_auth=tj.tax_auth
group by tl.tax_loc,tl.tax_auth,descr
)
select
concat('INSERT INTO ARTTXLOC
( TAX_LOCALITY, LINE_NO, DESCRIPTION, TAX_COL, LOC_LEVEL, TAX_RATE, CALC_METHOD, EFFECT_DATE, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( ''',100*tax_auth + tax_loc,''', ',tax_auth,',''',left(descr,35),''',1 ,',tax_auth,',',rate,', 1, ''01/01/1900'', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y'' );'),
ttl.* from ttl
order by tax_auth,tax_loc;
</pre>

====Tax Authority====
For the tax authority, we just need the code and the description...
<pre>
with tc as
(select ltrim(rtrim(tax_code)) "tax_code",
replace(replace(ltrim(rtrim(descr)),'(City of','('),'( ','(') "descr"
from taxc)
select
concat('INSERT INTO ARTTXAUT ( TAX_AUTHORITY, DESCRIPTION, INCREASE_DISC_FLAG, DISC_INCLUDES_TAX, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( ''',tax_code,''' , ''',left(descr,35),''', ''N'', ''Y'', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y'' );',''),
tc.* from tc;
</pre>

===Price Group Headers===
You need to import the Price Group (Category in CMD) before customers. Import the actual group prices after products are setup.
'''PRCC'''

Sample query:
<pre>
with aaa (clsno,descr) as
(
select -- core query that can be used with qg
ltrim(rtrim(price_cat)),
ltrim(rtrim(descry)) description
from prcc
)
select -- outer query
concat('INSERT INTO ARTPRGRP ( GROUP_NO, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG) VALUES
( ''',clsno,''',''',left(descr,35),''', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y'' );')
,clsno "group", descry description
from aaa
</pre>

Price Cat codes tend to be short and do not need to be mapped coming into Keystone.

===Dispatch Useage Class===
*'''USGE:''' Useage

===Customer Class (sanl)===
<pre>
with sac (clsno,descr) as
(
select
case ltrim(rtrim(sales_anl_code))
when 'NSF' THEN 'NS'
when 'CNA' THEN 'CN'
when 'C27' THEN '27'
when 'C29' THEN '29'
when 'C34' THEN '34'
when 'C53' THEN '53'
else ltrim(rtrim(sales_anl_code)) end
"class_no",
ltrim(rtrim(descry)) description
from sanl
)
select
concat('
INSERT INTO artcucls
( CLASS_NO, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( ''',clsno,''',''',left(descr,35),''', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y'' );
'),
ltrim(rtrim(clsno)) "class_no",
ltrim(rtrim(descry)) description, *
from sac
</pre>

===Customer===
*'''CUST:''' Customer

State needs to be mapped...
<pre>
CASE REPLACE(UPPER(TRIM(ADDR_STATE)),' ','')
WHEN 'NEBRASK' THEN 'NE'
WHEN 'NEWYORK' THEN 'NY'
WHEN 'NEWJERSEY' THEN 'NJ'
...
WHEN 'PENNSYLVAN' THEN 'PA'
WHEN 'SOUTHCARO' THEN 'SC'
WHEN 'TENNESSEE' THEN 'TN'
ELSE SUBSTR(UPPER(TRIM(ADDR_STATE)),1,2) END STATE,
</pre>

===Product Class (icat)===

A good product class table is essential for a Command conversion.

You will mostly likely need to map long item cat codes to product class codes. Instead of making them like the CMD item cat codes, so focus on making a well organized Keystone list and then have the customer help you mapping CMD item categories to Keystone classes.

Separate the sellable ingredient items for admixes, colors etc. from the non-sellable ingredient codes.
When creating product classes, set the defaults for sellable, product type and ingredient type. Then use the following script to update:

<pre>
update artprod p set sellable_flag =(select std_sellable_flag from artprcls c where c.class_no=p.product_class);
update artprod p set product_type =(select std_product_type from artprcls c where c.class_no=p.product_class);
update artprod p set ingredient_type =(select std_ingredient_type from artprcls c where c.class_no=p.product_class);
</pre>

Setup default sales G/L and make sure the plants are properly masked. You can use a variant of this script to update yoru sales g/l accounts...

<pre>
UPDATE ARTPROD P
SET p.SALES_GL=
(select substr(std_sales_gl,1,8) from artprcls pc where pc.class_no=p.product_class)||
(select substr(sales_gl_plant_mask,9,19) from artplant pl where pl.plant_no=p.plant_no)
where (select substr(std_sales_gl,1,8) from artprcls pc where pc.class_no=p.product_class) is not null
and p.product_class IN ('AA','BB','CC') -- Optionally only do this for select classes
</pre>

===Products===
Products are stored in 2 tables, imst and iloc.
*'''IMST:''' Product "Base". One record for base product that stores the category (class) description etc.
*'''ILOC:''' Product by Location

These need to be combined to create keystone products.

===Component Structure (icst)===
CMD dispatch mixes rarely are worth importing. Verify with customer, but expect to import directly from batch controls.

===Credit Terms (TRMS)===
You can import term codes and descriptions, but the meaning does not map from system to system.

<pre>
WITH T AS (
SELECT
case ltrim(rtrim(terms_code)) -- setup mapping for terms longer than 2 char. Save for customer import etc.
when '10N' THEN '1N'
WHEN '15T' THEN '5T'
WHEN '2.5' THEN '25'
ELSE LTRIM(RTRIM(TERMS_CODE)) END CLASS_NO
,LEFT(LTRIM(RTRIM(descr)),30) DESCRIPTION
FROM dbo.trms
)
SELECT
CONCAT('iNSERT INTO ARTCTCLS (CLASS_NO,DESCRIPTION) VALUES (''',CLASS_NO,''',''',DESCRIPTION,''');'),
* FROM T
</pre>

===Trucks===
Trucks

===Unit of Meausure (uoms)===
The key field for Unit of Measure is typically a long integer.

===Customer Prices (CPRD)===

*A plant of # means all plants. On import try to match this to a Keystone Base Price Plant. (If all plants point to 01 - import price to 01 if possible)

===List and Group Prices (IPRC)===
The table IPRC stores Category (cmd)/Group Prices and the equivelent of List prices. Category "#" is represents "List" in Keystone.
*Many times, what appears to be a Group(cat) is in fact used as a list price. You can preview the iprc data to the customer to determine this before the install. You can also import into Keystone and then convert a group to list prices.
*Often, a large amount of junk pricing is in this table. Preview to customer to determine if large amounts of pricing should be omitted.
*Where in Keystone, we often base pricing on X$ off list for a group or customer, in Command each price ends up as a record. If possible, find groups (price cats) that can be skipped and replaced with a price off list. (this may only apply to the mix items in a group/price cat)
*Expect redundant records that tneed to be processed by effect date. (Use of RANK or similar SQL tricks can correct for this)
*Expect Prices for items that may or may not exist.
*A plant of # means all plants. On import try to match this to a Keystone Base Price Plant. (If all plants point to 01 - import price to 01 if possible)

===Jobs===

====Job (proj)====
*Project Code and Name vs JOB PO Loc. Code is very cryptic. Name is very long. Best solution: - BatchJobCode as ProjCode. Import Proj Name into Invoice Desc..Import proj code into JobPoLoc but update with a cleaned up verion of the code.
*Base selection on Expir_date, Setup_date and the inactive_code of projects, customers, iloc and imst.

====Job Detail (prjp)====
*Tie this selection to the core query used for PROJ
*Plant # means all plants. On import try to match this to a Keystone Base Price Plant. (If all plants point to 01 - import price to 01 if possible)
*Plant number not cleanly in detail table. You can attempt to use the plant info in the project table. But then the next problem...
*The products from job detail will have mismatches with imported products. You will see prices for product (base) and plant combos that don't exist and thus are not imported into Keystone. Either these get skipped or you need clever rerouting selection queries to find valid product.
*Multiple price records per Customer/Project/Product. You need to use special query features such as rank to sort this out.

====Price Escalation (ppre)====
*Price Escalation - the price in prjp may not apply if the customer uses escalation.

===Conctacts===
*'''CCON:''' Contact Connection
*'''CTCT:''' Contact

===Zone===
<pre>
SELECT
CONCAT('iNSERT INTO ARTzone (zone,DESCRIPTION,def_tax_authority) VALUES (''',rtrim(ltrim(zone_code)),
''',''',left(rtrim(ltrim(DESCR)),35),
''',''',rtrim(ltrim(tax_code)),''');'),
zone.*
FROM ZONE
</pre>

===A/R Invoice===
AR may or may not come from Command system. This needs to be researched.
*Invoice Numbers may be numeric or Alpha. When Alpha - Invoice Number=0, JobPoLoc = Alpha Inovice Number.
*Create Preview outputs - DataScopeX is good for this.
*Establish a Hard Cutoff Date

ARTB
:*Curr_Bal_amt key field

Examples by Table

2024-02-26T13:57:54Z

Chanson: /* Salespeople (empl) */

===GL Accounts===
ACCT is the source in CMD but it's not high quality data.

===Company (Divisions)===
COMP will provide company list. This might be separate databases or separate divisions within a database.

===Plants (Locn/Plnt)===
Locations and plants are similar but different. Multiple plants may have on location.

*LOCN: Locations
*PLNT: Plant

Prices and items are normally setup by location. For example you may have location 01 with plants 1A and 1B. The product tables (IMST/ILOC) would only have a reference to location 01 where Keystone will have 1A and 1B.

===Drivers (empl)===
<pre>
with driv as (
SELECT --the core query
rtrim(ltrim(empl_code)) "DRIVER_CODE",
replace(rtrim(ltrim(NAME)),'''','''''') "DRIVER_NAME",
rtrim(ltrim(empl.phone_num)) Phone_no,
rtrim(ltrim(empl.assgn_plant_code)) Home_plant_no,
--EMPL.*,
''',''' QCQ,''',' q1,'=''' q2 -- these are helpers for the concat query
from empl
where driv_flag = 1
and inactive_code='00'
)
select -- this creates insert from the core query
concat('INSERT INTO artdriv ( DRIVER_CODE, DRIVER_NAME, PHONE_NO, HOME_PLANT_NO, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG,memo ) VALUES
( ''',driver_code,qcq,DRIVER_NAME,qcq,Phone_no,qcq,Home_plant_no,qcq ,'Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y',qcq,'Imported from Cmd'');' ),
driv.* from driv
</pre>

===Salespeople (empl)===
<pre>
with cs as
( select distinct(ltrim(rtrim(cust.cc_slsmn_empl_code))) custslsmn from cust where inactive_code='00' ), -- LIst of slsmn in active customers
slsemp as -- all sls employees
(SELECT
rtrim(ltrim(empl.empl_code)) empno,
rtrim(ltrim(empl.empl_code)) USER_1,
rtrim(LTRIM(empl.NAME)) DESCRIPTION,
rtrim(LTRIM(empl.PHONE_NUM)) PHONE_NO,
rtrim(LTRIM(empl.ASSGN_PLANT_CODE)) USER_2
FROM empl
where slsmn_flag='True')
,slpn as
(
select cs.*,slsemp.*,
case custslsmn
WHEN '182' THEN 'NJ'
WHEN '322' THEN 'RM'
WHEN 'S15' THEN '15'
---...
WHEN 'SALES' THEN 'SA'
else custslsmn
end class_no,''',''' qcq
from cs
INNER join slsemp on slsemp.empno=cs.custslsmn --only include slspn that are in active customers
) select
concat ( 'INSERT INTO artslspn ( CLASS_NO, EMPLOYEE_NO, DESCRIPTION, PHONE_NO, USER_2 ) VALUES (''',class_no,qcq,empno,qcq,DESCRIPTION,qcq,PHONE_NO,qcq,USER_2,''');'),
slpn.* from slpn
order by custslsmn
</pre>

===Taxes===
This is somewhat similar to Keystone setup, but not quite the same. Terminlogy is quite different:
Command - Keystone
Tax Code - Tax Authority
Tax Authority - Tax Level (e.g. 1=state, 2=County, 3=City)

CMD is always setup multi-level whereas Keystone customer often want to flatten the taxes to single level. This is a key up-front question explaining the pros/cons of each direction.

*'''TAXC:''' Tax (Similar to Keystone Tax Auth)
*'''TAXJ:''' Tax Structure
*'''TAXL:''' Tax Locality (not 1 to 1 with Keystone)

====Tax Locality====

<pre>
with ttl (tax_loc,tax_auth,descr,rate) as
(select -- Core query (can be used with QG)
cast(tl.tax_loc as integer),cast(tl.tax_auth as integer),
replace(replace(ltrim(rtrim(descr)),'(City of','('),'( ','('),
max(prim_rate_curr_pct) from taxl tl
inner join taxj tj on tl.tax_loc=tj.tax_loc and tl.tax_auth=tj.tax_auth
group by tl.tax_loc,tl.tax_auth,descr
)
select
concat('INSERT INTO ARTTXLOC
( TAX_LOCALITY, LINE_NO, DESCRIPTION, TAX_COL, LOC_LEVEL, TAX_RATE, CALC_METHOD, EFFECT_DATE, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( ''',100*tax_auth + tax_loc,''', ',tax_auth,',''',left(descr,35),''',1 ,',tax_auth,',',rate,', 1, ''01/01/1900'', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y'' );'),
ttl.* from ttl
order by tax_auth,tax_loc;
</pre>

====Tax Authority====
For the tax authority, we just need the code and the description...
<pre>
with tc as
(select ltrim(rtrim(tax_code)) "tax_code",
replace(replace(ltrim(rtrim(descr)),'(City of','('),'( ','(') "descr"
from taxc)
select
concat('INSERT INTO ARTTXAUT ( TAX_AUTHORITY, DESCRIPTION, INCREASE_DISC_FLAG, DISC_INCLUDES_TAX, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( ''',tax_code,''' , ''',left(descr,35),''', ''N'', ''Y'', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y'' );',''),
tc.* from tc;
</pre>

===Price Group Headers===
You need to import the Price Group (Category in CMD) before customers. Import the actual group prices after products are setup.
'''PRCC'''

Sample query:
<pre>
with aaa (clsno,descr) as
(
select -- core query that can be used with qg
ltrim(rtrim(price_cat)),
ltrim(rtrim(descry)) description
from prcc
)
select -- outer query
concat('INSERT INTO ARTPRGRP ( GROUP_NO, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG) VALUES
( ''',clsno,''',''',left(descr,35),''', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y'' );')
,clsno "group", descry description
from aaa
</pre>

Price Cat codes tend to be short and do not need to be mapped coming into Keystone.

===Dispatch Useage Class===
*'''USGE:''' Useage

===Customer Class (sanl)===
<pre>
with sac (clsno,descr) as
(
select
case ltrim(rtrim(sales_anl_code))
when 'NSF' THEN 'NS'
when 'CNA' THEN 'CN'
when 'C27' THEN '27'
when 'C29' THEN '29'
when 'C34' THEN '34'
when 'C53' THEN '53'
else ltrim(rtrim(sales_anl_code)) end
"class_no",
ltrim(rtrim(descry)) description
from sanl
)
select
concat('
INSERT INTO artcucls
( CLASS_NO, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( ''',clsno,''',''',left(descr,35),''', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y'' );
'),
ltrim(rtrim(clsno)) "class_no",
ltrim(rtrim(descry)) description, *
from sac
</pre>

===Customer===
*'''CUST:''' Customer

State needs to be mapped...
<pre)
CASE REPLACE(UPPER(TRIM(ADDR_STATE)),' ','')
WHEN 'NEBRASK' THEN 'NE'
WHEN 'NEWYORK' THEN 'NY'
WHEN 'NEWJERSEY' THEN 'NJ'
...
WHEN 'PENNSYLVAN' THEN 'PA'
WHEN 'SOUTHCARO' THEN 'SC'
WHEN 'TENNESSEE' THEN 'TN'
ELSE SUBSTR(UPPER(TRIM(ADDR_STATE)),1,2) END STATE,
</pre>

===Product Class (icat)===

A good product class table is essential for a Command conversion.

You will mostly likely need to map long item cat codes to product class codes. Instead of making them like the CMD item cat codes, so focus on making a well organized Keystone list and then have the customer help you mapping CMD item categories to Keystone classes.

Separate the sellable ingredient items for admixes, colors etc. from the non-sellable ingredient codes.
When creating product classes, set the defaults for sellable, product type and ingredient type. Then use the following script to update:

<pre>
update artprod p set sellable_flag =(select std_sellable_flag from artprcls c where c.class_no=p.product_class);
update artprod p set product_type =(select std_product_type from artprcls c where c.class_no=p.product_class);
update artprod p set ingredient_type =(select std_ingredient_type from artprcls c where c.class_no=p.product_class);
</pre>

Setup default sales G/L and make sure the plants are properly masked. You can use a variant of this script to update yoru sales g/l accounts...

<pre>
UPDATE ARTPROD P
SET p.SALES_GL=
(select substr(std_sales_gl,1,8) from artprcls pc where pc.class_no=p.product_class)||
(select substr(sales_gl_plant_mask,9,19) from artplant pl where pl.plant_no=p.plant_no)
where (select substr(std_sales_gl,1,8) from artprcls pc where pc.class_no=p.product_class) is not null
and p.product_class IN ('AA','BB','CC') -- Optionally only do this for select classes
</pre>

===Products===
Products are stored in 2 tables, imst and iloc.
*'''IMST:''' Product "Base". One record for base product that stores the category (class) description etc.
*'''ILOC:''' Product by Location

These need to be combined to create keystone products.

===Component Structure (icst)===
CMD dispatch mixes rarely are worth importing. Verify with customer, but expect to import directly from batch controls.

===Credit Terms (TRMS)===
You can import term codes and descriptions, but the meaning does not map from system to system.

<pre>
WITH T AS (
SELECT
case ltrim(rtrim(terms_code)) -- setup mapping for terms longer than 2 char. Save for customer import etc.
when '10N' THEN '1N'
WHEN '15T' THEN '5T'
WHEN '2.5' THEN '25'
ELSE LTRIM(RTRIM(TERMS_CODE)) END CLASS_NO
,LEFT(LTRIM(RTRIM(descr)),30) DESCRIPTION
FROM dbo.trms
)
SELECT
CONCAT('iNSERT INTO ARTCTCLS (CLASS_NO,DESCRIPTION) VALUES (''',CLASS_NO,''',''',DESCRIPTION,''');'),
* FROM T
</pre>

===Trucks===
Trucks

===Unit of Meausure (uoms)===
The key field for Unit of Measure is typically a long integer.

===Customer Prices (CPRD)===

*A plant of # means all plants. On import try to match this to a Keystone Base Price Plant. (If all plants point to 01 - import price to 01 if possible)

===List and Group Prices (IPRC)===
The table IPRC stores Category (cmd)/Group Prices and the equivelent of List prices. Category "#" is represents "List" in Keystone.
*Many times, what appears to be a Group(cat) is in fact used as a list price. You can preview the iprc data to the customer to determine this before the install. You can also import into Keystone and then convert a group to list prices.
*Often, a large amount of junk pricing is in this table. Preview to customer to determine if large amounts of pricing should be omitted.
*Where in Keystone, we often base pricing on X$ off list for a group or customer, in Command each price ends up as a record. If possible, find groups (price cats) that can be skipped and replaced with a price off list. (this may only apply to the mix items in a group/price cat)
*Expect redundant records that tneed to be processed by effect date. (Use of RANK or similar SQL tricks can correct for this)
*Expect Prices for items that may or may not exist.
*A plant of # means all plants. On import try to match this to a Keystone Base Price Plant. (If all plants point to 01 - import price to 01 if possible)

===Jobs===

====Job (proj)====
*Project Code and Name vs JOB PO Loc. Code is very cryptic. Name is very long. Best solution: - BatchJobCode as ProjCode. Import Proj Name into Invoice Desc..Import proj code into JobPoLoc but update with a cleaned up verion of the code.
*Base selection on Expir_date, Setup_date and the inactive_code of projects, customers, iloc and imst.

====Job Detail (prjp)====
*Tie this selection to the core query used for PROJ
*Plant # means all plants. On import try to match this to a Keystone Base Price Plant. (If all plants point to 01 - import price to 01 if possible)
*Plant number not cleanly in detail table. You can attempt to use the plant info in the project table. But then the next problem...
*The products from job detail will have mismatches with imported products. You will see prices for product (base) and plant combos that don't exist and thus are not imported into Keystone. Either these get skipped or you need clever rerouting selection queries to find valid product.
*Multiple price records per Customer/Project/Product. You need to use special query features such as rank to sort this out.

====Price Escalation (ppre)====
*Price Escalation - the price in prjp may not apply if the customer uses escalation.

===Conctacts===
*'''CCON:''' Contact Connection
*'''CTCT:''' Contact

===Zone===
<pre>
SELECT
CONCAT('iNSERT INTO ARTzone (zone,DESCRIPTION,def_tax_authority) VALUES (''',rtrim(ltrim(zone_code)),
''',''',left(rtrim(ltrim(DESCR)),35),
''',''',rtrim(ltrim(tax_code)),''');'),
zone.*
FROM ZONE
</pre>

===A/R Invoice===
AR may or may not come from Command system. This needs to be researched.
*Invoice Numbers may be numeric or Alpha. When Alpha - Invoice Number=0, JobPoLoc = Alpha Inovice Number.
*Create Preview outputs - DataScopeX is good for this.
*Establish a Hard Cutoff Date

ARTB
:*Curr_Bal_amt key field

Examples by Table

2024-02-26T13:25:25Z

Chanson:

===GL Accounts===
ACCT is the source in CMD but it's not high quality data.

===Company (Divisions)===
COMP will provide company list. This might be separate databases or separate divisions within a database.

===Plants (Locn/Plnt)===
Locations and plants are similar but different. Multiple plants may have on location.

*LOCN: Locations
*PLNT: Plant

Prices and items are normally setup by location. For example you may have location 01 with plants 1A and 1B. The product tables (IMST/ILOC) would only have a reference to location 01 where Keystone will have 1A and 1B.

===Drivers (empl)===
<pre>
with driv as (
SELECT --the core query
rtrim(ltrim(empl_code)) "DRIVER_CODE",
replace(rtrim(ltrim(NAME)),'''','''''') "DRIVER_NAME",
rtrim(ltrim(empl.phone_num)) Phone_no,
rtrim(ltrim(empl.assgn_plant_code)) Home_plant_no,
--EMPL.*,
''',''' QCQ,''',' q1,'=''' q2 -- these are helpers for the concat query
from empl
where driv_flag = 1
and inactive_code='00'
)
select -- this creates insert from the core query
concat('INSERT INTO artdriv ( DRIVER_CODE, DRIVER_NAME, PHONE_NO, HOME_PLANT_NO, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG,memo ) VALUES
( ''',driver_code,qcq,DRIVER_NAME,qcq,Phone_no,qcq,Home_plant_no,qcq ,'Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y',qcq,'Imported from Cmd'');' ),
driv.* from driv
</pre>

===Salespeople (empl)===
<pre>
with cs as
( select distinct(ltrim(rtrim(cust.cc_slsmn_empl_code))) custslsmn from cust where inactive_code='00' ),
slsemp as
(SELECT
rtrim(ltrim(empl.empl_code)) empno,
rtrim(ltrim(empl.empl_code)) USER_1,
rtrim(LTRIM(empl.NAME)) DESCRIPTION,
rtrim(LTRIM(empl.PHONE_NUM)) PHONE_NO,
rtrim(LTRIM(empl.ASSGN_PLANT_CODE)) USER_2
FROM empl
where slsmn_flag='True')
,slpn as
(
select cs.*,slsemp.*,
case custslsmn
WHEN '182' THEN 'NJ'
WHEN '322' THEN 'RM'
...
WHEN 'SALES' THEN 'SA'
else custslsmn
end class_no,''',''' qcq
from cs
left outer join slsemp on slsemp.empno=cs.custslsmn
) select
concat ( 'INSERT INTO artslspn ( CLASS_NO, EMPLOYEE_NO, DESCRIPTION, PHONE_NO, USER_2 ) VALUES (''',class_no,qcq,empno,qcq,DESCRIPTION,qcq,PHONE_NO,qcq,USER_2,''');'),
slpn.* from slpn
order by custslsmn
</pre>

===Taxes===
This is somewhat similar to Keystone setup, but not quite the same. Terminlogy is quite different:
Command - Keystone
Tax Code - Tax Authority
Tax Authority - Tax Level (e.g. 1=state, 2=County, 3=City)

CMD is always setup multi-level whereas Keystone customer often want to flatten the taxes to single level. This is a key up-front question explaining the pros/cons of each direction.

*'''TAXC:''' Tax (Similar to Keystone Tax Auth)
*'''TAXJ:''' Tax Structure
*'''TAXL:''' Tax Locality (not 1 to 1 with Keystone)

====Tax Locality====

<pre>
with ttl (tax_loc,tax_auth,descr,rate) as
(select -- Core query (can be used with QG)
cast(tl.tax_loc as integer),cast(tl.tax_auth as integer),
replace(replace(ltrim(rtrim(descr)),'(City of','('),'( ','('),
max(prim_rate_curr_pct) from taxl tl
inner join taxj tj on tl.tax_loc=tj.tax_loc and tl.tax_auth=tj.tax_auth
group by tl.tax_loc,tl.tax_auth,descr
)
select
concat('INSERT INTO ARTTXLOC
( TAX_LOCALITY, LINE_NO, DESCRIPTION, TAX_COL, LOC_LEVEL, TAX_RATE, CALC_METHOD, EFFECT_DATE, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( ''',100*tax_auth + tax_loc,''', ',tax_auth,',''',left(descr,35),''',1 ,',tax_auth,',',rate,', 1, ''01/01/1900'', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y'' );'),
ttl.* from ttl
order by tax_auth,tax_loc;
</pre>

====Tax Authority====
For the tax authority, we just need the code and the description...
<pre>
with tc as
(select ltrim(rtrim(tax_code)) "tax_code",
replace(replace(ltrim(rtrim(descr)),'(City of','('),'( ','(') "descr"
from taxc)
select
concat('INSERT INTO ARTTXAUT ( TAX_AUTHORITY, DESCRIPTION, INCREASE_DISC_FLAG, DISC_INCLUDES_TAX, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( ''',tax_code,''' , ''',left(descr,35),''', ''N'', ''Y'', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y'' );',''),
tc.* from tc;
</pre>

===Price Group Headers===
You need to import the Price Group (Category in CMD) before customers. Import the actual group prices after products are setup.
'''PRCC'''

Sample query:
<pre>
with aaa (clsno,descr) as
(
select -- core query that can be used with qg
ltrim(rtrim(price_cat)),
ltrim(rtrim(descry)) description
from prcc
)
select -- outer query
concat('INSERT INTO ARTPRGRP ( GROUP_NO, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG) VALUES
( ''',clsno,''',''',left(descr,35),''', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y'' );')
,clsno "group", descry description
from aaa
</pre>

Price Cat codes tend to be short and do not need to be mapped coming into Keystone.

===Dispatch Useage Class===
*'''USGE:''' Useage

===Customer Class (sanl)===
<pre>
with sac (clsno,descr) as
(
select
case ltrim(rtrim(sales_anl_code))
when 'NSF' THEN 'NS'
when 'CNA' THEN 'CN'
when 'C27' THEN '27'
when 'C29' THEN '29'
when 'C34' THEN '34'
when 'C53' THEN '53'
else ltrim(rtrim(sales_anl_code)) end
"class_no",
ltrim(rtrim(descry)) description
from sanl
)
select
concat('
INSERT INTO artcucls
( CLASS_NO, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( ''',clsno,''',''',left(descr,35),''', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y'' );
'),
ltrim(rtrim(clsno)) "class_no",
ltrim(rtrim(descry)) description, *
from sac
</pre>

===Customer===
*'''CUST:''' Customer

State needs to be mapped...
<pre)
CASE REPLACE(UPPER(TRIM(ADDR_STATE)),' ','')
WHEN 'NEBRASK' THEN 'NE'
WHEN 'NEWYORK' THEN 'NY'
WHEN 'NEWJERSEY' THEN 'NJ'
...
WHEN 'PENNSYLVAN' THEN 'PA'
WHEN 'SOUTHCARO' THEN 'SC'
WHEN 'TENNESSEE' THEN 'TN'
ELSE SUBSTR(UPPER(TRIM(ADDR_STATE)),1,2) END STATE,
</pre>

===Product Class (icat)===

A good product class table is essential for a Command conversion.

You will mostly likely need to map long item cat codes to product class codes. Instead of making them like the CMD item cat codes, so focus on making a well organized Keystone list and then have the customer help you mapping CMD item categories to Keystone classes.

Separate the sellable ingredient items for admixes, colors etc. from the non-sellable ingredient codes.
When creating product classes, set the defaults for sellable, product type and ingredient type. Then use the following script to update:

<pre>
update artprod p set sellable_flag =(select std_sellable_flag from artprcls c where c.class_no=p.product_class);
update artprod p set product_type =(select std_product_type from artprcls c where c.class_no=p.product_class);
update artprod p set ingredient_type =(select std_ingredient_type from artprcls c where c.class_no=p.product_class);
</pre>

Setup default sales G/L and make sure the plants are properly masked. You can use a variant of this script to update yoru sales g/l accounts...

<pre>
UPDATE ARTPROD P
SET p.SALES_GL=
(select substr(std_sales_gl,1,8) from artprcls pc where pc.class_no=p.product_class)||
(select substr(sales_gl_plant_mask,9,19) from artplant pl where pl.plant_no=p.plant_no)
where (select substr(std_sales_gl,1,8) from artprcls pc where pc.class_no=p.product_class) is not null
and p.product_class IN ('AA','BB','CC') -- Optionally only do this for select classes
</pre>

===Products===
Products are stored in 2 tables, imst and iloc.
*'''IMST:''' Product "Base". One record for base product that stores the category (class) description etc.
*'''ILOC:''' Product by Location

These need to be combined to create keystone products.

===Component Structure (icst)===
CMD dispatch mixes rarely are worth importing. Verify with customer, but expect to import directly from batch controls.

===Credit Terms (TRMS)===
You can import term codes and descriptions, but the meaning does not map from system to system.

<pre>
WITH T AS (
SELECT
case ltrim(rtrim(terms_code)) -- setup mapping for terms longer than 2 char. Save for customer import etc.
when '10N' THEN '1N'
WHEN '15T' THEN '5T'
WHEN '2.5' THEN '25'
ELSE LTRIM(RTRIM(TERMS_CODE)) END CLASS_NO
,LEFT(LTRIM(RTRIM(descr)),30) DESCRIPTION
FROM dbo.trms
)
SELECT
CONCAT('iNSERT INTO ARTCTCLS (CLASS_NO,DESCRIPTION) VALUES (''',CLASS_NO,''',''',DESCRIPTION,''');'),
* FROM T
</pre>

===Trucks===
Trucks

===Unit of Meausure (uoms)===
The key field for Unit of Measure is typically a long integer.

===Customer Prices (CPRD)===

*A plant of # means all plants. On import try to match this to a Keystone Base Price Plant. (If all plants point to 01 - import price to 01 if possible)

===List and Group Prices (IPRC)===
The table IPRC stores Category (cmd)/Group Prices and the equivelent of List prices. Category "#" is represents "List" in Keystone.
*Many times, what appears to be a Group(cat) is in fact used as a list price. You can preview the iprc data to the customer to determine this before the install. You can also import into Keystone and then convert a group to list prices.
*Often, a large amount of junk pricing is in this table. Preview to customer to determine if large amounts of pricing should be omitted.
*Where in Keystone, we often base pricing on X$ off list for a group or customer, in Command each price ends up as a record. If possible, find groups (price cats) that can be skipped and replaced with a price off list. (this may only apply to the mix items in a group/price cat)
*Expect redundant records that tneed to be processed by effect date. (Use of RANK or similar SQL tricks can correct for this)
*Expect Prices for items that may or may not exist.
*A plant of # means all plants. On import try to match this to a Keystone Base Price Plant. (If all plants point to 01 - import price to 01 if possible)

===Jobs===

====Job (proj)====
*Project Code and Name vs JOB PO Loc. Code is very cryptic. Name is very long. Best solution: - BatchJobCode as ProjCode. Import Proj Name into Invoice Desc..Import proj code into JobPoLoc but update with a cleaned up verion of the code.
*Base selection on Expir_date, Setup_date and the inactive_code of projects, customers, iloc and imst.

====Job Detail (prjp)====
*Tie this selection to the core query used for PROJ
*Plant # means all plants. On import try to match this to a Keystone Base Price Plant. (If all plants point to 01 - import price to 01 if possible)
*Plant number not cleanly in detail table. You can attempt to use the plant info in the project table. But then the next problem...
*The products from job detail will have mismatches with imported products. You will see prices for product (base) and plant combos that don't exist and thus are not imported into Keystone. Either these get skipped or you need clever rerouting selection queries to find valid product.
*Multiple price records per Customer/Project/Product. You need to use special query features such as rank to sort this out.

====Price Escalation (ppre)====
*Price Escalation - the price in prjp may not apply if the customer uses escalation.

===Conctacts===
*'''CCON:''' Contact Connection
*'''CTCT:''' Contact

===Zone===
<pre>
SELECT
CONCAT('iNSERT INTO ARTzone (zone,DESCRIPTION,def_tax_authority) VALUES (''',rtrim(ltrim(zone_code)),
''',''',left(rtrim(ltrim(DESCR)),35),
''',''',rtrim(ltrim(tax_code)),''');'),
zone.*
FROM ZONE
</pre>

===A/R Invoice===
AR may or may not come from Command system. This needs to be researched.
*Invoice Numbers may be numeric or Alpha. When Alpha - Invoice Number=0, JobPoLoc = Alpha Inovice Number.
*Create Preview outputs - DataScopeX is good for this.
*Establish a Hard Cutoff Date

ARTB
:*Curr_Bal_amt key field

Examples by Table

2024-02-26T13:20:56Z

Chanson:

Examples by Table

2024-02-23T19:34:06Z

Chanson: /* Component Stucture */

Examples by Table

2024-02-23T18:25:37Z

Chanson: /* Taxes */

Examples by Table

2024-02-22T21:11:51Z

Chanson: /* Product Class (icat) */

Command Import

2024-02-22T15:12:22Z

Chanson:

There is not ONE way to import Command Data - each situation is unique.

===Know This First===
What you need to know before importing Command's Data
*There is NO data integrity.
:*Expect sub tables, prices etc. to have records that do have matching main tables. (e.g. prices for customers that don't exist).
*Alpha fields must be trimmed - especially key fields. Expect both left and right padding on Customer and product codes.
:MS SQL hasn't had the "TRIM" statement for long, so most often you must select as:
:<code>LTRIM(RTRIM((PRJP.PROJ_CODE)) JOB_PO_LOC,</code>
:in some cases, such as Oracle you only have trim and must select as:
:<code>TRIM(PRJP.PROJ_CODE) JOB_PO_LOC,</code>
*Numeric Fields may be null. Use coalesce... <code>COALESCE(Finc_Chrg_amt,0) Finc_chrg_amt</code>
*Field IDs are longer - especially Tax Authorities, sales people and classes.
*Keep a list of mappings when setting up the class type fields to use in case statements...
<pre>
case TERMS
WHEN '15N' THEN '15'
WHEN 'N20' THEN '20'
...
WHEN 'CAS' THEN 'CA' ELSE '' END CUST_TERMS</pre>
*Some records, especially prices, have what appear to be duplicates. You need special queries that pull just the latest version of the record.
*Start with Small Tables and Build up (Like any Import)

===MS SQL to know===
*ltrim,rtrim
*replace
*left, right
*case
*iif
*rank

===Import Examples===

Here are some samples used in the past...
:[[Examples by Table]]
:[[Command Import: Hedger Work Notes]]
:[[Command Import: Rain MO Work Notes]]

===Command Tables===

*'''ACCT:''' GL Accounts
*'''ARTB:''' Aging
*'''CCON:''' Contact Connection
*'''COMP:''' Company
*'''CPRD:''' Customer Prices
*'''CTCT:''' Contact
*'''CUST:''' Customer Prices
*'''EMPL:''' Employee - Sales and Drivers
*'''ICAT:''' Item (Product)
*'''ICST:''' Component Structure
*'''ILOC:''' Product by Location (Similar to Plant)
*'''IMST:''' Products
*'''IPRC:''' Prices - by Level (Group)
*'''LOCN:''' Locations (Similar to Plant)
*'''PLNT:''' Plant
*'''PRCC:''' Price Group
*'''PRJP:''' Project Prices
*'''PROJ:''' Project
*'''SANL:''' Customer Class
*'''TAXC:''' Tax (Similar to Keystone Tax Auth)
*'''TAXJ:''' Tax Structure
*'''TAXL:''' Tax Locality (not 1 to 1 with Keystone)
*'''TRMS:''' Terms
*'''TRUC:''' Trucks
*'''UOMS:''' Unit of Meausure
*'''USGE:''' Useage

Examples by Table

2024-02-22T15:08:44Z

Chanson: /* Credit Terms (TRMS) */

Examples by Table

2024-02-22T15:03:41Z

Chanson: /* Price Group Headers */

Examples by Table

2024-02-22T14:57:14Z

Chanson: /* Customer */

Examples by Table

2024-02-22T14:18:50Z

Chanson:

Examples by Table

2024-02-21T17:06:32Z

Chanson: /* Project (proj) */

Examples by Table

2024-02-21T16:02:24Z

Chanson: /* Challenges with Job Import */

Examples by Table

2024-02-21T16:01:19Z

Chanson: /* Jobs */

Command Import

2024-02-20T16:21:18Z

Chanson: /* Know This First */

There is not ONE way to import Command Data - each situation is unique.

===Know This First===
What you need to know before importing Command's Data
*There is NO data integrity.
:*Expect sub tables, prices etc. to have records that do have matching main tables. (e.g. prices for customers that don't exist).
*Alpha fields must be trimmed - especially key fields. Expect both left and right padding on Customer and product codes.
:MS SQL hasn't had the "TRIM" statement for long, so most often you must select as:
:<code>LTRIM(RTRIM((PRJP.PROJ_CODE)) JOB_PO_LOC,</code>
:in some cases, such as Oracle you only have trim and must select as:
:<code>TRIM(PRJP.PROJ_CODE) JOB_PO_LOC,</code>
*Numeric Fields may be null. Use coalesce... <code>COALESCE(Finc_Chrg_amt,0) Finc_chrg_amt</code>
*Field IDs are longer - especially Tax Authorities, sales people and classes.
*Keep a list of mappings when setting up the class type fields to use in case statements...
<pre>
case TERMS
WHEN '15N' THEN '15'
WHEN 'N20' THEN '20'
...
WHEN 'CAS' THEN 'CA' ELSE '' END CUST_TERMS</pre>
*Some records, especially prices, have what appear to be duplicates. You need special queries that pull just the latest version of the record.
*Start with Small Tables and Build up (Like any Import)

===Import Examples===

Here are some samples used in the past...
:[[Examples by Table]]
:[[Command Import: Hedger Work Notes]]
:[[Command Import: Rain MO Work Notes]]

===Command Tables===

*'''ACCT:''' GL Accounts
*'''ARTB:''' Aging
*'''CCON:''' Contact Connection
*'''COMP:''' Company
*'''CPRD:''' Customer Prices
*'''CTCT:''' Contact
*'''CUST:''' Customer Prices
*'''EMPL:''' Employee - Sales and Drivers
*'''ICAT:''' Item (Product)
*'''ICST:''' Component Structure
*'''ILOC:''' Product by Location (Similar to Plant)
*'''IMST:''' Products
*'''IPRC:''' Prices - by Level (Group)
*'''LOCN:''' Locations (Similar to Plant)
*'''PLNT:''' Plant
*'''PRCC:''' Price Group
*'''PRJP:''' Project Prices
*'''PROJ:''' Project
*'''SANL:''' Customer Class
*'''TAXC:''' Tax (Similar to Keystone Tax Auth)
*'''TAXJ:''' Tax Structure
*'''TAXL:''' Tax Locality (not 1 to 1 with Keystone)
*'''TRMS:''' Terms
*'''TRUC:''' Trucks
*'''UOMS:''' Unit of Meausure
*'''USGE:''' Useage

Command Import

2024-02-20T16:18:01Z

Chanson: /* Know This First */

There is not ONE way to import Command Data - each situation is unique.

===Know This First===
What you need to know before importing Command's Data
*There is NO data integrity.
:*Expect sub tables, prices etc. to have records that do have matching main tables. (e.g. prices for customers that don't exist).
*Alpha fields must be trimmed - especially key fields. Expect both left and right padding on Customer and product codes.
:MS SQL hasn't had the "TRIM" statement for long, so most often you must select as:
:<code>LTRIM(RTRIM((PRJP.PROJ_CODE)) JOB_PO_LOC,</code>
:in some cases, such as Oracle you only have trim and must select as:
:<code>TRIM(PRJP.PROJ_CODE) JOB_PO_LOC,</code>
*Numeric Fields may be null. Use coalesce... <code>COALESCE(Finc_Chrg_amt,0) Finc_chrg_amt</code>
*Field IDs are longer - especially Tax Authorities, sales people and classes.
*Keep a list of mappings when setting up the class type fields to use in case statements...
<pre>
case TERMS
WHEN '15N' THEN '15'
WHEN 'N20' THEN '20'
...
WHEN 'CAS' THEN 'CA' ELSE '' END CUST_TERMS</pre>
*Start with Small Tables and Build up (Like any Import)

===Import Examples===

Here are some samples used in the past...
:[[Examples by Table]]
:[[Command Import: Hedger Work Notes]]
:[[Command Import: Rain MO Work Notes]]

===Command Tables===

*'''ACCT:''' GL Accounts
*'''ARTB:''' Aging
*'''CCON:''' Contact Connection
*'''COMP:''' Company
*'''CPRD:''' Customer Prices
*'''CTCT:''' Contact
*'''CUST:''' Customer Prices
*'''EMPL:''' Employee - Sales and Drivers
*'''ICAT:''' Item (Product)
*'''ICST:''' Component Structure
*'''ILOC:''' Product by Location (Similar to Plant)
*'''IMST:''' Products
*'''IPRC:''' Prices - by Level (Group)
*'''LOCN:''' Locations (Similar to Plant)
*'''PLNT:''' Plant
*'''PRCC:''' Price Group
*'''PRJP:''' Project Prices
*'''PROJ:''' Project
*'''SANL:''' Customer Class
*'''TAXC:''' Tax (Similar to Keystone Tax Auth)
*'''TAXJ:''' Tax Structure
*'''TAXL:''' Tax Locality (not 1 to 1 with Keystone)
*'''TRMS:''' Terms
*'''TRUC:''' Trucks
*'''UOMS:''' Unit of Meausure
*'''USGE:''' Useage

Examples by Table

2024-02-19T18:43:28Z

Chanson:

Examples by Table

2024-02-19T18:28:33Z

Chanson:

*'''GL Accounts''' (ACCT)
*'''COMP:''' Company
*'''USGE:''' Useage
*'''SANL:''' Customer Class

===Taxes===
*'''TAXC:''' Tax (Similar to Keystone Tax Auth)
*'''TAXJ:''' Tax Structure
*'''TAXL:''' Tax Locality (not 1 to 1 with Keystone)

===Salespeople and Drivers===
*'''EMPL:''' Employee - Sales and Drivers

===Conctacts===
*'''CCON:''' Contact Connection
*'''CTCT:''' Contact

===Customer===
*'''CUST:''' Customer
State needs to be mapped...
<pre)
CASE REPLACE(UPPER(TRIM(ADDR_STATE)),' ','')
WHEN 'NEBRASK' THEN 'NE'
WHEN 'NEWYORK' THEN 'NY'
WHEN 'NEWJERSEY' THEN 'NJ'
...
WHEN 'PENNSYLVAN' THEN 'PA'
WHEN 'SOUTHCARO' THEN 'SC'
WHEN 'TENNESSEE' THEN 'TN'
ELSE SUBSTR(UPPER(TRIM(ADDR_STATE)),1,2) END STATE,
</pre>

*'''ICAT:''' Item (Product)
*'''ICST:''' Component Structure
*'''ILOC:''' Product by Location (Similar to Plant)
*'''IMST:''' Products
*'''LOCN:''' Locations (Similar to Plant)
*'''PLNT:''' Plant
*'''PRCC:''' Price Group
*'''PRJP:''' Project Prices
*'''PROJ:''' Project

<pre>
with sac (clsno,descr) as
(
select
case ltrim(rtrim(sales_anl_code))
when 'NSF' THEN 'NS'
when 'CNA' THEN 'CN'
when 'C27' THEN '27'
when 'C29' THEN '29'
when 'C34' THEN '34'
when 'C53' THEN '53'
else ltrim(rtrim(sales_anl_code)) end
"class_no",
ltrim(rtrim(descr)) description
from sanl
)
select
concat('
INSERT INTO artcucls
( CLASS_NO, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( ''',clsno,''',''',left(descr,35),''', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y'' );
'),
ltrim(rtrim(clsno)) "class_no",
ltrim(rtrim(descr)) description, *
from sac
</pre>
*'''TRMS:''' Terms
*'''TRUC:''' Trucks
*'''UOMS:''' Unit of Meausure
===Prices===
*'''CPRD:''' Customer Prices
*'''IPRC:''' Prices - by Level (Group)

===A/R Invoice===
AR may or may not come from Command system. This needs to be researched.
*Invoice Numbers may be numeric or Alpha. When Alpha - Invoice Number=0, JobPoLoc = Alpha Inovice Number.
*Create Preview outputs - DataScopeX is good for this.
*Establish a Hard Cutoff Date

*ARTB
:*Curr_Bal_amt key field

Command Import

2024-02-19T12:57:10Z

Chanson:

There is not ONE way to import Command Data - each situation is unique.

===Know This First===
What you need to know before importing Command's Data
*There is NO data integrity.
:*Expect sub tables, prices etc. to have records that do have matching main tables. (e.g. prices for customers that don't exist).
*All fields must be trimmed - especially key fields. Expect both left and right padding on Customer and product codes.
:MS SQL hasn't had the "TRIM" statement for long, so most often you must select as:
:<code>LTRIM(RTRIM((PRJP.PROJ_CODE)) JOB_PO_LOC,</code>
:in some cases, such as Oracle you only have trim and must select as:
:<code>TRIM(PRJP.PROJ_CODE) JOB_PO_LOC,</code>
*Field IDs are longer - especially Tax Authorities, sales people and classes.
:*Keep a list of mappings when setting up the class type fields to use in case statements...
<pre>
case TERMS
WHEN '15N' THEN '15'
WHEN 'N20' THEN '20'
...
WHEN 'CAS' THEN 'CA' ELSE '' END CUST_TERMS</pre>
*Start with Small Tables and Build up (Like any Import)

===Import Examples===

Here are some samples used in the past...
:[[Examples by Table]]
:[[Command Import: Hedger Work Notes]]
:[[Command Import: Rain MO Work Notes]]

===Command Tables===

*'''ACCT:''' GL Accounts
*'''ARTB:''' Aging
*'''CCON:''' Contact Connection
*'''COMP:''' Company
*'''CPRD:''' Customer Prices
*'''CTCT:''' Contact
*'''CUST:''' Customer Prices
*'''EMPL:''' Employee - Sales and Drivers
*'''ICAT:''' Item (Product)
*'''ICST:''' Component Structure
*'''ILOC:''' Product by Location (Similar to Plant)
*'''IMST:''' Products
*'''IPRC:''' Prices - by Level (Group)
*'''LOCN:''' Locations (Similar to Plant)
*'''PLNT:''' Plant
*'''PRCC:''' Price Group
*'''PRJP:''' Project Prices
*'''PROJ:''' Project
*'''SANL:''' Customer Class
*'''TAXC:''' Tax (Similar to Keystone Tax Auth)
*'''TAXJ:''' Tax Structure
*'''TAXL:''' Tax Locality (not 1 to 1 with Keystone)
*'''TRMS:''' Terms
*'''TRUC:''' Trucks
*'''UOMS:''' Unit of Meausure
*'''USGE:''' Useage