[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT decode(FT.FURTHER_INFO_CODE,
'RL2_FURTHER_INFO_AMOUNT_201','201',
'RL2_FURTHER_INFO_AMOUNT_235','235',
'RL2_FURTHER_INFO_AMOUNT_210','210', /* 14701748 */
substr(substr(FT.FURTHER_INFO_CODE,25),1,1)||'-'||substr(substr(FT.FURTHER_INFO_CODE,25),2)) code,
FT.FURTHER_INFO_VALUE value
FROM PAY_CA_EOY_RL2_FURTHER_INFO_V FT
WHERE FT.ASSIGNMENT_ACTION_ID = p_assg_actid
ORDER BY code;
SELECT ppa.start_date,
ppa.effective_date,
ppa.business_group_id,
ppa.report_type,
ppa.legislative_parameters
INTO p_year_start,
p_year_end,
p_business_group_id,
p_report_type,
p_legislative_param
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 = 'RL2' AND
ppa.report_qualifier = 'CAEOYRL2' AND
ppa.report_category = 'ARCHIVE' AND
ppa.effective_date = p_effective_date AND
p_transmitter_org_id =
pay_ca_rl2_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;
p_sqlstr := 'select /*+ leading(ppa_mag,ppa_arch,paa_arch)
index(emp.paa_arch,PAY_ASSIGNMENT_ACTIONS_PK)
use_hash(emp.ppa_arch,hoi,tran.ppa_arch)
*/ distinct to_number(emp.person_id)
from pay_ca_eoy_rl2_employee_info_v emp,
pay_ca_eoy_rl2_trans_info_v tran,
pay_assignment_actions paa_arch,
pay_payroll_actions ppa_arch,
pay_payroll_actions ppa_mag,
hr_organization_information hoi
where ppa_mag.payroll_action_id = :payroll_action_id
and ppa_arch.business_group_id+0 = ppa_mag.business_group_id
and ppa_arch.effective_date = ppa_mag.effective_date
and ppa_arch.report_type = ''RL2''
and ppa_arch.payroll_action_id = paa_arch.payroll_action_id
and tran.reporting_year = to_char(ppa_arch.effective_date,''YYYY'')
and tran.business_group_id = ppa_arch.business_group_id
and tran.reporting_year = pay_ca_rl2_mag.get_parameter(''REPORTING_YEAR'',ppa_mag.legislative_parameters)
and paa_arch.payroll_action_id = tran.payroll_action_id
and paa_arch.action_status = ''C''
and paa_arch.assignment_action_id = emp.assignment_action_id
and paa_arch.payroll_action_id = emp.payroll_action_id
and emp.business_group_id = ppa_arch.business_group_id
and decode(hoi.org_information3, ''Y'', hoi.organization_id, hoi.org_information20) =
pycadar_pkg.get_parameter(''TRANSMITTER_PRE'', ppa_mag.legislative_parameters )
and hoi.org_information_context =''Prov Reporting Est''
and to_char(hoi.organization_id) =
pycadar_pkg.get_parameter(''PRE_ORGANIZATION_ID'',ppa_arch.legislative_parameters)
order by to_number(emp.person_id)' ;
SELECT paf.person_id,
paf.assignment_id,
paa_arch.tax_unit_id,
paf.effective_end_date,
paa_arch.assignment_action_id,
ppa_arch.payroll_action_id
FROM pay_payroll_actions ppa_arch,
pay_assignment_actions paa_arch,
per_all_assignments_f paf,
hr_organization_information hoi
WHERE ppa_arch.report_type = 'RL2'
AND ppa_arch.business_group_id+0 = p_business_grpid
AND ppa_arch.effective_date = p_effective_dt
AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
AND paa_arch.action_status = 'C'
AND paf.assignment_id = paa_arch.assignment_id
AND paf.person_id BETWEEN p_stperson AND p_endperson
AND paf.effective_start_date <= ppa_arch.effective_date
AND paf.effective_end_date >= ppa_arch.start_date
AND decode(hoi.org_information3, 'Y', hoi.organization_id, hoi.org_information20) =
substr(p_leg_param, instr(p_leg_param,'TRANSMITTER_PRE=')+16)
AND hoi.org_information_context = 'Prov Reporting Est'
AND hoi.organization_id =
substr(ppa_arch.legislative_parameters,
instr(ppa_arch.legislative_parameters,'PRE_ORGANIZATION_ID=')+20)
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 <= p_effective_dt);
SELECT pay_assignment_actions_s.nextval
INTO lockingactid
FROM dual;
/* Update the serial number column with the person id
*/
-- hr_utility.trace('updating asg. action');
update pay_assignment_actions aa
set aa.serial_number = to_char(l_person_id)
where aa.assignment_action_id = lockingactid;
SELECT nvl(transmitter_number,' '),
nvl(reporting_year,'0000'),
nvl(transmitter_package_type,'0'),
nvl(transmitter_type_indicator,'0'),
nvl(transmitter_name,' '),
nvl(source_of_slips,' '),
nvl(transmitter_address_line1,' '),
nvl(transmitter_address_line2,' '),
nvl(transmitter_city,' '),
nvl(transmitter_province,' '),
nvl(transmitter_postal_code,' '),
nvl(transmitter_tech_contact_name,' '),
nvl(transmitter_tech_contact_code,'000'),
nvl(transmitter_tech_contact_phone,'0000000'),
nvl(transmitter_tech_contact_extn,'0000'),
nvl(transmitter_tech_contact_lang,' '),
nvl(transmitter_acct_contact_name,' '),
nvl(transmitter_acct_contact_code,'000'),
nvl(transmitter_acct_contact_phone,'0000000'),
nvl(transmitter_acct_contact_extn,'0000'),
nvl(transmitter_acct_contact_lang,' ')
FROM pay_ca_eoy_rl2_trans_info_v
WHERE business_group_id = p_business_group_id
AND payroll_action_id = p_pact_id;
SELECT nvl(employer_name,' '),
nvl(quebec_business_number,'0000000000 0000'),
nvl(reporting_year,'0000'),
nvl(employer_add_line1,' '),
nvl(employer_add_line2,' '),
nvl(employer_city,' '),
nvl(employer_province,' '),
nvl(employer_postal_code,' ')
FROM pay_ca_eoy_rl2_trans_info_v
WHERE business_group_id = p_business_group_id
AND payroll_action_id = p_pact_id;
SELECT nvl(transmitter_number,' '),
nvl(reporting_year,'0000'),
nvl(transmitter_package_type,'0'),
nvl(transmitter_type_indicator,'0'),
nvl(transmitter_name,' '),
nvl(source_of_slips,' '),
nvl(transmitter_address_line1,' '),
nvl(transmitter_address_line2,' '),
nvl(transmitter_city,' '),
nvl(transmitter_province,' '),
nvl(transmitter_postal_code,' '),
nvl(transmitter_tech_contact_name,' '),
nvl(transmitter_tech_contact_code,'000'),
nvl(transmitter_tech_contact_phone,'0000000'),
nvl(transmitter_tech_contact_extn,'0000'),
nvl(transmitter_tech_contact_lang,' '),
nvl(transmitter_acct_contact_name,' '),
nvl(transmitter_acct_contact_code,'000'),
nvl(transmitter_acct_contact_phone,'0000000'),
nvl(transmitter_acct_contact_extn,'0000'),
nvl(transmitter_acct_contact_lang,' ')
FROM pay_ca_eoy_rl2_trans_info_v
WHERE business_group_id = p_business_group_id
AND payroll_action_id = p_pact_id;
SELECT
fnd_global.local_chr(13) || fnd_global.local_chr(10)
INTO EOL
FROM dual;
select paa.payroll_action_id, paa.assignment_id
from pay_assignment_actions paa
where paa.assignment_action_id = p_arch_asact_id;
select meaning
from hr_lookups
where
lookup_type = 'PAY_CA_MAG_EXCEPTIONS' and
lookup_code = p_lookup_code;
SELECT nvl(emp.negative_balance_flag, 'N')
FROM pay_ca_eoy_rl2_employee_info_v emp,
pay_ca_eoy_rl2_trans_info_v tran
WHERE emp.assignment_action_id = p_asg_action_id
AND emp.payroll_action_id = tran.payroll_action_id;
SELECT nvl(emp.negative_balance_flag, 'N')
FROM pay_ca_eoy_rl2_employee_info_v emp,
pay_ca_eoy_rl2_emp_info2_v emp2,
pay_ca_eoy_rl2_trans_info_v tran
WHERE emp.assignment_action_id = p_asg_action_id
AND emp.assignment_action_id = emp2.assignment_action_id
AND emp.payroll_action_id = emp2.payroll_action_id
AND emp.assignment_id = emp2.assignment_id
AND emp.person_id = emp2.person_id
AND emp.business_group_id = emp2.business_group_id
AND emp.payroll_action_id = tran.payroll_action_id;
SELECT
ppa.report_type,
ppa.business_group_id,
ppa.legislative_parameters
FROM
pay_payroll_actions ppa
WHERE
ppa.payroll_action_id = p_payactid;
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
FROM
pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_all_people_f ppf,
per_all_assignments_f paf,
pay_action_information pact
WHERE paa.assignment_action_id = pai.locking_action_id
AND paa.assignment_action_id = p_mag_asg_action_id
AND paf.assignment_id = paa.assignment_id
AND ppf.person_id = paf.person_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND pai.locked_action_id = pact.action_context_id
AND pact.action_information_category = 'CAEOY RL2 EMPLOYEE INFO'
AND pact.assignment_id = paa.assignment_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
ORDER BY
ppf.last_name,ppf.first_name,ppf.middle_names;
SELECT
to_number(substr(paa.serial_number,3,14)) asgactid,
--to_number(substr(paa.serial_number,17,14)) payactid,
paa_arch.payroll_action_id payactid, -- bug 10324391
paa.assignment_id asgid
FROM
pay_assignment_actions paa,
pay_assignment_actions paa_arch -- archiver payroll action_id
WHERE paa.assignment_action_id = p_payactid
AND paa_arch.assignment_action_id = to_number(substr(paa.serial_number,3,14));
select ppa.payroll_action_id,
ppa.business_group_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id = p_arch_action_id
and paa.payroll_action_id = ppa.payroll_action_id;
SELECT nvl(employer_name,' '),
nvl(quebec_business_number,'0000000000 0000'),
nvl(reporting_year,'0000'),
nvl(employer_add_line1,' '),
nvl(employer_add_line2,' '),
nvl(employer_add_line3,' '),
nvl(employer_city,' '),
nvl(employer_province,' '),
nvl(employer_country,' '),
nvl(employer_postal_code,' ')
FROM
pay_ca_eoy_rl2_trans_info_v
WHERE
business_group_id = p_business_group_id
AND payroll_action_id = p_pact_id;
SELECT nvl(tran.quebec_business_number,'0000000000 0000'),
nvl(tran.reporting_year,'0000'),
nvl(emp.rl2_slip_number,'000000000'),
nvl(emp.employee_sin,'000000000'),
nvl(emp.employee_last_name,' '),
nvl(emp.employee_first_name,' '),
nvl(emp.employee_middle_initial,' '),
nvl(emp.employee_address_line1,' '),
nvl(emp.employee_address_line2,' '),
nvl(emp.employee_address_line3,' '),
nvl(emp.employee_city,' '),
nvl(emp.employee_province,' '),
nvl(emp.employee_postal_code,' '),
nvl(emp.employee_number,' '),
emp.rl2_box_a,
emp.rl2_box_b,
emp.rl2_box_c,
emp.rl2_box_d,
emp.rl2_box_e,
emp.rl2_box_f,
emp.rl2_box_g,
emp.rl2_box_h,
emp.rl2_box_i,
emp.rl2_box_j,
emp.rl2_box_k,
emp.rl2_box_l,
emp.rl2_box_m,
emp.rl2_box_n,
emp.rl2_box_o,
decode(substr(emp.rl2_source_of_income,1,5),'OTHER','AUTRE', emp.rl2_source_of_income),
nvl(emp.negative_balance_flag, 'N'),
emp.person_id
FROM pay_ca_eoy_rl2_employee_info_v emp,
pay_ca_eoy_rl2_trans_info_v tran
WHERE emp.assignment_action_id = p_asg_action_id
AND emp.payroll_action_id = tran.payroll_action_id;
SELECT nvl(tran.quebec_business_number,'0000000000 0000'),
nvl(tran.reporting_year,'0000'),
nvl(emp.rl2_slip_number,'000000000'),
nvl(emp2.rl2_form_number,'000000000'),
nvl(emp.employee_sin,'000000000'),
nvl(emp.employee_last_name,' '),
nvl(emp.employee_first_name,' '),
nvl(emp.employee_middle_initial,' '),
nvl(emp.employee_address_line1,' '),
nvl(emp.employee_address_line2,' '),
nvl(emp.employee_address_line3,' '),
nvl(emp.employee_city,' '),
nvl(emp.employee_province,' '),
nvl(emp.employee_postal_code,' '),
nvl(emp.employee_number,' '),
emp.rl2_box_a,
emp.rl2_box_b,
emp.rl2_box_c,
emp.rl2_box_d,
emp.rl2_box_e,
emp.rl2_box_f,
emp.rl2_box_g,
emp.rl2_box_h,
emp.rl2_box_i,
emp.rl2_box_j,
emp.rl2_box_k,
emp.rl2_box_l,
emp.rl2_box_m,
emp.rl2_box_n,
emp.rl2_box_o,
decode(substr(emp.rl2_source_of_income,1,5),'OTHER','AUTRE', emp.rl2_source_of_income),
nvl(emp.negative_balance_flag, 'N'),
emp.person_id
FROM pay_ca_eoy_rl2_employee_info_v emp,
pay_ca_eoy_rl2_emp_info2_v emp2,
pay_ca_eoy_rl2_trans_info_v tran
WHERE emp.assignment_action_id = p_asg_action_id
AND emp.assignment_action_id = emp2.assignment_action_id
AND emp.payroll_action_id = emp2.payroll_action_id
AND emp.assignment_id = emp2.assignment_id
AND emp.person_id = emp2.person_id
AND emp.business_group_id = emp2.business_group_id
AND emp.payroll_action_id = tran.payroll_action_id;
select
'X'
from
hr_lookups hl
where
hl.lookup_type = 'PAY_CA_RL2_SOURCE_OF_INCOME'
and trim(hl.lookup_code) = trim(p_rl2_source_of_income)
and to_date('31/12/'||p_taxation_year,'dd/mm/yyyy')<= nvl(hl.end_date_active,to_date('31/12/4712','dd/mm/yyyy'))
and hl.enabled_flag='Y';
SELECT
meaning
FROM
hr_lookups
WHERE
lookup_type = 'PAY_CA_MAG_EXCEPTIONS' and
lookup_code = p_lookup_code;
SELECT hl.meaning code, fnd_number.canonical_to_number(FT.FOOTNOTE_AMOUNT) value
FROM PAY_CA_EOY_RL2_FOOTNOTE_INFO_V FT,
HR_LOOKUPS HL
WHERE FT.ASSIGNMENT_ACTION_ID = p_assg_actid
AND ((HL.LOOKUP_TYPE = 'PAY_CA_RL2_FOOTNOTES'
AND HL.lookup_code = FT.FOOTNOTE_CODE)
OR
(HL.LOOKUP_TYPE = 'PAY_CA_RL2_AUTOMATIC_FOOTNOTES'
AND HL.LOOKUP_CODE = FT.FOOTNOTE_CODE));
SELECT meaning
FROM hr_lookups
WHERE trim(lookup_code) = p_reporting_year
AND lookup_type = 'PAY_CA_RL2_PDF_AUTH'
AND enabled_flag='Y';
SELECT ACTION_INFORMATION1
FROM pay_action_information
WHERE action_context_id = arch_action_id
and ACTION_INFORMATION_CATEGORY = 'CAEOY RL2 EMPLOYEE INFO2';
SELECT
fnd_global.local_chr(13) || fnd_global.local_chr(10)
INTO EOL
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_a),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_b),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_c),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_d),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_e),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_f),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_g),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_h),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_i),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_j),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_k),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_l),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_m),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_o),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(l_further_amount(l_i),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(l_further_amount(l_i),l_format_mask)))
INTO l_formatted_box
FROM dual;
select decode(l_further_code(l_i),'235','F_2','210','F_3',replace(l_further_code(l_i),'-','_')) -- bug 14701748
into l_formatted_code
from dual;
SELECT ltrim(rtrim(to_char(l_further_amount(l_i),l_format_mask)))
INTO l_formatted_box
FROM dual;
SELECT ltrim(rtrim(to_char(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
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(convert(p_input_string,'UTF8'),
utl_raw.cast_to_varchar2(hextoraw('C380')),'A'
),
utl_raw.cast_to_varchar2(hextoraw('C38A')),'E'
),
utl_raw.cast_to_varchar2(hextoraw('C387')),'C'
),
utl_raw.cast_to_varchar2(hextoraw('C389')),'E'
),
utl_raw.cast_to_varchar2(hextoraw('C39C')),'U'
),
utl_raw.cast_to_varchar2(hextoraw('C399')),'U'
),
utl_raw.cast_to_varchar2(hextoraw('C39B')),'U'
),
utl_raw.cast_to_varchar2(hextoraw('C394')),'O'
),
utl_raw.cast_to_varchar2(hextoraw('C38F')),'I'
),
utl_raw.cast_to_varchar2(hextoraw('C38E')),'I'
),
utl_raw.cast_to_varchar2(hextoraw('C388')),'E'
),
utl_raw.cast_to_varchar2(hextoraw('C38B')),'E'
),
utl_raw.cast_to_varchar2(hextoraw('C382')),'A'
),
utl_raw.cast_to_varchar2(hextoraw('C592')),'OE'
),
utl_raw.cast_to_varchar2(hextoraw('C386')),'AE'
),
utl_raw.cast_to_varchar2(hextoraw('C3A9')),'e'
)
from dual;
SELECT nvl(tran.quebec_business_number,'0000000000 0000'),
nvl(tran.reporting_year,'0000'),
nvl(emp.rl2_slip_number,'000000000'),
nvl(emp.employee_sin,'000000000'),
nvl(emp.employee_last_name,' '),
nvl(emp.employee_first_name,' '),
nvl(emp.employee_middle_initial,' '),
nvl(emp.employee_address_line1,' '),
nvl(emp.employee_address_line2,' '),
nvl(emp.employee_address_line3,' '),
nvl(emp.employee_city,' '),
nvl(emp.employee_province,' '),
nvl(emp.employee_postal_code,' '),
nvl(emp.employee_number,' '),
emp.rl2_box_a,
emp.rl2_box_b,
emp.rl2_box_c,
emp.rl2_box_d,
emp.rl2_box_e,
emp.rl2_box_f,
emp.rl2_box_g,
emp.rl2_box_h,
emp.rl2_box_i,
emp.rl2_box_j,
emp.rl2_box_k,
emp.rl2_box_l,
emp.rl2_box_m,
emp.rl2_box_n,
emp.rl2_box_o,
decode(substr(emp.rl2_source_of_income,1,5),'OTHER','AUTRE', emp.rl2_source_of_income),
nvl(emp.negative_balance_flag, 'N'),
emp.person_id
FROM pay_ca_eoy_rl2_employee_info_v emp,
pay_ca_eoy_rl2_trans_info_v tran
WHERE emp.assignment_action_id = p_asg_action_id
AND emp.assignment_id = p_assignment_id
AND emp.payroll_action_id = tran.payroll_action_id;
SELECT report_type
FROM pay_payroll_actions
WHERE payroll_action_id = p_pactid;
pay_ca_payroll_utils.delete_actionid(p_pactid);