[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_SPAY_EFILE_FUN_PKG
Source
1 package body pay_kr_spay_efile_fun_pkg as
2 /*$Header: pykrspen.pkb 120.5 2011/02/18 19:27:30 pnethaga ship $ */
3
4 FUNCTION get_prev_emp_count (p_assignment_action_id IN NUMBER) RETURN NUMBER
5 IS
6 l_prev_emp_count NUMBER(4);
7 cursor csr_get_prev_emp_count is
8 select
9 nvl(count(fue.user_entity_id),0) prev_emp_count
10 from ff_Archive_items fai
11 ,ff_user_entities fue
12 where fue.user_entity_id = fai.user_entity_id
13 and fue.user_entity_name = 'X_KR_PREV_BP_NUMBER'
14 and fai.context1 = p_assignment_action_id
15 group by fai.context1;
16 BEGIN
17 open csr_get_prev_emp_count;
18 fetch csr_get_prev_emp_count into l_prev_emp_count;
19 close csr_get_prev_emp_count;
20
21 return l_prev_emp_count;
22 EXCEPTION
23 WHEN NO_DATA_FOUND THEN
24 l_prev_emp_count := 0;
25 return l_prev_emp_count;
26 END;
27 --
28 /***************************************************************************
29 Function returns separation pay amount after considering separation pension.
30 p_amount => Total Earning by working places.
31 If "Amount Expected" is present, return "Receivable Separation Pay"
32 Else directly return p_amount.
33 ***************************************************************************/
34 function get_sep_pay_amount (
35 p_assact in number,
36 p_amount in number
37 ) return number
38 is
39 l_amount_expected number;
40 l_deferred_amount number;
41 l_deferred_amount_prev number;
42 l_received_amount number;
43 l_received_amount_prev number;
44 l_receivable_sep_pay number;
45 l_amount_expected_prev number; -- Bug 9247404
46 l_return number;
47
48 cursor csr_sep_amounts is
49 select fai1.value nst_amount_expected,
50 fai2.value nst_receivable_sep_pay,
51 fai3.value nst_amount_expected_prev,
52 fai4.value,
53 fai5.value,
54 fai6.value,
55 fai7.value
56 from ff_Archive_items fai1,
57 ff_user_entities fue1,
58 ff_Archive_items fai2,
59 ff_user_entities fue2,
60 ff_Archive_items fai3,
61 ff_user_entities fue3,
62 ff_Archive_items fai4,
63 ff_user_entities fue4,
64 ff_Archive_items fai5,
65 ff_user_entities fue5,
66 ff_Archive_items fai6,
67 ff_user_entities fue6,
68 ff_Archive_items fai7,
69 ff_user_entities fue7
70
71 where fue1.user_entity_name = 'A_AMOUNT_EXPECTED_ASG_RUN'
72 and fue1.user_entity_id = fai1.user_entity_id
73 and fai1.context1 = p_assact
74 and fue2.user_entity_name = 'A_RECEIVABLE_SEPARATION_PAY_ASG_RUN'
75 and fue2.user_entity_id = fai2.user_entity_id
76 and fai2.context1 = fai1.context1
77 -- Bug 9247404
78 and fue3.user_entity_name = 'A_PREV_SEP_PENS_DTLS_AMT_EXP_STAT_SEP_ENTRY_VALUE'
79 and fue3.user_entity_id = fai3.user_entity_id
80 and fai3.context1 = fai1.context1
81
82 and fue4.user_entity_name = 'A_DEFERRED_AMOUNT_STAT_ASG_RUN'
83 and fue4.user_entity_id = fai4.user_entity_id
84 and fai4.context1 = fai1.context1
85
86 and fue5.user_entity_name = 'A_RECEIVED_AMOUNT_STAT_ASG_RUN'
87 and fue5.user_entity_id = fai5.user_entity_id
88 and fai5.context1 = fai1.context1
89
90 and fue6.user_entity_name = 'A_PREV_SEP_PENS_DTLS_DEFERRED_AMOUNT_STAT_ENTRY_VALUE'
91 and fue6.user_entity_id = fai6.user_entity_id
92 and fai6.context1 = fai1.context1
93
94 and fue7.user_entity_name = 'A_PREV_SEP_PENS_DTLS_RECEIVED_AMOUNT_STAT_ENTRY_VALUE'
95 and fue7.user_entity_id = fai7.user_entity_id
96 and fai7.context1 = fai1.context1;
97 begin
98 l_amount_expected := 0;
99 l_receivable_sep_pay := 0;
100 l_amount_expected_prev := 0;
101 l_deferred_amount := 0;
102 l_deferred_amount_prev := 0;
103 l_received_amount := 0;
104 l_received_amount_prev := 0;
105
106 open csr_sep_amounts;
107 fetch csr_sep_amounts into l_amount_expected, l_receivable_sep_pay, l_amount_expected_prev, l_deferred_amount
108 ,l_received_amount,l_deferred_amount_prev,l_received_amount_prev;
109 close csr_sep_amounts;
110
111 if (nvl(l_amount_expected,0) + nvl(l_amount_expected_prev,0) + nvl(l_deferred_amount,0) + nvl(l_received_amount,0)
112 + nvl(l_deferred_amount_prev,0) + nvl(l_received_amount_prev,0)) > 0 then
113 l_return := l_receivable_sep_pay;
114 else
115 l_return := p_amount;
116 end if;
117
118 return l_return;
119
120 end get_sep_pay_amount;
121
122 -- Bug 9409509
123 function get_nsep_pay_amount (
124 p_assact in number,
125 p_amount in number
126 ) return number
127 is
128 l_nst_amount_expected number;
129 l_nst_receivable_sep_pay number;
130 l_nst_amount_expected_prev number; -- Bug 9247404
131 l_nst_deferred_amount number;
132 l_nst_deferred_amount_prev number;
133 l_nst_received_amount number;
134 l_nst_received_amount_prev number;
135 l_return number;
136
137 cursor csr_nsep_amounts is
138 select fai1.value amount_expected,
139 fai2.value receivable_sep_pay,
140 fai3.value amount_expected_prev,
141 fai4.value,
142 fai5.value,
143 fai6.value,
144 fai7.value
145 from ff_Archive_items fai1,
146 ff_user_entities fue1,
147 ff_Archive_items fai2,
148 ff_user_entities fue2,
149 ff_Archive_items fai3,
150 ff_user_entities fue3,
151 ff_Archive_items fai4,
152 ff_user_entities fue4,
153 ff_Archive_items fai5,
154 ff_user_entities fue5,
155 ff_Archive_items fai6,
156 ff_user_entities fue6,
157 ff_Archive_items fai7,
158 ff_user_entities fue7
159
160 where fue1.user_entity_name = 'A_AMOUNT_EXPECTED_NONSTAT_ASG_RUN'
161 and fue1.user_entity_id = fai1.user_entity_id
162 and fai1.context1 = p_assact
163 and fue2.user_entity_name = 'A_RECEIVABLE_NON_STAT_SEP_PAY_ASG_RUN'
164 and fue2.user_entity_id = fai2.user_entity_id
165 and fai2.context1 = fai1.context1
166 -- Bug 9247404
167 and fue3.user_entity_name = 'A_PREV_SEP_PENS_DTLS_AMT_EXP_NONSTAT_SEP_ENTRY_VALUE'
168 and fue3.user_entity_id = fai3.user_entity_id
169 and fai3.context1 = fai1.context1
170
171 and fue4.user_entity_name = 'A_DEFERRED_AMOUNT_NONSTAT_ASG_RUN'
172 and fue4.user_entity_id = fai4.user_entity_id
173 and fai4.context1 = fai1.context1
174
175 and fue5.user_entity_name = 'A_RECEIVED_AMOUNT_NONSTAT_ASG_RUN'
176 and fue5.user_entity_id = fai5.user_entity_id
177 and fai5.context1 = fai1.context1
178
179 and fue6.user_entity_name = 'A_PREV_SEP_PENS_DTLS_DEFERRED_AMOUNT_NONSTAT_ENTRY_VALUE'
180 and fue6.user_entity_id = fai6.user_entity_id
181 and fai6.context1 = fai1.context1
182
183 and fue7.user_entity_name = 'A_PREV_SEP_PENS_DTLS_RECEIVED_AMOUNT_NONSTAT_ENTRY_VALUE'
184 and fue7.user_entity_id = fai7.user_entity_id
185 and fai7.context1 = fai1.context1;
186 begin
187 l_nst_amount_expected := 0;
188 l_nst_receivable_sep_pay := 0;
189 l_nst_amount_expected_prev := 0;
190 l_nst_deferred_amount := 0;
191 l_nst_deferred_amount_prev := 0;
192 l_nst_received_amount := 0;
193 l_nst_received_amount_prev := 0;
194
195 open csr_nsep_amounts;
196 fetch csr_nsep_amounts into l_nst_amount_expected, l_nst_receivable_sep_pay, l_nst_amount_expected_prev,
197 l_nst_deferred_amount,l_nst_received_amount,l_nst_deferred_amount_prev,l_nst_received_amount_prev;
198 close csr_nsep_amounts;
199
200 if (nvl(l_nst_amount_expected,0) + nvl(l_nst_amount_expected_prev,0)+ nvl(l_nst_deferred_amount,0) + nvl(l_nst_received_amount,0)
201 + nvl(l_nst_deferred_amount_prev,0) + nvl(l_nst_received_amount_prev,0)) > 0 then
202 l_return := l_nst_receivable_sep_pay;
203 else
204 l_return := p_amount;
205 end if;
206
207 return l_return;
208
209 end get_nsep_pay_amount;
210
211 function get_archive_item( p_assact in number) return varchar2
212 is
213 l_nst_receivable_pay number;
214 l_receivable_pay number;
215 l_nst_taxable_ear number;
216 l_taxable_ear number;
217 l_return varchar2(255);
218
219 cursor csr_nsep_amounts is
220 select fai1.value,
221 fai2.value,
222 fai3.value,
223 fai4.value
224 from ff_Archive_items fai1,
225 ff_user_entities fue1,
226 ff_Archive_items fai2,
227 ff_user_entities fue2,
228 ff_Archive_items fai3,
229 ff_user_entities fue3,
230 ff_Archive_items fai4,
231 ff_user_entities fue4
232 where fue1.user_entity_name = 'A_RECEIVABLE_NON_STAT_SEP_PAY_ASG_RUN'
233 and fue1.user_entity_id = fai1.user_entity_id
234 and fai1.context1 = p_assact
235 -- Bug 9247404
236 and fue2.user_entity_name = 'A_NON_STAT_SEP_PAY_TAXABLE_EARNINGS_ASG_RUN'
237 and fue2.user_entity_id = fai2.user_entity_id
238 and fai2.context1 = fai1.context1
239
240 and fue3.user_entity_name = 'A_TAXABLE_EARNINGS_WI_PREV_ASG_RUN'
241 and fue3.user_entity_id = fai3.user_entity_id
242 and fai3.context1 = fai1.context1
243
244 and fue4.user_entity_name = 'A_RECEIVABLE_SEPARATION_PAY_ASG_RUN'
245 and fue4.user_entity_id = fai4.user_entity_id
246 and fai4.context1 = fai1.context1;
247
248 begin
249 l_nst_receivable_pay := 0;
250 l_receivable_pay := 0;
251 l_nst_taxable_ear := 0;
252 l_taxable_ear := 0;
253
254 open csr_nsep_amounts;
255 fetch csr_nsep_amounts into l_nst_receivable_pay,l_nst_taxable_ear, l_taxable_ear,l_receivable_pay ;
256 close csr_nsep_amounts;
257 if l_nst_receivable_pay > 0 then
258 l_return := 'A_RECEIVABLE_NON_STAT_SEP_PAY_ASG_RUN';
259 else
260 l_return := 'A_NON_STAT_SEP_PAY_TAXABLE_EARNINGS_ASG_RUN';
261 end if;
262
263 if l_receivable_pay > 0 then
264 l_return := 'A_RECEIVABLE_SEPARATION_PAY_ASG_RUN';
265 else
266 l_return := 'A_TAXABLE_EARNINGS_WI_PREV_ASG_RUN';
267 end if;
268
269 return l_return;
270
271 end get_archive_item;
272 -- End of Bug 9409509
273 -- Bug 10216334
274 function get_emp_count(p_user_entity_name in varchar2 ,p_assact in number) return number
275 is
276 l_nst_taxable number;
277 l_st_taxable number;
278 l_nst_receivable number;
279 l_st_receivable number;
280 l_return number;
281
282 cursor csr_amounts is
283 select fai1.value,
284 fai2.value,
285 fai3.value,
286 fai4.value
287
288 from ff_Archive_items fai1,
289 ff_user_entities fue1,
290 ff_Archive_items fai2,
291 ff_user_entities fue2,
292 ff_Archive_items fai3,
293 ff_user_entities fue3,
294 ff_Archive_items fai4,
295 ff_user_entities fue4
296
297 where fue1.user_entity_name = 'A_NON_STAT_SEP_PAY_TAXABLE_EARNINGS_ASG_RUN'
298 and fue1.user_entity_id = fai1.user_entity_id
299 and fai1.context1 = p_assact
300
301 and fue2.user_entity_name = 'A_TAXABLE_EARNINGS_WI_PREV_ASG_RUN'
302 and fue2.user_entity_id = fai2.user_entity_id
303 and fai2.context1 = fai1.context1
304
305 and fue3.user_entity_name = 'A_RECEIVABLE_NON_STAT_SEP_PAY_ASG_RUN'
306 and fue3.user_entity_id = fai3.user_entity_id
307 and fai3.context1 = fai1.context1
308
309 and fue4.user_entity_name = 'A_RECEIVABLE_SEPARATION_PAY_ASG_RUN'
310 and fue4.user_entity_id = fai4.user_entity_id
311 and fai4.context1 = fai1.context1;
312 begin
313 l_nst_taxable := 0;
314 l_st_taxable := 0;
315 l_nst_receivable := 0;
316 l_st_receivable := 0;
317 l_return := 0;
318
319 open csr_amounts;
320 fetch csr_amounts into l_nst_taxable, l_st_taxable, l_nst_receivable, l_st_receivable;
321 close csr_amounts;
322
323 if p_user_entity_name = 'A_NON_STAT_SEP_PAY_TAXABLE_EARNINGS_ASG_RUN' then
324 if l_nst_taxable + l_nst_receivable > 0 then
325 l_return := 1;
326 elsif l_st_taxable + l_st_receivable > 0 then
327 l_return := 0;
328 end if;
329 elsif p_user_entity_name = 'A_TAXABLE_EARNINGS_WI_PREV_ASG_RUN' then
330 if l_nst_taxable + l_nst_receivable > 0 then
331 l_return := 0;
332 elsif l_st_taxable + l_st_receivable > 0 then
333 l_return := 1;
334 end if;
335 end if;
336
337 return l_return;
338
339 end get_emp_count;
340
341 end pay_kr_spay_efile_fun_pkg;