Suresh : A Blog on Oracle Applications

Monday, February 13, 2017

Translate Special Characters using REGEXP_REPLACE

Translate Special Characters using REGEXP_REPLACE

select * from ar.hz_parties
where upper(trim(REGEXP_REPLACE(party_name ,'[^[:alnum:]'' '']', NULL))) = upper(trim(REGEXP_REPLACE(:party_name,'[^[:alnum:]'' '']', NULL)));

Monday, January 23, 2017

Spool query Oracle Sql

set echo off
set feedback off
set linesize 1000
set pagesize 0
set sqlprompt ''
set trimspool on
SET TERMOUT OFF
spool SFDC_ASSETS_ENRICH_DATA_PROD.csv
select 'ID' || ',',
'NAME'|| ',',
'ACCOUNT__C'|| ',',
'PARENT_ASSET__C'|| ',',
'PARENT_ASSET__NAME'|| ',',
'SERIAL_NUMBER__C'|| ',',
'PRODUCT__C'|| ',',
'PRODUCT__PRODUCTCODE'|| ',',
'SHIP_DATE__C'|| ',',
'SHIP_TO_CUSTOMER_DATE__C'|| ',',
'REVISION__C'|| ',',
'UPGRADEKEY__C'|| ',',
'OPERATINGSTATUS__C'|| ',',
'ASSETSYSTEM__C'|| ',',
'NOT_FOR_RESALE_ASSET__C'|| ',',
'NFR_START_DATE__C'|| ',',
'NFR_EXPIRATION_DATE__C'|| ',',
'NFR_SOLD_TO_END_CUST__C'|| ',',
'DATE_NFR_ASSET_SOLD_END_CUST_C'|| ',',
'STATUS__C'|| ',',
'ISACTIVE__C'|| ',',
'WARRANTY_ENTIT_ASSET_ID__C'|| ',',
'WARRANTY_ENTIT_ACCOUNTID'|| ',',
'WARRANTY_ENTIT_NAME'|| ',',
'PRODUCT_NAME'|| ',',
'CUSTWARRANTYSTARTDATE__C'|| ',',
'WAR_ENTIT_HW_DURATION_YRS_C'|| ',',
'WAR_ENTIT_WARRANTY_DURATION__C'|| ',',
'WAR_ENTIT_LIMITED_LFTIME_WAR_C'|| ',',
'PRODUCT_WARRANTY_NAME'|| ',',
'LIMITED_OR_LIFETIME_WAR__C'|| ',',
'PROD_WAR_DURATION__C'|| ',',
'PROD_WAR_HW_DURATION_YEARS__C'|| ',',
'ETS_ASSET_PARENT_ID'|| ',',
'UPGRADE_KEY'|| ',',
'MFG_PN'|| ',',
'HW_ASSET_CURRENT_LEVEL'|| ',',
'LICENSE_GENERATED_DATE'|| ',',
'SITE_ID_SFDC'
from dual;
select '"' ||ID || '",' ||
'"' ||NAME || '",' ||
'"' ||ACCOUNT__C || '",' ||
'"' ||PARENT_ASSET__C || '",' ||
'"' ||PARENT_ASSET__NAME || '",' ||
'"' ||SERIAL_NUMBER__C || '",' ||
'"' ||PRODUCT__C || '",' ||
'"' ||PRODUCT__PRODUCTCODE || '",' ||
'"' ||SHIP_DATE__C || '",' ||
'"' ||SHIP_TO_CUSTOMER_DATE__C || '",' ||
'"' ||REVISION__C || '",' ||
'"' ||UPGRADEKEY__C || '",' ||
'"' ||OPERATINGSTATUS__C || '",' ||
'"' ||ASSETSYSTEM__C || '",' ||
'"' ||NOT_FOR_RESALE_ASSET__C || '",' ||
'"' ||NFR_START_DATE__C || '",' ||
'"' ||NFR_EXPIRATION_DATE__C || '",' ||
'"' ||NFR_SOLD_TO_END_CUST__C || '",' ||
'"' ||DATE_NFR_ASSET_SOLD_END_CUST_C || '",' ||
'"' ||STATUS__C || '",' ||
'"' ||ISACTIVE__C || '",' ||
'"' ||WARRANTY_ENTIT_ASSET_ID__C || '",' ||
'"' ||WARRANTY_ENTIT_ACCOUNTID || '",' ||
'"' ||WARRANTY_ENTIT_NAME || '",' ||
'"' ||PRODUCT_NAME || '",' ||
'"' ||CUSTWARRANTYSTARTDATE__C || '",' ||
'"' ||WAR_ENTIT_HW_DURATION_YRS_C || '",' ||
'"' ||WAR_ENTIT_WARRANTY_DURATION__C || '",' ||
'"' ||WAR_ENTIT_LIMITED_LFTIME_WAR_C || '",' ||
'"' ||PRODUCT_WARRANTY_NAME || '",' ||
'"' ||LIMITED_OR_LIFETIME_WAR__C || '",' ||
'"' ||PROD_WAR_DURATION__C || '",' ||
'"' ||PROD_WAR_HW_DURATION_YEARS__C || '",' ||
'"' ||ETS_ASSET_PARENT_ID || '",' ||
'"' ||UPGRADE_KEY || '",' ||
'"' ||MFG_PN || '",' ||
'"' ||HW_ASSET_CURRENT_LEVEL || '",' ||
'"' ||LICENSE_GENERATED_DATE || '",' ||
'"' ||Site_Id_SFDC|| '"'
from tmp_sfdc_assetenrich_prod_mv;
spool off;
exit;

