[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Modified transfer_batch() to update
multiple batch lines for a payroll
action.
Modified the select statement in
load_latest_asg_balances() to use
paa.action_sequence instead of ppa.
115.28 J.Hobbs 11-OCT-2002 Added logic to be able to process International
Payroll supported dimensions. Made changes to
dim_expiry_date()
validate_dimension()
get_current_value()
validate_batch()
115.27 N.Bristow 09-APR-2002 Changed code to allow balances to be loaded
historically.
115.26 M.Reid 04-FEB-2002 2211591: Corrected source_text parameter
115.25 D.Saxby 18-DEC-2001 GSCC standards fix.
115.24 D.Saxby 17-DEC-2001 Bug 2153245 - changes for Purge.
o New global data structure member:
purge_mode.
o Changed lock_batch to init purge_mode
as appropriate and reset the
BALANCE_ROLLUP to TRANSFER mode.
o Changed transfer_batch and
undo_transfer_batch to perform
commit based on new purge_mode.
o Don't error if upload detects an
assignment has been previously
processed and we are purging.
o Call bal_adjust_actions in purge
mode when appropriate.
o Added dbdrv line.
o Added commit at end of file.
115.23 A.Logue 25-JUN-2001 Performance changes to dim_expiry_date.
115.22 A.Logue 22-JUN-2001 Performance changes to
load_latest_asg_balances
including hints.
115.21 SuSivasu 20-JUN-2001 Re-arranged the parameter call
to the which_context function.
115.20 A.Logue 09-MAY-2001 Added some CBO hints. Bug 1763446.
115.19 SuSivasu 06-APR-2001 Added two SOURCE_ID and SOURCE_TEXT
contexts to the batch balance upload
tables.
115.18 JARTHURT 04-JAN-2001 Removed hard-coded calls to
pay_ca_bal_upload. These calls are now
performed dynamically using the new
functionality added in 115.17
115.17 N.Bristow 29-SEP-2000 Changes for Singapore, now passing
tax unit id to balance adjustments,
also passing batch_line_id to
include_adjustment.
115.16 A.Logue 13-JAN-2000 Ensure that error messages fetched from
hr_utility.get_message are of max length 240
to fit into pay_message_lines.
115.14 A.Logue 07-OCT-1999 Pass batch_line_status to dim_expiry_date so that
it does not call the legislative expiry_date
procedure if the line is to be discarded (ie not 'V').
This should give an improvement of performance.
115.13 A.Logue 06-OCT-1999 Put call to dim_expiry_date back into
csr_batch_line_transfer. Can do this as the
procedure does not have to be pragmatised in 11i
and hence can be called from the cursor (nb it
contains dynamic sql). Thus can remove the insert
sort implemented as part of 730491. This
should give an improvement of performance.
115.12 A.Logue 18-MAY-1999 Change dbms_output to hr_utility.trace.
115.11 A.Logue 14-MAY-1999 Order by line_id on line fetch.
115.9 T.Battoo 20-APR-1999 setting the previous value for
latest balances - this code had been
deleted for some reason.
115.8 A.Logue 15-APR-1999 Fix to support of canonical numbers.
110.10 A.Logue 30-NOV-1998 Bug 713456. Fix to legislation code
check in csr_initial_balance_feed
cursors.
110.8 A.Logue 24-NOV-1998 Bug 768805. Fix to include_adjustment dynamic
sql bind variables for new legislations.
110.7 A.Logue 23-NOV-1998 Bug 768805. Fix to is_supported dynamic sql
bind variables for new legislations.
110.6 A.Logue 17-NOV-1998 Bug 713456. Business group and legislation
code check on potential balance feeds in
csr_initial_balance_feed.
110.5 A.Logue 30-OCT-1998 Bug 730491. Changes to use dynamic sql to avoid
explicit legislative package references for
any new legislations. This has resulted in
a slighlty amended interface for any new
legislations where include_adjustment is now
passed the batch_line_id and returns a number,
and is_supported which now returns a number.
110.4 A.Logue 24-MAR-1998 Bug 485629. Fix for balance initialization
elements, check for
balance_initialization_flag AND
stops a thread attempting to process
a batch if it is already being
processed by another thread. Done by
batch header batch_status getting
L-ocked during processing.
110.3 N.Bristow 03-MAR-1998 Bug 630068. GRE name was not being
checked correctly.
110.2 N.Bristow 16-OCT-1997 Now setting the previous value for
latest balances.
40.26 A.Logue 02-JUL-1997 Bug 485629. Support for JP, CH and CA
legislations ie calls to legislative
routines.
40.25 A.Logue 26-JUN-1997 Bug 418064. Further fix for jurisdiction
clashes.
40.24 A.Logue 24-JUN-1997 Bug 418064. Now checks if invalid
combination of balance adjustments.
40.23 N.Bristow 18-FEB-1997 When validating the tax unit the name
as well as the id are now checked.
40.22 N.Bristow 04-FEB-1997 Now commits in chunks when performing
in undo mode.
40.21 N.Bristow 12-JUN-1996 Bug 373446. No longer performing
a full table scan when undoing
a batch.
40.20 N.Bristow 08-MAY-1996 Bug 359005. Now caching Tax Unit Id
when validating. Reinstated the
tax unit id column on
pay_balance_batch_lines. Tuned several
statements.
40.19 N.Bristow 18-MAR-1996 Now padding expired latest balance
columns with -9999.
40.18 N.Bristow 18-MAR-1996 Bug 349583. Order by clause on
csr_bal_adj was wrong, as a result
a no_data_found error was produced
later in the code.
40.17 N.Bristow 08-MAR-1996 Bug 346991. Upload not erroring
correctly when no defined balance id
is found for the balance to be loaded.
40.16 N.Bristow 14-DEC-1995 Error HR_7030_ELE_ENTRY_INV_ADJ was
not being raised correctly.
40.15 N.Bristow 27-Nov-1995 Now loads the latest balances when the
balance value is zero.
40.14 N.Bristow 22-Nov-1995 Added the loading of latest balances.
Latest balances are now loaded in the
transfer mode.
40.13 N.Bristow 11-Nov-1995 Now calling bal_adjust_actions to
perform the balance adjustment.
40.12 N.Bristow 02-Nov-1995 Statements that reference the
hr_tax_units_v view run very slow.
Changed to access base tables.
40.11 N.Bristow 23-Oct-1995 Now csr_batch_line_transfer ordering in
decending date order. Also reversed the
10.7 changes with regard to the BF
legislative functions.
40.10 N.Bristow 17-Oct-1995 Now using error tokens in fnd_messages.
Also changed the order by on
csr_batch_line_transfer.
40.8 N.Bristow 20-Sep_1995 Error status is now set when
an error is encountered.
40.7 M.Callaghan 11-Sep-1995 "whenever sqlerror" added.
Temp change: references to the package
pay_bf_bal_upload commented out for
prod 5 freeze.
40.6 N.Bristow 25-Aug-1995 Now picks up the correct
classifications.
40.5 N.Bristow 13-Jul-1995 Checking against wrong legislation
code.
40.4 N.Bristow 13-Jul-1995 Closing cursors on error.
40.3 N.Bristow 07-Jul-1995 Now uses the new rollback function.
40.2 N.Bristow 06-Jul-1995 General bugs discovered when testing.
40.1 J.S.Hobbs 16-May-1995 created.
*/
--
-- Array data types.
--
type number_array is table of number index by binary_integer;
select *
from pay_balance_batch_lines BL
where BL.batch_id = p_batch_id
and nvl(BL.batch_line_status, 'U') <> 'T'
order by BL.assignment_id,
BL.assignment_number
for update;
select BL.batch_id
,BL.batch_line_id
,BL.batch_line_status
,BL.assignment_id
,BL.balance_type_id
,BL.balance_dimension_id
,BL.dimension_name
,BL.balance_name
,BL.assignment_number
,BL.gre_name
,BL.tax_unit_id
,BL.jurisdiction_code
,BL.original_entry_id
,BL.source_id
,BL.source_text
,BL.source_number
,BL.source_text2
,BL.run_type_id
,BL.value
,trunc(nvl(BL.upload_date, BH.upload_date)) upload_date
,pay_balance_upload.count_contexts
(BL.balance_dimension_id, BL.dimension_name) no_of_contexts
,pay_balance_upload.dim_expiry_date
(BH.business_group_id
,trunc(nvl(BL.upload_date, BH.upload_date))
,BL.dimension_name
,BL.assignment_id
,BL.tax_unit_id
,BL.jurisdiction_code
,BL.original_entry_id
,BL.batch_line_status) expiry_date
from pay_balance_batch_headers BH
,pay_balance_batch_lines BL
where BH.batch_id = p_batch_id
and BL.batch_id = BH.batch_id
and BL.assignment_id = p_assignment_id
order by BL.assignment_id
,decode(BL.batch_line_status,'T',1 ,'E',2 ,'V',3)
,BL.balance_type_id
,trunc(nvl(BL.upload_date, BH.upload_date))
,pay_balance_upload.dim_expiry_date
(BH.business_group_id
,trunc(nvl(BL.upload_date,BH.upload_date))
,BL.dimension_name
,BL.assignment_id
,BL.tax_unit_id
,BL.jurisdiction_code
,BL.original_entry_id
,BL.batch_line_status) desc
,pay_balance_upload.count_contexts(
BL.balance_dimension_id, BL.dimension_name) desc
,BL.batch_line_id;
select *
from pay_balance_batch_lines BL
where BL.batch_id = p_batch_id
and BL.batch_line_status = 'T'
order by BL.payroll_action_id;
select batch_line_id
from pay_balance_batch_lines
where batch_id = p_batch_id;
delete from pay_message_lines
where source_type = 'L'
and source_id = pmlrec.batch_line_id;
delete from pay_message_lines
where source_type = 'H'
and source_id = p_batch_id;
select upper(prt.run_type_name) run_type_name,
prt.run_type_id
from pay_run_types_f prt,
per_business_groups_perf pbg
where pbg.business_group_id = p_business_group_id
and p_effective_date between prt.effective_start_date
and prt.effective_end_date
and (pbg.business_group_id = prt.business_group_id
or pbg.legislation_code = prt.legislation_code
or (prt.business_group_id is null
and prt.legislation_code is null))
and p_run_type_id = prt.run_type_id
and p_run_type_id is not null
union all
select upper(prt.run_type_name) run_type_name,
prt.run_type_id
from pay_run_types_f prt,
per_business_groups_perf pbg
where pbg.business_group_id = p_business_group_id
and p_effective_date between prt.effective_start_date
and prt.effective_end_date
and (pbg.business_group_id = prt.business_group_id
or pbg.legislation_code = prt.legislation_code
or (prt.business_group_id is null
and prt.legislation_code is null))
and upper(prt.run_type_name) = upper(p_run_type_name)
and p_run_type_id is null;
select upper(name) tax_unit_name,
tax_unit_id
from hr_tax_units_v
where business_group_id = p_business_group_id
and p_tax_unit_id = tax_unit_id
and p_tax_unit_id is not null
union all
select upper(name) tax_unit_name,
tax_unit_id
from hr_tax_units_v
where business_group_id = p_business_group_id
and upper(name) = upper(p_gre_name)
and p_tax_unit_id is null;
select upper(name) tax_unit_name,
ORGANIZATION_ID tax_unit_id
from hr_fr_establishments_v
where business_group_id = p_business_group_id
and p_establishment_id = ORGANIZATION_ID
and p_establishment_id is not null
union all
select upper(name) tax_unit_name,
ORGANIZATION_ID tax_unit_id
from hr_fr_establishments_v
where business_group_id = p_business_group_id
and upper(name) = upper(p_name)
and p_establishment_id is null;
select plr.rule_mode
into g_leg_rule
from pay_legislation_rules plr,
per_business_groups_perf pbg
where pbg.business_group_id = p_business_group
and plr.legislation_code = pbg.legislation_code
and plr.rule_type = 'TAX_UNIT';
insert into pay_message_lines
(line_sequence
,message_level
,source_id
,source_type
,line_text)
values
(pay_message_lines_s.nextval
,'F' -- 'F'atal
,decode(p_meesage_level, HEADER, p_batch_id, LINE, p_batch_line_id)
,decode(p_meesage_level, HEADER, 'H' , LINE, 'L')
,substr(l_message_text, 1, 240));
select BG.legislation_code
from per_business_groups_perf BG
where BG.business_group_id = p_business_group_id;
select count(CU.context_id)
into l_no_contexts
from pay_balance_dimensions BD
,ff_route_context_usages CU
where CU.route_id = BD.route_id
and BD.balance_dimension_id = p_balance_dimension_id;
select BBH.batch_id
from pay_balance_batch_headers BBH
where BBH.batch_id = p_batch_id
for update nowait;
select
nvl(sum(BA.adjustment_amount), 0)
,nvl(min(BA.adjustment_date), p_upload_date)
from pay_temp_balance_adjustments BA
where BA.balance_type_id = p_balance_type_id
and BA.adjustment_date between p_expiry_date
and p_upload_date
and ((p_batch_line_rec.jurisdiction_code is null) or
substr(p_batch_line_rec.jurisdiction_code, 1, p_jurisdiction_level)
= substr(BA.jurisdiction_code, 1, p_jurisdiction_level))
and nvl(p_batch_line_rec.tax_unit_id, nvl(BA.tax_unit_id, -1))
= nvl(BA.tax_unit_id, -1)
and nvl(p_batch_line_rec.original_entry_id, nvl(BA.original_entry_id, -1))
= nvl(BA.original_entry_id, -1)
and nvl(p_batch_line_rec.source_id, nvl(BA.source_id, -1))
= nvl(BA.source_id, -1)
and nvl(p_batch_line_rec.source_text, nvl(BA.source_text, '~nvl~'))
= nvl(BA.source_text, '~nvl~')
and nvl(p_batch_line_rec.run_type_id, nvl(BA.run_type_id, -1))
= nvl(BA.run_type_id, -1)
and nvl(p_batch_line_rec.source_number, nvl(BA.source_number, -1))
= nvl(BA.source_number, -1)
and nvl(p_batch_line_rec.source_text2, nvl(BA.source_text2, '~nvl~'))
= nvl(BA.source_text2, '~nvl~')
;
select ASS.payroll_id
from per_all_assignments_f ASS
where ASS.assignment_id = p_assignment_id
and p_effective_date between ASS.effective_start_date
and ASS.effective_end_date;
insert into pay_temp_balance_adjustments
(batch_line_id
,balance_type_id
,balance_dimension_id
,expiry_date
,element_link_id
,ibf_input_value_id
,jc_input_value_id
,adjustment_date
,adjustment_amount
,tax_unit_id
,jurisdiction_code
,source_id
,source_text
,source_number
,source_text2
,run_type_id
,original_entry_id)
values
(p_batch_line_rec.batch_line_id
,p_batch_line_rec.balance_type_id
,p_batch_line_rec.balance_dimension_id
,p_batch_line_rec.expiry_date
,g_balances(p_batch_line_rec.balance_type_id).element_link_id
,g_balances(p_batch_line_rec.balance_type_id).ibf_input_value_id
,g_balances(p_batch_line_rec.balance_type_id).jc_input_value_id
,l_adjustment_date
,l_adjustment_amount
,l_tax_unit_id
,p_batch_line_rec.jurisdiction_code
,p_batch_line_rec.source_id
,p_batch_line_rec.source_text
,p_batch_line_rec.source_number
,p_batch_line_rec.source_text2
,p_batch_line_rec.run_type_id
,p_batch_line_rec.original_entry_id);
select piv.input_value_id
into l_iv_id
from pay_input_values_f piv,
pay_element_links_f pel
where pel.element_link_id= p_element_link_id
and pel.element_type_id = piv.element_type_id
and piv.name = g_legislation_contexts(i).input_value_name
and piv.effective_start_date = START_OF_TIME
and piv.effective_end_date = END_OF_TIME
and pel.effective_start_date = START_OF_TIME
and pel.effective_end_date = END_OF_TIME;
SELECT LOC.region_1
from hr_locations_all LOC
, per_all_assignments_f ASSIGN
where p_effective_date BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
and ASSIGN.assignment_id = p_asg_id
and LOC.location_id = ASSIGN.location_id;
select peev.screen_entry_value
from pay_element_entries_f pee,
pay_element_types_f pet,
pay_element_entry_values_f peev,
pay_input_values_f piv
where pee.assignment_id = p_asg_id
and pet.element_name = 'WCB'
and pet.element_type_id = pee.element_type_id
and pee.element_entry_id = peev.element_entry_id
and peev.input_value_id = piv.input_value_id
and upper(piv.name) = upper(p_inp_name)
and p_effective_date between pee.effective_start_date and pee.effective_end_date
and p_effective_date between pet.effective_start_date and pet.effective_end_date
and p_effective_date between piv.effective_start_date and piv.effective_end_date
and p_effective_date between peev.effective_start_date and peev.effective_end_date;
select hsck.SEGMENT9 wcb_account
from per_all_assignments_f paaf,
hr_soft_coding_keyflex hsck
where paaf.assignment_id = p_asg_id
and paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
and hsck.enabled_flag = 'Y'
and p_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date;
select hsck.SEGMENT10 rate_code
from per_all_assignments_f paaf,
hr_soft_coding_keyflex hsck
where paaf.assignment_id = p_asg_id
and paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
and p_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date;
select to_number(target.ORG_INFORMATION2)
from hr_organization_information target
,hr_soft_coding_keyflex hsck
,per_all_assignments_f paaf
where target.organization_id = nvl(p_tax_unit_id,nvl(hsck.segment1,nvl(hsck.segment2,hsck.segment3)))
and target.ORG_INFORMATION1 = l_jur_code
and target.org_information_context = 'Provincial Reporting Info.'
and paaf.assignment_id = p_asg_id
and paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
and hsck.enabled_flag = 'Y'
and p_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date;
select JOB.name
from per_jobs JOB
, per_all_assignments_f ASSIGN
where p_effective_date BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
and ASSIGN.assignment_id = p_asg_id
and JOB.job_id (+)= ASSIGN.job_id;
select assignment_action_id into l_assignment_action_id from pay_assignment_actions where
payroll_action_id = p_payroll_action_id and assignment_id = p_asg_id;
INSERT INTO pay_action_contexts
(ASSIGNMENT_ACTION_ID
,ASSIGNMENT_ID
,CONTEXT_ID
,CONTEXT_VALUE)
VALUES
(l_assignment_action_id
,p_asg_id
,(select context_id from ff_contexts where context_name = 'SOURCE_ID')
,l_reporting_unit_id);
p_message := 'Insert Record in pay_action_contexts successfully';
hr_utility.trace('Exception raised while inserting data in pay_action_contexts '||p_asg_id);
p_message := 'Exception raised while inserting data in pay_action_contexts '||p_asg_id;
select TBA.*
,ASG.payroll_id
from pay_temp_balance_adjustments TBA
,per_all_assignments_f ASG
where
ASG.assignment_id = p_glbl_data_rec.assignment_id
and TBA.adjustment_date between ASG.effective_start_date
and ASG.effective_end_date
order by TBA.element_link_id
,TBA.adjustment_date
,TBA.jurisdiction_code
,TBA.original_entry_id
,TBA.tax_unit_id
,TBA.source_id
,TBA.source_text
,TBA.source_number
,TBA.source_text2
,TBA.run_type_id
,TBA.balance_type_id;
SELECT 'Y' from pay_balance_types
where BALANCE_TYPE_ID = p_balance_type_id
and BALANCE_NAME in ('WCB ER Liability','WCB Gross','WCB Pre Tax Reductions',
'WCB Subject','WCB Taxable');
update pay_balance_batch_lines BL
set BL.batch_line_status = 'T' -- Transferred
,BL.payroll_action_id = l_payroll_action_id
where BL.batch_line_id = p_batch_line_list(l_index);
g_balances.delete;
select BBH.batch_id
,trunc(BBH.upload_date) upload_date
from pay_balance_batch_headers BBH
where BBH.batch_id = p_batch_id
for update nowait;
select fnd_number.canonical_to_number(AP.parameter_value)
from pay_action_parameters AP
where AP.parameter_name = 'CHUNK_SIZE';
update pay_balance_batch_headers BBH
set BBH.batch_status = 'L'
where BBH.batch_id = p_batch_id;
select distinct nvl(BL.batch_line_status, 'U')
from pay_balance_batch_lines BL
where BL.batch_id = p_batch_id
order by decode(nvl(BL.batch_line_status, 'U'), 'U', 1
, 'T', 2
, 'E', 3
, 'V', 4);
update pay_balance_batch_headers BH
set BH.batch_status = l_status
where BH.batch_id = p_glbl_data_rec.batch_id;
select *
from pay_balance_batch_headers BBH
where BBH.batch_id = p_batch_id
for update;
select BG.business_group_id
,BG.name
,BG.legislation_code
from per_business_groups_perf BG
where p_business_group_id is not null
and BG.business_group_id = p_business_group_id
union all
select BG.business_group_id
,BG.name
,BG.legislation_code
from per_business_groups_perf BG
where p_business_group_id is null
and upper(BG.name) = upper(p_name);
select PL.payroll_id
,PL.payroll_name
,PL.consolidation_set_id
from pay_all_payrolls_f PL
where p_payroll_id is not null
and PL.business_group_id + 0 = p_business_group_id
and PL.payroll_id = p_payroll_id
and p_upload_date between PL.effective_start_date
and PL.effective_end_date
union all
select PL.payroll_id
,PL.payroll_name
,PL.consolidation_set_id
from pay_all_payrolls_f PL
where p_payroll_id is null
and PL.business_group_id + 0 = p_business_group_id
and upper(PL.payroll_name) = upper(p_payroll_name)
and p_upload_date between PL.effective_start_date
and PL.effective_end_date;
update pay_balance_batch_headers BBH
set BBH.business_group_id = l_batch_header_rec.business_group_id
,BBH.business_group_name = l_batch_header_rec.business_group_name
,BBH.payroll_id = l_batch_header_rec.payroll_id
,BBH.payroll_name = l_batch_header_rec.payroll_name
,BBH.batch_status = l_batch_header_rec.batch_status
where current of csr_batch_header;
update pay_balance_batch_headers BBH
set BBH.business_group_id = l_batch_header_rec.business_group_id
,BBH.business_group_name = l_batch_header_rec.business_group_name
,BBH.payroll_id = l_batch_header_rec.payroll_id
,BBH.payroll_name = l_batch_header_rec.payroll_name
where current of csr_batch_header;
select CO.context_name,
BD.database_item_suffix,
BD.legislation_code
from pay_balance_dimensions BD
,ff_route_context_usages CU
,ff_contexts CO
where BD.balance_dimension_id = p_balance_dimension_id
and CU.route_id = BD.route_id
and CO.context_id = CU.context_id;
select defined_balance_id
from pay_defined_balances
where balance_type_id = p_bal_type_id
and balance_dimension_id = p_bal_dimension_id;
select context_id
from ff_contexts
where context_name = p_cxt_name;
select pay_latest_balances_s.nextval
into l_lat_bal_id
from sys.dual;
select distinct person_id,business_group_id
into l_person_id,l_bus_grp_id
from per_all_assignments_f
where assignment_id = p_assignment_id;
insert into pay_latest_balances
(latest_balance_id,
assignment_id,
defined_balance_id,
assignment_action_id,
value,
person_id,
expired_assignment_action_id,
expired_value,
prev_assignment_action_id,
prev_balance_value,
tax_unit_id,
jurisdiction_code,
original_entry_id,
source_id,
source_text,
source_number,
source_text2
)
values (l_lat_bal_id,
p_assignment_id,
l_defined_bal,
p_asg_act_id,
p_value,
l_person_id,
-9999,
-9999,
-9999,
-9999,
p_tax_unit_id,
p_jurisdiction_code,
p_oee_id,
p_source_id,
p_source_text,
p_source_number,
p_source_text2
);
insert into pay_assignment_latest_balances
(latest_balance_id,
assignment_id,
defined_balance_id,
assignment_action_id,
value,
expired_assignment_action_id,
expired_value,
prev_assignment_action_id,
prev_balance_value)
values (l_lat_bal_id,
p_assignment_id,
l_defined_bal,
p_asg_act_id,
p_value,
-9999,
-9999,
-9999,
-9999);
insert into pay_balance_context_values
(latest_balance_id,
context_id,
value)
values (l_lat_bal_id,
ctx_id,
p_tax_unit_id);
insert into pay_balance_context_values
(latest_balance_id,
context_id,
value)
values (l_lat_bal_id,
ctx_id,
p_jurisdiction_code);
insert into pay_balance_context_values
(latest_balance_id,
context_id,
value)
values (l_lat_bal_id,
ctx_id,
p_oee_id);
insert into pay_balance_context_values
(latest_balance_id,
context_id,
value)
values (l_lat_bal_id,
ctx_id,
p_source_id);
insert into pay_balance_context_values
(latest_balance_id,
context_id,
value)
values (l_lat_bal_id,
ctx_id,
p_source_text);
insert into pay_balance_context_values
(latest_balance_id,
context_id,
value)
values (l_lat_bal_id,
ctx_id,
p_source_number);
insert into pay_balance_context_values
(latest_balance_id,
context_id,
value)
values (l_lat_bal_id,
ctx_id,
p_source_text2);
select paa.assignment_action_id
from pay_assignment_actions paa
where paa.assignment_id = p_asg_id
and paa.payroll_action_id = p_act_id;
select pbl.balance_type_id,
pbl.balance_name,
pbl.dimension_name,
pbl.balance_dimension_id,
pbl.batch_line_id,
pbl.gre_name,
pbl.tax_unit_id,
pbl.jurisdiction_code,
pbl.original_entry_id,
pbl.source_id,
pbl.source_text,
pbl.source_number,
pbl.source_text2,
pbl.run_type_id,
pbl.run_type_name,
pbl.payroll_action_id,
pbl.value
from
pay_balance_dimensions pbd,
pay_balance_batch_lines pbl
where pbl.batch_id = p_batch_id
and pbl.assignment_id = p_assignment_id
and pbl.balance_dimension_id = pbd.balance_dimension_id
and pbl.upload_date is null -- Don't consider historical loads
and pbd.dimension_type in ('A', 'P')
order by pbl.batch_line_status;
select /*+ ORDERED
USE_NL(pbl ppa pbf paa rr rrv)
INDEX(pbl PAY_BALANCE_BATCH_LINES_N51)
INDEX(ppa PAY_PAYROLL_ACTIONS_PK)
INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
INDEX(rr PAY_RUN_RESULTS_N50)
INDEX(rrv PAY_RUN_RESULT_VALUES_N50)
INDEX(pbf PAY_BALANCE_FEEDS_F_N2) */
to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
into l_asg_act_id
from
pay_balance_batch_lines pbl,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results rr,
pay_run_result_values rrv,
pay_balance_feeds_f pbf
where pbl.batch_id = p_glbl_data_rec.batch_id
and pbl.assignment_id = p_glbl_data_rec.assignment_id
and pbl.balance_type_id = l_lat_asg_balance.balance_type_id
and pbl.payroll_action_id = ppa.payroll_action_id
and pbl.balance_type_id = pbf.balance_type_id + 0
and ppa.effective_date between
pbf.effective_start_date and pbf.effective_end_date
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_id = pbl.assignment_id
and paa.assignment_action_id = rr.assignment_action_id
and rr.run_result_id = rrv.run_result_id
and rrv.input_value_id = pbf.input_value_id
and nvl(rrv.result_value, '0') <> '0'
and upper(nvl(nvl(pbl.gre_name, l_lat_asg_balance.gre_name),'-1')) =
upper(nvl(nvl(l_lat_asg_balance.gre_name,
pbl.gre_name), '-1'))
and upper(nvl(nvl(pbl.run_type_name, l_lat_asg_balance.run_type_name),'-1')) =
upper(nvl(nvl(l_lat_asg_balance.run_type_name,
pbl.run_type_name), '-1'))
and nvl(nvl(pbl.jurisdiction_code,
l_lat_asg_balance.jurisdiction_code), -1) =
nvl(nvl(l_lat_asg_balance.jurisdiction_code,
pbl.jurisdiction_code), -1)
and upper(nvl(nvl(pbl.source_text, l_lat_asg_balance.source_text),'-1')) =
upper(nvl(nvl(l_lat_asg_balance.source_text, pbl.source_text), '-1'))
and nvl(nvl(pbl.source_id, l_lat_asg_balance.source_id), -1) =
nvl(nvl(l_lat_asg_balance.source_id, pbl.source_id), -1)
and upper(nvl(nvl(pbl.source_text2, l_lat_asg_balance.source_text2),'-1')) =
upper(nvl(nvl(l_lat_asg_balance.source_text2, pbl.source_text2), '-1'))
and nvl(nvl(pbl.source_number, l_lat_asg_balance.source_number), -1) =
nvl(nvl(l_lat_asg_balance.source_number, pbl.source_number), -1)
and nvl(nvl(pbl.tax_unit_id, l_lat_asg_balance.tax_unit_id), -1) =
nvl(nvl(l_lat_asg_balance.tax_unit_id, pbl.tax_unit_id), -1)
and nvl(nvl(pbl.run_type_id, l_lat_asg_balance.run_type_id), -1) =
nvl(nvl(l_lat_asg_balance.run_type_id, pbl.run_type_id), -1)
and nvl(nvl(pbl.original_entry_id,
l_lat_asg_balance.original_entry_id), -1) =
nvl(nvl(l_lat_asg_balance.original_entry_id,
pbl.original_entry_id), -1);
select BD.balance_dimension_id
,upper(BD.dimension_name) dimension_name
from pay_balance_dimensions BD
where p_balance_dimension_id is not null
and BD.balance_dimension_id = p_balance_dimension_id
and nvl(BD.business_group_id, nvl(p_business_group_id, -1)) =
nvl(p_business_group_id, -1)
and nvl(BD.legislation_code, nvl(p_legislation_code, ' ')) =
nvl(p_legislation_code, ' ')
union all
select BD.balance_dimension_id
,upper(BD.dimension_name) dimension_name
from pay_balance_dimensions BD
where p_balance_dimension_id is null
and upper(BD.dimension_name) = upper(p_dimension_name)
and nvl(BD.business_group_id, nvl(p_business_group_id, -1)) =
nvl(p_business_group_id, -1)
and nvl(BD.legislation_code, nvl(p_legislation_code, ' ')) =
nvl(p_legislation_code, ' ');
select defined_balance_id
from pay_defined_balances
where balance_type_id = p_balance_type_id
and balance_dimension_id = p_dimension_id;
select EE.element_entry_id
from pay_element_entries_f EE
where EE.assignment_id = p_assignment_id
and EE.entry_type = 'E'
and EE.effective_start_date <= p_upload_date
and EE.element_entry_id = p_original_entry_id
and EE.original_entry_id is null
union all
select EE.element_entry_id
from pay_element_entries_f EE
where EE.assignment_id = p_assignment_id
and EE.entry_type = 'E'
and EE.effective_start_date <= p_upload_date
and EE.original_entry_id = p_original_entry_id;
select BT.balance_type_id
,upper(BT.balance_name) balance_name
from pay_balance_types BT
where p_balance_type_id is not null
and BT.balance_type_id = p_balance_type_id
and nvl(BT.business_group_id, nvl(p_business_group_id, -1)) =
nvl(p_business_group_id, -1)
and nvl(BT.legislation_code, nvl(p_legislation_code, ' ')) =
nvl(p_legislation_code, ' ')
union all
select BT.balance_type_id
,upper(BT.balance_name) balance_name
from pay_balance_types BT
where p_balance_type_id is null
and upper(BT.balance_name) = upper(p_balance_name)
and nvl(BT.business_group_id, nvl(p_business_group_id, -1)) =
nvl(p_business_group_id, -1)
and nvl(BT.legislation_code, nvl(p_legislation_code, ' ')) =
nvl(p_legislation_code, ' ');
select ET.element_type_id
,BT.jurisdiction_level
,IV.input_value_id ibf_input_value_id
,decode(nvl(BT.jurisdiction_level, 0),
0, null,
IV2.input_value_id) jc_input_value_id
from pay_balance_types BT
,pay_balance_feeds_f BF
,pay_input_values_f IV
,pay_input_values_f IV2
,pay_element_types_f ET
,pay_element_classifications EC
where BF.balance_type_id = p_balance_type_id
and BT.balance_type_id = BF.balance_type_id
and IV.input_value_id = BF.input_value_id
and ET.element_type_id = IV.element_type_id
and EC.classification_id = ET.classification_id
and EC.balance_initialization_flag = 'Y'
and ((nvl(BT.jurisdiction_level, 0) <> 0 and
IV2.element_type_id = ET.element_type_id and
IV2.name = p_glbl_data_rec.jurisdiction_iv) or
(nvl(BT.jurisdiction_level, 0) = 0 and
IV2.input_value_id = IV.input_value_id))
and (ET.business_group_id +0 = p_business_group_id
or (ET.business_group_id is null
and ET.legislation_code = p_legislation_code)
or (ET.business_group_id is null and ET.legislation_code is null))
and BF.effective_start_date = START_OF_TIME
and BF.effective_end_date = END_OF_TIME
and IV.effective_start_date = START_OF_TIME
and IV.effective_end_date = END_OF_TIME
and IV2.effective_start_date = START_OF_TIME
and IV2.effective_end_date = END_OF_TIME
and ET.effective_start_date = START_OF_TIME
and ET.effective_end_date = END_OF_TIME;
select EL.element_link_id
from pay_element_links_f EL
where EL.business_group_id = p_business_group_id
and EL.element_type_id = p_element_type_id
and EL.link_to_all_payrolls_flag = 'Y'
and EL.payroll_id is null
and EL.job_id is null
and EL.position_id is null
and EL.people_group_id is null
and EL.organization_id is null
and EL.grade_id is null
and EL.pay_basis_id is null
and EL.employment_category is null
and EL.effective_start_date = START_OF_TIME
and EL.effective_end_date = END_OF_TIME;
select ASG.assignment_id
,upper(ASG.assignment_number) assignment_number
,ASG.assignment_type
,ASG.business_group_id
,ASG.payroll_id
,ASG.effective_start_date
from per_all_assignments_f ASG
where p_assignment_id is not null
and ASG.business_group_id + 0 = p_business_group_id
and ASG.assignment_id = p_assignment_id
and p_upload_date between ASG.effective_start_date
and ASG.effective_end_date
union all
select ASG.assignment_id
,upper(ASG.assignment_number) assignment_number
,ASG.assignment_type
,ASG.business_group_id
,ASG.payroll_id
,ASG.effective_start_date
from per_all_assignments_f ASG
where p_assignment_id is null
and ASG.business_group_id + 0 = p_business_group_id
and ASG.assignment_number = p_assignment_number
and p_upload_date between ASG.effective_start_date
and ASG.effective_end_date;
select AA.payroll_action_id
from pay_assignment_actions AA, pay_payroll_actions PAA
where AA.assignment_id = p_assignment_id
and PAA.payroll_action_id = AA.payroll_action_id
and PAA.action_type <> 'BEE';
g_bal_vald.delete;
g_dim_vald.delete;
update pay_balance_batch_lines BL
set BL.assignment_number = l_batch_line_rec.assignment_number
,BL.assignment_id = l_batch_line_rec.assignment_id
,BL.balance_name = l_batch_line_rec.balance_name
,BL.balance_type_id = l_batch_line_rec.balance_type_id
,BL.dimension_name = l_batch_line_rec.dimension_name
,BL.balance_dimension_id = l_batch_line_rec.balance_dimension_id
,BL.gre_name = l_batch_line_rec.gre_name
,BL.tax_unit_id = l_batch_line_rec.tax_unit_id
,BL.jurisdiction_code = l_batch_line_rec.jurisdiction_code
,BL.original_entry_id = l_batch_line_rec.original_entry_id
,BL.source_id = l_batch_line_rec.source_id
,BL.source_text = l_batch_line_rec.source_text
,BL.source_number = l_batch_line_rec.source_number
,BL.source_text2 = l_batch_line_rec.source_text2
,BL.run_type_id = l_batch_line_rec.run_type_id
,BL.batch_line_status = l_batch_line_rec.batch_line_status
where current of csr_batch_line_validate;
select nvl(rule_mode, 'N')
into l_validation_supp
from pay_legislation_rules
where legislation_code = p_glbl_data_rec.legislation_code
and rule_type = 'BAL_INIT_VALIDATION';
select distinct TBA1.batch_line_id
from pay_temp_balance_adjustments TBA1,
pay_temp_balance_adjustments TBA2,
pay_balance_types BT
where TBA1.batch_line_id <> TBA2.batch_line_id
and TBA1.adjustment_date = TBA2.adjustment_date
and TBA1.balance_type_id = TBA2.balance_type_id
and TBA1.adjustment_amount <> 0
and TBA2.adjustment_amount <> 0
and nvl(TBA1.tax_unit_id, -1) = nvl(TBA2.tax_unit_id, -1)
and nvl(TBA1.run_type_id, -1) = nvl(TBA2.run_type_id, -1)
and nvl(TBA1.original_entry_id, -1) = nvl(TBA2.original_entry_id, -1)
and nvl(TBA1.source_id, -1) = nvl(TBA2.source_id, -1)
and nvl(TBA1.source_text, '~null~') = nvl(TBA2.source_text, '~null~')
and nvl(TBA1.source_number, -1) = nvl(TBA2.source_number, -1)
and nvl(TBA1.source_text2, '~null~') = nvl(TBA2.source_text2, '~null~')
and BT.balance_type_id = TBA1.balance_type_id
and nvl(substr(TBA1.jurisdiction_code,1,BT.jurisdiction_level), -1) =
nvl(substr(TBA2.jurisdiction_code,1,BT.jurisdiction_level), -1);
l_batch_line_list.delete;
g_payroll_actions.delete(l_payroll_action_num+1,g_payroll_actions.count);
delete from pay_temp_balance_adjustments;
g_payroll_actions.delete(l_payroll_action_num+1,g_payroll_actions.count);
update pay_balance_batch_lines BL
set BL.batch_line_status = 'E' -- Error
where BL.batch_line_id = l_batch_line_list(l_index);
delete from pay_temp_balance_adjustments;
g_payroll_actions.delete(l_payroll_action_num+1,g_payroll_actions.count);
update pay_balance_batch_lines BL
set BL.batch_line_status = 'E' -- Error
where BL.batch_line_id = l_batch_line_list(l_index);
delete from pay_temp_balance_adjustments;
select /*+ ORDERED
INDEX(ASG PER_ASSIGNMENTS_F_PK)*/
distinct ASG.assignment_id
from pay_balance_batch_lines BL,
per_all_assignments_f ASG
where BL.batch_id = p_batch_id
and BL.assignment_id = ASG.assignment_id
and ASG.business_group_id = p_business_group_id
and ASG.payroll_id + 0 = p_payroll_id
and p_effective_date between ASG.effective_start_date
and ASG.effective_end_date;
update pay_balance_batch_lines BL
set BL.batch_line_status = 'U'
,BL.payroll_action_id = null
where BL.batch_id = p_glbl_data_rec.batch_id
--and BL.assignment_id = l_asg_id
and BL.payroll_action_id = l_pyrl_act_id;
select BL.batch_line_id
from pay_balance_batch_lines BL
where BL.batch_id = p_batch_id
and BL.batch_line_status = 'E';
delete from pay_message_lines ML
where ML.message_level = 'F'
and ML.source_type = 'L'
and ML.source_id = errline.batch_line_id;
delete from pay_message_lines ML
where ML.message_level = 'F'
and ML.source_type = 'H'
and ML.source_id = p_glbl_data_rec.batch_id;
delete from pay_balance_batch_lines BBL
where BBL.batch_id = p_glbl_data_rec.batch_id;
delete from pay_balance_batch_headers BBH
where BBH.batch_id = p_glbl_data_rec.batch_id;
select ppa.payroll_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_id = p_payroll_id
and ppa.action_type <> 'I'
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_id = p_assignment_id;
select /*+ ORDERED
INDEX(ASG PER_ASSIGNMENTS_F_PK)*/
distinct ASG.assignment_id
from pay_balance_batch_lines BL,
per_all_assignments_f ASG
where BL.batch_id = p_batch_id
and BL.assignment_id = ASG.assignment_id
and BL.batch_line_status = 'T'
and ASG.business_group_id = p_business_group_id
and ASG.payroll_id + 0 = p_payroll_id
and p_effective_date between ASG.effective_start_date
and ASG.effective_end_date;
select pbh.business_group_id,
pbg.legislation_code,
pbh.payroll_id
from per_business_groups_perf pbg,
pay_balance_batch_headers pbh
where pbh.batch_id = p_batch_id
and pbh.business_group_id = pbg.business_group_id;
select
distinct ppa.payroll_action_id
from
pay_payroll_actions ppa
,pay_balance_batch_lines pbbl
where
ppa.action_status <> 'C'
and ppa.payroll_action_id = pbbl.payroll_action_id
and pbbl.batch_line_status = 'T'
and pbbl.batch_id = p_glbl_data_rec.batch_id
;
g_payroll_actions.delete;
update pay_balance_batch_headers
set batch_status = 'E'
where batch_id = l_glbl_data_rec.batch_id;
update pay_balance_batch_headers
set batch_status = 'E'
where batch_id = l_glbl_data_rec.batch_id;
select BBH.batch_status
into l_batch_status
from pay_balance_batch_headers BBH
where BBH.batch_id = p_batch_id;