[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',
'PAYE','NI','Earnings','Direct Payment','Pre NI Deductions','Pre Tax and NI Deductions')
AND pec.business_group_id IS NULL
AND pec.legislation_code = 'GB'
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'
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;
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',
'PAYE','NI','Earnings','Direct Payment','Pre NI Deductions','Pre Tax and NI Deductions')
AND pec.business_group_id IS NULL
AND pec.legislation_code = 'GB'
AND pet.classification_id = pec.classification_id
AND piv.element_type_id = pet.element_type_id
AND piv.name = 'Pay 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)
)
OR
(
(p_effective_end_date between piv.effective_start_date AND piv.effective_end_date)
AND (p_effective_end_date between pet.effective_start_date AND pet.effective_end_date)
)
);
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;
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 = 'GB'
AND pet.element_name = p_element_name
AND piv.name = p_value_name;
SELECT pac.context_id context_id
,pac.context_value context_value
,ff.context_name context_name
FROM ff_contexts ff
,pay_action_contexts pac
WHERE ff.context_name = p_context_name
AND pac.context_id = ff.context_id
AND pac.assignment_Action_id = p_assig_action_id;
SELECT prrv.result_value reference
FROM pay_element_entries_f peef
,pay_run_results prr
,pay_run_result_values prrv
,pay_input_values_f piv
WHERE peef.element_entry_id = p_element_entry_id
and piv.name ='Reference'
and piv.legislation_code='GB'
and peef.element_type_id = piv.element_type_id
and peef.element_type_id = prr.element_type_id
and peef.element_entry_id = prr.element_entry_id
and prr.assignment_action_id =p_assig_action_id
and prr.run_result_id = prrv.run_result_id
and prrv.input_value_id = piv.input_value_id
and p_effective_date between
peef.effective_start_date and peef.effective_end_date
and p_effective_date between
piv.effective_start_date and piv.effective_end_date;
SELECT per_information10
FROM per_all_people_f ppf,
per_all_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
and ppf.person_id = paf.person_id
and p_effective_date between
paf.effective_start_date and paf.effective_end_date
and p_effective_date between
ppf.effective_start_date and ppf.effective_end_date;
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 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 = 'GB'
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 employers_ref_no,
org.org_information2 tax_office_name,
org.org_information3 employer_name,
org.org_information4 employer_address,
org.org_information8 tax_office_phone_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 = 'Tax Details References'
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 pact.consolidation_set_id = p_consolidation_id -- 4071160
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');
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 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 ppf.person_id
FROM per_all_people_f ppf,
pay_payroll_actions ppa,
per_all_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 = 'UKPS'
and map.report_format = 'UKPSGEN'
and map.report_qualifier = 'GB'
and par.parameter_name = 'RANGE_PERSON_ID'; -- Bug fix 5567246
SELECT act.assignment_id assignment_id,
act.assignment_action_id run_action_id,
act1.assignment_action_id prepaid_action_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_all_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
as1.effective_start_date AND as1.effective_end_date
--AND act.action_status = 'C'
AND act.action_status in ('C','S') --Modified for the bug 10066755
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.action_status in ('C','S') --Modified for the bug 10066755
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 = 'UKPS')
ORDER BY act.assignment_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
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_all_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
as1.effective_start_date AND as1.effective_end_date
--AND act.action_status = 'C'
AND act.action_status in ('C','S') --Modified for the bug 10066755
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.action_status in ('C','S') --Modified for the bug 10066755
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 = 'UKPS')
ORDER BY act.assignment_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 pre.locked_action_id pre_assignment_action_id,
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,
paa.time_period_id time_period_id,
paa.action_type action_type /*Added for the bug 7502055*/
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
WHERE pre.locked_action_id = pay.locking_action_id
AND pre.locking_action_id = p_locking_action_id
AND pre.locked_action_id = passact.assignment_action_id
AND passact.payroll_action_id = ppaa.payroll_action_id
AND ppaa.action_type IN ('P','U')
AND pay.locked_action_id = assact.assignment_action_id
AND assact.payroll_action_id = paa.payroll_action_id
AND assact.source_action_id IS NULL
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 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.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 pact.payroll_action_id payroll_action_id,
pact.effective_date effective_date,
pact.date_earned date_earned,
pact.payroll_id,
org.org_information1 employers_ref_no,
org.org_information2 tax_office_name,
org.org_information3 employer_name,
org.org_information4 employer_address,
org.org_information8 tax_office_phone_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 = 'Tax Details References'
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 pact.consolidation_set_id = p_consolidation_id -- 4071160
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');
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');
delete from pay_action_information pai
where pai.action_context_id = p_payroll_action_id
and pai.action_context_type = 'PA'
and pai.action_information_category in ('EMPLOYEE OTHER INFORMATION')
and pai.action_information2 = 'MESG';
delete from pay_action_information pai
where pai.action_context_id = p_payroll_action_id
and pai.action_context_type = 'PA'
and pai.action_information_category in ('EMEA PAYROLL INFO');
SELECT /*+ leading(lck,paa2) */ lck.locking_action_id ACTION_CONTEXT_ID, pet.element_type_id, piv.input_value_id, pai.action_information4 NARRATIVE, pai.action_information5 PAYMENT_TYPE,
SUM(FND_NUMBER.CANONICAL_TO_NUMBER(prv.result_value)) value, SUM(FND_NUMBER.CANONICAL_TO_NUMBER(prv.result_value)) numeric_value
FROM pay_action_interlocks lck, -- archive action locking prepayment
pay_assignment_actions paa1, -- prepayment action
pay_assignment_actions paa2, -- archive action
pay_payroll_actions ppa, -- prepayment
pay_action_information pai, -- archived element/input value definition
pay_action_interlocks pac, -- prepayment locking payroll run/quickpay
pay_assignment_actions paa, -- payroll run/quickpay action
pay_payroll_actions ppa1, -- payroll run/quickpay action
pay_element_types_f pet, -- element types processed by the payroll run/quickpay
pay_input_values_f piv, -- "Pay values" of type Money
pay_run_results prr, -- run result created by the payroll run/quick pay
pay_run_result_values prv -- Run Result value (Pay Value) created by the payroll run/quickpay
WHERE lck.locking_action_id = paa2.assignment_action_id
AND paa2.payroll_action_id = pai.action_context_id
AND pai.action_context_type = 'PA'
AND pai.action_information_category = 'EMEA ELEMENT DEFINITION'
AND lck.locked_action_id = paa1.assignment_action_id
AND paa1.source_action_id IS NULL
AND paa1.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type IN ('P','U')
AND ppa.payroll_action_id = NVL (pai.action_information1,ppa.payroll_action_id)
AND paa1.assignment_action_id = pac.locking_action_id
AND pet.element_type_id = pai.action_information2
AND pet.element_type_id = piv.element_type_id
AND piv.input_value_id = pai.action_information3
AND prr.element_type_id = pet.element_type_id
AND prr.status IN ('P','PA')
AND prv.input_value_id = piv.input_value_id
AND prv.run_result_id = prr.run_result_id
AND piv.name = 'Pay Value'
AND piv.uom = 'M'
AND pac.locked_action_id = prr.assignment_action_id
AND pac.locked_action_id = paa.assignment_action_id
AND paa.payroll_action_id = ppa1.payroll_action_id
AND ppa1.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND ppa1.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND lck.locking_action_id = p_assignment_action_id
AND pai.action_information5 in (p_element_type_1 , p_element_type_2, p_element_type_3)
GROUP BY lck.locking_action_id, pet.element_type_id, piv.input_value_id, pai.action_information4, pai.action_information5;
select paa.assignment_id, ppa.effective_date
into l_assignment_id, l_effective_date
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id = p_assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id;