create table acc_fiscal_year(
id number(20) not null,
from_date date not null,
to_date date not null,
fiscal_year_from number(4) not null,
fiscal_year_to number(4) not null,

create_date date default null,
update_date date default null,
create_by number(20) not null,
update_by number(20) not null,


CONSTRAINT acc_fiscal_year_pk PRIMARY KEY(id)
)

 

create table acc_currency(

id number(20) not null,
currency_name varchar2(50) not null,
symbol_charecter varchar2(50) not null,

create_date date default null,
update_date date default null,
create_by number(20) not null,
update_by number(20) not null,

CONSTRAINT acc_currency_pk PRIMARY KEY(id)
)

 

create table acc_country(

id number(20) not null,
country_name varchar2(50) not null,
code varchar2(50) not null,
flag_img varchar2(50) default null,

create_date date default null,
update_date date default null,
create_by number(20) not null,
update_by number(20) not null,

CONSTRAINT acc_country_pk PRIMARY KEY(id)
)

 

create table hrm_company(       -- done
id number(20) not null,
name varchar2(100) not null,
alias varchar2(100) not null,
address varchar2(100) not null,
mobile varchar2(100) not null,
phone varchar2(100) not null,

is_active number(1) not null,  -- value: 0 or 1
create_date date default null,
update_date date default null,
create_by number(20) not null,
update_by number(20) not null,

CONSTRAINT hrm_company_pk PRIMARY KEY(id)
)

