[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ppa.start_date,
ppa.effective_date,
ppa.business_group_id,
ppa.report_type
INTO p_year_start,
p_year_end,
p_business_group_id,
p_report_type
FROM pay_payroll_actions ppa
WHERE payroll_action_id = p_pactid;
SELECT
ppa.payroll_action_id
FROM
pay_payroll_actions ppa
WHERE
ppa.business_group_id = p_bg_id AND
ppa.report_type = 'CAEOY_RL1_AMEND_PP'
AND ppa.report_qualifier = 'CAEOY_RL1_AMEND_PPQ'
AND ppa.report_category = 'ARCHIVE'
AND ppa.effective_date = p_effective_date AND
p_transmitter_org_id =
pay_ca_rl1_mag.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters);
SELECT
ppa.legislative_parameters
FROM
pay_payroll_actions ppa
WHERE
ppa.payroll_action_id = p_payroll_action_id;
SELECT substr(l_transmitter_number,1,2)
INTO dummy1
FROM dual;
SELECT to_number(substr(l_transmitter_number,3,6))
INTO dummy
FROM dual;
SELECT 'RZ-'||substr(fdi.user_name,23,2) code,fai.value value
FROM ff_database_items fdi,
ff_archive_items fai
WHERE fai.user_entity_id = fdi.user_entity_id
AND fai.context1 = p_arch_assact_id
AND fdi.user_name like 'CAEOY_RL1_BOXO_AMOUNT____PER_JD_YTD'
ORDER BY substr(fdi.user_name,5,4);
select substr(ltrim(rtrim(code)),1,60) code, value value
from (
select 'B-1' code,to_char(p_cpp_withheld) value from dual
where p_cpp_withheld <> 0
union
select 'G-2' code,to_char(p_cpp_taxable) value from dual
where p_cpp_taxable <> 0
union
select decode(fdi.user_name,'CAEOY_RL1_FURTHER_INFO_AMOUNT_201_AMT_PER_JD_YTD','201',
'CAEOY_RL1_FURTHER_INFO_AMOUNT_211_AMT_PER_JD_YTD','211',
'CAEOY_RL1_FURTHER_INFO_AMOUNT_235_AMT_PER_JD_YTD','235',
substr(replace(substr(fdi.user_name,31),'_AMT_PER_JD_YTD',''),1,1)||'-'||
substr(replace(substr(fdi.user_name,31),'_AMT_PER_JD_YTD',''),2)) code,
fai.value value
from ff_database_items fdi,
ff_archive_items fai
where fai.user_entity_id=fdi.user_entity_id
and fai.context1= p_arch_assact_id
and fdi.user_name LIKE 'CAEOY_RL1_FURTHER_INFO_AMOUNT_%_AMT_PER_JD_YTD'
union
select 'G-1' code,
pai.action_information5 value
from pay_action_information pai
where pai.action_context_id = p_arch_assact_id
and pai.action_information4 = 'G-1'
) order by code;
g_further_info_list.delete;
p_sqlstr := 'select distinct to_number(fai1.value)
from ff_archive_items fai1,
ff_database_items fdi1,
ff_archive_items fai2,
ff_database_items fdi2,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_payroll_actions ppa1,
hr_organization_information hoi
where ppa1.payroll_action_id = :payroll_action_id
and ppa.business_group_id+0 = ppa1.business_group_id
and ppa.effective_date = ppa1.effective_date
and ppa.report_type = ''CAEOY_RL1_AMEND_PP''
and ppa.payroll_action_id = paa.payroll_action_id
and fdi2.user_name = ''CAEOY_TAXATION_YEAR''
and fai2.user_entity_id = fdi2.user_entity_id
and fai2.value = pay_ca_rl1_mag.get_parameter(''REPORTING_YEAR'',ppa1.legislative_parameters)
and paa.payroll_action_id= fai2.context1
and paa.action_status = ''C''
and paa.assignment_action_id = fai1.context1
and fai1.user_entity_id = fdi1.user_entity_id
and fdi1.user_name = ''CAEOY_PERSON_ID''
and decode(hoi.org_information3, ''Y'', hoi.organization_id, hoi.org_information20) =
pay_ca_rl1_mag.get_parameter(''TRANSMITTER_PRE'', ppa1.legislative_parameters )
and hoi.org_information_context =''Prov Reporting Est''
and hoi.organization_id = pay_ca_rl1_mag.get_parameter(''PRE_ORGANIZATION_ID'', ppa.legislative_parameters )
order by to_number(fai1.value)' ;
SELECT paf.person_id,
paf.assignment_id,
hoi.organization_id,
paf.effective_end_date,
max(paa.assignment_action_id),
max(ppa.payroll_action_id) -- Added by ssmukher for Bug 3353115
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_all_assignments_f paf,
pay_payroll_actions ppa1,
hr_organization_information hoi
WHERE ppa1.payroll_action_id = p_pactid
AND ppa.report_type ='CAEOY_RL1_AMEND_PP'
AND ppa.business_group_id+0 = ppa1.business_group_id
AND ppa.effective_date = ppa1.effective_date
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C'
AND paf.assignment_id = paa.assignment_id
AND paf.person_id BETWEEN p_stperson AND p_endperson
AND paf.effective_start_date <= ppa.effective_date
AND paf.effective_end_date >= ppa.start_date
AND decode(hoi.org_information3, 'Y', hoi.organization_id, hoi.org_information20) =
pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',ppa.legislative_parameters)
AND hoi.org_information_context = 'Prov Reporting Est'
AND hoi.organization_id = pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',ppa.legislative_parameters)
AND paf.effective_end_date = (select max(paf1.effective_end_date)
from per_all_assignments_f paf1
where paf1.assignment_id = paf.assignment_id
and paf1.effective_start_date <= ppa1.effective_date)
AND not exists
( SELECT pail.locked_action_id
FROM pay_action_interlocks pail,
pay_payroll_actions pact,
pay_assignment_actions passt
WHERE pact.report_type = 'RL1_AMEND_MAG'
AND pact.payroll_action_id = passt.payroll_action_id
AND passt.assignment_action_id = pail.locking_action_id
AND pail.locked_action_id = paa.assignment_action_id)
AND not exists
( SELECT pail.locked_action_id
FROM pay_action_interlocks pail,
pay_payroll_actions pact,
pay_assignment_actions passt
WHERE pact.report_type = 'RL1_XML_MAG'
AND pact.payroll_action_id = passt.payroll_action_id
AND passt.assignment_action_id = pail.locking_action_id
AND pail.locked_action_id = paa.assignment_action_id)
AND not exists
( SELECT pail.locked_action_id
FROM pay_action_interlocks pail,
pay_payroll_actions pact,
pay_assignment_actions passt
WHERE pact.report_type = 'CAEOY_RL1_AMEND_PP'
AND pact.payroll_action_id = passt.payroll_action_id
AND passt.assignment_action_id = pail.locking_action_id
AND pail.locked_action_id = paa.assignment_action_id)
GROUP BY
paf.person_id,
paf.assignment_id,
hoi.organization_id,
paf.effective_end_date;
select ppa.report_type,
paa.assignment_id,
paa.assignment_action_id,
pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters)
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f paf
where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
and paa.assignment_id = paf.assignment_id
/* and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters) =
nvl(cp_pre_org_id,pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters))*/
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date = cp_effective_date
and ppa.business_group_id = cp_business_group_id
and ppa.report_type IN ('CAEOY_RL1_AMEND_PP','RL1_XML_MAG','RL1_AMEND_MAG')
and exists (select 1
from per_assignments_f paf
where paf.assignment_id = paa.assignment_id
and paf.effective_start_date <= cp_effective_date
and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
AND not exists
( SELECT pail.locked_action_id
FROM pay_action_interlocks pail,
pay_payroll_actions pact,
pay_assignment_actions passt
WHERE pact.report_type = 'RL1_AMEND_MAG'
AND pact.payroll_action_id = passt.payroll_action_id
AND passt.assignment_action_id = pail.locking_action_id
AND passt.serial_number=to_char(cp_person_id)
AND (pail.locked_action_id = paa.assignment_action_id
OR paa.assignment_action_id < passt.assignment_action_id))
group by paa.assignment_action_id,ppa.report_type,paa.assignment_id,
pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters) --Bug 9133270
order by paa.assignment_action_id desc;
select fai2.value,faic.context
from ff_archive_items fai2,
ff_archive_item_contexts faic,
ff_contexts fc
where fai2.context1 = cp_asg_act_id
AND fai2.user_entity_id = cp_uid_rl1amend_flag
AND fai2.archive_item_id = faic.archive_item_id
AND faic.context = 'QC'
AND faic.context_id = fc.context_id
AND fc.context_name = 'JURISDICTION_CODE';
select substr(full_name,1,48), employee_number
from per_people_f
where person_id = cp_person_id
order by effective_end_date desc;
select user_entity_id
from ff_database_items
where user_name = cp_user_name;
select payroll_action_id
from pay_payroll_actions
where business_group_id = cp_bg_id
and report_type = 'RL1'
and report_qualifier = 'CAEOYRL1'
and action_type = 'X'
and action_status = 'C'
and effective_date = cp_year
and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
legislative_parameters)
= to_char(cp_pre);
CURSOR c_paa_update_check (cp_locking_asg_act_id number) IS
select assignment_action_id from
pay_assignment_actions where
assignment_action_id = cp_locking_asg_act_id;
l_paa_update_check pay_assignment_actions.assignment_action_id%TYPE;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
/* Insert into pay_assignment_actions. */
hr_nonrun_asact.insact(lockingactid
,ln_primary_assignment_id
,p_pactid
,p_chunk
,l_tax_unit_id);
** Update the serial number column with Province_code QC,
** Archiver assignment_action and Archiver Payroll_action_id
** so that we need not refer back in the reports.
***********************************************************/
update pay_assignment_actions aa
set aa.serial_number = to_char(l_person_id)
where aa.assignment_action_id = lockingactid;
open c_paa_update_check(lockingactid);
fetch c_paa_update_check into l_paa_update_check;
hr_utility.trace('l_update_check : '||l_paa_update_check);
if c_paa_update_check%FOUND then
/***********************************************************
** Update the serial number column with Province_code QC,
** Archiver assignment_action and Archiver Payroll_action_id
** so that we need not refer back in the reports.
***********************************************************/
update pay_assignment_actions aa
set aa.serial_number = to_char(l_person_id)
where aa.assignment_action_id = lockingactid;
end if;--c_paa_update_check%FOUND
close c_paa_update_check;
select fai.value
from ff_archive_items fai,
ff_database_items fdi
where fai.user_entity_id = fdi.user_entity_id
and fai.context1 = b_context_id
and fdi.user_name = b_user_name;
SELECT
fnd_global.local_chr(13) || fnd_global.local_chr(10)
INTO EOL
FROM dual;
SELECT 'RZ-'||substr(fdi.user_name,23,2) code,to_number(fai.value) value
FROM ff_database_items fdi,
ff_archive_items fai
WHERE fai.user_entity_id = fdi.user_entity_id
AND fai.context1 = p_arch_assact_id
AND fdi.user_name like 'CAEOY_RL1_BOXO_AMOUNT____PER_JD_YTD'
ORDER BY substr(fdi.user_name,5,4);
select substr(ltrim(rtrim(code)),1,60) code, value value
from (
select decode(fdi.user_name,'CAEOY_RL1_FURTHER_INFO_AMOUNT_201_AMT_PER_JD_YTD','201',
'CAEOY_RL1_FURTHER_INFO_AMOUNT_211_AMT_PER_JD_YTD','211',
'CAEOY_RL1_FURTHER_INFO_AMOUNT_235_AMT_PER_JD_YTD','235',
substr(replace(substr(fdi.user_name,31),'_AMT_PER_JD_YTD',''),1,1)||'-'||
substr(replace(substr(fdi.user_name,31),'_AMT_PER_JD_YTD',''),2)) code,
to_number(fai.value) value
from ff_database_items fdi,
ff_archive_items fai
where fai.user_entity_id=fdi.user_entity_id
and fai.context1= p_arch_assact_id
and fdi.user_name LIKE 'CAEOY_RL1_FURTHER_INFO_AMOUNT_%_AMT_PER_JD_YTD'
union
select 'G-1' code,
to_number(pai.action_information5) value
from pay_action_information pai
where pai.action_context_id = p_arch_assact_id
and pai.action_information4 = 'G-1'
) order by code;
SELECT
to_number(substr(paa.serial_number,3,14)) asgactid , --archiver assignment action id
to_number(substr(paa.serial_number,17,14)) payactid, --archiver payroll action id
paa.assignment_id asgid
FROM
pay_assignment_actions paa
WHERE paa.assignment_action_id = p_assg_actid;
select fai.value
from ff_archive_items fai,
ff_database_items fdi
where fai.user_entity_id = fdi.user_entity_id
and fdi.user_name = 'CAEOY_RL1_PROVINCE_OF_EMPLOYMENT'
and fai.context1 =p_arch_asact_id;
SELECT
pai.locked_action_id, -- Archiver asg_action_id
paa.assignment_id,
pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id),
-- date_earned
fai.value -- Jurisdiction
FROM
ff_archive_items fai,
ff_database_items fdi,
per_all_people_f ppf,
per_all_assignments_f paf,
pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_assignment_actions paa_arch
WHERE
paa.assignment_action_id = p_mag_asg_action_id AND
ppa.payroll_action_id = paa.payroll_action_id AND
pai.locking_action_id = paa.assignment_action_id AND
paf.assignment_id = paa.assignment_id AND
ppf.person_id = paf.person_id AND
pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
between
paf.effective_start_date and paf.effective_end_date AND
pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
between
ppf.effective_start_date and ppf.effective_end_date AND
fai.context1 = pai.locked_action_id AND
fdi.user_name = 'CAEOY_RL1_PROVINCE_OF_EMPLOYMENT' AND
fai.user_entity_id = fdi.user_entity_id AND
paa_arch.assignment_action_id = fai.context1 AND
--paa_arch.payroll_action_id =
-- to_number(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')) AND
paa_arch.assignment_action_id = pai.locked_action_id
ORDER BY
ppf.last_name,ppf.first_name,ppf.middle_names;
SELECT ppa.legislative_parameters
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_payroll_action_id;
SELECT pei_information7
FROM per_people_extra_info pei
WHERE pei_information6=to_char(p_trans_id)
AND substr(pei_information5,1,4)=p_tax_year
AND to_number(pei.person_id) = p_person
AND pei.information_type = 'PAY_CA_RL1_FORM_NO'
ORDER BY pei_information7; /*Bug 13564765,15972601*/
SELECT
meaning
FROM
hr_lookups
WHERE
lookup_type = 'PAY_CA_MAG_EXCEPTIONS' and
lookup_code = p_lookup_code;
SELECT meaning
FROM hr_lookups
WHERE trim(lookup_code) = p_reporting_year
AND lookup_type = 'PAY_CA_RL1_PDF_AUTH'
AND enabled_flag='Y';
SELECT ppa.report_type
INTO l_rep_type
from pay_payroll_actions ppa
where payroll_action_id=l_payroll_actid;
SELECT
fnd_global.local_chr(13) || fnd_global.local_chr(10)
INTO EOL
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_further_amount(l_i)),l_format_mask)))
INTO l_formatted_box
FROM dual;
select decode(l_further_code(l_i),'201','F_1','211','F_2','235','F_3',replace(l_further_code(l_i),'-','_'))
into l_formatted_code
from dual;
SELECT ltrim(rtrim(to_char(to_number(l_further_amount(l_i)),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT
fnd_global.local_chr(13) || fnd_global.local_chr(10)
INTO EOL
FROM dual;
select decode(correspondence_language,NULL,'US',correspondence_language)
from per_all_people_f
where person_id = to_number(pay_ca_archive_utils.get_archive_value(
p_arch_assact_id,
'CAEOY_PERSON_ID'));
SELECT 'BOXO-'||substr(fdi.user_name,23,2) DB_Name,to_number(fai.value) value
FROM ff_database_items fdi,
ff_archive_items fai
WHERE fai.user_entity_id = fdi.user_entity_id
AND fai.context1 = p_arch_assact_id
AND fdi.user_name like 'CAEOY_RL1_BOXO_AMOUNT_R__PER_JD_YTD'
and fai.value <> '0'
ORDER BY substr(fdi.user_name,5,4);
select substr(ltrim(rtrim(code)),1,60) code,to_char(value,'999,999.99') value
from (
select pay_ca_rl1_reg.get_label(lookup_type,lookup_code,p_person_lang) code, p_cpp_withheld value
from hr_lookups
where lookup_type = 'PAY_CA_RL1_FOOTNOTES'
and lookup_code = 'CPP'
and p_cpp_withheld <> 0
union
select SUBSTR(fdi.user_name,11,4)||', '||pay_ca_rl1_reg.get_label(hl.lookup_type,hl.lookup_code,p_person_lang) code,
to_number(fai.value) value
from HR_LOOKUPS HL,
ff_database_items fdi,
ff_archive_items fai
where fai.user_entity_id=fdi.user_entity_id
and fai.context1= p_arch_assactid
and fdi.user_name like 'CAEOY_RL1_BOX%_AMT_PER_JD_YTD'
and fai.value <> '0'
and hl.lookup_type = 'PAY_CA_RL1_FOOTNOTES'
AND HL.LOOKUP_CODE = SUBSTR(replace(FDI.USER_NAME,'_AMT_PER_JD_YTD'),-2)
union all
select pay_ca_rl1_reg.get_label(hl.lookup_type,hl.lookup_code,p_person_lang) code,
to_number(pai.action_information5) value
from pay_action_information pai
, hr_lookups hl
where pai.action_context_id = p_arch_assactid
and hl.lookup_type = 'PAY_CA_RL1_NONBOX_FOOTNOTES'
and hl.lookup_code = pai.action_information4
);
SELECT
fnd_global.local_chr(13) || fnd_global.local_chr(10)
INTO EOL
from dual;
SELECT to_number(substr(paa.serial_number,17,14)) payactid --archiver payroll action id
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = p_assact_id;
SELECT
fnd_global.local_chr(13) || fnd_global.local_chr(10)
INTO EOL
FROM dual;
SELECT report_type
FROM pay_payroll_actions
WHERE payroll_action_id = p_pactid;