[Home] [Help]
PACKAGE BODY: APPS.PAY_ZA_BANK_DDF_UPD
Source
1 PACKAGE BODY PAY_ZA_BANK_DDF_UPD AS
2 /* $Header: pyzabnku.pkb 120.1 2010/03/25 09:01:08 rbabla noship $ */
3
4 PROCEDURE qualify_bnk_update(
5 p_assignment_id number
6 , p_qualifier out nocopy varchar2) AS
7
8 --Check if the assignment is either active or terminated after tax year start of 2010 year
9 cursor csr_assignments
10 is
11 select 1
12 from per_all_assignments_f paaf
13 where paaf.effective_end_date >= to_date('01/03/2009','dd/mm/yyyy')
14 and paaf.assignment_id = p_assignment_id
15 and paaf.business_group_id in (select business_group_id
16 from per_business_groups
17 where legislation_code='ZA');
18
19
20 l_exists number;
21
22 begin
23
24 hr_utility.set_location('Entering qualify_bnk_update',10);
25 p_qualifier :='N';
26 open csr_assignments;
27 fetch csr_assignments into l_exists;
28 if csr_assignments%found then
29 p_qualifier := 'Y';
30 end if;
31 close csr_assignments;
32 hr_utility.set_location('Exiting qualify_bnk_update',10);
33
34 END qualify_bnk_update;
35
36 PROCEDURE update_bnk(p_assignment_id number) IS
37
38 -- Cursor to fetch all the eligible assignments for the bank DDF update
39 cursor csr_ppm_category
40 is
41 select ppt.payment_type_name, ppymf.personal_payment_method_id,
42 ppymf.object_version_number, ppymf.effective_start_date
43 from pay_personal_payment_methods_f ppymf
44 , pay_org_payment_methods_f popmf
45 , pay_payment_types ppt
46 where ppt.territory_code='ZA'
47 and ppt.payment_type_name in ('Cheque','Credit Transfer','Manual Payment','ACB')
48 and popmf.payment_type_id = ppt.payment_type_id
49 and popmf.effective_end_date = (select max(effective_end_date)
50 from pay_org_payment_methods_f popmf2
51 where popmf.org_payment_method_id = popmf2.org_payment_method_id)
52 and ppymf.org_payment_method_id = popmf.org_payment_method_id
53 and ppymf.assignment_id = p_assignment_id
54 and ppymf.ppm_information_category is null
55 for update of ppymf.personal_payment_method_id;
56
57 l_object_version_number number;
58 l_effective_start_date date;
59 l_effective_end_date date;
60 l_comment_id number;
61 l_external_account_id number;
62 l_assignment_id number;
63 l_legislation_code varchar2(3) :='ZA';
64
65 BEGIN
66
67 -- call the procedure for each of the assignment_action_id's fetched
68 hr_utility.set_location('Entering update_bnk',10);
69 hr_utility.set_location('p_assignment_id = ' || p_assignment_id,30);
70
71 l_assignment_id := p_assignment_id;
72 hr_utility.set_location('Assignment_id = '||l_assignment_id,33);
73 FOR rec_ppm_category in csr_ppm_category
74 LOOP
75 begin
76 hr_utility.set_location('Updating PER_PAY_METH_ID:'||rec_ppm_category.personal_payment_method_id,34);
77 l_object_version_number := rec_ppm_category.object_version_number;
78 /*
79 hr_personal_pay_method_api.update_personal_pay_method
80 (
81 P_VALIDATE => false
82 ,P_EFFECTIVE_DATE => rec_ppm_category.effective_start_date
83 ,P_DATETRACK_UPDATE_MODE => 'CORRECTION'
84 ,p_personal_payment_method_id=> rec_ppm_category.personal_payment_method_id
85 ,p_ppm_information_category => l_legislation_code||'_'||upper(rec_ppm_category.payment_type_name)
86 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
87 ,P_COMMENT_ID => l_comment_id
88 ,P_EXTERNAL_ACCOUNT_ID => l_external_account_id
89 ,P_EFFECTIVE_START_DATE => l_effective_start_date
90 ,P_EFFECTIVE_END_DATE => l_effective_end_date
91 );
92 */
93 update pay_personal_payment_methods_f
94 set ppm_information_category = l_legislation_code||'_'||upper(rec_ppm_category.payment_type_name)
95 where personal_payment_method_id = rec_ppm_category.personal_payment_method_id;
96
97 hr_utility.set_location('Updated PER_PAY_METH_ID:'||rec_ppm_category.personal_payment_method_id,34);
98
99 exception
100 WHEN others then
101 hr_utility.set_location('Inside exception for PER_PAY_METH_ID :'||rec_ppm_category.personal_payment_method_id,34);
102
103 end;
104
105 END LOOP;
106
107 hr_utility.set_location('Exiting update_bnk',50);
108 END update_bnk;
109
110 END PAY_ZA_BANK_DDF_UPD ;