[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 2006/01/18 06:16 kthampan noship $ */
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 is
59 l_id number;
60 l_asg_count number;
61 l_remainder number;
62 l_full_chunk number;
63 l_temp number;
64 function get_assignment_count return number
65 is
66 l_select varchar2(1000);
67 l_from varchar2(1000);
68 l_where varchar2(15000);
69 l_group varchar2(1000);
70 l_sql varchar2(20000);
71 l_sql_cursor integer;
72 l_rows integer;
73 l_asg_count number;
74 l_ret number;
75 begin
76 l_select := 'select count(1)
77 from (select /*+ ORDERED use_nl(paa,paf,emp,pai_payroll)
78 use_index(pai_person,pay_action_information_n2)
79 use_index(pai,pay_action_information_n2) */
80 paf.person_id, max(paa.assignment_action_id) as asg_id ';
81 l_from := ' from pay_assignment_actions paa,
82 per_all_assignments_f paf,
83 pay_action_information emp,
84 pay_action_information pai_payroll ';
85 l_where := 'where paa.payroll_action_id = ' || p_payroll_action_id || '
86 and paa.action_status = ''C''
87 and paa.assignment_id = paf.assignment_id
88 and emp.action_information_category = ''EMPLOYEE DETAILS''
89 and emp.action_context_id = paa.assignment_action_id
90 and emp.action_context_type = ''AAP''
91 and pai_payroll.action_information_category = ''GB EMPLOYEE DETAILS''
92 and pai_payroll.action_context_id = paa.assignment_action_id
93 and pai_payroll.action_context_type = ''AAP'' ';
94
95 if p_assignment_action_id is not null then
96 l_where := l_where || 'and paa.assignment_action_id = ' || p_assignment_action_id ;
97 end if;
98
99 if p_person_type is not null then
100 l_from := l_from || ', per_all_people_f pap
101 , per_person_types ppt ';
102 l_where := l_where || ' and pap.person_id = paf.person_id
103 and pap.person_type_id = ppt.person_type_id
104 and ppt.system_person_type = ''EX_EMP'' ';
105 end if;
106 if p_organization_id is not null then
107 l_where := l_where || ' and emp.action_information2 = ' || p_organization_id ;
108 end if;
109 if p_location_code is not null then
110 l_where := l_where || ' and nvl(emp.action_information30,''0'')= ''' || p_location_code || ''' ' ;
111 end if;
112 if p_org_hierarchy is not null then
113 l_where := l_where || ' and emp.action_information2 in(select organization_id_child
114 from per_org_structure_elements
115 where business_group_id = ' || p_org_hierarchy ||
116 ' union
117 select ' || p_org_hierarchy || ' from dual)';
118 end if;
119 if p_assignment_set_id is not null then
120 l_from := l_from || ',hr_assignment_sets has
121 ,hr_assignment_set_amendments hasa ';
122 l_where := l_where ||
123 ' and has.assignment_set_id = ' || p_assignment_set_id ||
124 ' and has.assignment_set_id = hasa.assignment_set_id(+)
125 and (( has.payroll_id is null
126 and hasa.include_or_exclude = ''I''
127 and hasa.assignment_id = paa.assignment_id
128 )
129 OR
130 ( has.payroll_id is not null
131 and has.payroll_id = pai_payroll.ACTION_INFORMATION5
132 and nvl(hasa.include_or_exclude, ''I'') = ''I''
133 and nvl(hasa.assignment_id, paa.assignment_id) = paa.assignment_id
134 )) ';
135 end if;
136 l_group := ' group by paf.person_id, pai_payroll.action_information13 )';
137 hr_utility.trace(l_select);
138 hr_utility.trace(l_from);
139 hr_utility.trace(l_where);
140 hr_utility.trace(l_group);
141 l_sql := l_select || l_from || l_where || l_group;
142
143 l_sql_cursor := dbms_sql.open_cursor;
144 dbms_sql.parse(l_sql_cursor, l_sql, dbms_sql.v7);
145 dbms_sql.define_column (l_sql_cursor, 1, l_asg_count);
146 l_rows := dbms_sql.execute_and_fetch (l_sql_cursor, false);
147 dbms_sql.column_value (l_sql_cursor, 1, l_asg_count);
148 dbms_sql.close_cursor(l_sql_cursor);
149
150 if (l_rows = 1) then
151 l_ret := l_asg_count;
152 else
153 l_ret := 0;
154 end if;
155
156 return l_ret;
157 end;
158
159 function get_mod(p_total number, p_chunk_size number) return number
160 is
161 l_ret number;
162 begin
163 select mod(p_total,p_chunk_size)
164 into l_ret
165 from dual;
166 return l_ret;
167 end;
168
169 function get_div(p_total number, p_chunk_size number) return number
170 is
171 l_val number;
172 begin
173 select floor(p_total / p_chunk_size)
174 into l_val
175 from dual;
176 return l_val;
177 end;
178 begin
179 l_asg_count := get_assignment_count;
180 hr_utility.trace(l_asg_count);
181 hr_utility.trace(p_chunk_size);
182 if l_asg_count > 0 then
183 if l_asg_count <= p_chunk_size then
184 hr_utility.trace('All assignments in 1 chunk');
185 l_id := fnd_request.submit_request(application => 'PER',
186 program => 'PER_P11D_REP',
187 argument1 => p_print_address_page,
188 argument2 => p_print_p11d,
189 argument3 => p_print_p11d_summary,
190 argument4 => p_print_ws,
191 argument5 => p_payroll_action_id,
192 argument6 => p_organization_id,
193 argument7 => p_org_hierarchy,
194 argument8 => p_assignment_set_id,
195 argument9 => p_location_code,
196 argument10 => p_assignment_action_id,
197 argument11 => p_business_group_id,
198 argument12 => p_sort_order1,
199 argument13 => p_sort_order2,
200 argument14 => p_profile_out_folder,
201 argument15 => p_rec_per_file,
202 argument16 => p_chunk_size,
203 argument17 => 1,
204 argument18 => p_person_type);
205 write_header;
206 write_body(p_file_no => 1,
207 p_request_id => l_id,
208 p_file_name => 'o' || l_id || '.out',
209 p_size => l_asg_count);
210 write_footer(p_total => l_asg_count);
211 else
212 -- assignment count > chunk_size
213 l_remainder := get_mod(l_asg_count,p_chunk_size);
214 l_full_chunk := get_div(l_asg_count,p_chunk_size);
215 hr_utility.trace('Remainder ' || l_remainder);
216 hr_utility.trace('Chunk ' || l_full_chunk);
217 if (l_remainder > 0) then
218 l_full_chunk := l_full_chunk + 1;
219 end if;
220 write_header;
221 l_temp := 0;
222 for x in 1..l_full_chunk loop
223 hr_utility.trace('Processing chunk : ' || x);
224 l_id := fnd_request.submit_request(application => 'PER',
225 program => 'PER_P11D_REP',
226 argument1 => p_print_address_page,
227 argument2 => p_print_p11d,
228 argument3 => p_print_p11d_summary,
229 argument4 => p_print_ws,
230 argument5 => p_payroll_action_id,
231 argument6 => p_organization_id,
232 argument7 => p_org_hierarchy,
233 argument8 => p_assignment_set_id,
234 argument9 => p_location_code,
235 argument10 => p_assignment_action_id,
236 argument11 => p_business_group_id,
237 argument12 => p_sort_order1,
238 argument13 => p_sort_order2,
239 argument14 => p_profile_out_folder,
240 argument15 => p_rec_per_file,
241 argument16 => p_chunk_size,
242 argument17 => x,
243 argument18 => p_person_type);
244 if (x * p_chunk_size) > l_asg_count then
245 l_temp := (x * p_chunk_size) - l_asg_count;
246 else
247 l_temp := p_chunk_size;
248 end if;
249 write_body(p_file_no => x,
250 p_request_id => l_id,
251 p_file_name => 'o' || l_id || '.out',
252 p_size => l_temp);
253 end loop;
254 write_footer(p_total => l_asg_count);
255 end if;
256 end if;
257 end;
258
259 END PAY_GB_P11D_PDF_CONTROL;