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
|
DROP VIEW "public"."procurement_rfqs_view";--> statement-breakpoint
ALTER TABLE "procurement_rfqs" ADD COLUMN "pic_code" varchar(50);--> 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"."status", "procurement_rfqs"."pic_code", "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 pr_no
FROM pr_items
WHERE procurement_rfqs_id = "procurement_rfqs"."id"
AND major_yn = true
LIMIT 1
) as "po_no", (
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_id" = "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");
CREATE VIEW "public"."procurement_rfqs_view" AS (
SELECT
"procurement_rfqs"."id" AS "id",
"procurement_rfqs"."rfq_code",
"projects"."code" AS "project_code",
"projects"."name" AS "project_name",
"procurement_rfqs"."series",
"procurement_rfqs"."pic_code",
"procurement_rfqs"."rfq_sealed_yn",
"items"."item_code",
"items"."item_name",
"procurement_rfqs"."status",
"procurement_rfqs"."rfq_send_date",
"procurement_rfqs"."due_date",
"created_by_user"."name" AS "created_by_user_name",
"procurement_rfqs"."updated_at",
"updated_by_user"."name" AS "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 pr_no
FROM pr_items
WHERE procurement_rfqs_id = "procurement_rfqs"."id"
AND major_yn = true
LIMIT 1
) AS "po_no",
(
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_id" = "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"
);
|
