SQL Report of Sales Orders Pending (Billing)

[Former Member]

Hello,

I'm a Business Intelligence Analyst working with QlikView and a SAP SR3 Database. I'm trying to find a way to show the sales orders that are pending(billing), but until now it has been very difficult to find a way to do it, plus i don't have a SAP Analyst to help me on this.

My application already has a query that shows Sales information, but the actual pending status is not been retrieved in the right way.

That's the query that i'm working right now:

Select distinct vbap.vbeln "Ordem de Venda Valor", --Sales Order ID
             vbap.matnr "Material",--Material
             nvl(case when sum(case when vbap.zmeng > 0 then vbap.zmeng else vbap.kwmeng end) <> sum(nvl(case when vbfa.vbtyp_n = 'N' then vbrp.fkimg*-1 else vbrp.fkimg end, 0)) 
                                             then (vbap.netwr + vbap.mwsbp) - sum(nvl(case when vbfa.vbtyp_n = 'N' then (vbrp.netwr + vbrp.mwsbp)*-1 else vbrp.netwr + vbrp.mwsbp end, 0)) 
                       end, 
                       0) SALDO_-OV  -- Balance
      ,vbap.posnr "Item_Fat"
      ,case when nvl(sum(case when vbap.zmeng > 0 then vbap.zmeng else vbap.kwmeng end), 0)  >  sum(nvl(case when vbfa.vbtyp_n = 'N' then vbfa.rfmng*-1 else vbfa.rfmng end, 0)) 
            then 'Pendente' -- Billing Pending Status
            else 'Concluido' -- Billing Concluded Status
       end                                     STATUS_FAT_OV
From 
                               sapsr3.vbap   vbap
                              ,sapsr3.vbrp   vbrp
                              ,sapsr3.vbfa   vbfa
Where    vbrp.mandt (+)= vbap.mandt
           And vbrp.aubel (+)= vbap.vbeln
           And vbrp.aupos (+)= vbap.posnr 
           And vbfa.vbelv (+)= vbrp.vbeln
           And vbfa.mandt (+)= vbrp.mandt
           And vbfa.posnv (+)= vbrp.posnr
           And vbap.abgru not in ('19', '20', '21')            
           And vbrp.vbeln not in ( Select vb2.vbelv
                                                                                From sapsr3.VBFA vb2
                                                                         Where vb2.mandt = vbrp.mandt 
                                                                           and vb2.vbelv = vbrp.vbeln 
                                                                           and vb2.posnv = vbrp.posnr 
                                                                           and vb2.VBTYP_N = 'N' 
                                                                           and vb2.VBTYP_V = 'M')
          And vbrp.aubel not in (select ff.vbelv
                                                                       From sapsr3.vbfa ff
                                                                      Where ff.vbelv >= '0020000000'
                                                                        And ff.vbelv < '0040000000'
                                                                        And ff.vbelv = vbrp.vbeln 
                                                                        And ff.posnv = vbrp.posnr 
                                                                        And ff.mandt = vbrp.mandt)
Group By  vbap.vbeln
                     ,vbap.matnr
                     ,vbap.posnr
                     ,vbap.zmeng
                     ,vbap.kwmeng 
                     ,vbrp.fkimg 
                     ,vbap.netwr
                     ,vbap.mwsbp 
                     ,vbrp.netwr 
                     ,vbrp.mwsbp;

I would appreciate some help on this quest. I'm really newbie on SAP tables, so maybe it's everything wrong =/

Obs: Sorry for my bad English.

[former_member206632 · Accepted solution]

Hello Thiago (Olá Thiago),

Should I assume that your order type is not supposed to have deliveries made with reference to it? Is this a Sales Order -> Billing Document flow?

Regards,

João

[Former Member · Accepted solution]

João,

Sim seria esse o caminho da Ordem.

Eu não entendo muito do trabalho com o SAP, na verdade eu mexo muito pouco nessa parte, pois o meu desenvolvimento é no Qlikview, utilizando o banco de dados do SAP.

Muito obrigado pela atenção!

[former_member206632 · Accepted solution]

Hello Thiago,

I would like to reply in portuguese as well but I imagine that could be against the rules of SCN. So I will give you a list useful tables that you can use to meet your requirement:

VBAK: Sales Order Header

VBAP: Sales Order Items

VBUK: Status of sales documents at the header level

VBUP: Status of sales documents at the item level

VBFA: Document flow

I think that with VBUK-FKSTK only you will get there:

= 'C' => SO already billed

<> 'C' => Billing yet to be done

Abraço,

João