1 PACKAGE BODY PA_CC_TP_UTILS AS
2 /* $Header: PAXTPUTB.pls 120.4 2005/10/06 03:51:30 rgandhi noship $ */
3
4 ------------------------------------------------------------------------
5 --- is_rule_in_schedule_lines_
6 -----This function returns 'Y' if the transfer price rule is used in
7 -----transfer price schedule lines
8 ------------------------------------------------------------------------
9 FUNCTION is_rule_in_schedule_lines (p_rule_id IN NUMBER)
10 RETURN varchar2
11 IS
12
13 CURSOR c_tp_rule IS
14 select '1'
15 from dual
16 where exists (select 'Y'
17 from pa_cc_tp_schedule_lines sl
18 where sl.labor_tp_rule_id=p_rule_id
19 or sl.nl_tp_rule_id=p_rule_id);
20
21 v_ret_code varchar2(1) ;
22 v_dummy varchar2(1);
23
24 BEGIN
25 v_ret_code := 'N';
26
27 OPEN c_tp_rule ;
28 FETCH c_tp_rule INTO v_dummy;
29 IF c_tp_rule%FOUND THEN
30 v_ret_code := 'Y' ;
31 END IF;
32 CLOSE c_tp_rule;
33 RETURN v_ret_code;
34
35 EXCEPTION
36 WHEN NO_DATA_FOUND THEN
37 v_ret_code := 'N' ;
38 Return v_ret_code ;
39 WHEN OTHERS THEN
40 RAISE;
41 END is_rule_in_schedule_lines ;
42
43
44 ------------------------------------------------------------------------
45 --- function get_lowest_org_level
46 -----This function returns lowest org level( 'ORG','OU','LE',or 'BG')for given organization_id
47 -----if an organization has been classified in multiple levels
48 ------------------------------------------------------------------------
49 function get_lowest_org_level(p_organization_id in number) return varchar2
50 is
51
52 cursor c_ou is
53 select '1' from dual
54 where exists (select 'Y'
55 from hr_operating_units o
56 where o.business_group_id=decode(G_global_access,'Y',business_group_id,G_business_group_id)
57 and o.organization_id=p_organization_id);
58 cursor c_le is
59 select '1' from dual
60 where exists (select 'Y'
61 from hr_legal_entities o
62 where o.business_group_id=decode(G_global_access,'Y',business_group_id,G_business_group_id)
63 and o.organization_id=p_organization_id);
64
65 cursor c_bg is
66 select '1' from dual
67 where exists (select 'Y'
68 from hr_organization_units o1,
69 hr_organization_information o2
70 where o1.organization_id=o2.organization_id
71 and o1.organization_id=p_organization_id
72 and o2.org_information_context||'' ='CLASS'
73 and o2.org_information1='HR_BG');
74
75 v_ret_code varchar2(3);
76 v_dummy varchar2(1);
77
78 begin
79
80 v_ret_code:='ORG';
81 return v_ret_code;
82
83 /* Commented for Legal Entity Changes. After 12.0 TP SCHEDULE WILL
84 LOOK only in ORG HIERARCHY AND NOT IN OU,LE,BG
85 open c_ou;
86 fetch c_ou into v_dummy;
87 if c_ou%FOUND then
88 v_ret_code:='OU';
89 return v_ret_code;
90 else
91 open c_le;
92 fetch c_le into v_dummy;
93 if c_le%FOUND then
94 v_ret_code:='LE';
95 return v_ret_code;
96 else
97 if p_organization_id=G_business_group_id then
98 v_ret_code:='BG';
99 return v_ret_code;
100 else
101 open c_bg;
102 fetch c_bg into v_dummy;
103 if c_bg%found and g_global_access='Y' then
104 v_ret_code:='BG';
105 return v_ret_code;
106 end if;
107 end if;
108 end if;
109 end if;
110 close c_le;
111 close c_ou;
112 return v_ret_code;*/
113 exception
114 when no_data_found then
115 v_ret_code:='ORG';
116 return v_ret_code;
117 when others then
118 raise;
119 end get_lowest_org_level;
120
121
122 -------------------------------------------------------------------
123 ----procedure pre_insert_schedule_lines
124 ---- delete affected rows in schedule line lookup table when shcedule lines are inserted or updated
125 ---------------------------------------------------------------------------------
126 procedure pre_insert_schedule_lines(p_tp_schedule_id IN number,
127 p_prvdr_organization_id IN number,
128 p_recvr_organization_id in number)
129 is
130 v_prvdr_org_level varchar2(4);
131 v_recvr_org_level varchar2(4);
132
133 begin
134 v_prvdr_org_level :=get_highest_org_level(p_prvdr_organization_id);
135 if p_recvr_organization_id is not null then
136 v_recvr_org_level :=get_highest_org_level(p_recvr_organization_id);
137 else
138 v_recvr_org_level :='NULL';
139 end if;
140
141 if v_prvdr_org_level='ORG' and v_recvr_org_level='ORG' then
142 delete from pa_cc_tp_schedule_line_lkp
143 where prvdr_organization_id=p_prvdr_organization_id
144 and recvr_organization_id=p_recvr_organization_id
145 and tp_schedule_id=p_tp_schedule_id;
146
147 /* Commented for Legal Entity Changes. After 12.0 TP SCHEDULE WILL
148 LOOK only in ORG HIERARCHY AND NOT IN OU,LE,BG
149 elsif v_prvdr_org_level='ORG' and v_recvr_org_level='OU' then
150 delete from pa_cc_tp_schedule_line_lkp
151 where prvdr_organization_id=p_prvdr_organization_id
152 and recvr_org_id=p_recvr_organization_id
153 and tp_schedule_id=p_tp_schedule_id;
154
155 elsif v_prvdr_org_level='ORG' and v_recvr_org_level='LE' then
156 delete from pa_cc_tp_schedule_line_lkp
157 where tp_schedule_id=p_tp_schedule_id
158 and prvdr_organization_id=p_prvdr_organization_id
159 and recvr_org_id in
160 ( select organization_id
161 from hr_operating_units h
162 where h.legal_entity_id=to_char(p_recvr_organization_id));
163
164 elsif (v_prvdr_org_level='ORG' and v_recvr_org_level='BG') then
165 delete from pa_cc_tp_schedule_line_lkp
166 where tp_schedule_id=p_tp_schedule_id
167 and prvdr_organization_id=p_prvdr_organization_id
168 and recvr_org_id in
169 (select organization_id
170 from hr_operating_units h
171 where h.business_group_id =p_recvr_organization_id);*/
172
173 elsif (v_prvdr_org_level='ORG' and v_recvr_org_level='NULL') then
174 delete from pa_cc_tp_schedule_line_lkp
175 where tp_schedule_id=p_tp_schedule_id
176 and PRVDR_ORGANIZATION_ID =p_prvdr_organization_id;
177
178 /* Commented for Legal Entity Changes. After 12.0 TP SCHEDULE WILL
179 LOOK only in ORG HIERARCHY AND NOT IN OU,LE,BG
180 elsif v_prvdr_org_level='OU' and v_recvr_org_level='OU' then
181 delete from pa_cc_tp_schedule_line_lkp
182 where prvdr_org_id=p_prvdr_organization_id
183 and recvr_org_id=p_recvr_organization_id
184 and tp_schedule_id=p_tp_schedule_id;
185
186 elsif v_prvdr_org_level='OU' and v_recvr_org_level='LE' then
187 delete from pa_cc_tp_schedule_line_lkp
188 where tp_schedule_id=p_tp_schedule_id
189 and prvdr_org_id=p_prvdr_organization_id
190 and recvr_org_id in
191 ( select organization_id
192 from hr_operating_units h
193 where h.legal_entity_id=to_char(p_recvr_organization_id));
194
195 elsif (v_prvdr_org_level='OU' and v_recvr_org_level='BG') then
196 delete from pa_cc_tp_schedule_line_lkp
197 where tp_schedule_id=p_tp_schedule_id
198 and PRVDR_ORG_ID=p_prvdr_organization_id
199 and recvr_org_id in
200 (select organization_id
201 from hr_operating_units h
202 where h.business_group_id =p_recvr_organization_id);
203
204 elsif (v_prvdr_org_level='OU' and v_recvr_org_level='NULL') then
205 delete from pa_cc_tp_schedule_line_lkp
206 where tp_schedule_id=p_tp_schedule_id
207 and PRVDR_ORG_ID=p_prvdr_organization_id;
208
209 elsif v_prvdr_org_level='LE' and v_recvr_org_level='LE' then
210 delete from pa_cc_tp_schedule_line_lkp
211 where tp_schedule_id=p_tp_schedule_id
212 and prvdr_org_id in
213 (select organization_id
214 from hr_operating_units h
215 where h.legal_entity_id=to_char(p_prvdr_organization_id))
216 and recvr_org_id in
217 (select organization_id
218 from hr_operating_units h
219 where h.legal_entity_id=to_char(p_recvr_organization_id));
220 elsif (v_prvdr_org_level='LE' and v_recvr_org_level='BG') then
221 delete from pa_cc_tp_schedule_line_lkp
222 where tp_schedule_id=p_tp_schedule_id
223 and prvdr_org_id in
224 (select organization_id
225 from hr_operating_units h
226 where h.legal_entity_id=to_char(p_prvdr_organization_id))
227 and recvr_org_id in
228 (select organization_id from hr_operating_units h
229 where h.business_group_id=p_recvr_organization_id);
230
231 elsif (v_prvdr_org_level='LE' and v_recvr_org_level='NULL') then
232 delete from pa_cc_tp_schedule_line_lkp
233 where tp_schedule_id=p_tp_schedule_id
234 and prvdr_org_id in
235 (select organization_id
236 from hr_operating_units h
237 where h.legal_entity_id =to_char(p_prvdr_organization_id));
238
239 elsif (v_prvdr_org_level='BG' and v_recvr_org_level='BG') then
240 delete from pa_cc_tp_schedule_line_lkp
241 where tp_schedule_id=p_tp_schedule_id
242 and prvdr_org_id in
243 (select organization_id
244 from hr_operating_units h
245 where h.business_group_id =p_prvdr_organization_id)
246 and recvr_org_id in
247 (select organization_id
248 from hr_operating_units h
249 where h.business_group_id =p_recvr_organization_id);
250
251 elsif (v_prvdr_org_level='BG' and v_recvr_org_level='NULL') then
252 delete from pa_cc_tp_schedule_line_lkp
253 where tp_schedule_id=p_tp_schedule_id
254 and prvdr_org_id in
255 (select organization_id
256 from hr_operating_units h
257 where h.business_group_id =p_prvdr_organization_id);
258
259 Move the end of comment from here to the end for bug 4654754
260 --
261 -- The following cases are functionally not allowed to define.
262 -- But since an org can be at any level ( BG / LE / OU / ORG ) and get_highest_org_level
263 -- would give the maximum level to which an org is defined, the following cases arise
264 --
265
266 elsif (v_prvdr_org_level = 'BG' and v_recvr_org_level = 'LE') then
267 delete from pa_cc_tp_schedule_line_lkp
268 where tp_schedule_id=p_tp_schedule_id
269 and prvdr_org_id in
270 (select organization_id
271 from hr_operating_units h
272 where h.business_group_id =p_prvdr_organization_id)
273 and recvr_org_id in
274 (select organization_id
275 from hr_operating_units h
276 where h.legal_entity_id= to_char(p_prvdr_organization_id));
277
278 elsif (v_prvdr_org_level = 'BG' and v_recvr_org_level = 'OU') then
279 delete from pa_cc_tp_schedule_line_lkp
280 where tp_schedule_id=p_tp_schedule_id
281 and prvdr_org_id in
282 (select organization_id
283 from hr_operating_units h
284 where h.business_group_id =p_prvdr_organization_id)
285 and recvr_org_id =p_recvr_organization_id;
286
287 elsif (v_prvdr_org_level = 'BG' and v_recvr_org_level = 'ORG') then
288 delete from pa_cc_tp_schedule_line_lkp
289 where tp_schedule_id=p_tp_schedule_id
290 and prvdr_org_id in
291 (select organization_id
292 from hr_operating_units h
293 where h.business_group_id =p_prvdr_organization_id)
294 and recvr_organization_id =p_recvr_organization_id;
295
296 elsif (v_prvdr_org_level = 'LE' and v_recvr_org_level = 'OU') then
297 delete from pa_cc_tp_schedule_line_lkp
298 where tp_schedule_id=p_tp_schedule_id
299 and prvdr_org_id in
300 (select organization_id
301 from hr_operating_units h
302 where h.legal_entity_id=to_char(p_prvdr_organization_id))
303 and recvr_org_id = p_recvr_organization_id ;
304 elsif (v_prvdr_org_level = 'LE' and v_recvr_org_level = 'ORG') then
305 delete from pa_cc_tp_schedule_line_lkp
306 where tp_schedule_id=p_tp_schedule_id
307 and prvdr_org_id in
308 (select organization_id
309 from hr_operating_units h
310 where h.legal_entity_id=to_char(p_prvdr_organization_id))
311 and recvr_organization_id = p_recvr_organization_id ;
312 elsif (v_prvdr_org_level = 'OU' and v_recvr_org_level = 'ORG') then
313 delete from pa_cc_tp_schedule_line_lkp
314 where tp_schedule_id=p_tp_schedule_id
315 and prvdr_org_id = p_prvdr_organization_id
316 and recvr_organization_id = p_recvr_organization_id ; End of 4654754 */
317 else
318 null;
319 ---anything need to do here ??
320 end if;
321
322 end pre_insert_schedule_lines;
323
324 -----------------------------------------------------------------------
325 ----procedure pre_delete_schedule_lines
326 -----delete the row from schedule line lookup table if a schedule line is to be deleted from
327 -----schedule line table
328 ------------------------------------------------------------------------
329 procedure pre_delete_schedule_lines(p_tp_schedule_id in number,
330 p_tp_schedule_line_id in number)
331 is
332 begin
333 delete from pa_cc_tp_schedule_line_lkp
334 where tp_schedule_id=p_tp_schedule_id
335 and tp_schedule_line_id=p_tp_schedule_line_id;
336
337 end pre_delete_schedule_lines;
338
339
340 -------------------------------------------------------
341 ---procedure check_delete_tp_schedule_ok is an central API which check if a transfer
342 ---price schedule has been used in any other features. If yes, then it will return
343 ----error code and error stage
344 ----------------------------------------------------------
345 procedure check_delete_tp_schedule_ok(p_tp_schedule_id in number,
346 x_error_code in out NOCOPY number,/*File.sql.39*/
347 x_error_stage in out NOCOPY varchar2,/*File.sql.39*/
348 x_error_stack in out NOCOPY varchar2)/*File.sql.39*/
349 IS
350
351 old_stack varchar2(630);
352 l_return_val varchar2(1);
353
354 begin
355 x_error_code := 0;
356 old_stack := x_error_stack;
357 x_error_stack := x_error_stack || '->check_delete_tp_schedule_ok';
358
359 -- Check if schedule is used in projects or tasks
360 x_error_stage := 'check if projects or tasks use transfer price schedule'||p_tp_schedule_id ;
361 l_return_val := pa_project_utils.is_tp_schd_proj_task(p_tp_schedule_id);
362 if ( l_return_val = 'Y' ) then
363 x_error_code := 10;
364 x_error_stage := 'PA_CC_TP_SCHEDULE_IN_PROJ';
365 return;
366 end if;
367 exception
368 when others then
369 x_error_code := SQLCODE;
370 rollback;
371 return;
372 end check_delete_tp_schedule_ok;
373
374 -------------------------------------------------------
375 ---procedure check_del_update_rule_ok is an central API which check if a transfer
376 ---price rule has been used in schedule line or any other features. If yes, then it will return
377 ----error code and error stage
378 ----------------------------------------------------------
382 x_error_stack in out NOCOPY varchar2/*File.sql.39*/)
379 procedure check_del_update_rule_ok(p_tp_rule_id in number,
380 x_error_code in out NOCOPY number,/*File.sql.39*/
381 x_error_stage in out NOCOPY varchar2,/*File.sql.39*/
383 IS
384
385 old_stack varchar2(630);
386 l_return_val varchar2(1);
387
388 begin
389 x_error_code := 0;
390 old_stack := x_error_stack;
391 x_error_stack := x_error_stack || '->check_del_update_rule_ok';
392
393 -- Check if rule is used in schedule lines
394 x_error_stage := 'check if schedule lines use rule: '||p_tp_rule_id ;
395 l_return_val:=is_rule_in_schedule_lines(p_tp_rule_id);
396 if ( l_return_val = 'Y' ) then
397 x_error_code := 10;
398 x_error_stage := 'PA_CC_TP_NO_DELETE_RULE';
399 return;
400 end if;
401 exception
402 when others then
403 x_error_code := SQLCODE;
404 rollback;
405 return;
406 end check_del_update_rule_ok;
407
408
409 ------------------------------------------
410 ----function get_highest_org_level returns the highest level of the organization
411 -----if it has been classified at diffierent levels
412 -----------------------------------------------------------
413 function get_highest_org_level(p_organization_id in number)
414 return varchar2
415 IS
416 cursor c_bg is
417 select '1' from dual
418 where exists (select 'Y'
419 from hr_organization_units o1,
420 hr_organization_information o2
421 where o1.organization_id=o2.organization_id
422 and o1.organization_id=p_organization_id
423 and o2.org_information_context||''='CLASS'
424 and o2.org_information1='HR_BG');
425
426 cursor c_ou is
427 select '1' from dual
428 where exists (select 'Y'
429 from hr_operating_units
430 where organization_id=p_organization_id
431 and business_group_id=decode(G_global_access,'Y',business_group_id,G_business_group_id));
432 cursor c_le is
433 select '1' from dual
434 where exists (select 'Y'
435 from hr_legal_entities
436 where organization_id=p_organization_id
437 and business_group_id=decode(G_global_access,'Y',business_group_id,G_business_group_id));
438
439 v_ret_code varchar2(3);
440 v_dummy varchar2(1);
441 begin
442
443 /* Commented for Legal Entity Changes. After 12.0 TP SCHEDULE WILL
444 LOOK only in ORG HIERARCHY AND NOT IN OU,LE,BG
445
446 if (p_organization_id=G_business_group_id ) then
447 v_ret_code:='BG';
448 return(v_ret_code);
449 else
450 open c_bg;
451 fetch c_bg into v_dummy;
452 if (c_bg%found and G_global_access='Y') then
453 v_ret_code:='BG';
454 return(v_ret_code);
455 else
456 open c_le;
457 fetch c_le into v_dummy;
458 if c_le%found then
459 v_ret_code:='LE';
460 return (v_ret_code);
461 else
462 open c_ou;
463 fetch c_ou into v_dummy;
464 if c_ou%found then
465 v_ret_code:='OU';
466 return (v_ret_code);
467 else
468 v_ret_code:='ORG';
469 return (v_ret_code);
470 end if;
471 end if;
472 end if;
473 end if;
474 close c_le;
475
476 close c_ou;*/
477
478 v_ret_code:='ORG';
479 return (v_ret_code);
480
481 exception
482 when no_data_found then
483 v_ret_code:='ORG';
484 return v_ret_code;
485 when others then
486 raise;
487 end get_highest_org_level;
488
489 END PA_CC_TP_UTILS;
490
[Home] [Help]