Daily Stock and Sales Report

[Former Member]

Hello.I need to develop a stock and sales report in R/3. The columns in the output are:

SKU Code, Opening stock qty (as on 1st day of the month), Closing stock qty (as of end of transaction date), Sales Qty, Sales Qty value, Unit of Measure, Date.

While I have an idea how to go about the opening stock and sales details, I need help with the closing stock quantity calculation. Which tables do I access, fields, etc. .. any suggestions?

Thanks in advance.

[Former Member · Accepted solution]

Dear all,

Thank you for all the help and suggestions. I finally got it. Forget about using MKPF and MSEG. Use S031 and S033 instead. Thanks once again. P.F.A. the coding which I used. Hope it will be useful.

ZSD_RB_STKSLS.txt.zip

[moazzam_ali]

Dear

This coding seems ok to me. I was talking about MSEG table only. May be MSEG will create performance problem rest are ok.

For sale return you can show in same column too with - sign with quantity and value. If it is client's requirement to show in separate column then create it.

Please note that sale is taken into consideration with Invoices quantity and stock with movement types. Once we tried to cater sale with movement type 601 too but that was just a waist of time because we can't ever match 601 with invoices quantity due to FOC issues. So just focus on VBRP and VBRK for Sales.

Thank$

[Former Member]

Thank you. I will try and get back to you. Thank you all once again.

[Former Member]

Did quite some R&D, and am close to getting what I'm looking for but facing problem with the closing stock when I need to execute the same report for a previous period, day-wise. I'm not sure how to capture the previous day's closing stock which will be the current day's opening balance. Can you help me out?

This is the code I have for now regarding closing stock:

--------------------------------------------------

WHILE v_dateinc <= fkdat-high.

     SELECT SUM( mzubb ) SUM( magbb ) matnr sptag

                FROM s033

                INTO TABLE lt_s033

                WHERE werks IN ('1150','1151')

       AND sptag = v_dateinc GROUP BY matnr sptag .

     LOOP AT lt_opnstk INTO wa_opnstk.

       wa_cstk1-matnr = wa_opnstk-matnr.

       READ TABLE lt_s033 INTO wa_s033 WITH KEY matnr = wa_opnstk-matnr.

       IF sy-subrc = 0.

         wa_cstk1-sptag = wa_s033-sptag.

         wa_cstk1-cstock = wa_opnstk-ostock + wa_s033-mzubb - wa_s033-magbb .

       ELSE.

         wa_cstk1-sptag = v_dateinc.

         wa_cstk1-cstock = wa_opnstk-ostock  .

       ENDIF.

       IF wa_cstk1-cstock < 0.

         wa_cstk1-cstock = wa_cstk1-cstock * -1.

       ENDIF.

       wa_cstk1-ostock = wa_opnstk-ostock.

       SHIFT wa_cstk1-matnr LEFT DELETING LEADING '0'.

       APPEND wa_cstk1 TO lt_cstk1.

       CLEAR wa_cstk1.

     ENDLOOP.

     v_dateinc = v_dateinc + 1.

   ENDWHILE.

   SORT lt_cstk1.

[moazzam_ali]

Roslin

I have talked to my ABAPER and he gave me this coding and he asked me to just copy paste this and run for closing stock in back date. As I am not technical ABAPER so I am not very good in coding.

FORM fin_stock .
REFRESH: bestand2.
CLEAR: bestand2.
DATA: BEGIN OF imard OCCURS 100, "aktueller Materialbestand
werks LIKE mard-werks, "Werk
matnr LIKE mard-matnr, "Material
bwkey LIKE mbew-bwkey,
lgort LIKE mard-lgort, "Lagerort
labst LIKE mard-labst, "frei verwendbarer Bestand
umlme LIKE mard-umlme, "Umlagerungsbestand
insme LIKE mard-insme, "Qualitätsprüfbestand
einme LIKE mard-einme, "nicht frei verwendbarer Bestand
speme LIKE mard-speme, "gesperrter Bestand
retme LIKE mard-retme, "gesperrter Bestand
klabs LIKE mard-klabs, "frei verw. Konsignationsbestand
lbkum LIKE mbew-lbkum, "bewerteter Bestand
salk3(09) TYPE p DECIMALS 2, "n497992
waers LIKE t001-waers, "Währungseinheit
END OF imard.

