Number To words in function(Indian currency) and other Currencies

CREATE OR REPLACE FUNCTION Xx_Num_Convert_Words(no IN NUMBER,Curr IN CHAR,mincurr IN CHAR) RETURN CHAR AUTHID CURRENT_USER IS word VARCHAR2(300); no1 VARCHAR2(30); no2 VARCHAR2(30); BEGIN no1 := TO_CHAR(ROUND(no,2)); no2 := TO_CHAR(TRUNC(ROUND(no,2))); SELECT DECODE(GREATEST(no,1),no,(DECODE(SIGN(no-TRUNC(no)),0,DECODE(SIGN(LENGTH(no1)-6),-1,Curr||' '||TO_CHAR(TO_DATE(no1,'J'),'JSP'), DECODE(SIGN(LENGTH(no1)-8),-1,Curr||' '||TO_CHAR(TO_DATE(SUBSTR(no1,-1*LENGTH(no1),LENGTH(no1)-5),'J'),'JSP')|| ' LAKHS '||DECODE(SIGN(SUBSTR(no1,-5)),1,TO_CHAR(TO_DATE(SUBSTR(no1,-5),'J'),'JSP')), Curr||' '||TO_CHAR(TO_DATE(SUBSTR(no1,-1*LENGTH(no1),LENGTH(no1)-7),'J'),'JSP')||' CRORES '|| DECODE(SIGN(SUBSTR(no1,-7,2)),1,TO_CHAR(TO_DATE(SUBSTR(no1,-7,2),'J'),'JSP')||' LAKHS ')|| DECODE(SIGN(SUBSTR(no1,-5)),1,TO_CHAR(TO_DATE(SUBSTR(no1,-5),'J'),'JSP')))), /*-------------*/ DECODE(SIGN(LENGTH(no2)-6),-1,Curr||' '||TO_CHAR(TO_DATE(TRUNC(no2),'J'),'JSP'),DECODE(SIGN(LENGTH(no2)-8),-1, Curr||' '||TO_CHAR(TO_DATE(SUBSTR(no2,-1*LENGTH(no2),LENGTH(no2)-5),'J'),'JSP')||' LAKHS '|| DECODE(SIGN(SUBSTR(no2,-5)),1,TO_CHAR(TO_DATE(SUBSTR(no2,-5),'J'),'JSP')), Curr||' '||TO_CHAR(TO_DATE(SUBSTR(no2,-1*LENGTH(no2),LENGTH(no2)-7),'J'),'JSP')||' CRORES '|| DECODE(SIGN(SUBSTR(no2,-7,2)),1,TO_CHAR(TO_DATE(SUBSTR(no2,-7,2),'J'),'JSP')||' LAKHS ')|| DECODE(SIGN(SUBSTR(no2,-5)),1,TO_CHAR(TO_DATE(SUBSTR(no2,-5),'J'),'JSP')))) ||' and '||TO_CHAR(TO_DATE(DECODE(LENGTH(SUBSTR (no1,INSTR(no1,'.')+1,2) ), 1, (SUBSTR(no1,INSTR(no1,'.')+1,2)*10),2, (SUBSTR(no1,INSTR(no1,'.')+1,2) )),'J'),'JSP')||' '||mincurr)||' Only ') ,(TO_CHAR(TO_DATE(DECODE(LENGTH(SUBSTR (no1,INSTR(no1,'.')+1,2) ), 1, (SUBSTR(no1,INSTR(no1,'.')+1,2)*10),2, (SUBSTR(no1,INSTR(no1,'.')+1,2) )),'J'),'JSP'))||' '||mincurr||' Only ') INTO word FROM dual; RETURN (INITCAP(word)); END; To Run query: ============== select xx_num_convert_words(:CF_QTYUSPTXAMT,'','') from dual;


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

Below Code is for all currencies

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

calendar in Oracle-sqlplus

SELECT SUBSTR(LPAD( Month, 20-(20-LENGTH(month))/2 ),1,20) Month,
"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(DT,'fmMonthfm YYYY') month, TO_CHAR(dt+1,'iw') week,
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sun",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mon",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Tue",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Wed",
MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) "Thu",
MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) "Fri",
MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sat"
FROM ( SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt FROM all_objects
WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y'))
GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), TO_CHAR( dt+1, 'iw' ))
ORDER BY TO_DATE( month, 'Month YYYY' ), TO_NUMBER(week);

SRW Example

