Error #-2147217833 in PO

(1) Share

Report

Posted on by LaUnicaSupercenter

I'm getting this error when I trying to create a purchase order and/or transfer inventory out.

Store Operations Business Rules
    Error #-2147217833
    The statement has been terminated.
    (Source: Microsoft OLE DB Provider for SQL Server)
    (SQL State: 01000)
    (NativeError: 3621)
    No Help file available

    SELECT
    ItemID,
    SUM(CASE POType
      WHEN 0 THEN QuantityOrdered - QuantityReceivedToDate
      WHEN 1 THEN QuantityOrdered - QuantityReceivedToDate
      WHEN 2 THEN QuantityOrdered - QuantityReceivedToDate
      WHEN 3 THEN -QuantityOrdered + QuantityReceivedToDate
      WHEN 4 THEN QuantityOrdered - QuantityReceivedToDate
      WHEN 5 THEN -QuantityOrdered + QuantityReceivedToDate
    END) As OnOrder
    INTO ##TempQuantity
    FROM PurchaseOrderEntry
    INNER JOIN PurchaseOrder ON PurchaseOrder.ID PurchaseOrderEntry.PurchaseOrderID
    WHERE (PurchaseOrder.Status <> 2) AND (QuantityReceivedToDate <
QuantityOrdered) GROUP BY ItemID
    UPDATE ##TempOrder
    SET QuantityOrdered       CASE WHEN Item.Quantity - Item.QuantityCommitted <= Item.ReorderPoint
THEN
      ISNULL(Item.RestockLevel - (Item.Quantity - Item.QuantityCommitted +
ISNULL(##TempQuantity.OnOrder, 0)),

This is followed by an OK button.  When I click OK, I receive a second
error message:

    Store Operations Manager
    Run-time error '5':
    Invalid procedure call or argument

Also followed by an OK button, which when clicked, exits Store Operations
Manager.  This error is only occurring on one Department, and did not occur
on Monday when this Department was last ordered.  Does anyone know what
causes this error, and how can it be fixed?

*This post is locked for comments

All responses (8)

Answers (0)

Suggested answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: Error #-2147217833 in PO

thanks it worked
Craig Harris 795 on at

Like (0)

Report

RE: Error #-2147217833 in PO

I run the following select statements to find the "problem" value. Can add other fields to check.. reorderpoint etc

/* *********************************************** */

select * from VIEWONORDER

WHERE onorder > '10000' OR onorder < '-10000'

select * from VIEWONXFRIN

WHERE XFRIN > '10000' OR XFRIN < '-10000'

select * from VIEWONXFROUT

WHERE XFROUT > '10000' OR XFROUT < '-10000'

/* *********************************************** */

SELECT itemlookupcode, description, supplierid, quantity FROM Item

WHERE Quantity > '10000' OR Quantity < '-10000'

select ItemID, PurchaseOrderID, Itemdescription, lastupdated, Quantityreceivedtodate, quantityordered from purchaseorderentry where quantityordered > '10000' OR quantityordered < '-10000'

Order by QuantityOrdered DESC

select * from purchaseorderentry where quantityordered > '10000' OR quantityordered < '-10000'

Order by QuantityOrdered DESC

select * from purchaseorderentry where Quantityreceivedtodate > '10000' OR Quantityreceivedtodate < '-10000'

Order by QuantityOrdered DESC
Community Member Microsoft Employee on at

Like (0)

Report

RE: Error #-2147217833 in PO

so how can i fix it
Craig Harris 795 on at

Like (0)

Report

RE: Error #-2147217833 in PO

My 2 bits... I just had this error again. Turned out this time one of our employees had scanned a upc into a Restock level.

Cheers,

Craig
Suggested answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: Error #-2147217833 in PO

i have already solved this issue. my error was same but related to Inventory Valuation Report for which when checked at back end in ItemValueLog Table, i came to find out that OldAmount feild had a value which was out of the range which was initially causing problem. so for that i ran a query to modify the record instead of deleting the particular transaction. the query is as follows which would help others if they ever face the same issue or even with quantity issues related to same error this query can be used changing few things here and there.

update [Sample].[dbo].[ItemValueLog]

set OldAmount= 3.46

where ItemID= 3107

Cheerz

Ashish
Suggested answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: Error #-2147217833 in PO

You need to check the quantity fields in each item. The best way to do that is open SO Admin and connect to database. Then run a query

SELECT * FROM Item WHERE Quantity > 10000 OR WHERE Quantity < -10000

This was supplied in Archelle Marcito response above.

This could happen with any report that has item quantity or any numeric integer field. Sine the number is larger than what the reporting engine can handle.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Error #-2147217833 in PO

same error faced by me but while generating Inventory valuation report. the affected area is transactions. once i delete all transactions the report starts to work but unfortunately transactions are important for reporting purpose therefore needs to be solved somehow using query at SQL back end.
Suggested answer

archelle16 1,735 on at

Like (0)

Report
RE: Error #-2147217833 in PO

Maybe this will help you:

This problem can be caused by item having an overly large positive or negative inventory quantity and the new Details view in the Transfer and Purchase Order with FP1 when you try to query the details for the items linked to a supplier, but cannot because while processing the query the quantity is greater than the field size. The overly large quantity can normally be caused by scanning the barcode into the item's quantity field.

To resolve this issue, use the script below as a starting point to find the trouble item. Then update the quantity of the trouble item to a smaller inventory quantity.

SELECT * FROM Item WHERE Quantity > 10000 OR WHERE Quantity < -10000

Source: http://support.microsoft.com/kb/2447570