DATA: BEGIN OF bestand OCCURS 0,
* matnr LIKE mseg-matnr,
* bwkey LIKE mbew-bwkey,
* werks LIKE mseg-werks,
* charg LIKE mseg-charg,
* endmenge(09) TYPE p DECIMALS 3, "Bestand zu 'datum-high' XJD
* anfmenge(09) TYPE p DECIMALS 3, "Bestand zu 'datum-low' XJD
* meins LIKE mara-meins, "Mengeneinheit
* endwert(09) TYPE p DECIMALS 2, "n497992
* anfwert(09) TYPE p DECIMALS 2, "n497992
* soll(09) TYPE p DECIMALS 3, "XJD
* haben(09) TYPE p DECIMALS 3, "XJD
* sollwert(09) TYPE p DECIMALS 2, "n497992
* habenwert(09) TYPE p DECIMALS 2, "n497992
* waers LIKE t001-waers, "Währungsschlüssel
* lgort TYPE lgort_d,
* lgobe TYPE lgobe,
bwkey LIKE mbew-bwkey,
werks LIKE mseg-werks,
matnr LIKE mseg-matnr,
charg LIKE mseg-charg,
(DEL) endmenge like mard-labst, "Bestand zu 'datum-high' XJD
endmenge(09) TYPE p DECIMALS 3, "Bestand zu 'datum-high' XJD
(DEL) anfmenge like mard-labst, "Bestand zu 'datum-low' XJD
anfmenge(09) TYPE p DECIMALS 3, "Bestand zu 'datum-low' XJD
meins LIKE mara-meins, "Mengeneinheit
* values at date-low and date-high "n497992
endwert(09) TYPE p DECIMALS 2, "n497992
anfwert(09) TYPE p DECIMALS 2, "n497992

(DEL) soll like mseg-menge, "XJD
soll(09) TYPE p DECIMALS 3, "XJD
(DEL) haben like mseg-menge, "XJD
haben(09) TYPE p DECIMALS 3, "XJD
sollwert(09) TYPE p DECIMALS 2, "n497992
habenwert(09) TYPE p DECIMALS 2, "n497992
waers LIKE t001-waers, "Währungsschlüssel
END OF bestand.

* DATA: BEGIN OF bestand2 OCCURS 0,
* matnr LIKE mseg-matnr,
* bwkey LIKE mbew-bwkey,
* werks LIKE mseg-werks,
* charg LIKE mseg-charg,
* endmenge(09) TYPE p DECIMALS 3, "Bestand zu 'datum-high' XJD
* anfmenge(09) TYPE p DECIMALS 3, "Bestand zu 'datum-low' XJD
* meins LIKE mara-meins, "Mengeneinheit
* endwert(09) TYPE p DECIMALS 2, "n497992
* anfwert(09) TYPE p DECIMALS 2, "n497992
* soll(09) TYPE p DECIMALS 3, "XJD
* haben(09) TYPE p DECIMALS 3, "XJD
* sollwert(09) TYPE p DECIMALS 2, "n497992
* habenwert(09) TYPE p DECIMALS 2, "n497992
* waers LIKE t001-waers, "Währungsschlüssel
* END OF bestand2.

TYPES : BEGIN OF stype_mb5b_add,
dummy(01) TYPE c, "filler
END OF stype_mb5b_add.

