[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT legislative_parameters
INTO l_legislative_parameters
FROM pay_payroll_actions
WHERE payroll_action_id = p_pactid;
SELECT user_entity_id
INTO l_tax_year_ue_id
FROM ff_database_items
WHERE user_name='CAEOY_TAXATION_YEAR';
SELECT user_entity_id
INTO l_person_id_ue_id
FROM ff_database_items
WHERE user_name='CAEOY_PERSON_ID';
p_sqlstr := 'SELECT DISTINCT to_number(fai1.value)
FROM ff_archive_items fai1,
ff_archive_items fai2,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_payroll_actions ppa1
WHERE ppa1.payroll_action_id = :p_pactid
AND ppa.report_type = ''CAEOY_T4_AMEND_PP''
AND ppa.report_qualifier = ''CAEOY_T4_AMEND_PPQ''
AND ppa.report_category = ''ARCHIVE''
AND ppa.action_type = ''X''
AND ppa.action_status = ''C''
AND ppa.business_group_id = 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 fai2.user_entity_id = '|| l_tax_year_ue_id ||
' AND fai2.context1 = paa.payroll_action_id
AND fai2.value = '|| l_tax_year ||
' AND fai1.context1 = paa.assignment_action_id
AND fai1.user_entity_id = '||l_person_id_ue_id||
' ORDER BY to_number(fai1.value)';
SELECT paf.person_id,
paf.assignment_id,
paa.tax_unit_id,
paf.effective_end_date,
MAX(paa.assignment_action_id)
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_all_assignments_f paf,
pay_payroll_actions ppa1,
hr_organization_information hoi1
WHERE ppa1.payroll_action_id = p_pactid
AND ppa.report_type = 'CAEOY_T4_AMEND_PP'
AND ppa.report_qualifier = 'CAEOY_T4_AMEND_PPQ'
AND ppa.report_category = 'ARCHIVE'
AND ppa.action_type = 'X'
AND ppa.action_status = 'C'
AND ppa.business_group_id = 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 hoi1.org_information_context= 'Canada Employer Identification'
AND hoi1.org_information11 = l_trans_gre
AND paa.tax_unit_id = hoi1.organization_id
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 paf.effective_end_date = (SELECT MAX(paf2.effective_end_date)
FROM per_all_assignments_f paf2
WHERE paf2.assignment_id = paf.assignment_id
AND paf2.effective_start_date <= ppa.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 IN ('T4_AMEND_MAG','PYT4MAG','CAEOY_T4_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,
paa.tax_unit_id,
paf.effective_end_date
ORDER BY paf.person_id,
paf.assignment_id;
SELECT
ppa.report_type,
paa.assignment_action_id
FROM
pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE ppa.report_type IN ( 'T4_AMEND_MAG' , 'PYT4MAG' )
AND ppa.action_type = 'X'
AND ppa.action_status = 'C'
AND ppa.business_group_id = cp_business_group_id
AND ppa.effective_date = cp_effective_date
AND paa.assignment_id = cp_assignment_id
AND paa.tax_unit_id = cp_tax_unit_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C'
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 IN ( 'T4_AMEND_MAG' , 'PYT4MAG' , 'CAEOY_T4_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 )
ORDER BY
paa.assignment_action_id;
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 value
FROM ff_archive_items
WHERE context1 = cp_asg_act_id
AND user_entity_id = cp_uid_t4amend_flag;
SELECT user_entity_id
FROM ff_database_items
WHERE user_name = cp_user_name;
SELECT assignment_action_id
FROM pay_assignment_actions
WHERE assignment_action_id = cp_locking_asg_act_id;
SELECT pay_assignment_actions_s.nextval
INTO lockingactid
FROM dual;
UPDATE pay_assignment_actions aa
SET aa.serial_number = to_char(l_person_id)
WHERE aa.assignment_action_id = lockingactid;
SELECT ppa.payroll_action_id,ppa.business_group_id
FROM hr_organization_information hoi,
pay_payroll_actions ppa
WHERE hoi.organization_id = to_number(c_trans_id)
AND hoi.org_information_context='Fed Magnetic Reporting'
AND ppa.report_type = 'CAEOY_T4_AMEND_PP' -- T4 Archiver Report Type
AND hoi.organization_id = substr(ppa.legislative_parameters,
instr(ppa.legislative_parameters,'TRANSFER_GRE=')
+LENGTH('TRANSFER_GRE='),
(instr(ppa.legislative_parameters,' ',
instr(ppa.legislative_parameters,'TRANSFER_GRE=')
+LENGTH('TRANSFER_GRE=')))
-(instr(ppa.legislative_parameters,'TRANSFER_GRE=')
+LENGTH('TRANSFER_GRE=')))
AND ppa.effective_date = to_date('31-12-'||c_year,'DD-MM-YYYY');
SELECT DISTINCT ppa.payroll_action_id, hoi.organization_id, hou.name
FROM pay_payroll_actions ppa,
hr_organization_information hoi,
hr_all_organization_units hou
WHERE hoi.org_information_context = 'Canada Employer Identification'
AND hoi.org_information11 = p_trans
AND hou.business_group_id = p_bg_id
AND hou.organization_id = hoi.organization_id
AND ppa.report_type = 'CAEOY_T4_AMEND_PP'
AND ppa.effective_date = to_date('31-12-'||p_year,'DD-MM-YYYY')
AND ppa.business_group_id = p_bg_id
AND hoi.organization_id = substr(ppa.legislative_parameters,
instr(ppa.legislative_parameters,'TRANSFER_GRE=')
+LENGTH('TRANSFER_GRE='),
(instr(ppa.legislative_parameters, ' ',
instr(ppa.legislative_parameters,'TRANSFER_GRE=')
+LENGTH('TRANSFER_GRE=')))
-(instr(ppa.legislative_parameters,'TRANSFER_GRE=')
+LENGTH('TRANSFER_GRE=')));
SELECT hou.name
FROM hr_all_organization_units hou
WHERE hou.organization_id = to_number(b_org_id);
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 ppa.legislative_parameters,
ppa.effective_date,
ppa.business_group_id,
ppa.report_type
INTO p_legislative_parameters,
p_year_end,
p_business_group_id,
p_report_type
FROM pay_payroll_actions ppa
WHERE payroll_action_id = p_pactid;
SELECT
fnd_global.local_chr(13) || fnd_global.local_chr(10)
INTO EOL
FROM dual;
SELECT to_char(to_number(p_data), p_format)
INTO l_data FROM dual;
SELECT to_char(to_number(p_data), '99999999999999999999999999999999999990.99')
INTO l_data FROM dual;
SELECT pai.action_information4 ppreg_no
FROM pay_action_information pai,pay_payroll_actions ppa
WHERE pai.action_context_id = cp_pact_id
AND pai.tax_unit_id = cp_tax_unit_id
AND ppa.payroll_action_id = pai.action_context_id
AND pai.effective_date = ppa.effective_date
AND pai.action_information_category = 'CAEOY PENSION PLAN INFO'
ORDER BY 1 DESC;
SELECT
paa.assignment_action_id,
paa.payroll_action_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_action_interlocks pai
WHERE pai.locking_action_id = p_assg_actid
AND pai.locked_action_id = paa.assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.report_type = 'CAEOY_T4_AMEND_PP'
ORDER BY
paa.assignment_action_id DESC;
SELECT
serial_number
FROM
pay_assignment_actions
WHERE assignment_action_id = p_mag_asg_action_id;
SELECT
count(1)
FROM
ff_archive_items fai,
ff_database_items fdi
WHERE fai.context1 = p_aa_id
AND fai.user_entity_id = fdi.user_entity_id
AND fdi.user_name='CAEOY_PROVINCE_OF_EMPLOYMENT';
SELECT
pcli.information_value CPP_MAX,
pcli1.information_value EI_MAX,
pcli2.information_value PPIP_MAX
FROM
pay_payroll_actions ppa,
pay_ca_legislation_info pcli,
pay_ca_legislation_info pcli1,
pay_ca_legislation_info pcli2
WHERE ppa.payroll_action_id= p_payroll_actid
AND pcli.information_type = 'MAX_CPP_EARNINGS'
AND ppa.effective_date BETWEEN pcli.start_date AND pcli.end_date
AND pcli.jurisdiction_code IS NULL
AND pcli1.information_type = 'MAX_EI_EARNINGS'
AND pcli1.jurisdiction_code IS NULL
AND ppa.effective_date BETWEEN pcli1.start_date AND pcli1.end_date
AND pcli2.information_type = 'MAX_PPIP_EARNINGS'
AND pcli2.jurisdiction_code IS NULL
AND ppa.effective_date BETWEEN pcli2.start_date AND pcli2.end_date;
SELECT
fac.context
FROM
ff_archive_items fai,
ff_database_items fdi,
ff_archive_item_contexts fac,
ff_contexts ffc
WHERE fai.context1 = p_arch_action_id
AND fai.archive_item_id = fac.archive_item_id
AND fai.user_entity_id = fdi.user_entity_id
AND fdi.user_name = 'CAEOY_T4_AMENDMENT_FLAG'
AND fac.context_id = ffc.context_id
AND ffc.context_name = 'JURISDICTION_CODE'
AND fai.value='Y';
tab_employee.DELETE;