Create Accounting OPM GMF Completes with Error

Create Accounting Completes with Error - GMF 

Error :

GMFAACCP module: Create Accounting

+---------------------------------------------------------------------------+

Current system time is 21-SEP-2013 09:31:21

+---------------------------------------------------------------------------+

XDO Data Engine Version No: 5.6.3

Resp: 21591

Org ID : 82

Request ID: 21699798

All Parameters: P_SOURCE_APPLICATION_ID=555:P_APPLICATION_ID=555:P_DUMMY=Y:P_LEDGER_ID=1:P_PROCESS_CATEGORY_CODE=:P_END_DATE="2013/09/21 00:00:00":P_CREATE_ACCOUNTING_FLAG=Y:P_DUMMY_PARAM_1=Y:P_ACCOUNTING_MODE=D:P_DUMMY_PARAM_2=:P_ERRORS_ONLY_FLAG=N:P_REPORT_STYLE=S:P_TRANSFER_TO_GL_FLAG=:P_DUMMY_PARAM_3=:P_POST_IN_GL_FLAG=:P_GL_BATCH_NAME=:P_MIN_PRECISION=:P_INCLUDE_ZERO_AMOUNT_LINES=N:P_REQUEST_ID=:P_ENTITY_ID=:P_SOURCE_APPLICATION_NAME=Process Manufacturing Financials:P_APPLICATION_NAME=Process Manufacturing Financials:P_LEDGER_NAME=PepsiCo_VN:P_PROCESS_CATEGORY_NAME=:P_CREATE_ACCOUNTING=Yes:P_ACCOUNTING_MODE_NAME=Draft:P_ERRORS_ONLY=No:P_ACCOUNTING_REPORT_LEVEL=Summary:P_TRANSFER_TO_GL=:P_POST_IN_GL=:P_INCLUDE_ZERO_AMT_LINES=No:P_VALUATION_METHOD_CODE=:P_SECURITY_INT_1=:P_SECURITY_INT_2=:P_SECURITY_INT_3=:P_SECURITY_CHAR_1=:P_SECURITY_CHAR_2=:P_SECURITY_CHAR_3=:P_CONC_REQUEST_ID=:P_INCLUDE_USER_TRX_ID_FLAG=N:P_INCLUDE_USER_TRX_IDENTIFIERS=No:P_USER_ID=5497:DebugFlag=N

Data Template Code: GMFAACCP

Data Template Application Short Name: GMF

Debug Flag: N

{P_ACCOUNTING_REPORT_LEVEL=Summary, P_DUMMY=Y, P_ACCOUNTING_MODE_NAME=Draft, P_ERRORS_ONLY_FLAG=N, P_REPORT_STYLE=S, P_GL_BATCH_NAME=, P_END_DATE=2013/09/21 00:00:00, P_SECURITY_INT_3=, P_SECURITY_INT_2=, P_SECURITY_INT_1=, P_VALUATION_METHOD_CODE=, P_POST_IN_GL=, P_TRANSFER_TO_GL=, P_TRANSFER_TO_GL_FLAG=, P_INCLUDE_USER_TRX_IDENTIFIERS=No, P_USER_ID=5497, P_PROCESS_CATEGORY_NAME=, P_ERRORS_ONLY=No, P_DUMMY_PARAM_3=, P_SECURITY_CHAR_3=, P_DUMMY_PARAM_2=, P_SECURITY_CHAR_2=, P_DUMMY_PARAM_1=Y, P_SECURITY_CHAR_1=, P_ENTITY_ID=, P_PROCESS_CATEGORY_CODE=, P_INCLUDE_ZERO_AMT_LINES=No, P_LEDGER_ID=1, P_POST_IN_GL_FLAG=, P_APPLICATION_ID=555, P_INCLUDE_USER_TRX_ID_FLAG=N, P_APPLICATION_NAME=Process Manufacturing Financials, P_REQUEST_ID=, P_CONC_REQUEST_ID=, P_LEDGER_NAME=PepsiCo_VN, P_SOURCE_APPLICATION_ID=555, P_CREATE_ACCOUNTING=Yes, P_CREATE_ACCOUNTING_FLAG=Y, P_MIN_PRECISION=, P_SOURCE_APPLICATION_NAME=Process Manufacturing Financials, P_INCLUDE_ZERO_AMOUNT_LINES=N, P_ACCOUNTING_MODE=D}

