(김준회) 입찰 인터페이스 처리, 자재그룹명 매핑 수정, 자재그룹 뷰 수정, 부서별 도메인 할당시 동기화 처리, 도메인 부서 할당 다이얼로그 부서목록 스크롤 처리, 삼성중공업 사용자 global search 개선

2 files changed, 15 insertions, 10 deletions

diff --git a/db/schema/bidding.ts b/db/schema/bidding.ts
index 9c623232..40632607 100644
--- a/db/schema/bidding.ts
+++ b/db/schema/bidding.ts
@@ -172,6 +172,7 @@ export const biddings = pgTable('biddings', {
   createdAt: timestamp('created_at').defaultNow().notNull(),
   updatedAt: timestamp('updated_at').defaultNow().notNull(), // 최종수정일
   updatedBy: varchar('updated_by', { length: 100 }), // 최종수정자
+  ANFNR: varchar({length: 50}), // 원본 ANFNR 추적을 위한 Bidding/RFQ Number (ECC)
 })
 
 // 9. 사양설명회 정보 테이블
diff --git a/db/schema/items.ts b/db/schema/items.ts
index a7eb2a3f..ef19a276 100644
--- a/db/schema/items.ts
+++ b/db/schema/items.ts
@@ -1,6 +1,6 @@
 import { pgTable, varchar, text, timestamp, serial, integer, unique, pgView } from "drizzle-orm/pg-core"
-import { sql } from "drizzle-orm"
-import { MATERIAL_MASTER_PART_MATL } from "./MDG/mdg"
+import { sql, eq } from "drizzle-orm"
+import { MATERIAL_MASTER_PART_MATL, MATERIAL_MASTER_PART_MATL_CHARASGN } from "./MDG/mdg"
 
 // 자재 아이템 정보 테이블 (items) - 기존 CMCTB_MAT_CLAS 테이블 매핑 (SOAP 연결 시 컬럼이 추가/삭제될 수 있음)
 export const items = pgTable("items", {
@@ -89,23 +89,27 @@ export type ItemOffshoreHull = typeof itemOffshoreHull.$inferSelect;
 //각 테이블별 컬럼 변경(itemid -> itemCode)
 
 
-// 자재그룹 검색용 뷰 - MDG 스키마의 MATERIAL_MASTER_PART_MATL 테이블에서 DISTINCT 조회
+// 자재그룹 검색용 뷰 - MATKL(자재그룹코드)별 자재그룹 설명 조회
 export const materialSearchView = pgView("material_search_view").as((qb) => {
   return qb
     .select({
-      materialGroupCode: MATERIAL_MASTER_PART_MATL.MATKL,
-      materialName: MATERIAL_MASTER_PART_MATL.ZZNAME,
-      displayText: sql<string>`COALESCE(${MATERIAL_MASTER_PART_MATL.MATKL}, '') || ' - ' || COALESCE(${MATERIAL_MASTER_PART_MATL.ZZNAME}, '')`.as("display_text")
+      materialGroupCode: sql<string>`${MATERIAL_MASTER_PART_MATL.MATKL}`.as("material_group_code"),
+      materialGroupDesc: sql<string>`${MATERIAL_MASTER_PART_MATL_CHARASGN.ATWTB}`.as("material_group_desc"),
+      displayText: sql<string>`COALESCE(${MATERIAL_MASTER_PART_MATL.MATKL}, '') || ' - ' || COALESCE(${MATERIAL_MASTER_PART_MATL_CHARASGN.ATWTB}, '')`.as("display_text")
     })
     .from(MATERIAL_MASTER_PART_MATL)
+    .innerJoin(
+      MATERIAL_MASTER_PART_MATL_CHARASGN,
+      eq(MATERIAL_MASTER_PART_MATL.MATNR, MATERIAL_MASTER_PART_MATL_CHARASGN.MATNR)
+    )
     .where(sql`
       ${MATERIAL_MASTER_PART_MATL.MATKL} IS NOT NULL 
       AND ${MATERIAL_MASTER_PART_MATL.MATKL} != ''
-      AND ${MATERIAL_MASTER_PART_MATL.ZZNAME} IS NOT NULL 
-      AND ${MATERIAL_MASTER_PART_MATL.ZZNAME} != ''
+      AND ${MATERIAL_MASTER_PART_MATL_CHARASGN.ATWTB} IS NOT NULL 
+      AND ${MATERIAL_MASTER_PART_MATL_CHARASGN.ATWTB} != ''
     `)
-    .groupBy(MATERIAL_MASTER_PART_MATL.MATKL, MATERIAL_MASTER_PART_MATL.ZZNAME)
-    .orderBy(MATERIAL_MASTER_PART_MATL.MATKL, MATERIAL_MASTER_PART_MATL.ZZNAME);
+    .groupBy(MATERIAL_MASTER_PART_MATL.MATKL, MATERIAL_MASTER_PART_MATL_CHARASGN.ATWTB)
+    .orderBy(MATERIAL_MASTER_PART_MATL.MATKL, MATERIAL_MASTER_PART_MATL_CHARASGN.ATWTB);
 });
 
 export type MaterialSearchView = typeof materialSearchView.$inferSelect;