SQLVantage User Manual (English)

1. System Overview
2. Installation and Deployment (Windows / Linux)
3. Configuration
4. Administrator Guide
5. Report Design Guide (Core Chapter)
6. End-User Guide
7. Request Execution and Output
8. FAQ
9. Appendix: Data Storage, Backup, and Migration

1.1 What Is SQLVantage

SQLVantage is a web-based reporting system. The "administrator" maintains report definitions, while "end users" select a report on the web page, fill in query conditions, obtain results asynchronously, and export them as Excel / HTML / JSON / TEXT.

1.2 Technology Stack

This system has a pure web architecture: no client or plug-in needs to be installed in the browser — any modern browser can access it. On the server side, only the executable program and its accompanying configuration directories are needed to run, which makes deployment simple.

1.3 Core Concepts

1.4 User Roles

1.5 Directory Structure

The main files after extracting the release package:

SQLVantage/
├── SQLVantage.exe / sqlvantage   # Main program (Windows / Linux)
├── conf/
│   ├── app.conf        # System configuration (Port / Language / Oracle, etc.)
│   ├── data.dat        # Business database (Users / Responsibilities / Reports / Requests)
│   ├── license.dat     # License file
│   └── locale/         # Language packs
├── data/               # Runtime generated: <RequestID>.xlsx / <RequestID>.json
├── docs/               # User documentation (Multilingual, including images/)
└── tmp/                # Temporary files (Sessions, Tasks)

2.1 Environment Requirements

• Operating system: Windows 7+ / 64-bit Linux (x86_64)
• How it runs (recommended): use the released executable directly (SQLVantage.exe or the Linux binary); no runtime environment needs to be installed
• Oracle database: executing reports requires connectivity to Oracle (the system connects to Oracle EBS by default); please confirm the network and account in advance
• Disk/directory permissions: the program's working directory must be writable, because the runtime creates the data/ and tmp/ directories and reads/writes files under conf/

2.2 Installation on Windows

1. Extract: extract the release package (zip) to any directory, for example D:\SQLVantage\. After extraction, confirm that the following key files exist:

   D:\SQLVantage\
   ├── SQLVantage.exe      # Main program
   ├── conf\app.conf       # Configuration file
   ├── conf\data.dat       # Database (empty DB included in package)
   └── conf\locale\        # Language packs
   

2. (Optional) Modify the configuration: open conf\app.conf with Notepad and modify the listen address, port, Oracle connection, etc. according to Chapter 3.

3. Start the program: double-click SQLVantage.exe, or run it in the command line:

   cd D:\SQLVantage
   SQLVantage.exe
   

   After a successful startup, the console prints the version information and license status, and enters the listening state.

4. Access the system: open http://127.0.0.1:8080 in a browser (the default address; it can be modified in app.conf).

5. Firewall settings: if LAN/remote access is needed, open the corresponding port (such as 8080) in the Windows firewall:

   netsh advfirewall firewall add rule name="SQLVantage" dir=in action=allow protocol=TCP localport=8080
   

2.3 Installation on Linux

1. Extract: extract the release package (tar.gz or zip) to the target directory, for example /opt/sqlvantage:

   mkdir -p /opt/sqlvantage
   tar -xzf sqlvantage-linux-amd64.tar.gz -C /opt/sqlvantage
   cd /opt/sqlvantage
   

2. Grant the execute permission:

   chmod +x sqlvantage
   

3. (Optional) Modify the configuration: edit conf/app.conf (same as on Windows).

4. Test the foreground startup:

   ./sqlvantage
   

   When you see the version information and the listening log, startup succeeded; press Ctrl+C to stop.

5. Run in the background (systemd or nohup is recommended):

   Method A: nohup

   cd /opt/sqlvantage
   nohup ./sqlvantage > sqlvantage.log 2>&1 &
   

   Method B: systemd (create /etc/systemd/system/sqlvantage.service):

   [Unit]
   Description=SQLVantage Report System
   After=network.target
   
   [Service]
   WorkingDirectory=/opt/sqlvantage
   ExecStart=/opt/sqlvantage/sqlvantage
   Restart=always
   RestartSec=5
   User=sqlvantage
   
   [Install]
   WantedBy=multi-user.target
   

   Then execute:

   systemctl daemon-reload
   systemctl enable sqlvantage
   systemctl start sqlvantage
   systemctl status sqlvantage
   

