[Home] [Help]
PACKAGE: APPS.PAY_KR_NONSTAT_SPAY_EFILE
Source
1 package pay_kr_nonstat_spay_efile as
2 /*$Header: pykrnspef.pkh 120.6 2006/12/20 12:49:32 vaisriva noship $ */
3
4 level_cnt number;
5 g_bp_count number;
6
7 /*************************************************************************
8 * Function that count the BP's under the Business Group.
9 *************************************************************************/
10 function get_bp_count( primary_business_place_id IN Number) return Number;
11
12 /*************************************************************************
13 * Procedure to submit e-file request indirectly
14 *************************************************************************/
15
16 procedure submit_efile (errbuf out nocopy varchar2,
17 retcode out nocopy varchar2,
18 p_effective_date in varchar2,
19 p_business_place in varchar2,
20 p_report_for in varchar2, --5069923
21 p_magnetic_file_name in varchar2,
22 p_report_file_name in varchar2,
23 p_target_year in varchar2,
24 p_payroll_action_id in varchar2, --5069923
25 p_assignment_set_id in varchar2, --5069923
26 p_reported_date in varchar2,
27 p_characterset in varchar2, --5069923
28 p_business_group_id_hd in varchar2,
29 p_tax_reporting_org in varchar2, --4095229
30 p_tax_reporter in varchar2,
31 p_cont_phone_no in varchar2,
32 p_home_tax_id in varchar2,
33 p_org_struc_version_id in varchar2 --5069923
34 );
35
36 /*********************************************************
37 * Cursor to get data for record A
38 * Parameters:
39 * REPORTED_DATE
40 * PRIMARY_BP_ID
41 * START_DATE
42 * END_DATE
43 *********************************************************/
44
45 cursor c_record_a is
46 SELECT
47 'CORP_PHONE_NUMBER=P'
48 ,hla.telephone_number_1 corp_phone_number
49 ,'REPRESENTATIVE_TAX_OFFICE_CODE=P'
50 ,ihoi.org_information9 corp_tax_office_code
51 ,'CORP_NAME=P'
52 ,choi.org_information1 corp_name
53 ,'CORP_REP_NAME=P'
54 ,choi.org_information6 corp_rep_name
55 ,'CORP_NUMBER=P'
56 ,choi.org_information2 corp_number
57 ,'BP_NUMBER=P'
58 ,hoi.org_information2 bp_number
59 ,'CORPORATION_ID=P'
60 ,hoi.org_information10 corp_id
61 ,'BP_COUNT=P'
62 , g_bp_count
63 ,'REPORTED_DATE=P'
64 ,pay_magtape_generic.get_parameter_value('REPORTED_DATE')
65 FROM hr_organization_information hoi
66 ,hr_organization_information ihoi
67 ,hr_organization_information choi
68 ,hr_organization_units hou
69 ,hr_locations_all hla
70 WHERE hou.organization_id = pay_magtape_generic.get_parameter_value('PRIMARY_BP_ID')
71 and hou.business_group_id = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
72 and hou.organization_id = hoi.organization_id
73 and hoi.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
74 and hou.organization_id = ihoi.organization_id
75 and ihoi.org_information_context = 'KR_INCOME_TAX_OFFICE'
76 and choi.organization_id = to_number(hoi.org_information10)
77 and choi.org_information_context = 'KR_CORPORATE_INFORMATION'
78 and hla.location_id(+) = hou.location_id
79 and hla.style(+) = 'KR'
80 and g_bp_count > 0;
81
82 /*********************************************************
83 * Cursor to get distinct business places for record B
84 * Parameters:
85 * CORPORATION_ID
86 * BUSINESS_GROUP_ID
87 * TARGET_YEAR
88 *
89 *********************************************************/
90
91 cursor c_record_b_distinct_bp
92 is
93 SELECT distinct
94 'BP_NUMBER=P',
95 hoi.org_information2,
96 'TAX_OFFICE_CODE=P',
97 ihoi.org_information9,
98 'CORP_NAME=P',
99 choi.org_information1,
100 'CORP_REP_NAME=P',
101 choi.org_information6,
102 'CORP_REG_NUMBER=P',
103 choi.org_information2
104 FROM hr_organization_information hoi
105 ,hr_organization_information ihoi
106 ,hr_organization_information choi
107 WHERE hoi.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
108 and hoi.organization_id = ihoi.organization_id
109 and ihoi.org_information_context = 'KR_INCOME_TAX_OFFICE'
110 and hoi.org_information10 = pay_magtape_generic.get_parameter_value('CORPORATION_ID')
111 --Bug 5069923
112 and (
113 (pay_magtape_generic.get_parameter_value('REPORT_FOR')='A')
114 or (
115 (hoi.organization_id in (select
116 posev.ORGANIZATION_ID_child
117 from PER_ORG_STRUCTURE_ELEMENTS posev
118 where posev.org_structure_version_id = (pay_magtape_generic.get_parameter_value('ORG_STRUC_VERSION_ID'))
119 and exists (
120 select null
121 from hr_organization_information
122 where organization_id = posev.ORGANIZATION_ID_child
123 and org_information_context = 'CLASS'
124 and org_information1 = 'KR_BUSINESS_PLACE'
125 )
126 start with ORGANIZATION_ID_PARENT = ( decode ( pay_magtape_generic.get_parameter_value('REPORT_FOR'),'S',null,'SUB',pay_magtape_generic.get_parameter_value('PRIMARY_BP_ID')))
127 connect by prior ORGANIZATION_ID_child = ORGANIZATION_ID_PARENT
128 )
129 )
130 or (hoi.organization_id = pay_magtape_generic.get_parameter_value('PRIMARY_BP_ID')
131 )
132 )
133 )
134 and choi.organization_id = to_number(hoi.org_information10)
135 and choi.org_information_context = 'KR_CORPORATE_INFORMATION'
136 and exists (select 'x'
137 from pay_assignment_actions xpaa,
138 pay_payroll_actions xppa,
139 ff_Archive_items fai,
140 ff_user_entities fue
141 where xppa.action_type = 'X'
142 and xppa.action_status = 'C'
143 --Bug 5069923
144 and xppa.payroll_action_id = nvl(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'),xpaa.payroll_action_id)
145 and xppa.effective_date between
146 to_date(pay_magtape_generic.get_parameter_value('TARGET_YEAR')||'0101','YYYYMMDD')
147 and to_date(pay_magtape_generic.get_parameter_value('TARGET_YEAR')||'1231','YYYYMMDD')
148 and xppa.payroll_action_id = xpaa.payroll_action_id
149 and xpaa.action_status = 'C'
150 --Bug 5069923
151 and ( (pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID') is null)
152 or (hr_assignment_set.assignment_in_set(pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID'), xpaa.assignment_id) = 'Y')
153 )
154 and xppa.report_type = 'KR_SEP'
155 and xppa.business_group_id = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
156 and hoi.organization_id = xpaa.tax_unit_id
157 and fue.user_entity_id = fai.user_entity_id
158 and fue.user_entity_name = 'A_NON_STAT_SEP_PAY_TAXABLE_EARNINGS_ASG_RUN'
159 and fue.legislation_code = 'KR'
160 and fai.context1 = xpaa.assignment_action_id
161 and fai.value > '0'
162 )
163 order by hoi.org_information2;
164
165 /*********************************************************
166 * Cursor to get summary values for record B
167 * Parameters:
168 * CORPORATION_ID
169 * BUSINESS_GROUP_ID
170 * TARGET_YEAR
171 * TAX_OFFICE_CODE
172 * BP_NUMBER
173 *
174 *********************************************************/
175
176 cursor c_record_b_summary
177 is
178 SELECT 'TOTAL_ITAX=P'
179 ,SUM(decode(fue.user_entity_name,'A_GROSS_ITAX_ASG_RUN',fai.value,0)) ITAX
180 ,'TOTAL_STAX=P'
181 ,SUM(decode(fue.user_entity_name,'A_GROSS_STAX_ASG_RUN',fai.value,0)) STAX
182 ,'TOTAL_RTAX=P'
183 ,SUM(decode(fue.user_entity_name,'A_GROSS_RTAX_ASG_RUN',fai.value,0)) RTAX
184 ,'TOTAL_TAXABLE_EARNG=P'
185 --Bug 5659556
186 ,SUM(decode(fue.user_entity_name,
187 'A_TAXABLE_EARNINGS_WI_PREV_ASG_RUN', pay_kr_spay_efile_fun_pkg.get_sep_pay_amount(xpaa.assignment_action_id,fai.value),
188 'A_NON_STAT_SEP_PAY_TAXABLE_EARNINGS_ASG_RUN', fai.value, 0)) TAXABLE_EARNG
189 --
190 ,'EMPLOYEE_COUNT=P'
191 ,sum(decode(decode(fue.user_entity_name ,'A_NON_STAT_SEP_PAY_TAXABLE_EARNINGS_ASG_RUN',fai.value,0),0,0,1)) EMPCOUNT
192 ,'PREV_EMP_COUNT=P'
193 ,SUM(decode(fue.user_entity_name ,'X_KR_PREV_BP_NUMBER',1,0)) PREVEMPCOUNT
194 FROM pay_assignment_actions xpaa
195 ,pay_payroll_actions xppa
196 ,hr_organization_information hoi
197 ,hr_organization_information ihoi
198 ,hr_organization_units hou
199 ,ff_user_entities fue
200 ,ff_archive_items fai
201 ,ff_user_entities fue1
202 ,ff_archive_items fai1
203 WHERE xppa.action_type = 'X'
204 and xppa.action_status = 'C'
205 --Bug 5069923
206 and xppa.payroll_action_id = nvl(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'),xpaa.payroll_action_id)
207 and xppa.effective_date between
208 to_date(pay_magtape_generic.get_parameter_value('TARGET_YEAR')||'0101','YYYYMMDD')
209 and to_date(pay_magtape_generic.get_parameter_value('TARGET_YEAR')||'1231','YYYYMMDD')
210 and xppa.payroll_action_id = xpaa.payroll_action_id
211 and xpaa.action_status = 'C'
212 and xppa.report_type = 'KR_SEP'
213 and hoi.organization_id = xpaa.tax_unit_id
214 and hoi.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
215 and hoi.organization_id = ihoi.organization_id
216 and ihoi.org_information_context = 'KR_INCOME_TAX_OFFICE'
217 and ihoi.org_information9 = pay_magtape_generic.get_parameter_value('TAX_OFFICE_CODE')
218 and hoi.org_information2 = pay_magtape_generic.get_parameter_value('BP_NUMBER')
219 and hoi.org_information10 = pay_magtape_generic.get_parameter_value('CORPORATION_ID')
220 and hoi.organization_id = hou.organization_id
221 and hou.business_group_id = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
222 and fai.user_entity_id = fue.user_entity_id
223 and fue.user_entity_name IN ('A_GROSS_ITAX_ASG_RUN',
224 'A_GROSS_STAX_ASG_RUN',
225 'A_GROSS_RTAX_ASG_RUN',
226 'A_TAXABLE_EARNINGS_WI_PREV_ASG_RUN',
227 'X_KR_PREV_BP_NUMBER',
228 'A_NON_STAT_SEP_PAY_TAXABLE_EARNINGS_ASG_RUN')
229 and fai.context1 = xpaa.assignment_Action_id
230 --Bug 5069923
231 and ( (pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID') is null)
232 or (hr_assignment_set.assignment_in_set(pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID'), xpaa.assignment_id) = 'Y')
233 )
234 and fue1.user_entity_name = 'A_NON_STAT_SEP_PAY_TAXABLE_EARNINGS_ASG_RUN'
235 and fue1.legislation_code = 'KR'
236 and fai1.user_entity_id = fue1.user_entity_id
237 and fai1.context1 = xpaa.assignment_Action_id
238 and fai1.value > '0';
239
240 /*********************************************************
241 * Cursor to get the start_date and end_date of multiple
242 * years. Required Parameters:
243 * TARGET_YEAR
244 *
245 *********************************************************/
246 cursor c_multiple_year -- 4095229
247 is
248 select 'START_DATE=P',
249 fnd_date.date_to_canonical( to_date(pay_magtape_generic.get_parameter_value('TARGET_YEAR') || '0101',
250 'YYYYMMDD') ) start_date,
251 'END_DATE=P',
252 fnd_date.date_to_canonical( to_date(pay_magtape_generic.get_parameter_value('TARGET_YEAR') || '1231',
253 'YYYYMMDD') ) end_date
254
255 from dual;
256
257 /*********************************************************
258 * Cursor to get assignemnts for record C
259 * Parameters:
260 * CORPORATION_ID
261 * BUSINESS_GROUP_ID
262 * START_DATE
263 * END_DATE
264 * TAX_OFFICE_CODE
265 * BP_NUMBER
266 *********************************************************/
267
268 cursor c_record_c
269 is
270 SELECT 'ASSIGNMENT_ACTION_ID=C'
271 ,xpaa.assignment_action_id
272 -- Bug 4251252
273 ,'ASSIGNMENT_ACTION_ID=P'
274 ,xpaa.assignment_action_id
275 -- Bug 4251252
276 ,'ASG_ID=P'
277 ,xpaa.assignment_id
278 ,'PREVIOUS_EMP_COUNT=P'
279 ,pay_kr_nonstat_spay_efile_fun.get_prev_emp_count(xpaa.assignment_action_id)
280 FROM pay_assignment_actions xpaa
281 ,pay_payroll_actions xppa
282 ,hr_organization_information hoi
283 ,hr_organization_units hou
284 ,ff_user_entities fue
285 ,ff_archive_items fai
286 ,ff_user_entities fue1
287 ,ff_archive_items fai1
288 WHERE xppa.action_type = 'X'
289 and xppa.action_status = 'C'
290 and xppa.effective_date
291 between fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('START_DATE'))
292 and fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('END_DATE'))
293 and xppa.payroll_action_id = xpaa.payroll_action_id
294 --Bug 5069923
295 and xppa.payroll_action_id = nvl(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'),xpaa.payroll_action_id)
296 and xpaa.action_status = 'C'
297 and xppa.report_type = 'KR_SEP'
298 and hoi.organization_id = xpaa.tax_unit_id
299 and hoi.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
300 and hoi.org_information2 = pay_magtape_generic.get_parameter_value('BP_NUMBER')
301 and hoi.org_information10 = pay_magtape_generic.get_parameter_value('CORPORATION_ID')
302 and hoi.organization_id = hou.organization_id
303 and hou.business_group_id = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
304 and fue.user_entity_name = 'A_NON_STAT_SEP_PAY_TAXABLE_EARNINGS_ASG_RUN'
305 and fue.legislation_code = 'KR'
309 and ( (pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID') is null)
306 and fai.user_entity_id = fue.user_entity_id
307 and fai.context1 = xpaa.assignment_action_id
308 --Bug 5069923
310 or (hr_assignment_set.assignment_in_set(pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID'), xpaa.assignment_id) = 'Y')
311 )
312 and fai.value > '0'
313 and fue1.user_entity_name = 'X_KR_EMP_NI'
314 and fai1.user_entity_id = fue1.user_entity_id
315 and fai1.context1 = xpaa.assignment_action_id
316 order by fai1.value;
317
318 /*********************************************************
319 * Cursor to get ELEMENT_ENTRY_ID for record D
320 * Parameters:
321 * ASG_ID
322 * ASSIGNMENT_ACTION_ID
323 *********************************************************/
324
325 cursor c_record_d
326 is
327 select
328 'ELEMENT_ENTRY_ID=C',
329 fac.context
330 from
331 ff_contexts fc,
332 ff_user_entities fue,
333 ff_route_context_usages frc,
334 ff_archive_item_contexts fac,
335 ff_archive_items fai
336 where
337 fue.user_entity_name = 'X_KR_PREV_BP_NUMBER'
338 and fue.legislation_code = 'KR'
339 and fue.route_id = frc.route_id
340 and frc.context_id = fc.context_id
341 and fc.context_name = 'ELEMENT_ENTRY_ID'
342 and fai.context1 = pay_magtape_generic.get_parameter_value('ASSIGNMENT_ACTION_ID')
343 and fai.user_entity_id = fue.user_entity_id
344 and fac.archive_item_id = fai.archive_item_id
345 and fac.context_id = fc.context_id
346 order by
347 fai.value, fac.context ;
348
349 end pay_kr_nonstat_spay_efile;