Hola Andres!
Muchas gracias por responder.
Ahí va la consulta:
--Rentabilidad U1
SELECT
(case when T0."DocSubType" = '--'
Then 'Factura'
else 'Nota de Débito'
end) as "Tipo de Documento",
T0."DocEntry",
(T0."FolNumFrom"|| '-'|| T0."Letter"||'-'||T0."FolNumTo") as "Comprobante",
T0."U_FECHAPACTADA",
T0."DocDate",
T0."CardCode",
T0."CardName",
T3."ItmsGrpNam",
T1."ItemCode",
T2."ItemName",
T1."U_APROBACOT",
T2."CardCode" as "Proveedor",
T5."CardName" AS "Nombre del Proveedor",
T2."U_UnidadNegocio" as "Unidad de Negocio",
T1."Quantity",
T1."UomCode" as "Unidad de Medida",
T1."U_PVP" as "PVP",
T1."Price",
T1."LineTotal",
T1."U_DescuentoFlux" as "Descuento Flux",
T1."U_DescuentoSAP" as "Descuento SAP",
T1."U_COSTOREPO2" as "Costo Promedio",
T1."U_COSTOREPO1" as "Costo Reposición" ,
(T1."Price" - T1."U_COSTOREPO2") as "Rentabilidad Costo Prom",
(Case when T1."U_COSTOREPO2" > 0
then ((T1."Price" - T1."U_COSTOREPO2") / T1."U_COSTOREPO2" *100)
else 0
end ) as "Rentabilidad Costo Prom %",
(T1."Price" - T1."U_COSTOREPO1") as "Rentabilidad Costo Repo",
(Case when T1."U_COSTOREPO1" > 0
then ((T1."Price" - T1."U_COSTOREPO1") / T1."U_COSTOREPO1" *100 )
else 0
end ) as "Rentabilidad Costo Repo %",
T1."Commission",
T1."U_CanalVenta",
T1."U_Operador",
T1."U_Telemarketer",
T4."SlpName" ,
T1."U_RequiereVale" as "Requiere Vale",
T1."U_ValePresentado" as "Vale Presentado",
T1."U_VKM_COMPUESTO" as "KIT",
T1."U_Combo_Quantity" as "Cantidad de KIT",
T1."U_SUBZONADEREPARTO",
T1."U_ZONADEREPARTO",
T1."U_NROTRANSFER",
T1."U_PoliticaPrecio",
T6."U_Costo" "Costo base año anterior",
(T6."U_Costo" * T1."Quantity") "CMV a costo base año ant.",
(T1."U_COSTOREPO2" * T1."Quantity") as "Costo Promedio Total",
(((T1."U_COSTOREPO2" * T1."Quantity") * 100) / (T6."U_Costo" * T1."Quantity") - 100) "% Inflac. costos",
T6."U_Precio" "Precio base año ant.",
(T6."U_Precio" * T1."Quantity") "Subtot. a precio base año ant.",
((T1."LineTotal" * 100) / (T6."U_Precio" * T1."Quantity") - 100) "% Inflac. ventas"
FROM OINV T0 INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry"
inner join OITM as T2 on T2."ItemCode" = T1."ItemCode"
inner join OITB as T3 on T3."ItmsGrpCod" = T2."ItmsGrpCod"
LEFT join OSLP as T4 on T4."SlpCode" = T1."SlpCode"
INNER JOIN OCRD AS T5 ON T5."CardCode" = T2."CardCode"
LEFT JOIN "@ITMS" T6 ON T6."Name" = T2."ItemCode" AND T6."U_Year" = (YEAR(T0."DocDate")-1) AND T6."U_Month" = (MONTH(T0."DocDate"))
WHERE
MONTH(T0."DocDate") = MONTH(CURRENT_DATE)
AND T0."CANCELED" = 'N'
AND T3."ItmsGrpNam" NOT LIKE 'F%'
AND T2."U_UnidadNegocio" = 'U1'
union all
SELECT
(case when T0."ObjType" = '14'
Then 'Nota de Crédito'
end ) as "Tipo de Documento",
T0."DocEntry",
(T0."FolNumFrom"|| '-'|| T0."Letter"||'-'||T0."FolNumTo") as "Comprobante",
T0."U_FECHAPACTADA",
T0."DocDate",
T0."CardCode",
T0."CardName",
T3."ItmsGrpNam",
T1."ItemCode",
T2."ItemName",
T1."U_APROBACOT",
T2."CardCode" as "Proveedor",
T5."CardName" AS "Nombre del Proveedor",
T2."U_UnidadNegocio" as "Unidad de Negocio",
(T1."Quantity"* -1),
T1."UomCode" as "Unidad de Medida",
T1."U_PVP" as "PVP",
(T1."Price"*-1),
(T1."LineTotal"*-1),
T1."U_DescuentoFlux" as "Descuento Flux",
T1."U_DescuentoSAP" as "Descuento SAP",
(T1."U_COSTOREPO2"*-1) as "Costo Promedio",
(T1."U_COSTOREPO1" *-1) as "Costo Reposición",
((T1."Price" - T1."U_COSTOREPO2")*-1) as "Rentabilidad Costo Prom",
(Case when T1."U_COSTOREPO2" > 0
then ((T1."Price" - T1."U_COSTOREPO2") / T1."U_COSTOREPO2" *100)
else 0
end ) as "Rentabilidad Costo Prom %",
((T1."Price" - T1."U_COSTOREPO1")*-1) as "Rentabilidad Costo Repo",
(Case when T1."U_COSTOREPO1" > 0
then ((T1."Price" - T1."U_COSTOREPO1") / T1."U_COSTOREPO1" *100 )
else 0
end ) as "Rentabilidad Costo Repo %",
T1."Commission",
T1."U_CanalVenta",
T1."U_Operador",
T1."U_Telemarketer",
T4."SlpName",
T1."U_RequiereVale" as "Requiere Vale",
T1."U_ValePresentado" as "Vale Presentado",
T1."U_VKM_COMPUESTO" as "KIT",
T1."U_Combo_Quantity" as "Cantidad de KIT",
T1."U_SUBZONADEREPARTO",
T1."U_ZONADEREPARTO",
T1."U_NROTRANSFER",
T1."U_PoliticaPrecio",
T6."U_Costo" "Costo base año anterior",
(T6."U_Costo" * T1."Quantity") "CMV a costo base año ant.",
(T1."U_COSTOREPO2" * T1."Quantity") as "Costo Promedio Total",
(((T1."U_COSTOREPO2" * T1."Quantity") * 100) / (T6."U_Costo" * T1."Quantity") - 100) "% Inflac. costos",
T6."U_Precio" "Precio base año ant.",
(T6."U_Precio" * T1."Quantity") "Subtot. a precio base año ant.",
((T1."LineTotal" * 100) / (T6."U_Precio" * T1."Quantity") - 100) "% Inflac. ventas"
FROM ORIN T0 INNER JOIN RIN1 T1 ON T1."DocEntry" = T0."DocEntry"
inner join OITM as T2 on T2."ItemCode" = T1."ItemCode"
inner join OITB as T3 on T3."ItmsGrpCod" = T2."ItmsGrpCod"
LEFT join OSLP as T4 on T4."SlpCode" = T1."SlpCode"
INNER JOIN OCRD AS T5 ON T5."CardCode" = T2."CardCode"
LEFT JOIN "@ITMS" T6 ON T6."Name" = T2."ItemCode" AND T6."U_Year" = (YEAR(T0."DocDate")-1) AND T6."U_Month" = (MONTH(T0."DocDate"))
WHERE
MONTH(T0."DocDate") = MONTH(CURRENT_DATE)
AND T0."CANCELED" = 'N'
AND T3."ItmsGrpNam" NOT LIKE 'F%'
AND T2."U_UnidadNegocio" = 'U1'