6. Firewall / security group: open the port (such as 8080):

   firewall-cmd --permanent --add-port=8080/tcp && firewall-cmd --reload
   

2.4 Building from Source (Optional)

Available only to users who have obtained the source code: run the build command in the source directory to produce an executable for the current platform. For a formal deployment environment, it is recommended to use the officially released executable directly.

2.5 First Startup

On the first startup, the system automatically completes the following initialization:

1. Check the data file: reads conf/data.dat (shipped with the package; if it is missing, the program prompts "conf/data.dat is not found" and exits — please do not delete this file).
2. Create tables automatically: the four tables user, responsibility, report, and request are created automatically.
3. Create the administrator account automatically: on the first visit to /admin/login, if the user root does not exist, the system creates it automatically:
   • User name: root
   • Initial password: SQLVantage
   • Role: admin (administrator)
   • Security reminder: change this password immediately after the first login (the administrator can change it under "User Management").
4. Check the license file: if conf/license.dat is missing or invalid, the console prints a warning; the system can still run, but is subject to the license restrictions in Section 4.7.

3.1 Configuration File Location

The configuration file is conf/app.conf (INI format). There are two ways to modify it:

• Method 1 (recommended, via the UI): after logging in as an administrator, go to "System Settings" (/admin/setting), fill in the values and save; the system writes back to app.conf automatically.
• Method 2 (edit the file directly): modify conf/app.conf with a text editor, then restart the program.

3.2 Parameter Reference Table

3.3 When Changes Take Effect

• sessiongcmaxlifetime: takes effect immediately after saving.
• Other parameters (port, Oracle, etc.): the program must be restarted for the changes to take effect.

3.4 Language Switching

• The system has 12 built-in languages: zh-CN, zh-TW, en-US, ja-JP, ko-KR, fr-FR, de-DE, es-ES, th-TH, vi-VN, ru-RU, pt-PT.
• How to switch: add ?lang=zh-CN to the URL (e.g. /?lang=zh-CN), or switch via the language menu in the upper-right corner of the admin console; after selection, it is written to a Cookie valid for 1 year.

4.1 Administrator Login

1. In a browser, visit http://<server address>:<port>/admin/login.
2. Log in with the administrator account (initially root / SQLVantage).
3. After a successful login, the admin console (/admin) opens; the left menu contains: Report Management, Responsibility Management, User Management, Request Management, License Management, System Settings, Dashboard, About.

Note: the administrator account must satisfy Role = admin and Status = active; otherwise it cannot log in to the admin console.

4.2 Dashboard and Top Navigation

• The top navigation provides quick jumps: Dashboard (/admin), Report Execution (/request, new window), and Portal Home (/).
• In the upper-right corner, you can switch the language or log out (/admin/logout).

4.3 User Management

Entry: /admin/user (left menu "User Management").

User field reference:

Operations:

• Create: click the "New" button → fill in user name/email/role/status/password/confirm password → submit.
• Edit: click "Edit" on the row → email, role, and status can be changed; leaving the password blank means no change.
• Delete: click "Delete" on the row. Note:
  • The root account cannot be deleted;
  • A user who owns reports cannot be deleted (their reports must be deleted or transferred first).

Management tips:

• The end-user login entry is /login (home page), and the administrator login entry is /admin/login; the two are different.
• Setting an end user to inactive is enough to block their login; there is no need to delete the account.
• root is automatically hidden in the user list.

4.4 Responsibility Management

Entry: /admin/responsibility (left menu "Responsibility Management").

Responsibility field reference:

Operations:

• Create: click "New" → select a responsibility of a user from the dropdown (the data comes from the Oracle EBS query interface /api/user/responsibilities/); after selection, RespId / RespKey / Name are filled in automatically; it can also be filled in manually → submit.
• Edit / Delete: row buttons. Note: a responsibility referenced by a report cannot be deleted.

Purpose: every report must belong to a responsibility; the "Report Menu" on the end-user side is grouped by responsibility (reports without a responsibility are placed in the "Uncategorized" group).

4.5 Report Management

Entry: /admin/report (left menu "Report Management").

