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;Understand this decode
-
dr_gori
- Moderador
- Posts: 5024
- Joined: Mon, 03 May 2004 3:08 pm
- Location: Portland, OR USA
- Contact:
Thomas F. G
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
A simple decode to facilitate the life of the programmer: (package gl_crm_utilities_pkg)
-
DanielNN
- Moderador
- Posts: 641
- Joined: Mon, 03 Sep 2007 3:26 pm
- Location: Fortaleza - CE
att,
Daniel N.N.
Daniel N.N.
This is the famous (newly created) Euristology of nested conditions of Buzz Lightyear:
"to infinity and beyond".
in general I prefer to use Decode to Case, I found until I had done some "big", but nothing, nothing, nothing, compared to this.
"to infinity and beyond".
in general I prefer to use Decode to Case, I found until I had done some "big", but nothing, nothing, nothing, compared to this.
-
dr_gori
- Moderador
- Posts: 5024
- Joined: Mon, 03 May 2004 3:08 pm
- Location: Portland, OR USA
- Contact:
Thomas F. G
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
noctifero wrote: This is the famous (newly created) Euristics of nested conditions of Buzz Lightyear:
"to infinity and beyond" ..
Hooahahahahahahahahahaha: LOL:: LOL:
-
- Information
-
Who is online
Users browsing this forum: No registered users and 1 guest