TYPES : BEGIN OF stype_mseg_lean,
mblnr LIKE mkpf-mblnr,
mjahr LIKE mkpf-mjahr,
vgart LIKE mkpf-vgart,
blart LIKE mkpf-blart,
budat LIKE mkpf-budat,
cpudt LIKE mkpf-cpudt,
cputm LIKE mkpf-cputm,
usnam LIKE mkpf-usnam,
* process 'goods receipt/issue slip' as hidden field "n450596
xabln LIKE mkpf-xabln, "n450596
lbbsa LIKE t156m-lbbsa,
bwagr LIKE t156s-bwagr,
bukrs LIKE t001-bukrs,
belnr LIKE bkpf-belnr,
gjahr LIKE bkpf-gjahr,
waers LIKE mseg-waers,
zeile LIKE mseg-zeile,
bwart LIKE mseg-bwart,
matnr LIKE mseg-matnr,
werks LIKE mseg-werks,
lgort LIKE mseg-lgort,
charg LIKE mseg-charg,
bwtar LIKE mseg-bwtar,
kzvbr LIKE mseg-kzvbr,
kzbew LIKE mseg-kzbew,
sobkz LIKE mseg-sobkz,
kzzug LIKE mseg-kzzug,
bustm LIKE mseg-bustm,
bustw LIKE mseg-bustw,
mengu LIKE mseg-mengu,
wertu LIKE mseg-wertu,
shkzg LIKE mseg-shkzg,
menge LIKE mseg-menge,
meins LIKE mseg-meins,
dmbtr LIKE mseg-dmbtr,
dmbum LIKE mseg-dmbum,
xauto LIKE mseg-xauto,
kzbws LIKE mseg-kzbws,
* special flag for retail "n497992
retail(01) TYPE c, "n497992
oiglcalc(01) TYPE c, "n599218 A
oiglsku(07) TYPE p DECIMALS 3, "n599218 A
insmk LIKE mseg-insmk, "n599218 A
smbln LIKE mseg-smbln, " No. doc
sjahr LIKE mseg-sjahr, " Year "n497992
smblp LIKE mseg-smblp. " Item in doc
INCLUDE TYPE stype_mb5b_add.
TYPES : END OF stype_mseg_lean.

TYPES: stab_mseg_lean TYPE STANDARD TABLE OF stype_mseg_lean
WITH KEY mblnr mjahr.

DATA : g_s_mseg_lean TYPE stype_mseg_lean,
"g_s_mseg_update TYPE stype_mseg_lean, "n443935
g_t_mseg_lean TYPE stab_mseg_lean,
index_2 LIKE sy-tabix,
index_3 LIKE sy-tabix,
index_4 LIKE sy-tabix,
aktdat LIKE sy-datlo.

DATA: BEGIN OF imsweg OCCURS 1000,
mblnr LIKE mseg-mblnr,
mjahr LIKE mseg-mjahr,
zeile LIKE mseg-zeile,
matnr LIKE mseg-matnr,
charg LIKE mseg-charg,
bwtar LIKE mseg-bwtar,
werks LIKE mseg-werks,
lgort LIKE mseg-lgort,
sobkz LIKE mseg-sobkz,
bwart LIKE mseg-bwart,
shkzg LIKE mseg-shkzg,
xauto LIKE mseg-xauto,
menge LIKE mseg-menge,
meins LIKE mseg-meins,
dmbtr LIKE mseg-dmbtr,
dmbum LIKE mseg-dmbum,
bustm LIKE mseg-bustm,
bustw LIKE mseg-bustw, "147374

* define the fields for the IO-OIL specific functions "n599218 A
* mseg-oiglcalc CHAR 1 "n599218 A
* mseg-oiglsku QUAN 13 "n599218 A
oiglcalc(01) TYPE c, "n599218 A
oiglsku(07) TYPE p DECIMALS 3, "n599218 A
insmk LIKE mseg-insmk, "n599218 A
END OF imsweg.

DATA: BEGIN OF sum_mat OCCURS 100,
werks LIKE mseg-werks,
matnr LIKE mseg-matnr,
shkzg LIKE mseg-shkzg,
menge(09) TYPE p DECIMALS 3, "XJD
END OF sum_mat.

DATA: BEGIN OF sum_char OCCURS 100,
werks LIKE mseg-werks,
matnr LIKE mseg-matnr,
charg LIKE mseg-charg,
shkzg LIKE mseg-shkzg,
menge(09) TYPE p DECIMALS 3, "XJD
END OF sum_char.

DATA: BEGIN OF weg_mat OCCURS 0,
werks LIKE mseg-werks,
lgort LIKE mseg-lgort, " P30K140665
matnr LIKE mseg-matnr,
shkzg LIKE mseg-shkzg,
menge(09) TYPE p DECIMALS 3, "XJD
END OF weg_mat.

