[Home] [Help]
PACKAGE BODY: APPS.BEN_EXT_SORT
Source
1 package body BEN_EXT_SORT as
2 /* $Header: benxsort.pkb 120.0 2005/05/28 09:47:15 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_ext_sort.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------------< main >---------------------------------|
12 -- ----------------------------------------------------------------------------
13 PROCEDURE main
14 ( p_ext_rcd_in_file_id in number,
15 p_sort1_data_elmt_in_rcd_id in number,
16 p_sort2_data_elmt_in_rcd_id in number,
17 p_sort3_data_elmt_in_rcd_id in number,
18 p_sort4_data_elmt_in_rcd_id in number,
19 p_rcd_seq_num in number,
20 p_low_lvl_cd in varchar2,
21 p_prmy_sort_val out nocopy varchar2,
22 p_scnd_sort_val out nocopy varchar2,
23 p_thrd_sort_val out nocopy varchar2) is
24 --
25 l_proc varchar2(72) := g_package||'main';
26 --
27 l_plane number;
28 l_prmy_seq_num number;
29 l_scnd_seq_num number;
30 l_thrd_seq_num number;
31 l_frth_seq_num number;
32 l_prmy_val ben_ext_rslt_dtl.val_01%type := null;
33 l_scnd_val ben_ext_rslt_dtl.val_01%type := null;
34 l_thrd_val ben_ext_rslt_dtl.val_01%type := null;
35 l_frth_val ben_ext_rslt_dtl.val_01%type := null;
36 l_trans_count varchar2(50);
37 l_person_id varchar2(55);
38 l_ext_chg_evt_log_id varchar2(55);
39 l_per_cm_prvdd_id varchar2(15);
40 l_dflt_id number;
41 l_dflt_val ben_ext_rslt_dtl.val_01%type := null;
42 l_prmy_val_max number := 25;
43 l_scnd_val_max number := 25;
44 l_thrd_val_max number := 25;
45 l_frth_val_max number := 25;
46 l_person_id_max number := 15;
47 l_dflt_id_max number := 15;
48 g_ext_chg_evt_log_id_max number := 15;
49 g_per_cm_prvdd_id_max number := 10;
50 g_elig_pl_ord_no_max number := 8;
51 g_elig_opt_ord_no_max number := 7;
52 l_trans_count_max number := 10;
53 l_start_position number;
54 --
55
56 cursor c_get_seq_num (p_ext_data_elmt_in_rcd_id in number) is
57 select xer.seq_num
58 from ben_ext_data_elmt_in_rcd xer
59 where xer.ext_data_elmt_in_rcd_id = p_ext_data_elmt_in_rcd_id;
60
61 --
62 BEGIN
63 --
64 hr_utility.set_location('Entering'||l_proc, 5);
65 --
66
67 if p_low_lvl_cd = 'P' then -- pers
68 l_plane := 1;
69 elsif p_low_lvl_cd in ('E','G','Y','R','F','CO','WG','WR') then --enrt, elig, ele, run, cwb grp, cwb rate
70 l_plane := 2;
71 elsif p_low_lvl_cd in ('D','B','A','ED','PR') then -- dpnt, bnf, act itm, el dpnt,prem.
72 l_plane := 3;
73 else -- no sorting should occur
74 hr_utility.set_location('return withoit '||p_low_lvl_cd, 5);
75 return;
76 end if;
77 --
78 g_trans_count := g_trans_count + 1;
79 --
80 -- consider caching for efficiency.
81 --
82 if p_sort1_data_elmt_in_rcd_id is not null then
83 open c_get_seq_num(p_sort1_data_elmt_in_rcd_id);
84 fetch c_get_seq_num into l_prmy_seq_num;
85 close c_get_seq_num;
86 l_prmy_val := nvl(substr(ben_ext_fmt.g_val_tab(l_prmy_seq_num),1,l_prmy_val_max),' ');
87 end if;
88
89 if p_sort2_data_elmt_in_rcd_id is not null then
90 open c_get_seq_num(p_sort2_data_elmt_in_rcd_id);
91 fetch c_get_seq_num into l_scnd_seq_num;
92 close c_get_seq_num;
93 l_scnd_val := nvl(substr(ben_ext_fmt.g_val_tab(l_scnd_seq_num),1,l_scnd_val_max),' ');
94 end if;
95
96 if p_sort3_data_elmt_in_rcd_id is not null then
97 open c_get_seq_num(p_sort3_data_elmt_in_rcd_id);
98 fetch c_get_seq_num into l_thrd_seq_num;
99 close c_get_seq_num;
100 l_thrd_val := nvl(substr(ben_ext_fmt.g_val_tab(l_thrd_seq_num),1,l_thrd_val_max),' ');
101 end if;
102
103 if p_sort4_data_elmt_in_rcd_id is not null then
104 open c_get_seq_num(p_sort4_data_elmt_in_rcd_id);
105 fetch c_get_seq_num into l_frth_seq_num;
106 close c_get_seq_num;
107 l_frth_val := nvl(substr(ben_ext_fmt.g_val_tab(l_frth_seq_num),1,l_frth_val_max),' ');
108 end if;
109
110 --
111 l_trans_count := lpad(to_char(g_trans_count),l_trans_count_max,'0');
112 --
113 if l_plane = 1 then
114 --
115 l_start_position := 1;
116
117 if l_prmy_val is null then
118 l_prmy_val := nvl(substr(g_prmy_sort_val,l_start_position,l_prmy_val_max),' '); -- inherit from previous record
119 end if;
120
121 l_start_position := l_start_position + l_prmy_val_max;
122
123 if l_scnd_val is null then
124 l_scnd_val := nvl(substr(g_prmy_sort_val,l_start_position,l_scnd_val_max),' ');
125 end if;
126
127 l_start_position := l_start_position + l_scnd_val_max;
128
129 if l_thrd_val is null then
130 l_thrd_val := nvl(substr(g_prmy_sort_val,l_start_position,l_thrd_val_max),' ');
131 end if;
132
133 l_start_position := l_start_position + l_thrd_val_max;
134
135 if l_frth_val is null then
136 l_frth_val := nvl(substr(g_prmy_sort_val,l_start_position,l_frth_val_max),' ');
137 end if;
138 --
139 l_prmy_val := rpad(l_prmy_val,l_prmy_val_max);
140 l_scnd_val := rpad(l_scnd_val,l_scnd_val_max);
141 l_thrd_val := rpad(l_thrd_val,l_thrd_val_max);
142 l_frth_val := rpad(l_frth_val,l_frth_val_max);
143 --
144 l_person_id := lpad(to_char(ben_ext_person.g_person_id),l_person_id_max,'0');
145 l_ext_chg_evt_log_id := lpad(to_char(nvl(ben_ext_person.g_ext_chg_evt_log_id,0)),g_ext_chg_evt_log_id_max,'0');
146 l_per_cm_prvdd_id := lpad(to_char(nvl(ben_ext_person.g_per_cm_prvdd_id,0)),g_per_cm_prvdd_id_max,'0');
147 --
148 g_prmy_sort_val := l_prmy_val ||
149 l_scnd_val ||
150 l_thrd_val ||
151 l_frth_val ||
152 l_person_id || -- 1st default
153 l_ext_chg_evt_log_id || -- 2nd default
154 l_per_cm_prvdd_id || -- 3rd default
155 l_trans_count; -- handles same plane ordering.
156 --
157 g_scnd_sort_val := '0';
158 g_thrd_sort_val := '0';
159 --
160 elsif l_plane = 2 then
161 --
162 l_start_position := 2;
163
164 if l_prmy_val is null then
165 l_prmy_val := nvl(substr(g_scnd_sort_val,l_start_position,l_prmy_val_max),' ');
166 end if;
167
168 l_start_position := l_start_position + l_prmy_val_max;
169
170 if l_scnd_val is null then
171 l_scnd_val := nvl(substr(g_scnd_sort_val,l_start_position,l_scnd_val_max),' ');
172 end if;
173
174 l_start_position := l_start_position + l_scnd_val_max;
175
176 if l_thrd_val is null then
177 l_thrd_val := nvl(substr(g_scnd_sort_val,l_start_position,l_thrd_val_max),' ');
178 end if;
179
180 l_start_position := l_start_position + l_thrd_val_max;
181
182 if l_frth_val is null then
183 l_frth_val := nvl(substr(g_scnd_sort_val,l_start_position,l_frth_val_max),' ');
184 end if;
185 --
186 l_prmy_val := rpad(l_prmy_val,l_prmy_val_max);
187 l_scnd_val := rpad(l_scnd_val,l_scnd_val_max);
188 l_thrd_val := rpad(l_thrd_val,l_thrd_val_max);
189 l_frth_val := rpad(l_frth_val,l_frth_val_max);
190 --
191 if p_low_lvl_cd = 'E' then
192 l_dflt_id := ben_ext_person.g_enrt_benefit_order_num;
193 l_dflt_val := lpad(to_char(nvl(l_dflt_id,0)),l_dflt_id_max,'0');
194 elsif p_low_lvl_cd = 'G' then
195 l_dflt_val := lpad(to_char(nvl(ben_ext_person.g_elig_pl_ord_no,0)),g_elig_pl_ord_no_max,'0')||
196 lpad(to_char(nvl(ben_ext_person.g_elig_opt_ord_no,0)),g_elig_opt_ord_no_max,'0');
197 elsif p_low_lvl_cd = 'Y' then
198 l_dflt_id := ben_ext_person.g_element_input_value_sequence;
199 l_dflt_val := lpad(to_char(nvl(l_dflt_id,0)),l_dflt_id_max,'0');
200 elsif p_low_lvl_cd = 'R' then
201 l_dflt_id := ben_ext_person.g_runrslt_input_value_sequence;
202 l_dflt_val := lpad(to_char(nvl(l_dflt_id,0)),l_dflt_id_max,'0');
203 elsif p_low_lvl_cd = 'CO' then
204 l_dflt_id := ben_ext_person.g_contact_seq_num;
205 l_dflt_val := lpad(to_char(nvl(l_dflt_id,0)),l_dflt_id_max,'0');
206 elsif p_low_lvl_cd = 'F' then
207 l_dflt_val := ben_ext_person.g_flex_bnft_pool_name;
208 elsif p_low_lvl_cd = 'WG' then
209 l_dflt_val := ben_ext_person.g_CWB_Budget_Group_Plan_Name ;
210 elsif p_low_lvl_cd = 'WR' then
211 l_dflt_val := ben_ext_person.g_CWB_Awrd_Group_Plan_Name ;
212
213 end if;
214 --
215 g_scnd_sort_val := '1' ||
216 l_prmy_val ||
217 l_scnd_val ||
218 l_thrd_val ||
219 l_frth_val ||
220 l_dflt_val ||
221 l_trans_count;
222 --
223 g_thrd_sort_val := '0';
224 --
225 else -- l_plane = 3
226 --
227 l_start_position := 2;
228
229 if l_prmy_val is null then
230 l_prmy_val := nvl(substr(g_thrd_sort_val,l_start_position,l_prmy_val_max),' ');
231 end if;
232
233 l_start_position := l_start_position + l_prmy_val_max;
234
235 if l_scnd_val is null then
236 l_scnd_val := nvl(substr(g_thrd_sort_val,l_start_position,l_scnd_val_max),' ');
237 end if;
238
239 l_start_position := l_start_position + l_scnd_val_max;
240
241 if l_thrd_val is null then
242 l_thrd_val := nvl(substr(g_thrd_sort_val,l_start_position,l_thrd_val_max),' ');
243 end if;
244
245 l_start_position := l_start_position + l_thrd_val_max;
246
247 if l_frth_val is null then
248 l_frth_val := nvl(substr(g_thrd_sort_val,l_start_position,l_frth_val_max),' ');
249 end if;
250 --
251 l_prmy_val := rpad(l_prmy_val,l_prmy_val_max);
252 l_scnd_val := rpad(l_scnd_val,l_scnd_val_max);
253 l_thrd_val := rpad(l_thrd_val,l_thrd_val_max);
254 l_frth_val := rpad(l_frth_val,l_frth_val_max);
255 --
256 if p_low_lvl_cd = 'D' then
257 l_dflt_id := ben_ext_person.g_dpnt_contact_seq_num;
258 elsif p_low_lvl_cd = 'B' then
259 l_dflt_id := ben_ext_person.g_bnf_contact_seq_num;
260 elsif p_low_lvl_cd = 'ED' then
261 l_dflt_id := ben_ext_person.g_elig_dpnt_contact_seq_num;
262 elsif p_low_lvl_cd = 'A' then
263 l_dflt_id := ben_ext_person.g_actn_type_id;
264 elsif p_low_lvl_cd = 'PR' then
265 l_dflt_val := ben_ext_person.g_prem_type;
266 end if;
267 --
268 if p_low_lvl_cd <> 'PR' then
269 l_dflt_val := lpad(to_char(nvl(l_dflt_id,0)),l_dflt_id_max,'0');
270 end if;
271 --
272 g_thrd_sort_val := '1' ||
273 l_prmy_val ||
274 l_scnd_val ||
275 l_thrd_val ||
276 l_frth_val ||
277 l_dflt_val ||
278 l_trans_count;
279 --
280 end if; -- l_plane
281 --
282 p_prmy_sort_val := g_prmy_sort_val;
283 p_scnd_sort_val := g_scnd_sort_val;
284 p_thrd_sort_val := g_thrd_sort_val;
285 --
286 hr_utility.set_location('Exiting'||l_proc, 15);
287 --
288 END; -- main
289 --
290 END; -- package