Oracle Apps(EBS) - AR Receipt Register Query with Bank statement Header and Line Details

Below query is useful when you required  Non Misc Receipts Along with Bank Statement Header , Line Details and Activity name ( like Receipt Write off)

SELECT ACRA.RECEIPT_DATE
,( select distinct CSH.STATEMENT_NUMBER from  apps.ce_statement_reconcils_all CSRA,
                                    apps.ce_statement_lines CSL,
                                    apps.ce_statement_headers CSH 
                            where  CSRA.REFERENCE_ID=ACRHA.CASH_RECEIPT_HISTORY_ID
                                    AND CSRA.STATEMENT_LINE_ID=CSL.STATEMENT_LINE_ID
                                    AND CSL.STATEMENT_HEADER_ID=CSH.STATEMENT_HEADER_ID)  STATEMENT_NUMBER
,( select CSL.LINE_NUMBER from  apps.ce_statement_reconcils_all CSRA,
                                    apps.ce_statement_lines CSL,
                                    apps.ce_statement_headers CSH 
                            where  CSRA.REFERENCE_ID=ACRHA.CASH_RECEIPT_HISTORY_ID
                                    AND CSRA.STATEMENT_LINE_ID=CSL.STATEMENT_LINE_ID
                                    AND CSL.STATEMENT_HEADER_ID=CSH.STATEMENT_HEADER_ID)  LINE_NUMBER
,ACRA.RECEIPT_NUMBER
,DECODE(ARCAA.applied_payment_schedule_id, -1,NVL(SUBSTR(HCA_ONACC.ACCOUNT_NUMBER, INSTR(HCA_ONACC.ACCOUNT_NUMBER, '.')+1), HCA_ONACC.ACCOUNT_NUMBER), NVL(SUBSTR(HCA.ACCOUNT_NUMBER, INSTR(HCA.ACCOUNT_NUMBER, '.')+1), HCA.ACCOUNT_NUMBER)) CUSTOMER_NUMBER
,DECODE(ARCAA.applied_payment_schedule_id, -1,HP_ONACC.PARTY_NAME,HP.PARTY_NAME ) CUSTOMER_NAME
,DECODE(ARCAA.applied_payment_schedule_id, -1, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'ON_ACC'), -3,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'RCPT_WRITE_OFF'), -4,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CLAIM_INV'), -6,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_REFUND'), -8,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'REFUND'), -9,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_CHARGEBACK') ,RCTA.TRX_NUMBER) APPLIED_TO
,ARCAA.APPLY_DATE
,art.name ACTIVITY_NAME
,ARCAA.AMOUNT_APPLIED
,ACRA.AMOUNT RECEIPT_AMOUNT
,ACRA.CURRENCY_CODE TRANSACTION_CURRENCY
,FU.USER_NAME APPLIED_USER
,ACRA.CREATION_DATE
                    FROM apps.ar_receivable_applications_all ARCAA,
apps.ar_cash_receipts_all ACRA,
apps.ar_cash_receipt_history_all ACRHA,
apps.ra_customer_trx_all RCTA,
apps.hz_cust_accounts HCA,
apps.hz_parties HP,
apps.hz_cust_accounts HCA_ONACC,
apps.hz_parties HP_ONACC,
apps.fnd_user FU
,ar_receivables_trx_ALL art                         
                  WHERE ARCAA.CASH_RECEIPT_ID=ACRA.CASH_RECEIPT_ID(+)
