Query for invoices by row , connecting Orders (ORDR) and transport documents (ODLN)

[former_member646250]

Dear experts,

i'm working on a query for SAP B1 that can show me really fast ho my company is going.I need a query that shows my the invoices by row i'm making in a period, and i need to connect that to his order and document of transport.

The query is working, sometimes i loose some invoice,, i dont know why.

Can you please help me? The query is the following

SELECT Distinct(T0.DocNum ) AS 'Invoice', t0.taxdate AS 'Invoice date', T0.CARDCODE, T0.CARDNAME, t9.country, T1.ITEMCODE, T1.DSCRIPTION, T1.Quantity, T1.Price,T1.TotalSumSy,t4.docnum AS 'Ordine cliente', t5.docnum as' DDT', T5.Docdate as 'Data DDt', t4.cardcode, T4.CardName, T6.DOCTYPE
FROM [dbo].[OINV] T0 left JOIN [dbo].[INV1] T1 ON T0.DocEntry = T1.DocEntry
LEFT JOIN [dbo].[DLN1] T2 ON T1.BaseEntry = T2.DocEntry AND T1.BaseLine = T2.LineNum
LEFT JOIN [dbo].[RDR1] T3 ON T2.BaseEntry = T3.DocEntry AND T2.BaseLine = T3.LineNum
LEFT JOIN [dbo].[ORDR] T4 ON T4.DocEntry = T3.DocEntry
LEFT JOIN [dbo].[ODLN] T5 ON T5.DocEntry = T2.DocEntry
LEFT JOIN [dbo].[OCLG] T6 ON T6.DocNUM = T4.docnum
LEFT JOIN OCRD T9 ON T9.[CardCode] = T0.[CardCode]
LEFT JOIN CRD1 T10 ON T10.[CardCode] = T9.[CardCode]
left JOIN RIN1 T11 ON T11.BaseEntry = T0.DocEntry AND T11.BaseLine = T1.LineNum and T11.BaseType ='13'
WHERE T0.[taxDate] >= '[%0]' and T0.TAXDATE<='[%1]' and t11.itemcode is null and (t6.doctype =17 or t6.doctype is null)

[agustin_marcoscividanes]

Hi

perhaps you have sales invoices with no previous document, or sales invoices copied from sales order directly.

To obtain all results I suggest you run three queries, one for invoices with delivery and order, another with order and invoice, and the last with invoices with no previous document.

When you have the three queries, join them with UNION ALL to obtain all results in one query.

Kind regards

Agustín

[agustin_marcoscividanes]

Try this:

/* SELECT T0.DOCDATE FROM OINV */
declare @date_start datetime
declare @date_finish datetime
set @date_start = '[%0]'
set @date_finish = '[%1]'
select * FROM
(
SELECT Distinct(T0.DocNum ) AS 'Invoice', t0.taxdate AS 'Invoice date', T0.CARDCODE, T0.CARDNAME, t11.country, T1.ITEMCODE, T1.DSCRIPTION, T1.Quantity, T1.Price,T1.TotalSumSy,t4.docnum AS 'Ordine cliente', t5.docnum as' DDT', T5.Docdate as 'Data DDt', t4.cardcode, T4.CardName
FROM [dbo].[OINV] T0 left JOIN [dbo].[INV1] T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN [dbo].[DLN1] T2 ON T1.BaseEntry = T2.DocEntry AND T1.BaseLine = T2.LineNum AND T1.basetype = 15
INNER JOIN [dbo].[RDR1] T3 ON T2.BaseEntry = T3.DocEntry AND T2.BaseLine = T3.LineNum AND T2.basetype = 17
INNER JOIN [dbo].[ORDR] T4 ON T4.DocEntry = T3.DocEntry
INNER JOIN [dbo].[ODLN] T5 ON T5.DocEntry = T2.DocEntry
LEFT JOIN CRD1 T10 ON T10.[CardCode] = T0.[CardCode] AND T10.adresType = 'B'
LEFT JOIN OCRY T11 ON T10.[Country] = T11.[Code]
WHERE T0.[taxDate] >= @date_start and T0.TAXDATE<=@date_finish
union all
SELECT Distinct(T0.DocNum ) AS 'Invoice', t0.taxdate AS 'Invoice date', T0.CARDCODE, T0.CARDNAME, t11.country, T1.ITEMCODE, T1.DSCRIPTION, T1.Quantity, T1.Price,T1.TotalSumSy,t4.docnum AS 'Ordine cliente', null, null, t4.cardcode, T4.CardName
FROM [dbo].[OINV] T0 left JOIN [dbo].[INV1] T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN [dbo].[RDR1] T3 ON T1.BaseEntry = T3.DocEntry AND T2.BaseLine = T3.LineNum AND T2.basetype = 17
INNER JOIN [dbo].[ORDR] T4 ON T4.DocEntry = T3.DocEntry
LEFT JOIN CRD1 T10 ON T10.[CardCode] = T0.[CardCode] AND T10.adresType = 'B'
LEFT JOIN OCRY T11 ON T10.[Country] = T11.[Code]
WHERE T0.[taxDate] >= @date_start and T0.TAXDATE<=@date_finish
union all
SELECT Distinct(T0.DocNum ) AS 'Invoice', t0.taxdate AS 'Invoice date', T0.CARDCODE, T0.CARDNAME, t11.country, T1.ITEMCODE, T1.DSCRIPTION, T1.Quantity, T1.Price,T1.TotalSumSy,null AS 'Ordine cliente', null as' DDT', null as 'Data DDt', t0.cardcode, T0.CardName
FROM [dbo].[OINV] T0 
LEFT JOIN CRD1 T10 ON T10.[CardCode] = T0.[CardCode] AND T10.adresType = 'B'
LEFT JOIN OCRY T11 ON T10.[Country] = T11.[Code]
WHERE T0.[taxDate] >= @date_start and T0.TAXDATE<=@date_finish
) S0

[former_member646250]

Thank you Augustin, you've been very kind!

i've understood what your are saying but im not that expert with the function, can you please provide an example on that query?

Thanks