DATA: BEGIN OF it156w OCCURS 100, "149448
bustw LIKE t156w-bustw, "149448
xbgbb LIKE t156w-xbgbb, "149448
END OF it156w. "149448

DATA: BEGIN OF imchb OCCURS 100, "aktueller Chargenbestand
werks LIKE mchb-werks,
matnr LIKE mchb-matnr,
lgort LIKE mchb-lgort,
charg LIKE mchb-charg,
clabs LIKE mchb-clabs, "frei verwendbarer Chargenbestand
cumlm LIKE mchb-cumlm, "Umlagerungsbestand
cinsm LIKE mchb-cinsm, "Qualitätsprüfbestand
ceinm LIKE mchb-ceinm, "nicht frei verwendbarer Bestand
cspem LIKE mchb-cspem, "gesperrter Bestand
cretm LIKE mchb-cretm, "gesperrter Bestand
END OF imchb.

DATA: BEGIN OF weg_char OCCURS 100,
werks LIKE mseg-werks,
matnr LIKE mseg-matnr,
lgort LIKE mseg-lgort, " P30K140665
charg LIKE mseg-charg,
shkzg LIKE mseg-shkzg,
menge(09) TYPE p DECIMALS 3, "XJD
END OF weg_char.

DATA: BEGIN OF imara OCCURS 100,
matnr LIKE mara-matnr,
meins LIKE mara-meins,
mtart LIKE mara-mtart,
END OF imara.

LOOP AT it_matnr."it_raw.
========================MB5B Logic===================
SELECT
mseg~budat_mkpf
mseg~cpudt_mkpf
mseg~cputm_mkpf
mseg~mblnr
mseg~mjahr
mseg~usnam_mkpf
mseg~vgart_mkpf
mseg~bustm
mseg~bustw
mseg~bwart
mseg~bwtar
mseg~charg
mseg~dmbtr
mseg~dmbum
mseg~insmk
mseg~kzbew
mseg~kzbws
mseg~kzvbr
mseg~kzzug
mseg~lgort
mseg~matnr
mseg~meins
mseg~menge
mseg~mengu
mseg~shkzg
mseg~sjahr
mseg~smbln
mseg~smblp
mseg~sobkz
mseg~waers
mseg~werks
mseg~wertu
mseg~xauto
mseg~zeile
INTO
(g_s_mseg_lean-budat ,
g_s_mseg_lean-cpudt,
g_s_mseg_lean-cputm,

g_s_mseg_lean-mblnr,
g_s_mseg_lean-mjahr ,
g_s_mseg_lean-usnam,
g_s_mseg_lean-vgart,
g_s_mseg_lean-bustm,
g_s_mseg_lean-bustw,
g_s_mseg_lean-bwart,
g_s_mseg_lean-bwtar,
g_s_mseg_lean-charg,
g_s_mseg_lean-dmbtr,
g_s_mseg_lean-dmbum,
g_s_mseg_lean-insmk,
g_s_mseg_lean-kzbew,
g_s_mseg_lean-kzbws,
g_s_mseg_lean-kzvbr,
g_s_mseg_lean-kzzug,
g_s_mseg_lean-lgort,
g_s_mseg_lean-matnr,
g_s_mseg_lean-meins,
g_s_mseg_lean-menge,
g_s_mseg_lean-mengu,
g_s_mseg_lean-shkzg,
g_s_mseg_lean-sjahr,
g_s_mseg_lean-smbln,
g_s_mseg_lean-smblp,
g_s_mseg_lean-sobkz,
g_s_mseg_lean-waers,
g_s_mseg_lean-werks,
g_s_mseg_lean-wertu,
g_s_mseg_lean-xauto,
g_s_mseg_lean-zeile)
FROM mseg
WHERE mseg~matnr = it_matnr-matnr"it_raw-matnr2 "IN s_matnr
"AND mseg~bwart IN ('601','602','651','652','997','998','105','106','922','122','569','570','301','302','701','702')
AND mseg~werks IN ('1000','2000') "= '1000'
AND mseg~lgort NE ' '"IN g_ra_lgort
AND mseg~budat_mkpf GE s_date-low
AND mseg~sobkz = ' '"IN g_ra_sobkz
%_HINTS
DB2 '&SUBSTITUTE VALUES&'
ORACLE '&SUBSTITUTE VALUES&'.