AND ACRHA.CASH_RECEIPT_ID(+)=ACRA.CASH_RECEIPT_ID
AND ARCAA.APPLIED_CUSTOMER_TRX_ID=RCTA.CUSTOMER_TRX_ID(+)
AND RCTA.BILL_TO_CUSTOMER_ID=HCA.CUST_ACCOUNT_ID(+)
AND HCA.PARTY_ID=HP.PARTY_ID(+)
AND HCA_ONACC.CUST_ACCOUNT_ID(+)=ARCAA.ON_ACCT_CUST_ID
AND HCA_ONACC.PARTY_ID=HP_ONACC.PARTY_ID(+)
AND FU.USER_ID(+)=ARCAA.CREATED_BY
AND ARCAA.STATUS NOT IN ('UNAPP','UNID')
                            AND art.receivables_trx_id(+) = ARCAA.receivables_trx_id
                            AND ARCAA.GL_DATE BETWEEN NVL(TO_DATE(SUBSTR(:p_gl_date_from,1,10),'yyyy/mm/dd'),ARCAA.GL_DATE) AND NVL(TO_DATE(SUBSTR(:p_gl_date_to,1,10),'yyyy/mm/dd'),ARCAA.GL_DATE)
                            AND ARCAA.APPLY_DATE BETWEEN NVL(TO_DATE(SUBSTR(:p_apply_date_from,1,10),'yyyy/mm/dd'),ARCAA.APPLY_DATE) AND NVL(TO_DATE(SUBSTR(:p_apply_date_to,1,10),'yyyy/mm/dd'),ARCAA.APPLY_DATE)
                            AND(( HP.PARTY_NAME between NVL(:p_customer_name_low,HP.PARTY_NAME) AND NVL(:p_customer_name_high,HP.PARTY_NAME)) or
(HP_ONACC.PARTY_NAME between NVL(:p_customer_name_low,HP_ONACC.PARTY_NAME) AND NVL(:p_customer_name_high,HP_ONACC.PARTY_NAME))
   )
                            AND( ( HCA.ACCOUNT_NUMBER between NVL(:p_customer_number_low,HCA.ACCOUNT_NUMBER) AND NVL(:p_customer_number_high,HCA.ACCOUNT_NUMBER) or
   (HCA_ONACC.ACCOUNT_NUMBER between NVL(:p_customer_number_low,HCA_ONACC.ACCOUNT_NUMBER) AND NVL(:p_customer_number_high,HCA_ONACC.ACCOUNT_NUMBER))
  )
   )
                            AND ARCAA.ORG_ID=:p_org AND
                            ACRA.ORG_ID=:p_org AND
                            ACRHA.ORG_ID=:p_org
AND ACRA.TYPE != 'MISC'
AND ARCAA.REVERSAL_GL_DATE is NULL
and ACRHA.REVERSAL_GL_DATE is NULL
            UNION
                    SELECT ACRA.RECEIPT_DATE
                            ,NULL STATEMENT_NUMBER
                            ,NULL LINE_NUMBER
                            ,ACRA.RECEIPT_NUMBER
                            ,NVL(SUBSTR(HCA_ONACC.ACCOUNT_NUMBER, INSTR(HCA_ONACC.ACCOUNT_NUMBER, '.')+1), HCA_ONACC.ACCOUNT_NUMBER) CUSTOMER_NUMBER
                            ,HP_ONACC.PARTY_NAME CUSTOMER_NAME
                            ,DECODE(ARCAA.applied_payment_schedule_id, -1, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'ON_ACC'), -3,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'RCPT_WRITE_OFF'), -4,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CLAIM_INV'), -6,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_REFUND'), -8,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'REFUND'), -9,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_CHARGEBACK') ,null) APPLIED_TO
                            ,ARCAA.APPLY_DATE
                            ,art.name ACTIVITY_NAME
                            ,ARCAA.AMOUNT_APPLIED
                            ,ACRA.AMOUNT RECEIPT_AMOUNT
                            ,ACRA.CURRENCY_CODE TRANSACTION_CURRENCY
                            ,FU.USER_NAME APPLIED_USER
                            ,ACRA.CREATION_DATE
                    FROM apps.ar_receivable_applications_all ARCAA,
                            apps.ar_cash_receipts_all ACRA,
                            apps.ar_cash_receipt_history_all ACRHA,
                            apps.hz_cust_accounts HCA_ONACC,
                            apps.hz_parties HP_ONACC,
                            apps.fnd_user FU
                            ,ar_receivables_trx_ALL art
                    WHERE ARCAA.CASH_RECEIPT_ID=ACRA.CASH_RECEIPT_ID
AND ACRHA.CASH_RECEIPT_ID=ACRA.CASH_RECEIPT_ID
AND ARCAA.APPLIED_CUSTOMER_TRX_ID IS NULL
AND (HCA_ONACC.CUST_ACCOUNT_ID=ARCAA.ON_ACCT_CUST_ID or HCA_ONACC.CUST_ACCOUNT_ID=ACRA.PAY_FROM_CUSTOMER )
AND HCA_ONACC.PARTY_ID=HP_ONACC.PARTY_ID(+)
AND FU.USER_ID(+)=ARCAA.CREATED_BY
                            AND ARCAA.STATUS NOT IN ('UNAPP','UNID')
                            AND art.receivables_trx_id(+) = ARCAA.receivables_trx_id AND
