report

Former Member · ‎10-11-2007

hi i want a report based on tables BSEG and bkpf the selection screen will be based on year,doc type ,doc no, and business area.

the output should have doc no, no of line item,business area doc type ,fiscal year ,debit/credit indicator and the amount in local currency

i also want the total and the grand total!!!

can u plss help me with the coding!!

points promised

Former Member · ‎10-11-2007

hi

REPORT ZFI_TDS_NEW MESSAGE-ID 8I NO STANDARD PAGE HEADING

LINE-SIZE 185.

*----

TABLES USED IN THIS REPORT -

*----

TABLES: LFA1,BKPF ,BSAK.

*----

*--

DATA DECLARATION--

*----

TYPES: BEGIN OF TY_MOVE,

LIFNR TYPE LFA1-LIFNR,

PANNO TYPE J_1IMOVEND-J_1IPANNO,

END OF TY_MOVE.

DATA: IT_MOVE TYPE TABLE OF TY_MOVE ,

WA_MOVE TYPE TY_MOVE.

TYPES: BEGIN OF TY_LFA1,

LIFNR TYPE LFA1-LIFNR,

NAME1 TYPE LFA1-NAME1,

END OF TY_LFA1.

DATA:IT_LFA1 TYPE TABLE OF TY_LFA1,

WA_LFA1 TYPE TY_LFA1.

TYPES: BEGIN OF TY_BSAK,

GJAHR TYPE BSAK-GJAHR,

BELNR TYPE BSAK-BELNR,

END OF TY_BSAK.

DATA: IT_BSAK TYPE TABLE OF TY_BSAK,

WA_BSAK TYPE TY_BSAK.

TYPES: BEGIN OF TY_BSEG,

BELNR TYPE BSEG-BELNR,

GJAHR TYPE BSEG-GJAHR,

WRBTR TYPE BSEG-WRBTR,

QSSKZ TYPE BSEG-QSSKZ,

END OF TY_BSEG.

DATA: IT_BSEG TYPE TABLE OF TY_BSEG,

WA_BSEG TYPE TY_BSEG.

TYPES: BEGIN OF TY_FIT,

LIFNR TYPE BSAK-LIFNR,

GJAHR TYPE BSAK-GJAHR,

BELNR TYPE BSAK-BELNR,

BLDAT TYPE BSAK-BLDAT,

XBLNR TYPE BSAK-XBLNR,

DMBTR TYPE BSAK-DMBTR,

END OF TY_FIT.

DATA: IT_FIT TYPE TABLE OF TY_FIT ,

WA_FIT TYPE TY_FIT.

TYPES: BEGIN OF TY_OUT,

BELNR TYPE BSAK-BELNR,

LIFNR TYPE LFA1-LIFNR,

NAME1 TYPE LFA1-NAME1,

PANNO(15) TYPE C, " TYPE j_1imovend-j_1ipanno,

BLDAT TYPE BSAK-BLDAT,

XBLNR TYPE BSAK-XBLNR,

DMBTR TYPE BSAK-DMBTR,

TDS(8), " type p, LIKE bseg-wrbtr,

SUR(8) , " type p, LIKE bseg-wrbtr,

ECESS(8), " type p, LIKE bseg-wrbtr,

HCESS(8), " type p, LIKE bseg-wrbtr,

NET LIKE BSEG-WRBTR,

END OF TY_OUT.

DATA: IT_OUT TYPE TABLE OF TY_OUT,

WA_OUT TYPE TY_OUT.

DATA: L_VENDOR TYPE LFA1-LIFNR,

FLAG(1).

*----

*--

SELECTION SCREEN--

*----

SELECTION-SCREEN BEGIN OF BLOCK 1 WITH FRAME TITLE TEXT-001.

SELECT-OPTIONS: ZVENDOR FOR LFA1-LIFNR OBLIGATORY,

ZDATE FOR BSAK-BLDAT.

PARAMETERS: ZBUKRS LIKE BSEG-BUKRS .

SELECTION-SCREEN END OF BLOCK 1.

DATA: ZDATE1 LIKE BSAK-BLDAT.

*----

*--

SELECTION SCREEN VALIDATION--

*----

AT SELECTION-SCREEN.

AT SELECTION-SCREEN ON ZVENDOR.

IF NOT ZVENDOR[] IS INITIAL.

