Need Help using FMS

[Former Member]

Hi,

My Scenario:

Itemcode qty price total

a1 1 10 10

b1 1 11 11

a1 2 13 26

Actually, i got the output as

itemcode qty price total

a1 1 10 10

b1 1 11 11

a1 1 10 10

Using FMS, how can i retrieve the above scenario in purchase order screen using UDT.

Please help me.

Edited by: sagar simle on Apr 17, 2008 2:33 PM

[former_member583013 · Accepted solution]

You will need to explain your question in a little more detail

[Former Member · Accepted solution]

Hi,

My Scenario:

Itemcode qty price total

a1 1 10 10

b1 1 11 11

a1 2 13 26

Actually it is a work around for Amendment concept. Using FMS i need to Retrieve the above data.

Using FMS i will call all open PO in a UDF. If i select any PO number, By usinf Sgift+F2, all above are displayed in the itemcode, but it shows an wrongly.

itemcode qty price total

a1 1 10 10

b1 1 11 11

a1 1 10 10

Please see the difference for the itemcode a1.

Using FMS, how can i Retrieve the above scenario in purchase order screen using UDT.

If i am having different item in PO i can Retrieve, but if it is same item then shows an wrong data.

Please help me.

SAGAR

[former_member204969 · Accepted solution]

You should copy here the query text producing the erroneous result,

[Former Member · Accepted solution]

Sorry Istav,

I can't get you. Can u explain it breifly?

SAGAR

[Former Member · Accepted solution]

Sagar,

without the query is not easy to help you.

But I think that the problem is in the query, where when the item is moretime in order you worked allways with first line where the item occurs (or you are using select top 1). Try to use sum or make condition that the line you want receive isnt in previous result.

Petr

[former_member583013 · Accepted solution]

Sagar,

I am sorry to say but I am having difficulties understanding your language. Please answer the following

When you say "Using FMS i need to Retrieve the above data"

Itemcode qty price total

a1 1 10 10

b1 1 11 11

a1 2 13 26

What is this DATA and where is it coming from? Is it Purchase Order rows or from somewhere else?

Using FMS i will call all open PO in a UDF.

Where is this UDF?

If i select any PO number,

How are you selecting PO Number?

You can use a query like this to get open po quantities

SELECT T0.ItemCode AS 'Item No.', T0.OpenQty AS 'Quantity', T0.Price AS 'Price', T0.LineTotal AS 'Total' FROM [dbo\].[POR1\] T0

WHERE T0.LineStatus = N'O'

[Former Member · Accepted solution]

Hi,

Sorry for my language.

My Scenario:

I am working on workaround for PO Amendment. I have decided to call the Old PO thru FMS to amend its price which will be saved as a new PO.

My UDF Name: U_AMENDMENT

I have created a UDF in PO to show the Open PO’s of the vendor chosen in the document and I have assigned a FMS for this which is working fine.

Query:

Select DocNum from OPOR where DocStatus = 'O'

If I choose any one of that Open PO’s, details of Old PO’s such as Item Code, Open Qty and Unit Price should get appear in the new PO. I have created a FMS Query for this. That FMS is also working fine.

My issue is when the old PO two line items, having a same item with different unit price, I am not able to call the second line item’s unit price.

Eg., My old PO details:

Item Qty Price

Line 1: A 10 12

Line 1: A 10 13

The two line items are having different Prices for a same item since it has different delivery dates and contracts price.

Query:

For Itemcode:

-

---

SELECT T1.ItemCode

FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.DocStatus = 'O' AND T1.LineStatus = 'O' AND T0.DocNum = $[OPOR.U_AMENDMENT]

For Quantity:

-

---

SELECT T1.Quantity

FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.DocStatus = 'O' AND T1.LineStatus = 'O' AND T1.ITEMCODE = $[$38.1] and T0.DocNum = $[OPOR.U_AMENDMENT]

For Unit Price:

-

---

SELECT T1.pRICE

FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.DocStatus = 'O' AND T1.LineStatus = 'O' AND T1.ITEMCODE= $[$38.1] and T0.DocNum =$[OPOR.U_AMENDMENT]

When I am calling the details of the above PO thru formatted search in the new PO, I am able the get the 1st line item exactly. But the second line item’s details are not retrieved properly. It is retrieving the details of 1st line item only. I don’t know why it is happening.

Please help me for a solution.

SAGAR

[former_member583013 · Accepted solution]

Sagar,

I perfectly understand your scenario now.

The problem is because the Item Number you are referencing is same for both lines. The only thing that can be a differentiator is the LineNum field on the POR1 table.

As shown below please try to add the LineNum in the where statement in all your 3 SQL statements.

SELECT T1.ItemCode

FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.DocStatus = 'O' AND T1.LineStatus = 'O' AND T0.DocNum = $\[OPOR.U_AMENDMENT]

AND T1.LineNum = $\[$38.0.0]

This should work

Best wishes

Suda

[Former Member · Accepted solution]

Hi Suda,

Thanks a ton!!!

I assigned point for u.

SAGAR