http://appselango.blogspot.com/
FORMS TUTORIAL
http://sheikyerbouti.developpez.com/tutoforms10g/tutoforms10g.htm
FORMS TUTORIAL
http://sheikyerbouti.developpez.com/tutoforms10g/tutoforms10g.htm
Thursday, July 21, 2011
Wednesday, July 20, 2011
BLANKET AND STANDARE PURCHASE ORDER
SELECT pol.po_header_id,pol.line_num,pol.PO_LINE_ID,
rownum sl_no, prl.po_release_id,
ol.item_id item_code, pol.attribute11,
pol.item_description,pol.UNIT_MEAS_LOOKUP_CODE uom,
pll.quantity,pll.price_override, pll.need_by_date
FROM po_lines_all pol,
po_headers_all poh,
po_releases_all prl,
po_line_locations_all pll
WHERE poh.po_header_id = pol.po_header_id
AND pol.po_line_id =pll.po_line_id
AND pll.po_RELEASE_ID = prl.po_release_id(+)
AND poh.TYPE_LOOKUP_CODE = 'STANDARD'
and NVL(pll.cancel_flag,'N')='N'
and NVL(pll.closed_code,'OPEN') not in ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
UNION
SELECT pol.po_header_id,pol.line_num,pol.PO_LINE_ID,
rownum sl_no, null po_release_id, --prl.po_release_id,
pol.item_id item_code, pol.attribute11,
pol.item_description,pol.UNIT_MEAS_LOOKUP_CODE uom,
NVL(pol.quantity,0),NVL(pol.unit_price,0),
NULL need_by_date
FROM po_lines_all pol,
po_headers_all poh
WHERE poh.po_header_id = pol.po_header_id
--AND pol.po_line_id = pll.po_line_id
--AND pll.po_RELEASE_ID = prl.po_release_id(+)
AND poh.TYPE_LOOKUP_CODE = 'BLANKET'
AND :p_from_rel_num IS NULL
and NVL(pol.cancel_flag,'N')='N'
and NVL(pol.closed_code,'OPEN') not in ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
UNION
SELECT pol.po_header_id,pol.line_num,pol.PO_LINE_ID,
rownum sl_no, null po_release_id, --prl.po_release_id,
pol.item_id item_code, pol.attribute11,
pol.item_description,pol.UNIT_MEAS_LOOKUP_CODE uom,
pll.quantity, pll.price_override, pll.need_by_date
FROM po_lines_all pol,
po_headers_all poh,
po_releases_all prl,
po_line_locations_all pll
WHERE poh.po_header_id = pol.po_header_id
AND pol.po_line_id = pll.po_line_id
AND pll.po_RELEASE_ID = prl.po_release_id(+)
AND poh.TYPE_LOOKUP_CODE = 'BLANKET'
AND :p_from_rel_num IS NULL
AND pol.quantity IS NOT NULL
and NVL(pll.cancel_flag,'N')='N'
and NVL(pll.closed_code,'OPEN') not in ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
ORDER BY po_header_id,po_line_id
rownum sl_no, prl.po_release_id,
ol.item_id item_code, pol.attribute11,
pol.item_description,pol.UNIT_MEAS_LOOKUP_CODE uom,
pll.quantity,pll.price_override, pll.need_by_date
FROM po_lines_all pol,
po_headers_all poh,
po_releases_all prl,
po_line_locations_all pll
WHERE poh.po_header_id = pol.po_header_id
AND pol.po_line_id =pll.po_line_id
AND pll.po_RELEASE_ID = prl.po_release_id(+)
AND poh.TYPE_LOOKUP_CODE = 'STANDARD'
and NVL(pll.cancel_flag,'N')='N'
and NVL(pll.closed_code,'OPEN') not in ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
UNION
SELECT pol.po_header_id,pol.line_num,pol.PO_LINE_ID,
rownum sl_no, null po_release_id, --prl.po_release_id,
pol.item_id item_code, pol.attribute11,
pol.item_description,pol.UNIT_MEAS_LOOKUP_CODE uom,
NVL(pol.quantity,0),NVL(pol.unit_price,0),
NULL need_by_date
FROM po_lines_all pol,
po_headers_all poh
WHERE poh.po_header_id = pol.po_header_id
--AND pol.po_line_id = pll.po_line_id
--AND pll.po_RELEASE_ID = prl.po_release_id(+)
AND poh.TYPE_LOOKUP_CODE = 'BLANKET'
AND :p_from_rel_num IS NULL
and NVL(pol.cancel_flag,'N')='N'
and NVL(pol.closed_code,'OPEN') not in ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
UNION
SELECT pol.po_header_id,pol.line_num,pol.PO_LINE_ID,
rownum sl_no, null po_release_id, --prl.po_release_id,
pol.item_id item_code, pol.attribute11,
pol.item_description,pol.UNIT_MEAS_LOOKUP_CODE uom,
pll.quantity, pll.price_override, pll.need_by_date
FROM po_lines_all pol,
po_headers_all poh,
po_releases_all prl,
po_line_locations_all pll
WHERE poh.po_header_id = pol.po_header_id
AND pol.po_line_id = pll.po_line_id
AND pll.po_RELEASE_ID = prl.po_release_id(+)
AND poh.TYPE_LOOKUP_CODE = 'BLANKET'
AND :p_from_rel_num IS NULL
AND pol.quantity IS NOT NULL
and NVL(pll.cancel_flag,'N')='N'
and NVL(pll.closed_code,'OPEN') not in ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
ORDER BY po_header_id,po_line_id
Wednesday, July 6, 2011
Convert amount in numbers to amount in words in Oracle
International (Million )
CREATE OR REPLACE FUNCTION APPS.amt_in_words ( l_amt IN NUMBER, l_ccode VARCHAR2)
RETURN VARCHAR2
AS
words VARCHAR2(1000);
whole VARCHAR2(500) := NULL;
frac VARCHAR2(500) := NULL;
suffix1 VARCHAR2(20);
suffix2 VARCHAR2(20);
currency NUMBER (20,3);
code VARCHAR2(200);
c2 NUMBER;
i NUMBER;
p NUMBER;
flen NUMBER;
text VARCHAR2(30);
tl NUMBER;
tl1 NUMBER;
BEGIN
IF l_ccode IN ('BHD','KWD','OMR') THEN
text := TO_CHAR(l_amt,'99999999.999');
tl1 := 1000;
ELSE
text := TO_CHAR(l_amt,'99999999.99');
tl1 := 100;
END IF;
tl := LENGTH(text);
p := INSTR(text,'.',1,1);
tl := LENGTH(SUBSTR(text,p+1,tl));
currency := TRUNC(l_amt);
c2 := l_amt - currency;
c2 := c2 * tl1 ;
code :=UPPER(l_ccode);
BEGIN
SELECT DECODE (l_ccode,'USD', 'cents',
'GBP','pences',
'SAR', 'halalat',
'QAR','dirhams',
'AED', 'fils',
'DEM', 'pfennigs',
'EURO','euro cents',
'INR','paise',
'FRF', 'centimes',
'BHD','fils',
'KWD','fils',
'OMR','baizas')
INTO suffix2
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
BEGIN
SELECT DECODE (l_ccode,'USD', ' US Dollars',
'BHD',' Bahraini Dinars',
'GBP',' Pounds',
'KWD',' Kuwaiti Dinars',
'SAR', ' Saudi Riyals',
'QAR',' Qatari Riyals',
'AED', ' UAE Dirhams',
'DEM', ' German Marks',
'EURO',' Euros',
'INR',' Indian Rupees',
'FRF', ' French Franks',
'OMR',' Omani Rials',
'IRR', ' Iran Rials')
INTO suffix1
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
IF (currency + c2 = 0 ) THEN
--words :='Zero '||' and Zero '||INITCAP(suffix2)||' '||'Only';
words :='Zero ' || INITCAP(suffix1) ||' and Zero '||INITCAP(suffix2)||' '||'Only';
ELSE
words :=ap_amount_utilities_pkg.ap_convert_number(currency) ||','|| ap_amount_utilities_pkg.ap_convert_number(c2) ;
p := INSTR (words,',',1,1);
whole :=whole || SUBSTR(words,1,p-1);
frac :=frac || SUBSTR(words,p+1, LENGTH(words));
-- words := INITCAP(replace(whole,'-',' '))||' and '||
--INITCAP(frac)||' '||INITCAP(suffix2)||' '||'Only';
words := INITCAP(replace(whole,'-',' '))|| INITCAP(suffix1) ||' and '||
INITCAP(frac)||' '||INITCAP(suffix2)||' '||'Only';
END IF;
RETURN words;
EXCEPTION
WHEN OTHERS THEN
words := 'UNKNOWN CURRENCY';
RETURN words;
END amt_in_words;
/
===========================================================
Indian (Lakhs)
CREATE OR REPLACE FUNCTION APPS.spell_money (p_number IN NUMBER)
RETURN VARCHAR2
AS
TYPE myarray IS TABLE OF VARCHAR2 (255);
l_str myarray
:= myarray (' Thousand ',
' Lakh ',
' Crore ',
' Arab ',
' Kharab ',
' Shankh '
);
l_num VARCHAR2 (50) DEFAULT TRUNC (p_number);
l_return VARCHAR2 (4000);
BEGIN
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');
END IF;
l_num := SUBSTR (l_num, 1, LENGTH (l_num) - 3);
FOR i IN 1 .. l_str.COUNT
LOOP
EXIT WHEN l_num IS NULL;
IF (SUBSTR (l_num, LENGTH (l_num) - 1, 2) <> 0)
THEN
l_return :=
TO_CHAR (TO_DATE (SUBSTR (l_num, LENGTH (l_num) - 1, 2), 'J'),
'Jsp'
)
|| l_str (i)
|| l_return;
END IF;
l_num := SUBSTR (l_num, 1, LENGTH (l_num) - 2);
END LOOP;
IF TO_CHAR (p_number) LIKE '%.%'
THEN
l_num := SUBSTR (ROUND (p_number, 2), INSTR (p_number, '.') + 1);
IF (LENGTH (SUBSTR (ROUND (p_number, 2), INSTR (p_number, '.') + 1))) =
1
THEN
l_num := TO_NUMBER (TO_CHAR (l_num) || '0');
END IF;
IF l_num > 0
THEN
l_return :=
l_return
|| ' And '
|| TO_CHAR (TO_DATE (l_num, 'J'), 'Jsp')
|| ' Paise';
END IF;
END IF;
RETURN (l_return||' only');
END spell_money;
/
=================================================================
Arabic
CREATE OR REPLACE FUNCTION APPS.XX_AR_AMT_IN_WORDS (
P_inp_num IN NUMBER,
-- v_ce IN VARCHAR2 DEFAULT 'ريال يمني',
--Commented on 01-Oct-2014 remove 'ريال يمن(YR) for the arabic amount in words
v_ce IN VARCHAR2 DEFAULT NULL,
v_de IN VARCHAR2 DEFAULT 'قرش',
v_det IN NUMBER DEFAULT 0,
v_lan IN VARCHAR2 DEFAULT 'AR')
RETURN VARCHAR2
AS
-- Functions called : D3_To_Word
L_Return VARCHAR2 (1000);
-- جنيها ريال
m NUMBER := 0;
V_inp_Num NUMBER := 0;
V_inp_Int NUMBER := 0;
V_inp_dec NUMBER := 0;
V_Char VARCHAR2 (100);
V_Temp NUMBER := 0;
TYPE LionArray IS TABLE OF VARCHAR2 (500);
In_str LionArray
:= LionArray ('',
' الف', -- C125750 Updated Arabic spelling.
' مليون ',
' مليار ',
' ترليون ',
' فرليون ',
' سرسليون ',
' تسرسليون ',
' فيمتون ');
-- ENGLSH VARIBLE
eL_Return VARCHAR2 (1000);
em NUMBER := 0;
eV_inp_Num NUMBER := 0;
eV_inp_Int NUMBER := 0;
eV_inp_dec NUMBER := 0;
eV_Char VARCHAR2 (100);
eV_Temp NUMBER := 0;
TYPE eLionArray IS TABLE OF VARCHAR2 (500);
eIn_str eLionArray
:= eLionArray ('',
'Thousand ',
'Million ',
'Billion ',
'Trillion ',
'Quadrillion ',
'Quintillion ',
'Sextillion ',
'Septillion ',
'Octillion ',
'Nonillion ',
'Decillion ',
'Undecillion ',
'Duodecillion ');
-- Function
FUNCTION D3_To_Wordar (P1_number IN NUMBER)
RETURN VARCHAR2
AS
-- Pass only three digits
-- This function return word equivalent to its number
-- Accepted values are 0 to 999
L1_Return2 VARCHAR2 (500) := NULL;
n1 NUMBER := 0;
i1 NUMBER := 0;
j1 NUMBER := 0;
k1 NUMBER := 0;
V1_inp_Number NUMBER := 0;
V1_temp VARCHAR2 (50) := NULL;
TYPE OneArray IS TABLE OF VARCHAR2 (50);
TYPE TenArray IS TABLE OF VARCHAR2 (50);
TYPE hanArray IS TABLE OF VARCHAR2 (50);
OneStr OneArray
:= OneArray ('واحد',
'إثنان ',
'ثلاثة',
'أربعة',
'خمسة',
'ستة',
'سبعة',
'ثمانية',
'تسعة',
'عشرة',
'إحدىعشر',
'إثناعشر',
'ثلاثة عشر',
'أربعة عشر',
'خمسة عشر',
'ستة عشر',
'سبع عشر',
'ثمانية عشر',
'تسع عشر');
TenStr TenArray
:= TenArray ('',
'عشرون',
'ثلاثون',
'أربعون',
'خمسون',
'ستون',
'سبعون',
'ثمانون',
'تسعون');
hanStr hanArray
:= hanArray ('مائة',
'مائتان',
'ثلاثمائة',
'أربعمائة',
'خمسمائة',
'ستمائة',
'سبعمائة',
'ثمانمائة',
'تسعمائة');
BEGIN
--P_inp_num := rpad(to_char(P_inp_num),2,'0') ;
V1_inp_Number := P1_Number;
IF V1_inp_number > 999
THEN
V1_inp_number := 0;
END IF;
V1_temp := LPAD (TO_CHAR (V1_inp_number), 3, 0);
-- Find Hundredth position
n1 := TO_NUMBER (SUBSTR (V1_temp, 1, 1));
IF n1 > 0
THEN
L1_Return2 := hanStr (n1);
END IF;
i1 := TO_NUMBER (SUBSTR (V1_temp, 2, 2));
j1 := TO_NUMBER (SUBSTR (V1_temp, 2, 1));
k1 := TO_NUMBER (SUBSTR (V1_temp, 3, 1));
IF n1 > 0 AND (j1 > 0 OR k1 > 0)
THEN
L1_Return2 := L1_Return2 || ' و';
END IF;
IF i1 > 0 AND i1 < 20
THEN
L1_Return2 := L1_Return2 || OneStr (i1);
END IF;
IF j1 >= 2
THEN
IF k1 > 0
THEN
L1_Return2 := L1_Return2 || OneStr (k1) || ' و';
END IF;
L1_Return2 := L1_Return2 || TenStr (j1);
END IF;
IF V1_inp_number = 0
THEN
L1_Return2 := 'صفر';
END IF;
--if L1_Return2='ثلاثة' then Return L1_Return2; else Return L1_Return2||' جنيهاَ'; end if;-- جنيها ريال
RETURN L1_Return2;
END D3_To_Wordar;
-- another function
FUNCTION Digits3_To_Word (P_number IN NUMBER)
RETURN VARCHAR2
AS
-- Pass only three digits
-- This function return word equivalent to its number
-- Accepted values are 0 to 999
--
L_Return2 VARCHAR2 (500) := NULL;
i NUMBER := 0;
j NUMBER := 0;
k NUMBER := 0;
V_inp_Number NUMBER := 0;
V_temp VARCHAR2 (50) := NULL;
TYPE OneArray IS TABLE OF VARCHAR2 (50);
TYPE TenArray IS TABLE OF VARCHAR2 (50);
OneStr OneArray
:= OneArray ('One ',
'Two ',
'Three ',
'Four ',
'Five ',
'Six ',
'Seven ',
'Eight ',
'Nine ',
'Ten ',
'Eleven ',
'Twelve ',
'Thirteen ',
'Fourteen ',
'Fifteen ',
'Sixteen ',
'Seventeen ',
'Eighteen ',
'Nineteen ');
TenStr TenArray
:= TenArray ('',
'Twenty ',
'Thirty ',
'Forty ',
'Fifty ',
'Sixty ',
'Seventy ',
'Eighty ',
'Ninety ');
BEGIN
V_inp_Number := P_Number;
IF V_inp_number > 999
THEN
V_inp_number := 0;
END IF;
V_temp := LPAD (TO_CHAR (V_inp_number), 3, 0);
-- Find Hundredth position
i := TO_NUMBER (SUBSTR (V_temp, 1, 1));
IF i > 0
THEN
L_Return2 := OneStr (i) || 'Hundred ';
END IF;
-- Find last 2 digits
i := TO_NUMBER (SUBSTR (V_temp, 2, 2));
j := TO_NUMBER (SUBSTR (V_temp, 2, 1));
k := TO_NUMBER (SUBSTR (V_temp, 3, 1));
IF i > 0 AND i < 20
THEN
L_Return2 := L_Return2 || OneStr (i);
END IF;
IF j >= 2
THEN
L_Return2 := L_Return2 || TenStr (j);
IF k > 0
THEN
L_Return2 := L_Return2 || OneStr (k);
END IF;
END IF;
RETURN L_Return2;
END Digits3_To_Word;
BEGIN
IF v_lan = 'AR'
THEN
V_inp_Num := ABS (P_inp_Num);
V_inp_Int := TRUNC (V_inp_Num);
V_inp_Dec := V_inp_Num - V_inp_Int;
-- To abort to 100
IF V_inp_Dec > 1
THEN
V_inp_dec := V_inp_dec * 100;
V_inp_Dec := TO_NUMBER (SUBSTR (TO_CHAR (V_inp_Dec), 1, 2));
END IF;
V_char := TO_CHAR (V_inp_Int);
FOR m IN 1 .. In_Str.COUNT
LOOP
EXIT WHEN V_char IS NULL;
V_Temp := TO_NUMBER (SUBSTR (V_char, (LENGTH (V_char) - 2), 3));
IF V_temp > 0
THEN
L_Return :=
D3_To_WordAR (V_Temp) || In_str (m) || ' و' || L_Return;
END IF;
V_char := SUBSTR (V_char, 1, (LENGTH (V_Char) - 3));
END LOOP;
L_return := SUBSTR (L_return, 1, (LENGTH (L_return) - 1));
-- L_return := L_return || ' جنيها'; -- جنيها ريال
L_return := L_return;
--Commented on 01-Oct-2014 remove جنيها(Pounds) for the arabic amount in words
IF P_inp_num >= 1000 AND P_inp_num < 2000
THEN
L_return := SUBSTR (L_return, 5, LENGTH (L_return));
END IF;
-- IF P_inp_num>=2000 and P_inp_num<3000 Then L_return:='الفان'||Substr(L_return,11,Length(L_return));
IF P_inp_num >= 2000 AND P_inp_num < 3000
THEN
L_return := 'الفان' || SUBSTR (L_return, 12, LENGTH (L_return));
END IF;
IF P_inp_num >= 3000 AND P_inp_num <= 10000
THEN
L_return :=
SUBSTR (L_return, 1, (INSTR (L_return, 'آلف') - 1))
|| 'الآف'
|| SUBSTR (L_return,
(INSTR (L_return, 'آلف') + 3),
LENGTH (L_return));
-- if instr(L_return,'جنيها',1,2) >1 then L_return := end if;
END IF;
IF v_det = 1
THEN
IF V_inp_Dec > 0
THEN
-- lpad('5',2,'0')
L_return :=
L_return
|| v_ce
|| ' و'
|| TO_CHAR (RPAD (SUBSTR (V_inp_Dec, 2), 2, '0'))
|| v_de;
ELSE
L_return := L_return || ' ' || v_ce;
END IF;
ELSE
IF V_inp_Dec > 1
THEN
L_return :=
L_return
|| v_ce
|| ' و'
|| D3_To_WordAR (V_inp_Dec)
|| ' '
|| v_de;
ELSE
L_return := L_return || ' ' || v_ce;
END IF;
END IF;
IF V_inp_Int > 0
THEN
L_Return := L_Return;
ELSIF V_inp_Int = 0
THEN
L_return := '===';
END IF;
-- Return L_return;
-- return substr(L_return,1,6);
IF SUBSTR (L_return, 1, 6) = ' جنيها'
THEN
RETURN REPLACE (SUBSTR (L_return, 9), '.', '');
--|| ' فقط لا غير';
--Commented on 01-Oct-2014 remove فقط لا غي(Just do not Gail) for the arabic amount in words
ELSE
RETURN REPLACE (L_return, '.', '');
-- || ' فقط لا غير';
--Commented on 01-Oct-2014 remove فقط لا غي(Just do not Gail) for the arabic amount in words
END IF;
ELSE
IF P_inp_num < 0
THEN
eV_inp_Num := (P_inp_Num * -1);
ELSE
eV_inp_Num := P_inp_Num;
END IF;
eV_inp_Int := TRUNC (eV_inp_Num);
eV_inp_Dec := eV_inp_Num - eV_inp_Int;
IF eV_inp_Dec > 0
THEN
eV_inp_dec := eV_inp_dec * 100;
eV_inp_Dec := TO_NUMBER (SUBSTR (TO_CHAR (eV_inp_Dec), 1, 2));
END IF;
eV_char := TO_CHAR (eV_inp_Int);
FOR em IN 1 .. eIn_Str.COUNT
LOOP
EXIT WHEN eV_char IS NULL;
eV_Temp := TO_NUMBER (SUBSTR (eV_char, (LENGTH (eV_char) - 2), 3));
IF eV_temp > 0
THEN
eL_Return :=
Digits3_To_Word (eV_Temp) || eIn_str (em) || eL_Return;
END IF;
eV_char := SUBSTR (V_char, 1, (LENGTH (eV_Char) - 3));
END LOOP;
IF eV_inp_Int > 0
THEN
eL_Return := v_ce || ' ' || eL_Return || ' and ';
ELSIF eV_inp_Int = 0
THEN
eL_return := v_ce || ' Zero and ';
END IF;
-- Concatenate the word for decimal digits
IF v_det = 1
THEN
IF eV_inp_Dec > 0
THEN
eL_return :=
eL_return || Digits3_To_Word (eV_inp_Dec) || ' ' || v_de;
ELSIF eV_inp_Dec = 0
THEN
eL_Return := eL_return || ' Zero ' || v_de;
END IF;
ELSE
IF eV_inp_Dec > 0
THEN
eL_return := eL_return || TO_CHAR (eV_inp_Dec) || ' ' || v_de;
ELSIF eV_inp_Dec = 0
THEN
eL_Return := eL_return || ' Zero ' || v_de;
END IF;
END IF;
RETURN eL_return;
END IF;
END XX_AR_AMT_IN_WORDS;
/
select to_char(to_date(10,'J'), 'JSP') from dual
CREATE OR REPLACE FUNCTION APPS.amt_in_words ( l_amt IN NUMBER, l_ccode VARCHAR2)
RETURN VARCHAR2
AS
words VARCHAR2(1000);
whole VARCHAR2(500) := NULL;
frac VARCHAR2(500) := NULL;
suffix1 VARCHAR2(20);
suffix2 VARCHAR2(20);
currency NUMBER (20,3);
code VARCHAR2(200);
c2 NUMBER;
i NUMBER;
p NUMBER;
flen NUMBER;
text VARCHAR2(30);
tl NUMBER;
tl1 NUMBER;
BEGIN
IF l_ccode IN ('BHD','KWD','OMR') THEN
text := TO_CHAR(l_amt,'99999999.999');
tl1 := 1000;
ELSE
text := TO_CHAR(l_amt,'99999999.99');
tl1 := 100;
END IF;
tl := LENGTH(text);
p := INSTR(text,'.',1,1);
tl := LENGTH(SUBSTR(text,p+1,tl));
currency := TRUNC(l_amt);
c2 := l_amt - currency;
c2 := c2 * tl1 ;
code :=UPPER(l_ccode);
BEGIN
SELECT DECODE (l_ccode,'USD', 'cents',
'GBP','pences',
'SAR', 'halalat',
'QAR','dirhams',
'AED', 'fils',
'DEM', 'pfennigs',
'EURO','euro cents',
'INR','paise',
'FRF', 'centimes',
'BHD','fils',
'KWD','fils',
'OMR','baizas')
INTO suffix2
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
BEGIN
SELECT DECODE (l_ccode,'USD', ' US Dollars',
'BHD',' Bahraini Dinars',
'GBP',' Pounds',
'KWD',' Kuwaiti Dinars',
'SAR', ' Saudi Riyals',
'QAR',' Qatari Riyals',
'AED', ' UAE Dirhams',
'DEM', ' German Marks',
'EURO',' Euros',
'INR',' Indian Rupees',
'FRF', ' French Franks',
'OMR',' Omani Rials',
'IRR', ' Iran Rials')
INTO suffix1
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
IF (currency + c2 = 0 ) THEN
--words :='Zero '||' and Zero '||INITCAP(suffix2)||' '||'Only';
words :='Zero ' || INITCAP(suffix1) ||' and Zero '||INITCAP(suffix2)||' '||'Only';
ELSE
words :=ap_amount_utilities_pkg.ap_convert_number(currency) ||','|| ap_amount_utilities_pkg.ap_convert_number(c2) ;
p := INSTR (words,',',1,1);
whole :=whole || SUBSTR(words,1,p-1);
frac :=frac || SUBSTR(words,p+1, LENGTH(words));
-- words := INITCAP(replace(whole,'-',' '))||' and '||
--INITCAP(frac)||' '||INITCAP(suffix2)||' '||'Only';
words := INITCAP(replace(whole,'-',' '))|| INITCAP(suffix1) ||' and '||
INITCAP(frac)||' '||INITCAP(suffix2)||' '||'Only';
END IF;
RETURN words;
EXCEPTION
WHEN OTHERS THEN
words := 'UNKNOWN CURRENCY';
RETURN words;
END amt_in_words;
/
===========================================================
Indian (Lakhs)
CREATE OR REPLACE FUNCTION APPS.spell_money (p_number IN NUMBER)
RETURN VARCHAR2
AS
TYPE myarray IS TABLE OF VARCHAR2 (255);
l_str myarray
:= myarray (' Thousand ',
' Lakh ',
' Crore ',
' Arab ',
' Kharab ',
' Shankh '
);
l_num VARCHAR2 (50) DEFAULT TRUNC (p_number);
l_return VARCHAR2 (4000);
BEGIN
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');
END IF;
l_num := SUBSTR (l_num, 1, LENGTH (l_num) - 3);
FOR i IN 1 .. l_str.COUNT
LOOP
EXIT WHEN l_num IS NULL;
IF (SUBSTR (l_num, LENGTH (l_num) - 1, 2) <> 0)
THEN
l_return :=
TO_CHAR (TO_DATE (SUBSTR (l_num, LENGTH (l_num) - 1, 2), 'J'),
'Jsp'
)
|| l_str (i)
|| l_return;
END IF;
l_num := SUBSTR (l_num, 1, LENGTH (l_num) - 2);
END LOOP;
IF TO_CHAR (p_number) LIKE '%.%'
THEN
l_num := SUBSTR (ROUND (p_number, 2), INSTR (p_number, '.') + 1);
IF (LENGTH (SUBSTR (ROUND (p_number, 2), INSTR (p_number, '.') + 1))) =
1
THEN
l_num := TO_NUMBER (TO_CHAR (l_num) || '0');
END IF;
IF l_num > 0
THEN
l_return :=
l_return
|| ' And '
|| TO_CHAR (TO_DATE (l_num, 'J'), 'Jsp')
|| ' Paise';
END IF;
END IF;
RETURN (l_return||' only');
END spell_money;
/
=================================================================
Arabic
CREATE OR REPLACE FUNCTION APPS.XX_AR_AMT_IN_WORDS (
P_inp_num IN NUMBER,
-- v_ce IN VARCHAR2 DEFAULT 'ريال يمني',
--Commented on 01-Oct-2014 remove 'ريال يمن(YR) for the arabic amount in words
v_ce IN VARCHAR2 DEFAULT NULL,
v_de IN VARCHAR2 DEFAULT 'قرش',
v_det IN NUMBER DEFAULT 0,
v_lan IN VARCHAR2 DEFAULT 'AR')
RETURN VARCHAR2
AS
-- Functions called : D3_To_Word
L_Return VARCHAR2 (1000);
-- جنيها ريال
m NUMBER := 0;
V_inp_Num NUMBER := 0;
V_inp_Int NUMBER := 0;
V_inp_dec NUMBER := 0;
V_Char VARCHAR2 (100);
V_Temp NUMBER := 0;
TYPE LionArray IS TABLE OF VARCHAR2 (500);
In_str LionArray
:= LionArray ('',
' الف', -- C125750 Updated Arabic spelling.
' مليون ',
' مليار ',
' ترليون ',
' فرليون ',
' سرسليون ',
' تسرسليون ',
' فيمتون ');
-- ENGLSH VARIBLE
eL_Return VARCHAR2 (1000);
em NUMBER := 0;
eV_inp_Num NUMBER := 0;
eV_inp_Int NUMBER := 0;
eV_inp_dec NUMBER := 0;
eV_Char VARCHAR2 (100);
eV_Temp NUMBER := 0;
TYPE eLionArray IS TABLE OF VARCHAR2 (500);
eIn_str eLionArray
:= eLionArray ('',
'Thousand ',
'Million ',
'Billion ',
'Trillion ',
'Quadrillion ',
'Quintillion ',
'Sextillion ',
'Septillion ',
'Octillion ',
'Nonillion ',
'Decillion ',
'Undecillion ',
'Duodecillion ');
-- Function
FUNCTION D3_To_Wordar (P1_number IN NUMBER)
RETURN VARCHAR2
AS
-- Pass only three digits
-- This function return word equivalent to its number
-- Accepted values are 0 to 999
L1_Return2 VARCHAR2 (500) := NULL;
n1 NUMBER := 0;
i1 NUMBER := 0;
j1 NUMBER := 0;
k1 NUMBER := 0;
V1_inp_Number NUMBER := 0;
V1_temp VARCHAR2 (50) := NULL;
TYPE OneArray IS TABLE OF VARCHAR2 (50);
TYPE TenArray IS TABLE OF VARCHAR2 (50);
TYPE hanArray IS TABLE OF VARCHAR2 (50);
OneStr OneArray
:= OneArray ('واحد',
'إثنان ',
'ثلاثة',
'أربعة',
'خمسة',
'ستة',
'سبعة',
'ثمانية',
'تسعة',
'عشرة',
'إحدىعشر',
'إثناعشر',
'ثلاثة عشر',
'أربعة عشر',
'خمسة عشر',
'ستة عشر',
'سبع عشر',
'ثمانية عشر',
'تسع عشر');
TenStr TenArray
:= TenArray ('',
'عشرون',
'ثلاثون',
'أربعون',
'خمسون',
'ستون',
'سبعون',
'ثمانون',
'تسعون');
hanStr hanArray
:= hanArray ('مائة',
'مائتان',
'ثلاثمائة',
'أربعمائة',
'خمسمائة',
'ستمائة',
'سبعمائة',
'ثمانمائة',
'تسعمائة');
BEGIN
--P_inp_num := rpad(to_char(P_inp_num),2,'0') ;
V1_inp_Number := P1_Number;
IF V1_inp_number > 999
THEN
V1_inp_number := 0;
END IF;
V1_temp := LPAD (TO_CHAR (V1_inp_number), 3, 0);
-- Find Hundredth position
n1 := TO_NUMBER (SUBSTR (V1_temp, 1, 1));
IF n1 > 0
THEN
L1_Return2 := hanStr (n1);
END IF;
i1 := TO_NUMBER (SUBSTR (V1_temp, 2, 2));
j1 := TO_NUMBER (SUBSTR (V1_temp, 2, 1));
k1 := TO_NUMBER (SUBSTR (V1_temp, 3, 1));
IF n1 > 0 AND (j1 > 0 OR k1 > 0)
THEN
L1_Return2 := L1_Return2 || ' و';
END IF;
IF i1 > 0 AND i1 < 20
THEN
L1_Return2 := L1_Return2 || OneStr (i1);
END IF;
IF j1 >= 2
THEN
IF k1 > 0
THEN
L1_Return2 := L1_Return2 || OneStr (k1) || ' و';
END IF;
L1_Return2 := L1_Return2 || TenStr (j1);
END IF;
IF V1_inp_number = 0
THEN
L1_Return2 := 'صفر';
END IF;
--if L1_Return2='ثلاثة' then Return L1_Return2; else Return L1_Return2||' جنيهاَ'; end if;-- جنيها ريال
RETURN L1_Return2;
END D3_To_Wordar;
-- another function
FUNCTION Digits3_To_Word (P_number IN NUMBER)
RETURN VARCHAR2
AS
-- Pass only three digits
-- This function return word equivalent to its number
-- Accepted values are 0 to 999
--
L_Return2 VARCHAR2 (500) := NULL;
i NUMBER := 0;
j NUMBER := 0;
k NUMBER := 0;
V_inp_Number NUMBER := 0;
V_temp VARCHAR2 (50) := NULL;
TYPE OneArray IS TABLE OF VARCHAR2 (50);
TYPE TenArray IS TABLE OF VARCHAR2 (50);
OneStr OneArray
:= OneArray ('One ',
'Two ',
'Three ',
'Four ',
'Five ',
'Six ',
'Seven ',
'Eight ',
'Nine ',
'Ten ',
'Eleven ',
'Twelve ',
'Thirteen ',
'Fourteen ',
'Fifteen ',
'Sixteen ',
'Seventeen ',
'Eighteen ',
'Nineteen ');
TenStr TenArray
:= TenArray ('',
'Twenty ',
'Thirty ',
'Forty ',
'Fifty ',
'Sixty ',
'Seventy ',
'Eighty ',
'Ninety ');
BEGIN
V_inp_Number := P_Number;
IF V_inp_number > 999
THEN
V_inp_number := 0;
END IF;
V_temp := LPAD (TO_CHAR (V_inp_number), 3, 0);
-- Find Hundredth position
i := TO_NUMBER (SUBSTR (V_temp, 1, 1));
IF i > 0
THEN
L_Return2 := OneStr (i) || 'Hundred ';
END IF;
-- Find last 2 digits
i := TO_NUMBER (SUBSTR (V_temp, 2, 2));
j := TO_NUMBER (SUBSTR (V_temp, 2, 1));
k := TO_NUMBER (SUBSTR (V_temp, 3, 1));
IF i > 0 AND i < 20
THEN
L_Return2 := L_Return2 || OneStr (i);
END IF;
IF j >= 2
THEN
L_Return2 := L_Return2 || TenStr (j);
IF k > 0
THEN
L_Return2 := L_Return2 || OneStr (k);
END IF;
END IF;
RETURN L_Return2;
END Digits3_To_Word;
BEGIN
IF v_lan = 'AR'
THEN
V_inp_Num := ABS (P_inp_Num);
V_inp_Int := TRUNC (V_inp_Num);
V_inp_Dec := V_inp_Num - V_inp_Int;
-- To abort to 100
IF V_inp_Dec > 1
THEN
V_inp_dec := V_inp_dec * 100;
V_inp_Dec := TO_NUMBER (SUBSTR (TO_CHAR (V_inp_Dec), 1, 2));
END IF;
V_char := TO_CHAR (V_inp_Int);
FOR m IN 1 .. In_Str.COUNT
LOOP
EXIT WHEN V_char IS NULL;
V_Temp := TO_NUMBER (SUBSTR (V_char, (LENGTH (V_char) - 2), 3));
IF V_temp > 0
THEN
L_Return :=
D3_To_WordAR (V_Temp) || In_str (m) || ' و' || L_Return;
END IF;
V_char := SUBSTR (V_char, 1, (LENGTH (V_Char) - 3));
END LOOP;
L_return := SUBSTR (L_return, 1, (LENGTH (L_return) - 1));
-- L_return := L_return || ' جنيها'; -- جنيها ريال
L_return := L_return;
--Commented on 01-Oct-2014 remove جنيها(Pounds) for the arabic amount in words
IF P_inp_num >= 1000 AND P_inp_num < 2000
THEN
L_return := SUBSTR (L_return, 5, LENGTH (L_return));
END IF;
-- IF P_inp_num>=2000 and P_inp_num<3000 Then L_return:='الفان'||Substr(L_return,11,Length(L_return));
IF P_inp_num >= 2000 AND P_inp_num < 3000
THEN
L_return := 'الفان' || SUBSTR (L_return, 12, LENGTH (L_return));
END IF;
IF P_inp_num >= 3000 AND P_inp_num <= 10000
THEN
L_return :=
SUBSTR (L_return, 1, (INSTR (L_return, 'آلف') - 1))
|| 'الآف'
|| SUBSTR (L_return,
(INSTR (L_return, 'آلف') + 3),
LENGTH (L_return));
-- if instr(L_return,'جنيها',1,2) >1 then L_return := end if;
END IF;
IF v_det = 1
THEN
IF V_inp_Dec > 0
THEN
-- lpad('5',2,'0')
L_return :=
L_return
|| v_ce
|| ' و'
|| TO_CHAR (RPAD (SUBSTR (V_inp_Dec, 2), 2, '0'))
|| v_de;
ELSE
L_return := L_return || ' ' || v_ce;
END IF;
ELSE
IF V_inp_Dec > 1
THEN
L_return :=
L_return
|| v_ce
|| ' و'
|| D3_To_WordAR (V_inp_Dec)
|| ' '
|| v_de;
ELSE
L_return := L_return || ' ' || v_ce;
END IF;
END IF;
IF V_inp_Int > 0
THEN
L_Return := L_Return;
ELSIF V_inp_Int = 0
THEN
L_return := '===';
END IF;
-- Return L_return;
-- return substr(L_return,1,6);
IF SUBSTR (L_return, 1, 6) = ' جنيها'
THEN
RETURN REPLACE (SUBSTR (L_return, 9), '.', '');
--|| ' فقط لا غير';
--Commented on 01-Oct-2014 remove فقط لا غي(Just do not Gail) for the arabic amount in words
ELSE
RETURN REPLACE (L_return, '.', '');
-- || ' فقط لا غير';
--Commented on 01-Oct-2014 remove فقط لا غي(Just do not Gail) for the arabic amount in words
END IF;
ELSE
IF P_inp_num < 0
THEN
eV_inp_Num := (P_inp_Num * -1);
ELSE
eV_inp_Num := P_inp_Num;
END IF;
eV_inp_Int := TRUNC (eV_inp_Num);
eV_inp_Dec := eV_inp_Num - eV_inp_Int;
IF eV_inp_Dec > 0
THEN
eV_inp_dec := eV_inp_dec * 100;
eV_inp_Dec := TO_NUMBER (SUBSTR (TO_CHAR (eV_inp_Dec), 1, 2));
END IF;
eV_char := TO_CHAR (eV_inp_Int);
FOR em IN 1 .. eIn_Str.COUNT
LOOP
EXIT WHEN eV_char IS NULL;
eV_Temp := TO_NUMBER (SUBSTR (eV_char, (LENGTH (eV_char) - 2), 3));
IF eV_temp > 0
THEN
eL_Return :=
Digits3_To_Word (eV_Temp) || eIn_str (em) || eL_Return;
END IF;
eV_char := SUBSTR (V_char, 1, (LENGTH (eV_Char) - 3));
END LOOP;
IF eV_inp_Int > 0
THEN
eL_Return := v_ce || ' ' || eL_Return || ' and ';
ELSIF eV_inp_Int = 0
THEN
eL_return := v_ce || ' Zero and ';
END IF;
-- Concatenate the word for decimal digits
IF v_det = 1
THEN
IF eV_inp_Dec > 0
THEN
eL_return :=
eL_return || Digits3_To_Word (eV_inp_Dec) || ' ' || v_de;
ELSIF eV_inp_Dec = 0
THEN
eL_Return := eL_return || ' Zero ' || v_de;
END IF;
ELSE
IF eV_inp_Dec > 0
THEN
eL_return := eL_return || TO_CHAR (eV_inp_Dec) || ' ' || v_de;
ELSIF eV_inp_Dec = 0
THEN
eL_Return := eL_return || ' Zero ' || v_de;
END IF;
END IF;
RETURN eL_return;
END IF;
END XX_AR_AMT_IN_WORDS;
/
select to_char(to_date(10,'J'), 'JSP') from dual
Subscribe to:
Posts (Atom)