SELECT SINGLE * FROM LFA1 WHERE LIFNR IN ZVENDOR.

IF SY-SUBRC <> 0.

MESSAGE E000(8I) WITH 'enter valid vendor'.

ENDIF.

AT SELECTION-SCREEN ON ZDATE.

IF NOT ZDATE[] IS INITIAL.

SELECT BLDAT FROM BSAK INTO ZDATE1 WHERE BLDAT IN ZDATE.

ENDSELECT.

IF SY-SUBRC <> 0.

MESSAGE E000(8I) WITH 'enter valid date'.

ENDIF.

AT SELECTION-SCREEN ON ZBUKRS.

IF NOT ZBUKRS IS INITIAL.

SELECT SINGLE BUKRS FROM BSEG INTO ZBUKRS WHERE BUKRS = ZBUKRS.

IF SY-SUBRC <> 0.

MESSAGE E000(8I) WITH 'enter valid company code'.

ENDIF.

TOP-OF-PAGE.

WRITE:/4 'VENDOR', 18 'NAME',53 'PANNO',68 'DOC DATE',80 'VEN.INV NO',96

'INV.AMT'

,110 'TDS',120 'SUR',134 'ECESS',142 'HCESS',157 'NET.AMT'

.

SKIP.

WRITE:/ SY-ULINE.

*----

*--

START-OF-SELECTION--

*----

START-OF-SELECTION.

SELECT GJAHR BELNR FROM BSAK INTO TABLE IT_BSAK WHERE

LIFNR IN ZVENDOR

AND BLDAT IN ZDATE

AND BUKRS = ZBUKRS.

IF NOT IT_BSAK[] IS INITIAL.

SELECT BELNR GJAHR WRBTR QSSKZ FROM BSEG INTO TABLE IT_BSEG

FOR ALL ENTRIES IN IT_BSAK

WHERE BELNR = IT_BSAK-BELNR

AND GJAHR = IT_BSAK-GJAHR

AND QSSKZ <> ''

AND BSCHL = '50'.

SORT IT_BSEG BY BELNR GJAHR.

IF NOT IT_BSEG[] IS INITIAL.

SELECT LIFNR GJAHR BELNR BLDAT XBLNR DMBTR FROM BSAK INTO TABLE

IT_FIT FOR ALL ENTRIES IN IT_BSEG WHERE BELNR = IT_BSEG-BELNR .

ENDIF.

LOOP AT IT_FIT INTO WA_FIT.

MOVE WA_FIT-LIFNR TO WA_OUT-LIFNR.

MOVE WA_FIT-BELNR TO WA_OUT-BELNR.

MOVE WA_FIT-BLDAT TO WA_OUT-BLDAT.

MOVE WA_FIT-XBLNR TO WA_OUT-XBLNR.

MOVE WA_FIT-DMBTR TO WA_OUT-DMBTR.

LOOP AT IT_BSEG INTO WA_BSEG WHERE BELNR = WA_FIT-BELNR.

IF WA_BSEG-QSSKZ = 'A5'.

MOVE WA_BSEG-WRBTR TO WA_OUT-TDS.

ELSE.

IF WA_BSEG-QSSKZ = 'W1' OR WA_BSEG-QSSKZ ='W5' OR

WA_BSEG-QSSKZ = 'X1' OR WA_BSEG-QSSKZ ='Y1' OR

WA_BSEG-QSSKZ = 'Z1' OR WA_BSEG-QSSKZ ='W2' OR

WA_BSEG-QSSKZ = 'W6' OR WA_BSEG-QSSKZ ='X2' OR

WA_BSEG-QSSKZ = 'Y2' OR WA_BSEG-QSSKZ ='Z2' OR

WA_BSEG-QSSKZ = 'W3' OR WA_BSEG-QSSKZ ='W7' OR

WA_BSEG-QSSKZ = 'X3' OR WA_BSEG-QSSKZ ='Y3' OR

WA_BSEG-QSSKZ = 'Z3' OR WA_BSEG-QSSKZ ='W4' OR

WA_BSEG-QSSKZ = 'W8' OR WA_BSEG-QSSKZ ='X4' OR

WA_BSEG-QSSKZ = 'Y4' OR WA_BSEG-QSSKZ ='Z4'.

