通过发票编号取凭证编号,返回多行值，拼接到一起

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;