[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT upper(SUBSTR(trim(addr.address_line1),1,35)) addr1,
upper(SUBSTR(trim(addr.address_line2),1,35)) addr2,
upper(SUBSTR(trim(addr.address_line3),1,35)) addr3,
SUBSTR(addr.postal_code,1,10) post_code,
upper(SUBSTR(trim(addr.town_or_city),1,35)) addr4,
upper(SUBSTR(trim(addr.country),1,35)) country
FROM per_addresses addr
WHERE addr.person_id = p_person_id
AND ( addr.primary_flag = 'Y'
OR addr.primary_flag IS NULL)
AND p_effective_date
BETWEEN NVL(addr.date_from,fnd_date.canonical_to_date('0001/01/01 00:00:00'))
AND NVL(addr.date_to, fnd_date.canonical_to_date('4712/12/31 00:00:00'));
select hdei.document_number from hr_document_types hdt,hr_document_extra_info hdei
where hdt.category_code = 'PPT_INFO'
and hdt.document_type_id = hdei.document_type_id
and hdei.person_id = p_person_id;
SELECT l_column_name
FROM per_all_people_f
WHERE person_id = p_person_id
AND ATTRIBUTE_CATEGORY = l_context_name
AND p_effective_date between effective_start_date and effective_end_date;
SELECT l_column_name
FROM per_people_extra_info
WHERE person_id = p_person_id
AND PEI_INFORMATION_CATEGORY = l_context_name;
select max(effective_start_date) from per_all_people_f
where person_id = p_person_id
and effective_start_date <= p_effective_date;
sqlstr := 'select ' || l_column_name ||
' from per_people_extra_info where person_id = :p_person_id and PEI_INFORMATION_CATEGORY = :l_context_name';
sqlstr := 'select ' || l_column_name ||
' from per_all_people_f where person_id = :p_person_id and ATTRIBUTE_CATEGORY = :l_context_name' ||
' and :l_effective_date between effective_start_date and effective_end_date' ;
SELECT definition
INTO l_package
FROM ff_functions
WHERE name = l_function_name
and business_group_id = p_bg_id;
'SELECT '
|| l_package
|| ' (:p_person_id,:p_eff_date) '
|| 'FROM DUAL';
SELECT rownum Number_of_employee,
pap.person_id p_person_id,
paa.assignment_id,
SUBSTR(trim(pap.last_name), 1,35) last_name,
SUBSTR(trim(pap.first_name), 1,least(DECODE(instr(trim(pap.first_name),' '),0,35,instr(trim(pap.first_name),' ')),35)) first_name,
SUBSTR(trim(pap.middle_names), 1,least(decode(instr(trim(pap.middle_names),' '),0,35,instr(trim(pap.middle_names),' ')),35)) middle_name,
pap.title title,
SUBSTR(pap.national_identifier,1,9) national_identifier,
pap.date_of_birth date_of_birth,
SUBSTR(pap.sex,1,1) sex ,
DECODE(pap.per_information10,'Y','Y',NULL) agg_paye_flag,
DECODE(pap.per_information9,'Y','Y',NULL) multiple_asg_flag,
pap.business_group_id business_group_id
FROM pay_assignment_actions act,
per_all_assignments_f paa,
per_all_people_f pap
WHERE act.assignment_action_id = p_assactid
AND act.assignment_id = paa.assignment_id
AND paa.person_id = pap.person_id
AND p_effective_date between pap.effective_start_date and pap.effective_end_date
AND paa.effective_start_date =
(
SELECT MAX(paa2.effective_start_date)
FROM per_all_assignments_f paa2
WHERE paa2.assignment_id = paa.assignment_id
AND paa2.assignment_type = 'E'
AND paa2.effective_start_date <= p_effective_date
);
SELECT NVL(partner_name,''),
partner_ni_number
FROM ssp_medicals
WHERE maternity_id IN
(SELECT MAX(maternity_id)
FROM per_absence_attendances
WHERE person_id = c_person_id
AND ABSENCE_ATTENDANCE_TYPE_ID IN
(SELECT ABSENCE_ATTENDANCE_TYPE_ID
FROM per_absence_attendance_types
WHERE ABSENCE_CATEGORY IN ('GB_ADDL_PAT_ADOPT', 'GB_ADDL_PAT_BIRTH')
)
)
AND EVIDENCE_STATUS = 'CURRENT';
SELECT assignment_extra_info_id,
aei_information1 starter_decl,
aei_information2 prev_emp_paye_ref,
aei_information3 prev_tax_code,
aei_information4 date_left_prev_employer,
aei_information5 prev_tax_basis,
aei_information6 last_payment_period_type,
aei_information7 last_payment_period,
aei_information8 starter_flag,
aei_information10 continue_with_student_loan,
aei_information8 not_paid_between,
object_version_number object_version_number
FROM per_assignment_extra_info
WHERE assignment_id = p_asg_id
AND information_type = 'GB_RTI_ASG_DETAILS';
SELECT assignment_extra_info_id,
aei_information9 pensioner_flag,
aei_information12 date_pension_started,
trim(aei_information13) annual_pension,
aei_information14 recently_bereaved,
object_version_number object_version_number
FROM per_assignment_extra_info
WHERE assignment_id = p_asg_id
AND information_type = 'GB_RTI_ASG_DETAILS';
SELECT assignment_extra_info_id,
aei_information15 expat_statement,
aei_information16 date_emp_start_uk,
aei_information17 eea_cw_citizen,
aei_information18 epm6_scheme,
aei_information19 expat_flag,
object_version_number object_version_number
FROM per_assignment_extra_info
WHERE assignment_id = p_asg_id
AND information_type = 'GB_RTI_ASG_DETAILS';
select assignment_number from per_all_assignments_f where assignment_id = p_person_rec.assignment_id
and p_effective_date between effective_start_date and effective_end_date;
SELECT aei_information3 rti_payroll_id
FROM per_assignment_extra_info
WHERE assignment_id = p_person_rec.assignment_id
AND aei_information_category = 'GB_RTI_AGGREGATION';
select serv.actual_termination_date actual_termination_date
from
per_all_assignments_f asg,
per_periods_of_service serv
where asg.assignment_id = p_person_rec.assignment_id
and asg.period_of_service_id = serv.period_of_service_id
and p_effective_date between asg.effective_start_date and asg.effective_end_date
and actual_termination_date <= greatest(p_eyu_effective_date,p_effective_date);
SELECT min(paaf.effective_start_date) min_active_start_date
FROM per_all_assignments_f paaf,
per_assignment_status_types past
WHERE paaf.assignment_id = p_asg_id
AND paaf.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
SELECT max(prr.RUN_RESULT_ID)
FROM pay_run_results prr
WHERE prr.ASSIGNMENT_ACTION_ID = p_assignment_action_id
AND prr.ELEMENT_TYPE_ID = p_element_id
AND prr.status in ('P','PA');
SELECT max(decode(name,'Tax Code',result_value,NULL)) tax_code,
max(decode(name,'Tax Basis',result_value,NULL)) tax_basis
FROM pay_input_values_f v,
pay_run_result_values rrv
WHERE rrv.run_result_id = p_run_result_id
AND v.input_value_id = rrv.input_value_id
AND v.element_type_id = p_element_type_id;
SELECT max(decode(iv.name,'Tax Code',screen_entry_value)) tax_code,
max(decode(iv.name,'Tax Basis',DECODE(screen_entry_value,'N','Y',NULL),NULL)) tax_basis -- kvinayku bug no 14774165
FROM pay_element_entries_f e,
pay_element_entry_values_f v,
pay_input_values_f iv,
pay_element_links_f link
WHERE e.assignment_id = p_assignment_id
AND link.element_type_id = g_paye_details_id
AND e.element_link_id = link.element_link_id
AND e.element_entry_id = v.element_entry_id
AND iv.input_value_id = v.input_value_id
AND e.effective_end_date BETWEEN link.effective_start_date AND link.effective_end_date
AND e.effective_end_date BETWEEN iv.effective_start_date AND iv.effective_end_date
AND e.effective_end_date BETWEEN v.effective_start_date AND v.effective_end_date
AND e.effective_end_date = (select max(e1.effective_end_date)
from pay_element_entries_f e1,
pay_element_links_f link1
where link1.element_type_id = g_paye_details_id
and e1.assignment_id = p_assignment_id
and e1.element_link_id = link1.element_link_id);
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_starter_rec.assignment_extra_info_id,
p_aei_information_category => 'GB_RTI_ASG_DETAILS',
p_aei_information8 => 'Y');
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_pensioner_rec.assignment_extra_info_id,
p_aei_information_category => 'GB_RTI_ASG_DETAILS',
p_aei_information9 => 'Y');
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_expat_rec.assignment_extra_info_id,
p_aei_information_category => 'GB_RTI_ASG_DETAILS',
p_aei_information19 => 'Y');
SELECT trim(asg.primary_flag) asg_primary_flag,
trim(pap.per_information10) per_agg_flag
FROM per_all_people_f pap,
per_all_assignments_f asg
WHERE asg.assignment_id = c_asg_id
AND pap.person_id = asg.person_id
AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
SELECT MAX(ASSIGNMENT_ACTION_ID)
FROM pay_assignment_actions
WHERE SOURCE_ACTION_ID = c_lst_act_id;
select pdb.defined_balance_id from
pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_balance_types pbt
where pbt.balance_name = c_balance_name
and pbd.database_item_suffix = c_dim_name
and pbt.balance_type_id= pdb.balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pbt.legislation_code = 'GB'
and pdb.legislation_code = 'GB'
and pbd.legislation_code = 'GB';
select hr_dirbal.get_balance(p_last_asg_action_id,l_tax_pay_def_bal_id) from dual;
SELECT trim(asg.primary_flag) asg_primary_flag,
trim(pap.per_information10) per_agg_flag,
trim(pap.per_information9) per_ni_flag
FROM per_all_people_f pap,
per_all_assignments_f asg
WHERE asg.assignment_id = c_asg_id
AND pap.person_id = asg.person_id
AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
SELECT MAX(ASSIGNMENT_ACTION_ID)
FROM pay_assignment_actions
WHERE SOURCE_ACTION_ID = c_lst_act_id;
select pdb.defined_balance_id from pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_balance_types pbt
where pbt.balance_name = c_balance_name
and pbd.database_item_suffix = c_dim_name
and pbt.balance_type_id= pdb.balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pbt.legislation_code = 'GB'
and pbd.legislation_code = 'GB'
and pdb.legislation_code = 'GB';
select
distinct paei.assignment_id , fnd_date.canonical_to_date(paei.AEI_INFORMATION2)
from per_all_assignments_f paaf1, per_all_assignments_f paaf2 ,per_assignment_extra_info paei,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex sck
where paaf1.assignment_id = c_asg_id
and paaf1.person_id = paaf2.person_id
and paaf2.assignment_type = 'E'
and paei.assignment_id = paaf2.assignment_id
and paei.AEI_INFORMATION_CATEGORY = 'GB_RTI_AGGREGATION'
and paei. AEI_INFORMATION1 = 'Y'
AND pay.payroll_id = paaf2.payroll_id
and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and upper(g_tax_ref) = upper(sck.segment1)
and p_effective_date between pay.effective_start_date and pay.effective_end_date
AND paaf1.effective_start_date =
( select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id = paaf1.assignment_id
and asg2.assignment_type = 'E'
and asg2.effective_start_date <= g_end_year
)
AND paaf2.effective_start_date =
( select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id = paaf2.assignment_id
and asg2.assignment_type = 'E'
and asg2.effective_start_date <= g_end_year
)
order by 2 desc;
select hr_dirbal.get_balance(p_last_asg_action_id,l_tax_pay_def_bal_id) from dual;
select ACTION_TYPE from pay_assignment_actions paa, pay_payroll_actions ppa
where paa.assignment_action_id = p_last_asg_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and ACTION_TYPE in ('V');
SELECT AEI_INFORMATION10 nic_refund
FROM PER_ASSIGNMENT_EXTRA_INFO
WHERE INFORMATION_TYPE = 'GB_PAY_RTI'
AND AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI'
AND assignment_id = p_asg_id;
select
(ACTION_INFORMATION1),
(ACTION_INFORMATION2),
(ACTION_INFORMATION3),
(ACTION_INFORMATION18),
(ACTION_INFORMATION19),
(ACTION_INFORMATION20),
(ACTION_INFORMATION21),
(ACTION_INFORMATION22)
from pay_assignment_actions paa , pay_action_information pai
where paa.payroll_action_id = pactid
and paa.ASSIGNMENT_ID = p_asg_id
and pai.action_context_id = paa.assignment_action_id
and pai.ACTION_INFORMATION_CATEGORY = 'GB_RTI_FPS_ASG_DET2'
and ACTION_CONTEXT_TYPE = 'AAP';
select
(ACTION_INFORMATION1),
(ACTION_INFORMATION2),
(ACTION_INFORMATION3),
(ACTION_INFORMATION18),
(ACTION_INFORMATION19),
(ACTION_INFORMATION20),
(ACTION_INFORMATION21),
(ACTION_INFORMATION22)
from pay_assignment_actions paa , pay_action_information pai
where paa.payroll_action_id = pactid
and paa.ASSIGNMENT_ID in (select distinct assignment_id from per_all_assignments_f where person_id = p_archive_tab(0).person_id)
and pai.action_context_id = paa.assignment_action_id
and pai.ACTION_INFORMATION_CATEGORY = 'GB_RTI_FPS_ASG_DET2'
and ACTION_INFORMATION30 = 'Y'
and ACTION_CONTEXT_TYPE = 'AAP'
order by substr(ACTION_INFORMATION29,instr(ACTION_INFORMATION29,',')+1,length(ACTION_INFORMATION29)) desc; --payroll run asg action id
select
(ACTION_INFORMATION15),
(ACTION_INFORMATION16),
(ACTION_INFORMATION17),
(ACTION_INFORMATION18),
(ACTION_INFORMATION19),
(ACTION_INFORMATION20),
(ACTION_INFORMATION21),
(ACTION_INFORMATION22)
from pay_assignment_actions paa , pay_action_information pai
where paa.payroll_action_id = pactid
and paa.ASSIGNMENT_ID = p_asg_id
and pai.action_context_id = paa.assignment_action_id
and pai.ACTION_INFORMATION_CATEGORY = 'GB_RTI_EYU_ASG_DET'
and ACTION_CONTEXT_TYPE = 'AAP';
select
(ACTION_INFORMATION1) l_ni_cat,
(ACTION_INFORMATION3) l_lel,
(ACTION_INFORMATION4) l_et,
(ACTION_INFORMATION5) l_uap,
(ACTION_INFORMATION6) l_uel,
(ACTION_INFORMATION7) l_emr,
(ACTION_INFORMATION8) l_emp
from pay_assignment_actions paa , pay_action_information pai
where paa.payroll_action_id = pactid
and paa.ASSIGNMENT_ID = p_asg_id
and pai.action_context_id = paa.assignment_action_id
and pai.ACTION_INFORMATION_CATEGORY = 'GB_RTI_FPS_NI_DET'
and ACTION_CONTEXT_TYPE = 'AAP';
select
pai.assignment_id
from pay_assignment_actions paa , pay_action_information pai
where paa.payroll_action_id = pactid
and paa.ASSIGNMENT_ID in (select distinct assignment_id from per_all_assignments_f where person_id = p_person_id)
and pai.action_context_id = paa.assignment_action_id
and pai.ACTION_INFORMATION_CATEGORY = 'GB_RTI_FPS_ASG_DET2'
and ACTION_INFORMATION30 = 'Y'
and ACTION_CONTEXT_TYPE = 'AAP'
order by substr(ACTION_INFORMATION29,instr(ACTION_INFORMATION29,',')+1,length(ACTION_INFORMATION29)) desc; -- payroll run asg action id
select
(ACTION_INFORMATION1) l_ni_cat,
(ACTION_INFORMATION3) l_lel,
(ACTION_INFORMATION4) l_et,
(ACTION_INFORMATION5) l_uap,
(ACTION_INFORMATION6) l_uel,
(ACTION_INFORMATION7) l_emr,
(ACTION_INFORMATION8) l_emp
from pay_assignment_actions paa , pay_action_information pai
where paa.payroll_action_id = pactid
and paa.ASSIGNMENT_ID = l_latest_fps_asg
and pai.action_context_id = paa.assignment_action_id
and pai.ACTION_INFORMATION_CATEGORY = 'GB_RTI_FPS_NI_DET'
and ACTION_CONTEXT_TYPE = 'AAP';
INSERT
INTO pay_message_lines
(
line_sequence,
payroll_id,
message_level,
source_id,
source_type,
line_text
)
VALUES
(
pay_message_lines_s.nextval ,
100 ,
p_message_level, --'F' ,
p_assignment_action_id ,
'A' ,
SUBSTR(p_message_text,1,240)
);
select
nvl(UPPER(hoi.org_information11),' ') sender_id,
nvl(upper(substr(ltrim(hoi.org_information3),1,35)),' ') employer_name,
nvl(upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),' ') tax_ref_no,
lpad(nvl(substr(hoi.org_information1,1,3),' '),3,0) tax_office_no,
decode(PAY_GB_EOY_MAGTAPE.get_payroll_version, ' ', '0', PAY_GB_EOY_MAGTAPE.get_payroll_version) payroll_ver,
nvl(upper(substr(hoi.org_information6,1,13)),' ') acc_ref_no,
hoi.org_information7 econ
from pay_payroll_actions pact,
hr_organization_information hoi
where pact.payroll_action_id=pactid
and pact.business_group_id = hoi.organization_id
and hoi.org_information_context = 'Tax Details References'
and nvl(hoi.org_information10,'UK') = 'UK'
and substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8,
instr(pact.legislative_parameters||' ',' ', instr(pact.legislative_parameters,'TAX_REF=')+8) -
instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
SELECT ppa.effective_date
FROM pay_payroll_actions ppa, pay_assignment_actions paa
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id = p_assactid;
select pay_gb_eoy_archive.get_parameter(legislative_parameters, 'TEST'),
/*ppa.effective_date*/
sysdate
from pay_payroll_actions ppa
,pay_assignment_actions paa
where paa.assignment_action_id = c_assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id;
SELECT object_version_number,
assignment_extra_info_id
FROM per_assignment_extra_info
WHERE assignment_id = p_assig_id
AND information_type = p_type;
PROCEDURE insert_archive_row
(
p_assactid IN NUMBER,
p_effective_date IN DATE,
p_tab_rec_data IN action_info_table
)
IS
l_proc CONSTANT VARCHAR2(50):= g_package||'insert_archive_row';
END insert_archive_row;
Purpose : This returns the select statement that is used to create the
range rows.
Arguments :
Notes : The range cursor determines which people should be processed.
The normal practice is to include everyone, and then limit
the list during the assignment action creation.
--------------------------------------------------------------------------*/
PROCEDURE range_cursor
(
pactid IN NUMBER,
sqlstr OUT NOCOPY VARCHAR2
)
IS
l_proc CONSTANT VARCHAR2(35):= g_package||'range_cursor';
sqlstr := 'select distinct PERSON_ID '|| 'from per_all_people_f ppf, '
|| 'pay_payroll_actions ppa '|| 'where ppa.payroll_action_id = :payroll_action_id '
|| 'and ppa.business_group_id = ppf.business_group_id '
|| ' order by person_id';
SELECT
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
effective_date,
business_group_id
FROM pay_payroll_actions pact
WHERE payroll_action_id = pactid;
select distinct asg.assignment_id assignment_id,trim(pap.per_information10) per_agg_flag,trim(pap.per_information9) per_ni_flag,
pap.person_id person_id , asg.assignment_number l_asg_num
from per_all_people_f pap,
per_all_assignments_f asg,
per_assignment_status_types past,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex sck
where pap.person_id between stperson and endperson
and pap.person_id = asg.person_id
and asg.business_group_id +0 = l_business_group_id
and asg.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN','TERM_ASSIGN')
and asg.payroll_id = pay.payroll_id
and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and upper(l_tax_ref) = upper(sck.segment1)
and asg.effective_start_date =
( select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id = asg.assignment_id
and asg2.assignment_type = 'E'
and asg2.effective_start_date <= g_end_year
)
and l_effective_date between pap.effective_start_date and pap.effective_end_date
and l_effective_date between pay.effective_start_date and pay.effective_end_date
order by person_id,assignment_id;
SELECT aei_information3 rti_payroll_id
FROM per_assignment_extra_info
WHERE assignment_id = asg_id
AND aei_information_category = 'GB_RTI_AGGREGATION';
cursor csr_latest_fps_date(asg_id number) is select ppa.effective_date,ppa.payroll_action_id
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_id = asg_id
and paa.action_status ='C'
and ppa.action_type ='X'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.report_type in( 'RTI_FPS_REP' , 'RTI_EYU_REP_13')
and ppa.effective_date >= g_start_year
and UPPER(l_tax_ref) = UPPER(SUBSTR(pay_gb_eoy_archive.get_parameter(ppa.legislative_parameters,'TAX_REF'),1,20))
order by ppa.effective_date desc;
select paa.ASSIGNMENT_ACTION_ID , ppa.EFFECTIVE_DATE from pay_payroll_actions ppa,pay_assignment_actions paa
where ASSIGNMENT_ID = asg_id
and ppa.ACTION_TYPE in ('Q','R','B','I','V','P','U')
AND paa.action_status IN ('C', 'S')
and ppa.PAYROLL_ACTION_ID = paa.PAYROLL_ACTION_ID
and ppa.PAYROLL_ACTION_ID > p_pact_id
and ppa.EFFECTIVE_DATE <= g_end_year
order by ppa.effective_date desc;
SELECT
to_number (substr (max (lpad (paa.action_sequence, 15
, '0')
|| paa.assignment_action_id), 16))
, max (pact.effective_date) effective_date
FROM pay_assignment_actions paa
, pay_payroll_actions pact
, per_time_periods ptp
WHERE paa.assignment_id = asg_id
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q', 'R', 'B'
, 'I', 'V')
AND paa.action_status IN ('C', 'S')
AND ptp.regular_payment_date
BETWEEN l_latest_fps_run_date
AND g_end_year;
SELECT
pact.action_type
FROM pay_payroll_actions pact, pay_assignment_actions paa
WHERE paa.ASSIGNMENT_ACTION_ID = l_asg_act_id
and paa.payroll_action_id = pact.payroll_action_id;
select paa.ASSIGNMENT_ACTION_ID asg_act_id from pay_payroll_actions ppa,pay_assignment_actions paa,
per_all_assignments_f paaf,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex sck
where paa.ASSIGNMENT_ID = asg_id
AND paaf.assignment_id = asg_id
AND pay.payroll_id = paaf.payroll_id
and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and upper(l_tax_ref) = upper(sck.segment1)
and paaf.effective_start_date =
( select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id = paaf.assignment_id
and asg2.assignment_type = 'E'
and asg2.effective_start_date <= g_end_year
)
and l_effective_date between pay.effective_start_date and pay.effective_end_date
and ppa.ACTION_TYPE in ('Q','R','B','I','V','P','U')
AND paa.action_status IN ('C', 'S')
and ppa.PAYROLL_ACTION_ID = paa.PAYROLL_ACTION_ID
and ppa.PAYROLL_ACTION_ID > p_pact_id
and ppa.EFFECTIVE_DATE <= g_end_year;
select paa.assignment_action_id asg_act_id
FROM pay_assignment_actions paa
, pay_payroll_actions pact
, per_time_periods ptp,
per_all_assignments_f paaf,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex sck
WHERE paa.assignment_id = asg_id
AND paaf.assignment_id = asg_id
AND pay.payroll_id = paaf.payroll_id
and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and upper(l_tax_ref) = upper(sck.segment1)
and paaf.effective_start_date =
( select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id = paaf.assignment_id
and asg2.assignment_type = 'E'
and asg2.effective_start_date <= g_end_year
)
and l_effective_date between pay.effective_start_date and pay.effective_end_date
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q', 'R', 'B'
, 'I', 'V')
AND paa.action_status IN ('C', 'S')
AND ptp.regular_payment_date
BETWEEN g_start_year
AND g_end_year;
select paa.ASSIGNMENT_ACTION_ID asg_act_id from per_all_assignments_f paaf, pay_payroll_actions ppa,pay_assignment_actions paa,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex sck
where paaf.person_ID = per_id
and paa.assignment_id = paaf.assignment_id
AND pay.payroll_id = paaf.payroll_id
and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and upper(l_tax_ref) = upper(sck.segment1)
and l_effective_date between pay.effective_start_date and pay.effective_end_date
and ppa.ACTION_TYPE in ('Q','R','B','I','V','P','U')
AND paa.action_status IN ('C', 'S')
and ppa.PAYROLL_ACTION_ID = paa.PAYROLL_ACTION_ID
and ppa.PAYROLL_ACTION_ID > p_pact_id
AND paaf.effective_start_date =
( select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id = paaf.assignment_id
and asg2.assignment_type = 'E'
and asg2.effective_start_date <= g_end_year
)
and ppa.EFFECTIVE_DATE <= g_end_year;
select paa.ASSIGNMENT_ACTION_ID asg_act_id
FROM per_all_assignments_f paaf,
pay_assignment_actions paa
, pay_payroll_actions pact
, per_time_periods ptp,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex sck
WHERE paa.assignment_id = paaf.assignment_id
and paaf.person_id = per_id
AND pay.payroll_id = paaf.payroll_id
and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and upper(l_tax_ref) = upper(sck.segment1)
and l_effective_date between pay.effective_start_date and pay.effective_end_date
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q', 'R', 'B'
, 'I', 'V')
AND paa.action_status IN ('C', 'S')
AND ptp.regular_payment_date
BETWEEN g_start_year
AND g_end_year
AND paaf.effective_start_date =
( select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id = paaf.assignment_id
and asg2.assignment_type = 'E'
and asg2.effective_start_date <= g_end_year
);
SELECT /*+ USE_NL(paa, pact, ptp) */
to_number (substr (max (lpad (paa.action_sequence, 15
, '0')
|| paa.assignment_action_id), 16))
, max (pact.effective_date) effective_date
FROM per_all_assignments_f paaf,
pay_assignment_actions paa
, pay_payroll_actions pact
, per_time_periods ptp,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex sck
WHERE paa.assignment_id = paaf.assignment_id
and paaf.person_id = per_id
AND pay.payroll_id = paaf.payroll_id
and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and upper(l_tax_ref) = upper(sck.segment1)
and l_effective_date between pay.effective_start_date and pay.effective_end_date
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q', 'R', 'B'
, 'I', 'V')
AND paa.action_status IN ('C', 'S')
AND ptp.regular_payment_date
BETWEEN g_start_year
AND g_end_year
AND paaf.effective_start_date =
( select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id = paaf.assignment_id
and asg2.assignment_type = 'E'
and asg2.effective_start_date <= g_end_year
);
SELECT /*+ USE_NL(paa, pact, ptp) */
to_number (substr (max (lpad (paa.action_sequence, 15
, '0')
|| paa.assignment_action_id), 16))
, max (pact.effective_date) effective_date
FROM per_all_assignments_f paaf,
pay_assignment_actions paa
, pay_payroll_actions pact
, per_time_periods ptp,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex sck
WHERE paa.assignment_id = paaf.assignment_id
and paaf.person_id = per_id
and paaf.assignment_id <> asg_id
AND pay.payroll_id = paaf.payroll_id
and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and upper(l_tax_ref) = upper(sck.segment1)
and l_effective_date between pay.effective_start_date and pay.effective_end_date
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q', 'R', 'B'
, 'I', 'V')
AND paa.action_status IN ('C', 'S')
AND ptp.regular_payment_date
BETWEEN g_start_year
AND g_end_year
AND paaf.effective_start_date =
( select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id = paaf.assignment_id
and asg2.assignment_type = 'E'
and asg2.effective_start_date <= g_end_year
);
select assignment_id from pay_assignment_actions where assignment_action_id = p_asg_act_id;
select AEI_INFORMATION1, AEI_INFORMATION2 from per_assignment_extra_info where assignment_id = c_asg_id
and AEI_INFORMATION_CATEGORY = 'GB_RTI_AGGREGATION';
select
distinct paei.assignment_id , fnd_date.canonical_to_date(paei.AEI_INFORMATION2)
from per_all_assignments_f paaf1, per_all_assignments_f paaf2 ,per_assignment_extra_info paei,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex sck
where paaf1.assignment_id = c_asg_id
and paaf1.person_id = paaf2.person_id
and paaf2.assignment_type= 'E'
and paei.assignment_id = paaf2.assignment_id
and paei.AEI_INFORMATION_CATEGORY = 'GB_RTI_AGGREGATION'
and paei. AEI_INFORMATION1 = 'Y'
AND pay.payroll_id = paaf2.payroll_id
and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and upper(l_tax_ref) = upper(sck.segment1)
and l_effective_date between pay.effective_start_date and pay.effective_end_date
AND paaf1.effective_start_date =
( select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id = paaf1.assignment_id
and asg2.assignment_type = 'E'
and asg2.effective_start_date <= g_end_year
)
AND paaf2.effective_start_date =
( select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id = paaf2.assignment_id
and asg2.assignment_type = 'E'
and asg2.effective_start_date <= g_end_year
)
order by 2 desc;
SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;
-- Insert assignment into pay_assignment_actions
hr_nonrun_asact.insact ( lockingactid, asg_rec.assignment_id, pactid, chunk, NULL );
SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;
-- Insert assignment into pay_assignment_actions
hr_nonrun_asact.insact ( lockingactid, l_ni_rpt_asg_id, pactid, chunk, NULL );
SELECT aei.assignment_extra_info_id,
aei.object_version_number,
aei.aei_information8
FROM pay_assignment_actions paa,
per_assignment_extra_info aei
WHERE paa.assignment_action_id = p_assact
AND aei.assignment_id = paa.assignment_id
AND aei.information_type = p_type;
SELECT aei.assignment_extra_info_id,
aei.object_version_number,
aei.aei_information9
FROM pay_assignment_actions paa,
per_assignment_extra_info aei
WHERE paa.assignment_action_id = p_assact
AND aei.assignment_id = paa.assignment_id
AND aei.information_type = p_type;
SELECT aei.assignment_extra_info_id,
aei.object_version_number,
aei.aei_information19
FROM pay_assignment_actions paa,
per_assignment_extra_info aei
WHERE paa.assignment_action_id = p_assact
AND aei.assignment_id = paa.assignment_id
AND aei.information_type = p_type;
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_aei_starter_rec.assignment_extra_info_id,
p_aei_information_category => p_type,
p_aei_information8 => 'N');
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_aei_pensioner_rec.assignment_extra_info_id,
p_aei_information_category => p_type,
p_aei_information9 => 'N');
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_aei_expat_rec.assignment_extra_info_id,
p_aei_information_category => p_type,
p_aei_information19 => 'N');
SELECT
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT NVL(UPPER(hoi.org_information11),' ') sender_id,
DECODE(SUBSTR(pact.legislative_parameters,instr(pact.legislative_parameters,'TEST=') + 5,1) ,'N',' ','Y','1') test_indicator,
fnd_number.number_to_canonical(pact.request_id) request_id,
NVL(upper(SUBSTR(ltrim(hoi.org_information3),1,35)),' ') employer_name,
NVL(upper(SUBSTR(ltrim(SUBSTR(hoi.org_information1,4,11),'/'),1,10)),' ') tax_ref_no,
lpad(NVL(SUBSTR(hoi.org_information1,1,3),' '),3,0) tax_office_no,
DECODE(PAY_GB_EOY_MAGTAPE.get_payroll_version, ' ', '0', PAY_GB_EOY_MAGTAPE.get_payroll_version) payroll_ver,
NVL(upper(SUBSTR(hoi.org_information6,1,13)),' ') acc_ref_no,
report_type
FROM pay_payroll_actions pact,
hr_organization_information hoi
WHERE pact.payroll_action_id =pactid
AND pact.business_group_id = hoi.organization_id
AND hoi.org_information_context = 'Tax Details References'
AND NVL(hoi.org_information10,'UK') = 'UK'
AND SUBSTR(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8, instr(pact.legislative_parameters
||' ',' ', instr(pact.legislative_parameters,'TAX_REF=') +8) - instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
SELECT defined_balance_id
FROM pay_defined_balances db,
pay_balance_types b,
pay_balance_dimensions d
WHERE b.balance_name = p_balance_name
AND d.dimension_name = p_dimension_name
AND db.balance_type_id = b.balance_type_id
AND db.balance_dimension_id = d.balance_dimension_id
AND b.legislation_code = 'GB'
AND d.legislation_code = 'GB'
AND db.legislation_code = 'GB';
SELECT b.balance_name balance_name,
b.balance_type_id balance_type_id,
d.database_item_suffix database_item_suffix,
d.balance_dimension_id balance_dimension_id,
db.defined_balance_id defined_balance_id
FROM pay_defined_balances db,
pay_balance_types b,
pay_balance_dimensions d
WHERE db.balance_type_id = b.balance_type_id
AND db.balance_dimension_id = d.balance_dimension_id
AND (d.database_item_suffix = '_ASG_TD_YTD'
AND b.balance_name IN ('Taxable Pay','PAYE','Student Loan','SSP Total',
'SMP Total','SPP Adoption Total', 'SPP Birth Total','SAP Total',
'ASPP Adoption Total','ASPP Birth Total'
))
AND b.legislation_code = 'GB'
AND d.legislation_code = 'GB'
AND db.legislation_code = 'GB';
SELECT b.balance_name balance_name,
b.balance_type_id balance_type_id,
d.database_item_suffix database_item_suffix,
d.balance_dimension_id balance_dimension_id,
db.defined_balance_id defined_balance_id
FROM pay_defined_balances db,
pay_balance_types b,
pay_balance_dimensions d
WHERE db.balance_type_id = b.balance_type_id
AND db.balance_dimension_id = d.balance_dimension_id
AND ((d.database_item_suffix = '_PER_TD_YTD'
AND b.balance_name IN ('Taxable Pay','Student Loan','SSP Total','SMP Total',
'SPP Adoption Total', 'SAP Total','ASPP Adoption Total',
'SPP Birth Total','ASPP Birth Total'))
OR(d.database_item_suffix = '_PER_TD_CPE_YTD'
AND b.balance_name IN ('PAYE')))
and b.legislation_code = 'GB'
and d.legislation_code = 'GB'
and db.legislation_code = 'GB';
SELECT b.balance_name balance_name,
b.balance_type_id balance_type_id,
d.database_item_suffix database_item_suffix,
d.balance_dimension_id balance_dimension_id,
db.defined_balance_id defined_balance_id
FROM pay_defined_balances db,
pay_balance_types b,
pay_balance_dimensions d
WHERE db.balance_type_id = b.balance_type_id
AND db.balance_dimension_id = d.balance_dimension_id
AND (d.database_item_suffix = '_ASG_TD_YTD'
AND b.balance_name IN ('NI A Total','NI A Able',
'NI B Total','NI B Able',
'NI C Total','NI C Able',
'NI D Total','NI D Able',
'NI E Total','NI E Able',
'NI J Total','NI J Able',
'NI L Total','NI L Able'))
and b.legislation_code = 'GB'
and d.legislation_code = 'GB'
and db.legislation_code = 'GB';
SELECT b.balance_name balance_name,
b.balance_type_id balance_type_id,
d.database_item_suffix database_item_suffix,
d.balance_dimension_id balance_dimension_id,
db.defined_balance_id defined_balance_id
FROM pay_defined_balances db,
pay_balance_types b,
pay_balance_dimensions d
WHERE db.balance_type_id = b.balance_type_id
AND db.balance_dimension_id = d.balance_dimension_id
AND (d.database_item_suffix = '_PER_TD_YTD'
AND b.balance_name IN ('NI A Total','NI A Able',
'NI B Total','NI B Able',
'NI C Total','NI C Able',
'NI D Total','NI D Able',
'NI E Total','NI E Able',
'NI J Total','NI J Able',
'NI L Total','NI L Able'))
and b.legislation_code = 'GB'
and d.legislation_code = 'GB'
and db.legislation_code = 'GB';
SELECT distinct element_type_id
INTO g_paye_element_id
FROM pay_element_types_f
WHERE element_name = 'PAYE'
AND legislation_code = 'GB';
SELECT distinct element_type_id
INTO g_paye_details_id
FROM pay_element_types_f
WHERE element_name = 'PAYE Details'
AND legislation_code = 'GB';
select paaf.assignment_id ,
paaf.person_id ,
trim(NVL(pap.per_information9,'N')) per_ni_agg_flag,
trim(NVL(pap.per_information10,'N')) per_paye_agg_flag,
assignment_number
from pay_assignment_actions paa,
per_all_assignments_f paaf,
per_all_people_f pap
where assignment_action_id = p_assactid
and paaf.assignment_id = paa.assignment_id
and pap.person_id = paaf.person_id
and paaf.effective_start_date =
( select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id = paaf.assignment_id
and asg2.assignment_type = 'E'
and asg2.effective_start_date <= g_end_year
)
AND g_end_year BETWEEN pap.effective_start_date AND pap.effective_end_date;
SELECT
to_number (substr (max (lpad (paa.action_sequence, 15
, '0')
|| paa.assignment_action_id), 16))
, max (pact.effective_date) effective_date
FROM pay_assignment_actions paa
, pay_payroll_actions pact
, per_time_periods ptp
WHERE paa.assignment_id = asg_id
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q', 'R', 'B'
, 'I', 'V')
AND paa.action_status IN ('C', 'S')
AND ptp.regular_payment_date
BETWEEN g_start_year
AND g_end_year;
SELECT /*+ USE_NL(paa, pact, ptp) */
to_number (substr (max (lpad (paa.action_sequence, 15
, '0')
|| paa.assignment_action_id), 16))
, max (pact.effective_date) effective_date
FROM per_all_assignments_f paaf,
pay_assignment_actions paa
, pay_payroll_actions pact
, per_time_periods ptp,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex sck
WHERE paa.assignment_id = paaf.assignment_id
and paaf.person_id = person_id
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q', 'R', 'B'
, 'I', 'V')
AND paa.action_status IN ('C', 'S')
AND pay.payroll_id = paaf.payroll_id
and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and upper(g_tax_ref) = upper(sck.segment1)
AND ptp.regular_payment_date
BETWEEN g_start_year
AND g_end_year
AND paaf.effective_start_date =
( select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id = paaf.assignment_id
and asg2.assignment_type = 'E'
and asg2.effective_start_date <= g_end_year
);
select ppa.effective_date,ppa.payroll_action_id,ppa.report_type
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_id = asg_id
and paa.action_status ='C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.report_type in ( 'RTI_FPS_REP' , 'RTI_EYU_REP_13')
and UPPER(g_tax_ref) = UPPER(SUBSTR(pay_gb_eoy_archive.get_parameter(ppa.legislative_parameters,'TAX_REF'),1,20))
order by ppa.effective_date desc;
select ppa.effective_date,ppa.payroll_action_id,ppa.report_type
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_all_assignments_f paaf ,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex sck
where paaf.person_id = per_id
AND pay.payroll_id = paaf.payroll_id
and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and upper(g_tax_ref) = upper(sck.segment1)
and paa.assignment_id = paaf.assignment_id
and paa.action_status ='C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.report_type in ( 'RTI_FPS_REP' , 'RTI_EYU_REP_13')
and paaf.effective_start_date =
( select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id = paaf.assignment_id
and asg2.assignment_type = 'E'
and asg2.effective_start_date <= g_end_year
)
and g_end_year between pay.effective_start_date and pay.effective_end_date
order by ppa.effective_date desc;
select 'Y' from
pay_assignment_actions
where assignment_action_id = p_assactid
AND ACTION_STATUS = 'M';
SELECT assignment_extra_info_id l_aei_id,
aei_information8 starter_flag,
aei_information9 pensioner_flag,
aei_information19 expat_flag,
object_version_number l_ovn
FROM per_assignment_extra_info
WHERE assignment_id = p_asg_id
AND information_type = 'GB_RTI_ASG_DETAILS';
select count(*)
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_id = p_asg_id
and paa.action_status ='C'
and paa.assignment_action_id <> p_assactid
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.report_type in ( 'RTI_FPS_REP' , 'RTI_EYU_REP_13')
order by ppa.effective_date desc;
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_new_starter.l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS',
p_aei_information8 => 'N');
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_new_starter.l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS',
p_aei_information9 => 'N');
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_new_starter.l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS',
p_aei_information19 => 'N');
insert_archive_row(p_assactid, p_effective_date,l_archive_tab);
insert_archive_row(p_assactid, p_effective_date,l_archive_tab_ni_det);
SELECT report_type
FROM pay_payroll_actions pact
WHERE pact.payroll_action_id = pactid;
SELECT NVL(UPPER(hoi.org_information11),' ') sender_id,
DECODE(SUBSTR(pact.legislative_parameters,instr(pact.legislative_parameters,'TEST=') + 5,1) ,'N',' ','Y','1') test_indicator,
fnd_number.number_to_canonical(pact.request_id) request_id,
NVL(upper(SUBSTR(ltrim(hoi.org_information3),1,35)),' ') employer_name,
NVL(upper(SUBSTR(ltrim(SUBSTR(hoi.org_information1,4,11),'/'),1,10)),' ') tax_ref_no,
lpad(SUBSTR(hoi.ORG_INFORMATION1,0,instr(hoi.ORG_INFORMATION1,'/')-1),3,0) tax_office_no,
DECODE(PAY_GB_EOY_MAGTAPE.get_payroll_version, ' ', '0', PAY_GB_EOY_MAGTAPE.get_payroll_version) payroll_ver,
(lpad(SUBSTR(hoi.ORG_INFORMATION6,0,instr(hoi.ORG_INFORMATION6,'P')-1),3,0)
|| 'P'
|| SUBSTR(hoi.ORG_INFORMATION6,instr(hoi.ORG_INFORMATION6,'P') +1,1)
|| lpad(SUBSTR(hoi.ORG_INFORMATION6,instr(hoi.ORG_INFORMATION6,'P')+2,LENGTH(hoi.ORG_INFORMATION6)-3-(instr(hoi.ORG_INFORMATION6,'P')-1)),7,0)
|| SUBSTR(hoi.ORG_INFORMATION6,LENGTH(hoi.ORG_INFORMATION6),1) ) AS acc_ref_no,
hoi.org_information7 econ_number,
pact.business_group_id bus_grp_id,
pact.action_parameter_group_id act_param_grp_id,
org_information19 service_company
FROM pay_payroll_actions pact,
hr_organization_information hoi
WHERE pact.payroll_action_id =pactid
AND pact.business_group_id = hoi.organization_id
AND hoi.org_information_context = 'Tax Details References'
AND NVL(hoi.org_information10,'UK') = 'UK'
AND SUBSTR(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8, instr(pact.legislative_parameters
||' ',' ', instr(pact.legislative_parameters,'TAX_REF=') +8) - instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
SELECT legislative_parameters para,
fnd_number.number_to_canonical(request_id) control_id,
report_type,
business_group_id
FROM pay_payroll_actions pact
WHERE payroll_action_id = pactid;
SELECT NVL(hoi.org_information11,' ') sender_id,
NVL(upper(hoi.org_information2),' ') hrmc_office,
NVL(upper(hoi.org_information4),' ') er_addr,
NVL(upper(hoi.org_information3),' ') er_name
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_bus_id
AND hoi.org_information_context = 'Tax Details References'
AND NVL(hoi.org_information10,'UK') = 'UK'
AND upper(hoi.org_information1) = upper(p_tax_ref);
SELECT ppf.PAYROLL_NAME
from pay_payrolls_f ppf,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = pactid
and ppf.payroll_id = SUBSTR(pay_gb_eoy_archive.get_parameter(ppa.legislative_parameters,'PAYROLL'),1,20);
SELECT DISTINCT paa.assignment_action_id asg_action_id,
assignment_id
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE ppa.payroll_action_id = pactid -- pact_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'E';
SELECT DISTINCT line_text
FROM pay_message_lines
WHERE source_id = asg_action_id --9919881 --asg_action_id
AND message_level <> 'W'-- p_message_level
AND payroll_id = 100;
l_form_name := 'RTI Earlier Year Update Process (EYU) 2012/13';
SELECT DISTINCT paa.assignment_action_id asg_action_id,
assignment_id
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE ppa.payroll_action_id = pactid -- pact_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C';
SELECT DISTINCT paa.assignment_action_id asg_action_id,
assignment_id
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE ppa.payroll_action_id = pactid -- pact_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'E';
SELECT DISTINCT pap.first_name f_name ,
pap.middle_names m_name,
pap.last_name l_name,
pap.title title,
paa.assignment_number emp_no,
NVL(pap.national_identifier,' ')ni_no,
NVL(pap.employee_number,' ') employee_number,
TO_CHAR(paa.EFFECTIVE_START_DATE,'DD-MON-RRRR') start_date,
paa.EFFECTIVE_END_DATE end_date, --to_char(paa.EFFECTIVE_END_DATE,'DD-MON-RRRR') end_date
pap.person_id
FROM per_all_assignments_f paa,
per_assignment_status_types past,
per_all_people_f pap
WHERE paa.person_id = pap.person_id
AND paa.assignment_id = c_assignment_id
AND past.ASSIGNMENT_STATUS_TYPE_ID = paa.ASSIGNMENT_STATUS_TYPE_ID
AND past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN') -- Added for Bug#13626488
and pap.effective_start_date =
( select max(pap2.effective_start_date) from
per_all_people_f pap2
where pap2.person_id = pap.person_id
and pap2.effective_start_date <= g_end_year
)
and paa.effective_start_date =
( select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id = paa.assignment_id
and asg2.assignment_type = 'E'
and ASSIGNMENT_STATUS_TYPE_ID = paa.ASSIGNMENT_STATUS_TYPE_ID
and asg2.effective_start_date <= g_end_year
)
ORDER BY end_date DESC;
SELECT DISTINCT line_text
FROM pay_message_lines
WHERE source_id = asg_action_id
--AND message_level <> 'W'-- p_message_level
AND payroll_id = 100;
SELECT LINE_TEXT into err_msg FROM pay_message_lines WHERE source_id = action_id.asg_action_id and rownum <=1;
SELECT COUNT(DISTINCT(paaf.person_id))
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_all_assignments_f paaf
WHERE ppa.payroll_action_id = pactid -- pact_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C'
AND paaf.assignment_id = paa.assignment_id;
delete from pay_action_information pai
where pai.action_context_id = pactid
and pai.action_context_type = 'PA'
and pai.action_information_category in ('RTI PAYROLL INFO');
delete from pay_action_information pai
where pai.action_context_id = pactid
and pai.action_context_type = 'PA'
and pai.action_information_category in ('GB_RTI_FPS_EXTRA_DET');