How to countrol report output for movt. type (101,102,122,123,161 &162)

[former_member220553]

Hi Gurus ,

I have an assignment to create a development report to see pending bill against MIGO VS MIRO with rate difference that posted in MIRO.

How Can I Consider Only the payment related data only,

Requirement:

This output should not show any reversal on cancellation related data.

For example: PO: 100, (Value: 100000) GR, 100 MIRO, 100, (Value 100000), Payment (Value 100000)  (It should swhow)

                   1.   If  payment is cancelled output should  not show payment related data.

                    2. If Invoice is cancelled report should not show Invoice related data for that materiMal (Line item or doc).

                    3. If MIGO is cancelled for any particular line item with movt type 102 that particular line item qty ( both 101 and 102 should not show in                          out put screen .

                    4. For vendor return if both 122 and 123 is used for the particualr line item that line item should not show in the output screen.

                     5. Say for line item 10 material has been received with mvt type 101 qty 100, vendor return 50 with movt type, 122, then out put should

                          show 50 qty with Movt 122 and 50 with movt, 101.

                    6.  Movt type 161 related item should show, however for cancel with Movt type 162 for that line item should not show.

`         

Input Screen:

TABLE:     EKKO,EKPO and EKBE,

     Vendor                (EKKO)               From     To

       Plant                  (EKBE)              From    To (Mandatory)

      GRN No              (EKBE)              From    To

      Posting Date      (EKBE)                From    To  (Mandatory)

Output Field in Report.

Header 1	Header 2	Header 3	Header 4	Header 5	Header 6	Header 7	Header 8	Header 9	Header 10	Header 11	Header 12	Header 12
Po No	Po Date	Material No	Material Description	Plant	PO Qty	Rate/Unit	Total Amount	GRN No	GRN Date	GRN Qty	Delivery Note	Inv No
40000000000	10.10.2013	ROH001	Steel 8 mm	1000	10	TON	500000	50000000000	25.10.2013	100	1234	5100000001
Inv Date	Inv Qty	Inv. Amount,	Delivery Note	MIRO Text	Clearing doc	clearing date	Check No	Amount
25.10.2013	100	500000	1234	xx	6000000000	26.10.2013	C1231	500000

Subject was edited by: Jürgen L

[former_member183424 · Accepted solution]

Input Screen:

TABLE:     EKKO, EKPO and EKBE,

Vendor (EKKO) From     To

Plant (EKBE) From    To (Mandatory)

      GRN No (EKBE) From    To

      Posting Date (EKBE) From    To  (Mandatory)

Fetch data as per entered data

Vendor = EKKO-LIFNR

PLANT = EKPO-WERKS

Posting date = EKKO-BEDAT

Output Portion

Header 1 will be fetch from EKKO-EBELN {as per PO number (EBELN)}

Header 2 will be fetch from EKKO-BEDAT {as per PO number (EBELN)}

Header 3 will be fetch from EKKO-MATNR {as per PO number (EBELN)}

Header 4 will be fetch from MARA-MAKTX {as per material (MATNR)}

Header 5 will be fetch from EKPO-WERKS {as per PO number (EBELN)}

Header 6 will be fetch from EKPO-MENGE {as per PO number (EBELN)}

Header 7 = EKPO-NETPR {as per PO number (EBELN)}

Header 8 = EKPO-NETWR {as per PO number (EBELN)}

Header 9 = MSEG-MBLNR {as per PO number (EBELN)}

Header 10 = MKPF-BUDAT {as per Material document number (MBLNR)}

Header 11 = MSEG-MENGE {as per Material document number (MBLNR)}

Header 12 = MKPF-XBLNR {as per Material document number (MBLNR)}

Header 13 = RSEG-BELNR {as per PO number (EBELN)}

Header 14 = RBKP-BUDAT {as per Invoice Number (BELNR})

Header 15 = RSEG-MENGE {as per Invoice Number (BELNR})

Header 16 = RSEG-WRBTR {as per Invoice Number (BELNR})

Header 17 = RBKP-XBLNR {as per Invoice Number (BELNR})

Header 18 = RBKP-SGTXT {as per Invoice Number (BELNR})

And last 4 outputs you can contact you FI consultant.

Thanks

DEV

[former_member220553 · Accepted solution]

Hi Dev,

First of all I appreciate your effort.

I have completed  that part, however earlier I mentioned  I am stacking to get below required condition type.

Any suggestion should be appreciated.

Requirement:

This output should not show any reversal on cancellation related data.

For example: PO: 100, ( Net Value: 100) GR, 100 MIRO, 100, (Invoice Value 100000), Payment (Value 100000)  (It should swhow)

                   1.   If  payment is cancelled output should  not show payment related data.

                    2. If Invoice is cancelled report should not show Invoice related data for that materiMal (Line item or doc).

                    3. If MIGO is cancelled for any particular line item with movt type 102 that particular line item qty ( both 101 and 102 should not show in out put screen .

                    4. For vendor return if both 122 and 123 is used for the particualr line item that line item should not show in the output screen.

                     5. Say for line item 10 material has been received with mvt type 101 qty 100, vendor return 50 with movt type, 122, then out put should

                          show 50 qty with Movt 122 and 50 with movt, 101.

                    6.  Movt type 161 related item should show, however for cancel with Movt type 162 for that line item should not show.

`        

Br,

SKN.

[former_member183424 · Accepted solution]

Hi,

For 1 no point please contact with FI consultant. He can give you better information.

2. If the field RBKP-STBLG is updated with another invoice number, then delete this line item for invoice. (this field is updated when old invoice is cancelled.

see the screen shot.

3. On Table MSEG-MENGE as per EBELN and EBELP, logic will be against field BWART value 101 = Positive and 102 = Negative. You can also use field SHKZG value S= Positive and H = Negative.

If total of MSEG-MENGE = 0 (ZERO), then omit this line on Report.

4. Same as 102 and 102, it will be 122 and 123 instead of 101 and 102 . Condition will same as discuss on Option 3.

5. If you give the logic as option 3 and 4. It will calculate automatically.

6. Its also same as option 3. Use Movement type 161 and 162 instead 101 and 102.

Just tell your ABAPer to give this logic (as discuss on option 3) for all types of movement type OR the field SHKZG.

Your report will be same as per your requirement.

I have developed the same report..

Thanks

DEV

[former_member220553 · Accepted solution]

Hi Dev  ,

As per your given point No3.  I applied that logic in coding parT, however entire line item is deleting one by one.

Please share your view on this.

Below mention logic is applied.

READ TABLE GT_EKBE INTO GS_EKBE WITH KEY EBELN = GS_EKBE-EBELN
                                           EBELP = GS_EKBE-EBELP
                                           MENGE = GS_EKBE-MENGE .

  IF SY-SUBRC EQ 0 .
      DELETE GT_EKBE WHERE SHKZG EQ 'S' AND
                          EBELN = GS_EKBE-EBELN AND
                          EBELP = GS_EKBE-EBELP AND
                          MENGE = GS_EKBE-MENGE .
      DELETE GT_EKBE WHERE SHKZG EQ 'H' AND
                          EBELN = GS_EKBE-EBELN AND
                          EBELP = GS_EKBE-EBELP AND
                          MENGE = GS_EKBE-MENGE ." AND

    ENDIF .

Br,

SKN.

[former_member183424 · Accepted solution]

Hi,

SCN rule: Can't reply for this discussion which has already marked as "Assumed Answered."

Thanks

DEV

[former_member220553 · Accepted solution]

Hi Dev,

Thanks for your this valuable information.

Regards

SKN

[former_member183424]

Hi,

You can go through a development.

Make a FS for your requirement as per your requirement as below.

Just give the table and field name to ABAPer team.

They will give you the Z report as per your requirement.

Thanks

DEV

[Prasoon]

Hi,

   Could you please check the standard report MB5S. I think this report will fullfill all your requirement. If you want to edit or add new fields to this report, you may check for enhancement or a new z report by copying the MB5S program.

Regards,

AKPT

[former_member220553]

HI AKPT,

Can u please suggest according to our requirement bcoz MB5S is not filling our requirement.

BR,

SKN.

[Former Member]

Do you want the exact table names and fields? It will be best if you ask your functional guy.

Now, on logic part, we will take just movement type 101, 102 and 122 and discuss on how to proceed with it.

1. Filling Internal Table GT_EKBE101.

Select all necessary fields from EKBE (PO History) for which movement type is 101( 101 means Goods Receipt).

Code abstract:

SELECT ebeln ebelp belnr bwart other_fields

FROM ekbe

INTO gt_ekbe101

WHERE bwart = '101' AND <your conditions based on input parameters>.

2. Filling Internal table GT_EKBE102122.

Select all necessary fields from EKBE (PO History) for which movement type is 102 and 122( 102 means Goods Receipt Cancellation, 122 means ).

Code abstract:

SELECT ebeln ebelp belnr bwart other_fields

FROM ekbe

INTO gt_ekbe102122

WHERE bwart = '102' OR bwart = '122' AND <your conditions based on input parameters>.

3. Since Movement type 102, 122 is negating the stock amount, reducing the stock amount and 101 is adding to the stock amount, it would be logical to say that net stock =  amount(101) - amount(102+122).

Now we need to remove all entries from GT_EKBE101 which is present in GT_EKBE102122.

GT_EKBE101 (MINUS) GT_EKBE102122:

LOOP AT GT_EKBE101 INTO GS_EKBE101.

TABIX = SY-TABIX.

READ TABLE GT_EKBE102122 INTO GS_EKBE102122 WITH KEY EBELN = GT_EKBE101-EBELN EBELP = GT_EKBE101-EBELP BELNR = GT_EKBE101-BELNR.

IF SY-SUBRC = 0.

GS_EKBE101-FLAG_REMOVE = 'X'  "(FLAG_REMOVE can be a field of type C in structure of GT_EKBE101).

MODIFY GT_EKBE101 FROM GS_EKBE101 INDEX TABIX TRANSPORTING FLAG_REMOVE.

ENDIF.

ENDLOOP.

DELETE GT_EKBE101 WHERE FLAG_REMOVE IS NOT INITIAL.

Like this you can check and validate for remaining conditions/fields too.

Regards,

KS

[former_member220553]

Hi K S,

Please give some lights in your point no. 3

How to subtract from 101 qty and 102+122 qty.

If possible please provide the step.

3. Since Movement type 102, 122 is negating the stock amount, reducing the stock amount and 101 is adding to the stock amount, it would be logical to say that net stock =  amount(101) - amount(102+122).

Now we need to remove all entries from GT_EKBE101 which is present in GT_EKBE102122.

GT_EKBE101 (MINUS) GT_EKBE102122:

As per your valuable suggestion

Br,

SKN

[Former Member]

Hi Sanjib Naik,

Little change while selecting data in "gt_ekbe102122", adding lfbnr and ive written the changes in bold.

SELECT ebeln ebelp belnr lfbnr bwart other_fields

FROM ekbe

INTO TABLE gt_ekbe102122

FOR ALL ENTRIES IN gt_ekbe101

WHERE ( bwart = '102' OR bwart = '122' ) AND

( ebeln = gt_ekbe101-ebeln AND ebelp = gt_ekbe101-ebelp AND lfbnr = gt_ekbe101-belnr ).

Now,

If gt_ekbe102122-lfbr =  gt_ekbe101-belnr, then remove such rows from gt_ekbe101.

" lfbnr = reference document number

In essence, what we are saying is:

gt_ekbe101 - contains all GRs, movement type 101. (Goods Receipt)

gt_ekbe102122 - contains all cancelled and returned GRs FOR ALL ENTRIES IN gt_ekbe101.

Movement type 102 - cancelled GR  (could be because of wrong entry) and 122 - returned GRs (can be because of quality issue or anything).

relation:   gt_ekbe102122-lfbnr (refernce doc num) = gt_ekbe101-belnr (GR document number) along with gt_ekbe101-ebeln = gt_ekbe102122-ebeln and gt_ekbe101-ebelp = gt_102122-ebelp.

Now  to remove all entries from GT_EKBE101 which is present in GT_EKBE102122, using the above explained relation. Code as shown

LOOP AT GT_EKBE101 INTO GS_EKBE101.

TABIX = SY-TABIX. " Current index of table GT_EKBE101

READ TABLE GT_EKBE102122 INTO GS_EKBE102122 WITH KEY EBELN = GS_EKBE101-EBELN EBELP = GS_EKBE101-EBELP LFBNR = GS_EKBE101-BELNR. 

" ^^ Read gt_ekbe102122 (internal tabel) into gs_ekbe102122 (single line structure, work-area) where gt_ekbe101 has same 'ebeln' , same 'ebelp', as that of " " "gt_ekbe102122 AND gt_ekbe101-belnr (GR doc num) should be equal to gt_ekbe102122-lfbnr( ref doc num)

IF SY-SUBRC = 0. "(If the above condition is true, ie READ to gs_ekbe102122 successful)

GS_EKBE101-FLAG_REMOVE = 'X'  "(FLAG_REMOVE can be a field of type C in structure of GT_EKBE101).

MODIFY GT_EKBE101 FROM GS_EKBE101 INDEX TABIX TRANSPORTING FLAG_REMOVE. "Modify GT_EKBE with field "FLAG_REMOVE"

ENDIF.

ENDLOOP.

DELETE GT_EKBE101 WHERE FLAG_REMOVE IS NOT INITIAL. "delete entries from ght_ekbe101 where FLAG_REMOVE has a value

Hope i make sense now

Regards,

KS

[Former Member]

Hi,

You can create such report but you need to check data for Migo & Miro table

example if you dont want to consider cancelled migo logic should be Pass BELNR &GJAHR in the table MSEG , SJAHR = GJAHR & SMBLN=BELNR If you get entry don’t consider this line in report.

[former_member220553]

Hi Vipin,

Thanks for your suggestion vipin and it is working to only delete the cancel document, however I mention earlier I don't want to show both the doc.

For example:

.GR posting with mvt type 101 for line item 1( Doc No. 50000000000). and if  same line item is cancelled with Mvt type: 102  *(Doc No. 5000000001)then both posting and cancellation document should not show in the report.

Br.

SKN.