Search This Blog

Friday, May 27, 2011

Conditionally Enabling/Disabling Concurrent Program Parameters

Introduction : This post provides the guidance to the user with the necessary information for creating parameters for a concurrent program and making them conditionally enable/disable.
Steps to be followed :-
1.Create a procedure with two input parameters.
2.Create an executable with execution method as PL/SQL Procedure
  and execution file as the procedure created in step1.
3.Create a concurrent program with executable created in step2.
4.Assign the concurrent program to a request set to run the
  concurrent program from a responsibility.
5.Create two two three values sets.
6.Create three parameters for the concurrent program
  created in step 3 using value sets created in step 5.
7.Go to the responsibility to which the concurrent program assigned in step 4 to run the program..

Installation Steps

Step1 : Create procedure
CREATE OR REPLACE procedure test_proc(
errbuf OUT varchar2,
retcode out NUMBER,
p_One IN VARCHAR2,
p_two_dummy IN VARCHAR2,
p_two IN NUMBER)
is
begin
fnd_file.put_line (fnd_file.LOG,'Log File');
fnd_file.put_line (fnd_file.OUTPUT,'Out put File' || p_one || '   ' || p_two);
end test_proc;
/

Run above procedure in SQL * Plus / Toad.Step2:  Creating Executable:Navigation :- System Administrator Responsibility → Concurrent →  Program → Executable
Executable : TEST_PROC
Short Name: TEST_PROC
Application: Custom Development
Execution Method: PL/SQL Stored Procedure
Execution File Name : TEST_PROC

Save and close the Concurrent Program executable window.

Step 3: Creation of Values Sets
 3.1 Creating Value set
       Value Set Name  :LAMS_SRS_YES_NO_MAND
       Description     :Yes/No   
       List Type       : List Of Values
       Format          : Char
       Security Type   : No Security
       Validation Type : Table

       Edit Information:
            Table Name : FND_LOOKUPS
            Value      : MEANING         TYPE :VARCHAR2
            ID         : LOOKUP_CODE     TYPE :VARCHAR2
            Where/Order By : WHERE Lookup_type = 'YES_NO'    

Click on Test → Ok → Save and close Validation Table Information window and then value set window.

  3.2 Creating Value set for Dummy Parameter:

      Value Set Name: CST_SRS_MARGIN_ORDER_DUMMY2
      List Type     : List Of Values
      Format        : Char(Check Uppercase Only)
      Security Type : No Security
      Validation Type : None
 

  3.3 Value set for Sales Order Numbers:      Value Set Name : ONT_ORDER
      List Type   : List Of Values
      Format      : Number(Check Numbers Only)
      Security Type : No Security
      Validation Type : Table

      Edit Information:
        Table Name : OE_ORDER_HEADERS_ALL
        Value   : ORDER_NUMBER           TYPE :NUMBER
        Where/Order By : where :$FLEX$.XXLSS_ORDER_DUMMY = 'Y'     
 

Click on Test → Ok → Save and close Validation Table Information window and then value set window.
 Step 4 : Creating a concurrent Program

   Program          : TEST_PROC
   Short Name       : TEST_PROC
   Application      : Custom Development

   Executable  Name : TEST_PROC

Save and click on Parameters.
Step 5: Creation of Parameters
  5.1 Yes/No Parameter
         Seq          : 10
         Parameter    : p_one
         Value Set    : AMS_SRS_YES_NO_MAND
         Enabled      : Yes
         Required     : Yes
         Display      : Yes

  5.2Creating Dummy Parameter
         Seq : 15
         Parameter : p_two_dummy
         Value Set    : CST_SRS_MARGIN_ORDER_DUMMY2
         Default Type : SQL Statement
         Default Value: select decode        (:$FLEX$.XXONT_OSR_YES_NO,'Y','Y','N',NULL) from dual
         Enabled   : Yes
         Required  : No
         Display   : No

  5.3 Creating Conditional Parameter
          Seq : 20
          Parameter :  p_two
          Value Set : ONT_ORDER      
           Enabled   : Yes
           Required  : Yes
            Display   : Yes


Step 6 : Assigning the concurrent program a request group:Navigation:- System Administrator → Security → Responsibility → Request
Query for the Request group, 'OM Concurrent Programs'

Select Request Type and click on Add New
Type : Program
Name : TEST_PROC
Application : Custom Development

Save and close the Window.
Step7 : Running  the concurrent request:-
Navigation: Order Management Super User → Reports, Requests → Run Requests  →
  Give Program Name as 'TEST_PROC'
 When you select Yes for the first parameter then second parameter become mandatory.

FND_LOAD

