Src/Sankhya/Construtores de Componentes de BI/118.COMERCIA_ANALISE-DE-MARGEM-MARKUP.xml
<gadget>
<prompt-parameters>
<parameter id="DTINI" description="1. Data Inicial" metadata="date" required="true" keep-last="true"/>
<parameter id="DTFIM" description="2. Data Final" metadata="date" required="true" keep-last="true"/>
</prompt-parameters>
<level id="lvl_g5ly51" description="Principal">
<grid id="grd_g5ly52" tamTexto="18">
<title>
<![CDATA[ANÁLISE DE MARGEM E MARKUP PARA NOTAS EMITIDAS EM :DTINI ATÉ :DTFIM]]>
</title>
<expression type="sql" data-source="MGEDS">
<![CDATA[SELECT P.NUNOTA, p.VLRNOTA - p.vlrfrete AS vlrpedido, nota.valornota AS ValorDaNota, sum(nota.NotaVlrFrete) AS ValorFreteDaNota, p.VLRFRETE AS Rvlrfrete , VEN.APELIDO as Vendedor, CAST(p.codtipoper as varchar) + ' - ' + tipoper.descroper as TipoOperacao, sum(SANKHYA_PRODUCAO.sankhya.PRECODECUSTO(ite.CODPROD, ite.CODVOL)*QTDNEG) AS custo, FL.VLRFRETE AS FRETE, P.VLRNOTA-sum(SANKHYA_PRODUCAO.sankhya.PRECODECUSTO(ite.CODPROD, ite.CODVOL)*QTDNEG) AS MARGEM, P.VLRNOTA/CASE WHEN sum(SANKHYA_PRODUCAO.sankhya.PRECODECUSTO(ite.CODPROD, ite.CODVOL)*QTDNEG) = 0 THEN 0.01 ELSE sum(SANKHYA_PRODUCAO.sankhya.PRECODECUSTO(ite.CODPROD, ite.CODVOL)*QTDNEG) END AS MARKUP, (P.VLRNOTA-P.VLRFRETE)-sum(SANKHYA_PRODUCAO.sankhya.PRECODECUSTO(ite.CODPROD, ite.CODVOL)*QTDNEG) AS MARGEM_SEMFRETE, (P.VLRNOTA-P.VLRFRETE)/CASE WHEN sum(SANKHYA_PRODUCAO.sankhya.PRECODECUSTO(ite.CODPROD, ite.CODVOL)*QTDNEG) = 0 THEN 0.01 ELSE sum(SANKHYA_PRODUCAO.sankhya.PRECODECUSTO(ite.CODPROD, ite.CODVOL)*QTDNEG) END AS MARKUP_SEMFRETE FROM SANKHYA_PRODUCAO.sankhya.TGFCAB P INNER JOIN SANKHYA_PRODUCAO.sankhya.TGFVEN VEN ON VEN.CODVEND = P.CODVEND INNER JOIN SANKHYA_PRODUCAO.sankhya.TGFTOP TIPOPER on TIPOPER.CODTIPOPER = P.CODTIPOPER and P.DHTIPOPER = TIPOPER.DHALTER INNER JOIN SANKHYA_PRODUCAO.sankhya.TGFITE ITE ON ITE.NUNOTA = P.NUNOTA AND (CODVOL != 'KT' OR (CODVOL= 'KT' AND USOPROD = 'D')) INNER JOIN (SELECT cab.NUNOTA AS NotaNunota, cab.VLRNOTA AS ValorNota, cab.AD_PEDORIGINAL AS PedOriginal, cab.VLRFRETE AS NotaVlrFrete, CASE WHEN t.NUNOTA IS NULL THEN CASE WHEN w.NUNOTA IS NULL THEN CASE WHEN s.NUNOTA IS NULL THEN cab.NUNOTA END ELSE w.NUNOTA END ELSE t.NUNOTA END AS PedNunota FROM SANKHYA_PRODUCAO.sankhya.TGFCAB cab LEFT JOIN SANKHYA_PRODUCAO.sankhya.TGFCAB t ON t.NUNOTA = cab.AD_PEDORIGINAL AND t.CODTIPOPER IN (500, 501, 515) AND CAB.AD_PEDORIGINAL > 200000 AND cab.AD_BONIFICADO IS NULL LEFT JOIN SANKHYA_PRODUCAO.sankhya.TGFCAB w ON w.NUMNOTA = cab.AD_PEDORIGINAL AND w.CODTIPOPER IN (502) AND CAB.AD_PEDORIGINAL > 200000 AND cab.AD_BONIFICADO IS NULL LEFT JOIN SANKHYA_PRODUCAO.sankhya.TGFCAB S ON S.AD_PEDORIGINAL = cab.AD_PEDORIGINAL AND s.CODTIPOPER = 503 AND CAB.AD_PEDORIGINAL > 200000 AND cab.CODPARC = s.CODPARC AND cab.AD_BONIFICADO IS NULL WHERE cab.CODTIPOPER = 550 AND cab.DTNEG BETWEEN :dtini AND :dtfim) NOTA ON NOTA.PedNunota = P.NUNOTA LEFT JOIN (SELECT C.CODPROD, CUSREP FROM SANKHYA_PRODUCAO.sankhya.TGFCUS C INNER JOIN (SELECT CODPROD, MAX(DTATUAL) AS DT FROM SANKHYA_PRODUCAO.sankhya.TGFCUS GROUP BY CODPROD) CUSTO ON CUSTO.CODPROD = C.CODPROD AND CUSTO.DT = C.DTATUAL) CUS ON CUS.CODPROD = ITE.CODPROD LEFT JOIN SANKHYA_PRODUCAO.sankhya.AD_PEDIDOVTEXSC FL ON FL.PEDORIGINAL = P.AD_PEDORIGINAL GROUP BY nota.valornota, P.NUNOTA, P.VLRNOTA, P.VLRFRETE, FL.VLRFRETE, VEN.APELIDO, P.CODTIPOPER, TIPOPER.DESCROPER ORDER BY P.NUNOTA]]>
</expression>
<metadata>
<field name="NUNOTA" label="Nº do Pedido" type="I" visible="true" useFooter="false" mask="#.##0;-n"/>
<field name="vlrpedido" label="Valor do Pedido" type="F" visible="true" useFooter="true" mask="R$ #.##0,00"/>
<field name="ValorDaNota" label="Valor da NF-E" type="F" visible="true" useFooter="true" mask="R$ #.##0,00"/>
<field name="ValorFreteDaNota" label="ValorFreteDaNota" type="I" visible="true" useFooter="true" mask="R$ #.##0,00"/>
<field name="Rvlrfrete" label="Valor do Frete" type="F" visible="true" useFooter="true" mask="R$ #.##0,00"/>
<field name="Vendedor" label="Vendedor" type="S" visible="true" useFooter="false"/>
<field name="TipoOperacao" label="TOP" type="S" visible="true" useFooter="false"/>
<field name="custo" label="CMV" type="I" visible="true" useFooter="true" mask="R$ #.##0,00"/>
<field name="FRETE" label="CustoFrete" type="I" visible="true" useFooter="true" mask="R$ #.##0,00"/>
<field name="MARGEM" label="Margem" type="I" visible="true" useFooter="true" mask="R$ #.##0,00"/>
<field name="MARKUP" label="Markup" type="I" visible="true" useFooter="false" mask="#.##0,00;-n"/>
<field name="MARGEM_SEMFRETE" label="Margem s/Frete" type="I" visible="true" useFooter="true" mask="R$ #.##0,00"/>
<field name="MARKUP_SEMFRETE" label="Markup s/Frete" type="I" visible="true" useFooter="false" mask="#.##0,00;-n"/>
</metadata>
<on-click navigate-to="lvl_g5ly54">
<param id="NUNOTA">$NUNOTA</param>
</on-click>
</grid>
</level>
<level id="lvl_g5ly54" description="2O NIVEL">
<args>
<arg id="NUNOTA" type="integer"/>
</args>
<grid id="grd_g5ly55" tamTexto="18">
<title>
<![CDATA[Informações do pedido :NUNOTA]]>
</title>
<expression type="sql" data-source="MGEDS">
<![CDATA[SELECT P.NUNOTA, I.CODPROD, (RTRIM(PRO.DESCRPROD) + ' ' + RTRIM(ISNULL(PRO.COMPLDESC,''))) AS PRODUTO,VLRUNIT, I.QTDNEG, I.VLRTOT, sankhya.PRECODECUSTO(I.CODPROD,I.CODVOL) AS CUSTO FROM SANKHYA_PRODUCAO.sankhya.TGFCAB P INNER JOIN SANKHYA_PRODUCAO.sankhya.TGFITE I ON I.NUNOTA = P.NUNOTA inner join sankhya.TGFPRO PRO ON PRO.CODPROD = I.CODPROD where p.NUNOTA = :NUNOTA and i.codvol <> 'KT']]>
</expression>
<metadata>
<field name="NUNOTA" label="Pedido" type="I" visible="true" useFooter="false" mask="#.##0;-n"/>
<field name="CODPROD" label="Código Produto" type="I" visible="true" useFooter="false"/>
<field name="PRODUTO" label="Produto" type="S" visible="true" useFooter="false"/>
<field name="VLRUNIT" label="Valor Unitário" type="F" visible="true" useFooter="true" mask="R$ #.##0,00"/>
<field name="QTDNEG" label="Qtd Negociada" type="I" visible="true" useFooter="true" mask="#.##0,00;-n"/>
<field name="VLRTOT" label="Valor Total" type="F" visible="true" useFooter="true" mask="R$ #.##0,00"/>
<field name="CUSTO" label="Custo" type="F" visible="true" useFooter="true" mask="R$ #.##0,00"/>
</metadata>
</grid>
</level>
</gadget>