[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_P11D_PDF_CONTROL
Source
1 PACKAGE BODY PAY_GB_P11D_PDF_CONTROL AS
2 /* $Header: pygbp11dr.pkb 120.4.12010000.6 2009/10/07 07:45:51 namgoyal ship $ */
3
4 PROCEDURE write_header
5 IS
6 BEGIN
7 fnd_file.put_line(fnd_file.output,null);
8 fnd_file.put_line(fnd_file.output,'P11D PDF Output Report');
9 fnd_file.put_line(fnd_file.output,rpad('File No',9) ||
10 rpad('Request ID',21) ||
11 rpad('File Name',21) ||
12 rpad('P11D Per File',16));
13 fnd_file.put_line(fnd_file.output,rpad('-',8,'-') || ' ' ||
14 rpad('-',20,'-') || ' ' ||
15 rpad('-',20,'-') || ' ' ||
16 rpad('-',15,'-'));
17 END;
18
19 PROCEDURE write_body(p_file_no number,
20 p_request_id number,
21 p_file_name varchar2,
22 p_size number)
23 IS
24 BEGIN
25 fnd_file.put_line(fnd_file.output,rpad(p_file_no,9) ||
26 rpad(p_request_id,21) ||
27 rpad(p_file_name,21) ||
28 rpad(p_size,16));
29 END write_body;
30
31 PROCEDURE write_footer(p_total number)
32 IS
33 BEGIN
34 fnd_file.put_line(fnd_file.output,' ');
35 fnd_file.put_line(fnd_file.output,rpad('Total P11D PDF Produced',51) ||
36 rpad(p_total,16));
37 END;
38
39 PROCEDURE print_pdf(errbuf out NOCOPY VARCHAR2,
40 retcode out NOCOPY NUMBER,
41 p_print_address_page in varchar2 default null,
42 p_print_p11d in varchar2,
43 p_print_p11d_summary in varchar2,
44 p_print_ws in varchar2,
45 p_payroll_action_id in varchar2,
46 p_organization_id in varchar2 default null,
47 p_org_hierarchy in varchar2 default null,
48 p_assignment_set_id in varchar2 default null,
49 p_location_code in varchar2 default null,
50 p_assignment_action_id in varchar2 default null,
51 p_business_group_id in varchar2,
52 p_sort_order1 in varchar2 default null,
53 p_sort_order2 in varchar2 default null,
54 p_profile_out_folder in varchar2 default null,
55 p_rec_per_file in varchar2,
56 p_chunk_size in number,
57 p_person_type in varchar2 default null,
58 p_print_style in varchar2 default null, --bug 8241399
59 -- p_print style parameter added to suppress additional blank page
60 p_priv_mark in varchar2 default null)--bug 8942337
61 is
62 l_id number;
63 l_asg_count number;
64 l_remainder number;
65 l_full_chunk number;
66 l_temp number;
67 function get_assignment_count return number
68 is
69 l_select varchar2(1000);
70 l_from varchar2(1000);
71 l_where varchar2(15000);
72 l_group varchar2(1000);
73 l_sql varchar2(20000);
74 l_sql_cursor integer;
75 l_rows integer;
76 l_asg_count number;
77 l_ret number;
78 begin
79 l_select := 'select count(1)
80 from (select /*+ ORDERED use_nl(paa,paf,emp,pai_payroll)
81 use_index(pai_person,pay_action_information_n2)
82 use_index(pai,pay_action_information_n2) */
83 paf.person_id, max(paa.assignment_action_id) as asg_id ';
84 l_from := ' from pay_assignment_actions paa,
85 per_all_assignments_f paf,
86 pay_action_information emp,
87 pay_action_information pai_payroll ';
88 l_where := 'where paa.payroll_action_id = ' || p_payroll_action_id || '
89 and paa.action_status = ''C''
90 and paa.assignment_id = paf.assignment_id
91 and emp.action_information_category = ''EMPLOYEE DETAILS''
92 and emp.action_context_id = paa.assignment_action_id
93 and emp.action_context_type = ''AAP''
94 and pai_payroll.action_information_category = ''GB EMPLOYEE DETAILS''
95 and pai_payroll.action_context_id = paa.assignment_action_id
96 and pai_payroll.action_context_type = ''AAP'' ';
97
98 if p_assignment_action_id is not null then
99 l_where := l_where || 'and paa.assignment_action_id = ' || p_assignment_action_id ;
100 end if;
101
102 if p_person_type is not null then
103 l_from := l_from || ', per_all_people_f pap
104 , per_person_types ppt ';
105 l_where := l_where || ' and pap.person_id = paf.person_id
106 and pap.person_type_id = ppt.person_type_id
107 and (ppt.system_person_type = ''EX_EMP''
108 or ppt.system_person_type = ''EX_EMP_APL'') '; -- Added to fix the bug 8727098
109 end if;
110 if p_organization_id is not null then
111 l_where := l_where || ' and emp.action_information2 = ' || p_organization_id ;
112 end if;
113 if p_location_code is not null then
114 l_where := l_where || ' and nvl(emp.action_information30,''0'')= ''' || p_location_code || ''' ' ;
115 end if;
116 if p_org_hierarchy is not null then
117 l_where := l_where || ' and emp.action_information2 in(select organization_id_child
118 from per_org_structure_elements
119 where business_group_id = ' || p_org_hierarchy ||
120 ' union
121 select ' || p_org_hierarchy || ' from dual)';
122 end if;
123 if p_assignment_set_id is not null then
124 l_from := l_from || ',hr_assignment_sets has
125 ,hr_assignment_set_amendments hasa ';
126 l_where := l_where ||
127 ' and has.assignment_set_id = ' || p_assignment_set_id ||
128 ' and has.assignment_set_id = hasa.assignment_set_id(+)
129 and (( has.payroll_id is null
130 and hasa.include_or_exclude = ''I''
131 and hasa.assignment_id = paa.assignment_id
132 )
133 OR
134 ( has.payroll_id is not null
135 and has.payroll_id = pai_payroll.ACTION_INFORMATION5
136 and nvl(hasa.include_or_exclude, ''I'') = ''I''
137 and nvl(hasa.assignment_id, paa.assignment_id) = paa.assignment_id
138 )) ';
139 end if;
140 l_group := ' group by paf.person_id, pai_payroll.action_information13 )';
141 hr_utility.trace(l_select);
142 hr_utility.trace(l_from);
143 hr_utility.trace(l_where);
144 hr_utility.trace(l_group);
145 l_sql := l_select || l_from || l_where || l_group;
146
147 l_sql_cursor := dbms_sql.open_cursor;
148 dbms_sql.parse(l_sql_cursor, l_sql, dbms_sql.v7);
149 dbms_sql.define_column (l_sql_cursor, 1, l_asg_count);
150 l_rows := dbms_sql.execute_and_fetch (l_sql_cursor, false);
151 dbms_sql.column_value (l_sql_cursor, 1, l_asg_count);
152 dbms_sql.close_cursor(l_sql_cursor);
153
154 if (l_rows = 1) then
155 l_ret := l_asg_count;
156 else
157 l_ret := 0;
158 end if;
159
160 return l_ret;
161 end;
162
163 function get_mod(p_total number, p_chunk_size number) return number
164 is
165 l_ret number;
166 begin
167 select mod(p_total,p_chunk_size)
168 into l_ret
169 from dual;
170 return l_ret;
171 end;
172
173 function get_div(p_total number, p_chunk_size number) return number
174 is
175 l_val number;
176 begin
177 select floor(p_total / p_chunk_size)
178 into l_val
179 from dual;
180 return l_val;
181 end;
182 begin
183 l_asg_count := get_assignment_count;
184 hr_utility.trace(l_asg_count);
185 hr_utility.trace(p_chunk_size);
186 if l_asg_count > 0 then
187 if l_asg_count <= p_chunk_size then
188 hr_utility.trace('All assignments in 1 chunk');
189 l_id := fnd_request.submit_request(application => 'PER',
190 program => 'PER_P11D_REP',
191 argument1 => p_print_address_page,
192 argument2 => p_print_p11d,
193 argument3 => p_print_p11d_summary,
194 argument4 => p_print_ws,
195 argument5 => p_payroll_action_id,
196 argument6 => p_organization_id,
197 argument7 => p_org_hierarchy,
198 argument8 => p_assignment_set_id,
199 argument9 => p_location_code,
200 argument10 => p_assignment_action_id,
201 argument11 => p_business_group_id,
202 argument12 => p_sort_order1,
203 argument13 => p_sort_order2,
204 argument14 => p_profile_out_folder,
205 argument15 => p_rec_per_file,
206 argument16 => p_chunk_size,
207 argument17 => 1,
208 argument18 => p_person_type,
209 argument19 =>p_print_style,--bug 8241399
210 -- p_print style parameter added to suppress additional blank page
211 argument20 =>p_priv_mark);--bug 8942337
212
213 write_header;
214 write_body(p_file_no => 1,
215 p_request_id => l_id,
216 p_file_name => 'o' || l_id || '.out',
217 p_size => l_asg_count);
218 write_footer(p_total => l_asg_count);
219 else
220 -- assignment count > chunk_size
221 l_remainder := get_mod(l_asg_count,p_chunk_size);
222 l_full_chunk := get_div(l_asg_count,p_chunk_size);
223 hr_utility.trace('Remainder ' || l_remainder);
224 hr_utility.trace('Chunk ' || l_full_chunk);
225 if (l_remainder > 0) then
226 l_full_chunk := l_full_chunk + 1;
227 end if;
228 write_header;
229 l_temp := 0;
230 for x in 1..l_full_chunk loop
231 hr_utility.trace('Processing chunk : ' || x);
232 l_id := fnd_request.submit_request(application => 'PER',
233 program => 'PER_P11D_REP',
234 argument1 => p_print_address_page,
235 argument2 => p_print_p11d,
236 argument3 => p_print_p11d_summary,
237 argument4 => p_print_ws,
238 argument5 => p_payroll_action_id,
239 argument6 => p_organization_id,
240 argument7 => p_org_hierarchy,
241 argument8 => p_assignment_set_id,
242 argument9 => p_location_code,
243 argument10 => p_assignment_action_id,
244 argument11 => p_business_group_id,
245 argument12 => p_sort_order1,
246 argument13 => p_sort_order2,
247 argument14 => p_profile_out_folder,
248 argument15 => p_rec_per_file,
249 argument16 => p_chunk_size,
250 argument17 => x,
251 argument18 => p_person_type,
252 argument19 =>p_print_style,--bug 8241399
253 -- p_print style parameter added to suppress additional blank page
254 argument20 =>p_priv_mark);--bug 8942337
255 if (x * p_chunk_size) > l_asg_count then
256 --l_temp := (x * p_chunk_size) - l_asg_count;
257 l_temp := l_asg_count - ((x-1) * p_chunk_size) ; -- Modified for the bug #8781376
258 else
259 l_temp := p_chunk_size;
260 end if;
261 write_body(p_file_no => x,
262 p_request_id => l_id,
263 p_file_name => 'o' || l_id || '.out',
264 p_size => l_temp);
265 end loop;
266 write_footer(p_total => l_asg_count);
267 end if;
268 end if;
269 end;
270
271 END PAY_GB_P11D_PDF_CONTROL;