InventUpdateOnhand.sqlUpdateInventSumStr Method [AX 2012]
Builds a string with the SQL statement listed under remarks.
Syntax
protected str sqlUpdateInventSumStr()
Run On
Called
Return Value
Type: str
A string with the SQL statement to update.
Examples
The SQL statement is dynamically generated and depends on the contents of field groups and enabled configuration keys.
The statement will be similar to this: execute sp_executesql N'UPDATE INVENTSUM SET INVENTSUM.POSTEDQTY = INVENTSUM.POSTEDQTY + INVENTSUMDELTA.SUM_POSTEDQTY, INVENTSUM.POSTEDVALUE = INVENTSUM.POSTEDVALUE + INVENTSUMDELTA.SUM_POSTEDVALUE, INVENTSUM.DEDUCTED = INVENTSUM.DEDUCTED + INVENTSUMDELTA.SUM_DEDUCTED, INVENTSUM.RECEIVED = INVENTSUM.RECEIVED + INVENTSUMDELTA.SUM_RECEIVED, INVENTSUM.RESERVORDERED = INVENTSUM.RESERVORDERED + INVENTSUMDELTA.SUM_RESERVORDERED, INVENTSUM.RESERVPHYSICAL = INVENTSUM.RESERVPHYSICAL + INVENTSUMDELTA.SUM_RESERVPHYSICAL, INVENTSUM.ONORDER = INVENTSUM.ONORDER + INVENTSUMDELTA.SUM_ONORDER, INVENTSUM.ORDERED = INVENTSUM.ORDERED + INVENTSUMDELTA.SUM_ORDERED, INVENTSUM.QUOTATIONISSUE = INVENTSUM.QUOTATIONISSUE + INVENTSUMDELTA.SUM_QUOTATIONISSUE, INVENTSUM.QUOTATIONRECEIPT = INVENTSUM.QUOTATIONRECEIPT + INVENTSUMDELTA.SUM_QUOTATIONRECEIPT, INVENTSUM.REGISTERED = INVENTSUM.REGISTERED + INVENTSUMDELTA.SUM_REGISTERED, INVENTSUM.PICKED = INVENTSUM.PICKED + INVENTSUMDELTA.SUM_PICKED, INVENTSUM.AVAILORDERED = INVENTSUM.AVAILORDERED + INVENTSUMDELTA.SUM_AVAILORDERED, INVENTSUM.AVAILPHYSICAL = INVENTSUM.AVAILPHYSICAL + INVENTSUMDELTA.SUM_AVAILPHYSICAL, INVENTSUM.PHYSICALVALUE = INVENTSUM.PHYSICALVALUE + INVENTSUMDELTA.SUM_PHYSICALVALUE, INVENTSUM.ARRIVED = INVENTSUM.ARRIVED + INVENTSUMDELTA.SUM_ARRIVED, INVENTSUM.PHYSICALINVENT = INVENTSUM.PHYSICALINVENT + INVENTSUMDELTA.SUM_PHYSICALINVENT, INVENTSUM.LASTUPDDATEPHYSICAL = CASE WHEN INVENTSUM.LASTUPDDATEPHYSICAL > INVENTSUMDELTA.MAX_LASTUPDDATEPHYSICAL THEN INVENTSUM.LASTUPDDATEPHYSICAL ELSE INVENTSUMDELTA.MAX_LASTUPDDATEPHYSICAL END , INVENTSUM.LASTUPDDATEEXPECTED = CASE WHEN INVENTSUM.LASTUPDDATEEXPECTED > INVENTSUMDELTA.MAX_LASTUPDDATEEXPECTED THEN INVENTSUM.LASTUPDDATEEXPECTED ELSE INVENTSUMDELTA.MAX_LASTUPDDATEEXPECTED END , INVENTSUM.CLOSEDQTY = CASE WHEN ((INVENTSUM.POSTEDQTY + INVENTSUMDELTA.SUM_POSTEDQTY) = 0 AND (INVENTSUM.DEDUCTED + INVENTSUMDELTA.SUM_DEDUCTED) = 0 AND (INVENTSUM.PICKED + INVENTSUMDELTA.SUM_PICKED) = 0 AND (INVENTSUM.RECEIVED + INVENTSUMDELTA.SUM_RECEIVED) = 0 AND (INVENTSUM.REGISTERED + INVENTSUMDELTA.SUM_REGISTERED) = 0 AND (INVENTSUM.RESERVPHYSICAL + INVENTSUMDELTA.SUM_RESERVPHYSICAL) = 0 AND (INVENTSUM.RESERVORDERED + INVENTSUMDELTA.SUM_RESERVORDERED) = 0 AND (INVENTSUM.ONORDER + INVENTSUMDELTA.SUM_ONORDER) = 0 AND (INVENTSUM.ORDERED + INVENTSUMDELTA.SUM_ORDERED) = 0 AND (INVENTSUM.ARRIVED + INVENTSUMDELTA.SUM_ARRIVED) = 0 AND (INVENTSUM.QUOTATIONISSUE + INVENTSUMDELTA.SUM_QUOTATIONISSUE) = 0 AND (INVENTSUM.QUOTATIONRECEIPT + INVENTSUMDELTA.SUM_QUOTATIONRECEIPT) = 0) THEN 1 ELSE 0 END, INVENTSUM.CLOSED = CASE WHEN ((INVENTSUM.POSTEDQTY + INVENTSUMDELTA.SUM_POSTEDQTY) = 0 AND (INVENTSUM.DEDUCTED + INVENTSUMDELTA.SUM_DEDUCTED) = 0 AND (INVENTSUM.PICKED + INVENTSUMDELTA.SUM_PICKED) = 0 AND (INVENTSUM.RECEIVED + INVENTSUMDELTA.SUM_RECEIVED) = 0 AND (INVENTSUM.REGISTERED + INVENTSUMDELTA.SUM_REGISTERED) = 0 AND (INVENTSUM.RESERVPHYSICAL + INVENTSUMDELTA.SUM_RESERVPHYSICAL) = 0 AND (INVENTSUM.RESERVORDERED + INVENTSUMDELTA.SUM_RESERVORDERED) = 0 AND (INVENTSUM.ONORDER + INVENTSUMDELTA.SUM_ONORDER) = 0 AND (INVENTSUM.ORDERED + INVENTSUMDELTA.SUM_ORDERED) = 0 AND (INVENTSUM.ARRIVED + INVENTSUMDELTA.SUM_ARRIVED) = 0 AND (INVENTSUM.QUOTATIONISSUE + INVENTSUMDELTA.SUM_QUOTATIONISSUE) = 0 AND (INVENTSUM.QUOTATIONRECEIPT + INVENTSUMDELTA.SUM_QUOTATIONRECEIPT) = 0 AND (INVENTSUM.POSTEDVALUE + INVENTSUMDELTA.SUM_POSTEDVALUE) = 0 AND (INVENTSUM.PHYSICALVALUE + INVENTSUMDELTA.SUM_PHYSICALVALUE) = 0) THEN 1 ELSE 0 END FROM (SELECT SUM(POSTEDQTY) AS SUM_POSTEDQTY, SUM(POSTEDVALUE) AS SUM_POSTEDVALUE, SUM(DEDUCTED) AS SUM_DEDUCTED, SUM(RECEIVED) AS SUM_RECEIVED, SUM(RESERVORDERED) AS SUM_RESERVORDERED, SUM(RESERVPHYSICAL) AS SUM_RESERVPHYSICAL, SUM(ONORDER) AS SUM_ONORDER, SUM(ORDERED) AS SUM_ORDERED, SUM(QUOTATIONISSUE) AS SUM_QUOTATIONISSUE, SUM(QUOTATIONRECEIPT) AS SUM_QUOTATIONRECEIPT, SUM(REGISTERED) AS SUM_REGISTERED, SUM(PICKED) AS SUM_PICKED, SUM(AVAILORDERED) AS SUM_AVAILORDERED, SUM(AVAILPHYSICAL) AS SUM_AVAILPHYSICAL, SUM(PHYSICALVALUE) AS SUM_PHYSICALVALUE, SUM(ARRIVED) AS SUM_ARRIVED, SUM(PHYSICALINVENT) AS SUM_PHYSICALINVENT, MAX(LASTUPDDATEPHYSICAL) AS MAX_LASTUPDDATEPHYSICAL, MAX(LASTUPDDATEEXPECTED) AS MAX_LASTUPDDATEEXPECTED, INVENTSUMDELTA.ITEMID, INVENTSUMDELTA.INVENTDIMID FROM INVENTSUMDELTA WHERE INVENTSUMDELTA.DATAAREAID = @p2 AND INVENTSUMDELTA.TTSID = @p3 AND INVENTSUMDELTA.ISAGGREGATED = 0 GROUP BY INVENTSUMDELTA.ITEMID, INVENTSUMDELTA.INVENTDIMID ) AS INVENTSUMDELTA WHERE INVENTSUM.DATAAREAID = @p1 AND INVENTSUM.ITEMID = INVENTSUMDELTA.ITEMID AND INVENTSUM.INVENTDIMID = INVENTSUMDELTA.INVENTDIMID ',N'@p1 NVARCHAR(4), @p2 NVARCHAR(4), @p3 BIGINT',@p1=%1, @p2=%2, @p3='%3'