Pivot Function - Transform table rows to colums

PIVOT is very powerful option when data is available in table and requirement is to convert rows in column and display data for those column. One group function is mandatory to use this option.

Syntex -  
SELECT * FROM ( 

SELECT ColumnA,ColumnB,ColumnC,ColumnD 

FROM TABLE_NAME 

WHERE conditions)

PIVOT ( groupFunction(ColumnD) FOR ColumnC in (Values of ColumnC))

Example- assume we have employee expense details for each month. Now requirement is to generate a report which will display each employee’s monthly expenses in column.

Create Table & Insert Values>>

create table test_sandeep(name varchar2(10),dept varchar2(10),month number,amount number);

insert into test_sandeep values('Sandeep','ABC',1,300)

insert into test_sandeep values('Sandeep','ABC',1,120)

insert into test_sandeep values('Sandeep','ABC',2,400)

insert into test_sandeep values('Sandeep','ABC',3,200)

insert into test_sandeep values('Kumar','DEF',2,600)

insert into test_sandeep values('Kumar','DEF',3,700)

insert into test_sandeep values('Kumar','DEF',3,450)

To obtain monthly basis expense amount, we used to write below group by code -

SELECT name,

         dept,

         month,

         SUM (amount)

    FROM test_sandeep

GROUP BY name, dept, month

Using Pivot, we can obtain same data in below manner

SELECT *

  FROM (SELECT name,

               dept,

               month,

               amount

          FROM test_sandeep) PIVOT (SUM (amount) FOR month IN (1 "Jan", 2 "Feb", 3 "Mar"))

OPP Service Failure - Output Post Processing

Concurrent program for XML reports very often ends in warning with below message -

“Beginning post-processing of request $REQUEST_ID on node $APP_HOST at 13-OCT-2011 17:21:02.
 Post-processing of request $REQUEST_ID failed at 13-OCT-2011 17:21:04 with the error message:
 One or more post-processing actions failed. Consult the OPP service log for details.”

Cause - This issue can occur because of either opp service failure or custom xml template.

Fix 1- Check with DBA about opp service status and ask if it is not active.
Fix 2- If opp service is running, it can be issue with our custom xml template. Analyze the opp service log to catch the root cause.


To get opp service log - go to 
1) System administrator >>> Oracle Applications Manager >>concurrent Managers 

2) Select “output post processor” and click  “View Details”

3) Click on the output post processor to display all process running for OPP

4) Click on the log button  - to see the OPP request log


Go through the log file and get the error point. It have some keyword  which is causing issue.
Now check the properties of data field on template, Bad keyword will be available at some places.
correct it and save the template.

Run the program again..It shall complete normal and you can see your expected output file. 

Profiles 4 OAF pages

Below are the list of few profiles, you must know to work on OAF pages.

1) Show Diagnostics option on OAF pages -
    Set profile "FND:Diagnostics"= Yes


2) Display Personalization link on OAF pages -Set  any of profile options
          "Personalize Self-Service Defn"= YES
                          OR
          "Disable Self-Service Personal"=No


3) Display Personalization link for each region -
     Set profile "FND: Personalization Region Link Enabled" =YES

4) Show About this page on OAF Pages - enable any profile option mentioned above in 1 and 2.

Soft Link or Symbolic Link

S Link ( soft link or symbolic link) is required to read a shell script (.prog file) through a concurrent program.

We create a concurrent program of executable type as HOST. The host file extension is .prog. We must have to create a soft link to access this host file.

Host File Location – It is available at bin folder on custom top. ($XXXXX_TOP/bin)

Soft Link Creation –  ln -s  $FND_TOP/bin/fndcpesr  sourcefile

 ln -s  $FND_TOP/bin/fndcpesr  $v_custom_top/bin/XXXXX_TEST_SOFTLINK

The above command will create the soft link for XXXXX_TEST_SOFTLINK.prog file. The new link will look as below – 

Known Issues – Sometime concurrent program could not read the soft link, though it is available.  Below is the error message can be found in log file

FND-CP-ESP: Child: exec:: No such file or directory

/oracle/…/…/xxxxx/12.0.0/bin/XXXXX_TEST_SOFTLINK

Program exited with status 1

