Link between invoice number and material document number

[Former Member]

I need a way to get the list of material document numbers which correspond to a particular invoice. Is there a way to find this information? I am looking [looping] at table EKBE during a user exit in SAPMV60S but I need to restrict the entries by a range of (multiple) material document numbers.

Regards,

Davis

[Former Member]

Hi

with the help of PO and Item No (EBELN and EBELP) you have to take the link between the MBLNR and BELNR.

Take the info from the tables RSEG and RBKP which belongs to Invoice

Pass the EBELN and EBELP and INVOICE no

pass the same EBELN and EBELp to MSEG table and take the MBLNR (material Doc No)

also can check the EKBE table

Regards

Anji

[Former Member]

The trouble is that in EKBE we have a key comprised of several fields which is non-unique. What that means is that I can not use the PO number and item number of the PO (EBELN and EBELP) to find the material document number because it will not return just one material document number.

Davis

[Former Member]

Hi

Take the EKBE table

use the fields EBELN and EBELP (item No) along with BETYP (doc Category) (like E for GR or S for IR) to restrict the search to get the correct GR/IR Number

Regards

Anji

[Former Member]

I do not have a field called BETYP in EKBE. Also I can't use PO number and PO item because they will be the same. The only way to differentiate entries in EKBE is by using the date but I can't use the date because it will give me the same problem that I have now; the possibility of the same material document being tallied twice in the same day.

Is BETYP supposed to be in EKBE or another table?

Regards,

Davis

[Former Member]

It is VGABE not BETYP. Take a look at the values and you will figure it out. Also compare it with some examples in your system as to which one is the correct VGABE to use.

[Former Member]

The won't work. The only thing that I can remotely use in EKBE is BUDAT but it won't work either because I am tracking payments and two payments (two separate invoices) can be created on the same day.

I need a way to get the material document number based on the invoice number.

Davis

[Former Member]

The link between MBLNR (Material Doc num) and Invoice is

You can join MKPF and MSEG using MBLNR and MSEG will have EBELN and EBELP fields.

Sri

[Former Member]

The fields that you need to join EKBE and MSEG uniquely are the following

From EKBE:

BELNR -Number of Material Document

GJAHR -Material Document Year

BUZEI -Item in Material Document

These correspond to the following fields in MSEG, which make up the unique primary key:

MBLNR -Number of Material Document

MJAHR -Material Document Year

ZEILE -Item in Material Document

It's up to you whether you want to do an INNER JOIN, or load the data using FOR ALL ENTRIES, or do loop with a select-- but these fields will give you a unique connection.

The same thing applies when joining EKBE with MKPF, except that you don't need the Item in Material Document (BUZEI / ZEILE).

Therefore, the fields that you need to join EKBE and MKPF uniquely are the following

From EKBE:

BELNR -Number of Material Document

GJAHR -Material Document Year

These correspond to the following fields in MKPF, which make up the unique primary key:

MBLNR -Number of Material Document

MJAHR -Material Document Year

The invoice corresponds to XBLNR in BKPF.

You can join EKBE to BKPF as follows:

For the EKBE entry with BEWTP = 'Q', join EKBE and BKPF using the following fields. BEWTP is the PO history category.

The names are the same in both files, and these make up the unique primary key for BKPF:

BUKRS -Company Code (you need to get this from EKKO)

BELNR -Accounting Document Number

GJAHR -Fiscal Year

Good luck

Brian

Fix typo, add BKPF info

Message was edited by:

Brian Sammond

[Former Member]

Brian, thanks for your reply.

