diff options
Diffstat (limited to 'db/migrations/0043_glossy_shatterstar.sql')
| -rw-r--r-- | db/migrations/0043_glossy_shatterstar.sql | 106 |
1 files changed, 106 insertions, 0 deletions
diff --git a/db/migrations/0043_glossy_shatterstar.sql b/db/migrations/0043_glossy_shatterstar.sql new file mode 100644 index 00000000..06581c93 --- /dev/null +++ b/db/migrations/0043_glossy_shatterstar.sql @@ -0,0 +1,106 @@ +CREATE TABLE "incoterms" ( + "code" varchar(20) PRIMARY KEY NOT NULL, + "description" varchar(255) NOT NULL, + "is_active" boolean DEFAULT true NOT NULL, + "created_by" integer, + "created_at" timestamp DEFAULT now() NOT NULL +); +--> statement-breakpoint +CREATE TABLE "payment_terms" ( + "code" varchar(50) PRIMARY KEY NOT NULL, + "description" varchar(255) NOT NULL, + "is_active" boolean DEFAULT true NOT NULL, + "created_by" integer, + "created_at" timestamp DEFAULT now() NOT NULL +); +--> statement-breakpoint +CREATE TABLE "pr_items" ( + "id" serial PRIMARY KEY NOT NULL, + "procurement_rfqs_id" integer, + "rfq_item" varchar(50), + "pr_item" varchar(50), + "item_id" integer, + "material_code" varchar(255), + "material_category" varchar(255), + "acc" varchar(255), + "material_description" varchar(255), + "size" varchar(255), + "delivery_date" date, + "quantity" numeric(12, 2) DEFAULT 1, + "uom" varchar(50), + "gw_uom" varchar(50), + "spec_no" varchar(255), + "spec_url" varchar(255), + "tracking_no" varchar(255), + "major_yn" boolean DEFAULT false, + "project_def" varchar(255), + "prject_sc" varchar(255), + "prject_kl" varchar(255), + "prject_lc" varchar(255), + "prject_dl" varchar(255), + "remark" text +); +--> statement-breakpoint +CREATE TABLE "procurement_rfq_details" ( + "id" serial PRIMARY KEY NOT NULL, + "procurement_rfqs_id" integer, + "vendors_id" integer, + "currency" varchar(10) DEFAULT 'USD', + "payment_terms_code" varchar(50), + "incoterms_code" varchar(20), + "incoterms_detail" varchar(255), + "delivery_date" date NOT NULL, + "tax_code" varchar(255) DEFAULT 'VV', + "place_of_shipping" varchar(255), + "place_of_destination" varchar(255), + "remark" text, + "cancel_reason" text, + "updated_by" integer NOT NULL, + "updated_at" timestamp DEFAULT now() NOT NULL, + "material_price_related_yn" boolean DEFAULT false +); +--> statement-breakpoint +CREATE TABLE "procurement_rfqs" ( + "id" serial PRIMARY KEY NOT NULL, + "rfq_code" varchar(50), + "project_id" integer, + "series" varchar(50), + "item_code" varchar(100) NOT NULL, + "due_date" date NOT NULL, + "rfq_send_date" date NOT NULL, + "status" varchar(30) DEFAULT 'RFQ Created' NOT NULL, + "rfq_sealed_yn" boolean DEFAULT false, + "remark" text, + "created_by" integer NOT NULL, + "updated_by" integer NOT NULL, + "created_at" timestamp DEFAULT now() NOT NULL, + "updated_at" timestamp DEFAULT now() NOT NULL, + CONSTRAINT "procurement_rfqs_rfq_code_unique" UNIQUE("rfq_code") +); +--> statement-breakpoint +ALTER TABLE "incoterms" ADD CONSTRAINT "incoterms_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "payment_terms" ADD CONSTRAINT "payment_terms_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "pr_items" ADD CONSTRAINT "pr_items_procurement_rfqs_id_procurement_rfqs_id_fk" FOREIGN KEY ("procurement_rfqs_id") REFERENCES "public"."procurement_rfqs"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "pr_items" ADD CONSTRAINT "pr_items_item_id_items_id_fk" FOREIGN KEY ("item_id") REFERENCES "public"."items"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "procurement_rfq_details" ADD CONSTRAINT "procurement_rfq_details_procurement_rfqs_id_procurement_rfqs_id_fk" FOREIGN KEY ("procurement_rfqs_id") REFERENCES "public"."procurement_rfqs"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "procurement_rfq_details" ADD CONSTRAINT "procurement_rfq_details_vendors_id_vendors_id_fk" FOREIGN KEY ("vendors_id") REFERENCES "public"."vendors"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "procurement_rfq_details" ADD CONSTRAINT "procurement_rfq_details_payment_terms_code_payment_terms_code_fk" FOREIGN KEY ("payment_terms_code") REFERENCES "public"."payment_terms"("code") ON DELETE set null ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "procurement_rfq_details" ADD CONSTRAINT "procurement_rfq_details_incoterms_code_incoterms_code_fk" FOREIGN KEY ("incoterms_code") REFERENCES "public"."incoterms"("code") ON DELETE set null ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "procurement_rfq_details" ADD CONSTRAINT "procurement_rfq_details_updated_by_users_id_fk" FOREIGN KEY ("updated_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "procurement_rfqs" ADD CONSTRAINT "procurement_rfqs_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "procurement_rfqs" ADD CONSTRAINT "procurement_rfqs_item_code_items_id_fk" FOREIGN KEY ("item_code") REFERENCES "public"."items"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "procurement_rfqs" ADD CONSTRAINT "procurement_rfqs_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "procurement_rfqs" ADD CONSTRAINT "procurement_rfqs_updated_by_users_id_fk" FOREIGN KEY ("updated_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint +CREATE VIEW "public"."normalized_rfq_details_view" AS (select "procurement_rfq_details"."id", "procurement_rfqs"."id", "procurement_rfqs"."rfq_code", "projects"."code", "projects"."name", "items"."item_code", "items"."item_name", "vendors"."vendor_name", "vendors"."vendor_code", "procurement_rfq_details"."currency", "payment_terms"."code", "payment_terms"."description", "incoterms"."code", "incoterms"."description", "procurement_rfq_details"."incoterms_detail", "procurement_rfq_details"."delivery_date", "procurement_rfq_details"."tax_code", "procurement_rfq_details"."place_of_shipping", "procurement_rfq_details"."place_of_destination", "procurement_rfq_details"."material_price_related_yn", "updated_by_user"."name", "procurement_rfq_details"."updated_at" from "procurement_rfq_details" left join "procurement_rfqs" on "procurement_rfq_details"."procurement_rfqs_id" = "procurement_rfqs"."id" left join "projects" on "procurement_rfqs"."project_id" = "projects"."id" left join "items" on "procurement_rfqs"."item_code" = "items"."id" left join "vendors" on "procurement_rfq_details"."vendors_id" = "vendors"."id" left join "payment_terms" on "procurement_rfq_details"."payment_terms_code" = "payment_terms"."code" left join "incoterms" on "procurement_rfq_details"."incoterms_code" = "incoterms"."code" left join "users" "updated_by_user" on "procurement_rfq_details"."updated_by" = "updated_by_user"."id");--> statement-breakpoint +CREATE VIEW "public"."pr_items_view" AS (select "pr_items"."id", "pr_items"."procurement_rfqs_id", "pr_items"."rfq_item", "pr_items"."pr_item", "pr_items"."item_id", "pr_items"."material_code", "pr_items"."material_category", "pr_items"."acc", "pr_items"."material_description", "pr_items"."size", "pr_items"."delivery_date", "pr_items"."quantity", "pr_items"."uom", "pr_items"."quantity", "pr_items"."gw_uom", "pr_items"."spec_no", "pr_items"."spec_url", "pr_items"."tracking_no", "pr_items"."major_yn", "pr_items"."project_def", "pr_items"."prject_sc", "pr_items"."prject_kl", "pr_items"."prject_lc", "pr_items"."prject_dl", "pr_items"."remark", "procurement_rfqs"."rfq_code", "items"."item_code", "items"."item_name" from "pr_items" left join "procurement_rfqs" on "pr_items"."procurement_rfqs_id" = "procurement_rfqs"."id" left join "items" on "pr_items"."item_id" = "items"."id");--> statement-breakpoint +CREATE VIEW "public"."procurement_rfqs_view" AS (select "procurement_rfqs"."id", "procurement_rfqs"."rfq_code", "projects"."code", "projects"."name", "procurement_rfqs"."series", "procurement_rfqs"."rfq_sealed_yn", "items"."item_code", "items"."item_name", "procurement_rfqs"."rfq_send_date", "procurement_rfqs"."due_date", "created_by_user"."name", "procurement_rfqs"."updated_at", "updated_by_user"."name", "procurement_rfqs"."remark", ( + SELECT material_code + FROM pr_items + WHERE procurement_rfqs_id = "procurement_rfqs"."id" + AND major_yn = true + LIMIT 1 + ) as "major_item_material_code", ( + SELECT COUNT(*) + FROM pr_items + WHERE procurement_rfqs_id = "procurement_rfqs"."id" + ) as "pr_items_count" from "procurement_rfqs" left join "projects" on "procurement_rfqs"."project_id" = "projects"."id" left join "items" on "procurement_rfqs"."item_code" = "items"."id" left join "users" "created_by_user" on "procurement_rfqs"."created_by" = "created_by_user"."id" left join "users" "updated_by_user" on "procurement_rfqs"."updated_by" = "updated_by_user"."id");
\ No newline at end of file |