MOVE WA_BSEG-WRBTR TO WA_OUT-ECESS.

ELSE.

IF WA_BSEG-QSSKZ = '1A' OR WA_BSEG-QSSKZ = '1E' OR

WA_BSEG-QSSKZ = '1J' OR WA_BSEG-QSSKZ = '1N' OR

WA_BSEG-QSSKZ = '1S' OR WA_BSEG-QSSKZ = '1B' OR

WA_BSEG-QSSKZ = '1F' OR WA_BSEG-QSSKZ = '1K' OR

WA_BSEG-QSSKZ = '1P' OR WA_BSEG-QSSKZ = '1U' OR

WA_BSEG-QSSKZ = '1C' OR WA_BSEG-QSSKZ = '1G' OR

WA_BSEG-QSSKZ = '1L' OR WA_BSEG-QSSKZ = '1Q' OR

WA_BSEG-QSSKZ = '1W' OR WA_BSEG-QSSKZ = '1D' OR

WA_BSEG-QSSKZ = '1H' OR WA_BSEG-QSSKZ = '1M' OR

WA_BSEG-QSSKZ = '1R' OR WA_BSEG-QSSKZ = '1Y'.

MOVE WA_BSEG-WRBTR TO WA_OUT-HCESS.

ENDIF.

CLEAR WA_BSEG.

ENDLOOP.

SELECT SINGLE LIFNR J_1IPANNO FROM J_1IMOVEND INTO WA_MOVE WHERE

LIFNR = WA_FIT-LIFNR.

MOVE WA_MOVE-PANNO TO WA_OUT-PANNO.

SELECT SINGLE LIFNR NAME1 FROM LFA1 INTO WA_LFA1 WHERE

LIFNR = WA_FIT-LIFNR.

MOVE WA_LFA1-NAME1 TO WA_OUT-NAME1.

WA_OUT-NET = WA_OUT-DMBTR - WA_OUT-TDS - WA_OUT-ECESS

- WA_OUT-HCESS .

APPEND WA_OUT TO IT_OUT.

CLEAR WA_OUT.

CLEAR WA_FIT.

ENDLOOP.

LOOP AT IT_OUT INTO WA_OUT.

IF WA_OUT-LIFNR = L_VENDOR.

FLAG = 'X'.

ELSE.

CLEAR FLAG.

ENDIF.

IF FLAG = 'X'.

WRITE:/68 WA_OUT-BLDAT,

80 WA_OUT-XBLNR,90 WA_OUT-DMBTR,107 WA_OUT-TDS,133

WA_OUT-ECESS, 141 WA_OUT-HCESS,151 WA_OUT-NET.

ELSE.

WRITE:/4 WA_OUT-LIFNR, 18 WA_OUT-NAME1,53 WA_MOVE-PANNO,

68 WA_OUT-BLDAT,

80 WA_OUT-XBLNR,90 WA_OUT-DMBTR,107 WA_OUT-TDS,133

WA_OUT-ECESS, 141 WA_OUT-HCESS,151 WA_OUT-NET.

ENDIF.

L_VENDOR = WA_OUT-LIFNR.

ENDLOOP.

if this useful , reward points

regrds

karthi

gopi_narendra · ‎10-11-2007

* Types declarations

* Types declaration for the table BKPF - Accounting Document Header

types: begin of TY_CRATIO_BKPF,
         BUKRS type BKPF-BUKRS,    " Company Code
         BELNR type BKPF-BELNR,    " Accounting Document Number
         GJAHR type BKPF-GJAHR,    " Fiscal Year
         BLART type BKPF-BLART,    " Document Type
         BUDAT type BKPF-BUDAT,    " Posting Date in the Document
         XBLNR type BKPF-XBLNR,    " Reference Document Number
       end of TY_CRATIO_BKPF.


* Types declaration for the table BSEG - Accounting Document Segment

types: begin of TY_CRATIO_BSEG,
         BUKRS type BSEG-BUKRS,    " Company Code
         BELNR type BSEG-BELNR,    " Accounting Document Number
         GJAHR type BSEG-GJAHR,    " Fiscal Year
         BUZEI type BSEG-BUZEI,    " Line item
         SHKZG type BSEG-SHKZG,    " Debit/Credit Indicator
         DMBTR type BSEG-DMBTR,    " Amount in Local Currency
         ZUONR type BSEG-ZUONR,    " Assignment No / Allocation No
         HKONT type BSEG-HKONT,    " General Ledger Account
       end of TY_CRATIO_BSEG.


