Gift Batch Import File Specification

Description of Current Implementation in Open Petra (October 2014)

The purpose of this entry is to define the Gift Batch file layout and the way in which the data is validated.

Gift batch import records must be in CSV format, i.e. variable length fields separated by a delimiter (commonly a semi-colon, although commas are acceptable) with text fields enclosed in quotes. A position in the record must be left for each field, even if the field is empty.

The file is parsed line by line and, provided there are no errors, the batches are saved to the database. However, if there are critical errors no data is saved. The whole file content is rejected. But an attempt is made to read the file in its entirety so that a complete list of errors is built up and can be reported to the user. If more than 100 messages are found then the read is aborted because this is usually symptomatic of the same error(s) being duplicated many times over so there is little point in continuing. The goal is that the user should present the file to Open Petra and get a list of a few mistakes, which can all be corrected so that at the second attempt the file is parsed correctly.

Note that multiple mistakes on a single line are all reported.

Gift import batches consist of a batch header record followed by a collection of gift records.

Validation is performed at five levels: - Parsing. Each token is parsed as one of string, boolean, Int64, Int32 or Date. If the text in the file does not convert to the expected data type a parsing error is generated and a 'default' value is assumed so that it is possible to continue reading the file. For numbers a default value of -1 will be assumed, which is typically an invalid value, but this is preferable to using a default that might be valid. For decimals a value of 1.00 is assumed and for a date: 'Today'. - Standard Open Petra Validation. This is the validation that checks that critical strings are not empty, that strings do not exceed the allowable character count and so on. - Additional Open Petra manual validation. The finance modules have additional validation that followsd the standard Open Petra Validation Framework and is used to check the 'business logic'.

Batch Header Record

Row Type - “B”

Batch Description – [80ch. maximum] Required. Gift batch description

Gift Record

Row Type - “T”

Earlier Specification Prepared by Dwmosman in 2013

This specification has been retained so that it can be compared to the current implementation.

NOTE: This specification is a work in progress and has not yet been implemented. - Dwmosman 18:32, 27 February 2013 (UTC)

The purpose of this entry is to define the Gift Batch file layout and its validation requirements. The validation process should follow the OpenPetra validation framework.

Gift batch import records must be in CSV format. I.e., variable length fields separated by a delimiter (commonly a semi-colon, although commas are acceptable) with text fields enclosed in quotes. A position in the record must be left for each field, even if the field is empty.

Gift import batches consist of a batch header record followed by a collection of gift records.

Batch Header Record

Row Type - “B”

Batch Description – [80ch. maximum] Required. Gift batch description

Validation Rules: Cannot be empty field. Length must be 1-80 characters.

Bank Account Code – [16ch. maximum] Required. The bank account code for the batch. For a normal gift this should be an active bank account code. For "Gift in Kind" or "Other", it can be any active posting account.

Validation Rules: Cannot be empty. Must be an existing account in table a_account for the active ledger. Must be a posting account (a_posting_status_1). If gift type(a_gift_type_c) = MFinanceConstants.GIFT_TYPE_GIFT, then must be a bank account (a_account_property = "BANK ACCOUNT"). Generate a warning if the account is not an active account.

Hash Total – [decimal] Optional. If blank or 0, then this hash total will be ignored. If non-zero (plus or minus), then this will be compared against the sum of all gifts in the batch.

Validation Rules: If non-zero, then the hash total must equal the sum of the gift amounts from the batch.

GL Effective Date – [date] Required. The date must be within the date range of the current and open forward periods.

Validation Rules: Must be a valid date in the date format specified for the batch. Validate against period dates. Search for TFinancialYear.IsValidPostingPeriod. For an example, see csharp\ICT\Petra\Server\lib\MFinance\GL\GL.Importing.cs.

Currency Code – [16ch.] Must be a valid currency code from the OpenPetra currency table. If the account specified above is a foreign currency account, then this entry must match the currency of the account.

Validation Rules: The value must exist in table a_currency (a_currency_code_c). If the bank account specified above is a foreign currency account (a_foreign_currency_flag_1), then this field must match the currency of the account (a_foreign_currency_code_c).

Exchange Rate to Base – [decimal] Cannot be 0. Enter the exchange rate at the time the deposit was made. If the gift currency is the same as the base ledger, then exchange rate must be 1.

Validation Rules: Cannot be 0. If currency code above equals the currency of the active ledger, then exchange rate must equal 1.

Bank Cost Centre – [24ch.] The cost centre which is applied to the bank. Must be an active local posting cost centre for the ledger.

Validation Rules: Must be an existing entry in the cost centre table (a_cost_centre) for the active ledger with a_cost_centre_type_c = "Local" and a_posting_cost_centre_flag_1 = true. The user should be allowed to specify in the import dialog box that, in case of failure, a leading 0 should be added to the account (eg. "0300" instead of "300") and the lookup re-attempted.

Gift Type – [16ch.] Must be a valid gift type, "Gift", "Gift In Kind" or "Other".

