Sql & Pl/sql

Working on Oracle Dates..

Get the last date of a month: 

SELECT LAST_DAY (TO_DATE ('08','MM')) FROM dual;

Return the last Saturday of each month for a given year 

SELECT TO_CHAR (
NEXT_DAY (
LAST_DAY (TO_DATE (&my_month,'MM' )) - 7,
TO_CHAR (TO_DATE ('29-01-1927', 'DD-MM-YYYY' ),'DAY')
),'DD.MM.YYYY') "Last Saturday in December 2013"
FROM dual;

Return the last Saturdays for the current year.

SELECT TO_CHAR (
NEXT_DAY (
LAST_DAY (
ADD_MONTHS (TRUNC(SYSDATE,'Y'),ROWNUM-1))-7,
TO_CHAR (TO_DATE('29-01-1927', 'DD-MM-YYYY'),'DAY')
), 'DD.MM.YYYY') "Last Saturdays in 2013"
FROM ALL_OBJECTS
WHERE ROWNUM <= 12

Last Saturdays in 2013
----------------------

26.01.2013

23.02.2013

30.03.2013

27.04.2013

25.05.2013

29.06.2013

27.07.2013

31.08.2013

28.09.2013

26.10.2013

30.11.2013

28.12.2013

Next Monday

SELECT TO_CHAR(NEXT_DAY(sysdate,'MON'),'DD.MM.YYYY') "Next Monday from now"

  FROM DUAL;


To Print amount in words

    SELECT TO_CHAR( TO_DATE( SUBSTR(REPLACE(l_total_payment_amt,','),1,INSTR(REPLACE(l_total_payment_amt,','),'.')-1), 'j' ), 'Jsp' ) ||' Dollars And '||           SUBSTR(REPLACE(l_total_payment_amt,','),INSTR(replace(l_total_payment_amt,','),'.')+1,length(REPLACE(l_total_payment_amt,','))) ||' Cents '          FROM dual;

Sample Result # Two thousand six hundred fifty-nine Dollars And 5 Cents

Limitation of JSP command
Can not pring value greater than 5373484

ORA-01854: julian date must be between 1 and 5373484

01854. 00000 -  "julian date must be between 1 and 5373484"

*Cause:    An invalid Julian date was entered.

*Action:   Enter a valid Julian date between 1 and 5373484.’

Work Around for JSP command  # Use the following package in oracle apps ap_amount_utilities_pkg.ap_convert_number 

Identifying duplicate records in table

select *

    from

    (select d.*

          , count(*) over

            (partition by ORGANIZATION_CODE, ITEM_NUMBER,TANDEM_LOCATION,TANDEM_ITEM) cnt

     from XXINV_ITEM_CROSS_REF d

    )

    where cnt > 1; 

How to split comma separated string and pass to IN clause of select statement

For example, assume a select statement returns the following 

'SMITH,ALLEN,WARD,JONES'

select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual

connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null;