gray% setenv ORACLE_SID EMRS2
gray% setenv ORACLE_HOME /u02/app/oracle/product/7.3.2
gray% set path=(${ORACLE_HOME}/bin $path)
gray% rehash
gray% sqlplus
SQL*Plus: Release 3.3.2.0.0 - Production on Mon Mar 3 13:42:46
1997
Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
Enter user-name: psz
Enter password: <secret>
Connected to:
Oracle7 Server Release 7.3.2.1.0 - Production Release
PL/SQL Release 2.3.2.0.0 - Production
SQL> select table_name from all_tables where owner='BIGPAT';
no rows selected
SQL> select table_name from all_tables where owner='BIGPATS';
TABLE_NAME
------------------------------
CHILD_DOCS
CLINICAL_DATA
DOC_ATTRIBUTES
DOC_DESCRIPTION
DOC_STORE
PAT_DEMOGRAPH
PAT_FIN_ACCT
PAT_TEST_HISTV
PERSNL_PUBLIC
PHARMACY_TABLE
PPR
PROBLEMS
12 rows selected.
SQL> describe bigpats.child_docs;
| Name | Null? | Type |
| ------------------------------- -------- ---- | ||
| DOC_ID | NUMBER(38) | |
| CHILD_ID | NUMBER(38) | |
| CHILD_NAME | VARCHAR2(20) |
SQL> describe bigpats.clinical_data;
| Name | Null? | Type |
| ------------------------------- -------- ---- | ||
| DATA_NAME | VARCHAR2(80) | |
| VALUE | NUMBER(38) | |
| DATE_OBTAINED | DATE | |
| DATE_MODIFIED | DATE | |
| PAT_NUM | NUMBER(38) | |
| TIME_OF_DAY | VARCHAR2(5) | |
| DOC_ID | NUMBER(38) |
SQL> describe bigpats.doc_attributes;
| Name | Null? | Type |
| ------------------------------- -------- ---- | ||
| ATTRIBUTE | VARCHAR2(20) | |
| VALUE | VARCHAR2(230) | |
| DOC_ID | NUMBER(38) |
SQL> describe bigpats.doc_description;
| Name | Null? | Type |
| ------------------------------- -------- ---- | ||
| CREATION | DATE | |
| LAST_MODIFIED | DATE | |
| PRIMARY_SIGNATORY | NUMBER(38) | |
| SECONDARY_SIGNATORY | NUMBER(38) | |
| STATUS | VARCHAR2(20) | |
| COMPOUND | VARCHAR2(5) | |
| DOCUMENT_TYPE | VARCHAR2(20) | |
| DOC_ID | NUMBER(38) | |
| PAT_NUM | NUMBER(38) | |
| COST_CENTER | NUMBER(4) | |
| ESIG_DOC_ID | NUMBER(38) |
SQL> describe bigpats.doc_store;
| Name | Null? | Type |
| ------------------------------- -------- ---- | ||
| DOC_ID | NUMBER(38) | |
| CONTENT | LONG |
SQL> describe bigpats.pat_demograph;
| Name | Null? | Type |
| ------------------------------- -------- ---- | ||
| PAT_NUM | NOT NULL | NUMBER(9) |
| LAST_NAME | VARCHAR2(16) | |
| FIRST_NAME | VARCHAR2(11) | |
| MID_INITL | VARCHAR2(1) | |
| TITLE | VARCHAR2(3) | |
| SSN | NUMBER(9) | |
| PREV_NUM | NUMBER(9) | |
| PREV_NAME | VARCHAR2(31) | |
| STREET_ADDR | VARCHAR2(25) | |
| OTHER_ADDR | VARCHAR2(25) | |
| CITY_NAME | VARCHAR2(15) | |
| STATE_CD | VARCHAR2(2) | |
| ZIP_CD | VARCHAR2(9) | |
| PHONE_NUM | VARCHAR2(11) | |
| SEX_CD | VARCHAR2(1) | |
| DOB | DATE | |
| HGHT_VAL | NUMBER(3) | |
| WGT_VAL | NUMBER(5) | |
| BIRTH_PLACE_NAME | VARCHAR2(15) | |
| CH_EMP_STATUS | VARCHAR2(1) | |
| RACE_CD | VARCHAR2(1) | |
| ALLERGY_TXT | VARCHAR2(25) | |
| DEATH_DT_TM | DATE | |
| BAD_ADDR_STATUS | VARCHAR2(1) | |
| SPOUSE_FIRST_NAME | VARCHAR2(11) | |
| MAIDEN_NAME | VARCHAR2(16) | |
| FAMILY_PROV_NAME | VARCHAR2(25) | |
| DIS_EXPOSR_STATUS | VARCHAR2(1) | |
| LAST_OUTPAT_FIN_ENC_NUM | NUMBER(3) | |
| LAST_INPAT_FIN_ENC_NUM | NUMBER(3) | |
| EMER_NOTIFY_NAME | VARCHAR2(25) | |
| EMER_NOTIFY_PHONE_NUM_1 | VARCHAR2(11) | |
| EMER_NOTIFY_PHONE_NUM_2 | VARCHAR2(11) | |
| EMER_NOTIFY_RELTN_CD | VARCHAR2(2) | |
| NEXT_KIN_NAME | VARCHAR2(25) | |
| NEXT_KIN_ADDR | VARCHAR2(25) | |
| NEXT_KIN_CITY_NAME | VARCHAR2(15) | |
| NEXT_KIN_STATE_CD | VARCHAR2(2) | |
| NEXT_KIN_ZIP_CD | VARCHAR2(9) | |
| NEXT_KIN_PHONE_NUM | VARCHAR2(11) | |
| NEXT_KIN_RELTN_CD | VARCHAR2(2) | |
| NEXT_KIN_EMPLYR_NAME | VARCHAR2(25) | |
| NEXT_KIN_EMPLYR_CITY_NAME | VARCHAR2(15) | |
| NEXT_KIN_EMPLYR_STATE_CD | VARCHAR2(2) | |
| NEXT_KIN_EMPLYR_ZIP_CD | VARCHAR2(9) | |
| NEXT_KIN_EMPLYR_PHONE_NUM | VARCHAR2(11) | |
| MEHC_FAMILY_NUM | VARCHAR2(5) | |
| MEHC_MEMBR_NUM | VARCHAR2(2) | |
| OTHER_HEALTH_CENTR_NUM | VARCHAR2(15) | |
| OTHER_HEALTH_CENTR_ABBR | VARCHAR2(4) | |
| NATIVE_LANG_CD | VARCHAR2(2) | |
| INTERP_NEED_STATUS | VARCHAR2(1) | |
| USER_FLD_TXT | VARCHAR2(10) | |
| PREV_OUTPAT_FIN_ENC_NUM | NUMBER(3) | |
| PREV_INPAT_FIN_ENC_NUM | NUMBER(3) | |
| LAST_ASSIGN_FIN_ENC_NUM | NUMBER(3) | |
| LAST_DISCH_DT | DATE | |
| UPDT_USER_INITL | VARCHAR2(3) | |
| UPDT_DEPT_CD | VARCHAR2(3) | |
| UPDT_DT_TM | DATE |
SQL> describe bigpats.pat_fin_acct;
| Name | Null? | Type | |
| ------------------------------- -------- ---- | |||
| PAT_NUM | NOT NULL | NUMBER(9) | |
| PAT_FIN_ENC_NUM | NUMBER(3) | % encounter number | |
| CARE_CLS_CD | VARCHAR2(1) | ||
| STATUS | VARCHAR2(1) | ||
| PREADMT_REGSTR_DT_TM | DATE | ||
| BAD_DEBT_DT_TM | DATE | ||
| HIST_DT_TM | DATE | ||
| ACTIV_PAT_NUM | NUMBER(9) | % if mult nos | |
| ACTIV_PAT_FIN_ENC_NUM | NUMBER(3) | ||
| ACTIV_PAT_NAME | VARCHAR2(31) | ||
| GUAR_NUM | NUMBER(9) | % guardian number | |
| GUAR_RELTN_CD | VARCHAR2(2) | ||
| MARITAL_STATUS | VARCHAR2(1) | ||
| GEO_CD | VARCHAR2(6) | ||
| RELIG_CD | VARCHAR2(3) | ||
| COURTESY_CD | VARCHAR2(1) | % VIP | |
| SPECL_PROGRM_CD | VARCHAR2(2) | % e.g., research | |
| EMPLYMT_STATUS | VARCHAR2(1) | ||
| EMPLYR_NAME | VARCHAR2(25) | % denormalized | |
| EMPLYR_EMP_NUM | VARCHAR2(11) | ||
| EMPLYR_ADDR | VARCHAR2(30) | ||
| EMPLYR_CITY_NAME | VARCHAR2(15) | ||
| EMPLYR_STATE_CD | VARCHAR2(2) | ||
| EMPLYR_ZIP_CD | VARCHAR2(9) | ||
| EMPLYR_PHONE_NUM | VARCHAR2(11) | ||
| EMPLYR_CNTCT_NAME | VARCHAR2(25) | ||
| NURS_STN_CD | VARCHAR2(4) | ||
| RM_NUM | VARCHAR2(4) | ||
| BED_CD | VARCHAR2(2) | ||
| CARE_SUBCLS_CD | VARCHAR2(1) | ||
| PRNCPL_PAYOR_CD | VARCHAR2(2) | ||
| AR_SUBCLS_CD | VARCHAR2(3) | ||
| BAD_DEBT_CLS_CD | VARCHAR2(3) | ||
| SERV_CD | VARCHAR2(3) | % e.g., endocrine | |
| ADMT_PROV_NUM | NUMBER(5) | % admitting doc (e.g., in ER) | |
| ATND_PROV_NUM | NUMBER(5) | % attending doc | |
| REF_PROV_NUM | NUMBER(5) | % referring doc | |
| REF_PROV_NAME | VARCHAR2(25) | % denormalized | |
| MAJ_DRG_CD | NUMBER(3) | % principal DRG | |
| OUTLR_STATUS | VARCHAR2(1) | ||
| DRG_APPRV_STATUS | VARCHAR2(1) | ||
| GRPR_REVIEW_CD | NUMBER(2) | ||
| FINAL_BILL_DRG_CD | NUMBER(3) | ||
| MAJ_DIAG_CATGRY_NUM | NUMBER(2) | ||
| PRNCPL_PROCDR_CD | VARCHAR2(5) | % ICD-9-CM code | |
| PRNCPL_PROCDR_DT | DATE | ||
| PROCDR_PROV_NUM | NUMBER(5) | % MD | |
| PROCDR_PROV_NAME | VARCHAR2(25) | % denormalized | |
| PRNCPL_DIAG_CD | VARCHAR2(6) | % ICD-9-CM code | |
| MAJ_COST_DIAG_CD | VARCHAR2(6) | % null | |
| PREADMT_DT_TM | DATE | ||
| ADMT_DT_TM | DATE | ||
| ADMT_DIAG_CD | VARCHAR2(6) | % ICD-9-CM code | |
| ADMT_DIAG_DESCR | VARCHAR2(70) | % denormalized or free | |
| ADMT_CLS_CD | VARCHAR2(1) | ||
| ADMT_SOURCE_CD | VARCHAR2(1) | ||
| ADMT_USER_INITL | VARCHAR2(3) | ||
| DISCH_DT_TM | DATE | ||
| ANTCPT_DISCH_DT_TM | DATE | ||
| DISCH_DISP_CD | VARCHAR2(2) | ||
| DISCH_RELEASE_TXT | VARCHAR2(50) | % released to institution (rare) | |
| PREV_NURS_STN_CD | VARCHAR2(4) | ||
| PREV_RM_NUM | VARCHAR2(4) | ||
| PREV_BED_CD | VARCHAR2(2) | ||
| PREV_CARE_SUBCLS_CD | VARCHAR2(1) | ||
| PREV_SERV_CD | VARCHAR2(3) | ||
| PREV_ATND_PROV_NUM | NUMBER(5) | ||
| PREV_COND_CD | VARCHAR2(1) | ||
| PREV_TRNSFR_DT_TM | DATE | ||
| PEND_TRNSFR_DT_TM | DATE | ||
| PEND_TRNSFR_LOCTN_TXT | VARCHAR2(10) | ||
| TRNSFR_REASON_TXT | VARCHAR2(30) | ||
| TRNSFR_EFFCT_DT_TM | DATE | ||
| OUTPAT_CARE_CLS_CD | VARCHAR2(1) | ||
| ACCDNT_DT_TM | DATE | ||
| ACCDNT_LOCTN_CD | VARCHAR2(2) | ||
| LAST_OUTPAT_VISIT_DT_TM | DATE | ||
| EMER_DEPT_ARRIV_MODE_CD | VARCHAR2(1) | ||
| EMER_DEPT_DISCH_DT_TM | DATE | ||
| EMER_DEPT_DISP_CD | VARCHAR2(2) | ||
| EMER_DEPT_POLICE_NOTIFY_STATUS | VARCHAR2(1) | ||
| HEALTH_BOARD_NOTIFY_STATUS | VARCHAR2(1) | ||
| REGSTR_OTHER_TXT | VARCHAR2(60) | ||
| USER_FLD_TXT | VARCHAR2(100) | ||
| MED_REC_CHART_NUM | VARCHAR2(6) | % null | |
| MED_REC_CHART_LOCTN_CD | VARCHAR2(5) | % null | |
| MED_REC_CMNT_TXT | VARCHAR2(30) | ||
| BLOOD_PROGRM_STATUS | VARCHAR2(1) | ||
| BLOOD_FURN_CNT | NUMBER(2) | ||
| BLOOD_REPLACE_CNT | NUMBER(2) | ||
| BLOOD_ORGNZTN_NAME | VARCHAR2(8) | ||
| UB82_LOCTR_02 | VARCHAR2(30) | ||
| UB82_LOCTR_09 | VARCHAR2(7) | ||
| UB82_LOCTR_27 | VARCHAR2(8) | ||
| UB82_LOCTR_45 | VARCHAR2(17) | ||
| OCCUR_SPAN_CD | VARCHAR2(2) | % null | |
| OCCUR_BEGIN_DT | DATE | ||
| OCCUR_END_DT | DATE | ||
| TOTL_GROSS_CHRG_AMT | NUMBER(9,2) | ||
| LATE_ACTIV_AMT | NUMBER(9,2) | ||
| LATE_ACTIV_ADJST_AMT | NUMBER(9,2) | ||
| LAST_PAT_CYCLE_BILL_DT_TM | DATE | ||
| LAST_INS_CYCLE_BILL_DT_TM | DATE | ||
| FINAL_BILL_DT_TM | DATE | ||
| LAST_ACTIV_DT_TM | DATE | ||
| REBILL_STATUS | VARCHAR2(1) | ||
| LAST_PRORAT_DT_TM | DATE | ||
| LAST_POST_DT_TM | DATE | ||
| BAD_DEBT_WRITE_OFF_AMT | NUMBER(9,2) | ||
| BAD_DEBT_WRITE_OFF_RECOVER_AMT | NUMBER(9,2) | ||
| ACCT_REPRSNT_CD | VARCHAR2(3) | ||
| DIRECT_AR_STATUS | VARCHAR2(1) | ||
| LAST_STMT_DT_TM | DATE | ||
| STMT_SENT_CNT | NUMBER(2) | ||
| STMT_THREAT_CD | NUMBER(2) | ||
| RECLS_SUPPRESS_STATUS | VARCHAR2(1) | ||
| FIRST_STMT_STATUS | VARCHAR2(1) | ||
| FIRST_STMT_DAY_CNT | NUMBER(3) | ||
| OTHER_STMT_STATUS | VARCHAR2(1) | ||
| OTHER_STMT_DAY_CD | NUMBER(3) | ||
| STMT_SMALL_BAL_STATUS | VARCHAR2(1) | ||
| STMT_FORM_CLS_CD | VARCHAR2(1) | ||
| STMT_MSG_SUPPRESS_STATUS | VARCHAR2(1) | ||
| GUAR_CNTRCT_CTRL_NUM | NUMBER(9) | ||
| GUAR_CNTRCT_NUM | NUMBER(3) | ||
| CONVR_STATUS | VARCHAR2(1) | ||
| CYCLE_UPDT_DT_TM | DATE | ||
| MED_REC_UPDT_STATUS | VARCHAR2(1) | ||
| FACIL_TRNSFR_FROM_CD | VARCHAR2(5) | ||
| FACIL_TRNSFR_TO_CD | VARCHAR2(5) | ||
| UPDT_USER_INITL | VARCHAR2(3) | ||
| UPDT_DEPT_CD | VARCHAR2(3) | ||
| UPDT_DT_TM | DATE |
SQL> describe bigpats.pat_test_histv;
| Name | Null? | Type |
| ------------------------------- -------- ---- | ||
| PAT_TEST_ID | NOT NULL | NUMBER(15) |
| PAT_NUM | NOT NULL | NUMBER(9) |
| EVENT_START_DT_TM | NOT NULL | DATE |
| EVENT_DT_TM_KEY | NOT NULL | NUMBER(12) |
| TEST_ID | NOT NULL | NUMBER(7) |
| TEST_ABBR | VARCHAR2(10) | |
| EVENT_STOP_DT_TM | DATE | |
| TEST_PRTY_CD | VARCHAR2(2) | |
| REMOTE_SYSTEM_CD | NOT NULL | VARCHAR2(5) |
| REMOTE_EVENT_NUM | NOT NULL | VARCHAR2(15) |
| SUPERGRP_NUM | NUMBER(15) | |
| GRP_NUM | NUMBER(9) | |
| DETAIL_NUM | NUMBER(3) | |
| SUPERGRP_TEST_ID | NUMBER(7) | |
| GRP_TEST_ID | NUMBER(7) | |
| PARENT_ID | NUMBER(15) | |
| CHILD_LEVEL_VAL | NUMBER(2) | |
| ROOT_STATUS | VARCHAR2(1) | |
| DATA_CLS_CD | VARCHAR2(2) | |
| RSLT_VAL | VARCHAR2(8) | |
| RSLT_UNIT_TXT | VARCHAR2(10) | |
| RSLT_TYPE_CD | VARCHAR2(1) | |
| ABN_STATUS | VARCHAR2(2) | |
| ABN_TYPE_CD | VARCHAR2(2) | |
| REF_LOW_VAL | VARCHAR2(10) | |
| REF_HIGH_VAL | VARCHAR2(10) | |
| REF_TYPE_CD | VARCHAR2(1) | |
| DOCMNT_PNTR_ID | NUMBER(12) | |
| ORD_ID | NUMBER(38) | |
| ORD_PROV_NUM | NUMBER(38) | |
| ORD_LOCTN | VARCHAR2(4) | |
| RSLT_STATUS | VARCHAR2(2) | |
| CONFID_STATUS | VARCHAR2(1) | |
| CMNT_STATUS | VARCHAR2(1) | |
| ARCH_STATUS | VARCHAR2(1) | |
| DOB_STATUS | VARCHAR2(1) | |
| RSLT_SCR | NUMBER(2) | |
| RSLT_DT_TM | DATE | |
| REMOTE_RPT_DT_TM | DATE | |
| UPDT_DT_TM | NOT NULL | DATE |
SQL> describe bigpats.persnl_public;
| Name | Null? | Type | |
| ------------------------------- -------- ---- | |||
| PERSNL_ID | NOT NULL | NUMBER(38) | % PROV_NO is subset of these |
| LAST_NAME | NOT NULL | VARCHAR2(20) | |
| FIRST_NAME | VARCHAR2(20) | ||
| MID_INITL | VARCHAR2(1) | ||
| SUR_NAME | VARCHAR2(3) | ||
| TITLE | VARCHAR2(10) | ||
| PERSNL_TYPE_DESCR | VARCHAR2(20) | ||
| DEPT_CD | VARCHAR2(6) | ||
| PRIM_WORK_TYPE_CD | VARCHAR2(5) | ||
| CH_PHONE_NUM | VARCHAR2(7) | ||
| BEEPER_NUM | VARCHAR2(7) | ||
| CLSTR_USER_NAME | VARCHAR2(20) | ||
| CLSTR_ORACLE_USER_NAME | VARCHAR2(30) | ||
| EMAIL_ADDR | VARCHAR2(50) | ||
| PAPER_MAIL_ADDR | VARCHAR2(50) | ||
| VMAIL_ADDR | VARCHAR2(15) | ||
| AUTH_PERSNL_ID | NUMBER(38) | % authorizing personnel | |
| AUTH_END_DT | DATE | ||
| REC_VALID_STATUS | NOT NULL | VARCHAR2(1) | |
| LAST_NAME_SOUNDEX_CD | NOT NULL | VARCHAR2(4) |
SQL> describe bigpats.pharmacy_table;
| Name | Null? | Type |
| PATIENT_RECORD_NUMBER | NUMBER(9) | |
| PATIENT_VISIT_NUMBER | NUMBER(5) | |
| PATIENT_NAME | VARCHAR2(30) | |
| FUNCTION_CODE | VARCHAR2(3) | |
| DATE_OF_SERVICE | DATE | |
| FORMULARY_CODE_1 | VARCHAR2(5) | |
| FORMULARY_CODE_2 | VARCHAR2(5) | |
| RX_CODE | VARCHAR2(9) | |
| MEDICATION_NAME | VARCHAR2(30) | |
| SERVICE_QUANTITY | NUMBER(6) | |
| UNIT_PRICE | NUMBER(9,2) | |
| DOSE_FEE | NUMBER(9,2) | |
| LABOR_FEE | NUMBER(9,2) | |
| TOTAL_PRICE | NUMBER(9,2) | |
| MED_TYPE | VARCHAR2(1) | |
| DRG_CODE | VARCHAR2(3) | |
| ORDERING_DOC | VARCHAR2(25) | |
| LABOR_EXPENSE_CODE | VARCHAR2(1) | |
| DRUG_DOSE | NUMBER(9,2) | |
| FORMULARY_CLASS_1 | VARCHAR2(3) | |
| FORMULARY_CLASS_2 | VARCHAR2(3) | |
| DRUG_ROLE | VARCHAR2(1) | |
| ETXG_SEQUENCE | NUMBER(38) | |
| NURS_LOC | VARCHAR2(4) |
SQL> describe bigpats.ppr;
| Name | Null? | Type |
| ------------------------------- -------- ---- | ||
| ROLE | VARCHAR2(20) | |
| PROVIDER_ID | NUMBER(38) | |
| PAT_NUM | NUMBER(38) | |
| EXT_LAST | VARCHAR2(50) | |
| EXT_FIRST | VARCHAR2(50) | |
| EXT_PROV_NUM | NUMBER(38) | |
| COMMENTS | VARCHAR2(80) | |
| START_DATE | DATE | |
| END_DATE | DATE | |
| COST_CENTER | NUMBER(4) |
SQL> describe bigpats.problems;
| Name | Null? | Type |
| ------------------------------- -------- ---- | ||
| PROBLEM_NAME | VARCHAR2(80) | |
| START_DATE | DATE | |
| END_DATE | DATE | |
| PAT_NUM | NUMBER(38) | |
| REVOKE_DATE | DATE | |
| COST_CENTER | NUMBER(4) | |
| PRIMARY_DIAG_FLG | NUMBER(3) |
SQL> describe common.clinical_data_types;
| Name | Null? | Type |
| ------------------------------- -------- ---- | ||
| DATA_NAME | VARCHAR2(80) | |
| UNITS | VARCHAR2(80) |
SQL> describe common.cpt_code;
| Name | Null? | Type |
| ------------------------------- -------- ---- | ||
| CPT_CD | NOT NULL | NUMBER(38) |
| CPT_DESCR | NOT NULL | VARCHAR2(30) |
SQL> describe common.icd9_procdr;
| Name | Null? | Type |
| ------------------------------- -------- ---- | ||
| PROCDR_CD | VARCHAR2(4) | |
| PROCDR_DESC | VARCHAR2(24) |
SQL> describe psz.nametable;
| Name | Null? | Type |
| ------------------------------- -------- ---- | ||
| NAME | CHAR(50) | |
| PAT_NUM | NUMBER(30) |
SQL> select count(*) from psz.nametable;
| COUNT(*) |
| ---------- |
| 0 |