[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(max_regular_period_length,0)
FROM ar_period_types
WHERE period_type = per_type;
INSERT into ar_period_types
(period_type, max_regular_period_length)
VALUES (per_type, max_len);
UPDATE ar_period_types
SET max_regular_period_length = max_len
WHERE period_type = per_type;
SELECT 'still max'
INTO does_exist
FROM dual
WHERE EXISTS
(SELECT 'still max'
FROM gl_periods
WHERE period_type = per_type
AND adjustment_period_flag = 'N'
AND end_date - start_date + 1 = max_len
AND rowid <> row_id);
UPDATE ar_period_types pt
SET pt.max_regular_period_length =
(SELECT decode(call_mode,
'D',max(end_date - start_date) + 1,
'U',greatest(new_len, nvl(max(end_date-start_date)+1,0)))
FROM gl_periods per
WHERE per.adjustment_period_flag = 'N'
AND per.period_type = per_type
AND per.rowid <> row_id)
WHERE pt.period_type = per_type;
SELECT nvl(max(new_period_num + 1), 1)
FROM ar_periods
WHERE period_set_name = cal_name
AND period_type = per_type
AND start_date < new_start_date;
SELECT new_period_num
FROM ar_periods
WHERE period_set_name = cal_name
AND period_type = per_type
AND period_name = old_per_name;
UPDATE ar_periods
SET new_period_num = new_period_num + 1
WHERE period_set_name = cal_name
AND period_type = per_type
AND new_period_num >= new_pos;
INSERT INTO ar_periods
(period_set_name, period_type, start_date, end_date,
new_period_num, period_name)
VALUES
(cal_name, per_type, new_start_date, new_end_date,
new_pos, new_per_name);
UPDATE ar_periods
SET new_period_num = NULL
WHERE period_set_name = cal_name
AND period_type = per_type
AND period_name = old_per_name;
UPDATE ar_periods
SET new_period_num = new_period_num + 1
WHERE period_set_name = cal_name
AND period_type = per_type
AND new_period_num BETWEEN new_pos AND old_pos;
UPDATE ar_periods
SET new_period_num = new_period_num - 1
WHERE period_set_name = cal_name
AND period_type = per_type
AND new_period_num BETWEEN old_pos AND new_pos;
UPDATE ar_periods
SET start_date = new_start_date,
end_date = new_end_date,
period_name = new_per_name,
new_period_num = new_pos
WHERE period_set_name = cal_name
AND period_type = per_type
AND period_name = old_per_name;
UPDATE ar_periods
SET start_date = new_start_date,
end_date = new_end_date,
period_name = new_per_name
WHERE period_set_name = cal_name
AND period_type = per_type
AND period_name = old_per_name;
DELETE ar_periods
WHERE period_set_name = cal_name
AND period_type = per_type
AND period_name = old_per_name;
UPDATE ar_periods
SET new_period_num = new_period_num - 1
WHERE period_set_name = cal_name
AND period_type = per_type
AND new_period_num > old_pos;
SELECT 'Duplicate'
FROM GL_PERIODS gp
WHERE gp.period_set_name =
check_unique_num.calendar_name
AND gp.period_type = check_unique_num.period_type
AND gp.period_year = check_unique_num.period_year
AND gp.period_num = check_unique_num.period_num
AND ( row_id is NULL
OR gp.rowid <> row_id);
SELECT 'Duplicate'
FROM GL_PERIODS gp
WHERE gp.period_name = check_unique_name.period_name
AND gp.period_set_name =
check_unique_name.calendar_name
AND ( row_id IS NULL
OR gp.rowid <> row_id);
SELECT per.period_set_name, per.period_type,
per.period_name, per.period_year
FROM gl_periods per
WHERE per.rowid = row_id;
SELECT 'Opened'
FROM gl_period_statuses ps,
gl_ledgers led
WHERE ps.application_id IN (101, 275, 283)
AND ps.period_name = check_period_used.period_name
AND ps.closing_status <> 'N'
AND ps.ledger_id = led.ledger_id+0
AND ps.period_type = check_period_used.period_type
AND led.period_set_name =
check_period_used.calendar_name;
SELECT 'Used for budgets'
FROM gl_budgets b,
gl_budget_versions bv,
gl_budget_period_ranges bpr,
gl_ledgers led
WHERE bv.budget_name = b.budget_name
AND bv.budget_type = b.budget_type
AND bpr.budget_version_id = bv.budget_version_id
AND bpr.period_year =
check_period_used.period_year
AND led.ledger_id = b.ledger_id
AND led.period_set_name =
check_period_used.calendar_name
AND led.accounted_period_type = check_period_used.period_type;
SELECT 'Used for encumbrances'
FROM gl_ledgers led
WHERE led.period_set_name =
check_period_used.calendar_name
AND led.accounted_period_type = check_period_used.period_type
AND led.latest_encumbrance_year >=
check_period_used.period_year;
SELECT *
FROM GL_PERIODS
WHERE rowid = X_Rowid
FOR UPDATE of Period_Set_Name NOWAIT;
SELECT 'Overlapping'
FROM GL_PERIODS
WHERE period_set_name = x_period_set_name
AND period_type = x_period_type
AND start_date <= x_end_date
AND end_date >= x_start_date
AND adjustment_period_flag = 'N'
AND ( row_id is NULL
OR rowid <> row_id);
SELECT creation_date, rowid
INTO v_creation_date, v_rowid
FROM GL_PERIODS
WHERE period_set_name = X_Period_Set_Name
AND period_name = X_Period_Name;
gl_periods_pkg.Update_Row (
X_Rowid => v_rowid ,
X_Period_Set_Name => X_Period_Set_Name,
X_Period_Name => X_Period_Name,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => user_id,
X_Start_Date => to_date(X_Start_Date,'YYYY/MM/DD'),
X_End_Date => to_date(X_End_Date,'YYYY/MM/DD'),
X_Period_Type => X_Period_Type,
X_Period_Year => X_Period_Year,
X_Period_Num => X_Period_Num,
X_Quarter_Num => X_Quarter_Num,
X_Entered_Period_Name => X_Entered_Period_Name,
X_Last_Update_Login => 0,
X_Description => X_Description,
X_Attribute1 => X_Attribute1,
X_Attribute2 => X_Attribute2,
X_Attribute3 => X_Attribute3,
X_Attribute4 => X_Attribute4,
X_Attribute5 => X_Attribute5,
X_Attribute6 => X_Attribute6,
X_Attribute7 => X_Attribute7,
X_Attribute8 => X_Attribute8,
X_Context => X_Context,
X_Adjustment_Period_Flag => X_Adjustment_Period_Flag
);
gl_periods_pkg.Insert_Row (
X_Rowid => v_rowid ,
X_Period_Set_Name => X_Period_Set_Name,
X_Period_Name => X_Period_Name,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => user_id,
X_Creation_date => sysdate,
X_Created_By => user_id,
X_Start_Date => to_date(X_Start_Date,'YYYY/MM/DD'),
X_End_Date => to_date(X_End_Date,'YYYY/MM/DD'),
X_Period_Type => X_Period_Type,
X_Period_Year => X_Period_Year,
X_Period_Num => X_Period_Num,
X_Quarter_Num => X_Quarter_Num,
X_Entered_Period_Name => X_Entered_Period_Name,
X_Last_Update_Login => 0,
X_Description => X_Description,
X_Attribute1 => X_Attribute1,
X_Attribute2 => X_Attribute2,
X_Attribute3 => X_Attribute3,
X_Attribute4 => X_Attribute4,
X_Attribute5 => X_Attribute5,
X_Attribute6 => X_Attribute6,
X_Attribute7 => X_Attribute7,
X_Attribute8 => X_Attribute8,
X_Context => X_Context,
X_Adjustment_Period_Flag => X_Adjustment_Period_Flag
);
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Period_Set_Name VARCHAR2,
X_Period_Name VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Start_Date DATE,
X_End_Date DATE,
X_Period_Type VARCHAR2,
X_Period_Year NUMBER,
X_Period_Num NUMBER,
X_Quarter_Num NUMBER,
X_Entered_Period_Name VARCHAR2,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Last_Update_Login NUMBER,
X_Description 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_Context VARCHAR2,
X_Adjustment_Period_Flag VARCHAR2
) IS
CURSOR C IS SELECT rowid FROM GL_PERIODS
WHERE period_set_name = X_Period_Set_Name
AND period_name = X_Period_Name;
'INSERT',
X_Period_Set_Name,
X_Period_Type,
X_Adjustment_Period_Flag,
X_Start_Date+1, -- to make old and new date different
X_Start_Date,
X_End_Date,
X_Period_Name,
X_Period_Year,
X_Period_Year,
X_Quarter_Num,
X_Quarter_Num,
x_quarter_start_date,
x_year_start_date,
X_Creation_Date,
X_Created_By,
X_Last_Update_Date,
X_Last_Updated_By,
X_Last_Update_Login
);
INSERT INTO GL_PERIODS(
period_set_name,
period_name,
last_update_date,
last_updated_by,
start_date,
end_date,
period_type,
period_year,
period_num,
quarter_num,
entered_period_name,
creation_date,
created_by,
last_update_login,
description,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
context,
adjustment_period_flag,
quarter_start_date,
year_start_date
) VALUES (
X_Period_Set_Name,
X_Period_Name,
X_Last_Update_Date,
X_Last_Updated_By,
X_Start_Date,
X_End_Date,
X_Period_Type,
X_Period_Year,
X_Period_Num,
X_Quarter_Num,
X_Entered_Period_Name,
X_Creation_Date,
X_Created_By,
X_Last_Update_Login,
X_Description,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Attribute6,
X_Attribute7,
X_Attribute8,
X_Context,
X_Adjustment_Period_Flag,
x_quarter_start_date,
x_year_start_date
);
gl_period_statuses_pkg.insert_period(
X_period_set_name,
X_period_name,
X_start_date,
X_end_date,
X_period_type,
X_period_year,
X_period_num,
X_quarter_num,
X_adjustment_period_flag,
X_last_updated_by,
X_last_update_login,
x_quarter_start_date,
x_year_start_date);
END Insert_Row;
SELECT *
FROM GL_PERIODS
WHERE rowid = X_Rowid
FOR UPDATE of Period_Set_Name NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Period_Set_Name VARCHAR2,
X_Period_Name VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Start_Date DATE,
X_End_Date DATE,
X_Period_Type VARCHAR2,
X_Period_Year NUMBER,
X_Period_Num NUMBER,
X_Quarter_Num NUMBER,
X_Entered_Period_Name VARCHAR2,
X_Last_Update_Login NUMBER,
X_Description 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_Context VARCHAR2,
X_Adjustment_Period_Flag VARCHAR2
) IS
CURSOR get_old_name IS
SELECT period_name, start_date, end_date, period_year, quarter_num,
adjustment_period_flag
FROM gl_periods
WHERE rowid = X_rowid;
UPDATE GL_PERIODS
SET
period_set_name = X_Period_Set_Name,
period_name = X_Period_Name,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
start_date = X_Start_Date,
end_date = X_End_Date,
period_type = X_Period_Type,
period_year = X_Period_Year,
period_num = X_Period_Num,
quarter_num = X_Quarter_Num,
entered_period_name = X_Entered_Period_Name,
last_update_login = X_Last_Update_Login,
description = X_Description,
attribute1 = X_Attribute1,
attribute2 = X_Attribute2,
attribute3 = X_Attribute3,
attribute4 = X_Attribute4,
attribute5 = X_Attribute5,
attribute6 = X_Attribute6,
attribute7 = X_Attribute7,
attribute8 = X_Attribute8,
context = X_Context,
adjustment_period_flag = X_Adjustment_Period_Flag
WHERE rowid = X_rowid;
gl_period_statuses_pkg.update_period(
X_period_set_name,
X_period_name_old,
X_period_name,
X_start_date,
X_end_date,
X_period_type,
X_period_year,
X_period_num,
X_quarter_num,
X_adjustment_period_flag,
X_last_updated_by,
X_last_update_login);
'UPDATE',
X_Period_Set_Name,
X_Period_Type,
X_Adjustment_Period_Flag,
x_start_date_old,
X_Start_Date,
X_End_Date,
X_Period_Name,
x_period_year_old,
X_Period_Year,
x_quarter_num_old,
X_Quarter_Num,
x_quarter_start_date,
x_year_start_date,
sysdate,
X_Last_Updated_By,
X_Last_Update_Date,
X_Last_Updated_By,
X_Last_Update_Login
);
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
CURSOR get_old_name IS
SELECT period_name, period_set_name,
start_date,period_year, quarter_num, period_type,
adjustment_period_flag, end_date, period_num,
last_updated_by,last_update_login
FROM gl_periods
WHERE rowid = X_rowid;
x_last_updated_by NUMBER;
x_last_update_login NUMBER;
x_period_num_old,x_last_updated_by,x_last_update_login;
DELETE FROM GL_PERIODS
WHERE rowid = X_Rowid;
gl_period_statuses_pkg.delete_period(
X_period_set_name,
X_period_name_old);
'DELETE',
X_Period_Set_Name,
X_Period_Type,
X_Adjustment_Period_Flag,
x_start_date_old+1,
X_start_date_old,
x_end_date_old,
X_period_name_old,
x_period_year_old,
x_period_year_old,
x_quarter_num_old,
x_period_num_old,
x_quarter_start_date,
x_year_start_date,
sysdate,
x_last_updated_by,
sysdate,
x_last_updated_by,
x_last_update_login
);
END Delete_Row;
SELECT min(start_date)
INTO existing_quarter_start_date
FROM gl_periods
WHERE
period_set_name = x_period_set_name
AND period_type = x_period_type
AND period_year = x_period_year
AND quarter_num = x_quarter_num;
IF ( x_operation = 'INSERT') THEN
IF(x_start_date >= existing_quarter_start_date ) THEN
RETURN;
UPDATE gl_periods
SET quarter_start_date = x_quarter_start_date
WHERE
period_set_name = x_period_set_name
AND quarter_num = x_quarter_num
AND period_type = x_period_type
AND period_year = x_period_year;
UPDATE gl_period_statuses
SET quarter_start_date = x_quarter_start_date
WHERE
quarter_num = x_quarter_num
AND period_type = x_period_type
AND period_year = x_period_year
AND ledger_id IN
(SELECT ledger_id
FROM gl_ledgers
WHERE period_set_name = x_period_set_name);
SELECT min(start_date)
INTO existing_year_start_date
FROM gl_periods
WHERE
period_set_name = x_period_set_name
AND period_type = x_period_type
AND period_year = x_period_year;
IF ( x_operation = 'INSERT') THEN
IF(x_start_date >= existing_year_start_date ) THEN
RETURN;
UPDATE gl_periods
SET year_start_date = x_year_start_date
WHERE
period_set_name = x_period_set_name
AND period_type = x_period_type
AND period_year = x_period_year;
UPDATE gl_period_statuses
SET year_start_date = x_year_start_date
WHERE
period_type = x_period_type
AND period_year = x_period_year
AND ledger_id IN
(SELECT ledger_id
FROM gl_ledgers
WHERE period_set_name = x_period_set_name);
SELECT '1' FROM sys.dual
WHERE EXISTS
(SELECT 'Calendar used in LED with Average Balancing enabled'
FROM gl_ledgers
WHERE
period_set_name = x_period_set_name
AND enable_average_balances_flag= 'Y'
);
x_LAST_UPDATE_DATE DATE,
x_LAST_UPDATED_BY NUMBER,
x_LAST_UPDATE_LOGIN NUMBER
) IS
entered_year VARCHAR2(30);
x_LAST_UPDATE_DATE,
x_LAST_UPDATED_BY,
x_LAST_UPDATE_LOGIN
);
x_LAST_UPDATE_DATE,
x_LAST_UPDATED_BY,
x_LAST_UPDATE_LOGIN
);
'UPDATE',
x_year_start_date
);
'UPDATE',
x_quarter_start_date
);
PROCEDURE Insert_Period(Y_Rowid IN OUT NOCOPY VARCHAR2,
Y_Period_Set_Name VARCHAR2,
Y_Period_Name VARCHAR2,
Y_Last_Update_Date DATE,
Y_Last_Updated_By NUMBER,
Y_Start_Date DATE,
Y_End_Date DATE,
Y_Period_Type VARCHAR2,
Y_Period_Year NUMBER,
Y_Period_Num NUMBER,
Y_Quarter_Num NUMBER,
Y_Entered_Period_Name VARCHAR2,
Y_Creation_Date DATE,
Y_Created_By NUMBER,
Y_Last_Update_Login NUMBER,
Y_Description VARCHAR2,
Y_Attribute1 VARCHAR2,
Y_Attribute2 VARCHAR2,
Y_Attribute3 VARCHAR2,
Y_Attribute4 VARCHAR2,
Y_Attribute5 VARCHAR2,
Y_Attribute6 VARCHAR2,
Y_Attribute7 VARCHAR2,
Y_Attribute8 VARCHAR2,
Y_Context VARCHAR2,
Y_Adjustment_Period_Flag VARCHAR2
) IS
CURSOR check_period_num IS
SELECT number_per_fiscal_year
FROM GL_PERIOD_TYPES
WHERE period_type = Y_Period_Type;
GL_PERIODS_PKG.Insert_Row(Y_Rowid,
Y_Period_Set_Name,
Y_Period_Name,
Y_Last_Update_Date,
Y_Last_Updated_By,
Y_Start_Date,
Y_End_Date,
Y_Period_Type,
Y_Period_Year,
Y_Period_Num,
Y_Quarter_Num,
Y_Entered_Period_Name,
Y_Creation_Date,
Y_Created_By,
Y_Last_Update_Login,
Y_Description,
Y_Attribute1,
Y_Attribute2,
Y_Attribute3,
Y_Attribute4,
Y_Attribute5,
Y_Attribute6,
Y_Attribute7,
Y_Attribute8,
Y_Context,
Y_Adjustment_Period_Flag
);
fnd_message.set_token('PROCEDURE', 'GL_PERIODS_PKG.Insert_Period');
END Insert_Period;
PROCEDURE Update_Period(Y_Rowid IN OUT NOCOPY VARCHAR2,
Y_Period_Set_Name VARCHAR2,
Y_Period_Name VARCHAR2,
Y_Last_Update_Date DATE,
Y_Last_Updated_By NUMBER,
Y_Start_Date DATE,
Y_End_Date DATE,
Y_Period_Type VARCHAR2,
Y_Period_Year NUMBER,
Y_Period_Num NUMBER,
Y_Quarter_Num NUMBER,
Y_Entered_Period_Name VARCHAR2,
Y_Last_Update_Login NUMBER,
Y_Description VARCHAR2,
Y_Attribute1 VARCHAR2,
Y_Attribute2 VARCHAR2,
Y_Attribute3 VARCHAR2,
Y_Attribute4 VARCHAR2,
Y_Attribute5 VARCHAR2,
Y_Attribute6 VARCHAR2,
Y_Attribute7 VARCHAR2,
Y_Attribute8 VARCHAR2,
Y_Context VARCHAR2,
Y_Adjustment_Period_Flag VARCHAR2
) IS
CURSOR current_period IS
SELECT start_date,
end_date,
period_type,
period_year,
period_num,
quarter_num,
entered_period_name,
adjustment_period_flag,
rowid
FROM gl_periods
WHERE period_set_name = Y_Period_Set_Name
AND period_name = Y_Period_Name;
SELECT number_per_fiscal_year
FROM GL_PERIOD_TYPES
WHERE period_type = Y_Period_Type;
fnd_message.set_name('SQLGL', 'GL_API_UPDATE_NOT_ALLOWED');
GL_PERIODS_PKG.Update_Row(Y_Rowid,
Y_Period_Set_Name,
Y_Period_Name,
Y_Last_Update_Date,
Y_Last_Updated_By,
Y_Start_Date,
Y_End_Date,
Y_Period_Type,
Y_Period_Year,
Y_Period_Num,
Y_Quarter_Num,
Y_Entered_Period_Name,
Y_Last_Update_Login,
Y_Description,
Y_Attribute1,
Y_Attribute2,
Y_Attribute3,
Y_Attribute4,
Y_Attribute5,
Y_Attribute6,
Y_Attribute7,
Y_Attribute8,
Y_Context,
Y_Adjustment_Period_Flag
);
fnd_message.set_token('PROCEDURE', 'GL_PERIODS_PKG.Update_Period');
END Update_Period;