Report - SD module

Former Member · ‎2006 Dec 23

Hi,

I am creating a report for SD module.

I have to fetch the billing docs for a given date and find the open sales orders & print the report.

I could fetch the billing docs...open sales order for the given date.

What is the relation (link) between the Billing and the sales orders. HOw will i find out that the Open sales order and the billing doc. belong to the sales order made by the customer.

Thank you

Former Member · ‎2006 Dec 23

Hi,

You can use table VBFA to get the link between a sales order-delivery-billing.

Total all the billing quantities for a given sale order, if the total = quantity in sales order, then no more delivery/billing can be made for that order.

Related tables....

VBAK "Sales order header

VBAP " SAles order item

LIKP "Delivery header

LIPS "Delivery item

VBRK "Billing header

VBRP "Billing item.

Former Member · ‎2006 Dec 23

Hi

Apart from the info provided by Jithendra, you have to consider VBUK/VBUP for open sales orders.

I guess the report should be giving two outputs

1. Billing Docs for given date: Details from VBRK & VBRP where VBRK-ERDAT is the date on the selection screen.

2. Open Sales Orders: VBUK/VBUP --->VBAK/VBAP.

Kind Regards

Eswar

Former Member · ‎2006 Dec 23

In Table VBFA, make use of the fields..

1.VBELV "Preceeding document number (order)

2.VBTYP_N "document type( use for For orders)

3.VBELN "Subsequent document number (billing)

4.VBTYP_V "document type( use for For billing)

Former Member · ‎2006 Dec 23

Relation between a sales order and a Billing document ( Invoice ).

After the sales order is processed then comes the stage of Delivery where the items under the sales order are processed , picked , packed and Pgi'ed (goods issue is completed) .

once this is done then an Invoice (billing document number is generated for that delivery )

This is the Sequence.

For ur understanding goto VF03 and give an invoice number as input and hit an enter.

Now in the screen at the left top corner in the select Document Flow (shift+F7) and click that .. This will show u the status of the sales order /delivery /invoice number.

sales order in VBAK---item VBAP

delivery in LIKP ---iteM LIPS

Invoice in VBRK---item VBRP

in order to capture the sales order as open or closed ..

You need to proceed like

take the sales order number and goto table VBUK and input the sales order number and check for field VBUK-GBSTK NE C

if its C then its Closed else if it is A or B then its open .

GBSTK--is the overall processing status for the order ..

U need to write a join for this or directly fetch the entry from table VBAKUK.

here is a sample code to fetch the open orders at header and item level.

execute the code to understand teh logic

REPORT ZEX2  MESSAGE-ID arc NO STANDARD PAGE HEADING.

Tables :kna1,vbak.

SELECT-OPTIONS : so_vkorg FOR  vbak-vkorg OBLIGATORY, "sales area
                 so_vtweg FOR  vbak-vtweg OBLIGATORY,
                 so_spart FOR  vbak-spart,                                  
                 so_kunnr FOR  kna1-kunnr.                        "ur customer  


DATA : BEGIN OF sales_open OCCURS 0 ,
       vbeln LIKE vbak-vbeln,
       auart LIKE vbak-auart,
       kunnr LIKE kna1-kunnr,
       bstnk LIKE vbak-bstnk,
       lfstk LIKE vbuk-lfstk,
       fkstk LIKE vbuk-fkstk,
       gbstk LIKE vbuk-gbstk,
       END OF sales_open.

DATA : BEGIN OF itm_sales OCCURS 0,
       vbeln LIKE vbap-vbeln,
       posnr LIKE vbap-posnr,
       matnr LIKE vbap-matnr,
       kwmeng like vbap-kwmeng,
       lfsta LIKE vbup-lfsta,
       lfgsa LIKE vbup-lfgsa,
       fksta LIKE vbup-fksta,
       fksaa LIKE vbup-fksaa,
       gbsta LIKE vbup-gbsta,
       END OF itm_sales.
***
DATA : l_kunnr LIKE kna1-kunnr,
       l_vkorg LIKE vbak-vkorg,
       l_vtweg LIKE vbak-vtweg,
       l_spart LIKE vbak-spart.


DATA: v_statusl(20) TYPE c,
      v_statusb(20) TYPE c,
      v_statusf(20) TYPE c,
      v_statusg(20) TYPE c,
      v_status(20) TYPE c,
      v_field(1) TYPE c.

data : v_openqty like vbap-kwmeng.

**Selection Screen Validations.
AT SELECTION-SCREEN.
  PERFORM validations.
*&---------------------------------------------------------------------*
*&      Form  Validations
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM validations.

**Customer
  IF NOT so_kunnr[] IS INITIAL.
    SELECT SINGLE kunnr INTO l_kunnr
           FROM kna1
           WHERE kunnr IN so_kunnr.
    IF sy-subrc NE 0.
      MESSAGE e002 WITH text-005.
    ENDIF.
  ENDIF.
