Oracle APPS : HRMS : Query

HRMS Query

1) How to fetch Employee Latest Grade?

SELECT paaf.full_name || ' ' || paaf.employee_number emp_name,

       paaf.person_id emp_id, pg.NAME grade

  FROM per_all_people_f paaf,

       per_all_assignments_f pa,

       per_grades pg,

       fnd_user fu

 WHERE paaf.person_id = pa.person_id

   AND paaf.effective_end_date IN (SELECT MAX (paafs.effective_end_date)

                                     FROM per_all_people_f paafs

                                    WHERE paafs.person_id = paaf.person_id)

   AND pa.effective_end_date IN (

          SELECT MAX (paaf.effective_end_date)

            FROM per_all_assignments_f paafs

           WHERE paafs.assignment_id = pa.assignment_id

             AND paafs.person_id = pa.person_id)

   AND pa.grade_id(+) = pg.grade_id

   AND pa.person_id = fu.employee_id

   AND fu.user_id = :UserId;

2) 

Oracle Apps Query : How to delete Concurrent Program through API

 begin

fnd_program.delete_program('XX_EMP_COST_ITAX','Payroll');

fnd_program.delete_executable('XX_EMP_COST_ITAX','Payroll');

end;

Oracle : SQL Query : Special Character, Substr

 

1) How to remove Special Characters

"REPLACE(REPLACE(TRIM(REGEXP_REPLACE(:FREIGHT_TERMS_CODE,'[^a-z_A-Z ]')),CHR(11),''),CHR(13),'')" 

2) How to remove last 2 character from String

 substr( xpem.period_name,1,length(xpem.period_name)-2)

3) Query to get Sunday?

select

(next_day(last_day(trunc(sysdate)),'?')-7

-next_day(trunc(sysdate,'mm')-1,'?'))/7+1

as "sundays"

from dual;

SELECT TO_CHAR(dat,'DD-MON-RRRR')

FROM

    (SELECT TRUNC(SYSDATE,'MM') + level - 1 dat FROM dual

    connect by level <= LAST_DAY(TRUNC(SYSDATE)) - TRUNC(SYSDATE,'MM') + 1)

    WHERE TO_CHAR(dat,'DY') = 'SUN'

 GROUP BY TO_CHAR(dat,'MON-RRRR')

  

/* Formatted on 2018/04/06 15:18 (Formatter Plus v4.8.8) */

SELECT     TRUNC (:p_to_dat, 'MM') + LEVEL - 1 dat,TO_CHAR (TRUNC (:p_to_dat, 'MM') + LEVEL - 1, 'DY') days,

           CASE

              WHEN TO_CHAR (TRUNC (:p_to_dat, 'MM') + LEVEL - 1, 'DY') =

                                                                    'SUN'

                 THEN 'Holiday'

           END abc

      FROM DUAL

CONNECT BY LEVEL <= LAST_DAY (TRUNC (:p_to_dat)) - TRUNC (:p_to_dat, 'MM') + 1

------------------------------------------------------End Query to get sunday--------------------