Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Relatórios financeiros (A receber, recebidos, a pagar e pagos).

fabiobilicki
Active Contributor
‎2013 Sep 05 12:29 PM
17,416


Bom dia a todos,

Abaixo está as consultas desenvolvidas até o momento para gerar Contas à Pagar, Contas Pagas, Contas à receber e Contas recebidas.

Sempre tive essa visão de divisão dos relatórios financeiros, esse método ajuda atualmente bastante o pessoal do meu financeiro.

Se encontrarem algum problema na consulta, esteja a vontade para sugerir correções e melhorias, já que são baseadas no no modo que uso o SAP.

Contas à receber


  -- Notas fiscais de saída

SELECT

     'NS' as 'Tipo'  

    , MAX(T0.[DocNum]) as 'ID'  

    ,( ISNULL((SELECT TOP 1 T4.NfmName from ONFM T4 WHERE T4.AbsEntry = T0.Model),'') + ' / ' + t0.SeriesStr + ' / ' + CAST(T0.SERIAL AS VARCHAR) ) as 'Documento'

  

    , (CAST( MAX(T1.[InstlmntID]) as varchar) + ' de ' +

       (SELECT CAST(MAX(T3.[InstlmntID]) AS VARCHAR) FROM [dbo].[INV6] T3 WHERE T3.[DocEntry] = T0.[DocEntry]) ) as Parcela

    , MAX(T0.[TaxDate]) as 'Dt.Emissão'

    , MAX(T0.[DocDate]) as 'Dt.Entrada'  

    , MAX(T1.[DueDate]) as 'Dt.Vencimento'

    , MAX(T0.[CardCode]) as 'Codigo PN'

    , MAX(T0.[CardName]) as 'Nome PN'

    , MAX(T0.[TransId]) as TransId

    , MAX(T1.[InsTotal]) as 'TotalTitulo'

    , ( MAX(T1.[InsTotal]) - MAX(T1.[PaidToDate]) ) as 'TotalSaldo'

    , MAX(T0.[Comments]) as 'Observação'

    , T0.[ObjType]

    , (SELECT MAX(T3.BaseRef) FROM INV1 T3 WHERE T3.DocEntry = T0.DocEntry) as BaseRef

    FROM  [dbo].[OINV] T0

    INNER  JOIN [dbo].[INV6] T1  ON  T1.[DocEntry] = T0.[DocEntry]  

    LEFT OUTER  JOIN [dbo].[INV5] T2  ON  T0.[DocEntry] = T2.[AbsEntry]

    WHERE (T1.[TotalBlck] <> T1.[InsTotal] AND

          ( T1.[InsTotal] - T1.[PaidToDate] ) <> 0 )

    GROUP BY T0.[DocEntry], T1.[InstlmntID], T0.[ObjType], T0.Model, T0.SeriesStr, T0.Serial

  

