1 package pay_au_payment_summary_magtape AUTHID CURRENT_USER as
2 /* $Header: pyaupsm.pkh 120.8 2010/06/10 06:08:48 skshin noship $*/
3
4 level_cnt number;
5
6 ---------------------------------------------------------------------------+
7 -- These are PUBLIC procedures are required by the Archive/Magtape process.
8 -- Their names are stored in PAY_REPORT_FORMAT_MAPPINGS_F so that
9 -- the archive process knows what code to execute for each step of
10 -- the archive.
11 -----------------------------------------------------------------------------+
12
13 procedure range_code
14 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
15 p_sql out NOCOPY varchar2);
16
17 procedure assignment_action_code
18 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
19 p_start_person_id in per_all_people_f.person_id%type,
20 p_end_person_id in per_all_people_f.person_id%type,
21 p_chunk in number);
22
23 procedure initialization_code
24 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type);
25
26 procedure archive_code
27 (p_payroll_action_id in pay_assignment_actions.payroll_action_id%type,
28 p_effective_date in date);
29
30
31 --------------------------------------------------------------------+
32 -- These cursors are used to retrieve data and pass it to formulas.
33 --------------------------------------------------------------------+
34
35 --------------------------------------------------------------------+
36 -- PUBLIC cursor to select Supplier data.
37 --------------------------------------------------------------------+
38 cursor supplier_details_val IS -- (Bug 3132178) Created to be called for spawned process
39 select 'ASSIGNMENT_ACTION_ID=C',
40 ppac.assignment_action_id ps_report_id,
41 'TEST_FLAG=P',
42 'Y'
43 from pay_assignment_actions ppac,
44 pay_payroll_actions ppa
45 where ppa.payroll_action_id =pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
46 and ppac.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_PAYROLL_ACTION',ppa.legislative_parameters)
47 AND pay_au_payment_summary.get_archive_value('X_REPORTING_FLAG', ppac.assignment_action_id)='YES'
48 AND pay_au_payment_summary.get_archive_value('X_CURR_TERM_0_BAL_FLAG',ppac.assignment_action_id)='NO' /* Added for bug 5257622 */
49 and ppac.action_status = 'C'
50 and ppa.report_type = 'AU_PS_DATA_FILE_VAL'
51 and ppa.report_qualifier = 'AU'
52 and ppa.report_category = 'REPORT'
53 and rownum=1;
54
55
56 --(Bug 3132178) Added TEST_FLAG to the cursor which is set to N i.e. this cursor creates Production EFILE
57 cursor supplier_details is
58 select 'ASSIGNMENT_ACTION_ID=C',
59 apac.assignment_action_id ps_report_id,
60 'TEST_FLAG=P',
61 'N'
62 from pay_assignment_actions apac,
63 pay_assignment_actions ppac,
64 pay_payroll_actions ppa,
65 pay_assignment_actions mpac,
66 pay_action_interlocks ppai,
67 pay_action_interlocks mpai
68 where apac.assignment_action_id = ppai.locked_action_id
69 and mpac.assignment_Action_id = mpai.locking_action_id
70 and ppa.payroll_action_id = ppac.payroll_Action_id
71 and apac.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ID',ppa.legislative_parameters)
72 and ppac.assignment_action_id = mpai.locked_action_id
73 and mpac.payroll_action_id =pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
74 and apac.action_status = 'C'
75 and ppac.action_status = 'C' /* Bug No : 3288757 */
76 /*Bug 3400521 - Added the following joins to improve the performance*/
77 and ppa.action_status = 'C'
78 and ppa.report_type = 'AU_PAYMENT_SUMMARY_REPORT'
79 and ppa.report_qualifier = 'AU'
80 and ppa.report_category = 'REPORT'
81 and rownum=1;
82
83 --------------------------------------------------------------------+
84 -- PUBLIC cursor to select Legal Employer data.
85 --------------------------------------------------------------------+
86 cursor employer_details_val is -- (Bug 3132178) Created to be called for spawned process
87 select * from
88 (
89 select 'ASSIGNMENT_ACTION_ID=C',
90 ppac.assignment_action_id ps_report_id,
91 'FINANCIAL_YEAR_END=P',
92 substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa.legislative_parameters),6,4),
93 'FINANCIAL_YEAR_START=P',
94 substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa.legislative_parameters),1,4)
95 /*Bug 6630375 - removed ETP_PAYER_TYPE */
96 -- ,'ETP_PAYER_TYPE=P',
97 -- ,decode(fue.user_entity_name,'X_LUMP_SUM_C_DEDUCTIONS_ASG_YTD',decode(value ,0,null ,null,null, 'P')) etp_payer_type
98 from
99 pay_assignment_actions ppac,
100 pay_payroll_actions ppa,
101 ff_archive_items ff,
102 ff_user_entities fue
103 where ppa.payroll_action_id =pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
104 and ppac.action_status='C'
105 and ppac.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_PAYROLL_ACTION',ppa.legislative_parameters)
106 AND pay_au_payment_summary.get_archive_value('X_REPORTING_FLAG', ppac.assignment_action_id)='YES'
107 AND pay_au_payment_summary.get_archive_value('X_CURR_TERM_0_BAL_FLAG',ppac.assignment_action_id)='NO' /* Added for bug 5257622 */
108 and fue.user_Entity_id= ff.user_entity_id
109 and ff.context1= ppac.assignment_action_id
110 and ppa.report_type = 'AU_PS_DATA_FILE_VAL'
111 and ppa.report_qualifier = 'AU'
112 and ppa.report_category = 'REPORT'
113 -- order by etp_payer_type
114 )
115 where rownum=1;
116
117
118 cursor employer_details is
119 select * from
120 (
121 select /*+ ORDERED */ 'ASSIGNMENT_ACTION_ID=C',
122 apac.assignment_action_id ps_report_id,
123 'FINANCIAL_YEAR_END=P',
124 substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa.legislative_parameters),6,4),
125 'FINANCIAL_YEAR_START=P',
126 substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa.legislative_parameters),1,4)
127 /*Bug 6630375 - removed ETP_PAYER_TYPE */
128 -- ,'ETP_PAYER_TYPE=P',
129 -- ,decode(fue.user_entity_name,'X_LUMP_SUM_C_DEDUCTIONS_ASG_YTD',decode(value ,0,null ,null,null, 'P')) etp_payer_type
130 from
131 pay_assignment_actions mpac, --magtape
132 pay_action_interlocks mpai,
133 pay_assignment_actions ppac, --self printed
134 pay_action_interlocks ppai,
135 pay_assignment_actions apac, --archive
136 pay_payroll_actions ppa, --self printed
137 ff_archive_items ff,
138 ff_user_entities fue
139 where mpac.payroll_action_id =pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
140 and mpac.action_status ='C'
141 and ppac.action_status='C'
142 and mpai.locking_action_id = mpac.assignment_Action_id -- magtapes locking action id
143 and ppac.assignment_action_id = mpai.locked_action_id --self printed locked by maghtape
144 and ppai.locking_Action_id =ppac.assignment_action_id -- self printed has locked archive
145 and apac.assignment_action_id=ppai.locked_action_id --archive actionslocked by self printed
146 and apac.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ID',ppa.legislative_parameters)
147 and ppa.payroll_action_id = ppac.payroll_Action_id --payroll actions for self printed
148 and ppa.report_type='AU_PAYMENT_SUMMARY_REPORT'
149 and apac.assignment_id = ppac.assignment_id
150 and ppac.assignment_id = mpac.assignment_id
151 and fue.user_Entity_id= ff.user_entity_id
152 and ff.context1= apac.assignment_action_id
153 -- order by etp_payer_type
154 )
155 where rownum=1;
156 --------------------------------------------------------------------+
157 -- PUBLIC cursor to select Employee Payment summary data.
158 --------------------------------------------------------------------+
159 /* Bug #6630375 - Added Check for Assignment ID in both ARCHIVE and Magtape assignment actions */
160
161 cursor payment_summary_data_val is -- (Bug 3132178) Created to be called for spawned process
162 select 'ASSIGNMENT_ACTION_ID=C',
163 ppac.assignment_action_id ps_report_id
164 from pay_assignment_actions ppac,
165 pay_payroll_actions mpa,
166 pay_assignment_actions mpac
167 where mpa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
168 AND mpac.payroll_action_id = mpa.payroll_action_id /* Added for bug 6630375 */
169 AND mpac.assignment_id = ppac.assignment_id /* Added for bug 6630375 */
170 AND ppac.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_PAYROLL_ACTION',mpa.legislative_parameters)
171 AND pay_au_payment_summary.get_archive_value('X_REPORTING_FLAG', ppac.assignment_action_id)='YES'
172 AND pay_au_payment_summary.get_archive_value('X_CURR_TERM_0_BAL_FLAG',ppac.assignment_action_id)='NO' /* Added for bug 5257622 */
173 and ppac.action_status = 'C'
174 and mpa.report_type = 'AU_PS_DATA_FILE_VAL'
175 and mpa.report_qualifier = 'AU'
176 and mpa.report_category = 'REPORT'
177 /* Added check for bug 5353402 - Details will be displayed in the Data File only if sum of values of these balances are greater than zero */
178 and 1 <= (select sum(nvl(value,0))
179 from ff_user_entities fue, ff_archive_items ff
180 where fue.user_Entity_id = ff.user_entity_id
181 and fue.user_entity_name in
182 ('X_ALLOWANCE_1_ASG_YTD','X_ALLOWANCE_2_ASG_YTD','X_ALLOWANCE_3_ASG_YTD',
183 'X_ALLOWANCE_4_ASG_YTD','X_FRINGE_BENEFITS_ASG_YTD',
184 'X_CDEP_ASG_YTD','X_EARNINGS_TOTAL_ASG_YTD','X_WORKPLACE_DEDUCTIONS_ASG_YTD',
185 'X_LUMP_SUM_A_DEDUCTION_ASG_YTD','X_LUMP_SUM_A_PAYMENTS_ASG_YTD',
186 'X_LUMP_SUM_B_DEDUCTION_ASG_YTD','X_LUMP_SUM_B_PAYMENTS_ASG_YTD',
187 'X_LUMP_SUM_D_PAYMENTS_ASG_YTD','X_LUMP_SUM_E_PAYMENTS_ASG_YTD',
188 'X_TOTAL_TAX_DEDUCTIONS_ASG_YTD','X_UNION_FEES_ASG_YTD',
189 'X_RPT_EMPLOYER_SUPERANN_CONTR_ASG_YTD','X_EXEMPT_FOREIGN_EMPLOY_INC_ASG_YTD') /*bug9793447*/
190 and ff.context1= ppac.assignment_action_id
191 )
192 group by ppac.assignment_action_id;
193
194 cursor payment_summary_data is /* 5471093 */
195 select /*+ ORDERED */ 'ASSIGNMENT_ACTION_ID=C',
196 apac.assignment_action_id ps_report_id
197 from pay_assignment_actions mpac,
198 pay_action_interlocks mpai,
199 pay_assignment_actions ppac,
200 pay_action_interlocks ppai,
201 pay_assignment_actions apac,
202 pay_payroll_actions ppa
203 where ppa.payroll_action_id = ppac.payroll_Action_id
204 and apac.assignment_action_id = ppai.locked_action_id
205 and apac.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ID',ppa.legislative_parameters)
206 and ppai.locking_Action_id = ppac.assignment_action_id /* 5471093 */
207 and ppac.assignment_action_id = mpai.locked_action_id
208 and mpac.assignment_Action_id = mpai.locking_action_id
209 and mpac.payroll_action_id =pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
210 and apac.action_status = 'C'
211 and ppac.action_status = 'C' /* Bug No : 3288757 */
212 /*Bug 3400521 - Added the following joins to improve the performance*/
213 and mpac.action_status = 'C'
214 and ppa.action_status = 'C'
215 and ppa.report_type = 'AU_PAYMENT_SUMMARY_REPORT'
216 and ppa.report_qualifier = 'AU'
217 and ppa.report_category = 'REPORT'
218 and apac.assignment_id = ppac.assignment_id /* 5471093 */
219 and ppac.assignment_id = mpac.assignment_id /* 5471093 */
220 /* Added check for bug 5353402 - Details will be displayed in the Data File only if sum of values of these balances are greater than zero */
221 and 1 <= (select sum(nvl(value,0))
222 from ff_user_entities fue, ff_archive_items ff
223 where fue.user_Entity_id = ff.user_entity_id
224 and fue.user_entity_name in
225 ('X_ALLOWANCE_1_ASG_YTD','X_ALLOWANCE_2_ASG_YTD','X_ALLOWANCE_3_ASG_YTD',
226 'X_ALLOWANCE_4_ASG_YTD','X_FRINGE_BENEFITS_ASG_YTD',
227 'X_CDEP_ASG_YTD','X_EARNINGS_TOTAL_ASG_YTD','X_WORKPLACE_DEDUCTIONS_ASG_YTD',
228 'X_LUMP_SUM_A_DEDUCTION_ASG_YTD','X_LUMP_SUM_A_PAYMENTS_ASG_YTD',
229 'X_LUMP_SUM_B_DEDUCTION_ASG_YTD','X_LUMP_SUM_B_PAYMENTS_ASG_YTD',
230 'X_LUMP_SUM_D_PAYMENTS_ASG_YTD','X_LUMP_SUM_E_PAYMENTS_ASG_YTD',
231 'X_TOTAL_TAX_DEDUCTIONS_ASG_YTD','X_UNION_FEES_ASG_YTD',
232 'X_RPT_EMPLOYER_SUPERANN_CONTR_ASG_YTD','X_EXEMPT_FOREIGN_EMPLOY_INC_ASG_YTD') /*bug9793447*/
233 and ff.context1= apac.assignment_action_id
234 )
235 group by apac.assignment_action_id;
236
237 --------------------------------------------------------------------+
238 -- PUBLIC cursor to select Employee ETP Payment Summary data.
239 --------------------------------------------------------------------+
240 /* Bug #2113363 - In the User Entity Name, add X_PRE_JUL_83_COMPONENT_ASG_YTD,
241 X_POST_JUN_83_UNTAXED_ASG_YTD, X_POST_JUN_83_UNTAXED_ASG_YTD,X_INVALIDITY_PAYMENTS_ASG_YTD */
242 /* Bug #2581412 - Used ORDERED hint and added few joins to improve performance */
243 /* Bug #4570012 - Added Check for Employee Type in Cursor to ensure only terminated employees are picked */
244 /* Bug #6630375 - Added Check for Assignment ID in both ARCHIVE and Magtape assignment actions */
245
246 cursor etp_payment_summary_data_val IS -- (Bug 3132178) Created to be called for spawned process
247 select 'ASSIGNMENT_ACTION_ID=C',
248 ppac.assignment_action_id ps_report_id
249 from pay_assignment_actions ppac,
250 pay_payroll_actions mpa,
251 pay_assignment_actions mpac
252 where mpa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
253 AND mpac.payroll_action_id = mpa.payroll_action_id /* Added for bug 6630375 */
254 AND mpac.assignment_id = ppac.assignment_id /* Added for bug 6630375 */
255 AND ppac.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_PAYROLL_ACTION',mpa.legislative_parameters)
256 AND pay_au_payment_summary.get_archive_value('X_REPORTING_FLAG', ppac.assignment_action_id)='YES'
257 AND pay_au_payment_summary.get_archive_value('X_CURR_TERM_0_BAL_FLAG',ppac.assignment_action_id)='NO' /* Added for bug 5257622 */
258 and pay_au_payment_summary.get_archive_value('X_SORT_EMPLOYEE_TYPE',ppac.assignment_action_id) = 'T'
259 and ppac.action_status = 'C'
260 and mpa.report_type = 'AU_PS_DATA_FILE_VAL'
261 and mpa.report_qualifier = 'AU'
262 and mpa.report_category = 'REPORT'
263 and 1 <= (select sum(nvl(value,0))
264 from ff_user_entities fue, ff_archive_items ff
265 where fue.user_Entity_id = ff.user_entity_id
266 and fue.user_entity_name in
267 ('X_LUMP_SUM_C_DEDUCTIONS_ASG_YTD',
268 'X_PRE_JUL_83_COMPONENT_ASG_YTD',
269 'X_POST_JUN_83_UNTAXED_ASG_YTD',
270 'X_POST_JUN_83_TAXED_ASG_YTD',
271 'X_INVALIDITY_PAYMENTS_ASG_YTD')
272 and ff.context1= ppac.assignment_action_id
273 )
274 group by ppac.assignment_action_id;
275
276 /* Bug #4570012 - Added Check for Employee Type in Cursor to ensure only terminated employees are picked */
277
278 cursor etp_payment_summary_data is
279 select /*+ORDERED*/ 'ASSIGNMENT_ACTION_ID=C',
280 apac.assignment_action_id ps_report_id
281 from
282 pay_assignment_actions mpac, -- Magtape
283 pay_action_interlocks mpai,
284 pay_assignment_actions ppac, --Self Printed
285 pay_action_interlocks ppai,
286 pay_assignment_actions apac, --Archive
287 pay_payroll_actions ppa --Self Printed
288 where mpac.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
289 and mpac.action_status = 'C'
290 and ppac.action_status = 'C'
291 and mpac.assignment_Action_id = mpai.locking_action_id
292 and ppac.assignment_action_id = mpai.locked_action_id
293 and ppai.locking_Action_id = ppac.assignment_action_id
294 and apac.assignment_action_id = ppai.locked_action_id
295 and apac.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ID',ppa.legislative_parameters)
296 and pay_au_payment_summary.get_archive_value('X_SORT_EMPLOYEE_TYPE',apac.assignment_action_id) = 'T'
297 and ppa.payroll_action_id = ppac.payroll_Action_id
298 and ppa.report_type = 'AU_PAYMENT_SUMMARY_REPORT'
299 and apac.assignment_id = ppac.assignment_id
300 and ppac.assignment_id = mpac.assignment_id
301 and 1 <= (select sum(nvl(value,0))
302 from ff_user_entities fue, ff_archive_items ff
303 where fue.user_Entity_id = ff.user_entity_id
304 and fue.user_entity_name in
305 ('X_LUMP_SUM_C_DEDUCTIONS_ASG_YTD',
306 'X_PRE_JUL_83_COMPONENT_ASG_YTD',
307 'X_POST_JUN_83_UNTAXED_ASG_YTD',
308 'X_POST_JUN_83_TAXED_ASG_YTD',
309 'X_INVALIDITY_PAYMENTS_ASG_YTD')
310 and ff.context1= apac.assignment_action_id
311 )
312 group by apac.assignment_action_id;
313
314
315 end pay_au_payment_summary_magtape;
[Home] [Help]