create table smp_design_event(          -- done
id number(20) not null,         
stn_hrm_company_id number(20) not null,
event_name varchar2(50) not null, 
short_name varchar2(50) not null,   
parent_id number(20) 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_hrm_company_fk_15 FOREIGN KEY(stn_hrm_company_id) REFERENCES stn_hrm_company,
CONSTRAINT smp_design_event_pk PRIMARY KEY(id)
)

create table smp_design(             -- done
id number(20) not null,         
stn_hrm_company_id number(20) not null,
design_name varchar2(50) not null, 
design_no varchar2(50) not null, 
design_brif varchar2(100) not null,
technical_spec varchar2(200) not null,   
make_for varchar2(200) not null,
status varchar2(200) not null,  -- draft ,approved

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_16 FOREIGN KEY(stn_hrm_company_id) REFERENCES stn_hrm_company,
CONSTRAINT smp_design_pk PRIMARY KEY(id)
)

create table smp_design_desn_evnt(                 -- done
id number(20) not null, 
smp_design_id number(20) not null,        
smp_design_event_id number(20) not null,
sl_no number(2) not null,

CONSTRAINT smp_design_fk_1 FOREIGN KEY(smp_design_id) REFERENCES smp_design,
CONSTRAINT smp_design_event_fk_1 FOREIGN KEY(smp_design_event_id) REFERENCES smp_design_event,
CONSTRAINT smp_design_desn_evnt_pk PRIMARY KEY(id)
)

create table smp_design_byr_art(            -- done
id number(20) not null, 
smp_design_id number(20) not null,  
buyer_art_name varchar2(100) not null,      
buyer_art_doc_file_name varchar2(100) not null,
buyer_comment varchar2(100) not null,
CONSTRAINT smp_design_fk_3 FOREIGN KEY(smp_design_id) REFERENCES smp_design,
CONSTRAINT smp_design_byr_art_pk PRIMARY KEY(id)
)

create table smp_design_our_art(                -- done
id number(20) not null,                   -- multitle record
smp_design_id number(20) not null,  
our_art_name varchar2(100) not null,       
our_art_doc_file_name varchar2(100) not null,
our_comment varchar2(100) not null,
CONSTRAINT smp_design_fk_4 FOREIGN KEY(smp_design_id) REFERENCES smp_design,
CONSTRAINT smp_design_our_art_pk PRIMARY KEY(id)
)

