General Ledger FAQ for R12 Upgrade Scripts and Known Upgrade Issues

1. Where are the R12 upgrade scripts located ?

R12 upgrade SQL scripts can be found in $GL_TOP/patch/115/sql directory.

2. Which are the most important upgrade scripts and what changes do they make?

glrrncol.sql	Script has DDL statements to rename the column set_of_books_id to ledger_id in GL tables which are having column set_of_books_id.
glraslg1.sql	Upgrades all sets of books to ledgers.
glraslg2.sql	Updates the ledger category code to SECONDARY for Secondary Ledgers. Generates configuration ID for Primary Ledgers. Updates SLA accounting method code and type for Cash Ledgers. Updates SLA columns for ALC Ledgers.
glrasgls.sql	Creates GL_LEDGERS_S sequence to start with a value larger than the maximum set of books ID.
glrbaup2.sql	Script populates chart_of_accounts_id, period_set_name columns in GL_JE_BATCHES table for all those upgraded journal batches. In R11chart_of_accounts_id,period_set_name columns are not present in GL_JE_BATCHES table. These columns are added in R12.
glrautop.sql	Upgrades autopost criteria. Populates chart_of_accounts_id, period_set_name & accounted_period_type columns in GL_AUTOMATIC_POSTING_SETS.
glrconso.sql	Upgrades Consolidation Mapping definitions.Creates the Chart of Accounts mapping in the GL_COA_MAPPINGS table.
glrreval.sql	Upgrades Revaluation definitions. Populates chart_of_accounts_id column in GL_REVALUATIONS table.
glrrjeup.sql	Upgrades recurring journal tables.Populates chart_of_accounts_id column in GL_RECURRING_BATCHES table.
glrstinc.sql	Upgrades data for summary templates. Populates data in GL_SUMMARY_HIERARCHIES table.
glrflias.sql	Upgrade script for populating the GL_ACCESS_SETS, GL_ACCESS_SET_NORM_ASSIGN, and GL_ACCESS_SET_ASSIGNMENTS tables. Also populates the GL: Data Access Set profile option values for all applications/users/responsibilities for which GL:Set Of Books profile option is set.
glrmadef.sql	Upgrades MassAllocation definitions. Mainly populates LEDGER_ID, LEDGER_CURRENCY columns in GL_ALLOC_FORMULA_LINES table
glrstbco.sql	Upgrades Summary Template Budgetary Control Options. Populates the new GL_SUMMARY_BC_OPTIONS table
glrpdrat.sql	Upgrades GL_TRANSLATION_RATES table. Populates daily_translation_rate_type info in GL_LEDGERS table. Inserts converstion types data into GL_DAILY_CONVERSION_TYPES table. Populates period rates related info in GL_DAILY_RATES table.
glrsyssu.sql	Populates values for following new profile options 'GL_RECORDS_TO_PROCESS', 'GL_ACCOUNTS_TO_PROCESS' & 'GL_GLLEZL_ARCHIVE_ROWS'.
glrsyusg.sql	Upgrades System Usages table. Populate the new consolidation_ledger_flag and efb_upgrade_flag in GL_SYSTEM_USAGES table.
glrjiint.sql	Updates set_od_books_id column value to -1 in GL_INTERFACE table.
glrlebsv.sql	Upgrades legal entity and balancing segment assignment. Populates data in GL_LE_VALUE_SETS table.
glrjebnm.sql	Updates jounral batch name in GL_JE_BATCHES table to change batch naming convention.
glrbobco.sql	Upgrades Budget Organization & Budgetary Control Options. Populates the new GL_BUDORG_BC_OPTIONS table.
glrasrs2.sql	Creates Secondary ledger relationships.
glrasrs4.sql	Updates ALC GL ledger relationships from SUBLEDGER level to JOURNAL level if it is PURE 'Thin' MRC. Updates SLA and Primary Ledger IDs of ALC JOURNAL/BALANCE ledger relationships to be same as their Source Ledgers if their Source ledgers are Secondary/ALC. Populates the column alc_no_rate_action_code same as their source ledgers i.e. journal/subledger level ALC ledgers.
glrarvcs.sql	Upgrade script for populating the GL_AUTOREV_CRITERIA_SETS.

3. How can I determine the cause of the issues I am facing after R12 upgrade?

1. Find the tables related to the problem.
2. From above upgrade scripts information find the upgrade script which is populating data in those tables.
3. Now open upgrade log file and check whether that script has failed. Check the exact error occurred when the script is executed and fix that error.

4. Why do most of the GL transaction forms (Enter Journals, Budget Journals, etc.) not retrieve any records after the upgrade?

This issue occurs if set_of_books_id column in GL tables is not renamed to ledger_id or if ledger_id column is not populated with values.
So check the upgrade log files for any errors while executing DDL statements in upgrade script glrrncol.sql.If there are any errors fix them or log a SR with Oracle support. Once ledger_id column is populated in GL tables all transactions forms will show data properly.

