1 package ben_person_object AUTHID CURRENT_USER as
2 /* $Header: benperde.pkh 120.2 2012/01/04 06:51:09 amnaraya ship $ */
3 --
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 Name
12 Person Object Caching Routine
13 Purpose
14 This package is used to return person object information.
15 History
16 Version Date Who What?
17 --------- --------- ---------- --------------------------------------------
18 115.0 11-Jun-99 gperry Created(arcsed in by bbulusu)
19 115.1 18-Jun-99 gperry Added in person_date_info structure to store
20 the minimum effective date of the person
21 and the assignment.
22 Added in fte info structure to store
23 the total fte for a persons primary
24 assignment.
25 Added cache structure to store active
26 life event.
27 Added cache structure to store persons
28 benefits balance.
29 115.2 23-Jun-99 gperry Added in assignment extra info cache.
30 115.3 24-Jun-99 gperry Added in contacts cache.
31 115.4 24-Jun-99 gperry Removed css cache.
32 115.5 05-Aug-99 gperry Added last cached record logic.
33 115.6 09-Aug-99 gperry Added new cache routines.
34 assignment_status_types
35 soft_coding_keyflex
36 person_type_info
37 115.7 16-Aug-99 gperry Added nocopy compiler directive.
38 115.8 17-Aug-99 gperry Removed business group id from
39 set_ast_object.
40 115.9 18-Aug-99 gperry Added cache for ben_bnfts_bal_f
41 115.10 23-Aug-99 gperry Cache full time info using assignment id.
42 115.11 26-Aug-99 gperry Added cache for benefits assignment.
43 Added cache for applicants assignment.
44 Made assignment cache get employee
45 assignment.
46 115.12 26-Aug-99 gperry Made applicants assignment cache multirow.
47 115.22 02 May 00 RChase Performance NOCOPY changes
48 115.14 10 Jul 00 gperry Added firstass cache for WWBUG 1350997.
49 115.15 20 Jul 00 gperry Removed firstass cache and added allass
50 cache for WWBUG 1350997.
51 115.16 05 Oct 00 gperry Added ord_id to person type cache.
52 115.17 05 Jan 01 kmahendr Added parameter per_in_ler_id to get_object
53 115.18 20-Mar-02 vsethi added dbdrv lines
54 115.19 29-Apr-02 pabodla Bug 1631182 : Added person_type_id to
55 g_person_typ_info_rec
56 115.20 13 Feb 03 mhoyes - Fixed PGA memory growth bug 2800680.
57 115.21 24 jun 11 usaraswa Bug 12664112 Added Procedure get_object to take
58 p_assignmnet_id as a input argument
59 120.0.12000000.3 01-Jan-12 amnaraya Enh 13478736: Learner Access Eligibility Enhancement
60 Broke dual Maintenance from 11i - 12.0B.
61 Created a new procedures set_ass_object and get_assgn_object
62 with assignment_id as an extra parameter for fetching the
63 assignment records based on the passed in assignment_id.
64 -----------------------------------------------------------------------------
65 */
66 --
67 type g_person_date_info_rec is record
68 (person_id number,
69 min_per_effective_start_date date,
70 min_ass_effective_start_date date);
71 --
72 type g_person_fte_info_rec is record
73 (assignment_id number,
74 total_fte number,
75 fte number);
76 --
77 --Bug 1631182 - Added person_type_id
78 --
79 type g_person_typ_info_rec is record
80 (person_id number,
81 person_type_id number,
82 user_person_type varchar2(80),
83 system_person_type varchar2(30),
84 ord_id number);
85 --
86 -- Global type declarations.
87 --
88 type g_cache_per_table is table of per_all_people_f%rowtype index
89 by binary_integer;
90 --
91 type g_cache_ass_table is table of per_all_assignments_f%rowtype index
92 by binary_integer;
93 --
94 type g_cache_ast_table is table of per_assignment_status_types%rowtype index
95 by binary_integer;
96 --
97 type g_cache_aei_table is table of per_assignment_extra_info%rowtype index
98 by binary_integer;
99 --
100 type g_cache_pps_table is table of per_periods_of_service%rowtype index
101 by binary_integer;
102 --
103 type g_cache_pad_table is table of per_addresses%rowtype index
104 by binary_integer;
105 --
106 type g_cache_pil_table is table of ben_per_in_ler%rowtype index
107 by binary_integer;
108 --
109 type g_cache_bal_table is table of ben_per_bnfts_bal_f%rowtype index
110 by binary_integer;
111 --
112 type g_cache_bnb_table is table of ben_bnfts_bal_f%rowtype index
113 by binary_integer;
114 --
115 type g_cache_hsc_table is table of hr_soft_coding_keyflex%rowtype index
116 by binary_integer;
117 --
118 type g_cache_bal_per_table is table of ben_cache.g_cache_lookup index
119 by binary_integer;
120 --
121 type g_cache_typ_per_table is table of ben_cache.g_cache_lookup index
122 by binary_integer;
123 --
124 type g_cache_con_table is table of per_contact_relationships%rowtype index
125 by binary_integer;
126 --
127 type g_cache_con_per_table is table of ben_cache.g_cache_lookup index
128 by binary_integer;
129 --
130 type g_cache_app_ass_table is table of ben_cache.g_cache_lookup index
131 by binary_integer;
132 --
133 type g_cache_all_ass_table is table of ben_cache.g_cache_lookup index
134 by binary_integer;
135 --
136 type g_cache_date_table is table of g_person_date_info_rec index
137 by binary_integer;
138 --
139 type g_cache_fte_table is table of g_person_fte_info_rec index
140 by binary_integer;
141 --
142 type g_cache_typ_table is table of g_person_typ_info_rec index
143 by binary_integer;
144 --
145 g_cache_per_rec g_cache_per_table;
146 g_cache_ass_rec g_cache_ass_table;
147 g_cache_benass_rec g_cache_ass_table;
148 g_cache_ast_rec g_cache_ast_table;
149 g_cache_aei_rec g_cache_aei_table;
150 g_cache_pps_rec g_cache_pps_table;
151 g_cache_pad_rec g_cache_pad_table;
152 g_cache_hsc_rec g_cache_hsc_table;
153 g_cache_pil_rec g_cache_pil_table;
154 g_cache_bal_rec g_cache_bal_table;
155 g_cache_bnb_rec g_cache_bnb_table;
156 g_cache_bal_per_rec g_cache_bal_per_table;
157 g_cache_typ_rec g_cache_typ_table;
158 g_cache_typ_per_rec g_cache_typ_per_table;
159 g_cache_con_rec g_cache_con_table;
160 g_cache_con_per_rec g_cache_con_per_table;
161 g_cache_appass_rec g_cache_ass_table;
162 g_cache_app_ass_rec g_cache_app_ass_table;
163 g_cache_allass_rec g_cache_ass_table;
164 g_cache_all_ass_rec g_cache_all_ass_table;
165 g_cache_date_rec g_cache_date_table;
166 g_cache_fte_rec g_cache_fte_table;
167 --
168 -- Latest record caches
169 --
170 g_cache_last_per_rec per_all_people_f%rowtype;
171 g_cache_last_benass_rec per_all_assignments_f%rowtype;
172 g_cache_last_ass_rec per_all_assignments_f%rowtype;
173 g_cache_last_ast_rec per_assignment_status_types%rowtype;
174 g_cache_last_aei_rec per_assignment_extra_info%rowtype;
175 g_cache_last_pps_rec per_periods_of_service%rowtype;
176 g_cache_last_pad_rec per_addresses%rowtype;
177 g_cache_last_hsc_rec hr_soft_coding_keyflex%rowtype;
178 g_cache_last_pil_rec ben_per_in_ler%rowtype;
179 g_cache_last_date_rec g_person_date_info_rec;
180 g_cache_last_fte_rec g_person_fte_info_rec;
181 g_cache_last_con_rec g_cache_con_table;
182 g_cache_last_appass_rec g_cache_ass_table;
183 g_cache_last_allass_rec g_cache_ass_table;
184 g_cache_last_typ_rec g_cache_typ_table;
185 g_cache_last_bal_rec ben_per_bnfts_bal_f%rowtype;
186 g_cache_last_bnb_rec ben_bnfts_bal_f%rowtype;
187 --
188 -- Set object routines
189 --
190 procedure set_object
191 (p_rec in out nocopy per_all_assignments_f%rowtype);
192 procedure set_benass_object
193 (p_rec in out nocopy per_all_assignments_f%rowtype);
194 procedure set_appass_object
195 (p_rec in out nocopy per_all_assignments_f%rowtype);
196 procedure set_allass_object
197 (p_rec in out nocopy per_all_assignments_f%rowtype);
198 procedure set_object
199 (p_rec in out nocopy per_assignment_status_types%rowtype);
200 procedure set_object
201 (p_rec in out nocopy per_assignment_extra_info%rowtype);
202 procedure set_object
203 (p_rec in out nocopy per_all_people_f%rowtype);
204 procedure set_object
205 (p_rec in out nocopy per_periods_of_service%rowtype);
206 procedure set_object
207 (p_rec in out nocopy per_addresses%rowtype);
208 procedure set_object
209 (p_rec in out nocopy hr_soft_coding_keyflex%rowtype);
210 procedure set_object
211 (p_rec in out nocopy ben_per_in_ler%rowtype);
212 procedure set_object
213 (p_rec in out nocopy ben_bnfts_bal_f%rowtype);
214 procedure set_object
215 (p_rec in out nocopy ben_per_bnfts_bal_f%rowtype);
216 procedure set_object
217 (p_rec in out nocopy per_contact_relationships%rowtype);
218 procedure set_object
219 (p_rec in out nocopy g_person_date_info_rec);
220 procedure set_object
221 (p_rec in out nocopy g_person_fte_info_rec);
222 procedure set_object
223 (p_rec in out nocopy g_person_typ_info_rec);
224 procedure set_bal_per_object
225 (p_rec in out nocopy ben_cache.g_cache_lookup);
226 procedure set_con_per_object
227 (p_rec in out nocopy ben_cache.g_cache_lookup);
228 procedure set_app_ass_object
229 (p_rec in out nocopy ben_cache.g_cache_lookup);
230 procedure set_all_ass_object
231 (p_rec in out nocopy ben_cache.g_cache_lookup);
232 procedure set_typ_per_object
233 (p_rec in out nocopy ben_cache.g_cache_lookup);
234 --
235 procedure set_ass_object
236 (p_person_id in number,
237 p_business_group_id in number,
238 p_effective_date in date,
239 p_rec in out nocopy per_all_assignments_f%rowtype);
240 procedure set_benass_object
241 (p_person_id in number,
242 p_business_group_id in number,
243 p_effective_date in date,
244 p_rec in out nocopy per_all_assignments_f%rowtype);
245 procedure set_appass_object
246 (p_person_id in number,
247 p_business_group_id in number,
248 p_effective_date in date,
249 p_rec in out nocopy g_cache_ass_table);
250 procedure set_allass_object
251 (p_person_id in number,
252 p_business_group_id in number,
253 p_effective_date in date,
254 p_rec in out nocopy g_cache_ass_table);
255 procedure set_ast_object
256 (p_assignment_status_type_id in number,
257 p_rec in out nocopy per_assignment_status_types%rowtype);
258 procedure set_bnb_object
259 (p_bnfts_bal_id in number,
260 p_business_group_id in number,
261 p_effective_date in date,
262 p_rec in out nocopy ben_bnfts_bal_f%rowtype);
263 procedure set_ass_object
264 (p_assignment_id in number,
265 p_rec in out nocopy per_assignment_extra_info%rowtype);
266 procedure set_hsc_object
267 (p_soft_coding_keyflex_id in number,
268 p_rec in out nocopy hr_soft_coding_keyflex%rowtype);
269 procedure set_per_object
270 (p_person_id in number,
271 p_business_group_id in number,
272 p_effective_date in date,
273 p_rec in out nocopy per_all_people_f%rowtype);
274 procedure set_pps_object
275 (p_person_id in number,
276 p_business_group_id in number,
277 p_effective_date in date,
278 p_rec in out nocopy per_periods_of_service%rowtype);
279 procedure set_pad_object
280 (p_person_id in number,
281 p_business_group_id in number,
282 p_effective_date in date,
283 p_rec in out nocopy per_addresses%rowtype);
284 procedure set_pil_object
285 (p_person_id in number,
286 p_business_group_id in number,
287 p_effective_date in date,
288 p_per_in_ler_id in number default null,
289 p_rec in out nocopy ben_per_in_ler%rowtype);
290 procedure set_bal_object
291 (p_person_id in number,
292 p_business_group_id in number,
293 p_effective_date in date);
294 procedure set_con_object
295 (p_person_id in number,
296 p_business_group_id in number,
297 p_effective_date in date,
298 p_rec in out nocopy g_cache_con_table);
299 procedure set_typ_object
300 (p_person_id in number,
301 p_business_group_id in number,
302 p_effective_date in date,
303 p_rec in out nocopy g_cache_typ_table);
304 procedure set_per_dates_object
305 (p_person_id in number,
306 p_business_group_id in number,
307 p_effective_date in date,
308 p_rec in out nocopy g_person_date_info_rec);
309 procedure set_per_fte_object
310 (p_assignment_id in number,
311 p_business_group_id in number,
312 p_effective_date in date,
313 p_rec in out nocopy g_person_fte_info_rec);
314 --
315 -- Get object routines
316 --
317 procedure get_object
318 (p_person_id in number,
319 p_rec in out nocopy per_all_people_f%rowtype);
320 --Bug 12664112
321 -- Added Procedure get_object to take p_assignmnet_id as a input
322 -- argument
323 --ud changes start
324 -- adding new procedure taking latest assignment_id as input
325 procedure get_object
326 (p_person_id in number,
327 p_rec in out nocopy per_all_assignments_f%rowtype,
328 p_assignment_id in number);
329 --us changes end
330 --Bug 12664112
331
332 procedure get_object
333 (p_person_id in number,
334 p_rec in out nocopy per_all_assignments_f%rowtype);
335 procedure get_benass_object
336 (p_person_id in number,
337 p_rec in out nocopy per_all_assignments_f%rowtype);
338 procedure get_object
339 (p_bnfts_bal_id in number,
340 p_rec in out nocopy ben_bnfts_bal_f%rowtype);
341 procedure get_object
342 (p_assignment_status_type_id in number,
343 p_rec in out nocopy per_assignment_status_types%rowtype);
344 procedure get_object
345 (p_assignment_id in number,
346 p_rec in out nocopy per_assignment_extra_info%rowtype);
347 procedure get_object
348 (p_person_id in number,
349 p_rec in out nocopy per_periods_of_service%rowtype);
350 procedure get_object
351 (p_person_id in number,
352 p_rec in out nocopy per_addresses%rowtype);
353 procedure get_object
354 (p_person_id in number,
355 p_rec in out nocopy g_cache_con_table);
356 procedure get_object
357 (p_person_id in number,
358 p_rec in out nocopy g_cache_ass_table);
359 procedure get_allass_object
360 (p_person_id in number,
361 p_rec in out nocopy g_cache_ass_table);
362 procedure get_object
363 (p_person_id in number,
364 p_rec in out nocopy g_cache_typ_table);
365 procedure get_object
366 (p_person_id in number,
367 -- added per_in_ler_id for unrestricted enhancement
368 p_per_in_ler_id in number default null,
369 p_rec in out nocopy ben_per_in_ler%rowtype);
370 procedure get_object
371 (p_soft_coding_keyflex_id in number,
372 p_rec in out nocopy hr_soft_coding_keyflex%rowtype);
373 procedure get_object
374 (p_person_id in number,
375 p_bnfts_bal_id in number,
376 p_effective_date in date,
377 p_rec in out nocopy ben_per_bnfts_bal_f%rowtype);
378 procedure get_object
379 (p_person_id in number,
380 p_rec in out nocopy g_person_date_info_rec);
381 procedure get_object
382 (p_assignment_id in number,
383 p_rec in out nocopy g_person_fte_info_rec);
384 procedure get_bal_per_object
385 (p_person_id in number,
386 p_bnfts_bal_id in number,
387 p_effective_date in date,
388 p_rec in out nocopy ben_per_bnfts_bal_f%rowtype);
389 --
390 procedure clear_down_cache;
391 --
392 procedure defrag_caches;
393 /*Start Enh: 13478736 - Learner Access Eligibility Enhancement*/
394 procedure set_ass_object
395 (p_person_id in number,
396 p_assignment_id in number,
397 p_business_group_id in number,
398 p_effective_date in date,
399 p_rec in out nocopy per_all_assignments_f%rowtype);
400 procedure get_assgn_object
401 (p_person_id in number,
402 p_assignment_id in number,
403 p_rec in out nocopy per_all_assignments_f%rowtype);
404 /*End Enh: 13478736*/
405 --
406 end ben_person_object;
[Home] [Help]