E-Business Suite

Hi all,

I need to pass parameters to sql from discoverer. I have implemented it many times before. However this time I am facing a problem with it.
I am using following methods;

1- Create a package with two functions (get and set)
2- Set function is setting a global data parameter in the package.
3- I call the get function from sql.
4- Register the function in discoverer administrator.
5- Create calculation and condition in discoverer desktop.

Normally this way is worked many times for me, however this time it is not setting the date parameter from workbook. I tried many ways but i couldn't find any solution. SQL is working when i set the parameter manually.

Here is the package code;

CREATE OR REPLACE PACKAGE BODY XXNTC_MIZAN_RAPOR_PKG
AS
   FUNCTION set_first_date (p1 IN DATE)
      RETURN NUMBER
   IS
   BEGIN
      p_first_date := p1;
      RETURN 1;
   END;

   FUNCTION set_last_date (p2 IN DATE)
      RETURN NUMBER
   IS
   BEGIN
      p_last_date := p2;
      RETURN 1;
   END;

   FUNCTION get_first_date
      RETURN DATE
   AS
   BEGIN
      RETURN p_first_date;
   END;

   FUNCTION get_last_date
      RETURN DATE
   AS
   BEGIN
      RETURN p_last_date;
   END;
   
    FUNCTION SET_CLIENT_INFO (P1 IN varchar2)
        return number as
    begin
        dbms_application_info.set_client_info(P1);
        return 1;
    end;              
          
END;

The sql part;

SELECT 
    GL.NAME,
    GCC.CODE_COMBINATION_ID,
    NVL(ACILIS.DONEM_BORC, 0) ACILIS_BORC,
    NVL(ACILIS.DONEM_ALACAK, 0) ACILIS_ALACAK,
    NVL(CUM_TOPLAM.DONEM_BORC, 0) DONEM_BORC,
    NVL(CUM_TOPLAM.DONEM_ALACAK, 0) DONEM_ALACAK,
    NVL(ACILIS.DONEM_BORC, 0) + NVL(CUM_TOPLAM.DONEM_BORC, 0) DONEM_SONU_BORC,
    NVL(ACILIS.DONEM_ALACAK, 0) + NVL(CUM_TOPLAM.DONEM_ALACAK, 0) DONEM_SONU_ALACAK, 
    GCC.SEGMENT1,
    GCC.SEGMENT2,
    GCC.SEGMENT3,
    GCC.SEGMENT4,
    GCC.SEGMENT5,
    GCC.SEGMENT6,
    GCC.SEGMENT7,
    GCC.SEGMENT8,
    GCC.SEGMENT1
    || '-'
    || (SELECT FV.DESCRIPTION FROM APPS.XXNTC_FLEX_VALUES FV WHERE FV.SEGMENT_NUM = 1 AND FV.FLEX_VALUE = GCC.SEGMENT1 AND FV.ID_FLEX_NUM = GL.CHART_OF_ACCOUNTS_ID) SEGMENT1_DESC,
    GCC.SEGMENT2
    || '-'
    || (SELECT FV.DESCRIPTION FROM APPS.XXNTC_FLEX_VALUES FV WHERE FV.SEGMENT_NUM = 2 AND FV.FLEX_VALUE = GCC.SEGMENT2 AND FV.ID_FLEX_NUM = GL.CHART_OF_ACCOUNTS_ID) SEGMENT2_DESC,
    GCC.SEGMENT3
    || '-'
    || (SELECT FV.DESCRIPTION FROM APPS.XXNTC_FLEX_VALUES FV WHERE FV.SEGMENT_NUM = 3 AND FV.FLEX_VALUE = GCC.SEGMENT3 AND FV.ID_FLEX_NUM = GL.CHART_OF_ACCOUNTS_ID) SEGMENT3_DESC,
    GCC.SEGMENT4
    || '-'
    || (SELECT FV.DESCRIPTION FROM APPS.XXNTC_FLEX_VALUES FV WHERE FV.SEGMENT_NUM = 4 AND FV.FLEX_VALUE = GCC.SEGMENT4 AND FV.ID_FLEX_NUM = GL.CHART_OF_ACCOUNTS_ID) SEGMENT4_DESC,
    GCC.SEGMENT5
    || '-'
    || (SELECT FV.DESCRIPTION FROM APPS.XXNTC_FLEX_VALUES FV WHERE FV.SEGMENT_NUM = 5 AND FV.FLEX_VALUE = GCC.SEGMENT5 AND FV.ID_FLEX_NUM = GL.CHART_OF_ACCOUNTS_ID)SEGMENT5_DESC,
    GCC.SEGMENT6
    || '-'
    || (SELECT FV.DESCRIPTION FROM APPS.XXNTC_FLEX_VALUES FV WHERE FV.SEGMENT_NUM = 6 AND FV.FLEX_VALUE = GCC.SEGMENT6 AND FV.ID_FLEX_NUM = GL.CHART_OF_ACCOUNTS_ID)SEGMENT6_DESC,
    GCC.SEGMENT7
    || '-'
    || (SELECT FV.DESCRIPTION FROM APPS.XXNTC_FLEX_VALUES FV WHERE FV.SEGMENT_NUM = 7 AND FV.FLEX_VALUE = GCC.SEGMENT7 AND FV.ID_FLEX_NUM = GL.CHART_OF_ACCOUNTS_ID)SEGMENT7_DESC,
    GCC.SEGMENT8
    || '-'
    || (SELECT FV.DESCRIPTION FROM APPS.XXNTC_FLEX_VALUES FV WHERE FV.SEGMENT_NUM = 8 AND FV.FLEX_VALUE = GCC.SEGMENT8 AND FV.ID_FLEX_NUM = GL.CHART_OF_ACCOUNTS_ID)SEGMENT8_DESC         