create table smp_design_apv_log(            -- done
id number(20) not null, 
smp_design_id number(20) not null,        
buyer_comment varchar2(100) not null,
dgnap_stn_lookup_dtl_id number(20) not null,     -- design approved type 
smp_design_our_art_id number(20) not null,
CONSTRAINT smp_design_fk_5 FOREIGN KEY(smp_design_id) REFERENCES smp_design,
CONSTRAINT stn_lookup_dtl_fk_17 FOREIGN KEY(dgnap_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT smp_design_our_art_fk_2 FOREIGN KEY(smp_design_our_art_id) REFERENCES smp_design_our_art,
CONSTRAINT smp_design_apv_log_pk PRIMARY KEY(id)
)

create table smp_item_setup(                  -- done
id number(20) not null,         
stn_hrm_company_id number(20) not null,
stn_item_group_id number(20) not null,
stn_item_unit_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_18 FOREIGN KEY(stn_hrm_company_id) REFERENCES stn_hrm_company,
CONSTRAINT stn_item_group_fk_1 FOREIGN KEY(stn_item_group_id) REFERENCES stn_item_group,
CONSTRAINT stn_item_unit_fk_1 FOREIGN KEY(stn_item_unit_id) REFERENCES stn_item_unit,
CONSTRAINT smp_item_setup_pk PRIMARY KEY(id)
)

create table smp_item_setup_ts(             -- done
id number(20) not null,         
smp_item_setup_id number(20) not null,
technical_spec varchar2(500) not null,   --[ from sample ]
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 smp_item_setup_fk_1 FOREIGN KEY(smp_item_setup_id) REFERENCES smp_item_setup,
CONSTRAINT smp_item_setup_ts_pk PRIMARY KEY(id)
)

create table smp_our_mkt_mrc_team(              -- done
id number(20) not null,         
smp_item_setup_id number(20) not null,  -- from sample table 
marketing_type varchar2(50) not null, -- marketing,merchandiser
team_name varchar2(50) not null,  -- A,B,AA,BB
mkt_stn_hrm_employee_id number(20) 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 smp_item_setup_fk_66 FOREIGN KEY(smp_item_setup_id) REFERENCES smp_item_setup,
CONSTRAINT stn_hrm_employee_fk_66 FOREIGN KEY(mkt_stn_hrm_employee_id) REFERENCES stn_hrm_employee,
CONSTRAINT smp_our_mkt_mrc_team_pk PRIMARY KEY(id)
)

create table smp_our_mkt_mrc_tm_mb(          -- done
id number(20) not null,         
smp_item_setup_id number(20) not null,
smp_our_mkt_mrc_team_id number(20) not null,   --[from setup mkt_mrchen_team_member table]
mkt_stn_hrm_employee_id number(20) not null,
ac_head_no varchar2(50) default null,     -- come from accounting 

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


CONSTRAINT smp_item_setup_fk_12 FOREIGN KEY(smp_item_setup_id) REFERENCES smp_item_setup,
CONSTRAINT smp_our_mkt_mrc_team_fk_1 FOREIGN KEY(smp_our_mkt_mrc_team_id) REFERENCES smp_our_mkt_mrc_team,
CONSTRAINT mkt_hrm_employee_fk_2 FOREIGN KEY(mkt_stn_hrm_employee_id) REFERENCES stn_hrm_employee,
CONSTRAINT smp_our_mkt_mrc_tm_mb_pk PRIMARY KEY(id)
)

create table smp_item_our_mkt_mrc_usr(   -- done
id number(20) not null,
type varchar2(50) not null,   -- marketing, marchandiser         
smp_item_setup_id number(20) not null,
smp_our_mkt_mrc_tm_mb_id number(20) 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 smp_item_setup_fk_15 FOREIGN KEY(smp_item_setup_id) REFERENCES smp_item_setup,
CONSTRAINT smp_our_mkt_mrc_tm_mb_fk_1 FOREIGN KEY(smp_our_mkt_mrc_tm_mb_id) REFERENCES smp_our_mkt_mrc_tm_mb,
CONSTRAINT smp_item_our_mkt_mrc_usr_pk PRIMARY KEY(id)
)

create table smp_our_mkt_mrc_comsn_stn(                  -- done
id number(20) not null, 
type varchar2(20) not null,
stn_hrm_company_id number(20) default null,
smp_item_our_mkt_mrc_usr_id number(20) default null,
exchange_rate_bdt number(5,2) default null, 

is_active number(1) not null,     -- 0/1
create_date date default null,
update_date date default null,
create_by number(20) not null,
update_by number(20) not null,
      
CONSTRAINT smp_item_our_mkt_mrc_usr_fk_11 FOREIGN KEY(smp_item_our_mkt_mrc_usr_id) REFERENCES smp_item_our_mkt_mrc_usr,
CONSTRAINT stn_hrm_company_fk_11 FOREIGN KEY(stn_hrm_company_id) REFERENCES stn_hrm_company,
CONSTRAINT smp_our_mkt_mrc_comsn_stn_pk PRIMARY KEY(id)
)

create table smp_our_mkt_mrc_com_setdtl(              -- done
id number(20) not null,
smp_our_mkt_mrc_comsn_stn_id number(20) not null,
commission varchar2(50) not null,
minimum_amount number(2,2) default null,
from_date date default null,
to_date date default null,
CONSTRAINT smp_our_mkt_mrc_comsn_stn_fk12 FOREIGN KEY(smp_our_mkt_mrc_comsn_stn_id) REFERENCES smp_our_mkt_mrc_comsn_stn,
CONSTRAINT smp_our_mkt_mrc_com_setdtl_pk PRIMARY KEY(id)
)

create table smp_byr_mkt_mrc_comsn_stn(                  -- done
id number(20) not null, 
stn_party_id number(20) default null,
exchange_rate_bdt number(5,2) 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 stn_party_fk_13 FOREIGN KEY(stn_party_id) REFERENCES stn_party,
CONSTRAINT smp_byr_mkt_mrc_comsn_stn_pk PRIMARY KEY(id)
)

create table smp_byr_mkt_mrc_comsetdtl(              -- done
id number(20) not null,
smp_byr_mkt_mrc_comsn_stn_id number(20) not null,

buyer_comm number(2,2) not null,
mrc_comm number(2,2) not null,
mkt_comm number(2,2) not null,
from_date date not null,
to_date date not null,
CONSTRAINT smp_byr_mkt_mrc_comsn_stn_fk_1 FOREIGN KEY(smp_byr_mkt_mrc_comsn_stn_id) REFERENCES smp_byr_mkt_mrc_comsn_stn,
CONSTRAINT smp_byr_mkt_mrc_comsetdtl_pk PRIMARY KEY(id)
)

create table smp_item_attr_type_setn(            -- done
id number(20) not null,         
smp_item_setup_id number(20) not null,
attrtype_stn_lookup_id number(20) not null,   --[attrubute_type from setup lookup type table]

sl_no number(3) 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 smp_item_setup_fk_5 FOREIGN KEY(smp_item_setup_id) REFERENCES smp_item_setup,
CONSTRAINT stn_lookup_fk_15 FOREIGN KEY(attrtype_stn_lookup_id) REFERENCES stn_lookup,
CONSTRAINT smp_item_attr_type_setn_pk PRIMARY KEY(id)
)

create table smp_clr_sz_munt_punt_setn(            -- done
id number(20) not null,         
smp_item_setup_id number(20) not null,
is_color number(1) not null,
is_size number(1) not null,
is_munit number(1) not null,
is_punit number(1) 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 smp_item_setup_fk_21 FOREIGN KEY(smp_item_setup_id) REFERENCES smp_item_setup,
CONSTRAINT smp_clr_sz_munt_punt_setn_pk PRIMARY KEY(id)
)

create table smp_item_breakdown_setn(            -- done
id number(20) not null, 
smp_item_setup_id number(20) not null, 
field_name varchar2(30) not null,
field_type varchar2(30) not null,      -- static / foreigh
foreight_table varchar2(30) not null,  -- [ color,size,munit  from setup table]

sl_no number(2) not null,
is_active number(1) not null,          -- value: 0 or 1
CONSTRAINT smp_item_setup_fk_22 FOREIGN KEY(smp_item_setup_id) REFERENCES smp_item_setup,
CONSTRAINT smp_item_breakdown_setn_pk PRIMARY KEY(id)
)

create table smp_sample(             -- done
id number(20) not null, 
stn_hrm_company_id number(20) not null,                -- sbu
stn_apv_form_step_id number(20) not null,            -- [DRAFT,WIP,APPROVED from setting]
reqstg_stn_lookup_dtl_id number(20) not null,        -- reqstn_stage type lookup dtl    
reqslt_stn_lookup_dtl_id number(20) not null,        -- reqstn_slot type lookup dtl    
po_no varchar2(50) default null,
po_tp_stn_lookup_dtl_id number(20) default null,      -- po_type type lookup dtl 
pro_ty_stn_lookup_dtl_id number(20) default null,    -- product_type type lookup dtl 
sample_no_product_ref 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 stn_hrm_company_fk_22 FOREIGN KEY(stn_hrm_company_id) REFERENCES stn_hrm_company,
CONSTRAINT stn_apv_form_step_fk_1 FOREIGN KEY(stn_apv_form_step_id) REFERENCES stn_apv_form_step,
CONSTRAINT reqstg_stn_lookup_dtl_fk_22 FOREIGN KEY(reqstg_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT reqslt_lookup_dtl_fk_22 FOREIGN KEY(reqslt_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT po_tp_stn_lookup_dtl_fk_22 FOREIGN KEY(po_tp_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT pro_ty_stn_lookup_dtl_fk_22 FOREIGN KEY(pro_ty_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,

CONSTRAINT smp_sample_pk PRIMARY KEY(id)
)
 

	
create table smp_sample_party(  
id number(20) not null, 
stn_party_id number(20) not null,
stn_party_customer_id number(20) not null,
stn_party_contact_mkt_id number(20) not null,
stn_party_contact_mrc_id number(20) not null,
stn_party_delivery_port_id number(20) not null,
party_note varchar2(100) default null,
is_nomination varchar2(10) default null,

CONSTRAINT stn_party_fk_22 FOREIGN KEY(stn_party_id) REFERENCES stn_party,
CONSTRAINT stn_party_customer_fk_22 FOREIGN KEY(stn_party_customer_id) REFERENCES stn_party_customer,
CONSTRAINT stn_party_contact_mkt_fk_22 FOREIGN KEY(stn_party_contact_mkt_id) REFERENCES stn_party_contact_persn,
CONSTRAINT stn_party_contact_mrc_fk_22 FOREIGN KEY(stn_party_contact_mrc_id) REFERENCES stn_party_contact_persn,
CONSTRAINT stn_party_delivery_port_fk_22 FOREIGN KEY(stn_party_delivery_port_id) REFERENCES stn_party_delivery_port,

CONSTRAINT smp_sample_party_pk PRIMARY KEY(id)
)

create table smp_sample_3rd_pty_test(    -- done
id number(20) not null,  
smp_sample_id number(20) not null,    

third_part_test_name number(1) not null,
test_type varchar2(50) default null,
stn_party_id number(20) default null,
req_cert_stn_lookup_dtl_id number(20) default null,  -- req_certificate from lookup 
cert_std_stn_lookup_dtl_id number(20) default null,  -- certificate_std from lookup 
test_requirement varchar2(50) default null,
test_by varchar2(50) default null,
approx_test_price varchar2(50) default null,

CONSTRAINT smp_sample_fk_33 FOREIGN KEY(smp_sample_id) REFERENCES smp_sample,
CONSTRAINT stn_party_fk_33 FOREIGN KEY(stn_party_id) REFERENCES stn_party,
CONSTRAINT req_cert_stn_lookup_dtl_fk_33 FOREIGN KEY(req_cert_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT cert_std_stn_lookup_dtl_fk_33 FOREIGN KEY(cert_std_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT smp_sample_3rd_pty_test_pk PRIMARY KEY(id)
)

create table smp_sample_mail_attch(     -- done
id number(20) not null,  
smp_sample_id number(20) not null,
party_name varchar2(50) not null,     
mail_attach_link varchar2(100) default null,

CONSTRAINT smp_sample_fk_1 FOREIGN KEY(smp_sample_id) REFERENCES smp_sample,
CONSTRAINT smp_sample_mail_attch_pk PRIMARY KEY(id)
)

create table smp_sample_attr_type(          -- done
id number(20) not null,              
smp_sample_id number(20) default null,
attr_type_stn_lookup_id number(20) not null,  -- [attrubute_type from lookup ]

CONSTRAINT smp_sample_id_at_fk FOREIGN KEY(smp_sample_id) REFERENCES smp_sample,
CONSTRAINT attr_type_stn_lookup_fk_34 FOREIGN KEY(attr_type_stn_lookup_id) REFERENCES stn_lookup,
CONSTRAINT smp_sample_attr_type_pk PRIMARY KEY(id)
)

create table smp_sample_attr_val(            -- done
id number(20) not null, 
smp_sample_id number(20) not null,    
smp_sample_attr_type_id not null,             -- [from smp_sample_attribute_type]
attr_stn_lookup_dtl_id number(20) not null,   -- [attribute type from lookup_dtl]

CONSTRAINT smp_sample_fk_35 FOREIGN KEY(smp_sample_id) REFERENCES smp_sample,
CONSTRAINT smp_sample_attr_type_fk_35 FOREIGN KEY(smp_sample_attr_type_id) REFERENCES smp_sample_attr_type,
CONSTRAINT attr_stn_lookup_dtl_fk_35 FOREIGN KEY(attr_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT smp_sample_attr_val_pk PRIMARY KEY(id)
)

create table smp_sample_color(
id number(20) not null,              
smp_sample_id number(20) default null,
stn_color_id number(20) not null,  -- [from color]

CONSTRAINT smp_sample_fk_44 FOREIGN KEY(smp_sample_id) REFERENCES smp_sample,
CONSTRAINT stn_color_fk_44 FOREIGN KEY(stn_color_id) REFERENCES stn_color,
CONSTRAINT smp_sample_color_pk PRIMARY KEY(id)
)

create table smp_sample_size(            -- done
id number(20) not null,              
smp_sample_id number(20) default null,
stn_size_id number(20) not null,  -- [from size]

CONSTRAINT smp_sample_fk_45 FOREIGN KEY(smp_sample_id) REFERENCES smp_sample,
CONSTRAINT stn_size_fk_45 FOREIGN KEY(stn_size_id) REFERENCES stn_size,
CONSTRAINT smp_sample_size_pk PRIMARY KEY(id)
)

create table smp_sample_munit(           -- done
id number(20) not null,              
smp_sample_id number(20) default null,
stn_munit_id number(20) not null,  -- [from size]

CONSTRAINT smp_sample_fk_46 FOREIGN KEY(smp_sample_id) REFERENCES smp_sample,
CONSTRAINT stn_munit_fk_45 FOREIGN KEY(stn_munit_id) REFERENCES stn_munit,
CONSTRAINT smp_sample_munit_pk PRIMARY KEY(id)
)

create table smp_sample_punit(        -- done
id number(20) not null,              
smp_sample_id number(20) default null,
stn_punit_id number(20) not null,  -- [from punit]

CONSTRAINT smp_sample_fk_47 FOREIGN KEY(smp_sample_id) REFERENCES smp_sample,
CONSTRAINT stn_punit_fk_47 FOREIGN KEY(stn_punit_id) REFERENCES stn_punit,
CONSTRAINT smp_sample_punit_pk PRIMARY KEY(id)
)

create table smp_sample_breakdown(            -- done
id number(20) not null,
smp_item_setup_id number(20) not null, 
foreign_table varchar2(30) not null,     -- [color,size,munit,package.... tables from smp_item_breakdown_setn]
foreign_table_id varchar2(30) not null,  -- [color,size,munit,package.... tables from smp_item_breakdown_setn]

field_name varchar2(30) not null,
field_type varchar2(30) not null,         -- [static from smp_item_breakdown_setn]
field_value varchar2(30) not null,        -- [static from smp_item_breakdown_setn]
sl_no number(2) not null,
CONSTRAINT smp_item_setup_fk_48 FOREIGN KEY(smp_item_setup_id) REFERENCES smp_item_setup,
CONSTRAINT smp_sample_breakdown_pk PRIMARY KEY(id)

)

create table smp_sample_amd_cntrl(        -- done
id number(20) not null,
smp_sample_id number(20) not null,
req_type_stn_lookup_dtl_id number(20) not null,     -- reqstn_type from lookup new,addition,deduction,deletion
reqstn_code varchar2(50) not null,
sl_no number(2) not null,                          -- 1,2,3,4

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

CONSTRAINT smp_sample_fk_49 FOREIGN KEY(smp_sample_id) REFERENCES smp_sample,
CONSTRAINT req_type_stn_lookup_dtl_fk_49 FOREIGN KEY(req_type_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT smp_sample_amd_cntrl_pk PRIMARY KEY(id)
)
 

create table smp_sample_amd_record(                         -- done
smp_sample_amd_cntrl_id number(20) not null,       -- VVI

id number(20) not null, 
stn_hrm_company_id number(20) not null,                -- sbu
stn_apv_form_step_id number(20) not null,            -- [DRAFT,WIP,APPROVED from setting]
reqstg_stn_lookup_dtl_id number(20) not null,        -- reqstn_stage type lookup dtl    
reqslt_stn_lookup_dtl_id number(20) not null,        -- reqstn_slot type lookup dtl    
po_no varchar2(50) default null,
po_tp_stn_lookup_dtl_id number(20) default null,      -- po_type type lookup dtl 
pro_ty_stn_lookup_dtl_id number(20) default null,    -- product_type type lookup dtl 
sample_no_product_ref 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 stn_hrm_company_fk_50 FOREIGN KEY(stn_hrm_company_id) REFERENCES stn_hrm_company,
CONSTRAINT stn_apv_form_step_fk_50 FOREIGN KEY(stn_apv_form_step_id) REFERENCES stn_apv_form_step,
CONSTRAINT reqstg_stn_lookup_dtl_fk_50 FOREIGN KEY(reqstg_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT reqslt_lookup_dtl_fk_50 FOREIGN KEY(reqslt_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT po_tp_stn_lookup_dtl_fk_50 FOREIGN KEY(po_tp_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT pro_ty_stn_lookup_dtl_fk_50 FOREIGN KEY(pro_ty_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,

CONSTRAINT smp_sample_amd_record_pk PRIMARY KEY(id)
)
 

	
create table smp_sample_party_amd_record( 
smp_sample_amd_cntrl_id number(20) not null,       -- VVI 
id number(20) not null, 
stn_party_id number(20) not null,
stn_party_customer_id number(20) not null,
stn_party_contact_mkt_id number(20) not null,
stn_party_contact_mrc_id number(20) not null,
stn_party_delivery_port_id number(20) not null,
party_note varchar2(100) default null,
is_nomination varchar2(10) default null,

CONSTRAINT smp_sample_amd_cntrl_fk_22 FOREIGN KEY(smp_sample_amd_cntrl_id) REFERENCES smp_sample_amd_cntrl,
CONSTRAINT stn_party_fk_22 FOREIGN KEY(stn_party_id) REFERENCES stn_party,
CONSTRAINT stn_party_customer_fk_22 FOREIGN KEY(stn_party_customer_id) REFERENCES stn_party_customer,
CONSTRAINT stn_party_contact_mkt_fk_22 FOREIGN KEY(stn_party_contact_mkt_id) REFERENCES stn_party_contact_persn,
CONSTRAINT stn_party_contact_mrc_fk_22 FOREIGN KEY(stn_party_contact_mrc_id) REFERENCES stn_party_contact_persn,
CONSTRAINT stn_party_delivery_port_fk_22 FOREIGN KEY(stn_party_delivery_port_id) REFERENCES stn_party_delivery_port,

CONSTRAINT smp_sample_party_pk PRIMARY KEY(id)
)

create table smp_sample_3rdpty_amd_rcd(  -- done
smp_sample_amd_cntrl_id number(20) not null,       -- VVI

id number(20) not null,  
smp_sample_id number(20) not null,    

third_part_test_name number(1) not null,
test_type varchar2(50) default null,
stn_party_id number(20) default null,
req_cert_stn_lookup_dtl_id number(20) default null,  -- req_certificate from lookup 
cert_std_stn_lookup_dtl_id number(20) default null,  -- certificate_std from lookup 
test_requirement varchar2(50) default null,
test_by varchar2(50) default null,
approx_test_price varchar2(50) default null,

CONSTRAINT smp_sample_amd_cntrl_fk_51 FOREIGN KEY(smp_sample_amd_cntrl_id) REFERENCES smp_sample_amd_cntrl,
CONSTRAINT smp_sample_fk_51 FOREIGN KEY(smp_sample_id) REFERENCES smp_sample,
CONSTRAINT stn_party_fk_51 FOREIGN KEY(stn_party_id) REFERENCES stn_party,
CONSTRAINT req_cert_stn_lookup_dtl_fk_51 FOREIGN KEY(req_cert_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT cert_std_stn_lookup_dtl_fk_51 FOREIGN KEY(cert_std_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT smp_sample_3rdpty_amd_rcd_pk PRIMARY KEY(id)
)

create table smp_sample_mailatchamdrcd(             -- done
smp_sample_amd_cntrl_id number(20) not null, -- VVI
id number(20) not null,  
smp_sample_id number(20) not null,
party_name varchar2(50) not null     
mail_attach_link varchar2(100) default null,

CONSTRAINT smp_sample_amd_cntrl_fk_53 FOREIGN KEY(smp_sample_amd_cntrl_id) REFERENCES smp_sample_amd_cntrl,
CONSTRAINT smp_sample_fk_53 FOREIGN KEY(smp_sample_id) REFERENCES smp_sample,
CONSTRAINT smp_sample_mailatchamdrcd_pk PRIMARY KEY(id)
)

create table smp_sample_attrtypeamdrcd(               -- done
smp_sample_amd_cntrl_id number(20) not null,       -- VVI
id number(20) not null,              
smp_sample_id number(20) default null,
attr_type_stn_lookup_id number(20) not null,  -- [from smp_item_attr_type_setn]

CONSTRAINT smp_sample_amd_cntrl_fk_61 FOREIGN KEY(smp_sample_amd_cntrl_id) REFERENCES smp_sample_amd_cntrl,
CONSTRAINT smp_sample_fk_61 FOREIGN KEY(smp_sample_id) REFERENCES smp_sample,
CONSTRAINT attr_type_stn_lookup_fk_61 FOREIGN KEY(attr_type_stn_lookup_id) REFERENCES stn_lookup,
CONSTRAINT smp_sample_attrtypeamdrcd_pk PRIMARY KEY(id)
)

create table smp_sample_attrvalamdrcd(                   -- done
smp_sample_amd_cntrl_id number(20) not null,       -- VVI
id number(20) not null, 
smp_sample_id number(20) not null,    
smp_sample_attr_type_id not null,         -- [from smp_sample_attribute_type]
attr_stn_lookup_dtl_id number(20) not null,   -- [from lookup_dtl]

CONSTRAINT smp_sample_amd_cntrl_amd_fk_30 FOREIGN KEY(smp_sample_amd_cntrl_id) REFERENCES smp_sample_amd_cntrl,
CONSTRAINT smp_sample_fk_30 FOREIGN KEY(smp_sample_id) REFERENCES smp_sample,
CONSTRAINT smp_sample_attr_type_fk_30 FOREIGN KEY(smp_sample_attr_type_id) REFERENCES smp_sample_attr_type,
CONSTRAINT stn_lookup_dtl_fk_30 FOREIGN KEY(attr_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT smp_sample_attrvalamdrcd_pk PRIMARY KEY(id)
)

create table smp_sample_color_amd_rcd(            -- done
smp_sample_amd_cntrl_id number(20) not null,       -- VVI
id number(20) not null,              
smp_sample_id number(20) default null,
stn_color_id number(20) not null,  -- [from color]

CONSTRAINT smp_sample_amd_cntrl_fk_65 FOREIGN KEY(smp_sample_amd_cntrl_id) REFERENCES smp_sample_amd_cntrl,
CONSTRAINT smp_sample_fk_65 FOREIGN KEY(smp_sample_id) REFERENCES smp_sample,
CONSTRAINT stn_color_fk_65 FOREIGN KEY(stn_color_id) REFERENCES stn_color,
CONSTRAINT smp_sample_color_amd_rcd_pk PRIMARY KEY(id)
)

create table smp_sample_size_amd_record(                  -- done
smp_sample_amd_cntrl_id number(20) not null,       -- VVI
id number(20) not null,              
smp_sample_id number(20) default null,
stn_size_id number(20) not null,  -- [from size]

CONSTRAINT smp_sample_amd_cntrl_fk_66 FOREIGN KEY(smp_sample_amd_cntrl_id) REFERENCES smp_sample_amd_cntrl,
CONSTRAINT smp_sample_id_fk_66 FOREIGN KEY(smp_sample_id) REFERENCES smp_sample,
CONSTRAINT stn_size_fk_66 FOREIGN KEY(stn_size_id) REFERENCES stn_size,
CONSTRAINT smp_sample_size_amd_record_pk PRIMARY KEY(id)
)

create table smp_sample_munit_amd_rcd(             -- done
smp_sample_amd_cntrl_id number(20) not null,       -- VVI
id number(20) not null,              
smp_sample_id number(20) default null,
stn_munit_id number(20) not null,  -- [from size]

CONSTRAINT smp_sample_amd_cntrl_fk_67 FOREIGN KEY(smp_sample_amd_cntrl_id) REFERENCES smp_sample_amd_cntrl,
CONSTRAINT smp_sample_fk_67 FOREIGN KEY(smp_sample_id) REFERENCES smp_sample,
CONSTRAINT stn_munit_fk_67 FOREIGN KEY(stn_munit_id) REFERENCES stn_munit,
CONSTRAINT smp_sample_munit_amd_rcd_pk PRIMARY KEY(id)
)

create table smp_sample_punit_amd_rcd(            -- done
smp_sample_amd_cntrl_id number(20) not null,        -- VVI
id number(20) not null,              
smp_sample_id number(20) default null,
stn_punit_id number(20) not null,  -- [from punit]

CONSTRAINT smp_sample_amd_cntrl_fk_68 FOREIGN KEY(smp_sample_amd_cntrl_id) REFERENCES smp_sample_amd_cntrl,
CONSTRAINT smp_sample_fk_68 FOREIGN KEY(smp_sample_id) REFERENCES smp_sample,
CONSTRAINT stn_punit_fk_68 FOREIGN KEY(stn_punit_id) REFERENCES stn_punit,
CONSTRAINT smp_sample_punit_amd_rcd_pk PRIMARY KEY(id)
)

create table smp_sample_bkdown_amdrcd(                       -- done
smp_sample_amd_cntrl_id number(20) not null,       -- VVI
id number(20) not null,
smp_item_setup_id number(20) not null, 
foreign_table varchar2(30) not null,     -- [color,size,munit,package.... tables from smp_item_breakdown_setn]
foreign_table_id varchar2(30) not null,  -- [color,size,munit,package.... tables from smp_item_breakdown_setn]

field_name varchar2(30) not null,
field_type varchar2(30) not null,         -- [static from smp_item_breakdown_setn]
field_value varchar2(30) not null,        -- [static from smp_item_breakdown_setn]
sl_no number(2) not null,

CONSTRAINT smp_sample_amd_cntrl_fk_69 FOREIGN KEY(smp_sample_amd_cntrl_id) REFERENCES smp_sample_amd_cntrl,
CONSTRAINT smp_item_setup_fk_69 FOREIGN KEY(smp_item_setup_id) REFERENCES smp_item_setup,
CONSTRAINT smp_sample_bkdown_amdrcd_pk PRIMARY KEY(id)
)