APPEND g_s_mseg_lean TO g_t_mseg_lean.
ENDSELECT.

aktdat = sy-datlo + 30.
IF NOT ( s_date-low IS INITIAL OR s_date-high > aktdat ).
LOOP AT g_t_mseg_lean INTO g_s_mseg_lean
WHERE budat > s_date-high AND matnr = it_matnr-matnr."it_raw-matnr2 .
MOVE-CORRESPONDING g_s_mseg_lean TO imsweg.
APPEND imsweg.
DELETE g_t_mseg_lean. "on 13.12.2011
ENDLOOP.
ENDIF.
***********************************************
DESCRIBE TABLE imsweg LINES index_2.

SORT imsweg BY werks matnr shkzg. "auf Materialebene
LOOP AT imsweg.
IF ( imsweg-xauto IS INITIAL ) OR
( imsweg-bustm <> 'MA02' AND imsweg-bustm <> 'MA05' ).
MOVE-CORRESPONDING imsweg TO weg_mat.
COLLECT weg_mat.
ELSE.
DELETE imsweg.
ENDIF.
ENDLOOP.

SORT imsweg BY werks matnr shkzg. "auf Materialebene
LOOP AT imsweg WHERE matnr = it_matnr-matnr."it_raw-matnr2 .
IF ( imsweg-xauto IS INITIAL ) OR
( imsweg-bustm <> 'MA02' AND imsweg-bustm <> 'MA05' ).
MOVE-CORRESPONDING imsweg TO weg_char."weg_mat.
COLLECT weg_char."weg_mat.
ELSE.
DELETE imsweg.
ENDIF.
ENDLOOP.

***********************************************************
SORT g_t_mseg_lean BY werks matnr shkzg DESCENDING.

LOOP AT g_t_mseg_lean INTO g_s_mseg_lean WHERE matnr = it_matnr-matnr."it_raw-matnr2 .
IF ( g_s_mseg_lean-xauto IS INITIAL ) OR
( g_s_mseg_lean-bustm <> 'MA02' AND
g_s_mseg_lean-bustm <> 'MA05' ).
MOVE-CORRESPONDING g_s_mseg_lean TO sum_mat.
COLLECT sum_mat.
ELSE.
DELETE g_t_mseg_lean.
ENDIF.
ENDLOOP.

REFRESH imchb.
CLEAR imchb.
SELECT * FROM mchb INTO CORRESPONDING FIELDS OF TABLE imchb
WHERE werks IN ('1000','2000')"= '1000' "in g_ra_werks
"AND lgort IN ('1011','1012','1013','1014' )
AND matnr = it_matnr-matnr."it_raw-matnr2 . "IN s_matnr.
" and charg in charg.

SELECT * FROM mard INTO CORRESPONDING FIELDS OF TABLE imard
WHERE matnr = it_matnr-matnr."it_raw-matnr2.

********************************for DEV****************************
LOOP AT imard.
CLEAR weg_mat-menge.
MOVE-CORRESPONDING imard TO bestand.
* In 'bestand' wird über die Lagerorte summiert.
READ TABLE weg_mat WITH KEY werks = imard-werks
lgort = imard-lgort " P30K140665
matnr = imard-matnr
shkzg = 'S'.
bestand-endmenge = imard-labst + imard-insme + imard-speme
+ imard-einme + imard-retme
- weg_mat-menge.
CLEAR weg_mat-menge.
READ TABLE weg_mat WITH KEY werks = imard-werks
lgort = imard-lgort " P30K140665
matnr = imard-matnr
shkzg = 'H'.
bestand-endmenge = bestand-endmenge + weg_mat-menge.
READ TABLE imara WITH KEY matnr = bestand-matnr.
MOVE imara-meins TO bestand-meins.
"bestand-endmenge = bestand-endmenge * ( -1 ). " on 13.12.2011
COLLECT bestand.
ENDLOOP.
**********************************End DEV*****************************