Report fields:

Report lifecycle (important):

Draft（草稿，设计阶段）──▶ Release（已发布，用户可见）
       │                        │
       │                        └──▶ 不可直接删除，需先改为 Draft/Discard
       └──▶ Discard（废弃，用户不可见）

• Only reports with status Release appear in the report menu on the end-user side.
• Released (Release) reports cannot be deleted; first change the status to Draft or Discard in the list, then delete.
• The "Name", "Description", and "Status" columns support double-clicking a cell to edit directly (auto-saved).

Operations:

4.6 Request Management (Administrator View)

Entry: /admin/request (left menu "Request Management").

Administrators can view the report requests of all users (end users can only see their own), and can:

• View by report name/status/phase;
• View parameters, submitter, IP address, and created/completed time;
• Use the "Output" dropdown to directly download the Excel / HTML / JSON / TEXT result of a request;
• Delete individually, or check multiple and "batch delete".

For the meaning of request statuses, see Chapter 7.

4.7 License Management

Entry: /admin/license (left menu "License Management").

4.7.1 What Is a License File

The license file is conf/license.dat, a short piece of text issued by the vendor, containing the following license information:

The system automatically validates the license file at startup and on import; any tampering (modifying the registration information or the expiration date) will make the license invalid.

4.7.2 Purchase Process

1. Contact the SQLVantage vendor/developer and provide the following information:
   • Organization/company name (company);
   • The server registration ID to be licensed (reg_id, assigned by the vendor);
   • The desired license period.
2. The vendor generates a license file (a piece of text) with a license generation tool and delivers it to the customer.
3. After receiving the file, the customer imports it as described in 4.7.3.

4.7.3 Importing a License

1. Log in as an administrator → License Management (/admin/license).
2. The page shows the current license status (registration ID / company / expiration date; a red warning is shown when invalid or missing).
3. Click "Choose File" to select the received license file (it can be named arbitrarily, e.g. license.dat) → click "Import".
4. After a successful import, the system validates and refreshes the page automatically, showing the valid license information.

It can also be placed manually: save the content of the license file as conf/license.dat, then restart the program.

4.7.4 Restrictions Without a Valid License / After Expiration

4.8 System Settings

Entry: /admin/setting (left menu "System Settings"); visually edit conf/app.conf:

• Application settings: listen address, port, run mode, default language, maximum execution time;
• Session settings: session expiration time (seconds);
• Oracle database settings: server, port, service name, user name, password (with a plaintext/ciphertext toggle button).

After saving, some parameters take effect immediately; parameters such as the port require a program restart.

4.9 About

Entry: /admin/aboutus; view the system version, release information, etc.

This is the most important feature of SQLVantage. A report consists of three parts:

• SQL: defines which data to query (data source SQL + column metadata configuration)
• FORM: defines which query conditions users fill in (parameter form)
• HTML: defines how the results are displayed (table / chart / KPI card layout)

Each of the three has two pieces of data — "code" and "format JSON" — which are finally saved to the report record.

5.1 Designer Workbench

5.1.1 Entering the Designer

1. Log in as an administrator → Report Management (/admin/report).
2. Find the target report and click the "Code" button (purple).
3. A large designer window pops up (about 98% of the screen), with the interface split into left and right panes:

┌────────────────────────────────────────────────────────┐
│ [下拉：SQL设计 | FORM设计 | HTML设计]   [保存全部]        │
├───────────────────────────────┬────────────────────────┤
│ 左侧：代码编辑器                │ 右侧：动态设计面板       │
│ （SQL 代码 / FORM 代码 /        │ （随左侧模式切换）      │
│  HTML 代码 共用一个编辑器）     │   · SQL: 列元数据配置表  │
│                               │   · FORM: 参数配置表    │
│                               │   · HTML: 布局块配置表  │
└───────────────────────────────┴────────────────────────┘

5.1.2 The Three Modes

The dropdown at the top switches the design mode; the left editor and the right panel switch in sync:

5.1.3 Saving

• While designing: changes in the right panel are automatically written back to the hidden fields (sql_code/sql_format/form_code/form_format/html_code/html_format).
• Formal save: click the "Save All" button in the upper-left corner to submit all six pieces of data to /admin/report/code/ and save them to the database.

