diff options
Diffstat (limited to 'db/migrations/0311_amazing_maximus.sql')
| -rw-r--r-- | db/migrations/0311_amazing_maximus.sql | 48 |
1 files changed, 48 insertions, 0 deletions
diff --git a/db/migrations/0311_amazing_maximus.sql b/db/migrations/0311_amazing_maximus.sql new file mode 100644 index 00000000..22281fd8 --- /dev/null +++ b/db/migrations/0311_amazing_maximus.sql @@ -0,0 +1,48 @@ +CREATE VIEW "public"."gtc_clauses_with_vendor_view" AS (select "gtc_clauses"."id", "gtc_clauses"."document_id", "gtc_clauses"."parent_id", "gtc_clauses"."item_number", "gtc_clauses"."category", "gtc_clauses"."subtitle", "gtc_clauses"."content", "gtc_clauses"."sort_order", "gtc_clauses"."depth", "gtc_clauses"."full_path", "gtc_clauses"."is_active", "gtc_vendor_clauses"."id", "gtc_vendor_clauses"."vendor_document_id", "gtc_vendor_documents"."vendor_id", "vendors"."vendor_code", "vendors"."vendor_name", "gtc_documents"."type", "gtc_documents"."title", "gtc_documents"."revision", + COALESCE("gtc_vendor_clauses"."modified_item_number", "gtc_clauses"."item_number") + as "effective_item_number", + COALESCE("gtc_vendor_clauses"."modified_category", "gtc_clauses"."category") + as "effective_category", + COALESCE("gtc_vendor_clauses"."modified_subtitle", "gtc_clauses"."subtitle") + as "effective_subtitle", + COALESCE("gtc_vendor_clauses"."modified_content", "gtc_clauses"."content") + as "effective_content", + COALESCE("gtc_vendor_clauses"."sort_order", "gtc_clauses"."sort_order") + as "effective_sort_order", + COALESCE("gtc_vendor_clauses"."depth", "gtc_clauses"."depth") + as "effective_depth", + COALESCE("gtc_vendor_clauses"."full_path", "gtc_clauses"."full_path") + as "effective_full_path", + "gtc_vendor_clauses"."is_number_modified" IS TRUE + as "is_number_modified", + "gtc_vendor_clauses"."is_category_modified" IS TRUE + as "is_category_modified", + "gtc_vendor_clauses"."is_subtitle_modified" IS TRUE + as "is_subtitle_modified", + "gtc_vendor_clauses"."is_content_modified" IS TRUE + as "is_content_modified", + "gtc_vendor_clauses"."id" IS NOT NULL + as "has_vendor_clause", + ("gtc_vendor_clauses"."is_number_modified" IS TRUE) OR + ("gtc_vendor_clauses"."is_category_modified" IS TRUE) OR + ("gtc_vendor_clauses"."is_subtitle_modified" IS TRUE) OR + ("gtc_vendor_clauses"."is_content_modified" IS TRUE) + as "has_modifications", "gtc_vendor_clauses"."review_status", "gtc_vendor_clauses"."negotiation_note", + "gtc_vendor_clauses"."is_excluded" IS TRUE + as "is_excluded", + EXISTS( + SELECT 1 + FROM gtc_negotiation_history gnh + WHERE gnh.vendor_clause_id = "gtc_vendor_clauses"."id" + AND gnh.comment IS NOT NULL + AND gnh.comment != '' + ) + as "has_negotiation_history", "gtc_clauses"."created_at", "gtc_clauses"."updated_at", "gtc_vendor_clauses"."created_at", "gtc_vendor_clauses"."updated_at" from "gtc_clauses" left join "gtc_vendor_clauses" on + "gtc_clauses"."id" = "gtc_vendor_clauses"."base_clause_id" + left join "gtc_vendor_documents" on + "gtc_vendor_clauses"."vendor_document_id" = "gtc_vendor_documents"."id" + left join "vendors" on + "gtc_vendor_documents"."vendor_id" = "vendors"."id" + left join "gtc_documents" on + "gtc_clauses"."document_id" = "gtc_documents"."id" + );
\ No newline at end of file |