[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Inverse_Rowid IN OUT NOCOPY VARCHAR2,
X_From_Currency VARCHAR2,
X_To_Currency VARCHAR2,
X_Conversion_Date DATE,
X_Conversion_Type VARCHAR2,
X_Conversion_Rate NUMBER,
X_Inverse_Conversion_Rate NUMBER,
X_Status_Code IN OUT NOCOPY VARCHAR2,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Context 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_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Average_Balances_Used VARCHAR2,
X_Euro_Currency VARCHAR2
) IS
CURSOR C IS SELECT rowid FROM GL_DAILY_RATES
WHERE from_currency = X_From_Currency
AND to_currency = X_To_Currency
AND conversion_date = X_Conversion_Date
AND conversion_type = X_Conversion_Type;
CURSOR Inverse_C IS SELECT rowid FROM GL_DAILY_RATES
WHERE from_currency = X_To_Currency
AND to_currency = X_From_Currency
AND conversion_date = X_Conversion_Date
AND conversion_type = X_Conversion_Type;
INSERT INTO GL_DAILY_RATES(
from_currency,
to_currency,
conversion_date,
conversion_type,
conversion_rate,
status_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
) VALUES (
X_From_Currency,
X_To_Currency,
X_Conversion_Date,
X_Conversion_Type,
X_Conversion_Rate,
X_Status_Code,
X_Creation_Date,
X_Created_By,
X_Last_Update_Date,
X_Last_Updated_By,
X_Last_Update_Login,
X_Context,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Attribute6,
X_Attribute7,
X_Attribute8,
X_Attribute9,
X_Attribute10,
X_Attribute11,
X_Attribute12,
X_Attribute13,
X_Attribute14,
X_Attribute15
);
INSERT INTO GL_DAILY_RATES(
from_currency,
to_currency,
conversion_date,
conversion_type,
conversion_rate,
status_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
) VALUES (
X_To_Currency,
X_From_Currency,
X_Conversion_Date,
X_Conversion_Type,
X_Inverse_Conversion_Rate,
X_Status_Code,
X_Creation_Date,
X_Created_By,
X_Last_Update_Date,
X_Last_Updated_By,
X_Last_Update_Login,
X_Context,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Attribute6,
X_Attribute7,
X_Attribute8,
X_Attribute9,
X_Attribute10,
X_Attribute11,
X_Attribute12,
X_Attribute13,
X_Attribute14,
X_Attribute15
);
END Insert_Row;
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Context 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_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2
) IS
CURSOR C IS
SELECT *
FROM GL_DAILY_RATES
WHERE rowid = X_Rowid
FOR UPDATE of from_currency NOWAIT;
SELECT *
FROM GL_DAILY_RATES
WHERE rowid = X_Inverse_Rowid
FOR UPDATE of from_currency NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Inverse_Rowid VARCHAR2,
X_From_Currency VARCHAR2,
X_To_Currency VARCHAR2,
X_Conversion_Date DATE,
X_Conversion_Type VARCHAR2,
X_Conversion_Rate NUMBER,
X_Inverse_Conversion_Rate NUMBER,
X_Status_Code IN OUT NOCOPY VARCHAR2,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Context 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_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Average_Balances_Used VARCHAR2,
X_Euro_Currency VARCHAR2
) IS
ekey VARCHAR2(100);
UPDATE GL_DAILY_RATES
SET
from_currency = X_From_Currency,
to_currency = X_To_Currency,
conversion_date = X_Conversion_Date,
conversion_type = X_Conversion_Type,
conversion_rate = X_Conversion_Rate,
status_code = X_Status_Code,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
last_update_login = X_Last_Update_Login,
context = X_Context,
attribute1 = X_Attribute1,
attribute2 = X_Attribute2,
attribute3 = X_Attribute3,
attribute4 = X_Attribute4,
attribute5 = X_Attribute5,
attribute6 = X_Attribute6,
attribute7 = X_Attribute7,
attribute8 = X_Attribute8,
attribute9 = X_Attribute9,
attribute10 = X_Attribute10,
attribute11 = X_Attribute11,
attribute12 = X_Attribute12,
attribute13 = X_Attribute13,
attribute14 = X_Attribute14,
attribute15 = X_Attribute15
WHERE rowid = X_Rowid;
UPDATE GL_DAILY_RATES
SET
from_currency = X_To_Currency,
to_currency = X_From_Currency,
conversion_date = X_Conversion_Date,
conversion_type = X_Conversion_Type,
conversion_rate = X_Inverse_Conversion_Rate,
status_code = X_Status_Code,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
last_update_login = X_Last_Update_Login,
context = X_Context,
attribute1 = X_Attribute1,
attribute2 = X_Attribute2,
attribute3 = X_Attribute3,
attribute4 = X_Attribute4,
attribute5 = X_Attribute5,
attribute6 = X_Attribute6,
attribute7 = X_Attribute7,
attribute8 = X_Attribute8,
attribute9 = X_Attribute9,
attribute10 = X_Attribute10,
attribute11 = X_Attribute11,
attribute12 = X_Attribute12,
attribute13 = X_Attribute13,
attribute14 = X_Attribute14,
attribute15 = X_Attribute15
WHERE rowid = X_Inverse_Rowid;
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2,
X_Inverse_Rowid VARCHAR2,
X_From_Currency VARCHAR2,
X_To_Currency VARCHAR2,
X_Conversion_Type VARCHAR2,
X_Conversion_Date DATE,
X_Status_Code IN OUT NOCOPY VARCHAR2,
X_Average_Balances_Used VARCHAR2,
X_Euro_Currency VARCHAR2
) IS
ekey VARCHAR2(100);
DELETE FROM GL_DAILY_RATES
WHERE rowid IN (X_Rowid, X_Inverse_Rowid);
UPDATE GL_DAILY_RATES
SET
status_code = 'D'
WHERE rowid IN (X_Rowid, X_Inverse_Rowid);
DELETE FROM GL_DAILY_RATES
WHERE rowid = X_Rowid;
DELETE FROM GL_DAILY_RATES
WHERE rowid = X_Inverse_Rowid;
fnd_message.set_token('PROCEDURE', 'gl_daily_rates.delete_row');
END Delete_Row;
SELECT 1
INTO dummy
FROM dual D
WHERE NOT EXISTS ( SELECT 1
FROM GL_DAILY_RATES R
WHERE R.from_currency = X_From_Currency
AND R.to_currency = X_To_Currency
AND R.conversion_date = X_Conversion_Date
AND R.conversion_type = X_Conversion_Type
AND ( R.rowid <> X_Rowid OR X_Rowid IS NULL ));
SELECT nvl(max('Y'),'N')
INTO is_used
FROM dual
WHERE EXISTS (
SELECT 'found'
FROM GL_LEDGERS LGR, GL_LEDGER_RELATIONSHIPS REL
WHERE LGR.currency_code IN (X_From_Currency, X_To_Currency)
AND REL.source_ledger_id = LGR.ledger_id+0
AND REL.target_ledger_id = LGR.ledger_id+0
AND REL.application_id = 101
AND REL.target_ledger_category_code = 'ALC'
AND REL.relationship_type_code = 'BALANCE'
AND REL.target_currency_code IN (X_From_Currency, X_To_Currency)
AND ( LGR.daily_translation_rate_type = X_Conversion_Type
OR nvl(REL.alc_period_average_rate_type,
LGR.period_average_rate_type) = X_Conversion_Type
OR nvl(REL.alc_period_end_rate_type,
LGR.period_end_rate_type) = X_Conversion_Type));
PROCEDURE Insert_DateRange(X_From_Currency VARCHAR2,
X_To_Currency VARCHAR2,
X_From_Conversion_Date DATE,
X_To_Conversion_Date DATE,
X_User_Conversion_Type VARCHAR2,
X_Conversion_Rate NUMBER,
X_Mode_Flag VARCHAR2,
X_Inverse_Conversion_Rate NUMBER,
X_User_Id NUMBER,
X_Launch_Rate_Change VARCHAR2,
X_Error_Code VARCHAR2,
X_Context 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_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Used_For_AB_Translation VARCHAR2
) IS
BEGIN
-- Insert the row with conversion rate
INSERT INTO GL_DAILY_RATES_INTERFACE(
from_currency,
to_currency,
from_conversion_date,
to_conversion_date,
user_conversion_type,
conversion_rate,
mode_flag,
inverse_conversion_rate,
user_id,
launch_rate_change,
error_code,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
used_for_ab_translation
) VALUES (
X_From_Currency,
X_To_Currency,
X_From_Conversion_Date,
X_To_Conversion_Date,
X_User_Conversion_Type,
X_Conversion_Rate,
X_Mode_Flag,
X_Inverse_Conversion_Rate,
X_User_Id,
X_Launch_Rate_Change,
X_Error_Code,
X_Context,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Attribute6,
X_Attribute7,
X_Attribute8,
X_Attribute9,
X_Attribute10,
X_Attribute11,
X_Attribute12,
X_Attribute13,
X_Attribute14,
X_Attribute15,
X_Used_For_AB_Translation
);
END Insert_DateRange;
SELECT 'X'
FROM FND_CURRENCIES
WHERE currency_code = X_FROM_CURRENCY
AND currency_flag = 'Y'
AND enabled_flag ='Y'
AND sign(trunc(sysdate) -
nvl(trunc(start_date_active),trunc(sysdate))) <> -1
AND sign(trunc(sysdate)
- nvl(trunc(end_date_active),trunc(sysdate))) <> 1
AND decode(derive_type, 'EMU', sign( trunc(derive_effective) - trunc(X_CONVERSION_DATE)),1) > 0;
SELECT 'X'
FROM FND_CURRENCIES
WHERE currency_code = X_TO_CURRENCY
AND currency_flag = 'Y'
AND enabled_flag ='Y'
AND sign(trunc(sysdate) -
nvl(trunc(start_date_active),trunc(sysdate))) <> -1
AND sign(trunc(sysdate)
- nvl(trunc(end_date_active),trunc(sysdate))) <> 1
AND decode(derive_type,'EMU', sign( trunc(derive_effective) - trunc(X_CONVERSION_DATE) ), 1) > 0 ;
SELECT least(trunc(X_To_Conversion_DATE) -
trunc(X_From_Conversion_Date), 367)
into numDays
FROM dual;
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Context 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_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Average_Balances_Used VARCHAR2,
X_Euro_Currency VARCHAR2
) IS
lrowid rowid := null;
select rowid
into lrowid
from gl_daily_rates
where from_currency = X_From_Currency
and to_currency = X_To_Currency
and conversion_date = X_Conversion_Date
and conversion_type = X_Conversion_Type;
select rowid
into invrowid
from gl_daily_rates
where from_currency = X_To_Currency
and to_currency = X_From_Currency
and conversion_date = X_Conversion_Date
and conversion_type = X_Conversion_Type;
GL_DAILY_RATES_PKG.update_row(
X_Rowid =>lrowid,
X_Inverse_Rowid =>invrowid,
X_From_Currency =>X_From_Currency,
X_To_Currency =>X_To_Currency,
X_Conversion_Date =>X_Conversion_Date,
X_Conversion_Type =>X_Conversion_Type,
X_Conversion_Rate =>X_Conversion_Rate,
X_Inverse_Conversion_Rate =>X_Inverse_Conversion_Rate,
X_Status_Code =>X_Status_Code,
X_Creation_Date =>X_Creation_Date,
X_Created_By =>X_Created_By,
X_Last_Update_Date =>X_Last_Update_Date,
X_Last_Updated_By =>X_Last_Updated_By,
X_Last_Update_Login =>X_Last_Update_Login,
X_Context =>X_Context,
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_Attribute9 =>X_Attribute9,
X_Attribute10 =>X_Attribute10,
X_Attribute11 =>X_Attribute11,
X_Attribute12 =>X_Attribute12,
X_Attribute13 =>X_Attribute13,
X_Attribute14 =>X_Attribute14,
X_Attribute15 =>X_Attribute15,
X_Average_Balances_Used =>X_Average_Balances_Used,
X_Euro_Currency =>X_Euro_Currency
);
GL_DAILY_RATES_PKG.insert_row(
X_Rowid =>lrowid,
X_Inverse_Rowid =>invrowid,
X_From_Currency =>X_From_Currency,
X_To_Currency =>X_To_Currency,
X_Conversion_Date =>X_Conversion_Date,
X_Conversion_Type =>X_Conversion_Type,
X_Conversion_Rate =>X_Conversion_Rate,
X_Inverse_Conversion_Rate =>X_Inverse_Conversion_Rate,
X_Status_Code =>X_Status_Code,
X_Creation_Date =>X_Creation_Date,
X_Created_By =>X_Created_By,
X_Last_Update_Date =>X_Last_Update_Date,
X_Last_Updated_By =>X_Last_Updated_By,
X_Last_Update_Login =>X_Last_Update_Login,
X_Context =>X_Context,
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_Attribute9 =>X_Attribute9,
X_Attribute10 =>X_Attribute10,
X_Attribute11 =>X_Attribute11,
X_Attribute12 =>X_Attribute12,
X_Attribute13 =>X_Attribute13,
X_Attribute14 =>X_Attribute14,
X_Attribute15 =>X_Attribute15,
X_Average_Balances_Used =>X_Average_Balances_Used,
X_Euro_Currency =>X_Euro_Currency
);