LOOP AT bestand WHERE matnr = it_matnr-matnr."it_raw-matnr2 .
CLEAR sum_mat-menge.
READ TABLE sum_mat WITH KEY werks = bestand-werks
matnr = bestand-matnr
shkzg = 'S'.
MOVE sum_mat-menge TO bestand-soll.
CLEAR sum_mat-menge.
READ TABLE sum_mat WITH KEY werks = bestand-werks
matnr = bestand-matnr
shkzg = 'H'.
MOVE sum_mat-menge TO bestand-haben.
bestand-anfmenge = bestand-endmenge - bestand-soll
+ bestand-haben.

MODIFY bestand.
ENDLOOP.

SORT bestand BY matnr.
LOOP AT bestand WHERE matnr = it_matnr-matnr."it_raw-matnr2 .
AT END OF matnr.
SUM.
MOVE-CORRESPONDING bestand TO bestand2.
APPEND bestand2.
ENDAT.
ENDLOOP.

ENDLOOP.
*=====================================================
ENDFORM. " FIN_STOCK

Thank$

[former_member184080]

Hi Roslin,

I strongly recommend you to post your query in ABAP community. You will get the correct guidance as its purely coding related one.

You can use below link to post your queries related to ABAP.

http://scn.sap.com/community/abap

Regards, Sai Krishna.

[Former Member]

Normally due to some special fields you need to develop a report for this, but I always prefer to use standard for part analysis.  For example, in one of my Clients they had a 700 page Legacy System report showing stock movements for the month ... now who is going to read this document.

I always like to review the MB* transactions and review the MC* transactions..  Good examples:-

MCBA / MC.1 / MC46 / MC50

MCTA, etc

If you have a good background with LIS/SIS you could configure your own structure and do it from there too.  And as Ali has pointed if you do go down the ABAP bath then there will be multiple changes and bug fixes.

Regards

Waza

[moazzam_ali]

Roslin

We also have a same report with ZPSI Tcode. PSI means Production, Sale and Inventory. For this report we have struggled a lot and after so many issues finally we have accurate report. We can give material number or material group in selection with dates and system shows it's opening stock, procurement, production, sale and inventory (closing stock).

For this first you have to see whether you are creating this report on date wise or month (period) wise. If this is date wise then you have to calculate closing stock. For opening stock use MARDH table. For calculating stock consider all movement types and use table MSEG and MKPF. You will never get closing stock date wise in any SAP table. Historical data is always period wise.

For sale you have to use tables VBRK and VBRP. Invoice value, quantity, material number and unit of measure etc are in these fields.

Discuss this with your ABAPER and update me if you need more clarification.

Thank$

[former_member184080]

Hi,

Could you please explain bit more on closing stock quantity. Are you referring to the stock which is at storage location?If so why are you involving sales stock in closing stock ?

Please help me on closing stock, so that we can guide you.

I have developed similar report for one of my India implementations. Its very cumbersome report.So, be clear on the requirement and also, take help from MM as we need to consider lot of movement types.

Regards, Sai Krishna.

[Matthias038]

take a look at mb5b

regards

Matthias

[former_member182378]

Roslin,

Can you please give an explain for Closing stock quantity?

Stock at the end of the day could also be understood as closing stock quantity!

What is the definition of sales quantity? Is it sales order quantity or billing document quantity?

For stock, you can look into

- MKPF, with posting date

- take the material documents got, and put in table MSEG, with movement type = 101, material and get the quantity

For sales quantity and value, you can look into

- VBRK / VBAK

- and then into VBRP / VBAP and get the quantity and the netwr (net value) at line item level, for a material.

[Former Member]

Thank you, sir, for trying to help. I need to develop this report for one of our principle vendors. It must execute based on the Sales Organisation. As per the definitions given by them, Closing stock quantity is the current month closing stocks which are laid on as per current date. Sales quantity is the billing document quantity.

[moazzam_ali]

Roslin

For this you have to calculate stock. Like in my below post take opening stock from MARDH and by considering all debit and credit movements calculate closing stock on current date. This is automatically plus and minus all movements and give you closing stock. You have to tally this stock with MB5B report.

For sale quantity VBRP and VBRK tables. But for catering sale returns, don't forget to add minus indicator if billing type is sale return otherwise system will add sale return quantity in Sales.

Thank$

[former_member184080]

Hi,