Calling XDO Data Engine...

****Warning!!! Due to high volume of data, got out of memory exception...***

****Please retry with scalable option or modify the Data template to run in scalable mode...***

Solution:

1) Add a new parameter called Scalable Flag as follows -

Access the "System Administrator" responsibility.

Navigate to Concurrent > Program > Define.

Search for the report (e.g., Create Accounting)

Click on Parameters button.

Move to the last parameter.

Add the Scalable Flag as follows -

Parameter- P_SCALABLE_FLAG

Description - ScalableFlag

Enabled - Check

Value Set - XLA_SRS_NO_VALIDATION

Default Type - Constant

Default Value - Y

Prompt - P_SCALABLE_FLAG

Token - ScalableFlag (Case sensitive. Give this exact value)

2) Re-run the Create Accounting.

Note that the parameter - P_SCALABLE_FLAG - has a Default Value of 'Y'.

Gather Schema Statistics Completes with Error

Gather Schema Statistics Completes with Error 

SYMPTOMS
"Gather Schema Statistics" program reported following errors in request log files:
Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #2: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #3: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #4: ERROR: While GATHER_TABLE_STATS:  object_name=FII.FII_FIN_ITEM_HIERARCHIES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***


CHANGES
Problem started after database has been upgraded to 11G.
CAUSE
There are two reasons for that error message:
1 ) There are duplicate rows on FND_HISTOGRAM_COLS table for JE_BE_LINE_TYPE_MAP table.
Because of this problem, FND_STATS tries to gather histogram information using wrong command and
it fails with ora-20001 errors.

The following SQL should return one row, not two:
SQL>select column_name, nvl(hsize,254) hsize
from FND_HISTOGRAM_COLS
where table_name = 'JE_BE_LINE_TYPE_MAP'
order by column_name;

COLUMN_NAME HSIZE
------------------------------ ----------
SOURCE 254
SOURCE 254

Since there are two rows in histograms table, FND_STATS creates following command to gather statistics on table 'JE_BE_LINE_TYPE_MAP' :
 dbms_Stats.gather_table_stats(OWNNAME => 'GL', TABNAME
=>'JE_BE_LINE_TYPE_MAP',METHOD_OPT=>'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SOURCE SIZE 254 FOR
COLUMNS SOURCE SIZE 254');
 Above command will work on 9i and 10G databases but it will fail with ora-20001 errors on 11G.

2) Column does not exist on the table but still listed in FND_HISTOGRAMS_COL table.
You can use following SQL to identify. SQL will prompt for table name, use table name from the errors. In above examples you can use FII_FIN_ITEM_HIERARCHIES.
select hc.table_name, hc.column_name
from FND_HISTOGRAM_COLS hc , dba_tab_columns tc
where hc.table_name ='&TABLE_NAME'
and hc.table_name= tc.table_name (+)
and hc.column_name = tc.column_name (+)
and tc.column_name is null;
SOLUTION

Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them logged in as the applsys user.
Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.
-- identify duplicate rows

select table_name, column_name, count(*)
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(*) > 1;
-- Use above results on the following SQL to delete duplicates

delete from FND_HISTOGRAM_COLS
where table_name = '&TABLE_NAME'
and  column_name = '&COLUMN_NAME'
and rownum=1;
-- Use following SQL to delete obsoleted rows

delete from FND_HISTOGRAM_COLS
where (table_name, column_name) in
  (
   select hc.table_name, hc.column_name
   from FND_HISTOGRAM_COLS hc , dba_tab_columns tc
   where hc.table_name  ='&TABLE_NAME'
   and hc.table_name= tc.table_name (+)
   and hc.column_name = tc.column_name (+)
   and tc.column_name is null
  );

commit;

Working on Custom.PLL in Oracle R12

