1 PACKAGE BODY pay_monitor_balance_retrieval AS
2 /* $Header: pymnblrt.pkb 120.0 2005/05/29 06:47:48 appldev noship $ */
3 --
4 -------------------------------------------------------------------------
5 -- PROCEDURE monitor_balance_retrieval
6 -------------------------------------------------------------------------
7 -- Description:
8 -- The aim of this procedure is to output key data to a table, which can then
9 -- be interrogated, by users to determine which balances are being derived
10 -- from the route.
11 --
12 -- If the monitoring profile, HR_MONITOR_BALANCE_RETRIEVAL, has been set
13 -- the code searches for a value in the session variable 'MONITOR'. If the
14 -- value is null, then the default 'Monitoring all calls to route' is
15 -- set as the module name, otherwise the module name will be set to the value
16 -- in the session variable.
17 -- The code then calls output_bal_retrieval_data to insert monitoring data
18 -- into the table pay_monitor_balance_retrievals.
19 -------------------------------------------------------------------------
20 procedure monitor_balance_retrieval(p_defined_balance_id number
21 ,p_assignment_action_id number
22 ,p_reason varchar2) is
23 --
24 l_profile varchar2(2);
25 l_proc varchar2(80);
26 l_module_name pay_monitor_balance_retrievals.module_name%type;
27 --
28 BEGIN
29 l_proc := 'pay_monitor_balance_retrieval.monitor_balance_retrieval';
30 --
31 hr_utility.set_location('Entering: '||l_proc, 10);
32 --
33 -- this will be a call to get_profile once the profile is set up
34 --
35 l_profile := fnd_profile.value('HR_MONITOR_BALANCE_RETRIEVAL');
36 IF l_profile = 'Y' THEN
37 --
38 hr_utility.set_location(l_proc, 20);
39 --
40 -- if the session variable MONITOR is null, return the default - 'Monitoring
41 -- all calls to route'
42 --
43 l_module_name := nvl(get_session_var('MONITOR')
44 , 'Monitoring all calls to route');
45 BEGIN
46 --
47 hr_utility.set_location(l_proc, 30);
48 --
49 -- call at to insert row into table
50 --
51 output_bal_retrieval_data(l_module_name
52 ,sysdate
53 ,p_defined_balance_id
54 ,p_assignment_action_id
55 ,p_reason
56 );
57 END;
58 --
59 ELSE -- profile HR_MONITOR_BALANCE_RETRIEVAL is not Y
60 --
61 hr_utility.set_location(l_proc, 40);
62 END IF;
63 --
64 hr_utility.set_location('Leaving: '||l_proc, 50);
65 --
66 END monitor_balance_retrieval;
67 -------------------------------------------------------------------------
68 -- PROCEDURE output_bal_retrieval_data
69 -------------------------------------------------------------------------
70 -- Description:
71 -- output_bal_retrieval_data is an autonomous transaction, enabling data
72 -- to be written to table pay_monitor_balance_retrievals, and to be commited
73 -- independently of the package from which it is called.
74 --------------------------------------------------------------------------
75 PROCEDURE output_bal_retrieval_data(p_module_name varchar2
76 ,p_date_monitored date
77 ,p_defined_balance_id number
78 ,p_assignment_action_id number
79 ,p_reason varchar2)
80 IS
81 PRAGMA AUTONOMOUS_TRANSACTION;
82 --
83 l_proc varchar2(80);
84 --
85 BEGIN
86 --
87 l_proc := 'pay_monitor_balance_retrieval.output_bal_retrieval_data';
88 --
89 hr_utility.set_location('Entering: '||l_proc, 10);
90 --
91 -- insert values into table pay_monitor_balance_retrievals
92 --
93 insert into pay_monitor_balance_retrievals
94 (module_name
95 ,date_monitored
96 ,defined_balance_id
97 ,assignment_action_id
98 ,reason
99 )
100 values
101 (p_module_name
102 ,p_date_monitored
103 ,p_defined_balance_id
104 ,p_assignment_action_id
105 ,p_reason
106 );
107 --
108 commit;
109 --
110 hr_utility.set_location('Leaving: '||l_proc, 20);
111 --
112 END output_bal_retrieval_data;
113 -------------------------------------------------------------------------
114 -- SESSION VARIABLE FUNCTIONS
115 -------------------------------------------------------------------------
116 --
117 -- The following functions use the PLSQL index by tables:
118 -- SessionVarNames, table of VARCHAR2(64); and SessionVarValues,
119 -- also index by table of VARCHAR2(64).
120 -- index by VARCHAR2;
121 --------------------------------------------------------------------------------
122 -- The Functions get and set session_var force the name parameter to upper case
123 -- before storing in, or searching the table. The value is not converted.
124 --------------------------------------------------------------------------------
125 -- FUNCTION get_session_var
126 --
127 -- get_session_var performs a (dumb) linear search of the names table for
128 -- p_name. If found, it returns the value from the values table at the
129 -- corresponding index. This dumb method should be performant for small
130 -- amounts of variables (< 100 ?). Larger amounts would require review
131 -- of alternative methods. Of course, if PLSQL ever supports indexing
132 -- of PLSQL tables by VARCHAR, or other types besides BINARY INTEGER, this
133 -- would not be an issue.
134 --
135 -- NOTE the exception when a value is not found at the same index as
136 -- where a name is found. Such an event would signal serious problems
137 -- with the state of the tables. We should consider throwing an
138 -- HR or other exception if this ever happes.
139 --
140 -- NOTE that since we do not allow NULL values for session vars, the
141 -- get_session_var function can be used to test whether a var is set:
142 -- if it returns NULL, the var is not set.
143 --
144 FUNCTION get_session_var
145 (p_name VARCHAR2)
146 return VARCHAR2 is
147 l_temp_name VARCHAR2(64);
148 l_temp_value VARCHAR2(64);
149 l_loop_count NUMBER;
150 BEGIN
151
152 -- no NULL names
153 if p_name is NULL then
154 return NULL;
155 end if;
156
157 -- check whether var tables are empty
158 if SessionVarCount = 0 then
159 return NULL;
160 end if;
161
162 -- force upper case name, remove leading/trailin spaces
163 l_temp_name := rtrim(ltrim(upper(p_name)));
164
165 -- search name table
166 -- NOTE the level of PLSQL certified for 10.7 Apps does not support
167 -- use of NEXT .. LAST attributes on PLSQL index by tables for WNDS
168 -- pure code. So we use hard coded table row count to drive our search.
169 FOR l_loop_count IN 1 .. SessionVarCount LOOP
170 if SessionVarNames(l_loop_count) = l_temp_name then
171 -- debug trace - found our var
172 -- use exception in case values is out of sync with names
173 -- NOTE: the level of PLSQL certified with 10.7 Apps does
174 -- not support using the exists attribute, so we must use
175 -- an exception here.
176 begin
177 l_temp_value := SessionVarValues(l_loop_count);
178 exception
179 when no_data_found then
180 -- index not found, big problem here - should trhow exception
181 --
182 return NULL;
183 end;
184 return l_temp_value;
185 end if;
186 END LOOP;
187
188 -- debug trace
189 return NULL;
190 END get_session_var;
191 --------------------------------------------------------------------------------
192 -- FUNCTION get_session_var - overloaded with p_default. Calls base
193 -- get_session_var; but if var is not set, returns p_default.
194 --------------------------------------------------------------------------------
195 FUNCTION get_session_var
196 (p_name VARCHAR2,
197 p_default VARCHAR)
198 return VARCHAR2 is
199 l_temp_value VARCHAR2(64);
200 BEGIN
201 l_temp_value := NULL;
202
203 -- use base function to get value
204 l_temp_value := get_session_var(p_name);
205
206 -- if NULL value, return p_default
207 if l_temp_value is NULL then
208 return p_default;
209 end if;
210 END get_session_var;
211 --------------------------------------------------------------------------------
212 -- PROCEDURE set_session_var
213 --
214 -- Sets name and value in session var tables. Converts name to upper case,
215 -- does *not* convert value.
216 --
217 -- The set function does not store NULL names or values. However,
218 -- an attempt to set a NULL value will result in the variable of
219 -- that name being deleted from both the names and values tables.
220 --
221 --------------------------------------------------------------------------------
222 PROCEDURE set_session_var
223 (p_name IN VARCHAR2,
224 p_value IN VARCHAR2)
225 IS
226 l_temp_name VARCHAR2(64);
227 l_temp_value VARCHAR2(64);
228 l_loop_count NUMBER;
229 BEGIN
230 -- no NULL names
231 if p_name is NULL then
232 return;
233 end if;
234
235 -- force upper case name, remove leading/trailin spaces
236 l_temp_name := rtrim(ltrim(upper(p_name)));
237 --
238 if SessionVarCount > 0 then
239 -- search name table, we may already have this var
240 -- NOTE the level of PLSQL certified for 10.7 Apps does not support
241 -- use of NEXT .. LAST attributes on PLSQL index by tables for WNDS
242 -- pure code. So we use hard coded table row count to drive our search.
243 FOR l_loop_count IN 1 .. SessionVarCount LOOP
244 if SessionVarNames(l_loop_count) = l_temp_name then
245 -- debug trace - found our var
246 -- set value table row to new value
247 SessionVarValues(l_loop_count) := p_value;
248 -- and return
249 return;
250 end if;
251 END LOOP;
252 end if;
253
254 -- not found in names table, make new entries
255 -- debug trace - log new var count
256 SessionVarCount := SessionVarCount + 1;
257 SessionVarNames(SessionVarCount) := l_temp_name;
258 SessionVarValues(SessionVarCount) := p_value;
259
260 END set_session_var;
261 --------------------------------------------------------------------------------
262 -- PROCEDURE clear_session_vars
263 --------------------------------------------------------------------------------
264 PROCEDURE clear_session_vars
265 IS
266 BEGIN
267 -- delete tavbles by assigning empty tables. The level of PLSQL
268 -- supported with 10.7 does not support the delete attribute on
269 -- whole tables.
270 SessionVarNames := EmptyTable;
271 SessionVarValues := EmptyTable;
272 SessionVarCount := 0;
273 END clear_session_vars;
274 --------------------------------------------------------------------------------
275 END pay_monitor_balance_retrieval;
[Home] [Help]