[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_YEA_DON_EFILE_CONC_PKG
Source
1 package body pay_kr_yea_don_efile_conc_pkg as
2 /*$Header: pykrydcon.pkb 120.4 2012/01/19 10:14:12 rpahune ship $ */
3
4 /*************************************************************************
5 * Procedure to submit e-file request indirectly
6 *************************************************************************/
7
8 procedure submit_efile(
9 errbuf out nocopy varchar2,
10 retcode out nocopy varchar2,
11 p_business_place in varchar2,
12 p_REPORT_FOR in varchar2, --5069923
13 p_magnetic_file_name in varchar2,
14 p_report_file_name in varchar2,
15 p_payroll_action in varchar2,
16 p_assignment_set in varchar2,
17 p_report_type in varchar2,
18 p_reported_date in varchar2,
19 p_target_year in varchar2,
20 p_characterset in varchar2,
21 p_tax_rep_org in varchar2, -- Bug 10184060
22 p_tax_rep in varchar2, -- Bug 10184060
23 p_cont_phone_no in varchar2, -- Bug 10184060
24 p_home_tax_id in varchar2,
25 p_ORG_STRUC_VERSION_ID in varchar2 --5069923
26 )
27
28 is
29
30 l_req_id number;
31 l_message varchar2(2000);
32 l_phase varchar2(100);
33 l_status varchar2(100);
34 l_action_completed boolean;
35 l_bg_id number;
36
37 begin
38 get_bg_id(p_business_place, l_bg_id) ;
39 l_req_id := fnd_request.submit_request (
40 APPLICATION => 'PAY',
41 PROGRAM => 'PYKRYEAM_DON_I',
42 DESCRIPTION => 'KR Year End Adjustment Donation eFile (Internal)',
43 ARGUMENT1 => 'pay_magtape_generic.new_formula',
44 ARGUMENT2 => p_magnetic_file_name,
45 ARGUMENT3 => p_report_file_name,
46 ARGUMENT4 => null,
47 ARGUMENT5 => 'MAGTAPE_REPORT_ID=KR_YEA_DON_EFILE',
48 ARGUMENT6 => 'PRIMARY_BP_ID=' || p_business_place,
49 ARGUMENT7 => 'REPORTED_DATE=' || p_reported_date,
50 ARGUMENT8 => 'PAYROLL_ACTION_ID=' || p_payroll_action,
51 ARGUMENT9 => 'ASSIGNMENT_SET_ID=' || p_assignment_set,
52 ARGUMENT10 => 'REPORT_TYPE=' || p_report_type,
53 ARGUMENT11 => 'REPORT_DATE=' || p_reported_date,
54 ARGUMENT12 => 'TARGET_YEAR=' || p_target_year,
55 ARGUMENT13 => 'CHARACTER_SET=' || p_characterset,
56 ARGUMENT14 => 'TAX_REPORTING_ORG=' || p_tax_rep_org, -- Bug 10184060
57 ARGUMENT15 => 'TAX_REPORTER=' || p_tax_rep, -- Bug 10184060
58 ARGUMENT16 => 'CONT_PHONE_NO=' || p_cont_phone_no, -- Bug 10184060
59 ARGUMENT17 => 'HOME_TAX_ID=' || p_home_tax_id,
60 ARGUMENT18 => 'BG_ID=' || (l_bg_id),
61 ARGUMENT19 => 'REPORT_FOR=' || p_REPORT_FOR,
62 ARGUMENT20 => 'ORG_STRUC_VERSION_ID=' || p_ORG_STRUC_VERSION_ID
63 ) ;
64
65 if (l_req_id = 0) then
66 retcode := 2;
67 fnd_message.retrieve(errbuf);
68 else
69 commit;
70 end if;
71
72 end submit_efile;
73 --------------------------------------------------------------------------------------------------
74 procedure get_bg_id(
75 p_business_place in varchar2,
76 p_business_group_id out nocopy number
77 )
78 is
79
80 cursor csr_business_group_id is
81 select hou.business_group_id
82 from hr_organization_units hou
83 where hou.organization_id = p_business_place ;
84 begin
85 open csr_business_group_id ;
86 fetch csr_business_group_id into p_business_group_id ;
87 close csr_business_group_id ;
88 end get_bg_id;
89 --------------------------------------------------------------------------------------------------
90 -- Bug 10184060
91 --
92 function record_count(
93 p_bp_number in varchar2,
94 p_tax_office_code in varchar2,
95 p_item_name in varchar2) return varchar2
96 --------------------------------------------------------------------------------------------------
97 is
98 num_of_c_records number(20) := 0;
99 num_of_d_records number(20) := 0;
100 c_don_amt number(20) := 0;
101 c_amt_sub_exem number(20) := 0;
102
103 cursor csr_count_c is
104 select
105 count(*),
106 sum(nvl(to_number(aei_information9),0)),
107 sum(nvl(to_number(aei_information10),0))
108 from per_people_f pp,
109 per_assignments_f pa,
110 pay_assignment_actions paa,
111 pay_payroll_actions ppa,
112 hr_organization_units bp,
113 hr_organization_information hoi1,
114 hr_organization_information hoi2,
115 per_assignment_extra_info pae
116 where hoi1.org_information2 = p_bp_number
117 and hoi2.org_information9 = p_tax_office_code
118 and hoi1.organization_id = bp.organization_id
119 and hoi1.organization_id = hoi2.organization_id
120 and hoi1.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
121 and hoi2.org_information_context = 'KR_INCOME_TAX_OFFICE'
122 and ppa.business_group_id = bp.business_group_id
123 and ppa.report_type = 'YEA'
124 and ppa.report_qualifier = 'KR'
125 and ( (ppa.report_category in (pay_kr_yea_don_efile_pkg.g_normal_yea, pay_kr_yea_don_efile_pkg.g_interim_yea, pay_kr_yea_don_efile_pkg.g_re_yea))
126 or (ppa.payroll_action_id = pay_kr_yea_don_efile_pkg.g_payroll_action_id) )
127 and to_number(to_char(ppa.effective_date, 'YYYY')) = pay_kr_yea_don_efile_pkg.g_target_year
128 --
129 and ppa.action_type in ('B','X')
130 and paa.payroll_action_id = ppa.payroll_action_id
131 and ppa.payroll_action_id = ppa.payroll_action_id
132 --
133 and (
134 (pay_kr_yea_don_efile_pkg.g_assignment_set_id is null) or
135 (hr_assignment_set.assignment_in_set(pay_kr_yea_don_efile_pkg.g_assignment_set_id, paa.assignment_id) = 'Y')
136 )
137 and (
138 (pay_kr_yea_don_efile_pkg.g_re_yea <> 'R')
139 or
140 (pay_kr_yea_magtape_fun_pkg.latest_yea_action(paa.assignment_action_id, pay_kr_yea_don_efile_pkg.g_payroll_action_id, pay_kr_yea_don_efile_pkg.g_target_year) = 'Y')
141 )
142 --
143 and paa.tax_unit_id = bp.organization_id
144 and paa.action_status = 'C'
145 and pa.assignment_id = paa.assignment_id
146 and pp.person_id = pa.person_id
147 and ppa.effective_date
148 between pa.effective_start_date and pa.effective_end_date
149 and ppa.effective_date
150 between pp.effective_start_date and pp.effective_end_date
151 and pae.assignment_id = pa.assignment_id
152 and pae.information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
153 and pae.aei_information2 <= pay_kr_yea_don_efile_pkg.g_target_year
154 and to_char(fnd_date.canonical_to_date(pae.aei_information1),'yyyy') = pay_kr_yea_don_efile_pkg.g_target_year;
155
156 cursor csr_count_d is
157 select
158 count(*)
159 from
160 (select aei.aei_information5,
161 aei.aei_information7,
162 aei.aei_information8,
163 aei.aei_information12,
164 pp.last_name || pp.first_name,
165 aei.aei_information13,
166 sum(nvl(aei.aei_information4, 1)),
167 sum(aei.aei_information3)
168 from
169 per_assignment_extra_info aei,
170 pay_assignment_actions paa,
171 pay_payroll_actions ppa,
172 hr_organization_units bp,
173 hr_organization_information hoi1,
174 hr_organization_information hoi2,
175 per_people_f pp,
176 per_assignments_f pa,
177 per_kr_resident_reg_number_v resreg
178 where hoi1.org_information2 = p_bp_number
179 and hoi2.org_information9 = p_tax_office_code
180 and hoi1.organization_id = hoi2.organization_id
181 and hoi1.organization_id = bp.organization_id
182 and hoi2.org_information_context = 'KR_INCOME_TAX_OFFICE'
183 and hoi1.org_information_context like 'KR_BUSINESS_PLACE_REGISTRATION'
184 and ppa.report_type = 'YEA'
185 and ppa.report_qualifier = 'KR'
186 and ( (ppa.report_category in (pay_kr_yea_don_efile_pkg.g_normal_yea, pay_kr_yea_don_efile_pkg.g_interim_yea, pay_kr_yea_don_efile_pkg.g_re_yea)) or (ppa.payroll_action_id = pay_kr_yea_don_efile_pkg.g_payroll_action_id) )
187 and to_number(to_char(ppa.effective_date, 'YYYY')) = pay_kr_yea_don_efile_pkg.g_target_year
188 --
189 and ppa.action_type in ('B','X')
190 and paa.payroll_action_id = ppa.payroll_action_id
191 and ppa.payroll_action_id = ppa.payroll_action_id
192 and ((pay_kr_yea_don_efile_pkg.g_assignment_set_id is null) or (hr_assignment_set.assignment_in_set(pay_kr_yea_don_efile_pkg.g_assignment_set_id, paa.assignment_id) = 'Y'))
193 and ((pay_kr_yea_don_efile_pkg.g_re_yea <> 'R') or (pay_kr_yea_magtape_fun_pkg.latest_yea_action(paa.assignment_action_id, pay_kr_yea_don_efile_pkg.g_payroll_action_id, pay_kr_yea_don_efile_pkg.g_target_year) = 'Y'))
194 --
195 and paa.tax_unit_id = bp.organization_id
196 and paa.action_status = 'C'
197 and pa.assignment_id = paa.assignment_id
198 and fnd_date.canonical_to_date(aei.aei_information1)
199 between pa.effective_start_date and pa.effective_end_date
200 and fnd_date.canonical_to_date(aei.aei_information1)
201 between pp.effective_start_date and pp.effective_end_date
202 and pa.person_id = resreg.person_id
203 and fnd_date.canonical_to_date(aei.aei_information1) BETWEEN resreg.cont_effective_start_date
204 AND resreg.cont_effective_end_date
205 and fnd_date.canonical_to_date(aei.aei_information1) BETWEEN resreg.person_effective_start_date
206 AND resreg.person_effective_end_date
207 and fnd_date.canonical_to_date(aei.aei_information1) BETWEEN nvl(resreg.relationship_start_date, fnd_date.canonical_to_date(aei.aei_information1))
208 AND decode(resreg.relationship_end_date,NULL,fnd_date.canonical_to_date(aei.aei_information1),
209 decode(resreg.relationship_end_reason,'D',
210 TRUNC(add_months(resreg.relationship_end_date,12),'YYYY') -1,resreg.relationship_end_date))
211 and resreg.cont_person_id = pp.person_id
212 and pp.national_identifier = aei.aei_information13
213 and aei.assignment_id = pa.assignment_id
214 and aei.information_type = 'KR_YEA_DETAIL_DONATION_INFO'
215 and to_char(fnd_date.canonical_to_date(aei.aei_information1),'yyyy') = pay_kr_yea_don_efile_pkg.g_target_year
216 group by aei.assignment_id,
217 aei.aei_information13,
218 pp.last_name || pp.first_name,
219 aei.aei_information12,
220 aei.aei_information5,
221 aei.aei_information7,
222 aei.aei_information8);
223 begin
224 --
225 num_of_c_records := 0;
226 num_of_d_records := 0;
227 c_don_amt := 0;
228 c_amt_sub_exem := 0;
229
230 open csr_count_c;
231 fetch csr_count_c into num_of_c_records,c_don_amt,c_amt_sub_exem;
232 close csr_count_c;
233 --
234 open csr_count_d;
235 fetch csr_count_d into num_of_d_records;
236 close csr_count_d;
237 --
238 if p_item_name = 'C_RECORDS' then
239 return to_char(num_of_c_records);
240 elsif p_item_name = 'D_RECORDS' then
241 return to_char(num_of_d_records);
242 elsif p_item_name = 'DON_AMT' then
243 return to_char(c_don_amt);
244 elsif p_item_name = 'AMT_SUB_EXEM' then
245 return to_char(c_amt_sub_exem);
246 end if;
247
248 end;
249 --------------------------------------------------------------------------------------------------
250 end pay_kr_yea_don_efile_conc_pkg ;