[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUBSTR(legislative_parameters,
INSTR(legislative_parameters,p_token)+(LENGTH(p_token)+1),
INSTR(legislative_parameters,' ',
INSTR(legislative_parameters,p_token))
- (INSTR(legislative_parameters,p_token)+LENGTH(p_token))),
business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_pact_id;
SELECT org.org_information1,
org.org_information2,
org.org_information3,
org.org_information4,
org.org_information5,
org.org_information6
FROM hr_organization_information_v org
WHERE org.org_information_context = p_context
AND org.organization_id = p_bg_id;
SELECT pbt.balance_name,
pbd.database_item_suffix,
pbt.legislation_code,
pdb.defined_balance_id
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbt.balance_type_id = p_balance_type_id
AND pbd.balance_dimension_id = p_balance_dimension_id;
SELECT pet.formula_id
FROM pay_element_types_f pet,
ff_formulas_f fff
WHERE pet.element_type_id = p_element_type_id
AND pet.formula_id = fff.formula_id
AND fff.formula_name = 'ONCE_EACH_PERIOD'
AND p_effective_date BETWEEN
fff.effective_start_date AND fff.effective_end_date
AND p_effective_date BETWEEN
pet.effective_start_date AND pet.effective_end_date;
SELECT piv.uom
FROM pay_input_values_f piv
WHERE piv.input_value_id = p_input_value_id
AND p_effective_date BETWEEN
piv.effective_start_date AND piv.effective_end_date;
SELECT pet.element_type_id,
piv.input_value_id,
NVL(pet.reporting_name,pet.element_name) element_name,
pec.classification_name,
piv.uom
FROM pay_element_classifications pec,
pay_input_values_f piv,
pay_element_types_f pet
WHERE pec.classification_name IN
('Court Orders',
'Voluntary Deductions',
'Pre-Tax Deductions',
'Pre PRSI Deduction', -- Bug 2672763
'Pre Tax and Pre PRSI Deduction', -- Bug 2672763
'PAYE',
'PRSI',
'Earnings',
'Direct Payments',
'IE Earnings Non PRSIable', -- Bug 2943335
'IE Earnings Non Taxable and Non PRSIable', -- Bug 2943335
'IE Social Benefits Clearup', -- Bug 2943335
'IE Benefit In Kind Arrearage',--Bug 2367175
'IE Benefit In Kind Arrearage Recovery',
'Advance Earnings') --Bug 3720315
AND pet.element_name <> 'IE PRSI'
and pet.element_name not in ('IE Reduced Std Rate Cut Off' , 'IE Reduced Tax Credit')
AND pec.business_group_id IS NULL
AND pec.legislation_code = 'IE'
AND pet.classification_id = pec.classification_id
AND NVL(pet.business_group_id,p_business_group_id) = p_business_group_id
AND piv.element_type_id = pet.element_type_id
AND (
(piv.name ='Pay Value' )
OR (pet.element_name in ('IE BIK Arrearage Details','IE BIK Arrearage Recovery Details') and piv.name in ('BIK Arrearage','BIK Arrearage Recovered'))
OR (pet.element_name in ('IE PAYE at higher rate','IE PAYE at standard rate') AND piv.name ='Value' ))
AND p_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND p_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
Union -- Added for bug Fix 2367175
SELECT pet.element_type_id,piv.input_value_id,
NVL(pet.reporting_name,pet.element_name) element_name,
'Information',
piv.uom
FROM pay_element_classifications pec,
pay_input_values_f piv,
pay_element_types_f pet
WHERE pec.classification_name IN ( 'Information')
AND pec.business_group_id IS NULL
AND pec.legislation_code = 'IE'
AND pet.classification_id = pec.classification_id
AND NVL(pet.business_group_id,p_business_group_id) = p_business_group_id
AND piv.element_type_id = pet.element_type_id
AND p_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND p_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
-- Changed to improve the performance 4771780
-- moving them to decode and avoiding OR condition removes merge cartesian join
AND piv.name = decode(pet.element_name,
'IE BIK Accommodation Details','Taxable Value for Run',
'IE BIK Asset Type Details','Taxable Value for Run',
'IE BIK Company Vehicle Details','Taxable Value for Run',
'IE BIK Preferential Loan Details','Taxable Value for Run',
'IE BIK Other Reportable Item Details','Taxable Value for Run',
'IE BIK Non Recurring Reportable Items','Benefit Value'
);
SELECT pdb.defined_balance_id
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbt.balance_name = p_balance
AND pbd.database_item_suffix = p_dimension
AND pbd.legislation_code = 'IE'
AND pbd.business_group_id is NULL
AND pbt.legislation_code = 'IE'
AND pbt.business_group_id is NULL
AND pdb.legislation_code = 'IE'
AND pdb.business_group_id is NULL;
SELECT effective_date
FROM pay_payroll_actions
WHERE payroll_action_id = pactid;
SELECT pet.element_type_id,
piv.input_value_id
FROM pay_input_values_f piv,
pay_element_types_f pet
WHERE piv.element_type_id = pet.element_type_id
AND pet.legislation_code = 'IE'
AND pet.element_name = p_element_name
AND piv.name = p_value_name;
SELECT period_type
FROM pay_all_payrolls_f
WHERE payroll_id = p_payroll_id
AND p_effective_date
BETWEEN effective_start_date AND effective_end_date;
SELECT pdb.defined_balance_id
FROM pay_defined_balances pdb,
pay_balance_types pbt,
pay_balance_dimensions pbd
WHERE pbd.dimension_name = '_ASG_YTD'
AND pbd.legislation_code = 'IE'
AND pbt.balance_name = 'IE PRSI Insurable Weeks'
AND pbt.legislation_code = 'IE'
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pdb.legislation_code = 'IE';
SELECT org.organization_id
FROM
pay_all_payrolls_f ppf,
hr_soft_coding_keyflex flex,
hr_organization_information org
WHERE ppf.soft_coding_keyflex_id=flex.soft_coding_keyflex_id
AND ppf.business_group_id =p_business_group_id
AND org.org_information_context = 'IE_EMPLOYER_INFO'
AND org.organization_id=flex.segment4
AND ppf.consolidation_set_id =p_consolidation_set
-- AND ppf.payroll_id=p_payroll_id
AND ppf.effective_start_date <= p_end_date
AND ppf.effective_end_date >= p_start_date
AND rownum = 1;
SELECT substr(papf.first_name||' '||papf.middle_names,1,20)
FROM per_people_f papf,
per_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_action_id = p_run_assignment_action_id
AND paf.assignment_id = paa.assignment_id
AND paf.person_id = papf.person_id
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 papf.effective_start_date
and papf.effective_end_date;
SELECT substr(papf.last_name,1,20)
FROM per_people_f papf,
per_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_action_id = p_run_assignment_action_id
AND paf.assignment_id = paa.assignment_id
AND paf.person_id = papf.person_id
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 papf.effective_start_date
and papf.effective_end_date;
SELECT to_char(papf.date_of_birth,'dd-mon-yyyy')
FROM per_people_f papf,
per_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_action_id = p_run_assignment_action_id
AND paf.assignment_id = paa.assignment_id
AND paf.person_id = papf.person_id
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 papf.effective_start_date
and papf.effective_end_date;
SELECT pac.context_id, pac.context_value
FROM pay_action_contexts pac, ff_contexts ffc
WHERE pac.assignment_action_id = p_source_id
AND ffc.context_name = 'SOURCE_TEXT'
AND ffc.context_id = pac.context_id;
SELECT Context_ID,Context_Value
FROM PAY_ACTION_CONTEXTS
WHERE Assignment_Action_ID = p_source_id;
SELECT max(paa_rev.assignment_action_id)
FROM pay_assignment_actions paa_src
,pay_assignment_actions paa_rev
,pay_assignment_actions paa_cur
,pay_payroll_actions ppa_rev
,pay_action_interlocks pai_rev
WHERE paa_cur.assignment_action_id = c_assg_action_id
AND paa_src.source_action_id = paa_cur.source_action_id
AND paa_src.assignment_id = paa_cur.assignment_id
AND pai_rev.locked_action_id = paa_src.assignment_action_id
AND ppa_rev.action_type = 'V'
AND ppa_rev.payroll_action_id = paa_rev.payroll_action_id
AND paa_rev.assignment_id = paa_src.assignment_id
AND paa_rev.assignment_action_id = pai_rev.locking_action_id;
SELECT prv.result_value
FROM pay_run_result_values prv,
pay_run_results prr
WHERE prr.status IN ('P','PA')
AND prv.run_result_id = prr.run_result_id
AND prr.assignment_action_id = p_assignment_action_id
AND prr.element_type_id = p_element_type_id
AND prv.input_value_id = p_input_value_id
AND prv.result_value IS NOT NULL;
SELECT decode(g_element_table(l_index).uom, 'M',
ltrim(rtrim(to_char(fnd_number.canonical_to_number(rec_element_value.result_value), '999999999999999990.00'))),
rec_element_value.result_value)
INTO l_result_value
FROM dual;
SELECT ppf.payroll_id
FROM
pay_all_payrolls_f ppf
WHERE ppf.consolidation_set_id=p_consolidation_set
AND ppf.business_group_id =p_business_group_id
AND ppf.effective_start_date <= p_end_date
AND ppf.effective_end_date >= p_start_date
ORDER by payroll_id;
SELECT org.org_information2
FROM
pay_all_payrolls_f ppf,
hr_soft_coding_keyflex flex,
hr_organization_information org
WHERE ppf.soft_coding_keyflex_id=flex.soft_coding_keyflex_id
AND ppf.business_group_id =p_business_group_id
AND org.org_information_context = 'IE_EMPLOYER_INFO'
AND org.organization_id=flex.segment4
AND ppf.consolidation_set_id =p_consolidation_set
AND ppf.payroll_id=p_payroll_id
AND ppf.effective_start_date <= p_end_date
AND ppf.effective_end_date >= p_start_date;
SELECT pet.element_type_id,
piv.input_value_id
FROM pay_input_values_f piv,
pay_element_types_f pet
WHERE piv.element_type_id = pet.element_type_id
AND pet.legislation_code = 'IE'
AND pet.element_name = p_element_name
AND piv.name = p_value_name;
SELECT ppf.payroll_id
FROM pay_all_payrolls_f ppf
WHERE ppf.consolidation_set_id = p_consolidation_set_id
AND ppf.payroll_id = NVL(p_payroll_id,ppf.payroll_id)
AND p_effective_date BETWEEN
ppf.effective_start_date AND ppf.effective_end_date;
SELECT pact.payroll_action_id payroll_action_id,
pact.effective_date effective_date,
pact.date_earned date_earned,
pact.payroll_id,
org.org_information1 tax_details_ref_no,
org.org_information2 employer_paye_ref_no,
ppf.payroll_name payroll_name,
ppf.period_type period_type,
pact.pay_advice_message payroll_message
FROM pay_payrolls_f ppf,
pay_payroll_actions pact,
hr_soft_coding_keyflex flex,
hr_organization_information org
WHERE ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND org.org_information_context = 'IE_ORG_INFORMATION'
AND org.org_information1 = flex.segment1
AND ppf.business_group_id = org.organization_id
AND pact.payroll_id = ppf.payroll_id
AND pact.effective_date BETWEEN
ppf.effective_start_date AND ppf.effective_end_date
AND pact.payroll_id = NVL(p_payroll_id,pact.payroll_id)
AND ppf.consolidation_set_id = p_consolidation_id
AND pact.effective_date BETWEEN
p_start_date AND p_end_date
AND (pact.action_type = 'P' OR
pact.action_type = 'U')
AND pact.action_status = 'C'
AND NOT EXISTS (SELECT NULL
FROM pay_action_information pai
WHERE pai.action_context_id = pact.payroll_action_id
AND pai.action_context_type = 'PA'
AND pai.action_information_category = 'EMEA PAYROLL INFO'
AND pai.action_information5 = g_paye_ref ) -- Bug fix 4001540
-- Added for bug fix 3567562 to restrict details fetched based on PAYE Reference.
AND org.org_information2 = flex.segment3
AND org.org_information2 = g_paye_ref;
SELECT org_info.org_information3 employer_tax_addr1
,org_info.org_information4 employer_tax_addr2
,org_info.org_information5 employer_tax_addr3
,org_info.org_information6 employer_tax_contact
,org_info.org_information7 employer_tax_ref_phone
--,org_all.name employer_tax_rep_name
--Added for bug fix 3567562,mofified source of Employer statutory reporting name
,org_info.org_information8 employer_tax_rep_name
,pcs.business_group_id business_group_id
--
FROM hr_all_organization_units org_all
,hr_organization_information org_info
,pay_consolidation_sets pcs
WHERE pcs.consolidation_set_id = c_consolidation_set
AND org_all.organization_id = pcs.business_group_id
AND org_info.organization_id = org_all.organization_id
AND org_info.org_information_context = 'IE_ORG_INFORMATION'
-- Added for bug fix 3567562 to restrict details fetched based on PAYE Reference.
AND org_info.org_information2 = g_paye_ref ;
SELECT pact.payroll_action_id payroll_action_id,
pact.effective_date effective_date,
pact.date_earned date_earned,
pact.pay_advice_message payroll_message
FROM pay_payrolls_f ppf,
pay_payroll_actions pact
WHERE pact.payroll_id = ppf.payroll_id
AND pact.effective_date BETWEEN
ppf.effective_start_date AND ppf.effective_end_date
AND pact.payroll_id = p_payroll_id
AND pact.effective_date BETWEEN
p_start_date AND p_end_date
AND (pact.action_type = 'R' OR
pact.action_type = 'Q')
AND pact.action_status = 'C'
AND NOT EXISTS (SELECT NULL
FROM pay_action_information pai
WHERE pai.action_context_id = pact.payroll_action_id
AND pai.action_context_type = 'PA'
AND pai.action_information_category = 'EMPLOYEE OTHER INFORMATION'); */
sqlstr := 'SELECT 1 FROM dual WHERE to_char(:payroll_action_id) = dummy';
sqlstr := 'SELECT DISTINCT person_id
FROM per_people_f ppf,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND ppa.business_group_id +0= ppf.business_group_id
ORDER BY ppf.person_id'; */
sqlstr := 'SELECT distinct asg.person_id
FROM per_periods_of_service pos,
per_assignments_f asg,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND pos.person_id = asg.person_id
AND pos.period_of_service_id = asg.period_of_service_id
AND pos.business_group_id = ppa.business_group_id
AND asg.business_group_id = ppa.business_group_id
ORDER BY asg.person_id';
sqlstr := 'SELECT DISTINCT ppf.person_id
FROM per_people_f ppf,
pay_payroll_actions ppa,
per_assignments_f paaf
WHERE ppa.payroll_action_id = :payroll_action_id
AND ppf.business_group_id +0 = ppa.business_group_id
AND paaf.person_id = ppf.person_id
AND paaf.payroll_id = '|| to_char(l_payroll_id) ||
' ORDER BY ppf.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 = 'IEPS'
and map.report_format = 'IELDGEN'
and map.report_qualifier = 'IE'
and par.parameter_name = 'RANGE_PERSON_ID';
SELECT act.assignment_id assignment_id,
act.assignment_action_id run_action_id,
act1.assignment_action_id prepaid_action_id,
act.tax_unit_id tax_unit_id
FROM pay_payroll_actions ppa,
pay_payroll_actions appa,
pay_payroll_actions appa2,
pay_assignment_actions act,
pay_assignment_actions act1,
pay_action_interlocks pai,
per_assignments_f as1
WHERE ppa.payroll_action_id = p_pact_id
AND appa.consolidation_set_id = p_consolidation_id
AND appa.effective_date BETWEEN
ppa.start_date AND ppa.effective_date
AND as1.person_id BETWEEN
stperson AND endperson
AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
AND act.payroll_action_id = appa.payroll_action_id
AND act.source_action_id IS NULL
AND as1.assignment_id = act.assignment_id
-- AND ppa.effective_date BETWEEN
AND appa.effective_date BETWEEN -- Bug Fix 4260031
as1.effective_start_date AND as1.effective_end_date
AND act.action_status = 'C'
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status = 'C'
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
AND (as1.payroll_id = p_payroll_id OR p_payroll_id IS NULL)
AND NOT EXISTS (SELECT /*+ ORDERED */ NULL
FROM pay_action_interlocks pai1,
pay_assignment_actions act2,
pay_payroll_actions appa3
WHERE pai1.locked_action_id = act.assignment_action_id
AND act2.assignment_action_id = pai1.locking_action_id
AND act2.payroll_action_id = appa3.payroll_action_id
AND appa3.action_type = 'X'
AND appa3.report_type = 'IEPS')
ORDER BY act.assignment_id, act.assignment_action_id
FOR UPDATE OF as1.assignment_id;
SELECT act.assignment_id assignment_id,
act.assignment_action_id run_action_id,
act1.assignment_action_id prepaid_action_id,
act.tax_unit_id tax_unit_id
FROM pay_payroll_actions ppa,
pay_payroll_actions appa,
pay_payroll_actions appa2,
pay_assignment_actions act,
pay_assignment_actions act1,
pay_action_interlocks pai,
per_assignments_f as1,
pay_population_ranges ppr
WHERE ppa.payroll_action_id = p_pact_id
AND appa.consolidation_set_id = p_consolidation_id
AND appa.effective_date BETWEEN
ppa.start_date AND ppa.effective_date
AND as1.person_id = ppr.person_id
AND ppr.chunk_number = chunk
AND ppr.payroll_action_id = p_pact_id
AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
AND act.payroll_action_id = appa.payroll_action_id
AND act.source_action_id IS NULL
AND as1.assignment_id = act.assignment_id
-- AND ppa.effective_date BETWEEN
AND appa.effective_date BETWEEN -- Bug Fix 4260031
as1.effective_start_date AND as1.effective_end_date
AND act.action_status = 'C'
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status = 'C'
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
AND (as1.payroll_id = p_payroll_id OR p_payroll_id IS NULL)
AND NOT EXISTS (SELECT /*+ ORDERED */ NULL
FROM pay_action_interlocks pai1,
pay_assignment_actions act2,
pay_payroll_actions appa3
WHERE pai1.locked_action_id = act.assignment_action_id
AND act2.assignment_action_id = pai1.locking_action_id
AND act2.payroll_action_id = appa3.payroll_action_id
AND appa3.action_type = 'X'
AND appa3.report_type = 'IEPS')
ORDER BY act.assignment_id, act.assignment_action_id
FOR UPDATE OF as1.assignment_id;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
select 'Y' PPSN_OVERRIDE --aei_information1 PPSN_OVERRIDE --6633719
from per_assignment_extra_info
where assignment_id = p_assignment_id
and aei_information_category = 'IE_ASG_OVERRIDE';
SELECT pay.locking_action_id pre_assignment_action_id, -- Bugfix 4567566
pay.locked_action_id master_assignment_action_id,
assact.assignment_id assignment_id,
assact.payroll_action_id pay_payroll_action_id,
paa.effective_date effective_date,
ppaa.effective_date pre_effective_date,
paa.date_earned date_earned,
ptp.time_period_id time_period_id
FROM pay_action_interlocks pre,
pay_action_interlocks pay,
pay_payroll_actions paa,
pay_payroll_actions ppaa,
pay_assignment_actions assact,
pay_assignment_actions passact,
per_time_periods ptp -- added to fetch correct time period id
WHERE pre.locked_action_id = pay.locked_action_id
AND pre.locking_action_id = p_locking_action_id
AND pre.locked_action_id = assact.assignment_action_id
AND assact.payroll_action_id = paa.payroll_action_id
AND paa.action_type in ('R','Q')
AND pay.locking_action_id = passact.assignment_action_id
AND passact.payroll_action_id = ppaa.payroll_action_id
AND ppaa.action_type IN ('P','U')
AND assact.source_action_id IS NULL
AND paa.payroll_id = ptp.payroll_id
AND paa.date_earned between ptp.start_date and ptp.end_date
ORDER BY pay.locked_action_id;
SELECT paa.assignment_action_id child_assignment_action_id,
'S' run_type
FROM pay_assignment_actions paa,
pay_run_types_f prt
WHERE paa.source_action_id = p_master_assignment_action
AND paa.payroll_action_id = p_payroll_action_id
AND paa.assignment_id = p_assignment_id
AND paa.run_type_id = prt.run_type_id
AND prt.run_method = 'S'
AND p_effective_date BETWEEN
prt.effective_start_date AND prt.effective_end_date
UNION
SELECT paa.assignment_action_id child_assignment_action_id,
'NP' run_type
FROM pay_assignment_actions paa
WHERE paa.payroll_action_id = p_payroll_action_id
AND paa.assignment_id = p_assignment_id
AND paa.action_sequence = (SELECT MAX(paa1.action_sequence)
FROM pay_assignment_actions paa1,
pay_run_types_f prt1
WHERE prt1.run_type_id = paa1.run_type_id
AND prt1.run_method IN ('N','P')
AND paa1.payroll_action_id = p_payroll_action_id
AND paa1.assignment_id = p_assignment_id
AND paa1.source_action_id = p_master_assignment_action
AND p_effective_date BETWEEN
prt1.effective_start_date AND prt1.effective_end_date);*/
/*SELECT paa.assignment_action_id child_assignment_action_id,
'S' run_type
FROM pay_assignment_actions paa,
pay_run_types_f prt
WHERE paa.run_type_id = prt.run_type_id
AND prt.run_method = 'S'
AND p_effective_date BETWEEN prt.effective_start_date AND prt.effective_end_date
AND paa.assignment_action_id = (SELECT /*+ USE_NL(paa, ppa)
fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16)) child_assignment_action_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_id = p_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND (paa.source_action_id is not null or ppa.action_type in ('I','V','B'))
AND ppa.effective_date <= p_effective_date
AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
AND paa.action_status = 'C')
UNION */
-- Bug Fix 4260031
SELECT paa.assignment_action_id child_assignment_action_id,
prt.run_method run_type
FROM pay_assignment_actions paa,
pay_run_types_f prt
WHERE paa.run_type_id = prt.run_type_id
AND prt.run_method IN ('N','P')
AND p_effective_date BETWEEN prt.effective_start_date AND prt.effective_end_date
AND paa.assignment_action_id = (SELECT /*+ USE_NL(paa, ppa) */
fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16)) child_assignment_action_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_id = p_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND (paa.source_action_id is not null or ppa.action_type in ('I','V'))
AND ppa.effective_date between trunc(p_effective_date,'Y') and p_effective_date
AND ppa.action_type in ('R', 'Q', 'I', 'V') -- Removed B as run type is not populated 4606580
AND paa.action_status = 'C');
SELECT paa.assignment_action_id np_assignment_action_id,
prt.run_method
FROM pay_assignment_actions paa,
pay_run_types_f prt
WHERE paa.source_action_id = p_assignment_action_id
AND paa.payroll_action_id = p_payroll_action_id
AND paa.assignment_id = p_assignment_id
AND paa.run_type_id = prt.run_type_id
AND prt.run_method IN ('N','P')
AND p_effective_date BETWEEN
prt.effective_start_date AND prt.effective_end_date;*/
SELECT paa.assignment_action_id np_assignment_action_id,
prt.run_method
FROM pay_assignment_actions paa,
pay_run_types_f prt
WHERE paa.run_type_id = prt.run_type_id
AND prt.run_method IN ('N','P')
AND p_effective_date BETWEEN prt.effective_start_date AND prt.effective_end_date
AND paa.assignment_action_id = (SELECT /*+ USE_NL(paa, ppa)
fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_id = p_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND (paa.source_action_id is not null or ppa.action_type in ('I','V','B'))
AND ppa.effective_date between trunc(p_effective_date,'Y') and p_effective_date
AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
AND paa.action_status = 'C'); */
SELECT paa.chunk_number
INTO l_chunk_number
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = p_assactid;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM pay_action_information pai
WHERE pai.action_context_id = p_pact_id
AND pai.action_context_type = 'PA'
AND rownum = 1
);
SELECT pet.element_type_id,
piv.input_value_id
FROM pay_input_values_f piv,
pay_element_types_f pet
WHERE piv.element_type_id = pet.element_type_id
AND pet.legislation_code = 'IE'
AND pet.element_name = p_element_name
AND piv.name = p_value_name;
SELECT ppf.payroll_id
FROM pay_all_payrolls_f ppf
WHERE ppf.consolidation_set_id = p_consolidation_set_id
AND ppf.payroll_id = NVL(p_payroll_id,ppf.payroll_id)
AND p_effective_date BETWEEN
ppf.effective_start_date AND ppf.effective_end_date;
SELECT pact.payroll_action_id payroll_action_id,
pact.effective_date effective_date,
pact.date_earned date_earned,
pact.payroll_id,
org.org_information1 tax_details_ref_no,
org.org_information2 employer_paye_ref_no,
ppf.payroll_name payroll_name,
ppf.period_type period_type,
pact.pay_advice_message payroll_message
FROM pay_payrolls_f ppf,
pay_payroll_actions pact,
hr_soft_coding_keyflex flex,
hr_organization_information org
WHERE ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND org.org_information_context = 'IE_EMPLOYER_INFO'
AND org.organization_id = flex.segment4
-- AND ppf.business_group_id = org.organization_id
AND pact.payroll_id = ppf.payroll_id
AND pact.effective_date BETWEEN
ppf.effective_start_date AND ppf.effective_end_date
AND pact.payroll_id = NVL(p_payroll_id,pact.payroll_id)
AND ppf.consolidation_set_id = p_consolidation_id
AND (pact.action_type = 'P' OR
pact.action_type = 'U')
AND pact.action_status = 'C'
AND exists (SELECT NULL
FROM pay_assignment_actions paa,
pay_action_interlocks pai,
pay_assignment_actions paa_arc
WHERE pai.locked_action_id = paa.assignment_action_id
AND pai.locking_action_id = paa_arc.assignment_action_id
AND paa_arc.payroll_action_id = p_pact_id
AND paa.payroll_action_id = pact.payroll_action_id)
AND NOT EXISTS (SELECT NULL
FROM pay_action_information pai
WHERE pai.action_context_id = pact.payroll_action_id
AND pai.action_context_type = 'PA'
AND pai.action_information_category = 'EMEA PAYROLL INFO'
AND pai.action_information5 = g_paye_ref ) -- Bug fix 4001540
-- Added for bug fix 3567562 to restrict details fetched based on PAYE Reference.
-- AND org.org_information2 = flex.segment3
AND org.org_information2 = g_paye_ref;
SELECT hrl.address_line_1 employer_tax_addr1
,hrl.address_line_2 employer_tax_addr2
,hrl.address_line_3 employer_tax_addr3
,org_info.org_information4 employer_tax_contact
,hrl.telephone_number_1 employer_tax_ref_phone
--,org_all.name employer_tax_rep_name
--Added for bug fix 3567562,mofified source of Employer statutory reporting name
,org_all.name employer_tax_rep_name
,pcs.business_group_id business_group_id
--
FROM hr_all_organization_units org_all
,hr_organization_information org_info
,pay_consolidation_sets pcs
,hr_locations_all hrl
WHERE pcs.consolidation_set_id = c_consolidation_set
AND org_all.business_group_id = pcs.business_group_id
AND org_info.organization_id = org_all.organization_id
--Changed to handle new Employer architecture(4369280)
AND org_info.org_information_context = 'IE_EMPLOYER_INFO'
AND org_all.location_id = hrl.location_id (+)
-- Added for bug fix 3567562 to restrict details fetched based on PAYE Reference.
AND org_info.org_information2 = g_paye_ref ;
SELECT pact.payroll_action_id payroll_action_id,
pact.effective_date effective_date,
pact.date_earned date_earned,
pact.pay_advice_message payroll_message
FROM pay_payrolls_f ppf,
pay_payroll_actions pact
WHERE pact.payroll_id = ppf.payroll_id
AND pact.effective_date BETWEEN
ppf.effective_start_date AND ppf.effective_end_date
AND pact.payroll_id = p_payroll_id
AND pact.effective_date BETWEEN
p_start_date AND p_end_date
AND (pact.action_type = 'R' OR
pact.action_type = 'Q')
AND pact.action_status = 'C'
AND NOT EXISTS (SELECT NULL
FROM pay_action_information pai
WHERE pai.action_context_id = pact.payroll_action_id
AND pai.action_context_type = 'PA'
AND pai.action_information_category = 'EMPLOYEE OTHER INFORMATION');