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