Remember: after editing SQL / FORM / HTML, be sure to click "Save All"; otherwise the changes will be lost when the window is closed.

5.2 SQL Module (Designing the Report Data Source)

5.2.1 Writing the Query SQL

• The SQL uses Oracle syntax; write a SELECT statement directly (FROM/JOIN/WHERE/GROUP BY, etc. can be included).

• Query conditions use named parameter placeholders :parameter name, and the parameter name must match the field defined in the FORM module. For example, if the parameter P_OU_ID is defined in FORM, write the following in SQL:

  SELECT company_name, ou_id, amount
    FROM fnd_ou_tl
   WHERE ou_id = :P_OU_ID
  

• All column names selected in the SQL are the field identifiers of the Excel export and the HTML page table (it is recommended to use uppercase uniformly, e.g. COMPANY_NAME).

5.2.2 Column Metadata Configuration Table (Key: Excel Export)

The "SQL Column Metadata Configuration" table on the right has one row for each column output by the SQL:

Operations: click "Add Row" to add a column → double-click a cell to fill it in → a JSON snapshot is generated automatically (the black code preview area on the right), and is written back to sql_format in real time.

5.2.3 Mapping Between SQL Column Configuration and Excel Export

The system generates the Excel (xlsx) file in the background according to the following mapping rules:

In other words: the SQL column configuration table is the complete definition of the "header + column type + number format + alignment" of the Excel export. Even without any configuration, Excel can still be exported (text type and left-aligned by default, with the original column names as headers), but the exported Excel is more professional after configuration.

5.2.4 A Complete SQL Design Example

Suppose we want to build a "Department Cost Report":

1. SQL code (editor):

   SELECT DEPT_NAME, MONTH, TOTAL_AMOUNT, RATE
     FROM DEPT_COST_V
    WHERE MONTH = :P_MONTH
    ORDER BY DEPT_NAME
   

2. Column metadata configuration:

   field	title	type	precision	format	align
   DEPT_NAME	Department Name	text			left
   MONTH	Month	date		yyyy-mm	center
   TOTAL_AMOUNT	Total Amount	number	2	#,##0.00	right
   RATE	Cost Ratio	percent	2	0.00%	right

3. The exported Excel effect: the headers are "Department Name / Month / Total Amount / Cost Ratio"; amounts are right-aligned with thousands separators and 2 decimal places, and the ratio is displayed as a percentage.

5.3 FORM Module (Designing the Query Parameter Form)

5.3.1 Parameter Configuration Table

Each row of the "Parameter Configuration Table" on the right defines a query parameter:

Component type table:

5.3.2 Parameter Cascading (Dependent Filtering)

• api_url / query_sql support {variable name} placeholders: when the user changes an upstream parameter (such as selecting an organization), the system automatically replaces the placeholder with the actual value in the current form and dynamically requests the downstream dropdown options.
• If the upstream parameter is not filled in, the downstream dropdown shows "Please complete the filters above first" and clears its options, avoiding dirty data.
• Choose one of the static dropdown (static_options) and the dynamic dropdown (api_url / query_sql).

Data format requirement for dynamic dropdown options: each record returned by the API/SQL must contain two fields: val (value) and txt (display text).

5.3.3 Live Preview and Code Generation

• Below the table is the "Live Preview Area": the form (including date controls, cascading dropdowns, etc.) is rendered in real time as parameters are configured.
• The "Form Code Draft" text box at the bottom generates the complete FORM HTML code in real time.
• Click the "Copy and Apply" button: the draft code is written into the editor (FORM mode) and synchronized to form_code / form_format.

You can also skip the right panel and hand-write FORM HTML (form syntax) directly in the left editor; it takes effect on save as well.

5.3.4 Runtime Behavior

After an end user submits the form, the system binds the form data to the SQL as named parameters and executes it; the parameters are also recorded in the request, so that the result page / exported file can echo the query conditions.

5.4 HTML Module (Designing the Result Display)

5.4.1 Layout Block Configuration Table

Each row of the "HTML View Component Configuration" table on the right defines a display block:

Component reference:

5.4.2 Layout Preview and Code Generation

