[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM pay_balance_types_tl bttl,
pay_balance_types bt
WHERE ((p_mode = 'BALANCE_NAME' and
upper(bttl.balance_name) = upper(translate(p_balance_name,
'_',' '))) or
(p_mode = 'REPORTING_NAME' and
upper(bttl.reporting_name) = upper(translate(p_reporting_name,
'_',' '))))
AND bttl.balance_type_id = bt.balance_type_id
AND bttl.language = p_language
AND ( bt.balance_type_id <> p_balance_type_id OR p_balance_type_id is null )
AND ( g_business_group_id = bt.business_group_id + 0 OR g_business_group_id is null )
AND ( g_legislation_code = bt.legislation_code OR g_legislation_code is null );
select pbg.legislation_code
from per_business_groups pbg
where pbg.business_group_id = p_bg_id;
select rule_mode
from pay_legislation_rules
where rule_type = 'BAL_CATEGORY_MANDATORY'
and legislation_code = p_leg_code;
select bt.balance_type_id
from pay_balance_types bt
, per_business_groups_perf bg
where ((p_mode = 'BALANCE_NAME'
and upper(bt.balance_name) = upper(translate(p_balance_name,'_',' ')))
or (p_mode = 'REPORTING_NAME'
and upper(bt.reporting_name) = upper(translate(p_reporting_name,'_',' ')))
or (p_mode = 'ASSIGNMENT_RENUMERATION_ALLOWED_FLAG'
and bt.assignment_remuneration_flag = 'Y'))
and bt.business_group_id = bg.business_group_id (+)
and ((p_business_group_id is not null
and nvl(bt.business_group_id,-1) = p_business_group_id
or nvl(bt.legislation_code,' ') = v_bg_leg_code)
or (p_legislation_code is not null
and nvl(bt.legislation_code,' ') = p_legislation_code
or bt.business_group_id is not null
and bt.legislation_code = p_legislation_code)
or bt.business_group_id is null
and bt.legislation_code is null)
and (p_row_id is null
or (p_row_id is not null
and chartorowid(p_row_id) <> bt.rowid));
select legislation_code
from per_business_groups
where business_group_id = p_business_group_id;
procedure balance_type_cascade_delete
(
p_balance_type_id number
) is
--
cursor get_pbas(p_def_bal number) is
select balance_attribute_id
from pay_balance_attributes
where defined_balance_id = p_def_bal;
select db.defined_balance_id
from pay_defined_balances db
where db.balance_type_id = p_balance_type_id
for update;
hr_utility.set_location('Entering balance_type_cascade_delete', 5);
delete from pay_balance_feeds_f bf
where bf.balance_type_id = p_balance_type_id;
hr_utility.set_location('balance_type_cascade_delete', 10);
delete from pay_balance_classifications bc
where bc.balance_type_id = p_balance_type_id;
hr_utility.set_location('balance_type_cascade_delete', 15);
hr_utility.set_location('balance_type_cascade_delete', 20);
pay_defined_balances_pkg.chk_delete_defined_balance
(v_db_rec.defined_balance_id);
hr_utility.set_location('balance_type_cascade_delete',2);
pay_balance_attribute_api.delete_balance_attribute
(p_balance_attribute_id => each_pba.balance_attribute_id);
hr_utility.set_location('balance_type_cascade_delete',3);
delete from pay_defined_balances
where current of csr_def_bals;
end balance_type_cascade_delete;
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Balance_Type_Id IN OUT NOCOPY NUMBER,
X_Business_Group_Id NUMBER,
X_Legislation_Code VARCHAR2,
X_Currency_Code VARCHAR2,
X_Assignment_Remuneration_Flag VARCHAR2,
X_Balance_Name VARCHAR2,
-- --
X_Base_Balance_Name VARCHAR2,
-- --
X_Balance_Uom VARCHAR2,
X_Comments VARCHAR2,
X_Legislation_Subgroup VARCHAR2,
X_Reporting_Name VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Attribute16 VARCHAR2,
X_Attribute17 VARCHAR2,
X_Attribute18 VARCHAR2,
X_Attribute19 VARCHAR2,
X_Attribute20 VARCHAR2,
x_balance_category_id number default null,
x_base_balance_type_id number default null,
x_input_value_id number default null)
IS
--
CURSOR C IS SELECT rowid FROM pay_balance_types
WHERE balance_type_id = X_Balance_Type_Id;
CURSOR C2 IS SELECT pay_balance_types_s.nextval FROM sys.dual;
hr_utility.set_location('pay_balance_types_pkg.insert_row', 10);
INSERT INTO pay_balance_types
(balance_type_id,
business_group_id,
legislation_code,
currency_code,
assignment_remuneration_flag,
balance_name,
balance_uom,
comments,
legislation_subgroup,
reporting_name,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
balance_category_id,
base_balance_type_id,
input_value_id)
VALUES
(X_Balance_Type_Id,
X_Business_Group_Id,
X_Legislation_Code,
X_Currency_Code,
X_Assignment_Remuneration_Flag,
--X_Balance_Name,
-- --
X_Base_Balance_Name,
-- --
X_Balance_Uom,
X_Comments,
X_Legislation_Subgroup,
X_Reporting_Name,
X_Attribute_Category,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Attribute6,
X_Attribute7,
X_Attribute8,
X_Attribute9,
X_Attribute10,
X_Attribute11,
X_Attribute12,
X_Attribute13,
X_Attribute14,
X_Attribute15,
X_Attribute16,
X_Attribute17,
X_Attribute18,
X_Attribute19,
X_Attribute20,
x_balance_category_id,
x_base_balance_type_id,
x_input_value_id);
insert into PAY_BALANCE_TYPES_TL (
BALANCE_TYPE_ID,
BALANCE_NAME,
REPORTING_NAME,
LAST_UPDATE_DATE,
CREATION_DATE,
LANGUAGE,
SOURCE_LANG
) select
X_Balance_Type_Id,
X_Balance_Name,
X_Reporting_Name,
sysdate,
sysdate,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from PAY_BALANCE_TYPES_TL T
where T.BALANCE_TYPE_ID = X_Balance_Type_Id
and T.LANGUAGE = L.LANGUAGE_CODE);
'pay_balance_types_pkg.insert_row');
END Insert_Row;
CURSOR C IS SELECT * FROM pay_balance_types
WHERE rowid = X_Rowid FOR UPDATE of Balance_Type_Id NOWAIT;
select BALANCE_NAME,
REPORTING_NAME,
decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
from PAY_BALANCE_TYPES_TL
where BALANCE_TYPE_ID = X_BALANCE_TYPE_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
for update of BALANCE_TYPE_ID nowait;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Balance_Type_Id NUMBER,
X_Business_Group_Id NUMBER,
X_Legislation_Code VARCHAR2,
X_Currency_Code VARCHAR2,
X_Assignment_Remuneration_Flag VARCHAR2,
X_Balance_Name VARCHAR2,
X_Base_Balance_Name VARCHAR2,
X_Balance_Uom VARCHAR2,
X_Comments VARCHAR2,
X_Legislation_Subgroup VARCHAR2,
X_Reporting_Name VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Attribute16 VARCHAR2,
X_Attribute17 VARCHAR2,
X_Attribute18 VARCHAR2,
X_Attribute19 VARCHAR2,
X_Attribute20 VARCHAR2,
x_balance_category_id number default null,
x_base_balance_type_id number default null,
x_input_value_id number default null)
IS
--
BEGIN
--
-- Make sure balance type is valid ie. unique name only one remuneration
-- balance etc ...
chk_balance_type
(X_Rowid,
X_Business_Group_Id,
X_Legislation_Code,
X_Balance_Name,
X_Reporting_Name,
X_Assignment_Remuneration_Flag);
hr_utility.set_location('pay_balance_types_pkg.insert_row', 10);
UPDATE pay_balance_types
SET balance_type_id = X_Balance_Type_Id,
business_group_id = X_Business_Group_Id,
legislation_code = X_Legislation_Code,
currency_code = X_Currency_Code,
assignment_remuneration_flag = X_Assignment_Remuneration_Flag,
-- --
balance_name = X_Base_Balance_Name,
-- --
balance_uom = X_Balance_Uom,
comments = X_Comments,
legislation_subgroup = X_Legislation_Subgroup,
reporting_name = X_Reporting_Name,
attribute_category = X_Attribute_Category,
attribute1 = X_Attribute1,
attribute2 = X_Attribute2,
attribute3 = X_Attribute3,
attribute4 = X_Attribute4,
attribute5 = X_Attribute5,
attribute6 = X_Attribute6,
attribute7 = X_Attribute7,
attribute8 = X_Attribute8,
attribute9 = X_Attribute9,
attribute10 = X_Attribute10,
attribute11 = X_Attribute11,
attribute12 = X_Attribute12,
attribute13 = X_Attribute13,
attribute14 = X_Attribute14,
attribute15 = X_Attribute15,
attribute16 = X_Attribute16,
attribute17 = X_Attribute17,
attribute18 = X_Attribute18,
attribute19 = X_Attribute19,
attribute20 = X_Attribute20,
balance_category_id = x_balance_category_id,
base_balance_type_id = x_base_balance_type_id,
input_value_id = x_input_value_id
WHERE rowid = X_rowid;
'pay_balance_types_pkg.update_row');
update PAY_BALANCE_TYPES_TL
set BALANCE_NAME = X_BALANCE_NAME,
REPORTING_NAME = X_REPORTING_NAME,
LAST_UPDATE_DATE = sysdate,
SOURCE_LANG = userenv('LANG')
where BALANCE_TYPE_ID = X_BALANCE_TYPE_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
if (sql%notfound) then -- trap system errors during update
hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
hr_utility.set_message_token ('PROCEDURE','PAY_BALANCE_TYPES_PKG.UPDATE_TL_ROW');
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2,
-- Extra Columns
X_Balance_Type_Id NUMBER) IS
--
BEGIN
--
-- Remove balance feeds, balance classifications and defined balances.
balance_type_cascade_delete(X_Balance_Type_Id);
DELETE FROM pay_balance_types
WHERE rowid = X_Rowid;
'pay_balance_types_pkg.delete_row');
delete from PAY_BALANCE_TYPES_TL
where BALANCE_TYPE_ID = X_BALANCE_TYPE_ID;
hr_utility.set_message_token ('PROCEDURE','PAY_BALANCE_TYPES_PKG.DELETE_TL_ROW');
END Delete_Row;
delete from PAY_BALANCE_TYPES_TL T
where not exists
(select NULL
from PAY_BALANCE_TYPES B
where B.BALANCE_TYPE_ID = T.BALANCE_TYPE_ID
);
update PAY_BALANCE_TYPES_TL T set (
BALANCE_NAME,
REPORTING_NAME
) = (select
B.BALANCE_NAME,
B.REPORTING_NAME
from PAY_BALANCE_TYPES_TL B
where B.BALANCE_TYPE_ID = T.BALANCE_TYPE_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.BALANCE_TYPE_ID,
T.LANGUAGE
) in (select
SUBT.BALANCE_TYPE_ID,
SUBT.LANGUAGE
from PAY_BALANCE_TYPES_TL SUBB, PAY_BALANCE_TYPES_TL SUBT
where SUBB.BALANCE_TYPE_ID = SUBT.BALANCE_TYPE_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.BALANCE_NAME <> SUBT.BALANCE_NAME
or SUBB.REPORTING_NAME <> SUBT.REPORTING_NAME
or (SUBB.REPORTING_NAME is null and SUBT.REPORTING_NAME is not null)
or (SUBB.REPORTING_NAME is not null and SUBT.REPORTING_NAME is null)
));
insert into PAY_BALANCE_TYPES_TL (
BALANCE_TYPE_ID,
BALANCE_NAME,
REPORTING_NAME,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LANGUAGE,
SOURCE_LANG
) select
B.BALANCE_TYPE_ID,
B.BALANCE_NAME,
B.REPORTING_NAME,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
B.CREATED_BY,
B.CREATION_DATE,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from PAY_BALANCE_TYPES_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from PAY_BALANCE_TYPES_TL T
where T.BALANCE_TYPE_ID = B.BALANCE_TYPE_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
SELECT count(*) INTO result
FROM pay_balance_types
WHERE nvl(BALANCE_NAME,'~null~') = nvl(X_B_BALANCE_NAME,'~null~')
and nvl(LEGISLATION_CODE,'~null~') = nvl(X_B_LEGISLATION_CODE,'~null~')
and BUSINESS_GROUP_ID is NULL;
select balance_type_id
from pay_balance_types
where nvl(balance_name,'~null~')=nvl(x_b_balance_name,'~null~')
and nvl(legislation_code,'~null~') = nvl(x_b_legislation_code,'~null~')
and business_group_id is null
;
select balance_name
, language
from pay_balance_types_tl
where balance_type_id = p_balance_type_id
and p_language in (language, source_lang)
;
UPDATE pay_balance_types_tl
SET balance_name = nvl(x_balance_name,balance_name),
reporting_name = nvl(x_reporting_name,reporting_name),
last_update_date = Sysdate,
last_updated_by = decode(x_owner,'SEED',1,0),
last_update_login = 0,
source_lang = userenv('LANG')
WHERE userenv('LANG') IN (language,source_lang)
AND balance_type_id = l_balance_type_id
;