[Home] [Help]
PACKAGE BODY: APPS.PAY_HK_IR56_MAGTAPE
Source
1 package body pay_hk_ir56_magtape AS
2 /* $Header: pyhk56mt.pkb 120.0.12010000.2 2008/09/01 07:24:30 jalin ship $
3 **
4 ** Copyright (c) 2000 Oracle Corporation
5 ** All Rights Reserved
6 **
7 **
8 **
9 ** Change List
10 ** ===========
11 **
12 ** Date Author Reference Description
13 ** -----------+--------+---------+-------------
14 ** 17 SEP 2001 APUNEKAR N/A Inital version.
15 ** 04 JUN 2002 APUNEKAR N/A Fixed for Bug#2397884.
16 ** 04 JUN 2002 APUNEKAR N/A Open eit cursor only if archive message is null
17 ** 02 DEC 2002 SRRAJAGO 2689229 Included 'nocopy' option for the 'OUT' parameter
18 ** of the procedure 'range_code'
19 ** 11 MAR 2003 SRRAJAGO 2829320 In the process_assignments cursor, included the join
20 ** paa.action_status = 'C' to prevent magtape fetching
21 ** the errored archive records.
22 ** 29 MAY 2003 KAVERMA 2920731 Replaced tables per_all_assignments_f and per_all_people_f
23 ** by secured views per_assignments_f and per_people_f from queries.
24 ** 28 AUG 2008 JALIN 7324233 In the process_assignments cursor,it should
25 ** lock the assignments which has magtape
26 ** generatedi, it should not lock with archive
27 */
28
29
30
31
32
33 /********************************************************
34 * Procedure to fetch RANGE CODE *
35 **********************************************************/
36 procedure range_code
37 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
38 p_sql out nocopy varchar2
39 )
40 is
41 Begin
42 hr_utility.set_location('Start of range_code',1);
43 p_sql := 'SELECT distinct person_id ' ||
44 'FROM per_people_f ppf, ' ||
45 'pay_payroll_actions ppa ' ||
46 'WHERE ppa.payroll_action_id = :payroll_action_id ' ||
47 'AND ppa.business_group_id = ppf.business_group_id ' ||
48 'ORDER BY ppf.person_id';
49 hr_utility.set_location('End of range_code',2);
50 End range_code;
51
52 /********************************************************
53 * Procedure to fetch ASSIGNMENT ACTION CODE *
54 **********************************************************/
55
56 PROCEDURE assignment_action_code
57 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE,
58 p_start_person_id in per_all_people_f.person_id%TYPE,
59 p_end_person_id in per_all_people_f.person_id%TYPE,
60 p_chunk in number)
61 IS
62 v_next_action_id pay_assignment_actions.assignment_action_id%TYPE;
63 v_run_action_id pay_assignment_actions.assignment_action_id%TYPE;
64 v_archive_action_id pay_assignment_actions.assignment_action_id%TYPE;
65 x_hk_archive_message varchar2(1000);
66
67 CURSOR next_action_id is
68 SELECT pay_assignment_actions_s.NEXTVAL
69 FROM dual;
70
71
72
73 ----------------------
74 --Define Cursor to check EIT
75
76 cursor check_eit
77 (c_assignment_id in pay_assignment_actions.assignment_id%type,
78 c_legal_entity_id in pay_assignment_actions.tax_unit_id%type,
79 c_reporting_year in number) is
80 SELECT paei.assignment_extra_info_id,
81 paei.assignment_id,
82 paei.aei_information1,
83 paei.aei_information4
84 FROM per_assignment_extra_info paei,
85 per_assignment_info_types pait
86 WHERE paei.information_type = 'HR_IR56B_REPORTING_INFO_HK'
87 AND paei.assignment_id = c_assignment_id
88 AND paei.information_type = pait.information_type
89 AND pait.active_inactive_flag = 'Y'
90 AND paei.aei_information1 = c_reporting_year
91 AND paei.aei_information4 = c_legal_entity_id;
92
93
94 --------------------
95 CURSOR process_assignments
96 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE,
97 c_start_person_id in per_all_people_f.person_id%TYPE,
98 c_end_person_id in per_all_people_f.person_id%TYPE)
99
100 IS
101
102 select distinct paaf.assignment_id,
103 paa.assignment_action_id,
104 pay_core_utils.get_parameter('ARCHIVE_ACTION_ID',ppa2.legislative_parameters) archive_action_id,
105 pay_core_utils.get_parameter('LEGAL_ENTITY_ID',ppa3.legislative_parameters) legal_entity_id,
106 pay_core_utils.get_parameter('REPORTING_YEAR',ppa3.legislative_parameters) reporting_year
107 from per_people_f papf,
108 per_assignments_f paaf,
109 pay_payroll_actions ppa, --magtape action
110 pay_payroll_actions ppa2,--report action
111 pay_payroll_actions ppa3,--archive action
112 pay_assignment_actions paa
113 where
114 ppa.payroll_action_id = c_payroll_action_id
115 and papf.person_id between c_start_person_id and c_end_person_id
116 and papf.person_id = paaf.person_id
117 and papf.business_group_id = ppa.business_group_id
118 and ppa3.payroll_action_id = paa.payroll_action_id
119 and paaf.assignment_id= paa.assignment_id
120 and ppa2.payroll_action_id = pay_core_utils.get_parameter('REPORT_ACTION_ID',ppa.legislative_parameters)
121 and ppa3.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ACTION_ID',ppa2.legislative_parameters)
122 and ppa3.action_type = 'X'
123 and ppa3.action_status = 'C'
124 and paa.action_status = 'C' /* Bug No : 2829320 - To prevent Magtape fetching errored archive records */
125 and not exists /* dont process locked assignments, bug 7324233, it should only lock the assignments which has magtape generated previously */
126 (select locked_action_id
127 from pay_action_interlocks pai,
128 pay_payroll_actions ppa1,
129 pay_payroll_actions ppa2,
130 pay_assignment_actions paa1
131 where pai.locked_action_id = paa.assignment_action_id
132 and ppa1.action_type='X'
133 and ppa1.report_type = 'HK_IR56B_MAGTAPE'
134 and ppa2.action_type='X'
135 and ppa2.action_status='C'
136 and ppa1.action_status='C'
137 and ppa2.report_type = 'HK_IR56B_REPORT'
138 and paa1.assignment_action_id = pai.locking_action_id
139 and ppa1.payroll_action_id = paa1.payroll_action_id
140 and ppa2.payroll_action_id = pay_core_utils.get_parameter('REPORT_ACTION_ID',
141 ppa1.legislative_parameters)
142 );
143
144
145 eit_rec check_eit%rowtype;
146 --------------------
147 Begin
148 hr_utility.set_location
149 ('Start of assignment_action_code '||
150 p_payroll_action_id || ':' ||
151 p_start_person_id || ':' ||
152 p_end_person_id,
153 3);
154
155
156 FOR process_rec IN process_assignments (p_payroll_action_id,
157 p_start_person_id,
158 p_end_person_id)
159 LOOP
160
161 x_hk_archive_message :=pay_hk_ir56_report.get_archive_value
162 ('X_HK_ARCHIVE_MESSAGE',process_rec.assignment_action_id);
163
164 if x_hk_archive_message is null then/*Bug#2397884-Check for message first and then for EIT*/
165
166 open check_eit(process_rec.assignment_id,
167 process_rec.legal_entity_id,
168 process_rec.reporting_year
169 );
170 fetch check_eit into eit_rec;
171
172 --- Make Sure an EIT exists
173 if check_eit%FOUND then
174
175
176 --* Get the new action id
177 OPEN next_action_id;
178 FETCH next_action_id INTO v_next_action_id;
179 CLOSE next_action_id;
180
181 hr_utility.set_location('Before calling hr_nonrun_asact.insact',4);
182
183 hr_nonrun_asact.insact(v_next_action_id,
184 process_rec.assignment_id,
185 p_payroll_action_id,
186 p_chunk,
187 null);
188
189 hr_utility.set_location('After calling hr_nonrun_asact.insact',4);
190
191
192 hr_nonrun_asact.insint(v_next_action_id,
193 process_rec.assignment_action_id
194 );
195 close check_eit;
196 else --check EIT NOT FOUND
197 close check_eit;
198 raise_application_error(-20001, 'Assignment : ' || process_rec.assignment_id || ' has been processed by IR56B Report but no EIT exists.');
199 end if;
200 end if;
201
202
203
204 END LOOP;
205 hr_utility.set_location('End of assignment_action_code',5);
206
207 Exception
208 When Others Then
209 If next_action_id%ISOPEN Then
210 CLOSE next_action_id;
211 End If;
212 If check_eit%ISOPEN Then
213 CLOSE check_eit;
214 End If;
215
216 hr_utility.set_location('Exception in assignment_action_code ',20);
217 RAISE;
218
219 End assignment_action_code;
220
221
222 end pay_hk_ir56_magtape;