eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE: APPS.FND_DSQL

Source


1 PACKAGE fnd_dsql AS
2 /* $Header: AFUTSQLS.pls 120.1.12010000.1 2008/07/25 14:23:38 appldev ship $ */
3 
4 
5 /* ----------------------------------------------------------------------
6 EXAMPLE
7 
8   Let's say you want to construct
9 
10   SELECT my_column1, my_column2
11     FROM my_table
12    WHERE my_varchar2_column = l_my_varchar2
13      AND my_date_column = l_my_date
14      AND my_number_column = l_my_number
15 
16 
17   If this select statement is used frequently and if l_my_* variables are
18   changing each time, you should use binding to improve performance.
19 
20   However if your select statement is constructed in different procedures
21   you cannot pass all binds between those procedures.
22 
23   Here how to use this package. You can call these functions from anywhere you
24   want. fnd_dsql package will keep track of bind variables.
25 
26   ...
27   l_my_varchar2 := 'fnd_dsql test';
28   l_my_date := to_date('02-JAN-1980','DD-MON-YYYY');
29   l_my_number := 12345;
30 
31   fnd_dsql.init;
32 
33   fnd_dsql.add_text('SELECT my_column1, my_column2 ' ||
34                     'FROM my_table WHERE my_varchar2_column = ');
35   fnd_dsql.add_bind(l_my_varchar2);
36 
37   fnd_dsql.add_text('AND my_date_column = ');
38   fnd_dsql.add_bind(l_my_date);
39 
40   fnd_dsql.add_text('AND my_number_column = ');
41   fnd_dsql.add_bind(l_my_number);
42 
43   l_cursor_id := dbms_sql.open_cursor;
44   fnd_dsql.set_cursor(l_cursor_id);
45 
46   --
47   -- l_dsql_text will be:
48   --  SELECT my_column1, my_column2
49   --   FROM my_table
50   --  WHERE my_varchar2_column = :FND_BIND1
51   --    AND my_date_column = :FND_BIND2
52   --    AND my_number_column = :FND_BIND3
53   --
54   l_dsql_text := fnd_dsql.get_text(FALSE);
55   dbms_sql.parse(l_cursor_id, l_dsql_text, dbms_sql.native);
56 
57   fnd_dsql.do_binds;
58 
59   l_num_of_rows := dbms_sql.execute(l_cursor_id);
60 
61   l_dsql_debug := fnd_dsql.get_text(TRUE);
62 
63   --
64   -- l_dsql_debug will be:
65   --
66   -- SELECT my_column1, my_column2
67   --   FROM my_table
68   --  WHERE my_varchar2_column = :FND_BIND1
69   --    AND my_date_column = :FND_BIND2
70   --    AND my_number_column = :FND_BIND3
71   -- C:FND_BIND1= fnd_dsql_test
72   -- D:FND_BIND2= 1980/01/02 00:00:00
73   -- N:FND_BIND3= 12345
74   --
75   ...
76   ---------------------------------------------------------------------- */
77 
78 
79  /* ======================================================================
80   * ERROR HANDLING
81   * ----------------------------------------------------------------------
82   * In case of any error, this package will raise exception and will set
83   * message through fnd_message.
84   * To get this message use FND_MESSAGE utilities.
85   * ====================================================================== */
86 
87 
88  /* ======================================================================
89   * PROCEDURE init:
90   * ----------------------------------------------------------------------
91   * Initializes the internal structures.
92   * ====================================================================== */
93   PROCEDURE init;
94 
95  /* ======================================================================
96   * PROCEDURE add_text:
97   * ----------------------------------------------------------------------
98   * Appends p_text to the end of current dynamic sql statement.
99   * ====================================================================== */
100   PROCEDURE add_text(p_text IN VARCHAR2);
101 
102  /* ======================================================================
103   * PROCEDURE add_bind:
104   * ----------------------------------------------------------------------
105   * Appends :FND_BINDn to the end of current dynamic sql statement.
106   * n is the index of bind variable.
107   * p_value will be stored, and it will be binded in do_binds call.
108   * Maximum size for p_value in varchar2 case : 2000.
109   * Maximum number of binds : 100.
110   * ====================================================================== */
111   PROCEDURE add_bind(p_value       IN VARCHAR2);
112 
113   PROCEDURE add_bind(p_value       IN DATE);
114 
115   PROCEDURE add_bind(p_value       IN NUMBER);
116 
117  /* ======================================================================
118   * PROCEDURE set_cursor:
119   * ----------------------------------------------------------------------
120   * Sets the cursor id for current dynamic sql statment.
121   * ====================================================================== */
122   PROCEDURE set_cursor(p_cursor_id IN INTEGER);
123 
124  /* ======================================================================
125   * PROCEDURE do_binds:
126   * ----------------------------------------------------------------------
127   * Binds the bind variables.
128   * ====================================================================== */
129   PROCEDURE do_binds;
130 
131  /* ======================================================================
132   * FUNCTION get_text:
133   * ----------------------------------------------------------------------
134   * Returns current dynamic sql statement.
135   * ====================================================================== */
136   FUNCTION get_text(p_with_debug IN BOOLEAN DEFAULT FALSE) RETURN VARCHAR2;
137 
138  /* ======================================================================
139   * PROCEDURE fnd_dsql_test:
140   * ----------------------------------------------------------------------
141   * Used to test this package.
142   * ====================================================================== */
143   PROCEDURE fnd_dsql_test;
144 
145 END fnd_dsql;