CREATE TABLE accounts.organization_master (om_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, title VARCHAR(100) DEFAULT NULL, description TEXT DEFAULT NULL, parent_id BIGINT(20) UNSIGNED DEFAULT NULL, company INT(1) DEFAULT 0, accounting_division INT(1) DEFAULT NULL, lm_id BIGINT(20) UNSIGNED DEFAULT NULL, STATUS INT(1) DEFAULT NULL, inserted_by BIGINT(20) DEFAULT NULL, inserted_date DATETIME DEFAULT NULL, updated_by BIGINT(20) DEFAULT NULL, updated_date DATETIME DEFAULT NULL, PRIMARY KEY (om_id));



CREATE TABLE accounts.ledger_master (lm_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, ledgercode VARCHAR(20) DEFAULT NULL, acc_head VARCHAR(150) DEFAULT NULL, parent_id BIGINT(20) UNSIGNED DEFAULT NULL, om_id BIGINT(20) UNSIGNED DEFAULT NULL, cc INT(1) DEFAULT 0, opening_bal DOUBLE DEFAULT NULL, STATUS INT(1) DEFAULT NULL, inserted_by BIGINT(20) DEFAULT NULL, inserted_date DATETIME DEFAULT NULL, updated_by BIGINT(20) DEFAULT NULL, updated_date DATETIME DEFAULT NULL, PRIMARY KEY (lm_id), UNIQUE KEY acc_head (acc_head,om_id), FOREIGN KEY (om_id) REFERENCES accounts.organization_master (om_id));



INSERT INTO accounts.organization_master(title, description, parent_id,company,accounting_division,lm_id, STATUS) VALUES('ABC','ABC Group of Companies',0,0,0,NULL,1),('ABC-HQ','ABC Group of Companies - Head Quarters',1,0,0,NULL,1),('AET','ABC Educational Trust',0,0,0,NULL,1),('AIT','ABC Institute of Technology',3,0,0,NULL,1),('AVN School','ABC Vidhya Niketan School',3,0,0,NULL,1),('ABC-HO','ABC Head Office',3,0,0,NULL,1), ('AIT - Hostel and Mess','AIT Hostel and Mess',3,0,0,NULL,1); INSERT INTO accounts.organization_master(om_id,title, description, parent_id,company,accounting_division,lm_id, STATUS) VALUES(90,'Test','Test',0,0,0,NULL,1);



ALTER TABLE accounts.organization_master ADD FOREIGN KEY(lm_id) REFERENCES accounts.ledger_master(lm_id);



CREATE TABLE accounts.ledger_attribute_master (lam_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, attr_name VARCHAR(50) DEFAULT NULL, attr_type ENUM('text','select') DEFAULT NULL, value_type ENUM('amount','percentage','info') DEFAULT NULL, attr_order INT(11) DEFAULT NULL, status INT(11) DEFAULT NULL, inserted_by BIGINT(20) DEFAULT NULL, inserted_date DATETIME DEFAULT NULL, updated_by BIGINT(20) DEFAULT NULL, updated_date DATETIME DEFAULT NULL, PRIMARY KEY (lam_id));



INSERT INTO accounts.ledger_attribute_master (attr_name,attr_type, value_type,attr_order,rstatus) VALUES ('Address Line 1','text','info',1,1),('Address Line 2','text','info',2,1),('City','text','info',3,1),('State','text','info',4,1),('Pincode','text','info',5,1),('Contact Person','text','info',6,1),('Contact No.','text','info',7,1),('Email','text','info',8,1),('Name of the A/C Holder','text','info',9,1),('Bank Name','text','info',10,1),('Branch Name','text','info',11,1),('Bank A/C No.','text','info',12,1),('IFSCode','text','info',13,1),('GSTIN','text','info',14,1),('Max TDS','text','info',15,1),('Pan No','text','info',15,1),('Match Invoice Document','select','info',16,1),('TDS','select','percentage',17,1),('Staff_id','text','info',8,1);



CREATE TABLE accounts.ledger_attribute_values (lav_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, lm_id BIGINT(20) UNSIGNED DEFAULT NULL, lam_id BIGINT(20) UNSIGNED DEFAULT NULL, attr_value TEXT DEFAULT NULL, rstatus INT(1) DEFAULT 1, inserted_by BIGINT(20) DEFAULT NULL, inserted_date DATETIME DEFAULT NULL, updated_by BIGINT(20) DEFAULT NULL, updated_date DATETIME DEFAULT NULL, PRIMARY KEY (lav_id), UNIQUE KEY (lm_id,lam_id), FOREIGN KEY (lam_id) REFERENCES accounts.ledger_attribute_master (lam_id), FOREIGN KEY (lm_id) REFERENCES ledger_master (lm_id));



CREATE TABLE master_transaction_type ( mtt_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, tt_desc VARCHAR(40) DEFAULT NULL, trans_type ENUM('Debit','Credit','Both') DEFAULT NULL, rstatus INT(1) DEFAULT NULL, PRIMARY KEY (mtt_id));



INSERT INTO master_transaction_type(mtt_id,tt_desc,trans_type,rstatus) VALUES (1,'Cash Receipt','Debit',1),(2,'Cash Payment','Credit',1),(3,'Bank Receipt','Debit',1),(4,'Bank Payment','Credit',1),(5,'Debit Note','Both',1),(6,'Credit Note','Both',1),(7,'Journal Entries','Both',1),(8,'Fees Journal ','Both',1),(9,'Advance for Expenses Journal ','Both',1),(10,'General Journal ','Both',1);




Download Queries