Gift Batch Import File Specification

From OpenPetra Wiki
Revision as of 14:30, 22 February 2013 by Robertpickett (talk | contribs)
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] 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".
  • 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 – [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?

Currency Code – [16ch] Must be a valid currency code from table a_currency: a_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 – [Dec?] 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] 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")

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

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=3413’