Versions Compared

Old Version 12

changes.mady.by.user Bogdana Konovalenko (Unlicensed)

Saved on

compared with

New Version Current

changes.mady.by.user Aleksandr Virny (Unlicensed)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Table of Contents

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. 

Based on current tables next data must be aggregated and analyzed:

  • Number of patients per doctor
  • Number of patients registered for the same phone number
  • Number and percentage of patients per doctor that have set offline authorization method
  • Number and percentage of patients per legal entity that have set offline authorization method 

Fraud DB structure 

To simplify report creation and data analysis current tables should be denormalized  and aggregated.

Table for replication

  1. prm_db
    • legal_entities
    • divisions
    • employees
    • parties
    • party_users
    • black_list_users
    • audit_log
  2. ops_db
    • declarations
    • declarations_status_hstr
  3. il_db
    • declaration_requests
    • employee_requests
    • dictionaries
  4. mpi_db
    • persons

Mapping:

...

Table of Contents

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

  1. prm_db
    • legal_entities
    • divisions
    • employees
    • parties
    • party_users
    • black_list_users
    • audit_log
    • contracts
    • contract_divisions
    • contract_employees
  2. ops_db
    • declarations
    • declarations_status_hstr
  3. il_db
    • declaration_requests
    • employee_requests
    • dictionaries
    • contact_requests
  4. mpi_db
    • persons
    • audit_log

Mapping:

