DynDeveloper.com

	SOP Scripts
	eConnect
	taSopDeleteDocumentWrapper
	taSopLineDeleteWrapper
	taSOPLineIvcInsert Wrapper
	taSopLineIvcInsertComponent
	taSOPLotAuto - complete procedure
	Functions
	ddf_Address
	ddf_AllHoldsOnASOPDoc
	General
	Getting Email addresses from Dynamics GP
	Move a SOP transaction from open to history
	Posting Sequences
	SOP40200 'Allow' fields query
	sopGetIDNumber
	sopTrxDeleteWork
	Unvoid a SOP document
	Stored Procedures
	dd_SOP10102_UPD
	dd_SOPGetNextDocNumber/ sopGetIDNumber.
	dd_SOPPayment_INS
	dd_SOPRound - Round a SOP Multicurrency document to two decimal places
	dd_SOPTotal
	dd_SOPTotalTaxes
	dd_taSopVoidDocumentWrapper

GetNextNumber

Module: Bank Reconciliation

Category: Setup

Description:

Gets the next number based on the specified Transaction Type. This routing returns a formatted string CCCNNNNNNNNN where CCC is the Transaction Type code and NNNNNNNNN is the transaction number.

If two users call GetNextNumber with two different transaction types, the return values might be (assume 5 is the current Next Number), RCT000000005, and the second request would return IAJ000000006. The Transaction Number is independent of the Transaction Type.

Script Type: Function

Syntax:

GetNextNumber(TransactionType, CMSetup, CMTrxSetup)

Parameters:

in TransactionType Enumerated Transaction Type (Receipt, Withdrawal, etc)

in CMSetup CM Setup Table

in CMTrxSetup CM Transaction Setup Table

a Sample of my dexterity calling the function from t SDK....

local integer zintGetNextNumber;

open table CM_SETP;

zintGetNextNumber= GetNextNumber(6, 'CM Setup Table', CMTrxSetup)of form CM_Setup;

Why am I getting a message...Unknonwn Identifier CM Setup Table and CMTrxSetup?

Why am I getting a type incompatibility script zintGetNextNumberparameter #2

Maybe the better question is how do I pass those tables to the script if those tables aren't part of the form I am calling my script from?

SQL Query on GL Accounts with MDA codes

Hi there,

Does anyone have a SQL query to find out which accounts have an MDA code?

Thanks

Paul

GP and Papersave

I want to create a SQL report for auditors showing the GP transactions with valid Papersave documents attached.

What tables do I need?

Has anyone ever done this so I don't have to recreate the wheel?

Thank you in advance,

How to calculate the accumulated depreciation of an asset to date

Hi!

I would like to know if there is any way to calculate the accumulated depreciation of a fixed asset. I explain: I need to generate a report, which must be generated historically, so that if it is generated with a date prior to the current one, it allows me to recalculate the depreciation of an asset to the indicated date.

Example:

I have an acquisition date that indicates when I obtained the asset and an acquisition cost of the amount for which I obtained it.

Then if I go to the asset book screen, I can select a book ID (1), and based on this it brings me the information I want. As you can see, it establishes a date until which depreciation is going to apply (2), and then it tells me the accumulated depreciation up to that date (3). I am interested in knowing if there is any way that I can do this, a procedure or something.

Thanks!!!

Serial No. already exists for item when posting purchase batch

I am trying to post a purchasing batch and get the error Serial No. already exists for item

What would cause this and how does one resolve it?

Thank you,

Will

eConnect Error 11482 - Cancel Amount cannot be greater then quantity remaining

Summary

When trying to update a purchase order via an integration eConnect sometimes throws error '11482' (Cancel amount cannot be greater than quantity remaining). After doing some research I have discovered that this error is thrown when a POP10110 (PO Line) record has a negative 'QTYUNCMTBASE' value. Below I go into detail about why this error is thrown but I need help determining how best to avoid it. Does anyone have more information about how this error interacts with negative 'QTYUNCMTBASE' values?

Why is the Error Being Thrown?

The error is thrown from the below SQL block and uses a number of different parameters.

IF (@I_vQTYCANCE * @cUMQTYINB) > (@cQTYUNCMTBASE + (@cQTYCANCE * @cUMQTYINB))
BEGIN
SELECT @O_iErrorState = 11482

EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT
END

The parameters that start with '@I_v' are given by the user/application. Ones starting with '@c' are taken directly from the database.

@I_vQTYCANCE	The value passed to eConnect for the quantity canceled
@cUMQTYINB	The current database value for the quantity in base unit of measure
@cQTYUNCMTBASE	The current database value for the uncommitted quantity in base unit of measure
@cQTYCANCE	The current database value of the canceled quantity

For the initial analysis I focused on a single purchase order. For that particular order the first line item was throwing the error. I boiled down the above if statement to determine why this was true.

IF (@I_vQTYCANCE * @cUMQTYINB) > (@cQTYUNCMTBASE + (@cQTYCANCE * @cUMQTYINB))

I knew the given value of the '@I_vQTYCANCE' was '0'. So the statement then became…

IF (0 * @cUMQTYINB) > (@cQTYUNCMTBASE + (@cQTYCANCE * @cUMQTYINB))
IF (0) > (@cQTYUNCMTBASE + (@cQTYCANCE * @cUMQTYINB))

