1 package body pay_sg_iras_magtape as
2 /* $Header: pysgirmt.pkb 120.2.12010000.8 2009/06/24 05:15:40 jalin ship $ */
3 ---------------------------------------------------------------------------
4 -- These are PUBLIC procedures that are used within this package.
5 ---------------------------------------------------------------------------
6 ------------------------------------------------------------------------
7 -- The SELECT statement in this procedure returns the Person Ids for
8 -- Assignments that require the archive process to create an Assignment
9 -- Action.
10 -- Core Payroll recommend the SELECT has minimal restrictions.
11 ------------------------------------------------------------------------
12 procedure range_code
13 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
14 p_sql out nocopy varchar2) is
15 begin
16 hr_utility.set_location('Start of range_code',1);
17
18 p_sql := 'select distinct person_id ' ||
19 'from per_people_f ppf, ' ||
20 'pay_payroll_actions ppa ' ||
21 'where ppa.payroll_action_id = :payroll_action_id ' ||
22 'and ppa.business_group_id = ppf.business_group_id ' ||
23 'order by ppf.person_id';
24
25 hr_utility.set_location('End of range_code',2);
26 end range_code;
27
28 --------------------------------------------------------------------------
29 -- --
30 -- Name : GET_PARAMETERS --
31 -- Type : PROCEDURE --
32 -- Access : Private --
33 -- Description : This procedure determines the globals applicable --
34 -- through out the tenure of the process --
35 -- Parameters : --
36 -- IN : p_payroll_action_id NUMBER --
37 -- p_token_name VARCHAR2 --
38 -- OUT : p_token_value VARCHAR2 --
39 -- --
40 -- Change History : --
41 --------------------------------------------------------------------------
42 -- Rev# Date Userid Description --
43 --------------------------------------------------------------------------
44 -- 115.0 5-Jan-2006 lnagaraj Initial Version --
45 --------------------------------------------------------------------------
46 PROCEDURE get_parameters(p_payroll_action_id IN NUMBER,
47 p_token_name IN VARCHAR2,
48 p_token_value OUT NOCOPY VARCHAR2)
49 IS
50
51 CURSOR csr_parameter_info(p_pact_id NUMBER,
52 p_token CHAR) IS
53 SELECT SUBSTR(legislative_parameters,
54 INSTR(legislative_parameters,p_token)+(LENGTH(p_token)+1),
55 INSTR(legislative_parameters,' ',
56 INSTR(legislative_parameters,p_token))
57 - (INSTR(legislative_parameters,p_token)+LENGTH(p_token)))
58 ,business_group_id
59 FROM pay_payroll_actions
60 WHERE payroll_action_id = p_pact_id;
61
62 l_token_value VARCHAR2(150);
63 l_bg_id NUMBER;
64 l_proc VARCHAR2(100);
65 l_message VARCHAR2(255);
66
67 BEGIN
68
69 OPEN csr_parameter_info(p_payroll_action_id,
70 p_token_name);
71 FETCH csr_parameter_info INTO l_token_value,l_bg_id;
72 CLOSE csr_parameter_info;
73
74
75 p_token_value := TRIM(l_token_value);
76
77
78 IF (p_token_name = 'BG_ID') THEN
79 p_token_value := l_bg_id;
80 END IF;
81
82 IF (p_token_value IS NULL) THEN
83 p_token_value := '%';
84 END IF;
85
86
87 END get_parameters;
88
89 ------------------------------------------------------------------------
90 -- This procedure is used to restrict the Assignment Action Creation.
91 -- It calls the procedure that actually inserts the Assignment Actions.
92 -- The cursor selects the assignments for people who have been archived
93 -- for the Archive Run.
94 -- The archive assignment action is then locked by this (magtape)
95 -- assignment action. This is done so that the archive can not be
96 -- rolled back without first rolling back the magtape.
97 ------------------------------------------------------------------------
98 procedure assignment_action_code
99 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
100 p_start_person_id in per_all_people_f.person_id%type,
101 p_end_person_id in per_all_people_f.person_id%type,
102 p_chunk in number) is
103
104 v_next_action_id pay_assignment_actions.assignment_action_id%type;
105 v_archive_action_id pay_assignment_actions.assignment_action_id%type;
106 v_type ff_database_items.user_name%type;
107
108 cursor next_action_id is
109 select pay_assignment_actions_s.nextval
110 from dual;
111
112 cursor process_assignments
113 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
114 c_start_person_id in per_all_people_f.person_id%type,
115 c_end_person_id in per_all_people_f.person_id%type,
116 c_type in ff_database_items.user_name%type) is
117 select distinct a.assignment_id,
118 pay_core_utils.get_parameter('ARCHIVE_RUN_ID', pa.legislative_parameters) archive_run_id,
119 pay_core_utils.get_parameter('LEGAL_ENTITY_ID', pa.legislative_parameters) legal_entity_id
120 from per_assignments_f a, /* Bug# 2920732 */
121 per_people_f p,
122 pay_payroll_actions pa
123 where pa.payroll_action_id = c_payroll_action_id
124 and p.person_id between c_start_person_id and c_end_person_id
125 and p.person_id = a.person_id
126 and p.business_group_id = pa.business_group_id
127 and exists /* Bug No : 2242653 */
128 (select null
129 from
130 pay_payroll_actions ppa,
131 pay_assignment_actions pac
132 where ppa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_RUN_ID', pa.legislative_parameters)
133 and ppa.action_type = 'X'
134 and ppa.action_status = 'C'
135 and ppa.payroll_action_id = pac.payroll_action_id
136 and pac.assignment_id = a.assignment_id
137 )
138 and ((g_report_type = 'O' and not exists/*Bug:2858063*/
139 (
140 select pai.locking_action_id
141 from
142 pay_action_interlocks pai,
143 pay_assignment_actions paa1,--assignment action id of the action that locks archive
144 pay_assignment_actions paa2, -- assignment action id of the magtape
145 pay_payroll_actions ppa1, -- payroll action id of the process that locks archive
146 pay_payroll_actions ppa2 -- payroll action id of the magtape process
147 where pai.locked_action_id = paa1.assignment_action_id -- archive is locked
148 and pai.locking_action_id = paa2.assignment_action_id -- mgtape is looking the archive
149 and paa1.assignment_id = paa2.assignment_id
150 and paa1.assignment_id = a.assignment_id
151 and ppa1.action_type = 'X'
152 and ppa1.action_status = 'C'
153 and ppa1.report_type = 'SG_IRAS_ARCHIVE'
154 and ppa2.action_type = 'X'
155 and ppa2.action_status = 'C'
156 and ppa2.report_type = pa.report_type /* Bug#2833530 */
157 and pay_core_utils.get_parameter('LEGAL_ENTITY_ID', pa.legislative_parameters) = pay_core_utils.get_parameter('LEGAL_ENTITY_ID', ppa2.legislative_parameters) /* Bug 8240839 */
158 and ppa1.payroll_action_id = paa1.payroll_action_id
159 and ppa2.payroll_action_id = paa2.payroll_action_id
160 and ppa2.report_qualifier='SG'
161 and to_char(ppa2.effective_date, 'YYYY') =
162 (
163 select
164 pay_core_utils.get_parameter('BASIS_YEAR', ppa_arch.legislative_parameters)
165 from
166 pay_payroll_actions ppa_arch
167 where
168 ppa_arch.payroll_action_id =
169 pay_core_utils.get_parameter('ARCHIVE_RUN_ID', pa.legislative_parameters)
170 ) /* Bug#4888368 */
171 ))
172 or (g_report_type='A' AND exists (select '' from
173 pay_assignment_actions aacs,
174 pay_payroll_Actions ppas,
175 ff_archive_items ffis,
176 ff_database_items fdis
177 where ffis.context1 = aacs.assignment_action_id
178 and a.assignment_id = aacs.assignment_id
179 and aacs.payroll_action_id = ppas.payroll_action_id
180 and fdis.user_name = c_type
181 AND ffis.VALUE = 'Y'
182 and ppas.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_RUN_ID', pa.legislative_parameters)
183 and ppas.action_type = 'X'
184 and ppas.action_status = 'C'
185 and ffis.user_entity_id = fdis.user_entity_id )))
186 and not exists
187 (select null
188 from per_people_extra_info pei
189 where pei.person_id = p.person_id
190 and pei.pei_information1 is not null
191 and pei.information_type = 'HR_IR21_PROCESSING_DATES_SG');
192
193 cursor locked_action
194 (c_payroll_action_id pay_assignment_actions.payroll_action_id%type,
195 c_assignment_id pay_assignment_actions.assignment_id%type) is
196 select pac.assignment_action_id
197 from pay_assignment_actions pac
198 where pac.assignment_id = c_assignment_id
199 and pac.payroll_action_id = c_payroll_action_id;
200
201 begin
202 hr_utility.set_location('Start of assignment_action_code',3);
203
204 initialization_code(p_payroll_action_id);
205
206 select decode(g_file,'IR8A', 'X_IR8A_AMEND_INDICATOR','IR8S', 'X_IR8S_AMEND_INDICATOR','A8A','X_A8A_AMEND_INDICATOR','A8B','X_A8B_AMEND_INDICATOR')
207 into v_type from dual;
208
209 for process_rec in process_assignments (p_payroll_action_id,
210 p_start_person_id,
211 p_end_person_id,
212 v_type) loop
213 open next_action_id;
214 fetch next_action_id into v_next_action_id;
215 close next_action_id;
216
217 hr_utility.set_location('Before calling hr_nonrun_asact.insact',4);
218
219 pay_balance_pkg.set_context('TAX_UNIT_ID',process_rec.legal_entity_id);
220 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',v_next_action_id);
221
222 hr_nonrun_asact.insact(v_next_action_id, -- lockingactid
223 process_rec.assignment_id, -- assignid
224 p_payroll_action_id, -- pactid
225 p_chunk, -- chunk
226 process_rec.legal_entity_id); -- greid
227
228 open locked_action (process_rec.archive_run_id, process_rec.assignment_id);
229 fetch locked_action into v_archive_action_id;
230 if locked_action%found then
231 close locked_action;
232 hr_nonrun_asact.insint(v_next_action_id, -- locking action id
233 v_archive_action_id); -- locked action id
234 else
235 close locked_action;
236 end if;
237 hr_utility.set_location('After calling hr_nonrun_asact.insact',4);
238
239 end loop;
240
241 hr_utility.set_location('End of assignment_action_code',5);
242 end assignment_action_code;
243 ------------------------------------------------------------------------
244 -- This is used by legislation groups to set global contexts that are
245 -- required for the lifetime of the archiving process. This is null
246 -- because there are no setup requirements, but a procedure needs to
247 -- exist in pay_report_format_mappings_f, otherwise the archiver will
248 -- assume that no archival of data is required.
249 ------------------------------------------------------------------------
250 procedure initialization_code
251 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
252 begin
253 hr_utility.set_location('Start of initialization_code',6);
254 get_parameters(p_payroll_action_id,'REP_TYPE',g_report_type);
255 get_parameters(p_payroll_action_id,'FILE',g_file);
256 hr_utility.set_location('End of initialization_code',7);
257 end initialization_code;
258 ------------------------------------------------------------------------
259 -- Used to actually perform the archival of data. We are not archiving
260 -- any data here, so this is null.
261 ------------------------------------------------------------------------
262 procedure archive_code
263 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
264 p_effective_date in date) is
265
266 begin
267 hr_utility.set_location('Start of archive_code',8);
268 null;
269 hr_utility.set_location('End of archive_code',9);
270 end archive_code;
271
272 end pay_sg_iras_magtape;
[Home] [Help]