* types declaration for the tables BKPF & BSEG

types: begin of TY_CRATIO_FINAL,
         BUKRS type BKPF-BUKRS,    " Company Code
         BLART type BKPF-BLART,    " Document Type
         HKONT type BSEG-HKONT,    " General Ledger Account
         SHKZG type BSEG-SHKZG,    " Debit/Credit Indicator
         BELNR type BKPF-BELNR,    " Accounting Document Number
         ZUONR type BSEG-ZUONR,    " Assignment No / Allocation No
         BUDAT type BKPF-BUDAT,    " Posting Date in the Document
         XBLNR type BKPF-XBLNR,    " Reference Document Number
         DMBTR type BSEG-DMBTR,    " Amount in Local Currency
       end of TY_CRATIO_FINAL.


*Internal Structure declarations section

data : IS_CRATIO_BKPF  type TY_CRATIO_BKPF,
       IS_CRATIO_BSEG  type TY_CRATIO_BSEG,
       IS_CRATIO_FINAL type TY_CRATIO_FINAL.


*Internal table declarations section

data: IT_CRATIO_BKPF type standard table of TY_CRATIO_BKPF
                                                  initial size 0,
      IT_CRATIO_BSEG type standard table of TY_CRATIO_BSEG
                                                  initial size 0 ,
      IT_CRATIO_FINAL type standard table of TY_CRATIO_FINAL
                                                   initial size 0.


* Selection screen fields declarations

selection-screen : begin of block B1 with frame title TEXT-112.

select-options: SO_BUKRS for BKPF-BUKRS obligatory.  " Company code
select-options: SO_HKONT for BSEG-HKONT obligatory.  " G/L Account
parameters    : P_GJAHR type BSEG-GJAHR obligatory.  " Fiscal Year
select-options: SO_MONAT for BKPF-MONAT.             " Period
select-options: SO_BUDAT for BKPF-BUDAT.             " Posting date
parameters    : FLAG as checkbox default ' '.        " Details Y/N

selection-screen : end of block B1.

************************************************************************
* selection of data from BKPF (Accounting Document Header) based on
* selection screen fields Fiscal Period(P_MONAT) and Posting date
* (P_BUDAT) and document type(BLART NE 'SA') and storing the details
* retrieved into an internal table IT_CRATIO_BKPF
************************************************************************

  select BUKRS BELNR GJAHR BLART BUDAT XBLNR
          from BKPF
          into table IT_CRATIO_BKPF
          where BUKRS in SO_BUKRS
            and BUDAT in SO_BUDAT
            and GJAHR = P_GJAHR
            and BLART ne C_SA
            and MONAT in SO_MONAT
            %_hints oracle 'index(BKPF"2")'.


* sorting the internal table for BKPF based on document number(BELNR)
  sort IT_CRATIO_BKPF by BUKRS BELNR.


* checking for the initiality of the internal table
  if not IT_CRATIO_BKPF[] is initial.  "IF for it_cratio_BKPF


************************************************************************
* selection of data from BSEG ( Accounting Document Segment) based on
* selection screen fields company code(P_bukrs) , gl account (P_HKONT)
* fiscal year(P_GJAHR) and for each of entry of accounting document
* entries (IT_CRATIO) retrieved from BKPF table and storing the details
* retrieved into an internal table (IT_CRATIO1)
************************************************************************

    select BUKRS BELNR GJAHR BUZEI SHKZG DMBTR ZUONR HKONT
           from BSEG
           into corresponding fields of table IT_CRATIO_BSEG
           for all entries in IT_CRATIO_BKPF
           where BUKRS = IT_CRATIO_BKPF-BUKRS
             and BELNR = IT_CRATIO_BKPF-BELNR
             and GJAHR = IT_CRATIO_BKPF-GJAHR
             and HKONT in SO_HKONT.

  endif.                        "IF for it_cratio_BKPF

************************************************************************
* Moving data into internal table
************************************************************************


  loop at IT_CRATIO_BSEG into IS_CRATIO_BSEG.  " Start IT_CRATIO_BSEG


