ЕСОЗ - публічна документація
1. Fraud Detection DB
Purpose
The main goal of the Fraud Detection mechanism is to provide a possibility to identify and prevent possible financial losses due to fraud activities. In order to be able to do that there is a necessity to create fraud DB and using it implement triggers which automatically shows who is likely to be fraud.
Fraud DB structure
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 speciality_officio speciality.speciality_officio=true speciality.valid_to_date speciality_officio_valid_to_date speciality.speciality_officio=true parties
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 educations[count] - count items in the array qualifications qualifications qualifications qualifications_qty qualifications[count] - count items in the array specialities specialities specialities specialities_qty specialities[count] - count items in the array 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 inserted_by inserted_at inserted_at updated_by updated_by updated_at updated_at contract_employees - without changes
field_origin table_field_name description id id staff_units staff_units declaration_limit declaration_limit employee_id employee_id division_id division_id contract_id contract_id inserted_by inserted_by updated_at updated_at start_date start_date end_date end_date inserted_at inserted_at updated_by updated_by
- 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
4. persons
| 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 |
ЕСОЗ - публічна документація