[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
*/
g_debug boolean; --Bug#3193479
select balance_name,
pay_balance_pkg.get_value(def_id,
c_assignment_action_id,
c_registered_employer,
null,null,null,null) balance_value
from
( SELECT DISTINCT NVL(pbt.reporting_name,pbt.balance_name) balance_name,
pdb.defined_balance_id def_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 = c_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
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 pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbd.dimension_name = '_ASG_LE_YTD' --2610141
AND pbd.legislation_code = 'AU'
-- 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 ;*/
SELECT /*+ ORDERED */
DISTINCT pet.element_information2 balance_type_id
FROM per_all_assignments_f paf
,pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_run_results prr
,pay_element_types_f pet
WHERE paa.assignment_id = c_assignment_id
AND paf.business_group_id = ppa.business_group_id
AND ppa.effective_date BETWEEN c_start_date AND c_end_date
AND ppa.action_type in ('R','Q','B','V','I')
AND ppa.payroll_id = paf.payroll_id
AND paa.assignment_id = paf.assignment_id
AND paa.assignment_action_id = prr.assignment_Action_id
AND prr.element_type_id = pet.element_type_id
AND prr.status in ('P','PA')
AND paa.tax_unit_id = c_tax_unit_id
AND paa.action_status ='C'
AND pet.element_information_category = 'AU_EARNINGS'
AND pet.element_information1 = 'Y'
AND paa.payroll_action_id = ppa.payroll_Action_id
AND ppa.effective_date
BETWEEN paf.effective_start_date AND paf.effective_end_date
AND ppa.effective_date
BETWEEN pet.effective_start_date AND pet.effective_end_date;
select nvl(pbt.reporting_name,pbt.balance_name) balance_name, /* Bug 5743196 -Added nvl */
pdb.defined_balance_id
from pay_balance_types pbt
,pay_defined_balances pdb
,pay_balance_dimensions pbd
where pbt.balance_type_id = c_balance_type_id
and pdb.balance_type_id = pbt.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.dimension_name = '_ASG_LE_YTD'
and pbd.legislation_code ='AU';
g_balance_type_tab.delete;
allow_result_table.delete;
allow_context_table.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 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 ;
select pay_balance_pkg.get_value(pdb.defined_balance_id, v_assignment_action_id, c_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 max(paa.assignment_action_id) /* Bug#3095923 , 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 = v_assignment_id
and paf.assignment_id = paa.assignment_id
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 date_earned between v_year_start and v_year_end
and date_earned between paf.effective_start_date and paf.effective_end_date
and paa.tax_unit_id = c_registered_employer
AND ptp.time_period_id = ppa.time_period_id
GROUP BY ptp.time_period_id; --2610141
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',
'Other Income','Union Fees','Invalidity Payments','Lump Sum C Payments',
'Lump Sum C Deductions','Leave Payments Marginal','Termination Deductions'
, 'Workplace Giving Deductions' /* 4015082 */
)
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 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 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 */
g_result_table.delete;
g_context_table.delete;
bal_id.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,'Other Income',11
,'Workplace Giving Deductions', 12) sort_index /*4015082 */
, 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','Other Income',
'Workplace Giving Deductions') /* 4015082 */
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;
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 /*+ INDEX(rpac PAY_ASSIGNMENT_ACTIONS_N51)
INDEX(rpac pay_assignment_actions_n1)
INDEX(rppa PAY_PAYROLL_ACTIONS_N51)
INDEX(rppa PAY_PAYROLL_ACTIONS_PK) */''
FROM
pay_payroll_actions rppa
,pay_assignment_actions rpac
,per_assignments_f paaf -- Bug: 3872211
where (rppa.effective_date between p_fin_year_start and p_fin_year_end /*Bug3048962 */
or ( 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'
/* Start of Bug: 3872211 */
and rpac.assignment_id = paaf.assignment_id
and rppa.payroll_id = paaf.payroll_id
and paaf.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(rppa pay_payroll_actions_pk)
INDEX(ppr PAY_POPULATION_RANGES_N4)
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
,pay_population_ranges ppr
where rppa.payroll_action_id = p_payroll_action_id
and rppa.payroll_action_id = ppr.payroll_action_id
and ppr.chunk_number = p_chunk
and ppr.person_id = pap.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 /*+ INDEX(rpac PAY_ASSIGNMENT_ACTIONS_N51)
INDEX(rppa PAY_PAYROLL_ACTIONS_N51)
INDEX(rppa PAY_PAYROLL_ACTIONS_PK)
*/''
FROM
pay_payroll_actions rppa
,pay_assignment_actions rpac
,per_assignments_f paaf -- Bug: 3872211
where (rppa.effective_date between p_fin_year_start and p_fin_year_end /*Bug3048962 */
or ( 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'
/* Start of Bug: 3872211 */
and rpac.assignment_id = paaf.assignment_id
and rppa.payroll_id = paaf.payroll_id
and paaf.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 /*+ INDEX(rpac PAY_ASSIGNMENT_ACTIONS_N51)
INDEX(rpac pay_assignment_actions_n1)
INDEX(rppa PAY_PAYROLL_ACTIONS_N51)
INDEX(rppa PAY_PAYROLL_ACTIONS_PK) */''
FROM
pay_payroll_actions rppa
,pay_assignment_actions rpac
,per_assignments_f paaf -- Bug: 3872211
where (rppa.effective_date between p_fin_year_start and p_fin_year_end /*Bug3048962 */
or ( 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) ) --2610141 /* Bug 5708255 */
)
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'
/* Start of Bug: 3872211 */
and rpac.assignment_id = paaf.assignment_id
and rppa.payroll_id = paaf.payroll_id
and paaf.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;