[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE print_delete_user_rate_warning IS
c_text_files VARCHAR2(200);
END print_delete_user_rate_warning;
PROCEDURE print_delete_sys_rate_warning IS
c_text_files VARCHAR2(200);
END print_delete_sys_rate_warning;
PROCEDURE insert_cross_rate_set(
p_conversion_type IN VARCHAR2,
p_contra_currency IN VARCHAR2,
p_login_user IN NUMBER) IS
existed_curr_rec curr_rec;
SELECT pivot_currency
INTO x_pivot_currency
FROM gl_cross_rate_rules
WHERE conversion_type = p_conversion_type;
SELECT DISTINCT from_currency
BULK COLLECT INTO existed_curr_rec.r_from_curr
FROM gl_cross_rate_rule_dtls
WHERE conversion_type = p_conversion_type;
INSERT INTO gl_cross_rate_rule_dtls
(conversion_type, from_currency, to_currency,
enabled_flag, last_update_date, last_updated_by,
creation_date, created_by, last_update_login)
VALUES (p_conversion_type, p_contra_currency, p_contra_currency,
'N', SYSDATE, p_login_user,
SYSDATE, p_login_user, p_login_user);
INSERT INTO gl_cross_rate_rule_dtls
(conversion_type, from_currency,
to_currency, enabled_flag, last_update_date,
last_updated_by, creation_date, created_by,
last_update_login)
VALUES (p_conversion_type, p_contra_currency,
existed_curr_rec.r_from_curr(i), 'Y', SYSDATE,
p_login_user, SYSDATE, p_login_user,
p_login_user);
INSERT INTO gl_cross_rate_rule_dtls
(conversion_type, from_currency,
to_currency, enabled_flag, last_update_date,
last_updated_by, creation_date, created_by,
last_update_login)
VALUES (p_conversion_type, existed_curr_rec.r_from_curr(i),
p_contra_currency, 'Y', SYSDATE,
p_login_user, SYSDATE, p_login_user,
p_login_user);
END insert_cross_rate_set;
PROCEDURE update_cross_rate_set(
p_conversion_type IN VARCHAR2,
p_new_contra_currency IN VARCHAR2,
p_old_contra_currency IN VARCHAR2,
p_login_user IN NUMBER) IS
x_pivot_currency VARCHAR2(15);
SELECT pivot_currency
INTO x_pivot_currency
FROM gl_cross_rate_rules
WHERE conversion_type = p_conversion_type;
UPDATE gl_cross_rate_rule_dtls
SET from_currency = p_new_contra_currency,
last_update_date = SYSDATE,
last_updated_by = p_login_user,
last_update_login = p_login_user
WHERE conversion_type = p_conversion_type
AND from_currency = p_old_contra_currency;
UPDATE gl_cross_rate_rule_dtls
SET to_currency = p_new_contra_currency,
last_update_date = SYSDATE,
last_updated_by = p_login_user,
last_update_login = p_login_user
WHERE conversion_type = p_conversion_type
AND to_currency = p_old_contra_currency;
END update_cross_rate_set;
PROCEDURE delete_cross_rate_set(
p_conversion_type IN VARCHAR2,
p_contra_currency IN VARCHAR2) IS
BEGIN
DELETE FROM gl_cross_rate_rule_dtls
WHERE conversion_type = p_conversion_type
AND ( from_currency = p_contra_currency
OR to_currency = p_contra_currency);
END delete_cross_rate_set;
sys_daily_rate_cannot_delete daily_rate_rec;
usr_daily_rate_cannot_delete daily_rate_rec;
SELECT dri.from_currency,
dri.to_currency,
dri.from_conversion_date,
dri.to_conversion_date,
dct.conversion_type,
dri.conversion_rate,
NVL(dri.inverse_conversion_rate, 1/conversion_rate) inverse_conversion_rate,
dri.mode_flag
FROM gl_daily_rates_interface dri,
gl_daily_conversion_types dct
WHERE mode_flag IN ('I', 'D', 'T', 'N')
AND dct.user_conversion_type = dri.user_conversion_type
AND dri.batch_number = l_batch_number;
UPDATE gl_daily_rates_interface ri
SET ERROR_CODE =
(SELECT DECODE
(ct.ROWID,
NULL, 'NONEXISTANT_CONVERSION_TYPE',
DECODE
(LEAST(TRUNC(ri2.to_conversion_date)
- TRUNC(ri2.from_conversion_date),
367),
367, 'DATE_RANGE_TOO_LARGE',
DECODE
(LEAST(ri.conversion_rate, 0),
ri.conversion_rate, 'NEGATIVE_CONVERSION_RATE',
DECODE
(LEAST
(NVL(ri.inverse_conversion_rate,
1),
0),
ri.inverse_conversion_rate, 'NEGATIVE_INVERSE_RATE',
DECODE
(from_curr.ROWID,
NULL, 'NONEXISTANT_FROM_CURRENCY',
DECODE
(from_curr.enabled_flag,
'N', 'DISABLED_FROM_CURRENCY',
-- Bug 4222440 JVARKEY Error the never enabled currency
'X', 'DISABLED_FROM_CURRENCY',
DECODE
(from_curr.currency_flag,
'N', 'STATISTICAL_FROM_CURRENCY',
DECODE
(from_curr.currency_code,
'STAT', 'STATISTICAL_FROM_CURRENCY',
DECODE
(SIGN
(TRUNC(SYSDATE)
- NVL
(TRUNC
(from_curr.start_date_active),
TRUNC
(SYSDATE))),
-1, 'OUT_OF_DATE_FROM_CURRENCY',
DECODE
(SIGN
(TRUNC
(SYSDATE)
- NVL
(TRUNC
(from_curr.end_date_active),
TRUNC
(SYSDATE))),
1, 'OUT_OF_DATE_FROM_CURRENCY',
DECODE
(DECODE
(from_curr.derive_type,
'EMU', SIGN
(TRUNC
(from_curr.derive_effective)
- TRUNC
(ri2.to_conversion_date)),
1),
-1, 'EMU_FROM_CURRENCY',
0, 'EMU_FROM_CURRENCY',
DECODE
(to_curr.ROWID,
NULL, 'NONEXISTANT_TO_CURRENCY',
DECODE
(to_curr.enabled_flag,
'N', 'DISABLED_TO_CURRENCY',
-- Bug 4222440 JVARKEY Error the never enabled currency
'X', 'DISABLED_TO_CURRENCY',
DECODE
(to_curr.currency_flag,
'N', 'STATISTICAL_TO_CURRENCY',
DECODE
(to_curr.currency_code,
'STAT', 'STATISTICAL_TO_CURRENCY',
DECODE
(SIGN
(TRUNC
(SYSDATE)
- NVL
(TRUNC
(to_curr.start_date_active),
TRUNC
(SYSDATE))),
-1, 'OUT_OF_DATE_TO_CURRENCY',
DECODE
(SIGN
(TRUNC
(SYSDATE)
- NVL
(TRUNC
(to_curr.end_date_active),
TRUNC
(SYSDATE))),
1, 'OUT_OF_DATE_TO_CURRENCY',
DECODE
(DECODE
(to_curr.derive_type,
'EMU', SIGN
(TRUNC
(to_curr.derive_effective)
- TRUNC
(ri2.to_conversion_date)),
1),
-1, 'EMU_TO_CURRENCY',
0, 'EMU_TO_CURRENCY',
''))))))))))))))))))
FROM gl_daily_rates_interface ri2,
gl_daily_conversion_types ct,
fnd_currencies from_curr,
fnd_currencies to_curr
WHERE ri2.ROWID = ri.ROWID
AND ct.user_conversion_type(+) = ri2.user_conversion_type
AND from_curr.currency_code(+) = ri2.from_currency
AND to_curr.currency_code(+) = ri2.to_currency),
ri.batch_number = DECODE(l_batch_number,null,-99999,ri.batch_number)
WHERE ri.mode_flag IN('I', 'D', 'T', 'N')
AND ri.batch_number is NULL;
UPDATE GL_DAILY_RATES_INTERFACE T1
SET T1.error_code = 'DUPLICATE_ROWS'
WHERE
(T1.FROM_CURRENCY,T1.TO_CURRENCY,T1.USER_CONVERSION_TYPE,
T1.FROM_CONVERSION_DATE, T1.TO_CONVERSION_DATE)
IN
(
SELECT /*+ NO_MERGE */ T2.FROM_CURRENCY,T2.to_CURRENCY,T2.USER_CONVERSION_TYPE,
T2.FROM_CONVERSION_DATE, T2.TO_CONVERSION_DATE
FROM GL_DAILY_RATES_INTERFACE T2
WHERE mode_flag IN ('I', 'D', 'T', 'N')
GROUP BY T2.FROM_CURRENCY,T2.TO_CURRENCY,T2.USER_CONVERSION_TYPE,
T2.FROM_CONVERSION_DATE, T2.TO_CONVERSION_DATE
HAVING count(*) > 1)
AND mode_flag IN ('I', 'D', 'T', 'N')
AND T1.batch_number IS NULL;
UPDATE gl_daily_rates_interface ri
SET ERROR_CODE =
(SELECT DECODE
(ct.ROWID,
NULL, 'NONEXISTANT_CONVERSION_TYPE',
DECODE
(LEAST(TRUNC(ri2.to_conversion_date)
- TRUNC(ri2.from_conversion_date),
367),
367, 'DATE_RANGE_TOO_LARGE',
DECODE
(LEAST(ri.conversion_rate, 0),
ri.conversion_rate, 'NEGATIVE_CONVERSION_RATE',
DECODE
(LEAST
(NVL(ri.inverse_conversion_rate,
1),
0),
ri.inverse_conversion_rate, 'NEGATIVE_INVERSE_RATE',
DECODE
(from_curr.ROWID,
NULL, 'NONEXISTANT_FROM_CURRENCY',
DECODE
(from_curr.enabled_flag,
'N', 'DISABLED_FROM_CURRENCY',
-- Bug 4222440 JVARKEY Error the never enabled currency
'X', 'DISABLED_FROM_CURRENCY',
DECODE
(from_curr.currency_flag,
'N', 'STATISTICAL_FROM_CURRENCY',
DECODE
(from_curr.currency_code,
'STAT', 'STATISTICAL_FROM_CURRENCY',
DECODE
(SIGN
(TRUNC(SYSDATE)
- NVL
(TRUNC
(from_curr.start_date_active),
TRUNC
(SYSDATE))),
-1, 'OUT_OF_DATE_FROM_CURRENCY',
DECODE
(SIGN
(TRUNC
(SYSDATE)
- NVL
(TRUNC
(from_curr.end_date_active),
TRUNC
(SYSDATE))),
1, 'OUT_OF_DATE_FROM_CURRENCY',
DECODE
(DECODE
(from_curr.derive_type,
'EMU', SIGN
(TRUNC
(from_curr.derive_effective)
- TRUNC
(ri2.to_conversion_date)),
1),
-1, 'EMU_FROM_CURRENCY',
0, 'EMU_FROM_CURRENCY',
DECODE
(to_curr.ROWID,
NULL, 'NONEXISTANT_TO_CURRENCY',
DECODE
(to_curr.enabled_flag,
'N', 'DISABLED_TO_CURRENCY',
-- Bug 4222440 JVARKEY Error the never enabled currency
'X', 'DISABLED_TO_CURRENCY',
DECODE
(to_curr.currency_flag,
'N', 'STATISTICAL_TO_CURRENCY',
DECODE
(to_curr.currency_code,
'STAT', 'STATISTICAL_TO_CURRENCY',
DECODE
(SIGN
(TRUNC
(SYSDATE)
- NVL
(TRUNC
(to_curr.start_date_active),
TRUNC
(SYSDATE))),
-1, 'OUT_OF_DATE_TO_CURRENCY',
DECODE
(SIGN
(TRUNC
(SYSDATE)
- NVL
(TRUNC
(to_curr.end_date_active),
TRUNC
(SYSDATE))),
1, 'OUT_OF_DATE_TO_CURRENCY',
DECODE
(DECODE
(to_curr.derive_type,
'EMU', SIGN
(TRUNC
(to_curr.derive_effective)
- TRUNC
(ri2.to_conversion_date)),
1),
-1, 'EMU_TO_CURRENCY',
0, 'EMU_TO_CURRENCY',
''))))))))))))))))))
FROM gl_daily_rates_interface ri2,
gl_daily_conversion_types ct,
fnd_currencies from_curr,
fnd_currencies to_curr
WHERE ri2.ROWID = ri.ROWID
AND ct.user_conversion_type(+) = ri2.user_conversion_type
AND from_curr.currency_code(+) = ri2.from_currency
AND to_curr.currency_code(+) = ri2.to_currency)
WHERE ri.mode_flag IN('I', 'D', 'T', 'N')
AND ri.batch_number = l_batch_number;
UPDATE GL_DAILY_RATES_INTERFACE T1
SET T1.error_code = 'DUPLICATE_ROWS'
WHERE
(T1.FROM_CURRENCY,T1.TO_CURRENCY,T1.USER_CONVERSION_TYPE,
T1.FROM_CONVERSION_DATE, T1.TO_CONVERSION_DATE)
IN
(
SELECT /*+ NO_MERGE */ T2.FROM_CURRENCY,T2.to_CURRENCY,T2.USER_CONVERSION_TYPE,
T2.FROM_CONVERSION_DATE, T2.TO_CONVERSION_DATE
FROM GL_DAILY_RATES_INTERFACE T2
WHERE mode_flag IN ('I', 'D', 'T', 'N')
GROUP BY T2.FROM_CURRENCY,T2.TO_CURRENCY,T2.USER_CONVERSION_TYPE,
T2.FROM_CONVERSION_DATE, T2.TO_CONVERSION_DATE
HAVING count(*) > 1)
AND mode_flag IN ('I', 'D', 'T', 'N')
AND T1.batch_number = l_batch_number;
UPDATE gl_daily_rates_interface
SET mode_flag = 'X'
WHERE mode_flag IN('I', 'D') AND ERROR_CODE IS NOT NULL;
UPDATE gl_daily_rates_interface
SET mode_flag = 'F'
WHERE mode_flag IN('T', 'N') AND ERROR_CODE IS NOT NULL;
UPDATE gl_daily_rates_interface ri
SET mode_flag =
Decode(mode_flag,'T','F','N','F','I','X','D','X',mode_flag)
WHERE (mode_flag IN('T', 'N','I','D') AND batch_number = l_batch_number AND ERROR_CODE IS NOT NULL)
OR (mode_flag = 'N'
AND (EXISTS (SELECT 1 FROM gl_daily_rates dr,
gl_daily_conversion_types ct
WHERE dr.from_currency = ri.from_currency
AND dr.to_currency = ri.to_currency
AND dr.conversion_type = ct.conversion_type
AND ct.user_conversion_type = ri.user_conversion_type
AND dr.conversion_date BETWEEN ri.from_conversion_date
AND ri.to_conversion_date)
OR EXISTS (SELECT 1 FROM gl_daily_rates dr,
gl_daily_conversion_types ct
WHERE dr.from_currency = ri.to_currency
AND dr.to_currency = ri.from_currency
AND dr.conversion_type = ct.conversion_type
AND ct.user_conversion_type = ri.user_conversion_type
AND dr.conversion_date BETWEEN ri.from_conversion_date
AND ri.to_conversion_date)));
SELECT 'E'
INTO l_return_status
FROM gl_daily_rates_interface
WHERE mode_flag in ('X','F')
AND batch_number = l_batch_number
AND ROWNUM < 2;
UPDATE gl_daily_rates_interface
SET inverse_conversion_rate = 1 / conversion_rate
WHERE inverse_conversion_rate IS NULL AND conversion_rate > 0
AND batch_number = l_batch_number;
SELECT from_currency,
to_currency,
from_conversion_date,
to_conversion_date,
user_conversion_type,
conversion_rate,
inverse_conversion_rate,
DECODE(ERROR_CODE,
'NONEXISTANT_CONVERSION_TYPE', 'VF01',
'DATE_RANGE_TOO_LARGE', 'VF02',
'NEGATIVE_CONVERSION_RATE', 'VF03',
'NEGATIVE_INVERSE_RATE', 'VF04',
'NONEXISTANT_FROM_CURRENCY', 'VF05',
'DISABLED_FROM_CURRENCY', 'VF06',
'STATISTICAL_FROM_CURRENCY', 'VF07',
'OUT_OF_DATE_FROM_CURRENCY', 'VF08',
'EMU_FROM_CURRENCY', 'VF09',
'NONEXISTANT_TO_CURRENCY', 'VF10',
'DISABLED_TO_CURRENCY', 'VF11',
'STATISTICAL_TO_CURRENCY', 'VF12',
'OUT_OF_DATE_TO_CURRENCY', 'VF13',
'EMU_TO_CURRENCY', 'VF14',
'DUPLICATE_ROWS','VF15',
'VF16')
BULK COLLECT INTO daily_rate_validation_failure.r_from_curr,
daily_rate_validation_failure.r_to_curr,
daily_rate_validation_failure.r_from_date,
daily_rate_validation_failure.r_to_date,
daily_rate_validation_failure.r_type,
daily_rate_validation_failure.r_rate,
daily_rate_validation_failure.r_inverse_rate,
daily_rate_validation_failure.r_error_code
FROM gl_daily_rates_interface
WHERE mode_flag IN('X', 'F')
AND batch_number = l_batch_number;
SELECT dr.from_currency,
dr.to_currency,
gldct.user_conversion_type,
dr.conversion_date,
dr.conversion_rate,
dr.rate_source_code
BULK COLLECT INTO sys_daily_rate_cannot_delete.r_from_curr,
sys_daily_rate_cannot_delete.r_to_curr,
sys_daily_rate_cannot_delete.r_type,
sys_daily_rate_cannot_delete.r_conversion_date,
sys_daily_rate_cannot_delete.r_rate,
sys_daily_rate_cannot_delete.r_rate_source_code
FROM gl_daily_rates dr,
gl_daily_conversion_types gldct,
gl_row_multipliers rm,
gl_daily_conversion_types ct,
gl_daily_rates_interface ri
WHERE ri.mode_flag = 'D'
AND ct.user_conversion_type = ri.user_conversion_type || ''
AND rm.multiplier BETWEEN 1 AND (TRUNC(ri.to_conversion_date)
- TRUNC(ri.from_conversion_date)
+ 1)
AND ((dr.from_currency = ri.from_currency
AND dr.to_currency = ri.to_currency)
OR (dr.from_currency = ri.to_currency
AND dr.to_currency = ri.from_currency))
AND dr.conversion_type = ct.conversion_type
AND dr.conversion_date = TRUNC(ri.from_conversion_date)+rm.multiplier-1
AND dr.rate_source_code = 'SYSTEM'
AND dr.conversion_type = gldct.conversion_type
AND ri.batch_number = l_batch_number;
IF sys_daily_rate_cannot_delete.r_from_curr.COUNT > 0 THEN
fnd_file.put_line
(fnd_file.LOG,
sys_daily_rate_cannot_delete.r_from_curr.COUNT
|| ' system rates found (which cannot be deleted)!');
'No system rate found (which cannot be deleted)!');
FOR i IN 1 .. sys_daily_rate_cannot_delete.r_from_curr.COUNT LOOP
IF page_line_count = 1 THEN
gl_crm_utilities_pkg.print_report_title;
gl_crm_utilities_pkg.print_delete_user_rate_warning;
gl_crm_utilities_pkg.print_delete_user_rate_warning;
RPAD(sys_daily_rate_cannot_delete.r_type(i), 31, ' ')
|| RPAD
(sys_daily_rate_cannot_delete.r_conversion_date(i),
30, ' ')
|| RPAD(sys_daily_rate_cannot_delete.r_from_curr(i), 16,
' ')
|| RPAD(sys_daily_rate_cannot_delete.r_to_curr(i), 16,
' ')
|| LPAD(sys_daily_rate_cannot_delete.r_rate(i) || ' ',
13, ' ')
|| RPAD
(sys_daily_rate_cannot_delete.r_rate_source_code(i),
26, ' '));
SELECT dr.from_currency,
dr.to_currency,
gldct.user_conversion_type,
dr.conversion_date,
dr.conversion_rate,
dr.rate_source_code
BULK COLLECT INTO sys_daily_rate_cannot_override.r_from_curr,
sys_daily_rate_cannot_override.r_to_curr,
sys_daily_rate_cannot_override.r_type,
sys_daily_rate_cannot_override.r_conversion_date,
sys_daily_rate_cannot_override.r_rate,
sys_daily_rate_cannot_override.r_rate_source_code
FROM gl_daily_rates dr,
gl_daily_conversion_types gldct,
gl_row_multipliers rm,
gl_daily_conversion_types ct,
gl_daily_rates_interface ri
WHERE ct.user_conversion_type = ri.user_conversion_type || ''
AND rm.multiplier BETWEEN 1 AND (TRUNC(ri.to_conversion_date)
- TRUNC(ri.from_conversion_date)
+ 1)
AND ((dr.from_currency = ri.from_currency
AND dr.to_currency = ri.to_currency
AND ri.mode_flag in ('I','T'))
OR (dr.from_currency = ri.to_currency
AND dr.to_currency = ri.from_currency
AND ri.mode_flag = 'I'))
AND dr.conversion_type = ct.conversion_type
AND dr.conversion_date = TRUNC(ri.from_conversion_date)+rm.multiplier-1
AND dr.rate_source_code = 'SYSTEM'
AND dr.conversion_type = gldct.conversion_type
AND ri.batch_number = l_batch_number;
UPDATE gl_daily_rates_interface ri
SET used_for_ab_translation =
( SELECT nvl(max('Y'), 'N')
FROM gl_daily_conversion_types ct,
gl_ledgers led,
gl_ledger_relationships rel
WHERE ct.user_conversion_type = ri.user_conversion_type
AND rel.source_ledger_id = led.ledger_id
AND rel.target_ledger_id = led.ledger_id
AND rel.target_ledger_category_code = 'ALC'
AND rel.application_id = 101
AND led.currency_code IN (ri.from_currency, ri.to_currency)
AND rel.target_currency_code IN (ri.from_currency, ri.to_currency)
AND ( led.daily_translation_rate_type = ct.conversion_type
OR nvl(rel.alc_period_average_rate_type,
led.period_average_rate_type) = ct.conversion_type
OR nvl(rel.alc_period_end_rate_type,
led.period_end_rate_type) = ct.conversion_type)
AND ri.mode_flag IN ('I', 'D', 'T', 'N')
AND ri.batch_number = l_batch_number);
SELECT 'Y'
INTO l_launch_rate_change
FROM gl_daily_rates_interface
WHERE used_for_ab_translation = 'Y'
AND ROWNUM < 2;
'Marking D for rates meant to delete with types used for ab translation');
UPDATE gl_daily_rates dr
SET status_code = 'D'
WHERE ( (dr.rate_source_code IS NULL)
OR ( dr.rate_source_code IN('USER', 'TREASURY')
AND golden_rule_flag <> 'USER')
OR golden_rule_flag = 'USER')
AND (dr.from_currency,
dr.to_currency,
dr.conversion_type,
dr.conversion_date) IN(
SELECT ri.from_currency, ri.to_currency, --direct rate
ct.conversion_type,
TRUNC(ri.from_conversion_date) + rm.multiplier
- 1
FROM gl_row_multipliers rm,
gl_daily_conversion_types ct,
gl_daily_rates_interface ri
WHERE ri.mode_flag = 'D'
AND ri.batch_number = l_batch_number
AND ri.used_for_ab_translation = 'Y'
AND ct.user_conversion_type =
ri.user_conversion_type || ''
AND rm.multiplier BETWEEN 1
AND TRUNC
(ri.to_conversion_date)
- TRUNC
(ri.from_conversion_date)
+ 1
UNION ALL
SELECT ri.to_currency, ri.from_currency, --inverse rate
ct.conversion_type,
TRUNC(ri.from_conversion_date) + rm.multiplier
- 1
FROM gl_row_multipliers rm,
gl_daily_conversion_types ct,
gl_daily_rates_interface ri
WHERE ri.mode_flag = 'D'
AND ri.batch_number = l_batch_number
AND ri.used_for_ab_translation = 'Y'
AND ct.user_conversion_type =
ri.user_conversion_type || ''
AND rm.multiplier BETWEEN 1
AND TRUNC
(ri.to_conversion_date)
- TRUNC
(ri.from_conversion_date)
+ 1
);
'Deleting rates meant for insert and for deleted rates with types not used for ab translation');
DELETE gl_daily_rates dr
WHERE ( (dr.rate_source_code IS NULL)
OR ( dr.rate_source_code IN('USER', 'TREASURY')
AND golden_rule_flag <> 'USER')
OR golden_rule_flag = 'USER')
AND (dr.from_currency,
dr.to_currency,
dr.conversion_type,
dr.conversion_date) IN(
SELECT ri.from_currency, ri.to_currency, --direct rates
ct.conversion_type,
TRUNC(ri.from_conversion_date)
+ rm.multiplier - 1
FROM gl_row_multipliers rm,
gl_daily_conversion_types ct,
gl_daily_rates_interface ri
WHERE ri.batch_number = l_batch_number
AND ( ri.mode_flag IN('I', 'T')
OR ( ri.mode_flag = 'D'
AND ri.used_for_ab_translation <> 'Y'))
AND ct.user_conversion_type =
ri.user_conversion_type || ''
AND rm.multiplier BETWEEN 1
AND TRUNC
(ri.to_conversion_date)
- TRUNC
(ri.from_conversion_date)
+ 1
UNION ALL
SELECT ri.to_currency, ri.from_currency, --inverse rates
ct.conversion_type,
TRUNC(ri.from_conversion_date)
+ rm.multiplier - 1
FROM gl_row_multipliers rm,
gl_daily_conversion_types ct,
gl_daily_rates_interface ri
WHERE ri.batch_number = l_batch_number
AND ( ri.mode_flag IN('I', 'T')
OR ( ri.mode_flag = 'D'
AND ri.used_for_ab_translation <> 'Y'))
AND ct.user_conversion_type =
ri.user_conversion_type || ''
AND rm.multiplier BETWEEN 1
AND TRUNC
(ri.to_conversion_date)
- TRUNC
(ri.from_conversion_date)
+ 1
);
SQL%ROWCOUNT || ' rows deleted');
fnd_file.put_line(fnd_file.LOG, 'Insert all rates.');
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,
rate_source_code)
SELECT ri.from_currency, ri.to_currency, --direct rates
TRUNC(ri.from_conversion_date) + rm.multiplier - 1,
ct.conversion_type, ri.conversion_rate,
DECODE(ri.used_for_ab_translation, 'Y', 'O', 'C'),
SYSDATE, NVL(ri.user_id, 1), SYSDATE,
NVL(ri.user_id, 1), 1, ri.CONTEXT, ri.attribute1,
ri.attribute2, ri.attribute3, ri.attribute4,
ri.attribute5, ri.attribute6, ri.attribute7,
ri.attribute8, ri.attribute9, ri.attribute10,
ri.attribute11, ri.attribute12, ri.attribute13,
ri.attribute14, ri.attribute15,
DECODE(ri.mode_flag, 'T', 'TREASURY', 'N', 'TREASURY', 'USER')
FROM gl_row_multipliers rm,
gl_daily_conversion_types ct,
gl_daily_rates_interface ri
WHERE ri.mode_flag IN('I', 'T', 'N')
AND ct.user_conversion_type = ri.user_conversion_type || ''
AND rm.multiplier BETWEEN 1
AND TRUNC(ri.to_conversion_date)
- TRUNC(ri.from_conversion_date)
+ 1
AND ri.batch_number = l_batch_number
AND NOT EXISTS(
SELECT 1
FROM gl_daily_rates dr
WHERE dr.from_currency = ri.from_currency
AND dr.to_currency = ri.to_currency
AND dr.conversion_type = ct.conversion_type
AND dr.conversion_date =
TRUNC(ri.from_conversion_date)
+ rm.multiplier - 1)
UNION ALL
SELECT ri.to_currency, ri.from_currency, --inverse rates
TRUNC(ri.from_conversion_date) + rm.multiplier - 1,
ct.conversion_type,
NVL(ri.inverse_conversion_rate, 1 / ri.conversion_rate),
DECODE(ri.used_for_ab_translation, 'Y', 'O', 'C'),
SYSDATE, NVL(ri.user_id, 1), SYSDATE,
NVL(ri.user_id, 1), 1, ri.CONTEXT, ri.attribute1,
ri.attribute2, ri.attribute3, ri.attribute4,
ri.attribute5, ri.attribute6, ri.attribute7,
ri.attribute8, ri.attribute9, ri.attribute10,
ri.attribute11, ri.attribute12, ri.attribute13,
ri.attribute14, ri.attribute15,
DECODE(ri.mode_flag, 'T', 'TREASURY', 'N', 'TREASURY', 'USER')
FROM gl_row_multipliers rm,
gl_daily_conversion_types ct,
gl_daily_rates_interface ri
WHERE ri.mode_flag IN('I', 'T', 'N')
AND ct.user_conversion_type = ri.user_conversion_type || ''
AND rm.multiplier BETWEEN 1
AND TRUNC(ri.to_conversion_date)
- TRUNC(ri.from_conversion_date)
+ 1
AND ri.batch_number = l_batch_number
AND NOT EXISTS(
SELECT 1
FROM gl_daily_rates dr
WHERE dr.from_currency = ri.to_currency
AND dr.to_currency = ri.from_currency
AND dr.conversion_type = ct.conversion_type
AND dr.conversion_date =
TRUNC(ri.from_conversion_date)
+ rm.multiplier - 1);
SQL%ROWCOUNT || ' rows inserted');
SELECT COUNT(*)
INTO rows_need_calculation
FROM gl_daily_rates_interface
WHERE mode_flag IN('I', 'D', 'T', 'N')
AND user_conversion_type IN(
SELECT user_conversion_type
FROM gl_daily_conversion_types gct,
gl_cross_rate_rules gcrs
WHERE gct.conversion_type = gcrs.conversion_type)
AND batch_number = l_batch_number
AND rownum < 2;
DELETE FROM gl_daily_rates_interface
WHERE mode_flag IN('I', 'D', 'T', 'N')
AND batch_number = l_batch_number
AND user_conversion_type NOT IN(
SELECT user_conversion_type
FROM gl_daily_conversion_types gct,
gl_cross_rate_rules gcrs
WHERE gct.conversion_type = gcrs.conversion_type);
DELETE FROM gl_cross_rate_temp;
INSERT INTO gl_cross_rate_temp
(conversion_type, pivot_currency, from_currency,
to_currency, from_conversion_date,
to_conversion_date, conversion_rate,
inverse_conversion_rate, mode_flag,
used_for_ab_translation)
SELECT gldct.conversion_type,
DECODE(gldri.from_currency,
glcrs.pivot_currency, gldri.from_currency,
gldri.to_currency),
DECODE(gldri.from_currency,
glcrs.pivot_currency, gldri.to_currency,
gldri.from_currency),
glcrsd.to_currency, gldri.from_conversion_date,
gldri.to_conversion_date,
DECODE(gldri.from_currency,
glcrs.pivot_currency, gldri.conversion_rate,
gldri.inverse_conversion_rate),
DECODE(gldri.from_currency,
glcrs.pivot_currency, gldri.inverse_conversion_rate,
gldri.conversion_rate),
DECODE(gldri.mode_flag, 'D', 'D', 'I'),
gldri.used_for_ab_translation
FROM gl_daily_rates_interface gldri,
gl_daily_conversion_types gldct,
gl_cross_rate_rules glcrs,
gl_cross_rate_rule_dtls glcrsd
WHERE gldri.mode_flag IN('I', 'D', 'T', 'N')
AND gldri.user_conversion_type = gldct.user_conversion_type
AND gldct.conversion_type = glcrs.conversion_type
AND ( ( (gldri.from_currency = glcrs.pivot_currency)
AND (gldri.to_currency IN(
SELECT DISTINCT from_currency
FROM gl_cross_rate_rule_dtls glcrsd2
WHERE glcrs.conversion_type =
glcrsd2.conversion_type
--AND glcrs.pivot_currency = glcrsd2.pivot_currency
AND glcrsd2.enabled_flag = 'Y')))
OR ( (gldri.to_currency = glcrs.pivot_currency)
AND (gldri.from_currency IN(
SELECT DISTINCT from_currency
FROM gl_cross_rate_rule_dtls glcrsd3
WHERE glcrs.conversion_type =
glcrsd3.conversion_type
--AND glcrs.pivot_currency = glcrsd3.pivot_currency
AND glcrsd3.enabled_flag = 'Y'))))
AND glcrsd.conversion_type = gldct.conversion_type
AND glcrs.pivot_currency =
DECODE(gldri.from_currency,
glcrs.pivot_currency, gldri.from_currency,
gldri.to_currency)
AND glcrsd.from_currency =
DECODE(gldri.from_currency,
glcrs.pivot_currency, gldri.to_currency,
gldri.from_currency)
AND glcrsd.enabled_flag = 'Y'
AND glcrsd.to_currency <> glcrs.pivot_currency
AND gldri.batch_number = l_batch_number;
fnd_file.put_line(fnd_file.LOG, 'Update the used for ab translation');
UPDATE gl_cross_rate_temp ri
SET used_for_ab_translation =
( SELECT nvl(max('Y'), 'N')
FROM gl_daily_conversion_types ct,
gl_ledgers led,
gl_ledger_relationships rel
WHERE ct.conversion_type = ri.conversion_type
AND rel.source_ledger_id = led.ledger_id
AND rel.target_ledger_id = led.ledger_id
AND rel.target_ledger_category_code = 'ALC'
AND rel.application_id = 101
AND led.currency_code IN (ri.from_currency, ri.to_currency)
AND rel.target_currency_code IN (ri.from_currency, ri.to_currency)
AND ( led.daily_translation_rate_type = ct.conversion_type
OR nvl(rel.alc_period_average_rate_type,
led.period_average_rate_type) = ct.conversion_type
OR nvl(rel.alc_period_end_rate_type,
led.period_end_rate_type) = ct.conversion_type)
AND ri.mode_flag IN ('I', 'D', 'T', 'N'));
SELECT 'Y'
INTO l_launch_rate_change
FROM gl_cross_rate_temp
WHERE used_for_ab_translation = 'Y'
AND ROWNUM < 2;
'User Rate Rule or Both Rule; Checking if any user-defined rates cannot been cross-deleted.');
SELECT DISTINCT gldr.from_currency,
gldr.to_currency,
gldct.user_conversion_type,
gldr.conversion_date,
gldr.conversion_rate,
gldr.rate_source_code
BULK COLLECT INTO usr_daily_rate_cannot_delete.r_from_curr,
usr_daily_rate_cannot_delete.r_to_curr,
usr_daily_rate_cannot_delete.r_type,
usr_daily_rate_cannot_delete.r_conversion_date,
usr_daily_rate_cannot_delete.r_rate,
usr_daily_rate_cannot_delete.r_rate_source_code
FROM gl_daily_rates gldr,
gl_daily_conversion_types gldct,
gl_cross_rate_temp glcrt,
gl_row_multipliers glrm
WHERE gldr.conversion_type = gldct.conversion_type
AND ( (gldr.rate_source_code IS NULL)
OR ( (gldr.rate_source_code IS NOT NULL)
AND (gldr.rate_source_code IN
('USER', 'TREASURY'))))
AND glcrt.mode_flag = 'D'
AND ( ( (gldr.from_currency =
glcrt.from_currency)
AND (gldr.to_currency = glcrt.to_currency))
OR ( (gldr.to_currency =
glcrt.from_currency)
AND (gldr.from_currency =
glcrt.to_currency)))
AND gldr.conversion_type = glcrt.conversion_type
AND gldr.conversion_date =
TRUNC(glcrt.from_conversion_date)
+ glrm.multiplier - 1
AND glrm.multiplier BETWEEN 1
AND TRUNC
(glcrt.to_conversion_date)
- TRUNC
(glcrt.from_conversion_date)
+ 1;
FOR i IN 1 .. usr_daily_rate_cannot_delete.r_from_curr.COUNT LOOP
IF page_line_count = 1 THEN
gl_crm_utilities_pkg.print_report_title;
gl_crm_utilities_pkg.print_delete_sys_rate_warning;
gl_crm_utilities_pkg.print_delete_sys_rate_warning;
RPAD(usr_daily_rate_cannot_delete.r_type(i), 31, ' ')
|| RPAD
(usr_daily_rate_cannot_delete.r_conversion_date(i),
30, ' ')
|| RPAD(usr_daily_rate_cannot_delete.r_from_curr(i), 16,
' ')
|| RPAD(usr_daily_rate_cannot_delete.r_to_curr(i), 16,
' ')
|| LPAD(usr_daily_rate_cannot_delete.r_rate(i) || ' ',
13, ' ')
|| RPAD
(usr_daily_rate_cannot_delete.r_rate_source_code(i),
26, ' '));
SELECT DISTINCT gldr.from_currency,
gldr.to_currency,
gldct.user_conversion_type,
gldr.conversion_date,
gldr.conversion_rate,
gldr.rate_source_code
BULK COLLECT INTO usr_daily_rate_cannot_override.r_from_curr,
usr_daily_rate_cannot_override.r_to_curr,
usr_daily_rate_cannot_override.r_type,
usr_daily_rate_cannot_override.r_conversion_date,
usr_daily_rate_cannot_override.r_rate,
usr_daily_rate_cannot_override.r_rate_source_code
FROM gl_daily_rates gldr,
gl_daily_conversion_types gldct,
gl_cross_rate_temp glcrt,
gl_row_multipliers glrm
WHERE gldr.conversion_type = gldct.conversion_type
AND ( (gldr.rate_source_code IS NULL)
OR ( (gldr.rate_source_code IS NOT NULL)
AND (gldr.rate_source_code IN
('USER', 'TREASURY'))))
AND glcrt.mode_flag in ('I', 'T', 'N')
AND ( ( (gldr.from_currency =
glcrt.from_currency)
AND (gldr.to_currency = glcrt.to_currency))
OR ( (gldr.to_currency =
glcrt.from_currency)
AND (gldr.from_currency =
glcrt.to_currency)))
AND gldr.conversion_type = glcrt.conversion_type
AND gldr.conversion_date =
TRUNC(glcrt.from_conversion_date)
+ glrm.multiplier - 1
AND glrm.multiplier BETWEEN 1
AND TRUNC
(glcrt.to_conversion_date)
- TRUNC
(glcrt.from_conversion_date)
+ 1;
UPDATE gl_daily_rates gldr
SET status_code = 'D'
WHERE ( (gldr.rate_source_code IS NOT NULL
AND gldr.rate_source_code = 'SYSTEM'
AND golden_rule_flag <> 'SYSTEM')
OR golden_rule_flag = 'SYSTEM')
AND (gldr.from_currency,
gldr.to_currency,
gldr.conversion_type,
gldr.conversion_date) IN(
SELECT glcrt.from_currency, glcrt.to_currency, --direct rates
glcrt.conversion_type,
TRUNC(glcrt.from_conversion_date)
+ glrm.multiplier - 1
FROM gl_row_multipliers glrm,
gl_cross_rate_temp glcrt,
gl_daily_rates gldr
WHERE glcrt.mode_flag = 'D'
AND glcrt.used_for_ab_translation = 'Y'
AND gldr.from_currency = glcrt.from_currency
AND gldr.to_currency = glcrt.to_currency
AND gldr.conversion_type = glcrt.conversion_type
AND gldr.conversion_date =
TRUNC(glcrt.from_conversion_date)
+ glrm.multiplier - 1
AND glrm.multiplier BETWEEN 1
AND TRUNC
(glcrt.to_conversion_date)
- TRUNC
(glcrt.from_conversion_date)
+ 1
UNION ALL
SELECT glcrt.to_currency, glcrt.from_currency, -- inverse rates
glcrt.conversion_type,
TRUNC(glcrt.from_conversion_date)
+ glrm.multiplier - 1
FROM gl_row_multipliers glrm,
gl_cross_rate_temp glcrt,
gl_daily_rates gldr
WHERE glcrt.mode_flag = 'D'
AND glcrt.used_for_ab_translation = 'Y'
AND gldr.to_currency = glcrt.from_currency
AND gldr.from_currency = glcrt.to_currency
AND gldr.conversion_type = glcrt.conversion_type
AND gldr.conversion_date =
TRUNC(glcrt.from_conversion_date)
+ glrm.multiplier - 1
AND glrm.multiplier BETWEEN 1
AND TRUNC
(glcrt.to_conversion_date)
- TRUNC
(glcrt.from_conversion_date)
+ 1
);
'Delete for Cross Rates');
DELETE FROM gl_daily_rates gldr
WHERE ( (gldr.rate_source_code IS NOT NULL
AND gldr.rate_source_code = 'SYSTEM'
AND golden_rule_flag <> 'SYSTEM')
OR golden_rule_flag = 'SYSTEM')
AND (gldr.from_currency,
gldr.to_currency,
gldr.conversion_type,
gldr.conversion_date) IN(
SELECT glcrt.from_currency, glcrt.to_currency,
glcrt.conversion_type,
TRUNC(glcrt.from_conversion_date)
+ glrm.multiplier - 1
FROM gl_row_multipliers glrm,
gl_cross_rate_temp glcrt,
gl_daily_rates gldr
WHERE ( glcrt.mode_flag in ('I', 'T')
OR ( glcrt.mode_flag = 'D'
AND glcrt.used_for_ab_translation <>
'Y'))
AND gldr.from_currency = glcrt.from_currency
AND gldr.to_currency = glcrt.to_currency
AND gldr.conversion_type =
glcrt.conversion_type
AND gldr.conversion_date =
TRUNC(glcrt.from_conversion_date)
+ glrm.multiplier - 1
AND glrm.multiplier BETWEEN 1
AND TRUNC
(glcrt.to_conversion_date)
- TRUNC
(glcrt.from_conversion_date)
+ 1
UNION ALL
SELECT glcrt.to_currency, glcrt.from_currency,
glcrt.conversion_type,
TRUNC(glcrt.from_conversion_date)
+ glrm.multiplier - 1
FROM gl_row_multipliers glrm,
gl_cross_rate_temp glcrt,
gl_daily_rates gldr
WHERE ( glcrt.mode_flag in ('I', 'T')
OR ( glcrt.mode_flag = 'D'
AND glcrt.used_for_ab_translation <>
'Y'))
AND gldr.to_currency = glcrt.from_currency
AND gldr.from_currency = glcrt.to_currency
AND gldr.conversion_type =
glcrt.conversion_type
AND gldr.conversion_date =
TRUNC(glcrt.from_conversion_date)
+ glrm.multiplier - 1
AND glrm.multiplier BETWEEN 1
AND TRUNC
(glcrt.to_conversion_date)
- TRUNC
(glcrt.from_conversion_date)
+ 1
);
SQL%ROWCOUNT || ' rows deleted.');
UPDATE gl_cross_rate_temp rt
SET mode_flag = 'F'
WHERE mode_flag = 'N'
AND (EXISTS (SELECT 1 FROM gl_daily_rates dr
WHERE dr.from_currency = rt.from_currency
AND dr.to_currency = rt.to_currency
AND dr.conversion_type = rt.conversion_type
AND dr.conversion_date BETWEEN rt.from_conversion_date
AND rt.to_conversion_date)
OR EXISTS (SELECT 1 FROM gl_daily_rates dr
WHERE dr.from_currency = rt.to_currency
AND dr.to_currency = rt.from_currency
AND dr.conversion_type = rt.conversion_type
AND dr.conversion_date BETWEEN rt.from_conversion_date
AND rt.to_conversion_date));
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,
rate_source_code)
SELECT glcrt.from_currency, glcrt.to_currency,
TRUNC(glcrt.from_conversion_date) + glrm.multiplier
- 1,
glcrt.conversion_type,
glcrt.inverse_conversion_rate * gldr.conversion_rate,
DECODE(glcrt.used_for_ab_translation, 'Y', 'O', 'C'),
SYSDATE, 1, SYSDATE, 1, 1, 'SYSTEM'
FROM gl_row_multipliers glrm,
gl_cross_rate_temp glcrt,
gl_daily_rates gldr
WHERE glcrt.mode_flag in ('I', 'T', 'N')
AND gldr.from_currency = glcrt.pivot_currency
AND gldr.to_currency = glcrt.to_currency
AND gldr.conversion_type = glcrt.conversion_type
AND gldr.conversion_date =
TRUNC(glcrt.from_conversion_date)
+ glrm.multiplier - 1
AND glrm.multiplier BETWEEN 1
AND TRUNC
(glcrt.to_conversion_date)
- TRUNC
(glcrt.from_conversion_date)
+ 1
AND ( NOT EXISTS(
SELECT 1
FROM gl_daily_rates dr
WHERE dr.from_currency = glcrt.from_currency
AND dr.to_currency = glcrt.to_currency
AND dr.conversion_type =
glcrt.conversion_type
AND dr.conversion_date =
TRUNC(glcrt.from_conversion_date)
+ glrm.multiplier - 1)
OR NOT EXISTS(
SELECT 1
FROM gl_daily_rates dr
WHERE dr.from_currency = glcrt.to_currency
AND dr.to_currency = glcrt.from_currency
AND dr.conversion_type =
glcrt.conversion_type
AND dr.conversion_date =
TRUNC(glcrt.from_conversion_date)
+ glrm.multiplier - 1))
UNION
SELECT glcrt.to_currency, glcrt.from_currency,
TRUNC(glcrt.from_conversion_date) + glrm.multiplier
- 1,
glcrt.conversion_type,
glcrt.conversion_rate * gldr.conversion_rate,
DECODE(glcrt.used_for_ab_translation, 'Y', 'O', 'C'),
SYSDATE, 1, SYSDATE, 1, 1, 'SYSTEM'
FROM gl_row_multipliers glrm,
gl_cross_rate_temp glcrt,
gl_daily_rates gldr
WHERE glcrt.mode_flag in ('I', 'T', 'N')
AND gldr.to_currency = glcrt.pivot_currency
AND gldr.from_currency = glcrt.to_currency
AND gldr.conversion_type = glcrt.conversion_type
AND gldr.conversion_date =
TRUNC(glcrt.from_conversion_date)
+ glrm.multiplier - 1
AND glrm.multiplier BETWEEN 1
AND TRUNC
(glcrt.to_conversion_date)
- TRUNC
(glcrt.from_conversion_date)
+ 1
AND ( NOT EXISTS(
SELECT 1
FROM gl_daily_rates dr
WHERE dr.from_currency = glcrt.from_currency
AND dr.to_currency = glcrt.to_currency
AND dr.conversion_type =
glcrt.conversion_type
AND dr.conversion_date =
TRUNC(glcrt.from_conversion_date)
+ glrm.multiplier - 1)
OR NOT EXISTS(
SELECT 1
FROM gl_daily_rates dr
WHERE dr.from_currency = glcrt.to_currency
AND dr.to_currency = glcrt.from_currency
AND dr.conversion_type =
glcrt.conversion_type
AND dr.conversion_date =
TRUNC(glcrt.from_conversion_date)
+ glrm.multiplier - 1));
SQL%ROWCOUNT || ' rows inserted.');
DELETE FROM gl_daily_rates_interface
WHERE mode_flag IN('I', 'D', 'T', 'N')
AND batch_number = l_batch_number;
DELETE FROM gl_daily_rates_interface
WHERE mode_flag IN('I', 'D', 'T', 'N')
AND batch_number = l_batch_number;