Business Intelligence (BI) - ERP - Dashboards

STEPS REQUIRED TO CREATE A SIMPLE FLEX FIELD REPORT (continued to FLEX Field Reports )

1. Open the Report in Report Builder.
2. Create User defined parameters as per our requirement.

3. Call FND SRWINIT User Exit in Before Report Trigger.

BEGIN
SRW.USER_EXIT (’FND SRWINIT’);
RETURN (TRUE);
END;

4. Call FND SRWEXIT User Exit in After Report trigger.

BEGIN
SRW.USER_EXIT (’FND SRWEXIT’);
RETURN (TRUE);
END;

5. Call FND FLEXSQL User Exit in Before Report Trigger.

BEGIN
SRW.USER_EXIT (’FND SRWEXIT’);
BEGIN
SRW.REFERENCE (’:P_STRUCT_NUM’);
SRW.USER_EXIT (’FND FLEXSQL
APPL_SHORT_NAME= “SQLGL”
CODE = “GL#”
NUM = “:P_STRUCT_NUM”
OUTPUT = “:P_FLEXDATA”
MODE = “SELECT”
DISPLAY = “ALL” ‘);
END;
RETURN (TRUE);
END;

Note: When we are calling multiple user exits in a trigger we should call these
Users exits in separate Blocks (i.e. BEGIN … END).

6. Create a report query in the Data Model.
SELECT &P_FLEXDATA C_FLEXDATA
FROM GL_CODE_COMBINATION
WHERE CHART_OF_ACCOUNT_ID = &P_STRUCT_NUM;

7. Create two Formula Columns C_VALUE and C_DESC_ALL.
8. Call FND FLEXIDVAL in the PL/SQL formula of C-VALUE to reference
Value Code Combination.
To retrieve the concatenated flexfield segment values and description you
Incorporate the AOL user exits in these columns
BEGIN
SRW.REFERENCE (’:C_FLEXDATA’);
SRW.FND (:P_STRUCT_NUM);
SRW.USER_EXIT (’FND FLEX_ID_VAL
APPL_SHORT_NAME = “SQLGL”
CODE = “GL#”
NUM = “:P_STRUCT_NUM”
OUTPUT = “:P_FLEXDATA”
VALUE = “:C_VALUE”
DISPLAY = “ALL” ‘);
RETURN (:C_VALUE);
END;

9. CALL FND FLEXIDVAL in the PL/SQL formula of C_DESC_ALL to retrieve Description Code Combination.

BEGIN
SRW.REFERENCE (’:C_FLEXDATA’);
SRW.FND (:P_STRUCT_NUM);
SRW.USER_EXIT (’FND FLEX_ID_VAL
APPL_SHORT_NAME = “SQLGL”
CODE = “GL#”
NUM = “:P_STRUCT_NUM”
OUTPUT = “:P_FLEXDATA”
DESCRIPTION = “:C_DESC_ALL”
DISPLAY = “ALL” ‘);
RETURN (:C_DESC_ALL);
END;

10. Run the report with default Layout.

11. Compile and copy .RDF in module specific directory.

12. perform all the pre-requisites to register the report

System Administrator(Responsibility)

Concurrent >Program> Executable

Note: All the FND Tables are stored in Application Object Library (AOL).

Filed under: AOL (Application Object Library ), DBA (DataBase Administrator), ERP, Forms, Interview Questions(& answers), Oracle Apps, PL/SQL, Reports, SQL

Free Dashboards