Code for fetching MSEG-DUBMTR data positive/negative in Query

[Former Member]

Dear Experts,

I've made a query where the main table is MSEG and join with MKPF.

But I need to fetch data from DUBMTR (Amount in Local Currency) and MENGE_D (Quantity) as positive/negative value depends on following movement type.

101, 918, 919, 262, 602 as positive

102, 917, 920, 261, 601 as negative.

Would you please advice to make coding for this.

Thanks in advance

Zaied

[Sijin_Chandran]

Hello Zaiedul ,

In MSEG there is a field SHKZG ( which is Credit Debit indicator )

SHKZG = S ( Debit )  = +ve Sign

SHKZG = H ( Credit ) = -ve  Sign

Based on this indicator you can Multiply you MENGE and DMBTR with -1.

[Lokeshchemiti]

Hi Zaied,

Select values(DMBTR and MENGE - - - -) from mseg based on BWART(movement type) linking MSEG & MKPF with MBLNR,MJAHR ..,Then In loop pass the values based on SHKZG(Debit/Credit Indicator) .

or

Select values(DMBTR and MENGE - - - -) from mseg based on BWART(movement type) and SHKZG(Debit/Credit Indicator) ,linking MSEG & MKPF with MBLNR,MJAHR ..,into two different itab's .

Regards,

Lokesh

[Former Member]

Hello dears,

Thanks for your advice but would you please describe the codes to be written to.

I'm sorry that I couldn't know how to write it.

Thanks again...

[former_member187748]

Hi,

you can write your code as shown below, Please change it according to your use

Loop at itab INTO wa_tab

if wa_tab-SKHZG = 'S'.

       wa_tab-menge =  wa_tab-menge * -1.

       wa_tab-dmbtr = wa_tab-dmbtr * -1.

     ENDIF.

MODIFY TABLE itab TRANSPORTING menge dmbtr.

ENDLOOP.

[Lokeshchemiti]

Hi Zaied,

Here is the sample code,

Select DMBTR MENGE from MSEG into Corresponding fields of table it_mseg(internal table) where MBLNR = IT_MKPF-MBLNR and BWART = ('101', '918', '919', '262', '602' ,'102', '917', '920', '261', '601').

Loop at it_mseg into wa_mseg.

if SHKZG = 'H'."Credit

pass the values to final table  .

elseif SHKZG = 'S'. "Debit

pass the values to final table & multiply with minus.

endif.

endloop.

Hope this helps.

Regards,

Lokesh

[Sijin_Chandran]

While Fetching data from make sure you fetch SHKZG also ,

After that ,

LOOP at IT_MSEG into WA_MSEG.

    IF WA_MSEG-SHKZG = 'H'.

          WA_MSEG-DMBTR = WA_MSEG-DMBTR * -1.

         WA_MSEG-MENGE = WA_MSEG-MENGE * -1.

    ENDIF.

MODIFY IT_MSEG FROM WA_MSEG TRANSPORTING DMBTR MENGE.

ENDLOOP.

H is for Credit Entries and that will be for Reverse Movement type in short for Negative entries.

[Former Member]

Hi Zaiedul Hoque,

                              First of all there is no need for a JOIN between MSEG and MKPF.

Then for you selection of DMBTR and MENGE pls do,

DATA: BEGIN OF it_temp,

             dmbtr TYPE dmbtr,

             menge TYPE menge,

           END OF it_temp.  

CASE mwart.

     WHEN 101 OR 918 OR 919 OR 262 OR 602.

          SELECT dmbtr menge FROM mseg INTO it_temp WHERE bwart = (101, 918, 919, 262, 602).

     WHEN 102 OR 917 OR 920 OR  261 OR 601.

          SELECT dmbtr menge FROM mseg INTO it_temp WHERE bwart = (102, 917, 920, 261, 601).

           LOOP AT it_temp INTO wa_temp.

                wa_temp-bmbtr = wa_temp-dmbtr * -1.

                  wa_temp-menge = wa_temp-menge * -1.

                  MODIFY it_temp FROM wa_temp INDEX sy-tabix.

            ENDLOOP.  

ENDCASE.

HOPE THIS HELPS.

Happy Coding,

Santhosh Yadav

[Former Member]

Dear Mr.Santhosh,

Thanks for your coding.You are right "no need for a JOIN between MSEG and MKPF", I just added MKPF under MSEG to get some additional information.

And for the codes, normally for including any additional field I do create an additional field and then insert codes in that Field's code area.

But as I've seen your coding there probably two data described for Qty and Amount. Actually where should I insert them? Please describe. Hope you don't mind observing my depth in this area.

Thanks/ Zaied

[Former Member]

Hi Zaiedul,

                I don't really completely understand your question but for explanation, I have declared an internal table to hold multiple entries or records, as there can be many entries for each of those movement types with declaration

DATA: BEGIN OF it_temp,

             dmbtr TYPE dmbtr,

             menge TYPE menge,

           END OF it_temp.  

     whereas the fields dmbtr and menge are each fields or part on an internal table.

I have taken a case for each movement type. If your selection of data is based on movement type. if your selection is not based on movement type just ignore the CASE statements alone and pick those SELECT queries alone.

Actually I missed the "TABLE" and "IN" part in my select query.

the select query should be like,

SELECT dmbtr menge FROM mseg INTO TABLE it_temp WHERE bwart IN (101, 918, 919, 262, 602).

                                                            and

SELECT dmbtr menge FROM mseg INTO TABLE it_temp WHERE bwart IN (102, 917, 920, 261, 601).

Hope this Helps:)

Happy Coding,

Santhosh Yadav