Skip to Content
0
Former Member
Nov 24, 2006 at 09:23 AM

i need Opening & Closing Balance

69 Views

Hi Experts,

In this report i need the opening and Closing Balance,

single material it is working fine,

but i gave multiple materials it is not working.

TABLES: MKPF,LFA1,MSEG,EKET,T001L,MAKT,S031,S032,MARA,LIPS,LIKP,T001W.

DATA FLAG TYPE I.

DATA : MENGE_RE LIKE MSEG-MENGE,

MENGE_IS LIKE MSEG-MENGE,

MENGE_O LIKE MSEG-MENGE,

MENGE_BAL LIKE MSEG-MENGE.

DATA MAGBB LIKE S031-MAGBB.

DATA MZUBB LIKE S031-MZUBB.

DATA V_MONTH LIKE S031-SPMON.

DATA XBLNR LIKE MKPF-XBLNR.

DATA : BEGIN OF ITAB1 OCCURS 100,

MATNR LIKE MAKT-MATNR,

END OF ITAB1.

DATA : BEGIN OF ITAB2 OCCURS 100,

MATNR LIKE MAKT-MATNR,

WERKS LIKE MARD-WERKS,

LGORT LIKE MARD-LGORT,

BUSTW LIKE MSEG-BUSTW,

BWART LIKE MSEG-BWART,

SHKZG LIKE MSEG-SHKZG,

MENGE LIKE MSEG-MENGE,

END OF ITAB2.

DATA: PAGNO(5) TYPE C.

DATA : PAGENO LIKE SY-PAGNO.

DATA : V_PAGENO LIKE SY-PAGNO.

DATA V_STOCK LIKE MSEG-MENGE.

DATA V_STOCK1 LIKE MSEG-MENGE.

DATA: V_MONTH1 LIKE S031-SPMON.

DATA: OSTOCK LIKE MSEG-MENGE.

DATA: FLD(15).

DATA: TXT(50).

DATA: STR1 TYPE DATS,

STR2(14) TYPE C,

STR3(10) TYPE C.

DATA: P_DATE(20),

PR_DATE(12), " Previous statement period (FM)

PR_DATE1(6), " Previous statement period

SDATE LIKE SY-DATUM, " First date of the month

EDATE LIKE SY-DATUM, " Last date of the current month

RDATE LIKE SY-DATUM. " Last date of the Previous month

************************************************************************

  • I N I T I A L I Z A T I O N

************************************************************************

PAGNO = SY-PAGNO.

************************************************************************

  • A T S E L E C T I O N S C R E E N

************************************************************************

AT SELECTION-SCREEN.

************************************************************************

  • S T A R T O F S E L E C T I O N

************************************************************************

START-OF-SELECTION.

SET MARGIN 5.

PERFORM FETCH_DATA.

  • PERFORM WRITE_REPORT.

  • PERFORM STOCK.

************************************************************************

  • E N D O F S E L E C T I O N

************************************************************************

END-OF-SELECTION.

  • Report Selections

DATA: BEGIN OF IT_HEADER OCCURS 10,

WERKS LIKE MSEG-WERKS,

NAME1 LIKE T001W-NAME1,

MAKTX LIKE MAKT-MAKTX,

MATNR LIKE MSEG-MATNR,

MENGE_O LIKE MSEG-MENGE,

END OF IT_HEADER.

DATA: BEGIN OF ITAB OCCURS 50,

WERKS LIKE MSEG-WERKS,

MATNR LIKE MSEG-MATNR,

BWART LIKE MSEG-BWART,

LGORT LIKE MSEG-LGORT,

EBELN LIKE MSEG-EBELN,

XBLNR LIKE MKPF-XBLNR,

BUDAT LIKE MKPF-BUDAT,

MBLNR LIKE MSEG-MBLNR,

MENGE LIKE MSEG-MENGE,

MAKTX LIKE MAKT-MAKTX,

XAUTO LIKE MSEG-XAUTO,

