[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or '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 decode(pbt.balance_name,'Earnings_Total',1,
'Direct Payments',2,
'Termination_Payments',3,
'Involuntary Deductions',4,
'Pre Tax Deductions',5,
'Termination Deductions',6,
'Voluntary Deductions',7,
'Total_Tax_Deductions',8,
'Earnings_Non_Taxable',9,
'Employer_Charges',10,
'Lump Sum A Payments',11,
'Lump Sum B Payments',12,
'Lump Sum C Payments',13,
'Lump Sum D Payments',14,
'Lump Sum E Payments',15,
'Invalidity Payments',16,
'CDEP',17,
'Leave Payments Marginal',18,
'Reportable Employer Superannuation Contributions',19,
'Union Fees',20,
'Workplace Giving Deductions' ,21,
'Exempt Foreign Employment Income',22,
'ETP Tax Free Payments Excluded',23, /*start bug 14703826*/
'ETP Tax Free Payments Non Excluded',24,
'ETP Taxable Payments Excluded',25,
'ETP Taxable Payments Non Excluded',26,
'ETP Deductions Excluded',27, /* 27- 30 Added for empty slots */
'ETP Deductions Excluded Part of Prev',28,
'ETP Deductions Non Excluded',29,
'ETP Deductions Non Excluded Part of Prev',30, /*end bug 14703826*/
'Retro Earnings Leave Loading GT 12 Mths Amount',31,
'Retro Earnings Spread GT 12 Mths Amount',32,
'Retro Pre Tax GT 12 Mths Amount',33,
'Lump Sum C Deductions',34, /* bug8711855 */
'Foreign Leave Payments',35, /* Start Bug 10331262 */
'Foreign Leave Payments Marginal',36,
'Foreign Lump Sum A Payments',37,
'Foreign Leave Component Deduction',38,
'Foreign Lump Sum A Deduction',39 /* End Bug 10331262 */
,'Retro Earnings Additional GT 12 Mths Amount',40-- bug 13362286
) sort_index,
pdb.defined_balance_id defined_balance_id,
pbt.balance_name
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pbt.balance_name IN ('Earnings_Total','Direct Payments','Termination_Payments','Involuntary Deductions',
'Pre Tax Deductions','Termination Deductions','Voluntary Deductions','Total_Tax_Deductions',
'Earnings_Non_Taxable','Employer_Charges','Lump Sum A Payments','Lump Sum B Payments','Lump Sum C Payments',
'Lump Sum D Payments','Lump Sum E Payments','Invalidity Payments','CDEP','Leave Payments Marginal',
'Reportable Employer Superannuation Contributions','Union Fees', 'Workplace Giving Deductions','Exempt Foreign Employment Income',
'ETP Tax Free Payments Excluded','ETP Tax Free Payments Non Excluded','ETP Taxable Payments Excluded','ETP Taxable Payments Non Excluded',
'ETP Deductions Excluded','ETP Deductions Excluded Part of Prev','ETP Deductions Non Excluded','ETP Deductions Non Excluded Part of Prev',
'Retro Earnings Leave Loading GT 12 Mths Amount', 'Retro Earnings Spread GT 12 Mths Amount', 'Retro Pre Tax GT 12 Mths Amount','Lump Sum C Deductions'
,'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, 14703826 */
,'Retro Earnings Additional GT 12 Mths Amount')
AND pbd.database_item_suffix = '_ASG_LE_YTD'
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbt.legislation_code = 'AU'
ORDER BY sort_index;
SELECT decode(pbt.balance_name,
'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 -- bug 13362286
) 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')
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbd.dimension_name = '_ASG_LE_PTD'
order by sort_index;
select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) business_group_id
,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) legal_employer
,decode(pay_core_utils.get_parameter('PAYROLL',legislative_parameters),null,'%',pay_core_utils.get_parameter('PAYROLL',legislative_parameters)) payroll_id
,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('EMPLOYEE_TYPE',legislative_parameters),'C','Y','T','N','B','%') employee_type
,to_date('01-07-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fin_year_state_date
,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY') fin_year_end_date
,to_date('01-04-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_start_date
,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_end_date
,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_year_term
,pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug 4142159*/
,decode(pay_core_utils.get_parameter('REP_MODE',legislative_parameters),'SUMM','S','D') report_mode /*Bug 3891577*/
from pay_payroll_actions
where payroll_action_id = c_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 ='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 attribute_id
from PAY_BAL_ATTRIBUTE_DEFINITIONS
where attribute_name = c_attribute_name
;
select balance_type_id
from pay_balance_types
where balance_name = 'Fringe Benefits'
and legislation_code = 'AU';
select decode(pbt.balance_name,
'Earnings_Total',1
, 'Leave Payments Marginal',2
, 'Workplace Giving Deductions',3
, 'Lump Sum E Payments',4
, 'Retro Earnings Leave Loading GT 12 Mths Amount',5
, 'Retro Earnings Spread GT 12 Mths Amount',6
, 'Retro Pre Tax GT 12 Mths Amount',7
, 'Total_Tax_Deductions',8
, 'Termination Deductions',9
, 'Lump Sum C Deductions',10
, 'Foreign Tax Deductions',11
, 'Lump Sum A Payments',12
, 'Lump Sum D Payments',13
, 'Reportable Employer Superannuation Contributions',14
, 'Union Fees',15
, 'CDEP',16
, 'Exempt Foreign Employment Income',17
, 'Retro LT 12 Mths Prev Yr Amount', 18
, 'Retro Earnings Leave Loading LT 12 Mths Prev Yr Amount', 19
, 'Retro Earnings Spread LT 12 Mths Prev Yr Amount', 20
, 'Retro Pre Tax LT 12 Mths Prev Yr Amount', 21
, 'Retro LT 12 Mths Curr Yr Amount', 22
, 'Retro Earnings Leave Loading LT 12 Mths Curr Yr Amount', 23
, 'Retro Earnings Spread LT 12 Mths Curr Amount', 24
, 'Retro Tax GT12 Amount', 25
, 'Retro Tax LT12 Prev Amount', 26
, 'Retro Tax LT12 Curr Amount', 27
, 'Foreign Leave Payments',28
, 'Retro Earnings Additional GT 12 Mths Amount', 29 -- bug 13362286
, 'Retro Earnings Additional LT12 Prev Mths Amount', 30
, 'Retro Earnings Additional LT12 Curr Mths Amount', 31
) sort_index,
pbt.balance_type_id balance_type_id
from pay_balance_types pbt
where pbt.balance_name in (
'Earnings_Total'
, 'Leave Payments Marginal'
, 'Workplace Giving Deductions'
, 'Lump Sum E Payments'
, 'Retro Earnings Leave Loading GT 12 Mths Amount'
, 'Retro Earnings Spread GT 12 Mths Amount'
, 'Retro Pre Tax GT 12 Mths Amount'
, 'Total_Tax_Deductions'
, 'Termination Deductions'
, 'Lump Sum C Deductions'
, 'Foreign Tax Deductions'
, 'Lump Sum A Payments'
, 'Lump Sum D Payments'
, 'Reportable Employer Superannuation Contributions'
, 'Union Fees'
, 'CDEP'
, 'Exempt Foreign Employment Income' -- bug 10143762
/* start bug 9950136*/
, 'Retro LT 12 Mths Prev Yr Amount'
, 'Retro Earnings Leave Loading LT 12 Mths Prev Yr Amount'
, 'Retro Earnings Spread LT 12 Mths Prev Yr Amount'
, 'Retro Pre Tax LT 12 Mths Prev Yr Amount'
, 'Retro LT 12 Mths Curr Yr Amount'
, 'Retro Earnings Leave Loading LT 12 Mths Curr Yr Amount'
, 'Retro Earnings Spread LT 12 Mths Curr Amount'
, 'Retro Tax GT12 Amount'
, 'Retro Tax LT12 Prev Amount'
, 'Retro Tax LT12 Curr Amount'
/* end bug 9950136*/
, 'Foreign Leave Payments'
, 'Retro Earnings Additional GT 12 Mths Amount'
, 'Retro Earnings Additional LT12 Prev Mths Amount'
, 'Retro Earnings Additional LT12 Curr Mths Amount'
)
and pbt.legislation_code = 'AU'
order by sort_index;
g_balance_value_tab.delete;
hr_utility.set_location('g_parameters.delete_actions..........= '||g_parameters.delete_actions,30); /*Bug 4142159*/
p_lump_sum_E_ptd_tab.delete;
p_fw_fbt_bal_type_tab.delete;
p_fw_balance_type_tab.delete;
select parameter_value
from pay_action_parameters
where parameter_name = 'RANGE_PERSON_ID';
SELECT par.parameter_value
FROM pay_report_format_parameters par,
pay_report_format_mappings_f map
WHERE map.report_format_mapping_id = par.report_format_mapping_id
AND map.report_type = c_report_type
AND map.report_format = c_report_type
AND map.report_qualifier = 'AU'
AND par.parameter_name = 'RANGE_PERSON_ID'; -- Bug fix 5567246
select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) business_group_id
,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) legal_employer
,decode(pay_core_utils.get_parameter('PAYROLL',legislative_parameters),null,'%',pay_core_utils.get_parameter('PAYROLL',legislative_parameters)) payroll_id
,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('EMPLOYEE_TYPE',legislative_parameters),'C','Y','T','N','B','%') employee_type
,to_date('01-07-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fin_year_state_date
,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY') fin_year_end_date
,to_date('01-04-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_start_date
,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_end_date
,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_year_term
,pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug 4142159*/
,decode(pay_core_utils.get_parameter('REP_MODE',legislative_parameters),'SUMM','S','D') report_mode /*Bug 3891577*/
from pay_payroll_actions
where payroll_action_id = c_payroll_action_id;
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 = c_payroll_action_id
and pap.person_id between c_start_person and c_end_person
and pap.person_id = paa.person_id
and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_fin_year_end)),1,'Y','N')) LIKE c_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, c_lst_year_start) >= c_lst_year_start
and c_fin_year_end between pap.effective_start_date and pap.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
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 >= c_fbt_year_start
and iipaf.effective_start_date <= c_fin_year_end
AND iipaf.payroll_id IS NOT NULL) /*Bug 4688800*/
and paa.payroll_id like c_payroll_id
and paa.assignment_id like c_assignment_id
AND EXISTS (SELECT /*+ ORDERED */''
FROM per_assignments_f paaf
,pay_assignment_actions rpac
,pay_payroll_actions rppa
WHERE rppa.effective_date between c_fin_year_start AND c_fin_year_end /*Bug3048962 */
AND rppa.action_type in ('R','Q','B','I')
AND rpac.tax_unit_id = c_legal_employer
AND rppa.payroll_action_id = rpac.payroll_action_id
AND rpac.action_status = 'C'
AND rppa.payroll_id = paaf.payroll_id
AND rpac.assignment_id = paa.assignment_id
AND paaf.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 c_lst_fbt_yr_start AND c_fbt_year_end /*Bug3263659 */
AND rppa.effective_date between c_fbt_year_start AND c_fbt_year_end
AND pay_balance_pkg.get_value(c_fbt_defined_balance_id, rpac.assignment_action_id
+ decode(rppa.payroll_id, 0, 0, 0),c_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 = c_legal_employer
AND rppa.payroll_action_id = rpac.payroll_action_id
AND rpac.action_status = 'C'
AND rppa.payroll_id = paaf.payroll_id
AND rpac.assignment_id = paa.assignment_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 = c_payroll_action_id
and pap.person_id between c_start_person and c_end_person
and pap.person_id = paa.person_id
and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_fin_year_end)),1,'Y','N')) LIKE c_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, c_lst_year_start) >= c_lst_year_start
and c_fin_year_end between pap.effective_start_date and pap.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
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 >= c_fbt_year_start
and iipaf.effective_start_date <= c_fin_year_end
AND iipaf.payroll_id IS NOT NULL) /*Bug 4688800*/
and paa.payroll_id like c_payroll_id
and paa.assignment_id = c_assignment_id
AND EXISTS (SELECT /*+ ORDERED */''
FROM per_assignments_f paaf
,pay_assignment_actions rpac
,pay_payroll_actions rppa
WHERE rppa.effective_date between c_fin_year_start AND c_fin_year_end /*Bug3048962 */
AND rppa.action_type in ('R','Q','B','I')
AND rpac.tax_unit_id = c_legal_employer
AND rppa.payroll_action_id = rpac.payroll_action_id
AND rpac.action_status = 'C'
AND rppa.payroll_id = paaf.payroll_id
AND rpac.assignment_id = paa.assignment_id
AND paaf.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 c_lst_fbt_yr_start AND c_fbt_year_end /*Bug3263659 */
AND rppa.effective_date between c_fbt_year_start AND c_fbt_year_end
AND pay_balance_pkg.get_value(c_fbt_defined_balance_id, rpac.assignment_action_id
+ decode(rppa.payroll_id, 0, 0, 0),c_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 = c_legal_employer
AND rppa.payroll_action_id = rpac.payroll_action_id
AND rpac.action_status = 'C'
AND rppa.payroll_id = paaf.payroll_id
AND rpac.assignment_id = paa.assignment_id
AND paaf.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 = c_payroll_action_id
AND rppa.payroll_action_id = ppr.payroll_action_id
and ppr.payroll_action_id = c_payroll_action_id
AND ppr.chunk_number = c_chunk
AND pap.person_id = ppr.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 - (c_fin_year_end)),1,'Y','N')) LIKE c_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, c_lst_year_start) >= c_lst_year_start
AND c_fin_year_end between pap.effective_start_date AND pap.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.person_id = paa.person_id
AND iipaf.effective_end_date >= c_fbt_year_start
AND iipaf.effective_start_date <= c_fin_year_end
AND iipaf.payroll_id IS NOT NULL) /*Bug 4688800*/
AND paa.payroll_id like c_payroll_id
AND paa.assignment_id like c_assignment_id
AND EXISTS (SELECT /*+ ORDERED */''
FROM per_assignments_f paaf
,pay_assignment_actions rpac
,pay_payroll_actions rppa
WHERE rppa.effective_date between c_fin_year_start AND c_fin_year_end /*Bug3048962 */
AND rppa.action_type in ('R','Q','B','I')
AND rpac.tax_unit_id = c_legal_employer
AND rppa.payroll_action_id = rpac.payroll_action_id
AND rpac.action_status = 'C'
AND rppa.payroll_id = paaf.payroll_id
AND rpac.assignment_id = paa.assignment_id
AND paaf.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 c_lst_fbt_yr_start AND c_fbt_year_end /*Bug3263659 */
AND rppa.effective_date between c_fbt_year_start AND c_fbt_year_end
AND pay_balance_pkg.get_value(c_fbt_defined_balance_id, rpac.assignment_action_id
+ decode(rppa.payroll_id, 0, 0, 0),c_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 = c_legal_employer
AND rppa.payroll_action_id = rpac.payroll_action_id
AND rpac.action_status = 'C'
AND rppa.payroll_id = paaf.payroll_id
AND rpac.assignment_id = paa.assignment_id
AND paaf.assignment_id = paa.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 /* 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 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 ;
hr_utility.set_location('g_parameters.delete_actions..........= '||g_parameters.delete_actions,30); /*Bug 4142159*/
select pap.full_name,
paa.assignment_number,
paa.assignment_id,
to_number(pro.proposed_salary_n) actual_salary,
paa.normal_hours,
pps.actual_termination_date,
pps.date_start,
pgr.name grade,
paa.organization_id,
paa.payroll_id,
hsc.segment1 tax_unit_id,
hou.NAME organization_name,
hou1.name legal_employer
-- papf.payroll_name /*Bug 4688800*/
from per_people_f pap,
per_assignments_f paa,
per_grades_tl pgr,
per_periods_of_service pps,
per_pay_bases ppb,
per_pay_proposals pro,
per_assignment_status_types past,
hr_all_organization_units hou,
pay_assignment_actions paa1
,hr_soft_coding_keyflex hsc
,hr_all_organization_units hou1
-- ,pay_payrolls_f papf /*Bug 4688800*/
where pap.person_id = paa.person_id
and paa.assignment_id = paa1.assignment_id
and paa1.assignment_action_id = c_archive_assignment_action_id
and paa.business_group_id = c_business_group_id
and paa.grade_id = pgr.grade_id(+)
and pgr.language(+) = userenv('LANG')
and paa.pay_basis_id = ppb.pay_basis_id(+)
and paa.assignment_id = pro.assignment_id(+)
AND hou.organization_id = paa.organization_id
and hsc.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
and hou1.organization_id = hsc.segment1
-- and papf.payroll_id = paa.payroll_id /*Bug 4688800*/
-- and c_end_date between papf.effective_start_date and papf.effective_end_date /*Bug 4688800*/
and pps.period_of_service_id = paa.period_of_service_id
and paa.assignment_status_type_id = past.assignment_status_type_id
and paa.effective_end_date = ( select max(effective_end_date)
from per_assignments_f
WHERE assignment_id = paa.assignment_id
and effective_end_date >= c_start_date
and effective_start_date <= c_end_date)
and c_end_date between pap.effective_start_date and pap.effective_end_date
and pps.person_id = pap.person_id
and pro.change_date(+) <= c_end_date
and nvl(pro.approved,'Y') = 'Y'
and nvl(pro.change_date,to_date('4712/12/31','YYYY/MM/DD')) = (select nvl(max(pro1.change_date),to_date('4712/12/31','YYYY/MM/DD'))
from per_pay_proposals pro1
where pro1.assignment_id(+) = paa.assignment_id
and pro1.change_date(+) <= c_end_date
and nvl(pro1.approved,'Y')='Y');
SELECT paaf.payroll_id, pay.payroll_name
FROM per_all_assignments_f paaf,
pay_payrolls_f pay
WHERE paaf.assignment_id = c_assignment_id
and paaf.effective_end_date = (select max(effective_end_date)
From per_assignments_f iipaf
WHERE iipaf.assignment_id = c_assignment_id
and iipaf.effective_end_date >= c_start_date
and iipaf.effective_start_date <= c_end_date
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 pap.full_name,
paf.assignment_number,
paf.assignment_id,
pps.date_start,
pps.actual_termination_date
from per_people_f pap,
per_assignments_f paf,
per_periods_of_service pps,
pay_assignment_actions paa
where pap.person_id = paf.person_id
and paf.assignment_id = paa.assignment_id
and pps.person_id = pap.person_id
and pps.period_of_service_id = paf.period_of_service_id
and paf.business_group_id = c_business_group_id
and paa.assignment_action_id = c_archive_assignment_action_id
and paf.effective_end_date = ( select max(effective_end_date)
from per_assignments_f
WHERE assignment_id = paf.assignment_id
and effective_end_date >= c_start_date
and effective_start_date <= c_end_date)
and c_end_date between pap.effective_start_date and pap.effective_end_date;
select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
,max(paa.action_sequence) action_sequence
from pay_assignment_actions paa
, pay_payroll_actions ppa
, per_assignments_f paf
where paa.assignment_id = paf.assignment_id
and paf.assignment_id = c_assignment_id
and paa.assignment_id = c_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date between c_start_date and c_end_date
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 hr.meaning fin_year
from per_assignment_extra_info pae,
hr_lookups hr
where pae.aei_information_category = 'HR_PS_ISSUE_DATE_AU'
and pae.information_type = 'HR_PS_ISSUE_DATE_AU'
and pae.assignment_id = c_assignment_id
and pae.aei_information1 = to_char(c_fin_date,'YY')
and nvl(aei_information2,c_tax_unit_id) = decode(aei_information2,'-999',aei_information2,c_tax_unit_id)
and pae.aei_information1 = hr.lookup_code
and hr.lookup_type = 'AU_PS_FINANCIAL_YEAR';
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;
insert into pay_action_information(
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13,
assignment_id)
values(
pay_action_information_s.nextval,
g_arc_payroll_action_id,
'PA',
p_effective_date,
null,
l_employee_details.tax_unit_id,
'AU_EMPLOYEE_RECON_DETAILS',
l_employee_details.full_name,
l_employee_details.assignment_number,
l_employee_details.actual_salary,
l_employee_details.grade,
l_employee_details.normal_hours,
l_employee_details.actual_termination_date,
l_fin_year,
l_employee_details.organization_id,
g_tax_unit_id,
l_employee_details.payroll_id,
l_employee_details.organization_name,
l_employee_details.legal_employer,
l_payroll_name, /*Bug 4688800*/
l_employee_details.assignment_id);
g_result_table.delete;
g_result_table.delete;
g_context_table.delete;
f_fw_date_tab_g.delete;
j_fw_date_tab_g.delete;
g_fw_result_table.delete;
select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
,max(paa.action_sequence) action_sequence
from pay_assignment_actions paa
, pay_payroll_actions ppa
, per_assignments_f paf
where paa.assignment_id = paf.assignment_id
and paf.assignment_id = c_assignment_id
and paa.assignment_id = c_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date between c_start_date and c_end_date
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 = c_name
AND legislation_code = 'AU'
AND c_year_end BETWEEN effective_start_date
AND effective_end_date ;
l_fw_fbt_output_tab.delete;
t_fw_gross_type.delete;
f_fw_date_tab.delete;
j_fw_date_tab.delete;
select element_name,label classification_name,sum(amount) payment,sum(hours) hours,rate
from
(select distinct
nvl(pet.reporting_name, pet.element_name) element_name,
decode(instr(pec.classification_name, 'Earnings'), 0, null,
decode(pec2.classification_name, 'Non Taxable', 'Non Taxable Earnings','Taxable Earnings'))|| /* Bug 4179109, 5119734, 7571001*/
decode(instr(pec.classification_name, 'Payments'), 0, null,
decode(instr(pec.classification_name, 'Direct'), 0, 'Taxable Earnings', 'Direct Payments')) ||
decode(instr(pec.classification_name, 'Deductions'), 0, null,
decode(pec.classification_name , 'Termination Deductions' , 'Tax Deductions'
, 'Involuntary Deductions' , 'Post Tax Deductions'
, 'Voluntary Deductions' , 'Post Tax Deductions'
, 'Foreign Tax Deductions' , 'Tax Deductions'
, pec.classification_name )) ||
decode(instr(pec.classification_name, 'Employer Charges'), 0,null,'Employer Charges') label,
decode(pec.classification_name,'Foreign Tax Deductions',(-1 * NVL(prrv1.result_value,0)), prrv1.result_value) amount,
pay_au_rec_det_archive.get_element_payment_hours(prr.assignment_action_id,pet.element_type_id,prr.run_result_id,ppa.effective_date) hours, /*Bug 5603254 */
decode(pay_au_rec_det_archive.get_element_payment_rate(prr.assignment_action_id,pet.element_type_id,prr.run_result_id,ppa.effective_date), null,
(prrv1.result_value/pay_au_rec_det_archive.get_element_payment_hours(prr.assignment_action_id,pet.element_type_id,prr.run_result_id, ppa.effective_date)),
pay_au_rec_det_archive.get_element_payment_rate(prr.assignment_action_id,pet.element_type_id,prr.run_result_id,ppa.effective_date)) rate, /* 5599310 */
prr.run_result_id,
paa.source_action_id
from pay_element_types_f pet
,pay_input_values_f piv1
,pay_element_classifications pec
,pay_assignment_actions paa
,pay_payroll_actions ppa
,per_assignments_f paaf
,pay_run_results prr
,pay_run_result_values prrv1
,pay_element_classifications pec2
,pay_sub_classification_rules_f pscr
where paaf.assignment_id = c_assignment_id
and paaf.business_group_id = c_business_group_id
and paa.assignment_id = c_assignment_id
and paaf.assignment_id = paa.assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status = 'C'
and paa.tax_unit_id like c_tax_unit_id
and paaf.payroll_id = ppa.payroll_id
and paaf.business_group_id = ppa.business_group_id
and ppa.effective_date between c_start_date and c_end_date
and ppa.action_type in ('Q','R','I','B','V')
and prr.assignment_action_id = paa.assignment_action_id
and pet.element_type_id = prr.element_type_id
and pet.element_type_id = piv1.element_type_id
and prr.run_result_id = prrv1.run_result_id
and prrv1.input_value_id = piv1.input_value_id
and pet.classification_id = pec.classification_id
and pec.legislation_code = 'AU'
and piv1.name = 'Pay Value'
and pet.classification_id = pec.classification_id
and (instr(pec.classification_name, 'Earnings') > 0
or instr(pec.classification_name, 'Payments') > 0
or instr(pec.classification_name, 'Deductions') > 0
or instr(pec.classification_name, 'Employer Charges' ) > 0 )
and pet.element_type_id = pscr.element_type_id (+)
and ppa.effective_date between nvl(pscr.effective_start_date, ppa.effective_date)
and nvl(pscr.effective_end_date, ppa.effective_date)
and pscr.classification_id = pec2.classification_id(+)
and ppa.date_earned between pet.effective_start_date and pet.effective_end_date
and ppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
and prr.status in ('P','PA')
and NOT EXISTS
(
select 1
from pay_run_results prr1,
pay_element_types_f pet1
where prr1.assignment_action_id = paa.assignment_action_id
and prr1.element_type_id = pet1.element_type_id
and pet1.element_name in ('Retropay GT 12 Mths Amount',
'Retro Pre Tax GT 12 Mths Amount', /*start 9190980*/
'Retropay Earnings Spread GT 12 Mths Amount',
'Retropay Earnings Leave Loading GT 12 Mths Amount', /*end 9190980*/
'Retropay Earnings Additional GT12 Amount') -- bug 13362286
and prr1.source_id = prr.source_id
and prr.source_type='E' /*Bug 4363057 */
)
and NOT EXISTS
(
select 1
from pay_balance_feeds_f pbf,
pay_balance_types pbt
where pbt.balance_type_id = pbf.balance_type_id
and pbt.balance_name in ('Invalidity Payments','Lump Sum A Payments',
'Lump Sum B Payments','Lump Sum C Payments',
'Lump Sum D Payments','Lump Sum E Payments',/*Bug 5846278 */
'Retro Pre Tax GT 12 Mths Amount', /*start 9190980*/
'Retro Earnings Leave Loading GT 12 Mths Amount',
'Retro Earnings Spread GT 12 Mths Amount', /*end 9190980*/
'Retro Earnings Additional GT 12 Mths Amount') -- bug 13362286
and pbt.legislation_code = 'AU'
and pbf.input_value_id = piv1.input_value_id
)
and not exists /* added for bug 7571001 */
(
select 1
from
PAY_BALANCE_ATTRIBUTES pba
,pay_defined_balances pdb
,pay_balance_dimensions pbd
,PAY_BALANCE_FEEDS_F pbf
where pba.attribute_id = g_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.business_group_id = c_business_group_id
AND pbf.input_value_id = piv1.input_value_id
)
)
group by element_name,label,rate;
select /*+ ORDERED */ element_name,label, sum(amount) payment
from
(select distinct
nvl(pet.reporting_name, pet.element_name) element_name,
'Allowance' label, -- bug 7571001 Allowance only
prrv.result_value amount,
prr.run_result_id,
pac.source_action_id
FROM
per_all_assignments_f paa
,pay_assignment_actions pac
,pay_payroll_actions ppa
,pay_run_results prr
,pay_element_types_f pet
,pay_input_values_f piv
,pay_run_result_values prrv
,pay_balance_feeds_f pbf
,pay_defined_balances pdb
,pay_balance_dimensions pbd
, pay_balance_attributes pba
WHERE pba.attribute_id = g_attribute_id
AND pdb.defined_balance_id = pba.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.name ='Pay Value' -- Bug 8760756
AND piv.element_type_id = pet.element_type_id
and paa.assignment_id = c_assignment_id -- bug 12615137
and paa.business_group_id = c_business_group_id
AND pac.assignment_id = c_assignment_id
and pac.tax_unit_id = c_tax_unit_id
and paa.assignment_id = pac.assignment_id
and pac.payroll_action_id = ppa.payroll_Action_id
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 prr.run_result_id = prrv.run_result_id
and prrv.input_value_id = piv.input_value_id -- Bug 8760756
AND prr.status in ('P','PA')
AND pac.action_status ='C'
and ppa.effective_date between c_start_date and c_end_date
and ppa.effective_date between paa.effective_start_date and paa.effective_end_date
and ppa.effective_date between piv.effective_start_date and piv.effective_end_date
and ppa.effective_date between pet.effective_start_date and pet.effective_end_date
and ppa.effective_date between pbf.effective_start_date and pbf.effective_end_date
)
group by element_name,label
;
insert into pay_action_information (
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
assignment_id)
values (
pay_action_information_s.nextval,
p_assignment_action_id,
'AAP',
p_effective_date,
null,
g_tax_unit_id,
'AU_ELEMENT_RECON_DETAILS',
csr_ele_det.element_name,
csr_ele_det.classification_name,
null,
csr_ele_det.hours,
csr_ele_det.rate,
csr_ele_det.payment,
p_assignment_id);
hr_utility.set_location('After Inserting Element Values ',500);
insert into pay_action_information (
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
assignment_id)
values (
pay_action_information_s.nextval,
p_assignment_action_id,
'AAP',
p_effective_date,
null,
g_tax_unit_id,
'AU_ELEMENT_RECON_DETAILS',
csr_alw_det.element_name,
'Allowance',
null,
null, -- Bug 8760756
null, -- Bug 8760756
csr_alw_det.payment,
p_assignment_id);
hr_utility.set_location('After Inserting Allowance Element Values ',600);
insert into pay_action_information (
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
assignment_id)
values (
pay_action_information_s.nextval,
p_assignment_action_id,
'AAP',
p_effective_date,
null,
g_tax_unit_id,
'AU_ELEMENT_RECON_DETAILS',
l_ele_name(i),
l_ele_classification_name(i),
null,
null,
null,
l_ele_pay_value(i),
p_assignment_id);
SELECT pdb.defined_balance_id
,NVL(pbt.reporting_name,pbt.balance_name) balance_name
,pay_balance_pkg.get_value(pdb.defined_balance_id
,p_run_assignment_action_id
,g_tax_unit_id
,NULL,NULL,NULL,NULL,NULL,NULL,NULL) balance_value
FROM pay_balance_attributes pba
,pay_defined_balances pdb
,pay_balance_types pbt
,pay_balance_dimensions pbd
WHERE pba.attribute_id = g_attribute_id
AND pdb.defined_balance_id = pba.defined_balance_id
AND pbt.balance_type_id = pdb.balance_type_id
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.business_group_id = g_business_group_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pbd.dimension_name = '_ASG_LE_YTD'
and pbd.legislation_code = 'AU'
;
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 = g_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 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_fw_input_alw_table.delete;
f_fw_date_tab_g.delete;
j_fw_date_tab_g.delete;
g_fw_result_alw_table.delete;
p_fw_allowance_tab.delete;
insert into pay_action_information (
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10
)
values (
pay_action_information_s.nextval,
p_assignment_action_id,
'AAP',
p_effective_date,
null,
g_tax_unit_id,
p_assignment_id,
'AU_BALANCE_RECON_DETAILS_YTD',
l_YTD_TAXABLE_EARNINGS,
l_YTD_NON_TAXABLE_EARNINGS,
l_YTD_DEDUCTIONS,
l_YTD_TAX,
l_YTD_NET_PAYMENT,
l_YTD_EMPLOYER_CHARGES,
l_YTD_GROSS_EARNINGS,
l_YTD_PRE_TAX_DEDUCTIONS,
l_YTD_DIRECT_PAYMENTS,
p_run_action_sequence);
/*Bug 8587013 - The balances RESC and Exempt Foreign Employment Income are inserted into next available two columns
of PAY_ACTION_INFORMATION table and the Other Income balance is removed from archival*/
insert into pay_action_information (
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13
)
values (
pay_action_information_s.nextval,
p_assignment_action_id,
'AAP',
p_effective_date,
null,
g_tax_unit_id,
p_assignment_id,
'AU_PS_BALANCE_RECON_DETAILS',
l_YTD_PAYSUM_GROSS,
l_YTD_LUMPSUM_PAY,
l_YTD_ALLOWANCE,
l_YTD_RFB,
l_ETP_PAY,
l_ASSESSABLE_ETP,
l_YTD_CDEP,
l_YTD_UNION_FEES,
p_run_action_sequence,
L_YTD_WORKPLACE_GIVING_DED,
l_YTD_RESC,
l_YTD_FOREIGN_INCOME
); /* 4015082 , 8587013 */
insert into pay_action_information (
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12
)
values (
pay_action_information_s.nextval,
p_assignment_action_id,
'AAP',
p_effective_date,
null,
g_tax_unit_id,
p_assignment_id,
'AU_FWPS_BALANCE_RECON_DETAILS',
l_YTD_FW_PAYSUM_GROSS,
l_YTD_FW_TOTAL_TAX,
l_YTD_FW_FOREIGN_TAX,
l_YTD_FW_LUMPSUM_PAY,
l_YTD_FW_ALLOWANCE,
l_YTD_FW_RFB,
l_YTD_FW_CDEP,
l_YTD_FW_UNION_FEES,
l_YTD_FW_WORKPLACE_GIVING_DED,
l_YTD_FW_RESC,
p_run_action_sequence,
g_fw_gross_type
);
select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) business_group_id
,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) legal_employer
,pay_core_utils.get_parameter('PAYROLL',legislative_parameters) payroll_id
,pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters) assignment_id
,pay_core_utils.get_parameter('EMPLOYEE_TYPE',legislative_parameters) employee_type
,to_date('01-07-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fin_year_start_date
,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY') fin_year_end_date
,to_date('01-04-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_start_date
,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_end_date
,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_year_term
,pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug 4142159*/
,decode(pay_core_utils.get_parameter('REP_MODE',legislative_parameters),'SUMM','S','D') report_mode /* Bug 3891577*/
,pay_core_utils.get_parameter('OUTPUT_TYPE',legislative_parameters)p_output_type
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;
hr_utility.set_location('In Delete Actions'||l_parameters.delete_actions,919); /*Bug 4142159*/
'P_DELETE_ACTIONS='||l_parameters.delete_actions, /*Bug 4142159*/
'BLANKPAGES=NO',
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL
);
select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) business_group_id
,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) legal_employer
,pay_core_utils.get_parameter('PAYROLL',legislative_parameters) payroll_id
,pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters) assignment_id
,pay_core_utils.get_parameter('EMPLOYEE_TYPE',legislative_parameters) employee_type
,to_date('01-07-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fin_year_start_date
,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY') fin_year_end_date
,to_date('01-04-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_start_date
,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_end_date
,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_year_term
,pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions
,decode(pay_core_utils.get_parameter('REP_MODE',legislative_parameters),'SUMM','S','D') report_mode
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
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;
hr_utility.set_location('In Delete Actions'||l_parameters.delete_actions,919);
'P_DELETE_ACTIONS='||l_parameters.delete_actions, /*Bug 4142159*/
'BLANKPAGES=NO',
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL
);