----------------------Package Specifican----------------------------------
CREATE OR REPLACE PACKAGE APPS.NUM_TO_WORD
AS
PROCEDURE CONA_MAIN_DUP(
ipNumber
IN NUMBER,
ipcurr Varchar2,
opText OUT VARCHAR2);
PROCEDURE CONA_INT_NUM_DUP(
ipNumber
IN NUMBER,
ipcurr varchar2,
opText OUT VARCHAR2);
PROCEDURE CONA_NUM_20_99_DUP(
pNumber IN NUMBER,
ipcurr varchar2,
pText OUT VARCHAR2);
PROCEDURE CONA_NUM_0_19_DUP(
pNumber IN NUMBER,
pText IN OUT VARCHAR2);
END NUM_TO_WORD;
/
----------------------Package Body----------------------------------
CREATE OR REPLACE PACKAGE BODY APPS.NUM_TO_WORD AS
cPackageName varchar2(100):='CONA_KCT_RS_TEXT_DUP';
-- Jayanta --- CURRENCY Convert in Word ------
PROCEDURE CONA_MAIN_DUP(
ipNumber IN NUMBER,
ipcurr varchar2,
opText OUT VARCHAR2)
IS
cProcName constant varchar2(100):='cPackageName'||'SN_P_MAIN_DUP';
vStatement varchar2(100):= ' '; -- SQL statement
vParamString varchar2(100):= ' '; -- Passed Parameter
vText VARCHAR2(200);
vLen NUMBER;
vLenDec NUMBER;
vValInt NUMBER;
vValDec NUMBER;
vTempText VARCHAR2(200);
vmaincurr varchar2(5) := null;
BEGIN
vTempText := NULL;
-- vText := 'Rs ';
If ipcurr = 'INR' then
Vtext := 'Rupees ';
elsif
ipcurr = 'USD' then
Vtext := 'US$ ';
elsif
ipcurr = 'GBP' then
Vtext := 'Pound ';
elsif
ipcurr = 'EUR' then
Vtext := 'Euro ';
elsif
ipcurr = 'BTN' then
Vtext := 'Nu ';
end if;
vValInt := TRUNC(ipNumber); -- Extract the integral part
vValDec := (ipNumber - vValInt)*100; -- Extract the decimal part
-- Call Procedure SN_P_CONV_INT_NUM_DUP for Integral Part
vmaincurr := ipcurr;
CONA_INT_NUM_DUP(vValInt,vmaincurr,vTempText);
vText := vText || vTempText;
-- Call Procedure SN_P_CONV_INT_NUM_DUP for Decimal Part
vTempText := NULL;
CONA_INT_NUM_DUP(vValDec,vmaincurr,vTempText);
-- vText := vText ||' and Paise '|| vTempText ||' only';
If ipcurr = 'INR' then
vText := vText ||' and Paise '|| vTempText ||' only';
elsif
ipcurr = 'USD' then
vText := vText ||' and Cent '|| vTempText ||' only';
elsif
ipcurr = 'GBP' then
vText := vText ||' and Penny '|| vTempText ||' only';
elsif
ipcurr = 'EUR' then
vText := vText ||' and Pence '|| vTempText ||' only';
elsif
ipcurr = 'BTN' then
vText := vText ||' and Ch '|| vTempText ||' only';
end if;
opText := vText ;
END CONA_MAIN_DUP;
PROCEDURE CONA_INT_NUM_DUP(
ipNumber IN NUMBER,
ipcurr in varchar2,
opText OUT VARCHAR2)
IS
cProcName constant varchar2(100):='cPackageName'||'SN_P_CONV_INT_NUM_DUP';
vStatement varchar2(100):= ' '; -- SQL statement
vParamString varchar2(100):= ' '; -- Passed Parameter
vText VARCHAR2(100);
pText VARCHAR2(100);
vLenInt NUMBER;
vLenDec NUMBER;
vValInt NUMBER;
vValDec NUMBER;
vTempVal NUMBER;
vTempText VARCHAR2(100);
vCroreLen NUMBER;
vintnumcurr varchar2(5) := null;
BEGIN
vTempText := NULL;
vText := NULL;
vValInt := ipNumber;
pText := NULL;
vLenInt := LENGTH(TO_CHAR(vValInt));
vLenDec := LENGTH(TO_CHAR(vValDec));
vTempVal :=0;
vCroreLen :=0;
vintnumcurr := ipcurr;
-- If range more than or equal to one crore
IF(vValInt >= 10000000) THEN
-- Crore left side
vCroreLen := LENGTH(SUBSTR(TO_CHAR(vValInt),1,(vLenInt - 7)));
CONA_INT_NUM_DUP(TO_NUMBER(SUBSTR(TO_CHAR(vValInt),1,(vLenInt - 7))),vintnumcurr,vText);
IF (vText = 'one') THEN
pText := vText || ' Crore ';
ELSE
pText := vText || ' Crores ';
END IF;
vText := NULL;
-- Crore right side
vValInt := TO_NUMBER(SUBSTR(TO_CHAR(vValInt),(vCroreLen + 1),7));
IF(vValInt > 0) THEN
CONA_INT_NUM_DUP(vValInt,vintnumcurr,vText);
END IF;
ELSE
WHILE (vLenInt >= 1) LOOP
vTempText := NULL;
/* If range between 0 to 19 */
IF (vValInt >= 0 AND vValInt <= 19) THEN
CONA_NUM_0_19_DUP(vValInt,vTempText);
vText := vText || vTempText;
EXIT;
/* If range between 20 to 99 */
ELSIF (vValInt >= 20 AND vValInt <= 99) THEN
vTempText:= NULL;
CONA_NUM_20_99_DUP(SUBSTR(TO_CHAR(vValInt),1,1),vintnumcurr,vTempText);
vText := vText || vTempText;
IF (SUBSTR(TO_CHAR(vValInt),2,1)) <> 0 THEN
vText := vText ||'-';
vTempText:= NULL;
CONA_NUM_0_19_DUP(SUBSTR(TO_CHAR(vValInt),2,1),vTempText);
vText := vText || vTempText;
END IF;
EXIT;
/* If range between 100 to 999 */
ELSIF (vValInt >= 100 AND vValInt <= 999) THEN
CONA_NUM_0_19_DUP(SUBSTR(vValInt,1,1),vTempText);
vText := vText ||vTempText||' Hundred';
vValint := TO_NUMBER(SUBSTR(TO_CHAR(vValint),2,(vLenInt-1)));
/* If range between 1000 to 9999 */
ELSIF (vValInt >= 1000 AND vValInt <= 9999) THEN
CONA_NUM_0_19_DUP(SUBSTR(TO_CHAR(vValInt),1,1),vTempText);
vText := vText||vTempText||' Thousand';
vValint := TO_NUMBER(SUBSTR(TO_CHAR(vValint),2,(vLenInt-1)));
/* If range between 10,000 to 99,999 */
ELSIF (vValInt >= 10000 AND vValInt <= 99999) THEN
/* If range between 10,000 to 19,000 */
vTempVal := TO_NUMBER(SUBSTR(TO_CHAR(vValInt),1,2));
IF (vTempVal >= 0 AND vTempVal <= 19) THEN
vTempText:= NULL;
CONA_NUM_0_19_DUP(vTempVal,vTempText);
vText := vText || vTempText||' Thousand';
vValint := TO_NUMBER(SUBSTR(TO_CHAR(vValint),3,(vLenInt-1)));
/* If range between 20,000 to 99,999 */
ELSIF (vTempVal >= 20 AND vTempVal <= 99) THEN
vTempText:= NULL;
CONA_NUM_20_99_DUP(SUBSTR(TO_CHAR(vTempVal),1,1),vintnumcurr,vTempText);
vText := vText||vTempText;
IF (SUBSTR(TO_CHAR(vTempVal),2,1)) <> 0 THEN
vText := vText ||'-';
vTempText:= NULL;
CONA_NUM_0_19_DUP(SUBSTR(TO_CHAR(vValInt),2,1),vTempText);
vText := vText || vTempText ;
END IF;
vText := vText || ' Thousand';
vValint := TO_NUMBER(SUBSTR(TO_CHAR(vValint),3,(vLenInt-1)));
END IF;
/* If range between 1,00,000 to 99,99,999 */
ELSIF (vValInt >= 100000 AND vValInt <= 999999) THEN
CONA_NUM_0_19_DUP(SUBSTR(TO_CHAR(vValInt),1,1),vTempText);
IF (vTempText = 'one') THEN
vText := vText||vTempText||' Lakh';
ELSE
vText := vText||vTempText||' Lakhs';
END IF;
vValint := TO_NUMBER(SUBSTR(TO_CHAR(vValint),2,(vLenInt-1)));
/* If range between 10,00,000 to 99,99,999 */
ELSIF (vValInt >= 1000000 AND vValInt <= 9999999) THEN
/* If range between 10,00,000 to 19,99,999 */
vTempVal := TO_NUMBER(SUBSTR(TO_CHAR(vValInt),1,2));
IF (vTempVal >= 0 AND vTempVal <= 19) THEN
vTempText:= NULL;
CONA_NUM_0_19_DUP(vTempVal,vTempText);
vText := vText || vTempText||' Lakhs';
vValint := TO_NUMBER(SUBSTR(TO_CHAR(vValint),3,(vLenInt-1)));
/* If range between 20,00,000 to 99,99,999 */
ELSIF (vTempVal >= 20 AND vTempVal <= 99) THEN
vTempText:= NULL;
CONA_NUM_20_99_DUP(SUBSTR(TO_CHAR(vTempVal),1,1),vintnumcurr,vTempText);
vText := vText||vTempText;
IF (SUBSTR(TO_CHAR(vTempVal),2,1)) <> 0 THEN
vText := vText ||'-';
vTempText:= NULL;
CONA_NUM_0_19_DUP(SUBSTR(TO_CHAR(vValInt),2,1),vTempText);
vText := vText || vTempText ;
END IF;
vText := vText || ' Lakhs';
vValint := TO_NUMBER(SUBSTR(TO_CHAR(vValint),3,(vLenInt-1)));
END IF;
END IF;
vLenInt := vLenInt -1 ;
IF ( vValInt = 0 ) THEN
EXIT;
ELSE
vText := vText || ' ';
END IF;
END LOOP;
END IF;
opText :=pText|| vText ;
END CONA_INT_NUM_DUP;
PROCEDURE CONA_NUM_20_99_DUP
(pNumber IN NUMBER,
ipcurr in varchar2,
pText OUT VARCHAR2)
IS
cProcName constant varchar2(100):='cPackageName'||'CONA_NUM_20_99_DUP';
vStatement varchar2(100):= ' '; /* SQL statement */
vParamString varchar2(100):= ' '; /* Passed Parameter */
vText VARCHAR2(100);
v2099curr varchar2(5) := null;
BEGIN
-- vText := 'Rupees Zero and Paise ';
If ipcurr = 'INR' then
vText := 'Rupees Zero and Paise ';
elsif
ipcurr = 'USD' then
vText := 'US$ Zero and Pence ';
elsif
ipcurr = 'GBP' then
vText := 'Pound Zero and Pence ';
elsif
ipcurr = 'EUR' then
vText := 'Euro Zero and Pence ';
elsif
ipcurr = 'BTN' then
vText := 'Nu Zero and Ch ';
end if;
IF (pNumber = 2) THEN
pText := 'Twenty';
ELSIF (pNumber = 3) THEN
pText := 'Thirty';
ELSIF (pNumber = 4) THEN
pText := 'Forty';
ELSIF (pNumber = 5) THEN
pText := 'Fifty';
ELSIF (pNumber = 6) THEN
pText := 'Sixty';
ELSIF (pNumber = 7) THEN
pText := 'Seventy';
ELSIF (pNumber = 8) THEN
pText := 'Eighty';
ELSIF (pNumber = 9) THEN
pText := 'Ninety';
END IF;
END CONA_NUM_20_99_DUP;
PROCEDURE CONA_NUM_0_19_DUP
(pNumber IN NUMBER,
pText IN OUT VARCHAR2)
IS
cProcName constant varchar2(100):='cPackageName'||'CONA_NUM_0_19_DUP';
vStatement varchar2(100):= ' '; /* SQL statement */
vParamString varchar2(100):= ' '; /* Passed Parameter */
vText VARCHAR2(100);
BEGIN
IF (pNumber = 0) THEN
pText := pText || ' Zero';
ELSIF (pNumber = 1) THEN
pText := pText || 'One';
ELSIF (pNumber = 2) THEN
pText := pText ||'Two';
ELSIF (pNumber = 3) THEN
pText := pText ||'Three';
ELSIF (pNumber = 4) THEN
pText := pText ||'Four';
ELSIF (pNumber = 5) THEN
pText := pText ||'Five';
ELSIF (pNumber = 6) THEN
pText :=pText || 'Six';
ELSIF (pNumber = 7) THEN
pText :=pText || 'Seven';
ELSIF (pNumber = 8) THEN
pText :=pText || 'Eight';
ELSIF (pNumber = 9) THEN
pText := pText ||'Nine';
ELSIF (pNumber = 10) THEN
pText := pText ||'Ten';
ELSIF (pNumber = 11) THEN
pText := pText ||'Eleven';
ELSIF (pNumber = 12) THEN
pText := pText ||'Twelve';
ELSIF (pNumber = 13) THEN
pText := pText ||'Thirteen';
ELSIF (pNumber = 14) THEN
pText := pText ||'Foeteen';
ELSIF (pNumber = 15) THEN
pText := pText ||'Fifteen';
ELSIF (pNumber = 16) THEN
pText := pText ||'Sixteen';
ELSIF (pNumber = 17) THEN
pText := pText ||'Seventeen';
ELSIF (pNumber = 18) THEN
pText := pText ||'Eighteen';
ELSIF (pNumber = 19) THEN
pText := pText ||'Nineteen';
END IF;
END CONA_NUM_0_19_DUP;
END NUM_TO_WORD;
/
----------------------Package Body End--------------------------------