ARCAA.ORG_ID=:p_org
AND ACRA.ORG_ID=:p_org
AND ACRHA.ORG_ID=:p_org
AND ACRA.TYPE != 'MISC'                         
                            AND ARCAA.APPLIED_PAYMENT_SCHEDULE_ID <0
                            AND ARCAA.AMOUNT_APPLIED >0
                            AND ARCAA.REVERSAL_GL_DATE IS NULL
                            AND ACRHA.REVERSAL_GL_DATE IS NULL
                            AND ARCAA.GL_DATE BETWEEN NVL(TO_DATE(SUBSTR(:p_gl_date_from,1,10),'yyyy/mm/dd'),ARCAA.GL_DATE) AND NVL(TO_DATE(SUBSTR(:p_gl_date_to,1,10),'yyyy/mm/dd'),ARCAA.GL_DATE)
                            AND ARCAA.APPLY_DATE BETWEEN NVL(TO_DATE(SUBSTR(:p_apply_date_from,1,10),'yyyy/mm/dd'),ARCAA.APPLY_DATE) AND NVL(TO_DATE(SUBSTR(:p_apply_date_to,1,10),'yyyy/mm/dd'),ARCAA.APPLY_DATE)
                            AND HP_ONACC.PARTY_NAME between NVL(:p_customer_name_low,HP_ONACC.PARTY_NAME) AND NVL(:p_customer_name_high,HP_ONACC.PARTY_NAME)
                            AND HCA_ONACC.ACCOUNT_NUMBER between NVL(:p_customer_number_low,HCA_ONACC.ACCOUNT_NUMBER) AND NVL(:p_customer_number_high,HCA_ONACC.ACCOUNT_NUMBER)
                            order by RECEIPT_NUMBER,CREATION_DATE ASC  ;

Query to get the Project Expenditures Defined for an Employee for a Specific Period