...

  1. prm_db

    •   legal_entities

      field_origintable_field_namedescription
      idid
      namename
      short_nameshort_name
      public_namepublic_name
      statusstatus
      typetype
      owner_property_typeowner_property_type
      legal_formlegal_form
      edrpouedrpou
      kvedskvedsto_string(kveds)
      addresses.countryregistration_countrytype='REGISTRATION'
      addresses.arearegistration_areatype='REGISTRATION'
      addresses.regionregistration_regiontype='REGISTRATION'
      addresses.settlementregistration_settlementtype='REGISTRATION'
      addresses.settlement_typeregistration_settlement_typetype='REGISTRATION'
      addresses.settlement_idregistration_settlement_idtype='REGISTRATION'
      addresses.street_typeregistration_street_typetype='REGISTRATION'
      addresses.streetregistration_streettype='REGISTRATION'
      addresses.building&addresses.apartmentregistration_buildingtype='REGISTRATION', to_char(addresses.building&', '&addresses.apartment)
      addresses.zipregistration_ziptype='REGISTRATION'
      addresses.countryresidence_countrytype='RESIDENCE'
      addresses.arearesidence_areatype='RESIDENCE'
      addresses.regionresidence_regiontype='RESIDENCE'
      addresses.settlementresidence_settlementtype='RESIDENCE'
      addresses.settlement_typeresidence_settlement_typetype='RESIDENCE'
      addresses.settlement_idresidence_settlement_idtype='RESIDENCE'
      addresses.street_typeresidence_street_typetype='RESIDENCE'
      addresses.streetresidence_streettype='RESIDENCE'
      addresses.building&addresses.apartmentresidence_buildingtype='RESIDENCE', to_char(addresses.building&', '&addresses.apartment)
      addresses.zipresidence_ziptype='RESIDENCE'
      phonesmobile_phonetype='MOBILE'
      phonesland_line_phonetype='LAND_LINE'
      emailemail
      is_activeis_active
      inserted_byinserted_by
      updated_byupdated_by
      inserted_atinserted_at
      updated_atupdated_at
      capitation_contract_idcapitation_contract_id
      created_by_mis_client_idcreated_by_mis_client_id
      mis_verifiedmis_verified
      nhs_verifiednhs_verified


    • divisions

      registration_settlement_typeemail
      field_origintable_field_namedescription
      idid
      external_idexternal_id
      namename
      short_nameshort_name
      public_namepublic_namestatusstatustypetypeowner_property_typeowner_property_typelegal_formlegal_formedrpouedrpou
      kvedskvedsto_string(kveds)
      addresses.countryregistration_countrytype='REGISTRATION'
      addresses.arearegistration_areatype='REGISTRATION'
      addresses.regionregistration_regiontype='REGISTRATION'
      addresses.settlementregistration_settlementtype='REGISTRATION'
      addresses.settlement_type
      typetype
      mountain_groupmountain_group
      addresses.countryresidence_countrytype='RESIDENCE'
      addresses.arearesidence_areatype='RESIDENCE'
      addresses.regionresidence_regiontype='RESIDENCE'
      addresses.settlementresidence_settlementtype='RESIDENCE'
      addresses.settlement_typeresidence_settlement_typetype='RESIDENCE'
      addresses.settlement_idresidence_settlement_idtype='RESIDENCE'
      addresses.street_typeresidence_street_typetype='RESIDENCE'
      addresses.streetresidence_streettype='RESIDENCE'
      addresses.building&addresses.apartmentresidence_buildingtype='RESIDENCE', to_char(addresses.building&', '&addresses.apartment)
      addresses.zipresidence_ziptype='RESIDENCE'
      addresses.countryregistration_countrytype='REGISTRATION'
      addresses.settlement_idarearegistration_settlement_idareatype='REGISTRATION'
      addresses.street_typeregionregistration_street_typeregiontype='REGISTRATION'
      addresses.streetsettlementregistration_streetsettlementtype='REGISTRATION'
      addresses.building&addresses.apartmentsettlement_typeregistration_buildingsettlement_typetype='REGISTRATION', to_char(addresses.building&', '&addresses.apartment)'REGISTRATION'
      addresses.zipsettlement_idregistration_zipsettlement_idtype='REGISTRATION'
      addresses.countryresidence_countrystreet_typeregistration_street_typetype='RESIDENCEREGISTRATION'
      addresses.areastreetresidenceregistration_areastreettype='RESIDENCEREGISTRATION'
      addresses.regionresidence_regionbuilding&addresses.apartmentregistration_buildingtype='RESIDENCE'
      addresses.settlementresidence_settlementtype='RESIDENCE'
      addresses.settlement_typeresidence_settlement_typeREGISTRATION', to_char(addresses.building&', '&addresses.apartment)
      addresses.zipregistration_ziptype='RESIDENCEREGISTRATION'addresses.settlement_id
      residence_settlement_idphonesmobile_phonetype='RESIDENCEMOBILE'
      addresses.street_typeresidence_street_type

      phones

      		land_line_phonetype='RESIDENCELAND_LINE'
      addresses.streetresidence_streettype='RESIDENCE'
      addresses.building&addresses.apartmentresidence_buildingtype='RESIDENCE', to_char(addresses.building&', '&addresses.apartment)
      addresses.zipresidence_ziptype='RESIDENCE'
      phonesmobile_phonetype='MOBILE'
      phonesland_line_phonetype='LAND_LINE'
      email
      emailemail
      inserted_atinserted_at
      updated_atupdated_at
      legal_entity_idlegal_entity_id
      locationlocation
      statusstatus
      is_activeis_active


    • employees

      field_origintable_field_namedescription
      idid
      positionposition
      statusstatus
      employee_typeemployee_type
      is_activeis_active
      inserted_byinserted_by
      updated_byupdated_by
      inserted_atinserted_atupdated_atupdated_atcapitation_contract_idcapitation_contract_idcreated_by_mis_client_idcreated_by_mis_client_idmis_verifiedmis_verifiednhs_verifiednhs_verified
      divisions

      start_datestart_date
      end_dateend_date
      legal_entity_idlegal_entity_id
      division_iddivision_id
      party_idparty_id

      inserted_at

      		inserted_at
      updated_atupdated_at
      status_reasonstatus_reason
      specialityspeciality_officiospeciality.speciality_officio=true
      speciality.valid_to_datespeciality_officio_valid_to_datespeciality.speciality_officio=true


    • parties

      field_origintable_field_namedescriptionididpositionpositionstatusstatusemployee_typeemployee_typeis_activeis_activeinserted_byinserted_byupdated_byupdated_bystart_datestart_dateend_dateend_datelegal_entity_idlegal_entity_external
      field_origintable_field_namedescription
      ididexternal
      no_tax_idno_tax_idnamenametypetypemountain_groupmountain_group
      addresses.countryresidence_countrytype='RESIDENCE'
      addresses.arearesidence_areatype='RESIDENCE'
      addresses.regionresidence_regiontype='RESIDENCE'
      addresses.settlementresidence_settlementtype='RESIDENCE'
      addresses.settlement_typeresidence_settlement_typetype='RESIDENCE'
      addresses.settlement_idresidence_settlement_idtype='RESIDENCE'
      addresses.street_typeresidence_street_typetype='RESIDENCE'
      addresses.streetresidence_streettype='RESIDENCE'
      addresses.building&addresses.apartmentresidence_buildingtype='RESIDENCE', to_char(addresses.building&', '&addresses.apartment)
      addresses.zipresidence_ziptype='RESIDENCE'
      addresses.countryregistration_countrytype='REGISTRATION'
      addresses.arearegistration_areatype='REGISTRATION'
      addresses.regionregistration_regiontype='REGISTRATION'
      addresses.settlementregistration_settlementtype='REGISTRATION'
      addresses.settlement_typeregistration_settlement_typetype='REGISTRATION'
      addresses.settlement_idregistration_settlement_idtype='REGISTRATION'
      addresses.street_typeregistration_street_typetype='REGISTRATION'
      addresses.streetregistration_streettype='REGISTRATION'
      addresses.building&addresses.apartmentregistration_buildingtype='REGISTRATION', to_char(addresses.building&', '&addresses.apartment)
      addresses.zipregistration_ziptype='REGISTRATION'
      phonesmobile_phonetype='MOBILE'

      phones

      		land_line_phonetype='LAND_LINE'
      emailemailinserted_atinserted_atupdated_atupdated_atlegal_entity_idlegal_entity_idlocationlocationstatusstatusis_activeis_active

      employees


      gendergender
      inserted_byinserted_by
      updated_byupdated_by
      inserted_atinserted_at
      updated_atupdated_at
      educationseducations
      educationseducations_qtyeducations[count] - count items in the array
      qualificationsqualifications
      qualificationsqualifications_qtyqualifications[count] - count items in the array
      specialitiesspecialities
      specialitiesspecialities_qtyspecialities[count] - count items in the array
      science_degreescience_degree


    • party_users- without changes

    • audit_log 

      field_origintable_field_namedescription
      idid
      actor_idactor_id
      resourceresource
      resource_idresource_id
      changesetchangeset
      inserted_atinserted_at 


    • contracts

      field_origintable_field_namedescription
      idid
      start_datestart_date
      end_dateend_date
      statusstatus
      contractor_legal_entity_idcontractor_legal_entity_id
      contractor_owner_idcontractor_owner_id 
      contractor_basecontractor_base
      contractor_payment_details_mfocontractor_payment_details.MFO
      contractor_payment_details_bank_namecontractor_payment_details.bank_name
      contractor_payment_details_payer_accountcontractor_payment_details.payer_account
      contractor_rmsp_amountcontractor_rmsp_amount
      external_contractor_flagexternal_contractor_flag
      external_contractorsexternal_contractorsarray!
      nhs_signer_idnhs_signer_id
      nhs_signer_basenhs_signer_base
      nhs_legal_entity_idnhs_legal_entity_id
      nhs_payment_methodnhs_payment_method
      is_activeis_active
      is_suspendedis_suspended
      issue_cityissue_city
      nhs_contract_pricenhs_contract_price
      contract_numbercontract_number
      contract_request_idcontract_request_id
      status_reasonstatus_reason
      inserted_byinserted_by
      inserted_atinserted_at
      updated_byupdated_by
      updated_atupdated_at
      parent_contract_idparent_contract_id
      id_formid_form
      nhs_signed_datenhs_signed_date


    • contract_divisions - without changes

      field_origintable_field_namedescription
      idid
      division_iddivision_id
      • party

    • contract_id
      • party
    • contract_id
      inserted_
      • at
    • byinserted_
      • at
    • by
      • updated

    • inserted_at
      • updated
    • inserted_at
      • status_reasonstatus_reasonadditional_info.educationseducationsadditional_info.educationsadditional_info.educationseducations_qtyadditional_info.educations[count] - count items in the arrayadditional_info.qualificationsqualificationsadditional_info.qualificationsadditional_info.qualificationsqualifications_qtyadditional_info.qualifications[count] - count items in the arrayadditional_info.specialitiesspecialitiesadditional_info.specialitiesadditional_info.specialitiesspecialities_qtyadditional_info.specialities[count] - count items in the arrayadditional_info.specialities.specialityspeciality_officioadditional_info.specialities.speciality_officio=trueadditional_info.specialities.valid_to_datespeciality_officio_valid_to_dateadditional_info.specialities.speciality_officio=trueadditional_info.science_degreescience_degreeadditional_info.science_degree

      parties

      field_origintable_field_namedescriptionididfirst_namefirst_namesecond_namesecond_namelast_namelast_namebirth_datebirth_dategendergendertax_idtax_iddocuments.numberpassport_numberdocuments.type='PASSPORT'documents.numbernational_id_numberdocuments.type='NATIONAL_ID'documents.numberbirth_certificate_numberdocuments.type='BIRTH_CERTIFICATE'

      documents.number

      temporary_certificate_numberdocuments.type='TEMPORARY_CERTIFICATE'phonesmobile_phonetype='MOBILE'

      phones

      land_line_phonetype='LAND_LINE'

    • updated_byupdated_by 
      updated_atupdated_at


    • contract_employees - without changes

      field_origintable_field_namedescription
      idid
      staff_unitsstaff_units
      declaration_limitdeclaration_limit
      employee_idemployee_id
      division_iddivision_id
      contract_idcontract_id
      inserted_byinserted_by
      updated_atupdated_at
      start_datestart_date
      end_dateend_date
      inserted_atinserted_at
      updated_byupdated_by 


  2. ops_db
    • declarations - without field "seed"
    • declarations_status_hstr - without changes
  3. il_db

    • declaration_requests

      field_origintable_field_namedescription
      idid
      declaration_iddeclaration_id
      authentication_method_current.typeauth_methodauthentication_method_current.type
      authentication_method_current.numberauth_numberauthentication_method_current.{type='OTP'}.number
      statusstatus
      inserted_byinserted_by
      inserted_atinserted_at
      updated_byupdated_by
      updated_atupdated_at

    • employee_requests

      field_origintable_field_namedescription
      idid
      employee_idemployee_id
      statusstatus
      inserted_atinserted_at
      updated_atupdated_at

    • party_users- without changes



    • audit_log contract_requests

      id
      field_origintable_field_namedescription
      ididactor
      contractor_actor_idresourceresourceresourcelegal_entity_idresource_id
      changesetchangesetto_char(changeset)
      inserted_atinserted_at 
  4. ops_db
    • declarations - without changes
    • declarations_status_hstr - without changes
    5. il_db

    declaration_requests

    field_origintable_field_namedescriptionididdeclaration_iddeclaration_idauthentication_method_current.typeauth_methodauthentication_method_current.typeauthentication_method_current.numberauth_numberauthentication_method_current.{type='OTP'}.numberstatusstatusinserted_byinserted_byinserted_atinserted_atupdated_byupdated_byupdated_atupdated_at

    employee_requests

    field_origintable_field_namedescriptionididemployee_idemployee_idstatusstatusinserted_atinserted_atupdated_atupdated_at
  5. black_list_users - without changes 
  6. dictionaries

