[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_YEA_FORM_PKG
Source
1 package body pay_kr_yea_form_pkg as
2 /* $Header: pykryeaf.pkb 120.5 2006/09/22 10:58:21 pdesu noship $ */
3 -------------------------------------------------------------------------------------------------------
4 procedure chk_detail_medical_record
5 (
6 p_effective_date in date,
7 p_assignment_id in per_assignment_extra_info.assignment_id%type,
8 p_provider_reg_no in per_assignment_extra_info.aei_information1%type,
9 p_provider_reg_name in per_assignment_extra_info.aei_information1%type,
10 p_res_reg_no in per_assignment_extra_info.aei_information1%type,
11 p_relationship in per_assignment_extra_info.aei_information1%type,
12 p_disabled_aged in per_assignment_extra_info.aei_information1%type,
13 p_total_employee in out nocopy per_assignment_extra_info.aei_information1%type,
14 p_total_dependent in out nocopy per_assignment_extra_info.aei_information1%type,
15 p_total_aged in out nocopy per_assignment_extra_info.aei_information1%type,
16 p_total_disabled in out nocopy per_assignment_extra_info.aei_information1%type,
17 p_total_card_exp in out nocopy per_assignment_extra_info.aei_information1%type,
18 p_inv_provider_name out nocopy varchar2,
19 p_inv_relationship out nocopy varchar2,
20 p_inv_aged_disabled out nocopy varchar2
21 )
22 is
23 cursor csr_dup_provider_name is
24 select 'Y'
25 from per_assignment_extra_info
26 where
27 assignment_id = p_assignment_id and
28 information_type = 'KR_YEA_DETAIL_MEDICAL_EXP_INFO' and
29 aei_information5 = p_provider_reg_no and
30 aei_information6 <> p_provider_reg_name and
31 trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') =
32 trunc(p_effective_date, 'YYYY');
33
34 cursor csr_dup_relation_aged_disabled is
35 select
36 decode(aei_information7,p_relationship,'N','Y') inv_relationship,
37 decode(aei_information9,p_disabled_aged,'N','Y') inv_aged_disabled
38 from per_assignment_extra_info
39 where
40 assignment_id = p_assignment_id and
41 information_type = 'KR_YEA_DETAIL_MEDICAL_EXP_INFO' and
42 aei_information8 = p_res_reg_no and
43 trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') =
44 trunc(p_effective_date, 'YYYY');
45
46 Cursor csr_medical_totals is
47 select aei_information24, -- employee
48 aei_information7, -- dependent
49 aei_information9, -- aged
50 aei_information8 -- disabled
51 from per_assignment_extra_info pai
52 where
53 pai.assignment_id = p_assignment_id and
54 pai.information_type = 'KR_YEA_SP_TAX_EXEM_INFO' and
55 trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') =
56 trunc(p_effective_date, 'YYYY');
57 -- Bug 4704848
58 Cursor csr_total_card_exp is
59 select aei_information3 -- card expense
60 from per_assignment_extra_info pai
61 where
62 pai.assignment_id = p_assignment_id and
63 pai.information_type = 'KR_YEA_SP_TAX_EXEM_INFO2' and
64 trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') =
65 trunc(p_effective_date, 'YYYY');
66
67 Begin
68 --
69 p_inv_provider_name := 'N';
70 p_inv_relationship := 'N';
71 p_inv_aged_disabled := 'N';
72
73 open csr_dup_provider_name;
74 fetch csr_dup_provider_name into
75 p_inv_provider_name;
76 --
77 if csr_dup_provider_name%notfound then
78 p_inv_provider_name := 'N';
79 end if;
80 close csr_dup_provider_name;
81
82 open csr_dup_relation_aged_disabled;
83 fetch csr_dup_relation_aged_disabled into
84 p_inv_relationship, p_inv_aged_disabled;
85 --
86 if csr_dup_relation_aged_disabled%notfound then
87 p_inv_relationship := 'N';
88 p_inv_aged_disabled := 'N';
89 end if;
90 close csr_dup_relation_aged_disabled;
91
92 if (p_inv_provider_name = 'Y' or p_inv_relationship = 'Y' or p_inv_aged_disabled = 'Y') then
93 open csr_medical_totals;
94 fetch csr_medical_totals into
95 p_total_employee,
96 p_total_dependent,
97 p_total_aged,
98 p_total_disabled;
99
100 -- Bug 4704848
101 open csr_total_card_exp;
102 fetch csr_total_card_exp into p_total_card_exp;
103
104 if csr_medical_totals%notfound then
105 p_total_employee := null;
106 p_total_dependent := null;
107 p_total_aged := null;
108 p_total_disabled := null;
109 end if;
110
111 if csr_total_card_exp%notfound then
112 p_total_card_exp := null;
113 end if;
114 --
115 close csr_medical_totals;
116 close csr_total_card_exp;
117 end if;
118 End;
119 -------------------------------------------------------------------------------------------------------
120 function chk_dup_recipient_name (
121 p_effective_date in date,
122 p_assignment_id in per_assignment_extra_info.assignment_id%type,
123 p_recipient_reg_no in per_assignment_extra_info.aei_information1%type,
124 p_recipient_name in per_assignment_extra_info.aei_information1%type,
125 p_stat_total in out nocopy per_assignment_extra_info.aei_information1%type,
126 p_pol_total in out nocopy per_assignment_extra_info.aei_information1%type,
127 p_prom_fund_total in out nocopy per_assignment_extra_info.aei_information1%type,
128 p_tax_redn_total in out nocopy per_assignment_extra_info.aei_information1%type,
129 p_specified_total in out nocopy per_assignment_extra_info.aei_information1%type,
130 p_religious_total in out nocopy per_assignment_extra_info.aei_information1%type,
131 p_esoa_total in out nocopy per_assignment_extra_info.aei_information1%type,
132 p_others_total in out nocopy per_assignment_extra_info.aei_information1%type
133 ) return varchar2
134 is
135 cursor csr_dup_recipient_name is
136 select 'Y'
137 from per_assignment_extra_info
138 where
139 assignment_id = p_assignment_id
140 and information_type = 'KR_YEA_DETAIL_DONATION_INFO'
141 and aei_information7 = p_recipient_reg_no
142 and aei_information8 <> p_recipient_name
143 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
144 --
145 Cursor csr_don_totals1 is
146 select aei_information18, -- Statutory
147 aei_information20, -- Political
148 aei_information22, -- Specified
149 aei_information23, -- Tax Reduction Law
150 aei_information30 -- ESOA
151 from per_assignment_extra_info pai
152 where
153 pai.assignment_id = p_assignment_id
154 and pai.information_type = 'KR_YEA_SP_TAX_EXEM_INFO'
155 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') =
156 trunc(p_effective_date, 'YYYY');
157 --
158 Cursor csr_don_totals2 is
159 select aei_information4, -- Promotional Fund
160 aei_information5, -- Religious
161 aei_information6 -- Others
162 from per_assignment_extra_info pai
163 where
164 pai.assignment_id = p_assignment_id
165 and pai.information_type = 'KR_YEA_SP_TAX_EXEM_INFO2'
166 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') =
167 trunc(p_effective_date, 'YYYY');
168 --
169 l_dup_found varchar2(1) ;
170 --
171 begin
172 --
173 l_dup_found := 'N' ;
174 --
175 open csr_dup_recipient_name ;
176 fetch csr_dup_recipient_name into l_dup_found ;
177 --
178 if csr_dup_recipient_name%notfound then
179 l_dup_found := 'N' ;
180 end if ;
181 --
182 close csr_dup_recipient_name ;
183 --
184 if l_dup_found = 'Y' then
185 open csr_don_totals1 ;
186 fetch csr_don_totals1 into p_stat_total, p_pol_total, p_specified_total, p_tax_redn_total, p_esoa_total ;
187 --
188 if csr_don_totals1%notfound then
189 p_stat_total := '0' ;
190 p_pol_total := '0' ;
191 p_specified_total := '0' ;
192 p_tax_redn_total := '0' ;
193 p_esoa_total := '0' ;
194 end if ;
195 --
196 close csr_don_totals1 ;
197 --
198 --
199 open csr_don_totals2 ;
200 fetch csr_don_totals2 into p_prom_fund_total, p_religious_total, p_others_total ;
201 --
202 if csr_don_totals2%notfound then
203 p_prom_fund_total := '0' ;
204 p_religious_total := '0' ;
205 p_others_total := '0' ;
206 end if ;
207 --
208 close csr_don_totals2 ;
209 --
210 end if ;
211 --
212 return l_dup_found ;
213 end chk_dup_recipient_name ;
214 -------------------------------------------------------------------------------------------------------
215 function get_donation_tax_break(p_effective_date IN VARCHAR2,
216 p_political_donation IN NUMBER) return number
217 is
218 --
219 l_political_tax_brk_lim2004 NUMBER;
220 l_donation_tax_break NUMBER;
221 l_effective_date DATE ;
222 --
223 cursor csr_ff_global(p_glbvar in VARCHAR2) is
224 select glb.global_value
225 from ff_globals_f glb
226 where glb.global_name = p_glbvar
227 and l_effective_date between glb.effective_start_date and glb.effective_end_date;
228 begin
229 l_effective_date := fnd_date.canonical_to_date(p_effective_date) ;
230
231 OPEN csr_ff_global('KR_YEA_POLITICAL_DONATION_TAX_BREAK_LIM');
232 FETCH csr_ff_global into l_political_tax_brk_lim2004;
233 CLOSE csr_ff_global;
234 --
235 l_donation_tax_break := nvl(p_political_donation,0) - greatest(0,p_political_donation - l_political_tax_brk_lim2004);
236 --
237 return l_donation_tax_break;
238 --
239 end get_donation_tax_break;
240 -------------------------------------------------------------------------------------------------------
241 end pay_kr_yea_form_pkg;