Gift Batch Import File Specification

From OpenPetra Wiki
Jump to navigation Jump to search

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. 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.
  • Dwmosman 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".
  • Robertpickett The gift type can also be "Other" and I think that also allows any account, the same as for "Gift in Kind". In all cases only Posting Accounts should be allowed (a_posting_status_l).

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: Validate against period dates. Search for TFinancialYear.IsValidPostingPeriod. See GL.Importing.cs for example.
  • DwmosmanWhat 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 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).
  • Robertpickett If the Account is a foreign currency account (a_foreign_currency_flag_l) then the currency 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.
  • Dwmosman Cannot be 0. Any other constraints?
  • Robertpickett If the currency code matches the base currency of the ledger then the exchange rate must be 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.
  • Dwmosman 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.
  • Robertpickett The Cost Centre must also have a_cost_centre_type_c = "Local" and must be a Posting Cost Centre (a_posting_cost_centre_flag_l). In Petra, if it can't find the Cost Centre it trys again by adding a leading 0 (because of Excel's tendency to remove leading zero's when you don't want it to. If it then finds a match it asks the user if they want to use that CC instead (eg. "0300" instead of "300")
  • Dwmosman Rob, are there alternatives to this approach? Interactive batch loads across the client/server boundary can be tricky (for example, what if the requestor fails to notice the question dialog and the table is locked for all users for the day?). I will send an email on this

Gift Type – [16ch.] Optional. Must be "Gift", "Gift In Kind" or "Other". If left blank, it defaults to "Gift".

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.
  • Dwmosman 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_*
  • Dwmosman Could these constants be enumerated or a collection or something? Or are they so static that there will always be these three and I can just hard-code them into my validation routine?
  • Dwmosman In the OpenPetra database design it says that this field defaults to "gift". Is this acceptable? Or must the importer always put a value in this field?

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.

  • Pokorra I think that one file will either be in the shorter, or in the extended format, not mixed. I see, the import code checks for every transaction. that is fine, but not required.
  • Robertpickett In Petra the extended format is export only (provided to give a convenient csv file for manipulation in Excel), the import doesn't support it. I'm not sure whether it makes sense to import the long format since a number of the fields are calculated fields.

Row Type - “T”

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

  • Pokorra table p_partner, no special requirement. usually it is a partner of p_partner_class_c = "FAMILY", but I think that is not a requirement??? Rob?
  • Robertpickett The donor can be of any Partner Class. The one complication relates to Merged Partners. If the Partner Status is "MERGED" then the import should look up the Partner that it was merged into (maybe there is already a method for that in OpenPetra). If it can't find that Partner then import should fail, otherwise ask the user if they want to use that Partner instead of the one they entered (if they say no then import fails), otherwise use the new Partner.

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?

  • Pokorra I think it will be blank most of the time, because it will only be filled when the gift batch is posted, and a receipt is printed.
  • Robertpickett <none> is valid, as is blank. In Petra, if blank then it gets imported as <none>. Otherwise must exist in p_form_letter_design (field p_design_name_c)

>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

  • Pokorra it should actually be a FAMILY partner (p_partner_class_c) that has special type WORKER (p_partner_type), or a UNIT partner. not sure if that should all be validated during the import, because it is quite extensive. we have methods for that during posting of a gift batch.
  • Robertpickett Should also not allow Partners with status "MERGED".

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.

  • Pokorra it is important for a worker as well, because the money will go to the field where he is working. but the gift screen generates it, and during gift posting it is calculated again, so it is not so important to be validated, I think.
  • Robertpickett The Petra import works this out based on the recipient, but I guess the best method for that still has to be worked out for OpenPetra.

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_l matter?)

  • Pokorra we usually always require a valid motivation group and detail. a_restricted_l is just for read access permissions, who is allowed to see that gift. should not take effect here.

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_l, affect whether the motivation code can be used as a valid gift import code?

  • Pokorra I think you do not need to copy values from motivation detail record.
  • Robertpickett Only Motivation Details with a_motivation_status_l = true should be allowed. There is some code needed here to populate information like Cost Centre, Account, etc which does need to be done but is not a straightforward matter of copying values (it depends on other factors as well). I imagine the code to do that has already been written for the Gift Batch screen. If p_recipient_key_n on a_motivation_detail is populated and doesn't match the recipient key in the import file (and the recipient key is not 0) then the import should fail with an error.

Cost Centre Code – [24ch]

  • Robertpickett I don't think this should be here? Is it part of the extended format?

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

  • Robertpickett Same rules as for Receipt Letter Code but using p_mailing (p_mailing_code_c) for the lookup

Gift Comment Two

Gift Comment Two Type

Gift Comment Three

Gift Comment Three Type

Tax Deductable

  • Robertpickett Boolean. If blank then will be filled from a_tax_deductible_flag_l on a_motivation_detail.

Date Entered – Optional.

Retrieved from ‘https://wiki.openpetra.org/index.php?title=Gift_Batch_Import_File_Specification&oldid=3415’