Hi,
I am sending a large amount of data in custom workflow notification with clob data type.
It displays first 44 lines, but if data is more than 44 records then it gives subject error.
Can any body help me to resolve this issue?
Below is my function code:
FUNCTION Format_Notification_Msg (Trans_Id IN VARCHAR2) RETURN CLOB IS
l_clob CLOB;
l_body clob; --VARCHAR2(32767);
total_amount number;
cnt number; ----
CURSOR c IS
select rt.transaction_type,rt.quantity, rt.unit_of_measure, trunc(rt.transaction_date) transaction_date, rt.destination_type_code, rsh.receipt_num,rsh.receipt_source_code
,sup.vendor_name,pha.segment1 PO_Number ,pla.line_num ,pla.item_description, rt.country_of_origin_code
,trim(to_char(rt.po_unit_price,'999G999G999G999D99')) po_unit_price ,rt.currency_code,rt.currency_conversion_rate,trim(to_char((rt.quantity*rt.po_unit_price),'999G999G999G999D99')) amount, pra.release_num
,(select cc.concatenated_segments from po_distributions_all pda ,gl_code_combinations_kfv cc where cc.code_combination_id = pda.code_combination_id and pda.po_distribution_id = rt.po_distribution_id) cc
,rt.quantity*rt.po_unit_price amount2
from po.rcv_transactions rt
,po_headers_all pha
,po_lines_all pla
,rcv_shipment_headers rsh
,rcv_shipment_lines rsl
,ap_suppliers sup
,po_releases_all pra
where pha.po_header_id = pla.po_header_id
and rt.po_header_id = pla.po_header_id
and rt.po_line_id = pla.po_line_id
and rsh.shipment_header_id = rsl.shipment_header_id
and rt.shipment_header_id = rsl.shipment_header_id
and rt.shipment_line_id = rsl.shipment_line_id
and rt.vendor_id = sup.vendor_id
--and pra.po_header_id(+) = pha.po_header_id
and rt.po_release_id = pra.po_release_id(+)
and rt.transaction_type = 'RECEIVE'
and rsh.receipt_num = Trans_Id
order by pla.line_num;
BEGIN
FOR e IN C LOOP
total_amount := nvl(total_amount,0) + to_number(e.amount2);
END LOOP;
Dbms_lob.CreateTemporary(l_clob,TRUE);
l_body := null;
l_body := '<Html>';
l_body := l_body||'<Body>';
-- dbms_lob.append( l_body, '<Body>' ) ;
l_body := l_body||'<BR> ' || 'Please note that Receipt Number <B> '|| Trans_Id || ' with amount: ' || trim(to_char(total_amount,'999G999G999G999D99')) ||'</B> has been sent to you for approval. Detail is given below:' || '<Br> <BR> ';
--------------------------
l_body := l_body||'</Table><Br><Br>';
l_body := l_body||'<Table Border=1">';
l_body := l_body||'<Tr Bgcolor=rgb(162,202,230)>';
l_body := l_body||'<Th><P><Font Size = 2 Face="Verdana, Arial, Helvetica"><div align=left>Date';
l_body := l_body||'<Th><P><Font Size = 2 Face="Verdana, Arial, Helvetica"><div align=left>Vendor Name';
l_body := l_body||'<Th><P><Font Size = 2 Face="Verdana, Arial, Helvetica"><div align=left>PO Number';
l_body := l_body||'<Th><P><Font Size = 2 Face="Verdana, Arial, Helvetica"><div align=left>Release Number';
l_body := l_body||'<Th><P><Font Size = 2 Face="Verdana, Arial, Helvetica"><div align=left>Line';
l_body := l_body||'<Th><P><Font Size = 2 Face="Verdana, Arial, Helvetica"><div align=left>Item Description';
l_body := l_body||'<Th><P><Font Size = 2 Face="Verdana, Arial, Helvetica"><div align=left>Quantity';
l_body := l_body||'<Th><P><Font Size = 2 Face="Verdana, Arial, Helvetica"><div align=left>Unit';
l_body := l_body||'<Th><P><Font Size = 2 Face="Verdana, Arial, Helvetica"><div align=left>PO Unit Price';
l_body := l_body||'<Th><P><Font Size = 2 Face="Verdana, Arial, Helvetica"><div align=left>Currency';
l_body := l_body||'<Th><P><Font Size = 2 Face="Verdana, Arial, Helvetica"><div align=left>Amount';
l_body := l_body||'<Th><P><Font Size = 2 Face="Verdana, Arial, Helvetica"><div align=left>Code Combination';
l_body := l_body||'</Tr>';
FOR x IN C LOOP
cnt := cnt+1;
if cnt > 45 then exit; end if;
l_body := l_body||'<Tr>';
l_body := l_body||'<Td><P><Font Size = 2><div align=left>'||x.transaction_date||'</Td>';
l_body := l_body||'<Td><P><Font Size = 2><div align=left>'||x.vendor_name||'</Td>';
l_body := l_body||'<Td><P><Font Size = 2><div align=left>'||x.po_number||'</Td>';
l_body := l_body||'<Td><P><Font Size = 2><div align=left>'||x.release_num||'</Td>';
l_body := l_body||'<Td><P><Font Size = 2><div align=left>'||x.line_num||'</Td>';
l_body := l_body||'<Td><P><Font Size = 2><div align=left>'||x.item_description||'</Td>';
l_body := l_body||'<Td><P><Font Size = 2><div align=right>'||x.quantity||'</Td>';
l_body := l_body||'<Td><P><Font Size = 2><div align=left>'||x.unit_of_measure||'</Td>';
l_body := l_body||'<Td><P><Font Size = 2><div align=right>'||x.po_unit_price||'</Td>';
l_body := l_body||'<Td><P><Font Size = 2><div align=left>'||x.currency_code||'</Td>';
l_body := l_body||'<Td><P><Font Size = 2><div align=right>'||x.amount||'</Td>';
l_body := l_body||'<Td><P><Font Size = 2><div align=left>'||x.cc||'</Td>';
l_body := l_body||'</Tr>';
END LOOP;
l_body := l_body||'</Table><Br><Br>';
l_body := l_body||'</Body>'||CHR(10);
l_body := l_body||'</Html>'||CHR(10);
dbms_lob.writeappend(l_clob,LENGTH(l_body),l_body);
RETURN L_Clob;
ORA-06502: PL/SQL: numeric or value error ORA-0651
Who is online
Users browsing this forum: No registered users and 12 guests