[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*)
FROM GL_ALLOC_FORMULA_LINES gafl
WHERE gafl.allocation_formula_id = formula_id;
PROCEDURE delete_rows(formula_id NUMBER) IS
BEGIN
DELETE gl_alloc_formula_lines
WHERE allocation_formula_id = formula_id;
END delete_rows;
PROCEDURE delete_batch(batch_id NUMBER) IS
BEGIN
DELETE gl_alloc_formula_lines
WHERE allocation_formula_id IN
(SELECT allocation_formula_id
FROM gl_alloc_formulas
WHERE allocation_batch_id = batch_id);
END delete_batch;
SELECT ledger_id, actual_flag, transaction_currency
FROM gl_alloc_formula_lines
WHERE allocation_formula_id = x_allocation_formula_id
AND line_number = 4;
SELECT object_type_code, currency_code
FROM gl_ledgers
WHERE ledger_id = x_ledger_id;
SELECT 'different currency'
FROM gl_ledgers ldg,
gl_ledger_set_assignments lsa
WHERE lsa.ledger_set_id = x_ledger_id
AND ldg.ledger_id = lsa.ledger_id
AND ldg.object_type_code = 'L'
AND ldg.currency_code <> x_ledger_currency
AND rownum = 1;
SELECT count(DISTINCT ldg.currency_code)
INTO dummy_num
FROM gl_ledgers ldg, gl_ledger_set_assignments lsa
WHERE lsa.ledger_set_id = x_ledger_id
AND ldg.ledger_id = lsa.ledger_id
AND ldg.object_type_code = 'L';
PROCEDURE update_currency(formula_id NUMBER,
transaction_currency VARCHAR2,
conversion_method VARCHAR2) IS
CURSOR check_curr IS
SELECT 'x'
FROM gl_alloc_formula_lines
WHERE allocation_formula_id = formula_id
AND line_number = 1
AND ( ( currency_type = 'T'
AND ledger_currency <> update_currency.transaction_currency)
OR ( currency_type = 'E'
AND entered_currency <> update_currency.transaction_currency)
OR ( currency_type = 'S' ));
AND update_currency.transaction_currency <> 'STAT') THEN
OPEN check_curr;
UPDATE gl_alloc_formula_lines
SET transaction_currency = update_currency.transaction_currency
WHERE allocation_formula_id = formula_id;
END update_currency;
SELECT 'Changed'
FROM GL_ALLOC_FORMULA_LINES gafl
WHERE gafl.allocation_formula_id = currency_changed.formula_id
AND gafl.amount IS NULL
AND gafl.transaction_currency <> currency_changed.transaction_currency
AND rownum < 2;
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Allocation_Formula_Id IN OUT NOCOPY NUMBER,
X_Line_Number NUMBER,
X_Line_Type VARCHAR2,
X_Operator 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_Amount NUMBER,
X_Relative_Period VARCHAR2,
X_Period_Name VARCHAR2,
X_Transaction_Currency VARCHAR2,
X_Ledger_Currency VARCHAR2,
X_Currency_Type VARCHAR2,
X_Entered_Currency VARCHAR2,
X_Actual_Flag VARCHAR2,
X_Budget_Version_Id NUMBER,
X_Encumbrance_Type_Id NUMBER,
X_Amount_Type VARCHAR2,
X_Ledger_Id NUMBER,
X_Segment_Types_Key_Full VARCHAR2,
X_Segment_Break_Key VARCHAR2,
X_Segment1 VARCHAR2,
X_Segment2 VARCHAR2,
X_Segment3 VARCHAR2,
X_Segment4 VARCHAR2,
X_Segment5 VARCHAR2,
X_Segment6 VARCHAR2,
X_Segment7 VARCHAR2,
X_Segment8 VARCHAR2,
X_Segment9 VARCHAR2,
X_Segment10 VARCHAR2,
X_Segment11 VARCHAR2,
X_Segment12 VARCHAR2,
X_Segment13 VARCHAR2,
X_Segment14 VARCHAR2,
X_Segment15 VARCHAR2,
X_Segment16 VARCHAR2,
X_Segment17 VARCHAR2,
X_Segment18 VARCHAR2,
X_Segment19 VARCHAR2,
X_Segment20 VARCHAR2,
X_Segment21 VARCHAR2,
X_Segment22 VARCHAR2,
X_Segment23 VARCHAR2,
X_Segment24 VARCHAR2,
X_Segment25 VARCHAR2,
X_Segment26 VARCHAR2,
X_Segment27 VARCHAR2,
X_Segment28 VARCHAR2,
X_Segment29 VARCHAR2,
X_Segment30 VARCHAR2
) IS
CURSOR C IS SELECT rowid FROM GL_ALLOC_FORMULA_LINES
WHERE allocation_formula_id = X_Allocation_Formula_Id
AND line_number = X_Line_Number;
INSERT INTO GL_ALLOC_FORMULA_LINES(
allocation_formula_id,
line_number,
line_type,
operator,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
amount,
relative_period,
period_name,
transaction_currency,
ledger_currency,
currency_type,
entered_currency,
actual_flag,
budget_version_id,
encumbrance_type_id,
amount_type,
ledger_id,
ledger_action_code,
segment_types_key,
segment_break_key,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30
) VALUES (
X_Allocation_Formula_Id,
X_Line_Number,
X_Line_Type,
X_Operator,
X_Last_Update_Date,
X_Last_Updated_By,
X_Creation_Date,
X_Created_By,
X_Last_Update_Login,
X_Amount,
X_Relative_Period,
X_Period_Name,
X_Transaction_Currency,
X_Ledger_Currency,
X_Currency_Type,
X_Entered_Currency,
X_Actual_Flag,
X_Budget_Version_Id,
X_Encumbrance_Type_Id,
X_Amount_Type,
X_Ledger_Id,
SUBSTR(X_Segment_Types_Key_Full, 1, 1),
SUBSTR(X_Segment_Types_Key_Full, 3),
X_Segment_Break_Key,
X_Segment1,
X_Segment2,
X_Segment3,
X_Segment4,
X_Segment5,
X_Segment6,
X_Segment7,
X_Segment8,
X_Segment9,
X_Segment10,
X_Segment11,
X_Segment12,
X_Segment13,
X_Segment14,
X_Segment15,
X_Segment16,
X_Segment17,
X_Segment18,
X_Segment19,
X_Segment20,
X_Segment21,
X_Segment22,
X_Segment23,
X_Segment24,
X_Segment25,
X_Segment26,
X_Segment27,
X_Segment28,
X_Segment29,
X_Segment30
);
END Insert_Row;
SELECT *
FROM GL_ALLOC_FORMULA_LINES
WHERE rowid = X_Rowid
FOR UPDATE of Allocation_Formula_Id NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Allocation_Formula_Id NUMBER,
X_Line_Number NUMBER,
X_Line_Type VARCHAR2,
X_Operator VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Amount NUMBER,
X_Relative_Period VARCHAR2,
X_Period_Name VARCHAR2,
X_Transaction_Currency VARCHAR2,
X_Ledger_Currency VARCHAR2,
X_Currency_Type VARCHAR2,
X_Entered_Currency VARCHAR2,
X_Actual_Flag VARCHAR2,
X_Budget_Version_Id NUMBER,
X_Encumbrance_Type_Id NUMBER,
X_Amount_Type VARCHAR2,
X_Ledger_Id NUMBER,
X_Segment_Types_Key_Full VARCHAR2,
X_Segment_Break_Key VARCHAR2,
X_Segment1 VARCHAR2,
X_Segment2 VARCHAR2,
X_Segment3 VARCHAR2,
X_Segment4 VARCHAR2,
X_Segment5 VARCHAR2,
X_Segment6 VARCHAR2,
X_Segment7 VARCHAR2,
X_Segment8 VARCHAR2,
X_Segment9 VARCHAR2,
X_Segment10 VARCHAR2,
X_Segment11 VARCHAR2,
X_Segment12 VARCHAR2,
X_Segment13 VARCHAR2,
X_Segment14 VARCHAR2,
X_Segment15 VARCHAR2,
X_Segment16 VARCHAR2,
X_Segment17 VARCHAR2,
X_Segment18 VARCHAR2,
X_Segment19 VARCHAR2,
X_Segment20 VARCHAR2,
X_Segment21 VARCHAR2,
X_Segment22 VARCHAR2,
X_Segment23 VARCHAR2,
X_Segment24 VARCHAR2,
X_Segment25 VARCHAR2,
X_Segment26 VARCHAR2,
X_Segment27 VARCHAR2,
X_Segment28 VARCHAR2,
X_Segment29 VARCHAR2,
X_Segment30 VARCHAR2
) IS
BEGIN
UPDATE GL_ALLOC_FORMULA_LINES
SET
allocation_formula_id = X_Allocation_Formula_Id,
line_number = X_Line_Number,
line_type = X_Line_Type,
operator = X_Operator,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
last_update_login = X_Last_Update_Login,
amount = X_Amount,
relative_period = X_Relative_Period,
period_name = X_Period_Name,
transaction_currency = X_Transaction_Currency,
ledger_currency = X_Ledger_Currency,
currency_type = X_Currency_Type,
entered_currency = X_Entered_Currency,
actual_flag = X_Actual_Flag,
budget_version_id = X_Budget_Version_Id,
encumbrance_type_id = X_Encumbrance_Type_Id,
amount_type = X_Amount_Type,
ledger_id = X_Ledger_Id,
ledger_action_code = SUBSTR(X_Segment_Types_Key_Full, 1, 1),
segment_types_key = SUBSTR(X_Segment_Types_Key_Full, 3),
segment_break_key = X_Segment_Break_Key,
segment1 = X_Segment1,
segment2 = X_Segment2,
segment3 = X_Segment3,
segment4 = X_Segment4,
segment5 = X_Segment5,
segment6 = X_Segment6,
segment7 = X_Segment7,
segment8 = X_Segment8,
segment9 = X_Segment9,
segment10 = X_Segment10,
segment11 = X_Segment11,
segment12 = X_Segment12,
segment13 = X_Segment13,
segment14 = X_Segment14,
segment15 = X_Segment15,
segment16 = X_Segment16,
segment17 = X_Segment17,
segment18 = X_Segment18,
segment19 = X_Segment19,
segment20 = X_Segment20,
segment21 = X_Segment21,
segment22 = X_Segment22,
segment23 = X_Segment23,
segment24 = X_Segment24,
segment25 = X_Segment25,
segment26 = X_Segment26,
segment27 = X_Segment27,
segment28 = X_Segment28,
segment29 = X_Segment29,
segment30 = X_Segment30
WHERE rowid = X_rowid;
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
BEGIN
DELETE FROM GL_ALLOC_FORMULA_LINES
WHERE rowid = X_Rowid;
END Delete_Row;