1 package body PAY_SOE_UTIL as
2 /* $Header: pysoeutl.pkb 120.1.12010000.2 2009/01/05 11:40:05 parusia ship $ */
3 --
4 g_debug boolean := hr_utility.debug_enabled;
5 --
6 cursor config is
7 select org_information2 elements1
8 , org_information3 elements2
9 , org_information4 elements3
10 , org_information5 elements4
11 , org_information6 elements5
12 , org_information7 elements6
13 , org_information8 information1
14 , org_information9 balances1
15 , org_information10 balances2
16 , org_information11 balances3
17 from hr_organization_information
18 where organization_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
19 and org_information_context = 'Business Group:SOE Information'
20 and org_information1 = fnd_profile.value('PAY_SOE_USER_CATEGORY');
21 --
22 l_config config%ROWTYPE;
23 --
24 TYPE dataValueRecType is RECORD (colName varchar2(30)
25 ,colValue VARCHAR2(240)
26 ,firstCol BOOLEAN
27 ,lastCol BOOLEAN);
28 dataValueRec dataValueRecType;
29 --
30 TYPE dataValueTabType is TABLE of dataValueRecType INDEX by BINARY_INTEGER;
31 dataValueTab dataValueTabType;
32 --
33 i number := 0;
34 --
35 --
36 initialized boolean := FALSE;
37 -- Bug 7377886
38 -- global variable bgid_save will store the bg_id for which SOE Information
39 -- has been retrieved
40 bgid_save number ;
41 --
42 /* --------------------------------------------------------------------
43 Procedure : setValue
44 --------------------
45 Maintain pl/sql table of column name and value pairs. firstcol and
46 lastcol indicate the select statements first and last columns (used
47 to format the statement correctly
48 ----------------------------------------------------------------------- */
49 procedure setValue(name varchar2
50 ,value varchar2
51 ,firstCol BOOLEAN
52 ,lastCol BOOLEAN) is
53 begin
54 if g_debug then
55 hr_utility.trace('colid = ' || name || value);
56 end if;
57 --
58 dataValueRec.colName := ' COL' || name;
59 dataValueRec.colValue := value;
60 dataValueRec.firstCol := firstCol;
61 dataValueRec.lastCol := lastCol;
62 --
63 i := i + 1;
64 dataValueTab(i) := dataValueRec;
65 end;
66 --
67 /* --------------------------------------------------------------------
68 Procedure : clear
69 --------------------
70 Clears down existing dataValueTab PL/SQL table
71 ----------------------------------------------------------------------- */
72 procedure clear is
73 begin
74 dataValueTab.delete;
75 i := 0;
76 end;
77 --
78 /* --------------------------------------------------------------------
79 Function : genCursor
80 --------------------
81 Process contents of pl/SQL table to return SQL statement in the form
82
83 select 'a' COL1, 'b' COL2 from dual
84 union
85 select 'c' COL1, 'd' COL2 from dual;
86 ----------------------------------------------------------------------- */
87 function genCursor return long is
88 l_sql_string long;
89 l_ref_cursor ref_cursor;
90 begin
91 for a in dataValueTab.first..dataValueTab.last loop
92 if dataValueTab(a).firstCol then
93 l_sql_string := l_sql_string ||'select '; else
94 l_sql_string := l_sql_string ||',';
95 end if;
96 l_sql_string :=
97 l_sql_string || ''''
98 || dataValueTab(a).colValue
99 || ''''
100 || dataValueTab(a).colName;
101 --
102 if dataValueTab(a).lastCol then
103 l_sql_string :=
104 l_sql_string || ' from dual ';
105 --
106 if a <> dataValueTab.last then
107 l_sql_string := l_sql_string ||' union all ';
108 end if;
109 end if;
110 end loop;
111 --
112 -- Create the reference cursor
113 --
114 -- open l_ref_cursor for l_sql_string;
115 --
116 return l_sql_string;
117 end;
118 --
119 /* --------------------------------------------------------------------
120 Function : convertCursor
121 --------------------
122 The following function is called by the SOE moduled VO code for each of the
123 SOE Regions to convert the SQL string in to a REF CURSOR that
124 can be processed by the VO.
125 ----------------------------------------------------------------------- */
126 function convertCursor(p_sql_string long) return ref_cursor is
127 l_ref_cursor ref_cursor;
128 begin
129 if g_debug then
130 hr_utility.trace(substr(p_sql_string,1,2000));
131 hr_utility.trace(substr(p_sql_string,2001,2000));
132 hr_utility.trace(substr(p_sql_string,4001,2000));
133 end if;
134 --
135 open l_ref_cursor for p_sql_string;
136 return l_ref_cursor;
137 end;
138 --
139 /* --------------------------------------------------------------------
140 Function : getIDFlexValue
141 --------------------
142 The following function evaluates a key flexfield segment to determine
143 whether it has a Table Validated Value Set. If so then it returns
144 the MEANING value from the value set, otherwise the ID value.
145 This is used by the Bank Flexfield function, but is made public so
146 that it could be used by other legislatively defined functions.
147 ----------------------------------------------------------------------- */
148 function getIDFlexValue(p_id_flex_code in varchar2
149 ,p_id_flex_num in number
150 ,p_application_column_name varchar2
151 ,p_id in varchar2) return varchar2 is
152 --
153 TYPE FlexCurTyp IS REF CURSOR; -- define weak REF CURSOR type
154 flex_cv FlexCurTyp; -- declare cursor variable
155 --
156 l_code varchar2(30);
157 l_val varchar2(80);
158 --
159 l_sql varchar2(2000);
160 l_flex_value_set_id number;
161 --
162 cursor getTableValidation is
163 select f.flex_value_set_id
164 from fnd_id_flex_segments f
165 , fnd_flex_validation_tables t
166 where f.id_flex_code = p_id_flex_code
167 and f.id_flex_num = p_id_flex_num
168 and f.application_column_name = p_application_column_name
169 and f.flex_value_set_id = t.flex_value_set_id;
170 --
171 begin
172 open getTableValidation;
173 fetch getTableValidation into l_flex_value_set_id;
174 close getTableValidation;
175 --
176 if l_flex_value_set_id is not null then
177 l_sql := per_cagr_utility_pkg.get_sql_from_vset_id(l_flex_value_set_id);
178 --
179 OPEN flex_cv FOR l_sql;
180 loop
181 fetch flex_cv into l_code,l_val;
182 if flex_cv%notfound then
183 l_val := p_id;
184 exit;
185 elsif l_code = p_id then
186 exit;
187 end if;
188 end loop;
189 --
190 close flex_cv;
191 --
192 else
193 l_val := p_id;
194 end if;
198 --
195 --
196 return l_val;
197 end getIdFlexValue;
199 /* --------------------------------------------------------------------
200 Function : getBankDetails
201 --------------------
202 The following function retrieves the Bank Name or Bank Account Number
203 (depending on Segment Type passed in).
204 ----------------------------------------------------------------------- */
205 function getBankDetails(p_legislation_code varchar2
206 ,p_external_account_id varchar2
207 ,p_segment_type varchar2
208 ,p_mask number) return varchar2 is
209 --
210 cursor getSegment is
211 select decode(substr(meaning,1,3)
212 ,p_legislation_code||'_',substr(meaning,4,length(meaning)-3)
213 ,meaning)
214 from hr_lookups
215 where lookup_type = p_segment_type
216 and lookup_code = p_legislation_code;
217 --
218 l_segment varchar2(30);
219 l_value varchar2(80);
220 l_id_flex_num number;
221 l_id varchar2(80);
222 l_sql varchar2(2000);
223 l_seglen number;
224 begin
225 open getSegment;
226 fetch getSegment into l_segment;
227 close getSegment;
228 --
229 if l_segment is not null then
230 l_sql := 'select id_flex_num,'||l_segment||
231 ' from pay_external_accounts '||
232 ' where external_account_id = :eaId';
233 --
234 EXECUTE IMMEDIATE l_sql INTO l_id_flex_num, l_id
235 USING p_external_account_id;
236 --
237 l_value := getIdFlexValue('BANK',l_id_flex_num,l_segment,l_id);
238 end if;
239 --
240 if p_mask is not null then
241 l_seglen := length(l_value);
242 --
243 if sign(p_mask) = 1 then
244 -- Bugfix 5695538
245 -- Don't do any masking if the number of characters to display is
246 -- greater than the number of characters in the segment
247 if p_mask <= l_seglen then
248 l_value := lpad(substr(l_value,l_seglen-p_mask+1,p_mask),l_seglen,'X');
249 end if;
250 else
251 l_value := rpad(substr(l_value,1,abs(p_mask)),l_seglen,'X');
252 end if;
253 end if;
254 --
255 return l_value;
256 end getBankDetails;
257 --
258 /* --------------------------------------------------------------------
259 Function : getConfig
260 --------------------
261 This following function retrieves information from the
262 "Business Group:SOE" flexfield and returns the value of the segment requested.
263 ----------------------------------------------------------------------- */
264 function getConfig(p_config_type varchar2) return varchar2 is
265 --
266 l_config_value varchar2(80);
267 l_bgid number;
268 l_soe_user_cat varchar2(50);
269 l_init varchar2(10);
270 --
271 begin
272
273 select fnd_profile.value('PER_BUSINESS_GROUP_ID') into l_bgid from dual ;
274 select fnd_profile.value('PAY_SOE_USER_CATEGORY') into l_soe_user_cat from dual ;
275
276 if initialized = true then
277 l_init := 'true';
278 else
279 l_init := 'false';
280 end if ;
281
282 hr_utility.trace('initialized : '||l_init);
283 hr_utility.trace('PER_BUSINESS_GROUP_ID : '||l_bgid);
284 hr_utility.trace('PAY_SOE_USER_CATEGORY : '||l_soe_user_cat);
285
286 -- Bug 7377886
287 -- Added the clause bgid_save = l_bgid,
288 -- so that the SOE intialization part is skipped only if the BG is not changed
289 -- If BG is changed, then the SOE Information from BG needs to re-retrieved
290 if initialized and bgid_save = l_bgid then
291 null;
292 else
293 open config;
294 fetch config into l_config;
295 if config%notfound then
296 close config;
297 initialized := TRUE;
298 return null;
299 end if;
300 close config;
301 --
302 initialized := TRUE;
303 -- Bug 7377886
304 -- Saving the BG id for which the SOE information has been retrieved
305 bgid_save := l_bgid ;
306 end if;
307 --
308 if p_config_type = 'ELEMENTS1' then
309 l_config_value := l_config.elements1;
310 elsif p_config_type = 'ELEMENTS2' then
311 l_config_value := l_config.elements2;
312 elsif p_config_type = 'ELEMENTS3' then
313 l_config_value := l_config.elements3;
314 elsif p_config_type = 'ELEMENTS4' then
315 l_config_value := l_config.elements4;
316 elsif p_config_type = 'ELEMENTS5' then
317 l_config_value := l_config.elements5;
318 elsif p_config_type = 'ELEMENTS6' then
319 l_config_value := l_config.elements6;
320 elsif p_config_type = 'INFORMATION1' then
321 l_config_value := l_config.information1;
322 elsif p_config_type = 'BALANCES1' then
323 l_config_value := l_config.balances1;
324 elsif p_config_type = 'BALANCES2' then
325 l_config_value := l_config.balances2;
326 elsif p_config_type = 'BALANCES3' then
327 l_config_value := l_config.balances3;
328 end if;
329 --
330 hr_utility.trace('Returning '||p_config_type||' : '||l_config_value);
331 return l_config_value;
332 --
333 end getConfig;
334 --
335 end pay_soe_util;
[Home] [Help]