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

prm_db
- legal_entities
- divisions
- employees
- parties
- party_users
- black_list_users
- audit_log
ops_db
- declarations
- declarations_status_hstr
il_db
- declaration_requests
- employee_requests
- dictionaries
mpi_db
- persons

Mapping:

...

legal_entities

...

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

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'
addresses.country	residence_country	type='RESIDENCE'
addresses.area	residence_area	type='RESIDENCE'
addresses.region	residence_region	type='RESIDENCE'
addresses.settlementzip	residenceregistration_settlementzip	type='RESIDENCEREGISTRATION'	addresses.settlement_type
residence_settlement_typephones	mobile_phone	type='RESIDENCEMOBILE'
addresses.settlement_id	residence_settlement_id phones	land_line_phone	type='RESIDENCELAND_LINE'
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	emailemail	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

inserted_atinserted_atupdated_atupdated_atcapitation_contract_idcapitation_contract_idcreated_by_mis_client_idcreated_by_mis_client_idmis_verifiedmis_verifiednhs_verifiednhs_verified

divisions


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_origintable_field_namedescriptionididpositionpositionstatusstatusemployee_typeemployee_typeis_activeis_activeinserted_byinserted_byexternal

field_origin	table_field_name	description
id	idexternal
no_tax_id	no_tax_id
namegendername	gender	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

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

start

updated_

date

at

start

updated_

date

at

end

parent_

dateend

contract_

date

id

legal

parent_

entity

contract_id

legal

id_

entity

form id_

id

form

division

nhs_signed_

id

date

division

nhs_signed_

id

date

party_id

inserted_at

updated_at

status_reason

additional_info.educations

educations

additional_info.educations

educations_qty

additional_info.educations[count] - count items in the array

additional_info.qualifications

qualifications

additional_info.qualifications

qualifications_qty

additional_info.qualifications[count] - count items in the array

additional_info.specialities

specialities

additional_info.specialities

specialities_qty

additional_info.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

parties

field_origin

table_field_name

description

id

first_name

second_name

last_name

birth_date

gender

tax_id

documents.number

passport_number

documents.type='PASSPORT'

documents.number

national_id_number

documents.type='NATIONAL_ID'

documents.number

birth_certificate_number

documents.type='BIRTH_CERTIFICATE'

documents.number

temporary_certificate_number

documents.type='TEMPORARY_CERTIFICATE'

phones

mobile_phone

type='MOBILE'

phones

land_line_phone

type='LAND_LINE'

inserted_by

updated_by

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
party_users- without changes

audit_log

contract_requests

field_origin table_field_name description
id idactor
contractor_legal_entity_id actor_id resource resource resource_id resourcecontractor_legal_entity_id changeset changeset
inserted_at inserted_at

ops_db
- declarations - without field "seed"
- declarations_status_hstr - without changes

il_db

declaration_requests

field_origin

table_field_name

description

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

inserted_by

inserted_at

updated_by

updated_at

employee_requests

field_origin

table_field_name

description

id

employee_id

status

inserted_at

updated_at

black_list_users - without changes
dictionaries

4. persons

...

death_date

...

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

Versions Compared

Old Version 13

New Version Current

Key

Purpose

Fraud DB structure

Table for replication

Purpose

Fraud DB structure

Table for capitation

Tables for reimbursement

field_origin	table_field_name	description
id	id
actor_id	actor_id
resource	resource
resource_id	resource_id
changeset	changeset
inserted_at	inserted_at

field_origin	table_field_name	description
id	idactor
contractor_legal_entity_id	actor_id	resource	resource	resource_id	resourcecontractor_legal_entity_id	changeset	changeset
inserted_at	inserted_at

Page Comparison

Versions Compared

Old Version 13

New Version Current

Key

Purpose

Fraud DB structure

Table for replication

Purpose

Fraud DB structure

Table for capitation

Tables for reimbursement