FUNCTION CREATETAB RETURN BOOLEAN IS
BEGIN
SRW.DO_SQL('CREATE TABLE CHECK (EMPNO NUMBER NOT NULL
            PRIMARY KEY, SAL NUMBER (10,2)) PCTFREE 5
            PCTUSED 75');
RETURN(TRUE);
EXCEPTION
WHEN SRW.DO_SQL_FAILURE THEN
 SRW.MESSAGE(100, 'ERROR WHILE CREATING CHECK TABLE.');
 SRW.MESSAGE(50, 'REPORT WAS STOPPED BEFORE THE RUNTIME
 PARAMETER FORM.');
 RAISE SRW.PROGRAM_ABORT;
END;
/* Suppose you want to create a "table of contents" by getting the
** first character of a column's value, and page number on which its
** field fires to print.  Assume that you want to put the "table of contents"
** into a table named SHIP.  You could write the following construct:
*/

DECLARE
  PAGE_NO      NUMBER;
  PAGE_FOR     INDEX NUMBER;
  SORT_CHAR    CHAR(1);
  CMD_LINE     CHAR(200);
BEGIN
  SORT_CHAR := :SORT_NAME ;
  IF :CALLED = 'Y' THEN
    SRW.GET_PAGE_NUM(PAGE_FOR_INDEX);
    SRW.USER_EXIT('RWECOP PAGE_FOR_INDEX
                   P_START_PAGENO');
    SRW.MESSAGE(2,TO_CHAR(:P_START_PAGENO));
  END IF;
  SRW.GET_PAGE_NUM(PAGE_NO);
  CMD_LINE := 'INSERT INTO SHIP VALUES
       ('''||SORT_CHAR||''','||TO_CHAR(PAGE_NO)||')';
  SRW.MESSAGE(2,CMD_LINE);
  SRW.DO_SQL(CMD_LINE);
  COMMIT;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
       NULL;
  WHEN SRW.DO_SQL_FAILURE THEN
       SRW.MESSAGE(1,'FAILED TO INSERT ROW INTO SHIP
TABLE');
  WHEN OTHERS THEN
       COMMIT;
END;

Link for operating unit,inv_org & warehouse code

SELECT hou.NAME operating_unit_name,houn.internal_address_line,

         hou.short_code,

         hou.organization_id operating_unit_id,

         hou.set_of_books_id,

         hou.business_group_id,

         ood.organization_name inventory_organization_name,

         ood.organization_code Inv_organization_code,

         ood.organization_id Inv_organization_id,

         ood.chart_of_accounts_id,

         msci.secondary_inventory_name whse_code

    FROM hr_operating_units hou, org_organization_definitions ood,hr_all_organization_units houn,mtl_secondary_inventories msci

   WHERE 1 = 1 AND hou.organization_id = ood.operating_unit

   AND hou.organization_id=houn.organization_id

   AND ood.organization_id=msci.organization_id

   --AND ood.organization_code='WPO'

   AND houn.internal_address_line=NVL(:P_OPERRATING_UNIT, houn.internal_address_line) --'CCUS'

   AND ood.organization_code= NVL(:P_INVENTORY_ORG_CODE,ood.organization_code)--'WPO'

   AND msci.secondary_inventory_name=NVL(:P_WAREHOUSE_CODE,msci.secondary_inventory_name)--'WPFG'

ORDER BY hou.organization_id ASC

To get concurrent programs and value set details to compare P23 instance and P26 Instance

SELECT   SUBSTR(descriptive_flexfield_name, 7) end_user_column_name,

         fdf.description,

         enabled_flag,

         DEFAULT_VALUE,

         required_flag,

         display_flag,

         form_left_prompt,

         srw_param token,

         flex_value_set_name

--  fdf.creation_date,

--  fdf.last_updated_by,

-- fdf.last_update_date,

-- fdf.flex_value_set_id

FROM     fnd_descr_flex_col_usage_vl fdf,

         fnd_flex_value_sets ffv

WHERE    (application_id = 20003)

AND      fdf.flex_value_set_id = ffv.flex_value_set_id

AND      (descriptive_flexfield_name IN ('$SRS$.SH001801', '$SRS$.FM00501'))

ORDER BY descriptive_flexfield_name;

1) Enter your ALL Concurrent PROGRAM short names IN above QUERY "IN" clause ("here $SRS$." IS MANDATORY )

2) CONVERT output TO csv FILE. Get output FROM P23 AND P26

3) Compare above two files USING "ExamDiff" tool (OR you can USE ANY other tool which compare two files)