4 Replies Latest reply on Jun 17, 2018 7:01 AM by

    Adding Posting Date and Posted by whom in Query

      Hi,

      In the query below, I want to add posting data and the user name for posting, then what should be done?

      SELECT

      T0.CardName 'Customer Name'

      ,T0.DocDate 'Document Date'

      ,'AR Invoice' 'SOP Type'

      ,T0.DocNum 'SOP Number'

      ,T1.ItemCode 'Item Number'

      ,T1.Dscription 'Item Description'

      ,T1.Quantity 'QTY'

      ,T1.PriceBefDi 'Unit Price'

      ,T1.LineTotal 'Extended Price'

                    ,T0.DiscSum 'Total Discount'

                    ,T0.VatSum 'Total Tax'

      ,T0.U_BM_NextExpDt 'ASM'

      ,T0.NumAtCard 'Customer PO Number'

      ,T0.ShipToCode 'ShipToName from Sales Line Item'

      ,'SRV' 'Type'

      ,T7.ItmsGrpNam 'Category'

      ,'' 'Account Manager'

      ,T4.GroupName 'Customer Class'

      ,T0.CardCode 'Customer Number'

      ,T8.FormatCode 'Sales Account Number'

      ,T2.StateS 'State from Sales Transaction'

      ,'' 'Doc'

      ,DATEPART(WK,T0.DocDate) 'Week #'

      ,'' 'Cost'

      ,'' 'DOP'

                   ,T9.CardName

      FROM

      OINV T0

      INNER JOIN INV1 T1 ON T0.DocEntry=T1.DocEntry

      LEFT JOIN INV12 T2 ON T0.DocEntry=T2.DocEntry

      INNER JOIN OCRD T3 ON T0.CardCode=T3.CardCode

      INNER JOIN OCRG T4 ON T3.GroupCode=T4.GroupCode AND T4.GroupType='C'

      LEFT JOIN OCTG T5 ON T0.GroupNum=T5.GroupNum

      INNER JOIN OITM T6 ON T1.ItemCode=T6.ItemCode

      INNER JOIN OITB T7 ON T6.ItmsGrpCod=T7.ItmsGrpCod

      INNER JOIN OACT T8 ON T1.AcctCode=T8.AcctCode

                   LEFT JOIN OCRD T9 ON T0.BpChCode=T9.CardCode

      WHERE

      T0.DocType='I'

      AND T0.DocDate>='[%0]' AND T0.DocDate<='[%1]'

       

       

      UNION ALL

       

       

      SELECT

      T0.CardName 'Customer Name'

      ,T0.DocDate 'Document Date'

      ,'AR Invoice' 'SOP Type'

      ,T0.DocNum 'SOP Number'

      ,T1.ItemCode 'Item Number'

      ,T1.Dscription 'Item Description'

      ,-1*T1.Quantity 'QTY'

      ,T1.PriceBefDi 'Unit Price'

      ,-1*T1.LineTotal 'Extended Price'

                    ,T0.DiscSum 'Total Discount'

                    ,-1*T0.VatSum 'Total Tax'

      ,T0.U_BM_NextExpDt 'ASM'

      ,T0.NumAtCard 'Customer PO Number'

      ,T0.ShipToCode 'ShipToName from Sales Line Item'

      ,'SRV' 'Type'

      ,T7.ItmsGrpNam 'Category'

      ,'' 'Account Manager'

      ,T4.GroupName 'Customer Class'

      ,T0.CardCode 'Customer Number'

      ,T8.FormatCode 'Sales Account Number'

      ,T2.StateS 'State from Sales Transaction'

      ,'' 'Doc'

      ,DATEPART(WK,T0.DocDate) 'Week #'

      ,'' 'Cost'

      ,'' 'DOP'

                   ,T9.CardName

      FROM

      ORIN T0

      INNER JOIN RIN1 T1 ON T0.DocEntry=T1.DocEntry

      LEFT JOIN RIN12 T2 ON T0.DocEntry=T2.DocEntry

      INNER JOIN OCRD T3 ON T0.CardCode=T3.CardCode

      INNER JOIN OCRG T4 ON T3.GroupCode=T4.GroupCode AND T4.GroupType='C'

      LEFT JOIN OCTG T5 ON T0.GroupNum=T5.GroupNum

      INNER JOIN OITM T6 ON T1.ItemCode=T6.ItemCode

      INNER JOIN OITB T7 ON T6.ItmsGrpCod=T7.ItmsGrpCod

      INNER JOIN OACT T8 ON T1.AcctCode=T8.AcctCode

                   LEFT JOIN OCRD T9 ON T0.BpChCode=T9.CardCode

      WHERE

      T0.DocType='I'

      AND T0.DocDate>='[%0]' AND T0.DocDate<='[%1]'

       

      Any help would be highly appreciated. Thank you in Advance

       

      Regards,