[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
selects for setting NI Balance on primary
asgmts for multiple assignments
asnell 08-May-1997 Bug 492246 - Ensure tax reference transfers are
also payroll transfers
Defaulted Taxcode from element if no run exists
Speeded up latest PAYE action fetch
aparkes 11-Jul-1997 Added updates to ye_assignments for employee
Address,Start_of_emp.
Added TITLE to extract from per_people_f
Used cursor for SCON NIF, G and S balances.
amills 16-Jan-1998 Bug 572938. Added a new update statement
'Retrieve element entry update recurring'
for update of pay_gb_year_end_assignments
where an element entry exists that is the
result of an update recurring rule. The
logic is altered to check for this rule
first before locating run result value,
then defaulting to element (non- U.R.R.)
value as before.
aparkes 19-Jan-1998 Used GB Balance direct call package hr_dirbal
for balance fetching.
arundell 30-Mar-1998 Fixed bug 639910. Adjusted the way the last
assignment_action_id, previous_year_asg_action_id
and the last tax_run_result_id is derived, so
that payroll reversals are included.
hanand 16-Apr-1998 Fixed byg 656417. Included cases with
termination_type of 'L' for reporting NI_Y_LAST
asnell 23-Apr-1998 Fixed bug 660289. Only create SCON balance
entry if results were non zero
asnell 29-Apr-1998 Fixed bug 662438. If SCON entry value doesn't
exist on the date find the nearest match
hanand 04-Jun-1998 Fixed bug 678573. Changed the cursor
get_scon_bal to include 'Employer' balances.
Setting 'Total' to 'Employer' balance for
category 'S' and all other balances to 0.
amyers 110.11 06-NOV-1998 Fixed bug 715534. Added status checks to table
PAY_RUN_RESULTS in updates after balances have
been fetched.
amyers 110.12 23-FEB-1999 Fixed bug 818887. Aggregate rows by scon and
category; takes care of situation where more
SELECT COUNT(*)
INTO l_count
FROM
pay_gb_year_end_payrolls pay,
pay_gb_year_end_assignments ass,
pay_gb_year_end_values val
WHERE
pay.payroll_id = ass.payroll_id
AND ass.assignment_id = val.assignment_id
AND pay.business_group_id = p_business_group_id
AND pay.tax_year = p_year
AND pay.permit_number = NVL(p_permit,pay.permit_number)
AND pay.tax_reference_number = NVL(SUBSTR(p_tax_district_ref,4,8), pay.tax_reference_number)
AND pay.tax_district_reference = NVL(FND_NUMBER.CANONICAL_TO_NUMBER(SUBSTR(p_tax_district_ref,1,3)), pay.tax_district_reference);
SELECT scon.screen_entry_value
FROM
pay_element_entry_values_f scon,
pay_element_entry_values_f cat
WHERE
scon.element_entry_id = p_element_entry_id
and cat.element_entry_id = p_element_entry_id
and cat.effective_start_date = scon.effective_start_date
and cat.effective_end_date = scon.effective_end_date
and scon.input_value_id +0 = g_scon_input_id
and cat.input_value_id +0 = g_category_input_id
and scon.screen_entry_value is not null
and scon.effective_end_date >= p_effective_date
order by decode(cat.screen_entry_value,p_category,0,1),
scon.effective_end_date ;
select element_type_id into g_ni_id from
pay_element_types_f where element_name = 'NI'
and p_effective_date between
effective_start_date and effective_end_date;
select input_value_id into g_category_input_id from
pay_input_values_f
where name = 'Category'
and element_type_id = g_ni_id
and p_effective_date between
effective_start_date and effective_end_date;
select input_value_id into g_scon_input_id from
pay_input_values_f
where name = 'SCON'
and element_type_id = g_ni_id
and p_effective_date between
effective_start_date and effective_end_date;
clear down year end tables for selected permit
NOTES
*/
procedure trash(p_permit in varchar2 ,
p_business_group_id in number,
p_tax_district_ref in varchar2,
p_year in number) is
-- housekeeping procedure called pre extract to delete previously extracted
-- data
--
-- delete all the rows in values for this permit , if no permit specified
-- delete all rows within the business group
l_tax_district_reference pay_gb_year_end_payrolls.tax_district_reference%TYPE;
delete from pay_gb_year_end_values v where exists (
select '1' from pay_gb_year_end_assignments ye_asg,
pay_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 p.business_group_id = p_business_group_id
and org.organization_id = p_business_group_id
and nvl(p_permit,substr(flex.segment10,1,12)) =
substr(flex.segment10,1,12)
and nvl(l_tax_district_reference,substr(flex.segment1,1,3)) =
substr(flex.segment1,1,3)
and nvl(l_tax_reference_number,
substr(ltrim(substr(org_information1,4,8),'/') ,1,7)) =
substr(ltrim(substr(org_information1,4,8),'/') ,1,7)
and l_end_year between
p.effective_start_date and p.effective_end_date
and ye_asg.payroll_id = p.payroll_id
and v.assignment_id = ye_asg.assignment_id
and v.effective_end_date = ye_asg.effective_end_date);
plog ( '_ value rows deleted '||to_char(SQL%ROWCOUNT));
delete from pay_gb_year_end_assignments ye_asg where exists (
select '1' from pay_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 p.business_group_id = p_business_group_id
and org.organization_id = p_business_group_id
and nvl(p_permit,substr(flex.segment10,1,12)) =
substr(flex.segment10,1,12)
and nvl(l_tax_district_reference,substr(flex.segment1,1,3)) =
substr(flex.segment1,1,3)
and nvl(l_tax_reference_number,
substr(ltrim(substr(org_information1,4,8),'/') ,1,7)) =
substr(ltrim(substr(org_information1,4,8),'/') ,1,7)
and l_end_year between
p.effective_start_date and p.effective_end_date
and ye_asg.payroll_id = p.payroll_id);
plog ( '_ assignment rows deleted '||to_char(SQL%ROWCOUNT));
delete from pay_gb_year_end_payrolls ye_roll
where exists ( select '1' from pay_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 p.business_group_id = p_business_group_id
and org.organization_id = p_business_group_id
and nvl(p_permit,substr(flex.segment10,1,12)) =
substr(flex.segment10,1,12)
and nvl(l_tax_district_reference,substr(flex.segment1,1,3)) =
substr(flex.segment1,1,3)
and nvl(l_tax_reference_number,
substr(ltrim(substr(org_information1,4,8),'/') ,1,7)) =
substr(ltrim(substr(org_information1,4,8),'/') ,1,7)
and l_end_year between
p.effective_start_date and p.effective_end_date
and ye_roll.payroll_id = p.payroll_id);
plog ( '_ payroll rows deleted '||to_char(SQL%ROWCOUNT));
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_id, effective_end_date, sex, payroll_id,
previous_year_asg_action_id, last_asg_action_id, termination_type
from pay_gb_year_end_assignments
where request_id = p_request_id;
select ye_asg.rowid ye_asg_rowid
from pay_gb_year_end_assignments ye_asg
, pay_gb_year_end_payrolls ye_roll
where exists ( select person_id
from per_assignments_f asg,
pay_gb_year_end_payrolls yep2
where yep2.payroll_id = ye_asg.payroll_id
and yep2.TAX_REFERENCE_NUMBER = ye_roll.TAX_REFERENCE_NUMBER
and yep2.PERMIT_NUMBER <> ye_roll.PERMIT_NUMBER
and asg.effective_start_date < ye_roll.end_year
and asg.effective_end_date >= ye_roll.START_YEAR
and ye_asg.person_id = asg.person_id)
and MULTIPLE_ASG_FLAG is not null
and ye_asg.payroll_id = ye_roll.payroll_id
and request_id = p_request_id;
select assignment_id, effective_end_date, sex, payroll_id,
previous_year_asg_action_id, last_asg_action_id, termination_type
from pay_gb_year_end_assignments
where request_id = p_request_id
and eoy_primary_flag = 'Y';
select yea_prim.assignment_id s_asg_id,
yea_prim.effective_end_date s_end_date,
yev.ni_category_code s_ni_cat_code,
'M',
sum(yev.EARNINGS) s_earnings,
sum(yev.TOTAL_CONTRIBUTIONS) s_tot_con,
sum(yev.EMPLOYEES_CONTRIBUTIONS) s_ees_con,
sum(yev.EARNINGS_CONTRACTED_OUT) s_earnings_co,
sum(yev.CONTRIBUTIONS_CONTRACTED_OUT) s_con_co
from pay_gb_year_end_assignments yea_prim,
pay_gb_year_end_assignments ye_asg,
pay_gb_year_end_values yev,
pay_gb_year_end_payrolls yep_prim,
pay_gb_year_end_payrolls ye_roll
where yea_prim.eoy_primary_flag = 'Y'
and ye_asg.person_id = yea_prim.person_id
and yea_prim.payroll_id = yep_prim.payroll_id
and ye_asg.payroll_id = ye_roll.payroll_id
and ye_asg.assignment_id = yev.assignment_id
and ye_asg.effective_end_date = yev.effective_end_date
and yep_prim.tax_reference_number = ye_roll.tax_reference_number
and (yev.total_contributions <> 0 or yev.ni_category_code = 'X')
and yea_prim.assignment_id = l_asg_id
and yea_prim.request_id = p_request_id
group by yea_prim.assignment_id,
yea_prim.effective_end_date,
yev.ni_category_code;
select substr(bal.balance_name,4,1) cat_code,
substr(HR_GENERAL.DECODE_LOOKUP('GB_SCON',decode(substr(bal.balance_name,4,1),
'F',nvl(max(EV_SCON.screen_entry_value),
pay_year_end_extract.get_nearest_scon(
max(EV_SCON.element_entry_id),'F',max(PACT.effective_date))),
'G',nvl(max(EV_SCON.screen_entry_value),
pay_year_end_extract.get_nearest_scon(
max(EV_SCON.element_entry_id),'G',max(PACT.effective_date))),
'S',nvl(max(EV_SCON.screen_entry_value),
pay_year_end_extract.get_nearest_scon(
max(EV_SCON.element_entry_id),'S',max(PACT.effective_date))),
null)),1,9) scon,
100*nvl(sum(decode(substr(bal.balance_name,6),'Able',
fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0) able,
100*nvl(sum(decode(substr(bal.balance_name,6),'Total',
fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0) Total,
100*nvl(sum(decode(substr(bal.balance_name,6),'Employee',
fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0) Employee,
--
-- Bug Fix 678573 Start
--
100*nvl(sum(decode(substr(bal.balance_name,6),'Employer',
fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0) Employer,
--
-- Bug Fix 678573 End
--
100*nvl(sum(decode(substr(bal.balance_name,6),'CO Able',
fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0) CO_able,
100*nvl(sum(decode(substr(bal.balance_name,6),'CO',
fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0) CO
from pay_balance_feeds_f FEED
,pay_balance_types BAL
,pay_run_result_values TARGET
,pay_run_results RR
,pay_element_entry_values_f EV_SCON
,pay_element_entries_f E_NI
,pay_element_links_f EL_NI
,pay_payroll_actions PACT
,pay_assignment_actions ASSACT
,pay_payroll_actions BACT
,per_time_periods BPTP
,per_time_periods PPTP
,pay_assignment_actions BAL_ASSACT
where BAL_ASSACT.assignment_action_id = cp_l_asg_id
and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
and FEED.balance_type_id = BAL.balance_type_id
and BAL.balance_name like 'NI%'
and substr(BAL.balance_name,4,1) in ('F','G','S')
and FEED.input_value_id = TARGET.input_value_id
and TARGET.run_result_id = RR.run_result_id
and nvl(TARGET.result_value,'0') <> '0'
and RR.assignment_action_id = ASSACT.assignment_action_id
and E_NI.assignment_id = BAL_ASSACT.assignment_id
and EV_SCON.input_value_id +
decode(EV_SCON.element_entry_id,null,0,0) = cp_inp_val
and EV_SCON.element_entry_id = E_NI.element_entry_id
and EL_NI.element_link_id = E_NI.element_link_id
and EL_NI.element_type_id = cp_element_type
and PACT.effective_date between
E_NI.effective_start_date and E_NI.effective_end_date
and PACT.effective_date between
EL_NI.effective_start_date and EL_NI.effective_end_date
and PACT.effective_date between
EV_SCON.effective_start_date and EV_SCON.effective_end_date
and ASSACT.payroll_action_id = PACT.payroll_action_id
and PACT.effective_date between
FEED.effective_start_date and FEED.effective_end_date
and RR.status in ('P','PA')
and BPTP.time_period_id = BACT.time_period_id
and PPTP.time_period_id = PACT.time_period_id
and PPTP.regular_payment_date >= /* fin year start */
( to_date('06-04-' || to_char( to_number(
to_char( BPTP.regular_payment_date,'YYYY'))
+ decode(sign( BPTP.regular_payment_date - to_date('06-04-'
|| to_char(BPTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
-1,-1,0)),'DD-MM-YYYY'))
and PACT.effective_date >=
/* find the latest td payroll transfer date - compare each of the */
/* assignment rows with its predecessor looking for the payroll */
/* that had a different tax district at that date */
( select nvl(max(ASS.effective_start_date),
fnd_date.canonical_to_date('01-01-0001'))
from per_assignments_f ASS
,pay_payrolls_f NROLL
,hr_soft_coding_keyflex FLEX
,per_assignments_f PASS /* previous assignment */
,pay_payrolls_f PROLL
,hr_soft_coding_keyflex PFLEX
where ASS.assignment_id = BAL_ASSACT.assignment_id
and NROLL.payroll_id = ASS.payroll_id
and ASS.effective_start_date between
NROLL.effective_start_date and NROLL.effective_end_date
and NROLL.soft_coding_keyflex_id = FLEX.soft_coding_keyflex_id
and ASS.assignment_id = PASS.assignment_id
and PASS.effective_end_date = (ASS.effective_start_date - 1)
and ASS.effective_start_date <= BACT.effective_date
and PROLL.payroll_id = PASS.payroll_id
and ASS.effective_start_date between
PROLL.effective_start_date and PROLL.effective_end_date
and PROLL.soft_coding_keyflex_id = PFLEX.soft_coding_keyflex_id
and ASS.payroll_id <> PASS.payroll_id
and FLEX.segment1 <> PFLEX.segment1
)
and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
and ASSACT.assignment_id = BAL_ASSACT.assignment_id
group by EV_SCON.screen_entry_value, substr(bal.balance_name,4,1)
order by EV_SCON.screen_entry_value, substr(bal.balance_name,4,1);
plog ( '_ Delete previous extracted rows for this selection ');
select element_type_id into l_paye_details_id from
pay_element_types_f where element_name = 'PAYE Details'
and l_end_year between
effective_start_date and effective_end_date;
select 10 * pay_run_results_s.nextval
into l_max_run_result_id
from dual;
select element_type_id into l_ni_id from
pay_element_types_f where element_name = 'NI'
and l_end_year between
effective_start_date and effective_end_date;
select input_value_id into l_category_input_id from
pay_input_values_f
where name = 'Category'
and element_type_id = l_ni_id
and l_end_year between
effective_start_date and effective_end_date;
select input_value_id into l_scon_input_id from
pay_input_values_f
where name = 'SCON'
and element_type_id = l_ni_id
and l_end_year between
effective_start_date and effective_end_date;
begin -- ( insert pay_gb_year_end_payrolls
hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',4);
insert into pay_gb_year_end_payrolls
( BUSINESS_GROUP_ID,
PAYROLL_ID,
PERMIT_NUMBER,
PAYROLL_NAME,
TAX_DISTRICT_REFERENCE,
TAX_REFERENCE_NUMBER ,
TAX_DISTRICT_NAME ,
TAX_YEAR,
EMPLOYERS_NAME,
EMPLOYERS_ADDRESS_LINE,
ECON ,
SMP_RECOVERED,
SMP_COMPENSATION,
SSP_RECOVERED )
select p.business_group_id business_group_id,
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,8),'/') ,1,7) TAX_REFERENCE,
substr(org.org_information2 ,1,40) TAX_DISTRICT_NAME,
p_year TAX_YEAR,
substr(org.org_information3,1,36) EMPLOYERS_NAME,
substr(org.org_information4,1,60) EMPLOYERS_ADDRESS_LINE,
substr(org.org_information7,1,9) ECON,
flex.segment11 * 100 SMP_RECOVERED,
flex.segment12 * 100 SMP_COMPENSATION,
flex.segment13 * 100 SSP_RECOVERED
from pay_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 p.business_group_id = p_business_group_id
and org.organization_id = p_business_group_id
and l_end_year between
p.effective_start_date and p.effective_end_date
and not exists ( select null from pay_gb_year_end_payrolls ye_roll
where ye_roll.payroll_id = p.payroll_id ) ;
update pay_gb_year_end_payrolls ye_roll
set ( START_YEAR, END_YEAR ,PERIOD_TYPE, MAX_PERIOD_NUMBER) =
( select min(start_date), max(end_date), max(PERIOD_TYPE), max(PERIOD_NUM)
from per_time_periods ptp
where PTP.payroll_id = ye_roll.payroll_id
and PTP.regular_payment_date between
l_start_year and l_end_year);
update pay_gb_year_end_payrolls ye_roll
set ( START_PREVIOUS_YEAR, END_PREVIOUS_YEAR ) =
( select min(start_date), max(end_date) from per_time_periods ptp
where PTP.payroll_id = ye_roll.payroll_id
and PTP.regular_payment_date between
add_months(l_start_year,-12)
and add_months(l_end_year,-12));
end; -- ) insert pay_gb_year_end_payrolls
begin -- ( insert assignments
-- select all the assignments for a particular permit
-- note we only want the last date effective row - the permit on the
-- payroll for this dictates where it is reported even if the assignment
-- has been on more than one payroll in the year. The exception to this
-- is where tax district/reference transfers have occurred
-- find the latest assignment row this payroll year
-- add any assignment rows that are for tax reference changes
-- pick up latest effective end date and latest payroll
-- don't pick up null permits and if ni y is not reportable only pick up
-- current year assignments
--
--
--
hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',12);
insert into pay_gb_year_end_assignments (
assignment_id,
payroll_id,
effective_end_date,
request_id ,
extract_date,
assignment_number,
person_id,
organization_id,
location_id,
people_group_id)
select ass.assignment_id,
ass.payroll_id,
ass.effective_end_date,
p_request_id,
sysdate ,
ass.assignment_number,
ass.person_id,
ass.organization_id,
ass.location_id,
ass.people_group_id
from per_assignments_f ASS,
pay_gb_year_end_payrolls ye_roll
where ASS.payroll_id = ye_roll.payroll_id
/* 2 years scan to pick up NI Y last year - note we don't need to
worry about transfers for NI Y as it uses the STAT_YTD dimension */
and ASS.effective_end_date >=
decode(p_niy,'N', ye_roll.START_YEAR,
nvl(ye_roll.START_PREVIOUS_YEAR,ye_roll.START_YEAR))
and ASS.effective_start_date <= ye_roll.END_YEAR
and not exists ( select 1 from per_assignments_f ass2,
pay_gb_year_end_payrolls ye_roll2
where ass.assignment_id = ass2.assignment_id
and ass2.payroll_id = ye_roll2.payroll_id
and ass2.effective_end_date > ass.effective_end_date
and ass2.effective_end_date >=
decode(p_niy,'N', ye_roll2.START_YEAR,
nvl(ye_roll2.START_PREVIOUS_YEAR,ye_roll2.START_YEAR))
and ass2.effective_start_date <= ye_roll2.END_YEAR)
and exists
( select yep2.payroll_id from pay_gb_year_end_payrolls yep2
where ass.payroll_id = yep2.payroll_id
and yep2.permit_number = nvl(p_permit,nvl(yep2.permit_number,'x'))
and nvl(l_tax_district_reference,yep2.tax_district_reference) =
yep2.tax_district_reference
and nvl(l_tax_reference_number,yep2.tax_reference_number) =
yep2.tax_reference_number
and yep2.business_group_id = p_business_group_id);
insert into pay_gb_year_end_assignments (
assignment_id,
payroll_id,
effective_end_date,
request_id ,
extract_date,
termination_date,
termination_type,
assignment_number,
person_id,
organization_id,
location_id,
people_group_id)
select pass.assignment_id,
pass.payroll_id,
pass.effective_end_date,
p_request_id,
sysdate,
pass.effective_end_date termination_date,
'R' termination_type,
ass.assignment_number,
ass.person_id,
ass.organization_id,
ass.location_id,
ass.people_group_id
from per_assignments_f ASS
,pay_payrolls_f NROLL
,hr_soft_coding_keyflex FLEX
,per_assignments_f PASS
,pay_payrolls_f PROLL
,hr_soft_coding_keyflex PFLEX
,pay_gb_year_end_payrolls ye_roll
where NROLL.payroll_id = ASS.payroll_id
and ye_roll.payroll_id = PASS.payroll_id
and ASS.effective_start_date between
NROLL.effective_start_date and NROLL.effective_end_date
and NROLL.soft_coding_keyflex_id = FLEX.soft_coding_keyflex_id
and ASS.assignment_id = PASS.assignment_id
and PASS.effective_end_date = (ASS.effective_start_date - 1)
and PASS.effective_end_date >= ye_roll.START_YEAR
and PASS.effective_start_date <= ye_roll.END_YEAR
and PROLL.payroll_id = PASS.payroll_id
and ASS.effective_start_date between
PROLL.effective_start_date and PROLL.effective_end_date
and PROLL.soft_coding_keyflex_id = PFLEX.soft_coding_keyflex_id
and ASS.payroll_id <> PASS.payroll_id
and FLEX.segment1 <> PFLEX.segment1
and not exists ( select '1' from pay_gb_year_end_assignments ye_asg
where ye_asg.assignment_id = pass.assignment_id
and ye_asg.effective_end_date = pass.effective_end_date )
and exists ( select null from pay_gb_year_end_payrolls ye_roll
where ye_roll.payroll_id = pass.payroll_id
and ye_roll.permit_number = nvl(p_permit,ye_roll.permit_number)
and nvl(l_tax_district_reference,ye_roll.tax_district_reference) =
ye_roll.tax_district_reference
and nvl(l_tax_reference_number,ye_roll.tax_reference_number) =
ye_roll.tax_reference_number
and ye_roll.business_group_id = p_business_group_id);
update pay_gb_year_end_assignments ye_asg set ( effective_start_date ) =
(select max(effective_end_date) + 1 from pay_gb_year_end_assignments yea2
where yea2.assignment_id = ye_asg.assignment_id
and yea2.effective_end_date < ye_asg.effective_end_date )
where ye_asg.request_id = p_request_id;
on the payroll is not updated mid year? If so the following is simplified */
hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',16);
update pay_gb_year_end_assignments ye_asg set ( effective_start_date ) =
( select max(ASS.effective_start_date)
from per_assignments_f ASS
,pay_payrolls_f NROLL
,hr_soft_coding_keyflex FLEX
,per_assignments_f PASS
,pay_payrolls_f PROLL
,hr_soft_coding_keyflex PFLEX
,pay_gb_year_end_payrolls ye_roll
where ass.assignment_id = ye_asg.assignment_id
and ass.effective_start_date < ye_asg.effective_end_date
and NROLL.payroll_id = ASS.payroll_id
and ye_roll.payroll_id = PASS.payroll_id
and ASS.effective_start_date between
NROLL.effective_start_date and NROLL.effective_end_date
and NROLL.soft_coding_keyflex_id = FLEX.soft_coding_keyflex_id
and ASS.assignment_id = PASS.assignment_id
and PASS.effective_end_date = (ASS.effective_start_date - 1)
and PASS.effective_end_date >= ye_roll.START_YEAR
and PASS.effective_start_date <= ye_roll.END_YEAR
and PROLL.payroll_id = PASS.payroll_id
and PASS.payroll_id <> ASS.payroll_id
and ASS.effective_start_date between
PROLL.effective_start_date and PROLL.effective_end_date
and PROLL.soft_coding_keyflex_id = PFLEX.soft_coding_keyflex_id
and FLEX.segment1 <> PFLEX.segment1 )
where ye_asg.request_id = p_request_id
and ye_asg.effective_start_date is null;
UPDATE PAY_GB_YEAR_END_ASSIGNMENTS ye_asg
SET (LAST_ASG_ACTION_ID, LAST_EFFECTIVE_DATE) = (
select assact.assignment_action_id,
pact.effective_date
from pay_payroll_actions pact,
pay_assignment_actions assact
where assact.payroll_action_id = pact.payroll_action_id
and ye_asg.assignment_id = assact.assignment_id
and ye_asg.request_id = p_request_id
and assact.action_sequence =
(
select max(assact2.action_sequence)
from pay_assignment_actions assact2,
pay_payroll_actions pact2,
pay_gb_year_end_payrolls ye_roll
where assact2.assignment_id = ye_asg.assignment_id
and assact2.payroll_action_id = pact2.payroll_action_id
and pact2.payroll_id = ye_roll.payroll_id
and pact2.action_type in ( 'Q','R','B','I')
and assact2.action_status = 'C'
and pact2.effective_date <= ye_asg.effective_end_date
and pact2.effective_date between
nvl(ye_asg.effective_start_date,ye_roll.START_YEAR)
and ye_roll.END_YEAR
and not exists(
select '1'
from pay_action_interlocks pai,
pay_assignment_actions assact3,
pay_payroll_actions pact3
where pai.locked_action_id = assact2.assignment_action_id
and pai.locking_action_id = assact3.assignment_action_id
and pact3.payroll_action_id = assact3.payroll_action_id
and pact3.action_type = 'V'
and assact3.action_status = 'C'))
)
WHERE ye_asg.request_id = p_request_id;
update pay_gb_year_end_assignments ye_asg set
( LAST_NAME, FIRST_NAME, MIDDLE_NAME, DATE_OF_BIRTH, TITLE,
EXPENSE_CHECK_SEND_TO_ADDRESS, NATIONAL_INSURANCE_NUMBER, SEX,
PENSIONER_INDICATOR, MULTIPLE_ASG_FLAG) =
( select substr(last_name, 1,20) LAST_NAME,
substr(FIRST_NAME, 1,16) FIRST_NAME,
substr(MIDDLE_NAMES,1,7) MIDDLE_NAMES,
DATE_OF_BIRTH, TITLE,
substr(EXPENSE_CHECK_SEND_TO_ADDRESS,1,1) EXPENSE_CHECK_SENT_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_INFORMATION9,'Y','Y',null) MULTIPLE_ASG_FLAG -- MII
from per_people_f per
where per.person_id = ye_asg.person_id
and ye_asg.request_id = p_request_id
and l_end_year between
per.effective_start_date and per.effective_end_date )
where ye_asg.request_id = p_request_id;
update pay_gb_year_end_assignments ye_asg set ( DIRECTOR_INDICATOR ) =
( select 'D' from dual where exists ( select '1' from
per_people_f per
where ye_asg.person_id = per.person_id
and per.effective_start_date <= l_end_year
and per.effective_end_date >= l_start_year
and substr(PER_INFORMATION2,1,1) = 'Y'))
where ye_asg.request_id = p_request_id;
update pay_gb_year_end_assignments ye_asg
set ( termination_date, termination_type ) =
(select actual_termination_date ,'L'
from per_periods_of_service pos,
per_assignments_f asg,
pay_gb_year_end_payrolls ye_roll
where pos.person_id = ye_asg.person_id
and ye_asg.assignment_id = asg.assignment_id
and ye_asg.effective_end_date = asg.effective_end_date
and asg.period_of_service_id = pos.period_of_service_id
and ye_asg.payroll_id = ye_roll.payroll_id
and pos.actual_termination_date is not null
and nvl(pos.LAST_STANDARD_PROCESS_DATE,pos.actual_termination_date)
between
nvl(ye_asg.effective_start_date,
nvl(ye_roll.start_previous_year,ye_roll.start_year))
and least(ye_asg.effective_end_date,ye_roll.end_year))
where ye_asg.request_id = p_request_id
and exists ( select 1
from per_periods_of_service pos,
per_assignments_f asg,
pay_gb_year_end_payrolls ye_roll
where pos.person_id = ye_asg.person_id
and ye_asg.assignment_id = asg.assignment_id
and ye_asg.effective_end_date = asg.effective_end_date
and asg.period_of_service_id = pos.period_of_service_id
and ye_asg.payroll_id = ye_roll.payroll_id
and pos.actual_termination_date is not null
and nvl(pos.LAST_STANDARD_PROCESS_DATE,pos.actual_termination_date)
between
nvl(ye_asg.effective_start_date,
nvl(ye_roll.start_previous_year,ye_roll.start_year))
and least(ye_asg.effective_end_date,ye_roll.end_year))
;
update pay_gb_year_end_assignments ye_asg
set (start_of_emp) =
(select pos.date_start
from per_periods_of_service pos,
per_assignments_f asg,
pay_gb_year_end_payrolls ye_roll
where pos.person_id = ye_asg.person_id
and ye_asg.assignment_id = asg.assignment_id
and ye_asg.effective_end_date = asg.effective_end_date
and asg.period_of_service_id = pos.period_of_service_id
and ye_asg.payroll_id = ye_roll.payroll_id
and pos.date_start between l_start_year and l_end_year)
where ye_asg.request_id = p_request_id
and exists ( select 1
from per_periods_of_service pos,
per_assignments_f asg,
pay_gb_year_end_payrolls ye_roll
where pos.person_id = ye_asg.person_id
and ye_asg.assignment_id = asg.assignment_id
and ye_asg.effective_end_date = asg.effective_end_date
and asg.period_of_service_id = pos.period_of_service_id
and ye_asg.payroll_id = ye_roll.payroll_id
and pos.date_start between l_start_year and l_end_year)
;
update pay_gb_year_end_assignments ye_asg
set (address_line1, address_line2, address_line3, town_or_city,
county, postal_code) = (
select pad.address_line1,
pad.address_line2,
pad.address_line3,
pad.town_or_city,
SUBSTR(l.meaning,1,27) county,
SUBSTR(pad.postal_code,1,8)
from per_addresses pad,
hr_lookups l
where pad.person_id = ye_asg.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))
where ye_asg.request_id = p_request_id;
UPDATE PAY_GB_YEAR_END_ASSIGNMENTS ye_asg
SET (PREVIOUS_YEAR_ASG_ACTION_ID, PREVIOUS_YEAR_EFFECTIVE_DATE) = (
select assact.assignment_action_id,
pact.effective_date
from pay_payroll_actions pact,
pay_assignment_actions assact
where assact.payroll_action_id = pact.payroll_action_id
and ye_asg.assignment_id = assact.assignment_id
and ye_asg.request_id = p_request_id
and assact.action_sequence =
(
select max(assact2.action_sequence)
from pay_assignment_actions assact2,
pay_payroll_actions pact2,
pay_gb_year_end_payrolls ye_roll
where assact2.assignment_id = ye_asg.assignment_id
and assact2.payroll_action_id = pact2.payroll_action_id
and pact2.payroll_id = ye_roll.payroll_id
and pact2.action_type in ( 'Q','R','B','I')
and assact2.action_status = 'C'
and pact2.effective_date <= ye_asg.effective_end_date
and pact2.effective_date between
nvl(ye_asg.effective_start_date,ye_roll.START_PREVIOUS_YEAR)
and ye_roll.END_PREVIOUS_YEAR
and not exists(
select '1'
from pay_action_interlocks pai,
pay_assignment_actions assact3,
pay_payroll_actions pact3
where pai.locked_action_id = assact2.assignment_action_id
and pai.locking_action_id = assact3.assignment_action_id
and pact3.payroll_action_id = assact3.payroll_action_id
and pact3.action_type = 'V'
and assact3.action_status = 'C'))
)
WHERE ye_asg.request_id = p_request_id;
end; -- ) insert people
begin -- ( insert balances
hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',28);
l_count_values := 0; -- count the number of rows inserted into values for each
delete from pay_gb_year_end_assignments
where assignment_id = l_people.assignment_id
and effective_end_date = l_people.effective_end_date;
insert into pay_gb_year_end_values
(ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE,REPORTABLE,
TOTAL_CONTRIBUTIONS)
values (
l_people.assignment_id,
l_people.effective_end_date,
'Y' ,
'Y',
l_niy );
delete from pay_gb_year_end_assignments
where assignment_id = l_people.assignment_id
and effective_end_date = l_people.effective_end_date;
insert into pay_gb_year_end_values
(ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE, REPORTABLE,
EARNINGS,TOTAL_CONTRIBUTIONS,EMPLOYEES_CONTRIBUTIONS)
values (
l_people.assignment_id,
l_people.effective_end_date,
'A' ,'Y',
l_ni_able,
l_ni_tot,
l_ni_ees );
insert into pay_gb_year_end_values
(ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE, REPORTABLE,
EARNINGS,TOTAL_CONTRIBUTIONS,EMPLOYEES_CONTRIBUTIONS)
values (
l_people.assignment_id,
l_people.effective_end_date,
'B' ,'Y',
l_ni_able,
l_ni_tot,
l_ni_ees );
insert into pay_gb_year_end_values
(ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE, REPORTABLE,
TOTAL_CONTRIBUTIONS)
values (
l_people.assignment_id,
l_people.effective_end_date,
'C' ,'Y',
l_ni_tot);
insert into pay_gb_year_end_values
(ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE, REPORTABLE,
EARNINGS,TOTAL_CONTRIBUTIONS,EMPLOYEES_CONTRIBUTIONS,
EARNINGS_CONTRACTED_OUT,CONTRIBUTIONS_CONTRACTED_OUT)
values (
l_people.assignment_id,
l_people.effective_end_date,
'D' ,'Y',
l_ni_able,
l_ni_tot,
l_ni_ees,
l_ni_co_able,
l_ni_co );
insert into pay_gb_year_end_values
(ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE, REPORTABLE,
EARNINGS,TOTAL_CONTRIBUTIONS,EMPLOYEES_CONTRIBUTIONS,
EARNINGS_CONTRACTED_OUT,CONTRIBUTIONS_CONTRACTED_OUT)
values (
l_people.assignment_id,
l_people.effective_end_date,
'E' ,'Y',
l_ni_able,
l_ni_tot,
l_ni_ees,
l_ni_co_able,
l_ni_co );
insert into pay_gb_year_end_values
(ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE, REPORTABLE,
EARNINGS,TOTAL_CONTRIBUTIONS,EMPLOYEES_CONTRIBUTIONS,
EARNINGS_CONTRACTED_OUT,CONTRIBUTIONS_CONTRACTED_OUT,SCON)
values (
l_people.assignment_id,
l_people.effective_end_date,
wk_cat_code,
'Y',
wk_able,
wk_Total,
wk_Employee,
wk_CO_able,
wk_CO,
wk_scon);
l_count_values := l_count_values + 1; /*one row inserted*/
insert into pay_gb_year_end_values
(ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE, REPORTABLE,
EARNINGS,TOTAL_CONTRIBUTIONS,EMPLOYEES_CONTRIBUTIONS,
EARNINGS_CONTRACTED_OUT,CONTRIBUTIONS_CONTRACTED_OUT,SCON)
values (
l_people.assignment_id,
l_people.effective_end_date,
wk_cat_code,
'Y',
wk_able,
wk_Total,
wk_Employee,
wk_CO_able,
wk_CO,
wk_scon);
insert into pay_gb_year_end_values
(
ASSIGNMENT_ID,
EFFECTIVE_END_DATE,
NI_CATEGORY_CODE,
REPORTABLE,
TOTAL_CONTRIBUTIONS
)
values
(
l_people.assignment_id,
l_people.effective_end_date,
'P',
'Y',
l_nip
);
insert into pay_gb_year_end_values
(ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE,REPORTABLE)
select l_people.assignment_id,
l_people.effective_end_date,
SCREEN_ENTRY_VALUE,
'Y'
from pay_element_entries_f e,
pay_element_entry_values_f v,
pay_gb_year_end_payrolls ye_roll,
pay_element_links_f link
where e.assignment_id = l_people.assignment_id
and v.input_value_id + 0 = l_category_input_id
and link.element_type_id = l_ni_id
and e.element_link_id = link.element_link_id
and e.element_entry_id = v.element_entry_id
and l_people.payroll_id = ye_roll.payroll_id
and least(l_people.effective_end_date,ye_roll.end_year)
between link.effective_start_date and link.effective_end_date
and least(l_people.effective_end_date,ye_roll.end_year)
between e.effective_start_date and e.effective_end_date
and least(l_people.effective_end_date,ye_roll.end_year)
between v.effective_start_date and v.effective_end_date;
update pay_gb_year_end_assignments ye_asg set
SSP = l_ssp,
SMP = l_smp,
GROSS_PAY = l_gross,
TAX_PAID = l_paye,
SUPERANNUATION_PAID = l_super,
WIDOWS_AND_ORPHANS = l_widow,
TAXABLE_PAY = l_taxable
where assignment_id = l_people.assignment_id
and effective_end_date = l_people.effective_end_date;
end; -- ) insert balances
begin -- ( update non bal info
/* most people will have had paye calculated on the last run - pick these up */
hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',30);
update pay_gb_year_end_assignments ye_asg set ( TAX_RUN_RESULT_ID ) =
(select RUN_RESULT_ID from pay_run_results r
where r.element_type_id = l_paye_details_id
and r.status in ('P', 'PA')
and r.assignment_action_id = ye_asg.LAST_ASG_ACTION_ID )
where ye_asg.request_id = p_request_id;
/* if there are any who have no tax update find the latest update */
hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',32);
update pay_gb_year_end_assignments ye_asg set ( TAX_RUN_RESULT_ID ) =
(select r.RUN_RESULT_ID
from pay_assignment_actions assact,
pay_run_results r
where r.element_type_id+0 = l_paye_details_id + decode(assact.assignment_id,null,0,0)
and r.assignment_action_id = assact.assignment_action_id
and r.status in ('P', 'PA')
and assact.assignment_id = ye_asg.assignment_id
and assact.action_sequence = (
select max(assact2.action_sequence)
from pay_assignment_actions assact2,
pay_payroll_actions pact,
pay_gb_year_end_payrolls ye_roll
where assact2.assignment_id = ye_asg.assignment_id
and ye_roll.payroll_id = pact.payroll_id
and pact.payroll_action_id = assact2.payroll_action_id
and pact.effective_date between ye_roll.start_year and ye_roll.end_year
and ye_asg.tax_run_result_id is null
and ye_asg.last_asg_action_id is not null /*run this year */
and pact.effective_date <= ye_asg.LAST_EFFECTIVE_DATE
)
)
where ye_asg.TAX_RUN_RESULT_ID is null
and ye_asg.last_asg_action_id is not null /* there has been a run this year */
and ye_asg.request_id = p_request_id;
hr_utility.trace( 'Retrieve element entry update recurring');
update pay_gb_year_end_assignments ye_asg set
( TAX_CODE, W1_M1_INDICATOR,PREVIOUS_TAXABLE_PAY,PREVIOUS_TAX_PAID) =
(select max(decode(iv.name,'Tax Code',SCREEN_ENTRY_VALUE,null)) Tax_code,
max(decode(iv.name,'Tax Basis',SCREEN_ENTRY_VALUE,null)) Tax_Basis,
100 * fnd_number.canonical_to_number(max(decode(iv.name,'Pay Previous',
SCREEN_ENTRY_VALUE,null))) Pay_previous,
100 * fnd_number.canonical_to_number(max(decode(iv.name,'Tax Previous',
SCREEN_ENTRY_VALUE,null))) Tax_previous
from pay_element_entries_f e,
pay_element_entry_values_f v,
pay_gb_year_end_payrolls ye_roll,
pay_input_values_f iv,
pay_element_links_f link
where e.assignment_id = ye_asg.assignment_id
and link.element_type_id = l_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 ye_asg.payroll_id = ye_roll.payroll_id
and e.updating_action_id is not null
and least(ye_asg.effective_end_date,ye_roll.end_year)
between link.effective_start_date and link.effective_end_date
and least(ye_asg.effective_end_date,ye_roll.end_year)
between e.effective_start_date and e.effective_end_date
and least(ye_asg.effective_end_date,ye_roll.end_year)
between iv.effective_start_date and iv.effective_end_date
and least(ye_asg.effective_end_date,ye_roll.end_year)
between v.effective_start_date and v.effective_end_date)
where ye_asg.request_id = p_request_id;
hr_utility.trace( 'update taxcode with last taxcode used' );
update pay_gb_year_end_assignments ye_asg set
( TAX_CODE, W1_M1_INDICATOR,PREVIOUS_TAXABLE_PAY,PREVIOUS_TAX_PAID) =
(select max(decode(name,'Tax Code',result_value,null)) Tax_code,
max(decode(name,'Tax Basis',result_value,null)) Tax_Basis,
100 * fnd_number.canonical_to_number(max(decode(name,'Pay Previous',result_value,null))) Pay_previous,
100 * fnd_number.canonical_to_number(max(decode(name,'Tax Previous',result_value,null))) Tax_previous
from pay_input_values_f v,
pay_run_result_values rrv
where rrv.RUN_RESULT_ID = ye_asg.TAX_RUN_RESULT_ID
and v.INPUT_VALUE_ID = rrv.INPUT_VALUE_ID
and v.element_type_id = l_paye_details_id )
where ye_asg.tax_code is null
and ye_asg.request_id = p_request_id;
update pay_gb_year_end_assignments ye_asg set
( TAX_CODE, W1_M1_INDICATOR,PREVIOUS_TAXABLE_PAY,PREVIOUS_TAX_PAID) =
(select max(decode(iv.name,'Tax Code',SCREEN_ENTRY_VALUE,null)) Tax_code,
max(decode(iv.name,'Tax Basis',SCREEN_ENTRY_VALUE,null)) Tax_Basis,
100 * fnd_number.canonical_to_number(max(decode(iv.name,'Pay Previous',
SCREEN_ENTRY_VALUE,null))) Pay_previous,
100 * fnd_number.canonical_to_number(max(decode(iv.name,'Tax Previous',
SCREEN_ENTRY_VALUE,null))) Tax_previous
from pay_element_entries_f e,
pay_element_entry_values_f v,
pay_gb_year_end_payrolls ye_roll,
pay_input_values_f iv,
pay_element_links_f link
where e.assignment_id = ye_asg.assignment_id
and link.element_type_id = l_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 ye_asg.payroll_id = ye_roll.payroll_id
and least(ye_asg.effective_end_date,ye_roll.end_year)
between link.effective_start_date and link.effective_end_date
and least(ye_asg.effective_end_date,ye_roll.end_year)
between e.effective_start_date and e.effective_end_date
and least(ye_asg.effective_end_date,ye_roll.end_year)
between iv.effective_start_date and iv.effective_end_date
and least(ye_asg.effective_end_date,ye_roll.end_year)
between v.effective_start_date and v.effective_end_date)
where ye_asg.tax_code is null
and ye_asg.request_id = p_request_id;
update pay_gb_year_end_assignments ye_asg set
( WEEK_53_INDICATOR, W1_M1_INDICATOR, TAX_REFUND, TAX_PAID,
SUPERANNUATION_REFUND, SUPERANNUATION_PAID, TAX_CODE ) =
( select
decode(ye_roll.MAX_PERIOD_NUMBER,53,'3',54,'4',56,'6',' ') WEEK_53_INDICATOR,
decode(ye_asg.W1_M1_INDICATOR,'C',' ',decode(ptpt.NUMBER_PER_FISCAL_YEAR,
1,'M',2,'M',4,'M',6,'M',12,'M',24,'M','W')) W1_M1_INDICATOR,
decode(sign(ye_asg.TAX_PAID),-1,'R',' ') TAX_REFUND,
ye_asg.TAX_PAID * sign(ye_asg.TAX_PAID) TAX_PAID,
decode(sign(ye_asg.SUPERANNUATION_PAID),-1,'R',' ') SUPERANNUATION_REFUND,
ye_asg.SUPERANNUATION_PAID *
sign(ye_asg.SUPERANNUATION_PAID) SUPERANNUATION_PAID,
nvl(ye_asg.TAX_CODE,'NI') TAX_CODE
from pay_gb_year_end_payrolls ye_roll,
per_time_period_types ptpt
where ye_roll.payroll_id = ye_asg.payroll_id
and ye_roll.period_type = ptpt.period_type
and ye_asg.request_id = p_request_id )
where ye_asg.request_id = p_request_id;
select substr('EMPNO:'||ASSIGNMENT_NUMBER||
' '||LAST_NAME||
' has multiple assignments on more than one permit',1,132) mess
into l_error_text
from pay_gb_year_end_assignments
where rowid = l_invalid_masg.ye_asg_rowid
and rownum = 1;
update pay_gb_year_end_assignments pp
set eoy_primary_flag = 'Y' where pp.rowid = ( select
substr(max(lpad(gross_pay,9,'0')||p.rowid),-18)
from pay_gb_year_end_assignments p,
pay_gb_year_end_payrolls ye_roll,
pay_gb_year_end_payrolls yep2
where p.person_id = pp.person_id
and ye_roll.payroll_id = p.payroll_id
and yep2.payroll_id = pp.payroll_id
and ye_roll.tax_reference_number = yep2.tax_reference_number
and p.MULTIPLE_ASG_FLAG is not null )
and pp.MULTIPLE_ASG_FLAG is not null
and pp.request_id = p_request_id;
insert into pay_gb_year_end_values
(ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE,REPORTABLE,
EARNINGS,TOTAL_CONTRIBUTIONS,EMPLOYEES_CONTRIBUTIONS,
EARNINGS_CONTRACTED_OUT,CONTRIBUTIONS_CONTRACTED_OUT)
values (l_asg_details.s_asg_id, l_asg_details.s_end_date,
l_asg_details.s_ni_cat_code,
'M' , l_asg_details.s_earnings,
l_asg_details.s_tot_con, l_asg_details.s_ees_con,
l_asg_details.s_earnings_co, l_asg_details.s_con_co
);
update pay_gb_year_end_values yev set REPORTABLE = 'N'
where reportable = 'Y'
and exists ( select '1' from pay_gb_year_end_assignments ye_asg
where ye_asg.MULTIPLE_ASG_FLAG is not null
and ye_asg.assignment_id = yev.assignment_id
and ye_asg.effective_end_date = yev.effective_end_date
and ye_asg.request_id = p_request_id );
insert into pay_gb_year_end_values
( ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE,REPORTABLE)
select ye_asg.assignment_id, ye_asg.effective_end_date ,'X','Y'
from pay_gb_year_end_assignments ye_asg
where not exists ( select '1' from pay_gb_year_end_values yev
where ye_asg.assignment_id = yev.assignment_id
and ye_asg.effective_end_date = yev.effective_end_date
and yev.reportable <> 'N')
and ye_asg.request_id = p_request_id;
end; -- ) update non bal info