Data Synchronization: Data Synchronization is a process in which some setup data would be synchronized, and this would be more important when you are working in oracle application
development/implementation project. The equally important that AOL data Migration takes place necessary to synchronize the data across databases instance during
• Installations (New desc. flex field creations etc)
• Upgrades (Apps upgrade etc)
• Maintenance (Value set changes etc)


FNDLOAD is one and only oracle solution. It can be defined as a concurrent program that can move Oracle Applications data between database and text file representations. Or this can be defined as
FNDLOAD can download data from an application entity into an editable text file, which can be uploaded to another database.

Working of FNDLOAD: Conversion between database format and text file format is specified by a configuration file. Oracle does provide configuation file and it is important to just pass the
configuration file name and then just call the loader and leave everything .


These are the extensive list which can be done through FNDLOAD
• Concurrent Programs, Executables
• Request Groups, Request Sets
• Profile Options
• Key and Descriptive Flexfields
• Menus and Responsibilities
• Forms and Form Functions
• Attachments
• Messages
• Value Sets and Values
• Lookup Types
• User Responsibilities
• Printer Definitions
• FND Dictionary
• Help Configuration
• Document Sequences
• Concurrent Manager Schedules


Advantages using FNDLOAD are :• Because downloaded data is stored in a text file, version      administration is possible
• No learning curve.
• Fully supported and recommended by Oracle
• Capture the migrations in a file and use it during installations, clones etc. to migrate in batch
• Pin-point when something happened and where (database) easily
• Your AOL data migration process is now simplified and streamlined.


Disadvantages:• Applications patching mechanisms use FNDLOAD heavily – possibility of negative impact is
not zero
• UPLOAD_MODE=REPLACE only for menus
• No validation against migrating database/instance sensitive data.


Syntax : To use FNDLOAD, the following syntax is needed.
FNDLOAD apps/appspwd 0 Y mode configfile datafile entity [parameter1…..]
• The mode is either DOWNLOAD or UPLOAD.
• The configfile is the file that Fndload needs to download on upload data.
• The data file is the output file, in which the downloaded data is written
• The entity is the entity you want to download,


Modes of Operation:Two modes - Upload and Download…
1. Example of download:
FNDLOADapps/pwd 0 Y DOWNLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt \
PROGRAM CONCURRENT_PROGRAM_NAME= concurrent_program_short_name>
APPLICATION_SHORT_NAME=

2. Example of Upload
FNDLOAD apps/pwd 0 Y UPLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt -
CUSTOM_MODE=FORCE undocumented parameter


Sample Script Code:

1 - Printer StylesFNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct
file_name.ldt STYLE PRINTER_STYLE_NAME=”printer style name”


2 - LookupsFNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct
file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=”prod”
LOOKUP_TYPE=”lookup name”


3 - Descriptive Flexfield with all of specific Contexts
FNDLOAD
apps/apps@seed115 O 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=”prod” DESCRIPTIVE_FLEXFIELD_NAME=”desc flex name”
P_CONTEXT_CODE=”context name”


4 - Key Flexfield StructuresFNDLOAD apps/apps@seed115 O 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=”prod” ID_FLEX_CODE=”key flex code”
P_STRUCTURE_CODE=”structure name”


5 - Concurrent Programs
FNDLOAD
apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct
file_name.ldt PROGRAM APPLICATION_SHORT_NAME=”prod”
CONCURRENT_PROGRAM_NAME=”concurrent name”


6 - Value SetsFNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct
file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME=”value set name”


7 - Value Sets with values
FNDLOAD
apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct
file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME=”value set name”


8 - Profile OptionsFNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct
file_name.ldt PROFILE PROFILE_NAME=”profile option” APPLICATION_SHORT_NAME=”prod”


9 - Requset GroupFNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct
file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME=”request group”
APPLICATION_SHORT_NAME=”prod”


10 - Request SetsFNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct
file_name.ldt REQ_SET APPLICATION_SHORT_NAME=”prod” REQUEST_SET_NAME=”request
set”


11 - ResponsibilitiesFNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct
file_name.ldt FND_RESPONSIBILITY RESP_KEY=”responsibility


12 - MenusFNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct
file_name.ldt MENU MENU_NAME=”menu_name”


13 – Forms/FunctionsFNDLOAD apps/apps@seed115 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct
file_name.ldt FND_FORM_CUSTOM_RULES The Upload syntax for all styles: FNDLOAD
apps/apps@seed115 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt

14. User/ResponsibilitiesFNDLOAD apps/apps@seed115 0 Y DOWNLOAD @FND:patch/115/import/afscursp.lct
file_name.ldt FND_USER Then UPLOAD FNDLOAD
apps/apps@seed115 0 Y UPLOAD [UPLOAD_PARTIAL] @FND:patch/115/import/afscursp.lct file_name.ldt FND_USER []