4. persons

...

death_date

...

    • contractor_legal_entity_id
      contractor_owner_idcontractor_owner_id
      contractor_basecontractor_base
      contractor_payment_details_mfocontractor_payment_details.MFO
      contractor_payment_details_bank_namecontractor_payment_details.bank_name
      contractor_payment_details_payer_accountcontractor_payment_details.payer_account
      contractor_rmsp_amountcontractor_rmsp_amount
      external_contractor_flagexternal_contractor_flag
      start_datestart_date
      end_dateend_date
      nhs_legal_entity_idnhs_legal_entity_id
      nhs_signer_idnhs_signer_id
      nhs_signer_basenhs_signer_base
      issue_cityissue_city
      statusstatus
      status_reasonstatus_reason
      nhs_contract_pricenhs_contract_price
      nhs_payment_methodnhs_payment_method
      contract_numbercontract_number
      contract_idcontract_id
      id_formid_form
      inserted_byinserted_by
      inserted_atinserted_at
      updated_byupdated_by
      updated_atupdated_at
      nhs_signed_datenhs_signed_date
      parent_contract_idparent_contract_id
      miscmisc
      previous_request_idprevious_request_id
      assignee_idassignee_id
      external_contractorsexternal_contractorsjsonb[]
      contractor_employee_divisionscontractor_employee_divisionsjsonb[]
      contractor_divisionscontractor_divisionsjsonb
      contractor_signedcontractor_signed


    • black_list_users - without changes 
    • dictionaries