NAME1 LIKE T001W-NAME1,

MENGE_O LIKE MSEG-MENGE,

SHKZG LIKE MSEG-SHKZG,

MENGE_IS LIKE MSEG-MENGE,

MENGE_RE LIKE MSEG-MENGE,

END OF ITAB.

DATA: BEGIN OF INT_DATA OCCURS 50,

WERKS LIKE MSEG-WERKS,

MATNR LIKE MSEG-MATNR,

BWART LIKE MSEG-BWART,

XBLNR LIKE MKPF-XBLNR,

MBLNR LIKE MSEG-MBLNR,

BUDAT LIKE MKPF-BUDAT,

MAKTX LIKE MAKT-MAKTX,

MENGE_O LIKE MSEG-MENGE,

MENGE_R LIKE MSEG-MENGE,

MENGE_TO LIKE MSEG-MENGE,

MENGE_TI LIKE MSEG-MENGE,

MENGE_S LIKE MSEG-MENGE,

MENGE_ST LIKE MSEG-MENGE,

MENGE_AD LIKE MSEG-MENGE,

MENGE_RT LIKE MSEG-MENGE,

MENGE_C LIKE MSEG-MENGE,

MENGE_CL LIKE MSEG-MENGE,

MENGE_BAL LIKE MSEG-MENGE,

MENGE_CR LIKE MSEG-MENGE,

NAME1 LIKE T001W-NAME1,

MENGE_RE LIKE MSEG-MENGE,

MENGE_IS LIKE MSEG-MENGE,

MENGE_BAL1 LIKE MSEG-MENGE,

MENGE_ADD LIKE MSEG-MENGE,

SHKZG LIKE MSEG-SHKZG,

MENGE LIKE MSEG-MENGE,

LGORT LIKE MSEG-LGORT,

DATE(12),

CDATE(8),

END OF INT_DATA.

DATA: BEGIN OF INT_DATA1 OCCURS 50,

WERKS LIKE MSEG-WERKS,

MATNR LIKE MSEG-MATNR,

BWART LIKE MSEG-BWART,

MENGE_ST LIKE MSEG-MENGE,

MENGE_O LIKE MSEG-MENGE,

WAMNG LIKE EKET-WAMNG,

WEMNG LIKE EKET-WEMNG,

MENGE LIKE MSEG-MENGE,

RETPO LIKE EKPO-RETPO,

UMREN LIKE EKPO-UMREN,

UMREZ LIKE EKPO-UMREZ,

BSTMG LIKE EKPO-MENGE,

XBLNR LIKE MKPF-XBLNR,

END OF INT_DATA1.

DATA: BEGIN OF INT_DATA2 OCCURS 50,

WERKS LIKE MSEG-WERKS,

LGORT LIKE S031-LGORT,

MATNR LIKE MSEG-MATNR,

BWART LIKE MSEG-BWART,

MENGE_IS LIKE MSEG-MENGE,

MBWBEST LIKE S032-MBWBEST,

MAKTX LIKE MAKT-MAKTX,

END OF INT_DATA2.

  • DATA:V_GJAHR LIKE MKPF-BUDAT.

SELECTION-SCREEN BEGIN OF BLOCK 0 WITH FRAME TITLE TEXT-064.

SELECT-OPTIONS:

MATNR FOR MSEG-MATNR,

MATKL FOR MARA-MATKL OBLIGATORY,

WERKS FOR MSEG-WERKS OBLIGATORY,

LGORT FOR MSEG-LGORT,

BWART FOR MSEG-BWART,

S_DATE FOR MKPF-BUDAT.

SELECTION-SCREEN END OF BLOCK 0.

TOP-OF-PAGE.

PERFORM HEADER_WRITE.

&----


*& Form Fetch_data

&----


  • text

----


FORM FETCH_DATA .

SELECT * FROM MARA INTO CORRESPONDING FIELDS OF TABLE ITAB1

WHERE MATNR IN MATNR AND

MATKL IN MATKL.

