Skip to Content
0
Former Member
Jan 29, 2016 at 02:26 PM

A/R invoice email confirmation query

152 Views

Hi,


Can any one test my query to check if an invoice has been sent using both boyum and SAP ways of sending.

We need to query to tell if an invoice has been emailed or printed.



http://scn.sap.com/community/business-one/blog/2014/01/28/my-top-sql-queries-for-sap-business-one




SELECT 
DISTINCT
 t0.DocEntry,
 t0.DocNum, 
t0.CardCode,
 t0.CardName, 
t0.DocDate,
 t0.DocDueDate as 'Due Date', 
T1.U_BOY_DATE, 
t0.DocCur, 
t0.DocTotal,
 T0.DocStatus, 
T0.CANCELED, 
T0.Printed, 
CASE
 WHEN T1.[U_BOY_Action] = 2 THEN 'Printed' 
WHEN T1.[U_BOY_Action] = 4 THEN 'Emailed'
 ELSE CONVERT(VARCHAR,T1.[U_BOY_Action]) End AS 'Boyum_Action', 
T3.WasSent AS 'SAP_Sent', 
T4.Confirmed2 
FROM OINV t0 
LEFT JOIN [dbo].[@BOY_85_DEL_LOG]  T1  ON T0.DocNum = T1.[U_BOY_id]
 LEFT JOIN dbo.OALR T2 ON CONVERT(VARCHAR,T0.DocNum) = LEFT(CONVERT(VARCHAR,T2.MsgData),9)
 LEFT JOIN OAOB T3 ON T2.Code = T3.AlertCode 
LEFT JOIN AOB1 T4 ON T3.AlertCode = T4.AlertCode  ORDER BY t0.DocNum