1 package pay_nz_ems AUTHID CURRENT_USER as
2 /* $Header: pynzems.pkh 120.10.12020000.3 2013/02/20 07:31:55 mingyhua ship $
3 **
4 ** Copyright (c) 1999 Oracle Corporation
5 ** All Rights Reserved
6 **
7 ** EMS mag tape writer stuff
8 **
9 ** Change List
10 ** ===========
11 **
12 ** Date Author Reference Description
13 ** -----------+--------+---------+-------------
14 ** 27 JUL 1999 JTURNER N/A Big bang
15 ** 14 OCT 1999 ATOPOL N/A Bug-Fixes
16 ** 21 APR 2000 ABAJPAI N/A b.oraganizaion_id is made char in the view
17 ** ,corresponding changes done
18 ** 15 SEP 2000 SRIKRISH 1406196 Added a join in cursor c_detail
19 ** subquery input value to get correct
20 ** effective_start_date
21 ** 10 OCT 2001 HNAINANI 1864377 According to the requirements for Student Loan Tax Calc
22 ** STC should be displayed as a tax code only if 'STC ='Y and
23 ** Paye Tax Rate is not null - Otherwise display the actual Tax Code
24 ** 06 Dec 2001 Apunekar 2135629 Fixed EMS issues
25 ** 18 Dec 2001 Ragovind 2154439 Removed the Dynamic view for performance issue
26 ** 30 Jan 2002 shoskatt 2197687 Changed the cursor c_detail so that the value of Tax Code, Special Tax
27 ** code and Tax Rate is fetched from the view
28 ** 06 Mar 2002 Ragovind 2233511 Changed the c_detail cursor to fix the 2197687 and 2233511 bugs.
29 ** 25 Mar 2002 Apunekar 2276649 Corrected reporting of start and finish dates.
30 ** 05 Apr 2002 Apunekar 2306743 Handled multiple assignments beng reported.
31 ** 01 May 2002 SRussell 2352807 TAR 2188761.995. Return employees whose
32 ** start date was prior to payroll start.
33 ** 25 May 2002 Apunekar 2280938 Extra columns added for displaying negative values in exceptions report
34 ** 28 May 2002 Ragovind 2381433 Changed the c_detail cursor for employee having a payroll with +ve offset
35 ** 08 Nov 2002 Apunekar 2600912 Removed redundant columns as per the view changes.
36 ** 17 Jan 2002 srrajago 2755544 Included negative value check for student_loan_deduction in c_detail cursor.
37 ** 30 May 2002 puchil 2920728 Changed both the cursors to use secured views.
38 ** 22 Dec 2003 puchil 3306269 Removed fnd_sessions table in the header cursor to remove Cartesian joins.
39 ** 12-Apr-2007 dduvvuri 5846247 Modified header and detail cursors for KiwiSaver Requirement
40 ** 24-Apr-2007 dduvvuri 5846247 Modified spellings for certain Formula parameters req by KiwiSaver
41 ** 06-Nov-2008 dduvvuri 7480679 Modified c_detail cursor
42 ** 07-Nov-2008 dduvvuri 7480679 Modified c_detail cursor after review comments.
43 ** Removed usages of tables 'pay_input_values_f iv2' , 'pay_run_results prr2',
44 ** 'pay_run_result_values prrv2' from the query
45 ** 07-Nov-2007 dduvvuri 7480679 Backed out the above change and implemented it in a different way.
46 ** Modified the c_detail cursor to get the value of 'Tax Rate' input field
47 ** from a function in package pay_nz_ems_tax_rate
48 ** 10-NOv-2007 dduvvuri 7480679 Removed usages of tables 'pay_input_values_f iv2' , 'pay_run_results prr2',
49 ** 'pay_run_result_values prrv2' from the query.Also removed the usage of
50 ** "distinct" keyword in c_detail query. The parameters to the function call
51 ** in package pay_nz_ems_tax_rate are also changed.
52 ** 07-JAN-2010 dduvvuri 9237657 Made Changes in ems header and detail cursors due to
53 ** introduction of statutory changes effective 01-Apr-2010
54 ** 03-Feb-2010 dduvvuri 9237657 Multiply the values of payroll tax credits by -1 in header , detail cursors
55 ** because balance value of tax credits is negative and EMS must report positive value
56 ** 18-Mar-2010 dduvvuri 9484915 Payroll package version is based on Apps version 11i or R12
57 ** 05-Apr-2011 jmarupil 11730973 Added hints for increasing performance
58 ** 03-Feb-2012 dduvvuri 13627558 Modified both cursors for NZ statutory updates 2012.
59 ** 20-Feb-2013 mingyhua 16227130 Switched order of first_name and last_name in detail cursor.
60 ** It should be last_name first_name
61 */
62
63 level_cnt number ;
64
65 /*
66 ** Cursor to retrieve Inland Revenue employer monthly schedule e-file
67 ** header record
68 */
69
70 cursor c_header is
71 select 'HEADER_RECORD_INDICATOR=P'
72 , 'HDR' -- header_record_indicator
73 , 'EMPLOYER_IRD_NUMBER=P'
74 , lpad(replace(oi.org_information1,'-',NULL), 9, '0') -- employer_IRD_number
75 , 'RETURN_PERIOD=P'
76 , to_char(last_day(b.effective_date),'YYYYMMDD') -- return_period
77 , 'PAYROLL_CONTACT_NAME=P'
78 , oi.org_information2 -- payroll_contact_name
79 , 'PAYROLL_CONTACT_PHONE=P'
80 , replace
81 (replace(oi.org_information3, '(' ,NULL)
82 , ')' ,NULL
83 ) -- payroll_contact_phone
84 , 'PAYE=P'
85 , to_char(b.paye_deductions*100) -- PAYE
86 , 'CHILD_SUPPORT=P'
87 , to_char(b.child_support_deductions*100) -- child_support
88 , 'STUDENT_LOANS=P'
89 -- modified for bug 13627558
90 , to_char((b.student_loan_deductions + b.slcir_deduction + b.slbor_deduction)*100 ) -- student_loans
91 -- Changes for bug 5846247 start
92 , 'KIWISAVER_EMPLOYEE_CONTRIBUTIONS=P'
93 , to_char(b.kiwisaver_ee_contributions*100) -- Kiwisaver Employee Deductions
94 , 'KIWISAVER_EMPLOYER_CONTRIBUTIONS=P'
95 , to_char(b.kiwisaver_er_contributions*100) -- Kiwisaver Employer Contributions
96 -- Changes for bug 5846247 end
97 -- Changes for bug 9237657 start
98 , 'PAYROLL_TAX_CREDITS=P'
99 , to_char(-1*b.payroll_tax_credits*100) -- Payroll giving Tax Credits
100 -- Changes for bug 9237657 end
101 , 'FAMILY_ASSISTANCE=P'
102 , '0' -- family_assistance
103 , 'GROSS_EARNINGS=P'
104 , to_char(b.gross_earnings*100) -- gross_earnings
105 , 'EARNINGS_NOT_LIABLE_FOR_ACC_EP=P'
106 , to_char(b.earnings_not_liable_for_acc_ep*100) -- earnings_not_liable_for_acc_ep
107 -- Changes for bug 9237657 start
108 , 'PAYROLL_PACKAGE_VERSION=P'
109 , ( SELECT decode(substr(PRODUCT_VERSION,1,2),'11','Oracle HRMS V11i','Oracle HRMS V12')
110 FROM fnd_application a,fnd_product_installations p
111 WHERE a.application_id = p.application_id
112 AND substr(a.application_short_name, 1, 3) = 'PAY') -- Payroll Package Version
113 , 'PAYROLL_CONTACT_EMAIL=P'
114 , decode(instr(substr(oi.org_information5,
115 decode(sign(instr(oi.org_information5,'@')),0,length(oi.org_information5)+1,instr(oi.org_information5,'@'))
116 ),'.',1,2
117 ),0,decode(instr(substr(oi.org_information5,instr(oi.org_information5,'@')),'.'),0,null,oi.org_information5),null
118 ) -- Payroll Contact Email
119 , 'IR_FORM_VERSION_NO=P'
120 , '0004' -- IR_form_version_no
121 -- Changes for bug 9237657 end
122 , 'TRANSFER_HEAD_FLAG=P'
123 , 'notprinted'
124 from hr_organization_units ou /*Bug No 2920728*/
125 , hr_organization_information oi
126 , pay_nz_er_cal_mth_bal_v b
127 where ou.organization_id = pay_magtape_generic.get_parameter_value('REGISTERED_EMPLOYER')
128 and ou.business_group_id = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
129 and oi.organization_id = ou.organization_id
130 and oi.org_information_context = 'NZ_IRD_EMPLOYER'
131 and b.organization_id = to_char(ou.organization_id) ;
132
133 /*
134 ** Cursor to retrieve Inland Revenue employer monthly schedule e-file
135 ** detail records
136 */
137
138 cursor c_detail is
139 select 'DETAIL_RECORD_INDICATOR=P'
140 , 'DTL' -- detail_record_indicator
141 , 'EMPLOYEE_IRD_NUMBER=P'
142 , decode
143 (p.national_identifier
144 ,NULL, '000000000'
145 ,lpad(replace(p.national_identifier,'-',NULL), 9, '0')
146 ) -- employee_ird_number
147 , 'EMPLOYEE_NAME=P'
148 , replace
149 (substr(p.last_name || ' ' || p.first_name, 1, 20)
150 , ',', ' '
151 ) -- employee_name
152 , 'EMPLOYEE_TAX_CODE=P'
153 --
154 -- The NULL value was not recognised by the fast formula using the
155 -- default for ...
156 -- if ... was defaulted ...
157 -- therefore the string NULL VALUE is passed
158 -- 7480679 - made use of package pay_nz_ems_tax_rate to get the tax rate input value.
159 , nvl(decode(prrv1.result_value,'N',prrv.result_value,'Y', (decode(pay_nz_ems_tax_rate.get_tax_rate(s.effective_date,prr.run_result_id),'N',
160 prrv.result_value,'STC'))),'NULL VALUE') -- employee_tax_code
161 , 'START_DATE=P'
162 , decode(to_char(ptp.END_DATE ,'YYYYMM')
163 ,to_char(s.effective_date,'YYYYMM')
164 ,to_char(pos.date_start, 'YYYYMMDD')
165 ,'NULL VALUE'
166 ) -- start_date/*2276649*/
167 , 'FINISH_DATE=P'
168 , decode(to_char(nvl(pos.final_process_date,pos.last_standard_process_date), 'YYYYMM')
169 ,to_char(s.effective_date,'YYYYMM')
170 , to_char(pos.actual_termination_date,'YYYYMMDD')
171 ,'NULL VALUE'
172 ) -- finish_date/*2135629,2276649*/
173 , 'GROSS_EARNINGS=P'
174 , b.gross_earnings*100 -- gross_earnings
175 , 'EARNINGS_NOT_LIABLE_FOR_ACC_EP=P'
176 , b.earnings_not_liable_for_acc_ep*100 -- earnings_not_liable_for_acc_ep
177 , 'LUMP_SUM_INDICATOR=P'
178 , decode(b.extra_emol_at_low_tax_rate, 'Y', 1, 0) -- lump_sum_indicator
179 , 'PAYE_DEDUCTIONS=P'
180 , b.paye_deductions*100 -- paye_deductions
181 , 'CHILD_SUPPORT_DEDUCTIONS=P'
182 , b.child_support_deductions*100 -- child_support_deductions
183 , 'CHILD_SUPPORT_CODE=P'
184 , nvl(b.child_support_code, 'NULL VALUE') -- child_support_code
185 , 'STUDENT_LOAN_DEDUCTIONS=P'
186 , b.student_loan_deductions*100 -- student_loan_deductions
187 -- changes for bug 13627558 start
188 , 'SLCIR_DEDUCTION=P'
189 , b.slcir_deduction*100
190 , 'SLBOR_DEDUCTION=P'
191 , b.slbor_deduction*100
192 -- changes for bug 13627558 end
193 -- Changes for bug 5846247 start
194 , 'KIWISAVER_EMPLOYEE_CONTRIBUTIONS=P'
195 , b.kiwisaver_ee_contributions*100 -- Kiwisaver Employee Deductions
196 , 'KIWISAVER_EMPLOYER_CONTRIBUTIONS=P'
197 , b.kiwisaver_er_contributions*100 -- Kiwisaver Employer Contributions
198 -- Changes for bug 5846247 end
199 -- Changes for bug 9237657 start
200 , 'PAYROLL_TAX_CREDITS=P'
201 , -1*b.payroll_tax_credits*100
202 -- Changes for bug 9237657 end
203 , 'FAMILY_ASSISTANCE=P'
204 , '0' -- family_assistance
205 , 'EMPLOYEE_NUMBER=P'
206 , p.employee_number
207 , 'VALID=P'
208 , decode(sign(least(b.gross_earnings,
209 b.earnings_not_liable_for_acc_ep,
210 b.paye_deductions,
211 b.child_support_deductions,b.student_loan_deductions)),-1,0 /* Bug No : 2755544 */
212 ,decode(sign(b.gross_earnings - (b.paye_deductions + b.child_support_deductions + b.student_loan_deductions)),-1,0,1)) flag
213 from hr_organization_units ou /*Bug No 2920728*/
214 , per_people_f p /*Bug No 2920728*/
215 , per_assignments_f a
216 , hr_soft_coding_keyflex scl
217 , pay_nz_asg_cal_mth_bal_v b
218 , pay_element_types_f et
219 , pay_input_values_f iv
220 , pay_input_values_f iv1
221 -- , pay_input_values_f iv2 /* 7480679 */
222 , pay_run_results prr
223 , pay_run_results prr1
224 -- , pay_run_results prr2 /* 7480679 */
225 , pay_run_result_values prrv
226 , pay_run_result_values prrv1
227 -- , pay_run_result_values prrv2 /* 7480679 */
228 , pay_assignment_actions assact
229 , pay_payroll_actions pact
230 , fnd_sessions s
231 , per_periods_of_service pos
232 , per_time_periods ptp
233 where ou.organization_id = pay_magtape_generic.get_parameter_value('REGISTERED_EMPLOYER')
234 and ou.business_group_id = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
235 and b.organization_id = to_char(ou.organization_id)
236 and p.effective_start_date <= last_day(s.effective_date)
237 and p.effective_end_date >= to_date('01/' || to_char(s.effective_date, 'mm/yyyy'), 'dd/mm/yyyy')
238 -- the following sub query makes sure that we get the person record in effect at the end of the month
239 and p.effective_start_date = (select max(p2.effective_start_date)
240 from per_people_f p2 /*Bug No 2920728*/
241 where p2.person_id = p.person_id
242 and p2.effective_start_date <= last_day(s.effective_date)
243 )
244 and scl.soft_coding_keyflex_id = a.soft_coding_keyflex_id
245 and b.assignment_id = a.assignment_id
246 --
247 -- start TAR 2188761.995 change.
248 --
249 -- and pos.DATE_START between ptp.START_DATE and ptp.END_DATE /*2276649*/
250 -- and ptp.PAYROLL_ID = a.PAYROLL_ID
251 --
252 AND (ptp.payroll_id,ptp.start_date) = (SELECT payroll_id,MIN(start_date)
253 FROM per_time_periods
254 WHERE payroll_id = a.payroll_id
255 AND end_date > pos.date_start
256 GROUP BY payroll_id)
257 -- end TAR 2188761.995 change
258 --
259 and b.organization_id = scl.segment1
260 and a.person_id = p.person_id
261 and a.effective_start_date <= last_day(s.effective_date)
262 and a.effective_end_date >= to_date('01/' || to_char(s.effective_date, 'mm/yyyy'), 'dd/mm/yyyy')
263 -- the following sub query makes sure that we get the assignment in effect at the end of the month
264 and a.effective_start_date = (select max(a2.effective_start_date)
265 from per_assignments_f a2
266 where a2.person_id = a.person_id
267 and a2.assignment_id = a.assignment_id
268 and a2.effective_start_date <= last_day(s.effective_date)
269 )
270 and et.legislation_code = 'NZ'
271 and et.element_name in ('PAYE Information', 'Withholding Tax Information Record')
272 and et.effective_start_date <= last_day(s.effective_date)
273 and et.effective_end_date >= to_date('01/' || to_char(s.effective_date, 'mm/yyyy'), 'dd/mm/yyyy')
274 -- the following sub query makes sure that we get the element type in effect at the end of the month
275 and et.effective_start_date = (select /*+ INDEX(et2,PAY_ELEMENT_TYPES_F_PK) */ max(et2.effective_start_date)
276 from pay_element_types_f et2
277 where et2.element_type_id = et.element_type_id
278 and et2.effective_start_date <= last_day(s.effective_date)
279 and et2.effective_end_date > et2.effective_start_date
280 )
281 and assact.assignment_action_id = ( select max(assact4.assignment_action_id)
282 from pay_assignment_actions assact4
283 ,pay_payroll_actions pact4
284 ,pay_run_results prr4
285 where assact4.assignment_id = a.assignment_id
286 and assact4.assignment_action_id = prr4.assignment_action_id
287 and pact4.payroll_action_id = assact4.payroll_action_id
288 and pact4.effective_date between to_date('01/' || to_char(s.effective_date, 'mm/yyyy'), 'dd/mm/yyyy')
289 and last_day(s.effective_date)
290 and prr4.element_type_id = et.element_type_id )
291 and iv.name = 'Tax Code'
292 and iv.element_type_id = et.element_type_id
293 and prr.element_type_id = et.element_type_id
294 and prr.run_result_id = prrv.run_result_id
295 and prrv.input_value_id = iv.input_value_id
296 and prr.assignment_action_id = assact.assignment_action_id
297 and iv1.name = 'Special Tax Code'
298 and iv1.element_type_id = et.element_type_id
299 and prr1.element_type_id = et.element_type_id
300 and prr1.run_result_id = prrv1.run_result_id
301 and prrv1.input_value_id = iv1.input_value_id
302 and prr1.assignment_action_id = assact.assignment_action_id
303 /* 7480679 - Removed all joins related to "Tax Rate" input value */
304 and pact.payroll_action_id = assact.payroll_action_id
305 and assact.assignment_id = a.assignment_id
306 and s.session_id = userenv('SESSIONID')
307 and pos.person_id = p.person_id
308 -- the next couple of clauses identify the period of service, if there is more then one period
309 -- of service in the month this query will return record for each period of service
310 and pos.date_start <= last_day(s.effective_date)
311 and nvl(pos.final_process_date, s.effective_date) >=
312 to_date('01/' || to_char(s.effective_date, 'mm/yyyy'), 'dd/mm/yyyy') /*2135629*/
313 order by flag desc;
314
315 end pay_nz_ems ;
[Home] [Help]