[Home] [Help]
Skip to content
PACKAGE: APPS.PAY_AU_RECON_SUMMARY
Source
1 package pay_au_recon_summary AUTHID CURRENT_USER as
2 /* $Header: pyauprec.pkh 120.11.12020000.2 2012/07/04 21:11:53 amnaraya ship $*/
3
4 /*
5 *** ------------------------------------------------------------------------+
6 *** Program: pay_au_recon_summary (Package Header)
7 ***
8 *** Change History
9 ***
10 *** Date Changed By Version Description of Change
11 *** --------- ---------- ------- ----------------------------------------+
12 *** 07 APR 03 apunekar 1.1 Initial version
13 *** 18 Jun 03 Ragovind 1.3 Bug#3004966 - Performance Fix
14 *** 20 Jun 03 Ragovind 1.4 Bug#3004966 - Fix for Performance Improvement
15 *** 02 Jul 03 JLin 1.5 Bug 3034189 - Added g_fbt_defined_balance_id
16 *** 20-OCT-03 punmehta 115.6 Bug#3193479 - Implemented Batch Balance Retrieval. for that created new function to populate
17 *** global plsql table and other functions gets the balance value from this function.
18 *** 21-OCT-03 punmehta 115.7 Bug#3193479 - Modified OUT with OUT NOCOPY
19 *** 23-OCT-03 punmehta 115.8 Bug#3213539 - Modified get_total_fbt defination
20 *** 23-OCT-03 punmehta 115.9 Bug#3213539 - Modified comment
21 *** 27-OCT-03 vgsriniv 115.10 Bug#3215982 - Varible g_index is declared and initialized to zero
22 *** 22-Nov-03 punmehta 115.11 Bug#3263659 - Removed unused arguments from function declarations.
23 *** 10-Feb-04 punmehta 115.12 Bug#3098367 - Added check to not report if all balances are zero.
24 *** 08-Mar-04 srrajago 115.13 Bug#3186840 - Declared PL/SQL tables.In 'populate_bal_ids', included a parameter l_le_level.
25 *** Introduced procedures 'get_group_values_bbr','get_assgt_curr_term_values_bbr',
26 *** 'populate_group_def_bal_ids' and 'get_group_assgt_values_bbr'.
27 *** 29-Mar-04 srrajago 115.14 Bug#3186840 - Removed the procedure 'get_group_assgt_values_bbr'.
28 *** 11-Jun-04 punmehta 115.18 Bug#3686549 - Added a new parameter and logic to take more then 1 yrs term employees
29 *** 11-Jun-04 punmehta 115.19 Bug#3686549 - Modified for GSCC warnings
30 *** 21-Jun-04 punmehta 115.20 Bug#3693034 - Added default null to paramater
31 *** 07-Jul-04 punmehta 115.21 Bug#3749530 - Added archival model to archive assignment_actions for performance
35 *** 06-DEC-05 abhkumar 115.25 Bug#4863149 Modified the code to raise error message when there is no defined balance id for the allowance balance.
32 *** 09-Aug-04 abhkumar 115.22 Bug#2610141 - Legal Employer enhancement changes.
33 *** 30-Dec-04 avenkatk 115.23 Bug#3899641 - Added Functional Dependancy comment.
34 *** 18-Jan-05 hnainani 115.23 Bug#4015082 - Workplace Giving Deductions
36 *** 02-DEC-08 skshin 115.26 Bug#7571001 Added new pl/sql tables and modified function parameters for allowance balance group level reporting
37 *** 28-JAN-09 skshin 115.28 Bug#7571001 Corrected check_file error of multiple comments on the same line
38 *** 23-JUN-09 pmatamsr 115.29 Bug#8587013 Added functions 'Total_RESC','Total_Foreign_Income' and removed 'Total_Other_Income'.
39 *** 07-SEP-09 pmatamsr 115.30 Bug#8769345 Added global variables to hold the values of ETP taxbale and Tax Free payments balances
40 *** 19-NOV-09 skshin 115.32 Bug#8711855 Added global variables for retro GT12 balances (Lump Sum E)
41 *** 28-SEP-10 dduvvuri 115.33 Bug#9147438 Added changes for Foreign Worker Enhancement
42 *** 02-Dec-12 avenkatk 115.34 Bug#10331262 Add def bal ID's for FW Term Pay and Leave Payments
43 *** Removed unwanted def bal ID's used for FW enhancement
44 *** 02-Feb-12 skshin 115.36 Bug#13362286 Added g_db_id_rea global variable
45 */
46 /* Bug#3004966 */
47 g_ttd_count number :=1;
48 g_lump_sum_c_bal_typ_id pay_balance_types.balance_type_id%type;
49 g_term_ded_bal_typ_id pay_balance_types.balance_type_id%type;
50 g_tot_tax_ded_bal_typ_id pay_balance_types.balance_type_id%type;
51 /* End Bug#3004966 */
52 g_index number:=0; /* Bug#3215982 */
53 /* Bug 3034198 */
54 g_fbt_defined_balance_id pay_defined_balances.defined_balance_id%TYPE :=0;
55 /* End bug 3034189 */
56
57 /*------- Bug# 3193479 ---------------------------------------------------------------*/
58
59 g_input_table pay_balance_pkg.t_balance_value_tab;
60 g_result_table pay_balance_pkg.t_detailed_bal_out_tab;
61 g_context_table pay_balance_pkg.t_context_tab;
62 /* Start of Bug : 3186840*/
63
64 /* Changes for 9147438 start */
65 g_fw_input_table pay_au_foreign_workers.tab_bal_type;
66 g_fw_result_table pay_balance_pkg.t_detailed_bal_out_tab;
67 g_fw_context_table pay_balance_pkg.t_context_tab;
68 g_fw_input_alw_table pay_au_foreign_workers.tab_bal_type;
69 g_fw_result_alw_table pay_balance_pkg.t_detailed_bal_out_tab;
70 g_fw_allowance_tab pay_au_payment_summary.t_allowance_balance%type;
71
72 type r_allowance_bal is record (balance_name pay_balance_types.balance_name%type, balance_value number);
73 type tab_allownace_balance is table of r_allowance_bal index by binary_integer;
74 p_fw_allowance_tab tab_allownace_balance;
75 /* Changes for 9147438 end */
76
77 g_input_term_details_table pay_balance_pkg.t_balance_value_tab;
78 g_result_term_details_table pay_balance_pkg.t_detailed_bal_out_tab;
79
80 g_input_group_details_table pay_balance_pkg.t_balance_value_tab;
81 g_result_group_details_table pay_balance_pkg.t_detailed_bal_out_tab;
82
83 g_input_fw_grp_dtls_table pay_au_foreign_workers.tab_bal_type;
84 g_result_fw_grp_dtls_table pay_balance_pkg.t_detailed_bal_out_tab;
85
86
87 g_input_grp_assgt_dets_table pay_balance_pkg.t_balance_value_tab;
88 g_result_grp_assgt_dets_table pay_balance_pkg.t_detailed_bal_out_tab;
89
90
91 g_bal_dim_level varchar2(1);
92 g_dimension_name pay_balance_dimensions.dimension_name%TYPE;
93
94 /* End of Bug : 3186840 */
95
96 /* start bug 7571001 */
97 g_input_alw_table pay_balance_pkg.t_balance_value_tab;
98 g_result_alw_table pay_balance_pkg.t_detailed_bal_out_tab;
99
100 g_input_group_alw_table pay_balance_pkg.t_balance_value_tab;
101 g_result_group_alw_table pay_balance_pkg.t_detailed_bal_out_tab;
102 /* end bug 7571001 */
103
104 TYPE
105 g_bal_type_tab IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER; /*Bug#4863149*/
106
107 TYPE bal_type_ids is record
108 (balance_value NUMBER
109 );
110 type bal_tab is table of bal_type_ids index by binary_integer;
111 bal_id bal_tab;
112 fw_bal_id bal_tab;
113
114 -- Procedure for Batch Balance Retrieval
115 PROCEDURE get_value_bbr(c_year_start DATE,
116 c_year_end DATE,
117 c_assignment_id pay_assignment_actions.assignment_id%type,
118 c_fbt_rate ff_globals_f.global_value%TYPE,
119 c_ml_rate ff_globals_f.global_value%TYPE,
120 p_assignment_action_id pay_assignment_actions.assignment_id%type,
121 p_tax_unit_id hr_all_organization_units.organization_id%TYPE,
122 p_termination_date DATE, --Bug 3098367
123 p_display_flag OUT NOCOPY VARCHAR2, --Bug 3098367
124 p_output_tab OUT NOCOPY bal_tab
125 --, p_message OUT NOCOPY VARCHAR2); /* bug 7571001 - removed the parameter */
126 );
127
128 -- TO return the value from plsql table populated by BBR
129 function get_bal_value_new(p_defined_balance_id pay_defined_balances.defined_balance_id%TYPE)
130 return number;
131
132 g_db_id_fbt pay_defined_balances.defined_balance_id%TYPE;
133 g_db_id_cdep pay_defined_balances.defined_balance_id%TYPE;
134 g_db_id_wgd pay_defined_balances.defined_balance_id%TYPE; /* 4015082 */
135 g_db_id_et pay_defined_balances.defined_balance_id%TYPE;
136 g_db_id_lsad pay_defined_balances.defined_balance_id%TYPE;
137 g_db_id_lsap pay_defined_balances.defined_balance_id%TYPE;
138 g_db_id_lsbp pay_defined_balances.defined_balance_id%TYPE;
142 g_db_id_lscp pay_defined_balances.defined_balance_id%TYPE;
139 g_db_id_lsbd pay_defined_balances.defined_balance_id%TYPE;
140 g_db_id_lsdp pay_defined_balances.defined_balance_id%TYPE;
141 g_db_id_lsep pay_defined_balances.defined_balance_id%TYPE;
143 g_db_id_lscd pay_defined_balances.defined_balance_id%TYPE;
144 g_db_id_ttd pay_defined_balances.defined_balance_id%TYPE;
145 g_db_id_uf pay_defined_balances.defined_balance_id%TYPE;
146 g_db_id_ip pay_defined_balances.defined_balance_id%TYPE;
147 g_db_id_lpm pay_defined_balances.defined_balance_id%TYPE;
148 g_db_id_td pay_defined_balances.defined_balance_id%TYPE;
149 g_db_id_oi pay_defined_balances.defined_balance_id%TYPE;
150 /*Begin 8587013 - Added global variables to hold the values of RESC and Exempt Foreign Employment Income balances*/
151 g_db_id_resc pay_defined_balances.defined_balance_id%TYPE;
152 g_db_id_efei pay_defined_balances.defined_balance_id%TYPE;
153 /*End 8587013*/
154 /* Start 8769345 */
155 g_db_id_tftn pay_defined_balances.defined_balance_id%TYPE;
156 g_db_id_tftp pay_defined_balances.defined_balance_id%TYPE;
157 g_db_id_tfln pay_defined_balances.defined_balance_id%TYPE;
158 g_db_id_tflp pay_defined_balances.defined_balance_id%TYPE;
159 g_db_id_ttn pay_defined_balances.defined_balance_id%TYPE;
160 g_db_id_ttp pay_defined_balances.defined_balance_id%TYPE;
161 g_db_id_tln pay_defined_balances.defined_balance_id%TYPE;
162 g_db_id_tlp pay_defined_balances.defined_balance_id%TYPE;
163 /* End 8769345 */
164 /* Start 8711855 */
165 g_db_id_rll pay_defined_balances.defined_balance_id%TYPE;
166 g_db_id_res pay_defined_balances.defined_balance_id%TYPE;
167 g_db_id_rpt pay_defined_balances.defined_balance_id%TYPE;
168 /* End 8711855 */
169
170 /* Start Bug 10331262 */
171
172 g_db_id_flp pay_defined_balances.defined_balance_id%TYPE;
173 g_db_id_flpm pay_defined_balances.defined_balance_id%TYPE;
174 g_db_id_flsap pay_defined_balances.defined_balance_id%TYPE;
175 g_db_id_flcd pay_defined_balances.defined_balance_id%TYPE;
176 g_db_id_flsad pay_defined_balances.defined_balance_id%TYPE;
177
178 /* End Bug 10331262 */
179
180 g_db_id_rea pay_defined_balances.defined_balance_id%TYPE; -- bug 13362286
181
182 /*--------end of Bug# 3193479---------------------------------------------------------------*/
183
184
185 g_lst_yr_term varchar2(10);--Bug:3686549
186
187 type exclusion_asg is record
188 (employee_name per_all_people_f.full_name%type,
189 assignment_number per_all_assignments_f.assignment_number%type,
190 assignment_id per_all_assignments_f.assignment_id%type,
191 reason varchar2(100)
192 );
193
194 --Change done
195 type exc_table is table of exclusion_asg index by varchar2(15);
196
197 exc_tab1 exc_table;
198
199 --Bug#3749530 - Function modified to set globals parmaters
200 function populate_bal_ids(p_le_level IN varchar2 DEFAULT NULL,
201 p_business_group_id hr_organization_units.organization_id%type,
202 p_lst_yr_term VARCHAR2 DEFAULT NULL ) return number; -- Bug : 3186840
203
204 Function total_gross -- Bug 3263659
205 return number;
206
207 /* bug 7571001 - added for adjusting allowance */
208 function adjust_retro_group_allowances(t_allowance_balance IN OUT NOCOPY pay_au_payment_summary.t_allowance_balance%type
209 ,p_year_start in DATE
210 ,p_year_end in DATE
211 ,p_registered_employer in NUMBER
212 )
213 return number;
214
215 /* bug 7571001 - p_meesage introduced by 4863149 is removed */
216 Function get_total_allowances ( p_year_start DATE,
217 p_year_end DATE,
218 p_assignment_id pay_assignment_actions.assignment_id%type,
219 p_assignment_action_id pay_assignment_actions.assignment_id%type,
220 p_tax_unit_id hr_all_organization_units.organization_id%type)
221 return number;
222
223
224
225 Function get_total_fbt(c_year_start DATE, --Bug#3213539
226 c_assignment_id pay_assignment_actions.assignment_id%type,
227 p_tax_unit_id hr_all_organization_units.organization_id%TYPE,
228 c_fbt_rate ff_globals_f.global_value%TYPE,
229 c_ml_rate ff_globals_f.global_value%TYPE,
230 p_termination VARCHAR2)
231 return number ;
232
233
234 function get_total_cdep -- Bug 3263659
235 return number ;
236
237 function get_total_workplace -- Bug 4015082
238 return number ;
239
240
241 function Total_Lump_Sum_A_Payments -- Bug 3263659
242 return number ;
243
244 function Total_Lump_Sum_B_Payments -- Bug 3263659
245 return number ;
246
247 function Total_Lump_Sum_D_Payments -- Bug 3263659
248 return number ;
249
250 function Total_Lump_Sum_E_Payments(c_year_end DATE,
251 c_assignment_id pay_assignment_actions.assignment_id%type,
252 c_registered_employer NUMBER) --2610141
253 return number ;
254
255 function Total_Union_fees -- Bug 3263659
256 return number ;
257
258
259 function Total_Tax_deductions -- Bug 3263659
260 return number ;
261
262
263 function Total_RESC -- Bug 8587013
264 return number ;
265
266 function Total_Foreign_Income -- Bug 8587013
267 return number ;
268
269 function Total_Invalidity_Payments -- Bug 3263659
270 return number ;
271
272 function etp_details
273 (p_assignment_id in pay_assignment_actions.ASSIGNMENT_ID%type
274 ,p_year_start in pay_payroll_Actions.effective_date%type
275 ,p_year_end in pay_payroll_Actions.effective_date%type)
276 return number;
277
278 function post30jun1983_value
279 return number;
280
281 function etp_gross
282 return number;
283
284 function assessable_income
285 return number;
286
287 function etp_tax
288 return number;
289
290 function get_exclusion_info(flag varchar2 ,p_assignment_id number) return varchar2;
291
292 function get_assignment_id(p_assignment_id number) return number;
293
294 function populate_exclusion_table(p_assignment_id per_all_assignments_f.assignment_id%type,
295 p_financial_year varchar2,
296 p_financial_year_end date,
297 p_tax_unit_id number --2610141
298 )
299 return number ;
300
301
302 /* Start of Bug : 3186840 */
303
304 PROCEDURE populate_group_def_bal_ids (p_dimension_name IN pay_balance_dimensions.dimension_name%TYPE
305 ,p_business_group_id per_business_groups.business_group_id%TYPE); -- bug 7571001
306
307 PROCEDURE get_group_values_bbr
308 (p_year_start DATE
309 ,p_year_end DATE
310 ,p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE DEFAULT NULL
311 , p_date_earned IN date
312 , p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
313 , p_group_output_tab OUT NOCOPY bal_tab );
314
315 PROCEDURE get_assgt_curr_term_values_bbr
316 ( p_year_start IN DATE
317 , p_year_end IN DATE
318 , p_assignment_id IN pay_assignment_actions.assignment_id%type
319 , p_fbt_rate IN ff_globals_f.global_value%TYPE
320 , p_ml_rate IN ff_globals_f.global_value%TYPE
321 , p_assignment_action_id IN pay_assignment_actions.assignment_action_id%type
322 , p_tax_unit_id IN hr_all_organization_units.organization_id%TYPE
323 , p_emp_type IN varchar2
324 , p_term_output_tab OUT NOCOPY bal_tab);
325 --, p_message OUT NOCOPY VARCHAR2); /* bug 7571001 - removed the parameter */
326
327 /* End of Bug : 3186840 */
328
329
330 ------------ --Bug#3749530 All the procedures added below are for archival model------
331
332 --------------------------------------------------------------------
333 -- These are PUBLIC procedures are required by the Archive process.
334 -- There names are stored in PAY_REPORT_FORMAT_MAPPINGS_F so that
335 -- the archive process knows what code to execute for each step of
336 -- the archive.
337 --------------------------------------------------------------------
338
339 --------------------------------------------------------------------
340 -- This procedure returns a sql string to select a range
341 -- of assignments eligible for archival.
342 --------------------------------------------------------------------
343
344 procedure range_code
345 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
346 p_sql out nocopy varchar2);
347
348 --------------------------------------------------------------------
349 -- This procedure is used to set global contexts
350 -- however in current case it is a dummy procedure. In case this
351 -- procedure is not present then archiver assumes that
352 -- no archival is required.
353 --------------------------------------------------------------------
354
355 procedure initialization_code
356 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type);
357
358
359 --------------------------------------------------------------------
360 -- This procedure further restricts the assignment_id's
361 -- returned by range_code
362 --------------------------------------------------------------------
363 procedure assignment_action_code
364 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
365 p_start_person_id in per_all_people_f.person_id%type,
366 p_end_person_id in per_all_people_f.person_id%type,
367 p_chunk in number);
368
369
370 --------------------------------------------------------------------
371 -- This procedure is actually used to archive data . It
372 -- internally calls private procedures to archive balances ,
373 -- employee details, employer details and supplier details .
374 --------------------------------------------------------------------
375 procedure archive_code
376 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
377 p_effective_date in date);
378
379
380 procedure spawn_ps_report
381 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type);
382
383
384 end pay_au_recon_summary;