[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT end_user_column_name
FROM fnd_descr_flex_col_usage_vl
WHERE descriptive_flexfield_name like 'Action Information DF'
and descriptive_flex_context_code = cp_flex_ctx_code
AND enabled_flag = 'Y';
SELECT pbg.legislation_code
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_business_groups pbg
WHERE paa.assignment_action_id = cp_chk_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.business_group_id = pbg.business_group_id;
SELECT action_status
FROM pay_assignment_actions
WHERE assignment_action_id = cp_chk_action_id ;
SELECT pai_arch.locking_action_id
FROM pay_action_interlocks pai_chk,
pay_action_interlocks pai_arch,
pay_assignment_actions paa_arch,
pay_assignment_actions paa_chk,
pay_pre_payments ppp,
pay_payroll_actions ppa_arch,
pay_payroll_actions ppa_chk,
hr_lookups hrl
WHERE pai_chk.locking_action_id = cp_chk_action_id
--Pre-Payment Assignment Action ID is locked by both Check Writer
--and Payroll Archive Process (It can be locked by NACHA as well)
AND pai_arch.locked_action_id = pai_chk.locked_action_id
AND paa_chk.assignment_action_id = pai_chk.locking_action_id
AND paa_chk.payroll_action_id = ppa_chk.payroll_action_id
AND ppa_chk.action_type = 'H'
AND paa_chk.pre_payment_id = ppp.pre_payment_id
AND (
(ppp.source_action_id IS NOT NULL
AND SUBSTR(paa_arch.serial_number,0,2) in ('PN','UN','PY','UY') -- added for P1 14647358
AND ppp.source_action_id = fnd_number.canonical_to_number(SUBSTR(paa_arch.serial_number, 3))
AND (INSTR(paa_arch.serial_number, 'PY') <> 0
or INSTR(paa_arch.serial_number, 'UY') <> 0)
AND paa_arch.source_action_id IS NOT NULL)
OR
(ppp.source_action_id IS NULL
AND SUBSTR(paa_arch.serial_number,0,2) in ('PN','UN','PY','UY') -- added for P1 14647358
AND ppp.assignment_action_id = fnd_number.canonical_to_number(SUBSTR(paa_arch.serial_number, 3))
AND (INSTR(paa_arch.serial_number, 'PN') <> 0
or INSTR(paa_arch.serial_number, 'UN') <> 0)
AND paa_arch.source_action_id IS NULL)
)
AND paa_arch.assignment_action_id = pai_arch.locking_action_id
AND ppa_arch.payroll_action_id = paa_arch.payroll_action_id
AND ppa_arch.action_type = 'X'
AND hrl.lookup_type = 'PAYSLIP_REPORT_TYPES'
AND hrl.lookup_code = cp_legislation_code
AND ppa_arch.report_type = hrl.meaning ;
SELECT paa_arch_ctx.assignment_action_id
FROM pay_action_interlocks pai_chk,
pay_action_interlocks pai_arch,
pay_assignment_actions paa_arch,
pay_assignment_actions paa_arch_ctx,
pay_assignment_actions paa_chk,
pay_pre_payments ppp,
pay_payroll_actions ppa_arch,
pay_payroll_actions ppa_chk,
hr_lookups hrl
WHERE pai_chk.locking_action_id = cp_chk_action_id
--Pre-Payment Assignment Action ID is locked by both Check Writer
--and Payroll Archive Process (It can be locked by NACHA as well)
AND pai_arch.locked_action_id = pai_chk.locked_action_id
AND paa_chk.assignment_action_id = pai_chk.locking_action_id
AND paa_chk.payroll_action_id = ppa_chk.payroll_action_id
AND pai_arch.locking_action_id = paa_arch_ctx.source_action_id
/*bug 13876466 for multiassignment case for third party cheque writer*/
AND (
paa_arch_ctx.tax_unit_id = paa_chk.tax_unit_id
OR paa_arch_ctx.assignment_id <> paa_chk.assignment_id
)
AND ppa_chk.action_type = 'H'
AND paa_chk.pre_payment_id = ppp.pre_payment_id
AND (
(
ppp.source_action_id IS NOT NULL
AND ppp.source_action_id = fnd_number.canonical_to_number (substr (paa_arch_ctx.serial_number, 3))
AND (
instr (paa_arch_ctx.serial_number, 'PY') <> 0
OR instr (paa_arch_ctx.serial_number, 'UY') <> 0
)
)
OR (
ppp.source_action_id IS NULL
/*AND ppp.assignment_action_id = fnd_number.canonical_to_number (substr (paa_arch_ctx.serial_number, 3)) bug 13876466*/
AND (
instr (paa_arch_ctx.serial_number, 'PN') <> 0
OR instr (paa_arch_ctx.serial_number, 'UN') <> 0
)
)
)
AND paa_arch.assignment_action_id = pai_arch.locking_action_id
AND ppa_arch.payroll_action_id = paa_arch.payroll_action_id
AND ppa_arch.action_type = 'X'
AND hrl.lookup_type = 'PAYSLIP_REPORT_TYPES'
AND hrl.lookup_code = cp_legislation_code
AND ppa_arch.report_type = hrl.meaning ;
SELECT paa_arch.assignment_action_id
FROM pay_action_interlocks pai_chk,
pay_action_interlocks pai_prepay,
pay_action_interlocks pai_arch,
pay_assignment_actions paa_arch,
pay_assignment_actions paa_prepay,
pay_payroll_actions ppa_arch,
pay_payroll_actions ppa_prepay,
hr_lookups hrl
WHERE pai_chk.locking_action_id = cp_chk_action_id
AND pai_chk.locked_action_id = pai_prepay.locking_action_id
AND pai_prepay.locking_action_id = paa_prepay.assignment_action_id
AND paa_prepay.payroll_action_id = ppa_prepay.payroll_action_id
AND ppa_prepay.action_type IN ('P','U')
AND pai_prepay.locked_action_id = pai_arch.locked_action_id
AND paa_arch.assignment_action_id = pai_arch.locking_action_id
AND ppa_arch.payroll_action_id = paa_arch.payroll_action_id
AND hrl.lookup_type = 'PAYSLIP_REPORT_TYPES'
AND hrl.lookup_code = cp_legislation_code
AND ppa_arch.report_type = hrl.meaning;
SELECT pai_arch.locking_action_id
FROM pay_action_interlocks pai_chk,
pay_action_interlocks pai_arch,
pay_assignment_actions paa_arch,
pay_assignment_actions paa_chi_pp,
pay_assignment_actions paa_chk,
pay_pre_payments ppp,
pay_payroll_actions ppa_arch,
pay_payroll_actions ppa_chk,
hr_lookups hrl
WHERE pai_chk.locking_action_id = cp_chk_action_id
--Pre-Payment Assignment Action ID is locked by both Check Writer
--and Payroll Archive Process (It can be locked by NACHA as well)
-- AND pai_arch.locked_action_id = pai_chk.locked_action_id
AND paa_chk.assignment_action_id = pai_chk.locking_action_id
AND paa_chk.payroll_action_id = ppa_chk.payroll_action_id
AND ppa_chk.action_type = 'H'
AND paa_chk.pre_payment_id = ppp.pre_payment_id
AND (
(ppp.source_action_id IS NOT NULL
AND SUBSTR(paa_arch.serial_number,0,2) in ('PN','UN','PY','UY') -- added for P1 14647358
AND ppp.source_action_id = fnd_number.canonical_to_number(SUBSTR(paa_arch.serial_number, 3))
AND (INSTR(paa_arch.serial_number, 'PY') <> 0
or INSTR(paa_arch.serial_number, 'UY') <> 0)
AND paa_arch.source_action_id IS NOT NULL)
OR
(ppp.source_action_id IS NULL
AND SUBSTR(paa_arch.serial_number,0,2) in ('PN','UN','PY','UY') -- added for P1 14647358
AND ppp.prepayment_action_id = fnd_number.canonical_to_number(SUBSTR(paa_arch.serial_number, 3))
AND (INSTR(paa_arch.serial_number, 'PN') <> 0
or INSTR(paa_arch.serial_number, 'UN') <> 0)
AND paa_arch.source_action_id IS NULL)
)
AND paa_chi_pp.assignment_action_id = pai_chk.locked_action_id
AND paa_chi_pp.source_action_id = pai_arch.locked_action_id
AND paa_arch.assignment_action_id = pai_arch.locking_action_id
AND ppa_arch.payroll_action_id = paa_arch.payroll_action_id
AND ppa_arch.action_type = 'X'
AND hrl.lookup_type = 'PAYSLIP_REPORT_TYPES'
AND hrl.lookup_code = cp_legislation_code
AND ppa_arch.report_type = hrl.meaning ;
SELECT DISTINCT assignment_id
FROM pay_assignment_actions
WHERE assignment_action_id = cp_chk_asg_act_id;
SELECT ppa.effective_date
,ppa.payroll_action_id
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
WHERE paa.assignment_action_id = cp_chk_asg_act_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type = 'H';
SELECT ppf.first_name
, ppf.last_name
, ppf.order_name
, ppf.full_name
, ppf.national_identifier
, ppf.employee_number
, pj.name
, hou.name
, paf.payroll_id
, prl.payroll_name
, ppf.middle_names
, ppf.title
, paf.assignment_number
FROM per_all_assignments_f paf
, per_all_people_f ppf
, per_periods_of_service pps
, per_jobs pj
, hr_organization_units hou
, pay_payrolls_f prl
WHERE paf.person_id = ppf.person_id
and paf.assignment_id = p_assignment_id
AND paf.job_id = pj.job_id(+)
and paf.organization_id = hou.organization_id
and prl.payroll_id=paf.payroll_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
and p_effective_date between prl.effective_start_date
and prl.effective_end_date
and pps.person_id = ppf.person_id
and pps.date_start = (select max(pps1.date_start)
from per_periods_of_service pps1
where pps1.person_id = paf.person_id
and pps1.date_start <= p_effective_date);
SELECT nvl(paa.serial_number,'-9999')
,substr(fnd_date.date_to_canonical(ppa.effective_date),1,10)
,substr(nvl(fnd_date.date_to_canonical(ppa.overriding_dd_date),fnd_date.date_to_canonical(ppa.effective_date)),1,10)
,ppa.payroll_action_id
FROM pay_assignment_actions paa,pay_payroll_actions ppa
WHERE paa.assignment_action_id = cp_chk_asg_act_id
AND paa.payroll_action_id = ppa.payroll_action_id ;
SELECT pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_NAME', pea.territory_code),
pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_BRANCH', pea.territory_code),
pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_ACCOUNT_NAME', pea.territory_code),
pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_ACCOUNT_NUMBER', pea.territory_code),
pea.segment4,
pea.segment2
FROM pay_org_payment_methods_f popm
, pay_external_accounts pea
WHERE org_payment_method_id = p_org_payment_method_id
AND popm.external_account_id = pea.external_account_id
AND p_effective_date between popm.EFFECTIVE_START_DATE
and popm.EFFECTIVE_END_DATE;
SELECT pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_NAME', pea.territory_code),
pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_BRANCH', pea.territory_code),
pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_ACCOUNT_NAME', pea.territory_code),
pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_ACCOUNT_NUMBER', pea.territory_code),
pea.segment4,
pea.segment2
FROM pay_personal_payment_methods_f pppm
, pay_external_accounts pea
WHERE pppm.personal_payment_method_id = p_per_pay_method
AND pppm.external_account_id = pea.external_account_id
AND p_effective_date between pppm.EFFECTIVE_START_DATE
and pppm.EFFECTIVE_END_DATE;
SELECT ppp.payees_org_payment_method_id,
ppp.personal_payment_method_id,
ppp.org_payment_method_id,
-- added for 13029999
popm.defined_balance_id,
ppp.pre_payment_id
FROM pay_pre_payments ppp
, pay_action_interlocks pai
, pay_assignment_actions paa
, pay_assignment_actions paa_chq
, pay_payroll_actions ppa
, pay_payroll_actions ppa_chq
, pay_org_payment_methods_f popm
WHERE paa_chq.assignment_action_id = cp_chk_action_id
and paa_chq.assignment_action_id = pai.locking_action_id
and pai.locked_action_id = paa.assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppp.assignment_action_id = paa.assignment_action_id
and ppp.pre_payment_id = paa_chq.pre_payment_id
and popm.org_payment_method_id = ppp.org_payment_method_id
and ppa_chq.payment_type_id=popm.payment_type_id
and (ppa_chq.org_payment_method_id is NULL
or
ppa_chq.org_payment_method_id = ppp.org_payment_method_id)
and (ppa_chq.payroll_action_id = cp_chk_payroll_act_id)
and ppa_chq.effective_date between popm.effective_start_date and popm.effective_end_date;
UPDATE pay_assignment_actions
SET action_status = 'S'
WHERE assignment_action_id = ln_chq_asg_action_id;
hr_utility.trace('Update Successful..');
hr_utility.trace('Update Unsuccessful..');