************************************************************************
* Moving the consolidated document details from BKPF & BSEG
* into the final internal table
************************************************************************

    read table IT_CRATIO_BKPF into IS_CRATIO_BKPF
                         with key BUKRS = IS_CRATIO_BSEG-BUKRS
                                  BELNR = IS_CRATIO_BSEG-BELNR
                                  GJAHR = IS_CRATIO_BSEG-GJAHR
                         binary search.


    if SY-SUBRC = 0.

      move IS_CRATIO_BKPF-BUKRS to IS_CRATIO_FINAL-BUKRS.
      move IS_CRATIO_BKPF-BLART to IS_CRATIO_FINAL-BLART.
      move IS_CRATIO_BSEG-HKONT to IS_CRATIO_FINAL-HKONT.
      move IS_CRATIO_BSEG-SHKZG to IS_CRATIO_FINAL-SHKZG.
      move IS_CRATIO_BKPF-BELNR to IS_CRATIO_FINAL-BELNR.
      move IS_CRATIO_BSEG-ZUONR to IS_CRATIO_FINAL-ZUONR.
      move IS_CRATIO_BKPF-BUDAT to IS_CRATIO_FINAL-BUDAT.
      move IS_CRATIO_BKPF-XBLNR to IS_CRATIO_FINAL-XBLNR.
      move IS_CRATIO_BSEG-DMBTR to IS_CRATIO_FINAL-DMBTR.
      append IS_CRATIO_FINAL to IT_CRATIO_FINAL.
      clear IS_CRATIO_FINAL.

    endif.


    clear : IS_CRATIO_BKPF,
            IS_CRATIO_BSEG.


  endloop.                                     " End IT_CRATIO_BSEG

<b>I hope this sample code is enough.

Now if you want to enhance further try on ur own and let me know if u need any help</b>

Regards

Gopi

Former Member · ‎10-11-2007

how do i get the total of the amount in local currency

Former Member · ‎10-11-2007

Hi,

Go through the following Code

Use the following F.M's

CONVERT_TO_FOREIGN_CURRENCY Convert local currency to foreign currency.

CONVERT_TO_LOCAL_CURRENCY Convert from foreign currency to local currency

Check the following Code

PARAMETERS: P_UKURS LIKE TCURR-UKURS.
DATA: BEGIN OF GI_TAB OCCURS 0,
KONWA LIKE KONP-KONWA,
STPRS LIKE MBEW-STPRS,
WAERS LIKE T001-WAERS,
END OF GI_TAB.

DATA: L_STPRS LIKE MBEW-STPRS,
L_RATE LIKE TCURR-UKURS.

IF P_UKURS IS INITIAL.
CLEAR L_RATE.
ELSE.
L_RATE = P_UKURS / 100.
ENDIF.


CALL FUNCTION 'CONVERT_TO_FOREIGN_CURRENCY'
EXPORTING
DATE = SY-DATUM
FOREIGN_CURRENCY = GI_TAB-KONWA
LOCAL_AMOUNT = GI_TAB-STPRS
LOCAL_CURRENCY = GI_TAB-WAERS
RATE = L_RATE
IMPORTING
FOREIGN_AMOUNT = L_STPRS
EXCEPTIONS
NO_RATE_FOUND = 1
OVERFLOW = 2
NO_FACTORS_FOUND = 3
NO_SPREAD_FOUND = 4
DERIVED_2_TIMES = 5
OTHERS = 6.



CALL FUNCTION 'CONVERT_TO_LOCAL_CURRENCY'
EXPORTING
DATE = SY-DATUM
FOREIGN_AMOUNT = L_BELOEB
FOREIGN_CURRENCY = 'CAD'
LOCAL_CURRENCY = 'USD'
RATE = L_RATE
* TYPE_OF_RATE = 'M'
IMPORTING
EXCHANGE_RATE = L_RATE
* FOREIGN_FACTOR =
LOCAL_AMOUNT = L_BELOEB
* LOCAL_FACTOR =
* EXCHANGE_RATEX =
* FIXED_RATE =
* DERIVED_RATE_TYPE =
EXCEPTIONS
NO_RATE_FOUND = 1
OVERFLOW = 2
NO_FACTORS_FOUND = 3
NO_SPREAD_FOUND = 4
DERIVED_2_TIMES = 5
OTHERS = 6.

Thanks.

Reward If Helpful.