[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE select_row (recinfo IN OUT NOCOPY rg_reports%ROWTYPE) IS
BEGIN
SELECT *
INTO recinfo
FROM rg_reports
WHERE report_id = recinfo.report_id;
END select_row;
SELECT rg_reports_s.nextval
INTO new_sequence_number
FROM dual;
SELECT 1 INTO dummy FROM dual
WHERE NOT EXISTS
(SELECT 1
FROM rg_report_requests
WHERE report_id = cur_report_id
);
SELECT 1 INTO dummy FROM dual
WHERE NOT EXISTS
(SELECT 1
FROM rg_report_requests
WHERE report_id = cur_report_id
AND report_set_id IS NOT NULL
);
SELECT 1 INTO dummy FROM dual
WHERE NOT EXISTS
(SELECT 1
FROM rg_reports
WHERE report_id <> cur_report_id
AND name = new_name
AND application_id = cur_application_id
);
SELECT substr(meaning, 1, 16)
INTO X_adhoc_prefix
FROM rg_lookups
WHERE LOOKUP_TYPE='FSG_ADHOC_REPORT_NAME_PREFIX'
AND LOOKUP_CODE = 'ADHOC_PREFIX';
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Application_Id NUMBER,
X_Report_Id IN OUT NOCOPY NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Name IN OUT NOCOPY VARCHAR2,
X_Report_Title VARCHAR2,
X_Security_Flag VARCHAR2,
X_Column_Set_Id NUMBER,
X_Row_Set_Id NUMBER,
X_Rounding_Option VARCHAR2,
X_Output_Option VARCHAR2,
X_Report_Display_Set_Id NUMBER,
X_Content_Set_Id NUMBER,
X_Row_Order_Id NUMBER,
X_Parameter_Set_Id NUMBER,
X_Unit_Of_Measure_Id VARCHAR2,
X_Id_Flex_Code VARCHAR2,
X_Structure_Id NUMBER,
X_Segment_Override VARCHAR2,
X_Override_Alc_Ledger_Currency VARCHAR2,
X_Period_Set_Name VARCHAR2,
X_Minimum_Display_Level NUMBER,
X_Description 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_reports
WHERE report_id = X_Report_Id;
SELECT substr(meaning, 1, 16) || X_Report_Id
INTO X_Name
FROM rg_lookups
WHERE LOOKUP_TYPE='FSG_ADHOC_REPORT_NAME_PREFIX'
AND LOOKUP_CODE = 'ADHOC_PREFIX';
INSERT INTO rg_reports(
application_id,
report_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
name,
report_title,
security_flag,
column_set_id,
row_set_id,
rounding_option,
output_option,
report_display_set_id,
content_set_id,
row_order_id,
parameter_set_id,
unit_of_measure_id,
id_flex_code,
structure_id,
segment_override,
override_alc_ledger_currency,
period_set_name,
minimum_display_level,
description,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
) VALUES (
X_Application_Id,
X_Report_Id,
X_Last_Update_Date,
X_Last_Updated_By,
X_Last_Update_Login,
X_Creation_Date,
X_Created_By,
X_Name,
X_Report_Title,
X_Security_Flag,
X_Column_Set_Id,
X_Row_Set_Id,
X_Rounding_Option,
X_Output_Option,
X_Report_Display_Set_Id,
X_Content_Set_Id,
X_Row_Order_Id,
X_Parameter_Set_Id,
X_Unit_Of_Measure_Id,
X_Id_Flex_Code,
X_Structure_Id,
X_Segment_Override,
X_Override_Alc_Ledger_Currency,
X_Period_Set_Name,
X_Minimum_Display_Level,
X_Description,
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;
SELECT *
FROM rg_reports
WHERE rowid = X_Rowid
FOR UPDATE of Report_Id NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Application_Id NUMBER,
X_Report_Id NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Name VARCHAR2,
X_Report_Title VARCHAR2,
X_Security_Flag VARCHAR2,
X_Column_Set_Id NUMBER,
X_Row_Set_Id NUMBER,
X_Rounding_Option VARCHAR2,
X_Output_Option VARCHAR2,
X_Report_Display_Set_Id NUMBER,
X_Content_Set_Id NUMBER,
X_Row_Order_Id NUMBER,
X_Parameter_Set_Id NUMBER,
X_Unit_Of_Measure_Id VARCHAR2,
X_Id_Flex_Code VARCHAR2,
X_Structure_Id NUMBER,
X_Segment_Override VARCHAR2,
X_Override_Alc_Ledger_Currency VARCHAR2,
X_Period_Set_Name VARCHAR2,
X_Minimum_Display_Level NUMBER,
X_Description 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
UPDATE rg_reports
SET
application_id = X_Application_Id,
report_id = X_Report_Id,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
last_update_login = X_Last_Update_Login,
name = X_Name,
report_title = X_Report_Title,
security_flag = X_Security_Flag,
column_set_id = X_Column_Set_Id,
row_set_id = X_Row_Set_Id,
rounding_option = X_Rounding_Option,
output_option = X_Output_Option,
report_display_set_id = X_Report_Display_Set_Id,
content_set_id = X_Content_Set_Id,
row_order_id = X_Row_Order_Id,
parameter_set_id = X_Parameter_Set_Id,
unit_of_measure_id = X_Unit_Of_Measure_Id,
id_flex_code = X_Id_Flex_Code,
structure_id = X_Structure_Id,
segment_override = X_Segment_Override,
override_alc_ledger_currency = X_Override_Alc_Ledger_Currency,
period_set_name = X_Period_Set_Name,
minimum_display_level = X_Minimum_Display_Level,
description = X_Description,
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;
PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
X_parameter_set_id NUMBER(15);
SELECT nvl(parameter_set_id,-1)
INTO X_parameter_set_id
FROM rg_reports
WHERE rowid = X_Rowid;
DELETE FROM rg_reports
WHERE rowid = X_Rowid;
DELETE FROM rg_report_parameters
WHERE parameter_set_id = X_parameter_set_id;
END Delete_Row;
PROCEDURE select_columns(report_id IN NUMBER,
name IN OUT NOCOPY VARCHAR2) IS
recinfo rg_reports%ROWTYPE;
select_row(recinfo);
END select_columns;
SELECT 1 INTO dummy FROM dual
WHERE NOT EXISTS
(SELECT 1
FROM rg_report_axes R_A,
rg_report_standard_axes_b S_A
WHERE
(R_A.axis_set_id = row_set_id OR
R_A.axis_set_id = column_set_id)
AND R_A.parameter_num IS NOT NULL
AND R_A.standard_axis_id = S_A.standard_axis_id
AND S_A.simple_where_name = 'BUDGET'
);
SELECT 1 INTO dummy FROM dual
WHERE NOT EXISTS
(SELECT 1
FROM rg_report_axes R_A,
rg_report_standard_axes_b S_A
WHERE
(R_A.axis_set_id = row_set_id OR
R_A.axis_set_id = column_set_id
)
AND R_A.parameter_num IS NOT NULL
AND R_A.standard_axis_id = S_A.standard_axis_id
AND S_A.simple_where_name = 'ENCUMBRANCE'
);
SELECT 1 INTO dummy FROM dual
WHERE NOT EXISTS
(SELECT 1
FROM rg_report_axes R_A
WHERE
(R_A.axis_set_id = row_set_id OR
R_A.axis_set_id = column_set_id)
AND R_A.parameter_num IS NOT NULL
);
SELECT application_id, id_flex_code, structure_id,
segment_override, override_alc_ledger_currency
FROM rg_reports
WHERE report_id = x_report_id;
SELECT max(concatenated_segment_delimiter)
INTO coa_delimiter
FROM FND_ID_FLEX_STRUCTURES
WHERE APPLICATION_ID = appl_id
AND ID_FLEX_CODE = flex_code
AND ID_FLEX_NUM = flex_num;