Locate the trace file generated for a trace enabled concurrent request

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_v1 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;

Find out long running Concurrent Requests

Using the below query we can find out which concurrent requests are running from a long time than expected time.


SELECT a.request_id
,a.oracle_process_id "SPID"
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
,a.description
,a.ARGUMENT_TEXT
,b.node_name
,b.db_instance
,a.logfile_name
,a.logfile_node_name
,a.outfile_name
,q.concurrent_queue_name
,a.phase_code,a.status_code, a.completion_text
, actual_start_date
, actual_completion_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || '' = 'C') avg_mins
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.phase_code = 'R'
AND a.status_code = 'R'
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
ORDER BY a.actual_start_date DESC




The above query gives the spid of the request through which we can pull the respective details


SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c
WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = 'ACTIVE'
AND a.spid = '2475'
ORDER BY a.spid, c.piece


Then find the reason for the long running and then kill the process through OS level if need

FNDLOAD

It can be defined as a concurrent program that can move Oracle Applications data between database and text file representations.Or this can be defined as FNDLOAD can download data from an application entity into an editable text file, which can be uploaded to another database.

How it works


Conversion between database format and text file format is specified by a configuration file. Oracle does provide configuration file and it is important to just pass the configuration file name and then just call the loader and leave everything .The schematic visual representation of the loader is like:






What can be done?


These are the extensive list which can be done through FNDLOAD
Concurrent Programs, Executables

Request Groups, Request Sets

Profile Options

Key and Descriptive Flexfields

Menus and Responsibilities

Forms and Form Functions

Attachments

Messages

Value Sets and Values

Lookup Types

User Responsibilities

Printer Definitions

FND Dictionary

Help Configuration

Document Sequences

Concurrent Manager Schedules



what are advantages when using FNDLOAD are :
Because downloaded data is stored in a text file, version administration is possible

There is nothing to worry to go to purchase because..Investment = 0$

No learning curve. this is relief for developer/dbas

Fully supported and recommended by Oracle

Capture the migrations in a file and use it during installations, clones etc. to migrate in batch

Pin-point when something happened and where (database) easily

Your AOL data migration process is now simplified and streamlined.



Some Disadvantages also :
Applications patching mechanisms use FNDLOAD heavily possibility of negative impact is not zero

UPLOAD_MODE=REPLACE only for menus

No validation against migrating database/instance sensitive data

The Syntax
To use FNDLOAD, the following syntax is needed.

FNDLOAD apps/appspwd 0 Y mode configfile datafile entity [parameter1¦..] As you can see, some info is needed.- First you will need to know the Apps password.

The mode is either DOWNLOAD or UPLOAD.

The configfile is the file that Fndload needs to download on upload data.

The data file is the output file, in which the downloaded data is written

The entity is the entity you want to download,

Parameters van is passed to download a certain Alert


Modes of Operation
This is important because it would drive the whole flow, and it always be two mode ..Upload and Download

Example of download:
FNDLOADapps/pwd 0 Y DOWNLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt \ PROGRAM CONCURRENT_PROGRAM_NAME= concurrent_program_short_name> APPLICATION_SHORT_NAME=<application_short_name>
Example of Upload
FNDLOAD apps/pwd 0 Y UPLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt - CUSTOM_MODE=FORCE undocumented parameter WHAT IS FNDLOADLoad Options:

Application level OR single entity level download

(Example) Download all the profile options of Inventory or just the INV: Default Primary UOM

Entire OR Partial upload of a data file

(Example) Upload the entire myfile.ldt or just a single enity indicated by - and mode UPLOAD or UPLOAD_PARTIAL

Entity name required for UPLOAD_PARTIAL mode

Custom mode force update

To override Oracle's upload algorithm and update the custom AOL data regardless, use CUSTOM_MODE= FORCE

UPLOAD_MODE= REPLACE (only for menus)

Support for NLS uploads

NLS data uploads for translated columns are supported, use UPLOAD_MODE= NLS
Then ..Where is Config File Located
By default Oracle delivers most of configuration files you can use to download certain entities.

Configuration files with extension.lct

On Unix - all the configuration files are in $FND_TOP/patch/115/import directory

On Unix Oracle also places the original configuration files in $FND_TOP/admin/import directory

Data files with extension .ldt

and..FNDLOAD File Structures is

The config files (.lct) are delivered and maintained by Oracle

It has entity definitions, parent-child relationships and user input parameters identified by :NAME

Downloading a parent automatically downloads all children - (Example) Concurrent Program download
Take a note FNDLOAD File Structures

The data files (.ldt) have both entity definition and the data

It also shows the version and the location of the config file (.lct) that was used

Without the config file, data file is useless

Without the data file, config file is meaningless
..Remember regarding FNDLOAD Files

Key files: .lct and .ldt

