[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT formula_id
FROM ff_formulas_f
WHERE formula_name = 'AU_PAYROLL_TAX'
AND p_start_date between effective_start_date and effective_end_date
;
SELECT 'X' INTO l_session_flag
FROM fnd_sessions
WHERE session_id = USERENV('SESSIONID');
insert into fnd_sessions (SESSION_ID, EFFECTIVE_DATE) values (userenv('sessionid'),trunc(p_start_date));
SELECT decode(pbt.balance_name,'Payroll_Tax_Salaries_Wages',1,'Payroll_Tax_Commissions',2,
'Payroll_Tax_Bonuses_Allowances',3,'Payroll_Tax_Director_Fees',4,
'Payroll_Tax_Fringe_Benefits',7,'Payroll_Tax_Superannuation',8,
'Payroll_Tax_Contractor_Payments',9, 'Payroll_Tax_Other_Taxable_Payments' , '10'
) sort_index,
pdb.defined_balance_id defined_balance_id
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pbt.balance_name IN ( 'Payroll_Tax_Salaries_Wages', 'Payroll_Tax_Commissions' ,
'Payroll_Tax_Bonuses_Allowances', 'Payroll_Tax_Director_Fees',
'Payroll_Tax_Fringe_Benefits', 'Payroll_Tax_Superannuation', 'Payroll_Tax_Contractor_Payments',
'Payroll_Tax_Other_Taxable_Payments' )
AND pbd.database_item_suffix = c_database_item_suffix
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;
g_balance_value_tab.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 pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
to_date(pay_core_utils.get_parameter('PERIOD',legislative_parameters),'YYYY/MM/DD') period, /*4709766 */
to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') end_date,
pay_core_utils.get_parameter('TAX_STATE',legislative_parameters) Tax_state,
pay_core_utils.get_parameter('REP_TYPE',legislative_parameters)report_type,
pay_core_utils.get_parameter('REP_NAME',legislative_parameters)report_name,
pay_core_utils.get_parameter('ACTT',legislative_parameters)act_override_threshold,
pay_core_utils.get_parameter('VICT',legislative_parameters)vic_override_threshold,
pay_core_utils.get_parameter('NSWT',legislative_parameters)nsw_override_threshold,
pay_core_utils.get_parameter('QLDT',legislative_parameters)qld_override_threshold,
pay_core_utils.get_parameter('WAT',legislative_parameters)wa_override_threshold,
pay_core_utils.get_parameter('NTT',legislative_parameters)nt_override_threshold,
pay_core_utils.get_parameter('SAT',legislative_parameters)sa_override_threshold,
pay_core_utils.get_parameter('TAST',legislative_parameters)tas_override_threshold
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = c_payroll_action_id;
SELECT pdb.defined_balance_id defined_balance_id
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pbt.balance_name = 'Payroll_Tax_' || c_state_code
AND pbd.database_item_suffix = c_database_item_suffix
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbt.legislation_code = 'AU';
SELECT pdb.defined_balance_id defined_balance_id
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pbt.balance_name = 'Payroll_Tax_' || c_state_code || '_Termination_Payments'
AND pbd.database_item_suffix = c_database_item_suffix
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbt.legislation_code = 'AU';
SELECT pdb.defined_balance_id defined_balance_id
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pbt.balance_name = 'Payroll_Tax_' || c_state_code || '_Eligible_Termination_Payments'
AND pbd.database_item_suffix = c_database_item_suffix
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbt.legislation_code = 'AU';
select distinct per_information2, paa.tax_unit_id,
assignment_action_id assignment_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_people_f pap,
per_assignments_f paf
where paa.payroll_action_id = ppa.payroll_action_id
and pap.person_id = paf.person_id
and paa.assignment_id = paf.assignment_id
and paa.tax_unit_id=nvl(c_registered_employer, paa.tax_unit_id)
and paf.business_group_id=c_business_group_id
and ppa.action_status='C'
and (pap.per_information3 = 'N' or pap.per_information3 is null)
and ppa.action_type in ('R','Q','I','B','V')
and ppa.effective_date between c_start_date and c_end_date
and ppa.effective_date between pap.effective_start_date and pap.effective_end_date /* 4729052 */
AND (paa.source_action_id IS NULL
OR (paa.source_action_id IS NOT NULL AND ppa.run_type_id IS NULL))
and paf.effective_end_date = (select max(effective_end_date) /* 4729052 */
From per_assignments_f iipaf
WHERE iipaf.assignment_id = paf.assignment_id
and iipaf.effective_end_date >= c_start_date
and iipaf.effective_start_date <= c_end_date)
order by per_information2;
select paa.assignment_action_id,
paa.action_sequence,
paaf.assignment_id,
paa.tax_unit_id,
paa.source_action_id master_action_id,
paa2.tax_unit_id master_tax_unit_id
from per_people_f pap,
per_assignments_f paaf,
pay_payroll_actions ppa,
pay_payroll_actions ppa1,
pay_assignment_actions paa,
pay_assignment_actions paa2,
per_periods_of_service pps
where ppa.payroll_action_id = p_payroll_action_id
and paa.assignment_id = paaf.assignment_id
and paa2.assignment_id = paaf.assignment_id
AND paa2.assignment_id = paa.assignment_id
and pap.person_id between p_start_person and p_end_person
and pap.person_id = paaf.person_id
and pap.person_id = pps.person_id
and pps.period_of_service_id = paaf.period_of_service_id
and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
and ppa1.payroll_action_id = paa.payroll_action_id
and ppa1.payroll_action_id = paa2.payroll_action_id
AND paa2.action_status ='C'
AND paa.action_status ='C'
and (pap.per_information3 = 'N' or pap.per_information3 is null)
AND (pap.per_information2 = p_tax_state or p_tax_state is null)
AND paa2.assignment_action_id = nvl(paa.source_action_id, paa.assignment_action_id)
AND (paa.source_action_id IS NULL)
-- OR (paa.source_action_id IS NOT NULL AND ppa.run_type_id IS NULL))
and ppa1.business_group_id = ppa.business_group_id
and ppa.business_group_id = p_business_group_id
and ppa1.action_type in ('R','Q','I','B','V')
and ( paa.tax_unit_id = p_legal_employer or p_legal_employer is null)
and ppa1.effective_date between p_archive_start_date and p_archive_end_date
and paaf.effective_end_date = (select max(effective_end_date)
From per_assignments_f iipaf
WHERE iipaf.assignment_id = paaf.assignment_id
and iipaf.effective_end_date >= p_archive_start_date
and iipaf.effective_start_date <= p_archive_end_date)
order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id, paa.source_action_id, paa2.tax_unit_id;
SELECT pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
to_date(pay_core_utils.get_parameter('PERIOD',legislative_parameters),'YYYY/MM/DD') period,
to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') end_date,
pay_core_utils.get_parameter('TAX_STATE',legislative_parameters) Tax_state,
pay_core_utils.get_parameter('REP_TYPE',legislative_parameters)report_type,
pay_core_utils.get_parameter('REP_NAME',legislative_parameters)report_name,
pay_core_utils.get_parameter('ACTT',legislative_parameters)act_override_threshold,
pay_core_utils.get_parameter('VICT',legislative_parameters)vic_override_threshold,
pay_core_utils.get_parameter('NSWT',legislative_parameters)nsw_override_threshold,
pay_core_utils.get_parameter('QLDT',legislative_parameters)qld_override_threshold,
pay_core_utils.get_parameter('WAT',legislative_parameters)wa_override_threshold,
pay_core_utils.get_parameter('NTT',legislative_parameters)nt_override_threshold,
pay_core_utils.get_parameter('SAT',legislative_parameters)sa_override_threshold,
pay_core_utils.get_parameter('TAST',legislative_parameters)tas_override_threshold
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = c_payroll_action_id;
select pay_assignment_actions_s.nextval
from dual;
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,
assignment_id
)
values(
pay_action_information_s.nextval,
l_next_assignment_action_id,
'AAP',
null,
null,
csr_rec.tax_unit_id,
'AU_ARCHIVE_ASG_DETAILS',
csr_rec.assignment_action_id,
p_payroll_action_id,
csr_rec.action_sequence,
csr_rec.assignment_id
);
select full_name
from per_people_f pap
where person_id= c_person_id
and pap.effective_end_date =
(select max(effective_end_date)
from per_people_f pap1
where pap1.person_id = pap.person_id
and pap1.effective_end_date >= c_start_date
and pap1.effective_start_date <= c_end_date
);
select /* pap.full_name, */
paaf.assignment_number employee_number,
ppa1.effective_date,
paa2.assignment_action_id,
paaf.assignment_id,
paaf.organization_id,
hou.NAME organization_name,
hsc.segment1 tax_unit_id,
hou1.NAME Legal_Employer ,
pap.person_id,
pap.per_information2 state_code ,
hoi.org_information1 business_group_id,
/* 4716254 ,4718544 */
decode(pap.per_information2 , 'VIC', hoi.org_information4,
'WA' , hoi.org_information11,
'QLD' , hoi.org_information7,
'SA' , hoi.org_information8,
'NSW' , hoi.org_information5,
'ACT' , hoi.org_information10,
'NT' , hoi.org_information9,
'TAS' , hoi.org_information6) dge_state,
/* 4716254 ,4718544 */
hoi.org_information2 dge_legal_employer,
hoi.org_information3 dge_group_name,
hl.meaning state_desc
from per_people_f pap,
per_assignments_f paaf,
pay_payroll_actions ppa,
pay_payroll_actions ppa1,
pay_assignment_actions paa,
pay_assignment_actions paa2,
hr_soft_coding_keyflex hsc,
hr_organization_units hou,
hr_organization_units hou1,
hr_organization_information hoi,
hr_lookups hl,
per_periods_of_service pps
where ppa.payroll_action_id = c_payroll_action_id
and paa.assignment_Action_id = c_assignment_Action_id /*5235423 */
and paa.assignment_id = paaf.assignment_id
and pap.person_id = paaf.person_id
and paa.assignment_id = c_assignment_id
AND pap.per_information2 = hl.lookup_code
AND hl.lookup_type = 'AU_STATE'
AND hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
AND hou.organization_id = paaf.organization_id
and hoi.org_information_context(+) = 'AU_PAYROLL_TAX_DGE'
AND hou1.organization_id = hoi.organization_id(+)
AND hou1.organization_id = hsc.segment1
AND paa2.assignment_id = paa.assignment_id
and pap.person_id = pps.person_id
and pps.period_of_service_id = paaf.period_of_service_id
and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
and ppa1.payroll_action_id = paa.payroll_action_id
and ppa1.payroll_action_id = paa2.payroll_action_id
AND paa2.action_status ='C'
AND paa.action_status ='C'
and (pap.per_information3 = 'N' or pap.per_information3 is null)
AND paa2.assignment_action_id = nvl(paa.source_action_id, paa.assignment_action_id)
AND paa.source_action_id IS NULL /* 5235423 */
and ppa1.business_group_id = ppa.business_group_id
and ppa.business_group_id = c_business_group_id
and ppa1.action_type in ('R','Q','I','B','V')
and ppa1.effective_date between c_start_date and c_end_date
and paaf.effective_end_date = (select max(effective_end_date)
From per_assignments_f iipaf
WHERE iipaf.assignment_id = paaf.assignment_id
and iipaf.effective_end_date >= c_start_date
and iipaf.effective_start_date <= c_end_date)
/* and c_end_date between pap.effective_start_date and pap.effective_end_date */
order by paaf.assignment_id, paa2.assignment_action_id, hsc.segment1;
select pai.action_information1, pai.action_information2, pai.tax_unit_id, pai.assignment_id,pai.action_information3
from pay_action_information pai
where action_information_category = 'AU_ARCHIVE_ASG_DETAILS'
and pai.action_context_id = c_arc_ass_act_id;
SELECT pdb.defined_balance_id defined_balance_id
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pbt.balance_name = 'Payroll_Tax_' || c_state_code
AND pbd.database_item_suffix = c_database_item_suffix
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbt.legislation_code = 'AU';
SELECT pdb.defined_balance_id defined_balance_id
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pbt.balance_name = 'Payroll_Tax_' || c_state_code || '_Termination_Payments'
AND pbd.database_item_suffix = c_database_item_suffix
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbt.legislation_code = 'AU';
SELECT pdb.defined_balance_id defined_balance_id
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pbt.balance_name = 'Payroll_Tax_' || c_state_code || '_Eligible_Termination_Payments'
AND pbd.database_item_suffix = c_database_item_suffix
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbt.legislation_code = 'AU';
insert into pay_action_information(
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
action_information_category,
assignment_id,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9)
values(
pay_action_information_s.nextval,
g_arc_payroll_action_id,
'PA',
p_effective_date,
null,
l_tax_unit_id,
'AU_PAYROLL_TAX_EMPLOYEE_DETAILS',
l_assignment_id,
csr_rec.employee_number,
csr_rec.person_id,
l_full_name,
csr_rec.state_desc,
csr_rec.legal_employer,
csr_rec.state_code,
csr_rec.dge_state,
csr_rec.dge_legal_employer,
csr_rec.dge_group_name);
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,
action_information13,
action_information14,
action_information15) /* 4731692 */
values (
pay_action_information_s.nextval,
p_assignment_action_id,
'AAP',
p_effective_date,
null,
l_tax_unit_id,
l_assignment_id,
'AU_PAYROLL_TAX_BALANCE_DETAILS',
l_salaries_wages,
l_commission,
l_bonus_allowances,
l_director_fees,
l_termination_payments,
l_eligible_term_payments,
l_Fringe_Benefits,
l_Superannuation,
l_Contractor_payments,
l_Other_taxable_Income,
l_Taxable_Income,
l_max_action_sequence,
g_le_taxable_income, /* 4713372 */
g_count,
csr_rec.state_code); /* 4731692 */
SELECT pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
to_date(pay_core_utils.get_parameter('PERIOD',legislative_parameters),'YYYY/MM/DD') period,
to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') end_date,
pay_core_utils.get_parameter('TAX_STATE',legislative_parameters) Tax_state,
pay_core_utils.get_parameter('REP_TYPE',legislative_parameters)report_type,
pay_core_utils.get_parameter('REP_NAME',legislative_parameters)report_name,
pay_core_utils.get_parameter('ACTT',legislative_parameters)act_override_threshold,
pay_core_utils.get_parameter('VICT',legislative_parameters)vic_override_threshold,
pay_core_utils.get_parameter('NSWT',legislative_parameters)nsw_override_threshold,
pay_core_utils.get_parameter('QLDT',legislative_parameters)qld_override_threshold,
pay_core_utils.get_parameter('WAT',legislative_parameters)wa_override_threshold,
pay_core_utils.get_parameter('NTT',legislative_parameters)nt_override_threshold,
pay_core_utils.get_parameter('SAT',legislative_parameters)sa_override_threshold,
pay_core_utils.get_parameter('TAST',legislative_parameters)tas_override_threshold,
pay_core_utils.get_parameter('OUTPUT_TYPE',legislative_parameters) p_output_type
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = c_payroll_action_id;
select pay_core_utils.get_parameter('REP_NAME',legislative_parameters)
from pay_payroll_actions ppa
where ppa.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;