eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE: APPS.PAY_KR_SPAY_EFILE

Source


1 package pay_kr_spay_efile AUTHID CURRENT_USER as
2 /* $Header: pykrspef.pkh 120.6.12010000.6 2010/02/26 03:35:10 pnethaga ship $ */
3 
4 level_cnt number;
5 
6 cursor c_sep_tax_header_count
7 is
8 SELECT  'BP_COUNT=P'
9         ,count(distinct hoi.org_information2||ihoi.org_information9) bp_count
10         ,'CORPORATION_ID=P'
11         ,hoi.org_information10
12 FROM    pay_assignment_actions      xpaa
13        ,pay_payroll_actions         xppa
14        ,hr_organization_information hoi
15        ,hr_organization_units       hou
16        ,hr_organization_units       phou
17        ,hr_organization_information phoi
18        ,hr_organization_information ihoi
19 WHERE   xppa.action_type                        = 'X'
20         and   xppa.action_status                = 'C'
21         and   xppa.effective_date between
22         pay_magtape_generic.get_parameter_value('START_DATE')
23         and
24         pay_magtape_generic.get_parameter_value('END_DATE')
25         and   xppa.payroll_action_id            = xpaa.payroll_action_id
26         and   xpaa.action_status                = 'C'
27 	--Bug 5069923
28 	and   xppa.payroll_action_id 		= nvl(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'),xpaa.payroll_action_id)    --Bug 5069923
29 	--Bug 5069923
30   	and   (   (pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID') is null)
31 	       or (hr_assignment_set.assignment_in_set(pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID'), xpaa.assignment_id) = 'Y')
32 	      )
33 	--Bug 5069923
34         and   (   (pay_magtape_generic.get_parameter_value('REPORT_FOR')='A')
35                or (    (hoi.organization_id in (select posev.ORGANIZATION_ID_child
36 						from   PER_ORG_STRUCTURE_ELEMENTS posev
37 						where  posev.org_structure_version_id=(pay_magtape_generic.get_parameter_value('ORG_STRUC_VERSION_ID'))
38 						       and exists ( select null
39 								    from   hr_organization_information
40 								    where  organization_id = posev.ORGANIZATION_ID_child
41 									   and org_information_context = 'CLASS'
42 									   and org_information1 = 'KR_BUSINESS_PLACE'
43 								   )
44 						       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')))
45 						       connect by prior ORGANIZATION_ID_child = ORGANIZATION_ID_PARENT
46 						)
47 			)
48         	     or (hoi.organization_id    = pay_magtape_generic.get_parameter_value('PRIMARY_BP_ID')
49 			)
50 		  )
51 	      )
52         and   xppa.report_type                  = 'KR_SEP'
53         and   xpaa.tax_unit_id                  = hou.organization_id
54         and   hou.organization_id               = hoi.organization_id
55         and   hoi.org_information_context       = 'KR_BUSINESS_PLACE_REGISTRATION'
56         and   hoi.org_information10             = phoi.org_information10
57         and   phou.organization_id              = pay_magtape_generic.get_parameter_value('PRIMARY_BP_ID')
58         and   phou.organization_id              = phoi.organization_id
59         and   phoi.org_information_context      = 'KR_BUSINESS_PLACE_REGISTRATION'
60         and   ihoi.org_information_context      = 'KR_INCOME_TAX_OFFICE'
61 	--Bug 5069923
62 	and   xppa.business_group_id            = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
63         and   ihoi.organization_id              = hou.organization_id
64         group by hoi.org_information10;
65 
66 cursor c_sep_tax_header
67 is
68 SELECT      'CORP_PHONE_NUMBER=P'
69            ,hla.telephone_number_1      corp_phone_number
70            ,'REPRESENTATIVE_TAX_OFFICE_CODE=P'
71            ,ihoi.org_information9       corp_tax_office_code
72            ,'CORP_NAME=P'
73            ,choi.org_information1       corp_name
74            ,'CORP_REP_NAME=P'
75            ,choi.org_information6       corp_rep_name
76            ,'CORP_NUMBER=P'
77            ,choi.org_information2       corp_number
78            ,'BP_NUMBER=P'
79            ,hoi.org_information2        bp_number
80            ,'REPORTED_DATE=P'
81            ,fnd_date.date_to_canonical(pay_magtape_generic.get_parameter_value('REPORTED_DATE'))
82            ,'CHARACTER_TYPE=P'
83            ,'101'  character_type
84            ,'REPORTING_PERIOD_P=P'
85            ,'1' reporting_period
86 	   ,'HOME_TAX_ID=P'
87 	   ,nvl(pay_magtape_generic.get_parameter_value('HOME_TAX_ID'), ' ')
88   FROM      hr_organization_information hoi
89            ,hr_organization_information ihoi
90            ,hr_organization_information choi
91            ,hr_organization_units       hou
92            ,hr_locations_all            hla
93  WHERE     hou.organization_id               = pay_magtape_generic.get_parameter_value('PRIMARY_BP_ID')
94   and      hou.organization_id               = hoi.organization_id
95   and      hoi.org_information_context       = 'KR_BUSINESS_PLACE_REGISTRATION'
96   and      hou.organization_id               = ihoi.organization_id
97   and      ihoi.org_information_context      = 'KR_INCOME_TAX_OFFICE'
98   and      choi.organization_id              = to_number(hoi.org_information10)
99   and      choi.org_information_context      = 'KR_CORPORATE_INFORMATION'
100   and      hla.location_id(+)                = hou.location_id
101   and      hla.style(+)                      = 'KR'
102   -- Bug 4272920
103   and      hou.organization_id = (select xpaa.tax_unit_id
104                   from  pay_assignment_actions      xpaa
105                         ,pay_payroll_actions        xppa
106                  where xppa.action_type             = 'X'
107                  and   xppa.action_status           = 'C'
108                  and   xppa.effective_date between  pay_magtape_generic.get_parameter_value('START_DATE')
109                                                and  pay_magtape_generic.get_parameter_value('END_DATE')
110                  and   xppa.payroll_action_id       = xpaa.payroll_action_id
111 		 --Bug 5069923
112 	  	 and   xppa.payroll_action_id 	    = nvl(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'),xpaa.payroll_action_id)    --Bug 5069923
113 		 --Bug 5069923
114   		 and   (    (pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID') is null)
115 			 or (hr_assignment_set.assignment_in_set(pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID'), xpaa.assignment_id) = 'Y')
116 		       )
117                  and   xpaa.action_status           = 'C'
118                  and   xppa.report_type             = 'KR_SEP'
119 		 and   xppa.report_category 	    = 'KR_SEP'
120 		 and   xppa.report_qualifier	    = 'KR'
121                  and   xpaa.tax_unit_id             = hou.organization_id
122 		 and   rownum 			    = 1
123              );
124   -- End of 4272920
125 
126 cursor c_sep_tax_B
127 is
128 SELECT  distinct 'BP_NUMBER=P',
129         hoi.org_information2,
130         'TAX_OFFICE_CODE=P',
131         ihoi.org_information9
132 FROM    pay_assignment_actions      xpaa
133         ,pay_payroll_actions         xppa
134         ,hr_organization_information hoi
135         ,hr_organization_information ihoi
136 WHERE   xppa.action_type                        = 'X'
137         and   xppa.action_status                = 'C'
138         and   xppa.effective_date between
139               -- Bug 4253329
140               pay_magtape_generic.get_parameter_value('START_DATE')
141               -- End of 4253329
142         and   pay_magtape_generic.get_parameter_value('END_DATE')
143         and   xppa.payroll_action_id            = xpaa.payroll_action_id
144 	--Bug 5069923
145 	and   xppa.payroll_action_id 		= nvl(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'),xpaa.payroll_action_id)
146 	--Bug 5069923
147 	and   (     (pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID') is null)
148 		 or (hr_assignment_set.assignment_in_set(pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID'), xpaa.assignment_id) = 'Y')
149 	      )
150         and   xpaa.action_status                = 'C'
151         and   xppa.report_type                  = 'KR_SEP'
152 	--Bug 5069923
153         and   (     (pay_magtape_generic.get_parameter_value('REPORT_FOR')='A')
154 		 or (     (hoi.organization_id in ( select posev.ORGANIZATION_ID_child
155 						    from   PER_ORG_STRUCTURE_ELEMENTS posev
156 						    where  posev.org_structure_version_id=(pay_magtape_generic.get_parameter_value('ORG_STRUC_VERSION_ID'))
157 						           and exists (select null
158 								       from   hr_organization_information
159 								       where  organization_id = posev.ORGANIZATION_ID_child
160 									      and org_information_context = 'CLASS'
161 									      and org_information1 = 'KR_BUSINESS_PLACE'
162 								       )
163 							    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')))
164 							    connect by prior ORGANIZATION_ID_child = ORGANIZATION_ID_PARENT
165 						   )
166 			   )
167          		or (hoi.organization_id = pay_magtape_generic.get_parameter_value('PRIMARY_BP_ID')
168 			   )
169 		     )
170 	      )
171         and   hoi.organization_id               = xpaa.tax_unit_id
172         and   hoi.org_information_context       = 'KR_BUSINESS_PLACE_REGISTRATION'
173         and   hoi.organization_id               = ihoi.organization_id
174         and   ihoi.org_information_context      = 'KR_INCOME_TAX_OFFICE'
175         and   hoi.org_information10             = pay_magtape_generic.get_parameter_value('CORPORATION_ID')
176         and   xppa.business_group_id            = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
177         order by hoi.org_information2;
178 
179 cursor c_sep_tax_B_summary
180 is
181 SELECT  'TOTAL_ITAX=P'
182         ,SUM(decode(fue.user_entity_name,'A_GROSS_ITAX_ASG_RUN',fai.value,0)) ITAX
183         ,'TOTAL_STAX=P'
184         ,SUM(decode(fue.user_entity_name,'A_GROSS_STAX_ASG_RUN',fai.value,0)) STAX
185         ,'TOTAL_RTAX=P'
186         ,SUM(decode(fue.user_entity_name,'A_GROSS_RTAX_ASG_RUN',fai.value,0)) RTAX
187         ,'TOTAL_TAX=P'
188         ,SUM(decode(fue.user_entity_name ,'A_TAXABLE_EARNINGS_WI_PREV_ASG_RUN',  -- 5652360
189              pay_kr_spay_efile_fun_pkg.get_sep_pay_amount(xpaa.assignment_action_id,fai.value),0))  TOTALTAX
190         ,'EMPLOYEE_COUNT=P'
191         ,sum(decode(decode(fue.user_entity_name ,'A_TAXABLE_EARNINGS_WI_PREV_ASG_RUN',
192 	pay_kr_spay_efile_fun_pkg.get_sep_pay_amount(xpaa.assignment_action_id,fai.value),0),0,0,1))  EMPCOUNT --Bug 9409509
193         ,'PREV_EMP_COUNT=P'
194         ,SUM(decode(fue.user_entity_name ,'X_KR_PREV_BP_NUMBER',1,0))  PREVEMPCOUNT
195   FROM  pay_assignment_actions      xpaa
196        ,pay_payroll_actions         xppa
197        ,hr_organization_information hoi
198        ,hr_organization_information ihoi
199        ,hr_organization_units       hou
200        ,ff_user_entities            fue
201        ,ff_archive_items            fai
202  WHERE   xppa.action_type                       = 'X'
203   and   xppa.action_status                      = 'C'
204   and   xppa.effective_date between
205         -- Bug 4253329
206         pay_magtape_generic.get_parameter_value('START_DATE')
207         -- End of 4253329
208   and   pay_magtape_generic.get_parameter_value('END_DATE')
209   and   xppa.payroll_action_id                  = xpaa.payroll_action_id
210   --Bug 5069923
211   and   xppa.payroll_action_id 		        = nvl(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'),xpaa.payroll_action_id)
212   and   xpaa.action_status                      = 'C'
213   and   xppa.report_type                        = 'KR_SEP'
214   and   hoi.organization_id                     = xpaa.tax_unit_id
215   and   hoi.org_information_context             = 'KR_BUSINESS_PLACE_REGISTRATION'
216   and   hoi.organization_id                     = ihoi.organization_id
217   and   ihoi.org_information_context            = 'KR_INCOME_TAX_OFFICE'
218   and   ihoi.org_information9                   = pay_magtape_generic.get_parameter_value('TAX_OFFICE_CODE')
219   and   hoi.org_information2                    = pay_magtape_generic.get_parameter_value('BP_NUMBER')
220   and   hoi.org_information10                   = pay_magtape_generic.get_parameter_value('CORPORATION_ID')
221   and   hoi.organization_id                     = hou.organization_id
222   and   hou.business_group_id                   = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
223   and   fai.user_entity_id                      = fue.user_entity_id
224   and   fue.user_entity_name                    IN ('A_GROSS_ITAX_ASG_RUN',
225                                                     'A_GROSS_STAX_ASG_RUN',
226                                                     'A_GROSS_RTAX_ASG_RUN',
227                                                     'A_TAXABLE_EARNINGS_WI_PREV_ASG_RUN',
228                                                     'X_KR_EMP_NAME',
229                                                     'X_KR_PREV_BP_NUMBER')
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   fai.context1= xpaa.assignment_action_id
235 -- 3627111
236   and   not exists ( select 'x'
237 	                 from ff_user_entities            fue1
238 	                     ,ff_archive_items            fai1
239 	                 where fue1.user_entity_name      in ('A_NON_STAT_SEP_PAY_TAXABLE_EARNINGS_ASG_RUN','A_RECEIVABLE_NON_STAT_SEP_PAY_ASG_RUN')
240 	                 and fue1.legislation_code         = 'KR'
241 	                 and fai1.user_entity_id           = fue1.user_entity_id
242 	                 and fai1.context1                 = xpaa.assignment_action_id
243 	                 and fai1.value                    > '0' );
244 
245 
246 cursor c_sep_tax_B_detail
247 is
248 SELECT  'ASSIGNMENT_ACTION_ID=C'
249         ,max(xpaa.assignment_action_id)
250 	,'REPORTING_PERIOD=P'
251 	,pay_magtape_generic.get_parameter_value('REPORTING_PERIOD')
252 FROM    pay_assignment_actions      xpaa
253        ,pay_payroll_actions         xppa
254        ,hr_organization_information hoi
255        ,hr_organization_information ihoi
256        ,hr_organization_units       hou
257 WHERE   xppa.action_type                        = 'X'
258   and   xppa.action_status                      = 'C'
259   and   xppa.effective_date between
260         -- Bug 4253329
261         pay_magtape_generic.get_parameter_value('START_DATE')
262         -- End of 4253329
263   and   pay_magtape_generic.get_parameter_value('END_DATE')
264   and   xppa.payroll_action_id                  = xpaa.payroll_action_id
265   --Bug 5069923
266   and   xppa.payroll_action_id 		        = nvl(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'),xpaa.payroll_action_id)
267   and   xpaa.action_status                      = 'C'
268   and   xppa.report_type                        = 'KR_SEP'
269   and   hoi.organization_id                     = xpaa.tax_unit_id
270   and   hoi.org_information_context             = 'KR_BUSINESS_PLACE_REGISTRATION'
271   and   hoi.organization_id                     = ihoi.organization_id
272   --Bug 5069923
273   and	(     (pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID') is null)
274            or (hr_assignment_set.assignment_in_set(pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID'), xpaa.assignment_id) = 'Y')
275         )
276   and   ihoi.org_information_context            = 'KR_INCOME_TAX_OFFICE'
277   and   ihoi.org_information9                   = pay_magtape_generic.get_parameter_value('TAX_OFFICE_CODE')
278   and   hoi.org_information2                    = pay_magtape_generic.get_parameter_value('BP_NUMBER')
279   and   hoi.org_information10                   = pay_magtape_generic.get_parameter_value('CORPORATION_ID')
280   and   hoi.organization_id                     = hou.organization_id
281   and   hou.business_group_id                   = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID');
282 
283 
284 cursor c_sep_tax_C
285 is
286 SELECT  'ASSIGNMENT_ACTION_ID=C'
287        ,xpaa.assignment_action_id
288        -- Bug 4253329
289        ,'ASSIGNMENT_ACTION_ID=P'
290        ,xpaa.assignment_action_id
291        -- Bug 4253329
292        ,'ASG_ID=P'
293        ,xpaa.assignment_id
294        ,'PREVIOUS_EMP_COUNT=P'
295        ,nvl(pay_kr_spay_efile_fun_pkg.get_prev_emp_count(xpaa.assignment_action_id),0)
296        ,'SRS_START_DATE=P'
297        ,fnd_date.date_to_canonical(pay_magtape_generic.get_parameter_value('START_DATE'))
298        ,'SRS_END_DATE=P'
299        ,fnd_date.date_to_canonical(pay_magtape_generic.get_parameter_value('END_DATE'))
300        -- Bug 4201616
301        ,'RUN_TYPE_NAME=P'
302        ,prt.run_type_name
303        ,'PAYROLL_EFFECTIVE_DATE=P'
304        ,fnd_date.date_to_canonical(ppa.effective_date)
305        ,'BP_NUMBER=P'
306        ,hoi.org_information2
307        ,'CORP_NAME=P'
308        ,hoi.org_information1
309        -- End of 4201616
310        ,fai.value
311        -- Bug 4201616
312 FROM    pay_assignment_actions	    paa
313        ,pay_payroll_actions 	    ppa
314        ,pay_action_interlocks	    pai
315        ,pay_run_types		    prt
316        -- End of 4201616
317        ,pay_assignment_actions      xpaa
318        ,pay_payroll_actions         xppa
319        ,hr_organization_information hoi
320        ,hr_organization_information ihoi
321        ,hr_organization_units       hou
322        ,ff_user_entities            fue
323        ,ff_archive_items            fai
324 WHERE    xppa.action_type                       = 'X'
325 and     xppa.action_status                      = 'C'
326 and     xppa.effective_date between
327         -- Bug 4253329
328         pay_magtape_generic.get_parameter_value('START_DATE')
329         -- End of 4253329
330 and     pay_magtape_generic.get_parameter_value('END_DATE')
331 and     xppa.payroll_action_id                  = xpaa.payroll_action_id
332 --Bug 5069923
333 and     xppa.payroll_action_id 		        = nvl(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'),xpaa.payroll_action_id)
334 and     xpaa.action_status                      = 'C'
335 and     xppa.report_type                        = 'KR_SEP'
336 -- Bug 4201616
337 and 	paa.action_status 			= 'C'
338 and 	paa.source_action_id 			IS NOT NULL
339 and 	ppa.payroll_action_id 			= paa.payroll_action_id
340 and 	pai.locked_action_id			= paa.assignment_action_id
341 and 	xpaa.assignment_action_id 		= pai.locking_action_id
342 -- End of 4201616
343 and     hoi.organization_id                     = xpaa.tax_unit_id
344 and     hoi.org_information_context             = 'KR_BUSINESS_PLACE_REGISTRATION'
345 and     hoi.organization_id                     = ihoi.organization_id
346 and     ihoi.org_information_context            = 'KR_INCOME_TAX_OFFICE'
347 and     ihoi.org_information9                   = pay_magtape_generic.get_parameter_value('TAX_OFFICE_CODE')
348 and     hoi.org_information2                    = pay_magtape_generic.get_parameter_value('BP_NUMBER')
349 and     hoi.org_information10                   = pay_magtape_generic.get_parameter_value('CORPORATION_ID')
350 and     hoi.organization_id                     = hou.organization_id
351 --Bug 5069923
352 and	(     (pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID') is null)
353            or (hr_assignment_set.assignment_in_set(pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID'), xpaa.assignment_id) = 'Y')
354         )
355 and     hou.business_group_id                   = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
356 and     fue.user_entity_name                    in ('A_TAXABLE_EARNINGS_WI_PREV_ASG_RUN')
357 and     fai.user_entity_id                      = fue.user_entity_id
358 and     fai.context1                            = xpaa.assignment_action_id
359 and 	prt.run_type_id				= paa.run_type_id  -- Bug 4201616
360 -- 3627111
361 and     exists ( select 'x'
362 	                 from ff_user_entities            fue2
363 	                     ,ff_archive_items            fai2
364 	                 where fue2.user_entity_name       in ('A_TAXABLE_EARNINGS_WI_PREV_ASG_RUN','A_RECEIVABLE_SEPARATION_PAY_ASG_RUN')
365 	                 and fue2.legislation_code         = 'KR'
366 	                 and fai2.user_entity_id           = fue2.user_entity_id
367 	                 and fai2.context1                 = xpaa.assignment_action_id
368 	                 and fai2.value                    > '0' )
369 and     not exists ( select 'x'
370 	                 from ff_user_entities            fue1
371 	                     ,ff_archive_items            fai1
372 	                 where fue1.user_entity_name       in ('A_NON_STAT_SEP_PAY_TAXABLE_EARNINGS_ASG_RUN','A_RECEIVABLE_NON_STAT_SEP_PAY_ASG_RUN')
373 	                 and fue1.legislation_code         = 'KR'
374 	                 and fai1.user_entity_id           = fue1.user_entity_id
375 	                 and fai1.context1                 = xpaa.assignment_action_id
376 	                 and fai1.value                    > '0' )
377 order by fai.value;
378 
379 cursor c_sep_tax_D
380 is
381 select
382         'ELEMENT_ENTRY_ID=C',
383         fac.context,
384         fai.value
385 from
386         ff_contexts fc,
387         ff_user_entities fue,
388         ff_route_context_usages frc,
389         ff_archive_item_contexts fac,
390         ff_archive_items fai
391 where
392         fue.user_entity_name = 'X_KR_PREV_BP_NUMBER'
393         and fue.legislation_code = 'KR'
394         and fue.route_id = frc.route_id
395         and frc.context_id = fc.context_id
396         and fc.context_name = 'ELEMENT_ENTRY_ID'
397         and fai.context1 = pay_magtape_generic.get_parameter_value('ASSIGNMENT_ACTION_ID')
398         and fai.user_entity_id = fue.user_entity_id
399         and fac.archive_item_id = fai.archive_item_id
400         and fac.context_id = fc.context_id
401 order by
402        fai.value, fac.context ;
403 
404 end pay_kr_spay_efile;