Search This Blog

Monday, August 26, 2013

IBY_DISBURSE_SUBMIT_PUB_PKG.deriveExactPayeeIdFromContext:Fatal: Exception when attempting to perform exact match for given payee context

Payable Open Interface Import Issue:

The issue is related to Payable Open Interface Import.
While loading invoices, we found that Payable Open Interface has fetched the invoices but those invoices are not found in the Rejection Table AP_INTERFACE_REJECTIONS.

While searching the log file, we found that those invoices has failed in to "Derive Party ID Validation".
On the Debug Switch and we will find that invoices failed in Exception:

Check Invoice Validation 17:
IBY_DISBURSE_SUBMIT_PUB_PKG.deriveExactPayeeIdFromContext:Fatal: Exception when attempting to perform exact match for given payee context.6
IBY_DISBURSE_SUBMIT_PUB_PKG.deriveExactPayeeIdFromContext:SQL code: -14226
IBY_DISBURSE_SUBMIT_PUB_PKG.deriveExactPayeeIdFromContext:SQL err msg: ORA-01422: exact fetch returns more than requested number of rows6
IBY_DISBURSE_SUBMIT_PUB_PKG.deriveExactPayeeIdFromContext:EXIT 

We raised the SR with Oracle and found there is data fix required for the issue.
No RCA has provided by Oracle on the Issue.

Solution:
Data Fix Patch
13857555 - RCA: ISSUES WITH DUPLICATE PAYEE DELETION GDF: 10140168
Post patch - Run the script brought in by the Data fix patch.
1.      Run the script $IBY_TOP/patch/115/sql/iby_dup_payee_sel.sql 
2.      log file 10140168-diag-.html which shows affected transactions.
3.      Check the report 10140168-diag-.html to identify/review the affected transactions. 
4.      Run $IBY_TOP/patch/115/sql/iby_dup_payee_fix.sql to fix the duplicate records.
5.      This will generate log file 10140168-fix-.html.This log will also list the backup table names

6.      Verify.
a) Check the results. Run the following query and verify that no records are returned.

SELECT
A.EXT_PAYEE_ID , 
A.PAYEE_PARTY_ID , 
A.PAYMENT_FUNCTION , 
A.EXCLUSIVE_PAYMENT_FLAG ,
A.PARTY_SITE_ID ,
A.SUPPLIER_SITE_ID ,
A.ORG_ID ,
A.ORG_TYPE ,
A.DEFAULT_PAYMENT_METHOD_CODE ,
A.ECE_TP_LOCATION_CODE ,
A.BANK_CHARGE_BEARER ,
A.BANK_INSTRUCTION1_CODE ,
A.BANK_INSTRUCTION2_CODE ,
A.BANK_INSTRUCTION_DETAILS ,
A.PAYMENT_REASON_CODE ,
A.PAYMENT_REASON_COMMENTS ,
A.INACTIVE_DATE ,
A.PAYMENT_TEXT_MESSAGE1 ,
A.PAYMENT_TEXT_MESSAGE2 ,
A.PAYMENT_TEXT_MESSAGE3 ,
A.DELIVERY_CHANNEL_CODE ,
A.PAYMENT_FORMAT_CODE ,
A.SETTLEMENT_PRIORITY ,
A.REMIT_ADVICE_DELIVERY_METHOD ,
A.REMIT_ADVICE_EMAIL ,
A.REMIT_ADVICE_FAX 
FROM iby_external_payees_all a
WHERE EXISTS (SELECT 'duplicates' 
                                FROM iby_external_payees_all b 
                                WHERE a.payee_party_id = b.payee_party_id 
                                AND a.payment_function = b.payment_function 
                                AND NVL(a.party_site_id, '0') = NVL(b.party_site_id, '0') 
                                AND NVL(a.supplier_site_id, '0') = NVL(b.supplier_site_id, '0') 
                                AND NVL(a.org_id, '0') = NVL(b.org_id, '0') 
                                AND NVL(a.org_type, '0') = NVL(b.org_type, '0') 
                                AND a.ext_payee_id <> b.ext_payee_id
                                )
ORDER BY a.PAYEE_PARTY_ID, a.last_update_date DESC;


b) Also check the log file 10140168-fix-.html

Tuesday, August 20, 2013

List of Form Functions attached to Responsibility

List of Form Functions attached to Responsibility

SELECT DISTINCT faa.application_name application, rtl.responsibility_name,
ffl.user_function_name, ff.function_name, ffl.description,
ff.TYPE
FROM fnd_compiled_menu_functions cmf,
fnd_form_functions ff,
fnd_form_functions_tl ffl,
fnd_responsibility r,
fnd_responsibility_vl rtl,
apps.fnd_application_all_view faa
WHERE cmf.function_id = ff.function_id
AND r.menu_id = cmf.menu_id
AND rtl.responsibility_id = r.responsibility_id
AND cmf.grant_flag = 'Y'
AND ff.function_id = ffl.function_id
AND faa.application_id(+) = r.application_id
AND UPPER(rtl.responsibility_name) LIKE '%TEST%'
AND r.end_date IS NULL
AND rtl.end_date IS NULL
ORDER BY rtl.responsibility_name;

Tuesday, August 13, 2013

Concurrent Processing - CP Analyzer for E-Business Suite

Concurrent Processing - CP Analyzer for E-Business Suite:

The Concurrent Processing Analyzer is a Self-Service Health-Check script which reviews the overall Concurrent Processing Footprint, analyzes the current configurations and settings for the environment providing feedback and recommendations on Best Practices.

  • This is a non-invasive script which provides recommended actions to be performed on the instance it was run on and can be run at any time.  For production instances, always apply any changes to a recent clone to ensure an expected outcome.
