[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
delivered in patch 2000669 insert_row so that the startup mode
(either GENERIC, STARTUP or USER) can
be identified. This is required, as a
enhancement request was made where by
functionality for chk_bal_clasification
if different depending on what mode
you are in. In USER mode there is no
change. In STARTUP mode, it is now
possible to feed a balance from more
than one secondary classifcation.
16-NOV-2001 RThirlby 115.3 Added commit to end of file for GSCC
standards.
01-JUL-2002 RCallaghan 115.4 Added checkfile line.
06-DEC-2002 ALogue 115.5 NOCOPY changes. Bug 2692195
============================================================================*/
--
-----------------------------------------------------------------------------
-- Name --
-- chk_balance_classification --
-- Purpose --
-- Make sure the balance classification is unique ie. only one --
-- classification per family is allowed for a balance. --
-- Bug 930964 - It is now possible to have more than one balance --
-- classification per family, when in STARTUP mode. --
-- The restriction will remain for USER mode, to help --
-- prevent users creating duplicate feeds to balances. --
-- Arguments --
-- See below. --
-- Bug 930964 - Added new parameter p_mode. The where clause restriction --
-- alters depending on this value. This should be left null --
-- when in USER mode.
-- Notes --
-----------------------------------------------------------------------------
--
procedure chk_balance_classification
(
p_row_id varchar2,
p_balance_type_id number,
p_classification_id number,
p_mode varchar2 default null
) is
--
cursor csr_unique_bal_classification (p_startup_mode varchar2) is
select ecl.classification_id
from pay_element_classifications ecl
where ecl.classification_id = p_classification_id
and not exists
(select null
from pay_balance_classifications bcl
where bcl.balance_type_id = p_balance_type_id
and bcl.classification_id = ecl.classification_id
and (p_row_id is null or
(p_row_id is not null and
chartorowid(p_row_id) <> bcl.rowid)))
and ((p_startup_mode is null
and ((ecl.parent_classification_id is null and
not exists
(select null
from pay_balance_classifications bcl2,
pay_element_classifications ecl2
where bcl2.balance_type_id = p_balance_type_id
and ecl2.classification_id = bcl2.classification_id
and ecl2.parent_classification_id = ecl.classification_id
and (p_row_id is null or
(p_row_id is not null and
chartorowid(p_row_id) <> bcl2.rowid))))
or (ecl.parent_classification_id is not null and
not exists
(select null
from pay_balance_classifications bcl3,
pay_element_classifications ecl3
where bcl3.balance_type_id = p_balance_type_id
and ecl3.classification_id = bcl3.classification_id
and (ecl3.parent_classification_id =
ecl.parent_classification_id or
ecl3.classification_id =
ecl.parent_classification_id)
and (p_row_id is null or
(p_row_id is not null and
chartorowid(p_row_id) <> bcl3.rowid))))))
or (p_startup_mode is not null));
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Balance_Classification_Id IN OUT NOCOPY NUMBER,
X_Business_Group_Id NUMBER,
X_Legislation_Code VARCHAR2,
X_Balance_Type_Id NUMBER,
X_Classification_Id NUMBER,
X_Scale NUMBER,
X_Legislation_Subgroup VARCHAR2,
X_mode VARCHAR2 default null)
IS
--
CURSOR C IS SELECT rowid FROM pay_balance_classifications
WHERE balance_classification_id = X_Balance_Classification_Id;
CURSOR C2 IS SELECT pay_balance_classifications_s.nextval FROM sys.dual;
INSERT INTO pay_balance_classifications
(balance_classification_id,
business_group_id,
legislation_code,
balance_type_id,
classification_id,
scale,
legislation_subgroup)
VALUES
(X_Balance_Classification_Id,
X_Business_Group_Id,
X_Legislation_Code,
X_Balance_Type_Id,
X_Classification_Id,
X_Scale,
X_Legislation_Subgroup);
'pay_bal_classifications_pkg.insert_row');
END Insert_Row;
CURSOR C IS SELECT * FROM pay_balance_classifications
WHERE rowid = X_Rowid FOR UPDATE of Balance_Classification_Id
NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Balance_Classification_Id NUMBER,
X_Business_Group_Id NUMBER,
X_Legislation_Code VARCHAR2,
X_Balance_Type_Id NUMBER,
X_Classification_Id NUMBER,
X_Scale NUMBER,
X_Legislation_Subgroup VARCHAR2) IS
BEGIN
--
UPDATE pay_balance_classifications
SET balance_classification_id = X_Balance_Classification_Id,
business_group_id = X_Business_Group_Id,
legislation_code = X_Legislation_Code,
balance_type_id = X_Balance_Type_Id,
classification_id = X_Classification_Id,
scale = X_Scale,
legislation_subgroup = X_Legislation_Subgroup
WHERE rowid = X_rowid;
'pay_bal_classifications_pkg.update_row');
('UPDATE',
X_Balance_Classification_Id,
X_Scale);
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2,
-- Extra Columns
X_Balance_Classification_Id NUMBER) IS
BEGIN
--
-- Remove all balance feeds that are linked to the balance classification.
--
hr_balance_feeds.upd_del_bf_bal_class
('DELETE',
X_Balance_Classification_Id,
null);
DELETE FROM pay_balance_classifications
WHERE rowid = X_Rowid;
'pay_bal_classifications_pkg.delete_row');
END Delete_Row;