diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/schema/ECC/ecc.ts | 74 | ||||
| -rw-r--r-- | db/schema/index.ts | 5 | ||||
| -rw-r--r-- | db/schema/knox/employee.ts | 53 |
3 files changed, 131 insertions, 1 deletions
diff --git a/db/schema/ECC/ecc.ts b/db/schema/ECC/ecc.ts new file mode 100644 index 00000000..e836909e --- /dev/null +++ b/db/schema/ECC/ecc.ts @@ -0,0 +1,74 @@ +import { + integer, + varchar, + timestamp, + decimal, + pgSchema, +} from 'drizzle-orm/pg-core'; + +// 적용된 WSDL 파일: IF_ECC_EVCP_PR_INFORMATION.wsdl + +// WSDL 정의에서 CHG_GB, 응답할 메시지 타입(성공/실패) 및 내용은 메타데이터이다. +// 따라서 로깅 테이블에 저장하고, 별도 스키마를 생성하지는 않는다. + +// S-ERP에서 받는 ECC 데이터를 MDG와 구분하기 위해 스키마를 새로 생성한다. +export const ECCSchema = pgSchema('ecc'); + +// Table: PR_INFORMATION_T_BID_HEADER +export const PR_INFORMATION_T_BID_HEADER = ECCSchema.table( + 'PR_INFORMATION_T_BID_HEADER', + { + id: integer('id').primaryKey().generatedByDefaultAsIdentity(), + ANFNR: varchar({ length: 10 }).notNull(), // Bidding/RFQ Number // From: IF_ECC_EVCP_PR_INFORMATION.wsdl // Required + EKGRP: varchar({ length: 10 }), // Purchaing Group // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + EKORG: varchar({ length: 10 }), // Purchasing Organization // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + WERKS: varchar({ length: 10 }), // Plant // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + ZBSART: varchar({ length: 10 }).notNull(), // Bidding // From: IF_ECC_EVCP_PR_INFORMATION.wsdl // Required + ZRFQ_TRS_DT: varchar({ length: 10 }), // Date on Creating Bidding // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + ZRFQ_TRS_TM: varchar({ length: 10 }), // Time of Creating Bidding // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + + createdAt: timestamp('created_at').defaultNow().notNull(), + updatedAt: timestamp('updated_at').defaultNow().notNull(), + } +); + +// Table: PR_INFORMATION_T_BID_ITEM +export const PR_INFORMATION_T_BID_ITEM = ECCSchema.table( + 'PR_INFORMATION_T_BID_ITEM', + { + id: integer('id').primaryKey().generatedByDefaultAsIdentity(), + ANFNR: varchar({ length: 10 }).notNull(), // Bidding/RFQ Number // From: IF_ECC_EVCP_PR_INFORMATION.wsdl // Required + ANFPS: varchar({ length: 10 }).notNull(), // Item Number of Bidding // From: IF_ECC_EVCP_PR_INFORMATION.wsdl // Required + AUFNR: varchar({ length: 12 }), // Order Number // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + BANFN: varchar({ length: 10 }).notNull(), // Purchase Requisition Number // From: IF_ECC_EVCP_PR_INFORMATION.wsdl // Required + BANPO: varchar({ length: 10 }).notNull(), // Item Number of Purchase Requisition // From: IF_ECC_EVCP_PR_INFORMATION.wsdl // Required + BPRME: varchar({ length: 10 }), // Order Price Unit (Purchasing) // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + BRGEW: decimal({ precision: 15, scale: 3 }), // Gross Weight // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + DISMM: varchar({ length: 10 }), // MRP Type // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + EBELP: varchar({ length: 10 }), // Series PO Item Seq // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + GEWEI: varchar({ length: 10 }), // Weight Unit // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + KNTTP: varchar({ length: 10 }), // Account Assignment Category // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + KOSTL: varchar({ length: 10 }), // Cost Center // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + LFDAT: varchar({ length: 10 }), // Delivery Date // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + MATKL: varchar({ length: 10 }), // Material Group // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + MATNR: varchar({ length: 18 }), // Material Number // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + MEINS: varchar({ length: 10 }), // Purchase Requisition Unit of Measure // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + MENGE: decimal({ precision: 15, scale: 3 }), // Purchase Requisition Quantity // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + PEINH: integer(), // Price Unit // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + PERNR: varchar({ length: 20 }), // Employee ID // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + POSID: varchar({ length: 24 }), // WBS No // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + PREIS: decimal({ precision: 15, scale: 2 }), // Price in Purchase Requisition // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + PSPID: varchar({ length: 24 }), // Project No // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + SAKTO: varchar({ length: 10 }), // G/L Account Number // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + TXZ01: varchar({ length: 40 }), // Short Text // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + WAERS1: varchar({ length: 10 }), // PR Currency Key // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + WAERS2: varchar({ length: 10 }), // Reference Price Currency Key // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + ZCON_NO_PO: varchar({ length: 15 }), // PR Consolidation Number // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + ZREQ_FN: varchar({ length: 10 }), // P/R Request Number // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + ZREQ_PO: varchar({ length: 10 }), // Item Number of P/R Request // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + ZRSLT_AMT: decimal({ precision: 17, scale: 2 }), // Reference Price // From: IF_ECC_EVCP_PR_INFORMATION.wsdl + + createdAt: timestamp('created_at').defaultNow().notNull(), + updatedAt: timestamp('updated_at').defaultNow().notNull(), + } +); diff --git a/db/schema/index.ts b/db/schema/index.ts index e3a3469c..100084ed 100644 --- a/db/schema/index.ts +++ b/db/schema/index.ts @@ -37,4 +37,7 @@ export * from './MDG/mdg' export * from './SOAP/soap'; // NONSAP Oracle DB 스키마 -export * from './NONSAP/nonsap';
\ No newline at end of file +export * from './NONSAP/nonsap'; + +// ECC SOAP 수신용 (RFQ, PO, PR 데이터) +export * from './ECC/ecc';
\ No newline at end of file diff --git a/db/schema/knox/employee.ts b/db/schema/knox/employee.ts new file mode 100644 index 00000000..8c228130 --- /dev/null +++ b/db/schema/knox/employee.ts @@ -0,0 +1,53 @@ +import { pgSchema, varchar, timestamp, jsonb, text, index } from "drizzle-orm/pg-core"; + +export const knoxSchema = pgSchema("knox"); + +export const employee = knoxSchema.table("employee", { + epId: varchar("ep_id", { length: 25 }).primaryKey(), + employeeNumber: varchar("employee_number", { length: 20 }), + userId: varchar("user_id", { length: 50 }), + fullName: varchar("full_name", { length: 100 }), + givenName: varchar("given_name", { length: 100 }), + sirName: varchar("sir_name", { length: 50 }), + companyCode: varchar("company_code", { length: 10 }), + companyName: varchar("company_name", { length: 100 }), + departmentCode: varchar("department_code", { length: 50 }), + departmentName: varchar("department_name", { length: 255 }), + titleCode: varchar("title_code", { length: 20 }), + titleName: varchar("title_name", { length: 100 }), + emailAddress: varchar("email_address", { length: 255 }), + mobile: varchar("mobile", { length: 50 }), + employeeStatus: varchar("employee_status", { length: 2 }), + employeeType: varchar("employee_type", { length: 2 }), + accountStatus: varchar("account_status", { length: 2 }), + securityLevel: varchar("security_level", { length: 2 }), + preferredLanguage: varchar("preferred_language", { length: 5 }), + description: text("description"), + raw: jsonb("raw").notNull(), + createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(), + updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(), + enCompanyName: varchar("en_company_name", { length: 100 }), + enDepartmentName: varchar("en_department_name", { length: 255 }), + enDiscription: varchar("en_discription", { length: 255 }), + enFullName: varchar("en_full_name", { length: 100 }), + enGivenName: varchar("en_given_name", { length: 100 }), + enGradeName: varchar("en_grade_name", { length: 100 }), + enSirName: varchar("en_sir_name", { length: 50 }), + enTitleName: varchar("en_title_name", { length: 100 }), + gradeName: varchar("grade_name", { length: 100 }), + gradeTitleIndiCode: varchar("grade_title_indi_code", { length: 2 }), + jobName: varchar("job_name", { length: 100 }), + realNameYn: varchar("real_name_yn", { length: 2 }), + serverLocation: varchar("server_location", { length: 2 }), + titleSortOrder: varchar("title_sort_order", { length: 10 }), +}, (table) => { + return { + companyDepartmentIdx: index("knox_employee_company_department_idx").on(table.companyCode, table.departmentCode), + employeeNumberIdx: index("knox_employee_number_idx").on(table.employeeNumber), + userIdIdx: index("knox_employee_user_id_idx").on(table.userId), + emailIdx: index("knox_employee_email_idx").on(table.emailAddress), + }; +}); + +export type KnoxEmployee = typeof employee.$inferSelect; +export type NewKnoxEmployee = typeof employee.$inferInsert;
\ No newline at end of file |