Benefits
  • Immediate Analysis and Output of Concurrent Environment
    • E-Business Applications Concurrent Processing Analyzer Overview
      • Total Purge Eligible Records in FND_CONCURRENT_REQUESTS
      • E-Business Suite Version
      • Concurrent Processing Database Parameter Settings
      • Applied ATG Patches
      • Known 1-Off Patches on top of Rollups
    • E-Business Applications Concurrent Request Analysis
      • Long Running Reports During Business Hours
      • Elapsed Time History of Concurrent Requests
      • Requests Currently Running on a System
      • FND_CONCURRENT_REQUESTS Totals
      • Running Requests
      • Total Pending Requests by Status Code
      • Count Pending Regularly Scheduled/Non Regularly-Scheduled Requests
      • Count of Pending Requests on Hold/Not on Hold
      • Listing of Scheduled Requests
      • Listing of Pending Requests on Hold
      • Listing of Pending Requests Not on Hold
      • Volume of Daily Concurrent Requests for Last Month
      • Identify/Resolve the Pending/Standby Issue, if Caused by Run Alone Flag
      • Tablespace Statistics for the fnd_concurrent tables
    • E-Business Applications Concurrent Manager Analysis
      • Concurrent Managers Active and Enabled
      • Concurrent Manager Processes by Workshift
      • Active Manager for Applications that are not Installed/Used
      • Total Target Processes for Request Managers (Excluding Off-Hours)
      • Request Managers with Incorrect Cache Size
      • Concurrent Manager Request Summary by Manager
      • Check Manager Queues for Pending Requests
      • Check the Configuration of OPP
  • Identifies Concurrent System Setup and configurations
  • Identifies and recommends Concurrent Best Practices
  • Easy to add Tool for regular Concurrent Maintenance
  • Execute Analysis anytime to compare trending from past outputs
For more details: Check below Link

R12: EBTax Setup and Data Integrity Analyzer

R12: EBTax Setup and Data Integrity Analyzer :

The purpose of this document is to describe how to use the diagnostic script Tax_Setup_detect_pkg.sql  to identify known data integrity issues within your EBTax Setup during Migration/Upgrade.

This script may be safely run at any time. No data is created, updated, or deleted by this script.

The script will produce an html output report of all known problems relating to the EBTax Setup during Migration/Upgradation/Implementation you have along with information on notes and/or data-fix patches which may already be available to resolve the  issues.

You should always run this script prior to logging a Service Request when you suspect there is EBTax Setup related data corruption or if you simply want to proactively verify the data integrity of your EBTax Setup.

https://support.oracle.com/epmos/faces/ui/km/SearchDocDisplay.jspx?id=1529429.1&type=DOCUMENT&recommended=true


Monday, August 12, 2013

Tracking Oracle Customization's Using Register Flagged Files Tool in R12

Having Better Life With Customizations...

Whenever we implement Oracle Applications, the client often comes up with some businesses processes which cannot be completely implemented using the existing ERP processes. These are generally accommodated by customizations in the ERP. This gives rise to creation of custom objects/systems. Over a period of time when the actual use of system increases the number of customizations increases too.

During a course of time it may happen that the company needs to upgrade the environment or apply certain patches that are released by Oracle. It may happen that there is a strong impact on the customizations that are built. If the customizations are done by using ‘Customization By Modification’ approach there is a danger of the changes being completely washed off by the upgrade or patch application. So is there any way to avoid this? Or at least a way to minimize the impact?

Yes there is!!!

The way is to use ‘Register Flagged Files Tool’ in R12.

Register Flagged Files tool can be accessed through Oracle Applications Manager (OAM) responsibility in R12.

Flagged files is a concept within the Oracle eBusiness Suite (EBS) release 12, where you flag a standard deployment file, let's say a Forms file, a Package or a Java class file. When you run the patch analysis, the list of flagged files will be checked and in case one of these files gets patched, the analysis report will tell you.

You can flag as many files as you want, in whatever relationship they are with your customizations. In addition to the flag itself you can add a comment. You should use this comment to point to your customization reference (here XXAR_RPT_066 or XXAP_CUST_030). It is suggested to put the flagged files data file directly into your CEMLI patch. Herewith the flagged files registration will be executed right at the same time when the patch gets applied.

Friday, August 9, 2013

Standard Line Status Flows in OM

Standard Line Status Flows in OM

Oracle Order Management captures the order line status in the Sales Order Pad on the Line Items Main tab in the status field and in the Order Organizer on the Summary and Line tabs. Oracle Shipping Execution displays the delivery line status in the Shipping Transactions Form on the Lines/LPN Main tab in the Line Status field. For a standard flow the statuses are:

Begin by placing the order in Order Management (OM):

Entered (OM): Order is saved but not booked.

Booked (OM): Order is booked.

Scheduled (OM): A user can customize the Workflow to show the Scheduled status which indicates that the order line has been successfully scheduled. When the ship line logic starts, the order line status changes to Awaiting Shipping.

Awaiting Shipping (OM): Order is booked but lines are not yet picked.

Open (OM): This status of a delivery on the Additional Line Information form indicates that none of the delivery lines associated with that delivery have been ship confirmed.

Ready to Release (SE): Order line is booked and passed to Shipping Execution. It is now a delivery line that is eligible for Pick Release.

Submitted for Release (SE): In Release 11i.4, Submitted for Release status changes name to Released to warehouse.

