[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
*** 28 Nov 01 nnaresh 1.1 Updated for GSCC Standards
*** 29 Nov 01 nnaresh 1.2 Replaced REM with ***
*** 03 DEC 02 Ragovind 1.7 Added NOCOPY for the function range_code.
*** 18 FEB 03 nanuradh 1.8 2786549 Removed number_of_copies parameter when setting
*** printer options
*** 25 FEB 03 nanuradh 1.9 2786549 Removed the fix done for the bug #2786549
*** 29 MAY 03 apunekar 1.10 2920725 Corrected base tables to support security model
*** 17 NOV 03 avenkatk 1.11 3132172 Added support for printing report on
*** duplex printers.
*** 10 FEB 04 punmehta 1.12 3098353 Added check for archive flag
*** 21 JUL 04 srrajago 1.13 3768288 Modified cursor 'csr_get_print_options' to fetch value 'number_of_copies'
*** and the same is passed to fnd_request.set_print_options.Resolved GSCC warning in
*** assigning value -1 to ps_request_id.
*** 09 AUG 04 abhkumar 1.14 2610141 Legal Employer Enhancement
*** 09 DEC 04 ksingla 1.15 3937976 Added check for archive flag X_CURR_TERM_0_BAL_FLAG
*** 06 DEC 05 avenkatk 1.16 4859876 Added support for XML Publisher PDF Template
*** 02 JAN 06 avenkatk 1.17 4891196 Added support for PDF and Postscript generation of report.
*** 03 Jan 06 abhargav 1.18 4726357 Added function to get the self serivce option.
*** 28_feb-07 abhargav 1.20 5743270 Added check so that empty self printed report will not be generated
*** for cases where for all the assignment Self Printed flag is set 'Yes'.
*** 09-Jan-08 avenkatk 115.21 6470581 Added Changes for Amended Payment Summary
*** 23-Jan-08 avenkatk 115.22 6470581 Resolved GSCC Errors
*** ------------------------------------------------------------------------+
*/
g_debug boolean; /* Bug 6470581 */
p_sql := ' select distinct p.person_id' ||
' from per_people_f p,' ||
' pay_payroll_actions pa' ||
' where pa.payroll_action_id = :payroll_action_id' ||
' and p.business_group_id = pa.business_group_id' ||
' order by p.person_id';
select distinct a.assignment_id,
pay_core_utils.get_parameter('ARCHIVE_ID', pa.legislative_parameters) archive_action_id,
ppac.assignment_action_id
from per_assignments_f a,
per_people_f p,
pay_payroll_actions pa,
pay_payroll_actions ppa,
pay_assignment_actions ppac
where pa.payroll_action_id = c_payroll_action_id
and p.person_id between c_start_person_id and c_end_person_id
and p.person_id = a.person_id
and p.business_group_id = pa.business_group_id
and ppa.payroll_action_id = ppac.payroll_action_id
and a.assignment_id = ppac.assignment_id
and ppa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ID', pa.legislative_parameters)
And ppa.action_type = 'X'
and ppa.action_status = 'C'
and pay_au_payment_summary.get_archive_value('X_REPORTING_FLAG', ppac.assignment_action_id)='YES' --3098353
and pay_au_payment_summary.get_archive_value('X_CURR_TERM_0_BAL_FLAG', ppac.assignment_action_id)='NO' --3937976
and not exists
(select locked_action_id
FROM pay_action_interlocks pail
WHERE pail.locked_action_id=ppac.assignment_action_id)
and ppac.assignment_action_id in
(select max(ppac1.assignment_action_id)
from pay_assignment_actions ppac1,
pay_payroll_Actions ppaa
where ppaa.action_type ='X'
and ppaa.action_status ='C'
and pay_core_utils.get_parameter('REGISTERED_EMPLOYER', ppaa.legislative_parameters) =
pay_core_utils.get_parameter('REGISTERED_EMPLOYER', pa.legislative_parameters) --2610141
and ppaa.report_type ='AU_PAYMENT_SUMMARY'
and ppaa.payroll_Action_id = ppac1.payroll_action_id
group by ppac1.assignment_id);
select pay_assignment_actions_s.nextval
from dual;
SELECT to_number(pay_core_utils.get_parameter('REGISTERED_EMPLOYER', ppa.legislative_parameters)) registered_employer
,pay_core_utils.get_parameter('ARCHIVE_ID', ppa.legislative_parameters) archive_id
,NVL(pay_core_utils.get_parameter('PAY_SUM_TYPE', ppa.legislative_parameters),'O') payment_summary_type
,pay_core_utils.get_parameter('FINANCIAL_YEAR', ppa.legislative_parameters) fin_year
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = c_payroll_action_id;
SELECT DISTINCT a.assignment_id
,pay_core_utils.get_parameter('ARCHIVE_ID', pa.legislative_parameters) archive_action_id
,ppac.assignment_action_id
,pmaa.assignment_action_id datafile_action_id
FROM per_assignments_f a
,per_people_f p
,pay_payroll_actions pa
,pay_payroll_actions ppa
,pay_assignment_actions ppac
,pay_assignment_actions pmaa
,pay_payroll_actions pmpa
WHERE pa.payroll_action_id = c_payroll_action_id
AND p.person_id between c_start_person_id and c_end_person_id
AND p.person_id = a.person_id
AND p.business_group_id = pa.business_group_id
AND ppa.payroll_action_id = ppac.payroll_action_id
AND a.assignment_id = ppac.assignment_id
AND ppa.payroll_action_id = c_archive_id
AND ppa.action_type = 'X'
AND ppa.action_status = 'C'
AND pay_au_payment_summary.get_archive_value('X_REPORTING_FLAG', ppac.assignment_action_id)='YES'
AND pay_au_payment_summary.get_archive_value('X_CURR_TERM_0_BAL_FLAG', ppac.assignment_action_id)='NO'
AND pay_au_payment_summary.get_archive_value('X_PAYMENT_SUMMARY_TYPE', ppac.assignment_action_id)='A' /* Indicates something has changed */
AND pmaa.assignment_id = ppac.assignment_id
AND pmaa.payroll_action_id = pmpa.payroll_action_id
AND pmpa.report_type = 'AU_PS_DATA_FILE'
AND pmpa.action_type = 'X'
AND pmpa.action_status = 'C'
AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER', pmpa.legislative_parameters) = c_reg_emp
AND pay_core_utils.get_parameter('FINANCIAL_YEAR', pmpa.legislative_parameters) = c_financial_year
AND NOT EXISTS
(SELECT locked_action_id
FROM pay_action_interlocks pail
WHERE pail.locked_action_id=ppac.assignment_action_id)
AND ppac.assignment_action_id IN
(SELECT MAX(ppac1.assignment_action_id)
FROM pay_assignment_actions ppac1,
pay_payroll_Actions ppaa
WHERE ppaa.action_type ='X'
AND ppaa.action_status ='C'
AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER', ppaa.legislative_parameters) =
pay_core_utils.get_parameter('REGISTERED_EMPLOYER', pa.legislative_parameters) --2610141
AND ppaa.report_type ='AU_PAY_SUMM_AMEND'
AND ppaa.payroll_Action_id = ppac1.payroll_action_id
GROUP BY ppac1.assignment_id);
hr_utility.set_location('inserted assigment action assignment '|| process_rec.assignment_id ,15);
select p.current_chunk_number
from pay_payroll_actions p
where payroll_action_id = p_payroll_action_id;
SELECT a.formula_id
FROM ff_formulas_f a,
ff_formula_types t
WHERE a.formula_name = p_formula_name
AND business_group_id IS NULL
AND legislation_code = 'AU'
AND a.formula_type_id = t.formula_type_id
AND t.formula_type_name = 'Oracle Payroll';
SELECT printer,
print_style,
decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output,
number_of_copies /* Bug: 3768288 */
FROM pay_payroll_actions pact,
fnd_concurrent_requests fcr
WHERE fcr.request_id = pact.request_id
AND pact.payroll_action_id = p_payroll_action_id;
select count(ppav.assignment_id)
from pay_au_eoy_values_v ppav,
pay_payroll_actions ppa,
pay_assignment_actions pac
where ppa.payroll_action_id= p_payroll_action_id
and ppav.payroll_action_id =pay_core_utils.get_parameter('ARCHIVE_ID',ppa.legislative_parameters)
and ppa.report_type='AU_PAYMENT_SUMMARY_REPORT'
and ppav.assignment_id=pac.assignment_id
and ppav.X_REPORTING_FLAG = 'YES'
and ppav.X_CURR_TERM_0_BAL_FLAG='NO'
and pac.payroll_action_id=p_payroll_action_id
and decode(pay_core_utils.get_parameter('SS_PREF',ppa.legislative_parameters),'N',ss_pref(pac.assignment_id),'N') ='N'
;
select paf.business_group_id, paf.location_id, paf.organization_id,paf.person_id
from per_assignments_f paf
where paf.assignment_id = p_assignemnt_id
and paf.effective_start_date =
(select max(effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
);
SELECT online_opt
FROM
(
Select PEI_INFORMATION2 online_opt, 1 sort_col
from PER_PEOPLE_EXTRA_INFO ppit
where ppit.person_id=c_person_id
and ppit.pei_information1= 'PAYMENTSUMMARY'
and ppit.information_type='HR_SELF_SERVICE_PER_PREFERENCE'
union
Select LEI_INFORMATION2 online_opt, 2 sort_col
FROM hr_location_extra_info hlei
WHERE hlei.location_id = c_loc_id
And hlei.lei_information1= 'PAYMENTSUMMARY'
AND hlei.information_type = 'HR_SELF_SERVICE_LOC_PREFERENCE'
UNION
SELECT org_information2 online_opt,
3 sort_col
FROM hr_organization_information hoi
WHERE hoi.organization_id = c_org_id
and hoi.org_information1 = 'PAYMENTSUMMARY'
AND hoi.org_information_context = 'HR_SELF_SERVICE_ORG_PREFERENCE'
UNION
SELECT org_information2 online_opt,
4 sort_col
FROM hr_organization_information hoi
WHERE hoi.organization_id = c_bg_id
And hoi.org_information1 = 'PAYMENTSUMMARY'
AND hoi.org_information_context = 'HR_SELF_SERVICE_BG_PREFERENCE'
)
WHERE online_opt IS NOT NULL
ORDER BY sort_col;