ERPstuff - Oracle EBS Community

function CF_RT_UOM_RATEFormula return Number is

v_rate number;
begin
select distinct muc.CONVERSION_RATE
into v_rate
from rcv_transactions rt, rcv_shipment_headers rsh, rcv_shipment_lines rsl, mtl_uom_conversions muc
where rt.SHIPMENT_HEADER_ID = rsh.SHIPMENT_HEADER_ID
and muc.UNIT_OF_MEASURE = rt.UNIT_OF_MEASURE
and rt.SHIPMENT_LINE_ID = rsl.SHIPMENT_LINE_ID
and rsh.RECEIPT_NUM = :GRN_NO
and rt.ORGANIZATION_ID = rg_id
and rsl.ITEM_ID = :ITEM_ID
and rt.TRANSACTION_TYPE = 'DELIVER';
return v_rate;

exception when no_data_found then
return 1;

end;


when i run report on 19-FEB-2007 then this error occur and it is working well for other days.

item id is differ but rec_number is same
e.g item id Rec_num
16 540082 4693 19-FEB-2007
17 161849 4693 19-FEB-2007
18 540072 4693 19-FEB-2007
19 426064 4693 19-FEB-2007
20 426062 4693 19-FEB-2007


Please give me idea that i resolve to this problem.

Did you manage to get an answer to this issue; I'm having the same issue with one of my reports6i.


Thanks,

Leo

Instead of report paste the query in the sqlplus and check the results. May be returning multiple values or data size issue. thanks

Instead of report paste the query in the sqlplus and check the results. May be returning multiple values or data size issue. thanks

<i><div align="right">Originally posted by admin - Apr 23 2009 : 08:29:29 AM</div id="right"></i>

function CUSTOMER_NAMEFormula return Char is
t_customer_name varchar2(200);
t_reference_id number :=:reference_7;
t_ae_line_id number :=:ae_line_id;
begin
if :source= 'Receivables' then

select customer_name
into t_customer_name
from ra_customers
where customer_id = t_reference_id;

elsif :source ='Payables' then

select v.vendor_name
into t_customer_name
from ap_ae_lines_all ael,
po_vendors v
where ael.ae_line_id = :ae_line_id
and ael.third_party_id = v.vendor_id;
end if;
return nvl(t_customer_name,null);
exception when no_data_found then
return(null);
end;

It is not a query issue because I can query the data fine but it doesn't work in a report.

As I said earlier check the sizes of variables. Like reports container size in which you are returning values. Also check variables like t_customer_name varchar2(200); and verify if any customer is not greater than this size.... And as these are functions and there must be a main query so check that as well. First try to find the place from where the error is raising like commenting a function then execute etc. Thanks