FND USER EXIT SYNTAX

FND FLEXSQL - This user exits allows you to use Flex fields in Reports

Call this user exit to create a SQL fragment usable by your report to tailor your SELECT
statement that retrieves flexfield values. This fragment allows you to SELECT flexfield
values or to create a WHERE, ORDER BY, GROUP BY, or HAVING clause to limit or
sort the flexfield values returned by your SELECT statement. You call this user exit once
for each fragment you need for your select statement. You define all flexfield columns in
your report as type CHARACTER even though your table may use NUMBER or DATE or
some other datatype.
Syntax:
FND FLEXSQL
CODE=? flexfield code?
APPL_SHORT_NAME=? application short name?
OUTPUT=?: output lexical parameter name?
MODE=?{ SELECT | WHERE | HAVING | ORDER BY}?
[DISPLAY=?{ALL | flexfield qualifier | segment number}?]
[SHOWDEPSEG=?{Y | N}?]
[NUM=?: structure defining lexical? |
MULTINUM=?{Y | N}?]
[TABLEALIAS=? code combination table alias?]
[OPERATOR=?{ = | < | > | <= | >= | != | ?||?|
BETWEEN | QBE}?]
[OPERAND1=?: input parameter or value?]
[OPERAND2=?: input parameter or value?]



e.g-

SRW.USER_EXIT('FND FLEXSQL CODE="MSTK" NUM=":P_STRUCT_NUM" DISPLAY="ALL"
APPL_SHORT_NAME="INV" OUTPUT=":P_ITEM_FLEXDATA" MODE="SELECT" TABLEALIAS="msi"');




FND FLEXIDVAL - This user exits allows you to use Flex fields in Reports

Call this user exit to populate fields for display. You pass the key flexfields data retrieved
by the query into this exit from the formula column. With this exit you display values,
descriptions and prompts by passing appropriate token (any one of VALUE,
DESCRIPTION, APROMPT or LPROMPT).
Syntax:
FND FLEXIDVAL
CODE=? flexfield code?
APPL_SHORT_NAME=? application short name?
DATA=?: source column name?
[NUM=?: structure defining source column/lexical?]
[DISPLAY=?{ALL| flexfield qualifier| segment number}?]
[IDISPLAY=?{ALL| flexfield qualifier| segmentnumber}?]
[SHOWDEPSEG=?{Y | N}?]
[VALUE=?: output column name?]
[DESCRIPTION=?: output column name?]
[APROMPT=?: output column name?]
[LPROMPT=?: output column name?]
[PADDED_VALUE=?: output column name?]
[SECURITY=?: column name?]

---

These two user exits are used for the Key Flex field reporting. Where you have to capture and print the date which stored in the segments of the table.

FND FLEXSQL is used to capture the data from segments.
FND FLEXIDVAL is used to populate the captured segment values.

If you want to use these user exits then we have follow below steps :

1. Create 3 Variables

Name DataType Initial value
P_CONC_REQUEST_ID Number(20) --
P_Struct_Num Number(20) --
P_Flex_Data Char(6000) (Segment1||' '||Segment2......||Segmentn)

2. In Before Report Trigger

SRW.Reference(:P_Struct_Num);
SRW.User_Exit('FND SRWINIT');
SRW.User_Exit('FND FLEXSQL
CODE="GL#"
NUM=":P_Struct_Num"
Appl_short_Name="SQLGL"
Output=":P_Flex_Data"
Mode = "Select"
Display ="All"');
compile and close.

3. In After Report Trigger
SRW.USER_EXIT('FND SRWEXIT');
compile and close.

4. Go to Data Model in Report Builder
selet Last_update_date,&P_Flex_Data C_Flexdata from gl_code_combinations
where chart_of_accounts_id=:P_Struct_Num

5. got to Data model create layout using wizard.

6. Create a formula column with CF_Data datatype char(6000)

In that formula column

SRW.REFERENCE(:P_Struct_Num);
SRW.REFERENCE(:C_Flexdata);
SRW.USER_EXIT('FND FLEXIDVAL
CODE="GL#"
NUM=":P_Struct_Num"
Appl_short_Name="SQLGL"
Date=":C_Flexdata"
Value = ":CF_Data"
Mode = "Select"
Display ="All"');
return (:CF_Data);

compile and close.

7. Register the report in the applications and run it.