Released to Warehouse (SE): Pick Release has started but not completed. Either no allocations were created or allocations have not been Pick Confirmed.

Not Ready to Release (SE): A delivery line may be in this status when it is interfaced manually into Shipping, is not scheduled and has no reservations. When lines are imported automatically from Order Management this status is not used.

Released (SE): Pick Release has run successfully. The action has translated the delivery lines into move order lines in Inventory. The move order lines have been allocated and pick confirmed. Once a delivery has been created, the delivery and its associated delivery lines are eligible for Ship Confirm. This status is no longer valid starting with Release 11i.4. Released was replaced by the following three additional statuses:

Backordered (SE): The delivery line is pick released but no allocations were created or partial allocations occurred. As an example, if a delivery line has a quantity of 100, and at pick release only 25 are available for allocation, the original delivery line splits to create a new line (quantity of 75) for the unallocated portion with a status of

Backordered. The quantity on the original delivery line changes to 25 to reflect the allocated portion with a status of Staged/Pick Confirmed.
Staged/Pick Confirmed (SE): The delivery line is successfully pick released. It occurs after pick confirm to indicate subinventory transfer from source location to staging location is complete. Lines staged until they are ship confirmed.

Both Backordered and Staged/Pick Confirmed status provide the ability to perform opportunistic cross-docking for warehouse organizations with Oracle Warehouse Management System (WMS) installed.

Shipped (SE): This line status indicates that the delivery associated with the delivery line(s) is ship confirmed.

In Transit (SE): This delivery status indicates that the delivery associated with the line is ship confirmed and the pick up stop is closed.

Confirmed (SE): This delivery status indicates that the delivery line is either shipped or backordered and the trip stops are open.

Navigate back to Order Management and query the order which results in OM pulling updated Pick Release information from Shipping Execution (in R11i.4, these statuses will occur after Staged/Pick Confirmed and Backordered respectively):

Picked (OM): Pick release has completed normally (both allocation and pick confirm). The delivery associated with the delivery line(s) may have also been Ship Confirmed but the Delivery may not be set in transit and the Trip may not be closed.

Picked Partial (OM): This status occurs when a delivery line is not allocated the full quantity during Pick Release and Ship Confirm has not occurred. The delivery line splits during Ship Confirm and the information passes to Order Management through the Process Order API. The order line then splits to reflect the changes that occurred during the Shipping process. As an example, a customer orders quantity 50. There are 20 on hand in inventory. The delivery line splits into two delivery lines and therefore represents two order lines in Order Management. The order line with quantity 20 has the status of Picked or Shipped depending on whether
or not the delivery line is Ship Confirmed, the Delivery set in transit and the Trip closed. The second order line with a quantity of 30 has a status of Awaiting Shipping.

Shipping Execution pushes status information to Order Management once Ship
Confirm is completed:

Shipped (OM): The delivery associated with the line is Ship Confirmed. The Delivery status is set to in transit. This status appears in the Additional Line Information at the Pick Status field.

Interfaced (SE): If delivery was sourced from Oracle OM: The delivery line is shipped and the OM Interface and Inventory Interface concurrent processes have completed.

If delivery was sourced from an Oracle Application other than OM: The delivery
line is shipped and the Inventory Interface concurrent process has completed.

Awaiting Fulfillment (OM): Not all shippable lines in a fulfillment set or a configuration are fulfilled. This is a synchronization step within the Workflow process.

Fulfilled (OM): All lines in a fulfillment set are fulfilled.
Note: Fulfillment Sets are defined as a group of order lines that get fulfilled together. Items that are not shippable can be in fulfillment sets with shippable items, and then will not be fulfilled (and therefore invoiced) until the shippable items are fulfilled. A line can belong to either a ship set or an arrival set, but can belong to multiple fulfillment sets.

Interfaced to Receivables (OM): Invoice Interface has been launched. Order Management writes information to Receivables tables.
Partially Interfaced to Receivables (OM): This status is used in a PTO flow and indicates that the particular PTO item is required for revenue.

Closed (OM): Closed indicates that the line is closed. It does not necessarily indicate that the line is interfaced to Accounts Receivable (AR) since you must “close line” activity in a no-bill flow.

Canceled (OM): Indicates that the line has been completely canceled. No further processing will occur for this line.

The following scenario will emulate a Standard customer order from the first customer call to the invoice. The line status will assist the customer service agent on the Shipper's side to answer the questions of the Customer.

Entered Status (OM)
A customer calls and begins placing an order with the customer service representative. The customer is unclear whether or not the order is complete and indicates that he/she will call back to finish placing the order. The customer service representative saves the order to capture the current information but will not book the order because the customer has indicated that the order is not complete. Both the Order Header and the Order Lines associated with the customer call will have the status of Entered once the order is saved. The line on the order exists in the system and can be queried when the customer calls back to complete the order.

Booked Status (OM)
The customer service representative receives a second call the customer and adds two additional lines to the order. The customer indicates that the order is complete so the user Books the order.

Ready to Release Status (SE)
Once the order has been booked, the information passes to Shipping Execution. Order lines appear as delivery lines. Initially, it is a one to one ratio of order line to delivery line. The customer service agent calls the warehouse to ensure that the order that was just booked has appeared in Shipping Execution. The warehouse clerk queries the delivery lines by the order number provided by the customer service representative and indicates that the Line Status is Ready to Release indicating the delivery lines are eligible for Pick Release. The customer service representative has been assured that the booked order lines are visible in the Shipping Transactions form and are ready for the next step, Pick Release.

