QTY Pulled Calculation

SUGGESTED

I am giving my best attempt at scripting a calculation of what we have on hand compared to the original ordered quantity and populating a UDF.  I know I have some holes as to the logic of the process and my end goal but I think I can figure those out if I can get this script to populate the UDF.  I piggy backed off of this script, using-im_itemwarehouse_bus-or-_svc, because it is almost the same of what I am trying to do except different table.  My knowledge of "looping" is limited and I receive an error stating below to start off.  Any help in direction?

'on pretotals of the SO_HEADER table
'calculates the quantity on hand - quantity on sales order to populate qty pulled

rVal = 0
sItem = ""
sItemType = ""
oItem = 0
QOH = 0 'QTY ON HAND
QOS = 0 'QTY ON SALES ORDER
QORD = 0 'QTY ORDERED
QAVAIL = 0
QPULLED = 0
sWHS = ""

Set oSOLines = oBusObj.AsObject(oBusObj.Lines)

lineupdated = 0

oSOLines.MoveFirst
do until (oSOLines.Eof and lineupdated = 1)

rVal = oSOLines.GetValue("ItemType$", sItemType)
set oItemWhse = oSession.As.Object(oSession.GetObject("IM_ItemWarehouse_svc"))

if trim(sItemType) = "1" then 'START- only run for inventory items

rVal = oSOLines.GetValue("ItemCode$", sItem)
rVal = oSOLines.GetValue("WareouseCode$", sWHS)
rVal = oSOLines.GetValue("QuantityOrdered$", QORD)

'rVal = oItemWhse.SetKeyValue("ItemCode$", sItem)
'rVal = oItemWhse.SetKeyValue("WarehouseCode$", sWHS)

rVal = oItemWhse.Find()

	if rVal = 1 then

	QOH = 0
	QOS = 0

	rVal = oItemWhse.GetValue("QuantityOnHand", QOH)
	rVal = oItemWhse.GetValue("QuantityOnSalesOrder", QOS)

	end if

		QAVAIL = QOH - QOS	

		if QORD > QAVAIL then

		QPULLED = QAVAIL

		rVal = oSOLines.SetValue("UDF_QTY_PULLED", QPULLED)

		rVal =oSOLines.Write()

		lineUpated = 1			

			if QORD <= QAVAIL then

			QPULLED = QORD

			rVal = oSOLines.SetValue("UDF_QTY_PULLED", QPULLED)
				
			rVal =oSOLines.Write()

			lineUpated = 1			

			end if
		
		end if

end if

rVal = oSOLines.MoveNext()

loop

Parents Reply
  • 0 in reply to Kevin M

    Long explanation, short version is we want to add up two udf’s to equal the total line count before printing the pick tickets.  These udf’s, in my thinking allow us to “allocate” the actual items to the sales order before processing the order.  We are doing this right now through excel, access, and VI.  I’m trying to do it all within Sage.

Children
  • 0 in reply to jland47

    I'd do all that in a custom report.  Quantities go stale quickly, and with a script like this you'd have to edit each order to re-trigger the calculations.

    If editing an order (instead of working on a new order), the IMIW QtyOnSO will include that order's amount...

    If working with a back order, the quantity left on order is in a different column...

    To pre-allocate items to pick, I'd consider creating unposted invoices and work from them.

  • 0 in reply to Kevin M

    If your items were Lot valuation, you'd be able to enable this option to pre-allocate quantities at the SO stage.

  • 0 in reply to jland47

    You do know that in your setting you can set it so you can allocate the lot or serial number during Sales Order Entry which allocates the items to the sales orders before invoicing.

  • 0 in reply to BigLouie

    We were trying to get away from Lot valuations because we are moving away from the IIG matrix enhancement and majority of our products will "Average Cost".

  • 0 in reply to jland47

    Use one lot, and that will effectively be average cost, with pre-allocations being allowed using Sage standard features.

  • 0 in reply to Kevin M

    Let me know if I am over thinking this, I created some test items with the same lot for all items and distributed, in order to only process/pick orders with fully distributed lines does that not require scripting, I don't see a way to filter the pick tickets to print without a script.  I would still need to add up each line qty distributed and compare to the total unit count for the lines to set a flag correct?

  • 0 in reply to jland47

    A script to check if the lines have been fully distributed is easier than managing the quantity allocations yourself.  SO detail has a property to check... without the extra IMIW lookup, and you avoid all issues around editing orders vs new / backorders / multiple SO for the same item...

  • 0 in reply to Kevin M

    So in theory I could script to look at the "lotserialfullydistributed" field for value "Y" if yes then continue to the next line, it doesn't matter if the first line is not distributed and the second is because I can end if "N", correct is this looping or just a oSOLines.MoveNext() expression?  Two questions as I am thinking of scenarios, what would be the expression to say if the last line of the SO is "Y" then check off UDF to print or do I need to still create more UDF's.  If an item is a miscellaneous item (charge or item), found answer default value is "Y" so that covers those lines.

    Just did a receipt of goods entry to test, once the quantity becomes available it doesn't automatically distribute the qty to the SO lines, so how is this updated?  I would rather leave off the option to "Check for Available Quantity" if that was a solution

  • 0 in reply to Kevin M

    I've encountered this. I have made a similar script but, after said in done trying to retrigger a change on the order to refresh those amount isn't worth the effort. A custom report or allowing SAGE to allocate for you is the better option.

  • 0 in reply to Bareus16

    Still thinking on solutions, custom reports have been mentioned twice now, which I'm am struggling to see the way that would help us or just don't understand the method required.  I've ran some test with unposted invoices and it takes away the flexibility to change sales orders after entering them (we have customers constantly changing sizes after the order is entered).  Our Sage consultant has recommended staying away from the lot serial options because of added overhead and complications?  We do have a call with Scanforce next week to see if they have a solution that could help, Scanco presented us with a solution, but not sure if we are ready to dive into the whole barcoding process yet.