I think I may have not explained it well. I need the material document number. All I have is the invoice (billing document). What is happening is we charge 3rd party freight and we do it through MIGO; we started using MIGO after the ECC 5.0 upgrade. We do multiple billings to one PO and each billing <b>could</b> have 3rd party freight added on. There can even be multiple billings (material document numbers) per invoice and there can be multiple invoices on the same line item per day. When we are calculating the total bill for 3rd party freight the code (I am trying to piece together 3 coder's code and figure out what it is doing) is doing a select * on EKBE passing in the Purchase Order number. What this is doing is running each time MIGO runs.

What ends up happening is that if I make a billing (invoice with many possible material documents)on PO 1 on 10/4/07 and have a total 3rd party freight charge of $5 for the day. Then on 10/5 I bill a few more line items (or the same line item) and that creates a few material document numbers. This code is run each time. Now what happens is that when it loops at EKBE for the PO it also sees the billings for 10/4 so it ends up adding 10/4's total to the total on 10/5. Basically the charges are being inflated. We can't filter by date because there could be several invoices for the same day on the same PO. The only other possible way to filter EKBE that I can think of is by material document number. So I need to either use the PO, SO, or invoice number in order to get the material document number.

I apologize but I do not have my work laptop with me right now so I can't post the code until the morning. Sorry for the long post too.

Davis

[Former Member]

Alright, here is the code in question. I will do my best to explain what is happening (to the best of my knowledge). Three coders have worked on this and there are no comments so I will add a few in.

  clear temp_val.
  clear temp_valr.
  clear temp_val1.
* Get the PO no and the line item no of PO through the document flow of
* Sales order line item.
  SELECT * FROM VBFA INTO Z_VBFA WHERE VBELV = VBAP-VBELN AND
                           POSNV = VBAP-POSNR AND
                           VBTYP_N = 'V'.
    check z_vbfa-posnn eq '000001'.
    W_IND = 'X'.
* Getting the PO history for the line item.
    SELECT SINGLE * FROM EKKO INTO Z_EKKO WHERE EBELN = Z_VBFA-VBELN.

****************************************************************
* This is where I have the problem.  This select will get all material document numbers (billings)
* for the PO but I need to restrict the results (lines) to certain billings based on an Invoice number.
    SELECT * FROM EKBE WHERE EBELN = Z_VBFA-VBELN AND
                  EBELP = Z_VBFA-POSNN.
****************************************************************

        X_KEYFLD(10) = EKBE-BELNR.

        X_KEYFLD+10(1) ='%'.
*
        SELECT SINGLE * FROM BKPF WHERE BUKRS = Z_EKKO-BUKRS AND
*                                       BELNR = EKBE-BELNR AND
                                        AWKEY LIKE X_KEYFLD AND
        CHECK SY-SUBRC = 0.
* Getting the Vendor Invoice header data
        SELECT SINGLE * FROM BSEG WHERE BUKRS = Z_EKKO-BUKRS AND
                                        BELNR = BKPF-BELNR AND
                                        HKONT = '0000416000'.
        CHECK SY-SUBRC = 0.
* Move the Unplanned Delivery Cost to the new field defined in komp.
        IF BSEG-SHKZG = 'S'.
          ADD BSEG-DMBTR TO TEMP_VAL.
          CLEAR BSEG.
        ELSE.
          TEMP_VAL = TEMP_VAL - BSEG-DMBTR.
          CLEAR BSEG.
        ENDIF.
        W_BELNR = EKBE-BELNR.

    ENDSELECT.
  ENDSELECT.

  MOVE TEMP_VAL TO TKOMP-ZZTPF.
  MOVE XVBRP-PSTYV TO TKOMP-ZZPSTYV.

[Former Member]

Davis,

There is no direct link between an Invoice and its corresponding material document. They both are linked to the PO, but not to each other.

[Former Member]

That is what I discovered too but I assume there has to be a way to link them together using one, or more, documents/tables. If all else fails I can always append EKBE and use it as a flag to say if the billing was already "used"

Davis.

[Former Member]

It looks like you're missing a piece of code.

        SELECT SINGLE * FROM BKPF WHERE BUKRS = Z_EKKO-BUKRS AND
*                                       BELNR = EKBE-BELNR AND
                                        AWKEY LIKE X_KEYFLD AND
        CHECK SY-SUBRC = 0.

The SELECT just ends at the "AND".

Should there be another line after the "AND"?

You said you had an invoice number; why can't you use that in your WHERE clause to limit what is returned from BSEG?

What field are you using for the invoice?

I had mentioned BKPF-XBLNR before, which we use to store the vendor invoice on a PO, but what you are doing sounds different. Are you referring to an invoice that you created?

I think that this might be in BSEG-REBZG, and you should be able to filter on this field.

Shouldn't such an additional filter give you what you want?

e.g.

if the invoice specification is a single value:

* Getting the Vendor Invoice header data
  SELECT SINGLE * FROM bseg WHERE bukrs = z_ekko-bukrs AND
                                  belnr = bkpf-belnr AND
                                  hkont = '0000416000' AND
                                  rebzg = invoice

or, if the invoice specification is a range or list:

* Getting the Vendor Invoice header data
  SELECT SINGLE * FROM bseg WHERE bukrs = z_ekko-bukrs AND
                                  belnr = bkpf-belnr AND
                                  hkont = '0000416000' AND
                                  rebzg IN s_invoice

If you are not using rebzg, then do the same against whatever field you are using.

Good luck

Brian

[Former Member]

I will have to see if we use BSEG-REBZG in that capacity, if so then I think it should work. Unfortunately I will not be able to do any work with it until Friday. I will revisit this thread, and your suggestion, then.

Thanks, for the help,

Davis