Profile Utilities:Diagnostics

 Utilities:Diagnostics to enable Access to  custom code under HELP > Diagnostics

Additional Parameter Operating Unit in R12

You must have observed additional parameter (Operating Unit) option while running the concurrent program in R12. It's due to MOAC feature to run the program for selective OU only.

This additional parameter is controlled from System Administrator responsibility Self service version.

This post is to help how to pass value to this parameter if we are triggering such programs from database.

DECLARE
l_request_id  NUMBER;
BEGIN
           dbms_output.put_line('OAL - START::');
           FND_REQUEST.SET_ORG_ID(82); 
          -- This is required to set the value for additional Operating Unit Parameter

         l_request_id :=
         fnd_request.submit_request( 'APPLICATION_SHORT_NAME' ,'CPSHORTNAME' ,NULL ,NULL ,TRUE,Arg1,Arg2......);


           dbms_output.put_line('OAL - END::'|| l_request_id );

EXCEPTION
WHEN OTHERS THEN
      dbms_output.put_line('OAL - Exception ::'||SQLERRM);
END;

How to add LOV field on OAF page without OAF customizations

Adding LOV field on OAF page, requires creation of many OAF objects. Those not having much exposure on OAF can use below to achieve same –

•  
• Check if any DFF is available on the page and can be reused by adding the new segments
• Adding value set to any segment will display LOV on segment on OAF pages
• If no DFF available, create custom DFF using respective table
• Create new flex item on page to include the custom DFF
• Assign the value set to the segment will fulfill the need.

 

This is simple and easily maintainable as LOV data will be fully controlled by your value set used.

How to create custom DFF

Mostly we use standard DFF by adding more context and segment combination.  This will be very useful when we don’t have standard DFF enable and need to store some additional details.

 

Oracle provides option to create custom DFF –

 

Login to EBS >>Application Developer (R) >> Flexfield >>Descriptive >> Register

Application – Oracle IProcurement

Title – Sandeep

Table Application – Oracle IProcurement

Structure Column – Attribute15 (This should be attribute category. Taken attribute15 as no category was available on selected table)

 

Name – TESTDFF

Description –

Table Name – POR_ITEM_ATTRIBUTE_VALUES

Context Prompt – TestSandeep

DFF View Name – SANDEEP_DFV (Name should be ending with _DFV)

 

 

Sandeep DFF on table POR_ITEM_ATTRIBUTE_VALUES has been created successfully.

 

 

Flexfield >>Descriptive >> Segment >> select segment and create attribute as standard DFF

 

 

Now we can use this DFF at any page. In case any LOV is required define the segment and add the value set with attribute.

 
 

Make Dependent VS Enterable Without Passing Parent VS

While creating dependent value set in EBS, we found dependent (child) field is greyed (disabled) at beginning until parent field gets some value.

Requirement is to enable child field from beginning even though parent field does not have any value passed.

Supplier site is dependent on supplier and hence is disable initially and get enabled once supplier name will have value.

Above will be achieved by adding below in where condition –

WHERE and ORDER BY

Vendor_id = :$FLEX$.Supplier_test

Solution dependent VS can be enabled since beginning by modifying the above WHERE condition as below –

WHERE and ORDER BY

Vendor_id = :$FLEX$.Supplier_test:NULL

This will enable site value set.

From : Dixidha

More than 2 column values in LOV

To enable LOV on a parameter, a Table value set is associated with parameter field. While running LOV displays Value and Meaning values in LOV window.

Requirement is to add extra additional fields in LOV.

Fox example if we have supplier site LOV along with site code and site name, supplier name will add extra advantage to pick the right site if we have same name sites available.  

Solution is additional columns option available on value set definition form.

Syntax

Column_name1 “Alias” (*),Column_name2 “Alias” (*),….

                                                                                                                                                                                                                       

 

Craeted VS supplier site and added below in additional columns

 segment1 “VendorName”(*),vendor_id ”Vendor_id”(*)

Supplier LOV shows vendor name, vendor number and vendor id.

From : Dixidha

Value for the Flexfield Segment does not Exist in the Value Set

 

 

Defining a value set on any DFF will disallow to search records having non eligible (out of value set range) segment values.

 

A good example of this is to create a DFF of segment project task name. Initially associate a value set of Table type which shows all tasks from pa_tasks_all.

We create some records using assigned DFF. After sometime update value set definition to restrict disabled/end dated tasks.

 

Now search for records which has been created earlier and having disabled tasks assigned. Oracle form will throw below error message –

 

 