Sunday, October 9, 2016

Substr Instr to get first middle and last name

select :party_name,

TRIM(SUBSTR(:party_name, 1, INSTR(:party_name, ' ', -1,1))) fname,

SUBSTR(:party_name, INSTR(:party_name, ' ', -1)+1) lname

from dual;

/* Formatted on 7/10/2023 3:19:47 PM (QP5 v5.294) */

SET SERVEROUTPUT ON

DECLARE

--i number

l_count_line_id NUMBER;

l_party_name VARCHAR2 (100);

l_name VARCHAR2 (100);

BEGIN

l_count_line_id := 4;

l_party_name := '012,345,678,910';

DBMS_OUTPUT.put_line ('l_party_name : ' || l_party_name);

FOR i IN 1 .. l_count_line_id

LOOP

l_name :=

REGEXP_SUBSTR (l_party_name

, '[^,]+'

, 1

, i);

DBMS_OUTPUT.put_line ('l_name : ' || l_name);

END LOOP;

END;

Thursday, August 25, 2016

update and insert using for all bulk collect

DECLARE
CURSOR rec_cur IS
SELECT row_id, x_sfdc_account_id
FROM s_org_ext
where x_sfdc_account_id is not null
-- and row_id in ('1+12N+1177',
--'1+12N+1182',
--'1+12N+1185')
;

TYPE vc1_tab_t IS TABLE OF VARCHAR2(256);
TYPE vc2_tab_t IS TABLE OF VARCHAR2(256);

pk_tab vc1_tab_t;
-- fk_tab vc1_tab_t;
fill_tab VC2_TAB_T;
BEGIN
OPEN rec_cur;
LOOP
FETCH rec_cur BULK COLLECT INTO pk_tab, fill_tab LIMIT 1000;
EXIT WHEN pk_tab.COUNT() = 0;

FORALL i IN pk_tab.FIRST .. pk_tab.LAST
UPDATE md_parties
SET attribute_osr = fill_tab(i)
WHERE orig_system_reference = pk_tab(i)
and source_system =5;
commit;
END LOOP;
CLOSE rec_cur;
END;

DECLARE
CURSOR rec_cur IS
SELECT *
FROM md_party_asset_link_Bkp;

TYPE vc1_tab_t IS TABLE OF rec_cur%rowtype;

pk_tab vc1_tab_t;
-- fk_tab vc1_tab_t;
BEGIN
OPEN rec_cur;
LOOP
FETCH rec_cur BULK COLLECT INTO pk_tab LIMIT 100000;
EXIT WHEN pk_tab.COUNT() = 0;

FORALL i IN pk_tab.FIRST .. pk_tab.LAST
INSERT INTO md_party_asset_link
VALUES pk_tab (i);
commit;
END LOOP;
CLOSE rec_cur;
END;

/* Formatted on 9/13/2016 3:18:21 PM (QP5 v5.185.11230.41888) */
DECLARE
TYPE prod_tab IS TABLE OF md_party_asset_link_Bkp%ROWTYPE;

products_tab prod_tab := prod_tab ();
start_time NUMBER;
end_time NUMBER;
BEGIN
-- Populate a collection - 100000 rows
SELECT *
BULK COLLECT INTO products_tab
FROM md_party_asset_link_Bkp;

Start_time := DBMS_UTILITY.get_time;

FORALL i IN products_tab.FIRST .. products_tab.LAST
INSERT INTO md_party_asset_link
VALUES products_tab (i);

end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE ('Bulk Insert: ' || TO_CHAR (end_time - start_time));
COMMIT;
END;

Friday, September 11, 2015

tracking exceptions in oracle 10g ( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE)

How to log line number alongwith SQLCODE and SQLERRM?

How to find where an error was raised in PL/SQL

how to get the stack trace, package name and procedure name?