Root Cause – This issue appear if there is any control M character in prog file, which is the source for soft link.

Check for control M Char -$ ./XXXXX_TEST_SOFTLINK.prog

if output is file contents => No control M character available

if output has control M Char => 

Resolutions – Remove the control M character and re create the soft link.

1)      Delete the existing soft link   >>$  rm    XXXXX_TEST_SOFTLINK  

2)      Remove the control M character >>$ cat    XXXXX_TEST_SOFTLINK.prog | col -b >  XXXXX_TEST_SOFTLINK1.prog

3)   Delete the existing prog file  >> rm XXXXX_TEST_SOFTLINK.prog
4)   Rename the new prog file to old  >>$  mv     XXXXX_TEST_SOFTLINK1.prog  XXXXX_TEST_SOFTLINK.prog

4)      Re create the soft link for XXXXX_TEST_SOFTLINK.prog

**PS – Do not copy prog file in  binary mode or zipped mode to server. It may include control M character to prog file.

FNDLOAD - Core Oracle Apps Objects Migration

FNDLOAD is very useful oracle provided utility to migrate core oracle apps objects (Flex fields,Concurrent program,responsibility,user,profiles,messages,request group.......forms personalization etc) from one instance to other.This post is all about FNDLOAD utility. 

Oracle provided all setup objects being stored in FND tables and shared  across the application.The name FNDLOAD - to load/download objects to/from FND Tables.

1) Upload objects - 

 FNDLOAD apps_user_name/apps_password 0 Y UPLOAD $Control_file $Object_name.ldt  

2) Download objects -

FNDLOAD apps_user_name/apps_password 0 Y DOWNLOAD $Control_file $Object_name.ldt [Parameters]

Parameters - 

$Object_TYPE [$Object_TYPE_Name="Your Object Name"] APPLICATION_SHORT_NAME=$'XXXXX' [$SubObject_TYPE] [$SubObject_TYPE_Name="Your sub Object Name"]

$Control_file => Control file is .lct file specific to object.Give the complete patch of file or download file at same location.most of the cases it is available at $FND_TOP/patch/import

$Object_name.ldt  => Downloaded file will be saved with this name

$Object_TYPE => downloaded object type (REQUEST_GROUP,PROGRAM etc)

Sub Object Type is required in case object is holding many objects and requirement is to download single sub object. Ex - Request Group contains many concurrent program entry, to download request group holding only your conc prog, use sub object type as program and give your program name. 

Below is the FNDLOAD command for different objects -

========================================================================= 
FNDLOAD example for few frequently used objects
========================================================================= 

XML Data Definition and Template

FNDLOAD apps/appspwd 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct DATADEFTEMPLATE.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME='XXXXX' DATA_SOURCE_CODE='Your Data Definition Code'

Concurrent Program

FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="prod" CONCURRENT_PROGRAM_NAME="concurrent name"

Request Set

FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME="prod" REQUEST_SET_NAME="request set"

Request Group 

FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="prod"

Single Program from Request Group

FNDLOAD apps/appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct DATALOADERFILE.ldt REQUEST_GROUP REQUEST_GROUP_NAME="Your Request Group name" APPLICATION_SHORT_NAME="Your Request Group Application short name" REQUEST_GROUP_UNIT UNIT_NAME="Your concurrent program short name"

Single Message

FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct YOURLDTFILENAME.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME='XXXXX' MESSAGE_NAME='YOUR MESSAGE NAME'

Message

FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct YOURLDTFILENAME.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME='XXXXX'

Responsibility

FNDLOAD apps/appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility

Value set

FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name"

Value set with values

FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="value set name"

 

Menu

FNDLOAD apps/appspwd  0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME="menu_name"

Profiles

FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="XXXXX"

KFF - Key Flex Fields

FNDLOAD apps/appspwd 0 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="XXXXX" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name"

DFF - Descriptive Flex Fields

FNDLOAD apps/appspwd 0 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="xxxxx" DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name"

 Lookup

FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="xxxxx" LOOKUP_TYPE="lookup name"

Printer Style 

FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name"

Defects Manager - Oracle Reports

1) ORA - 24324 : Service handle not initiated ==> SELECT

Resolution - Check the Database connection and re connect. This error is because of  Database disconnection.