Cyberquery Implementation QAD 20071 SE Vijay Venbakkam and Nectar Daloglou March 2015 QAD MWUG Conference Visteon at a Glance One of the worlds most recognized automotive suppliers Provides products to virtually every vehicle manufacturer around the world ID: 602190
Download Presentation The PPT/PDF document "Visteon" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Visteon Cyberquery Implementation – QAD 2007.1 SE
Vijay Venbakkam and Nectar Daloglou
March 2015 QAD MWUG ConferenceSlide2
Visteon at a Glance
One of the world’s most recognized automotive suppliers
Provides products to virtually every vehicle manufacturer around the world
Product portfolio: ElectronicsFocused on, and well-positioned in, high-growth automotive cockpit electronics markets Annual Sales of $ 3.1 billion (2014)1~10,500 employeesFacilities in 20 countries24 Manufacturing Locations15 Regional Tech Centers (8 global)
Page 2
(1) Adjusted to include a full year of Johnson Controls electronics
salesSlide3
Page
3
Visteon Electronics
, a top global cockpit electronics supplier of connectivity, infotainment, driver information and controls products.Vehicle Cockpit ElectronicsVisteon’s Core BusinessAudio and infotainment
Audio head unitsInfotainmentAudio componentsInformation and controlsInstrument clustersDisplaysClimate controlsDecorative control panelsSlide4
Page
4
A Strong and Growing Customer BaseSlide5
Global Footprint
Page
5
Americas
EuropeChinaAsia Pacific
Japan
Technical
Centers
Holland, MI, U.S.
Van Buren Township, U.S.
Cergy
, FR
Chelmsford, GB
Karlsruhe,
DE
Sofia, BG
Shanghai *
Chennai, IN
Regional Customer &
Technical Centers
Chihuahua, MX
São Paolo, BR
Binley
, GBKerpen, DEMonheim, DEPalmela, PRParis, FRSophia Antipolis, FRChangchun *Xuzhou *Pune, INSeoul, KR
HiroshimaNagoyaYokohamaManufacturing SitesChihuahua, MX x2Gravatai, BRManaus, BRReynosa, MXBir El Bey, TNLa Ferté Bernard, FRNamestovo, SKPalmela, PRSkopje, MKVladimir, RU *Changchun x2 * Chongqing x3 *Shaoxing *Shanghai x2 *Xuzhou *Bogor, ID *Chennai, INCheonan, KRRayong, TLHiroshima
Corporate HeadquartersGrace Lake Corporate Center, Van Buren Township, Michigan, U.S.* Joint VentureSlide6
Page
6
QAD at Visteon
Global launch initiative 2008-2015
Objective: Replace mainframe legacy ERP system inherited from Ford
“Core Model” definition with regional customizations for legal/statutory/customer
Extensive Finance customizations
2007.1SE (eB2.1 SP5)
Bolt-on applications: Eagle RF Express,
CyberQuery
Integration middleware, extensive use of
QXtend
Inbound & OutboundSlide7
Page
7
Visteon
Cyberquery
Project Implementation
Global CQ launch initiative started in 2011
Project Objectives
To replace legacy QAD Forms tool with an improved & integrated solution
To implement
a real-time
& efficient BI reporting tool for QAD
Project Key Drivers
Legacy Forms solution out of support
Upgrading legacy solution too expensive
Frequency of critical production issues was high
Need to develop complex QAD forms (including supporting Russian
C
yrillic, Chinese & Japanese fonts, 2D barcodes & complex NA export documentation)
Legacy BI application had performance issues
Custom QAD browses were too slow
Need for efficient, real time QAD reporting with Excel and e-mail compatibilitySlide8
Page
8
Visteon
Cyberquery
Project Implementation
Solution options evaluated
Continue with legacy Forms solution
Upgrade the legacy Forms software version
Evaluate other QAD Forms and Reporting solutions
Cyberquery
QAD Reporting Framework
Others
Key Factors influencing decision
Ability to generate QAD Forms and improve BI reporting capabilities
Cost efficiency
Replace existing (legacy) applications seamlessly
Solution Decision
Cyberquery
(~ June, 2011)Slide9
Page
9
Visteon
Cyberquery
– QAD Forms Methodology
QAD DB
QAD Forms
PO
Shipper
Invoice, etc
.Slide10
Page
10
Visteon
Cyberquery
– QAD Forms Methodology
Page
10
Visteon
Cyberquery
– QAD Forms Methodology
QAD DB
QAD Forms
PO
Shipper
Invoice, etc
.
Progress programs
- Print PO (5.10)
- Shipper (50.7.9.10)
- Invoice (7.13.12)
- Etc.Slide11
Page
11
Visteon
Cyberquery
– QAD Forms Methodology
Page
11
Visteon
Cyberquery
– QAD Forms Methodology
QAD DB
QAD Forms
PO
Shipper
Invoice, etc
.
Progress programs
- Print PO (5.10)
- Shipper (50.7.9.10)
- Invoice (7.13.12)
- Etc.
Is the Output To = CQ
***?
Output to Page,
Terminal or File
NoSlide12
Page
12
Visteon
Cyberquery
– QAD Forms Methodology
Page
12
Visteon
Cyberquery
– QAD Forms Methodology
QAD DB
QAD Forms
PO
Shipper
Invoice, etc
.
Progress programs
- Print PO (5.10)
- Shipper (50.7.9.10)
- Invoice (7.13.12)
- Etc.
Spooled data
(custom table “
xxcq_det
”)Is the Output To = CQ***?YesOutput to Page, Terminal or File
NoSlide13
Page
13
Visteon
Cyberquery
– QAD Forms Methodology
Page
13
Visteon
Cyberquery
– QAD Forms Methodology
QAD DB
QAD Forms
PO
Shipper
Invoice, etc
.
Progress programs
- Print PO (5.10)
- Shipper (50.7.9.10)
- Invoice (7.13.12)
- Etc.
Spooled data
(custom table “
xxcq_det
”)Is the Output To = CQ***?YesOutput to Page, Terminal or File
NoCyberquery Execution ProcessCreates CQ macro, if requiredExecutes ‘vcq.exe’ (CQ startup)Executes the CQ Macro (& the included EQ queries)Uses CSV files for label headersUses ‘cqprint.cfg’ definitionsPrints or E-mails PDF documentsDetermines macro (GCM) & Print/EM distribution?Slide14
Page
14
Visteon
Cyberquery
– QAD Forms Methodology
Page
14
Visteon
Cyberquery
– QAD Forms Methodology
QAD DB
QAD Forms
PO
Shipper
Invoice, etc
.
Progress programs
- Print PO (5.10)
- Shipper (50.7.9.10)
- Invoice (7.13.12)
- Etc.
Spooled data
(custom table “
xxcq_det
”)Is the Output To = CQ***?YesOutput to Page, Terminal or File
NoCyberquery Execution ProcessCreates Macro per GCM setupExecutes ‘vcq.exe’Runs CQ Macro (& EQ queries)Uses CSV files for label headersUses ‘cqprint.cfg’ definitionsPrints or E-mails PDF documentsDetermines macro (GCM) & Print/EM distribution?Slide15
Page
15
Visteon
Cyberquery
– Architecture
Page
15
QAD Database
Server
(UNIX)
-
Cyberquery
Windows Print Server
-
Cyberquery
Print Service (CQCS)
-
Cyberquery
Print Queues
/
cq
/7.5-0
/
cq/7.5-0/prdUS/forms
cqprint.cfgFormsCQ Temp files/etc/acs.cfgCQ Configuration-DB ConnectionsQADSlide16
Windows Print Server
Page
16
Visteon
Cyberquery
– cqprint.cfg
Page
16
“
cqprint.cfg
” –
Cyberquery
Printers configuration file
hcvlg321Slide17
Page
17
Visteon
Cyberquery
– cqprint.cfg Guidelines
Page
17
CQ likes it local:
Exact spelling requiredSlide18
Page
18
Visteon
Cyberquery
– cqprint.cfg Guidelines
con’t
Page
18
Windows server:
CQ PrinterSlide19
Page
19
Visteon
Cyberquery
– QAD Forms Examples
Page
19
List of important QAD Forms (migrated or newly developed)
Purchase Orders (discrete & supplier schedules)
Delivery Notes (Shippers) & Invoices
QAD
Picklists
(Pre-Shippers)
NA Shippers/Invoices (US to MX Southbound)
NA Shippers/Invoices (MX to US Northbound)
Supplier ASN (PO Shippers) Master Bills of Lading
Customer Master Bills of Lading
EU Odette Delivery Notes
EU
Renalt
Delivery Notes
EU VDA4939 (Germany) Delivery notes, for BMW or VW plant shipments
Russia TORG-12 (Delivery Notes) & TTN_T1 (Invoices)
RTV (Return to Vendor) Shippers/Delivery notes
AR Customer Invoices (including EU VAT, Factura & Proforma Invoices)AR Debit & Credit MemosSlide20
Page
20
Visteon
Cyberquery
– QAD Forms Examples
Page
20
Code Master (Generalized Codes
Maint
.) setupSlide21
Page
21
Visteon
Cyberquery
– QAD Forms Examples
Page
21
“CQ” Printer dummy queues – QAD Printer Setup maintenanceSlide22
Page
22
Visteon
Cyberquery
– QAD Forms Examples
Page
22Slide23
Page
23
Visteon
Cyberquery
– QAD Forms Examples
Page
23Slide24
Page
24
Visteon
Cyberquery
– QAD Forms Examples
Page
24Slide25
Page
25
Visteon
Cyberquery
– QAD Forms Examples
Page
25Slide26
Page
26
Visteon
Cyberquery
– QAD Forms Examples
Page
26Slide27
Page
27
Visteon
Cyberquery
– QAD Forms Examples
Page
27Slide28
Page
28
Visteon
Cyberquery
– QAD Forms Examples
Page
28Slide29
Page
29
Visteon
Cyberquery
– QAD Forms Examples
Page
29Slide30
Page
30
Visteon
Cyberquery
– QAD Forms Examples
Page
30Slide31
Page
31
Visteon
Cyberquery
– QAD Forms Examples
Page
31Slide32
Page
32
Visteon
Cyberquery
– QAD Forms Examples
Page
32Slide33
Page
33
Visteon
Cyberquery
– QAD Forms Examples
Page
33Slide34
Page
34
Visteon
Cyberquery
– QAD Forms Examples
Page
34Slide35
Page
35
Visteon
Cyberquery
– QAD Forms Examples
Page
35Slide36
Page
36
Visteon
Cyberquery
– QAD Forms Examples
Page
36Slide37
Page
37
Visteon
Cyberquery
– QAD Forms Examples
Page
37Slide38
Page
38
Visteon
Cyberquery
– QAD Forms Examples
Page
38Slide39
Page
39
Visteon
Cyberquery
– QAD Forms Examples
Page
39Slide40
Page
40
Visteon
Cyberquery
– QAD Reports Implemented
Page
40
Cyberquery
reports deployed in QAD .NET
Utilizing QAD user groups (roles) for accessibility
Report URL links are tagged with specific QAD user roles on QAD .NET (Tomcat) server
Configured as URL reports
Hyperlinks setup inside QAD .NET
Execute CQ in the background and run
Cyberquery
macros and queries
Run from QAD Production environments, but query data against DR databases
Key decision to avoid any potential production performance impacts
Deployed on the DR server (in standard
Cyberquery
Webreports
/Launchpad folders)
All reports formatted to open / save in Excel
Few Cyberquery reports implementedOperations Transaction browseBTS (Build to Schedule) reportContainer reports (multiple versions, by department specific filters)ASN status report &Financial year-end audit reports (ad hoc queries) &Miscellaneous (on demand) queries and reportsSlide41
Page
41
Visteon
Cyberquery
– QAD Report sample
Page
41
Click on a report (link) to run itSlide42
Page
42
Visteon
Cyberquery
– QAD Report sample
Page
42
Enter the parameters & click on the ‘Run’ iconSlide43
Page
43
Visteon
Cyberquery
– QAD Report sample
Page
43Slide44
Page
44
Visteon
Cyberquery
– QAD Report sample
Page
44Slide45
Page
45
Visteon
Cyberquery
– Web Reports Architecture
Page
45
QAD Database
Server
(DR)
-
Cyberquery
/
cq
/7.5-0
/
cq
/7.5-0/
prdUS
/
webreports
/etc/acs.cfg
CQ Configuration
-DB ConnectionsQAD
.NET UI Tomcat Server./<tomcat>/webapps/qadhome/configurations/qadprd1/menus/ plugin-menu.xmlSlide46
Page
46
Visteon
Cyberquery
– OpenEdge Replication
Page
46
Primary Machine
Users
Doing
Work
Source
Database
AI Extents
Replication
Server
Replication
Agent
Target
Database
Users
(Read-only)
Secondary MachineSlide47
Page
47
Visteon
Cyberquery
– plugins-menu.xml
Page
47
<Security>
<
SecurityConstraint
key="CQINQ" constraint="allow">^M
<Role>INQNON</Role>
<Role>INQFIN</Role>
<Role>COMMAND</Role>
<Role>PRODSUPP</Role>
</
SecurityConstraint
>
</Security>
<Properties>
<Property name="CQ" value="
Cyberscience
" type="label" />
</Properties>
<ShellMenus>
<ShellMenu key="Cyberscience" menuType="application" label="CQ Reports" >Security group identifier within this fileCorresponding to QAD user groups such as in User Group MaintenanceMenu folder in .NET UISlide48
Page
48
Visteon
Cyberquery
– plugins-menu.xml
Page
48
<
ShellMenuItem
key="Cyberscience2"
menuType
="application" label="CQ BTS Report" security="CQINQ" image="Admin“>
<Command type="
QAD.Commands.WebBrowserCommand
“>
<Parameter value="http://server.visteon.com:612/webreport_prd1/webreports/MPL/BTS_Report/bts_macro.mf?cq-report-type=xls" type="
System.Uri,System
"/>
<Property name="Title" value="
Cyberscience
"/>
</Command>
</
ShellMenuItem
><ShellMenuItem key="Cyberscience3"
menuType="application" label="CQ Operations Transaction Browse" security="CQINQ" image="Admin“><Command type="QAD.Commands.WebBrowserCommand“> <Parameter value="http://server.visteon.com:612/webreport_prd1/webreports/MPL/Ops_Trx_Browse/Ops_Trx_Browse.mf?cq-report-type=xls" type="System.Uri,System"/><Property name="Title" value="Cyberscience"/> </Command></ShellMenuItem></ShellMenus>
URL to web reportQAD menu itemMenu IDSecurity group IDSlide49
Page
49
Visteon
Cyberquery
– plugins-menu.xml
Page
49
End ResultSlide50
Page
50
Visteon
Cyberquery
– Database Considerations
Page
50
Run Against a Replicated DB
SQL Permissions
Secondary Login Broker
Run UPDATE STATISTICSSlide51
Page
51
Visteon
Cyberquery
– Project Benefits & Lessons
Page
51
Benefits & Milestones
Implemented multiple QAD forms globally across all BU’s
Forms are an Integral part of Visteon’s manufacturing, shipping and supply chain operations
All business critical (legacy) forms successfully migrated to
Cyberquery
Legacy application disabled & de-commissioned
New forms supporting regional languages and 1D & 2D barcodes implemented
Critical business reports implemented (embedded inside QAD .NET with security)
Lessons learnt
CQ license key expiration must be monitored carefully
For existing standard or custom forms, re-writing of QAD logic not recommended
Our approach uses QAD generated ‘spool file’ table to print (all forms)
CQ reporting can be deployed from inside QAD .NET or a Launchpad site
Print server / printer issues do occur randomly but work around and resolution available.Slide52
Page
52
Visteon
Cyberquery
– Q & A
Page
52
Questions
????
Vijay
Venbakkam, CPIM
QAD Consultant,
Detroit Engineered Products, Inc.
vijay_v@depusa.com
248-792-1546
Nectar Daloglou,
DBA
White Star Software, LLC
nd@wss.com
514-777-1445Slide53
Page
53
www.visteon.com