Impressão de layout de documentos (cotação de vendas e pedido de vendas) utilizando HTML

Para ativar o recurso, deve ser incluída a chave a seguir no arquivo “Configs.js” localizado na pasta “Assets”

let habilitaGeracaoLayoutHTML = true;

Código fonte para criação da função de banco de dados, válido para SAP HANA.

Layout Retrato

CREATE FUNCTION CRMONE_LAYOUT_HTML
(
DocType VARCHAR(255),
DocEntry INT
)
RETURNS
HTML NCLOB
LANGUAGE SQLSCRIPT
AS
BEGIN


---- COMPOSIÇÃO HTML
DECLARE HEAD NCLOB;
DECLARE BODY NCLOB;
DECLARE BODYROWS NCLOB;
DECLARE IMPOSTOS NCLOB;
DECLARE FEET NCLOB;

---- DADOS EMPRESA
DECLARE DADOSEMPRESA NCLOB;

---- CABEÇALHO DOCUMENTO
DECLARE SIGLA NVARCHAR(20);
DECLARE TIPODOC NVARCHAR(250);
DECLARE NUMDOC INT;
DECLARE BPLID INT;
DECLARE DATADOC NVARCHAR(20);
DECLARE NUMREFPN NVARCHAR(100);
DECLARE CODPN NVARCHAR(50);
DECLARE NOMEPN NVARCHAR(200);
DECLARE TIPORUA NVARCHAR(250);
DECLARE NOMERUA NVARCHAR(250);
DECLARE NUMRUA NVARCHAR(250);
DECLARE COMPRUA NVARCHAR(250);
DECLARE BAIRRO NVARCHAR(250);
DECLARE CEP NVARCHAR(250);
DECLARE CIDADE NVARCHAR(250);
DECLARE ESTADO NVARCHAR(250);
DECLARE TAX0 NVARCHAR(250);
DECLARE TAX1 NVARCHAR(250);
DECLARE TAX4 NVARCHAR(250);
DECLARE TEL1 NVARCHAR(30);
DECLARE TEL2 NVARCHAR(30);
DECLARE TEL3 NVARCHAR(30);
DECLARE TEL4 NVARCHAR(30);
DECLARE EMAIL NVARCHAR(250);
DECLARE PESSOACONTATO NVARCHAR(200);

----- LINHAS DO DOCUMENTO
DECLARE NUMLINHA NVARCHAR(20);
DECLARE CODITEM NVARCHAR(100);
DECLARE DESCITEM NVARCHAR(200);
DECLARE TEXTOLIVRE NVARCHAR(200);
DECLARE QUANT NVARCHAR(30);
DECLARE PRECO NVARCHAR(30);
DECLARE PERCDESC NVARCHAR(30);
DECLARE PRECOCDESC NVARCHAR(30);
DECLARE VALORTOTAL NVARCHAR(30);
DECLARE DATAENTREGA NVARCHAR(30);

------- DADOS RODAPÉ
DECLARE TOTALPRODUTOS NVARCHAR(30);
DECLARE TOTALIMPOSTOSADICIONAIS NVARCHAR(30);
DECLARE TOTALDOCUMENTO NVARCHAR(30);
DECLARE DESPESASADICIONAIS NVARCHAR(30);
DECLARE DESCONTOTOTAL NVARCHAR(30);
DECLARE VENDEDOR NVARCHAR(100);
DECLARE CONDPAGTO NVARCHAR(100);
DECLARE TRANSPORTADORA NVARCHAR(200);
DECLARE TIPOFRETE NVARCHAR(100);
DECLARE OBSERVACOES NVARCHAR(254);
DECLARE PESOBRUTO NVARCHAR(20);
DECLARE PESOLIQUIDO NVARCHAR(20);


----- PROCESSAMENTO



IF DocType = '17'
THEN

SIGLA := 'P. V.';

SELECT 
'PEDIDO DE VENDAS', 
T0."DocNum", 
IFNULL(T0."BPLId",0), 
TO_VARCHAR( T0."DocDate", 'DD/MM/YYYY'),
IFNULL(T0."NumAtCard",''),
IFNULL(T0."CardCode",''),
IFNULL(T0."CardName",''),
IFNULL(T1."AddrTypeS",''), 
IFNULL(T1."StreetS",''), 
IFNULL(T1."StreetNoS",''), 
IFNULL(T1."BuildingS",''), 
IFNULL(T1."BlockS",''), 
IFNULL(T1."ZipCodeS",''),
IFNULL(T1."CityS",''),  
IFNULL(T1."StateS",''), 
IFNULL(T1."TaxId0",''), 
IFNULL(T1."TaxId1",''), 
IFNULL(TO_NVARCHAR(T1."TaxId4"),''),
IFNULL(T2."Phone1",''),
IFNULL(T2."Phone2",''),
IFNULL(T2."Fax",''),
IFNULL(T2."Cellular",''),
IFNULL(T2."E_Mail",''),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."DocTotal",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."VatSum",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."DocTotal",0)-IFNULL(T0."VatSum",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."TotalExpns",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."DiscSum",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
IFNULL(T3."SlpName",''),
IFNULL(T4."PymntGroup",''),
IFNULL(T5."CardName",''),
CASE 
WHEN T1."Incoterms" = '0' THEN 'Frete por conta do Remetente (CIF)'
WHEN T1."Incoterms" = '1' THEN 'Frete por conta do Destinatário (FOB)'
WHEN T1."Incoterms" = '2' THEN 'Frete por conta de Terceiros'
WHEN T1."Incoterms" = '3' THEN 'Transporte Próprio por conta do Remetente'
WHEN T1."Incoterms" = '4' THEN 'Transporte Próprio por conta do Destinatário'
WHEN T1."Incoterms" = '9' THEN 'Sem Ocorrência de Transporte'
ELSE 'Não informado' END,
IFNULL(T0."Comments",''),
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IFNULL(T1."GrsWeight",0),'999,999,990.000'),'0.000'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IFNULL(T1."NetWeight",0),'999,999,990.000'),'0.000'),',','@'),'.',','),'@','.'),
IFNULL(T6."Name",'') || ' - '|| IFNULL(T6."FirstName",'') || ' ' || IFNULL(T6."LastName",'') 
INTO TIPODOC, NUMDOC, BPLID, DATADOC, NUMREFPN, 
CODPN, NOMEPN,  TIPORUA, NOMERUA, NUMRUA, COMPRUA, BAIRRO, CEP, CIDADE, ESTADO, 
TAX0, TAX1, TAX4,
TEL1, TEL2, TEL3, TEL4, EMAIL,
TOTALDOCUMENTO, TOTALIMPOSTOSADICIONAIS, TOTALPRODUTOS, DESPESASADICIONAIS, DESCONTOTOTAL, 
VENDEDOR, CONDPAGTO, TRANSPORTADORA, TIPOFRETE, OBSERVACOES,
PESOBRUTO, PESOLIQUIDO,
PESSOACONTATO
FROM ORDR T0
INNER JOIN RDR12 T1 ON T0."DocEntry" = T1."DocEntry"
INNER JOIN OCRD T2 ON T0."CardCode" = T2."CardCode"
LEFT JOIN OSLP T3 ON T0."SlpCode" = T3."SlpCode"
INNER JOIN OCTG T4 ON T0."GroupNum" = T4."GroupNum"
LEFT JOIN OCRD T5 ON T1."Carrier" = T5."CardCode"
LEFT JOIN OCPR T6 ON T0."CntctCode" = T6."CntctCode" 
WHERE T0."DocEntry" = :DocEntry;

IF :BPLID = 0
	THEN
		
	DADOSEMPRESA := 
		'
		<b>RAZAO SOCIAL MATRIZ LTDA.</b></br>
		Rua Xyz, 100 - Centro</br>
		CEP 90000-000 - Porto Alegre - RS</br>
		CNPJ: 99.999.999/0001-01 - I.E.: 123.456.789</br>
		Telefone +55 (51) 11112222</br>
		www.empresa.com.br
		';	

	ELSE
	SELECT 
		'<b>' || IFNULL(T0."BPLName",'')|| '</b></br>'||
		IFNULL(T0."AddrType",'') || ' ' ||
		IFNULL(T0."Street",'') || ', ' ||
		IFNULL(T0."StreetNo",'') || ' ' || 
		IFNULL(T0."Building",'')|| ' - ' ||
		IFNULL(T0."Block",'') || '</br>' ||
		'CEP '|| IFNULL(T0."ZipCode",'') || ' - ' ||
		IFNULL(T0."City",'') || ' - ' ||
		IFNULL(T0."State",'') || '</br>' ||
		'CNPJ: ' || IFNULL(T0."TaxIdNum",'') || ' - ' ||
		'I.E.: ' || IFNULL(T0."TaxIdNum2",'') || '</br>' ||
		(SELECT 'Telefone ' || IFNULL("Phone1",'') || ' '|| IFNULL("Phone2",'') FROM "OADM") || '</br>' ||
		(SELECT IFNULL("IntrntAdrs",'') FROM ADM1) ||'</br>'		
		INTO DADOSEMPRESA
		FROM OBPL T0 
		WHERE T0."BPLId" = :BPLID;


	END IF;
	


SELECT 
(
SELECT 
TO_NVARCHAR(T0."LineNum"+1,'999') AS "td",
IFNULL(T0."ItemCode",'') AS "td",
IFNULL(T0."Dscription",'') || ' - ' || IFNULL(T0."FreeTxt",'') AS "td", 
IFNULl(T0."unitMsr",'') AS "td",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."Quantity",0),'999,999,990.000'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."PriceBefDi",0),'999,999,990.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."DiscPrcnt",0),'999,999,990.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."LineTotal"/T0."Quantity",0),'999,999,990.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."LineTotal",0),'999,999,990.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(ICMS."TaxRate",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(ST."TaxSum",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IPI."TaxRate",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IPI."TaxSum",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IFNULL(IPI."TaxSum",0)+IFNULL(ST."TaxSum",0)+T0."LineTotal",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
TO_NVARCHAR(T0."ShipDate", 'DD/MM/YYYY') AS "td align=right"
FROM RDR1 T0 
LEFT JOIN RDR4 ICMS ON ICMS."DocEntry" = T0."DocEntry" AND ICMS."LineNum" = T0."LineNum" AND ICMS."staType" = 10
LEFT JOIN RDR4 IPI ON IPI."DocEntry" = T0."DocEntry" AND IPI."LineNum" = T0."LineNum" AND IPI."staType" = 16
LEFT JOIN RDR4 ST ON ST."DocEntry" = T0."DocEntry" AND ST."LineNum" = T0."LineNum" AND ST."staType" = 13
WHERE T0."DocEntry" = :DocEntry FOR XML
)
INTO BODYROWS
FROM DUMMY;

SELECT
IFNULL((

SELECT 
CASE WHEN T0."TaxInPrice" = 'Y'
THEN IFNULL(T1."Name",'') 
ELSE IFNULL(T1."Name",'') || ' (+)' END AS "td", 
CASE WHEN T0."TaxInPrice" = 'N' 
THEN '' 
ELSE REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(SUM( T0."TaxSum"),'999,999,999.99'),'0.00'),',','@'),'.',','),'@','.')
 END AS "td align=right", 
