How to diagnose the performance issue in oracle apps R12.

1.  Trace - We run the trace based on particular activity. We require to generate trace based on particular activity like concurrent report, or form which is leading to time consumption. Trace can be run at the below level

- Trace can be run at respective Form Level

- Trace need to be enabled at Concurrent Define window.

- Trace fetching based on Request ID

Once trace file is generated. Reveiw the trace file to find out at which level there performance issue for a Particular SQL ID as is mentioned below.
                            ·         Parsing
                            ·         Fetching
                            ·         Execution

2. AWR Report: AWR Report will be run every 1 hour as scheduled by DBA team. For this report we will be able to find out Top 10 SQL ID which is causing performance issue. Based on SQL ID, we will generated

3. SQLT Report:  For particular SQL ID we have to generate SQLT Report.  Which will help us to identify the number indexes created on particular table or Query which is causing for SQL Execution issue.

Oracle Enterprise Management (OEM)

From Oracle Enterprise Management (OEM) window we will be able to find the load on the server. As per the graph below, Blue indicates high load on the server.

`

Blocking Queries:

select to_char(a.LOGON_TIME,'DDMonYY HH24:MM'),a.sid, a.serial#,c.NAME, a.STATUS,a.ACTION from V$SESSION a ,DBA_BLOCKERS b,AUDIT_ACTIONS c

where a.sid = b.holding_session and a.command = c.action;

select /*+ rule */ 'SID '||a.sid||' is blocking the sessions '||b.sid from v$lock a, v$lock b where a.block=1 and b.request >0;

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status, b.osuser, b.machine

                 FROM  v$locked_object a, v$session b,  dba_objects c

                 WHERE b.SID = a.session_id AND a.object_id = c.object_id  AND b.sid in ( select holding_session from DBA_BLOCKERS ) ;

  

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status,b.osuser, b.machine FROM  v$locked_object a, v$session b,  dba_objects c

WHERE b.SID = a.session_id AND a.object_id = c.object_id AND b.sid in ( select holding_session from DBA_BLOCKERS ) ;

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status, b.osuser, b.machine FROM  v$locked_object a, v$session b,  dba_objects c

WHERE b.SID = a.session_id AND a.object_id = c.object_id  AND b.sid in ( select holding_session from DBA_BLOCKERS ) ;

SELECT session_id  FROM DBA_LOCKS WHERE BLOCKING_OTHERS = 'Blocking';

select * from DBA_WAITERS;

select * from dba_blockers;

select * from v$lock where block=1;

SELECT DECODE (request, 0, 'Holder: ', 'Waiter: ') || sid sess,inst_id,id1, id2,lmode,request, TYPE

FROM gv$LOCK  WHERE (id1, id2, TYPE) IN (SELECT id1, id2, TYPE FROM gv$LOCK WHERE request > 0)

ORDER BY id1, request;

SELECT 'alter system kill session ''' || s.sid || ',' || s.SERIAL# || ','||'@'||''|| i.instance_number || ''';' a,'ps -ef |grep LOCAL=NO|grep ' || p.SPID SPID,

'kill -9 ' || p.SPID,s.status FROM gv$session s, gv$process p,gv$instance i WHERE ( (p.addr(+) = s.paddr) AND (p.inst_id(+) = s.inst_id)) AND s.sid = &sid;

SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE

|| ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING '

|| S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) OBJ_ID:' ||L1.ID1||' OBJ_NAME:'||O.OBJECT_NAME

AS BLOCKING_STATUS

FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2,DBA_OBJECTS O

WHERE S1.SID=L1.SID AND S2.SID=L2.SID

AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID

AND L1.ID1=OBJECT_ID

AND L1.ID1=O.OBJECT_ID

AND L1.BLOCK > 0 AND L2.REQUEST > 0

AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2;

    SELECT

   l1.sid || ' is blocking ' || l2.sid blocking_sessions

FROM

   gv$lock l1, gv$lock l2

WHERE

   l1.block = 1 AND

   l2.request > 0 AND

   l1.id1 = l2.id1 AND

   l1.id2 = l2.id2;

Report Used to reconcile FA module to GL

Report in FA Module.

1. Cost Summary Report
2. Journal Reserve Report.

Report in GL Module.

1. Journal Extended Report

Incase of data migaration point to be remebered for Fixed Asset Data Upload

Note: Any value entered in Accumulated depreciation Field system will calcualted depreciation for Current Period only. If no value entered in depreciation Field system will calculate depreciation from beginning of the asset.

Case Study 1: Create a new asset, With Accumulated Depreciation(let's say X)  and YTD Depreciation.    

Run Depreciation to Calculate current Method Depreciation. In Accumulated Deprecation , system will calculated current method Depreciation
and add to accumulated deprecation as entered while creating asset(X Value + current Period Depreciation)
while running create Accounting. System will Generated accounting
Entry for Asset addition and Current month Depreciation but no accounting entry will be created for accumulated Depreciation entered while Creating Asset..
If Accumulated Depreciation is entered, System will calculated Depreciation for current Period only.

Illustration:

Asset Cost: 10000
Accumulated Depreciation: 500
Date Place in Service : 01-JAN-2013
Depreciation Method Life: 40 Yrs.   (480 Months)
After Running Depreciation for MAR-16
Deprecation calculate for the period= Asset Cost/ Life in months=  10000/480= 20.83
Now
Accumulated Depreciation will be 500+ 20.83=   520.83.

Accounting entry will be generated for Asset Addition as below

Asset Cost A/c............Dr   10000
      To Asset Clearing A/c.......................Cr   10000

Accounting entry will be generated for Depreciation as below

Depreciation A/c..............Dr   20.83
                   To Accumulated Depreciation A/c....20.83
Note: No Accounting will be generated for Accumulated Deprecation entered before running Depreciation.

Case Study 2: Create a new asset, With Accumulated Depreciation as null and YTD Depreciation as null and when you save record. System will create YTD Deprecation and
accumulated Depreciation till Previous Period.
Run Depreciation to Calculate current Method Depreciation.   
while running create Accounting. System will Generated accounting
Entry for Asset addition and Current month Depreciation but no accounting entry will be created for accumulated Depreciation.
If Accumulated Depreciation is entered, System will calculated Depreciation for currrent Period only.

Illustration:

Asset Cost: 10000
Accumulated Depreciation:  NULL
YTD Depreciation: NULL
Date Place in Service : 01-JAN-2013
Depreciation Method Life: 40 Yrs.
after Running Depreciation for MAR-16
Deprecation calculate from Date place in Service: 20.83 (Dep per Month)* 39(Till Current Month)= 812.5
Now
Accumulated Depreciation will be = 812.5
YTD Depreciation will be=812.5.


Accounting entry will be generated for Asset Addition as below

Asset Cost A/c............Dr   10000
      To Asset Clearing A/c.......................Cr   10000

Accounting entry will be generated for Depreciation as below

Depreciation A/c..............Dr   812.5
                   To Accumulated Depreciation A/c....812.5
Note: Depreciation Accounting will be generated from Date place in service to till Date

Not able to update the TAX Calendar in TDS Regime. Error Appears - "FRM-40654 : Record has been updated. Requery block to see change"

UPDATE jai_regimes
      SET creation_date = TRUNC(creation_date) ,
             last_update_date = TRUNC(last_update_date),
             effective_from = TRUNC(effective_from)
WHERE REGIME_NAME = 'TDS'
/
COMMIT;

Error while Incomplete AR Invoice, APP-AR-11526: ENCOUNTERED AN ERROR IN JAI_AR_TAX_DEP_PKG. dETERMINE_MANUAL_ar_tax

Download & Apply the following Patch: 26410637:R12.JAI.B and Test it.

How to set the customer number in Oracle apps / Customer Number Sequence updation in AR

Note: Refer Doc ID 265315.1)

Switch to the Application Developer responsibility,
In the Navigator window select Application, then Database, and finally Sequence.

To automatically assign a unique number to every new customer, check the Automatic Customer Numbering box. Do not check this box if you want to manually assign customer numbers. Oracle Applications system administrator or developer can specify the initial number to be used for Automatic Customer Numbering.

Switch to the Application Developer responsibility.

In the Navigator window select Application, then Database, and finally Sequence.

In the Name field of the Sequences window, query for HZ_ACCOUNT_NUM_S.

In the Start Value field of the Sequences window, enter the initial number to be used for Automatic Customer Numbering.

You were previously entering customer numbers manually. Now you want to generate the number automatically but you want to specify the value the sequence should start from. How can this be done? The database sequence that handles the creation of customer numbers is defined in HZ_ACCOUNT_NUM_S.

Care should be taken to ensure that you do not define a number which will cause duplicate customer numbers to exist in your system. you can run the following to identify the highest number currently in use within your system: Select max(account_number) from hz_cust_accounts_all; Then using the number returned above, define the sequence to start with a higher value. If for example the above returns 20000, then you can do the following to ensure the sequence will not create overlapping account numbers:

DROP SEQUENCE AR.HZ_ACCOUNT_NUM_S;

CREATE SEQUENCE AR.HZ_ACCOUNT_NUM_S START WITH 20000 INCREMENT BY 1 MAXVALUE 999999 CACHE 20;

Auto Invoicing Setup Steps

1) In OM System Parameters Window, For item Validation Organisation assign Master Inventory(MI) or Actual Inventory (AI).

2) Create Transaction Type for line and order.
3) Define Document Sequence for the transaction type.
4) Assign Document Sequence and Document Category in Document Assignment window
5) For OM Responsibility assign " QP Item Validation Organisation".
6) Assign you item in advance pricing form.
7) Define Carrier Method.
8) Define Release Sequence Rule.
9) Define Pick Slip Grouping Rule.
10) Define Release Rules.
11) Define Document Set for Pick Release.
12) Define Document Set for Ship Confirmation.
13) Define Ship Confirmation Rules.
14) Shipping Parameters.
15) Define Picker Role.
16) Define Shipper Role.
17) Define grants to shipper and picker
18) Open Periods in Inventory, Purchasing and General Ledger Modules.
19) Create Receivables Transaction Type
20) Create Imported Transaction Source
21) Assign Receivable transaction type and sources to Transaction type create in OM.

You have encountered an unexpected error. Please contact the System Administrator for assistance. Unable to Open Supplier OAF Page

Solution: Need to Set MO: Operating Unit at Site Level(Specify your Operating Unit)

Unable to make payment for the Invoice. Due to Invoice Status: 'Selected for Payment'. But no Payment Batches created by selecting this invoice.

Run the below SQL Query. The invoice Status will be changed automatically to "Validated"

update ap.ap_payment_schedules_all 
set checkrun_id =''
where invoice_id ='57553'-- Give your Invoice_ID
and checkrun_id ='34564'--- Give your Checkrun_ID

Now you cancel the invoice or You can make the Payment for the invoice.

AP invoice accounting status not updated to ‘yes’ still it is showing ‘no’ for one invoice

Analysis: We are able to see JE for invoice when we checked view accounting from invoice screen, so the problem with only status

Solution: As it seems data corruption issues, we logged a ‘SR’ with oracle, they have provided data fix. The issue has been resolved.