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

Partner Module

Banking Details

Currently in Petra the SEPA mandate start and expiry dates are stored in p_partner_attribute with p_code_c = "SEPA", p_value_c = <bank account number>, p_valid_from_d = <SEPA start date>, p_valid_to_d = <SEPA expiry date>. In OpenPetra there should probably be additional fields in p_banking_details for this data instead since it is related to a bank account rather than a Partner.

Finance Module

General Ledger Settings

New entries in a_ledger_init_flag need to be set up:

• BRANCH-PROCESS needs to be added if a_branch_processing_l is set in a_ledger
• INTL-CURRENCY needs to be added if a_intl_currency_c is set in a_ledger

We need to consider if for entries in a_ledger_init_flag we still need to keep fields in a_ledger or if we can remove them. This would affect SUSP-ACCT, BUDGET, BRANCH-PROCESS, CURRENCY, INTL-CURRENCY and possibly more

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:

1. 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