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 –

 
 

What is Virtual Private Database (VPD)

VPD is new Oracle Database feature to enable policies on database objects ( i.e Table, Synonyms, View etc) This is used to control data access for security reasons.

 

What actually VPD does >> this append additional where condition to objects based on defined policies.  

 

How to use VPD>> below are the steps used to implement VPD in system.

1)     Identify the object:  Lest take APPS.PO_HEADERS_ALL (Synonym) for VPD testing.  

2)     Create the function: Create a function ( or package.function) which will return the required filter condition

3)     Add to policy – Use standard oracle API to create a policy for created function ( step 2)

  

Example –

Step#1) Assume PO_HEDAERS_ALL has 100 records. Will update DFF Column (Attribute1) =’Sandeep’ for any 10 records.

Requirement is  to restrict data having DFF value as ‘Sandeep’ for schema ‘xxxxx’.

 

Step#2) Created function Sandeep

 

 

 

 

 
 

 

 Step#3) 

 

 

 

 

 

 

  

Now run the below statement –

 

APPS Schema

Select count (1) from PO_Headers_all  

>> 100 rows selected

 

XXXXX Schema

Select count (1) from PO_Headers_all  

>> 10 rows selected

 

APPSQUERY Schema

Select count (1) from PO_Headers_all  

>> 100 rows selected

 

 

VPD is one of the major changes in MOAC concepts in R12.

 

Generic UNIX script to import OAF personalizations

Below is the piece of UNIX which will import OAF personalization to system.  This is a common script and will work for all CO, VO extension or other OAF personalization.

 

 

# ***********************************************************************************************************

#  Import OAF Personalizations

# ***********************************************************************************************************

echo "# ******************************************************************************" >> $v_log

echo "# Import OAF Personalizations" >> $v_log

echo "# ******************************************************************************" >> $v_log

echo ""

echo "Immporting OAF Personalizations...."

 

########### Import CO Extension & Other Personalizations ################################

for file in `find unzipped_folder | grep PG.xml`

do

                                v_file_name=`echo $file | rev | cut -d "/" -f1 | rev`

                                lv=`echo $file | sed s/$v_file_name/"@"/g | cut -d "@" -f1`

                                v_MDS_RN=`echo $JAVA_TOP/$lv`

                                v_rootPackage=`echo $lv | sed s/" unzipped_folder "/"@"/g | cut -d "@" -f2`

                                                               

                                echo "File Name : $v_file_name" >>$v_log

                echo "lv :: $lv" >>$v_log

                                echo "MDS_RN : $v_MDS_RN" >>$v_log

                                echo "Root Package : $v_rootPackage" >>$v_log

     

                                 java oracle.jrad.tools.xml.importer.XMLImporter ${v_MDS_RN}${v_file_name} -username apps -password $v_apps_schema -dbconnection "$lv_jdbc" -rootdir ${v_MDS_RN} -rootPackage $v_rootPackage >>$v_log

 

                                if [ $? != 0 ];then

                                                echo "ERROR - Failed while importing $file"   >>$v_log

                                                echo "ERROR - Failed while importing $file"  

                                else

                                                echo "Import $file successfully "  >>$v_log

                                fi

                                echo " " >> $v_log

done

 

 

########### Import VO Extension################################

for file in `find xxxxx | grep .jpx`

do

                                v_file_name=`echo $file | rev | cut -d "/" -f1 | rev`

                                lv=`echo $file | sed s/$v_file_name/"@"/g | cut -d "@" -f1`

                                v_MDS_RN=`echo $JAVA_TOP/$lv`

                               

                                                               

                                echo "File Name : $v_file_name" >>$v_log

                                echo "lv :: $lv" >>$v_log

                                echo "MDS_RN : $v_MDS_RN" >>$v_log

                                      

                                 java oracle.jrad.tools.xml.importer.JPXImporter ${v_MDS_RN}/${v_file_name} -username apps -password $v_apps_schema -dbconnection "$lv_jdbc" >>$v_log

 

                                if [ $? != 0 ];then

                                                echo "ERROR - Failed while importing $file"   >>$v_log

                                                echo "ERROR - Failed while importing $file"  

                                else

                                                echo "Import $file successfully "  >>$v_log

                                fi

                                echo " " >> $v_log

done

fi

# ******************************************************************************

 

 

 

Prerequisite is to place all java,class & XML files at correct folder path. For example if we are extending the agreement CO.  File structures should be –

 

xxxx.oracle.apps.po.documents.agreement.webui

*.class

*.java

xxxx.oracle.apps.po.documents.agreement.webui.customization.site/responsibility.0/responsibility_id

*.xml

 

 
 

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 **

 
 

SQL to find XML Files (RTF/Data Template/RTF ) Details

Given SQL is to fetch the files details uploaded through XML Publisher Administrator Responsibility

 

SELECT

 a.application_short_name,

  a.data_source_code,

  b.lob_code,

  b.lob_type,

  b.file_name,

  b.last_update_date

FROM

   XDO_DS_DEFINITIONS_VL a,

   XDO_LOBS b

WHERE a.data_source_code    =b.lob_code

AND a.application_short_name=’XXXXX’

AND a.data_source_code      =:ConCurrentProgram or Data Defintion Code;

;

 
 

Import data Template/RTF/Bursting control files

Problem is that any custom XML publisher report requires dependency on application access in PROD (XML Publisher Administrator (R)). All supporting files for XML report are being uploaded through application.

 

Requirement is to have any import method for XML publisher supporting files i.e.

Data Template file (XML file)

Bursting Control File (XML file)

Template (RTF file)

 

Resolution

XDOLoader is the standard OA utility to suffice this requirement. This is similar to unix based FNDLOAD utility and should be run from server with all parameters.  

 