Validation Rules: If blank, populate the field from MFinanceConstants.GIFT_TYPE_GIFT. If non-blank, must equal one of GIFT_TYPE_GIFT, GIFT_TYPE_GIFT_IN_KIND or GIFT_TYPE_OTHER (use the hard coded constants from Shared/lib/MFinance/Constants.cs, GIFT_TYPE_*)

Gift Record

Row Type - “T”

Donor Key – [Int64] The partner key of the donor. The donor must be entered as a partner before the gift batch is imported. Note that if the donors partner record has been merged into another partner, then the new partner key must be specified instead of the key of the merged record.

Validation Rules: Must be a valid key into the partner table, p_partner. However, if the partner status is "MERGED", then the new partner must have been used so the import will fail (ideally the new partner key that should have been used will be included in the error message).

Donor Short Name – This field is included for compatibility with the Gift Batch Export. It is not used on import.

Method of Giving Code – [16ch.] Select one of the options defined in the Method of Giving table for your installation.

Validation Rules: Must be an existing "a_method_of_giving_code_c" code in the Method of Giving table, a_method_of_giving.

Method of Payment Code – [16ch.] Select one of the options defined in the Method of Payment table for your installation.

Validation Rules: Must be an existing "a_method_of_payment_code_c" code in the Method of Payment table, a_method_of_payment.

Reference – [20ch.] Reference number/code for the gift transaction.

Validation Rules: Cannot be longer than 20 characters.

Receipt Letter Code – [16ch.] Optional. "<none>" is the same as blank. Otherwise select one of the values from your installations Form Letter Design table.

Validation Rules: If empty or "<none>", populate the field with "<none>". Otherwise, the entry must be an existing code (p_design_name_c) in the table p_form_letter_design.

Recipient Key – [Int64] Select the key of the recipient from the partner table.

Validation Rules: Must be a valid key (p_partner_key_n) from the partner table, p_partner. Class (p_partner_key_n) must be "FAMILY" or "UNIT". If Family, tnen special type (p_partner_type) must be "WORKER". Partner status must not be "MERGED".

Recipient Short Name – This field is included for compatibility with the Gift Batch Export. It is not used on import.

Gift Amount – [decimal] Enter the gift amount.

Validation Rules: Must be a valid non-zero decimal number. Can be positive or negative.

Confidential Gift Flag – [boolean] Optional. "yes" or "no". Enter yes to indicate a confidential gift. Defaults to "no".

Validation Rules: Must be "yes", "no" or blank. If blank, default to false.

Motivation Group Code – [16ch.] Select an entry from the installation's Motivation Group table for the active ledger.

Validation Rules: Must be a valid code (a_motivation_group_code_c) from the Motivation Group table, a_motivation_group, for the active ledger (a_ledger_number_i).

Motivation Detail Code– [16ch.] Select an entry from the installations Motivation Detail table for the selected Motivation Group Code for the active ledger

Validation Rules: Must be a valid entry in the Motivation Detail table, (a_motivation_detail: a_motivation_detail_code_c, a_ledger_number_i = active ledger, a_motivation_group_code_c = motivation group code) and with status = true (a_motivation_status_1). The motivation detail recipient key (p_recipient_key_n) must match the recipient key on the import record if they are both non-zero.

Cost Centre Code – This field is included for compatibility with the Gift Batch Export. It is not used on import.

Gift Comment One – [160ch.] Optional. Long description of the gift.

Validation Rules - Cannot be more than 160 characters in length. No need to check for SQL Injection if the SubmitChanges DataAccess Methods are used as these always use Parameterized SQL queries. With that kind of SQL queries the Gift Comment One will be a Parameter of Type string to the SQL query and the RDBMS will never execute Query Parameters as SQL, hence SQL Injection cannot happen.

Gift Comment One Type – Optional. If used, must contain a valid Gift Comment Type. Cannot be blank if Gift Comment One is used.

Validation Rules: - blank (cannot be blank if Gift Comment One is not blank) or a valid GIFT_COMMENT_TYPE. See Shared/lib/MFinance/Constants.cs, GIFT_COMMENT_TYPE*. (Note: that Constant.cs file is actually missing more constants: Donor, Recipient, Office)

Mailing Code – [16ch.] Optional. "<none>" is the same as blank. Otherwise select one of the values from your installations Mailing Code table.

Validation Rules: If empty or "<none>", populate the field with "<none>". Otherwise, the entry must be an existing code (p_mailing_code_c) in the table p_mailing.

Gift Comment Two - See Gift Comment One

Gift Comment Two Type - See Gift Comment One Type

Gift Comment Three - See Gift Comment One

Gift Comment Three Type - See Gift Comment One type

Tax Deductable [boolean] Optional. if blank, it will be filled from a_tax_deductible_flag_1 from the Motivation Detail.

Validation Rules - Must be blank, "yes" or "no". If blank, then the field will be populated from the a_tax_deductible_flag_1 field from the selected Motivation Detail.

Date Entered – [date] Optional. If blank, the gift batch effective date is used.

Validation Rules: Must be blank or a valid date in the specified import format. If blank, then the field is populated from the batch effective date.