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 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
   • audit_log

Mapping:

...

  legal_entities

...

divisions

...

phones

...

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_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_

   at

   • by

     updated_

   atlegal_entity_idlegal_entity_idlocationlocationstatusstatusis_activeis_activeemployees

   • by

     
     

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

   • declaration_requests

     statusstatusemployee_typeemployee_typeis_activeis_activeinserted_byinserted_byupdated_byupdated_bystart_datestart_dateend_dateend_datelegal_entity_idlegal_entity_iddivision_iddivision_idparty_idparty_id

     field_origin	table_field_name	description
     id	id

     positionposition

     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

     status

     field_

     reason

     origin

     status_reasonspecialityspeciality_officiospeciality.speciality_officio=truespeciality.valid_to_datespeciality_officio_valid_to_datespeciality.speciality_officio=true
     parties

     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

   no

   • contractor_legal_

   tax

   • entity_id

   no

   • contractor_legal_

   tax
   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'}.numberstatusstatus

   • entity_id

   gendergenderinserted_byinserted_byupdated_byupdated_byinserted_atinserted_atupdated_atupdated_ateducationseducationseducationseducations_qtyeducations[count] - count items in the arrayqualificationsqualificationsqualificationsqualifications_qtyqualifications[count] - count items in the arrayspecialitiesspecialitiesspecialitiesspecialities_qtyspecialities[count] - count items in the arrayscience_degreescience_degree

   • party_users- without changes

   audit_log 

   field_origintable_field_namedescriptionididactor_idactor_idresourceresourceresource_idresource_idchangesetchangesetinserted_atinserted_at 
4. ops_db
   
   • declarations - without field "seed"
   • declarations_status_hstr - without changes
5. • 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

   employee_requests

   field_origintable_field_namedescriptionididemployee_idemployee_idstatusstatusinserted_atinserted_atupdated_atupdated_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

     
     

   • black_list_users - without changes 
   • dictionaries

...

• 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_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

• program_medications

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

...

• medication_request_request