[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* INSERT INTO tmp
* VALUES (p_xfdf_string);*/
SELECT DISTINCT payroll_name
FROM pay_payrolls_f
WHERE payroll_id = p_payroll_id;
SELECT period_name
FROM per_time_periods
WHERE time_period_id = p_period_id;
SELECT consolidation_set_name
FROM pay_consolidation_sets
WHERE consolidation_set_id = p_consolidation_set_id;
SELECT dummy_break,
payment_method_type,
payment_method_type || ' Totals' payment_method_type_totals,
NAME,
source_sort_code,
bank_name,
bank_branch,
account_number,
account_name,
amount,
total_assignments_paid,
SUM (amount) OVER (PARTITION BY payment_method_type)
AS amount_per_type,
SUM (amount) OVER (PARTITION BY dummy_break)
AS total_amount_paid,
SUM (total_assignments_paid) OVER (PARTITION BY
payment_method_type)
AS asg_paid_per_type,
SUM (total_assignments_paid) OVER (PARTITION BY dummy_break)
AS total_asg_paid
FROM (SELECT 'X' dummy_break,
SUBSTR (ppttl.payment_type_name, 1, 14)
payment_method_type,
popmftl.org_payment_method_name NAME,
SUBSTR (pea.segment1, 1, 6) source_sort_code,
hr_general.decode_lookup ('HR_IE_BANK',
pea.segment2) bank_name,
SUBSTR (pea.segment3, 1, 35) bank_branch,
SUBSTR (pea.segment4, 1, 8) account_number,
SUBSTR (pea.segment5, 1, 18) account_name,
SUM (TO_NUMBER (ppp.VALUE)) amount,
COUNT (ppp.VALUE) total_assignments_paid
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_pre_payments ppp,
pay_org_payment_methods_f_tl popmftl,
pay_org_payment_methods_f popmf,
pay_payment_types_tl ppttl,
pay_payment_types ppt,
pay_external_accounts pea,
per_time_periods ptp
WHERE ppt.payment_type_id = ppttl.payment_type_id
AND ppttl.LANGUAGE = USERENV ('LANG')
AND popmf.org_payment_method_id =
popmftl.org_payment_method_id
AND popmftl.LANGUAGE = USERENV ('LANG')
AND ppa.payroll_action_id = paa.payroll_action_id
AND ( p_consolidation_set_id IS NULL
OR ppa.consolidation_set_id =
p_consolidation_set_id
)
AND ppa.action_type IN ('U', 'P')
AND ppa.action_status = 'C'
AND ppa.payroll_id = p_payroll_id
AND ptp.payroll_id = ppa.payroll_id -- Bug 5070091 Offset payroll Change
-- Commented for Time Period Change
--AND ptp.time_period_id = p_period_id
/* AND ppa.effective_date BETWEEN ptp.start_date
AND ptp.regular_payment_date
*/
AND ppa.effective_date BETWEEN popmf.effective_start_date
AND popmf.effective_end_date
AND paa.assignment_action_id = ppp.assignment_action_id
AND ppp.org_payment_method_id =
popmf.org_payment_method_id
AND popmf.payment_type_id = ppt.payment_type_id
AND popmf.external_account_id = pea.external_account_id
AND exists ( SELECT NULL -- Bug 5070091 Offset payroll Change
FROM pay_assignment_actions paa_run,
pay_action_interlocks pai_run,
pay_payroll_actions ppa_run
WHERE ppa_run.payroll_id = p_payroll_id
AND ptp.time_period_id = p_period_id
AND ppa_run.date_earned between ptp.start_date and ptp.end_date
AND ppa_run.action_type in ('R','Q')
AND ppa_run.payroll_action_id = paa_run.payroll_action_id
AND paa_run.assignment_action_id = pai_run.locked_action_id
AND pai_run.locking_action_id = paa.assignment_action_id
)
GROUP BY ppttl.payment_type_name,
popmftl.org_payment_method_name,
pea.segment1,
pea.segment2,
pea.segment3,
pea.segment4,
pea.segment5,
ppa.consolidation_set_id,
ppa.effective_date,
ptp.start_date,
ptp.end_date,
popmf.effective_start_date,
popmf.effective_end_date);
SELECT '*** Warning: Not all payroll runs have been paid in this
payroll period ***'
text_not_all_payroll_runs_paid
FROM DUAL
WHERE EXISTS ( SELECT NULL
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp -- Bug 5070091 Offset payroll change
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_status = 'C'
AND ppa.action_type IN ('Q', 'R')
AND ppa.payroll_id = p_payroll_id
AND ptp.payroll_id = ppa.payroll_id -- Bug 5070091 Offset payroll change
AND ppa.date_earned between ptp.start_date and ptp.end_date
--AND ppa.time_period_id = p_period_id
AND NOT EXISTS (
SELECT 1
FROM pay_action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
WHERE pai.locked_action_id = paa.assignment_action_id
AND pai.locking_action_id = paa1.assignment_action_id
AND paa1.payroll_action_id = ppa1.payroll_action_id
AND ppa1.action_type IN ('U', 'P')
AND ppa1.action_status = 'C'
)
);
SELECT '*** Warning: These Amount totals include payments from previous
payroll period(s) ***'
payments_from_previous_periods
FROM /* per_time_periods ptp, */
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_pre_payments ppp
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND ( p_consolidation_set_id IS NULL
OR ppa.consolidation_set_id = p_consolidation_set_id
)
AND ppa.action_status = 'C'
AND ppa.payroll_id = p_payroll_id
--AND ptp.time_period_id = p_period_id
--AND ppa.effective_date BETWEEN ptp.start_date AND ptp.end_date
AND ppa.action_type IN ('U', 'P')
AND ppp.assignment_action_id = paa.assignment_action_id
AND EXISTS (
SELECT 1
FROM pay_action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1,
per_time_periods ptp1
WHERE pai.locking_action_id = paa.assignment_action_id
AND pai.locked_action_id = paa1.assignment_action_id
AND ppa1.payroll_action_id = paa1.payroll_action_id
AND ppa1.action_type IN ('Q', 'R')
AND ppa1.action_status = 'C'
AND ppa1.payroll_id = ptp1.payroll_id --Bug 5070091 Offset payroll change
AND ppa1.date_earned between ptp1.start_date and ptp1.end_date
AND ptp1.time_period_id <> p_period_id)
--AND ppa1.time_period_id <> ptp.time_period_id);
SELECT 1
FROM pay_action_interlocks pai2,
pay_assignment_actions paa2,
pay_payroll_actions ppa2,
per_time_periods ptp2
WHERE pai2.locking_action_id = paa.assignment_action_id
AND pai2.locked_action_id = paa2.assignment_action_id
AND ppa2.payroll_action_id = paa2.payroll_action_id
AND ppa2.action_type IN ('Q', 'R')
AND ppa2.action_status = 'C'
AND ppa2.payroll_id = ptp2.payroll_id
AND ppa2.date_earned between ptp2.start_date and ptp2.end_date
AND ptp2.time_period_id = p_period_id
);
SELECT fnd_date.date_to_displaydate (SYSDATE)
INTO l_report_date
FROM DUAL;
vxmltable.DELETE;