[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
mreid 01-OCT-1999 Added explicit column list to insert on
ff_archive_items
nbristow 09-NOV-1999 Changes to the ff_archive_item table made for
the new Archive Item API.
alogue 15-FEB-2000 Utf8 support : ff_database_items user_name and
ff_user_entities item_name lengthened to 240.
Use of varchar_240_tbl.
Remove insert of 'AAP' into
ff_archive_items.archive_type as column doesn't
exist in 11i.
nbristow 19-MAY-2000 Added the deinitialize section.
ssarma 03-AUG-2000 Added US specific code for EOY related issue.
It starts as -- if g_leg_code = US then ....
nbristow 18-FEB-2001 Changed the process_employee so that the dynamic
procedure call can be done with out the
initialisation procedure.
mreid 26-MAR-2002 Bug 2281868. Added legislation_code to
csr_defined_balance cursor.
nbristow 16-JUL-2002 Added standard_deinit.
alogue 23-JUN-2003 Handle removal of lines from pay_population_ranges
in remove_report_actions. Bug 3017447.
nbristow 16-DEC-2003 Now delteting from pay_temp_object_actions.
alogue 25-FEB-2004 Bulk operations within remove_report_actions
for performance purposes.
nbristow 09-JUL-2004 Added process_chunk.
mreid 11-NOV-2005 Bug 4729140: added date effective joins in
arch_db_items_loop
alogue 31-AUG-2007 Bug 6196572: performance fix to
remove_report_actions. Deletion of assignment
actions to inside this loop to avoid rollback
segment issue.
--
--
* ---------------------------------------------------------------------
*/
-- TYPES
--
-- The table types are just simple tables or various types. The records
-- are composite types of tables that contain a size (sz) to hold the
-- number of data items currently stored in the table. Data items are
-- stored in the tables within the records contiguously from 1 to sz.
--==================================================================
TYPE varchar_1_tbl IS TABLE OF VARCHAR(1) INDEX BY binary_integer;
select to_number(UE.creator_id)
from ff_database_items DI,
ff_user_entities UE
where DI.user_name = p_db_item_name
and UE.user_entity_id = DI.user_entity_id
and UE.creator_type = 'B'
and (UE.legislation_code = l_legislation_code
or UE.business_group_id = l_business_group_id);
select frpv.value
into l_jursd_value
from ff_route_parameter_values frpv,
ff_route_parameters frp
where frpv.route_parameter_id = frp.route_parameter_id
and frpv.user_entity_id = p_user_entity_id
and frp.route_id = p_route_id
and frp.parameter_name = 'Jursd. Level';
SELECT distinct us.item_name
FROM pay_magnetic_blocks mb,
pay_magnetic_records mr,
ff_fdi_usages_f us
WHERE mb.report_format = p_report_format AND
mb.magnetic_block_id = mr.magnetic_block_id AND
mr.formula_id = us.formula_id AND
us.usage = 'D' AND
p_effective_date BETWEEN us.effective_start_date AND
us.effective_end_date;
SELECT con.context_name name
FROM ff_user_entities ue,
ff_route_context_usages rcu,
ff_contexts con
WHERE ue.user_entity_id = p_user_entity_id AND
ue.route_id = rcu.route_id AND
con.context_id = rcu.context_id ;
SELECT dbi.user_entity_id, dbi.data_type
INTO a_user_entity_id, a_data_type
FROM ff_database_items dbi,
ff_user_entities ue
WHERE dbi.user_name = db_items_row.item_name AND
dbi.user_entity_id = ue.user_entity_id AND
((ue.legislation_code is null
and ue.business_group_id is null
and not exists
(select ''
from ff_user_entities fue2,
ff_database_items fdi2
where fdi2.user_name = db_items_row.item_name
and fdi2.user_entity_id = fue2.user_entity_id
and (fue2.business_group_id = l_business_group_id
or fue2.legislation_code = l_legislation_code)
)
)
OR (ue.business_group_id is null
and l_legislation_code = ue.legislation_code
and not exists
(select ''
from ff_user_entities fue2,
ff_database_items fdi2
where fdi2.user_name = db_items_row.item_name
and fdi2.user_entity_id = fue2.user_entity_id
and fue2.business_group_id = l_business_group_id
)
)
OR ue.business_group_id + 0 = l_business_group_id
);
SELECT dbi.user_entity_id,ue.creator_type,ue.route_id
INTO user_entity_id,creator_type,route_id
FROM ff_database_items dbi,
ff_user_entities ue
WHERE dbi.user_name = name AND
dbi.user_entity_id = ue.user_entity_id
and ((ue.legislation_code is null
and ue.business_group_id is null
and not exists
(select ''
from ff_user_entities fue2,
ff_database_items fdi2
where fdi2.user_name = name
and fdi2.user_entity_id = fue2.user_entity_id
and (fue2.business_group_id = l_business_group_id
or fue2.legislation_code = l_legislation_code)
)
)
or (ue.business_group_id is null
and l_legislation_code = ue.legislation_code
and not exists
(select ''
from ff_user_entities fue2,
ff_database_items fdi2
where fdi2.user_name = name
and fdi2.user_entity_id = fue2.user_entity_id
and fue2.business_group_id = l_business_group_id
)
)
or ue.business_group_id + 0 = l_business_group_id
);
SELECT jurisdiction_level jur_lev
INTO l_balance_dbis.jur_level(l_balance_dbis.sz)
FROM pay_balance_types pbt,
pay_defined_balances pdb
WHERE pbt.balance_type_id= pdb.balance_type_id AND
pdb.defined_balance_id =
l_balance_dbis.balance_id(l_balance_dbis.sz);
SELECT pa.business_group_id,
bg.legislation_code,
pa.effective_date,
pa.date_earned,
pa.legislative_parameters
INTO l_business_group_id,
l_legislation_code,
l_effective_date,
l_date_earned,
legislative_parameters
FROM pay_payroll_actions pa,
per_business_groups bg
WHERE pa.payroll_action_id = l_payroll_action_id AND
pa.business_group_id = bg.business_group_id;
SELECT prfm.report_format, pac.report_type
INTO l_report_format, l_report_type
FROM pay_report_format_mappings_f prfm,
pay_payroll_actions pac
WHERE prfm.report_type = pac.report_type
AND prfm.report_qualifier = pac.report_qualifier
AND prfm.report_category = pac.report_category
AND pac.payroll_action_id = l_payroll_action_id
AND pac.effective_date BETWEEN effective_start_date AND
effective_end_date;
select prfm.initialization_code
into init_proc
from pay_report_format_mappings_f prfm,
pay_payroll_actions ppa
where ppa.payroll_action_id = pactid
and ppa.report_type = prfm.report_type
and ppa.report_category = prfm.report_category
and ppa.effective_date between prfm.effective_start_date
and prfm.effective_end_date
and ppa.report_qualifier = prfm.report_qualifier;
select prfm.deinitialization_code
into deinit_proc
from pay_report_format_mappings_f prfm,
pay_payroll_actions ppa
where ppa.payroll_action_id = pactid
and ppa.report_type = prfm.report_type
and ppa.report_category = prfm.report_category
and ppa.effective_date between prfm.effective_start_date
and prfm.effective_end_date
and ppa.report_qualifier = prfm.report_qualifier;
INSERT INTO ff_archive_items
( ARCHIVE_ITEM_ID, USER_ENTITY_ID, CONTEXT1, VALUE)
VALUES
( ff_archive_items_s.nextval,p_user_entity_id,p_context1,p_value);
select context_id into v_context_id
from ff_contexts
where context_name= l_contexts_dbi.name(i);
insert into ff_archive_item_contexts
(archive_item_id,sequence_no,context,context_id)
VALUES
(ff_archive_items_s.currval,
1,v_context_value,v_context_id);
l_jur_set.delete;
select context_id into v_context_id
from ff_contexts
where context_name=l_contexts_dbi.name(i);
insert into ff_archive_item_contexts
(archive_item_id,sequence_no,context,context_id)
VALUES
(ff_archive_items_s.currval,1,
v_context_value,v_context_id);
l_jur_set.delete;
select prfm.archive_code
into archiv_proc
from pay_report_format_mappings_f prfm,
pay_payroll_actions ppa
where ppa.payroll_action_id = pactid
and ppa.report_type = prfm.report_type
and ppa.report_category = prfm.report_category
and ppa.effective_date between prfm.effective_start_date
and prfm.effective_end_date
and ppa.report_qualifier = prfm.report_qualifier;
g_context_values.name.delete;
g_context_values.value.delete;
select prfm.archive_code
into archiv_proc
from pay_report_format_mappings_f prfm,
pay_payroll_actions ppa
where ppa.payroll_action_id = pactid
and ppa.report_type = prfm.report_type
and ppa.report_category = prfm.report_category
and ppa.effective_date between prfm.effective_start_date
and prfm.effective_end_date
and ppa.report_qualifier = prfm.report_qualifier;
select assignment_action_id
from pay_assignment_actions
where payroll_action_id = p_payroll_act;
select object_action_id
from pay_temp_object_actions
where payroll_action_id = p_payroll_act;
select assignment_action_id
from pay_assignment_actions
where payroll_action_id = p_payroll_act
and chunk_number = p_chunk;
delete from pay_action_interlocks
where locking_action_id = aalist(i);
delete from PAY_MESSAGE_LINES
where source_id = aalist(i)
and source_type = 'A';
delete from pay_assignment_actions
where assignment_action_id = aalist(i);
delete from PAY_MESSAGE_LINES
where source_id = objlist(i)
and source_type = 'A';
delete from pay_temp_object_actions
where object_action_id = objlist(i);
delete from PAY_MESSAGE_LINES
where source_id = p_pact_id
and source_type = 'P';
delete from pay_population_ranges
where payroll_action_id = p_pact_id;
delete from pay_payroll_actions
where payroll_action_id = p_pact_id;
delete from pay_action_interlocks
where locking_action_id = aalist(i);
delete from PAY_MESSAGE_LINES
where source_id = aalist(i)
and source_type = 'A';
delete from pay_assignment_actions
where payroll_action_id = p_pact_id
and chunk_number = p_chunk_no;
select pay_core_utils.get_parameter('REMOVE_ACT',
pa1.legislative_parameters)
into remove_act
from pay_payroll_actions pa1
where pa1.payroll_action_id = pactid;