SELECT MATNR WERKS LGORT

INTO TABLE ITAB2

FROM MARD FOR ALL ENTRIES IN ITAB1

WHERE WERKS IN WERKS AND

LGORT IN LGORT AND

MATNR = ITAB1-MATNR.

LOOP AT ITAB2.

SELECT M~MATNR M~WERKS M~LGORT M~BWART M~MENGE M~MBLNR M~MJAHR M~XAUTO M~SHKZG

M~EBELN N~BUDAT N~XBLNR S~NAME1

INTO CORRESPONDING FIELDS OF ITAB

FROM MSEG AS M INNER JOIN MKPF AS N ON

M~MBLNR = N~MBLNR

AND M~MJAHR = N~MJAHR

INNER JOIN T001W AS S ON

M~WERKS = S~WERKS

WHERE N~BUDAT IN S_DATE AND

M~WERKS = ITAB2-WERKS AND

M~LGORT = ITAB2-LGORT AND

M~MATNR = ITAB2-MATNR AND

M~BWART IN ('101','601','641','602','651','642','652','551','102','552').

APPEND ITAB.

CLEAR ITAB.

ENDSELECT.

IF SY-SUBRC NE 0.

ITAB-MATNR = ITAB2-MATNR.

ITAB-WERKS = ITAB2-WERKS.

ITAB-LGORT = ITAB2-LGORT.

ITAB-BWART = ITAB2-BWART.

APPEND ITAB.

ENDIF.

CLEAR: ITAB2,ITAB.

ENDLOOP.

DELETE ADJACENT DUPLICATES FROM ITAB2 COMPARING MATNR WERKS .

V_MONTH = S_DATE-LOW+0(6).

SELECT * INTO CORRESPONDING FIELDS OF TABLE INT_DATA2

FROM S032 FOR ALL ENTRIES IN ITAB2

WHERE MATNR = ITAB2-MATNR

AND WERKS = ITAB2-WERKS

AND LGORT = SPACE

AND MBWBEST <> SPACE.

LOOP AT INT_DATA2 .

SELECT SINGLE * FROM S031

WHERE MATNR = INT_DATA2-MATNR AND

WERKS = INT_DATA2-WERKS AND

LGORT = SPACE AND

SPMON GT V_MONTH.

MODIFY INT_DATA2.

CLEAR: INT_DATA2, S031.

ENDLOOP.

DELETE INT_DATA WHERE MENGE_O IS INITIAL .

DELETE ADJACENT DUPLICATES FROM INT_DATA COMPARING XBLNR MATNR MAKTX.

V_MONTH1 = S_DATE-LOW+0(6).

SORT ITAB BY MATNR WERKS XBLNR .

LOOP AT ITAB.

XBLNR = ITAB-XBLNR.

INT_DATA-MENGE = ITAB-MENGE.

INT_DATA-BWART = ITAB-BWART.

INT_DATA-XBLNR = ITAB-XBLNR.

INT_DATA-BUDAT = ITAB-BUDAT.

INT_DATA-MATNR = ITAB-MATNR.

INT_DATA-MBLNR = ITAB-MBLNR.

INT_DATA-MENGE = ITAB-MENGE.

INT_DATA-SHKZG = ITAB-SHKZG.

INT_DATA-MENGE_IS = ITAB-MENGE_IS.

INT_DATA-MENGE_RE = ITAB-MENGE_RE.

INT_DATA-MENGE_O = ITAB-MENGE_O.

INT_DATA-NAME1 = ITAB-NAME1.

AT NEW MATNR.

FLAG = 1.

IT_HEADER-MATNR = INT_DATA-MATNR.

SELECT SINGLE MAKTX FROM MAKT INTO IT_HEADER-MAKTX WHERE

MATNR = INT_DATA-MATNR.

APPEND IT_HEADER.

CLEAR IT_HEADER.

endat.

IF FLAG = 1.

PERFORM GET_OPENING_STOCK.

ENDIF.