CASE WHEN T0."TaxInPrice" = 'Y' 
THEN '' 
ELSE REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(SUM( T0."TaxSum"),'999,999,999.99'),'0.00'),',','@'),'.',','),'@','.')
END AS "td align=right" 
FROM RDR4 T0  
INNER JOIN OSTT T1 ON T0."staType" = T1."AbsId" 
WHERE T0."DocEntry" = :DocEntry 
GROUP BY T1."Name", T0."TaxInPrice" 
ORDER BY T0."TaxInPrice",1 FOR XML
),'<tr><td>SEM IMPOSTOS CALCULADOS</td><td align=right>0,00</td><td align=right>0,00</td></tr>')
INTO IMPOSTOS
FROM DUMMY;


------------------------- FIM PEDIDO DE VENDAS

ELSE

------------------------- INICIO COTAÇÃO DE VENDAS

SIGLA := 'C. V.';

SELECT 
'COTAÇÃO DE VENDAS', 
T0."DocNum", 
IFNULL(T0."BPLId",0), 
TO_VARCHAR( T0."DocDate", 'DD/MM/YYYY'),
IFNULL(T0."NumAtCard",''),
IFNULL(T0."CardCode",''),
IFNULL(T0."CardName",''),
IFNULL(T1."AddrTypeS",''), 
IFNULL(T1."StreetS",''), 
IFNULL(T1."StreetNoS",''), 
IFNULL(T1."BuildingS",''), 
IFNULL(T1."BlockS",''), 
IFNULL(T1."ZipCodeS",''),
IFNULL(T1."CityS",''),  
IFNULL(T1."StateS",''), 
IFNULL(T1."TaxId0",''), 
IFNULL(T1."TaxId1",''), 
IFNULL(TO_NVARCHAR(T1."TaxId4"),''),
IFNULL(T2."Phone1",''),
IFNULL(T2."Phone2",''),
IFNULL(T2."Fax",''),
IFNULL(T2."Cellular",''),
IFNULL(T2."E_Mail",''),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."DocTotal",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."VatSum",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."DocTotal",0)-IFNULL(T0."VatSum",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."TotalExpns",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."DiscSum",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
IFNULL(T3."SlpName",''),
IFNULL(T4."PymntGroup",''),
IFNULL(T5."CardName",''),
CASE 
WHEN T1."Incoterms" = '0' THEN 'Frete por conta do Remetente (CIF)'
WHEN T1."Incoterms" = '1' THEN 'Frete por conta do Destinatário (FOB)'
WHEN T1."Incoterms" = '2' THEN 'Frete por conta de Terceiros'
WHEN T1."Incoterms" = '3' THEN 'Transporte Próprio por conta do Remetente'
WHEN T1."Incoterms" = '4' THEN 'Transporte Próprio por conta do Destinatário'
WHEN T1."Incoterms" = '9' THEN 'Sem Ocorrência de Transporte'
ELSE 'Não informado' END,
IFNULL(T0."Comments",''),
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IFNULL(T1."GrsWeight",0),'999,999,990.000'),'0.000'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IFNULL(T1."NetWeight",0),'999,999,990.000'),'0.000'),',','@'),'.',','),'@','.'),
IFNULL(T6."Name",'') || ' - '|| IFNULL(T6."FirstName",'') || ' ' || IFNULL(T6."LastName",'') 
INTO TIPODOC, NUMDOC, BPLID, DATADOC, NUMREFPN, 
CODPN, NOMEPN,  TIPORUA, NOMERUA, NUMRUA, COMPRUA, BAIRRO, CEP, CIDADE, ESTADO, 
TAX0, TAX1, TAX4,
TEL1, TEL2, TEL3, TEL4, EMAIL,
TOTALDOCUMENTO, TOTALIMPOSTOSADICIONAIS, TOTALPRODUTOS, DESPESASADICIONAIS, DESCONTOTOTAL, 
VENDEDOR, CONDPAGTO, TRANSPORTADORA, TIPOFRETE, OBSERVACOES,
PESOBRUTO, PESOLIQUIDO,
PESSOACONTATO
FROM OQUT T0
INNER JOIN QUT12 T1 ON T0."DocEntry" = T1."DocEntry"
INNER JOIN OCRD T2 ON T0."CardCode" = T2."CardCode"
LEFT JOIN OSLP T3 ON T0."SlpCode" = T3."SlpCode"
INNER JOIN OCTG T4 ON T0."GroupNum" = T4."GroupNum"
LEFT JOIN OCRD T5 ON T1."Carrier" = T5."CardCode"
LEFT JOIN OCPR T6 ON T0."CntctCode" = T6."CntctCode" 
WHERE T0."DocEntry" = :DocEntry;

IF :BPLID = 0
	THEN
		
	DADOSEMPRESA := 
		'
		<b>RAZAO SOCIAL MATRIZ LTDA.</b></br>
		Rua Xyz, 100 - Centro</br>
		CEP 90000-000 - Porto Alegre - RS</br>
		CNPJ: 99.999.999/0001-01 - I.E.: 123.456.789</br>
		Telefone +55 (51) 11112222</br>
		www.empresa.com.br
		';	

	ELSE
	SELECT 
		'<b>' || IFNULL(T0."BPLName",'')|| '</b></br>'||
		IFNULL(T0."AddrType",'') || ' ' ||
		IFNULL(T0."Street",'') || ', ' ||
		IFNULL(T0."StreetNo",'') || ' ' || 
		IFNULL(T0."Building",'')|| ' - ' ||
		IFNULL(T0."Block",'') || '</br>' ||
		'CEP '|| IFNULL(T0."ZipCode",'') || ' - ' ||
		IFNULL(T0."City",'') || ' - ' ||
		IFNULL(T0."State",'') || '</br>' ||
		'CNPJ: ' || IFNULL(T0."TaxIdNum",'') || ' - ' ||
		'I.E.: ' || IFNULL(T0."TaxIdNum2",'') || '</br>' ||
		(SELECT 'Telefone ' || IFNULL("Phone1",'') || ' '|| IFNULL("Phone2",'') FROM "OADM") || '</br>' ||
		(SELECT IFNULL("IntrntAdrs",'') FROM ADM1) ||'</br>'		
		INTO DADOSEMPRESA
		FROM OBPL T0 
		WHERE T0."BPLId" = :BPLID;


	END IF;
	


SELECT 
(
SELECT 
TO_NVARCHAR(T0."LineNum"+1,'999') AS "td",
IFNULL(T0."ItemCode",'') AS "td",
IFNULL(T0."Dscription",'') || ' - ' || IFNULL(T0."FreeTxt",'') AS "td", 
IFNULl(T0."unitMsr",'') AS "td",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."Quantity",0),'999,999,990.000'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."PriceBefDi",0),'999,999,990.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."DiscPrcnt",0),'999,999,990.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."LineTotal"/T0."Quantity",0),'999,999,990.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."LineTotal",0),'999,999,990.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(ICMS."TaxRate",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(ST."TaxSum",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IPI."TaxRate",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IPI."TaxSum",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IFNULL(IPI."TaxSum",0)+IFNULL(ST."TaxSum",0)+T0."LineTotal",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
TO_NVARCHAR(T0."ShipDate", 'DD/MM/YYYY') AS "td align=right"
FROM QUT1 T0 
LEFT JOIN QUT4 ICMS ON ICMS."DocEntry" = T0."DocEntry" AND ICMS."LineNum" = T0."LineNum" AND ICMS."staType" = 10
LEFT JOIN QUT4 IPI ON IPI."DocEntry" = T0."DocEntry" AND IPI."LineNum" = T0."LineNum" AND IPI."staType" = 16
LEFT JOIN QUT4 ST ON ST."DocEntry" = T0."DocEntry" AND ST."LineNum" = T0."LineNum" AND ST."staType" = 13
WHERE T0."DocEntry" = :DocEntry FOR XML
)
INTO BODYROWS
FROM DUMMY;

SELECT
IFNULL((

SELECT 
CASE WHEN T0."TaxInPrice" = 'Y'
THEN IFNULL(T1."Name",'') 
ELSE IFNULL(T1."Name",'') || ' (+)' END AS "td", 
CASE WHEN T0."TaxInPrice" = 'N' 
THEN '' 
ELSE REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(SUM( T0."TaxSum"),'999,999,999.99'),'0.00'),',','@'),'.',','),'@','.')
 END AS "td align=right", 
CASE WHEN T0."TaxInPrice" = 'Y' 
THEN '' 
ELSE REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(SUM( T0."TaxSum"),'999,999,999.99'),'0.00'),',','@'),'.',','),'@','.')
END AS "td align=right" 
FROM QUT4 T0  
INNER JOIN OSTT T1 ON T0."staType" = T1."AbsId" 
WHERE T0."DocEntry" = :DocEntry 
GROUP BY T1."Name", T0."TaxInPrice" 
ORDER BY T0."TaxInPrice",1 FOR XML
),'<tr><td>SEM IMPOSTOS CALCULADOS</td><td align=right>0,00</td><td align=right>0,00</td></tr>')
INTO IMPOSTOS
FROM DUMMY;

END IF;



---------------------- PROCESSAMENTO DO HMTL

HEAD := 
'
<head>
	<title>
		'|| :SIGLA ||' - Núm. # '|| :NUMDOC ||'
	</title>
	<style type="text/css">

		body {
		font-family: Arial, Verdana, Geneva, sans-serif;
		font-size: 8pt;
		width: 700px;
		}

		table {
		border-collapse: collapse;
		border: none;
		font-family: Arial, Verdana, Geneva, sans-serif;
		font-size: 8pt;
		}

		.tabelasemborda{
			border-collapse: none;
			border: 1px solid white;
		}
		
		
		.tabelainfo{
			border: 1px solid white;
			border-left: 1px solid white;
		}

		thead {
			border: 1px solid black;
			font-family: Arial, Verdana, Geneva, sans-serif;
			font-size: 8pt;
		}

		tbody {
		    border: 1px solid black;    
		    font-family: Arial, Verdana, Geneva, sans-serif;
			font-size: 8pt;
		}
		
		.dadosPedido {
			border-left: 1px solid black;
		}
		
		
		.dadosCliente {
			border-top: 1px solid black;
		}

		.rodape {
			font-size: 6pt;
		}


	</style>
</head>
<body>

