[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select business_group_id,
effective_date,
legislative_parameters
into g_bg_id,
g_effective_date,
l_legislative_parameters
from pay_payroll_actions
where payroll_action_id = p_arch_payroll_action_id;
SELECT pes.element_set_name
FROM pay_element_sets_vl pes,
pay_payroll_actions rppa, -- run pact
pay_assignment_actions rpaa, -- run assact
pay_action_interlocks rpai -- run interlock by archive assact
WHERE rpai.locking_action_id = cp_arch_assignment_action_id
AND rpaa.assignment_action_id = rpai.locked_action_id
AND rppa.payroll_action_id = rpaa.payroll_action_id
-- Element Set is available only when "Run"
-- waste of resource to check action_type.
-- AND rppa.action_type = 'R'
AND pes.element_set_id = rppa.element_set_id
ORDER BY rpaa.action_sequence desc;
SELECT /*+ ORDERED */
rpaa.assignment_id,
prr.element_type_id,
NVL(pettl.reporting_name, pettl.element_name) reporting_name,
prrv.input_value_id,
prrv.result_value,
decode(pbf.scale, 1, 'E', -1, 'D', 'O') payment_type
FROM pay_action_interlocks pai, -- run interlock by archive assact
pay_assignment_actions rpaa, -- run assact
pay_payroll_actions rppa, -- run pact
pay_run_results prr,
pay_run_result_values prrv,
pay_balance_feeds_f pbf,
pay_element_types_f_tl pettl
WHERE pai.locking_action_id = cp_arch_assignment_action_id
AND rpaa.assignment_action_id = pai.locked_action_id
AND rppa.payroll_action_id = rpaa.payroll_action_id
AND rppa.action_type in ('R', 'Q', 'B')
AND prr.assignment_action_id = rpaa.assignment_action_id
AND prr.status IN ('P', 'PA')
AND prrv.run_result_id = prr.run_result_id
AND prrv.result_value IS NOT NULL
and pbf.balance_type_id = c_net_pay_bal_id
and pbf.input_value_id = prrv.input_value_id
and rppa.effective_date
between pbf.effective_start_date and pbf.effective_end_date
AND pettl.element_type_id = prr.element_type_id
AND pettl.language = userenv('LANG');
SELECT 'Y'
FROM pay_element_classifications pec,
pay_sub_classification_rules_f sub
WHERE pec.classification_name = c_yea_deduction_cl
and sub.element_type_id = cp_element_type_id
AND pec.classification_id = sub.classification_id
and cp_run_effective_date
between sub.effective_start_date and sub.effective_end_date;
select /*+ ORDERED */
ppaa.assignment_action_id prepay_assignment_action_id,
pppa.effective_date prepay_effective_date,
ppaa.assignment_id prepay_assignment_id
from pay_action_interlocks ppai, -- prepay interlocks by arch assact
pay_assignment_actions ppaa, -- prepay assact
pay_payroll_actions pppa -- prepay pact
where ppai.locking_action_id = cp_arch_assignment_action_id
and ppaa.assignment_action_id = ppai.locked_action_id
and pppa.payroll_action_id = ppaa.payroll_action_id
and pppa.action_type in ('P', 'U');
select /*+ ORDERED USE_NL(PPP OPM OPMTL PPT PEA PPM BNK BCH) */
pea.segment1 bank_code,
bnk.bank_name,
bch.branch_name,
pea.segment4 branch_code,
hr_general.decode_lookup('PAY_METHOD_PAYMENT_TYPE',
decode(ppt.category, 'CA', 'CASH', 'MT', 'DEPOSIT', NULL)) payment_type_meaning,
hr_general.decode_lookup('JP_BANK_ACCOUNT_TYPE', pea.segment7) account_type_meaning,
pea.segment7 account_type,
pea.segment8 account_number,
pea.segment9 account_name,
pea.segment10 description1,
ppp.value,
-- ppp.pre_payment_id,
opm.org_payment_method_id,
opmtl.org_payment_method_name,
opm.currency_code,
ppm.personal_payment_method_id
from (
select paa.assignment_action_id
from pay_assignment_actions paa
connect by prior paa.assignment_action_id = paa.source_action_id
start with paa.assignment_action_id = cp_prepay_assignment_action_id
) v,
pay_pre_payments ppp,
pay_org_payment_methods_f opm,
pay_org_payment_methods_f_tl opmtl,
pay_payment_types ppt,
pay_external_accounts pea,
pay_personal_payment_methods_f ppm,
pay_jp_banks bnk,
pay_jp_bank_branches bch
where ppp.assignment_action_id = v.assignment_action_id
and opm.org_payment_method_id = ppp.org_payment_method_id
and cp_prepay_effective_date
between opm.effective_start_date and opm.effective_end_date
-- Exclude 3rd party pay
and opm.defined_balance_id is not null
and opmtl.org_payment_method_id = opm.org_payment_method_id
and opmtl.language = userenv('LANG')
and ppt.payment_type_id = opm.payment_type_id
-- Exclude 3rd party payment
and ppm.personal_payment_method_id(+) = ppp.personal_payment_method_id
and cp_prepay_effective_date
between ppm.effective_start_date(+) and ppm.effective_end_date(+)
and pea.external_account_id(+) = ppm.external_account_id
and bnk.bank_code(+) = pea.segment1
and bch.bank_code(+) = pea.segment1
and bch.branch_code(+) = pea.segment4;
SELECT 'Y'
FROM hr_organization_information
WHERE organization_id = cp_organization_id
AND org_information_context = cp_org_info_context;
SELECT /*+ ORDERED */
rpaa.assignment_id,
hoi.org_information2 element_type_id,
hoi.org_information3 input_value_id,
nvl(hoi.org_information7, nvl(pettl.reporting_name, pettl.element_name)) reporting_name,
piv.uom,
prrv.result_value,
piv.lookup_type,
piv.value_set_id
FROM pay_action_interlocks rpai, -- run interlock by archive assact
pay_assignment_actions rpaa, -- run assact
pay_payroll_actions rppa, -- run pact
pay_run_results prr,
hr_organization_information hoi,
pay_element_types_f pet,
pay_element_types_f_tl pettl,
pay_input_values_f piv,
pay_run_result_values prrv
WHERE rpai.locking_action_id = cp_arch_assignment_action_id
AND rpaa.assignment_action_id = rpai.locked_action_id
AND rppa.payroll_action_id = rpaa.payroll_action_id
AND rppa.action_type in ('R', 'Q', 'B')
AND prr.assignment_action_id = rpaa.assignment_action_id
AND prr.status IN ('P', 'PA')
AND hoi.organization_id = cp_organization_id
AND hoi.org_information_context = cp_org_information_context
AND hoi.org_information1 = 'ELEMENT'
AND fnd_number.canonical_to_number(hoi.org_information2) = prr.element_type_id
and pet.element_type_id = prr.element_type_id
and rppa.effective_date
between pet.effective_start_date and pet.effective_end_date
AND pettl.element_type_id = pet.element_type_id
AND pettl.language = userenv('LANG')
AND piv.input_value_id = fnd_number.canonical_to_number(hoi.org_information3)
AND rppa.effective_date
between piv.effective_start_date and piv.effective_end_date
AND prrv.input_value_id = piv.input_value_id
AND prrv.run_result_id = prr.run_result_id;
SELECT pai.action_information_id,
pai.object_version_number
FROM pay_action_information pai
WHERE pai.action_context_id = cp_arch_assignment_action_id
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = 'EMPLOYEE OTHER INFORMATION'
AND pai.action_information2 = 'BALANCE';
-- Delete balances which has archived in get_personal_information procedure
-- because balances archived are based on prepay assact, not run assact,
-- which means only balances with "_PAYMENTS" dimensions are archived.
--
FOR l_emp_other_info_bal_rec IN csr_emp_other_info_bal(p_arch_assignment_action_id) LOOP
pay_action_information_api.delete_action_information(
p_action_information_id => l_emp_other_info_bal_rec.action_information_id,
p_object_version_number => l_emp_other_info_bal_rec.object_version_number);
pay_emp_action_arch.insert_rows_thro_api_process(
p_action_context_id => p_arch_assignment_action_id,
p_action_context_type => 'AAP',
p_assignment_id => p_arch_assignment_id,
p_tax_unit_id => NULL,
p_curr_pymt_eff_date => g_payment_date,
p_tab_rec_data => pay_emp_action_arch.lrr_act_tab);
'SELECT DISTINCT per.person_id
FROM per_all_people_f per,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND ppa.business_group_id + 0 = per.business_group_id
ORDER BY per.person_id';
SELECT /*+ ORDERED */
ppaa.assignment_id prepay_assignment_id,
ppaa.assignment_action_id prepay_assignment_action_id,
rpaa.assignment_action_id run_assignment_action_id
FROM pay_payroll_actions xppa, -- archive pact
pay_payroll_actions pppa, -- prepay pact
pay_assignment_actions ppaa, -- prepay assact
per_all_assignments_f paaf,
pay_action_interlocks rpai, -- run interlock by archive assact
pay_assignment_actions rpaa, -- run assact
pay_payroll_actions rppa -- run pact
WHERE xppa.payroll_action_id = cp_payroll_action_id
AND pppa.payroll_id = cp_payroll_id
AND pppa.consolidation_set_id = cp_consolidation_set_id
AND pppa.action_type IN ('P', 'U')
AND pppa.effective_date
BETWEEN xppa.start_date AND xppa.effective_date
AND ppaa.payroll_action_id = pppa.payroll_action_id
-- Only lock master prepayment assignment action
AND ppaa.source_action_id is null
AND ppaa.action_status = 'C'
AND paaf.assignment_id = ppaa.assignment_id
AND xppa.effective_date
BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.person_id
BETWEEN cp_start_person_id AND cp_end_person_id
-- The following payroll_id validation will removed in near future.
AND paaf.payroll_id + 0 = pppa.payroll_id
AND rpai.locking_action_id = ppaa.assignment_action_id
AND rpaa.assignment_action_id = rpai.locked_action_id
-- AND rpaa.action_status = 'C'
AND rppa.payroll_action_id = rpaa.payroll_action_id
AND rppa.action_type IN ('R', 'Q', 'B')
AND NOT EXISTS(
SELECT /*+ ORDERED */
NULL
FROM pay_action_interlocks xpai2,
pay_assignment_actions xpaa2,
pay_payroll_actions xppa2
WHERE xpai2.locked_action_id = ppaa.assignment_action_id
AND xpaa2.assignment_action_id = xpai2.locking_action_id
AND xppa2.payroll_action_id = xpaa2.payroll_action_id
AND xppa2.action_type = 'X'
AND xppa2.report_type = 'JPPS')
AND NOT EXISTS(
SELECT /*+ ORDERED */
null
FROM pay_action_interlocks vpai,
pay_assignment_actions vpaa,
pay_payroll_actions vppa
WHERE vpai.locked_action_id = rpaa.assignment_action_id
AND vpaa.assignment_action_id = vpai.locking_action_id
AND vppa.payroll_action_id = vpaa.payroll_action_id
AND vppa.action_type = 'V')
ORDER BY ppaa.assignment_id, ppaa.assignment_action_id
FOR UPDATE OF paaf.assignment_id;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_arch_assignment_action_id
FROM dual;
hr_utility.trace('inserting into PAY_ASSIGNMENT_ACTIONS');
hr_utility.trace('inserting into PAY_ACTION_INTERLOCKS (PREPAY)');
hr_utility.trace('inserting into PAY_ACTION_INTERLOCKS (RUN)');
select /*+ ORDERED */
xpaa.assignment_id,
rpaa.assignment_action_id,
rpaa.assignment_id,
rppa.effective_date,
rppa.time_period_id,
asg.organization_id
into l_arch_assignment_id,
l_run_assignment_action_id,
l_run_assignment_id,
l_run_effective_date,
l_time_period_id,
l_organization_id
from pay_assignment_actions xpaa, -- archive assact
pay_action_interlocks rpai, -- run interlock by archive
pay_assignment_actions rpaa, -- run assact
pay_payroll_actions rppa, -- run pact
per_all_assignments_f asg
where xpaa.assignment_action_id = p_assignment_action_id
and rpai.locking_action_id = xpaa.assignment_action_id
and rpaa.assignment_action_id = rpai.locked_action_id
and rppa.payroll_action_id = rpaa.payroll_action_id
and rppa.action_type in ('R', 'Q', 'B')
and asg.assignment_id = rpaa.assignment_id
and rppa.effective_date
between asg.effective_start_date and asg.effective_end_date
and not exists(
select /*+ ORDERED */
null
from pay_action_interlocks rpai2, -- run interlock by archive
pay_assignment_actions rpaa2, -- run assact
pay_payroll_actions rppa2 -- run pact
where rpai2.locking_action_id = xpaa.assignment_action_id
and rpaa2.assignment_action_id = rpai2.locked_action_id
and rpaa2.action_sequence > rpaa.action_sequence
and rppa2.payroll_action_id = rpaa2.payroll_action_id
and rppa2.action_type in ('R', 'Q', 'B'));
pay_emp_action_arch.insert_rows_thro_api_process(
p_action_context_id => p_assignment_action_id,
p_action_context_type => 'AAP',
p_assignment_id => l_arch_assignment_id,
p_tax_unit_id => null,
p_curr_pymt_eff_date => l_run_effective_date,
p_tab_rec_data => pay_emp_action_arch.lrr_act_tab);
select 'Y'
from dual
where exists(
select null
from pay_action_information
where action_context_id = p_payroll_action_id
and action_context_type = 'PA');