INT_DATA-MENGE_O = OSTOCK.

CASE ITAB-BWART.

WHEN '101'.

IF INT_DATA-SHKZG = 'S'.

INT_DATA-MENGE_R = INT_DATA-MENGE_R + ITAB-MENGE.

ENDIF.

WHEN '651'.

IF INT_DATA-SHKZG = 'S'.

  • IF ITAB-XAUTO = 'X'.

INT_DATA-MENGE_RT = INT_DATA-MENGE_RT + ITAB-MENGE.

ENDIF.

  • ENDIF.

WHEN '351' OR '641'.

IF INT_DATA-SHKZG = 'H'.

IF ITAB-LGORT = 0.

INT_DATA-MENGE_R = INT_DATA-MENGE_R + ITAB-MENGE.

ELSE.

INT_DATA-MENGE_TO = INT_DATA-MENGE_TO + ITAB-MENGE.

ENDIF.

ENDIF.

WHEN '602' OR '642'.

IF INT_DATA-SHKZG = 'S'.

IF ITAB-LGORT = 0.

INT_DATA-MENGE_S = INT_DATA-MENGE_S - ITAB-MENGE.

ELSE.

INT_DATA-MENGE_CL = INT_DATA-MENGE_CL + ITAB-MENGE.

ENDIF.

ENDIF.

WHEN '552'.

IF INT_DATA-SHKZG = 'S'.

IF ITAB-LGORT = 0.

INT_DATA-MENGE_AD = INT_DATA-MENGE_AD - ITAB-MENGE.

ELSE.

INT_DATA-MENGE_ADD = INT_DATA-MENGE_ADD + ITAB-MENGE.

ENDIF.

ENDIF.

WHEN '601'.

IF INT_DATA-SHKZG = 'H'.

INT_DATA-MENGE_S = INT_DATA-MENGE_S + ITAB-MENGE.

ENDIF.

WHEN '641' .

IF INT_DATA-SHKZG = 'H'.

IF ITAB-LGORT = 0.

INT_DATA-MENGE_TO = INT_DATA-MENGE_TO + ITAB-MENGE.

ENDIF.

ENDIF.

WHEN '652' OR '102'.

IF INT_DATA-SHKZG = 'H'.

  • IF ITAB-XAUTO = 'X'.

INT_DATA-MENGE_CR = INT_DATA-MENGE_CR + ITAB-MENGE.

  • ENDIF.

ENDIF.

WHEN '551'.

IF INT_DATA-SHKZG = 'H'.

INT_DATA-MENGE_AD = INT_DATA-MENGE_AD + ITAB-MENGE.

ENDIF.

ENDCASE.

COLLECT INT_DATA.

CLEAR: INT_DATA,XBLNR, FLAG, OSTOCK.

IF INT_DATA-SHKZG = 'S'.

MENGE_RE = INT_DATA-MENGE_R + INT_DATA-MENGE_RT + INT_DATA-MENGE_CL

+ INT_DATA-MENGE_ADD.

ENDIF.

IF INT_DATA-SHKZG = 'H'.

MENGE_IS = INT_DATA-MENGE_S + INT_DATA-MENGE_TO +

INT_DATA-MENGE_CR + INT_DATA-MENGE_AD.

ENDIF.

ENDLOOP.

SORT INT_DATA BY BUDAT XBLNR BWART WERKS MATNR.

LOOP AT INT_DATA WHERE MENGE_O = 0 AND MENGE_RT = 0 AND

MENGE_CL = 0 AND MENGE_AD = 0 AND

MENGE_S = 0 AND MENGE_TO = 0 AND

MENGE_CR = 0 AND MENGE_R = 0 AND MENGE_BAL = 0.

DELETE IT_HEADER WHERE MATNR = INT_DATA-MATNR.

ENDLOOP.

DELETE INT_DATA WHERE MENGE_O = 0 AND MENGE_RT = 0 AND

MENGE_CL = 0 AND MENGE_AD = 0 AND

