diff options
Diffstat (limited to 'db/migrations/0369_wide_the_phantom.sql')
| -rw-r--r-- | db/migrations/0369_wide_the_phantom.sql | 12 |
1 files changed, 12 insertions, 0 deletions
diff --git a/db/migrations/0369_wide_the_phantom.sql b/db/migrations/0369_wide_the_phantom.sql new file mode 100644 index 00000000..7b6f4926 --- /dev/null +++ b/db/migrations/0369_wide_the_phantom.sql @@ -0,0 +1,12 @@ +DROP VIEW "public"."tbe_documents_view";--> statement-breakpoint +CREATE VIEW "public"."tbe_documents_view" AS (select dr.id as "document_review_id", dr.tbe_session_id as "tbe_session_id", dr.document_source as "document_source", + CASE + WHEN dr.document_source = 'buyer' THEN dr.buyer_attachment_id + WHEN dr.document_source = 'vendor' THEN dr.vendor_attachment_id + ELSE NULL + END + as "document_id", dr.document_type as "document_type", dr.document_name as "document_name", COALESCE(ba_rev.original_file_name, vd.original_file_name) as "original_file_name", COALESCE(ba_rev.file_path, vd.file_path) as "file_path", COALESCE(ba_rev.file_size, vd.file_size) as "file_size", COALESCE(ba_rev.file_type, vd.file_type) as "file_type", dr.review_status as "review_status", dr.technical_compliance as "technical_compliance", dr.quality_acceptable as "quality_acceptable", COALESCE(dr.requires_revision, false) as "requires_revision", COALESCE(dr.has_pdftron_comments, false) as "has_pdftron_comments", dr.pdftron_document_id as "pdftron_document_id", COALESCE(dr.pdftron_annotation_count, 0) as "pdftron_annotation_count", dr.reviewed_by as "reviewed_by", dr.reviewed_at as "reviewed_at", dr.review_comments as "review_comments", + CASE WHEN dr.document_source = 'vendor' THEN vd.submitted_by ELSE NULL END + as "submitted_by", + CASE WHEN dr.document_source = 'vendor' THEN vd.submitted_at ELSE NULL END + as "submitted_at", dr.created_at as "created_at", dr.updated_at as "updated_at" from "rfq_last_tbe_document_reviews" "dr" left join "rfq_last_attachments" "ba" on dr.buyer_attachment_id = "ba"."id" left join "rfq_last_attachment_revisions" "ba_rev" on dr.buyer_attachment_revision_id = "ba_rev"."id" left join "rfq_last_tbe_vendor_documents" "vd" on dr.vendor_attachment_id = "vd"."id");
\ No newline at end of file |