[Home] [Help]
PACKAGE: APPS.PAY_NZ_EMS
Source
1 package pay_nz_ems as
2 /* $Header: pynzems.pkh 120.0.12000000.3 2007/04/27 13:44:44 dduvvuri noship $
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 */
42
43 level_cnt number ;
44
45 /*
46 ** Cursor to retrieve Inland Revenue employer monthly schedule e-file
47 ** header record
48 */
49
50 cursor c_header is
51 select 'HEADER_RECORD_INDICATOR=P'
52 , 'HDR' -- header_record_indicator
53 , 'EMPLOYER_IRD_NUMBER=P'
54 , lpad(replace(oi.org_information1,'-',NULL), 9, '0') -- employer_IRD_number
55 , 'RETURN_PERIOD=P'
56 , to_char(last_day(b.effective_date),'YYYYMMDD') -- return_period
57 , 'PAYROLL_CONTACT_NAME=P'
58 , oi.org_information2 -- payroll_contact_name
59 , 'PAYROLL_CONTACT_PHONE=P'
60 , replace
61 (replace(oi.org_information3, '(' ,NULL)
62 , ')' ,NULL
63 ) -- payroll_contact_phone
64 , 'PAYE=P'
65 , to_char(b.paye_deductions*100) -- PAYE
66 , 'CHILD_SUPPORT=P'
67 , to_char(b.child_support_deductions*100) -- child_support
68 , 'STUDENT_LOANS=P'
69 , to_char(b.student_loan_deductions*100) -- student_loans
70 -- Changes for bug 5846247 start
71 , 'KIWISAVER_EMPLOYEE_CONTRIBUTIONS=P'
72 , to_char(b.kiwisaver_ee_contributions*100) -- Kiwisaver Employee Deductions
73 , 'KIWISAVER_EMPLOYER_CONTRIBUTIONS=P'
74 , to_char(b.kiwisaver_er_contributions*100) -- Kiwisaver Employer Contributions
75 -- Changes for bug 5846247 end
76 , 'FAMILY_ASSISTANCE=P'
77 , '0' -- family_assistance
78 , 'GROSS_EARNINGS=P'
79 , to_char(b.gross_earnings*100) -- gross_earnings
80 , 'EARNINGS_NOT_LIABLE_FOR_ACC_EP=P'
81 , to_char(b.earnings_not_liable_for_acc_ep*100) -- earnings_not_liable_for_acc_ep
82 , 'IR_FORM_VERSION_NO=P'
83 -- Changed IR_form_version_no below for bug 5846247
84 , '0002' -- IR_form_version_no
85 , 'TRANSFER_HEAD_FLAG=P'
86 , 'notprinted'
87 from hr_organization_units ou /*Bug No 2920728*/
88 , hr_organization_information oi
89 , pay_nz_er_cal_mth_bal_v b
90 where ou.organization_id = pay_magtape_generic.get_parameter_value('REGISTERED_EMPLOYER')
91 and ou.business_group_id = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
92 and oi.organization_id = ou.organization_id
93 and oi.org_information_context = 'NZ_IRD_EMPLOYER'
94 and b.organization_id = to_char(ou.organization_id) ;
95
96 /*
97 ** Cursor to retrieve Inland Revenue employer monthly schedule e-file
98 ** detail records
99 */
100
101 cursor c_detail is
102 select 'DETAIL_RECORD_INDICATOR=P'
103 , 'DTL' -- detail_record_indicator
104 , 'EMPLOYEE_IRD_NUMBER=P'
105 , decode
106 (p.national_identifier
107 ,NULL, '000000000'
108 ,lpad(replace(p.national_identifier,'-',NULL), 9, '0')
109 ) -- employee_ird_number
110 , 'EMPLOYEE_NAME=P'
111 , replace
112 (substr(p.first_name || ' ' || p.last_name, 1, 20)
113 , ',', ' '
114 ) -- employee_name
115 , 'EMPLOYEE_TAX_CODE=P'
116 --
117 -- The NULL value was not recognised by the fast formula using the
118 -- default for ...
119 -- if ... was defaulted ...
120 -- therefore the string NULL VALUE is passed
121 --
122 , nvl(decode(prrv1.result_value,'N',prrv.result_value,'Y',(decode(prrv2.result_value,null,
123 prrv.result_value,'STC'))),'NULL VALUE') -- employee_tax_code
124 , 'START_DATE=P'
125 , decode(to_char(ptp.END_DATE ,'YYYYMM')
126 ,to_char(s.effective_date,'YYYYMM')
127 ,to_char(pos.date_start, 'YYYYMMDD')
128 ,'NULL VALUE'
129 ) -- start_date/*2276649*/
130 , 'FINISH_DATE=P'
131 , decode(to_char(nvl(pos.final_process_date,pos.last_standard_process_date), 'YYYYMM')
132 ,to_char(s.effective_date,'YYYYMM')
133 , to_char(pos.actual_termination_date,'YYYYMMDD')
134 ,'NULL VALUE'
135 ) -- finish_date/*2135629,2276649*/
136 , 'GROSS_EARNINGS=P'
137 , b.gross_earnings*100 -- gross_earnings
138 , 'EARNINGS_NOT_LIABLE_FOR_ACC_EP=P'
139 , b.earnings_not_liable_for_acc_ep*100 -- earnings_not_liable_for_acc_ep
140 , 'LUMP_SUM_INDICATOR=P'
141 , decode(b.extra_emol_at_low_tax_rate, 'Y', 1, 0) -- lump_sum_indicator
142 , 'PAYE_DEDUCTIONS=P'
143 , b.paye_deductions*100 -- paye_deductions
144 , 'CHILD_SUPPORT_DEDUCTIONS=P'
145 , b.child_support_deductions*100 -- child_support_deductions
146 , 'CHILD_SUPPORT_CODE=P'
147 , nvl(b.child_support_code, 'NULL VALUE') -- child_support_code
148 , 'STUDENT_LOAN_DEDUCTIONS=P'
149 , b.student_loan_deductions*100 -- student_loan_deductions
150 -- Changes for bug 5846247 start
151 , 'KIWISAVER_EMPLOYEE_CONTRIBUTIONS=P'
152 , b.kiwisaver_ee_contributions*100 -- Kiwisaver Employee Deductions
153 , 'KIWISAVER_EMPLOYER_CONTRIBUTIONS=P'
154 , b.kiwisaver_er_contributions*100 -- Kiwisaver Employer Contributions
155 -- Changes for bug 5846247 end
156 , 'FAMILY_ASSISTANCE=P'
157 , '0' -- family_assistance
158 , 'EMPLOYEE_NUMBER=P'
159 , p.employee_number
160 , 'VALID=P'
161 , decode(sign(least(b.gross_earnings,
162 b.earnings_not_liable_for_acc_ep,
163 b.paye_deductions,
164 b.child_support_deductions,b.student_loan_deductions)),-1,0 /* Bug No : 2755544 */
165 ,decode(sign(b.gross_earnings - (b.paye_deductions + b.child_support_deductions + b.student_loan_deductions)),-1,0,1)) flag
166 from hr_organization_units ou /*Bug No 2920728*/
167 , per_people_f p /*Bug No 2920728*/
168 , per_assignments_f a
169 , hr_soft_coding_keyflex scl
170 , pay_nz_asg_cal_mth_bal_v b
171 , pay_element_types_f et
172 , pay_input_values_f iv
173 , pay_input_values_f iv1
174 , pay_input_values_f iv2
175 , pay_run_results prr
176 , pay_run_results prr1
177 , pay_run_results prr2
178 , pay_run_result_values prrv
179 , pay_run_result_values prrv1
180 , pay_run_result_values prrv2
181 , pay_assignment_actions assact
182 , pay_payroll_actions pact
183 , fnd_sessions s
184 , per_periods_of_service pos
185 , per_time_periods ptp
186 where ou.organization_id = pay_magtape_generic.get_parameter_value('REGISTERED_EMPLOYER')
187 and ou.business_group_id = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
188 and b.organization_id = to_char(ou.organization_id)
189 and p.effective_start_date <= last_day(s.effective_date)
190 and p.effective_end_date >= to_date('01/' || to_char(s.effective_date, 'mm/yyyy'), 'dd/mm/yyyy')
191 -- the following sub query makes sure that we get the person record in effect at the end of the month
192 and p.effective_start_date = (select max(p2.effective_start_date)
193 from per_people_f p2 /*Bug No 2920728*/
194 where p2.person_id = p.person_id
195 and p2.effective_start_date <= last_day(s.effective_date)
196 )
197 and scl.soft_coding_keyflex_id = a.soft_coding_keyflex_id
198 and b.assignment_id = a.assignment_id
199 --
200 -- start TAR 2188761.995 change.
201 --
202 -- and pos.DATE_START between ptp.START_DATE and ptp.END_DATE /*2276649*/
203 -- and ptp.PAYROLL_ID = a.PAYROLL_ID
204 --
205 AND (ptp.payroll_id,ptp.start_date) = (SELECT payroll_id,MIN(start_date)
206 FROM per_time_periods
207 WHERE payroll_id = a.payroll_id
208 AND end_date > pos.date_start
209 GROUP BY payroll_id)
210 -- end TAR 2188761.995 change
211 --
212 and b.organization_id = scl.segment1
213 and a.person_id = p.person_id
214 and a.effective_start_date <= last_day(s.effective_date)
215 and a.effective_end_date >= to_date('01/' || to_char(s.effective_date, 'mm/yyyy'), 'dd/mm/yyyy')
216 -- the following sub query makes sure that we get the assignment in effect at the end of the month
217 and a.effective_start_date = (select max(a2.effective_start_date)
218 from per_assignments_f a2
219 where a2.person_id = a.person_id
220 and a2.assignment_id = a.assignment_id
221 and a2.effective_start_date <= last_day(s.effective_date)
222 )
223 and et.element_name in ('PAYE Information', 'Withholding Tax Information Record')
224 and et.effective_start_date <= last_day(s.effective_date)
225 and et.effective_end_date >= to_date('01/' || to_char(s.effective_date, 'mm/yyyy'), 'dd/mm/yyyy')
226 -- the following sub query makes sure that we get the element type in effect at the end of the month
227 and et.effective_start_date = (select max(et2.effective_start_date)
228 from pay_element_types_f et2
229 where et2.element_type_id = et.element_type_id
230 and et2.effective_start_date <= last_day(s.effective_date)
231 )
232 and assact.assignment_action_id = ( select max(assact4.assignment_action_id)
233 from pay_assignment_actions assact4
234 ,pay_payroll_actions pact4
235 ,pay_run_results prr4
236 where assact4.assignment_id = a.assignment_id
237 and assact4.assignment_action_id = prr4.assignment_action_id
238 and pact4.payroll_action_id = assact4.payroll_action_id
239 and pact4.effective_date between to_date('01/' || to_char(s.effective_date, 'mm/yyyy'), 'dd/mm/yyyy')
240 and last_day(s.effective_date)
241 and prr4.element_type_id = et.element_type_id )
242 and iv.name = 'Tax Code'
243 and iv.element_type_id = et.element_type_id
244 and prr.element_type_id = et.element_type_id
245 and prr.run_result_id = prrv.run_result_id
246 and prrv.input_value_id = iv.input_value_id
247 and prr.assignment_action_id = assact.assignment_action_id
248 and iv1.name = 'Special Tax Code'
249 and iv1.element_type_id = et.element_type_id
250 and prr1.element_type_id = et.element_type_id
251 and prr1.run_result_id = prrv1.run_result_id
252 and prrv1.input_value_id = iv1.input_value_id
253 and prr1.assignment_action_id = assact.assignment_action_id
254 and iv2.name = 'Tax Rate'
255 and iv2.element_type_id = et.element_type_id
256 and prr2.element_type_id = et.element_type_id
257 and prr2.run_result_id = prrv2.run_result_id
258 and prrv2.input_value_id = iv2.input_value_id
259 and prr2.assignment_action_id = assact.assignment_action_id
260 and pact.payroll_action_id = assact.payroll_action_id
261 and assact.assignment_id = a.assignment_id
262 and s.session_id = userenv('SESSIONID')
263 and pos.person_id = p.person_id
264 -- the next couple of clauses identify the period of service, if there is more then one period
265 -- of service in the month this query will return record for each period of service
266 and pos.date_start <= last_day(s.effective_date)
267 and nvl(pos.final_process_date, s.effective_date) >=
268 to_date('01/' || to_char(s.effective_date, 'mm/yyyy'), 'dd/mm/yyyy') /*2135629*/
269 order by flag desc;
270
271
272 end pay_nz_ems ;