Hello gurus,
I need to represent values in the below format:
| End Customer Account Name |
| End Customer Country |
| Sales Order # |
| Product SKU | Quantity | Start date | End date |
The below ones can be of multiple rows
| Product SKU | Quantity | Start date | End date |
I used the below approach but the whole content i,e repeating
| End Customer Account Name |
| End Customer Country |
| Sales Order # |
| Product SKU | Quantity | Start date | End date |
DECLARE
p_order_number NUMBER;
TYPE V_STR IS TABLE OF VARCHAR2(32767);
v_str_col v_str:= v_str();
c_str CLOB;
v_count NUMBER:=0;
v_service_Reference_type_Code VARCHAR2(300):=NULL;
v_refer_item VARCHAR2(1000):=NULL;
l_email_users VARCHAR2(2000):=NULL;
BEGIN
EXECUTE IMMEDIATE 'SELECT ''<html><head>
<style>
table, th, td {
border: 1px solid black;
}
</style>
</head>
<body>
<table>
<tr>
<th colspan="2">Sales Order ''||order_number||'' has been shipped for Details below: </th>
</tr>
<tr>
<th colspan="2">Lici Information</th>
</tr>
<tr>
<td>End Customer Account Name</td>
<td>''||end_customer_party||''</td>
</tr>
<tr>
<td>End Customer Country</td>
<td>''||territory_short_name||''</td>
</tr>
<tr>
<td>Sales Order #</td>
<td>''||order_number||''</td>
</tr>
<tr>
<td>Product SKU</td>
<td>''||ordered_item||''</td>
</tr>
<tr>
<td>Quantity</td>
<td>''||ordered_quantity||''</td>
</tr>
<tr>
<td>Subscription Start Date</td>
<td>''||SERVICE_START_DATE||''</td>
</tr>
<tr>
<td>Subscription End Date</td>
<td>''||SERVICE_END_DATE||''</td>
</tr>
</table>
</body>
</html>
<br /><br />
<table>
<tr>
<th colspan="2">Technical Contact Information</th>
</tr>
<tr>
<td>Reseller</td>
<td>''||attribute18||''</td>
</tr>
<tr>
<td>Technical Name</td>
<td>''||attribute19||''</td>
</tr>
<tr>
<td>Technical Contact E-Mail Address</td>
<td>''||attribute20||''</td>
</tr>
</table>
<br /><br />
<table>
<tr>
<td>Purchase Order #</td>
<td>''||cust_po_number||''</td>
</tr>
</table>
<br /><br />
<table>
<tr>
<td>Please Provide the Serial Number :</td>
</tr>
</table>
<br /><br />
</table>
<br /><br />
''
FROM apps.TEST_VIEW
WHERE 1=1
AND ORDER_NUMBER = ' ||''''||108256||''''||''
BULK COLLECT INTO v_str_col;
FOR I IN 1 .. v_str_col.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_str_col(i));
c_str := c_str||CHR(13)||v_str_col(i);
v_count:= v_str_col.COUNT;
END LOOP;
END;