Commercial Table List

By ukmodak | April 1st 2024 12:11:14 PM | viewed 521 times
Sl Table Name: Query example recs
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)
)
 
bONEandALL
Visitor

Total : 27273

Today :9

Today Visit Country :

  • United States
  • Sweden
  • Singapore
  • Russia