<table>
	<thead>
	<tr>
	<td width="200" height="120">
	<img src="http://crmonedemobrdouglas.dwu.com.br:8070/assets/img-project/logo_cliente_200x112px.png" width="200" height="112">
	</td>
	<td widht="290" height="120" colspan="4">
	<p align="left">
	' || :DADOSEMPRESA ||'
	</p>
	</td>
	
	<td width="200" height="120" class="dadosPedido">
	<b>
	' || :TIPODOC ||'
	</b></br>
	Núm. #: '|| :NUMDOC ||'</br>
	Data: '|| :DATADOC ||'</br>
	Ref. do cliente: '|| :NUMREFPN ||'</br>
	</br>
	</br>
	</td>
	</tr>

	<tr>
	<td colspan="6" class="dadosCliente">
	<b>DADOS DO CLIENTE</b></br></br>
	<b>' || :CODPN ||' - '|| :NOMEPN ||'</b></br>
	CNPJ/CPF: '|| :TAX0 || :TAX4 || ' - I.E.: '|| :TAX1 ||'</br>
	'|| :TIPORUA ||' '|| :NOMERUA || ', ' || :NUMRUA ||' '||:COMPRUA ||' - '|| :BAIRRO ||'</br>
	CEP '|| :CEP ||' - '|| :CIDADE || ' - '  || :ESTADO ||'</br>
	</br>
	Contato: ' || :PESSOACONTATO || '</br>
	Telefone(s): ' || :TEL1 || '   '|| :TEL2 ||'   '|| :TEL3 || '   ' || :TEL4 || '</br>
	E-mail: ' || :EMAIL || '
	</td>
	</tr>
	
</thead>

';
--INTO HEAD FROM DUMMY;

------- DADOS DO CORPO

BODY :=
'
<tbody>
<tr>
<td colspan="6" align=left>
</br>
<b>ITENS DO DOCUMENTO</b></br>
<table border="1">
<tbody>
<tr>
<th>#</th>
<th>Código</th>
<th>Descrição</th>
<th>Un. medida</th>
<th>Quant.</th>
<th>Preço unit.</th>
<th>% desc.</th>
<th>Vlr. c/ desc.</th>
<th>Vlr. total</th>
<th>% ICMS</th>
<th>Vlr. ST</th>
<th>% IPI</th>
<th>Vlr. IPI</br>% IPI</th>
<th>Vlr. Total + ST + IPI</th>
<th>Data entrega</th>
</tr>
'
|| REPLACE(REPLACE(REPLACE(REPLACE(:BODYROWS,'<resultset>',''),'</resultset>',''),'<row>','<tr>'),'</row>','</tr>') || 
'
</tbody>
</table>
</br>
</tbody>'
;



------- DADOS DO RODAPÉ
FEET := 
'
<tfeet>
<tr >

	<th align=left colspan="2">
		<b>INFORMAÇÕES GERAIS</b></br></br>
		
		<table>
			<tbody class="tabelasemborda">
			<tr>
				<td width="200">
				<b>Cond. pag.: </b>' || :CONDPAGTO || '</br>
				<b>Transp.: </b>' || :TRANSPORTADORA || '</br>
				<b>Tipo de frete: </b>' || :TIPOFRETE || '</br>
				<b>Vendedor: </b>' || :VENDEDOR ||'</br>
				</td>
				<td  align=left>
				<b>Peso líq: </b> ' || :PESOLIQUIDO || 'kg</br>
				<b>Peso Bruto: </b> ' || :PESOBRUTO || 'kg</br>
				</td>
			</tr>
			</tbody>
		</table>
	</th>
	<th>
	</th>
	<th rowspan="2">
	</th>
	<th colspan="2" rowspan="2"  align=left widht="300">
		<b>TOTAIS</b>
				<table border="2"  cellpadding="2" width="300">
					<tbody>
					<tr>
						<td widht="150" colspan="2">VALOR TOTAL DOS PRODUTOS (+)
						</td>
						<td width="75" align=right>
						'|| :TOTALPRODUTOS ||'
						</td>
					</tr>
		
						' 
						|| REPLACE(REPLACE(REPLACE(REPLACE(:IMPOSTOS,'<resultset>',''),'</resultset>',''),'<row>','<tr>'),'</row>','</tr>') ||
						'
					<tr>
						<td  colspan="2">FRETE/SEGURO/OUTROS (+)</td>
						<td align=right>
						' || :DESPESASADICIONAIS ||'
						</td>
					</tr>
					<tr>
						<td  colspan="2">DESCONTO FINANCEIRO (-)</td>
						<td align=right>
						'|| :DESCONTOTOTAL ||'
						</td>
					</tr>
					<tr>
						<td colspan="2"> 
						<b>VALOR TOTAL DO DOCUMENTO (=)</b>
						</td>
						<td align=right><b>
						' || :TOTALDOCUMENTO ||'
						</b>
						</td>
					</tr>
					</tbody>
				</table>
				</br>
				</br>
				</br>
				</br>
				</br>
	</th>
</tr>
<tr>
	<th colspan="3" align=left width="500">	
		<b>OBSERVAÇÕES</b></br></br>
		<table>
		<tbody class="tabelasemborda">
			<tr>
				<td height="50" align=left class="tabelasemborda">
				' || :OBSERVACOES ||'
				</td>
			</tr>
		</tbody>
		</table> 
		</br>
	</th>
</tr>

</tfeet>

</table>
<p class="rodape"><b>CRM One - Layout 1.0</b></p>
</body>
';




-------

 SELECT HEAD || BODY || FEET INTO HTML FROM DUMMY;

 
 END;

Layout Paisagem

CREATE FUNCTION CRMONE_LAYOUT_HTML
(
DocType VARCHAR(255),
DocEntry INT
)
RETURNS
HTML NCLOB
LANGUAGE SQLSCRIPT
AS
BEGIN


---- COMPOSIÇÃO HTML
DECLARE HEAD NCLOB;
DECLARE BODY NCLOB;
DECLARE BODYROWS NCLOB;
DECLARE IMPOSTOS NCLOB;
DECLARE FEET NCLOB;

---- DADOS EMPRESA
DECLARE DADOSEMPRESA NCLOB;

---- CABEÇALHO DOCUMENTO
DECLARE SIGLA NVARCHAR(20);
DECLARE TIPODOC NVARCHAR(250);
DECLARE NUMDOC INT;
DECLARE BPLID INT;
DECLARE DATADOC NVARCHAR(20);
DECLARE NUMREFPN NVARCHAR(100);
DECLARE CODPN NVARCHAR(50);
DECLARE NOMEPN NVARCHAR(200);
DECLARE TIPORUA NVARCHAR(250);
DECLARE NOMERUA NVARCHAR(250);
DECLARE NUMRUA NVARCHAR(250);
DECLARE COMPRUA NVARCHAR(250);
DECLARE BAIRRO NVARCHAR(250);
DECLARE CEP NVARCHAR(250);
DECLARE CIDADE NVARCHAR(250);
DECLARE ESTADO NVARCHAR(250);
DECLARE TAX0 NVARCHAR(250);
DECLARE TAX1 NVARCHAR(250);
DECLARE TAX4 NVARCHAR(250);
DECLARE TEL1 NVARCHAR(30);
DECLARE TEL2 NVARCHAR(30);
DECLARE TEL3 NVARCHAR(30);
DECLARE TEL4 NVARCHAR(30);
DECLARE EMAIL NVARCHAR(250);
DECLARE PESSOACONTATO NVARCHAR(200);

----- LINHAS DO DOCUMENTO
DECLARE NUMLINHA NVARCHAR(20);
DECLARE CODITEM NVARCHAR(100);
DECLARE DESCITEM NVARCHAR(200);
DECLARE TEXTOLIVRE NVARCHAR(200);
DECLARE QUANT NVARCHAR(30);
DECLARE PRECO NVARCHAR(30);
DECLARE PERCDESC NVARCHAR(30);
DECLARE PRECOCDESC NVARCHAR(30);
DECLARE VALORTOTAL NVARCHAR(30);
DECLARE DATAENTREGA NVARCHAR(30);

------- DADOS RODAPÉ
DECLARE TOTALPRODUTOS NVARCHAR(30);
DECLARE TOTALIMPOSTOSADICIONAIS NVARCHAR(30);
DECLARE TOTALDOCUMENTO NVARCHAR(30);
DECLARE DESPESASADICIONAIS NVARCHAR(30);
DECLARE DESCONTOTOTAL NVARCHAR(30);
DECLARE VENDEDOR NVARCHAR(100);
DECLARE CONDPAGTO NVARCHAR(100);
DECLARE TRANSPORTADORA NVARCHAR(200);
DECLARE TIPOFRETE NVARCHAR(100);
DECLARE OBSERVACOES NVARCHAR(254);
DECLARE PESOBRUTO NVARCHAR(20);
DECLARE PESOLIQUIDO NVARCHAR(20);


----- PROCESSAMENTO



IF DocType = '17'
THEN

SIGLA := 'P. V.';

SELECT 
'PEDIDO DE VENDAS', 
T0."DocNum", 
IFNULL(T0."BPLId",0), 
TO_VARCHAR( T0."DocDate", 'DD/MM/YYYY'),
IFNULL(T0."NumAtCard",''),
IFNULL(T0."CardCode",''),
IFNULL(T0."CardName",''),
IFNULL(T1."AddrTypeS",''), 
IFNULL(T1."StreetS",''), 
IFNULL(T1."StreetNoS",''), 
IFNULL(T1."BuildingS",''), 
IFNULL(T1."BlockS",''), 
IFNULL(T1."ZipCodeS",''),
IFNULL(T1."CityS",''),  
IFNULL(T1."StateS",''), 
IFNULL(T1."TaxId0",''), 
IFNULL(T1."TaxId1",''), 
IFNULL(TO_NVARCHAR(T1."TaxId4"),''),
IFNULL(T2."Phone1",''),
IFNULL(T2."Phone2",''),
IFNULL(T2."Fax",''),
IFNULL(T2."Cellular",''),
IFNULL(T2."E_Mail",''),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."DocTotal",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."VatSum",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."DocTotal",0)-IFNULL(T0."VatSum",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."TotalExpns",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."DiscSum",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
IFNULL(T3."SlpName",''),
IFNULL(T4."PymntGroup",''),
IFNULL(T5."CardName",''),
CASE 
WHEN T1."Incoterms" = '0' THEN 'Frete por conta do Remetente (CIF)'
WHEN T1."Incoterms" = '1' THEN 'Frete por conta do Destinatário (FOB)'
WHEN T1."Incoterms" = '2' THEN 'Frete por conta de Terceiros'
WHEN T1."Incoterms" = '3' THEN 'Transporte Próprio por conta do Remetente'
WHEN T1."Incoterms" = '4' THEN 'Transporte Próprio por conta do Destinatário'
WHEN T1."Incoterms" = '9' THEN 'Sem Ocorrência de Transporte'
ELSE 'Não informado' END,
IFNULL(T0."Comments",''),
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IFNULL(T1."GrsWeight",0),'999,999,990.000'),'0.000'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IFNULL(T1."NetWeight",0),'999,999,990.000'),'0.000'),',','@'),'.',','),'@','.'),
IFNULL(T6."Name",'') || ' - '|| IFNULL(T6."FirstName",'') || ' ' || IFNULL(T6."LastName",'') 
INTO TIPODOC, NUMDOC, BPLID, DATADOC, NUMREFPN, 
CODPN, NOMEPN,  TIPORUA, NOMERUA, NUMRUA, COMPRUA, BAIRRO, CEP, CIDADE, ESTADO, 
TAX0, TAX1, TAX4,
TEL1, TEL2, TEL3, TEL4, EMAIL,
TOTALDOCUMENTO, TOTALIMPOSTOSADICIONAIS, TOTALPRODUTOS, DESPESASADICIONAIS, DESCONTOTOTAL, 
VENDEDOR, CONDPAGTO, TRANSPORTADORA, TIPOFRETE, OBSERVACOES,
PESOBRUTO, PESOLIQUIDO,
PESSOACONTATO
FROM ORDR T0
INNER JOIN RDR12 T1 ON T0."DocEntry" = T1."DocEntry"
INNER JOIN OCRD T2 ON T0."CardCode" = T2."CardCode"
LEFT JOIN OSLP T3 ON T0."SlpCode" = T3."SlpCode"
INNER JOIN OCTG T4 ON T0."GroupNum" = T4."GroupNum"
LEFT JOIN OCRD T5 ON T1."Carrier" = T5."CardCode"
LEFT JOIN OCPR T6 ON T0."CntctCode" = T6."CntctCode" 
WHERE T0."DocEntry" = :DocEntry;