It has always been possible in PL/SQL to identify the source of an exception in a block of code; i.e. to know the precise point at which a block of code failed. However, until Oracle 10g, it has not been possible for developers to intervene in the exception in any way whilst retaining this information (for example to record the exception and its source in application logs). This has been the cause of many a frustration for developers.

Finally, with the release of 10g, Oracle has added provision for PL/SQL developers to trap AND log exceptions accurately for the first time. The DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function gives us the flexibility we have been demanding for years and the information that the DBMS_UTILITY.FORMAT_ERROR_STACK function simply didn't provide.

The following simple PL/SQL block demonstrates that Oracle will happily tell us where a procedure, function or anonymous block hit an exception.

SQL> BEGIN
  2     EXECUTE IMMEDIATE 'garbage';
  3  END;
  4  /

BEGIN
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 2

There are many PL/SQL developers who consider this to be adequate. They might, for example, take screen scrapes of their scheduling systems' output as application logs and be satisfied with the level of information demonstrated above. Or perhaps their front-end applications display the error stack as seen above.

Many systems, however, have a requirement to write application logs to files or tables. Therefore, to ensure that the exception is logged, the following "pseudo-approach" is taken by many developers (note that in the simple examples that follow I've substituted DBMS_OUTPUT.PUT_LINE for an application logging package).

SQL> BEGIN
  2     EXECUTE IMMEDIATE 'garbage';
  3  EXCEPTION
  4     WHEN OTHERS THEN
  5        DBMS_OUTPUT.PUT_LINE( SQLERRM );
  6        RAISE;
  7  END;
  8  /

ORA-00900: invalid SQL statement
BEGIN
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 6

The result is that the point in the code where the exception was raised moves to the explicit RAISE call, as can be seen at the end of the error stack above (highlighted in bold). The application logs would now record the fact that an ORA-00900 was raised, but in a scaled-up application, it wouldn't know which statement hit the exception. This can be a major problem. For example, I recently had to debug another developer's procedure, which contained 98 separate UPDATE statements and one of them "in the middle somewhere" failed with an invalid number exception. The only way to identify the actual statement was by removing the WHEN OTHERS so Oracle could tell me the correct line number.

As stated earlier, Oracle has supplied the DBMS_UTILITY.FORMAT_ERROR_STACK function for years, but this is of no use in solving this problem, as the following example demonstrates.

SQL> BEGIN
  2     EXECUTE IMMEDIATE 'garbage';
  3  EXCEPTION
  4     WHEN OTHERS THEN
  5        DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_STACK );
  6        RAISE;
  7  END;
  8  /

ORA-00900: invalid SQL statement

BEGIN
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 6

The DBMS_UTILITY.FORMAT_ERROR_STACK function in this instance provides no information over and above the SQLERRM function used in the previous example. The only difference is that the DBMS_UTILITY.FORMAT_ERROR_STACK function appends a line feed!

The long awaited Oracle enhancement to solve this problem was introduced in the first release of 10g. The DBMS_UTILITY.FORMAT_ERROR_BACKTRACE provides the error stack all the way back to source. In a simple example such as the following, the output is very simple and provides the accurate information we require.

SQL> BEGIN
  2     EXECUTE IMMEDIATE 'garbage';
  3  EXCEPTION
  4     WHEN OTHERS THEN
  5        DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
  6        RAISE;
  7  END;
  8  /

ORA-06512: at line 2

BEGIN
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 6

Note, however, that the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function does not supply the error message, just the error's propagation path. We therefore need to include a call to SQLERRM.

SQL> BEGIN
  2     EXECUTE IMMEDIATE 'garbage';
  3  EXCEPTION
  4     WHEN OTHERS THEN
  5        DBMS_OUTPUT.PUT_LINE( SQLERRM );
  6        DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
  7        RAISE;
  8  END;
  9  /

ORA-00900: invalid SQL statement
ORA-06512: at line 2

BEGIN
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 7

This example demonstrates that we now have sufficient information for our application logs, while the error stack generated from the RAISE call in line 7 can be discarded (it is included to send the necessary failure signal to the calling program / scheduler / shell).

As the nesting of exception blocks increases, so does the amount of information the new function provides, as the following example demonstrates (note that this time I have not re-raised the exception to keep the output clear).

SQL> CREATE PROCEDURE will_error AS
  2  BEGIN
  3     RAISE PROGRAM_ERROR;
  4  END;
  5  /

Procedure created.

SQL> BEGIN
  2     will_error();
  3  EXCEPTION
  4     WHEN OTHERS THEN
  5        DBMS_OUTPUT.PUT_LINE( SQLERRM );
  6        DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
  7  END;
  8  /

ORA-06501: PL/SQL: program error
ORA-06512: at "SCOTT.WILL_ERROR", line 3
ORA-06512: at line 2

