function GET_DOC_SEQUENCE_VALUE(P_INVOICE_NUM varchar2) return varchar2 is
V_DOC_SEQUENCE_VALUE varchar2(2000);
cursor cur_doc (l_invoice varchar2 )is
select doc_sequence_value
from ap_invoices_all
where invoice_num = l_invoice
and cancelled_date is null;
l_count number:=0;
begin
begin
select count(*)
into l_count
from ap_invoices_all
where invoice_num = P_INVOICE_NUM
and cancelled_date is null;
exception
when others then
V_DOC_SEQUENCE_VALUE := '';
end;
for l_cur_doc in cur_doc(P_INVOICE_NUM) loop
if l_count = 1 then
V_DOC_SEQUENCE_VALUE:=l_cur_doc.doc_sequence_value;
else
if V_DOC_SEQUENCE_VALUE is null then
V_DOC_SEQUENCE_VALUE:=l_cur_doc.doc_sequence_value;
else
V_DOC_SEQUENCE_VALUE:=V_DOC_SEQUENCE_VALUE||','||l_cur_doc.doc_sequence_value;
end if ;
end if ;
end loop ;
RETURN V_DOC_SEQUENCE_VALUE;
END;
经验首页