Enable/Disable Concurrent Program Parameters

Requirement - Business need is to have multiple parameters but end user will be allowed to use one at a time. selection of any parameter, should disable others.  

Solution - Below is one possible option which provides the same behavior in other way.

This requires one additional parameter as Selection/Choice which will decide the enabled parameter.

There will be as many dummy (non displayed) parameters as required parameters in program. actual parameters will be dependent on respective dummy parameters.

Dummy parameters will be dependent on selection parameters and hold values based on selection.

Example - User requires 2 parameters supplier number and name to be available on one report and only one should be selected at time. So the requirement was to make other disable. dependent parameters has limitations and it cannot flow bottom to top. we can achieve this by introducing one selection parameter, which can hold 2 values -

1)      Name – Supplier Name

2)      Number – Supplier Number

Initially both Supplier Number and Name range parameter will be disabled.

 

Selection = Name , will enable supplier Name parameter range.

Selection = Number , will enable supplier Number parameter range.

Implementation – Create 3 dummy parameter (1 displayable and 2 non displayable).

           

Parameter	Value Set	Enable	Display
Test1	VS0	Y	Y
Test2	VS1	Y	N
Test3	VS2	Y	N

Value Set - VS0, will hold below values

Parameter Test2 and Test3 :

 

Default Value  select decode(:$FLEX$.VS0,'NUMBER','Y',NULL) from dual

Test3 will have similar definition.

Value Set =  VS2

Default Value  select decode(:$FLEX$.VS0,'NAME','Y',NULL) from dual

Now include the below condition for Number and Supplier Value set definition –

Supplier Number Value set  -   :$FLEX$.VS1='Y'

Supplier Name Value set     -   :$FLEX$.VS2='Y'

          

Add Concurrent Program to Request Group Using Database

Requirement – Need to register custom concurrent program to request group without having system administrator responsibility

Solution – We can use below script to add custom program to respective request group  

DECLARE

BEGIN

FND_PROGRAM.add_to_group

(

 PROGRAM_SHORT_NAME =>'XX_MANAGERS'       

,PROGRAM_APPLICATION =>'AR'                             

,REQUEST_GROUP => 'Receivables All'

,GROUP_APPLICATION =>'AR'

) ;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('Object already exists');

END ;

/

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.

 

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

               

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"

Do we kNOw ????

Through this post, we highlight few points being used in oracle & application very often but still unknown....

1) ORA-0000 : normal,successful completion - For every successful transaction oracle returns ORA-0000.

2)Concurrent Programs use auto commit - If invoking same procedure from database do commit at end, to avoid different behavior as concurrent program.  

3)Change profile using FA resp - sometime few profiles need cache clear to reflect the change value. Changing  profile value using Functional Administrator responsibility does not require any cache clear.

4)Dummy Oracle Apps Link - mentioned url will launch the oracle application, create your login and do  practice oracle apps at home
  http://vis1200.solutionbeacon.net/OA_HTML/AppsLogin

Better Coding Practice - To Capture log messages

Once the code in production and stuck with some issue.tough to debug.So the best practice to write custom programs is -
1) Create a profile with default value 'N'
2) Put debug statement based on profile value
       IF fnd_profile.value('Custom Profile') = 'Y' THEN
           Interface - FND_FILE.PUT_LINE(FND_FILE.LOG,'Debug Level 1' );  
           Reports -  SRW.MESSAGE(1,'Debug Level 1')     
      END IF;
3) Run interface by setting the profile value 'Y'