diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/schema/vendors.ts | 502 |
1 files changed, 341 insertions, 161 deletions
diff --git a/db/schema/vendors.ts b/db/schema/vendors.ts index 182b7037..90900517 100644 --- a/db/schema/vendors.ts +++ b/db/schema/vendors.ts @@ -1,8 +1,9 @@ // db/schema/vendors.ts import { pgTable, serial, varchar, text, timestamp, boolean, integer ,pgView} from "drizzle-orm/pg-core"; import { items } from "./items"; -import { sql, eq, relations} from "drizzle-orm"; +import { sql, eq, relations } from "drizzle-orm"; import { users } from "./users"; +import { vendorPQSubmissions } from "./pq"; // vendorTypes 테이블 생성 @@ -63,8 +64,302 @@ export const vendors = pgTable("vendors", { createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), + + // --- [시작] Oracle DB 추가 필드들 (CMCTB_VENDOR_GENERAL 기준) --- + accountGroup: varchar("account_group", { length: 4 }), // ACNT_GRP - 계정그룹 + accountGroupType: varchar("account_group_type", { length: 2 }), // ACNT_GRP_TP - 계정그룹종류 + customerCode: varchar("customer_code", { length: 10 }), // CSTM_CD - 고객코드 + postingHoldIndicator: varchar("posting_hold_indicator", { length: 1 }), // PST_HOLD_ORDR - 전기보류지시자 + purchaseHoldIndicator: varchar("purchase_hold_indicator", { length: 1 }), // PUR_HOLD_ORDR - 구매보류지시자 + holdReason: varchar("hold_reason", { length: 200 }), // HOLD_CAUS - 보류사유 + deleteIndicator: varchar("delete_indicator", { length: 1 }), // DEL_ORDR - 삭제지시자 + companyId: varchar("company_id", { length: 6 }), // CO_ID - 법인ID + businessType: varchar("business_type", { length: 90 }), // BIZTP - 사업유형 + industryType: varchar("industry_type", { length: 90 }), // BIZCON - 산업유형 + registrationDate: varchar("registration_date", { length: 8 }), // REG_DT - 등록일자 + registrationTime: varchar("registration_time", { length: 6 }), // REG_DTM - 등록시간 + registrarId: varchar("registrar_id", { length: 13 }), // REGR_ID - 등록자 + approvalDate: varchar("approval_date", { length: 8 }), // AGR_DT - 승인일자 + approvalTime: varchar("approval_time", { length: 6 }), // AGR_TM - 승인시간 + approverId: varchar("approver_id", { length: 13 }), // AGR_R_ID - 승인자ID + changeDate: varchar("change_date", { length: 8 }), // CHG_DT - 변경일자 + changeTime: varchar("change_time", { length: 6 }), // CHG_TM - 변경시간 + changerId: varchar("changer_id", { length: 13 }), // CHGR_ID - 변경자ID + nationCode: varchar("nation_code", { length: 3 }), // NTN_CD - 국가코드 + representativeTelNumber: varchar("representative_tel_number", { length: 30 }), // REP_TEL_NO - 대표전화번호 + representativeFaxNumber: varchar("representative_fax_number", { length: 31 }), // REP_FAX_NO - 대표FAX번호 + businessRegistrationNumber: varchar("business_registration_number", { length: 10 }), // BIZR_NO - 사업자번호 + corporateRegistrationNumberOracle: varchar("corporate_registration_number_oracle", { length: 18 }), // CO_REG_NO - 법인등록번호 + taxCode4: varchar("tax_code_4", { length: 54 }), // TX_CD_4 - 세금번호4 + companyEstablishmentDate: varchar("company_establishment_date", { length: 8 }), // CO_INST_DT - 설립일자 + vendorType: varchar("vendor_type", { length: 2 }), // VNDR_TP - 구매처유형 + globalTopCode: varchar("global_top_code", { length: 11 }), // GBL_TOP_CD - GLOBALTOP코드 + globalTopName: varchar("global_top_name", { length: 120 }), // GBL_TOP_NM - GLOBALTOP명 + domesticTopCode: varchar("domestic_top_code", { length: 11 }), // DMST_TOP_CD - 국내TOP코드 + domesticTopName: varchar("domestic_top_name", { length: 120 }), // DMST_TOP_NM - 국내TOP명 + businessUnitCode: varchar("business_unit_code", { length: 11 }), // BIZ_UOM_CD - 사업단위코드 + businessUnitName: varchar("business_unit_name", { length: 120 }), // BIZ_UOM_NM - 사업단위명 + dunsNumber: varchar("duns_number", { length: 11 }), // DNS_NO - DUNS번호 + interfaceDate: varchar("interface_date", { length: 8 }), // IF_DT - 인터페이스일자 + interfaceTime: varchar("interface_time", { length: 6 }), // IF_TM - 인터페이스시간 + interfaceStatus: varchar("interface_status", { length: 1 }), // IF_STAT - 인터페이스상태 + interfaceMessage: varchar("interface_message", { length: 100 }), // IF_MSG - 인터페이스메시지 + title: varchar("title", { length: 45 }), // TTL - 타이틀 + vatRegistrationNumber: varchar("vat_registration_number", { length: 20 }), // VAT_REG_NO - 부가세등록번호 + giroVendorIndicator: varchar("giro_vendor_indicator", { length: 1 }), // GIRO_VNDR_ORDR - 지로VENDOR지시자 + vendorName1: varchar("vendor_name_1", { length: 120 }), // VNDRNM_1 - Vendor명1 + vendorName2: varchar("vendor_name_2", { length: 120 }), // VNDRNM_2 - VENDOR명2 + vendorName3: varchar("vendor_name_3", { length: 120 }), // VNDRNM_3 - VENDOR명3 + vendorName4: varchar("vendor_name_4", { length: 120 }), // VNDRNM_4 - VENDOR명4 + vendorNameAbbreviation1: varchar("vendor_name_abbreviation_1", { length: 60 }), // VNDRNM_ABRV_1 - VENDOR명약어1 + vendorNameAbbreviation2: varchar("vendor_name_abbreviation_2", { length: 60 }), // VNDRNM_ABRV_2 - VENDOR명약어2 + potentialVendorCode: varchar("potential_vendor_code", { length: 10 }), // PTNT_VNDRCD - 잠재VENDOR코드 + address1: varchar("address_1", { length: 120 }), // ADR_1 - 주소1 + address2: varchar("address_2", { length: 512 }), // ADR_2 - 주소2 + qualityManagerName: varchar("quality_manager_name", { length: 60 }), // QLT_CHRGR_NM - 품질담당자명 + qualityManagerTelNumber: varchar("quality_manager_tel_number", { length: 30 }), // QLT_CHRGR_TELNO - 품질담당자전화번호 + qualityManagerEmail: varchar("quality_manager_email", { length: 241 }), // QLT_CHRGR_EMAIL - 품질담당자이메일 + subWorkplaceSequence: varchar("sub_workplace_sequence", { length: 16 }), // SB_WKA_SEQ - SUB작업장순서 + overlapCauseCode: varchar("overlap_cause_code", { length: 2 }), // OVLAP_CAUS_CD - 중복사유코드 + documentType: varchar("document_type", { length: 3 }), // DOC_TP - 문서유형 + documentNumber: varchar("document_number", { length: 25 }), // DOC_NO - 문서번호 + partialDocument: varchar("partial_document", { length: 3 }), // PTN_DOC - 부분문서 + documentVersion: varchar("document_version", { length: 2 }), // DOC_VER - 문서버전 + inboundFlag: varchar("inbound_flag", { length: 1 }), // INB_FLAG - 인바운드플래그 + deleteHoldIndicator: varchar("delete_hold_indicator", { length: 1 }), // DEL_HOLD_ORDR - 삭제보류지시자 + purchaseHoldDate: varchar("purchase_hold_date", { length: 8 }), // PUR_HOLD_DT - 구매보류일자 + postBox: varchar("post_box", { length: 30 }), // POBX - 사서함 + internationalLocationCheckNumber: integer("international_location_check_number"), // INTL_LCTN_CHK_NUM - 국제LOCATION점검숫자 + withholdingTaxGenderKey: varchar("withholding_tax_gender_key", { length: 1 }), // SRCETX_RP_SEX_KEY - 원천세의무자성별키 + vendorContractManager1: varchar("vendor_contract_manager_1", { length: 105 }), // VNDR_CNRT_CHRGR_1 - VENDOR계약담당자1 + vendorContractManager2: varchar("vendor_contract_manager_2", { length: 105 }), // VNDR_CNRT_CHRGR_2 - VENDOR계약담당자2 + representativeResidentNumber: varchar("representative_resident_number", { length: 13 }), // REPR_RESNO - 대표생년월일 + companyVolume: varchar("company_volume", { length: 1 }), // CO_VLM - 기업규모 + // --- [끝] Oracle DB 추가 필드들 (CMCTB_VENDOR_GENERAL 기준) --- +}); + +// ------- [시작] MDZ 인터페이스 목적 테이블 추가 ------------- + +// 벤더 업무그룹 테이블 (CMCTB_VENDOR_GRP 대응) +export const vendorBusinessGroups = pgTable("vendor_business_groups", { + id: serial("id").primaryKey(), + vendorId: integer("vendor_id").notNull().references(() => vendors.id), + businessGroupCode: varchar("business_group_code", { length: 3 }).notNull(), // BIZ_GRP_CD - 업무그룹코드 + createdDate: varchar("created_date", { length: 8 }), // CRTE_DT - 생성일자 + createdTime: varchar("created_time", { length: 6 }), // CRTE_TM - 생성시간 + creatorId: varchar("creator_id", { length: 13 }), // CRTER_ID - 생성자ID + changeDate: varchar("change_date", { length: 8 }), // CHG_DT - 변경일자 + changeTime: varchar("change_time", { length: 6 }), // CHG_TM - 변경시간 + changerId: varchar("changer_id", { length: 13 }), // CHGR_ID - 변경자ID + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), +}); + +// 사내협력사 벤더 테이블 (CMCTB_VENDOR_INCO 대응) +export const vendorInternalPartners = pgTable("vendor_internal_partners", { + id: serial("id").primaryKey(), + vendorId: integer("vendor_id").notNull().references(() => vendors.id), + vendorName: varchar("vendor_name", { length: 120 }), // VNDRNM - VENDOR코명 + representativeName: varchar("representative_name", { length: 30 }), // REPR_NM - 대표자명 + partnerType: varchar("partner_type", { length: 1 }), // PRTNR_GB - 협력사구분 + internalPartnerCode: varchar("internal_partner_code", { length: 3 }), // INCO_PRTNR_CD - 사내협력사코드 + internalPartnerWorkplace1: varchar("internal_partner_workplace_1", { length: 1 }), // INCO_PRTNR_WKA_1 - 사내협력사작업장1 + internalPartnerWorkplace2: varchar("internal_partner_workplace_2", { length: 1 }), // INCO_PRTNR_WKA_2 - 사내협력사작업장2 + internalPartnerWorkplace3: varchar("internal_partner_workplace_3", { length: 1 }), // INCO_PRTNR_WKA_3 - 사내협력사작업장3 + jobTypeCode: varchar("job_type_code", { length: 2 }), // JBTYPE_CD - 직종코드 + jobTypeCode2: varchar("job_type_code_2", { length: 2 }), // JBTYPE_CD_2 - 직종코드2 + individualCorporateType: varchar("individual_corporate_type", { length: 2 }), // INDV_CO_GB - 개인법인구분 + internalFoundationYn: varchar("internal_foundation_yn", { length: 1 }), // INCO_FOND_YN - 사내창립유무 + dockNumber: varchar("dock_number", { length: 25 }), // DOCK_NO - 도크번호 + companyInputDate: varchar("company_input_date", { length: 8 }), // OCMP_INP_DT - 당사투입일자 + internalWithdrawalDate: varchar("internal_withdrawal_date", { length: 8 }), // INCO_DUSE_DT - 사내철수일자 + industrialInsurancePremiumRate: integer("industrial_insurance_premium_rate"), // INDST_INS_PMRAT - 산재보험요율 + contractPerformanceGuarantee: integer("contract_performance_guarantee"), // CNRT_PFRM_GRAMT - 계약이행보증금 + wageRate: integer("wage_rate"), // WGE_RAT - 임금율 + correspondingDepartmentCode1: varchar("corresponding_department_code_1", { length: 30 }), // CRSPD_DEPTCD_1 - 해당부서코드1 + correspondingDepartmentCode2: varchar("corresponding_department_code_2", { length: 30 }), // CRSPD_DEPTCD_2 - 해당부서코드2 + correspondingTeamBelonging: varchar("corresponding_team_belonging", { length: 100 }), // CRSPD_TEAM_BLNG - 해당팀소속 + internalPartnerItem1: varchar("internal_partner_item_1", { length: 120 }), // INCO_PRTNR_ITM_1 - 사내협력사종목1 + internalPartnerItem2: varchar("internal_partner_item_2", { length: 120 }), // INCO_PRTNR_ITM_2 - 사내협력사종목2 + officeLocation: varchar("office_location", { length: 240 }), // OFC_LOC - 사무실위치 + representativeCompanyCareer: varchar("representative_company_career", { length: 300 }), // REP_OCMP_CARR - 대표당사경력 + internalWithdrawalReason: varchar("internal_withdrawal_reason", { length: 600 }), // INCO_DUSE_CAUS - 사내철수사유 + telephoneNumber: varchar("telephone_number", { length: 30 }), // TEL_NO - 전화번호 + address1: varchar("address_1", { length: 200 }), // ADR1 - 주소 + address2: varchar("address_2", { length: 200 }), // ADR2 - 상세주소 + oldVendorCode: varchar("old_vendor_code", { length: 10 }), // OLD_VNDRCD - 이전 VENDOR코드 + treeNumber: varchar("tree_number", { length: 1 }), // TREE_NUM - 하위 VENDOR 갯수 + createdDate: varchar("created_date", { length: 8 }), // CRTE_DT - 생성일자 + createdTime: varchar("created_time", { length: 6 }), // CRTE_TM - 생성시간 + createdUserId: varchar("created_user_id", { length: 13 }), // CRTE_USR_ID - 생성사용자ID + changeDate: varchar("change_date", { length: 8 }), // CHG_DT - 수정일자 + changeTime: varchar("change_time", { length: 6 }), // CHG_TM - 수정시간 + changeUserId: varchar("change_user_id", { length: 13 }), // CHG_USR_ID - 수정사용자ID + upperJobType: varchar("upper_job_type", { length: 2 }), // UPR_JBTYPE - 직종단가 + supplierBusinessPlaceCode: varchar("supplier_business_place_code", { length: 4 }), // ZBYBP - 공급받는자 종사업장 식별코드 + remark: varchar("remark", { length: 4000 }), // RMK - 비고 + withdrawalPlanYn: varchar("withdrawal_plan_yn", { length: 1 }), // WDL_PLN_YN - 철수예정유무 + wageDelayOccurrence: varchar("wage_delay_occurrence", { length: 8 }), // WGE_DELY_DVL - 임금체불발생 + escrowYn: varchar("escrow_yn", { length: 1 }), // ESCROW_YN - 에스크로가입유무 + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), +}); + +// 벤더 구매조직 테이블 (CMCTB_VENDOR_PORG 대응) +export const vendorPurchaseOrganizations = pgTable("vendor_purchase_organizations", { + id: serial("id").primaryKey(), + vendorId: integer("vendor_id").notNull().references(() => vendors.id), + purchaseOrgCode: varchar("purchase_org_code", { length: 4 }).notNull(), // PUR_ORG_CD - 구매조직 + purchaseOrderCurrency: varchar("purchase_order_currency", { length: 5 }), // PUR_ORD_CUR - 구매오더통화 + paymentTerms: varchar("payment_terms", { length: 4 }), // SPLY_COND - 지급조건 + deliveryTerms1: varchar("delivery_terms_1", { length: 3 }), // DL_COND_1 - 인도조건1 + deliveryTerms2: varchar("delivery_terms_2", { length: 90 }), // DL_COND_2 - 인도조건2 + calculationSchemaGroup: varchar("calculation_schema_group", { length: 2 }), // CALC_SHM_GRP - 계산스키마그룹 + grBasedInvoiceVerification: varchar("gr_based_invoice_verification", { length: 1 }), // GR_BSE_INVC_VR - GR기준송장검증 + automaticPurchaseOrderIndicator: varchar("automatic_purchase_order_indicator", { length: 1 }), // AT_PUR_ORD_ORDR - 자동구매오더지시자 + purchaseHoldIndicator: varchar("purchase_hold_indicator", { length: 1 }), // PUR_HOLD_ORDR - 구매보류지시자 + deleteIndicator: varchar("delete_indicator", { length: 1 }), // DEL_ORDR - 삭제지시자 + interfaceDate: varchar("interface_date", { length: 8 }), // IF_DT - 인터페이스일자 + interfaceTime: varchar("interface_time", { length: 6 }), // IF_TM - 인터페이스시간 + interfaceStatus: varchar("interface_status", { length: 1 }), // IF_STAT - 인터페이스상태 + interfaceMessage: varchar("interface_message", { length: 100 }), // IF_MSG - 인터페이스메시지 + orderConfirmationRequestIndicator: varchar("order_confirmation_request_indicator", { length: 1 }), // ORD_CNFM_REQ_ORDR - 오더확인요청지시자 + salesManagerName: varchar("sales_manager_name", { length: 120 }), // SALE_CHRGR_NM - 영업담당자명 + vendorTelephoneNumber: varchar("vendor_telephone_number", { length: 30 }), // VNDR_TELNO - VENDOR전화번호 + confirmationControlKey: varchar("confirmation_control_key", { length: 4 }), // CNFM_CTL_KEY - 확정제어키 + purchaseHoldDate: varchar("purchase_hold_date", { length: 8 }), // PUR_HOLD_DT - 구매보류일자 + purchaseHoldReason: varchar("purchase_hold_reason", { length: 120 }), // PUR_HOLD_CAUS - 구매보류사유 + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), +}); + +// 벤더 대표자 이메일 테이블 (CMCTB_VENDOR_REPREMAIL 대응) +export const vendorRepresentativeEmails = pgTable("vendor_representative_emails", { + id: serial("id").primaryKey(), + vendorId: integer("vendor_id").notNull().references(() => vendors.id), + addressNumber: varchar("address_number", { length: 10 }), // ADR_NO - 주소번호 + representativeSequence: varchar("representative_sequence", { length: 3 }).notNull(), // REPR_SER - 대표자순번 + validStartDate: varchar("valid_start_date", { length: 8 }).notNull(), // VLD_ST_DT - 유효시작일자 + emailAddress: varchar("email_address", { length: 241 }), // EMAIL_ADR - 이메일주소 + interfaceDate: varchar("interface_date", { length: 8 }), // IF_DT - 인터페이스일자 + interfaceTime: varchar("interface_time", { length: 6 }), // IF_TM - 인터페이스시간 + interfaceStatus: varchar("interface_status", { length: 1 }), // IF_STAT - 인터페이스상태 + interfaceMessage: varchar("interface_message", { length: 100 }), // IF_MSG - 인터페이스메시지 + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), +}); + +// 벤더 대표자 팩스 테이블 (CMCTB_VENDOR_REPRFAX 대응) +export const vendorRepresentativeFaxes = pgTable("vendor_representative_faxes", { + id: serial("id").primaryKey(), + vendorId: integer("vendor_id").notNull().references(() => vendors.id), + addressNumber: varchar("address_number", { length: 10 }), // ADR_NO - 주소번호 + representativeSequence: varchar("representative_sequence", { length: 3 }).notNull(), // REPR_SER - 대표자순번 + validStartDate: varchar("valid_start_date", { length: 8 }).notNull(), // VLD_ST_DT - 유효시작일자 + nationCode: varchar("nation_code", { length: 3 }), // NTN_CD - 국가코드 + faxNumber: varchar("fax_number", { length: 30 }), // FAXNO - 팩스번호 + faxExtensionNumber: varchar("fax_extension_number", { length: 10 }), // FAX_ETS_NO - 팩스내선번호 + interfaceDate: varchar("interface_date", { length: 8 }), // IF_DT - 인터페이스일자 + interfaceTime: varchar("interface_time", { length: 6 }), // IF_TM - 인터페이스시간 + interfaceStatus: varchar("interface_status", { length: 1 }), // IF_STAT - 인터페이스상태 + interfaceMessage: varchar("interface_message", { length: 100 }), // IF_MSG - 인터페이스메시지 + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), +}); + +// 벤더 대표자 전화번호 테이블 (CMCTB_VENDOR_REPRTEL 대응) +export const vendorRepresentativeTelephones = pgTable("vendor_representative_telephones", { + id: serial("id").primaryKey(), + vendorId: integer("vendor_id").notNull().references(() => vendors.id), + addressNumber: varchar("address_number", { length: 10 }), // ADR_NO - 주소번호 + representativeSequence: varchar("representative_sequence", { length: 3 }).notNull(), // REPR_SER - 대표자순번 + validStartDate: varchar("valid_start_date", { length: 8 }).notNull(), // VLD_ST_DT - 유효시작일자 + nationCode: varchar("nation_code", { length: 3 }), // NTN_CD - 국가코드 + telephoneNumber: varchar("telephone_number", { length: 30 }), // TELNO - 전화번호 + extensionNumber: varchar("extension_number", { length: 10 }), // ETX_NO - 내선번호 + mobileIndicator: varchar("mobile_indicator", { length: 1 }), // HP_ORDR - 핸드폰지시자 + interfaceDate: varchar("interface_date", { length: 8 }), // IF_DT - 인터페이스일자 + interfaceTime: varchar("interface_time", { length: 6 }), // IF_TM - 인터페이스시간 + interfaceStatus: varchar("interface_status", { length: 1 }), // IF_STAT - 인터페이스상태 + interfaceMessage: varchar("interface_message", { length: 100 }), // IF_MSG - 인터페이스메시지 + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), +}); + +// 벤더 대표자 URL 테이블 (CMCTB_VENDOR_REPRURL 대응) +export const vendorRepresentativeUrls = pgTable("vendor_representative_urls", { + id: serial("id").primaryKey(), + vendorId: integer("vendor_id").notNull().references(() => vendors.id), + addressNumber: varchar("address_number", { length: 10 }), // ADR_NO - 주소번호 + representativeSequence: varchar("representative_sequence", { length: 3 }).notNull(), // REPR_SER - 대표자순번 + validStartDate: varchar("valid_start_date", { length: 8 }).notNull(), // VLD_ST_DT - 유효시작일자 + url: varchar("url", { length: 2048 }), // URL - URL + interfaceDate: varchar("interface_date", { length: 8 }), // IF_DT - 인터페이스일자 + interfaceTime: varchar("interface_time", { length: 6 }), // IF_TM - 인터페이스시간 + interfaceStatus: varchar("interface_status", { length: 1 }), // IF_STAT - 인터페이스상태 + interfaceMessage: varchar("interface_message", { length: 100 }), // IF_MSG - 인터페이스메시지 + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), +}); + +// 벤더 세금번호 테이블 (CMCTB_VENDOR_TAXNUM 대응) +export const vendorTaxNumbers = pgTable("vendor_tax_numbers", { + id: serial("id").primaryKey(), + vendorId: integer("vendor_id").notNull().references(() => vendors.id), + taxNumberCategory: varchar("tax_number_category", { length: 4 }).notNull(), // TX_NO_CTG - 세금번호범주 + businessPartnerTaxNumber: varchar("business_partner_tax_number", { length: 20 }), // BIZ_PTNR_TX_NO - 사업파트너세금번호 + interfaceDate: varchar("interface_date", { length: 8 }), // IF_DT - 인터페이스일자 + interfaceTime: varchar("interface_time", { length: 6 }), // IF_TM - 인터페이스시간 + interfaceStatus: varchar("interface_status", { length: 1 }), // IF_STAT - 인터페이스상태 + interfaceMessage: varchar("interface_message", { length: 100 }), // IF_MSG - 인터페이스메시지 + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), +}); + +// 벤더 파트너역할 테이블 (CMCTB_VENDOR_VFPN 대응) +export const vendorPartnerFunctions = pgTable("vendor_partner_functions", { + id: serial("id").primaryKey(), + vendorId: integer("vendor_id").notNull().references(() => vendors.id), + purchaseOrgCode: varchar("purchase_org_code", { length: 4 }).notNull(), // PUR_ORG_CD - 구매조직 + vendorSubNumber: varchar("vendor_sub_number", { length: 6 }).notNull(), // VNDR_SUB_NO - VENDOR서브번호 + plantCode: varchar("plant_code", { length: 4 }).notNull(), // PLNT_CD - 플랜트코드 + partnerFunction: varchar("partner_function", { length: 2 }).notNull(), // PTNR_SKL - 파트너기능 + partnerCounter: varchar("partner_counter", { length: 3 }).notNull(), // PTNR_CNT - 파트너카운터 + otherReferenceVendorCode: varchar("other_reference_vendor_code", { length: 10 }), // ETC_REF_VNDRCD - 기타참조VENDOR코드 + defaultPartnerIndicator: varchar("default_partner_indicator", { length: 1 }), // BSE_PTNR_ORDR - 기본파트너지시자 + interfaceDate: varchar("interface_date", { length: 8 }), // IF_DT - 인터페이스일자 + interfaceTime: varchar("interface_time", { length: 6 }), // IF_TM - 인터페이스시간 + interfaceStatus: varchar("interface_status", { length: 1 }), // IF_STAT - 인터페이스상태 + interfaceMessage: varchar("interface_message", { length: 100 }), // IF_MSG - 인터페이스메시지 + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), +}); + +// 벤더 원천세 테이블 (CMCTB_VENDOR_WHTHX 대응) +export const vendorWithholdingTax = pgTable("vendor_withholding_tax", { + id: serial("id").primaryKey(), + vendorId: integer("vendor_id").notNull().references(() => vendors.id), + companyCode: varchar("company_code", { length: 4 }).notNull(), // CO_CD - 회사코드 + withholdingTaxType: varchar("withholding_tax_type", { length: 2 }).notNull(), // SRCE_TX_TP - 원천세유형 + withholdingTaxRelatedIndicator: varchar("withholding_tax_related_indicator", { length: 1 }), // SRCE_TX_REL_ORDR - 원천세관련지시자 + recipientType: varchar("recipient_type", { length: 2 }), // RECIP_TP - 수취인유형 + withholdingTaxIdentificationNumber: varchar("withholding_tax_identification_number", { length: 16 }), // SRCE_TX_IDENT_NO - 원천세식별번호 + withholdingTaxCode: varchar("withholding_tax_code", { length: 2 }), // SRCE_TX_NO - 원천세코드 + exemptionCertificateNumber: varchar("exemption_certificate_number", { length: 15 }), // DCHAG_CERT_NO - 면제증명서번호 + exemptionRate: integer("exemption_rate"), // DCHAG_RAT - 면제율 + exemptionStartDate: varchar("exemption_start_date", { length: 8 }), // DCHAG_ST_DT - 면제시작일자 + exemptionEndDate: varchar("exemption_end_date", { length: 8 }), // DCHAG_ED_DT - 면제종료일 + exemptionReason: varchar("exemption_reason", { length: 200 }), // DCHAG_CAUS - 면제사유 + interfaceDate: varchar("interface_date", { length: 8 }), // IF_DT - 인터페이스일자 + interfaceTime: varchar("interface_time", { length: 6 }), // IF_TM - 인터페이스시간 + interfaceStatus: varchar("interface_status", { length: 1 }), // IF_STAT - 인터페이스상태 + interfaceMessage: varchar("interface_message", { length: 100 }), // IF_MSG - 인터페이스메시지 + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), }); +// ------- [끝] MDZ 인터페이스 목적 테이블 추가 ------------- + export const vendorContacts = pgTable("vendor_contacts", { id: serial("id").primaryKey(), vendorId: integer("vendor_id").notNull().references(() => vendors.id), @@ -135,137 +430,18 @@ export type VendorWithAttachments = Vendor & { export type VendorItemsView = typeof vendorItemsView.$inferSelect -export const vendorInvestigations = pgTable("vendor_investigations", { - id: serial("id").primaryKey(), - - // 어떤 벤더에 대한 실사인지 참조 - vendorId: integer("vendor_id").notNull().references(() => vendors.id), - - // 실사 상태 - // 예: "PLANNED" | "IN_PROGRESS" | "COMPLETED" | "CANCELED" - investigationStatus: varchar("investigation_status", { - length: 50, - enum: [ - "PLANNED", - "IN_PROGRESS", - "COMPLETED", - "CANCELED", - ], - }) - .notNull() - .default("PLANNED"), - - // 실사 일정 시작일 / 종료일 - scheduledStartAt: timestamp("scheduled_start_at"), - scheduledEndAt: timestamp("scheduled_end_at"), - - // 실제 실사 완료일 - completedAt: timestamp("completed_at"), - - // 실사 내용이나 특이사항 - investigationNotes: text("investigation_notes"), - - createdAt: timestamp("created_at").defaultNow().notNull(), - updatedAt: timestamp("updated_at").defaultNow().notNull(), -}); - -// 타입 정의 -export type VendorInvestigation = typeof vendorInvestigations.$inferSelect; - -export const vendorInvestigationAttachments = pgTable( - "vendor_investigation_attachments", - { - id: serial("id").primaryKey(), - // 어떤 실사 (investigation)에 대한 첨부파일인지 - investigationId: integer("investigation_id") - .notNull() - .references(() => vendorInvestigations.id, { onDelete: "cascade" }), - - fileName: varchar("file_name", { length: 255 }).notNull(), - filePath: varchar("file_path", { length: 1024 }).notNull(), - - // 첨부파일 종류 (예: 보고서, 사진, 기타 등 구분) - attachmentType: varchar("attachment_type", { length: 50 }).default("REPORT"), - - createdAt: timestamp("created_at").defaultNow().notNull(), - updatedAt: timestamp("updated_at").defaultNow().notNull(), - } -); - -// 첨부파일 테이블 타입 -export type VendorInvestigationAttachment = - typeof vendorInvestigationAttachments.$inferSelect; - -/** - * A view that joins vendor_investigations + vendors, - * and also embeds contacts & possibleItems as JSON arrays. - */ -export const vendorInvestigationsView = pgView( - "vendor_investigations_view" -).as((qb) => { - return qb - .select({ - // Investigation fields - investigationId: vendorInvestigations.id, - investigationStatus: vendorInvestigations.investigationStatus, - scheduledStartAt: vendorInvestigations.scheduledStartAt, - scheduledEndAt: vendorInvestigations.scheduledEndAt, - completedAt: vendorInvestigations.completedAt, - investigationNotes: vendorInvestigations.investigationNotes, - investigationCreatedAt: vendorInvestigations.createdAt, - investigationUpdatedAt: vendorInvestigations.updatedAt, - - // Vendor fields - vendorId: vendorInvestigations.vendorId, - vendorName: vendors.vendorName, - vendorCode: vendors.vendorCode, - vendorTaxId: vendors.taxId, - vendorStatus: vendors.status, - vendorCountry: vendors.country, - vendorEmail: vendors.email, - vendorPhone: vendors.phone, - vendorWebsite: vendors.website, - - // JSON-aggregated contacts - contacts: sql<string>`( - SELECT COALESCE( - json_agg(json_build_object( - 'contactName', c.contact_name, - 'contactEmail', c.contact_email, - 'contactPhone', c.contact_phone, - 'contactPosition', c.contact_position, - 'isPrimary', c.is_primary, - 'contactCreatedAt', c.created_at, - 'contactUpdatedAt', c.updated_at - )), - '[]'::json - ) - FROM vendor_contacts c - WHERE c.vendor_id = ${vendors.id} - )`.as("contacts"), - - // JSON-aggregated possible items with itemName from items table - possibleItems: sql<string>`( - SELECT COALESCE( - json_agg(json_build_object( - 'itemCode', pi.item_code, - 'itemName', i.item_name, - 'itemCreatedAt', pi.created_at, - 'itemUpdatedAt', pi.updated_at - )), - '[]'::json - ) - FROM vendor_possible_items pi - LEFT JOIN items i ON pi.item_code = i.item_code - WHERE pi.vendor_id = ${vendors.id} - )`.as("possibleItems"), - }) - .from(vendorInvestigations) - .leftJoin( - vendors, - eq(vendorInvestigations.vendorId, vendors.id) - ) -}) +// ------- [시작] MDG 대응을 위한 새로운 테이블 타입 정의 ------------- +export type VendorBusinessGroup = typeof vendorBusinessGroups.$inferSelect +export type VendorInternalPartner = typeof vendorInternalPartners.$inferSelect +export type VendorPurchaseOrganization = typeof vendorPurchaseOrganizations.$inferSelect +export type VendorRepresentativeEmail = typeof vendorRepresentativeEmails.$inferSelect +export type VendorRepresentativeFax = typeof vendorRepresentativeFaxes.$inferSelect +export type VendorRepresentativeTelephone = typeof vendorRepresentativeTelephones.$inferSelect +export type VendorRepresentativeUrl = typeof vendorRepresentativeUrls.$inferSelect +export type VendorTaxNumber = typeof vendorTaxNumbers.$inferSelect +export type VendorPartnerFunction = typeof vendorPartnerFunctions.$inferSelect +export type VendorWithholdingTax = typeof vendorWithholdingTax.$inferSelect +// ------- [끝] MDG 대응을 위한 새로운 테이블 타입 정의 ------------- export const vendorCandidates = pgTable("vendor_candidates", { @@ -310,6 +486,7 @@ export const vendorDetailView = pgView("vendor_detail_view").as((qb) => { vendorCode: vendors.vendorCode, taxId: vendors.taxId, address: vendors.address, + businessSize:vendors.businessSize, country: vendors.country, phone: vendors.phone, email: vendors.email, @@ -483,36 +660,37 @@ export const vendorsWithTypesView = pgView("vendors_with_types").as((qb) => { return qb .select({ // Vendor fields - id: vendors.id, - vendorName: vendors.vendorName, - vendorCode: vendors.vendorCode, - taxId: vendors.taxId, - address: vendors.address, - country: vendors.country, - phone: vendors.phone, - email: vendors.email, - website: vendors.website, - status: vendors.status, - vendorTypeId: vendors.vendorTypeId, - representativeName: vendors.representativeName, - representativeBirth: vendors.representativeBirth, - representativeEmail: vendors.representativeEmail, - representativePhone: vendors.representativePhone, - corporateRegistrationNumber: vendors.corporateRegistrationNumber, - items: vendors.items, - creditAgency: vendors.creditAgency, - creditRating: vendors.creditRating, - cashFlowRating: vendors.cashFlowRating, - createdAt: vendors.createdAt, - updatedAt: vendors.updatedAt, - + id: sql<number>`${vendors.id}`.as("id"), + vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"), + vendorCode: sql<string>`${vendors.vendorCode}`.as("vendor_code"), + taxId: sql<string>`${vendors.taxId}`.as("tax_id"), + address: sql<string>`${vendors.address}`.as("address"), + country: sql<string>`${vendors.country}`.as("country"), + phone: sql<string>`${vendors.phone}`.as("phone"), + email: sql<string>`${vendors.email}`.as("email"), + businessSize: sql<string>`${vendors.businessSize}`.as("business_size"), + website: sql<string>`${vendors.website}`.as("website"), + status: sql<string>`${vendors.status}`.as("status"), + vendorTypeId: sql<number>`${vendors.vendorTypeId}`.as("vendor_type_id"), + representativeName: sql<string>`${vendors.representativeName}`.as("representative_name"), + representativeBirth: sql<string>`${vendors.representativeBirth}`.as("representative_birth"), + representativeEmail: sql<string>`${vendors.representativeEmail}`.as("representative_email"), + representativePhone: sql<string>`${vendors.representativePhone}`.as("representative_phone"), + corporateRegistrationNumber: sql<string>`${vendors.corporateRegistrationNumber}`.as("corporate_registration_number"), + items: sql<string>`${vendors.items}`.as("items"), + creditAgency: sql<string>`${vendors.creditAgency}`.as("credit_agency"), + creditRating: sql<string>`${vendors.creditRating}`.as("credit_rating"), + cashFlowRating: sql<string>`${vendors.cashFlowRating}`.as("cash_flow_rating"), + createdAt: sql<Date>`${vendors.createdAt}`.as("created_at"), + updatedAt: sql<Date>`${vendors.updatedAt}`.as("updated_at"), + // Vendor type fields - vendorTypeName: vendorTypes.nameKo, - vendorTypeNameEn: vendorTypes.nameEn, - vendorTypeCode: vendorTypes.code, - + vendorTypeName: sql<string>`${vendorTypes.nameKo}`.as("vendor_type_name"), + vendorTypeNameEn: sql<string>`${vendorTypes.nameEn}`.as("vendor_type_name_en"), + vendorTypeCode: sql<string>`${vendorTypes.code}`.as("vendor_type_code"), + // Computed vendor category field - vendorCategory: sql` + vendorCategory: sql<string>` CASE WHEN ${vendors.status} = 'ACTIVE' THEN '정규업체' WHEN ${vendors.status} IN ('INACTIVE', 'BLACKLISTED', 'REJECTED') THEN '' @@ -527,5 +705,7 @@ export const vendorsWithTypesView = pgView("vendors_with_types").as((qb) => { ); }); + // You can also create interfaces for the view -export type VendorWithType = typeof vendorsWithTypesView.$inferSelect;
\ No newline at end of file +export type VendorWithType = typeof vendorsWithTypesView.$inferSelect; + |