**Sales Organization
  IF NOT so_vkorg[] IS INITIAL.
    SELECT SINGLE vkorg INTO l_vkorg
           FROM tvko
           WHERE vkorg IN so_vkorg.
    IF sy-subrc NE 0.
      MESSAGE e003 WITH text-006.
    ENDIF.
  ENDIF.
**Distribution Channel
  IF NOT so_vtweg[] IS INITIAL.
    SELECT SINGLE vtweg INTO l_vtweg
            FROM tvkov
            WHERE   vkorg IN so_vkorg
             AND    vtweg IN so_vtweg.
    IF sy-subrc NE 0.
      MESSAGE e004 WITH text-007.
    ENDIF.
  ENDIF.
**Division
  IF NOT so_spart[] IS INITIAL.
    SELECT SINGLE spart INTO l_spart
            FROM tvta
            WHERE   vkorg IN so_vkorg
            AND     vtweg IN so_vtweg
            AND     spart IN so_spart.
    IF sy-subrc NE 0.
      MESSAGE e005 WITH text-008.
    ENDIF.
  ENDIF.

ENDFORM.                    " Validations

Top-of-page.
PERFORM sales_top_of_page.

Start-of-selection.
PERFORM sales_sel.


*&---------------------------------------------------------------------*
*&      Form  sales_sel
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM sales_sel.

SELECT vbeln auart kunnr bstnk
     lfstk fkstk gbstk
     INTO TABLE sales_open
     FROM vbakuk
     WHERE vkorg IN so_vkorg
     AND   vtweg IN so_vtweg
     AND   spart IN so_spart
     AND   kunnr IN so_kunnr
     AND gbstk NE 'C'.

  LOOP AT sales_open.


    WRITE:/4 sy-vline,
           5 sales_open-vbeln HOTSPOT ON COLOR 2 INTENSIFIED OFF,
           16 sy-vline,
           17 sales_open-auart COLOR 2 INTENSIFIED OFF,
           27 sy-vline,
           28 sales_open-kunnr COLOR 2 INTENSIFIED OFF,
           40 sy-vline,
           41 sales_open-bstnk COLOR 2 INTENSIFIED OFF,
           55 sy-vline,
           56 sales_open-lfstk,
           76 sy-vline,
           77 sales_open-fkstk,
           96 sy-vline,
           97 sales_open-gbstk ,
           117 sy-vline.

    HIDE sales_open-vbeln .
  ENDLOOP.

ENDFORM.                    " sales_sel


*&---------------------------------------------------------------------*
*&      Form  sales_top_of_page
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM sales_top_of_page.

  WRITE:/4 sy-uline(114),
     50 'OPEN SALES ORDERS' COLOR 7 INTENSIFIED ON .

  WRITE: /4 sy-vline,
          5 'SalesOrder' COLOR 1 ,
          16 sy-vline,
         17  'OrderType' COLOR 1,
         27  sy-vline,
         28  'Customer' COLOR 1,
         40  sy-vline,
         41  'PoNumber' COLOR 1,
         55  sy-vline,
         56  'Delivery Status' COLOR 1,
         76  sy-vline,
         77  'Billing Status' COLOR 1,
         96  sy-vline,
         97  'Processing Status' COLOR 1,
         117  sy-vline .
  WRITE:/4 sy-uline(114).

ENDFORM.                    " sales_top_of_page


AT LINE-SELECTION.

  SELECT       a~vbeln
               a~posnr
               a~matnr
               a~kwmeng
               b~lfsta
               b~lfgsa
               b~fksta
               b~fksaa
               b~gbsta
               INTO TABLE itm_sales
               FROM vbap AS a JOIN vbup AS b
               ON a~vbeln EQ b~vbeln
               AND a~posnr EQ b~posnr
               AND b~gbsta NE 'C'
               WHERE a~vbeln EQ sales_open-vbeln.


  IF NOT sales_open IS INITIAL.
    LOOP AT itm_sales.

      at end of vbeln .
      sum.
      v_openqty = itm_sales-kwmeng.

      endat.

      WRITE:/5  itm_sales-vbeln,
                itm_sales-posnr,
                itm_sales-matnr,
                itm_sales-kwmeng,
                itm_sales-lfsta,
                itm_sales-lfgsa,
                itm_sales-fksta,
                itm_sales-fksaa,
                itm_sales-gbsta.



    ENDLOOP.
  ENDIF.
 skip 2.
  write:/  'open Quantity for the order is ', v_openqty .

in order to fetch the invoice number from sales order ..

goto table VBFA and give the input as sales order number in Preceding document no(VBELV) and in the Document category of subsequent document (VBTYP_N) give it as caps 'M'

for fetching delivery from sales order same as above with vbtyp_n = 'J'.

regards,

vijay