[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
02-Oct-95 D.Kerr 40.7 310643 : overloads for insert_row,
update_row and delete_row
05-Mar-97 J.Alloun 40.8 Changed all occurances of system.dual
to sys.dual for next release requirements.
08-Dec-03 T.Habara 115.1 Bug 3285363. Modified cursor C3 in
insert_row().
Added commit, dbdrv, whenever oserror.
Added nocopy changes.
26-APR-04 A.Logue 115.2 Performance fix to C1 in
check_run_result_usage.
============================================================================*/
-----------------------------------------------------------------------------
-- Name --
-- Insert_Row --
-- Purpose --
-- Table handler procedure that supports the insert of a balance feed --
-- via the Define Balance Type form. --
-- Arguments --
-- See below. --
-- Notes --
-----------------------------------------------------------------------------
--
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Balance_Feed_Id IN OUT NOCOPY NUMBER,
X_Effective_Start_Date DATE,
X_Effective_End_Date DATE,
X_Business_Group_Id NUMBER,
X_Legislation_Code VARCHAR2,
X_Balance_Type_Id NUMBER,
X_Input_Value_Id NUMBER,
X_Scale NUMBER,
X_Legislation_Subgroup VARCHAR2 ) IS
BEGIN
Insert_Row(X_Rowid => X_Rowid,
X_Balance_Feed_Id => X_Balance_Feed_Id,
X_Effective_Start_Date => X_Effective_Start_Date,
X_Effective_End_Date => X_Effective_End_Date,
X_Business_Group_Id => X_Business_Group_Id,
X_Legislation_Code => X_Legislation_Code,
X_Balance_Type_Id => X_Balance_Type_Id ,
X_Input_Value_Id => X_Input_Value_Id,
X_Scale => X_Scale,
X_Legislation_Subgroup => X_Legislation_Subgroup ,
X_Initial_Balance_Feed => FALSE ) ;
END Insert_Row;
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Balance_Feed_Id IN OUT NOCOPY NUMBER,
X_Effective_Start_Date DATE,
X_Effective_End_Date DATE,
X_Business_Group_Id NUMBER,
X_Legislation_Code VARCHAR2,
X_Balance_Type_Id NUMBER,
X_Input_Value_Id NUMBER,
X_Scale NUMBER,
X_Legislation_Subgroup VARCHAR2,
X_Initial_Balance_Feed BOOLEAN ) IS
--
l_balance_feed_id number:= X_Balance_Feed_Id;
CURSOR C IS SELECT rowid FROM pay_balance_feeds_f
WHERE balance_feed_id = l_balance_feed_id;
CURSOR C2 IS SELECT pay_balance_feeds_s.nextval FROM sys.dual;
CURSOR C3 IS SELECT 'Y'
FROM pay_balance_feeds_f blf,
pay_input_values_f inv,
pay_element_types_f elt,
pay_element_classifications ec
WHERE blf.balance_type_id = X_Balance_Type_Id
AND blf.input_value_id = inv.input_value_id
AND inv.element_type_id = elt.element_type_id
AND nvl(elt.legislation_code
,nvl(l_legislation_code, '~nvl~'))
= nvl(l_legislation_code, '~nvl~')
AND nvl(elt.business_group_id
,nvl(X_Business_Group_Id, -1))
= nvl(X_Business_Group_Id, -1)
AND elt.classification_id = ec.classification_id
AND ec.balance_initialization_flag = 'Y';
INSERT INTO pay_balance_feeds_f
(balance_feed_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
balance_type_id,
input_value_id,
scale,
legislation_subgroup)
VALUES
(l_balance_feed_id,
X_Effective_Start_Date,
X_Effective_End_Date,
X_Business_Group_Id,
X_Legislation_Code,
X_Balance_Type_Id,
X_Input_Value_Id,
X_Scale,
X_Legislation_Subgroup);
'pay_balance_feeds_f_pkg.insert_row');
END Insert_Row;
CURSOR C IS SELECT * FROM pay_balance_feeds_f
WHERE rowid = X_Rowid FOR UPDATE of Balance_Feed_Id NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Balance_Feed_Id NUMBER,
X_Effective_Start_Date DATE,
X_Effective_End_Date DATE,
X_Business_Group_Id NUMBER,
X_Legislation_Code VARCHAR2,
X_Balance_Type_Id NUMBER,
X_Input_Value_Id NUMBER,
X_Scale NUMBER,
X_Legislation_Subgroup VARCHAR2 ) IS
BEGIN
Update_Row(X_Rowid => X_Rowid,
X_Balance_Feed_Id => X_Balance_Feed_Id,
X_Effective_Start_Date => X_Effective_Start_Date,
X_Effective_End_Date => X_Effective_End_Date,
X_Business_Group_Id => X_Business_Group_Id,
X_Legislation_Code => X_Legislation_Code,
X_Balance_Type_Id => X_Balance_Type_Id,
X_Input_Value_Id => X_Input_Value_Id,
X_Scale => X_Scale,
X_Legislation_Subgroup => X_Legislation_Subgroup,
X_Initial_Balance_Feed => FALSE );
END Update_Row ;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Balance_Feed_Id NUMBER,
X_Effective_Start_Date DATE,
X_Effective_End_Date DATE,
X_Business_Group_Id NUMBER,
X_Legislation_Code VARCHAR2,
X_Balance_Type_Id NUMBER,
X_Input_Value_Id NUMBER,
X_Scale NUMBER,
X_Legislation_Subgroup VARCHAR2,
X_Initial_Balance_Feed BOOLEAN) IS
BEGIN
--
-- Lock balance type to stop other users changing the balance feed.
--
hr_balance_feeds.lock_balance_type(X_Balance_Type_Id);
UPDATE pay_balance_feeds_f
SET balance_feed_id = X_Balance_Feed_Id,
effective_start_date = X_Effective_Start_Date,
effective_end_date = X_Effective_End_Date,
business_group_id = X_Business_Group_Id,
legislation_code = X_Legislation_Code,
balance_type_id = X_Balance_Type_Id,
input_value_id = X_Input_Value_Id,
scale = X_Scale,
legislation_subgroup = X_Legislation_Subgroup
WHERE rowid = X_rowid;
'pay_balance_feeds_f_pkg.update_row');
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2,
-- Extra Columns
X_Balance_Type_Id NUMBER ) IS
BEGIN
Delete_Row(X_Rowid => X_Rowid,
X_Balance_Type_Id => X_Balance_Type_Id,
X_Input_Value_Id => NULL,
X_Initial_Balance_Feed => FALSE ) ;
END Delete_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2,
-- Extra Columns
X_Balance_Type_Id NUMBER,
X_Input_Value_Id NUMBER,
X_Initial_Balance_Feed BOOLEAN) IS
BEGIN
--
-- Lock balance type to stop other users changing the balance feed.
--
hr_balance_feeds.lock_balance_type(X_Balance_Type_Id);
DELETE FROM pay_balance_feeds_f
WHERE rowid = X_Rowid;
'pay_balance_feeds_f_pkg.delete_row');
END Delete_Row;
CURSOR C1 IS SELECT 1
FROM pay_run_result_values rrv
WHERE rrv.input_value_id = X_Input_Value_Id ;