Hi all,
I know this has been asked a couple times already but the answers seems to vary between posts with a lot suggesting to use the stored procedure to achieve it. I have gone down the path of using a union query and for the most part it seems to work but it seems to get it mixed up when adding and removing the lines manually in SAP.
Can anyone suggest why this might be? I've gone through a few code iterations and this is the closest i've got it to working.
SELECT DISTINCT<br>T1.LineNum,<br>0 AS 'Line Seq',<br> T0.[CardName], <br> T0.[DocNum], <br> T0.[DocDate], <br> T0.[Address],<br> T0.[Address2], <br> T2.[SlpName], <br> T2.[Mobil], <br> T2.[Email],<br> T0.DocEntry,<br> T0.U_ProjName,<br> T1.ItemCode,<br> T1.Dscription,<br> T1.Quantity,<br> T1.Price,<br> T4.Tel1,<br> '\\SMBPDC\CCC Storage\Shared Folders\SBO_LSA_LIVE\Pictures\'+convert(varchar,T3.Picturname) as Picturname,<br> T4.Cellolar,<br> T4.E_MailL,<br> T4.FirstName,<br> T4.LastName,<br> T5.PymntGroup,<br> CAST (T0.[U_LSA_Deposit] AS DECIMAL) AS Deposit,<br> T0.[U_LSA_Period],<br> T0.Series,<br> T1.FreeTxt,<br> CAST(T3.UserText AS varchar(MAX)) AS ItemInfo,<br> '' AS 'LineText',<br> T0.U_EID,<br> T8.U_entityName,<br> T0.U_useEntityName,<br> T8.U_entityAlwaysOn<br>FROM OQUT T0 <br> INNER JOIN QUT1 T1 ON T0.[DocEntry] = T1.[DocEntry] AND T1.LineType = 'R' <br> INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode<br> INNER JOIN OITM T3 ON T1.[ItemCode] = T3.[ItemCode]<br> LEFT JOIN OCPR T4 ON T0.[CntctCode] = T4.[CntctCode]<br> INNER JOIN OCTG T5 ON T0.[GroupNum] = T5.[GroupNum]<br> INNER JOIN OITB T7 ON T3.ItmsGrpCod = T7.ItmsGrpCod<br>INNER JOIN OCRD T8 ON T8.CardCode = T0.CardCode<br>WHERE T0.DocEntry = 8931 AND T0.ObjType = 23<br><br>UNION ALL<br><br>SELECT DISTINCT<br>T1.AftLineNum,<br>T1.LineSeq,<br> T0.[CardName], <br> T0.[DocNum], <br> T0.[DocDate], <br> T0.[Address], <br> T0.[Address2], <br> T2.[SlpName], <br> T2.[Mobil], <br> T2.[Email],<br> T0.DocEntry,<br> T0.U_ProjName,<br> '',<br> '',<br> 0,<br> 0,<br> T4.Tel1,<br> '',<br> T4.Cellolar,<br> T4.E_MailL,<br> T4.FirstName,<br> T4.LastName,<br> T5.PymntGroup,<br> CAST (T0.[U_LSA_Deposit] AS DECIMAL) AS Deposit,<br> T0.[U_LSA_Period],<br> T0.Series,<br> '',<br> '',<br> CAST(T1.LineText AS varchar(MAX)) AS LineText,<br> T0.U_EID,<br> T6.U_entityName,<br> T0.U_useEntityName,<br> T6.U_entityAlwaysOn<br>FROM OQUT T0 <br> INNER JOIN QUT10 T1 ON T0.[DocEntry] = T1.[DocEntry] AND T1.LineType = 'T'<br> INNER JOIN QUT1 T3 ON T3.DocEntry = T1.DocEntry AND T1.AftLineNum = T3.VisOrder <br> INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode<br> LEFT JOIN OCPR T4 ON T0.[CntctCode] = T4.[CntctCode]<br> INNER JOIN OCTG T5 ON T0.[GroupNum] = T5.[GroupNum]<br> INNER JOIN OCRD T6 ON T6.CardCode = T0.CardCode<br>WHERE T0.DocEntry = 8931 AND T0.ObjType = '{?ObjectId@}'<br>ORDER BY T1.LineNum, [Line Seq]
Appreciate any assistance on this one.
Nick