1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
|
CREATE TABLE "avl_list" (
"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "avl_list_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
"is_template" boolean DEFAULT false,
"construction_sector" varchar(10) NOT NULL,
"project_code" varchar(50),
"ship_type" varchar(50),
"avl_kind" varchar(50),
"ht_division" varchar(10),
"rev" integer DEFAULT 1,
"created_at" timestamp DEFAULT now(),
"created_by" varchar(50),
"updated_at" timestamp DEFAULT now(),
"updated_by" varchar(50)
);
--> statement-breakpoint
CREATE TABLE "avl_vendor_info" (
"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "avl_vendor_info_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
"project_code" varchar(50),
"avl_list_id" integer,
"owner_suggestion" boolean DEFAULT false,
"shi_suggestion" boolean DEFAULT false,
"equip_bulk_division" varchar(1),
"discipline_code" varchar(10),
"discipline_name" varchar(50),
"material_name_customer_side" varchar(100),
"package_code" varchar(50),
"package_name" varchar(100),
"material_group_code" varchar(50),
"material_group_name" varchar(100),
"vendor_id" integer,
"vendor_name" varchar(100),
"vendor_code" varchar(50),
"avl_vendor_name" varchar(100),
"tier" varchar(20),
"fa_target" boolean DEFAULT false,
"fa_status" varchar(50),
"is_agent" boolean DEFAULT false,
"contract_signer_id" integer,
"contract_signer_name" varchar(100),
"contract_signer_code" varchar(50),
"headquarter_location" varchar(50),
"manufacturing_location" varchar(50),
"has_avl" boolean DEFAULT false,
"is_blacklist" boolean DEFAULT false,
"is_bcc" boolean DEFAULT false,
"tech_quote_number" varchar(50),
"quote_code" varchar(50),
"quote_vendor_id" integer,
"quote_vendor_name" varchar(100),
"quote_vendor_code" varchar(50),
"quote_country" varchar(50),
"quote_total_amount" numeric(15, 2),
"quote_received_date" varchar(10),
"recent_quote_date" varchar(10),
"recent_quote_number" varchar(50),
"recent_order_date" varchar(10),
"recent_order_number" varchar(50),
"remark" varchar(1000),
"created_at" timestamp DEFAULT now(),
"updated_at" timestamp DEFAULT now()
);
--> statement-breakpoint
CREATE TABLE "vendor_pool" (
"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "vendor_pool_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
"construction_sector" varchar(10) NOT NULL,
"ht_division" varchar(10) NOT NULL,
"design_category_code" varchar(2) NOT NULL,
"design_category" varchar(50) NOT NULL,
"equip_bulk_division" varchar(1) NOT NULL,
"package_code" varchar(50),
"package_name" varchar(100),
"material_group_code" varchar(50),
"material_group_name" varchar(100),
"sm_code" varchar(50),
"similar_material_name_purchase" varchar(100),
"similar_material_name_other" varchar(100),
"vendor_code" varchar(50),
"vendor_name" varchar(100),
"tax_id" varchar(50),
"fa_target" boolean DEFAULT false,
"fa_status" varchar(50),
"fa_remark" varchar(200),
"tier" varchar(20),
"is_agent" boolean DEFAULT false,
"contract_signer_code" varchar(50),
"contract_signer_name" varchar(100),
"headquarter_location" varchar(50),
"manufacturing_location" varchar(50),
"avl_vendor_name" varchar(100),
"similar_vendor_name" varchar(100),
"has_avl" boolean DEFAULT false,
"is_blacklist" boolean DEFAULT false,
"is_bcc" boolean DEFAULT false,
"purchase_opinion" varchar(500),
"ship_type_common" boolean DEFAULT false,
"ship_type_amax" boolean DEFAULT false,
"ship_type_smax" boolean DEFAULT false,
"ship_type_vlcc" boolean DEFAULT false,
"ship_type_lngc" boolean DEFAULT false,
"ship_type_cont" boolean DEFAULT false,
"offshore_type_common" boolean DEFAULT false,
"offshore_type_fpso" boolean DEFAULT false,
"offshore_type_flng" boolean DEFAULT false,
"offshore_type_fpu" boolean DEFAULT false,
"offshore_type_platform" boolean DEFAULT false,
"offshore_type_wtiv" boolean DEFAULT false,
"offshore_type_gom" boolean DEFAULT false,
"pic_name" varchar(50),
"pic_email" varchar(100),
"pic_phone" varchar(20),
"agent_name" varchar(50),
"agent_email" varchar(100),
"agent_phone" varchar(20),
"recent_quote_date" varchar(10),
"recent_quote_number" varchar(50),
"recent_order_date" varchar(10),
"recent_order_number" varchar(50),
"registration_date" timestamp DEFAULT now(),
"registrant" varchar(50),
"last_modified_date" timestamp DEFAULT now(),
"last_modifier" varchar(50)
);
--> statement-breakpoint
DROP TABLE "S-ERP"."TB_SAP_EquipInfo" CASCADE;--> statement-breakpoint
DROP TABLE "S-ERP"."TB_SAP_MainternanceBOM" CASCADE;--> statement-breakpoint
DROP TABLE "S-ERP"."TB_SAP_MaterialInfo" CASCADE;--> statement-breakpoint
DROP TABLE "S-ERP"."TB_SAP_MaterialPurchase" CASCADE;--> statement-breakpoint
DROP TABLE "S-ERP"."TB_SAP_MaterialReceiving" CASCADE;--> statement-breakpoint
DROP TABLE "S-ERP"."TB_SAP_MaterialRelease" CASCADE;--> statement-breakpoint
DROP TABLE "S-ERP"."TB_SAP_MaterialRepair" CASCADE;--> statement-breakpoint
DROP TABLE "S-ERP"."TB_SAP_MaterialStock" CASCADE;--> statement-breakpoint
DROP TABLE "S-ERP"."TB_SAP_Order" CASCADE;--> statement-breakpoint
DROP TABLE "S-ERP"."TB_SAP_OrderBreakdown" CASCADE;--> statement-breakpoint
DROP TABLE "S-ERP"."TB_SAP_OrderConfirm" CASCADE;--> statement-breakpoint
DROP TABLE "S-ERP"."TB_SAP_OrderNotice" CASCADE;--> statement-breakpoint
ALTER TABLE "avl_vendor_info" ADD CONSTRAINT "avl_vendor_info_avl_list_id_avl_list_id_fk" FOREIGN KEY ("avl_list_id") REFERENCES "public"."avl_list"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
DROP SCHEMA "S-ERP";
|