Further to Ali's clear explanation, please ingnore the billing types which are related to Credit memo/Debit memo (For cash Difference) as they don't play any role in stock movements.

I suggest you to have a disucssion with MM and ABAPer to finalise the movement types and other calculations.

Let me know if you need any further inputs.

Regards, Sai Krishna.

[Former Member]

Okay... will try and get back to you. Thank you very much.

[moazzam_ali]

Yes sure. Please discuss with your technical guys and test. Also consider that you are going to use MSEG table in which there are millions/billions of entries exist so there might be performace issue if you give a year in selection or so. So make date field mandatory and try to execute this on monthly basis. I have shared this because in my case I had performance issue and system was taking 15/20 minutes for a single material. We had resolved that issue too but first you create the report and update with your findings.

Thank$

[former_member182378]

Roslin,

Both Moazzam and Kesari have brought forward an interesting point - Document category & calculation of Quantity of sales; in the report.

In your report, you would also like to have Sales quantity value. Please think if you want to include transactions done in debit and credit processes in to the Sales value!

The quantity might be ignored as there are no physical movement of goods, but the value paid / received via these processes, would business want to keep track of that?

[Former Member]

I will need to execute this report daily. Scheduling it as a background job. Hopefully, things should go well.

Below is the initial coding I have done. I'll do the necessary changes and get back to you.

-----------------------------------------------------------------------

prev_date = ( sy-datum - 1 ).

   v_date  = ( sy-datum - 1 ).

   v_month = v_date+0(6).

   SELECT DISTINCT matnr

                FROM eord

                INTO TABLE lt_eord

                WHERE lifnr IN vendor .

   SELECT matnr mzubb magbb

           FROM s031

           INTO TABLE lt_s031

     FOR ALL ENTRIES IN lt_eord

           WHERE matnr = lt_eord-matnr

           AND spmon LT v_month.

   LOOP AT lt_s031 INTO wa_s031.

     wa_opnstk-matnr = wa_s031-matnr.

     wa_opnstk-ostock = wa_s031-magbb - wa_s031-mzubb.

     IF wa_opnstk-ostock < 0.

       wa_opnstk-ostock = wa_opnstk-ostock * -1.

     ENDIF.

     COLLECT wa_opnstk INTO lt_opnstk.

     CLEAR wa_opnstk.

   ENDLOOP.

[Former Member]

For Sales returns, we have separate columns: Return quantity and Return value. What I plan on doing is to separate these from the sales quantity based on the Billing type.

[former_member184080]

Good. However, While considering the sales qty, please eliminate return document types, Credit/Debit memo's with value difference. Otherwise, it will never tally.

Regards, Sai Krishna.

[jignesh_mehta3]

Hello,

MB5B will surely give the necessary details you are looking for.

You can also create a Variant & schedule a background job which will run everyday at a fixed time .This Variant can also calculate Start date (begining of month) & current date dynamically.

Thanks,

Jignesh Mehta

[Former Member]

Okay... I did think of this but how do I get it in the format our vendor requires?

[former_member184080]

Hi,

There is another SCN thread in ABAP on Opening/Closing stock. It may help you.

http://scn.sap.com/thread/551531

http://scn.sap.com/thread/1583228

https://scn.sap.com/thread/2019662

Regards, Sai Krishna.

[moazzam_ali]

Dear Jignesh

MB5B can not give sales quantity and value. We can use MB5B just to tally the opening and closing stock.

Thank$

[former_member184080]

Dear Moazzam,

MB5B will gives all type of movements for any material. Yes, I agree that you will not get the sales value from this.

Regards, Sai Krishna

[former_member182378]

Moazzam,

Thank you for your post!

For sale quantity VBRP and VBRK tables. But for catering sale returns, don't forget to add minus indicator if billing type is sale return otherwise system will add sale return quantity in Sales.

As Roslin is reporting from two perspectives - one, stock and two, sales quantity and value.

Your comment about RE sales should not be considered as normal sales. More specifically RE sales is "opposite" of normal sales.

But in case of stock, this quantity is adding to the plant stock.

Roslin,

How are you thinking of reporting stock - only unrestricted or you are going to include Quality inspection and blocked stock too?