I also knew that the current database value for the '@cQTYCANCE' was '0'. So the statement progressed…

IF (0) > (@cQTYUNCMTBASE + (0 * @cUMQTYINB))
IF (0) > (@cQTYUNCMTBASE + (0))

Which ultimately became…

IF (0) > (@cQTYUNCMTBASE)

Looking at this it's easy to see what's happening. The error will be thrown if the '@cQTYUNCMTBASE' parameter has a negative value. The '@cQTYUNCMTBASE' parameter is simply the current value of the 'QTYUNCMTBASE' on the 'POP10110' record at the time the eConnect stored procedure is run. So, I looked at the problem 'POP10110' record to determine if the 'QTYUNCMTBASE' value was indeed negative and confirmed that it was.

So we know the error is being thrown because the 'QTYUNCMTBASE' value is negative... but why is it negative? Is that an accepted value?

How Did the 'QTYUNCMTBASE' Value Become Negative?

I looked at one of the orders throwing the eConnect error and found that the line item in question did indeed have a negative 'QTYUNCMTBASE' value. The 'QTYUNCMTBASE' column was set to '-9175' for the first line of the 'P0474258' order. But why -9175?

When editing a PO eConnect sets the 'QTYUNCMTBASE' value using the following logic.

QTYUNCMTBASE = CASE
WHEN @I_vPOLNESTA = 1
THEN ((@I_vQUANTITY - @I_vQTYCANCE) * @UMQTYINB)
ELSE ((@I_vQUANTITY - @I_vQTYCANCE) * @UMQTYINB) - @cQTYSHPPD1
END

I know the 'I_vPOLNESTA' of the line is not '1' in this case (it pretty much never is in my case) so I looked at the 'ELSE' block.

ELSE ((@I_vQUANTITY - @I_vQTYCANCE) * @UMQTYINB) - @cQTYSHPPD1

The first parameters are easy to identify.

@I_vQUANTITY	The quantity ordered value passed to eConnect
@I_vQTYCANCE	The quantity canceled value passed to eConnect (usually 0)
@UMQTYINB	The quantity in base unit of measure (usually 1)

The last one is a calculated field that appears further up in the stored procedure and sums together all the 'QTYSHPPD' values on associated 'POP10500' records.

@cQTYSHPPD1

The sum of all the 'QTYSHPPD' values for the receipts of the current line

SELECT @cQTYSHPPD1 = isnull(sum(QTYSHPPD * UMQTYINB), 0)
FROM POP10500(NOLOCK)
WHERE PONUMBER = @I_vPONUMBER
AND POLNENUM = @I_vORD
AND STATUS = 1

Now that we know where these values are coming from we can evaluate the ELSE block that is responsible for setting the 'QTYUNCMTBASE' value on the line. In this case it boils down to...

ELSE ((@I_vQUANTITY - @I_vQTYCANCE) * @UMQTYINB) - @cQTYSHPPD1

I know that the '@I_vQTYCANCE' is '0'...

ELSE ((@I_vQUANTITY - 0) * @UMQTYINB) - @cQTYSHPPD1

ELSE ((@I_vQUANTITY) * @UMQTYINB) - @cQTYSHPPD1

I know that the '@UMQTYINB' is '1'...

ELSE ((@I_vQUANTITY) * 1) - @cQTYSHPPD1

So then we have...

ELSE (@I_vQUANTITY - @cQTYSHPPD1)

And in the case of the first line of PO 'P0474258' the values ended up being.

ELSE (75,000 - 84,175)

Which, when you do the math, equals -9175... the value we see after updating the order. This means eConnect is setting the value of the lines 'QTYUNCMTBASE' to a negative value when updating an order.

Is It Okay for This Value to Be Negative?

Unfortunately, I don't know. I don't yet understand why the sum of the 'QTYSHPPD' values of associated 'POP10500' records is larger than the quantity on the PO line. Does this mean that a user has "over-received" product for a line? Something else?

Potential Fixes

When using the Dynamics GP purchasing reconcile utility on these orders the tool will revert the 'QTYUNCMTBASE' to '0' if it's negative. This will prevent the issue from occurring but eConnect will set the 'QTYUNCMTBASE' to a negative value again after a successful update (see above) if the sum of the 'QTYSHPPD' values for the receipts on the line are greater than the quantity ordered. So this tool would need to be run after anyone updates a line with these specific conditions.

We could update the 'taPoLinePost' procedure to correct these negative 'QTYUNCMTBASE' values after updating an order but I still don't know enough about the data to feel extremely comfortable doing that.

Conclusion

There appears to be some purchase orders where the sum of all the received quantities is more than the quantity listed on the purchase order line. When editing an order like this via eConnect, the 'QTYUNCMTBASE' is set to a negative number. When the order is then edited again eConnect is using the 'QTYUNCMTBASE' in the if statement that determines if error 11482 should be thrown. The if statement becomes 'true' because 'QTYUNCMTBASE' has a negative value and the stored procedure cannot complete successfully.

There does not appear to be anything the user can do to avoid this issue once you get a negative 'QTYUNCMTBASE' value.

Does anyone know more about this situation or have any idea how to avoid this eConnect error?

Archives

GetNextNumber