create table acc_common_conf(

id number(20) not null,
hrm_company_id number(20) not null, 
type_stn_lookup_dtl_id number(20) not null,  -- group/leadger
acc_type_id number(20) not null,  -- OE,L,A,E,R
conf_name varchar2(100) not null,

create_date date default null,
update_date date default null,
create_by number(20) not null,
update_by number(20) not null,

CONSTRAINT type_stn_lookup_dtl_81 FOREIGN KEY(type_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT acc_type_id_81 FOREIGN KEY(acc_type_id) REFERENCES acc_type,
CONSTRAINT acc_common_conf_pk PRIMARY KEY(id)
)

create table acc_common_conf_dtl(

id number(20) not null,
acc_common_conf_id number(20) not null,
conf_val_stn_lookup_dtl_id number(20) not null,
conf_name varchar2(100) not null,

create_date date default null,
update_date date default null,
create_by number(20) not null,
update_by number(20) not null,

CONSTRAINT acc_common_conf_fk_81 FOREIGN KEY(acc_common_conf_id) REFERENCES acc_common_conf,
CONSTRAINT conf_val_stn_lookup_dtl_81 FOREIGN KEY(conf_val_lookup_dtl_id) REFERENCES conf_val_stn_lookup_dtl,
CONSTRAINT acc_common_conf_dtl_pk PRIMARY KEY(id)
)

 

create table acc_account_type(
id number(20) not null,
account_type_name varchar2(100) not null,
account_no_from number(6) not null,
account_no_to number(6) not null,
normal_balance varchar2(10) not null,
short_name varchar2(10) not null,
sl_no int(1) not null,
CONSTRAINT acc_account_type_pk PRIMARY KEY(id)
)

 

create table acc_account_group(
id number(20) not null,
acc_fiscal_year_id number(20) not null,   -- composite primary key
hrm_company_id number(20) not null,       -- composite primary key

acc_account_type_id number(20) not null,
group_name varchar2(100) not null,
account_no varchar2(30) not null         -- 2001-001-002
account_short_name varchar2(20) not null,
sl_no int(10) not null,

label_type varchar2(50) not null,       ---  root,menu,tree
parent_id number(20) default null,
is_active number(1) not null,           -- value: 0 or 1

CONSTRAINT acc_fiscal_year_fk FOREIGN KEY(acc_fiscal_year_id) REFERENCES acc_fiscal_year,
CONSTRAINT hrm_company_id_fk FOREIGN KEY(hrm_company_id) REFERENCES hrm_company,
CONSTRAINT acc_account_type_id_fk FOREIGN KEY(acc_account_type_id) REFERENCES acc_account_type,
CONSTRAINT acc_account_group_pk PRIMARY KEY(id)
)

 

create table acc_group_rltn(
id number(20) not null,
bottom_id number(20) default null,
tree_id number(20) default null,
top_id number(20) not null,
CONSTRAINT acc_group_rltn_pk PRIMARY KEY(id)
)

 

create table acc_leadger(

id number(20) not null,
acc_fiscal_year_id number(20) not null,   -- composite primary key
hrm_company_id number(20) not null,       -- composite primary key
acc_currency_id number(20) not null,
acc_country_id number(20) not null,
acc_account_type_id number(20) not null,
acc_account_group_id number(20) not null,

leadger_name varchar2(100) not null,
account_no varchar2(30) not null         -- 2001-001-002
leadger_short_name varchar2(10) not null,
sl_no int(10) not null,

create_date date default null,
update_date date default null,
create_by number(20) not null,
update_by number(20) not null,

opening_balance  number(20,2) not null,
outstading_debit number(20,2) not null,
outstading_credit number(20,2) not null,

CONSTRAINT acc_fiscal_year_fk FOREIGN KEY(acc_fiscal_year_id) REFERENCES acc_fiscal_year,
CONSTRAINT hrm_company_fk FOREIGN KEY(hrm_company_id) REFERENCES hrm_company,
CONSTRAINT acc_currency_fk FOREIGN KEY(acc_currency_id) REFERENCES acc_currency,
CONSTRAINT acc_country_fk FOREIGN KEY(acc_country_id) REFERENCES acc_country,
CONSTRAINT acc_account_type_fk FOREIGN KEY(acc_account_type_id) REFERENCES acc_account_type,
CONSTRAINT acc_account_group_fk FOREIGN KEY(acc_account_group_id) REFERENCES acc_account_group,


CONSTRAINT acc_leadger_pk PRIMARY KEY(id)
)

 

create table acc_leadger_config_dtl(

id number(20) not null,
acc_leadger_id number(20) not null,
acc_common_conf_dtl_id number(20) not null,  -- mailing_address(common),is budget,is_cost_center,is_vat_tax_gst,is_bill,is_transsaction,is_bank_ac_dtl,is_row_intentory,is_finish_good,...

CONSTRAINT acc_leadger_fk FOREIGN KEY(acc_leadger_id) REFERENCES acc_leadger,
CONSTRAINT acc_common_conf_dtl_fk FOREIGN KEY(acc_common_conf_dtl_id) REFERENCES acc_common_conf_dtl,
CONSTRAINT acc_leadger_config_dtl_pk PRIMARY KEY(id)
)

 

create table acc_leadger_config_dtl(

id number(20) not null,
acc_group_id number(20) not null,
acc_common_conf_dtl_id number(20) not null,  -- is_show_as_sub_leadger,...

CONSTRAINT acc_group_fk FOREIGN KEY(acc_group_id) REFERENCES acc_group,
CONSTRAINT acc_common_conf_dtl_fk FOREIGN KEY(acc_common_conf_dtl_id) REFERENCES acc_common_conf_dtl,
CONSTRAINT acc_group_config_dtl_pk PRIMARY KEY(id)
)

 

create table acc_cost_center_setup(    
id number(20) not null,
stn_hrm_company_id number(20) not null,
cost_center_name varchar2(50) not null,
is_active number(1) not null,           -- value: 0 or 1

create_date date default null,
update_date date default null,
create_by number(20) not null,
update_by number(20) not null,

CONSTRAINT stn_hrm_company_fk FOREIGN KEY(stn_hr_company_id) REFERENCES stn_hrm_company,
CONSTRAINT acc_cost_center_setup_pk PRIMARY KEY(id)
)

 

create table acc_cost_center(    
id number(20) not null,
acc_leadger_id number(20) not null,
acc_cost_center_id number(20) not null,

CONSTRAINT acc_cost_center_fk FOREIGN KEY(acc_cost_center_id) REFERENCES acc_cost_center,
CONSTRAINT acc_leadger_fk FOREIGN KEY(acc_leadger_id) REFERENCES acc_leadger,
CONSTRAINT acc_cost_center_pk PRIMARY KEY(id)
)

 

create table acc_store(    
id number(20) not null,
stn_hrm_company_id number(20) not null,
store_name varchar2(50) not null,
is_active number(1) not null,           -- value: 0 or 1

create_date date default null,
update_date date default null,
create_by number(20) not null,
update_by number(20) not null,

CONSTRAINT stn_hrm_company_fk FOREIGN KEY(stn_hr_company_id) REFERENCES stn_hrm_company,
CONSTRAINT acc_store_pk PRIMARY KEY(id)
)

 

create table acc_warehouse(    
id number(20) not null,
stn_hrm_company_id number(20) not null,
store_name varchar2(50) not null,
is_active number(1) not null,           -- value: 0 or 1

create_date date default null,
update_date date default null,
create_by number(20) not null,
update_by number(20) not null,

CONSTRAINT stn_hrm_company_fk FOREIGN KEY(stn_hr_company_id) REFERENCES stn_hrm_company,
CONSTRAINT acc_warehouse_pk PRIMARY KEY(id)
)

 

create table acc_store_row_item_brkdn(    
id number(20) not null,
acc_leadger_id number(20) not null,
acc_store_id number(20) not null,
purchage_acc_leadger_id number(20) not null,
sales_acc_leadger_id number(20) not null,

item_name varchar2(30) not null, 
style_no varchar2(30) default null, 
color varchar2(30) default null,
size varchar2(30) default null,
qty number(10) default null,
rate varchar2(5) default null,
sl_no number(2) not null,

CONSTRAINT acc_leadger_id_fk_76 FOREIGN KEY(acc_leadger_id) REFERENCES acc_leadger,
CONSTRAINT acc_store_fk_76 FOREIGN KEY(acc_store_id) REFERENCES acc_store,
CONSTRAINT purchage_acc_leadger_fk_76 FOREIGN KEY(purchage_acc_leadger_id) REFERENCES acc_leadger,
CONSTRAINT sales_acc_leadger_fk_76 FOREIGN KEY(sales_acc_leadger_id) REFERENCES acc_leadger,

CONSTRAINT acc_store_row_item_brkdn_pk PRIMARY KEY(id)

)

create table acc_wh_row_item_brkdn(    
id number(20) not null,
acc_leadger_id number(20) not null,
acc_warehouse_id number(20) not null,
purchage_acc_leadger_id number(20) not null,
sales_acc_leadger_id number(20) not null,

item_name varchar2(30) not null, 
style_no varchar2(30) default null, 
color varchar2(30) default null,
size varchar2(30) default null,
qty number(10) default null,
rate varchar2(5) default null,
sl_no number(2) not null,

CONSTRAINT acc_leadger_id_fk_76 FOREIGN KEY(acc_leadger_id) REFERENCES acc_leadger,
CONSTRAINT acc_warehouse_fk_76 FOREIGN KEY(acc_warehouse_id) REFERENCES acc_warehouse,
CONSTRAINT purchage_acc_leadger_fk_76 FOREIGN KEY(purchage_acc_leadger_id) REFERENCES acc_leadger,
CONSTRAINT sales_acc_leadger_fk_76 FOREIGN KEY(sales_acc_leadger_id) REFERENCES acc_leadger,

CONSTRAINT acc_wh_row_item_brkdn_pk PRIMARY KEY(id)

)

create table acc_store_fg_item_brkdn(    
id number(20) not null,
acc_leadger_id number(20) not null,
acc_store_id number(20) not null,
purchage_acc_leadger_id number(20) not null,
sales_acc_leadger_id number(20) not null,

item_name varchar2(30) not null, 
style_no varchar2(30) default null, 
color varchar2(30) default null,
size varchar2(30) default null,
qty number(10) default null,
rate varchar2(5) default null,
sl_no number(2) not null,

CONSTRAINT acc_leadger_id_fk_76 FOREIGN KEY(acc_leadger_id) REFERENCES acc_leadger,
CONSTRAINT acc_store_fk_76 FOREIGN KEY(acc_store_id) REFERENCES acc_store,
CONSTRAINT purchage_acc_leadger_fk_76 FOREIGN KEY(purchage_acc_leadger_id) REFERENCES acc_leadger,
CONSTRAINT sales_acc_leadger_fk_76 FOREIGN KEY(sales_acc_leadger_id) REFERENCES acc_leadger,

CONSTRAINT acc_store_fg_item_brkdn_pk PRIMARY KEY(id)

)

create table acc_wh_fg_item_brkdn(    
id number(20) not null,
acc_leadger_id number(20) not null,
acc_warehouse_id number(20) not null,
purchage_acc_leadger_id number(20) not null,
sales_acc_leadger_id number(20) not null,

item_name varchar2(30) not null, 
style_no varchar2(30) default null, 
color varchar2(30) default null,
size varchar2(30) default null,
qty number(10) default null,
rate varchar2(5) default null,
sl_no number(2) not null,

CONSTRAINT acc_leadger_fk_76 FOREIGN KEY(acc_leadger_id) REFERENCES acc_leadger,
CONSTRAINT acc_warehouse_fk_76 FOREIGN KEY(acc_warehouse_id) REFERENCES acc_warehouse,
CONSTRAINT purchage_acc_leadger_fk_76 FOREIGN KEY(purchage_acc_leadger_id) REFERENCES acc_leadger,
CONSTRAINT sales_acc_leadger_fk_76 FOREIGN KEY(sales_acc_leadger_id) REFERENCES acc_leadger,

CONSTRAINT acc_wh_fg_item_brkdn_pk PRIMARY KEY(id)

)

create table acc_leadger_mailing(    
id number(20) not null,
acc_leadger_id number(20) not null,

name varchar2(100) default null,
address varchar2(100) default null,
country varchar2(100) default null,
state varchar2(100) default null,
pin varchar2(100) default null,
contact_person varchar2(100) default null,
telephone_no varchar2(100) default null,
mobile_no varchar2(100) default null,
fax_no varchar2(100) default null,
e-mail varchar2(100) default null,
cc varchar2(100) default null,
website varchar2(100) default null,

create_date date default null,
update_date date default null,
create_by number(20) not null,
update_by number(20) not null,

CONSTRAINT acc_leadger_fk FOREIGN KEY(acc_leadger_id) REFERENCES acc_leadger,
CONSTRAINT acc_leadger_mailing_pk PRIMARY KEY(id)
)

 

create table acc_bank_name(    
id number(20) not null,
bank_name varchar2(100) default null,
create_date date default null,
update_date date default null,
create_by number(20) not null,
update_by number(20) not null,

CONSTRAINT acc_bank_name_pk PRIMARY KEY(id)
)

 

create table acc_bank_branch_name(    
id number(20) not null,
branch_name varchar2(100) default null,
create_date date default null,
update_date date default null,
create_by number(20) not null,
update_by number(20) not null,

CONSTRAINT acc_bank_branch_name_pk PRIMARY KEY(id)
)

 

create table acc_leadger_bank_ac(    
id number(20) not null,
acc_leadger_id number(20) not null,
acc_bank_name_id number(20) not null,
acc_bank_branch_name_id number(20) not null,


ac_name varchar2(100) default null,
ac_no varchar2(100) default null,
address varchar2(100) default null,
manager_name varchar2(100) default null,
manager_phone varchar2(100) default null,

create_date date default null,
update_date date default null,
create_by number(20) not null,
update_by number(20) not null,

CONSTRAINT acc_leadger_fk FOREIGN KEY(acc_leadger_id) REFERENCES acc_leadger,
CONSTRAINT acc_bank_name_fk FOREIGN KEY(acc_bank_name_id) REFERENCES acc_bank_name,
CONSTRAINT acc_bank_branch_name_fk FOREIGN KEY(acc_bank_branch_name_id) REFERENCES acc_bank_branch_name,
CONSTRAINT acc_leadger_bank_ac_pk PRIMARY KEY(id)
)

 

create table acc_leadger_bank_ac_dtl(    
id number(20) not null,
acc_leadger_bank_ac_id number(20) not null,

check_no_from number(30) default null,
check_no_to number(30) default null,
no_of_check number(30) default null,

create_date date default null,
update_date date default null,
create_by number(20) not null,
update_by number(20) not null,

CONSTRAINT acc_leadger_bank_ac_fk FOREIGN KEY(acc_leadger_bank_ac_id) REFERENCES acc_leadger_bank_ac,
CONSTRAINT acc_leadger_bank_ac_dtl_pk PRIMARY KEY(id)
)

 

create table acc_purc_sales_trans(    
id number(20) not null,
acc_leadger_id number(20) not null,

nickname varchar2(50) default null,
favourite_name varchar2(50) default null,
trans_type_lookup_dtl_id  number(20) default null,
default number(1) default null,

create_date date default null,
update_date date default null,
create_by number(20) not null,
update_by number(20) not null,

CONSTRAINT acc_leadger_fk FOREIGN KEY(acc_leadger_id) REFERENCES acc_leadger,
CONSTRAINT trans_type_lookup_dtl_fk FOREIGN KEY(trans_type_lookup_dtl_id) REFERENCES lookup_dtl,
CONSTRAINT acc_purc_sales_trans_pk PRIMARY KEY(id)
)

 

create table acc_vat_tax_gst(    
id number(20) not null,
acc_leadger_id number(20) not null,
v_t_g_type_lookup_dtl_id number(20) not null,
rate number(2,2) not null,
calculate_from_date date default null,

create_date date default null,
update_date date default null,
create_by number(20) not null,
update_by number(20) not null,

CONSTRAINT acc_leadger_fk FOREIGN KEY(acc_leadger_id) REFERENCES acc_leadger,
CONSTRAINT v_t_g_type_lookup_dtl_fk FOREIGN KEY(v_t_g_type_lookup_dtl_id) REFERENCES lookup_dtl,

CONSTRAINT acc_vat_tax_gst_pk PRIMARY KEY(id)
)

 

create table acc_bill_ref(    
id number(20) not null,
acc_leadger_id number(20) not null,
bill_type_lookup_dtl_id number(20) not null,
bill_no number(2,2) not null,
amount number(20,2) not null,
total_amount number(20,2) not null,
dr_cr varchar2(10) default null,
due_date date default null,

create_date date default null,
update_date date default null,
create_by number(20) not null,
update_by number(20) not null,

CONSTRAINT acc_leadger_fk FOREIGN KEY(acc_leadger_id) REFERENCES acc_leadger,
CONSTRAINT bill_type_lookup_dtl_fk FOREIGN KEY(bill_type_lookup_dtl) REFERENCES lookup_dtl,

CONSTRAINT acc_bill_ref_pk PRIMARY KEY(id)
)

 

create table acc_purc_sales_invoice(    
id number(20) not null,
acc_leadger_id number(20) not null,
voice_type_lookup_dtl_id number(20) not null,

invoice_no  varchar2(30) not null,
invoice_date  date not null,

supplier_name varchar2(50) default null,
supplier_address varchar2(50) default null,
supplier_phone varchar2(50) default null,
supplier_mobile varchar2(50) default null,
supplier_country varchar2(50) default null,


buyer_name varchar2(50) default null,
buyer_address varchar2(50) default null,
buyer_phone varchar2(50) default null,
buyer_mobile varchar2(50) default null,
buyer_country varchar2(50) default null,

mathod_of_dispatch varchar2(50) default null,
mathod_of_payment varchar2(50) default null,
type_of_shipment varchar2(50) default null,
port_of_landing varchar2(50) default null,
port_of_discharge varchar2(50) default null,
consignment_total varchar2(50) default null,
delivery_date date default null,

create_date date default null,
update_date date default null,
create_by number(20) not null,
update_by number(20) not null,

CONSTRAINT acc_leadger_fk FOREIGN KEY(acc_leadger_id) REFERENCES acc_leadger,
CONSTRAINT voice_type_lookup_dtl_fk FOREIGN KEY(bill_type_lookup_dtl) REFERENCES lookup_dtl,

CONSTRAINT acc_purc_sales_invoice_pk PRIMARY KEY(id)
)

create table acc_voucher(

id number(20) not null,
acc_fiscal_year_id number(20) not null,
stn_hrm_company_id number(20) not null,
voucher_type varchar2(50) not null,       --- receive,payment,purchase,debit_note,sales,sales_note,contra,journal
voucher_no number(50) not null,           --- 1,2,3 (RV-1,PV-1,PR-1,DNV-1,SV-1,CNV-1,CV-1,JV-1)
trans_date date not null,
month_name varchar(15) default null,
year varchar2(4) default null,

acc_leadger_id number(20) not null,
total_amount number(20,2) not null,
dr_cr varchar(15) default null,



naration varchar2(100) default null,

create_date date default null,
update_date date default null,
create_by number(20) not null,
update_by number(20) not null,

CONSTRAINT acc_voucher_pk PRIMARY KEY(id)
)

 

create table acc_voucher_dtl(
id number(20) not null,
acc_voucher_id number(20) not null, 

acc_leadger_id number(20) not null,
amount number(20,2) not null,
dr_cr varchar2(15) default null,

CONSTRAINT acc_voucher_dtl_pk PRIMARY KEY(id)
)

 

create table acc_bank_reconci(
id number(20) not null,
acc_voucher_id number(20) not null, --- receive,payment,contra,journal
acc_leadger_id number(20) not null,
   
bank_trans_date date default null,

CONSTRAINT acc_voucher_pk PRIMARY KEY(id)
)

 

create table acc_bank_reconci_dtl(
id number(20) not null,
acc_voucher_id number(20) not null, --- receive,payment,contra,journal
acc_voucher_dtl_id number(20) not null,
acc_leadger_id number(20) not null,
   
bank_trans_date date default null,

CONSTRAINT acc_bank_reconci_dtl_pk PRIMARY KEY(id)
)