Staged/Pick Confirmed and Released to Warehouse Statuses (SE)
The warehouse clerk launches Pick Release. Upon querying the delivery lines by order number, the warehouse clerk will see that the Pick Release status is: Staged/Pick Confirmed for those delivery lines that have received allocation and Pick Confirmed successfully and Released to Warehouse for delivery lines that require a manual Pick Confirm or have not been allocated.

Picked and Awaiting Shipping Statuses (OM)
The customer who placed the order calls up and wants to know the status, the customer service representative queries up the order in the Order Organizer and finds that the status of the lines are Picked and Awaiting Shipping. The customer service representative is equipped to report that two of the order lines are processing smoothly as they have been picked from their source location and transferred to the staging location within the warehouse. The customer service representative can also inform the customer that the third order line has been released to the warehouse.

Closed and Picked Status (OM)
The warehouse clerk has just Ship Confirmed the delivery associated with the delivery lines corresponding to the customer’s order. The warehouse clerk used the check boxes on the Ship Confirm form to automatically set the delivery in transit and close the trip. Order Management will be updated through the Process Order API and the order lines that previously had the status of Picked will now show a status of Closed. The customer calls back to check the status of the order, the customer service representative can tell the customer the date(s) that two of the order lines physically shipped from the warehouse. The customer service representative can also inform the customer that the third order line has been Picked, indicating that the next function is to ship the product.

Prepare Control File Within a Minute

Prepare Control File Within a Minute

Select decode (column_id, 1, ' ', ' , ') ||
rpad (column_name, 33, ' ') ||
decode (data_type,
'VARCHAR2', 'CHAR NULLIF ('||column_name||'=BLANKS)',
'FLOAT', 'DECIMAL EXTERNAL NULLIF('||column_name||'=BLANKS)',
'NUMBER', decode (data_precision, 0,
'INTEGER EXTERNAL NULLIF ('||column_name||
'=BLANKS)', decode (data_scale, 0,
'INTEGER EXTERNAL NULLIF ('||
column_name||'=BLANKS)',
'DECIMAL EXTERNAL NULLIF ('||
column_name||'=BLANKS)')),
'DATE', 'DATE "mm/dd/yy" NULLIF ('||
column_name||'=BLANKS)', null)
from all_tab_columns
where table_name = upper ('$LOADTABLE')
and owner = upper ('$SCHEMA')
order by column_id;

Note: 
1. In the $LOADTABLE pass the table to which you want to SQL the data
2. In the $SCHEMA pass the schema in which the table is located.

Menu Structure Query

Menu Structure Query:

SELECT     LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.entry_sequence SEQUENCE,
           LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.prompt prompt,
           menu_entry.grant_flag grant_flag,
           DECODE (menu_entry.sub_menu_id,
                   NULL, 'FUNCTION',
                   DECODE (menu_entry.function_id, NULL, 'SUBMENU', 'BOTH')
                  ) TYPE,
           menu2.user_menu_name, func2.user_function_name
      FROM fnd_menu_entries_vl menu_entry,
           fnd_menus_tl menu,
           fnd_form_functions_tl func,
           fnd_form_functions_tl func2,
           fnd_menus_tl menu2
     WHERE menu_entry.sub_menu_id = menu.menu_id(+)
       AND menu_entry.function_id = func.function_id(+)
       AND menu_entry.sub_menu_id = menu2.menu_id(+)
       AND menu_entry.function_id = func2.function_id(+)
       AND grant_flag = 'Y'
START WITH menu_entry.menu_id =
                     (SELECT menu_id
                        FROM fnd_menus_tl menu2
                       WHERE menu2.user_menu_name = :MENU_NAME)
CONNECT BY menu_entry.menu_id = PRIOR menu_entry.sub_menu_id
  ORDER SIBLINGS BY menu_entry.entry_sequence

Saturday, August 3, 2013

Concurrent Manager and program related scripts

Concurrent Manager and program related scripts

SQL Script to Troubleshoot a long-running concurrent request

set term on
set feedback on
set echo on
set arraysize 4
set linesize 200
set pages 9999
set underline =;
column username format A15
column sid format 9990 heading SID
column type format A4
column lmode format 990 heading 'HELD'
column request format 990 heading 'REQ'
column id1 format 9999990
column id2 format 9999990
column sql_text format a100
column name format a80
break on id1 skip 1 dup
undefine v_request_id
define v_request_id
undefine v_spid
define v_spid
undefine v_sid
define v_sid
spool vj_concurrent_monitor.lst

Prompt Enter the concurrent_request_id
Accept v_request_id
prompt checking requests
select oracle_process_id from fnd_concurrent_requests where request_id='&v_request_id';

Prompt Enter the operating system oracle process id for this concurrent request
accept v_spid
Prompt Getting the sid
SELECT SID,SERIAL#,LOGON_TIME FROM V$SESSION WHERE PADDR IN
(SELECT ADDR FROM V$PROCESS WHERE SPID='&v_spid');

prompt Enter the session id for this concurrent request
accept v_sid
prompt memory usage for this session
SELECT A.SID,A.USERNAME,B.VALUE,c.name FROM V$SESSION a,V$SESSTAT B,V$STATNAME C WHERE A.SID=B.SID
AND B.STATISTIC#=C.STATISTIC# AND C.NAME like'%memor%' and a.sid='&v_sid';

prompt resource usage for this session
SELECT A.SID,A.USERNAME,B.VALUE,c.name FROM V$SESSION a,V$SESSTAT B,V$STATNAME C WHERE A.SID=B.SID
AND B.STATISTIC#=C.STATISTIC# and a.sid='&v_sid' order by b.value;


