Friday, May 1, 2015

AOL - Creating Custom Account Flex Field Parameter in Concurrent Programs

We have seen in seeded GL reports that take the accounting flex field as parameters, the DFF form opens on the SRS form when we click on the flex field parameter. The user enters the flex field into the form. The flex field is then transferred into the report and the output is generated. This is a very handy way of allowing the user to enter the Accounting KFF.

To create a custom accounting flex field parameter, Below are the steps

Step 1: Create a RDF(XXAA_ACCT_FLEXFIELD_REP.rdf) with two(P_ACCT_LO, P_ACCT_HI) parameters, below is the query.

SELECT
      GCC.CODE_COMBINATION_ID
      ,GCC.CONCATENATED_SEGMENTS
      ,GCC.SEGMENT1
      ,GCC.SEGMENT2
      ,GCC.SEGMENT3
      ,GCC.SEGMENT4
      ,GCC.SEGMENT5
FROM GL_CODE_COMBINATIONS_KFV GCC
WHERE 1=1
      AND gcc.segment1 BETWEEN NVL (SUBSTR (:P_ACCT_LO, 1, 2), '00') AND NVL (SUBSTR (:P_ACCT_HI, 1, 2), '99')
      AND gcc.segment2 BETWEEN NVL (SUBSTR (:P_ACCT_LO, 4, 3), '000') AND NVL (SUBSTR (:P_ACCT_HI, 4, 3), '999')
      AND gcc.segment3 BETWEEN NVL (SUBSTR (:P_ACCT_LO, 8, 4), '0000') AND NVL (SUBSTR (:P_ACCT_HI, 8, 4), '9999')
      AND gcc.segment4 BETWEEN NVL (SUBSTR (:P_ACCT_LO, 13, 4), '0000') AND NVL (SUBSTR (:P_ACCT_HI, 13, 4), '9999')
      AND gcc.segment5 BETWEEN NVL (SUBSTR (:P_ACCT_LO, 18), '000') AND NVL (SUBSTR (:P_ACCT_HI, 18), '999')
ORDER BY 3,4,5,6,7

 


Compile the RDF and save

Step 2:
Create a value set(XXAA_GL_SRS_LEDGER_FLEXFIELD) with validation type as Pair
 
Navigation : System Administrator  àApplication àValidation àSet

Enter the value set name and select the validation type as Pair and click on the edit information

Pair :
  Use the Validation Rules window to define special validation for a Special value set. You also use this window to define validation routines for a Pair value set.

You can use this region to define a value set that lets your users enter an entire key flexfield combination within a single report parameter. For example, you may want to pass concatenated Accounting Flexfield segments as a parameter to a report. With this type of value set, a user can enter the report parameter and then see the "normal" behavior of a key flexfield, such as the key flexfield window and segment validation associated with that key flexfield. You use Oracle Application Object Library flexfield routines for these special value sets

Enter the below code and save
-->Event : EDIT 
FND POPIDR APPL_SHORT_NAME="SQLGL" CODE="GL#" REQUIRED="N" DISPLAY="1" DISPLAY="2" DISPLAY="3" DISPLAY="4" DISPLAY="5" VALIDATE="NONE" SEG=":!VALUE" DESC=":!MEANING" NAVIGATE="!DIR"

--> Event : VALIDATE 
FND VALIDR APPL_SHORT_NAME="SQLGL" CODE="GL#" VALIDATE="NONE" REQUIRED="N" DISPLAY="1" DISPLAY="2" DISPLAY="3" DISPLAY="4" DISPLAY="5" DESC=":!MEANING" SEG=":!VALUE"

Step 3 : Create a Concurrent Program(XXAA Account Flex Field Report) 
Note : Port the RDF(XXAA_ACCT_FLEXFIELD_REP.rdf) file into unix server

Log in to Oracle Apps with System Administrator responsibility.
Navigation: Concurrent àProgram àExecutable
 

Executable : Enter the executable name, short name and RDF name.
 
Concurrent Program : Enter the program name, short name and select the executable name and select the output type format as XML

Save the form and click on Parameters button.
 

Step 4 : Create below parameters 
1)      Parameter : From Account
Select the value set as we created in step 2(XXAA_GL_SRS_LEDGER_FLEXFIELD)
Enter the token value : P_ACCT_LO
 

2)      Parameter : To Account
Select the value set as we created in step 2(XXAA_GL_SRS_LEDGER_FLEXFIELD)
Enter the token value : P_ACCT_HI
 
Save it

Step 5:
Assign the concurrent program to the request set
 

 
Save and close the form.

Step 6 :  Create data definition, data temple and upload the rtf file 
Date Definition :

Data Template :
 


Step 7 : Run the concurrent program from any Payables responsibility

Select the from account and to account values and submit the program


Check the report output

2 comments:

Sri Vignesh said...

may i know what is 1,2,3,4,5 denotes here in edit and validate section.

Raju Ch said...

Those are 5 Segments

Post a Comment

Best Blogger TipsGet Flower Effect