8 Replies Latest reply: Dec 5, 2013 2:20 AM by hemu RSS

    stop apex engine detected

    hemu

      hi

      i have a below query which runs fine in sqldeveloper or at sqlplusw

      however the same query i am trying in apex(4.2) it is not showing any result.

      i debuged the report and found that all parameters are storing/and sending values as expected

      but following two line seems to be required attention

      1)NLS of database and client differs, characterset conversion needed

      2) stop apex engine detected.

       

      my query is

      SELECT * FROM
      (
        select lbrcode,substr(br_name(LBRCODE),1,20) BRANCH,
        custno,
        longname Name,
        trim(substr(prdacctid,1,8))  PRD,
        substr(prdacctid,17,8) Acno,
        LIMIT,
        to_char(tot1 ,'999999999999.99') Total_dp_allowed,
        dpdate DP_date,
        expiry_date DP_EXPdATE,
        LIMIT_EXPdATE,
        to_char(cl_bal,'9999999999999.99') cl_bal,
        to_char(avg,'9999999999999.99') Aveg_balance,
        to_char(debit,'9999999999999.99') Debit_turnover,
        to_char(credit,'9999999999999.99') Credit_turnover,
        INT,
        case when tot1<>0 then to_char(round((abs(debit)*100)/tot1,2),'9999999999.99') else to_char(0,'9999999999.99')||'dp_not_found' end as Debit_turnover_percentage,
        case when tot1<>0 then to_char(round((abs(credit)*100)/tot1,2),'9999999999.99') else to_char(0,'9999999999.99')||'DP_Not_found' end as credit_turnover_percentage,
        case when tot1<>0 then to_char(round((abs(avg)*100)/tot1,2),'9999999999.99') else to_char(0,'9999999999.99') end as  Utilisation_percentage
        from
        (
        Select
        Q1.LBRCODE,
        Q2.CUSTNO,
        Q2.LONGNAME,
        Q2.PRDACCTID,
        Q3.TOTSANCLIMIT LIMIT,
        Q3.EXPLESSGRACE LIMIT_EXPdATE,
        q7.int,
        NVL(Q5.TOTSANCLIMIT,0) ad_hoc_limit,
        (NVL(TOTALDPALLOWED,0)+NVL(Q5.TOTSANCLIMIT,0)) TOT1,
        NVL(DPDATE,'01-JAN-1900') dpdate,
        NVL(EXPIRYDATE,'01-JAN-1900') expiry_date,
      
      
        CASE WHEN trim(substr(Q2.PRDACCTID,1,8)) in ('CC','CDOD','DDPOD') THEN Q1.BALANCE4
        ELSE
        (BALANCE1+(BALANCE5-BALANCE4)) END AS CL_BAL,
        AVG,
        DEBIT,
        CREDIT
        from
        (
        SELECT * FROM
        (
        SELECT D010014.*,
        RANK() OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY cbldate DESC) AS RNK
        FROM d010014
        WHERE trim(substr(prdacctid,1,8)) in ('CC','CDOD','DDPOD')
        AND CBLDATE<=to_date(:P304_TODATE,'dd-mon-yyyy')
        )
        WHERE RNK=1
        )q1,
        (
        select d009022.* from d009022
        where acctstat<>3
        and trim(substr(prdacctid,1,8)) in ('CC','CDOD','DDPOD')
        ORDER BY LBRCODE
        )Q2,
        (
        SELECT * FROM
        (
        SELECT D009042.*,
        RANK() OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY EFFFROMDATE DESC) AS RNK
        FROM D009042 WHERE trim(substr(prdacctid,1,8)) IN ('CC','CDOD','DDPOD')
        ) WHERE RNK=1
        )Q3,
        ( 
        SELECT * FROM
        (
        SELECT D009046.*,
        RANK() OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY dpdate DESC) AS RNK
        FROM D009046 WHERE trim(substr(prdacctid,1,8)) in ('CC','CDOD','DDPOD')
        ) WHERE RNK=1
        )Q4,
        ( 
        SELECT * FROM
        (
        SELECT D009047.*,
        RANK() OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY EFFFROMDATE DESC) AS RNK
        FROM D009047 WHERE trim(substr(prdacctid,1,8)) in ('CC','CDOD','DDPOD')
        ) WHERE RNK=1
        )Q5,
        (
        select branch,PRD,
        to_char(sum(a),'9999999999999.99') product,
        TO_CHAR(SUM(A)/TO_NUMBER(TO_DATE(:P304_TODATE,'DD-MON-YYYY')-TO_DATE(:P304_FROMDATE,'DD-MON-YYYY')),'9999999999.99') AVG,
        LAST_BAL,
        LAST_CBL_DATE,
        MIN_DATE
        from
        (
        select branch,prd,bal,
        case when days<>0  then bal*days else bal end as a,
        days,LAST_BAL,LAST_CBL_DATE,MIN_DATE
        from
        (
        SELECT LBRCODE branch,PRDACCTID prd,CBLDATE c_date,
        CASE WHEN TRIM(SUBSTR(PRDACCTID,1,8)) IN ('CC','CDOD','DDPOD') THEN BALANCE4
        ELSE
        (BALANCE1+(BALANCE5-BALANCE4))
        END AS BAL,
        LEAD(CBLDATE,1) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE) PRE_DATE,
        LEAD(CBLDATE,1) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE) - CBLDATE DAYS,
        LAST_VALUE(BALANCE4) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LAST_BAL,
        LAST_VALUE(CBLDATE) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LAST_CBL_DATE,
        FIRST_VALUE(CBLDATE) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MIN_DATE
        FROM D010014
        WHERE FLOOR(SUBSTR(PRDACCTID,17,8)) <>0
        and trim(substr(prdacctid,1,8)) in ('CC','CDOD','DDPOD')
        AND CBLDATE between to_date(:P304_FROMDATE,'dd-mon-yyyy') and to_date(:P304_TODATE,'dd-mon-yyyy')
      
      
        )
        ) group by branch,prd,LAST_BAL,LAST_CBL_DATE,MIN_DATE ORDER BY BRANCH,PRD
        )Q6,
        (
        select
        sum(decode(DRCR,'D',FCYTRNAMT,0)) DEBIT,
        SUM(DECODE(DRCR,'C',FCYTRNAMT,0)) CREDIT,
        SUM(DECODE(CASHFLOWTYPE,'INTDR',FCYTRNAMT,0)) INT,
        MAINACCTID,LBRCODE
        from d009040
        where TRIM(SUBSTR(mainacctid,17,8))<>0
        AND TRIM(SUBSTR(MAINACCTID,1,8)) IN ('CC','CDOD','DDPOD')
        and postdate between to_date(:P304_FROMDATE,'dd-mon-yyyy') and to_date(:P304_TODATE,'dd-mon-yyyy')
        AND CANCELEDFLAG<>'C'
        GROUP BY MAINACCTID,LBRCODE
        )Q7
      
      
      WHERE q1.lbrcode=q2.lbrcode
      and q2.prdacctid=q1.prdacctid
      and q1.lbrcode=q3.lbrcode
      and q1.prdacctid=q3.prdacctid
      and q1.lbrcode=q4.lbrcode(+)
      and q1.prdacctid=q4.prdacctid(+)
      and q1.lbrcode=q5.lbrcode(+)
      and q1.prdacctid=q5.prdacctid(+)
      AND Q1.LBRCODE||Q1.PRDACCTID=Q6.BRANCH||Q6.PRD
      AND Q1.LBRCODE||Q1.PRDACCTID=Q7.LBRCODE||Q7.MAINACCTID
      AND Q3.TOTSANCLIMIT >=:P304_SANC_AMT
      order by Q1.LBRCODE,q1.prdacctid
      )
      )
      WHERE LBRCODE BETWEEN :P304_FROMBRANCH AND :P304_TOBRANCH
      AND (
      :P304_SELECTLIST IS NULL OR 
      instr(':'||:P304_SELECTLIST||':',':'||trim(PRD)||':') > 0)
      

      what is happening?

      i have oracle11g standard edition