At last, we now have a full propagation record of our exception, from its origin through to the outermost caller. Reading the stack from top to bottom, note that the exact points at which the exceptions were encountered are preserved. A major caveat to this is, of course, that if we go back to procedure WILL_ERROR and re-raise the exception in a WHEN OTHERS or such-like, we will once again lose the correct line.

Therefore, an important distinction needs to be made between application code that needs to be logged and that which doesn't. In my mind it is fairly clear that the various utility packages I include in my overall application will not handle unexpected exceptions in any way. These will be captured and logged by the business-rule packages that process data and need to write to application log files. These processing packages will each contain a call to the new DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function to enable them to log the precise origins and propagation path of an exception. At last!

Source from http://www.oracle-developer.net/display.php?id=318

Thursday, August 13, 2015

Oracle AR Outstanding Amount (Aging Report)

Customer outstanding amount.

The table ar_payment_Schedules_all gives you the outstanding information as on date. For eg. if the system date is 13th April and if you query the ar_payment_schedules_all table, the amount_due_remaining column will give you the open amount as on that date.
However if you want the oustanding as on some previous date, lets say as on 31st March, in that case you have to rollback all the applications that would have occured from 1st april to 13th april.

Find below the script that I used to get the oustanding as on any previous date. Pls. note that I am using a temp table here to populate the details.

CREATE TABLE ZXC_AGING_CUST1
(
CUSTOMER_ID NUMBER,
INVOICE_NUMBER VARCHAR2 (20),
INVOICE_DATE DATE,
GL_DATE DATE,
INVOICE_AMOUNT NUMBER,
TAX_AMOUNT NUMBER,
ACCTD_OUTSTANDING_AMOUNT NUMBER,
DUE_DATE DATE,
DAYS_OUTSTANDING NUMBER,
INSTALLMENT_NUMBER NUMBER,
DAYS_LATE_AS_OF NUMBER,
CURRENT_OS_AMT NUMBER,
CASH_RECEIPT_AMT NUMBER,
ADJ_AMT NUMBER,
CREDIT_MEMO_AMT NUMBER,
CREDIT_MEMO_AMT_1 NUMBER
);

CREATE TABLE ZXC_AGING_CUST2
(
CUSTOMER_ID NUMBER,
INVOICE_NUMBER VARCHAR2 (20),
TRX_TYPE VARCHAR2 (100),
ACCTD_OUTSTANDING_AMOUNT NUMBER,
GL_DATE DATE
);

DECLARE
v_cash_receipt NUMBER;
v_adjustment NUMBER;
v_credit_memo NUMBER;
v_as_of_outstanding NUMBER;
v_cash_receipt_acctd NUMBER;
v_adjustment_acctd NUMBER;
v_credit_memo_acctd NUMBER;
v_credit_memo_acctd_1 NUMBER;
v_as_of_outstanding_acctd NUMBER;
p_as_of_date DATE;

CURSOR cs_get_trx (
p_as_of_date1 IN DATE)
IS
SELECT ps.customer_id CUST_ACCOUNT_ID,
trx.creation_date INV_CREATION_DATE,
ps.trx_number INVOICE_NUMBER,
trx.trx_date INVOICE_DATE,
ps.gl_date GL_DATE,
NVL (ps.amount_due_original, 0) INVOICE_AMOUNT,
NVL (ps.tax_original, 0) TAX_AMOUNT,
NVL (ps.acctd_amount_due_remaining, 0) ACCTD_OUTSTANDING_AMOUNT,
ps.due_date,
CEIL (SYSDATE - ps.due_date) DAYS_OUTSTANDING,
ps.payment_schedule_id,
ps.number_of_due_dates INSTALLMENT_NUMBER,
trx.customer_trx_id,
CEIL (p_as_of_date1 - ps.due_date) DAYS_LATE_AS_OF
FROM ra_customer_trx TRX, ar_payment_schedules PS
WHERE trx.customer_trx_id = ps.customer_trx_id
AND ps.gl_date <= p_as_of_date1
AND ps.gl_date_closed > p_as_of_date1;

