Home » Developer & Programmer » Reports & Discoverer » HELP ME PLEASE WITH REPORT PLEASE, PLEASE!!!!!!
HELP ME PLEASE WITH REPORT PLEASE, PLEASE!!!!!! [message #87938] Thu, 08 November 2001 06:11 Go to next message
SARR
Messages: 1
Registered: November 2001
Junior Member
I have a very strange problem with reports.
I must choose between 5 status(NA, NC, AK, PC and IC) the status needed by my report(it would be the final status).
I have written a PL/SQL function. When i run the report, i dont get any records. I just get an error message which is
rep- 1401. A fatal error PL/SQL is generated.

Can anybody help me ?
What can I do to correct my program ?
Please Help me ?

GREATEST THANKS !

THIS IS MY PROGRAM:
--------------------------------------------------------------------------------------------------------------

function statuts_finauxFormula return VARCHAR2 is

--DECLARATION DES VARIABLES

Vnb_nk NUMBER; -- Total trouvé pour les statuts NK
Vnb_nc NUMBER; -- Total trouvé pour les statuts NC
Vnb_ak NUMBER; -- Total trouvé pour les statuts AK
Vnb_pc NUMBER; -- Total trouvé pour les statuts PC
Vnb_ic NUMBER; -- Total trouvé pour les statuts IC

Vstatut BRISUIPAY.STATUT%type; -- Variable de même type que STATUT
Vref_emet_roc BRISUIPAY.REF_EMET_ROC%type; -- Variable de même type que REF_EMET_ROC
Vflux BRISUIPAY.FLUX%type; -- Variable de même type que FLUX
Vsuipay_id BRISUIPAY.SUIPAY_ID%type; -- Variable de même type que SUIPAY_ID
Vlibel_statut BRISUIPAY.LIBEL_STATUT%type; -- Variable de même type que LIBEL_STATUT
Vhoro BRISUIPAY.HORO%type; -- Variable de même type que HORO
flux BRISUIPAY.FLUX%type; -- Variable de même type que FLUX

BEGIN

-- TRAITEMENT DES FLUX 398SW( REQUETTE GENERALE)
------------------------------------------------------------------------------------

IF (flux='398') THEN

-- RECUPERATION DU NOMBRE DE NK POUR LES 398
------------------------------------------------------------------------

SELECT distinct
a.flux,
a.statut,
a.libel_statut,
a.ref_emet_roc,
TO_CHAR(a.horo,'DD/MM/YYYY HH24:MI:SS'),
DECODE(a.statut, 'NK', 1, 2)nb_nk
INTO Vstatut,Vref_emet_roc,Vnb_nk
FROM brisuipay a
WHERE statut = 'NK' AND flux = 'ACK'
AND ref_emet_roc IN (SELECT ref_emet_roc FROM brisuipay WHERE flux='398')
AND ref_emet_roc= a.ref_emet_roc ;

-- RECUPERATION DU NOMBRE DE NC POUR LES 398
-----------------------------------------------------------------------------

SELECT distinct
a.flux,
a.statut,
a.libel_statut,
a.ref_emet_roc,
TO_CHAR(a.horo,'DD/MM/YYYY HH24:MI:SS'),
DECODE(a.statut, 'NC', 1, 2)nb_nc
INTO Vstatut,Vref_emet_roc,Vnb_nc
FROM BRISUIPAY a
WHERE statut = 'NC' AND flux = 'ACB'
AND ref_emet_roc IN (SELECT ref_emet_roc FROM brisuipay WHERE flux='398')
AND ref_emet_roc= a.ref_emet_roc ;

-- RECUPERATION DU NOMBRE DE AK POUR LES 398
------------------------------------------------------------------------------


SELECT distinct
a.flux,
a.statut,
a.libel_statut,
a.ref_emet_roc,
TO_CHAR(a.horo,'DD/MM/YYYY HH24:MI:SS'),
DECODE(a.statut, 'AK', 1, 2)nb_ak
INTO Vstatut,Vref_emet_roc,Vnb_ak
FROM BRISUIPAY a
WHERE statut = 'AK' AND flux = 'ACK'
AND ref_emet_roc IN (SELECT ref_emet_roc FROM brisuipay WHERE flux='398')
AND ref_emet_roc= a.ref_emet_roc ;

-- RECUPERATION DU NOMBRE DE PC POUR LES 398
-----------------------------------------------------------------------------

SELECT distinct
a.flux,
a.statut,
a.libel_statut,
a.ref_emet_roc,
TO_CHAR(a.horo,'DD/MM/YYYY HH24:MI:SS'),
DECODE(a.statut, 'PC', 1, 2)nb_pc
INTO Vstatut,Vref_emet_roc,Vnb_pc
FROM BRISUIPAY a
WHERE statut = 'PC' AND flux = 'ACB'
AND ref_emet_roc IN (SELECT ref_emet_roc FROM brisuipay WHERE flux='398')
AND ref_emet_roc= a.ref_emet_roc ;

-- RECUPERATION DU NOMBRE DE IC POUR LES 398
-----------------------------------------------------------------------------

SELECT distinct
a.flux,
a.statut,
a.libel_statut,
a.ref_emet_roc,
TO_CHAR(a.horo,'DD/MM/YYYY HH24:MI:SS'),
DECODE(a.statut, 'IC', 1, 2)nb_ic
INTO Vstatut,Vref_emet_roc,Vnb_ic
FROM BRISUIPAY a
WHERE statut = 'IC' AND flux = '398'
AND ref_emet_roc IN (SELECT ref_emet_roc FROM brisuipay WHERE flux='398')
AND ref_emet_roc= a.ref_emet_roc;

IF (Vnb_nk > 0) THEN Vstatut:='NK';

ELSIF (Vnb_nc > 0) THEN Vstatut:='NC';

ELSIF (Vnb_ak > 0) THEN Vstatut:='AK';

ELSIF (Vnb_pc > 0) THEN Vstatut:='PC';

ELSE Vstatut:='IC';

return(Vstatut);

END IF ;

--SINON -----> TRAITEMENT DES FLUX DIFFERENTS DES 398SW
-----------------------------------------------------------------------------------------------

ELSE

SELECT statut, libel_statut, TO_CHAR(horo,'DD/MM/YYYY HH24:MI:SS')
INTO Vstatut, Vlibel_statut, Vhoro
FROM brisuipay
WHERE (suipay_id,ref_emet_roc) IN (SELECT max(suipay_id),ref_emet_roc FROM brisuipay GROUP BY ref_emet_roc);
return(Vstatut);

END IF;

END;

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

----------------------------------------------------------------------
Re: HELP ME PLEASE WITH REPORT PLEASE, PLEASE!!!!!! [message #87946 is a reply to message #87938] Mon, 12 November 2001 20:14 Go to previous messageGo to next message
Bala Subramannian
Messages: 2
Registered: November 2001
Junior Member
hai
Try to break the PL/SQL code into 2 different functions to see the effect.If the problem is solved mail me back.
bye
Bala Subramannian

----------------------------------------------------------------------
Re: HELP ME PLEASE WITH REPORT PLEASE, PLEASE!!!!!! [message #88444 is a reply to message #87938] Mon, 02 September 2002 05:45 Go to previous messageGo to next message
MOHAMMED MAQSOOD ALI
Messages: 1
Registered: September 2002
Junior Member
I am having a PL/SQL formula column to display description of a grade based on a grade code.
the code is like this

grade_ds Varchar2(75);
begin
Select ltrim(rtrim(code_long_desc)) into grade_ds
from gn_codes_Detail
where code_id = 'GT'
and to_number(code_value) = :grade
and to_number(sort_order) = :cmp_id;
Return grade_ds;
Exception
When No_data_found then
return 'U/C';
end;

I am getting the error REP-1401 CF_GRADE_DESCFORMULA : FATAL PL/SQL ERROR ENCOUNTERED.

and reports get terminated.
PLEASE HELP ME.
Re: HELP ME PLEASE WITH REPORT PLEASE, PLEASE!!!!!! [message #88961 is a reply to message #88444] Wed, 09 July 2003 04:21 Go to previous message
DANISH
Messages: 5
Registered: March 2002
Junior Member
No Message Body
Previous Topic: Problem with paper size in Reports Builder
Next Topic: Urgent -Group Above Report
Goto Forum:
  


Current Time: Mon Jan 17 10:28:06 CST 2022