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
|
CREATE TABLE "incoterms" (
"code" varchar(20) PRIMARY KEY NOT NULL,
"description" varchar(255) NOT NULL,
"is_active" boolean DEFAULT true NOT NULL,
"created_by" integer,
"created_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "payment_terms" (
"code" varchar(50) PRIMARY KEY NOT NULL,
"description" varchar(255) NOT NULL,
"is_active" boolean DEFAULT true NOT NULL,
"created_by" integer,
"created_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "pr_items" (
"id" serial PRIMARY KEY NOT NULL,
"procurement_rfqs_id" integer,
"rfq_item" varchar(50),
"pr_item" varchar(50),
"item_id" integer,
"material_code" varchar(255),
"material_category" varchar(255),
"acc" varchar(255),
"material_description" varchar(255),
"size" varchar(255),
"delivery_date" date,
"quantity" numeric(12, 2) DEFAULT 1,
"uom" varchar(50),
"gw_uom" varchar(50),
"spec_no" varchar(255),
"spec_url" varchar(255),
"tracking_no" varchar(255),
"major_yn" boolean DEFAULT false,
"project_def" varchar(255),
"prject_sc" varchar(255),
"prject_kl" varchar(255),
"prject_lc" varchar(255),
"prject_dl" varchar(255),
"remark" text
);
--> statement-breakpoint
CREATE TABLE "procurement_rfq_details" (
"id" serial PRIMARY KEY NOT NULL,
"procurement_rfqs_id" integer,
"vendors_id" integer,
"currency" varchar(10) DEFAULT 'USD',
"payment_terms_code" varchar(50),
"incoterms_code" varchar(20),
"incoterms_detail" varchar(255),
"delivery_date" date NOT NULL,
"tax_code" varchar(255) DEFAULT 'VV',
"place_of_shipping" varchar(255),
"place_of_destination" varchar(255),
"remark" text,
"cancel_reason" text,
"updated_by" integer NOT NULL,
"updated_at" timestamp DEFAULT now() NOT NULL,
"material_price_related_yn" boolean DEFAULT false
);
--> statement-breakpoint
CREATE TABLE "procurement_rfqs" (
"id" serial PRIMARY KEY NOT NULL,
"rfq_code" varchar(50),
"project_id" integer,
"series" varchar(50),
"item_code" varchar(100) NOT NULL,
"due_date" date NOT NULL,
"rfq_send_date" date NOT NULL,
"status" varchar(30) DEFAULT 'RFQ Created' NOT NULL,
"rfq_sealed_yn" boolean DEFAULT false,
"remark" text,
"created_by" integer NOT NULL,
"updated_by" integer NOT NULL,
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp DEFAULT now() NOT NULL,
CONSTRAINT "procurement_rfqs_rfq_code_unique" UNIQUE("rfq_code")
);
--> statement-breakpoint
ALTER TABLE "incoterms" ADD CONSTRAINT "incoterms_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "payment_terms" ADD CONSTRAINT "payment_terms_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "pr_items" ADD CONSTRAINT "pr_items_procurement_rfqs_id_procurement_rfqs_id_fk" FOREIGN KEY ("procurement_rfqs_id") REFERENCES "public"."procurement_rfqs"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "pr_items" ADD CONSTRAINT "pr_items_item_id_items_id_fk" FOREIGN KEY ("item_id") REFERENCES "public"."items"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_details" ADD CONSTRAINT "procurement_rfq_details_procurement_rfqs_id_procurement_rfqs_id_fk" FOREIGN KEY ("procurement_rfqs_id") REFERENCES "public"."procurement_rfqs"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_details" ADD CONSTRAINT "procurement_rfq_details_vendors_id_vendors_id_fk" FOREIGN KEY ("vendors_id") REFERENCES "public"."vendors"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_details" ADD CONSTRAINT "procurement_rfq_details_payment_terms_code_payment_terms_code_fk" FOREIGN KEY ("payment_terms_code") REFERENCES "public"."payment_terms"("code") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_details" ADD CONSTRAINT "procurement_rfq_details_incoterms_code_incoterms_code_fk" FOREIGN KEY ("incoterms_code") REFERENCES "public"."incoterms"("code") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_details" ADD CONSTRAINT "procurement_rfq_details_updated_by_users_id_fk" FOREIGN KEY ("updated_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfqs" ADD CONSTRAINT "procurement_rfqs_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfqs" ADD CONSTRAINT "procurement_rfqs_item_code_items_id_fk" FOREIGN KEY ("item_code") REFERENCES "public"."items"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfqs" ADD CONSTRAINT "procurement_rfqs_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfqs" ADD CONSTRAINT "procurement_rfqs_updated_by_users_id_fk" FOREIGN KEY ("updated_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE VIEW "public"."normalized_rfq_details_view" AS (select "procurement_rfq_details"."id", "procurement_rfqs"."id", "procurement_rfqs"."rfq_code", "projects"."code", "projects"."name", "items"."item_code", "items"."item_name", "vendors"."vendor_name", "vendors"."vendor_code", "procurement_rfq_details"."currency", "payment_terms"."code", "payment_terms"."description", "incoterms"."code", "incoterms"."description", "procurement_rfq_details"."incoterms_detail", "procurement_rfq_details"."delivery_date", "procurement_rfq_details"."tax_code", "procurement_rfq_details"."place_of_shipping", "procurement_rfq_details"."place_of_destination", "procurement_rfq_details"."material_price_related_yn", "updated_by_user"."name", "procurement_rfq_details"."updated_at" from "procurement_rfq_details" left join "procurement_rfqs" on "procurement_rfq_details"."procurement_rfqs_id" = "procurement_rfqs"."id" left join "projects" on "procurement_rfqs"."project_id" = "projects"."id" left join "items" on "procurement_rfqs"."item_code" = "items"."id" left join "vendors" on "procurement_rfq_details"."vendors_id" = "vendors"."id" left join "payment_terms" on "procurement_rfq_details"."payment_terms_code" = "payment_terms"."code" left join "incoterms" on "procurement_rfq_details"."incoterms_code" = "incoterms"."code" left join "users" "updated_by_user" on "procurement_rfq_details"."updated_by" = "updated_by_user"."id");--> statement-breakpoint
CREATE VIEW "public"."pr_items_view" AS (select "pr_items"."id", "pr_items"."procurement_rfqs_id", "pr_items"."rfq_item", "pr_items"."pr_item", "pr_items"."item_id", "pr_items"."material_code", "pr_items"."material_category", "pr_items"."acc", "pr_items"."material_description", "pr_items"."size", "pr_items"."delivery_date", "pr_items"."quantity", "pr_items"."uom", "pr_items"."quantity", "pr_items"."gw_uom", "pr_items"."spec_no", "pr_items"."spec_url", "pr_items"."tracking_no", "pr_items"."major_yn", "pr_items"."project_def", "pr_items"."prject_sc", "pr_items"."prject_kl", "pr_items"."prject_lc", "pr_items"."prject_dl", "pr_items"."remark", "procurement_rfqs"."rfq_code", "items"."item_code", "items"."item_name" from "pr_items" left join "procurement_rfqs" on "pr_items"."procurement_rfqs_id" = "procurement_rfqs"."id" left join "items" on "pr_items"."item_id" = "items"."id");--> statement-breakpoint
CREATE VIEW "public"."procurement_rfqs_view" AS (select "procurement_rfqs"."id", "procurement_rfqs"."rfq_code", "projects"."code", "projects"."name", "procurement_rfqs"."series", "procurement_rfqs"."rfq_sealed_yn", "items"."item_code", "items"."item_name", "procurement_rfqs"."rfq_send_date", "procurement_rfqs"."due_date", "created_by_user"."name", "procurement_rfqs"."updated_at", "updated_by_user"."name", "procurement_rfqs"."remark", (
SELECT material_code
FROM pr_items
WHERE procurement_rfqs_id = "procurement_rfqs"."id"
AND major_yn = true
LIMIT 1
) as "major_item_material_code", (
SELECT COUNT(*)
FROM pr_items
WHERE procurement_rfqs_id = "procurement_rfqs"."id"
) as "pr_items_count" from "procurement_rfqs" left join "projects" on "procurement_rfqs"."project_id" = "projects"."id" left join "items" on "procurement_rfqs"."item_code" = "items"."id" left join "users" "created_by_user" on "procurement_rfqs"."created_by" = "created_by_user"."id" left join "users" "updated_by_user" on "procurement_rfqs"."updated_by" = "updated_by_user"."id");
|