IF :BPLID = 0
	THEN
		
	DADOSEMPRESA := 
		'
		<b>RAZAO SOCIAL MATRIZ LTDA.</b></br>
		Rua Xyz, 100 - Centro</br>
		CEP 90000-000 - Porto Alegre - RS</br>
		CNPJ: 99.999.999/0001-01 - I.E.: 123.456.789</br>
		Telefone +55 (51) 11112222</br>
		www.empresa.com.br
		';	

	ELSE
	SELECT 
		'<b>' || IFNULL(T0."BPLName",'')|| '</b></br>'||
		IFNULL(T0."AddrType",'') || ' ' ||
		IFNULL(T0."Street",'') || ', ' ||
		IFNULL(T0."StreetNo",'') || ' ' || 
		IFNULL(T0."Building",'')|| ' - ' ||
		IFNULL(T0."Block",'') || '</br>' ||
		'CEP '|| IFNULL(T0."ZipCode",'') || ' - ' ||
		IFNULL(T0."City",'') || ' - ' ||
		IFNULL(T0."State",'') || '</br>' ||
		'CNPJ: ' || IFNULL(T0."TaxIdNum",'') || ' - ' ||
		'I.E.: ' || IFNULL(T0."TaxIdNum2",'') || '</br>' ||
		(SELECT 'Telefone ' || IFNULL("Phone1",'') || ' '|| IFNULL("Phone2",'') FROM "OADM") || '</br>' ||
		(SELECT IFNULL("IntrntAdrs",'') FROM ADM1) ||'</br>'		
		INTO DADOSEMPRESA
		FROM OBPL T0 
		WHERE T0."BPLId" = :BPLID;


	END IF;
	


SELECT 
(
SELECT 
TO_NVARCHAR(T0."LineNum"+1,'999') AS "td",
IFNULL(T0."ItemCode",'') AS "td",
IFNULL(T0."Dscription",'') || ' - ' || IFNULL(T0."FreeTxt",'') AS "td", 
IFNULl(T0."unitMsr",'') AS "td",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."Quantity",0),'999,999,990.000'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."PriceBefDi",0),'999,999,990.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."DiscPrcnt",0),'999,999,990.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."LineTotal"/T0."Quantity",0),'999,999,990.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."LineTotal",0),'999,999,990.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(ICMS."TaxRate",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(ST."TaxSum",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IPI."TaxRate",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IPI."TaxSum",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IFNULL(IPI."TaxSum",0)+IFNULL(ST."TaxSum",0)+T0."LineTotal",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
TO_NVARCHAR(T0."ShipDate", 'DD/MM/YYYY') AS "td align=right"
FROM RDR1 T0 
LEFT JOIN RDR4 ICMS ON ICMS."DocEntry" = T0."DocEntry" AND ICMS."LineNum" = T0."LineNum" AND ICMS."staType" = 10
LEFT JOIN RDR4 IPI ON IPI."DocEntry" = T0."DocEntry" AND IPI."LineNum" = T0."LineNum" AND IPI."staType" = 16
LEFT JOIN RDR4 ST ON ST."DocEntry" = T0."DocEntry" AND ST."LineNum" = T0."LineNum" AND ST."staType" = 13
WHERE T0."DocEntry" = :DocEntry FOR XML
)
INTO BODYROWS
FROM DUMMY;

SELECT
IFNULL((

SELECT 
CASE WHEN T0."TaxInPrice" = 'Y'
THEN IFNULL(T1."Name",'') 
ELSE IFNULL(T1."Name",'') || ' (+)' END AS "td", 
CASE WHEN T0."TaxInPrice" = 'N' 
THEN '' 
ELSE REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(SUM( T0."TaxSum"),'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.')
 END AS "td align=right", 
CASE WHEN T0."TaxInPrice" = 'Y' 
THEN '' 
ELSE REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(SUM( T0."TaxSum"),'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.')
END AS "td align=right" 
FROM RDR4 T0  
INNER JOIN OSTT T1 ON T0."staType" = T1."AbsId" 
WHERE T0."DocEntry" = :DocEntry 
GROUP BY T1."Name", T0."TaxInPrice" 
ORDER BY T0."TaxInPrice",1 FOR XML
),'<tr><td>SEM IMPOSTOS CALCULADOS</td><td align=right>0,00</td><td align=right>0,00</td></tr>')
INTO IMPOSTOS
FROM DUMMY;


------------------------- FIM PEDIDO DE VENDAS

ELSE

------------------------- INICIO COTAÇÃO DE VENDAS

SIGLA := 'C. V.';

SELECT 
'COTAÇÃO DE VENDAS', 
T0."DocNum", 
IFNULL(T0."BPLId",0), 
TO_VARCHAR( T0."DocDate", 'DD/MM/YYYY'),
IFNULL(T0."NumAtCard",''),
IFNULL(T0."CardCode",''),
IFNULL(T0."CardName",''),
IFNULL(T1."AddrTypeS",''), 
IFNULL(T1."StreetS",''), 
IFNULL(T1."StreetNoS",''), 
IFNULL(T1."BuildingS",''), 
IFNULL(T1."BlockS",''), 
IFNULL(T1."ZipCodeS",''),
IFNULL(T1."CityS",''),  
IFNULL(T1."StateS",''), 
IFNULL(T1."TaxId0",''), 
IFNULL(T1."TaxId1",''), 
IFNULL(TO_NVARCHAR(T1."TaxId4"),''),
IFNULL(T2."Phone1",''),
IFNULL(T2."Phone2",''),
IFNULL(T2."Fax",''),
IFNULL(T2."Cellular",''),
IFNULL(T2."E_Mail",''),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."DocTotal",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."VatSum",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."DocTotal",0)-IFNULL(T0."VatSum",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."TotalExpns",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."DiscSum",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
IFNULL(T3."SlpName",''),
IFNULL(T4."PymntGroup",''),
IFNULL(T5."CardName",''),
CASE 
WHEN T1."Incoterms" = '0' THEN 'Frete por conta do Remetente (CIF)'
WHEN T1."Incoterms" = '1' THEN 'Frete por conta do Destinatário (FOB)'
WHEN T1."Incoterms" = '2' THEN 'Frete por conta de Terceiros'
WHEN T1."Incoterms" = '3' THEN 'Transporte Próprio por conta do Remetente'
WHEN T1."Incoterms" = '4' THEN 'Transporte Próprio por conta do Destinatário'
WHEN T1."Incoterms" = '9' THEN 'Sem Ocorrência de Transporte'
ELSE 'Não informado' END,
IFNULL(T0."Comments",''),
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IFNULL(T1."GrsWeight",0),'999,999,990.000'),'0.000'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IFNULL(T1."NetWeight",0),'999,999,990.000'),'0.000'),',','@'),'.',','),'@','.'),
IFNULL(T6."Name",'') || ' - '|| IFNULL(T6."FirstName",'') || ' ' || IFNULL(T6."LastName",'') 
INTO TIPODOC, NUMDOC, BPLID, DATADOC, NUMREFPN, 
CODPN, NOMEPN,  TIPORUA, NOMERUA, NUMRUA, COMPRUA, BAIRRO, CEP, CIDADE, ESTADO, 
TAX0, TAX1, TAX4,
TEL1, TEL2, TEL3, TEL4, EMAIL,
TOTALDOCUMENTO, TOTALIMPOSTOSADICIONAIS, TOTALPRODUTOS, DESPESASADICIONAIS, DESCONTOTOTAL, 
VENDEDOR, CONDPAGTO, TRANSPORTADORA, TIPOFRETE, OBSERVACOES,
PESOBRUTO, PESOLIQUIDO,
PESSOACONTATO
FROM OQUT T0
INNER JOIN QUT12 T1 ON T0."DocEntry" = T1."DocEntry"
INNER JOIN OCRD T2 ON T0."CardCode" = T2."CardCode"
LEFT JOIN OSLP T3 ON T0."SlpCode" = T3."SlpCode"
INNER JOIN OCTG T4 ON T0."GroupNum" = T4."GroupNum"
LEFT JOIN OCRD T5 ON T1."Carrier" = T5."CardCode"
LEFT JOIN OCPR T6 ON T0."CntctCode" = T6."CntctCode" 
WHERE T0."DocEntry" = :DocEntry;

IF :BPLID = 0
	THEN
		
	DADOSEMPRESA := 
		'
		<b>RAZAO SOCIAL MATRIZ LTDA.</b></br>
		Rua Xyz, 100 - Centro</br>
		CEP 90000-000 - Porto Alegre - RS</br>
		CNPJ: 99.999.999/0001-01 - I.E.: 123.456.789</br>
		Telefone +55 (51) 11112222</br>
		www.empresa.com.br
		';	

	ELSE
	SELECT 
		'<b>' || IFNULL(T0."BPLName",'')|| '</b></br>'||
		IFNULL(T0."AddrType",'') || ' ' ||
		IFNULL(T0."Street",'') || ', ' ||
		IFNULL(T0."StreetNo",'') || ' ' || 
		IFNULL(T0."Building",'')|| ' - ' ||
		IFNULL(T0."Block",'') || '</br>' ||
		'CEP '|| IFNULL(T0."ZipCode",'') || ' - ' ||
		IFNULL(T0."City",'') || ' - ' ||
		IFNULL(T0."State",'') || '</br>' ||
		'CNPJ: ' || IFNULL(T0."TaxIdNum",'') || ' - ' ||
		'I.E.: ' || IFNULL(T0."TaxIdNum2",'') || '</br>' ||
		(SELECT 'Telefone ' || IFNULL("Phone1",'') || ' '|| IFNULL("Phone2",'') FROM "OADM") || '</br>' ||
		(SELECT IFNULL("IntrntAdrs",'') FROM ADM1) ||'</br>'		
		INTO DADOSEMPRESA
		FROM OBPL T0 
		WHERE T0."BPLId" = :BPLID;


	END IF;
	


