[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
01-NOV-96 GPERRY 40.1 Added Insert_Lookup for seeding
1099R relevant lookups. Added Write as
a debugging tool for use when testing 1099R
reports on a site or internally.
06-NOV-96 GPERRY 40.2 Added function date_earned.
13-NOV-96 GPERRY 40.3 Removed function call to hr_api as not
valid on QA database.
17-DEC-96 HEKIM 40.4 Fixed block definition structure viewer
26-FEB-97 HEKIM 40.5 Changed message name from HR_ to PAY_
20-MAR-97 HEKIM 40.6 Added udf_Exists and Delete_udf.
14-JUL-97 HEKIM 40.7 Change message numbers to 5003x range
29/07/97 mfender 110.2 Corected untranslatable date formats
08-APR-99 DJOSHI Verfied and converted for Canonical
Complience of Date
18-jun-1999 achauhan 115.2 replaced dbms_output with
hr_utility.trace
17-aug-1999 rthakur 115.3 Added function get_parameter.
07-jan-2000 vmehta 115.4 Modified function get_parameter
to take care of the condition
where the second parameter is
passed in as null. bug 1069642
18-jan-2002 fusman 115.5 Changed the default date from 01/01/1996
to 01/01/1901 for p_creation_date,p_last_update_date
and p_effective_date.Also added dbdrv commands.
02-jul-2002 fusman 115.6 Bug:2296797 Added legislation code.
25-Apr-2005 sackumar 115.8 Bug 4055762 introduce ltrim,rtrim function
in get_parameter function.
*/
-----------------------------------------------------------------------------
-- Name
-- Write
-- Purpose
-- Provides debugging information that can be picked up when running the
-- concurrent process from SRS. Write output to the TEST_1099R table that
-- should exist with the following columns.
-- SEQUENCE NUMBER
-- TEXT VARCHAR2(240)
-- Due to the fact that the debugging table will not be shipped as part
-- of the installation the write information will be editted out.
-- Arguments
-- p_action denotes action type (I,D) Insert or Delete
-- p_sequence denotes sequence number of insertion
-- p_message denotes message to be written to table
-- p_write_mode denotes whether info is written to table (default is FALSE)
-- Notes
-- For a non-test site this should never write to the TEST_1099R table.
-----------------------------------------------------------------------------
PROCEDURE Write (p_action IN VARCHAR2,
p_sequence IN NUMBER DEFAULT NULL,
p_message IN VARCHAR2 DEFAULT NULL,
p_write_mode IN BOOLEAN DEFAULT TRUE ) IS
BEGIN
--
IF upper(p_action) = 'D' THEN
--
-- Clear down table
--
--deLETE FROM TEST_1099R;
SELECT MAX(paf.effective_end_date)
INTO l_max_assignment_date
FROM per_assignments_f paf
WHERE paf.assignment_id = p_assignment_id;
SELECT formula_id
FROM ff_formulas_f
WHERE legislation_code = p_legislation_code
AND formula_name = UPPER(p_formula_name)
AND p_session_date BETWEEN effective_start_date
AND effective_end_date;
SELECT report_format
FROM pay_report_format_mappings_f
WHERE report_type = p_report_type
AND report_qualifier = p_state
AND p_period_end BETWEEN effective_start_date AND effective_end_date;
SELECT fnd_number.canonical_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 = 'US'; /* Bug: 2296797 */
SELECT sr.jurisdiction_code
FROM pay_state_rules sr
WHERE sr.state_code = p_state;
SELECT payroll_action_id
FROM pay_payroll_actions ppa
WHERE ppa.business_group_id = p_business_group_id
AND ppa.effective_date = p_period_end
AND ppa.legislative_parameters like
'USMAGTAPE'|| '-' ||
lpad(p_report_type, 5)||'-'||
lpad(p_state, 5) || '%';
UPDATE pay_payroll_actions pa
SET pa.action_status = 'E'
WHERE pa.payroll_action_id = p_payroll_action_id;
PROCEDURE Update_Action_Status ( p_payroll_action_id NUMBER ) IS
--
BEGIN
--
-- Sets the payroll action to a status of 'C'omplete.
--
hr_utility.set_location('pay_mag_utils.update_action_status',1);
UPDATE pay_payroll_actions pa
SET pa.action_status = 'C'
WHERE pa.payroll_action_id = p_payroll_action_id;
hr_utility.set_location('pay_mag_utils.update_action_status',2);
UPDATE pay_assignment_actions aa
SET aa.action_status = 'C'
WHERE aa.payroll_action_id = p_payroll_action_id
AND aa.action_status = 'U';
END Update_Action_Status;
select pay_payroll_actions_s.nextval
into l_payroll_action_id
from sys.dual;
INSERT INTO pay_payroll_actions
(payroll_action_id,
action_type,
business_group_id,
action_population_status,
action_status,
effective_date,
date_earned,
legislative_parameters,
object_version_number )
VALUES
( l_payroll_action_id,
'X', -- (X) Magnetic Report
p_business_group_id,
'U', -- (U)npopulated
'U', -- (U)nprocessed
p_period_end,
p_period_end,
l_legislative_parms,
1);
SELECT aa.assignment_action_id
FROM pay_assignment_actions aa
WHERE aa.payroll_action_id = p_payroll_action_id
AND aa.assignment_id = p_assignment_id
AND aa.tax_unit_id IS NULL;
UPDATE pay_assignment_actions aa
SET aa.tax_unit_id = p_tax_unit_id
WHERE aa.assignment_action_id = l_assignment_action_id;
PROCEDURE Insert_Lookup
(p_lookup_code in varchar2,
p_lookup_type in varchar2,
p_application_id in number default 800,
p_created_by in number default 1,
p_creation_date in date default to_date('01/01/1901','DD/MM/YYYY'),
p_enabled_flag in varchar2 default 'Y',
p_last_updated_by in number default 1,
p_last_update_date in date default to_date('01/01/1901','DD/MM/YYYY'),
p_meaning in varchar2,
p_effective_date in date default to_date('01/01/1901','DD/MM/YYYY')) IS
--
l_dummy VARCHAR2(1);
SELECT NULL
FROM HR_LOOKUPS HR
WHERE HR.lookup_type = p_lookup_type
AND HR.lookup_code = p_lookup_code;
INSERT INTO hr_lookups
(lookup_code,
lookup_type,
application_id,
created_by,
creation_date,
enabled_flag,
last_updated_by,
last_update_date,
meaning)
VALUES
(p_lookup_code,
p_lookup_type,
p_application_id,
p_created_by,
p_creation_date,
p_enabled_flag,
p_last_updated_by,
p_last_update_date,
p_meaning);
END Insert_Lookup;
g_message := 'Inserting format for '||p_block_name;
hr_utility.trace('Inserting format for '||p_block_name);
SELECT pmb.block_name block_name,
pmb.main_block_flag main_block_flag,
pmb2.block_name next_block_name,
pmr.next_block_id next_block_id,
ff.formula_name formula_name
FROM pay_magnetic_blocks pmb,
pay_magnetic_records pmr,
pay_magnetic_blocks pmb2,
ff_formulas_f ff
WHERE pmb.magnetic_block_id = p_magnetic_block_id
AND pmb.magnetic_block_id = pmr.magnetic_block_id
AND pmr.next_block_id = pmb2.magnetic_block_id (+)
AND pmr.formula_id = ff.formula_id
ORDER by pmr.sequence;
SELECT null
FROM HR_ORG_INFORMATION_TYPES hoit
WHERE hoit.org_information_type = p_org_info_type;
SELECT null
FROM pay_magnetic_blocks pmb
WHERE pmb.report_format = p_report_format;
SELECT pmb.magnetic_block_id
FROM pay_magnetic_blocks pmb
WHERE pmb.report_format = p_report_format
AND pmb.main_block_flag = 'Y';
select function_id
into l_udf_id
from ff_functions
where upper(name) = upper(p_udf_name)
and business_group_id is null
and legislation_code = 'US';
PROCEDURE Delete_udf (p_udf_name in varchar2) IS
l_udf_id number(9) := 0;
delete from ff_function_parameters
where function_id = l_udf_id;
delete from ff_function_context_usages
where function_id = l_udf_id;
delete from ff_functions
where function_id = l_udf_id;
end Delete_udf;