prompt this session waited on
select sid,event,wait_time,state from v$session_wait where sid='&v_sid' order by wait_time;


prompt current sql executing by this session
select a.sid,b.sorts,b.executions,b.loads,b.parse_calls,b.disk_reads,
b.buffer_gets,b.rows_processed,C.sql_text from v$session a,v$sqlarea b,V$SQLTEXT C
where a.sql_address=b.address and b.address=c.address and a.sid='&v_sid';


prompt sql which is taking more than 3mb in shared pool
prompt nosql should take morethan 1mb in shared pool.
prompt please ask the developers to tune the following sql statements
select name,
namespace,type,sharable_mem/(1024*1024) sharablemem,loads,executions,locks,pins,kept from v$db_object_cache
where SHARABLE_MEM>3000000;


prompt sort segments using by this session
SELECT s.username,s.sid,s.osuser,s.process,s.machine,u.extents, u.blocks,u.tablespace FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr order by extents;
and s.sid='&v_sid';

prompt current temp segments free in this instance
SELECT tablespace_name, extent_size, total_extents, used_extents, free_extents, max_used_size FROM v$sort_segment;



prompt total system events at this time
select event,total_waits waits, total_timeouts timeouts, time_waited total_time from v$system_event order by total_waits;

prompt latch contention if thery is any
SELECT latch#, name, gets, misses, sleeps FROM v$latch WHERE sleeps>0 ORDER BY sleeps ;


prompt the latch which is sleeping
select name, sleeps,latch# from v$latch_children where sleeps>4 order by sleeps;


spool off
clear columns
clear breaks

How to find out which request is handle by which concurrent queue.
a) First find out short_name of a program and then pass it as parameter to below query.

b) The below query will give you output
 I - Included  - Included in new concurrent queue
 E - excluded from Standard Manager

This way you know now this running program (concurrent request) is handled by new manager and not part of standard manager.

SELECT A.INCLUDE_FLAG, A.QUEUE_APPLICATION_ID, C.USER_CONCURRENT_QUEUE_NAME,
 B.CONCURRENT_PROGRAM_NAME
FROM APPLSYS.FND_CONCURRENT_QUEUE_CONTENT A, APPLSYS.FND_CONCURRENT_PROGRAMS B, APPS.FND_CONCURRENT_QUEUES_VL C
WHERE type_id = b.concurrent_program_id and b.concurrent_program_name = ‘&SHORT_NAME’ and c.concurrent_queue_id = a.concurrent_queue_id


How to find out Summary of Concurrent requests.

SELECT
request_id, SUBSTR(requestor,1,25), SUBSTR(program,1,50), SUBSTR(user_concurrent_program_name,1,100),
TO_CHAR(actual_start_date,’dd/mm/yy :hh24:mi’) start_date,
TO_CHAR(actual_completion_date,’dd/mm/yy :hh24:mi’) completion_date,
FLOOR((ACTUAL_COMPLETION_DATE- ACTUAL_START_DATE)*24) “in Hours”,
(((ACTUAL_COMPLETION_DATE- ACTUAL_START_DATE)*24)-(FLOOR((ACTUAL_COMPLETION_DATE- ACTUAL_START_DATE)*24)))*60 “In_Min”
–requestor, program, user_concurrent_program_name
FROM fnd_conc_req_summary_v
WHERE (ACTUAL_COMPLETION_DATE- ACTUAL_START_DATE)*24*60 >10

How to find database SID from a Concurrent request.

column process heading “FNDLIBR PID”
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = ‘R’;
You need your concurrent request ID as an input.
c.SPID= is the operating system process id
d.sid= is the Oracle process id

Cancel Concurrent requests. We don't need concurrent requests which are scheduled in production to keep running in test. We use the following update to cancel them.
update fnd_concurrent_requests
set phase_code='C',
status_code='D'
where phase_code = 'P'
and concurrent_program_id not in (
select concurrent_program_id
from fnd_concurrent_programs_tl
where user_concurrent_program_name like '%Synchronize%tables%'
or user_concurrent_program_name like '%Workflow%Back%'
or user_concurrent_program_name like '%Sync%responsibility%role%'
or user_concurrent_program_name like '%Workflow%Directory%')
and (status_code = 'I' OR status_code = 'Q');

Use the SQL below to only cancel the running requests connecting as sys
UPDATE applsys.fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE phase_code = 'R' and status_code ='R'
/
commit

Also, please put all Pending Jobs on Hold, using the SQL below connecting as sys

update applsys.fnd_concurrent_requests
set hold_flag='Y' where
phase_code='P' and hold_flag='N'
/
commit
update fnd_concurrent_requests fcr
set phase_code = 'C',
status_code = 'D'
where fcr.PHASE_CODE <> 'C'
and (fcr.program_application_id,fcr.CONCURRENT_PROGRAM_id) in
(select fcp.application_id,fcp.concurrent_program_id from fnd_concurrent_programs fcp,fnd_executables_vl fev
where fcp.executable_application_id=fev.application_id and fcp.executable_id=fev.executable_id
and (upper(fev.user_executable_name) like 'AL%MAIL%'
or upper(fev.user_executable_name) like 'AL%FTP%'
or upper(fev.user_executable_name) like 'AL%EXCEL%'))
/


To change the number of processes for the standard manager
update FND_CONCURRENT_QUEUE_SIZE
set min_processes = 4
where concurrent_queue_id = 0;

how to find params passed to request from backend

