SACWIS PRODUCTION DATABASE

UPDATE REQUEST

Date: / May 29, 2014
Remedy Request ID: / Not Applicable
Production Build ID: / P.2.23.0
Build Description : / Business Desc:
Case Service Review
- PARTICIPATION_STATUS_CODE was added to CASE_SVC_REVIEW_MEMBER.....it will still remain in CASE_SVC_ACTIVITY for historical data
- PARTICIPATION_STATUS_TEXT was added to CASE_SERVICE_REVIEW
Adoption
- inserts into MENU and PROFILE_RESOURCE for ongoing assessment
- deletes from MENU for safety plan and safety assessment
- updates to MENU to reorder
Person
- package additions for Person Snapshot
- new tables for Person Snapshot
1692/adoption/foster Home Study
- package additions for Provider Snapshot and Home Study Basic topic
- package update for Provider Merge
- HM_STDY_ADOPT_FOSTER_DETAIL created and all data in this table has been migrated from PROVIDER_HOME_STUDY to allow for use of the common fields for mulitple types of home studies
- creation of the following tables for the 1692 Home Study functionality:
- HM_STDY_FOSTER_TO_ADOPT_DETAIL
- HOME_STUDY_MEMBER
- HOME_STUDY_NARRATIVE
- ASSOCIATED_PROVIDER_LINK
- Ref Data updates to the following domain codes:
- ProviderType
- ProviderTypeStatus
- InquiryType
- InquiryTypePrvdrTypeCd
- HomeStudyTypeCode
- HmStdyVerifItemCode
- Ref Data additions of the following domain codes:
- HomeStudyCategory
- HmStdyNarrativeItemCode
- HmStdyFstr2AdptVerifStatCd
- HmStdyFstr2AdptAsmtVerifStatCd
- HmStdyDispositionCode1692
- TypeOfProviderAssociation
- Inserts into HM_STDY_VERIF_STATUS_LINK
- Inserts into MENU and PROFILE_RESOURCE for the 1692 link
- PROVIDER_HOME_STUDY - added new fields
- HOME_STUDY_VERIFICATION - new field (status_Domain_Code)
- PROVIDER_FAMILY_DESCRIPTION - new field income_Expenses_Change_Code
- PROVIDER_HOME_DETAIL - new field home_Education_Program_Text
Updates for:
- Df 23055 Delete Provider Member Income, Resource, Expense Notifications
- Df 22578 Adoptive Care International Provider Type
- Df 21868 Changes to Provider Info – International Adoptive 1692 Provider Types
- Df 22248 & 21868 Changes to Home Study DB Structure
- Df 22037 Home Study Data Freeze / Storage
- Df 22038 Foster To Adopt Home Study Assessment JFS 1692
Reports
-  Df 22528 & 22469 Rpt 133 Safety Audit JFS 1348
-  Df 11183 Rpt 135 Initial Home Study JFS 1673
-  Df 22496 & 22529 Rpt 137 Assessment For Child Placement Update JFS 1385
-  Rpt 150 Provider Directory Report
-  Df 23050 & 21728 Rpt 151 Licensing Activities Detail
-  Rpt 152 Certification Activity Report
-  Df 23009 Rpt 358 NYTD Statistical Report

Section 1 –

Type of Build : / ¨  Hot Fix / x  Full
Database Team Responsible Party: / SACWIS DBA’s

Section 2 – Technical Solution

