| Table of Contents |
|---|
...
To simplify report creation and data analysis current tables should be denormalized and aggregated.
Table for
...
capitation
- prm_db
- legal_entities
- divisions
- employees
- parties
- party_users
- black_list_users
- audit_log
- contracts
- contract_divisions
- contract_employees
- ops_db
- declarations
- declarations_status_hstr
- il_db
- declaration_requests
- employee_requests
- dictionaries
- contact_requests
- mpi_db
- persons
- audit_log
Mapping:
- prm_db
legal_entities
field_origin table_field_name description id id name name short_name short_name public_name public_name status status type type owner_property_type owner_property_type legal_form legal_form edrpou edrpou kveds kveds to_string(kveds) addresses.country registration_country type='REGISTRATION' addresses.area registration_area type='REGISTRATION' addresses.region registration_region type='REGISTRATION' addresses.settlement registration_settlement type='REGISTRATION' addresses.settlement_type registration_settlement_type type='REGISTRATION' addresses.settlement_id registration_settlement_id type='REGISTRATION' addresses.street_type registration_street_type type='REGISTRATION' addresses.street registration_street type='REGISTRATION' addresses.building&addresses.apartment registration_building type='REGISTRATION', to_char(addresses.building&', '&addresses.apartment) addresses.zip registration_zip type='REGISTRATION' addresses.country residence_country type='RESIDENCE' addresses.area residence_area type='RESIDENCE' addresses.region residence_region type='RESIDENCE' addresses.settlement residence_settlement type='RESIDENCE' addresses.settlement_type residence_settlement_type type='RESIDENCE' addresses.settlement_id residence_settlement_id type='RESIDENCE' addresses.street_type residence_street_type type='RESIDENCE' addresses.street residence_street type='RESIDENCE' addresses.building&addresses.apartment residence_building type='RESIDENCE', to_char(addresses.building&', '&addresses.apartment) addresses.zip residence_zip type='RESIDENCE' phones mobile_phone type='MOBILE' phones land_line_phone type='LAND_LINE' email email is_active is_active inserted_by inserted_by updated_by updated_by inserted_at inserted_at updated_at updated_at capitation_contract_id capitation_contract_id created_by_mis_client_id created_by_mis_client_id mis_verified mis_verified nhs_verified nhs_verified divisions
field_origin table_field_name description id id external_id external_id name name type type mountain_group mountain_group addresses.country residence_country type='RESIDENCE' addresses.area residence_area type='RESIDENCE' addresses.region residence_region type='RESIDENCE' addresses.settlement residence_settlement type='RESIDENCE' addresses.settlement_type residence_settlement_type type='RESIDENCE' addresses.settlement_id residence_settlement_id type='RESIDENCE' addresses.street_type residence_street_type type='RESIDENCE' addresses.street residence_street type='RESIDENCE' addresses.building&addresses.apartment residence_building type='RESIDENCE', to_char(addresses.building&', '&addresses.apartment) addresses.zip residence_zip type='RESIDENCE' addresses.country registration_country type='REGISTRATION' addresses.area registration_area type='REGISTRATION' addresses.region registration_region type='REGISTRATION' addresses.settlement registration_settlement type='REGISTRATION' addresses.settlement_type registration_settlement_type type='REGISTRATION' addresses.settlement_id registration_settlement_id type='REGISTRATION' addresses.street_type registration_street_type type='REGISTRATION' addresses.street registration_street type='REGISTRATION' addresses.building&addresses.apartment registration_building type='REGISTRATION', to_char(addresses.building&', '&addresses.apartment) addresses.zip registration_zip type='REGISTRATION' phones mobile_phone type='MOBILE' phones
land_line_phone type='LAND_LINE' email email inserted_at inserted_at updated_at updated_at legal_entity_id legal_entity_id location location status status is_active is_active employees
field_origin table_field_name description id id position position status status employee_type employee_type is_active is_active inserted_by inserted_by updated_by updated_by start_date start_date end_date end_date legal_entity_id legal_entity_id division_id division_id party_id party_id inserted_at
inserted_at updated_at updated_at status_reason status_reason speciality additionalspeciality_info.educations educations additional_info.educations additional_info.officio speciality.speciality_officio=true speciality.valid_to_date speciality_officio_valid_to_date speciality.speciality_officio=true parties
parties
additional_info.field_origin table_field_name description id id no_tax_id no_tax_id gender gender inserted_by inserted_by updated_by updated_by inserted_at inserted_at updated_at updated_at educations educations educations educations_qty
additional_info.educations[count] - count items in the array
additional_info.qualifications qualifications qualifications additional_info.qualifications
additional_info.qualifications_qty
additional_info.additional_info.qualifications[count] - count items in the array specialities specialities additional_info.specialities
additional_info.specialities specialities_qty specialities[count] - count items in the array additional_info.specialities.speciality speciality_officio additional_info.specialities.speciality_officio=true additional_info.specialities.valid_to_date speciality_officio_valid_to_date additional_info.specialities.speciality_officio=true additional_info.science_degree science_degree additional_info.science_degree science_degree science_degree party_users- without changes
audit_log
field_origin table_field_name description id id actor_id actor_id resource resource resource_id resource_id changeset changeset inserted_at inserted_at contracts
field_origin table_field_name description id id start_date start_date end_date end_date status status contractor_legal_entity_id contractor_legal_entity_id contractor_owner_id contractor_owner_id contractor_base contractor_base contractor_payment_details_mfo contractor_payment_details.MFO contractor_payment_details_bank_name contractor_payment_details.bank_name contractor_payment_details_payer_account contractor_payment_details.payer_account contractor_rmsp_amount contractor_rmsp_amount external_contractor_flag external_contractor_flag external_contractors external_contractors array! nhs_signer_id nhs_signer_id nhs_signer_base nhs_signer_base nhs_legal_entity_id nhs_legal_entity_id nhs_payment_method nhs_payment_method is_active is_active is_suspended is_suspended issue_city issue_city nhs_contract_price nhs_contract_price contract_number contract_number contract_request_id contract_request_id status_reason status_reason inserted_by inserted_by inserted_at inserted_at updated_by updated_by updated_at updated_at parent_contract_id parent_contract_id id_form id_form nhs_signed_date nhs_signed_date contract_divisions - without changes
field_origin table_field_name description id id division_id division_id contract_id contract_id inserted_by updatedinserted_by inserted_at inserted_at updated_by updated_by updated_at updated_at partycontract_
usersemployees - without changes
audit_log
field_origin table_field_name description id idactor staff_units staff_units declaration_limit declaration_limit employee_id actoremployee_id resource resource resourcedivision_id division_id contract_id resourcecontract_id changeset changesetinserted_by inserted_by updated_at updated_at start_date start_date end_date end_date inserted_at inserted_at updated_by updated_by
inserted_by inserted_by updatedgender gender - ops_db
- declarations - without field "seed"
- declarations_status_hstr - without changes
- il_db
declaration_requests
field_origin table_field_name description id id declaration_id declaration_id authentication_method_current.type auth_method authentication_method_current.type authentication_method_current.number auth_number authentication_method_current.{type='OTP'}.number status status inserted_by inserted_by inserted_at inserted_at updated_by updated_by updated_at updated_at employee_requests
field_origin table_field_name description id id employee_id employee_id status status inserted_at inserted_at updated_at updated_at contract_requests
field_origin table_field_name description id id contractor_legal_entity_id contractor_legal_entity_id contractor_owner_id contractor_owner_id contractor_base contractor_base contractor_payment_details_mfo contractor_payment_details.MFO contractor_payment_details_bank_name contractor_payment_details.bank_name contractor_payment_details_payer_account contractor_payment_details.payer_account contractor_rmsp_amount contractor_rmsp_amount external_contractor_flag external_contractor_flag start_date start_date end_date end_date nhs_legal_entity_id nhs_legal_entity_id nhs_signer_id nhs_signer_id nhs_signer_base nhs_signer_base issue_city issue_city status status status_reason status_reason nhs_contract_price nhs_contract_price nhs_payment_method nhs_payment_method contract_number contract_number contract_id contract_id id_form id_form inserted_by inserted_by inserted_at inserted_at updated_by updated_by updated_at updated_at nhs_signed_date nhs_signed_date parent_contract_id parent_contract_id misc misc previous_request_id previous_request_id assignee_id assignee_id external_contractors external_contractors jsonb[] contractor_employee_divisions contractor_employee_divisions jsonb[] contractor_divisions contractor_divisions jsonb contractor_signed contractor_signed - black_list_users - without changes
- dictionaries
...
| field_origin | table_field_name | description |
|---|---|---|
| id | id | |
| birth_date | birth_date | |
| birth_country | birth_country | |
| gender | gender | |
death_date | death_date | |
| is_active | is_active | |
| status | status | |
| patient_signed | patient_signed | |
| process_disclousure_data_consent | process_disclousure_data_consent | |
| phones | mobile_phone | type='MOBILE' |
| phones | land_line_phone | type='LAND_LINE' |
| authentication_method.type | auth_method | authentication_method.type |
| authentication_method.number | auth_number | authentication_method.{type='OTP'}.number |
| inserted_by | inserted_by | |
| updated_by | updated_by | |
| inserted_at | inserted_at | |
| updated_at | updated_at |
Tables for reimbursement
- prm
- ingridients
- innms
- medical_programs
- medication
- program_medications
- ops
- medication_dispense_details
- medication_dispense_status_hstr
- medication_dispenses
- medication_requests
- medication_requests_status_hstr
- il
- declaration_requests
- medication_request_request
Mapping
- prm
ingridients
field_origin table_field_name id id dosage dosage dosage.numerator_unit numerator_unit dosage.numerator_value
numerator_value dosage.denumerator_unit denumerator_unit dosage.denumerator_value denumerator_value is_primary is_primary medication_child_id medication_child_id innm_child_id innm_child_id parent_id parent_id inserted_at inserted_at updated_at updated_at - innms - no changes
- medical_programs - no changes
- medication
| field_origin | table_field_name |
|---|---|
| id | id |
| name | name |
| type | type |
| manufacturer | manufacturer |
| manufacturer.name | manufacturer_name |
| manufacturer.country | manufacturer_country |
| code_atc | code_atc |
| is_active | is_active |
| form | form |
| container | container |
| container.numerator_unit | numerator_unit |
| container.numerator_value | numerator_value |
| container.denumerator_unit | denumerator_unit |
| container.denumerator_value | denumerator_value |
| package_qty | package_qty |
| package_min_qty | package_min_qty |
| certificate | certificate |
| certificate_expired_at | certificate_expired_at |
| inserted_at | inserted_at |
| inserted_by | inserted_by |
| updated_at | updated_at |
| updated_by | updated_by |
- program_medications
| field_origin | table_field_name |
|---|---|
| id | id |
| reimbursement | reimbursement |
| reimbursement.type | reimbursement_type |
| reimbursement.reimbursement_amount | reimbursement_amount |
| is_active | is_active |
| medication_request_allowed | medication_request_allowed |
| medication_id | medication_id |
| medical_program_id | medical_program_id |
| inserted_at | inserted_at |
| inserted_by | inserted_by |
| updated_at | updated_at |
| updated_by | updated_by |
2. ops
- medication_dispense_details - no changes
- medication_dispense_status_hstr - no changes
- medication_dispenses - no changes
- medication_requests - without verification_code
- medication_requests_status_hstr - no changes
3. il
- medication_request_request
| field_origin | table_field_name |
|---|---|
| id | id |
| data | data |
| data.created_at | created_at |
| data.dispense_valid_from | dispense_valid_from |
| data.dispense_valid_to | dispense_valid_to |
| data.division_id | division_id |
| data.employee_id | employee_id |
| data.ended_at | ended_at |
| data.legal_entity_id | legal_entity_id |
| data.medical_program_id | medical_program_id |
| data.medication_id | medication_id |
| data.medication_qty | medication_qty |
| data.person_id | person_id |
| data.started_at | started_at |
| request_number | request_number |
| status | status |
| medication_request_id | medication_request_id |
| inserted_at | inserted_at |
| inserted_by | inserted_by |
| updated_at | updated_at |
| updated_by | updated_by |