SELECT
  EXPT.EXPENDITURE_TYPE ,
  EXPI.Quantity ,
  CASE
    WHEN (EXPT.unit_of_measure='HOURS')
    THEN NVL(CDL.PROJECT_BURDENED_COST,cdl.project_raw_cost)
  END Raw_cost ,
  --EXPI.DENOM_RAW_COST
  EXPI.ACCRUED_REVENUE ,
  EXPI.expenditure_item_id ,
  EXPI.expenditure_id ,
  EXPI.EXPENDITURE_ITEM_DATE,
  PAP.GL_PERIOD_NAME GL_PERIOD,
  PAP.PERIOD_NAME PA_PERIOD,
  PAP.START_DATE Period_Start_Date,
  PAP.END_DATE Period_End_Date,
  --  (SELECT
  --DECODE(EXPT.Expenditure_type,'Straight Time',EXPI.Quantity,'Overtime 0.0X',EXPI.Quantity, 'Overtime 1.0X', EXPI.Quantity,'Overtime 1.5X',EXPI.Quantity,'Overtime 2.0X',EXPI.Quantity, 'Overtime 2.5X',EXPI.Quantity,'Premium',EXPI.quantity,0)Total_Hours,
  CASE
    WHEN EXPT.Expenditure_category='Labor'
    AND EXPT.unit_of_measure      ='HOURS'
    THEN EXPI.Quantity
    ELSE 0
  END Total_Hours,
  --  (SELECT SUM (
  CASE
    WHEN EXPT.EXPENDITURE_CATEGORY = 'Labor'
    AND EXPT.UNIT_OF_MEASURE       = 'HOURS'
    THEN
      CASE
        WHEN EXPT.EXPENDITURE_TYPE LIKE 'Overtime%'
        THEN EXPI.QUANTITY
        WHEN EXPT.EXPENDITURE_TYPE IN ('Premium','Weekend Premium-Saturday','Weekend Premium-Sunday','Unpaid OT')
        THEN EXPI.QUANTITY
        ELSE 0
      END
    ELSE 0
  END
  --)
  --  FROM PA.PA_EXPENDITURE_TYPES EXPT,
  --      PA.PA_EXPENDITURE_ITEMS_ALL EXPI1
  -- WHERE     1 = 1
  --  AND EXPI.EXPENDITURE_ID = EXPI1.EXPENDITURE_ID
  --  AND EXPI1.EXPENDITURE_TYPE = EXPT.EXPENDITURE_TYPE)
  OT_DT_Hours,
  -- (SELECT SUM (
  CASE
    WHEN EXPT.EXPENDITURE_CATEGORY = 'Labor'
    AND EXPT.UNIT_OF_MEASURE       = 'HOURS'
    THEN
      CASE
        WHEN EXPT.EXPENDITURE_TYPE LIKE 'Overtime%'
        THEN 0
        WHEN EXPT.EXPENDITURE_TYPE IN ('Premium','Weekend Premium-Saturday','Weekend Premium-Sunday','Unpaid OT')
        THEN 0
        WHEN EXPT.EXPENDITURE_TYPE NOT IN('Overtime 0.0','Overtime 1.5X','Overtime 2.0X','Overtime 1.0X','Overtime 2.5X','Premium','Weekend Premium-Saturday','Weekend Premium-Sunday','Unpaid OT','Union Vac/Supp Dues')
        THEN EXPI.QUANTITY
      END
  END
  --)
  --  FROM PA.PA_EXPENDITURE_TYPES EXPT,
  --     PA.PA_EXPENDITURE_ITEMS_ALL EXPI1
  -- WHERE     1 = 1
  --    AND EXP.EXPENDITURE_ID = EXPI1.EXPENDITURE_ID
  -- AND EXPI1.EXPENDITURE_TYPE = EXPT.EXPENDITURE_TYPE)
  Regular_Hours,
  CDL.acct_raw_cost,
  EXP.INCURRED_BY_PERSON_ID
FROM apps.PA_PERIODS_ALL pap,
  apps.PA_COST_DISTRIBUTION_LINES_ALL CDL,
  apps.PA_EXPENDITURE_ITEMS_ALL EXPI,
  apps.PA_EXPENDITURE_TYPES EXPT,
  apps.PA_EXPENDITURES_ALL EXP
WHERE CDL.PA_DATE BETWEEN PAP.START_DATE(+) AND PAP.END_DATE(+)
AND CDL.ORG_ID                 = PAP.ORG_ID(+)
AND PAP.GL_PERIOD_NAME         ='SEP-17'
AND CDL.LINE_NUM(+)            = 1
AND CDL.EXPENDITURE_ITEM_ID(+) = EXPI.EXPENDITURE_ITEM_ID
AND EXPI.EXPENDITURE_TYPE      = EXPT.EXPENDITURE_TYPE
AND EXP.EXPENDITURE_ID         = EXPI.EXPENDITURE_ID
AND EXP.INCURRED_BY_PERSON_ID IS NOT NULL

JDeveloper Installation and Setting Environment

Prerequisites 

Desktop with 1.5 GB RAM 

1.Telnet and FTP access to apps and db server 

2.Database connectivity details:

3.Apps username and password,

4.SID,

5.Host Name and port. 

6.Exact version of OA Framework on server

Get the JDeveloper software ( Ex:ZIP File  : p4141787_11i_GENERIC.zip )

Copy  into required drive ( folder ) / ( C:\)

Eg: C:\ JDEV  ( create JDEV ( name can be any one ) folder in C-drive

Extract the ZIP file name p4141787_11i_GENERIC.zip )

Right click à WinZip à Extract To Here

After extracting it generates following

JDEV---- ( user created folder )

|____ jdevbin  

|____ Jdevdoc

|____   jdevhome

Take the shortcut of  C:\JDEV\jdevbin\jdev\bin\ jdevW.exe     to desktop

Copy the  TEST.dbc file form Oracle Apps Server to JDeveloper

Source Oracle Apps path:   

\oracle\inst_TOP\apps\test2\appl\fnd\12.0.0\secure\TEST.dbc

JDeveloprer Path: C:\JDEV\jdevhome\jdev\dbc_files\secure

 

Set the Environment variables of O/S

My Computer à Advanced à Environment Variables à New à 

Variable Name : JDEV_USER_HOME

Variable Value : C:\JDEV\jdevhome\jdev

OK à OKàOK

Testing Functionality of Jdeveloper

Go to connection  à Right Click à New database connection à Next à 

Connection Name : test ( as desired )

Connection Type : Oracle ( JDBC )

Next :

User Name : apps

Password : apps

Next

Driver  : thin

Host Name : localhost ( if database is on the local system, else URL of DB server )

JDBC Port: 1521

SID       : VIS

For details see the vis.dbc located in the folder :

\\oracle\inst_TOP\apps\fnd\12.0.3\secure\apps

APPS_JDBC_URL=jdbc\:oracle\:thin\:@(DESCRIPTION\=(LOAD_BALANCE\=YES)(FAILOVER\=YES)(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=tcp)(HOST\=APPS.ora.com)(PORT\=1521)))(CONNECT_DATA\=(SID\=VIS)))