Probably you can think this point over, and discuss with the business, along with the other points.

[former_member182378]

Roslin,

Thank you for this interesting thread!

 I need to develop this report for one of our principle vendors. It must execute based on the Sales Organisation.

I can understand, to extract data (for reporting purposes) based on Sales Organization.

But how would you keep track of Stock based on Sales organization?

[former_member182378]

Roslin,

If you are considering to report Sales value, would it be a good idea to have a field "Currency" (in your report)?

Please confirm with your client, would billing document currency do? or it should be some other currency? (e.g. currency of the company code)

[Former Member]

I discussed this with one of my seniors. We are taking all of the stock types. Tried random material numbers from MARD table and tallied the results with Mb5b report. Exact match in opening and closing stocks! Hoping to complete the rest of it soon and get back to you with the results.

[Former Member]

Since this is going out on a global level, our principle vendor has standardized the format they require. Should they come back later asking us to add the currency, I assume it would be, most likely, the local currency that is chosen, since this is what is used in the billing document. Let's see.

[moazzam_ali]

Roslin

I will also recommend you to take all stock types in your report to get accurate results. We are also doing the same. Your report should tally with MB5B.

Thank$

[moazzam_ali]

You should take currency from vbrp table. If you are using local currency or some foreign currency all currencies used in SD invoices can be fetched from VBRP with their values. Add this before vendor asks for it.

Are you creating this report in alv or in fix lay out write statement?

[former_member184080]

Hi TW,

You are correct. But, Moazzam is also correct. You are not supposed to consider the return stocks into sales stock. That's why we have eliminate this and show either as a returns stock or add into available stock. But, we have a risk again here, return stock may be good in condition or we have to scrap.

So, its advisable to show that as a separate return orders column. Anyway, its upto business  to decide how they want the report. Ofcourse, we as a consultant has to guide them

Regards, Sai Krishna.

[former_member184080]

Hi TW,

In my case we developed at sales org level. Simply becasue the client has only one plant and sales org.

Regards, Sai Krishna.

[Former Member]

In the SKU Master report, price must be given in local currency. Which is why I guess, they are not taking it again in the stock and sales report.

[Former Member]

Just need some clarifications. After getting the opening stocks, I added the below code. But it only picked up details of one item. How do I modify this to add the closing stocks of all the materials? Do I need to call in MARDH or any other table(s) also?

-------------------------------------------------------------------

SELECT matnr labst umlme insme einme speme retme

           FROM mard

           INTO TABLE lt_mard

     FOR ALL ENTRIES IN lt_eord

           WHERE matnr = lt_eord-matnr

     AND lfgja = v_year

     AND lfmon = v_month.

   LOOP AT lt_mard INTO wa_mard.

     wa_mard2-matnr = wa_mard-matnr.

     wa_mard2-cstock = wa_mard-labst + wa_mard-umlme + wa_mard-insme + wa_mard-einme + wa_mard-speme + wa_mard-retme.

     IF wa_mard2-cstock < 0.

       wa_mard2-cstock = wa_mard2-cstock * -1.

     ENDIF.

     COLLECT wa_mard2 INTO lt_mard2.

     CLEAR wa_mard2.

   ENDLOOP.

   SORT lt_mard2.

[Former Member]

Issue is:-

WHERE matnr = lt_eord-matnr

Though this is getting into ABAP now and not SD!

Regards

Waza

[Former Member]

Ok. But I need it to take all the materials that were extracted into the internal table of EORD. What do I do?

[former_member211108]

Hi All,

Interesting thread..

I am just wondering what will happen to the stock which is delivered (pgi done) but not billed. Billing might take place latter??

[moazzam_ali]

Dear

For this report you have to make sure that billing is done timely otherwise there will be miss match in sales and stock.

Thanks

[former_member211108]

Hello,

May be you can change the logic so that all the possible ifs and buts considered

Thanks and Regards

Atulkumar Dagade

[former_member184065]

Dear,

Your requirement is not possible by one thing .You need to get data from many sources into Excel and you have to observe .Please have look into MM60 and MC.1 T.Codes .

Note : There are so many sources to get the Data from Many reports .Please search in Google .

Thanks,

Naren