NVL SQL
Syntax:
NVL( string1, replace_with )
Explanation:
 |
| NVL SQL |
Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts
Monday, September 9, 2019
Oracle Pl SQL Cursor
Oracle Pl SQL Cursor
Declare
lv_desc varchar2(1000);
cursor c1 is
select nvl(t.name,rctla.description) name
from ar_memo_lines_all_tl t,
ar_memo_lines_all_b b,
ra_customer_trx_lines_all rctla
WHERE b.memo_line_id = t.memo_line_id(+)
and b.MEMO_LINE_ID(+) = rctla.MEMO_LINE_ID
and b.LINE_TYPE(+) = rctla.LINE_TYPE
and rctla.CUSTOMER_TRX_ID = :CUSTOMER_TRX_ID
and rctla.ORG_ID = b.ORG_ID(+)
and (t.LANGUAGE = 'US' or t.LANGUAGE is null)
and (t.org_id = b.org_id or( t.org_id is null and b.org_id is null))
and rctla.org_id = 82;
begin
lv_desc := null;
FOR type in c1
LOOP
if lv_desc is null then
lv_desc := type.name;
else
lv_desc := lv_desc||', '||type.name;
end if;
END LOOP;
RETURN lv_desc;
end;
AP Reconciliation
AP Reconciliation
select 'Invoice Accounting' Accounting
,aia.ORG_ID Operating_Unit
,pv.SEGMENT1 Supplier_Code
,aia.INVOICE_TYPE_LOOKUP_CODE Invoice_Type
,pv.VENDOR_TYPE_LOOKUP_CODE VENDOR_TYPE_LOOKUP_CODE
,pv.VENDOR_NAME Supplier_Name
,pvsa.VENDOR_SITE_CODE Supplier_Site
,aia.INVOICE_ID INVOICE_ID_or_CHECK_ID
,to_char( aia.DOC_SEQUENCE_VALUE ) voucher_no
,pvsa.ATTRIBUTE1 leg_code
,aia.INVOICE_TYPE_LOOKUP_CODE PAY_COD_or_INV_TYP_LOKUP_COD
, aia.INVOICE_NUM INVOICE_NUMBER
,TO_CHAR('N/A') CHECK_NUMBER
,DECODE(AP_INVOICES_PKG.GET_POSTING_STATUS( AIa.INVOICE_ID),'P','Partial','Y','Accounted','No') ACCOUNTED
,decode((select count(aida.ATTRIBUTE2) from ap_invoice_distributions_all aida where aida.INVOICE_ID = aia.INVOICE_ID and aida.LINE_TYPE_LOOKUP_CODE = 'ITEM' and aida.ATTRIBUTE_CATEGORY = 'Purchasing Information'),
1,(select (aida.ATTRIBUTE2) from ap_invoice_distributions_all aida where aida.INVOICE_ID = aia.INVOICE_ID and aida.LINE_TYPE_LOOKUP_CODE = 'ITEM' and aida.ATTRIBUTE_CATEGORY = 'Purchasing Information')
,'Invoice Num = '||aia.INVOICE_NUM) RR
,xel.ACCOUNTED_DR ACCOUNTED_DR
,xel.ACCOUNTED_CR ACCOUNTED_CR
,xel.CODE_COMBINATION_ID
,gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
|| '.'
|| gcc.segment8 code
,xel.code_combination_id account
,XEL.CODE_COMBINATION_ID account_desc
,TO_CHAR(nvl(aia.EXCHANGE_RATE,1)) exchange_rate
-- ,NVL(aia.ATTRIBUTE4,aia.APPROVAL_DESCRIPTION) Legacy_Voucher
,aia.DESCRIPTION
,aia.GL_DATE
,aia.INVOICE_DATE INVOICE_DATE_or_CHECK_DATE
,aia.CREATION_DATE
, AP_INVOICES_PKG.GET_APPROVAL_STATUS( AIA.INVOICE_ID,
AIA.INVOICE_AMOUNT, AIA.PAYMENT_STATUS_FLAG,
AIA.INVOICE_TYPE_LOOKUP_CODE) APPROVAL_STATUS_LOOKUP_CODE
,xel.ACCOUNTING_DATE
,xel.GL_TRANSFER_MODE_CODE
,xel.LEDGER_ID
,xel.CURRENCY_CODE
,xel.ACCOUNTING_CLASS_CODE
,xeh.GL_TRANSFER_STATUS_CODE
,APPS.FND_USER_AP_PKG.GET_USER_NAME(AIA.CREATED_BY) user_name
from xla_ae_lines xel, xla_ae_headers xeh, ap_invoices_all aia,
xla.xla_transaction_entities xte
,po_vendors pv
,po_vendor_sites_all pvsa
,gl_code_combinations gcc
where xte.application_id = 200
and xel.application_id = xeh.application_id
and xte.application_id = xeh.application_id
and xel.ae_header_id = xeh.ae_header_id
and xte.entity_code = 'AP_INVOICES'
and xte.source_id_int_1 = aia.invoice_id
and xte.entity_id = xeh.entity_id
and aia.VENDOR_ID = pv.VENDOR_ID
and aia.VENDOR_SITE_ID = pvsa.VENDOR_SITE_ID
and xel.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
and xel.LEDGER_ID = :p_sob_id
and xel.ACCOUNTING_CLASS_CODE in ('LIABILITY','PREPAID_EXPENSE')
-- AND XEL.CODE_COMBINATION_ID IN (SELECT GC.CODE_COMBINATION_ID FROM GL_CODE_COMBINATIONS GC
--GC.SEGMENT4 IN ('2201010001','2201010002','2201010003','2201010004','2201010005','3207021001','3207021002','3207021003','3207021004','3207021005')
--)
-- and (TO_CHAR(AIA.INVOICE_DATE, 'YYYY/MM/DD')||' 00:00:00' BETWEEN :p_DATE_FROM AND :P_DATE_TO --11380
-- OR TO_CHAR(xel.accounting_DATE, 'YYYY/MM/DD')||' 00:00:00' BETWEEN :p_DATE_FROM AND :P_DATE_TO --11380 )
-- select 30797 + 7910 from dual
union all
select 'Check Accounting' Accounting
,aca.ORG_ID Operating_Unit
,pv.SEGMENT1 Supplier_Code
,aia.INVOICE_TYPE_LOOKUP_CODE Invoice_Type
,pv.VENDOR_TYPE_LOOKUP_CODE VENDOR_TYPE_LOOKUP_CODE
,pv.VENDOR_NAME Supplier_Name
,pvsa.VENDOR_SITE_CODE Supplier_Site
,aca.CHECK_ID INVOICE_ID_or_CHECK_ID
,'N/A' voucher_no
,pvsa.ATTRIBUTE1 leg_code
,'Invoice' PAY_COD_or_INV_TYP_LOKUP_COD
,to_char( aia.INVOICE_NUM ) INVOICE_NUM
,TO_CHAR(ACA.CHECK_NUMBER) CHECK_NUMBER
,DECODE(AP_CHECKS_PKG.GET_POSTING_STATUS(ACa.CHECK_ID),'Y','Processed','N','Unprocessed','P','Partial','S','Processing','Check it Out') ACCOUNTED
,'N/A' RR
,xel.ACCOUNTED_DR ACCOUNTED_DR
,xel.ACCOUNTED_CR ACCOUNTED_CR
,xel.CODE_COMBINATION_ID
,gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
|| '.'
|| gcc.segment8 code
,xel.code_combination_id account
,XEL.CODE_COMBINATION_ID account_desc
-- ,decode(:p_sob_id,2022,apps.get_acc_code_all(xel.code_combination_id),2026,apps.get_acc_code_all_ns(xel.code_combination_id)) account
-- ,decode(:p_sob_id,2022,apps.get_acc_desc(XEL.CODE_COMBINATION_ID),2026,apps.get_acc_desc_ns(XEL.CODE_COMBINATION_ID)) account_desc
,'N/A' exchange_rate
-- ,'N/A' Legacy_Voucher
,aca.DESCRIPTION
,aca.CHECK_DATE GL_DATE
,aca.CHECK_DATE INVOICE_DATE_or_CHECK_DATE
,aca.CREATION_DATE
,'N/A' APPROVAL_STATUS_LOOKUP_CODE
,xel.ACCOUNTING_DATE
,xel.GL_TRANSFER_MODE_CODE
,xel.LEDGER_ID
,xel.CURRENCY_CODE
,xel.ACCOUNTING_CLASS_CODE
,xeh.GL_TRANSFER_STATUS_CODE
,APPS.FND_USER_AP_PKG.GET_USER_NAME(ACA.CREATED_BY) user_name
from xla_ae_lines xel,
xla_ae_headers xeh,
ap_checks_all aca,
xla.xla_transaction_entities xte
,po_vendors pv
,po_vendor_sites_all pvsa
,gl_code_combinations gcc
,ap_invoices_all aia
where xte.application_id = 200
and xel.application_id = xeh.application_id
and xte.application_id = xeh.application_id
and xel.ae_header_id = xeh.ae_header_id
and xte.entity_code = 'AP_PAYMENTS'
and xte.source_id_int_1 = aca.CHECK_ID
and xte.entity_id = xeh.entity_id
and aia.invoice_id = aca.CHECK_ID--xte.source_id_int_1
and aca.VENDOR_ID = pv.VENDOR_ID
and aca.VENDOR_SITE_ID = pvsa.VENDOR_SITE_ID
and xel.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
and xel.ACCOUNTING_CLASS_CODE in ('LIABILITY','PREPAID_EXPENSE')
and xel.LEDGER_ID = :p_sob_id
-- AND XEL.CODE_COMBINATION_ID IN (SELECT GC.CODE_COMBINATION_ID FROM GL_CODE_COMBINATIONS GC
--WHERE GC.SEGMENT4 IN ('2201010001','2201010002','2201010003','2201010004','2201010005','3207021001','3207021002','3207021003','3207021004','3207021005'))
-- AND (TO_CHAR(XEL.ACCOUNTING_DATE, 'YYYY/MM/DD')||' 00:00:00' BETWEEN :p_DATE_FROM AND :P_DATE_TO --11380
-- OR TO_CHAR(ACA.CHECK_DATE, 'YYYY/MM/DD')||' 00:00:00' BETWEEN :p_DATE_FROM AND :P_DATE_TO --11380 );
Profile Option Query in Oracle APPS
Profile Option Query in Oracle APPS
select
b.user_profile_option_name "Long Name"
, a.profile_option_name "Short Name"
, decode(to_char(c.level_id),'10001','Site'
,'10002','Application'
,'10003','Responsibility'
,'10004','User'
,'Unknown') "Level"
, decode(to_char(c.level_id),'10001','Site'
,'10002',nvl(h.application_short_name,to_char(c.level_value))
,'10003',nvl(g.responsibility_name,to_char(c.level_value))
,'10004',nvl(e.user_name,to_char(c.level_value))
,'Unknown') "Level Value"
, c.PROFILE_OPTION_VALUE "Profile Value"
, c.profile_option_id "Profile ID"
, to_char(c.LAST_UPDATE_DATE,'DD-MON-YYYY HH24:MI') "Updated Date"
, nvl(d.user_name,to_char(c.last_updated_by)) "Updated By"
from
apps.fnd_profile_options a
, apps.FND_PROFILE_OPTIONS_VL b
, apps.FND_PROFILE_OPTION_VALUES c
, apps.FND_USER d
, apps.FND_USER e
, apps.FND_RESPONSIBILITY_VL g
, apps.FND_APPLICATION h
where
--a.application_id = nvl(401, a.application_id)
--and a.profile_option_name = nvl('INV', a.profile_option_name)
b.user_profile_option_name like 'Concurrent:OPP Response Timeout' -- 'AFLOG_ENABLED'
and a.profile_option_name = b.profile_option_name
and a.profile_option_id = c.profile_option_id
and a.application_id = c.application_id
and c.last_updated_by = d.user_id (+)
and c.level_value = e.user_id (+)
and c.level_value = g.responsibility_id (+)
and c.level_value = h.application_id (+)
order by
b.user_profile_option_name, c.level_id,
decode(to_char(c.level_id),'10001','Site'
,'10002',nvl(h.application_short_name,to_char(c.level_value))
,'10003',nvl(g.responsibility_name,to_char(c.level_value))
,'10004',nvl(e.user_name,to_char(c.level_value))
,'Unknown');
Oracle Password Encryption
Oracle Password Encryption
Simple password encryption package to demonstrate how values can be encrypted and decrypted using Oracle's DBMS Obfuscation Toolkit.
Connect to SYS AS SYSDBA
Create Package
CREATE OR REPLACE PACKAGE PASSWORD AS
function encrypt(i_password varchar2) return varchar2;
function decrypt(i_password varchar2) return varchar2;
END PASSWORD;
/
show errors
CREATE OR REPLACE PACKAGE BODY PASSWORD AS
-- key must be exactly 8 bytes long
c_encrypt_key varchar2(8) := 'key45678';
function encrypt (i_password varchar2) return varchar2 is
v_encrypted_val varchar2(38);
v_data varchar2(38);
begin
-- Input data must have a length divisible by eight
v_data := RPAD(i_password,(TRUNC(LENGTH(i_password)/8)+1)*8,CHR(0));
DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(
input_string => v_data,
key_string => c_encrypt_key,
encrypted_string => v_encrypted_val);
return v_encrypted_val;
end encrypt;
function decrypt (i_password varchar2) return varchar2 is
v_decrypted_val varchar2(38);
begin
DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(
input_string => i_password,
key_string => c_encrypt_key,
decrypted_string => v_decrypted_val);
return v_decrypted_val;
end decrypt;
end PASSWORD;
/
show errors
-- Test if it is working...
select password.encrypt('PASSWORD1') from dual;
select password.decrypt(app_password.encrypt('PASSWORD1')) from dual;
select password.encrypt('PSW2') from dual;
select password.decrypt(app_password.encrypt('PSW2')) from dual;
Wednesday, October 3, 2012
Spell Number: Convert Number into words
Spell Number: Convert Number into words
How can you convert a number into words using Oracle Sql Query?
How can I spell number?
Means:
12 = Twelve
102 = One Hundred Two
1020 = One Thousand Twenty
12.12
= twelve point twelve
Here’s a classy query which will convert number into
words but this technique is having limitations which is discussed later
in this topic .Please see the query below:
select to_char(to_date(:number,'j'),'jsp') from dual;
If I pass 134 in number, then the output will : one hundred thirty-four
SELECT TO_CHAR (TO_DATE (134, 'j'), 'jsp') FROM DUAL;
//Output: one hundred thirty-four
SELECT TO_CHAR (TO_DATE (34835, 'j'), 'jsp') FROM DUAL;
//Output: thirty-four thousand eight hundred
thirty-five
SELECT TO_CHAR (TO_DATE (3447837, 'j'), 'jsp') FROM DUAL;
//Output: three million four hundred forty-seven thousand eight hundred thirty-seven
Understanding:
So how the query works?
If you look into the inner most part of the query to_date(:number,'j') the ‘j’ or J is the Julian Date (January 1, 4713
BC), basically this date is been used for astronomical studies.
So to_date(:number,'j') it take the number represented by number and
pretend it is a julian date, convert into a date.
If you pass 3 to number, so it will convert date to
3rd Jan 4713 BC, it means 3 is added to the Julian date.
Now to_char(to_date(:number,'j'),'jsp'), jsp = Now; take that date(to_date(:number,'j')) and spell the julian number it represents
Limitation & workaround
There is a limitation while using Julian dates ,It
ranges from 1 to 5373484. That’s why if you put the values after 5373484, it
will throw you an error as shown below:
ORA-01854:
julian date must be between 1 and 5373484
To cater the above problem ,create a function ,and
with little trick with j->jsp ,you
can fetch the desired result.
CREATE OR REPLACE FUNCTION
spell_number (p_number IN NUMBER)
RETURN VARCHAR2
AS
TYPE myArray IS TABLE OF VARCHAR2 (255);
l_str myArray
:= myArray ('',
'
thousand ',
'
million ',
'
billion ',
'
trillion ',
'
quadrillion ',
'
quintillion ',
'
sextillion ',
'
septillion ',
'
octillion ',
'
nonillion ',
'
decillion ',
'
undecillion ',
'
duodecillion ');
l_num
VARCHAR2 (50) DEFAULT TRUNC (p_number);
l_return
VARCHAR2 (4000);
BEGIN
FOR i IN 1 ..
l_str.COUNT
LOOP
EXIT WHEN
l_num IS NULL;
IF (SUBSTR (l_num, LENGTH (l_num) - 2, 3) <> 0)
THEN
l_return :=
TO_CHAR (TO_DATE (SUBSTR (l_num, LENGTH (l_num) - 2, 3), 'J'),
'Jsp')
|| l_str (i)
|| l_return;
END IF;
l_num := SUBSTR (l_num, 1, LENGTH (l_num) - 3);
END LOOP;
RETURN
l_return;
END;
/
SELECT
spell_number (12345678904321) FROM DUAL;
Output:
Twelve trillion Three Hundred Forty-Five
billion Six Hundred Seventy-Eight million Nine Hundred Four thousand Three
Hundred Twenty-One
Limitation
Limitation of this function is decimal
figure. This function is not catering decimal figures.
So I am writing simple program to cater
decimal figure but It ranges from 1 to 5373484. L anyways later In this article I have shared the
program which will cater decimal figures too.
CREATE OR REPLACE FUNCTION spell_number(P_NUMBER
IN NUMBER) RETURN VARCHAR IS
WORDS VARCHAR2(2000);
BEGIN
IF INSTR(P_NUMBER,'.') <> 0 THEN
SELECT INITCAP ((TO_CHAR (TO_DATE (SUBSTR (round(to_number(P_NUMBER),2), 1,
INSTR (P_NUMBER, '.', 1) - 1),
'J'
),
'JSP'
)
)
)
|| ' Saudi
Riyals '||'and
'
|| INITCAP ((TO_CHAR (TO_DATE (SUBSTR (round(to_number(P_NUMBER),2), INSTR (P_NUMBER, '.', 1) + 1),
'J'
),
'JSP'
)
)
)
|| '
Halalas' Curr into WORDS
FROM DUAL;
ELSE
SELECT INITCAP ((TO_CHAR (TO_DATE (P_NUMBER,'J'),
'JSP'
)
)
)
|| ' Saudi
Riyals and 00 Halalas'
Curr into
WORDS
FROM DUAL;
END IF;
RETURN
WORDS;
Exception when others then return '00
Saudi Riyals and 00 Halalas';
END;
/
Examples
select test_convert_num_to_words(0) from dual
//output 00
Saudi Riyals and 00 Halalas
select test_convert_num_to_words(10) from dual
//output Ten
Saudi Riyals and 00 Halalas
select test_convert_num_to_words(10.12) from dual
//output Ten
Saudi Riyals andTwelve Halalas
select test_convert_num_to_words(10.129) from dual
//output Ten
Saudi Riyals and Thirteen Halalas
This function
rounded the figure up to two decimal places
Now I am
going to share the function which will cater decimal figure as well but its
limitation will be 1 to 999999999999.
CREATE OR REPLACE FUNCTION APPS.g5ps_spell_number(in_char varchar2) RETURN varchar2 IS
--
--
*********************************************************************************
--
Function : Convert number to spell *
--
Usage : f_num_spelled('34567.88') *
--
*********************************************************************************
--
sub_char varchar2(100);
amt_with_cents number(15,2);
dollars varchar2(100);
len number(2);
c_num number(18) := to_number(in_char) ;
cents varchar2(100) := null ;
full_amt varchar2(100);
function
F_SUB_1000 (in_char varchar2 ) return varchar2 is
len
number(1) := length(in_char);
c_num
number := null ;
c1
number;
c1_char
varchar2(50);
c2
number;
c2_char
varchar2(50);
out_char
varchar2(100);
begin
c_num
:= to_number(in_char);
if c_num > 0 and
c_num < 1000 then
out_char := to_char(to_date(in_char, 'J'), 'JSP');
else
out_char := null;
end if;
return(out_char) ;
end;
function
F_SUB_1000000 (in_char varchar2 ) return varchar2 is
len
number(1) := length(in_char);
c_num
number := null ;
c1
number;
c1_char
varchar2(100);
c2
number;
c2_char
varchar2(100);
out_char
varchar2(200);
begin
c_num
:= to_number(in_char);
if (
c_num >= 1000 and
c_num < 1000000 ) then
c1 := FLOOR(c_num/1000);
c1_char := to_char(to_date(c1, 'J'), 'JSP');
c2 := c_num - (c1 * 1000);
if c2 > 0 then
c2_char := f_sub_1000(to_char(c2));
else
c2_char := null;
end if;
out_char :=
c1_char||'
THOUSAND '||c2_char ;
else
out_char :=
f_sub_1000(in_char) ;
end if;
return(out_char) ;
end;
function
F_SUB_1000000000 (in_char varchar2 ) return varchar2 is
len
number(2) := length(in_char);
c_num
number := null ;
c1
number;
c1_char
varchar2(100);
c2
number;
c2_char
varchar2(100);
out_char
varchar2(200);
begin
c_num
:= to_number(in_char);
if (
c_num >= 1000000 and
c_num < 1000000000 ) then
c1 := FLOOR(c_num/1000000);
c1_char := to_char(to_date(c1, 'J'), 'JSP');
c2 := c_num - (c1 * 1000000);
if c2 > 0 then
c2_char := f_sub_1000000 (to_char(c2));
else
c2_char := null;
end if;
out_char :=
c1_char||'
MILLION '||c2_char ;
else
out_char := f_sub_1000000 (in_char);
end if;
return(out_char) ;
end;
function
F_SUB_1000000000000 (in_char varchar2 ) return varchar2 is
len
number(2) := length(in_char);
c_num
number(18) := null ;
c1
number;
c1_char
varchar2(100);
c2
number;
c2_char
varchar2(100);
out_char
varchar2(200);
begin
c_num
:= to_number(in_char);
if (
c_num >= 1000000000 and
c_num < 1000000000000 ) then
c1 := FLOOR(c_num/1000000000);
c1_char := to_char(to_date(c1, 'J'), 'JSP');
c2 := c_num - (c1 * 1000000000);
if c2 > 0 then
c2_char := f_sub_1000000000 (to_char(c2));
else
c2_char := null;
end if;
out_char :=
c1_char||'
BILLION '||c2_char ;
else
out_char := f_sub_1000000000 (in_char);
end if;
return(out_char) ;
end;
BEGIN
if to_number(in_char) = 0 then
return ('ZERO');
end if;
amt_with_cents := to_number(in_char);
full_amt := to_char(amt_with_cents, '9999999999990.90');
if to_number(substr(full_amt, 1, instr(full_amt, '.')-1)) <> 0 then
dollars := to_char(to_number(substr(full_amt, 1, instr(full_amt, '.')-1)));
else
dollars := '0';
end if;
if to_number(substr(full_amt, instr(full_amt, '.')+1)) <> 0 then
-- cents := ' AND PAISA
'||to_char(to_date(substr(full_amt, -2, 2), 'J'), 'JSP');
cents := 'SAUDI
RIYALS AND '||to_char(to_date(substr(full_amt, -2, 2), 'J'), 'JSP')||'
HALALA(s) ONLY ';
else
cents := null;
end if;
len := length(dollars);
c_num := to_number(dollars);
if dollars = '0' then
return('ZERO '||cents);
elsif
c_num < 1000 and cents is null then
return(f_sub_1000(dollars)||' SAUDI
RIYALS ONLY'||cents);
elsif
c_num < 1000 then
return(f_sub_1000(dollars)||' '||cents);
elsif
c_num >= 1000 and
c_num < 1000000 and
cents is null then
return(f_sub_1000000(dollars)||' SAUDI
RIYALS ONLY'||cents);
elsif
c_num >= 1000 and
c_num < 1000000 then
return(f_sub_1000000(dollars)||' '||cents);
elsif
c_num >= 1000000 and
c_num < 1000000000 and
cents is null then
return(f_sub_1000000000(dollars)||' SAUDI
RIYALS ONLY'||cents);
elsif
c_num >= 1000000 and
c_num < 1000000000 then
return(f_sub_1000000000(dollars)||' '||cents);
elsif
c_num >= 1000000000 and
c_num < 1000000000000 and
cents is null then
return(f_sub_1000000000000(dollars)||' SAUDI
RIYALS ONLY'||cents);
elsif
c_num >= 1000000000 and
c_num < 1000000000000 then
return(f_sub_1000000000000(dollars)||' '||cents);
else
return('the
number is too large !');
end if;
END;
/
Your feedback/comment is quite important for improving this blog so feel free to leave your comments & suggestions
Subscribe to:
Posts (Atom)
