[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pay_fi_archive_umfr.get_parameter (
legislative_parameters,
'ARCHIVE'
),
TO_NUMBER (
pay_fi_archive_umfr.get_parameter (
legislative_parameters,
'TRADE_UNION_ID'
)
)
trade,
TO_NUMBER (
pay_fi_archive_umfr.get_parameter (
legislative_parameters,
'LEGAL_EMPLOYER_ID'
)
)
legal,
TO_NUMBER (
pay_fi_archive_umfr.get_parameter (
legislative_parameters,
'LOCAL_UNIT_ID'
)
)
LOCAL,
pay_fi_archive_umfr.get_parameter (
legislative_parameters,
'PERIOD'
)
period,
fnd_date.canonical_to_date (
pay_fi_archive_umfr.get_parameter (
legislative_parameters,
'PERIOD_END_DATE'
)
)
period_end_date,
effective_date effective_date, business_group_id bg_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT hou.NAME, hoi.org_information1, hoi.org_information5
FROM hr_organization_information hoi, hr_organization_units hou
WHERE org_information_context = 'FI_TRADE_UNION_DETAILS'
AND hou.organization_id = csr_v_trade_union_id
AND hoi.organization_id = hou.organization_id;
SELECT hou.NAME, hoi.org_information1, hoi.org_information8
FROM hr_organization_information hoi, hr_organization_units hou
WHERE org_information_context = 'FI_LEGAL_EMPLOYER_DETAILS'
AND hoi.organization_id = hou.organization_id
AND hou.organization_id = csr_v_legal_employer_id;
SELECT hoi_le.org_information1 local_unit_id,
hou_lu.NAME local_unit_name,
hoi_lu.org_information1 y_spare_number,
hoi_lu.org_information2 local_unit_number
FROM hr_organization_units hou_le,
hr_organization_information hoi_le,
hr_organization_units hou_lu,
hr_organization_information hoi_lu
WHERE hoi_le.organization_id = hou_le.organization_id
AND hou_le.organization_id = csr_v_legal_employer_id
AND hoi_le.org_information_context = 'FI_LOCAL_UNITS'
AND hou_lu.organization_id = hoi_le.org_information1
AND hou_lu.organization_id = hoi_lu.organization_id
AND hoi_lu.org_information_context = 'FI_LOCAL_UNIT_DETAILS';
SELECT hou.NAME, hoi.org_information1 y_spare_number,
hoi.org_information2 local_unit_number
FROM hr_organization_information hoi, hr_organization_units hou
WHERE org_information_context = 'FI_LOCAL_UNIT_DETAILS'
AND hoi.organization_id = hou.organization_id
AND hou.organization_id = csr_v_local_unit_id;
'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 = ppf.business_group_id
ORDER BY ppf.person_id';
'select 1 from dual where to_char(:payroll_action_id) = dummy';
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
,hr_soft_coding_keyflex hsck
,pay_run_result_values TARGET
,pay_run_results RR
,pay_element_entries_f PEEF
,pay_element_types_f PETF
, pay_input_values_f PIV
, per_all_people_f pap
WHERE ppa.payroll_action_id = p_payroll_action_id
AND appa.effective_date BETWEEN l_period_start_date
AND l_period_end_date
AND as1.person_id BETWEEN p_start_person
AND p_end_person
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 -- Master Action
AND as1.assignment_id = act.assignment_id
AND as1.person_id = pap.person_id
AND pap.per_information9 =
TO_CHAR (p_trade_union_id)
AND ppa.effective_date BETWEEN as1.effective_start_date
AND as1.effective_end_date
AND act.action_status IN ('C','S') -- 10229501
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status IN ('C','S') -- 10229501
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U')
AND appa2.effective_date BETWEEN l_period_start_date
AND l_period_end_date
-- Prepayments or Quickpay Prepayments
AND ( p_local_unit_id IS NULL
OR ( p_local_unit_id IS NOT NULL
AND hsck.segment2 = TO_CHAR (p_local_unit_id)
)
)
AND hsck.SOFT_CODING_KEYFLEX_ID=as1.SOFT_CODING_KEYFLEX_ID
-- AND hsck.segment2 = p_local_unit_id
AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
AND act.TAX_UNIT_ID = p_legal_employer_id
and TARGET.run_result_id = RR.run_result_id
AND (( RR.assignment_action_id
in ( Select act2.assignment_action_id
from pay_assignment_actions act2
Where act2.source_action_id=act.assignment_action_id
AND act2.action_status = 'C' -- Completed
AND act2.payroll_action_id = act.payroll_action_id))
or
(RR.assignment_action_id=act.assignment_action_id))
and RR.status in ('P','PA')
and PEEF.element_entry_id = RR.element_entry_id
and PEEF.element_type_id = RR.element_type_id
and PEEF.element_type_id = PETF.element_type_id
and PETF.legislation_code ='FI'
and PETF.element_name = 'Trade Union Membership Fees'
and PIV.element_type_id = PETF.element_type_id
and PIV.input_value_id = TARGET.input_value_id
and PIV.name='Third Party Payee'
and TARGET.result_value = to_char(p_trade_union_id)
and act.assignment_id IN
(SELECT MIN(act.assignment_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
,hr_soft_coding_keyflex hsck
,pay_run_result_values TARGET
,pay_run_results RR
,pay_element_entries_f PEEF
,pay_element_types_f PETF
, pay_input_values_f PIV
, per_all_people_f pap
WHERE ppa.payroll_action_id = p_payroll_action_id
AND appa.effective_date BETWEEN l_period_start_date
AND l_period_end_date
AND as1.person_id BETWEEN p_start_person
AND p_end_person
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 -- Master Action
AND as1.assignment_id = act.assignment_id
AND as1.person_id = pap.person_id
AND pap.per_information9 =
TO_CHAR (p_trade_union_id)
AND ppa.effective_date BETWEEN as1.effective_start_date
AND as1.effective_end_date
AND act.action_status IN ('C','S') -- 10229501
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status IN ('C','S') -- 10229501
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U')
AND appa2.effective_date BETWEEN l_period_start_date
AND l_period_end_date
-- Prepayments or Quickpay Prepayments
AND hsck.SOFT_CODING_KEYFLEX_ID=as1.SOFT_CODING_KEYFLEX_ID
AND ( p_local_unit_id IS NULL
OR ( p_local_unit_id IS NOT NULL
AND hsck.segment2 = TO_CHAR (p_local_unit_id)
)
)
-- AND hsck.segment2 = p_local_unit_id
AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
AND act.TAX_UNIT_ID = p_legal_employer_id
and TARGET.run_result_id = RR.run_result_id
AND (( RR.assignment_action_id
in ( Select act2.assignment_action_id
from pay_assignment_actions act2
Where act2.source_action_id=act.assignment_action_id
AND act2.action_status = 'C' -- Completed
AND act2.payroll_action_id = act.payroll_action_id))
or
(RR.assignment_action_id=act.assignment_action_id))
and RR.status in ('P','PA')
and PEEF.element_entry_id = RR.element_entry_id
and PEEF.element_type_id = RR.element_type_id
and PEEF.element_type_id = PETF.element_type_id
and PETF.legislation_code ='FI'
and PETF.element_name = 'Trade Union Membership Fees'
and PIV.element_type_id = PETF.element_type_id
and PIV.input_value_id = TARGET.input_value_id
and PIV.name='Third Party Payee'
and TARGET.result_value = to_char(p_trade_union_id)
GROUP BY as1.person_id
)
and (act.assignment_id ,act.assignment_action_id ) IN
(SELECT act.assignment_id , max(act.assignment_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
,hr_soft_coding_keyflex hsck
,pay_run_result_values TARGET
,pay_run_results RR
,pay_element_entries_f PEEF
,pay_element_types_f PETF
, pay_input_values_f PIV
, per_all_people_f pap
WHERE ppa.payroll_action_id = p_payroll_action_id
AND appa.effective_date BETWEEN l_period_start_date
AND l_period_end_date
AND as1.person_id BETWEEN p_start_person
AND p_end_person
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 -- Master Action
AND as1.assignment_id = act.assignment_id
AND as1.person_id = pap.person_id
AND pap.per_information9 =
TO_CHAR (p_trade_union_id)
AND ppa.effective_date BETWEEN as1.effective_start_date
AND as1.effective_end_date
AND act.action_status IN ('C','S') -- 10229501
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status IN ('C','S') -- 10229501
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U')
AND appa2.effective_date BETWEEN l_period_start_date
AND l_period_end_date
AND hsck.SOFT_CODING_KEYFLEX_ID=as1.SOFT_CODING_KEYFLEX_ID
AND ( p_local_unit_id IS NULL
OR ( p_local_unit_id IS NOT NULL
AND hsck.segment2 = TO_CHAR (p_local_unit_id)
)
)
AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
AND act.TAX_UNIT_ID = p_legal_employer_id
and TARGET.run_result_id = RR.run_result_id
AND (( RR.assignment_action_id
in ( Select act2.assignment_action_id
from pay_assignment_actions act2
Where act2.source_action_id=act.assignment_action_id
AND act2.action_status = 'C' -- Completed
AND act2.payroll_action_id = act.payroll_action_id))
or
(RR.assignment_action_id=act.assignment_action_id))
and RR.status in ('P','PA')
and PEEF.element_entry_id = RR.element_entry_id
and PEEF.element_type_id = RR.element_type_id
and PEEF.element_type_id = PETF.element_type_id
and PETF.legislation_code ='FI'
and PETF.element_name = 'Trade Union Membership Fees'
and PIV.element_type_id = PETF.element_type_id
and PIV.input_value_id = TARGET.input_value_id
and PIV.name='Third Party Payee'
and TARGET.result_value = to_char(p_trade_union_id)
GROUP BY act.assignment_id
)
ORDER BY act.assignment_id;
SELECT DECODE (
g_period,
'MONTH', TRUNC (g_period_end_date, 'MM'),
'BIMONTH', TRUNC (
ADD_MONTHS (
g_period_end_date,
MOD (
TO_NUMBER (
TO_CHAR (g_period_end_date, 'MM')
),
2
)
- 1
),
'MM'
),
'BIWEEK', g_period_end_date - 14,
'QUARTER', TRUNC (g_period_end_date, 'Q')
)
INTO g_period_start_date
FROM DUAL;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM DUAL;
SELECT DISTINCT prepay_payact.payroll_action_id prepay_payact_id,
run_payact.date_earned date_earned
FROM pay_action_interlocks archive_intlck,
pay_assignment_actions prepay_assact,
pay_payroll_actions prepay_payact,
pay_action_interlocks prepay_intlck,
pay_assignment_actions run_assact,
pay_payroll_actions run_payact,
pay_assignment_actions archive_assact
WHERE archive_intlck.locking_action_id =
archive_assact.assignment_action_id
AND archive_assact.payroll_action_id =
p_payroll_action_id
AND prepay_assact.assignment_action_id =
archive_intlck.locked_action_id
AND prepay_payact.payroll_action_id =
prepay_assact.payroll_action_id
AND prepay_payact.action_type IN ('U', 'P')
AND prepay_intlck.locking_action_id =
prepay_assact.assignment_action_id
AND run_assact.assignment_action_id =
prepay_intlck.locked_action_id
AND run_payact.payroll_action_id =
run_assact.payroll_action_id
AND run_payact.action_type IN ('Q', 'R')
ORDER BY prepay_payact.payroll_action_id;
SELECT DISTINCT prepay_payact.payroll_action_id prepay_payact_id,
run_payact.date_earned date_earned,
run_payact.payroll_action_id run_payact_id
FROM pay_action_interlocks archive_intlck,
pay_assignment_actions prepay_assact,
pay_payroll_actions prepay_payact,
pay_action_interlocks prepay_intlck,
pay_assignment_actions run_assact,
pay_payroll_actions run_payact,
pay_assignment_actions archive_assact
WHERE archive_intlck.locking_action_id =
archive_assact.assignment_action_id
AND archive_assact.payroll_action_id =
p_payroll_action_id
AND prepay_assact.assignment_action_id =
archive_intlck.locked_action_id
AND prepay_payact.payroll_action_id =
prepay_assact.payroll_action_id
AND prepay_payact.action_type IN ('U', 'P')
AND prepay_intlck.locking_action_id =
prepay_assact.assignment_action_id
AND run_assact.assignment_action_id =
prepay_intlck.locked_action_id
AND run_payact.payroll_action_id =
run_assact.payroll_action_id
AND run_payact.action_type IN ('Q', 'R')
ORDER BY prepay_payact.payroll_action_id;
SELECT territory_short_name
FROM fnd_territories_vl
WHERE territory_code = p_territory_code;
SELECT u.creator_id
FROM ff_user_entities u, ff_database_items d
WHERE d.user_name = p_user_name
AND u.user_entity_id = d.user_entity_id
AND (u.legislation_code = 'FI')
AND (u.business_group_id IS NULL)
AND u.creator_type = 'B';
SELECT prepay_assact.assignment_action_id prepay_assact_id,
prepay_assact.assignment_id prepay_assgt_id,
prepay_payact.payroll_action_id prepay_payact_id,
prepay_payact.effective_date prepay_effective_date,
run_assact.assignment_id run_assgt_id,
run_assact.assignment_action_id run_assact_id,
run_payact.payroll_action_id run_payact_id,
run_payact.payroll_id payroll_id
FROM pay_action_interlocks archive_intlck,
pay_assignment_actions prepay_assact,
pay_payroll_actions prepay_payact,
pay_action_interlocks prepay_intlck,
pay_assignment_actions run_assact,
pay_payroll_actions run_payact
WHERE archive_intlck.locking_action_id = p_locking_action_id
AND prepay_assact.assignment_action_id =
archive_intlck.locked_action_id
AND prepay_payact.payroll_action_id =
prepay_assact.payroll_action_id
AND prepay_payact.action_type IN ('U', 'P')
AND prepay_intlck.locking_action_id =
prepay_assact.assignment_action_id
AND run_assact.assignment_action_id =
prepay_intlck.locked_action_id
AND run_payact.payroll_action_id = run_assact.payroll_action_id
AND run_payact.action_type IN ('Q', 'R')
ORDER BY prepay_intlck.locking_action_id,
prepay_intlck.locked_action_id DESC;
SELECT ptp.end_date end_date,
ptp.regular_payment_date regular_payment_date,
ptp.time_period_id time_period_id,
ppa.date_earned date_earned,
ppa.effective_date effective_date, ptp.start_date start_date
FROM per_time_periods ptp,
pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE ptp.payroll_id = ppa.payroll_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id = p_assact_id
AND ppa.payroll_action_id = p_pay_act_id
AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date;
SELECT payroll_action_id
FROM pay_assignment_actions
WHERE assignment_action_id = p_assignment_action_id;
SELECT pap.LAST_NAME || ' ' || pap.FIRST_NAME NAME, pap.national_identifier,
paa.assignment_id assignment_id,
pap.per_information18 membership_start_date,
pap.per_information19 membership_end_date
FROM per_all_people_f pap,
per_all_assignments_f paa,
hr_soft_coding_keyflex scl,
pay_assignment_actions pasa
WHERE paa.person_id = pap.person_id
AND pasa.assignment_id = paa.assignment_id
AND scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
AND pap.effective_start_date <= g_period_end_date
AND pap.effective_end_date >= g_period_start_date
AND paa.effective_start_date <= g_period_end_date
AND paa.effective_end_date >= g_period_start_date
AND pap.business_group_id = csr_v_business_group_id
AND scl.segment2 = csr_v_local_unit_id
AND pasa.assignment_action_id = p_assignment_action_id;
SELECT ue.creator_id
FROM ff_user_entities ue, ff_database_items di
WHERE di.user_name = csr_v_balance_name
AND ue.user_entity_id = di.user_entity_id
AND ue.legislation_code = 'FI'
AND ue.business_group_id IS NULL
AND ue.creator_type = 'B';
SELECT scl.segment2
FROM per_all_assignments_f paa,
hr_soft_coding_keyflex scl,
pay_assignment_actions pasa
WHERE pasa.assignment_action_id = p_assignment_action_id
AND pasa.assignment_id = paa.assignment_id
AND scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id;
SELECT 1
INTO l_flag
FROM pay_action_information
WHERE action_information_category = 'EMEA REPORT INFORMATION'
AND action_information1 = 'PYFIUMFR'
AND action_information2 = 'PER'
AND action_context_id = p_assignment_action_id;