1.    Enable show custom events: Log in to the Application Developer responsibility and enable Show Custom Events.Check the blog post for enabling custom events:

Custom events can be enabled through the below navigation:

Once you enable show custom events, oracle will display the name of each and every event that can be trapped using CUSTOM.pll. Using this technique, you can identify the custom event that you need to trap to achieve the desired result.

2.    Gather information: A message box will appear when this form is opened, indicating that the form name is FNDCPMPE, the block name is FND_EXECUTABLES, the field name is USER_EXECUTABLE_NAME, and the event name is WHEN-NEW-FORM-INSTANCE. Note down the names of these components as these will be required inprogramming CUSTOM.pll. To double-check the name of field, navigate to the Executable field and use the Help/Diagnostics/Examine menu to see the name.

3.    Write code in CUSTOM.pll: Open Oracle Forms Builder, highlight PL/SQL Libraries, and navigate from the menu to File | Open. Open the CUSTOM.pll file and expand the nodes. Within the node Attached Libraries, you will see FNDSQF and APPCORE2. Write your logic in a procedure called event, using the following sample syntax:

procedure event(event_name varchar2) is

form_name varchar2(30) := name_in('system.current_form');

block_name varchar2(30) := name_in('system.cursor_block');

begin

if form_name='FNDCPMPE' then

if event_name='WHEN-NEW-FORM-INSTANCE' then

app_item_property2.set_property('FND_EXECUTABLES.USER_EXECUTABLE_NAME',CASE_RESTRICTION, UPPERCASE);

end if;

end if;

end event;

OR you can even write your extensions in a package.event(event_name) and call this procedure in packagebody of custom.event.

After making the programming changes to CUSTOM.pll, scroll down to the bottom of the package body Custom and make these changes to the version history of CUSTOM.pll:

This code should appear as a sublisting under #3fdrcsid('$Header: CUSTOM.pld 120.1 2009/03/03 18:43:00 appldev ship $');

4.    Transfer CUSTOM.pll to $AU_TOP/resource: Log in to the forms server and change the directory to $AU_TOP/resource. Ensure you have a backup of CUSTOM.pll either in the source control system or on the file system. Next, transfer CUSTOM.pll from your desktop to $AU_TOP/resource.

5.    Generate CUSTOM.pll: Use the command frmcmp_batch, replacing the apps password with the relevant value on your system. After running this command, CUSTOM.plx will be created in $AU_TOP/resource:

cd $AU_TOP/resource

##For R12 use frmcmp_batch

frmcmp_batch module=CUSTOM.pll userid=apps/appspassword output_

file=./CUSTOM.plx compile_all=special module_type=LIBRARY

batch=yes

##For 11i Use f60gen command as shown below

f60gen module=CUSTOM.pll userid=apps/appspassword output_file=./

CUSTOM.plx module_type=LIBRARY

6.    Test your changes: Log out and log back in to the Concurrent Program Executable screen. You will notice that it is no longer possible to enter the concurrent program executable name using lowercase letters.

Item Attributes During Migration 11i to R12

When migrating from release 11i to release 12.1.x certain Item attributes "Attributes Control" should be set at Organization Level not at Master Organization Level. Below is the

list of attributes that it is recommended to have setup at the organization level if the master inventory organization is not "process enabled".

item_type
shelf_life_code
shelf_life_days
lot_control_code
auto_lot_alpha_prefix
start_auto_lot_number
location_control_code
eng_item_flag
lot_status_enabled
default_lot_status_id
dual_uom_control
secondary_uom_code
dual_uom_deviation_high
dual_uom_deviation_low
secondary_default_ind
tracking_quantity_ind
ont_pricing_qty_source
purchasing_item_flag
customer_order_flag
shippable_item_flag
internal_order_flag
invoiceable_item_flag
so_transactions_flag
taxable_flag
inventory_item_flag
inventory_asset_flag
costing_enabled_flag
stock_enabled_flag
build_in_wip_flag
mtl_transactions_enabled_flag
purchasing_enabled_flag
customer_order_enabled_flag
internal_order_enabled_flag
invoice_enabled_flag