SELECT 
(
SELECT 
TO_NVARCHAR(T0."LineNum"+1,'999') AS "td",
IFNULL(T0."ItemCode",'') AS "td",
IFNULL(T0."Dscription",'') || ' - ' || IFNULL(T0."FreeTxt",'') AS "td", 
IFNULl(T0."unitMsr",'') AS "td",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."Quantity",0),'999,999,990.000'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."PriceBefDi",0),'999,999,990.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."DiscPrcnt",0),'999,999,990.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."LineTotal"/T0."Quantity",0),'999,999,990.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."LineTotal",0),'999,999,990.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(ICMS."TaxRate",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(ST."TaxSum",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IPI."TaxRate",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IPI."TaxSum",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IFNULL(IPI."TaxSum",0)+IFNULL(ST."TaxSum",0)+T0."LineTotal",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
TO_NVARCHAR(T0."ShipDate", 'DD/MM/YYYY') AS "td align=right"
FROM QUT1 T0 
LEFT JOIN QUT4 ICMS ON ICMS."DocEntry" = T0."DocEntry" AND ICMS."LineNum" = T0."LineNum" AND ICMS."staType" = 10
LEFT JOIN QUT4 IPI ON IPI."DocEntry" = T0."DocEntry" AND IPI."LineNum" = T0."LineNum" AND IPI."staType" = 16
LEFT JOIN QUT4 ST ON ST."DocEntry" = T0."DocEntry" AND ST."LineNum" = T0."LineNum" AND ST."staType" = 13
WHERE T0."DocEntry" = :DocEntry FOR XML
)
INTO BODYROWS
FROM DUMMY;

SELECT
IFNULL((

SELECT 
CASE WHEN T0."TaxInPrice" = 'Y'
THEN IFNULL(T1."Name",'') 
ELSE IFNULL(T1."Name",'') || ' (+)' END AS "td", 
CASE WHEN T0."TaxInPrice" = 'N' 
THEN '' 
ELSE REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(SUM( T0."TaxSum"),'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.')
 END AS "td align=right", 
CASE WHEN T0."TaxInPrice" = 'Y' 
THEN '' 
ELSE REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(SUM( T0."TaxSum"),'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.')
END AS "td align=right" 
FROM QUT4 T0  
INNER JOIN OSTT T1 ON T0."staType" = T1."AbsId" 
WHERE T0."DocEntry" = :DocEntry 
GROUP BY T1."Name", T0."TaxInPrice" 
ORDER BY T0."TaxInPrice",1 FOR XML
),'<tr><td>SEM IMPOSTOS CALCULADOS</td><td align=right>0,00</td><td align=right>0,00</td></tr>')
INTO IMPOSTOS
FROM DUMMY;

END IF;



---------------------- PROCESSAMENTO DO HMTL

HEAD := 
'
<head>
	<title>
		'|| :SIGLA ||' - Núm. # '|| :NUMDOC ||'
	</title>
	<style type="text/css">

		body {
		font-family: Arial, Verdana, Geneva, sans-serif;
		font-size: 8pt;
		width: 1032px;
		}

		table {
		width: 1032px;
		border-collapse: collapse;
		border: none;
		font-family: Arial, Verdana, Geneva, sans-serif;
		font-size: 8pt;
		}
		
		thead {
			border: 1px solid black;
			font-family: Arial, Verdana, Geneva, sans-serif;
			font-size: 8pt;
		}
		
		tbody {
		    border: 1px solid black;    
		    font-family: Arial, Verdana, Geneva, sans-serif;
			font-size: 8pt;
		}
		
		
		.tabelatotais {
		width: 400px;
		border-collapse: collapse;
		border: none;
		font-family: Arial, Verdana, Geneva, sans-serif;
		font-size: 8pt;
		}

		.tabelasemborda{
			width: 400px;
			border-collapse: none;
			border: 1px solid white;
		}
		
		
		.tabelaInfoGeral{
			width: 400px;
			border: 1px solid white;
			border-top: 1px solid white
			border-left: 1px solid white;
		}
		
		.dadosEmpresa{
			border-right: 1px solid black
		}

		.dadosCliente {
			border-top: 1px solid black;
		}
		
		.dadosPedido {
			border-left: 1px solid black;
		}
		
		.rodape {
			font-size: 6pt;
		}


	</style>
</head>
<body>

<table>
	<thead>
	<tr>
	<td width="160" height="120">
	<img src="http://144.22.199.81:8098/assets/img-project/logo_cliente.png?1=1" width="150" height="84">
	</td>
	<td width="340" height="120" colspan="2" class="dadosEmpresa">
	<p align="left">
	' || :DADOSEMPRESA ||'
	</p>
	</td>
	<td width="380" colspan="2">
	<b>DADOS DO CLIENTE</b></br></br>
	<b>' || :CODPN ||' - '|| :NOMEPN ||'</b></br>
	CNPJ/CPF: '|| :TAX0 || :TAX4 || ' - I.E.: '|| :TAX1 ||'</br>
	'|| :TIPORUA ||' '|| :NOMERUA || ', ' || :NUMRUA ||' '||:COMPRUA ||' - '|| :BAIRRO ||'</br>
	CEP '|| :CEP ||' - '|| :CIDADE || ' - '  || :ESTADO ||'</br>
	</br>
	Contato: ' || :PESSOACONTATO || '</br>
	Telefone(s): ' || :TEL1 || '   '|| :TEL2 ||'   '|| :TEL3 || '   ' || :TEL4 || '</br>
	E-mail: ' || :EMAIL || '
	</td>	
	<td width="200" height="120" class="dadosPedido">
	<b>
	' || :TIPODOC ||'
	</b></br>
	Núm. #: '|| :NUMDOC ||'</br>
	Data: '|| :DATADOC ||'</br>
	Ref. do cliente: '|| :NUMREFPN ||'</br>
	</br>
	</br>
	</td>
	</tr>


</thead>

';


------- DADOS DO CORPO

BODY :=
'
<tbody>
<tr>
<td colspan="6" align=left>
</br>
<b>ITENS DO DOCUMENTO</b></br>
<table border="1">
<tr>
<th>#</th>
<th>Código</th>
<th widht="400">Descrição</th>
<th>Un. medida</th>
<th>Quant.</th>
<th>Preço unit.</th>
<th>% desc.</th>
<th>Vlr. c/ desc.</th>
<th>Vlr. total</th>
<th>% ICMS</th>
<th>Vlr. ST</th>
<th>% IPI</th>
<th>Vlr. IPI</br>% IPI</th>
<th>Vlr. Total + ST + IPI</th>
<th>Data entrega</th>
</tr>
'
|| REPLACE(REPLACE(REPLACE(REPLACE(:BODYROWS,'<resultset>',''),'</resultset>',''),'<row>','<tr>'),'</row>','</tr>') || 
'
</table>
</br>
</tbody>'
;



------- DADOS DO RODAPÉ
FEET := 
'
<tfeet>
<tr>

	<th align=left colspan="2" width="400">
		<b>INFORMAÇÕES GERAIS</b></br></br>
		
		<table class="tabelaInfoGeral">
			<tbody class="tabelaInfoGeral">
			<tr>
				<td>
				<b>Cond. pag.: </b>' || :CONDPAGTO || '</br>
				<b>Transp.: </b>' || :TRANSPORTADORA || '</br>
				<b>Tipo de frete: </b>' || :TIPOFRETE || '</br>
				<b>Vendedor: </b>' || :VENDEDOR ||'</br>
				</td>
				<td align=left>
				<b>Peso líq: </b> ' || :PESOLIQUIDO || 'kg</br>
				<b>Peso Bruto: </b> ' || :PESOBRUTO || 'kg</br>
				</td>
			</tr>
			</tbody>
		</table>
	</th>
	<th colspan="2">
	</th>
	<th colspan="2" rowspan="2"  align=left width="300">
		<b>TOTAIS</b>
				<table border="2"  cellpadding="2" class="tabelatotais">
					<tbody>
					<tr>
						<td widht="150" colspan="2">VALOR TOTAL DOS PRODUTOS (+)
						</td>
						<td width="75" align=right>
						'|| :TOTALPRODUTOS ||'
						</td>
					</tr>
		
						' 
						|| REPLACE(REPLACE(REPLACE(REPLACE(:IMPOSTOS,'<resultset>',''),'</resultset>',''),'<row>','<tr>'),'</row>','</tr>') ||
						'
					<tr>
						<td  colspan="2">FRETE/SEGURO/OUTROS (+)</td>
						<td align=right>
						' || :DESPESASADICIONAIS ||'
						</td>
					</tr>
					<tr>
						<td  colspan="2">DESCONTO FINANCEIRO (-)</td>
						<td align=right>
						'|| :DESCONTOTOTAL ||'
						</td>
					</tr>
					<tr>
						<td colspan="2"> 
						<b>VALOR TOTAL DO DOCUMENTO (=)</b>
						</td>
						<td align=right><b>
						' || :TOTALDOCUMENTO ||'
						</b>
						</td>
					</tr>
					</tbody>
				</table>
				</br>
				</br>
				</br>
				</br>
				</br>
	</th>
</tr>
<tr>
	<th colspan="3" align=left width="500">	
		<b>OBSERVAÇÕES</b></br></br>
		<table class="tabelasemborda">
		<tbody class="tabelasemborda">
			<tr>
				<td height="50" align=left class="tabelasemborda">
				' || :OBSERVACOES ||'
				</td>
			</tr>
		</tbody>
		</table> 
		</br>
	</th>
</tr>

</tfeet>

</table>
<p class="rodape"><b>CRM One - Layout 1.0</b></p>
</body>
';




-------

 SELECT HEAD || BODY || FEET INTO HTML FROM DUMMY;

 
 END;

Layout Retrato e Paisagem na mesma função

Se desejar, pode deixar no mesmo código layout para Retrato ou Paisagem

Utilizando a variável “ORIENTACAO” para definir se retrato ou paisagem

CREATE FUNCTION CRMONE_LAYOUT_HTML
(
DocType VARCHAR(255),
DocEntry INT
)
RETURNS
HTML NCLOB
LANGUAGE SQLSCRIPT
AS
BEGIN


DECLARE ORIENTACAO NVARCHAR(1);

---- COMPOSIÇÃO HTML
DECLARE HEAD NCLOB;
DECLARE BODY NCLOB;
DECLARE BODYROWS NCLOB;
DECLARE IMPOSTOS NCLOB;
DECLARE FEET NCLOB;

---- DADOS EMPRESA
DECLARE DADOSEMPRESA NCLOB;

---- CABEÇALHO DOCUMENTO
DECLARE SIGLA NVARCHAR(20);
DECLARE TIPODOC NVARCHAR(250);
DECLARE NUMDOC INT;
DECLARE BPLID INT;
DECLARE DATADOC NVARCHAR(20);
DECLARE NUMREFPN NVARCHAR(100);
DECLARE CODPN NVARCHAR(50);
DECLARE NOMEPN NVARCHAR(200);
DECLARE TIPORUA NVARCHAR(250);
DECLARE NOMERUA NVARCHAR(250);
DECLARE NUMRUA NVARCHAR(250);
DECLARE COMPRUA NVARCHAR(250);
DECLARE BAIRRO NVARCHAR(250);
DECLARE CEP NVARCHAR(250);
DECLARE CIDADE NVARCHAR(250);
DECLARE ESTADO NVARCHAR(250);
DECLARE TAX0 NVARCHAR(250);
DECLARE TAX1 NVARCHAR(250);
DECLARE TAX4 NVARCHAR(250);
DECLARE TEL1 NVARCHAR(30);
DECLARE TEL2 NVARCHAR(30);
DECLARE TEL3 NVARCHAR(30);
DECLARE TEL4 NVARCHAR(30);
DECLARE EMAIL NVARCHAR(250);
DECLARE PESSOACONTATO NVARCHAR(200);

----- LINHAS DO DOCUMENTO
DECLARE NUMLINHA NVARCHAR(20);
DECLARE CODITEM NVARCHAR(100);
DECLARE DESCITEM NVARCHAR(200);
DECLARE TEXTOLIVRE NVARCHAR(200);
DECLARE QUANT NVARCHAR(30);
DECLARE PRECO NVARCHAR(30);
DECLARE PERCDESC NVARCHAR(30);
DECLARE PRECOCDESC NVARCHAR(30);
DECLARE VALORTOTAL NVARCHAR(30);
DECLARE DATAENTREGA NVARCHAR(30);

------- DADOS RODAPÉ
DECLARE TOTALPRODUTOS NVARCHAR(30);
DECLARE TOTALIMPOSTOSADICIONAIS NVARCHAR(30);
DECLARE TOTALDOCUMENTO NVARCHAR(30);
DECLARE DESPESASADICIONAIS NVARCHAR(30);
DECLARE DESCONTOTOTAL NVARCHAR(30);
DECLARE VENDEDOR NVARCHAR(100);
DECLARE CONDPAGTO NVARCHAR(100);
DECLARE TRANSPORTADORA NVARCHAR(200);
DECLARE TIPOFRETE NVARCHAR(100);
DECLARE OBSERVACOES NVARCHAR(254);
DECLARE PESOBRUTO NVARCHAR(20);
DECLARE PESOLIQUIDO NVARCHAR(20);


----- PROCESSAMENTO

--- ORIENTACAO R = RETRATO OU P = PAISAGEM
ORIENTACAO := 'R'; 



IF DocType = '17'
THEN

SIGLA := 'P. V.';

SELECT 
'PEDIDO DE VENDAS', 
T0."DocNum", 
IFNULL(T0."BPLId",0), 
TO_VARCHAR( T0."DocDate", 'DD/MM/YYYY'),
IFNULL(T0."NumAtCard",''),
IFNULL(T0."CardCode",''),
IFNULL(T0."CardName",''),
IFNULL(T1."AddrTypeS",''), 
IFNULL(T1."StreetS",''), 
IFNULL(T1."StreetNoS",''), 
IFNULL(T1."BuildingS",''), 
IFNULL(T1."BlockS",''), 
IFNULL(T1."ZipCodeS",''),
IFNULL(T1."CityS",''),  
IFNULL(T1."StateS",''), 
IFNULL(T1."TaxId0",''), 
IFNULL(T1."TaxId1",''), 
IFNULL(TO_NVARCHAR(T1."TaxId4"),''),
IFNULL(T2."Phone1",''),
IFNULL(T2."Phone2",''),
IFNULL(T2."Fax",''),
IFNULL(T2."Cellular",''),
IFNULL(T2."E_Mail",''),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."DocTotal",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."VatSum",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."DocTotal",0)-IFNULL(T0."VatSum",0)-IFNULL(T0."TotalExpns",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."TotalExpns",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."DiscSum",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
IFNULL(T3."SlpName",''),
IFNULL(T4."PymntGroup",''),
IFNULL(T5."CardName",''),
CASE 
WHEN T1."Incoterms" = '0' THEN 'Frete por conta do Remetente (CIF)'
WHEN T1."Incoterms" = '1' THEN 'Frete por conta do Destinatário (FOB)'
WHEN T1."Incoterms" = '2' THEN 'Frete por conta de Terceiros'
WHEN T1."Incoterms" = '3' THEN 'Transporte Próprio por conta do Remetente'
WHEN T1."Incoterms" = '4' THEN 'Transporte Próprio por conta do Destinatário'
WHEN T1."Incoterms" = '9' THEN 'Sem Ocorrência de Transporte'
ELSE 'Não informado' END,
IFNULL(T0."Comments",''),
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IFNULL(T1."GrsWeight",0),'999,999,990.000'),'0.000'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IFNULL(T1."NetWeight",0),'999,999,990.000'),'0.000'),',','@'),'.',','),'@','.'),
IFNULL(T6."Name",'') || ' - '|| IFNULL(T6."FirstName",'') || ' ' || IFNULL(T6."LastName",'') 
INTO TIPODOC, NUMDOC, BPLID, DATADOC, NUMREFPN, 
CODPN, NOMEPN,  TIPORUA, NOMERUA, NUMRUA, COMPRUA, BAIRRO, CEP, CIDADE, ESTADO, 
TAX0, TAX1, TAX4,
TEL1, TEL2, TEL3, TEL4, EMAIL,
TOTALDOCUMENTO, TOTALIMPOSTOSADICIONAIS, TOTALPRODUTOS, DESPESASADICIONAIS, DESCONTOTOTAL, 
VENDEDOR, CONDPAGTO, TRANSPORTADORA, TIPOFRETE, OBSERVACOES,
PESOBRUTO, PESOLIQUIDO,
PESSOACONTATO
FROM ORDR T0
INNER JOIN RDR12 T1 ON T0."DocEntry" = T1."DocEntry"
INNER JOIN OCRD T2 ON T0."CardCode" = T2."CardCode"
LEFT JOIN OSLP T3 ON T0."SlpCode" = T3."SlpCode"
INNER JOIN OCTG T4 ON T0."GroupNum" = T4."GroupNum"
LEFT JOIN OCRD T5 ON T1."Carrier" = T5."CardCode"
LEFT JOIN OCPR T6 ON T0."CntctCode" = T6."CntctCode" 
WHERE T0."DocEntry" = :DocEntry;

