Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Stock Transfer Report after Invoice

Go to solution
aabhas_wilmar
Contributor

‎10-23-2007 11:55 AM

0 Kudos

Hello Experts

Please spend one minute's time to read this. It may be long but its the exact scenario.

I have got a requirement to generate a Stock Transfer Report for the bills passed (invoice receipt) for a particular date range. Note that the delivery against the PO can be for partial quantities too.

I am using RBKP table to get all the invoice numbers falling in the certain date range selection. Against these Invoices I am retrieving the PO number and other report relevant data from the RSEG Table. And using the PO I need to get into the MSEG table to retrieve Mat Doc numbers so that I can drill into the excise tables to get Excise No.

Now the problem is:

Suppose the PO <b>4900000337 </b>contains various items


item no  | material  | qty
----------------------------
10       | mat1      | 1050
20       | mat2      | 2670
30       | mat3      | 450

Now one partial delivery is made as


item no  | material  | qty
---------------------------
10       | mat1      | 100
20       | mat2      | 250

<i>mat doc 4900012713, mov type 641</i>

and the against this delivery an invoice receipt is created using MIRO, <b>5105600790</b>

another partial delivery is made as 


item no  | material  | qty
---------------------------
20       | mat1      | 350
30       | mat2      | 50

<i>mat doc 4900012714, mov type 641</i>

against this delivery an invoice receipt is created using MIRO, <b>5105600791</b>

How do I link the invoice no to the material documents to know the quantity of the particular item of the PO... as we see item no 20 is common in both the case.

RSEG
Document Number Fiscal Year Item   Purchasing Doc. Item

5105600790      2007        000007 4900000337      00020
5105600791      2007        000007 4900000337      00020

MSEG
Material Doc. Mat. Doc. Year Item Line ID Parent ID Movement Type Material     Quantity          Purchase Order Item

4900012713    2007           0004 000005  000002    641           90050106            250.000  4900000337     00020
4900012714    2007           0002 000003  000001    641           90050106            350.000  4900000337     00020

1 ACCEPTED SOLUTION

Go to solution
narayani_krishnamoorthy
Contributor

‎10-24-2007 11:38 AM

0 Kudos

Hi,

you said you know the purchase order number , Go to table EKBE.

Give the PO number also the item number.

You will see the invoice numbers as well as the material document numbers.

The EKBE table is the purchase order history table.

Regards,

Narayani

9 REPLIES 9

Go to solution
narayani_krishnamoorthy
Contributor

‎10-24-2007 11:38 AM

0 Kudos

Hi,

you said you know the purchase order number , Go to table EKBE.

Give the PO number also the item number.

You will see the invoice numbers as well as the material document numbers.

The EKBE table is the purchase order history table.

Regards,

Narayani

Go to solution
aabhas_wilmar
Contributor
In response to narayani_krishnamoorthy

‎10-25-2007 1:22 PM

0 Kudos

Thanks for replying Narayani...

As you said I went thru the EKBE table but it doesn't contains any Invoice Number... It does contains the material doc no against the PO with mov type 641 also the corresponding delivery note no in XBLNR (reference field) There is one more ref. field LFBNR which is empty (is this the field for inv no. u were talking about for invoice no.?)

Also, even earlier I have visited the other PO History table <b>EKBZ</b> but the problem remains same...

There are tables that link <b>Invoice</b> to <b>PO+Item</b>

<i>and</i>

tables which link <b>PO+Items</b> to <b>Mat doc->delv. doc. -> excise doc...</b>

But none of them is enabling to link the invoice to mat doc !!

and the prob is that the report is to be drawn Bill-wise (inv. no.)...

Can ANYONE help me further?

Regards,

Aabhas

Go to solution
narayani_krishnamoorthy
Contributor
In response to narayani_krishnamoorthy

‎10-25-2007 2:15 PM

0 Kudos

Hi,

Can you tell me the the entire flow again please??

Is my assumption correct??

you created a Stock transfer PO, then created a delivery=> then billing??

if that right then did you check the VBFA table by giving po as preceeding doc and delivery number as subsequent doc.

Hope this help.

thanks

Narayani

Message was edited by:

Narayani

Go to solution
aabhas_wilmar
Contributor
In response to narayani_krishnamoorthy

‎10-25-2007 2:22 PM

0 Kudos

Thanks for understanding the problem.

I do understand everything goes into the tables and links must exist... also because ME23N - PO History is displaying everything I want...

Can I have you email address please...? I have it all documented and would like to mail it to explain further...

Thanks & Regards

Aabhas...

Go to solution
narayani_krishnamoorthy
Contributor
In response to narayani_krishnamoorthy

