[Home] [Help]
PACKAGE: APPS.PAY_KR_NONSTAT_SPAY_EFILE
Source
4 level_cnt number;
1 package pay_kr_nonstat_spay_efile AUTHID CURRENT_USER as
2 /*$Header: pykrnspef.pkh 120.15.12020000.4 2013/03/11 07:08:10 scireddy ship $ */
3
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 * Function to get the context for record 'D'.
14 *************************************************************************/
15 function get_record_d_context(p_assignment_action_id IN number) return number;
16
17 /*************************************************************************
18 * Procedure to submit e-file request indirectly
19 *************************************************************************/
20
21 procedure submit_efile (errbuf out nocopy varchar2,
22 retcode out nocopy varchar2,
23 p_effective_date in varchar2,
24 p_business_place in varchar2,
25 p_report_for in varchar2, --5069923
26 p_magnetic_file_name in varchar2,
27 p_report_file_name in varchar2,
28 p_target_year in varchar2,
29 p_payroll_action_id in varchar2, --5069923
30 p_assignment_set_id in varchar2, --5069923
31 p_reported_date in varchar2,
32 p_reporting_period in varchar2,
33 p_characterset in varchar2, --5069923
34 p_business_group_id_hd in varchar2,
35 p_tax_reporting_org in varchar2, --4095229
36 p_tax_reporter in varchar2,
37 p_cont_phone_no in varchar2,
38 p_home_tax_id in varchar2,
39 p_org_struc_version_id in varchar2 --5069923
40 );
41
42 /*********************************************************
43 * Cursor to get data for record A
44 * Parameters:
45 * REPORTED_DATE
46 * PRIMARY_BP_ID
47 * START_DATE
48 * END_DATE
49 *********************************************************/
50
51 cursor c_record_a is
52 SELECT
53 'CORP_PHONE_NUMBER=P'
54 ,hla.telephone_number_1 corp_phone_number
55 ,'REPRESENTATIVE_TAX_OFFICE_CODE=P'
56 ,ihoi.org_information9 corp_tax_office_code
57 ,'CORP_NAME=P'
58 ,choi.org_information1 corp_name
59 ,'CORP_REP_NAME=P'
60 ,choi.org_information6 corp_rep_name
61 ,'CORP_NUMBER=P'
62 ,choi.org_information2 corp_number
63 ,'BP_NUMBER=P'
64 ,hoi.org_information2 bp_number
65 ,'CORPORATION_ID=P'
66 ,hoi.org_information10 corp_id
67 ,'BP_COUNT=P'
68 , g_bp_count
69 ,'REPORTED_DATE=P'
70 ,pay_magtape_generic.get_parameter_value('REPORTED_DATE')
71 FROM hr_organization_information hoi
72 ,hr_organization_information ihoi
73 ,hr_organization_information choi
74 ,hr_organization_units hou
75 ,hr_locations_all hla
76 WHERE hou.organization_id = pay_magtape_generic.get_parameter_value('PRIMARY_BP_ID')
77 and hou.business_group_id = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
78 and hou.organization_id = hoi.organization_id
79 and hoi.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
80 and hou.organization_id = ihoi.organization_id
81 and ihoi.org_information_context = 'KR_INCOME_TAX_OFFICE'
82 and choi.organization_id = to_number(hoi.org_information10)
83 and choi.org_information_context = 'KR_CORPORATE_INFORMATION'
84 and hla.location_id(+) = hou.location_id
85 and hla.style(+) = 'KR'
86 and g_bp_count > 0;
87
88 /*********************************************************
89 * Cursor to get distinct business places for record B
90 * Parameters:
91 * CORPORATION_ID
92 * BUSINESS_GROUP_ID
93 * TARGET_YEAR
94 *
95 *********************************************************/
96
97 cursor c_record_b_distinct_bp
98 is
99 SELECT distinct
100 'BP_NUMBER=P',
101 hoi.org_information2,
102 'TAX_OFFICE_CODE=P',
103 ihoi.org_information9,
104 'CORP_NAME=P',
105 choi.org_information1,
106 'CORP_REP_NAME=P',
107 choi.org_information6,
108 'CORP_REG_NUMBER=P',
109 choi.org_information2
110 FROM hr_organization_information hoi
111 ,hr_organization_information ihoi
112 ,hr_organization_information choi
113 WHERE hoi.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
114 and hoi.organization_id = ihoi.organization_id
115 and ihoi.org_information_context = 'KR_INCOME_TAX_OFFICE'
116 and hoi.org_information10 = pay_magtape_generic.get_parameter_value('CORPORATION_ID')
117 --Bug 5069923
118 and (
119 (pay_magtape_generic.get_parameter_value('REPORT_FOR')='A')
120 or (
121 (hoi.organization_id in (select
122 posev.ORGANIZATION_ID_child
123 from PER_ORG_STRUCTURE_ELEMENTS posev
124 where posev.org_structure_version_id = (pay_magtape_generic.get_parameter_value('ORG_STRUC_VERSION_ID'))
125 and exists (
126 select null
127 from hr_organization_information
128 where organization_id = posev.ORGANIZATION_ID_child
129 and org_information_context = 'CLASS'
130 and org_information1 = 'KR_BUSINESS_PLACE'
131 )
132 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')))
133 connect by prior ORGANIZATION_ID_child = ORGANIZATION_ID_PARENT
134 )
135 )
136 or (hoi.organization_id = pay_magtape_generic.get_parameter_value('PRIMARY_BP_ID')
137 )
138 )
139 )
140 and choi.organization_id = to_number(hoi.org_information10)
141 and choi.org_information_context = 'KR_CORPORATE_INFORMATION'
142 and exists (select 'x'
143 from pay_assignment_actions xpaa,
144 pay_payroll_actions xppa,
145 ff_Archive_items fai,
146 ff_user_entities fue
147 where xppa.action_type = 'X'
148 and xppa.action_status = 'C'
149 --Bug 5069923
150 and xppa.payroll_action_id = nvl(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'),xpaa.payroll_action_id)
151 and xppa.effective_date between
152 to_date(pay_magtape_generic.get_parameter_value('TARGET_YEAR')||'0101','YYYYMMDD')
153 and to_date(pay_magtape_generic.get_parameter_value('TARGET_YEAR')||'1231','YYYYMMDD')
154 and xppa.payroll_action_id = xpaa.payroll_action_id
155 and xpaa.action_status = 'C'
156 --Bug 5069923
157 and ( (pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID') is null)
158 or (hr_assignment_set.assignment_in_set(pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID'), xpaa.assignment_id) = 'Y')
159 )
160 and xppa.report_type = 'KR_SEP'
161 and xppa.business_group_id = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
162 and hoi.organization_id = xpaa.tax_unit_id
163 and fue.user_entity_id = fai.user_entity_id
164 and fue.user_entity_name in ('A_NON_STAT_SEP_PAY_TAXABLE_EARNINGS_ASG_RUN','A_RECEIVABLE_NON_STAT_SEP_PAY_ASG_RUN','A_RECEIVABLE_SEPARATION_PAY_ASG_RUN',
165 'A_TAXABLE_EARNINGS_WI_PREV_ASG_RUN' ) --Bug 9409509
166 and fue.legislation_code = 'KR'
167 and fai.context1 = xpaa.assignment_action_id
168 and fai.value > '0'
169 )
170 order by hoi.org_information2;
171
172 /*********************************************************
173 * Cursor to get summary values for record B
174 * Parameters:
175 * CORPORATION_ID
176 * BUSINESS_GROUP_ID
177 * TARGET_YEAR
178 * TAX_OFFICE_CODE
179 * BP_NUMBER
180 *
181 *********************************************************/
182
183 cursor c_record_b_summary
184 is
185 SELECT 'TOTAL_ITAX=P'
186 ,SUM(decode(fue.user_entity_name,'A_GROSS_ITAX_ASG_RUN',fai.value,0)) ITAX
187 ,'TOTAL_STAX=P'
188 ,SUM(decode(fue.user_entity_name,'A_GROSS_STAX_ASG_RUN',fai.value,0)) STAX
189 ,'TOTAL_RTAX=P'
190 ,SUM(decode(fue.user_entity_name,'A_GROSS_RTAX_ASG_RUN',fai.value,0)) RTAX
191 ,'TOTAL_TAXABLE_EARNG=P'
192 --Bug 5659556
193 ,SUM(decode(fue.user_entity_name,
197 , 'TS_REC_SEP_PAY=P'
194 'A_TAXABLE_EARNINGS_WI_PREV_ASG_RUN', pay_kr_spay_efile_fun_pkg.get_sep_pay_amount(xpaa.assignment_action_id,fai.value),
195 'A_NON_STAT_SEP_PAY_TAXABLE_EARNINGS_ASG_RUN',pay_kr_spay_efile_fun_pkg.get_nsep_pay_amount(xpaa.assignment_action_id,fai.value), 0)) TAXABLE_EARNG
196 --
198 , SUM(decode(fue.user_entity_name,'A_RECEIVABLE_SEPARATION_PAY_ASG_RUN',fai.value,0)) SEPPAYAMT
199 , 'TS_EARNINS=P'
200 , SUM(decode(fue.user_entity_name,'A_TAXABLE_EARNINGS_WI_PREV_ASG_RUN',fai.value,0)) TAXEARN
201 , 'TS_AMT_EXP=P'
202 , SUM(decode(fue.user_entity_name,'A_AMOUNT_EXPECTED_ASG_RUN',fai.value,0)) AMTEXP
203 , 'TS_PREV_AMT_EXP=P'
204 , SUM(decode(fue.user_entity_name,'A_PREV_SEP_PENS_DTLS_AMT_EXP_STAT_SEP_ENTRY_VALUE',fai.value,0)) PREVAMTEXP
205 , 'TS_DEF_AMT=P'
206 , SUM(decode(fue.user_entity_name,'A_DEFERRED_AMOUNT_STAT_ASG_RUN',fai.value,0)) DEFAMT
207 , 'TS_DEF_NS_AMT=P'
208 , SUM(decode(fue.user_entity_name,'A_DEFERRED_AMOUNT_NONSTAT_ASG_RUN',fai.value,0)) DEFNSAMT
209 , 'TS_REC_NS_AMT=P'
210 , SUM(decode(fue.user_entity_name,'A_RECEIVED_AMOUNT_NONSTAT_ASG_RUN',fai.value,0)) RECNSAMT
211 , 'TS_REC_STAT_AMT=P'
212 , SUM(decode(fue.user_entity_name,'A_RECEIVED_AMOUNT_STAT_ASG_RUN',fai.value,0)) RECSTATAMT
213 , 'TS_AMT_EXP_NS=P'
214 , SUM(decode(fue.user_entity_name,'A_AMOUNT_EXPECTED_NONSTAT_ASG_RUN',fai.value,0)) AMTEXPNS
215 , 'TS_REC_NS_SEP_AMT=P'
216 , SUM(decode(fue.user_entity_name,'A_RECEIVABLE_NON_STAT_SEP_PAY_ASG_RUN',fai.value,0)) RECNSSEPAMT
217 , 'TS_NS_TAX_EARN=P'
218 , SUM(decode(fue.user_entity_name,'A_NON_STAT_SEP_PAY_TAXABLE_EARNINGS_ASG_RUN',fai.value,0)) RECNSEARN
219 , 'TS_TRAN_STAT=P'
220 , SUM(decode(fue.user_entity_name,'A_TRANSFERRED_AMOUNT_FOR_STAT_SEP_PAY_ASG_RUN',fai.value,0)) TRANSSTAT
221 , 'TS_TRAN_NSTAT=P'
222 , SUM(decode(fue.user_entity_name,'A_TRANSFERRED_AMOUNT_FOR_NONSTAT_SEP_PAY_ASG_RUN',fai.value,0)) TRANSNSTAT
223 , 'TS_PREV_DEF_ENT=P'
224 , SUM(decode(fue.user_entity_name,'A_PREV_SEP_PENS_DTLS_DEFERRED_AMOUNT_STAT_ENTRY_VALUE',fai.value,0)) PREVDEFENTAMT
225 , 'TS_PREV_REC_ENT=P'
226 , SUM(decode(fue.user_entity_name,'A_PREV_SEP_PENS_DTLS_RECEIVED_AMOUNT_STAT_ENTRY_VALUE',fai.value,0)) PREVRECENTAMT
227 , 'TS_PREV_DEF_NS_ENT=P'
228 , SUM(decode(fue.user_entity_name,'A_PREV_SEP_PENS_DTLS_DEFERRED_AMOUNT_NONSTAT_ENTRY_VALUE',fai.value,0)) PREVDEFNSENTAMT
229 , 'TS_PREV_REC_NS_ENT=P'
230 , SUM(decode(fue.user_entity_name,'A_PREV_SEP_PENS_DTLS_RECEIVED_AMOUNT_NONSTAT_ENTRY_VALUE',fai.value,0)) PREVRECNSENTAMT
231 , 'TS_PRE_SEP_ENT=P'
232 , SUM(decode(fue.user_entity_name,'A_PREV_SEP_PENS_DTLS_AMT_EXP_NONSTAT_SEP_ENTRY_VALUE',fai.value,0)) PRESEPENT
233 , 'TS_FULL_DEF_EARN=P'
234 ,SUM(decode(fue.user_entity_name,
235 'A_FULL_DEF_TAXABLE_EARNINGS_ASG_RUN', pay_kr_spay_efile_fun_pkg.get_sep_pay_amount(xpaa.assignment_action_id,fai.value),
236 'A_FULL_DEF_NS_EARNINGS_ASG_RUN',pay_kr_spay_efile_fun_pkg.get_nsep_pay_amount(xpaa.assignment_action_id,fai.value), 0)) FULLDEFEARN
237 , 'TS_DEF_TAX=P'
238 , SUM(decode(fue.user_entity_name,'A_DEFERRED_TAX_AMOUNT_ASG_RUN',fai.value,0)) DEFTAX
239 ,'TS_PREV_SEP_PAY=P'
240 ,SUM(decode(fue.user_entity_name,'X_KR_PREV_SEP_PAY',fai.value,0)) PREVSEPPAY
241 ,'TS_PREV_SEP_PAY_INS=P'
242 ,SUM(decode(fue.user_entity_name,'X_KR_PREV_SEP_INSURANCE',fai.value,0)) PREVSEPPAYINS
243 ,'TS_PREV_NS_SEP_PAY=P'
244 ,SUM(decode(fue.user_entity_name,'X_KR_PREV_SEP_ALLOWANCE',fai.value,0)) PREVNSSEPPAY
245 ,'TS_PREV_LUMP_SUM=P'
246 ,SUM(decode(fue.user_entity_name,'X_KR_PREV_LUMP_SUM_STAT_ENT',fai.value,0)) PREVLUMPSUM
247 ,'TS_PREV_NS_LUMP_SUM=P'
248 ,SUM(decode(fue.user_entity_name,'X_KR_PREV_LUMP_SUM_NST_ENT',fai.value,0)) PREVNSLUMPSUM
249 ,'EMPLOYEE_COUNT=P'
250 ,sum(decode(pay_kr_spay_efile_fun_pkg.get_emp_count(fue.user_entity_name,xpaa.assignment_action_id),0,0,1 )) EMPCOUNT -- Bug 9409509
251 ,'PREV_EMP_COUNT=P'
252 ,SUM(decode(fue.user_entity_name ,'X_KR_PREV_BP_NUMBER',1,0)) PREVEMPCOUNT
253 ,'REPORTING_PERIOD=P'
254 ,pay_magtape_generic.get_parameter_value('REPORTING_PERIOD')
255 FROM pay_assignment_actions xpaa
256 ,pay_payroll_actions xppa
257 ,hr_organization_information hoi
258 ,hr_organization_information ihoi
259 ,hr_organization_units hou
260 ,ff_user_entities fue
261 ,ff_archive_items fai
262 ,ff_user_entities fue1
263 ,ff_archive_items fai1
264 WHERE xppa.action_type = 'X'
265 and xppa.action_status = 'C'
266 --Bug 5069923
267 and xppa.payroll_action_id = nvl(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'),xpaa.payroll_action_id)
268 and xppa.effective_date between
269 to_date(pay_magtape_generic.get_parameter_value('TARGET_YEAR')||'0101','YYYYMMDD')
270 and to_date(pay_magtape_generic.get_parameter_value('TARGET_YEAR')||'1231','YYYYMMDD')
271 and xppa.payroll_action_id = xpaa.payroll_action_id
272 and xpaa.action_status = 'C'
273 and xppa.report_type = 'KR_SEP'
274 and hoi.organization_id = xpaa.tax_unit_id
275 and hoi.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
276 and hoi.organization_id = ihoi.organization_id
277 and ihoi.org_information_context = 'KR_INCOME_TAX_OFFICE'
278 and ihoi.org_information9 = pay_magtape_generic.get_parameter_value('TAX_OFFICE_CODE')
279 and hoi.org_information2 = pay_magtape_generic.get_parameter_value('BP_NUMBER')
280 and hoi.org_information10 = pay_magtape_generic.get_parameter_value('CORPORATION_ID')
281 and hoi.organization_id = hou.organization_id
285 'A_GROSS_STAX_ASG_RUN',
282 and hou.business_group_id = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
283 and fai.user_entity_id = fue.user_entity_id
284 and fue.user_entity_name IN ('A_GROSS_ITAX_ASG_RUN',
286 'A_GROSS_RTAX_ASG_RUN',
287 'A_TAXABLE_EARNINGS_WI_PREV_ASG_RUN',
288 'X_KR_PREV_BP_NUMBER',
289 'A_NON_STAT_SEP_PAY_TAXABLE_EARNINGS_ASG_RUN',
290 'A_RECEIVABLE_SEPARATION_PAY_ASG_RUN',
291 'A_TAXABLE_EARNINGS_WI_PREV_ASG_RUN',
292 'A_AMOUNT_EXPECTED_ASG_RUN',
293 'A_PREV_SEP_PENS_DTLS_AMT_EXP_STAT_SEP_ENTRY_VALUE',
294 'A_DEFERRED_AMOUNT_STAT_ASG_RUN',
295 'A_DEFERRED_AMOUNT_NONSTAT_ASG_RUN',
296 'A_RECEIVED_AMOUNT_NONSTAT_ASG_RUN',
297 'A_RECEIVED_AMOUNT_STAT_ASG_RUN',
298 'A_AMOUNT_EXPECTED_NONSTAT_ASG_RUN',
299 'A_RECEIVABLE_NON_STAT_SEP_PAY_ASG_RUN',
300 'A_NON_STAT_SEP_PAY_TAXABLE_EARNINGS_ASG_RUN',
301 'A_TRANSFERRED_AMOUNT_FOR_STAT_SEP_PAY_ASG_RUN',
302 'A_TRANSFERRED_AMOUNT_FOR_NONSTAT_SEP_PAY_ASG_RUN',
303 'A_PREV_SEP_PENS_DTLS_DEFERRED_AMOUNT_STAT_ENTRY_VALUE',
304 'A_PREV_SEP_PENS_DTLS_RECEIVED_AMOUNT_STAT_ENTRY_VALUE',
305 'A_PREV_SEP_PENS_DTLS_DEFERRED_AMOUNT_NONSTAT_ENTRY_VALUE',
306 'A_PREV_SEP_PENS_DTLS_RECEIVED_AMOUNT_NONSTAT_ENTRY_VALUE',
307 'A_PREV_SEP_PENS_DTLS_AMT_EXP_NONSTAT_SEP_ENTRY_VALUE',
308 'A_FULL_DEF_TAXABLE_EARNINGS_ASG_RUN',
309 'A_FULL_DEF_NS_EARNINGS_ASG_RUN',
310 'A_DEFERRED_TAX_AMOUNT_ASG_RUN',
311 'X_KR_PREV_SEP_PAY',
312 'X_KR_PREV_SEP_INSURANCE',
313 'X_KR_PREV_SEP_ALLOWANCE',
314 'X_KR_PREV_LUMP_SUM_STAT_ENT',
315 'X_KR_PREV_LUMP_SUM_NST_ENT'
316 )
317 and fai.context1 = xpaa.assignment_Action_id
318 --Bug 5069923
319 and ( (pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID') is null)
320 or (hr_assignment_set.assignment_in_set(pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID'), xpaa.assignment_id) = 'Y')
321 )
322 and fue1.user_entity_name = pay_kr_spay_efile_fun_pkg.get_archive_item(xpaa.assignment_Action_id) -- Bug 9409509
323 and fue1.legislation_code = 'KR'
324 and fai1.user_entity_id = fue1.user_entity_id
325 and fai1.context1 = xpaa.assignment_Action_id
326 and fai1.value > '0';
327
328 /*********************************************************
329 * Cursor to get the start_date and end_date of multiple
330 * years. Required Parameters:
331 * TARGET_YEAR
332 *
333 *********************************************************/
334 cursor c_multiple_year -- 4095229
335 is
336 select 'START_DATE=P',
337 fnd_date.date_to_canonical( to_date(pay_magtape_generic.get_parameter_value('TARGET_YEAR') || '0101',
338 'YYYYMMDD') ) start_date,
339 'END_DATE=P',
340 fnd_date.date_to_canonical( to_date(pay_magtape_generic.get_parameter_value('TARGET_YEAR') || '1231',
341 'YYYYMMDD') ) end_date
342
343 from dual;
344
345 /*********************************************************
346 * Cursor to get assignemnts for record C
347 * Parameters:
348 * CORPORATION_ID
349 * BUSINESS_GROUP_ID
350 * START_DATE
351 * END_DATE
352 * TAX_OFFICE_CODE
353 * BP_NUMBER
354 *********************************************************/
355
356 cursor c_record_c
357 is
358 SELECT 'ASSIGNMENT_ACTION_ID=C'
359 ,xpaa.assignment_action_id
360 -- Bug 4251252
361 ,'ASSIGNMENT_ACTION_ID=P'
362 ,xpaa.assignment_action_id
363 -- Bug 4251252
364 ,'ASG_ID=P'
365 ,xpaa.assignment_id
366 ,'PREVIOUS_EMP_COUNT=P'
367 ,pay_kr_nonstat_spay_efile_fun.get_prev_emp_count(xpaa.assignment_action_id)
368 -- Bug 7712932
369 ,'STAT_SEP_PAY_OVR_TAX_BRK=P'
370 ,pay_kr_nonstat_spay_efile_fun.get_sep_pay_ovr_tax_brk(xpaa.assignment_action_id,xpaa.assignment_id,'SEP')
371 ,'NSTAT_SEP_PAY_OVR_TAX_BRK=P'
372 ,pay_kr_nonstat_spay_efile_fun.get_sep_pay_ovr_tax_brk(xpaa.assignment_action_id,xpaa.assignment_id,'NSEP')
373 -- End of Bug 7712932
374 ,'BP_NUMBER=P'
375 ,hoi.org_information2
376 ,'CORP_NAME=P'
377 ,hoi.org_information1
378 /* Bug 10216334 */
379 ,'PREV_SEP_PEN_ACCOUNT_NUMBER=P'
380 ,nvl(pay_kr_report_pkg.get_result_value_char( pay_kr_nonstat_spay_efile_fun.get_assignment_action_id(xpaa.assignment_action_id,xpaa.assignment_id),to_number(pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID'))
381 ,'PREV_SEP_PENS_DTLS', 'Account Number'),' ')
382 ,'PREV_HIRING_DATE=P'
383 ,pay_kr_nonstat_spay_efile_fun.get_element_rr_date_value( pay_kr_nonstat_spay_efile_fun.get_assignment_action_id(xpaa.assignment_action_id,xpaa.assignment_id),
384 'PREV_FH_DATE', 'WKPD')
385 ,'PREV_LEAVING_DATE=P'
386 ,pay_kr_nonstat_spay_efile_fun.get_element_rr_date_value( pay_kr_nonstat_spay_efile_fun.get_assignment_action_id(xpaa.assignment_action_id,xpaa.assignment_id),
387 'PREV_LL_DATE', 'WKPD')
388 ,'PREV_HIRING_DATE_NST=P'
389 ,pay_kr_nonstat_spay_efile_fun.get_element_rr_date_value( pay_kr_nonstat_spay_efile_fun.get_assignment_action_id(xpaa.assignment_action_id,xpaa.assignment_id),
390 'PREV_FH_DATE','WKPD_NON_STAT_SEP_PAY')
391 ,'PREV_LEAVING_DATE_NST=P'
392 ,pay_kr_nonstat_spay_efile_fun.get_element_rr_date_value( pay_kr_nonstat_spay_efile_fun.get_assignment_action_id(xpaa.assignment_action_id,xpaa.assignment_id),
393 'PREV_LL_DATE', 'WKPD_NON_STAT_SEP_PAY')
394 ,'EFFECTIVE_DATE=P'
395 ,to_char(xppa.effective_date,'YYYY/MM/DD')
396
397
398 FROM pay_assignment_actions xpaa
399 ,pay_payroll_actions xppa
400 ,hr_organization_information hoi
401 ,hr_organization_units hou
402 ,ff_user_entities fue
403 ,ff_archive_items fai
404 ,ff_user_entities fue1
405 ,ff_archive_items fai1
406 WHERE xppa.action_type = 'X'
407 and xppa.action_status = 'C'
408 and xppa.effective_date
409 between fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('START_DATE'))
410 and fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('END_DATE'))
411 and xppa.payroll_action_id = xpaa.payroll_action_id
412 --Bug 5069923
413 and xppa.payroll_action_id = nvl(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'),xpaa.payroll_action_id)
414 and xpaa.action_status = 'C'
415 and xppa.report_type = 'KR_SEP'
416 and hoi.organization_id = xpaa.tax_unit_id
417 and hoi.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
418 and hoi.org_information2 = pay_magtape_generic.get_parameter_value('BP_NUMBER')
419 and hoi.org_information10 = pay_magtape_generic.get_parameter_value('CORPORATION_ID')
420 and hoi.organization_id = hou.organization_id
421 and hou.business_group_id = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
422 and fue.user_entity_name = pay_kr_spay_efile_fun_pkg.get_archive_item(xpaa.assignment_Action_id) -- Bug 9409509
423 and fue.legislation_code = 'KR'
424 and fai.user_entity_id = fue.user_entity_id
425 and fai.context1 = xpaa.assignment_action_id
426 --Bug 5069923
427 and ( (pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID') is null)
428 or (hr_assignment_set.assignment_in_set(pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID'), xpaa.assignment_id) = 'Y')
429 )
430 and fai.value > '0'
431 and fue1.user_entity_name = 'X_KR_EMP_NI'
432 and fai1.user_entity_id = fue1.user_entity_id
433 and fai1.context1 = xpaa.assignment_action_id
434 order by fai1.value;
435
436 /*********************************************************
437 * Cursor to get ELEMENT_ENTRY_ID for record D
438 * Parameters:
439 * ASG_ID
440 * ASSIGNMENT_ACTION_ID
441 *********************************************************/
442
443 cursor c_record_d
444 is
445 select
446 'ELEMENT_ENTRY_ID=C',
447 pay_kr_nonstat_spay_efile.get_record_d_context(pay_magtape_generic.get_parameter_value('ASSIGNMENT_ACTION_ID'))
448 from dual;
449
450 end pay_kr_nonstat_spay_efile;