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:

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

2. ops_db
   
   • declarations - without field "seed"
   • declarations_status_hstr - without changes
3. 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

   • black_list_users - without changes 
   • dictionaries

4. persons