Skip to Content
avatar image
Former Member

SQL Report of Sales Orders Pending (Billing)

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_OVFrom                                sapsr3.vbap   vbap                              ,sapsr3.vbrp   vbrp                              ,sapsr3.vbfa   vbfaWhere    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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Feb 27, 2013 at 03:14 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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