diff options
Diffstat (limited to 'db/migrations/0293_nappy_chamber.sql')
| -rw-r--r-- | db/migrations/0293_nappy_chamber.sql | 62 |
1 files changed, 62 insertions, 0 deletions
diff --git a/db/migrations/0293_nappy_chamber.sql b/db/migrations/0293_nappy_chamber.sql new file mode 100644 index 00000000..bfeb2997 --- /dev/null +++ b/db/migrations/0293_nappy_chamber.sql @@ -0,0 +1,62 @@ +CREATE TABLE "nonsap"."nonsap_user" ( + "USR_ID" varchar(255) PRIMARY KEY NOT NULL, + "USR_NM" varchar(255), + "USR_ENM" varchar(255), + "EMPNO" varchar(255), + "CO_CD" varchar(255), + "CO_NM" varchar(255), + "DEPTCD" varchar(255), + "DEPTNM" varchar(255), + "MAST_DEPTCD" varchar(255), + "MAST_DEPTNM" varchar(255), + "VNDRCD" varchar(255), + "VNDRNM" varchar(255), + "REGL_ORORD_GB" varchar(255), + "JG_CD" varchar(255), + "JG_NM" varchar(255), + "JK_CD" varchar(255), + "JK_NM" varchar(255), + "EMAIL_ADR" varchar(255), + "TELNO" varchar(255), + "HP_NO" varchar(255), + "ADR" varchar(255), + "MYSNG_ID" varchar(255), + "MYSNG_USR_ID" varchar(255), + "MYSNG_USE_YN" varchar(255), + "CHRG_BIZ_NM" varchar(255), + "FIN_PWD_CHG_DTM" varchar(255), + "FIN_LGN_DTM" varchar(255), + "FIN_LOGOUT_DTM" varchar(255), + "FIN_LGN_FAIL_TMS" varchar(255), + "FIN_USEIP" varchar(255), + "UNLOCK_DTM" varchar(255), + "LOCK_YN" varchar(255), + "AGR_YN" varchar(255), + "DEL_YN" varchar(255), + "BIZLOC_GB_CD" varchar(255), + "BIZLOC_GB_NM" varchar(255), + "GRD_NM" varchar(255), + "CH_DEPTCD" varchar(255), + "CH_DEPTNM" varchar(255), + "ORG_OTHER_NAME" varchar(255), + "GRADE_OTHER_NAME" varchar(255), + "FAX_NO" varchar(255), + "FS_INPR_ID" varchar(255), + "FS_INP_DTM" varchar(255), + "FIN_CHGR_ID" varchar(255), + "FIN_CHG_DTM" varchar(255), + "LOFF_GB" varchar(255), + "DEL_DTM" varchar(255) +); +--> statement-breakpoint +DROP VIEW "public"."user_view";--> statement-breakpoint +ALTER TABLE "users" ADD COLUMN "employee_number" varchar(50);--> statement-breakpoint +ALTER TABLE "users" ADD COLUMN "knox_id" varchar(50);--> statement-breakpoint +ALTER TABLE "users" ADD COLUMN "nonsap_user_id" varchar(50);--> statement-breakpoint +ALTER TABLE "users" ADD COLUMN "is_absent" boolean;--> statement-breakpoint +ALTER TABLE "users" ADD COLUMN "is_deleted_on_non_sap" boolean;--> statement-breakpoint +ALTER TABLE "users" ADD COLUMN "is_regular_employee" boolean;--> statement-breakpoint +ALTER TABLE "users" ADD CONSTRAINT "users_nonsap_user_id_unique" UNIQUE("nonsap_user_id");--> statement-breakpoint +CREATE VIEW "public"."user_view" AS (select "users"."id" as "user_id", "users"."name" as "user_name", "users"."phone" as "user_phone", "users"."email" as "user_email", "users"."domain" as "user_domain", "users"."image_url" as "user_image", "users"."employee_number" as "employee_number", "users"."deptName" as "dept_name", "users"."knox_id" as "knox_id", "users"."is_locked" as "is_locked", "users"."is_absent" as "is_absent", "users"."is_deleted_on_non_sap" as "is_deleted_on_non_sap", "users"."is_regular_employee" as "is_regular_employee", "vendors"."id" as "company_id", "vendors"."vendor_name" as "company_name", + array_agg("roles"."name") + as "roles", "users"."created_at" as "created_at", "users"."updated_at" as "updated_at", "users"."deactivated_at" as "deactivated_at" from "users" left join "vendors" on "users"."company_id" = "vendors"."id" left join "user_roles" on "users"."id" = "user_roles"."user_id" left join "roles" on "user_roles"."role_id" = "roles"."id" group by "users"."id", "vendors"."id");
\ No newline at end of file |