윤서의 포트폴리오

ASIS 전체 ERD

erDiagram
    cm_code_group["cm_code_group<br/>(공통_코드그룹)"] {
        number code_group_id PK
        string group_code
        string group_name
    }
    cm_code["cm_code<br/>(공통_코드정보)"] {
        number code_id PK
        string code_group_code
        string code_value
        string code_name
    }
    cm_department["cm_department<br/>(공통_부서정보)"] {
        number dept_id PK
        string dept_code
        string dept_name
    }
    cm_staff["cm_staff<br/>(공통_직원정보)"] {
        number staff_id PK
        string staff_no
        string staff_name
        string dept_code
    }
    cm_patient["cm_patient<br/>(공통_환자정보)"] {
        number patient_id PK
        string patient_no
        string patient_name
        datetime birth_date
    }
    cm_patient_insurance["cm_patient_insurance<br/>(공통_환자보험)"] {
        number patient_insurance_id PK
        number patient_id
        string patient_no
        string payer_name
    }
    cm_room["cm_room<br/>(공통_병실정보)"] {
        number room_id PK
        string room_code
        string room_name
        string dept_code
    }

    gn_appointment["gn_appointment<br/>(일반_예약정보)"] {
        number appointment_id PK
        string appointment_no
        string patient_no
        string dept_code
        string doctor_no
        datetime appointment_at
    }
    gn_checkin["gn_checkin<br/>(일반_체크인정보)"] {
        number checkin_id PK
        string appointment_no
        string patient_no
        datetime checked_in_at
    }
    gn_waiting_queue["gn_waiting_queue<br/>(일반_대기열)"] {
        number queue_id PK
        string patient_no
        string dept_code
        number queue_no
    }
    gn_patient_notice["gn_patient_notice<br/>(일반_환자안내알림)"] {
        number notice_id PK
        string patient_no
        string notice_type_cd
        datetime sent_at
    }

    ad_encounter["ad_encounter<br/>(원무_내원정보)"] {
        number encounter_id PK
        string encounter_no
        string patient_no
        string appointment_no
        string dept_code
    }
    ad_admission["ad_admission<br/>(원무_입원정보)"] {
        number admission_id PK
        string encounter_no
        string patient_no
        string room_code
    }
    ad_order_charge["ad_order_charge<br/>(원무_오더청구내역)"] {
        number order_charge_id PK
        string encounter_no
        string charge_code
        string source_no
    }
    ad_bill["ad_bill<br/>(원무_청구서)"] {
        number bill_id PK
        string bill_no
        string encounter_no
        string patient_no
    }
    ad_payment["ad_payment<br/>(원무_수납정보)"] {
        number payment_id PK
        string bill_no
        string patient_no
        number payment_amount
    }
    ad_insurance_claim["ad_insurance_claim<br/>(원무_보험청구)"] {
        number claim_id PK
        string bill_no
        string patient_no
        string payer_code
    }

    cl_vital_sign["cl_vital_sign<br/>(진료_바이탈사인)"] {
        number vital_sign_id PK
        string encounter_no
        string patient_no
        datetime measured_at
    }
    cl_clinical_note["cl_clinical_note<br/>(진료_진료기록)"] {
        number note_id PK
        string encounter_no
        string patient_no
        string author_no
    }
    cl_diagnosis["cl_diagnosis<br/>(진료_진단정보)"] {
        number diagnosis_id PK
        string encounter_no
        string patient_no
        string icd_code
    }
    cl_medication_order["cl_medication_order<br/>(진료_투약오더)"] {
        number medication_order_id PK
        string order_no
        string encounter_no
        string patient_no
        string medication_code
    }
    cl_procedure_order["cl_procedure_order<br/>(진료_처치오더)"] {
        number procedure_order_id PK
        string order_no
        string encounter_no
        string patient_no
        string procedure_code
    }

    cs_specimen["cs_specimen<br/>(진료지원_검체정보)"] {
        number specimen_id PK
        string specimen_no
        string patient_no
        string encounter_no
        string order_no
    }
    cs_lab_order["cs_lab_order<br/>(진료지원_검사오더)"] {
        number lab_order_id PK
        string lab_order_no
        string encounter_no
        string patient_no
        string procedure_order_no
        string specimen_no
    }
    cs_lab_result["cs_lab_result<br/>(진료지원_검사결과)"] {
        number lab_result_id PK
        string lab_order_no
        string analyte_code
    }
    cs_imaging_order["cs_imaging_order<br/>(진료지원_영상오더)"] {
        number imaging_order_id PK
        string imaging_order_no
        string encounter_no
        string patient_no
        string procedure_order_no
    }
    cs_imaging_result["cs_imaging_result<br/>(진료지원_영상판독결과)"] {
        number imaging_result_id PK
        string imaging_order_no
        string report_status_cd
    }
    cs_dispense["cs_dispense<br/>(진료지원_조제정보)"] {
        number dispense_id PK
        string dispense_no
        string order_no
        string patient_no
    }

    cm_code_group ||--o{ cm_code : logical
    cm_department ||--o{ cm_staff : logical
    cm_department ||--o{ cm_room : logical
    cm_patient ||--o{ cm_patient_insurance : logical

    cm_patient ||--o{ gn_appointment : logical
    cm_staff ||--o{ gn_appointment : logical
    cm_department ||--o{ gn_appointment : logical
    gn_appointment ||--o{ gn_checkin : logical
    cm_patient ||--o{ gn_checkin : logical
    cm_patient ||--o{ gn_waiting_queue : logical
    cm_department ||--o{ gn_waiting_queue : logical
    cm_patient ||--o{ gn_patient_notice : logical

    gn_appointment ||--o{ ad_encounter : logical
    cm_patient ||--o{ ad_encounter : logical
    cm_department ||--o{ ad_encounter : logical
    ad_encounter ||--o{ ad_admission : logical
    cm_room ||--o{ ad_admission : logical
    ad_encounter ||--o{ ad_order_charge : logical
    ad_encounter ||--o| ad_bill : logical
    ad_bill ||--o{ ad_payment : logical
    ad_bill ||--o{ ad_insurance_claim : logical

    ad_encounter ||--o{ cl_vital_sign : logical
    ad_encounter ||--o{ cl_clinical_note : logical
    ad_encounter ||--o{ cl_diagnosis : logical
    ad_encounter ||--o{ cl_medication_order : logical
    ad_encounter ||--o{ cl_procedure_order : logical

    cl_procedure_order ||--o{ cs_specimen : logical
    cs_specimen ||--o{ cs_lab_order : logical
    cl_procedure_order ||--o{ cs_lab_order : logical
    cs_lab_order ||--o{ cs_lab_result : logical
    cl_procedure_order ||--o{ cs_imaging_order : logical
    cs_imaging_order ||--o| cs_imaging_result : logical
    cl_medication_order ||--o{ cs_dispense : logical

PostgreSQL TOBE ERD

postgresql_erd.md

erDiagram
    tb_cd_grp["tb_cd_grp<br/>(코드 그룹 마스터)"] {
        string grp_cd PK
        string grp_nm
    }
    tb_cd["tb_cd<br/>(공통 코드 마스터)"] {
        string grp_cd PK
        string cd_val PK
        string cd_nm
    }
    tb_org["tb_org<br/>(기관 마스터)"] {
        string org_cd PK
        string org_nm
    }
    tb_dept["tb_dept<br/>(부서 마스터)"] {
        string dept_cd PK
        string org_cd FK
        string upr_dept_cd FK
        string dept_nm
    }
    tb_emp["tb_emp<br/>(직원 마스터)"] {
        string emp_no PK
        string org_cd FK
        string dept_cd FK
        string emp_nm
    }
    tb_pt["tb_pt<br/>(환자 마스터)"] {
        string pt_no PK
        string org_cd FK
        string pt_nm
    }
    tb_pt_cntc["tb_pt_cntc<br/>(환자 연락처)"] {
        string pt_no PK
        int cntc_seq PK
        string cntc_nm
    }
    tb_insr_pyr["tb_insr_pyr<br/>(보험자 마스터)"] {
        string pyr_cd PK
        string pyr_nm
    }
    tb_pt_insr["tb_pt_insr<br/>(환자 보험)"] {
        string pt_no PK
        string pyr_cd PK
        date eff_st_dt PK
    }
    tb_wd["tb_wd<br/>(병동 마스터)"] {
        string wd_cd PK
        string org_cd FK
        string dept_cd FK
    }
    tb_room["tb_room<br/>(병실 마스터)"] {
        string room_cd PK
        string wd_cd FK
        string dept_cd FK
    }

    tb_apnt["tb_apnt<br/>(예약 정보)"] {
        string apnt_no PK
        string org_cd FK
        string pt_no FK
        string dept_cd FK
        string dr_emp_no FK
    }
    tb_ckin["tb_ckin<br/>(체크인 정보)"] {
        string ckin_no PK
        string apnt_no FK
        string pt_no FK
    }
    tb_wait_q["tb_wait_q<br/>(대기열)"] {
        string wait_no PK
        string apnt_no FK
        string pt_no FK
        string dept_cd FK
    }
    tb_pt_ntf["tb_pt_ntf<br/>(환자 안내 알림)"] {
        string pt_ntf_no PK
        string pt_no FK
        string apnt_no FK
    }

    tb_enc["tb_enc<br/>(내원/진료 에피소드)"] {
        string enc_no PK
        string org_cd FK
        string pt_no FK
        string apnt_no FK
        string dept_cd FK
    }
    tb_adm["tb_adm<br/>(입원 정보)"] {
        string adm_no PK
        string enc_no FK
        string pt_no FK
        string room_cd FK
    }
    tb_chg_itm["tb_chg_itm<br/>(청구 항목 마스터)"] {
        string chg_cd PK
        string chg_nm
    }
    tb_enc_chg["tb_enc_chg<br/>(에피소드별 청구 내역)"] {
        string enc_no PK
        int chg_seq PK
        string chg_cd FK
    }
    tb_bill["tb_bill<br/>(수납 청구서)"] {
        string bill_no PK
        string enc_no FK
        string pt_no FK
    }
    tb_pay["tb_pay<br/>(수납 정보)"] {
        string pay_no PK
        string bill_no FK
    }
    tb_clm["tb_clm<br/>(보험 청구)"] {
        string clm_no PK
        string bill_no FK
        string pt_no FK
        string pyr_cd FK
        date eff_st_dt FK
    }

    tb_dx_mst["tb_dx_mst<br/>(진단 마스터)"] {
        string dx_cd PK
        string dx_nm
    }
    tb_med_mst["tb_med_mst<br/>(약품 마스터)"] {
        string med_cd PK
        string med_nm
    }
    tb_proc_mst["tb_proc_mst<br/>(처치/검사 오더 마스터)"] {
        string proc_cd PK
        string proc_nm
    }
    tb_vs["tb_vs<br/>(바이탈 사인)"] {
        string enc_no PK
        datetime msr_dtm PK
        string pt_no FK
    }
    tb_note["tb_note<br/>(진료 기록 노트)"] {
        string note_no PK
        string enc_no FK
        string pt_no FK
    }
    tb_enc_dx["tb_enc_dx<br/>(에피소드 진단)"] {
        string enc_no PK
        string dx_cd PK
        string dx_tp_cd PK
        string pt_no FK
    }
    tb_rx_ord["tb_rx_ord<br/>(투약 오더)"] {
        string ord_no PK
        string enc_no FK
        string pt_no FK
        string med_cd FK
    }
    tb_proc_ord["tb_proc_ord<br/>(처치 오더)"] {
        string ord_no PK
        string enc_no FK
        string pt_no FK
        string proc_cd FK
    }

    tb_spcm["tb_spcm<br/>(검체 정보)"] {
        string spcm_no PK
        string pt_no FK
        string enc_no FK
        string proc_ord_no FK
    }
    tb_lab_tst_mst["tb_lab_tst_mst<br/>(검사 항목 마스터)"] {
        string tst_cd PK
        string tst_nm
    }
    tb_lab_ord["tb_lab_ord<br/>(검사 오더)"] {
        string lab_ord_no PK
        string enc_no FK
        string pt_no FK
        string proc_ord_no FK
        string spcm_no FK
        string tst_cd FK
    }
    tb_lab_rslt["tb_lab_rslt<br/>(검사 결과)"] {
        string lab_ord_no PK
        int anl_seq PK
    }
    tb_img_exam_mst["tb_img_exam_mst<br/>(영상 검사 마스터)"] {
        string exam_cd PK
        string exam_nm
    }
    tb_img_ord["tb_img_ord<br/>(영상 오더)"] {
        string img_ord_no PK
        string enc_no FK
        string pt_no FK
        string proc_ord_no FK
        string exam_cd FK
    }
    tb_img_rslt["tb_img_rslt<br/>(영상 판독 결과)"] {
        string img_ord_no PK
        string rpt_st_cd
    }
    tb_dsps["tb_dsps<br/>(조제 정보)"] {
        string dsps_no PK
        string rx_ord_no FK
        string pt_no FK
    }

    tb_cd_grp ||--o{ tb_cd : has
    tb_org ||--o{ tb_dept : has
    tb_dept ||--o{ tb_dept : parent
    tb_org ||--o{ tb_emp : has
    tb_dept ||--o{ tb_emp : belongs
    tb_org ||--o{ tb_pt : has
    tb_pt ||--o{ tb_pt_cntc : has
    tb_insr_pyr ||--o{ tb_pt_insr : covers
    tb_pt ||--o{ tb_pt_insr : has
    tb_org ||--o{ tb_wd : has
    tb_dept ||--o{ tb_wd : belongs
    tb_wd ||--o{ tb_room : has
    tb_dept ||--o{ tb_room : belongs

    tb_pt ||--o{ tb_apnt : books
    tb_dept ||--o{ tb_apnt : receives
    tb_emp ||--o{ tb_apnt : attends
    tb_apnt ||--o{ tb_ckin : starts
    tb_pt ||--o{ tb_ckin : checks
    tb_apnt ||--o{ tb_wait_q : queues
    tb_pt ||--o{ tb_pt_ntf : notified

    tb_apnt ||--o{ tb_enc : converts
    tb_pt ||--o{ tb_enc : has
    tb_dept ||--o{ tb_enc : owns
    tb_enc ||--o{ tb_adm : admits
    tb_room ||--o{ tb_adm : assigned
    tb_chg_itm ||--o{ tb_enc_chg : billed
    tb_enc ||--o{ tb_enc_chg : charged
    tb_enc ||--o| tb_bill : billed
    tb_bill ||--o{ tb_pay : paid
    tb_bill ||--o{ tb_clm : claims
    tb_pt_insr ||--o{ tb_clm : uses

    tb_enc ||--o{ tb_vs : measures
    tb_enc ||--o{ tb_note : notes
    tb_dx_mst ||--o{ tb_enc_dx : diagnoses
    tb_enc ||--o{ tb_enc_dx : has
    tb_med_mst ||--o{ tb_rx_ord : orders
    tb_enc ||--o{ tb_rx_ord : has
    tb_proc_mst ||--o{ tb_proc_ord : orders
    tb_enc ||--o{ tb_proc_ord : has

    tb_proc_ord ||--o{ tb_spcm : produces
    tb_spcm ||--o{ tb_lab_ord : sampled
    tb_lab_tst_mst ||--o{ tb_lab_ord : ordered
    tb_lab_ord ||--o{ tb_lab_rslt : results
    tb_img_exam_mst ||--o{ tb_img_ord : ordered
    tb_img_ord ||--o| tb_img_rslt : results
    tb_rx_ord ||--o{ tb_dsps : dispensed

MySQL DW ERD

mysql_erd.md

erDiagram
    dim_date["dim_date<br/>(날짜 차원)"] {
        int date_key PK
        date full_date
    }
    dim_org["dim_org<br/>(기관 차원)"] {
        int org_sk PK
        string org_cd
        string org_nm
    }
    dim_dept["dim_dept<br/>(부서 차원)"] {
        int dept_sk PK
        int org_sk FK
        string dept_cd
        string dept_nm
    }
    dim_emp["dim_emp<br/>(직원 차원)"] {
        int emp_sk PK
        int org_sk FK
        int dept_sk FK
        string emp_no
        string emp_nm
    }
    dim_patient["dim_patient<br/>(환자 차원)"] {
        int patient_sk PK
        int org_sk FK
        string pt_no
        string pt_nm
    }
    dim_payer["dim_payer<br/>(보험자 차원)"] {
        int payer_sk PK
        string pyr_cd
        string pyr_nm
    }
    dim_room["dim_room<br/>(병실 차원)"] {
        int room_sk PK
        int dept_sk FK
        string room_cd
        string room_nm
    }
    dim_diagnosis["dim_diagnosis<br/>(진단 차원)"] {
        int diagnosis_sk PK
        string dx_cd
        string dx_nm
    }
    dim_medication["dim_medication<br/>(약품 차원)"] {
        int medication_sk PK
        string med_cd
        string med_nm
    }
    dim_procedure["dim_procedure<br/>(처치 차원)"] {
        int procedure_sk PK
        string proc_cd
        string proc_nm
    }
    dim_lab_test["dim_lab_test<br/>(검사 항목 차원)"] {
        int lab_test_sk PK
        string tst_cd
        string tst_nm
    }
    dim_imaging_exam["dim_imaging_exam<br/>(영상 검사 차원)"] {
        int imaging_exam_sk PK
        string exam_cd
        string exam_nm
    }

    fact_encounter["fact_encounter<br/>(내원 팩트)"] {
        int encounter_fact_id PK
        string enc_no
        int org_sk FK
        int dept_sk FK
        int patient_sk FK
        int attending_emp_sk FK
        int arrive_date_key FK
        int close_date_key FK
    }
    fact_billing["fact_billing<br/>(원무 수익 팩트)"] {
        int billing_fact_id PK
        string bill_no
        int org_sk FK
        int dept_sk FK
        int patient_sk FK
        int payer_sk FK
        int issue_date_key FK
        int close_date_key FK
    }
    fact_medication_order["fact_medication_order<br/>(투약 오더 팩트)"] {
        int medication_fact_id PK
        string ord_no
        int org_sk FK
        int dept_sk FK
        int patient_sk FK
        int ordering_emp_sk FK
        int medication_sk FK
        int order_date_key FK
        int stop_date_key FK
    }
    fact_procedure_order["fact_procedure_order<br/>(처치 오더 팩트)"] {
        int procedure_fact_id PK
        string ord_no
        int org_sk FK
        int dept_sk FK
        int patient_sk FK
        int ordering_emp_sk FK
        int procedure_sk FK
        int request_date_key FK
        int perform_date_key FK
    }
    fact_lab_result["fact_lab_result<br/>(검사 결과 팩트)"] {
        int lab_result_fact_id PK
        string lab_ord_no
        int anl_seq
        int org_sk FK
        int dept_sk FK
        int patient_sk FK
        int ordering_emp_sk FK
        int lab_test_sk FK
        int order_date_key FK
        int result_date_key FK
    }
    fact_imaging_exam["fact_imaging_exam<br/>(영상 검사 팩트)"] {
        int imaging_fact_id PK
        string img_ord_no
        int org_sk FK
        int dept_sk FK
        int patient_sk FK
        int ordering_emp_sk FK
        int imaging_exam_sk FK
        int schedule_date_key FK
        int start_date_key FK
        int end_date_key FK
        int report_date_key FK
    }
    fact_dispense["fact_dispense<br/>(조제 팩트)"] {
        int dispense_fact_id PK
        string dsps_no
        string rx_ord_no
        int org_sk FK
        int dept_sk FK
        int patient_sk FK
        int medication_sk FK
        int pharmacist_emp_sk FK
        int dispense_date_key FK
    }

    dim_org ||--o{ dim_dept : has
    dim_org ||--o{ dim_emp : has
    dim_dept ||--o{ dim_emp : belongs
    dim_org ||--o{ dim_patient : has
    dim_dept ||--o{ dim_room : has

    dim_org ||--o{ fact_encounter : occurs
    dim_dept ||--o{ fact_encounter : occurs
    dim_patient ||--o{ fact_encounter : visits
    dim_emp ||--o{ fact_encounter : attends
    dim_date ||--o{ fact_encounter : dates

    dim_org ||--o{ fact_billing : billed
    dim_dept ||--o{ fact_billing : billed
    dim_patient ||--o{ fact_billing : billed
    dim_payer ||--o{ fact_billing : covers
    dim_date ||--o{ fact_billing : dates

    dim_org ||--o{ fact_medication_order : orders
    dim_dept ||--o{ fact_medication_order : orders
    dim_patient ||--o{ fact_medication_order : orders
    dim_emp ||--o{ fact_medication_order : orders
    dim_medication ||--o{ fact_medication_order : ordered
    dim_date ||--o{ fact_medication_order : dates

    dim_org ||--o{ fact_procedure_order : orders
    dim_dept ||--o{ fact_procedure_order : orders
    dim_patient ||--o{ fact_procedure_order : orders
    dim_emp ||--o{ fact_procedure_order : orders
    dim_procedure ||--o{ fact_procedure_order : ordered
    dim_date ||--o{ fact_procedure_order : dates

    dim_org ||--o{ fact_lab_result : results
    dim_dept ||--o{ fact_lab_result : results
    dim_patient ||--o{ fact_lab_result : results
    dim_emp ||--o{ fact_lab_result : orders
    dim_lab_test ||--o{ fact_lab_result : tests
    dim_date ||--o{ fact_lab_result : dates

    dim_org ||--o{ fact_imaging_exam : exams
    dim_dept ||--o{ fact_imaging_exam : exams
    dim_patient ||--o{ fact_imaging_exam : exams
    dim_emp ||--o{ fact_imaging_exam : orders
    dim_imaging_exam ||--o{ fact_imaging_exam : ordered
    dim_date ||--o{ fact_imaging_exam : dates

    dim_org ||--o{ fact_dispense : dispenses
    dim_dept ||--o{ fact_dispense : dispenses
    dim_patient ||--o{ fact_dispense : dispenses
    dim_emp ||--o{ fact_dispense : dispenses
    dim_medication ||--o{ fact_dispense : meds
    dim_date ||--o{ fact_dispense : dates

MySQL DM Lineage ERD

mysql_erd.md

flowchart TB
    subgraph SOURCE["DW / STAGE 원천"]
        fact_billing["<div class='source-table-node'><div class='source-table-title'>fact_billing</div><div class='source-table-row key'>PK billing_fact_id</div><div class='source-table-row'>bill_no</div><div class='source-table-row'>enc_no</div><div class='source-table-row'>issue_date_key</div><div class='source-table-row'>org_sk</div><div class='source-table-row'>dept_sk</div><div class='source-table-row'>total_charge_amt</div><div class='source-table-row'>insurance_amt</div><div class='source-table-row'>patient_pay_amt</div><div class='source-table-row'>receipt_amt</div><div class='source-table-row'>claim_amt</div></div>"]
        fact_encounter["<div class='source-table-node'><div class='source-table-title'>fact_encounter</div><div class='source-table-row key'>PK encounter_fact_id</div><div class='source-table-row'>enc_no</div><div class='source-table-row'>enc_tp_cd</div><div class='source-table-row'>arrive_date_key</div><div class='source-table-row'>close_date_key</div><div class='source-table-row'>org_sk</div><div class='source-table-row'>dept_sk</div><div class='source-table-row'>patient_sk</div></div>"]
        fact_medication_order["<div class='source-table-node'><div class='source-table-title'>fact_medication_order</div><div class='source-table-row key'>PK medication_fact_id</div><div class='source-table-row'>ord_no</div><div class='source-table-row'>order_date_key</div><div class='source-table-row'>org_sk</div><div class='source-table-row'>dept_sk</div><div class='source-table-row'>medication_sk</div><div class='source-table-row'>qty</div></div>"]
        fact_procedure_order["<div class='source-table-node'><div class='source-table-title'>fact_procedure_order</div><div class='source-table-row key'>PK procedure_fact_id</div><div class='source-table-row'>ord_no</div><div class='source-table-row'>request_date_key</div><div class='source-table-row'>org_sk</div><div class='source-table-row'>dept_sk</div></div>"]
        fact_lab_result["<div class='source-table-node'><div class='source-table-title'>fact_lab_result</div><div class='source-table-row key'>PK lab_result_fact_id</div><div class='source-table-row'>lab_ord_no</div><div class='source-table-row'>anl_seq</div><div class='source-table-row'>lab_test_sk</div><div class='source-table-row'>abnormal_yn</div></div>"]
        fact_dispense["<div class='source-table-node'><div class='source-table-title'>fact_dispense</div><div class='source-table-row key'>PK dispense_fact_id</div><div class='source-table-row'>dsps_no</div><div class='source-table-row'>rx_ord_no</div><div class='source-table-row'>medication_sk</div><div class='source-table-row'>dsps_qty</div></div>"]
        stg_tobe_enc["<div class='source-table-node'><div class='source-table-title'>stg_tobe_enc</div><div class='source-table-row key'>PK enc_no</div><div class='source-table-row'>org_cd</div><div class='source-table-row'>dept_cd</div></div>"]
        stg_tobe_lab_ord["<div class='source-table-node'><div class='source-table-title'>stg_tobe_lab_ord</div><div class='source-table-row key'>PK lab_ord_no</div><div class='source-table-row'>enc_no</div><div class='source-table-row'>ord_dtm</div></div>"]
        stg_tobe_lab_rslt["<div class='source-table-node'><div class='source-table-title'>stg_tobe_lab_rslt</div><div class='source-table-row key'>PK lab_ord_no</div><div class='source-table-row key'>PK anl_seq</div><div class='source-table-row'>rslt_dtm</div></div>"]
        dim_date["<div class='source-table-node'><div class='source-table-title'>dim_date</div><div class='source-table-row key'>PK date_key</div><div class='source-table-row'>full_date</div></div>"]
        dim_org["<div class='source-table-node'><div class='source-table-title'>dim_org</div><div class='source-table-row key'>PK org_sk</div><div class='source-table-row'>org_cd</div></div>"]
        dim_dept["<div class='source-table-node'><div class='source-table-title'>dim_dept</div><div class='source-table-row key'>PK dept_sk</div><div class='source-table-row'>dept_cd</div><div class='source-table-row'>dept_nm</div></div>"]
        dim_medication["<div class='source-table-node'><div class='source-table-title'>dim_medication</div><div class='source-table-row key'>PK medication_sk</div><div class='source-table-row'>med_cd</div><div class='source-table-row'>med_nm</div></div>"]
        dim_lab_test["<div class='source-table-node'><div class='source-table-title'>dim_lab_test</div><div class='source-table-row key'>PK lab_test_sk</div><div class='source-table-row'>tst_cd</div><div class='source-table-row'>tst_nm</div><div class='source-table-row'>tst_grp_cd</div><div class='source-table-row'>rslt_unit_cd</div></div>"]
    end

    subgraph DM["Data Mart 집계 결과"]
        dm_daily_revenue_by_dept["<div class='dm-table-node'><div class='dm-table-title'>dm_daily_revenue_by_dept</div><div class='dm-table-row pk'>PK revenue_dt</div><div class='dm-table-row pk'>PK org_cd</div><div class='dm-table-row pk'>PK dept_cd</div><div class='dm-table-row'>dept_nm</div><div class='dm-table-row'>outpatient_cnt</div><div class='dm-table-row'>inpatient_cnt</div><div class='dm-table-row'>total_charge_amt</div><div class='dm-table-row'>insurance_amt</div><div class='dm-table-row'>patient_pay_amt</div><div class='dm-table-row'>receipt_amt</div><div class='dm-table-row'>claim_amt</div></div>"]
        dm_monthly_clinical_kpi["<div class='dm-table-node'><div class='dm-table-title'>dm_monthly_clinical_kpi</div><div class='dm-table-row pk'>PK yyyymm</div><div class='dm-table-row pk'>PK org_cd</div><div class='dm-table-row pk'>PK dept_cd</div><div class='dm-table-row'>dept_nm</div><div class='dm-table-row'>encounter_cnt</div><div class='dm-table-row'>patient_cnt</div><div class='dm-table-row'>medication_order_cnt</div><div class='dm-table-row'>procedure_order_cnt</div><div class='dm-table-row'>avg_order_per_encounter</div></div>"]
        dm_daily_lab_tat["<div class='dm-table-node'><div class='dm-table-title'>dm_daily_lab_tat</div><div class='dm-table-row pk'>PK tat_dt</div><div class='dm-table-row pk'>PK org_cd</div><div class='dm-table-row pk'>PK dept_cd</div><div class='dm-table-row pk'>PK test_group_cd</div><div class='dm-table-row'>order_cnt</div><div class='dm-table-row'>result_cnt</div><div class='dm-table-row'>abnormal_cnt</div><div class='dm-table-row'>avg_tat_minutes</div></div>"]
        dm_monthly_top_medication["<div class='dm-table-node'><div class='dm-table-title'>dm_monthly_top_medication</div><div class='dm-table-row pk'>PK yyyymm</div><div class='dm-table-row pk'>PK org_cd</div><div class='dm-table-row pk'>PK dept_cd</div><div class='dm-table-row pk'>PK med_cd</div><div class='dm-table-row'>med_nm</div><div class='dm-table-row'>order_cnt</div><div class='dm-table-row'>order_qty</div><div class='dm-table-row'>dispense_qty</div><div class='dm-table-row'>rank_no</div></div>"]
        dm_monthly_patient_flow["<div class='dm-table-node'><div class='dm-table-title'>dm_monthly_patient_flow</div><div class='dm-table-row pk'>PK yyyymm</div><div class='dm-table-row pk'>PK org_cd</div><div class='dm-table-row pk'>PK dept_cd</div><div class='dm-table-row'>new_patient_cnt</div><div class='dm-table-row'>revisit_patient_cnt</div><div class='dm-table-row'>admission_cnt</div><div class='dm-table-row'>discharge_cnt</div><div class='dm-table-row'>emergency_cnt</div></div>"]
    end

    classDef dmTable fill:transparent,stroke:transparent,color:#111827
    classDef sourceTable fill:transparent,stroke:transparent,color:#111827
    class fact_billing,fact_encounter,fact_medication_order,fact_procedure_order,fact_lab_result,fact_dispense,stg_tobe_enc,stg_tobe_lab_ord,stg_tobe_lab_rslt,dim_date,dim_org,dim_dept,dim_medication,dim_lab_test sourceTable
    class dm_daily_revenue_by_dept,dm_monthly_clinical_kpi,dm_daily_lab_tat,dm_monthly_top_medication,dm_monthly_patient_flow dmTable

    fact_billing -->|금액 SUM| dm_daily_revenue_by_dept
    fact_encounter -.->|enc_no 기준 외래/입원 건수| dm_daily_revenue_by_dept
    dim_date -->|issue_date_key -> revenue_dt| dm_daily_revenue_by_dept
    dim_org -->|org_sk -> org_cd| dm_daily_revenue_by_dept
    dim_dept -->|dept_sk -> dept_cd/dept_nm| dm_daily_revenue_by_dept

    fact_encounter -->|내원/환자 COUNT| dm_monthly_clinical_kpi
    fact_medication_order -->|투약 오더 COUNT| dm_monthly_clinical_kpi
    fact_procedure_order -->|처치 오더 COUNT| dm_monthly_clinical_kpi
    dim_date -->|arrive/order/request date -> yyyymm| dm_monthly_clinical_kpi
    dim_org -->|org_sk -> org_cd| dm_monthly_clinical_kpi
    dim_dept -->|dept_sk -> dept_cd/dept_nm| dm_monthly_clinical_kpi

    stg_tobe_lab_ord -->|ord_dtm -> tat_dt, order_cnt| dm_daily_lab_tat
    stg_tobe_lab_rslt -->|rslt_dtm -> TAT, result_cnt| dm_daily_lab_tat
    fact_lab_result -->|abnormal_yn, lab_test_sk| dm_daily_lab_tat
    stg_tobe_enc -->|enc_no -> org_cd/dept_cd| dm_daily_lab_tat
    dim_lab_test -->|tst_grp_cd -> test_group_cd| dm_daily_lab_tat
    dim_org -->|org_cd 매핑| dm_daily_lab_tat
    dim_dept -->|dept_cd 매핑| dm_daily_lab_tat

    fact_medication_order -->|약품별 주문/수량 집계| dm_monthly_top_medication
    fact_dispense -.->|rx_ord_no 기준 조제 수량| dm_monthly_top_medication
    dim_medication -->|med_cd, med_nm| dm_monthly_top_medication
    dim_date -->|order_date_key -> yyyymm| dm_monthly_top_medication
    dim_org -->|org_sk -> org_cd| dm_monthly_top_medication
    dim_dept -->|dept_sk -> dept_cd| dm_monthly_top_medication

    fact_encounter -->|신환/재진/입원/퇴원/응급 집계| dm_monthly_patient_flow
    dim_date -->|arrive_date_key, close_date_key -> yyyymm| dm_monthly_patient_flow
    dim_org -->|org_sk -> org_cd| dm_monthly_patient_flow
    dim_dept -->|dept_sk -> dept_cd| dm_monthly_patient_flow

ASIS 공통 ERD

asis_erd_common.md

erDiagram
    cm_code_group["cm_code_group<br/>(공통_코드그룹)"] {
        number code_group_id PK
        string group_code
        string group_name
    }
    cm_code["cm_code<br/>(공통_코드정보)"] {
        number code_id PK
        string code_group_code
        string code_value
        string code_name
    }
    cm_department["cm_department<br/>(공통_부서정보)"] {
        number dept_id PK
        string dept_code
        string dept_name
    }
    cm_staff["cm_staff<br/>(공통_직원정보)"] {
        number staff_id PK
        string staff_no
        string staff_name
        string dept_code
    }
    cm_patient["cm_patient<br/>(공통_환자정보)"] {
        number patient_id PK
        string patient_no
        string patient_name
    }
    cm_patient_insurance["cm_patient_insurance<br/>(공통_환자보험)"] {
        number patient_insurance_id PK
        string patient_no
        string payer_name
        datetime effective_from
    }
    cm_room["cm_room<br/>(공통_병실정보)"] {
        number room_id PK
        string room_code
        string room_name
        string dept_code
    }

    cm_code_group ||--o{ cm_code : logical
    cm_department ||--o{ cm_staff : logical
    cm_department ||--o{ cm_room : logical
    cm_patient ||--o{ cm_patient_insurance : logical

ASIS 일반 ERD

asis_erd_general.md

erDiagram
    cm_patient["cm_patient<br/>(공통_환자정보)"] {
        string patient_no PK
        string patient_name
    }
    cm_department["cm_department<br/>(공통_부서정보)"] {
        string dept_code PK
        string dept_name
    }
    cm_staff["cm_staff<br/>(공통_직원정보)"] {
        string staff_no PK
        string staff_name
    }
    gn_appointment["gn_appointment<br/>(일반_예약정보)"] {
        number appointment_id PK
        string appointment_no
        string patient_no
        string dept_code
        string doctor_no
    }
    gn_checkin["gn_checkin<br/>(일반_체크인정보)"] {
        number checkin_id PK
        string appointment_no
        string patient_no
    }
    gn_waiting_queue["gn_waiting_queue<br/>(일반_대기열)"] {
        number queue_id PK
        string patient_no
        string dept_code
        number queue_no
    }
    gn_patient_notice["gn_patient_notice<br/>(일반_환자안내알림)"] {
        number notice_id PK
        string patient_no
        string notice_type_cd
    }

    cm_patient ||--o{ gn_appointment : logical
    cm_department ||--o{ gn_appointment : logical
    cm_staff ||--o{ gn_appointment : logical
    gn_appointment ||--o{ gn_checkin : logical
    cm_patient ||--o{ gn_waiting_queue : logical
    cm_department ||--o{ gn_waiting_queue : logical
    cm_patient ||--o{ gn_patient_notice : logical

ASIS 원무 ERD

asis_erd_administration.md

erDiagram
    cm_patient["cm_patient<br/>(공통_환자정보)"] {
        string patient_no PK
    }
    cm_room["cm_room<br/>(공통_병실정보)"] {
        string room_code PK
    }
    gn_appointment["gn_appointment<br/>(일반_예약정보)"] {
        string appointment_no PK
    }
    ad_encounter["ad_encounter<br/>(원무_내원정보)"] {
        number encounter_id PK
        string encounter_no
        string patient_no
        string appointment_no
    }
    ad_admission["ad_admission<br/>(원무_입원정보)"] {
        number admission_id PK
        string encounter_no
        string patient_no
        string room_code
    }
    ad_order_charge["ad_order_charge<br/>(원무_오더청구내역)"] {
        number order_charge_id PK
        string encounter_no
        string charge_code
    }
    ad_bill["ad_bill<br/>(원무_청구서)"] {
        number bill_id PK
        string bill_no
        string encounter_no
    }
    ad_payment["ad_payment<br/>(원무_수납정보)"] {
        number payment_id PK
        string bill_no
    }
    ad_insurance_claim["ad_insurance_claim<br/>(원무_보험청구)"] {
        number claim_id PK
        string bill_no
    }

    gn_appointment ||--o{ ad_encounter : logical
    cm_patient ||--o{ ad_encounter : logical
    ad_encounter ||--o{ ad_admission : logical
    cm_room ||--o{ ad_admission : logical
    ad_encounter ||--o{ ad_order_charge : logical
    ad_encounter ||--o| ad_bill : logical
    ad_bill ||--o{ ad_payment : logical
    ad_bill ||--o{ ad_insurance_claim : logical

ASIS 진료 ERD

asis_erd_clinical.md

erDiagram
    ad_encounter["ad_encounter<br/>(원무_내원정보)"] {
        string encounter_no PK
        string patient_no
    }
    cl_vital_sign["cl_vital_sign<br/>(진료_바이탈사인)"] {
        number vital_sign_id PK
        string encounter_no
        string patient_no
    }
    cl_clinical_note["cl_clinical_note<br/>(진료_진료기록)"] {
        number note_id PK
        string encounter_no
        string patient_no
        string author_no
    }
    cl_diagnosis["cl_diagnosis<br/>(진료_진단정보)"] {
        number diagnosis_id PK
        string encounter_no
        string icd_code
    }
    cl_medication_order["cl_medication_order<br/>(진료_투약오더)"] {
        number medication_order_id PK
        string order_no
        string encounter_no
        string medication_code
    }
    cl_procedure_order["cl_procedure_order<br/>(진료_처치오더)"] {
        number procedure_order_id PK
        string order_no
        string encounter_no
        string procedure_code
    }

    ad_encounter ||--o{ cl_vital_sign : logical
    ad_encounter ||--o{ cl_clinical_note : logical
    ad_encounter ||--o{ cl_diagnosis : logical
    ad_encounter ||--o{ cl_medication_order : logical
    ad_encounter ||--o{ cl_procedure_order : logical

ASIS 진료지원 ERD

asis_erd_clinical_support.md

erDiagram
    cl_procedure_order["cl_procedure_order<br/>(진료_처치오더)"] {
        string order_no PK
    }
    cl_medication_order["cl_medication_order<br/>(진료_투약오더)"] {
        string order_no PK
    }
    cs_specimen["cs_specimen<br/>(진료지원_검체정보)"] {
        number specimen_id PK
        string specimen_no
        string order_no
    }
    cs_lab_order["cs_lab_order<br/>(진료지원_검사오더)"] {
        number lab_order_id PK
        string lab_order_no
        string procedure_order_no
        string specimen_no
    }
    cs_lab_result["cs_lab_result<br/>(진료지원_검사결과)"] {
        number lab_result_id PK
        string lab_order_no
        string analyte_code
    }
    cs_imaging_order["cs_imaging_order<br/>(진료지원_영상오더)"] {
        number imaging_order_id PK
        string imaging_order_no
        string procedure_order_no
    }
    cs_imaging_result["cs_imaging_result<br/>(진료지원_영상판독결과)"] {
        number imaging_result_id PK
        string imaging_order_no
    }
    cs_dispense["cs_dispense<br/>(진료지원_조제정보)"] {
        number dispense_id PK
        string dispense_no
        string order_no
    }

    cl_procedure_order ||--o{ cs_specimen : logical
    cs_specimen ||--o{ cs_lab_order : logical
    cl_procedure_order ||--o{ cs_lab_order : logical
    cs_lab_order ||--o{ cs_lab_result : logical
    cl_procedure_order ||--o{ cs_imaging_order : logical
    cs_imaging_order ||--o| cs_imaging_result : logical
    cl_medication_order ||--o{ cs_dispense : logical

TOBE 공통 ERD

tobe_erd_common.md

erDiagram
    tb_cd_grp["tb_cd_grp<br/>(코드 그룹 마스터)"] {
        string grp_cd PK
        string grp_nm
    }
    tb_cd["tb_cd<br/>(공통 코드 마스터)"] {
        string grp_cd PK
        string cd_val PK
    }
    tb_org["tb_org<br/>(기관 마스터)"] {
        string org_cd PK
    }
    tb_dept["tb_dept<br/>(부서 마스터)"] {
        string dept_cd PK
        string org_cd FK
        string upr_dept_cd FK
    }
    tb_emp["tb_emp<br/>(직원 마스터)"] {
        string emp_no PK
        string org_cd FK
        string dept_cd FK
    }
    tb_pt["tb_pt<br/>(환자 마스터)"] {
        string pt_no PK
        string org_cd FK
    }
    tb_pt_cntc["tb_pt_cntc<br/>(환자 연락처)"] {
        string pt_no PK
        int cntc_seq PK
    }
    tb_insr_pyr["tb_insr_pyr<br/>(보험자 마스터)"] {
        string pyr_cd PK
    }
    tb_pt_insr["tb_pt_insr<br/>(환자 보험)"] {
        string pt_no PK
        string pyr_cd PK
        date eff_st_dt PK
    }
    tb_wd["tb_wd<br/>(병동 마스터)"] {
        string wd_cd PK
        string org_cd FK
        string dept_cd FK
    }
    tb_room["tb_room<br/>(병실 마스터)"] {
        string room_cd PK
        string wd_cd FK
        string dept_cd FK
    }

    tb_cd_grp ||--o{ tb_cd : has
    tb_org ||--o{ tb_dept : has
    tb_dept ||--o{ tb_dept : parent
    tb_org ||--o{ tb_emp : has
    tb_dept ||--o{ tb_emp : belongs
    tb_org ||--o{ tb_pt : has
    tb_pt ||--o{ tb_pt_cntc : has
    tb_insr_pyr ||--o{ tb_pt_insr : covers
    tb_pt ||--o{ tb_pt_insr : has
    tb_org ||--o{ tb_wd : has
    tb_dept ||--o{ tb_wd : belongs
    tb_wd ||--o{ tb_room : has
    tb_dept ||--o{ tb_room : belongs

TOBE 일반 ERD

tobe_erd_general.md

erDiagram
    tb_pt["tb_pt<br/>(환자 마스터)"] {
        string pt_no PK
    }
    tb_dept["tb_dept<br/>(부서 마스터)"] {
        string dept_cd PK
    }
    tb_emp["tb_emp<br/>(직원 마스터)"] {
        string emp_no PK
    }
    tb_apnt["tb_apnt<br/>(예약 정보)"] {
        string apnt_no PK
        string pt_no FK
        string dept_cd FK
        string dr_emp_no FK
    }
    tb_ckin["tb_ckin<br/>(체크인 정보)"] {
        string ckin_no PK
        string apnt_no FK
        string pt_no FK
    }
    tb_wait_q["tb_wait_q<br/>(대기열)"] {
        string wait_no PK
        string apnt_no FK
        string pt_no FK
    }
    tb_pt_ntf["tb_pt_ntf<br/>(환자 안내 알림)"] {
        string pt_ntf_no PK
        string pt_no FK
        string apnt_no FK
    }

    tb_pt ||--o{ tb_apnt : books
    tb_dept ||--o{ tb_apnt : receives
    tb_emp ||--o{ tb_apnt : attends
    tb_apnt ||--o{ tb_ckin : starts
    tb_apnt ||--o{ tb_wait_q : queues
    tb_pt ||--o{ tb_pt_ntf : notified

TOBE 원무 ERD

tobe_erd_administration.md

erDiagram
    tb_pt["tb_pt<br/>(환자 마스터)"] {
        string pt_no PK
    }
    tb_room["tb_room<br/>(병실 마스터)"] {
        string room_cd PK
    }
    tb_apnt["tb_apnt<br/>(예약 정보)"] {
        string apnt_no PK
    }
    tb_enc["tb_enc<br/>(내원/진료 에피소드)"] {
        string enc_no PK
        string pt_no FK
        string apnt_no FK
    }
    tb_adm["tb_adm<br/>(입원 정보)"] {
        string adm_no PK
        string enc_no FK
        string room_cd FK
    }
    tb_chg_itm["tb_chg_itm<br/>(청구 항목 마스터)"] {
        string chg_cd PK
    }
    tb_enc_chg["tb_enc_chg<br/>(에피소드별 청구 내역)"] {
        string enc_no PK
        int chg_seq PK
        string chg_cd FK
    }
    tb_bill["tb_bill<br/>(수납 청구서)"] {
        string bill_no PK
        string enc_no FK
    }
    tb_pay["tb_pay<br/>(수납 정보)"] {
        string pay_no PK
        string bill_no FK
    }
    tb_clm["tb_clm<br/>(보험 청구)"] {
        string clm_no PK
        string bill_no FK
    }

    tb_apnt ||--o{ tb_enc : converts
    tb_pt ||--o{ tb_enc : has
    tb_enc ||--o{ tb_adm : admits
    tb_room ||--o{ tb_adm : assigned
    tb_chg_itm ||--o{ tb_enc_chg : billed
    tb_enc ||--o{ tb_enc_chg : charged
    tb_enc ||--o| tb_bill : billed
    tb_bill ||--o{ tb_pay : paid
    tb_bill ||--o{ tb_clm : claims

TOBE 진료 ERD

tobe_erd_clinical.md

erDiagram
    tb_enc["tb_enc<br/>(내원/진료 에피소드)"] {
        string enc_no PK
        string pt_no FK
    }
    tb_dx_mst["tb_dx_mst<br/>(진단 마스터)"] {
        string dx_cd PK
    }
    tb_med_mst["tb_med_mst<br/>(약품 마스터)"] {
        string med_cd PK
    }
    tb_proc_mst["tb_proc_mst<br/>(처치/검사 오더 마스터)"] {
        string proc_cd PK
    }
    tb_vs["tb_vs<br/>(바이탈 사인)"] {
        string enc_no PK
        datetime msr_dtm PK
    }
    tb_note["tb_note<br/>(진료 기록 노트)"] {
        string note_no PK
        string enc_no FK
    }
    tb_enc_dx["tb_enc_dx<br/>(에피소드 진단)"] {
        string enc_no PK
        string dx_cd PK
        string dx_tp_cd PK
    }
    tb_rx_ord["tb_rx_ord<br/>(투약 오더)"] {
        string ord_no PK
        string enc_no FK
        string med_cd FK
    }
    tb_proc_ord["tb_proc_ord<br/>(처치 오더)"] {
        string ord_no PK
        string enc_no FK
        string proc_cd FK
    }

    tb_enc ||--o{ tb_vs : measures
    tb_enc ||--o{ tb_note : notes
    tb_dx_mst ||--o{ tb_enc_dx : diagnoses
    tb_enc ||--o{ tb_enc_dx : has
    tb_med_mst ||--o{ tb_rx_ord : orders
    tb_enc ||--o{ tb_rx_ord : has
    tb_proc_mst ||--o{ tb_proc_ord : orders
    tb_enc ||--o{ tb_proc_ord : has

TOBE 진료지원 ERD

tobe_erd_clinical_support.md

erDiagram
    tb_proc_ord["tb_proc_ord<br/>(처치 오더)"] {
        string ord_no PK
    }
    tb_rx_ord["tb_rx_ord<br/>(투약 오더)"] {
        string ord_no PK
    }
    tb_spcm["tb_spcm<br/>(검체 정보)"] {
        string spcm_no PK
        string proc_ord_no FK
    }
    tb_lab_tst_mst["tb_lab_tst_mst<br/>(검사 항목 마스터)"] {
        string tst_cd PK
    }
    tb_lab_ord["tb_lab_ord<br/>(검사 오더)"] {
        string lab_ord_no PK
        string proc_ord_no FK
        string spcm_no FK
        string tst_cd FK
    }
    tb_lab_rslt["tb_lab_rslt<br/>(검사 결과)"] {
        string lab_ord_no PK
        int anl_seq PK
    }
    tb_img_exam_mst["tb_img_exam_mst<br/>(영상 검사 마스터)"] {
        string exam_cd PK
    }
    tb_img_ord["tb_img_ord<br/>(영상 오더)"] {
        string img_ord_no PK
        string proc_ord_no FK
        string exam_cd FK
    }
    tb_img_rslt["tb_img_rslt<br/>(영상 판독 결과)"] {
        string img_ord_no PK
    }
    tb_dsps["tb_dsps<br/>(조제 정보)"] {
        string dsps_no PK
        string rx_ord_no FK
    }

    tb_proc_ord ||--o{ tb_spcm : produces
    tb_spcm ||--o{ tb_lab_ord : sampled
    tb_lab_tst_mst ||--o{ tb_lab_ord : ordered
    tb_lab_ord ||--o{ tb_lab_rslt : results
    tb_img_exam_mst ||--o{ tb_img_ord : ordered
    tb_img_ord ||--o| tb_img_rslt : results
    tb_rx_ord ||--o{ tb_dsps : dispensed

ETL Overview - 데이터 마이그레이션부터 DW(Data Warehouse) 구축까지의 전체 프로세스.

README.md

flowchart LR
    subgraph ASIS["ASIS Oracle (FREEPDB1)"]
        OCOM["hsp_com (공통)"]
        OGEN["hsp_gen (일반)"]
        OADM["hsp_adm (원무)"]
        OCLI["hsp_clinic (진료)"]
        OCS["hsp_clinic_sup (진료지원)"]
    end

    subgraph TOBE["TOBE PostgreSQL (hospital_tobe)"]
        PCOM["hsp_com (공통)"]
        PGEN["hsp_gen (일반)"]
        PADM["hsp_adm (원무)"]
        PCLI["hsp_clinic (진료)"]
        PCS["hsp_clinic_sup (진료지원)"]
    end

    MIG["MigrationETL Job"]

    subgraph DW["MySQL DW/DM (hospital_dw)"]
        STG["stg_tobe_* (TOBE 스테이징)"]
        DIM["dim_* (차원)"]
        FACT["fact_* (팩트)"]
        DM["dm_* (집계/분석)"]
    end

    OCOM --> MIG
    OGEN --> MIG
    OADM --> MIG
    OCLI --> MIG
    OCS --> MIG
    MIG --> PCOM
    MIG --> PGEN
    MIG --> PADM
    MIG --> PCLI
    MIG --> PCS

    PCOM --> STG
    PGEN --> STG
    PADM --> STG
    PCLI --> STG
    PCS --> STG

    STG --> DIM
    STG --> FACT
    DIM --> FACT
    FACT --> DM
    STG --> DM

    classDef migrationNode fill:transparent,stroke:transparent
    class MIG migrationNode

Migration ETL Job Example

flowchart LR
    subgraph SRC["ASIS Oracle source tables"]
        P["cm_patient (환자)"]
        I["cm_patient_insurance (환자보험)"]
        A["gn_appointment (예약)"]
    end

    subgraph EXT["Extract"]
        J["join by patient_no / patient_id"]
        F["filter active rows"]
        S["select latest insurance"]
    end

    subgraph TRN["Cleanse & Transform"]
        C1["cleanse: trim / null handling"]
        C2["standardize: gender / insurance codes"]
        C3["derive: birth_date / appointment_date"]
        C4["deduplicate by business key"]
    end

    subgraph TGT["TOBE PostgreSQL target tables"]
        T1["tb_pt (환자 마스터)"]
        T2["tb_pt_insr (환자 보험)"]
    end

    P --> J
    I --> J
    A --> J
    J --> F --> S
    S --> C1 --> C2 --> C3 --> C4
    C4 --> T1
    C4 --> T2

알아두면 쓸모있는 오라클 함수 사용 예제

SQL

Run 버튼을 누르면 최대 10건의 조회 결과가 표시됩니다.