Monday 22 January 2018

PLSQL Script to Show Amount in Words in Oracle Apps R12


PLSQL Script to Show Figure Amount  in Words in Oracle Apps R12


CREATE OR REPLACE FUNCTION APPS.xx_money_words(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 xx_money_words;
/

7 comments:

arif said...

Thanks and Regards. Oracle Apps R12 & Fusion Training Videos at affordable cost.
please check oracleappstechnical.com for details.

Anonymous said...

Nice blog, I really like what you write in this blog, I also have some relevant Information about Best HR Training In Hyderabad | Hr training institute in Hyderabad! if you want more information.
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad

Anonymous said...

Nice blog, I really like what you write in this blog, I also have some relevant Information about Best HR Training In Hyderabad | Hr training institute in Hyderabad! if you want more information.
Oracle Fusion HCM Online Training

shaik shah said...

Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
Oracle Fusion HCM Online Training

Rainbow Training Institute said...

Nice post……. your article is really informative and helpful for me and other bloggers too

Spark and Scala Online Training
Spark Scala Training
Hyderabad

Rainbow Training Institute said...

Really very helpful article , Thank you for sharing

Big Data and Hadoop Online Training
Big Data Hadoop Training
Hyderabad

Anonymous said...

Thank you for sharing such detailed Blog. I am learning a lot from you. Visit my website to get best Information About Best MPSC Coaching Institute in Andheri
Best MPSC Coaching Institute in Andheri
MPSC Coaching Institute in Andheri

Post a Comment

Contact us for any Collaboration, Project Support & On Job Support Work

Name

Email *

Message *