FROM
    GL_CODE_COMBINATIONS GCC,
    GL_LEDGERS GL,
    (SELECT 
        GJL.CODE_COMBINATION_ID,
        GL.NAME, 
        GL.LEDGER_ID,
        SUM(GJL.ACCOUNTED_DR) DONEM_BORC,
        SUM(GJL.ACCOUNTED_CR) DONEM_ALACAK
    FROM 
        GL_JE_LINES GJL,
        GL_JE_HEADERS GJH,
        GL_PERIODS GP,
        GL_LEDGERS GL 
    WHERE 
        1=1
        AND GJL.PERIOD_NAME = GP.PERIOD_NAME
        --AND GJL.CODE_COMBINATION_ID = 14005
        AND GJL.LEDGER_ID = GL.LEDGER_ID
        AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
        AND GJH.JE_CATEGORY = '1'
        AND GJL.STATUS = 'P'
        AND GP.PERIOD_SET_NAME = 'TAKVIM'
        AND START_DATE BETWEEN TO_DATE('01.01.'||TO_CHAR(APPS.XXNTC_MIZAN_RAPOR_PKG.get_first_date, 'RRRR')) AND APPS.XXNTC_MIZAN_RAPOR_PKG.get_first_date
        AND END_DATE BETWEEN TO_DATE('01.01.'||TO_CHAR(APPS.XXNTC_MIZAN_RAPOR_PKG.get_first_date, 'RRRR')) AND APPS.XXNTC_MIZAN_RAPOR_PKG.get_first_date
    GROUP BY GJL.CODE_COMBINATION_ID, GL.NAME, GL.LEDGER_ID) ACILIS,
    (SELECT 
        GJL.CODE_COMBINATION_ID,
        GL.NAME, 
        GL.LEDGER_ID,
        SUM(GJL.ACCOUNTED_DR) DONEM_BORC,
        SUM(GJL.ACCOUNTED_CR) DONEM_ALACAK
    FROM 
        GL_JE_LINES GJL,
        GL_JE_HEADERS GJH,
        GL_PERIODS GP,
        GL_LEDGERS GL 
    WHERE 
        1=1
        AND GJL.PERIOD_NAME = GP.PERIOD_NAME
        --AND GJL.CODE_COMBINATION_ID = 14005
        AND GJL.LEDGER_ID = GL.LEDGER_ID
        AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
        AND GJH.JE_CATEGORY NOT IN ('1')
        AND GJL.STATUS = 'P'
        AND GP.PERIOD_SET_NAME = 'TAKVIM'
        AND START_DATE BETWEEN TO_DATE('01.01.'||TO_CHAR(APPS.XXNTC_MIZAN_RAPOR_PKG.get_first_date, 'RRRR')) AND APPS.XXNTC_MIZAN_RAPOR_PKG.get_first_date
        AND END_DATE BETWEEN TO_DATE('01.01.'||TO_CHAR(APPS.XXNTC_MIZAN_RAPOR_PKG.get_first_date, 'RRRR')) AND APPS.XXNTC_MIZAN_RAPOR_PKG.get_first_date
    GROUP BY GJL.CODE_COMBINATION_ID, GL.NAME, GL.LEDGER_ID) CUM_TOPLAM
WHERE
    1=1
    AND GCC.CODE_COMBINATION_ID = ACILIS.CODE_COMBINATION_ID (+)
    AND GCC.CODE_COMBINATION_ID = CUM_TOPLAM.CODE_COMBINATION_ID (+)
    AND GL.LEDGER_ID = ACILIS.LEDGER_ID 
    AND GL.LEDGER_ID = CUM_TOPLAM.LEDGER_ID

I also tried dbms_application_info.set_client_info. This time again sql is working but desktop throws ORA-01847: day of month must be between 1 and last day of month even it between 1 and 31.

Any help will be appreciated.
Regards.
Anil.