[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE select_row(recinfo IN OUT NOCOPY rg_report_axis_sets%ROWTYPE) IS
BEGIN
select * INTO recinfo
from rg_report_axis_sets
where axis_set_id = recinfo.axis_set_id;
END select_row;
PROCEDURE select_columns(X_axis_set_id NUMBER,
X_name IN OUT NOCOPY VARCHAR2) IS
recinfo rg_report_axis_sets%ROWTYPE;
select_row(recinfo);
END select_columns;
PROCEDURE update_structure_info(X_axis_set_id NUMBER,
X_id_flex_code VARCHAR2,
X_structure_id NUMBER) IS
BEGIN
UPDATE rg_report_axis_sets
SET id_flex_code = X_id_flex_code,
structure_id = X_structure_id
WHERE axis_set_id = NVL( X_axis_set_id, -1);
END update_structure_info;
select 1 into dummy from dual
where not exists
(select 1 from rg_report_axis_sets
where name = X_name
and axis_set_type = X_axis_set_type
and application_id = X_application_id
and ((X_rowid IS NULL) OR (rowid <> X_rowid)));
SELECT 1 INTO dummy FROM sys.dual
WHERE NOT EXISTS
( SELECT 1
FROM rg_reports
WHERE row_set_id = X_axis_set_id
UNION
SELECT 1
FROM rg_report_display_sets
WHERE row_set_id = X_axis_set_id
UNION
SELECT 1
FROM rg_report_display_groups
WHERE row_set_id = X_axis_set_id
);
SELECT 1 INTO dummy FROM sys.dual
WHERE NOT EXISTS
( SELECT 1
FROM rg_reports
WHERE column_set_id = X_axis_set_id
UNION
SELECT 1
FROM rg_report_display_sets
WHERE column_set_id = X_axis_set_id
UNION
SELECT 1
FROM rg_report_display_groups
WHERE column_set_id = X_axis_set_id
);
select rg_report_axis_sets_s.nextval
into next_group_id
from dual;
PROCEDURE insert_row(X_rowid IN OUT NOCOPY VARCHAR2,
X_application_id NUMBER,
X_axis_set_id IN OUT NOCOPY NUMBER,
X_name VARCHAR2,
X_axis_set_type VARCHAR2,
X_security_flag VARCHAR2,
X_display_in_list_flag VARCHAR2,
X_period_set_name VARCHAR2,
X_description VARCHAR2,
X_column_set_header VARCHAR2,
X_segment_name VARCHAR2,
X_id_flex_code VARCHAR2,
X_structure_id 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_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_taxonomy_id NUMBER) IS
CURSOR C IS SELECT rowid FROM rg_report_axis_sets
WHERE axis_set_id = X_axis_set_id;
INSERT INTO rg_report_axis_sets
(application_id ,
axis_set_id ,
name ,
axis_set_type ,
security_flag ,
display_in_list_flag ,
period_set_name ,
description ,
column_set_header ,
segment_name ,
id_flex_code ,
structure_id ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login ,
context ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
taxonomy_id)
VALUES
(X_application_id ,
X_axis_set_id ,
X_name ,
X_axis_set_type ,
X_security_flag ,
X_display_in_list_flag ,
X_period_set_name ,
X_description ,
X_column_set_header ,
X_segment_name ,
X_id_flex_code ,
X_structure_id ,
X_creation_date ,
X_created_by ,
X_last_update_date ,
X_last_updated_by ,
X_last_update_login ,
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_taxonomy_id);
END insert_row;
SELECT *
FROM rg_report_axis_sets
WHERE rowid = X_rowid
FOR UPDATE OF name NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
PROCEDURE update_row(X_rowid IN OUT NOCOPY VARCHAR2,
X_application_id NUMBER,
X_axis_set_id NUMBER,
X_name VARCHAR2,
X_axis_set_type VARCHAR2,
X_security_flag VARCHAR2,
X_display_in_list_flag VARCHAR2,
X_period_set_name VARCHAR2,
X_description VARCHAR2,
X_column_set_header VARCHAR2,
X_segment_name VARCHAR2,
X_id_flex_code VARCHAR2,
X_structure_id NUMBER,
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_taxonomy_id NUMBER) IS
BEGIN
UPDATE rg_report_axis_sets
SET application_id = X_application_id ,
axis_set_id = X_axis_set_id ,
name = X_name ,
axis_set_type = X_axis_set_type ,
security_flag = X_security_flag ,
display_in_list_flag = X_display_in_list_flag ,
period_set_name = X_period_set_name ,
description = X_description ,
column_set_header = X_column_set_header ,
segment_name = X_segment_name ,
id_flex_code = X_id_flex_code ,
structure_id = X_structure_id ,
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 ,
taxonomy_id = X_taxonomy_id
WHERE rowid = X_rowid;
END update_row;
PROCEDURE delete_row(X_rowid VARCHAR2,
X_axis_set_id NUMBER) IS
BEGIN
rg_report_axes_pkg.delete_rows(X_axis_set_id);
DELETE FROM rg_report_axis_sets
WHERE rowid = X_rowid;
END delete_row;
v_last_updated_by NUMBER;
SELECT axis_set_id, creation_date, last_updated_by,
rowid, security_flag
INTO v_axis_set_id, v_creation_date, v_last_updated_by,
v_rowid, v_security_flag
FROM RG_REPORT_AXIS_SETS
WHERE SEEDED_NAME = X_Seeded_Name;
IF (v_last_updated_by <> 1) THEN
RETURN;
* Update only if force_edits is 'Y' or owner = 'SEED'
*/
IF ( user_id = 1 OR X_Force_Edits = 'Y' ) THEN
RG_REPORT_AXIS_SETS_PKG.update_row(
X_rowid => v_rowid,
X_application_id => X_Application_Id,
X_axis_set_id => v_axis_set_id,
X_security_flag => v_security_flag,
X_name => X_Name,
X_axis_set_type => X_Axis_Set_Type,
X_display_in_list_flag => X_Display_In_List_Flag,
X_period_set_name => null,
X_description => X_Description,
X_column_set_header => X_Column_Set_Header,
X_segment_name => X_Segment_Name,
X_id_flex_code => X_Id_Flex_Code,
X_structure_id => X_Structure_Id,
X_last_update_date => sysdate,
X_last_updated_by => user_id,
X_last_update_login => 0,
X_context => X_Context,
X_attribute1 => X_Attribute1,
X_attribute2 => X_Attribute2,
X_attribute3 => X_Attribute3,
X_attribute4 => X_Attribute4,
X_attribute5 => X_Attribute5,
X_attribute6 => X_Attribute6,
X_attribute7 => X_Attribute7,
X_attribute8 => X_Attribute8,
X_attribute9 => X_Attribute9,
X_attribute10 => X_Attribute10,
X_attribute11 => X_Attribute11,
X_attribute12 => X_Attribute12,
X_attribute13 => X_Attribute13,
X_attribute14 => X_Attribute14,
X_attribute15 => X_Attribute15,
X_taxonomy_id => null
);
* If the row doesn't exist yet, call Insert_Row().
*/
RG_REPORT_AXIS_SETS_PKG.insert_row(
X_rowid => v_rowid,
X_application_id => X_Application_Id,
X_axis_set_id => v_axis_set_id,
X_security_flag => 'N',
X_name => X_Name,
X_axis_set_type => X_Axis_Set_Type,
X_display_in_list_flag => X_Display_In_List_Flag,
X_period_set_name => null,
X_description => X_Description,
X_column_set_header => X_Column_Set_Header,
X_segment_name => X_Segment_Name,
X_id_flex_code => X_Id_Flex_Code,
X_structure_id => X_Structure_Id,
X_creation_date => sysdate,
X_created_by => user_id,
X_last_update_date => sysdate,
X_last_updated_by => user_id,
X_last_update_login => 0,
X_context => X_Context,
X_attribute1 => X_Attribute1,
X_attribute2 => X_Attribute2,
X_attribute3 => X_Attribute3,
X_attribute4 => X_Attribute4,
X_attribute5 => X_Attribute5,
X_attribute6 => X_Attribute6,
X_attribute7 => X_Attribute7,
X_attribute8 => X_Attribute8,
X_attribute9 => X_Attribute9,
X_attribute10 => X_Attribute10,
X_attribute11 => X_Attribute11,
X_attribute12 => X_Attribute12,
X_attribute13 => X_Attribute13,
X_attribute14 => X_Attribute14,
X_attribute15 => X_Attribute15,
X_taxonomy_id => null);
UPDATE RG_REPORT_AXIS_SETS
SET SEEDED_NAME = X_Seeded_Name
WHERE rowid = v_rowid;
* Update only if force_edits is 'Y' or owner = 'SEED'
*/
IF ( user_id = 1 or X_Force_Edits = 'Y' ) THEN
UPDATE RG_REPORT_AXIS_SETS
SET
name = X_Name,
description = X_Description,
last_update_date = sysdate,
last_updated_by = user_id,
last_Update_login = 0
WHERE
seeded_name = X_Seeded_Name
AND
userenv('LANG') =
( SELECT language_code
FROM FND_LANGUAGES
WHERE installed_flag = 'B' );