Syntex

java oracle.apps.xdo.oa.util.XDOLoader  

MODE                                                                 UPLOAD/DOWNLOAD

–DB_USERNAME                                             apps user name

–DB_PASSOWRD                                             apps password

–JDBC_CONNECTION                                     jdbc connection string

–LOB_TYPE                                                       TEMPLATE_SOURCE/   
                                                                              DATA_TEMPLATE/
                                                                              BURSTING_FILE             

–APPS_SHORT_NAME                                     xxxxx

–LOB_CODE                                                       Data definition Code

–LANGUAGE                                                      en

–XDO_FILE_TYPE                                             RTF/XML-DATA-TEMPLATE
                                                                              /XML-BURSTING-FILE

[–CUSTOM_MODE]                                            FORCE

[–LOG_FILE]                                                       log file name

[-DEBUG]                                                             true/false

 

 

RTF File >>

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD appsPWD -JDBC_CONNECTION  "(DESCRIPTION= (ADDRESS=(

PROTOCOL=TCP)(Host=XXXXXXXXXX)(Port=100))(CONNECT_DATA=(SID=EBS123)))"  -LOB_TYPE TEMPLATE_SOURCE -APPS_SHORT_NAME XXXXX  -LOB_CODE DATA_DEFINITION_CODE -LANGUAGE en  -XDO_FILE_TYPE RTF -FILE_NAME 123.rtf -CUSTOM_MODE FORCE

 

 

 

Data Template File >>

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD appsPWD -JDBC_CONNECTION  "(DESCRIPTION= (ADDRESS=(

PROTOCOL=TCP)(Host=XXXXXXXXXX)(Port=100))(CONNECT_DATA=(SID=EBS123)))"  -LOB_TYPE DATA_TEMPLATE -APPS_SHORT_NAME XXXXX  -LOB_CODE  DATA_DEFINITION_CODE -LANGUAGE en  -XDO_FILE_TYPE XML-DATA-TEMPLATE -FILE_NAME 123.xml -CUSTOM_MODE FORCE

 

 

Bursting File >>

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD appsPWD -JDBC_CONNECTION  "(DESCRIPTION= (ADDRESS=(

PROTOCOL=TCP)(Host=XXXXXXXXXX)(Port=100))(CONNECT_DATA=(SID=EBS123)))"  -LOB_TYPE BURSTING_FILE -APPS_SHORT_NAME XXXXX  -LOB_CODE  DATA_DEFINITION_CODE -LANGUAGE en  -XDO_FILE_TYPE XML-BURSTING-FILE -FILE_NAME 123b.xml -CUSTOM_MODE FORCE

 

**Highlighted are user defined inputs

 

Concurrent Program Status

/* Query to check program status and completion time*/


SELECT fcr.REQUEST_ID,
  FU.USER_NAME,
  f.responsibility_name,
  fcr.USER_CONCURRENT_PROGRAM_NAME,
  DECODE(fcr.PHASE_CODE,'C','Completed','R','Running','E','Error','P','Pending','Other') PHASE,
  DECODE(fcr.STATUS_CODE,'C','Normal','E','Error','G','Warning','X','Terminated','Q','Sacheduled',fcr.STATUS_CODE)STATUS ,
  TO_CHAR(fcr.ACTUAL_START_DATE,'DD-MON-YYYY HH24:MI:SS') STARTED_AT,
  TO_CHAR(fcr.ACTUAL_COMPLETION_DATE,'DD-MON-YYYY HH24:MI:SS') ENDS_ON,
  ROUND((NVL(fcr.ACTUAL_COMPLETION_DATE,SYSDATE)-fcr.ACTUAL_START_DATE)*24*60) "Duration (mins)",
  fcr.argument_text,fo.file_type,fo.file_size,fo.file_name,LOGFILE_NAME, LOGFILE_NODE_NAME, OUTFILE_NAME,OUTFILE_NODE_NAME
FROM FND_CONCURRENT_REQUESTS fcr1,
  FND_CONC_REQ_SUMMARY_V FCR,
  FND_USER FU,
  fnd_conc_req_outputs fo,
  fnd_responsibility_vl f
WHERE FCR.REQUESTED_BY=FU.USER_ID
AND fcr.request_id = fo.concurrent_request_id(+)
and f.responsibility_id =  fcr.responsibility_id
and fcr1.REQUEST_ID = fcr.REQUEST_ID
AND USER_CONCURRENT_PROGRAM_NAME like ('%Programname%')
--and user_name like '%SANDEEP%'
ORDER BY NVL(fcr.ACTUAL_START_DATE,SYSDATE-1000) DESC;

Send Email using Task Scheduler

Requirement – Task scheduler to send any file as attachment.

 

Prerequisite – Should have SMTP details for any available email service

 

Resolution – Task scheduler has send email option, which is not helpful and people face many issues.

sendEmail is one utility, which can be used to fulfill the need. Download this executable file and save at system.

1.        Create a task in scheduler
2.        Select action- Run a program
3.      Give the sendEmail.exe in file name

1.  Start in (optional)=path of sendEmail.exe in your system
2.  Add arguments (Optional) – provide mailing details as mentioned below -

-f sandeep19rm@gmail.com -u Test Email -m Here is the Email Option! -a sandeep.txt -t sandeep@oalearning.com -cc learning@oalearning.com -s smtp.gmail.com:587 -xu username@gmail.com -xp password -o tls=yes

 

Options :

-f	From Email Address
-u	Mail Subject
-m	Mail Body
-a	Attachment file name
-t	To Email Address
-cc	CC Email Address
-bcc	BCC Email Address
-s	SMTP details (server:port)
-xu	Username
-xp	Password
-o	Mailing options