Others >> ERP >> Common Setup Table

Common Setup Table List

By ukmodak | April 1st 2024 12:11:14 PM | viewed 351 times
Sl Table Name Query example record
1 stn_lookup 
create table stn_lookup(         -- done
id number(20) not null,
name varchar2(100) not null,
type varchar2(100) not null, -- product_attribute,country,district,....
keyword varchar2(100) not null,
alias varchar2(50) default null,
is_parent number(20) default null,
is_active number(1) not null,  -- value: 0 or 1
html_type varchar2(10) not null,  -- value: ,text,combo,radio,checkbox
create_date date default null,
update_date date default null,
create_by number(20) not null,
update_by number(20) not null,
CONSTRAINT stn_lookup_pk PRIMARY KEY(id)
)
country,division,district,production type,customer type,Color Group,Size Group,Season,Year,style_no
2 stn_lookup_dtl 
create table stn_lookup_dtl(    
id number(20) not null,
stn_lookup_id number(20) not null,
parent_id number(20) not null,
value varchar2(100) not null,
alias varchar2(50) default null,
code varchar2(50) default null,
short_desc varchar2(50) default null,
long_desc varchar2(100) default null,
is_active number(1) 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 stn_lookup_fk_2 FOREIGN KEY(stn_lookup_id) REFERENCES stn_lookup, 
CONSTRAINT stn_lookup_dtl_fk_2 FOREIGN KEY(parent_id) REFERENCES stn_lookup_dtl, 
CONSTRAINT stn_lookup_dtl_pk PRIMARY KEY(id)
)
3 stn_color 
create table stn_color(      -- done
id number(20) not null,
sg_stn_lookup_dtl_id number(20) not null,   --[size group]
name varchar2(100) not null,
code varchar2(50) default null,
html_code varchar2(50) default null,
is_active number(1) 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 stn_lookup_dtl_fk_3 FOREIGN KEY(sg_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, 
CONSTRAINT stn_color_pk PRIMARY KEY(id)
)
4 stn_size 
create table stn_size(      -- done
id number(20) not null,
sg_stn_lookup_dtl_id number(20) not null,   --[size group ]
name varchar2(100) not null,
code varchar2(50) default null,
specification varchar2(50) default null,
is_active number(1) 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 stn_lookup_dtl_fk_4 FOREIGN KEY(sg_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, 
CONSTRAINT stn_size_pk PRIMARY KEY(id)
)
5 stn_munit 
create table stn_munit(             -- done
id number(20) not null,
name varchar2(100) not null,
code varchar2(50) default null,
specification varchar2(50) default 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_munit_pk PRIMARY KEY(id)
)
6 stn_punit 
create table stn_punit(          -- done
id number(20) not null,
name varchar2(100) not null,
code varchar2(50) default null,
symbol varchar2(50) default null,
specification varchar2(50) default null,
is_active number(1) 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 stn_punit_pk PRIMARY KEY(id)
)
7 stn_hrm_company (from hrm modules) 
create table stn_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 stn_hrm_company_pk PRIMARY KEY(id)
)
8 stn_package 
create table stn_package(           -- done
id number(20) not null,
stn_hrm_company_id number(20) not null,
pct_stn_lookup_dtl_id number(20) not null,  --[pack type : case crtn size, ration crtn size]
code varchar2(50) default 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_7 FOREIGN KEY(stn_hrm_company_id) REFERENCES stn_hrm_company,
CONSTRAINT stn_lookup_dtl_fk_7 FOREIGN KEY(pct_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT stn_package_pk PRIMARY KEY(id)
)
9 stn_munit_converter_dtl 
create table stn_munit_convrt_dtl(     -- done
id number(20) not null,
from_munit_id number(20) not null,
to_munit_id number(20) not null,
from_munit_equal_val number(3,2) 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_munit_fk_1 FOREIGN KEY(from_munit_id) REFERENCES stn_munit, 
CONSTRAINT stn_munit_fk_2 FOREIGN KEY(to_munit_id) REFERENCES stn_munit, 
CONSTRAINT stn_munit_convrt_dtl_pk PRIMARY KEY(id)
)
10 stn_party 
create table stn_party(            -- done
id number(20) not null,
pt_stn_lookup_dtl_id number(20) not null,     -- party type  
company_name varchar2(100) not null,
business_type varchar2(20) default null,
party_origin varchar2(100) default null,
office_address varchar2(100) default null,
business_start_date date default null,
eqos_code varchar2(50) default null,
ref_code varchar2(50) default null,
phone varchar2(50) default null,
mobile varchar2(50) default null,
email varchar2(50) default null,
ac_head_no varchar2(50) default null,     -- come from accounting 
website varchar2(50) default null,
yearly_turn_over varchar2(50) default null,
no_of_employee varchar2(50) default null,
no_of_worker varchar2(50) default null,
division_state varchar2(50) default null,
district_province varchar2(50) default null,
country varchar2(50) default null,

bin_vat_reg_no varchar2(50) default null,
tin_no varchar2(50) default null,
area_code varchar2(50) default 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_lookup_dtl_fk_9 FOREIGN KEY(pt_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, 
CONSTRAINT stn_party_pk PRIMARY KEY(id)
)
11 party_item_nomination 
create table stn_party_item_nom(    -- done
id number(20) not null,
stn_party_id number(20) not null,
smp_item_setup number(20) not null,
nom_sts_lookup_dtl_id number(20) not null,
value_per_year varchar2(100) not null,
buyer_commision varchar2(100) not null,
attach_file_path 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 stn_party_fk_10 FOREIGN KEY(stn_party_id) REFERENCES stn_party,   
CONSTRAINT stn_lookup_dtl_fk_10 FOREIGN KEY(nom_sts_lookup_dtl_id) REFERENCES stn_lookup_dtl, 
CONSTRAINT stn_party_item_nom_pk PRIMARY KEY(id)
)
12 stn_party_contact_persn (buyer marketing,marchandiser) 
create table stn_party_con_prsn(     -- done
id number(20) not null,
stn_party_id number(20) not null,
person_type varchar2(20) not null,  -- country_director,marketing,marchandiser
full_name varchar2(100) not null,
designation varchar2(20) default null,
department varchar2(20) default null,
phone varchar2(20) default null,
ac_head_no varchar2(50) default null,     -- come from accounting 
email varchar2(50) default null,
email_cc varchar2(50) default 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_party_id_cont_prsn_fk FOREIGN KEY(stn_party_id) REFERENCES stn_party, 
CONSTRAINT stn_party_con_prsn_pk PRIMARY KEY(id)
)
13 stn_party_bank_dtl 
create table stn_party_bank_dtl(          -- done
id number(20) not null,
stn_party_id number(20) not null,

bank_name varchar2(100) not null,
branch_name varchar2(100) not null,
account_no varchar2(100) not null,
swif_code varchar2(100) not null,
address 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 stn_party_fk_6 FOREIGN KEY(stn_party_id) REFERENCES stn_party,
CONSTRAINT stn_party_bank_dtl_pk PRIMARY KEY(id)
)
14 stn_party_customer 
create table stn_party_customer(          -- done
id number(20) not null,
stn_party_id number(20) not null,
customer_stn_party_id number(20) 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_party_fk_7 FOREIGN KEY(stn_party_id) REFERENCES stn_party, 
CONSTRAINT stn_party_fk_8 FOREIGN KEY(customer_stn_party_id) REFERENCES stn_party,
CONSTRAINT stn_party_customer_pk PRIMARY KEY(id)
)
15 stn_party_delivery_port 
create table stn_party_del_port(        -- done
id number(20) not null,
stn_party_id number(20) not null,
delivery_point varchar2(50) not null,
address varchar2(50) not null,
district_state varchar2(50) not null,
country 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_party_fk_9 FOREIGN KEY(stn_party_id) REFERENCES stn_party, 
CONSTRAINT stn_party_del_port_pk PRIMARY KEY(id)
)
16 stn_hrm_company_bank_dtl 
create table stn_hrm_com_bank_dtl(            -- done
id number(20) not null,
stn_hrm_company_id number(20) not null,
bank_name varchar2(100) not null,
branch_name varchar2(100) not null,
account_no varchar2(100) not null,
swif_code varchar2(100) not null,
address 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 stn_hrm_company_fk_1 FOREIGN KEY(stn_hrm_company_id) REFERENCES stn_hrm_company,
CONSTRAINT stn_hrm_com_bank_dtl_pk PRIMARY KEY(id)
)
17 stn_hrm_employee (from hrm modules) 
create table stn_hrm_employee(                  -- done
id number(20) not null,
full_name varchar2(100) not null,
designation varchar2(100) not null,
department varchar2(100) not null,
mobile_no varchar2(100) not null,
stn_hrm_company_id number(20) 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_3 FOREIGN KEY(stn_hrm_company_id) REFERENCES stn_hrm_company, 
CONSTRAINT stn_hrm_employee_pk PRIMARY KEY(id)
)
18 stn_role 
create table stn_role(                 -- done
id number(20) not null,
role_name 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 stn_role_pk PRIMARY KEY(id)
)
  1. user -- access own company,own unit as define
  2. admin --access own company,own unit as define
  3. supper_admin- access all
  4. marketing - access own customer/buyer
  5. marketing_head -- access own customer/buyer and own team member
  6. marchandiser - -- access own customer/buyer
  7. marchandiser_head -- access own customer/buyer and own team member
  8. party_admin -- access own party all
  9. party_user -- access own party as define
  10. production_technitian -- access own company own unit as define
  11. production_unit_head -- access own company own unit as define
  12. commercial -- access own company own unit as define
  13. account -- access own company own unit as define
19 stn_auth_user 
create table stn_auth_user(          -- done
id number(20) not null,
ut_stn_lookup_dtl_id number(20) not null,   --[user type]
stn_hrm_employee_id number(20) default null,
stn_party_id number(20) default null,
user_name varchar2(20) not null,
password varchar2(20) 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_lookup_dtl_fk_19 FOREIGN KEY(ut_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT stn_hrm_employee_fk_19 FOREIGN KEY(stn_hrm_employee_id) REFERENCES stn_hrm_employee,
CONSTRAINT stn_party_fk_19 FOREIGN KEY(stn_party_id) REFERENCES stn_party,
CONSTRAINT stn_auth_user_pk PRIMARY KEY(id)
)
    user_type
  1. hrm_employee
  2. hrm_marketing
  3. hrm_marchandiser
  4. buyer
  5. customer
  6. supplier
20 stn_menu 
create table stn_menu(                 -- done
id number(20) not null,
menu_type varchar2(50) not null,         -- main_menu,top_menu,left_menu
menu_title varchar2(50) not null,
label_type varchar2(50) not null,       ---  root,menu,tree
manu_icon varchar2(50) not null,       
path_url varchar2(50) not null,
internal_link number(1) not null,          -- -- value: 1 or 0     
parent_id number(20) default 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_menu_fk_1 FOREIGN KEY(parent_id) REFERENCES stn_menu,
CONSTRAINT stn_menu_pk PRIMARY KEY(id)
)
21 stn_menu_role_permission 
create table stn_menu_role_perm(   -- done
id number(20) not null,         
stn_role_id number(20) default null,
stn_menu_id number(20) default null,
is_delete number(1) not null,             -- value: 1 or 0
is_edit number(1) not null,               -- value: 1 or 0
is_insert number(1) not null,             -- value: 1 or 0
is_view number(1) not null,               -- value: 1 or 0

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

CONSTRAINT stn_role_fk_3 FOREIGN KEY(stn_role_id) REFERENCES stn_role,
CONSTRAINT stn_menu_fk_5 FOREIGN KEY(stn_menu_id) REFERENCES stn_menu,
CONSTRAINT stn_menu_role_perm_pk PRIMARY KEY(id)
)
22 stn_item_store 
create table stn_item_store(        -- done
id number(20) not null,         
store_name varchar2(50) not null,
alias varchar2(50) default null,
stn_hrm_company_id number(20) default 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_11 FOREIGN KEY(stn_hrm_company_id) REFERENCES stn_hrm_company,
CONSTRAINT stn_item_store_pk PRIMARY KEY(id)
)
23 stn_item_group 
create table stn_item_group(      -- done;
id number(20) not null,         
group_name varchar2(50) not null,
alias varchar2(50) default 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_item_group_pk PRIMARY KEY(id)
)
  • Raw Material
  • General
  • Engineering
  • Finish Goods
  • Unfinish Goods
  • Wastage
24 stn_item_unit 
create table stn_item_unit(              -- done
id number(20) not null,         
stn_item_group_id number(20) not null,
unit_name varchar2(50) not null,
alias varchar2(50) default null,
type varchar2(50)not null,                    -- root,menu,tree
parent_id number(20) default null,
sl_no number(10) 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_item_group_fk_2 FOREIGN KEY(stn_item_group_id) REFERENCES stn_item_group,
CONSTRAINT stn_item_unit_pk PRIMARY KEY(id)
)
    • Raw Material
      • ---
    • Chemical
      • ---
    • Packaging Item
      • ---
    • Production Consumable
      • ---
    • Plastic
      • ---
    • Metal
      • ---
    • Germents
      • ---
    • Carelael
      • ---
    • Transit Sticker
      • ---
    • Non RFID
      • ---
  • General
    • Packing Material
    • Office Stationary
    • Electric Goods
    • Office Supplier
    • Safety Equipment
    • IT Product
    • Parts and Accessories
    • Medicine
    • Advertisement
    • Transport
  • Engineering
    • Electrical
    • Civil
    • Machenical
  • Finish Goods
    • Promotional
25 stn_apv_flow_user_sts 
create table stn_apv_flow_usr_sts(    -- done
id number(20) not null,
stn_hrm_company_id number(20) not null,
flow_name varchar2(50) not null,
alias varchar2(50) not null, 
stn_auth_user_id number(20) not null, 
is_active number(1) not null, 
CONSTRAINT stn_hrm_company_fk_12 FOREIGN KEY(stn_hrm_company_id) REFERENCES stn_hrm_company,
CONSTRAINT stn_auth_user_fk_1 FOREIGN KEY(stn_auth_user_id) REFERENCES stn_auth_user,
CONSTRAINT stn_apv_flow_usr_sts_pk PRIMARY KEY(id)
)
26 stn_apv_form_step 
create table stn_apv_form_step(          -- done
id number(20) not null,
stn_hrm_company_id number(20) not null,
form_name varchar2(50) not null,
cur_stn_apv_flow_usr_sts_id number(20) not null, 
next_stn_apv_flow_usr_sts_id number(20) not null,
step_no number(4) not null,
is_active number(1) not null,  
CONSTRAINT stn_hrm_company_fk_13 FOREIGN KEY(stn_hrm_company_id) REFERENCES stn_hrm_company,
CONSTRAINT stn_apv_flow_usr_sts_fk_1 FOREIGN KEY(cur_stn_apv_flow_usr_sts_id) REFERENCES stn_apv_flow_usr_sts,
CONSTRAINT stn_apv_flow_usr_sts_fk_2 FOREIGN KEY(next_stn_apv_flow_usr_sts_id) REFERENCES stn_apv_flow_usr_sts,
CONSTRAINT stn_apv_form_step_pk PRIMARY KEY(id)
)
Latest Tutorial
Most Viewed Tutorial
bONEandALL
Visitor

Total : 20972

Today :26

Today Visit Country :

  • Germany
  • United States
  • Singapore
  • China
  • United Kingdom
  • South Korea
  • Czechia

Copyright 2018 by ukmodak. All Rights Reserved. | Powered by ukmodak | last update : April 7th 2024 07:11:52 AM