MENGE_S = 0 AND MENGE_TO = 0 AND

MENGE_CR = 0 AND MENGE_R = 0 AND MENGE_BAL = 0.

FORMAT RESET.

LOOP AT INT_DATA.

PERFORM BALANCE.

MODIFY INT_DATA.

ENDLOOP.

PERFORM WRITE_REPORT.

INT_DATA-MENGE_O = OSTOCK.

FORMAT RESET.

WRITE:/1(156) SY-ULINE.

ENDFORM.

&----


*& Form Write_Report

&----


  • text

----


FORM WRITE_REPORT.

SORT IT_HEADER BY MATNR.

LOOP AT IT_HEADER.

  • SKIP .

AT NEW MATNR.

WRITE:/1(60) SY-ULINE.

READ TABLE IT_HEADER INDEX SY-TABIX.

WRITE:/1 SY-VLINE,

2 'Material :'(006),14(7) IT_HEADER-MATNR NO-GAP,'-',

IT_HEADER-MAKTX,

60 SY-VLINE.

WRITE:/1(60) SY-ULINE.

*READ TABLE INT_DATA WITH KEY MATNR = IT_HEADER-MATNR.

  • IF SY-SUBRC NE 0 AND OSTOCK = 0.

  • CONTINUE.

  • ENDIF.

SKIP.

SET LEFT SCROLL-BOUNDARY COLUMN 50.

WRITE:/1(156) SY-ULINE.

FORMAT COLOR 1 INTENSIFIED OFF.

WRITE:/1 SY-VLINE,

2 'Doc. No' CENTERED,

14 SY-VLINE,

15(14) 'Doc.Date' CENTERED,

27 SY-VLINE,

28(10) 'MVT' CENTERED,

40 SY-VLINE.

WRITE:41(57) 'Receipts' CENTERED,

89 SY-VLINE, 90(47) 'Issues' CENTERED,130 SY-VLINE,131(12) 'C.Balance' CENTERED,144 SY-VLINE,

145 'MAT.DOC NO' CENTERED,156 SY-VLINE,

/40 SY-VLINE,1 SY-VLINE,14 SY-VLINE,27 SY-VLINE,40 SY-VLINE,

41(130) SY-ULINE,

/40 SY-VLINE, 43 'STO-In' CENTERED , 51 SY-VLINE,

52 'Sales Returns' CENTERED , 65 SY-VLINE,

66 'Canc of Invoice' CENTERED, 82 SY-VLINE,

83 'Adj' CENTERED, 89 SY-VLINE,

90 'Sales' CENTERED, 97 SY-VLINE,

98 'STO-Out' CENTERED, 106 SY-VLINE,

107 'Canc of Sales' CENTERED, 120 SY-VLINE,

121 'Adj' CENTERED, 130 SY-VLINE,144 SY-VLINE,156 SY-VLINE,

1 SY-VLINE,14 SY-VLINE,27 SY-VLINE,40 SY-VLINE.

WRITE:/1(156) SY-ULINE.

FORMAT RESET.

PERFORM GET_OPENING_STOCK.

FORMAT COLOR 4 INTENSIFIED OFF.

WRITE:/1 SY-VLINE,

106 'Opening Balance' CENTERED,

130 SY-VLINE,

156 SY-VLINE.

WRITE: 130(8) OSTOCK.

WRITE:/1(156) SY-ULINE.

LOOP AT INT_DATA WHERE MATNR = IT_HEADER-MATNR.

FORMAT COLOR 2 INTENSIFIED OFF.

*WRITE:/1(156) SY-ULINE.

WRITE:/1 SY-VLINE,

2 INT_DATA-XBLNR,

14 SY-VLINE,

15 INT_DATA-BUDAT,

27 SY-VLINE,

28 INT_DATA-BWART,

40 SY-VLINE,

43(9) INT_DATA-MENGE_R,

51 SY-VLINE,

52(9) INT_DATA-MENGE_RT,

