1 package body pay_au_payment_summary as
2 /* $Header: pyaupsp.pkb 120.44.12010000.8 2008/08/06 06:52:46 ubhat ship $*/
3 /*
4 *** ------------------------------------------------------------------------+
5 *** Program: pay_au_payment_summary (Package Body)
6 ***
7 *** Change History
8 ***
9 *** Date Changed By Version Description of Change
10 *** --------- ---------- ------- ----------------------------------------+
11 *** 01 MAR 01 rbsinha 1.0 Initial version
12 *** 11 MAY 01 rbsinha 1.19 Bug #1768813 - to retrieve the value of
13 fringe_benefits calc_all_balances (effective_date,assignment_id,defined_balance_id)
14 is being used instead of calc_all_balances(assignment_action_id,defined_balance_id)
15 *** 11 MAY 01 rbsinha 1.19 Bug #1764010 - process_assignment cursor modified
16 Locked action id for payment summary report changed to
17 Locking action id
18 *** 11 MAY 01 rbsinha 1.21 Bug #1763290 -Modified employee_details
19 cursor to archive current and terminated
20 employees.Removed sort items from
21 archive_etp_details.
22 Bug #1763245 -Modified employer_details
23 and supplier_details
24 *** 22 MAY rbsinha 1.22 Bug No : 17164017 and Bug No : 1789886
25 Added archive item : X_UNION_NAMES and
26 X_PAYMENT_SUMMARY_SIGNATORY
27 *** 17 JUL rbsinha 1.25 Bug No : 1746093 - Added the fields
28 X_ETP_EMPLOYEE_PAYMENT_DATE and
29 X_ETP_ON_TERMINATION_PAID . These fields
30 are populated in the procedure
31 *** 15 Sep apunekar 1.26 Made changes for Bug1956018.
32 *** 18 Sep rbsinha 1.27 Made changes for Bug1951539 and Bug1903647.
33 *** 03 Oct apunekar 1.28 Made changes for Bug2021219.
34 *** 11 Oct apunekar 1.28 Made changes for Bug1955993
35 *** 29 Oct 01 shoskatt 1.39 The archived value of x_post_jun_83_untaxed_asg_ytd
36 *** should not contain lumpsum D Amount(Bug #2075782)
37 *** 31 Oct 01 shoskatt 1.40 If the Termination Date of the employee is in the
38 *** next financial year, then the employee type should
39 *** be current(Bug# 1973978)
40 *** 17 Apr 02 srrajago 1.41 Performance Issue (Bug No : 2263587)
41 *** 17 Apr 02 srrajago 1.42 Included checkfile command
42 *** 22 Apr 02 srrajago 1.43 In Cursor employee_detail, hr_locations_all has been replaced by hr_locations (Bug No : 2263587).
43 *** 06 May 02 vgsriniv 1.44 Cursor get_allowance_balances modified Bug 2359428, 2359423
44 *** 03 Jun 02 Ragovind 1.45 Cursor get_allowance_balances modified for Bug#2398315 to fix the bug 2359428 and to make compatible with both 8i and 9i database versions.
45 *** 15 Jul 02 shoskatt 1.46 For retrieving the ETP Payment values, get the maximum assignment
46 *** action id for which ETP values have been processed (Bug #2459527)
47 *** 16 Jul 02 shoskatt 1.47 Removed some redundant cursors,Introduced date check for
48 *** c_get_max_ass_act_id cursor,Also fixed Bug #2448446
49 *** 24 Jul 02 shoskatt 1.48 Improved performance by tuning cursors process_assignments,
50 *** c_archive_fbt_info and c_archive_info(Bug #2454595)
51 *** 08 Aug 02 shoskatt 1.49 Improved performance by tuning cursors process_assignments
52 *** and etp_code (Bug #2501105)
53 *** 09 Aug 02 Ragovind 1.50 Changed the cursor archive_employee cursor to fetch the data for Terminated-Rehired
54 *** Employees (Bug# 2448441).
55 *** 16 Aug 02 vgsriniv 1.52 Modified employee_details cursor. Removed to_date for the date parameter
56 *** in the select statement for employee type (Bug #2512431)
57 *** 26 Sep 02 kaverma 1.53 Modified cursor employer_details and etp_code. Also
58 *** introduced a cursor balance_exists (Bug No 2581436 )
59 *** 24 Oct 02 srrajago 1.54 Modified the cursor process_assignments (Bug No : 2574186)
60 *** 28 Oct 02 srrajago 1.55 Included action_type 'I' for 'Balance Initialization' in cursor
61 *** process_assignments (Bug No : 2574186)
62 *** 01 Nov 02 shoskatt 1.56 Changed the etp_code to check for action type and added the function
63 *** to check for the existence of Lumpsum C value. balance_exists cursor
64 *** has been changed. (Bug #2646912)
65 *** 18 Nov 02 Ragovind 1.57 Modified the cursor Get_Allowance_Balances for Performance Improvement for Bug#2665475.
66 *** 01 Dec 02 Apunekar 1.58 Modified cursor employee_details for Bug#2689175
67 *** 01 Dec 02 Apunekar 1.59 Added nocopy
68 *** 16 Jan 03 Apunekar 1.61 Modified process_assignments,employee_details cursor and
69 *** archive_etp_payment_details procedure (Bug 2574186)
70 *** 30 Jan 03 Kaverma 1.62 Modified 'process_assignments' cursor 'not exists' clause (Bug 2777142)
71 *** 30 Jan 03 Apunekar 1.63 Modified cursors employee_details and etp_details for bug 2774577
72 *** 18 Feb 03 Ragvoind 1.64 Add Join for Period of service ID to the Employee_details Cursor. Bug#2786146
73 *** 25 Feb 03 Ragovind 1.65 Modified the process_assignments cursor to avoid MERGE-JOIN-CARTESIAN Bug#2786835.
74 *** 04 Mar 03 Kaverma 1.66 Modified procedure 'archive_prepost_details' to use Lump Sum C Payment balance
75 *** to get pre and post 83 values (Bug No : 2826802)
76 *** 24 Mar 03 Kaverma 1.67 Modified cursor etp_code (Bug No : 2856638)
77 *** 02 May 03 Nanuradh 1.68 Modified the proceduce assignment_action_code by adding temporay check.
78 *** Excluded processing all the employees, where lump sum E payment exists
79 *** after 01-JUL-2003 (Bug: 2822446)
80 *** 02 May 03 Nanuradh 1.69 Modified the cursor c_employee_number to improve the performance.(Bug: 2822446)
81 *** 05 May 03 Nanuradh 1.70 Modified the cursor c_employee_number.
82 *** 05 May 03 Nanuradh 1.71 Corrected the message to be printed in the out file.
83 *** 05 May 03 Apunekar 1.72 Bug2855658 - Fixed for Retro Allowances
84 *** 05 May 03 Apunekar 1.73 Bug2855658 - Fixed for Performance
85 *** 15 May 03 Ragovind 1.74 Bug2819479 - ETP Pre/Post Enhancement.
86 *** 21 May 03 Apunekar 1.75 Bug2968127 - Cleared PL/SQL allowances table after processing.
87 *** 27 May 03 Apunekar 1.76 Bug2977533 - Ordered addresses by date_from.
88 *** 27-Jun-03 Hnainani 11591.5 Bug#3019374 Removed FBT check from Main Query
89 *** 28-Jun-03 SRussell 11591.7 Bug#3019374 Replaced process_Assignments
90 cursor after discussion with core.
91 *** 28-Jun-03 SRussell 1.83 Copied branched version 11591.7 into main code.
92 *** 28-Jun-03 SRussell 11592.2 Branched code. Put hints in
93 process_assignments cursor.
94 *** 04-Jul-03 Apunekar 11592.3 Added check to get latest person record in financial year in process_assignments
95 *** 04-Jul-03 Apunekar 115.84 Bug3019374 Changed process_assignments,etp_code,Get_retro_Entry_ids cursors , included branched version changes in mainline code.
96 *** 16-Jul-03 Apunekar 115.85 Includes fix for 3048724
97 *** 17-Jul-03 Apunekar 115.86 Bug3019374 Modified Cursor employee_details for performance fix.
98 *** 23 Jul 03 Nanuradh 115.87 Bug#2984390 - Added an extra parameter to the function call etp_prepost_ratios - ETP Pre/post Enhancement
99 *** 29 Jul 03 Nanuradh 115.88 Bug#2881272 - Modified the proceduce assignment_action_code by removing the temporary check.
100 *** Included processing of all the employees, where Lump sum E payment exists after 01-JUL-2003.
101 *** 28-Jul-03 Apunekar 115.90 Bug3073082 - Cursor employee_details removed date formatting in decode.
102 *** 29-Jul-03 Nanuradh 115.91 Bug#2881272 - Deleted the commented code in process_assignments function.
103 *** 28-Jul-03 Apunekar 115.92 Bug#3075153 - Modified employee_details
104 *** 04-Aug-03 Apunekar 115.93 Bug#3077528 - Backed out previous changes made
105 *** 06-Aug-03 Apunekar 115.94 Bug#3043049 - Used secured views in range_code and process_assignments for security
106 *** 06-Aug-03 Apunekar 115.95 Bug#3043049 - Used secured views in process_assignments_only for security
107 *** 13-AUG-03 Nanuradh 115.96 Bug#3095919 - If single lump sum E payment is less than $400 then the payment is included in gross earnings
108 *** otherwise it is included in Lump sum E payment.
109 *** 21-AUG-03 punmehta 115.97 Bug#3095919 - Modified the Cursor c_get_pay_earned_date to fetch effective_date instead of date_earned
110 *** 22-AUG-03 punmehta 115.98 Bug#3095919 - Modified the Cursor name c_get_pay_earned_date
111 *** to c_get_pay_effective_date and variable name of date_earned to effective_date
112 *** 22-Nov-03 punmehta 115.99 Bug#3263659 - Modified employee_details and archive code to check for the termination date
113 *** and added check for 'g_debug' before tracing for performance
114 *** 11-Dec-03 punmehta 115.100 Bug#3263659 - Archive_code , before calling archive_etp_code put a check for termination date
115 *** 06-Feb-04 punmehta 115.101 Bug#3245909 - Modified c_get_pay_effective_date cursor to fetch Dates for only master assignment action.
116 *** 06-Feb-04 punmehta 115.102 Bug#3245909 - Removed unwanted code.
117 *** 10-Feb-04 punmehta 115.103 Bug#3098353 - Archived a new flag which is false if all the balances are zero.
118 *** 11-Feb-04 punmehta 115.104 Bug#3098353 - Modified IF caluse for setting employee_end date.
119 *** 11-Feb-04 punmehta 115.105 Bug#3098353 - Renamed variables
120 *** 12-Feb-04 punmehta 115.106 Bug#3132178 - New procedure for calling magtape process.
121 *** 13-Feb-04 punmehta 115.107 Bug#3132178 - Coding Standards, changed SELECT to cursor
122 *** 18-Feb-04 jkarouza 115.08 Bug#3172963 - Use of BBR to retrieve balances wherever possible.
123 *** 02-Apr-04 punmehta 115.109 Bug#3549553 - Modified Union cursor and FBT cursor for balances.
124 *** 24-Apr-04 puchil 115.110 Bug#3586388 - Changed the cursor etp_code and removed gscc warnings.
125 *** 07-May-04 avenkatk 115.111 Bug#3580487 - Modified Union Cursor,removed call to hr_aubal.calc_all_balances.
126 *** 28-MAY-04 punmehta 115.112 Bug#3642409 - Added new index INDEX(a per_assignments_f_N12) to cursor process_assignments.
127 *** 28-MAY-04 punmehta 115.112 Bug#3642409 - Removed Rownum from cursor process_assignments.
128 *** 03-JUN-04 abhkumar 115.113 Bug#3661230 - Modified the process assignment cursor to take all the employees for archiving purpose.
129 *** 21-JUN-04 srrajago 115.118 Bug#3701869 - Modified the cursor csr_get_bbr_aseq to fetch max action_sequence instead of
130 *** assignment_action_id. Introduced cursor csr_get_bbr_assignment_action to pass the
131 *** correct assignment_action_id based on action_sequence to pay_balance_pkg.Also handled
132 *** the parameter Last Year Termination value setting if it is not enabled.
133 *** 21-JUN-04 srrajago 115.119 Bug#3701869 - Modified cursors 'csr_get_bbr_aseq' and 'csr_get_bbr_asg_actions'. Handled the parameter
134 *** Last Year Termination value setting if it is not enabled in another place which was
135 *** missed in the previous fix.
136 *** 25-JUN-04 srrajago 115.120 Bug#3603495 - Performance Fix - Modified cursors 'c_get_pay_effective_date' and 'etp_BA_or_BI'.
137 *** Introduced per_assignments_f table and its joins.
138 *** 03-JUL-04 srrajago 115.122 Bug#3743010 - Reverted back the fix in the previous version fixed for Bug: 3728357. This is same as
139 *** the version 115.20
140 *** 05-JUL-04 srrajago 115.123 Bug#3603495 - Performance Fix - Modified cursor 'c_get_pay_effective_date'.
141 *** 05-JUL-04 punmehta 115.124 Bug#3744930 - Modified for Re-hire
142 *** 05-JUL-04 punmehta 115.125 Bug#3755305 - Modified to get the action_id based on max action_squence
143 *** 09 Aug 04 abhkumar 115.126 Bug2610141 - Legal Employer Enhancement
144 *** 12 Aug 04 abhkumar 115.127 Bug2610141 - Modified code so that legal employer end date is not archived twice.
145 *** 06 Oct 04 avenkatk 115.128 Bug#3815301 - Modified cursor process_assignments and process_assignments_only for Payroll Updation.
146 *** 12 Oct 04 avenkatk 115.129 Bug#3815301 - Modified cursor process_assignments and process_assignments_only for better performance.
147 *** 09 Dec 04 ksingla 115.130 Bug#3937976 - Archived a new flag which is true if an employee ,current or terminated in the current year,
148 has zero balances in the current year.
149 *** 14 Dec 04 ksingla 115.131 Bug#3937976 - Removed redundant code,defaulted the parameter l_curr_term_0_bal_flag to 'NO'.
150 *** 15 Dec 04 hnainani 115.132 Bug#4015082 - Changes to archive Workplace Giving Deductions
151 *** 23 Dec 04 abhkumar 115.133 Bug#4063321 - Fixed issues related to Terminated employees, allowance details and LE dates
152 *** 24 Dec 04 abhkumar 115.134 Bug#4063321 - Removed GSCC errors
153 *** 30 Dec 04 ksingla 115.135 Bug#4000955 - Modified subquery of process_assignments and process_assignments_only not to archive employees for
154 any legal employer if Manual PS is issued for 'ALL' legal employers or without any legal employer.
155 *** 30 Dec 04 avenkatk 115.136 Bug#3899641 - Functional Dependancy Comment Added.
156 *** 07 Feb 05 ksingla 115.137 Bug#4161460 Modified the cursor get_allowance_balances
157 *** 12 Feb 05 abhargav 115.138 bug#4174037 Modified the cursor get_allowance_balances to avoid the unnecessary get_value() call.
158 *** 17 Feb 05 abhkumar 115.139 Bug#4161460 Rolled back changes made in 115.137
159 *** 16 Mar 05 ksingla 115.140 Bug#4177679 Modified the subquery of employee_details to archive correct employee le_start_date
160 when legal employer is changed .
161 *** 17 Mar 05 ksingla 115.141 Bug#4177679 Modified the subquery of employee_details to archive correct employee le_start_date
162 when person details are modified .
163 *** 31 Mar 05 ksingla 115.142 Bug#4177679 Modified the subquery of employee_details to archive correct employee le_end_date
164 when "Leave Loading" segment is modified.
165 *** 11 Apr 05 ksingla 115.143 Bug#4278361 Modified the cursor etp_paid. Added a new table pay_payrolls_f for performance issues.
166 *** 12 Apr 05 avenkatk 115.144 Bug#4299506 Modified Cursor employee_details - Sub Query modified to archive employee details for employees terminated in previous year.
167 *** 13 Apr 05 ksingla 115.145 Bug#4278379 Modified the cursor get_retro_entry_ids for performance.
168 *** 14 Apr 05 ksingla 115.146 Bug#4278379 Rolled back the changes done to cursor get_retro_entry_ids.
169 *** 18 Apr 05 ksingla 115.147 Bug#4278299 Modified the cursor get_allowance_balances for performance.
170 *** 19 Apr 05 ksingla 115.148 Bug#4281290 Modified the cursor etp_code for performance.
171 *** 20 Apr 05 ksingla 115.149 Bug#4177679 Modified for etp employee start date.
172 *** 25 Apr 05 ksingla 115.150 Bug#4278299 Rolled back the changes done in version 115.147.
173 *** 05 May 05 abhkumar 115.151 Bug#4377367 Added join in the cursor process_assignments to archive the end-dated employees.
174 *** 24 May 05 abhargav 115.152 Bug#4363057 Changes due to Retro Tax enhancement.
175 *** 24 May 05 abhargav 115.152 Bug#4387183 Modified file to to archive employee details for FBT employees.
176 Included the fix for Bug# 4375020
177 and included action_type 'V' in cursor csr_get_dates
178 *** 20 Jul 05 abhkumar 115.153 Bug#4418107 Modified call to pay_au_paye_ff.get_retro_period in adjust_retro_allowances
179 *** 08-AUG-05 hnainani 115.154 Bug#3660322 Added Quotes around Extra Information Query (-999) to not erro out for Character values
180 *** 02-OCT-05 abhkumar 115.156 Bug#4653934 Modified assignment action code to pick those employees who do have payroll attached
181 at start of the financial year but not at the end of financial year.
182 *** 15-Nov-05 avenkatk 115.157 Bug#4738470 Change cursor for Maximum assignment_action_id.
183 *** 02-DEC-05 abhkumar 115.158 Bug#4701566 Modified the cursor get_allowance_balances to get allowance value for end-dated
184 employees and also improve the performance of the query.
185 *** 06-DEC-05 abhkumar 115.159 Bug#4863149 Modified the code to raise error message when there is no defined balance id for the allowance balance.
186 *** 06-Dec-05 ksingla 115.160 Bug#4866415 Removed round for l_pre01jul1983_value and l_post30jun1983_value
187 *** 06-DEC095 avenkatk 115.161 Bug#4866934 Initialized balance values to 0 for FBT Employee
188 *** 04-Jan-06 ksingla 115.162 Bug#4925650 Modified cursor c_get_effective_date to resolve performance issues.
189 *** 04-Jan-06 ksingla 115.163 Bug#4926521 Modified cursor process_assignments to resolve performance issues.
190 *** 03-Mar-06 abhkumar 115.164 Bug#5075662 Modified for etp employee start date.
191 *** 16-Mar-06 ksingla 115.165 Bug#5099419 Modified to round off correctly.
192 *** 21-Mar-06 ksingla 115.166 Bug#5099419 Removed changes done for bug 4926521
193 *** 20-Jun-06 ksingla 115.167 Bug#5333143 Add_months included to fetch FBT_RATE and MEDICARE_LEVY
194 *** 29-Jun-06 avenkatk 115.168 Bug#5364017 Added check for "Generic" Address Style in Employee Address.
195 *** 03-Jul-06 avenkatk 115.169 Bug#5367061 ETP Start Date with be ETP Service Date entered else Hire Date. Backed out Fix 4177679.
196 *** 11-Aug-06 hnainani 115.170 Bug#5395393 Modified the v_lst_year_start variable to be assigned to financial year start
197 *** instead of FBT year Start. This was done to keep it consistent with the
198 *** Archive_code procedure and End Of Year Reconciliation Reports.
199 *** 01-Sep-06 sclarke 115.172 Bug#4925547 Altered archive of allowances and unions to support 2006/2007 layout.
200 *** 05-Oct-06 priupadh 115.174 The file is now dual maintained ,R12 version will be in Sync.
201 *** 06-Oct-06 hnainani 115.179 Bug# 5377624 Modified cursor c_get_pay_Effective_date to link Time Period Id with Date_Earned
202 *** to get the assignment action id
203 ***
204 *** 10-Oct-06 abhargav 115.180 Bug#4925547 Added bug references for changes done under Bug#4925547.
205 *** 17-Nov-06 abhargav 115.181 Bug#5591993 Modified cursor CSR_UNIONS_2006 to avoid MJC.
206 *** 20-Nov-06 sclarke 115.182 Bug#5666937 Enabled the 'order by' of the where clause of get_allowance_balances
207 *** cursor.
208 *** 28-Nov-06 sclarke 115.183 Bug#5679568 Procedure archive_2006_unions -
209 *** Handled case where both old and new unions exists but number of new unions is < 4
210 *** 19-Dec-06 ksingla 115.184 Bug#5708255 Added code to get value of global FBT_THRESHOLD
211 *** 27-Dec-06 ksingla 115.185 Bug#5708255 Added to_number to all occurrences of g_fbt_threshold
212 *** 8-Jan-06 ksingla 115.186 Bug#5743196 Added nvl to cursor Get_Retro_allowances
213 *** 23-Mar-07 ksingla 115.187 Bug#5371102 Modified for performance fixes
214 *** 26-Apr-07 sbaburao 115.188 Bug#5846278 Modified the function adjust_retro_allowance for Enhanced Retropay
215 *** 27-Apr-07 sbaburao 115.189 Bug#5846278 Modified the check for cursor get_legislation_rule to default the value of l_adv_retro_flag to 'N'
216 *** 10-May-07 priupadh 115.190 Bug#5956223 Modified function archive_etp_details added archive items X_TRANSITIONAL_ETP and X_PART_OF_PREVIOUS_ETP
217 *** 24-May-07 priupadh 115.191 Bug#6069614 Removed the if conditions which checks the death benefit type other then 'Dependent'
218 *** 01-Jun-07 tbakashi 115.192 Bug#6086060 Added trunc function for union values and allowance values.
219 *** 09-Jun-07 tbakashi 115.193 Bug#6086060 Added trunc in archive_limited_values and removed in archive_allowance_details
220 *** 10-Jun-07 priupadh 115.194 Bug#6112527 Added the changes removed in Bug#6069614 , with condition that for death benefit type Dependent
221 *** only archive if Fin Year is 2007/2008 or greater .
222 *** 14-Jun-07 tbakashi 115.195 Bug#6086060 Removing the trunc's added as they create inconsistency of value in data file.
223 *** 23-Aug-07 avenkatk 115.196 Bug#5371102 Modified Cursor csr_get_dates for performance.
224 *** 03-Sep-07 priupadh 115.197 Bug#6192381 For multiple ETP enh ,modified initialization_code,archive_code,archive_prepost_details
225 *** archive_etp_details and added procedure adjust_old_etp_values
226 *** 06-Sep-07 priupadh 115.198 Bug#6192381 Modified the Balance Names for Invalidity Component,removed commented code from archive_prepost_details
227 *** 07-Sep-07 priupadh 115.199 Bug#6192381 Removed multiple comments tab in one line to avoid chksql error
228 *** 07-Jan-08 avenkatk 115.200 Bug#6470581 Added Changes for supporting Amended payment summaries
229 *** 23-Jan-08 avenkatk 115.201 Bug#6740581 Resolved GSCC Errors
230 *** 30-May-08 priupadh 115.202 Bug#7135544 Added NVL (N) to variables lv_transitional_flag and lv_part_prev_etp_flag
231 *** if Null values are returned from cursor.
232 *** 18-Jun-08 avenkatk 115.203 Bug#7138494 Added Changes for RANGE_PERSON_ID
233 *** 26-Jun-08 priupadh 115.204 Bug#7171534 Added t_allowance_balance.count > 0 in adjust_retro_allowances
234 *** 07-Jul-08 avenkatk 115.205 Bug#7234263 Modified archive_employee_details for End assignments
235 *** 16-Jul-08 avenkatk 115.206 Bug#7242551 Modified archive_employee_details for fetching correct TFN
236 *** -------------------------------------------------------------------------------------------------------+
237 */
238 g_debug boolean;
239 g_business_group_id number;
240 g_package constant varchar2(30) := 'pay_au_payment_summary';
241 g_legislation_code constant varchar2(2) := 'AU';
242 g_dimension_id pay_balance_dimensions.balance_dimension_id%type;
243 g_balance_type_id pay_balance_types.balance_type_id%type; /*Bug#5591993*/
244 g_fbt_threshold ff_globals_f.global_value%TYPE ; /* Bug 5708255 */
245 --
246 /*
247 ** 4177679 this new flag would be Y when the etp employee date has to be archived
248 ** else N if le start date has to be archived for X_ETP_EMPLOYEE_START_DATE
249 */
250 g_le_etp_flag varchar2(10) ;
251 --
252 -------------------------------------------------------------------------------------------------------+
253 -- Define global variable to store defined_balance_id's and the corresponding balance values for BBR.
254 -------------------------------------------------------------------------------------------------------+
255 p_balance_value_tab pay_balance_pkg.t_balance_value_tab; -- 3172963
256 p_context_table pay_balance_pkg.t_context_tab; -- 2610141
257 p_result_table pay_balance_pkg.t_detailed_bal_out_tab; -- 2610141
258
259 ---------------------------------------------------------------------------------------------------------------------------+
260 --Bug 6192381
261 --For ETP Payment Balances define global variable to store defined_balance_id's and the corresponding balance values for BBR.
262 ---------------------------------------------------------------------------------------------------------------------------+
263
264 p_etp_balance_value_tab pay_balance_pkg.t_balance_value_tab;
265 p_etp_context_table pay_balance_pkg.t_context_tab;
266 p_etp_result_table pay_balance_pkg.t_detailed_bal_out_tab;
267
268 /* Bug 6470581 - PL/SQL table to hold Archive Items when submitted for Amended payment summary */
269 p_all_dbi_tab pay_au_payment_summary_amend.archive_db_tab ;
270
271 --------------------------------------------------------------------+
272 -- This procedure returns a sql string to select a range
273 -- of assignments eligible for archival.
274 --------------------------------------------------------------------+
275
276 procedure range_code
277 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
278 p_sql out nocopy varchar2) is
279 begin
280 g_debug := hr_utility.debug_enabled;
281 IF g_debug THEN
282 hr_utility.set_location('Start of range_code',1);
283 END if;
284 /* Bug#3043049*/
285 p_sql := ' select distinct p.person_id' ||
286 ' from per_people_f p,' ||
287 ' pay_payroll_actions pa' ||
288 ' where pa.payroll_action_id = :payroll_action_id' ||
289 ' and p.business_group_id = pa.business_group_id' ||
290 ' order by p.person_id';
291 IF g_debug THEN
292 hr_utility.set_location('End of range_code',2);
293 END if;
294 end range_code;
295
296
297 --------------------------------------------------------------------+
298 -- This procedure is used to set global contexts
299 -- however in current case it is a dummy procedure. In case this
300 -- procedure is not present then archiver assumes that no archival is required.
301 --------------------------------------------------------------------+
302
303
304 procedure initialization_code (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
305 --
306 --------------------------------------------------------------------------------------+
307 -- Cursor to setup table to hold balance_id's to be retrieved using BBR. (Bug3172963)
308 --------------------------------------------------------------------------------------+
309 --
310
311 /*Bug 6192381 Added New Balances for Multiple ETP Enhancement*/
312
313 cursor c_get_defined_balance_id is
314 select decode(pbt.balance_name, 'CDEP',1
315 , 'Leave Payments Marginal',2
316 , 'Lump Sum A Deductions',3
317 , 'Lump Sum A Payments',4
318 , 'Lump Sum B Deductions',5
319 , 'Lump Sum B Payments',6
320 , 'Lump Sum C Deductions',7
321 , 'Lump Sum C Payments',8
322 , 'Lump Sum D Payments',9
323 , 'Total_Tax_Deductions',10
324 , 'Termination Deductions',11
325 , 'Other Income',12
326 , 'Union Fees',13
327 , 'Invalidity Payments',14
328 , 'Lump Sum E Payments',15
329 , 'Earnings_Total', 16
330 , 'Workplace Giving Deductions' , 17
331 , 'ETP Deductions Transitional Not Part of Prev Term',18 /* Begin 6192381 */
332 , 'ETP Deductions Transitional Part of Prev Term',19
333 , 'ETP Deductions Life Benefit Not Part of Prev Term',20
334 , 'ETP Deductions Life Benefit Part of Prev Term',21
335 , 'Invalidity Payments Life Benefit Not Part of Prev Term',22
336 , 'Invalidity Payments Life Benefit Part of Prev Term',23
337 , 'Invalidity Payments Transitional Not Part of Prev Term',24
338 , 'Invalidity Payments Transitional Part of Prev Term',25) sort_index, /*4015082 ,6192381 */
339 pdb.defined_balance_id defined_balance_id
340 from pay_balance_types pbt
341 , pay_balance_dimensions pbd
342 , pay_defined_balances pdb
343 where pbt.balance_name in ( 'CDEP'
344 , 'Leave Payments Marginal'
345 , 'Lump Sum A Deductions'
346 , 'Lump Sum A Payments'
347 , 'Lump Sum B Deductions'
348 , 'Lump Sum B Payments'
349 , 'Lump Sum C Deductions'
350 , 'Lump Sum C Payments'
351 , 'Lump Sum D Payments'
352 , 'Total_Tax_Deductions'
353 , 'Termination Deductions'
354 , 'Other Income'
355 , 'Union Fees'
356 , 'Invalidity Payments'
357 , 'Lump Sum E Payments'
358 , 'Earnings_Total'
359 , 'Workplace Giving Deductions'
360 , 'ETP Deductions Transitional Not Part of Prev Term' /* Begin 6192381 */
361 , 'ETP Deductions Transitional Part of Prev Term'
362 , 'ETP Deductions Life Benefit Not Part of Prev Term'
363 , 'ETP Deductions Life Benefit Part of Prev Term'
364 , 'Invalidity Payments Life Benefit Not Part of Prev Term'
365 , 'Invalidity Payments Life Benefit Part of Prev Term'
366 , 'Invalidity Payments Transitional Not Part of Prev Term'
367 , 'Invalidity Payments Transitional Part of Prev Term') /* 4015082 , End 6192381 */
368 and pbd.database_item_suffix = '_ASG_LE_YTD' --2610141
369 and pbt.balance_type_id = pdb.balance_type_id
370 and pbd.balance_dimension_id = pdb.balance_dimension_id
371 and pbt.legislation_code = 'AU'
372 order by sort_index;
373
374 /*Bug 6192381 Added cursor c_get_etp_defined_balance_id for ETP Pay Balances*/
375
376 cursor c_get_etp_defined_balance_id is
377 select decode(pbt.balance_name, 'ETP Payments Transitional Not Part of Prev Term',1
378 , 'ETP Payments Transitional Part of Prev Term',2
379 , 'ETP Payments Life Benefit Not Part of Prev Term',3
380 , 'ETP Payments Life Benefit Part of Prev Term',4
381 , 'Lump Sum C Payments',5) sort_index,
382 pdb.defined_balance_id defined_balance_id
383 from pay_balance_types pbt
384 , pay_balance_dimensions pbd
385 , pay_defined_balances pdb
386 where pbt.balance_name in ('ETP Payments Transitional Not Part of Prev Term'
387 ,'ETP Payments Transitional Part of Prev Term'
388 ,'ETP Payments Life Benefit Not Part of Prev Term'
389 ,'ETP Payments Life Benefit Part of Prev Term'
390 ,'Lump Sum C Payments')
391 and pbd.database_item_suffix = '_ASG_LE_YTD'
392 and pbt.balance_type_id = pdb.balance_type_id
393 and pbd.balance_dimension_id = pdb.balance_dimension_id
394 and pbt.legislation_code = 'AU'
395 order by sort_index;
396
397 --
398 /*
399 ** 4863149 - Introduced a new cursor c_le_ytd_dimension_id
400 ** Moved here to avoid having to be executed for each assignment
401 */
402 cursor c_le_ytd_dimension_id is
403 select balance_dimension_id
404 from pay_balance_dimensions pbd
405 where pbd.dimension_name = '_ASG_LE_YTD'
406 and pbd.legislation_code = g_legislation_code;
407 /*Bug#5591993*/
408 -- Cursor to fetch the balance type id for Seeded balance Union Fees which will be
409 -- used in Curosr CSR_UNIONS_2006
410 CURSOR c_union_balance IS
411 SELECT balance_type_id from pay_balance_types
412 WHERE balance_name='Union Fees'
413 and legislation_code='AU';
414
415
416 /* Bug 6470581 - Added the following to initialize the global g_fbt_threshold */
417 CURSOR c_get_fbt_global(c_year_end DATE)
418 IS
419 SELECT global_value
420 FROM ff_globals_f
421 WHERE global_name = 'FBT_THRESHOLD'
422 AND legislation_code = 'AU'
423 AND c_year_end BETWEEN effective_start_date
424 AND effective_end_date;
425
426 CURSOR get_params(c_payroll_action_id per_all_assignments_f.assignment_id%type)
427 IS
428 SELECT to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY') Financial_year_end
429 ,ppa.business_group_id
430 FROM pay_payroll_actions ppa
431 WHERE ppa.payroll_action_id = c_payroll_Action_id;
432
433 l_fin_year_date DATE;
434
435 /* End Bug 6470581 */
436
437 l_procedure constant varchar2(80) := g_package || '.initialization_code';
438 --
439 begin
440 g_debug := hr_utility.debug_enabled;
441 if g_debug then
442 hr_utility.set_location('Start of initialization_code',1);
443 end if;
444 --
445 open c_union_balance;
446 fetch c_union_balance into g_balance_type_id;
447 close c_union_balance;
448 --
449 --
450 open c_le_ytd_dimension_id;
451 fetch c_le_ytd_dimension_id into g_dimension_id;
452 close c_le_ytd_dimension_id;
453 --
454 p_balance_value_tab.delete;
455 for csr_rec in c_get_defined_balance_id
456 loop
457 p_balance_value_tab(csr_rec.sort_index).defined_balance_id := csr_rec.defined_balance_id;
458 end loop;
459
460 if g_debug then
461 hr_utility.set_location('Defined Balance Ids for balances are:' , 15);
462 hr_utility.trace('--------------------------------------------');
463 hr_utility.trace('CDEP ===>' || p_balance_value_tab(1).defined_balance_id);
464 hr_utility.trace('Leave Payments Marginal ===>' || p_balance_value_tab(2).defined_balance_id);
465 hr_utility.trace('Earnings_Total ===>' || p_balance_value_tab(16).defined_balance_id);
466 hr_utility.trace('Workplace Giving ===>' || p_balance_value_tab(17).defined_balance_id); /*4015082 */
467 hr_utility.trace('Lump Sum A Deductions ===>' || p_balance_value_tab(3).defined_balance_id);
468 hr_utility.trace('Lump Sum A Payments ===>' || p_balance_value_tab(4).defined_balance_id);
469 hr_utility.trace('Lump Sum B Deductions ===>' || p_balance_value_tab(5).defined_balance_id);
470 hr_utility.trace('Lump Sum B Payments ===>' || p_balance_value_tab(6).defined_balance_id);
471 hr_utility.trace('Lump Sum C Deductions ===>' || p_balance_value_tab(7).defined_balance_id);
472 hr_utility.trace('Lump Sum C Payments ===>' || p_balance_value_tab(8).defined_balance_id);
473 hr_utility.trace('Lump Sum D Payments ===>' || p_balance_value_tab(9).defined_balance_id);
474 hr_utility.trace('Total_Tax_Deduction ===>' || p_balance_value_tab(10).defined_balance_id);
475 hr_utility.trace('Termination Deductions ===>' || p_balance_value_tab(11).defined_balance_id);
476 hr_utility.trace('Other Income ===>' || p_balance_value_tab(12).defined_balance_id);
477 hr_utility.trace('Union Fees ===>' || p_balance_value_tab(13).defined_balance_id);
478 hr_utility.trace('Invalidity Payments ===>' || p_balance_value_tab(14).defined_balance_id);
479 hr_utility.trace('Lump Sum E Payments ===>' || p_balance_value_tab(15).defined_balance_id);
480 hr_utility.trace('ETP Deductions Transitional Not Part of Prev Term ===>' || p_balance_value_tab(18).defined_balance_id);/*Begin Bug 6192381 */
481 hr_utility.trace('ETP Deductions Transitional Part of Prev Term ===>' || p_balance_value_tab(19).defined_balance_id);
482 hr_utility.trace('ETP Deductions Life Benefit Not Part of Prev Term ===>' || p_balance_value_tab(20).defined_balance_id);
483 hr_utility.trace('ETP Deductions Life Benefit Part of Prev Term ===>' || p_balance_value_tab(21).defined_balance_id);
484 hr_utility.trace('Invalidity Payments Life Benefit Not Part of Prev Term ===>' || p_balance_value_tab(22).defined_balance_id);
485 hr_utility.trace('Invalidity Payments Life Benefit Part of Prev Term ===>' || p_balance_value_tab(23).defined_balance_id);
486 hr_utility.trace('Invalidity Payments Transitional Not Part of Prev Term ===>' || p_balance_value_tab(24).defined_balance_id);
487 hr_utility.trace('Invalidity Payments Transitional Part of Prev Term ===>' || p_balance_value_tab(25).defined_balance_id);/*End Bug 6192381 */
488
489 end if;
490
491 /*Bug 6192381 For fetching Defined Balance Id's for new ETP payment Balances */
492 p_etp_balance_value_tab.delete;
493 for csr_etp_rec in c_get_etp_defined_balance_id
494 loop
495 p_etp_balance_value_tab(csr_etp_rec.sort_index).defined_balance_id := csr_etp_rec.defined_balance_id;
496 end loop;
497
498
499 if g_debug then
500 hr_utility.set_location('Defined Balance Ids for ETP Payment balances are:' , 15);
501 hr_utility.trace('--------------------------------------------');
502 hr_utility.trace('ETP Payments Transitional Not Part of Prev Term ===>' || p_etp_balance_value_tab(1).defined_balance_id);
503 hr_utility.trace('ETP Payments Transitional Part of Prev Term ===>' || p_etp_balance_value_tab(2).defined_balance_id);
504 hr_utility.trace('ETP Payments Life Benefit Not Part of Prev Term ===>' || p_etp_balance_value_tab(3).defined_balance_id);
505 hr_utility.trace('ETP Payments Life Benefit Part of Prev Term ===>' || p_etp_balance_value_tab(4).defined_balance_id);
506 hr_utility.trace('Lump Sum C Payments ===>' || p_etp_balance_value_tab(5).defined_balance_id);
507 end if;
508
509 /* Bug 6470581 - Initialize g_payment_summary_type */
510 g_payment_summary_type := 'O';
511
512 /* Bug 6470581 - Added code to initialize g_fbt_threshold */
513
514 OPEN get_params(p_payroll_action_id);
515 FETCH get_params INTO l_fin_year_date,g_business_group_id;
516 CLOSE get_params;
517
518 OPEN c_get_fbt_global (add_months(l_fin_year_date,-3)); /* Add_months included for bug 5333143 */
519 FETCH c_get_fbt_global into g_fbt_threshold;
520 CLOSE c_get_fbt_global;
521
522
523 if g_debug then
524 hr_utility.set_location('g_fbt_threshold '||g_fbt_threshold,19);
525 hr_utility.set_location('End of initialization_code',20);
526 end if;
527 /* End Changes Bug 6470581 */
528 exception
529 when others then
530 if g_debug then
531 hr_utility.set_location('Error in initialization_code',100);
532 end if;
533 raise;
534
535 end initialization_code;
536
537
538 /*
539 Bug 7138494 - Added Function range_person_on
540 --------------------------------------------------------------------
541 Name : range_person_on
542 Type : Function
543 Access: Private
544 Description: Checks if RANGE_PERSON_ID is enabled for
545 Archive process.
546 --------------------------------------------------------------------
547 */
548
549 FUNCTION range_person_on
550 RETURN BOOLEAN
551 IS
552
553 CURSOR csr_action_parameter is
554 select parameter_value
555 from pay_action_parameters
556 where parameter_name = 'RANGE_PERSON_ID';
557
558 CURSOR csr_range_format_param is
559 select par.parameter_value
560 from pay_report_format_parameters par,
561 pay_report_format_mappings_f map
562 where map.report_format_mapping_id = par.report_format_mapping_id
563 and map.report_type = 'AU_PAYMENT_SUMMARY'
564 and map.report_format = 'AU_PAYMENT_SUMMARY'
565 and map.report_qualifier = 'AU'
566 and par.parameter_name = 'RANGE_PERSON_ID'; -- Bug fix 5567246
567
568 l_return boolean;
569 l_action_param_val varchar2(30);
570 l_report_param_val varchar2(30);
571
572 BEGIN
573
574 g_debug := hr_utility.debug_enabled;
575
576 IF g_debug
577 THEN
578 hr_utility.set_location('range_person_on',10);
579 END IF;
580
581 BEGIN
582
583 open csr_action_parameter;
584 fetch csr_action_parameter into l_action_param_val;
585 close csr_action_parameter;
586
587 IF g_debug
588 THEN
589 hr_utility.set_location('range_person_on',20);
590 END IF;
591
592 open csr_range_format_param;
593 fetch csr_range_format_param into l_report_param_val;
594 close csr_range_format_param;
595 IF g_debug
596 THEN
597 hr_utility.set_location('range_person_on',30);
598 END IF;
599 EXCEPTION WHEN NO_DATA_FOUND THEN
600 l_return := FALSE;
601 END;
602 --
603 IF g_debug
604 THEN
605 hr_utility.set_location('range_person_on',40);
606 END IF;
607
608 IF l_action_param_val = 'Y' AND l_report_param_val = 'Y' THEN
609 l_return := TRUE;
610
611 IF g_debug
612 THEN
613 hr_utility.trace('Range Person = True');
614 END IF;
615 ELSE
616 l_return := FALSE;
617 END IF;
618 --
619 RETURN l_return;
620 --
621 END range_person_on;
622
623 --------------------------------------------------------------------+
624 -- This procedure further restricts the assignment_id's
625 -- returned by range_code
626 --------------------------------------------------------------------+
627
628 -- this procedure filters the assignments selected by range_code procedure
629 -- it then calls hr_nonrun.insact to create an assignment id
630 -- the cursor to select assignment action selects three types of employees
631 -- all current assignments eligible for archival ,
632 -- terminated employees eligible for archival
633 -- and all those assignment ids who have received a Fringe benifit
634 -- component of more than $1000 for that FBT year
635 -- and have been terminated during the FBT year
636
637 procedure assignment_action_code
638 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
639 p_start_person_id in per_all_people_f.person_id%type,
640 p_end_person_id in per_all_people_f.person_id%type,
641 p_chunk in number) is
642
643
644 v_next_action_id pay_assignment_actions.assignment_action_id%type;
645
646 v_lst_year_start date ;
647 v_fbt_year_start date ;
648 v_lst_fbt_year_start date ; --Bug#3661230
649 v_fbt_year_end date ;
650 v_fin_year_start date ;
651 v_fin_year_end date ;
652 v_assignment_id varchar2(50);
653 v_registered_employer varchar2(50);
654 v_financial_year varchar2(50);
655 v_payroll_id varchar2(50);
656 v_employee_type varchar2(1);
657 v_asg_id number;
658 v_reg_emp number;
659 l_lst_yr_term varchar(10); --Bug#3661230
660
661 ----------------------------------------------+
662 -- cursor to get the archive parameters
663 ----------------------------------------------+
664 cursor get_params(c_payroll_action_id per_all_assignments_f.assignment_id%type)
665 is
666 select to_date('01-07-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') Financial_year_start
667 ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY') Financial_year_end
668 ,to_date('01-04-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') FBT_year_start
669 ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') FBT_year_end
670 ,decode(pay_core_utils.get_parameter('EMPLOYEE_TYPE',legislative_parameters),'C','Y','T','N','B','%') Employee_type
671 ,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) Registered_Employer
672 ,pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters) Financial_year
673 ,decode(pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters),null,'%',
674 pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters)) Assignment_id
675 ,decode(pay_core_utils.get_parameter('PAYROLL',legislative_parameters),null,'%',pay_core_utils.get_parameter('PAYROLL',legislative_parameters)) payroll_id
676 ,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_yr_term /*3661230*/
677 ,pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) Business_group_id
678 from pay_payroll_actions
679 where payroll_action_id = c_payroll_Action_id;
680
681 ----------------------------------------------+
682 -- cursor to restrict assignment ids
683 ----------------------------------------------+
684 /* 4926521 Modified adn put group by in 2 sub queries for performacne */
685 /* 5099419 Removed fix for bug 4926521 */
686 Cursor process_assignments(c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
687 c_start_person_id in per_all_people_f.person_id%type,
688 c_end_person_id in per_all_people_f.person_id%type)
689 is
690 select /*+ INDEX(p per_people_f_pk)
691 INDEX(a per_assignments_f_fk1)
692 INDEX(a per_assignments_f_N12)
693 INDEX(pa pay_payroll_actions_pk)
694 INDEX(pps per_periods_of_service_n3)
695 */ distinct a.assignment_id
696 from per_people_f p /*Bug3043049*/
697 ,per_assignments_f a /*Bug3043049*/
698 ,pay_payroll_actions pa
699 ,per_periods_of_service pps
700 where pa.payroll_action_id = c_payroll_action_id
701 and p.person_id between c_start_person_id and c_end_person_id
702 and p.person_id = a.person_id
703 and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (v_fin_year_end)),1,'Y','N')) LIKE v_employee_type --Bug#3744930
704 and pps.period_of_service_id = a.period_of_service_id
705 and a.business_group_id = pa.business_group_id
706 and to_char(a.assignment_id) like v_assignment_id
707 and pps.person_id = p.person_id
708 and nvl(pps.actual_termination_date, v_lst_year_start) >= v_lst_year_start -- Bug3661230, Bug3048724 ,Bug 3263659
709 and v_fin_year_end between p.effective_start_date and p.effective_end_date
710 -- and least(nvl(pps.actual_termination_date,v_fin_year_end),v_fin_year_end) between a.effective_start_date and a.effective_end_date -- Bug 3815301
711 and a.effective_end_date = (select max(effective_end_date) /* 4377367 */
712 From per_assignments_f iipaf
713 WHERE iipaf.assignment_id = a.assignment_id
714 and iipaf.effective_end_date >= v_fbt_year_start
715 and iipaf.effective_start_date <= v_fin_year_end
716 AND iipaf.payroll_id IS NOT NULL) /*Bug# 4653934*/
717 and a.payroll_id like v_payroll_id -- Bug 3815301
718 and exists
719 (select /*+ INDEX(rpac PAY_ASSIGNMENT_ACTIONS_N51)
720 INDEX(rppa PAY_PAYROLL_ACTIONS_N51*/ ''
721 from
722 pay_payroll_actions rppa
723 ,pay_assignment_actions rpac /*Bug3048962 */
724 ,per_assignments_f paaf /*Bug 3815301 */
725 where ( rppa.effective_date between v_fin_year_start and v_fin_year_end
726 or ( pps.actual_termination_date between v_lst_fbt_year_start and v_fbt_year_end /*Bug3263659 */ --Bug#3661230
727 and rppa.effective_date between v_fbt_year_start and v_fbt_year_end
728 and pay_balance_pkg.get_value(g_fbt_defined_balance_id,rpac.assignment_action_id
729 + decode(rppa.payroll_id, 0, 0, 0),v_reg_emp,null,null,null,null) > to_number(g_fbt_threshold)) /* Bug 5708255 */ --2610141
730 )
731 and rppa.action_type in ('R','Q','B','I')
732 and rpac.tax_unit_id = v_reg_emp
733 and rppa.payroll_action_id = rpac.payroll_action_id
734 and rpac.action_status='C'
735 and rpac.assignment_id = paaf.assignment_id
736 and rppa.payroll_id = paaf.payroll_id /*Bug 3815301 */
737 and paaf.assignment_id = a.assignment_id
738 and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date /*Bug 3815301 */
739 )
740 and not exists
741 (select distinct paat.assignment_id
742 from pay_action_interlocks pail,
743 pay_assignment_actions paat,
744 pay_payroll_actions paas
745 where paat.assignment_id = a.assignment_id
746 and paas.action_type ='X'
747 and paas.action_status ='C'
748 and paas.report_type ='AU_PAYMENT_SUMMARY_REPORT'
749 and pail.locking_action_id = paat.assignment_action_id
750 and paat.payroll_action_id = paas.payroll_action_id
751 and pay_core_utils.get_parameter('FINANCIAL_YEAR',paas.legislative_parameters) = v_financial_year
752 and pay_core_utils.get_parameter('REGISTERED_EMPLOYER',paas.legislative_parameters) = v_reg_emp
753 )
754 and not exists ( select aei_information1
755 from per_assignment_extra_info,
756 hr_lookups
757 where assignment_id = a.assignment_id
758 and aei_information1 is not null
759 and aei_information1 = lookup_code
760 and nvl(aei_information2,v_reg_emp) = decode(aei_information2,'-999',aei_information2,v_reg_emp) -- 2610141 and 4000955
761 and lookup_type ='AU_PS_FINANCIAL_YEAR'
762 and meaning = v_financial_year
763 );
764
765 /*Bug 4000955 Modified subquery of process_assignments not to archive employees for
766 any legal employer if Manual PS is issued for 'ALL' legal employers or without any legal employer
767 If the Manual PS is issued for 'ALL' the legal employers the aei_information2 would be -999 done
768 in the view HR_AU_LEG_EMP_AEI_V */
769
770 /* Cursor added for bug3019374 -- Processed when a single assignment is entered*/
771
772
773 Cursor process_assignments_only(c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
774 c_start_person_id in per_all_people_f.person_id%type,
775 c_end_person_id in per_all_people_f.person_id%type)
776 is
777 select distinct a.assignment_id
778 from per_people_f p /*Bug3043049*/
779 ,per_assignments_f a /*Bug3043049*/
780 ,pay_payroll_actions pa
781 ,per_periods_of_service pps
782 where pa.payroll_action_id = c_payroll_action_id
783 and p.person_id between c_start_person_id and c_end_person_id
784 and p.person_id = a.person_id
785 and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (v_fin_year_end)),1,'Y','N')) LIKE v_employee_type ----Bug#3744930
786 and pps.period_of_service_id = a.period_of_service_id
787 and a.business_group_id = pa.business_group_id
788 and a.assignment_id = v_assignment_id
789 and pps.person_id = p.person_id
790 and nvl(pps.actual_termination_date, v_lst_year_start) >= v_lst_year_start -- Bug3661230 , Bug3048724, Bug 3263659
791 and v_fin_year_end between p.effective_start_date and p.effective_end_date
792 -- and least(nvl(pps.actual_termination_date,v_fin_year_end),v_fin_year_end) between a.effective_start_date and a.effective_end_date -- Bug 3815301
793 and a.effective_end_date = (select max(effective_end_date) /* 4377367 */
794 From per_assignments_f iipaf
795 WHERE iipaf.assignment_id = a.assignment_id
796 and iipaf.effective_end_date >= v_fbt_year_start
797 and iipaf.effective_start_date <= v_fin_year_end
798 AND iipaf.payroll_id IS NOT NULL) /*Bug# 4653934*/
799 and a.payroll_id like v_payroll_id -- Bug 3815301
800 and exists
801 (select /*+ INDEX(rpac PAY_ASSIGNMENT_ACTIONS_N51)
802 INDEX(rppa PAY_PAYROLL_ACTIONS_N51 */ ''
803 from pay_payroll_actions rppa
804 ,pay_assignment_actions rpac/*Bug3048962 */
805 ,per_assignments_f paaf /*Bug 3815301 */
806 where ( rppa.effective_date between v_fin_year_start and v_fin_year_end
807 or ( pps.actual_termination_date between v_lst_fbt_year_start and v_fbt_year_end /*Bug3263659 */ --Bug#3661230
808 and rppa.effective_date between v_fbt_year_start and v_fbt_year_end
809 and pay_balance_pkg.get_value(g_fbt_defined_balance_id,rpac.assignment_action_id
810 + decode(rppa.payroll_id, 0, 0, 0),v_reg_emp,null,null,null,null) > to_number(g_fbt_threshold)) /* Bug 5708255 */ --2610141
811 )
812 and rppa.action_type in ('R','Q','B','I')
813 and rpac.tax_unit_id = v_reg_emp
814 and rppa.payroll_action_id = rpac.payroll_action_id
815 and rpac.action_status='C'
816 and rpac.assignment_id = paaf.assignment_id
817 and rppa.payroll_id = paaf.payroll_id /*Bug 3815301 */
818 and paaf.assignment_id = v_assignment_id
819 and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date /*Bug 3815301 */
820 )
821 and not exists
822 (select distinct paat.assignment_id
823 from pay_action_interlocks pail,
824 pay_assignment_actions paat,
825 pay_payroll_actions paas
826 where paat.assignment_id = a.assignment_id
827 and paas.action_type ='X'
828 and paas.action_status ='C'
829 and paas.report_type ='AU_PAYMENT_SUMMARY_REPORT'
830 and pail.locking_action_id = paat.assignment_action_id
831 and paat.payroll_action_id = paas.payroll_action_id
832 and pay_core_utils.get_parameter('FINANCIAL_YEAR',paas.legislative_parameters) = v_financial_year
833 and pay_core_utils.get_parameter('REGISTERED_EMPLOYER',paas.legislative_parameters) = v_reg_emp) --2610141
834 and not exists ( select aei_information1
835 from per_assignment_extra_info,
836 hr_lookups
837 where assignment_id = a.assignment_id
838 and aei_information1 is not null
839 and aei_information1 = lookup_code
840 and nvl(aei_information2,v_reg_emp) = decode(aei_information2,'-999',aei_information2,v_reg_emp) -- 2610141 and 4000955
841 and lookup_type ='AU_PS_FINANCIAL_YEAR'
842 and meaning = v_financial_year);
843
844
845 /*Bug 4000955 Modified subquery of process_assignments_only not to archive employees for
846 any legal employer if Manual PS is issued for 'ALL' legal employers or without any legal employer
847 If the Manual PS is issued for 'ALL' the legal employers the aei_information2 would be -999 done
848 in the view HR_AU_LEG_EMP_AEI_V
849 */
850
851
852 /*
853 Bug 7138494 - Added Cursor for Range Person
854 - Uses person_id in pay_population_ranges
855 --------------------------------------------------------------------+
856 -- Cursor : range_process_assignments
857 -- Description : Fetches assignments For Recconciling Payment Summary
858 -- Returns DISTINCT assignment_id
859 -- Used when RANGE_PERSON_ID feature is enabled
860 --------------------------------------------------------------------+
861 */
862
863 CURSOR range_process_assignments(c_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE,
864 c_chunk IN NUMBER)
865 IS
866 SELECT /*+ INDEX(pap per_people_f_pk)
867 INDEX(rppa pay_payroll_actions_pk)
868 INDEX(ppr PAY_POPULATION_RANGES_N4)
869 INDEX(paa per_assignments_f_N12)
870 INDEX(pps per_periods_of_service_PK)
871 */ a.assignment_id
872 FROM per_people_f p /*Bug3043049*/
873 ,per_assignments_f a /*Bug3043049*/
874 ,pay_payroll_actions pa
875 ,per_periods_of_service pps
876 ,pay_population_ranges ppr
877 WHERE pa.payroll_action_id = c_payroll_action_id
878 AND pa.payroll_action_id = ppr.payroll_action_id
879 AND ppr.chunk_number = c_chunk
880 AND p.person_id = ppr.person_id
881 AND p.person_id = a.person_id
882 AND decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (v_fin_year_end)),1,'Y','N')) LIKE v_employee_type --Bug#3744930
883 AND pps.period_of_service_id = a.period_of_service_id
884 AND a.business_group_id = pa.business_group_id
885 AND to_char(a.assignment_id) LIKE v_assignment_id
886 AND pps.person_id = p.person_id
887 AND nvl(pps.actual_termination_date, v_lst_year_start) >= v_lst_year_start -- Bug3661230, Bug3048724 ,Bug 3263659
888 AND v_fin_year_end BETWEEN p.effective_start_date AND p.effective_end_date
889 AND a.effective_end_date = (SELECT MAX(effective_end_date) /* 4377367 */
890 FROM per_assignments_f iipaf
891 WHERE iipaf.assignment_id = a.assignment_id
892 AND iipaf.effective_end_date >= v_fbt_year_start
893 AND iipaf.effective_start_date <= v_fin_year_end
894 AND iipaf.payroll_id IS NOT NULL) /*Bug# 4653934*/
895 AND a.payroll_id LIKE v_payroll_id -- Bug 3815301
896 AND EXISTS
897 (SELECT /*+ INDEX(rpac PAY_ASSIGNMENT_ACTIONS_N51)
898 INDEX(rppa PAY_PAYROLL_ACTIONS_N51*/ ''
899 FROM
900 pay_payroll_actions rppa
901 ,pay_assignment_actions rpac /*Bug3048962 */
902 ,per_assignments_f paaf /*Bug 3815301 */
903 WHERE ( rppa.effective_date BETWEEN v_fin_year_start AND v_fin_year_end
904 OR ( pps.actual_termination_date BETWEEN v_lst_fbt_year_start AND v_fbt_year_end /*Bug3263659 */ --Bug#3661230
905 AND rppa.effective_date BETWEEN v_fbt_year_start AND v_fbt_year_end
906 AND pay_balance_pkg.get_value(g_fbt_defined_balance_id,rpac.assignment_action_id
907 + decode(rppa.payroll_id, 0, 0, 0),v_reg_emp,null,null,null,null) > to_number(g_fbt_threshold)
908 )
909 )
910 AND rppa.action_type in ('R','Q','B','I')
911 AND rpac.tax_unit_id = v_reg_emp
912 AND rppa.payroll_action_id = rpac.payroll_action_id
913 AND rpac.action_status ='C'
914 AND rpac.assignment_id = paaf.assignment_id
915 AND rppa.payroll_id = paaf.payroll_id /*Bug 3815301 */
916 AND paaf.assignment_id = a.assignment_id
917 AND rppa.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date /*Bug 3815301 */
918 )
919 AND NOT EXISTS
920 (SELECT paat.assignment_id
921 FROM pay_action_interlocks pail,
922 pay_assignment_actions paat,
923 pay_payroll_actions paas
924 WHERE paat.assignment_id = a.assignment_id
925 AND paas.action_type ='X'
926 AND paas.action_status ='C'
927 AND paas.report_type ='AU_PAYMENT_SUMMARY_REPORT'
928 AND pail.locking_action_id = paat.assignment_action_id
929 AND paat.payroll_action_id = paas.payroll_action_id
930 AND pay_core_utils.get_parameter('FINANCIAL_YEAR',paas.legislative_parameters) = v_financial_year
931 AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER',paas.legislative_parameters) = v_reg_emp
932 )
933 AND NOT EXISTS
934 ( SELECT aei_information1
935 FROM per_assignment_extra_info,
936 hr_lookups
937 WHERE assignment_id = a.assignment_id
938 AND aei_information1 IS NOT NULL
939 AND aei_information1 = lookup_code
940 AND nvl(aei_information2,v_reg_emp) = decode(aei_information2,'-999',aei_information2,v_reg_emp) -- 2610141 and 4000955
941 AND lookup_type ='AU_PS_FINANCIAL_YEAR'
942 AND meaning = v_financial_year
943 );
944
945
946 cursor next_action_id is
947 select pay_assignment_actions_s.nextval
948 from dual;
949
950 /* Bug 5708255 */
951 -------------------------------------------
952 -- Added cursor to get value of global FBT_THRESHOLD
953 --------------------------------------------
954 CURSOR c_get_fbt_global(c_year_end DATE)
955 IS
956 SELECT global_value
957 FROM ff_globals_f
958 WHERE global_name = 'FBT_THRESHOLD'
959 AND legislation_code = 'AU'
960 AND c_year_end BETWEEN effective_start_date
961 AND effective_end_date ;
962
963 --amit
964
965 Cursor c_fbt_balance is
966 select pdb.defined_balance_id
967 from pay_balance_types pbt,
968 pay_defined_balances pdb,
969 pay_balance_dimensions pbd
970 where pbt.balance_name ='Fringe Benefits'
971 and pbt.balance_type_id = pdb.balance_type_id
972 and pdb.balance_dimension_id = pbd.balance_dimension_id /* Bug 2501105 */
973 and pbd.legislation_code ='AU'
974 and pbd.dimension_name ='_ASG_LE_FBT_YTD' --2610141
975 and pbd.legislation_code = pbt.legislation_code
976 and pbd.legislation_code = pdb.legislation_code;
977
978
979
980 begin
981 g_debug := hr_utility.debug_enabled;
982
983
984 IF g_debug THEN
985 hr_utility.set_location('Start of assignment_action_code',1);
986 END IF;
987 -------------------------------------------------------------
988 -- get the paramters for archival process
989 -------------------------------------------------------------
990 open get_params(p_payroll_action_id);
991 fetch get_params
992 into v_fin_year_start
993 ,v_fin_year_end
994 ,v_fbt_year_start
995 ,v_fbt_year_end
996 ,v_employee_type
997 ,v_registered_employer
998 ,v_financial_year
999 ,v_assignment_id
1000 ,v_payroll_id
1001 ,l_lst_yr_term /*Bug3661230*/
1002 ,g_business_group_id ;
1003 close get_params;
1004
1005 /* The following check is introduced for Bug: 3701869. Parameter Last Year Termination has been introduced through
1006 Bug: 3263659. If the customer does not have this new functionality enabled, the default should work. Hence the
1007 check for the parameter if null has been introduced to make sure the correct fbt dates are set. */
1008
1009 IF (l_lst_yr_term IS NULL) THEN
1010 l_lst_yr_term := 'Y';
1011 END IF;
1012
1013 /**** Bug#3661230 **********/
1014 IF l_lst_yr_term = 'Y' THEN
1015 -- v_lst_year_start := ADD_MONTHS(v_fbt_year_start,-12); -- 3263659
1016 v_lst_year_start := ADD_MONTHS(v_fin_year_start,-12); -- 5395393
1017 v_lst_fbt_year_start := v_fbt_year_start;
1018 ELSE
1019 v_lst_year_start := TO_DATE('01-01-1900','DD-MM-YYYY');
1020 v_lst_fbt_year_start := TO_DATE('01-01-1900','DD-MM-YYYY');
1021 END IF;
1022 /* end of Bug#3661230 **/
1023
1024 ---amit
1025 If g_fbt_defined_balance_id is null OR g_fbt_defined_balance_id =0 Then
1026 Open c_fbt_balance;
1027 Fetch c_fbt_balance into g_fbt_defined_balance_id;
1028 Close c_fbt_balance;
1029 End if;
1030 ----amit
1031
1032 /* Bug 5708255 */
1033 open c_get_fbt_global (add_months(v_fin_year_end,-3)); /* Add_months included for bug 5333143 */
1034 fetch c_get_fbt_global into g_fbt_threshold;
1035 close c_get_fbt_global;
1036
1037 hr_utility.set_location('Anitha g_fbt_threshold Value in ass_action_code '||g_fbt_threshold,1000);
1038
1039 v_reg_emp := to_number(v_registered_employer); /*added-sun*/
1040
1041 if (v_assignment_id <> '%' and v_payroll_id <> '%') then /*Added for bug 3019374*/
1042
1043 for process_rec in process_assignments_only(p_payroll_action_id,
1044 p_start_person_id,
1045 p_end_person_id)
1046 Loop /* Bug: 2881272 - Removed the temporary check which was included during the fix 2822446 */
1047 open next_action_id;
1048 fetch next_action_id into v_next_action_id;
1049 close next_action_id;
1050 IF g_debug THEN
1051 hr_utility.set_location('Calling hr_nonrun_asact.insact for assignment id :'||process_rec.assignment_id,2);
1052 END if;
1053
1054 hr_nonrun_asact.insact(v_next_action_id,
1055 process_rec.assignment_id,
1056 p_payroll_action_id,
1057 p_chunk,
1058 null);
1059 IF g_debug THEN
1060 hr_utility.set_location('After calling hr_nonrun_asact.insact',3);
1061 END if;
1062 end loop;
1063
1064 else
1065 /* Multiple Assignments */
1066
1067 /* Bug 7138494 - Added Changes for Range Person
1068 - Call Cursor using pay_population_ranges if Range Person Enabled
1069 Else call Old Cursor
1070 */
1071
1072 IF range_person_on
1073 THEN
1074
1075 FOR csr_rec IN range_process_assignments(p_payroll_action_id
1076 ,p_chunk)
1077 LOOP
1078 OPEN next_action_id;
1079 FETCH next_action_id INTO v_next_action_id;
1080 CLOSE next_action_id;
1081 IF g_debug THEN
1082 hr_utility.set_location('Calling hr_nonrun_asact.insact for assignment id :'||csr_rec.assignment_id,2);
1083 END if;
1084
1085 hr_nonrun_asact.insact(v_next_action_id,
1086 csr_rec.assignment_id,
1087 p_payroll_action_id,
1088 p_chunk,
1089 null);
1090 IF g_debug THEN
1091 hr_utility.set_location('After calling hr_nonrun_asact.insact',3);
1092 END IF;
1093 END LOOP;
1094
1095 ELSE /* Retain Old Logic - No Range Person */
1096
1097 for process_rec in process_assignments (p_payroll_action_id,
1098 p_start_person_id,
1099 p_end_person_id)
1100 Loop
1101 open next_action_id;
1102 fetch next_action_id into v_next_action_id;
1103 close next_action_id;
1104 IF g_debug THEN
1105 hr_utility.set_location('Calling hr_nonrun_asact.insact for assignment id :'||process_rec.assignment_id,2);
1106 END if;
1107 hr_nonrun_asact.insact(v_next_action_id,
1108 process_rec.assignment_id,
1109 p_payroll_action_id,
1110 p_chunk,
1111 null);
1112 IF g_debug THEN
1113 hr_utility.set_location('After calling hr_nonrun_asact.insact',3);
1114 END if;
1115 end loop;
1116
1117 END IF; /* End Range Person check */
1118
1119 end if;
1120
1121 IF g_debug THEN
1122 hr_utility.set_location('End of assignment_action_code',4);
1123 END if;
1124
1125
1126 exception
1127 when others then
1128 IF g_debug THEN
1129 hr_utility.set_location('error raised in assignment_action_code procedure ',5);
1130 END if;
1131 raise;
1132 end assignment_action_code;
1133
1134
1135 ---------------------------------
1136 --Functions
1137 function get_max_effective_person_date (p_person_id per_all_people_f .person_id%type)
1138 return date
1139 is
1140 l_effective_date date ;
1141 cursor c_effective_date
1142 is
1143 select max(effective_start_date)
1144 from per_all_people_f p
1145 where person_id =p_person_id ;
1146 begin
1147 open c_effective_date;
1148 fetch c_effective_date into l_effective_date ;
1149 close c_effective_date ;
1150 return l_effective_date ;
1151 end;
1152
1153 function get_max_effective_asg_date(p_asg_id per_all_assignments_f .assignment_id%type)
1154 return date
1155 is
1156 l_effective_date date ;
1157 cursor c_effective_date
1158 is
1159 select max(effective_start_date)
1160 from per_all_assignments_f p
1161 where assignment_id =p_asg_id ;
1162 begin
1163 open c_effective_date;
1164 fetch c_effective_date into l_effective_date ;
1165 close c_effective_date ;
1166 return l_effective_date ;
1167 end;
1168
1169
1170
1171 ------------------------------------------------------------------------+
1172 -- Creates the Extract Archive Database Item.
1173 -- Called from
1174 -- 1. Archive_supplier_details
1175 -- 2. Archive_employer_details
1176 -- 3. Archive_employee_details
1177 -- 4. Archive_Balance_Details
1178 ------------------------------------------------------------------------+
1179
1180
1181 procedure create_extract_archive_details
1182 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
1183 p_user_entity_name in ff_user_entities.user_entity_name%type,
1184 p_value in out nocopy ff_archive_items.value%type) is
1185
1186
1187
1188 cursor get_user_entity_id(c_user_entity_name in varchar2)
1189 is
1190 select fue.user_entity_id,
1191 dbi.data_type
1192 from ff_user_entities fue,
1193 ff_database_items dbi
1194 where user_entity_name =c_user_entity_name
1195 and fue.user_entity_id =dbi.user_entity_id;
1196
1197 v_full_name per_all_people_f.full_name%type;
1198 v_user_entity_id ff_user_entities.user_entity_id%type;
1199 v_archive_item_id ff_archive_items.archive_item_id%type;
1200 v_data_type ff_database_items.data_type%type;
1201 v_object_version_number ff_archive_items.object_version_number%type;
1202 v_some_warning boolean;
1203
1204 i_index NUMBER; /* Bug 6470581 */
1205
1206 begin
1207 g_debug := hr_utility.debug_enabled;
1208
1209 IF g_debug THEN
1210 hr_utility.set_location('Start of create_extract_archive_details',15);
1211 hr_utility.set_location('Assignment action id is :' || p_assignment_action_id,16);
1212 hr_utility.set_location('Database item name is :' || p_user_entity_name,16);
1213 hr_utility.set_location('Value is :' || p_value,17);
1214 END if;
1215
1216 /* Bug 6470581 - Added Changes for Amended Payment Summary Type
1217 i. If g_payment_summary_type = 'O' (Original) - Archive values in ff_archive_items
1218 ii. If g_payment_summary_type = 'A' (Amended) - Add values to PL/SQL Table p_all_dbi_tab
1219 */
1220
1221 IF g_payment_summary_type = 'O'
1222 THEN
1223
1224 open get_user_entity_id (p_user_entity_name);
1225 hr_utility.trace('the value of the user entity is '||p_user_entity_name);
1226 fetch get_user_entity_id into v_user_Entity_id,
1227 v_data_type;
1228
1229 -----------------------------------------------------------------------------------------------+
1230 -- if the archive item datatype is date then convert
1231 -- it into canonical format before archiving
1232 -- this is required because before inserting into ff_archive_items
1233 -- ff_archive_api validates the value
1234 -----------------------------------------------------------------------------------------------+
1235
1236 if (v_data_type = 'D') then
1237 p_value:= to_char(to_date(p_value,'DDMMYYYY'),fnd_date.canonical_mask);
1238 end if;
1239
1240
1241 if get_user_entity_id%found then
1242
1243 close get_user_entity_id;
1244 ff_archive_api.create_archive_item
1245 (p_validate => false -- boolean in default
1246 ,p_archive_item_id => v_archive_item_id -- number out
1247 ,p_user_entity_id => v_user_entity_id -- number in
1248 ,p_archive_value => p_value -- varchar2 in
1249 ,p_archive_type => 'AAP' -- varchar2 in default
1250 ,p_action_id => p_assignment_action_id -- number in
1251 ,p_legislation_code => 'AU' -- varchar2 in
1252 ,p_object_version_number => v_object_version_number -- number out
1253 ,p_context_name1 => 'ASSIGNMENT_ACTION_ID' -- varchar2 in default
1254 ,p_context1 => p_assignment_action_id -- varchar2 in default
1255 ,p_some_warning => v_some_warning); -- boolean out
1256 else
1257
1258 close get_user_entity_id;
1259 IF g_debug THEN
1260 hr_utility.set_location('User entity not found :'||p_user_entity_name,20);
1261 END if;
1262 end if;
1263
1264 ELSE
1265
1266 OPEN get_user_entity_id (p_user_entity_name);
1267 FETCH get_user_entity_id INTO v_user_Entity_id,
1268 v_data_type;
1269 IF (v_data_type = 'D') THEN
1270 p_value:= to_char(to_date(p_value,'DDMMYYYY'),fnd_date.canonical_mask);
1271 END IF;
1272
1273 IF get_user_entity_id%FOUND
1274 THEN
1275 CLOSE get_user_entity_id;
1276 IF g_debug
1277 THEN
1278 hr_utility.set_location('Amended Payment Summary - Update the archive PL/SQL table',2000);
1279 hr_utility.set_location('p_user_entity_name '||p_user_entity_name,2000);
1280 END IF;
1281 i_index := NVL(p_all_dbi_tab.LAST,-1) + 1;
1282 p_all_dbi_tab(i_index).db_item_name := p_user_entity_name;
1283 p_all_dbi_tab(i_index).db_item_value := p_value;
1284
1285 IF g_debug
1286 THEN
1287 hr_utility.set_location('Updated Index '||i_index,2010);
1288 END IF;
1289 ELSE
1290 CLOSE get_user_entity_id;
1291 IF g_debug THEN
1292 hr_utility.set_location('User entity not found :'||p_user_entity_name,2020);
1293 END if;
1294 END IF;
1295 END IF;
1296
1297 IF g_debug THEN
1298 hr_utility.set_location('End of create_extract_archive_detail',18);
1299 END if;
1300
1301 exception
1302 when others then
1303 if get_user_entity_id%isopen then
1304 close get_user_entity_id;
1305 IF g_debug THEN
1306 hr_utility.set_location('closing..',117);
1307 END if;
1308 end if;
1309 IF g_debug THEN
1310 hr_utility.set_location('Error in create_extract_archive_details',20);
1311 END if;
1312 raise;
1313 end create_extract_archive_details;
1314
1315 ----------------------------------------------------------------------+
1316 -- procedure to archive balance details
1317 -- Passed Balance name Bug #2454595
1318 ----------------------------------------------------------------------+
1319
1320 procedure archive_balance_details
1321 (p_assignment_action_id in pay_assignment_actions.ASSIGNMENT_ACTION_ID%TYPE
1322 ,p_max_assignment_action_id in pay_assignment_actions.ASSIGNMENT_ACTION_ID%TYPE --2610141
1323 ,p_registered_employer in NUMBER
1324 ,p_database_item_name in ff_database_items.user_name%TYPE
1325 ,p_balance_name in pay_balance_types.balance_name%TYPE
1326 ,p_legislation_code in pay_defined_balances.legislation_code%TYPE
1327 ,p_year_start in DATE
1328 ,p_year_end in DATE
1329 ,p_assignment_id in pay_assignment_actions.ASSIGNMENT_ID%type
1330 ,p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
1331 ,p_bal_value OUT NOCOPY varchar2) IS -- Bug 3098353
1332
1333 v_bal_value varchar2(20);
1334 v_earnings_ytd varchar2(20);
1335 v_lump_sum_E_ytd varchar2(20);
1336 v_lump_sum_E_ptd varchar2(20);
1337 v_effective_date DATE;
1338
1339 -------------------------------------------------------------------+
1340 -- Cursor to calculate balances for a given database item
1341 -- To calculate balances we require defined balance id and
1342 -- that can be retrieved only from pay_defined_balances
1343 -- first cursor is to retrive Fringe Benefits Balance
1344 -- second cursor is to retrive other Balance values
1345 -------------------------------------------------------------------+
1346 -- cursors modified for bug #1768813
1347 -------------------------------------------------------------------+
1348
1349 -------------------------------------------------------------------+
1350 -- cursors c_archive_fbt_info and c_archive_info
1351 -- modified to use balance name instead of archive item name
1352 -- Bug #2454595
1353 -------------------------------------------------------------------+
1354 cursor c_archive_fbt_info(c_balance_name pay_balance_types.balance_name%type,
1355 c_year_end DATE,
1356 c_assignment_id pay_assignment_actions.assignment_id%type)
1357 is
1358 select pay_balance_pkg.get_value(pdb.defined_balance_id,
1359 p_max_assignment_action_id,
1360 p_registered_employer,
1361 null,null,null,null) --2610141
1362 from pay_balance_types pbt,
1363 pay_defined_balances pdb,
1364 pay_balance_dimensions pbd
1365 where pbt.balance_name = c_balance_name
1366 and pbt.legislation_code = 'AU'
1367 and pbt.balance_type_id = pdb.balance_type_id
1368 and pbd.balance_dimension_id = pdb.balance_dimension_id
1369 and pbd.dimension_name = '_ASG_LE_FBT_YTD';
1370
1371
1372
1373 ------------------------------------------------------------
1374 -- Cursor to calculate tax deduction balance ( Bug 1903647)
1375 ------------------------------------------------------------
1376
1377
1378 CURSOR c_get_global(c_name VARCHAR2
1379 ,c_year_end DATE)
1380 IS
1381 SELECT global_value
1382 ,data_type
1383 FROM ff_globals_f
1384 WHERE global_name = c_name
1385 AND legislation_code = 'AU'
1386 AND c_year_end BETWEEN effective_start_date
1387 AND effective_end_date ;
1388
1389 /* Bug: 3095919 Cursor to fetch Lump sum E payments PTD value */
1390 CURSOR c_single_lumpsum_E_payment(c_effective_date DATE,
1391 c_assignment_id pay_assignment_actions.assignment_id%type,
1392 c_assignment_action_id pay_assignment_actions.assignment_action_id%type) --2610141
1393 IS
1394 select sum(pay_balance_pkg.get_value(pdb.defined_balance_id,
1395 c_assignment_action_id,
1396 p_registered_employer,
1397 null,null,null,null)) --2610141
1398 FROM pay_balance_types pbt,
1399 pay_defined_balances pdb,
1400 pay_balance_dimensions pbd
1401 WHERE pbt.legislation_code = 'AU'
1402 AND pbt.balance_name = 'Lump Sum E Payments'
1403 AND pbt.balance_type_id = pdb.balance_type_id
1404 AND pbd.balance_dimension_id = pdb.balance_dimension_id
1405 AND pbd.dimension_name = '_ASG_LE_PTD';
1406
1407 /* Bug No: 3603495 - Performance Fix in c_get_pay_effective_date - Introduced per_assignments_f and its joins */
1408 /* Bug 4363057 - Cursor has been modified so that the Lump Sum E Payments given to previous legal employers
1409 can be taken into account while calculating payment summary gross.*/
1410
1411 CURSOR c_get_pay_effective_date(c_assignment_id pay_assignment_actions.assignment_id%type
1412 ,c_year_start in DATE
1413 ,c_year_end in DATE)
1414 IS
1415 select /*+ USE_NL(ptp) */ -- Bug 4925650
1416 max(paa.assignment_action_id) -- Bug: 3095919, Bug 2610141
1417 from per_assignments_f paf,
1418 pay_payroll_Actions ppa,
1419 pay_assignment_Actions paa,
1420 per_time_periods ptp
1421 where ppa.payroll_Action_id = paa.payroll_Action_id
1422 and paa.assignment_id = c_assignment_id
1423 and paf.assignment_id = paa.assignment_id
1424 and paa.tax_unit_id = p_registered_employer --2610141
1425 and action_type in ('Q','R','V')
1426 AND (paa.source_action_id IS NULL
1427 OR (paa.source_action_id IS NOT NULL AND ppa.run_type_id IS NULL)) /*Bug 4363057*/
1428 and ppa.effective_date between c_year_start and c_year_end /*bug 4063321*/
1429 /* Bug# 5377624 */
1430 -- AND ptp.time_period_id = ppa.time_period_id
1431 and ppa.payroll_id = ptp.payroll_id
1432 and ppa.payroll_id=paf.payroll_id /* Added for bug 5371102 , query 1 */
1433 and ppa.date_earned between ptp.start_date and ptp.end_date
1434 /* Bug# 5377624 */
1435 and ppa.date_earned between paf.effective_start_date and paf.effective_end_date
1436 GROUP BY ptp.time_period_id;
1437
1438
1439 r_global c_get_global%ROWTYPE;
1440
1441 sum_various Number;
1442 counter Number;
1443 l_reporting_amt Number;
1444 l_medicare_levy NUMBER ;
1445 l_fbt_rate NUMBER ;
1446 l_assignment_action_id number; --2610141
1447
1448
1449 e_bad_global exception ;
1450
1451 begin
1452 sum_various := 0;
1453 counter := 1;
1454
1455 IF g_debug THEN
1456 hr_utility.set_location('Start of create_extract_archive_balance',1);
1457 hr_utility.set_location('Start of p_assg_action_id '|| p_assignment_action_id,2);
1458 hr_utility.set_location('Start of p_database_item_name '|| p_database_item_name,3);
1459 END if;
1460
1461 ------------------------------------------------------------+
1462 -- get the balance value for the database item
1463 ------------------------------------------------------------+
1464
1465 if p_database_item_name = 'X_FRINGE_BENEFITS_ASG_YTD' then
1466 open c_archive_fbt_info (p_balance_name, -- Bug #2454595
1467 p_year_end,
1468 p_assignment_id);
1469
1470 fetch c_archive_fbt_info
1471 into v_bal_value; -- Bug #2454595
1472
1473 /* Bug 5708255 Changed from 1000 to g_fbt_threshold */
1474
1475 if c_archive_fbt_info%found AND v_bal_value > to_number(g_fbt_threshold) THEN --Bug: 3549553- To fetch only ASG_YTD level balances
1476 close c_archive_fbt_info;
1477 IF g_debug THEN
1478 hr_utility.set_location('Calling create_extract_archive_details for item :'|| p_database_item_name,4);
1479 END if;
1480
1481 ----------------------------------------------------
1482 -- get global values for MEDICARE_LEVY and FBT_RATE
1483 ---------------------------------------------------
1484
1485 open c_get_global ( 'FBT_RATE',add_months(p_year_end,-3)); /* Add_months included for bug 5333143 */
1486 fetch c_get_global
1487 into r_global;
1488
1489 l_fbt_rate := r_global.global_value ;
1490
1491 if c_get_global%notfound then
1492 raise e_bad_global;
1493 end if;
1494
1495 close c_get_global ;
1496
1497 open c_get_global ( 'MEDICARE_LEVY',add_months(p_year_end,-3)); /* Add_months included for bug 5333143 */
1498
1499 fetch c_get_global
1500 into r_global;
1501
1502 l_medicare_levy := r_global.global_value ;
1503
1504 if c_get_global%notfound then
1505 raise e_bad_global;
1506 end if;
1507
1508 close c_get_global ;
1509
1510 l_reporting_amt := v_bal_value/(1-(l_fbt_rate+l_medicare_levy));
1511 l_reporting_amt := round(l_reporting_amt,2);
1512
1513
1514 create_extract_archive_details(p_assignment_action_id
1515 ,p_database_item_name
1516 ,l_reporting_amt);
1517
1518 else
1519 IF g_debug THEN
1520 hr_utility.set_location('FBT Below Reportable limit for this balance:'||p_database_item_name,5);
1521 END if;
1522 v_bal_value := 0;
1523 close c_archive_fbt_info;
1524 end if;
1525
1526 elsif p_database_item_name = 'X_EARNINGS_TOTAL_ASG_YTD' then
1527
1528 ---------------------------------------
1529 -- archive earnings_total balance
1530 ---------------------------------------
1531 v_lump_sum_E_ytd :=0;
1532 v_lump_sum_E_ptd :=0;
1533 v_earnings_ytd := 0;
1534
1535 /* Remove as balances now retrieved using BBR - Bug 3172963
1536 open c_archive_total_earnings_info ( p_year_end,
1537 p_assignment_id);
1538
1539 fetch c_archive_total_earnings_info into v_earnings_ytd;
1540
1541 if c_archive_total_earnings_info%found then
1542 close c_archive_total_earnings_info;
1543 */
1544 /* Bug 3172963 */
1545 /* v_earnings_total := 'Leave Payments Marginal' + 'Earnings_Total' + 'Workplace Giving 4015082 */
1546 v_earnings_ytd := p_result_table(2).balance_value + p_result_table(16).balance_value + p_result_table(17).balance_value;
1547
1548
1549 if (v_earnings_ytd >= 0) then
1550
1551 /* Remove as balance now retrieved using BBR - Bug 3172963
1552 open c_archive_info('Lump Sum E Payments',p_year_end,p_assignment_id);
1553 fetch c_archive_info into v_lump_sum_E_ytd;
1554 close c_archive_info;
1555 */
1556 v_lump_sum_E_ytd := p_result_table(15).balance_value;
1557 v_bal_value := v_earnings_ytd + v_lump_sum_E_ytd; --Bug 3098353
1558
1559 if p_year_start >= to_date('01-07-2003','DD-MM-YYYY') then /* Bug: 3095919 */
1560 if v_lump_sum_E_ytd <> 0 then
1561 OPEN c_get_pay_effective_date(p_assignment_id
1562 ,p_year_start
1563 ,p_year_end) ;
1564 LOOP
1565 fetch c_get_pay_effective_date into l_assignment_action_id; --2610141
1566 EXIT WHEN c_get_pay_effective_date%NOTFOUND;
1567 open c_single_lumpsum_E_payment(v_effective_date,
1568 p_assignment_id,
1569 l_assignment_action_id); --2610141
1570 fetch c_single_lumpsum_E_payment into v_lump_sum_E_ptd;
1571
1572 /* Bug: 3095919 If single lump sum E payment is less than $400 then the amount is included in gross earnings.
1573 If single lump sum E payment is greater than $400 then it is included in Lump sum E payments.
1574 */
1575 if v_lump_sum_E_ptd < 400 then
1576 v_lump_sum_E_ytd := v_lump_sum_E_ytd - v_lump_sum_E_ptd;
1577 else
1578 v_earnings_ytd := v_earnings_ytd - v_lump_sum_E_ptd;
1579 end if;
1580 close c_single_lumpsum_E_payment;
1581 END LOOP;
1582 CLOSE c_get_pay_effective_date;
1583 end if;
1584 else /* prior to financial year 2003, deduct Lump sum E amount from total earnings */
1585 v_earnings_ytd := v_earnings_ytd - v_lump_sum_E_ytd;
1586 end if;
1587
1588 IF g_debug THEN
1589 hr_utility.set_location('Calling create_extract_archive_details for item :'|| p_database_item_name,4);
1590 END if;
1591
1592 create_extract_archive_details(p_assignment_action_id
1593 ,p_database_item_name
1594 ,v_earnings_ytd);
1595 create_extract_archive_details(p_assignment_action_id
1596 ,'X_LUMP_SUM_E_PAYMENTS_ASG_YTD'
1597 ,v_lump_sum_E_ytd);
1598 else
1599 IF g_debug THEN
1600 hr_utility.set_location('Balance value not found for this balance:'||p_database_item_name,5);
1601 END if;
1602 /* close c_archive_total_earnings_info; */ --Bug 3172963
1603 end if;
1604
1605 elsif p_database_item_name = 'X_TOTAL_TAX_DEDUCTIONS_ASG_YTD' then
1606
1607 ---------------------------------------
1608 -- archive Total Tax Deductions balance
1609 ---------------------------------------
1610
1611 /* Remove as balances now retrieved using BBR - Bug 3172963 */
1612 /* open c_archive_total_tax_info( p_year_end,
1613 p_assignment_id);
1614
1615 fetch c_archive_total_tax_info into v_bal_value;
1616
1617 if c_archive_total_tax_info%found then
1618 close c_archive_total_tax_info;
1619 */
1620
1621 -- v_bal_val := 'Total_Tax_Deductions' + 'Termination Deductions' - 'Lump Sum C Deductions'
1622 v_bal_value := p_result_table(10).balance_value + p_result_table(11).balance_value
1623 - p_result_table(7).balance_value;
1624
1625 IF g_debug THEN
1626 hr_utility.set_location('Calling create_extract_archive_details for item :'|| p_database_item_name,4);
1627 END if;
1628 create_extract_archive_details(p_assignment_action_id
1629 ,p_database_item_name
1630 ,v_bal_value);
1631
1632 else /* archive other balances */
1633
1634 /* Remove as balances now retrieved using BBR - Bug 3172963 */
1635 /* open c_archive_info (p_balance_name, -- Bug#2454595
1636 p_year_end,
1637 p_assignment_id);
1638
1639 fetch c_archive_info into v_bal_value; -- Bug #2454595
1640
1641 if c_archive_info%found then
1642 close c_archive_info;
1643 */
1644
1645 /*Bug 6192381 Added New Balances for Multiple ETP Enhancement*/
1646
1647 if (p_balance_name = 'CDEP') then
1648 v_bal_value := p_result_table(1).balance_value;
1649 elsif (p_balance_name = 'Lump Sum A Deductions') then
1650 v_bal_value := p_result_table(3).balance_value;
1651 elsif (p_balance_name = 'Lump Sum A Payments') then
1652 v_bal_value := p_result_table(4).balance_value;
1653 elsif (p_balance_name = 'Lump Sum B Deductions') then
1654 v_bal_value := p_result_table(5).balance_value;
1655 elsif (p_balance_name = 'Lump Sum B Payments') then
1656 v_bal_value := p_result_table(6).balance_value;
1657 elsif (p_balance_name = 'Lump Sum D Payments') then
1658 v_bal_value := p_result_table(9).balance_value;
1659 elsif (p_balance_name = 'Other Income') then
1660 v_bal_value := p_result_table(12).balance_value;
1661 elsif (p_balance_name = 'Union Fees') then
1662 v_bal_value := p_result_table(13).balance_value;
1663 elsif (p_balance_name = 'Invalidity Payments') then
1664 v_bal_value := p_result_table(14).balance_value;
1665 elsif (p_balance_name = 'Lump Sum C Payments') then
1666 v_bal_value := p_result_table(8).balance_value;
1667 elsif (p_balance_name = 'Lump Sum C Deductions') then
1668 v_bal_value := p_result_table(7).balance_value;
1669 elsif (p_balance_name = 'Workplace Giving Deductions') then /* 4015082 */
1670 v_bal_value := p_result_table(17).balance_value;
1671 elsif (p_balance_name = 'ETP Deductions Transitional Not Part of Prev Term') then /* Begin 6192381 */
1672 v_bal_value := p_result_table(18).balance_value;
1673 elsif (p_balance_name = 'ETP Deductions Transitional Part of Prev Term') then
1674 v_bal_value := p_result_table(19).balance_value;
1675 elsif (p_balance_name = 'ETP Deductions Life Benefit Not Part of Prev Term') then
1676 v_bal_value := p_result_table(20).balance_value;
1677 elsif (p_balance_name = 'ETP Deductions Life Benefit Part of Prev Term') then
1678 v_bal_value := p_result_table(21).balance_value;
1679 elsif (p_balance_name = 'Invalidity Payments Life Benefit Not Part of Prev Term') then
1680 v_bal_value := p_result_table(22).balance_value;
1681 elsif (p_balance_name = 'Invalidity Payments Life Benefit Part of Prev Term') then
1682 v_bal_value := p_result_table(23).balance_value;
1683 elsif (p_balance_name = 'Invalidity Payments Transitional Not Part of Prev Term') then
1684 v_bal_value := p_result_table(24).balance_value;
1685 elsif (p_balance_name = 'Invalidity Payments Transitional Part of Prev Term') then
1686 v_bal_value := p_result_table(25).balance_value;
1687 end if; /* End 6192381 */
1688
1689 IF g_debug THEN
1690 hr_utility.set_location('Calling create_extract_archive_details for item :'|| p_database_item_name,4);
1691 END if;
1692 create_extract_archive_details(p_assignment_action_id
1693 ,p_database_item_name
1694 ,v_bal_value);
1695
1696 /* else
1697 IF g_debug THEN
1698 hr_utility.set_location('Balance value not found for this balance:'||p_database_item_name,5);
1699 END if;
1700 close c_archive_info;
1701 end if;
1702 */
1703
1704 end if;
1705 p_bal_value := v_bal_value; --Bug 3098353
1706
1707 exception
1708 when e_bad_global then
1709 IF g_debug THEN
1710 hr_utility.set_location('archive_balance_details : Global value not found ',15);
1711 END if;
1712 close c_get_global;
1713 when zero_divide then
1714 IF g_debug THEN
1715 hr_utility.set_location('archive_balance_details : Division By Zero ',15);
1716 END if;
1717 when others then
1718 IF g_debug THEN
1719 hr_utility.set_location('Error in archive_balance_details procedure',15);
1720 END if;
1721 raise;
1722
1723 end archive_balance_details;
1724
1725 -----------------------------------------------------------------------------
1726 /*Function Introduced for Bug2855658*/
1727
1728 function adjust_retro_allowances(t_allowance_balance IN OUT NOCOPY tab_allownace_balance
1729 ,p_year_start in DATE
1730 ,p_year_end in DATE
1731 ,p_assignment_id in pay_assignment_actions.ASSIGNMENT_ID%type
1732 ,p_registered_employer in NUMBER --2610141
1733 )
1734 return number
1735 is
1736
1737 CURSOR Get_retro_Entry_ids(c_year_start DATE,
1738 c_year_end DATE,
1739 c_assignment_id pay_assignment_actions.assignment_id%type)
1740 IS
1741 SELECT /*+ ORDERED */ pee.element_entry_id element_entry_id,
1742 ppa.date_earned date_earned,
1743 pee.assignment_id assignment_id,
1744 pac.tax_unit_id /* Added for bug #5846278 */
1745 FROM per_all_assignments_f paa
1746 ,per_periods_of_service pps
1747 ,pay_assignment_actions pac
1748 ,pay_payroll_actions ppa
1749 ,pay_element_entries_f pee
1750 ,pay_run_results prr
1751 ,pay_element_types_f pet
1752 ,pay_balance_types pbt
1753 WHERE paa.assignment_id = c_assignment_id
1754 AND pet.element_information_category = 'AU_EARNINGS'
1755 AND pet.element_information1 = 'Y'
1756 AND pet.element_information2=pbt.balance_type_id
1757 AND pps.PERIOD_OF_SERVICE_ID = paa.PERIOD_OF_SERVICE_ID
1758 AND NVL(pps.actual_termination_date,c_year_end)
1759 BETWEEN paa.effective_start_date AND paa.effective_end_date
1760 AND ppa.date_earned BETWEEN pet.effective_start_date AND pet.effective_end_date
1761 AND pac.payroll_action_id = ppa.payroll_Action_id
1762 AND pac.assignment_id = paa.assignment_id
1763 AND pac.tax_unit_id = p_registered_employer --2610141
1764 AND ppa.effective_date BETWEEN c_year_start AND c_year_end /*bug 4063321*/
1765 AND pac.assignment_Action_id = prr.assignment_Action_id
1766 AND prr.element_type_id=pet.element_type_id
1767 AND pee.element_entry_id=prr.source_id
1768 AND pee.creator_type in ('EE','RR')
1769 AND pee.assignment_id = paa.assignment_id /*Added for bug3019374*/
1770 AND ppa.action_status='C'
1771 AND pac.action_status='C'
1772 AND ppa.date_earned between pee.effective_start_date and pee.effective_end_date;
1773
1774
1775
1776
1777 Cursor Get_Retro_allowances(c_element_entry_id pay_element_entries_f.element_entry_id%type
1778 )
1779 IS
1780 select distinct NVL(pbt.reporting_name,pbt.balance_name) balance_name /* Bug 5743196 Added nvl */
1781 , prv.result_value balance_value
1782 from
1783 pay_element_entries_f pee,
1784 pay_run_results prr,
1785 pay_run_result_values prv,
1786 pay_element_types_f pet,
1787 pay_balance_types pbt
1788 where
1789 pee.element_entry_id=c_element_entry_id
1790 and prr.source_id=pee.element_entry_id
1791 and prv.run_result_id=prr.run_result_id
1792 AND pet.element_information_category = 'AU_EARNINGS'
1793 AND pet.element_information1 = 'Y'
1794 AND pet.element_information2=pbt.balance_type_id
1795 AND prr.element_type_id=pet.element_type_id
1796 AND pee.element_entry_id=prr.source_id;
1797
1798 /* Added to check the legislation rule for bug #5846278 */
1799 CURSOR get_legislation_rule
1800 IS
1801 SELECT plr.rule_mode
1802 FROM pay_legislation_rules plr
1803 WHERE plr.legislation_code = 'AU'
1804 AND plr.rule_type ='ADVANCED_RETRO';
1805
1806 rec_retro_Allowances Get_retro_Allowances%ROWTYPE;
1807 TYPE
1808 r_ret_allowances IS RECORD(balance_name pay_balance_types.balance_name%TYPE,
1809 balance_value Number);
1810 TYPE
1811 tab_ret_allowances IS TABLE OF r_ret_allowances INDEX BY BINARY_INTEGER;
1812 t_ret_allowances tab_ret_allowances;
1813
1814 rec_ret_entry_ids Get_retro_Entry_ids%ROWTYPE;
1815
1816
1817 ret_counter Number;
1818 retro_start date;
1819 retro_end date;
1820 x number;
1821 /* Added for #bug no 5846278 */
1822 orig_eff_date date;
1823 retro_eff_date date;
1824 time_span varchar2(10);
1825 retro_type varchar2(50);
1826 l_adv_retro_flag pay_legislation_rules.rule_mode%TYPE;
1827
1828 Begin
1829 g_debug := hr_utility.debug_enabled;
1830 ret_counter := 1;
1831 /* Bug# 5846278 */
1832 /* Checked for legislation rule.*/
1833
1834 OPEN get_legislation_rule;
1835 FETCH get_legislation_rule INTO l_adv_retro_flag;
1836 IF get_legislation_rule%NOTFOUND THEN
1837 l_adv_retro_flag := 'N';
1838 END IF;
1839 CLOSE get_legislation_rule;
1840
1841 /* Retropay by element - logic for Retropay By Element is used */
1842
1843 IF l_adv_retro_flag <> 'Y'
1844 THEN
1845
1846 OPEN Get_retro_Entry_ids(p_year_start,p_year_end,p_assignment_id);
1847 LOOP
1848 FETCH Get_retro_Entry_ids INTO rec_ret_entry_ids;
1849 IF Get_retro_Entry_ids%NOTFOUND Then
1850 IF g_debug THEN
1851 hr_utility.set_location('Get_retro_Entry_Id: not found',1);
1852 END if;
1853 Exit;
1854 End If;
1855 IF g_debug THEN
1856 hr_utility.set_location('Calling Get Retro Periods',2);
1857 END if;
1858
1859 x:=pay_au_paye_ff.get_retro_period(rec_ret_entry_ids.element_entry_id,
1860 rec_ret_entry_ids.date_earned,
1861 p_registered_employer, /*Bug 4418107*/
1862 retro_start,
1863 retro_end);
1864
1865 IF g_debug THEN
1866 hr_utility.set_location('Back from call to Get Retro Periods',3);
1867 END if;
1868
1869 IF months_between(rec_ret_entry_ids.date_earned,retro_end) > 12 then
1870 IF g_debug THEN
1871 hr_utility.set_location('Getting Retro Allowance Greater than 12 months',4);
1872 END if;
1873
1874 OPEN Get_retro_Allowances(rec_ret_entry_ids.element_entry_id);
1875 FETCH Get_retro_Allowances INTO rec_retro_Allowances;
1876 CLOSE Get_retro_Allowances;
1877
1878
1879 If NVL(rec_retro_Allowances.balance_value,0) > 0 Then
1880
1881 t_ret_allowances(ret_counter).balance_name := rec_retro_Allowances.balance_name;
1882 t_ret_allowances(ret_counter).balance_value := rec_retro_Allowances.balance_value;
1883 ret_counter := ret_counter+1;
1884 End If;
1885
1886 END IF;
1887 END LOOP;
1888
1889 CLOSE Get_retro_Entry_ids;
1890
1891 /*Bug 7171534 Added t_allowance_balance.count > 0 in if clause */
1892 if t_ret_allowances.count > 0 and t_allowance_balance.count > 0 then
1893 For i in 1..t_ret_allowances.last
1894 LOOP
1895 For j in 1..t_allowance_balance.last
1896 LOOP
1897 if t_ret_allowances(i).balance_name = t_allowance_balance(j).balance_name then
1898 t_allowance_balance(j).balance_value := t_allowance_balance(j).balance_value - t_ret_allowances(i).balance_value;
1899 exit;
1900 end if;
1901 END LOOP;
1902 END LOOP;
1903 end if;
1904
1905 t_ret_allowances.delete;
1906
1907 /*bug #5846278 Enh Retro .
1908 If Retrospective Payment Greater than 12 months then it is deducted from total allowance*/
1909
1910 ELSE
1911 OPEN Get_retro_Entry_ids(p_year_start,p_year_end,p_assignment_id);
1912 LOOP
1913 FETCH Get_retro_Entry_ids INTO rec_ret_entry_ids;
1914 IF Get_retro_Entry_ids%NOTFOUND Then
1915 IF g_debug THEN
1916 hr_utility.set_location('Get_retro_Entry_Id: not found',1);
1917 END if;
1918 Exit;
1919 End If;
1920 IF g_debug THEN
1921 hr_utility.set_location('Calling Get Retro Time Span',2);
1922 END if;
1923
1924 x:= pay_au_paye_ff.get_retro_time_span(rec_ret_entry_ids.element_entry_id,
1925 rec_ret_entry_ids.date_earned,
1926 rec_ret_entry_ids.tax_unit_id,
1927 retro_start,
1928 retro_end,
1929 orig_eff_date,
1930 retro_eff_date,
1931 time_span,
1932 retro_type);
1933 IF g_debug THEN
1934 hr_utility.set_location('Back from call to Get Retro Time Span',3);
1935 END if;
1936 IF time_span ='GT12' then
1937 IF g_debug THEN
1938 hr_utility.set_location('Getting Retro Allowance Greater than 12 months',4);
1939 END if;
1940 OPEN Get_retro_Allowances(rec_ret_entry_ids.element_entry_id);
1941 FETCH Get_retro_Allowances INTO rec_retro_Allowances;
1942 CLOSE Get_retro_Allowances;
1943
1944 If NVL(rec_retro_Allowances.balance_value,0) > 0 Then
1945 t_ret_allowances(ret_counter).balance_name := rec_retro_Allowances.balance_name;
1946 t_ret_allowances(ret_counter).balance_value := rec_retro_Allowances.balance_value;
1947 ret_counter := ret_counter+1;
1948
1949 End If;
1950 END IF;
1951 END LOOP;
1952
1953 CLOSE Get_retro_Entry_ids;
1954
1955 /*Bug 7171534 Added t_allowance_balance.count > 0 in if clause */
1956 if t_ret_allowances.count > 0 and t_allowance_balance.count > 0 then
1957 For i in 1..t_ret_allowances.last
1958 LOOP
1959 For j in 1..t_allowance_balance.last
1960 LOOP
1961
1962 if t_ret_allowances(i).balance_name = t_allowance_balance(j).balance_name then
1963 t_allowance_balance(j).balance_value := t_allowance_balance(j).balance_value - t_ret_allowances(i).balance_value;
1964
1965 exit;
1966 end if;
1967 END LOOP;
1968 END LOOP;
1969 end if;
1970
1971 t_ret_allowances.delete;
1972
1973 END IF; /*bug #5846278 */
1974 return 1;
1975
1976 End adjust_retro_allowances;
1977
1978
1979 /* Bug#4925547 */
1980
1981 procedure archive_limited_values
1982 (p_assignment_action_id number
1983 ,p_table in out nocopy tab_allownace_balance
1984 ,p_limit in binary_integer
1985 ,p_name_prefix in varchar2
1986 ,p_name_suffix in varchar2
1987 ,p_value_prefix in varchar2
1988 ,p_value_suffix in varchar2
1989 ,p_total_name in varchar2
1990 ) is
1991 l_total_value number := 0;
1992 l_procedure constant varchar2(80) := g_package||'.archive_limited_values';
1993 begin
1994 --
1995 -- Do the number of available balance exceed the limit?
1996 --
1997 if p_table.count > p_limit then
1998 --
1999 -- Sum the number of balances above the limit into a single value
2000 -- Store the summed value and the name into the index corresponding to the limit.
2001 --
2002 for i in p_limit..p_table.count
2003 loop
2004 if g_debug then
2005 hr_utility.set_location(l_procedure,5);
2006 end if;
2007 if p_table.exists(i) then
2008 l_total_value := l_total_value + p_table(i).balance_value;
2009 end if;
2010 end loop;
2011 p_table(p_limit).balance_name := p_total_name;
2012 p_table(p_limit).balance_value := l_total_value;
2013 end if;
2014 --
2015 -- Archive the values up to the limit
2016 --
2017 for i in 1..(p_limit)
2018 loop
2019 if p_table.exists(i) then
2020 if g_debug then
2021 hr_utility.set_location(l_procedure||' : archiving',20);
2022 end if;
2023
2024 create_extract_archive_details
2025 (p_assignment_action_id
2026 ,p_name_prefix || i || p_name_suffix
2027 , p_table(i).balance_name
2028 );
2029 create_extract_archive_details
2030 (p_assignment_action_id
2031 ,p_value_prefix || i || p_value_suffix
2032 ,p_table(i).balance_value
2033 );
2034 end if;
2035 end loop;
2036 end archive_limited_values;
2037 --
2038 ----------------------------------------------------------------------------
2039 --* Archive Allowance details
2040 -----------------------------
2041
2042 procedure archive_allowance_details
2043 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
2044 ,p_max_assignment_action_id in pay_assignment_actions.assignment_action_id%type
2045 ,p_registered_employer in number
2046 ,p_year_start in date
2047 ,p_year_end in date
2048 ,p_assignment_id in pay_assignment_actions.assignment_id%type
2049 ,p_alw_bal_exist out nocopy varchar2 -- 3098353
2050 ) IS
2051 l_procedure constant varchar2(80) := g_package||'.archive_allowance_details';
2052 /*
2053 ** 4174037 Modified the cursor get_allowance_balances to avoid the unnecessary get_value() call.
2054 ** 4701566 Modified the subquery of the below cursor to get allowance value for end-dated
2055 ** employees and also improve the performance of the query.
2056 */
2057 cursor get_allowance_balances
2058 (c_year_start date
2059 ,c_year_end date
2060 ,c_assignment_id pay_assignment_actions.assignment_id%type
2061 ,c_dimension_id pay_balance_dimensions.balance_dimension_id%type
2062 ) IS
2063 select balance_name
2064 , pay_balance_pkg.get_value(def_id,p_max_assignment_action_id,p_registered_employer,null,null,null,null) balance_value
2065 , def_id
2066 , bal_type_id
2067 from
2068 ( select distinct nvl(pbt.reporting_name,pbt.balance_name) balance_name
2069 , pdb.defined_balance_id def_id
2070 , pbt.balance_type_id bal_type_id
2071 from pay_element_types_f pet
2072 , per_all_assignments_f paa
2073 , pay_balance_types pbt
2074 , pay_defined_balances pdb
2075 --, pay_balance_dimensions pbd
2076 --, per_periods_of_service pps
2077 , pay_payroll_actions ppa
2078 , pay_assignment_actions pac
2079 , pay_run_results prr
2080 where pac.assignment_id = c_assignment_id
2081 and pac.tax_unit_id = p_registered_employer --2610141
2082 and paa.assignment_id = pac.assignment_id
2083 and pac.payroll_action_id = ppa.payroll_Action_id
2084 and ppa.effective_date between c_year_start and c_year_end /*bug 4063321*/
2085 and ppa.payroll_id = paa.payroll_id
2086 and ppa.action_type in ('Q','R','B','I','V')
2087 and pac.assignment_action_id = prr.assignment_Action_id
2088 and prr.element_type_id = pet.element_type_id
2089 and pet.element_information_category = 'AU_EARNINGS'
2090 and pet.element_information1 = 'Y'
2091 and pet.element_information2 = pbt.balance_type_id
2092 and pbt.balance_type_id = pdb.balance_type_id(+)
2093 and pdb.balance_dimension_id(+) = c_dimension_id
2094 --and pbd.balance_dimension_id = pdb.balance_dimension_id /*4863149*/
2095 --and pbd.dimension_name = '_ASG_LE_YTD' --2610141 /*4863149*/
2096 --and pbd.legislation_code = 'AU' /* Bug#2665475 , 4863149*/
2097 --and pps.period_of_service_id = paa.period_of_service_id
2098 --and nvl(pps.actual_termination_date,c_year_end)
2099 and ppa.effective_date between paa.effective_start_date and paa.effective_end_date
2100 and ppa.date_earned between pet.effective_start_date and pet.effective_end_date
2101 )
2102 order by 2 desc ;
2103
2104 rec_allowance_balances get_allowance_balances%rowtype;
2105 counter number := 1;
2106 i number;
2107 t_allowance_2006 tab_allownace_balance;
2108 --
2109 begin
2110 if g_debug then
2111 hr_utility.set_location(l_procedure, 1);
2112 hr_utility.trace('p_assignment_action_id.....= ' || p_assignment_action_id);
2113 hr_utility.trace('p_max_assignment_action_id.= ' || p_max_assignment_action_id);
2114 hr_utility.trace('p_registered_employer......= ' || p_registered_employer);
2115 hr_utility.trace('p_year_start...............= ' || p_year_start);
2116 hr_utility.trace('p_year_end.................= ' || p_year_end);
2117 hr_utility.trace('p_assignment_id............= ' || p_assignment_id);
2118 end if;
2119 --
2120 -----------------------------------------------------------------------------------------
2121 -- Archive the Allowances
2122 -----------------------------------------------------------------------------------------
2123 --
2124 -- Archive up to 4 allowances , store the value and name of the balance for the employees
2125 -- If the employee has more than 4 allowances than store name and value of first
2126 -- 3 allowances based on the value of balance in descending order.
2127 -- Then calculate the sum of the remaining balances and store name as 'Various'
2128 -- /* Bug#4925547 */
2129 -- To support 2006/2007 Payment Summary we need to archive the new 30 allowance names and
2130 -- values as well as continuing to archive the original 4 items mentioned above
2131 --
2132 -----------------------------------------------------------------------------------------
2133 --
2134 open get_allowance_balances(p_year_start, p_year_end, p_assignment_id, g_dimension_id); /*4863149*/
2135 loop
2136 fetch get_allowance_balances into rec_allowance_balances;
2137 if get_allowance_balances%notfound then
2138 if g_debug then
2139 hr_utility.set_location(l_procedure, 3);
2140 end if;
2141 exit;
2142 end if;
2143 --
2144 -- 4863149 - Raise error when there is no defined balance id for allowance balance
2145 --
2146 if rec_allowance_balances.def_id is null then
2147 raise_application_error(-20101, 'Allowance Balance Type ' || rec_allowance_balances.bal_type_id || ' not associated with dimension _ASG_LE_YTD'); /* 4177630 */
2148 end if ;
2149 --
2150 if nvl(rec_allowance_balances.balance_value,0) > 0 then
2151 if g_debug then
2152 hr_utility.set_location(l_procedure, 3);
2153 hr_utility.set_location(l_procedure || ' : balance_name... = '||rec_allowance_balances.balance_name,1);
2154 hr_utility.set_location(l_procedure || ' : balance_value.. = '||rec_allowance_balances.balance_value,1);
2155 end if;
2156 t_allowance_balance(counter).balance_name := rec_allowance_balances.balance_name;
2157 t_allowance_balance(counter).balance_value := rec_allowance_balances.balance_value;
2158 counter := counter + 1;
2159 end if;
2160 end loop;
2161 close get_allowance_balances;
2162 --
2163 -- 2855658
2164 --
2165 i := adjust_retro_allowances
2166 (t_allowance_balance
2167 ,p_year_start
2168 ,p_year_end
2169 ,p_assignment_id
2170 ,p_registered_employer --2610141
2171 );
2172 --
2173 -- Copy the table as we need to manipulate and archive twice
2174 -- to support the pre 2006 and post archives.
2175 --
2176 t_allowance_2006 := t_allowance_balance;
2177 --
2178 -- /* Bug#4925547 */
2179 -- Always archive the previous method for allowances
2180 -- as these are used in other processes as well as form
2181 -- the total value for allowances in the payment summary
2182 --
2183 --
2184 -- If more than 4 allowances exists, calculate sum of all
2185 -- and assign the sum to 4th balance with name 'Various'
2186 --
2187 archive_limited_values
2188 (p_assignment_action_id => p_assignment_action_id
2189 ,p_table => t_allowance_balance
2190 ,p_limit => 4
2191 ,p_name_prefix => 'X_ALLOWANCE_NAME_'
2192 ,p_name_suffix => ''
2193 ,p_value_prefix => 'X_ALLOWANCE_'
2194 ,p_value_suffix => '_ASG_YTD'
2195 ,p_total_name => 'Various'
2196 );
2197 --/* Bug#4925547 */
2198 -- Now if the financial year is 2006/2007 or above
2199 -- then we perform the additional archive.
2200 --
2201 if to_number(to_char(p_year_start,'YYYY')) >= 2006 then
2202 if g_debug then
2203 hr_utility.set_location(l_procedure, 30);
2204 end if;
2205 --
2206 archive_limited_values
2207 (p_assignment_action_id => p_assignment_action_id
2208 ,p_table => t_allowance_2006
2209 ,p_limit => 30
2210 ,p_name_prefix => 'X_ALLOWANCE_'
2211 ,p_name_suffix => '_NAME'
2212 ,p_value_prefix => 'X_ALLOWANCE_'
2213 ,p_value_suffix => '_VALUE'
2214 ,p_total_name => 'Miscellaneous'
2215 );
2216 end if;
2217 --
2218 -- 3098353:- Checks if the Allowance exist in the current year.
2219 --
2220 if t_allowance_balance.count > 0 then
2221 p_alw_bal_exist := 'TRUE';
2222 else
2223 p_alw_bal_exist := 'FALSE';
2224 end if;
2225 ---------------------------------------------------------
2226 t_allowance_balance.delete; /* 2968127- cleared PL/SQL table */
2227 if g_debug then
2228 hr_utility.set_location(l_procedure,999);
2229 end if;
2230 exception
2231 when others then
2232 if g_debug then
2233 hr_utility.set_location(l_procedure,000);
2234 end if;
2235 raise;
2236 end archive_allowance_details;
2237 --
2238 -----------------------------------------------------------
2239 -- in case for a terminated employee payroll has been run
2240 -- but prepayments have not been made then this is an exception
2241 -- condition in the validation report. This procedure
2242 -- archives X_ETP_ON_TERMNATION_PAID and
2243 -- X_ETP_EMPLOYEE_PAYMENT_DATE
2244 -----------------------------------------------------------
2245
2246 procedure archive_etp_payment_details
2247 ( p_assignment_action_id pay_assignment_actions.assignment_action_id%type,
2248 p_registered_employer NUMBER, --2610141
2249 p_assignment_id per_all_Assignments_f.assignment_id%type ,
2250 p_year_start date,
2251 p_year_end date ) as
2252
2253
2254 cursor etp_paid(c_assignment_id per_all_Assignments_f.assignment_id%type,
2255 c_year_start date ,
2256 c_year_end date ) is
2257
2258 select prv.result_value
2259 ,ppa.payroll_action_id
2260 ,pac.assignment_action_id
2261 ,ppa.effective_date
2262 from pay_element_types_f pet
2263 ,pay_input_values_f piv
2264 ,per_all_assignments_f paa
2265 ,pay_run_results prr
2266 ,pay_run_result_values prv
2267 ,pay_assignment_actions pac
2268 ,pay_payroll_actions ppa
2269 ,pay_payrolls_f papf /* bug Number 4278361 */
2270 where pet.element_type_id = piv.element_type_id
2271 and pet.element_name = 'ETP on Termination'
2272 and piv.name = 'Pay ETP Components'
2273 and paa.assignment_id = c_assignment_id
2274 and prv.input_value_id = piv.input_value_id
2275 and prr.element_type_id = pet.element_type_id
2276 and prr.run_result_id = prv.run_result_id
2277 and prr.assignment_action_id = pac.assignment_action_id
2278 and pac.assignment_id = paa.assignment_id
2279 and pac.payroll_action_id = ppa.payroll_action_id
2280 and paa.effective_start_date between pet.effective_start_date
2281 and pet.effective_end_date
2282 and paa.effective_start_date between piv.effective_start_date
2283 and piv.effective_end_date
2284 and papf.payroll_id=paa.payroll_id
2285 and ppa.payroll_id=papf.payroll_id
2286 and ppa.action_type in ('R','Q','I','B','V') /* bug Number 4278361 */
2287 and ppa.effective_date between papf.effective_start_date and papf.effective_end_date
2288 and ppa.effective_date between paa.effective_start_date
2289 and paa.effective_end_date
2290 and ppa.effective_date between c_year_start
2291 and c_year_end
2292 and pac.tax_unit_id = p_registered_employer; --2610141
2293
2294 cursor etp_prepayment
2295 (c_assignment_action_id pay_assignment_actions.assignment_action_id%type,
2296 c_payroll_action_id pay_payroll_actions.payroll_action_id%type,
2297 c_year_start date,
2298 c_year_end date )
2299 is select to_char(pppa.effective_date,'DDMMYYYY')
2300 from pay_action_interlocks pai
2301 ,pay_assignment_actions pac
2302 ,pay_payroll_actions ppa
2303 ,pay_assignment_actions ppac
2304 ,pay_payroll_actions pppa
2305 where pac.payroll_action_id = ppa.payroll_action_id
2306 and pac.assignment_action_id = c_assignment_action_id
2307 and pac.assignment_action_id = pai.locked_action_id
2308 and ppa.payroll_action_id = c_payroll_action_id
2309 and ppac.assignment_action_id =pai.locking_Action_id
2310 and pppa.payroll_Action_id = ppac.payroll_Action_id
2311 and ppa.effective_date between c_year_start
2312 and c_year_end;
2313 r_etp_paid etp_paid%rowtype;
2314
2315 -- cursor to get payroll and assignment actions when termination payments are done
2316 -- through balance adjustment/balance initialization(Bug 2574186)
2317
2318 /* Bug No: 3603495 - Performance Fix - Modified the following cursor by introducing per_assignments_f table and its joins */
2319
2320 cursor etp_BA_or_BI
2321 (c_assignment_id per_all_assignments_f.assignment_id%type,
2322 c_year_start date ,
2323 c_year_end date )
2324 is select max(ppa.payroll_action_id) payroll_action_id
2325 ,max(pac.assignment_action_id) assignment_action_id
2326 from per_assignments_f paf
2327 ,pay_assignment_actions pac
2328 ,pay_payroll_actions ppa
2329 where pac.assignment_id = c_assignment_id
2330 and pac.tax_unit_id = p_registered_employer --2610141
2331 and paf.assignment_id = pac.assignment_id
2332 and ppa.action_type in ('B','I')
2333 and pac.payroll_action_id = ppa.payroll_action_id
2334 and pac.action_status = 'C'
2335 and ppa.action_status = 'C'
2336 and ppa.payroll_id = paf.payroll_id /* Added for bug 5371102 for performance*/
2337 and ppa.date_earned between paf.effective_start_date and paf.effective_end_date /* Added for bug 5371102 for performance*/
2338 and (pay_balance_pkg.get_value(pkg_lump_sum_c_def_bal_id, pac.assignment_action_id,p_registered_employer,null,null,null,null)) > 0 --2610141
2339 and ppa.effective_date between c_year_start
2340 and c_year_end ;
2341
2342 r_etp_ba_or_bi etp_BA_or_BI%rowtype;
2343
2344 -- Bug No : 2574186 initialize the l_etp_paid as 'N', So that the dbi
2345 -- X_ETP_ON_TERMINATION_PAID is archived as 'N' incase the Lump C Payemnt
2346 -- exists and prepayment is not not run
2347 l_etp_paid varchar2(1);
2348 l_etp_paid_date varchar2(20);
2349
2350 begin
2351
2352 l_etp_paid := 'N';
2353
2354 open etp_paid(p_assignment_id,p_year_start,p_year_end);
2355 fetch etp_paid into r_etp_paid ;
2356
2357 if (etp_paid%found and r_etp_paid.result_value = 'Y') then
2358 l_etp_paid := 'Y';
2359
2360 open etp_prepayment(r_etp_paid.assignment_action_id,r_etp_paid.payroll_action_id,p_year_start,p_year_end);
2361 fetch etp_prepayment into l_etp_paid_date;
2362
2363 if (etp_prepayment%notfound) then
2364 l_etp_paid := 'N';
2365 end if;
2366
2367 close etp_prepayment;
2368
2369 else
2370 -- Bug 2574186 - Check if the prepayment is run in case of BA/BI
2371 open etp_BA_or_BI(p_assignment_id,p_year_start,p_year_end);
2372 fetch etp_BA_or_BI into r_etp_ba_or_bi ;
2373 if etp_BA_or_BI%found then
2374 l_etp_paid := 'Y';
2375 open etp_prepayment(r_etp_ba_or_bi.assignment_action_id,r_etp_ba_or_bi.payroll_action_id,p_year_start,p_year_end);
2376 fetch etp_prepayment into l_etp_paid_date;
2377 if (etp_prepayment%notfound) then
2378 l_etp_paid := 'N';
2379 end if;
2380 close etp_prepayment;
2381 end if;
2382 close etp_BA_or_BI;
2383 end if;
2384 close etp_paid;
2385
2386
2387 ---------------------------------------
2388 -- create the archive items
2389 ---------------------------------------
2390
2391 create_extract_archive_details(p_assignment_action_id,
2392 'X_ETP_ON_TERMINATION_PAID',
2393 l_etp_paid );
2394 create_extract_archive_details(p_assignment_action_id,
2395 'X_ETP_EMPLOYEE_PAYMENT_DATE',
2396 l_etp_paid_date);
2397 exception
2398 when others then
2399 IF g_debug THEN
2400 hr_utility.set_location('Error in archive_etp_payment_details ',99);
2401 END if;
2402 Raise;
2403 end archive_etp_payment_details ;
2404 --
2405 -- /* Bug#4925547 */
2406 -- As of 2006/2007 financial year we provide an alternative solution to archiving
2407 -- union fees.
2408 --
2409 -- As of 2006/2007 there is a new method of setting up union fees
2410 --------------------------------------------------------------------
2411 -- This method involves entering an associated balance into a flex
2412 -- segment of the Element Developer DF.
2413 -- The archive must continue to use the old method (mentioned above)
2414 -- and in addition to this, if the financial year is 2006/2007 or
2415 -- above then also archive for the new method.
2416 --------------------------------------------------------------------
2417 --
2418 procedure archive_2006_unions
2419 (p_assignment_id in per_all_assignments.assignment_id%type
2420 ,p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
2421 ,p_max_assignment_action_id in pay_assignment_actions.assignment_action_id%type
2422 ,p_registered_employer in number
2423 ,p_year_start in date
2424 ,p_year_end in date
2425 --,p_alw_bal_exist out nocopy varchar2
2426 ) as
2427
2428 --
2429 -- This cursor returns the balance value for union elements. Element that are identified as a 'Union' fee element
2430 -- will have segment 1 of the Element Developer flex linking the element to a specific balance
2431 -- in conjunction with feeding the legislative balance called 'Union Fees'
2432 -- Bug#5591993 Removed join for table pay_balance_type to avoid MJC
2433 -- and moved the code to cursor c_union_balance(initliazation code).
2434 cursor csr_unions_2006 (c_balance_type pay_balance_types.balance_type_id%type) is
2435 select distinct nvl(pbt.reporting_name, pbt.balance_name) balance_name
2436 , pay_balance_pkg.get_value(pdb.defined_balance_id, p_max_assignment_action_id, p_registered_employer,null,null,null,null) balance_value
2437 , pdb.defined_balance_id def_id
2438 , pbt.balance_type_id bal_type_id
2439 from pay_element_types_f pet
2440 , per_all_assignments_f paa
2441 , pay_balance_types pbt
2442 , pay_defined_balances pdb
2443 , pay_payroll_actions ppa
2444 , pay_assignment_actions pac
2445 , pay_run_results prr
2446 , pay_balance_feeds_f pbf
2447 , pay_input_values_f piv
2448 where pac.assignment_id = p_assignment_id
2449 and pac.tax_unit_id = p_registered_employer
2450 and paa.assignment_id = pac.assignment_id
2451 and pac.payroll_action_id = ppa.payroll_Action_id
2452 and ppa.effective_date between p_year_start and p_year_end
2453 and ppa.payroll_id = paa.payroll_id
2454 and ppa.action_type in ('Q','R','B','I','V')
2455 and pac.assignment_action_id = prr.assignment_action_id
2456 and prr.element_type_id = pet.element_type_id
2457 and pet.element_information_category = 'AU_VOLUNTARY DEDUCTIONS'
2458 and pet.element_information1 = pbt.balance_type_id
2459 and pbt.balance_type_id = pdb.balance_type_id(+)
2460 and pdb.balance_dimension_id(+) = g_dimension_id
2461 and ppa.effective_date between paa.effective_start_date and paa.effective_end_date
2462 and ppa.date_earned between pet.effective_start_date and pet.effective_end_date
2463 and pet.element_type_id = piv.element_type_id
2464 and ppa.date_earned between piv.effective_start_date and piv.effective_end_date
2465 and piv.input_value_id = pbf.input_value_id
2466 and ppa.date_earned between pbf.effective_start_date and pbf.effective_end_date
2467 and pbf.balance_type_id = c_balance_type
2468 order by 2 desc ;
2469 --
2470 l_procedure constant varchar2(80) := g_package || '.archive_2006_unions';
2471 l_counter number := 1;
2472 l_4_value number;
2473 l_4_name pay_balance_types.balance_name%type;
2474 l_total_value number := 0;
2475 l_balance_type pay_balance_types.balance_type_id%type;
2476 begin
2477 g_debug := hr_utility.debug_enabled;
2478 --
2479 -- If the financial year permits then perform the union fees archive for 2006/2007.
2480 --
2481 if to_number(to_char(p_year_start,'YYYY')) >= 2006 then
2482 --
2483 for rec_union in csr_unions_2006(g_balance_type_id) loop
2484
2485 --
2486 -- 4863149 - Raise error when there is no defined balance id for allowance balance
2487 --
2488 if g_debug then
2489 hr_utility.set_location(l_procedure, 3);
2490 end if;
2491 --
2492 if rec_union.def_id is null then
2493 raise_application_error(-20101, 'Balance ID ' || rec_union.bal_type_id || ' not associated with dimension _ASG_LE_YTD');
2494 end if ;
2495 --
2496 -- Store all the values in a table
2497 --
2498 if nvl(rec_union.balance_value,0) > 0 then
2499 if g_debug then
2500 hr_utility.set_location(l_procedure, 3);
2501 hr_utility.set_location(l_procedure || ' : balance_name... = '||rec_union.balance_name,3);
2502 hr_utility.set_location(l_procedure || ' : balance_value.. = '||rec_union.balance_value,3);
2503 end if;
2504 t_union_table(l_counter).balance_name := rec_union.balance_name;
2505 t_union_table(l_counter).balance_value := rec_union.balance_value;
2506 l_counter := l_counter + 1;
2507 end if;
2508 end loop;
2509 --
2510 -- No archive the names, values
2511 --
2512 /*
2513 archive_limited_values
2514 (p_assignment_action_id => p_assignment_action_id
2515 ,p_table => t_union_table
2516 ,p_limit => 4
2517 ,p_name_prefix => 'X_UNION_'
2518 ,p_name_suffix => '_NAME'
2519 ,p_value_prefix => 'X_UNION_'
2520 ,p_value_suffix => '_VALUE'
2521 ,p_total_name => 'Miscellaneous'
2522 );
2523 */
2524 -- Due to the change in display of union fees for the 2006 payment summary layout,
2525 -- there are now 2 methods of setting up union fee elements.
2526 -- =====================================================
2527 -- The first and 'original' method involves users feeding their own elements to the
2528 -- 'Union Fees' legislative balance.
2529 -- This is mandatory setup so that all existing functionality is retained.
2530 --
2531 -- The 'new' method involves users 'linking' their union elements to an additional balance
2532 -- they have created. This enables the identification of values for the individual unions.
2533 -- The 'linking' involves creating a feed to the new balance and selecting that balance
2534 -- as the 'Union fees primary balance' in the Element DF.
2535 -- This setup is not mandatory
2536 --
2537 -- Although it is recommended that users setup ALL their union elements using only 1 of the above methods,
2538 -- it cannot be enforced within the application. Therefore, to handle this case we use the 'Union Fees'
2539 -- balance as the definitive balance and check this when archiving union elements which adhere to the
2540 -- 'new' method of setup
2541 --
2542 --
2543 -- Archive the first 3 elements which adhere to the 'new' setup
2544 --
2545 for i in 1..3
2546 loop
2547 --
2548 -- Only archive if the values exist
2549 --
2550 if t_union_table.exists(i) then
2551 if g_debug then
2552 hr_utility.set_location(l_procedure,20);
2553 end if;
2554 create_extract_archive_details(p_assignment_action_id,'X_UNION_' || i || '_NAME',t_union_table(i).balance_name);
2555 create_extract_archive_details(p_assignment_action_id,'X_UNION_' || i || '_VALUE',t_union_table(i).balance_value);
2556 l_total_value := l_total_value + t_union_table(i).balance_value;
2557 end if;
2558 end loop;
2559 --
2560 -- We still need to archive the 4th position... if it exists
2561 --
2562 if g_debug then
2563 hr_utility.trace('There are '||t_union_table.count||' union elements');
2564 hr_utility.trace('Already archived value is '||l_total_value);
2565 end if;
2566 --
2567 -- The definitive figure for Union Fees is stored in the legislative union balance 'Union Fees'.
2568 -- It is possible that there may be a combination of old and new union elements being used.
2569 --
2570 -- A 'misc' figure must be archived if:
2571 -- 1. There exists more than 4 new union elements.
2572 -- 2. There is a combination of new and old union elements.
2573 -- In this case the sum of new unions will not equal the legislative union balance.
2574 --
2575 --
2576 -- 5679568
2577 --
2578 if t_union_table.count > 4
2579 or l_total_value <> p_result_table(13).balance_value then
2580 --
2581 -- There are more than 4 union elements with the 'new' setup method
2582 -- however, it is possible that the remaining union elements are not all using the 'new' method
2583 -- so we must use the definitive balance to obtain the correct value. To do this
2584 -- we subtract the values just archived from the definitive balance. The remainder must
2585 -- equal the correct value.
2586 --
2587 l_4_name := 'Miscellaneous';
2588 l_4_value := p_result_table(13).balance_value - l_total_value;
2589 create_extract_archive_details(p_assignment_action_id,'X_UNION_4_NAME',l_4_name);
2590 create_extract_archive_details(p_assignment_action_id,'X_UNION_4_VALUE',l_4_value);
2591 else
2592 --
2593 -- If a 4th union exists then lets archive it
2594 --
2595 if t_union_table.exists(4) then
2596 --
2597 -- Check if the values added up equal the value stored in the definitive balance.
2598 --
2599 if g_debug then
2600 hr_utility.set_location(l_procedure,35);
2601 end if;
2602 --
2603 if (l_total_value + t_union_table(4).balance_value) = p_result_table(13).balance_value then
2604 --
2605 -- All elements have been setup using the new method and therefore the values are correct.
2606 -- We can store the 4th value in its corresponding position
2607 --
2608 if g_debug then
2609 hr_utility.set_location(l_procedure,40);
2610 end if;
2611 --
2612 l_4_name := t_union_table(4).balance_name;
2613 l_4_value := t_union_table(4).balance_value;
2614 else
2615 --
2616 -- There is a amalgamation of methods used to setup union elements.
2617 -- This means that we need to check the definitive balance to obtain correct values.
2618 -- This additional value must be stored in the 4th position and displayed as 'Miscellaneous'
2619 --
2620 l_4_name := 'Miscellaneous';
2621 l_4_value := p_result_table(13).balance_value - l_total_value;
2622 end if;
2623 create_extract_archive_details(p_assignment_action_id,'X_UNION_4_NAME',l_4_name);
2624 create_extract_archive_details(p_assignment_action_id,'X_UNION_4_VALUE',l_4_value);
2625 end if;
2626 end if;
2627 --
2628 end if;
2629 --
2630 /*
2631 if t_union_table.count > 0 then
2632 p_alw_bal_exist := 'TRUE';
2633 else
2634 p_alw_bal_exist := 'FALSE';
2635 end if;
2636 */
2637 ---------------------------------------------------------
2638 t_union_table.delete;
2639 --
2640 end archive_2006_unions;
2641 --
2642 ----------------------------------------------------------------------+
2643 -- procedure to archive 'X_UNION_NAME' - added for
2644 -- bug no : 1764017
2645 ----------------------------------------------------------------------+
2646 --
2647 procedure archive_union_name
2648 (p_assignment_id in per_all_assignments.assignment_id%type
2649 ,p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
2650 ,p_max_assignment_action_id in pay_assignment_actions.assignment_action_id%type
2651 ,p_registered_employer number
2652 ,p_year_start in date
2653 ,p_year_end in date
2654 ) as
2655 --
2656 cursor csr_union_fees
2657 (c_assignment_id per_all_assignments.assignment_id%type
2658 ,c_year_end date
2659 ) is
2660 select distinct pet.reporting_name
2661 , pet.element_information_category
2662 , pet.element_information1
2663 from pay_balance_types pbt
2664 , pay_balance_feeds_f pbf
2665 , pay_input_values_f piv
2666 , pay_element_types_f pet
2667 , pay_element_entries_f pee
2668 , pay_element_links_f pel
2669 , per_all_people_f pap
2670 , per_periods_of_service pps
2671 , per_all_assignments_f paa
2672 where pet.element_type_id = piv.element_type_id
2673 and pbf.input_value_id = piv.input_value_id
2674 and pbf.balance_type_id = pbt.balance_type_id
2675 and pet.element_type_id = pel.element_type_id
2676 and pel.element_link_id = pee.element_link_id
2677 and pee.assignment_id = c_assignment_id
2678 and pee.assignment_id = paa.assignment_id
2679 and paa.person_id = pap.person_id
2680 and paa.person_id = pps.person_id
2681 and pbt.balance_name = 'Union Fees'
2682 and pbt.legislation_code = g_legislation_code
2683 and pet.effective_start_date = (select max(et.effective_start_date )
2684 from pay_element_types_f et
2685 where et.element_type_id= pet.element_type_id
2686 and nvl(pps.actual_termination_date,c_year_end)
2687 between pet.effective_Start_date and pet.effective_end_date
2688 );
2689 --
2690 -- 2610141 - Removed the cursor for getting union fees
2691 --
2692 /*CURSOR union_def_bal_id
2693 IS
2694 SELECT pdb.defined_balance_id
2695 FROM pay_balance_types pbt
2696 ,pay_defined_balances pdb
2697 ,pay_balance_dimensions pbd
2698 WHERE pbt.balance_name = 'Union Fees'
2699 AND pbt.legislation_code = 'AU'
2700 AND pbd.legislation_code = 'AU'
2701 AND pbt.balance_type_id = pdb.balance_type_id
2702 AND pbd.balance_dimension_id = pdb.balance_dimension_id
2703 AND pbd.dimension_name = '_ASG_LE_YTD'; --2610141*/
2704
2705 l_reporting_name pay_element_types_f.reporting_name%type;
2706 l_procedure constant varchar2(80) := g_package || '.archive_union_name';
2707 l_element_information_category pay_element_types_f.element_information_category%type;
2708 l_element_information1 pay_element_types_f.element_information1%type;
2709 l_count number := 0;
2710 l_2006_union_setup_found boolean;
2711 --
2712 begin
2713 g_debug := hr_utility.debug_enabled;
2714 if g_debug then
2715 hr_utility.set_location(l_procedure,1);
2716 end if;
2717 --
2718 -------------------------------------------
2719 -- if two or more elements feed the balance
2720 -- 'Union Fees' then reporting_name = 'MISCELLANEOUS'
2721 -- else archive the reporting name for the element.
2722 -- Notice that only the name is considered here, the value
2723 -- is archived elsewhere.
2724 --
2725 --
2726 -- 4738470 - Included check for balance value
2727 -- Only a single value needs to be checked, which is already stored in
2728 -- position 13 of the table.
2729 --
2730 if p_result_table(13).balance_value > 0 then
2731 --
2732 for rec_union_fees in csr_union_fees(p_assignment_id, p_year_end) loop
2733 -- /* Bug#4925547 */
2734 -- Check if any of the elements have had the new union setup entered
2735 --
2736 if rec_union_fees.element_information_category = 'AU_VOLUNTARY DEDUCTIONS'
2737 and rec_union_fees.element_information1 is not null then
2738 l_2006_union_setup_found := true;
2739 end if;
2740 --
2741 l_reporting_name := rec_union_fees.reporting_name;
2742 l_count := l_count + 1;
2743 end loop;
2744 --
2745 -- If more than 1 union fees element exists
2746 -- then the displayed name should be as follows
2747 --
2748 if l_count > 1 then
2749 l_reporting_name :='MISCELLANEOUS';
2750 end if;
2751 --
2752 if g_debug then
2753 hr_utility.trace('reporting name : '||l_reporting_name);
2754 end if;
2755 --
2756 end if;
2757 --
2758 if l_reporting_name is not null then
2759 if g_debug then
2760 hr_utility.set_location(l_procedure, 20);
2761 end if;
2762 --
2763 create_extract_archive_details
2764 (p_assignment_action_id
2765 ,'X_UNION_NAMES'
2766 ,l_reporting_name
2767 );
2768 end if;
2769 -- /* Bug#4925547 */
2770 -- If the new setup has been performed then archive for this case
2771 --
2772 if l_2006_union_setup_found then
2773 if g_debug then
2774 hr_utility.set_location(l_procedure, 30);
2775 end if;
2776 archive_2006_unions
2777 (p_assignment_id => p_assignment_id
2778 ,p_assignment_action_id => p_assignment_action_id
2779 ,p_max_assignment_action_id => p_max_assignment_action_id
2780 ,p_registered_employer => p_registered_employer
2781 ,p_year_start => p_year_start
2782 ,p_year_end => p_year_end
2783 -- ,p_alw_bal_exist => p_alw_bal_exist
2784 );
2785 else
2786 -- /* Bug#4925547 */
2787 -- Otherwise we archive the old setup method for unions
2788 -- into the new archive items, assuming the financial year
2789 -- is 2006/2007 and above
2790 --
2791 if g_debug then
2792 hr_utility.set_location(l_procedure, 40);
2793 end if;
2794 --
2795 if to_number(to_char(p_year_start,'YYYY')) >= 2006 then
2796 if g_debug then
2797 hr_utility.set_location(l_procedure, 50);
2798 hr_utility.trace('l_reporting_name => '||l_reporting_name);
2799 hr_utility.trace('l_value_13 => '||p_result_table(13).balance_value);
2800 end if;
2801 -- /* Bug#4925547 */
2802 -- Format the misc name correctly for 2006 layout
2803 --
2804 if l_reporting_name = 'MISCELLANEOUS' then
2805 l_reporting_name := 'Miscellaneous';
2806 end if;
2807 create_extract_archive_details(p_assignment_action_id,'X_UNION_1_NAME',l_reporting_name);
2808 create_extract_archive_details(p_assignment_action_id,'X_UNION_1_VALUE',p_result_table(13).balance_value);
2809 end if;
2810 end if;
2811 --
2812 if g_debug then
2813 hr_utility.set_location(l_procedure,999);
2814 end if;
2815 exception
2816 when others then
2817 if g_debug then
2818 hr_utility.set_location(l_procedure, 000);
2819 end if;
2820 raise;
2821 end archive_union_name;
2822
2823 ----------------------------------------------------------------------+
2824 -- procedure to archive terminated employees details
2825 ----------------------------------------------------------------------+
2826 Procedure archive_etp_details
2827 (p_business_group_id in hr_organization_units.business_group_id%type,
2828 p_registered_employer in hr_organization_units.organization_id%type,
2829 p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
2830 p_assignment_Action_id in pay_assignment_actions.assignment_action_id%type,
2831 p_assignment_id in pay_assignment_actions.assignment_id%type,
2832 p_year_start in pay_payroll_actions.effective_date%type,
2833 p_year_end in pay_payroll_Actions.effective_date%type,
2834 p_lst_year_start in pay_payroll_Actions.effective_date%type,/*Bug3661230 Added one extra parameter*/
2835 p_transitional_flag out nocopy varchar2, /*Bug 6192381 Added New Parameters p_transitional_flag and p_part_prev_etp_flag */
2836 p_part_prev_etp_flag out nocopy varchar2) is
2837
2838
2839 l_etp_last_name per_all_people_f.last_name%type;
2840 l_etp_first_name per_all_people_f.first_name%type;
2841 l_etp_middle_name per_all_people_f.middle_names%type;
2842 l_etp_address_1 hr_locations.address_line_1%type;
2843 l_etp_address_2 hr_locations.address_line_2%type;
2844 l_etp_address_3 hr_locations.address_line_3%type;
2845 l_etp_suburb hr_locations.town_or_city%type;
2846 l_etp_state hr_locations.region_1%type;
2847 l_etp_postcode hr_locations.postal_code%type;
2848 l_etp_country fnd_territories_tl.territory_short_name%type;
2849 l_etp_employee_number per_all_people_f.employee_number%type;
2850 l_etp_date_of_birth per_all_people_f.date_of_birth%type;
2851 l_etp_employee_start_date per_periods_of_service.date_start%type;
2852 l_etp_death_benefit per_periods_of_service.leaving_reason%type;
2853 l_asgmnt_loc hr_locations.location_code%type;
2854 l_emp_no per_all_people_f.employee_number%type;
2855 l_payroll pay_all_payrolls_f.payroll_name%type;
2856 l_emp_type per_all_people_f.current_employee_flag%type;
2857 l_address_date_from date;
2858
2859 -----------------------------------------------------------------------------------------------+
2860 -- cursor to fetch terminated employees details
2861 -----------------------------------------------------------------------------------------------+
2862 cursor etp_details( c_business_group_id in hr_organization_units.business_group_id%type,
2863 c_registered_employer in hr_organization_units.organization_id%type,
2864 c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
2865 c_assignment_id in pay_assignment_actions.assignment_id%type,
2866 c_year_start in pay_payroll_actions.effective_date%type,
2867 c_year_end in pay_payroll_Actions.effective_date%type)
2868 is
2869 select pev.screen_entry_value tax_file_number
2870 ,pap.last_name employee_last_name
2871 ,pap.first_name employee_first_name
2872 ,substr(pap.middle_names, 1, decode(instr(pap.middle_names,' '), 0, 60, instr(pap.middle_names,'',1)-1)) employee_middle_name
2873 ,pad.address_line1 employee_address_1
2874 ,pad.address_line2 employee_address_2
2875 ,pad.address_line3 employee_address_3
2876 ,pad.town_or_city employee_suburb
2877 ,pad.region_1 employee_state
2878 ,pad.postal_code employee_postcode
2879 ,fta.territory_short_name employee_country
2880 ,pad.style address_style -- Bug 5364017
2881 ,pad.country address_country -- Bug 5364017
2882 ,pap.employee_number employee_number
2883 ,to_char(pap.date_of_birth,'DDMMYYYY') employee_date_of_birth
2884 ,to_char(pps.date_start,'DDMMYYYY') employee_start_date
2885 ,pps.pds_information2 death_benefit_type
2886 ,nvl(to_char(pps.actual_termination_date,'DDMMYYYY'),'31124712') employee_termination_date
2887 ,decode(pps.pds_information1, 'AU_D','Y', 'N') death_benefit/*bug#1955993*/
2888 ,pad.date_from date_from/*Bug 2977533 */
2889 from hr_organization_information hoi,
2890 hr_organization_units hou,
2891 hr_soft_coding_keyflex hsc,
2892 pay_element_types_f pet,
2893 pay_input_values_f piv,
2894 pay_element_links_f pel,
2895 pay_element_entries_f pee,
2896 pay_element_entry_values_f pev,
2897 per_all_assignments_f paa,
2898 per_all_people_f pap,
2899 per_addresses pad,
2900 fnd_territories_tl fta,
2901 per_periods_of_service pps,
2902 pay_payroll_actions ppa,
2903 pay_assignment_actions pac,
2904 hr_locations_all hlc /* Bug No : 2263587 */
2905 where hou.business_group_id = c_business_group_id
2906 and hou.organization_id = c_registered_employer
2907 and ppa.action_type = 'X'
2908 and hou.organization_id = hoi.organization_id
2909 and hoi.org_information_context = 'AU_LEGAL_EMPLOYER'
2910 and hou.business_group_id = pap.business_group_id
2911 and hsc.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
2912 and pet.element_name = 'Tax Information'
2913 and piv.name = 'Tax File Number'
2914 and pet.element_type_id = piv.element_type_id
2915 and pet.element_type_id = pel.element_type_id
2916 and pel.element_link_id = pee.element_link_id
2917 and pee.element_entry_id = pev.element_entry_id
2918 and piv.input_value_id = pev.input_value_id
2919 and paa.assignment_id = pee.assignment_id
2920 and pap.person_id = paa.person_id
2921 and pap.person_id = pad.person_id(+)
2922 and pad.primary_flag(+) = 'Y' /*Added for bug 2774577*/
2923 and fta.territory_code(+) = pad.country
2924 and fta.language(+) = userenv('LANG')
2925 and paa.location_id = hlc.location_id(+)
2926 and pap.person_id = pps.person_id
2927 and ppa.payroll_action_id = pac.payroll_action_id
2928 and paa.assignment_id = pac.assignment_id
2929 and pac.assignment_id = c_assignment_id
2930 and pac.payroll_action_id = c_payroll_action_id
2931 and hsc.segment1 = c_registered_employer /*Bug 2610141, Bug 4063321*/
2932 and pps.actual_termination_date between paa.effective_start_date
2933 and paa.effective_end_date
2934 and pps.actual_termination_date between pap.effective_start_date
2935 and pap.effective_end_date
2936 and pps.actual_termination_date between pel.effective_start_date
2937 and pel.effective_end_date
2938 and pps.actual_termination_date between pee.effective_start_date
2939 and pee.effective_end_date
2940 and pps.actual_termination_date between pet.effective_start_date
2941 and pet.effective_end_date
2942 and pps.actual_termination_date between pev.effective_start_date
2943 and pev.effective_end_date
2944 and pps.actual_termination_date between piv.effective_start_date
2945 and piv.effective_end_date
2946 and pps.actual_termination_date between p_lst_year_start /*Bug3661230*/
2947 and c_year_end
2948 order by pad.date_from desc;/*Bug 2977533 */
2949
2950 --------------------------------------------------------------------------------------------------------------------------+
2951 -- Bug 5956223 cursor to fetch input values Transitional ETP and Part of Previously Paid ETP of Element ETP on Termination
2952 --------------------------------------------------------------------------------------------------------------------------+
2953 cursor etp_trans_paid_flags(c_assignment_id per_all_Assignments_f.assignment_id%type,
2954 c_year_start date ,
2955 c_year_end date ) IS
2956 select prv.result_value INPUT_VALUE,piv.name INPUT_NAME
2957 from pay_element_types_f pet
2958 ,pay_input_values_f piv
2959 ,per_all_assignments_f paa
2960 ,pay_run_results prr
2961 ,pay_run_result_values prv
2962 ,pay_assignment_actions pac
2963 ,pay_payroll_actions ppa
2964 where pet.element_type_id = piv.element_type_id
2965 and pet.element_name = 'ETP on Termination'
2966 and piv.name in ('Transitional ETP','Part of Previously Paid ETP')
2967 and paa.assignment_id = c_assignment_id
2968 and prv.input_value_id = piv.input_value_id
2969 and prr.element_type_id = pet.element_type_id
2970 and prr.run_result_id = prv.run_result_id
2971 and prr.assignment_action_id = pac.assignment_action_id
2972 and pac.assignment_id = paa.assignment_id
2973 and pac.payroll_action_id = ppa.payroll_action_id
2974 and paa.effective_start_date between pet.effective_start_date and pet.effective_end_date
2975 and paa.effective_start_date between piv.effective_start_date and piv.effective_end_date
2976 and ppa.action_type in ('R','Q','I','B','V')
2977 and ppa.effective_date between paa.effective_start_date and paa.effective_end_date
2978 and ppa.effective_date between c_year_start and c_year_end
2979 and pac.tax_unit_id = p_registered_employer
2980 order by prr.run_result_id;
2981
2982 l_etp_details etp_details%rowtype;
2983 l_country fnd_territories_tl.territory_short_name%type;
2984
2985 lv_transitional_flag VARCHAR2(1);
2986 lv_part_prev_etp_flag VARCHAR2(1);
2987 Begin
2988 IF g_debug THEN
2989 hr_utility.set_location('Start of archive etp details procedure.. ', 1);
2990 hr_utility.set_location('Assignment action id : '||p_assignment_action_id, 2);
2991 END if;
2992
2993 lv_transitional_flag :='N';
2994 lv_part_prev_etp_flag :='N';
2995
2996 open etp_details(p_business_group_id
2997 ,p_registered_employer
2998 ,p_payroll_action_id
2999 ,p_assignment_id
3000 ,p_year_start
3001 ,p_year_end);
3002
3003 fetch etp_details into l_etp_details;
3004
3005 if etp_details%found then
3006 close etp_details;
3007 IF g_debug THEN
3008 hr_utility.set_location('etp tfn ' || l_etp_details.tax_file_number, 1);
3009 END if;
3010
3011 create_extract_archive_details(p_assignment_action_id,
3012 'X_ETP_TAX_FILE_NUMBER',
3013 l_etp_details.tax_file_number);
3014
3015 create_extract_archive_details(p_assignment_action_id,
3016 'X_ETP_EMPLOYEE_SURNAME',
3017 l_etp_details.employee_last_name);
3018
3019
3020 create_extract_archive_details(p_assignment_action_id,
3021 'X_ETP_EMPLOYEE_FIRST_NAME',
3022 l_etp_details.employee_first_name);
3023
3024 create_extract_archive_details(p_assignment_action_id,
3025 'X_ETP_EMPLOYEE_MIDDLE_NAME',
3026 l_etp_details.employee_middle_name);
3027
3028 create_extract_archive_details(p_assignment_action_id,
3029 'X_ETP_EMPLOYEE_ADDRESS_1',
3030 l_etp_details.employee_address_1);
3031
3032 create_extract_archive_details(p_assignment_action_id,
3033 'X_ETP_EMPLOYEE_ADDRESS_2',
3034 l_etp_details.employee_address_2);
3035
3036 create_extract_archive_details(p_assignment_action_id,
3037 'X_ETP_EMPLOYEE_ADDRESS_3',
3038 l_etp_details.employee_address_3);
3039
3040 create_extract_archive_details(p_assignment_action_id,
3041 'X_ETP_EMPLOYEE_SUBURB',
3042 l_etp_details.employee_suburb);
3043
3044 create_extract_archive_details(p_assignment_action_id,
3045 'X_ETP_EMPLOYEE_STATE',
3046 l_etp_details.employee_state);
3047
3048 create_extract_archive_details(p_assignment_action_id,
3049 'X_ETP_EMPLOYEE_POSTCODE',
3050 l_etp_details.employee_postcode);
3051
3052 /* Bug 5364017 - Check for Generic Address
3053 IF Address Style is Generic - then get the country name as entered in Address form
3054 */
3055 IF (l_etp_details.address_style = 'GENERIC')
3056 THEN
3057 l_country := l_etp_details.address_country;
3058 ELSE
3059 l_country := l_etp_details.employee_country;
3060 END IF;
3061
3062 create_extract_archive_details(p_assignment_action_id,
3063 'X_ETP_EMPLOYEE_COUNTRY',
3064 l_country);
3065
3066 create_extract_archive_details(p_assignment_action_id,
3067 'X_ETP_EMPLOYEE_DATE_OF_BIRTH',
3068 l_etp_details.employee_date_of_birth);
3069
3070 create_extract_archive_details(p_assignment_action_id,
3071 'X_ETP_DEATH_BENEFIT',
3072 l_etp_details.death_benefit);
3073
3074 create_extract_archive_details(p_assignment_action_id,
3075 'X_ETP_DEATH_BENEFIT_TYPE',
3076 l_etp_details.death_benefit_type);
3077
3078 else
3079 IF g_debug THEN
3080 hr_utility.set_location('Details for terminated employee not found ', 3);
3081 END if;
3082 close etp_details;
3083 end if;
3084
3085 /*Bug 5956223 Begin*/
3086 for csr_trans in etp_trans_paid_flags(p_assignment_id ,p_year_start,p_year_end )
3087 loop
3088 if csr_trans.INPUT_NAME = 'Transitional ETP' then
3089 lv_transitional_flag := csr_trans.INPUT_VALUE;
3090 end if;
3091
3092 if csr_trans.INPUT_NAME = 'Part of Previously Paid ETP' then
3093 lv_part_prev_etp_flag := csr_trans.INPUT_VALUE;
3094 end if;
3095
3096 end loop;
3097
3098 /*Bug 6192381 assigning values to out variables */
3099 /*Bug 7135544 Added NVL (N) to lv_transitional_flag and lv_part_prev_etp_flag */
3100 p_transitional_flag := nvl(lv_transitional_flag,'N');
3101 p_part_prev_etp_flag := nvl(lv_part_prev_etp_flag,'N');
3102
3103 create_extract_archive_details(p_assignment_action_id,
3104 'X_TRANSITIONAL_ETP',
3105 p_transitional_flag);
3106
3107 create_extract_archive_details(p_assignment_action_id,
3108 'X_PART_OF_PREVIOUS_ETP',
3109 p_part_prev_etp_flag);
3110
3111 /*Bug 5956223 End*/
3112
3113 ----------------------------------------------------
3114 -- Added for Bug 1746093
3115 -- this subprogram archives X_ETP_ON_TERMINATION_PAID
3116 -- and X_ETP_EMPLOYEE_PAYMENT_DATE
3117 ----------------------------------------------------
3118 archive_etp_payment_details( p_assignment_action_id ,
3119 p_registered_employer, --2610141
3120 p_assignment_id ,
3121 p_year_start,
3122 p_year_end );
3123
3124 IF g_debug THEN
3125 hr_utility.set_location('exiting archive etp details procedure ', 3);
3126 END if;
3127 exception
3128 when others then
3129 if etp_details%isopen then
3130 close etp_details;
3131 end if;
3132 IF g_debug THEN
3133 hr_utility.set_location('Error in archive etp details. ', 300);
3134 END if;
3135 raise;
3136 End archive_etp_details;
3137
3138 ----------------------------------------------------------------------+
3139 -- this procedure creates archive items
3140 -- X_PRE_JUL_83_COMPONENT_ASG_YTD
3141 -- X_POST_JUN_83_TAXED_ASG_YTD
3142 -- X_POST_JUN_83_UNTAXED_ASG_YTD
3143 -- X_DAYS_PRE_JUL_83
3144 -- X_DAYS_POST_JUL_83
3145 -- for terminated employees
3146 ---------------------------------------------------------------------+
3147
3148
3149 procedure archive_prepost_details
3150 (p_assignment_action_id in pay_assignment_actions.ASSIGNMENT_ACTION_ID%TYPE
3151 ,p_max_assignment_action_id in pay_assignment_actions.ASSIGNMENT_ACTION_ID%TYPE --2610141
3152 ,p_registered_employer in pay_assignment_actions.TAX_UNIT_ID%TYPE --2610141
3153 ,p_legislation_code in pay_defined_balances.legislation_code%TYPE
3154 ,p_assignment_id in pay_assignment_actions.ASSIGNMENT_ID%type
3155 ,p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
3156 ,p_actual_termination_date in per_periods_of_service.actual_termination_date%TYPE
3157 ,p_date_start in per_periods_of_service.date_start%TYPE
3158 ,p_year_start in pay_payroll_actions.effective_date%type
3159 ,p_year_end in pay_payroll_Actions.effective_date%type
3160 ,p_transitional_flag in varchar2 /*Bug 6192381 Added New Parameters p_transitional_flag and p_part_prev_etp_flag */
3161 ,p_part_prev_etp_flag in varchar2) is
3162
3163
3164 v_some_warning BOOLEAN;
3165 v_bal_value VARCHAR2(20);
3166 v_user_entity_id NUMBER;
3167 v_archive_item_id ff_archive_items.archive_item_id%TYPE;
3168 v_object_version_number NUMBER;
3169 e_prepost_error EXCEPTION;
3170 l_result NUMBER;
3171
3172 l_etp_payment NUMBER;
3173 l_pre01jul1983_days NUMBER;
3174 l_post30jun1983_days NUMBER;
3175 l_pre01jul1983_ratio NUMBER;
3176 l_post30jun1983_ratio NUMBER;
3177 l_pre01jul1983_value NUMBER;
3178 l_post30jun1983_value NUMBER;
3179 l_etp_service_date date; /* Bug# 2984390 */
3180 v_etp_service_date VARCHAR2(20);
3181 l_le_etp_service_date date; /* Bug 4177679*/
3182 l_balance_name pay_balance_types.balance_name%TYPE;
3183 l_indx NUMBER;
3184 TYPE prepost_rec IS RECORD (item_name varchar2(50),
3185 item_value number );
3186 TYPE prepost_type IS TABLE OF prepost_rec INDEX BY BINARY_INTEGER;
3187 tab_prepost_dtls prepost_type;
3188
3189 l_etp_pay_tran_no_ppetp NUMBER;
3190 l_etp_pay_tran_ppetp NUMBER;
3191 l_etp_pay_no_tran_no_ppetp NUMBER;
3192 l_etp_pay_no_tran_ppetp NUMBER;
3193 l_etp_pay_total NUMBER;
3194
3195 l_old_etp_pay NUMBER;
3196 l_old_pre01jul1983_value NUMBER;
3197 l_old_post30jun1983_value NUMBER;
3198
3199 l_item_name_pre VARCHAR2(50);
3200 l_item_name_post VARCHAR2(50);
3201
3202 /* Bug 2826802 : Cursor to get the Lump Sum C Payment balance value */
3203 /* Bug 6192381 : Removed cursor get_etp_payment_value as using BBR now*/
3204
3205 type etp_bal_type is table of varchar2(100) index by binary_integer;
3206 tab_etp_bal_name etp_bal_type ;
3207
3208 begin
3209 IF g_debug THEN
3210 hr_utility.set_location('Start of archive_prepost_details',15);
3211 hr_utility.set_location('Start of p_assg_action_id '|| p_assignment_action_id,16);
3212 END if;
3213
3214 --------------------------------------------------------------------------------+
3215 -- if the employee type is not current then archive the following termination details
3216 -- X_PRE_JUL_83_COMPONENT_ASG_YTD
3217 -- X_POST_JUN_83_UNTAXED_ASG_YTD
3218 -- X_POST_JUN_83_TAXED_ASG_YTD
3219 -- X_DAYS_PRE_JUL_83
3220 -- X_DAYS_POST_JUL_83
3221
3222 -- Bug No : 2826802
3223 -- The value of the Lump Sum C Payment balance will be fetched using
3224 -- hr_aubal.calc_all_balances. The value will be used as ETP Payment.
3225 --
3226 -- This value is multiplied by etp ratio obtained from
3227 -- pay_au_terminations.etp_prepost_ratios to get
3228 -- the value for X_PRE_JUL_83_COMPONENT and X_POST_JUN_83_TAXED.
3229 --
3230 -- Value for the other two archive items X_DAYS_PRE_JUL_83
3231 -- and X_DAYS_POST_JUL_83 are returned as an out parameter.
3232 -- The archive item name and archive item value are
3233 -- populated in a pl/sql table ( which is of record type)
3234 --
3235 -- Now we have all the parameters required to call create_extract_archive_details
3236 -- so we call it for the above archive items.
3237 --------------------------------------------------------------------------------+
3238
3239 /* Bug 2826802 : Get the Value of etp payment as value of the Lump Sum C Payment
3240 Balance and use it to get the pre and post 83 values */
3241
3242 l_pre01jul1983_value := 0;
3243 l_post30jun1983_value := 0;
3244
3245 l_pre01jul1983_days := 0;
3246 l_post30jun1983_days := 0;
3247
3248 l_etp_pay_tran_no_ppetp := 0;
3249 l_etp_pay_tran_ppetp := 0;
3250 l_etp_pay_no_tran_no_ppetp := 0;
3251 l_etp_pay_no_tran_ppetp := 0;
3252 l_etp_pay_total := 0;
3253
3254 l_old_etp_pay := 0;
3255 l_old_pre01jul1983_value := 0;
3256 l_old_post30jun1983_value := 0;
3257
3258 --------------------------------------------------------------------------------+
3259 -- this procedure gets the ratios to calculate prejul83 balance and postjun83 balance
3260 --------------------------------------------------------------------------------+
3261 IF g_debug THEN
3262 hr_utility.set_location('calling pay_au_terminations.etp_prepost_ratios ',17);
3263 END if;
3264
3265 l_result := pay_au_terminations.etp_prepost_ratios(
3266 p_assignment_id -- number in
3267 ,p_date_start -- date in
3268 ,p_actual_termination_date -- date in
3269 ,'N' -- Bug#2819479 Flag to check whether this function called by Termination Form.
3270 ,l_pre01jul1983_days -- number out
3271 ,l_post30jun1983_days -- number out
3272 ,l_pre01jul1983_ratio -- number out
3273 ,l_post30jun1983_ratio -- number out
3274 ,l_etp_service_date -- date out
3275 ,l_le_etp_service_date); -- date out /* Bug 4177679 */
3276
3277 /*Bug 6192381 Introduced call to pay_balance_pkg.get_value to use BBR for ETP Payment Balances
3278 Removed cursor get_etp_payment_value and its call*/
3279
3280 p_etp_result_table.delete;
3281
3282 p_etp_context_table(1).tax_unit_id := p_registered_employer;
3283 --
3284 pay_balance_pkg.get_value
3285 (p_assignment_action_id => p_max_assignment_action_id
3286 ,p_defined_balance_lst => p_etp_balance_value_tab
3287 ,p_context_lst => p_etp_context_table
3288 ,p_output_table => p_etp_result_table
3289 );
3290
3291 if g_debug then
3292 hr_utility.trace('------------------------------------------------');
3293 hr_utility.trace('ETP Payments Transitional Not Part of Prev Term ===>' || p_etp_result_table(1).balance_value);
3294 hr_utility.trace('ETP Payments Transitional Part of Prev Term ===>' || p_etp_result_table(2).balance_value);
3295 hr_utility.trace('ETP Payments Life Benefit Not Part of Prev Term ===>' || p_etp_result_table(3).balance_value);
3296 hr_utility.trace('ETP Payments Life Benefit Part of Prev Term ===>' || p_etp_result_table(4).balance_value);
3297 hr_utility.trace('Lump Sum C Payments ===>' || p_etp_result_table(5).balance_value);
3298 end if;
3299
3300 tab_etp_bal_name(1) := 'ETP Payments Transitional Not Part of Prev Term';
3301 tab_etp_bal_name(2) := 'ETP Payments Transitional Part of Prev Term';
3302 tab_etp_bal_name(3) := 'ETP Payments Life Benefit Not Part of Prev Term';
3303 tab_etp_bal_name(4) := 'ETP Payments Life Benefit Part of Prev Term';
3304 tab_etp_bal_name(5) := 'Lump Sum C Payments';
3305
3306 l_indx := 1;
3307
3308 for etp_pay_rec in 1..tab_etp_bal_name.count
3309 loop
3310
3311 if g_debug then
3312 hr_utility.trace('Balance Name '||tab_etp_bal_name(etp_pay_rec)|| ' Balance Value '||p_etp_result_table(etp_pay_rec).balance_value);
3313 end if;
3314
3315
3316 if l_result = 0 then
3317 raise e_prepost_error;
3318 else
3319 l_pre01jul1983_value := round(p_etp_result_table(etp_pay_rec).balance_value*l_pre01jul1983_ratio,2); /* Bug 4866415 , Bug 5099419*/
3320 l_post30jun1983_value := round(p_etp_result_table(etp_pay_rec).balance_value*l_post30jun1983_ratio,2); /* Bug 4866415 , Bug 5099419 */
3321
3322 --------------------------------------------------------------------------------+
3323 --populate a pl/sql table with the details of the archive items
3324 --------------------------------------------------------------------------------+
3325
3326 /* Bug 6192381 appending Pre Post values for new balances */
3327
3328 if tab_etp_bal_name(etp_pay_rec) = 'ETP Payments Transitional Not Part of Prev Term' then
3329
3330 l_etp_pay_tran_no_ppetp := p_etp_result_table(etp_pay_rec).balance_value;
3331
3332 tab_prepost_dtls(l_indx).item_name :='X_PRE_JUL_83_COMP_TRANS_NOT_PPTERM_ASG_YTD';
3333 tab_prepost_dtls(l_indx).item_value :=l_pre01jul1983_value;
3334
3335 l_indx := l_indx + 1;
3336
3337 tab_prepost_dtls(l_indx).item_name :='X_POST_JUN_83_TAXED_TRANS_NOT_PPTERM_ASG_YTD';
3338 tab_prepost_dtls(l_indx).item_value :=l_post30jun1983_value;
3339
3340 l_indx := l_indx + 1;
3341
3342 elsif tab_etp_bal_name(etp_pay_rec) = 'ETP Payments Transitional Part of Prev Term' then
3343
3344 l_etp_pay_tran_ppetp := p_etp_result_table(etp_pay_rec).balance_value;
3345
3346 tab_prepost_dtls(l_indx).item_name :='X_PRE_JUL_83_COMP_TRANS_PPTERM_ASG_YTD';
3347 tab_prepost_dtls(l_indx).item_value :=l_pre01jul1983_value;
3348
3349 l_indx := l_indx + 1;
3350
3351 tab_prepost_dtls(l_indx).item_name :='X_POST_JUN_83_TAXED_TRANS_PPTERM_ASG_YTD';
3352 tab_prepost_dtls(l_indx).item_value :=l_post30jun1983_value;
3353
3354 l_indx := l_indx + 1;
3355
3356 elsif tab_etp_bal_name(etp_pay_rec) = 'ETP Payments Life Benefit Not Part of Prev Term' then
3357
3358 l_etp_pay_no_tran_no_ppetp := p_etp_result_table(etp_pay_rec).balance_value;
3359
3360 tab_prepost_dtls(l_indx).item_name :='X_PRE_JUL_83_COMP_NOT_TRANS_NOT_PPTERM_ASG_YTD';
3361 tab_prepost_dtls(l_indx).item_value :=l_pre01jul1983_value;
3362
3363 l_indx := l_indx + 1;
3364
3365 tab_prepost_dtls(l_indx).item_name :='X_POST_JUN_83_TAXED_NOT_TRANS_NOT_PPTERM_ASG_YTD';
3366 tab_prepost_dtls(l_indx).item_value :=l_post30jun1983_value;
3367
3368 l_indx := l_indx + 1;
3369
3370 elsif tab_etp_bal_name(etp_pay_rec) = 'ETP Payments Life Benefit Part of Prev Term' then
3371
3372 l_etp_pay_no_tran_ppetp := p_etp_result_table(etp_pay_rec).balance_value;
3373
3374 tab_prepost_dtls(l_indx).item_name :='X_PRE_JUL_83_COMP_NOT_TRANS_PPTERM_ASG_YTD';
3375 tab_prepost_dtls(l_indx).item_value :=l_pre01jul1983_value;
3376
3377 l_indx := l_indx + 1;
3378
3379 tab_prepost_dtls(l_indx).item_name :='X_POST_JUN_83_TAXED_NOT_TRANS_PPTERM_ASG_YTD';
3380 tab_prepost_dtls(l_indx).item_value :=l_post30jun1983_value;
3381
3382 l_indx := l_indx + 1;
3383
3384 elsif tab_etp_bal_name(etp_pay_rec) = 'Lump Sum C Payments' then
3385
3386 l_etp_pay_total := p_etp_result_table(etp_pay_rec).balance_value;
3387
3388 tab_prepost_dtls(l_indx).item_name :='X_PRE_JUL_83_COMPONENT_ASG_YTD';
3389 tab_prepost_dtls(l_indx).item_value :=l_pre01jul1983_value;
3390
3391 l_indx := l_indx + 1;
3392
3393 tab_prepost_dtls(l_indx).item_name :='X_POST_JUN_83_TAXED_ASG_YTD';
3394 tab_prepost_dtls(l_indx).item_value :=l_post30jun1983_value;
3395
3396 l_indx := l_indx + 1;
3397
3398 tab_prepost_dtls(l_indx).item_name :='X_DAYS_PRE_JUL_83';
3399 tab_prepost_dtls(l_indx).item_value :=l_pre01jul1983_days;
3400
3401 l_indx := l_indx + 1;
3402
3403 tab_prepost_dtls(l_indx).item_name :='X_DAYS_POST_JUL_83';
3404 tab_prepost_dtls(l_indx).item_value :=l_post30jun1983_days;
3405
3406 l_indx := l_indx + 1;
3407
3408 end if;
3409
3410 /* Bug# 2984390 - If ETP service date is entered then X_ETP_EMPLOYEE_START_DATE consists of ETP service
3411 date otherwise Hiredate */
3412 /* Bug 4177679 - If ETP service date is entered then X_ETP_EMPLOYEE_START_DATE consists of ETP service
3413 date otherwise legal employer start date */
3414 /* Bug# 5367061 - If ETP service date is entered then X_ETP_EMPLOYEE_START_DATE consists of ETP service
3415 date otherwise Hiredate, Fix made in 4177679 backed out. */
3416
3417 g_le_etp_flag := 'Y' ;
3418
3419 v_etp_service_date := to_char(l_etp_service_date,'DDMMYYYY');
3420 --------------------------------------------------------------------------------+
3421 -- fetch the user_entity_id for all the archive items and call procedure
3422 -- ff_archive_api.create_archive_item to arhive this value.
3423 --------------------------------------------------------------------------------+
3424 end if;
3425 end loop;
3426
3427 tab_prepost_dtls(l_indx).item_name :='X_POST_JUN_83_UNTAXED_ASG_YTD';
3428 tab_prepost_dtls(l_indx).item_value :=0; /* Bug #2075782 */
3429
3430 create_extract_archive_details(p_assignment_action_id,
3431 'X_ETP_EMPLOYEE_START_DATE',
3432 v_etp_service_date);
3433
3434 /* Begin Bug 6192381 For Termination Payments before Multiple ETP Payment Enhancement the new balances for each combination
3435 does not exist , therefore to find out the values difference of Old balance which has total values and sum of new balances
3436 is taken to archive the old values
3437
3438 This value is then divided in Pre Post ratios and added to corresponding values based on input values Transitional ETP
3439 and Part of Previously Paid ETP .
3440 */
3441
3442 l_old_etp_pay := l_etp_pay_total - ( l_etp_pay_tran_no_ppetp + l_etp_pay_tran_ppetp +
3443 l_etp_pay_no_tran_no_ppetp + l_etp_pay_no_tran_ppetp );
3444
3445 If ( l_old_etp_pay > 0 ) then
3446
3447 l_old_pre01jul1983_value := round(l_old_etp_pay*l_pre01jul1983_ratio,2);
3448 l_old_post30jun1983_value := round(l_old_etp_pay*l_post30jun1983_ratio,2);
3449
3450 If p_transitional_flag ='Y' and p_part_prev_etp_flag = 'N' then
3451 l_item_name_pre := 'X_PRE_JUL_83_COMP_TRANS_NOT_PPTERM_ASG_YTD';
3452 l_item_name_post := 'X_POST_JUN_83_TAXED_TRANS_NOT_PPTERM_ASG_YTD';
3453 End if;
3454
3455 If p_transitional_flag ='Y' and p_part_prev_etp_flag = 'Y' then
3456 l_item_name_pre := 'X_PRE_JUL_83_COMP_TRANS_PPTERM_ASG_YTD';
3457 l_item_name_post := 'X_POST_JUN_83_TAXED_TRANS_PPTERM_ASG_YTD';
3458 End if;
3459
3460 If p_transitional_flag ='N' and p_part_prev_etp_flag = 'N' then
3461 l_item_name_pre := 'X_PRE_JUL_83_COMP_NOT_TRANS_NOT_PPTERM_ASG_YTD';
3462 l_item_name_post := 'X_POST_JUN_83_TAXED_NOT_TRANS_NOT_PPTERM_ASG_YTD';
3463 End if;
3464
3465 If p_transitional_flag ='N' and p_part_prev_etp_flag = 'Y' then
3466 l_item_name_pre := 'X_PRE_JUL_83_COMP_NOT_TRANS_PPTERM_ASG_YTD';
3467 l_item_name_post := 'X_POST_JUN_83_TAXED_NOT_TRANS_PPTERM_ASG_YTD';
3468 End if;
3469
3470 for j in 1..tab_prepost_dtls.COUNT
3471 loop
3472
3473 if tab_prepost_dtls(j).item_name = l_item_name_pre then
3474 tab_prepost_dtls(j).item_value := tab_prepost_dtls(j).item_value + l_old_pre01jul1983_value ;
3475 end if;
3476
3477 if tab_prepost_dtls(j).item_name = l_item_name_post then
3478 tab_prepost_dtls(j).item_value := tab_prepost_dtls(j).item_value + l_old_post30jun1983_value ;
3479 end if;
3480 end loop;
3481
3482 End if; /* End Bug 6192381 */
3483
3484 for cnt in 1..tab_prepost_dtls.COUNT loop
3485 create_extract_archive_details(p_assignment_action_id,
3486 tab_prepost_dtls(cnt).item_name,
3487 tab_prepost_dtls(cnt).item_value);
3488 end loop;
3489
3490
3491 IF g_debug THEN
3492 hr_utility.set_location('End of archive_prepost_details',18);
3493 END if;
3494
3495 exception
3496 when e_prepost_error then
3497 IF g_debug THEN
3498 hr_utility.set_location('error from pay_au_terminations.etp_prepost_ratios',20);
3499 END if;
3500 when others then
3501 IF g_debug THEN
3502 hr_utility.set_location('error in archive_prepost_details',21);
3503 END if;
3504 raise;
3505 end archive_prepost_details;
3506
3507 ---------------------------------------------------------------------------------------+
3508 -- this procedure archives all the data related to employer.
3509 ---------------------------------------------------------------------------------------+
3510
3511 Procedure archive_employer_details
3512 (p_business_group_id in hr_organization_units.business_group_id%type,
3513 p_max_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE, --2610141
3514 p_registered_employer in hr_organization_units.organization_id%type,
3515 p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
3516 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3517 p_assignment_id in pay_assignment_actions.assignment_id%type,
3518 p_year_start in pay_payroll_actions.effective_date%type,
3519 p_year_end in pay_payroll_Actions.effective_date%type) is
3520
3521
3522
3523 l_group_act_no hr_organization_information.org_information1%type;
3524 l_business_name hr_organization_information.org_information3%type;
3525 l_trading_name hr_organization_information.org_information4%type;
3526 l_abn hr_organization_information.org_information12%type;
3527 l_branch_number hr_organization_information.org_information13%type;
3528 l_contact_name hr_organization_information.org_information7%type;
3529 l_tel_number per_addresses.telephone_number_1%type;
3530 l_address_1 hr_locations.address_line_1%type;
3531 l_address_2 hr_locations.address_line_2%type;
3532 l_address_3 hr_locations.address_line_3%type;
3533 l_suburb hr_locations.town_or_city%type;
3534 l_state hr_locations.region_1%type;
3535 l_postcode hr_locations.postal_code%type;
3536 l_country fnd_territories_tl.territory_short_name%type;
3537 l_signatory hr_organization_information.org_information8%type;
3538 e_employer_nf EXCEPTION;
3539
3540
3541 ---------------------------------------------------------------------------------+
3542 -- cursor to get the employer details
3543 -- changed for bug# 1783245
3544 ---------------------------------------------------------------------------------+
3545
3546 cursor employer_details
3547 is
3548 select hoi.org_information1 group_act_no
3549 ,hoi.org_information3 business_name
3550 ,hoi.org_information4 trading_name
3551 ,hoi.org_information12 abn
3552 ,hoi.org_information13 branch_no
3553 ,papcont.first_name || ' ' || papcont.last_name
3554 contact_name
3555 ,hoi.org_information14 telephone_number
3556 ,papsign.first_name || ' ' || papsign.last_name
3557 signatory
3558 ,hlc.address_line_1 address_1
3559 ,hlc.address_line_2 address_2
3560 ,hlc.address_line_3 address_3
3561 ,hlc.town_or_city suburb
3562 ,hlc.region_1 state
3563 ,hlc.postal_code postcode
3564 ,ftl.territory_short_name country
3565 from hr_organization_information hoi
3566 ,hr_locations hlc
3567 ,fnd_territories_tl ftl
3568 ,hr_organization_units hou
3569 ,per_all_people_f papcont
3570 ,per_all_people_f papsign
3571 where hou.business_group_id = p_business_group_id
3572 and hou.organization_id = p_registered_employer
3573 and hou.organization_id = hoi.organization_id
3574 and hoi.org_information_context = 'AU_LEGAL_EMPLOYER'
3575 and ftl.territory_code = hlc.country
3576 and ftl.language = userenv('LANG')
3577 and hlc.location_id = hou.location_id
3578 and papcont.person_id = hoi.org_information7
3579 and papcont.effective_start_date = (select max(effective_start_date)
3580 from per_all_people_f p
3581 where papcont.person_id=p.person_id)
3582 and papsign.person_id = hoi.org_information8
3583 and papsign.effective_start_date = (select max(effective_start_date)
3584 from per_all_people_f p
3585 where papsign.person_id=p.person_id);
3586
3587
3588 Begin
3589 IF g_debug THEN
3590 hr_utility.set_location('Start of archive employer details ..',0);
3591 END if;
3592 open employer_details ;
3593 fetch employer_details into
3594 l_group_act_no
3595 ,l_business_name
3596 ,l_trading_name
3597 ,l_abn
3598 ,l_branch_number
3599 ,l_contact_name
3600 ,l_tel_number
3601 ,l_signatory
3602 ,l_address_1
3603 ,l_address_2
3604 ,l_address_3
3605 ,l_suburb
3606 ,l_state
3607 ,l_postcode
3608 ,l_country ;
3609 if employer_details%found then
3610 close employer_details;
3611 IF g_debug THEN
3612 hr_utility.set_location('Creating archive Item X_EMPLOYER_BUSINESS_NAME',1);
3613 END if;
3614 create_extract_archive_details(p_assignment_action_id,
3615 'X_EMPLOYER_BUSINESS_NAME',
3616 l_business_name);
3617 IF g_debug THEN
3618 hr_utility.set_location('Creating archive Item X_EMPLOYER_GROUP_ACT_NO',2);
3619 END if;
3620 create_extract_archive_details(p_assignment_action_id,
3621 'X_EMPLOYER_GROUP_ACT_NO',
3622 l_group_act_no);
3623 IF g_debug THEN
3624 hr_utility.set_location('Creating archive Item X_EMPLOYER_TRADING_NAME',3);
3625 END if;
3626 create_extract_archive_details(p_assignment_action_id,
3627 'X_EMPLOYER_TRADING_NAME',
3628 l_trading_name);
3629 IF g_debug THEN
3630 hr_utility.set_location('Creating archive Item X_EMPLOYER_BRANCH_NUMBER',114);
3631 END if;
3632 create_extract_archive_details(p_assignment_action_id,
3633 'X_EMPLOYER_BRANCH_NUMBER',
3634 l_branch_number);
3635 IF g_debug THEN
3636 hr_utility.set_location('Creating archive Item X_EMPLOYER_ABN',114);
3637 END if;
3638 create_extract_archive_details(p_assignment_action_id,
3639 'X_EMPLOYER_ABN',
3640 l_abn);
3641 IF g_debug THEN
3642 hr_utility.set_location('Creating archive Item X_EMPLOYER_CONTACT_NAME',5);
3643 END if;
3644 create_extract_archive_details(p_assignment_action_id,
3645 'X_EMPLOYER_CONTACT_NAME',
3646 l_contact_name);
3647 IF g_debug THEN
3648 hr_utility.set_location('Creating archive Item X_EMPLOYER_CONTACT_TELEPHONE',6);
3649 END if;
3650 create_extract_archive_details(p_assignment_action_id,
3651 'X_EMPLOYER_CONTACT_TELEPHONE',
3652 l_tel_number);
3653 IF g_debug THEN
3654 hr_utility.set_location('Creating archive Item X_EMPLOYER_ADDRESS_1',7);
3655 END if;
3656 create_extract_archive_details(p_assignment_action_id,
3657 'X_EMPLOYER_ADDRESS_1',
3658 l_address_1);
3659 IF g_debug THEN
3660 hr_utility.set_location('Creating archive Item X_EMPLOYER_ADDRESS_2',8);
3661 END if;
3662 create_extract_archive_details(p_assignment_action_id,
3663 'X_EMPLOYER_ADDRESS_2',
3664 l_address_2);
3665 IF g_debug THEN
3666 hr_utility.set_location('Creating archive Item X_EMPLOYER_ADDRESS_3',8);
3667 END if;
3668 create_extract_archive_details(p_assignment_action_id,
3669 'X_EMPLOYER_ADDRESS_3',
3670 l_address_3);
3671 IF g_debug THEN
3672 hr_utility.set_location('Creating archive Item X_EMPLOYER_SUBURB',9);
3673 END if;
3674 create_extract_archive_details(p_assignment_action_id,
3675 'X_EMPLOYER_SUBURB',
3676 l_suburb);
3677 IF g_debug THEN
3678 hr_utility.set_location('Creating archive Item X_EMPLOYER_STATE',10);
3679 END if;
3680 create_extract_archive_details(p_assignment_action_id,
3681 'X_EMPLOYER_STATE',
3682 l_state);
3683 IF g_debug THEN
3684 hr_utility.set_location('Creating archive Item X_EMPLOYER_POSTCODE',11);
3685 END if;
3686 create_extract_archive_details(p_assignment_action_id,
3687 'X_EMPLOYER_POSTCODE',
3688 l_postcode);
3689 IF g_debug THEN
3690 hr_utility.set_location('Creating archive Item X_EMPLOYER_COUNTRY',12);
3691 END if;
3692 create_extract_archive_details(p_assignment_action_id,
3693 'X_EMPLOYER_COUNTRY',
3694 l_country);
3695 --------------------------------
3696 -- Added for Bug No :1789886
3697 --------------------------------
3698 IF g_debug THEN
3699 hr_utility.set_location('Creating archive Item X_PAYMENT_SUMMARY_SIGNATORY',12);
3700 END if;
3701 create_extract_archive_details(p_assignment_action_id,
3702 'X_PAYMENT_SUMMARY_SIGNATORY',
3703 l_signatory);
3704
3705 --------------------------------
3706 -- Added for Bug No :1764017
3707 --------------------------------
3708 archive_union_name
3709 (p_assignment_id
3710 ,p_assignment_action_id
3711 ,p_max_assignment_action_id --2610141
3712 ,p_registered_employer --2610141
3713 ,p_year_start
3714 ,p_year_end
3715 );
3716 else
3717 close employer_details;
3718 raise e_employer_nf;
3719 end if;
3720 exception
3721 when e_employer_nf then
3722 IF g_debug THEN
3723 hr_utility.set_location('No employer Details found for the assigment id ',20);
3724 END if;
3725 when others then
3726 IF g_debug THEN
3727 hr_utility.set_location('Error in archive_employer_details ',99);
3728 END if;
3729 raise;
3730 End archive_employer_details ;
3731
3732 ---------------------------------------------------------------------------------------+
3733 -- this procedure archives all the data related to employee.
3734 ---------------------------------------------------------------------------------------+
3735
3736 Procedure archive_employee_details
3737 (p_business_group_id in hr_organization_units.business_group_id%type,
3738 p_registered_employer in hr_organization_units.organization_id%type,
3739 p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
3740 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3741 p_assignment_id in pay_assignment_actions.assignment_id%type,
3742 p_year_start in pay_payroll_actions.effective_date%type,
3743 p_year_end in pay_payroll_Actions.effective_date%type,
3744 p_end_date_flag in varchar2,
3745 p_fbt_year_start IN pay_payroll_Actions.effective_date%type) is /*Bug# 4653934*/
3746
3747 l_tfn_no pay_element_entry_values_f.screen_entry_value%type;
3748 l_first_name per_all_people_f.first_name%type;
3749 l_middle_name per_all_people_f.middle_names%type;
3750 l_surname per_all_people_f.last_name%type;
3751 l_address_1 hr_locations.address_line_1%type;
3752 l_address_2 hr_locations.address_line_2%type;
3753 l_address_3 hr_locations.address_line_3%type;
3754 l_suburb hr_locations.town_or_city%type;
3755 l_state hr_locations.region_1%type;
3756 l_postcode hr_locations.postal_code%type;
3757 l_country fnd_territories_tl.territory_short_name%type;
3758 l_start_date VARCHAR2(20);
3759 l_termination_date VARCHAR2(20);
3760 l_dob VARCHAR2(20);
3761 l_asgmnt_loc hr_locations.location_code%type;
3762 l_emp_no per_all_people_f.employee_number%type;
3763 l_payroll pay_all_payrolls_f.payroll_name%type;
3764 l_emp_type per_all_people_f.current_employee_flag%type;
3765 l_address_date_from date;
3766 l_date_earned pay_payroll_actions.date_earned%type;
3767 l_effective_date date; -- Bug3263659
3768 l_actual_termination_date per_periods_of_service.actual_termination_date%type;
3769 l_final_process_date date; -- Bug3263659
3770 l_fpd_archive VARCHAR2(20); -- Bug3098353
3771 l_le_start_date VARCHAR2(20);
3772 l_le_end_date VARCHAR2(20); -- Bug 2610141
3773
3774 l_address_style per_addresses.style%type; -- Bug 5364017
3775 l_add_country per_addresses.country%type; -- Bug 5364017
3776
3777 e_employee_nf EXCEPTION;
3778
3779 l_le_etp_service_date varchar2(20); /* Bug 4177679 */
3780 l_le_etp_start_date varchar2(20); /*Bug 5075662 */
3781
3782 ---------------------------------------------------------------------------------+
3783 -- cursor to get the employee details
3784 ---------------------------------------------------------------------------------+
3785
3786 /* Bug 1973978 -- If the Termination date of the employee is greater than the last date of the current financial
3787 year then the employee should be treated as an Current Employee */
3788 /* Bug 2512431 -- Removed to_date for p_year_end in the select statement for emp_type */
3789 /* Bug 2977533 - When a new address is created thro SS applications ,2 rows are created in
3790 per_addresses table for the same primary address;the previous address is end dated.
3791 This can also be simulated thro apps if a primary addresses is end dated and a new
3792 primary address is created after the end date
3793 The cursor fetches 2 rows and picks the old address first.To get the latest address
3794 order by clause has been added to pick the latest row first */
3795
3796
3797 /*Bug3019374 - Cursor employee_details broken up into 2 cursor -> cursor employee_details
3798 and cursor tfn_number*/
3799
3800 cursor tfn_number(c_assignment_id per_all_assignments.assignment_id%TYPE,
3801 c_date_earned pay_payroll_actions.date_earned%TYPE)
3802 is
3803 select pev.screen_entry_value tfn_no
3804 from
3805 pay_element_types_f pet,
3806 pay_input_values_f piv,
3807 pay_element_links_f pel,
3808 pay_element_entries_f pee,
3809 pay_element_entry_values_f pev
3810 where pet.element_name = 'Tax Information'
3811 and piv.name = 'Tax File Number'
3812 and pet.element_type_id = piv.element_type_id
3813 and pet.element_type_id = pel.element_type_id
3814 and pel.element_link_id = pee.element_link_id
3815 and pee.element_entry_id = pev.element_entry_id
3816 and piv.input_value_id = pev.input_value_id
3817 and pee.assignment_id = c_assignment_id
3818 and c_date_earned between pel.effective_start_date and pel.effective_end_date
3819 and c_date_earned between pev.effective_start_date and pev.effective_end_date
3820 and c_date_earned between pet.effective_start_date and pet.effective_end_date
3821 and c_date_earned between pee.effective_start_date and pee.effective_end_date
3822 and c_date_earned between piv.effective_start_date and piv.effective_end_date;
3823
3824
3825 /*Bug3019374 - Cursor broken up into 2 cursor -> cursor employee_details
3826 and cursor tfn_number*/
3827 /* Bug 4299506 - Join in subquery modified for Archiving details of employees
3828 terminated in previous years */
3829 /* Bug 7242551 - Modified sub query to fetch date earned based on max(action_sequence)
3830 and not max(payroll_action_id)
3831 */
3832
3833 cursor employee_details
3834 is
3835 select distinct
3836 pap.first_name first_name
3837 ,substr(pap.middle_names, 1,
3838 decode(instr(pap.middle_names,' '),
3839 0, 60, instr(pap.middle_names,'',1)-1)) middle_name
3840 ,pap.last_name surname
3841 ,pad.address_line1 address_1
3842 ,pad.address_line2 address_2
3843 ,pad.address_line3 address_3
3844 ,pad.town_or_city suburb
3845 ,pad.region_1 state
3846 ,pad.postal_code postcode
3847 ,fta.territory_short_name country
3848 ,pad.style address_style -- Bug 5364017
3849 ,pad.country address_country -- Bug 5364017
3850 ,to_char(pps.date_start,'DDMMYYYY') start_date
3851 ,nvl(to_char(pps.actual_termination_date,
3852 'DDMMYYYY'),'31124712') termination_date
3853 ,pps.final_process_date final_process_date -- Bug3263659
3854 ,to_char(pap.date_of_birth,'DDMMYYYY') dob
3855 ,hlc.location_code asgmnt_loc
3856 ,pap.employee_number emp_no
3857 ,decode(pps.actual_termination_date,null,'C',decode(sign(pps.actual_termination_date - p_year_end),1,'C','T')) emp_type /* Bug #1973978 */
3858 ,pad.date_from
3859 ,ppa1.date_earned
3860 ,ppa1.effective_date -- Bug3263659
3861 ,pps.actual_termination_date
3862 ,to_char(paaf.effective_start_date,'DDMMYYYY')
3863 ,to_char(paa.effective_end_date,'DDMMYYYY') -- Bug 2610141
3864 from hr_organization_information hoi,
3865 hr_organization_units hou,
3866 hr_soft_coding_keyflex hsc,
3867 hr_locations hlc, /* Bug No : 2263587 */
3868 per_all_assignments_f paa,
3869 per_all_assignments_f paaf, /* Bug : 2610141 */
3870 per_all_people_f pap,
3871 per_addresses pad,
3872 fnd_territories_tl fta,
3873 per_periods_of_service pps,
3874 pay_payroll_actions ppa,
3875 pay_assignment_actions pac,
3876 pay_payroll_actions ppa1, /* Bug# 2448441 */
3877 pay_assignment_actions pac1 /* Bug# 2448441 */
3878 where hou.business_group_id = p_business_group_id
3879 and hou.organization_id = p_registered_employer
3880 and ppa.action_type = 'X'
3881 and hou.organization_id = hoi.organization_id
3882 and hoi.org_information_context = 'AU_LEGAL_EMPLOYER'
3883 and hou.business_group_id = pap.business_group_id
3884 and hsc.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
3885 and paa.location_id = hlc.location_id(+)
3886 and to_char(hou.organization_id)= hsc.segment1
3887 and pap.person_id = paa.person_id
3888 and pap.person_id = pad.person_id(+)
3889 and pad.primary_flag(+) = 'Y' /*Added for bug 2774577*/
3890 and fta.territory_code(+) = pad.country
3891 and fta.language(+) = userenv('LANG')
3892 and pap.person_id = pps.person_id
3893 and pps.period_of_service_id = paa.period_of_service_id /* Bug#2786146 */
3894 and ppa.payroll_action_id = pac.payroll_action_id
3895 and ppa.payroll_action_id = p_payroll_action_id
3896 and paa.assignment_id = pac.assignment_id
3897 and paa.assignment_id = p_assignment_id
3898 and paaf.assignment_id = paa.assignment_id -- Bug 2610141
3899 /* Added for Bug# 2448441 */
3900 and paa.assignment_id = pac1.assignment_id
3901 and ppa1.payroll_action_id = pac1.payroll_action_id
3902 and pac1.assignment_action_id = (select to_number(substr(max(lpad(paa2.action_sequence,15,'0')||paa2.assignment_action_id),16)) /*Bug 7242551 */
3903 from pay_payroll_actions ppa2,
3904 pay_assignment_actions paa2
3905 where ppa2.action_type in ('R','Q','B','I') --Bug 2574186
3906 and ppa2.payroll_action_id = paa2.payroll_action_id
3907 and paa2.tax_unit_id = p_registered_employer -- Bug 2610141
3908 and paa2.assignment_id = paa.assignment_id
3909 and ppa2.effective_date between add_months(p_year_start,-3) and p_year_end )/*Bug3048962 */
3910 /* End of Bug# 2448441 */
3911 and (paa.effective_start_date, paaf.effective_start_date)
3912 = (select max(a.effective_Start_date),min(a.effective_start_date) -- Bug 2610141
3913 from per_all_assignments_f a
3914 , hr_soft_coding_keyflex hsc1 --Added for bug 4177679
3915 where a.assignment_id = paa.assignment_id
3916 and hsc1.soft_coding_keyflex_id = a.soft_coding_keyflex_id --Added for bug 4177679
3917 and hsc1.segment1= p_registered_employer --Added for bug 4177679
3918 and nvl(pps.actual_termination_date,p_year_end)
3919 between a.effective_Start_date and pap.effective_end_date /*2689175*/
3920 and a.effective_end_date >= least(nvl(pps.actual_termination_date,p_year_start),p_year_start))--Added for bug 4177679,4299506
3921 and pap.effective_start_date = (select max(effective_Start_date)
3922 from per_all_people_f p
3923 where p.person_id = pap.person_id
3924 and nvl(pps.actual_termination_date,p_year_end) between p.effective_Start_date and p.effective_end_date)
3925 ORDER BY pad.date_from DESC;/*Bug2977533*/
3926
3927
3928 /*Bug# 4653934 - Introduced a new cursor to get the payroll name for the employee. This has been done to take care of cases
3929 where assignment has payroll attached to it for few months but is not attached at the end of year*/
3930 CURSOR c_get_payroll_name
3931 IS
3932 SELECT pay.payroll_name
3933 FROM per_all_assignments_f paaf,
3934 pay_payrolls_f pay
3935 WHERE paaf.assignment_id = p_assignment_id
3936 and paaf.effective_end_date = (select max(effective_end_date)
3937 From per_assignments_f iipaf
3938 WHERE iipaf.assignment_id = p_assignment_id
3939 and iipaf.effective_end_date >= p_fbt_year_start
3940 and iipaf.effective_start_date <= p_year_end
3941 AND iipaf.payroll_id IS NOT NULL)
3942 AND pay.payroll_id = paaf.payroll_id
3943 AND paaf.effective_end_date BETWEEN pay.effective_start_date AND pay.effective_end_date;
3944
3945
3946 Begin
3947
3948 IF g_debug THEN
3949 hr_utility.set_location('Start of archive employee details ..',0);
3950 END if;
3951 open employee_details ;
3952 fetch employee_details into
3953 l_first_name
3954 ,l_middle_name
3955 ,l_surname
3956 ,l_address_1
3957 ,l_address_2
3958 ,l_address_3
3959 ,l_suburb
3960 ,l_state
3961 ,l_postcode
3962 ,l_country
3963 ,l_address_style -- Bug 5364017
3964 ,l_add_country -- Bug 5364017
3965 ,l_start_date
3966 ,l_termination_date
3967 ,l_final_process_date -- Bug3263659
3968 ,l_dob
3969 ,l_asgmnt_loc
3970 ,l_emp_no
3971 -- ,l_payroll
3972 ,l_emp_type
3973 ,l_address_date_from
3974 ,l_date_earned
3975 ,l_effective_date -- Bug3263659
3976 ,l_actual_termination_date
3977 ,l_le_start_date
3978 ,l_le_end_date; -- Bug 2610141
3979
3980
3981 OPEN c_get_payroll_name;
3982 FETCH c_get_payroll_name INTO l_payroll;
3983 CLOSE c_get_payroll_name;
3984
3985 IF g_debug THEN
3986 hr_utility.set_location('In Archive_employee_details ',1000);
3987 hr_utility.set_location('l_first_name '||l_first_name,1000);
3988 hr_utility.set_location('l_surname '||l_surname,1000);
3989 hr_utility.set_location('l_address_1 '||l_address_1,1000);
3990 hr_utility.set_location('l_address_style '||l_address_style,1000);
3991 hr_utility.set_location('l_start_date '||l_start_date,1000);
3992 hr_utility.set_location('l_termination_date '||l_termination_date,1000);
3993 hr_utility.set_location('l_final_process_date '||l_final_process_date,1000);
3994 hr_utility.set_location('l_emp_no '||l_emp_no,1000);
3995 hr_utility.set_location('l_emp_type '||l_emp_type,1000);
3996 hr_utility.set_location('l_date_earned '||l_date_earned,1000);
3997 hr_utility.set_location('l_effective_date '||l_effective_date,1000);
3998 hr_utility.set_location('l_actual_termination_date '||l_actual_termination_date,1000);
3999 hr_utility.set_location('l_le_start_date '||l_le_start_date,1000);
4000 hr_utility.set_location('l_le_end_date '||l_le_end_date,1000);
4001 END if;
4002
4003 if employee_details%found then
4004 close employee_details;
4005
4006 /* - If the Employee is terminated in the current financial year then get the tax file number
4007 at the time of termination.
4008
4009 Else use the date_earned of the last payroll action to get the tax file number Bug3019374
4010
4011 */
4012
4013 /* Bug3263659 - If termination date is in the last year then use final process date , otherwise current logic */
4014 /* Bug 7234263 - Added Least for end dated assignments where there may be no assignment record on Act Term Date */
4015
4016 if l_emp_type = 'T' then
4017 if l_actual_termination_date < p_year_start AND l_effective_date >= p_year_start
4018 then
4019 l_actual_termination_date :=nvl(l_final_process_date,l_effective_date);/* Bug 3098353 To set the End Date as Payment Date if Final_process is null */
4020 l_termination_date:= to_char(l_actual_termination_date,'DDMMYYYY');
4021 end if;
4022 l_date_earned:=least(l_actual_termination_date,l_date_earned); /* Bug 7234263*/
4023 end if;
4024 /* End of Changes for Bug3263659 */
4025
4026 l_le_etp_start_date := l_le_start_date; /*Bug 5075662 - Variable introduced to store the actual le start date which
4027 will be archived as X_ETP_EMPLOYEE_START_DATE if ETP service date is null*/
4028 ----------------------------2610141--------------------------------
4029 IF to_date(l_le_start_date,'DDMMYYYY') < p_year_start then
4030 l_le_start_date := to_char(p_year_start,'DDMMYYYY');
4031 ELSE
4032 l_le_start_date := l_le_start_date;
4033 END IF;
4034
4035 IF to_date(l_le_end_date,'DDMMYYYY') > p_year_end THEN /*Bug 4063321*/
4036 l_le_end_date := to_char(p_year_end,'DDMMYYYY');
4037 ELSE
4038 l_le_end_date := l_le_end_date;
4039 END IF;
4040 ----------------------------------------------------------------
4041
4042 IF g_debug THEN
4043 hr_utility.set_location('In Archive_employee_details, after adjsutments ',1000);
4044 hr_utility.set_location('l_termination_date '||l_termination_date,1000);
4045 hr_utility.set_location('l_final_process_date '||l_final_process_date,1000);
4046 hr_utility.set_location('l_date_earned '||l_date_earned,1000);
4047 hr_utility.set_location('l_effective_date '||l_effective_date,1000);
4048 hr_utility.set_location('l_actual_termination_date '||l_actual_termination_date,1000);
4049 hr_utility.set_location('l_le_start_date '||l_le_start_date,1000);
4050 hr_utility.set_location('l_le_end_date '||l_le_end_date,1000);
4051 END if;
4052
4053
4054 open tfn_number(p_assignment_id,l_date_earned); /*Bug3019374*/
4055 fetch tfn_number into l_tfn_no;
4056
4057 IF g_debug THEN
4058 hr_utility.set_location('tfn_number '||l_tfn_no,1000);
4059 END IF;
4060
4061
4062 If tfn_number%FOUND then /*If Tax File details found then archive employe details*/
4063 close tfn_number;
4064
4065 IF g_debug THEN
4066 hr_utility.set_location('Creating archive Item X_EMPLOYEE_TAX_FILE_NUMBER',1);
4067 END if;
4068 create_extract_archive_details(p_assignment_action_id,
4069 'X_EMPLOYEE_TAX_FILE_NUMBER',
4070 l_tfn_no);
4071
4072 IF g_debug THEN
4073 hr_utility.set_location('Creating archive Item X_EMPLOYEE_FIRST_NAME',3);
4074 END if;
4075 create_extract_archive_details(p_assignment_action_id,
4076 'X_EMPLOYEE_FIRST_NAME',
4077 l_first_name);
4078 IF g_debug THEN
4079 hr_utility.set_location('Creating archive Item X_EMPLOYEE_MIDDLE_NAME',4);
4080 END if;
4081 create_extract_archive_details(p_assignment_action_id,
4082 'X_EMPLOYEE_MIDDLE_NAME',
4083 l_middle_name);
4084 IF g_debug THEN
4085 hr_utility.set_location('Creating archive Item X_EMPLOYEE_SURNAME',5);
4086 END if;
4087 create_extract_archive_details(p_assignment_action_id,
4088 'X_EMPLOYEE_SURNAME',
4089 l_surname);
4090 IF g_debug THEN
4091 hr_utility.set_location('Creating archive Item X_EMPLOYEE_ADDRESS_1',7);
4092 END if;
4093 create_extract_archive_details(p_assignment_action_id,
4094 'X_EMPLOYEE_ADDRESS_1',
4095 l_address_1);
4096 IF g_debug THEN
4097 hr_utility.set_location('Creating archive Item X_EMPLOYEE_ADDRESS_2',8);
4098 END if;
4099 create_extract_archive_details(p_assignment_action_id,
4100 'X_EMPLOYEE_ADDRESS_2',
4101 l_address_2);
4102 IF g_debug THEN
4103 hr_utility.set_location('Creating archive Item X_EMPLOYEE_ADDRESS_3',8);
4104 END if;
4105 create_extract_archive_details(p_assignment_action_id,
4106 'X_EMPLOYEE_ADDRESS_3',
4107 l_address_3);
4108 IF g_debug THEN
4109 hr_utility.set_location('Creating archive Item X_EMPLOYEE_SUBURB',9);
4110 END if;
4111 create_extract_archive_details(p_assignment_action_id,
4112 'X_EMPLOYEE_SUBURB',
4113 l_suburb);
4114 IF g_debug THEN
4115 hr_utility.set_location('Creating archive Item X_EMPLOYEE_STATE',10);
4116 END if;
4117 create_extract_archive_details(p_assignment_action_id,
4118 'X_EMPLOYEE_STATE',
4119 l_state);
4120 IF g_debug THEN
4121 hr_utility.set_location('Creating archive Item X_EMPLOYEE_POSTCODE',11);
4122 END if;
4123 create_extract_archive_details(p_assignment_action_id,
4124 'X_EMPLOYEE_POSTCODE',
4125 l_postcode);
4126
4127 /* Bug 5364017 - Check for Generic Address
4128 IF Address Style is Generic - then get the country name as entered in Address form
4129 */
4130 IF l_address_style = 'GENERIC'
4131 THEN
4132 l_country := l_add_country;
4133 END IF;
4134
4135 IF g_debug THEN
4136 hr_utility.set_location('Creating archive Item X_EMPLOYEE_COUNTRY',12);
4137 END if;
4138 create_extract_archive_details(p_assignment_action_id,
4139 'X_EMPLOYEE_COUNTRY',
4140 l_country);
4141 IF g_debug THEN
4142 hr_utility.set_location('Creating archive Item X_EMPLOYEE_START_DATE',12);
4143 END if;
4144 create_extract_archive_details(p_assignment_action_id,
4145 'X_EMPLOYEE_START_DATE',
4146 l_start_date);
4147
4148 /* 3098353 Employee End Date Archived in main Archive_Code */
4149
4150 IF g_debug THEN
4151 hr_utility.set_location('Creating archive Item X_EMPLOYEE_DATE_OF_BIRTH',12);
4152 END if;
4153 create_extract_archive_details(p_assignment_action_id,
4154 'X_EMPLOYEE_DATE_OF_BIRTH',
4155 l_dob);
4156 /* bug 3098353 */
4157 IF g_debug THEN
4158 hr_utility.set_location('Creating archive Item X_EMPLOYEE_FINAL_PROCESS_DATE',12);
4159 END if;
4160 l_fpd_archive := to_char(l_final_process_date,'DDMMYYYY');
4161 create_extract_archive_details(p_assignment_action_id,
4162 'X_EMPLOYEE_FINAL_PROCESS_DATE',
4163 l_fpd_archive);
4164 ---------------------------------------------------------------
4165 -- archive sort details
4166 ---------------------------------------------------------------
4167 IF g_debug THEN
4168 hr_utility.set_location('Creating archive Item X_SORT_EMPLOYEE_NUMBER',12);
4169 END if;
4170 create_extract_archive_details(p_assignment_action_id,
4171 'X_SORT_EMPLOYEE_NUMBER',
4172 l_emp_no );
4173 IF g_debug THEN
4174 hr_utility.set_location('Creating archive Item X_SORT_ASSIGNMENT_LOCATION',12);
4175 END if;
4176 create_extract_archive_details(p_assignment_action_id,
4177 'X_SORT_ASSIGNMENT_LOCATION',
4178 l_asgmnt_loc );
4179 IF g_debug THEN
4180 hr_utility.set_location('Creating archive Item X_SORT_PAYROLL',12);
4181 END if;
4182 create_extract_archive_details(p_assignment_action_id,
4183 'X_SORT_PAYROLL',
4184 l_payroll );
4185 IF g_debug THEN
4186 hr_utility.set_location('Creating archive Item X_SORT_EMPLOYEE_TYPE',12);
4187 END if;
4188 create_extract_archive_details(p_assignment_action_id,
4189 'X_SORT_EMPLOYEE_TYPE',
4190 l_emp_type );
4191 IF g_debug THEN
4192 hr_utility.set_location('Creating archive Item X_SORT_EMPLOYEE_LAST_NAME',5);
4193 END if;
4194 create_extract_archive_details(p_assignment_action_id,
4195 'X_SORT_EMPLOYEE_LAST_NAME',
4196 l_surname);
4197
4198
4199 ----------------------------2610141--------------------------------
4200 IF g_debug THEN
4201 hr_utility.set_location('Creating archive Item X_EMPLOYEE_LE_START_DATE',5);
4202 END if;
4203 l_le_etp_service_date := l_le_etp_start_date; /* Bug 4177679, Bug# 5075662 */
4204
4205 /*Bug# 4363057 - If condition introduced so that the X_EMPLOYEE_LE_START_DATE gets archived only once.
4206 If for submitted legal employer employee is not active in the financial year then
4207 X_EMPLOYEE_LE_START_DATE is archived in the archive_code*/
4208
4209 IF p_end_date_flag <> 'B' THEN
4210 create_extract_archive_details(p_assignment_action_id,
4211 'X_EMPLOYEE_LE_START_DATE',
4212 l_le_start_date);
4213 END IF;
4214 /* Bug 4177679 ETP start date to be archived */
4215 if g_le_etp_flag ='N' then
4216 create_extract_archive_details(p_assignment_action_id,
4217 'X_ETP_EMPLOYEE_START_DATE',
4218 l_le_etp_service_date);
4219 IF g_debug THEN
4220 hr_utility.set_location('End of archive_prepost_details',18);
4221 END if;
4222 g_le_etp_flag := 'Y';
4223 end if ;
4224
4225
4226 IF g_debug THEN
4227 hr_utility.set_location('Creating archive Item X_EMPLOYEE_LE_END_DATE',5);
4228 END if;
4229
4230 IF p_end_date_flag = 'N' THEN
4231 create_extract_archive_details(p_assignment_action_id,
4232 'X_EMPLOYEE_LE_END_DATE',
4233 l_le_end_date);
4234 END IF;
4235 ----------------------------2610141--------------------------------
4236 end if;
4237
4238 else
4239 close employee_details;
4240 raise e_employee_nf;
4241 end if;
4242 exception
4243 when e_employee_nf then
4244 IF g_debug THEN
4245 hr_utility.set_location('No employee Details found for the assigment id ',20);
4246 END if;
4247 when others then
4248 IF g_debug THEN
4249 hr_utility.set_location('Error in archive_employee_details ',99);
4250 END if;
4251 raise;
4252 End archive_employee_details;
4253
4254
4255 ------------------------------------------------------------------------+
4256 -- procedure to archive supplier details
4257 -- calls create_extract_archive_Details
4258 ------------------------------------------------------------------------+
4259 Procedure archive_supplier_details
4260 (p_business_group_id in hr_organization_units.business_group_id%type,
4261 p_registered_employer in hr_organization_units.organization_id%type,
4262 p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
4263 p_assignment_Action_id in pay_assignment_actions.assignment_action_id%type,
4264 p_assignment_id in pay_assignment_actions.assignment_id%type,
4265 p_year_start in pay_payroll_actions.effective_date%type,
4266 p_year_end in pay_payroll_Actions.effective_date%type) is
4267
4268
4269 l_report_end_date varchar2(20);
4270 l_supplier_number hr_organization_information.org_information1%type;
4271 l_supplier_name hr_organization_information.org_information3%type;
4272 l_supplier_abn hr_organization_information.org_information5%type;
4273 l_supplier_contact_name per_all_people_f.full_name%type;
4274 l_supplier_contact_phone per_addresses.telephone_number_1%type;
4275 l_supplier_address_1 hr_locations.address_line_1%type;
4276 l_supplier_address_2 hr_locations.address_line_2%type;
4277 l_supplier_address_3 hr_locations.address_line_3%type;
4278 l_supplier_suburb hr_locations.town_or_city%type;
4279 l_supplier_state hr_locations.region_1%type;
4280 l_supplier_postcode hr_locations.postal_code%type;
4281 l_supplier_country fnd_territories_tl.territory_short_name%type;
4282
4283 ------------------------------------------------------------------------------+
4284 -- cursor to get supplier details
4285 ------------------------------------------------------------------------------+
4286
4287 cursor supplier_details
4288 is
4289 select '3006' ||to_char(p_year_end,'YYYY') report_end_date
4290 ,hoi.org_information1 supplier_number
4291 ,hoi.org_information3 supplier_name
4292 ,hoi.org_information12 supplier_abn
4293 ,pap.first_name || ' ' || pap.last_name supplier_contact_name
4294 ,hoi.org_information14 supplier_contact_phone
4295 ,hrl.address_line_1 supplier_address_1
4296 ,hrl.address_line_2 supplier_address_2
4297 ,hrl.address_line_3 supplier_address_3
4298 ,hrl.town_or_city supplier_suburb
4299 ,hrl.region_1 supplier_state
4300 ,hrl.postal_code supplier_postcode
4301 ,ftl.territory_short_name supplier_country
4302 ,pap.email_address email_address
4303 from hr_organization_information hoi
4304 ,hr_organization_units hou
4305 ,hr_locations hrl
4306 ,fnd_territories_tl ftl
4307 ,per_all_people_f pap
4308 where hou.business_group_id = p_business_group_id
4309 and hou.organization_id = p_registered_employer
4310 and hou.organization_id = hoi.organization_id
4311 and hoi.org_information_context = 'AU_LEGAL_EMPLOYER'
4312 and hrl.location_id = hou.location_id
4313 and ftl.territory_code = hrl.country
4314 and ftl.language = userenv('LANG')
4315 and hoi.org_information7 = pap.person_id
4316 and pap.effective_start_date = (select max(effective_start_date)
4317 from per_all_people_f p
4318 where pap.person_id=p.person_id);
4319
4320 Begin
4321 IF g_debug THEN
4322 hr_utility.set_location('Archiving Supplier Details ',1);
4323 hr_utility.set_location('Assignments action id is '||p_assignment_action_id,2);
4324 END if;
4325
4326
4327 for sd in supplier_details
4328 loop
4329
4330 IF g_debug THEN
4331 hr_utility.set_location('Creating Archive Item X_REPORT_END_DATE',3);
4332 END if;
4333 create_extract_archive_details (p_assignment_action_id
4334 ,'X_REPORT_END_DATE'
4335 ,sd.report_end_date);
4336
4337 IF g_debug THEN
4338 hr_utility.set_location('Creating archive Item X_SUPPLIER_NUMBER',3);
4339 END if;
4340 create_extract_archive_details(p_assignment_action_id,
4341 'X_SUPPLIER_NUMBER',
4342 sd.supplier_number);
4343 IF g_debug THEN
4344 hr_utility.set_location('Creating archive Item X_SUPPLIER_NAME',3);
4345 END if;
4346 create_extract_archive_details(p_assignment_action_id,
4347 'X_SUPPLIER_NAME',
4348 sd.supplier_name);
4349 IF g_debug THEN
4350 hr_utility.set_location('Creating archive Item X_SUPPLIER_ABN',3);
4351 END if;
4352 create_extract_archive_details(p_assignment_action_id,
4353 'X_SUPPLIER_ABN',
4354 sd.supplier_abn);
4355 IF g_debug THEN
4356 hr_utility.set_location('Creating archive Item X_SUPPLIER_CONTACT_NAME',3);
4357 END if;
4358 create_extract_archive_details(p_assignment_action_id,
4359 'X_SUPPLIER_CONTACT_NAME',
4360 sd.supplier_contact_name);
4361 IF g_debug THEN
4362 hr_utility.set_location('Creating archive Item X_SUPPLIER_CONTACT_PHONE',3);
4363 END if;
4364 create_extract_archive_details(p_assignment_action_id,
4365 'X_SUPPLIER_CONTACT_TELEPHONE',
4366 sd.supplier_contacT_phone);
4367 IF g_debug THEN
4368 hr_utility.set_location('Creating archive Item X_SUPPLIER_EMAIL',3);
4369 END if;
4370 create_extract_archive_details(p_assignment_action_id,
4371 'X_SUPPLIER_EMAIL',
4372 sd.email_address);
4373 IF g_debug THEN
4374 hr_utility.set_location('Creating archive Item X_SUPPLIER_ADDRESS_1',3);
4375 END if;
4376 create_extract_archive_details(p_assignment_action_id,
4377 'X_SUPPLIER_ADDRESS_1',
4378 sd.supplier_address_1);
4379 IF g_debug THEN
4380 hr_utility.set_location('Creating archive Item X_SUPPLIER_ADDRESS_2',3);
4381 END if;
4382 create_extract_archive_details(p_assignment_action_id,
4383 'X_SUPPLIER_ADDRESS_2',
4384 sd.supplier_address_2);
4385
4386 IF g_debug THEN
4387 hr_utility.set_location('Creating archive Item X_SUPPLIER_ADDRESS_3',3);
4388 END if;
4389 create_extract_archive_details(p_assignment_action_id,
4390 'X_SUPPLIER_ADDRESS_3',
4391 sd.supplier_address_3);
4392 IF g_debug THEN
4393 hr_utility.set_location('Creating archive Item X_SUPPLIER_SUBURB',3);
4394 END if;
4395 create_extract_archive_details(p_assignment_action_id,
4396 'X_SUPPLIER_SUBURB',
4397 sd.supplier_suburb);
4398 IF g_debug THEN
4399 hr_utility.set_location('Creating archive Item X_SUPPLIER_STATE',3);
4400 END if;
4401 create_extract_archive_details(p_assignment_action_id,
4402 'X_SUPPLIER_STATE',
4403 sd.supplier_state);
4404 IF g_debug THEN
4405 hr_utility.set_location('Creating archive Item X_SUPPLIER_POSTCODE',3);
4406 END if;
4407 create_extract_archive_details(p_assignment_action_id,
4408 'X_SUPPLIER_POSTCODE',
4409 sd.supplier_postcode);
4410
4411 IF g_debug THEN
4412 hr_utility.set_location('Creating archive Item X_SUPPLIER_COUNTRY',3);
4413 END if;
4414 create_extract_archive_details(p_assignment_action_id,
4415 'X_SUPPLIER_COUNTRY',
4416 sd.supplier_country);
4417
4418
4419 end loop;
4420 IF g_debug THEN
4421 hr_utility.set_location('Archived all the supplier details',200);
4422 END if;
4423 exception
4424 when others then
4425 IF g_debug THEN
4426 hr_utility.set_location('Error in archiving supplier details ',10);
4427 END if;
4428 raise;
4429
4430 End archive_supplier_details;
4431
4432 /*-------------------------------------------------------------------------------
4433 Bug 6192381 Procedure to adjust old etp deduction and old Invalidity Payments
4434
4435 -------------------------------------------------------------------------------*/
4436 procedure adjust_old_etp_values
4437 (p_trans_etp_flag in Varchar2
4438 ,p_part_of_prev_etp_flag in Varchar2
4439 ) is
4440
4441 l_procedure constant varchar2(80) := g_package || '.adjust_old_etp_values';
4442
4443 l_old_etp_ded NUMBER;
4444 l_old_inv_pay NUMBER;
4445
4446 Begin
4447 g_debug := hr_utility.debug_enabled;
4448
4449 if g_debug then
4450 hr_utility.set_location(l_procedure, 1);
4451 hr_utility.trace('In p_trans_etp_flag '||p_trans_etp_flag);
4452 hr_utility.trace('In p_part_of_prev_etp_flag '||p_part_of_prev_etp_flag);
4453 hr_utility.trace('------------------------------------------------');
4454 hr_utility.trace('Lump Sum C Deductions ===>' || p_result_table(7).balance_value);
4455 hr_utility.trace('Invalidity Payments ===>' || p_result_table(14).balance_value);
4456 hr_utility.trace('ETP Deductions Transitional Not Part of Prev Term ===>' || p_result_table(18).balance_value);
4457 hr_utility.trace('ETP Deductions Transitional Part of Prev Term ===>' || p_result_table(19).balance_value);
4458 hr_utility.trace('ETP Deductions Life Benefit Not Part of Prev Term ===>' || p_result_table(20).balance_value);
4459 hr_utility.trace('ETP Deductions Life Benefit Part of Prev Term ===>' || p_result_table(21).balance_value);
4460 hr_utility.trace('Invalidity Payments Life Benefit Not Part of Prev Term ===>' || p_result_table(22).balance_value);
4461 hr_utility.trace('Invalidity Payments Life Benefit Part of Prev Term ===>' || p_result_table(23).balance_value);
4462 hr_utility.trace('Invalidity Payments Transitional Not Part of Prev Term ===>' || p_result_table(24).balance_value);
4463 hr_utility.trace('Invalidity Payments Transitional Part of Prev Term ===>' || p_result_table(25).balance_value);
4464
4465 end if;
4466
4467 l_old_etp_ded := 0;
4468 l_old_inv_pay := 0;
4469
4470 /* Bug 6192381 For Termination Payments before Multiple ETP Payment Enhancement the new balances for each combination
4471 does not exist , therefore to find out the values difference of Old balance which has total values and sum of new balances
4472 is taken to archive the old values . this is done for ETP Deduction and Invalidity Payments
4473
4474 l_old_etp_ded := Lump Sum C Deductions - ( ETP Deductions Transitional Not Part of Prev Term +
4475 ETP Deductions Transitional Part of Prev Term +
4476 ETP Deductions Life Benefit Not Part of Prev Term +
4477 ETP Deductions Life Benefit Part of Prev Term )
4478
4479 l_old_inv_pay := Invalidity Payments - ( Invalidity Payments Life Benefit Not Part of Prev ETP +
4480 Invalidity Payments Life Benefit Part of Prev ETP +
4481 Invalidity Payments Transitional Not Part of Prev ETP +
4482 Invalidity Payments Transitional Part of Prev ETP )
4483
4484 If these values are greater then 0 then based on Inputs Transitional ETP and Part of Previously Paid ETP , these values are
4485 added to corresponding Balances .
4486 */
4487
4488 l_old_etp_ded := p_result_table(7).balance_value - (p_result_table(18).balance_value + p_result_table(19).balance_value +
4489 p_result_table(20).balance_value + p_result_table(21).balance_value );
4490
4491 l_old_inv_pay := p_result_table(14).balance_value - (p_result_table(22).balance_value + p_result_table(23).balance_value +
4492 p_result_table(24).balance_value + p_result_table(25).balance_value );
4493
4494 if g_debug then
4495 hr_utility.trace('l_old_etp_ded ===>' || l_old_etp_ded);
4496 hr_utility.trace('l_old_inv_pay ===>' || l_old_inv_pay);
4497 end if;
4498
4499
4500 if ( l_old_etp_ded > 0 ) then
4501 if (p_trans_etp_flag = 'Y' and p_part_of_prev_etp_flag ='N') then
4502 p_result_table(18).balance_value := p_result_table(18).balance_value + l_old_etp_ded ;
4503 end if;
4504
4505 if (p_trans_etp_flag = 'Y' and p_part_of_prev_etp_flag ='Y') then
4506 p_result_table(19).balance_value := p_result_table(19).balance_value + l_old_etp_ded ;
4507 end if;
4508
4509 if (p_trans_etp_flag = 'N' and p_part_of_prev_etp_flag ='N') then
4510 p_result_table(20).balance_value := p_result_table(20).balance_value + l_old_etp_ded ;
4511 end if;
4512
4513 if (p_trans_etp_flag = 'N' and p_part_of_prev_etp_flag ='Y') then
4514 p_result_table(21).balance_value := p_result_table(21).balance_value + l_old_etp_ded ;
4515 end if;
4516
4517 end if;
4518
4519 if ( l_old_inv_pay > 0 ) then
4520 if (p_trans_etp_flag = 'N' and p_part_of_prev_etp_flag ='N') then
4521 p_result_table(22).balance_value := p_result_table(22).balance_value + l_old_inv_pay ;
4522 end if;
4523
4524 if (p_trans_etp_flag = 'N' and p_part_of_prev_etp_flag ='Y') then
4525 p_result_table(23).balance_value := p_result_table(23).balance_value + l_old_inv_pay ;
4526 end if;
4527
4528 if (p_trans_etp_flag = 'Y' and p_part_of_prev_etp_flag ='N') then
4529 p_result_table(24).balance_value := p_result_table(24).balance_value + l_old_inv_pay ;
4530 end if;
4531
4532 if (p_trans_etp_flag = 'Y' and p_part_of_prev_etp_flag ='Y') then
4533 p_result_table(25).balance_value := p_result_table(25).balance_value + l_old_inv_pay ;
4534 end if;
4535
4536 end if;
4537
4538 exception
4539 when others then
4540 IF g_debug THEN
4541 hr_utility.set_location('Error in adjust_old_etp_values ',10);
4542 END if;
4543
4544 End adjust_old_etp_values;
4545
4546 --------------------------------------------------------------------+
4547 -- This procedure is actually used to archive data . It
4548 -- internally calls private procedures to archive balances ,
4549 -- employee details, employer details and supplier details .
4550 -- Calls the following procedures
4551 -- 1. archive_balance_details
4552 -- 2. archive_etp_details
4553 -- 3. archive_prepost_details
4554 -- 4. archive_employer_details
4555 -- 5. archive_supplier_details
4556 -- 6. archive_employee_details
4557 --------------------------------------------------------------------+
4558 procedure archive_code
4559 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
4560 ,p_effective_date in date
4561 ) is
4562 --
4563 l_procedure constant varchar2(80) := g_package || '.archive_code';
4564 l_assignment_id pay_assignment_actions.assignment_id%type;
4565 l_business_group_id pay_payroll_actions.business_group_id%type ;
4566 l_registered_employer hr_organization_units.organization_id%type;
4567 l_current_le hr_organization_units.organization_id%type; --4363057
4568 l_payroll_action_id pay_payroll_actions.payroll_action_id%type ;
4569 l_year_start pay_payroll_Actions.effective_date%type;
4570 l_year_end pay_payroll_actions.effective_date%type;
4571 l_employee_type per_all_people_f.current_Employee_Flag%type;
4572 l_current_employee_flag per_all_people_f.current_employee_flag%type :='Y';
4573 l_actual_termination_date per_periods_of_service.actual_termination_date%TYPE;
4574 l_date_start per_periods_of_service.date_start%TYPE;
4575 l_asg_start pay_payroll_actions.effective_date%type;
4576 l_asg_end pay_payroll_actions.effective_date%type;
4577 l_effective_date pay_payroll_actions.effective_date%type;
4578 l_death_benefit_type varchar2(100);
4579 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
4580 l_max_assignment_action_id pay_assignment_actions.assignment_action_id%type;
4581 l_bbr_assignment_action_id pay_assignment_actions.assignment_action_id%type;
4582 l_fbt_assignment_action_id pay_assignment_actions.assignment_action_id%type; --2610141
4583 lump_sum_c_found boolean := false;
4584 l_final_process_date date; --263659
4585 l_term_date varchar2(10); --3263659
4586 l_fetched_termination_date per_periods_of_service.actual_termination_date%TYPE; --3263659
4587 l_reporting_flag varchar2(5) := 'YES'; --3098353
4588 l_bal_value varchar2(20); --3098353
4589 l_alw_bal_exist varchar2(20); --3098353
4590 l_lst_yr_term varchar2(10); --3661230
4591 v_lst_year_start date ; --3661230
4592 l_fbt_year_start date;
4593 l_net_balance number := 0; --3098353
4594 l_fbt_balance number := 0; --3098353
4595 l_dummy number; --4363057
4596 l_pay_start varchar2(10); --4363057
4597 l_pay_end varchar2(10); --4363057
4598 l_curr_term_0_bal_flag varchar2(5) :='NO'; --3937976
4599 l_le_end_date_flag varchar2(5) := 'N'; --2610141
4600 --
4601 -------------------------------------------------+
4602 -- Cursor to get details for assignment action id
4603 -------------------------------------------------+
4604 --
4605 cursor c_action(c_assignment_action_id number) is
4606 select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',ppa.legislative_parameters)
4607 , pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa.legislative_parameters)
4608 , pay_core_utils.get_parameter('EMPLOYEE_TYPE',ppa.legislative_parameters)
4609 , ppa.payroll_action_id
4610 , paa.assignment_id
4611 , to_date('01-07-'|| substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa.legislative_parameters),1,4),'DD-MM-YYYY')
4612 , to_date('30-06-'|| substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa.legislative_parameters),6,4),'DD-MM-YYYY')
4613 , pay_core_utils.get_parameter('LST_YR_TERM',ppa.legislative_parameters) /*Bug3661230*/
4614 from pay_assignment_actions paa
4615 , pay_payroll_actions ppa
4616 where paa.assignment_action_id = c_assignment_action_id
4617 and ppa.payroll_action_id = paa.payroll_action_id ;
4618 --
4619 -------------------------------------------------+
4620 -- Cursor to get current employee flag
4621 -- Bug 2856638 : added parameter c_business_group_id
4622 -------------------------------------------------+
4623 --
4624 cursor etp_code
4625 (c_assignment_id in pay_assignment_actions.assignment_id%type
4626 ,c_lst_year_start in pay_payroll_actions.effective_date%type --3263659
4627 ,c_year_start in pay_payroll_actions.effective_date%type
4628 ,c_year_end in pay_payroll_actions.effective_date%type
4629 ,c_def_bal_id in pay_defined_balances.defined_balance_id%type
4630 ,c_business_group_id in pay_payroll_actions.business_group_id%type
4631 ) is
4632 select distinct nvl(current_employee_flag,'N') current_employee_flag,
4633 actual_termination_date,
4634 date_start,
4635 pps.pds_information2,
4636 to_number(substr(max(lpad(ppa.action_sequence,15,'0')||ppa.assignment_action_id),16)), --3755305
4637 pps.final_process_date final_process_date --3263659
4638 from per_all_people_f p,
4639 per_all_assignments_f a,
4640 per_periods_of_service pps,
4641 pay_all_payrolls_f papf, --4281290
4642 pay_payroll_actions pa,
4643 pay_assignment_actions ppa
4644 where a.person_id = p.person_id
4645 and pps.person_id = p.person_id
4646 and a.assignment_id = ppa.assignment_id
4647 and papf.business_group_id = p.business_group_id --4281290
4648 and pa.payroll_id = papf.payroll_id --4281290
4649 and pa.effective_date between papf.effective_start_date and papf.effective_end_date --4281290
4650 and pa.payroll_Action_id = ppa.payroll_Action_id
4651 and ppa.tax_unit_id = l_registered_employer --2610141
4652 and (
4653 (pps.actual_termination_date between c_lst_year_start and c_year_end
4654 and pa.effective_date between c_year_start and c_year_end --3263659
4655 )
4656 or
4657 (pps.actual_termination_date between l_fbt_year_start
4658 and to_date('30-06-'||to_char(c_year_start,'YYYY'),'DD-MM-YYYY')
4659 and pa.effective_date between to_date('01-04-'||to_char(c_year_start,'YYYY'),'DD-MM-YYYY')
4660 and to_date('30-06-'||to_char(c_year_start,'YYYY'),'DD-MM-YYYY')
4661 )
4662 )
4663 and a.assignment_id = c_assignment_id
4664 and p.effective_start_date = (select max(pp.effective_start_date)
4665 from per_all_people_f pp
4666 where p.person_id = pp.person_id
4667 and p.business_group_id = c_business_group_id
4668 ) -- Bug 2856638
4669 and a.effective_start_date = (select max(aa.effective_start_date)
4670 from per_all_assignments_f aa
4671 where aa.assignment_id = c_assignment_id
4672 ) --4281290
4673 and pa.action_type in ('R','Q','I','B','V') --2646912, 4063321
4674 and a.period_of_service_id = pps.period_of_service_id --3586388
4675 group by nvl(current_employee_flag,'N') --3019374
4676 , actual_termination_date
4677 , date_start
4678 , pps.pds_information2
4679 , pps.final_process_date; --3263659
4680 --
4681 --3263659
4682 cursor cr_effective_date (c_assignment_action_id pay_assignment_actions.assignment_action_id%type) IS
4683 select ppa.effective_date
4684 from pay_payroll_actions ppa,
4685 pay_assignment_actions paa
4686 where paa.assignment_action_id = c_assignment_action_id
4687 and ppa.payroll_action_id = paa.payroll_action_id;
4688 --
4689 -------------------------------------------------+
4690 -- Cursor to check if 'Lump Sum C Payments' Balance
4691 -- exists. This is used to decide if archive_etp_Details
4692 -- is to be called or not(Bug - 2581436 )
4693 -------------------------------------------------+
4694 cursor balance_exists is
4695 select pdb.defined_balance_id
4696 from pay_balance_types pbt,
4697 pay_defined_balances pdb,
4698 pay_balance_dimensions pbd
4699 where pbt.balance_name ='Lump Sum C Payments'
4700 and pbt.balance_type_id = pdb.balance_type_id
4701 and pdb.balance_dimension_id = pbd.balance_dimension_id --2501105
4702 and pbd.legislation_code = g_legislation_code
4703 and pdb.legislation_code = g_legislation_code
4704 and pbd.dimension_name = '_ASG_LE_YTD'; --2610141
4705 --
4706 ----------------------------------------------------------------------------------------+
4707 -- Cursor to get maximum assignment_action_id to use in BBR Call (Bug 4738470)
4708 ----------------------------------------------------------------------------------------+
4709 --
4710 cursor c_max_asg_action_id
4711 (c_assignment_id per_all_assignments_f.assignment_id%TYPE
4712 ,c_business_group_id hr_all_organization_units.organization_id%TYPE
4713 ,c_tax_unit_id hr_all_organization_units.organization_id%TYPE
4714 ,c_year_start date
4715 ,c_year_end date
4716 ) is
4717 select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id,
4718 max(paa.action_sequence) action_sequence
4719 from pay_assignment_actions paa,
4720 pay_payroll_actions ppa,
4721 per_assignments_f paf
4722 where paa.assignment_id = paf.assignment_id
4723 and paf.assignment_id = c_assignment_id
4724 and ppa.payroll_action_id = paa.payroll_action_id
4725 and ppa.effective_date between c_year_start and c_year_end
4726 and ppa.payroll_id = paf.payroll_id
4727 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
4728 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
4729 and paa.action_status = 'C'
4730 and paa.tax_unit_id = c_tax_unit_id;
4731
4732 /*Bug# 4363057 - Three new cursors introduced for the Retro enhancement
4733 a) csr_check_eff_le - This cursor will check whether the employee
4734 is active in the current financial year for the submitted legal employer
4735 b) csr_get_dates - This cursor will get the maximum and minimum payment dates for the employee
4736 in the current financial year. This cursor should execute only when the
4737 employee is not active in the current financial year for the submitted legal employer.
4738 c) csr_get_end_le - This cursor will get the effective legal employer on the last payment date of the employee
4739 and it will be passed as an argument to archive_employee_details procedure for archiving
4740 employee details. This cursor should also execute only when the employee is not active in the current financial year for the submitted legal employer.
4741 */
4742 cursor csr_check_eff_le
4743 (c_assignment_id per_assignments_f.assignment_id%type
4744 ,c_legal_employer hr_organization_units.organization_id%type
4745 ,c_year_start pay_payroll_actions.effective_date%type
4746 ,c_year_end pay_payroll_actions.effective_date%type
4747 ) is
4748 select paaf.assignment_id
4749 from per_assignments_f paaf,
4750 hr_soft_coding_keyflex hsck
4751 where paaf.assignment_id = c_assignment_id
4752 and paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
4753 and paaf.effective_start_date <= c_year_end
4754 and paaf.effective_end_date >= c_year_start
4755 and hsck.segment1 = c_legal_employer;
4756 --
4757
4758 /* Bug 5371102 - Added joins on Payroll ID for better performance */
4759 cursor csr_get_dates
4760 (c_assignment_id per_assignments_f.assignment_id%type
4761 ,c_year_start pay_payroll_actions.effective_date%type
4762 ,c_year_end pay_payroll_actions.effective_date%type
4763 ,c_legal_employer hr_organization_units.organization_id%type
4764 ) is
4765 select to_char(min(ppa.effective_date),'DDMMYYYY'), to_char(max(ppa.effective_date),'DDMMYYYY')
4766 from pay_assignment_actions paa,
4767 pay_payroll_actions ppa,
4768 per_assignments_f paaf
4769 where paa.assignment_id = paaf.assignment_id
4770 and paaf.assignment_id = c_assignment_id
4771 and paa.payroll_action_id = ppa.payroll_action_id
4772 and ppa.effective_date between c_year_start and c_year_end
4773 and ppa.payroll_id = paaf.payroll_id
4774 and ppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
4775 and paa.tax_unit_id = c_legal_employer
4776 and paa.action_status = 'C'
4777 and ppa.action_type in ('R','Q','V'); /*Bug 4387183*/
4778 --
4779 -- 4387183 - This cursor has been modified. It will now pick the maximum effective record for the
4780 -- assignment between FBT year start and financial year end.
4781 --
4782 cursor csr_get_end_le
4783 (c_assignment_id per_assignments_f.assignment_id%type
4784 ,c_year_end pay_payroll_actions.effective_date%type
4785 ,c_year_start pay_payroll_actions.effective_date%type
4786 ) is
4787 select hsck.segment1
4788 from per_assignments_f paaf,
4789 hr_soft_coding_keyflex hsck
4790 where paaf.assignment_id = c_assignment_id
4791 and paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
4792 and paaf.effective_start_date <= c_year_end
4793 and paaf.effective_end_date >= c_year_start
4794 order by paaf.effective_start_date desc;
4795 --
4796 -- End 4363057
4797 --
4798 ---------------------------------------------------------------------------+
4799 -- This table is used to store all the balance names
4800 -- for these, archive_balance_details procedure is called to
4801 -- create respective balance.
4802 ---------------------------------------------------------------------------+
4803 --
4804 type bal_type is table of varchar2(100) index by binary_integer;
4805 tab_bal_name bal_type ;
4806 --
4807 ---------------------------------------------------------------------------+
4808 --- This table is used to store the actual balance names(Bug #2454595)
4809 --- If any balances are added further, then corresponding archive item
4810 --- PL/SQL table should be updated.
4811 ---------------------------------------------------------------------------+
4812 --
4813 type bal_actual_type is table of varchar2(100) index by binary_integer;
4814 tab_bal_actual_name bal_actual_type ;
4815 l_bbr_action_sequence pay_assignment_actions.action_sequence%type; --3701869
4816
4817 lv_trans_etp_flag varchar2(1);
4818 lv_part_of_prev_etp_flag varchar2(1);
4819
4820 begin
4821 g_debug := hr_utility.debug_enabled;
4822 if g_debug then
4823 hr_utility.set_location(l_procedure, 1);
4824 hr_utility.set_location(l_procedure ||' => assignment action:'||p_assignment_action_id,2);
4825 end if;
4826 --
4827
4828 lv_trans_etp_flag :='N';
4829 lv_part_of_prev_etp_flag :='N';
4830
4831 tab_bal_name(1) :='X_FRINGE_BENEFITS_ASG_YTD';
4832 tab_bal_name(2) :='X_CDEP_ASG_YTD';
4833 tab_bal_name(3) :='X_EARNINGS_TOTAL_ASG_YTD';
4834 tab_bal_name(4) :='X_LUMP_SUM_A_DEDUCTIONS_ASG_YTD';
4835 tab_bal_name(5) :='X_LUMP_SUM_A_PAYMENTS_ASG_YTD';
4836 tab_bal_name(6) :='X_LUMP_SUM_B_DEDUCTIONS_ASG_YTD';
4837 tab_bal_name(7) :='X_LUMP_SUM_B_PAYMENTS_ASG_YTD';
4838 tab_bal_name(8) :='X_LUMP_SUM_D_PAYMENTS_ASG_YTD';
4839 tab_bal_name(9) :='X_TOTAL_TAX_DEDUCTIONS_ASG_YTD';
4840 tab_bal_name(10):='X_OTHER_INCOME_ASG_YTD';
4841 tab_bal_name(11):='X_UNION_FEES_ASG_YTD';
4842 tab_bal_name(12):='X_INVALIDITY_PAYMENTS_ASG_YTD';
4843 tab_bal_name(13):='X_LUMP_SUM_C_PAYMENTS_ASG_YTD';
4844 tab_bal_name(14):='X_LUMP_SUM_C_DEDUCTIONS_ASG_YTD';
4845 tab_bal_name(15):='X_WORKPLACE_GIVING_DEDUCTIONS_ASG_YTD'; /*4015082 */
4846 /* Begin 6192381 */
4847 tab_bal_name(16):='X_ETP_DED_TRANS_PPTERM_ASG_YTD';
4848 tab_bal_name(17):='X_ETP_DED_TRANS_NOT_PPTERM_ASG_YTD';
4849 tab_bal_name(18):='X_ETP_DED_NOT_TRANS_PPTERM_ASG_YTD';
4850 tab_bal_name(19):='X_ETP_DED_NOT_TRANS_NOT_PPTERM_ASG_YTD';
4851 tab_bal_name(20):='X_INV_PAY_NOT_TRANS_NOT_PPTERM_ASG_YTD';
4852 tab_bal_name(21):='X_INV_PAY_NOT_TRANS_PPTERM_ASG_YTD';
4853 tab_bal_name(22):='X_INV_PAY_TRANS_NOT_PPTERM_ASG_YTD';
4854 tab_bal_name(23):='X_INV_PAY_TRANS_PPTERM_ASG_YTD';
4855
4856 /* End 6192381 */
4857 --
4858 ---------------------------------------------------------------------------+
4859 --- Hard Coded Balance names(Bug #2454595)
4860 ---------------------------------------------------------------------------+
4861 tab_bal_actual_name(1) := 'Fringe Benefits';
4862 tab_bal_actual_name(2) := 'CDEP';
4863 tab_bal_actual_name(3) := 'Earnings_Total';
4864 tab_bal_actual_name(4) := 'Lump Sum A Deductions';
4865 tab_bal_actual_name(5) := 'Lump Sum A Payments';
4866 tab_bal_actual_name(6) := 'Lump Sum B Deductions';
4867 tab_bal_actual_name(7) := 'Lump Sum B Payments';
4868 tab_bal_actual_name(8) := 'Lump Sum D Payments';
4869 tab_bal_actual_name(9) := 'Total_Tax_Deductions';
4870 tab_bal_actual_name(10):= 'Other Income';
4871 tab_bal_actual_name(11):= 'Union Fees';
4872 tab_bal_actual_name(12):= 'Invalidity Payments';
4873 tab_bal_actual_name(13):= 'Lump Sum C Payments';
4874 tab_bal_actual_name(14):= 'Lump Sum C Deductions';
4875 tab_bal_actual_name(15):= 'Workplace Giving Deductions'; /* 4015082 */
4876
4877 /* Begin 6192381 */
4878 tab_bal_actual_name(16):= 'ETP Deductions Transitional Part of Prev Term';
4879 tab_bal_actual_name(17):= 'ETP Deductions Transitional Not Part of Prev Term';
4880 tab_bal_actual_name(18):= 'ETP Deductions Life Benefit Part of Prev Term';
4881 tab_bal_actual_name(19):= 'ETP Deductions Life Benefit Not Part of Prev Term';
4882 tab_bal_actual_name(20):= 'Invalidity Payments Life Benefit Not Part of Prev Term';
4883 tab_bal_actual_name(21):= 'Invalidity Payments Life Benefit Part of Prev Term';
4884 tab_bal_actual_name(22):= 'Invalidity Payments Transitional Not Part of Prev Term';
4885 tab_bal_actual_name(23):= 'Invalidity Payments Transitional Part of Prev Term';
4886
4887 /* End 6192381 */
4888
4889 /* Bug 6470581 - Added Changes for Amended Payment Summary
4890 i. Initialized Amended PS PL/SQL table
4891 ii. Archive Payment Summary Flag DB Items
4892 */
4893
4894
4895 p_all_dbi_tab.delete;
4896
4897 IF g_payment_summary_type = 'O'
4898 THEN
4899
4900 create_extract_archive_details
4901 (p_assignment_action_id
4902 ,'X_PAYMENT_SUMMARY_TYPE'
4903 ,g_payment_summary_type
4904 );
4905
4906 create_extract_archive_details
4907 (p_assignment_action_id
4908 ,'X_PAYG_PAYMENT_SUMMARY_TYPE'
4909 ,g_payment_summary_type
4910 );
4911
4912 create_extract_archive_details
4913 (p_assignment_action_id
4914 ,'X_ETP1_PAYMENT_SUMMARY_TYPE'
4915 ,g_payment_summary_type
4916 );
4917
4918 create_extract_archive_details
4919 (p_assignment_action_id
4920 ,'X_ETP2_PAYMENT_SUMMARY_TYPE'
4921 ,g_payment_summary_type
4922 );
4923
4924 create_extract_archive_details
4925 (p_assignment_action_id
4926 ,'X_ETP3_PAYMENT_SUMMARY_TYPE'
4927 ,g_payment_summary_type
4928 );
4929
4930 create_extract_archive_details
4931 (p_assignment_action_id
4932 ,'X_ETP4_PAYMENT_SUMMARY_TYPE'
4933 ,g_payment_summary_type
4934 );
4935
4936 END IF;
4937
4938 /* End changes Bug 6470581 */
4939
4940 --
4941 open c_action(p_assignment_action_id);
4942 fetch c_action
4943 into l_business_group_id
4944 , l_registered_employer
4945 , l_employee_type
4946 , l_payroll_action_id
4947 , l_assignment_id
4948 , l_year_start
4949 , l_year_end
4950 , l_lst_yr_term; --3661230
4951 close c_action;
4952 --
4953 -- 3701869
4954 --
4955 l_lst_yr_term := nvl(l_lst_yr_term,'Y');
4956 --
4957 -- 3661230
4958 --
4959 if l_lst_yr_term = 'Y' then
4960 v_lst_year_start := add_months(l_year_start,-12); -- 3263659
4961 l_fbt_year_start := to_date('01-04-'||to_char(l_year_start,'YYYY'),'DD-MM-YYYY');
4962 else
4963 v_lst_year_start := to_date('01-01-1900','DD-MM-YYYY');
4964 l_fbt_year_start := to_date('01-01-1900','DD-MM-YYYY');
4965 end if;
4966 --
4967 if g_debug then
4968 hr_utility.set_location(l_procedure ||' => 1 assignment_id :' || l_assignment_id, 2);
4969 end if;
4970 --------------------------------------------------------+
4971 -- archival of data for terminated employees
4972 --------------------------------------------------------+
4973 --
4974 if (l_employee_type <> 'C') then
4975 --
4976 -- 2646912
4977 -- Store the value of defined balance id for using for next assignments
4978 -- lump_sum_c_found is used to indicate whether Lump Sum C Payment amount exists for the employee
4979 --
4980 if pkg_lump_sum_c_def_bal_id is null or pkg_lump_sum_c_def_bal_id = 0 then
4981 if g_debug then
4982 hr_utility.set_location(l_procedure ,6 );
4983 end if;
4984 --
4985 open balance_exists;
4986 fetch balance_exists
4987 into pkg_lump_sum_c_def_bal_id;
4988 --
4989 if balance_exists%notfound then
4990 pkg_lump_sum_c_def_bal_id := -1;
4991 if g_debug then
4992 hr_utility.set_location(l_procedure ,7 );
4993 end if;
4994 end if;
4995 close balance_exists;
4996 end if;
4997 --
4998 if pkg_lump_sum_c_def_bal_id <> -1 then
4999 open etp_code
5000 (l_assignment_id
5001 ,v_lst_year_start
5002 ,l_year_start
5003 ,l_year_end
5004 ,pkg_lump_sum_c_def_bal_id
5005 ,l_business_group_id
5006 ); -- 2856638
5007 fetch etp_code
5008 into l_current_employee_flag
5009 , l_actual_termination_date
5010 , l_date_start
5011 , l_death_benefit_type
5012 , l_max_assignment_action_id -- 3019374
5013 , l_final_process_date; -- 3263659
5014 --
5015 if etp_code%found then -- 3019374
5016 if (pay_balance_pkg.get_value(pkg_lump_sum_c_def_bal_id, l_max_assignment_action_id, l_registered_employer,null,null,null,null) > 0) then --2610141
5017 lump_sum_c_found := true;
5018 if g_debug then
5019 hr_utility.set_location('Lump Sum C Payment found.' ,9 );
5020 end if;
5021 end if;
5022 --
5023 -- 3263659
5024 -- To set the actual termination date so as to archive all balanced in case paid in
5025 -- this year and terminated in last year
5026 --
5027 open cr_effective_date(l_max_assignment_action_id);
5028 fetch cr_effective_date
5029 into l_effective_date;
5030 close cr_effective_date;
5031 --
5032 l_fetched_termination_date := l_actual_termination_date; -- Bug3263659 To store actual date for Pre-post calculation
5033 if (l_actual_termination_date < l_year_start and l_effective_date >= l_year_start ) then
5034 l_actual_termination_date := nvl(l_final_process_date,l_effective_date); /* Bug 3098353 To set the End Date as
5035 Payment Date if final_process is null */
5036 end if;
5037 end if;
5038 close etp_code;
5039 end if;
5040
5041 if lump_sum_c_found = false then
5042 if g_debug then
5043 hr_utility.set_location('Lump Sum C Payment balance does not exists ',101);
5044 end if;
5045 end if;
5046 --
5047 /* Bug3263659 l_actual_termination_date AND clause added to prevent etp being archive in case its
5048 terminated in last year and no runs in current year
5049 */
5050 /* Bug 6112527 Added condition to_number(to_char(l_year_start,'YYYY')) >= 2007 for archive death benefit type D only after Fin Year 2007/2008 */
5051
5052 if (((l_death_benefit_type <>'D' or to_number(to_char(l_year_start,'YYYY')) >= 2007) or l_death_benefit_type is null) and (lump_sum_c_found = true)
5053 and not (l_actual_termination_date between l_fbt_year_start and to_date('30-06-'||to_char(l_year_start,'YYYY'),'DD-MM-YYYY')) ) then --Bug#3661230
5054 --
5055 -- 2448446 , 2646912
5056 --
5057 if g_debug then
5058 hr_utility.set_location('creating etp details for assignment id: ' ||l_assignment_id, 7);
5059 end if;
5060 --
5061 ------------------------------------------------------+
5062 -- call procedure to archive details of terminated employees
5063 -- archive etp details procedure archives employee related details
5064 -- archive prespost details archives prejul83 and postjun83 information
5065 -------------------------------------------------------+
5066 archive_etp_details
5067 (l_business_group_id
5068 ,l_registered_employer
5069 ,l_payroll_action_id
5070 ,p_assignment_action_id
5071 ,l_assignment_id
5072 ,l_year_start
5073 ,l_year_end
5074 ,v_lst_year_start /*Bug3661230 Added one extra parameter*/
5075 ,lv_trans_etp_flag /*Bug 6192381 Added New Parameters lv_trans_etp_flag and lv_part_of_prev_etp_flag */
5076 ,lv_part_of_prev_etp_flag
5077 );
5078 --
5079 l_term_date := to_char(l_actual_termination_date,'DDMMYYYY'); -- Bug3263659 TO Archive modified termination date
5080 --
5081 create_extract_archive_details
5082 (p_assignment_action_id
5083 ,'X_ETP_EMPLOYEE_END_DATE'
5084 ,l_term_date
5085 );
5086 --
5087 if g_debug then
5088 hr_utility.set_location('creating prejul83 and post jun 83 details for assignment id :' ||l_assignment_id, 7);
5089 end if;
5090 --
5091 archive_prepost_details
5092 (p_assignment_action_id
5093 ,l_max_assignment_action_id --2610141
5094 ,l_registered_employer --2610141
5095 ,g_legislation_code
5096 ,l_assignment_id
5097 ,l_payroll_action_id
5098 ,l_fetched_termination_date --3263659
5099 ,l_date_start
5100 ,l_year_start
5101 ,l_year_end
5102 ,lv_trans_etp_flag /*Bug 6192381 Added New Parameters lv_trans_etp_flag and lv_part_of_prev_etp_flag */
5103 ,lv_part_of_prev_etp_flag);
5104 --
5105 end if;
5106 end if; /* (l_employee_type <> 'C') */
5107 --
5108 ----------------------------------------------------------------------------
5109 -- if the employee has been terminated in the FBT year then archive only the
5110 -- Fringe Benefits balance , other balances should be zero
5111 -- else archive all balances
5112 ----------------------------------------------------------------------------
5113 if (l_actual_termination_date is not null) and
5114 (l_actual_termination_date between l_fbt_year_start and to_date('30-06-'||to_char(l_year_start,'YYYY'),'DD-MM-YYYY')) then --Bug#3661230
5115 --
5116 -- 4738470 - Get the Maximum assignment_action_id for FBT employee
5117 --
5118 open c_max_asg_action_id
5119 ( l_assignment_id
5120 , l_business_group_id
5121 , l_registered_employer
5122 , add_months(l_year_start,-3)
5123 , (l_year_start - 1)
5124 );
5125 fetch c_max_asg_action_id
5126 into l_fbt_assignment_action_id
5127 , l_bbr_action_sequence;
5128 close c_max_asg_action_id;
5129 --
5130 -- 2610141
5131 --
5132 archive_balance_details
5133 (p_assignment_action_id
5134 ,l_fbt_assignment_action_id --2610141
5135 ,l_registered_employer --2610141
5136 ,tab_bal_name(1) -- X_FRINGE_BENEFITS_ASG_YTD
5137 ,tab_bal_actual_name(1) -- 2454595
5138 ,g_legislation_code
5139 ,l_year_start
5140 ,l_year_end
5141 ,l_assignment_id
5142 ,l_payroll_action_id
5143 ,l_bal_value
5144 ); --3098353
5145 --
5146 l_net_balance := l_bal_value; --2610141
5147 l_actual_termination_date := l_year_start; --3098353 - Since in case of only FBT employee,
5148 -- period dates should be year start date.
5149 --
5150 -- 4866934 - Set the Balance Values to 0 for FBT Employee
5151 --
5152 p_result_table.delete;
5153 for i in p_balance_value_tab.first..p_balance_value_tab.last
5154 loop
5155 p_result_table(i).balance_value := 0;
5156 end loop;
5157 --
5158 else -- 3172963
5159 if g_debug then
5160 hr_utility.set_location('Get assignment_action_id for BBR call, for assignment id: ' || l_assignment_id, 11);
5161 end if;
5162 --
5163 -- 4738470 - Get the Maximum assignment_action_id
5164 --
5165 open c_max_asg_action_id
5166 (l_assignment_id
5167 ,l_business_group_id
5168 ,l_registered_employer
5169 ,l_year_start
5170 ,l_year_end
5171 );
5172 fetch c_max_asg_action_id
5173 into l_bbr_assignment_action_id
5174 , l_bbr_action_sequence;
5175 close c_max_asg_action_id;
5176 --
5177 if g_debug then
5178 hr_utility.set_location(l_procedure, 12);
5179 hr_utility.trace('Using ASSIGNMENT_ACTION_ID: ' || l_bbr_assignment_action_id);
5180 end if;
5181 --
5182 -- 4866934 - Flush balance values in PL/SQL table
5183 --
5184 p_result_table.delete;
5185 --
5186 -- Changes made for bug 2610141 Start here
5187 --
5188 p_context_table(1).tax_unit_id := l_registered_employer;
5189 --
5190 pay_balance_pkg.get_value
5191 (p_assignment_action_id => l_bbr_assignment_action_id
5192 ,p_defined_balance_lst => p_balance_value_tab
5193 ,p_context_lst => p_context_table
5194 ,p_output_table => p_result_table
5195 );
5196 --
5197 -- Changes made for bug 2610141 Ends here
5198 --
5199 if g_debug then
5200 hr_utility.set_location(l_procedure, 13);
5201 hr_utility.trace('------------------------------------------------');
5202 hr_utility.trace('CDEP ===>' || p_result_table(1).balance_value);
5203 hr_utility.trace('Leave Payments Marginal ===>' || p_result_table(2).balance_value);
5204 hr_utility.trace('Lump Sum A Deductions ===>' || p_result_table(3).balance_value);
5205 hr_utility.trace('Lump Sum A Payments ===>' || p_result_table(4).balance_value);
5206 hr_utility.trace('Lump Sum B Deductions ===>' || p_result_table(5).balance_value);
5207 hr_utility.trace('Lump Sum B Payments ===>' || p_result_table(6).balance_value);
5208 hr_utility.trace('Lump Sum C Deductions ===>' || p_result_table(7).balance_value);
5209 hr_utility.trace('Lump Sum C Payments ===>' || p_result_table(8).balance_value);
5210 hr_utility.trace('Lump Sum D Payments ===>' || p_result_table(9).balance_value);
5211 hr_utility.trace('Total_Tax_Deduction ===>' || p_result_table(10).balance_value);
5212 hr_utility.trace('Termination Deductions ===>' || p_result_table(11).balance_value);
5213 hr_utility.trace('Other Income ===>' || p_result_table(12).balance_value);
5214 hr_utility.trace('Union Fees ===>' || p_result_table(13).balance_value);
5215 hr_utility.trace('Invalidity Payments ===>' || p_result_table(14).balance_value);
5216 hr_utility.trace('Lump Sum E Payments ===>' || p_result_table(15).balance_value);
5217 hr_utility.trace('Earnings_Total ===>' || p_result_table(16).balance_value);
5218 hr_utility.trace('Workplace Giving ===>' || p_result_table(17).balance_value); /* 4015082 */
5219 hr_utility.trace('ETP Deductions Transitional Not Part of Prev Term ===>' || p_result_table(18).balance_value);
5220 hr_utility.trace('ETP Deductions Transitional Part of Prev Term ===>' || p_result_table(19).balance_value);
5221 hr_utility.trace('ETP Deductions Life Benefit Not Part of Prev Term ===>' || p_result_table(20).balance_value);
5222 hr_utility.trace('ETP Deductions Life Benefit Part of Prev Term ===>' || p_result_table(21).balance_value);
5223 hr_utility.trace('Invalidity Payments Life Benefit Not Part of Prev Term ===>' || p_result_table(22).balance_value);
5224 hr_utility.trace('Invalidity Payments Life Benefit Part of Prev Term ===>' || p_result_table(23).balance_value);
5225 hr_utility.trace('Invalidity Payments Transitional Not Part of Prev Term ===>' || p_result_table(24).balance_value);
5226 hr_utility.trace('Invalidity Payments Transitional Part of Prev Term ===>' || p_result_table(25).balance_value);
5227 end if;
5228
5229 if g_debug then
5230 hr_utility.set_location('lv_trans_etp_flag :' ||lv_trans_etp_flag, 7);
5231 hr_utility.set_location('lv_part_of_prev_etp_flag:' ||lv_part_of_prev_etp_flag, 7);
5232 end if;
5233
5234 /*Bug 6192381 Procedure to adjust old etp deduction and old Invalidity Payments */
5235 adjust_old_etp_values(lv_trans_etp_flag
5236 ,lv_part_of_prev_etp_flag);
5237 --
5238 for cnt in 1..tab_bal_name.count
5239 loop
5240 if g_debug then
5241 hr_utility.set_location('creating '||tab_bal_name(cnt)||' balance for ass action id: '|| l_bbr_assignment_action_id,14);
5242 hr_utility.set_location('Death benefit type:'||l_death_benefit_type,113);
5243 end if;
5244 --
5245 ---------------------------------+
5246 -- call procedure to archive all balances
5247 ---------------------------------+
5248 ---------------------------------
5249 -- 1956018
5250 ---------------------------------
5251 --
5252
5253 /* Bug 6112527 Modified condition that for death benefit type archive balances if Fin Year >= 2007/2008
5254 From 01-Jul-2007 ETP Amount for termination type Death and Benefit type Dependent amount is taxable */
5255 if ((l_death_benefit_type = 'D' and to_number(to_char(l_year_start,'YYYY')) <= 2006) and (tab_bal_name(cnt) in
5256 ('X_LUMP_SUM_C_PAYMENTS_ASG_YTD','X_LUMP_SUM_C_DEDUCTIONS_ASG_YTD'))) then
5257 null;
5258 else
5259
5260 if tab_bal_name.exists(cnt) then
5261 --
5262 -- 2610141
5263 --
5264 if tab_bal_name(cnt) = 'X_FRINGE_BENEFITS_ASG_YTD' then
5265 --
5266 -- 4738470 - Get the FBT Maximum assignment_action_id
5267 --
5268 open c_max_asg_action_id
5269 (l_assignment_id
5270 ,l_business_group_id
5271 ,l_registered_employer
5272 ,add_months(l_year_start,-3)
5273 ,add_months(l_year_end,-3)
5274 );
5275 fetch c_max_asg_action_id
5276 into l_fbt_assignment_action_id
5277 , l_bbr_action_sequence;
5278 close c_max_asg_action_id;
5279 --
5280 archive_balance_details
5281 (p_assignment_action_id
5282 ,l_fbt_assignment_action_id -- 2610141
5283 ,l_registered_employer -- 2610141
5284 ,tab_bal_name(1) -- X_FRINGE_BENEFITS_ASG_YTD
5285 ,tab_bal_actual_name(1) -- 2454595 Fringe Benefits
5286 ,g_legislation_code
5287 ,l_year_start
5288 ,l_year_end
5289 ,l_assignment_id
5290 ,l_payroll_action_id
5291 ,l_bal_value
5292 ); --3098353
5293 else
5294 --
5295 -- 2610141
5296 --
5297 archive_balance_details
5298 (p_assignment_action_id
5299 ,l_bbr_assignment_action_id -- 2610141
5300 ,l_registered_employer -- 2610141
5301 ,tab_bal_name(cnt)
5302 ,tab_bal_actual_name(cnt) -- 2454595
5303 ,g_legislation_code
5304 ,l_year_start
5305 ,l_year_end
5306 ,l_assignment_id
5307 ,l_payroll_action_id
5308 ,l_bal_value
5309 ); --3098353
5310 end if;
5311 --
5312 -- 3098353
5313 --
5314 if cnt = 1 then -- To store FBT balance value
5315 l_fbt_balance := l_bal_value;
5316 end if;
5317 --
5318 -- 3937976
5319 --
5320 l_net_balance:= l_net_balance + to_number(nvl(l_bal_value,0));
5321 --
5322 end if;
5323 end if;
5324 end loop;
5325 --
5326 -- By default reporting_flag is 'YES' i.e the employee will be displayed in any of the reports.
5327 -- The following statement checks that if employee is terminated in last year and the sum of
5328 -- balances is zero then reporting_flag will be 'NO' i.e. in this case the employee
5329 -- should not be reported in any of the reports.
5330 --
5331 if l_fetched_termination_date < l_year_start and l_net_balance = 0 then
5332 l_reporting_flag := 'NO';
5333 end if;
5334 --
5335 -- 3937976
5336 --
5337 if nvl(l_fetched_termination_date,to_date('31/12/4712','DD/MM/YYYY')) >= l_year_start and l_net_balance = 0 then
5338 l_curr_term_0_bal_flag:='YES';
5339 end if;
5340 end if;
5341 --
5342 -----------------------------------------------
5343 --* Archive Allowance details
5344 -----------------------------------------------
5345 archive_allowance_details
5346 (p_assignment_action_id
5347 ,l_bbr_assignment_action_id --2610141
5348 ,l_registered_employer --2610141
5349 ,l_year_start
5350 ,l_year_end
5351 ,l_assignment_id
5352 ,l_alw_bal_exist
5353 );
5354 --
5355 -- Perform archive of 2006 unions setup
5356 --
5357 /*
5358 archive_2006_unions
5359 (p_assignment_id => l_assignment_id
5360 ,p_assignment_action_id => p_assignment_action_id
5361 ,p_max_assignment_action_id => l_bbr_assignment_action_id
5362 ,p_registered_employer => l_registered_employer
5363 ,p_year_start => l_year_start
5364 ,p_year_end => l_year_end
5365 ,p_alw_bal_exist => l_alw_bal_exist
5366 );
5367 */
5368 --
5369 -- 3098353
5370 --
5371 -- This flag is sets the reporting_flag if the allowance are paid to employee
5372 -- in the current year.
5373 --
5374 if l_alw_bal_exist = 'TRUE' then
5375 l_reporting_flag := 'YES';
5376 l_curr_term_0_bal_flag :='NO'; -- 3937976
5377 end if;
5378 --
5379 -- If only FBT is reported than period end date should be year start date
5380 --
5381 if (l_net_balance - l_fbt_balance) = 0 and l_alw_bal_exist = 'FALSE' and l_fbt_balance <> 0 then
5382 l_actual_termination_date := l_year_start;
5383 l_term_date := to_char(l_actual_termination_date,'DDMMYYYY');
5384 if g_debug then
5385 hr_utility.set_location('Creating archive Item X_EMPLOYEE_END_DATE',12);
5386 end if;
5387 --
5388 -- 2610141
5389 --
5390 create_extract_archive_details
5391 (p_assignment_action_id
5392 ,'X_EMPLOYEE_LE_END_DATE'
5393 ,l_term_date
5394 );
5395 l_le_end_date_flag := 'Y';
5396 end if;
5397 --
5398 -- 2610141
5399 --
5400 if l_actual_termination_date <> l_fetched_termination_date and l_le_end_date_flag = 'N' then
5401 l_term_date := to_char(l_actual_termination_date,'DDMMYYYY');
5402 create_extract_archive_details
5403 (p_assignment_action_id
5404 ,'X_EMPLOYEE_LE_END_DATE'
5405 ,l_term_date
5406 );
5407 l_le_end_date_flag := 'Y';
5408 end if;
5409 --
5410 -- 2610141
5411 --
5412 if g_debug then
5413 hr_utility.set_location('Creating archive Item X_EMPLOYEE_END_DATE',12);
5414 end if;
5415 --
5416 l_term_date := to_char(l_actual_termination_date,'DDMMYYYY');
5417 create_extract_archive_details
5418 (p_assignment_action_id
5419 ,'X_EMPLOYEE_END_DATE'
5420 ,l_term_date
5421 );
5422 --
5423 create_extract_archive_details
5424 (p_assignment_action_id
5425 ,'X_REPORTING_FLAG'
5426 ,l_reporting_flag
5427 );
5428 --
5429 --3937976
5430 --
5431 create_extract_archive_details
5432 (p_assignment_action_id
5433 ,'X_CURR_TERM_0_BAL_FLAG'
5434 ,l_curr_term_0_bal_flag
5435 );
5436 --
5437 ------------------------------------------------------+
5438 --call procedure to archive supplier details
5439 -------------------------------------------------------+
5440 archive_supplier_details
5441 (l_business_group_id
5442 ,l_registered_employer
5443 ,l_payroll_action_id
5444 ,p_assignment_action_id
5445 ,l_assignment_id
5446 ,l_year_start
5447 ,l_year_end
5448 );
5449 --
5450 if g_debug then
5451 hr_utility.set_location('creating employee details for assignment id: ' || l_assignment_id, 9);
5452 end if;
5453 --
5454 ------------------------------------------------------+
5455 --call procedure to archive employee details
5456 -------------------------------------------------------+
5457 /*Bug# 4363057 - This logic is introduced to check if the employee is active for the submitted
5458 legal employer in the current financial year.
5459 If employee is active for the submitted legal employer then pass the value to
5460 archive_employee_details.
5461 If employee is not active for the submitted legal employer then get the maximum and
5462 minimum payment dates for the legal employer. Get the legal employer effective at the maximum
5463 payment date and pass it to archive_employee_details
5464 */
5465 /*Bug 4387183 - Above logic has been modified to take care of terminated employees.
5466 - FBT employees terminated in the FBT period will be archived with le start and end dates as
5467 financial year start dates.
5468 - When Payment Summary is submitted for Employees terminated with final process date as null and
5469 for legal employer in which they have recieved retropayments but are not active in the current
5470 financial year then the le start date will be set as financial year start, while the le end date
5471 will be set as the Retropayment date.
5472 - When Payment Summary is submitted for Employees terminated with final process date not null and
5473 for legal employer in which they have recieved retropayments but are not active in the current
5474 financial year then the le start date will be set as financial year start, while the le end date
5475 will be set as the final process date.
5476 */
5477 --
5478 open csr_check_eff_le(l_assignment_id, l_registered_employer, l_year_start, l_year_end);
5479 fetch csr_check_eff_le into l_dummy;
5480 if csr_check_eff_le%notfound then
5481 open csr_get_dates(l_assignment_id, l_year_start,l_year_end,l_registered_employer);
5482 fetch csr_get_dates into l_pay_start, l_pay_end;
5483 close csr_get_dates;
5484 --
5485 -- 4387183
5486 -- If the le end date has been archived already, that is for terminated
5487 -- employees then don't archive the start and end dates here. Le start
5488 -- date will be archived in archive_employee_detail
5489 --
5490 if l_le_end_date_flag = 'N' then
5491 --
5492 create_extract_archive_details
5493 (p_assignment_action_id
5494 ,'X_EMPLOYEE_LE_START_DATE'
5495 ,l_pay_start
5496 );
5497 create_extract_archive_details
5498 (p_assignment_action_id
5499 ,'X_EMPLOYEE_LE_END_DATE'
5500 ,l_pay_end
5501 );
5502 l_le_end_date_flag := 'B';
5503 end if;
5504 --
5505 open csr_get_end_le(l_assignment_id, l_year_end,l_fbt_year_start);
5506 fetch csr_get_end_le into l_current_le;
5507 close csr_get_end_le;
5508 --
5509 archive_employee_details
5510 (l_business_group_id
5511 ,l_current_le
5512 ,l_payroll_action_id
5513 ,p_assignment_action_id
5514 ,l_assignment_id
5515 ,l_year_start
5516 ,l_year_end
5517 ,l_le_end_date_flag
5518 ,l_fbt_year_start -- 4653934
5519 );
5520 else
5521 archive_employee_details
5522 (l_business_group_id
5523 ,l_registered_employer --2610141
5524 ,l_payroll_action_id
5525 ,p_assignment_action_id
5526 ,l_assignment_id
5527 ,l_year_start
5528 ,l_year_end
5529 ,l_le_end_date_flag
5530 ,l_fbt_year_start -- 4653934
5531 );
5532 end if;
5533 close csr_check_eff_le;
5534 --
5535 -- 4363057
5536 --
5537 if g_debug then
5538 hr_utility.set_location('creating employer details for assignment id: ' || l_assignment_id, 10);
5539 end if;
5540 --
5541 ------------------------------------------------------+
5542 --call procedure to archive employer details
5543 -------------------------------------------------------+
5544 --
5545 archive_employer_details
5546 (l_business_group_id
5547 ,l_bbr_assignment_action_id --2610141
5548 ,l_registered_employer --2610141
5549 ,l_payroll_action_id
5550 ,p_assignment_action_id
5551 ,l_assignment_id
5552 ,l_year_start
5553 ,l_year_end
5554 );
5555
5556 /* Bug 6470581 -Call Amended Payment Summary manipulation logic
5557 if this is Amended Payment Summary Run */
5558
5559 IF g_payment_summary_type = 'A'
5560 THEN
5561
5562 pay_au_payment_summary_amend.modify_and_archive_code
5563 (p_assignment_action_id => p_assignment_action_id
5564 ,p_effective_date => p_effective_date
5565 ,p_all_tab_new => p_all_dbi_tab);
5566
5567 END IF;
5568
5569 if g_debug then
5570 hr_utility.set_location('End of archive code', 37);
5571 end if;
5572
5573 exception
5574 when others then
5575 if g_debug then
5576 hr_utility.set_location('error in archive code - assignment id :' ||l_assignment_id,11);
5577 end if;
5578 raise;
5579 end archive_code;
5580
5581 --------------------------------------------------------------------+
5582 -- This function is used to get end of year values for archive items
5583 -- called from validation report and payment summary report
5584 --------------------------------------------------------------------+
5585
5586
5587 function get_archive_value(p_user_entity_name in ff_user_entities.user_entity_name%type,
5588 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type)
5589 return varchar2 is
5590
5591
5592 -- cursor to fetch the archive value
5593
5594 cursor csr_get_value(p_user_entity_name varchar2,
5595 p_assignment_action_id number) is
5596 select fai.value
5597 from ff_archive_items fai,
5598 ff_user_entities fue
5599 where fai.context1 = p_assignment_action_id
5600 and fai.user_entity_id = fue.user_entity_id
5601 and fue.user_entity_name = p_user_entity_name;
5602
5603
5604 l_value ff_archive_items.value%type;
5605 e_no_value_found exception;
5606
5607
5608 begin
5609
5610 g_debug := hr_utility.debug_enabled;
5611
5612 IF g_debug THEN
5613 hr_utility.set_location('Start of get archive value ',1);
5614 END if;
5615
5616 open csr_get_value(p_user_entity_name,
5617 p_assignment_action_id);
5618 fetch csr_get_value into l_value;
5619
5620 if csr_get_value%notfound then
5621 l_value := null;
5622 close csr_get_value;
5623 raise e_no_value_found;
5624 else
5625 close csr_get_value;
5626 end if;
5627 return(l_value);
5628 IF g_debug THEN
5629 hr_utility.set_location('End of get archive value ',2);
5630 END if;
5631
5632
5633 exception
5634 when e_no_value_found then
5635 IF g_debug THEN
5636 hr_utility.set_location('error in get archive value - assignment_action_id:' ||p_assignment_action_id,3);
5637 hr_utility.set_location('error in get archive value - user entity name :' ||p_user_entity_name,3);
5638 END if;
5639 return (null);
5640 when others then
5641 IF g_debug THEN
5642 hr_utility.set_location('error in get archive value - assignment_action_id:' ||p_assignment_action_id,3);
5643 hr_utility.set_location('error in get archive value - user entity name :' ||p_user_entity_name,3);
5644 END if;
5645 return (null);
5646 Raise;
5647 end get_archive_value;
5648
5649
5650
5651 -- Following Procedure is created as per enhancement - Bug#3132178
5652 -- This procedure checks if TEST_EFILE flag is TRUE then submit a request for Magtape Process
5653
5654 procedure spawn_data_file
5655 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
5656 is
5657
5658 ps_request_id NUMBER;
5659 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
5660 l_business_group_id number;
5661 l_start_date date;
5662 l_end_date date;
5663 l_effective_date date;
5664 l_legal_employer number;
5665 l_FINANCIAL_YEAR_code varchar2(10);
5666 l_TEST_EFILE varchar2(10);
5667 l_FINANCIAL_YEAR varchar2(10);
5668 l_legislative_param varchar2(200);
5669
5670 --------------------------------------------------------------------+
5671 -- Cursor : csr_params
5672 -- Description : Fetches User Parameters from Legislative_paramters
5673 -- column.
5674 --------------------------------------------------------------------+
5675
5676 CURSOR csr_magtape_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
5677 IS
5678 SELECT pay_core_utils.get_parameter('TEST_EFILE',legislative_parameters) TEST_EFILE,
5679 pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) BUSINESS_GROUP_ID,
5680 pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters) FINANCIAL_YEAR,
5681 pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) REGISTERED_EMPLOYER,
5682 to_date(pay_core_utils.get_parameter('START_DATE',legislative_parameters),'YYYY/MM/DD') start_date,
5683 to_date(pay_core_utils.get_parameter('END_DATE',legislative_parameters),'YYYY/MM/DD') end_date,
5684 to_date(pay_core_utils.get_parameter('EFFECTIVE_DATE',legislative_parameters),'YYYY/MM/DD') EFFECTIVE_DATE
5685 FROM pay_payroll_actions ppa
5686 WHERE ppa.payroll_action_id = c_payroll_action_id;
5687
5688
5689 CURSOR csr_lookup_code (c_financial_year varchar2)
5690 IS
5691 SELECT LOOKUP_CODE
5692 FROM HR_LOOKUPS
5693 WHERE lookup_type = 'AU_PS_FINANCIAL_YEAR'
5694 AND enabled_flag = 'Y'
5695 AND meaning =c_financial_year;
5696
5697 Begin
5698
5699 ps_request_id :=-1;
5700 l_TEST_EFILE :='N';
5701 g_debug := hr_utility.debug_enabled;
5702
5703 OPEN csr_magtape_params(p_payroll_action_id);
5704 FETCH csr_magtape_params
5705 INTO l_TEST_EFILE,
5706 l_business_group_id,
5707 l_FINANCIAL_YEAR,
5708 l_legal_employer,
5709 l_start_date,
5710 l_end_date,
5711 l_EFFECTIVE_DATE;
5712 CLOSE csr_magtape_params;
5713
5714 IF l_TEST_EFILE = 'Y' THEN
5715 OPEN csr_lookup_code(l_financial_year);
5716 FETCH csr_lookup_code
5717 INTO l_financial_year_code;
5718 CLOSE csr_lookup_code;
5719
5720 /* Bug 6470581 - Added the Payment Summary Type parameter for Datafile call */
5721
5722 l_legislative_param := 'BUSINESS_GROUP_ID=' || l_business_group_id ||' '
5723 || 'FINANCIAL_YEAR=' || l_FINANCIAL_YEAR ||' '
5724 || 'REGISTERED_EMPLOYER=' || l_legal_employer ||' '
5725 || 'IS_TESTING=' || 'Y' ||' '
5726 || 'ARCHIVE_PAYROLL_ACTION=' || to_char(p_payroll_action_id)||' '
5727 || 'END_DATE=' || to_char(l_end_date,'YYYY/MM/DD HH:MI:SS')||' '
5728 || 'PAYMENT_SUMMARY_TYPE=' || 'O'; /* Bug 6470581 */
5729
5730 ps_request_id := fnd_request.submit_request
5731 ('PAY',
5732 'PYAUPSDF',
5733 null,
5734 null,
5735 false,
5736 'ARCHIVE',
5737 'AU_PS_DATA_FILE_VAL', -- Report_format of magtape process
5738 'AU',
5739 to_char(l_start_date,'YYYY/MM/DD HH:MI:SS'),
5740 to_char(l_EFFECTIVE_DATE,'YYYY/MM/DD HH:MI:SS'),
5741 'REPORT',
5742 l_business_group_id,
5743 null,
5744 null,
5745 l_legal_employer,
5746 l_FINANCIAL_YEAR_code,
5747 'END_DATE='||to_char(l_end_date,'YYYY/MM/DD HH:MI:SS'),
5748 'Y', -- IS_TESTING Parameter
5749 'O', /* Bug 6470581 */
5750 'AU_PAYMENT_SUMMARY', /* Bug 6470581 */
5751 to_number(p_payroll_action_id), -- Archive_PAyroll_Action
5752 l_legislative_param -- Legislative parameters
5753 );
5754
5755
5756 END IF;
5757
5758
5759 end spawn_data_file;
5760
5761 end pay_au_payment_summary;
[Home] [Help]