[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
achauhan 40.7 08-aug-1998 Commented out the insert into ff_archive_item_contexts
The table definition of ff_archive_item_contexts has
changed and the old archive process does not need to
populate this table since there is only one contxt for the routes
djoshi 40.8 08-Apr-1999 Verfied for Canonical Complience of Date
mreid 115.5 30-Nov-1999 Added column names to ff_archive_items insert
alogue 115.6 15-Feb-2000 Utf8 support : varchar_240_tbl required for item_name.
mreid 115.7 13-Sep-2001 Added column name to all inserts
rsirigir 115.8 13-Aug-2002 Bug 2484696, included dbdrv commands to conform to
GSCC compliance
meshah 115.9 10-Oct-2005 Added nocopy to the out parameters
*/
--==============================================================================
-- 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 number_tbl IS TABLE OF NUMBER INDEX BY binary_integer;
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;
DELETE FROM fnd_sessions WHERE session_id = userenv('SESSIONID');
INSERT INTO fnd_sessions (session_id, effective_date)
VALUES ( userenv('SESSIONID'),l_effective_date );
/* SQWLD - replace this select from pay_report_format_mappings with lookup_format,
parse media type from leg params
ORIGINAL CODE (before SQWLD):
SELECT report_format
INTO l_report_format
FROM pay_report_format_mappings
WHERE report_type = l_report_type AND
report_qualifier = l_state AND
l_effective_date BETWEEN effective_start_date AND
effective_end_date;
SELECT sr.jurisdiction_code
INTO jurisdiction_code
FROM pay_state_rules sr
WHERE sr.state_code = l_state;
SELECT parameter_value
INTO l_chunk_size
FROM pay_action_parameters
WHERE parameter_name = 'CHUNK_SIZE';
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';
SELECT cx.context_name
FROM ff_user_entities ue,
ff_route_context_usages rcu,
ff_contexts cx
WHERE ue.user_entity_id = p_user_entity_id AND
ue.route_id = rcu.route_id AND
rcu.context_id = cx.context_id;
SELECT dbi.user_entity_id, dbi.data_type
INTO a_user_entity_id, a_data_type
FROM ff_database_items dbi
WHERE dbi.user_name = db_items_row.item_name;
SELECT dbi.user_entity_id,ue.creator_type
INTO user_entity_id,creator_type
FROM ff_database_items dbi,
ff_user_entities ue
WHERE dbi.user_name = name AND
dbi.user_entity_id = ue.user_entity_id;
INSERT INTO pay_message_lines
( line_sequence, payroll_id, message_level,
source_id, source_type, line_text )
VALUES
( pay_message_lines_s.nextval,
NULL,
'I',
l_payroll_action_id,
'P',
p_line );
SELECT period
INTO period
FROM pay_w2_magnetic_tape_reports
WHERE payroll_action_id = l_payroll_action_id;
SELECT ai.value
INTO r
FROM ff_database_items dbi,
ff_archive_items ai
WHERE dbi.user_entity_id = ai.user_entity_id AND
dbi.user_name = p_item_name AND
ai.context1 = p_context1;
SELECT name
INTO r
FROM hr_organization_units
WHERE organization_id = p_context2;
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 );
/* context2 is not supported in the old archive process. So, commenting this insert */
IF p_context2 IS NOT NULL THEN
-- INSERT INTO ff_archive_item_contexts VALUES
-- ( ff_archive_items_s.currval,2,p_context2 );
SELECT value
INTO chk
FROM ff_archive_items ai,
ff_archive_item_contexts aic
WHERE ai.archive_item_id = aic.archive_item_id AND
ai.user_entity_id = p_user_entity_id AND
ai.context1 = p_context1 AND
aic.sequence_no = 2 AND
aic.context = p_context2;
SELECT value
INTO chk
FROM ff_archive_items ai
WHERE ai.user_entity_id = p_user_entity_id AND
ai.context1 = p_context1;
SELECT DISTINCT aa.tax_unit_id
FROM pay_assignment_actions aa
WHERE aa.payroll_action_id = p_payroll_action_id
UNION
SELECT p_transmitter_tax_unit_id
FROM DUAL;
SELECT aa.assignment_action_id,
aa.assignment_id,
pay_magtape_generic.date_earned (p_effective_date,aa.assignment_id)
date_earned,
aa.tax_unit_id
FROM pay_assignment_actions aa
WHERE aa.payroll_action_id = p_payroll_action_id;
SELECT MAX(assignment_action_id)
INTO aaid
FROM pay_assignment_actions
WHERE tax_unit_id = employee_row.tax_unit_id AND
assignment_id = employee_row.assignment_id;
DELETE FROM ff_archive_item_contexts ic
WHERE EXISTS ( SELECT '1'
FROM ff_contexts con,
ff_route_context_usages rcu,
ff_user_entities ue,
pay_assignment_actions assact,
ff_archive_items i
WHERE i.archive_item_id = ic.archive_item_id
AND assact.payroll_action_id = p_payroll_action_id
AND assact.assignment_action_id = i.context1
AND i.user_entity_id = ue.user_entity_id
AND rcu.route_id = ue.route_id
AND rcu.sequence_no = 1
AND rcu.context_id = con.context_id
AND con.context_name ||''= 'ASSIGNMENT_ACTION_ID' )
OR EXISTS ( SELECT '1'
FROM ff_contexts con,
ff_route_context_usages rcu,
ff_user_entities ue,
ff_archive_items i
WHERE i.archive_item_id = ic.archive_item_id
AND i.context1 = p_payroll_action_id
AND i.user_entity_id = ue.user_entity_id
AND rcu.route_id = ue.route_id
AND rcu.sequence_no = 1
AND rcu.context_id = con.context_id
AND con.context_name ||''= 'PAYROLL_ACTION_ID' );
DELETE FROM ff_archive_items i
WHERE EXISTS ( SELECT '1'
FROM ff_contexts con,
ff_route_context_usages rcu,
ff_user_entities ue,
pay_assignment_actions assact
WHERE assact.payroll_action_id = p_payroll_action_id
AND assact.assignment_action_id = i.context1
AND i.user_entity_id = ue.user_entity_id
AND rcu.route_id = ue.route_id
AND rcu.sequence_no = 1
AND rcu.context_id = con.context_id
AND con.context_name ||''= 'ASSIGNMENT_ACTION_ID' )
OR EXISTS ( SELECT '1'
FROM ff_contexts con,
ff_route_context_usages rcu,
ff_user_entities ue
WHERE i.context1 = p_payroll_action_id
AND i.user_entity_id = ue.user_entity_id
AND rcu.route_id = ue.route_id
AND rcu.sequence_no = 1
AND rcu.context_id = con.context_id
AND con.context_name ||''= 'PAYROLL_ACTION_ID' );
DELETE FROM pay_assignment_actions
WHERE payroll_action_id = p_payroll_action_id;