CURSOR cs_get_receipt (
p_as_of_date2 IN DATE)
IS
SELECT ps.customer_id CUST_ACCOUNT_ID,
ps.payment_schedule_id,
CEIL (p_as_of_date - ps.GL_DATE) days_late_as_of_r,
ps.gl_date,
cr.receipt_number,
app.cash_receipt_id,
SUM (app.acctd_amount_applied_from) ACCTD_AMOUNT_APPLIED
FROM ar_receivable_applications app,
ar_cash_receipts cr,
ar_payment_schedules ps
WHERE app.cash_receipt_id = cr.cash_receipt_id
AND app.payment_schedule_id = ps.payment_schedule_id
AND app.status IN ('ACC', 'UNAPP', 'UNID', 'OTHER ACC')
AND NVL (app.confirmed_flag, 'Y') = 'Y'
AND app.gl_date <= p_as_of_date2
AND ps.gl_date <= p_as_of_date2
AND ps.gl_date_closed > p_as_of_date2
AND ( ( app.reversal_gl_date IS NOT NULL
AND ps.gl_date <= p_as_of_date2)
OR app.reversal_gl_date IS NULL)
GROUP BY ps.customer_id,
cr.receipt_number,
app.cash_receipt_id,
ps.payment_schedule_id,
ps.gl_date
HAVING SUM (app.acctd_amount_applied_from) <> 0;
BEGIN
BEGIN
mo_global.set_policy_context ('S', '69');
END;

DELETE zxc_aging_cust1;

p_as_of_date := TO_DATE ('&Enter_as_of_date', 'DD-MON-RRRR');

FOR invoice IN cs_get_trx (p_as_of_date)
LOOP
/* cash applied after p_as_of_date */

SELECT NVL (
SUM (
NVL (acctd_amount_applied_to, 0.0)
+ NVL (acctd_earned_discount_taken, 0.0)
+ NVL (acctd_unearned_discount_taken, 0.0)),
0.0)
INTO v_cash_receipt_acctd
FROM ar_receivable_applications
WHERE TRUNC (gl_date) > p_as_of_date
AND status || '' = 'APP'
AND NVL (confirmed_flag, 'Y') = 'Y'
AND applied_payment_schedule_id = invoice.payment_schedule_id
AND application_type LIKE 'CASH%';

/* adjustments applied after p_as_of_date */

SELECT NVL (SUM (ar_adjustments.acctd_amount), 0.0)
INTO v_adjustment_acctd
FROM ar_adjustments
WHERE TRUNC (gl_date) > p_as_of_date
AND status = 'A'
AND payment_schedule_id = invoice.payment_schedule_id;

/* invoice credited after p_as_of_date */

SELECT NVL (SUM (NVL (acctd_amount_applied_to, 0.0)), 0.0)
INTO v_credit_memo_acctd
FROM ar_receivable_applications
WHERE applied_payment_schedule_id = invoice.payment_schedule_id
AND NVL (confirmed_flag, 'Y') = 'Y'
AND status || '' = 'APP'
AND TRUNC (gl_date) > p_as_of_date
AND application_type LIKE 'CM%';

/* credit memo applied after p_as_of_date */
SELECT NVL (SUM (NVL (acctd_amount_applied_to, 0.0)), 0.0)
INTO v_credit_memo_acctd_1
FROM ar_receivable_applications
WHERE payment_schedule_id = invoice.payment_schedule_id
AND NVL (confirmed_flag, 'Y') = 'Y'
AND status || '' = 'APP'
AND TRUNC (gl_date) > p_as_of_date
AND application_type LIKE 'CM%';

/* calculate actual outstanding amount */

v_as_of_outstanding_acctd :=
invoice.acctd_outstanding_amount
+ v_cash_receipt_acctd
- v_adjustment_acctd
+ v_credit_memo_acctd
- v_credit_memo_acctd_1;

INSERT INTO zxc_aging_cust1 (customer_id,
invoice_number,
invoice_date,
gl_date,
invoice_amount,
tax_amount,
acctd_outstanding_amount,
due_date,
days_outstanding,
installment_number,
days_late_as_of,
current_os_amt,
cash_receipt_amt,
adj_amt,
credit_memo_amt,
credit_memo_amt_1)
VALUES (invoice.cust_account_id,
invoice.invoice_number,
invoice.invoice_date,
invoice.gl_date,
invoice.invoice_amount,
invoice.tax_amount,
v_as_of_outstanding_acctd,
invoice.due_date,
invoice.days_outstanding,
invoice.installment_number,
invoice.days_late_as_of,
invoice.acctd_outstanding_amount,
v_cash_receipt_acctd,
v_adjustment_acctd,
v_credit_memo_acctd,
v_credit_memo_acctd_1);
END LOOP;

COMMIT;

FOR receipt IN cs_get_receipt (p_as_of_date)
LOOP
INSERT INTO zxc_aging_cust2 (customer_id,
invoice_number,
trx_type,
acctd_outstanding_amount,
gl_date)
VALUES (receipt.cust_account_id,
receipt.receipt_number,
'RECEIPT',
-1 * receipt.acctd_amount_applied,
receipt.gl_date);
END LOOP;

COMMIT;
END;

Wednesday, August 5, 2015

Oracle Report Manager Release Notes, Release 12.0.6 (Note 741767.1)
This document provides delta ination from Release Update Pack 4 (.ATG_PF.A.DELTA.4) to Oracle Report Manager Release Update Pack 6 (R12.ATG_PF.A.DELTA.6).