UNION ALL

  -- Lançamentos manuais

      SELECT 'LC30' as Tipo

         , T0.[TransId] as 'ID'       

         , CAST( T0.[TransId] as varchar) as 'Documento'

         , '1 de 1' as 'Parcela'

         , T0.[TaxDate] as 'Dt.Emissão'

         , T0.[RefDate] as 'Dt.Entrada'

         , T0.[DueDate] as 'Dt.Vencimento'

         , T2.[CardCode] as 'Codigo PN'

         , T2.[CardName] as 'Nome PN'       

         , T0.[TransId]

         , T0.[Debit] as 'TotalTitulo'

         , T0.[BalDueDeb]  as 'TotalSaldo'

         , T0.[LineMemo] as 'Observação'

         , T0.[ObjType]

         , 0 as BaseRef

    FROM  [dbo].[JDT1] T0

    INNER  JOIN [dbo].[OJDT] T1  ON  T0.[TransId] = T1.[TransId] 

    INNER  JOIN [dbo].[OCRD] T2  ON  T0.[ShortName] = T2.[CardCode] 

    where T0.[TransType] = 30 and T0.[BalDueDeb] > 0

   

   UNION  ALL

  

  -- Baixa por conta.

    SELECT 'LC30' as Tipo

         , T0.[TransId] as 'ID'       

         , CAST( T0.[TransId] as varchar) as 'Documento'

         , '1 de 1' as 'Parcela'

         , T0.[TaxDate] as 'Dt.Emissão'

         , T0.[RefDate] as 'Dt.Entrada'

         , T0.[DueDate] as 'Dt.Vencimento'

         , T2.[CardCode] as 'Codigo PN'

         , T2.[CardName] as 'Nome PN'       

         , T0.[TransId]

         , T0.[Credit] as 'TotalTitulo'

         , T0.[BalDueCred]  as 'TotalSaldo'

         , T0.[LineMemo] as 'Observação'

         , T0.[ObjType]

         , 0 as BaseRef       

    FROM  [dbo].[JDT1] T0

    INNER  JOIN [dbo].[OJDT] T1  ON  T0.[TransId] = T1.[TransId] 

    INNER  JOIN [dbo].[OCRD] T2  ON  T0.[ShortName] = T2.[CardCode] 

    where  T0.[TransType] = 30 and T0.[BalDueCred] <> 0

    

   UNION  ALL

    -- Boletos

    SELECT 'BOL' as Tipo

         , T0.[BoeNum] as 'ID'       

         , ('BOL/'+ CAST( T0.[BoeNum] as varchar)) as 'Documento'

         , '1 de 1' as 'Parcela'

         , T0.[Pmntdate] as 'Dt.Emissão'

         , T0.[Pmntdate] as 'Dt.Entrada'

         , T0.[DueDate] as 'Dt.Vencimento'

         , T0.[CardCode] as 'Codigo PN'

         , T0.[CardName] as 'Nome PN'       

         , T0.[BoeNum]

         , T0.[BoeSum] as 'TotalTitulo'

         , T0.[BoeSum] as 'TotalSaldo'

--       , 'Boleto' as 'Observação' -- agora mostra o número das notas fiscais de saída (somente objtype=13 por enquanto)

, 'Observação' = ( select        

            ( ISNULL((SELECT TOP 1 T14.NfmName from ONFM T14 WHERE T14.AbsEntry = T12.Model),'') + ' / ' + T12.SeriesStr + ' / ' + CAST(T12.SERIAL AS VARCHAR) ) + ', '

            from ORCT T10

            INNER JOIN RCT2 T11 ON T11.DocNum = T10.DocEntry

            INNER JOIN OINV T12 ON T12.DocEntry = T11.DocEntry and T12.ObjType = T11.InvType

            WHERE T10.BoeAbs = T0.BoeKey

            FOR XML PATH('')

            )

         , 0 as 'ObjType'

         , T0.RefNum as BaseRef

    FROM  [dbo].[OBOE] T0

    where T0.BoeType = 'I' and (T0.BoeStatus = 'G' or T0.BoeStatus = 'S')

Contas Recebidas


