The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
INTO dummy
FROM dual
WHERE NOT EXISTS
(SELECT 1
FROM rg_dss_dim_segments
WHERE dimension_id = X_dimension_id
AND sequence = X_sequence
AND ((X_rowid IS NULL) OR (rowid <> X_rowid))
);
SELECT 1
INTO dummy
FROM dual
WHERE NOT EXISTS
(SELECT 1
FROM rg_dss_dim_segments
WHERE dimension_id = X_dimension_id
AND application_column_name = X_application_column_name
AND ((X_rowid IS NULL) OR (rowid <> X_rowid))
);
SELECT count(sequence)
INTO num_of_dim_segs
FROM rg_dss_dim_segments
WHERE dimension_id = X_dimension_id;
PROCEDURE insert_row(X_master_dimension_id IN OUT NOCOPY NUMBER,
X_rowid IN OUT NOCOPY VARCHAR2,
X_dimension_id IN OUT NOCOPY NUMBER,
X_sequence NUMBER,
X_application_column_name VARCHAR2,
X_id_flex_code VARCHAR2,
X_id_flex_num NUMBER,
X_max_desc_size NUMBER,
X_creation_date DATE,
X_created_by NUMBER,
X_last_update_date DATE,
X_last_updated_by NUMBER,
X_last_update_login NUMBER,
X_range_set_id NUMBER,
X_account_type VARCHAR2,
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
) IS
CURSOR C IS SELECT rowid FROM rg_dss_dim_segments
WHERE dimension_id = X_dimension_id
AND sequence = X_sequence;
INSERT INTO rg_dss_dim_segments
(dimension_id ,
sequence ,
application_column_name ,
id_flex_code ,
id_flex_num ,
max_desc_size ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login ,
range_set_id ,
account_type ,
context ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 )
VALUES
(X_dimension_id ,
X_sequence ,
X_application_column_name ,
X_id_flex_code ,
X_id_flex_num ,
X_max_desc_size ,
X_creation_date ,
X_created_by ,
X_last_update_date ,
X_last_updated_by ,
X_last_update_login ,
X_range_set_id ,
X_account_type ,
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 );
END insert_row;
PROCEDURE update_row(X_rowid IN OUT NOCOPY VARCHAR2,
X_dimension_id NUMBER,
X_sequence NUMBER,
X_application_column_name VARCHAR2,
X_id_flex_code VARCHAR2,
X_id_flex_num NUMBER,
X_max_desc_size NUMBER,
X_last_update_date DATE,
X_last_updated_by NUMBER,
X_last_update_login NUMBER,
X_range_set_id NUMBER,
X_account_type VARCHAR2,
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
) IS
BEGIN
IF (RG_DSS_DIMENSIONS_PKG.used_in_frozen_system(X_Dimension_Id) = 1) THEN
-- can't modify a dimension that is used in a frozen system
FND_MESSAGE.set_name('RG', 'RG_DSS_FROZEN_SYSTEM');
UPDATE rg_dss_dim_segments
SET dimension_id = X_dimension_id ,
sequence = X_sequence ,
application_column_name = X_application_column_name ,
id_flex_code = X_id_flex_code ,
id_flex_num = X_id_flex_num ,
max_desc_size = X_max_desc_size ,
last_update_date = X_last_update_date ,
last_updated_by = X_last_updated_by ,
last_update_login = X_last_update_login ,
range_set_id = X_range_set_id ,
account_type = X_account_type ,
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
WHERE rowid = X_rowid;
END update_row;
SELECT *
FROM rg_dss_dim_segments
WHERE rowid = X_rowid
FOR UPDATE OF sequence NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
PROCEDURE delete_row(
X_rowid VARCHAR2,
X_Dimension_Id NUMBER) IS
BEGIN
IF (RG_DSS_DIMENSIONS_PKG.used_in_frozen_system(X_Dimension_Id) = 1) THEN
-- can't modify a dimension that is used in a frozen system
FND_MESSAGE.set_name('RG', 'RG_DSS_FROZEN_SYSTEM');
DELETE FROM rg_dss_dim_segments
WHERE rowid = X_rowid;
END delete_row;