Data Conversion from Petra to Openpetra
see also
This page is about data conversion / migration from Petra to OpenPetra.
- see also List of unused columns/tables in the database structure: we need still to clean up some things in petra.xml
- see also Migrating data from legacy systems for the actual upgrade process
Finance Module
no splitting of tables across several years
This needs to be investigated/tested, on speed etc.
a_this_year_old_batch and a_previous_year_batch are merged into a_batch. The same for a_transaction and a_journal and a_trans_anal_attrib. Similar for a_corporate_exchange_rate and a_prev_year_corp_ex_rate
see http://bazaar.launchpad.net/~tpokorra/openpetraorg/20110926_dumpPetra2x/revision/1382
To discuss:
- a_journal does not contain a_base_currency_c, which the other tables contain. It is helpful to avoid changing historical data when the base currency changes. This would need to be tested with a NUnit test for the reports, if this function is still needed and should be maintained (11 years after the Euro conversion).
Standard and System Accounts
The Converter has to create the new set of account properties
In the code you can reference TAccountPropertyEnum.
This data shall be stored in a_account_property. Therefore new key of a_property_code_c is defined:
- Is_Special_Account
The primary key of a_account_property is:
CONSTRAINT a_account_property_pk PRIMARY KEY (a_ledger_number_i, a_account_code_c, a_property_code_c, a_property_value_c),
and so more than one value can be assigned to a key.
Standard Accounts
&GLOBAL-DEFINE EARNINGS-BF-ACCT 9700 &GLOBAL-DEFINE DIRECT-XFER-ACCT 5501 &GLOBAL-DEFINE ICH-SETTLEMENT-ACCT 5601 &GLOBAL-DEFINE ICH-ACCT 8500 &GLOBAL-DEFINE INTERNAL-XFER-ACCT 9800 &GLOBAL-DEFINE ADMIN-FEE-INCOME-ACCT 3400 &GLOBAL-DEFINE ADMIN-FEE-EXPENSE-ACCT 4900 &GLOBAL-DEFINE FUND-TRANSFER-INCOME-ACCT 3300 &GLOBAL-DEFINE FUND-TRANSFER-EXPENSE-ACCT 4800
Actually in use are ...
&GLOBAL-DEFINE ICH-ACCT 8500
In this case we set:
a_property_code_c = "Is_Special_Account"; a_property_value_c = "EARNINGS_BF_ACCT"
There is only one standard account for each of the types above. So the first value which is found shall be used.
Hint: The characters "-" and "_" are replaced in order to get a proper enum set in c#.
And furthermore there is another hard coded account
IF a_ledger.a_ilt_processing_centre_l THEN DO: FOR EACH a_account WHERE a_account.a_ledger_number_i EQ pv_ledger_number_i AND a_account.a_posting_status_l AND a_account.a_account_code_c BEGINS "85" ...
But this is ICH-ACCT
System Accounts
&GLOBAL-DEFINE GIFT-HEADING GIFT &GLOBAL-DEFINE INTER-LEDGER-HEADING ILT &GLOBAL-DEFINE BANK-HEADING CASH &GLOBAL-DEFINE BALANCE-SHEET-HEADING BAL SHT &GLOBAL-DEFINE PROFIT-AND-LOSS-HEADING PL &GLOBAL-DEFINE INCOME-HEADING INC &GLOBAL-DEFINE EXPENSE-HEADING EXP &GLOBAL-DEFINE DEBTOR-HEADING DRS &GLOBAL-DEFINE CREDITOR-HEADING CRS &GLOBAL-DEFINE TOTAL-ASSET-HEADING ASSETS &GLOBAL-DEFINE TOTAL-LIABILITY-HEADING LIABS &GLOBAL-DEFINE EQUITY-HEADING RET EARN
In this case we set:
a_property_code_c = "Is_Special_Account"; a_property_value_c = "GIFT_HEADING"
In this case we get a unique summation account.
GLM Period Records
OpenPetra may have a different way of handling forward periods than Petra and this will need to be considered when migrating the data. Currently OpenPetra stores a glm period record for every potential period (ie. number of periods + forward periods) and maintains a balance in all of them (ie. when something is posted the balance will be updated in the current period and all subsequent period records). Petra only maintains balances up to the current maximum period (ie. if your current period is 10 and there are 5 forward periods then there would be no balance in the period 16 GLM period records). So, the conversion script would need to populate the additional periods.
Change of Personnel Module Data
Personal Data Link
The following fields have been moved from table p_person to table pm_personal_data. During conversion the values need to be taken across.
p_believer_since_year_i p_believer_since_comment_c
Driving Licence Information
This information will no longer be available from the "Personal Data" (now "Miscellaneous") link in the Personnel Data Screen but should now be entered as a new record under "Personal Documents" (Document Type "DRIVING-LICENCE" with Category "GENERAL").
Data conversion needs to move data from pm_personal_data.pm_driving_license_number_c and create a new record in pm_document for this. Same needs to be done by data import from 2.x file format.
Language Levels
In Petra 2.x the list of language levels contains entries 01 to 10 and 99. This makes it too difficult for users to choose the appropriate level. A simplified model needs to be put in place and data conversion as well as import from previous file formats need to take this into account.
Therefore less levels are suggested:
- 01 Basic (Replaces 00, 01, 02, 03): "Uses a narrow range of language, adequate for basic needs and simple situations. Does not really have sufficient language to cope with normal day-to-day, real-life communication, but basic communication is possible with adequate opportunities for assistance."
- 02 Intermediate (Replaces 04, 05, 06, 07): "Uses the language independently and effectively in familiar situations. Rather frequent lapses in accuracy, fluency, appropriateness and organisation, but usually succeeds in communication and comprehending the general message."
- 03 Advanced (Replaces 08, 09): "Uses a full range of language with proficiency approaching that in the learner's own mother tongue. Copes well even with demanding and complex language situations. Makes minor lapses in accuracy, fluency, appropriateness and organisation which do not affect communication."
- 99 Unknown: "Speaks the language to some extent, level unknown."
Removing unused data items
The following fields have been removed from tables used in the personnel module:
TODO