

CREATE TABLE abo_card_conditions(
	tenant_no int NOT NULL,
	abo_card_condition_cd varchar(50) NOT NULL,
	expiry varchar(30),
	all_products SMALLINT,
	all_services SMALLINT,
	period_time varchar(50),
	rebate_percent decimal(5, 2),
	max_amount int,
	PRIMARY KEY (tenant_no, abo_card_condition_cd)
);


CREATE TABLE abo_card_condition_items(
	tenant_no int NOT NULL,
	abo_card_condition_cd varchar(50) NOT NULL,
	item_company_no int NOT NULL,
	item_department_no int NOT NULL,
	item_cd varchar(50) NOT NULL,
	item_mapping_group_cd varchar(50),
	rebate_percent decimal(5, 2),
	special_price decimal(5, 2),
	commission_price decimal(5, 2),
	
	PRIMARY KEY (tenant_no, abo_card_condition_cd,item_company_no,item_department_no,item_cd)

);


ALTER TABLE abo_card_condition_items  ADD  CONSTRAINT fk_abo_card_condition_items_dp FOREIGN KEY(tenant_no, abo_card_condition_cd)
REFERENCES abo_card_conditions (tenant_no, abo_card_condition_cd);


ALTER TABLE market_conditions ADD abo_card_condition_cd varchar(50) ;

ALTER TABLE market_conditions ADD  CONSTRAINT fk_market_conditions_acc FOREIGN KEY(tenant_no, abo_card_condition_cd)
REFERENCES abo_card_conditions (tenant_no, abo_card_condition_cd);

ALTER TABLE businessunit_conditions ADD abo_card_condition_cd varchar(50) ;

ALTER TABLE businessunit_conditions ADD  CONSTRAINT fk_businessunit_conditions_acc FOREIGN KEY(tenant_no, abo_card_condition_cd)
REFERENCES abo_card_conditions (tenant_no, abo_card_condition_cd);


ALTER TABLE sales_inv_positions ADD 	customer_contract_no int ;
ALTER TABLE sales_inv_positions ADD 	cc_condition_valid_from TIMESTAMP ;
ALTER TABLE sales_inv_positions ADD 	cc_condition_card_no varchar(30) ;
ALTER TABLE sales_inv_positions ADD 	cc_condition_card_nm varchar(100) ;
ALTER TABLE sales_inv_positions ADD 	cc_condition_cd varchar(10) ;
ALTER TABLE sales_inv_positions ADD 	cc_card_uid varchar(36) ;
ALTER TABLE sales_inv_positions ADD 	cc_used smallint ;
ALTER TABLE sales_inv_positions ADD 	item_mapping_group_cd varchar(50) ;
ALTER TABLE sales_inv_positions ADD 	a_sales_pricelist_id int ;
ALTER TABLE sales_inv_positions ADD 	v_customer_contract_no int ;
ALTER TABLE sales_inv_positions ADD 	v_condition_valid_from TIMESTAMP ;
ALTER TABLE sales_inv_positions ADD 	v_condition_valid_to TIMESTAMP ;
ALTER TABLE sales_inv_positions ADD 	v_condition_card_no varchar(30) ;
ALTER TABLE sales_inv_positions ADD 	v_card_uid varchar(36) ;
ALTER TABLE sales_inv_positions ADD 	v_condition_cd varchar(10) ;
ALTER TABLE sales_inv_positions ADD 	cc_special_price decimal(20, 3) ;
ALTER TABLE sales_inv_positions ADD 	cc_rebate_percent decimal(20, 3) ;


ALTER TABLE d_sales_inv_positions ADD 	customer_contract_no int ;
ALTER TABLE d_sales_inv_positions ADD 	cc_condition_valid_from TIMESTAMP ;
ALTER TABLE d_sales_inv_positions ADD 	cc_condition_card_no varchar(30) ;
ALTER TABLE d_sales_inv_positions ADD 	cc_condition_card_nm varchar(100) ;
ALTER TABLE d_sales_inv_positions ADD 	cc_condition_cd varchar(10) ;
ALTER TABLE d_sales_inv_positions ADD 	cc_card_uid varchar(36) ;
ALTER TABLE d_sales_inv_positions ADD 	cc_used smallint ;
ALTER TABLE d_sales_inv_positions ADD 	item_mapping_group_cd varchar(50) ;
ALTER TABLE d_sales_inv_positions ADD 	a_sales_pricelist_id int ;
ALTER TABLE d_sales_inv_positions ADD 	v_customer_contract_no int ;
ALTER TABLE d_sales_inv_positions ADD 	v_condition_valid_from TIMESTAMP ;
ALTER TABLE d_sales_inv_positions ADD 	v_condition_valid_to TIMESTAMP ;
ALTER TABLE d_sales_inv_positions ADD 	v_condition_card_no varchar(30) ;
ALTER TABLE d_sales_inv_positions ADD 	v_card_uid varchar(36) ;
ALTER TABLE d_sales_inv_positions ADD 	v_condition_cd varchar(10) ;
ALTER TABLE d_sales_inv_positions ADD 	cc_special_price decimal(20, 3) ;
ALTER TABLE d_sales_inv_positions ADD 	cc_rebate_percent decimal(20, 3) ;


CREATE TABLE pos_tse ( 
	tenant_no INTEGER NOT NULL, 
	pos_cd VARCHAR(10) NOT NULL, 	 
	public_key VARCHAR(300) NOT NULL,
	serial_number VARCHAR(100),
	certificate VARCHAR(3000), 
	certificate_expiration_date TIMESTAMP, 
	version VARCHAR(30), 
	time_format VARCHAR(30), 
	signature_algorithm VARCHAR(30), 
	client_id VARCHAR(30), 
	type VARCHAR(30),
	PRIMARY KEY (tenant_no, pos_cd, public_key)
);


ALTER TABLE pos_tse  ADD  CONSTRAINT fk_pos_tse_pos FOREIGN KEY(tenant_no, pos_cd)
REFERENCES points_of_sale (tenant_no, pos_cd);