select T0.DocNum as 'NumBaixa', T0.DocEntry as 'NumNR', T0.SumApplied as 'ValorBaixa', T1.DocDate as 'DataBaixa',

          CASE

           WHEN T0.InvType = 13 THEN (SELECT MAX(T3.BaseRef) FROM INV1 T3 WHERE T3.DocEntry = T0.DocEntry)

          END as 'BaseRef',

     CASE

           WHEN T0.InvType = 13 THEN (SELECT TOP 1 ( ISNULL((SELECT TOP 1 T4.NfmName from ONFM T4 WHERE T4.AbsEntry = T3.Model),'') + ' / ' + ISNULL(T3.SeriesStr,'') + ' / ' + CAST(T3.Serial AS VARCHAR) )

                                 FROM OINV T3 WHERE T3.DocNum = T0.DocEntry  )

           WHEN T0.InvType = 30 THEN 'LC /' + CAST(T0.DocNum AS VARCHAR)

     END as 'Documento',

     CASE

        WHEN T0.InvType = 13 THEN

                (CAST( T0.[DocLine] + 1 as varchar) + ' de ' +

                (SELECT CAST(MAX(T3.[InstlmntID]) AS VARCHAR) FROM [dbo].[INV6] T3 WHERE T3.[DocEntry] = T0.[DocEntry]) )

        WHEN T0.InvType = 30 THEN '1 de 1'

     END as 'Parcela',

     CASE

           WHEN T0.InvType = 13 THEN (SELECT TOP 1 T3.[TaxDate] FROM OINV T3 WHERE T3.DocNum = T0.DocEntry)

           WHEN T0.InvType = 30 THEN (SELECT TOP 1 T3.[TaxDate] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)

     END as 'DataEmissao',

   

     CASE

           WHEN T0.InvType = 13 THEN (SELECT TOP 1 T3.[DocDate] FROM OINV T3 WHERE T3.DocNum = T0.DocEntry)

           WHEN T0.InvType = 30 THEN (SELECT TOP 1 T3.[RefDate] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)

     END as 'DataEntrada',

     CASE

           WHEN T0.InvType = 13 THEN (SELECT TOP 1 T3.[DueDate] FROM INV6 T3 WHERE T3.DocEntry = T0.DocEntry and T3.InstlmntID = T0.DocLine + 1)

           WHEN T0.InvType = 30 THEN (SELECT TOP 1 T3.[DueDate] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)

     END as 'DataVencimento',

   

     CASE

           WHEN T0.InvType = 13 THEN (SELECT TOP 1 T3.[InsTotal] FROM INV6 T3 WHERE T3.DocEntry = T0.DocEntry and T3.InstlmntID = T0.DocLine + 1)

           WHEN T0.InvType = 30 THEN (SELECT TOP 1 T3.[LocTotal] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)

     END as 'ValorDocumento',

   

      (ISNULL( (SELECT TOP 1 T3.AcctName from OACT T3 WHERE T3.AcctCode = T1.TrsfrAcct and T1.TrsfrSum <> 0 ) + ', ', '') +

       ISNULL( (SELECT TOP 1 T3.AcctName from OACT T3 WHERE T3.AcctCode = T1.CashAcct and T1.CashSum <> 0) + ', ', '') +

       ISNULL( (SELECT TOP 1 T4.AcctName from VPM1 T3 INNER JOIN OACT T4 ON (T4.AcctCode = T3.CheckAct) WHERE T3.DocNum = T1.DocNum and T1.CheckSum > 0 )+ ', ', '' ))

      as 'TipoBaixa',

      isnull((select max(T4.ExtrMatch) from JDT1 T4 where (T4.Transid = T1.Transid and T4.ExtrMatch <> 0) ),0) as 'Reconciliacao',

      (ISNULL( (SELECT TOP 1 T3.Finanse from OACT T3 WHERE T3.AcctCode = T1.TrsfrAcct and T1.TrsfrSum <> 0 ) + ', ', '') +

       ISNULL( (SELECT TOP 1 T3.Finanse from OACT T3 WHERE T3.AcctCode = T1.CashAcct and T1.CashSum <> 0) + ', ', '') +

       ISNULL( (SELECT TOP 1 T4.Finanse from VPM1 T3 INNER JOIN OACT T4 ON (T4.AcctCode = T3.CheckAct) WHERE T3.DocNum = T1.DocNum and T1.CheckSum > 0 )+ ', ', '' ))

      as 'ContaFinanceira',

     

     T1.TrsfrAcct,

     T1.CashAcct,

     T1.CheckAcct,

     T1.CardCode,

     T5.U_UpCodAnt,

     T1.CardName,   

     T1.BoeNum

    

