[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
*** 09-Feb-05 ksingla 115.57 Bug#4173809 Modified the cursor c_eit_updated for Manual PS issues.
*** 12 Feb 05 abhargav 115.58 bug#4174037 Modified the cursor get_allowance_balances to avoid the unnecessary get_value() call.
*** 17 Feb 05 abhkumar 115.59 Bug#4161460 Rolled back the changes made in version 115.56.
*** 05 Apr 05 ksingla 115.60 Bug#4256486 Modified the etp_code for performance.
*** 12 Apr 05 avenkatk 115.61 Bug#4256506 Changed c_max_asg_action_id in procedure get_total_fbt for performance.
*** 18 Apr 05 ksingla 115.62 Bug#4278272 Changed the cursor get_allowance_balances for performance issues.
*** 19 Apr 05 ksingla 115.63 Bug#4278407 Changed the cursor c_get_details to improve performance.
*** 22 Apr 05 ksingla 115.64 Bug#4177679 Added a new paramter to the function call etp_prepost_ratios.
*** 25 Apr 05 ksingla 115.65 Bug#4278272 Rolled back the changes done in version 115.62.
*** 05 May 05 abhkumar 115.66 Bug#4377367 Added join in the cursor c_asgids to archive the end-dated employees.
*** 09 JUl 05 abhargav 115.67 Bug#4363057 Changes due to Retro Tax enhancement.
*** 2 AUG 05 hnainani 115.68 Bug#4478752 Added quotes to -999 to allow for Character values in flexfield.
*** 02-OCT-05 abhkumar 115.70 Bug#4688800 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.
*** 02-DEC-05 abhkumar 115.71 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.72 Bug#4863149 Modified the code to raise error message when there is no defined balance id for the allowance balance.
*** 09-DEC-05 ksingla 115.73 Bug#4872594 Removed round from Pre and post Jul values.
*** 15-DEC-05 ksingla 115.74 Bug#4872594 Put round off upto 2 decimal places.
*** 15-DEC-05 ksingla 115.75 Bug#4888097 Inititalise allowance variables to prevent picking value for previous employees when the current employee
*** being processed doesn't has a allowance.
*** 20-JUL-06 priupadh 115.76 Bug#5397790 In Cursor etp_code added a join of period_of_service_id
*** 19-Dec-06 ksingla 115.77 Bug#5708255 Added code to get value of global FBT_THRESHOLD
*** 27-Dec-06 ksingla 115.78 Bug#5708255 Added to_number to all occurrences of g_fbt_threshold
*** 8-Jan-06 ksingla 115.79 Bug#5743196 Added nvl to cursor c_allowance_balance
*** 13-Feb-06 priupadh 115.80 N/A Version for restoring Triple Maintanence between 11i-->R12(Branch) -->R12(MainLine)
*** 24-May-06 priupadh 115.81 Bug#6069614 Removed the if conditions which checks the death benefit type other then 'Dependent'
*** 06-Jun-06 priupadh 115.82 Bug#6112527 Added the condition removed for Bug#6069614 with check that only archive termination type death/dependent if Fin Year is 2007/2008 or greater.
*** 20-Mar-08 avenkatk 115.84 Bug#6839263 Added changes for support of XML migrated reports in R12.1
** 21-Mar-08 avenkatk 115.85 Bug#6839263 Added Logic to set the OPP Template options for PDF output
*** 26-May-08 bkeshary 115.86 Bug#7030285 Modified the calculation for Assessable Income
*** 26-May-08 bkeshary 115.87 Bug#7030285 Added File Change History
*** 18-Jun-08 avenkatk 115.88 Bug#7138494 Added Changes for RANGE_PERSON_ID
*** 18-Jun-08 avenkatk 115.89 Bug#7138494 Modified Allowance Cursor for peformance
*** 01-Jul-08 avenkatk 115.90 Bug#7138494 Modified Allowance Cursor - Added ORDERED HINT
*** 02-Dec-08 skshin 115.91 Bug#7571001 Enabled Group Level Dimension for Allowances
*** 20-JAN-09 skshin 115.92 Bug#7571001 Modified cursors as suggested by comments in bug 7571001 and added comments
*** 28-Apr-09 pmatamsr 115.93 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'.
*** 23-Jun-09 pmatamsr 115.94 Bug#8587013 Added changes to support archival of balances 'Reportable Employer Superannuation Contributions' and
*** 'Exempt Foreign Employment Income' introduced as part of PS changes 2009 and removed the reporting of Other Income balance.
*** 07-Sep-09 pmatamsr 115.95 Bug#8769345 Modified functions populate_bal_ids ,etp_details and procedure get_assgt_curr_term_values_bbr to support ETP Taxable and Tax Free
*** balances introduced as part of statutory changes to super rollover.
*** 19-Nov-09 skshin 115.98 Bug#8711855 Modified Total_Lump_Sum_E_Payments procedure to call get_lumpsumE_value function and changed g_input_term_details_table index ids
*** 15-Dec-09 pmatamsr 115.99 Bug#9190980 Added a new argument v_adj_lump_sum_pre_tax in call to get_lumpsumE_value function.
*** 13-Jan-09 pmatamsr 115.100 Bug#9226023 Added logic to support the calculation of ETP taxable and Tax Free components for terminated employees processed
*** before applying the patch 8769345.
*** 28-SEP-10 dduvvuri 115.101 Bug#9147438 Changes done for Foreign Worker EOY reporting enhancement
*** 29-SEP-10 dduvvuri 115.102 Bug#9147438 Fixed certain FBT threshold related issues and no data found errors
*** 20-OCT-10 dduvvuri 115.104 Bug#10209338 Initialiased FW FBT and Reporting amounts to 0 in procedure get_value_bbr to ensure correct values are
*** returned when multiple assignments are present.
*** 22-Nov-10 skshin 115.105 Bug#10143762 Adjusted Exempt Foreign Income from Gross_Earnings for both INB and FW type.
*** Bug#10216064 LT12_Curr retro and LT12_Curr retro Tax are to be reported on each type of payment summary based on
*** assignment type of original period. The other retros and associated retro Taxes are to be reported on INB payment summary.
*** 01-Dec-10 avenkatk 115.106 Bug#10331262 Made changes for FW Leave and Termination payment reporting
*** Bug#10209338 Also corrected RESC,RFB and Lump Sum D Reporting issues.
*** get_group_values_bbr - Removed all FW retreival
*** Function etp_details - corrected index for ETP tax free, taxable reporting
*** 07-Jun-11 prasrang 115.107 Bug#12400821 Performance Improvement done for westpac customer
*** 07-Jul-11 dduvvuri 115.109 Bug#12725161 This version is a rollback of 115.108 version with the bug 12698821 fixed in a different way to avoid eoy related issues
*** 26-sep-11 dduvvuri 115.110 Bug#12400821 Performance improvements in EXISTS clause in all 3 assignment_action_code cursors done for westpac customer
*** 02-Feb-12 skshin 115.111 Bug#13362286 Add Retro Earnings Additional GT12 balance for Lump Sum E and foreign worker
*** 26-Apr-12 prasrang 115.112 Bug#13989281 Modified the indexes of internal tables g_input_term_details_table and g_result_term_details_table.
*** 22-Jun-12 jmarupil 115.113 Bug#14060570 Modified the condition for allowances retro balances
*** 7-Dec-12 skshin 115.115 Bug#14703826 Modified to retrieve new ETP balances for Excluded and Non Excluded
*/
g_debug boolean; --Bug#3193479
SELECT /*+ ORDERED */
PEE.ELEMENT_ENTRY_ID ELEMENT_ENTRY_ID,
PPA.DATE_EARNED DATE_EARNED,
PEE.ASSIGNMENT_ID ASSIGNMENT_ID,
PAC.TAX_UNIT_ID,
PDB.BALANCE_TYPE_ID
FROM
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,
PAY_ELEMENT_ENTRIES_F PEE ,
PAY_ELEMENT_TYPES_F PET ,
PER_ALL_ASSIGNMENTS_F PAA ,
PER_PERIODS_OF_SERVICE PPS ,
PAY_RUN_RESULTS PRR ,
PAY_ASSIGNMENT_ACTIONS PAC ,
PAY_PAYROLL_ACTIONS PPA
WHERE pbad.attribute_name = 'AU_EOY_ALLOWANCE'
AND pbad.legislation_code = 'AU'
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
AND pee.element_type_id = piv.element_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 pac.payroll_action_id = ppa.payroll_Action_id
AND pac.assignment_id = paa.assignment_id
AND pac.tax_unit_id = p_registered_employer
AND ppa.effective_date BETWEEN c_year_start AND c_year_end
AND pac.assignment_Action_id = prr.assignment_Action_id
AND prr.element_type_id=pet.element_type_id
AND prr.element_type_id = pee.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
AND paa.business_group_id = ppa.business_group_id
AND paa.business_group_id = pet.business_group_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 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
,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
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
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 NVL(pbt.reporting_name,pbt.balance_name)
from pay_balance_types pbt, pay_defined_balances pdb
where pdb.defined_balance_id = p_def_bal_id
and pdb.balance_type_id = pbt.balance_type_id
;
g_result_alw_table.delete;
t_allowance_balance.delete;
g_result_group_alw_table.delete;
t_allowance_balance.delete;
select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
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 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 ;
l_fw_fbt_output_tab.delete;
t_fw_gross_type.delete;
f_fw_date_tab.delete;
j_fw_date_tab.delete;
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
, 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;
p_lump_sum_E_ptd_tab.delete;
SELECT pbt.balance_name,pbt.balance_type_id,pdb.defined_balance_id
FROM pay_balance_types pbt,
pay_defined_balances pdb, --Bug# 3193479
pay_balance_dimensions pbd
where pbt.legislation_code = 'AU'
and pbt.balance_name in
('CDEP','Earnings_Total','Lump Sum A Deductions',
'Lump Sum A Payments','Lump Sum B Deductions','Lump Sum B Payments',
'Lump Sum D Payments','Lump Sum E Payments','Total_Tax_Deductions',
'Union Fees','Invalidity Payments','Lump Sum C Payments',
'Lump Sum C Deductions','Leave Payments Marginal','Termination Deductions'
, 'Workplace Giving Deductions' /* 4015082 */
, 'Reportable Employer Superannuation Contributions' /* 8587013 */
, 'Exempt Foreign Employment Income' /* 8587013 */
, 'ETP Tax Free Payments Excluded' /*start bug 14703826*/
, 'ETP Taxable Payments Excluded'
, 'ETP Tax Free Payments Non Excluded'
, 'ETP Taxable Payments Non Excluded' /*end bug 14703826*/
, 'Retro Earnings Leave Loading GT 12 Mths Amount' --bug8711855
, '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' /* 10331262 */
, 'Retro Earnings Additional GT 12 Mths Amount' -- bug 13362286
)
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbd.legislation_code = 'AU'
AND pdb.legislation_code = 'AU'
AND pbd.dimension_name = c_dimension_name;
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
, '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.legislation_code = 'AU'
and 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' -- bug 13362286
, 'Retro Earnings Additional LT12 Prev Mths Amount'
, 'Retro Earnings Additional LT12 Curr Mths Amount'
)
order by sort_index;
select pbt.balance_type_id,
pbt.balance_name
from PAY_BAL_ATTRIBUTE_DEFINITIONS pbad
,pay_balance_attributes pba
,pay_defined_balances pdb
,pay_balance_types pbt
,pay_balance_dimensions pbd
where pbad.attribute_name = 'AU_EOY_ALLOWANCE'
and pba.attribute_id = pbad.attribute_id
and pba.defined_balance_id = pdb.defined_balance_id
and pdb.balance_type_id = pbt.balance_type_id
and pdb.business_group_id = p_business_group_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pbd.dimension_name = '_ASG_LE_YTD'
and pbd.legislation_code = 'AU';
select balance_type_id
from pay_balance_types
where balance_name = 'Fringe Benefits'
and legislation_code = 'AU';
select pbt.balance_name
, pdb.defined_balance_id
from PAY_BAL_ATTRIBUTE_DEFINITIONS pbad
,pay_balance_attributes pba
,pay_defined_balances pdb
,pay_balance_types pbt
,pay_balance_dimensions pbd
where pbad.attribute_name = 'AU_EOY_ALLOWANCE'
and pba.attribute_id = pbad.attribute_id
and pba.defined_balance_id = pdb.defined_balance_id
and pdb.balance_type_id = pbt.balance_type_id
and pdb.business_group_id = p_business_group_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pbd.dimension_name = '_ASG_LE_YTD'
and pbd.legislation_code = 'AU'
;
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
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;
g_fw_input_table.delete;
g_fw_input_alw_table.delete;
p_fw_fbt_bal_type_tab.delete;
g_input_alw_table.delete;
SELECT distinct nvl(current_employee_flag,'N') current_employee_flag
,actual_termination_date
,date_start
,pps.pds_information2
from per_all_people_f p,
per_all_assignments_f a,
per_periods_of_service pps
where a.person_id = p.person_id
and pps.person_id = p.person_id
and pps.period_of_service_id=a.period_of_service_id /*Bug 5397790 */
and ( pps.actual_termination_date between c_lst_year_start --bug 3686549
and c_year_end ) --Bug 3263659
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 a.effective_start_date = (SELECT max(aa.effective_start_date)
from per_all_assignments_f aa
where aa.assignment_id = c_assignment_id); /*Bug 4256486 */
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;
g_result_table.delete;
g_context_table.delete;
bal_id.delete;
g_fw_result_table.delete;
f_fw_date_tab_g.delete;
j_fw_date_tab_g.delete;
t_fw_gross_type.delete;
g_fw_result_table.delete;
g_fw_result_alw_table.delete;
SELECT distinct paat.assignment_id
from pay_action_interlocks pail,
pay_assignment_actions paat,
pay_payroll_actions paas
where paat.assignment_id = c_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) = c_financial_year
and pay_core_utils.get_parameter('REGISTERED_EMPLOYER',paas.legislative_parameters) = p_tax_unit_id; --2610141
SELECT pap.last_name,
paa.assignment_number
from per_all_people_f pap,per_all_assignments_f paa
where pap.person_id=paa.person_id
and paa.assignment_id=c_assignment_id
and paa.effective_start_date = (SELECT max(paa1.effective_start_date)
from per_all_assignments_f paa1
where paa1.assignment_id = c_assignment_id) /* Bug 4278407*/
and pap.effective_start_date = (SELECT max(ppf.effective_start_date)
from per_all_people_f ppf
where pap.person_id = ppf.person_id);
CURSOR c_eit_updated(c_assignment_id per_all_assignments_f.assignment_id%type,
c_financial_year varchar2)
is
SELECT assignment_id
from per_assignment_extra_info,
hr_lookups
where assignment_id = c_assignment_id
and aei_information1 is not null
and aei_information1 = lookup_code
and nvl(aei_information2,p_tax_unit_id) = decode(aei_information2,'-999',aei_information2,p_tax_unit_id) --Bug 4173809
and lookup_type ='AU_PS_FINANCIAL_YEAR'
and meaning = c_financial_year;
/*Bug 4173809 - Cursor updated so that the assignment is reported in the exception section when Manual PS
is issued against 'All' legal employers or a particular legal employer
If the Manual PS is issued for 'All' the legal employers the aei_information2 would be -999*/
l_assignment_id per_all_assignments_f.assignment_id%type;
OPEN c_eit_updated(p_assignment_id,p_financial_year);
FETCH c_eit_updated into l_assignment_id;
if c_eit_updated%found then
OPEN c_get_details(l_assignment_id,p_financial_year_end);
CLOSE c_eit_updated;
CLOSE c_eit_updated;
SELECT decode(pbt.balance_name,'Lump Sum A Payments',1,'Lump Sum B Payments',2,
'Lump Sum D Payments',3,'Union Fees',4,'Lump Sum C Deductions',5,
'Termination Deductions',6,'Total_Tax_Deductions',7,'Earnings_Total',8,'Leave Payments Marginal',9,
'CDEP',10,'Reportable Employer Superannuation Contributions', 11 ,'Workplace Giving Deductions', 12 ,
'Exempt Foreign Employment Income' ,13) sort_index /*4015082 , 8587013*/
, pdb.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 A Payments','Lump Sum B Payments','Lump Sum D Payments',
'Union Fees','Lump Sum C Deductions','Termination Deductions',
'Total_Tax_Deductions','Earnings_Total','Leave Payments Marginal','CDEP',
'Workplace Giving Deductions','Reportable Employer Superannuation Contributions','Exempt Foreign Employment Income') /* 4015082 , 8587013*/
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbd.legislation_code = 'AU'
AND pdb.legislation_code = 'AU'
AND pbd.dimension_name = p_dimension_name
ORDER BY sort_index;
select pbt.balance_name
,pdb.defined_balance_id
from pay_balance_types pbt
,pay_defined_balances pdb
,pay_balance_dimensions pbd
where pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbd.dimension_name = p_dimension_name
AND pdb.business_group_id = p_business_group_id
AND pbd.legislation_code = 'AU'
AND exists (
select null
from PAY_BAL_ATTRIBUTE_DEFINITIONS pbad
,pay_balance_attributes pba
,pay_defined_balances pdb2
,pay_balance_dimensions pbd2
where pbad.attribute_name = 'AU_EOY_ALLOWANCE'
and pba.attribute_id = pbad.attribute_id
and pba.defined_balance_id = pdb2.defined_balance_id
and pdb2.business_group_id = p_business_group_id
and pbt.balance_type_id = pdb2.balance_type_id
and pbd2.balance_dimension_id = pdb2.balance_dimension_id
and pbd2.dimension_name = '_ASG_LE_YTD'
and pbd2.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
) sort_index,
pbt.balance_type_id balance_type_id
FROM pay_balance_types pbt
WHERE pbt.legislation_code = 'AU'
and 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
/* 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' -- bug 13362286
)
ORDER BY sort_index;
g_input_group_alw_table.delete;
bal_id.delete;
g_result_group_details_table.delete;
g_context_table.delete;
bal_id.delete;
g_result_term_details_table.delete;
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 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_REC_PS_ARCHIVE'
and map.report_format = 'AU_REC_PS_ARCHIVE'
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
,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_ID',legislative_parameters),null,'%',pay_core_utils.get_parameter('PAYROLL_ID',legislative_parameters)) payroll_id
,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_yr_term /*Bug3661230*/
,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 pay_assignment_actions_s.nextval
from dual;
SELECT /*+ INDEX(pap per_people_f_pk)
INDEX(rppa pay_payroll_actions_pk)
INDEX(paa per_assignments_f_N12)
INDEX(pps per_periods_of_service_pk)
*/ paa.assignment_id
from per_people_f pap
,per_assignments_f paa
,pay_payroll_actions rppa
,per_periods_of_service pps
where rppa.payroll_action_id = p_payroll_action_id
and pap.person_id between p_start_person_id and p_end_person_id
and pap.person_id = paa.person_id
and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (p_fin_year_end)),1,'Y','N')) LIKE p_employee_type
and pps.period_of_service_id = paa.period_of_service_id
and pap.person_id = pps.person_id
and rppa.business_group_id=paa.business_group_id
and nvl(pps.actual_termination_date, p_lst_year_start) >= p_lst_year_start
and p_fin_year_end between pap.effective_start_date and pap.effective_end_date
/* Start of Bug: 3872211 */
and paa.effective_end_date = (SELECT MAX(effective_end_date) /*4377367*/
FROM per_assignments_f iipaf
WHERE iipaf.assignment_id = paa.assignment_id
AND iipaf.effective_end_date >= p_fbt_year_start
AND iipaf.effective_start_date <= p_fin_year_end
AND iipaf.payroll_id IS NOT NULL) /* Bug#4688800 */
and paa.payroll_id like p_payroll_id
/* End of Bug: 3872211 */
AND EXISTS (SELECT /*+ ORDERED */''
FROM
per_assignments_f paaf
,pay_assignment_actions rpac
,pay_payroll_actions rppa
where rppa.effective_date between p_fin_year_start and p_fin_year_end /*Bug3048962 */
and rppa.action_type in ('R','Q','B','I')
and rpac.tax_unit_id = p_legal_employer
and rppa.payroll_action_id = rpac.payroll_action_id
and rpac.action_status = 'C'
and rppa.payroll_id = paaf.payroll_id
and paaf.assignment_id = paa.assignment_id
and rpac.assignment_id = paa.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 p_lst_fbt_yr_start and p_fbt_year_end /*Bug3263659 */
and rppa.effective_date between p_fbt_year_start and p_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),p_legal_employer,null,null,null,null) > to_number(g_fbt_threshold)
and rppa.action_type in ('R','Q','B','I')
and rpac.tax_unit_id = p_legal_employer
and rppa.payroll_action_id = rpac.payroll_action_id
and rpac.action_status = 'C'
and rppa.payroll_id = paaf.payroll_id
and paaf.assignment_id = paa.assignment_id
and rpac.assignment_id = paa.assignment_id
and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
);
SELECT paa.assignment_id
from per_people_f pap
,per_assignments_f paa
,pay_payroll_actions rppa
,per_periods_of_service pps
,pay_population_ranges ppr
where rppa.payroll_action_id = p_payroll_action_id
and rppa.payroll_action_id = ppr.payroll_action_id
AND ppr.payroll_action_id = p_payroll_action_id
and ppr.chunk_number = p_chunk
and ppr.person_id = pap.person_id
and pap.person_id = paa.person_id
AND PAA.PERSON_ID = PPR.PERSON_ID
and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (p_fin_year_end)),1,'Y','N')) LIKE p_employee_type
and pps.period_of_service_id = paa.period_of_service_id
and pap.person_id = pps.person_id
and rppa.business_group_id=paa.business_group_id
and nvl(pps.actual_termination_date, p_lst_year_start) >= p_lst_year_start
and p_fin_year_end between pap.effective_start_date and pap.effective_end_date
/* Start of Bug: 3872211 */
and paa.effective_end_date = (SELECT MAX(effective_end_date) /*4377367*/
FROM per_assignments_f iipaf
WHERE iipaf.assignment_id = paa.assignment_id
AND IIPAF.PERSON_ID = PAA.PERSON_ID
AND iipaf.effective_end_date >= p_fbt_year_start
AND iipaf.effective_start_date <= p_fin_year_end
AND iipaf.payroll_id IS NOT NULL) /* Bug#4688800 */
and paa.payroll_id like p_payroll_id
/* End of Bug: 3872211 */
AND EXISTS (SELECT /*+ ORDERED */''
FROM
per_assignments_f paaf
,pay_assignment_actions rpac
,pay_payroll_actions rppa
where rppa.effective_date between p_fin_year_start and p_fin_year_end /*Bug3048962 */
and rppa.action_type in ('R','Q','B','I')
and rpac.tax_unit_id = p_legal_employer
and rppa.payroll_action_id = rpac.payroll_action_id
and rpac.action_status = 'C'
and rppa.payroll_id = paaf.payroll_id
and paaf.assignment_id = paa.assignment_id
and rpac.assignment_id = paa.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 p_lst_fbt_yr_start and p_fbt_year_end /*Bug3263659 */
and rppa.effective_date between p_fbt_year_start and p_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),p_legal_employer,null,null,null,null) > to_number(g_fbt_threshold)
and rppa.action_type in ('R','Q','B','I')
and rpac.tax_unit_id = p_legal_employer
and rppa.payroll_action_id = rpac.payroll_action_id
and rpac.action_status = 'C'
and rppa.payroll_id = paaf.payroll_id
and paaf.assignment_id = paa.assignment_id
and rpac.assignment_id = paa.assignment_id
and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
);
SELECT /*+ INDEX(pap per_people_f_pk)
INDEX(paa per_assignments_f_fk1)
INDEX(paa per_assignments_f_N12)
INDEX(rppa pay_payroll_actions_pk)
INDEX(pps per_periods_of_service_n3)
*/ distinct paa.assignment_id
from per_people_f pap
,per_assignments_f paa
,pay_payroll_actions rppa
,per_periods_of_service pps
where rppa.payroll_action_id = p_payroll_action_id
and pap.person_id between p_start_person_id and p_end_person_id
and pap.person_id = paa.person_id
and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (p_fin_year_end)),1,'Y','N')) LIKE p_employee_type
and pps.period_of_service_id = paa.period_of_service_id
and paa.assignment_id = p_assignment_id
and pap.person_id = pps.person_id
and rppa.business_group_id=paa.business_group_id
and nvl(pps.actual_termination_date, p_lst_year_start) >= p_lst_year_start
and p_fin_year_end between pap.effective_start_date and pap.effective_end_date
-- and least(nvl(pps.actual_termination_date,p_fin_year_end),p_fin_year_end) between paa.effective_start_date and paa.effective_end_date
and paa.effective_end_date = (select max(effective_end_date) /*4377367*/
From per_assignments_f iipaf
WHERE iipaf.assignment_id = paa.assignment_id
and iipaf.effective_end_date >= p_fbt_year_start
and iipaf.effective_start_date <= p_fin_year_end
AND iipaf.payroll_id IS NOT NULL) /* Bug#4688800 */
and paa.payroll_id like p_payroll_id
AND EXISTS (SELECT /*+ ORDERED */''
FROM
per_assignments_f paaf
,pay_assignment_actions rpac
,pay_payroll_actions rppa
where rppa.effective_date between p_fin_year_start and p_fin_year_end /*Bug3048962 */
and rppa.action_type in ('R','Q','B','I')
and rpac.tax_unit_id = p_legal_employer
and rppa.payroll_action_id = rpac.payroll_action_id
and rpac.action_status = 'C'
and rppa.payroll_id = paaf.payroll_id
and paaf.assignment_id = p_assignment_id
and rpac.assignment_id = p_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 p_lst_fbt_yr_start and p_fbt_year_end /*Bug3263659 */
and rppa.effective_date between p_fbt_year_start and p_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),p_legal_employer,null,null,null,null) > to_number(g_fbt_threshold)
and rppa.action_type in ('R','Q','B','I')
and rpac.tax_unit_id = p_legal_employer
and rppa.payroll_action_id = rpac.payroll_action_id
and rpac.action_status = 'C'
and rppa.payroll_id = paaf.payroll_id
and paaf.assignment_id = p_assignment_id
and rpac.assignment_id = p_assignment_id
and rppa.effective_date between paaf.effective_start_date and paaf.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
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 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 pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters) Financial_year
,pay_core_utils.get_parameter('EMPLOYEE_TYPE',legislative_parameters) Employee_type
,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) legal_employer
,pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters) Assignment_id
,pay_core_utils.get_parameter('PAYROLL_ID',legislative_parameters) payroll_id
,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_yr_term
,pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) Business_group_id
,pay_core_utils.get_parameter('OUTPUT_TYPE',legislative_parameters)p_output_type /* Bug# 6839263 */
from pay_payroll_actions
where payroll_action_id =c_payroll_Action_id;
SELECT printer,
print_style,
decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
,number_of_copies /* Bug 4116833 */
FROM pay_payroll_actions pact,
fnd_concurrent_requests fcr
WHERE fcr.request_id = pact.request_id
AND pact.payroll_action_id = p_payroll_action_id;