[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rg_dss_variables_s.nextval
INTO new_id
FROM sys.dual;
SELECT COUNT(variable_id)
INTO NumRecords
FROM rg_dss_var_dimensions
WHERE variable_id = X_Variable_Id;
SELECT 1
INTO dummy
FROM dual
WHERE NOT EXISTS
(SELECT 1
FROM rg_dss_systems sys,
rg_dss_system_variables svr
WHERE svr.variable_id = X_Variable_Id
AND svr.system_id > 0
AND svr.system_id = sys.system_id
AND sys.freeze_flag = 'Y');
SELECT 1
INTO dummy
FROM dual
WHERE NOT EXISTS
(SELECT 1
FROM rg_dss_var_dimensions vdm,
rg_dss_dimensions dim
WHERE vdm.variable_id = X_Variable_Id
AND vdm.dimension_id = dim.dimension_id
AND dim.dimension_type = 'T' );
SELECT 1
INTO dummy
FROM dual
WHERE NOT EXISTS
(SELECT 1
FROM rg_dss_var_dimensions vdm,
rg_dss_dimensions dim
WHERE vdm.variable_id = X_Variable_Id
AND vdm.dimension_id = dim.dimension_id
AND dim.level_code = 'S' );
SELECT 1
INTO dummy
FROM rg_dss_variables
WHERE name = X_Name
AND ((X_Rowid IS NULL) OR (rowid <> X_Rowid));
SELECT 1
INTO dummy
FROM rg_dss_variables
WHERE object_name = X_Object_Name
AND ((X_Rowid IS NULL) OR (rowid <> X_Rowid));
SELECT 1
INTO dummy
FROM dual
WHERE NOT EXISTS
(SELECT 1
FROM rg_dss_system_variables
WHERE variable_id = X_Variable_Id
);
SELECT application_column_name, 'ANY'
FROM fnd_id_flex_segments
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND enabled_flag = 'Y'
AND id_flex_num = X_Chart_of_Account_Id;
SELECT decode(dim.level_code,'D','D','S','R','D')
INTO seg_value
FROM rg_dss_dimensions dim,
rg_dss_dim_segments ds,
rg_dss_var_dimensions vd
WHERE ds.dimension_id = vd.dimension_id
AND vd.variable_id = X_Variable_Id
AND ds.application_column_name = seg_name
AND vd.dimension_id = dim.dimension_id;
SELECT 'DR'
INTO seg_value
FROM rg_dss_var_selections vs
WHERE
vs.variable_id = X_Variable_Id
AND vs.application_column_name = seg_name;
PROCEDURE insert_row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Variable_Id IN OUT NOCOPY NUMBER,
X_Name VARCHAR2,
X_Object_Name VARCHAR2,
X_Column_Label VARCHAR2,
X_Balance_Type VARCHAR2,
X_Currency_Type VARCHAR2,
X_Currency_Code VARCHAR2,
X_Id_Flex_Code VARCHAR2,
X_Id_Flex_Num NUMBER,
X_Ledger_Id NUMBER,
X_Budget_Version_Id NUMBER,
X_Encumbrance_Type_Id NUMBER,
X_Level_Code VARCHAR2,
X_Status_Code VARCHAR2,
X_Description VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Context 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_Segment1_Type IN OUT NOCOPY VARCHAR2,
X_Segment2_Type IN OUT NOCOPY VARCHAR2,
X_Segment3_Type IN OUT NOCOPY VARCHAR2,
X_Segment4_Type IN OUT NOCOPY VARCHAR2,
X_Segment5_Type IN OUT NOCOPY VARCHAR2,
X_Segment6_Type IN OUT NOCOPY VARCHAR2,
X_Segment7_Type IN OUT NOCOPY VARCHAR2,
X_Segment8_Type IN OUT NOCOPY VARCHAR2,
X_Segment9_Type IN OUT NOCOPY VARCHAR2,
X_Segment10_Type IN OUT NOCOPY VARCHAR2,
X_Segment11_Type IN OUT NOCOPY VARCHAR2,
X_Segment12_Type IN OUT NOCOPY VARCHAR2,
X_Segment13_Type IN OUT NOCOPY VARCHAR2,
X_Segment14_Type IN OUT NOCOPY VARCHAR2,
X_Segment15_Type IN OUT NOCOPY VARCHAR2,
X_Segment16_Type IN OUT NOCOPY VARCHAR2,
X_Segment17_Type IN OUT NOCOPY VARCHAR2,
X_Segment18_Type IN OUT NOCOPY VARCHAR2,
X_Segment19_Type IN OUT NOCOPY VARCHAR2,
X_Segment20_Type IN OUT NOCOPY VARCHAR2,
X_Segment21_Type IN OUT NOCOPY VARCHAR2,
X_Segment22_Type IN OUT NOCOPY VARCHAR2,
X_Segment23_Type IN OUT NOCOPY VARCHAR2,
X_Segment24_Type IN OUT NOCOPY VARCHAR2,
X_Segment25_Type IN OUT NOCOPY VARCHAR2,
X_Segment26_Type IN OUT NOCOPY VARCHAR2,
X_Segment27_Type IN OUT NOCOPY VARCHAR2,
X_Segment28_Type IN OUT NOCOPY VARCHAR2,
X_Segment29_Type IN OUT NOCOPY VARCHAR2,
X_Segment30_Type IN OUT NOCOPY VARCHAR2
) IS
CURSOR C IS
SELECT rowid
FROM rg_dss_variables
WHERE variable_id = X_Variable_Id;
INSERT INTO rg_dss_variables(
variable_id,
name,
object_name,
column_label,
balance_type,
currency_type,
currency_code,
id_flex_code,
id_flex_num,
ledger_id,
budget_version_id,
encumbrance_type_id,
level_code,
status_code,
description,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
segment1_type,
segment2_type,
segment3_type,
segment4_type,
segment5_type,
segment6_type,
segment7_type,
segment8_type,
segment9_type,
segment10_type,
segment11_type,
segment12_type,
segment13_type,
segment14_type,
segment15_type,
segment16_type,
segment17_type,
segment18_type,
segment19_type,
segment20_type,
segment21_type,
segment22_type,
segment23_type,
segment24_type,
segment25_type,
segment26_type,
segment27_type,
segment28_type,
segment29_type,
segment30_type
) VALUES (
X_Variable_Id,
X_Name,
X_Object_Name,
X_Column_Label,
X_Balance_Type,
X_Currency_Type,
X_Currency_Code,
X_Id_Flex_Code,
X_Id_Flex_Num,
X_Ledger_Id,
X_Budget_Version_Id,
X_Encumbrance_Type_Id,
X_Level_Code,
X_Status_Code,
X_Description,
X_Last_Update_Date,
X_Last_Updated_By,
X_Last_Update_Login,
X_Creation_Date,
X_Created_By,
X_Context,
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_Segment1_Type,
X_Segment2_Type,
X_Segment3_Type,
X_Segment4_Type,
X_Segment5_Type,
X_Segment6_Type,
X_Segment7_Type,
X_Segment8_Type,
X_Segment9_Type,
X_Segment10_Type,
X_Segment11_Type,
X_Segment12_Type,
X_Segment13_Type,
X_Segment14_Type,
X_Segment15_Type,
X_Segment16_Type,
X_Segment17_Type,
X_Segment18_Type,
X_Segment19_Type,
X_Segment20_Type,
X_Segment21_Type,
X_Segment22_Type,
X_Segment23_Type,
X_Segment24_Type,
X_Segment25_Type,
X_Segment26_Type,
X_Segment27_Type,
X_Segment28_Type,
X_Segment29_Type,
X_Segment30_Type
);
END Insert_Row;
SELECT *
FROM rg_dss_variables
WHERE rowid = X_Rowid
FOR UPDATE of variable_id NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Variable_Id NUMBER,
X_Name VARCHAR2,
X_Object_Name VARCHAR2,
X_Column_Label VARCHAR2,
X_Balance_Type VARCHAR2,
X_Currency_Type VARCHAR2,
X_Currency_Code VARCHAR2,
X_Id_Flex_Code VARCHAR2,
X_Id_Flex_Num NUMBER,
X_Ledger_Id NUMBER,
X_Budget_Version_Id NUMBER,
X_Encumbrance_Type_Id NUMBER,
X_Level_Code VARCHAR2,
X_Status_Code VARCHAR2,
X_Description VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Context 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_Segment1_Type IN OUT NOCOPY VARCHAR2,
X_Segment2_Type IN OUT NOCOPY VARCHAR2,
X_Segment3_Type IN OUT NOCOPY VARCHAR2,
X_Segment4_Type IN OUT NOCOPY VARCHAR2,
X_Segment5_Type IN OUT NOCOPY VARCHAR2,
X_Segment6_Type IN OUT NOCOPY VARCHAR2,
X_Segment7_Type IN OUT NOCOPY VARCHAR2,
X_Segment8_Type IN OUT NOCOPY VARCHAR2,
X_Segment9_Type IN OUT NOCOPY VARCHAR2,
X_Segment10_Type IN OUT NOCOPY VARCHAR2,
X_Segment11_Type IN OUT NOCOPY VARCHAR2,
X_Segment12_Type IN OUT NOCOPY VARCHAR2,
X_Segment13_Type IN OUT NOCOPY VARCHAR2,
X_Segment14_Type IN OUT NOCOPY VARCHAR2,
X_Segment15_Type IN OUT NOCOPY VARCHAR2,
X_Segment16_Type IN OUT NOCOPY VARCHAR2,
X_Segment17_Type IN OUT NOCOPY VARCHAR2,
X_Segment18_Type IN OUT NOCOPY VARCHAR2,
X_Segment19_Type IN OUT NOCOPY VARCHAR2,
X_Segment20_Type IN OUT NOCOPY VARCHAR2,
X_Segment21_Type IN OUT NOCOPY VARCHAR2,
X_Segment22_Type IN OUT NOCOPY VARCHAR2,
X_Segment23_Type IN OUT NOCOPY VARCHAR2,
X_Segment24_Type IN OUT NOCOPY VARCHAR2,
X_Segment25_Type IN OUT NOCOPY VARCHAR2,
X_Segment26_Type IN OUT NOCOPY VARCHAR2,
X_Segment27_Type IN OUT NOCOPY VARCHAR2,
X_Segment28_Type IN OUT NOCOPY VARCHAR2,
X_Segment29_Type IN OUT NOCOPY VARCHAR2,
X_Segment30_Type IN OUT NOCOPY VARCHAR2
) IS
BEGIN
IF (RG_DSS_VARIABLES_PKG.used_in_frozen_system(X_Variable_Id)) THEN
-- can't modify a variable that is used in a frozen system
FND_MESSAGE.set_name('RG', 'RG_DSS_FROZEN_SYSTEM');
UPDATE rg_dss_variables
SET
variable_id = X_Variable_Id,
name = X_Name,
object_name = X_Object_Name,
column_label = X_Column_Label,
balance_type = X_Balance_Type,
currency_type = X_Currency_Type,
currency_code = X_Currency_Code,
id_flex_code = X_Id_Flex_Code,
id_flex_num = X_Id_Flex_Num,
ledger_id = X_Ledger_Id,
budget_version_id = X_Budget_Version_Id,
encumbrance_type_id = X_Encumbrance_Type_Id,
level_code = X_Level_Code,
status_code = X_Status_Code,
description = X_Description,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
last_update_login = X_Last_Update_Login,
context = X_Context,
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,
segment1_type = X_Segment1_Type,
segment2_type = X_Segment2_Type,
segment3_type = X_Segment3_Type,
segment4_type = X_Segment4_Type,
segment5_type = X_Segment5_Type,
segment6_type = X_Segment6_Type,
segment7_type = X_Segment7_Type,
segment8_type = X_Segment8_Type,
segment9_type = X_Segment9_Type,
segment10_type = X_Segment10_Type,
segment11_type = X_Segment11_Type,
segment12_type = X_Segment12_Type,
segment13_type = X_Segment13_Type,
segment14_type = X_Segment14_Type,
segment15_type = X_Segment15_Type,
segment16_type = X_Segment16_Type,
segment17_type = X_Segment17_Type,
segment18_type = X_Segment18_Type,
segment19_type = X_Segment19_Type,
segment20_type = X_Segment20_Type,
segment21_type = X_Segment21_Type,
segment22_type = X_Segment22_Type,
segment23_type = X_Segment23_Type,
segment24_type = X_Segment24_Type,
segment25_type = X_Segment25_Type,
segment26_type = X_Segment26_Type,
segment27_type = X_Segment27_Type,
segment28_type = X_Segment28_Type,
segment29_type = X_Segment29_Type,
segment30_type = X_Segment30_Type
WHERE rowid = X_rowid;
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2, X_Variable_Id NUMBER) IS
BEGIN
IF (RG_DSS_VARIABLES_PKG.used_in_frozen_system(X_Variable_Id)) THEN
-- can't modify a variable that is used in a frozen system
FND_MESSAGE.set_name('RG', 'RG_DSS_FROZEN_SYSTEM');
DELETE FROM rg_dss_var_dimensions
WHERE variable_id = X_Variable_Id;
DELETE FROM rg_dss_var_selections
WHERE variable_id = X_Variable_Id;
DELETE FROM rg_dss_var_templates
WHERE variable_id = X_Variable_Id;
DELETE FROM rg_dss_variables
WHERE rowid = X_Rowid;
END Delete_Row;