select CONCURRENT_PROGRAM_ID,CONCURRENT_PROGRAM_NAME from fnd_concurrent_programs where concurrent_program_name like '';

select REQUEST_ID,CONCURRENT_PROGRAM_ID,substr(ARGUMENT_TEXT,1,60)params,status_code,phase_code from fnd_concurrent_requests where CONCURRENT_PROGRAM_ID=;

To increase the jvm for OPP

"From Note ID 737311.1 we need to do step 3 We are changing this parameter to 2048m as mentioned below in script.
Configure the Output Post Processor's JVM. These steps set the JVM to 2GB, depending upon
your server's size you might find 3 GB (-mx3072m), 4GB (-mx4096m) or even 5GB (-mx5120m) is a better value. This setting prevents the error ""java.lang.OutOfMemoryError: Java heap space""
in the Output Post Processor's log associated to the Subledger Accounting Program.
Login to SQL*Plus as APPS.
SQL>update FND_CP_SERVICES set DEVELOPER_PARAMETERS =
'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx2048m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');
Bounce the concurrent managers.

one more eg:
To determine current heap size:
select DEVELOPER_PARAMETERS
from FND_CP_SERVICES
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');

To increase heap size to 1024:
update FND_CP_SERVICES
set DEVELOPER_PARAMETERS = 'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx1024m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');


Query to find pending concurrent requests

select count(*)
from APPS.FND_ 2 CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.PHASE_CODE = 'P'
and b.requested_start_date <= sysdate

Pending job details
"SELECT c.user_name, request_id, phase_code, status_code, hold_flag,
TO_CHAR(requested_start_date,'DD-MON-YY:HH24:MM:SS') Requested_Start_Date,
user_concurrent_program_name, b.concurrent_program_id
FROM applsys.fnd_concurrent_requests a,
applsys.fnd_concurrent_programs_tl b,
applsys.fnd_user c
WHERE a.phase_code = 'P'
AND a.concurrent_program_id = b.concurrent_program_id
AND b.LANGUAGE = 'US'
AND c.user_id = a.requested_by
ORDER BY user_concurrent_program_name;"


Cancel scheduled concurrent Request “Gather Schema Statistics” sqlplus apps/apps
sql>
update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P'
AND status_code in ('Q','I') and concurrent_program_id=38121;
Commit;
Exit


Putting all concurrent jobs on hold:
Update applsys.fnd_concurrent_requests set hold_flag='Y' where phase_code in ('R','P','I');

Cancel scheduled concurrent Request “Gather Schema Statistics”
sqlplus apps/apps
sql>
update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P'
AND status_code in ('Q','I') and concurrent_program_id=38121;
Commit;
Exit

To terminate request from backend
SQL> select REQUEST_ID,ORACLE_ID,ORACLE_PROCESS_ID,ORACLE_SESSION_ID,OS_PROCESS_ID from applsys.FND_CONCURRENT_REQUESTS where REQUEST_ID=577945;

REQUEST_ID ORACLE_ID ORACLE_PROCESS_ID ORACLE_SESSION_ID
---------- ---------- ------------------------------ -----------------
OS_PROCESS_ID
------------------------------------------------------------------------------------------------------------------------------------
577945 900 13348 1242142
13299

SQL> !kill -9 13348
/bin/ksh: kill: 13348: No such process

SQL> update APPLSYS.fnd_Concurrent_requests set PHASE_CODE='C', STATUS_CODE='D' where REQUEST_ID=577945;

1 row updated.

SQL> commit;

Commit complete.

Schedule “Purge Concurrent Request and/or Manager Data”
Cancel existing scheduled request before scheduling new.
# ebappsenv
# sqlplus apps
update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P'
AND status_code in ('Q','I')
and concurrent_program_id=32263;

Parameter:
Entity : REQUEST
Mode : Age
Mode Value : 15  On non-prod environments as per case# 734780

Schedule:
on specific days : Wednesday and Saturday at 19:00 CET


Run Gather Schema Statistics as a Concurrent request 
Cancel any pending jobs for “Gather Schema Statistics”
Sqlplus apps/appspasswd
Sql>
update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P'
AND status_code in ('Q','I') and concurrent_program_id=38121;

Login to Oracle applications of target instance as sysadmin thru Appjump
Select system administrator Responisibility
Verify whether Concurrent Request “Gather Schema Statistics” is running or not
If not running, schedule the request to run immediately with Parameters : ALL,10


To check status fo running requests:

column REQUEST heading 'Request' format a8
column PHASE heading 'Phase' format A8
column STATUS heading 'Status' format A8
column PROGRAM heading 'Program Name' format A40
column SHORT heading 'Short Name' format A15
column REQUESTOR heading 'Requestor' format A10
column START_TIME heading 'Start Time' format A15
column RUN_TIME justify left heading 'Time(m)' format 999999.9
column OSPID heading 'OSPID' format a5
column OS_PIDa heading 'OSPIDA' format a6
column SID heading 'SID' format 99999
column serial# heading 'Serial#' format 99999

