[Home] [Help]
PACKAGE: APPS.PAY_AU_RECON_SUMMARY
Source
1 package pay_au_recon_summary as
2 /* $Header: pyauprec.pkh 120.1.12010000.1 2008/07/27 22:05:58 appldev 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
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
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.
36
37 */
38 /* Bug#3004966 */
39 g_ttd_count number :=1;
40 g_lump_sum_c_bal_typ_id pay_balance_types.balance_type_id%type;
41 g_term_ded_bal_typ_id pay_balance_types.balance_type_id%type;
42 g_tot_tax_ded_bal_typ_id pay_balance_types.balance_type_id%type;
43 /* End Bug#3004966 */
44 g_index number:=0; /* Bug#3215982 */
45 /* Bug 3034198 */
46 g_fbt_defined_balance_id pay_defined_balances.defined_balance_id%TYPE :=0;
47 /* End bug 3034189 */
48
49 /*------- Bug# 3193479 ---------------------------------------------------------------*/
50
51 g_input_table pay_balance_pkg.t_balance_value_tab;
52 g_result_table pay_balance_pkg.t_detailed_bal_out_tab;
53 g_context_table pay_balance_pkg.t_context_tab;
54
55 /* Start of Bug : 3186840*/
56
57 g_input_term_details_table pay_balance_pkg.t_balance_value_tab;
58 g_result_term_details_table pay_balance_pkg.t_detailed_bal_out_tab;
59
60 g_input_group_details_table pay_balance_pkg.t_balance_value_tab;
61 g_result_group_details_table pay_balance_pkg.t_detailed_bal_out_tab;
62
63 g_input_grp_assgt_dets_table pay_balance_pkg.t_balance_value_tab;
64 g_result_grp_assgt_dets_table pay_balance_pkg.t_detailed_bal_out_tab;
65
66
67 g_bal_dim_level varchar2(1);
68 g_dimension_name pay_balance_dimensions.dimension_name%TYPE;
69
70 /* End of Bug : 3186840 */
71
72 TYPE
73 g_bal_type_tab IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER; /*Bug#4863149*/
74
75 TYPE bal_type_ids is record
76 (balance_value NUMBER
77 );
78 type bal_tab is table of bal_type_ids index by binary_integer;
79 bal_id bal_tab;
80
81 -- Procedure for Batch Balance Retrieval
82 PROCEDURE get_value_bbr(c_year_start DATE,
83 c_year_end DATE,
84 c_assignment_id pay_assignment_actions.assignment_id%type,
85 c_fbt_rate ff_globals_f.global_value%TYPE,
86 c_ml_rate ff_globals_f.global_value%TYPE,
87 p_assignment_action_id pay_assignment_actions.assignment_id%type,
88 p_tax_unit_id hr_all_organization_units.organization_id%TYPE,
89 p_termination_date DATE, --Bug 3098367
90 p_display_flag OUT NOCOPY VARCHAR2, --Bug 3098367
91 p_output_tab OUT NOCOPY bal_tab,
92 p_message OUT NOCOPY VARCHAR2 /* Bug#4863149 */
93 );
94 -- TO return the value from plsql table populated by BBR
95 function get_bal_value_new(p_defined_balance_id pay_defined_balances.defined_balance_id%TYPE)
96 return number;
97
98 g_db_id_fbt pay_defined_balances.defined_balance_id%TYPE;
99 g_db_id_cdep pay_defined_balances.defined_balance_id%TYPE;
100 g_db_id_wgd pay_defined_balances.defined_balance_id%TYPE; /* 4015082 */
101 g_db_id_et pay_defined_balances.defined_balance_id%TYPE;
102 g_db_id_lsad pay_defined_balances.defined_balance_id%TYPE;
103 g_db_id_lsap pay_defined_balances.defined_balance_id%TYPE;
104 g_db_id_lsbp pay_defined_balances.defined_balance_id%TYPE;
105 g_db_id_lsbd pay_defined_balances.defined_balance_id%TYPE;
106 g_db_id_lsdp pay_defined_balances.defined_balance_id%TYPE;
107 g_db_id_lsep pay_defined_balances.defined_balance_id%TYPE;
108 g_db_id_lscp pay_defined_balances.defined_balance_id%TYPE;
109 g_db_id_lscd pay_defined_balances.defined_balance_id%TYPE;
110 g_db_id_ttd pay_defined_balances.defined_balance_id%TYPE;
111 g_db_id_oi pay_defined_balances.defined_balance_id%TYPE;
112 g_db_id_uf pay_defined_balances.defined_balance_id%TYPE;
113 g_db_id_ip pay_defined_balances.defined_balance_id%TYPE;
114 g_db_id_lpm pay_defined_balances.defined_balance_id%TYPE;
115 g_db_id_td pay_defined_balances.defined_balance_id%TYPE;
116
117
118
119 /*--------end of Bug# 3193479---------------------------------------------------------------*/
120
121
122 g_lst_yr_term varchar2(10);--Bug:3686549
123
124 type exclusion_asg is record
125 (employee_name per_all_people_f.full_name%type,
126 assignment_number per_all_assignments_f.assignment_number%type,
127 assignment_id per_all_assignments_f.assignment_id%type,
128 reason varchar2(100)
129 );
130
131 type exc_table is table of exclusion_asg index by binary_integer;
132
133 exc_tab1 exc_table;
134
135 --Bug#3749530 - Function modified to set globals parmaters
136 function populate_bal_ids(p_le_level IN varchar2 DEFAULT NULL,
137 p_business_group_id hr_organization_units.organization_id%type,
138 p_lst_yr_term VARCHAR2 DEFAULT NULL ) return number; -- Bug : 3186840
139
140 Function total_gross -- Bug 3263659
141 return number;
142
143
144 Function get_total_allowances(c_year_start DATE,
145 c_year_end DATE,
146 c_assignment_id pay_assignment_actions.assignment_id%type,
147 c_assignment_action_id pay_assignment_actions.assignment_action_id%type,
148 c_registered_employer NUMBER,
149 c_message OUT NOCOPY VARCHAR2) /* Bug#4863149 */
150 return number;
151
152
153
154 Function get_total_fbt(c_year_start DATE, --Bug#3213539
155 c_assignment_id pay_assignment_actions.assignment_id%type,
156 p_tax_unit_id hr_all_organization_units.organization_id%TYPE,
157 c_fbt_rate ff_globals_f.global_value%TYPE,
158 c_ml_rate ff_globals_f.global_value%TYPE,
159 p_termination VARCHAR2)
160 return number ;
161
162
163 function get_total_cdep -- Bug 3263659
164 return number ;
165
166 function get_total_workplace -- Bug 4015082
167 return number ;
168
169
170 function Total_Lump_Sum_A_Payments -- Bug 3263659
171 return number ;
172
173 function Total_Lump_Sum_B_Payments -- Bug 3263659
174 return number ;
175
176 function Total_Lump_Sum_D_Payments -- Bug 3263659
177 return number ;
178
179 function Total_Lump_Sum_E_Payments(c_year_end DATE,
180 c_assignment_id pay_assignment_actions.assignment_id%type,
181 c_registered_employer NUMBER) --2610141
182 return number ;
183
184 function Total_Union_fees -- Bug 3263659
185 return number ;
186
187
188 function Total_Tax_deductions -- Bug 3263659
189 return number ;
190
191
192 function Total_Other_Income -- Bug 3263659
193 return number ;
194
195
196 function Total_Invalidity_Payments -- Bug 3263659
197 return number ;
198
199 function etp_details
200 (p_assignment_id in pay_assignment_actions.ASSIGNMENT_ID%type
201 ,p_year_start in pay_payroll_Actions.effective_date%type
202 ,p_year_end in pay_payroll_Actions.effective_date%type)
203 return number;
204
205 function post30jun1983_value
206 return number;
207
208 function etp_gross
209 return number;
210
211 function assessable_income
212 return number;
213
214 function etp_tax
215 return number;
216
217 function get_exclusion_info(flag varchar2 ,p_assignment_id number) return varchar2;
218
219 function get_assignment_id(p_assignment_id number) return number;
220
221 function populate_exclusion_table(p_assignment_id per_all_assignments_f.assignment_id%type,
222 p_financial_year varchar2,
223 p_financial_year_end date,
224 p_tax_unit_id number --2610141
225 )
226 return number ;
227
228
229 /* Start of Bug : 3186840 */
230
231 PROCEDURE populate_group_def_bal_ids (p_dimension_name IN pay_balance_dimensions.dimension_name%TYPE);
232
233 PROCEDURE get_group_values_bbr
234 ( p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE DEFAULT NULL
235 , p_date_earned IN date
236 , p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
237 , p_group_output_tab OUT NOCOPY bal_tab );
238
239 PROCEDURE get_assgt_curr_term_values_bbr
240 ( p_year_start IN DATE
241 , p_year_end IN DATE
242 , p_assignment_id IN pay_assignment_actions.assignment_id%type
243 , p_fbt_rate IN ff_globals_f.global_value%TYPE
244 , p_ml_rate IN ff_globals_f.global_value%TYPE
245 , p_assignment_action_id IN pay_assignment_actions.assignment_action_id%type
246 , p_tax_unit_id IN hr_all_organization_units.organization_id%TYPE
247 , p_emp_type IN varchar2
248 , p_term_output_tab OUT NOCOPY bal_tab
249 , p_message OUT NOCOPY VARCHAR2); /* Bug#4863149 */
250
251 /* End of Bug : 3186840 */
252
253
254 ------------ --Bug#3749530 All the procedures added below are for archival model------
255
256 --------------------------------------------------------------------
257 -- These are PUBLIC procedures are required by the Archive process.
258 -- There names are stored in PAY_REPORT_FORMAT_MAPPINGS_F so that
259 -- the archive process knows what code to execute for each step of
260 -- the archive.
261 --------------------------------------------------------------------
262
263 --------------------------------------------------------------------
264 -- This procedure returns a sql string to select a range
265 -- of assignments eligible for archival.
266 --------------------------------------------------------------------
267
268 procedure range_code
269 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
270 p_sql out nocopy varchar2);
271
272 --------------------------------------------------------------------
273 -- This procedure is used to set global contexts
274 -- however in current case it is a dummy procedure. In case this
275 -- procedure is not present then archiver assumes that
276 -- no archival is required.
277 --------------------------------------------------------------------
278
279 procedure initialization_code
280 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type);
281
282
283 --------------------------------------------------------------------
284 -- This procedure further restricts the assignment_id's
285 -- returned by range_code
286 --------------------------------------------------------------------
287 procedure assignment_action_code
288 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
289 p_start_person_id in per_all_people_f.person_id%type,
290 p_end_person_id in per_all_people_f.person_id%type,
291 p_chunk in number);
292
293
294 --------------------------------------------------------------------
295 -- This procedure is actually used to archive data . It
296 -- internally calls private procedures to archive balances ,
297 -- employee details, employer details and supplier details .
298 --------------------------------------------------------------------
299 procedure archive_code
300 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
301 p_effective_date in date);
302
303
304 procedure spawn_ps_report
305 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type);
306
307
308 end pay_au_recon_summary;
309