Gift Batch Import File Specification

NOTE: This is very much a work in progress and needs to be completed and cleaned up before it can be trusted.

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 Import Batches consist of a batch header record followed by a collection of gift records. All records are CSV types commonly delimited by semi-colons although commas are acceptable.

Batch Header Record fields

Row Type - “B”

Batch Description – [80ch] Required

Bank Account Code – [16ch] Required. Must be valid code. Edit against table ?? (note: for gift in kind, this does not have to be a bank account (i.e., could be any account). I assume that for gift in kind, it does not have to be a bank cost centre either?)

• Pokorra I just checked in Petra 2.x: if it is a "gift in kind", it can be any account from table a_account of the current ledger. perhaps a warning if the account is not active anymore. if it is a normal gift, it is an account that has account property bank account. a_account_property, "BANK ACCOUNT".

Hash Total – [Dec]Total of all gifts in the batch or 0 (0 means don't use hash total). Can this be blank on the input record?

GL Effective Date – Required. What are the requirements here? Does this date have to be within the date range of the current and following accounting periods? If empty, does it default to today's date? Are there any cross-edits with gift date?

• Pokorra gl effective date has to be in the date range of current and open forwarding periods. there should be existing methods to check that. See csharp\ICT\Petra\Server\lib\MFinance\GL\GL.Importing.cs, search for TFinancialYear.IsValidPostingPeriod. The value cannot be empty.

Currency Code – [16ch] Must be a valid currency code from table a_currency: a_currency_code_c

Exchange Rate to Base – [Dec?] Cannot be 0. Any other constraints?

Bank Cost Centre – [24ch] Must be valid cost centre. Determined how?

• Pokorra cost centre is not related to bank. can be any active costcentre, table a_cost_centre, depending on the ledger number.

Gift Type – [16ch] Must be one of {Gifts, Gifts in kind, Other}. (hard-coded options?)

• Pokorra please use the hard coded constants from Shared/lib/MFinance/Constants.cs, GIFT_TYPE_*

Gift Record fields

Note: Gift records have two formats. The normal format is 22 fields. The extended format has greater than 22 fields. Both formats can appear in the same batch. Fields below that are preceded by a ">" are only used in the extended format.

Row Type - “T”

Donor Key – [bigint, Int64]. Must be a valid donor key from partner(?) table, xxx

Donor Short Name – note: This field is required in the gift row but can be empty and is always ignored by the import process.

Method of Giving Code – [16ch] Validate against entries in Method of Giving table, a_method_of_giving: a_method_of_giving_code_c

Method of Payment Code – [16ch] Validate against entries in Method of payment table, a_method_of_payment: a_method_of_payment_code_c

Reference – [20ch]

Receipt Letter Code – [16ch] values sourced from ??. Is <none> is a valid value? Can this be blank?

>Receipt Number – [int]

>First Time Gift – [bool]

>Receipt Printed – [bool]

Recipient Key – [bigint] Must be (active?) partner from partner table, p_partner: p_partner_key_n

Recipient Short Name – ignored by import

>Recipient Ledger Number – [bigint] Optional?. Equates to field ledger number? Maybe only for unit level recipients? Or for allocating to a family who works multiple fields? If entered, must match recipient field ledger number. On the gift screens this is auto generated from recipient.

Gift Amount – [dec]

>Gift Amount International – [dec]

Confidential Gift Flag – [bool]

Motivation Group Code – [16ch] Optional? Validate against table a_motivation_group: a_ledger_number_i, a_motivation_group_code_c. (does value of a_restricted_1 matter?)

Motivation Detail Code– [16ch] Optional? Validate against table a_motivation_detail: a_ledger_number_i, a_motivation_group_code_c, a_motivation_detail_code_c (note: motivation detail already has fields for recipient, receipt, tax deductible, cost centre, etc. Should edit compare these to the corresponding gift fields in some way? Should these be used to populate the gift fields? Also, does the field, a_motivation_status_1, affect whether the motivation code can be used as a valid gift import code?

Cost Centre Code – [24ch]

Gift Comment One – optional? Max length?

Gift Comment One Type – are comment types hard-coded or tabled? Can this be blank if comment is not blank?

Mailing Code

Gift Comment Two

Gift Comment Two Type

Gift Comment Three

Gift Comment Three Type

Tax Deductable

Date Entered – Optional.