Overview
When you are trying to export inventory from Everest you face the following issues:
- Exporting to the Excel 97-2003 format is successful, but the inventory is incomplete.
- Exporting to the Excel 2007+ format yields a document that cannot be opened and the following error appears:
Error Number 2006
Error Message Out Of Memory
Error Source BaseBrowser
Error Method BaseBrowser alaExportExecute
This behavior is caused by a high number of records being retrieved which triggers the application to timeout and corrupt the Excel file. Everest is configured to timeout any query that takes over 1 minute to complete.
Exporting to the Excel 97-2003 format works because it is limited to 65,536 records. This enables the query to finish before it timeouts as it only pulls data up to the Excel 97-2003 record limit.
Solution
The timeout limit cannot be changed in the application. However, the same result can be achieved by exporting the data directly from the database as a CSV file:
- Open the SQL Server Management Studio
- Right-click on your company database and choose New query
-
Run the following query:
Note: you can also find the query in the attached SQL file.SET TRANSACTION ISOLATION LEVEL Read UNCOMMITTED; SELECT TOP 20 ITEMS.ITEMNO ITEMNO, ITEMS.DESCRIPT DESCRIPT, ITEMS.ACTIVE, CAST(CASE WHEN DIVISION.FF_ACTIVE = 'T' AND (ITEMS.FF_ACTIVE = 'T' ) THEN (ITEMS.COST *(100 + ISNULL(FF,0))/100) ELSE ITEMS.COST END AS FLOAT) AS COST, BRAND, CATEGORY, MAX_STOCK, IRD.MIN_QUANTITY MIN_NEEDED, MODEL, QTY_STK, CAST(ISNULL(QTY_STK, 0) AS FLOAT) TOTAL_STOCK, CAST(ISNULL(Q_ON_CREDI, 0) AS FLOAT) Q_ON_CREDI, CAST(ISNULL(Q_ON_ORDER, 0) AS FLOAT) Q_ON_ORDER, CAST(ISNULL(Q_ON_CREDIT_COMM, 0) AS FLOAT) Q_ON_CREDIT_COMM, CAST(ISNULL(Q_ON_ORDER_COMM, 0) AS FLOAT) Q_ON_ORDER_COMM, CAST(ISNULL(Q_ON_RESERVE_COMM, 0) AS FLOAT) Q_ON_RESERVE_COMM, CAST(ISNULL(Q_ON_RMA_COMM, 0) AS FLOAT) Q_ON_RMA_COMM, CAST(ISNULL(Q_ON_RESER, 0) AS FLOAT) Q_ON_RESER, CAST(ISNULL(Q_ON_RMA, 0) AS FLOAT) Q_ON_RMA, SALE_MEAS, CAST(IMS1.SELLPRIC AS FLOAT) SELLPRIC, MATRIX_ITEM_TYPE, CASE WHEN MATRIX_ITEM_TYPE = 1 THEN CASE WHEN ITEMS.ACTIVE = 'T' THEN 0 ELSE 24 END WHEN MATRIX_ITEM_TYPE = 3 THEN 1 ELSE CASE WHEN MATRIX_ITEM_TYPE = 0 AND INVENTORED = 'F' THEN CASE WHEN ITEMS.ACTIVE = 'T' THEN 2 ELSE 13 END WHEN MATRIX_ITEM_TYPE = 0 AND AUTOSERIAL = 'T' AND ITEMS.IS_GIFT_CARD = 'T' THEN CASE WHEN ITEMS.ACTIVE = 'T' THEN 25 ELSE 14 END WHEN MATRIX_ITEM_TYPE = 0 AND AUTOSERIAL = 'T' THEN CASE WHEN ITEMS.ACTIVE = 'T' THEN 3 ELSE 14 END WHEN MATRIX_ITEM_TYPE = 0 AND SERIALIZE = 'T' THEN CASE WHEN ITEMS.ACTIVE = 'T' THEN 4 ELSE 15 END WHEN MATRIX_ITEM_TYPE = 0 AND INVENTORED = 'T' THEN CASE WHEN ITEMS.ACTIVE = 'T' THEN 5 ELSE 16 END WHEN MATRIX_ITEM_TYPE = 2 AND INVENTORED = 'F' THEN CASE WHEN ITEMS.ACTIVE = 'T' THEN 6 ELSE 17 END WHEN MATRIX_ITEM_TYPE = 2 AND AUTOSERIAL = 'T' THEN CASE WHEN ITEMS.ACTIVE = 'T' THEN 7 ELSE 18 END WHEN MATRIX_ITEM_TYPE = 2 AND SERIALIZE = 'T' THEN CASE WHEN ITEMS.ACTIVE = 'T' THEN 8 ELSE 19 END WHEN MATRIX_ITEM_TYPE = 2 AND INVENTORED = 'T' THEN CASE WHEN ITEMS.ACTIVE = 'T' THEN 9 ELSE 20 END WHEN MATRIX_ITEM_TYPE = 4 AND AUTOSERIAL = 'T' THEN CASE WHEN ITEMS.ACTIVE = 'T' THEN 10 ELSE 21 END WHEN MATRIX_ITEM_TYPE = 4 AND SERIALIZE = 'T' THEN CASE WHEN ITEMS.ACTIVE = 'T' THEN 11 ELSE 22 END WHEN MATRIX_ITEM_TYPE = 4 AND INVENTORED = 'T' THEN CASE WHEN ITEMS.ACTIVE = 'T' THEN 12 ELSE 23 END END END AS TYPE , M.ENTRY_TYPE, ISNULL(M.DEC_ACCURACY, 0) AS PURC_FRAC, CAST(CASE WHEN AVG_COST >0 AND DIVISION.FF_ACTIVE = 'T' AND (ITEMS.FF_ACTIVE = 'T' ) THEN (AVG_COST * (100 + ISNULL(FF,0))/100) ELSE AVG_COST END AS FLOAT) AS AVG_COST, CAST(CASE WHEN M.ENTRY_TYPE = 2 THEN DBO.CONVERT_MEASURE_QTY((DBO.CONVERT_MEASURE_QTY(QTY_STK, ISNULL(MF.CONV_FACTOR, 1), 1, ISNULL(MF1.CONV_FACTOR, 1), 1) - DBO.CONVERT_MEASURE_QTY(Q_ON_RESER, ISNULL(MF.CONV_FACTOR, 1), 1, ISNULL(MF1.CONV_FACTOR, 1), 1) - DBO.CONVERT_MEASURE_QTY(Q_ON_RMA, ISNULL(MF.CONV_FACTOR, 1), 1, ISNULL(MF1.CONV_FACTOR, 1), 1)),1, ISNULL(MF.CONV_FACTOR, 1), CAST(1 AS DECIMAL)/ISNULL(MF1.CONV_FACTOR, 1), 0) ELSE (ISNULL(QTY_STK,0) - ISNULL(Q_ON_RESER,0) - ISNULL(Q_ON_RMA,0)) END AS FLOAT) AVAILABLE, SERIALIZE, (CASE WHEN MS.ENTRY_TYPE = 2 THEN MFS.CONV_FACTOR ELSE 1 END) SALEFRACTION, IMS1.MARKUP, PURC_MEAS, MANUCODE, ITEMS.FF_ACTIVE, FF, INVENTORED, AUTOSERIAL, (CASE WHEN M.ENTRY_TYPE = 2 THEN MF.CONV_FACTOR ELSE 1 END) FRACTION, (ISNULL(CAST(CASE WHEN IMS1.MARKUP=0 THEN 0 WHEN IMS1.MARKUP >0 AND FLD_MARKUP = 1 THEN CASE WHEN DIVISION.FF_ACTIVE = 'T' AND (ITEMS.FF_ACTIVE = 'T' ) THEN CASE WHEN (IE.COST IS NULL) OR (IE.COST = 0) THEN (((ITEMS.COST *(100+ISNULL(FF,0))/100) * (100 + ISNULL(IMS1.MARKUP,0))/100)/ISNULL(MEFS.CONV_FACTOR,1)) ELSE ((IE.COST * (100 + ISNULL(FF,0))/100) * (100 + ISNULL(IMS1.MARKUP,0))/100) END ELSE CASE WHEN (IE.COST IS NULL) OR (IE.COST = 0) THEN ((ITEMS.COST * (100 + ISNULL(IMS1.MARKUP,0))/100)/ISNULL(MEFS.CONV_FACTOR,1)) ELSE (IE.COST * (100 + ISNULL(IMS1.MARKUP,0))/100) END END WHEN IMS1.MARKUP >0 THEN CASE WHEN FLD_MARKUP = 2 AND DIVISION.FF_ACTIVE = 'T' AND (ITEMS.FF_ACTIVE = 'T' ) THEN (((AVG_COST * (100 +ISNULL(FF,0))/100) * (100 + ISNULL(IMS1.MARKUP,0)) /100) /ISNULL(MEFS.CONV_FACTOR,1)) ELSE ((AVG_COST * (100 + ISNULL(IMS1.MARKUP,0))/100) / ISNULL(MEFS.CONV_FACTOR,1)) END END AS FLOAT),0)) AS MPRICE , STOCK_MEAS, CAST(CASE WHEN DIVISION.FF_ACTIVE = 'T' AND (ITEMS.FF_ACTIVE = 'T' ) THEN (ITEMS.LAST_COST *(100 + ISNULL(FF,0))/100) ELSE ITEMS.LAST_COST END AS FLOAT) LAST_COST, CAST(CASE WHEN DIVISION.FF_ACTIVE = 'T' AND (ITEMS.FF_ACTIVE = 'T' ) THEN ((ITEMS.COST *(100 + ISNULL(FF,0))/100) - (ITEMS.LAST_COST *(100 + ISNULL(FF,0))/100)) ELSE (ISNULL(ITEMS.COST,0) - ISNULL(ITEMS.LAST_COST,0)) END AS FLOAT) LASTADDONCOST, ITEMS.IS_GIFT_CARD , IC.DESCRIPT CLASS_DESCRIPT, ITEMS.USAGE_QUANTITY, ITEMS.USAGE_TYPE, CASE DIVISION.SALE_PERIOD WHEN 0 THEN '' ELSE CAST (DIVISION.SALE_PERIOD AS VARCHAR) + SPACE(1) + CASE DIVISION.SALE_PERIOD_TYPE WHEN 1 THEN 'WEEK' WHEN 2 THEN 'MONTH' WHEN 3 THEN 'QUARTER' ELSE 'YEAR' END END USAGE_SALE_TYPE, ITEMS.CUSTCHAR1, ITEMS.CUSTCHAR2, ITEMS.CUSTCHAR3, ITEMS.CUSTCHAR4, ITEMS.CUSTDATE1, ITEMS.CUSTDATE2, ITEMS.CUSTDATE3, ITEMS.CUSTDATE4, ITEMS.CUSTLOG1, ITEMS.CUSTLOG2, ITEMS.CUSTLOG3, ITEMS.CUSTLOG4, ITEMS.CUSTMEMO1, ITEMS.CUSTMEMO2, ITEMS.CUSTMEMO3, ITEMS.CUSTMEMO4, ITEMS.CUSTNUM1, ITEMS.CUSTNUM2, ITEMS.CUSTNUM3, ITEMS.CUSTNUM4, CAST(DBO.GET_STOCK_FORBROWSER(ITEMS.ITEMNO,'HDBCULVERCITY','TOTAL_STOCK',M.ENTRY_TYPE, ISNULL(MF.CONV_FACTOR, 1), ISNULL(MF1.CONV_FACTOR, 1),ISNULL(MFS.CONV_FACTOR, 1), ISNULL(MEFS.CONV_FACTOR, 1), ISNULL(MFP.CONV_FACTOR, 1), ISNULL(MEFP.CONV_FACTOR, 1), ITEMS.INVENTORED, ITEMS.QTY_STK, ITEMS.Q_ON_ORDER, ITEMS.Q_ON_ORDER_COMM, ITEMS.Q_ON_RESER, ITEMS.Q_ON_RESERVE_COMM, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RMA_COMM, ITEMS.Q_ON_CREDI, ITEMS.Q_ON_CREDIT_COMM ) AS FLOAT) AS [HDBCULVERCITY_TOTAL_STOCK], CAST(DBO.GET_STOCK_FORBROWSER(ITEMS.ITEMNO,'HDBCULVERCITY','AVAILABLE',M.ENTRY_TYPE, ISNULL(MF.CONV_FACTOR, 1), ISNULL(MF1.CONV_FACTOR, 1),ISNULL(MFS.CONV_FACTOR, 1), ISNULL(MEFS.CONV_FACTOR, 1), ISNULL(MFP.CONV_FACTOR, 1), ISNULL(MEFP.CONV_FACTOR, 1), ITEMS.INVENTORED, ITEMS.QTY_STK, ITEMS.Q_ON_ORDER, ITEMS.Q_ON_ORDER_COMM, ITEMS.Q_ON_RESER, ITEMS.Q_ON_RESERVE_COMM, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RMA_COMM, ITEMS.Q_ON_CREDI, ITEMS.Q_ON_CREDIT_COMM ) AS FLOAT) AS [HDBCULVERCITY_AVAILABLE], CAST(DBO.GET_QSTK1STK2_FORBROWSER(ITEMS.ITEMNO,'HDBCULVERCITY','QTY_STK + Q_ON_ORDER','','1',M.ENTRY_TYPE, ISNULL(MF.CONV_FACTOR, 1), ISNULL(MF1.CONV_FACTOR, 1),0,0,ITEMS.INVENTORED, ITEMS.QTY_STK, ITEMS.Q_ON_ORDER, ITEMS.Q_ON_ORDER_COMM, ITEMS.Q_ON_RESER, ITEMS.Q_ON_RESERVE_COMM, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RMA_COMM, ITEMS.Q_ON_CREDI, ITEMS.Q_ON_CREDIT_COMM ) AS FLOAT) AS [HDBCULVERCITY_STK1], CAST(DBO.GET_QSTK1STK2_FORBROWSER(ITEMS.ITEMNO,'HDBCULVERCITY','QTY_STK - Q_ON_RESERVE_COMM - Q_ON_RMA_COMM','','1',M.ENTRY_TYPE, ISNULL(MF.CONV_FACTOR, 1), ISNULL(MF1.CONV_FACTOR, 1),0,0, ITEMS.INVENTORED, ITEMS.QTY_STK, ITEMS.Q_ON_ORDER, ITEMS.Q_ON_ORDER_COMM, ITEMS.Q_ON_RESER, ITEMS.Q_ON_RESERVE_COMM, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RMA_COMM, ITEMS.Q_ON_CREDI, ITEMS.Q_ON_CREDIT_COMM ) AS FLOAT) AS [HDBCULVERCITY_STK2], CAST(DBO.GET_STOCK_FORBROWSER(ITEMS.ITEMNO,'HDBCULVERCITY','Q_ON_RESER',M.ENTRY_TYPE, ISNULL(MF.CONV_FACTOR, 1), ISNULL(MF1.CONV_FACTOR, 1),ISNULL(MFS.CONV_FACTOR, 1), ISNULL(MEFS.CONV_FACTOR, 1), ISNULL(MFP.CONV_FACTOR, 1), ISNULL(MEFP.CONV_FACTOR, 1), ITEMS.INVENTORED, ITEMS.QTY_STK, ITEMS.Q_ON_ORDER, ITEMS.Q_ON_ORDER_COMM, ITEMS.Q_ON_RESER, ITEMS.Q_ON_RESERVE_COMM, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RMA_COMM, ITEMS.Q_ON_CREDI, ITEMS.Q_ON_CREDIT_COMM ) AS FLOAT) AS [HDBCULVERCITY_Q_ON_RESER], CAST(DBO.GET_STOCK_FORBROWSER(ITEMS.ITEMNO,'HDBCULVERCITY','Q_ON_RESERVE_COMM',M.ENTRY_TYPE, ISNULL(MF.CONV_FACTOR, 1), ISNULL(MF1.CONV_FACTOR, 1),ISNULL(MFS.CONV_FACTOR, 1), ISNULL(MEFS.CONV_FACTOR, 1), ISNULL(MFP.CONV_FACTOR, 1), ISNULL(MEFP.CONV_FACTOR, 1), ITEMS.INVENTORED, ITEMS.QTY_STK, ITEMS.Q_ON_ORDER, ITEMS.Q_ON_ORDER_COMM, ITEMS.Q_ON_RESER, ITEMS.Q_ON_RESERVE_COMM, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RMA_COMM, ITEMS.Q_ON_CREDI, ITEMS.Q_ON_CREDIT_COMM ) AS FLOAT) AS [HDBCULVERCITY_Q_ON_RESERVE_COMM], CAST(DBO.GET_STOCK_FORBROWSER(ITEMS.ITEMNO,'HDBCULVERCITY','Q_ON_ORDER',M.ENTRY_TYPE, ISNULL(MF.CONV_FACTOR, 1), ISNULL(MF1.CONV_FACTOR, 1),ISNULL(MFS.CONV_FACTOR, 1), ISNULL(MEFS.CONV_FACTOR, 1), ISNULL(MFP.CONV_FACTOR, 1), ISNULL(MEFP.CONV_FACTOR, 1), ITEMS.INVENTORED, ITEMS.QTY_STK, ITEMS.Q_ON_ORDER, ITEMS.Q_ON_ORDER_COMM, ITEMS.Q_ON_RESER, ITEMS.Q_ON_RESERVE_COMM, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RMA_COMM, ITEMS.Q_ON_CREDI, ITEMS.Q_ON_CREDIT_COMM ) AS FLOAT) AS [HDBCULVERCITY_Q_ON_ORDER], CAST(DBO.GET_STOCK_FORBROWSER(ITEMS.ITEMNO,'HDBCULVERCITY','Q_ON_ORDER_COMM',M.ENTRY_TYPE, ISNULL(MF.CONV_FACTOR, 1), ISNULL(MF1.CONV_FACTOR, 1),ISNULL(MFS.CONV_FACTOR, 1), ISNULL(MEFS.CONV_FACTOR, 1), ISNULL(MFP.CONV_FACTOR, 1), ISNULL(MEFP.CONV_FACTOR, 1), ITEMS.INVENTORED, ITEMS.QTY_STK, ITEMS.Q_ON_ORDER, ITEMS.Q_ON_ORDER_COMM, ITEMS.Q_ON_RESER, ITEMS.Q_ON_RESERVE_COMM, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RMA_COMM, ITEMS.Q_ON_CREDI, ITEMS.Q_ON_CREDIT_COMM ) AS FLOAT) AS [HDBCULVERCITY_Q_ON_ORDER_COMM], CAST(DBO.GET_STOCK_FORBROWSER(ITEMS.ITEMNO,'HDBCULVERCITY','Q_ON_CREDI',M.ENTRY_TYPE, ISNULL(MF.CONV_FACTOR, 1), ISNULL(MF1.CONV_FACTOR, 1),ISNULL(MFS.CONV_FACTOR, 1), ISNULL(MEFS.CONV_FACTOR, 1), ISNULL(MFP.CONV_FACTOR, 1), ISNULL(MEFP.CONV_FACTOR, 1), ITEMS.INVENTORED, ITEMS.QTY_STK, ITEMS.Q_ON_ORDER, ITEMS.Q_ON_ORDER_COMM, ITEMS.Q_ON_RESER, ITEMS.Q_ON_RESERVE_COMM, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RMA_COMM, ITEMS.Q_ON_CREDI, ITEMS.Q_ON_CREDIT_COMM ) AS FLOAT) AS [HDBCULVERCITY_Q_ON_CREDI], CAST(DBO.GET_STOCK_FORBROWSER(ITEMS.ITEMNO,'HDBCULVERCITY','Q_ON_CREDIT_COMM',M.ENTRY_TYPE, ISNULL(MF.CONV_FACTOR, 1), ISNULL(MF1.CONV_FACTOR, 1),ISNULL(MFS.CONV_FACTOR, 1), ISNULL(MEFS.CONV_FACTOR, 1), ISNULL(MFP.CONV_FACTOR, 1), ISNULL(MEFP.CONV_FACTOR, 1), ITEMS.INVENTORED, ITEMS.QTY_STK, ITEMS.Q_ON_ORDER, ITEMS.Q_ON_ORDER_COMM, ITEMS.Q_ON_RESER, ITEMS.Q_ON_RESERVE_COMM, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RMA_COMM, ITEMS.Q_ON_CREDI, ITEMS.Q_ON_CREDIT_COMM ) AS FLOAT) AS [HDBCULVERCITY_Q_ON_CREDIT_COMM], CAST(DBO.GET_STOCK_FORBROWSER(ITEMS.ITEMNO,'HDBCULVERCITY','Q_ON_RMA',M.ENTRY_TYPE, ISNULL(MF.CONV_FACTOR, 1), ISNULL(MF1.CONV_FACTOR, 1),ISNULL(MFS.CONV_FACTOR, 1), ISNULL(MEFS.CONV_FACTOR, 1), ISNULL(MFP.CONV_FACTOR, 1), ISNULL(MEFP.CONV_FACTOR, 1), ITEMS.INVENTORED, ITEMS.QTY_STK, ITEMS.Q_ON_ORDER, ITEMS.Q_ON_ORDER_COMM, ITEMS.Q_ON_RESER, ITEMS.Q_ON_RESERVE_COMM, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RMA_COMM, ITEMS.Q_ON_CREDI, ITEMS.Q_ON_CREDIT_COMM ) AS FLOAT) AS [HDBCULVERCITY_Q_ON_RMA], CAST(DBO.GET_STOCK_FORBROWSER(ITEMS.ITEMNO,'HDBCULVERCITY','Q_ON_RMA_COMM',M.ENTRY_TYPE, ISNULL(MF.CONV_FACTOR, 1), ISNULL(MF1.CONV_FACTOR, 1),ISNULL(MFS.CONV_FACTOR, 1), ISNULL(MEFS.CONV_FACTOR, 1), ISNULL(MFP.CONV_FACTOR, 1), ISNULL(MEFP.CONV_FACTOR, 1), ITEMS.INVENTORED, ITEMS.QTY_STK, ITEMS.Q_ON_ORDER, ITEMS.Q_ON_ORDER_COMM, ITEMS.Q_ON_RESER, ITEMS.Q_ON_RESERVE_COMM, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RMA_COMM, ITEMS.Q_ON_CREDI, ITEMS.Q_ON_CREDIT_COMM ) AS FLOAT) AS [HDBCULVERCITY_Q_ON_RMA_COMM], CAST(DBO.GET_STOCK_FORBROWSER(ITEMS.ITEMNO,'MAIN','TOTAL_STOCK',M.ENTRY_TYPE, ISNULL(MF.CONV_FACTOR, 1), ISNULL(MF1.CONV_FACTOR, 1),ISNULL(MFS.CONV_FACTOR, 1), ISNULL(MEFS.CONV_FACTOR, 1), ISNULL(MFP.CONV_FACTOR, 1), ISNULL(MEFP.CONV_FACTOR, 1), ITEMS.INVENTORED, ITEMS.QTY_STK, ITEMS.Q_ON_ORDER, ITEMS.Q_ON_ORDER_COMM, ITEMS.Q_ON_RESER, ITEMS.Q_ON_RESERVE_COMM, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RMA_COMM, ITEMS.Q_ON_CREDI, ITEMS.Q_ON_CREDIT_COMM ) AS FLOAT) AS [MAIN_TOTAL_STOCK], CAST(DBO.GET_STOCK_FORBROWSER(ITEMS.ITEMNO,'MAIN','AVAILABLE',M.ENTRY_TYPE, ISNULL(MF.CONV_FACTOR, 1), ISNULL(MF1.CONV_FACTOR, 1),ISNULL(MFS.CONV_FACTOR, 1), ISNULL(MEFS.CONV_FACTOR, 1), ISNULL(MFP.CONV_FACTOR, 1), ISNULL(MEFP.CONV_FACTOR, 1), ITEMS.INVENTORED, ITEMS.QTY_STK, ITEMS.Q_ON_ORDER, ITEMS.Q_ON_ORDER_COMM, ITEMS.Q_ON_RESER, ITEMS.Q_ON_RESERVE_COMM, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RMA_COMM, ITEMS.Q_ON_CREDI, ITEMS.Q_ON_CREDIT_COMM ) AS FLOAT) AS [MAIN_AVAILABLE], CAST(DBO.GET_QSTK1STK2_FORBROWSER(ITEMS.ITEMNO,'MAIN','QTY_STK + Q_ON_ORDER','','1',M.ENTRY_TYPE, ISNULL(MF.CONV_FACTOR, 1), ISNULL(MF1.CONV_FACTOR, 1),0,0,ITEMS.INVENTORED, ITEMS.QTY_STK, ITEMS.Q_ON_ORDER, ITEMS.Q_ON_ORDER_COMM, ITEMS.Q_ON_RESER, ITEMS.Q_ON_RESERVE_COMM, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RMA_COMM, ITEMS.Q_ON_CREDI, ITEMS.Q_ON_CREDIT_COMM ) AS FLOAT) AS [MAIN_STK1], CAST(DBO.GET_QSTK1STK2_FORBROWSER(ITEMS.ITEMNO,'MAIN','QTY_STK - Q_ON_RESERVE_COMM - Q_ON_RMA_COMM','','1',M.ENTRY_TYPE, ISNULL(MF.CONV_FACTOR, 1), ISNULL(MF1.CONV_FACTOR, 1),0,0, ITEMS.INVENTORED, ITEMS.QTY_STK, ITEMS.Q_ON_ORDER, ITEMS.Q_ON_ORDER_COMM, ITEMS.Q_ON_RESER, ITEMS.Q_ON_RESERVE_COMM, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RMA_COMM, ITEMS.Q_ON_CREDI, ITEMS.Q_ON_CREDIT_COMM ) AS FLOAT) AS [MAIN_STK2], CAST(DBO.GET_STOCK_FORBROWSER(ITEMS.ITEMNO,'MAIN','Q_ON_RESER',M.ENTRY_TYPE, ISNULL(MF.CONV_FACTOR, 1), ISNULL(MF1.CONV_FACTOR, 1),ISNULL(MFS.CONV_FACTOR, 1), ISNULL(MEFS.CONV_FACTOR, 1), ISNULL(MFP.CONV_FACTOR, 1), ISNULL(MEFP.CONV_FACTOR, 1), ITEMS.INVENTORED, ITEMS.QTY_STK, ITEMS.Q_ON_ORDER, ITEMS.Q_ON_ORDER_COMM, ITEMS.Q_ON_RESER, ITEMS.Q_ON_RESERVE_COMM, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RMA_COMM, ITEMS.Q_ON_CREDI, ITEMS.Q_ON_CREDIT_COMM ) AS FLOAT) AS [MAIN_Q_ON_RESER], CAST(DBO.GET_STOCK_FORBROWSER(ITEMS.ITEMNO,'MAIN','Q_ON_RESERVE_COMM',M.ENTRY_TYPE, ISNULL(MF.CONV_FACTOR, 1), ISNULL(MF1.CONV_FACTOR, 1),ISNULL(MFS.CONV_FACTOR, 1), ISNULL(MEFS.CONV_FACTOR, 1), ISNULL(MFP.CONV_FACTOR, 1), ISNULL(MEFP.CONV_FACTOR, 1), ITEMS.INVENTORED, ITEMS.QTY_STK, ITEMS.Q_ON_ORDER, ITEMS.Q_ON_ORDER_COMM, ITEMS.Q_ON_RESER, ITEMS.Q_ON_RESERVE_COMM, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RMA_COMM, ITEMS.Q_ON_CREDI, ITEMS.Q_ON_CREDIT_COMM ) AS FLOAT) AS [MAIN_Q_ON_RESERVE_COMM], CAST(DBO.GET_STOCK_FORBROWSER(ITEMS.ITEMNO,'MAIN','Q_ON_ORDER',M.ENTRY_TYPE, ISNULL(MF.CONV_FACTOR, 1), ISNULL(MF1.CONV_FACTOR, 1),ISNULL(MFS.CONV_FACTOR, 1), ISNULL(MEFS.CONV_FACTOR, 1), ISNULL(MFP.CONV_FACTOR, 1), ISNULL(MEFP.CONV_FACTOR, 1), ITEMS.INVENTORED, ITEMS.QTY_STK, ITEMS.Q_ON_ORDER, ITEMS.Q_ON_ORDER_COMM, ITEMS.Q_ON_RESER, ITEMS.Q_ON_RESERVE_COMM, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RMA_COMM, ITEMS.Q_ON_CREDI, ITEMS.Q_ON_CREDIT_COMM ) AS FLOAT) AS [MAIN_Q_ON_ORDER], CAST(DBO.GET_STOCK_FORBROWSER(ITEMS.ITEMNO,'MAIN','Q_ON_ORDER_COMM',M.ENTRY_TYPE, ISNULL(MF.CONV_FACTOR, 1), ISNULL(MF1.CONV_FACTOR, 1),ISNULL(MFS.CONV_FACTOR, 1), ISNULL(MEFS.CONV_FACTOR, 1), ISNULL(MFP.CONV_FACTOR, 1), ISNULL(MEFP.CONV_FACTOR, 1), ITEMS.INVENTORED, ITEMS.QTY_STK, ITEMS.Q_ON_ORDER, ITEMS.Q_ON_ORDER_COMM, ITEMS.Q_ON_RESER, ITEMS.Q_ON_RESERVE_COMM, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RMA_COMM, ITEMS.Q_ON_CREDI, ITEMS.Q_ON_CREDIT_COMM ) AS FLOAT) AS [MAIN_Q_ON_ORDER_COMM], CAST(DBO.GET_STOCK_FORBROWSER(ITEMS.ITEMNO,'MAIN','Q_ON_CREDI',M.ENTRY_TYPE, ISNULL(MF.CONV_FACTOR, 1), ISNULL(MF1.CONV_FACTOR, 1),ISNULL(MFS.CONV_FACTOR, 1), ISNULL(MEFS.CONV_FACTOR, 1), ISNULL(MFP.CONV_FACTOR, 1), ISNULL(MEFP.CONV_FACTOR, 1), ITEMS.INVENTORED, ITEMS.QTY_STK, ITEMS.Q_ON_ORDER, ITEMS.Q_ON_ORDER_COMM, ITEMS.Q_ON_RESER, ITEMS.Q_ON_RESERVE_COMM, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RMA_COMM, ITEMS.Q_ON_CREDI, ITEMS.Q_ON_CREDIT_COMM ) AS FLOAT) AS [MAIN_Q_ON_CREDI], CAST(DBO.GET_STOCK_FORBROWSER(ITEMS.ITEMNO,'MAIN','Q_ON_CREDIT_COMM',M.ENTRY_TYPE, ISNULL(MF.CONV_FACTOR, 1), ISNULL(MF1.CONV_FACTOR, 1),ISNULL(MFS.CONV_FACTOR, 1), ISNULL(MEFS.CONV_FACTOR, 1), ISNULL(MFP.CONV_FACTOR, 1), ISNULL(MEFP.CONV_FACTOR, 1), ITEMS.INVENTORED, ITEMS.QTY_STK, ITEMS.Q_ON_ORDER, ITEMS.Q_ON_ORDER_COMM, ITEMS.Q_ON_RESER, ITEMS.Q_ON_RESERVE_COMM, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RMA_COMM, ITEMS.Q_ON_CREDI, ITEMS.Q_ON_CREDIT_COMM ) AS FLOAT) AS [MAIN_Q_ON_CREDIT_COMM], CAST(DBO.GET_STOCK_FORBROWSER(ITEMS.ITEMNO,'MAIN','Q_ON_RMA',M.ENTRY_TYPE, ISNULL(MF.CONV_FACTOR, 1), ISNULL(MF1.CONV_FACTOR, 1),ISNULL(MFS.CONV_FACTOR, 1), ISNULL(MEFS.CONV_FACTOR, 1), ISNULL(MFP.CONV_FACTOR, 1), ISNULL(MEFP.CONV_FACTOR, 1), ITEMS.INVENTORED, ITEMS.QTY_STK, ITEMS.Q_ON_ORDER, ITEMS.Q_ON_ORDER_COMM, ITEMS.Q_ON_RESER, ITEMS.Q_ON_RESERVE_COMM, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RMA_COMM, ITEMS.Q_ON_CREDI, ITEMS.Q_ON_CREDIT_COMM ) AS FLOAT) AS [MAIN_Q_ON_RMA], CAST(DBO.GET_STOCK_FORBROWSER(ITEMS.ITEMNO,'MAIN','Q_ON_RMA_COMM',M.ENTRY_TYPE, ISNULL(MF.CONV_FACTOR, 1), ISNULL(MF1.CONV_FACTOR, 1),ISNULL(MFS.CONV_FACTOR, 1), ISNULL(MEFS.CONV_FACTOR, 1), ISNULL(MFP.CONV_FACTOR, 1), ISNULL(MEFP.CONV_FACTOR, 1), ITEMS.INVENTORED, ITEMS.QTY_STK, ITEMS.Q_ON_ORDER, ITEMS.Q_ON_ORDER_COMM, ITEMS.Q_ON_RESER, ITEMS.Q_ON_RESERVE_COMM, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RMA_COMM, ITEMS.Q_ON_CREDI, ITEMS.Q_ON_CREDIT_COMM ) AS FLOAT) AS [MAIN_Q_ON_RMA_COMM], ISNULL(MP.DEC_ACCURACY, 0) AS P_PURC_FRAC, ISNULL(MS.DEC_ACCURACY, 0) AS S_PURC_FRAC, SALEPRICE_DEC_ACCURACY, PURCPRICE_DEC_ACCURACY, CASE WHEN MP.ENTRY_TYPE = 2 THEN ISNULL(MFP.CONV_FACTOR, 1) ELSE 1 END AS PURCFRACTION FROM ITEMS WITH (NOLOCK) LEFT OUTER JOIN (MEASURE M WITH(NOLOCK) LEFT OUTER JOIN MEASURE_FACTOR MF WITH(NOLOCK) ON M.CODE = MF.MEAS_CODE AND M.SUB_MEAS = MF.CONV_MEAS_CODE) ON (STOCK_MEAS = M.CODE) LEFT OUTER JOIN (MEASURE MA WITH(NOLOCK) LEFT OUTER JOIN MEASURE_FACTOR MF1 WITH(NOLOCK) ON MA.CODE = MF1.MEAS_CODE AND MA.LEAST_SUB_MEAS = MF1.CONV_MEAS_CODE) ON (STOCK_MEAS = MA.CODE) LEFT OUTER JOIN (MEASURE MS WITH(NOLOCK) LEFT OUTER JOIN MEASURE_FACTOR MFS WITH(NOLOCK) ON MS.CODE = MFS.MEAS_CODE AND MS.SUB_MEAS = MFS.CONV_MEAS_CODE) ON (SALE_MEAS = MS.CODE) LEFT OUTER JOIN (MEASURE MP WITH(NOLOCK) LEFT OUTER JOIN MEASURE_FACTOR MFP WITH(NOLOCK) ON MP.CODE = MFP.MEAS_CODE AND MP.SUB_MEAS = MFP.CONV_MEAS_CODE) ON (PURC_MEAS = MP.CODE) LEFT JOIN POSTING_GROUP PG WITH(NOLOCK) ON ITEMS.PGID = PG.PGID LEFT OUTER JOIN ITEM_MEASURE IM1 WITH(NOLOCK) ON IM1.ITEMNO = ITEMS.ITEMNO AND IM1.MEAS_TYPE = 2 AND ITEMS.PURC_MEAS = IM1.MEAS_CODE LEFT OUTER JOIN ITEM_MEASURE IMS1 WITH(NOLOCK) ON IMS1.ITEMNO = ITEMS.ITEMNO AND IMS1.MEAS_TYPE = 1 AND ITEMS.SALE_MEAS = IMS1.MEAS_CODE LEFT OUTER JOIN ITEM_MEASURE IE WITH(NOLOCK) ON IE.MEAS_TYPE = 2 AND IE.ITEMNO = ITEMS.ITEMNO AND IE.MEAS_CODE = ITEMS.SALE_MEAS LEFT OUTER JOIN ITEM_MEASURE IE_STK WITH(NOLOCK) ON IE_STK.MEAS_TYPE = 2 AND IE_STK.ITEMNO = ITEMS.ITEMNO AND IE_STK.MEAS_CODE = ITEMS.STOCK_MEAS LEFT OUTER JOIN ITEM_MEASURE IEP WITH(NOLOCK) ON IEP.MEAS_TYPE = 1 AND IEP.ITEMNO = ITEMS.ITEMNO AND IEP.MEAS_CODE = ITEMS.PURC_MEAS LEFT OUTER JOIN MEASURE_FACTOR MEFS WITH(NOLOCK) ON MEFS.MEAS_CODE = ITEMS.STOCK_MEAS AND MEFS.CONV_MEAS_CODE = ITEMS.SALE_MEAS LEFT OUTER JOIN MEASURE_FACTOR MEFP WITH(NOLOCK) ON MEFP.MEAS_CODE = ITEMS.STOCK_MEAS AND MEFP.CONV_MEAS_CODE = ITEMS.PURC_MEAS LEFT OUTER JOIN DIVISION WITH (NOLOCK) ON DIVISION.ACTIVE = 'T' LEFT OUTER JOIN ITEM_REPLENISH_DEPART IRD WITH (NOLOCK) ON (IRD.ITEM_CODE = ITEMS.ITEMNO) AND (IRD.DEPART = 'MAIN') LEFT OUTER JOIN ITEM_REPLENISH_VENDOR IRV WITH (NOLOCK) ON IRV.ITEM_CODE = ITEMS.ITEMNO AND IRV.PRIMARY_VENDOR = 'T' LEFT OUTER JOIN ITEM_CLASSIFICATION IC WITH (NOLOCK) ON ITEMS.CLASSIFICATION_ID = IC.CLASSIFICATION_ID WHERE ITEMS.ACTIVE = 'T' ORDER BY ITEMS.DESCRIPT DESC SET TRANSACTION ISOLATION LEVEL Read COMMITTED;
Note that running this query will take a significant amount of time
- Right-click on the result list and choose Save Results As...
- In the dialog, choose CSV as the format, specify a name, and click Save.
Comments
0 comments
Please sign in to leave a comment.