| Subject | Re: [ib-support] Firebird RC1 Backup & Restore Problem |
|---|---|
| Author | Raf Schandevyl |
| Post date | 2001-11-25T00:09:27Z |
This is the error I'm getting.
gbak: restoring privilege for user SYSDBA
gbak: ERROR: attempt to store duplicate value (visible to active
transactions) i
n unique index "RDB$INDEX_11"
gbak: Exiting before completion due to errors
I attached the script for creating the DB. Try to create it, then
backup and restore it and you'll get the error.
Regards,
Raf Schandevyl
Claudio Valderrama C. wrote:
SET SQL DIALECT 3;
CREATE DATABASE 'localhost:E:\Databanken\Incasso\v1\incasso1.gdb'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 8192
DEFAULT CHARACTER SET ISO8859_1;
/* Domains definitions */
CREATE DOMAIN DOM_ACCOUNT_NR AS VARCHAR (25) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_ADDRESS AS VARCHAR (200) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_BOOLEAN AS SMALLINT
DEFAULT 0
NOT NULL;
CREATE DOMAIN DOM_BTW_NR AS CHAR (15) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_CITY AS VARCHAR (150) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_CODE AS VARCHAR (20) CHARACTER SET ISO8859_1
NOT NULL;
CREATE DOMAIN DOM_CONDITION_TYPE AS CHAR (1) CHARACTER SET ISO8859_1
NOT NULL;
CREATE DOMAIN DOM_COUNT AS INTEGER
DEFAULT 0
NOT NULL;
CREATE DOMAIN DOM_CURRENCY_CODE AS CHAR (3) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_CURRENT_DATE AS DATE
DEFAULT CURRENT_DATE;
CREATE DOMAIN DOM_CURRENT_USER AS VARCHAR (20) CHARACTER SET ISO8859_1
DEFAULT USER;
CREATE DOMAIN DOM_DATE AS DATE;
CREATE DOMAIN DOM_DESCRIPTION AS VARCHAR (100) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_EMAIL AS VARCHAR (100) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_FILE_NAME AS VARCHAR (200) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_FILE_NR AS INTEGER
DEFAULT 1
NOT NULL;
CREATE DOMAIN DOM_INVOICENR AS INTEGER
NOT NULL;
CREATE DOMAIN DOM_LINK_TYPE AS SMALLINT;
CREATE DOMAIN DOM_MONEY AS NUMERIC (18, 3)
DEFAULT 0
NOT NULL;
CREATE DOMAIN DOM_NAME AS VARCHAR (200) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_OPERATION_CODE AS CHAR (8) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_PAYEMENT_TYPE AS CHAR (1) CHARACTER SET ISO8859_1
NOT NULL;
CREATE DOMAIN DOM_PERCENT AS NUMERIC (5, 2)
DEFAULT 0
NOT NULL;
CREATE DOMAIN DOM_PHONE AS VARCHAR (12) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_RECORD_ID AS INTEGER;
CREATE DOMAIN DOM_REFERENCE AS VARCHAR (50) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_REMARKS AS BLOB sub_type 1 segment size 1 CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_SIGN AS SMALLINT
DEFAULT 0
NOT NULL;
CREATE DOMAIN DOM_STATE AS SMALLINT;
CREATE DOMAIN DOM_STATEMENT AS VARCHAR (255) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_STRUCTURED_STATEMENT AS CHAR (12) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_TEXT AS BLOB sub_type 1 segment size 1 CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_TRANSACTION_GROUP AS CHAR (2) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_TRANSACTION_OPERATION AS CHAR (2) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_TRANSACTION_TYPE AS CHAR (1) CHARACTER SET ISO8859_1
NOT NULL;
CREATE DOMAIN DOM_VAT_NO AS VARCHAR (20) CHARACTER SET ISO8859_1;
CREATE DOMAIN SEQUENCE_NO AS SMALLINT
DEFAULT 0
NOT NULL;
/* Generators definitions */
CREATE GENERATOR ACCOUNTS$NEXT_ID ;
CREATE GENERATOR CLIENTS$NEXT_ID ;
CREATE GENERATOR CODA$NEXT_ID ;
CREATE GENERATOR FILES$NEXT_ID ;
CREATE GENERATOR FINANCE$NEXT_ID ;
CREATE GENERATOR INVOICES$NEXT_ID ;
CREATE GENERATOR RDB$SECURITY ;
CREATE GENERATOR REPORTS$NEXT_ID ;
CREATE GENERATOR SUPPORT$NEXT_ID ;
CREATE GENERATOR SYSTEM$NEXT_ID ;
CREATE GENERATOR TEST$NUMPART_RECORD_ID_GEN ;
/* External functions definition */
DECLARE EXTERNAL FUNCTION UDF_SUBSTR
CSTRING (200),
SMALLINT,
SMALLINT
RETURNS CSTRING (200) FREE_IT
ENTRY_POINT 'IB_UDF_substr' MODULE_NAME 'ib_udf';
/* Tables definitions */
CREATE TABLE ACCOUNTS$ACCOUNTS (
ACCOUNT_ID DOM_RECORD_ID NOT NULL,
CODE DOM_CODE NOT NULL collate ISO8859_1,
DESCRIPTION DOM_DESCRIPTION collate ISO8859_1,
ACCOUNT_NR DOM_ACCOUNT_NR collate ISO8859_1,
CODA_IMPORT DOM_BOOLEAN
DEFAULT 1 NOT NULL,
INVOICEABLE DOM_BOOLEAN
DEFAULT 1 NOT NULL);
CREATE TABLE ACCOUNTS$CODA (
CODA_ID DOM_RECORD_ID NOT NULL,
FILE_NAME DOM_FILE_NAME NOT NULL collate ISO8859_1,
CREATION_DATE DOM_CURRENT_DATE,
CREATION_USER DOM_CURRENT_USER collate ISO8859_1,
STATE_ID DOM_STATE);
CREATE TABLE ACCOUNTS$JOURNAL (
JOURNAL_ID DOM_RECORD_ID NOT NULL,
TRANSACTION_ID DOM_RECORD_ID NOT NULL,
ACCOUNT_ID DOM_RECORD_ID NOT NULL,
AMOUNT DOM_MONEY NOT NULL,
DESCRIPTION DOM_DESCRIPTION collate ISO8859_1,
FILE_ID DOM_RECORD_ID,
INVOICED DOM_BOOLEAN NOT NULL,
VALUTA_DATE DOM_CURRENT_DATE);
CREATE TABLE ACCOUNTS$TRANSACTIONS (
TRANSACTION_ID DOM_RECORD_ID NOT NULL,
CODA_ID DOM_RECORD_ID,
CREATION_DATE DOM_CURRENT_DATE,
CREATION_USER DOM_CURRENT_USER collate ISO8859_1,
DESCRIPTION DOM_DESCRIPTION collate ISO8859_1);
CREATE TABLE CLIENTS$CLIENTS (
CLIENT_ID DOM_RECORD_ID NOT NULL,
CODE DOM_CODE NOT NULL collate ISO8859_1,
NAME DOM_NAME collate ISO8859_1,
ADDRESS DOM_ADDRESS collate ISO8859_1,
ZIP_CODE_ID DOM_RECORD_ID NOT NULL,
INVOICE_NAME DOM_NAME collate ISO8859_1,
INVOICE_ADDRESS DOM_ADDRESS collate ISO8859_1,
INVOICE_ZIP_ID DOM_RECORD_ID,
VAT_NO DOM_VAT_NO collate ISO8859_1,
LANGUAGE_ID DOM_RECORD_ID NOT NULL,
E_MAIL DOM_EMAIL collate ISO8859_1,
BANK_ACCOUNT_NR DOM_ACCOUNT_NR collate ISO8859_1,
CURRENT_INTREST_PERCENT DOM_PERCENT NOT NULL,
CURRENT_COST_PERCENT DOM_PERCENT NOT NULL,
CURRENT_COST_MINIMUM DOM_MONEY NOT NULL,
FILE_COUNT DOM_COUNT,
OPEN_FILES DOM_COUNT NOT NULL,
TELEPHONE DOM_PHONE collate ISO8859_1,
TELEFAX DOM_PHONE collate ISO8859_1,
CONTACT DOM_NAME collate ISO8859_1);
CREATE TABLE CLIENTS$CONDITIONS (
RECORD_ID DOM_RECORD_ID NOT NULL,
CLIENT_ID DOM_RECORD_ID,
CONDITION_TYPE DOM_CONDITION_TYPE NOT NULL collate ISO8859_1,
FROM_DATE DOM_CURRENT_DATE,
END_VALUE DOM_MONEY NOT NULL,
INVOICE_PERCENT DOM_PERCENT NOT NULL,
INVOICE_MINIMUM DOM_MONEY NOT NULL);
CREATE TABLE CODA$BOOKING (
RECORD_ID DOM_RECORD_ID NOT NULL,
TRANSACTION_ID DOM_RECORD_ID,
FILE_ID DOM_RECORD_ID,
AMOUNT DOM_MONEY NOT NULL);
CREATE TABLE CODA$FILES (
FILE_ID DOM_RECORD_ID NOT NULL,
FILE_NAME DOM_FILE_NAME NOT NULL collate ISO8859_1);
CREATE TABLE CODA$FILE_SECTIONS (
SECTION_ID DOM_RECORD_ID NOT NULL,
FILE_ID DOM_RECORD_ID NOT NULL,
CREATION_DATE DOM_DATE NOT NULL,
SEQUENCE_NO SEQUENCE_NO NOT NULL,
ACCOUNT_NO DOM_ACCOUNT_NR NOT NULL collate ISO8859_1,
AMOUNT DOM_MONEY NOT NULL,
AMOUNT_DATE DOM_DATE NOT NULL,
ACCOUNT_ID DOM_RECORD_ID);
CREATE TABLE CODA$TRANSACTIONS (
TRANSACTION_ID DOM_RECORD_ID NOT NULL,
SECTION_ID DOM_RECORD_ID NOT NULL,
AMOUNT DOM_MONEY NOT NULL,
VALUTA_DATE DOM_DATE NOT NULL,
STRUCTURED_STATEMENT DOM_BOOLEAN NOT NULL,
"STATEMENT" DOM_STATEMENT collate ISO8859_1,
BANK_REFERENCE DOM_CODE NOT NULL collate ISO8859_1,
OPERATION_CODE DOM_OPERATION_CODE collate ISO8859_1,
ACCOUNT_NO DOM_ACCOUNT_NR collate ISO8859_1,
NAME DOM_NAME collate ISO8859_1,
ADDRESS DOM_NAME collate ISO8859_1,
CITY DOM_NAME collate ISO8859_1,
PARENT_ID DOM_RECORD_ID,
BOOKED DOM_BOOLEAN NOT NULL,
HAS_DETAILS DOM_BOOLEAN NOT NULL,
CLIENT_REFERENCE DOM_DESCRIPTION collate ISO8859_1,
ORIGINAL_AMOUNT DOM_MONEY NOT NULL,
ORIGINAL_CURRENCY DOM_CURRENCY_CODE collate ISO8859_1,
LINK_TYPE DOM_STATE,
ACTION_CODE DOM_STATE,
CHECKED_STATEMENT DOM_STATEMENT collate ISO8859_1,
DOSSIERNR DOM_STATEMENT collate ISO8859_1,
CHECKED DOM_BOOLEAN NOT NULL);
CREATE TABLE CODA$TRANSACTIONS_OPERATIONS (
OPERATION_ID DOM_RECORD_ID NOT NULL,
GROUP_ID DOM_RECORD_ID NOT NULL,
CODE DOM_TRANSACTION_OPERATION NOT NULL collate ISO8859_1,
DESCRIPTION DOM_DESCRIPTION collate ISO8859_1);
CREATE TABLE CODA$TRANSACTION_GROUP (
GROUP_ID DOM_RECORD_ID NOT NULL,
CODE DOM_TRANSACTION_GROUP NOT NULL collate ISO8859_1,
DESCRIPTION DOM_DESCRIPTION collate ISO8859_1);
CREATE TABLE CODA$TRANSACTION_TYPES (
TYPE_ID DOM_RECORD_ID NOT NULL,
CODE DOM_TRANSACTION_TYPE NOT NULL collate ISO8859_1,
DESCRIPTION DOM_DESCRIPTION collate ISO8859_1);
CREATE TABLE FILES$ACTIONS (
ACTION_ID DOM_RECORD_ID NOT NULL,
CODE DOM_CODE NOT NULL collate ISO8859_1,
DESCRIPTION DOM_DESCRIPTION collate ISO8859_1,
AMOUNT DOM_MONEY NOT NULL,
INVOICEABLE DOM_BOOLEAN NOT NULL,
SYSTEM DOM_BOOLEAN NOT NULL);
CREATE TABLE FILES$COSTS (
COST_ID DOM_RECORD_ID NOT NULL,
CODE DOM_CODE NOT NULL collate ISO8859_1,
DESCRIPTION DOM_DESCRIPTION collate ISO8859_1,
INVOICEABLE DOM_BOOLEAN
DEFAULT 1 NOT NULL,
SYSTEM DOM_BOOLEAN NOT NULL);
CREATE TABLE FILES$DEPTORS (
DEPTOR_ID DOM_RECORD_ID NOT NULL,
NAME DOM_NAME collate ISO8859_1,
ADDRESS DOM_ADDRESS collate ISO8859_1,
ZIP_CODE_ID DOM_RECORD_ID,
LANGUAGE_ID DOM_RECORD_ID NOT NULL,
CREATION_DATE DOM_CURRENT_DATE,
CREATION_USER DOM_CURRENT_USER collate ISO8859_1,
E_MAIL DOM_EMAIL,
TITLE_ID DOM_RECORD_ID,
OLD_ID DOM_RECORD_ID NOT NULL,
TELEPHONE DOM_PHONE collate ISO8859_1,
TELEFAX DOM_PHONE collate ISO8859_1,
FILE_COUNT DOM_COUNT NOT NULL,
OPEN_FILES DOM_COUNT NOT NULL);
CREATE TABLE FILES$FILES (
FILE_ID DOM_RECORD_ID NOT NULL,
CLIENT_ID DOM_RECORD_ID NOT NULL,
FILE_NR DOM_FILE_NR,
DEPTOR_ID DOM_RECORD_ID NOT NULL,
REFERENCE DOM_REFERENCE,
COLLECTOR_ID DOM_RECORD_ID NOT NULL,
STATE_ID DOM_RECORD_ID NOT NULL,
CREATION_DATE DOM_CURRENT_DATE,
CREATION_USER DOM_CURRENT_USER,
DATE_CLOSED DOM_DATE,
AMOUNT DOM_MONEY NOT NULL,
COSTS DOM_MONEY
DEFAULT 0 NOT NULL,
INTEREST DOM_MONEY
DEFAULT 0 NOT NULL,
TOTAL COMPUTED BY (AMOUNT + COSTS + INTEREST),
PAYED_AMOUNT DOM_MONEY NOT NULL,
PAYED_INTEREST DOM_MONEY NOT NULL,
PAYED_COSTS DOM_MONEY NOT NULL,
PAYED_UNKNOWN DOM_MONEY NOT NULL,
PAYED_TOTAL COMPUTED BY (PAYED_AMOUNT + PAYED_INTEREST + PAYED_COSTS + PAYED_UNKNOWN),
PAYABLE COMPUTED BY (AMOUNT + COSTS + INTEREST - PAYED_AMOUNT - PAYED_INTEREST - PAYED_INTEREST),
CREATION_MONTH COMPUTED BY (EXTRACT(MONTH FROM CREATION_DATE)),
CREATION_YEAR COMPUTED BY (EXTRACT(YEAR FROM CREATION_DATE)),
SALDO_AMOUNT COMPUTED BY (AMOUNT - PAYED_AMOUNT),
SALDO_INTEREST COMPUTED BY (INTEREST - PAYED_INTEREST),
SALDO_COSTS COMPUTED BY (COSTS - PAYED_COSTS),
REFUND_STATEMENT DOM_STRUCTURED_STATEMENT collate ISO8859_1,
STRUCTURED_STATEMENT DOM_STRUCTURED_STATEMENT collate ISO8859_1,
LAST_ACTION_DATE DOM_CURRENT_DATE,
INVOICED DOM_BOOLEAN
DEFAULT 1 NOT NULL);
CREATE TABLE FILES$FILE_ACTIONS (
FILE_ACTION_ID DOM_RECORD_ID NOT NULL,
FILE_ID DOM_RECORD_ID NOT NULL,
ACTION_ID DOM_RECORD_ID NOT NULL,
REMARKS DOM_REMARKS,
ACTION_DATE DOM_CURRENT_DATE,
ACTION_USER DOM_CURRENT_USER collate ISO8859_1);
CREATE TABLE FILES$FILE_COSTS (
RECORD_ID DOM_RECORD_ID NOT NULL,
FILE_ID DOM_RECORD_ID NOT NULL,
COST_ID DOM_RECORD_ID DEFAULT 0 NOT NULL,
CREATION_DATE DOM_CURRENT_DATE,
AMOUNT DOM_MONEY NOT NULL,
INVOICED DOM_BOOLEAN NOT NULL,
INVOICEABLE DOM_BOOLEAN
DEFAULT 1 NOT NULL,
LINK_ID DOM_RECORD_ID);
CREATE TABLE FILES$PAYMENTS (
PAYEMENT_ID DOM_RECORD_ID NOT NULL,
FILE_ID DOM_RECORD_ID NOT NULL,
REFERENCE_ID DOM_RECORD_ID,
JOURNAL_ID DOM_RECORD_ID NOT NULL,
PAYMENT_DATE DOM_DATE NOT NULL,
AMOUNT DOM_MONEY NOT NULL,
INVOICE_ID DOM_RECORD_ID,
INVOICED DOM_BOOLEAN NOT NULL,
PAYMENT_FOR DOM_PAYEMENT_TYPE
DEFAULT 'A' NOT NULL collate ISO8859_1,
CREATION_DATE DOM_CURRENT_DATE,
CREATION_USER DOM_CURRENT_USER collate ISO8859_1,
INVOICEABLE DOM_BOOLEAN
DEFAULT 1 NOT NULL,
COMMISSION DOM_MONEY NOT NULL);
CREATE TABLE FILES$REFERENCES (
REFERENCE_ID DOM_RECORD_ID NOT NULL,
FILE_ID DOM_RECORD_ID NOT NULL,
REFERENCE DOM_REFERENCE collate ISO8859_1,
AMOUNT DOM_MONEY NOT NULL,
INTEREST_PERCENT DOM_PERCENT NOT NULL,
COST_PERCENT DOM_PERCENT NOT NULL,
COST_MINIMUM DOM_MONEY NOT NULL,
AUTO_CALCULATE DOM_BOOLEAN
DEFAULT 1 NOT NULL,
END_DATE DOM_DATE,
START_DATE DOM_DATE,
COSTS DOM_MONEY
DEFAULT 0 NOT NULL,
INTEREST DOM_MONEY
DEFAULT 0 NOT NULL,
CREATION_DATE DOM_CURRENT_DATE,
CREATION_USER DOM_CURRENT_USER collate ISO8859_1,
PAYED_AMOUNT DOM_MONEY NOT NULL,
PAYED_INTEREST DOM_MONEY NOT NULL,
PAYED_COSTS DOM_MONEY NOT NULL,
SALDO_AMOUNT COMPUTED BY (AMOUNT - PAYED_AMOUNT),
SALDO_INTEREST COMPUTED BY (INTEREST - PAYED_INTEREST),
SALDO_COSTS COMPUTED BY (COSTS - PAYED_COSTS));
CREATE TABLE FILES$REMARKS (
REMARK_ID DOM_RECORD_ID NOT NULL,
FILE_ID DOM_RECORD_ID NOT NULL,
REMARK DOM_TEXT,
CREATION_DATE DOM_CURRENT_DATE,
CREATION_USER DOM_CURRENT_USER collate ISO8859_1);
CREATE TABLE INVOICES$INVOICES (
INVOICE_ID DOM_RECORD_ID NOT NULL,
INVOICE_NR DOM_INVOICENR NOT NULL,
CLIENT_ID DOM_RECORD_ID NOT NULL,
INVOICE_DATE DOM_CURRENT_DATE NOT NULL);
CREATE TABLE INVOICES$INVOICE_DETAILS (
INVOICE_DETAIL_ID DOM_RECORD_ID NOT NULL,
INVOICE_ID DOM_RECORD_ID,
FILE_ID DOM_RECORD_ID,
AMOUNT DOM_MONEY NOT NULL);
CREATE TABLE SUPPORT$COUNTRIES (
COUNTRY_ID DOM_RECORD_ID NOT NULL,
CODE DOM_CODE NOT NULL collate ISO8859_1,
DESCRIPTION DOM_DESCRIPTION collate ISO8859_1,
TELEPHONE_CODE DOM_CODE NOT NULL collate ISO8859_1,
SYSTEM_DEFAULT DOM_BOOLEAN NOT NULL);
CREATE TABLE SUPPORT$LANGUAGES (
LANGUAGE_ID DOM_RECORD_ID NOT NULL,
CODE DOM_CODE NOT NULL collate ISO8859_1,
DESCRIPTION DOM_DESCRIPTION,
SYSTEM_DEFAULT DOM_BOOLEAN);
CREATE TABLE SUPPORT$TITLES (
TITLE_ID DOM_RECORD_ID NOT NULL,
LANGUAGE_ID DOM_RECORD_ID NOT NULL,
CODE DOM_CODE NOT NULL collate ISO8859_1,
DESCRIPTION DOM_DESCRIPTION collate ISO8859_1);
CREATE TABLE SUPPORT$ZIP_CODES (
ZIP_CODE_ID DOM_RECORD_ID NOT NULL,
COUNTRY_ID DOM_RECORD_ID NOT NULL,
CODE DOM_CODE NOT NULL collate ISO8859_1,
CITY DOM_CITY collate ISO8859_1,
POPULATION_COST DOM_MONEY NOT NULL);
CREATE TABLE SYSTEM$COLLECTORS (
COLLECTOR_ID DOM_RECORD_ID NOT NULL,
CODE DOM_CODE NOT NULL collate ISO8859_1,
NAME DOM_NAME collate ISO8859_1,
ADDRESS DOM_ADDRESS collate ISO8859_1,
ZIP_CODE_ID DOM_RECORD_ID NOT NULL,
BTW_NR DOM_BTW_NR collate ISO8859_1,
LANGUAGE_ID DOM_RECORD_ID NOT NULL,
SYSTEM_DEFAULT DOM_BOOLEAN NOT NULL);
CREATE TABLE SYSTEM$EMPLOYEES (
EMPLOYEE_ID DOM_RECORD_ID NOT NULL,
CODE DOM_CODE NOT NULL collate ISO8859_1,
NAME DOM_NAME collate ISO8859_1,
ADDRESS DOM_ADDRESS collate ISO8859_1,
ZIP_CODE_ID DOM_RECORD_ID NOT NULL,
LANGUAGE_ID DOM_RECORD_ID NOT NULL);
/* Views definition */
/* View: CLIENTS$CLIENTS_ALL_INFO */
CREATE VIEW CLIENTS$CLIENTS_ALL_INFO (
CLIENT_ID,
CODE,
NAME,
ADDRESS,
ZIP_CODE_ID,
INVOICE_NAME,
INVOICE_ADDRESS,
INVOICE_ZIP_ID,
VAT_NO,
LANGUAGE_ID,
E_MAIL,
BANK_ACCOUNT_NR,
CURRENT_INTREST_PERCENT,
CURRENT_COST_PERCENT,
CURRENT_COST_MINIMUM,
FILE_COUNT,
OPEN_FILES,
TELEPHONE,
TELEFAX,
CONTACT,
ZIP_CODE,
CITY,
LANGUAGE_CODE,
COUNTRY_CODE,
INVOICE_ZIP_CODE,
INVOICE_CITY)
AS
SELECT CLIENTS$CLIENTS.CLIENT_ID,
CLIENTS$CLIENTS.CODE,
CLIENTS$CLIENTS.NAME,
CLIENTS$CLIENTS.ADDRESS,
CLIENTS$CLIENTS.ZIP_CODE_ID,
CLIENTS$CLIENTS.INVOICE_NAME,
CLIENTS$CLIENTS.INVOICE_ADDRESS,
CLIENTS$CLIENTS.INVOICE_ZIP_ID,
CLIENTS$CLIENTS.VAT_NO,
CLIENTS$CLIENTS.LANGUAGE_ID,
CLIENTS$CLIENTS.E_MAIL,
CLIENTS$CLIENTS.BANK_ACCOUNT_NR,
CLIENTS$CLIENTS.CURRENT_INTREST_PERCENT,
CLIENTS$CLIENTS.CURRENT_COST_PERCENT,
CLIENTS$CLIENTS.CURRENT_COST_MINIMUM,
CLIENTS$CLIENTS.FILE_COUNT,
CLIENTS$CLIENTS.OPEN_FILES,
CLIENTS$CLIENTS.TELEPHONE,
CLIENTS$CLIENTS.TELEFAX,
CLIENTS$CLIENTS.CONTACT,
CLIENTS_ZIP_CODES.CODE,
CLIENTS_ZIP_CODES.CITY,
SUPPORT$LANGUAGES.CODE,
SUPPORT$COUNTRIES.CODE,
INVOICE_ZIP_CODES.CODE,
INVOICE_ZIP_CODES.CITY
FROM CLIENTS$CLIENTS
JOIN SUPPORT$LANGUAGES ON (SUPPORT$LANGUAGES.LANGUAGE_ID = CLIENTS$CLIENTS.LANGUAGE_ID)
JOIN SUPPORT$ZIP_CODES CLIENTS_ZIP_CODES ON (CLIENTS$CLIENTS.ZIP_CODE_ID = CLIENTS_ZIP_CODES.ZIP_CODE_ID)
JOIN SUPPORT$COUNTRIES ON (SUPPORT$ZIP_CODES.COUNTRY_ID = SUPPORT$COUNTRIES.COUNTRY_ID)
JOIN SUPPORT$ZIP_CODES INVOICE_ZIP_CODES ON (INVOICE_ZIP_CODES.ZIP_CODE_ID = CLIENTS$CLIENTS.INVOICE_ZIP_ID)
;
/* View: FILES$DEPTORS_ALL_INFO */
CREATE VIEW FILES$DEPTORS_ALL_INFO (
DEPTOR_ID,
NAME,
ADDRESS,
ZIP_CODE_ID,
LANGUAGE_ID,
CREATION_DATE,
CREATION_USER,
E_MAIL,
TITLE_ID,
OLD_ID,
TELEPHONE,
TELEFAX,
COUNTRY_CODE,
COUNTRY_DESCRIPTION,
COUNTRY_TELEPHONE_CODE,
ZIP_CODE,
CITY,
TITLE_CODE,
TITLE_DESCRIPTION,
LANGUAGE_CODE,
LANGUAGE_DESCRIPTION,
FILE_COUNT,
OPEN_FILES)
AS
SELECT FILES$DEPTORS.DEPTOR_ID,
FILES$DEPTORS.NAME,
FILES$DEPTORS.ADDRESS,
FILES$DEPTORS.ZIP_CODE_ID,
FILES$DEPTORS.LANGUAGE_ID,
FILES$DEPTORS.CREATION_DATE,
FILES$DEPTORS.CREATION_USER,
FILES$DEPTORS.E_MAIL,
FILES$DEPTORS.TITLE_ID,
FILES$DEPTORS.OLD_ID,
FILES$DEPTORS.TELEPHONE,
FILES$DEPTORS.TELEFAX,
SUPPORT$COUNTRIES.CODE,
SUPPORT$COUNTRIES.DESCRIPTION,
SUPPORT$COUNTRIES.TELEPHONE_CODE,
SUPPORT$ZIP_CODES.CODE,
SUPPORT$ZIP_CODES.CITY,
SUPPORT$TITLES.CODE,
SUPPORT$TITLES.DESCRIPTION,
SUPPORT$LANGUAGES.CODE,
SUPPORT$LANGUAGES.DESCRIPTION,
FILES$DEPTORS.FILE_COUNT,
FILES$DEPTORS.OPEN_FILES
FROM FILES$DEPTORS
JOIN SUPPORT$LANGUAGES ON (SUPPORT$LANGUAGES.LANGUAGE_ID = FILES$DEPTORS.LANGUAGE_ID)
JOIN SUPPORT$TITLES ON (FILES$DEPTORS.TITLE_ID = SUPPORT$TITLES.TITLE_ID)
JOIN SUPPORT$ZIP_CODES ON (FILES$DEPTORS.ZIP_CODE_ID = SUPPORT$ZIP_CODES.ZIP_CODE_ID)
JOIN SUPPORT$COUNTRIES ON (SUPPORT$ZIP_CODES.COUNTRY_ID = SUPPORT$COUNTRIES.COUNTRY_ID)
;
/* View: FILES$FILE_ACTIONS_ALL_INFO */
CREATE VIEW FILES$FILE_ACTIONS_ALL_INFO (
FILE_ACTION_ID,
FILE_ID,
ACTION_ID,
REMARKS,
ACTION_DATE,
ACTION_USER,
ACTION_CODE,
ACTION_DESCRIPTION)
AS
SELECT FILES$FILE_ACTIONS.FILE_ACTION_ID,
FILES$FILE_ACTIONS.FILE_ID,
FILES$FILE_ACTIONS.ACTION_ID,
FILES$FILE_ACTIONS.REMARKS,
FILES$FILE_ACTIONS.ACTION_DATE,
FILES$FILE_ACTIONS.ACTION_USER,
FILES$ACTIONS.CODE,
FILES$ACTIONS.DESCRIPTION
FROM FILES$ACTIONS
INNER JOIN FILES$FILE_ACTIONS ON (FILES$ACTIONS.ACTION_ID = FILES$FILE_ACTIONS.ACTION_ID)
;
/* View: FILES$FILES_ALL_INFO */
CREATE VIEW FILES$FILES_ALL_INFO (
FILE_ID,
CLIENT_ID,
FILE_NR,
DEPTOR_ID,
REFERENCE,
COLLECTOR_ID,
STATE_ID,
CREATION_DATE,
CREATION_USER,
DATE_CLOSED,
AMOUNT,
COSTS,
INTEREST,
PAYED_AMOUNT,
PAYED_INTEREST,
PAYED_COSTS,
PAYED_UNKNOWN,
PAYED_TOTAL,
PAYABLE,
TOTAL,
SALDO_AMOUNT,
SALDO_INTEREST,
SALDO_COSTS,
CREATION_MONTH,
CREATION_YEAR,
DEPTOR_NAME,
DEPTOR_ADDRESS,
DEPTOR_ZIP_CODE,
DEPTOR_CITY,
DEPTOR_COUNTRY_CODE,
DEPTOR_LANGUAGE_CODE,
DEPTOR_TELEPHONE,
DEPTOR_TELEFAX,
DEPTOR_E_MAIL,
COLLECTOR_CODE,
COLLECTOR_NAME,
CLIENT_CODE,
CLIENT_NAME)
AS
SELECT FILES$FILES.FILE_ID,
FILES$FILES.CLIENT_ID,
FILES$FILES.FILE_NR,
FILES$FILES.DEPTOR_ID,
FILES$FILES.REFERENCE,
FILES$FILES.COLLECTOR_ID,
FILES$FILES.STATE_ID,
FILES$FILES.CREATION_DATE,
FILES$FILES.CREATION_USER,
FILES$FILES.DATE_CLOSED,
FILES$FILES.AMOUNT,
FILES$FILES.COSTS,
FILES$FILES.INTEREST,
FILES$FILES.PAYED_AMOUNT,
FILES$FILES.PAYED_INTEREST,
FILES$FILES.PAYED_COSTS,
FILES$FILES.PAYED_UNKNOWN,
FILES$FILES.PAYED_TOTAL,
FILES$FILES.PAYABLE,
FILES$FILES.TOTAL,
FILES$FILES.SALDO_AMOUNT,
FILES$FILES.SALDO_INTEREST,
FILES$FILES.COSTS,
FILES$FILES.CREATION_MONTH,
FILES$FILES.CREATION_YEAR,
FILES$DEPTORS.NAME,
FILES$DEPTORS.ADDRESS,
SUPPORT$ZIP_CODES.CODE,
SUPPORT$ZIP_CODES.CITY,
SUPPORT$COUNTRIES.CODE,
SUPPORT$LANGUAGES.CODE,
FILES$DEPTORS.TELEPHONE,
FILES$DEPTORS.TELEFAX,
FILES$DEPTORS.E_MAIL,
SYSTEM$COLLECTORS.CODE,
SYSTEM$COLLECTORS.NAME,
CLIENTS$CLIENTS.CODE,
CLIENTS$CLIENTS.NAME
FROM FILES$FILES
JOIN FILES$DEPTORS ON (FILES$FILES.DEPTOR_ID = FILES$DEPTORS.DEPTOR_ID)
JOIN SUPPORT$ZIP_CODES ON (FILES$DEPTORS.ZIP_CODE_ID = SUPPORT$ZIP_CODES.ZIP_CODE_ID)
JOIN SUPPORT$COUNTRIES ON (SUPPORT$ZIP_CODES.COUNTRY_ID = SUPPORT$COUNTRIES.COUNTRY_ID)
JOIN SUPPORT$LANGUAGES ON (FILES$DEPTORS.LANGUAGE_ID = SUPPORT$LANGUAGES.LANGUAGE_ID)
JOIN SYSTEM$COLLECTORS ON (SYSTEM$COLLECTORS.COLLECTOR_ID = FILES$FILES.COLLECTOR_ID)
JOIN CLIENTS$CLIENTS ON (FILES$FILES.CLIENT_ID = CLIENTS$CLIENTS.CLIENT_ID)
;
/* View: FILES$PAYMENTS_ALL_INFO */
CREATE VIEW FILES$PAYMENTS_ALL_INFO (
PAYEMENT_ID,
FILE_ID,
REFERENCE_ID,
JOURNAL_ID,
PAYMENT_DATE,
AMOUNT,
CREATION_DATE,
CREATION_USER,
INVOICE_ID,
INVOICED,
PAYMENT_FOR,
REFERENCE,
ACCOUNT_CODE,
ACCOUNT_DESCRIPTION,
TRANSACTION_DESCRIPTION,
CODA_FILE_NAME)
AS
SELECT FILES$PAYMENTS.PAYEMENT_ID,
FILES$PAYMENTS.FILE_ID,
FILES$PAYMENTS.REFERENCE_ID,
FILES$PAYMENTS.JOURNAL_ID,
FILES$PAYMENTS.PAYMENT_DATE,
FILES$PAYMENTS.AMOUNT,
FILES$PAYMENTS.CREATION_DATE,
FILES$PAYMENTS.CREATION_USER,
FILES$PAYMENTS.INVOICE_ID,
FILES$PAYMENTS.INVOICED,
FILES$PAYMENTS.PAYMENT_FOR,
FILES$REFERENCES.REFERENCE,
ACCOUNTS$ACCOUNTS.CODE,
ACCOUNTS$ACCOUNTS.DESCRIPTION,
ACCOUNTS$TRANSACTIONS.DESCRIPTION,
ACCOUNTS$CODA.FILE_NAME
FROM FILES$PAYMENTS
LEFT JOIN ACCOUNTS$JOURNAL ON (ACCOUNTS$JOURNAL.JOURNAL_ID = FILES$PAYMENTS.JOURNAL_ID)
LEFT JOIN ACCOUNTS$ACCOUNTS ON (ACCOUNTS$JOURNAL.ACCOUNT_ID = ACCOUNTS$ACCOUNTS.ACCOUNT_ID)
LEFT JOIN ACCOUNTS$TRANSACTIONS ON (ACCOUNTS$TRANSACTIONS.TRANSACTION_ID = ACCOUNTS$JOURNAL.TRANSACTION_ID)
LEFT JOIN FILES$REFERENCES ON (FILES$PAYMENTS.REFERENCE_ID = FILES$REFERENCES.REFERENCE_ID)
LEFT JOIN ACCOUNTS$CODA ON (ACCOUNTS$CODA.CODA_ID = ACCOUNTS$TRANSACTIONS.CODA_ID)
;
/* View: INVOICES$INVOICEABLE_AMOUNTS */
CREATE VIEW INVOICES$INVOICEABLE_AMOUNTS (
PAYEMENT_ID,
CLIENT_ID,
CODE,
NAME,
ADDRESS,
ZIP_CODE,
CITY,
FILE_ID,
FILE_NR,
FILE_REFERENCE,
DEPTOR_NAME,
DEPTOR_ADDRESS,
DEPTOR_ZIP_CODE,
DEPTOR_CITY,
PAYMENT_DATE,
AMOUNT,
COMMISSION,
PAYMENT_FOR,
REFERENCE)
AS
SELECT FILES$PAYMENTS.PAYEMENT_ID,
CLIENTS$CLIENTS_ALL_INFO.CLIENT_ID,
CLIENTS$CLIENTS_ALL_INFO.CODE,
CLIENTS$CLIENTS_ALL_INFO.NAME,
CLIENTS$CLIENTS_ALL_INFO.ADDRESS,
CLIENTS$CLIENTS_ALL_INFO.ZIP_CODE,
CLIENTS$CLIENTS_ALL_INFO.CITY,
FILES$FILES_ALL_INFO.FILE_ID,
FILES$FILES_ALL_INFO.FILE_NR,
FILES$FILES_ALL_INFO.REFERENCE,
FILES$FILES_ALL_INFO.DEPTOR_NAME,
FILES$FILES_ALL_INFO.DEPTOR_ADDRESS,
FILES$FILES_ALL_INFO.DEPTOR_ZIP_CODE,
FILES$FILES_ALL_INFO.DEPTOR_CITY,
FILES$PAYMENTS.PAYMENT_DATE,
FILES$PAYMENTS.AMOUNT,
FILES$PAYMENTS.COMMISSION,
FILES$PAYMENTS.PAYMENT_FOR,
FILES$REFERENCES.REFERENCE
FROM FILES$PAYMENTS
INNER JOIN FILES$FILES_ALL_INFO ON (FILES$PAYMENTS.FILE_ID = FILES$FILES_ALL_INFO.FILE_ID)
INNER JOIN CLIENTS$CLIENTS_ALL_INFO ON (FILES$FILES_ALL_INFO.CLIENT_ID = CLIENTS$CLIENTS_ALL_INFO.CLIENT_ID)
LEFT JOIN FILES$REFERENCES ON (FILES$PAYMENTS.REFERENCE_ID = FILES$REFERENCES.REFERENCE_ID)
WHERE
(
(FILES$PAYMENTS.INVOICED = 0)
and
(FILES$PAYMENTS.INVOICEABLE = 1)
)
;
/* View: SUPPORT$ZIP_CODES_ALL_INFO */
CREATE VIEW SUPPORT$ZIP_CODES_ALL_INFO (
ZIP_CODE_ID,
COUNTRY_ID,
CODE,
CITY,
COUNTRY_CODE,
COUNTRY_DESCRIPTION)
AS
SELECT SUPPORT$ZIP_CODES.ZIP_CODE_ID,
SUPPORT$ZIP_CODES.COUNTRY_ID,
SUPPORT$ZIP_CODES.CODE,
SUPPORT$ZIP_CODES.CITY,
SUPPORT$COUNTRIES.CODE,
SUPPORT$COUNTRIES.DESCRIPTION
FROM SUPPORT$ZIP_CODES
INNER JOIN SUPPORT$COUNTRIES ON (SUPPORT$ZIP_CODES.COUNTRY_ID = SUPPORT$COUNTRIES.COUNTRY_ID)
;
/* Unique keys definition */
ALTER TABLE ACCOUNTS$ACCOUNTS ADD CONSTRAINT PK_ACCOUNTS$ACCOUNTS PRIMARY KEY (ACCOUNT_ID);
ALTER TABLE ACCOUNTS$CODA ADD CONSTRAINT PK_ACCOUNTS$CODA PRIMARY KEY (CODA_ID);
ALTER TABLE ACCOUNTS$JOURNAL ADD CONSTRAINT PK_ACCOUNTS$JOURNAL PRIMARY KEY (JOURNAL_ID);
ALTER TABLE ACCOUNTS$TRANSACTIONS ADD CONSTRAINT PK_ACCOUNTS$TRANSACTIONS PRIMARY KEY (TRANSACTION_ID);
ALTER TABLE CLIENTS$CLIENTS ADD CONSTRAINT PK_CLIENTS$CLIENTS PRIMARY KEY (CLIENT_ID);
ALTER TABLE CLIENTS$CONDITIONS ADD CONSTRAINT PK_CLIENTS$CONDITIONS PRIMARY KEY (RECORD_ID);
ALTER TABLE CODA$BOOKING ADD CONSTRAINT PK_CODA$BOOKING PRIMARY KEY (RECORD_ID);
ALTER TABLE CODA$FILES ADD CONSTRAINT PK_CODA$FILES PRIMARY KEY (FILE_ID);
ALTER TABLE CODA$FILE_SECTIONS ADD CONSTRAINT PK_CODA$FILE_SECTIONS PRIMARY KEY (SECTION_ID);
ALTER TABLE CODA$TRANSACTIONS ADD CONSTRAINT PK_CODA$TRANSACTIONS PRIMARY KEY (TRANSACTION_ID);
ALTER TABLE CODA$TRANSACTIONS_OPERATIONS ADD CONSTRAINT PK_CODA$TRANSACTIONS_OPERATIONS PRIMARY KEY (OPERATION_ID);
ALTER TABLE CODA$TRANSACTION_GROUP ADD CONSTRAINT PK_CODA$TRANSACTION_GROUP PRIMARY KEY (GROUP_ID);
ALTER TABLE CODA$TRANSACTION_TYPES ADD CONSTRAINT PK_CODA$TRANSACTION_TYPES PRIMARY KEY (TYPE_ID);
ALTER TABLE FILES$ACTIONS ADD CONSTRAINT PK_FILES$ACTIONS PRIMARY KEY (ACTION_ID);
ALTER TABLE FILES$COSTS ADD CONSTRAINT PK_FILES$COSTS PRIMARY KEY (COST_ID);
ALTER TABLE FILES$DEPTORS ADD CONSTRAINT PK_FILES$DEPTORS PRIMARY KEY (DEPTOR_ID);
ALTER TABLE FILES$FILES ADD CONSTRAINT PK_FILES$FILES PRIMARY KEY (FILE_ID);
ALTER TABLE FILES$FILE_ACTIONS ADD CONSTRAINT PK_FILES$FILE_ACTIONS PRIMARY KEY (FILE_ACTION_ID);
ALTER TABLE FILES$FILE_COSTS ADD CONSTRAINT PK_FILES$FILE_COSTS PRIMARY KEY (RECORD_ID);
ALTER TABLE FILES$PAYMENTS ADD CONSTRAINT PK_FILES$PAYMENTS PRIMARY KEY (PAYEMENT_ID);
ALTER TABLE FILES$REFERENCES ADD CONSTRAINT PK_FILES$REFERENCES PRIMARY KEY (REFERENCE_ID);
ALTER TABLE FILES$REMARKS ADD CONSTRAINT PK_FILES$REMARKS PRIMARY KEY (REMARK_ID);
ALTER TABLE INVOICES$INVOICES ADD CONSTRAINT PK_INVOICES$INVOICES PRIMARY KEY (INVOICE_ID);
ALTER TABLE INVOICES$INVOICE_DETAILS ADD CONSTRAINT PK_INVOICES$INVOICE_DETAILS PRIMARY KEY (INVOICE_DETAIL_ID);
ALTER TABLE SUPPORT$COUNTRIES ADD CONSTRAINT PK_SUPPORT$COUNTRIES PRIMARY KEY (COUNTRY_ID);
ALTER TABLE SUPPORT$LANGUAGES ADD CONSTRAINT PK_SUPPORT$LANGUAGES PRIMARY KEY (LANGUAGE_ID);
ALTER TABLE SUPPORT$TITLES ADD CONSTRAINT PK_SUPPORT$TITLES PRIMARY KEY (TITLE_ID);
ALTER TABLE SUPPORT$ZIP_CODES ADD CONSTRAINT PK_SUPPORT$ZIP_CODES PRIMARY KEY (ZIP_CODE_ID);
ALTER TABLE SYSTEM$COLLECTORS ADD CONSTRAINT PK_SYSTEM$COLLECTORS PRIMARY KEY (COLLECTOR_ID);
ALTER TABLE SYSTEM$EMPLOYEES ADD CONSTRAINT PK_SYSTEM$EMPLOYEES PRIMARY KEY (EMPLOYEE_ID);
/* Foreign keys definition */
ALTER TABLE CLIENTS$CONDITIONS ADD CONSTRAINT FK_CLIENTS$CONDITIONS_CLIENT_ID FOREIGN KEY (CLIENT_ID) REFERENCES CLIENTS$CLIENTS (CLIENT_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE CODA$BOOKING ADD CONSTRAINT FK_CODA$BOOKING_FILE_ID FOREIGN KEY (FILE_ID) REFERENCES FILES$FILES (FILE_ID) ON UPDATE CASCADE;
ALTER TABLE CODA$BOOKING ADD CONSTRAINT FK_CODA$BOOKING_TRANSACTION_ID FOREIGN KEY (TRANSACTION_ID) REFERENCES CODA$TRANSACTIONS (TRANSACTION_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE CODA$FILE_SECTIONS ADD CONSTRAINT FK_CODA$FILE_SECTIONS_FILE_ID FOREIGN KEY (FILE_ID) REFERENCES CODA$FILES (FILE_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE CODA$TRANSACTIONS ADD CONSTRAINT FK_CODA$TRANSACTIONS_SECTION_ID FOREIGN KEY (SECTION_ID) REFERENCES CODA$FILE_SECTIONS (SECTION_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE FILES$FILES ADD CONSTRAINT FK_FILES$FILES_CLIENT_ID FOREIGN KEY (CLIENT_ID) REFERENCES CLIENTS$CLIENTS (CLIENT_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE FILES$FILES ADD CONSTRAINT FK_FILES$FILES_COLLECTOR_ID FOREIGN KEY (COLLECTOR_ID) REFERENCES SYSTEM$COLLECTORS (COLLECTOR_ID) ON UPDATE CASCADE;
ALTER TABLE FILES$FILES ADD CONSTRAINT FK_FILES$FILES_DEPTOR_ID FOREIGN KEY (DEPTOR_ID) REFERENCES FILES$DEPTORS (DEPTOR_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE FILES$FILE_ACTIONS ADD CONSTRAINT FK_FILES$FILE_ACTIONS_ACTION_ID FOREIGN KEY (ACTION_ID) REFERENCES FILES$ACTIONS (ACTION_ID) ON UPDATE CASCADE;
ALTER TABLE FILES$FILE_ACTIONS ADD CONSTRAINT FK_FILES$FILE_ACTIONS_FILE_ID FOREIGN KEY (FILE_ID) REFERENCES FILES$FILES (FILE_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE FILES$FILE_COSTS ADD CONSTRAINT FK_FILES$FILE_COSTS_COST_ID FOREIGN KEY (COST_ID) REFERENCES FILES$COSTS (COST_ID) ON UPDATE CASCADE;
ALTER TABLE FILES$FILE_COSTS ADD CONSTRAINT FK_FILES$FILE_COSTS_FILE_ID FOREIGN KEY (FILE_ID) REFERENCES FILES$FILES (FILE_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE FILES$PAYMENTS ADD CONSTRAINT FK_FILES$PAYMENTS_FILE_ID FOREIGN KEY (FILE_ID) REFERENCES FILES$FILES (FILE_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE FILES$PAYMENTS ADD CONSTRAINT FK_FILES$PAYMENTS_REFERENCE_ID FOREIGN KEY (REFERENCE_ID) REFERENCES FILES$REFERENCES (REFERENCE_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE FILES$REFERENCES ADD CONSTRAINT FK_FILES$REFERENCES_FILE_ID FOREIGN KEY (FILE_ID) REFERENCES FILES$FILES (FILE_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE FILES$REMARKS ADD CONSTRAINT FK_FILES$REMARKS_FILE_ID FOREIGN KEY (FILE_ID) REFERENCES FILES$FILES (FILE_ID) ON DELETE CASCADE ON UPDATE CASCADE;
/* Indices definition */
CREATE INDEX IDX_FILES$DEPTORS ON FILES$DEPTORS (OLD_ID);
ALTER INDEX IDX_FILES$DEPTORS INACTIVE;
CREATE INDEX IDX_FILES$FILES_FILE_NR ON FILES$FILES (FILE_NR);
ALTER INDEX IDX_FILES$FILES_FILE_NR INACTIVE;
CREATE INDEX IDX_FILES$FILES_REFUND_STATEMEN ON FILES$FILES (REFUND_STATEMENT);
ALTER INDEX IDX_FILES$FILES_REFUND_STATEMEN INACTIVE;
CREATE UNIQUE INDEX IDX_FILES$FILES_STATEMENT ON FILES$FILES (STRUCTURED_STATEMENT);
ALTER INDEX IDX_FILES$FILES_STATEMENT INACTIVE;
CREATE INDEX IDX_SUPPORT$ZIP_CODES ON SUPPORT$ZIP_CODES (CODE, CITY);
ALTER INDEX IDX_SUPPORT$ZIP_CODES INACTIVE;
CREATE UNIQUE INDEX UN_IDX_CLIENTS$CLIENTS_CODE ON CLIENTS$CLIENTS (CODE);
ALTER INDEX UN_IDX_CLIENTS$CLIENTS_CODE INACTIVE;
SET TERM ^ ;
/* Stored procedures definition */
/* Stored Procedure: FILES$CREATE_PAYEMENT_AMOUNT */
CREATE PROCEDURE FILES$CREATE_PAYEMENT_AMOUNT (
PARJOURNAL_ID INTEGER,
PARFILE_ID INTEGER,
PARAMOUNT NUMERIC (18, 3),
PARDATE DATE)
RETURNS (
SALDOAMOUNT NUMERIC (18, 3))
AS
DECLARE VARIABLE varReferenceID INTEGER;
DECLARE VARIABLE varSaldoAmount NUMERIC(18,3);
BEGIN
SaldoAmount = parAmount;
FOR SELECT FILES$REFERENCES.REFERENCE_ID,
FILES$REFERENCES.SALDO_AMOUNT
FROM FILES$REFERENCES
WHERE (FILES$REFERENCES.FILE_ID = :parFile_ID) AND
(FILES$REFERENCES.SALDO_AMOUNT > 0)
INTO :varReferenceID, :varSaldoAmount DO
BEGIN
IF (varSaldoAmount > PARAMOUNT) THEN
BEGIN
INSERT INTO FILES$PAYMENTS (JOURNAL_ID, REFERENCE_ID, AMOUNT, PAYMENT_FOR, PAYMENT_DATE)
VALUES (:PARJOURNAL_ID, :varReferenceID, :PARAMOUNT, 'A', :parDate);
SaldoAmount = 0;
END
ELSE
BEGIN
SaldoAmount = PARAMOUNT - varSaldoAmount;
parAmount = parAmount - varSaldoAmount;
INSERT INTO FILES$PAYMENTS (JOURNAL_ID, REFERENCE_ID, AMOUNT, PAYMENT_FOR, PAYMENT_DATE)
VALUES (:PARJOURNAL_ID, :varReferenceID, :varSaldoAmount, 'A', :parDate);
END
IF (SaldoAmount = 0) THEN EXIT;
END
END
^
/* Stored Procedure: FILES$CREATE_PAYEMENT_COSTS */
CREATE PROCEDURE FILES$CREATE_PAYEMENT_COSTS (
PARJOURNAL_ID INTEGER,
PARFILE_ID INTEGER,
PARAMOUNT NUMERIC (18, 3),
PARDATE DATE)
RETURNS (
SALDOAMOUNT NUMERIC (18, 3))
AS
DECLARE VARIABLE varReferenceID INTEGER;
DECLARE VARIABLE varSaldoAmount NUMERIC(18,3);
BEGIN
SaldoAmount = parAmount;
FOR SELECT FILES$REFERENCES.REFERENCE_ID,
FILES$REFERENCES.SALDO_COSTS
FROM FILES$REFERENCES
WHERE (FILES$REFERENCES.FILE_ID = :parFile_ID) AND
(FILES$REFERENCES.SALDO_COSTS > 0)
INTO :varReferenceID, :varSaldoAmount DO
BEGIN
IF (varSaldoAmount > PARAMOUNT) THEN
BEGIN
INSERT INTO FILES$PAYMENTS (JOURNAL_ID, REFERENCE_ID, AMOUNT, PAYMENT_FOR, PAYMENT_DATE)
VALUES (:PARJOURNAL_ID, :varReferenceID, :PARAMOUNT, 'C', :parDate);
SaldoAmount = 0;
END
ELSE
BEGIN
SaldoAmount = PARAMOUNT - varSaldoAmount;
parAmount = parAmount - varSaldoAmount;
INSERT INTO FILES$PAYMENTS (JOURNAL_ID, REFERENCE_ID, AMOUNT, PAYMENT_FOR, PAYMENT_DATE)
VALUES (:PARJOURNAL_ID, :varReferenceID, :varSaldoAmount, 'C', :parDate);
END
IF (SaldoAmount = 0) THEN EXIT;
END
END
^
/* Stored Procedure: FILES$CREATE_PAYEMENT_INTEREST */
CREATE PROCEDURE FILES$CREATE_PAYEMENT_INTEREST (
PARJOURNAL_ID INTEGER,
PARFILE_ID INTEGER,
PARAMOUNT NUMERIC (18, 3),
PARDATE DATE)
RETURNS (
SALDOAMOUNT NUMERIC (18, 3))
AS
DECLARE VARIABLE varReferenceID INTEGER;
DECLARE VARIABLE varSaldoAmount NUMERIC(18,3);
BEGIN
SaldoAmount = parAmount;
FOR SELECT FILES$REFERENCES.REFERENCE_ID,
FILES$REFERENCES.SALDO_INTEREST
FROM FILES$REFERENCES
WHERE (FILES$REFERENCES.FILE_ID = :parFile_ID) AND
(FILES$REFERENCES.SALDO_INTEREST > 0)
INTO :varReferenceID, :varSaldoAmount DO
BEGIN
IF (varSaldoAmount > PARAMOUNT) THEN
BEGIN
INSERT INTO FILES$PAYMENTS (JOURNAL_ID, REFERENCE_ID, AMOUNT, PAYMENT_FOR, PAYMENT_DATE)
VALUES (:PARJOURNAL_ID, :varReferenceID, :PARAMOUNT, 'I', :parDate);
SaldoAmount = 0;
END
ELSE
BEGIN
SaldoAmount = PARAMOUNT - varSaldoAmount;
parAmount = parAmount - varSaldoAmount;
INSERT INTO FILES$PAYMENTS (JOURNAL_ID, REFERENCE_ID, AMOUNT, PAYMENT_FOR, PAYMENT_DATE)
VALUES (:PARJOURNAL_ID, :varReferenceID, :varSaldoAmount, 'I', :parDate);
END
IF (SaldoAmount = 0) THEN EXIT;
END
END
^
/* Stored Procedure: FILES$CREATE_PAYEMENT_UNKNOWN */
CREATE PROCEDURE FILES$CREATE_PAYEMENT_UNKNOWN (
PARJOURNAL_ID INTEGER,
PARFILE_ID INTEGER,
PARAMOUNT NUMERIC (18, 3),
PARDATE DATE)
RETURNS (
SALDOAMOUNT NUMERIC (18, 3))
AS
DECLARE VARIABLE varReferenceID INTEGER;
DECLARE VARIABLE varSaldoAmount NUMERIC(18,3);
BEGIN
INSERT INTO FILES$PAYMENTS (JOURNAL_ID, FILE_ID, AMOUNT, PAYMENT_FOR, PAYMENT_DATE)
VALUES (:PARJOURNAL_ID, :parFile_ID, :parAmount, '?', :parDate);
END
^
/* Stored Procedure: ACCOUNTS$SET_PAYEMENT_TYPE */
CREATE PROCEDURE ACCOUNTS$SET_PAYEMENT_TYPE (
PARJOURNALID INTEGER,
PARFILEID INTEGER,
PARAMOUNT NUMERIC (18, 3),
PARDATE DATE)
AS
DECLARE VARIABLE varSaldoAmount NUMERIC(18,3);
BEGIN
varSaldoAmount = parAmount;
IF (varSaldoAmount > 0) THEN
BEGIN
EXECUTE PROCEDURE FILES$CREATE_PAYEMENT_AMOUNT (:parJournalID, :parFileID,:varSaldoAmount, :parDate) RETURNING_VALUES :varSaldoAmount;
END
IF (varSaldoAmount > 0) THEN
BEGIN
EXECUTE PROCEDURE FILES$CREATE_PAYEMENT_INTEREST (:parJournalID, :parFileID,:varSaldoAmount, :parDate) RETURNING_VALUES :varSaldoAmount;
END
IF (varSaldoAmount > 0) THEN
BEGIN
EXECUTE PROCEDURE FILES$CREATE_PAYEMENT_COSTS (:parJournalID, :parFileID,:varSaldoAmount, :parDate) RETURNING_VALUES :varSaldoAmount;
END
IF (varSaldoAmount <> 0) THEN
BEGIN
EXECUTE PROCEDURE FILES$CREATE_PAYEMENT_UNKNOWN (:parJournalID, :parFileID,:varSaldoAmount, :parDate) RETURNING_VALUES :varSaldoAmount;
END
END
^
/* Stored Procedure: CONVERT$CREATE_INFO */
CREATE PROCEDURE CONVERT$CREATE_INFO
AS
BEGIN
/* Procedure body */
INSERT INTO SUPPORT$LANGUAGES (CODE, DESCRIPTION, SYSTEM_DEFAULT) VALUES ('DUTCH', 'Dutch', 1);
INSERT INTO SUPPORT$LANGUAGES (CODE, DESCRIPTION) VALUES ('FRENCH', 'French');
INSERT INTO SUPPORT$LANGUAGES (CODE, DESCRIPTION) VALUES ('ENGLISH', 'English');
INSERT INTO SUPPORT$COUNTRIES (CODE, DESCRIPTION, TELEPHONE_CODE, SYSTEM_DEFAULT) VALUES ('BE', 'Belgium', '032', 1);
INSERT INTO SUPPORT$ZIP_CODES (CODE, CITY) VALUES ('UNKNOWN', 'Not Specified');
INSERT INTO SYSTEM$COLLECTORS (CODE, NAME, SYSTEM_DEFAULT) VALUES ('V&B','Vrydagh & Boriau', 1);
INSERT INTO ACCOUNTS$TRANSACTIONS (TRANSACTION_ID, DESCRIPTION) VALUES (1, 'CONVERTION');
INSERT INTO FILES$COSTS (COST_ID, CODE, DESCRIPTION) VALUES (0, 'UNKNOWN', 'Unkown by conversion');
INSERT INTO FILES$COSTS (CODE, DESCRIPTION) VALUES ('POP', 'Population');
INSERT INTO FILES$COSTS (CODE, DESCRIPTION) VALUES ('PAYEMENT', 'Payement Cost');
END
^
/* Stored Procedure: FILES$GET_PAYEMENT_COMMISSION */
CREATE PROCEDURE FILES$GET_PAYEMENT_COMMISSION (
PARFILEID INTEGER,
PARAMOUNT NUMERIC (18, 2))
RETURNS (
RESULT NUMERIC (18, 2))
AS
DECLARE VARIABLE varMin NUMERIC(18,2);
DECLARE VARIABLE varPercent NUMERIC(5,2);
DECLARE VARIABLE varClientID INTEGER;
BEGIN
RESULT = 0;
SELECT FILES$FILES.CLIENT_ID
FROM FILES$FILES
WHERE FILES$FILES.FILE_ID = :parFileID
INTO :varClientID;
SELECT CLIENTS$CONDITIONS.INVOICE_PERCENT, CLIENTS$CONDITIONS.INVOICE_MINIMUM
FROM CLIENTS$CONDITIONS
WHERE (CLIENTS$CONDITIONS.CLIENT_ID = :varClientID) AND (CLIENTS$CONDITIONS.END_VALUE = (SELECT MAX(CC2.END_VALUE)
FROM CLIENTS$CONDITIONS CC2
WHERE CC2.CLIENT_ID = :varClientID))
INTO :varPercent, :varMin;
RESULT = (parAmount * :varPercent) / 100;
IF (RESULT < :varMin) THEN RESULT = :varMin;
IF (RESULT IS NULL) THEN RESULT = 0;
END
^
/* Stored Procedure: SUPPORT$AT */
CREATE PROCEDURE SUPPORT$AT (
PARSOURCE VARCHAR (255),
PARSEARCH VARCHAR (255))
RETURNS (
RESULT INTEGER)
AS
DECLARE VARIABLE varCheck VARCHAR(255);
DECLARE VARIABLE varTemp VARCHAR(255);
BEGIN
/* Procedure body */
RESULT = 0;
IF (parSource IS NULL) THEN EXIT;
IF (parSearch IS NULL) THEN EXIT;
RESULT = 1;
varCheck = UDF_SUBSTR(parSource, RESULT, 1);
WHILE (NOT varCheck = parSearch) DO
BEGIN
RESULT = RESULT +1;
varCheck = UDF_SUBSTR(parSource, RESULT, RESULT);
END
END
^
/* Stored Procedure: SUPPORT$ENCODEDATE */
CREATE PROCEDURE SUPPORT$ENCODEDATE (
AYEAR INTEGER,
AMONTH INTEGER,
ADAY INTEGER)
RETURNS (
RESULT DATE)
AS
BEGIN
RESULT = cast( ADay || '.' || AMonth || '.' || AYear as DATE);
SUSPEND;
END
^
/* Stored Procedure: SUPPORT$DAYSOFMONTH */
CREATE PROCEDURE SUPPORT$DAYSOFMONTH (
AYEAR INTEGER,
AMONTH INTEGER)
RETURNS (
RESULT INTEGER)
AS
DECLARE VARIABLE WorkDate DATE;
BEGIN
Result = 31;
WHILE (Result > 28 AND WorkDate IS NULL) DO
BEGIN
EXECUTE PROCEDURE SUPPORT$ENCODEDATE(AYear, AMonth, Result)
RETURNING_VALUES(WorkDate);
WHEN ANY DO Result = Result -1;
END
END
^
/* Stored Procedure: SUPPORT$DECODEDATE */
CREATE PROCEDURE SUPPORT$DECODEDATE (
ADATE TIMESTAMP)
RETURNS (
AYEAR INTEGER,
AMONTH INTEGER,
ADAY INTEGER)
AS
BEGIN
AYear = Extract (Year from ADate);
AMonth = Extract (Month from ADate);
ADay = Extract (Day from ADate);
SUSPEND;
END
^
/* Stored Procedure: SUPPORT$MOD */
CREATE PROCEDURE SUPPORT$MOD (
DIVIDEND NUMERIC (18, 0),
DIVISOR INTEGER)
RETURNS (
RESULT INTEGER)
AS
BEGIN
/* This procedure calculates the modulus of two numbers */
IF(Dividend = 0) THEN Result = 0;
ELSE
RESULT = Dividend-(CAST((Dividend / Divisor)-0.49 AS INTEGER)*Divisor);
suspend;
END
^
/* Stored Procedure: SUPPORT$INCDATE */
CREATE PROCEDURE SUPPORT$INCDATE (
IN_DATE TIMESTAMP,
IN_SECONDS INTEGER,
IN_MINUTES INTEGER,
IN_HOURS INTEGER,
IN_DAYS INTEGER,
IN_MONTHS INTEGER,
IN_YEARS INTEGER)
RETURNS (
RESULT TIMESTAMP)
AS
DECLARE VARIABLE var_SecondsOfTime INTEGER;
DECLARE VARIABLE var_Time TIME;
DECLARE VARIABLE var_Day INTEGER;
DECLARE VARIABLE var_Month INTEGER;
DECLARE VARIABLE var_Year INTEGER;
DECLARE VARIABLE var_ModHour INTEGER;
DECLARE VARIABLE var_ModMonth INTEGER;
DECLARE VARIABLE var_DaysPerMonth INTEGER;
BEGIN
IF (IN_SECONDS IS NULL) THEN IN_SECONDS = 0;
IF (IN_MINUTES IS NULL) THEN IN_MINUTES = 0;
IF (IN_HOURS IS NULL) THEN IN_HOURS = 0;
IF (IN_DAYS IS NULL) THEN IN_DAYS = 0;
IF (IN_MONTHS IS NULL) THEN IN_MONTHS = 0;
IF (IN_YEARS IS NULL) THEN IN_YEARS = 0;
/* function IncTime(ADateTime: TDateTime; Hours, Minutes, Seconds, MSecs: Integer): TDateTime; */
/* Result := ADateTime + (Hours div 24) + (((Hours mod 24) * 3600000 + Minutes * 60000 + Seconds * 1000 + MSecs) / MSecsPerDay); */
var_Time = CAST(in_Date AS TIME);
var_SecondsOfTime = EXTRACT(HOUR FROM var_Time)*60*60 +
EXTRACT(MINUTE FROM var_Time)*60 +
EXTRACT(SECOND FROM var_Time) +
in_Hours*60*60 + in_Minutes*60 + in_Seconds;
in_Days = in_Days + CAST( var_SecondsOfTime/(24*60*60) - 0.49 AS INTEGER );
EXECUTE PROCEDURE SUPPORT$MOD( var_SecondsOfTime, 86400 )
RETURNING_VALUES ( var_SecondsOfTime );
var_Time = CAST( '00:00' AS TIME );
var_Time = var_Time + var_SecondsOfTime;
EXECUTE PROCEDURE SUPPORT$DECODEDATE( in_Date ) RETURNING_VALUES ( var_Year ,var_Month, var_Day );
var_Year = var_Year + in_Years;
var_Year = var_Year + CAST(in_Months/12-0.49 AS INTEGER);
EXECUTE PROCEDURE SUPPORT$MOD( in_Months, 12 ) RETURNING_VALUES ( var_ModMonth );
var_Month = var_Month + var_ModMonth;
IF (var_Month < 1) THEN
BEGIN
var_Month = var_Month + 12;
var_Year = var_Year - 1;
END
ELSE
IF (var_Month > 12) THEN
BEGIN
var_Month = var_Month - 12;
var_Year = var_Year + 1;
END
EXECUTE PROCEDURE SUPPORT$DAYSOFMONTH ( var_Year, var_Month )
RETURNING_VALUES ( var_DaysPerMonth );
IF (var_Day > var_DaysPerMonth) THEN
BEGIN
EXECUTE PROCEDURE SUPPORT$DAYSOFMONTH ( var_Year, var_Month )
RETURNING_VALUES ( var_Day );
END
EXECUTE PROCEDURE SUPPORT$ENCODEDATE ( var_Year, var_Month, var_Day )
RETURNING_VALUES ( RESULT );
RESULT = RESULT + in_Days;
RESULT = CAST(RESULT AS DATE) + var_Time;
END
^
/* Stored Procedure: SUPPORT$NUMPART */
CREATE PROCEDURE SUPPORT$NUMPART (
PARSTRING VARCHAR (255))
RETURNS (
RESULT VARCHAR (255))
AS
DECLARE VARIABLE varPos INTEGER;
DECLARE VARIABLE varStartPos INTEGER;
DECLARE VARIABLE varNextChar CHAR(1);
DECLARE VARIABLE varQuit INTEGER;
BEGIN
/* Procedure body */
RESULT = null;
varStartPos = 0;
varPos = 1;
varQuit = 0;
IF (parString IS NULL) THEN EXIT;
WHILE ((varQuit = 0) AND (varPos < 255)) DO
BEGIN
varNextChar = UDF_SUBSTR(parString,:varPos , :varPos);
IF ((varNextChar IN ('0','1','2','3','4','5','6','7','8','9')) AND (varStartPos = 0)) THEN
BEGIN
varStartPos = varPos;
END
IF (NOT (varNextChar IN ('0','1','2','3','4','5','6','7','8','9')) AND (varStartPos <> 0)) THEN
BEGIN
varQuit = 1;
RESULT = UDF_SUBSTR(parString, varStartPos, varPos - 1);
END
varPos = varPos + 1;
END
END
^
/* Stored Procedure: SUPPORT$STR_LEN */
CREATE PROCEDURE SUPPORT$STR_LEN (
PARSTRING VARCHAR (255))
RETURNS (
RESULT INTEGER)
AS
DECLARE VARIABLE varTemp VARCHAR(255);
BEGIN
RESULT = null;
IF (parString IS NULL) THEN EXIT;
varTemp = '';
RESULT = 0;
WHILE (NOT parString LIKE varTemp) DO
BEGIN
varTemp = varTemp || '_';
RESULT = RESULT + 1;
END
END
^
/* Stored Procedure: SUPPORT$STRUCTURED_STATEMENT */
CREATE PROCEDURE SUPPORT$STRUCTURED_STATEMENT (
PARNUMBER INTEGER)
RETURNS (
RESULT CHAR (12))
AS
DECLARE VARIABLE varNumber VARCHAR(10);
DECLARE VARIABLE varMOD INTEGER;
DECLARE VARIABLE varDividend INTEGER;
DECLARE VARIABLE varLength INTEGER;
DECLARE VARIABLE varCheck VARCHAR(2);
BEGIN
/* Procedure body */
varNumber = CAST(parNumber AS VARCHAR(10));
varDividend = CAST(varNumber AS INTEGER);
EXECUTE PROCEDURE SUPPORT$MOD(varDividend, 97) RETURNING_VALUES :varMOD;
EXECUTE PROCEDURE SUPPORT$STR_LEN(varNumber) RETURNING_VALUES :varLength;
WHILE (varLength<10) DO
BEGIN
varNumber = '0' || varNumber;
EXECUTE PROCEDURE SUPPORT$STR_LEN(varNumber) RETURNING_VALUES :varLength;
END
varCheck = CAST(varMod AS VARCHAR(2));
EXECUTE PROCEDURE SUPPORT$STR_LEN(varCheck) RETURNING_VALUES :varLength;
WHILE (varLength<2) DO
BEGIN
varCheck = '0' || varCheck;
EXECUTE PROCEDURE SUPPORT$STR_LEN(varCheck) RETURNING_VALUES :varLength;
END
RESULT = varNumber || varCheck;
SUSPEND;
END
^
SET TERM ; ^
SET TERM ^ ;
/* Triggers definition */
/* Trigger: ACCOUNTS$ACCOUNTS_BI */
CREATE TRIGGER ACCOUNTS$ACCOUNTS_BI FOR ACCOUNTS$ACCOUNTS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.ACCOUNT_ID IS NULL) THEN
NEW.ACCOUNT_ID = GEN_ID(ACCOUNTS$NEXT_ID, 1);
END
^
/* Trigger: ACCOUNTS$C_SET_CODA_ID */
CREATE TRIGGER ACCOUNTS$C_SET_CODA_ID FOR ACCOUNTS$CODA ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.CODA_ID IS NULL) THEN
NEW.CODA_ID = GEN_ID(ACCOUNTS$NEXT_ID, 1);
END
^
/* Trigger: "ACCOUNTS$J-CONVERT" */
CREATE TRIGGER "ACCOUNTS$J-CONVERT" FOR ACCOUNTS$JOURNAL ACTIVE
AFTER INSERT POSITION 0
AS
BEGIN
IF (NEW.FILE_ID IS NOT NULL) THEN
BEGIN
EXECUTE PROCEDURE ACCOUNTS$SET_PAYEMENT_TYPE (NEW.JOURNAL_ID, NEW.FILE_ID, NEW.AMOUNT, NEW.VALUTA_DATE);
END
IF (NEW.INVOICED = 1) THEN
BEGIN
UPDATE FILES$PAYMENTS
SET FILES$PAYMENTS.INVOICED = 1
WHERE FILES$PAYMENTS.JOURNAL_ID = NEW.JOURNAL_ID;
END
END
^
/* Trigger: ACCOUNTS$J_SET_JOURNAL_ID */
CREATE TRIGGER ACCOUNTS$J_SET_JOURNAL_ID FOR ACCOUNTS$JOURNAL ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.JOURNAL_ID IS NULL) THEN
BEGIN
NEW.JOURNAL_ID = GEN_ID(ACCOUNTS$NEXT_ID, 1);
END
END
^
/* Trigger: ACCOUNTS$SET_INVOICABLE_AU */
CREATE TRIGGER ACCOUNTS$SET_INVOICABLE_AU FOR ACCOUNTS$ACCOUNTS ACTIVE
AFTER UPDATE POSITION 0
AS
DECLARE VARIABLE varDBKey CHAR(8);
BEGIN
IF (NEW.INVOICEABLE <> OLD.INVOICEABLE) THEN
BEGIN
FOR SELECT FILES$PAYMENTS.RDB$DB_KEY
FROM ACCOUNTS$JOURNAL
JOIN FILES$PAYMENTS ON (ACCOUNTS$JOURNAL.JOURNAL_ID = FILES$PAYMENTS.JOURNAL_ID)
WHERE (ACCOUNTS$JOURNAL.ACCOUNT_ID = NEW.ACCOUNT_ID) AND
(FILES$PAYMENTS.INVOICED = 0)
INTO :varDBKey DO
BEGIN
UPDATE FILES$PAYMENTS
SET FILES$PAYMENTS.INVOICEABLE = NEW.INVOICEABLE
WHERE FILES$PAYMENTS.RDB$DB_KEY = :varDBKey;
END
END
END
^
/* Trigger: ACCOUNTS$T_SET_TRANSACTION_ID */
CREATE TRIGGER ACCOUNTS$T_SET_TRANSACTION_ID FOR ACCOUNTS$TRANSACTIONS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.TRANSACTION_ID IS NULL) THEN
NEW.TRANSACTION_ID = GEN_ID(ACCOUNTS$NEXT_ID, 1);
END
^
/* Trigger: AI_CLIENTS$CONDITIONS_RECORD_ID */
CREATE TRIGGER AI_CLIENTS$CONDITIONS_RECORD_ID FOR CLIENTS$CONDITIONS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.RECORD_ID IS NULL) THEN
NEW.RECORD_ID = GEN_ID(CLIENTS$NEXT_ID, 1);
END
^
/* Trigger: AI_CLIENTS$C_SET_CLIENT_ID */
CREATE TRIGGER AI_CLIENTS$C_SET_CLIENT_ID FOR CLIENTS$CLIENTS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.CLIENT_ID IS NULL) THEN
NEW.CLIENT_ID = GEN_ID(CLIENTS$NEXT_ID, 1);
END
^
/* Trigger: AI_CODA$BOOKING_RECORD_ID */
CREATE TRIGGER AI_CODA$BOOKING_RECORD_ID FOR CODA$BOOKING ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.RECORD_ID IS NULL) THEN
NEW.RECORD_ID = GEN_ID(CODA$NEXT_ID, 1);
END
^
/* Trigger: AI_CODA$FILES_FILE_ID */
CREATE TRIGGER AI_CODA$FILES_FILE_ID FOR CODA$FILES ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.FILE_ID IS NULL) THEN
NEW.FILE_ID = GEN_ID(CODA$NEXT_ID, 1);
END
^
/* Trigger: AI_CODA$TG_SET_GROUP_ID */
CREATE TRIGGER AI_CODA$TG_SET_GROUP_ID FOR CODA$TRANSACTION_GROUP ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.GROUP_ID IS NULL) THEN
NEW.GROUP_ID = GEN_ID(CODA$NEXT_ID, 1);
END
^
/* Trigger: AI_CODA$TO_SET_OPERATION_ID */
CREATE TRIGGER AI_CODA$TO_SET_OPERATION_ID FOR CODA$TRANSACTIONS_OPERATIONS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.OPERATION_ID IS NULL) THEN
NEW.OPERATION_ID = GEN_ID(CODA$NEXT_ID, 1);
END
^
/* Trigger: AI_CODA$TT_SET_TYPE_ID */
CREATE TRIGGER AI_CODA$TT_SET_TYPE_ID FOR CODA$TRANSACTION_TYPES ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.TYPE_ID IS NULL) THEN
NEW.TYPE_ID = GEN_ID(CODA$NEXT_ID, 1);
END
^
/* Trigger: AI_FILES$COSTS_SET_COST_ID */
CREATE TRIGGER AI_FILES$COSTS_SET_COST_ID FOR FILES$COSTS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.COST_ID IS NULL) THEN
NEW.COST_ID = GEN_ID(FILES$NEXT_ID, 1);
END
^
/* Trigger: AI_FILES$SET_ACTION_ID */
CREATE TRIGGER AI_FILES$SET_ACTION_ID FOR FILES$ACTIONS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.ACTION_ID IS NULL) THEN
NEW.ACTION_ID = GEN_ID(FILES$NEXT_ID, 1);
END
^
/* Trigger: AI_FILES$SET_DEPTOR_ID */
CREATE TRIGGER AI_FILES$SET_DEPTOR_ID FOR FILES$DEPTORS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.DEPTOR_ID IS NULL) THEN
NEW.DEPTOR_ID = GEN_ID(FILES$NEXT_ID, 1);
END
^
/* Trigger: AI_FILES$SET_FILE_ACTION_ID */
CREATE TRIGGER AI_FILES$SET_FILE_ACTION_ID FOR FILES$FILE_ACTIONS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.FILE_ACTION_ID IS NULL) THEN
NEW.FILE_ACTION_ID = GEN_ID(FILES$NEXT_ID, 1);
END
^
/* Trigger: AI_FILES$SET_FILE_ID */
CREATE TRIGGER AI_FILES$SET_FILE_ID FOR FILES$FILES ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.FILE_ID IS NULL) THEN
NEW.FILE_ID = GEN_ID(FILES$NEXT_ID, 1);
END
^
/* Trigger: AI_FILES$SET_REFERENCE_ID */
CREATE TRIGGER AI_FILES$SET_REFERENCE_ID FOR FILES$REFERENCES ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.REFERENCE_ID IS NULL) THEN
NEW.REFERENCE_ID = GEN_ID(FILES$NEXT_ID, 1);
END
^
/* Trigger: AI_INVOICES$ID_SET_RECORD_ID */
CREATE TRIGGER AI_INVOICES$ID_SET_RECORD_ID FOR INVOICES$INVOICE_DETAILS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.INVOICE_DETAIL_ID IS NULL) THEN
NEW.INVOICE_DETAIL_ID = GEN_ID(INVOICES$NEXT_ID, 1);
END
^
/* Trigger: AI_INVOICES$INVOICES_INVOICE_ID */
CREATE TRIGGER AI_INVOICES$INVOICES_INVOICE_ID FOR INVOICES$INVOICES ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.INVOICE_ID IS NULL) THEN
NEW.INVOICE_ID = GEN_ID(INVOICES$NEXT_ID, 1);
END
^
/* Trigger: AI_SUPPORT$SET_COUNTRY_ID */
CREATE TRIGGER AI_SUPPORT$SET_COUNTRY_ID FOR SUPPORT$COUNTRIES ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.COUNTRY_ID IS NULL) THEN
NEW.COUNTRY_ID = GEN_ID(SUPPORT$NEXT_ID, 1);
END
^
/* Trigger: AI_SUPPORT$SET_LANGUAGE_ID */
CREATE TRIGGER AI_SUPPORT$SET_LANGUAGE_ID FOR SUPPORT$LANGUAGES ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.LANGUAGE_ID IS NULL) THEN
NEW.LANGUAGE_ID = GEN_ID(SUPPORT$NEXT_ID, 1);
END
^
/* Trigger: AI_SUPPORT$TITLES_TITLE_ID */
CREATE TRIGGER AI_SUPPORT$TITLES_TITLE_ID FOR SUPPORT$TITLES ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.TITLE_ID IS NULL) THEN
NEW.TITLE_ID = GEN_ID(SUPPORT$NEXT_ID, 1);
END
^
/* Trigger: AI_SYSTEM$SET_COLLECTOR_ID */
CREATE TRIGGER AI_SYSTEM$SET_COLLECTOR_ID FOR SYSTEM$COLLECTORS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.COLLECTOR_ID IS NULL) THEN
NEW.COLLECTOR_ID = GEN_ID(SYSTEM$NEXT_ID, 1);
END
^
/* Trigger: AI_SYSTEM$SET_EMPLOYEE_ID */
CREATE TRIGGER AI_SYSTEM$SET_EMPLOYEE_ID FOR SYSTEM$EMPLOYEES ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.EMPLOYEE_ID IS NULL) THEN
NEW.EMPLOYEE_ID = GEN_ID(SYSTEM$NEXT_ID, 1);
END
^
/* Trigger: BI_FILES$D_CHECK_VALUES */
CREATE TRIGGER BI_FILES$D_CHECK_VALUES FOR FILES$DEPTORS ACTIVE
BEFORE INSERT POSITION 1
AS
BEGIN
IF (NEW.LANGUAGE_ID IS NULL) THEN
BEGIN
SELECT SUPPORT$LANGUAGES.LANGUAGE_ID FROM SUPPORT$LANGUAGES WHERE SUPPORT$LANGUAGES.SYSTEM_DEFAULT = 1 INTO NEW.LANGUAGE_ID;
END
END
^
/* Trigger: BI_FILES$F_CHECK_VALUES */
CREATE TRIGGER BI_FILES$F_CHECK_VALUES FOR FILES$FILES ACTIVE
BEFORE INSERT POSITION 1
AS
BEGIN
/* Trigger body */
IF (NEW.STATE_ID IS NULL) THEN
BEGIN
NEW.STATE_ID = 0;
END
IF (NEW.COLLECTOR_ID IS NULL) THEN
BEGIN
SELECT SYSTEM$COLLECTORS.COLLECTOR_ID FROM SYSTEM$COLLECTORS WHERE SYSTEM$COLLECTORS.SYSTEM_DEFAULT = 1 INTO NEW.COLLECTOR_ID;
END
IF (NEW.STRUCTURED_STATEMENT IS NULL) THEN
BEGIN
EXECUTE PROCEDURE SUPPORT$STRUCTURED_STATEMENT (NEW.FILE_NR) RETURNING_VALUES NEW.STRUCTURED_STATEMENT;
END
END
^
/* Trigger: BI_SUPPORT$SET_ZIP_CODE_ID */
CREATE TRIGGER BI_SUPPORT$SET_ZIP_CODE_ID FOR SUPPORT$ZIP_CODES ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.ZIP_CODE_ID IS NULL) THEN
NEW.ZIP_CODE_ID = GEN_ID(SUPPORT$NEXT_ID, 1);
END
^
/* Trigger: BI_SUPPORT$ZC_CHECK_VALUES */
CREATE TRIGGER BI_SUPPORT$ZC_CHECK_VALUES FOR SUPPORT$ZIP_CODES ACTIVE
BEFORE INSERT POSITION 1
AS
BEGIN
/* Trigger body */
IF (NEW.COUNTRY_ID IS NULL) THEN
BEGIN
SELECT SUPPORT$COUNTRIES.COUNTRY_ID FROM SUPPORT$COUNTRIES WHERE SUPPORT$COUNTRIES.SYSTEM_DEFAULT = 1 INTO NEW.COUNTRY_ID;
END
END
^
/* Trigger: BI_SYSTEM$COL_CHECK_VALUES */
CREATE TRIGGER BI_SYSTEM$COL_CHECK_VALUES FOR SYSTEM$COLLECTORS ACTIVE
BEFORE INSERT POSITION 1
AS
BEGIN
/* Trigger body */
IF (NEW.LANGUAGE_ID IS NULL) THEN
BEGIN
SELECT SUPPORT$LANGUAGES.LANGUAGE_ID FROM SUPPORT$LANGUAGES WHERE SUPPORT$LANGUAGES.SYSTEM_DEFAULT = 1 INTO NEW.LANGUAGE_ID;
END
IF (NEW.ZIP_CODE_ID IS NULL) THEN
BEGIN
SELECT SUPPORT$ZIP_CODES.ZIP_CODE_ID FROM SUPPORT$ZIP_CODES WHERE SUPPORT$ZIP_CODES.CODE = 'UNKNOWN' INTO NEW.ZIP_CODE_ID;
END
END
^
/* Trigger: CLIENTS$BI_C_CHECK_VALUES */
CREATE TRIGGER CLIENTS$BI_C_CHECK_VALUES FOR CLIENTS$CLIENTS ACTIVE
BEFORE INSERT POSITION 1
AS
BEGIN
IF (NEW.ZIP_CODE_ID IS NULL) THEN
BEGIN
SELECT SUPPORT$ZIP_CODES.ZIP_CODE_ID FROM SUPPORT$ZIP_CODES WHERE SUPPORT$ZIP_CODES.CODE = 'UNKNOWN' INTO NEW.ZIP_CODE_ID;
END
IF (NEW.LANGUAGE_ID IS NULL) THEN
BEGIN
SELECT SUPPORT$LANGUAGES.LANGUAGE_ID FROM SUPPORT$LANGUAGES WHERE SUPPORT$LANGUAGES.SYSTEM_DEFAULT = 1 INTO NEW.LANGUAGE_ID;
END
END
^
/* Trigger: CODA$FS_SET_SECTION_ID */
CREATE TRIGGER CODA$FS_SET_SECTION_ID FOR CODA$FILE_SECTIONS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.SECTION_ID IS NULL) THEN
NEW.SECTION_ID = GEN_ID(CODA$NEXT_ID, 1);
END
^
/* Trigger: CODA$SET_ACCOUNT_ID */
CREATE TRIGGER CODA$SET_ACCOUNT_ID FOR CODA$FILE_SECTIONS ACTIVE
BEFORE INSERT POSITION 1
AS
BEGIN
SELECT ACCOUNTS$ACCOUNTS.ACCOUNT_ID
FROM ACCOUNTS$ACCOUNTS
WHERE ACCOUNTS$ACCOUNTS.ACCOUNT_NR = NEW.ACCOUNT_NO
INTO NEW.ACCOUNT_ID;
END
^
/* Trigger: CODA$TRANSACTIONS_AI */
CREATE TRIGGER CODA$TRANSACTIONS_AI FOR CODA$TRANSACTIONS ACTIVE
AFTER INSERT POSITION 0
AS
BEGIN
IF (NEW.PARENT_ID IS NOT NULL) THEN
BEGIN
UPDATE CODA$TRANSACTIONS PARENT
SET PARENT.HAS_DETAILS = 1
WHERE PARENT.TRANSACTION_ID = NEW.PARENT_ID;
END
END
^
/* Trigger: CODA$TRANSACTIONS_CHECK_BI */
CREATE TRIGGER CODA$TRANSACTIONS_CHECK_BI FOR CODA$TRANSACTIONS ACTIVE
BEFORE INSERT POSITION 1
AS
DECLARE VARIABLE varFileID INTEGER;
DECLARE VARIABLE varStatement CHAR(255);
DECLARE VARIABLE varFileNR INTEGER;
BEGIN
/* Check operation type */
IF ((NEW.OPERATION_CODE LIKE '00150%') OR (NEW.OPERATION_CODE LIKE '00152%') OR (NEW.OPERATION_CODE LIKE '00950%')) THEN
BEGIN
IF ((NEW.STRUCTURED_STATEMENT = 1) AND ((NEW.STATEMENT LIKE '101%') OR (NEW.STATEMENT LIKE '102%'))) THEN
BEGIN
varStatement = UDF_SUBSTR(NEW.STATEMENT,4, 16);
SELECT FILES$FILES.FILE_ID
FROM FILES$FILES
WHERE FILES$FILES.STRUCTURED_STATEMENT = :varStatement
INTO :varFileID;
IF (varFileID IS NOT NULL) THEN
BEGIN
INSERT INTO CODA$BOOKING (TRANSACTION_ID, FILE_ID, AMOUNT) VALUES (NEW.TRANSACTION_ID, :varFileID, NEW.AMOUNT);
NEW.LINK_TYPE = 1;
NEW.ACTION_CODE = 9; /* OK */
END
END
IF ((NEW.STRUCTURED_STATEMENT = 0) OR (NEW.STRUCTURED_STATEMENT IS NULL)) THEN /* Check IF Statement is a Number*/
BEGIN
varStatement = NEW.STATEMENT;
IF (NEW.STATEMENT STARTING WITH 'DOSSIER NR. ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 13, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'DOSSIER NR ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 12, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'DOSSIER NO ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 12, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'DOSSIER REF ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 13, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'REF DOSSIER ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 13, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'DOSSIER N ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 11, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'DOSSIER ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 9, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'DOSS ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 6, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'DOS. ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 6, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'REF. ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 6, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'DOS ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 5, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'REF ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 5, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'DOS.') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 5, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'REF.') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 5, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'REF ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 5, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'NO ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 4, 255);
EXECUTE PROCEDURE SUPPORT$NUMPART(:varStatement) RETURNING_VALUES :varStatement;
varFileNR = CAST(varStatement AS INTEGER);
SELECT FILES$FILES.FILE_ID
FROM FILES$FILES
WHERE FILES$FILES.FILE_NR = :varFileNr
INTO :varFileID;
IF (varFileID IS NOT NULL) THEN
BEGIN
INSERT INTO CODA$BOOKING (TRANSACTION_ID, FILE_ID, AMOUNT) VALUES (NEW.TRANSACTION_ID, :varFileID, NEW.AMOUNT);
NEW.LINK_TYPE = 1;
NEW.ACTION_CODE = 8; /* Should be checked */
END
WHEN SQLCODE -413 DO
BEGIN
NEW.ACTION_CODE = 1;
END
WHEN SQLCODE -802 DO
BEGIN
NEW.ACTION_CODE = 1;
END
END
END
IF (NEW.OPERATION_CODE LIKE '84150100') THEN
BEGIN
IF ((NEW.STRUCTURED_STATEMENT = 1) AND (NEW.STATEMENT LIKE '105%')) THEN
BEGIN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 48, 60);
NEW.LINK_TYPE = 1;
SELECT FILES$FILES.FILE_ID
FROM FILES$FILES
WHERE FILES$FILES.STRUCTURED_STATEMENT = :varStatement
INTO :varFileID;
IF (varFileID IS NOT NULL) THEN
BEGIN
INSERT INTO CODA$BOOKING (TRANSACTION_ID, FILE_ID, AMOUNT) VALUES (NEW.TRANSACTION_ID, :varFileID, NEW.AMOUNT);
NEW.LINK_TYPE = 1;
NEW.ACTION_CODE = 9; /* OK */
END
END
END
END
^
/* Trigger: CODA$T_SET_TRANSACTION_ID */
CREATE TRIGGER CODA$T_SET_TRANSACTION_ID FOR CODA$TRANSACTIONS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.TRANSACTION_ID IS NULL) THEN
NEW.TRANSACTION_ID = GEN_ID(CODA$NEXT_ID, 1);
END
^
/* Trigger: FILES$AD_F_SET_FILE_COUNT */
CREATE TRIGGER FILES$AD_F_SET_FILE_COUNT FOR FILES$FILES ACTIVE
AFTER DELETE POSITION 0
AS
BEGIN
IF (OLD.DATE_CLOSED IS NULL) THEN
BEGIN
UPDATE CLIENTS$CLIENTS
SET CLIENTS$CLIENTS.OPEN_FILES = CLIENTS$CLIENTS.OPEN_FILES - 1,
CLIENTS$CLIENTS.FILE_COUNT = CLIENTS$CLIENTS.FILE_COUNT - 1
WHERE CLIENTS$CLIENTS.CLIENT_ID = OLD.CLIENT_ID;
UPDATE FILES$DEPTORS
SET FILES$DEPTORS.FILE_COUNT = FILES$DEPTORS.FILE_COUNT - 1,
FILES$DEPTORS.OPEN_FILES = FILES$DEPTORS.OPEN_FILES - 1
WHERE FILES$DEPTORS.DEPTOR_ID = OLD.DEPTOR_ID;
END
ELSE
BEGIN
UPDATE CLIENTS$CLIENTS
SET CLIENTS$CLIENTS.FILE_COUNT = CLIENTS$CLIENTS.FILE_COUNT - 1
WHERE CLIENTS$CLIENTS.CLIENT_ID = OLD.CLIENT_ID;
UPDATE FILES$DEPTORS
SET FILES$DEPTORS.FILE_COUNT = FILES$DEPTORS.FILE_COUNT - 1
WHERE FILES$DEPTORS.DEPTOR_ID = OLD.DEPTOR_ID;
END
END
^
/* Trigger: FILES$AI_F_SET_FILE_COUNT */
CREATE TRIGGER FILES$AI_F_SET_FILE_COUNT FOR FILES$FILES ACTIVE
AFTER INSERT POSITION 0
AS
BEGIN
/* Trigger body */
IF (NEW.DATE_CLOSED IS NOT NULL) THEN
BEGIN
UPDATE CLIENTS$CLIENTS
SET CLIENTS$CLIENTS.FILE_COUNT = CLIENTS$CLIENTS.FILE_COUNT + 1
WHERE CLIENTS$CLIENTS.CLIENT_ID = NEW.CLIENT_ID;
UPDATE FILES$DEPTORS
SET FILES$DEPTORS.FILE_COUNT = FILES$DEPTORS.FILE_COUNT + 1
WHERE FILES$DEPTORS.DEPTOR_ID = NEW.DEPTOR_ID;
END
ELSE
BEGIN
UPDATE CLIENTS$CLIENTS
SET CLIENTS$CLIENTS.FILE_COUNT = CLIENTS$CLIENTS.FILE_COUNT + 1,
CLIENTS$CLIENTS.OPEN_FILES = CLIENTS$CLIENTS.OPEN_FILES + 1
WHERE CLIENTS$CLIENTS.CLIENT_ID = NEW.CLIENT_ID;
UPDATE FILES$DEPTORS
SET FILES$DEPTORS.FILE_COUNT = FILES$DEPTORS.FILE_COUNT + 1,
FILES$DEPTORS.OPEN_FILES = FILES$DEPTORS.OPEN_FILES + 1
WHERE FILES$DEPTORS.DEPTOR_ID = NEW.DEPTOR_ID;
END
END
^
/* Trigger: FILES$AU_F_SET_FILE_COUNT */
CREATE TRIGGER FILES$AU_F_SET_FILE_COUNT FOR FILES$FILES ACTIVE
AFTER UPDATE POSITION 0
AS
BEGIN
/* Check reopen */
IF ((OLD.DATE_CLOSED IS NOT NULL) AND (NEW.DATE_CLOSED IS NULL)) THEN
BEGIN
UPDATE CLIENTS$CLIENTS
SET CLIENTS$CLIENTS.OPEN_FILES = CLIENTS$CLIENTS.OPEN_FILES + 1
WHERE CLIENTS$CLIENTS.CLIENT_ID = NEW.CLIENT_ID;
UPDATE FILES$DEPTORS
SET FILES$DEPTORS.OPEN_FILES = FILES$DEPTORS.OPEN_FILES + 1
WHERE FILES$DEPTORS.DEPTOR_ID = NEW.DEPTOR_ID;
END
IF ((NEW.DATE_CLOSED IS NOT NULL) AND (OLD.DATE_CLOSED IS NULL)) THEN
BEGIN
UPDATE CLIENTS$CLIENTS
SET CLIENTS$CLIENTS.OPEN_FILES = CLIENTS$CLIENTS.OPEN_FILES - 1
WHERE CLIENTS$CLIENTS.CLIENT_ID = NEW.CLIENT_ID;
UPDATE FILES$DEPTORS
SET FILES$DEPTORS.OPEN_FILES = FILES$DEPTORS.OPEN_<br/><br/>(Message over 64 KB, truncated)
gbak: restoring privilege for user SYSDBA
gbak: ERROR: attempt to store duplicate value (visible to active
transactions) i
n unique index "RDB$INDEX_11"
gbak: Exiting before completion due to errors
I attached the script for creating the DB. Try to create it, then
backup and restore it and you'll get the error.
Regards,
Raf Schandevyl
Claudio Valderrama C. wrote:
>Did you use gbak with -v for verbosity? What error message do you get?----------
>
>C.
>---------
>Claudio Valderrama C.
>Ingeniero en Informática - Consultor independiente
>http://www.cvalde.com - http://www.firebirdSQL.org
>
>
>"Raf Schandevyl" <Raf.Schandevyl@...> wrote in message
>news:<3BFF6584.1070105@...>...
>
>>Since I installed the RC1 I can't restore my DB anymore. From my point
>>of view it has something to do with the cascading deletes & updates on
>>my foreign key definitions.
>>
>>Anyone having the same problems ?
>>
>>Regards
>>Raf Schandevyl
>>
>>
>>
>>To unsubscribe from this group, send an email to:
>>ib-support-unsubscribe@egroups.com
>>
>>
>>
>>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>>
>>
>>
>
>
>
>To unsubscribe from this group, send an email to:
>ib-support-unsubscribe@egroups.com
>
>
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>
>
SET SQL DIALECT 3;
CREATE DATABASE 'localhost:E:\Databanken\Incasso\v1\incasso1.gdb'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 8192
DEFAULT CHARACTER SET ISO8859_1;
/* Domains definitions */
CREATE DOMAIN DOM_ACCOUNT_NR AS VARCHAR (25) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_ADDRESS AS VARCHAR (200) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_BOOLEAN AS SMALLINT
DEFAULT 0
NOT NULL;
CREATE DOMAIN DOM_BTW_NR AS CHAR (15) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_CITY AS VARCHAR (150) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_CODE AS VARCHAR (20) CHARACTER SET ISO8859_1
NOT NULL;
CREATE DOMAIN DOM_CONDITION_TYPE AS CHAR (1) CHARACTER SET ISO8859_1
NOT NULL;
CREATE DOMAIN DOM_COUNT AS INTEGER
DEFAULT 0
NOT NULL;
CREATE DOMAIN DOM_CURRENCY_CODE AS CHAR (3) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_CURRENT_DATE AS DATE
DEFAULT CURRENT_DATE;
CREATE DOMAIN DOM_CURRENT_USER AS VARCHAR (20) CHARACTER SET ISO8859_1
DEFAULT USER;
CREATE DOMAIN DOM_DATE AS DATE;
CREATE DOMAIN DOM_DESCRIPTION AS VARCHAR (100) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_EMAIL AS VARCHAR (100) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_FILE_NAME AS VARCHAR (200) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_FILE_NR AS INTEGER
DEFAULT 1
NOT NULL;
CREATE DOMAIN DOM_INVOICENR AS INTEGER
NOT NULL;
CREATE DOMAIN DOM_LINK_TYPE AS SMALLINT;
CREATE DOMAIN DOM_MONEY AS NUMERIC (18, 3)
DEFAULT 0
NOT NULL;
CREATE DOMAIN DOM_NAME AS VARCHAR (200) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_OPERATION_CODE AS CHAR (8) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_PAYEMENT_TYPE AS CHAR (1) CHARACTER SET ISO8859_1
NOT NULL;
CREATE DOMAIN DOM_PERCENT AS NUMERIC (5, 2)
DEFAULT 0
NOT NULL;
CREATE DOMAIN DOM_PHONE AS VARCHAR (12) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_RECORD_ID AS INTEGER;
CREATE DOMAIN DOM_REFERENCE AS VARCHAR (50) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_REMARKS AS BLOB sub_type 1 segment size 1 CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_SIGN AS SMALLINT
DEFAULT 0
NOT NULL;
CREATE DOMAIN DOM_STATE AS SMALLINT;
CREATE DOMAIN DOM_STATEMENT AS VARCHAR (255) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_STRUCTURED_STATEMENT AS CHAR (12) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_TEXT AS BLOB sub_type 1 segment size 1 CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_TRANSACTION_GROUP AS CHAR (2) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_TRANSACTION_OPERATION AS CHAR (2) CHARACTER SET ISO8859_1;
CREATE DOMAIN DOM_TRANSACTION_TYPE AS CHAR (1) CHARACTER SET ISO8859_1
NOT NULL;
CREATE DOMAIN DOM_VAT_NO AS VARCHAR (20) CHARACTER SET ISO8859_1;
CREATE DOMAIN SEQUENCE_NO AS SMALLINT
DEFAULT 0
NOT NULL;
/* Generators definitions */
CREATE GENERATOR ACCOUNTS$NEXT_ID ;
CREATE GENERATOR CLIENTS$NEXT_ID ;
CREATE GENERATOR CODA$NEXT_ID ;
CREATE GENERATOR FILES$NEXT_ID ;
CREATE GENERATOR FINANCE$NEXT_ID ;
CREATE GENERATOR INVOICES$NEXT_ID ;
CREATE GENERATOR RDB$SECURITY ;
CREATE GENERATOR REPORTS$NEXT_ID ;
CREATE GENERATOR SUPPORT$NEXT_ID ;
CREATE GENERATOR SYSTEM$NEXT_ID ;
CREATE GENERATOR TEST$NUMPART_RECORD_ID_GEN ;
/* External functions definition */
DECLARE EXTERNAL FUNCTION UDF_SUBSTR
CSTRING (200),
SMALLINT,
SMALLINT
RETURNS CSTRING (200) FREE_IT
ENTRY_POINT 'IB_UDF_substr' MODULE_NAME 'ib_udf';
/* Tables definitions */
CREATE TABLE ACCOUNTS$ACCOUNTS (
ACCOUNT_ID DOM_RECORD_ID NOT NULL,
CODE DOM_CODE NOT NULL collate ISO8859_1,
DESCRIPTION DOM_DESCRIPTION collate ISO8859_1,
ACCOUNT_NR DOM_ACCOUNT_NR collate ISO8859_1,
CODA_IMPORT DOM_BOOLEAN
DEFAULT 1 NOT NULL,
INVOICEABLE DOM_BOOLEAN
DEFAULT 1 NOT NULL);
CREATE TABLE ACCOUNTS$CODA (
CODA_ID DOM_RECORD_ID NOT NULL,
FILE_NAME DOM_FILE_NAME NOT NULL collate ISO8859_1,
CREATION_DATE DOM_CURRENT_DATE,
CREATION_USER DOM_CURRENT_USER collate ISO8859_1,
STATE_ID DOM_STATE);
CREATE TABLE ACCOUNTS$JOURNAL (
JOURNAL_ID DOM_RECORD_ID NOT NULL,
TRANSACTION_ID DOM_RECORD_ID NOT NULL,
ACCOUNT_ID DOM_RECORD_ID NOT NULL,
AMOUNT DOM_MONEY NOT NULL,
DESCRIPTION DOM_DESCRIPTION collate ISO8859_1,
FILE_ID DOM_RECORD_ID,
INVOICED DOM_BOOLEAN NOT NULL,
VALUTA_DATE DOM_CURRENT_DATE);
CREATE TABLE ACCOUNTS$TRANSACTIONS (
TRANSACTION_ID DOM_RECORD_ID NOT NULL,
CODA_ID DOM_RECORD_ID,
CREATION_DATE DOM_CURRENT_DATE,
CREATION_USER DOM_CURRENT_USER collate ISO8859_1,
DESCRIPTION DOM_DESCRIPTION collate ISO8859_1);
CREATE TABLE CLIENTS$CLIENTS (
CLIENT_ID DOM_RECORD_ID NOT NULL,
CODE DOM_CODE NOT NULL collate ISO8859_1,
NAME DOM_NAME collate ISO8859_1,
ADDRESS DOM_ADDRESS collate ISO8859_1,
ZIP_CODE_ID DOM_RECORD_ID NOT NULL,
INVOICE_NAME DOM_NAME collate ISO8859_1,
INVOICE_ADDRESS DOM_ADDRESS collate ISO8859_1,
INVOICE_ZIP_ID DOM_RECORD_ID,
VAT_NO DOM_VAT_NO collate ISO8859_1,
LANGUAGE_ID DOM_RECORD_ID NOT NULL,
E_MAIL DOM_EMAIL collate ISO8859_1,
BANK_ACCOUNT_NR DOM_ACCOUNT_NR collate ISO8859_1,
CURRENT_INTREST_PERCENT DOM_PERCENT NOT NULL,
CURRENT_COST_PERCENT DOM_PERCENT NOT NULL,
CURRENT_COST_MINIMUM DOM_MONEY NOT NULL,
FILE_COUNT DOM_COUNT,
OPEN_FILES DOM_COUNT NOT NULL,
TELEPHONE DOM_PHONE collate ISO8859_1,
TELEFAX DOM_PHONE collate ISO8859_1,
CONTACT DOM_NAME collate ISO8859_1);
CREATE TABLE CLIENTS$CONDITIONS (
RECORD_ID DOM_RECORD_ID NOT NULL,
CLIENT_ID DOM_RECORD_ID,
CONDITION_TYPE DOM_CONDITION_TYPE NOT NULL collate ISO8859_1,
FROM_DATE DOM_CURRENT_DATE,
END_VALUE DOM_MONEY NOT NULL,
INVOICE_PERCENT DOM_PERCENT NOT NULL,
INVOICE_MINIMUM DOM_MONEY NOT NULL);
CREATE TABLE CODA$BOOKING (
RECORD_ID DOM_RECORD_ID NOT NULL,
TRANSACTION_ID DOM_RECORD_ID,
FILE_ID DOM_RECORD_ID,
AMOUNT DOM_MONEY NOT NULL);
CREATE TABLE CODA$FILES (
FILE_ID DOM_RECORD_ID NOT NULL,
FILE_NAME DOM_FILE_NAME NOT NULL collate ISO8859_1);
CREATE TABLE CODA$FILE_SECTIONS (
SECTION_ID DOM_RECORD_ID NOT NULL,
FILE_ID DOM_RECORD_ID NOT NULL,
CREATION_DATE DOM_DATE NOT NULL,
SEQUENCE_NO SEQUENCE_NO NOT NULL,
ACCOUNT_NO DOM_ACCOUNT_NR NOT NULL collate ISO8859_1,
AMOUNT DOM_MONEY NOT NULL,
AMOUNT_DATE DOM_DATE NOT NULL,
ACCOUNT_ID DOM_RECORD_ID);
CREATE TABLE CODA$TRANSACTIONS (
TRANSACTION_ID DOM_RECORD_ID NOT NULL,
SECTION_ID DOM_RECORD_ID NOT NULL,
AMOUNT DOM_MONEY NOT NULL,
VALUTA_DATE DOM_DATE NOT NULL,
STRUCTURED_STATEMENT DOM_BOOLEAN NOT NULL,
"STATEMENT" DOM_STATEMENT collate ISO8859_1,
BANK_REFERENCE DOM_CODE NOT NULL collate ISO8859_1,
OPERATION_CODE DOM_OPERATION_CODE collate ISO8859_1,
ACCOUNT_NO DOM_ACCOUNT_NR collate ISO8859_1,
NAME DOM_NAME collate ISO8859_1,
ADDRESS DOM_NAME collate ISO8859_1,
CITY DOM_NAME collate ISO8859_1,
PARENT_ID DOM_RECORD_ID,
BOOKED DOM_BOOLEAN NOT NULL,
HAS_DETAILS DOM_BOOLEAN NOT NULL,
CLIENT_REFERENCE DOM_DESCRIPTION collate ISO8859_1,
ORIGINAL_AMOUNT DOM_MONEY NOT NULL,
ORIGINAL_CURRENCY DOM_CURRENCY_CODE collate ISO8859_1,
LINK_TYPE DOM_STATE,
ACTION_CODE DOM_STATE,
CHECKED_STATEMENT DOM_STATEMENT collate ISO8859_1,
DOSSIERNR DOM_STATEMENT collate ISO8859_1,
CHECKED DOM_BOOLEAN NOT NULL);
CREATE TABLE CODA$TRANSACTIONS_OPERATIONS (
OPERATION_ID DOM_RECORD_ID NOT NULL,
GROUP_ID DOM_RECORD_ID NOT NULL,
CODE DOM_TRANSACTION_OPERATION NOT NULL collate ISO8859_1,
DESCRIPTION DOM_DESCRIPTION collate ISO8859_1);
CREATE TABLE CODA$TRANSACTION_GROUP (
GROUP_ID DOM_RECORD_ID NOT NULL,
CODE DOM_TRANSACTION_GROUP NOT NULL collate ISO8859_1,
DESCRIPTION DOM_DESCRIPTION collate ISO8859_1);
CREATE TABLE CODA$TRANSACTION_TYPES (
TYPE_ID DOM_RECORD_ID NOT NULL,
CODE DOM_TRANSACTION_TYPE NOT NULL collate ISO8859_1,
DESCRIPTION DOM_DESCRIPTION collate ISO8859_1);
CREATE TABLE FILES$ACTIONS (
ACTION_ID DOM_RECORD_ID NOT NULL,
CODE DOM_CODE NOT NULL collate ISO8859_1,
DESCRIPTION DOM_DESCRIPTION collate ISO8859_1,
AMOUNT DOM_MONEY NOT NULL,
INVOICEABLE DOM_BOOLEAN NOT NULL,
SYSTEM DOM_BOOLEAN NOT NULL);
CREATE TABLE FILES$COSTS (
COST_ID DOM_RECORD_ID NOT NULL,
CODE DOM_CODE NOT NULL collate ISO8859_1,
DESCRIPTION DOM_DESCRIPTION collate ISO8859_1,
INVOICEABLE DOM_BOOLEAN
DEFAULT 1 NOT NULL,
SYSTEM DOM_BOOLEAN NOT NULL);
CREATE TABLE FILES$DEPTORS (
DEPTOR_ID DOM_RECORD_ID NOT NULL,
NAME DOM_NAME collate ISO8859_1,
ADDRESS DOM_ADDRESS collate ISO8859_1,
ZIP_CODE_ID DOM_RECORD_ID,
LANGUAGE_ID DOM_RECORD_ID NOT NULL,
CREATION_DATE DOM_CURRENT_DATE,
CREATION_USER DOM_CURRENT_USER collate ISO8859_1,
E_MAIL DOM_EMAIL,
TITLE_ID DOM_RECORD_ID,
OLD_ID DOM_RECORD_ID NOT NULL,
TELEPHONE DOM_PHONE collate ISO8859_1,
TELEFAX DOM_PHONE collate ISO8859_1,
FILE_COUNT DOM_COUNT NOT NULL,
OPEN_FILES DOM_COUNT NOT NULL);
CREATE TABLE FILES$FILES (
FILE_ID DOM_RECORD_ID NOT NULL,
CLIENT_ID DOM_RECORD_ID NOT NULL,
FILE_NR DOM_FILE_NR,
DEPTOR_ID DOM_RECORD_ID NOT NULL,
REFERENCE DOM_REFERENCE,
COLLECTOR_ID DOM_RECORD_ID NOT NULL,
STATE_ID DOM_RECORD_ID NOT NULL,
CREATION_DATE DOM_CURRENT_DATE,
CREATION_USER DOM_CURRENT_USER,
DATE_CLOSED DOM_DATE,
AMOUNT DOM_MONEY NOT NULL,
COSTS DOM_MONEY
DEFAULT 0 NOT NULL,
INTEREST DOM_MONEY
DEFAULT 0 NOT NULL,
TOTAL COMPUTED BY (AMOUNT + COSTS + INTEREST),
PAYED_AMOUNT DOM_MONEY NOT NULL,
PAYED_INTEREST DOM_MONEY NOT NULL,
PAYED_COSTS DOM_MONEY NOT NULL,
PAYED_UNKNOWN DOM_MONEY NOT NULL,
PAYED_TOTAL COMPUTED BY (PAYED_AMOUNT + PAYED_INTEREST + PAYED_COSTS + PAYED_UNKNOWN),
PAYABLE COMPUTED BY (AMOUNT + COSTS + INTEREST - PAYED_AMOUNT - PAYED_INTEREST - PAYED_INTEREST),
CREATION_MONTH COMPUTED BY (EXTRACT(MONTH FROM CREATION_DATE)),
CREATION_YEAR COMPUTED BY (EXTRACT(YEAR FROM CREATION_DATE)),
SALDO_AMOUNT COMPUTED BY (AMOUNT - PAYED_AMOUNT),
SALDO_INTEREST COMPUTED BY (INTEREST - PAYED_INTEREST),
SALDO_COSTS COMPUTED BY (COSTS - PAYED_COSTS),
REFUND_STATEMENT DOM_STRUCTURED_STATEMENT collate ISO8859_1,
STRUCTURED_STATEMENT DOM_STRUCTURED_STATEMENT collate ISO8859_1,
LAST_ACTION_DATE DOM_CURRENT_DATE,
INVOICED DOM_BOOLEAN
DEFAULT 1 NOT NULL);
CREATE TABLE FILES$FILE_ACTIONS (
FILE_ACTION_ID DOM_RECORD_ID NOT NULL,
FILE_ID DOM_RECORD_ID NOT NULL,
ACTION_ID DOM_RECORD_ID NOT NULL,
REMARKS DOM_REMARKS,
ACTION_DATE DOM_CURRENT_DATE,
ACTION_USER DOM_CURRENT_USER collate ISO8859_1);
CREATE TABLE FILES$FILE_COSTS (
RECORD_ID DOM_RECORD_ID NOT NULL,
FILE_ID DOM_RECORD_ID NOT NULL,
COST_ID DOM_RECORD_ID DEFAULT 0 NOT NULL,
CREATION_DATE DOM_CURRENT_DATE,
AMOUNT DOM_MONEY NOT NULL,
INVOICED DOM_BOOLEAN NOT NULL,
INVOICEABLE DOM_BOOLEAN
DEFAULT 1 NOT NULL,
LINK_ID DOM_RECORD_ID);
CREATE TABLE FILES$PAYMENTS (
PAYEMENT_ID DOM_RECORD_ID NOT NULL,
FILE_ID DOM_RECORD_ID NOT NULL,
REFERENCE_ID DOM_RECORD_ID,
JOURNAL_ID DOM_RECORD_ID NOT NULL,
PAYMENT_DATE DOM_DATE NOT NULL,
AMOUNT DOM_MONEY NOT NULL,
INVOICE_ID DOM_RECORD_ID,
INVOICED DOM_BOOLEAN NOT NULL,
PAYMENT_FOR DOM_PAYEMENT_TYPE
DEFAULT 'A' NOT NULL collate ISO8859_1,
CREATION_DATE DOM_CURRENT_DATE,
CREATION_USER DOM_CURRENT_USER collate ISO8859_1,
INVOICEABLE DOM_BOOLEAN
DEFAULT 1 NOT NULL,
COMMISSION DOM_MONEY NOT NULL);
CREATE TABLE FILES$REFERENCES (
REFERENCE_ID DOM_RECORD_ID NOT NULL,
FILE_ID DOM_RECORD_ID NOT NULL,
REFERENCE DOM_REFERENCE collate ISO8859_1,
AMOUNT DOM_MONEY NOT NULL,
INTEREST_PERCENT DOM_PERCENT NOT NULL,
COST_PERCENT DOM_PERCENT NOT NULL,
COST_MINIMUM DOM_MONEY NOT NULL,
AUTO_CALCULATE DOM_BOOLEAN
DEFAULT 1 NOT NULL,
END_DATE DOM_DATE,
START_DATE DOM_DATE,
COSTS DOM_MONEY
DEFAULT 0 NOT NULL,
INTEREST DOM_MONEY
DEFAULT 0 NOT NULL,
CREATION_DATE DOM_CURRENT_DATE,
CREATION_USER DOM_CURRENT_USER collate ISO8859_1,
PAYED_AMOUNT DOM_MONEY NOT NULL,
PAYED_INTEREST DOM_MONEY NOT NULL,
PAYED_COSTS DOM_MONEY NOT NULL,
SALDO_AMOUNT COMPUTED BY (AMOUNT - PAYED_AMOUNT),
SALDO_INTEREST COMPUTED BY (INTEREST - PAYED_INTEREST),
SALDO_COSTS COMPUTED BY (COSTS - PAYED_COSTS));
CREATE TABLE FILES$REMARKS (
REMARK_ID DOM_RECORD_ID NOT NULL,
FILE_ID DOM_RECORD_ID NOT NULL,
REMARK DOM_TEXT,
CREATION_DATE DOM_CURRENT_DATE,
CREATION_USER DOM_CURRENT_USER collate ISO8859_1);
CREATE TABLE INVOICES$INVOICES (
INVOICE_ID DOM_RECORD_ID NOT NULL,
INVOICE_NR DOM_INVOICENR NOT NULL,
CLIENT_ID DOM_RECORD_ID NOT NULL,
INVOICE_DATE DOM_CURRENT_DATE NOT NULL);
CREATE TABLE INVOICES$INVOICE_DETAILS (
INVOICE_DETAIL_ID DOM_RECORD_ID NOT NULL,
INVOICE_ID DOM_RECORD_ID,
FILE_ID DOM_RECORD_ID,
AMOUNT DOM_MONEY NOT NULL);
CREATE TABLE SUPPORT$COUNTRIES (
COUNTRY_ID DOM_RECORD_ID NOT NULL,
CODE DOM_CODE NOT NULL collate ISO8859_1,
DESCRIPTION DOM_DESCRIPTION collate ISO8859_1,
TELEPHONE_CODE DOM_CODE NOT NULL collate ISO8859_1,
SYSTEM_DEFAULT DOM_BOOLEAN NOT NULL);
CREATE TABLE SUPPORT$LANGUAGES (
LANGUAGE_ID DOM_RECORD_ID NOT NULL,
CODE DOM_CODE NOT NULL collate ISO8859_1,
DESCRIPTION DOM_DESCRIPTION,
SYSTEM_DEFAULT DOM_BOOLEAN);
CREATE TABLE SUPPORT$TITLES (
TITLE_ID DOM_RECORD_ID NOT NULL,
LANGUAGE_ID DOM_RECORD_ID NOT NULL,
CODE DOM_CODE NOT NULL collate ISO8859_1,
DESCRIPTION DOM_DESCRIPTION collate ISO8859_1);
CREATE TABLE SUPPORT$ZIP_CODES (
ZIP_CODE_ID DOM_RECORD_ID NOT NULL,
COUNTRY_ID DOM_RECORD_ID NOT NULL,
CODE DOM_CODE NOT NULL collate ISO8859_1,
CITY DOM_CITY collate ISO8859_1,
POPULATION_COST DOM_MONEY NOT NULL);
CREATE TABLE SYSTEM$COLLECTORS (
COLLECTOR_ID DOM_RECORD_ID NOT NULL,
CODE DOM_CODE NOT NULL collate ISO8859_1,
NAME DOM_NAME collate ISO8859_1,
ADDRESS DOM_ADDRESS collate ISO8859_1,
ZIP_CODE_ID DOM_RECORD_ID NOT NULL,
BTW_NR DOM_BTW_NR collate ISO8859_1,
LANGUAGE_ID DOM_RECORD_ID NOT NULL,
SYSTEM_DEFAULT DOM_BOOLEAN NOT NULL);
CREATE TABLE SYSTEM$EMPLOYEES (
EMPLOYEE_ID DOM_RECORD_ID NOT NULL,
CODE DOM_CODE NOT NULL collate ISO8859_1,
NAME DOM_NAME collate ISO8859_1,
ADDRESS DOM_ADDRESS collate ISO8859_1,
ZIP_CODE_ID DOM_RECORD_ID NOT NULL,
LANGUAGE_ID DOM_RECORD_ID NOT NULL);
/* Views definition */
/* View: CLIENTS$CLIENTS_ALL_INFO */
CREATE VIEW CLIENTS$CLIENTS_ALL_INFO (
CLIENT_ID,
CODE,
NAME,
ADDRESS,
ZIP_CODE_ID,
INVOICE_NAME,
INVOICE_ADDRESS,
INVOICE_ZIP_ID,
VAT_NO,
LANGUAGE_ID,
E_MAIL,
BANK_ACCOUNT_NR,
CURRENT_INTREST_PERCENT,
CURRENT_COST_PERCENT,
CURRENT_COST_MINIMUM,
FILE_COUNT,
OPEN_FILES,
TELEPHONE,
TELEFAX,
CONTACT,
ZIP_CODE,
CITY,
LANGUAGE_CODE,
COUNTRY_CODE,
INVOICE_ZIP_CODE,
INVOICE_CITY)
AS
SELECT CLIENTS$CLIENTS.CLIENT_ID,
CLIENTS$CLIENTS.CODE,
CLIENTS$CLIENTS.NAME,
CLIENTS$CLIENTS.ADDRESS,
CLIENTS$CLIENTS.ZIP_CODE_ID,
CLIENTS$CLIENTS.INVOICE_NAME,
CLIENTS$CLIENTS.INVOICE_ADDRESS,
CLIENTS$CLIENTS.INVOICE_ZIP_ID,
CLIENTS$CLIENTS.VAT_NO,
CLIENTS$CLIENTS.LANGUAGE_ID,
CLIENTS$CLIENTS.E_MAIL,
CLIENTS$CLIENTS.BANK_ACCOUNT_NR,
CLIENTS$CLIENTS.CURRENT_INTREST_PERCENT,
CLIENTS$CLIENTS.CURRENT_COST_PERCENT,
CLIENTS$CLIENTS.CURRENT_COST_MINIMUM,
CLIENTS$CLIENTS.FILE_COUNT,
CLIENTS$CLIENTS.OPEN_FILES,
CLIENTS$CLIENTS.TELEPHONE,
CLIENTS$CLIENTS.TELEFAX,
CLIENTS$CLIENTS.CONTACT,
CLIENTS_ZIP_CODES.CODE,
CLIENTS_ZIP_CODES.CITY,
SUPPORT$LANGUAGES.CODE,
SUPPORT$COUNTRIES.CODE,
INVOICE_ZIP_CODES.CODE,
INVOICE_ZIP_CODES.CITY
FROM CLIENTS$CLIENTS
JOIN SUPPORT$LANGUAGES ON (SUPPORT$LANGUAGES.LANGUAGE_ID = CLIENTS$CLIENTS.LANGUAGE_ID)
JOIN SUPPORT$ZIP_CODES CLIENTS_ZIP_CODES ON (CLIENTS$CLIENTS.ZIP_CODE_ID = CLIENTS_ZIP_CODES.ZIP_CODE_ID)
JOIN SUPPORT$COUNTRIES ON (SUPPORT$ZIP_CODES.COUNTRY_ID = SUPPORT$COUNTRIES.COUNTRY_ID)
JOIN SUPPORT$ZIP_CODES INVOICE_ZIP_CODES ON (INVOICE_ZIP_CODES.ZIP_CODE_ID = CLIENTS$CLIENTS.INVOICE_ZIP_ID)
;
/* View: FILES$DEPTORS_ALL_INFO */
CREATE VIEW FILES$DEPTORS_ALL_INFO (
DEPTOR_ID,
NAME,
ADDRESS,
ZIP_CODE_ID,
LANGUAGE_ID,
CREATION_DATE,
CREATION_USER,
E_MAIL,
TITLE_ID,
OLD_ID,
TELEPHONE,
TELEFAX,
COUNTRY_CODE,
COUNTRY_DESCRIPTION,
COUNTRY_TELEPHONE_CODE,
ZIP_CODE,
CITY,
TITLE_CODE,
TITLE_DESCRIPTION,
LANGUAGE_CODE,
LANGUAGE_DESCRIPTION,
FILE_COUNT,
OPEN_FILES)
AS
SELECT FILES$DEPTORS.DEPTOR_ID,
FILES$DEPTORS.NAME,
FILES$DEPTORS.ADDRESS,
FILES$DEPTORS.ZIP_CODE_ID,
FILES$DEPTORS.LANGUAGE_ID,
FILES$DEPTORS.CREATION_DATE,
FILES$DEPTORS.CREATION_USER,
FILES$DEPTORS.E_MAIL,
FILES$DEPTORS.TITLE_ID,
FILES$DEPTORS.OLD_ID,
FILES$DEPTORS.TELEPHONE,
FILES$DEPTORS.TELEFAX,
SUPPORT$COUNTRIES.CODE,
SUPPORT$COUNTRIES.DESCRIPTION,
SUPPORT$COUNTRIES.TELEPHONE_CODE,
SUPPORT$ZIP_CODES.CODE,
SUPPORT$ZIP_CODES.CITY,
SUPPORT$TITLES.CODE,
SUPPORT$TITLES.DESCRIPTION,
SUPPORT$LANGUAGES.CODE,
SUPPORT$LANGUAGES.DESCRIPTION,
FILES$DEPTORS.FILE_COUNT,
FILES$DEPTORS.OPEN_FILES
FROM FILES$DEPTORS
JOIN SUPPORT$LANGUAGES ON (SUPPORT$LANGUAGES.LANGUAGE_ID = FILES$DEPTORS.LANGUAGE_ID)
JOIN SUPPORT$TITLES ON (FILES$DEPTORS.TITLE_ID = SUPPORT$TITLES.TITLE_ID)
JOIN SUPPORT$ZIP_CODES ON (FILES$DEPTORS.ZIP_CODE_ID = SUPPORT$ZIP_CODES.ZIP_CODE_ID)
JOIN SUPPORT$COUNTRIES ON (SUPPORT$ZIP_CODES.COUNTRY_ID = SUPPORT$COUNTRIES.COUNTRY_ID)
;
/* View: FILES$FILE_ACTIONS_ALL_INFO */
CREATE VIEW FILES$FILE_ACTIONS_ALL_INFO (
FILE_ACTION_ID,
FILE_ID,
ACTION_ID,
REMARKS,
ACTION_DATE,
ACTION_USER,
ACTION_CODE,
ACTION_DESCRIPTION)
AS
SELECT FILES$FILE_ACTIONS.FILE_ACTION_ID,
FILES$FILE_ACTIONS.FILE_ID,
FILES$FILE_ACTIONS.ACTION_ID,
FILES$FILE_ACTIONS.REMARKS,
FILES$FILE_ACTIONS.ACTION_DATE,
FILES$FILE_ACTIONS.ACTION_USER,
FILES$ACTIONS.CODE,
FILES$ACTIONS.DESCRIPTION
FROM FILES$ACTIONS
INNER JOIN FILES$FILE_ACTIONS ON (FILES$ACTIONS.ACTION_ID = FILES$FILE_ACTIONS.ACTION_ID)
;
/* View: FILES$FILES_ALL_INFO */
CREATE VIEW FILES$FILES_ALL_INFO (
FILE_ID,
CLIENT_ID,
FILE_NR,
DEPTOR_ID,
REFERENCE,
COLLECTOR_ID,
STATE_ID,
CREATION_DATE,
CREATION_USER,
DATE_CLOSED,
AMOUNT,
COSTS,
INTEREST,
PAYED_AMOUNT,
PAYED_INTEREST,
PAYED_COSTS,
PAYED_UNKNOWN,
PAYED_TOTAL,
PAYABLE,
TOTAL,
SALDO_AMOUNT,
SALDO_INTEREST,
SALDO_COSTS,
CREATION_MONTH,
CREATION_YEAR,
DEPTOR_NAME,
DEPTOR_ADDRESS,
DEPTOR_ZIP_CODE,
DEPTOR_CITY,
DEPTOR_COUNTRY_CODE,
DEPTOR_LANGUAGE_CODE,
DEPTOR_TELEPHONE,
DEPTOR_TELEFAX,
DEPTOR_E_MAIL,
COLLECTOR_CODE,
COLLECTOR_NAME,
CLIENT_CODE,
CLIENT_NAME)
AS
SELECT FILES$FILES.FILE_ID,
FILES$FILES.CLIENT_ID,
FILES$FILES.FILE_NR,
FILES$FILES.DEPTOR_ID,
FILES$FILES.REFERENCE,
FILES$FILES.COLLECTOR_ID,
FILES$FILES.STATE_ID,
FILES$FILES.CREATION_DATE,
FILES$FILES.CREATION_USER,
FILES$FILES.DATE_CLOSED,
FILES$FILES.AMOUNT,
FILES$FILES.COSTS,
FILES$FILES.INTEREST,
FILES$FILES.PAYED_AMOUNT,
FILES$FILES.PAYED_INTEREST,
FILES$FILES.PAYED_COSTS,
FILES$FILES.PAYED_UNKNOWN,
FILES$FILES.PAYED_TOTAL,
FILES$FILES.PAYABLE,
FILES$FILES.TOTAL,
FILES$FILES.SALDO_AMOUNT,
FILES$FILES.SALDO_INTEREST,
FILES$FILES.COSTS,
FILES$FILES.CREATION_MONTH,
FILES$FILES.CREATION_YEAR,
FILES$DEPTORS.NAME,
FILES$DEPTORS.ADDRESS,
SUPPORT$ZIP_CODES.CODE,
SUPPORT$ZIP_CODES.CITY,
SUPPORT$COUNTRIES.CODE,
SUPPORT$LANGUAGES.CODE,
FILES$DEPTORS.TELEPHONE,
FILES$DEPTORS.TELEFAX,
FILES$DEPTORS.E_MAIL,
SYSTEM$COLLECTORS.CODE,
SYSTEM$COLLECTORS.NAME,
CLIENTS$CLIENTS.CODE,
CLIENTS$CLIENTS.NAME
FROM FILES$FILES
JOIN FILES$DEPTORS ON (FILES$FILES.DEPTOR_ID = FILES$DEPTORS.DEPTOR_ID)
JOIN SUPPORT$ZIP_CODES ON (FILES$DEPTORS.ZIP_CODE_ID = SUPPORT$ZIP_CODES.ZIP_CODE_ID)
JOIN SUPPORT$COUNTRIES ON (SUPPORT$ZIP_CODES.COUNTRY_ID = SUPPORT$COUNTRIES.COUNTRY_ID)
JOIN SUPPORT$LANGUAGES ON (FILES$DEPTORS.LANGUAGE_ID = SUPPORT$LANGUAGES.LANGUAGE_ID)
JOIN SYSTEM$COLLECTORS ON (SYSTEM$COLLECTORS.COLLECTOR_ID = FILES$FILES.COLLECTOR_ID)
JOIN CLIENTS$CLIENTS ON (FILES$FILES.CLIENT_ID = CLIENTS$CLIENTS.CLIENT_ID)
;
/* View: FILES$PAYMENTS_ALL_INFO */
CREATE VIEW FILES$PAYMENTS_ALL_INFO (
PAYEMENT_ID,
FILE_ID,
REFERENCE_ID,
JOURNAL_ID,
PAYMENT_DATE,
AMOUNT,
CREATION_DATE,
CREATION_USER,
INVOICE_ID,
INVOICED,
PAYMENT_FOR,
REFERENCE,
ACCOUNT_CODE,
ACCOUNT_DESCRIPTION,
TRANSACTION_DESCRIPTION,
CODA_FILE_NAME)
AS
SELECT FILES$PAYMENTS.PAYEMENT_ID,
FILES$PAYMENTS.FILE_ID,
FILES$PAYMENTS.REFERENCE_ID,
FILES$PAYMENTS.JOURNAL_ID,
FILES$PAYMENTS.PAYMENT_DATE,
FILES$PAYMENTS.AMOUNT,
FILES$PAYMENTS.CREATION_DATE,
FILES$PAYMENTS.CREATION_USER,
FILES$PAYMENTS.INVOICE_ID,
FILES$PAYMENTS.INVOICED,
FILES$PAYMENTS.PAYMENT_FOR,
FILES$REFERENCES.REFERENCE,
ACCOUNTS$ACCOUNTS.CODE,
ACCOUNTS$ACCOUNTS.DESCRIPTION,
ACCOUNTS$TRANSACTIONS.DESCRIPTION,
ACCOUNTS$CODA.FILE_NAME
FROM FILES$PAYMENTS
LEFT JOIN ACCOUNTS$JOURNAL ON (ACCOUNTS$JOURNAL.JOURNAL_ID = FILES$PAYMENTS.JOURNAL_ID)
LEFT JOIN ACCOUNTS$ACCOUNTS ON (ACCOUNTS$JOURNAL.ACCOUNT_ID = ACCOUNTS$ACCOUNTS.ACCOUNT_ID)
LEFT JOIN ACCOUNTS$TRANSACTIONS ON (ACCOUNTS$TRANSACTIONS.TRANSACTION_ID = ACCOUNTS$JOURNAL.TRANSACTION_ID)
LEFT JOIN FILES$REFERENCES ON (FILES$PAYMENTS.REFERENCE_ID = FILES$REFERENCES.REFERENCE_ID)
LEFT JOIN ACCOUNTS$CODA ON (ACCOUNTS$CODA.CODA_ID = ACCOUNTS$TRANSACTIONS.CODA_ID)
;
/* View: INVOICES$INVOICEABLE_AMOUNTS */
CREATE VIEW INVOICES$INVOICEABLE_AMOUNTS (
PAYEMENT_ID,
CLIENT_ID,
CODE,
NAME,
ADDRESS,
ZIP_CODE,
CITY,
FILE_ID,
FILE_NR,
FILE_REFERENCE,
DEPTOR_NAME,
DEPTOR_ADDRESS,
DEPTOR_ZIP_CODE,
DEPTOR_CITY,
PAYMENT_DATE,
AMOUNT,
COMMISSION,
PAYMENT_FOR,
REFERENCE)
AS
SELECT FILES$PAYMENTS.PAYEMENT_ID,
CLIENTS$CLIENTS_ALL_INFO.CLIENT_ID,
CLIENTS$CLIENTS_ALL_INFO.CODE,
CLIENTS$CLIENTS_ALL_INFO.NAME,
CLIENTS$CLIENTS_ALL_INFO.ADDRESS,
CLIENTS$CLIENTS_ALL_INFO.ZIP_CODE,
CLIENTS$CLIENTS_ALL_INFO.CITY,
FILES$FILES_ALL_INFO.FILE_ID,
FILES$FILES_ALL_INFO.FILE_NR,
FILES$FILES_ALL_INFO.REFERENCE,
FILES$FILES_ALL_INFO.DEPTOR_NAME,
FILES$FILES_ALL_INFO.DEPTOR_ADDRESS,
FILES$FILES_ALL_INFO.DEPTOR_ZIP_CODE,
FILES$FILES_ALL_INFO.DEPTOR_CITY,
FILES$PAYMENTS.PAYMENT_DATE,
FILES$PAYMENTS.AMOUNT,
FILES$PAYMENTS.COMMISSION,
FILES$PAYMENTS.PAYMENT_FOR,
FILES$REFERENCES.REFERENCE
FROM FILES$PAYMENTS
INNER JOIN FILES$FILES_ALL_INFO ON (FILES$PAYMENTS.FILE_ID = FILES$FILES_ALL_INFO.FILE_ID)
INNER JOIN CLIENTS$CLIENTS_ALL_INFO ON (FILES$FILES_ALL_INFO.CLIENT_ID = CLIENTS$CLIENTS_ALL_INFO.CLIENT_ID)
LEFT JOIN FILES$REFERENCES ON (FILES$PAYMENTS.REFERENCE_ID = FILES$REFERENCES.REFERENCE_ID)
WHERE
(
(FILES$PAYMENTS.INVOICED = 0)
and
(FILES$PAYMENTS.INVOICEABLE = 1)
)
;
/* View: SUPPORT$ZIP_CODES_ALL_INFO */
CREATE VIEW SUPPORT$ZIP_CODES_ALL_INFO (
ZIP_CODE_ID,
COUNTRY_ID,
CODE,
CITY,
COUNTRY_CODE,
COUNTRY_DESCRIPTION)
AS
SELECT SUPPORT$ZIP_CODES.ZIP_CODE_ID,
SUPPORT$ZIP_CODES.COUNTRY_ID,
SUPPORT$ZIP_CODES.CODE,
SUPPORT$ZIP_CODES.CITY,
SUPPORT$COUNTRIES.CODE,
SUPPORT$COUNTRIES.DESCRIPTION
FROM SUPPORT$ZIP_CODES
INNER JOIN SUPPORT$COUNTRIES ON (SUPPORT$ZIP_CODES.COUNTRY_ID = SUPPORT$COUNTRIES.COUNTRY_ID)
;
/* Unique keys definition */
ALTER TABLE ACCOUNTS$ACCOUNTS ADD CONSTRAINT PK_ACCOUNTS$ACCOUNTS PRIMARY KEY (ACCOUNT_ID);
ALTER TABLE ACCOUNTS$CODA ADD CONSTRAINT PK_ACCOUNTS$CODA PRIMARY KEY (CODA_ID);
ALTER TABLE ACCOUNTS$JOURNAL ADD CONSTRAINT PK_ACCOUNTS$JOURNAL PRIMARY KEY (JOURNAL_ID);
ALTER TABLE ACCOUNTS$TRANSACTIONS ADD CONSTRAINT PK_ACCOUNTS$TRANSACTIONS PRIMARY KEY (TRANSACTION_ID);
ALTER TABLE CLIENTS$CLIENTS ADD CONSTRAINT PK_CLIENTS$CLIENTS PRIMARY KEY (CLIENT_ID);
ALTER TABLE CLIENTS$CONDITIONS ADD CONSTRAINT PK_CLIENTS$CONDITIONS PRIMARY KEY (RECORD_ID);
ALTER TABLE CODA$BOOKING ADD CONSTRAINT PK_CODA$BOOKING PRIMARY KEY (RECORD_ID);
ALTER TABLE CODA$FILES ADD CONSTRAINT PK_CODA$FILES PRIMARY KEY (FILE_ID);
ALTER TABLE CODA$FILE_SECTIONS ADD CONSTRAINT PK_CODA$FILE_SECTIONS PRIMARY KEY (SECTION_ID);
ALTER TABLE CODA$TRANSACTIONS ADD CONSTRAINT PK_CODA$TRANSACTIONS PRIMARY KEY (TRANSACTION_ID);
ALTER TABLE CODA$TRANSACTIONS_OPERATIONS ADD CONSTRAINT PK_CODA$TRANSACTIONS_OPERATIONS PRIMARY KEY (OPERATION_ID);
ALTER TABLE CODA$TRANSACTION_GROUP ADD CONSTRAINT PK_CODA$TRANSACTION_GROUP PRIMARY KEY (GROUP_ID);
ALTER TABLE CODA$TRANSACTION_TYPES ADD CONSTRAINT PK_CODA$TRANSACTION_TYPES PRIMARY KEY (TYPE_ID);
ALTER TABLE FILES$ACTIONS ADD CONSTRAINT PK_FILES$ACTIONS PRIMARY KEY (ACTION_ID);
ALTER TABLE FILES$COSTS ADD CONSTRAINT PK_FILES$COSTS PRIMARY KEY (COST_ID);
ALTER TABLE FILES$DEPTORS ADD CONSTRAINT PK_FILES$DEPTORS PRIMARY KEY (DEPTOR_ID);
ALTER TABLE FILES$FILES ADD CONSTRAINT PK_FILES$FILES PRIMARY KEY (FILE_ID);
ALTER TABLE FILES$FILE_ACTIONS ADD CONSTRAINT PK_FILES$FILE_ACTIONS PRIMARY KEY (FILE_ACTION_ID);
ALTER TABLE FILES$FILE_COSTS ADD CONSTRAINT PK_FILES$FILE_COSTS PRIMARY KEY (RECORD_ID);
ALTER TABLE FILES$PAYMENTS ADD CONSTRAINT PK_FILES$PAYMENTS PRIMARY KEY (PAYEMENT_ID);
ALTER TABLE FILES$REFERENCES ADD CONSTRAINT PK_FILES$REFERENCES PRIMARY KEY (REFERENCE_ID);
ALTER TABLE FILES$REMARKS ADD CONSTRAINT PK_FILES$REMARKS PRIMARY KEY (REMARK_ID);
ALTER TABLE INVOICES$INVOICES ADD CONSTRAINT PK_INVOICES$INVOICES PRIMARY KEY (INVOICE_ID);
ALTER TABLE INVOICES$INVOICE_DETAILS ADD CONSTRAINT PK_INVOICES$INVOICE_DETAILS PRIMARY KEY (INVOICE_DETAIL_ID);
ALTER TABLE SUPPORT$COUNTRIES ADD CONSTRAINT PK_SUPPORT$COUNTRIES PRIMARY KEY (COUNTRY_ID);
ALTER TABLE SUPPORT$LANGUAGES ADD CONSTRAINT PK_SUPPORT$LANGUAGES PRIMARY KEY (LANGUAGE_ID);
ALTER TABLE SUPPORT$TITLES ADD CONSTRAINT PK_SUPPORT$TITLES PRIMARY KEY (TITLE_ID);
ALTER TABLE SUPPORT$ZIP_CODES ADD CONSTRAINT PK_SUPPORT$ZIP_CODES PRIMARY KEY (ZIP_CODE_ID);
ALTER TABLE SYSTEM$COLLECTORS ADD CONSTRAINT PK_SYSTEM$COLLECTORS PRIMARY KEY (COLLECTOR_ID);
ALTER TABLE SYSTEM$EMPLOYEES ADD CONSTRAINT PK_SYSTEM$EMPLOYEES PRIMARY KEY (EMPLOYEE_ID);
/* Foreign keys definition */
ALTER TABLE CLIENTS$CONDITIONS ADD CONSTRAINT FK_CLIENTS$CONDITIONS_CLIENT_ID FOREIGN KEY (CLIENT_ID) REFERENCES CLIENTS$CLIENTS (CLIENT_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE CODA$BOOKING ADD CONSTRAINT FK_CODA$BOOKING_FILE_ID FOREIGN KEY (FILE_ID) REFERENCES FILES$FILES (FILE_ID) ON UPDATE CASCADE;
ALTER TABLE CODA$BOOKING ADD CONSTRAINT FK_CODA$BOOKING_TRANSACTION_ID FOREIGN KEY (TRANSACTION_ID) REFERENCES CODA$TRANSACTIONS (TRANSACTION_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE CODA$FILE_SECTIONS ADD CONSTRAINT FK_CODA$FILE_SECTIONS_FILE_ID FOREIGN KEY (FILE_ID) REFERENCES CODA$FILES (FILE_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE CODA$TRANSACTIONS ADD CONSTRAINT FK_CODA$TRANSACTIONS_SECTION_ID FOREIGN KEY (SECTION_ID) REFERENCES CODA$FILE_SECTIONS (SECTION_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE FILES$FILES ADD CONSTRAINT FK_FILES$FILES_CLIENT_ID FOREIGN KEY (CLIENT_ID) REFERENCES CLIENTS$CLIENTS (CLIENT_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE FILES$FILES ADD CONSTRAINT FK_FILES$FILES_COLLECTOR_ID FOREIGN KEY (COLLECTOR_ID) REFERENCES SYSTEM$COLLECTORS (COLLECTOR_ID) ON UPDATE CASCADE;
ALTER TABLE FILES$FILES ADD CONSTRAINT FK_FILES$FILES_DEPTOR_ID FOREIGN KEY (DEPTOR_ID) REFERENCES FILES$DEPTORS (DEPTOR_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE FILES$FILE_ACTIONS ADD CONSTRAINT FK_FILES$FILE_ACTIONS_ACTION_ID FOREIGN KEY (ACTION_ID) REFERENCES FILES$ACTIONS (ACTION_ID) ON UPDATE CASCADE;
ALTER TABLE FILES$FILE_ACTIONS ADD CONSTRAINT FK_FILES$FILE_ACTIONS_FILE_ID FOREIGN KEY (FILE_ID) REFERENCES FILES$FILES (FILE_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE FILES$FILE_COSTS ADD CONSTRAINT FK_FILES$FILE_COSTS_COST_ID FOREIGN KEY (COST_ID) REFERENCES FILES$COSTS (COST_ID) ON UPDATE CASCADE;
ALTER TABLE FILES$FILE_COSTS ADD CONSTRAINT FK_FILES$FILE_COSTS_FILE_ID FOREIGN KEY (FILE_ID) REFERENCES FILES$FILES (FILE_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE FILES$PAYMENTS ADD CONSTRAINT FK_FILES$PAYMENTS_FILE_ID FOREIGN KEY (FILE_ID) REFERENCES FILES$FILES (FILE_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE FILES$PAYMENTS ADD CONSTRAINT FK_FILES$PAYMENTS_REFERENCE_ID FOREIGN KEY (REFERENCE_ID) REFERENCES FILES$REFERENCES (REFERENCE_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE FILES$REFERENCES ADD CONSTRAINT FK_FILES$REFERENCES_FILE_ID FOREIGN KEY (FILE_ID) REFERENCES FILES$FILES (FILE_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE FILES$REMARKS ADD CONSTRAINT FK_FILES$REMARKS_FILE_ID FOREIGN KEY (FILE_ID) REFERENCES FILES$FILES (FILE_ID) ON DELETE CASCADE ON UPDATE CASCADE;
/* Indices definition */
CREATE INDEX IDX_FILES$DEPTORS ON FILES$DEPTORS (OLD_ID);
ALTER INDEX IDX_FILES$DEPTORS INACTIVE;
CREATE INDEX IDX_FILES$FILES_FILE_NR ON FILES$FILES (FILE_NR);
ALTER INDEX IDX_FILES$FILES_FILE_NR INACTIVE;
CREATE INDEX IDX_FILES$FILES_REFUND_STATEMEN ON FILES$FILES (REFUND_STATEMENT);
ALTER INDEX IDX_FILES$FILES_REFUND_STATEMEN INACTIVE;
CREATE UNIQUE INDEX IDX_FILES$FILES_STATEMENT ON FILES$FILES (STRUCTURED_STATEMENT);
ALTER INDEX IDX_FILES$FILES_STATEMENT INACTIVE;
CREATE INDEX IDX_SUPPORT$ZIP_CODES ON SUPPORT$ZIP_CODES (CODE, CITY);
ALTER INDEX IDX_SUPPORT$ZIP_CODES INACTIVE;
CREATE UNIQUE INDEX UN_IDX_CLIENTS$CLIENTS_CODE ON CLIENTS$CLIENTS (CODE);
ALTER INDEX UN_IDX_CLIENTS$CLIENTS_CODE INACTIVE;
SET TERM ^ ;
/* Stored procedures definition */
/* Stored Procedure: FILES$CREATE_PAYEMENT_AMOUNT */
CREATE PROCEDURE FILES$CREATE_PAYEMENT_AMOUNT (
PARJOURNAL_ID INTEGER,
PARFILE_ID INTEGER,
PARAMOUNT NUMERIC (18, 3),
PARDATE DATE)
RETURNS (
SALDOAMOUNT NUMERIC (18, 3))
AS
DECLARE VARIABLE varReferenceID INTEGER;
DECLARE VARIABLE varSaldoAmount NUMERIC(18,3);
BEGIN
SaldoAmount = parAmount;
FOR SELECT FILES$REFERENCES.REFERENCE_ID,
FILES$REFERENCES.SALDO_AMOUNT
FROM FILES$REFERENCES
WHERE (FILES$REFERENCES.FILE_ID = :parFile_ID) AND
(FILES$REFERENCES.SALDO_AMOUNT > 0)
INTO :varReferenceID, :varSaldoAmount DO
BEGIN
IF (varSaldoAmount > PARAMOUNT) THEN
BEGIN
INSERT INTO FILES$PAYMENTS (JOURNAL_ID, REFERENCE_ID, AMOUNT, PAYMENT_FOR, PAYMENT_DATE)
VALUES (:PARJOURNAL_ID, :varReferenceID, :PARAMOUNT, 'A', :parDate);
SaldoAmount = 0;
END
ELSE
BEGIN
SaldoAmount = PARAMOUNT - varSaldoAmount;
parAmount = parAmount - varSaldoAmount;
INSERT INTO FILES$PAYMENTS (JOURNAL_ID, REFERENCE_ID, AMOUNT, PAYMENT_FOR, PAYMENT_DATE)
VALUES (:PARJOURNAL_ID, :varReferenceID, :varSaldoAmount, 'A', :parDate);
END
IF (SaldoAmount = 0) THEN EXIT;
END
END
^
/* Stored Procedure: FILES$CREATE_PAYEMENT_COSTS */
CREATE PROCEDURE FILES$CREATE_PAYEMENT_COSTS (
PARJOURNAL_ID INTEGER,
PARFILE_ID INTEGER,
PARAMOUNT NUMERIC (18, 3),
PARDATE DATE)
RETURNS (
SALDOAMOUNT NUMERIC (18, 3))
AS
DECLARE VARIABLE varReferenceID INTEGER;
DECLARE VARIABLE varSaldoAmount NUMERIC(18,3);
BEGIN
SaldoAmount = parAmount;
FOR SELECT FILES$REFERENCES.REFERENCE_ID,
FILES$REFERENCES.SALDO_COSTS
FROM FILES$REFERENCES
WHERE (FILES$REFERENCES.FILE_ID = :parFile_ID) AND
(FILES$REFERENCES.SALDO_COSTS > 0)
INTO :varReferenceID, :varSaldoAmount DO
BEGIN
IF (varSaldoAmount > PARAMOUNT) THEN
BEGIN
INSERT INTO FILES$PAYMENTS (JOURNAL_ID, REFERENCE_ID, AMOUNT, PAYMENT_FOR, PAYMENT_DATE)
VALUES (:PARJOURNAL_ID, :varReferenceID, :PARAMOUNT, 'C', :parDate);
SaldoAmount = 0;
END
ELSE
BEGIN
SaldoAmount = PARAMOUNT - varSaldoAmount;
parAmount = parAmount - varSaldoAmount;
INSERT INTO FILES$PAYMENTS (JOURNAL_ID, REFERENCE_ID, AMOUNT, PAYMENT_FOR, PAYMENT_DATE)
VALUES (:PARJOURNAL_ID, :varReferenceID, :varSaldoAmount, 'C', :parDate);
END
IF (SaldoAmount = 0) THEN EXIT;
END
END
^
/* Stored Procedure: FILES$CREATE_PAYEMENT_INTEREST */
CREATE PROCEDURE FILES$CREATE_PAYEMENT_INTEREST (
PARJOURNAL_ID INTEGER,
PARFILE_ID INTEGER,
PARAMOUNT NUMERIC (18, 3),
PARDATE DATE)
RETURNS (
SALDOAMOUNT NUMERIC (18, 3))
AS
DECLARE VARIABLE varReferenceID INTEGER;
DECLARE VARIABLE varSaldoAmount NUMERIC(18,3);
BEGIN
SaldoAmount = parAmount;
FOR SELECT FILES$REFERENCES.REFERENCE_ID,
FILES$REFERENCES.SALDO_INTEREST
FROM FILES$REFERENCES
WHERE (FILES$REFERENCES.FILE_ID = :parFile_ID) AND
(FILES$REFERENCES.SALDO_INTEREST > 0)
INTO :varReferenceID, :varSaldoAmount DO
BEGIN
IF (varSaldoAmount > PARAMOUNT) THEN
BEGIN
INSERT INTO FILES$PAYMENTS (JOURNAL_ID, REFERENCE_ID, AMOUNT, PAYMENT_FOR, PAYMENT_DATE)
VALUES (:PARJOURNAL_ID, :varReferenceID, :PARAMOUNT, 'I', :parDate);
SaldoAmount = 0;
END
ELSE
BEGIN
SaldoAmount = PARAMOUNT - varSaldoAmount;
parAmount = parAmount - varSaldoAmount;
INSERT INTO FILES$PAYMENTS (JOURNAL_ID, REFERENCE_ID, AMOUNT, PAYMENT_FOR, PAYMENT_DATE)
VALUES (:PARJOURNAL_ID, :varReferenceID, :varSaldoAmount, 'I', :parDate);
END
IF (SaldoAmount = 0) THEN EXIT;
END
END
^
/* Stored Procedure: FILES$CREATE_PAYEMENT_UNKNOWN */
CREATE PROCEDURE FILES$CREATE_PAYEMENT_UNKNOWN (
PARJOURNAL_ID INTEGER,
PARFILE_ID INTEGER,
PARAMOUNT NUMERIC (18, 3),
PARDATE DATE)
RETURNS (
SALDOAMOUNT NUMERIC (18, 3))
AS
DECLARE VARIABLE varReferenceID INTEGER;
DECLARE VARIABLE varSaldoAmount NUMERIC(18,3);
BEGIN
INSERT INTO FILES$PAYMENTS (JOURNAL_ID, FILE_ID, AMOUNT, PAYMENT_FOR, PAYMENT_DATE)
VALUES (:PARJOURNAL_ID, :parFile_ID, :parAmount, '?', :parDate);
END
^
/* Stored Procedure: ACCOUNTS$SET_PAYEMENT_TYPE */
CREATE PROCEDURE ACCOUNTS$SET_PAYEMENT_TYPE (
PARJOURNALID INTEGER,
PARFILEID INTEGER,
PARAMOUNT NUMERIC (18, 3),
PARDATE DATE)
AS
DECLARE VARIABLE varSaldoAmount NUMERIC(18,3);
BEGIN
varSaldoAmount = parAmount;
IF (varSaldoAmount > 0) THEN
BEGIN
EXECUTE PROCEDURE FILES$CREATE_PAYEMENT_AMOUNT (:parJournalID, :parFileID,:varSaldoAmount, :parDate) RETURNING_VALUES :varSaldoAmount;
END
IF (varSaldoAmount > 0) THEN
BEGIN
EXECUTE PROCEDURE FILES$CREATE_PAYEMENT_INTEREST (:parJournalID, :parFileID,:varSaldoAmount, :parDate) RETURNING_VALUES :varSaldoAmount;
END
IF (varSaldoAmount > 0) THEN
BEGIN
EXECUTE PROCEDURE FILES$CREATE_PAYEMENT_COSTS (:parJournalID, :parFileID,:varSaldoAmount, :parDate) RETURNING_VALUES :varSaldoAmount;
END
IF (varSaldoAmount <> 0) THEN
BEGIN
EXECUTE PROCEDURE FILES$CREATE_PAYEMENT_UNKNOWN (:parJournalID, :parFileID,:varSaldoAmount, :parDate) RETURNING_VALUES :varSaldoAmount;
END
END
^
/* Stored Procedure: CONVERT$CREATE_INFO */
CREATE PROCEDURE CONVERT$CREATE_INFO
AS
BEGIN
/* Procedure body */
INSERT INTO SUPPORT$LANGUAGES (CODE, DESCRIPTION, SYSTEM_DEFAULT) VALUES ('DUTCH', 'Dutch', 1);
INSERT INTO SUPPORT$LANGUAGES (CODE, DESCRIPTION) VALUES ('FRENCH', 'French');
INSERT INTO SUPPORT$LANGUAGES (CODE, DESCRIPTION) VALUES ('ENGLISH', 'English');
INSERT INTO SUPPORT$COUNTRIES (CODE, DESCRIPTION, TELEPHONE_CODE, SYSTEM_DEFAULT) VALUES ('BE', 'Belgium', '032', 1);
INSERT INTO SUPPORT$ZIP_CODES (CODE, CITY) VALUES ('UNKNOWN', 'Not Specified');
INSERT INTO SYSTEM$COLLECTORS (CODE, NAME, SYSTEM_DEFAULT) VALUES ('V&B','Vrydagh & Boriau', 1);
INSERT INTO ACCOUNTS$TRANSACTIONS (TRANSACTION_ID, DESCRIPTION) VALUES (1, 'CONVERTION');
INSERT INTO FILES$COSTS (COST_ID, CODE, DESCRIPTION) VALUES (0, 'UNKNOWN', 'Unkown by conversion');
INSERT INTO FILES$COSTS (CODE, DESCRIPTION) VALUES ('POP', 'Population');
INSERT INTO FILES$COSTS (CODE, DESCRIPTION) VALUES ('PAYEMENT', 'Payement Cost');
END
^
/* Stored Procedure: FILES$GET_PAYEMENT_COMMISSION */
CREATE PROCEDURE FILES$GET_PAYEMENT_COMMISSION (
PARFILEID INTEGER,
PARAMOUNT NUMERIC (18, 2))
RETURNS (
RESULT NUMERIC (18, 2))
AS
DECLARE VARIABLE varMin NUMERIC(18,2);
DECLARE VARIABLE varPercent NUMERIC(5,2);
DECLARE VARIABLE varClientID INTEGER;
BEGIN
RESULT = 0;
SELECT FILES$FILES.CLIENT_ID
FROM FILES$FILES
WHERE FILES$FILES.FILE_ID = :parFileID
INTO :varClientID;
SELECT CLIENTS$CONDITIONS.INVOICE_PERCENT, CLIENTS$CONDITIONS.INVOICE_MINIMUM
FROM CLIENTS$CONDITIONS
WHERE (CLIENTS$CONDITIONS.CLIENT_ID = :varClientID) AND (CLIENTS$CONDITIONS.END_VALUE = (SELECT MAX(CC2.END_VALUE)
FROM CLIENTS$CONDITIONS CC2
WHERE CC2.CLIENT_ID = :varClientID))
INTO :varPercent, :varMin;
RESULT = (parAmount * :varPercent) / 100;
IF (RESULT < :varMin) THEN RESULT = :varMin;
IF (RESULT IS NULL) THEN RESULT = 0;
END
^
/* Stored Procedure: SUPPORT$AT */
CREATE PROCEDURE SUPPORT$AT (
PARSOURCE VARCHAR (255),
PARSEARCH VARCHAR (255))
RETURNS (
RESULT INTEGER)
AS
DECLARE VARIABLE varCheck VARCHAR(255);
DECLARE VARIABLE varTemp VARCHAR(255);
BEGIN
/* Procedure body */
RESULT = 0;
IF (parSource IS NULL) THEN EXIT;
IF (parSearch IS NULL) THEN EXIT;
RESULT = 1;
varCheck = UDF_SUBSTR(parSource, RESULT, 1);
WHILE (NOT varCheck = parSearch) DO
BEGIN
RESULT = RESULT +1;
varCheck = UDF_SUBSTR(parSource, RESULT, RESULT);
END
END
^
/* Stored Procedure: SUPPORT$ENCODEDATE */
CREATE PROCEDURE SUPPORT$ENCODEDATE (
AYEAR INTEGER,
AMONTH INTEGER,
ADAY INTEGER)
RETURNS (
RESULT DATE)
AS
BEGIN
RESULT = cast( ADay || '.' || AMonth || '.' || AYear as DATE);
SUSPEND;
END
^
/* Stored Procedure: SUPPORT$DAYSOFMONTH */
CREATE PROCEDURE SUPPORT$DAYSOFMONTH (
AYEAR INTEGER,
AMONTH INTEGER)
RETURNS (
RESULT INTEGER)
AS
DECLARE VARIABLE WorkDate DATE;
BEGIN
Result = 31;
WHILE (Result > 28 AND WorkDate IS NULL) DO
BEGIN
EXECUTE PROCEDURE SUPPORT$ENCODEDATE(AYear, AMonth, Result)
RETURNING_VALUES(WorkDate);
WHEN ANY DO Result = Result -1;
END
END
^
/* Stored Procedure: SUPPORT$DECODEDATE */
CREATE PROCEDURE SUPPORT$DECODEDATE (
ADATE TIMESTAMP)
RETURNS (
AYEAR INTEGER,
AMONTH INTEGER,
ADAY INTEGER)
AS
BEGIN
AYear = Extract (Year from ADate);
AMonth = Extract (Month from ADate);
ADay = Extract (Day from ADate);
SUSPEND;
END
^
/* Stored Procedure: SUPPORT$MOD */
CREATE PROCEDURE SUPPORT$MOD (
DIVIDEND NUMERIC (18, 0),
DIVISOR INTEGER)
RETURNS (
RESULT INTEGER)
AS
BEGIN
/* This procedure calculates the modulus of two numbers */
IF(Dividend = 0) THEN Result = 0;
ELSE
RESULT = Dividend-(CAST((Dividend / Divisor)-0.49 AS INTEGER)*Divisor);
suspend;
END
^
/* Stored Procedure: SUPPORT$INCDATE */
CREATE PROCEDURE SUPPORT$INCDATE (
IN_DATE TIMESTAMP,
IN_SECONDS INTEGER,
IN_MINUTES INTEGER,
IN_HOURS INTEGER,
IN_DAYS INTEGER,
IN_MONTHS INTEGER,
IN_YEARS INTEGER)
RETURNS (
RESULT TIMESTAMP)
AS
DECLARE VARIABLE var_SecondsOfTime INTEGER;
DECLARE VARIABLE var_Time TIME;
DECLARE VARIABLE var_Day INTEGER;
DECLARE VARIABLE var_Month INTEGER;
DECLARE VARIABLE var_Year INTEGER;
DECLARE VARIABLE var_ModHour INTEGER;
DECLARE VARIABLE var_ModMonth INTEGER;
DECLARE VARIABLE var_DaysPerMonth INTEGER;
BEGIN
IF (IN_SECONDS IS NULL) THEN IN_SECONDS = 0;
IF (IN_MINUTES IS NULL) THEN IN_MINUTES = 0;
IF (IN_HOURS IS NULL) THEN IN_HOURS = 0;
IF (IN_DAYS IS NULL) THEN IN_DAYS = 0;
IF (IN_MONTHS IS NULL) THEN IN_MONTHS = 0;
IF (IN_YEARS IS NULL) THEN IN_YEARS = 0;
/* function IncTime(ADateTime: TDateTime; Hours, Minutes, Seconds, MSecs: Integer): TDateTime; */
/* Result := ADateTime + (Hours div 24) + (((Hours mod 24) * 3600000 + Minutes * 60000 + Seconds * 1000 + MSecs) / MSecsPerDay); */
var_Time = CAST(in_Date AS TIME);
var_SecondsOfTime = EXTRACT(HOUR FROM var_Time)*60*60 +
EXTRACT(MINUTE FROM var_Time)*60 +
EXTRACT(SECOND FROM var_Time) +
in_Hours*60*60 + in_Minutes*60 + in_Seconds;
in_Days = in_Days + CAST( var_SecondsOfTime/(24*60*60) - 0.49 AS INTEGER );
EXECUTE PROCEDURE SUPPORT$MOD( var_SecondsOfTime, 86400 )
RETURNING_VALUES ( var_SecondsOfTime );
var_Time = CAST( '00:00' AS TIME );
var_Time = var_Time + var_SecondsOfTime;
EXECUTE PROCEDURE SUPPORT$DECODEDATE( in_Date ) RETURNING_VALUES ( var_Year ,var_Month, var_Day );
var_Year = var_Year + in_Years;
var_Year = var_Year + CAST(in_Months/12-0.49 AS INTEGER);
EXECUTE PROCEDURE SUPPORT$MOD( in_Months, 12 ) RETURNING_VALUES ( var_ModMonth );
var_Month = var_Month + var_ModMonth;
IF (var_Month < 1) THEN
BEGIN
var_Month = var_Month + 12;
var_Year = var_Year - 1;
END
ELSE
IF (var_Month > 12) THEN
BEGIN
var_Month = var_Month - 12;
var_Year = var_Year + 1;
END
EXECUTE PROCEDURE SUPPORT$DAYSOFMONTH ( var_Year, var_Month )
RETURNING_VALUES ( var_DaysPerMonth );
IF (var_Day > var_DaysPerMonth) THEN
BEGIN
EXECUTE PROCEDURE SUPPORT$DAYSOFMONTH ( var_Year, var_Month )
RETURNING_VALUES ( var_Day );
END
EXECUTE PROCEDURE SUPPORT$ENCODEDATE ( var_Year, var_Month, var_Day )
RETURNING_VALUES ( RESULT );
RESULT = RESULT + in_Days;
RESULT = CAST(RESULT AS DATE) + var_Time;
END
^
/* Stored Procedure: SUPPORT$NUMPART */
CREATE PROCEDURE SUPPORT$NUMPART (
PARSTRING VARCHAR (255))
RETURNS (
RESULT VARCHAR (255))
AS
DECLARE VARIABLE varPos INTEGER;
DECLARE VARIABLE varStartPos INTEGER;
DECLARE VARIABLE varNextChar CHAR(1);
DECLARE VARIABLE varQuit INTEGER;
BEGIN
/* Procedure body */
RESULT = null;
varStartPos = 0;
varPos = 1;
varQuit = 0;
IF (parString IS NULL) THEN EXIT;
WHILE ((varQuit = 0) AND (varPos < 255)) DO
BEGIN
varNextChar = UDF_SUBSTR(parString,:varPos , :varPos);
IF ((varNextChar IN ('0','1','2','3','4','5','6','7','8','9')) AND (varStartPos = 0)) THEN
BEGIN
varStartPos = varPos;
END
IF (NOT (varNextChar IN ('0','1','2','3','4','5','6','7','8','9')) AND (varStartPos <> 0)) THEN
BEGIN
varQuit = 1;
RESULT = UDF_SUBSTR(parString, varStartPos, varPos - 1);
END
varPos = varPos + 1;
END
END
^
/* Stored Procedure: SUPPORT$STR_LEN */
CREATE PROCEDURE SUPPORT$STR_LEN (
PARSTRING VARCHAR (255))
RETURNS (
RESULT INTEGER)
AS
DECLARE VARIABLE varTemp VARCHAR(255);
BEGIN
RESULT = null;
IF (parString IS NULL) THEN EXIT;
varTemp = '';
RESULT = 0;
WHILE (NOT parString LIKE varTemp) DO
BEGIN
varTemp = varTemp || '_';
RESULT = RESULT + 1;
END
END
^
/* Stored Procedure: SUPPORT$STRUCTURED_STATEMENT */
CREATE PROCEDURE SUPPORT$STRUCTURED_STATEMENT (
PARNUMBER INTEGER)
RETURNS (
RESULT CHAR (12))
AS
DECLARE VARIABLE varNumber VARCHAR(10);
DECLARE VARIABLE varMOD INTEGER;
DECLARE VARIABLE varDividend INTEGER;
DECLARE VARIABLE varLength INTEGER;
DECLARE VARIABLE varCheck VARCHAR(2);
BEGIN
/* Procedure body */
varNumber = CAST(parNumber AS VARCHAR(10));
varDividend = CAST(varNumber AS INTEGER);
EXECUTE PROCEDURE SUPPORT$MOD(varDividend, 97) RETURNING_VALUES :varMOD;
EXECUTE PROCEDURE SUPPORT$STR_LEN(varNumber) RETURNING_VALUES :varLength;
WHILE (varLength<10) DO
BEGIN
varNumber = '0' || varNumber;
EXECUTE PROCEDURE SUPPORT$STR_LEN(varNumber) RETURNING_VALUES :varLength;
END
varCheck = CAST(varMod AS VARCHAR(2));
EXECUTE PROCEDURE SUPPORT$STR_LEN(varCheck) RETURNING_VALUES :varLength;
WHILE (varLength<2) DO
BEGIN
varCheck = '0' || varCheck;
EXECUTE PROCEDURE SUPPORT$STR_LEN(varCheck) RETURNING_VALUES :varLength;
END
RESULT = varNumber || varCheck;
SUSPEND;
END
^
SET TERM ; ^
SET TERM ^ ;
/* Triggers definition */
/* Trigger: ACCOUNTS$ACCOUNTS_BI */
CREATE TRIGGER ACCOUNTS$ACCOUNTS_BI FOR ACCOUNTS$ACCOUNTS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.ACCOUNT_ID IS NULL) THEN
NEW.ACCOUNT_ID = GEN_ID(ACCOUNTS$NEXT_ID, 1);
END
^
/* Trigger: ACCOUNTS$C_SET_CODA_ID */
CREATE TRIGGER ACCOUNTS$C_SET_CODA_ID FOR ACCOUNTS$CODA ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.CODA_ID IS NULL) THEN
NEW.CODA_ID = GEN_ID(ACCOUNTS$NEXT_ID, 1);
END
^
/* Trigger: "ACCOUNTS$J-CONVERT" */
CREATE TRIGGER "ACCOUNTS$J-CONVERT" FOR ACCOUNTS$JOURNAL ACTIVE
AFTER INSERT POSITION 0
AS
BEGIN
IF (NEW.FILE_ID IS NOT NULL) THEN
BEGIN
EXECUTE PROCEDURE ACCOUNTS$SET_PAYEMENT_TYPE (NEW.JOURNAL_ID, NEW.FILE_ID, NEW.AMOUNT, NEW.VALUTA_DATE);
END
IF (NEW.INVOICED = 1) THEN
BEGIN
UPDATE FILES$PAYMENTS
SET FILES$PAYMENTS.INVOICED = 1
WHERE FILES$PAYMENTS.JOURNAL_ID = NEW.JOURNAL_ID;
END
END
^
/* Trigger: ACCOUNTS$J_SET_JOURNAL_ID */
CREATE TRIGGER ACCOUNTS$J_SET_JOURNAL_ID FOR ACCOUNTS$JOURNAL ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.JOURNAL_ID IS NULL) THEN
BEGIN
NEW.JOURNAL_ID = GEN_ID(ACCOUNTS$NEXT_ID, 1);
END
END
^
/* Trigger: ACCOUNTS$SET_INVOICABLE_AU */
CREATE TRIGGER ACCOUNTS$SET_INVOICABLE_AU FOR ACCOUNTS$ACCOUNTS ACTIVE
AFTER UPDATE POSITION 0
AS
DECLARE VARIABLE varDBKey CHAR(8);
BEGIN
IF (NEW.INVOICEABLE <> OLD.INVOICEABLE) THEN
BEGIN
FOR SELECT FILES$PAYMENTS.RDB$DB_KEY
FROM ACCOUNTS$JOURNAL
JOIN FILES$PAYMENTS ON (ACCOUNTS$JOURNAL.JOURNAL_ID = FILES$PAYMENTS.JOURNAL_ID)
WHERE (ACCOUNTS$JOURNAL.ACCOUNT_ID = NEW.ACCOUNT_ID) AND
(FILES$PAYMENTS.INVOICED = 0)
INTO :varDBKey DO
BEGIN
UPDATE FILES$PAYMENTS
SET FILES$PAYMENTS.INVOICEABLE = NEW.INVOICEABLE
WHERE FILES$PAYMENTS.RDB$DB_KEY = :varDBKey;
END
END
END
^
/* Trigger: ACCOUNTS$T_SET_TRANSACTION_ID */
CREATE TRIGGER ACCOUNTS$T_SET_TRANSACTION_ID FOR ACCOUNTS$TRANSACTIONS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.TRANSACTION_ID IS NULL) THEN
NEW.TRANSACTION_ID = GEN_ID(ACCOUNTS$NEXT_ID, 1);
END
^
/* Trigger: AI_CLIENTS$CONDITIONS_RECORD_ID */
CREATE TRIGGER AI_CLIENTS$CONDITIONS_RECORD_ID FOR CLIENTS$CONDITIONS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.RECORD_ID IS NULL) THEN
NEW.RECORD_ID = GEN_ID(CLIENTS$NEXT_ID, 1);
END
^
/* Trigger: AI_CLIENTS$C_SET_CLIENT_ID */
CREATE TRIGGER AI_CLIENTS$C_SET_CLIENT_ID FOR CLIENTS$CLIENTS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.CLIENT_ID IS NULL) THEN
NEW.CLIENT_ID = GEN_ID(CLIENTS$NEXT_ID, 1);
END
^
/* Trigger: AI_CODA$BOOKING_RECORD_ID */
CREATE TRIGGER AI_CODA$BOOKING_RECORD_ID FOR CODA$BOOKING ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.RECORD_ID IS NULL) THEN
NEW.RECORD_ID = GEN_ID(CODA$NEXT_ID, 1);
END
^
/* Trigger: AI_CODA$FILES_FILE_ID */
CREATE TRIGGER AI_CODA$FILES_FILE_ID FOR CODA$FILES ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.FILE_ID IS NULL) THEN
NEW.FILE_ID = GEN_ID(CODA$NEXT_ID, 1);
END
^
/* Trigger: AI_CODA$TG_SET_GROUP_ID */
CREATE TRIGGER AI_CODA$TG_SET_GROUP_ID FOR CODA$TRANSACTION_GROUP ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.GROUP_ID IS NULL) THEN
NEW.GROUP_ID = GEN_ID(CODA$NEXT_ID, 1);
END
^
/* Trigger: AI_CODA$TO_SET_OPERATION_ID */
CREATE TRIGGER AI_CODA$TO_SET_OPERATION_ID FOR CODA$TRANSACTIONS_OPERATIONS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.OPERATION_ID IS NULL) THEN
NEW.OPERATION_ID = GEN_ID(CODA$NEXT_ID, 1);
END
^
/* Trigger: AI_CODA$TT_SET_TYPE_ID */
CREATE TRIGGER AI_CODA$TT_SET_TYPE_ID FOR CODA$TRANSACTION_TYPES ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.TYPE_ID IS NULL) THEN
NEW.TYPE_ID = GEN_ID(CODA$NEXT_ID, 1);
END
^
/* Trigger: AI_FILES$COSTS_SET_COST_ID */
CREATE TRIGGER AI_FILES$COSTS_SET_COST_ID FOR FILES$COSTS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.COST_ID IS NULL) THEN
NEW.COST_ID = GEN_ID(FILES$NEXT_ID, 1);
END
^
/* Trigger: AI_FILES$SET_ACTION_ID */
CREATE TRIGGER AI_FILES$SET_ACTION_ID FOR FILES$ACTIONS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.ACTION_ID IS NULL) THEN
NEW.ACTION_ID = GEN_ID(FILES$NEXT_ID, 1);
END
^
/* Trigger: AI_FILES$SET_DEPTOR_ID */
CREATE TRIGGER AI_FILES$SET_DEPTOR_ID FOR FILES$DEPTORS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.DEPTOR_ID IS NULL) THEN
NEW.DEPTOR_ID = GEN_ID(FILES$NEXT_ID, 1);
END
^
/* Trigger: AI_FILES$SET_FILE_ACTION_ID */
CREATE TRIGGER AI_FILES$SET_FILE_ACTION_ID FOR FILES$FILE_ACTIONS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.FILE_ACTION_ID IS NULL) THEN
NEW.FILE_ACTION_ID = GEN_ID(FILES$NEXT_ID, 1);
END
^
/* Trigger: AI_FILES$SET_FILE_ID */
CREATE TRIGGER AI_FILES$SET_FILE_ID FOR FILES$FILES ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.FILE_ID IS NULL) THEN
NEW.FILE_ID = GEN_ID(FILES$NEXT_ID, 1);
END
^
/* Trigger: AI_FILES$SET_REFERENCE_ID */
CREATE TRIGGER AI_FILES$SET_REFERENCE_ID FOR FILES$REFERENCES ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.REFERENCE_ID IS NULL) THEN
NEW.REFERENCE_ID = GEN_ID(FILES$NEXT_ID, 1);
END
^
/* Trigger: AI_INVOICES$ID_SET_RECORD_ID */
CREATE TRIGGER AI_INVOICES$ID_SET_RECORD_ID FOR INVOICES$INVOICE_DETAILS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.INVOICE_DETAIL_ID IS NULL) THEN
NEW.INVOICE_DETAIL_ID = GEN_ID(INVOICES$NEXT_ID, 1);
END
^
/* Trigger: AI_INVOICES$INVOICES_INVOICE_ID */
CREATE TRIGGER AI_INVOICES$INVOICES_INVOICE_ID FOR INVOICES$INVOICES ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.INVOICE_ID IS NULL) THEN
NEW.INVOICE_ID = GEN_ID(INVOICES$NEXT_ID, 1);
END
^
/* Trigger: AI_SUPPORT$SET_COUNTRY_ID */
CREATE TRIGGER AI_SUPPORT$SET_COUNTRY_ID FOR SUPPORT$COUNTRIES ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.COUNTRY_ID IS NULL) THEN
NEW.COUNTRY_ID = GEN_ID(SUPPORT$NEXT_ID, 1);
END
^
/* Trigger: AI_SUPPORT$SET_LANGUAGE_ID */
CREATE TRIGGER AI_SUPPORT$SET_LANGUAGE_ID FOR SUPPORT$LANGUAGES ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.LANGUAGE_ID IS NULL) THEN
NEW.LANGUAGE_ID = GEN_ID(SUPPORT$NEXT_ID, 1);
END
^
/* Trigger: AI_SUPPORT$TITLES_TITLE_ID */
CREATE TRIGGER AI_SUPPORT$TITLES_TITLE_ID FOR SUPPORT$TITLES ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.TITLE_ID IS NULL) THEN
NEW.TITLE_ID = GEN_ID(SUPPORT$NEXT_ID, 1);
END
^
/* Trigger: AI_SYSTEM$SET_COLLECTOR_ID */
CREATE TRIGGER AI_SYSTEM$SET_COLLECTOR_ID FOR SYSTEM$COLLECTORS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.COLLECTOR_ID IS NULL) THEN
NEW.COLLECTOR_ID = GEN_ID(SYSTEM$NEXT_ID, 1);
END
^
/* Trigger: AI_SYSTEM$SET_EMPLOYEE_ID */
CREATE TRIGGER AI_SYSTEM$SET_EMPLOYEE_ID FOR SYSTEM$EMPLOYEES ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.EMPLOYEE_ID IS NULL) THEN
NEW.EMPLOYEE_ID = GEN_ID(SYSTEM$NEXT_ID, 1);
END
^
/* Trigger: BI_FILES$D_CHECK_VALUES */
CREATE TRIGGER BI_FILES$D_CHECK_VALUES FOR FILES$DEPTORS ACTIVE
BEFORE INSERT POSITION 1
AS
BEGIN
IF (NEW.LANGUAGE_ID IS NULL) THEN
BEGIN
SELECT SUPPORT$LANGUAGES.LANGUAGE_ID FROM SUPPORT$LANGUAGES WHERE SUPPORT$LANGUAGES.SYSTEM_DEFAULT = 1 INTO NEW.LANGUAGE_ID;
END
END
^
/* Trigger: BI_FILES$F_CHECK_VALUES */
CREATE TRIGGER BI_FILES$F_CHECK_VALUES FOR FILES$FILES ACTIVE
BEFORE INSERT POSITION 1
AS
BEGIN
/* Trigger body */
IF (NEW.STATE_ID IS NULL) THEN
BEGIN
NEW.STATE_ID = 0;
END
IF (NEW.COLLECTOR_ID IS NULL) THEN
BEGIN
SELECT SYSTEM$COLLECTORS.COLLECTOR_ID FROM SYSTEM$COLLECTORS WHERE SYSTEM$COLLECTORS.SYSTEM_DEFAULT = 1 INTO NEW.COLLECTOR_ID;
END
IF (NEW.STRUCTURED_STATEMENT IS NULL) THEN
BEGIN
EXECUTE PROCEDURE SUPPORT$STRUCTURED_STATEMENT (NEW.FILE_NR) RETURNING_VALUES NEW.STRUCTURED_STATEMENT;
END
END
^
/* Trigger: BI_SUPPORT$SET_ZIP_CODE_ID */
CREATE TRIGGER BI_SUPPORT$SET_ZIP_CODE_ID FOR SUPPORT$ZIP_CODES ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.ZIP_CODE_ID IS NULL) THEN
NEW.ZIP_CODE_ID = GEN_ID(SUPPORT$NEXT_ID, 1);
END
^
/* Trigger: BI_SUPPORT$ZC_CHECK_VALUES */
CREATE TRIGGER BI_SUPPORT$ZC_CHECK_VALUES FOR SUPPORT$ZIP_CODES ACTIVE
BEFORE INSERT POSITION 1
AS
BEGIN
/* Trigger body */
IF (NEW.COUNTRY_ID IS NULL) THEN
BEGIN
SELECT SUPPORT$COUNTRIES.COUNTRY_ID FROM SUPPORT$COUNTRIES WHERE SUPPORT$COUNTRIES.SYSTEM_DEFAULT = 1 INTO NEW.COUNTRY_ID;
END
END
^
/* Trigger: BI_SYSTEM$COL_CHECK_VALUES */
CREATE TRIGGER BI_SYSTEM$COL_CHECK_VALUES FOR SYSTEM$COLLECTORS ACTIVE
BEFORE INSERT POSITION 1
AS
BEGIN
/* Trigger body */
IF (NEW.LANGUAGE_ID IS NULL) THEN
BEGIN
SELECT SUPPORT$LANGUAGES.LANGUAGE_ID FROM SUPPORT$LANGUAGES WHERE SUPPORT$LANGUAGES.SYSTEM_DEFAULT = 1 INTO NEW.LANGUAGE_ID;
END
IF (NEW.ZIP_CODE_ID IS NULL) THEN
BEGIN
SELECT SUPPORT$ZIP_CODES.ZIP_CODE_ID FROM SUPPORT$ZIP_CODES WHERE SUPPORT$ZIP_CODES.CODE = 'UNKNOWN' INTO NEW.ZIP_CODE_ID;
END
END
^
/* Trigger: CLIENTS$BI_C_CHECK_VALUES */
CREATE TRIGGER CLIENTS$BI_C_CHECK_VALUES FOR CLIENTS$CLIENTS ACTIVE
BEFORE INSERT POSITION 1
AS
BEGIN
IF (NEW.ZIP_CODE_ID IS NULL) THEN
BEGIN
SELECT SUPPORT$ZIP_CODES.ZIP_CODE_ID FROM SUPPORT$ZIP_CODES WHERE SUPPORT$ZIP_CODES.CODE = 'UNKNOWN' INTO NEW.ZIP_CODE_ID;
END
IF (NEW.LANGUAGE_ID IS NULL) THEN
BEGIN
SELECT SUPPORT$LANGUAGES.LANGUAGE_ID FROM SUPPORT$LANGUAGES WHERE SUPPORT$LANGUAGES.SYSTEM_DEFAULT = 1 INTO NEW.LANGUAGE_ID;
END
END
^
/* Trigger: CODA$FS_SET_SECTION_ID */
CREATE TRIGGER CODA$FS_SET_SECTION_ID FOR CODA$FILE_SECTIONS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.SECTION_ID IS NULL) THEN
NEW.SECTION_ID = GEN_ID(CODA$NEXT_ID, 1);
END
^
/* Trigger: CODA$SET_ACCOUNT_ID */
CREATE TRIGGER CODA$SET_ACCOUNT_ID FOR CODA$FILE_SECTIONS ACTIVE
BEFORE INSERT POSITION 1
AS
BEGIN
SELECT ACCOUNTS$ACCOUNTS.ACCOUNT_ID
FROM ACCOUNTS$ACCOUNTS
WHERE ACCOUNTS$ACCOUNTS.ACCOUNT_NR = NEW.ACCOUNT_NO
INTO NEW.ACCOUNT_ID;
END
^
/* Trigger: CODA$TRANSACTIONS_AI */
CREATE TRIGGER CODA$TRANSACTIONS_AI FOR CODA$TRANSACTIONS ACTIVE
AFTER INSERT POSITION 0
AS
BEGIN
IF (NEW.PARENT_ID IS NOT NULL) THEN
BEGIN
UPDATE CODA$TRANSACTIONS PARENT
SET PARENT.HAS_DETAILS = 1
WHERE PARENT.TRANSACTION_ID = NEW.PARENT_ID;
END
END
^
/* Trigger: CODA$TRANSACTIONS_CHECK_BI */
CREATE TRIGGER CODA$TRANSACTIONS_CHECK_BI FOR CODA$TRANSACTIONS ACTIVE
BEFORE INSERT POSITION 1
AS
DECLARE VARIABLE varFileID INTEGER;
DECLARE VARIABLE varStatement CHAR(255);
DECLARE VARIABLE varFileNR INTEGER;
BEGIN
/* Check operation type */
IF ((NEW.OPERATION_CODE LIKE '00150%') OR (NEW.OPERATION_CODE LIKE '00152%') OR (NEW.OPERATION_CODE LIKE '00950%')) THEN
BEGIN
IF ((NEW.STRUCTURED_STATEMENT = 1) AND ((NEW.STATEMENT LIKE '101%') OR (NEW.STATEMENT LIKE '102%'))) THEN
BEGIN
varStatement = UDF_SUBSTR(NEW.STATEMENT,4, 16);
SELECT FILES$FILES.FILE_ID
FROM FILES$FILES
WHERE FILES$FILES.STRUCTURED_STATEMENT = :varStatement
INTO :varFileID;
IF (varFileID IS NOT NULL) THEN
BEGIN
INSERT INTO CODA$BOOKING (TRANSACTION_ID, FILE_ID, AMOUNT) VALUES (NEW.TRANSACTION_ID, :varFileID, NEW.AMOUNT);
NEW.LINK_TYPE = 1;
NEW.ACTION_CODE = 9; /* OK */
END
END
IF ((NEW.STRUCTURED_STATEMENT = 0) OR (NEW.STRUCTURED_STATEMENT IS NULL)) THEN /* Check IF Statement is a Number*/
BEGIN
varStatement = NEW.STATEMENT;
IF (NEW.STATEMENT STARTING WITH 'DOSSIER NR. ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 13, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'DOSSIER NR ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 12, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'DOSSIER NO ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 12, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'DOSSIER REF ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 13, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'REF DOSSIER ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 13, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'DOSSIER N ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 11, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'DOSSIER ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 9, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'DOSS ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 6, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'DOS. ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 6, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'REF. ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 6, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'DOS ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 5, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'REF ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 5, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'DOS.') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 5, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'REF.') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 5, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'REF ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 5, 255);
ELSE IF (NEW.STATEMENT STARTING WITH 'NO ') THEN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 4, 255);
EXECUTE PROCEDURE SUPPORT$NUMPART(:varStatement) RETURNING_VALUES :varStatement;
varFileNR = CAST(varStatement AS INTEGER);
SELECT FILES$FILES.FILE_ID
FROM FILES$FILES
WHERE FILES$FILES.FILE_NR = :varFileNr
INTO :varFileID;
IF (varFileID IS NOT NULL) THEN
BEGIN
INSERT INTO CODA$BOOKING (TRANSACTION_ID, FILE_ID, AMOUNT) VALUES (NEW.TRANSACTION_ID, :varFileID, NEW.AMOUNT);
NEW.LINK_TYPE = 1;
NEW.ACTION_CODE = 8; /* Should be checked */
END
WHEN SQLCODE -413 DO
BEGIN
NEW.ACTION_CODE = 1;
END
WHEN SQLCODE -802 DO
BEGIN
NEW.ACTION_CODE = 1;
END
END
END
IF (NEW.OPERATION_CODE LIKE '84150100') THEN
BEGIN
IF ((NEW.STRUCTURED_STATEMENT = 1) AND (NEW.STATEMENT LIKE '105%')) THEN
BEGIN
varStatement = UDF_SUBSTR(NEW.STATEMENT, 48, 60);
NEW.LINK_TYPE = 1;
SELECT FILES$FILES.FILE_ID
FROM FILES$FILES
WHERE FILES$FILES.STRUCTURED_STATEMENT = :varStatement
INTO :varFileID;
IF (varFileID IS NOT NULL) THEN
BEGIN
INSERT INTO CODA$BOOKING (TRANSACTION_ID, FILE_ID, AMOUNT) VALUES (NEW.TRANSACTION_ID, :varFileID, NEW.AMOUNT);
NEW.LINK_TYPE = 1;
NEW.ACTION_CODE = 9; /* OK */
END
END
END
END
^
/* Trigger: CODA$T_SET_TRANSACTION_ID */
CREATE TRIGGER CODA$T_SET_TRANSACTION_ID FOR CODA$TRANSACTIONS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.TRANSACTION_ID IS NULL) THEN
NEW.TRANSACTION_ID = GEN_ID(CODA$NEXT_ID, 1);
END
^
/* Trigger: FILES$AD_F_SET_FILE_COUNT */
CREATE TRIGGER FILES$AD_F_SET_FILE_COUNT FOR FILES$FILES ACTIVE
AFTER DELETE POSITION 0
AS
BEGIN
IF (OLD.DATE_CLOSED IS NULL) THEN
BEGIN
UPDATE CLIENTS$CLIENTS
SET CLIENTS$CLIENTS.OPEN_FILES = CLIENTS$CLIENTS.OPEN_FILES - 1,
CLIENTS$CLIENTS.FILE_COUNT = CLIENTS$CLIENTS.FILE_COUNT - 1
WHERE CLIENTS$CLIENTS.CLIENT_ID = OLD.CLIENT_ID;
UPDATE FILES$DEPTORS
SET FILES$DEPTORS.FILE_COUNT = FILES$DEPTORS.FILE_COUNT - 1,
FILES$DEPTORS.OPEN_FILES = FILES$DEPTORS.OPEN_FILES - 1
WHERE FILES$DEPTORS.DEPTOR_ID = OLD.DEPTOR_ID;
END
ELSE
BEGIN
UPDATE CLIENTS$CLIENTS
SET CLIENTS$CLIENTS.FILE_COUNT = CLIENTS$CLIENTS.FILE_COUNT - 1
WHERE CLIENTS$CLIENTS.CLIENT_ID = OLD.CLIENT_ID;
UPDATE FILES$DEPTORS
SET FILES$DEPTORS.FILE_COUNT = FILES$DEPTORS.FILE_COUNT - 1
WHERE FILES$DEPTORS.DEPTOR_ID = OLD.DEPTOR_ID;
END
END
^
/* Trigger: FILES$AI_F_SET_FILE_COUNT */
CREATE TRIGGER FILES$AI_F_SET_FILE_COUNT FOR FILES$FILES ACTIVE
AFTER INSERT POSITION 0
AS
BEGIN
/* Trigger body */
IF (NEW.DATE_CLOSED IS NOT NULL) THEN
BEGIN
UPDATE CLIENTS$CLIENTS
SET CLIENTS$CLIENTS.FILE_COUNT = CLIENTS$CLIENTS.FILE_COUNT + 1
WHERE CLIENTS$CLIENTS.CLIENT_ID = NEW.CLIENT_ID;
UPDATE FILES$DEPTORS
SET FILES$DEPTORS.FILE_COUNT = FILES$DEPTORS.FILE_COUNT + 1
WHERE FILES$DEPTORS.DEPTOR_ID = NEW.DEPTOR_ID;
END
ELSE
BEGIN
UPDATE CLIENTS$CLIENTS
SET CLIENTS$CLIENTS.FILE_COUNT = CLIENTS$CLIENTS.FILE_COUNT + 1,
CLIENTS$CLIENTS.OPEN_FILES = CLIENTS$CLIENTS.OPEN_FILES + 1
WHERE CLIENTS$CLIENTS.CLIENT_ID = NEW.CLIENT_ID;
UPDATE FILES$DEPTORS
SET FILES$DEPTORS.FILE_COUNT = FILES$DEPTORS.FILE_COUNT + 1,
FILES$DEPTORS.OPEN_FILES = FILES$DEPTORS.OPEN_FILES + 1
WHERE FILES$DEPTORS.DEPTOR_ID = NEW.DEPTOR_ID;
END
END
^
/* Trigger: FILES$AU_F_SET_FILE_COUNT */
CREATE TRIGGER FILES$AU_F_SET_FILE_COUNT FOR FILES$FILES ACTIVE
AFTER UPDATE POSITION 0
AS
BEGIN
/* Check reopen */
IF ((OLD.DATE_CLOSED IS NOT NULL) AND (NEW.DATE_CLOSED IS NULL)) THEN
BEGIN
UPDATE CLIENTS$CLIENTS
SET CLIENTS$CLIENTS.OPEN_FILES = CLIENTS$CLIENTS.OPEN_FILES + 1
WHERE CLIENTS$CLIENTS.CLIENT_ID = NEW.CLIENT_ID;
UPDATE FILES$DEPTORS
SET FILES$DEPTORS.OPEN_FILES = FILES$DEPTORS.OPEN_FILES + 1
WHERE FILES$DEPTORS.DEPTOR_ID = NEW.DEPTOR_ID;
END
IF ((NEW.DATE_CLOSED IS NOT NULL) AND (OLD.DATE_CLOSED IS NULL)) THEN
BEGIN
UPDATE CLIENTS$CLIENTS
SET CLIENTS$CLIENTS.OPEN_FILES = CLIENTS$CLIENTS.OPEN_FILES - 1
WHERE CLIENTS$CLIENTS.CLIENT_ID = NEW.CLIENT_ID;
UPDATE FILES$DEPTORS
SET FILES$DEPTORS.OPEN_FILES = FILES$DEPTORS.OPEN_<br/><br/>(Message over 64 KB, truncated)