select substr(fcrv.request_id,1,8)REQUEST,
decode(fcrv.phase_code,'P','Pending','R','Running','I','Inactive','Completed')PHASE,
decode(fcrv.status_code,
'A','Waiting',
'B','Resuming',
'C','Normal',
'F','Scheduled',
'G','Warning',
'H','On Hold',
'I','Normal',
'M','No Manager',
'Q','Standby',
'R','Normal',
'S','Suspended',
'T','Terminating',
'U','Disabled',
'W','Paused',
'X','Terminated',
'Z','Waiting',fcrv.status_code)STATUS,
substr(fcrv.program,1,40)PROGRAM,substr(fcrv.PROGRAM_SHORT_NAME,1,15)SHORT,
substr(fcrv.requestor,1,15)REQUESTOR,
-- to_char(fcrv.actual_start_date,'MM/DD/RR HH24:MI')START_TIME,
round(((sysdate - fcrv.actual_start_date)*1440),1)RUN_TIME,
substr(fcr.oracle_process_id,1,7)OSPID,s.sid,s.serial#
from apps.fnd_conc_req_summary_v fcrv,
apps.fnd_concurrent_requests fcr,
v$session s,v$process p
where fcrv.phase_code = 'R'
and fcrv.request_id = fcr.request_id
and s.paddr = p.addr
and fcr.oracle_process_id = p.spid
and fcrv.concurrent_program_id not in ('40112','40113','36887')
--and trunc(fcrv.actual_start_date) like trunc(sysdate)
order by PHASE, STATUS, REQUEST desc;

Take export dump of concurrent tables and import

exp userid=applsys/xxxx file=conc.dmp log=exp.log tables=FND_CONCURRENT_QUEUES,FND_CONCURRENT_QUEUES_TL,
FND_CONCURRENT_QUEUE_SIZE, FND_CONCURRENT_QUEUE_CONTENT

Truncate the fnd concurrent tables

SQL> truncate table fnd_Concurrent_queues;

Table truncated.

SQL> truncate table FND_CONCURRENT_QUEUES_TL;

Table truncated.

SQL> truncate table FND_CONCURRENT_QUEUE_SIZE;

Table truncated.

SQL> truncate table FND_CONCURRENT_QUEUE_CONTENT;

Table truncated.
Import the data back into the fnd tables which was exported as part of step above

imp userid=applsys/apps ignore=y file=conc.dmp full=y log=imp.log


Check whether the GSM is Up and Running in the system --> Profile option or using the Following Query :
select DECODE(b.profile_option_value, 'Y', 'Enabled', 'Disabled') DETAILS
from fnd_profile_options a, fnd_profile_option_values b
where a.APPLICATION_ID = b.APPLICATION_ID
and a.PROFILE_OPTION_ID = b.PROFILE_OPTION_ID
and a.PROFILE_OPTION_NAME = 'CONC_GSM_ENABLED';


Check whether the Service Manager is up and Running by the following Query :
select CONCURRENT_QUEUE_NAME, ENABLED_FLAG, MAX_PROCESSES, RUNNING_PROCESSES
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME like 'FNDSM%';

The following SQL scripts located under $FND_TOP/sql are useful when diagnosing concurrent manager problems:

afimchk.sql Tells the status of the ICM and PMON method

afcmstat.sql Lists active manager processes

afrqrun.sql Lists all the running, waiting and Terminating requests

afrqwait.sql Lists requests that are constrained and waiting for the ICM to release them.

afrqscm.sql Prints log file name of managers that can run a given request. It can be used to check for possible errors when a request stays in pending status. It requires a request id value.

afcmcreq.sql Prints the log file name of the manager that processed the request

afrqstat.sql Summary of completed concurrent requests grouped by completion status and execution type. It requires number of days prior to today on which to report parameter.

afimlock.sql Lists locks that the ICM is waiting to get

afcmrrq.sql Lists managers that currently are running a request


Reports on requests that having been running for over a specified amount of time (hard coded as 4 hours). Exceptions, in addition to the defaults below, can be added by entering the program ID for the program exceptions under multi-items. 

Runs Every 20 minutes

Uses the following query:
select b.REQUEST_ID, a.DESCRIPTION, b.phase_code,
(sysdate - b.actual_start_date) * 24 "running",
to_char(sysdate, 'mm/dd/yyyy hh:mi') "now",
to_char(b.request_date, 'mm/dd/yyyy hh:mi') "request_date",
to_char(b.actual_start_date, 'mm/dd/yyyy hh:mi') "start_time",
b.program_application_id "program_application_id",
b.concurrent_program_id "concurrent_program_id"
from APPS.FND_CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.STATUS_CODE = 'R'
and b.PHASE_CODE = 'R'
and ((sysdate - b.actual_start_date) * 24) > 4
and a.CONCURRENT_PROGRAM_ID NOT IN(36887,43393,38121,42789,31556)

Excludes:
36887 - Workflow Mailer
43393 - ITM Adapter
38121 - Gather Schema Statistics
42789 - OAM Applications Dashboard Collection
31556 - Planning Manager

Monitors pending jobs exceeds the specified threshold. Excessive pending jobs may indicate an issue with the Concurrent Manager. Uses the following query:

select a.concurrent_program_name, b.REQUEST_ID, a.description,
to_char(b.request_date, 'mm/dd/yyyy hh:mi:ss') ""request_date"",
to_char(b.requested_start_date, 'mm/dd/yyyy hh:mi:ss') ""request_start""
from APPS.FND_CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.PHASE_CODE = 'P'
and b.requested_start_date <= sysdate


The error threshold monitor will alert if the number of failed jobs exceeds the user defined threshold in a 30 minute period.

select a.concurrent_program_name, b.REQUEST_ID, a.description,
b.status_code, b.phase_code
from APPS.FND_CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.STATUS_CODE IN ('E')
and b.actual_completion_date > sysdate - 1/48


Find request which are put on hold
SQL> select REQUEST_ID from fnd_concurrent_requests where phase_code ='P' and hold_flag='Y';


To find oracle_process id for a request id to pull trace file from udump:

