drop TABLE pgdb_scm.inv_Inv_Post_Detail;
drop TABLE pgdb_scm.inv_inv_post_line;
drop TABLE pgdb_scm.inv_inv_shipping_notice_l;
drop TABLE pgdb_scm.inv_inv_shipping_notice_h;
drop TABLE pgdb_scm.inv_inv_shipping_dlvy_l;

CREATE TABLE pgdb_scm.inv_Inv_Post_Detail (
post_detail_id varchar(36) primary key,
	post_line_id varchar(4000) ,
	node_id varchar(4000) ,
	org_id varchar(4000) ,
	bill_type varchar(64) ,
	bill_header_id varchar(64) ,
	bill_line_id varchar(64) ,
	fin_txn_id text ,
	crt_user varchar ,
	crt_time timestamp ,
	upd_user varchar ,
	upd_time timestamp ,
	crt_name varchar(128) ,
	upd_name varchar(128) ,
	crt_host varchar(200) ,
	upd_host varchar(200) ,
	bill_header_code varchar(128) ,
	bill_line_code varchar(128) ,
	txn_date timestamp ,
	extend_fin_txn_id text
)CLUSTERED INTO 4 shards WITH (number_of_routing_shards = 80);

CREATE TABLE pgdb_scm.inv_inv_post_line (
	post_line_id varchar(36) primary key,
	post_header_id varchar(36) ,
	dlvy_header_id varchar(36) ,
	dlvy_line_id varchar(36) ,
	distribute_qty integer  DEFAULT 0,
	line_no varchar(50) ,
	orig_source_header_id varchar ,
	orig_source_line_id varchar ,
	orig_source_no varchar(36) ,
	return_qty integer  DEFAULT 0,
	source_id varchar(36) ,
	crt_user varchar ,
	crt_time timestamp ,
	upd_user varchar ,
	upd_time timestamp ,
	crt_name varchar(128) ,
	upd_name varchar(128) ,
	crt_host varchar(200) ,
	upd_host varchar(200) ,
	rbt_amount integer ,
	delay_flag varchar(64))CLUSTERED INTO 4 SHARDS
WITH (
   number_of_routing_shards = 80
);

CREATE TABLE pgdb_scm.inv_inv_shipping_notice_l (
	line_id varchar primary key,
	header_id varchar,
	organization_id varchar,
	line_no varchar ,
	sched_ship_qty integer,
	orig_txn_qty integer ,
	plan_ship_date timestamp ,
	arrival_date timestamp ,
	subinventory_code varchar,
	locator_code varchar,
	factory_id varchar ,
	consignee_address varchar ,
	consignee_name varchar ,
	consignee_phone varchar ,
	logistics_code varchar ,
	logistics_remarks varchar ,
	fac_remarks varchar ,
	final_organization_id varchar ,
	final_subinv_code varchar ,
	final_locator_code varchar ,
	final_factory_id varchar ,
	export_type varchar ,
	is_matching_flag varchar ,
	unit_rbt_amount integer ,
	lines_status varchar ,
	remarks varchar ,
	crt_user varchar,
	crt_time timestamp,
	upd_user varchar,
	upd_time timestamp,
	orig_source_line_id varchar,
	consignee_province varchar ,
	consignee_city varchar ,
	consignee_district varchar ,
	orig_source_ship_line_id varchar ,
	approved_status varchar ,
	factory_code varchar,
	orig_source_ship_header_id varchar ,
	consignee_email varchar ,
	inv_organization_id varchar(64) ,
	export_organization_id varchar(64) ,
	item_id varchar(64) ,
	item_code varchar(64) ,
	lot_number varchar(512) ,
	price integer ,
	total_amount integer ,
	rbt_amount integer ,
	tax_code varchar(64) ,
	ref_shipping_num varchar(300) ,
	trade_use varchar(1000) ,
	dept_oid varchar(64) ,
	bu_oid varchar(64) ,
	return_type varchar(64) ,
	return_reason varchar(4000) ,
	source_sys varchar(64) ,
	air_trans_reason varchar(4000) ,
	is_logistic varchar(64) ,
	is_online_declare_customs varchar(64) ,
	is_merge_box varchar(64) ,
	is_sample_transfer varchar(1) ,
	is_corp_export varchar(1) ,
	pro_command_num varchar(300) ,
	sp_pro_command_num varchar(300) ,
	business_type varchar(10) ,
	orig_source_parent_line_id varchar(64) ,
	posted_qty integer ,
	dvly_qty integer ,
	final_factory_code varchar(64) ,
	consignor_province varchar(100) ,
	consignor_city varchar(100) ,
	consignor_district varchar(100) ,
	consignor_address varchar(4000) ,
	consignor_name varchar(100) ,
	consignor_phone varchar(100) ,
	consignor_email varchar(200) ,
	source_bill_flag varchar(300) ,
	biz_type varchar(64) ,
	final_item_code varchar(100) ,
	final_item_id varchar(64) ,
	tax_rate integer ,
	receive_factory_code varchar(64) ,
	receive_factory_id varchar(64) ,
	receive_subinv_code varchar(64) ,
	receive_locator_code varchar(64) ,
	transfer_type varchar(100) ,
	wait_pass_qty integer ,
	is_only_invoice varchar(1) ,
	is_print_dlvy varchar(1) ,
	locator_id varchar(200) ,
	final_locator_id varchar(200) ,
	crt_name varchar(100) ,
	upd_name varchar(100) ,
	avg_tax_unit_price integer ,
	orig_sched_ship_qty integer ,
	bad_vendor_code varchar ,
	bad_vendor_name varchar ,
	bad_reason varchar ,
	raw_item_code varchar ,
	transaction_cost_unit_price integer ,
	inv_stock_organization_id varchar(64) ,
	scrap_reason varchar(1000) ,
	serial_number varchar(4000) ,
	biz_legal_rep_oid varchar(64) ,
	biz_organization_id varchar(64) ,
	biz_item_id varchar(64) ,
	biz_item_code varchar(100) ,
	biz_factory_id varchar(64) ,
	biz_factory_code varchar(64) ,
	biz_locator_id varchar(200) ,
	biz_locator_code varchar(64) ,
	biz_subinventory_code varchar(64) ,
	orig_source_ship_post_line_id varchar(64) ,
	supply_bu_oid varchar(64))CLUSTERED INTO 4 SHARDS
