[Home] [Help]
Skip to content
PACKAGE BODY: APPS.PAY_KR_YEA_FORM_PKG
Source
1 package body pay_kr_yea_form_pkg as
2 /* $Header: pykryeaf.pkb 120.9 2012/01/19 10:16:53 rpahune ship $ */
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 p_public_legal_total in out nocopy per_assignment_extra_info.aei_information1%type
134 ) return varchar2 -- Bug 7142612
135 is
136 cursor csr_dup_recipient_name is
137 select 'Y'
138 from per_assignment_extra_info
139 where
140 assignment_id = p_assignment_id
141 and information_type = 'KR_YEA_DETAIL_DONATION_INFO'
142 and aei_information7 = p_recipient_reg_no
143 and aei_information8 <> p_recipient_name
144 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
145 --
146 Cursor csr_don_totals1 is
147 select aei_information18, -- Statutory
148 aei_information20, -- Political
149 aei_information22, -- Specified
150 aei_information23, -- Tax Reduction Law
151 aei_information30 -- ESOA
152 from per_assignment_extra_info pai
153 where
154 pai.assignment_id = p_assignment_id
155 and pai.information_type = 'KR_YEA_SP_TAX_EXEM_INFO'
156 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') =
157 trunc(p_effective_date, 'YYYY');
158 --
159 Cursor csr_don_totals2 is
160 select aei_information4, -- Promotional Fund
161 aei_information5, -- Religious
162 aei_information6, -- Others
163 aei_information7 -- Public Legal Entity Donation Trust -- Bug 7142612
164 from per_assignment_extra_info pai
165 where
166 pai.assignment_id = p_assignment_id
167 and pai.information_type = 'KR_YEA_SP_TAX_EXEM_INFO2'
168 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') =
169 trunc(p_effective_date, 'YYYY');
170 --
171 l_dup_found varchar2(1) ;
172 --
173 begin
174 --
175 l_dup_found := 'N' ;
176 --
177 open csr_dup_recipient_name ;
178 fetch csr_dup_recipient_name into l_dup_found ;
179 --
180 if csr_dup_recipient_name%notfound then
181 l_dup_found := 'N' ;
182 end if ;
183 --
184 close csr_dup_recipient_name ;
185 --
186 if l_dup_found = 'Y' then
187 open csr_don_totals1 ;
188 fetch csr_don_totals1 into p_stat_total, p_pol_total, p_specified_total, p_tax_redn_total, p_esoa_total ;
189 --
190 if csr_don_totals1%notfound then
191 p_stat_total := '0' ;
192 p_pol_total := '0' ;
193 p_specified_total := '0' ;
194 p_tax_redn_total := '0' ;
195 p_esoa_total := '0' ;
196 end if ;
197 --
198 close csr_don_totals1 ;
199 --
200 --
201 open csr_don_totals2 ;
202 fetch csr_don_totals2 into p_prom_fund_total, p_religious_total, p_others_total, p_public_legal_total ;
203 --
204 if csr_don_totals2%notfound then
205 p_prom_fund_total := '0' ;
206 p_religious_total := '0' ;
207 p_others_total := '0' ;
208 p_public_legal_total := '0' ; -- Bug 7142612
209 end if ;
210 --
211 close csr_don_totals2 ;
212 --
213 end if ;
214 --
215 return l_dup_found ;
216 end chk_dup_recipient_name ;
217 -------------------------------------------------------------------------------------------------------
218 function get_donation_tax_break(p_effective_date IN VARCHAR2,
219 p_political_donation IN NUMBER) return number
220 is
221 --
222 l_political_tax_brk_lim2004 NUMBER;
223 l_donation_tax_break NUMBER;
224 l_effective_date DATE ;
225 --
226 cursor csr_ff_global(p_glbvar in VARCHAR2) is
227 select glb.global_value
228 from ff_globals_f glb
229 where glb.global_name = p_glbvar
230 and l_effective_date between glb.effective_start_date and glb.effective_end_date;
231 begin
232 l_effective_date := fnd_date.canonical_to_date(p_effective_date) ;
233
234 OPEN csr_ff_global('KR_YEA_POLITICAL_DONATION_TAX_BREAK_LIM');
235 FETCH csr_ff_global into l_political_tax_brk_lim2004;
236 CLOSE csr_ff_global;
237 --
238 l_donation_tax_break := nvl(p_political_donation,0) - greatest(0,p_political_donation - l_political_tax_brk_lim2004);
239 --
240 return l_donation_tax_break;
241 --
242 end get_donation_tax_break;
243
244 end pay_kr_yea_form_pkg;