The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE check_ledger_selection(X_Allocation_Formula_Id NUMBER) IS
CURSOR to_ledger_selection IS
SELECT count(DISTINCT nvl(ledger_id, -1))
FROM gl_alloc_formula_lines
WHERE allocation_formula_id = X_Allocation_Formula_Id
AND line_number in (4, 5);
OPEN to_ledger_selection;
FETCH to_ledger_selection INTO counts;
CLOSE to_ledger_selection;
CLOSE to_ledger_selection;
END check_ledger_selection;
SELECT 'Duplicate'
FROM GL_ALLOC_FORMULAS gaf
WHERE gaf.allocation_batch_id = batch_id
AND gaf.name = formula_name
AND ( row_id is null
OR gaf.rowid <> row_id);
SELECT gl_alloc_formulas_s.NEXTVAL
FROM dual;
PROCEDURE delete_rows(batch_id NUMBER) IS
BEGIN
gl_alloc_form_lines_pkg.delete_batch(batch_id);
DELETE gl_alloc_formulas
WHERE allocation_batch_id = batch_id;
END delete_rows;
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Allocation_Formula_Id IN OUT NOCOPY NUMBER,
X_Allocation_Batch_Id IN OUT NOCOPY NUMBER,
X_Name VARCHAR2,
X_Run_Sequence NUMBER,
X_Je_Category_Name VARCHAR2,
X_Full_Allocation_Flag VARCHAR2,
X_Conversion_Method_Code VARCHAR2,
X_Currency_Conversion_Type VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Last_Update_Login NUMBER,
X_Description VARCHAR2,
X_Actual_Flag VARCHAR2,
X_Validation_Status VARCHAR2
) IS
CURSOR C IS SELECT rowid FROM GL_ALLOC_FORMULAS
WHERE allocation_formula_id = X_Allocation_Formula_Id;
check_ledger_selection(X_Allocation_Formula_Id);
INSERT INTO GL_ALLOC_FORMULAS(
allocation_formula_id,
allocation_batch_id,
name,
run_sequence,
je_category_name,
full_allocation_flag,
validation_status,
conversion_method_code,
currency_conversion_type,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
description
) VALUES (
X_Allocation_Formula_Id,
X_Allocation_Batch_Id,
X_Name,
X_Run_Sequence,
X_Je_Category_Name,
X_Full_Allocation_Flag,
X_Validation_Status,
X_Conversion_Method_Code,
X_Currency_Conversion_Type,
X_Last_Update_Date,
X_Last_Updated_By,
X_Creation_Date,
X_Created_By,
X_Last_Update_Login,
X_Description
);
END Insert_Row;
SELECT *
FROM GL_ALLOC_FORMULAS
WHERE rowid = X_Rowid
FOR UPDATE of Allocation_Formula_Id NOWAIT;
FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Allocation_Formula_Id NUMBER,
X_Allocation_Batch_Id NUMBER,
X_Name VARCHAR2,
X_Run_Sequence NUMBER,
X_Je_Category_Name VARCHAR2,
X_Full_Allocation_Flag VARCHAR2,
X_Conversion_Method_Code VARCHAR2,
X_Currency_Conversion_Type VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Description VARCHAR2,
X_Actual_Flag VARCHAR2,
X_Transaction_Currency VARCHAR2,
Currency_Changed IN OUT NOCOPY VARCHAR2,
X_Validation_Status VARCHAR2
) IS
BEGIN
-- Make sure the user has completed all of the formula
-- lines.
IF (NOT gl_alloc_form_lines_pkg.complete_formula(
X_allocation_formula_id,
X_actual_flag)) THEN
IF (X_actual_flag = 'B') THEN
fnd_message.set_name('SQLGL', 'GL_COMPLETE_FOUR_FORMULA_LINES');
gl_alloc_form_lines_pkg.update_currency(
X_Allocation_Formula_id,
X_Transaction_Currency,
X_Conversion_Method_Code);
check_ledger_selection(X_Allocation_Formula_Id);
UPDATE GL_ALLOC_FORMULAS
SET
allocation_formula_id = X_Allocation_Formula_Id,
allocation_batch_id = X_Allocation_Batch_Id,
name = X_Name,
run_sequence = X_Run_Sequence,
je_category_name = X_Je_Category_Name,
full_allocation_flag = X_Full_Allocation_Flag,
validation_status = X_Validation_Status,
conversion_method_code = X_Conversion_Method_Code,
currency_conversion_type = X_Currency_Conversion_Type,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
last_update_login = X_Last_Update_Login,
description = X_Description
WHERE rowid = X_rowid;
END Update_Row;
PROCEDURE Delete_Row(Allocation_formula_id NUMBER, X_Rowid VARCHAR2) IS
BEGIN
-- Delete all of the associated formula lines
gl_alloc_form_lines_pkg.delete_rows(
allocation_formula_id);
DELETE FROM GL_ALLOC_FORMULAS
WHERE rowid = X_Rowid;
END Delete_Row;