Quisiera saber si alguno tiene o conoce si hay alguna marca para listar los pedidos que no han sido facturados , actualmente yo tengo un reporte de la siguiente manera.
revisando e intentado de varias formas pero siempre me salen “ordenes de venta” que estan facturados pero en el reporte no me sale el numero de factura…
el orden seria de ordr a oinv pero por algun motivo dependiendo no me sale correctamente.
Agradeceria bastante si alguno tuviera “alguna luz”
SELECT DISTINCT
O1."DocDate",
O1."CardName" as "Cliente",
R1."ItemCode" as "Codigo",
R1."Dscription" as "Descripcion",
R1."Quantity",
R1."Currency" as "Moneda",
R1."Price" AS "Precio",
R1."TotalFrgn" as "Total/Linea",
T2."SlpName" "Vendedor",
T4."ItmsGrpNam" as "Categoria",
O1."DocNum" as "Pedido",
O2."DocNum" as "Entrega",
O4."DocNum" as "Fact Venta"
FROM ORDR O1
INNER JOIN RDR1 R1 ON O1."DocEntry" = R1."DocEntry"
LEFT JOIN DLN1 D1 ON D1."BaseEntry" = R1."TrgetEntry"
LEFT JOIN ODLN O2 ON D1."DocEntry" = O2."DocEntry"
LEFT JOIN INV1 I1 ON O1."DocEntry" = I1."BaseEntry"
LEFT JOIN OINV O4 ON I1."DocEntry" = O4."DocEntry"
INNER JOIN OSLP T2 ON O1."SlpCode" = T2."SlpCode"
INNER JOIN OITM T3 ON R1."ItemCode" = T3."ItemCode"
INNER JOIN OITB T4 ON T3."ItmsGrpCod" = T4."ItmsGrpCod"
INNER JOIN OCRD O5 ON O1."CardCode" = O5."CardCode"
WHERE
O1."DocDate" BETWEEN '[%0]' AND '[%1]'
and
(O4."DocNum" IS NULL )
ORDER BY O1."DocNum"
este es el segundo intentando pero tampoco.
SELECT S0."CardName"
,S0."ItemCode"
,T2."SlpName" "Vendedor"
,T4."ItmsGrpNam" AS "Categoria"
------------
,S0."ItemCode" AS "Codigo"
,S0."Descripcion"
,S0."Quantity"
,S0."Moneda"
,S0."Precio"
,S0."Total/Linea"
,S0."U/D"
---------------
,S0."DocNum" as "Pedido"
,S2."DocNum" as "Factura"
,S2."DocTotal"
FROM
(
SELECT T0."DocEntry" AS "SO_DE"
,T0."SlpCode"
,T0."CardCode"
,T0."DocNum"
,T1."TrgetEntry" AS "SO_TE"
,T0."CardName"
,T1."ItemCode"
,T1."Dscription" AS "Descripcion"
,T1."Quantity"
,T1."U_SCGFabricante"
,T1."U_SCG_Vendedor"
,T1."U_SCGCategoria"
,T1."U_SCGVendedor2"
,T1."Currency" AS "Moneda"
,T1."Price" AS "Precio"
,T1."TotalFrgn" AS "Total/Linea"
,T1."U_SCGUnidadVenta" AS "U/D"
FROM ORDR T0
LEFT JOIN RDR1 T1 ON T0."DocEntry" = T1."DocEntry"
) S0
LEFT JOIN
(
SELECT T0."DocEntry" AS "DN_DE"
,T0."DocNum" AS "DeliveryNumber"
,T1."TrgetEntry" AS "DN_TE"
FROM ODLN T0
LEFT JOIN DLN1 T1 ON T0."DocEntry" = T1."DocEntry"
GROUP BY T0."DocEntry"
,T0."DocNum"
,T1."TrgetEntry"
) S1
ON S0."SO_TE" = S1."DN_DE"
---
LEFT JOIN
(
SELECT T0."DocEntry" AS "DN_DE"
,T0."DocNum" AS "DeliveryNumber"
,T1."TrgetEntry" AS "DN_TE"
FROM ODLN T0
LEFT JOIN DLN1 T1 ON T0."DocEntry" = T1."DocEntry"
GROUP BY T0."DocEntry"
,T0."DocNum"
,T1."TrgetEntry"
) S10
ON S10."DN_DE" = S0."SO_TE"
---
LEFT JOIN OINV S2 ON S1."DN_TE" = S2."DocEntry"
INNER JOIN "SBOTRIDM".OSLP T2
ON S0."SlpCode" = T2."SlpCode"
INNER JOIN "SBOTRIDM".OITM T3
ON S0."ItemCode" = T3."ItemCode"
INNER JOIN "SBOTRIDM".OITB T4
ON T3."ItmsGrpCod" = T4."ItmsGrpCod"
INNER JOIN "SBOTRIDM".OCRD O5
ON S0."CardCode" = O5."CardCode"
--WHERE S0."DocNum" = '26957'
WHERE S0."DocNum" = '27504'
--WHERE S2."DocNum" IS NULL
ORDER BY S0."DocNum"