Cosultant for Database Development - Iran
TERMS OF REFERENCE
Database Development Project
Period: August and September 2017
The Norwegian Refugee Council (NRC) is a non-governmental humanitarian organization with 60 years of experience in helping create a safer and more dignified life for refugees and internally displaced people. NRC advocates for the rights of displaced populations and offers assistance within the shelter, education, food security and water, sanitation and hygiene sectors. We aim to provide high quality and innovative technical solutions to daily challenges of life in displacement that also offer protection.
Through our programme in Iran, we help Afghan refugees cope with the struggles of living in long-term displacement. NRC’s programmes support Afghan refugees as they seek to improve their living conditions, prevent secondary displacement, help to guarantee their access to basic services, and reinforce their coping strategies. We support UNHCR’s Regional Solutions Strategy for Afghan Refugees (SSAR) and advocate for better coordination in the humanitarian response. NRC has offices located in Tehran and Kerman, and undertakes further activities in the provinces of Semnan, Qom and Alborz.
A growing part of NRC’s activities in Iran involves distributing multi-purpose cash assistance, in the form of Saman Bank cards, to Afghan refugees. NRC loads each bon card with a specific amount of money relevant to the needs of the Afghan refugee and his/her household. With these bon cards, the refugees determine how to spend the cash according to their household priorities.
OVERVIEW OF NRC DATABASES
Cash management database. NRC Iran has developed a cash management database, based on SQL Server 2012 R2. NRC staff interview Afghan refugees using a questionnaire, and enter the data directly into the database through an ASP.net data entry function. The database performs some calculations on the information provided by the refugee to determine how much cash NRC will transfer. The database then tracks the status of each card, e.g., whether the refugee information has been sent to the bank, whether the bank has provided a card to NRC, whether the card has been successfully delivered to the Afghan refugee, and on what date, etc. The database has an internal search function that checks whether a new refugee has previously received cash card. As of July 2017, around 1,500 Afghan refugees are registered in NRC’s cash management database.
Information and counselling database. NRC’s Information and Counselling Unit has a second SQL database of around 500 beneficiaries. This database is a ‘case management’ system, i.e., the status of each refugee can be changed as the refugee’s status changes, and staff members can add case notes. As in the cash database, beneficiaries are identified uniquely by their Amayesh (refugee) card number. Information and Counselling staff add around 5 new cases each week to this database. Currently, there are no links between the cash and Information and Counselling databases.
Accountability database. NRC would like to develop an additional database functionality in order to log and track all incoming queries from Afghan refugees. This would resemble a ‘ticketing’ system that reminds the NRC staff after a set period to address the query and close it. The system should allow referrals to different business units, e.g., if the question relates to education, the user should be able to identify the query as relevant to the education unit. In addition, users must be able to add case notes to give details of the nature of the calls. This database should be able to interface with the cash and Information and Counselling databases so the user can understand whether an Afghan refugee who is calling NRC is already receiving an NRC service, as this will change the response NRC gives. Overall, this database will help NRC staff to be accountable to beneficiaries, and ensure that NRC deals with all queries in a professional and timely manner.
OBJECTIVES AND KEY ACTIVITES
There are two key objectives related to this consultancy:
Improving the cash management database. Due to changes in NRC’s cash programming, the cash management database needs fundamentally restructuring and improving. Prior to July 2017, NRC gave ‘one use’ cash cards (Saman Bank gift cards) to Afghan refugees, funded by one donor. These cards could not be ‘topped up’ with additional funds. As of July 2017, new government regulations mean that any programme unit at NRC is able to issue a ‘bon’ card (like a debit card), and any other unit can ‘top up’ the card at any time in the future. In addition, the number of donors has increased, and for each donor, a different questionnaire may be used to determine eligibility or transfer value. For example, the Information and Counselling team may issue a bon card to an Afghan family for $400 one month, and three months later, the hygiene team may decide to ‘top up’ the bon card with an additional $50. The $400 may be from one donor, calculated with one questionnaire, while the $50 top up may be from a different donor, using another questionnaire. All these changes mean the database needs a thorough restructuring to support NRC’s cash programming in the years ahead.
In addition to its existing functionality, the Consultant is required to develop the database thus:
- Card type. To ensure the cash database can accept either gift cards or bon cards;
- Top ups at any time. To ensure that, for bon cards, the cash database can register which amounts were distributed or ‘topped up’ by which business unit and at which time;
- Multiple donors. To ensure that the cash database registers which donors(s) funded which card; one card may be funded by multiple donors;
- Multiple questionnaires. To ensure that the cash database can handle multiple data entry questionnaires, depending on donor;
- Multiple calculations. To ensure that the cash database can perform different eligibility and transfer value calculations, depending on donor;
- Calculation flexibility. To ensure that the thresholds/values in the eligibility and transfer value calculations can be changed. For example, the current calculation transfers a different cash value to a family with less than 5 members than a family with more than 5 members. The new database should have flexibility to change that number if required.
- Search functionality. To ensure that the cash database has a search functionality, so that when a new Afghan is approached for interview, the user can tell whether the Afghan has been interviewed previously. This will involve searching on multiple fields such as Amayesh number, phone number, family name, etc;
- View records and cash history functionality. To ensure that users can view records (completed questionnaires) and also complete cash history (a log of all cash transfers, dates, donors, amounts, etc.);
- Edit record functionality. To ensure that some users (depending on access rights) can edit records;
- Import card number functionality. To speed up the process of entering card numbers after Saman Bank has sent them to NRC, the database user should be able to upload an excel file and the system automatically populates the card number field, at minimum for Amayesh card holders.
- Reporting functionality. To ensure that the cash database has a simple reporting functionality that enables users to create their own standard reports showing e.g., # bon and gift cards distributed per donor per province per month;
- Historical data. To ensure that ‘historical data’ (the existing 1,500 Afghan refugees in the current database) are transferred into the new database;
- Secure data. To ensure that the data in the cash database is secured from external attack, and users have the appropriate user rights;
Online functionality. To ensure that the system is accessible online to unlimited users.
Developing accountability case management/ticket functionality. The accountability tracking database could be a stand-alone database, or integrated within either the cash or Information and Counselling databases. This is for the Consultant to determine and recommend a good way forward.
The Consultant is required to develop the following functionality:
- Case open. User must be able to open a case, with a simple interface, with a selection of the following fields: refugee name and family name, gender, age, province, address, mobile phone number 1, mobile phone number 2, Family Amayesh card number, Individual Amayesh card number;
- Search functionality. Based on the fields entered, the user should be able to easily search existing cash or Information and Counselling databases to identify if the user is an existing NRC beneficiary;
- Existing beneficiary flag. User must be able to flag if the user is an existing NRC beneficiary and identify for what service (Information and Counselling, IPA, FSL, …);
- Case type. User must be able to select a type of case from a drop-down list, e.g., general query, specific query, complaint level 1 (least serious), complaint level 2, complaint level 3 (most serious). Ideally the list is editable by NRC ICT staff;
- Case notes. User must be able to type in notes related to the case. The case notes should allow multiple notes to be added at different times, e.g., if the user is called back several times and new notes are added;
- Optional referrals. User must be able to register a referral to a pre-defined colleague(s), ideally providing template text for manually pasting into NRC’s Outlook system, containing sufficient case information for the colleague to make a follow-up. The system should log the referral and set a reminder for the user to follow-up with the referee to check the status. The reminder will change depending on the type of case. E.g. complaint level 3 = the most serious = the refugee should have had a call back within 24 hours so the reminder should be within 24 hours. If the case is open more than 24 hours for a complaint level 3, then the system should flag the status ‘case open overdue’;
- Case status. Case must be switchable between case open, case open overdue, case closed resolved, case closed unresolved;
- Reporting. Case files should be printable (pdf/word). A status summary table should be available e.g., number of cases open/closed/overdue etc. by month, year, and since the project start. The summary table should be exportable into word/excel, and printable;
- Language. English is preferred;
- Secure data. To ensure that the data in the cash database is secured from external attack, and users have the appropriate user rights;
Online functionality. To ensure that the system is accessible online to one user.
TIMELINE AND DELIVERABLES
The Consultancy will start in August and is estimated to last two months. Improving the cash database is the first priority, and adding accountability functionality is the second priority. The Consultant must deliver the following documents after 2 weeks of starting the project:
- A detailed design document, explaining clearly the steps that the Consultant will take in order to improve the cash database, and including a schematic of the new cash database, showing in detail the structure of the new tables, and the relationships between them. This detailed design document will also include the accountability functionality and describe how this system will interface with the cash and Information and Counselling databases. NRC’s database expert must approve the detailed design document before any development work can start;
- User manuals must be developed in English for the cash database and accountability functionality;
- The source code must be delivered to the NRC ICT Officer upon project completion.
As part of the project, the Consultant must deliver a one-day end-user training for the cash database, and a one-day end-user training for the accountability database.
As part of the project, the Consultant agrees to fix any bugs identified on the system for a period of 6 months after the project closure, at no cost to NRC. This responsibility does not extend to administering the system.
ACCESS TO EXISTING DATABASES
As part of the project, the Consultant will have full access to NRC Iran’s existing databases and will be able to spend time with the developer and users of those databases, who can provide full details on how the current databases are structured and what the user requirements are.
- 5 years of experience working with SQL server, ASP.net, etc., and reporting softwares;
- Fluent spoken and written English and Farsi;
- Experience working in humanitarian contexts is a plus.
BUDGET AND PAYMENT TERMS
Bids shall include budgets in USD that are based on the terms of reference above. The sum shall include all fees and costs (including international flights, per diems, hotels, transportation around Tehran, taxes, and insurance) incurred in conducting the consultancy tasks. The Consultant is responsible for paying any social security insurance or taxes required by the Iranian Government for work performed in Iran. NRC commits to providing desk space for the Consultant to work in its office in Tehran.
The lump sum will be paid in three instalments:
- 20% upon acceptance by NRC of the detailed design document;
- 50% upon acceptance of the cash database, source code, user manuals, and training;
- 30% upon acceptance of the accountability database, source code, user manuals, and training.
- APPLICATION PROCESS AND REQUIREMENTS
- TIMELINE AND DELIVERABLES
This bid is open to individual consultants, groups of individual consultants, or consulting firms.
Bids must include the following:
- Cover letter to be submitted in webcruiter which stating the Consultant’s skills, experiences and references to show suitability for the consultancy (max 2 pages; letters over this limit will invalidate the bid);
- CV of proposed Consultant(s) to be submitted in webcruiter which showing clearly similar assignments and three references (max 2 pages; CVs over this limit will invalidate the bid).
Submit completed bids in hard copy to the NRC Iran Logistics Department or soft copies to email@example.com by close of day on 26 July 2017.
Address: NRC, Building 26, Narenjestan 6, off Pasdaran, Tehran, Iran. +98 2122046143
- Organization: NRC - Norwegian Refugee Council
- Location: Tehrān
- Grade: Level not specified
- Closing Date: 2017-07-26