Query to find Request group name from Concurrent Program Name

Here's the query to find the request group name by passing concurrent program name as parameter

SELECT
    FRG.REQUEST_GROUP_NAME,
    FRG.DESCRIPTION,
    CP.CONCURRENT_PROGRAM_ID,
    CP.CONCURRENT_PROGRAM_NAME,
    CPT.USER_CONCURRENT_PROGRAM_NAME,
    FRV.RESPONSIBILITY_NAME
FROM
    FND_REQUEST_GROUPS FRG,
    FND_REQUEST_GROUP_UNITS FRGU,
    FND_CONCURRENT_PROGRAMS CP,
    FND_CONCURRENT_PROGRAMS_TL CPT,
    FND_RESPONSIBILITY_VL FRV
WHERE
    FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
    AND FRGU.REQUEST_UNIT_ID = CP.CONCURRENT_PROGRAM_ID
    AND CP.CONCURRENT_PROGRAM_ID = CPT.CONCURRENT_PROGRAM_ID
    AND FRV.REQUEST_GROUP_ID = FRG.REQUEST_GROUP_ID(+)
AND CPT.USER_CONCURRENT_PROGRAM_NAME LIKE 'XX Conc Pgm Name';

Set up for OAF pages development

This post helps you in setting up the OAF page development environment.

Pre-Requisites to Setup OAF development environment
-------------------------------------------------------------------------------
1) download Jdeveloper OAF Patch for respective Oracle eBiz Instance.
2) download dbc files from unix server for respective Oracle eBiz Instance you are going to work with.

Detailed instructions
----------------------------------

1) Find your Oracle eBiz Version by logging into your apps -- Click About this page link at bottom of the page -- Click Technology components tab -- you will see the screen below

Note down your OA Framework version. In my case it is 12.1.3.

So i need to download the Jdeveloper OAF patch for this version(12.1.3) in the below link.
How to find the correct version of JDeveloper to use with eBusiness Suite 11i or Release 12.x (Doc ID 416708.1)

you will find the table like below shown



click the respective patch and download it.

Extract the downloaded .zip file into any location.
C:\p7523554_R12_GENERIC

you will find 3 folders. (jdevbin, jdevdoc, jdevhome)

Set the JDEV_USER_HOME environment variable
----------------------------------------------------
Right click my computer -> Properties -> Advanced Tab -> Environment variables



Variable name : JDEV_USER_HOME
Variable value will be DRIVE_LOCATION:\PATCH_NAME\jdevhome\jdev


You have almost completed the setup.

Downloading dbc(database connectivity file) 
-----------------------------------------------
This file used to connect the Jdeveloper to your Oracle eBiz Instance.
This file will be available with your DBA's or $FND_SECURE top.

FTP the .dbc file to "C:\p7523554_R12_GENERIC\jdevhome\jdev\dbc_files\secure"

dbc file contains information like below

#DB Settings
#Mon May 02 18:26:56 IST 2012

GUEST_USER_PWD=GUEST/ORACLE
APPL_SERVER_ID=A1E590D05E00456FE04400000000000021673182782189486806263446144422
FND_JDBC_BUFFER_DECAY_INTERVAL=300
APPS_JDBC_DRIVER_TYPE=THIN

FND_JDBC_BUFFER_MIN=1
GWYUID=APPLSYSPUB/PUB
FND_JDBC_BUFFER_MAX=5
APPS_JDBC_URL=
FND_JDBC_STMT_CACHE_SIZE=100
TWO_TASK=POC12
JDBC\:oracle.jdbc.maxCachedBufferSize=358400
JDBC\:processEscapes=true
FND_MAX_JDBC_CONNECTIONS=500
FND_JDBC_USABLE_CHECK=false
FNDNAM=APPS
FND_JDBC_PLSQL_RESET=false
DB_PORT=1553
FND_JDBC_CONTEXT_CHECK=true
FND_JDBC_BUFFER_DECAY_SIZE=5

Now start your OAF page development.

LONG RAW to BLOB Conversion

We all know there's a function called TO_LOB which converts LONG to BLOB object.

But we might experience this rare case LONG_RAW to BLOB. Yes i encountered this problem when i want to execute this query in oracle apps 11i Oracle 9 database.

SELECT TRUNC(SUM(DBMS_LOB.GETLENGTH(image))/1024/1024,2)||' MB'  bytes

FROM   per_images

Error:  ORA-00997: illegal use of LONG datatype

I was wondering how come the query is executing in R12 not in 11i..then i cross checked it

In oracle apps R12 the IMAGE column data type in PER_IMAGES table is  BLOB

whereas In oracle apps 11i the IMAGE column data type in PER_IMAGES table is LONG RAW

So the above query will not work in 11i..so i want to convert this long_raw to blob data type.

I tried this

SELECT TRUNC(SUM(DBMS_LOB.GETLENGTH(TO_LOB(image)))/1024/1024,2)||' MB'  bytes

FROM   per_images

ORA-00932: inconsistent datatypes: expected - got BINARY

Note: If the size is less than 32k you will get the value.

Solution to get the length of the image column is:

CREATE TABLE XX_PER_IMAGES_TEMP (IMAGE BLOB);

INSERT INTO XX_PER_IMAGES_TEMP

                                 SELECT

                                   TO_LOB(IMAGE)

                                 FROM

                                    PER_IMAGES;

SELECT TRUNC(SUM(DBMS_LOB.GETLENGTH(image))/1024/1024,2) image_size

FROM

XX_PER_IMAGES_TEMP;

Hope this post helps you on how to convert. comments please.