[Home] [Help]
PACKAGE BODY: APPS.PER_RU_CURRENCY_CONVERSION
Source
1 PACKAGE BODY PER_RU_CURRENCY_CONVERSION as
2 /* $Header: perucurr.pkb 120.2.12010000.1 2008/10/01 07:14:26 parusia noship $ */
3 --
4 PROCEDURE currency_rur_to_rub
5 (errbuf OUT NOCOPY VARCHAR2
6 ,retcode OUT NOCOPY NUMBER
7 ,p_business_group_id IN NUMBER
8 ,p_conv_curr_code IN VARCHAR2
9 ) IS
10
11 CURSOR get_all_bgs( cp_old_currency VARCHAR2, cp_new_currency VARCHAR2 ) IS
12 SELECT hoi.organization_id, hou.name
13 FROM hr_organization_units hou,
14 hr_organization_information hoi,
15 hr_organization_information hoi1
16 WHERE hoi.org_information_context = 'Business Group Information'
17 AND hoi.org_information9 = 'RU'
18 AND hoi.org_information10 in (cp_old_currency, cp_new_currency)
19 AND hou.organization_id = hoi.organization_id
20 AND hou.organization_id = hoi1.organization_id
21 AND hoi1.org_information_context = 'CLASS'
22 AND hoi1.org_information1 = 'HR_BG'
23 AND hoi1.org_information2 = 'Y'
24 AND NOT EXISTS ( SELECT 1
25 FROM pay_patch_status pps
26 WHERE pps.patch_number = to_char(hoi.organization_id)
27 AND pps.patch_name = 'Currency_Conversion_for_Russia'
28 AND pps.legislation_code = 'RU'
29 AND pps.status = 'C' );
30
31 CURSOR c_pay_gl_interface ( cp_business_group_id NUMBER ) IS
32 SELECT pgi.assignment_action_id
33 FROM pay_gl_interface pgi
34 ,pay_payroll_actions ppa
35 WHERE ppa.business_group_id = cp_business_group_id
36 AND pgi.run_payroll_action_id = ppa.payroll_action_id;
37
38 CURSOR c_legi_ele_tmplt IS
39 SELECT template_id
40 FROM pay_element_templates
41 WHERE legislation_code = 'RU';
42
43 CURSOR c_bg_ele_tmplt( cp_business_group_id NUMBER ) IS
44 SELECT template_id
45 FROM pay_element_templates
46 WHERE business_group_id = cp_business_group_id;
47
48 CURSOR c_ben_chc_popl( cp_business_group_id NUMBER ) IS
49 SELECT rowid
50 FROM ben_pil_elctbl_chc_popl
51 WHERE business_group_id = cp_business_group_id;
52
53 CURSOR c_ben_elig_per( cp_business_group_id NUMBER ) IS
54 SELECT rowid
55 FROM ben_elig_per_f
56 WHERE business_group_id = cp_business_group_id;
57
58 ln_business_group_id NUMBER;
59 lv_business_group_name VARCHAR2(400);
60
61 ln_legislation_done NUMBER;
62 ln_commit_cnt NUMBER;
63 lv_old_currency VARCHAR2(10);
64 lv_new_currency VARCHAR2(10);
65 ln_ota_act_ver_count NUMBER;
66
67 BEGIN
68
69 ln_legislation_done := 0;
70 lv_old_currency := 'RUR';
71 lv_new_currency := 'RUB';
72
73 OPEN get_all_bgs( lv_old_currency, lv_new_currency );
74 LOOP
75 FETCH get_all_bgs INTO ln_business_group_id
76 ,lv_business_group_name;
77 EXIT WHEN get_all_bgs%NOTFOUND;
78
79 IF ln_legislation_done = 0 THEN
80
81 UPDATE pay_balance_types
82 SET currency_code = lv_new_currency
83 WHERE legislation_code = 'RU'
84 AND currency_code = lv_old_currency;
85
86 UPDATE pay_element_types_f
87 SET input_currency_code = decode(input_currency_code,
88 lv_old_currency,
89 lv_new_currency,
90 input_currency_code )
91 ,output_currency_code = decode(output_currency_code,
92 lv_old_currency,
93 lv_new_currency,
94 output_currency_code )
95 WHERE legislation_code = 'RU'
96 AND ( input_currency_code = lv_old_currency OR
97 output_currency_code = lv_old_currency );
98
99 UPDATE pay_legislation_rules
100 SET rule_mode = lv_new_currency
101 WHERE legislation_code = 'RU'
102 AND rule_type = 'DC'
103 AND rule_mode = lv_old_currency;
104
105 UPDATE pay_leg_setup_defaults
106 SET currency_code = lv_new_currency
107 WHERE legislation_code = 'RU'
108 AND currency_code = lv_old_currency;
109
110 UPDATE pay_monetary_units
111 SET currency_code = lv_new_currency
112 WHERE legislation_code = 'RU'
113 AND currency_code = lv_old_currency;
114
115 UPDATE pay_payment_types
116 SET currency_code = lv_new_currency
117 WHERE territory_code = 'RU'
118 AND currency_code = lv_old_currency;
119
120 UPDATE pay_pss_transaction_steps
121 SET currency_code = lv_new_currency
122 WHERE territory_code = 'RU'
123 AND currency_code = lv_old_currency;
124
125 UPDATE pqp_exception_reports
126 SET currency_code = lv_new_currency
127 WHERE legislation_code = 'RU'
128 AND currency_code = lv_old_currency;
129
130 FOR tmplt IN c_legi_ele_tmplt
131 LOOP
132
133 UPDATE pay_shadow_balance_types
134 SET currency_code = lv_new_currency
135 WHERE template_id = tmplt.template_id
136 AND currency_code = lv_old_currency;
137
138 UPDATE pay_shadow_element_types
139 SET input_currency_code = decode(input_currency_code,
140 lv_old_currency,
141 lv_new_currency,
142 input_currency_code )
143 ,output_currency_code = decode(output_currency_code,
144 lv_old_currency,
145 lv_new_currency,
146 output_currency_code )
147 WHERE template_id = tmplt.template_id
148 AND ( input_currency_code = lv_old_currency OR
149 output_currency_code = lv_old_currency );
150
151 END LOOP;
152
153 ln_legislation_done := 1;
154
155 END IF;
156
157 /* PAY_BALANCE_TYPES */
158
159 UPDATE pay_balance_types
160 SET currency_code = lv_new_currency
161 WHERE business_group_id = ln_business_group_id
162 AND currency_code = lv_old_currency;
163
164 /* PAY_ELEMENT_TYPES_F */
165
166 UPDATE pay_element_types_f
167 SET input_currency_code = decode(input_currency_code,
168 lv_old_currency,
169 lv_new_currency,
170 input_currency_code )
171 ,output_currency_code = decode(output_currency_code,
172 lv_old_currency,
173 lv_new_currency,
174 output_currency_code )
175 WHERE business_group_id = ln_business_group_id
176 AND ( input_currency_code = lv_old_currency OR
177 output_currency_code = lv_old_currency );
178
179 /* PAY_MONETARY_UNITS */
180
181 UPDATE pay_monetary_units
182 SET currency_code = lv_new_currency
183 WHERE business_group_id = ln_business_group_id
184 AND currency_code = lv_old_currency;
185
186 /* PAY_ORG_PAYMENT_METHODS_F */
187
188 UPDATE pay_org_payment_methods_f
189 SET currency_code = lv_new_currency
190 WHERE business_group_id = ln_business_group_id
191 AND currency_code = lv_old_currency;
192
193 /* PER_QUALIFICATIONS */
194
195 UPDATE per_qualifications
196 SET fee_currency = lv_new_currency
197 WHERE business_group_id = ln_business_group_id
198 AND fee_currency = lv_old_currency;
199
200 /* PER_RECRUITMENT_ACTIVITIES */
201
202 UPDATE per_recruitment_activities
203 SET currency_code = lv_new_currency
204 WHERE business_group_id = ln_business_group_id
205 AND currency_code = lv_old_currency;
206
207 /* PER_WORK_INCIDENTS */
208
209 UPDATE per_work_incidents pwi
210 SET compensation_currency = lv_new_currency
211 WHERE compensation_currency = lv_old_currency
212 AND EXISTS ( SELECT 1
213 FROM per_all_people_f ppf
214 WHERE ppf.person_id = pwi.person_id
215 AND ppf.business_group_id = ln_business_group_id );
216
217 /* PER_SALARY_SURVEY_LINES */
218
219 UPDATE per_salary_survey_lines
220 SET currency_code = lv_new_currency
221 WHERE currency_code = lv_old_currency;
222
223 /* PQH_ACCOMMODATIONS_F */
224
225 UPDATE pqh_accommodations_f
226 SET rental_value_currency = lv_new_currency
227 WHERE business_group_id = ln_business_group_id
228 AND rental_value_currency = lv_old_currency;
229
230 /* PQH_ASSIGN_ACCOMMODATIONS_F */
231
232 UPDATE pqh_assign_accommodations_f
233 SET indemnity_currency = lv_new_currency
234 WHERE business_group_id = ln_business_group_id
235 AND indemnity_currency = lv_old_currency;
236
237 /* PQH_FR_VALIDATIONS */
238
239 UPDATE pqh_fr_validations
240 SET deduction_currency_code = decode( deduction_currency_code,
241 lv_old_currency,
242 lv_new_currency,
243 deduction_currency_code )
244 ,employee_currency_code = decode( employee_currency_code,
245 lv_old_currency,
246 lv_new_currency,
247 employee_currency_code )
248 ,employer_currency_code = decode( employer_currency_code,
249 lv_old_currency,
250 lv_new_currency,
251 employer_currency_code )
252 WHERE business_group_id = ln_business_group_id
253 AND ( deduction_currency_code = lv_old_currency OR
254 employee_currency_code = lv_old_currency OR
255 employer_currency_code = lv_old_currency );
256
257 /* PQH_BUDGETS */
258
259 UPDATE pqh_budgets
260 SET currency_code = lv_new_currency
261 WHERE business_group_id = ln_business_group_id
262 AND currency_code = lv_old_currency;
263
264 /* PQP_EXCEPTION_REPORTS */
265
266 UPDATE pqp_exception_reports
267 SET currency_code = lv_new_currency
268 WHERE business_group_id = ln_business_group_id
269 AND currency_code = lv_old_currency;
270
271 /* PQP_VEHICLE_REPOSITORY_F */
272
273 UPDATE pqp_vehicle_repository_f
274 SET currency_code = lv_new_currency
275 WHERE business_group_id = ln_business_group_id
276 AND currency_code = lv_old_currency;
277
278 /* PQP_VEHICLE_DETAILS */
279
280 UPDATE pqp_vehicle_details
281 SET currency_code = lv_new_currency
282 WHERE business_group_id = ln_business_group_id
283 AND currency_code = lv_old_currency;
284
285 /* PSP_ADJUSTMENT_CONTROL_TABLE */
286
287 UPDATE psp_adjustment_control_table
288 SET currency_code = lv_new_currency
289 WHERE business_group_id = ln_business_group_id
290 AND currency_code = lv_old_currency;
291
292 /* PSP_DISTRIBUTION_INTERFACE */
293
294 UPDATE psp_distribution_interface
295 SET currency_code = lv_new_currency
296 WHERE business_group_id = ln_business_group_id
297 AND currency_code = lv_old_currency;
298
299 /* PSP_PAYROLL_CONTROLS */
300
301 UPDATE psp_payroll_controls
302 SET currency_code = lv_new_currency
303 WHERE business_group_id = ln_business_group_id
304 AND currency_code = lv_old_currency;
305
306 /* PSP_PAYROLL_INTERFACE */
307
308 UPDATE psp_payroll_interface
309 SET currency_code = lv_new_currency
310 WHERE business_group_id = ln_business_group_id
311 AND currency_code = lv_old_currency;
312
313 /* BEN_ACTL_PREM_F */
314
315 UPDATE ben_actl_prem_f
316 SET uom = decode( uom, lv_old_currency, lv_new_currency, uom )
317 ,cr_lkbk_uom = decode( cr_lkbk_uom, lv_old_currency,
318 lv_new_currency, cr_lkbk_uom )
319 WHERE business_group_id = ln_business_group_id
320 AND ( uom = lv_old_currency OR cr_lkbk_uom = lv_old_currency );
321
322 /* BEN_BNFTS_BAL_F */
323
324 UPDATE ben_bnfts_bal_f
325 SET uom = lv_new_currency
326 WHERE business_group_id = ln_business_group_id
327 AND uom = lv_old_currency;
328
329 /* BEN_CNTNG_PRTN_ELIG_PRFL_F */
330
331 UPDATE ben_cntng_prtn_elig_prfl_f
332 SET pymt_must_be_rcvd_uom = lv_new_currency
333 WHERE business_group_id = ln_business_group_id
334 AND pymt_must_be_rcvd_uom = lv_old_currency;
335
336 /* BEN_CNTNG_PRTN_PRFL_RT_F */
337
338 UPDATE ben_cntng_prtn_prfl_rt_f
339 SET pymt_must_be_rcvd_uom = lv_new_currency
340 WHERE business_group_id = ln_business_group_id
341 AND pymt_must_be_rcvd_uom = lv_old_currency;
342
343 /* BEN_DRVBL_FCTR_UOM */
344
345 UPDATE ben_drvbl_fctr_uom
346 SET uom_cd = lv_new_currency
347 WHERE business_group_id = ln_business_group_id
348 AND uom_cd = lv_old_currency;
349
350 /* BEN_ENRT_PREM_RBV */
351
352 UPDATE ben_enrt_prem_rbv
353 SET uom = lv_new_currency
354 WHERE business_group_id = ln_business_group_id
355 AND uom = lv_old_currency;
356
357 /* BEN_PGM_F */
358
359 UPDATE ben_pgm_f
360 SET pgm_uom = lv_new_currency
361 WHERE business_group_id = ln_business_group_id
362 AND pgm_uom = lv_old_currency;
363
364 /* BEN_PIL_EPE_POPL_RBV */
365
366 UPDATE ben_pil_epe_popl_rbv
367 SET uom = lv_new_currency
368 WHERE business_group_id = ln_business_group_id
369 AND uom = lv_old_currency;
370
371 /* BEN_PL_FRFS_VAL_F */
372
373 UPDATE ben_pl_frfs_val_f
374 SET uom = lv_new_currency
375 WHERE business_group_id = ln_business_group_id
376 AND uom = lv_old_currency;
377
378 /* BEN_PRTT_ENRT_RSLT_F_RBV */
379
380 UPDATE ben_prtt_enrt_rslt_f_rbv
381 SET uom = lv_new_currency
382 WHERE business_group_id = ln_business_group_id
383 AND uom = lv_old_currency;
384
385 /* BEN_PRTT_PREM_F */
386
387 UPDATE ben_prtt_prem_f
388 SET std_prem_uom = lv_new_currency
389 WHERE business_group_id = ln_business_group_id
390 AND std_prem_uom = lv_old_currency;
391
392 /* BEN_PRTT_REIMBMT_RQST_F */
393
394 UPDATE ben_prtt_reimbmt_rqst_f
395 SET rqst_amt_uom = lv_new_currency
396 WHERE business_group_id = ln_business_group_id
397 AND rqst_amt_uom = lv_old_currency;
398
399 /* BEN_PRTT_PREM_BY_MO_F */
400
401 UPDATE ben_prtt_prem_by_mo_f
402 SET antcpd_prtt_cntr_uom = lv_new_currency
403 WHERE business_group_id = ln_business_group_id
404 AND antcpd_prtt_cntr_uom = lv_old_currency;
405
406 /* BEN_COMP_LVL_FCTR */
407
408 UPDATE ben_comp_lvl_fctr
409 SET comp_lvl_uom = lv_new_currency
410 WHERE business_group_id = ln_business_group_id
411 AND comp_lvl_uom = lv_old_currency;
412
413 /* BEN_PL_R_OIPL_PREM_BY_MO_F */
414
415 UPDATE ben_pl_r_oipl_prem_by_mo_f
416 SET uom = lv_new_currency
417 WHERE business_group_id = ln_business_group_id
418 AND uom = lv_old_currency;
419
420 /* BEN_PL_F */
421
422 UPDATE BEN_PL_F
423 SET nip_pl_uom = lv_new_currency
424 WHERE business_group_id = ln_business_group_id
425 AND nip_pl_uom = lv_old_currency;
426
427 /* BEN_PL_BNF_F */
428
429 UPDATE ben_pl_bnf_f
430 SET amt_dsgd_uom = lv_new_currency
431 WHERE business_group_id = ln_business_group_id
432 AND amt_dsgd_uom = lv_old_currency;
433
434 /* BEN_PL_R_OIPL_ASSET_F */
435
436 UPDATE ben_pl_r_oipl_asset_f
437 SET mkt_val_uom = lv_new_currency
438 WHERE business_group_id = ln_business_group_id
439 AND mkt_val_uom = lv_old_currency;
440
441 /* BEN_CRT_ORDR */
442
443 UPDATE ben_crt_ordr
444 SET uom = lv_new_currency
445 WHERE business_group_id = ln_business_group_id
446 AND uom = lv_old_currency;
447
448 /* BEN_ENRT_PREM */
449
450 UPDATE ben_enrt_prem
451 SET uom = lv_new_currency
452 WHERE business_group_id = ln_business_group_id
453 AND uom = lv_old_currency;
454
455 /* IRC_ALL_RECRUITING_SITES */
456
457 UPDATE irc_all_recruiting_sites
458 SET posting_cost_currency = lv_new_currency
459 WHERE posting_cost_currency = lv_old_currency;
460
461 /* IRC_SEARCH_CRITERIA */
462
463 UPDATE irc_search_criteria
464 SET salary_currency = lv_new_currency
465 WHERE salary_currency = lv_old_currency;
466
467 /* OTA_ACTIVITY_VERSIONS */
468
469 SELECT count(*)
470 INTO ln_ota_act_ver_count
471 FROM ota_activity_versions
472 WHERE budget_currency_code = lv_old_currency;
473 IF ln_ota_act_ver_count > 0
474 THEN
475 UPDATE ota_activity_versions
476 SET budget_currency_code = lv_new_currency
477 WHERE budget_currency_code = lv_old_currency;
478 END IF;
479
480 /* OTA_EVENTS */
481
482 UPDATE ota_events
483 SET budget_currency_code = decode( budget_currency_code,
484 lv_old_currency,
485 lv_new_currency,
486 budget_currency_code)
487 ,currency_code = decode( currency_code,
488 lv_old_currency,
489 lv_new_currency,
490 currency_code)
491 WHERE business_group_id = ln_business_group_id
492 AND ( budget_currency_code = lv_old_currency OR
493 currency_code = lv_old_currency );
494
495 /* OTA_PRICE_LISTS */
496
497 UPDATE ota_price_lists
498 SET currency_code = lv_new_currency
499 WHERE business_group_id = ln_business_group_id
500 AND currency_code = lv_old_currency;
501
502 /* OTA_SUPPLIABLE_RESOURCES */
503
504 UPDATE ota_suppliable_resources
505 SET currency_code = lv_new_currency
506 WHERE business_group_id = ln_business_group_id
507 AND currency_code = lv_old_currency;
508
509 /* OTA_TRAINING_PLANS */
510
511 UPDATE ota_training_plans
512 SET budget_currency = lv_new_currency
513 WHERE business_group_id = ln_business_group_id
514 AND budget_currency = lv_old_currency;
515
516 /* OTA_TRAINING_PLAN_COSTS */
517
518 UPDATE ota_training_plan_costs
519 SET currency_code = lv_new_currency
520 WHERE business_group_id = ln_business_group_id
521 AND currency_code = lv_old_currency;
522
523 /* PAY_GL_INTERFACE */
524
525 ln_commit_cnt := 0;
526
527 FOR paygl IN c_pay_gl_interface( ln_business_group_id )
528 LOOP
529
530 UPDATE pay_gl_interface
531 SET currency_code = lv_new_currency
532 WHERE assignment_action_id = paygl.assignment_action_id
533 AND currency_code = lv_old_currency;
534
535 ln_commit_cnt := ln_commit_cnt + 1;
536
537 IF ln_commit_cnt >= 100 THEN
538 commit;
539 ln_commit_cnt := 0;
540 END IF;
541
542 END LOOP;
543
544 /* PAY SHADOW SCHEMA */
545
546 FOR ele_tmplt IN c_bg_ele_tmplt( ln_business_group_id )
547 LOOP
548
549 UPDATE pay_shadow_balance_types
550 SET currency_code = lv_new_currency
551 WHERE template_id = ele_tmplt.template_id
552 AND currency_code = lv_old_currency;
553
554 UPDATE pay_shadow_element_types
555 SET input_currency_code = decode(input_currency_code,
556 lv_old_currency,
557 lv_new_currency,
558 input_currency_code )
559 ,output_currency_code = decode(output_currency_code,
560 lv_old_currency,
561 lv_new_currency,
562 output_currency_code )
563 WHERE template_id = ele_tmplt.template_id
564 AND ( input_currency_code = lv_old_currency OR
565 output_currency_code = lv_old_currency );
566
567 END LOOP;
568
569 /* BEN_PIL_ELCTBL_CHC_POPL */
570
571 ln_commit_cnt := 0;
572
573 FOR chc IN c_ben_chc_popl( ln_business_group_id )
574 LOOP
575
576 UPDATE ben_pil_elctbl_chc_popl
577 SET uom = lv_new_currency
578 WHERE rowid = chc.rowid
579 AND uom = lv_old_currency;
580
581 ln_commit_cnt := ln_commit_cnt + 1;
582
583 IF ln_commit_cnt >= 100 THEN
584 commit;
585 ln_commit_cnt := 0;
586 END IF;
587
588 END LOOP;
589
590 /* BEN_ELIG_PER_F */
591
592 ln_commit_cnt := 0;
593
594 FOR elig IN c_ben_elig_per( ln_business_group_id )
595 LOOP
596
597 UPDATE ben_elig_per_f
598 SET rt_comp_ref_uom = lv_new_currency
599 WHERE rowid = elig.rowid
600 AND rt_comp_ref_uom = lv_old_currency;
601
602 ln_commit_cnt := ln_commit_cnt + 1;
603
604 IF ln_commit_cnt >= 100 THEN
605 commit;
606 ln_commit_cnt := 0;
607 END IF;
608
609 END LOOP;
610
611 /* HR_ORGANIZATION_INFORMATION */
612
613 UPDATE hr_organization_information
614 SET org_information10 = lv_new_currency
615 WHERE organization_id = ln_business_group_id
616 AND org_information_context = 'Business Group Information'
617 AND org_information10 = lv_old_currency;
618
619 INSERT INTO pay_patch_status
620 (id, patch_number, patch_name, applied_date, status,
621 description, update_date, legislation_code)
622 VALUES (pay_patch_status_s.nextval, to_char(ln_business_group_id),
623 'Currency_Conversion_for_Russia', sysdate, 'C',
624 lv_business_group_name, sysdate, 'RU');
625
626 END LOOP;
627
628 CLOSE get_all_bgs;
629
630 --COMMIT;
631
632 EXCEPTION
633 WHEN others THEN
634 raise;
635
636 END currency_rur_to_rub;
637
638
639 FUNCTION get_converted_curr_code ( p_business_group_id NUMBER )
640 RETURN VARCHAR2 IS
641
642 ln_conv_curr_cnt NUMBER;
643 lv_conv_curr_code VARCHAR2(80);
644
645 BEGIN
646
647 SELECT count(*)
648 INTO ln_conv_curr_cnt
649 FROM hr_organization_units hou,
650 hr_organization_information hoi,
651 hr_organization_information hoi1
652 WHERE hoi.org_information_context = 'Business Group Information'
653 AND hoi.org_information9 = 'RU'
654 AND hoi.org_information10 = 'RUR'
655 AND hou.organization_id = hoi.organization_id
656 AND hou.organization_id = hoi1.organization_id
657 AND hoi1.org_information_context = 'CLASS'
658 AND hoi1.org_information1 = 'HR_BG'
659 AND hoi1.org_information2 = 'Y';
660
661 IF ln_conv_curr_cnt = 0 THEN
662
663 lv_conv_curr_code := 'CONVERTED';
664
665 ELSE
666
667 lv_conv_curr_code := 'CONVERTING';
668
669 END IF;
670
671 RETURN lv_conv_curr_code;
672
673 END get_converted_curr_code;
674
675 END PER_RU_CURRENCY_CONVERSION ;