[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY 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_Funds_Check_Level_Code VARCHAR2,
X_Amount_Type VARCHAR2,
X_Boundary_Code VARCHAR2,
X_Funding_Budget_Version_Id NUMBER,
X_Range_Id NUMBER
) IS
CURSOR check_overlaps IS
SELECT 'Overlap'
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM gl_budgets b1,
gl_budget_versions bv1,
gl_budorg_bc_options ba,
gl_period_statuses pf1,
gl_period_statuses pl1,
gl_budgets b2,
gl_budget_versions bv2,
gl_period_statuses pf2,
gl_period_statuses pl2
WHERE b1.current_version_id = bv1.version_num
AND b1.budget_name = bv1.budget_name
AND bv1.budget_version_id = ba.funding_budget_version_id
AND b1.first_valid_period_name = pf1.period_name
AND b1.last_valid_period_name = pl1.period_name
AND b2.current_version_id = bv2.version_num
AND b2.budget_name = bv2.budget_name
AND bv2.budget_version_id = X_Funding_Budget_Version_Id
AND b2.first_valid_period_name = pf2.period_name
AND b2.last_valid_period_name = pl2.period_name
AND ba.range_id = X_Range_Id
AND pf1.application_id = 101
AND pf1.ledger_id = b1.ledger_id
AND pl1.application_id = 101
AND pl1.ledger_id = b1.ledger_id
AND pf2.application_id = 101
AND pf2.ledger_id = b2.ledger_id
AND pl2.application_id = 101
AND pl2.ledger_id = b2.ledger_id
AND NOT ( (pl1.effective_period_num < pf2.effective_period_num)
OR (pf1.effective_period_num > pl2.effective_period_num)
)
);
SELECT rowid
FROM gl_budorg_bc_options
WHERE range_id = X_Range_Id
AND funding_budget_version_id = X_Funding_Budget_Version_Id;
INSERT INTO gl_budorg_bc_options(
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
funds_check_level_code,
amount_type,
boundary_code,
funding_budget_version_id,
range_id
) VALUES (
X_Last_Update_Date,
X_Last_Updated_By,
X_Creation_Date,
X_Created_By,
X_Last_Update_Login,
X_Funds_Check_Level_Code,
X_Amount_Type,
X_Boundary_Code,
X_Funding_Budget_Version_Id,
X_Range_Id
);
END Insert_Row;
SELECT *
FROM gl_budorg_bc_options
WHERE rowid = X_Rowid
FOR UPDATE of Range_Id NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Funds_Check_Level_Code VARCHAR2,
X_Amount_Type VARCHAR2,
X_Boundary_Code VARCHAR2,
X_Funding_Budget_Version_Id NUMBER,
X_Range_Id NUMBER
) IS
BEGIN
UPDATE gl_budorg_bc_options
SET
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
last_update_login = X_Last_Update_Login,
funds_check_level_code = X_Funds_Check_Level_Code,
amount_type = X_Amount_Type,
boundary_code = X_Boundary_Code,
funding_budget_version_id = X_Funding_Budget_Version_Id,
range_id = X_Range_Id
WHERE rowid = X_rowid;
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
BEGIN
DELETE FROM gl_budorg_bc_options
WHERE rowid = X_Rowid;
END Delete_Row;
PROCEDURE delete_budorg_bc_options(xrange_id NUMBER)IS
BEGIN
DELETE FROM gl_budorg_bc_options
WHERE range_id = xrange_id;
'gl_budorg_bc_options_pkg.delete_budorg_bc_options');
END delete_budorg_bc_options;
PROCEDURE Insert_BC_Options(X_Rowid IN OUT NOCOPY 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_Funds_Check_Level_Code VARCHAR2,
X_Amount_Type VARCHAR2,
X_Boundary_Code VARCHAR2,
X_Funding_Budget_Version_Id NUMBER,
X_Range_Id NUMBER
) IS
CURSOR check_funds_check_level IS
SELECT 'X'
FROM GL_LOOKUPS
WHERE LOOKUP_TYPE = 'FUNDS_CHECK_LEVEL'
AND lookup_code = X_Funds_Check_Level_Code;
SELECT 'X'
FROM GL_LOOKUPS_AMOUNT_TYPES_V
WHERE amount_type = X_Amount_Type;
SELECT 'X'
FROM GL_LOOKUPS_BOUNDARIES_V
WHERE boundary_code = X_Boundary_Code;
SELECT 'X'
FROM GL_BUDGET_VERSIONS
WHERE budget_version_id = X_Funding_Budget_Version_Id;
SELECT gl1.currency_code,
gl1.enable_budgetary_control_flag,
gl2.entry_code,
gl2.currency_code
INTO L_Functional_Currency,
L_Budgetary_Control_Flag,
L_Entry_Code,
L_Currency_Code
FROM gl_ledgers gl1, gl_budget_assignment_ranges gl2
WHERE gl2.range_id = X_Range_Id
AND gl1.ledger_id = gl2.ledger_id;
Insert_Row(
X_Rowid,
X_Last_Update_Date,
X_Last_Updated_By,
X_Creation_Date,
X_Created_By,
X_Last_Update_Login,
X_Funds_Check_Level_Code,
X_Amount_Type,
X_Boundary_Code,
X_Funding_Budget_Version_Id,
X_Range_Id);
'GL_BUDORG_BC_OPTIONS_PKG.Insert_BC_Options');
END Insert_BC_Options;
PROCEDURE Update_BC_Options(X_Range_Id NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Funds_Check_Level_Code VARCHAR2,
X_Amount_Type VARCHAR2,
X_Boundary_Code VARCHAR2,
X_Funding_Budget_Version_Id NUMBER
) IS
CURSOR check_funds_check_level IS
SELECT 'X'
FROM GL_LOOKUPS
WHERE LOOKUP_TYPE = 'FUNDS_CHECK_LEVEL'
AND lookup_code = X_Funds_Check_Level_Code;
SELECT 'X'
FROM GL_LOOKUPS_AMOUNT_TYPES_V
WHERE amount_type = X_Amount_Type;
SELECT 'X'
FROM GL_LOOKUPS_BOUNDARIES_V
WHERE boundary_code = X_Boundary_Code;
SELECT 'X'
FROM GL_BUDGET_VERSIONS
WHERE budget_version_id = X_Funding_Budget_Version_Id;
SELECT gl1.currency_code,
gl1.enable_budgetary_control_flag,
gl2.entry_code,
gl2.currency_code
INTO L_Functional_Currency,
L_Budgetary_Control_Flag,
L_Entry_Code,
L_Currency_Code
FROM gl_ledgers gl1, gl_budget_assignment_ranges gl2
WHERE gl2.range_id = X_Range_Id
AND gl1.ledger_id = gl2.ledger_id;
UPDATE gl_budorg_bc_options
SET
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
last_update_login = X_Last_Update_Login,
funds_check_level_code = X_Funds_Check_Level_Code,
amount_type = X_Amount_Type,
boundary_code = X_Boundary_Code
WHERE range_id = X_range_id
AND funding_budget_version_id = X_Funding_Budget_Version_Id;
'GL_BUDORG_BC_OPTIONS_PKG.Update_BC_Options');
END Update_BC_Options;