‎10-25-2007 2:27 PM

0 Kudos

could also read the earlier reply and let me know whether it helped.

you can mail me on scooby96@gmail.com

Go to solution
narayani_krishnamoorthy
Contributor
In response to narayani_krishnamoorthy

‎10-26-2007 5:24 AM

0 Kudos

hi,

I guess I figured out where you will get the value.

you have the purchase order, delivery number, invoice number,

now your problem how to link the material doc with this invoice number

Table BSEG : lists invoice numbers(BELNR) , delivery orders(VBELN), and the corresponding purchase orders(EBELN).

Table VBFA : put value of VBELV as the delivery order number,

you will the material document number(VBELN), item number also.

Table MSEG : you can fill the material doc, item number.

Table EKBE will also list both the delivery number and the material doc number.

Hope this helps.

Regards,

Narayani

Go to solution
aabhas_wilmar
Contributor
In response to narayani_krishnamoorthy

‎10-26-2007 7:31 AM

0 Kudos

Thanks again, but the problem is not yet solved.

I already had a look into the BSEG table but the problem is that it doesn't contains any rows against the invoice no.

For your reference, using the PO number <b>4900000343</b>, I retrieved the following data out of BSEG...

Table:          BSEG
Displayed Fields:  10 of  10  Fixed Columns:                5  List Width 1023

   Document Number Line item Line item ID Posting key Account Type Transaction Value date Assignment         Cost Center Billing Doc.

   0490021088      001       M            99          M            BSX         24.10.2007 20071024                       0010004216
   0490021088      002       U            89          M            BSX         24.10.2007 20071024                       0010004216
   0490021088      003       P            83          S            AUM         24.10.2007 0006201001         0006201001  0010004216
   0490021088      004       F            50          S            FR1         24.10.2007 490000034300010                0010004216
   0490021088      005       M            99          M            BSX         24.10.2007 20071024                       0010004216
   0490021088      006       U            89          M            BSX         24.10.2007 20071024                       0010004216
   0490021088      007       P            83          S            AUM         24.10.2007 0006201001         0006201001  0010004216
   0490021088      008       F            50          S            FR1         24.10.2007 490000034300020                0010004216
   0490021089      001       M            99          M            BSX         24.10.2007 20071024                       0010004217
   0490021089      002       U            89          M            BSX         24.10.2007 20071024                       0010004217
   0490021089      003       P            83          S            AUM         24.10.2007 0006201001         0006201001  0010004217
   0490021089      004       F            50          S            FR1         24.10.2007 490000034300020                0010004217
   0490021089      005       P            83          S            AUM         24.10.2007 0006201001         0006201001  0010004217
   0490021089      006       F            50          S            FR1         24.10.2007 490000034300030                0010004217
   0510000678      002       F            40          S            FR1         24.10.2007 490000034300010
   0510000678      003       F            40          S            FR1         24.10.2007 490000034300020
   0510000679      002       F            40          S            FR1         24.10.2007 490000034300020
   0510000679      003       F            40          S            FR1         24.10.2007 490000034300030

any more suggestions? or any other way to do the STO / Invoice against STO ?

regards,

Aabhas

Go to solution
narayani_krishnamoorthy
Contributor
In response to narayani_krishnamoorthy

‎10-26-2007 8:55 AM

0 Kudos

Hi,

1) MIRO is done against Purchase Order right??

2) In BSEG : you put the PO number 4900000343 then the below rows are visible right??

3) Can you please paste the rows : BELNR, EBELN and VBELN only ??

Can you also show the rows for the PO that you have mentioned in your first message.

Regards,

Narayani

Message was edited by:

Narayani

Go to solution
aabhas_wilmar
Contributor
In response to narayani_krishnamoorthy

‎10-26-2007 9:53 AM

0 Kudos

Hi...

<i>1) MIRO is done against Purchase Order right??</i>

> Yes, MIRO is done against PO. And, when I try to do it against Deliv. Doc. its not possible as its not displaying any items!

<i>2) In BSEG : you put the PO number 4900000343 then the below rows are visible right??</i>

>Yes.

<i>3) Can you please paste the rows : BELNR, EBELN and VBELN only ??</i>

Here they are...(with some other fields which might be of importance)

For <b>PO 4900000343</b>

Table:          BSEG

Document Number Transaction Assignment         Billing Doc. Plant  	Quantity     Purchasing Doc. Item
BELNR      	KTOSL 	    ZUONR              VBELN        WERKS  	MENGE        EBELN           EBELP

