672 lines
28 KiB
Transact-SQL
672 lines
28 KiB
Transact-SQL
-- Created by GitHub Copilot in SSMS - review carefully before executing
|
|
DECLARE @tpl NVARCHAR(MAX)= '
|
|
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Entry'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
DROP TABLE @dbo@.Entry;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Entry'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
CREATE TABLE @dbo@.Entry (
|
|
Entry_ID int NOT NULL,
|
|
ET_accounting_period int NOT NULL,
|
|
ET_document_header int NOT NULL,
|
|
ET_sales_tax_code int NULL,
|
|
ET_currency int NOT NULL,
|
|
ET_entry_type int NOT NULL,
|
|
ET_account int NOT NULL,
|
|
ET_predefined_entry int NULL,
|
|
ET_entry_address int NULL,
|
|
ET_batch_run int NULL,
|
|
ET_value_date datetime NOT NULL,
|
|
ET_original_date datetime NOT NULL,
|
|
ET_input_date datetime NOT NULL,
|
|
ET_debit_base_amount decimal(14,2) NULL,
|
|
ET_credit_base_amount decimal(14,2) NULL,
|
|
ET_debit_currency_amount decimal(14,2) NULL,
|
|
ET_credit_currency_amount decimal(14,2) NULL,
|
|
ET_exchange_rate float NOT NULL DEFAULT (1),
|
|
ET_debit_quantity decimal(11,4) NULL,
|
|
ET_credit_quantity decimal(11,4) NULL,
|
|
ET_extourne_number int NULL,
|
|
ET_official_number int NULL,
|
|
ET_text varchar(60) NULL,
|
|
ET_hide_code bit NOT NULL DEFAULT (0),
|
|
ET_tax_reclaiming_rate decimal(5,2) NULL,
|
|
ET_VAT_identifier varchar(60) NULL,
|
|
ET_TS timestamp NOT NULL,
|
|
ET_tax_base_currency_amount decimal(14,2) NULL,
|
|
ET_interco_type smallint NULL,
|
|
ET_VGUID varchar(36) NULL DEFAULT (newid()),
|
|
ET_master_ID varchar(60) NULL,
|
|
ET_reconciliation_status smallint NULL,
|
|
ET_reconciliation_base_amount decimal(14,2) NULL,
|
|
ET_reconciliation_curr_amount decimal(14,2) NULL,
|
|
ET_bmc_user_profile int NULL,
|
|
ET_APS_TS datetime NULL DEFAULT (getdate()),
|
|
ET_rowguid uniqueidentifier NOT NULL DEFAULT (newsequentialid()),
|
|
ET_pharmacy_code char(6) NOT NULL DEFAULT (''@phcode@''),
|
|
CONSTRAINT PK_Entry PRIMARY KEY NONCLUSTERED (Entry_ID),
|
|
CONSTRAINT CK_ET_interco_type CHECK ([ET_interco_type] >= 1 and [ET_interco_type] <= 3),
|
|
CONSTRAINT CK_ET_pharmacy_code CHECK ([ET_pharmacy_code]=''@phcode@'')
|
|
);
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Entry_reconciliation'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
DROP TABLE @dbo@.Entry_reconciliation;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Entry_reconciliation'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
CREATE TABLE @dbo@.Entry_reconciliation (
|
|
Entry_reconciliation_ID int NOT NULL,
|
|
ETR_entry int NOT NULL,
|
|
ETR_linked_entry int NOT NULL,
|
|
ETR_base_amount decimal(14,2) NULL,
|
|
ETR_currency_amount decimal(14,2) NULL,
|
|
ETR_debit_base_amount decimal(14,2) NULL,
|
|
ETR_credit_base_amount decimal(14,2) NULL,
|
|
ETR_debit_currency_amount decimal(14,2) NULL,
|
|
ETR_credit_currency_amount decimal(14,2) NULL,
|
|
ETR_value_date datetime NULL,
|
|
ETR_remark varchar(255) NULL,
|
|
ETR_TS timestamp NOT NULL,
|
|
ETR_VGUID varchar(36) NULL DEFAULT (newid()),
|
|
ETR_master_ID varchar(60) NULL,
|
|
ETR_rowguid uniqueidentifier NOT NULL DEFAULT (newsequentialid()),
|
|
ETR_pharmacy_code char(6) NOT NULL DEFAULT (''@phcode@''),
|
|
CONSTRAINT PK_Entry_reconciliation PRIMARY KEY NONCLUSTERED (Entry_reconciliation_ID),
|
|
CONSTRAINT CK_ETR_pharmacy_code CHECK ([ETR_pharmacy_code]=''@phcode@'')
|
|
);
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Financial_relation_account'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
DROP TABLE @dbo@.Financial_relation_account;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Financial_relation_account'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
CREATE TABLE @dbo@.Financial_relation_account (
|
|
Financial_relation_account_ID int NOT NULL,
|
|
FNRA_subsidiary int NOT NULL,
|
|
FNRA_BVR_member int NULL,
|
|
FNRA_source_financial_relation int NULL,
|
|
FNRA_address int NOT NULL,
|
|
FNRA_beneficiary_address int NULL,
|
|
FNRA_document_predefined_entry int NULL,
|
|
FNRA_payment_predefined_entry int NULL,
|
|
FNRA_financial_institution int NOT NULL,
|
|
FNRA_account int NULL,
|
|
FNRA_supplier_DTA_structure uniqueidentifier NULL,
|
|
FNRA_payroll_DTA_structure uniqueidentifier NULL,
|
|
FNRA_sequence smallint NULL,
|
|
FNRA_account_number varchar(30) NULL,
|
|
FNRA_text varchar(60) NULL,
|
|
FNRA_house_bank bit NOT NULL DEFAULT ((0)),
|
|
FNRA_by_default bit NOT NULL DEFAULT ((0)),
|
|
FNRA_by_default_payroll bit NOT NULL DEFAULT ((0)),
|
|
FNRA_cust_ident varchar(15) NULL,
|
|
FNRA_doc_number_pos smallint NOT NULL DEFAULT ((0)),
|
|
FNRA_doc_number_length smallint NULL,
|
|
FNRA_value_date_pos smallint NOT NULL DEFAULT ((0)),
|
|
FNRA_value_date_length smallint NULL,
|
|
FNRA_value_date_format varchar(15) NULL,
|
|
FNRA_DTA_identifier varchar(15) NULL,
|
|
FNRA_DTA_payroll_identifier varchar(15) NULL,
|
|
FNRA_IBAN_identifier varchar(60) NULL,
|
|
FNRA_bank_fees_settlement smallint NULL,
|
|
FNRA_active bit NOT NULL DEFAULT ((1)),
|
|
FNRA_EAS_process bit NOT NULL DEFAULT ((0)),
|
|
FNRA_VGUID varchar(36) NULL DEFAULT (newid()),
|
|
FNRA_master_ID varchar(60) NULL,
|
|
FNRA_TS timestamp NOT NULL,
|
|
FNRA_QR_IBAN_identifier varchar(60) NULL,
|
|
FNRA_rowguid uniqueidentifier NOT NULL DEFAULT (newsequentialid()),
|
|
FNRA_pharmacy_code char(6) NOT NULL DEFAULT (''@phcode@''),
|
|
CONSTRAINT PK_Financial_relation_account PRIMARY KEY NONCLUSTERED (Financial_relation_account_ID),
|
|
CONSTRAINT CK_FNRA_bank_fees_settlement CHECK ([FNRA_bank_fees_settlement]>=(1) AND [FNRA_bank_fees_settlement]<=(3)),
|
|
CONSTRAINT CK_FNRA_pharmacy_code CHECK ([FNRA_pharmacy_code]=''@phcode@'')
|
|
);
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Address'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
DROP TABLE @dbo@.Address;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Address'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
CREATE TABLE @dbo@.Address (
|
|
Address_ID int NOT NULL,
|
|
AD_title int NULL,
|
|
AD_language int NOT NULL,
|
|
AD_mailing_language int NULL,
|
|
AD_country int NOT NULL,
|
|
AD_bmc_user_profile int NULL,
|
|
AD_name varchar(60) NULL,
|
|
AD_name_supplement varchar(255) NULL,
|
|
AD_first_name varchar(30) NULL,
|
|
AD_middle_name varchar(4) NULL,
|
|
AD_last_name varchar(30) NULL,
|
|
AD_address_1 varchar(60) NULL,
|
|
AD_address_2 varchar(60) NULL,
|
|
AD_address_3 varchar(60) NULL,
|
|
AD_zip_code varchar(15) NULL,
|
|
AD_city varchar(30) NULL,
|
|
AD_state varchar(15) NULL,
|
|
AD_postal_routing_address varchar(60) NULL,
|
|
AD_area varchar(15) NULL,
|
|
AD_activity varchar(255) NULL,
|
|
AD_EDI_identifier varchar(15) NULL,
|
|
AD_VAT_identifier varchar(60) NULL,
|
|
AD_swisskey varchar(30) NULL,
|
|
AD_EU_identity_code varchar(15) NULL,
|
|
AD_sex smallint NOT NULL DEFAULT (3),
|
|
AD_date_of_birth datetime NULL,
|
|
AD_mailing bit NOT NULL DEFAULT (0),
|
|
AD_advertising bit NOT NULL DEFAULT (0),
|
|
AD_status smallint NOT NULL DEFAULT (1),
|
|
AD_remark varchar(255) NULL,
|
|
AD_memo nvarchar(max) NULL,
|
|
AD_concordat varchar(15) NULL,
|
|
AD_place_of_origin varchar(30) NULL,
|
|
AD_place_of_birth varchar(30) NULL,
|
|
AD_social_security_number varchar(30) NULL,
|
|
AD_qualification varchar(255) NULL,
|
|
AD_creation_date datetime NULL,
|
|
AD_modified bit NOT NULL DEFAULT (0),
|
|
AD_TS timestamp NOT NULL,
|
|
AD_VGUID varchar(36) NULL DEFAULT (newid()),
|
|
AD_master_ID varchar(60) NULL,
|
|
AD_criteria_schema_header uniqueidentifier NULL,
|
|
AD_end_date_validity datetime NULL,
|
|
AD_customs_identifier varchar(15) NULL,
|
|
AD_enterprise_identifier varchar(30) NULL,
|
|
AD_geo_address varchar(60) NULL,
|
|
AD_geo_zip_code varchar(15) NULL,
|
|
AD_geo_city varchar(60) NULL,
|
|
AD_geo_state varchar(15) NULL,
|
|
AD_geo_country int NULL,
|
|
AD_geo_postal_routing_address varchar(60) NULL,
|
|
AD_rowguid uniqueidentifier NOT NULL DEFAULT (newsequentialid()),
|
|
AD_pharmacy_code char(6) NOT NULL DEFAULT (''@phcode@''),
|
|
AD_origin tinyint NOT NULL DEFAULT ((4)),
|
|
AD_omnichannel_UUID char(64) NULL,
|
|
AD_address_supplement varchar(60) NULL,
|
|
CONSTRAINT PK_Address PRIMARY KEY NONCLUSTERED (Address_ID),
|
|
CONSTRAINT CK_AD_origin CHECK ([AD_origin]=(5) OR [AD_origin]=(4) OR [AD_origin]=(3) OR [AD_origin]=(2) OR [AD_origin]=(1)),
|
|
CONSTRAINT CK_AD_pharmacy_code CHECK ([AD_pharmacy_code]=''@phcode@''),
|
|
CONSTRAINT CK_AD_sex CHECK ([AD_sex] >= 1 and [AD_sex] <= 4),
|
|
CONSTRAINT CK_AD_status CHECK ([AD_status] = 99 or [AD_status] = 5 or [AD_status] = 4 or [AD_status] = 3 or [AD_status] = 2 or [AD_status] = 1)
|
|
);
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Address_Criteria'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
DROP TABLE @dbo@.Address_Criteria;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Address_Criteria'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
CREATE TABLE @dbo@.Address_Criteria (
|
|
Address_criteria_ID int NOT NULL,
|
|
ADCR_subsidiary int NULL,
|
|
ADCR_address int NOT NULL,
|
|
ADCR_criteria int NOT NULL,
|
|
ADCR_criteria_weight smallint NULL,
|
|
ADCR_remark nvarchar(max) NULL,
|
|
ADCR_update_date datetime NULL,
|
|
ADCR_active bit NOT NULL DEFAULT (1),
|
|
ADCR_TS timestamp NOT NULL,
|
|
ADCR_valid_from datetime NULL,
|
|
ADCR_valid_until datetime NULL,
|
|
ADCR_VGUID varchar(36) NULL DEFAULT (newid()),
|
|
ADCR_master_ID varchar(60) NULL,
|
|
ADCR_rowguid uniqueidentifier NOT NULL DEFAULT (newsequentialid()),
|
|
ADCR_pharmacy_code char(6) NOT NULL DEFAULT (''@phcode@''),
|
|
CONSTRAINT PK_Address_criteria PRIMARY KEY NONCLUSTERED (Address_criteria_ID),
|
|
CONSTRAINT CK_ADCR_pharmacy_code CHECK ([ADCR_pharmacy_code]=''@phcode@'')
|
|
);
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Address_key'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
DROP TABLE @dbo@.Address_key;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Address_key'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
CREATE TABLE @dbo@.Address_key (
|
|
Address_key_ID int NOT NULL,
|
|
ADK_subsidiary int NOT NULL,
|
|
ADK_address int NOT NULL,
|
|
ADK_key varchar(15) NULL,
|
|
ADK_type smallint NOT NULL DEFAULT (2),
|
|
ADK_remark varchar(255) NULL,
|
|
ADK_user_ID varchar(18) NULL,
|
|
ADK_APS_TS datetime NOT NULL DEFAULT (getdate()),
|
|
ADK_VGUID varchar(36) NULL DEFAULT (newid()),
|
|
ADK_master_ID varchar(60) NULL,
|
|
ADK_TS timestamp NOT NULL,
|
|
ADK_rowguid uniqueidentifier NOT NULL DEFAULT (newsequentialid()),
|
|
ADK_pharmacy_code char(6) NOT NULL DEFAULT (''@phcode@''),
|
|
CONSTRAINT PK_Address_key PRIMARY KEY NONCLUSTERED (Address_key_ID),
|
|
CONSTRAINT CK_ADK_pharmacy_code CHECK ([ADK_pharmacy_code]=''@phcode@'')
|
|
);
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Document_error'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
DROP TABLE @dbo@.Document_error;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Document_error'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
CREATE TABLE @dbo@.Document_error (
|
|
Document_error_GUID uniqueidentifier NOT NULL,
|
|
DE_document_header int NULL,
|
|
DE_document_line int NULL,
|
|
DE_reason int NOT NULL,
|
|
DE_remedy int NULL,
|
|
DE_bmc_user_profile int NOT NULL,
|
|
DE_creation_date datetime NOT NULL DEFAULT (getdate()),
|
|
DE_update_date datetime NULL,
|
|
DE_proceed bit NOT NULL DEFAULT ((0)),
|
|
DE_remark varchar(8000) NULL,
|
|
DE_APS_TS datetime NOT NULL DEFAULT (getdate()),
|
|
DE_VGUID varchar(36) NULL DEFAULT (newid()),
|
|
DE_master_ID varchar(60) NULL,
|
|
DE_TS timestamp NOT NULL,
|
|
DE_delivery_note_header int NULL,
|
|
DE_xml_remark xml NULL,
|
|
DE_xml_data xml NULL,
|
|
DE_triaFact_update_date datetime NULL,
|
|
DE_pharmacy_code char(6) NOT NULL DEFAULT (''@phcode@''),
|
|
CONSTRAINT PK_Document_error PRIMARY KEY NONCLUSTERED (Document_error_GUID),
|
|
CONSTRAINT CK_DE_pharmacy_code CHECK ([DE_pharmacy_code]=''@phcode@'')
|
|
);
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Accounting_turnover_agg'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
DROP TABLE @dbo@.Accounting_turnover_agg;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Accounting_turnover_agg'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
CREATE TABLE @dbo@.Accounting_turnover_agg (
|
|
Accounting_turnover_agg_ID int IDENTITY(1,1) NOT NULL,
|
|
ATA_organizational_unit int NOT NULL,
|
|
ATA_type tinyint NOT NULL,
|
|
ATA_debit_credit tinyint NOT NULL,
|
|
ATA_sub_type tinyint NOT NULL,
|
|
ATA_expense_item_key varchar(30) NULL,
|
|
ATA_nature tinyint NOT NULL,
|
|
ATA_sub_nature varchar(15) NULL,
|
|
ATA_tax_code varchar(4) NULL,
|
|
ATA_amount decimal(14,2) NOT NULL,
|
|
ATA_amount_VAT decimal(14,2) NOT NULL,
|
|
ATA_tax_amount decimal(14,2) NOT NULL,
|
|
ATA_accounting_value_date datetime2(7) NOT NULL,
|
|
ATA_load_date datetime2(7) NOT NULL,
|
|
ATA_tax_rate decimal(3,2) NULL,
|
|
ATA_pharmacy_code char(6) NOT NULL DEFAULT (''@phcode@''),
|
|
ATA_doc_number varchar(15) NULL,
|
|
CONSTRAINT PK_Accounting_turnover_agg PRIMARY KEY CLUSTERED (Accounting_turnover_agg_ID),
|
|
CONSTRAINT CK_ATA_pharmacy_code CHECK ([ATA_pharmacy_code]=''@phcode@'')
|
|
);
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Account'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
DROP TABLE @dbo@.Account;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Account'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
CREATE TABLE @dbo@.Account (
|
|
Account_ID int NOT NULL,
|
|
AC_sales_tax_code int NULL,
|
|
AC_default_currency int NOT NULL,
|
|
AC_column_swap_account_number int NULL,
|
|
AC_print_style int NULL,
|
|
AC_account_interest int NULL,
|
|
AC_transitory_account int NULL,
|
|
AC_type smallint NOT NULL DEFAULT ((1)),
|
|
AC_can_be_debited bit NOT NULL DEFAULT ((1)),
|
|
AC_can_be_credited bit NOT NULL DEFAULT ((1)),
|
|
AC_availability_start_date datetime NOT NULL,
|
|
AC_availability_end_date datetime NULL,
|
|
AC_liable_to_tax smallint NOT NULL DEFAULT ((1)),
|
|
AC_credit_limit_alert decimal NULL,
|
|
AC_credit_limit_blocking decimal NULL,
|
|
AC_credit_limit_action smallint NULL,
|
|
AC_currency_revaluation bit NOT NULL DEFAULT ((0)),
|
|
AC_foreign_currency_allowed bit NOT NULL DEFAULT ((0)),
|
|
AC_multicurrency bit NOT NULL DEFAULT ((0)),
|
|
AC_replacement_account int NULL,
|
|
AC_balance_sheet_column_swap bit NOT NULL DEFAULT ((0)),
|
|
AC_CA_breakdown_required bit NOT NULL DEFAULT ((0)),
|
|
AC_PCT_required bit NOT NULL DEFAULT ((0)),
|
|
AC_PJE_breakdown_required bit NOT NULL DEFAULT ((0)),
|
|
AC_cash_and_bank_account_code smallint NULL DEFAULT ((1)),
|
|
AC_number_of_decimals smallint NULL,
|
|
AC_tax_reclaiming_rate decimal NULL,
|
|
AC_def_currency_rate_type smallint NOT NULL DEFAULT ((1)),
|
|
AC_remark varchar(255) NULL,
|
|
AC_grouping_level_1 smallint NULL DEFAULT ((1)),
|
|
AC_grouping_level_2 smallint NULL DEFAULT ((1)),
|
|
AC_reconciliation bit NOT NULL DEFAULT ((1)),
|
|
AC_depreciation smallint NOT NULL DEFAULT ((2)),
|
|
AC_TS timestamp NOT NULL,
|
|
AC_VGUID varchar(36) NULL DEFAULT (newid()),
|
|
AC_master_ID varchar(60) NULL,
|
|
AC_rowguid uniqueidentifier NOT NULL DEFAULT (newsequentialid()),
|
|
AC_pharmacy_code char(6) NOT NULL DEFAULT (''@phcode@''),
|
|
CONSTRAINT PK_Account PRIMARY KEY NONCLUSTERED (Account_ID),
|
|
CONSTRAINT CK_Account_pharmacy_code CHECK ([AC_pharmacy_code]=''@phcode@'')
|
|
);
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Account_key'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
DROP TABLE @dbo@.Account_key;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Account_key'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
CREATE TABLE @dbo@.Account_key (
|
|
Account_key_ID int NOT NULL,
|
|
ACK_accounting_plan int NOT NULL,
|
|
ACK_fiscal_year int NOT NULL,
|
|
ACK_account int NOT NULL,
|
|
ACK_key varchar(30) NULL,
|
|
ACK_type smallint NOT NULL,
|
|
ACK_remark varchar(255) NULL,
|
|
ACK_TS timestamp NOT NULL,
|
|
ACK_VGUID varchar(36) NULL DEFAULT (newid()),
|
|
ACK_master_ID varchar(60) NULL,
|
|
ACK_rowguid uniqueidentifier NOT NULL DEFAULT (newsequentialid()),
|
|
ACK_pharmacy_code char(6) NOT NULL DEFAULT (''@phcode@''),
|
|
CONSTRAINT PK_Account_key PRIMARY KEY NONCLUSTERED (Account_key_ID),
|
|
CONSTRAINT CK_Account_key_pharmacy_code CHECK ([ACK_pharmacy_code]=''@phcode@'')
|
|
);
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Account_link'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
DROP TABLE @dbo@.Account_link;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Account_link'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
CREATE TABLE @dbo@.Account_link (
|
|
Account_link_ID int NOT NULL,
|
|
ACL_subsidiary int NOT NULL,
|
|
ACL_address int NULL,
|
|
ACL_account int NOT NULL,
|
|
ACL_type smallint NOT NULL,
|
|
ACL_by_default bit NOT NULL DEFAULT (0),
|
|
ACL_remark varchar(255) NULL,
|
|
ACL_TS timestamp NOT NULL,
|
|
ACL_VGUID varchar(36) NULL DEFAULT (newid()),
|
|
ACL_master_ID varchar(60) NULL,
|
|
ACL_rowguid uniqueidentifier NOT NULL DEFAULT (newsequentialid()),
|
|
ACL_pharmacy_code char(6) NOT NULL DEFAULT (''@phcode@''),
|
|
CONSTRAINT PK_Account_link PRIMARY KEY NONCLUSTERED (Account_link_ID),
|
|
CONSTRAINT CK_Account_link_pharmacy_code CHECK ([ACL_pharmacy_code]=''@phcode@'')
|
|
);
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Account_text'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
DROP TABLE @dbo@.Account_text;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Account_text'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
CREATE TABLE @dbo@.Account_text (
|
|
Account_text_ID int NOT NULL,
|
|
ACTX_account int NOT NULL,
|
|
ACTX_fiscal_year int NOT NULL,
|
|
ACTX_language int NOT NULL,
|
|
ACTX_usage_text varchar(30) NULL,
|
|
ACTX_text varchar(255) NULL,
|
|
ACTX_TS timestamp NOT NULL,
|
|
ACTX_VGUID varchar(36) NULL DEFAULT (newid()),
|
|
ACTX_master_ID varchar(60) NULL,
|
|
ACTX_instructions varchar(8000) NULL,
|
|
ACTX_rowguid uniqueidentifier NOT NULL DEFAULT (newsequentialid()),
|
|
ACTX_pharmacy_code char(6) NOT NULL DEFAULT (''@phcode@''),
|
|
CONSTRAINT PK_Account_text PRIMARY KEY NONCLUSTERED (Account_text_ID),
|
|
CONSTRAINT CK_Account_text_pharmacy_code CHECK ([ACTX_pharmacy_code]=''@phcode@'')
|
|
);
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Batch_run'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
DROP TABLE @dbo@.Batch_run;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Batch_run'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
CREATE TABLE @dbo@.Batch_run (
|
|
Batch_run_ID int NOT NULL,
|
|
BR_subsidiary int NOT NULL,
|
|
BR_batch_type int NOT NULL,
|
|
BR_bmc_user_profile int NULL,
|
|
BR_batch_number varchar(15) NOT NULL,
|
|
BR_value_date datetime NULL,
|
|
BR_remark varchar(255) NULL,
|
|
BR_text varchar(60) NULL,
|
|
BR_status smallint NOT NULL DEFAULT (1),
|
|
BR_TS timestamp NOT NULL,
|
|
BR_VGUID varchar(36) NULL DEFAULT (newid()),
|
|
BR_master_ID varchar(60) NULL,
|
|
BR_parameters varchar(max) NULL,
|
|
BR_rowguid uniqueidentifier NOT NULL DEFAULT (newsequentialid()),
|
|
BR_pharmacy_code char(6) NOT NULL DEFAULT (''@phcode@''),
|
|
CONSTRAINT PK_Batch_run PRIMARY KEY NONCLUSTERED (Batch_run_ID),
|
|
CONSTRAINT CK_Batch_run_pharmacy_code CHECK ([BR_pharmacy_code]=''@phcode@'')
|
|
);
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''BVR_member'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
DROP TABLE @dbo@.BVR_member;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = ''BVR_member'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
CREATE TABLE @dbo@.BVR_member (
|
|
BVR_member_ID int NOT NULL,
|
|
BVRM_member_number varchar(15) NULL,
|
|
BVRM_member_account varchar(15) NULL,
|
|
BVRM_bank smallint NOT NULL DEFAULT (1),
|
|
BVRM_cust_ident_pos smallint NOT NULL DEFAULT (0),
|
|
BVRM_cust_ident_length smallint NULL,
|
|
BVRM_TS timestamp NOT NULL,
|
|
BVRM_VGUID varchar(36) NULL DEFAULT (newid()),
|
|
BVRM_master_ID varchar(60) NULL,
|
|
BVR_rowguid uniqueidentifier NOT NULL DEFAULT (newsequentialid()),
|
|
BVRM_pharmacy_code char(6) NOT NULL DEFAULT (''@phcode@''),
|
|
CONSTRAINT PK_BVR_member PRIMARY KEY NONCLUSTERED (BVR_member_ID),
|
|
CONSTRAINT CK_BVR_member_pharmacy_code CHECK ([BVRM_pharmacy_code]=''@phcode@'')
|
|
);
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''CRS_batch_run'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
DROP TABLE @dbo@.CRS_batch_run;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = ''CRS_batch_run'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
CREATE TABLE @dbo@.CRS_batch_run (
|
|
CRS_batch_run_ID int NOT NULL,
|
|
CRSBR_batch_run int NOT NULL,
|
|
CRSBR_conversation_ID int NOT NULL,
|
|
CRSBR_source_reference varchar(255) NULL,
|
|
CRSBR_rowguid uniqueidentifier NOT NULL DEFAULT (newsequentialid()),
|
|
CRSBR_pharmacy_code char(6) NOT NULL DEFAULT (''@phcode@''),
|
|
CONSTRAINT PK_CRS_batch_run PRIMARY KEY NONCLUSTERED (CRS_batch_run_ID),
|
|
CONSTRAINT CK_CRS_batch_run_pharmacy_code CHECK ([CRSBR_pharmacy_code]=''@phcode@'')
|
|
);
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''CRS_document_header'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
DROP TABLE @dbo@.CRS_document_header;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = ''CRS_document_header'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
CREATE TABLE @dbo@.CRS_document_header (
|
|
CRS_document_header_ID int NOT NULL,
|
|
CRSDH_document_header int NOT NULL,
|
|
CRSDH_conversation_ID int NOT NULL,
|
|
CRSDH_source tinyint NOT NULL,
|
|
CRSDH_creation_date datetime2 NOT NULL,
|
|
CRSDH_source_reference varchar(255) NULL,
|
|
CRSDH_rowguid uniqueidentifier NOT NULL DEFAULT (newsequentialid()),
|
|
CRSDH_pharmacy_code char(6) NOT NULL DEFAULT (''@phcode@''),
|
|
CONSTRAINT PK_CRS_document_header PRIMARY KEY NONCLUSTERED (CRS_document_header_ID),
|
|
CONSTRAINT CK_CRS_document_header_pharmacy_code CHECK ([CRSDH_pharmacy_code]=''@phcode@'')
|
|
);
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''DH_Criteria'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
DROP TABLE @dbo@.DH_Criteria;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = ''DH_Criteria'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
CREATE TABLE @dbo@.DH_Criteria (
|
|
DH_criteria_ID int NOT NULL,
|
|
DHCR_document_header int NOT NULL,
|
|
DHCR_criteria int NOT NULL,
|
|
DHCR_remark varchar(255) NULL,
|
|
DHCR_TS timestamp NOT NULL,
|
|
DHCR_VGUID varchar(36) NULL DEFAULT (newid()),
|
|
DHCR_master_ID varchar(60) NULL,
|
|
DHCR_rowguid uniqueidentifier NOT NULL DEFAULT (newsequentialid()),
|
|
DHCR_pharmacy_code char(6) NOT NULL DEFAULT (''@phcode@''),
|
|
CONSTRAINT PK_DH_Criteria PRIMARY KEY NONCLUSTERED (DH_criteria_ID),
|
|
CONSTRAINT CK_DH_Criteria_pharmacy_code CHECK ([DHCR_pharmacy_code]=''@phcode@'')
|
|
);
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Document_date'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
DROP TABLE @dbo@.Document_date;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Document_date'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
CREATE TABLE @dbo@.Document_date (
|
|
Document_date_ID int NOT NULL,
|
|
DD_batch_run int NULL,
|
|
DD_document_header int NOT NULL,
|
|
DD_insurance_bill_to int NULL,
|
|
DD_date datetime NOT NULL,
|
|
DD_real_date datetime NULL,
|
|
DD_preparation_date datetime NULL,
|
|
DD_promised_date datetime NULL,
|
|
DD_element_requirement_date datetime NULL,
|
|
DD_amount decimal NULL,
|
|
DD_type smallint NOT NULL,
|
|
DD_label varchar(60) NULL,
|
|
DD_mandatory bit NOT NULL DEFAULT ((0)),
|
|
DD_percentage decimal NULL,
|
|
DD_reminder_level smallint NULL,
|
|
DD_delivery_type smallint NULL,
|
|
DD_tracking_number varchar(30) NULL,
|
|
DD_error_list varchar(255) NULL,
|
|
DD_remark varchar(max) NULL,
|
|
DD_VGUID varchar(36) NULL DEFAULT (newid()),
|
|
DD_master_ID varchar(60) NULL,
|
|
DD_TS timestamp NOT NULL,
|
|
DD_PH_insurance_plan uniqueidentifier NULL,
|
|
DD_rowguid uniqueidentifier NOT NULL DEFAULT (newsequentialid()),
|
|
DD_pharmacy_code char(6) NOT NULL DEFAULT (''@phcode@''),
|
|
CONSTRAINT PK_Document_date PRIMARY KEY NONCLUSTERED (Document_date_ID),
|
|
CONSTRAINT CK_Document_date_pharmacy_code CHECK ([DD_pharmacy_code]=''@phcode@'')
|
|
);
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Document_header'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
DROP TABLE @dbo@.Document_header;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = ''Document_header'' AND schema_id = SCHEMA_ID(''@dbo@''))
|
|
CREATE TABLE @dbo@.Document_header (
|
|
Document_header_ID int NOT NULL,
|
|
DH_subsidiary int NOT NULL,
|
|
DH_address_contact_person_list int NULL,
|
|
DH_VAT_period int NULL,
|
|
DH_language int NULL,
|
|
DH_default_task int NULL,
|
|
DH_invoicing_method int NULL,
|
|
DH_payment_method int NULL,
|
|
DH_source_financial_relation int NULL,
|
|
DH_financial_relation_account int NULL,
|
|
DH_shipping_method int NULL,
|
|
DH_work_order int NULL,
|
|
DH_journal int NULL,
|
|
DH_default_accounting_period int NULL,
|
|
DH_bmc_user_profile int NULL,
|
|
DH_currency int NULL,
|
|
DH_delivery_code int NULL,
|
|
DH_payment_terms int NULL,
|
|
DH_predefined_entry int NOT NULL,
|
|
DH_account int NULL,
|
|
DH_sold_to int NOT NULL,
|
|
DH_ship_to int NOT NULL,
|
|
DH_bill_to int NOT NULL,
|
|
DH_send_bill_to int NOT NULL,
|
|
DH_add_turnover_to int NOT NULL,
|
|
DH_freight_forwarder int NULL,
|
|
DH_def_PCEM int NULL,
|
|
DH_def_inventory_site int NULL,
|
|
DH_def_inventory_location int NULL,
|
|
DH_def_sales_representative int NULL,
|
|
DH_vehicle int NULL,
|
|
DH_time_period int NULL,
|
|
DH_printed_copies smallint NOT NULL DEFAULT (0),
|
|
DH_copies smallint NOT NULL DEFAULT (0),
|
|
DH_originals smallint NOT NULL DEFAULT (0),
|
|
DH_reminder_level smallint NOT NULL DEFAULT (0),
|
|
DH_label varchar(30) NULL,
|
|
DH_ship_to_text varchar(255) NULL,
|
|
DH_sold_to_text varchar(255) NULL,
|
|
DH_send_bill_to_text varchar(255) NULL,
|
|
DH_our_ref varchar(255) NULL,
|
|
DH_your_ref varchar(255) NULL,
|
|
DH_reference varchar(255) NULL,
|
|
DH_doc_number varchar(15) NULL,
|
|
DH_accounting_number int NULL,
|
|
DH_batch_number int NULL,
|
|
DH_archive_number varchar(60) NULL,
|
|
DH_accounting_status smallint NOT NULL DEFAULT (1),
|
|
DH_open_item bit NOT NULL DEFAULT (0),
|
|
DH_doc_date datetime NOT NULL,
|
|
DH_value_date datetime NOT NULL,
|
|
DH_input_date datetime NULL,
|
|
DH_delivery_date datetime NULL,
|
|
DH_due_date_basis datetime NULL,
|
|
DH_management_value_date datetime NULL,
|
|
DH_dealt_by varchar(60) NULL,
|
|
DH_remark varchar(max) NULL,
|
|
DH_version smallint NOT NULL DEFAULT (0),
|
|
DH_status smallint NOT NULL,
|
|
DH_state smallint NOT NULL DEFAULT (1),
|
|
DH_print_code smallint NOT NULL,
|
|
DH_tax_calculated bit NOT NULL DEFAULT (0),
|
|
DH_exchange_rate float NULL DEFAULT (1),
|
|
DH_BVR_reference varchar(30) NULL,
|
|
DH_BVR_original_line varchar(255) NULL,
|
|
DH_invoice_group varchar(30) NULL,
|
|
DH_TS timestamp NOT NULL,
|
|
DH_organizational_unit int NULL,
|
|
DH_IPI_reference varchar(60) NULL,
|
|
DH_bank_fees_settlement smallint NULL,
|
|
DH_cash_status smallint NULL,
|
|
DH_inventory_interface smallint NULL,
|
|
DH_accounting_interface smallint NULL,
|
|
DH_VGUID varchar(36) NULL DEFAULT (newid()),
|
|
DH_inventory_status smallint NULL,
|
|
DH_master_ID varchar(60) NULL,
|
|
DH_allocation_status smallint NULL,
|
|
DH_ALT_type smallint NULL,
|
|
DH_bonus_points decimal NULL,
|
|
DH_IOU_signature_status smallint NULL,
|
|
DH_IOU_signature_unlock_code varchar(255) NULL,
|
|
DH_external_doc_number varchar(30) NULL,
|
|
DH_VIP_card_barcode varchar(30) NULL,
|
|
DH_transitory_PERM int NULL,
|
|
DH_transitory_start_date datetime NULL,
|
|
DH_robot_batch_number varchar(60) NULL,
|
|
DH_prescription_control_status smallint NULL,
|
|
DH_prescription_control_status_date datetime NULL,
|
|
DH_multi_basket_GUID uniqueidentifier NULL,
|
|
DH_employee_card_number nvarchar(15) NULL,
|
|
DH_rowguid uniqueidentifier NOT NULL DEFAULT (newsequentialid()),
|
|
DH_pharmacy_code char(6) NOT NULL DEFAULT (''@phcode@''),
|
|
CONSTRAINT PK_Document_header PRIMARY KEY NONCLUSTERED (Document_header_ID),
|
|
CONSTRAINT CK_Document_header_pharmacy_code CHECK ([DH_pharmacy_code]=''@phcode@'')
|
|
);
|
|
|
|
';
|
|
|
|
DECLARE @trg TABLE(
|
|
id INT IDENTITY NOT NULL PRIMARY KEY,
|
|
phcode VARCHAR(6) NOT NULL,
|
|
sch VARCHAR(20) NOT NULL
|
|
);
|
|
|
|
--list of pharmacies and phcy_code to handle
|
|
INSERT INTO @trg (
|
|
[phcode],
|
|
[sch]
|
|
)
|
|
VALUES ('gam091','ama091_dbo')
|
|
,('gam251', 'ama251_dbo')
|
|
,('gam511', 'ama511_dbo')
|
|
;
|
|
|
|
DECLARE @phcode VARCHAR(6), @sch VARCHAR(20);
|
|
/* declare variables */
|
|
DECLARE @q NVARCHAR(MAX)
|
|
|
|
DECLARE csr_schema CURSOR FAST_FORWARD READ_ONLY FOR
|
|
SELECT [t].[phcode], [t].[sch]
|
|
FROM @trg AS [t];
|
|
|
|
OPEN csr_schema
|
|
|
|
FETCH NEXT FROM csr_schema INTO @phcode, @sch
|
|
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
SET @q = REPLACE(REPLACE(@tpl, '@dbo@', @sch), '@phcode@', @phcode)
|
|
|
|
--PRINT @q
|
|
EXEC [sys].[sp_executesql]
|
|
@q
|
|
,N''
|
|
;
|
|
FETCH NEXT FROM csr_schema INTO @phcode, @sch
|
|
END
|
|
|
|
CLOSE csr_schema
|
|
DEALLOCATE csr_schema |