IF :BPLID = 0
	THEN
		
	DADOSEMPRESA := 
		'
		<b>RAZAO SOCIAL MATRIZ LTDA.</b></br>
		Rua Xyz, 100 - Centro</br>
		CEP 90000-000 - Porto Alegre - RS</br>
		CNPJ: 99.999.999/0001-01 - I.E.: 123.456.789</br>
		Telefone +55 (51) 11112222</br>
		www.empresa.com.br
		';	

	ELSE
	SELECT 
		'<b>' || IFNULL(T0."BPLName",'')|| '</b></br>'||
		IFNULL(T0."AddrType",'') || ' ' ||
		IFNULL(T0."Street",'') || ', ' ||
		IFNULL(T0."StreetNo",'') || ' ' || 
		IFNULL(T0."Building",'')|| ' - ' ||
		IFNULL(T0."Block",'') || '</br>' ||
		'CEP '|| IFNULL(T0."ZipCode",'') || ' - ' ||
		IFNULL(T0."City",'') || ' - ' ||
		IFNULL(T0."State",'') || '</br>' ||
		'CNPJ: ' || IFNULL(T0."TaxIdNum",'') || ' - ' ||
		'I.E.: ' || IFNULL(T0."TaxIdNum2",'') || '</br>' ||
		(SELECT 'Telefone ' || IFNULL("Phone1",'') || ' '|| IFNULL("Phone2",'') FROM "OADM") || '</br>' ||
		(SELECT IFNULL("IntrntAdrs",'') FROM ADM1) ||'</br>'		
		INTO DADOSEMPRESA
		FROM OBPL T0 
		WHERE T0."BPLId" = :BPLID;


	END IF;
	


SELECT 
(
SELECT 
TO_NVARCHAR(T0."LineNum"+1,'999') AS "td",
IFNULL(T0."ItemCode",'') AS "td",
IFNULL(T0."Dscription",'') || ' - ' || IFNULL(T0."FreeTxt",'') AS "td", 
IFNULl(T0."unitMsr",'') AS "td",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."Quantity",0),'999,999,990.000'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."PriceBefDi",0),'999,999,990.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."DiscPrcnt",0),'999,999,990.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."LineTotal"/T0."Quantity",0),'999,999,990.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."LineTotal",0),'999,999,990.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(ICMS."TaxRate",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(ST."TaxSum",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IPI."TaxRate",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IPI."TaxSum",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IFNULL(IPI."TaxSum",0)+IFNULL(ST."TaxSum",0)+T0."LineTotal",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
TO_NVARCHAR(T0."ShipDate", 'DD/MM/YYYY') AS "td align=right"
FROM RDR1 T0 
LEFT JOIN RDR4 ICMS ON ICMS."DocEntry" = T0."DocEntry" AND ICMS."LineNum" = T0."LineNum" AND ICMS."staType" = 10  AND ICMS."RelateType" = 1
LEFT JOIN RDR4 IPI ON IPI."DocEntry" = T0."DocEntry" AND IPI."LineNum" = T0."LineNum" AND IPI."staType" = 16  AND IPI."RelateType" = 1
LEFT JOIN RDR4 ST ON ST."DocEntry" = T0."DocEntry" AND ST."LineNum" = T0."LineNum" AND ST."staType" = 13  AND ST."RelateType" = 1
WHERE T0."DocEntry" = :DocEntry FOR XML
)
INTO BODYROWS
FROM DUMMY;

SELECT
IFNULL((

SELECT 
CASE WHEN T0."TaxInPrice" = 'Y'
THEN IFNULL(T1."Name",'') 
ELSE IFNULL(T1."Name",'') || ' (+)' END AS "td", 
CASE WHEN T0."TaxInPrice" = 'N' 
THEN '' 
ELSE REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(SUM( T0."TaxSum"),'999,999,999.99'),'0.00'),',','@'),'.',','),'@','.')
 END AS "td align=right", 
CASE WHEN T0."TaxInPrice" = 'Y' 
THEN '' 
ELSE REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(SUM( T0."TaxSum"),'999,999,999.99'),'0.00'),',','@'),'.',','),'@','.')
END AS "td align=right" 
FROM RDR4 T0  
INNER JOIN OSTT T1 ON T0."staType" = T1."AbsId" 
WHERE T0."DocEntry" = :DocEntry 
GROUP BY T1."Name", T0."TaxInPrice" 
ORDER BY T0."TaxInPrice",1 FOR XML
),'<tr><td>SEM IMPOSTOS CALCULADOS</td><td align=right>0,00</td><td align=right>0,00</td></tr>')
INTO IMPOSTOS
FROM DUMMY;


------------------------- FIM PEDIDO DE VENDAS

ELSE

------------------------- INICIO COTAÇÃO DE VENDAS

SIGLA := 'C. V.';