FROM RCT2 T0

INNER JOIN ORCT T1 ON T1.DocEntry = T0.DocNum

LEFT JOIN OCRD T5 ON T5.CardCode = T1.CardCode

WHERE T1.Canceled = 'N' AND T1.BoeStatus is null

order by T0.DocNum desc

Contas à Pagar


SELECT

     'NE' as 'Tipo'  

    , MAX(T0.[DocNum]) as 'ID'  

    ,( (SELECT TOP 1 T4.NfmName from ONFM T4 WHERE T4.AbsEntry = T0.Model) + ' / ' + t0.SeriesStr + ' / ' + CAST(T0.SERIAL AS VARCHAR) ) as 'Documento'

  

    , (CAST( MAX(T1.[InstlmntID]) as varchar) + ' de ' +

       (SELECT CAST(MAX(T3.[InstlmntID]) AS VARCHAR) FROM [dbo].[PCH6] T3 WHERE T3.[DocEntry] = T0.[DocEntry]) ) as Parcela

    , MAX(T0.[TaxDate]) as 'Dt.Emissão'

    , MAX(T0.[DocDate]) as 'Dt.Entrada'  

    , MAX(T1.[DueDate]) as 'Dt.Vencimento'

    , MAX(T0.[CardCode]) as 'Codigo PN'

    , MAX(T0.[CardName]) as 'Nome PN'

    , MAX(T0.[TransId]) as TransId

    , MAX(T1.[InsTotal]) as 'TotalTitulo'

    , ( MAX(T1.[InsTotal]) - MAX(T1.[PaidToDate]) ) as 'TotalSaldo'

    , ISNULL(MAX(T0.[Comments]),'') as 'Observação'

    , T0.[ObjType]

    , (SELECT MAX(T3.BaseRef) FROM PCH1 T3 WHERE T3.DocEntry = T0.DocEntry) as BaseRef

    FROM  [dbo].[OPCH] T0

    INNER  JOIN [dbo].[PCH6] T1  ON  T1.[DocEntry] = T0.[DocEntry]  

    LEFT OUTER  JOIN [dbo].[PCH5] T2  ON  T0.[DocEntry] = T2.[AbsEntry]

    WHERE T1.[TotalBlck] <> T1.[InsTotal] AND

          (T1.[InsTotal] - T1.[PaidToDate]) <> 0

     GROUP BY T0.[DocEntry], T1.[InstlmntID], T0.[ObjType], T0.Model, T0.SeriesStr, T0.Serial

UNION ALL

  

    SELECT 'LC46' as Tipo

         , T0.[TransId] as 'ID'       

         , CAST( T0.[TransId] as varchar) as 'Documento'

         , '1 de 1' as 'Parcela'

         , T0.[TaxDate] as 'Dt.Emissão'

         , T0.[RefDate] as 'Dt.Entrada'

         , T0.[DueDate] as 'Dt.Vencimento'

         , T2.[CardCode] as 'Codigo PN'

         , T2.[CardName] as 'Nome PN'       

         , T0.[TransId]

         , (T0.[Debit] * -1) as 'TotalTitulo'

         , (T0.[BalDueDeb] * -1) as 'TotalSaldo'

         , T0.[LineMemo] as 'Observação'

         , T0.[ObjType]

         , 0 as BaseRef

    FROM  [dbo].[JDT1] T0

    INNER  JOIN [dbo].[OJDT] T1  ON  T0.[TransId] = T1.[TransId] 

    INNER  JOIN [dbo].[OCRD] T2  ON  T0.[ShortName] = T2.[CardCode] 

    where T0.[TransType] = 46 and T0.[BalDueDeb] < 0

