how we will select all the invoices from table BSIK AND BSAK

[Former Member]

Hi,

how we will select all the invoices for vendors based on the date from table BSIK AND BSAK ?

wether we should use inner join for it.if yes then how ?

[mvoros]

the table BSIK contains all open items for vendors and BSAK contains all closed items for vendors. Hence you need to union the records from these two tables. Just use one select for each table.

Cheers

[Former Member]

If you want the select open item and closed item in one selection, you can select directrly to BKPF and BSEG table, use the document type as selection parameter.

[Former Member]

If you want all invoices, you ca use seperate select for both tables and a single internal table as shown in the below example.

SELECT bukrs
         lifnr
         gjahr
         belnr
         buzei
         blart
         monat
         shkzg
         dmbtr
         hkont
         prctr FROM bsak
               INTO TABLE gt_bsak
               WHERE bukrs IN so_bukrs
               and lifnr in r_vcode
               and gjahr = p_gjahr
               AND blart IN ('BP', 'MB', 'ZC', 'AB')
               AND monat EQ p_monat.

  SELECT bukrs
         lifnr
         gjahr
         belnr
         buzei
         blart
         monat
         shkzg
         dmbtr
         hkont
         prctr FROM bsik
               APPENDING TABLE gt_bsak
               WHERE bukrs IN so_bukrs
               and lifnr in r_vcode
               and gjahr = p_gjahr
               AND blart IN ('BP', 'MB', 'ZC', 'AB')
               AND monat EQ p_monat.

Regards

Sathar

[Former Member]

This is how I fetched the necessary open/cleared items from the tables BSIK and BSAK,

FORM get_invoices_for_date_range.

    SELECT xblnr                       " Reference document
      INTO (w_xblnr)
      FROM bsik
     WHERE lifnr IN s_lifnr
       AND budat IN s_wadat.

      IF w_prev_xblnr NE w_xblnr.
        w_prev_xblnr = w_xblnr.
        w_status = c_status_op.
* To filter the invoices.
        PERFORM filter_invoices_vbrk.
      ENDIF.                           " IF W_PREV_XBLNR NE...

    ENDSELECT.

    SELECT xblnr                       " Reference document
      INTO (w_xblnr)
      FROM bsak
     WHERE lifnr IN s_lifnr
       AND budat IN s_wadat.

      IF w_prev_xblnr NE w_xblnr.
        w_prev_xblnr = w_xblnr.
        w_status = c_status_cl.
* To filter the invoices.
        PERFORM filter_invoices_vbrk.
      ENDIF.                           " IF W_PREV_XBLNR NE...

    ENDSELECT.

  IF t_invc IS INITIAL.
    MESSAGE i888(sabapdocu)
       WITH text-nod.
    LEAVE TO TRANSACTION sy-tcode.
  ENDIF.                               " IF T_INVC IS...

ENDFORM.                               " GET_INVOICES_FOR_DATE_RANGE

*----------------------------------------------------------------------*
*  FORM FILTER_INVOICES_VBRK                                         *
*----------------------------------------------------------------------*
*  Subroutine for filtering invoicess                                              *
*----------------------------------------------------------------------*
*  There are no interface parameters to be passed to this subroutine.  *
*----------------------------------------------------------------------*
FORM filter_invoices_vbrk..

  SELECT vbeln                         " Invoice doc.
    INTO (w_invc)
    FROM vbrk
   WHERE vbeln EQ w_xblnr.

     fs_invc-vbeln = w_invc.
    fs_invc-status = w_status.
    APPEND fs_invc TO t_invc.

  ENDSELECT.

ENDFORM.                               " FILTER_INVOICES_VBRK

[Former Member]

Hi Iqbal,

The accounting document Header table BKPF is having a field called AWKEY with is the combination of invoice document and year.

from that AWKEY you can find out for which vendor invoice an accounting document is prepared or not.

BKPF_{AWKEY --> RBKP}BELNR.

these may help you.

comment on it.

regards

Kumar M.