General Ledger Setup:

· 749971.1 : R12: How To Remove Balancing Segment Value Assignments From A Ledger

· 744962.1 : R12: How To Generate Reporting Sequence?

· 756230.1 : R12: Define Security Rules For Segment Company

· 753574.1 : How To Find Flex Structures Which Use The Same Flex_value_sets Twice

· 753232.1 : Cannot assign ledgers to multiple ledger sets - APP-FND-01206 error

· 752682.1 : After Upgrade To R12 Upgraded Translation Rate Types Cannot Be Updated

· 752013.1 : Impact of Chart of Accounts Segments On Performance

· 736255.1 : What Subledgers Are Supported For Third Party Control Accounts ?

· 746556.1 : R12: How To Map Accounts For Manual Journals To The Secondary Ledger

· 744196.1 : R12:Unable To Configure Additional Intercompany Rules

General Ledger Journals/Posting/Journal Import:

· 736299.1 : How Are Users Restricted From Posting Journals Across Ledgers?

· 752978.1 : Enter Journal In GL Is Failing At Category With Error: ORA-06512 : in ''.GL_AUTOREVERSE_OPTIONS_PKG''.

· 752178.1 : What patch provides the posted_by column enhancement to the GL_JE_BATCHES table?

· 752016.1 : EA01 - You do not have read and write access to all of the journals in the batch

· 752012.1 : Unable To View Journal From Journal Approval Notification-ORA-10402 No Data Found

· 751679.1 : Is it Possible to Correct a Journal via the Journal Import Correct Screen Without Unfreezing the Source?

· 751841.1 : Recurring Journals (GLPRJE) Errors out with PRJE0008

· 750129.1 : Income Statement Closing Journals Dont Close All Rev/Exp Accounts

· 751816.1 : Imported Journals Error With EC08, EC03 From Journal Wizard With Currency In Lower Case

· 729256.1 : R12: PPOS0002: Posting Error, COA Mapping Invalid or Disabled Error

· 749239.1 : R12 Journal Wizard: Budget LOV Returns No Values For Budget Journal

· 728303.1 : R12: Oracle Error 20001 In Fdpstp When Running Journal Autocopy

· 729312.1 : Unable to Edit Journal From Correct Journal Import Form, When Source is Not Frozen

· 747594.1 : R12: Journal Amount ($) Not Displaying On Journal Approval Email Notifications

· 735347.1 : Autocopy Journal Results in Two Copies of the Reporting Currency Journal Being Created

· 747501.1 : R12: Journal Wizard Throws ''This Application Function Is Not Configured To Any Integrator''

1. Confirm the correct settings for this profile option, logged in as sysadmin: GL : Default Desktop Viewer - this needs to point to your correct excel version

2. If this works for some responsibilities but does not work for some others, please compare them as explained below:
2.1. For the responsibility for which this is working, query the Menu from sysadmin--application--menu
2.2. Check the function name defined for the journal wizard (Launch Journal Wizard)
2.3. Go to sysadmin--application--function and query the same function name, take screenshots of the various tabs - Description, Properties, Form and Web HTML
2.4. Now for the responsibility for which this is not working, again query the menu from sysadmin.
2.5. Check the function name defined for the journal wizard
Note- if the problematic responsibilities use customized menus, make sure you added the correct function and not a similar one for Journal Wizard (eg. GL_ADI_JOURNAL_WIZARD_LAUNCH against GL_ADI_JOURNAL_WIZARD_LAUNCH_G)
2.6. Query the function name defined and check that the values in the Description, Properties, Form and Web HTML tabs are the same as that in the function for the working responsibility.
2.7. If the correct function is not added, add it and retest the issue.

3. If this does not work for any responsibility and it never did, again follow the steps above and add the Launch Journal Wizard function manually.
Make sure you have attached GL_SUPERUSER menu to the responsibility (seeded) or if a custom menu is involved, follow the steps above and add the missing function by comparing to the seeded GL_SUPERUSER menu.

4. Make sure you did not add GL_SUPERUSER_G menu instead of GL_SUPERUSER without intending so.
If you are not a Public Sector customer, you might reconsider and use the seeded GL_SUPERUSER.
If you need to use GL_SUPERUSER_G menu please check Note 453557.1 'Menu "GL_SUPERUSER" has ASM feature, but "GL_SUPERUSER_G" menu does not.' as you might have run into BUG 6207483 ' R12 SIP THE DIFFERENCE BETWEEN WITH _G MENUS AND WITHOUT _G MENUS'.

· 746560.1 : GLPPOSS Hanging On INSERT INTO GL_ACCOUNT_HIERARCHIES

