1 package body pay_hk_ir56_archive AS
2 -- $Header: pyhk56ar.pkb 120.3.12010000.6 2009/07/28 22:58:41 jalin ship $
3
4 -- Copyright (C) 1999 Oracle Corporation
5 -- All Rights Reserved
6 --
7 -- Script to create pay_hk_ir56_archive package body
8 -- for HK IR56B Archive
9 --
10 -- Change List
11 -- ===========
12 --
13 -- Date Author Reference Description
14 -- -----------+----------------+---------+------------------------------------------
15 -- 05 Jul 2001 A Tripathi Initial version
16 -- 18 Jul 2001 A Tripathi Added procedure to archive data for
17 -- employee manually excluded not paid in HKD
18 -- 10 Aug 2001 A Tripathi Modified the process_assignment cursor as per
19 -- the code review comment
20 -- 27 Aug 2001 A Tripathi 1955980 Removed the checks for employee paid in currency
21 -- other than HKD
22 -- 28 Aug 2001 A Tripathi 1961965 Changed archive_employer_details cursor
23 -- 04 Sep 2001 A Punekar Changed submit_report to submit controll listing
24 -- report for magtape
25 -- 18 Sep 2001 A Tripathi 1999637 Changed archive_balance_details procedure,
26 -- added cursor to get maximum assignment_action_id
27 -- 19 Jan 2002 A Tripath 2189121 Modified Validate_Employee to handle date track
28 -- changes to manual exclusion
29 -- 23 Jan 2002 A Tripath 2189137 Changes done to make messages more meaningful
30 -- Added g_employee_number
31 -- 25 Jan 2002 A Tripath 2189121 Corrected the date track change.
32 -- 10 Apr 2002 J Lin 2302857 1. Corrected action_type in the cursor 56_balances
33 -- 2. return greatest of quarter_start_date and
34 -- basic year start date in cursor qrchive_quarter
35 -- _details
36 -- 29 Feb 2002 A Punekar 2263589 Modified for performance fix
37 -- 17 Sep 2002 vgsriniv 2558852 Increased the item value size for
38 -- storing employee details and
39 -- quarters info
40 -- 17 Sep 2002 vgsriniv 2558852 Increased the item value size
41 -- further to 150 for storing
42 -- other_name(first_name+middle_names)
43 -- of employee
44 -- 02 Dec 2002 srrajago 2689229 Included the nocopy option for 'OUT' parameter of the
45 -- procedure 'range_code'
46 -- 16 Dec 2002 srrajago 2701921 Modified cursor ir56_employee_info to fetch the
47 -- concatenated values of address_line1,address_line2 and
48 -- address_line3 in 'address_lines' which is stored in
49 -- tab_empl(13).item_value ('X_HK_RESIDENTIAL_ADDRESS_1')
50 -- for address_type 'HK_R' and in tab_empl(15).item_name
51 -- ('X_HK_CORRESPONDENCE_ADDRESS_1') for address_type 'HK_C'.
52 -- 10 Dec 2002 srrajago 2740270 Modified the cursor process_assignments.Included a cursor
53 -- get_params.Assignment ids fetch is done through a
54 -- separate cursor check_run and stored in a PL/SQL table.
55 -- 16 Dec 2002 srrajago 2740270 Cursor check_run modified according to coding standards.
56 -- Exception section included in the assignment_action_code
57 -- section.
58 -- 22 Jan 2003 puchil 2762276 Modified the cursor ir56_employee_info to select area_code_res
59 -- and modified tab_empl(8).item_value ('X_HK_RESIDENTIAL_ADDRESS_AREA_CODE')
60 -- to archive area_code_res instead of town_or_city if address_type is 'HK_R'
61 -- 24 Jan 2003 srrajago 2760137 Cursor quarters_info modified to include per_all_assignments_f and
62 -- per_periods_of_service tables and related joins. quarters_period_start
63 -- and quarters_period_end have also been modified.
64 -- 27 Jan 2003 srrajago 2760137 Included nvl check for actual_termination_date in the field
65 -- quarters_period_end of the cursor quarters_info.
66 -- 10 Feb 2003 puchil 2778848 Changed the effective date check in cursor process_assignments
67 -- so as to eliminate terminated employees. Also removed the
68 -- check for IR56F, G in cursor as terminated employees are eliminated.
69 -- 19 Feb 2003 apunekar 2810178 Removed no. of copies passed to fnd_request.set_print_options
70 -- 20 Feb 2003 puchil 2805822 Cursor quarters_info modified to return quarters_period_start and
71 -- and quarters_period_end within a particular financial year.
72 -- 20 Feb 2003 puchil 2805822 Changed the cursor quarters_info to have correct date
73 -- i.e., from 31-01-4712 to 31-12-4712
74 -- 25 Feb 2003 apunekar 2810178 Reverted fix
75 -- 25 Feb 2003 puchil 2805822 Changed the cursor quarters_info to have date effective
76 -- check so as to select the correct Quarters information.
77 -- Also closed the Get_ManualExclusion cursor in Validate_employee
78 -- procedure.
79 -- 26 Feb 2003 puchil 2778848 Changed cursor ir56_Employee_info so as to eliminate the
80 -- check for address_type
81 -- 28 Feb 2003 srrajago 2824718 In the cursor quarters_info, included a join
82 -- paa.period_of_service_id = pps.period_of_service_id
83 -- 11 Mar 2003 srrajago 2829320 In the cursor ir56_Employee_info, included substr function for
84 -- address_lines column so that 240 characters are only fetched.
85 -- In the procedure Archive_Employee_details -> record type archive_rec,
86 -- the item_name and item_value declarations modified.
87 -- 12 Mar 2003 srrajago 2843765 In the call to pay_hk_ir56.get_emoluments, the parameter value passed
88 -- for balance name was 'MAGTAPE_ORSO' and 'MAGTAPE_MPF'. These have been
89 -- modified to 'HK_MAGTAPE_ORSO' and 'HK_MAGTAPE_MPF' respectively.
90 -- 27 Mar 2003 srrajago 2853776 Included a join in the where clause of the cursor quarters_info to
91 -- pick up the correct quarters balance data when reversal is run.
92 -- 15 Apr 2003 srrajago 2890935 Removed the codes that are involved in archiving
93 -- 'X_HK_MAGTAPE_MPF_ASG_LE_YTD' and 'X_HK_MAGTAPE_ORSO_ASG_LE_YTD'.
94 -- 06 May 2003 puchil 2942797 Changed cursor ir56_Employee_info, removed column chineese_full_name,
95 -- and all references to it.
96 -- 06 May 2003 srrajago 2853776 Modified the sub-query in the cursor quarters_info.
97 -- 07 May 2003 shoskatt 2945151 Changed the cursor ir56_employee_info to fetch chinese_full_name. This was
98 -- removed w.r.t earlier fix (Bug# 2942797)
99 -- 08 May 2003 srrajago 2853776 Removed the financial year condition check from the main part of the
100 -- cursor quarters_info and included the same in the sub-query.
101 -- 30 May 2003 kaverma 2920731 Replaced tables per_all_assignments_f and per_all_people_f by secured views
102 -- per_assignments_f and per_people_f respectively form the queries
103 -- 05 Jun 2003 puchil 2949952 Archived the country value in X_HK_RES_COUNTRY
104 -- 21 Jul 2003 srrajago 3055512 Employees terminated on 31-MAR-YYYY should not be included in the archive
105 -- run for the year YYYY. Hence modified the cursor process_assignments by
106 -- including the join with actual_termination_date <> 31-Mar-YYYY.
107 -- 28 Aug 2003 srrajago 3059915 # In the cursor 'process_assignments', few joins with effective_date,
108 -- reporting_year and business_group_id included.Few joins with
109 -- pay_core_utils removed.
110 -- # In the cursors 'ir56_Spouse_info' and 'ir56_Employee_info',view
111 -- fnd_territories_vl replaced with fnd_territories_tl table and a
112 -- join with language also included.
113 -- # In the procedure 'Archive_Excep_Error_dtls', cursor 'ir56_employer_info'
114 -- modified.Table per_assignments_f and its related joins removed.
115 -- Cursor 'ir56_Employee_info' also modified.A join with period_of_service_id
116 -- added.
117 -- 15 Dec 2003 srrajago 3193217 Modified the procedure 'archive_balance_details'. Call to the function
118 -- 'get_emoluments' and the return values substitution have been changed as package
119 -- 'pay_hk_ir56' modified. Cursor 'ir56_balances' removed.
120 -- 25 May 2004 avenkatk 3642506 #In Cursor ir56_Employee_info,modified the cursor to fetch the latest Person Details.
121 -- #In Cursor ir56_Spouse_info,modified the cursor to fetch the latest Spouse Info Details.
122 --
123 -- 09 Dec 2004 jkarouza 3916743 Modified cursor check_run for performance improvement for Bug 3916743.
124 -- 01 Jun 2004 jlin 4396794 Replaced substr with substrb for address_lines
125 -- 27 Dec 2005 snimmala 4260143 Modified the cursor max_assign_action_id for performance improvement
126 -- 29 Dec 2005 snimmala 4260143 Modified the cursor max_assign_action_id.
127 -- 20 Jun 2008 jalin 7184102 Added ORDER BY clause into cursor quarters_info
128 -- 22 Jul 2008 jalin 7184102 ORDER BY clause should use quarter_start_date instead of b.quarter_start_date
129 -- 28 Aug 2008 tbakashi 7210187 Added the cursor ir56_Report_info for replacement details.
130 -- 18 Dec 2008 dduvvuri 7635388 Added cursor ir56f_report_date to fetch last run date of IR56F report.
131 -- 29 Jul 2009 jalin 8338664 Modified employer_name to extract 60 characters
132
133 --* GLobal variables (populated in archive_code procedure)
134 g_assignment_id pay_assignment_actions.assignment_id%TYPE;
135 g_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
136 g_payroll_Action_id pay_payroll_actions.payroll_action_id%TYPE;
137 g_archive_message ff_archive_items.value%TYPE := NULL;
138 g_error_in_quarter BOOLEAN := FALSE;
139 g_business_group_id hr_organization_units.business_group_id%TYPE; -- for submitting the PAYHKCTL report
140
141 --* Bug 2189137
142 g_employee_number per_all_people_f.employee_number%TYPE;
143
144 TYPE
145 archive_rec IS RECORD (item_name Varchar2(100),
146 item_value Varchar2(100));
147 TYPE
148 archive_tab IS TABLE OF archive_rec INDEX BY BINARY_INTEGER;
149
150 tab_empr archive_tab;
151 --------------------------------------------------------------------
152 --* This procedure returns a sql string to select a range
153 --* of assignments eligible for archival.
154 --------------------------------------------------------------------
155 PROCEDURE range_code
156 (p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE,
157 p_sql OUT nocopy Varchar2) is
158 Begin
159 hr_utility.set_location('Start of range_code',1);
160 p_sql := 'SELECT distinct person_id ' ||
161 'FROM per_people_f ppf, ' ||
162 'pay_payroll_actions ppa ' ||
163 'WHERE ppa.payroll_action_id = :payroll_action_id ' ||
164 'AND ppa.business_group_id = ppf.business_group_id ' ||
165 'ORDER BY ppf.person_id';
166 hr_utility.set_location('End of range_code',2);
167 End range_code;
168
169 ------------------------------------------------------------------------
170 -- This is used by legislation groups to set global contexts that are
171 -- required for the lifetime of the archiving process. This is null
172 -- because there are no setup requirements, but a PROCEDURE needs to
173 -- exist in pay_report_format_mappings_f, otherwise the archiver will
174 -- assume that no archival of data is required.
175 ------------------------------------------------------------------------
176 PROCEDURE initialization_code
177 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE) is
178 Begin
179 hr_utility.set_location('pyhkirar: Start of initialization_code',6);
180 g_payroll_action_id := p_payroll_action_id;
181 hr_utility.set_location('pyhkirar: End of initialization_code',7);
182
183 End initialization_code;
184
185 ------------------------------------------------------------------------
186 -- This PROCEDURE is used to restrict the Assignment Action Creation.
187 -- It calls the PROCEDURE that actually inserts the Assignment Actions.
188 -- The CURSOR SELECTs the assignments that have had any payroll
189 -- processing for the Legal Entity within the Reporting Year.
190 ------------------------------------------------------------------------
191 PROCEDURE assignment_action_code
192 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE,
193 p_start_person_id in per_all_people_f.person_id%TYPE,
194 p_end_person_id in per_all_people_f.person_id%TYPE,
195 p_chunk in number)
196 IS
197 v_next_action_id pay_assignment_actions.assignment_action_id%TYPE;
198 v_run_action_id pay_assignment_actions.assignment_action_id%TYPE;
199
200 /* Following variables introduced for Bug No : 2740270 */
201 v_fin_start_date date;
202 v_fin_end_date date;
203 v_business_group_id pay_payroll_actions.business_group_id%TYPE;
204 v_legal_entity_id hr_organization_units.organization_id%TYPE;
205 v_reporting_year NUMBER;
206
207 l_counter number:=1;
208 counter number:=1;
209 t_aid per_all_assignments.assignment_id%type;
210
211 TYPE t_assignment_list IS TABLE OF per_all_assignments_f.assignment_id%type;
212 asglist t_assignment_list;
213
214
215 CURSOR next_action_id is
216 SELECT pay_assignment_actions_s.NEXTVAL
217 FROM dual;
218
219 /* Introduced the following cursor get_params for Bug No : 2740270 */
220
221 CURSOR get_params(c_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
222 IS
223 SELECT to_date('01-04-'|| to_char(to_number(pay_core_utils.get_parameter('REPORTING_YEAR',legislative_parameters))
224 -1),'DD-MM-YYYY') FINANCIAL_YEAR_START
225 ,to_date('31-03-'||pay_core_utils.get_parameter('REPORTING_YEAR',legislative_parameters),'DD-MM-YYYY')
226 FINANCIAL_YEAR_END
227 ,pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) BUSINESS_GROUP_ID
228 ,pay_core_utils.get_parameter('LEGAL_ENTITY_ID',legislative_parameters) LEGAL_ENTITY_ID
229 ,pay_core_utils.get_parameter('REPORTING_YEAR',legislative_parameters) REPORTING_YEAR
230 FROM pay_payroll_actions
231 WHERE payroll_action_id = c_payroll_Action_id;
232
233 /* Bug No : 2740270 - Cursor to get all the assignments in the basis year
234 for whom R,B,I,Q is run */
235 CURSOR check_run
236 ( c_business_group_id pay_payroll_actions.business_group_id%TYPE,
237 c_legal_entity_id hr_organization_units.organization_id%TYPE,
238 c_fin_start_date date,
239 c_fin_end_date date )
240 IS
241 SELECT distinct pac.assignment_id
242 FROM pay_payroll_actions ppa,
243 pay_payrolls_f pay, /* Added for Bug 3916743 - performance fix. */
244 pay_assignment_actions pac
245 WHERE ppa.action_type in ('R','B','I','Q')
246 AND ppa.payroll_action_id = pac.payroll_action_id
247 AND ppa.business_group_id = c_business_group_id
248 AND pac.tax_unit_id = c_legal_entity_id
249 AND ppa.effective_date between c_fin_start_date and c_fin_end_date
250 AND ppa.action_status = 'C'
251 AND pac.action_status = 'C'
252 AND ppa.payroll_id = pay.payroll_id /* This and next line added for Bug 3916743 */
253 AND pay.business_group_id = c_business_group_id
254 ORDER BY pac.assignment_id;
255
256
257 /* Bug No : 2740270 - Modified cursor process_assignments. Included two in parameters c_fin_start_date and
258 c_fin_end_date. Removed per_all_people_f table and related joins modified.Checks with effective_date
259 modified with the new in parameters.Inner query modified to include joins with report type
260 'HK_IR56B_ARCHIVE' and action status with 'C' and action type with 'X'.Selection of assignments ids
261 is now done through a cursor check_run and hence that part of the query has been removed from process
262 assignments */
263
264 /*Bug No : 2778848 - Modified cursor process_assignments, the select statement is changed to
265 eliminate the terminated employee details, by including per_periods_of_service and checking
266 for the existance of assignment as of 31 of march. Since terminated employees are not selected, the
267 check for whether IR56F or IR56G is run becomes invalid hence this check is removed. */
268
269 CURSOR process_assignments
270 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE,
271 c_start_person_id in per_all_people_f.person_id%TYPE,
272 c_end_person_id in per_all_people_f.person_id%TYPE,
273 c_fin_start_date in date,
274 c_fin_end_date in date,
275 c_business_group_id pay_payroll_actions.business_group_id%TYPE,
276 c_legal_entity_id hr_organization_units.organization_id%TYPE,
277 c_reporting_year NUMBER)
278 IS
279 SELECT DISTINCT a.assignment_id assignment_id
280 FROM per_assignments_f a,
281 pay_payroll_actions pa,
282 per_periods_of_service pps
283 WHERE pa.payroll_action_id = c_payroll_action_id
284 AND a.person_id BETWEEN c_start_person_id and c_end_person_id
285 AND a.business_group_id = pa.business_group_id
286 AND TO_DATE('31-03-'||c_reporting_year, 'DD-MM-YYYY') between a.effective_start_date and a.effective_end_date
287 AND TO_DATE('31-03-'||c_reporting_year, 'DD-MM-YYYY') between pps.date_start
288 and NVL(pps.actual_termination_date, TO_DATE('31-12-4712', 'DD-MM-YYYY'))
289 AND NVL(pps.actual_termination_date, TO_DATE('31-12-4712', 'DD-MM-YYYY')) <> TO_DATE('31-03-'||c_reporting_year, 'DD-MM-YYYY') -- Bug: 3055512
290 AND pps.period_of_service_id = a.period_of_service_id
291 AND pps.person_id = a.person_id
292 AND NOT EXISTS -- don't produce if they've had ir56b report produced.
293 (SELECT NULL
294 FROM pay_action_interlocks pai,
295 pay_payroll_actions ppai,
296 pay_payroll_actions ppaa,
297 pay_assignment_actions paa
298 WHERE paa.assignment_id = a.assignment_id
299 AND ppaa.action_type='X'
300 AND ppaa.report_type = 'HK_IR56B_ARCHIVE'
301 AND ppai.action_type='X'
302 AND ppai.action_status='C'
303 AND ppaa.action_status='C'
304 AND ppai.report_type = 'HK_IR56B_REPORT'
305 AND paa.assignment_action_id = pai.locking_action_id
306 AND ppai.payroll_action_id = paa.payroll_action_id
307 AND ppaa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ACTION_ID',
308 ppai.legislative_parameters)
309 /* Start of Bug No : 3059915 */
310 AND ppaa.business_group_id = c_business_group_id
311 AND ppaa.business_group_id = ppai.business_group_id
312 AND to_char(ppaa.effective_date,'YYYY') = c_reporting_year
313 AND ppaa.effective_date = ppai.effective_date
314 /* End of Bug No : 3059915 */
315 AND pay_core_utils.get_parameter('LEGAL_ENTITY_ID',ppaa.legislative_parameters) =
316 c_legal_entity_id
317 )
318 ;
319
320 Begin
321 hr_utility.set_location('Start of assignment_action_code '||
322 p_payroll_action_id || ':' || p_start_person_id || ':' || p_end_person_id,3);
323
324 /* Bug No : 2740270 - Fetching values from the cursor get_params */
325 OPEN get_params(p_payroll_action_id);
326 FETCH get_params
327 INTO v_fin_start_date
328 ,v_fin_end_date
329 ,v_business_group_id
330 ,v_legal_entity_id
331 ,v_reporting_year;
332 CLOSE get_params;
333
334 /* Bug No : 2740270 - Store all the assignment ids for which either of R,B,I.Q is run
335 for a basis year in a PL/SQL table. Once they are stored, next time onwards only
336 table is searched for the assignment ids and the query will not be executed.
337 This avoids the execution of the query for all the assignments */
338
339 IF t_assignmentid_store.count = 0 THEN
340 OPEN check_run(v_business_group_id,v_legal_entity_id,v_fin_start_date,v_fin_end_date);
341 LOOP
342 FETCH check_run INTO t_aid;
343 EXIT WHEN check_run%NOTFOUND;
344 t_assignmentid_store(t_aid) := t_aid;
345 END LOOP;
346 CLOSE check_run;
347 END IF;
348
349 /* Bug No : 2740270 - Used bulk collect to store all the assignment ids fetched from
350 process_assignments cursor to improve performance */
351
352 OPEN process_assignments(p_payroll_action_id,p_start_person_id,p_end_person_id,v_fin_start_date,v_fin_end_date,
353 v_business_group_id,v_legal_entity_id,v_reporting_year);
354 FETCH process_assignments bulk collect INTO asglist;
355 CLOSE process_assignments;
356
357 FOR i IN 1..asglist.count
358 LOOP
359 IF asglist.exists(i) THEN
360 IF t_assignmentid_store.exists(asglist(i)) THEN
361 OPEN next_action_id;
362 FETCH next_action_id INTO v_next_action_id;
363 CLOSE next_action_id;
364
365 hr_utility.set_location('Before calling hr_nonrun_asact.insact',4);
366 hr_nonrun_asact.insact(v_next_action_id,
367 asglist(i),
368 p_payroll_action_id,
369 p_chunk,
370 null);
371 hr_utility.set_location('After calling hr_nonrun_asact.insact',4);
372 END IF;
373 END IF;
374 END LOOP;
375
376 EXCEPTION
377 WHEN OTHERS THEN
378 IF next_action_id%ISOPEN THEN
379 CLOSE next_action_id;
380 END IF;
381 hr_utility.set_location('Exception in assignment_action_code ',20);
382 RAISE;
383 End assignment_action_code;
384
385
386 ---------------------------------
387 -- [ Archive Employee details ]
388 ---------------------------------
389
390 PROCEDURE Archive_Employee_details(
391 p_business_group_id IN hr_organization_units.business_group_id%TYPE,
392 p_legal_entity_id IN hr_organization_units.organization_id%TYPE,
393 p_reporting_year IN Varchar2)
394 IS
395 v_loop_cnt number := 0;
396
397 -----------------------------------------
398 --* get employee records to archive
399 -----------------------------------------
400 /* Bug 2778848 : Removed check for address type as cursor was failing when the address type was
401 other than HK_R or HK_C. */
402
403 /* Bug No : 2829320 - Included substr function for address_lines column so that 240 characters are only fetched */
404 /*Bug 2942797 - Removed column chineese_full_name*/
405 CURSOR ir56_Employee_info
406 (c_assignment_id pay_assignment_actions.assignment_id%TYPE,
407 c_reporting_year Varchar2 )
408 IS
409 SELECT DISTINCT
410 papf.national_identifier hk_id_card_no,
411 DECODE(papf.marital_status, 'M',
412 DECODE(sex, 'F', NVL(previous_last_name, last_name), last_name)
413 ,last_name) last_name,
414 TRIM(papf.first_name||' '||papf.middle_names) other_name,
415 papf.per_information5 chinese_full_name, /* Bug 2945151 */
416 SUBSTR(papf.sex,1,1) sex,
417 DECODE(papf.marital_status,'M',2,1) marital_status,
418 papf.per_information1
419 ||DECODE(papf.per_information2,NULL, NULL, ' '
420 || ftv.territory_short_name) passport_info,
421 papf.employee_number employee_number,
422 pad.address_type address_type, /* Start of Bug No : 2701921, 4396794 */
423 substrb(decode(pad.address_line1,null,'', pad.address_line1 ||
424 decode(pad.address_line2,null,decode(pad.address_line3,null,'',', '),', ')) ||
425 decode(pad.address_line2,null,'', pad.address_line2 || decode(pad.address_line3,null,'',', ')) ||
426 pad.address_line3,1,240) address_lines, /* End of Bug : 2701921,4396794 */
427 pad.town_or_city town_or_city,
428 pad.country country,
429 DECODE(pad.style, 'HK', ','||hrl.meaning, NULL) area_code,
430 DECODE(pad.style, 'HK', hrl.meaning, NULL) area_code_res,/*Added for bug 2762276 to store residential address area code*/
431 paei.aei_information1 capacity_employed,
432 hsck.segment2 principal_emp_name,
433 TO_CHAR(GREATEST(TO_DATE('01/04/'||
434 TO_CHAR(TO_NUMBER(c_reporting_year)-1),'DD/MM/YYYY')
435 ,pps.date_start), 'YYYYMMDD') employment_start_date,
436 c_reporting_year||'0331' employment_end_date,
437 papf.per_information9 employee_tfn,
438 hsck.segment5 remarks,
439 pcr.primary_contact_flag primary_contact_flag,
440 NVL(pcr.contact_person_id,0) person_id, -- used in spouse cursor
441 pcr.contact_type contact_type, -- used in spouse cursor
442 pcr.date_start date_start
443 FROM per_people_f papf,
444 per_assignments_f paaf,
445 fnd_territories_tl ftv, /* Bug No : 3059915 */
446 per_contact_relationships pcr,
447 per_addresses pad,
448 per_assignment_extra_info paei,
449 per_periods_of_service pps,
450 hr_soft_coding_keyflex hsck,
451 hr_lookups hrl
452 WHERE paaf.person_id = papf.person_id
453 AND TO_DATE('31-03-'|| c_reporting_year, 'DD-MM-YYYY')
454 BETWEEN paaf.effective_start_date and paaf.effective_end_date
455 AND papf.effective_end_date = NVL(pps.actual_termination_date,TO_DATE('31-12-4712','dd-mm-yyyy')) /* Bug No : 3642506*/
456 AND papf.per_information2 = ftv.territory_code(+)
457 AND ftv.language(+) = userenv('LANG') /* Bug No : 3059915 */
458 AND pps.period_of_service_id = paaf.period_of_service_id /* Bug No : 3059915 */
459 AND papf.person_id = pcr.person_id(+)
460 AND papf.business_group_id = p_business_group_id
461 AND pcr.business_group_id(+) = p_business_group_id -- watch this condition
462 AND NVL(pcr.date_end(+),TO_DATE('31-12-4712','dd-mm-yyyy')) =TO_DATE('31-12-4712','dd-mm-yyyy') /* Bug No : 3642506*/
463 AND paaf.assignment_id = c_assignment_id
464 AND papf.person_id = pad.person_id(+)
465 AND NVL(pad.date_to(+),TO_DATE('31-12-4712','dd-mm-yyyy')) = TO_DATE('31-12-4712','dd-mm-yyyy') /* Bug No : 3642506*/
466 AND pad.region_1 = hrl.lookup_code(+)
467 AND pad.business_group_id(+) = p_business_group_id
468 AND hrl.lookup_type(+)= 'HK_AREA_CODES'
469 AND paei.assignment_id(+) = paaf.assignment_id
470 AND paei.aei_information_category(+) = 'HR_EMPLOYMENT_INFO_HK'
471 AND hsck.soft_coding_keyflex_id(+) = paaf.soft_coding_keyflex_id
472 AND TO_DATE('31-03-'|| c_reporting_year, 'DD-MM-YYYY')
473 BETWEEN nvl(hsck.start_date_active(+),to_date('01-01-1900','dd-mm-yyyy'))
474 AND NVL(hsck.end_date_active(+),TO_DATE('31-12-4712','dd-mm-yyyy'))
475 AND pps.person_id = paaf.person_id
476 AND TO_DATE('31-03-'||c_reporting_year, 'DD-MM-YYYY')
477 BETWEEN pps.date_start
478 AND NVL(pps.actual_termination_date,TO_DATE('31-12-4712','dd-mm-yyyy'))
479 ORDER BY papf.national_identifier ASC,pcr.primary_contact_flag DESC,pcr.date_start DESC;
480 --* Order by clause above insures that contact type with primary contactflag is selected
481 --* first, if more than one spouse record exists without primary contact flag then
482 --* select the spouse with earliest start date. This is used in the subsequent cursor
483 --* for getting the spouse detail for an employee
484
485 employee_rec ir56_Employee_info%ROWTYPE;
486
487 /* 7210187 */
488
489 CURSOR ir56_Report_info
490 (c_assignment_id pay_assignment_actions.assignment_id%TYPE,
491 c_reporting_year Varchar2 )
492 IS
493 SELECT DISTINCT
494 substr(paei.aei_information2,1,10) last_run_date,
495 paei.aei_information5 last_run_type
496
497 from per_assignment_extra_info paei
498
499 where paei.aei_information_category = 'HR_IR56B_REPORTING_INFO_HK'
500 AND to_number(paei.assignment_id) = c_assignment_id
501 AND to_number(paei.aei_information1) = c_reporting_year;
502
503 report_rec ir56_Report_info%ROWTYPE;
504
505 /* 7210187 */
506
507 /* 7635388 - Cursor ir56f_report_date created to fetch the last run date of IR56F report */
508 CURSOR ir56f_report_date
509 (c_assignment_id pay_assignment_actions.assignment_id%TYPE)
510 IS
511 SELECT TO_CHAR(to_date(paei.aei_information2,'yyyy/mm/dd hh24:mi:ss'),'yyyy/mm/dd')
512 FROM per_assignment_extra_info paei,
513 per_assignment_info_types pait
514 WHERE paei.assignment_id = c_assignment_id
515 AND paei.information_type = 'HR_LE_REPORTING_HK'
516 AND paei.information_type = pait.information_type
517 AND pait.active_inactive_flag = 'Y';
518
519
520
521 --* From above employee_info cursor, if contact_type ='S'
522 --* and and pcr.person_id is not null,then open cursor to
523 --* spouse details
524
525 CURSOR ir56_Spouse_info(c_person_id per_all_people_f.person_id%TYPE,
526 c_reporting_year Varchar2 )
527 IS
528 SELECT papf_spouse.last_name
529 || DECODE(papf_spouse.first_name, null, null, ', '
530 || papf_spouse.first_name)
531 || DECODE(papf_spouse.middle_names, null, null, ', '
532 || papf_spouse.middle_names) spouse_name,
533 papf_spouse.national_identifier spouse_hk_id,
534 papf_spouse.per_information1
535 ||DECODE(papf_spouse.per_information2,NULL, NULL, ' '
536 || ftv.territory_short_name) passport_info
537 FROM per_people_f papf_spouse,
538 fnd_territories_tl ftv /* Bug No : 3059915 */
539 WHERE papf_spouse.person_id = c_person_id
540 AND papf_spouse.business_group_id = p_business_group_id
541 AND papf_spouse.per_information2 = ftv.territory_code(+)
542 AND ftv.language(+) = userenv('LANG') /* Bug No : 3059915 */
543 AND papf_spouse.effective_end_date = TO_DATE('31-12-4712','dd-mm-yyyy'); /* Bug No : 3642506 */
544
545 spouse_rec ir56_Spouse_info%ROWTYPE;
546
547 /* Bug 2558852 Increased the item_value size from 100 to 250 to
548 accomodate address details and other_name of the employee */
549
550 /* Bug No : 2829320 - declared item_name and item_value using %type */
551
552 TYPE
553 archive_rec IS RECORD (item_name ff_user_entities.user_entity_name%type,
554 item_value ff_archive_items.value%type);
555 TYPE
556 archive_tab IS TABLE OF archive_rec INDEX BY BINARY_INTEGER;
557 tab_empl archive_tab;
558
559 tab_count Number := 1;
560
561 spouse_found Char(1) ;
562 printed_once Char(1) ;
563
564 p_value Varchar2(240);
565 l_date varchar2(40); /* 7635388 */
566 Begin
567
568 ---------------------------------------
569 --* Initialization section
570 ---------------------------------------
571 spouse_found := 'N';
572 printed_once := 'N';
573
574 tab_empl(1).item_name := 'X_HK_HKID';
575 --*
576 tab_empl(2).item_name := 'X_HK_LAST_NAME';
577 --*
578 tab_empl(3).item_name := 'X_HK_OTHER_NAMES';
579 --*
580 tab_empl(4).item_name := 'X_HK_CHINESE_FULL_NAME';
581 --*
582 tab_empl(5).item_name := 'X_HK_SEX';
583 --*
584 tab_empl(6).item_name := 'X_HK_MARITAL_STATUS';
585 --*
586 tab_empl(7).item_name := 'X_HK_PASSPORT_INFO';
587 --*
588 tab_empl(8).item_name := 'X_HK_RESIDENTIAL_ADDRESS_AREA_CODE';
589 --*
590 tab_empl(9).item_name := 'X_HK_CAPACITY_EMPLOYED';
591 --*
592 tab_empl(10).item_name := 'X_HK_PRINCIPAL_EMPLOYER_NAME';
593 --*
594 tab_empl(11).item_name := 'X_HK_EMPLOYMENT_START_DATE';
595
596 --*
597 tab_empl(12).item_name := 'X_HK_EMPLOYMENT_END_DATE';
598 --*
599 tab_empl(13).item_name := 'X_HK_RESIDENTIAL_ADDRESS_1';
600
601 --*
602 tab_empl(14).item_name := 'X_HK_RESIDENTIAL_ADDRESS_2';
603 --*
604 tab_empl(15).item_name := 'X_HK_CORRESPONDENCE_ADDRESS_1';
605 --*
606 tab_empl(16).item_name := 'X_HK_SPOUSE_NAME';
607 --*
608 tab_empl(17).item_name := 'X_HK_SPOUSE_HKID';
609 --*
610 tab_empl(18).item_name := 'X_HK_SPOUSE_PASSPORT_INFO';
611 --*
612 tab_empl(19).item_name := 'X_HK_EMPLOYEE_TFN';
613 --*
614 tab_empl(20).item_name := 'X_HK_REMARKS';
615 --*
616 tab_empl(21).item_name := 'X_HK_CORRESPONDENCE_ADDRESS_2';
617 --*
618 tab_empl(22).item_name := 'X_HK_RES_COUNTRY'; /* 2949952 */
619 --*
620 tab_empl(23).item_name := 'X_HK_LAST_RUN_DATE'; /* 7210187 */
621 --*
622 tab_empl(24).item_name := 'X_HK_LAST_RUN_TYPE';
623
624
625 ---------------------------------------
626 --* Initialization section over
627 ---------------------------------------
628
629
630
631
632 OPEN ir56_Employee_info(g_assignment_id,p_reporting_year);
633 LOOP
634 FETCH ir56_Employee_info INTO employee_rec;
635 IF ir56_Employee_info%FOUND Then
636
637
638 ---------------------------------------
639 --* Prepare employee data
640 ---------------------------------------
641
642 /*
643 The cursor is created in such a way that for an assignment it could return
644 more than one row. So printed_once flag is used to avoid the archival records
645 from getting duplicated
646 */
647
648
649
650 IF printed_once = 'N' Then
651
652 --* 'X_HK_HKID'
653 tab_empl(1).item_value := employee_rec.hk_id_card_no;
654
655 --* 'X_HK_LAST_NAME'
656 tab_empl(2).item_value := rtrim(employee_rec.last_name);
657
658 --* 'X_HK_OTHER_NAMES'
659 tab_empl(3).item_value := employee_rec.other_name;
660
661 --* 'X_HK_CHINESE_FULL_NAME'
662 tab_empl(4).item_value := employee_rec.chinese_full_name; /*Bug 2942797*/
663 /*Bug 2945151 - Set the Chinese Full Name back. This was removed w.r.t
664 earlier Bug 2942797 */
665
666 --* 'X_HK_SEX'
667 tab_empl(5).item_value := employee_rec.sex;
668
669 --* 'X_HK_MARITAL_STATUS'
670 tab_empl(6).item_value := employee_rec.marital_status;
671
672 --* 'X_HK_PASSPORT_INFO'
673 tab_empl(7).item_value := employee_rec.passport_info;
674
675 --* 'X_HK_CAPACITY_EMPLOYED'
676 tab_empl(9).item_value := employee_rec.capacity_employed;
677
678 --* 'X_HK_PRINCIPAL_EMPLOYER_NAME'
679 tab_empl(10).item_value := employee_rec.principal_emp_name;
680
681 --* 'X_HK_EMPLOYMENT_START_DATE'
682 tab_empl(11).item_value := employee_rec.employment_start_date;
683
684 --* 'X_HK_EMPLOYMENT_END_DATE'
685 tab_empl(12).item_value := employee_rec.employment_end_date;
686
687 --* 'X_HK_EMPLOYEE_TFN'
688 tab_empl(19).item_value := employee_rec.employee_tfn;
689
690 --* 'X_HK_REMARKS'
691 tab_empl(20).item_value := employee_rec.remarks;
692
693 --* Bug 2189137
694 --* 'Employee number
695 g_employee_number := employee_rec.employee_number;
696
697
698 printed_once := 'Y';
699 End if;
700
701 /* 7210187 */
702
703 OPEN ir56_Report_info(g_assignment_id,p_reporting_year);
704
705 FETCH ir56_Report_info INTO report_rec;
706
707 If ir56_Report_info%found then
708
709 tab_empl(23).item_value := report_rec.last_run_date;
710 tab_empl(24).item_value := report_rec.last_run_type;
711 else /* 7635388 - fetch the last run date of IR56F report */
712 OPEN ir56f_report_date(g_assignment_id);
713 FETCH ir56f_report_date INTO l_date;
714 if l_date is not null then
715 tab_empl(23).item_value := l_date;
716 tab_empl(24).item_value := 'REPLACEMENT';
717 end if;
718 CLOSE ir56f_report_date;
719 End If;
720
721 CLOSE ir56_Report_info;
722
723 /* 7210187 */
724
725 /* printed_flag is not used since the cursor may return more than one record,
726 for example address_type can have two values , HK_R and HK_C for a single
727 assignment.In that case the cursor will return 2 rows and both the values
728 need to be stored */
729
730 If employee_rec.address_type='HK_R' Then
731 --* 'X_HK_RESIDENTIAL_ADDRESS_1'
732 tab_empl(13).item_value := employee_rec.address_lines; /* Bug No : 2701921 */
733
734 /* Moved the following two lines from the above if block as residential address
735 area code requires check for address type. further, the item_value is populated
736 by area_code_res instead of town_or_city -- Bug 2762276*/
737 --* 'X_HK_RESIDENTIAL_ADDRESS_AREA_CODE'
738 tab_empl(8).item_value := employee_rec.area_code_res;
739
740 /*Start of fix for Bug 2949952 */
741 --* 'X_HK_RESIDENTIAL_ADDRESS_2'
742 tab_empl(14).item_value := employee_rec.town_or_city;
743
744 --* 'X_HK_RES_COUNTRY'
745 tab_empl(22).item_value := employee_rec.country;
746 /*End of fix for Bug 2949952 */
747
748 End If;
749
750 --* 'X_HK_CORRESPONDENCE_ADDRESS_1'
751 If employee_rec.address_type='HK_C' Then
752 tab_empl(15).item_value := employee_rec.address_lines; /* Bug No : 2701921 */
753 End If;
754
755 --* 'X_HK_CORRESPONDENCE_ADDRESS_2'
756 If employee_rec.address_type='HK_C' Then
757 If employee_rec.country IS NULL Then
758 tab_empl(21).item_value := employee_rec.town_or_city
759 ||employee_rec.area_code;
760 Else
761 tab_empl(21).item_value := employee_rec.town_or_city
762 ||employee_rec.area_code
763 ||','||employee_rec.country;
764 End If;
765 End If;
766
767
768 --* Employee data over
769
770 ---------------------------------------
771 --* Prepare spouse data
772 ---------------------------------------
773 ---* Find the spouse detail only if 1) the person is having a spouse 2) contact type ='S'
774 ---* 3) the employee cursor may loop more than once , don't find the spouse again if found
775 ---* earlier.The order by clause in the employee cursor ensures that if the primary contact
776 ---* type is 'Y' then that record is selected first . If the primary contact is 'Y' ,
777 ---* no need to find other types of spouse ie. of type other than 'Y'.If no contact
778 ---* with type 'Y' exists then get any other contact type details ie. 'N'
779
780 If ( (employee_rec.person_id <> 0) AND (employee_rec.contact_type = 'S')
781 AND (spouse_found = 'N')) Then
782
783 OPEN ir56_Spouse_info(employee_rec.person_id,p_reporting_year);
784 FETCH ir56_Spouse_info INTO spouse_rec;
785 If ir56_Spouse_info%FOUND Then
786 --*'X_HK_SPOUSE_NAME'
787 tab_empl(16).item_value := spouse_rec.spouse_name;
788
789 --*'X_HK_SPOUSE_HKID'
790 tab_empl(17).item_value := spouse_rec.spouse_hk_id;
791
792 --* 'X_HK_SPOUSE_PASSPORT_INFO'
793 tab_empl(18).item_value := spouse_rec.passport_info;
794
795 spouse_found := 'Y';
796 End If;
797 CLOSE ir56_Spouse_info;
798 End if;
799 Else
800 --* No employee details exists
801 Exit;
802 End If;
803 END LOOP;
804 ---------------------------------------
805 --* Archive employee and spouse details
806 ---------------------------------------
807
808 For tab_count in 1..tab_empl.COUNT
809 LOOP
810 If tab_empl.EXISTS(tab_count) Then
811 archive_item(tab_empl(tab_count).item_name,
812 g_assignment_action_id,
813 tab_empl(tab_count).item_value);
814
815 End If;
816 END LOOP;
817
818 CLOSE ir56_Employee_info;
819 Exception
820 When Others Then
821 If ir56_Employee_info%ISOPEN Then
822 CLOSE ir56_Employee_info;
823 End If;
824 hr_utility.set_location('Archive_employee_details ,Exception others',20);
825 RAISE;
826 End Archive_Employee_details;
827
828 ---------------------------------
829 -- [ Archive Employer details ]
830 ---------------------------------
831 PROCEDURE Archive_Employer_details (
832 p_business_group_id IN hr_organization_units.business_group_id%TYPE,
833 p_legal_entity_id IN hr_organization_units.organization_id%TYPE,
834 p_reporting_year IN Varchar2)
835 IS
836 v_loop_cnt number := 0;
837
838 -----------------------------------------
839 --* get employer records to archive
840 -- Bug 8338664, extract employer_name 60 characters
841 -----------------------------------------
842 CURSOR ir56_Employer_info
843 (c_legal_entity_id hr_organization_units.organization_id%TYPE,
844 c_reporting_year Varchar2 )
845 IS
846 SELECT DISTINCT
847 substr(hou.name,1,60) employer_name,
848 hoi.org_information1 employer_tfn,
849 hoi.org_information2 designation,
850 p_legal_entity_id legal_employer_id,
851 hoi.org_information3 contact_name,
852 p_reporting_year reporting_year,
853 TO_CHAR(SYSDATE,'YYYYMMDD') issue_date
854 FROM hr_organization_information hoi,
855 hr_organization_units hou
856 WHERE hoi.org_information_context = 'HK_LEGAL_EMPLOYER'
857 AND hoi.organization_id = hou.organization_id
858 AND hoi.organization_id = c_legal_entity_id ;
859
860 employer_rec ir56_employer_info%ROWTYPE;
861
862
863
864 tab_count Number := 1;
865 tab_index Number := 1;
866
867 e_employer_notfound Exception;
868 Begin
869
870 if tab_empr.count = 0 then/*2263589-Store in table only once.open cursor only first time*/
871
872 OPEN ir56_Employer_info(p_legal_entity_id, p_reporting_year);
873
874 FETCH ir56_Employer_info into employer_rec;
875
876 If ir56_Employer_info%FOUND Then
877 ---------------------------------------
878 --* Prepare employer the data to arhive
879 ---------------------------------------
880 --*
881 tab_empr(1).item_name := 'X_HK_EMPLOYER_TFN';
882 tab_empr(1).item_value := employer_rec.employer_tfn;
883 --*
884 tab_empr(2).item_name := 'X_HK_EMPLOYER_NAME';
885 tab_empr(2).item_value := employer_rec.employer_name;
886 --*
887 tab_empr(3).item_name := 'X_HK_LEGAL_EMPLOYER_ID';
888 tab_empr(3).item_value := employer_rec.legal_employer_id;
889 --*
890 tab_empr(4).item_name := 'X_HK_DESIGNATION';
891 tab_empr(4).item_value := employer_rec.designation;
892 --*
893 tab_empr(5).item_name := 'X_HK_CONTACT';
894 tab_empr(5).item_value := employer_rec.contact_name;
895 --*
896 tab_empr(6).item_name := 'X_HK_REPORTING_YEAR';
897 tab_empr(6).item_value := employer_rec.reporting_year;
898 --*
899 tab_empr(7).item_name := 'X_HK_ISSUE_DATE';
900 tab_empr(7).item_value := employer_rec.issue_date;
901
902
903 CLOSE ir56_Employer_info;
904 Else
905 CLOSE ir56_Employer_info;
906 RAISE e_employer_notfound;
907 end if;
908 end if;
909
910 --* end employer data
911 For tab_count in 1..tab_empr.COUNT
912 LOOP
913 If tab_empr.EXISTS(tab_count) Then
914 archive_item(tab_empr(tab_count).item_name,
915 g_assignment_action_id,
916 tab_empr(tab_count).item_value);
917 End If;
918 END LOOP;
919
920
921 -------------------------------------------
922 --* Call procedure to archive employee data
923 -------------------------------------------
924 Archive_Employee_details(
925 p_business_group_id,
926 p_legal_entity_id,
927 p_reporting_year);
928
929
930 Exception
931 When e_employer_notfound then
932 hr_utility.set_location('No employee Details found for the assigment id ',20);
933 When Others Then
934 If ir56_Employer_info%ISOPEN Then
935 CLOSE ir56_Employer_info;
936 End If;
937 hr_utility.set_location('Error in archive_employee_details ',99);
938 RAISE;
939 End Archive_Employer_details;
940
941 ---------------------------------
942 -- [ Archive Balance details ]
943 ---------------------------------
944 PROCEDURE Archive_Balance_details(
945 p_business_group_id IN hr_organization_units.business_group_id%TYPE,
946 p_legal_entity_id IN hr_organization_units.organization_id%TYPE,
947 p_reporting_year IN Varchar2)
948 IS
949
950 CURSOR max_assign_action_id(c_assignment_id pay_assignment_actions.assignment_id%TYPE,
951 c_reporting_year Varchar2 )
952 IS
953 SELECT paa.assignment_action_id
954 FROM pay_assignment_actions paa
955 WHERE paa.assignment_id = c_assignment_id
956 and paa.action_sequence = (select max(paa2.action_sequence)
957 from pay_assignment_actions paa2,
958 pay_payroll_actions ppa
959 where paa2.assignment_id = c_assignment_id
960 and ppa.payroll_action_id = paa2.payroll_action_id
961 and paa2.action_status = 'C'
962 and ppa.action_type in ('R', 'Q', 'B', 'I', 'V')
963 and ppa.effective_date BETWEEN TO_DATE('01-04-'|| TO_CHAR(TO_NUMBER(c_reporting_year)-1), 'DD-MM-YYYY')
964 and TO_DATE('31-03-'||c_reporting_year, 'DD-MM-YYYY'));
965
966 rec_max_assign_action_id max_assign_action_id%ROWTYPE;
967
968 l_emol_details pay_hk_ir56.g_emol_details_tab;
969 i Number := 1;
970 v_period_format Varchar2(100); -- Holds the period in the format yyyymmdd-yyyymmdd
971
972 Begin
973 OPEN max_assign_action_id(g_assignment_id,p_reporting_year);
974 FETCH max_assign_action_id INTO rec_max_assign_action_id;
975 CLOSE max_assign_action_id;
976
977 l_emol_details := PAY_HK_IR56.GET_EMOLUMENTS
978 (g_assignment_id,
979 rec_max_assign_action_id.assignment_action_id,
980 p_legal_entity_id,
981 p_reporting_year);
982
983 FOR i IN l_emol_details.FIRST..l_emol_details.LAST
984 LOOP
985 IF(l_emol_details.EXISTS(i)) THEN
986
987 archive_item('X_HK_' || l_emol_details(i).balance_name ||'_ASG_LE_YTD',
988 g_assignment_action_id,
989 l_emol_details(i).balance_value);
990 archive_item('X_HK_' || l_emol_details(i).balance_name ||'_DESCRIPTION',
991 g_assignment_action_id,
992 l_emol_details(i).particulars);
993
994 --* the period returned is in the format dd/mm/yyyy-dd/mm/yyyy
995 --* change it to yyyymmdd - yyyymmdd
996 If (l_emol_details(i).period_dates IS NOT NULL) THEN
997 v_period_format := substr(l_emol_details(i).period_dates,7,4)||substr(l_emol_details(i).period_dates,4,2)||
998 substr(l_emol_details(i).period_dates,1,2)||' - '||
999 substr(l_emol_details(i).period_dates,20,4)||substr(l_emol_details(i).period_dates,17,2)||
1000 substr(l_emol_details(i).period_dates,14,2);
1001 Else
1002 v_period_format := l_emol_details(i).period_dates;
1003 end If;
1004
1005 archive_item('X_HK_' || l_emol_details(i).balance_name ||'_PERIOD',
1006 g_assignment_action_id,
1007 v_period_format);
1008 END IF;
1009 END LOOP;
1010
1011 Exception
1012 When Others Then
1013 hr_utility.set_location('Error in archive_balance_details ',99);
1014 RAISE;
1015 End Archive_Balance_details;
1016
1017 ---------------------------------
1018 -- [ Archive Quarter details ]
1019 ---------------------------------
1020 PROCEDURE Archive_Quarter_details(
1021 p_business_group_id IN hr_organization_units.business_group_id%TYPE,
1022 p_legal_entity_id IN hr_organization_units.organization_id%TYPE,
1023 p_reporting_year IN Varchar2)
1024 IS
1025 v_loop_cnt number := 0;
1026
1027 /* Bug No : 2760137 - Modified the cursor quarters_info */
1028
1029 /* Bug No : 2805822 - Modified the cursor quarters_info, changed the decode statements for
1030 quarter_period_start and quarter_period_end which is calculated as below:-
1031
1032 If user has entered quarter start and end dates, and either start
1033 date, end date, or both, fall within the tax year being processed
1034 The quarters period is calculated as follows:
1035
1036 Start Date = 1-Apr Tax Year, Quarters Start Date or Hire Date
1037 (whichever is later)
1038 End Date = 31-Mar Tax Year, Quarters End Date or Termination Date
1039 (whichever is earlier)
1040
1041 If user has entered quarter start and end dates, and both start and
1042 end date fall outside the processing year, OR if the user has not
1043 entered dates. The quarters period is calculated as follows:
1044
1045 Start Date = 1-Apr Tax Year or Hire Date(whichever is later)
1046
1047 End Date = 31-Mar Tax Year or Termination Date(whichever is earlier) */
1048
1049 /* Bug No : 2805822 - Modified the cursor quarters_info, added date effective check
1050 so that quarters details for that particular financial year is selected*/
1051
1052 /* Bug No : 2853776 - Cursor quarters_info modified -
1053 The sub query included for Bug No: 2853776 has been modified to join max action_sequence instead of
1054 max assignment action id.*/
1055
1056 /* Bug 7184102 Added ORDER BY clause into cursor quarters_info */
1057
1058 CURSOR quarters_info
1059 (c_assignment_id IN pay_assignment_actions.assignment_id%TYPE,
1060 c_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE)
1061 IS
1062 SELECT b.assignment_id assignment_id,
1063 b.SOURCE_ID source_id,
1064 b.QUARTERS_ADDRESS quarters_address,
1065 b.QUARTERS_NATURE quarters_nature,
1066 to_char(decode(to_date(max(b.quarters_period_start),'DD/MM/YYYY'), null,
1067 greatest(max(pps.date_start),to_date(to_char(to_number(p_reporting_year)-1)||'0401', 'YYYYMMDD')),
1068 decode(greatest(to_date(max(b.quarters_period_start),'DD/MM/YYYY'), to_date(p_reporting_year||'0331', 'YYYYMMDD')),
1069 to_date(max(b.quarters_period_start),'DD/MM/YYYY'),
1070 greatest(max(pps.date_start),to_date(to_char(to_number(p_reporting_year)-1)||'0401', 'YYYYMMDD')),
1071 greatest(max(pps.date_start),to_date(to_char(to_number(p_reporting_year)-1)||'0401', 'YYYYMMDD'), to_date(max(b.quarters_period_start),'DD/MM/YYYY')))), 'YYYYMMDD') quarters_period_start,
1072 to_char(decode(to_date(max(b.quarters_period_end),'DD/MM/YYYY'), null,
1073 least(to_date(p_reporting_year||'0331', 'YYYYMMDD'), nvl(max(pps.actual_termination_date), to_date('31-12-4712', 'DD-MM-YYYY'))),
1074 decode(least(to_date(max(b.quarters_period_end),'DD/MM/YYYY'), to_date(to_char(to_number(p_reporting_year)-1)||'0401', 'YYYYMMDD')),
1075 to_date(max(b.quarters_period_end),'DD/MM/YYYY'),
1076 least(to_date(p_reporting_year||'0331', 'YYYYMMDD'), nvl(max(pps.actual_termination_date), to_date('31-12-4712', 'DD-MM-YYYY'))),
1077 least(to_date(max(b.quarters_period_end),'DD/MM/YYYY'), nvl(max(pps.actual_termination_date), to_date('31-12-4712', 'DD-MM-YYYY')), to_date(p_reporting_year||'0331', 'YYYYMMDD')))), 'YYYYMMDD') quarters_period_end,
1078 max(b.QUARTERS_ER_TO_LANDLORD) QUARTERS_ER_TO_LANDLORD,
1079 max(b.QUARTERS_EE_TO_LANDLORD) QUARTERS_EE_TO_LANDLORD,
1080 max(b.QUARTERS_REFUND_TO_EE) QUARTERS_REFUND_TO_EE,
1081 max(b.QUARTERS_EE_TO_ER) QUARTERS_EE_TO_ER
1082 FROM pay_hk_ir56_quarters_info_v b,
1083 per_periods_of_service pps,
1084 per_assignments_f paa
1085 WHERE b.assignment_id = c_assignment_id
1086 AND paa.assignment_id = b.assignment_id
1087 AND paa.person_id = pps.person_id
1088 AND paa.period_of_service_id = pps.period_of_service_id /* Bug No : 2824718 */
1089 AND b.action_sequence = (SELECT max(action_sequence)
1090 FROM pay_hk_ir56_quarters_info_v
1091 WHERE assignment_id = b.assignment_id
1092 AND source_id = b.source_id /* Bug No : 2853776 */
1093 AND start_date between
1094 to_date('01/04/'||to_char(to_number(p_reporting_year)-1),'DD/MM/YYYY')
1095 AND to_date('31/03/'||p_reporting_year,'DD/MM/YYYY')
1096 AND end_date between
1097 to_date('01/04/'||to_char(to_number(p_reporting_year)-1),'DD/MM/YYYY')
1098 AND to_date('31/03/'||p_reporting_year,'DD/MM/YYYY'))
1099 GROUP BY
1100 b.assignment_id,
1101 b.SOURCE_ID,
1102 b.QUARTERS_ADDRESS,
1103 b.QUARTERS_NATURE,
1104 quarters_period_start,
1105 quarters_period_end
1106 ORDER BY quarters_period_start,quarters_period_end; /* Bug 7184102 */
1107
1108 quarters_rec quarters_info%rowtype;
1109
1110 /* Bug 2558852 Increased the item_value size from 100 to 150 to
1111 accomodate quarters address which can exceed 100 characters */
1112 TYPE
1113 archive_rec IS RECORD (item_name Varchar2(100),
1114 item_value Varchar2(150));
1115 TYPE
1116 archive_tab IS TABLE OF archive_rec INDEX BY BINARY_INTEGER;
1117 tab_quarter archive_tab;
1118
1119 tab_count Number := 1;
1120
1121 Begin
1122
1123 ---------------------------------------
1124 --* Initialization section
1125 ---------------------------------------
1126 -------------
1127 --* Quarter 1
1128 -------------
1129 tab_quarter(1).item_name := 'X_HK_QUARTERS_PROVIDED';
1130 --*
1131 tab_quarter(2).item_name := 'X_HK_QUARTERS_1_ADDRESS';
1132 --*
1133 tab_quarter(3).item_name := 'X_HK_QUARTERS_1_NATURE';
1134 --*
1135 tab_quarter(4).item_name := 'X_HK_QUARTERS_1_PERIOD';
1136 --*
1137 tab_quarter(5).item_name := 'X_HK_QUARTERS_1_ER_TO_LL_LE_YTD';
1138 --*
1139 tab_quarter(6).item_name := 'X_HK_QUARTERS_1_EE_TO_LL_LE_YTD';
1140 --*
1141 tab_quarter(7).item_name := 'X_HK_QUARTERS_1_REFUND_TO_EE_LE_YTD';
1142 --*
1143 tab_quarter(8).item_name := 'X_HK_QUARTERS_1_EE_TO_ER_LE_YTD';
1144
1145 -------------
1146 --* Quarter 2
1147 -------------
1148 tab_quarter(9).item_name := 'X_HK_QUARTERS_2_ADDRESS';
1149 --*
1150 tab_quarter(10).item_name := 'X_HK_QUARTERS_2_NATURE';
1151 --*
1152 tab_quarter(11).item_name := 'X_HK_QUARTERS_2_PERIOD';
1153 --*
1154 tab_quarter(12).item_name := 'X_HK_QUARTERS_2_ER_TO_LL_LE_YTD';
1155 --*
1156 tab_quarter(13).item_name := 'X_HK_QUARTERS_2_EE_TO_LL_LE_YTD';
1157 --*
1158 tab_quarter(14).item_name := 'X_HK_QUARTERS_2_REFUND_TO_EE_LE_YTD';
1159 --*
1160 tab_quarter(15).item_name := 'X_HK_QUARTERS_2_EE_TO_ER_LE_YTD';
1161 --*
1162
1163 g_error_in_quarter := FALSE;
1164
1165 OPEN quarters_info(g_assignment_id,g_assignment_action_id);
1166 FETCH quarters_info INTO quarters_rec;
1167 If quarters_info%FOUND Then
1168 --*
1169 tab_quarter(1).item_value := 1;
1170 --*
1171 tab_quarter(2).item_value := quarters_rec.quarters_address ; -- check it
1172 --*
1173 tab_quarter(3).item_value := quarters_rec.quarters_nature;
1174 --*
1175 tab_quarter(4).item_value := quarters_rec.QUARTERS_PERIOD_START||'-'
1176 ||quarters_rec.QUARTERS_PERIOD_END ;
1177 --*
1178 tab_quarter(5).item_value := quarters_rec.QUARTERS_ER_TO_LANDLORD;
1179 --*
1180 tab_quarter(6).item_value := quarters_rec.QUARTERS_EE_TO_LANDLORD;
1181 --*
1182 tab_quarter(7).item_value := quarters_rec. QUARTERS_REFUND_TO_EE;
1183 --*
1184 tab_quarter(8).item_value := quarters_rec.QUARTERS_EE_TO_ER;
1185 --*
1186
1187 --* Fetch again to get the second quarter detail if exists
1188
1189 FETCH quarters_info into quarters_rec;
1190 If quarters_info%FOUND then
1191 --*
1192 tab_quarter(9).item_value := quarters_rec.quarters_address ; -- check it
1193 --*
1194 tab_quarter(10).item_value := quarters_rec.quarters_nature;
1195 --*
1196 tab_quarter(11).item_value := quarters_rec.QUARTERS_PERIOD_START||'-'
1197 ||quarters_rec.QUARTERS_PERIOD_END ;
1198 --*
1199 tab_quarter(12).item_value := quarters_rec.QUARTERS_ER_TO_LANDLORD;
1200 --*
1201 tab_quarter(13).item_value := quarters_rec.QUARTERS_EE_TO_LANDLORD;
1202 --*
1203 tab_quarter(14).item_value := quarters_rec.QUARTERS_REFUND_TO_EE;
1204 --*
1205 tab_quarter(15).item_value := quarters_rec.QUARTERS_EE_TO_ER;
1206 End If;
1207
1208 --* If more than 2 quarters exists then its an error
1209 FETCH quarters_info into quarters_rec;
1210 If quarters_info%FOUND Then
1211 --* If the employee is having more than 2 quaretrs then archive message need to be
1212 --* populated but the record need to be archived
1213 g_archive_message := g_assignment_action_id||':Employee number '
1214 ||g_employee_number||' has more than two quarters locations.';
1215 g_error_in_quarter := TRUE;
1216
1217 End if;
1218 Else
1219 --* If no quarters exists then set X_HK_QUARTERS_PROVIDED to 0
1220 tab_quarter(1).item_value := 0;
1221 End if;
1222
1223 --* Archive Quarter details
1224 For tab_count in 1..tab_quarter.COUNT
1225 LOOP
1226 If tab_quarter.EXISTS(tab_count) Then
1227 archive_item(tab_quarter(tab_count).item_name,
1228 g_assignment_action_id,
1229 tab_quarter(tab_count).item_value);
1230
1231 End If;
1232 END LOOP;
1233
1234 CLOSE quarters_info;
1235
1236 Exception
1237 When Others Then
1238 If quarters_info%ISOPEN Then
1239 CLOSE quarters_info;
1240 End If;
1241 hr_utility.set_location('Error in archive_quarter_details ',99);
1242 RAISE;
1243 End Archive_Quarter_details;
1244
1245
1246 ---------------------------------
1247 -- [ Archive Overseas details ]
1248 ---------------------------------
1249
1250 PROCEDURE Archive_Overseas_Details(
1251 p_business_group_id IN hr_organization_units.business_group_id%TYPE,
1252 p_legal_entity_id IN hr_organization_units.organization_id%TYPE,
1253 p_reporting_year IN Varchar2)
1254 IS
1255 v_loop_cnt number := 0;
1256
1257 CURSOR os_info (c_assignment_id in pay_assignment_actions.assignment_id%TYPE)
1258 IS
1259 SELECT *
1260 FROM pay_hk_ir56_overseas_concern_v
1261 WHERE assignment_id = c_assignment_id
1262 AND tax_reporting_year = p_reporting_year;
1263
1264 os_rec os_info%ROWTYPE;
1265
1266 e_More_Than_1_Overseas_conc Exception;
1267
1268 p_Value Varchar2(240);
1269
1270 Begin
1271
1272 OPEN os_info(g_assignment_id);
1273 FETCH os_info into os_rec;
1274 If os_info%FOUND Then
1275 archive_item('X_HK_OVERSEAS_CONCERN',g_assignment_action_id,1);
1276 archive_item('X_HK_OVERSEAS_AMOUNT',g_assignment_action_id,os_rec.overseas_amount_message);
1277 archive_item('X_HK_OVERSEAS_NAME',g_assignment_action_id,os_rec.overseas_name);
1278 archive_item('X_HK_OVERSEAS_ADDRESS',g_assignment_action_id,os_rec.overseas_address);
1279 Else
1280 archive_item('X_HK_OVERSEAS_CONCERN',g_assignment_action_id,0);
1281 archive_item('X_HK_OVERSEAS_AMOUNT',g_assignment_action_id,NULL);
1282 archive_item('X_HK_OVERSEAS_NAME',g_assignment_action_id, NULL);
1283 archive_item('X_HK_OVERSEAS_ADDRESS',g_assignment_action_id,NULL);
1284 End If;
1285
1286 --* If the employee is having more than 1 overseas concrens then archive message
1287 --* need to be populated but rest the record need to be archived
1288 FETCH os_info into os_rec;
1289 If os_info%FOUND Then
1290 --* If the message is not pupulated for Quarter then only archive the
1291 --* message for overseas concern.We can have only one message per employee
1292 If g_error_in_quarter = FALSE Then
1293 g_archive_message := g_assignment_action_id||':Employee number '
1294 ||g_employee_number||' has multiple overseas concern details';
1295 End If;
1296 End If;
1297 CLOSE os_info;
1298 Exception
1299 When Others Then
1300 If os_info%ISOPEN Then
1301 CLOSE os_info;
1302 End If;
1303 hr_utility.set_location('Error in archive_quarter_details ',99);
1304 RAISE;
1305 End Archive_Overseas_Details;
1306
1307 Procedure Archive_Excep_Error_dtls(
1308 p_business_group_id IN hr_organization_units.business_group_id%TYPE,
1309 p_legal_entity_id IN hr_organization_units.organization_id%TYPE,
1310 p_reporting_year IN Varchar2)
1311 IS
1312 CURSOR ir56_Employer_info
1313 (c_legal_entity_id hr_organization_units.organization_id%TYPE,
1314 c_reporting_year Varchar2 )
1315 IS
1316 SELECT DISTINCT
1317 hou.name employer_name,
1318 hoi.org_information1 employer_tfn,
1319 p_reporting_year reporting_year
1320 FROM hr_organization_information hoi,
1321 hr_organization_units hou
1322 WHERE hoi.org_information_context = 'HK_LEGAL_EMPLOYER'
1323 AND hoi.organization_id = hou.organization_id
1324 AND hoi.organization_id = c_legal_entity_id ;
1325
1326 employer_rec ir56_employer_info%ROWTYPE;
1327
1328 CURSOR ir56_Employee_info
1329 (c_assignment_id pay_assignment_actions.assignment_id%TYPE,
1330 c_reporting_year Varchar2 )
1331 IS
1332 SELECT DISTINCT
1333 papf.national_identifier hk_id_card_no,
1334 DECODE(papf.marital_status, 'M',
1335 DECODE(sex, 'F', NVL(previous_last_name, last_name), last_name)
1336 ,last_name) last_name,
1337 TRIM(papf.first_name||' '||papf.middle_names) other_name
1338 FROM per_people_f papf,
1339 per_assignments_f paaf,
1340 per_periods_of_service pps
1341 WHERE paaf.person_id = papf.person_id
1342 AND TO_DATE('31-03-'|| c_reporting_year, 'DD-MM-YYYY')
1343 BETWEEN paaf.effective_start_date and paaf.effective_end_date
1344 AND TO_DATE('31-03-'|| c_reporting_year, 'DD-MM-YYYY')
1345 BETWEEN papf.effective_start_date and papf.effective_end_date
1346 AND papf.business_group_id = p_business_group_id
1347 AND paaf.assignment_id = c_assignment_id
1348 AND pps.person_id = paaf.person_id
1349 AND pps.period_of_service_id = paaf.period_of_service_id /* Bug No : 3059915 */
1350 AND TO_DATE('31-03-'||c_reporting_year, 'DD-MM-YYYY')
1351 BETWEEN pps.date_start
1352 AND NVL(pps.actual_termination_date,TO_DATE('31-12-4712','dd-mm-yyyy'));
1353
1354 employee_rec ir56_Employee_info%ROWTYPE;
1355
1356 TYPE
1357 archive_rec IS RECORD (item_name Varchar2(100),
1358 item_value Varchar2(100));
1359 TYPE
1360 archive_tab IS TABLE OF archive_rec INDEX BY BINARY_INTEGER;
1361
1362 tab_exception archive_tab;
1363
1364 tab_count Number := 1;
1365 tab_index Number := 1;
1366
1367 e_employer_notfound Exception;
1368 e_employee_notfound Exception;
1369 Begin
1370
1371 OPEN ir56_Employer_info(p_legal_entity_id, p_reporting_year);
1372 FETCH ir56_Employer_info into employer_rec;
1373
1374 If ir56_Employer_info%FOUND Then
1375 --------------------------------------------
1376 --* Prepare employer/employee data to arhive
1377 --------------------------------------------
1378 --*
1379 tab_exception(1).item_name := 'X_HK_EMPLOYER_TFN';
1380 tab_exception(1).item_value := employer_rec.employer_tfn;
1381 --*
1382 tab_exception(2).item_name := 'X_HK_EMPLOYER_NAME';
1383 tab_exception(2).item_value := employer_rec.employer_name;
1384 --*
1385 tab_exception(3).item_name := 'X_HK_REPORTING_YEAR';
1386 tab_exception(3).item_value := employer_rec.reporting_year;
1387
1388 OPEN ir56_Employee_info(g_assignment_id,p_reporting_year);
1389 FETCH ir56_Employee_info INTO employee_rec ;
1390 If ir56_Employee_info%FOUND Then
1391 --*
1392 tab_exception(4).item_name := 'X_HK_LAST_NAME';
1393 tab_exception(4).item_value := employee_rec.last_name;
1394 --*
1395 tab_exception(5).item_name := 'X_HK_OTHER_NAMES';
1396 tab_exception(5).item_value := employee_rec.other_name;
1397 --*
1398 tab_exception(6).item_name := 'X_HK_HKID';
1399 tab_exception(6).item_value := employee_rec.hk_id_card_no;
1400
1401 --* Archive the details prepared above
1402 For tab_count in 1..tab_exception.COUNT
1403 LOOP
1404 If tab_exception.EXISTS(tab_count) Then
1405 archive_item(tab_exception(tab_count).item_name,
1406 g_assignment_action_id,
1407 tab_exception(tab_count).item_value);
1408 End If;
1409 END LOOP;
1410 Else
1411 CLOSE ir56_Employee_info;
1412 RAISE e_employee_notfound;
1413 End If;
1414 Else
1415 CLOSE ir56_Employer_info;
1416 RAISE e_employer_notfound;
1417 End If;
1418
1419 CLOSE ir56_Employee_info;
1420 CLOSE ir56_Employer_info;
1421
1422 Exception
1423 When e_employer_notfound then
1424 hr_utility.set_location('From Archive_excp:No employer Details found ',20);
1425 When e_employee_notfound then
1426 hr_utility.set_location('From Archive_excp:No employee Details found ',20);
1427 When Others Then
1428 If ir56_Employer_info%ISOPEN Then
1429 CLOSE ir56_Employer_info;
1430 End If;
1431 If ir56_Employee_info%ISOPEN Then
1432 CLOSE ir56_Employee_info;
1433 End If;
1434 hr_utility.set_location('Error in archive_employee_details ',99);
1435 RAISE;
1436 End Archive_Excep_Error_dtls;
1437
1438
1439 --------------------------------------------------
1440 --* This function validates
1441 --* Person is manually excluded
1442 --------------------------------------------------
1443
1444 Function Validate_Employee(p_reporting_year IN Varchar2) Return Number
1445 IS
1446
1447 Cursor Get_ManualExclusion(p_assignment_id per_all_assignments_f.assignment_id%TYPE)
1448 IS
1449 SELECT pap.employee_number
1450 FROM per_assignments_f a,
1451 hr_soft_coding_keyflex sck,
1452 per_people_f pap
1453 WHERE a.assignment_id = p_assignment_id
1454 AND a.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
1455 AND pap.person_id = a.person_id
1456 AND TO_DATE('31/03'||p_reporting_year,'DD/MM/YYYY')
1457 BETWEEN a.effective_start_date AND a.effective_end_date
1458 AND NVL(sck.segment3, 'Y') = 'N';
1459
1460
1461 l_employee_number per_all_people_f.employee_number%TYPE;
1462
1463 Begin
1464 --* Find whether person is manually excluded
1465 OPEN Get_ManualExclusion(g_assignment_id);
1466 FETCH Get_ManualExclusion INTO l_employee_number;
1467 If Get_ManualExclusion%FOUND Then
1468 g_employee_number := l_employee_number;
1469 g_archive_message := g_assignment_action_id||':Employee number '
1470 ||g_employee_number||' has been manually excluded';
1471 CLOSE Get_ManualExclusion;
1472 RETURN 0;
1473 End If;
1474
1475 CLOSE Get_ManualExclusion; /*Bug 2805822*/
1476 RETURN 1;
1477 Exception
1478 When Others Then
1479 NULL;
1480 End Validate_Employee;
1481
1482
1483 PROCEDURE Archive_Info
1484 (
1485 p_business_group_id IN hr_organization_units.business_group_id%TYPE,
1486 p_legal_entity_id IN hr_organization_units.organization_id%TYPE,
1487 p_reporting_year IN Varchar2)
1488 IS
1489 res Number;
1490 Begin
1491 ----------------------------------------------------------------
1492 --* Validate_Employee checks whether the emplyee is manually
1493 --* excluded.
1494 --* If so then message is set in HK_ARCHIVE_MESSAGE it
1495 --* returns value 0 and the assignment is not processed.
1496 --* Note : For employees having more than 2 Quartyers
1497 --* and more than 1 overseas concerns message is still
1498 --* populated in HK_ARCHIVE_MESSAGE but the assignment is
1499 --* not prevented from the archival.So these cases are not handled
1500 --* in this functions . They are handled in there respective procs
1501 ------------------------------------------------------------------
1502
1503
1504 res := Validate_Employee(p_reporting_year);
1505 If res = 1 Then
1506 --* Archive Employer details calls Archive_Employee_Details internally
1507 g_archive_message := NULL;
1508 Archive_Employer_details(
1509 p_business_group_id,
1510 p_legal_entity_id,
1511 p_reporting_year) ;
1512 Archive_Balance_details(
1513 p_business_group_id,
1514 p_legal_entity_id,
1515 p_reporting_year) ;
1516 Archive_Quarter_details(
1517 p_business_group_id,
1518 p_legal_entity_id,
1519 p_reporting_year) ;
1520 Archive_Overseas_details(
1521 p_business_group_id,
1522 p_legal_entity_id,
1523 p_reporting_year) ;
1524
1525 archive_item('X_HK_SHEET_NO', g_assignment_action_id,0);
1526
1527 archive_item('X_HK_ARCHIVE_MESSAGE',
1528 g_assignment_action_id,
1529 g_archive_message);
1530
1531 Else
1532 --* Archive only the details needed to print on the
1533 --* exception listing report when the employee is manually excluded
1534 --* 1) employer TFN 2) employer name 3) reporting year 4) employee last name
1535 --* 5) employee other names 6) employee hkid
1536 Archive_Excep_Error_dtls(
1537 p_business_group_id,
1538 p_legal_entity_id,
1539 p_reporting_year) ;
1540 archive_item('X_HK_ARCHIVE_MESSAGE',
1541 g_assignment_action_id,
1542 g_archive_message);
1543
1544 End if;
1545 End archive_info;
1546
1547 ------------------------------------------------------------------------
1548 -- SELECTs the SRS parameters for the archive and calls other PROCEDUREs
1549 -- to archive the data in groups because depending on the data,
1550 -- different parameters are required.
1551 ------------------------------------------------------------------------
1552
1553 PROCEDURE archive_code
1554 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE,
1555 p_effective_date in date)
1556 IS
1557 v_person_id per_all_people_f.person_id%TYPE;
1558 v_assignment_id per_all_assignments_f.assignment_id%TYPE;
1559 v_business_group_id hr_organization_units.business_group_id%TYPE;
1560 v_legal_entity_id pay_assignment_actions.tax_unit_id%TYPE;
1561 v_reporting_year varchar2(4);
1562 v_archive_date pay_payroll_actions.effective_date%TYPE;
1563
1564 CURSOR archive_parameters
1565 (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
1566 IS
1567 SELECT paa.person_id,
1568 pac.assignment_id,
1569 pay_core_utils.get_parameter('BUSINESS_GROUP_ID',ppa.legislative_parameters),
1570 pay_core_utils.get_parameter('LEGAL_ENTITY_ID',ppa.legislative_parameters),
1571 pay_core_utils.get_parameter('REPORTING_YEAR',ppa.legislative_parameters),
1572 effective_date
1573 FROM pay_payroll_actions ppa,
1574 pay_assignment_actions pac,
1575 per_assignments_f paa
1576 WHERE pac.assignment_action_id = c_assignment_action_id
1577 AND ppa.payroll_action_id = pac.payroll_action_id
1578 AND paa.assignment_id = pac.assignment_id;
1579
1580 e_No_Assignment_Found Exception;
1581
1582 Begin
1583 hr_utility.set_location('pyhkirar: Start of archive_code',10);
1584
1585 OPEN archive_parameters (p_assignment_action_id);
1586 FETCH archive_parameters INTO v_person_id,
1587 v_assignment_id,
1588 v_business_group_id,
1589 v_legal_entity_id,
1590 v_reporting_year,
1591 v_archive_date;
1592 If archive_parameters%FOUND Then
1593 hr_utility.set_location('pyhkirar: Person Id: ' || to_char(v_person_id) ,100);
1594
1595 --* set the global value for assignment_id
1596 g_assignment_id := v_assignment_id;
1597
1598 --* set the global value for business_group_id
1599 g_business_group_id := v_business_group_id;
1600
1601 --* set the global value for assignment_action_id
1602 g_assignment_action_id := p_assignment_action_id;
1603
1604 If g_assignment_id IS NOT NULL Then
1605 Archive_Info(v_business_group_id,v_legal_entity_id, v_reporting_year);
1606 Else
1607 CLOSE archive_parameters;
1608 RAISE e_No_Assignment_Found;
1609 End if;
1610 End If;
1611
1612 CLOSE archive_parameters;
1613
1614 hr_utility.set_location('pyhkirar: End of archive_code',20);
1615 Exception
1616 When e_No_Assignment_Found Then
1617 hr_utility.set_location('Exception: ARCHIVE_CODE,:No assignment id for assignment action id '||
1618 p_assignment_action_id,20);
1619 When Others Then
1620 If archive_parameters%ISOPEN Then
1621 CLOSE archive_parameters;
1622 End If;
1623 hr_utility.set_location('Error in archive_code ',99);
1624 RAISE;
1625 End archive_code;
1626
1627
1628 ---------------------------------------------------------------------------
1629 -- Calls the archive utility to actually perform the archive of the item.
1630 ---------------------------------------------------------------------------
1631
1632 PROCEDURE archive_item
1633 (p_user_entity_name IN ff_user_entities.user_entity_name%TYPE,
1634 p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE,
1635 p_archive_value IN ff_archive_items.value%TYPE)
1636 IS
1637 v_user_entity_id ff_user_entities.user_entity_id%TYPE;
1638 v_archive_item_id ff_archive_items.archive_item_id%TYPE;
1639 v_object_version_number ff_archive_items.object_version_number%TYPE;
1640 v_some_warning boolean;
1641
1642 CURSOR user_entity_id(c_user_entity_name ff_user_entities.user_entity_name%TYPE)
1643 IS
1644 SELECT user_entity_id
1645 FROM ff_user_entities
1646 WHERE user_entity_name = c_user_entity_name;
1647
1648 Begin
1649 hr_utility.set_location('Start of archive_item',10);
1650
1651 OPEN user_entity_id (p_user_entity_name);
1652 FETCH user_entity_id into v_user_entity_id;
1653 If user_entity_id%FOUND Then
1654
1655 ff_archive_api.create_archive_item
1656 (p_validate => false -- boolean in default
1657 ,p_archive_item_id => v_archive_item_id -- number out
1658 ,p_user_entity_id => v_user_entity_id -- number in
1659 ,p_archive_value => p_archive_value -- varchar2 in
1660 ,p_archive_type => 'AAP' -- varchar2 in default
1661 ,p_action_id => p_assignment_action_id -- number in
1662 ,p_legislation_code => 'HK' -- varchar2 in
1663 ,p_object_version_number => v_object_version_number -- number out
1664 ,p_context_name1 => 'ASSIGNMENT_ACTION_ID' -- varchar2 in default
1665 ,p_context1 => p_assignment_action_id -- varchar2 in default
1666 ,p_some_warning => v_some_warning); -- boolean out
1667
1668 hr_utility.set_location('End of archive_item',20);
1669 Else
1670 hr_utility.set_location('User entity not found :'||p_user_entity_name,20);
1671 End If;
1672 CLOSE user_entity_id;
1673
1674 Exception
1675 When Others Then
1676 If user_entity_id%ISOPEN Then
1677 CLOSE user_entity_id;
1678 End If;
1679 hr_utility.set_location('Error in archive_item ',99);
1680 RAISE;
1681 End archive_item;
1682
1683 ------------------------------
1684 -- [ Submit the report ]
1685 ------------------------------
1686 FUNCTION SUBMIT_REPORT
1687 (p_archive_or_magtape in varchar2) RETURN Number
1688 IS
1689 l_count NUMBER := 0;
1690 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
1691 l_archive_action_id pay_payroll_actions.payroll_action_id%TYPE;
1692
1693 l_number_of_copies NUMBER := 0;/*Reverted fix for 2810178 */
1694 l_request_id NUMBER := 0;
1695 l_print_return BOOLEAN;
1696 l_report_short_name varchar2(30);
1697
1698 l_formula_id number ;
1699
1700 l_error_text varchar2(255) ;
1701 e_missing_formula exception ;
1702 e_submit_error exception ;
1703
1704 -- Cursor to get the report print options.
1705
1706 cursor csr_get_print_options(p_payroll_action_id NUMBER) IS
1707 SELECT printer,
1708 print_style,
1709 decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
1710 FROM pay_payroll_actions pact,
1711 fnd_concurrent_requests fcr
1712 WHERE fcr.request_id = pact.request_id
1713 AND pact.payroll_action_id = p_payroll_action_id;
1714
1715 rec_print_options csr_get_print_options%ROWTYPE;
1716
1717 cursor get_archive_id is
1718 select distinct ppa3.payroll_action_id
1719 from
1720 pay_payroll_actions ppa, -- Magtape payroll action
1721 pay_payroll_actions ppa2, -- Report payroll action
1722 pay_payroll_actions ppa3 -- Archive payroll action
1723 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
1724 and ppa2.payroll_action_id =pay_core_utils.get_parameter('REPORT_ACTION_ID', ppa.legislative_parameters)
1725 and ppa3.payroll_action_id =pay_core_utils.get_parameter('ARCHIVE_ACTION_ID', ppa2.legislative_parameters);
1726
1727
1728
1729
1730 begin
1731
1732 -- Get all of the parameters needed to submit the report. Parameters defined
1733 -- in the concurrent program definition are passed through here by the PAR
1734 -- process. End the loop by the exception clause because we don't know
1735 -- what order the parameters will be in.
1736
1737 -- Default the parameters in case they are not found.
1738 hr_utility.set_location('Submit report called',1);
1739
1740 l_archive_action_id := 0;
1741
1742 Begin
1743 LOOP
1744 l_count := l_count + 1;
1745 if pay_mag_tape.internal_prm_names(l_count) = 'TRANSFER_PAYROLL_ACTION_ID' then
1746 if p_archive_or_magtape = 'MAGTAPE' then
1747 OPEN get_archive_id;
1748 FETCH get_archive_id into l_archive_action_id;
1749 else
1750 l_archive_action_id := to_number(pay_mag_tape.internal_prm_values(l_count));
1751 end if;
1752 end if;
1753 END LOOP;
1754 Exception
1755 When no_data_found then
1756 hr_utility.set_location('No data found',1);
1757 NULL;
1758 When value_error then
1759 hr_utility.set_location('Value error',1);
1760 NULL;
1761 End;
1762 -- Default the number of report copies to 0.
1763 l_number_of_copies := 0;/*Reverted fix for 2810178 */
1764 -- Set up the printer options.
1765
1766 OPEN csr_get_print_options(l_archive_action_id);
1767 FETCH csr_get_print_options INTO rec_print_options;
1768 CLOSE csr_get_print_options;
1769
1770 hr_utility.set_location('fnd_request.set_print_options',1);
1771
1772 /*Reverted fix for 2810178 */
1773 l_print_return := fnd_request.set_print_options
1774 (printer => rec_print_options.printer,
1775 style => rec_print_options.print_style,
1776 copies => l_number_of_copies,
1777 save_output => hr_general.char_to_bool(rec_print_options.save_output),
1778 print_together => 'N');
1779
1780 l_report_short_name := 'PAYHKCTL';
1781
1782 -- Submit the report
1783
1784 Begin
1785
1786 -- Need to supply the parameters with keywords because it's a postscript report
1787 -- and the option version=2.0b set in the SRS definition uses a keyword, hence
1788
1789 hr_utility.set_location('fnd_request.submit_request',1);
1790
1791 l_request_id := fnd_request.submit_request
1792 (application => 'PAY',
1793 program => l_report_short_name,
1794 argument1 => 'P_ARCHIVE_ACTION_ID='||l_archive_action_id,
1795 argument2 => 'P_ARCHIVE_OR_MAGTAPE='||p_archive_or_magtape,
1796 argument3 => 'P_BUSINESS_GROUP_ID='||g_business_group_id);
1797
1798 -- If an error submitting report then get message and put to log.
1799
1800 hr_utility.set_location('l_request_id : '||l_request_id,1);
1801
1802 If l_request_id = 0 Then
1803 RAISE e_submit_error;
1804 End If;
1805 RETURN l_request_id;
1806 Exception
1807 When e_submit_error then
1808 ROLLBACK ;
1809 raise_application_error(-20001, 'Could Not submit report') ;
1810 Return 0;
1811 When others then
1812 ROLLBACK;
1813 raise_application_error(-20001, sqlerrm) ;
1814 Return 0;
1815 End;
1816
1817 End submit_report;
1818
1819 End pay_hk_ir56_archive;
[Home] [Help]