AOL - Creating a Custom Account Flex Field Parameter for Concurrent Program in Oracle

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