UNION ALL

      SELECT 'LC30' as Tipo

         , T0.[TransId] as 'ID'       

         , CAST( T0.[TransId] as varchar) as 'Documento'

         , '1 de 1' as 'Parcela'

         , T0.[TaxDate] as 'Dt.Emissão'

         , T0.[RefDate] as 'Dt.Entrada'

         , T0.[DueDate] as 'Dt.Vencimento'

         , T2.[CardCode] as 'Codigo PN'

         , T2.[CardName] as 'Nome PN'       

         , T0.[TransId]

         , T0.[Credit] as 'TotalTitulo'

         , T0.[BalDueCred]  as 'TotalSaldo'

         , T0.[LineMemo] as 'Observação'

         , T0.[ObjType]

         , 0 as BaseRef

    FROM  [dbo].[JDT1] T0

    INNER  JOIN [dbo].[OJDT] T1  ON  T0.[TransId] = T1.[TransId] 

    INNER  JOIN [dbo].[OCRD] T2  ON  T0.[ShortName] = T2.[CardCode] 

    where T0.[TransType] = 30 and T0.[BalDueCred] <> 0

   

UNION ALL

      SELECT 'LC30' as Tipo

         , T0.[TransId] as 'ID'       

         , CAST( T0.[TransId] as varchar) as 'Documento'

         , '1 de 1' as 'Parcela'

         , T0.[TaxDate] as 'Dt.Emissão'

         , T0.[RefDate] as 'Dt.Entrada'

         , T0.[DueDate] as 'Dt.Vencimento'

         , T2.[CardCode] as 'Codigo PN'

         , T2.[CardName] as 'Nome PN'       

         , T0.[TransId]

         , T0.[Credit] as 'TotalTitulo'

         , T0.[BalDueCred]  as 'TotalSaldo'

         , T0.[LineMemo] as 'Observação'

         , T0.[ObjType]

         , 0 as BaseRef       

    FROM  [dbo].[JDT1] T0

    INNER  JOIN [dbo].[OJDT] T1  ON  T0.[TransId] = T1.[TransId] 

    INNER  JOIN [dbo].[OCRD] T2  ON  T0.[ShortName] = T2.[CardCode] 

    where T0.[TransType] = 30 and T0.[BalDueCred] <> 0

UNION ALL

SELECT

     'DE' as 'Tipo'  

    , MAX(T0.[DocNum]) as 'ID'  

    ,( ISNULL((SELECT TOP 1 T4.NfmName from ONFM T4 WHERE T4.AbsEntry = T0.Model),'') + ' / ' + t0.SeriesStr + ' / ' + CAST(T0.SERIAL AS VARCHAR) ) as 'Documento'

    , (CAST( MAX(T1.[InstlmntID]) as varchar) + ' de ' +

       (SELECT CAST(MAX(T3.[InstlmntID]) AS VARCHAR) FROM [dbo].[PCH6] T3 WHERE T3.[DocEntry] = T0.[DocEntry]) ) as Parcela

    , MAX(T0.[TaxDate]) as 'Dt.Emissão'

    , MAX(T0.[DocDate]) as 'Dt.Entrada'  

    , MAX(T1.[DueDate]) as 'Dt.Vencimento'

    , MAX(T0.[CardCode]) as 'Codigo PN'

    , MAX(T0.[CardName]) as 'Nome PN'

    , MAX(T0.[TransId]) as TransId

    , (MAX(T1.[InsTotal]) * -1) as 'TotalTitulo'

    , ( ( MAX(T1.[InsTotal]) - MAX(T1.[PaidToDate]) ) * -1) as 'TotalSaldo'

    , MAX(T0.[Comments]) as 'Observação'

    , T0.[ObjType]

    , (SELECT MAX(T3.BaseRef) FROM RPC1 T3 WHERE T3.DocEntry = T0.DocEntry) as BaseRef

    FROM  [dbo].[ORPC] T0

    INNER  JOIN [dbo].[RPC6] T1  ON  T1.[DocEntry] = T0.[DocEntry]  

    LEFT OUTER  JOIN [dbo].[RPC5] T2  ON  T0.[DocEntry] = T2.[AbsEntry]

    WHERE T1.[TotalBlck] <> T1.[InsTotal] AND

          (T1.[InsTotal] - T1.[PaidToDate]) <> 0

    GROUP BY T0.[DocEntry], T1.[InstlmntID], T0.[ObjType], T0.Model, T0.SeriesStr, T0.Serial