Next

Test Connection

Result: success

Next

Finish

ORA-01792 maximum number of columns in a table or view is 1000 FROM mtl_parameters

Symptoms:-

Concurrent program completed warning with   “ORA-01792 maximum number of columns in a table or view is 1000 FROM mtl_parameters” this reason.

Solution:-

SQL> alter session set "_fix_control"='17376322:OFF'; 

or at system level : 
SQL> alter system set "_fix_control"='17376322:OFF'; 

Enabling Create/View Accounting from Toolbar on Receipt Summary Form for a Custom Responsibility

How to Enable Create/View Accounting from Toolbar on Receipt Summary Form for a Custom Responsibility

Goal:-

What are the steps to enable Create/View Accounting for a user-defined responsibility?

Solution:-

Please add Subfunction "SLA: View Accounting - Lines Inquiries" and "XLA: View Accounting Lines" into your own menu as following:

Responsibility: System Administrator
Navigation : Application > Menu

   Query for the Menu value: FUN_AR_RECEIPT_PROCESSING
   Scroll down and add following lines :


         Sequence: Next Number
         Prompt: Leave blank
         Submenu: Leave blank
         Function: 'SLA: View Accounting - Lines Inquiries' from the LOV
         Description: Leave blank

       Sequence: Next Number
         Prompt: Leave blank
         Submenu: Leave blank
         Function: 'XLA: View Accounting Lines' from the LOV
         Description: Anything say "View Accounting Lines"

then save the change and retest the issue.

Workflow download and upload commands in Oracle apps

Workflow upload

WFLOAD <apps/pwd>@<connect_string> 0 Y {UPLOAD | UPGRADE | FORCE} <filepath>[<file_name.wft>]

Example:

WFLOAD apps/pwd@<connect_string> 0 Y UPLOAD $XXDOY_TOP/install/XXDOYAP.wft

Different “Upload Modes” applicable to WFLOAD:

UPGRADE	Honors both protection and customization levels of data
UPLOAD	Honors only protection level of data [No respect of Customization Level]
FORCE	Force upload regardless of protection or customization level

Workflow Download

WFLOAD <apps_user_name>/<password>@db 0 Y DOWNLOAD file_name.wft <Item_Type>

Example:

WFLOAD <apps_user_name>/<password>@db 0 Y DOWNLOAD XXDOYAP.wft

Convert the Amount in to the Word using Function in Oracle Apps EBS R12

 

Convert the Amount in to the Word using Function in Oracle Apps EBS R12.

Function:

CREATE OR REPLACE FUNCTION APPS.Get_amount_to_word(P_LC_AMOUNT IN NUMBER,P_CURRENCY_CODE IN VARCHAR2)
RETURN VARCHAR2
IS

x_tot_amount1     NUMBER            := 0;
x_tot_amount      VARCHAR2(30)      := 0;
x_amount_in_word  VARCHAR2(2000)    := 0;

BEGIN

x_tot_amount1 := p_lc_amount;

    x_tot_amount := TO_CHAR(x_tot_amount1,'999999999999.99');
   
    BEGIN
        IF NVL(x_tot_amount1,0) >= 0 THEN
            IF p_currency_code ='INR' THEN -- ## g_const_currency_bdt = 'BDT' ##--
           
                BEGIN
                    SELECT  'Rupee '||REPLACE(AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 1))||
                            DECODE(AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 2)),NULL,' Only',
                            ' and Paisa'||AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 2))||' Only'),'-',' ')
                      INTO x_amount_in_word
                      FROM DUAL;
                END;
            ELSIF p_currency_code <>'INR' THEN
           
               BEGIN
                    SELECT  REPLACE(AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 1))||
                            DECODE(AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 2)),NULL,' Only',
                            ' and Cents '||AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 2))||' Only'),'-',' ')
                      INTO x_amount_in_word
                      FROM DUAL;
                END;
--            ELSIF p_currency_code ='USD' THEN   
--           
--               BEGIN
--                    SELECT  'Dollar '||REPLACE(AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 1))||
--                            DECODE(AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 2)),NULL,' Only',
--                            ' and Cents '||AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 2))||' Only'),'-',' ')
--                      INTO x_amount_in_word
--                      FROM DUAL;
--                END;
           
            END IF;
        END IF;
    END;
   
    RETURN (x_amount_in_word);
   
    EXCEPTION
    WHEN OTHERS THEN
    FND_FILE.put_line(fnd_file.log,'--##ERROR##--In PACKAGE.function = XXBEX_ASIA_LC_ATHRZTN_PRINT_PK.xxget_amount_to_word/'||SQLERRM);
    RETURN 0;   
