1 package body pay_sg_iras_magtape as
2 /* $Header: pysgirmt.pkb 120.2 2006/10/25 00:58:40 jalin noship $ */
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 -- This procedure is used to restrict the Assignment Action Creation.
29 -- It calls the procedure that actually inserts the Assignment Actions.
30 -- The cursor selects the assignments for people who have been archived
31 -- for the Archive Run.
32 -- The archive assignment action is then locked by this (magtape)
33 -- assignment action. This is done so that the archive can not be
34 -- rolled back without first rolling back the magtape.
35 ------------------------------------------------------------------------
36 procedure assignment_action_code
37 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
38 p_start_person_id in per_all_people_f.person_id%type,
39 p_end_person_id in per_all_people_f.person_id%type,
40 p_chunk in number) is
41
42 v_next_action_id pay_assignment_actions.assignment_action_id%type;
43 v_archive_action_id pay_assignment_actions.assignment_action_id%type;
44
45 cursor next_action_id is
46 select pay_assignment_actions_s.nextval
47 from dual;
48
49 cursor process_assignments
50 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
51 c_start_person_id in per_all_people_f.person_id%type,
52 c_end_person_id in per_all_people_f.person_id%type) is
53 select distinct a.assignment_id,
54 pay_core_utils.get_parameter('ARCHIVE_RUN_ID', pa.legislative_parameters) archive_run_id,
55 pay_core_utils.get_parameter('LEGAL_ENTITY_ID', pa.legislative_parameters) legal_entity_id
56 from per_assignments_f a, /* Bug# 2920732 */
57 per_people_f p,
58 pay_payroll_actions pa
59 where pa.payroll_action_id = c_payroll_action_id
60 and p.person_id between c_start_person_id and c_end_person_id
61 and p.person_id = a.person_id
62 and p.business_group_id = pa.business_group_id
63 and exists /* Bug No : 2242653 */
64 (select null
65 from
66 pay_payroll_actions ppa,
67 pay_assignment_actions pac
68 where ppa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_RUN_ID', pa.legislative_parameters)
69 and ppa.action_type = 'X'
70 and ppa.action_status = 'C'
71 and ppa.payroll_action_id = pac.payroll_action_id
72 and pac.assignment_id = a.assignment_id
73 )
74 and not exists/*Bug:2858063*/
75 (
76 select pai.locking_action_id
77 from
78 pay_action_interlocks pai,
79 pay_assignment_actions paa1,--assignment action id of the action that locks archive
80 pay_assignment_actions paa2, -- assignment action id of the magtape
81 pay_payroll_actions ppa1, -- payroll action id of the process that locks archive
82 pay_payroll_actions ppa2 -- payroll action id of the magtape process
83 where pai.locked_action_id = paa1.assignment_action_id -- archive is locked
84 and pai.locking_action_id = paa2.assignment_action_id -- mgtape is looking the archive
85 and paa1.assignment_id = paa2.assignment_id
86 and paa1.assignment_id = a.assignment_id
87 and ppa1.action_type = 'X'
88 and ppa1.action_status = 'C'
89 and ppa1.report_type = 'SG_IRAS_ARCHIVE'
90 and ppa2.action_type = 'X'
91 and ppa2.action_status = 'C'
92 and ppa2.report_type = pa.report_type /* Bug#2833530 */
93 and ppa1.payroll_action_id = paa1.payroll_action_id
94 and ppa2.payroll_action_id = paa2.payroll_action_id
95 and ppa2.report_qualifier='SG'
96 and to_char(ppa2.effective_date, 'YYYY') =
97 (
98 select
99 pay_core_utils.get_parameter('BASIS_YEAR', ppa_arch.legislative_parameters)
100 from
101 pay_payroll_actions ppa_arch
102 where
103 ppa_arch.payroll_action_id =
104 pay_core_utils.get_parameter('ARCHIVE_RUN_ID', pa.legislative_parameters)
105 ) /* Bug#4888368 */
106 )
107 and not exists
108 (select null
109 from per_people_extra_info pei
110 where pei.person_id = p.person_id
111 and pei.pei_information1 is not null
112 and pei.information_type = 'HR_IR21_PROCESSING_DATES_SG');
113
114 cursor locked_action
115 (c_payroll_action_id pay_assignment_actions.payroll_action_id%type,
116 c_assignment_id pay_assignment_actions.assignment_id%type) is
117 select pac.assignment_action_id
118 from pay_assignment_actions pac
119 where pac.assignment_id = c_assignment_id
120 and pac.payroll_action_id = c_payroll_action_id;
121
122 begin
123 hr_utility.set_location('Start of assignment_action_code',3);
124
125 for process_rec in process_assignments (p_payroll_action_id,
126 p_start_person_id,
127 p_end_person_id) loop
128 open next_action_id;
129 fetch next_action_id into v_next_action_id;
130 close next_action_id;
131
132 hr_utility.set_location('Before calling hr_nonrun_asact.insact',4);
133
134 pay_balance_pkg.set_context('TAX_UNIT_ID',process_rec.legal_entity_id);
135 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',v_next_action_id);
136
137 hr_nonrun_asact.insact(v_next_action_id, -- lockingactid
138 process_rec.assignment_id, -- assignid
139 p_payroll_action_id, -- pactid
140 p_chunk, -- chunk
141 process_rec.legal_entity_id); -- greid
142
143 open locked_action (process_rec.archive_run_id, process_rec.assignment_id);
144 fetch locked_action into v_archive_action_id;
145 if locked_action%found then
146 close locked_action;
147 hr_nonrun_asact.insint(v_next_action_id, -- locking action id
148 v_archive_action_id); -- locked action id
149 else
150 close locked_action;
151 end if;
152 hr_utility.set_location('After calling hr_nonrun_asact.insact',4);
153
154 end loop;
155
156 hr_utility.set_location('End of assignment_action_code',5);
157 end assignment_action_code;
158 ------------------------------------------------------------------------
159 -- This is used by legislation groups to set global contexts that are
160 -- required for the lifetime of the archiving process. This is null
161 -- because there are no setup requirements, but a procedure needs to
162 -- exist in pay_report_format_mappings_f, otherwise the archiver will
163 -- assume that no archival of data is required.
164 ------------------------------------------------------------------------
165 procedure initialization_code
166 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
167 begin
168 hr_utility.set_location('Start of initialization_code',6);
169 null;
170 hr_utility.set_location('End of initialization_code',7);
171 end initialization_code;
172 ------------------------------------------------------------------------
173 -- Used to actually perform the archival of data. We are not archiving
174 -- any data here, so this is null.
175 ------------------------------------------------------------------------
176 procedure archive_code
177 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
178 p_effective_date in date) is
179
180 begin
181 hr_utility.set_location('Start of archive_code',8);
182 null;
183 hr_utility.set_location('End of archive_code',9);
184 end archive_code;
185 ---------------------------------------------------------------------------
186 -- 5616519, This function is not needed anymore.
187 ---------------------------------------------------------------------------
188 -- function return_indicator_flag( p_assignment_id in number,
189 -- p_report_type in varchar2 ) return varchar2 is
190 -- cursor csr_indicator_flag is
191 -- Select
192 -- DECODE( p_report_type,'SG_A8A',nvl(pae.aei_information2,'N'),
193 -- 'SG_IR8A',nvl(pae.aei_information3,'N'),'SG_IR8S',nvl(pae.aei_information1,'N'),'N')
194 -- from per_assignment_extra_info pae
195 -- Where pae.assignment_id = p_assignment_id
196 -- and pae.information_type = 'IRAS_MAGTAPE_INFO_SG'
197 -- and pae.aei_information_category = 'IRAS_MAGTAPE_INFO_SG' ;
198 --
199 -- l_indicator_flag varchar2(1);
200 -- indicator_flag varchar2(1);
201 --
202 -- begin
203 -- l_indicator_flag := 'N';
204 -- indicator_flag := 'N';
205 --
206 -- open csr_indicator_flag;
207 -- fetch csr_indicator_flag into l_indicator_flag ;
208 --
209 -- if csr_indicator_flag%FOUND then
210 -- indicator_flag := l_indicator_flag ;
211 -- end if;
212 --
213 -- close csr_indicator_flag;
214 -- return indicator_flag;
215 -- end return_indicator_flag;
216
217 end pay_sg_iras_magtape;
[Home] [Help]