select oracle_process_id ,
decode(status_code,'R','Running','D','Canceled','E','Error','X','Terminated','G','Warning','T','Terminating')""Status_code"",
phase_code,to_char(actual_start_date,'DD-MON-YYYY=>hh24:mi:ss') ""Login Time""
from apps.fnd_concurrent_requests where request_id='&Enter_conn_req_id'


To find spid of a request to get the trace file
prompt accept request prompt 'Please enter the concurrent request id for the appropriate concurrent program:
prompt

column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 80
set pages 22
set head off

SELECT 'Request id: '||request_id ,'Trace id: '||oracle_Process_id, 'Trace Flag: '||req.enable_trace,
'Trace Name:'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc', 'Prog. Name: '||prog.user_concurrent_program_name,'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running') ||'-'||decode(status_code,'R','Normal'),'SID Serial: '||ses.sid||','|| ses.serial#,'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname
where req.request_id ='&request'
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id


To check failed jobs submitted by an user
set lines 1000 pages 100
clear columns
col "Submitted By" format a15 word_wrap
select user_name "Submitted By", request_id "Request #",
to_char(cr.request_date,'dd-mon-rr hh24:mi') "Submitted on",
to_char(cr.last_update_date,'dd-mon-rr hh24:mi') "Failed on"
from applsys.fnd_concurrent_requests cr,
applsys.fnd_user u
where u.user_id = cr.requested_by
and user_name like '%BATCH%'
and cr.status_code ='E'
and cr.phase_code ='C'
and cr.request_date > sysdate - 1
order by 1

Statement to put the jobs on hold and release them lateron
To hold the requests (as apps user):
1) Drop table apps.str_dba_conc_req_hold ;
2) Create table apps.str_dba_conc_req_hold as select * from fnd_Concurrent_requests where PHASE_CODE='P' and hold_flag='N';
3) select count(*) from apps.str_dba_conc_req_hold ;
4) update fnd_Concurrent_requests set hold_flag='Y' where PHASE_CODE='P' and hold_flag='N' and request_id in (select request_id from apps.str_dba_conc_req_hold) ;

NOTE: You have to commit if select & update are same number of records. Otherwise rollback and try again till the numbers are same

5) Commit;

To Release these requests in prod after patching, here is the step :

6) a. update fnd_Concurrent_requests set hold_flag='N' where request_id in (select request_id from apps.str_dba_conc_req_hold);
b. commit;


How to take cm program trace.

Responsibility: System Administrator
Navigate: Concurrent > Program > Define
Query Concurrent Program
Select the Enable Trace Checkbox

Responsibility: System Administrator
Navigate: Profiles > System
Query Profile Option Concurrent: Allow Debugging
Set profile to Yes

Logon to the Responsibility that runs the Concurrent Program
In the Submit Request Screen click on Debug Options (B)
Select the Checkbox for SQL Trace


To submit active user request from backend
CONCSUB APPS/APPS SYSADMIN "System Administrator" SYSADMIN CONCURRENT FND FNDSCURS PROGRAM_NAME='"Active Users"'

sql program to submit request from backend
"SET SERVEROUTPUT ON
declare
req_id number;
begin
DBMS_OUTPUT.PUT_LINE('In begin');
fnd_global.APPS_INITIALIZE (0, 21758, 671);
req_id := FND_REQUEST.SUBMIT_REQUEST(application => 'FND',program => 'FNDSCURS',description => '',start_time =>
'',sub_request => FALSE);

if (req_id = 0) then
/* Handle submission error */
DBMS_OUTPUT.PUT_LINE('Request ID :' || req_id);
DBMS_OUTPUT.PUT_LINE('As the request ID is 0, the request was not submitted');
DBMS_OUTPUT.PUT_LINE('Please verify this part again');
else
DBMS_OUTPUT.PUT_LINE('Request ID :' || req_id);
DBMS_OUTPUT.PUT_LINE('Request submitted successfully');
commit;
end if;
end;
/

Meaning of status_code and phase_code in FND_CONCURRENT_REQUESTS table STATUS_CODE Column:

A - Waiting
B - Resuming
C - Normal
D - Cancelled
E - Error
F - Scheduled
G - Warning
H - On Hold
I - Normal
M - No Manager
Q - Standby
R - Normal
S - Suspended
T - Terminating
U - Disabled
W - Paused
X - Terminated
Z - Waiting


PHASE_CODE column

C - Completed
I - Inactive
P - Pending
R - Running

Friday, August 2, 2013

Excel Template Report using BI Publisher

Excel Template Report:

Most of the people knows that XML Publisher Report can only be build using RTF File. However, latest version of BI Publisher 11.1.15 onward has flexibility to create XML Publisher Report in Excel too.

Advantages:
a) If one of your client ask you to develop a XML Publisher report in multi-tab excel format, we generally say that it is not possible using XML Publisher. But now, it is possible using Excel Template Report.

b) If one of your client ask you to prepare a XML Publisher report where formulas should be visible as like in Excel,  we generally say that it is not possible using XML Publisher. But now, it is possible using Excel Template Report.

c) Splitting of Data into multiple tabs based on conditions

d) Split hierarchical data across multiple sheets and dynamically name the sheets

e) Create sheets of data that have master-detail relationships

f)  Use native Excel functionality

Disadvantages:
a) RTF Report is easy to design in comparison with Excel Template Report.

b) RTF Report is easy to debug in comparison with Excel Template Report.

Please find the below link for Excel Template Report:

http://docs.oracle.com/cd/E21764_01/bi.1111/e13881/T527073T571887.htm#exc_xslt_tr