Dynamic SQL query in Data Template

Recently I came across couple of questions on dynamic/conditional query within Data template and those could be easily answered through usage of substitute variables.

So what is substitute variable and how to define and use it?

These are the placeholders and get replace with the actual values before the query execution. We can use Data Template parameters, global variables from PL/SQL default package, element names from data structure section and lexical (KFF variables) as substitute variables.

The substitute variable could be use to define the complete or part of SQL query. In following example, the "where clause" of the query set dynamically based on the "pwherecaluse" global variable defined in default PL/SQL package (Employee).

dt1.jpg

Lets have a look on BeforeReportTrigger function from employee package, which is default pl/sql package for this Data Template.
dt2.jpg

Data Engine executes the BeforeReportTrigger trigger before executing any SQL query. The pwhereclause variable will be set based on the p_DEPTNO parameter. If the p_DEPTNO is 10, the final query will be

dt3.jpg

The same way we can use the substitute variable to define complete query dynamically and the query_text can be constructed conditionally within beforeReportTrigger.

<sqlStatement name="Q1">
<![CDATA[ SELECT &query_text ]]>
</sqlStatement>

Lexical variable can be use in similar way to define a dynamic query. This is EBS/Oracle Report concept to use FND Flex APIs to implement KFF within Data Template.

Here is sample implementation for one of the EBS GL report.

dt4.jpg

<sqlStatement name="Q_MAIN">
<![CDATA[
SELECT
S.user_je_source_name Source,
B.name Batch_Name,
B.default_effective_date Batch_Eff_date,
B.posted_date Batch_Posted_Date,
B.je_batch_id Batch_Id,
B.running_total_accounted_dr B_TOT_DR,
B.running_total_accounted_cr B_TOT_CR,
D.je_header_id Header_id,
D.name Header_Name,
C.user_je_category_name Category,
D.running_total_accounted_dr H_TOT_DR,
D.running_total_accounted_cr H_TOT_CR,
J.je_line_num Je_Line_Num,
decode(nvl(CC.code_combination_id, -1), -1, 'A',null)
FLEXDATA_H,
J.effective_date Line_Effective_Date,
J.description Line_Description,
J.accounted_dr Line_Acc_Dr,
J.accounted_cr Line_Acc_Cr,
D.currency_code Currency_Code,
D.external_reference Header_Reference,
&POSTING_STATUS_SELECT Recerence1_4,
nvl(J.stat_amount,0) Line_Stat_Amount,
GLL.description Batch_Type,
B.actual_flag Actual_Flag,
GLL2.meaning Journal_Type,
SOB.consolidation_sob_flag Cons_Sob_Flag,
&FLEXDATA_DSP FLEXDATA_DSP,
&FLEXDATA_SECURE FLEXDATA_SECURE
FROM
gl_lookups GLL,
gl_je_sources S,
gl_je_categories C,
gl_je_lines J,
gl_code_combinations CC,
gl_je_headers D,
gl_je_batches B,
gl_lookups GLL2,
gl_sets_of_books SOB
WHERE
GLL.lookup_code = B.actual_flag AND
GLL.lookup_type = 'BATCH_TYPE' AND
GLL2.lookup_type = 'AB_JOURNAL_TYPE' AND
GLL2.lookup_code = B.average_journal_flag AND
SOB.set_of_books_id = :P_SET_OF_BOOKS_ID AND
S.je_source_name = D.je_source AND
C.je_category_name = D.je_category AND
J.code_combination_id = CC.code_combination_id(+) AND
J.je_header_id = D.je_header_id AND
&CURRENCY_WHERE AND
D.je_source = NVL(:P_JE_SOURCE_NAME, D.je_source) AND
D.je_batch_id = B.je_batch_id AND
&POSTING_STATUS_WHERE AND
B.name = NVL(:P_BATCH_NAME, B.name) AND
&PERIOD_WHERE AND
B.set_of_books_id = :P_SET_OF_BOOKS_ID
ORDER BY
S.user_je_source_name,
B.actual_flag,
B.name,
B.default_effective_date,
D.name,
J.je_line_num
]]>
</sqlStatement>

Hopefully this will help you to have more control over your data generation process. All the sample templates and PL/SQL package mentioned in this article can be downloaded from following links.

emp_single_query.xml

EMPLOYEEB.pls

GLRGNJ_datatemplate.xml

Comments:

Hey , it was really very helpful . Thanks a lot. :)

Posted by Sookie on December 11, 2009 at 09:13 PM PST #

Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon.

Posted by Antonetta Goodkin on January 08, 2010 at 10:55 PM PST #

Your blog was very helpful..Thanks..

Posted by Siddharth on January 27, 2010 at 09:04 PM PST #

MQ998 Quad Band Spy Camera Touch Screen Sports Wrist Watch Phone - 1.5 Inch Touch Screen Spy Camera Bluetooth Vibration Watch Phone. Water Resistant watch phone.

Posted by wrist watch phone on April 29, 2011 at 05:11 AM PDT #

Very Interesting Post! Thank You For Thi Blog!

Posted by government foreclosure listings on April 29, 2011 at 07:31 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

bocadmin_ww

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today