Contas pagas (este é legal, sai os números dos cheques recebidos separados por virgula, comando XML PATH)



select T0.DocNum as 'NumBaixa', T0.DocEntry as 'NumNR', T0.SumApplied as 'ValorBaixa', T1.DocDate as 'DataBaixa',

          CASE

           WHEN T0.InvType = 18 THEN (SELECT MAX(T3.BaseRef) FROM PCH1 T3 WHERE T3.DocEntry = T0.DocEntry)

           WHEN T0.InvType = 204 THEN (SELECT MAX(T3.BaseRef) FROM DPO1 T3 WHERE T3.DocEntry = T0.DocEntry)

          END as 'BaseRef',

     CASE

           WHEN T0.InvType = 18  THEN (SELECT TOP 1 ( ISNULL((SELECT TOP 1 T4.NfmName from ONFM T4 WHERE T4.AbsEntry = T3.Model),'') + ' / ' + ISNULL(T3.SeriesStr,'') + ' / ' + CAST(T3.Serial AS VARCHAR) )

                                 FROM OPCH T3 WHERE T3.DocNum = T0.DocEntry  )

           WHEN T0.InvType = 204 THEN 'ADT/' + CAST(T0.DocNum AS VARCHAR)

           WHEN T0.InvType = 46  THEN 'LC/' + CAST(T0.DocNum AS VARCHAR)

           WHEN T0.InvType = 30  THEN 'LC/' + CAST(T0.DocNum AS VARCHAR) + ' / ' + (SELECT TOP 1 T3.[Memo] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)

     END as 'Documento',

     CASE

        WHEN T0.InvType = 18 THEN

                (CAST( T0.[DocLine] + 1 as varchar) + ' de ' +

                (SELECT CAST(MAX(T3.[InstlmntID]) AS VARCHAR) FROM [dbo].[PCH6] T3 WHERE T3.[DocEntry] = T0.[DocEntry]) )

        WHEN T0.InvType = 204 THEN

                (CAST( T0.[DocLine] + 1 as varchar) + ' de ' +

                (SELECT CAST(MAX(T3.[InstlmntID]) AS VARCHAR) FROM [dbo].[DPO6] T3 WHERE T3.[DocEntry] = T0.[DocEntry]) )

        WHEN T0.InvType = 46 THEN '1 de 1'

        WHEN T0.InvType = 30 THEN '1 de 1'

     END as 'Parcela',

     CASE

           WHEN T0.InvType = 18  THEN (SELECT TOP 1 T3.[TaxDate] FROM OPCH T3 WHERE T3.DocNum = T0.DocEntry)

           WHEN T0.InvType = 204 THEN (SELECT TOP 1 T3.[TaxDate] FROM ODPO T3 WHERE T3.DocNum = T0.DocEntry)         

           WHEN T0.InvType = 46  THEN (SELECT TOP 1 T3.[TaxDate] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)

           WHEN T0.InvType = 30  THEN (SELECT TOP 1 T3.[TaxDate] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)

     END as 'DataEmissao',

   

     CASE

           WHEN T0.InvType = 18  THEN (SELECT TOP 1 T3.[DocDate] FROM OPCH T3 WHERE T3.DocNum = T0.DocEntry)

           WHEN T0.InvType = 204 THEN (SELECT TOP 1 T3.[DocDate] FROM ODPO T3 WHERE T3.DocNum = T0.DocEntry)         

           WHEN T0.InvType = 46  THEN (SELECT TOP 1 T3.[RefDate] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)

           WHEN T0.InvType = 30  THEN (SELECT TOP 1 T3.[RefDate] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)

     END as 'DataEntrada',

     CASE

           WHEN T0.InvType = 18  THEN (SELECT TOP 1 T3.[DueDate] FROM PCH6 T3 WHERE T3.DocEntry = T0.DocEntry and T3.InstlmntID = T0.DocLine + 1)

           WHEN T0.InvType = 204 THEN (SELECT TOP 1 T3.[DueDate] FROM DPO6 T3 WHERE T3.DocEntry = T0.DocEntry and T3.InstlmntID = T0.DocLine + 1)         

           WHEN T0.InvType = 46  THEN (SELECT TOP 1 T3.[DueDate] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)

           WHEN T0.InvType = 30  THEN (SELECT TOP 1 T3.[DueDate] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)

     END as 'DataVencimento',

   

     CASE

           WHEN T0.InvType = 18  THEN (SELECT TOP 1 T3.[InsTotal] FROM PCH6 T3 WHERE T3.DocEntry = T0.DocEntry and T3.InstlmntID = T0.DocLine + 1)

           WHEN T0.InvType = 204 THEN (SELECT TOP 1 T3.[InsTotal] FROM DPO6 T3 WHERE T3.DocEntry = T0.DocEntry and T3.InstlmntID = T0.DocLine + 1)

           WHEN T0.InvType = 46  THEN (SELECT TOP 1 T3.[LocTotal] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)

           WHEN T0.InvType = 30  THEN (SELECT TOP 1 T3.[LocTotal] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)

     END as 'ValorDocumento',

   

      (ISNULL( (SELECT TOP 1 T3.AcctName from OACT T3 WHERE T3.AcctCode = T1.TrsfrAcct and T1.TrsfrSum <> 0 ) + ', ', '') +

       ISNULL( (SELECT TOP 1 T3.AcctName from OACT T3 WHERE T3.AcctCode = T1.CashAcct and T1.CashSum <> 0) + ', ', '') +

       ISNULL( (SELECT TOP 1 T4.AcctName from VPM1 T3 INNER JOIN OACT T4 ON (T4.AcctCode = T3.CheckAct) WHERE T3.DocNum = T1.DocNum and T1.CheckSum > 0 )+ ', ', '' ))

      as 'TipoBaixa',

      isnull((select max(T4.ExtrMatch) from JDT1 T4 where (T4.Transid = T1.Transid and T4.ExtrMatch <> 0) ),0) as 'Reconciliacao',

      (ISNULL( (SELECT TOP 1 T3.Finanse from OACT T3 WHERE T3.AcctCode = T1.TrsfrAcct and T1.TrsfrSum <> 0 ) + ', ', '') +

       ISNULL( (SELECT TOP 1 T3.Finanse from OACT T3 WHERE T3.AcctCode = T1.CashAcct and T1.CashSum <> 0) + ', ', '') +

       ISNULL( (SELECT TOP 1 T4.Finanse from VPM1 T3 INNER JOIN OACT T4 ON (T4.AcctCode = T3.CheckAct) WHERE T3.DocNum = T1.DocNum and T1.CheckSum > 0 )+ ', ', '' ))

      as 'ContaFinanceira',

     T1.TrsfrAcct,

     T1.CashAcct,

     T1.CheckAcct,

     T1.CardCode,

     T1.CardName,

     T0.InvType,

Cheques = (Select CAST(T11.CheckNum as varchar(10)) + ', ' FROM VPM1 T11 WHERE T11.DocNum = T0.DocNum for XML PATH(''))

    

FROM VPM2 T0

INNER JOIN OVPM T1 ON T1.DocEntry = T0.DocNum

WHERE T1.Canceled = 'N'

Se for usar as consultas, por favor identifique a origem.

Abraço a todos

Fabio.

SAP 8.82 PL11

25 Comments
Labels in this area
Popular Blog Posts