65 SY-VLINE,

66(9) INT_DATA-MENGE_CL,

82 SY-VLINE,

83(7) INT_DATA-MENGE_ADD,

89 SY-VLINE,

90(9) INT_DATA-MENGE_S,

97 SY-VLINE,

98(9) INT_DATA-MENGE_TO,

106 SY-VLINE,

107(9) INT_DATA-MENGE_CR,

120 SY-VLINE,

121(9) INT_DATA-MENGE_AD,

130 SY-VLINE,

131(9) INT_DATA-MENGE_BAL,

144 SY-VLINE,

145 INT_DATA-MBLNR,

156 SY-VLINE.

*WRITE:/1(156) SY-ULINE.

FORMAT RESET.

ENDLOOP.

WRITE:/1(156) SY-ULINE.

ENDAT.

ENDLOOP.

ENDFORM.

&----


*& Form BALANCE

&----


  • text

----


  • --> p1 text

  • <-- p2 text

----


FORM BALANCE.

IF INT_DATA-SHKZG = 'S'.

MENGE_RE = MENGE_RE + INT_DATA-MENGE.

INT_DATA-MENGE_BAL = MENGE_RE.

INT_DATA-MENGE_RE = INT_DATA-MENGE.

MODIFY INT_DATA TRANSPORTING MENGE_RE MENGE_BAL.

ELSEIF INT_DATA-SHKZG = 'H'.

MENGE_RE = MENGE_RE - INT_DATA-MENGE.

INT_DATA-MENGE_BAL = MENGE_RE.

INT_DATA-MENGE_IS = INT_DATA-MENGE.

MODIFY INT_DATA TRANSPORTING MENGE_IS MENGE_BAL.

ENDIF.

ENDFORM. " BALANCE

&----


*& Form HEADER_WRITE.

&----


  • text

----


FORM HEADER_WRITE.

SKIP.

WRITE: /1(156) SY-ULINE.

WRITE: /1 SY-VLINE,

2(144) 'xxxxxxxxxxxxxxxxxxxx'

INTENSIFIED CENTERED," INVERSE ON

156 SY-VLINE.

WRITE: /1(156) SY-ULINE.

WRITE: /1 SY-VLINE,

2(144) 'STOCK LEDGER' INTENSIFIED CENTERED,

"INVERSE ON,

156 SY-VLINE.

WRITE: /1(156) SY-ULINE.

SELECT SINGLE * FROM T001W WHERE

WERKS IN WERKS.

IF WERKS-HIGH EQ SPACE.

CONCATENATE 'Plant : ' WERKS-LOW INTO TXT.

ELSE.

CONCATENATE 'FROM PLANT : ' WERKS-LOW 'TO PLANT : ' WERKS-HIGH INTO TXT SEPARATED BY SPACE.

ENDIF.

WRITE:/ 'Plant :', WERKS-LOW,SPACE ,'-', T001W-NAME1,

82 'As Of Date :', 95 sy-datum.

IF NOT S_DATE IS INITIAL.

WRITE: / 'From:'(004), S_DATE-LOW, SPACE, 'To:'(006), S_DATE-HIGH,82 'PAGE No:', 91 SY-PAGNO..

ENDIF.

FORMAT RESET.

ENDFORM.

&----


*& Form GET_OPENING_STOCK

&----


  • text

----


FORM GET_OPENING_STOCK.

SELECT SUM( MZUBB ) SUM( MAGBB ) INTO (V_STOCK, V_STOCK1)

FROM S031 WHERE MATNR = ITAB-MATNR AND

WERKS = ITAB-WERKS AND

SPMON LT V_MONTH1 AND LGORT = ITAB-LGORT.

OSTOCK = V_STOCK1 - V_STOCK.

IF OSTOCK < 0.

OSTOCK = OSTOCK * -1.

MENGE_RE = OSTOCK.

ENDIF.

ENDFORM. " GET_OPENING_STOCK

Thanks & Regards,

Sreedhar.