Location of Solution Documentation: / R:\AS\Business Process Layer Unit\Deployment Artifacts\Release 2.23.0\Release 2.15.0 Preliminary Database Changes
Solution Documentation Folder Name:
Software/SQL File Name: / sacwis_prod_db_p.2.23.
List new table(s)/View/MView be added:
List new column(s) be added / modified: / Add 59 New Table(s) with New Column(s)
1.  HM_STDY_ADOPT_FOSTER_DETAIL
( HM_STDY_ADOPT_FOSTER_DETAIL_ID NUMBER NOT NULL,
PROVIDER_HOME_STUDY_ID NUMBER NOT NULL,
PROVIDER_CHAR_ID NUMBER NULL,
PROVIDER_FMLY_DESC_ID NUMBER NULL,
PROVIDER_HOME_DESC_ID NUMBER NULL,
PRVDR_PLCMNT_CRITERIA_ID NUMBER NULL,
AMEND_UPDATE_COMMENT VARCHAR2(1000) NULL,
DISPOSITION_REASON_CODE VARCHAR2(30) NULL,
FIRE_INSPECT_CONDUCT_DATE DATE NULL,
FIRE_INSPECT_REQUIRE_FLAG NUMBER NULL,
LEVEL_OF_CARE_CODE VARCHAR2(30) NULL,
OTHER_REASON_TEXT VARCHAR2(1000) NULL,
SAFETY_AUDIT_COMMENT VARCHAR2(1000) NULL,
SAFETY_AUDIT_CONDUCT_DATE DATE NULL,
SAFETY_AUDIT_DECISION_CODE VARCHAR2(30) NULL,
MIN_TRNG_HOURS_REQUIRED_NBR INTEGER NULL,
VERIFICATION_DISQUALIFY_CODE VARCHAR2(30) NULL,
VERIFICATION_DISQUALIFY_TEXT VARCHAR2(1000) NULL,
VERIFICATION_RESTRICT_CODE VARCHAR2(30) NULL,
VERIFICATION_RESTRICT_TEXT VARCHAR2(1000) NULL,
SERVICE_LIMIT_NBR INTEGER NULL,
NOT_INITIATED_30_DAYS_TEXT VARCHAR2(1000) NULL,
NOT_COMPLETED_180_DAYS_TEXT VARCHAR2(1000) NULL,
COPY_OF_OAC_PROVIDED_CODE VARCHAR2(30) NULL,
COPY_OF_FC_POLICIES_CODE VARCHAR2(30) NULL,
MATERIALS_DISCUSSED_CODE VARCHAR2(30) NULL,
CREATED_BY NUMBER NOT NULL,
CREATED_DATE DATE NOT NULL,
MODIFIED_BY NUMBER NOT NULL,
MODIFIED_DATE DATE NOT NULL)
2.  HM_STDY_FOSTER_TO_ADOPT_DETAIL
(HM_STDY_FOSTER_TO_ADOPT_DTL_ID NUMBER NOT NULL,
PROVIDER_HOME_STUDY_ID NUMBER NOT NULL,
PERSON_ID NUMBER NOT NULL,
POTENTIAL_PROVIDER_ID NUMBER NULL,
SIBLING_GROUP_FLAG NUMBER NULL,
SIBLING_GROUP_COUNT NUMBER NULL,
ADPT_SIBLING_GROUP_FLAG NUMBER NULL,
TIME_LIVING_IN_HOME VARCHAR2(40) NULL,
INVOLVED_IN_CASE_PLAN_FLAG NUMBER NULL,
CASE_PLAN_GOALS_TEXT VARCHAR2(4000) NULL,
NEW_PROVIDER_FLAG NUMBER NULL,
FOSTER_CARE_PLACEMENT_DATE DATE NULL,
CREATED_BY NUMBER NOT NULL,
CREATED_DATE DATE NOT NULL,
MODIFIED_BY NUMBER NOT NULL,
MODIFIED_DATE DATE NOT NULL)
3.  HOME_STUDY_MEMBER
( HOME_STUDY_MEMBER_ID NUMBER NOT NULL,
PROVIDER_HOME_STUDY_ID NUMBER NOT NULL,
PERSON_ID NUMBER NOT NULL,
ROLE_CODE VARCHAR2(30) NOT NULL,
CREATED_BY NUMBER NOT NULL,
CREATED_DATE DATE NOT NULL,
MODIFIED_BY NUMBER NOT NULL,
MODIFIED_DATE DATE NOT NULL)
4.  HOME_STUDY_NARRATIVE
(HOME_STUDY_NARRATIVE_ID NUMBER NOT NULL,
PROVIDER_HOME_STUDY_ID NUMBER NOT NULL,
NARRATIVE_CODE VARCHAR2(30) NOT NULL,
NARRATIVE_TEXT VARCHAR2(4000) NULL,
CREATED_BY NUMBER NOT NULL,
CREATED_DATE DATE NOT NULL,
MODIFIED_BY NUMBER NOT NULL,
MODIFIED_DATE DATE NOT NULL)
5.  ASSOCIATED_PROVIDER_LINK
(ASSOCIATED_PROVIDER_LINK_ID NUMBER NOT NULL,
PRIMARY_PROVIDER_ID NUMBER NOT NULL,
ASSOCIATED_PROVIDER_ID NUMBER NOT NULL,
PROVIDER_ASSOC_TYPE_CODE VARCHAR2 (30) NOT NULL,
CREATED_BY NUMBER NOT NULL,
CREATED_DATE DATE NOT NULL,
MODIFIED_BY NUMBER NOT NULL,
MODIFIED_DATE DATE NOT NULL)
6.  SNAPSHOT_BASE (
snapshot_id NUMBER NOT NULL,
snapshot_type_code VARCHAR2(30) NOT NULL,
snapshot_ref_id NUMBER NOT NULL,
created_by NUMBER NOT NULL,
created_date DATE NOT NULL,
modified_by NUMBER NOT NULL,
modified_date DATE NOT NULL)
7.  S_ACADEMIC_EVALUATION (
snapshot_id NUMBER NOT NULL,
academic_evaluation_id NUMBER NOT NULL,
person_id NUMBER,
evaluation_date DATE,
grade_code VARCHAR2(30),
reason_for_no_grade_code VARCHAR2(30),
grade_repeated_flag NUMBER,
further_assessment_reqd_flag NUMBER,
further_assessment_reqd VARCHAR2(1000),
high_achiever_flag NUMBER,
avg_achiever_flag NUMBER,
low_achiever_flag NUMBER,
good_grades_flag NUMBER,
likes_school_flag NUMBER,
struggles_with_school_flag NUMBER,
desires_post_sec_ed_flag NUMBER,
cognitive_function_code VARCHAR2(30),
performance_details VARCHAR2(3000),
school_problems VARCHAR2(3000),
behaviour_problems_code VARCHAR2(30),
academic_problems_code VARCHAR2(30),
prolonged_absence_flag NUMBER,
acad_delay_poor_attend_flag NUMBER,
attendance VARCHAR2(3000),
tutoring_other VARCHAR2(250),
clubs VARCHAR2(250),
activities_other VARCHAR2(250),
expected_graduation_date DATE,
diploma_received_flag NUMBER,
graduation_date DATE,
ged_certificate_flag NUMBER,
ged_date DATE,
graduate_before19_flag NUMBER,
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE,
educational_setting VARCHAR2(4000)
8.  s_academic_evaluation_flags (
snapshot_id NUMBER NOT NULL,
academic_evaluation_flags_id NUMBER NOT NULL,
academic_evaluation_id NUMBER,
academic_flag_code VARCHAR2(30),
academic_flag_group_code VARCHAR2(30),
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE)
9.  s_address (
snapshot_id NUMBER NOT NULL,
address_id NUMBER NOT NULL,
domestic_foreign_flag NUMBER,
street_nbr VARCHAR2(10),
pre_directional_code VARCHAR2(30),
street_name VARCHAR2(200),
street_suffix_code VARCHAR2(30),
post_directional_code VARCHAR2(30),
unit_name_code VARCHAR2(30),
unit_nbr VARCHAR2(20),
neighborhood_name VARCHAR2(30),
city_name VARCHAR2(60),
county_code VARCHAR2(30),
other_county_name VARCHAR2(50),
state_code VARCHAR2(30),
zip5_code VARCHAR2(5),
zip4_code VARCHAR2(4),
directions VARCHAR2(4000),
school_district_code VARCHAR2(30),
other_school_district_name VARCHAR2(50),
jurisdiction_code VARCHAR2(30),
other_jurisdiction_name VARCHAR2(50),
geo_code VARCHAR2(30),
census_tract VARCHAR2(30),
valid_address_flag NUMBER,
foreign_address_line1 VARCHAR2(200),
foreign_address_line2 VARCHAR2(200),
foreign_address_line3 VARCHAR2(200),
env_hazard_flag NUMBER,
env_hazard_desc VARCHAR2(4000),
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE)
10.  s_agency (
snapshot_id NUMBER NOT NULL,
agency_id NUMBER NOT NULL,
party_id NUMBER,
agency_code VARCHAR2(30),
agency_logo_code VARCHAR2(30),
agency_name VARCHAR2(100),
agency_reference_id NUMBER,
coa_flag NUMBER,
comments_text VARCHAR2(200),
inactive_flag NUMBER,
main_office_flag NUMBER,
cwla_flag NUMBER,
ive_flag NUMBER,
nasw_flag NUMBER,
office_url VARCHAR2(100),
public_agency_flag NUMBER,
bulk_med_flag NUMBER,
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE,
placement_pay_flag NUMBER,
payment_code VARCHAR2(2),
account_number NUMBER,
ein_number VARCHAR2(9),
address_code NUMBER,
oaks_vendor_id VARCHAR2(10),
medicaid_live_flag NUMBER,
default_court_detail_id NUMBER)
11. s_aka (
snapshot_id NUMBER NOT NULL,
aka_id NUMBER NOT NULL,
person_id NUMBER,
aka_prefix_code VARCHAR2(30),
aka_first_name VARCHAR2(35),
aka_middle_name VARCHAR2(25),
aka_last_name VARCHAR2(35),
aka_suffix_code VARCHAR2(30),
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE,
aka_type_code VARCHAR2(30)
)
12. s_background_check_result (
snapshot_id NUMBER NOT NULL,
background_check_id NUMBER NOT NULL,
person_id NUMBER,
offense VARCHAR2(50),
offense_city VARCHAR2(50),
offense_state_code VARCHAR2(2),
conviction_date DATE,
sentence VARCHAR2(50),
probation_parole_code VARCHAR2(30),
license_revoked_code VARCHAR2(30),
background_check_comment VARCHAR2(2000),
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE)
13.  s_birth_information (
snapshot_id NUMBER NOT NULL,
birth_information_id NUMBER NOT NULL,
person_id NUMBER,
hospital_name VARCHAR2(90),
county_code VARCHAR2(30),
other_county VARCHAR2(50),
birth_weight_lbs VARCHAR2(10),
birth_weight_ozs VARCHAR2(10),
birth_weight_kgs VARCHAR2(10),
birth_weight_gms VARCHAR2(10),
birth_length_inches VARCHAR2(10),
rh_factor_code VARCHAR2(30),
apgar1_min_score_code VARCHAR2(30),
apgar5_min_score_code VARCHAR2(30),
gestational_term VARCHAR2(30),
initial_pregnancy_checkup_code VARCHAR2(30),
labor_duration VARCHAR2(20),
location_details_text VARCHAR2(100),
position_code VARCHAR2(30),
delivery_type_code VARCHAR2(30),
cesarean_explanation VARCHAR2(300),
heart_murmur_birth_code VARCHAR2(30),
heart_murmur_birth_text VARCHAR2(300),
atbirth_complications VARCHAR2(300),
postbirth_complications VARCHAR2(300),
newborn_blood_type_code VARCHAR2(30),
newborn_has_pku_code VARCHAR2(30),
nb_has_sickle_cell_code VARCHAR2(30),
nb_has_galactosemia_code VARCHAR2(30),
nb_has_hypothyroidism_code VARCHAR2(30),
newborn_has_other1_code VARCHAR2(30),
newborn_has_other1 VARCHAR2(25),
newborn_has_other2_code VARCHAR2(30),
newborn_has_other2 VARCHAR2(25),
multiple_births_code VARCHAR2(30),
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE)
14.  s_characteristic (
snapshot_id NUMBER NOT NULL,
characteristic_id NUMBER NOT NULL,
characteristic_group_code VARCHAR2(30),
person_id NUMBER,
characteristic_code VARCHAR2(30),
sub_category_code VARCHAR2(30),
self_reported_code VARCHAR2(30),
observed_code VARCHAR2(30),
clinically_diagnosed_code VARCHAR2(30),
diagnosed_by VARCHAR2(100),
diagnosis_date DATE,
observed_by VARCHAR2(100),
observation_date DATE,
additional_information VARCHAR2(4000),
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE,
begin_eff_date DATE,
self_reported_by VARCHAR2(100),
not_applicable_code VARCHAR2(30),
end_reason VARCHAR2(2000),
end_eff_date DATE,
self_reported_date DATE,
unknown_code VARCHAR2(30))
15.  s_childhood_illness (
snapshot_id NUMBER NOT NULL,
childhood_illness_id NUMBER NOT NULL,
medical_record_status_id NUMBER,
illness_value_group_code VARCHAR2(30),
illness_value_code VARCHAR2(30),
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE)
16.  s_discharge_info (
snapshot_id NUMBER NOT NULL,
discharge_id NUMBER NOT NULL,
person_id NUMBER,
discharge_code VARCHAR2(30),
start_date DATE,
end_date DATE,
grade_level_code VARCHAR2(30),
explanation VARCHAR2(3000),
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE)
17.  s_employee (
snapshot_id NUMBER NOT NULL,
employee_id NUMBER NOT NULL,
employee_county_id VARCHAR2(10),
employee_email_address VARCHAR2(100),
hire_date DATE,
termination_date DATE,
on_leave_flag NUMBER,
supervisor_override_flag NUMBER,
partnership_flag NUMBER,
first_year_flag NUMBER,
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE,
bci_requested_date DATE,
bci_received_date DATE)
18.  s_employment_history (
snapshot_id NUMBER NOT NULL,
employment_history_id NUMBER NOT NULL,
person_id NUMBER,
employer_name VARCHAR2(40),
begin_date DATE,
end_date DATE,
address_line1 VARCHAR2(100),
address_line2 VARCHAR2(100),
city VARCHAR2(30),
state_code VARCHAR2(30),
zip_code VARCHAR2(5),
zip_code_ext VARCHAR2(4),
country VARCHAR2(15),
employment_comments VARCHAR2(400),
phone_nbr VARCHAR2(10),
phone_nbr_ext VARCHAR2(7),
invalid_flag NUMBER,
invalid_reason VARCHAR2(400),
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE,
crise_flag NUMBER)
19.  s_ethnicity (
snapshot_id NUMBER NOT NULL,
ethnicity_id NUMBER NOT NULL,
person_id NUMBER,
ethnicity_code VARCHAR2(30),
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE)
20.  s_health_care_provider (
snapshot_id NUMBER NOT NULL,
healthcare_provider_info_id NUMBER NOT NULL,
person_id NUMBER,
provider_id NUMBER,
provider_name_code VARCHAR2(90),
provider_type_code VARCHAR2(30),
first_visit_date DATE,
service_end_date DATE,
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE,
crise_flag NUMBER)
21.  s_iep (
snapshot_id NUMBER NOT NULL,
iep_id NUMBER NOT NULL,
person_id NUMBER,
copy_of_iep_flag NUMBER,
school_district_code VARCHAR2(30),
other_district VARCHAR2(50),
effective_date DATE,
expiration_date DATE,
participants VARCHAR2(2000),
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE)
22.  s_iep_amendment (
snapshot_id NUMBER NOT NULL,
iep_amendment_id NUMBER NOT NULL,
iep_id NUMBER,
amendment_date DATE,
participants VARCHAR2(2000),
school_district_code VARCHAR2(30),
other_district VARCHAR2(50),
reason VARCHAR2(1000),
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE)
23.  s_immunization_information (
snapshot_id NUMBER NOT NULL,
immunization_information_id NUMBER NOT NULL,
mental_treatment_details_id NUMBER,
medical_treatment_details_id NUMBER,
person_id NUMBER,
immunization_type_code VARCHAR2(30),
other_immunization_type VARCHAR2(50),
immunization_date DATE,
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE)
24.  s_immunization_record_status (
snapshot_id NUMBER NOT NULL,
immunization_status_id NUMBER NOT NULL,
person_id NUMBER,
no_immunization_flag NUMBER,
no_immunization_reason VARCHAR2(300),
immun_upto_date_code VARCHAR2(30),
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE)
25.  S_insurance_coverage (
snapshot_id NUMBER NOT NULL,
insurance_coverage_id NUMBER NOT NULL,
person_id NUMBER,
insurance_coverage_type VARCHAR2(30),
policy_holder_id NUMBER,
policy_id VARCHAR2(40),
group_id VARCHAR2(40),
begin_date DATE,
end_date DATE,
coverage_from_employer_flag NUMBER,
provider_name VARCHAR2(50),
address_line1 VARCHAR2(100),
address_line2 VARCHAR2(100),
city_name VARCHAR2(40),
state_code VARCHAR2(30),
zip5_code VARCHAR2(5),
zip4_code VARCHAR2(4),
phone_number VARCHAR2(10),
employer_name VARCHAR2(40),
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE,
phone_extension VARCHAR2(7),
crise_flag NUMBER,
invalid_flag NUMBER,
invalid_reason VARCHAR2(400))
26.  s_insurance_participant (
snapshot_id NUMBER NOT NULL,
insurance_participant_id NUMBER NOT NULL,
insurance_coverage_id NUMBER,
covered_person_id NUMBER,
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE)
27.  s_marital_history (
snapshot_id NUMBER NOT NULL,
marital_hist_id NUMBER NOT NULL,
person_id NUMBER,
spouse_name VARCHAR2(60),
rel_status_code VARCHAR2(30),
begin_eff_date DATE,
end_eff_date DATE,
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE,
crise_flag NUMBER)
28.  s_medical_records (
snapshot_id NUMBER NOT NULL,
medical_record_status_id NUMBER NOT NULL,
person_id NUMBER,
medical_records_avail_code VARCHAR2(30),
medical_record_status_text VARCHAR2(1000),
other_childhood_disease1_code VARCHAR2(30),
other_childhood_disease1_text VARCHAR2(300),
other_childhood_disease2_code VARCHAR2(30),
other_childhood_disease2_text VARCHAR2(300),
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE)
29.  s_medical_sp_treatment_details (
snapshot_id NUMBER NOT NULL,
treatment_details_id NUMBER NOT NULL,
height VARCHAR2(25),
weight VARCHAR2(25),
req_physical_exam_flag NUMBER,
procedure_text VARCHAR2(3000),
prognosis_text VARCHAR2(3000),
diagnosis_results VARCHAR2(3000),
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE,
other_medical_information VARCHAR2(4000))
30.  s_medication_information (
snapshot_id NUMBER NOT NULL,
medication_information_id NUMBER NOT NULL,
mental_treatment_details_id NUMBER,
medical_treatment_details_id NUMBER,
person_id NUMBER,
medication_name VARCHAR2(90),
medication_dosage VARCHAR2(100),
prescribed_by VARCHAR2(90),
number_of_refills_prescribed VARCHAR2(50),
initial_prescription_date DATE,
location_details_text VARCHAR2(100),
discontinued_date DATE,
notes VARCHAR2(1000),
pharmacy_name_text VARCHAR2(90),
phone_text VARCHAR2(10),
phone_ext VARCHAR2(7),
fax_number VARCHAR2(10),
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE,
medication_daily_code VARCHAR2(30))
31.  S_mental_diagnosis_details (
snapshot_id NUMBER NOT NULL,
mental_diagnosis_id NUMBER NOT NULL,
treatment_details_id NUMBER,
mental_diag_group_code VARCHAR2(30),
mental_diagnosis_code VARCHAR2(30),
condition_present_flag NUMBER,
details_text VARCHAR2(3000),
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE)
32.  S_mental_treatment_details (
snapshot_id NUMBER NOT NULL,
treatment_details_id NUMBER NOT NULL,
weight VARCHAR2(25),
recommendation_text VARCHAR2(3000),
prognosis_text VARCHAR2(3000),
other_concerns_details VARCHAR2(3000),
diagnosed_by VARCHAR2(90),
diagnosed_date DATE,
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE)
33.  s_mfe (
snapshot_id NUMBER NOT NULL,
mfe_id NUMBER NOT NULL,
person_id NUMBER,
mfe_date DATE,
school_district_code VARCHAR2(30),
other_district VARCHAR2(50),
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE)
34.  s_military_history (
snapshot_id NUMBER NOT NULL,
military_history_id NUMBER NOT NULL,
person_id NUMBER,
military_branch_code VARCHAR2(30),
other_military_text VARCHAR2(50),
entered_date DATE,
discharged_date DATE,
discharge_type_code VARCHAR2(30),
discharge_explanation_text VARCHAR2(200),
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE,
crise_flag NUMBER)
35.  s_parental_status (
snapshot_id NUMBER NOT NULL,
parental_status_id NUMBER NOT NULL,
person_id NUMBER,
parental_status_type_code VARCHAR2(30),
first_name VARCHAR2(60),
last_name VARCHAR2(60),
gender_code VARCHAR2(30),
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE,
crise_flag NUMBER)
36.  s_party (
snapshot_id NUMBER NOT NULL,
party_id NUMBER NOT NULL,
party_type_code VARCHAR2(30),
party_name VARCHAR2(60))
37.  s_party_address (
snapshot_id NUMBER NOT NULL,
party_address_id NUMBER NOT NULL,
party_id NUMBER,
address_id NUMBER,
party_address_type_code VARCHAR2(30),
care_of_name VARCHAR2(250),
delivery_desc VARCHAR2(250),
primary_address_flag NUMBER,
begin_eff_date DATE,
end_eff_date DATE,
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE)
38.  s_party_contact_medium (
snapshot_id NUMBER NOT NULL,
party_contact_med_id NUMBER NOT NULL,
party_id NUMBER,
contact_type_code VARCHAR2(30),
party_contact VARCHAR2(130),
party_contact_description VARCHAR2(100),
primary_flag NUMBER,
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE)
39.  s_performance (
snapshot_id NUMBER NOT NULL,
performance_id NUMBER NOT NULL,
person_id NUMBER,
education_record_status_code VARCHAR2(30),
missing_records_explanation VARCHAR2(500),
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE)
40.  s_person (
snapshot_id NUMBER NOT NULL,
person_id NUMBER NOT NULL,
party_id NUMBER,
prefix_code VARCHAR2(30),
first_name VARCHAR2(35),
middle_name VARCHAR2(25),
last_name VARCHAR2(35),
suffix_code VARCHAR2(30),
gender_code VARCHAR2(30),
ssn VARCHAR2(11),
birth_date DATE,
birth_city_name VARCHAR2(50),
birth_state VARCHAR2(40),
birth_country_name VARCHAR2(20),
driv_lic_nbr VARCHAR2(15),
driv_lic_exp_date DATE,
driv_lic_iss_state_code VARCHAR2(2),
indian_tribe1 VARCHAR2(60),
indian_tribe2 VARCHAR2(60),
religion_code VARCHAR2(30),
other_religion_desc VARCHAR2(50),
other_race_desc VARCHAR2(50),
other_ethnicity_desc VARCHAR2(50),
other_language_desc VARCHAR2(50),
interpreter_flag NUMBER,
deceased_flag NUMBER,
deceased_date DATE,
prev_adopted_code VARCHAR2(1),
age_adopted_code VARCHAR2(30),
education_level_code VARCHAR2(30),
hispanic_latino_code VARCHAR2(30),
ohio_resident_flag NUMBER,
actv_military_flag NUMBER,
dep_actv_military_flag NUMBER,
criminal_hist_code VARCHAR2(1),
safety_hazard_flag NUMBER,
env_hazard_flag NUMBER,
safety_plan_flag NUMBER,
awol_flag NUMBER,
mental_hlth_haz_flag NUMBER,
drug_actvty_haz_flag NUMBER,
contag_disease_haz_flag NUMBER,
domestic_violence_haz_flag NUMBER,
gang_actvty_haz_flag NUMBER,
prior_wrkr_threat_haz_flag NUMBER,
violent_crime_haz_flag NUMBER,
sexual_predator_haz_flag NUMBER,
explosive_behav_haz_flag NUMBER,
protective_service_alert_flag NUMBER,
safety_haz_narrative VARCHAR2(4000),
background_check_rcvd_date DATE,
background_check_rqstd_date DATE,
created_by NUMBER,
created_date DATE,
modified_by NUMBER,
modified_date DATE,
char_not_completed_flag NUMBER,
char_not_disability_flag NUMBER,
estimated_dob_flag NUMBER,
weapons_hazard_flag NUMBER,
other_hazard_flag NUMBER,
indian_tribe_recognized1 VARCHAR2(30),
indian_tribe_recognized2 VARCHAR2(30),
rapback_flag NUMBER,
deceased_date_unknown_flag NUMBER,
us_citizen_code VARCHAR2(30),
alien_status_code VARCHAR2(30),
country_of_origin_code VARCHAR2(30),
us_entry_date DATE,
crise_segment_date DATE,
verification_code_1 VARCHAR2(10),