· 552209.1 : R12: ''Journal Import Created'' Shown On Journal Description Line Instead Of Detailed Subledger Information

· 744405.1 : R12 How Can Budget Journal Records Be Removed From GL_BC_PACKETS ?

· 742727.1 : R12: Posting Errors With ORA-01400: cannot insert NULL into ''GL''.''GL_BC_PACKETS_HISTS''

· 727434.1 : R12-Generating Error FRM-41017 When Reviewing Budget Journals

· 743516.1 : R12: Posting Intracompany Journal throws ''PPOS0261: The following journals post to balancing segment values which are not mapped to legal entities''

· 742891.1 : R12:Intercompany Segment Not ally Populated By Intercompany Balancing Rules

· 742383.1 : R12: Sequencing/Document Numbering Not Working for SLA Create Accounting Process Created Journals

· 742170.1 : Journal Reversal Criteria Is Not Working For Imported Journals

· 741946.1 : R12 Cannot Change Period On Unposted Reversal Journals

General Ledger Miscellaneous:

· 756311.1 : R12 Budget Mass allocations for Multiple Periods Completes with Error: ORA-00904: SV0 CHILD_FLEX_VALUE Invalid Identifier

· 756160.1 : Upgrading From R11 to R12 Fails Glrstinc.Sql - ORA-00001 GL_SUMMARY_HIERARCHIES_U1 Violated

· 755680.1 : GLRXVCJ Journals - Voucher: OPP states: Extension function error: Error invoking ''regroup''

· 755116.1 : Autocopy Should Not Copy The Reporting Currency Journal - It Results In Duplicate Journals In Reporting Ledger

· 754354.1 : R12 General Ledger Account Balances Across Ledgers Report (GLRGCBGT) Errors with REP-0069, REP-57054, ORA-06512: at APPS.GL_ACCESS_SETS_PKG

· 753562.1 : GLAVAL Validate MassAllocations Terminated With Signal 11

· 558518.1 : What is the latest General Ledger performance patch for the summarization programs for 11i?

· 751698.1 : Daily Rates Import And Calculation Process Errors with Duplicate Row or Overlapping Date Range Found

· 746311.1 : R12: GLCRVL Revaluation Throws ''CRVL0005: Unable to create unrealized gain/loss accounts''

· 742025.1 : Performance Issue With Translation Program After Upgrading To 10G Database

· 752173.1 : R12: The Upload option in the Budget Integrator menu disappears

· 751714.1 : Translated Subsidiary Balance Does Not Match Consolidated Balance -Why?

· 751072.1 : How To Find Whether A Message Is From A Security Rule Or A Cross Validation Rule

· 750917.1 : R12: GLOOAP: OOAP0017: Please Define The Ledger Initial Period

· 750339.1 : R12: Running Account Inquiry When Data Access Set is Defined as Read Only Causes Unexpected Error

· 731221.1 : R12: Unable to Drill Down from Ledger to Subledger Payables with Legal Entity Security Rule

· 748624.1 : Funds Transfer Makes Funds Available Before Approval

· 747901.1 : GLCheckSetup DIAG - UNABLE TO CONVERT PERIOD START OR END DATE TO DATE FORMAT

· 747460.1 : Mass Allocation Is Creating Large Amount Under Rounding Adjustment

· 746347.1 : AUTOMATIC RECONCILIATION NOT WORKING WHEN SUM DEBIT SUM CREDIT = 0

· 746221.1 : What Is The Impact In The Period Closing If Multiple Operating Units Share Same Ledger

· 744646.1 : In Release 12 What Are The Budgetary Control Funds Checking STATUS_CODE Values ?

· 744147.1 : Journal Reconciliation form (GLXRCENT) Does Not Open Review Journal When Pressing The Button

· 741050.1 : GLXACSDW Account Analysis Subledger Details Errors For One Account With REP-1401

· 744147.1 : Journal Reconciliation form (GLXRCENT) Does Not Open Review Journal When Pressing The Button

General Ledger FSG:

· 550281.1 : R12: FSG with CPOI Token Always Shows First Calendar Period Defined for the Ledger in the Report Output

· 752756.1 : FSG Report Fails With Error Message Name: RG_COMP_NONEXISTANT

· 751675.1 : FSG DOESN''T DISPLAY LEDGER CURRENCY AMOUNTS

· 750417.1 : R12: FSG Variance And Variance% Amounts Are Not Calculated

· 735024.1 : R12: How To Restrict Access to FSG Parameters Using Definition Access Set?

· 747546.1 : Clicking the Run Report button on the Define Report form(RGXGDREP) fails due to Ora-01403: No Data Found Frm-40735: When-Button-Pressed Trigger Raised

· 742667.1 : R12 When Run FSG Using Ledger Set Report Errors With RG_RARG0005 But Report Runs Ok If Run For A Ledger