END Get_amount_to_word;

====================================================================

CREATE OR REPLACE FUNCTION APPS.XX_CONVERT_AMOUNT_TO_WORDS (P_AMT       IN NUMBER )                                    
                                               RETURN VARCHAR2 IS
M_MAIN_AMT_TEXT      VARCHAR2(2000) ;
M_TOP_AMT_TEXT       VARCHAR2(2000) ;
M_BOTTOM_AMT_TEXT    VARCHAR2(2000) ;
M_DECIMAL_TEXT       VARCHAR2(2000) ;
M_TOP                NUMBER(20,5) ;
M_MAIN_AMT           NUMBER(20,5) ;
M_TOP_AMT            NUMBER(20,5) ;
M_BOTTOM_AMT         NUMBER(20,5) ;
M_DECIMAL            NUMBER(20,5) ;
M_AMT                NUMBER(20,5);
M_TEXT               VARCHAR2(2000) ;
BEGIN
   M_MAIN_AMT        := NULL ;
   M_TOP_AMT_TEXT    := NULL ;
   M_BOTTOM_AMT_TEXT := NULL ;
   M_DECIMAL_TEXT    := NULL ;
  
   -- To get paise part
   M_DECIMAL    := P_AMT - TRUNC(P_AMT) ;
  
   IF M_DECIMAL >0 THEN
   M_DECIMAL := ROUND(M_DECIMAL *100);
   END IF;
  
   M_AMT        := TRUNC(P_AMT) ;          


   M_TOP        := TRUNC(M_AMT / 100000) ;
   M_MAIN_AMT   := TRUNC(M_TOP / 100);
   M_TOP_AMT    := M_TOP - M_MAIN_AMT * 100 ;
   M_BOTTOM_AMT :=  M_AMT - (M_TOP * 100000) ;

  IF M_MAIN_AMT > 0 THEN
      M_MAIN_AMT_TEXT := TO_CHAR(TO_DATE(M_MAIN_AMT,'J'),'JSP') ;
      IF M_MAIN_AMT = 1 THEN
        M_MAIN_AMT_TEXT := M_MAIN_AMT_TEXT || ' CRORE ' ;
      ELSE
        M_MAIN_AMT_TEXT := M_MAIN_AMT_TEXT || ' CRORES ' ;
      END IF ;
   END IF ;

   IF M_TOP_AMT > 0 THEN
      M_TOP_AMT_TEXT := TO_CHAR(TO_DATE(M_TOP_AMT,'J'),'JSP') ;
      IF M_TOP_AMT = 1 THEN
        M_TOP_AMT_TEXT := M_TOP_AMT_TEXT || ' LAKH ' ;
      ELSE
        M_TOP_AMT_TEXT := M_TOP_AMT_TEXT || ' LAKHS ' ;
      END IF;
   END IF ;
   IF M_BOTTOM_AMT > 0 THEN
      M_BOTTOM_AMT_TEXT := TO_CHAR(TO_DATE(M_BOTTOM_AMT,'J'),'JSP') ;
   END IF ;
   IF M_DECIMAL > 0 THEN
      IF NVL(M_BOTTOM_AMT,0) + NVL(M_TOP_AMT,0) > 0 THEN
         M_DECIMAL_TEXT := ' AND ' || TO_CHAR(TO_DATE(M_DECIMAL,'J'),'JSP') || ' Paise ' ;
      ELSE
         M_DECIMAL_TEXT :=  TO_CHAR(TO_DATE(M_DECIMAL,'J'),'JSP') ||' Paise ';
      END IF ;
        END IF ;
   M_TEXT := LOWER(M_MAIN_AMT_TEXT || M_TOP_AMT_TEXT || M_BOTTOM_AMT_TEXT || ' Rupees' || M_DECIMAL_TEXT || ' ONLY') ;
   M_TEXT := UPPER(SUBSTR(M_TEXT,1,1))|| SUBSTR(M_TEXT,2);
   M_TEXT := 'Rupees'||' '|| M_TEXT;
   RETURN (M_TEXT);

END XX_CONVERT_AMOUNT_TO_WORDS;