Gift Batch Import File Specification: Difference between revisions

From OpenPetra Wiki
Jump to navigation Jump to search
(Tidied up Int64 changes and previous keying errors)
 
(5 intermediate revisions by 2 users not shown)
Line 1: Line 1:
'''NOTE: This is very much a work in progress and needs to be completed and cleaned up before it can be trusted.'''
== 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 CSV format file is, of course, human readable but may contain accented characters (for a donor's name for example) and the file may be encoded in a number of different formats.  There is a special wiki page devoted to [[Text File Encoding in Open Petra]].
 
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.
 
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 follows the standard Open Petra Validation Framework and is used to check the 'business logic'.
* ''Import-specific validation''. While most validation falls into one of the above categories there are a few cases where the validation is import-specific.  So, for example, the number of columns in the row must be correct and the first row of a file must describe a Batch and not a Gift.
* ''Foreign Key Exception Handling''. The intention is that the validation performed using the Validation Framework should mean that this level of exception handling never occurs.  However there is specific foreign key exception handling in place that will show a 'friendly' message should an attempt be made to submit a row to the database that has a foreign key error.  If this does occur, reading the file will stop at the offending row.
* ''Other Exception Handling''. The intention is that as far as possible validation should read the whole file detecting errors and reporting them once the file has been completely read.  Until the first error has been found each row is provisionally added to the database, but once the first error has been found this process ceases, because attempting to add a bad record to the database would cause an 'exception' to be raised which would bring the import to a halt on that line.  However, if some aspect of validation has been missed in the design of the code, any database errors will be handled by a 'master exception handler' and the reason displayed and code will stop reading the file on that row.
 
Gift import batches consist of a repeating sequence of a batch header record followed by a collection of one or more individual gift records.
 
=== File Structure ===
Any lines that start with # or /* are treated as comments and are ignored.
The first line in the file should be a 'Batch'.  If the first line is a transaction, the code will create a dummy batch so that the file can continue to be read (but nothing will be saved to the database).
A Batch Header Record must contain 9 columns. A Gift Record must contain either 21 or 27 columns (there are two possible formats).
 
=== Batch Header Record ===
'''Row Type''' - “B”
: '''Validation Rules''': There must be 9 columns
 
'''Batch Description''' – <nowiki>[80ch. maximum] </nowiki> Required.  Gift batch description
: '''Validation Rules''': Cannot be empty field. Length must be 1-80 characters.
 
'''Bank Account Code''' – <nowiki>[16ch. maximum] </nowiki> 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''': The code must exist in the a_account table and must reference an active account.  Import fails if the account is inactive.  ('''Incomplete Validation''')
 
'''Hash Total''' – <nowiki>[decimal] </nowiki> Required. As with entering a batch using the Client GUI, this value can either be zero or should be the anticipated sum total of the batch. A zero value would mean that the batch could be posted.  Any other value would mean that the batch cannot be posted unless the batch total and hash total are identical.
: '''Validation Rules''': The field cannot be blank.  There is no check at import time that a non-zero hash total matches the batch total. ('''Validation differs from spec.''')
 
'''GL Effective Date''' – <nowiki>[Date] </nowiki> Required. The date must be within the date range of the current and open forward periods.
: '''Validation Rules''': The date must be within the date range of the current and open forward periods.
 
'''Currency Code''' – <nowiki>[16ch. maximum] </nowiki> Required. 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''': If the currency is not the same as the base ledger currency, there must be a pair of entries in the Corporate Exchange Rate table for the month in question.  ('''Incomplete Validation''')
 
'''Exchange Rate To Base''' – <nowiki>[decimal] </nowiki> Required. 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''': The exchange rate must be a positive decimal.  If the currency is the ledger base currency the rate must be 1.00.
 
'''Bank Cost Centre''' – <nowiki>[24ch. maximum] </nowiki> Required.  The cost centre which is applied to the bank. Must be an active local posting cost centre for the ledger.
: '''Validation Rules''': The cost centre code must exist in the a_cost_centre table and must reference an active account.  Import fails if the account is inactive.  ('''Incomplete Validation''')
 
'''Gift Type''' – <nowiki>[16ch. maximum] </nowiki> Optional.  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.
: '''Validation Rules''': None!  ('''Incomplete Validation''')
 
=== Gift Record ===
'''Row Type''' - “T”
: '''Validation Rules''': There must be either 21 or 27 columns.
 
'''Donor Key''' – <nowiki>[Int64] </nowiki> Required.  The partner key of the donor. The donor must be entered as a partner before the gift batch is imported. Note that if the donor's 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. Note that a partner key of 0 is treated as valid. (Is that right?)  A merged partner key is not allowed.  ('''Validation differs from spec.''')
 
'''Donor Short Name''' – This field is included for compatibility with the Gift Batch Export. It is not used on import.
 
'''Method of Giving Code''' – <nowiki>[16ch. maximum] </nowiki> Optional. If not blank the option must be one that is defined in the Method of Giving table for your installation.
: '''Validation Rules''': None - apart from foreign key exception handling!  ('''Incomplete Validation''')
 
'''Method of Payment Code''' – <nowiki>[16ch. maximum] </nowiki> Optional. If not blank the option must be one that is defined in the Method of Payment table for your installation.
: '''Validation Rules''': None - apart from foreign key exception handling!  ('''Incomplete Validation''')
 
'''Reference''' – <nowiki>[20ch. maximum] </nowiki> Optional.
: '''Validation Rules''': None beyond the standard validation of the maximum length.
 
'''Receipt Letter Code''' – <nowiki>[16ch. maximum] </nowiki> Optional. If not blank the option must be one that is defined in the Form Letter Design table.  (Does this table exist??)
: '''Validation Rules''': None!  ('''Incomplete Validation''')
 
'''Receipt Number''' (27 column version only) – <nowiki>[Int32] </nowiki> Optional. A receipt number.  If blank it will default to 0.  ('''Urgent - will default to -1!''')
 
'''First Time Gift''' (27 column version only) – <nowiki>[Boolean] </nowiki> Optional. "yes" or "no". Enter yes to indicate a first time gift. Defaults to "no".
 
'''Receipt Printed''' (27 column version only) – <nowiki>[Boolean] </nowiki> Optional. "yes" or "no". Enter yes to indicate a receipt has been printed. Defaults to "no".
 
The following fields make up the gift 'detail'.  If the "T" row field values for the previous columns match exactly the same columns in the previous "T" row, then this row is saved as part of the same gift as an additional gift detail.
 
'''Recipient Key''' – <nowiki>[Int64] </nowiki> Required.  The partner key of the recipient in the p_partner table.  Partner Class must be "FAMILY" or "UNIT". If Family, tnen special type (p_partner_type) must be "WORKER". Partner status must not be "MERGED".
: '''Validation Rules''': Partner key must be a valid, non-merged, partner that is either a FAMILY or UNIT.  No check is made on the partner_type of a FAMILY.  Note that the recipent key can be 0. ('''Incomplete Validation''')
 
'''Recipient Short Name''' – This field is included for compatibility with the Gift Batch Export. It is not used on import.
 
'''Recipient Ledger Number''' (27 column version only) – <nowiki>[Int64] </nowiki> Optional.  Meaning depends on recipient partner key type.
 
'''Gift Amount''' – <nowiki>[decimal] </nowiki> Required. The amount of this gift.
: '''Validation Rules''': Must be a valid non-zero decimal number. Can be positive or negative.
 
'''Gift Amount International''' (27 column version only) – <nowiki>[decimal] </nowiki> Optional. The amount of this gift in the ledger international currency. If blank a value of 0 will be used.
 
'''Confidential Gift Flag''' – <nowiki>[Boolean] </nowiki> Optional. "yes" or "no". Enter yes to indicate a confidential gift. Defaults to "no".
: '''Validation Rules''': If "yes" the value is taken as TRUE. Any other text, or none, is taken as FALSE.
 
'''Motivation Group Code''' – <nowiki>[16ch. maximum] </nowiki> Required. Select an entry from the installation's Motivation Group table for the active ledger.
: '''Validation Rules''': If the code is MOTIVATION_GROUP_GIFT and the recipient partner key cannot be 0.  No check is yet made that the code specified exists.  ('''Incomplete Validation''')
 
'''Motivation Detail Code''' – <nowiki>[16ch. maximum] </nowiki> Required. Select an entry from the installations Motivation Detail table for the selected Motivation Group Code for the active ledger.
: '''Validation Rules''': No special validation. ('''Incomplete Validation''')
 
'''Cost Centre Code''' (27 column version only) – <nowiki>[16ch. maximum] </nowiki> Optional.  If specified, it must refer to an existing active cost centre.
 
'''Gift Comment 1''' – <nowiki>[160ch. maximum] </nowiki> Optional. Long description of the gift.
: '''Validation Rules''': No special validation.
 
'''Gift Comment 1 Type''' – <nowiki>[160ch. maximum] </nowiki> Optional. If used, must contain a valid Gift Comment Type. Cannot be blank if Gift Comment 1 is used.
: '''Validation Rules''': Cannot be blank if Gift Comment 1 is specified.  No check made on a specific value. ('''Incomplete Validation''')
 
'''Mailing Code''' – <nowiki>[16ch. maximum] </nowiki> Optional. If used it must be a valid mailing code from the p_mailing table.
: '''Validation Rules''': No special validation.  ('''Incomplete Validation''')
 
'''Gift Comment 2''' – <nowiki>[160ch. maximum] </nowiki> Optional. Long description of the gift.
: '''Validation Rules''': No special validation.
 
'''Gift Comment 2 Type''' – <nowiki>[160ch. maximum] </nowiki> Optional. If used, must contain a valid Gift Comment Type. Cannot be blank if Gift Comment 2 is used.
: '''Validation Rules''': Cannot be blank if Gift Comment 2 is specified.  No check made on a specific value. ('''Incomplete Validation''')
 
'''Gift Comment 3''' – <nowiki>[160ch. maximum] </nowiki> Optional. Long description of the gift.
: '''Validation Rules''': No special validation.
 
'''Gift Comment 3 Type''' – <nowiki>[160ch. maximum] </nowiki> Optional. If used, must contain a valid Gift Comment Type. Cannot be blank if Gift Comment 3 is used.
: '''Validation Rules''': Cannot be blank if Gift Comment 3 is specified.  No check made on a specific value. ('''Incomplete Validation''')
 
'''Tax Deductible Flag''' – <nowiki>[Boolean] </nowiki> Optional. if blank, it will be filled from a_tax_deductible_flag_1 from the Motivation Detail.
: '''Validation Rules''': No special validation.  ('''Urgent! This implementation is incorrect''').
 
'''Date Entered''' – <nowiki>[Date] </nowiki> This field is currently not used.  The code will need changing to implement it.  ('''Urgent!''')
 
== Earlier Specification Prepared by [[User:Dwmosman|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. - [[User:Dwmosman|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.
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.   
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 fields ==
=== Batch Header Record ===
'''Row Type''' - “B”
'''Row Type''' - “B”


Line 13: Line 151:
'''Bank Account Code''' – <nowiki>[16ch. maximum] </nowiki> 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.
'''Bank Account Code''' – <nowiki>[16ch. maximum] </nowiki> 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.
: '''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.
* [[User:Dwmosman|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?)
* [[User:Pokorra|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".
* [[User:Robertpickett|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).
* [[User:Dwmosman|Dwmosman]] 16:53, 27 February 2013 (UTC) --- updated ---


'''Hash Total''' – <nowiki>[decimal] </nowiki>  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.
'''Hash Total''' – <nowiki>[decimal] </nowiki>  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.  
: '''Validation Rules:'''  If non-zero, then the hash total must equal the sum of the gift amounts from the batch.  
* [[User:Dwmosman|Dwmosman]] Can this be blank on the input record?
* [[User:Robertpickett|Robertpickett]] Yes, it can be blank


'''GL Effective Date''' – <nowiki>[date] </nowiki>  Required.  The date must be within the date range of the current and open forward periods.
'''GL Effective Date''' – <nowiki>[date] </nowiki>  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.  See GL.Importing.cs for example.
: '''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 [http://bazaar.launchpad.net/~openpetracore/openpetraorg/trunkhosted/view/head:/csharp/ICT/Petra/Server/lib/MFinance/GL/GL.Importing.cs csharp\ICT\Petra\Server\lib\MFinance\GL\GL.Importing.cs].  
* [[User:Dwmosman|Dwmosman]]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?
* [[User:Pokorra|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 [http://bazaar.launchpad.net/~openpetracore/openpetraorg/trunkhosted/view/head:/csharp/ICT/Petra/Server/lib/MFinance/GL/GL.Importing.cs csharp\ICT\Petra\Server\lib\MFinance\GL\GL.Importing.cs], search for TFinancialYear.IsValidPostingPeriod. The value cannot be empty.


'''Currency Code''' – <nowiki>[16ch.] </nowiki>  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.
'''Currency Code''' – <nowiki>[16ch.] </nowiki>  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).
: '''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).
* [[User:Robertpickett|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''' – <nowiki>[decimal] </nowiki>  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.
'''Exchange Rate to Base''' – <nowiki>[decimal] </nowiki>  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.
: '''Validation Rules:'''  Cannot be 0.  If currency code above equals the currency of the active ledger, then exchange rate must equal 1.
* [[User:Dwmosman|Dwmosman]]  Cannot be 0. Any other constraints?
* [[User:Robertpickett|Robertpickett]] If the currency code matches the base currency of the ledger then the exchange rate must be 1


'''Bank Cost Centre''' – <nowiki>[24ch.] </nowiki>  The cost centre which is applied to the bank.  Must be an active local posting cost centre for the ledger.
'''Bank Cost Centre''' – <nowiki>[24ch.] </nowiki>  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.   
: '''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.
* [[User:Dwmosman|Dwmosman]] Must be valid cost centre. Determined how?
* [[User:Pokorra|Pokorra]] cost centre is not related to bank. can be any active costcentre, table a_cost_centre, depending on the ledger number.
* [[User:Robertpickett|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")
* [[User:Dwmosman|Dwmosman]] 08:52, 26 February 2013 (UTC) 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''' – <nowiki>[16ch.] </nowiki> Optional. Must be "Gift", "Gift In Kind" or "Other".  If left blank, it defaults to "Gift".
'''Gift Type''' – <nowiki>[16ch.] </nowiki>  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.
: '''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 [http://bazaar.launchpad.net/~openpetracore/openpetraorg/trunkhosted/view/head:/csharp/ICT/Petra/Shared/lib/MFinance/Constants.cs Shared/lib/MFinance/Constants.cs], GIFT_TYPE_*)
* [[User:Dwmosman|Dwmosman]] Must be one of {Gifts, Gifts in kind, Other}. (hard-coded options?)
* [[User:Pokorra|Pokorra]] please use the hard coded constants from [http://bazaar.launchpad.net/~openpetracore/openpetraorg/trunkhosted/view/head:/csharp/ICT/Petra/Shared/lib/MFinance/Constants.cs Shared/lib/MFinance/Constants.cs], GIFT_TYPE_*
* [[User:Dwmosman|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?
* [[User:Dwmosman|Dwmosman]] 08:52, 26 February 2013 (UTC) 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?
* [[User:Robertpickett|Robertpickett]] In Petra there must always be a value. I don't know whether it is valid to use Gift as default. It probably is but would need to ask user community to be sure
== 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.
*[[User:Pokorra|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.
* [[User:Robertpickett|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.
* [[User:Dwmosman|Dwmosman]] Should I eliminate the extended format? (Fields preceded by a ">" sign are part of the extended format)
* [[User:Robertpickett|Robertpickett]] I think it could be eliminated from the import, unless Timo had a particular reason for including it.


=== Gift Record ===
'''Row Type''' - “T”
'''Row Type''' - “T”


'''Donor Key''' – <nowiki>[Int64] </nowiki> 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.
'''Donor Key''' – <nowiki>[Int64] </nowiki> 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 be used instead.  Ideally the system will look up the new new partner and ask the user if they want to use the new partner.  If yes, replace the key with the new key.  If  no, then fail the import. 
: '''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).
* [[User:Dwmosman|Dwmosman]] Must be a valid donor key from partner(?) table, xxx
*[[User:Pokorra|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?
* [[User:Robertpickett|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.
* [[User:christiankatict|christiankatict]] Changed Type from 'integer' back to 'Int64' as PartnerKeys can exceed the number range of an int/Int32.


'''Donor Short Name''' – note: This field is required in the gift row but can be empty and is always ignored by the import process.
'''Donor Short Name''' – This field is included for compatibility with the Gift Batch Export.  It is not used on import.
: [[User:Dwmosman|Dwmosman]] Is there a reason we can't remove this field from the import since its not used?


'''Method of Giving Code''' – <nowiki>[16ch.] </nowiki> Select one of the options defined in the Method of Giving table for your installation.
'''Method of Giving Code''' – <nowiki>[16ch.] </nowiki> Select one of the options defined in the Method of Giving table for your installation.
Line 78: Line 186:
'''Reference''' – <nowiki>[20ch.] </nowiki> Reference number/code for the gift transaction.
'''Reference''' – <nowiki>[20ch.] </nowiki> Reference number/code for the gift transaction.
: '''Validation Rules:''' Cannot be longer than 20 characters.
: '''Validation Rules:''' Cannot be longer than 20 characters.
* [[User:Dwmosman|Dwmosman]] Can this field be empty?


'''Receipt Letter Code''' – <nowiki>[16ch.] </nowiki> Optional.  "<none>" is the same as blank.  Otherwise select one of the values from your installations Form Letter Design table.
'''Receipt Letter Code''' – <nowiki>[16ch.] </nowiki> 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.
: '''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.
* [[User:Dwmosman|Dwmosman]] values sourced from ??. Is <nowiki><none> is a valid value? </nowiki>Can this be blank?
*[[User:Pokorra|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.
* [[User:Robertpickett|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''' – <nowiki>[integer] </nowiki> Optional.  Gift receipt number. Defaults to 0.
: '''Validation Rules:''' Must be blank, 0 or a valid positive integer.
>'''First Time Gift''' – <nowiki>[boolean] </nowiki> Optional.  Enter "yes" or "no" to indicate whether this is a first time gift for the donor.  Defaults to "no".
: '''Validation Rules:''' Must be "yes", "no" or blank.  If blank, default to "no" (zero).
>'''Receipt Printed''' – <nowiki>[bool]</nowiki> Optional.  Enter "yes" or "no" to indicate whether the receipt has been printed for this gift. Defaults to "no".
: '''Validation Rules:''' Must be "yes", "no" or blank.  If blank, default to "no" (zero).


'''Recipient Key''' – <nowiki>[Int64] </nowiki>  Select the key of the recipient from the partner table.
'''Recipient Key''' – <nowiki>[Int64] </nowiki>  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".
: '''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".
* [[User:Dwmosman|Dwmosman]] Must be (active?) partner from partner table, p_partner: p_partner_key_n
*[[User:Pokorra|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.
* [[User:Robertpickett|Robertpickett]] Should also not allow Partners with status "MERGED".
* [[User:christiankatict|christiankatict]] Changed Type from 'integer' back to 'Int64' as PartnerKeys can exceed the number range of an int/Int32.


'''Recipient Short Name''' – ignored by import
'''Recipient Short Name''' – This field is included for compatibility with the Gift Batch Export. It is not used on import.
* [[User:Dwmosman|Dwmosman]] Can I remove this from the import, since its ignored anyway?
 
>'''Recipient Ledger Number''' – <nowiki>[Int64] </nowiki>Optional?.
* [[User:Dwmosman|Dwmosman]] 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.
*[[User:Pokorra|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.
* [[User:Robertpickett|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.
* [[User:Dwmosman|Dwmosman]] 08:52, 26 February 2013 (UTC) Can I remove this from the import?  It sounds like it is recalculated at posting anyway so its basically ignored?
* [[User:christiankatict|christiankatict]] Changed Type from 'integer' back to 'Int64' as PartnerKeys can exceed the number range of an int/Int32.


'''Gift Amount''' – <nowiki>[decimal]</nowiki> Enter the gift amount.
'''Gift Amount''' – <nowiki>[decimal]</nowiki> Enter the gift amount.
: '''Validation Rules:''' Must be a valid decimal number
: '''Validation Rules:''' Must be a valid non-zero decimal number. Can be positive or negative.
* [[User:Robertpickett|Robertpickett]] Must be non-zero. Can be positive or negative
* [[User:Dwmosman|Dwmosman]] 08:52, 26 February 2013 (UTC) Do I need to edit this for the correct number of decimal places? (some currencies have 0 or 3 decimal places)
* [[User:Dwmosman|Dwmosman]] 08:52, 26 February 2013 (UTC) Are there rules about what can be entered here for Gift in Kind or Other?
 
>'''Gift Amount International''' – <nowiki>[decimal] </nowiki>


'''Confidential Gift Flag '''–<nowiki> [boolean]</nowiki> Optional.  "yes" or "no". Enter yes to indicate a confidential gift.  Defaults to "no".
'''Confidential Gift Flag '''–<nowiki> [boolean]</nowiki> Optional.  "yes" or "no". Enter yes to indicate a confidential gift.  Defaults to "no".
Line 125: Line 203:
'''Motivation Group Code''' – <nowiki>[16ch.] </nowiki> Select an entry from the installation's Motivation Group table for the active ledger.
'''Motivation Group Code''' – <nowiki>[16ch.] </nowiki> 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).
: '''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).
* [[User:Dwmosman|Dwmosman]] Optional? Validate against table a_motivation_group: a_ledger_number_i, a_motivation_group_code_c. (does value of a_restricted_l matter?)
*[[User:Pokorra|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'''– <nowiki>[16ch.] </nowiki> Select an entry from the installations Motivation Detail table for the selected Motivation Group Code for the active ledger
'''Motivation Detail Code'''– <nowiki>[16ch.] </nowiki> 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 code (a_motivation_detail_code_c) from the Motivation Detail table, a_motivation_detail with the active ledger (a_ledger_number_i), the selected group code (a_motivation_group_code_c) and with status = true (a_motivation_status_1).
: '''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.
: [[User:Dwmosman|Dwmosman]] 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?
*[[User:Pokorra|Pokorra]] I think you do not need to copy values from motivation detail record.
* [[User:Robertpickett|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.
* [[User:Dwmosman|Dwmosman]] 08:52, 26 February 2013 (UTC) Actually, if you create the gift detail in OpenPetra, these fields are generated and you cannot manually enter or change them. So I assume, that they must be regenerated at post? (because the values in the DB could have changed between entry and post).  So if they are in the import file and they contradict each other?  So it seems that a consistent decision should be made as to which fields can be manually controlled (either online or via batch import) and which are to be automatically derived once other fields, like motivational detail, are selected?


'''Cost Centre Code''' –<nowiki> [24ch] </nowiki>
'''Cost Centre Code''' – This field is included for compatibility with the Gift Batch Export.  It is not used on import.
* [[User:Robertpickett|Robertpickett]] I don't think this should be here? Is it part of the extended format?
* [[User:Dwmosman|Dwmosman]] 08:52, 26 February 2013 (UTC) It is in the current OpenPetra code for the normal import format.  Its also in the gift detail table in the database. Is this a design question that should be discussed?


'''Gift Comment One''' – <nowiki>[160ch.] </nowiki> Optional.  Long description of the gift.
'''Gift Comment One''' – <nowiki>[160ch.] </nowiki> Optional.  Long description of the gift.
: '''Validation Rules''' - Check for SQL injection.
: '''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.
* [[User:Dwmosman|Dwmosman]] optional? Max length?
*[[User:Pokorra|Pokorra]] yes, is optional. length 160 characters: see http://openpetraorg.sourceforge.net/dbdoc/index.html?table=a_gift_detail&group=gift
* [[User:christiankatict|christiankatict]] 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.
'''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 or a valid GIFT_COMMENT_TYPE.  Cannot be blank if Gift Comment One is not blank.
: '''Validation Rules:''' - blank (cannot be blank if Gift Comment One is not blank) or a valid GIFT_COMMENT_TYPE. See [http://bazaar.launchpad.net/~openpetracore/openpetraorg/trunkhosted/view/head:/csharp/ICT/Petra/Shared/lib/MFinance/Constants.cs Shared/lib/MFinance/Constants.cs], GIFT_COMMENT_TYPE*. (Note: that Constant.cs file is actually missing more constants: Donor, Recipient, Office)
* [[User:Dwmosman|Dwmosman]] are comment types hard-coded or tabled? Can this be blank if comment is not blank?
*[[User:Pokorra|Pokorra]] hard coded, see [http://bazaar.launchpad.net/~openpetracore/openpetraorg/trunkhosted/view/head:/csharp/ICT/Petra/Shared/lib/MFinance/Constants.cs Shared/lib/MFinance/Constants.cs], GIFT_COMMENT_TYPE*. that Constant.cs file is actually missing more constants: Donor, Recipient, Office
* [[User:Robertpickett|Robertpickett]] Must contain a value if comment is not blank.
* [[User:Dwmosman|Dwmosman]] 08:52, 26 February 2013 (UTC) As per my earlier question.  Could these constants be kept in perhaps a generic that I could ping it with Linq or some such :)
* [[User:Dwmosman|Dwmosman]] 08:52, 26 February 2013 (UTC) For my own edification, can someone
 


'''Mailing Code''' – <nowiki>[16ch.] </nowiki> Optional.  "<none>" is the same as blank.  Otherwise select one of the values from your installations Mailing Code table.
'''Mailing Code''' – <nowiki>[16ch.] </nowiki> 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.
: '''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.
* [[User:Robertpickett|Robertpickett]] Same rules as for Receipt Letter Code but using p_mailing (p_mailing_code_c) for the lookup


'''Gift Comment Two''' - See Gift Comment One
'''Gift Comment Two''' - See Gift Comment One
Line 167: Line 227:


'''Tax Deductable''' <nowiki>[boolean] </nowiki> Optional.  if blank, it will be filled from a_tax_deductible_flag_1 from the Motivation Detail.
'''Tax Deductable''' <nowiki>[boolean] </nowiki> 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 from the selected 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.
* [[User:Robertpickett|Robertpickett]] Boolean. If blank then will be filled from a_tax_deductible_flag_l on a_motivation_detail.


'''Date Entered''' – <nowiki>[date] </nowiki> Optional.  If blank, the gift batch effective date is used.
'''Date Entered''' – <nowiki>[date] </nowiki> 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.
: '''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.
* [[User:Robertpickett|Robertpickett]] not in Petra.

Latest revision as of 19:47, 27 Mayıs 2016

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 CSV format file is, of course, human readable but may contain accented characters (for a donor's name for example) and the file may be encoded in a number of different formats. There is a special wiki page devoted to Text File Encoding in Open Petra.

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.

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 follows the standard Open Petra Validation Framework and is used to check the 'business logic'.
  • Import-specific validation. While most validation falls into one of the above categories there are a few cases where the validation is import-specific. So, for example, the number of columns in the row must be correct and the first row of a file must describe a Batch and not a Gift.
  • Foreign Key Exception Handling. The intention is that the validation performed using the Validation Framework should mean that this level of exception handling never occurs. However there is specific foreign key exception handling in place that will show a 'friendly' message should an attempt be made to submit a row to the database that has a foreign key error. If this does occur, reading the file will stop at the offending row.
  • Other Exception Handling. The intention is that as far as possible validation should read the whole file detecting errors and reporting them once the file has been completely read. Until the first error has been found each row is provisionally added to the database, but once the first error has been found this process ceases, because attempting to add a bad record to the database would cause an 'exception' to be raised which would bring the import to a halt on that line. However, if some aspect of validation has been missed in the design of the code, any database errors will be handled by a 'master exception handler' and the reason displayed and code will stop reading the file on that row.

Gift import batches consist of a repeating sequence of a batch header record followed by a collection of one or more individual gift records.

File Structure

Any lines that start with # or /* are treated as comments and are ignored. The first line in the file should be a 'Batch'. If the first line is a transaction, the code will create a dummy batch so that the file can continue to be read (but nothing will be saved to the database). A Batch Header Record must contain 9 columns. A Gift Record must contain either 21 or 27 columns (there are two possible formats).

Batch Header Record

Row Type - “B”

Validation Rules: There must be 9 columns

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: The code must exist in the a_account table and must reference an active account. Import fails if the account is inactive. (Incomplete Validation)

Hash Total – [decimal] Required. As with entering a batch using the Client GUI, this value can either be zero or should be the anticipated sum total of the batch. A zero value would mean that the batch could be posted. Any other value would mean that the batch cannot be posted unless the batch total and hash total are identical.

Validation Rules: The field cannot be blank. There is no check at import time that a non-zero hash total matches the batch total. (Validation differs from spec.)

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

Validation Rules: The date must be within the date range of the current and open forward periods.

Currency Code – [16ch. maximum] Required. 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: If the currency is not the same as the base ledger currency, there must be a pair of entries in the Corporate Exchange Rate table for the month in question. (Incomplete Validation)

Exchange Rate To Base – [decimal] Required. 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: The exchange rate must be a positive decimal. If the currency is the ledger base currency the rate must be 1.00.

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

Validation Rules: The cost centre code must exist in the a_cost_centre table and must reference an active account. Import fails if the account is inactive. (Incomplete Validation)

Gift Type – [16ch. maximum] Optional. 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.

Validation Rules: None! (Incomplete Validation)

Gift Record

Row Type - “T”

Validation Rules: There must be either 21 or 27 columns.

Donor Key – [Int64] Required. The partner key of the donor. The donor must be entered as a partner before the gift batch is imported. Note that if the donor's 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. Note that a partner key of 0 is treated as valid. (Is that right?) A merged partner key is not allowed. (Validation differs from spec.)

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. maximum] Optional. If not blank the option must be one that is defined in the Method of Giving table for your installation.

Validation Rules: None - apart from foreign key exception handling! (Incomplete Validation)

Method of Payment Code – [16ch. maximum] Optional. If not blank the option must be one that is defined in the Method of Payment table for your installation.

Validation Rules: None - apart from foreign key exception handling! (Incomplete Validation)

Reference – [20ch. maximum] Optional.

Validation Rules: None beyond the standard validation of the maximum length.

Receipt Letter Code – [16ch. maximum] Optional. If not blank the option must be one that is defined in the Form Letter Design table. (Does this table exist??)

Validation Rules: None! (Incomplete Validation)

Receipt Number (27 column version only) – [Int32] Optional. A receipt number. If blank it will default to 0. (Urgent - will default to -1!)

First Time Gift (27 column version only) – [Boolean] Optional. "yes" or "no". Enter yes to indicate a first time gift. Defaults to "no".

Receipt Printed (27 column version only) – [Boolean] Optional. "yes" or "no". Enter yes to indicate a receipt has been printed. Defaults to "no".

The following fields make up the gift 'detail'. If the "T" row field values for the previous columns match exactly the same columns in the previous "T" row, then this row is saved as part of the same gift as an additional gift detail.

Recipient Key – [Int64] Required. The partner key of the recipient in the p_partner table. Partner Class must be "FAMILY" or "UNIT". If Family, tnen special type (p_partner_type) must be "WORKER". Partner status must not be "MERGED".

Validation Rules: Partner key must be a valid, non-merged, partner that is either a FAMILY or UNIT. No check is made on the partner_type of a FAMILY. Note that the recipent key can be 0. (Incomplete Validation)

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

Recipient Ledger Number (27 column version only) – [Int64] Optional. Meaning depends on recipient partner key type.

Gift Amount – [decimal] Required. The amount of this gift.

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

Gift Amount International (27 column version only) – [decimal] Optional. The amount of this gift in the ledger international currency. If blank a value of 0 will be used.

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

Validation Rules: If "yes" the value is taken as TRUE. Any other text, or none, is taken as FALSE.

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

Validation Rules: If the code is MOTIVATION_GROUP_GIFT and the recipient partner key cannot be 0. No check is yet made that the code specified exists. (Incomplete Validation)

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

Validation Rules: No special validation. (Incomplete Validation)

Cost Centre Code (27 column version only) – [16ch. maximum] Optional. If specified, it must refer to an existing active cost centre.

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

Validation Rules: No special validation.

Gift Comment 1 Type – [160ch. maximum] Optional. If used, must contain a valid Gift Comment Type. Cannot be blank if Gift Comment 1 is used.

Validation Rules: Cannot be blank if Gift Comment 1 is specified. No check made on a specific value. (Incomplete Validation)

Mailing Code – [16ch. maximum] Optional. If used it must be a valid mailing code from the p_mailing table.

Validation Rules: No special validation. (Incomplete Validation)

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

Validation Rules: No special validation.

Gift Comment 2 Type – [160ch. maximum] Optional. If used, must contain a valid Gift Comment Type. Cannot be blank if Gift Comment 2 is used.

Validation Rules: Cannot be blank if Gift Comment 2 is specified. No check made on a specific value. (Incomplete Validation)

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

Validation Rules: No special validation.

Gift Comment 3 Type – [160ch. maximum] Optional. If used, must contain a valid Gift Comment Type. Cannot be blank if Gift Comment 3 is used.

Validation Rules: Cannot be blank if Gift Comment 3 is specified. No check made on a specific value. (Incomplete Validation)

Tax Deductible Flag – [Boolean] Optional. if blank, it will be filled from a_tax_deductible_flag_1 from the Motivation Detail.

Validation Rules: No special validation. (Urgent! This implementation is incorrect).

Date Entered – [Date] This field is currently not used. The code will need changing to implement it. (Urgent!)

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.
Retrieved from ‘https://wiki.openpetra.org/index.php?title=Gift_Batch_Import_File_Specification&oldid=4575’