diff options
Diffstat (limited to 'db/migrations/0124_sparkling_shinko_yamashiro.sql')
| -rw-r--r-- | db/migrations/0124_sparkling_shinko_yamashiro.sql | 48 |
1 files changed, 48 insertions, 0 deletions
diff --git a/db/migrations/0124_sparkling_shinko_yamashiro.sql b/db/migrations/0124_sparkling_shinko_yamashiro.sql new file mode 100644 index 00000000..2affccea --- /dev/null +++ b/db/migrations/0124_sparkling_shinko_yamashiro.sql @@ -0,0 +1,48 @@ +CREATE TABLE "vendor_response_attachments_b" ( + "id" serial PRIMARY KEY NOT NULL, + "vendor_response_id" integer NOT NULL, + "file_name" varchar(255) NOT NULL, + "original_file_name" varchar(255) NOT NULL, + "file_path" varchar(512) NOT NULL, + "file_size" integer, + "file_type" varchar(100), + "description" varchar(500), + "uploaded_by" integer, + "uploaded_at" timestamp DEFAULT now() NOT NULL +); +--> statement-breakpoint +DROP VIEW "public"."initial_rfq_detail";--> statement-breakpoint +ALTER TABLE "vendor_response_attachments_b" ADD CONSTRAINT "vendor_response_attachments_b_vendor_response_id_vendor_attachment_responses_id_fk" FOREIGN KEY ("vendor_response_id") REFERENCES "public"."vendor_attachment_responses"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "vendor_response_attachments_b" ADD CONSTRAINT "vendor_response_attachments_b_uploaded_by_users_id_fk" FOREIGN KEY ("uploaded_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint +CREATE VIEW "public"."initial_rfq_detail" AS ( + SELECT + br.id as rfq_id, + br.rfq_code, + br.status as rfq_status, + ir.id as initial_rfq_id, + ir.initial_rfq_status, + ir.vendor_id, + v.vendor_code, + v.vendor_name, + v.country as vendor_country, + v.business_size as vendor_business_size, + ir.due_date, + ir.valid_date, + ir.incoterms_code, + inc.description as incoterms_description, + ir.short_list, + ir.return_yn, + ir.cp_request_yn, + ir.prject_gtc_yn, + ir.return_revision, + ir.gtc, + ir.gtc_valid_date, + ir.classification, + ir.sparepart, + ir.created_at, + ir.updated_at + FROM b_rfqs br + JOIN initial_rfq ir ON br.id = ir.rfq_id + LEFT JOIN vendors v ON ir.vendor_id = v.id + LEFT JOIN incoterms inc ON ir.incoterms_code = inc.code +);
\ No newline at end of file |