Approach – Try restricting the validation query while search.  

 

Solution – We faced similar issue and did below steps to overcome issue –

           1) Created a profile (XXXXX_SANDEEP_PRF) and set value as N

           2) Updated the value set query as

 

           Current condition –

           WHERE   sysdate between start_date and nvl(completion_date,sysdate)

 

           Updated Condition –

            WHERE  (sysdate between start_date and nvl(completion_date,sysdate))

                               OR

                               (nvl(:$PROFILES$.XXXXX_SANDEEP_PRF,'Y')='N')

           3) Use form personalization to change the profile value in cache in search and edit mode

           4) Set the profile value =Y, when in Edit mode. This will fail the second condition and only first 

                     condition will be valid and so only active task will appear in LOV.

          Set the profile value = N when in search mode. First condition will be failed since task is  

              end dated but profile condition will pass and so record will appear without error.

            5)  Did not try this in same form. We have created 2 form functions ( Entry (profile Y) and 

                 Enquiry (profile N) mode)

 
 

 

Change the profile value at run time in Core Forms

We have one custom profile which value is set at different levels. Need is to change the value while working on core forms.

                                                

This can be achieved using form personalization. Form personalization has 4 different actions type.

    1)    Property

    2)    Message

    3)    Builtin

    4)   Menu

 

Builtin provides capability to change the profile value during run time in cache.

 

 

This will change the profile value in cache during run time J

 

 
 

 

FNDLOAD for Audit Configurations

Below is the LCT file and syntex to download the LDT version of oracle Audit configurations


File Name - affaudit.lct

FNDLOAD apps/$apps_Pwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/affaudit.lct $file_Name.ldt FND_AUDIT_GROUPS GROUP_NAME="$Group_Name"

 

How to restrict LOV data in EBS

Requirement is to restrict some data from LOV available on OAF pages or Core Forms.

 

If LOV is available at Core forms, it’s difficult to restrict data as core form personalization does not support this level restriction.

If we are at OAF page view extension (OAF customization) is the option to restrict LOV data. In next lines we shall discuss an easy method to restrict data in LOV for both Core forms and OAF pages.

 

How to achieve this?

It’s quite easier to restrict LOV data using Oracle provided data restriction method. Oracle recommends VPD for data restriction, which can be a better approach over OAF customization.

This can be useful for core forms as well.

 

Use below steps to implement this –

>Find out the source for LOV data –

OAF Page – Go to about this page >> Find associated VO >> Check the underlying SQL

>> Find the table/view having actual data >> Use VPD to restrict the data on source table/view

 

Core Form – Get the fmb file (from AU_TOP) and open in form builder >> Find the associated LOV >> Find Attached Data Source

>> Find the table/view having actual data >> Use VPD to restrict the data on source table/view

 

Use of VPD over OAF customization will be easy to maintain and deploy to higher instances.

 

Example:

Restrict Create Order options on order (Buyer work center) page:

Create LOV has 2 options 1) SPO and 2) CSPO >> we need to exclude SPO option

 

Find the source for LOV data and got that source table is PO_DOC_STYLE_LINES_TL

 

Created function to restrict SPO on the source table

   

                     

                      

                       

 

 

 

 

 

 

Add this function to policy.

 

 

 

 

 

 

 

 

 

 

 

Go to application and check the create option –

 
 

FNDLOAD+

FNDLOAD+ is extended (customized) standard FNDLOAD utility to upload LDT files.

 

How to use -

• Login to server
• Go to any directory (/staging/Sandeep)
• Place all your LDT files
• Call FNDLOAD+

 

User Inputs -

 FNDLOAD+ will ask for Apps DB password

 

Syntax-

[Unix server sandeep] $ FNDLOAD+

 

What is  +  in this?

• Not required to use complete FNDLOAD syntax
• Not required to know the LCT file name for upload
• Single run is required to upload all LDT files available in current dir

 

 

How to get FNDLOAD+ available in my environments –

• Check the $PATH variable       (echo $PATH)
• Go to any of the defined dir  (cd ….)
• Create file Sandeep.sh
• Create FNDLOAD+ (cp Sandeep.sh FNDLOAD+)                                                              

***** Only to UPLOAD ldt files*************

 

 

UPLOAD LDT Files using shell script

Requirement is to migrate the configuration files (LDTs) across environments.

 

Resolution - FNDLOAD is Oracle provided utility to achieve this by passing certain inputs. The required inputs are

1)       DB credentials

2)       Respective LCT name

This is very commonly and frequently used utility in EBD development.

 