SELECT 
'COTAÇÃO DE VENDAS', 
T0."DocNum", 
IFNULL(T0."BPLId",0), 
TO_VARCHAR( T0."DocDate", 'DD/MM/YYYY'),
IFNULL(T0."NumAtCard",''),
IFNULL(T0."CardCode",''),
IFNULL(T0."CardName",''),
IFNULL(T1."AddrTypeS",''), 
IFNULL(T1."StreetS",''), 
IFNULL(T1."StreetNoS",''), 
IFNULL(T1."BuildingS",''), 
IFNULL(T1."BlockS",''), 
IFNULL(T1."ZipCodeS",''),
IFNULL(T1."CityS",''),  
IFNULL(T1."StateS",''), 
IFNULL(T1."TaxId0",''), 
IFNULL(T1."TaxId1",''), 
IFNULL(TO_NVARCHAR(T1."TaxId4"),''),
IFNULL(T2."Phone1",''),
IFNULL(T2."Phone2",''),
IFNULL(T2."Fax",''),
IFNULL(T2."Cellular",''),
IFNULL(T2."E_Mail",''),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."DocTotal",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."VatSum",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."DocTotal",0)-IFNULL(T0."VatSum",0)-IFNULL(T0."TotalExpns",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."TotalExpns",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."DiscSum",0),'999,999,990.00'),',','@'),'.',','),'@','.'),
IFNULL(T3."SlpName",''),
IFNULL(T4."PymntGroup",''),
IFNULL(T5."CardName",''),
CASE 
WHEN T1."Incoterms" = '0' THEN 'Frete por conta do Remetente (CIF)'
WHEN T1."Incoterms" = '1' THEN 'Frete por conta do Destinatário (FOB)'
WHEN T1."Incoterms" = '2' THEN 'Frete por conta de Terceiros'
WHEN T1."Incoterms" = '3' THEN 'Transporte Próprio por conta do Remetente'
WHEN T1."Incoterms" = '4' THEN 'Transporte Próprio por conta do Destinatário'
WHEN T1."Incoterms" = '9' THEN 'Sem Ocorrência de Transporte'
ELSE 'Não informado' END,
IFNULL(T0."Comments",''),
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IFNULL(T1."GrsWeight",0),'999,999,990.000'),'0.000'),',','@'),'.',','),'@','.'),
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IFNULL(T1."NetWeight",0),'999,999,990.000'),'0.000'),',','@'),'.',','),'@','.'),
IFNULL(T6."Name",'') || ' - '|| IFNULL(T6."FirstName",'') || ' ' || IFNULL(T6."LastName",'') 
INTO TIPODOC, NUMDOC, BPLID, DATADOC, NUMREFPN, 
CODPN, NOMEPN,  TIPORUA, NOMERUA, NUMRUA, COMPRUA, BAIRRO, CEP, CIDADE, ESTADO, 
TAX0, TAX1, TAX4,
TEL1, TEL2, TEL3, TEL4, EMAIL,
TOTALDOCUMENTO, TOTALIMPOSTOSADICIONAIS, TOTALPRODUTOS, DESPESASADICIONAIS, DESCONTOTOTAL, 
VENDEDOR, CONDPAGTO, TRANSPORTADORA, TIPOFRETE, OBSERVACOES,
PESOBRUTO, PESOLIQUIDO,
PESSOACONTATO
FROM OQUT T0
INNER JOIN QUT12 T1 ON T0."DocEntry" = T1."DocEntry"
INNER JOIN OCRD T2 ON T0."CardCode" = T2."CardCode"
LEFT JOIN OSLP T3 ON T0."SlpCode" = T3."SlpCode"
INNER JOIN OCTG T4 ON T0."GroupNum" = T4."GroupNum"
LEFT JOIN OCRD T5 ON T1."Carrier" = T5."CardCode"
LEFT JOIN OCPR T6 ON T0."CntctCode" = T6."CntctCode" 
WHERE T0."DocEntry" = :DocEntry;

IF :BPLID = 0
	THEN
		
	DADOSEMPRESA := 
		'
		<b>RAZAO SOCIAL MATRIZ LTDA.</b></br>
		Rua Xyz, 100 - Centro</br>
		CEP 90000-000 - Porto Alegre - RS</br>
		CNPJ: 99.999.999/0001-01 - I.E.: 123.456.789</br>
		Telefone +55 (51) 11112222</br>
		www.empresa.com.br
		';	

	ELSE
	SELECT 
		'<b>' || IFNULL(T0."BPLName",'')|| '</b></br>'||
		IFNULL(T0."AddrType",'') || ' ' ||
		IFNULL(T0."Street",'') || ', ' ||
		IFNULL(T0."StreetNo",'') || ' ' || 
		IFNULL(T0."Building",'')|| ' - ' ||
		IFNULL(T0."Block",'') || '</br>' ||
		'CEP '|| IFNULL(T0."ZipCode",'') || ' - ' ||
		IFNULL(T0."City",'') || ' - ' ||
		IFNULL(T0."State",'') || '</br>' ||
		'CNPJ: ' || IFNULL(T0."TaxIdNum",'') || ' - ' ||
		'I.E.: ' || IFNULL(T0."TaxIdNum2",'') || '</br>' ||
		(SELECT 'Telefone ' || IFNULL("Phone1",'') || ' '|| IFNULL("Phone2",'') FROM "OADM") || '</br>' ||
		(SELECT IFNULL("IntrntAdrs",'') FROM ADM1) ||'</br>'		
		INTO DADOSEMPRESA
		FROM OBPL T0 
		WHERE T0."BPLId" = :BPLID;


	END IF;
	


SELECT 
(
SELECT 
TO_NVARCHAR(T0."LineNum"+1,'999') AS "td",
IFNULL(T0."ItemCode",'') AS "td",
IFNULL(T0."Dscription",'') || ' - ' || IFNULL(T0."FreeTxt",'') AS "td", 
IFNULl(T0."unitMsr",'') AS "td",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."Quantity",0),'999,999,990.000'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."PriceBefDi",0),'999,999,990.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."DiscPrcnt",0),'999,999,990.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."LineTotal"/T0."Quantity",0),'999,999,990.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(TO_NVARCHAR(IFNULL(T0."LineTotal",0),'999,999,990.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(ICMS."TaxRate",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(ST."TaxSum",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IPI."TaxRate",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IPI."TaxSum",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(IFNULL(IPI."TaxSum",0)+IFNULL(ST."TaxSum",0)+T0."LineTotal",'999,999,990.00'),'0.00'),',','@'),'.',','),'@','.') AS "td align=right",
TO_NVARCHAR(T0."ShipDate", 'DD/MM/YYYY') AS "td align=right"
FROM QUT1 T0 
LEFT JOIN QUT4 ICMS ON ICMS."DocEntry" = T0."DocEntry" AND ICMS."LineNum" = T0."LineNum" AND ICMS."staType" = 10 AND ICMS."RelateType" = 1
LEFT JOIN QUT4 IPI ON IPI."DocEntry" = T0."DocEntry" AND IPI."LineNum" = T0."LineNum" AND IPI."staType" = 16  AND IPI."RelateType" = 1
LEFT JOIN QUT4 ST ON ST."DocEntry" = T0."DocEntry" AND ST."LineNum" = T0."LineNum" AND ST."staType" = 13  AND ST."RelateType" = 1
WHERE T0."DocEntry" = :DocEntry FOR XML
)
INTO BODYROWS
FROM DUMMY;

SELECT
IFNULL((

SELECT 
CASE WHEN T0."TaxInPrice" = 'Y'
THEN IFNULL(T1."Name",'') 
ELSE IFNULL(T1."Name",'') || ' (+)' END AS "td", 
CASE WHEN T0."TaxInPrice" = 'N' 
THEN '' 
ELSE REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(SUM( T0."TaxSum"),'999,999,999.99'),'0.00'),',','@'),'.',','),'@','.')
 END AS "td align=right", 
CASE WHEN T0."TaxInPrice" = 'Y' 
THEN '' 
ELSE REPLACE(REPLACE(REPLACE(IFNULL(TO_NVARCHAR(SUM( T0."TaxSum"),'999,999,999.99'),'0.00'),',','@'),'.',','),'@','.')
END AS "td align=right" 
FROM QUT4 T0  
INNER JOIN OSTT T1 ON T0."staType" = T1."AbsId" 
WHERE T0."DocEntry" = :DocEntry 
GROUP BY T1."Name", T0."TaxInPrice" 
ORDER BY T0."TaxInPrice",1 FOR XML
),'<tr><td>SEM IMPOSTOS CALCULADOS</td><td align=right>0,00</td><td align=right>0,00</td></tr>')
INTO IMPOSTOS
FROM DUMMY;

END IF;







---------------------- PROCESSAMENTO DO HMTL

IF ORIENTACAO = 'R'
THEN

-- PROCESSAMENTO DO HMTL RETRATO

HEAD := 
'
<head>
	<title>
		'|| :SIGLA ||' - Núm. # '|| :NUMDOC ||'
	</title>
	<style type="text/css">

		body {
		font-family: Arial, Verdana, Geneva, sans-serif;
		font-size: 8pt;
		width: 700px;
		}

		table {
		border-collapse: collapse;
		border: none;
		font-family: Arial, Verdana, Geneva, sans-serif;
		font-size: 8pt;
		}

		.tabelasemborda{
			border-collapse: none;
			border: 1px solid white;
		}
		
		
		.tabelainfo{
			border: 1px solid white;
			border-left: 1px solid white;
		}

		thead {
			border: 1px solid black;
			font-family: Arial, Verdana, Geneva, sans-serif;
			font-size: 8pt;
		}

		tbody {
		    border: 1px solid black;    
		    font-family: Arial, Verdana, Geneva, sans-serif;
			font-size: 8pt;
		}
		
		.dadosPedido {
			border-left: 1px solid black;
		}
		
		
		.dadosCliente {
			border-top: 1px solid black;
		}

		.rodape {
			font-size: 6pt;
		}


	</style>
</head>
<body>

<table>
	<thead>
	<tr>
	<td width="200" height="120">
	<img src="https://www.dwu.com.br/wp-content/uploads/2020/11/cropped-cropped-Preferencial-3.png" width="160" height="90">
	</td>
	<td widht="290" height="120" colspan="4">
	<p align="left">
	' || :DADOSEMPRESA ||'
	</p>
	</td>
	
	<td width="200" height="120" class="dadosPedido">
	<b>
	' || :TIPODOC ||'
	</b></br>
	Núm. #: '|| :NUMDOC ||'</br>
	Data: '|| :DATADOC ||'</br>
	Ref. do cliente: '|| :NUMREFPN ||'</br>
	</br>
	</br>
	</td>
	</tr>

	<tr>
	<td colspan="6" class="dadosCliente">
	<b>DADOS DO CLIENTE</b></br></br>
	<b>' || :CODPN ||' - '|| :NOMEPN ||'</b></br>
	CNPJ/CPF: '|| :TAX0 || :TAX4 || ' - I.E.: '|| :TAX1 ||'</br>
	'|| :TIPORUA ||' '|| :NOMERUA || ', ' || :NUMRUA ||' '||:COMPRUA ||' - '|| :BAIRRO ||'</br>
	CEP '|| :CEP ||' - '|| :CIDADE || ' - '  || :ESTADO ||'</br>
	</br>
	Contato: ' || :PESSOACONTATO || '</br>
	Telefone(s): ' || :TEL1 || '   '|| :TEL2 ||'   '|| :TEL3 || '   ' || :TEL4 || '</br>
	E-mail: ' || :EMAIL || '
	</td>
	</tr>
	
