1 package fa_rx_util_pkg as
2 /* $Header: FARXUTLS.pls 120.3.12010000.2 2008/07/31 06:53:22 sbhaskar ship $ */
3
4 ------------------------------------
5 -- Global types
6 ------------------------------------
7 --
8 -- Used to identify the select list as well as the columns to insert
9
10 type Rep_Columns_Rec is record (
11 -- Primary Key to this table of records
12 --
13 primary_key varchar2(40),
14 -- The name of the column that is being selected
15 -- Fully qualify the column name to include the table alias
16 -- Bug 1379946: increased size of select_column_name
17 select_column_name varchar2(4000),
18 -- The name of the column where it will be inserted
19 -- If this column is not to be inserted, then make this NULL
20 insert_column_name varchar2(30),
21 -- This is the variable that will hold the value temporarily
22 -- Make sure it is of the same type and fully qualify the variable
23 -- name and include the package name
24 placeholder_name varchar2(60),
25 -- Other values
26 column_type varchar2(30),
27 column_length number
28 );
29 type Rep_Columns_Array is table of Rep_Columns_Rec index by binary_integer;
30
31 --
32 -- This record assigns the values of the events for each section
33 -- as well as if this section is even enabled.
34 --
35 type Report_Record is record (
36 section_name varchar2(20),
37 enabled boolean,
38 before_report varchar2(300),
39 bind varchar2(300),
40 after_fetch varchar2(300),
41 after_report varchar2(300)
42 );
43 type Report_Array is table of Report_Record index by binary_integer;
44
45
46 ------------------------------------
47 -- Global (public) variables
48 ------------------------------------
49
50 --
51 -- Report sections
52 -- These variables are set using the procedure assign_report().
53 -- You should only manipulate these variables if you need to do something a bit more complex
54 -- then what the assign_report() procedure provides.
55 -- e.g., in your plug-in code, you may want to have a before report trigger which fires
56 -- before the main before report trigger. assign_report() always sets the
57 -- triggers to run after the core report.
58 Num_Sections number;
59 Report Report_Array;
60 -- During your report triggers, you may want to know which section you are running currently.
61 -- This value is made available to use within your triggers.
62 -- NOTE: Changing this value will not change which section is begin run. This is provided as
63 -- informational only. It's value will be reset to the current section whenever a new section begins.
64 Current_Section varchar2(20);
65
66 --
67 -- Column mappings
68 -- These variables are set during init_request() and assign_column() procedures. They are provided
69 -- as informational only and you should not change these values - EVER!
70 Num_Columns number;
71 Rep_Columns Rep_Columns_Array;
72 Interface_Table varchar2(30);
73
74 --
75 -- SELECT statement parts
76 -- The different parts of the SELECT statement (From/Where/Group by/Having/Order by) are available for
77 -- developers to manipulate. These should be manipulated from within the before_report event.
78 -- If you are the main report developer (not the plug-in), then you should simply assign your PL/SQL
79 -- statements here. Plug-in developers should make sure to concatenate their listing.
80 --
81 -- NOTE: Plug-ins --> Make sure to include and ',' or 'AND' (for where clauses) when appending your part.
82 --
83 -- These parts will be used to create a SELECT statement.
84 -- assign_column() function creates the select list portion. These parts will be used as follows:
85 -- 'SELECT '||<select list from assign_column()>||
86 -- ' FROM '||<From_Clause>||
87 -- DECODE(Where_Clause, NULL, NULL, ' WHERE '||Where_Clause)||
88 -- DECODE(Group_By_Clause, NULL, NULL, ' GROUP BY '||Group_By_Clause)||
89 -- DECODE(Having_Clause, NULL, NULL, ' HAVING '||Having_Clause)||
90 -- DECODE(Order_By_Clause, NULL, NULL, ' ORDER BY '||Order_By_Clause)
91 --
92 --
93 -- Assigned values to this record by the function assign_columns
94 -- This record is to store the specifics of a single column
95 -- in the SELECT and/or the INSERT statement.
96 --
97 -- NOTE: Unlike some of the other global public variables,
98 -- these should be assigned values directly. There are no functions
99 -- which will set these up for you.
100 Hint_Clause varchar2(500);
101 From_Clause varchar2(10000);
102 Where_Clause varchar2(10000);
103 Group_By_Clause varchar2(10000);
104 Having_Clause varchar2(10000);
105 Order_By_Clause varchar2(10000);
106
107
108 ------------------------------------
109 -- Functions/Procedures
110 ------------------------------------
111
112 -------------------------------------------------------------------------
113 --
114 -- PROCEDURE init_request
115 --
116 -- Parameters
117 -- p_calling_proc The name of the procedure calling this function
118 -- p_request_id Request ID of this concurrent request.
119 -- p_interface_table Optional parameter to pass the name
120 -- of the interface table to insert into.
121 --
122 -- Description
123 -- This function initializes some of the parameters needed by RX.
124 -- These include:
125 -- User_ID
126 -- Login_ID
127 -- Today's Date <-- All three used in <WHO Columns>
128 -- Interface Table name <-- retrieved using p_request_id
129 -- The first time this procedure is called, this function caches the
130 -- value of p_calling_proc. The procedure run_report() will run the report
131 -- only when called with the same value.
132 -- This allows for init_request(), assign_report(), and run_report() to
133 -- be called multiple times (which is needed when creating a plug-in.
134 --
135 -- NOTES
136 -- If this function is called with a request id of 0, it will assume
137 -- that you are trying to debug this code from SQL*Plus and skip
138 -- this routine. It assumes that your testing script has already
139 -- called init_debug (below) to initialize these routines.
140 --
141 -- Modification History
142 -- KMIZUTA 12-MAR-99 Created.
143 --
144 -------------------------------------------------------------------------
145 procedure init_request(p_calling_proc in varchar2, p_request_id in number,
146 p_interface_table in varchar2 default null);
147
148
149 -------------------------------------------------------------------------
150 --
151 -- PROCEDURE init_debug
152 --
153 -- Parameters
154 -- p_interface_table Interface table for the RX Report
155 --
156 -- Description
157 -- Does pretty much the same as init_request() except it initializes
158 -- the Interface Table name from the parameter. When debugging
159 -- using SQL*Plus, there will be no request_id from which you could
160 -- find out the interface table name.
161 -- You should prepare a test script whenever testing RX reports which
162 -- 1) Calls fnd_global.initialize
163 -- This initializes Oracle Applications.
164 -- 2) Calls fa_rx_util_pkg.enable_debug(debug_dir, debug_file)
165 -- Enable debugging for these routines (if you want)
166 -- 3) Calls fa_rx_util_pkg.init_debug
167 -- Initialize these routines
168 -- 4) Calls your RX report
169 --
170 -- Modification History
171 -- KMIZUTA 12-MAR-99 Created.
172 --
173 -------------------------------------------------------------------------
174 procedure init_debug(p_interface_table in varchar2);
175
176
177 -------------------------------------------------------------------------
178 --
179 -- PROCEDURE assign_column
180 --
181 -- Parameters
182 -- p_key This is sort of like the primary key. Plug-ins
183 -- will be able to override what you specify
184 -- here using this key.
185 -- p_select_column_name
186 -- This is the name of the column that you will
187 -- be selecting from. Make sure to fully qualify
188 -- the column name (i.e., make sure the table
189 -- alias is included as in cc.code_combination_id).
190 -- p_insert_column_name
191 -- This is the name of the column in the interface
192 -- table where this value will be stored.
193 -- p_placeholder_name
194 -- This is the name of the package variable into
195 -- which this value is temporarily stored.
196 -- Make sure to fully qualify your variable name
197 -- with the package name (i.e., package_foo.struct_bar.var_name)
198 -- p_column_type Data type. Must be either VARCHAR2, NUMBER, DATE
199 -- p_column_length Needed only if the column type is VARCHAR2.
200 -- The length of the PLACEHOLDER variable.
201 --
202 -- Description
203 -- This procedure assigns this column to the report. These values
204 -- will be used to construct the SELECT statement as well as the
205 -- INSERT statement.
206 --
207 -- NOTES:
208 -- You may want to SELECT from a column which will be used in your
209 -- after fetch trigger. If you do not want to insert this value into
210 -- the interface table, simply keep p_insert_column_name NULL.
211 -- On the other hand, you may have a value which you calculate in
212 -- the after fetch trigger, but there is no source column from
213 -- the select statement. In this case, simply leave p_select_column_name
214 -- NULL.
215 -- In either case, you must specify p_placeholder_name
216 --
217 -- Modification History
218 -- KMIZUTA 12-MAR-99 Created.
219 --
220 -------------------------------------------------------------------------
221 procedure assign_column(p_key in varchar2,
222 p_select_column_name in varchar2,
223 p_insert_column_name in varchar2,
224 p_placeholder_name in varchar2,
225 p_column_type in varchar2,
226 p_column_length in number default NULL);
227
228 -------------------------------------------------------------------------
229 --
230 -- PROCEDURE assign_report
231 --
232 -- Parameters
233 -- p_section_name Your report may have multiple sections
234 -- where each section has a different
235 -- SELECT statement. You can have this model
236 -- run through each of your SELECT statements
237 -- in separate sections. Specify your section
238 -- name here.
239 -- p_before_report
240 -- p_bind
241 -- p_after_fetch
242 -- p_after_report These are the event triggers. Specify the
243 -- name of your procedure that you would
244 -- like to have called in each event. Make
245 -- sure to fully specify the procedure name
246 -- (i.e., add package name). Also, make sure
247 -- you add the ';' at the end.
248 --
249 --
250 -- Description
251 -- This procedure assigns the different event blocks for a given
252 -- section.
253 --
254 -- NOTES
255 -- The before report, after fetch, and after report event blocks
256 -- should not use any host variables.
257 -- The bind event must pass the host variable :CURSOR_SELECT.
258 -- This variable should be of type number or integer and you
259 -- should use this value as the cursor you pass to
260 -- dbms_sql.bind_variable().
261 --
262 -- Logic Flow
263 -- Blocks marked with a (*) are the events that are being set
264 -- by this procedure.
265 --
266 -- ------------------
267 -- | Before Report* |<----|
268 -- ------------------ |
269 -- | |
270 -- v |
271 -- ------------------ |
272 -- | Build Select | |
273 -- ------------------ |
274 -- | |
275 -- v |
276 -- ------------------ |
277 -- | Bind Select* | |
278 -- ------------------ |
279 -- | |
280 -- v |
281 -- ------------------ |
282 -- | Fetch Row |<-| |
283 -- ------------------ | |
284 -- | | |
285 -- v | |
286 -- ------------------ | |
287 -- | After Fetch* | | |
288 -- ------------------ | |
289 -- | | |
290 -- v | |
291 -- ------------------ | |
292 -- | Insert Row | | |
293 -- ------------------ | |
294 -- | | |
295 -- v | |
296 -- ------------------ | |
297 -- | Get Next Row |--| |
298 -- ------------------ |
299 -- | |
300 -- no more |
301 -- | |
302 -- v |
303 -- ------------------ |
304 -- | Next Section |-----|
305 -- ------------------
306 -- |
307 -- no more
308 -- |
309 -- v
310 -- ------------------
311 -- | After Report* |
312 -- ------------------
313 -- |
314 -- v
315 --
316 -- Before Report
317 -- Before report should call assign_column and set values to
318 -- From_Clause, Where_Clause, Group_By_Clause, Having_Clause,
319 -- Order_By_Clause. This is basically building up the SELECT
320 -- statement and the insert statement.
321 -- You may also do any preprocessing that may be required such
322 -- as leaving an audit of your run.
323 --
324 -- Bind
325 -- The bind event is called once after the select statement is
326 -- built and it has been parsed. You will be passed the value
327 -- of the cursor for the SELECT statement in a host variable
328 -- by the name of :CURSOR_SELECT. You will need to call
329 -- dbms_sql.bind_variabe to bind any variables that you may
330 -- have included in your Where_Clause, Group_By_Clause,
331 -- Having_Clause, or Order_By_Clause.
332 --
333 -- After Fetch
334 -- This event is called after each row is fetched. You can
335 -- assume that the placeholder variables that you specified
336 -- in your calls to assign_column are holding the value
337 -- for the current row. Do any processing that needs to
338 -- be done before the insert.
339 --
340 -- After Report
341 -- This event is called after the report section finishes.
342 -- You should do any cleanup here.
343 -- e.g., you may have opened a cursor which is used within
344 -- your after fetch event. You will want to close this
345 -- cursor within this routine.
346 --
347 -- Modification History
348 -- KMIZUTA 12-MAR-99 Created.
349 --
350 -------------------------------------------------------------------------
351 procedure assign_report(p_section_name in varchar2,
352 p_enabled in boolean,
353 p_before_report in varchar2,
354 p_bind in varchar2,
355 p_after_fetch in varchar2,
356 p_after_report in varchar2);
357
358 -------------------------------------------------------------------------
359 --
360 -- PROCEDURE run_report
364 -- run_report() function.
361 --
362 -- Parameters
363 -- p_calling_proc This is the procedure that is calling the
365 -- retcode Concurrent program return code
366 -- errbuf Error/Warning message buffer
367 --
368 -- Description
369 -- This is the procedure which actually runs your report. The logic
370 -- flow that is described above (in assign_report), is actually
371 -- performed by this procedure. Make sure to call this procedure
372 -- only after you have called init_request (or init_debug) and
373 -- assign_report.
374 --
375 -- The sucessful/warning/error status will be returned in retcode and errbuf
376 --
377 -- NOTES
378 -- The first time init_request() procedure is called, this function caches the
379 -- value of p_calling_proc. The procedure run_report() will run the report
380 -- only when called with the same value.
381 -- This allows for init_request(), assign_report(), and run_report() to
382 -- be called multiple times (which is needed when creating a plug-in.
383 --
384 -- Modification History
385 -- KMIZUTA 12-MAR-99 Created.
386 --
387 -------------------------------------------------------------------------
388 procedure run_report(p_calling_proc in varchar2, retcode out nocopy number, errbuf out varchar2);
389
390
391 -------------------------------------------------------------------------
392 --
393 -- PROCEDURE log
394 -- PROCEDURE out
395 --
396 -- Parameters
397 -- msg String to be logged/outputted
398 --
399 -- Description
400 -- These files are wrappers to other routines. It basically handles
401 -- your output to log and output files. This was added here so that
402 -- if you ever need to quickly make it so that all output goes
403 -- through dbms_output, you can just modify these functions.
404 --
405 -- Modification History
406 -- KMIZUTA 12-MAR-99 Created.
407 --
408 -------------------------------------------------------------------------
409 procedure log(msg in varchar2);
410 procedure out(msg in varchar2);
411
412
413 ------------------------------------
414 -- Debuggin Routines
415 ------------------------------------
416 -------------------------------------------------------------------------
417 -- PROCEDURE enable_debug
418 --
419 -- Parameters
420 -- None
421 -- OR
422 -- debug_dir Directory where the debug file will reside
423 -- debug_file Filename of debug file.
424 --
425 -- Description
426 -- If you are calling this routine from a PL/SQL concurrent program
427 -- then you should call the version with no parameters. This will
428 -- automatically route all of your debug statements to the log file.
429 -- If you are calling this routine from a PL/SQL package called from
430 -- within SQL*Plus, you must specify the directory and file name
431 -- of the debug file. NOTE: the database must have write permission.
432 -- (Such as /sqlcom/log).
433 --
434 -- Modification History
435 -- KMIZUTA 12-MAR-99 Created.
436 --
437 -------------------------------------------------------------------------
438 procedure enable_debug;
439 procedure enable_debug(debug_dir in varchar2, debug_file in varchar2);
440 procedure enable_debug(bufsize in number);
441
442 -------------------------------------------------------------------------
443 -- PROCEDURE disable_debug
444 --
445 -- Parameters
446 -- None
447 --
448 -- Description
449 -- Stop debugging
450 --
451 -- Modification History
452 -- KMIZUTA 12-MAR-99 Created.
453 --
454 -------------------------------------------------------------------------
455 procedure disable_debug;
456
457 -------------------------------------------------------------------------
458 -- PROCEDURE debug_enabled
459 --
460 -- Parameters
461 -- None
462 --
463 -- Returns
464 -- Boolean Returns true if debugging is enabled.
465 --
466 -- Description
467 -- Checks to see if debugging is enabled. Call this function
468 -- if you want to have a block of code run only if debugging
469 -- is enabled.
470 -- If you have a whole bunch of debug() calls in succession,
471 -- then it is preferable if you first check to see if debuggin
472 -- is enabled.
473 --
474 -- Modification History
475 -- KMIZUTA 12-MAR-99 Created.
476 --
477 -------------------------------------------------------------------------
478 function debug_enabled return boolean;
479
480 -------------------------------------------------------------------------
481 -- PROCEDURE debug
482 --
483 -- Parameters
484 -- msg String to be logged to the debug file
485 --
486 -- Description
487 -- Send debug message to the debug (log) file.
488 --
489 -- Modification History
490 -- KMIZUTA 12-MAR-99 Created.
491 --
492 -------------------------------------------------------------------------
493 procedure debug(msg in varchar2);
494
495
496 -------------------------------------------------------------------------
497 -- PROCEDURE enable_trace, disable_trace
498 --
499 -- Parameters
500 -- None
501 --
502 -- Description
503 -- Enable and disable SQL Tracing
504 --
505 -- Modification History
506 -- KMIZUTA 12-MAR-99 Created.
507 --
508 -------------------------------------------------------------------------
509 procedure enable_trace;
510 procedure disable_trace;
511
512 end fa_rx_util_pkg;
[Home] [Help]