• The "Live Layout Preview" area displays a high-fidelity skeleton of each block (title + block type + width) in real time.
• "Html Code Draft" generates the complete HTML code in real time (including runtime attributes such as data-component, data-subtotal, data-charttype, data-xfield, data-yfields).
• Click "Copy and Apply" to write it into the editor and synchronize html_code / html_format.

You can also hand-write HTML template fragments directly in the left editor (common template syntax is supported). For the data objects available to the template at render time, see 5.4.3.

5.4.3 Result Page Rendering Mechanism

When an end user downloads/views an HTML result (/request/output?ext=html), the system renders the report HTML code together with the query result JSON, column configuration, etc.:

The page automatically renders containers with data-component="table" as data tables, chart as charts, and card as KPI cards.

5.5 Report Publishing Process (Recommended Administrator Workflow)

1. 报表管理 → 新建报表（选择职责、填名称、状态选 Draft）
2. 点「代码」进入设计器
3. SQL 设计：写查询 SQL + 配置列元数据（Excel 导出依据）
4. FORM 设计：配置查询参数（与 SQL 参数一一对应）
5. HTML 设计：配置展示布局（表格/图表/指标卡）
6. 点「保存全部」→ 关闭设计器
7. 回到报表列表，把状态改为 Release（发布）
8. 普通用户登录即可在报表菜单中看到该报表并执行

5.6 Design Notes

• The parameter names in SQL and FORM must match exactly (SQL uses :parameter name, FORM uses field).
• SQL column names should be uppercase; the column metadata configuration table converts field to uppercase automatically.
• The report SQL must be precompilable by Oracle (db.Prepare); syntax errors will cause the request execution to fail (Status Error).
• Without a valid license, the report limit is 3; confirm the license status before designing.
• After saving, execute a request once in "Request Management" or on the end-user side to verify that the SQL and the display are correct.

6.1 Login