0490021088      BSX         20071024           0010004216   2006             50.000  4900000343      00010
0490021088      BSX         20071024           0010004216   6201             50.000  4900000343      00010
0490021088      AUM         0006201001         0010004216   6201             50.000  4900000343      00010
0490021088      FR1         490000034300010    0010004216   6201             50.000  4900000343      00010
0490021088      BSX         20071024           0010004216   2006            100.000  4900000343      00020
0490021088      BSX         20071024           0010004216   6201            100.000  4900000343      00020
0490021088      AUM         0006201001         0010004216   6201            100.000  4900000343      00020
0490021088      FR1         490000034300020    0010004216   6201            100.000  4900000343      00020
0490021089      BSX         20071024           0010004217   2006            200.000  4900000343      00020
0490021089      BSX         20071024           0010004217   6201            200.000  4900000343      00020
0490021089      AUM         0006201001         0010004217   6201            200.000  4900000343      00020
0490021089      FR1         490000034300020    0010004217   6201            200.000  4900000343      00020
0490021089      AUM         0006201001         0010004217   6201            100.000  4900000343      00030
0490021089      FR1         490000034300030    0010004217   6201            100.000  4900000343      00030
0510000678      FR1         490000034300010                 6201             50.000  4900000343      00010
0510000678      FR1         490000034300020                 6201            100.000  4900000343      00020
0510000679      FR1         490000034300020                 6201            200.000  4900000343      00020
0510000679      FR1         490000034300030                 6201            100.000  4900000343      00030

Can you also show the rows for the PO that you have mentioned in your first message.

<b>PO 4900000337</b>

Table:          BSEG

Document Number Transaction Assignment         Billing Doc. Plant  	Quantity     Purchasing Doc. Item
BELNR      	KTOSL 	    ZUONR              VBELN        WERKS  	MENGE        EBELN           EBELP

0490021080      AUM         0006201001         0010004205   6201            350.000  4900000337      00070
0490021080      FR1         490000033700070    0010004205   6201            350.000  4900000337      00070
0490021080      BSX         20071017           0010004205   2006             50.000  4900000337      00100
0490021080      BSX         20071017           0010004205   6201             50.000  4900000337      00100
0490021080      AUM         0006201001         0010004205   6201             50.000  4900000337      00100
0490021080      FR1         490000033700100    0010004205   6201             50.000  4900000337      00100
0490021081      BSX         20071017           0010004206   2006            500.000  4900000337      00010
0490021081      BSX         20071017           0010004206   6201            500.000  4900000337      00010
0490021081      AUM         20071017           0010004206   6201            500.000  4900000337      00010
0490021081      FR1         490000033700010    0010004206   6201            500.000  4900000337      00010
0490021081      BSX         20071017           0010004206   2006            300.000  4900000337      00020
0490021081      AUM         0006201001         0010004206   6201            300.000  4900000337      00020
0490021081      FR1         490000033700020    0010004206   6201            300.000  4900000337      00020
0490021082      BSX         20071017           0010004207   2006            300.000  4900000337      00010
0490021082      BSX         20071017           0010004207   6201            300.000  4900000337      00010
0490021082      AUM         20071017           0010004207   6201            300.000  4900000337      00010
0490021082      FR1         490000033700010    0010004207   6201            300.000  4900000337      00010
0490021082      BSX         20071017           0010004207   2006            250.000  4900000337      00020
0490021082      AUM         0006201001         0010004207   6201            250.000  4900000337      00020
0490021082      FR1         490000033700020    0010004207   6201            250.000  4900000337      00020
0490021101      BSX         20071025           0010004229   2006          1,000.000  4900000337      00010
0490021101      BSX         20071025           0010004229   6201          1,000.000  4900000337      00010
0490021101      AUM         20071025           0010004229   6201          1,000.000  4900000337      00010
0490021101      FR1         490000033700010    0010004229   6201          1,000.000  4900000337      00010
0490021101      BSX         20071025           0010004229   2006            250.000  4900000337      00020
0490021101      AUM         0006201001         0010004229   6201            250.000  4900000337      00020
0490021101      FR1         490000033700020    0010004229   6201            250.000  4900000337      00020
0510000675      FR1         490000033700030                 6201            400.000  4900000337      00030
0510000675      FR1         490000033700070                 6201            250.000  4900000337      00070
0510000675      FR1         490000033700090                 6201             50.000  4900000337      00090
0510000676      FR1         490000033700070                 6201            350.000  4900000337      00070
0510000676      FR1         490000033700100                 6201             50.000  4900000337      00100
0510000677      FR1         490000033700010                 6201            800.000  4900000337      00010
0510000677      FR1         490000033700020                 6201            550.000  4900000337      00020

Thanks. I have added you on GoogleTalk... if you use it, we can discuss there and update the thread...

Regards

Aabhas K Vishnoi