1 package body hr_gen_pkg as
2 /* $Header: hrgen.pkb 115.11 2004/05/12 04:05:41 njaladi ship $
3 ------------------------------------------------------------------------------
4 +==============================================================================+
5 | Copyright (c) 1994 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +==============================================================================+
9 Name
10 HR Libraries server-side agent
14 a form and its libraries both refer to the same server-side package.
11 Purpose
12 Agent handles all server-side traffic to and from forms libraries. This
13 is particularly necessary because we wish to avoid the situation where
15 Forms/libraries appears to be unable to cope with this situation in
16 circumstances which we cannot yet define.
17 History
18 21 Apr 95 N Simpson Created
19 17 May 95 N Simpson Added comment_text, insert_comment
20 and get_customized_restriction to
21 remove all sql from client side, and
22 thus allow the library to be made
23 global.
24 05-JUN-1995 N Simpson Modified legislation/business group
25 restriction on
26 get_customized_restriction.
27 14 Jun 95 N Simpson Added get_dates, change_ses_date
28 23 Jun 95 D Kerr Added bg_name and bg_currency_code
29 to init_forms and temporary overload.
30 11 Oct 95 J Thuringer Removed spurious end of comment marker
31 20 Dec 96 D Kerr init_forms: Removed temp. overload.
32 Modified in line with changes to
33 underlying procedure.
34 Added tracing procedures.
35 19 Aug 97 Sxshah Banner now on eack line.
36 24 Dec 97 D Kerr Added delete_ses_rows
37 17-FEB-99 D Kerr 11.5: added get_dates overload
38
39 05 Dec 99 Dave Kerr This file now diverges from 11.0 version
40 Added p_hr_trace_dest to init_forms
41 06 Jul 00 G Perry Changed comment text to 32000 for
42 WWBUG 1382371.
43 17 Jul 01 M Enderby Addition of ADE related pipe code
44 05 Feb 02 G Sayers Added dbdrv and commit to comply with
45 GSCC Standards.
46 05-Dec-2002 A.Holt NOCOPY Performance Changes for 11.5.9
47 01-Jul-2003 tvankayl procedure Get_customized_restriction modified
48 to take values from PAY_CUSTOM_RESTRICTIONS_VL instead of
49 PAY_CUSTOMIZED_RESTRICTIONS.
50 115.10 21-Nov-2003 vramanai Modified message name in Procedure
51 Get_Customized_Restriction to
52 HR_7070_CUST_INVALID_CUST_NAME .
53 115.11 12-May-2003 njaladi 3577964- Added new autonomous transactin wrapper
54 procedure putSessionAttributeValue which calls
55 the icx_sec.putSessionAttributeValue.
56
57 */
58 g_dummy number; -- Used in various places throughout the package
59 PROCEDURE init_forms(p_business_group_id IN NUMBER,
60 p_short_name OUT NOCOPY VARCHAR2,
61 p_bg_name OUT NOCOPY VARCHAR2,
62 p_bg_currency_code OUT NOCOPY VARCHAR2,
63 p_legislation_code OUT NOCOPY VARCHAR2,
64 p_session_date IN OUT NOCOPY DATE,
65 p_ses_yesterday OUT NOCOPY DATE,
66 p_start_of_time OUT NOCOPY DATE,
67 p_end_of_time OUT NOCOPY DATE,
68 p_sys_date OUT NOCOPY DATE,
69 p_enable_hr_trace IN BOOLEAN,
70 p_hr_trace_dest IN VARCHAR2 DEFAULT 'DBMS_PIPE'
71
72 /* This code not yet implemented
73 ,p_form_name varchar2 default null
74 ,p_actual_version varchar2 default null*/
75
76 ) IS
77 begin
78
79 hr_general.init_forms(p_business_group_id ,
80 p_short_name ,
81 p_bg_name ,
82 p_bg_currency_code ,
83 p_legislation_code ,
84 p_session_date ,
85 p_ses_yesterday ,
86 p_start_of_time ,
87 p_end_of_time ,
88 p_sys_date ,
89 p_enable_hr_trace,
90 p_hr_trace_dest
91 /* This code not yet implemented
92 ,p_form_name
93 ,p_actual_version */
94 );
95 end init_forms;
96 --------------------------------------------------------------------------------
97 procedure get_customized_restriction (
98 --
99 -- Gets information about a customization of a form
100 --
101 --
102 p_restriction_name in varchar2,
103 p_form_name in varchar2,
104 p_business_group_id in number,
105 p_legislation_code in varchar2,
106 p_application_id out nocopy number,
107 p_query_title out nocopy varchar2,
108 p_standard_title out nocopy varchar2,
109 p_customized_restriction_id out nocopy number,
110 p_message_name out nocopy varchar2) is
111 --
112 cursor csr_restriction is
113 select application_id,
114 query_form_title,
115 standard_form_title,
116 enabled_flag,
117 customized_restriction_id
118 from pay_custom_restrictions_vl
119 where name = p_restriction_name
120 and form_name = p_form_name
121 and (business_group_id = p_business_group_id
122 or business_group_id is null)
123 and (legislation_code = p_legislation_code
124 or legislation_code is null);
125 --
126 cursor csr_form_name is
127 select 1
128 from pay_customized_restrictions
129 where name = p_restriction_name;
130 --
131 l_enabled_flag varchar2 (30);
132 --
133 begin
134 --
135 hr_utility.set_location ('hr_gen_pkg.GET_CUSTOMIZED_RESTRICTION',1);
136 --
137 -- Get the details of the requested customization
138 --
139 open csr_restriction;
140 fetch csr_restriction into
141 --
142 p_application_id,
143 p_query_title,
144 p_standard_title,
145 l_enabled_flag,
146 p_customized_restriction_id;
147 --
148 hr_utility.set_location ('hr_gen_pkg.GET_CUSTOMIZED_RESTRICTION',2);
149 --
150 if csr_restriction%found then
151 --
152 hr_utility.trace ('Customized restriction found');
153 --
154 close csr_restriction;
155 --
156 -- If the customization is disabled, pass this info to the client
157 --
158 if l_enabled_flag <> 'Y' then
159 --
160 hr_utility.trace ('Customization is disabled');
161 p_message_name := 'HR_7074_CUST_NOT_ENABLED';
162 --
163 end if;
164 --
165 else
166 --
167 hr_utility.trace ('Customization was not found for the current form');
168 --
169 -- If the customization was not found, then establish why.
170 --
171 open csr_form_name; -- Is the customization for a different form?
172 fetch csr_form_name into g_dummy;
173 --
174 if csr_form_name%found then -- Tell the client that the customization applies to a different form
175 --
176 hr_utility.trace ('Customization was found for a different form');
177 p_message_name := 'HR_7070_CUST_INVALID_CUST_NAME';
178 --
179 else -- Tell the client that the requested customization does not exist
180 --
181 hr_utility.trace ('Customization was not found for any form');
182 p_message_name := 'HR_7072_CUST_NO_EXIST_NAME';
183 --
184 end if;
185 --
186 close csr_form_name;
187 --
188 end if;
189 --
190 hr_utility.set_location ('hr_gen_pkg.GET_CUSTOMIZED_RESTRICTION',3);
191 --
192 end get_customized_restriction;
193 --------------------------------------------------------------------------------
194 procedure insert_comment (
195 --
196 -- Inserts a comment into the HR comments table
197 --
198 --
199 p_source_table_name varchar2,
200 p_comment_text varchar2,
201 p_comment_id in out nocopy number) is
202 --
203 cursor csr_next_comment_id is
204 select hr_comments_s.nextval
205 from sys.dual;
206 --
207 begin
208 --
209 open csr_next_comment_id;
210 fetch csr_next_comment_id into p_comment_id;
211 close csr_next_comment_id;
212 --
213 insert into hr_comments (
214 --
215 comment_id,
216 source_table_name,
217 comment_text)
218 values (
219 p_comment_id,
220 p_source_table_name,
221 p_comment_text);
222 --
223 end insert_comment;
224 --------------------------------------------------------------------------------
225 function comment_text (p_comment_id number) return varchar2 is
226 --
227 -- Gets a comment from the HR comments table
228 --
229 cursor csr_comment is
230 select comment_text
231 from hr_comments
232 where comment_id = p_comment_id;
233 --
234 /* Expanded to 32000 for WWBUG 1382371 */
235 l_comment_text varchar2 (32000);
236 --
237 begin
238 --
239 open csr_comment;
240 fetch csr_comment into l_comment_text;
241 close csr_comment;
242 --
243 return l_comment_text;
244 --
245 end comment_text;
246 --------------------------------------------------------------------------------
247 procedure get_dates(
248 p_ses_date out nocopy date,
249 p_ses_yesterday_date out nocopy date,
250 p_start_of_time out nocopy date,
251 p_end_of_time out nocopy date,
252 p_sys_date out nocopy date ) is
253 l_commit number ;
254 begin
255
256 dt_fndate.get_dates(
257 p_ses_date => p_ses_date,
258 p_ses_yesterday_date => p_ses_yesterday_date,
259 p_start_of_time => p_start_of_time,
260 p_end_of_time => p_end_of_time,
261 p_sys_date => p_sys_date,
262 p_commit => l_commit);
263
264 if l_commit = 1
265 then
266 commit;
267 end if;
268
269 end get_dates;
270
271 procedure get_dates(
272 p_ses_date out nocopy date,
273 p_ses_yesterday_date out nocopy date,
274 p_start_of_time out nocopy date,
275 p_end_of_time out nocopy date,
276 p_sys_date out nocopy date,
277 p_commit out nocopy number) is
278
279 --
280 begin
281 --
282 dt_fndate.get_dates(
283 p_ses_date => p_ses_date,
284 p_ses_yesterday_date => p_ses_yesterday_date,
285 p_start_of_time => p_start_of_time,
286 p_end_of_time => p_end_of_time,
287 p_sys_date => p_sys_date,
288 p_commit => p_commit);
289 --
290 end get_dates;
291 --------------------------------------------------------------------------------
292 procedure change_ses_date (
293 p_ses_date date,
294 p_commit out nocopy number) is
295 --
296 begin
297 --
298 dt_fndate.change_ses_date (p_ses_date, p_commit);
299 --
300 end change_ses_date;
301 --------------------------------------------------------------------------------
302 procedure delete_ses_rows is
303 v_commit number;
304 begin
305 dt_fndate.delete_ses_rows(p_commit => v_commit);
306 if v_commit = 1 then
307 commit;
308 end if;
309 end delete_ses_rows ;
310 --------------------------------------------------------------------------------
311 procedure trace_on(trace_mode in varchar2, session_identifier in varchar2) is
312 begin
313
314 hr_utility.trace_on(trace_mode,session_identifier) ;
315
316 end trace_on ;
317 --------------------------------------------------------------------------------
318 procedure trace_off is
319 begin
320
321 hr_utility.trace_off ;
322
323 end trace_off ;
324 --------------------------------------------------------------------------------
325 procedure trace(trace_data in varchar2) is
326 begin
327
328 hr_utility.trace(trace_data) ;
329
330 end trace ;
331 --------------------------------------------------------------------------------
332 procedure set_location(procedure_name in varchar2, stage in number) is
333 begin
334
335 hr_utility.set_location(procedure_name,stage);
336
337 end set_location;
338 --------------------------------------------------------------------------------
339 procedure set_trace_options(p_options in varchar2) is
340 begin
341
342 hr_utility.set_trace_options(p_options);
343
344 end set_trace_options;
345 --------------------------------------------------------------------------------
346 -- ADE Procedures
347 --------------------------------------------------------------------------------
348 procedure pipe_ade_detail (p_detail IN varchar2) IS
349 BEGIN
350 dbms_pipe.pack_message(p_detail);
351 END pipe_ade_detail;
352 --------------------------------------------------------------------------------
353 procedure reset_pipe_buffer IS
354 BEGIN
355 dbms_pipe.reset_buffer;
356 END reset_pipe_buffer;
357 --------------------------------------------------------------------------------
358 procedure purge_pipe(p_pipename IN varchar2) IS
359 BEGIN
360 dbms_pipe.purge(p_pipename);
361 END purge_pipe;
362 --------------------------------------------------------------------------------
363 procedure send_pipe_message(p_pipename IN varchar2) IS
364 l_result integer;
365 BEGIN
366 l_result := dbms_pipe.send_message(p_pipename, 60, 16384);
367 END send_pipe_message;
368 --------------------------------------------------------------------------------
369 procedure putSessionAttributeValue ( p_name in varchar2,
370 p_value in varchar2,
371 p_session_id in number) is
372 pragma AUTONOMOUS_TRANSACTION;
373 BEGIN
374 icx_sec.putSessionAttributeValue(
375 p_name => p_name,
376 p_value => p_value,
377 p_session_id => p_session_id);
378 commit;
379 END putSessionAttributeValue;
380
381 --
382 end hr_gen_pkg;