</thead>

';
--INTO HEAD FROM DUMMY;

------- DADOS DO CORPO

BODY :=
'
<tbody>
<tr>
<td colspan="6" align=left>
</br>
<b>ITENS DO DOCUMENTO</b></br>
<table border="1">
<tbody>
<tr>
<th>#</th>
<th>Código</th>
<th>Descrição</th>
<th>Un. medida</th>
<th>Quant.</th>
<th>Preço unit.</th>
<th>% desc.</th>
<th>Vlr. c/ desc.</th>
<th>Vlr. total</th>
<th>% ICMS</th>
<th>Vlr. ST</th>
<th>% IPI</th>
<th>Vlr. IPI</br>% IPI</th>
<th>Vlr. Total + ST + IPI</th>
<th>Data entrega</th>
</tr>
'
|| REPLACE(REPLACE(REPLACE(REPLACE(:BODYROWS,'<resultset>',''),'</resultset>',''),'<row>','<tr>'),'</row>','</tr>') || 
'
</tbody>
</table>
</br>
</tbody>'
;



------- DADOS DO RODAPÉ
FEET := 
'
<tfeet>
<tr >

	<th align=left colspan="2">
		<b>INFORMAÇÕES GERAIS</b></br></br>
		
		<table>
			<tbody class="tabelasemborda">
			<tr>
				<td width="200">
				<b>Cond. pag.: </b>' || :CONDPAGTO || '</br>
				<b>Transp.: </b>' || :TRANSPORTADORA || '</br>
				<b>Tipo de frete: </b>' || :TIPOFRETE || '</br>
				<b>Vendedor: </b>' || :VENDEDOR ||'</br>
				</td>
				<td  align=left>
				<b>Peso líq: </b> ' || :PESOLIQUIDO || 'kg</br>
				<b>Peso Bruto: </b> ' || :PESOBRUTO || 'kg</br>
				</td>
			</tr>
			</tbody>
		</table>
	</th>
	<th>
	</th>
	<th rowspan="2">
	</th>
	<th colspan="2" rowspan="2"  align=left widht="300">
		<b>TOTAIS</b>
				<table border="2"  cellpadding="2" width="300">
					<tbody>
					<tr>
						<td widht="150" colspan="2">VALOR TOTAL DOS PRODUTOS (+)
						</td>
						<td width="75" align=right>
						'|| :TOTALPRODUTOS ||'
						</td>
					</tr>
		
						' 
						|| REPLACE(REPLACE(REPLACE(REPLACE(:IMPOSTOS,'<resultset>',''),'</resultset>',''),'<row>','<tr>'),'</row>','</tr>') ||
						'
					<tr>
						<td  colspan="2">FRETE/SEGURO/OUTROS (+)</td>
						<td align=right>
						' || :DESPESASADICIONAIS ||'
						</td>
					</tr>
					<tr>
						<td  colspan="2">DESCONTO FINANCEIRO (-)</td>
						<td align=right>
						'|| :DESCONTOTOTAL ||'
						</td>
					</tr>
					<tr>
						<td colspan="2"> 
						<b>VALOR TOTAL DO DOCUMENTO (=)</b>
						</td>
						<td align=right><b>
						' || :TOTALDOCUMENTO ||'
						</b>
						</td>
					</tr>
					</tbody>
				</table>
				</br>
				</br>
				</br>
				</br>
				</br>
	</th>
</tr>
<tr>
	<th colspan="3" align=left width="500">	
		<b>OBSERVAÇÕES</b></br></br>
		<table>
		<tbody class="tabelasemborda">
			<tr>
				<td height="50" align=left class="tabelasemborda">
				' || :OBSERVACOES ||'
				</td>
			</tr>
		</tbody>
		</table> 
		</br>
	</th>
</tr>

</tfeet>

</table>
<p class="rodape"><b>CRM One - Layout 1.0</b></p>
</body>
';

ELSE 

-- PROCESSAMENTO DO HMTL PAISAGEM

HEAD := 
'
<head>
	<title>
		'|| :SIGLA ||' - Núm. # '|| :NUMDOC ||'
	</title>
	<style type="text/css">

		body {
		font-family: Arial, Verdana, Geneva, sans-serif;
		font-size: 8pt;
		width: 1032px;
		}

		table {
		width: 1032px;
		border-collapse: collapse;
		border: none;
		font-family: Arial, Verdana, Geneva, sans-serif;
		font-size: 8pt;
		}
		
		thead {
			border: 1px solid black;
			font-family: Arial, Verdana, Geneva, sans-serif;
			font-size: 8pt;
		}
		
		tbody {
		    border: 1px solid black;    
		    font-family: Arial, Verdana, Geneva, sans-serif;
			font-size: 8pt;
		}
		
		
		.tabelatotais {
		width: 400px;
		border-collapse: collapse;
		border: none;
		font-family: Arial, Verdana, Geneva, sans-serif;
		font-size: 8pt;
		}

		.tabelasemborda{
			width: 400px;
			border-collapse: none;
			border: 1px solid white;
		}
		
		
		.tabelaInfoGeral{
			width: 400px;
			border: 1px solid white;
			border-top: 1px solid white
			border-left: 1px solid white;
		}
		
		.dadosEmpresa{
			border-right: 1px solid black
		}

		.dadosCliente {
			border-top: 1px solid black;
		}
		
		.dadosPedido {
			border-left: 1px solid black;
		}
		
		.rodape {
			font-size: 6pt;
		}


	</style>
</head>
<body>

<table>
	<thead>
	<tr>
	<td width="160" height="120">
	<img src="https://www.dwu.com.br/wp-content/uploads/2020/11/cropped-cropped-Preferencial-3.png" width="160" height="90">
	</td>
	<td width="340" height="120" colspan="2" class="dadosEmpresa">
	<p align="left">
	' || :DADOSEMPRESA ||'
	</p>
	</td>
	<td width="380" colspan="2">
	<b>DADOS DO CLIENTE</b></br></br>
	<b>' || :CODPN ||' - '|| :NOMEPN ||'</b></br>
	CNPJ/CPF: '|| :TAX0 || :TAX4 || ' - I.E.: '|| :TAX1 ||'</br>
	'|| :TIPORUA ||' '|| :NOMERUA || ', ' || :NUMRUA ||' '||:COMPRUA ||' - '|| :BAIRRO ||'</br>
	CEP '|| :CEP ||' - '|| :CIDADE || ' - '  || :ESTADO ||'</br>
	</br>
	Contato: ' || :PESSOACONTATO || '</br>
	Telefone(s): ' || :TEL1 || '   '|| :TEL2 ||'   '|| :TEL3 || '   ' || :TEL4 || '</br>
	E-mail: ' || :EMAIL || '
	</td>	
	<td width="200" height="120" class="dadosPedido">
	<b>
	' || :TIPODOC ||'
	</b></br>
	Núm. #: '|| :NUMDOC ||'</br>
	Data: '|| :DATADOC ||'</br>
	Ref. do cliente: '|| :NUMREFPN ||'</br>
	</br>
	</br>
	</td>
	</tr>


</thead>

';


------- DADOS DO CORPO

BODY :=
'
<tbody>
<tr>
<td colspan="6" align=left>
</br>
<b>ITENS DO DOCUMENTO</b></br>
<table border="1">
<tr>
<th>#</th>
<th>Código</th>
<th widht="400">Descrição</th>
<th>Un. medida</th>
<th>Quant.</th>
<th>Preço unit.</th>
<th>% desc.</th>
<th>Vlr. c/ desc.</th>
<th>Vlr. total</th>
<th>% ICMS</th>
<th>Vlr. ST</th>
<th>% IPI</th>
<th>Vlr. IPI</br>% IPI</th>
<th>Vlr. Total + ST + IPI</th>
<th>Data entrega</th>
</tr>
'
|| REPLACE(REPLACE(REPLACE(REPLACE(:BODYROWS,'<resultset>',''),'</resultset>',''),'<row>','<tr>'),'</row>','</tr>') || 
'
</table>
</br>
</tbody>'
;



------- DADOS DO RODAPÉ
FEET := 
'
<tfeet>
<tr>

	<th align=left colspan="2" width="400">
		<b>INFORMAÇÕES GERAIS</b></br></br>
		
		<table class="tabelaInfoGeral">
			<tbody class="tabelaInfoGeral">
			<tr>
				<td>
				<b>Cond. pag.: </b>' || :CONDPAGTO || '</br>
				<b>Transp.: </b>' || :TRANSPORTADORA || '</br>
				<b>Tipo de frete: </b>' || :TIPOFRETE || '</br>
				<b>Vendedor: </b>' || :VENDEDOR ||'</br>
				</td>
				<td align=left>
				<b>Peso líq: </b> ' || :PESOLIQUIDO || 'kg</br>
				<b>Peso Bruto: </b> ' || :PESOBRUTO || 'kg</br>
				</td>
			</tr>
			</tbody>
		</table>
	</th>
	<th colspan="2">
	</th>
	<th colspan="2" rowspan="2"  align=left width="300">
		<b>TOTAIS</b>
				<table border="2"  cellpadding="2" class="tabelatotais">
					<tbody>
					<tr>
						<td widht="150" colspan="2">VALOR TOTAL DOS PRODUTOS (+)
						</td>
						<td width="75" align=right>
						'|| :TOTALPRODUTOS ||'
						</td>
					</tr>
		
						' 
						|| REPLACE(REPLACE(REPLACE(REPLACE(:IMPOSTOS,'<resultset>',''),'</resultset>',''),'<row>','<tr>'),'</row>','</tr>') ||
						'
					<tr>
						<td  colspan="2">FRETE/SEGURO/OUTROS (+)</td>
						<td align=right>
						' || :DESPESASADICIONAIS ||'
						</td>
					</tr>
					<tr>
						<td  colspan="2">DESCONTO FINANCEIRO (-)</td>
						<td align=right>
						'|| :DESCONTOTOTAL ||'
						</td>
					</tr>
					<tr>
						<td colspan="2"> 
						<b>VALOR TOTAL DO DOCUMENTO (=)</b>
						</td>
						<td align=right><b>
						' || :TOTALDOCUMENTO ||'
						</b>
						</td>
					</tr>
					</tbody>
				</table>
				</br>
				</br>
				</br>
				</br>
				</br>
	</th>
</tr>
<tr>
	<th colspan="3" align=left width="500">	
		<b>OBSERVAÇÕES</b></br></br>
		<table class="tabelasemborda">
		<tbody class="tabelasemborda">
			<tr>
				<td height="50" align=left class="tabelasemborda">
				' || :OBSERVACOES ||'
				</td>
			</tr>
		</tbody>
		</table> 
		</br>
	</th>
</tr>

</tfeet>

</table>
<p class="rodape"><b>CRM One - Layout 1.0</b></p>
</body>
';


END IF;


-------

 SELECT HEAD || BODY || FEET INTO HTML FROM DUMMY;

 
 END;