[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
*** -------------------------------------------------------------------------------------------------------+
*/
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
, 'Other Income',12
, '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) sort_index, /*4015082 ,6192381 */
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'
, 'Other Income'
, '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 */
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
, 'Lump Sum C Payments',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 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')
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 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
,ppa.business_group_id
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = c_payroll_Action_id;
p_balance_value_tab.delete;
p_etp_balance_value_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 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
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 /*+ INDEX(rpac PAY_ASSIGNMENT_ACTIONS_N51)
INDEX(rppa PAY_PAYROLL_ACTIONS_N51*/ ''
from
pay_payroll_actions rppa
,pay_assignment_actions rpac /*Bug3048962 */
,per_assignments_f paaf /*Bug 3815301 */
where ( rppa.effective_date between v_fin_year_start and v_fin_year_end
or ( 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 = paaf.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_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 /*+ INDEX(rpac PAY_ASSIGNMENT_ACTIONS_N51)
INDEX(rppa PAY_PAYROLL_ACTIONS_N51 */ ''
from pay_payroll_actions rppa
,pay_assignment_actions rpac/*Bug3048962 */
,per_assignments_f paaf /*Bug 3815301 */
where ( rppa.effective_date between v_fin_year_start and v_fin_year_end
or ( 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 = paaf.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_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 /*+ INDEX(pap per_people_f_pk)
INDEX(rppa pay_payroll_actions_pk)
INDEX(ppr PAY_POPULATION_RANGES_N4)
INDEX(paa per_assignments_f_N12)
INDEX(pps per_periods_of_service_PK)
*/ 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.chunk_number = c_chunk
AND p.person_id = ppr.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 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 /*+ INDEX(rpac PAY_ASSIGNMENT_ACTIONS_N51)
INDEX(rppa PAY_PAYROLL_ACTIONS_N51*/ ''
FROM
pay_payroll_actions rppa
,pay_assignment_actions rpac /*Bug3048962 */
,per_assignments_f paaf /*Bug 3815301 */
WHERE ( rppa.effective_date BETWEEN v_fin_year_start AND v_fin_year_end
OR ( 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)
)
)
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 = paaf.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_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 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 ;
select sum(pay_balance_pkg.get_value(pdb.defined_balance_id,
c_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.legislation_code = 'AU'
AND pbt.balance_name = 'Lump Sum E Payments'
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';
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 = paa.assignment_id
and paa.tax_unit_id = p_registered_employer --2610141
and action_type in ('Q','R','V')
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;
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 */
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
,pay_balance_types pbt
WHERE paa.assignment_id = c_assignment_id
AND pet.element_information_category = 'AU_EARNINGS'
AND pet.element_information1 = 'Y'
AND pet.element_information2=pbt.balance_type_id
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 ppa.date_earned BETWEEN pet.effective_start_date AND pet.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;
select distinct 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
where
pee.element_entry_id=c_element_entry_id
and prr.source_id=pee.element_entry_id
and prv.run_result_id=prr.run_result_id
AND pet.element_information_category = 'AU_EARNINGS'
AND pet.element_information1 = 'Y'
AND pet.element_information2=pbt.balance_type_id
AND prr.element_type_id=pet.element_type_id
AND pee.element_entry_id=prr.source_id;
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 balance_name
, pay_balance_pkg.get_value(def_id,p_max_assignment_action_id,p_registered_employer,null,null,null,null) balance_value
, def_id
, bal_type_id
from
( select distinct nvl(pbt.reporting_name,pbt.balance_name) balance_name
, 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_balance_dimensions pbd
--, per_periods_of_service pps
, pay_payroll_actions ppa
, pay_assignment_actions pac
, pay_run_results prr
where pac.assignment_id = c_assignment_id
and pac.tax_unit_id = p_registered_employer --2610141
and paa.assignment_id = pac.assignment_id
and pac.payroll_action_id = ppa.payroll_Action_id
and ppa.effective_date between c_year_start and c_year_end /*bug 4063321*/
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_EARNINGS'
and pet.element_information1 = 'Y'
and pet.element_information2 = pbt.balance_type_id
and pbt.balance_type_id = pdb.balance_type_id(+)
and pdb.balance_dimension_id(+) = c_dimension_id
--and pbd.balance_dimension_id = pdb.balance_dimension_id /*4863149*/
--and pbd.dimension_name = '_ASG_LE_YTD' --2610141 /*4863149*/
--and pbd.legislation_code = 'AU' /* Bug#2665475 , 4863149*/
--and pps.period_of_service_id = paa.period_of_service_id
--and nvl(pps.actual_termination_date,c_year_end)
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
)
order by 2 desc ;
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 ;
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 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 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 ;
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 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
,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 */
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 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 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
,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
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 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.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 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;
p_all_dbi_tab.delete;
p_result_table.delete;
p_result_table.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;