5. Why journal batches created before upgrade cannot be queried in journals form?

This happens if chart_of_accounts_id column in GL_JE_BATCHES table is null for the batches created before upgrade. Check upgrade log files and see if the script glrbaup2.sql is errored out. If it has errored out please rerun glrbaup2.sql script by following below steps.

1. Navigate to $GL_TOP/patch/115/sql directory:
   cd $GL_TOP/patch/115/sql
2. Connect to database using sqlplus as APPS user.
3. Execute glrbaup2.sql from sql command prompt:
   sql> @glrbaup2.sql

6. On the account inquiry form, after upgrade, when click on Show Journal button the following error occurs: "APP-FND-01347: You no longer have access to the specific information requested in this screen". How do I fix this?

This happens if chart_of_accounts_id column in GL_JE_BATCHES table is null for the batches created before upgrade. Check upgrade log files and see if the script glrbaup2.sql is errored out. If it has errored out please rerun glrbaup2.sql script by following below steps.

1. Navigate to $GL_TOP/patch/115/sql directory:
   cd $GL_TOP/patch/115/sql
2. Connect to database using sqlplus as APPS user.
3. Execute glrbaup2.sql from sql command prompt:
   sql> @glrbaup2.sql

7. When open the Enter Budget amounts form after the upgrade, the following error occurs: "ORA-00942: Table or view does not exist". How do I fix this?

It occurs if the view GL_BUDGET_ASSIGNMENTS_UNIQUE_V is not created or invalid. To fix this error execute below command from $GL_TOP/patch/115/odf path:

> adodfcmp userid=apps/apps mode=views odffile=glgvw.odf touser=apps/apps priv_schema=system/manager changedb=NO

Check the log file created by above command and follow the instructions to create the missing view GL_BUDGET_ASSIGNMENTS_UNIQUE_V.
Once the view is created enter budget amounts form can be opened.

8. Why Auto post criteria form does not retrieve records after upgrade?

If chart_of_accounts_id column is null in GL_AUTOMATIC_POSTING_SETS table, auto post criteria form can not retrieve any record. Check the upgrade log file and see if the script glrautop.sql has errored out. If so rerun glrautop.sql script by following below steps.

1. Navigate to $GL_TOP/patch/115/sql directory:
   cd $GL_TOP/patch/115/sql
2. Connect to database using sqlplus as APPS user.
3. Execute glrautop.sql from sql command prompt:
   sql> @glrautop.sql

9. GL Posting program after upgrade ends with error "PERF0005: Oracle error detected in glpipi() - ORA-00942: table or view does not exist". Why?

This error comes when GL_POSTING_INTERIM table is not created.

Create this table using gl_posting_interim.xdf file. After that posting program will run fine.
See note:308427.1 The XDF Comparison Utility (FndXdfCmp) and note:551325.1 How to verify or create a Database Object using a odf (adodfcmp) or xdf (FndXdfCmp) file ? 

10. After upgrade GL:Data Access Set profile option is not assigned to all GL responsibilities. How to fix this?

This can happen if the script glrflias.sql fails during upgrade.
Check upgrade log files and see whether the script glrflias.sql has failed with ora-01722 error.

If so execute below insert statement after taking the back up of FND_PROFILE_OPTION_VALUES table, then GL:Data access set profile will be assigned to all responsibilities for which GL:Set Of books profile option is set in 11i.

create table FND_PROFILE_OPTION_VALUES_BK as
select * from FND_PROFILE_OPTION_VALUES;
INSERT INTO FND_PROFILE_OPTION_VALUES pov
(APPLICATION_ID,
PROFILE_OPTION_ID,
LEVEL_ID,
LEVEL_VALUE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PROFILE_OPTION_VALUE,
LEVEL_VALUE_APPLICATION_ID)
SELECT
pov1.application_id,
po1.profile_option_id,
pov1.level_id,
pov1.level_value,
sysdate,
1,
sysdate,
1,
0,
ldg.implicit_access_set_id,
pov1.level_value_application_id
FROM
FND_PROFILE_OPTION_VALUES pov1,
GL_LEDGERS ldg,
FND_PROFILE_OPTIONS PO,
FND_PROFILE_OPTIONS PO1
WHERE pov1.application_id = 101
AND pov1.profile_option_id = po.profile_option_id
AND PO.profile_option_name='GL_SET_OF_BKS_ID'
AND po1.profile_option_name='GL_ACCESS_SET_ID'
AND pov1.level_id <> 10004
AND to_char(ldg.ledger_id) = pov1.profile_option_value
AND ldg.implicit_access_set_id IS NOT NULL
AND NOT EXISTS
(select 1 from fnd_profile_option_values pov2
where pov2.application_id = pov1.application_id
and pov2.profile_option_id = po1.profile_option_id
and pov2.level_id = pov1.level_id
and pov2.level_value = pov1.level_value
and nvl(pov2.level_value_application_id, -1)
= nvl(pov1.level_value_application_id, -1));
commit;