WITH (
   number_of_routing_shards = 80
);

CREATE TABLE pgdb_scm.inv_inv_shipping_dlvy_l (
	line_id varchar(64) primary key,
	header_id varchar(64) ,
	orig_source_header_id varchar(512) ,
	orig_source_line_id varchar(512) ,
	organization_id varchar(64) ,
	shipping_header_id varchar(64) ,
	shipping_line_id varchar(64),
	line_no varchar(100) ,
	lines_status varchar(100) ,
	posted_by varchar(64) ,
	posted_date timestamp ,
	actual_qty integer DEFAULT 0,
	plan_ship_date timestamp ,
	subinventory_code varchar(300) ,
	locator_code varchar(300) ,
	remarks varchar(4000) ,
	posted_qty integer ,
	crt_user varchar(64) ,
	crt_time timestamp ,
	upd_user varchar(64) ,
	upd_time timestamp ,
	asn_bill_no varchar(300) ,
	crt_name varchar ,
	upd_name varchar ,
	inv_organization_id varchar(64) ,
	asn_line_no varchar(300) ,
	item_id varchar(64) ,
	item_code varchar(30) ,
	lot_number varchar(300) ,
	final_factory_id varchar(64) ,
	final_subinv_code varchar(300) ,
	final_locator_code varchar(300) ,
	transaction_type_id integer ,
	final_organization_id varchar(64) ,
	export_organization_id varchar(64) ,
	is_final_factory_same varchar(1) ,
	supervision_mode varchar(1000) ,
	trade_term varchar(50) ,
	sn_number varchar(64) ,
	product_date timestamp ,
	quality_inspect_result varchar(64) ,
	parent_id varchar(64) ,
	print_remark varchar(4000) ,
	biz_locator_id varchar(200) ,
	biz_locator_code varchar(64) ,
	biz_subinventory_code varchar(64) ,
	biz_item_id varchar(64) ,
	biz_item_code varchar(100) ,
	return_type varchar(64) ,
	has_send_packing_list varchar(32)) CLUSTERED INTO 4 SHARDS
WITH (
   number_of_routing_shards = 80
);

CREATE TABLE pgdb_scm.inv_inv_shipping_notice_h (
	header_id varchar primary key,
	shipping_num varchar ,
	bill_type varchar ,
	status_code varchar(32),
	final_organization_id varchar(32) ,
	eori_flag varchar ,
	is_foreign_flag varchar(32) ,
	is_inner_order_flag varchar(32) ,
	currency_code varchar(32) ,
	orig_source_code varchar(64) ,
	remarks varchar(4000) ,
	crt_user varchar(32),
	crt_time timestamp,
	upd_user varchar(32),
	upd_time timestamp,
	crt_bill_name varchar ,
	crt_bill_user varchar ,
	crt_bill_time timestamp ,
	source_type varchar(64) ,
	ship_vendor_id varchar(100) ,
	ship_vendor_name varchar(1000) ,
	transaction_action_id integer ,
	bill_cus_code varchar(64) ,
	ship_cus_code varchar(64) ,
	final_cus_code varchar(64) ,
	orig_source_header_id varchar(64) ,
	transfer_reason varchar(64) ,
	fee_dept_oid varchar(64) ,
	require_dept_oid varchar(64) ,
	trade_term varchar(50) ,
	transaction_type_id integer ,
	is_final_factory_same varchar(1) ,
	reverse_type varchar(10) ,
	source_number varchar(4000) ,
	is_auto_transfer_in varchar(1) ,
	po_so_type varchar(50) ,
	print_time timestamp ,
	source_sub_type varchar(64) ,
	vendor_code varchar(64))CLUSTERED INTO 4 SHARDS
WITH (
   number_of_routing_shards = 80
);