Excel File
- Katerina
- Vladimir Bedrosov
- Rishal Sait
Preconditions
1. Create excel file
Create excel file in the following format: ExcelFileFormat.
Data Description
The table below presents data required for members' XML file generation using Excel files. The software expects all below fields to be available in the Excel file generated by the payer system. Note that the sequence of columns in an Excel file does not matter, the application only checks the column names.
Column Name | Description | Mandatory |
|---|---|---|
| SenderID | Payer the member belongs to. | Yes |
| PersonUnifiedNumber | Unique Number to uniquely identify the resident. This number is issued by the Ministry of Interior (MOI). | Yes |
| PersonPassportNumber | Passport number, or if not available, the National ID. This field is optional. | No |
| PersonNationalityCode | Person Nationality Code. | No |
| PersonNationality | Member nationality. A list of allowed nationalities is published on Shafafiya. | Yes |
| PersonMiddleNameEn | Person Middle Name in English. This field is optional. | No |
| PersonMiddleNameAr | Person Middle Name in Arabic. This field is optional. | No |
| PersonLastNameEn | Person Last Name in English. This field is optional. | No |
| PersonLastNameAr | Person Last Name in Arabic. This field is optional. | No |
| PersonIsResident | Flag if the person is the UAE resident. | Yes |
| PersonGender | Member's gender: 1 for male and 0 for female. | Yes |
| PersonFirstNameEn | Person First Name in English. This field is optional. | No |
| PersonFirstNameAr | Person First Name in Arabic. This field is optional. | No |
| PersonFirstName | Member's first name. This field is optional. | No |
| PersonEmiratesIDNumber | Emirates ID Number of the member. If this number is unavailable, the following default values are accepted:
GA will use the following logic if this field is blank in the data source provided by the payer:
| Yes |
| PersonContactNumber | Telephone contact number provided by the member. This field is optional. | No |
| PersonCityCode | Code of Residence City for the person. | No |
| PersonCity | Member’s actual city of residence. | Yes |
| PersonBirthDate | Member's birth date. The expected formats are explained in section Supported Date Formats. | Yes |
| MemberRelationTo | Member ID of the principal member of the family. | Yes |
| MemberRelation | Member's family relationship. The allowed values are: 1 – means Principal 2 – means Spouse 3 – means Child 4 – means Parent 5 – means Other | Yes |
| MemberID | Member insurance card number. | Yes |
| ContractVATPercent | Value Added Tax percent for the collected premium. Expected values are between 0 to 100. | Yes |
| ContractVAT | Total Value Added Tax amount appropriated for the collected premium. | Yes |
| ContractTPAID | HAAD license number of TPA that administers member's contact. | Yes |
| ContractStartDate | Contract start date. GA expects date values in one of the following formats:
| Yes |
| ContractRenewalDate | Contract latest renewal date. GA expects date values in one of the following formats:
| Yes |
| ContractPolicyHolder | Contract policyholder that may have one of the following values: 1 — means Government, 2 — means Government related services, 3 — means Other, 4 — means Private companies with less than 1000 employees, 5 — means Private companies with 1000 employees or more. | Yes |
| ContractPayerID | Contract's payer HAAD license. | Yes |
| ContractPackageName | Name of the insurance package. | Yes |
| ContractGrossPremium | Annual premium for the contract. | Yes |
| ContractExpiryDate | Date when the contract expires. The expected formats are explained in section Supported Date Formats. | Yes |
| ContractCompanyID | Trade license of the member's company. | Yes |
| ContractCollectedPremium | AED amount received by the Payer as a premium for the term of the Member’s insurance contract. | Yes |
Configuration
The following settings are required to be made for Person.Register transaction generation from 'Excel':
1. Go to Greenrain Automation Settings
Log in to Greenrain Automation. Select "Settings" from the Toolbar → 'Greenrain Automation Settings' form opens.
Figure1.
2. Configure Person.Register tab
Figure2.
Select "Person.Register" tab and configure settings:
- 'Generate and upload Person.Register transactions to Shafafiya': should be checked.
- 'Processing interval' drop-down fills automatically with custom values. User can select an interval from the list, '30 minutes' for example.
- 'Choose where Greenrain Automation will get person register data from':
- 'Excel' value should be selected from drop-down list.
- 'Select folder where to expect Excel files'. Specify the directory from which you want to retrieve data (excel file, see Preconditions p.1).
- Click "Save" button.
3. Check common configuration
Generate PersonRegister transaction
1. Go to Greenrain Automation Services
Select "Services" from the Toolbar → 'Greenrain Automation Services' form opens (refer to Figure1.).
2. Start the service
It allows to start a service after certain runtime configured on the Person Resister tab (refer to Configuration → 2. Configure Person.Register tab).
- Click "Start the service" link:
- 'Download Service Status' will be changed to RUNNING (marked with green).
- 'Stop the service' link will be displayed under the status.
- Click "Close" button.
Figure3.
"Run" link in the Toolbar allows a user to run the service at any time on demand (refer to Figure1.).
3. Check PersonRegister transaction
- check e-mail notification.
- check Greenrain Automation database:
- PersonRegister table: 1 record is created;
- Transactions table: 1 record is created.
- check PersonRegister transaction in Greenrain Messenger:
- Log in to Greenrain Messanger (refer to common configuration → 3. Configure Post Office Settings).
- Select "Download" tab.
- Set filters and click "Search" button → the uploaded Person.Register transaction has been found:
- set "Transaction date"
- set "Person.Register" checked
- set "Sent only" and "New only"
Figure4.