Wednesday, 24 June 2020

sample function

CREATE OR REPLACE FUNCTION APPS.GRCPCG_TWOGLPER
--   v_period_name IN VARCHAR2)
   RETURN NUMBER
IS
   v_period_cnt   NUMBER;
BEGIN
   SELECT COUNT (*)
     INTO v_period_cnt
     FROM (SELECT RPAD (
                     fat.application_name,
                       (20 - LENGTH (fat.application_name))
                     + LENGTH (fat.application_name),
                     ' ')
                     application_name,
                  gps.show_status,
                  gps.period_name
             FROM gl_period_statuses_v gps,
                  fnd_application_tl fat,
                  gl_sets_of_books gsb
            WHERE     1 = 1
                  --AND gps.period_name = v_period_name
                  AND fat.application_id = gps.application_id
                  AND gps.set_of_books_id = gsb.set_of_books_id
                  AND gps.application_id IN (0000)
                  AND gps.set_of_books_id = 0000) test
    WHERE bmstest.show_status = 'Open';

   IF v_period_cnt > 2
   THEN
      RETURN (v_period_cnt);
   ELSE
      RETURN (0);
   END IF;
END;

------------------------------------------------

GRANT ALL ON apps.GRCPCG_TWOGLPER TO XXLAAPPS

-------------------------------------------------