1 package pay_au_payment_summary_magtape as
2 /* $Header: pyaupsm.pkh 120.6.12010000.2 2008/08/06 06:50:56 ubhat ship $*/
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_OTHER_INCOME_ASG_YTD','X_UNION_FEES_ASG_YTD')
189 and ff.context1= ppac.assignment_action_id
190 )
191 group by ppac.assignment_action_id;
192
193 cursor payment_summary_data is /* 5471093 */
194 select /*+ ORDERED */ 'ASSIGNMENT_ACTION_ID=C',
195 apac.assignment_action_id ps_report_id
196 from pay_assignment_actions mpac,
197 pay_action_interlocks mpai,
198 pay_assignment_actions ppac,
199 pay_action_interlocks ppai,
200 pay_assignment_actions apac,
201 pay_payroll_actions ppa
202 where ppa.payroll_action_id = ppac.payroll_Action_id
203 and apac.assignment_action_id = ppai.locked_action_id
204 and apac.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ID',ppa.legislative_parameters)
205 and ppai.locking_Action_id = ppac.assignment_action_id /* 5471093 */
206 and ppac.assignment_action_id = mpai.locked_action_id
207 and mpac.assignment_Action_id = mpai.locking_action_id
208 and mpac.payroll_action_id =pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
209 and apac.action_status = 'C'
210 and ppac.action_status = 'C' /* Bug No : 3288757 */
211 /*Bug 3400521 - Added the following joins to improve the performance*/
212 and mpac.action_status = 'C'
213 and ppa.action_status = 'C'
214 and ppa.report_type = 'AU_PAYMENT_SUMMARY_REPORT'
215 and ppa.report_qualifier = 'AU'
216 and ppa.report_category = 'REPORT'
217 and apac.assignment_id = ppac.assignment_id /* 5471093 */
218 and ppac.assignment_id = mpac.assignment_id /* 5471093 */
219 /* 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 */
220 and 1 <= (select sum(nvl(value,0))
221 from ff_user_entities fue, ff_archive_items ff
222 where fue.user_Entity_id = ff.user_entity_id
223 and fue.user_entity_name in
224 ('X_ALLOWANCE_1_ASG_YTD','X_ALLOWANCE_2_ASG_YTD','X_ALLOWANCE_3_ASG_YTD',
225 'X_ALLOWANCE_4_ASG_YTD','X_FRINGE_BENEFITS_ASG_YTD',
226 'X_CDEP_ASG_YTD','X_EARNINGS_TOTAL_ASG_YTD','X_WORKPLACE_DEDUCTIONS_ASG_YTD',
227 'X_LUMP_SUM_A_DEDUCTION_ASG_YTD','X_LUMP_SUM_A_PAYMENTS_ASG_YTD',
228 'X_LUMP_SUM_B_DEDUCTION_ASG_YTD','X_LUMP_SUM_B_PAYMENTS_ASG_YTD',
229 'X_LUMP_SUM_D_PAYMENTS_ASG_YTD','X_LUMP_SUM_E_PAYMENTS_ASG_YTD',
230 'X_TOTAL_TAX_DEDUCTIONS_ASG_YTD','X_OTHER_INCOME_ASG_YTD','X_UNION_FEES_ASG_YTD')
231 and ff.context1= apac.assignment_action_id
232 )
233 group by apac.assignment_action_id;
234
235 --------------------------------------------------------------------+
236 -- PUBLIC cursor to select Employee ETP Payment Summary data.
237 --------------------------------------------------------------------+
238 /* Bug #2113363 - In the User Entity Name, add X_PRE_JUL_83_COMPONENT_ASG_YTD,
239 X_POST_JUN_83_UNTAXED_ASG_YTD, X_POST_JUN_83_UNTAXED_ASG_YTD,X_INVALIDITY_PAYMENTS_ASG_YTD */
240 /* Bug #2581412 - Used ORDERED hint and added few joins to improve performance */
241 /* Bug #4570012 - Added Check for Employee Type in Cursor to ensure only terminated employees are picked */
242 /* Bug #6630375 - Added Check for Assignment ID in both ARCHIVE and Magtape assignment actions */
243
244 cursor etp_payment_summary_data_val IS -- (Bug 3132178) Created to be called for spawned process
245 select 'ASSIGNMENT_ACTION_ID=C',
246 ppac.assignment_action_id ps_report_id
247 from pay_assignment_actions ppac,
248 pay_payroll_actions mpa,
249 pay_assignment_actions mpac
250 where mpa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
251 AND mpac.payroll_action_id = mpa.payroll_action_id /* Added for bug 6630375 */
252 AND mpac.assignment_id = ppac.assignment_id /* Added for bug 6630375 */
253 AND ppac.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_PAYROLL_ACTION',mpa.legislative_parameters)
254 AND pay_au_payment_summary.get_archive_value('X_REPORTING_FLAG', ppac.assignment_action_id)='YES'
255 AND pay_au_payment_summary.get_archive_value('X_CURR_TERM_0_BAL_FLAG',ppac.assignment_action_id)='NO' /* Added for bug 5257622 */
256 and pay_au_payment_summary.get_archive_value('X_SORT_EMPLOYEE_TYPE',ppac.assignment_action_id) = 'T'
257 and ppac.action_status = 'C'
258 and mpa.report_type = 'AU_PS_DATA_FILE_VAL'
259 and mpa.report_qualifier = 'AU'
260 and mpa.report_category = 'REPORT'
261 and 1 <= (select sum(nvl(value,0))
262 from ff_user_entities fue, ff_archive_items ff
263 where fue.user_Entity_id = ff.user_entity_id
264 and fue.user_entity_name in
265 ('X_LUMP_SUM_C_DEDUCTIONS_ASG_YTD',
266 'X_PRE_JUL_83_COMPONENT_ASG_YTD',
267 'X_POST_JUN_83_UNTAXED_ASG_YTD',
268 'X_POST_JUN_83_TAXED_ASG_YTD',
269 'X_INVALIDITY_PAYMENTS_ASG_YTD')
270 and ff.context1= ppac.assignment_action_id
271 )
272 group by ppac.assignment_action_id;
273
274 /* Bug #4570012 - Added Check for Employee Type in Cursor to ensure only terminated employees are picked */
275
276 cursor etp_payment_summary_data is
277 select /*+ORDERED*/ 'ASSIGNMENT_ACTION_ID=C',
278 apac.assignment_action_id ps_report_id
279 from
280 pay_assignment_actions mpac, -- Magtape
281 pay_action_interlocks mpai,
282 pay_assignment_actions ppac, --Self Printed
283 pay_action_interlocks ppai,
284 pay_assignment_actions apac, --Archive
285 pay_payroll_actions ppa --Self Printed
286 where mpac.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
287 and mpac.action_status = 'C'
288 and ppac.action_status = 'C'
289 and mpac.assignment_Action_id = mpai.locking_action_id
290 and ppac.assignment_action_id = mpai.locked_action_id
291 and ppai.locking_Action_id = ppac.assignment_action_id
292 and apac.assignment_action_id = ppai.locked_action_id
293 and apac.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ID',ppa.legislative_parameters)
294 and pay_au_payment_summary.get_archive_value('X_SORT_EMPLOYEE_TYPE',apac.assignment_action_id) = 'T'
295 and ppa.payroll_action_id = ppac.payroll_Action_id
296 and ppa.report_type = 'AU_PAYMENT_SUMMARY_REPORT'
297 and apac.assignment_id = ppac.assignment_id
298 and ppac.assignment_id = mpac.assignment_id
299 and 1 <= (select sum(nvl(value,0))
300 from ff_user_entities fue, ff_archive_items ff
301 where fue.user_Entity_id = ff.user_entity_id
302 and fue.user_entity_name in
303 ('X_LUMP_SUM_C_DEDUCTIONS_ASG_YTD',
304 'X_PRE_JUL_83_COMPONENT_ASG_YTD',
305 'X_POST_JUN_83_UNTAXED_ASG_YTD',
306 'X_POST_JUN_83_TAXED_ASG_YTD',
307 'X_INVALIDITY_PAYMENTS_ASG_YTD')
308 and ff.context1= apac.assignment_action_id
309 )
310 group by apac.assignment_action_id;
311
312
313 end pay_au_payment_summary_magtape;
[Home] [Help]