[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT assignment_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE
paa.assignment_action_id = c_assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
SELECT /*+ USE_NL(paa, ppa) */
fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE
paa.assignment_id = c_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.source_action_id is null
AND ppa.effective_date <= c_effective_date
AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
SELECT /*+ USE_NL(paa, ppa) */
fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE
paa.assignment_id = c_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.source_action_id is not null
AND ppa.effective_date <= c_effective_date
AND ppa.action_type in ('R', 'Q')
--AND paa.action_status = 'C'
AND paa.source_action_id = c_master_asg_action_id ;
select pdb.defined_balance_id from
pay_defined_balances pdb,
pay_balance_dimensions pbd
where pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.database_item_suffix = c_db_item_suffix
and pdb.balance_type_id = c_balance_type_id;
SELECT ppa.effective_date
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_action_id = p_assignment_action_id;
SELECT
effective_start_date
INTO
l_period_from_date
FROM
ff_globals_f
WHERE global_name = l_global_name
AND business_group_id = p_business_group_id
AND p_user_date BETWEEN effective_start_date AND effective_end_date;
select nvl(min(P.effective_start_date)
,fnd_date.canonical_to_date('4712/12/31'))
into l_event_from_date
from per_people_f p, /* should this be all ? */
per_assignments_f ass
where p.per_information2 = 'Y'
and ASS.person_id = P.person_id
and P.effective_start_date <= p_end_date
and p.effective_end_date >= p_start_date
and p_end_date between
ass.effective_start_date and ass.effective_end_date
and ass.assignment_id = p_assignment_id ;
SELECT ptp.end_date, ppa.effective_date
FROM per_time_periods ptp,
pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE
paa.assignment_action_id = c_assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.time_period_id = ptp.time_period_id;
SELECT value, assignment_id
from pay_assignment_latest_balances
Where assignment_action_id = c_asg_action_id
and defined_balance_id = c_defined_balance_id;
SELECT
BAL_ASSACT.assignment_id,
BAL_ASSACT.action_sequence,
BACT.effective_date,
PTP.regular_payment_date,
BACT.business_group_id
FROM
pay_assignment_actions BAL_ASSACT,
pay_payroll_actions BACT,
per_time_periods PTP
WHERE
BAL_ASSACT.assignment_action_id = p_assignment_action_id
AND PTP.time_period_id = BACT.time_period_id
AND BACT.payroll_action_id = BAL_ASSACT.payroll_action_id;
SELECT
DB.balance_type_id,
DIM.dimension_name,
DIM.database_item_suffix ,
DIM.legislation_code,
DIM.business_group_id
FROM
pay_defined_balances DB,
pay_balance_dimensions DIM
WHERE DB.defined_balance_id = p_defined_balance_id
AND DIM.balance_dimension_id = DB.balance_dimension_id;
select palb.value
from pay_assignment_latest_balances palb,
pay_balance_context_values pbcv
where pbcv.context_id = c_source_id
and pbcv.latest_balance_id = palb.latest_balance_id
and palb.assignment_action_id = c_assignment_action_id
and palb.defined_balance_id = c_defined_bal_id;
SELECT CONTEXT_ID
FROM FF_CONTEXTS
where context_name ='SOURCE_TEXT';
select pdb.balance_type_id,
pbd.dimension_name
from pay_balance_dimensions pbd,
pay_defined_balances pdb
where pdb.defined_balance_id = c_defined_balance_id
and pdb.balance_dimension_id = pbd.balance_dimension_id;
select pdb.balance_type_id,
pbd.dimension_name,
pbd.route_id
from pay_balance_dimensions pbd,
pay_defined_balances pdb
where pdb.defined_balance_id = c_defined_balance_id
and pdb.balance_dimension_id = pbd.balance_dimension_id;
select pca.context_value,
ffc.context_name
from pay_action_contexts pca,
ff_contexts ffc,
ff_route_context_usages frc,
pay_balance_dimensions pbd
where pbd.route_id = p_route_id
and pbd.route_id = frc.route_id
and frc.context_id = ffc.context_id
and ffc.context_id = pca.context_id
and pca.assignment_action_id = p_act_id
and (ffc.context_name = p_context_01 OR ffc.context_name = p_context_02)
and (ffc.context_name <> 'SOURCE_TEXT'
or (ffc.context_name = 'SOURCE_TEXT' AND
exists ( select 1
from pay_run_results rr,
pay_run_result_values rrv,
pay_input_values_f piv,
pay_element_types_f petf
where rr.assignment_action_id = pca.assignment_action_id
and rr.element_type_id = petf.element_type_id
and rr.run_result_id = rrv.run_result_id
and piv.input_value_id = rrv.input_value_id
and piv.name = 'Reference'
and nvl(rrv.result_value, 'Unknown') = pca.context_value
and petf.element_name IN
(
'CAO Scotland', 'CAO Scotland NTPP', 'CMA Scotland', 'CMA Scotland NTPP', 'Court Order',
'Court Order NTPP', 'Court Order Non Priority', 'Court Order Non Priority NTPP',
'EAS Scotland', 'EAS Scotland NTPP', 'Setup Court Order Balance'
)
)
)
);
SELECT ptp.end_date, ppa.effective_date
FROM per_time_periods ptp,
pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE
paa.assignment_action_id = c_assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.time_period_id = ptp.time_period_id;
SELECT ptp.end_date, ppa.effective_date
FROM per_time_periods ptp,
pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE
paa.assignment_action_id = c_assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.time_period_id = ptp.time_period_id;
SELECT ptp.end_date, ppa.effective_date
FROM per_time_periods ptp,
pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE
paa.assignment_action_id = c_assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.time_period_id = ptp.time_period_id;
SELECT ptp.end_date, ppa.effective_date
FROM per_time_periods ptp,
pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE
paa.assignment_action_id = c_assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.time_period_id = ptp.time_period_id;
SELECT /*+ ORDERED INDEX (ASSACT PAY_ASSIGNMENT_ACTIONS_N51,
PACT PAY_PAYROLL_ACTIONS_PK,
FEED PAY_BALANCE_FEEDS_F_UK2,
PPTP PER_TIME_PERIODS_PK,
RR PAY_RUN_RESULTS_N50,
TARGET PAY_RUN_RESULT_VALUES_PK) */
NVL(SUM(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0)
INTO
l_balance
FROM
pay_assignment_actions ASSACT
,pay_payroll_actions PACT
,pay_balance_feeds_f FEED
,per_time_periods PPTP
,pay_run_results RR
,pay_run_result_values TARGET
WHERE
FEED.balance_type_id = P_BALANCE_TYPE_ID
AND FEED.input_value_id = TARGET.input_value_id
AND TARGET.run_result_id = RR.run_result_id
AND RR.assignment_action_id = ASSACT.assignment_action_id
AND ASSACT.payroll_action_id = PACT.payroll_action_id
AND nvl(TARGET.result_value,'0') <> '0'
AND PACT.effective_date BETWEEN
FEED.effective_start_date AND FEED.effective_end_date
AND RR.status IN ('P','PA')
AND PACT.time_period_id = PPTP.time_period_id
AND PPTP.regular_payment_date >= P_PERIOD_FROM_DATE
AND PACT.effective_date >= P_EVENT_FROM_DATE
AND PACT.effective_date <= P_TO_DATE
AND ASSACT.action_sequence <= NVL(P_ACTION_SEQUENCE,ASSACT.action_sequence)
AND ASSACT.assignment_id = P_ASSIGNMENT_ID;
SELECT
pay_balance_dimensions_s.NEXTVAL
INTO
l_bal_next
FROM DUAL;
INSERT INTO FF_ROUTES
(
route_id,
route_name,
user_defined_flag,
description,
text
)
VALUES
(
ff_routes_s.NEXTVAL,
'ROUTE_NAME_' || ff_routes_s.CURRVAL ,
'N',
'User balance dimension for '||
UPPER(RPAD(p_suffix,30,' ')) || 'USER-REG ASG '||
p_start_dd_mm || ' RESET'|| TO_CHAR(p_frequency,'00'),
l_route_text
);
INSERT INTO ff_route_context_usages
(
route_id,
context_id,
sequence_no
)
SELECT
ff_routes_s.CURRVAL,
context_id,
1
FROM
ff_contexts
WHERE
context_name = 'ASSIGNMENT_ACTION_ID';
INSERT INTO ff_route_parameters
(
route_parameter_id,
route_id,
sequence_no,
parameter_name,
data_type
)
VALUES
(
ff_route_parameters_s.NEXTVAL,
ff_routes_s.CURRVAL,
1,
'BALANCE TYPE ID',
'N'
);
INSERT INTO pay_balance_dimensions
(
balance_dimension_id,
business_group_id,
legislation_code,
route_id,
database_item_suffix,
dimension_name,
dimension_type,
description,
feed_checking_code,
legislation_subgroup,
payments_flag,
expiry_checking_code,
expiry_checking_level,
feed_checking_type
)
VALUES
(
pay_balance_dimensions_s.CURRVAL,
p_business_group_id,
NULL,
ff_routes_s.CURRVAL,
p_suffix,
l_dim_name,
l_dim_type,
'User dimension defined by Request Id ' || l_req_id,
NULL,
NULL,
'N',
'hr_gbbal.check_expiry',
'P',
NULL
);
SELECT prrv.result_value
FROM pay_run_result_values prrv,
pay_run_results prr,
pay_input_values iv
WHERE prr.run_result_id = c_run_result_id
AND prr.run_result_id = prrv.run_result_id
AND iv.name = 'Reference'
AND iv.input_value_id = prrv.input_value_id;
SELECT prr.source_id
FROM pay_run_results prr
WHERE prr.run_result_id = c_run_result_id;
SELECT max(paa.assignment_action_id)
FROM pay_assignment_actions paa
WHERE
paa.source_action_id = p_assignment_action_id
AND paa.source_action_id is not null;
SELECT value
from pay_assignment_latest_balances
Where assignment_action_id = c_asg_action_id
and defined_balance_id = c_defined_balance_id;
select distinct pel.element_type_id element_type_id,
nvl(ent.original_entry_id, ent.element_entry_id) source_id
from pay_element_entries_f ent,
pay_element_links_f pel,
pay_user_rows_f urows,
pay_payroll_actions bact,
per_time_periods bptp,
pay_assignment_actions bassact
where bassact.assignment_action_id = p_assignment_action_id
and UROWS.user_table_id = g_ni_cat_indicator_table_id
and fnd_number.canonical_to_number(UROWS.ROW_LOW_RANGE_OR_NAME) = PEL.ELEMENT_TYPE_ID
and g_start_of_year between
UROWS.effective_start_date and UROWS.effective_end_date
and bact.payroll_action_id = bassact.payroll_action_id
and bptp.time_period_id = bact.time_period_id
and ent.assignment_id = bassact.assignment_id
and ent.effective_end_date >= g_start_of_year
and ent.effective_start_date <= bptp.end_date
and ent.element_link_id = pel.element_link_id
and pel.business_group_id + 0 = bact.business_group_id
and ent.effective_end_date between
pel.effective_start_date and pel.effective_end_date;
select max(decode(PRR.element_type_id,g_ni_a_element_type_id,1,0))
, max(decode(PRR.element_type_id,g_ni_b_element_type_id,1,0))
, max(decode(PRR.element_type_id,g_ni_c_element_type_id,1,0))
, max(decode(PRR.element_type_id,g_ni_d_element_type_id,1,0))
, max(decode(PRR.element_type_id,g_ni_e_element_type_id,1,0))
, max(decode(PRR.element_type_id,g_ni_f_element_type_id,1,0))
, max(decode(PRR.element_type_id,g_ni_g_element_type_id,1,0))
, max(decode(PRR.element_type_id,g_ni_j_element_type_id,1,0))
, max(decode(PRR.element_type_id,g_ni_l_element_type_id,1,0))
, max(decode(PRR.element_type_id,g_ni_s_element_type_id,1,0))
from
PAY_RUN_RESULTS PRR
, PER_TIME_PERIODS PPTP
, PAY_PAYROLL_ACTIONS PACT
, PAY_ASSIGNMENT_ACTIONS ASSACT
, PAY_ASSIGNMENT_ACTIONS BASSACT
where PRR.source_id = p_source_id
and PRR.source_type = 'I'
AND PACT.PAYROLL_ACTION_ID = ASSACT.PAYROLL_ACTION_ID
AND PACT.ACTION_TYPE <> 'I'
AND PPTP.TIME_PERIOD_ID = PACT.TIME_PERIOD_ID
AND PPTP.regular_payment_date >= g_start_of_year
AND BASSACT.ASSIGNMENT_ACTION_ID = p_assignment_action_id
AND ASSACT.ACTION_SEQUENCE <= BASSACT.ACTION_SEQUENCE
AND ASSACT.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND ASSACT.ASSIGNMENT_ID = BASSACT.ASSIGNMENT_ID;
select max(decode(FEED.balance_type_id,g_ni_a_id,1,g_ni_a_able_id,1,0))
, max(decode(FEED.balance_type_id,g_ni_b_id,1,g_ni_b_able_id,1,0))
, max(decode(FEED.balance_type_id,g_ni_c_id,1,g_ni_c_able_id,1,0))
, max(decode(FEED.balance_type_id,g_ni_d_id,1,g_ni_d_able_id,1,0))
, max(decode(FEED.balance_type_id,g_ni_e_id,1,g_ni_e_able_id,1,0))
, max(decode(FEED.balance_type_id,g_ni_f_id,1,g_ni_f_able_id,1,0))
, max(decode(FEED.balance_type_id,g_ni_g_id,1,g_ni_g_able_id,1,0))
, max(decode(FEED.balance_type_id,g_ni_j_id,1,g_ni_j_able_id,1,0))
, max(decode(FEED.balance_type_id,g_ni_l_id,1,g_ni_l_able_id,1,0))
, max(decode(FEED.balance_type_id,g_ni_s_id,1,g_ni_s_able_id,1,0))
from
PAY_BALANCE_FEEDS_F FEED
, PAY_RUN_RESULT_VALUES PRRV
, PAY_RUN_RESULTS PRR
, PER_TIME_PERIODS PPTP
, PAY_PAYROLL_ACTIONS PACT
, PAY_ASSIGNMENT_ACTIONS ASSACT
, PAY_ASSIGNMENT_ACTIONS BASSACT
WHERE FEED.balance_type_id in (
g_ni_a_id, g_ni_a_able_id
,g_ni_b_id, g_ni_b_able_id
,g_ni_c_id, g_ni_c_able_id
,g_ni_d_id, g_ni_d_able_id
,g_ni_e_id, g_ni_e_able_id
,g_ni_f_id, g_ni_f_able_id
,g_ni_g_id, g_ni_g_able_id
,g_ni_j_id, g_ni_j_able_id
,g_ni_l_id, g_ni_l_able_id
,g_ni_s_id, g_ni_s_able_id
)
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PACT.PAYROLL_ACTION_ID = ASSACT.PAYROLL_ACTION_ID
AND PACT.action_type in ('I',g_action_typer,g_action_typeq,g_action_typeb)
AND PPTP.TIME_PERIOD_ID = PACT.TIME_PERIOD_ID
AND PPTP.regular_payment_date >= g_start_of_year
AND BASSACT.ASSIGNMENT_ACTION_ID = p_assignment_action_id
AND PRRV.RESULT_VALUE IS NOT NULL
AND PRRV.RESULT_VALUE <> '0'
AND PPTP.regular_payment_date is not null
AND FEED.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PACT.effective_date between
FEED.effective_start_date and FEED.effective_end_date
AND ASSACT.ACTION_SEQUENCE <= BASSACT.ACTION_SEQUENCE
AND ASSACT.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND ASSACT.ASSIGNMENT_ID = BASSACT.ASSIGNMENT_ID;
select 1
from per_time_periods ptp
where ptp.regular_payment_date >= g_start_of_year
and ptp.time_period_id in
(
select
null
from pay_payroll_actions pact
where pact.action_type = 'I'
);
select paa.assignment_id,
paa.action_sequence,
ppa.effective_date
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_action_id = c_assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id;
select user_table_id
into g_ni_cat_indicator_table_id
from pay_user_tables
where user_table_name = 'NI_CATEGORY_INDICATOR_ELEMENTS'
and legislation_code = 'GB';
select max(decode(balance_name, 'NI A Total',balance_type_id,0))
,max(decode(balance_name, 'NI A Able' ,balance_type_id,0))
,max(decode(balance_name, 'NI B Total',balance_type_id,0))
,max(decode(balance_name, 'NI B Able' ,balance_type_id,0))
,max(decode(balance_name, 'NI C Total',balance_type_id,0))
,max(decode(balance_name, 'NI C Able' ,balance_type_id,0))
,max(decode(balance_name, 'NI D Total',balance_type_id,0))
,max(decode(balance_name, 'NI D Able' ,balance_type_id,0))
,max(decode(balance_name, 'NI E Total',balance_type_id,0))
,max(decode(balance_name, 'NI E Able' ,balance_type_id,0))
,max(decode(balance_name, 'NI F Total',balance_type_id,0))
,max(decode(balance_name, 'NI F Able' ,balance_type_id,0))
,max(decode(balance_name, 'NI G Total',balance_type_id,0))
,max(decode(balance_name, 'NI G Able' ,balance_type_id,0))
,max(decode(balance_name, 'NI J Total',balance_type_id,0))
,max(decode(balance_name, 'NI J Able' ,balance_type_id,0))
,max(decode(balance_name, 'NI L Total',balance_type_id,0))
,max(decode(balance_name, 'NI L Able' ,balance_type_id,0))
,max(decode(balance_name, 'NI S Total',balance_type_id,0))
,max(decode(balance_name, 'NI S Able' ,balance_type_id,0))
into
g_ni_a_id, g_ni_a_able_id
,g_ni_b_id, g_ni_b_able_id
,g_ni_c_id, g_ni_c_able_id
,g_ni_d_id, g_ni_d_able_id
,g_ni_e_id, g_ni_e_able_id
,g_ni_f_id, g_ni_f_able_id
,g_ni_g_id, g_ni_g_able_id
,g_ni_j_id, g_ni_j_able_id
,g_ni_l_id, g_ni_l_able_id
,g_ni_s_id, g_ni_s_able_id
from pay_balance_types
where 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 F Total', 'NI F Able'
,'NI G Total', 'NI G Able'
,'NI J Total', 'NI J Able'
,'NI L Total', 'NI L Able'
,'NI S Total', 'NI S Able'
)
and legislation_code = 'GB';
select
max(ptp.regular_payment_date)
,max(decode(e.element_name,'NI',e.element_type_id,0))
,max(decode(e.element_name,'NI A',e.element_type_id,0))
,max(decode(e.element_name,'NI B',e.element_type_id,0))
,max(decode(e.element_name,'NI C',e.element_type_id,0))
,max(decode(e.element_name,'NI D',e.element_type_id,0))
,max(decode(e.element_name,'NI E',e.element_type_id,0))
,max(decode(e.element_name,'NI F',e.element_type_id,0))
,max(decode(e.element_name,'NI G',e.element_type_id,0))
,max(decode(e.element_name,'NI J Deferment',e.element_type_id,0))
,max(decode(e.element_name,'NI L Deferment',e.element_type_id,0))
,max(decode(e.element_name,'NI S',e.element_type_id,0))
into
l_regular_payment_date
,g_ni_element_type_id
,g_ni_a_element_type_id
,g_ni_b_element_type_id
,g_ni_c_element_type_id
,g_ni_d_element_type_id
,g_ni_e_element_type_id
,g_ni_f_element_type_id
,g_ni_g_element_type_id
,g_ni_j_element_type_id
,g_ni_l_element_type_id
,g_ni_s_element_type_id
from pay_element_types_f e,
per_time_periods ptp,
pay_payroll_actions bact,
pay_assignment_actions bassact
where element_name in ( 'NI'
,'NI A'
,'NI B'
,'NI C'
,'NI D'
,'NI E'
,'NI F'
,'NI G'
,'NI J Deferment'
,'NI L Deferment'
,'NI S')
and e.legislation_code = 'GB'
and bassact.assignment_action_id = p_assignment_action_id_child -- bug fix 4088228
and bassact.payroll_action_id = bact.payroll_action_id
and ptp.time_period_id = bact.time_period_id
and bact.date_earned between
e.effective_start_date and e.effective_end_date;
select balance_dimension_id
into g_asg_td_ytd
from pay_balance_dimensions
where dimension_name = '_ASG_TD_YTD';
select max(decode(balance_type_id,g_ni_a_id,defined_balance_id,0))
,max(decode(balance_type_id,g_ni_a_able_id,defined_balance_id,0))
,max(decode(balance_type_id,g_ni_b_id,defined_balance_id,0))
,max(decode(balance_type_id,g_ni_b_able_id,defined_balance_id,0))
,max(decode(balance_type_id,g_ni_c_id,defined_balance_id,0))
,max(decode(balance_type_id,g_ni_c_able_id,defined_balance_id,0))
,max(decode(balance_type_id,g_ni_d_id,defined_balance_id,0))
,max(decode(balance_type_id,g_ni_d_able_id,defined_balance_id,0))
,max(decode(balance_type_id,g_ni_e_id,defined_balance_id,0))
,max(decode(balance_type_id,g_ni_e_able_id,defined_balance_id,0))
,max(decode(balance_type_id,g_ni_f_id,defined_balance_id,0))
,max(decode(balance_type_id,g_ni_f_able_id,defined_balance_id,0))
,max(decode(balance_type_id,g_ni_g_id,defined_balance_id,0))
,max(decode(balance_type_id,g_ni_g_able_id,defined_balance_id,0))
,max(decode(balance_type_id,g_ni_j_id,defined_balance_id,0))
,max(decode(balance_type_id,g_ni_j_able_id,defined_balance_id,0))
,max(decode(balance_type_id,g_ni_l_id,defined_balance_id,0))
,max(decode(balance_type_id,g_ni_l_able_id,defined_balance_id,0))
,max(decode(balance_type_id,g_ni_s_id,defined_balance_id,0))
,max(decode(balance_type_id,g_ni_s_able_id,defined_balance_id,0))
into
g_ni_a_defbal_id,
g_ni_a_able_defbal_id,
g_ni_b_defbal_id,
g_ni_b_able_defbal_id,
g_ni_c_defbal_id,
g_ni_c_able_defbal_id,
g_ni_d_defbal_id,
g_ni_d_able_defbal_id,
g_ni_e_defbal_id,
g_ni_e_able_defbal_id,
g_ni_f_defbal_id,
g_ni_f_able_defbal_id,
g_ni_g_defbal_id,
g_ni_g_able_defbal_id,
g_ni_j_defbal_id,
g_ni_j_able_defbal_id,
g_ni_l_defbal_id,
g_ni_l_able_defbal_id,
g_ni_s_defbal_id,
g_ni_s_able_defbal_id
from pay_defined_balances
where balance_dimension_id = g_asg_td_ytd
and balance_type_id in
(g_ni_a_id, g_ni_a_able_id
,g_ni_b_id, g_ni_b_able_id
,g_ni_c_id, g_ni_c_able_id
,g_ni_d_id, g_ni_d_able_id
,g_ni_e_id, g_ni_e_able_id
,g_ni_f_id, g_ni_f_able_id
,g_ni_g_id, g_ni_g_able_id
,g_ni_j_id, g_ni_j_able_id
,g_ni_l_id, g_ni_l_able_id
,g_ni_s_id, g_ni_s_able_id)
and legislation_code = 'GB'
and business_group_id is null;
select nvl(assact.source_action_id, assact.assignment_action_id)
into l_action_id
from pay_assignment_actions assact
where assact.assignment_action_id = p_action_id;