erp

Commercial Table List

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




create table cmc_po(
id number(20) not null,
code varchar2(50) not null,
stn_hrm_company_id number(20) not null,
po_cat_lookup_dtl_id number(20) not null, -- import,export
po_no varchar2(50) default null,
invoice_no varchar2(20) not null,
po_date_issue_date date not null, 
delivery_date date not null,
due_date date not null,
po_file_name varchar2(20) default null,

is_lock 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_hrm_company_fk_81 FOREIGN KEY(stn_hrm_company_id) REFERENCES stn_hrm_company,
CONSTRAINT stn_lookup_dtl_fk_81 FOREIGN KEY(pi_cat_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT cmc_po_pk PRIMARY KEY(id)
)
 


create table cmc_po_buyer(
id number(20) not null,
cmc_po_id number(20) not null, 
stn_party_id number(20) not null,

isu_stn_party_bank_dtl_id number(20) not null,

negotiating_lion_bank varchar2(50) default null,
negotiating_lion_bank_branch varchar2(50) default null,
stn_party_con_prsn_id number(20) not null,
buy_stn_party_customer_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 cmc_po_fk_82 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po,
CONSTRAINT stn_party_fk_82 FOREIGN KEY(stn_party_id) REFERENCES stn_party,
CONSTRAINT stn_party_bank_dtl_fk_82 FOREIGN KEY(isu_stn_party_bank_dtl_id) REFERENCES stn_party_bank_dtl,
CONSTRAINT stn_party_con_prsn_fk_82 FOREIGN KEY(stn_party_con_prsn_id) REFERENCES stn_party_con_prsn,
CONSTRAINT stn_party_customer_fk_82 FOREIGN KEY(buy_stn_party_customer_id) REFERENCES stn_party_customer,
CONSTRAINT cmc_po_buyer_pk PRIMARY KEY(id)
)
 


create table cmc_po_seller(
id number(20) not null,
cmc_po_id number(20) not null,

stn_party_id number(20) not null,
adv_stn_party_bank_dtl_id number(20) not null,

forwading_bank varchar2(50) default null,
orwading_bank_branch varchar2(50) default null,
noty_stn_party_customer_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 cmc_po_83 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po,
CONSTRAINT stn_party_fk_83 FOREIGN KEY(stn_party_id) REFERENCES stn_party,
CONSTRAINT stn_party_bank_dtl_fk_83 FOREIGN KEY(adv_stn_party_bank_dtl_id) REFERENCES stn_party_bank_dtl,
CONSTRAINT stn_party_customer_fk_83 FOREIGN KEY(noty_stn_party_customer_id) REFERENCES stn_party_customer,
CONSTRAINT cmc_po_seller_pk PRIMARY KEY(id)
)
 


create table cmc_po_shipment(
id number(20) not null,
cmc_po_id number(20) not null,

partial_shipment int(1) not null, -- 0/1
transhipment int(1) not null, -- 0/1
ty_of_ship_stn_lookup_dtl_id number(20) not null,
del_term_stn_lookup_dtl_id number(20) not null,
 
port_of_loading varchar2(20) not null,
port_of_dischare varchar2(20) not null,

place_of_receipt varchar2(20) not null,
place_of_distination varchar2(20) not null,

ten_dl_term_stn_lookup_dtl_id number(20) not null,

CONSTRAINT cmc_po_fk85 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po,
CONSTRAINT stn_lookup_dtl_id_fk_85 FOREIGN KEY(ty_of_ship_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT stn_lookup_dtl_fk_85 FOREIGN KEY(del_term_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT stn_lookup_dtl_id_id_fk_85 FOREIGN KEY(ten_dl_term_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT cmc_po_shipment_pk PRIMARY KEY(id)
)
 

create table cmc_po_items(
id number(20) not null,
cmc_po_id number(20) not null,
style_no_stn_lookup_dtl_id number(20) default null,
stn_color_id number(20) default null,
stn_size_id number(20) default null,
prod_desc varchar2(20) not null,
qty number(5) not null,
stn_munit_id number(5) not null,
stn_punit_id number(5) not null,
rate number(5,2) not null,
amount number(5,2) not null,
case_crtn_stn_package_id number(20) not null,
case_qty number(5,2) default null,
ration_crtn_stn_package_id number(20) not null,
ration_qty number(5,2) default null,

CONSTRAINT cmc_po_fk_11 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po,
CONSTRAINT stn_lookup_dtl_fk_11 FOREIGN KEY(style_no_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT stn_color_fk_11 FOREIGN KEY(stn_color_id) REFERENCES stn_color,
CONSTRAINT stn_size_fk_11 FOREIGN KEY(stn_size_id) REFERENCES stn_size,
CONSTRAINT stn_munit_fk_11 FOREIGN KEY(stn_munit_id) REFERENCES stn_munit,
CONSTRAINT stn_punit_fk_11 FOREIGN KEY(stn_punit_id) REFERENCES stn_punit,
CONSTRAINT stn_package_fk_11 FOREIGN KEY(case_crtn_stn_package_id) REFERENCES stn_package,
CONSTRAINT stn_package_fk_11 FOREIGN KEY(ration_crtn_stn_package_id) REFERENCES stn_package,
CONSTRAINT cmc_po_items_pk PRIMARY KEY(id)
)
 

create table cmc_po_terms(
id number(20) not null,
cmc_po_id number(20) not null,
sl number(2) not null,
code varchar2(20) not null,
term_and_cond varchar2(100) not null,

CONSTRAINT cmc_po_fk_87 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po,
CONSTRAINT cmc_po_terms_pk PRIMARY KEY(id)
)
 


create table cmc_po_revs_control(        -- done
id number(20) not null,
cmc_po_id number(20) not null,
req_type_stn_lookup_dtl_id number(20) not null,     -- reqstn_type from lookup new,revs
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 cmc_po_fk_101 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po,
CONSTRAINT stn_lookup_dtl_fk_101 FOREIGN KEY(req_type_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT cmc_po_revs_control_pk PRIMARY KEY(id)
)
 


create table cmc_po_revs_recs(
cmc_po_revs_control_id number(20) not null,       -- VVI

id number(20) not null,
code varchar2(50) not null,
stn_hrm_company_id number(20) not null,
pi_cat_stn_lookup_dtl_id number(20) not null, -- import,export
po_no varchar2(50) default null,
invoice_no varchar2(20) not null,
po_date_issue_date date not null, 
delivery_date date not null,
due_date date not null,
po_file_name varchar2(20) default null,
is_lock 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 cmc_po_revs_control_fk_102 FOREIGN KEY(cmc_po_revs_control_id) REFERENCES cmc_po_revs_control,
CONSTRAINT stn_hrm_company_fk_102 FOREIGN KEY(stn_hrm_company_id) REFERENCES stn_hrm_company,
CONSTRAINT stn_lookup_dtl_fk_102 FOREIGN KEY(pi_cat_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT cmc_po_revs_recs_pk PRIMARY KEY(id)
)
 


create table cmc_po_buyer_revs_recs(
cmc_po_revs_control_id number(20) not null,       -- VVI


id number(20) not null,
cmc_po_id number(20) not null, 
stn_party_id number(20) not null,

isu_stn_party_bank_dtl_id number(20) not null,

negotiating_lion_bank varchar2(50) default null,
negotiating_lion_bank_branch varchar2(50) default null,
stn_party_con_prsn_id number(20) not null,
buy_stn_party_customer_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 cmc_po_revs_control_fk_103 FOREIGN KEY(cmc_po_revs_control_id) REFERENCES cmc_po_revs_control,
CONSTRAINT cmc_po_id_fk_103 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po,
CONSTRAINT stn_party_fk_103 FOREIGN KEY(stn_party_id) REFERENCES stn_party,
CONSTRAINT stn_party_bank_dtl_fk_103 FOREIGN KEY(isu_stn_party_bank_dtl_id) REFERENCES stn_party_bank_dtl,
CONSTRAINT stn_party_con_prsn_fk_103 FOREIGN KEY(stn_party_con_prsn_id) REFERENCES stn_party_con_prsn,
CONSTRAINT stn_party_customer_id_fk_103 FOREIGN KEY(buy_stn_party_customer_id) REFERENCES stn_party_customer,
CONSTRAINT cmc_po_buyer_revs_recs_pk PRIMARY KEY(id)
)
 


create table cmc_po_seller_revs_recs(
cmc_po_revs_control_id number(20) not null,       -- VVI

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

stn_party_id number(20) not null,
adv_stn_party_bank_dtl_id number(20) not null,

forwading_bank varchar2(50) default null,
orwading_bank_branch varchar2(50) default null,
noty_stn_party_customer_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 cmc_po_revs_control_fk_88 FOREIGN KEY(cmc_po_revs_control_id) REFERENCES cmc_po_revs_control,
CONSTRAINT cmc_po_fk_88 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po,
CONSTRAINT stn_party_fk_88 FOREIGN KEY(stn_party_id) REFERENCES stn_party,
CONSTRAINT stn_party_bank_dtl_fk_88 FOREIGN KEY(adv_stn_party_bank_dtl_id) REFERENCES stn_party_bank_dtl,
CONSTRAINT stn_party_customer_fk_88 FOREIGN KEY(noty_stn_party_customer_id) REFERENCES stn_party_customer,
CONSTRAINT cmc_po_seller_revs_recs_pk PRIMARY KEY(id)
)
 


create table cmc_po_shipment_revs_recs(
cmc_po_revs_control_id number(20) not null,       -- VVI

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

partial_shipment number(1) not null, -- 0/1
transhipment number(1) not null, -- 0/1
ty_of_ship_stn_lookup_dtl_id number(20) not null,
del_term_stn_lookup_dtl_id number(20) not null,
 
port_of_loading varchar2(20) not null,
port_of_dischare varchar2(20) not null,

place_of_receipt varchar2(20) not null,
place_of_distination varchar2(20) not null,

ten_dl_term_stn_lookup_dtl_id number(20) not null,

CONSTRAINT cmc_po_revs_con_fk_104 FOREIGN KEY(cmc_po_revs_control_id) REFERENCES cmc_po_revs_control,
CONSTRAINT cmc_po_fk_104 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po,
CONSTRAINT stn_lookup_dtl_fk_104 FOREIGN KEY(ty_of_ship_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT stn_lookup_dtl_fk_114 FOREIGN KEY(del_term_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT stn_lookup_dtl_fk_124 FOREIGN KEY(ten_dl_term_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT cmc_po_shipment_revs_rcd_pk PRIMARY KEY(id)
)
 

create table cmc_po_items_revs_recs(
cmc_po_revs_control_id number(20) not null,       -- VVI

id number(20) not null,
cmc_po_id number(20) not null,
style_no_stn_lookup_dtl_id number(20) default null,
stn_color_id number(20) default null,
stn_size_id number(20) default null,
prod_desc varchar2(20) not null,
qty number(5) not null,
stn_munit_id number(5) not null,
stn_punit_id number(5) not null,
rate number(5,2) not null,
amount number(5,2) not null,
case_crtn_stn_package_id number(20) not null,
case_qty number(5,2) default null,
ration_crtn_stn_package_id number(20) not null,
ration_qty number(5,2) default null,

CONSTRAINT cmc_po_revs_control_fk_105 FOREIGN KEY(cmc_po_revs_control_id) REFERENCES cmc_po_revs_control,
CONSTRAINT cmc_po_fk_105 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po,
CONSTRAINT stn_lookup_dtl_fk_105 FOREIGN KEY(style_no_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT stn_color_fk_105 FOREIGN KEY(stn_color_id) REFERENCES stn_color,
CONSTRAINT stn_size_fk_105 FOREIGN KEY(stn_size_id) REFERENCES stn_size,
CONSTRAINT stn_munit_fk_105 FOREIGN KEY(stn_munit_id) REFERENCES stn_munit,
CONSTRAINT stn_punit_fk_105 FOREIGN KEY(stn_punit_id) REFERENCES stn_punit,
CONSTRAINT stn_package_fk_105 FOREIGN KEY(case_crtn_stn_package_id) REFERENCES stn_package,
CONSTRAINT stn_package_fk_115 FOREIGN KEY(ration_crtn_stn_package_id) REFERENCES stn_package,
CONSTRAINT cmc_po_items_revs_recs_pk PRIMARY KEY(id)
)
 

create table cmc_po_terms_revs_recs(
cmc_po_revs_control_id number(20) not null,       -- VVI

id number(20) not null,
cmc_po_id number(20) not null,
sl number(2) not null,
code varchar2(20) not null,
term_and_cond varchar2(100) not null,

CONSTRAINT cmc_po_revs_control_fk_106 FOREIGN KEY(cmc_po_revs_control_id) REFERENCES cmc_po_revs_control,
CONSTRAINT cmc_po_fk_106 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po,
CONSTRAINT cmc_po_terms_revs_recs_pk PRIMARY KEY(id)
)
 


create table cmc_pi(
id number(20) not null,
cmc_po_id number(20) not null,
code varchar2(50) not null,
stn_hrm_company_id number(20) not null,
pi_cat_stn_lookup_dtl_id number(20) not null, -- import,export
invoice_no varchar2(20) not null,
po_date_issue_date date not null, 
delivery_date date not null,
due_date date not null,

is_lock 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 cmc_po_fk_107 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po,
CONSTRAINT stn_hrm_company_fk_107 FOREIGN KEY(stn_hrm_company_id) REFERENCES stn_hrm_company,
CONSTRAINT stn_lookup_dtl_fk_107 FOREIGN KEY(pi_cat_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT cmc_pi_pk PRIMARY KEY(id)
)
 


create table cmc_pi_buyer(
id number(20) not null,
cmc_pi_id number(20) not null, 
cmc_po_buyer_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 cmc_pi_fk_108 FOREIGN KEY(cmc_pi_id) REFERENCES cmc_pi,
CONSTRAINT cmc_po_buyer_fk_108 FOREIGN KEY(cmc_po_buyer_id) REFERENCES cmc_po_buyer,
CONSTRAINT cmc_pi_buyer_pk PRIMARY KEY(id)
)
 


create table cmc_pi_seller(
id number(20) not null,
cmc_pi_id number(20) not null,
cmc_po_seller_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 cmc_pi_fk_109 FOREIGN KEY(cmc_pi_id) REFERENCES cmc_pi,
CONSTRAINT cmc_po_seller_fk_109 FOREIGN KEY(cmc_po_seller_id) REFERENCES cmc_po_seller,
CONSTRAINT cmc_pi_seller_pk PRIMARY KEY(id)
)
 


create table cmc_pi_shipment(
id number(20) not null,
cmc_pi_id number(20) not null,

partial_shipment number(1) not null, -- 0/1
transhipment number(1) not null, -- 0/1
ty_of_ship_stn_lookup_dtl_id number(20) not null,
del_term_stn_lookup_dtl_id number(20) not null,
 
port_of_loading varchar2(20) not null,
port_of_dischare varchar2(20) not null,

place_of_receipt varchar2(20) not null,
place_of_distination varchar2(20) not null,

ten_dl_term_stn_lookup_dtl_id number(20) not null,

CONSTRAINT cmc_po_fk_100 FOREIGN KEY(cmc_pi_id) REFERENCES cmc_po,
CONSTRAINT stn_lookup_dtl_fk_110 FOREIGN KEY(ty_of_ship_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT stn_lookup_dtl_fk_120 FOREIGN KEY(del_term_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT stn_lookup_dtl_fk_130 FOREIGN KEY(ten_dl_term_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT cmc_pi_shipment_pk PRIMARY KEY(id)
)
 

create table cmc_pi_items(
id number(20) not null,
cmc_pi_id number(20) not null,
batch_lot_no number(2) default null,
style_no_stn_lookup_dtl_id number(20) default null,
stn_color_id number(20) default null,
stn_size_id number(20) default null,
prod_desc varchar2(20) not null,
qty number(5) not null,
stn_munit_id number(20) not null,
stn_punit_id number(20) not null,
rate number(5,2) not null,
amount number(5,2) not null,
case_crtn_stn_package_id number(20) not null,
case_qty number(5,2) default null,
ration_crtn_stn_package_id number(20) not null,
ration_qty number(5,2) default null,

CONSTRAINT cmc_pi_fk_131 FOREIGN KEY(cmc_pi_id) REFERENCES cmc_po,
CONSTRAINT stn_lookup_dtl_fk_131 FOREIGN KEY(style_no_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT stn_color_fk_131 FOREIGN KEY(stn_color_id) REFERENCES stn_color,
CONSTRAINT stn_size_fk_131 FOREIGN KEY(stn_size_id) REFERENCES stn_size,
CONSTRAINT stn_munit_fk_131 FOREIGN KEY(stn_munit_id) REFERENCES stn_munit,
CONSTRAINT stn_punit_fk_131 FOREIGN KEY(stn_punit_id) REFERENCES stn_punit,
CONSTRAINT stn_package_fk_131 FOREIGN KEY(case_crtn_stn_package_id) REFERENCES stn_package,
CONSTRAINT stn_package_fk_141 FOREIGN KEY(ration_crtn_stn_package_id) REFERENCES stn_package,
CONSTRAINT cmc_pi_items_pk PRIMARY KEY(id)
)
 

create table cmc_pi_terms(
id number(20) not null,
cmc_pi_id number(20) not null,
sl number(2) not null,
code varchar2(20) not null,
term_and_cond varchar2(100) not null,

CONSTRAINT cmc_pi_fk_91 FOREIGN KEY(cmc_pi_id) REFERENCES cmc_pi,
CONSTRAINT cmc_pi_terms_pk PRIMARY KEY(id)
)
 


create table cmc_pi_revs_control(        -- done
id number(20) not null,
cmc_pi_id number(20) not null,
req_type_stn_lookup_dtl_id number(20) not null,     -- reqstn_type from lookup new,revs
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 cmc_pi_fk102 FOREIGN KEY(cmc_pi_id) REFERENCES cmc_pi,
CONSTRAINT stn_lookup_dtl_fk102 FOREIGN KEY(req_type_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT cmc_pi_revs_control_pk PRIMARY KEY(id)
)
 


create table cmc_pi_revs_recs(
cmc_pi_revs_control_id number(20) not null,       -- VVI

id number(20) not null,
cmc_po_id number(20) not null,
code varchar2(50) not null,
stn_hrm_company_id number(20) not null,
pi_cat_stn_lookup_dtl_id number(20) not null, -- import,export
invoice_no varchar2(20) not null,
po_date_issue_date date not null, 
delivery_date date not null,
due_date date not null,

is_lock 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 cmc_pi_revs_control_fk105 FOREIGN KEY(cmc_pi_revs_control_id) REFERENCES cmc_pi_revs_control,
CONSTRAINT stn_hrm_company_fk105 FOREIGN KEY(stn_hrm_company_id) REFERENCES stn_hrm_company,
CONSTRAINT stn_lookup_dtl_fk105 FOREIGN KEY(pi_cat_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT cmc_pi_revs_recs_pk PRIMARY KEY(id)
)
 


create table cmc_pi_buyer_revs_recs(
cmc_pi_revs_control_id number(20) not null,       -- VVI

id number(20) not null,
cmc_pi_id number(20) not null, 
cmc_po_buyer_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 cmc_pi_revs_control_fk106 FOREIGN KEY(cmc_pi_revs_control_id) REFERENCES cmc_pi_revs_control,
CONSTRAINT cmc_po_id_fk106 FOREIGN KEY(cmc_pi_id) REFERENCES cmc_po,
CONSTRAINT cmc_po_buyer_fk106 FOREIGN KEY(cmc_po_buyer_id) REFERENCES cmc_po_buyer,
CONSTRAINT cmc_pi_buyer_revs_recs_pk PRIMARY KEY(id)
)
 


create table cmc_pi_seller_revs_recs(
cmc_pi_revs_control_id number(20) not null,       -- VVI

id number(20) not null,
cmc_pi_id number(20) not null,
cmc_po_seller_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 cmc_pi_revs_control_fk107 FOREIGN KEY(cmc_pi_revs_control_id) REFERENCES cmc_pi_revs_control,
CONSTRAINT cmc_po_fk107 FOREIGN KEY(cmc_pi_id) REFERENCES cmc_pi,
CONSTRAINT cmc_po_seller_fk107 FOREIGN KEY(cmc_po_seller_id) REFERENCES cmc_po_seller,
CONSTRAINT cmc_pi_seller_revs_recs_pk PRIMARY KEY(id)
)
 


create table cmc_pi_shipment_revs_recs(
cmc_pi_revs_control_id number(20) not null,       -- VVI
id number(20) not null,
cmc_pi_id number(20) not null,

partial_shipment number(1) not null, -- 0/1
transhipment number(1) not null, -- 0/1
ty_of_ship_stn_lookup_dtl_id number(20) not null,
del_term_stn_lookup_dtl_id number(20) not null,
 
port_of_loading varchar2(20) not null,
port_of_dischare varchar2(20) not null,

place_of_receipt varchar2(20) not null,
place_of_distination varchar2(20) not null,

ten_dl_term_stn_lookup_dtl_id number(20) not null,


CONSTRAINT cmc_pi_revs_control_fk108 FOREIGN KEY(cmc_pi_revs_control_id) REFERENCES cmc_pi_revs_control,
CONSTRAINT cmc_pi_fk108 FOREIGN KEY(cmc_pi_id) REFERENCES cmc_pi,
CONSTRAINT stn_lookup_dtl_fk108 FOREIGN KEY(ty_of_ship_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT stn_lookup_dtl_fk148 FOREIGN KEY(del_term_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT stn_lookup_dtl_fk158 FOREIGN KEY(ten_dl_term_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT cmc_pi_shipment_revs_rcd_pk PRIMARY KEY(id)
)
 

create table cmc_pi_items_revs_recs(
cmc_pi_revs_control_id number(20) not null,       -- VVI
id number(20) not null,
cmc_pi_id number(20) not null,
batch_lot_no number(2) default null,
style_no_stn_lookup_dtl_id number(20) default null,
stn_color_id number(20) default null,
stn_size_id number(20) default null,
prod_desc varchar2(20) not null,
qty number(5) not null,
stn_munit_id number(5) not null,
stn_punit_id number(5) not null,
rate number(5,2) not null,
amount number(5,2) not null,
case_crtn_stn_package_id number(20) not null,
case_qty number(5,2) default null,
ration_crtn_stn_package_id number(20) not null,
ration_qty number(5,2) default null,

CONSTRAINT cmc_pi_revs_control_fk109 FOREIGN KEY(cmc_pi_revs_control_id) REFERENCES cmc_pi_revs_control,
CONSTRAINT cmc_pi_fk109 FOREIGN KEY(cmc_pi_id) REFERENCES cmc_pi,
CONSTRAINT stn_lookup_dtl_fk109 FOREIGN KEY(style_no_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT stn_color_fk109 FOREIGN KEY(stn_color_id) REFERENCES stn_color,
CONSTRAINT stn_size_fk109 FOREIGN KEY(stn_size_id) REFERENCES stn_size,
CONSTRAINT stn_munit_fk109 FOREIGN KEY(stn_munit_id) REFERENCES stn_munit,
CONSTRAINT stn_punit_fk109 FOREIGN KEY(stn_punit_id) REFERENCES stn_punit,
CONSTRAINT stn_package_fk169 FOREIGN KEY(case_crtn_stn_package_id) REFERENCES stn_package,
CONSTRAINT stn_package_fk109 FOREIGN KEY(ration_crtn_stn_package_id) REFERENCES stn_package,
CONSTRAINT cmc_pi_items_revs_recs_pk PRIMARY KEY(id)
)
 

create table cmc_pi_terms_revs_recs(
cmc_pi_revs_control_id number(20) not null,       -- VVI

id number(20) not null,
cmc_pi_id number(20) not null,
sl number(2) not null,
code varchar2(20) not null,
term_and_cond varchar2(100) not null,

CONSTRAINT cmc_pi_revs_control_fk110 FOREIGN KEY(cmc_pi_revs_control_id) REFERENCES cmc_pi_revs_control,
CONSTRAINT cmc_pi_fk110 FOREIGN KEY(cmc_pi_id) REFERENCES cmc_pi,
CONSTRAINT cmc_pi_terms_revs_recs_pk PRIMARY KEY(id)
)
 

create table cmc_lc(
id number(20) not null,
stn_hrm_company_id number(20) not null,
code varchar2(50) not null,
lc_for varchar2(50) not null,                   -- import,export
cur_stn_apv_flow_step_id number(20) not null,
next_stn_apv_flow_step_id number(20) not null,
lc_type_stn_lookup_dtl_id number(20) not null,
doc_type_stn_lookup_dtl_id number(20) not null, 
lc_sc_no varchar2(50) not null,
lc_open_date date not null,
lc_expire_date date not null,
sales_contact_no varchar2(50) not null,
sales_contact_date date not null,
bank_ref_no varchar2(50) not null,
adv_by_stn_lookup_dtl_id number(20) not null,
advised_date date not null, 
in_cnt_of_stn_lookup_dtl_id number(20) not null,
amount_tolerance_plus_perc number(2,2) not null,
amount_tolerance_minus_perc number(2,2) not null,  

CONSTRAINT stn_hrm_company_id_fk FOREIGH KEY(stn_hrm_company_id) REFERENCES stn_hrm_company,
CONSTRAINT cur_stn_apv_flow_step_fk FOREIGH KEY(cur_stn_apv_flow_step_id) REFERENCES stn_apv_flow_step,
CONSTRAINT next_stn_apv_flow_step_fk FOREIGH KEY(next_stn_apv_flow_step_id) REFERENCES stn_apv_flow_step,
CONSTRAINT lc_type_stn_lookup_dtl_fk FOREIGH KEY(lc_type_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT doc_type_stn_lookup_dtl_fk FOREIGH KEY(doc_type_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT adv_by_stn_lookup_dtl_fk FOREIGH KEY(adv_by_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT in_cnt_of_stn_lookup_dtl_fk FOREIGH KEY(in_cnt_of_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT cmc_lc_pk PRIMARY KEY(id)
)
 

create table cmc_lc_btb(
id number(20) not null,
cmc_lc_id number(20) not null,
btb_lc_type_lookup_dtl_id number(20) not null,
export_lc_id number(20) not null,

CONSTRAINT cmc_lc_fk FOREIGH KEY(cmc_lc_id) REFERENCES cmc_lc,
CONSTRAINT btb_lc_type_lookup_dtl_fk FOREIGH KEY(btb_lc_type_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT export_lc_fk FOREIGH KEY(export_lc_id) REFERENCES cmc_lc,
CONSTRAINT cmc_lc_btb_pk PRIMARY KEY(id)
)
 

create table cmc_lc_other(
id number(20) not null,
cmc_lc_id number(20) not null,
bd_bank_ref_no varchar2(50) default null, 
bd_bank_circular_no varchar2(50) default null, 
application_date date default null,
auth_signature_file_name varchar2(50) default null, 

CONSTRAINT cmc_lc_fk FOREIGH KEY(cmc_lc_id) REFERENCES cmc_lc,
CONSTRAINT cmc_lc_other_pk PRIMARY KEY(id)
)
 

create table cmc_lc_pi(
id number(20) not null,
cmc_lc_id number(20) not null,
cmc_pi_id number(20) not null,

CONSTRAINT cmc_lc_fk FOREIGH KEY(cmc_lc_id) REFERENCES cmc_lc,
CONSTRAINT cmc_pi_fk FOREIGH KEY(cmc_pi_id) REFERENCES cmc_pi,
CONSTRAINT cmc_lc_pi_pk PRIMARY KEY(id)
)
 

create table cmc_lc_revs_control(
id number(20) not null,
cmc_po_id number(20) not null,
req_type_stn_lookup_dtl_id number(20) not null,     -- reqstn_type from lookup new,revised
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 cmc_po_fk01 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po,
CONSTRAINT req_type_stn_lookup_dtl_fk101 FOREIGN KEY(req_type_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT cmc_lc_revs_control_pk PRIMARY KEY(id)
)
 

create table cmc_lc_revs_recs(

cmc_lc_revs_control_id number(20) not null,

id number(20) not null,
stn_hrm_company_id number(20) not null,
code varchar2(50) not null,
lc_for varchar2(50) not null,                   -- import,export
cur_stn_apv_flow_step_id number(20) not null,
next_stn_apv_flow_step_id number(20) not null,
lc_type_stn_lookup_dtl_id number(20) not null,
doc_type_stn_lookup_dtl_id number(20) not null, 
lc_sc_no varchar2(50) not null,
lc_open_date date not null,
lc_expire_date date not null,
sales_contact_no varchar2(50) not null,
sales_contact_date date not null,
bank_ref_no varchar2(50) not null,
adv_by_stn_lookup_dtl_id number(20) not null,
advised_date date not null, 
in_cnt_of_stn_lookup_dtl_id number(20) not null,
amount_tolerance_plus_perc number(2,2) not null,
amount_tolerance_minus_perc number(2,2) not null,  

CONSTRAINT cmc_lc_revs_control_fk FOREIGH KEY(cmc_lc_revs_control_id) REFERENCES cmc_lc_revs_control,

CONSTRAINT stn_hrm_company_id_fk FOREIGH KEY(stn_hrm_company_id) REFERENCES stn_hrm_company,
CONSTRAINT cur_stn_apv_flow_step_fk FOREIGH KEY(cur_stn_apv_flow_step_id) REFERENCES stn_apv_flow_step,
CONSTRAINT next_stn_apv_flow_step_fk FOREIGH KEY(next_stn_apv_flow_step_id) REFERENCES stn_apv_flow_step,
CONSTRAINT lc_type_stn_lookup_dtl_fk FOREIGH KEY(lc_type_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT doc_type_stn_lookup_dtl_fk FOREIGH KEY(doc_type_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT adv_by_stn_lookup_dtl_fk FOREIGH KEY(adv_by_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT in_cnt_of_stn_lookup_dtl_fk FOREIGH KEY(in_cnt_of_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT cmc_lc_revs_recs_pk PRIMARY KEY(id)
)
 

create table cmc_lc_btb_revs_recs(
cmc_lc_revs_control_id number(20) not null,

id number(20) not null,
cmc_lc_id number(20) not null,
btb_lc_type_lookup_dtl_id number(20) not null,
export_lc_id number(20) not null,

CONSTRAINT cmc_lc_fk FOREIGH KEY(cmc_lc_id) REFERENCES cmc_lc,

CONSTRAINT cmc_lc_revs_control_fk FOREIGH KEY(cmc_lc_revs_control_id) REFERENCES cmc_lc_revs_control,
CONSTRAINT btb_lc_type_lookup_dtl_fk FOREIGH KEY(btb_lc_type_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT export_lc_fk FOREIGH KEY(export_lc_id) REFERENCES cmc_lc,

CONSTRAINT cmc_lc_btb_revs_recs_pk PRIMARY KEY(id)
)
 

create table cmc_lc_other_revs_recs(
cmc_lc_revs_control_id number(20) not null,

id number(20) not null,
cmc_lc_id number(20) not null,
bd_bank_ref_no varchar2(50) default null, 
bd_bank_circular_no varchar2(50) default null, 
application_date date default null,
auth_signature_file_name varchar2(50) default null, 

CONSTRAINT cmc_lc_revs_control_fk FOREIGH KEY(cmc_lc_revs_control_id) REFERENCES cmc_lc_revs_control,

CONSTRAINT cmc_lc_fk FOREIGH KEY(cmc_lc_id) REFERENCES cmc_lc,
CONSTRAINT cmc_lc_other_revs_recs_pk PRIMARY KEY(id)
)
 

create table cmc_lc_pi_revs_recs(
cmc_lc_revs_control_id number(20) not null,


id number(20) not null,
cmc_lc_id number(20) not null,
cmc_pi_id number(20) not null,


CONSTRAINT cmc_lc_revs_control_fk FOREIGH KEY(cmc_lc_revs_control_id) REFERENCES cmc_lc_revs_control,

CONSTRAINT cmc_lc_fk FOREIGH KEY(cmc_lc_id) REFERENCES cmc_lc,
CONSTRAINT cmc_pi_fk FOREIGH KEY(cmc_pi_id) REFERENCES cmc_pi,

CONSTRAINT cmc_lc_pi_revs_recs_pk PRIMARY KEY(id)
)
 

create table cmc_lc_doc_accept(

id number(20) not null,
lc_for varchar2(50) not null,       -- import,export
stn_hrm_company_id number(20) not null,
cmc_lc_id number(20) not null,

bank_maturity_date date not null,
remarks varchar2(150) not null,
is_complete number(1) not null,   -- 0/1

CONSTRAINT cmc_lc_fk FOREIGH KEY(cmc_lc_id) REFERENCES cmc_lc,
CONSTRAINT stn_hrm_company_fk FOREIGH KEY(stn_hrm_company_id) REFERENCES stn_hrm_company,
CONSTRAINT cmc_lc_doc_accept_pk PRIMARY KEY(id)
)
 

create table cmc_lc_doc_accept_dtl(
id number(20) not null,
cmc_lc_doc_accept_id number(20) not null,
sl number(2) not null,
doc_name varchar2(150) not null,
no_of_doc number(2) not null,
receive_date date default null,
submit_date date default null,

CONSTRAINT cmc_lc_doc_accept_fk FOREIGH KEY(cmc_lc_doc_accept_id) REFERENCES cmc_lc_doc_accept,
CONSTRAINT cmc_lc_doc_accept_dtl_pk PRIMARY KEY(id)
)
 

create table cmc_lc_item_accept(
id number(20) not null,
lc_for varchar2(50) not null,         -- import,export
stn_hrm_company_id number(20) not null,
cmc_lc_id number(20) not null,
remarks varchar2(150) not null,
is_complete number(1) not null,       -- 0/1

CONSTRAINT cmc_lc_fk FOREIGH KEY(cmc_lc_id) REFERENCES cmc_lc,
CONSTRAINT stn_hrm_company_fk_11 FOREIGH KEY(stn_hrm_company_id) REFERENCES stn_hrm_company,
CONSTRAINT cmc_lc_item_accept_pk PRIMARY KEY(id)
)
 

create table cmc_lc_item_accept_dtl(
id number(20) not null,
cmc_lc_item_accept_id number(20) not null,

cmcer_no varchar2(20) not null,
batch_lot_no varchar2(20) not null,
style_no_stn_lookup_dtl_id number(20) default null,
stn_color_id number(20) default null,
stn_size_id number(20) default null,
prod_desc varchar2(20) not null,
qty number(5) not null,
stn_munit_id number(5) not null,
stn_punit_id number(5) not null,
case_stn_package_id number(20) default null,
case_qty number(5) default null,
ratio_stn_package_id number(20) default null,
ratio_qty number(5) default null,
rate number(5,2) not null,
amount number(5,2) not null,



CONSTRAINT cmc_lc_item_accept_fk FOREIGH KEY(cmc_lc_item_accept_id) REFERENCES cmc_lc_item_accept,

CONSTRAINT style_no_stn_lookup_dtl_fk FOREIGH KEY(style_no_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT stn_color_fk FOREIGH KEY(stn_color_id) REFERENCES stn_color,
CONSTRAINT stn_size_fk FOREIGH KEY(stn_size_id) REFERENCES stn_size,
CONSTRAINT stn_munit_fk FOREIGH KEY(stn_munit_id) REFERENCES stn_munit,
CONSTRAINT stn_punit_fk FOREIGH KEY(stn_punit_id) REFERENCES stn_punit,
CONSTRAINT case_stn_package_fk FOREIGH KEY(case_stn_package_id) REFERENCES case_stn_package,
CONSTRAINT ratio_stn_package_fk FOREIGH KEY(ratio_stn_package_id) REFERENCES ratio_stn_package,

CONSTRAINT cmc_lc_item_accept_dtl_pk PRIMARY KEY(id)
)
 

create table cmc_lc_crd(
id number(20) not null,
lc_for varchar2(50) not null, -- import,export
stn_hrm_company_id number(20) not null,
cmc_lc_id number(20) not null,
remarks varchar2(150) not null,

CONSTRAINT cmc_lc_fk FOREIGH KEY(cmc_lc_id) REFERENCES cmc_lc,
CONSTRAINT stn_hrm_company_fk FOREIGH KEY(stn_hrm_company_id) REFERENCES stn_hrm_company,
CONSTRAINT cmc_lc_crd_pk PRIMARY KEY(id)
)
 

create table cmc_lc_crd_dtl(
id number(20) not null,
cmc_lc_crd_id number(20) not null,

sl number(2) not null,
doc_name_stn_lookup_dtl_id varchar2(20) not null,
ref_no varchar2(20) not null,
amount number(5,2) not null,
stn_punit_id number(5) not null,
convert_rate number(5,2) default null,
stn_convert_punit_id number(5) not null,
dated date not null,
apv_sts_stn_lookup_dtl_id number(20) not null,


CONSTRAINT cmc_lc_crd_fk FOREIGH KEY(cmc_lc_crd_id) REFERENCES cmc_lc_crd,
CONSTRAINT stn_lookup_dtl_fk11 FOREIGH KEY(doc_name_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT stn_punit_fk FOREIGH KEY(stn_punit_id) REFERENCES stn_punit,
CONSTRAINT stn_convert_punit_fk FOREIGH KEY(stn_convert_punit_id) REFERENCES stn_convert_punit,
CONSTRAINT stn_lookup_dtl_fk_11 FOREIGH KEY(apv_sts_lookup_dtl_id) REFERENCES stn_lookup_dtl,
CONSTRAINT cmc_lc_crd_dtl_pk PRIMARY KEY(id)
)
 

create table cmc_lc_realize(
id number(20) not null,
lc_for varchar2(50) not null, -- import,export
cmc_lc_id number(20) not null,
stn_hrm_company_id number(20) not null,
realize_date date not null,

CONSTRAINT cmc_lc_fk FOREIGH KEY(cmc_lc_id) REFERENCES cmc_lc,
CONSTRAINT stn_hrm_company_fk FOREIGH KEY(stn_hrm_company_id) REFERENCES stn_lookup_dtl,
CONSTRAINT cmc_lc_realize_pk PRIMARY KEY(id)
)
 

Sl	Table Name:	Query
1-26	Setup Table(From Setup Module)	----
1	cmc_po (From cmcer Module(Export)/Self Module(import))	create table cmc_po( id number(20) not null, code varchar2(50) not null, stn_hrm_company_id number(20) not null, po_cat_lookup_dtl_id number(20) not null, -- import,export po_no varchar2(50) default null, invoice_no varchar2(20) not null, po_date_issue_date date not null, delivery_date date not null, due_date date not null, po_file_name varchar2(20) default null, is_lock 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_hrm_company_fk_81 FOREIGN KEY(stn_hrm_company_id) REFERENCES stn_hrm_company, CONSTRAINT stn_lookup_dtl_fk_81 FOREIGN KEY(pi_cat_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT cmc_po_pk PRIMARY KEY(id) )
2	cmc_po_buyer (From cmcer Module(Export)/Self Module(import))	create table cmc_po_buyer( id number(20) not null, cmc_po_id number(20) not null, stn_party_id number(20) not null, isu_stn_party_bank_dtl_id number(20) not null, negotiating_lion_bank varchar2(50) default null, negotiating_lion_bank_branch varchar2(50) default null, stn_party_con_prsn_id number(20) not null, buy_stn_party_customer_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 cmc_po_fk_82 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po, CONSTRAINT stn_party_fk_82 FOREIGN KEY(stn_party_id) REFERENCES stn_party, CONSTRAINT stn_party_bank_dtl_fk_82 FOREIGN KEY(isu_stn_party_bank_dtl_id) REFERENCES stn_party_bank_dtl, CONSTRAINT stn_party_con_prsn_fk_82 FOREIGN KEY(stn_party_con_prsn_id) REFERENCES stn_party_con_prsn, CONSTRAINT stn_party_customer_fk_82 FOREIGN KEY(buy_stn_party_customer_id) REFERENCES stn_party_customer, CONSTRAINT cmc_po_buyer_pk PRIMARY KEY(id) )
3	cmc_po_seller (From cmcer Module(Export)/Self Module(import))	create table cmc_po_seller( id number(20) not null, cmc_po_id number(20) not null, stn_party_id number(20) not null, adv_stn_party_bank_dtl_id number(20) not null, forwading_bank varchar2(50) default null, orwading_bank_branch varchar2(50) default null, noty_stn_party_customer_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 cmc_po_83 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po, CONSTRAINT stn_party_fk_83 FOREIGN KEY(stn_party_id) REFERENCES stn_party, CONSTRAINT stn_party_bank_dtl_fk_83 FOREIGN KEY(adv_stn_party_bank_dtl_id) REFERENCES stn_party_bank_dtl, CONSTRAINT stn_party_customer_fk_83 FOREIGN KEY(noty_stn_party_customer_id) REFERENCES stn_party_customer, CONSTRAINT cmc_po_seller_pk PRIMARY KEY(id) )
4	cmc_po_shipment (From cmcer Module(Export)/Self Module(import))	create table cmc_po_shipment( id number(20) not null, cmc_po_id number(20) not null, partial_shipment int(1) not null, -- 0/1 transhipment int(1) not null, -- 0/1 ty_of_ship_stn_lookup_dtl_id number(20) not null, del_term_stn_lookup_dtl_id number(20) not null, port_of_loading varchar2(20) not null, port_of_dischare varchar2(20) not null, place_of_receipt varchar2(20) not null, place_of_distination varchar2(20) not null, ten_dl_term_stn_lookup_dtl_id number(20) not null, CONSTRAINT cmc_po_fk85 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po, CONSTRAINT stn_lookup_dtl_id_fk_85 FOREIGN KEY(ty_of_ship_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT stn_lookup_dtl_fk_85 FOREIGN KEY(del_term_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT stn_lookup_dtl_id_id_fk_85 FOREIGN KEY(ten_dl_term_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT cmc_po_shipment_pk PRIMARY KEY(id) )
5	cmc_po_items (From cmcer Module(Export)/Self Module(import))	create table cmc_po_items( id number(20) not null, cmc_po_id number(20) not null, style_no_stn_lookup_dtl_id number(20) default null, stn_color_id number(20) default null, stn_size_id number(20) default null, prod_desc varchar2(20) not null, qty number(5) not null, stn_munit_id number(5) not null, stn_punit_id number(5) not null, rate number(5,2) not null, amount number(5,2) not null, case_crtn_stn_package_id number(20) not null, case_qty number(5,2) default null, ration_crtn_stn_package_id number(20) not null, ration_qty number(5,2) default null, CONSTRAINT cmc_po_fk_11 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po, CONSTRAINT stn_lookup_dtl_fk_11 FOREIGN KEY(style_no_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT stn_color_fk_11 FOREIGN KEY(stn_color_id) REFERENCES stn_color, CONSTRAINT stn_size_fk_11 FOREIGN KEY(stn_size_id) REFERENCES stn_size, CONSTRAINT stn_munit_fk_11 FOREIGN KEY(stn_munit_id) REFERENCES stn_munit, CONSTRAINT stn_punit_fk_11 FOREIGN KEY(stn_punit_id) REFERENCES stn_punit, CONSTRAINT stn_package_fk_11 FOREIGN KEY(case_crtn_stn_package_id) REFERENCES stn_package, CONSTRAINT stn_package_fk_11 FOREIGN KEY(ration_crtn_stn_package_id) REFERENCES stn_package, CONSTRAINT cmc_po_items_pk PRIMARY KEY(id) )
6	cmc_po_terms (From cmcer Module(Export)/Self Module(import))	create table cmc_po_terms( id number(20) not null, cmc_po_id number(20) not null, sl number(2) not null, code varchar2(20) not null, term_and_cond varchar2(100) not null, CONSTRAINT cmc_po_fk_87 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po, CONSTRAINT cmc_po_terms_pk PRIMARY KEY(id) )
7	cmc_po_revs_control(only revs = revised allowed)	create table cmc_po_revs_control( -- done id number(20) not null, cmc_po_id number(20) not null, req_type_stn_lookup_dtl_id number(20) not null, -- reqstn_type from lookup new,revs 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 cmc_po_fk_101 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po, CONSTRAINT stn_lookup_dtl_fk_101 FOREIGN KEY(req_type_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT cmc_po_revs_control_pk PRIMARY KEY(id) )
8	cmc_po_revs_recs(recs = records)	create table cmc_po_revs_recs( cmc_po_revs_control_id number(20) not null, -- VVI id number(20) not null, code varchar2(50) not null, stn_hrm_company_id number(20) not null, pi_cat_stn_lookup_dtl_id number(20) not null, -- import,export po_no varchar2(50) default null, invoice_no varchar2(20) not null, po_date_issue_date date not null, delivery_date date not null, due_date date not null, po_file_name varchar2(20) default null, is_lock 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 cmc_po_revs_control_fk_102 FOREIGN KEY(cmc_po_revs_control_id) REFERENCES cmc_po_revs_control, CONSTRAINT stn_hrm_company_fk_102 FOREIGN KEY(stn_hrm_company_id) REFERENCES stn_hrm_company, CONSTRAINT stn_lookup_dtl_fk_102 FOREIGN KEY(pi_cat_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT cmc_po_revs_recs_pk PRIMARY KEY(id) )
9	cmc_po_buyer_revs_recs	create table cmc_po_buyer_revs_recs( cmc_po_revs_control_id number(20) not null, -- VVI id number(20) not null, cmc_po_id number(20) not null, stn_party_id number(20) not null, isu_stn_party_bank_dtl_id number(20) not null, negotiating_lion_bank varchar2(50) default null, negotiating_lion_bank_branch varchar2(50) default null, stn_party_con_prsn_id number(20) not null, buy_stn_party_customer_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 cmc_po_revs_control_fk_103 FOREIGN KEY(cmc_po_revs_control_id) REFERENCES cmc_po_revs_control, CONSTRAINT cmc_po_id_fk_103 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po, CONSTRAINT stn_party_fk_103 FOREIGN KEY(stn_party_id) REFERENCES stn_party, CONSTRAINT stn_party_bank_dtl_fk_103 FOREIGN KEY(isu_stn_party_bank_dtl_id) REFERENCES stn_party_bank_dtl, CONSTRAINT stn_party_con_prsn_fk_103 FOREIGN KEY(stn_party_con_prsn_id) REFERENCES stn_party_con_prsn, CONSTRAINT stn_party_customer_id_fk_103 FOREIGN KEY(buy_stn_party_customer_id) REFERENCES stn_party_customer, CONSTRAINT cmc_po_buyer_revs_recs_pk PRIMARY KEY(id) )
10	cmc_po_seller_revs_recs	create table cmc_po_seller_revs_recs( cmc_po_revs_control_id number(20) not null, -- VVI id number(20) not null, cmc_po_id number(20) not null, stn_party_id number(20) not null, adv_stn_party_bank_dtl_id number(20) not null, forwading_bank varchar2(50) default null, orwading_bank_branch varchar2(50) default null, noty_stn_party_customer_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 cmc_po_revs_control_fk_88 FOREIGN KEY(cmc_po_revs_control_id) REFERENCES cmc_po_revs_control, CONSTRAINT cmc_po_fk_88 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po, CONSTRAINT stn_party_fk_88 FOREIGN KEY(stn_party_id) REFERENCES stn_party, CONSTRAINT stn_party_bank_dtl_fk_88 FOREIGN KEY(adv_stn_party_bank_dtl_id) REFERENCES stn_party_bank_dtl, CONSTRAINT stn_party_customer_fk_88 FOREIGN KEY(noty_stn_party_customer_id) REFERENCES stn_party_customer, CONSTRAINT cmc_po_seller_revs_recs_pk PRIMARY KEY(id) )
11	cmc_po_shipment_revs_recs	create table cmc_po_shipment_revs_recs( cmc_po_revs_control_id number(20) not null, -- VVI id number(20) not null, cmc_po_id number(20) not null, partial_shipment number(1) not null, -- 0/1 transhipment number(1) not null, -- 0/1 ty_of_ship_stn_lookup_dtl_id number(20) not null, del_term_stn_lookup_dtl_id number(20) not null, port_of_loading varchar2(20) not null, port_of_dischare varchar2(20) not null, place_of_receipt varchar2(20) not null, place_of_distination varchar2(20) not null, ten_dl_term_stn_lookup_dtl_id number(20) not null, CONSTRAINT cmc_po_revs_con_fk_104 FOREIGN KEY(cmc_po_revs_control_id) REFERENCES cmc_po_revs_control, CONSTRAINT cmc_po_fk_104 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po, CONSTRAINT stn_lookup_dtl_fk_104 FOREIGN KEY(ty_of_ship_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT stn_lookup_dtl_fk_114 FOREIGN KEY(del_term_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT stn_lookup_dtl_fk_124 FOREIGN KEY(ten_dl_term_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT cmc_po_shipment_revs_rcd_pk PRIMARY KEY(id) )
12	cmc_po_items_revs_recs	create table cmc_po_items_revs_recs( cmc_po_revs_control_id number(20) not null, -- VVI id number(20) not null, cmc_po_id number(20) not null, style_no_stn_lookup_dtl_id number(20) default null, stn_color_id number(20) default null, stn_size_id number(20) default null, prod_desc varchar2(20) not null, qty number(5) not null, stn_munit_id number(5) not null, stn_punit_id number(5) not null, rate number(5,2) not null, amount number(5,2) not null, case_crtn_stn_package_id number(20) not null, case_qty number(5,2) default null, ration_crtn_stn_package_id number(20) not null, ration_qty number(5,2) default null, CONSTRAINT cmc_po_revs_control_fk_105 FOREIGN KEY(cmc_po_revs_control_id) REFERENCES cmc_po_revs_control, CONSTRAINT cmc_po_fk_105 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po, CONSTRAINT stn_lookup_dtl_fk_105 FOREIGN KEY(style_no_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT stn_color_fk_105 FOREIGN KEY(stn_color_id) REFERENCES stn_color, CONSTRAINT stn_size_fk_105 FOREIGN KEY(stn_size_id) REFERENCES stn_size, CONSTRAINT stn_munit_fk_105 FOREIGN KEY(stn_munit_id) REFERENCES stn_munit, CONSTRAINT stn_punit_fk_105 FOREIGN KEY(stn_punit_id) REFERENCES stn_punit, CONSTRAINT stn_package_fk_105 FOREIGN KEY(case_crtn_stn_package_id) REFERENCES stn_package, CONSTRAINT stn_package_fk_115 FOREIGN KEY(ration_crtn_stn_package_id) REFERENCES stn_package, CONSTRAINT cmc_po_items_revs_recs_pk PRIMARY KEY(id) )
13	cmc_po_terms_revs_recs	create table cmc_po_terms_revs_recs( cmc_po_revs_control_id number(20) not null, -- VVI id number(20) not null, cmc_po_id number(20) not null, sl number(2) not null, code varchar2(20) not null, term_and_cond varchar2(100) not null, CONSTRAINT cmc_po_revs_control_fk_106 FOREIGN KEY(cmc_po_revs_control_id) REFERENCES cmc_po_revs_control, CONSTRAINT cmc_po_fk_106 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po, CONSTRAINT cmc_po_terms_revs_recs_pk PRIMARY KEY(id) )
14	cmc_pi	create table cmc_pi( id number(20) not null, cmc_po_id number(20) not null, code varchar2(50) not null, stn_hrm_company_id number(20) not null, pi_cat_stn_lookup_dtl_id number(20) not null, -- import,export invoice_no varchar2(20) not null, po_date_issue_date date not null, delivery_date date not null, due_date date not null, is_lock 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 cmc_po_fk_107 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po, CONSTRAINT stn_hrm_company_fk_107 FOREIGN KEY(stn_hrm_company_id) REFERENCES stn_hrm_company, CONSTRAINT stn_lookup_dtl_fk_107 FOREIGN KEY(pi_cat_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT cmc_pi_pk PRIMARY KEY(id) )
15	cmc_pi_buyer	create table cmc_pi_buyer( id number(20) not null, cmc_pi_id number(20) not null, cmc_po_buyer_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 cmc_pi_fk_108 FOREIGN KEY(cmc_pi_id) REFERENCES cmc_pi, CONSTRAINT cmc_po_buyer_fk_108 FOREIGN KEY(cmc_po_buyer_id) REFERENCES cmc_po_buyer, CONSTRAINT cmc_pi_buyer_pk PRIMARY KEY(id) )
16	cmc_pi_seller	create table cmc_pi_seller( id number(20) not null, cmc_pi_id number(20) not null, cmc_po_seller_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 cmc_pi_fk_109 FOREIGN KEY(cmc_pi_id) REFERENCES cmc_pi, CONSTRAINT cmc_po_seller_fk_109 FOREIGN KEY(cmc_po_seller_id) REFERENCES cmc_po_seller, CONSTRAINT cmc_pi_seller_pk PRIMARY KEY(id) )
17	cmc_pi_shipment	create table cmc_pi_shipment( id number(20) not null, cmc_pi_id number(20) not null, partial_shipment number(1) not null, -- 0/1 transhipment number(1) not null, -- 0/1 ty_of_ship_stn_lookup_dtl_id number(20) not null, del_term_stn_lookup_dtl_id number(20) not null, port_of_loading varchar2(20) not null, port_of_dischare varchar2(20) not null, place_of_receipt varchar2(20) not null, place_of_distination varchar2(20) not null, ten_dl_term_stn_lookup_dtl_id number(20) not null, CONSTRAINT cmc_po_fk_100 FOREIGN KEY(cmc_pi_id) REFERENCES cmc_po, CONSTRAINT stn_lookup_dtl_fk_110 FOREIGN KEY(ty_of_ship_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT stn_lookup_dtl_fk_120 FOREIGN KEY(del_term_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT stn_lookup_dtl_fk_130 FOREIGN KEY(ten_dl_term_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT cmc_pi_shipment_pk PRIMARY KEY(id) )
18	cmc_pi_items	create table cmc_pi_items( id number(20) not null, cmc_pi_id number(20) not null, batch_lot_no number(2) default null, style_no_stn_lookup_dtl_id number(20) default null, stn_color_id number(20) default null, stn_size_id number(20) default null, prod_desc varchar2(20) not null, qty number(5) not null, stn_munit_id number(20) not null, stn_punit_id number(20) not null, rate number(5,2) not null, amount number(5,2) not null, case_crtn_stn_package_id number(20) not null, case_qty number(5,2) default null, ration_crtn_stn_package_id number(20) not null, ration_qty number(5,2) default null, CONSTRAINT cmc_pi_fk_131 FOREIGN KEY(cmc_pi_id) REFERENCES cmc_po, CONSTRAINT stn_lookup_dtl_fk_131 FOREIGN KEY(style_no_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT stn_color_fk_131 FOREIGN KEY(stn_color_id) REFERENCES stn_color, CONSTRAINT stn_size_fk_131 FOREIGN KEY(stn_size_id) REFERENCES stn_size, CONSTRAINT stn_munit_fk_131 FOREIGN KEY(stn_munit_id) REFERENCES stn_munit, CONSTRAINT stn_punit_fk_131 FOREIGN KEY(stn_punit_id) REFERENCES stn_punit, CONSTRAINT stn_package_fk_131 FOREIGN KEY(case_crtn_stn_package_id) REFERENCES stn_package, CONSTRAINT stn_package_fk_141 FOREIGN KEY(ration_crtn_stn_package_id) REFERENCES stn_package, CONSTRAINT cmc_pi_items_pk PRIMARY KEY(id) )
19	cmc_pi_terms	create table cmc_pi_terms( id number(20) not null, cmc_pi_id number(20) not null, sl number(2) not null, code varchar2(20) not null, term_and_cond varchar2(100) not null, CONSTRAINT cmc_pi_fk_91 FOREIGN KEY(cmc_pi_id) REFERENCES cmc_pi, CONSTRAINT cmc_pi_terms_pk PRIMARY KEY(id) )
20	cmc_pi_revs_control(all insert,ammend info recorred here)	create table cmc_pi_revs_control( -- done id number(20) not null, cmc_pi_id number(20) not null, req_type_stn_lookup_dtl_id number(20) not null, -- reqstn_type from lookup new,revs 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 cmc_pi_fk102 FOREIGN KEY(cmc_pi_id) REFERENCES cmc_pi, CONSTRAINT stn_lookup_dtl_fk102 FOREIGN KEY(req_type_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT cmc_pi_revs_control_pk PRIMARY KEY(id) )
21	cmc_pi_revs_recs	create table cmc_pi_revs_recs( cmc_pi_revs_control_id number(20) not null, -- VVI id number(20) not null, cmc_po_id number(20) not null, code varchar2(50) not null, stn_hrm_company_id number(20) not null, pi_cat_stn_lookup_dtl_id number(20) not null, -- import,export invoice_no varchar2(20) not null, po_date_issue_date date not null, delivery_date date not null, due_date date not null, is_lock 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 cmc_pi_revs_control_fk105 FOREIGN KEY(cmc_pi_revs_control_id) REFERENCES cmc_pi_revs_control, CONSTRAINT stn_hrm_company_fk105 FOREIGN KEY(stn_hrm_company_id) REFERENCES stn_hrm_company, CONSTRAINT stn_lookup_dtl_fk105 FOREIGN KEY(pi_cat_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT cmc_pi_revs_recs_pk PRIMARY KEY(id) )
22	cmc_pi_buyer_revs_recs	create table cmc_pi_buyer_revs_recs( cmc_pi_revs_control_id number(20) not null, -- VVI id number(20) not null, cmc_pi_id number(20) not null, cmc_po_buyer_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 cmc_pi_revs_control_fk106 FOREIGN KEY(cmc_pi_revs_control_id) REFERENCES cmc_pi_revs_control, CONSTRAINT cmc_po_id_fk106 FOREIGN KEY(cmc_pi_id) REFERENCES cmc_po, CONSTRAINT cmc_po_buyer_fk106 FOREIGN KEY(cmc_po_buyer_id) REFERENCES cmc_po_buyer, CONSTRAINT cmc_pi_buyer_revs_recs_pk PRIMARY KEY(id) )
23	cmc_pi_seller_revs_recs	create table cmc_pi_seller_revs_recs( cmc_pi_revs_control_id number(20) not null, -- VVI id number(20) not null, cmc_pi_id number(20) not null, cmc_po_seller_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 cmc_pi_revs_control_fk107 FOREIGN KEY(cmc_pi_revs_control_id) REFERENCES cmc_pi_revs_control, CONSTRAINT cmc_po_fk107 FOREIGN KEY(cmc_pi_id) REFERENCES cmc_pi, CONSTRAINT cmc_po_seller_fk107 FOREIGN KEY(cmc_po_seller_id) REFERENCES cmc_po_seller, CONSTRAINT cmc_pi_seller_revs_recs_pk PRIMARY KEY(id) )
24	cmc_pi_shipment_revs_recs	create table cmc_pi_shipment_revs_recs( cmc_pi_revs_control_id number(20) not null, -- VVI id number(20) not null, cmc_pi_id number(20) not null, partial_shipment number(1) not null, -- 0/1 transhipment number(1) not null, -- 0/1 ty_of_ship_stn_lookup_dtl_id number(20) not null, del_term_stn_lookup_dtl_id number(20) not null, port_of_loading varchar2(20) not null, port_of_dischare varchar2(20) not null, place_of_receipt varchar2(20) not null, place_of_distination varchar2(20) not null, ten_dl_term_stn_lookup_dtl_id number(20) not null, CONSTRAINT cmc_pi_revs_control_fk108 FOREIGN KEY(cmc_pi_revs_control_id) REFERENCES cmc_pi_revs_control, CONSTRAINT cmc_pi_fk108 FOREIGN KEY(cmc_pi_id) REFERENCES cmc_pi, CONSTRAINT stn_lookup_dtl_fk108 FOREIGN KEY(ty_of_ship_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT stn_lookup_dtl_fk148 FOREIGN KEY(del_term_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT stn_lookup_dtl_fk158 FOREIGN KEY(ten_dl_term_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT cmc_pi_shipment_revs_rcd_pk PRIMARY KEY(id) )
25	cmc_pi_items_revs_recs	create table cmc_pi_items_revs_recs( cmc_pi_revs_control_id number(20) not null, -- VVI id number(20) not null, cmc_pi_id number(20) not null, batch_lot_no number(2) default null, style_no_stn_lookup_dtl_id number(20) default null, stn_color_id number(20) default null, stn_size_id number(20) default null, prod_desc varchar2(20) not null, qty number(5) not null, stn_munit_id number(5) not null, stn_punit_id number(5) not null, rate number(5,2) not null, amount number(5,2) not null, case_crtn_stn_package_id number(20) not null, case_qty number(5,2) default null, ration_crtn_stn_package_id number(20) not null, ration_qty number(5,2) default null, CONSTRAINT cmc_pi_revs_control_fk109 FOREIGN KEY(cmc_pi_revs_control_id) REFERENCES cmc_pi_revs_control, CONSTRAINT cmc_pi_fk109 FOREIGN KEY(cmc_pi_id) REFERENCES cmc_pi, CONSTRAINT stn_lookup_dtl_fk109 FOREIGN KEY(style_no_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT stn_color_fk109 FOREIGN KEY(stn_color_id) REFERENCES stn_color, CONSTRAINT stn_size_fk109 FOREIGN KEY(stn_size_id) REFERENCES stn_size, CONSTRAINT stn_munit_fk109 FOREIGN KEY(stn_munit_id) REFERENCES stn_munit, CONSTRAINT stn_punit_fk109 FOREIGN KEY(stn_punit_id) REFERENCES stn_punit, CONSTRAINT stn_package_fk169 FOREIGN KEY(case_crtn_stn_package_id) REFERENCES stn_package, CONSTRAINT stn_package_fk109 FOREIGN KEY(ration_crtn_stn_package_id) REFERENCES stn_package, CONSTRAINT cmc_pi_items_revs_recs_pk PRIMARY KEY(id) )
26	cmc_pi_terms_revs_recs	create table cmc_pi_terms_revs_recs( cmc_pi_revs_control_id number(20) not null, -- VVI id number(20) not null, cmc_pi_id number(20) not null, sl number(2) not null, code varchar2(20) not null, term_and_cond varchar2(100) not null, CONSTRAINT cmc_pi_revs_control_fk110 FOREIGN KEY(cmc_pi_revs_control_id) REFERENCES cmc_pi_revs_control, CONSTRAINT cmc_pi_fk110 FOREIGN KEY(cmc_pi_id) REFERENCES cmc_pi, CONSTRAINT cmc_pi_terms_revs_recs_pk PRIMARY KEY(id) )
27	cmc_lc	create table cmc_lc( id number(20) not null, stn_hrm_company_id number(20) not null, code varchar2(50) not null, lc_for varchar2(50) not null, -- import,export cur_stn_apv_flow_step_id number(20) not null, next_stn_apv_flow_step_id number(20) not null, lc_type_stn_lookup_dtl_id number(20) not null, doc_type_stn_lookup_dtl_id number(20) not null, lc_sc_no varchar2(50) not null, lc_open_date date not null, lc_expire_date date not null, sales_contact_no varchar2(50) not null, sales_contact_date date not null, bank_ref_no varchar2(50) not null, adv_by_stn_lookup_dtl_id number(20) not null, advised_date date not null, in_cnt_of_stn_lookup_dtl_id number(20) not null, amount_tolerance_plus_perc number(2,2) not null, amount_tolerance_minus_perc number(2,2) not null, CONSTRAINT stn_hrm_company_id_fk FOREIGH KEY(stn_hrm_company_id) REFERENCES stn_hrm_company, CONSTRAINT cur_stn_apv_flow_step_fk FOREIGH KEY(cur_stn_apv_flow_step_id) REFERENCES stn_apv_flow_step, CONSTRAINT next_stn_apv_flow_step_fk FOREIGH KEY(next_stn_apv_flow_step_id) REFERENCES stn_apv_flow_step, CONSTRAINT lc_type_stn_lookup_dtl_fk FOREIGH KEY(lc_type_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT doc_type_stn_lookup_dtl_fk FOREIGH KEY(doc_type_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT adv_by_stn_lookup_dtl_fk FOREIGH KEY(adv_by_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT in_cnt_of_stn_lookup_dtl_fk FOREIGH KEY(in_cnt_of_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT cmc_lc_pk PRIMARY KEY(id) )
28	cmc_lc_btb	create table cmc_lc_btb( id number(20) not null, cmc_lc_id number(20) not null, btb_lc_type_lookup_dtl_id number(20) not null, export_lc_id number(20) not null, CONSTRAINT cmc_lc_fk FOREIGH KEY(cmc_lc_id) REFERENCES cmc_lc, CONSTRAINT btb_lc_type_lookup_dtl_fk FOREIGH KEY(btb_lc_type_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT export_lc_fk FOREIGH KEY(export_lc_id) REFERENCES cmc_lc, CONSTRAINT cmc_lc_btb_pk PRIMARY KEY(id) )
29	cmc_lc_other	create table cmc_lc_other( id number(20) not null, cmc_lc_id number(20) not null, bd_bank_ref_no varchar2(50) default null, bd_bank_circular_no varchar2(50) default null, application_date date default null, auth_signature_file_name varchar2(50) default null, CONSTRAINT cmc_lc_fk FOREIGH KEY(cmc_lc_id) REFERENCES cmc_lc, CONSTRAINT cmc_lc_other_pk PRIMARY KEY(id) )
30	cmc_lc_pi	create table cmc_lc_pi( id number(20) not null, cmc_lc_id number(20) not null, cmc_pi_id number(20) not null, CONSTRAINT cmc_lc_fk FOREIGH KEY(cmc_lc_id) REFERENCES cmc_lc, CONSTRAINT cmc_pi_fk FOREIGH KEY(cmc_pi_id) REFERENCES cmc_pi, CONSTRAINT cmc_lc_pi_pk PRIMARY KEY(id) )
31	cmc_lc_revs_control	create table cmc_lc_revs_control( id number(20) not null, cmc_po_id number(20) not null, req_type_stn_lookup_dtl_id number(20) not null, -- reqstn_type from lookup new,revised 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 cmc_po_fk01 FOREIGN KEY(cmc_po_id) REFERENCES cmc_po, CONSTRAINT req_type_stn_lookup_dtl_fk101 FOREIGN KEY(req_type_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT cmc_lc_revs_control_pk PRIMARY KEY(id) )
32	cmc_lc_revs_recs(revs = revised,recs = records)	create table cmc_lc_revs_recs( cmc_lc_revs_control_id number(20) not null, id number(20) not null, stn_hrm_company_id number(20) not null, code varchar2(50) not null, lc_for varchar2(50) not null, -- import,export cur_stn_apv_flow_step_id number(20) not null, next_stn_apv_flow_step_id number(20) not null, lc_type_stn_lookup_dtl_id number(20) not null, doc_type_stn_lookup_dtl_id number(20) not null, lc_sc_no varchar2(50) not null, lc_open_date date not null, lc_expire_date date not null, sales_contact_no varchar2(50) not null, sales_contact_date date not null, bank_ref_no varchar2(50) not null, adv_by_stn_lookup_dtl_id number(20) not null, advised_date date not null, in_cnt_of_stn_lookup_dtl_id number(20) not null, amount_tolerance_plus_perc number(2,2) not null, amount_tolerance_minus_perc number(2,2) not null, CONSTRAINT cmc_lc_revs_control_fk FOREIGH KEY(cmc_lc_revs_control_id) REFERENCES cmc_lc_revs_control, CONSTRAINT stn_hrm_company_id_fk FOREIGH KEY(stn_hrm_company_id) REFERENCES stn_hrm_company, CONSTRAINT cur_stn_apv_flow_step_fk FOREIGH KEY(cur_stn_apv_flow_step_id) REFERENCES stn_apv_flow_step, CONSTRAINT next_stn_apv_flow_step_fk FOREIGH KEY(next_stn_apv_flow_step_id) REFERENCES stn_apv_flow_step, CONSTRAINT lc_type_stn_lookup_dtl_fk FOREIGH KEY(lc_type_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT doc_type_stn_lookup_dtl_fk FOREIGH KEY(doc_type_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT adv_by_stn_lookup_dtl_fk FOREIGH KEY(adv_by_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT in_cnt_of_stn_lookup_dtl_fk FOREIGH KEY(in_cnt_of_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT cmc_lc_revs_recs_pk PRIMARY KEY(id) )
33	cmc_lc_btb_revs_recs	create table cmc_lc_btb_revs_recs( cmc_lc_revs_control_id number(20) not null, id number(20) not null, cmc_lc_id number(20) not null, btb_lc_type_lookup_dtl_id number(20) not null, export_lc_id number(20) not null, CONSTRAINT cmc_lc_fk FOREIGH KEY(cmc_lc_id) REFERENCES cmc_lc, CONSTRAINT cmc_lc_revs_control_fk FOREIGH KEY(cmc_lc_revs_control_id) REFERENCES cmc_lc_revs_control, CONSTRAINT btb_lc_type_lookup_dtl_fk FOREIGH KEY(btb_lc_type_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT export_lc_fk FOREIGH KEY(export_lc_id) REFERENCES cmc_lc, CONSTRAINT cmc_lc_btb_revs_recs_pk PRIMARY KEY(id) )
34	cmc_lc_other_revs_recs	create table cmc_lc_other_revs_recs( cmc_lc_revs_control_id number(20) not null, id number(20) not null, cmc_lc_id number(20) not null, bd_bank_ref_no varchar2(50) default null, bd_bank_circular_no varchar2(50) default null, application_date date default null, auth_signature_file_name varchar2(50) default null, CONSTRAINT cmc_lc_revs_control_fk FOREIGH KEY(cmc_lc_revs_control_id) REFERENCES cmc_lc_revs_control, CONSTRAINT cmc_lc_fk FOREIGH KEY(cmc_lc_id) REFERENCES cmc_lc, CONSTRAINT cmc_lc_other_revs_recs_pk PRIMARY KEY(id) )
35	cmc_lc_pi_revs_recs	create table cmc_lc_pi_revs_recs( cmc_lc_revs_control_id number(20) not null, id number(20) not null, cmc_lc_id number(20) not null, cmc_pi_id number(20) not null, CONSTRAINT cmc_lc_revs_control_fk FOREIGH KEY(cmc_lc_revs_control_id) REFERENCES cmc_lc_revs_control, CONSTRAINT cmc_lc_fk FOREIGH KEY(cmc_lc_id) REFERENCES cmc_lc, CONSTRAINT cmc_pi_fk FOREIGH KEY(cmc_pi_id) REFERENCES cmc_pi, CONSTRAINT cmc_lc_pi_revs_recs_pk PRIMARY KEY(id) )
36	cmc_lc_doc_accept	create table cmc_lc_doc_accept( id number(20) not null, lc_for varchar2(50) not null, -- import,export stn_hrm_company_id number(20) not null, cmc_lc_id number(20) not null, bank_maturity_date date not null, remarks varchar2(150) not null, is_complete number(1) not null, -- 0/1 CONSTRAINT cmc_lc_fk FOREIGH KEY(cmc_lc_id) REFERENCES cmc_lc, CONSTRAINT stn_hrm_company_fk FOREIGH KEY(stn_hrm_company_id) REFERENCES stn_hrm_company, CONSTRAINT cmc_lc_doc_accept_pk PRIMARY KEY(id) )
37	cmc_lc_doc_accept_dtl	create table cmc_lc_doc_accept_dtl( id number(20) not null, cmc_lc_doc_accept_id number(20) not null, sl number(2) not null, doc_name varchar2(150) not null, no_of_doc number(2) not null, receive_date date default null, submit_date date default null, CONSTRAINT cmc_lc_doc_accept_fk FOREIGH KEY(cmc_lc_doc_accept_id) REFERENCES cmc_lc_doc_accept, CONSTRAINT cmc_lc_doc_accept_dtl_pk PRIMARY KEY(id) )
38	cmc_lc_item_accept	create table cmc_lc_item_accept( id number(20) not null, lc_for varchar2(50) not null, -- import,export stn_hrm_company_id number(20) not null, cmc_lc_id number(20) not null, remarks varchar2(150) not null, is_complete number(1) not null, -- 0/1 CONSTRAINT cmc_lc_fk FOREIGH KEY(cmc_lc_id) REFERENCES cmc_lc, CONSTRAINT stn_hrm_company_fk_11 FOREIGH KEY(stn_hrm_company_id) REFERENCES stn_hrm_company, CONSTRAINT cmc_lc_item_accept_pk PRIMARY KEY(id) )
39	cmc_lc_item_accept_dtl	create table cmc_lc_item_accept_dtl( id number(20) not null, cmc_lc_item_accept_id number(20) not null, cmcer_no varchar2(20) not null, batch_lot_no varchar2(20) not null, style_no_stn_lookup_dtl_id number(20) default null, stn_color_id number(20) default null, stn_size_id number(20) default null, prod_desc varchar2(20) not null, qty number(5) not null, stn_munit_id number(5) not null, stn_punit_id number(5) not null, case_stn_package_id number(20) default null, case_qty number(5) default null, ratio_stn_package_id number(20) default null, ratio_qty number(5) default null, rate number(5,2) not null, amount number(5,2) not null, CONSTRAINT cmc_lc_item_accept_fk FOREIGH KEY(cmc_lc_item_accept_id) REFERENCES cmc_lc_item_accept, CONSTRAINT style_no_stn_lookup_dtl_fk FOREIGH KEY(style_no_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT stn_color_fk FOREIGH KEY(stn_color_id) REFERENCES stn_color, CONSTRAINT stn_size_fk FOREIGH KEY(stn_size_id) REFERENCES stn_size, CONSTRAINT stn_munit_fk FOREIGH KEY(stn_munit_id) REFERENCES stn_munit, CONSTRAINT stn_punit_fk FOREIGH KEY(stn_punit_id) REFERENCES stn_punit, CONSTRAINT case_stn_package_fk FOREIGH KEY(case_stn_package_id) REFERENCES case_stn_package, CONSTRAINT ratio_stn_package_fk FOREIGH KEY(ratio_stn_package_id) REFERENCES ratio_stn_package, CONSTRAINT cmc_lc_item_accept_dtl_pk PRIMARY KEY(id) )
40	cmc_lc_crd	create table cmc_lc_crd( id number(20) not null, lc_for varchar2(50) not null, -- import,export stn_hrm_company_id number(20) not null, cmc_lc_id number(20) not null, remarks varchar2(150) not null, CONSTRAINT cmc_lc_fk FOREIGH KEY(cmc_lc_id) REFERENCES cmc_lc, CONSTRAINT stn_hrm_company_fk FOREIGH KEY(stn_hrm_company_id) REFERENCES stn_hrm_company, CONSTRAINT cmc_lc_crd_pk PRIMARY KEY(id) )
41	cmc_lc_crd_dtl	create table cmc_lc_crd_dtl( id number(20) not null, cmc_lc_crd_id number(20) not null, sl number(2) not null, doc_name_stn_lookup_dtl_id varchar2(20) not null, ref_no varchar2(20) not null, amount number(5,2) not null, stn_punit_id number(5) not null, convert_rate number(5,2) default null, stn_convert_punit_id number(5) not null, dated date not null, apv_sts_stn_lookup_dtl_id number(20) not null, CONSTRAINT cmc_lc_crd_fk FOREIGH KEY(cmc_lc_crd_id) REFERENCES cmc_lc_crd, CONSTRAINT stn_lookup_dtl_fk11 FOREIGH KEY(doc_name_stn_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT stn_punit_fk FOREIGH KEY(stn_punit_id) REFERENCES stn_punit, CONSTRAINT stn_convert_punit_fk FOREIGH KEY(stn_convert_punit_id) REFERENCES stn_convert_punit, CONSTRAINT stn_lookup_dtl_fk_11 FOREIGH KEY(apv_sts_lookup_dtl_id) REFERENCES stn_lookup_dtl, CONSTRAINT cmc_lc_crd_dtl_pk PRIMARY KEY(id) )
42	cmc_lc_realize	create table cmc_lc_realize( id number(20) not null, lc_for varchar2(50) not null, -- import,export cmc_lc_id number(20) not null, stn_hrm_company_id number(20) not null, realize_date date not null, CONSTRAINT cmc_lc_fk FOREIGH KEY(cmc_lc_id) REFERENCES cmc_lc, CONSTRAINT stn_hrm_company_fk FOREIGH KEY(stn_hrm_company_id) REFERENCES stn_lookup_dtl, CONSTRAINT cmc_lc_realize_pk PRIMARY KEY(id) )

Latest Tutorial