[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
09-Jan-2013 krreddy 115.3 14759137 Modified to update the logic for
name_and_initials, w1_m1_indicator
and NI Employee YTD values.
10-Jan-2013 ssarap 115.4 16102030 Archiving date of birth , sex and tax year.
17-Jan-2013 krreddy 115.5 16175074 Modified for the below fields:
w1_m1_indicator, week_53_indicator, refund_flag etc.
24-Jan-2013 krreddy 115.6 16076312 Modified for the below:
PAYE Aggregation logic
NI Aggregation logic
25-Jan-2013 krreddy 115.7 16076312 Modified to fix No Data Found error and remove unnecessary variables.
29-Jan-2013 ssarap 115.8 Added the function get_column_value to be used in PYGBRTIP60OP.xml report.
=============================================================================*/
--
--
g_package CONSTANT VARCHAR2(20) := 'pay_gb_rti_fps.';
SELECT to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL_ID')) payroll_id,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
effective_date eff_date,
business_group_id bg_id
FROM pay_payroll_actions pact
WHERE payroll_action_id = p_pactid;
SELECT
distinct substr(flex.segment1,1,3) tax_district_reference,
substr(ltrim(substr(org_information1,4,11),'/') ,1,10) tax_reference,
flex.segment1 emp_paye_ref,
substr(org.org_information2 ,1,40) tax_district_name,
substr(ltrim(org.org_information3),1,36) employers_name,
substr(ltrim(org.org_information4),1,60) employers_address_line
FROM pay_all_payrolls_f p,
hr_soft_coding_keyflex flex,
hr_organization_information org
WHERE p.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND org.org_information_context = 'Tax Details References'
AND org.org_information1 = flex.segment1
AND NVL(org.org_information10,'UK') = 'UK'
AND flex.segment10 IS NOT NULL
AND p.business_group_id = p_bg_id
AND org.organization_id = p_bg_id
AND p_end_year BETWEEN p.effective_start_date
AND p.effective_end_date
AND org.org_information1 = p_tax_ref;
SELECT substr(last_name, 1,35) last_name,
substr(first_name, 1,35) first_name,
substr(middle_names,1,35) middle_names,
date_of_birth, title,
substr(expense_check_send_to_address,1,1) expense_check_send_to_address,
substr(national_identifier,1,9) national_identifier,
substr(sex,1,1) sex ,
decode(substr(per_information4,1,1),'Y','P',' ') pensioner_indicator,
decode(per_information10,'Y','Y',NULL) agg_paye_flag,
decode(per_information9,'Y','Y',NULL) multiple_asg_flag,
per.employee_number employee_number,
per.EXPENSE_CHECK_SEND_TO_ADDRESS
FROM per_all_people_f per
WHERE per.person_id = p_person_id
AND g_end_year BETWEEN per.effective_start_date
AND per.effective_end_date;
SELECT ltrim(rtrim(pad.address_line1)) address_line1,
ltrim(rtrim(pad.address_line2)) address_line2,
ltrim(rtrim(pad.address_line3)) address_line3,
ltrim(rtrim(pad.town_or_city)) town_or_city,
substr(l.meaning,1,27) county,
substr(pad.postal_code,1,8),
country
FROM per_addresses pad,
hr_lookups l
WHERE pad.person_id = p_person_id
AND pad.primary_flag = 'Y'
AND l.lookup_type(+) = 'GB_COUNTY'
AND l.lookup_code(+) = pad.region_1
AND sysdate BETWEEN nvl(pad.date_from, sysdate)
AND nvl(pad.date_to, sysdate);
SELECT substr(ftt.territory_short_name, 1, 35) country -- 4011263
FROM fnd_territories_tl ftt
WHERE ftt.territory_code = p_country_code
AND ftt.language = userenv('LANG');
SELECT asg.assignment_number,
asg.people_group_id
FROM per_all_assignments_f asg
WHERE asg.assignment_id = p_asg_id
AND sysdate BETWEEN asg.effective_start_date AND asg.effective_end_date;
SELECT MAX(ASSIGNMENT_ACTION_ID)
FROM pay_assignment_actions
WHERE SOURCE_ACTION_ID = c_lst_act_id;
SELECT MAX(DECODE(name,'Tax Code',prrv.result_value,NULL)),
MAX(DECODE(name,'Tax Basis',prrv.result_value,NULL)),
to_number(max(decode(name,'Pay Previous',
fnd_number.canonical_to_number(prrv.result_value),NULL)))
pay_previous,
to_number(max(decode(name,'Tax Previous',
fnd_number.canonical_to_number(prrv.result_value),NULL)))
tax_previous
FROM pay_run_results prr ,
pay_run_result_values prrv ,
pay_input_values_f pivf
WHERE prr.assignment_action_id = p_action_id
AND prr.ELEMENT_TYPE_ID = g_paye_element_id
AND prrv.run_result_id = prr.run_result_id
AND pivf.INPUT_VALUE_ID = prrv.INPUT_VALUE_ID;
SELECT max(decode(iv.name,'Tax Code',screen_entry_value)) tax_code,
max(decode(iv.name,'Tax Basis',screen_entry_value,NULL)) tax_basis,
max(decode(iv.name,'Pay Previous',
fnd_number.canonical_to_number(screen_entry_value),NULL))
pay_previous,
max(decode(iv.name,'Tax Previous',
fnd_number.canonical_to_number(screen_entry_value),NULL))
tax_previous
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_asg_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_asg_id
and e1.element_link_id = link1.element_link_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';
SELECT hr_dirbal.get_balance(p_last_asg_action_id,l_tax_pay_def_bal_id)
FROM dual;
SELECT max(ppa.payroll_id),
max(ptp.period_type),
max(ptp.period_num) max_period_number,
min(ptp.start_date) start_year,
max(ptp.end_date) end_year
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
WHERE assignment_action_id = p_last_asg_action_id
AND ppa.time_period_id = ptp.time_period_id
AND ppa.payroll_id = ptp.payroll_id
AND paa.payroll_action_id = ppa.payroll_action_id;
SELECT ptpt.number_per_fiscal_year
FROM per_time_period_types ptpt
WHERE p_period_type = ptpt.period_type;
SELECT start_date
FROM per_time_periods ptp
WHERE payroll_id = p_payroll_id
AND regular_payment_date BETWEEN p_prl_start_year AND p_prl_end_year
AND period_num = p_prl_max_period_number;
SELECT SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref
FROM pay_payroll_actions pact
WHERE payroll_action_id = p_pactid;
SELECT min(paaf.effective_start_date) min_active,
max(paaf.effective_end_date) max_active
FROM per_all_assignments_f paaf,
per_assignment_status_types past,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex flex
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')
AND paaf.payroll_id = papf.payroll_id
AND paaf.effective_start_date BETWEEN papf.effective_start_date and papf.effective_end_date
AND papf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND flex.segment1 = p_tax_ref;
SELECT trim(NVL(asg.primary_flag,'N')) asg_primary_flag,
trim(NVL(pap.per_information9,'N')) per_ni_agg_flag,
trim(NVL(pap.per_information10,'N')) per_paye_agg_flag,
asg.assignment_number assignment_number
FROM per_all_people_f pap,
per_all_assignments_f asg,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_action_id = p_last_asg_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_id = asg.assignment_id
AND pap.person_id = asg.person_id
AND ppa.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND ppa.effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
AND pap.per_information_category = 'GB';
SELECT
/*+ USE_NL(paa, pact, ptp) */
to_number(SUBSTR(MAX(lpad(paa.action_sequence,15,'0')
|| paa.assignment_action_id),16)) max_asg_act_id,
MAX(pact.effective_date) effective_date
FROM pay_assignment_actions paa,
per_all_assignments_f paaf,
pay_payroll_actions pact,
per_time_periods ptp
WHERE paa.assignment_id = paaf.assignment_id
AND paaf.person_id = p_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 ptp.regular_payment_date BETWEEN p_start_date AND p_end_date;
select paa.assignment_action_id, paa.assignment_id
from pay_assignment_actions paa,
per_all_assignments_f paaf
where paaf.person_id = p_person_id
and paaf.assignment_id = paa.assignment_id
and paa.payroll_action_id = p_pactid
and paaf.effective_start_date =
(select min(paaf1.effective_start_date)
from pay_assignment_actions paa1,
per_all_assignments_f paaf1
where paaf1.person_id = p_person_id
and paaf1.assignment_id = paa1.assignment_id
and paa1.payroll_action_id = p_pactid
);
select paa.assignment_id
from pay_assignment_actions paa,
per_all_assignments_f paaf,
per_all_people_f papf
where paaf.person_id = p_person_id --41650 --
and paaf.assignment_id = paa.assignment_id
and paa.payroll_action_id = p_pactid --923889 --
and papf.person_id = paaf.person_id
and trim(nvl(paaf.primary_flag,'N')) = 'Y';
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;
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;
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;
SELECT 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';
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 = l_last_asg_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and ACTION_TYPE in ('V');
SELECT 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';
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 = l_last_asg_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and ACTION_TYPE in ('V');
SELECT 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';
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 = l_last_asg_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and ACTION_TYPE in ('V');
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)
-- public procedure which archives the payroll information, then returns a
-- varchar2 defining a SQL Statement to select all the people that may be
-- eligible for Year End reporting.
-- The archiver uses this cursor to split the people into chunks for parallel
-- processing.
IS
--
l_proc CONSTANT VARCHAR2(32):= g_package||'range_cursor';
SELECT
-- to_date('06/04/'||to_char(start_date,'YYYY'),'dd/mm/yyyy') start_year,
-- effective_date end_year,
business_group_id,
substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'PERMIT'),1,12) permit,
substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'TAX_REF'),1,3) tax_dist,
substr(ltrim(substr(pay_gb_eoy_archive.get_parameter(
legislative_parameters,'TAX_REF'),4,11),'/'),1,10) tax_ref, -- 4011263: tax ref can be 10 chars long
substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'TEST'),1,1) test_indicator,
substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'UNIQUE_TEST_ID'),1,8) unique_test_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_pact_id;
SELECT
p.payroll_id payroll_id,
substr(flex.segment10,1,12) permit_number,
p.payroll_name payroll_name,
substr(flex.segment1,1,3) tax_district_reference,
substr(ltrim(substr(org_information1,4,11),'/') ,1,10) tax_reference,
flex.segment1 emp_paye_ref,
substr(org.org_information2 ,1,40) tax_district_name,
substr(ltrim(org.org_information3),1,36) employers_name, -- 4011263: added ltrim
substr(ltrim(org.org_information4),1,60) employers_address_line, -- 4011263: added ltrim
substr(nvl(flex.segment14,org.org_information7),1,9) econ
FROM pay_all_payrolls_f p,
hr_soft_coding_keyflex flex,
hr_organization_information org
WHERE p.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND org.org_information_context = 'Tax Details References'
AND org.org_information1 = flex.segment1
AND NVL(org.org_information10,'UK') = 'UK'
AND flex.segment10 IS NOT NULL
AND p.business_group_id = p_bg_id
AND org.organization_id = p_bg_id
AND p_end_year BETWEEN p.effective_start_date
AND p.effective_end_date;
SELECT flex.segment1
FROM pay_all_payrolls_f p,
hr_soft_coding_keyflex flex
WHERE p.soft_coding_keyflex_id = flex.soft_coding_keyflex_id(+)
AND p.payroll_id = p_payroll_id
AND p.effective_start_date <= hr_gbbal.span_end(p_end_year)
AND p.effective_end_date >= hr_gbbal.span_start(p_end_year)
AND nvl(flex.segment1, 'XYZ') <> nvl(p_paye_ref, 'ABC');
SELECT
min(start_date) start_year,
max(end_date) end_year,
max(period_type) period_type,
max(period_num) max_period_number
FROM per_time_periods ptp
WHERE ptp.payroll_id = p_payroll_id
AND ptp.regular_payment_date BETWEEN p_start_year
AND p_end_year;
SELECT user_entity_id
FROM ff_user_entities
WHERE user_entity_name = p_entity_name
AND legislation_code = 'GB'
AND business_group_id IS NULL;
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 +0= ppf.business_group_id
--and rownum < 50
ORDER BY ppf.person_id';
sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
SELECT --to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL_ID')) payroll_id,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
-- SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'FIRST_FPS'),1,1) first_fps,
effective_date,
business_group_id,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'ASG_SET'),1,20) asg_set_id
-- fnd_date.canonical_to_date(SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'FIRST_FPS_DATE'),1,10)) first_fps_date
FROM pay_payroll_actions pact
WHERE payroll_action_id = pactid;
SELECT asg.person_id person_id,
trim(asg.primary_flag) asg_primary_flag,
trim(nvl(pap.per_information10,'N')) per_agg_flag,
trim(nvl(pap.per_information9,'N')) ni_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 asg.business_group_id = l_business_group_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 EFFECTIVE_DATE from pay_payroll_actions where payroll_action_id = p_pre_pact_id;
select distinct paaf.assignment_id assignment_id,
trim(paaf.primary_flag) asg_primary_flag,
trim(pap.per_information10) per_agg_flag,
pap.person_id
from per_all_people_f pap,
per_all_assignments_f paaf,
per_assignment_status_types past,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex flex
where pap.person_id between stperson and endperson
and pap.person_id = paaf.person_id
and paaf.assignment_type = 'E'
and paaf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN','TERM_ASSIGN')
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 <= c_eff_date
)
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 <= c_eff_date
)
AND papf.payroll_id = paaf.payroll_id
AND flex.SOFT_CODING_KEYFLEX_ID = papf.SOFT_CODING_KEYFLEX_ID
AND upper(flex.segment1) = upper(c_tax_ref)
AND (c_asg_set_id IS NULL -- don't check for assignment set in this case
OR EXISTS (SELECT 1 FROM hr_assignment_sets has1
WHERE has1.assignment_set_id = c_asg_set_id
-- AND has1.business_group_id = asg.business_group_id
AND nvl(has1.payroll_id, paaf.payroll_id ) = paaf.payroll_id
AND (NOT EXISTS (SELECT 1 -- chk no amendmts
FROM hr_assignment_set_amendments hasa1
WHERE hasa1.assignment_set_id =
has1.assignment_set_id)
OR EXISTS (SELECT 1 -- chk include amendmts
FROM hr_assignment_set_amendments hasa2
WHERE hasa2.assignment_set_id =
has1.assignment_set_id
AND hasa2.assignment_id = paaf.assignment_id
AND nvl(hasa2.include_or_exclude,'I') = 'I')
OR (NOT EXISTS (SELECT 1 --chk no exlude amendmts
FROM hr_assignment_set_amendments hasa3
WHERE hasa3.assignment_set_id =
has1.assignment_set_id
AND hasa3.assignment_id = paaf.assignment_id
AND nvl(hasa3.include_or_exclude,'I') = 'E')
AND NOT EXISTS (SELECT 1 --and chk no Inc amendmts
FROM hr_assignment_set_amendments hasa4
WHERE hasa4.assignment_set_id =
has1.assignment_set_id
AND nvl(hasa4.include_or_exclude,'I') = 'I') ) -- end checking exclude amendmts
) -- done checking amendments
) -- done asg set check when not null
)
--and pap.person_id = 41689
order by pap.person_id;
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';
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 = p_asgid
AND paa.payroll_action_id = pact.payroll_action_id
--AND pact.payroll_id = l_payroll_id --commented for testing
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 p_start_year
AND p_end_year;
SELECT assignment_id
FROM pay_assignment_actions
WHERE assignment_action_id = c_aggr_max_act_id;
SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;
SELECT effective_date,
business_group_id,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PRE_PAY_ID'),1,20) pre_pay_id,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYROLL_ID'),1,20) payroll_id,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
SUBSTR(pay_gb_eoy_archive.get_parameter (legislative_parameters, 'FIRST_FPS'), 1,1) first_fps
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT
ppa.effective_date
FROM
pay_payroll_actions ppa
WHERE
ppa.payroll_action_id = pre_pay_id;
SELECT effective_date
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 petf.element_type_id,
input_value_id
FROM pay_element_types_f petf,
pay_input_values_f pivf
WHERE petf.element_name = 'NI'
AND petf.element_type_id = pivf.element_type_id
AND pivf.name = 'Process Type'
AND petf.legislation_code = 'GB'
AND pivf.legislation_code = 'GB'
AND l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
AND l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date;
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', 'SMP Total',
'SAP Total', 'SPP Adoption Total', 'SPP Birth Total',
'ASPP Adoption Total', 'ASPP Birth Total', 'Widows and Orphans'))
OR(d.database_item_suffix = '_PER_TD_YTD'
AND b.balance_name IN ('Taxable Pay', 'Student Loan', 'SMP Total',
'SAP Total', 'SPP Adoption Total', 'SPP Birth Total',
'ASPP Adoption Total', 'ASPP Birth Total', 'Widows and Orphans'))
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 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 asg.assignment_id,
asg.EFFECTIVE_START_DATE asg_eff_start_date,
asg.person_id,
asg.assignment_number,
asg.payroll_id
FROM per_all_assignments_f asg,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_action_id = c_asg_act_id
AND paa.assignment_id = asg.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND (ppa.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
OR asg.effective_end_date BETWEEN g_start_year AND g_end_year)
AND asg.effective_start_date = (SELECT max(asg1.effective_start_date)
FROM per_all_assignments_f asg1
WHERE asg1.assignment_id = asg.assignment_id
AND asg1.effective_start_date <= g_end_year);
SELECT trim(NVL(asg.primary_flag,'N')) asg_primary_flag,
trim(NVL(pap.per_information9,'N')) per_ni_agg_flag,
trim(NVL(pap.per_information10,'N')) per_paye_agg_flag,
asg.payroll_id payroll_id
FROM per_all_people_f pap,
per_all_assignments_f asg,
pay_assignment_actions paa
WHERE paa.assignment_action_id = c_asg_act_id
AND paa.assignment_id = asg.assignment_id
AND pap.person_id = asg.person_id
-- and asg.business_group_id = l_business_group_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
AND pap.per_information_category = 'GB';
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 = p_asgid
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.payroll_id = l_payroll_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 p_start_year
AND p_end_year;
SELECT
--to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL_ID')) payroll_id,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
effective_date eff_date,
business_group_id bg_id,
pact.payroll_action_id pactid
FROM pay_payroll_actions pact,
pay_assignment_actions paa
WHERE paa.payroll_action_id = pact.payroll_action_id
AND paa.assignment_action_id = p_assactid;
SELECT paa.assignment_id
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = c_asg_act_id;
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
where paaf1.assignment_id = c_asg_id
and paaf1.person_id = paaf2.person_id
and paaf2.assignment_status_type_id in(select ASSIGNMENT_STATUS_TYPE_ID from per_assignment_status_types past where past.LEGISLATION_CODE is null and past.BUSINESS_GROUP_ID is null
and PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN')
and paei.assignment_id = paaf2.assignment_id
and paei.AEI_INFORMATION_CATEGORY = 'GB_RTI_AGGREGATION'
and paei. AEI_INFORMATION1 = 'Y'
order by 2 desc;
SELECT count(distinct act.assignment_action_id),
max(act.assignment_action_id) assignment_action_id
FROM pay_payroll_actions ppa, --Current pactid
pay_payroll_actions appa, --Payroll Run
pay_payroll_actions appa2, --Prepayments
pay_assignment_actions act, --Payroll Run
pay_assignment_actions act1, --Prepayments
pay_action_interlocks pai, --Prepayments
per_all_assignments_f as1
WHERE as1.person_id = p_person_id --45885
AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
AND act.payroll_action_id = appa.payroll_action_id
AND act.source_action_id IS NULL
AND as1.assignment_id = act.assignment_id
AND ppa.effective_date BETWEEN as1.effective_start_date AND as1.effective_end_date
AND act.action_status = 'C'
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status = 'C'
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
AND appa2.payroll_action_id = p_pre_pact_id;
SELECT
/*+ USE_NL(paa, pact, ptp) */
to_number(SUBSTR(MAX(lpad(paa.action_sequence,15,'0')
|| paa.assignment_action_id),16)) max_asg_act_id,
MAX(pact.effective_date) effective_date
FROM pay_assignment_actions paa,
per_all_assignments_f paaf,
pay_payroll_actions pact,
per_time_periods ptp
WHERE paa.assignment_id = paaf.assignment_id
AND paaf.person_id = c_person_id --41650
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 c_start_date AND c_end_date; --'06-APR-2012' AND '05-APR-2013'
SELECT asg.assignment_number
FROM per_all_assignments_f asg,
pay_assignment_actions paa
WHERE paa.assignment_action_id = c_asg_act_id
AND paa.assignment_id = asg.assignment_id
AND c_cur_last_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
insert_archive_row(p_assactid, p_effective_date,l_archive_tab);
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 , ppapre.EFFECTIVE_DATE
from pay_payrolls_f ppf,pay_payroll_actions ppapre,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_ID'),1,20)
and ppapre.payroll_action_id = SUBSTR(pay_gb_eoy_archive.get_parameter(ppa.legislative_parameters,'PRE_PAY_ID'),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 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,
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')
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 <= l_effective_date
)
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 <= l_effective_date
)
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 DISTINCT line_text
FROM pay_message_lines
WHERE source_id = asg_action_id
AND message_level = 'W'
AND payroll_id = 100;
select EFFECTIVE_DATE
from pay_payroll_actions
where payroll_action_id = p_pact_id;
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 ('GB RTI P60 PAYROLL DET');
l_stmt := 'select '|| p_column_name ||' from pay_people_groups pp where pp.people_group_id = :PEOPLE_GRP_ID';