erp

Database Table List

By ukmodak | April 1st 2024 12:11:14 PM | viewed 494 times





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)
)

 

Sl	Table Name:	Query
1-26	Setup Table(From Setup Module)	----
1	acc_fiscal_year	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) )
2	acc_currency	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) )
3	acc_country	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) )
4	hrm_company (from hrm modules)	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) )
5	acc_common_conf	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) )
6	acc_common_conf_dtl	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) )
7	acc_account_type	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) )
8	acc_account_group	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) )
9	acc_group_rltn	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) )
10	acc_leadger	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) )
11	acc_leadger_config_dtl	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) )
12	acc_group_config_dtl	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) )
13	acc_cost_center_setup	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) )
13	acc_cost_center	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) )
14	acc_store (From Warehouse Module)	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) )
15	acc_warehouse (From Warehouse Module)	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) )
16	acc_store_row_item_brkdn -- like order breakdown table	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) )
17	acc_wh_row_item_brkdn -- like order breakdown table	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) )
18	acc_store_fg_item_brkdn -- like order breakdown table	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) )
19	acc_wh_fg_item_brkdn -- like order breakdown table	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) )
20	acc_leadger_mailing	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) )
21	acc_bank_name	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) )
22	acc_bank_branch_name	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) )
23	acc_leadger_bank_ac	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) )
24	acc_leadger_bank_ac_dtl	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) )
25	acc_purc_sales_trans	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) )
26	acc_vat_tax_gst	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) )
27	acc_bill_ref	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) )
28	acc_purc_sales_invoice	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) )
29	acc_voucher	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) )
30	acc_voucher_dtl	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) )
31	acc_bank_reconci	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) )
32	acc_bank_reconci_dtl	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) )

Latest Tutorial