Through this post we are sharing a shell script which can exclude LCT name parameter knowledge for LDT migration.

 

# !/bin/sh

 

echo "Enter APPS password : "

stty -echo

read v_apps_schema

stty echo

 

for file in `ls *.ldt`

do

v_ext=`echo | cut -d "." -f2`

v_lct=`grep "patch/115/import/" $file | cut -d "@" -f2`

echo "File Name    :$file" 

echo "Control File :$v_lct"

FNDLOAD apps/$v_apps_schema 0 Y UPLOAD @$v_lct $file

if [ $? != 0 ];then

echo "ERROR - Failed while uploading $file" 

else

echo "Uploaded $file successfully " 

fi

done

 

• Copy the above script and save this as Sandeep.sh
• $Dos2unix Sandeep.sh
• Place the source LDT files and Sandeep.sh
• $sh Sandeep.sh

 

How to get JDBC Connection details

In EBS JDBC connection are required while migrating XML files to other environments. XML files are source for XML publisher reports and OAF personalization.

Like FNDLOAD Oracle has provided JPXimporter and XDOloder java utilities to inport XML data into standard database tables. Java utilities require JDBC connection in order to execute and hence below are 2 methods to get JDBC connection.

 

 

Method 1 > Using Database

Parameters	Oralce Functions
Host Name/IP Address	UTL_INADDR.get_host_address
SID	sys_context('userenv','instance_name')
Port	Hard Code

 

 

 

Method 2 > Using context files

Server has context file to store all context details. One can connect to server and extract the jdbc connection string using unix command.

 

lv_contxtfile=`echo $CONTEXT_FILE`

echo "CONTEXT_FILE  : $lv_contxtfile"

lv_jdbc=`grep '</jdbc_url>' $lv_contxtfile | cut -d "@" -f2 | cut -d "<" -f1`

echo "JDBC COnnection String : $lv_jdbc"

 

o    Echo $CONTEXT_FILE will list the context file name with full path

o    Grep will search string “</jdbc_url>” in context file and returned line will be cut from literal “@”. –f2 will return the right side portion from “@”.

o     Which will be cut further with literal “<” and -f1 will return the left side portion now.

o The final output should be complete JDBC_STRING, which can be used.

 

 

** Thanks to my colleagues Srini & Manish for above info **

 
 

Excel Output Without XML publisher

Requirement– There are limited formats available to view Concurrent Program output. XML is one format which is not available. 

                    

Available Options – XML publisher is one frequently used solution, which has capability to display output in PDF, EXCEL and RTF formats. EXCEL file generated using xml processing is having more size compare to actuals.

                                    

Solution – This post is to discuss another way to generate output file in EXCEL format without using XML publisher. A small setup change is required.

1. Create a concurrent program having PL/SQL as executable
2. Select the output type as any available option (ex - PCI)                              
3. Print the output using FND_FILE.PUT_LINE(FND_FILE.OUTPUT,’Output Message’);
4. Change the Mime type(view option) as EXCEL for PCL file format
5. Run the concurrent program
6. Select Tool>>Copy option to see the output

Script to Update the Mime Type

Limitation – To view multi column format, use a separator during output print. Once output is opened in excel use excel options to display multi column view by using used separator.  

Emailing Concurrent Program Output

Requirement –  It is very common that business request to share concurrent program output file on every run.

 

 

Resolution – There are multiple options available to notify the output via email. Some of those are as below -

• - PL/SQL program unit to send the output file
• - Unix commands to send the output file
• - Email bursting concepts, if it is XML publisher report

 

Each program requires, additional effort/custom programs to achieve the emailing feature. In this post we shall learn the options already available in system. Which need small configuration change and CP will send the o/p as email.

 

 

 

Steps :-

1)      Configuration Change – 2 profile needs to be set.

·        FND: SMTP Host   >> This to store the smtp server host details

·        FND: SMTP Host  >> This to store the smtp server port details

 

 

2)     While submitting the CP, click the Delivery Opts (B)

 

 

3)     Go to Email tab

 

 

4)     Provide the To/From email address and Subject line, then press OK (B)

5)     Submit/Schedule the program

Email bursting should be better approach, if we are working with XML publisher report.

Program will send the output file to specified email address. Program log file can be referenced in case any issue occurred.

Profile to Enable Examine Option

Requirement -  Examine option in oracle core forms is not opening

On Oracle forms getting below error, while trying to explore examine options

Resolution -  Set the profile “Utilities:Diagnostics” to YES

This profile will enable examine option J

Check the examine option again