Run Time Restriction on Concurrent Program Parameter Values

Requirement – Have one program with 2 parameters. 1) Email Options and 2) Invoice Status  

                     Email option can be Yes or No – No will be default value

                    Invoice Status can have Approved, Cancelled, Needs reapproval and Never approved options.

As per requirement  user should be able to pick any of 4 possible Invoice Status, if Email option is No but  in case if Email option is Yes

User should be restricted to select any value other than Approved.

Solution –  This can be easily achieved if we create a custom table having invoice status values along with one additional column to map Email options value.                       

-          Create one custom table using below script

CREATE TABLE O_APPROVAL_STATUS_TAB

(

  status_id                                NUMBER,

  inv_approval_status                             VARCHAR2(50),

  description                VARCHAR2(50),

  email_flag                 VARCHAR2(5)

)

/

GRANT ALL ON O_APPROVAL_STATUS_TAB TO APPS

/

insert into O_APPROVAL_STATUS_TAB values(10,'APPROVED','Validated','Y')

/

insert into O_APPROVAL_STATUS_TAB values(20,'APPROVED','Validated','N')

/

insert into O_APPROVAL_STATUS_TAB values(30,'CANCELLED','Calcelled','N')

/

insert into O_APPROVAL_STATUS_TAB values(40,'NEEDS REAPPROVAL','Needs Revalidation','N')

/

insert into O_APPROVAL_STATUS_TAB values(50,'NEVER APPROVED','Never Validated','N')

-          Create  custom value set O_APPROVAL_VS

    

   Go to Edit Information and select table name as O_APPROVAL_STATUS_TAB

-          Set Where condition as  email_flag=$:FLEX$.Yes_No

-          Create one concurrent Program and define 2 parameters

a)      Email Option              – Yes_No                                             ( Value Set )

b)      Invoice Status            – O_APPROVAL_VS                        ( Value Set )

-          Assign this program to any request group and validate the results

Output Validation – Run the program and change the Email option values Yes and No.

 

OPP Service log file and location on server

In previous post on OPP, we learnt about OPP services using application. Below mentioned SQL is to identify the log file name and location in unix server.

If concurrent program ends in warning due to any OPP service failure, run below sql by passing request id.

SELECT fcpp.concurrent_request_id req_id,
  fcp.node_name,
  fcp.logfile_name
FROM fnd_conc_pp_actions fcpp,
  fnd_concurrent_processes fcp
WHERE fcpp.processor_id        = fcp.concurrent_process_id
AND fcpp.action_type           = 6
AND fcpp.concurrent_request_id = &REQ -- 12345

Req_id      Node_name          LogFileName
12345  ebs002learn   /oracle/ebs/inst/apps/ebs_002learn/logs/appl/conc/log/FNDOPP383669.txt
 
Open putty
Login to server ebs002learn
open specified Logfile and search for your request id (12345) for detail log message

**** Above SQL is from my DBA Friend ( Jyothi )

Concurrent Program Parameter Validation

Requirement – Input should be in specific format for one parameter. Let’s say we have one free text parameter (email) which must be ended with “@OLearning.com” .

It should not accept if email is not having @OLearning.com at end

How to Achieve -

Create one value set of validation type = Special 

Press Edit Information and than Select event = Validate

Write validation logic in Function section

FND PLSQL "DECLARE
lv_str varchar2(100) := :!VALUE;
lv_com varchar2(20);
lv_standard varchar2(100) :='@OLearning.com';
BEGIN
IF (lv_str IS NOT NULL) THEN
 Select substr(lv_str,instr(lv_str,'@'

,1),length(lv_str)) INTO
lv_com from dual;

       IF lv_com<>lv_standard  THEN
                 fnd_message.set_name('XXXXX','@OLearning is Required');
           fnd_message.raise_error;
   END IF;
END IF;
END;"

                Go to concurrent program definition page

                Press parameter button

                Create parameter for email address and associate above created value set (OLearning_VS)

                Save the program

Validation –

Go to  respective responsibility

Run the concurrent program

Pass the email parameter = sandeep@gmail.com and tab out

               

Pass the email parameter = sandeep@OLearning.com   and tab out

               

Different Header and Footer for First Page - XML Publisher Report

Requirement  was to report invoice details in xml publisher in PDF format. If there is multiple pages for any invoice, it should show different header details.

In rtf it is very easily achievable using MS word features.

• 1)      Open the rtf file
• 2)      Go Header & Footer section
• 3)      Check the flag “Different First Page”
• 

• 4)      Write the Header/footer Details
• 
• 5)      Go to the second page (if rtf has only one page, put some line space to reach second page)
• 6)      Create second header/footer details
• 
• 7)      Remove the extra line space created in step 5
• 8)      Load the data source
• 9)      Preview the output 
• 

  
  

  
  

  
  

  
  

  
  

  
  

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.