1 PACKAGE BODY PQP_GB_PENSION_SCHEME_UPDATE AS
2 -- /* $Header: pqpgbschupd.pkb 120.0.12000000.1 2007/02/06 15:28:22 appldev noship $ */
3
4 ----------------------------------------------------------------------------+
5 --This procedure is called from concurrent program to update
6 --Pension scheme type(of AVC elements) information held in element type eit
7 ----------------------------------------------------------------------------+
8 PROCEDURE process_scheme_type
9 (errbuf OUT NOCOPY VARCHAR2
10 ,retcode OUT NOCOPY VARCHAR2
11 ,p_business_group_id IN NUMBER
12 ,p_execution_mode IN VARCHAR2 ) is
13
14 l_bg_name varchar2(80);
15 PROGRAM_FAILURE CONSTANT NUMBER := 2 ;
16 PROGRAM_SUCCESS CONSTANT NUMBER := 0 ;
17
18 Procedure print_details is
19 --Procedure to Output AVC deduction element name
20 --and its Pension scheme type
21 Cursor csr_element_and_type is
22 Select petf.element_name,
23 pee.eei_information8 scheme_type
24 From pay_element_classifications pec,
25 pay_element_types_f petf,
26 pay_element_type_extra_info pee
27 Where pec.classification_name ='Pre Tax Deductions'
28 and pec.legislation_code='GB'
29 and petf.classification_id = pec.classification_id
30 and sysdate between
31 petf.effective_start_date and petf.effective_end_date
32 and petf.business_group_id =p_business_group_id
33 and pee.information_type = 'PQP_GB_PENSION_SCHEME_INFO'
34 and pee.eei_information_category= 'PQP_GB_PENSION_SCHEME_INFO'
35 and pee.eei_information4 = 'AVC'
36 and petf.element_type_id = pee.element_type_id
37 and pee.eei_information12 is null;
38
39 Begin --print_details
40
41 fnd_file.put_line(fnd_file.output,
42 'List of Existing Additional Voluntary Contribution'
43 ||' Elements and Their Pension Scheme Types: ');
44 fnd_file.new_line(fnd_file.output,1);
45
46 fnd_file.put_line(fnd_file.output,rpad('-',80,'-')
47 ||' '
48 ||rpad('-',19,'-'));
49
50 fnd_file.put_line(fnd_file.output,rpad('Element Name',80)
51 ||' '
52 ||'Pension Scheme Type');
53
54 fnd_file.put_line(fnd_file.output,rpad('-',80,'-')
55 ||' '
56 ||rpad('-',19,'-'));
57
58 --loop through the cursor
59 --and print element name and its pension scheme type
60 For i in csr_element_and_type loop
61 fnd_file.put_line(fnd_file.output,rpad(i.element_name,85,' ')
62 ||i.scheme_type);
63
64 End loop;
65 End print_details ;
66
67 Procedure update_scheme_type is
68 --update element eit eei_information8 with
69 --pension scheme type picked from lookup.
70 Cursor csr_element_details is
71 select petf.element_type_id,petf.element_name,
72 upper(hr.description) description
73 from hr_lookups hr,
74 pay_element_types_f petf,
75 pay_element_classifications pec
76 where hr.lookup_type='PQP_GB_PENSION_SCHEME_UPDATE'
77 and hr.enabled_flag='Y'
78 and (upper(hr.description) in ('COMP','COSR')
79 or hr.description is null)
80 and petf.element_name = hr.meaning
81 and sysdate between --to restrict rows to 1
82 petf.effective_start_date and petf.effective_end_date
83 and petf.business_group_id=p_business_group_id
84 and petf.classification_id = pec.classification_id
85 and pec.classification_name='Pre Tax Deductions'
86 and pec.legislation_code='GB';
87
88 type element_details_typ is table of csr_element_details%rowtype
89 index by binary_integer;
90 element_details_tab element_details_typ;
91
92 Begin --update_scheme_type
93
94 fnd_file.put_line(fnd_file.output,
95 'List of Elements Updated with Pension Scheme Types: ');
96 fnd_file.new_line(fnd_file.output,1);
97
98 fnd_file.put_line(fnd_file.output,rpad('-',80,'-')
99 ||' '
100 ||rpad('-',19,'-'));
101
102 fnd_file.put_line(fnd_file.output,rpad('Element Name',80)
103 ||' '
104 ||'Pension Scheme Type');
105
106 fnd_file.put_line(fnd_file.output,rpad('-',80,'-')
107 ||' '
108 ||rpad('-',19,'-'));
109
110
111 Open csr_element_details;
112 Fetch csr_element_details Bulk Collect Into element_details_tab;
113 Close csr_element_details;
114
115 If element_details_tab.count>0 Then
116
117 For i in element_details_tab.first..element_details_tab.last loop
118
119 update pay_element_type_extra_info pee
120 set pee.eei_information8 = element_details_tab(i).description
121 where pee.element_type_id = element_details_tab(i).element_type_id
122 and pee.information_type = 'PQP_GB_PENSION_SCHEME_INFO'
123 and pee.eei_information_category= 'PQP_GB_PENSION_SCHEME_INFO'
124 and pee.eei_information4 = 'AVC'
125 and pee.eei_information12 is null;
126
127 If(sql%rowcount>0) then
128 fnd_file.put_line(fnd_file.output,
129 rpad(element_details_tab(i).element_name,85,' ')
130 ||element_details_tab(i).description);
131 End If;
132 End Loop;
133 Else
134 fnd_file.put_line(fnd_file.log,
135 'Element_details_tab.count : '||Element_details_tab.count
136 );
137 End If;
138 End Update_scheme_type;
139
140
141 BEGIN --process_scheme_type
142
143 --write parameters to log file
144 fnd_file.put_line(fnd_file.log,
145 'Business Group id : '||p_business_group_id);
146 fnd_file.put_line(fnd_file.log,'Execution Mode : '||p_execution_mode);
147
148
149 select name
150 into l_bg_name
151 from per_business_groups_perf
152 where business_group_id = p_business_group_id;
153
154
155 fnd_file.put_line(fnd_file.output,'Business Group Name : '||l_bg_name);
156 fnd_file.new_line(fnd_file.output,2);
157
158 if p_execution_mode ='PRINT'
159 then
160 print_details;
161 elsif p_execution_mode ='UPDATE'
162 then
163 update_scheme_type;
164 end if;
165
166
167 retcode:=PROGRAM_SUCCESS;
168
169 return;
170 Exception
171 When others then
172 rollback;
173 errbuf := NULL;
174 retcode := PROGRAM_FAILURE ;
175 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
176 End process_scheme_type;
177 END PQP_GB_PENSION_SCHEME_UPDATE;
178
[Home] [Help]