Vincular OINV y ODLN

[vhzl123]

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'

[jitin_chawla]

Hi,

Check this below:

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 T2."DocEntry" = T3."DocEntry"
WHERE T1.BaseType = '15' AND
    YEAR(T3."DocDate") = '2023' 
    AND YEAR(T0."DocDate") = '2024'

Kr,

Jitin

[BattleshipCobra]

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