You must run the FNDLOADas apps user not as applsys or any other, otherwise you will receive Ora-6550

Both are easily readable, editable and portable

DO NOT MODIFY Oracle's .lct files

Use your favorite editor (I would suggest Textpad or Editplus) to manipulate only the .ldt files but be cautious about data type, length, delimiter placements etc.

Use the log file outputs or .ldt file contents creatively for quick file comparisons and answer questions faster (Why can't I access that? What is that profile option name, value and level? What is the value set used for that DFF segment attribute10 etc.)

Partial string searches (which value set has Priority something in its where clause? Etc)


Sample Script Code for these Objects :
1 - Printer Styles

FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name" 2 - Lookups

FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="prod" LOOKUP_TYPE="lookup name" 3 - Descriptive Flexfield with all of specific Contexts

FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=?COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME="prod" DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name" 4 - Key Flexfield Structures

FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=?COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL? APPLICATION_SHORT_NAME="prod" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name" 5 - Concurrent Programs

FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="prod" CONCURRENT_PROGRAM_NAME="concurrent name" 6 - Value Sets

FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name" 7 - Value Sets with values

FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="value set name" 8 - Profile Options

FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="prod" 9 - Requset Group

FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="prod" 10 - Request Sets

FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME="prod" REQUEST_SET_NAME="request set" 11 - Responsibilities

FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility 12 - Menus

FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME="menu_name" 13 - Forms/Functions

FNDLOAD apps/apps@seed115 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES The Upload syntax for all styles: FNDLOAD apps/apps@seed115 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt 14. User/Responsibilities

FNDLOAD apps/apps@seed115 0 Y DOWNLOAD @FND:patch/115/import/afscursp.lct file_name.ldt FND_USER Then UPLOAD FNDLOAD apps/apps@seed115 0 Y UPLOAD [UPLOAD_PARTIAL] @FND:patch/115/import/afscursp.lct file_name.ldt FND_USER [<USER>]
References:

Oracle Applications Systems Administrator's Guide
Metalink Notes: 117084.1, 228614.1 232029.1 , 274667.1
https://sites.google.com/site/oracledb009/

EBS Table types

The different Table-types and names used in Oracle Apps are

_ALL are multi-org tables.

_TL are tables corresponding to another table with the same name minus the _TL. These tables provide multiple language support. For each item in the table without _TL there can be many rows in the _TL table, but all with different values in the LANGUAGE column.

_F these are date tracked tables which occur in HR and Payroll. For these there are two date columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE which together with the PK identifies a row uniquely. The date intervals cannot overlap.

_B these are the BASE tables.
They are very important and the data is stored in the these table with all validations.
It is supposed that these table will always contain the perfect format data.
If anything happens to the BASE table data, then it is a data corruption issue.

_V are views.

_VL are views for multi language tables which combines the row of the base table with the corresponding row of the _TL table where the LANGUAGE = USERENV('LANG').

_S are sequences, used for finding new values for the primary key of a table.

_A are Audit Shadow Tables.

_AVn and _ACn are Audit Shadow Views (when data was changed, and with what values).

Query to get the Application Modules available

select f.application_id,f.application_short_name,f.basepath,fl.application_name
from fnd_application f,fnd_application_tl fl
where fl.application_id = f.application_id and fl.language='US'
group by f.application_id,f.application_short_name,f.basepath,
fl.application_name,fl.language order by f.application_id
/
Sample output would be like

APPLICATION_ID APPLICATION_SHORT_NAME BASEPATH APPLICATION_NAME
0 FND FND_TOP Application Object Library
1 SYSADMIN FND_TOP System Administration
3 AU AU_TOP Application Utilities
40 EMS EMS_TOP Environment Management System
41 PTX PTX_TOP Patch Tracking System
50 AD AD_TOP Applications DBA
60 SHT SHT_TOP Applications Shared Technology
99 OAM OAM_TOP Oracle Applications Manager
101 SQLGL GL_TOP General Ledger
140 OFA FA_TOP Assets
160 ALR ALR_TOP Alert
168 RG RG_TOP Application Report Generator
170 CS CS_TOP Service
172 CCT CCT_TOP Telephony Manager
174 ECX ECX_TOP XML Gateway
175 EC EC_TOP e-Commerce Gateway
177 POS POS_TOP iSupplier Portal
178 ICX ICX_TOP Oracle iProcurement
185 XTR XTR_TOP Treasury
186 QRM QRM_TOP Risk Management
190 AZ AZ_TOP Application Implementation
191 BIS BIS_TOP Applications BIS
200 SQLAP AP_TOP Payables
201 PO PO_TOP Purchasing
202 CHV CHV_TOP Supplier Scheduling
203 AME AME_TOP Approvals Management
204 XLE XLE_TOP Legal Entity Configurator