diff options
Diffstat (limited to 'db/migrations/0314_overjoyed_menace.sql')
| -rw-r--r-- | db/migrations/0314_overjoyed_menace.sql | 49 |
1 files changed, 49 insertions, 0 deletions
diff --git a/db/migrations/0314_overjoyed_menace.sql b/db/migrations/0314_overjoyed_menace.sql new file mode 100644 index 00000000..2417cee7 --- /dev/null +++ b/db/migrations/0314_overjoyed_menace.sql @@ -0,0 +1,49 @@ +DROP VIEW "public"."gtc_clauses_with_vendor_view";--> statement-breakpoint +CREATE VIEW "public"."gtc_clauses_with_vendor_view" AS (select base_clauses.id as "base_clause_id", base_clauses.document_id as "document_id", base_clauses.parent_id as "parent_id", base_clauses.item_number as "base_item_number", base_clauses.category as "base_category", base_clauses.subtitle as "base_subtitle", base_clauses.content as "base_content", base_clauses.sort_order as "base_sort_order", base_clauses.depth as "base_depth", base_clauses.full_path as "base_full_path", base_clauses.is_active as "base_is_active", vendor_clauses.id as "vendor_clause_id", vendor_clauses.vendor_document_id as "vendor_document_id", vendor_docs.vendor_id as "vendor_id", v.vendor_code as "vendor_code", v.vendor_name as "vendor_name", base_doc.type as "document_type", base_doc.title as "document_title", base_doc.revision as "document_revision", + COALESCE(vendor_clauses.modified_item_number, base_clauses.item_number) + as "effective_item_number", + COALESCE(vendor_clauses.modified_category, base_clauses.category) + as "effective_category", + COALESCE(vendor_clauses.modified_subtitle, base_clauses.subtitle) + as "effective_subtitle", + COALESCE(vendor_clauses.modified_content, base_clauses.content) + as "effective_content", + COALESCE(vendor_clauses.sort_order, base_clauses.sort_order) + as "effective_sort_order", + COALESCE(vendor_clauses.depth, base_clauses.depth) + as "effective_depth", + COALESCE(vendor_clauses.full_path, base_clauses.full_path) + as "effective_full_path", + COALESCE(vendor_clauses.is_number_modified, false) + as "is_number_modified", + COALESCE(vendor_clauses.is_category_modified, false) + as "is_category_modified", + COALESCE(vendor_clauses.is_subtitle_modified, false) + as "is_subtitle_modified", + COALESCE(vendor_clauses.is_content_modified, false) + as "is_content_modified", + vendor_clauses.id IS NOT NULL + as "has_vendor_clause", + COALESCE(vendor_clauses.is_number_modified, false) OR + COALESCE(vendor_clauses.is_category_modified, false) OR + COALESCE(vendor_clauses.is_subtitle_modified, false) OR + COALESCE(vendor_clauses.is_content_modified, false) + as "has_modifications", vendor_clauses.review_status as "review_status", vendor_clauses.negotiation_note as "negotiation_note", + COALESCE(vendor_clauses.is_excluded, false) + as "is_excluded", + EXISTS( + SELECT 1 + FROM gtc_negotiation_history gnh + WHERE gnh.vendor_clause_id = vendor_clauses.id + AND gnh.comment IS NOT NULL + AND gnh.comment != '' + ) + as "has_negotiation_history", base_clauses.created_at as "base_created_at", base_clauses.updated_at as "base_updated_at", vendor_clauses.created_at as "vendor_created_at", vendor_clauses.updated_at as "vendor_updated_at" from gtc_clauses base_clauses left join gtc_vendor_clauses vendor_clauses on + base_clauses.id = vendor_clauses.base_clause_id + left join gtc_vendor_documents vendor_docs on + vendor_clauses.vendor_document_id = vendor_docs.id + left join vendors v on + vendor_docs.vendor_id = v.id + left join gtc_documents base_doc on + base_clauses.document_id = base_doc.id + );
\ No newline at end of file |