Edi Yanto (何 萬 新)

Oracle Reports with Flexfield Data as Parameter

Posted on: April 29, 2014


These are the basic steps we should write an Oracle Reports that accesses flexfields data:

  1. Define “Before Report” trigger
    • SRW.USER_EXIT(‘FND SRWINIT’);
  2. Define “After Report” trigger
    • SRW.USER_EXIT(‘FND SRWEXIT’);
  3. Define
    Name P_CONC_REQUEST_ID
    Datatype Number
    Width 20
    Initial Value
    Name P_FLEXDATA
    Datatype Character
    Width 600
    Initial Value (SEGMENT1||’\n’||SEGMENT2||’\n’||SEGMENT3||’\n’||SEGMENT4||’\n’||SEGMENT5||’\n’||SEGMENT6||’\n’||SEGMENT7||’\n’||SEGMENT8||’\n’||SEGMENT9||’\n’||SEGMENT10||’\n’||SEGMENT11||’\n’||SEGMENT12||’\n’||SEGMENT13||’\n’||SEGMENT14||’\n’||SEGMENT15||’\n’||SEGMENT16||’\n’||SEGMENT17||’\n’||SEGMENT18||’\n’||SEGMENT19||’\n’||SEGMENT20||’\n’||SEGMENT21||’\n’||SEGMENT22||’\n’||SEGMENT23||’\n’||SEGMENT24||’\n’||SEGMENT25||’\n’||SEGMENT26||’\n’||SEGMENT27||’\n’||SEGMENT28||’\n’||SEGMENT29||’\n’||SEGMENT30
  4. Define other parameters on report builder
    Name Datatype Width
    P_SOB_ID Number 20
    P_COA_ID Number 20
    P_ACC_FROM Character 2000
    P_ACC_TO Character 2000
  5. Define WHERE_FLEX placeholder column as lexical parameter (we want to see the value of this column)
  6. Call FND FLEXSQL to populate P_FLEXDATA and WHERE_FLEX parameter in “Before Report” trigger
    fnd_flexsql

7. Define report query
SELECT &P_FLEXDATA C_FLEXDATA, SEGMENT1||’.’||SEGMENT2||’.’||SEGMENT3||’.’||SEGMENT4||’.’||SEGMENT5||’.’||SEGMENT6||’.’||SEGMENT7 coa
FROM GL_CODE_COMBINATIONS
WHERE &WHERE_FLEX
AND CHART_OF_ACCOUNTS_ID = :P_COA_ID

8.  Create and Populate value for formula columns C_FLEXFIELD (segment values) and C_DESC_ALL (segment descriptions)
c_flexfield
c_desc_all

9. Register report and parameters on oracle apps
param

10. Testing report on apps using output type “XML”
– Report parameters
parameter_report
– Output
output_xml

Reference: Oracle Reports 2.5 Flexfield Support API

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,368 other followers


Edi Yanto
Oracle Applications Technical Consultant
View Edi Yanto's profile on LinkedIn

My Certifications






Follow me on Twitter

Archives

April 2014
M T W T F S S
« Aug   May »
 123456
78910111213
14151617181920
21222324252627
282930  

Visitors

Categories

Blog Stats

  • 140,585 hits

Users Online

web counter

Pages

%d bloggers like this: