[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Duplicate'
FROM gl_historical_rates hist
WHERE hist.ledger_id = x_ledger_id
AND hist.code_combination_id = x_code_combination_id
AND hist.period_name = x_period_name
AND hist.target_currency = x_target_currency
AND hist.usage_code = x_usage_code
AND ( x_rowid is NULL
OR
hist.rowid <> x_rowid );
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Ledger_Id NUMBER,
X_Period_Name VARCHAR2,
X_Period_Num NUMBER,
X_Period_Year NUMBER,
X_Code_Combination_Id NUMBER,
X_Target_Currency VARCHAR2,
X_Update_Flag 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_Rate_Type VARCHAR2,
X_Translated_Rate NUMBER,
X_Translated_Amount NUMBER,
X_Account_Type VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Context VARCHAR2,
X_Usage_Code VARCHAR2,
X_Chart_of_Accounts_Id NUMBER
) IS
CURSOR C IS SELECT rowid FROM gl_historical_rates
WHERE ledger_id = X_Ledger_Id
AND code_combination_id = X_Code_Combination_Id
AND period_name = X_Period_Name
AND target_currency = X_Target_Currency
AND usage_code = X_Usage_Code;
INSERT INTO gl_historical_rates(
ledger_id,
period_name,
period_num,
period_year,
code_combination_id,
target_currency,
update_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
rate_type,
translated_rate,
translated_amount,
account_type,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
context,
usage_code
) VALUES (
X_Ledger_Id,
X_Period_Name,
X_Period_Num,
X_Period_Year,
X_Code_Combination_Id,
X_Target_Currency,
X_Update_Flag,
X_Last_Update_Date,
X_Last_Updated_By,
X_Creation_Date,
X_Created_By,
X_Last_Update_Login,
X_Rate_Type,
X_Translated_Rate,
X_Translated_Amount,
X_Account_Type,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Context,
X_Usage_Code
);
X_Last_Updated_By,
X_Chart_of_Accounts_id,
X_Period_Name,
X_Usage_Code
);
X_Last_Updated_By,
X_Chart_of_Accounts_Id,
X_Period_Name,
X_Usage_Code
);
END Insert_Row;
X_Update_Flag VARCHAR2,
X_Rate_Type VARCHAR2,
X_Translated_Rate NUMBER,
X_Translated_Amount NUMBER,
X_Account_Type VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Context VARCHAR2,
X_Usage_Code VARCHAR2
) IS
CURSOR C IS
SELECT *
FROM gl_historical_rates
WHERE rowid = X_Rowid
FOR UPDATE of Ledger_Id NOWAIT;
AND ( (Recinfo.update_flag = X_Update_Flag)
OR ( (Recinfo.update_flag IS NULL)
AND (X_Update_Flag IS NULL)))
AND ( (Recinfo.rate_type = X_Rate_Type)
OR ( (Recinfo.rate_type IS NULL)
AND (X_Rate_Type IS NULL)))
AND ( (Recinfo.translated_rate = X_Translated_Rate)
OR ( (Recinfo.translated_rate IS NULL)
AND (X_Translated_Rate IS NULL)))
AND ( (Recinfo.translated_amount = X_Translated_Amount)
OR ( (Recinfo.translated_amount IS NULL)
AND (X_Translated_Amount IS NULL)))
AND ( (Recinfo.account_type = X_Account_Type)
OR ( (Recinfo.account_type IS NULL)
AND (X_Account_Type IS NULL)))
AND ( (Recinfo.attribute1 = X_Attribute1)
OR ( (Recinfo.attribute1 IS NULL)
AND (X_Attribute1 IS NULL)))
AND ( (Recinfo.attribute2 = X_Attribute2)
OR ( (Recinfo.attribute2 IS NULL)
AND (X_Attribute2 IS NULL)))
AND ( (Recinfo.attribute3 = X_Attribute3)
OR ( (Recinfo.attribute3 IS NULL)
AND (X_Attribute3 IS NULL)))
AND ( (Recinfo.attribute4 = X_Attribute4)
OR ( (Recinfo.attribute4 IS NULL)
AND (X_Attribute4 IS NULL)))
AND ( (Recinfo.attribute5 = X_Attribute5)
OR ( (Recinfo.attribute5 IS NULL)
AND (X_Attribute5 IS NULL)))
AND ( (Recinfo.context = X_Context)
OR ( (Recinfo.context IS NULL)
AND (X_Context IS NULL)))
AND ( (Recinfo.usage_code = X_Usage_Code)
OR ( (Recinfo.usage_code IS NULL)
AND (X_Usage_Code IS NULL)))
) then
return;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Ledger_Id NUMBER,
X_Period_Name VARCHAR2,
X_Period_Num NUMBER,
X_Period_Year NUMBER,
X_Code_Combination_Id NUMBER,
X_Target_Currency VARCHAR2,
X_Update_Flag VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Rate_Type VARCHAR2,
X_Translated_Rate NUMBER,
X_Translated_Amount NUMBER,
X_Account_Type VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Context VARCHAR2,
X_Usage_Code VARCHAR2,
X_Chart_of_Accounts_Id NUMBER
) IS
BEGIN
UPDATE gl_historical_rates
SET
ledger_id = X_Ledger_Id,
period_name = X_Period_Name,
period_num = X_Period_Num,
period_year = X_Period_Year,
code_combination_id = X_Code_Combination_Id,
target_currency = X_Target_Currency,
update_flag = X_Update_Flag,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
last_update_login = X_Last_Update_Login,
rate_type = X_Rate_Type,
translated_rate = X_Translated_Rate,
translated_amount = X_Translated_Amount,
account_type = X_Account_Type,
attribute1 = X_Attribute1,
attribute2 = X_Attribute2,
attribute3 = X_Attribute3,
attribute4 = X_Attribute4,
attribute5 = X_Attribute5,
context = X_Context,
usage_code = X_Usage_Code
WHERE rowid = X_rowid;
X_Last_Updated_By,
X_Chart_of_Accounts_id,
X_Period_Name,
X_Usage_Code
);
X_Last_Updated_By,
X_Chart_of_Accounts_id,
X_Period_Name,
X_Usage_Code
);
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
BEGIN
DELETE FROM gl_historical_rates
WHERE rowid = X_Rowid;
END Delete_Row;
SELECT s.application_column_name, s.segment_num
FROM fnd_id_flex_segments s, fnd_segment_attribute_values v
WHERE s.application_id = v.application_id
AND s.id_flex_code = v.id_flex_code
AND s.id_flex_num = v.id_flex_num
AND s.application_column_name = v.application_column_name
AND v.application_id = 101
AND v.id_flex_code = 'GL#'
AND v.id_flex_num = x_chart_of_accounts_id
AND v.segment_attribute_type = 'GL_BALANCING'
AND v.attribute_value = 'Y';
PROCEDURE Insert_Row_WebADI_Wrapper(
X_Ledger IN VARCHAR2,
X_Functional_Currency IN VARCHAR2,
X_Target_Currency IN VARCHAR2,
X_PERIOD_NAME IN VARCHAR2,
X_Value_Type IN VARCHAR2,
X_Value IN NUMBER,
X_Rate_Type IN VARCHAR2,
X_Usage_Code IN VARCHAR2,
X_Segment1 IN VARCHAR2,
X_Segment2 IN VARCHAR2,
X_Segment3 IN VARCHAR2,
X_Segment4 IN VARCHAR2,
X_Segment5 IN VARCHAR2,
X_Segment6 IN VARCHAR2,
X_Segment7 IN VARCHAR2,
X_Segment8 IN VARCHAR2,
X_Segment9 IN VARCHAR2,
X_Segment10 IN VARCHAR2,
X_Segment11 IN VARCHAR2,
X_Segment12 IN VARCHAR2,
X_Segment13 IN VARCHAR2,
X_Segment14 IN VARCHAR2,
X_Segment15 IN VARCHAR2,
X_Segment16 IN VARCHAR2,
X_Segment17 IN VARCHAR2,
X_Segment18 IN VARCHAR2,
X_Segment19 IN VARCHAR2,
X_Segment20 IN VARCHAR2,
X_Segment21 IN VARCHAR2,
X_Segment22 IN VARCHAR2,
X_Segment23 IN VARCHAR2,
X_Segment24 IN VARCHAR2,
X_Segment25 IN VARCHAR2,
X_Segment26 IN VARCHAR2,
X_Segment27 IN VARCHAR2,
X_Segment28 IN VARCHAR2,
X_Segment29 IN VARCHAR2,
X_Segment30 IN VARCHAR2) IS
X_Rowid VARCHAR2(30);
X_Update_Flag VARCHAR2(1);
X_Last_Update_Date DATE;
X_Last_Updated_By NUMBER;
X_Last_Update_Login NUMBER;
select
s.segment_name segment,
s.application_column_name app_col,
s.segment_num num
from
fnd_id_flex_segments_vl s
where
s.id_flex_num = flex_num
and s.application_id = 101
and s.id_flex_code = 'GL#'
order by s.segment_num;
SELECT ledger_id
INTO l_ledger_id
FROM gl_ledgers
WHERE NAME = X_ledger;
select count(*)
into l_access_count
from gl_access_set_ledgers_v
where access_set_id = l_access_set_id
and object_type_code = 'L'
and access_privilege_code = 'F';
SELECT TO_CHAR(SYSDATE, 'DD-Mon-YYYY')
INTO V_sysdate_str
FROM DUAL;
SELECT rate_type
INTO V_Rate_Type
FROM gl_lookups_rate_type_v
WHERE X_Rate_Type = show_rate_type;
SELECT DECODE(lookup_code, 'Average', 'A', 'S')
INTO V_Usage_Code
FROM gl_lookups
WHERE lookup_type = 'GL_HIST_RATES_USAGE' AND meaning = X_Usage_Code;
SELECT period_year, period_num
INTO X_Period_Year, X_Period_Num
FROM GL_PERIOD_STATUSES
WHERE application_id = 101
AND ledger_id = l_ledger_id
AND period_name = X_PERIOD_NAME;
X_Update_Flag := 'N';
X_Last_Update_Date := SYSDATE;
X_Last_Updated_By := fnd_global.user_id;
X_Last_Update_Login := fnd_global.login_id;
SELECT chart_of_accounts_id
INTO X_Chart_of_Accounts_Id
FROM gl_ledgers
WHERE ledger_id = l_ledger_id;
SELECT COUNT(segment_num)
INTO v_segments_num
FROM fnd_id_flex_segments
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = X_Chart_of_Accounts_Id;
GL_CODE_COMBINATIONS_PKG.select_columns(X_Code_Combination_Id,
X_Account_Type, dummy);
DELETE FROM gl_historical_rates
WHERE ledger_id = l_ledger_id
AND code_combination_id = X_Code_Combination_Id
AND period_name = X_PERIOD_NAME
AND target_currency = X_Target_Currency
AND usage_code = V_Usage_Code;
GL_HISTORICAL_RATES_PKG.Insert_Row(X_Rowid, l_ledger_id,
X_PERIOD_NAME, X_Period_Num,
X_Period_Year, X_Code_Combination_Id,
X_Target_Currency, X_Update_Flag,
X_Last_Update_Date,
X_Last_Updated_By, X_Creation_Date,
X_Created_By, X_Last_Update_Login,
V_Rate_Type, X_Translated_Rate,
X_Translated_Amount, X_Account_Type,
X_Attribute1, X_Attribute2,
X_Attribute3, X_Attribute4,
X_Attribute5, X_Context,
V_Usage_Code, X_Chart_of_Accounts_Id);
END Insert_Row_WebADI_Wrapper;