[Home] [Help]
PACKAGE BODY: APPS.PAY_MX_YEAREND_REP
Source
1 PACKAGE BODY PAY_MX_YEAREND_REP AS
2 /* $Header: paymxyearend.pkb 120.20 2012/03/01 05:21:35 vvijayku ship $ */
3
4
5 TYPE number_tbl is TABLE OF NUMBER INDEX BY BINARY_INTEGER;
6 TYPE varchar_80_tbl is TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
7 -- TYPE varchar_240_tbl is TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
8 TYPE date_tbl IS TABLE OF DATE INDEX BY BINARY_INTEGER;
9
10
11 TYPE format37_cache_r is RECORD
12 (
13 payroll_action_id number_tbl,
14 person_id number_tbl,
15 effective_date date_tbl,
16 bal_name varchar_80_tbl,
17 bal_value number_tbl,
18 sz number
19 ) ;
20
21
22 g_format37_cache format37_cache_r;
23 -- g_f37_bal varchar_240_tbl ;
24
25 /******************************************************************
26 Name : get_yearch_bal_amt
27 Purpose : returns ye archived balance
28 *****************************************************************/
29 FUNCTION get_ye_arch_bal_amt (ye_payroll_action_id in number,
30 ye_person_id in number,
31 ye_effective_date in date,
32 ye_balance_name in varchar2
33 ) RETURN NUMBER
34 IS
35 l_bal_amt number := 0;
36
37 BEGIN
38
39 hr_utility.trace('Inside pay_mx_yearend_rep.get_ye_arch_bal_amt');
40 hr_utility.trace('ye_payroll_action_id : '||ye_payroll_action_id);
41 hr_utility.trace('ye_person_id : '||ye_person_id);
42 hr_utility.trace('ye_effective_date : '||ye_effective_date);
43 hr_utility.trace('ye_balance_name : '||ye_balance_name);
44
45 select round(SUM(nvl(fnd_number.canonical_to_number(fai.value),0)))
46 into l_bal_amt
47 from pay_assignment_actions paa,
48 pay_action_information pai,
49 ff_archive_items fai,
50 ff_archive_item_contexts fic,
51 ff_user_entities fue,
52 ff_contexts ffc,
53 pay_payroll_actions ppa
54 where paa.payroll_action_id = ye_payroll_action_id
55 and paa.payroll_action_id = ppa.payroll_action_id
56 and paa.serial_number = ye_person_id
57 and pai.action_context_id = paa.assignment_action_id
58 /*and pai.effective_date = ye_effective_date*/
59 and pai.action_information7 = fnd_date.date_to_canonical(ye_effective_date)
60 and fai.context1 = paa.assignment_action_id
61 and fai.archive_item_id = fic.archive_item_id
62 and fai.user_entity_id = fue.user_entity_id
63 and fic.context_id = ffc.context_id
64 and ffc.context_name ='TAX_UNIT_ID'
65 and ltrim(rtrim(fic.context)) in(
66 SELECT DISTINCT gre_node.entity_id
67 FROM per_gen_hierarchy_nodes gre_node,
68 per_gen_hierarchy_nodes le_node,
69 per_gen_hierarchy_versions hier_ver,
70 fnd_lookup_values flv
71 WHERE gre_node.node_type = 'MX GRE'
72 and gre_node.entity_id = fic.context
73 AND gre_node.business_group_id = ppa.business_group_id
74 --AND pay_mx_yrend_arch.gre_exists (gre_node.entity_id) = 1
75 AND le_node.node_type = 'MX LEGAL EMPLOYER'
76 AND gre_node.hierarchy_version_id = le_node.hierarchy_version_id
77 AND le_node.hierarchy_node_id = gre_node.parent_hierarchy_node_id
78 AND gre_node.hierarchy_version_id = hier_ver.hierarchy_version_id
79 AND status = flv.lookup_code
80 AND flv.meaning = 'Active'
81 AND flv.LANGUAGE = 'US'
82 AND flv.lookup_type = 'PQH_GHR_HIER_VRSN_STATUS'
83 AND ye_effective_date BETWEEN hier_ver.date_from
84 AND NVL(hier_ver.date_to,
85 hr_general.end_of_time))
86 and fue.user_entity_name = ye_balance_name ;
87
88 hr_utility.trace('l_bal_amt : '||l_bal_amt);
89
90 return(l_bal_amt);
91
92
93 EXCEPTION
94 when no_data_found then
95 return(0);
96 END get_ye_arch_bal_amt;
97
98
99 /******************************************************************
100 Name : get_cache_balance
101 Purpose : retruns balance value from the cache
102 ******************************************************************/
103 function get_cache_balance( p_payroll_action_id in number,
104 p_person_id in number,
105 p_effective_date in date,
106 p_bal_name in varchar2 )
107
108 return number is
109
110 ctr number;
111 l_bal_value number;
112
113 begin
114
115 l_bal_value := 0;
116
117 for ctr in 1..g_format37_cache.sz loop
118
119 if (g_format37_cache.payroll_action_id(ctr) = p_payroll_action_id)
120 and (g_format37_cache.person_id(ctr) = p_person_id)
121 and (g_format37_cache.effective_date(ctr) = p_effective_date)
122 and (g_format37_cache.bal_name(ctr) = p_bal_name ) then
123
124 l_bal_value := g_format37_cache.bal_value(ctr);
125
126 end if;
127
128 end loop;
129
130 return nvl(l_bal_value,0) ;
131
132 -- This will be zero if the balance is not in the cached format37s
133 end get_cache_balance;
134
135 /******************************************************************
136 Name : load_bal
137 Purpose : loads balances in the pl/sql table
138 ******************************************************************/
139 procedure load_bal (p_payroll_action_id in number,
140 p_person_id in number,
141 p_effective_date in date
142 ) IS
143
144
145 -- Get balances from archiver
146 CURSOR c_get_balances IS
147 SELECT DISTINCT
148 fue_live.user_entity_name
149 FROM pay_bal_attribute_definitions pbad,
150 pay_balance_attributes pba,
151 pay_defined_balances pdb_attr,
152 pay_defined_balances pdb_call,
153 pay_balance_dimensions pbd,
154 ff_user_entities fue_live
155 WHERE pbad.attribute_name = 'Year End Balances'
156 AND pbad.legislation_code = 'MX'
157 AND pba.attribute_id = pbad.attribute_id
158 AND pdb_attr.defined_balance_id = pba.defined_balance_id
159 AND pdb_attr.balance_type_id = pdb_call.balance_type_id
160 AND pdb_call.balance_dimension_id = pbd.balance_dimension_id
161 AND pbd.database_item_suffix = '_PER_PDS_GRE_YTD'
162 AND pbd.legislation_code = pbad.legislation_code
163 AND fue_live.creator_id = pdb_call.defined_balance_id
164 AND fue_live.creator_type = 'B'
165 ORDER BY fue_live.user_entity_name;
166
167 cursor c_prev_er_isr_withheld is
168 select nvl(action_information9,'N'),
169 decode(nvl(action_information9,'N'),'Y', round(nvl(to_number(action_information24),0)),0) ,
170 decode(nvl(action_information9,'N'),'Y', round(nvl(to_number(action_information25),0)),0) ,
171 decode(nvl(action_information9,'N'),'Y', round(nvl(to_number(action_information27),0)),0) ,
172 to_number(action_information8 )
173 from pay_assignment_actions paa,
174 pay_action_information pai
175 where paa.payroll_action_id = p_payroll_action_id
176 and paa.serial_number = p_person_id
177 --and pai.effective_date = p_effective_date
178 and pai.action_information7 = fnd_date.date_to_canonical(p_effective_date) /*Bug 8402505*/
179 and pai.action_context_id = paa.assignment_action_id
180 and pai.action_information_category='MX YREND EE DETAILS' ;
181
182 l_annual_tax_calc_flag varchar2(1);
183 l_prev_er_isr_earnings number ;
184 l_prev_er_isr_withheld number ;
185 l_prev_er_isr_exempt number ;
186 l_seniority number ;
187 ln_isr_on_cum_earn number ;
188
189 begin
190
191 /* ----------
192
193 Following balances should be archived during the ye archive process
194 cursor c_get_balances should return the following balances
195
196 AMENDS_PER_PDS_GRE_YTD
197 FORMAT_2D_AID_FOR_PANTRY_AND_FOOD_PER_PDS_GRE_YTD
198 FORMAT_37_ASSIMILATED_EARNINGS_PER_PDS_GRE_YTD
199 FORMAT_37_CURRENT_FISCAL_YEAR_ARREARS_PER_PDS_GRE_YTD
200 FORMAT_37_ISR_CREDITABLE_SUBSIDY_AS_PER_FRACTION_III_PER_PDS_GRE_YTD
201 FORMAT_37_ISR_CREDITABLE_SUBSIDY_AS_PER_FRACTION_IV_PER_PDS_GRE_YTD
202 FORMAT_37_ISR_EXEMPT_FOR_AMENDS_PER_PDS_GRE_YTD
203 FORMAT_37_ISR_EXEMPT_FOR_OTHER_INCOME_PER_PDS_GRE_YTD
204 FORMAT_37_ISR_EXEMPT_FOR_SOCIAL_FORESIGHT_EARNINGS_PER_PDS_GRE_YTD
205 FORMAT_37_ISR_ON_NON_CUMULATIVE_EARNINGS_PER_PDS_GRE_YTD
206 FORMAT_37_ISR_ON_SUBJECT_BOX_TOTALS_PER_PDS_GRE_YTD
207 FORMAT_37_ISR_SUBJECT_FOR_AMENDS_PER_PDS_GRE_YTD
208 FORMAT_37_ISR_SUBJECT_FOR_OTHER_INCOME_PER_PDS_GRE_YTD
209 FORMAT_37_ISR_WITHHELD_FOR_ASSIMILATED_EARNINGS_PER_PDS_GRE_YTD
210 FORMAT_37_ISR_WITHHELD_FOR_RETIREMENT_EARNINGS_PER_PDS_GRE_YTD
211 FORMAT_37_NON_CUMULATIVE_AMENDS_PER_PDS_GRE_YTD
212 FORMAT_37_PREVIOUS_FISCAL_YEAR_ARREARS_PER_PDS_GRE_YTD
213 FORMAT_37_RETIREMENT_CUMULATIVE_EARNINGS_PER_PDS_GRE_YTD
214 FORMAT_37_RETIREMENT_DAILY_EARNINGS_IN_ONE_PAYMENT_PER_PDS_GRE_YTD
215 FORMAT_37_RETIREMENT_EARNINGS_DAYS_PER_PDS_GRE_YTD
216 FORMAT_37_RETIREMENT_EARNINGS_IN_ONE_PAYMENT_PER_PDS_GRE_YTD
217 FORMAT_37_RETIREMENT_EARNINGS_IN_PARTIAL_PAYMENTS_PER_PDS_GRE_YTD
218 FORMAT_37_RETIREMENT_EXEMPT_EARNINGS_PER_PDS_GRE_YTD
219 FORMAT_37_RETIREMENT_PERIOD_EARNINGS_PER_PDS_GRE_YTD
220 FORMAT_37_RETIREMENT_TAXABLE_EARNINGS_PER_PDS_GRE_YTD
221 FORMAT_37_SOCIAL_FORESIGHT_EARNINGS_PER_PDS_GRE_YTD
222 ISR_CALCULATED_PER_PDS_GRE_YTD
223 ISR_CREDITABLE_SUBSIDY_PER_PDS_GRE_YTD
224 ISR_CREDIT_TO_SALARY_PAID_PER_PDS_GRE_YTD
225 ISR_CREDIT_TO_SALARY_PER_PDS_GRE_YTD
226 ISR_NON_CREDITABLE_SUBSIDY_PER_PDS_GRE_YTD
227 ISR_SUBJECT_FOR_AMENDS_PER_PDS_GRE_YTD
228 ISR_WITHHELD_FOR_AMENDS_PER_PDS_GRE_YTD
229 ISR_WITHHELD_PER_PDS_GRE_YTD
230 LAST_MONTHLY_ORDINARY_SALARY_PER_PDS_GRE_YTD
231 LAST_MONTHLY_ORDINARY_SALARY_WITHHELD_PER_PDS_GRE_YTD
232 YEAR_END_ISR_EXEMPT_FOR_CHILDREN_SCHOLARSHIP_PER_PDS_GRE_YTD
233 YEAR_END_ISR_EXEMPT_FOR_CHRISTMAS_BONUS_PER_PDS_GRE_YTD
234 YEAR_END_ISR_EXEMPT_FOR_DISABILITIES_SUBSIDY_PER_PDS_GRE_YTD
235 YEAR_END_ISR_EXEMPT_FOR_DOMINICAL_PREMIUM_PER_PDS_GRE_YTD
236 YEAR_END_ISR_EXEMPT_FOR_EDUCATIONAL_AID_PER_PDS_GRE_YTD
237 YEAR_END_ISR_EXEMPT_FOR_FIXED_EARNINGS_PER_PDS_GRE_YTD
238 YEAR_END_ISR_EXEMPT_FOR_FUNERAL_AID_PER_PDS_GRE_YTD
239 YEAR_END_ISR_EXEMPT_FOR_GASOLINE_COUPONS_PER_PDS_GRE_YTD
240 YEAR_END_ISR_EXEMPT_FOR_GLASSES_AID_PER_PDS_GRE_YTD
241 YEAR_END_ISR_EXEMPT_FOR_HEALTHCARE_REIMBURSEMENT_PER_PDS_GRE_YTD
242 YEAR_END_ISR_EXEMPT_FOR_LIFE_INSURANCE_PREMIUM_PER_PDS_GRE_YTD
243 YEAR_END_ISR_EXEMPT_FOR_MAJOR_MEDICAL_EXPENSE_INSURANCE_PER_PDS_GRE_YTD
244 YEAR_END_ISR_EXEMPT_FOR_OVERTIME_PER_PDS_GRE_YTD
245 YEAR_END_ISR_EXEMPT_FOR_PANTRY_COUPONS_PER_PDS_GRE_YTD
246 YEAR_END_ISR_EXEMPT_FOR_PROFIT_SHARING_PER_PDS_GRE_YTD
247 YEAR_END_ISR_EXEMPT_FOR_PUNCTUALITY_INCENTIVE_PER_PDS_GRE_YTD
248 YEAR_END_ISR_EXEMPT_FOR_RENTAL_AID_PER_PDS_GRE_YTD
249 YEAR_END_ISR_EXEMPT_FOR_RESTAURANT_COUPONS_PER_PDS_GRE_YTD
250 YEAR_END_ISR_EXEMPT_FOR_SAVINGS_BOX_PER_PDS_GRE_YTD
251 YEAR_END_ISR_EXEMPT_FOR_SAVINGS_FUND_PER_PDS_GRE_YTD
252 YEAR_END_ISR_EXEMPT_FOR_TRANSPORTATION_AID_PER_PDS_GRE_YTD
253 YEAR_END_ISR_EXEMPT_FOR_TRAVEL_EXPENSES_PER_PDS_GRE_YTD
254 YEAR_END_ISR_EXEMPT_FOR_UNIFORM_COUPONS_PER_PDS_GRE_YTD
255 YEAR_END_ISR_EXEMPT_FOR_UNION_QUOTA_PAID_BY_ER_PER_PDS_GRE_YTD
256 YEAR_END_ISR_EXEMPT_FOR_VACATION_PREMIUM_PER_PDS_GRE_YTD
257 YEAR_END_ISR_EXEMPT_FOR_WORKER_CONTRIBUTION_PAID_BY_ER_PER_PDS_GRE_YTD
258 YEAR_END_ISR_SUBJECT_FOR_CHILDREN_SCHOLARSHIP_PER_PDS_GRE_YTD
259 YEAR_END_ISR_SUBJECT_FOR_CHRISTMAS_BONUS_PER_PDS_GRE_YTD
260 YEAR_END_ISR_SUBJECT_FOR_DISABILITIES_SUBSIDY_PER_PDS_GRE_YTD
261 YEAR_END_ISR_SUBJECT_FOR_DOMINICAL_PREMIUM_PER_PDS_GRE_YTD
262 YEAR_END_ISR_SUBJECT_FOR_EDUCATIONAL_AID_PER_PDS_GRE_YTD
263 YEAR_END_ISR_SUBJECT_FOR_FIXED_EARNINGS_PER_PDS_GRE_YTD
264 YEAR_END_ISR_SUBJECT_FOR_FUNERAL_AID_PER_PDS_GRE_YTD
265 YEAR_END_ISR_SUBJECT_FOR_GASOLINE_COUPONS_PER_PDS_GRE_YTD
266 YEAR_END_ISR_SUBJECT_FOR_GLASSES_AID_PER_PDS_GRE_YTD
267 YEAR_END_ISR_SUBJECT_FOR_HEALTHCARE_REIMBURSEMENT_PER_PDS_GRE_YTD
268 YEAR_END_ISR_SUBJECT_FOR_LIFE_INSURANCE_PREMIUM_PER_PDS_GRE_YTD
269 YEAR_END_ISR_SUBJECT_FOR_MAJOR_MEDICAL_EXPENSE_INSURANCE_PER_PDS_GRE_YTD
270 YEAR_END_ISR_SUBJECT_FOR_OVERTIME_PER_PDS_GRE_YTD
271 YEAR_END_ISR_SUBJECT_FOR_PANTRY_COUPONS_PER_PDS_GRE_YTD
272 YEAR_END_ISR_SUBJECT_FOR_PROFIT_SHARING_PER_PDS_GRE_YTD
273 YEAR_END_ISR_SUBJECT_FOR_PUNCTUALITY_INCENTIVE_PER_PDS_GRE_YTD
274 YEAR_END_ISR_SUBJECT_FOR_RENTAL_AID_PER_PDS_GRE_YTD
275 YEAR_END_ISR_SUBJECT_FOR_RESTAURANT_COUPONS_PER_PDS_GRE_YTD
276 YEAR_END_ISR_SUBJECT_FOR_SAVINGS_BOX_PER_PDS_GRE_YTD
277 YEAR_END_ISR_SUBJECT_FOR_SAVINGS_FUND_PER_PDS_GRE_YTD
278 YEAR_END_ISR_SUBJECT_FOR_TRANSPORTATION_AID_PER_PDS_GRE_YTD
279 YEAR_END_ISR_SUBJECT_FOR_TRAVEL_EXPENSES_PER_PDS_GRE_YTD
280 YEAR_END_ISR_SUBJECT_FOR_UNIFORM_COUPONS_PER_PDS_GRE_YTD
281 YEAR_END_ISR_SUBJECT_FOR_UNION_QUOTA_PAID_BY_ER_PER_PDS_GRE_YTD
282 YEAR_END_ISR_SUBJECT_FOR_VACATION_PREMIUM_PER_PDS_GRE_YTD
283 YEAR_END_ISR_SUBJECT_FOR_WORKER_CONTRIBUTION_PAID_BY_ER_PER_PDS_GRE_YTD
284
285 EMPLOYE_STATE_TAX_WITHHELD_PER_PDS_GRE_YTD
286
287 YEAR_END_STOCK_OPTIONS_VESTING_MARKET_VALUE_PER_PDS_GRE_YTD
288 YEAR_END_STOCK_OPTIONS_GRANT_PRICE_PER_PDS_GRE_YTD
289 YEAR_END_STOCK_OPTIONS_CUMULATIVE_INCOME_PER_PDS_GRE_YTD
290 YEAR_END_STOCK_OPTIONS_ISR_WITHHELD_PER_PDS_GRE_YTD
291
292
293
294 ------ */
295
296 -- get the Previous ER ISR earnings,Withheld and Exempt
297 l_annual_tax_calc_flag := null ;
298 l_prev_er_isr_earnings := 0 ;
299 l_prev_er_isr_withheld := 0 ;
300 l_prev_er_isr_exempt := 0 ;
301 l_seniority := null ;
302
303 open c_prev_er_isr_withheld ;
304 fetch c_prev_er_isr_withheld into l_annual_tax_calc_flag, l_prev_er_isr_earnings, l_prev_er_isr_withheld,
305 l_prev_er_isr_exempt, l_seniority ;
306 close c_prev_er_isr_withheld ;
307
308
309 g_format37_cache.sz := 0;
310
311 FOR c_get_balances_rec IN c_get_balances LOOP
312 g_format37_cache.sz := g_format37_cache.sz + 1;
313 g_format37_cache.payroll_action_id(g_format37_cache.sz) := p_payroll_action_id ;
314 g_format37_cache.person_id(g_format37_cache.sz) := p_person_id ;
315 g_format37_cache.effective_date(g_format37_cache.sz) := p_effective_date ;
316 g_format37_cache.bal_name(g_format37_cache.sz) := 'A_'||c_get_balances_rec.user_entity_name ;
317
318 if l_seniority is null and
319 ( c_get_balances_rec.user_entity_name = 'AMENDS_PER_PDS_GRE_YTD' or
320 c_get_balances_rec.user_entity_name = 'FORMAT_37_ISR_EXEMPT_FOR_AMENDS_PER_PDS_GRE_YTD' or
321 c_get_balances_rec.user_entity_name = 'FORMAT_37_ISR_SUBJECT_FOR_AMENDS_PER_PDS_GRE_YTD' or
322 c_get_balances_rec.user_entity_name = 'FORMAT_37_NON_CUMULATIVE_AMENDS_PER_PDS_GRE_YTD'
323 ) then
324
325 g_format37_cache.bal_value(g_format37_cache.sz) := 0 ;
326
327 elsif l_annual_tax_calc_flag <> 'Y' and
328 c_get_balances_rec.user_entity_name = 'ISR_CALCULATED_PER_PDS_GRE_YTD' then
329
330 g_format37_cache.bal_value(g_format37_cache.sz) := 0 ;
331
332 else
333 g_format37_cache.bal_value(g_format37_cache.sz) :=
334 pay_mx_yearend_rep.get_ye_arch_bal_amt(p_payroll_action_id,p_person_id,p_effective_date,
335 'A_'||c_get_balances_rec.user_entity_name ) ;
336 end if;
337
338 END LOOP ;
339
340
341 g_format37_cache.sz := g_format37_cache.sz + 1;
342 g_format37_cache.payroll_action_id(g_format37_cache.sz) := p_payroll_action_id;
343 g_format37_cache.person_id(g_format37_cache.sz) := p_person_id ;
344 g_format37_cache.effective_date(g_format37_cache.sz) := p_effective_date ;
345 g_format37_cache.bal_name(g_format37_cache.sz) := 'PREV_ER_ISR_EARNINGS' ;
346 g_format37_cache.bal_value(g_format37_cache.sz) := l_prev_er_isr_earnings ;
347
348 g_format37_cache.sz := g_format37_cache.sz + 1;
349 g_format37_cache.payroll_action_id(g_format37_cache.sz) := p_payroll_action_id;
350 g_format37_cache.person_id(g_format37_cache.sz) := p_person_id ;
351 g_format37_cache.effective_date(g_format37_cache.sz) := p_effective_date ;
352 g_format37_cache.bal_name(g_format37_cache.sz) := 'PREV_ER_ISR_WITHHELD' ;
353 g_format37_cache.bal_value(g_format37_cache.sz) := l_prev_er_isr_withheld ;
354
355 g_format37_cache.sz := g_format37_cache.sz + 1;
356 g_format37_cache.payroll_action_id(g_format37_cache.sz) := p_payroll_action_id;
357 g_format37_cache.person_id(g_format37_cache.sz) := p_person_id ;
358 g_format37_cache.effective_date(g_format37_cache.sz) := p_effective_date ;
359 g_format37_cache.bal_name(g_format37_cache.sz) := 'PREV_ER_ISR_EXEMPT' ;
360 g_format37_cache.bal_value(g_format37_cache.sz) := l_prev_er_isr_exempt ;
361
362 -- derived balances
363 -- Sum of subject earnings caused for wages and salaries
364 -- j1 Sum of Subject Portions from h to i1
365 g_format37_cache.sz := g_format37_cache.sz + 1;
366 g_format37_cache.payroll_action_id(g_format37_cache.sz) := p_payroll_action_id;
367 g_format37_cache.person_id(g_format37_cache.sz) := p_person_id ;
368 g_format37_cache.effective_date(g_format37_cache.sz) := p_effective_date ;
369 g_format37_cache.bal_name(g_format37_cache.sz) := 'TOTAL_SUBJECT_EARNINGS' ;
370 g_format37_cache.bal_value(g_format37_cache.sz) :=
371 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
372 'A_YEAR_END_ISR_SUBJECT_FOR_FIXED_EARNINGS_PER_PDS_GRE_YTD') +
373 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
374 'A_YEAR_END_ISR_SUBJECT_FOR_CHRISTMAS_BONUS_PER_PDS_GRE_YTD') +
375 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
376 'A_YEAR_END_ISR_SUBJECT_FOR_TRAVEL_EXPENSES_PER_PDS_GRE_YTD') +
377 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
378 'A_YEAR_END_ISR_SUBJECT_FOR_OVERTIME_PER_PDS_GRE_YTD') +
379 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
380 'A_YEAR_END_ISR_SUBJECT_FOR_VACATION_PREMIUM_PER_PDS_GRE_YTD') +
381 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
382 'A_YEAR_END_ISR_SUBJECT_FOR_DOMINICAL_PREMIUM_PER_PDS_GRE_YTD') +
383 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
384 'A_YEAR_END_ISR_SUBJECT_FOR_PROFIT_SHARING_PER_PDS_GRE_YTD' ) +
385 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
386 'A_YEAR_END_ISR_SUBJECT_FOR_HEALTHCARE_REIMBURSEMENT_PER_PDS_GRE_YTD') +
387 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
388 'A_YEAR_END_ISR_SUBJECT_FOR_SAVINGS_FUND_PER_PDS_GRE_YTD') +
389 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
390 'A_YEAR_END_ISR_SUBJECT_FOR_SAVINGS_BOX_PER_PDS_GRE_YTD') +
391 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
392 'A_YEAR_END_ISR_SUBJECT_FOR_PANTRY_COUPONS_PER_PDS_GRE_YTD') +
393 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
394 'A_YEAR_END_ISR_SUBJECT_FOR_FUNERAL_AID_PER_PDS_GRE_YTD') +
395 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
396 'A_YEAR_END_ISR_SUBJECT_FOR_WORKER_CONTRIBUTION_PAID_BY_ER_PER_PDS_GRE_YTD' ) +
397 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
398 'A_YEAR_END_ISR_SUBJECT_FOR_PUNCTUALITY_INCENTIVE_PER_PDS_GRE_YTD') +
399 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
400 'A_YEAR_END_ISR_SUBJECT_FOR_LIFE_INSURANCE_PREMIUM_PER_PDS_GRE_YTD') +
401 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
402 'A_YEAR_END_ISR_SUBJECT_FOR_MAJOR_MEDICAL_EXPENSE_INSURANCE_PER_PDS_GRE_YTD') +
403 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
404 'A_YEAR_END_ISR_SUBJECT_FOR_RESTAURANT_COUPONS_PER_PDS_GRE_YTD') +
405 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
406 'A_YEAR_END_ISR_SUBJECT_FOR_GASOLINE_COUPONS_PER_PDS_GRE_YTD') +
407 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
408 'A_YEAR_END_ISR_SUBJECT_FOR_UNIFORM_COUPONS_PER_PDS_GRE_YTD') +
409 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
410 'A_YEAR_END_ISR_SUBJECT_FOR_RENTAL_AID_PER_PDS_GRE_YTD') +
411 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
412 'A_YEAR_END_ISR_SUBJECT_FOR_EDUCATIONAL_AID_PER_PDS_GRE_YTD' ) +
413 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
414 'A_YEAR_END_ISR_SUBJECT_FOR_GLASSES_AID_PER_PDS_GRE_YTD') +
415 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
416 'A_YEAR_END_ISR_SUBJECT_FOR_TRANSPORTATION_AID_PER_PDS_GRE_YTD' ) +
417 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
418 'A_YEAR_END_ISR_SUBJECT_FOR_UNION_QUOTA_PAID_BY_ER_PER_PDS_GRE_YTD') +
419 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
420 'A_YEAR_END_ISR_SUBJECT_FOR_DISABILITIES_SUBSIDY_PER_PDS_GRE_YTD' ) +
421 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
422 'A_YEAR_END_ISR_SUBJECT_FOR_CHILDREN_SCHOLARSHIP_PER_PDS_GRE_YTD' ) +
423 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
424 'PREV_ER_ISR_EARNINGS' ) +
425 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
426 'A_FORMAT_37_ISR_SUBJECT_FOR_OTHER_INCOME_PER_PDS_GRE_YTD' ) ;
427
428
429 -- Sum of exempt earnings caused for wages and salaries k1
430 g_format37_cache.sz := g_format37_cache.sz + 1;
431 g_format37_cache.payroll_action_id(g_format37_cache.sz) := p_payroll_action_id;
432 g_format37_cache.person_id(g_format37_cache.sz) := p_person_id ;
433 g_format37_cache.effective_date(g_format37_cache.sz) := p_effective_date ;
434 g_format37_cache.bal_name(g_format37_cache.sz) := 'TOTAL_EXEMPT_EARNINGS' ;
435 g_format37_cache.bal_value(g_format37_cache.sz) :=
436 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
437 'A_YEAR_END_ISR_EXEMPT_FOR_FIXED_EARNINGS_PER_PDS_GRE_YTD') +
438 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
439 'A_YEAR_END_ISR_EXEMPT_FOR_CHRISTMAS_BONUS_PER_PDS_GRE_YTD') +
440 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
441 'A_YEAR_END_ISR_EXEMPT_FOR_TRAVEL_EXPENSES_PER_PDS_GRE_YTD') +
442 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
443 'A_YEAR_END_ISR_EXEMPT_FOR_OVERTIME_PER_PDS_GRE_YTD') +
444 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
445 'A_YEAR_END_ISR_EXEMPT_FOR_VACATION_PREMIUM_PER_PDS_GRE_YTD') +
446 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
447 'A_YEAR_END_ISR_EXEMPT_FOR_DOMINICAL_PREMIUM_PER_PDS_GRE_YTD') +
448 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
449 'A_YEAR_END_ISR_EXEMPT_FOR_PROFIT_SHARING_PER_PDS_GRE_YTD' ) +
450 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
451 'A_YEAR_END_ISR_EXEMPT_FOR_HEALTHCARE_REIMBURSEMENT_PER_PDS_GRE_YTD') +
452 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
453 'A_YEAR_END_ISR_EXEMPT_FOR_SAVINGS_FUND_PER_PDS_GRE_YTD') +
454 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
455 'A_YEAR_END_ISR_EXEMPT_FOR_SAVINGS_BOX_PER_PDS_GRE_YTD') +
456 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
457 'A_YEAR_END_ISR_EXEMPT_FOR_PANTRY_COUPONS_PER_PDS_GRE_YTD') +
458 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
459 'A_YEAR_END_ISR_EXEMPT_FOR_FUNERAL_AID_PER_PDS_GRE_YTD') +
460 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
461 'A_YEAR_END_ISR_EXEMPT_FOR_WORKER_CONTRIBUTION_PAID_BY_ER_PER_PDS_GRE_YTD' ) +
462 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
463 'A_YEAR_END_ISR_EXEMPT_FOR_PUNCTUALITY_INCENTIVE_PER_PDS_GRE_YTD') +
464 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
465 'A_YEAR_END_ISR_EXEMPT_FOR_LIFE_INSURANCE_PREMIUM_PER_PDS_GRE_YTD') +
466 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
467 'A_YEAR_END_ISR_EXEMPT_FOR_MAJOR_MEDICAL_EXPENSE_INSURANCE_PER_PDS_GRE_YTD') +
468 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
469 'A_YEAR_END_ISR_EXEMPT_FOR_RESTAURANT_COUPONS_PER_PDS_GRE_YTD') +
470 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
471 'A_YEAR_END_ISR_EXEMPT_FOR_GASOLINE_COUPONS_PER_PDS_GRE_YTD') +
472 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
473 'A_YEAR_END_ISR_EXEMPT_FOR_UNIFORM_COUPONS_PER_PDS_GRE_YTD') +
474 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
475 'A_YEAR_END_ISR_EXEMPT_FOR_RENTAL_AID_PER_PDS_GRE_YTD') +
476 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
477 'A_YEAR_END_ISR_EXEMPT_FOR_EDUCATIONAL_AID_PER_PDS_GRE_YTD' ) +
478 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
479 'A_YEAR_END_ISR_SUBJECT_FOR_GLASSES_AID_PER_PDS_GRE_YTD') +
480 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
481 'A_YEAR_END_ISR_EXEMPT_FOR_TRANSPORTATION_AID_PER_PDS_GRE_YTD' ) +
482 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
483 'A_YEAR_END_ISR_EXEMPT_FOR_UNION_QUOTA_PAID_BY_ER_PER_PDS_GRE_YTD') +
484 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
485 'A_YEAR_END_ISR_EXEMPT_FOR_DISABILITIES_SUBSIDY_PER_PDS_GRE_YTD' ) +
486 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
487 'A_YEAR_END_ISR_EXEMPT_FOR_CHILDREN_SCHOLARSHIP_PER_PDS_GRE_YTD' ) +
488 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
489 'PREV_ER_ISR_EXEMPT' ) +
490 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
491 'A_FORMAT_37_ISR_EXEMPT_FOR_OTHER_INCOME_PER_PDS_GRE_YTD') ;
492
493 -- Total earnings caused for salary, wages and assimilated concepts
494 -- A = O + P + a + i + m + Q1 + R1
495 g_format37_cache.sz := g_format37_cache.sz + 1;
496 g_format37_cache.payroll_action_id(g_format37_cache.sz) := p_payroll_action_id;
497 g_format37_cache.person_id(g_format37_cache.sz) := p_person_id ;
498 g_format37_cache.effective_date(g_format37_cache.sz) := p_effective_date ;
499 g_format37_cache.bal_name(g_format37_cache.sz) := 'TOT_EARNING_ASSI_CONCEPTS' ;
500 g_format37_cache.bal_value(g_format37_cache.sz) :=
501 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
502 'A_FORMAT_37_RETIREMENT_DAILY_EARNINGS_IN_ONE_PAYMENT_PER_PDS_GRE_YTD') +
503 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
504 'A_FORMAT_37_RETIREMENT_EARNINGS_IN_PARTIAL_PAYMENTS_PER_PDS_GRE_YTD' ) +
505 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,'A_AMENDS_PER_PDS_GRE_YTD') +
506 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
507 'A_FORMAT_37_ASSIMILATED_EARNINGS_PER_PDS_GRE_YTD' ) +
508 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
509 'A_YEAR_END_STOCK_OPTIONS_CUMULATIVE_INCOME_PER_PDS_GRE_YTD' ) +
510 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,'TOTAL_SUBJECT_EARNINGS' ) +
511 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,'TOTAL_EXEMPT_EARNINGS') ;
512
513
514 -- c = Exempt earnings a - d
515 -- a Total amount paid Amends
516 -- d Subject earnings ISR Subject for Amends
517 g_format37_cache.sz := g_format37_cache.sz + 1;
518 g_format37_cache.payroll_action_id(g_format37_cache.sz) := p_payroll_action_id;
519 g_format37_cache.person_id(g_format37_cache.sz) := p_person_id ;
520 g_format37_cache.effective_date(g_format37_cache.sz) := p_effective_date ;
521 g_format37_cache.bal_name(g_format37_cache.sz) := 'ISR_EXEMPT_FOR_AMENDS' ;
522 g_format37_cache.bal_value(g_format37_cache.sz) :=
523 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,'A_AMENDS_PER_PDS_GRE_YTD' ) -
524 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,'A_ISR_SUBJECT_FOR_AMENDS_PER_PDS_GRE_YTD') ;
525
526 -- Exempt earnings
527 -- C = T + c + R1
528 g_format37_cache.sz := g_format37_cache.sz + 1;
529 g_format37_cache.payroll_action_id(g_format37_cache.sz) := p_payroll_action_id;
530 g_format37_cache.person_id(g_format37_cache.sz) := p_person_id ;
531 g_format37_cache.effective_date(g_format37_cache.sz) := p_effective_date ;
532 g_format37_cache.bal_name(g_format37_cache.sz) := 'TOT_EXEMPT_EARNINGS' ;
533 g_format37_cache.bal_value(g_format37_cache.sz) :=
534 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
535 'A_FORMAT_37_RETIREMENT_EXEMPT_EARNINGS_PER_PDS_GRE_YTD' ) +
536 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,'ISR_EXEMPT_FOR_AMENDS') +
537 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,'TOTAL_EXEMPT_EARNINGS') ;
538
539
540 -- Non cumulative earnings
541 -- W = U - V
542 g_format37_cache.sz := g_format37_cache.sz + 1;
543 g_format37_cache.payroll_action_id(g_format37_cache.sz) := p_payroll_action_id;
544 g_format37_cache.person_id(g_format37_cache.sz) := p_person_id ;
545 g_format37_cache.effective_date(g_format37_cache.sz) := p_effective_date ;
546 g_format37_cache.bal_name(g_format37_cache.sz) := 'RET_NON_CUMULATIVE_EARNINGS' ;
547 g_format37_cache.bal_value(g_format37_cache.sz) :=
548 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
549 'A_FORMAT_37_RETIREMENT_TAXABLE_EARNINGS_PER_PDS_GRE_YTD' ) -
550 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
551 'A_FORMAT_37_RETIREMENT_CUMULATIVE_EARNINGS_PER_PDS_GRE_YTD') ;
552
553 -- need to calculate d first using
554 -- g Non-cumulative earnings IF d <> e AND d > e THEN g = d minus e ELSE g = 0
555 --d Subject earnings ISR Subject for Amends
556 --e Cumulative earnings (last monthly ordinary salary)
557 g_format37_cache.sz := g_format37_cache.sz + 1;
558 g_format37_cache.payroll_action_id(g_format37_cache.sz) := p_payroll_action_id;
559 g_format37_cache.person_id(g_format37_cache.sz) := p_person_id ;
560 g_format37_cache.effective_date(g_format37_cache.sz) := p_effective_date ;
561 g_format37_cache.bal_name(g_format37_cache.sz) := 'NON_CUMULATIVE_AMENDS' ;
562 if ( get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,'A_ISR_SUBJECT_FOR_AMENDS_PER_PDS_GRE_YTD' ) <>
563 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,'A_LAST_MONTHLY_ORDINARY_SALARY_PER_PDS_GRE_YTD' )
564 ) AND
565 ( get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,'A_ISR_SUBJECT_FOR_AMENDS_PER_PDS_GRE_YTD' ) >
566 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,'A_LAST_MONTHLY_ORDINARY_SALARY_PER_PDS_GRE_YTD' )
567 ) THEN
568
569 g_format37_cache.bal_value(g_format37_cache.sz) :=
570 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,'A_ISR_SUBJECT_FOR_AMENDS_PER_PDS_GRE_YTD' ) -
571 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,'A_LAST_MONTHLY_ORDINARY_SALARY_PER_PDS_GRE_YTD' ) ;
572
573 else
574 g_format37_cache.bal_value(g_format37_cache.sz) := 0 ;
575 end if;
576
577
578 -- Non cumulative earnings
579 -- D Non cumulative earnings W + g
580 -- W Non cumulative earnings U - V
581 -- g Non-cumulative earnings IF d <> e AND d > e THEN g = d - e ELSE g = 0
582 g_format37_cache.sz := g_format37_cache.sz + 1;
583 g_format37_cache.payroll_action_id(g_format37_cache.sz) := p_payroll_action_id;
584 g_format37_cache.person_id(g_format37_cache.sz) := p_person_id ;
585 g_format37_cache.effective_date(g_format37_cache.sz) := p_effective_date ;
586 g_format37_cache.bal_name(g_format37_cache.sz) := 'TOT_NON_CUMULATIVE_EARNINGS' ;
587 g_format37_cache.bal_value(g_format37_cache.sz) :=
588 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,'RET_NON_CUMULATIVE_EARNINGS') +
589 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,'NON_CUMULATIVE_AMENDS') ;
590
591 /*Bug#:9171641:Calculate 'Total amount of deductable voluntary contributions' */
592 -- Total amount of deductable voluntary contributions
593 -- E = TOT_DED_VOL_CONTRIBUTION
594
595 g_format37_cache.sz := g_format37_cache.sz + 1;
596 g_format37_cache.payroll_action_id(g_format37_cache.sz) := p_payroll_action_id;
597 g_format37_cache.person_id(g_format37_cache.sz) := p_person_id ;
598 g_format37_cache.effective_date(g_format37_cache.sz) := p_effective_date ;
599 g_format37_cache.bal_name(g_format37_cache.sz) := 'TOT_DED_VOL_CONTRIBUTION' ;
600 g_format37_cache.bal_value(g_format37_cache.sz) :=
601 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
602 'A_FORMAT_37_VOLUNTARY_CONTRIBUTIONS_ER_PER_PDS_GRE_YTD');
603 /*Bug#:9171641 */
604
605 -- Cumulative earnings
606 -- F = A -B - C - D -E
607
608 g_format37_cache.sz := g_format37_cache.sz + 1;
609 g_format37_cache.payroll_action_id(g_format37_cache.sz) := p_payroll_action_id;
610 g_format37_cache.person_id(g_format37_cache.sz) := p_person_id ;
611 g_format37_cache.effective_date(g_format37_cache.sz) := p_effective_date ;
612 g_format37_cache.bal_name(g_format37_cache.sz) := 'TOT_CUMULATIVE_EARNINGS' ;
613 g_format37_cache.bal_value(g_format37_cache.sz) :=
614 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,'TOT_EARNING_ASSI_CONCEPTS') -
615 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
616 'A_EMPLOYEE_STATE_TAX_WITHHELD_PER_PDS_GRE_YTD') -
617 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,'TOT_EXEMPT_EARNINGS') -
618 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,'TOT_NON_CUMULATIVE_EARNINGS')-
619 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,'TOT_DED_VOL_CONTRIBUTION') ;
620 /*Bug#:9171641: Subtract 'Cumulative earnings' by TOT_DED_VOL_CONTRIBUTION */
621
622
623 -- Tax on cumulative earnings
624 -- K = F - G
625 g_format37_cache.sz := g_format37_cache.sz + 1;
626 g_format37_cache.payroll_action_id(g_format37_cache.sz) := p_payroll_action_id;
627 g_format37_cache.person_id(g_format37_cache.sz) := p_person_id ;
628 g_format37_cache.effective_date(g_format37_cache.sz) := p_effective_date ;
629 g_format37_cache.bal_name(g_format37_cache.sz) := 'ISR_ON_CUMULATIVE_EARNINGS' ;
630 if l_annual_tax_calc_flag = 'Y' then
631 ln_isr_on_cum_earn :=
632 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,'A_ISR_CALCULATED_PER_PDS_GRE_YTD') -
633 --get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,'A_ISR_CREDITABLE_SUBSIDY_PER_PDS_GRE_YTD') -
634 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,'A_ISR_SUBSIDY_FOR_EMPLOYMENT_PER_PDS_GRE_YTD');
635
636 if ln_isr_on_cum_earn < 0 then
637 g_format37_cache.bal_value(g_format37_cache.sz) := 0;
638 else
639 g_format37_cache.bal_value(g_format37_cache.sz) := ln_isr_on_cum_earn;
640 end if;
641
642 else
643 g_format37_cache.bal_value(g_format37_cache.sz) := 0 ;
644 end if;
645
646
647 -- Tax on income caused in fiscal year
648 -- L = J + K
649 -- M = K + L
650 g_format37_cache.sz := g_format37_cache.sz + 1;
651 g_format37_cache.payroll_action_id(g_format37_cache.sz) := p_payroll_action_id;
652 g_format37_cache.person_id(g_format37_cache.sz) := p_person_id ;
653 g_format37_cache.effective_date(g_format37_cache.sz) := p_effective_date ;
654 g_format37_cache.bal_name(g_format37_cache.sz) := 'TAX_ON_INCOME_FISCAL_YEAR' ;
655 if l_annual_tax_calc_flag = 'Y' then
656 g_format37_cache.bal_value(g_format37_cache.sz) :=
657 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,'ISR_ON_CUMULATIVE_EARNINGS') +
658 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
659 'A_FORMAT_37_ISR_ON_NON_CUMULATIVE_EARNINGS_PER_PDS_GRE_YTD') ;
660 else
661 g_format37_cache.bal_value(g_format37_cache.sz) := 0 ;
662 end if ;
663
664 -- U1 Tax withheld in fiscal year ISR Withheld - h
665 -- ISR Withheld
666 -- h Tax withheld ISR Withheld for Amends
667 g_format37_cache.sz := g_format37_cache.sz + 1;
668 g_format37_cache.payroll_action_id(g_format37_cache.sz) := p_payroll_action_id;
669 g_format37_cache.person_id(g_format37_cache.sz) := p_person_id ;
670 g_format37_cache.effective_date(g_format37_cache.sz) := p_effective_date ;
671 g_format37_cache.bal_name(g_format37_cache.sz) := 'TAX_WITHHELD_IN_FISCAL_YEAR' ;
672 g_format37_cache.bal_value(g_format37_cache.sz) :=
673 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
674 'A_ISR_WITHHELD_PER_PDS_GRE_YTD') -
675 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
676 'A_ISR_WITHHELD_FOR_AMENDS_PER_PDS_GRE_YTD') ;
677
678 -- Tax withheld
679 -- N = x + h + j + n + U1 + V1
680
681 g_format37_cache.sz := g_format37_cache.sz + 1;
682 g_format37_cache.payroll_action_id(g_format37_cache.sz) := p_payroll_action_id;
683 g_format37_cache.person_id(g_format37_cache.sz) := p_person_id ;
684 g_format37_cache.effective_date(g_format37_cache.sz) := p_effective_date ;
685 g_format37_cache.bal_name(g_format37_cache.sz) := 'ISR_TAX_WITHHELD' ;
686 g_format37_cache.bal_value(g_format37_cache.sz) :=
687 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
688 'A_FORMAT_37_ISR_WITHHELD_FOR_RETIREMENT_EARNINGS_PER_PDS_GRE_YTD') +
689 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
690 'A_ISR_WITHHELD_FOR_AMENDS_PER_PDS_GRE_YTD') +
691 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
692 'A_FORMAT_37_ISR_WITHHELD_FOR_ASSIMILATED_EARNINGS_PER_PDS_GRE_YTD') +
693 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
694 'A_YEAR_END_STOCK_OPTIONS_ISR_WITHHELD_PER_PDS_GRE_YTD') +
695 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,
696 'TAX_WITHHELD_IN_FISCAL_YEAR') +
697 get_cache_balance(p_payroll_action_id,p_person_id,p_effective_date,'PREV_ER_ISR_WITHHELD') ;
698
699 end load_bal;
700
701 /******************************************************************
702 Name : bal_loaded
703 Purpose : function to check whether the balances loaded to pl/sql
704 table or not
705 ******************************************************************/
706 function bal_loaded(p_payroll_action_id in number,
707 p_person_id in number,
708 p_effective_date in date
709 )
710 return boolean is
711
712 l_bal_loaded boolean ;
713 begin
714 l_bal_loaded := FALSE ;
715
716 if g_format37_cache.sz is not null then
717
718 for ctr in 1..g_format37_cache.sz loop
719
720 if ( g_format37_cache.payroll_action_id(ctr) is null
721 and g_format37_cache.person_id(ctr) is null
722 and g_format37_cache.effective_date(ctr) is null ) or
723 (g_format37_cache.payroll_action_id(ctr) = p_payroll_action_id
724 and g_format37_cache.person_id(ctr) = p_person_id
725 and g_format37_cache.effective_date(ctr) = p_effective_date) then
726
727 l_bal_loaded := TRUE ;
728
729 end if;
730
731 end loop ;
732
733 end if;
734
735 return l_bal_loaded ;
736
737 end bal_loaded ;
738
739 /******************************************************************
740 Name : get_f37_balance
741 Purpose : retruns format37 balance. called from the
742 pay_mx_isr_tax_format_v
743 ******************************************************************/
744 function get_f37_balance(p_payroll_action_id in number,
745 p_person_id in number,
746 p_effective_date in date,
747 p_bal_name in varchar2 )
748 return number is
749
750 l_bal_amt number;
751 l_bal_loaded boolean ;
752
753 begin
754 --
755
756
757 l_bal_amt := 0 ;
758
759
760 l_bal_loaded := bal_loaded(p_payroll_action_id, p_person_id,p_effective_date ) ;
761
762 if NOT l_bal_loaded then
763
764 load_bal(p_payroll_action_id, p_person_id,p_effective_date );
765
766 end if;
767
768 l_bal_amt := get_cache_balance(p_payroll_action_id, p_person_id,p_effective_date,p_bal_name ) ;
769
770 return l_bal_amt;
771
772 end get_f37_balance ;
773
774
775 end pay_mx_yearend_rep;