2 weeks ago
Hola!
Quiero generar una query que me devuelta los albanes de un año que se han facturado un año posterior.
He generado el codigo que adjunto pero me mezcla albaranes y facturas. No me muestra la factura correcta para ese albarán.
¿Me puede ayudar alguien con esto? Gracias!!
SELECT DISTINCT
T3."CardCode" AS "Cliente",
T3."DocNum" AS "NumAlbaran",
T3."DocEntry" AS "NavegarAlbaran",
T0."CardCode",
T0."DocNum" AS "NumFactura",
T3."DocDate" AS "FechaContabilizacionAlbaran",
T0."DocDate" AS "FechaContabilizacionFactura"
FROM
OINV T0
INNER JOIN
INV1 T1 ON T0."DocEntry" = T1."DocEntry"
LEFT JOIN
DLN1 T2 ON T1."BaseEntry" = T2."DocEntry"
LEFT JOIN
ODLN T3 ON T1."DocEntry" = T3."DocEntry"
WHERE
YEAR(T3."DocDate") = '2023'
AND YEAR(T0."DocDate") = '2024'
Hello,
Give this code a try, it should do you what you need:
SELECT
T0."CardCode" AS "Cliente"
,T0."DocNum" AS "NumAlbaran"
,X."InvCardCode"
,X."Invoice" AS "NumFactura"
,T0."DocDate" AS "FechaContabilizacionAlbaran"
,YEAR(T0."DocDate") AS "FechaPuestaAno"
,X."InvDate" AS "FechaContabilizacionFactura"
,X."InvYear" AS "FacturaEnviadaAno"
FROM
ODLN T0
INNER JOIN (
SELECT
D0."BaseEntry" AS "DocEntry"
,D1."DocNum" AS "Invoice"
,D1."CardCode" AS "InvCardCode"
,D1."DocDate" AS "InvDate"
,YEAR(D1."DocDate") AS "InvYear"
FROM
INV1 D0
INNER JOIN OINV D1 ON D0."DocEntry" = D1."DocEntry"
WHERE
D0."BaseType" = 15
AND
D0."VisOrder" = 0
) X ON T0."DocEntry" = X."DocEntry"
WHERE YEAR(T0."DocDate") <> X."InvYear" AND YEAR(T0."DocDate") = '2023'
ORDER BY T0."DocNum"
You could avoid this whole issue by looking into the "Use Shipped Goods Account for Customer" checkbox in Administration >> System Initialization >> General Settings >> BP Tab:
If you check this box and then you define a "Shipped Goods" account, it will accrue the COGS values until you process the invoice so you don't have to match them. It's the simple solution to avoid having to manually match revenue and COGS via query and journal entry.
Hopefully this answer helps!
Mike
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
9 | |
8 | |
7 | |
7 | |
3 | |
3 | |
3 | |
2 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.