1
2
3
4
5
6
7
8
9
10
11
12
13
|
DROP VIEW "public"."vendor_investigations_view";--> statement-breakpoint
ALTER TABLE "vendor_investigation_attachments" ADD COLUMN "file_size" integer;--> statement-breakpoint
ALTER TABLE "vendor_investigation_attachments" ADD COLUMN "mime_type" varchar(100);--> statement-breakpoint
CREATE VIEW "public"."vendor_investigations_view" AS (select "vendor_investigations"."id", "vendor_investigations"."vendor_id", "vendor_investigations"."pq_submission_id", "vendor_investigations"."requester_id", "vendor_investigations"."qm_manager_id", "vendor_investigations"."investigation_status", "vendor_investigations"."evaluation_type", "vendor_investigations"."investigation_address", "vendor_investigations"."investigation_method", "vendor_investigations"."scheduled_start_at", "vendor_investigations"."scheduled_end_at", "vendor_investigations"."forecasted_at", "vendor_investigations"."requested_at", "vendor_investigations"."confirmed_at", "vendor_investigations"."completed_at", "vendor_investigations"."evaluation_score", "vendor_investigations"."evaluation_result", "vendor_investigations"."investigation_notes", "vendor_investigations"."created_at", "vendor_investigations"."updated_at", "vendors"."vendor_name", "vendors"."vendor_code", requester.name as "requesterName", qm_manager.name as "qmManagerName", (
CASE
WHEN EXISTS (
SELECT 1 FROM vendor_investigation_attachments via
WHERE via.investigation_id = "vendor_investigations"."id"
)
THEN true
ELSE false
END
) as "hasAttachments" from "vendor_investigations" left join "vendors" on "vendor_investigations"."vendor_id" = "vendors"."id" left join users AS requester on "vendor_investigations"."requester_id" = requester.id left join users AS qm_manager on "vendor_investigations"."qm_manager_id" = qm_manager.id);
|
