| Table of Contents |
|---|
...
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:
...
Fraud DB structure
To simplify report creation and data analysis current tables should be denormalized and aggregated.
...
- 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 additional_info.educations educations additional_info.educations additional_info.educations educations_qty additional_info.educations[count] - count items in the array additional_info.qualifications qualifications additional_info.qualifications additional_info.qualifications qualifications_qty additional_info.qualifications[count] - count items in the array additional_info.specialities specialities additional_info.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 id first_name first_name second_name second_name last_name last_name birth_date birth_date gender gender tax_id 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 inserted_by updated_by updated_by inserted_at inserted_at updated_at updated_at 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
- 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 - black_list_users - without changes
- dictionaries
...