· 745550.1 : FSG: PTD-Variance Incorrect Report with Multiple Currencies and Sets of Books

· 744514.1 : Are XBRL Taxonomies Provided and Are Any Patches Required for XBRL for R12?

· 742420.1 : Message Not Found. Application: Sqlgl, Message Name: Gl_fsg_drl_err_error

· 740460.1 : Generating Report For Xml Output Using Report manager Returns Signal 11

· 738834.1 : FSG report run with The Output Type Set To - Get A Signal 11 Error

Oracle Internal Controls Manager:

· 736876.1 : We Are Not Able To Run And Review Violations For Concurrent Program Constraints

· 374461.1 : OICM: Where Do You Assign Process Owner To a Process?

· 567768.1 : Need to Link Latest Revision of Control to the Audit Procedure

· 382833.1 : How To Remove Processes from the Risk Library?

· 417515.1 : Cannot Access Findings And Remediation Tab Pages

· 362224.1 : OICM: Where are Constraints Sets Defined For Oracle Internal Controls Manager?

· 752022.1 : Segregation Of Duties Creation (Preventative) Problem

· 747034.1 : Check For Constraint Violations Check Permissions Assigned To Roles?

· 745263.1 : Child Process Risk and Control Count Not updated on Risk Library when the Parent Process is Approved

· 745159.1 : Role And Responsibility Not Appearing On Sod Constraint Report

· 736876.1 : We Are Not Able To Run And Review Violations For Concurrent Program Constraints

· 726317.1 : Not Able to View Constraint Violations Report as PDF - Not a Supported File Type error

· 742662.1 : R12: Changes in Configurations Not Being Reflected In The ITA Module

· 742224.1 : Organization Sub-Process Removal From Parent Process Not Working

· 735551.1 : No Button Available To Assign Roles To Organizations

· 734226.1 : OICM: Update Opinion Button Not Activated After Clicking Update Button

ADI/ADI/Report Manager:

· 726958.1 : List of Patches Released in Report Manager for Releases 11i and 12

· 726989.1 : List of Patches in Web Applications Desktop Integrator (Web ADI) for Releases 11i and 12

· 751948.1 : Unable To View Published Large FSG Reports - It Times Out

· 744855.1 : R12 While Opening The Large Fsg Report receive error: You have encountered an unexpected error

· 745061.1 : Unable To View Large FSG FRM Opens Blank Excel

· 746541.1 : R12: Is It Possible To Merge Report Manager One-Off Patches

· 749653.1 : Report manager - Drilldown in Excel hangs; works in HTML and PDF

· 750134.1 : System Errors When Creating A Menu Item From Reports Repository

· 751166.1 : FRM:Template Editor Does Not Generate Properly when Line Number have Decimals

· 739398.1 : Unable To Publish A Report In Excel With Report Manager

· 731281.1 : R12:Publishing FSG Report In Excel Results In 'You Have Insufficient Privileges'

· 740822.1 : R12: Calculation By Using Formulas Are Not Working On Web ADI Integrators

· 742740.1 : Exception During Parsing Of Upload Document-Budget Upload

· 556465.1 : Connection To The Server Error Msg When Using Web Adi journal upload

· 742740.1 : Exception During Parsing Of Upload Document-Budget Upload

· 751676.1 : How Can A User Refresh Data In A Previously Submitted Fsg?

· 751121.1 : Can a Custom Viewer Be Used With Report Manager?

· 747105.1 : Report Manager Does Not Drill Down From Excel, Multiple Tabs, When Expansion Value = All

· 746204.1 : R12 Error Generated Excel Output Due To Use Of Double Underline In Row Set

· 733140.1 : FSG Drilldown Throws GL_FSG_DRL_IT_CONCISE_MSG And Shows A Different Account Than The One Selected For Drill

· 564264.1 : Implementing WEBADI Budget Wizard Solution for 11i

· 735943.1 : R12: Unable To Insert Rows On WebADI Budget Template-Spreadsheet Unprotected

Advanced Global Intercompany System:

· 726980.1 : R12: How to Prevent Users From Entering AGIS Transactions in Closed GL Periods

· 752004.1 : Import Intercompany Transactions Missing Transaction Type

· 749620.1 : Is there a way to delete the data that exists in the FUN_INTERFACE_BATCHES tables?

· 749150.1 : R12 AGIS: Reject Inbound: Error Message: Complete The Accounting Distribution For The Recipient.

· 747537.1 : GLXIEENT Receiver Unable To Query Intercompany Transaction

· 743904.1 : AGIS Unable To Create Outbound Transactions due to ''unknown runtime error occurred'' from Javascript

· 742701.1 : AGIS Transaction Import fails with Invalid Balancing Segment Value