Open the system home page in a browser (default http://<server address>:<port>/), and click the "User Login" card to enter the login page /login. The login page provides two login methods (tab switch):

Method 1: ERP Verification (Oracle EBS Single Sign-On)

1. Switch to the "ERP Verification" tab.
2. Enter the EBS user name (e.g. APPS) and click "Verify Login".
3. The system checks via Oracle whether the user's session in the EBS icx_sessions table is valid (30-minute valid window); if valid, the user enters the system directly.
4. Features: no local password is required; the login identity is the user's identity in EBS.

Method 2: Local Account

1. Switch to the "Local Account" tab.
2. Enter the user name and password assigned by the administrator, and click "Login".
3. Features: the account is created by the administrator under "User Management".

Note: an account set to inactive (departed) by the administrator cannot log in.

6.2 Portal Home Page

After a successful login, the portal home page (/) opens, including:

• Top: a welcome message (user name) and the Change Password entry.
• Navigation cards:
  • New Report Request (enters the report execution page /request);
  • My Requests (view historical requests and results);
  • Cards such as Administrator Login, System Settings, License Management, and About (not needed by end users; clicking them redirects to the administrator login page).
• In the upper-right corner, you can switch the UI language.

6.3 Creating a New Report Request

1. On the portal home page, click the "New Report Request" card, or visit /request directly.
2. On the request list page, click the "New" button.
3. The "Report Menu" window pops up: reports are grouped by responsibility (module), and only released (Release) reports are shown; you can also search by name in the dropdown at the top.
4. Click a report, and its parameter form loads on the right.
5. Fill in the query conditions (date/dropdown/text, etc.) and click "Submit".
6. After a successful submission, a new record appears in the request list with Status Queued.

6.4 My Request List

Entry: /request (also reachable from the portal card at the top).

Operations: click the "Output" dropdown on a row and choose Excel / HTML / JSON / TEXT to open or download the result file in the corresponding format in a new window.

6.5 Changing the Password

1. Click "Change Password" at the top of the portal home page.
2. Fill in the current password, the new password, and confirm the new password (the new password must be at least 6 characters).
3. After a successful submission, you can log in with the new password (only valid for local-account login; the ERP verification method is unrelated to the EBS password).

7.1 Execution Flow (Asynchronous)

用户提交请求
   │
   ▼
写入 request 表（Phase=Pending, Status=Queued）
   │
   ▼
后台轮询协程（每 3 秒）拉取排队任务（每批最多 5 个，并发最多 3 个）
   │
   ▼
乐观锁抢占任务 → Phase=Running, Status=Processing
   │
   ▼
解析参数 → 读取报表 SQL → Oracle 预编译执行
   │
   ├── 失败 → Status=Error（记录错误消息）
   │
   ▼
生成结果文件：data/<请求ID>.xlsx 与 data/<请求ID>.json
   │
   ▼
Phase=Completed, Status=Success

• Timeout protection: tasks that run longer than max_execution_time (default 30 minutes) are automatically marked as Terminated.
• Result files are saved in the data/ directory by request ID and never overwrite each other.

7.2 Output Formats

7.3 Permission Control

• End users can only see requests submitted by themselves (filtered by creator).
• Administrators can view the requests of all users in "Request Management".
• When an ERP-logged-in user submits, if the form contains organization permission parameters such as P_OU_ID / P_ORG_ID, the system validates whether the value is within the OU/ORG range that the user owns in EBS; out-of-scope values are rejected.

Q1: The startup reports "conf/data.dat is not found"? The database file is missing. Please confirm that the package was fully extracted and conf/data.dat exists; do not replace it with an empty file — use the database file shipped with the release package.

Q2: The page cannot be opened in the browser? Confirm that the program is running; confirm that httpaddr/httpport are configured correctly (default 127.0.0.1:8080 — local access works, while access from other machines requires 0.0.0.0 and an open firewall port).

Q3: The login says the account or password is wrong?

• Local account: confirm that the user name/password is correct and the status is active.
• Administrator: confirm that the role is admin.
• Forgot the password: ask the administrator to reset it under "User Management".

Q4: The request status stays Error after submission? Mostly a SQL syntax error or an Oracle connection problem. Check the error message in the "Message" column of the request: Prepare statement failed means SQL precompilation failed; Oracle connection failed means the database connection configuration is wrong (check the oracle_* parameters in app.conf).

Q5: The report cannot be seen in the end-user menu? Confirm that the report status is Release (only released reports are visible).

Q6: The request stays Running for a long time? Tasks running longer than max_execution_time minutes are terminated automatically. You can increase this parameter appropriately and restart.

Q7: Creating a report says the license limit has been reached? No valid license file has been imported (or it has expired); without a valid license, at most 3 reports are allowed. Please import the license as described in Section 4.7.

Q8: The Excel export headers are English column names? title was not filled in the SQL column metadata configuration, or it does not match the SQL output column names. In the column configuration table of "SQL design", fill field to match the SQL output column and fill in title.

Q9: The system still uses the old password after the Oracle password was changed? After modifying oracle_password in conf/app.conf, the program must be restarted.

Q10: The port change does not take effect? app.conf requires a restart after modification; you can also modify it on the "System Settings" page (a restart is equally required for it to take effect).

9.1 Data Storage Locations

9.2 Backup Recommendations

• Minimal backup set: conf/ (app.conf + data.dat + license.dat) + optional data/ (historical results).
• Full backup: the entire program directory (including conf/ and data/).

9.3 Migrating to a New Server

1. Deploy the same version of the program on the target machine as described in Chapter 2.
2. Stop the old program → copy conf/ (app.conf, data.dat, license.dat) to the corresponding location on the new machine.
3. If historical results are needed, copy data/ as well.
4. Start the new program and check whether the configuration (Oracle address, port, etc.) is suitable for the new environment.

9.4 Migrating This Document (Multilingual) and Reading Online

• This manual is stored in the docs/ directory of the program, named by language: zh-CN.md, zh-TW.md, en-US.md, ja-JP.md, ko-KR.md, fr-FR.md, de-DE.md, es-ES.md, th-TH.md, vi-VN.md, ru-RU.md, pt-PT.md.
• Images are stored uniformly in the docs/images/ directory, and the document references them using local relative paths (e.g. ![schematic](images/xxx.png)), with no third-party remote image links. When migrating the document, copy the docs/images/ directory together with it, so the images travel with the document.
• Reading online: all .md files are pure Markdown and can be rendered and read online directly in document repositories on platforms such as Gitea, GitLab, and Gitee, without any additional tools.
• The content of each language version is identical; see the index in docs/README.md for the entry point.