[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
T Grisco 16-MAY-1996 Fixed bug #366661 where selecting from
pay_balance_feeds_f returns more than 1 row
because it's date-tracked.
T Grisco 25-MAY-1996 Fixed performance bug #368631.
N Bristow 17-JUN-1996 Bug 374880. Changed structure creation to
make use of pl/sql tables to improve
performance. Also fixed previous change.
J Alloun 30-JUL-1996 Added error handling.
M Fender 10-JUL-1997 Removed show errors call
A Mills 28-AUG-1997 Altered date checking for NLS compliance.
N Bristow 06-JUL-1998 Bug 692520. Now checking the balance types
and dimensions for business group id.
M Reid 25-JUL-2000 Corrected call to ins_balance_feed to use
business_group_id
JARTHURT 15-JAN-2001 Changed hard-coded USD currency code to use
instead the business group's currency code.
Change made so this script can be used for
Canadian legislation.
M Reid 18-APR-2001 Use uom_code instead of uom for Jurisdiction
T Habara 15-JUL-2004 115.6 Bug 3768760. Modified csr_is_balance_fed.
Added nocopy. GSCC standards.
*/
--
--
-- Global declarations
type char_array is table of varchar2(80) index by binary_integer;
select balance_type_id,
nvl(jurisdiction_level, 999),
balance_uom
into l_balance_type_id, l_jurisdiction_level, l_bal_uom
from pay_balance_types
where upper(balance_name) = l_baltyp_name
and ((business_group_id = p_busgrp_id)
or( business_group_id is null
and legislation_code = p_leg_code)
or( business_group_id is null
and legislation_code is null)
)
for update of balance_type_id;
select balance_dimension_id
into l_balance_dim_id
from pay_balance_dimensions
where upper(dimension_name) = l_baldim_name
and ((business_group_id = p_busgrp_id)
or( business_group_id is null
and legislation_code = p_leg_code)
or( business_group_id is null
and legislation_code is null)
);
select balance_name,
dimension_name
from pay_balance_batch_lines
where batch_id = c_batch_id;
select hou.business_group_id,
hou.legislation_code
into l_bg_id,
l_leg_code
from per_business_groups hou,
pay_balance_batch_headers bbh
where bbh.batch_id = p_batch_id
and upper(hou.name) = upper(bbh.business_group_name);
select balance_feed_id
from pay_balance_feeds_f BF,
pay_input_values_f IV,
pay_element_types_f ET,
pay_element_classifications EC
where EC.balance_initialization_flag = 'Y'
and ET.classification_id = EC.classification_id
and ET.effective_start_date = to_date('0001/01/01','YYYY/MM/DD')
and ET.effective_end_date = to_date('4712/12/31','YYYY/MM/DD')
and IV.element_type_id = ET.element_type_id
and IV.input_value_id = BF.input_value_id
and IV.effective_start_date = to_date('0001/01/01','YYYY/MM/DD')
and IV.effective_end_date = to_date('4712/12/31','YYYY/MM/DD')
and BF.balance_type_id = p_balance_type_id
and BF.effective_start_date = to_date('0001/01/01','YYYY/MM/DD')
and BF.effective_end_date = to_date('4712/12/31','YYYY/MM/DD')
and nvl(BF.business_group_id, p_business_group) = p_business_group;
select pbg.business_group_id, pbg.name ,
pbg.currency_code
into l_bg_id, l_bg_name,
l_currency_code
from pay_balance_batch_headers bbh,
per_business_groups pbg
where batch_id = p_batch_id
and upper(pbg.name) = upper(bbh.business_group_name);
update pay_element_types_f ELEM
set ELEM.element_information1 = 'B'
where element_type_id = l_element_type_id;
p_insert_type => 'INSERT_INPUT_VALUE',
p_element_link_id => l_elem_link_id,
p_input_value_id => l_input_val_id,
p_input_value_name => 'Jurisdiction',
p_costable_type => NULL,
p_validation_start_date =>
to_date('01-01-0001','DD-MM-YYYY'),
p_validation_end_date =>
to_date('31-12-4712','DD-MM-YYYY'),
p_default_value => NULL,
p_max_value => NULL,
p_min_value => NULL,
p_warning_or_error_flag => NULL,
p_hot_default_flag => NULL,
p_legislation_code => NULL,
p_pay_value_name => NULL,
p_element_type_id => l_element_type_id);
create an input value for each balance_name selected and
name it after the balance it is created for.
*/
--
l_input_val_id := pay_db_pay_setup.create_input_value (
p_element_name => l_element_name,
p_name =>
substr(l_bal_name, 1, 28)||j,
p_uom_code =>
g_baltyp_tbl_uom(l_bal_count),
p_business_group_name => l_bg_name,
p_effective_start_date =>
to_date('01-01-0001','DD-MM-YYYY'),
p_effective_end_date =>
to_date('31-12-4712','DD-MM-YYYY'),
p_display_sequence => j+1);
p_insert_type => 'INSERT_INPUT_VALUE',
p_element_link_id => l_elem_link_id,
p_input_value_id => l_input_val_id,
p_input_value_name =>
substr(l_bal_name, 1 , 28)||j,
p_costable_type => NULL,
p_validation_start_date =>
to_date('01-01-0001','DD-MM-YYYY'),
p_validation_end_date =>
to_date('31-12-4712','DD-MM-YYYY'),
p_default_value => NULL,
p_max_value => NULL,
p_min_value => NULL,
p_warning_or_error_flag => NULL,
p_hot_default_flag => NULL,
p_legislation_code => NULL,
p_pay_value_name => NULL,
p_element_type_id => l_element_type_id);