[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM GL_LEDGERS ledger1
WHERE ledger1.ledger_id = X_From_Ledger_Id
AND ledger1.currency_code = (SELECT ledger2.currency_code
FROM GL_LEDGERS ledger2
WHERE ledger2.ledger_id = X_To_Ledger_Id);
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Consolidation_Id IN OUT NOCOPY NUMBER,
X_Name VARCHAR2,
X_Coa_Mapping_Id NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_From_Ledger_Id NUMBER,
X_To_Ledger_Id NUMBER,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Last_Update_Login NUMBER,
X_Description VARCHAR2,
X_Method VARCHAR2,
X_From_Currency_Code VARCHAR2,
X_From_Location VARCHAR2,
X_From_Oracle_Id VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Context VARCHAR2,
X_Usage VARCHAR2,
X_Run_Journal_Import_Flag VARCHAR2,
X_Audit_Mode_Flag VARCHAR2,
X_Summarize_Lines_Flag VARCHAR2,
X_Run_Posting_Flag VARCHAR2,
X_Security_Flag VARCHAR2
) IS
CURSOR C IS SELECT rowid FROM gl_consolidation
WHERE consolidation_id = X_Consolidation_Id;
INSERT INTO gl_consolidation(
consolidation_id,
name,
coa_mapping_id,
last_update_date,
last_updated_by,
from_ledger_id,
to_ledger_id,
creation_date,
created_by,
last_update_login,
description,
method,
from_currency_code,
from_location,
from_oracle_id,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
context,
usage_code,
run_journal_import_flag,
audit_mode_flag,
summarize_lines_flag,
run_posting_flag,
security_flag
) VALUES (
X_Consolidation_Id,
X_Name,
X_Coa_Mapping_Id,
X_Last_Update_Date,
X_Last_Updated_By,
X_From_Ledger_Id,
X_To_Ledger_Id,
X_Creation_Date,
X_Created_By,
X_Last_Update_Login,
X_Description,
X_Method,
X_From_Currency_Code,
X_From_Location,
X_From_Oracle_Id,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Context,
X_Usage,
X_Run_Journal_Import_Flag,
X_Audit_Mode_Flag,
X_Summarize_Lines_Flag,
X_Run_Posting_Flag,
X_Security_Flag
);
END Insert_Row;
SELECT *
FROM gl_consolidation
WHERE rowid = X_Rowid
FOR UPDATE of Consolidation_Id NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Consolidation_Id NUMBER,
X_Name VARCHAR2,
X_Coa_Mapping_Id NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_From_Ledger_Id NUMBER,
X_To_Ledger_Id NUMBER,
X_Last_Update_Login NUMBER,
X_Description VARCHAR2,
X_Method VARCHAR2,
X_From_Currency_Code VARCHAR2,
X_From_Location VARCHAR2,
X_From_Oracle_Id VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Context VARCHAR2,
X_Usage VARCHAR2,
X_Run_Journal_Import_Flag VARCHAR2,
X_Audit_Mode_Flag VARCHAR2,
X_Summarize_Lines_Flag VARCHAR2,
X_Run_Posting_Flag VARCHAR2,
X_Security_Flag VARCHAR2
) IS
BEGIN
-- Check that from and to ledgers have same funcional currency
if (X_Method = 'T') then
Check_Same_Currency(X_To_Ledger_Id, X_From_Ledger_Id);
UPDATE gl_consolidation
SET
consolidation_id = X_Consolidation_Id,
name = X_Name,
coa_mapping_id = X_Coa_Mapping_Id,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
from_ledger_id = X_From_Ledger_Id,
to_ledger_id = X_To_Ledger_Id,
last_update_login = X_Last_Update_Login,
description = X_Description,
method = X_Method,
from_currency_code = X_From_Currency_Code,
from_location = X_From_Location,
from_oracle_id = X_From_Oracle_Id,
attribute1 = X_Attribute1,
attribute2 = X_Attribute2,
attribute3 = X_Attribute3,
attribute4 = X_Attribute4,
attribute5 = X_Attribute5,
context = X_Context,
usage_code = X_Usage,
run_journal_import_flag = X_Run_Journal_Import_Flag,
audit_mode_flag = X_Audit_Mode_Flag,
summarize_lines_flag = X_Summarize_Lines_Flag,
run_posting_flag = X_Run_Posting_Flag,
security_flag = X_Security_Flag
WHERE rowid = X_rowid;
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2, X_Consolidation_Id NUMBER) IS
BEGIN
DELETE FROM gl_consolidation
WHERE rowid = X_Rowid;
END Delete_Row;
SELECT 1
FROM GL_CONSOLIDATION glc
WHERE glc.name = X_Name
AND ( X_Rowid is NULL
OR glc.rowid <> X_Rowid);
SELECT 1
FROM GL_CONSOLIDATION glc
WHERE glc.consolidation_id = X_Consolidation_Id
AND ( X_Rowid is NULL
OR glc.rowid <> X_Rowid);
SELECT 'Y'
FROM DUAL
WHERE EXISTS
( SELECT 'Mapping found in a mapping set'
FROM GL_CONS_SET_ASSIGNMENTS ASG
WHERE ASG.consolidation_id = X_Consolidation_Id
);
SELECT 'Y'
FROM DUAL
WHERE EXISTS
( SELECT 'Mapping has been run atleast once'
FROM GL_CONSOLIDATION_HISTORY COH
WHERE COH.consolidation_id = X_Consolidation_Id
);