4. persons

field_origintable_field_namedescription
idid
birth_datebirth_date
birth_countrybirth_country
gendergender
emailemail

death_date

death_date
is_activeis_active
statusstatus
patient_signedpatient_signed
process_disclousure_data_consentprocess_disclousure_data_consent
phonesmobile_phonetype='MOBILE'
phonesland_line_phonetype='LAND_LINE'
authentication_method.typeauth_methodauthentication_method.type
authentication_method.numberauth_numberauthentication_method.{type='OTP'}.number
inserted_byinserted_by
updated_byupdated_by
inserted_atinserted_at
updated_atupdated_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

  1. prm

    • ingridients 

      field_origintable_field_name
      idid
      dosagedosage
      dosage.numerator_unitnumerator_unit

      dosage.numerator_value

      		numerator_value
      dosage.denumerator_unitdenumerator_unit
      dosage.denumerator_valuedenumerator_value
      is_primaryis_primary
      medication_child_idmedication_child_id
      innm_child_idinnm_child_id
      parent_idparent_id
      inserted_atinserted_at
      updated_atupdated_at


    • innms - no changes
    • medical_programs - no changes
    • medication
field_origintable_field_name
idid
namename
typetype
manufacturermanufacturer
manufacturer.namemanufacturer_name
manufacturer.countrymanufacturer_country
code_atccode_atc
is_activeis_active
formform
containercontainer
container.numerator_unitnumerator_unit
container.numerator_valuenumerator_value
container.denumerator_unitdenumerator_unit
container.denumerator_valuedenumerator_value
package_qtypackage_qty
package_min_qtypackage_min_qty
certificatecertificate
certificate_expired_atcertificate_expired_at
inserted_atinserted_at
inserted_byinserted_by
updated_atupdated_at
updated_byupdated_by


  • program_medications
field_origintable_field_name
idid
reimbursementreimbursement
reimbursement.typereimbursement_type
reimbursement.reimbursement_amountreimbursement_amount
is_activeis_active
medication_request_allowedmedication_request_allowed
medication_idmedication_id
medical_program_idmedical_program_id
inserted_atinserted_at
inserted_byinserted_by
updated_atupdated_at
updated_byupdated_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_origintable_field_name
idid
datadata
data.created_atcreated_at
data.dispense_valid_fromdispense_valid_from
data.dispense_valid_todispense_valid_to
data.division_iddivision_id
data.employee_idemployee_id
data.ended_atended_at
data.legal_entity_idlegal_entity_id
data.medical_program_idmedical_program_id
data.medication_idmedication_id
data.medication_qtymedication_qty
data.person_idperson_id
data.started_atstarted_at
request_numberrequest_number
statusstatus
medication_request_idmedication_request_id
inserted_atinserted_at
inserted_byinserted_by
updated_atupdated_at
updated_byupdated_by