[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
*** in the select statement for employee type (Bug #2512431)
*** 26 Sep 02 kaverma 1.53 Modified cursor employer_details and etp_code. Also
*** introduced a cursor balance_exists (Bug No 2581436 )
*** 24 Oct 02 srrajago 1.54 Modified the cursor process_assignments (Bug No : 2574186)
*** 28 Oct 02 srrajago 1.55 Included action_type 'I' for 'Balance Initialization' in cursor
*** process_assignments (Bug No : 2574186)
*** 01 Nov 02 shoskatt 1.56 Changed the etp_code to check for action type and added the function
*** to check for the existence of Lumpsum C value. balance_exists cursor
*** has been changed. (Bug #2646912)
*** 18 Nov 02 Ragovind 1.57 Modified the cursor Get_Allowance_Balances for Performance Improvement for Bug#2665475.
*** 01 Dec 02 Apunekar 1.58 Modified cursor employee_details for Bug#2689175
*** 01 Dec 02 Apunekar 1.59 Added nocopy
*** 16 Jan 03 Apunekar 1.61 Modified process_assignments,employee_details cursor and
*** archive_etp_payment_details procedure (Bug 2574186)
*** 30 Jan 03 Kaverma 1.62 Modified 'process_assignments' cursor 'not exists' clause (Bug 2777142)
*** 30 Jan 03 Apunekar 1.63 Modified cursors employee_details and etp_details for bug 2774577
*** 18 Feb 03 Ragvoind 1.64 Add Join for Period of service ID to the Employee_details Cursor. Bug#2786146
*** 25 Feb 03 Ragovind 1.65 Modified the process_assignments cursor to avoid MERGE-JOIN-CARTESIAN Bug#2786835.
*** 04 Mar 03 Kaverma 1.66 Modified procedure 'archive_prepost_details' to use Lump Sum C Payment balance
*** to get pre and post 83 values (Bug No : 2826802)
*** 24 Mar 03 Kaverma 1.67 Modified cursor etp_code (Bug No : 2856638)
*** 02 May 03 Nanuradh 1.68 Modified the proceduce assignment_action_code by adding temporay check.
*** Excluded processing all the employees, where lump sum E payment exists
*** after 01-JUL-2003 (Bug: 2822446)
*** 02 May 03 Nanuradh 1.69 Modified the cursor c_employee_number to improve the performance.(Bug: 2822446)
*** 05 May 03 Nanuradh 1.70 Modified the cursor c_employee_number.
*** 05 May 03 Nanuradh 1.71 Corrected the message to be printed in the out file.
*** 05 May 03 Apunekar 1.72 Bug2855658 - Fixed for Retro Allowances
*** 05 May 03 Apunekar 1.73 Bug2855658 - Fixed for Performance
*** 15 May 03 Ragovind 1.74 Bug2819479 - ETP Pre/Post Enhancement.
*** 21 May 03 Apunekar 1.75 Bug2968127 - Cleared PL/SQL allowances table after processing.
*** 27 May 03 Apunekar 1.76 Bug2977533 - Ordered addresses by date_from.
*** 27-Jun-03 Hnainani 11591.5 Bug#3019374 Removed FBT check from Main Query
*** 28-Jun-03 SRussell 11591.7 Bug#3019374 Replaced process_Assignments
cursor after discussion with core.
*** 28-Jun-03 SRussell 1.83 Copied branched version 11591.7 into main code.
*** 28-Jun-03 SRussell 11592.2 Branched code. Put hints in
process_assignments cursor.
*** 04-Jul-03 Apunekar 11592.3 Added check to get latest person record in financial year in process_assignments
*** 04-Jul-03 Apunekar 115.84 Bug3019374 Changed process_assignments,etp_code,Get_retro_Entry_ids cursors , included branched version changes in mainline code.
*** 16-Jul-03 Apunekar 115.85 Includes fix for 3048724
*** 17-Jul-03 Apunekar 115.86 Bug3019374 Modified Cursor employee_details for performance fix.
*** 23 Jul 03 Nanuradh 115.87 Bug#2984390 - Added an extra parameter to the function call etp_prepost_ratios - ETP Pre/post Enhancement
*** 29 Jul 03 Nanuradh 115.88 Bug#2881272 - Modified the proceduce assignment_action_code by removing the temporary check.
*** Included processing of all the employees, where Lump sum E payment exists after 01-JUL-2003.
*** 28-Jul-03 Apunekar 115.90 Bug3073082 - Cursor employee_details removed date formatting in decode.
*** 29-Jul-03 Nanuradh 115.91 Bug#2881272 - Deleted the commented code in process_assignments function.
*** 28-Jul-03 Apunekar 115.92 Bug#3075153 - Modified employee_details
*** 04-Aug-03 Apunekar 115.93 Bug#3077528 - Backed out previous changes made
*** 06-Aug-03 Apunekar 115.94 Bug#3043049 - Used secured views in range_code and process_assignments for security
*** 06-Aug-03 Apunekar 115.95 Bug#3043049 - Used secured views in process_assignments_only for security
*** 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
*** otherwise it is included in Lump sum E payment.
*** 21-AUG-03 punmehta 115.97 Bug#3095919 - Modified the Cursor c_get_pay_earned_date to fetch effective_date instead of date_earned
*** 22-AUG-03 punmehta 115.98 Bug#3095919 - Modified the Cursor name c_get_pay_earned_date
*** to c_get_pay_effective_date and variable name of date_earned to effective_date
*** 22-Nov-03 punmehta 115.99 Bug#3263659 - Modified employee_details and archive code to check for the termination date
*** and added check for 'g_debug' before tracing for performance
*** 11-Dec-03 punmehta 115.100 Bug#3263659 - Archive_code , before calling archive_etp_code put a check for termination date
*** 06-Feb-04 punmehta 115.101 Bug#3245909 - Modified c_get_pay_effective_date cursor to fetch Dates for only master assignment action.
*** 06-Feb-04 punmehta 115.102 Bug#3245909 - Removed unwanted code.
*** 10-Feb-04 punmehta 115.103 Bug#3098353 - Archived a new flag which is false if all the balances are zero.
*** 11-Feb-04 punmehta 115.104 Bug#3098353 - Modified IF caluse for setting employee_end date.
*** 11-Feb-04 punmehta 115.105 Bug#3098353 - Renamed variables
*** 12-Feb-04 punmehta 115.106 Bug#3132178 - New procedure for calling magtape process.
*** 13-Feb-04 punmehta 115.107 Bug#3132178 - Coding Standards, changed SELECT to cursor
*** 18-Feb-04 jkarouza 115.08 Bug#3172963 - Use of BBR to retrieve balances wherever possible.
*** 02-Apr-04 punmehta 115.109 Bug#3549553 - Modified Union cursor and FBT cursor for balances.
*** 24-Apr-04 puchil 115.110 Bug#3586388 - Changed the cursor etp_code and removed gscc warnings.
*** 07-May-04 avenkatk 115.111 Bug#3580487 - Modified Union Cursor,removed call to hr_aubal.calc_all_balances.
*** 28-MAY-04 punmehta 115.112 Bug#3642409 - Added new index INDEX(a per_assignments_f_N12) to cursor process_assignments.
*** 28-MAY-04 punmehta 115.112 Bug#3642409 - Removed Rownum from cursor process_assignments.
*** 03-JUN-04 abhkumar 115.113 Bug#3661230 - Modified the process assignment cursor to take all the employees for archiving purpose.
*** 21-JUN-04 srrajago 115.118 Bug#3701869 - Modified the cursor csr_get_bbr_aseq to fetch max action_sequence instead of
*** assignment_action_id. Introduced cursor csr_get_bbr_assignment_action to pass the
*** correct assignment_action_id based on action_sequence to pay_balance_pkg.Also handled
*** the parameter Last Year Termination value setting if it is not enabled.
*** 21-JUN-04 srrajago 115.119 Bug#3701869 - Modified cursors 'csr_get_bbr_aseq' and 'csr_get_bbr_asg_actions'. Handled the parameter
*** Last Year Termination value setting if it is not enabled in another place which was
*** missed in the previous fix.
*** 25-JUN-04 srrajago 115.120 Bug#3603495 - Performance Fix - Modified cursors 'c_get_pay_effective_date' and 'etp_BA_or_BI'.
*** Introduced per_assignments_f table and its joins.
*** 03-JUL-04 srrajago 115.122 Bug#3743010 - Reverted back the fix in the previous version fixed for Bug: 3728357. This is same as
*** the version 115.20
*** 05-JUL-04 srrajago 115.123 Bug#3603495 - Performance Fix - Modified cursor 'c_get_pay_effective_date'.
*** 05-JUL-04 punmehta 115.124 Bug#3744930 - Modified for Re-hire
*** 05-JUL-04 punmehta 115.125 Bug#3755305 - Modified to get the action_id based on max action_squence
*** 09 Aug 04 abhkumar 115.126 Bug2610141 - Legal Employer Enhancement
*** 12 Aug 04 abhkumar 115.127 Bug2610141 - Modified code so that legal employer end date is not archived twice.
*** 06 Oct 04 avenkatk 115.128 Bug#3815301 - Modified cursor process_assignments and process_assignments_only for Payroll Updation.
*** 12 Oct 04 avenkatk 115.129 Bug#3815301 - Modified cursor process_assignments and process_assignments_only for better performance.
*** 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,
has zero balances in the current year.
*** 14 Dec 04 ksingla 115.131 Bug#3937976 - Removed redundant code,defaulted the parameter l_curr_term_0_bal_flag to 'NO'.
*** 15 Dec 04 hnainani 115.132 Bug#4015082 - Changes to archive Workplace Giving Deductions
*** 23 Dec 04 abhkumar 115.133 Bug#4063321 - Fixed issues related to Terminated employees, allowance details and LE dates
*** 24 Dec 04 abhkumar 115.134 Bug#4063321 - Removed GSCC errors
*** 30 Dec 04 ksingla 115.135 Bug#4000955 - Modified subquery of process_assignments and process_assignments_only not to archive employees for
any legal employer if Manual PS is issued for 'ALL' legal employers or without any legal employer.
*** 30 Dec 04 avenkatk 115.136 Bug#3899641 - Functional Dependancy Comment Added.
*** 07 Feb 05 ksingla 115.137 Bug#4161460 Modified the cursor get_allowance_balances
*** 12 Feb 05 abhargav 115.138 bug#4174037 Modified the cursor get_allowance_balances to avoid the unnecessary get_value() call.
*** 17 Feb 05 abhkumar 115.139 Bug#4161460 Rolled back changes made in 115.137
*** 16 Mar 05 ksingla 115.140 Bug#4177679 Modified the subquery of employee_details to archive correct employee le_start_date
when legal employer is changed .
*** 17 Mar 05 ksingla 115.141 Bug#4177679 Modified the subquery of employee_details to archive correct employee le_start_date
when person details are modified .
*** 31 Mar 05 ksingla 115.142 Bug#4177679 Modified the subquery of employee_details to archive correct employee le_end_date
when "Leave Loading" segment is modified.
*** 11 Apr 05 ksingla 115.143 Bug#4278361 Modified the cursor etp_paid. Added a new table pay_payrolls_f for performance issues.
*** 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.
*** 13 Apr 05 ksingla 115.145 Bug#4278379 Modified the cursor get_retro_entry_ids for performance.
*** 14 Apr 05 ksingla 115.146 Bug#4278379 Rolled back the changes done to cursor get_retro_entry_ids.
*** 18 Apr 05 ksingla 115.147 Bug#4278299 Modified the cursor get_allowance_balances for performance.
*** 19 Apr 05 ksingla 115.148 Bug#4281290 Modified the cursor etp_code for performance.
*** 20 Apr 05 ksingla 115.149 Bug#4177679 Modified for etp employee start date.
*** 25 Apr 05 ksingla 115.150 Bug#4278299 Rolled back the changes done in version 115.147.
*** 05 May 05 abhkumar 115.151 Bug#4377367 Added join in the cursor process_assignments to archive the end-dated employees.
*** 24 May 05 abhargav 115.152 Bug#4363057 Changes due to Retro Tax enhancement.
*** 24 May 05 abhargav 115.152 Bug#4387183 Modified file to to archive employee details for FBT employees.
Included the fix for Bug# 4375020
and included action_type 'V' in cursor csr_get_dates
*** 20 Jul 05 abhkumar 115.153 Bug#4418107 Modified call to pay_au_paye_ff.get_retro_period in adjust_retro_allowances
*** 08-AUG-05 hnainani 115.154 Bug#3660322 Added Quotes around Extra Information Query (-999) to not erro out for Character values
*** 02-OCT-05 abhkumar 115.156 Bug#4653934 Modified assignment action code to pick those employees who do have payroll attached
at start of the financial year but not at the end of financial year.
*** 15-Nov-05 avenkatk 115.157 Bug#4738470 Change cursor for Maximum assignment_action_id.
*** 02-DEC-05 abhkumar 115.158 Bug#4701566 Modified the cursor get_allowance_balances to get allowance value for end-dated
employees and also improve the performance of the query.
*** 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.
*** 06-Dec-05 ksingla 115.160 Bug#4866415 Removed round for l_pre01jul1983_value and l_post30jun1983_value
*** 06-DEC095 avenkatk 115.161 Bug#4866934 Initialized balance values to 0 for FBT Employee
*** 04-Jan-06 ksingla 115.162 Bug#4925650 Modified cursor c_get_effective_date to resolve performance issues.
*** 04-Jan-06 ksingla 115.163 Bug#4926521 Modified cursor process_assignments to resolve performance issues.
*** 03-Mar-06 abhkumar 115.164 Bug#5075662 Modified for etp employee start date.
*** 16-Mar-06 ksingla 115.165 Bug#5099419 Modified to round off correctly.
*** 21-Mar-06 ksingla 115.166 Bug#5099419 Removed changes done for bug 4926521
*** 20-Jun-06 ksingla 115.167 Bug#5333143 Add_months included to fetch FBT_RATE and MEDICARE_LEVY
*** 29-Jun-06 avenkatk 115.168 Bug#5364017 Added check for "Generic" Address Style in Employee Address.
*** 03-Jul-06 avenkatk 115.169 Bug#5367061 ETP Start Date with be ETP Service Date entered else Hire Date. Backed out Fix 4177679.
*** 11-Aug-06 hnainani 115.170 Bug#5395393 Modified the v_lst_year_start variable to be assigned to financial year start
*** instead of FBT year Start. This was done to keep it consistent with the
*** Archive_code procedure and End Of Year Reconciliation Reports.
*** 01-Sep-06 sclarke 115.172 Bug#4925547 Altered archive of allowances and unions to support 2006/2007 layout.
*** 05-Oct-06 priupadh 115.174 The file is now dual maintained ,R12 version will be in Sync.
*** 06-Oct-06 hnainani 115.179 Bug# 5377624 Modified cursor c_get_pay_Effective_date to link Time Period Id with Date_Earned
*** to get the assignment action id
***
*** 10-Oct-06 abhargav 115.180 Bug#4925547 Added bug references for changes done under Bug#4925547.
*** 17-Nov-06 abhargav 115.181 Bug#5591993 Modified cursor CSR_UNIONS_2006 to avoid MJC.
*** 20-Nov-06 sclarke 115.182 Bug#5666937 Enabled the 'order by' of the where clause of get_allowance_balances
*** cursor.
*** 28-Nov-06 sclarke 115.183 Bug#5679568 Procedure archive_2006_unions -
*** Handled case where both old and new unions exists but number of new unions is < 4
*** 19-Dec-06 ksingla 115.184 Bug#5708255 Added code to get value of global FBT_THRESHOLD
*** 27-Dec-06 ksingla 115.185 Bug#5708255 Added to_number to all occurrences of g_fbt_threshold
*** 8-Jan-06 ksingla 115.186 Bug#5743196 Added nvl to cursor Get_Retro_allowances
*** 23-Mar-07 ksingla 115.187 Bug#5371102 Modified for performance fixes
*** 26-Apr-07 sbaburao 115.188 Bug#5846278 Modified the function adjust_retro_allowance for Enhanced Retropay
*** 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'
*** 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
*** 24-May-07 priupadh 115.191 Bug#6069614 Removed the if conditions which checks the death benefit type other then 'Dependent'
*** 01-Jun-07 tbakashi 115.192 Bug#6086060 Added trunc function for union values and allowance values.
*** 09-Jun-07 tbakashi 115.193 Bug#6086060 Added trunc in archive_limited_values and removed in archive_allowance_details
*** 10-Jun-07 priupadh 115.194 Bug#6112527 Added the changes removed in Bug#6069614 , with condition that for death benefit type Dependent
*** only archive if Fin Year is 2007/2008 or greater .
*** 14-Jun-07 tbakashi 115.195 Bug#6086060 Removing the trunc's added as they create inconsistency of value in data file.
*** 23-Aug-07 avenkatk 115.196 Bug#5371102 Modified Cursor csr_get_dates for performance.
*** 03-Sep-07 priupadh 115.197 Bug#6192381 For multiple ETP enh ,modified initialization_code,archive_code,archive_prepost_details
*** archive_etp_details and added procedure adjust_old_etp_values
*** 06-Sep-07 priupadh 115.198 Bug#6192381 Modified the Balance Names for Invalidity Component,removed commented code from archive_prepost_details
*** 07-Sep-07 priupadh 115.199 Bug#6192381 Removed multiple comments tab in one line to avoid chksql error
*** 07-Jan-08 avenkatk 115.200 Bug#6470581 Added Changes for supporting Amended payment summaries
*** 23-Jan-08 avenkatk 115.201 Bug#6740581 Resolved GSCC Errors
*** 30-May-08 priupadh 115.202 Bug#7135544 Added NVL (N) to variables lv_transitional_flag and lv_part_prev_etp_flag
*** if Null values are returned from cursor.
*** 18-Jun-08 avenkatk 115.203 Bug#7138494 Added Changes for RANGE_PERSON_ID
*** 26-Jun-08 priupadh 115.204 Bug#7171534 Added t_allowance_balance.count > 0 in adjust_retro_allowances
*** 07-Jul-08 avenkatk 115.205 Bug#7234263 Modified archive_employee_details for End assignments
*** 16-Jul-08 avenkatk 115.206 Bug#7242551 Modified archive_employee_details for fetching correct TFN
*** 25-Nov-08 skshin 115.207 Bug#7571001 Added changes in archive_allowance_detils and adjust_retro_allowances for Balance Attribute reporting
*** 25-Nov-08 skshin 115.208 Bug#7571001 Modified Cursor Get_retro_Entry_ids and Cursor Get_Retro_allowances to avoid duplcate joins
*** 16-Apr-09 skshin 115.209 Bug#8423565 Modified archive_union_name to display Miscellaneous when 'Balance Initialization 4' is used.
*** 28-Apr-09 pmatamsr 115.210 Bug#8441044 Cursor c_get_pay_effective_date is modified to consider Lump Sum E payments for payment summary gross calculation
*** for action types 'B' and 'I'.
*** 13-May-09 pmatamsr 115.211 Bug#8315198 Modified initialization_code,archive_etp_details,archive_employee_details,archive_balance_details and
*** archive_code procedures as part of FY2009 Payment Summary changes.
*** 18-May-09 pmatamsr 115.212 Bug#8315198 Modified archive_etp_details,archive_employee_details and archive_code procedures such that the new DB items
*** added for FY2009 Payment Summary enhancement are archived only if FY is 2009 or greater.
*** 23-Jun-09 pmatamsr 115.213 Bug#8587013 Modified initialization_code,archive_balance_details and archive_code procedures to remove references to
*** Other Income balance.
*** 03-Aug-09 dduvvuri 115.214 Bug#5008855 Modified cursors etp_details and csr_union_fees to add a join on per_periods_of _service
*** 07-Sep-09 pmatamsr 115.215 Bug#8769345 Modified initialization_code and archive_prepost_details such that the new balances addeed are used
** during the pre 83 and post 83 components archival process.
*** 19-Nov-09 skshin 115.218 Bug#8711855 Moved single Lump Sum E adjustment logic to new function get_lumpsumE_value.
*** Modifed archive_balance_details procedure to call get_lumpsumE_value function and include additional Lump Sum E balances to existing Lump Sum E calculation.
*** 15-Dec-09 pmatamsr 115.219 Bug#9190980 Modified Lump Sum E adjustment logic for Retro GT12 Pre Tax Deductions in get_lumpsumE_value function.
*** 13-Jan-09 pmatamsr 115.220 Bug#9226023 Added logic to support the calculation of taxable and tax free portions of ETP for the terminated employees processed
*** before applying the patch 8769345.
*** 04-May-10 skshin 115.223 Bug#9675451 Restored the missing code to assign v_earnings_ytd and v_lump_sum_E_ytd to v_bal_value in archive_balance_details.
*** 05-Jul-10 avenkatk 115.226 Bug#9872891 For employee terminated in previous year with ETP pay this year - modified the ETP Pay Date and LE End Date archived
*** 06-Jul-10 skshin 115.227 Bug#9147430 Added Foreign Workers
*** 23-Aug-10 skshin 115.228 Bug#10048508 Repositioned to get g_business_group_id corectly and added to check FW allowance/union plsql table values exist.
*** 31-Aug-10 dduvvuri 115.229 Bug#10064551 Modified select column for middle_name in cursor employee_details in procedure archive_employee_details
and in cursor etp_details in procedure archive_etp_details.
*** 04-Sep-10 skshin 115.230 Bug#10084818 Initialized l_union_count, l_total_value, and l_alw_total for FW2 payment summary
*** 09-Sep-10 skshin 115.232 Bug#10100047 Added p_fw_alw_limit_tab and removed cdep_ytd value from g_net_balance.
*** 18-Oct-10 skshin 115.233 Bug#10143762 Adjusted Exempt Foreign Income from Gross_Earnings for both INB and FW type.
*** 22-Nov-10 skshin 115.234 Bug#10216064 LT12_Curr retro and LT12_Curr retro Tax are to be reported on each type of payment summary based on
*** 9950136 assignment type of original period. The other retros and associated retro Taxes are to be reported on INB payment summary.
*** 30-Nov-10 avenkatk 115.235 Bug#10348688 Corrected parameters in call to get_foreign_payment_amount
*** 01-Dec-10 avenkatk 115.236 Bug#10331262 Added new procedure -get_foreign_leave_payments. Made changes for FW Termination payment reporting
*** 13-May-11 skshin 115.237 Bug#12400935 Modified range_process_assignments cursor and added additional joins for better performance
*** 01-June-11 scireddy 115.238 Bug# 12584258 Changes done in archive_etp_payment_details procedure to provide the actual_termination_date for ETP_Payment_date,
*** if there are no Prepayments after balance adjustments.
*** 02-Jun-11 scireddy 115.239 Bug#12584258 Modified the Else clause in 'archive_etp_payment_details' procedure and added a join condition in 'act_term_date' cursor.
*** 06-Jun-11 scireddy 115.241 Bug# 12625929 Changes are done to the Else clause in 'archive_etp_payment_details' procedure.
*** 07-Jul-11 dduvvuri 115.243 Bug# 12725161 This version is a rollback of 115.242 version with the bug 12698821 fixed in a different way to avoid eoy related issues
*** 27-Jul-11 rrajaman 115.244 Bug#12744254 Added context check in cursors referring to assignment EIT for HR_PS_ISSUE_DATE_AU
*** 26-Sep-11 dduvvuri 115.245 Bug#12400821 Performance improvements in EXISTS clause in all 3 assignment_action_code cursors done for westpac customer.
*** 28-Oct-11 dduvvuri 115.246 Bug#9846470 Modified the archiving logic for X_EMPLOYEE_LE_END_DATE archive item for FBT only payments for active and
*** terminated employees
*** 06-Dec-11 prasrang 115.247 Bug#13043341 Added cursors range_process_assignment_set and process_assignment_set in assignment_action_code procedure to
*** handle Assignment Set parameter.
*** Bug#13043357 Changed the parameter to cursor tfn_number in procedure archive_employee_details.
*** 16-Dec-11 prasrang 115.248 Bug#13043357 Parameter l_date_earned used with Cursor tfn_number in procedure archive_employee_details if no value
*** returned using p_year_end parameter.
*** 02-Feb-12 skshin 115.249 Bug#13362286 Add Retro Earnings Additional GT12 balance for Lump Sum E and foreign worker
*** 10-May-12 skshin 115.250 Bug#14060570 Adjusted the negative retro allowance greater than 12 months
*** 07-Dec-12 skshin 115.252 Bug#14703826 Modifed to archive the new ETP balances
*** -------------------------------------------------------------------------------------------------------+
*/
g_debug boolean;
p_sql := ' select distinct p.person_id' ||
' from per_people_f p,' ||
' pay_payroll_actions pa' ||
' where pa.payroll_action_id = :payroll_action_id' ||
' and p.business_group_id = pa.business_group_id' ||
' order by p.person_id';
select decode(pbt.balance_name, 'CDEP',1
, 'Leave Payments Marginal',2
, 'Lump Sum A Deductions',3
, 'Lump Sum A Payments',4
, 'Lump Sum B Deductions',5
, 'Lump Sum B Payments',6
, 'Lump Sum C Deductions',7
, 'Lump Sum C Payments',8
, 'Lump Sum D Payments',9
, 'Total_Tax_Deductions',10
, 'Termination Deductions',11
, 'Exempt Foreign Employment Income',12 /* 8315198, 8587013 */
, 'Union Fees',13
, 'Invalidity Payments',14
, 'Lump Sum E Payments',15
, 'Earnings_Total', 16
, 'Workplace Giving Deductions' , 17
, 'ETP Deductions Transitional Not Part of Prev Term',18 /* Begin 6192381 */
, 'ETP Deductions Transitional Part of Prev Term',19
, 'ETP Deductions Life Benefit Not Part of Prev Term',20
, 'ETP Deductions Life Benefit Part of Prev Term',21
, 'Invalidity Payments Life Benefit Not Part of Prev Term',22
, 'Invalidity Payments Life Benefit Part of Prev Term',23
, 'Invalidity Payments Transitional Not Part of Prev Term',24
, 'Invalidity Payments Transitional Part of Prev Term',25 /*4015082 ,6192381 */
, 'Reportable Employer Superannuation Contributions',26 /*Bug 8315198 ,8587013 */
, 'Retro Earnings Leave Loading GT 12 Mths Amount', 27 -- start 8711855
, 'Retro Earnings Spread GT 12 Mths Amount', 28
, 'Retro Pre Tax GT 12 Mths Amount', 29
, 'Foreign Leave Payments',30 /* Start Bug 10331262 */
, 'Foreign Leave Payments Marginal',31
, 'Foreign Lump Sum A Payments',32
, 'Foreign Leave Component Deduction',33
, 'Foreign Lump Sum A Deduction',34
, 'Retro Earnings Additional GT 12 Mths Amount',35 -- bug 13362286
, 'ETP Deductions Excluded',36 /* start bug 14703826 */
, 'ETP Deductions Excluded Part of Prev',37
, 'ETP Deductions Non Excluded',38
, 'ETP Deductions Non Excluded Part of Prev',39 /* end bug 14703826 */
) sort_index, -- end 8711855
pdb.defined_balance_id defined_balance_id
from pay_balance_types pbt
, pay_balance_dimensions pbd
, pay_defined_balances pdb
where pbt.balance_name in ( 'CDEP'
, 'Leave Payments Marginal'
, 'Lump Sum A Deductions'
, 'Lump Sum A Payments'
, 'Lump Sum B Deductions'
, 'Lump Sum B Payments'
, 'Lump Sum C Deductions'
, 'Lump Sum C Payments'
, 'Lump Sum D Payments'
, 'Total_Tax_Deductions'
, 'Termination Deductions'
, 'Exempt Foreign Employment Income' /* 8315198 ,8587013 */
, 'Union Fees'
, 'Invalidity Payments'
, 'Lump Sum E Payments'
, 'Earnings_Total'
, 'Workplace Giving Deductions'
, 'ETP Deductions Transitional Not Part of Prev Term' /* Begin 6192381 */
, 'ETP Deductions Transitional Part of Prev Term'
, 'ETP Deductions Life Benefit Not Part of Prev Term'
, 'ETP Deductions Life Benefit Part of Prev Term'
, 'Invalidity Payments Life Benefit Not Part of Prev Term'
, 'Invalidity Payments Life Benefit Part of Prev Term'
, 'Invalidity Payments Transitional Not Part of Prev Term'
, 'Invalidity Payments Transitional Part of Prev Term' /* 4015082 , End 6192381 */
, 'Reportable Employer Superannuation Contributions' /*Bug 8315198*/
, 'Retro Earnings Leave Loading GT 12 Mths Amount' -- start 8711855
, 'Retro Earnings Spread GT 12 Mths Amount'
, 'Retro Pre Tax GT 12 Mths Amount'
, 'Foreign Leave Payments'
, 'Foreign Leave Payments Marginal'
, 'Foreign Lump Sum A Payments'
, 'Foreign Leave Component Deduction'
, 'Foreign Lump Sum A Deduction'
, 'Retro Earnings Additional GT 12 Mths Amount' -- bug 13362286
, 'ETP Deductions Excluded'
, 'ETP Deductions Excluded Part of Prev'
, 'ETP Deductions Non Excluded'
, 'ETP Deductions Non Excluded Part of Prev'
) -- end 8711855
and pbd.database_item_suffix = '_ASG_LE_YTD' --2610141
and pbt.balance_type_id = pdb.balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pbt.legislation_code = 'AU'
order by sort_index;
select decode(pbt.balance_name, 'ETP Payments Transitional Not Part of Prev Term',1
, 'ETP Payments Transitional Part of Prev Term',2
, 'ETP Payments Life Benefit Not Part of Prev Term',3
, 'ETP Payments Life Benefit Part of Prev Term',4
, 'ETP Tax Free Payments Transitional Not Part of Prev Term',5
, 'ETP Taxable Payments Transitional Not Part of Prev Term',6
, 'ETP Tax Free Payments Transitional Part of Prev Term',7
, 'ETP Taxable Payments Transitional Part of Prev Term',8
, 'ETP Tax Free Payments Life Benefit Not Part of Prev Term',9
, 'ETP Taxable Payments Life Benefit Not Part of Prev Term',10
, 'ETP Tax Free Payments Life Benefit Part of Prev Term',11
, 'ETP Taxable Payments Life Benefit Part of Prev Term',12
, 'Lump Sum C Payments',13) sort_index,
pdb.defined_balance_id defined_balance_id
from pay_balance_types pbt
, pay_balance_dimensions pbd
, pay_defined_balances pdb
where pbt.balance_name in ('ETP Payments Transitional Not Part of Prev Term'
,'ETP Payments Transitional Part of Prev Term'
,'ETP Payments Life Benefit Not Part of Prev Term'
,'ETP Payments Life Benefit Part of Prev Term'
,'Lump Sum C Payments'
,'ETP Tax Free Payments Transitional Not Part of Prev Term'
,'ETP Taxable Payments Transitional Not Part of Prev Term'
,'ETP Tax Free Payments Transitional Part of Prev Term'
,'ETP Taxable Payments Transitional Part of Prev Term'
,'ETP Tax Free Payments Life Benefit Not Part of Prev Term'
,'ETP Taxable Payments Life Benefit Not Part of Prev Term'
,'ETP Tax Free Payments Life Benefit Part of Prev Term'
,'ETP Taxable Payments Life Benefit Part of Prev Term')
and pbd.database_item_suffix = '_ASG_LE_YTD'
and pbt.balance_type_id = pdb.balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pbt.legislation_code = 'AU'
order by sort_index;
select decode(pbt.balance_name, 'ETP Tax Free Payments Excluded',1
, 'ETP Taxable Payments Excluded',2
, 'Invalidity Payments',3
, 'ETP Deductions Excluded',4
, 'ETP Deductions Excluded Part of Prev',5) sort_index,
pdb.defined_balance_id defined_balance_id
from pay_balance_types pbt
, pay_balance_dimensions pbd
, pay_defined_balances pdb
where pbt.balance_name in (
'ETP Tax Free Payments Excluded'
, 'ETP Taxable Payments Excluded'
, 'Invalidity Payments'
, 'ETP Deductions Excluded'
, 'ETP Deductions Excluded Part of Prev')
and pbd.database_item_suffix = '_ASG_LE_RUN'
and pbt.balance_type_id = pdb.balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pbt.legislation_code = 'AU'
order by sort_index;
select decode(pbt.balance_name, 'ETP Tax Free Payments Non Excluded',1
, 'ETP Taxable Payments Non Excluded',2
, 'ETP Deductions Non Excluded',3
, 'ETP Deductions Non Excluded Part of Prev', 4) sort_index,
pdb.defined_balance_id defined_balance_id
from pay_balance_types pbt
, pay_balance_dimensions pbd
, pay_defined_balances pdb
where pbt.balance_name in (
'ETP Tax Free Payments Non Excluded'
, 'ETP Taxable Payments Non Excluded'
, 'ETP Deductions Non Excluded'
, 'ETP Deductions Non Excluded Part of Prev')
and pbd.database_item_suffix = '_ASG_LE_RUN'
and pbt.balance_type_id = pdb.balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pbt.legislation_code = 'AU'
order by sort_index;
select balance_dimension_id
from pay_balance_dimensions pbd
where pbd.dimension_name = '_ASG_LE_YTD'
and pbd.legislation_code = g_legislation_code;
SELECT balance_type_id from pay_balance_types
WHERE balance_name='Union Fees'
and legislation_code='AU';
SELECT global_value
FROM ff_globals_f
WHERE global_name = 'FBT_THRESHOLD'
AND legislation_code = 'AU'
AND c_year_end BETWEEN effective_start_date
AND effective_end_date;
SELECT to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY') Financial_year_end
,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa.legislative_parameters) registered_employer /*bug9147430*/
,ppa.business_group_id
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = c_payroll_Action_id;
select attribute_id
from PAY_BAL_ATTRIBUTE_DEFINITIONS
where attribute_name = c_attribute_name
;
SELECT decode(pbt.balance_name,
'Lump Sum E Payments', 1
,'Retro Earnings Leave Loading GT 12 Mths Amount', 2
,'Retro Earnings Spread GT 12 Mths Amount', 3
,'Retro Pre Tax GT 12 Mths Amount', 4
,'Retro Earnings Additional GT 12 Mths Amount', 5) sort_index -- bug 13362286
, pdb.defined_balance_id defined_balance_id
FROM pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
WHERE pbt.legislation_code = 'AU'
AND pbt.balance_name in ( 'Lump Sum E Payments'
,'Retro Earnings Leave Loading GT 12 Mths Amount'
,'Retro Earnings Spread GT 12 Mths Amount'
,'Retro Pre Tax GT 12 Mths Amount'
,'Retro Earnings Additional GT 12 Mths Amount') -- bug 13362286
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbd.dimension_name = '_ASG_LE_PTD'
order by sort_index;
select balance_type_id
from pay_balance_types
where balance_name = 'Fringe Benefits'
and legislation_code = 'AU';
select decode(pbt.balance_name,
'Earnings_Total',1
, 'Leave Payments Marginal',2
, 'Workplace Giving Deductions',3
, 'Lump Sum E Payments',4
, 'Retro Earnings Leave Loading GT 12 Mths Amount',5
, 'Retro Earnings Spread GT 12 Mths Amount',6
, 'Retro Pre Tax GT 12 Mths Amount',7
, 'Total_Tax_Deductions',8
, 'Termination Deductions',9
, 'Lump Sum C Deductions',10
, 'Foreign Tax Deductions',11
, 'Lump Sum A Payments',12
, 'Lump Sum D Payments',13
, 'Reportable Employer Superannuation Contributions',14
, 'Union Fees',15
, 'CDEP',16
, 'Exempt Foreign Employment Income',17
, 'Retro LT 12 Mths Prev Yr Amount', 18
, 'Retro Earnings Leave Loading LT 12 Mths Prev Yr Amount', 19
, 'Retro Earnings Spread LT 12 Mths Prev Yr Amount', 20
, 'Retro Pre Tax LT 12 Mths Prev Yr Amount', 21
, 'Retro LT 12 Mths Curr Yr Amount', 22
, 'Retro Earnings Leave Loading LT 12 Mths Curr Yr Amount', 23
, 'Retro Earnings Spread LT 12 Mths Curr Amount', 24
, 'Retro Tax GT12 Amount', 25
, 'Retro Tax LT12 Prev Amount', 26
, 'Retro Tax LT12 Curr Amount', 27
, 'Foreign Leave Payments',28
, 'Retro Earnings Additional GT 12 Mths Amount', 29 -- bug 13362286
, 'Retro Earnings Additional LT12 Prev Mths Amount', 30
, 'Retro Earnings Additional LT12 Curr Mths Amount', 31
) sort_index,
pbt.balance_type_id balance_type_id
from pay_balance_types pbt
where pbt.balance_name in (
'Earnings_Total'
, 'Leave Payments Marginal'
, 'Workplace Giving Deductions'
, 'Lump Sum E Payments'
, 'Retro Earnings Leave Loading GT 12 Mths Amount'
, 'Retro Earnings Spread GT 12 Mths Amount'
, 'Retro Pre Tax GT 12 Mths Amount'
, 'Total_Tax_Deductions'
, 'Termination Deductions'
, 'Lump Sum C Deductions'
, 'Foreign Tax Deductions'
, 'Lump Sum A Payments'
, 'Lump Sum D Payments'
, 'Reportable Employer Superannuation Contributions'
, 'Union Fees'
, 'CDEP'
, 'Exempt Foreign Employment Income' -- bug 10143762
/* start bug 9950136 */
, 'Retro LT 12 Mths Prev Yr Amount'
, 'Retro Earnings Leave Loading LT 12 Mths Prev Yr Amount'
, 'Retro Earnings Spread LT 12 Mths Prev Yr Amount'
, 'Retro Pre Tax LT 12 Mths Prev Yr Amount'
, 'Retro LT 12 Mths Curr Yr Amount'
, 'Retro Earnings Leave Loading LT 12 Mths Curr Yr Amount'
, 'Retro Earnings Spread LT 12 Mths Curr Amount'
, 'Retro Tax GT12 Amount'
, 'Retro Tax LT12 Prev Amount'
, 'Retro Tax LT12 Curr Amount'
/* end bug 9950136 */
, 'Foreign Leave Payments'
, 'Retro Earnings Additional GT 12 Mths Amount'
, 'Retro Earnings Additional LT12 Prev Mths Amount'
, 'Retro Earnings Additional LT12 Curr Mths Amount'
)
and pbt.legislation_code = 'AU'
order by sort_index;
SELECT pbt.balance_type_id,
pbt.balance_name
FROM pay_balance_attributes pba
,pay_defined_balances pdb
,pay_balance_types pbt
,pay_balance_dimensions pbd
WHERE pba.attribute_id = g_attribute_id
AND pdb.defined_balance_id = pba.defined_balance_id
AND pbt.balance_type_id = pdb.balance_type_id
AND pdb.business_group_id = g_business_group_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbd.dimension_name = '_ASG_LE_YTD';
select distinct pbt.balance_type_id
from pay_element_types_f pet
, pay_balance_types pbt
, pay_input_values_f piv
, pay_balance_feeds_f pbf
where pet.element_information_category = 'AU_VOLUNTARY DEDUCTIONS'
and pet.element_information1 = pbt.balance_type_id
and pet.element_type_id = piv.element_type_id
and piv.input_value_id = pbf.input_value_id
and pbf.balance_type_id = g_balance_type_id
and pbt.business_group_id = g_business_group_id;
p_balance_value_tab.delete;
p_lump_sum_E_ptd_tab.delete;
p_etp_balance_value_tab.delete;
p_etp_excl_bal_value_tab.delete;
p_etp_non_excl_bal_value_tab.delete;
p_fw_fbt_bal_type_tab.delete;
p_fw_balance_type_tab.delete;
p_fw_alw_balance_type_tab.delete;
p_fw_union_balance_type_tab.delete;
select parameter_value
from pay_action_parameters
where parameter_name = 'RANGE_PERSON_ID';
select par.parameter_value
from pay_report_format_parameters par,
pay_report_format_mappings_f map
where map.report_format_mapping_id = par.report_format_mapping_id
and map.report_type = 'AU_PAYMENT_SUMMARY'
and map.report_format = 'AU_PAYMENT_SUMMARY'
and map.report_qualifier = 'AU'
and par.parameter_name = 'RANGE_PERSON_ID'; -- Bug fix 5567246
select distinct hasa.include_or_exclude inc_or_exc
from
hr_assignment_set_amendments hasa,
hr_assignment_sets has
where hasa.assignment_set_id = has.assignment_set_id
and has.business_group_id = g_business_group_id
and has.assignment_set_id = v_assignment_set_id;
select to_date('01-07-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') Financial_year_start
,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY') Financial_year_end
,to_date('01-04-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') FBT_year_start
,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') FBT_year_end
,decode(pay_core_utils.get_parameter('EMPLOYEE_TYPE',legislative_parameters),'C','Y','T','N','B','%') Employee_type
,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) Registered_Employer
,pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters) Financial_year
,decode(pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters),null,'%',
pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters)) Assignment_id
,decode(pay_core_utils.get_parameter('PAYROLL',legislative_parameters),null,'%',pay_core_utils.get_parameter('PAYROLL',legislative_parameters)) payroll_id
,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_yr_term /*3661230*/
,pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) Business_group_id
,decode(pay_core_utils.get_parameter('ASSIGNMENT_SET_ID',legislative_parameters),null,'%',
pay_core_utils.get_parameter('ASSIGNMENT_SET_ID',legislative_parameters)) Assignment_set_id /*Bug 13043341*/
from pay_payroll_actions
where payroll_action_id = c_payroll_Action_id;
select /*+ INDEX(p per_people_f_pk)
INDEX(a per_assignments_f_fk1)
INDEX(a per_assignments_f_N12)
INDEX(pa pay_payroll_actions_pk)
INDEX(pps per_periods_of_service_n3)
*/ distinct a.assignment_id
from per_people_f p /*Bug3043049*/
,per_assignments_f a /*Bug3043049*/
,pay_payroll_actions pa
,per_periods_of_service pps
where pa.payroll_action_id = c_payroll_action_id
and p.person_id between c_start_person_id and c_end_person_id
and p.person_id = a.person_id
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
and pps.period_of_service_id = a.period_of_service_id
and a.business_group_id = pa.business_group_id
and to_char(a.assignment_id) like v_assignment_id
and pps.person_id = p.person_id
and nvl(pps.actual_termination_date, v_lst_year_start) >= v_lst_year_start -- Bug3661230, Bug3048724 ,Bug 3263659
and v_fin_year_end between p.effective_start_date and p.effective_end_date
-- 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
and a.effective_end_date = (select max(effective_end_date) /* 4377367 */
From per_assignments_f iipaf
WHERE iipaf.assignment_id = a.assignment_id
and iipaf.effective_end_date >= v_fbt_year_start
and iipaf.effective_start_date <= v_fin_year_end
AND iipaf.payroll_id IS NOT NULL) /*Bug# 4653934*/
and a.payroll_id like v_payroll_id -- Bug 3815301
AND EXISTS
(select /*+ ORDERED */''
from
per_assignments_f paaf
,pay_assignment_actions rpac
,pay_payroll_actions rppa
where rppa.effective_date between v_fin_year_start and v_fin_year_end
and rppa.action_type in ('R','Q','B','I')
and rpac.tax_unit_id = v_reg_emp
and rppa.payroll_action_id = rpac.payroll_action_id
and rpac.action_status ='C'
and rpac.assignment_id = a.assignment_id
and rppa.payroll_id = paaf.payroll_id /*Bug 3815301 */
and paaf.assignment_id = a.assignment_id
and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date /*Bug 3815301 */
UNION
select /*+ ORDERED */''
from
per_assignments_f paaf
,pay_assignment_actions rpac
,pay_payroll_actions rppa
where pps.actual_termination_date between v_lst_fbt_year_start and v_fbt_year_end /*Bug3263659 */ --Bug#3661230
and rppa.effective_date between v_fbt_year_start and v_fbt_year_end
and pay_balance_pkg.get_value(g_fbt_defined_balance_id,rpac.assignment_action_id
+ decode(rppa.payroll_id, 0, 0, 0),v_reg_emp,null,null,null,null) > to_number(g_fbt_threshold) /* Bug 5708255 */ --2610141
and rppa.action_type in ('R','Q','B','I')
and rpac.tax_unit_id = v_reg_emp
and rppa.payroll_action_id = rpac.payroll_action_id
and rpac.action_status ='C'
and rpac.assignment_id = a.assignment_id
and rppa.payroll_id = paaf.payroll_id /*Bug 3815301 */
and paaf.assignment_id = a.assignment_id
and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date /*Bug 3815301 */
)
and not exists
(select distinct paat.assignment_id
from pay_action_interlocks pail,
pay_assignment_actions paat,
pay_payroll_actions paas
where paat.assignment_id = a.assignment_id
and paas.action_type ='X'
and paas.action_status ='C'
and paas.report_type ='AU_PAYMENT_SUMMARY_REPORT'
and pail.locking_action_id = paat.assignment_action_id
and paat.payroll_action_id = paas.payroll_action_id
and pay_core_utils.get_parameter('FINANCIAL_YEAR',paas.legislative_parameters) = v_financial_year
and pay_core_utils.get_parameter('REGISTERED_EMPLOYER',paas.legislative_parameters) = v_reg_emp
)
and not exists ( select aei_information1
from per_assignment_extra_info,
hr_lookups
where assignment_id = a.assignment_id
and aei_information_category = 'HR_PS_ISSUE_DATE_AU' --Bug 12744254
and aei_information1 is not null
and aei_information1 = lookup_code
and nvl(aei_information2,v_reg_emp) = decode(aei_information2,'-999',aei_information2,v_reg_emp) -- 2610141 and 4000955
and lookup_type ='AU_PS_FINANCIAL_YEAR'
and meaning = v_financial_year
);
select distinct a.assignment_id
from per_people_f p /*Bug3043049*/
,per_assignments_f a /*Bug3043049*/
,pay_payroll_actions pa
,per_periods_of_service pps
where pa.payroll_action_id = c_payroll_action_id
and p.person_id between c_start_person_id and c_end_person_id
and p.person_id = a.person_id
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
and pps.period_of_service_id = a.period_of_service_id
and a.business_group_id = pa.business_group_id
and a.assignment_id = v_assignment_id
and pps.person_id = p.person_id
and nvl(pps.actual_termination_date, v_lst_year_start) >= v_lst_year_start -- Bug3661230 , Bug3048724, Bug 3263659
and v_fin_year_end between p.effective_start_date and p.effective_end_date
-- 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
and a.effective_end_date = (select max(effective_end_date) /* 4377367 */
From per_assignments_f iipaf
WHERE iipaf.assignment_id = a.assignment_id
and iipaf.effective_end_date >= v_fbt_year_start
and iipaf.effective_start_date <= v_fin_year_end
AND iipaf.payroll_id IS NOT NULL) /*Bug# 4653934*/
and a.payroll_id like v_payroll_id -- Bug 3815301
AND EXISTS
(select /*+ ORDERED */''
from
per_assignments_f paaf
,pay_assignment_actions rpac
,pay_payroll_actions rppa
where rppa.effective_date between v_fin_year_start and v_fin_year_end
and rppa.action_type in ('R','Q','B','I')
and rpac.tax_unit_id = v_reg_emp
and rppa.payroll_action_id = rpac.payroll_action_id
and rpac.action_status ='C'
and rpac.assignment_id = v_assignment_id
and rppa.payroll_id = paaf.payroll_id /*Bug 3815301 */
and paaf.assignment_id = v_assignment_id
and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date /*Bug 3815301 */
UNION
select /*+ ORDERED */''
from
per_assignments_f paaf
,pay_assignment_actions rpac
,pay_payroll_actions rppa
where pps.actual_termination_date between v_lst_fbt_year_start and v_fbt_year_end /*Bug3263659 */ --Bug#3661230
and rppa.effective_date between v_fbt_year_start and v_fbt_year_end
and pay_balance_pkg.get_value(g_fbt_defined_balance_id,rpac.assignment_action_id
+ decode(rppa.payroll_id, 0, 0, 0),v_reg_emp,null,null,null,null) > to_number(g_fbt_threshold) /* Bug 5708255 */ --2610141
and rppa.action_type in ('R','Q','B','I')
and rpac.tax_unit_id = v_reg_emp
and rppa.payroll_action_id = rpac.payroll_action_id
and rpac.action_status ='C'
and rpac.assignment_id = v_assignment_id
and rppa.payroll_id = paaf.payroll_id /*Bug 3815301 */
and paaf.assignment_id = v_assignment_id
and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date /*Bug 3815301 */
)
and not exists
(select distinct paat.assignment_id
from pay_action_interlocks pail,
pay_assignment_actions paat,
pay_payroll_actions paas
where paat.assignment_id = a.assignment_id
and paas.action_type ='X'
and paas.action_status ='C'
and paas.report_type ='AU_PAYMENT_SUMMARY_REPORT'
and pail.locking_action_id = paat.assignment_action_id
and paat.payroll_action_id = paas.payroll_action_id
and pay_core_utils.get_parameter('FINANCIAL_YEAR',paas.legislative_parameters) = v_financial_year
and pay_core_utils.get_parameter('REGISTERED_EMPLOYER',paas.legislative_parameters) = v_reg_emp) --2610141
and not exists ( select aei_information1
from per_assignment_extra_info,
hr_lookups
where assignment_id = a.assignment_id
and aei_information_category = 'HR_PS_ISSUE_DATE_AU' --Bug 12744254
and aei_information1 is not null
and aei_information1 = lookup_code
and nvl(aei_information2,v_reg_emp) = decode(aei_information2,'-999',aei_information2,v_reg_emp) -- 2610141 and 4000955
and lookup_type ='AU_PS_FINANCIAL_YEAR'
and meaning = v_financial_year);
SELECT a.assignment_id
FROM per_people_f p /*Bug3043049*/
,per_assignments_f a /*Bug3043049*/
,pay_payroll_actions pa
,per_periods_of_service pps
,pay_population_ranges ppr
WHERE pa.payroll_action_id = c_payroll_action_id
AND pa.payroll_action_id = ppr.payroll_action_id
AND ppr.payroll_action_id = c_payroll_action_id
AND ppr.chunk_number = c_chunk
AND p.person_id = ppr.person_id
AND p.person_id = a.person_id
AND a.person_id = ppr.person_id
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
AND pps.period_of_service_id = a.period_of_service_id
AND a.business_group_id = pa.business_group_id
AND to_char(a.assignment_id) LIKE v_assignment_id
AND pps.person_id = p.person_id
AND nvl(pps.actual_termination_date, v_lst_year_start) >= v_lst_year_start -- Bug3661230, Bug3048724 ,Bug 3263659
AND v_fin_year_end BETWEEN p.effective_start_date AND p.effective_end_date
AND a.effective_end_date = (SELECT MAX(effective_end_date) /* 4377367 */
FROM per_assignments_f iipaf
WHERE iipaf.assignment_id = a.assignment_id
AND iipaf.person_id = a.person_id
AND iipaf.effective_end_date >= v_fbt_year_start
AND iipaf.effective_start_date <= v_fin_year_end
AND iipaf.payroll_id IS NOT NULL) /*Bug# 4653934*/
AND a.payroll_id LIKE v_payroll_id -- Bug 3815301
AND EXISTS
(select /*+ ORDERED */''
from
per_assignments_f paaf
,pay_assignment_actions rpac
,pay_payroll_actions rppa
where rppa.effective_date between v_fin_year_start and v_fin_year_end
and rppa.action_type in ('R','Q','B','I')
and rpac.tax_unit_id = v_reg_emp
and rppa.payroll_action_id = rpac.payroll_action_id
and rpac.action_status ='C'
and rpac.assignment_id = a.assignment_id
and rppa.payroll_id = paaf.payroll_id /*Bug 3815301 */
and paaf.assignment_id = a.assignment_id
and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date /*Bug 3815301 */
UNION
select /*+ ORDERED */''
from
per_assignments_f paaf
,pay_assignment_actions rpac
,pay_payroll_actions rppa
where pps.actual_termination_date between v_lst_fbt_year_start and v_fbt_year_end /*Bug3263659 */ --Bug#3661230
and rppa.effective_date between v_fbt_year_start and v_fbt_year_end
and pay_balance_pkg.get_value(g_fbt_defined_balance_id,rpac.assignment_action_id
+ decode(rppa.payroll_id, 0, 0, 0),v_reg_emp,null,null,null,null) > to_number(g_fbt_threshold) /* Bug 5708255 */ --2610141
and rppa.action_type in ('R','Q','B','I')
and rpac.tax_unit_id = v_reg_emp
and rppa.payroll_action_id = rpac.payroll_action_id
and rpac.action_status ='C'
and rpac.assignment_id = a.assignment_id
and rppa.payroll_id = paaf.payroll_id /*Bug 3815301 */
and paaf.assignment_id = a.assignment_id
and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date /*Bug 3815301 */
)
AND NOT EXISTS
(SELECT paat.assignment_id
FROM pay_action_interlocks pail,
pay_assignment_actions paat,
pay_payroll_actions paas
WHERE paat.assignment_id = a.assignment_id
AND paas.action_type ='X'
AND paas.action_status ='C'
AND paas.report_type ='AU_PAYMENT_SUMMARY_REPORT'
AND pail.locking_action_id = paat.assignment_action_id
AND paat.payroll_action_id = paas.payroll_action_id
AND pay_core_utils.get_parameter('FINANCIAL_YEAR',paas.legislative_parameters) = v_financial_year
AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER',paas.legislative_parameters) = v_reg_emp
)
AND NOT EXISTS
( SELECT aei_information1
FROM per_assignment_extra_info,
hr_lookups
WHERE assignment_id = a.assignment_id
AND aei_information_category = 'HR_PS_ISSUE_DATE_AU' --Bug 12744254
AND aei_information1 IS NOT NULL
AND aei_information1 = lookup_code
AND nvl(aei_information2,v_reg_emp) = decode(aei_information2,'-999',aei_information2,v_reg_emp) -- 2610141 and 4000955
AND lookup_type ='AU_PS_FINANCIAL_YEAR'
AND meaning = v_financial_year
);
select pay_assignment_actions_s.nextval
from dual;
SELECT global_value
FROM ff_globals_f
WHERE global_name = 'FBT_THRESHOLD'
AND legislation_code = 'AU'
AND c_year_end BETWEEN effective_start_date
AND effective_end_date ;
select pdb.defined_balance_id
from pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where pbt.balance_name ='Fringe Benefits'
and pbt.balance_type_id = pdb.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id /* Bug 2501105 */
and pbd.legislation_code ='AU'
and pbd.dimension_name ='_ASG_LE_FBT_YTD' --2610141
and pbd.legislation_code = pbt.legislation_code
and pbd.legislation_code = pdb.legislation_code;
SELECT a.assignment_id
FROM per_people_f p
,per_assignments_f a
,pay_payroll_actions pa
,per_periods_of_service pps
,pay_population_ranges ppr
,hr_assignment_set_amendments hasa
WHERE pa.payroll_action_id = c_payroll_action_id
AND pa.payroll_action_id = ppr.payroll_action_id
AND ppr.payroll_action_id = c_payroll_action_id
AND ppr.chunk_number = c_chunk
AND p.person_id = ppr.person_id
AND p.person_id = a.person_id
AND a.person_id = ppr.person_id
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
AND pps.period_of_service_id = a.period_of_service_id
AND a.business_group_id = pa.business_group_id
AND hasa.ASSIGNMENT_SET_ID = to_number(v_assignment_set_id)
AND a.assignment_id = hasa.ASSIGNMENT_ID
AND upper(hasa.INCLUDE_OR_EXCLUDE)='I'
AND pps.person_id = p.person_id
AND nvl(pps.actual_termination_date, v_lst_year_start) >= v_lst_year_start
AND v_fin_year_end BETWEEN p.effective_start_date AND p.effective_end_date
AND a.effective_end_date = (SELECT MAX(effective_end_date) /* 4377367 */
FROM per_assignments_f iipaf
WHERE iipaf.assignment_id = a.assignment_id
AND iipaf.person_id = a.person_id
AND iipaf.effective_end_date >= v_fbt_year_start
AND iipaf.effective_start_date <= v_fin_year_end
AND iipaf.payroll_id IS NOT NULL)
AND a.payroll_id LIKE v_payroll_id
AND EXISTS
(select /*+ ORDERED */''
from
per_assignments_f paaf
,pay_assignment_actions rpac
,pay_payroll_actions rppa
where rppa.effective_date between v_fin_year_start and v_fin_year_end
and rppa.action_type in ('R','Q','B','I')
and rpac.tax_unit_id = v_reg_emp
and rppa.payroll_action_id = rpac.payroll_action_id
and rpac.action_status ='C'
and rpac.assignment_id = a.assignment_id
and rppa.payroll_id = paaf.payroll_id
and paaf.assignment_id = a.assignment_id
and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
UNION
select /*+ ORDERED */''
from
per_assignments_f paaf
,pay_assignment_actions rpac
,pay_payroll_actions rppa
where pps.actual_termination_date between v_lst_fbt_year_start and v_fbt_year_end
and rppa.effective_date between v_fbt_year_start and v_fbt_year_end
and pay_balance_pkg.get_value(g_fbt_defined_balance_id,rpac.assignment_action_id
+ decode(rppa.payroll_id, 0, 0, 0),v_reg_emp,null,null,null,null) > to_number(g_fbt_threshold)
and rppa.action_type in ('R','Q','B','I')
and rpac.tax_unit_id = v_reg_emp
and rppa.payroll_action_id = rpac.payroll_action_id
and rpac.action_status ='C'
and rpac.assignment_id = a.assignment_id
and rppa.payroll_id = paaf.payroll_id /*Bug 3815301 */
and paaf.assignment_id = a.assignment_id
and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date /*Bug 3815301 */
)
AND NOT EXISTS
(SELECT paat.assignment_id
FROM pay_action_interlocks pail,
pay_assignment_actions paat,
pay_payroll_actions paas
WHERE paat.assignment_id = a.assignment_id
AND paas.action_type ='X'
AND paas.action_status ='C'
AND paas.report_type ='AU_PAYMENT_SUMMARY_REPORT'
AND pail.locking_action_id = paat.assignment_action_id
AND paat.payroll_action_id = paas.payroll_action_id
AND pay_core_utils.get_parameter('FINANCIAL_YEAR',paas.legislative_parameters) = v_financial_year
AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER',paas.legislative_parameters) = v_reg_emp
)
AND NOT EXISTS
( SELECT aei_information1
FROM per_assignment_extra_info,
hr_lookups
WHERE assignment_id = a.assignment_id
AND aei_information_category = 'HR_PS_ISSUE_DATE_AU'
AND aei_information1 IS NOT NULL
AND aei_information1 = lookup_code
AND nvl(aei_information2,v_reg_emp) = decode(aei_information2,'-999',aei_information2,v_reg_emp)
AND lookup_type ='AU_PS_FINANCIAL_YEAR'
AND meaning = v_financial_year
);
select /*+ INDEX(p per_people_f_pk)
INDEX(a per_assignments_f_fk1)
INDEX(a per_assignments_f_N12)
INDEX(pa pay_payroll_actions_pk)
INDEX(pps per_periods_of_service_n3)
*/ distinct a.assignment_id
from per_people_f p /*Bug3043049*/
,per_assignments_f a /*Bug3043049*/
,pay_payroll_actions pa
,per_periods_of_service pps
,hr_assignment_set_amendments hasa
where pa.payroll_action_id = c_payroll_action_id
and p.person_id between c_start_person_id and c_end_person_id
and p.person_id = a.person_id
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
and pps.period_of_service_id = a.period_of_service_id
and a.business_group_id = pa.business_group_id
AND hasa.ASSIGNMENT_SET_ID = to_number(v_assignment_set_id)
AND a.assignment_id = hasa.ASSIGNMENT_ID
AND upper(hasa.INCLUDE_OR_EXCLUDE)='I'
and pps.person_id = p.person_id
and nvl(pps.actual_termination_date, v_lst_year_start) >= v_lst_year_start -- Bug3661230, Bug3048724 ,Bug 3263659
and v_fin_year_end between p.effective_start_date and p.effective_end_date
-- 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
and a.effective_end_date = (select max(effective_end_date) /* 4377367 */
From per_assignments_f iipaf
WHERE iipaf.assignment_id = a.assignment_id
and iipaf.effective_end_date >= v_fbt_year_start
and iipaf.effective_start_date <= v_fin_year_end
AND iipaf.payroll_id IS NOT NULL) /*Bug# 4653934*/
and a.payroll_id like v_payroll_id -- Bug 3815301
AND EXISTS
(select /*+ ORDERED */''
from
per_assignments_f paaf
,pay_assignment_actions rpac
,pay_payroll_actions rppa
where rppa.effective_date between v_fin_year_start and v_fin_year_end
and rppa.action_type in ('R','Q','B','I')
and rpac.tax_unit_id = v_reg_emp
and rppa.payroll_action_id = rpac.payroll_action_id
and rpac.action_status ='C'
and rpac.assignment_id = a.assignment_id
and rppa.payroll_id = paaf.payroll_id /*Bug 3815301 */
and paaf.assignment_id = a.assignment_id
and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date /*Bug 3815301 */
UNION
select /*+ ORDERED */''
from
per_assignments_f paaf
,pay_assignment_actions rpac
,pay_payroll_actions rppa
where pps.actual_termination_date between v_lst_fbt_year_start and v_fbt_year_end /*Bug3263659 */ --Bug#3661230
and rppa.effective_date between v_fbt_year_start and v_fbt_year_end
and pay_balance_pkg.get_value(g_fbt_defined_balance_id,rpac.assignment_action_id
+ decode(rppa.payroll_id, 0, 0, 0),v_reg_emp,null,null,null,null) > to_number(g_fbt_threshold) /* Bug 5708255 */ --2610141
and rppa.action_type in ('R','Q','B','I')
and rpac.tax_unit_id = v_reg_emp
and rppa.payroll_action_id = rpac.payroll_action_id
and rpac.action_status ='C'
and rpac.assignment_id = a.assignment_id
and rppa.payroll_id = paaf.payroll_id /*Bug 3815301 */
and paaf.assignment_id = a.assignment_id
and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date /*Bug 3815301 */
)
and not exists
(select distinct paat.assignment_id
from pay_action_interlocks pail,
pay_assignment_actions paat,
pay_payroll_actions paas
where paat.assignment_id = a.assignment_id
and paas.action_type ='X'
and paas.action_status ='C'
and paas.report_type ='AU_PAYMENT_SUMMARY_REPORT'
and pail.locking_action_id = paat.assignment_action_id
and paat.payroll_action_id = paas.payroll_action_id
and pay_core_utils.get_parameter('FINANCIAL_YEAR',paas.legislative_parameters) = v_financial_year
and pay_core_utils.get_parameter('REGISTERED_EMPLOYER',paas.legislative_parameters) = v_reg_emp
)
and not exists ( select aei_information1
from per_assignment_extra_info,
hr_lookups
where assignment_id = a.assignment_id
and aei_information_category = 'HR_PS_ISSUE_DATE_AU' --Bug 12744254
and aei_information1 is not null
and aei_information1 = lookup_code
and nvl(aei_information2,v_reg_emp) = decode(aei_information2,'-999',aei_information2,v_reg_emp) -- 2610141 and 4000955
and lookup_type ='AU_PS_FINANCIAL_YEAR'
and meaning = v_financial_year
);
select PAYROLL_ID into v_payroll_id from HR_ASSIGNMENT_SETS where ASSIGNMENT_SET_ID=v_assignment_set_id;
SELECT count(*) INTO v_count FROM hr_assignment_set_amendments
WHERE ASSIGNMENT_SET_ID=v_assignment_set_id AND ASSIGNMENT_ID=csr_rec.assignment_id;
SELECT count(*) INTO v_count FROM hr_assignment_set_amendments
WHERE ASSIGNMENT_SET_ID=v_assignment_set_id AND ASSIGNMENT_ID=process_rec.assignment_id;
select max(effective_start_date)
from per_all_people_f p
where person_id =p_person_id ;
select max(effective_start_date)
from per_all_assignments_f p
where assignment_id =p_asg_id ;
select fue.user_entity_id,
dbi.data_type
from ff_user_entities fue,
ff_database_items dbi
where user_entity_name =c_user_entity_name
and fue.user_entity_id =dbi.user_entity_id;
hr_utility.set_location('Amended Payment Summary - Update the archive PL/SQL table',2000);
hr_utility.set_location('Updated Index '||i_index,2010);
select pay_balance_pkg.get_value(pdb.defined_balance_id,
p_max_assignment_action_id,
p_registered_employer,
null,null,null,null) --2610141
from pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where pbt.balance_name = c_balance_name
and pbt.legislation_code = 'AU'
and pbt.balance_type_id = pdb.balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pbd.dimension_name = '_ASG_LE_FBT_YTD';
SELECT global_value
,data_type
FROM ff_globals_f
WHERE global_name = c_name
AND legislation_code = 'AU'
AND c_year_end BETWEEN effective_start_date
AND effective_end_date ;
l_fw_fbt_output_tab.delete;
t_fw_gross_type.delete;
f_fw_date_tab.delete;
j_fw_date_tab.delete;
SELECT /*+ ORDERED */ pee.element_entry_id element_entry_id,
ppa.date_earned date_earned,
pee.assignment_id assignment_id,
pac.tax_unit_id, /* Added for bug #5846278 */
pdb.balance_type_id
FROM per_all_assignments_f paa
,per_periods_of_service pps
,pay_assignment_actions pac
,pay_payroll_actions ppa
,pay_element_entries_f pee
,pay_run_results prr
,pay_element_types_f pet
/* below added for bug 7571001 */
,PAY_BAL_ATTRIBUTE_DEFINITIONS pbad
, PAY_BALANCE_ATTRIBUTES pba
,pay_defined_balances pdb
,pay_balance_dimensions pbd
,PAY_BALANCE_FEEDS_F pbf
,pay_input_values_f piv
WHERE paa.assignment_id = c_assignment_id
/* start added for bug 7571001 */
AND pbad.attribute_name = 'AU_EOY_ALLOWANCE'
AND pbad.legislation_code = 'AU'
AND pac.assignment_id = c_assignment_id
AND pbad.attribute_id = pba.attribute_id
AND pba.defined_balance_id = pdb.defined_balance_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pbd.dimension_name = '_ASG_LE_YTD'
and pbd.legislation_code = 'AU'
AND pdb.balance_type_id = pbf.balance_type_id
AND pbf.input_value_id = piv.input_value_id
AND piv.element_type_id = pet.element_type_id
/* end added for bug 7571001 */
AND pps.PERIOD_OF_SERVICE_ID = paa.PERIOD_OF_SERVICE_ID
AND NVL(pps.actual_termination_date,c_year_end)
BETWEEN paa.effective_start_date AND paa.effective_end_date
AND pac.payroll_action_id = ppa.payroll_Action_id
AND pac.assignment_id = paa.assignment_id
AND pac.tax_unit_id = p_registered_employer --2610141
AND ppa.effective_date BETWEEN c_year_start AND c_year_end /*bug 4063321*/
AND pac.assignment_Action_id = prr.assignment_Action_id
AND prr.element_type_id=pet.element_type_id
AND pee.element_entry_id=prr.source_id
AND pee.creator_type in ('EE','RR')
AND pee.assignment_id = paa.assignment_id /*Added for bug3019374*/
AND ppa.action_status='C'
AND pac.action_status='C'
AND ppa.date_earned between pee.effective_start_date and pee.effective_end_date
AND ppa.date_earned BETWEEN pet.effective_start_date AND pet.effective_end_date
AND ppa.date_earned between pbf.effective_start_date and pbf.effective_end_date
AND ppa.date_earned between piv.effective_start_date and piv.effective_end_date
;
select NVL(pbt.reporting_name,pbt.balance_name) balance_name /* Bug 5743196 Added nvl */
,prv.result_value balance_value
from
pay_element_entries_f pee,
pay_run_results prr,
pay_run_result_values prv,
pay_element_types_f pet,
pay_balance_types pbt
/* below added for bug 7571001 */
,PAY_BALANCE_FEEDS_F pbf
,pay_input_values_f piv
where
pee.element_entry_id=c_element_entry_id
and prv.run_result_id=prr.run_result_id
AND pee.element_entry_id=prr.source_id
AND prr.element_type_id=pet.element_type_id
/* start added for bug 7571001 */
AND pbt.balance_type_id = c_balance_type_id
AND pbt.balance_type_id = pbf.balance_type_id
AND pbf.input_value_id = piv.input_value_id
AND piv.element_type_id = pet.element_type_id
/* end added for bug 7571001 */
AND pee.effective_start_date between pet.effective_start_date and pet.effective_end_date
AND pee.effective_start_date between pbf.effective_start_date and pbf.effective_end_date
AND pee.effective_start_date between piv.effective_start_date and piv.effective_end_date
;
SELECT plr.rule_mode
FROM pay_legislation_rules plr
WHERE plr.legislation_code = 'AU'
AND plr.rule_type ='ADVANCED_RETRO';
t_ret_allowances.delete;
t_ret_allowances.delete;
SELECT TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
,ptp.start_date
,ptp.end_date
,MAX(paa.action_sequence) action_sequence
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf,
per_time_periods ptp
WHERE paa.assignment_id = paf.assignment_id
AND paf.assignment_id = c_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
AND ppa.payroll_id = paf.payroll_id
AND paf.payroll_id = ptp.payroll_id
AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
AND ppa.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paa.action_status = 'C'
AND paa.tax_unit_id = c_tax_unit_id
AND ptp.start_date <= c_end_date
AND ptp.end_date >= c_start_date
GROUP BY paa.assignment_id, ptp.time_period_id, ptp.start_date,ptp.end_date
ORDER BY ptp.start_date;
SELECT paf.assignment_id
,paf.effective_start_date
,paf.effective_end_date
,paf.payroll_id
,hsc.segment1 tax_unit_id
FROM per_assignments_f paf
,hr_soft_coding_keyflex hsc
WHERE paf.assignment_id = c_assignment_id
AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
AND hsc.segment1 LIKE c_tax_unit_id
AND to_char(paf.assignment_status_type_id) NOT LIKE c_f_status_id
AND to_char(paf.assignment_status_type_id) NOT LIKE c_j_status_id
AND paf.effective_start_date <= c_end_date
AND paf.effective_end_date >= c_start_date
ORDER BY paf.effective_start_date ASC;
SELECT pdb.defined_balance_id
,decode(pbt.balance_name, 'Foreign Leave Payments',1
, 'Earnings_Total',2
, 'Standard_Tax_Deductions',3
, 'Foreign Worker Days',4
) sort_index
FROM pay_defined_balances pdb
,pay_balance_dimensions pbd
,pay_balance_types pbt
WHERE pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbd.dimension_name = '_ASG_LE_PTD'
AND pbd.legislation_code = 'AU'
AND pdb.balance_type_id = pbt.balance_type_id
AND pbt.legislation_code = 'AU'
AND pbt.balance_name IN (
'Foreign Leave Payments'
,'Earnings_Total'
,'Standard_Tax_Deductions'
,'Foreign Worker Days')
ORDER BY sort_index;
l_result_table.DELETE;
select NVL(pbt.reporting_name,pbt.balance_name) balance_name
from pay_defined_balances pdb,
pay_balance_types pbt
where pdb.defined_balance_id = c_defined_balance_id
and pdb.balance_type_id = pbt.balance_type_id
and pdb.business_group_id = g_business_group_id;
select distinct pet.reporting_name
, pet.element_information_category
, pet.element_information1
from pay_balance_types pbt
, pay_balance_feeds_f pbf
, pay_input_values_f piv
, pay_element_types_f pet
, pay_element_entries_f pee
, pay_element_links_f pel
, per_all_people_f pap
, per_periods_of_service pps
, per_all_assignments_f paa
where pet.element_type_id = piv.element_type_id
and pbf.input_value_id = piv.input_value_id
and pbf.balance_type_id = pbt.balance_type_id
and pet.element_type_id = pel.element_type_id
and pel.element_link_id = pee.element_link_id
and pee.assignment_id = c_assignment_id
and pee.assignment_id = paa.assignment_id
and paa.person_id = pap.person_id
and paa.person_id = pps.person_id
and paa.period_of_service_id = pps.period_of_service_id
and pbt.balance_name = 'Union Fees'
and pbt.legislation_code = g_legislation_code
and pet.effective_start_date = (select max(et.effective_start_date )
from pay_element_types_f et
where et.element_type_id= pet.element_type_id
and c_fw_period_end between pet.effective_Start_date and pet.effective_end_date
);
p_fw_union_tab_sorted.delete;
p_fw_alw_tab_sorted.delete;
f_fw_date_tab_g.delete;
j_fw_date_tab_g.delete;
l_fw_output_tab.delete;
l_fw_union_output_tab.delete;
p_fw_union_tab.delete;
t_union_bal_sort.delete;
SELECT CAST (MULTISET (SELECT rec_au_eoy_bal_type(balance_name, balance_value)
FROM TABLE(t_union_bal_sort)
ORDER BY balance_value desc)
AS tab_au_eoy_bal_type)
INTO t_union_bal_sort
FROM dual;
l_fw_alw_output_tab.delete;
p_fw_allowance_tab.delete;
t_alw_bal_sort.delete;
p_fw_alw_tab.delete;
p_fw_alw_limit_tab.delete;
SELECT CAST (MULTISET (SELECT rec_au_eoy_bal_type(balance_name, balance_value)
FROM TABLE(t_alw_bal_sort)
ORDER BY balance_value desc)
AS tab_au_eoy_bal_type)
INTO t_alw_bal_sort
FROM dual;
SELECT pdb.defined_balance_id
,NVL(pbt.reporting_name,pbt.balance_name) balance_name
,pay_balance_pkg.get_value(pdb.defined_balance_id
,p_max_assignment_action_id
,p_registered_employer
,NULL,NULL,NULL,NULL,NULL,NULL,NULL) balance_value
FROM pay_balance_attributes pba
,pay_defined_balances pdb
,pay_balance_types pbt
,pay_balance_dimensions pbd
WHERE pba.attribute_id = g_attribute_id
AND pdb.defined_balance_id = pba.defined_balance_id
AND pbt.balance_type_id = pdb.balance_type_id
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.business_group_id = g_business_group_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pbd.dimension_name = '_ASG_LE_YTD'
ORDER BY 3 DESC
;
SELECT CAST (MULTISET (SELECT rec_au_eoy_bal_type(balance_name, balance_value)
FROM TABLE(t_alw_bal_sort)
WHERE balance_value > 0
ORDER BY balance_value desc)
AS tab_au_eoy_bal_type)
INTO t_alw_bal_sort
FROM dual;
t_allowance_balance.delete;
t_allowance_balance.delete; /* 2968127- cleared PL/SQL table */
select prv.result_value
,ppa.payroll_action_id
,pac.assignment_action_id
,ppa.effective_date
from pay_element_types_f pet
,pay_input_values_f piv
,per_all_assignments_f paa
,pay_run_results prr
,pay_run_result_values prv
,pay_assignment_actions pac
,pay_payroll_actions ppa
,pay_payrolls_f papf /* bug Number 4278361 */
where pet.element_type_id = piv.element_type_id
and pet.element_name = 'ETP on Termination'
and piv.name = 'Pay ETP Components'
and paa.assignment_id = c_assignment_id
and prv.input_value_id = piv.input_value_id
and prr.element_type_id = pet.element_type_id
and prr.run_result_id = prv.run_result_id
and prr.assignment_action_id = pac.assignment_action_id
and pac.assignment_id = paa.assignment_id
and pac.payroll_action_id = ppa.payroll_action_id
and paa.effective_start_date between pet.effective_start_date
and pet.effective_end_date
and paa.effective_start_date between piv.effective_start_date
and piv.effective_end_date
and papf.payroll_id=paa.payroll_id
and ppa.payroll_id=papf.payroll_id
and ppa.action_type in ('R','Q','I','B','V') /* bug Number 4278361 */
and ppa.effective_date between papf.effective_start_date and papf.effective_end_date
and ppa.effective_date between paa.effective_start_date
and paa.effective_end_date
and ppa.effective_date between c_year_start
and c_year_end
and pac.tax_unit_id = p_registered_employer; --2610141
is select to_char(pppa.effective_date,'DDMMYYYY')
from pay_action_interlocks pai
,pay_assignment_actions pac
,pay_payroll_actions ppa
,pay_assignment_actions ppac
,pay_payroll_actions pppa
where pac.payroll_action_id = ppa.payroll_action_id
and pac.assignment_action_id = c_assignment_action_id
and pac.assignment_action_id = pai.locked_action_id
and ppa.payroll_action_id = c_payroll_action_id
and ppac.assignment_action_id =pai.locking_Action_id
and pppa.payroll_Action_id = ppac.payroll_Action_id
and ppa.effective_date between c_year_start
and c_year_end;
is select max(ppa.payroll_action_id) payroll_action_id
,max(pac.assignment_action_id) assignment_action_id
from per_assignments_f paf
,pay_assignment_actions pac
,pay_payroll_actions ppa
where pac.assignment_id = c_assignment_id
and pac.tax_unit_id = p_registered_employer --2610141
and paf.assignment_id = pac.assignment_id
and ppa.action_type in ('B','I')
and pac.payroll_action_id = ppa.payroll_action_id
and pac.action_status = 'C'
and ppa.action_status = 'C'
and ppa.payroll_id = paf.payroll_id /* Added for bug 5371102 for performance*/
and ppa.date_earned between paf.effective_start_date and paf.effective_end_date /* Added for bug 5371102 for performance*/
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
and ppa.effective_date between c_year_start
and c_year_end ;
is select to_char(pps.actual_termination_date,'DDMMYYYY')
from per_all_assignments_f paa,
per_periods_of_service pps
where paa.person_id = pps.person_id
and paa.period_of_service_id = pps.period_of_service_id
and paa.assignment_id = c_assignment_id
and pps.actual_termination_date between paa.effective_start_date
and paa.effective_end_date
and pps.actual_termination_date between c_year_start
and c_year_end ;
select distinct nvl(pbt.reporting_name, pbt.balance_name) balance_name
, pay_balance_pkg.get_value(pdb.defined_balance_id, p_max_assignment_action_id, p_registered_employer,null,null,null,null) balance_value
, pdb.defined_balance_id def_id
, pbt.balance_type_id bal_type_id
from pay_element_types_f pet
, per_all_assignments_f paa
, pay_balance_types pbt
, pay_defined_balances pdb
, pay_payroll_actions ppa
, pay_assignment_actions pac
, pay_run_results prr
, pay_balance_feeds_f pbf
, pay_input_values_f piv
where pac.assignment_id = p_assignment_id
and pac.tax_unit_id = p_registered_employer
and paa.assignment_id = pac.assignment_id
and paa.assignment_id = p_assignment_id
and pac.payroll_action_id = ppa.payroll_Action_id
and ppa.effective_date between p_year_start and p_year_end
and ppa.payroll_id = paa.payroll_id
and ppa.action_type in ('Q','R','B','I','V')
and pac.assignment_action_id = prr.assignment_action_id
and prr.element_type_id = pet.element_type_id
and pet.element_information_category = 'AU_VOLUNTARY DEDUCTIONS'
and pet.element_information1 = pbt.balance_type_id
and pbt.balance_type_id = pdb.balance_type_id(+)
and pdb.balance_dimension_id(+) = g_dimension_id
and ppa.effective_date between paa.effective_start_date and paa.effective_end_date
and ppa.date_earned between pet.effective_start_date and pet.effective_end_date
and pet.element_type_id = piv.element_type_id
and prr.element_type_id = piv.element_type_id
and ppa.date_earned between piv.effective_start_date and piv.effective_end_date
and piv.input_value_id = pbf.input_value_id
and ppa.date_earned between pbf.effective_start_date and pbf.effective_end_date
and pbf.balance_type_id = c_balance_type
order by 2 desc ;
SELECT CAST (MULTISET (SELECT rec_au_eoy_bal_type(balance_name, balance_value)
FROM TABLE(t_union_bal_sort)
WHERE balance_value > 0
ORDER BY balance_value desc)
AS tab_au_eoy_bal_type)
INTO t_union_bal_sort
FROM dual;
t_union_table.delete;
t_union_table.delete;
select distinct pet.reporting_name
, pet.element_information_category
, pet.element_information1
from pay_balance_types pbt
, pay_balance_feeds_f pbf
, pay_input_values_f piv
, pay_element_types_f pet
, pay_element_entries_f pee
, pay_element_links_f pel
, per_all_people_f pap
, per_periods_of_service pps
, per_all_assignments_f paa
where pet.element_type_id = piv.element_type_id
and pbf.input_value_id = piv.input_value_id
and pbf.balance_type_id = pbt.balance_type_id
and pet.element_type_id = pel.element_type_id
and pel.element_link_id = pee.element_link_id
and pee.assignment_id = c_assignment_id
and pee.assignment_id = paa.assignment_id
and paa.person_id = pap.person_id
and paa.person_id = pps.person_id
and paa.period_of_service_id = pps.period_of_service_id
and pbt.balance_name = 'Union Fees'
and pbt.legislation_code = g_legislation_code
and pet.effective_start_date = (select max(et.effective_start_date )
from pay_element_types_f et
where et.element_type_id= pet.element_type_id
and nvl(pps.actual_termination_date,c_year_end)
between pet.effective_Start_date and pet.effective_end_date
);
SELECT pdb.defined_balance_id
FROM pay_balance_types pbt
,pay_defined_balances pdb
,pay_balance_dimensions pbd
WHERE pbt.balance_name = 'Union Fees'
AND pbt.legislation_code = 'AU'
AND pbd.legislation_code = 'AU'
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbd.dimension_name = '_ASG_LE_YTD'; --2610141*/
select pev.screen_entry_value tax_file_number
,pap.last_name employee_last_name
,pap.first_name employee_first_name
,substr(pap.middle_names, 1, decode(instr(pap.middle_names,' '), 0, 60, instr(pap.middle_names,' ',1)-1)) employee_middle_name --Bug 10064551
,pad.address_line1 employee_address_1
,pad.address_line2 employee_address_2
,pad.address_line3 employee_address_3
,pad.town_or_city employee_suburb
,pad.region_1 employee_state
,pad.postal_code employee_postcode
,fta.territory_short_name employee_country
,pad.style address_style -- Bug 5364017
,pad.country address_country -- Bug 5364017
,pap.employee_number employee_number
,to_char(pap.date_of_birth,'DDMMYYYY') employee_date_of_birth
,to_char(pps.date_start,'DDMMYYYY') employee_start_date
,pps.pds_information2 death_benefit_type
,nvl(to_char(pps.actual_termination_date,'DDMMYYYY'),'31124712') employee_termination_date
,decode(pps.pds_information1, 'AU_D','Y', 'N') death_benefit/*bug#1955993*/
,pad.date_from date_from/*Bug 2977533 */
,decode(pps.pds_information1, 'AU_D',nvl(pps.pds_information11,'000 000 000'),pev.screen_entry_value) etp_death_benefit_tfn /*Bug#8315198*/
from hr_organization_information hoi,
hr_organization_units hou,
hr_soft_coding_keyflex hsc,
pay_element_types_f pet,
pay_input_values_f piv,
pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_entry_values_f pev,
per_all_assignments_f paa,
per_all_people_f pap,
per_addresses pad,
fnd_territories_tl fta,
per_periods_of_service pps,
pay_payroll_actions ppa,
pay_assignment_actions pac,
hr_locations_all hlc /* Bug No : 2263587 */
where hou.business_group_id = c_business_group_id
and hou.organization_id = c_registered_employer
and ppa.action_type = 'X'
and hou.organization_id = hoi.organization_id
and hoi.org_information_context = 'AU_LEGAL_EMPLOYER'
and hou.business_group_id = pap.business_group_id
and hsc.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
and pet.element_name = 'Tax Information'
and piv.name = 'Tax File Number'
and pet.element_type_id = piv.element_type_id
and pet.element_type_id = pel.element_type_id
and pel.element_link_id = pee.element_link_id
and pee.element_entry_id = pev.element_entry_id
and piv.input_value_id = pev.input_value_id
and paa.assignment_id = pee.assignment_id
and pap.person_id = paa.person_id
and pap.person_id = pad.person_id(+)
and pad.primary_flag(+) = 'Y' /*Added for bug 2774577*/
and fta.territory_code(+) = pad.country
and fta.language(+) = userenv('LANG')
and paa.location_id = hlc.location_id(+)
and pap.person_id = pps.person_id
and paa.period_of_service_id = pps.period_of_service_id
and ppa.payroll_action_id = pac.payroll_action_id
and paa.assignment_id = pac.assignment_id
and pac.assignment_id = c_assignment_id
and pac.payroll_action_id = c_payroll_action_id
and hsc.segment1 = c_registered_employer /*Bug 2610141, Bug 4063321*/
and pps.actual_termination_date between paa.effective_start_date
and paa.effective_end_date
and pps.actual_termination_date between pap.effective_start_date
and pap.effective_end_date
and pps.actual_termination_date between pel.effective_start_date
and pel.effective_end_date
and pps.actual_termination_date between pee.effective_start_date
and pee.effective_end_date
and pps.actual_termination_date between pet.effective_start_date
and pet.effective_end_date
and pps.actual_termination_date between pev.effective_start_date
and pev.effective_end_date
and pps.actual_termination_date between piv.effective_start_date
and piv.effective_end_date
and pps.actual_termination_date between p_lst_year_start /*Bug3661230*/
and c_year_end
order by pad.date_from desc;/*Bug 2977533 */
select prv.result_value INPUT_VALUE,piv.name INPUT_NAME
from pay_element_types_f pet
,pay_input_values_f piv
,per_all_assignments_f paa
,pay_run_results prr
,pay_run_result_values prv
,pay_assignment_actions pac
,pay_payroll_actions ppa
where pet.element_type_id = piv.element_type_id
and pet.element_name = 'ETP on Termination'
and piv.name in ('Transitional ETP','Part of Previously Paid ETP')
and paa.assignment_id = c_assignment_id
and prv.input_value_id = piv.input_value_id
and prr.element_type_id = pet.element_type_id
and prr.run_result_id = prv.run_result_id
and prr.assignment_action_id = pac.assignment_action_id
and pac.assignment_id = paa.assignment_id
and pac.payroll_action_id = ppa.payroll_action_id
and paa.effective_start_date between pet.effective_start_date and pet.effective_end_date
and paa.effective_start_date between piv.effective_start_date and piv.effective_end_date
and ppa.action_type in ('R','Q','I','B','V')
and ppa.effective_date between paa.effective_start_date and paa.effective_end_date
and ppa.effective_date between c_year_start and c_year_end
and pac.tax_unit_id = p_registered_employer
order by prr.run_result_id;
p_etp_result_table.delete;
SELECT pet.element_name
,to_char(ppa.effective_date,'DDMMYYYY') run_effective_date
,pac.assignment_action_id
,ppa.payroll_action_id
FROM pay_run_results prr
,per_all_assignments_f paaf
,pay_assignment_actions pac
,pay_payroll_actions ppa
,pay_payrolls_f papf
,pay_element_types_f pet
WHERE pac.assignment_id = p_assignment_id
AND pac.assignment_action_id = prr.assignment_action_id
AND pac.tax_unit_id = p_registered_employer
AND pac.assignment_id = paaf.assignment_id
AND paaf.assignment_id = p_assignment_id
AND pac.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type in ('R','Q','I','B','V')
AND ppa.payroll_id = papf.payroll_id
AND paaf.payroll_id = papf.payroll_id
AND ppa.effective_date between p_year_start and p_year_end
AND prr.status in ('P','PA')
AND prr.element_type_id = pet.element_type_id
AND pet.element_name IN ('ETP Taxable Payments Excluded','ETP Taxable Payments Non Excluded')
AND pet.legislation_code = 'AU'
AND ppa.effective_date between pet.effective_start_date and pet.effective_end_date
AND ppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
order by pet.element_name, pac.assignment_action_id;
select to_char(pppa.effective_date,'DDMMYYYY')
from pay_action_interlocks pai
,pay_assignment_actions pac
,pay_payroll_actions ppa
,pay_assignment_actions ppac
,pay_payroll_actions pppa
where pac.payroll_action_id = ppa.payroll_action_id
and pac.assignment_action_id = c_assignment_action_id
and pac.assignment_action_id = pai.locked_action_id
and ppa.payroll_action_id = c_payroll_action_id
and ppac.assignment_action_id =pai.locking_Action_id
and pppa.payroll_Action_id = ppac.payroll_Action_id
and ppa.effective_date between p_year_start
and p_year_end;
select ppa.payroll_action_id
,pac.assignment_action_id
,to_char(ppa.effective_date,'DDMMYYYY') run_effective_date
from per_assignments_f paf
,pay_assignment_actions pac
,pay_payroll_actions ppa
where pac.assignment_id = p_assignment_id
and pac.tax_unit_id = p_registered_employer
and paf.assignment_id = pac.assignment_id
and paf.assignment_id = p_assignment_id
and ppa.action_type in ('B','I')
and pac.payroll_action_id = ppa.payroll_action_id
and pac.action_status = 'C'
and ppa.action_status = 'C'
and ppa.payroll_id = paf.payroll_id
and ppa.date_earned between paf.effective_start_date and paf.effective_end_date
and (pay_balance_pkg.get_value(c_lsC_payments_def_bal_id, pac.assignment_action_id,p_registered_employer,null,null,null,null)) <> 0
and ppa.effective_date between p_year_start and p_year_end
order by pac.assignment_action_id;
tab_etp_excl_dtls.delete;
tab_etp_excl_pp_dtls.delete;
tab_etp_non_excl_dtls.delete;
tab_etp_non_excl_pp_dtls.delete;
p_etp_excl_result_table.delete;
p_etp_non_excl_result_table.delete;
p_etp_excl_result_table.delete;
p_etp_non_excl_result_table.delete;
tab_etp_excl_arch.delete;
tab_etp_excl_pp_arch.delete;
tab_etp_non_excl_arch.delete;
tab_etp_non_excl_pp_arch.delete;
select hoi.org_information1 group_act_no
,hoi.org_information3 business_name
,hoi.org_information4 trading_name
,hoi.org_information12 abn
,hoi.org_information13 branch_no
,papcont.first_name || ' ' || papcont.last_name
contact_name
,hoi.org_information14 telephone_number
,papsign.first_name || ' ' || papsign.last_name
signatory
,hlc.address_line_1 address_1
,hlc.address_line_2 address_2
,hlc.address_line_3 address_3
,hlc.town_or_city suburb
,hlc.region_1 state
,hlc.postal_code postcode
,ftl.territory_short_name country
from hr_organization_information hoi
,hr_locations hlc
,fnd_territories_tl ftl
,hr_organization_units hou
,per_all_people_f papcont
,per_all_people_f papsign
where hou.business_group_id = p_business_group_id
and hou.organization_id = p_registered_employer
and hou.organization_id = hoi.organization_id
and hoi.org_information_context = 'AU_LEGAL_EMPLOYER'
and ftl.territory_code = hlc.country
and ftl.language = userenv('LANG')
and hlc.location_id = hou.location_id
and papcont.person_id = hoi.org_information7
and papcont.effective_start_date = (select max(effective_start_date)
from per_all_people_f p
where papcont.person_id=p.person_id)
and papsign.person_id = hoi.org_information8
and papsign.effective_start_date = (select max(effective_start_date)
from per_all_people_f p
where papsign.person_id=p.person_id);
/* Bug 2512431 -- Removed to_date for p_year_end in the select statement for emp_type */
/* Bug 2977533 - When a new address is created thro SS applications ,2 rows are created in
per_addresses table for the same primary address;the previous address is end dated.
select pev.screen_entry_value tfn_no
from
pay_element_types_f pet,
pay_input_values_f piv,
pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_entry_values_f pev
where pet.element_name = 'Tax Information'
and piv.name = 'Tax File Number'
and pet.element_type_id = piv.element_type_id
and pet.element_type_id = pel.element_type_id
and pel.element_link_id = pee.element_link_id
and pee.element_entry_id = pev.element_entry_id
and piv.input_value_id = pev.input_value_id
and pee.assignment_id = c_assignment_id
and c_date_earned between pel.effective_start_date and pel.effective_end_date
and c_date_earned between pev.effective_start_date and pev.effective_end_date
and c_date_earned between pet.effective_start_date and pet.effective_end_date
and c_date_earned between pee.effective_start_date and pee.effective_end_date
and c_date_earned between piv.effective_start_date and piv.effective_end_date;
select distinct
pap.first_name first_name
,substr(pap.middle_names, 1,
decode(instr(pap.middle_names,' '),
0, 60, instr(pap.middle_names,' ',1)-1)) middle_name --Bug 10064551
,pap.last_name surname
,pad.address_line1 address_1
,pad.address_line2 address_2
,pad.address_line3 address_3
,pad.town_or_city suburb
,pad.region_1 state
,pad.postal_code postcode
,fta.territory_short_name country
,pad.style address_style -- Bug 5364017
,pad.country address_country -- Bug 5364017
,to_char(pps.date_start,'DDMMYYYY') start_date
,nvl(to_char(pps.actual_termination_date,
'DDMMYYYY'),'31124712') termination_date
,pps.final_process_date final_process_date -- Bug3263659
,to_char(pap.date_of_birth,'DDMMYYYY') dob
,hlc.location_code asgmnt_loc
,pap.employee_number emp_no
,decode(pps.actual_termination_date,null,'C',decode(sign(pps.actual_termination_date - p_year_end),1,'C','T')) emp_type /* Bug #1973978 */
,pad.date_from
,ppa1.date_earned
,ppa1.effective_date -- Bug3263659
,pps.actual_termination_date
,to_char(paaf.effective_start_date,'DDMMYYYY')
,to_char(paa.effective_end_date,'DDMMYYYY') -- Bug 2610141
,pps.pds_information1 termination_type --Bug 8315198
from hr_organization_information hoi,
hr_organization_units hou,
hr_soft_coding_keyflex hsc,
hr_locations hlc, /* Bug No : 2263587 */
per_all_assignments_f paa,
per_all_assignments_f paaf, /* Bug : 2610141 */
per_all_people_f pap,
per_addresses pad,
fnd_territories_tl fta,
per_periods_of_service pps,
pay_payroll_actions ppa,
pay_assignment_actions pac,
pay_payroll_actions ppa1, /* Bug# 2448441 */
pay_assignment_actions pac1 /* Bug# 2448441 */
where hou.business_group_id = p_business_group_id
and hou.organization_id = p_registered_employer
and ppa.action_type = 'X'
and hou.organization_id = hoi.organization_id
and hoi.org_information_context = 'AU_LEGAL_EMPLOYER'
and hou.business_group_id = pap.business_group_id
and hsc.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
and paa.location_id = hlc.location_id(+)
and to_char(hou.organization_id)= hsc.segment1
and pap.person_id = paa.person_id
and pap.person_id = pad.person_id(+)
and pad.primary_flag(+) = 'Y' /*Added for bug 2774577*/
and fta.territory_code(+) = pad.country
and fta.language(+) = userenv('LANG')
and pap.person_id = pps.person_id
and pps.period_of_service_id = paa.period_of_service_id /* Bug#2786146 */
and ppa.payroll_action_id = pac.payroll_action_id
and ppa.payroll_action_id = p_payroll_action_id
and paa.assignment_id = pac.assignment_id
and paa.assignment_id = p_assignment_id
and paaf.assignment_id = paa.assignment_id -- Bug 2610141
/* Added for Bug# 2448441 */
and paa.assignment_id = pac1.assignment_id
and ppa1.payroll_action_id = pac1.payroll_action_id
and pac1.assignment_action_id = (select to_number(substr(max(lpad(paa2.action_sequence,15,'0')||paa2.assignment_action_id),16)) /*Bug 7242551 */
from pay_payroll_actions ppa2,
pay_assignment_actions paa2
where ppa2.action_type in ('R','Q','B','I') --Bug 2574186
and ppa2.payroll_action_id = paa2.payroll_action_id
and paa2.tax_unit_id = p_registered_employer -- Bug 2610141
and paa2.assignment_id = paa.assignment_id
and ppa2.effective_date between add_months(p_year_start,-3) and p_year_end )/*Bug3048962 */
/* End of Bug# 2448441 */
and (paa.effective_start_date, paaf.effective_start_date)
= (select max(a.effective_Start_date),min(a.effective_start_date) -- Bug 2610141
from per_all_assignments_f a
, hr_soft_coding_keyflex hsc1 --Added for bug 4177679
where a.assignment_id = paa.assignment_id
and hsc1.soft_coding_keyflex_id = a.soft_coding_keyflex_id --Added for bug 4177679
and hsc1.segment1= p_registered_employer --Added for bug 4177679
and nvl(pps.actual_termination_date,p_year_end)
between a.effective_Start_date and pap.effective_end_date /*2689175*/
and a.effective_end_date >= least(nvl(pps.actual_termination_date,p_year_start),p_year_start))--Added for bug 4177679,4299506
and pap.effective_start_date = (select max(effective_Start_date)
from per_all_people_f p
where p.person_id = pap.person_id
and nvl(pps.actual_termination_date,p_year_end) between p.effective_Start_date and p.effective_end_date)
ORDER BY pad.date_from DESC;/*Bug2977533*/
SELECT pay.payroll_name
FROM per_all_assignments_f paaf,
pay_payrolls_f pay
WHERE paaf.assignment_id = p_assignment_id
and paaf.effective_end_date = (select max(effective_end_date)
From per_assignments_f iipaf
WHERE iipaf.assignment_id = p_assignment_id
and iipaf.effective_end_date >= p_fbt_year_start
and iipaf.effective_start_date <= p_year_end
AND iipaf.payroll_id IS NOT NULL)
AND pay.payroll_id = paaf.payroll_id
AND paaf.effective_end_date BETWEEN pay.effective_start_date AND pay.effective_end_date;
select '3006' ||to_char(p_year_end,'YYYY') report_end_date
,hoi.org_information1 supplier_number
,hoi.org_information3 supplier_name
,hoi.org_information12 supplier_abn
,pap.first_name || ' ' || pap.last_name supplier_contact_name
,hoi.org_information14 supplier_contact_phone
,hrl.address_line_1 supplier_address_1
,hrl.address_line_2 supplier_address_2
,hrl.address_line_3 supplier_address_3
,hrl.town_or_city supplier_suburb
,hrl.region_1 supplier_state
,hrl.postal_code supplier_postcode
,ftl.territory_short_name supplier_country
,pap.email_address email_address
from hr_organization_information hoi
,hr_organization_units hou
,hr_locations hrl
,fnd_territories_tl ftl
,per_all_people_f pap
where hou.business_group_id = p_business_group_id
and hou.organization_id = p_registered_employer
and hou.organization_id = hoi.organization_id
and hoi.org_information_context = 'AU_LEGAL_EMPLOYER'
and hrl.location_id = hou.location_id
and ftl.territory_code = hrl.country
and ftl.language = userenv('LANG')
and hoi.org_information7 = pap.person_id
and pap.effective_start_date = (select max(effective_start_date)
from per_all_people_f p
where pap.person_id=p.person_id);
select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',ppa.legislative_parameters)
, pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa.legislative_parameters)
, pay_core_utils.get_parameter('EMPLOYEE_TYPE',ppa.legislative_parameters)
, ppa.payroll_action_id
, paa.assignment_id
, to_date('01-07-'|| substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa.legislative_parameters),1,4),'DD-MM-YYYY')
, to_date('30-06-'|| substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa.legislative_parameters),6,4),'DD-MM-YYYY')
, pay_core_utils.get_parameter('LST_YR_TERM',ppa.legislative_parameters) /*Bug3661230*/
from pay_assignment_actions paa
, pay_payroll_actions ppa
where paa.assignment_action_id = c_assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id ;
select distinct nvl(current_employee_flag,'N') current_employee_flag,
actual_termination_date,
date_start,
pps.pds_information2,
to_number(substr(max(lpad(ppa.action_sequence,15,'0')||ppa.assignment_action_id),16)), --3755305
pps.final_process_date final_process_date --3263659
from per_all_people_f p,
per_all_assignments_f a,
per_periods_of_service pps,
pay_all_payrolls_f papf, --4281290
pay_payroll_actions pa,
pay_assignment_actions ppa
where a.person_id = p.person_id
and pps.person_id = p.person_id
and a.assignment_id = ppa.assignment_id
and papf.business_group_id = p.business_group_id --4281290
and p.business_group_id = c_business_group_id
and a.business_group_id = c_business_group_id --4281290
and ppa.assignment_id = c_assignment_id
and papf.business_group_id = c_business_group_id
and pa.payroll_id = papf.payroll_id --4281290
and pa.effective_date between papf.effective_start_date and papf.effective_end_date --4281290
and pa.payroll_Action_id = ppa.payroll_Action_id
and ppa.tax_unit_id = l_registered_employer --2610141
and (
(pps.actual_termination_date between c_lst_year_start and c_year_end
and pa.effective_date between c_year_start and c_year_end --3263659
)
or
(pps.actual_termination_date between l_fbt_year_start
and to_date('30-06-'||to_char(c_year_start,'YYYY'),'DD-MM-YYYY')
and pa.effective_date between to_date('01-04-'||to_char(c_year_start,'YYYY'),'DD-MM-YYYY')
and to_date('30-06-'||to_char(c_year_start,'YYYY'),'DD-MM-YYYY')
)
)
and a.assignment_id = c_assignment_id
and p.effective_start_date = (select max(pp.effective_start_date)
from per_all_people_f pp
where p.person_id = pp.person_id
and p.business_group_id = c_business_group_id
) -- Bug 2856638
and a.effective_start_date = (select max(aa.effective_start_date)
from per_all_assignments_f aa
where aa.assignment_id = c_assignment_id
) --4281290
and pa.action_type in ('R','Q','I','B','V') --2646912, 4063321
and a.period_of_service_id = pps.period_of_service_id --3586388
group by nvl(current_employee_flag,'N') --3019374
, actual_termination_date
, date_start
, pps.pds_information2
, pps.final_process_date; --3263659
select ppa.effective_date
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id = c_assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id;
select pdb.defined_balance_id
from pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where pbt.balance_name ='Lump Sum C Payments'
and pbt.legislation_code = g_legislation_code
and pbt.balance_type_id = pdb.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id --2501105
and pbd.legislation_code = g_legislation_code
and pdb.legislation_code = g_legislation_code
and pbd.dimension_name = '_ASG_LE_YTD'; --2610141
select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id,
max(paa.action_sequence) action_sequence
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf
where paa.assignment_id = paf.assignment_id
and paf.assignment_id = c_assignment_id
and paa.assignment_id = c_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date between c_year_start and c_year_end
and ppa.payroll_id = paf.payroll_id
and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
and paa.action_status = 'C'
and paa.tax_unit_id = c_tax_unit_id;
select paaf.assignment_id
from per_assignments_f paaf,
hr_soft_coding_keyflex hsck
where paaf.assignment_id = c_assignment_id
and paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
and paaf.effective_start_date <= c_year_end
and paaf.effective_end_date >= c_year_start
and hsck.segment1 = c_legal_employer;
select to_char(min(ppa.effective_date),'DDMMYYYY'), to_char(max(ppa.effective_date),'DDMMYYYY')
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paaf
where paa.assignment_id = paaf.assignment_id
and paaf.assignment_id = c_assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.effective_date between c_year_start and c_year_end
and ppa.payroll_id = paaf.payroll_id
and ppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
and paa.tax_unit_id = c_legal_employer
and paa.action_status = 'C'
and ppa.action_type in ('R','Q','V'); /*Bug 4387183*/
select hsck.segment1
from per_assignments_f paaf,
hr_soft_coding_keyflex hsck
where paaf.assignment_id = c_assignment_id
and paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
and paaf.effective_start_date <= c_year_end
and paaf.effective_end_date >= c_year_start
order by paaf.effective_start_date desc;
SELECT max(ppa.effective_date) effective_date
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf,
pay_element_types_f pet,
pay_run_results prr
WHERE paa.assignment_id = paf.assignment_id
AND paf.assignment_id = c_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.effective_date BETWEEN c_year_start AND c_year_end
AND ppa.payroll_id = paf.payroll_id
AND ppa.action_type IN ('R', 'Q', 'I', 'V', 'B')
AND ppa.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paa.action_status = 'C'
AND paa.tax_unit_id = c_tax_unit_id
AND prr.assignment_action_id = paa.assignment_action_id
AND prr.element_type_id = pet.element_type_id
AND pet.element_name = 'ETP on Termination'
AND pet.legislation_code = 'AU'
AND pet.business_group_id IS NULL
AND ppa.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date;
p_all_dbi_tab.delete;
p_result_table.delete;
p_result_table.delete;
t_fw_gross_type.delete;
select fai.value
from ff_archive_items fai,
ff_user_entities fue
where fai.context1 = p_assignment_action_id
and fai.user_entity_id = fue.user_entity_id
and fue.user_entity_name = p_user_entity_name;
SELECT pay_core_utils.get_parameter('TEST_EFILE',legislative_parameters) TEST_EFILE,
pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) BUSINESS_GROUP_ID,
pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters) FINANCIAL_YEAR,
pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) REGISTERED_EMPLOYER,
to_date(pay_core_utils.get_parameter('START_DATE',legislative_parameters),'YYYY/MM/DD') start_date,
to_date(pay_core_utils.get_parameter('END_DATE',legislative_parameters),'YYYY/MM/DD') end_date,
to_date(pay_core_utils.get_parameter('EFFECTIVE_DATE',legislative_parameters),'YYYY/MM/DD') EFFECTIVE_DATE
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = c_payroll_action_id;
SELECT LOOKUP_CODE
FROM HR_LOOKUPS
WHERE lookup_type = 'AU_PS_FINANCIAL_YEAR'
AND enabled_flag = 'Y'
AND meaning =c_financial_year;
select /*+ USE_NL(ptp) */ -- Bug 4925650
max(paa.assignment_action_id) -- Bug: 3095919, Bug 2610141
from per_assignments_f paf,
pay_payroll_Actions ppa,
pay_assignment_Actions paa,
per_time_periods ptp
where ppa.payroll_Action_id = paa.payroll_Action_id
and paa.assignment_id = c_assignment_id
and paf.assignment_id = c_assignment_id
and paf.assignment_id = paa.assignment_id
and paa.tax_unit_id = p_registered_employer --2610141
and action_type in ('Q','R','V','B','I')
AND (paa.source_action_id IS NULL
OR (paa.source_action_id IS NOT NULL AND ppa.run_type_id IS NULL)) /*Bug 4363057*/
and ppa.effective_date between c_year_start and c_year_end /*bug 4063321*/
/* Bug# 5377624 */
-- AND ptp.time_period_id = ppa.time_period_id
and ppa.payroll_id = ptp.payroll_id
and ppa.payroll_id=paf.payroll_id /* Added for bug 5371102 , query 1 */
and ppa.date_earned between ptp.start_date and ptp.end_date
/* Bug# 5377624 */
and ppa.date_earned between paf.effective_start_date and paf.effective_end_date
GROUP BY ptp.time_period_id;
p_result_lsE_ptd_table.delete;
SELECT pee.element_entry_id element_entry_id,
prv.result_value,
ppa.date_earned date_earned,
ppa.effective_date,
pec.classification_name
FROM per_all_assignments_f paa
,per_periods_of_service pps
,pay_assignment_actions pac
,pay_payroll_actions ppa
,pay_element_entries_f pee
,pay_run_results prr
,pay_run_result_values prv
,pay_element_types_f pet
,pay_input_values_f piv
,pay_element_classifications pec
WHERE paa.assignment_id = p_assignment_id
AND pps.PERIOD_OF_SERVICE_ID = paa.PERIOD_OF_SERVICE_ID
AND NVL(pps.actual_termination_date, p_end_date)
BETWEEN paa.effective_start_date AND paa.effective_end_date
AND pac.assignment_id = paa.assignment_id
AND pac.assignment_id = pee.assignment_id
AND pac.tax_unit_id = p_tax_unit_id
AND pac.payroll_action_id = ppa.payroll_action_id
AND ppa.effective_date BETWEEN p_start_date AND p_end_date
AND pac.assignment_action_id = prr.assignment_action_id
AND prr.element_type_id=pet.element_type_id
and prr.run_result_id = prv.run_result_id
and prv.input_value_id = piv.input_value_id
and piv.name = 'Pay Value'
and pec.classification_id = pet.classification_id
and (pec.classification_name in ('Earnings', 'Pre Tax Deductions')
OR
(piv.element_type_id = pet.element_type_id
and pet.element_name = 'Tax Deduction'))
AND pee.element_entry_id=prr.element_entry_id
AND pee.creator_type in ('EE','RR')
AND pee.assignment_id = paa.assignment_id
AND ppa.action_status='C'
AND pac.action_status='C'
AND ppa.date_earned between pee.effective_start_date and pee.effective_end_date
AND ppa.date_earned BETWEEN pet.effective_start_date AND pet.effective_end_date
AND ppa.date_earned between piv.effective_start_date and piv.effective_end_date;
select 'Y'
from per_all_assignments_f paa
,per_time_periods ptp
,per_assignment_status_types past
where paa.assignment_id = p_assignment_id
and paa.payroll_id = ptp.payroll_id
and c_date = ptp.end_date
and past.assignment_status_type_id = paa.assignment_status_type_id
and past.user_status = p_fw_type
and c_date between paa.effective_start_date and paa.effective_end_date
;