search

a2000-erp-team / WEBERP

WEBERP
0 stars 0 forks source link

Stock Count #103

Closed jessa21 closed 5 years ago

jessa21 commented 5 years ago

image.png

ZawZawThein commented 5 years ago

Pages: Areas/Inventory/A21IMStockCounting/Content.cshtml Areas/Inventory/A21IMStockCounting/Index.cshtml Areas/Inventory/A21IMStockCounting/Script.cshtml ` Controller: Areas\Inventory\Controllers\A21IMStockCountingController.cs

ZawZawThein commented 5 years ago

For Report:

A2000ERP\A2000ERP\Controllers\PreviewReportController.cs

Repx file:

  1. StockCountWorkSheet.repx
  2. IMReCount1sheet.repx
  3. IMReCount2sheet.repx 4.R21IMStockVariance.repx

Dataset: Report_Dataset\Inventory\R21IMStockCountSheet.xsd

Dataset: Report_Dataset\Inventory\R21IMStockVariance.xsd

ZawZawThein commented 5 years ago

Alter Procedure R21IMStockVariance @StockDate datetime, @Warehouse varchar(50) AS
BEGIN

SELECT DISTINCT MSTProduct.Product, isnull(MSTProduct.PName,'''') AS Name, MSTProduct.StockUnit, MSTProduct.Class1, MSTProduct.Brand, R21IMStockVarianceActQuantity.Quantity AS Quantity, A21IMWarehouseStock.Warehouse, A21IMWarehouseStock.Count, A21IMWarehouseStock.Recount1, A21IMWarehouseStock.Recount2, A21IMWarehouseStock.FlagRecount1, A21IMWarehouseStock.FlagRecount2, --IIf([FlagRecount2],[Recount2],IIf([FlagRecount1],[Recount1],[Count])) AS QuantityCounted, R21IMStockVarianceActQuantity.StockValue --IIf([Quantity]=0,0,R21IMStockVarianceActQuantity.StockValue/[Quantity]) AS AvgUnitPrice

FROM MSTProduct INNER JOIN A21IMWarehouseStock ON MSTProduct.Product = A21IMWarehouseStock.Product AND MSTProduct.Company = A21IMWarehouseStock.Company LEFT JOIN dbo.R21IMStockVarianceActQuantity( @StockDate, @Warehouse) as R21IMStockVarianceActQuantity

ON A21IMWarehouseStock.Product = R21IMStockVarianceActQuantity.Product

WHERE (((A21IMWarehouseStock.Warehouse)='B2B') AND ((MSTProduct.Type)='I'))

End

ZawZawThein commented 5 years ago

CREATE function [dbo].[R21IMStockVarianceActQuantity ] ( @StockDate datetime, @Warehouse varchar(50) ) returns table return

SELECT DISTINCT A21IMTransactionDetail.Product, A21IMTransactionDetail.Warehouse, dbo.RDX(Sum([ReceiptQuantity]-[IssueQuantity]),4) AS Quantity, dbo.RDX((IIf([ReceiptQuantity] Is Null,0,[ReceiptQuantity])IIf([Rate] Is Null,0,[Rate]) -(IIf([IssueQuantity] Is Null,0, [IssueQuantity])IIf([Cost] Is Null,0,[Cost]))) *IIf([ExchangeRate] Is Null,1,[ExchangeRate]),4) AS StockValue

FROM (A21IMTransactionDetail INNER JOIN A21GLTransactionHeader ON (A21IMTransactionDetail.TrnNo = A21GLTransactionHeader.TrnNo) AND (A21IMTransactionDetail.TrnType = A21GLTransactionHeader.TrnType) AND (A21IMTransactionDetail.Company = A21GLTransactionHeader.Company)) INNER JOIN A21IMWarehouseStock ON (A21IMTransactionDetail.Company = A21IMWarehouseStock.Company) AND (A21IMTransactionDetail.Product = A21IMWarehouseStock.Product)

AND (A21IMTransactionDetail.Warehouse = A21IMWarehouseStock.Warehouse) WHERE (((A21GLTransactionHeader.TrnDate)<= @StockDate) AND ((A21IMTransactionDetail.Warehouse)= @Warehouse))

GROUP BY A21IMTransactionDetail.Product, A21IMTransactionDetail.Warehouse, ReceiptQuantity,Rate,IssueQuantity,Cost,ExchangeRate

zeusa2000 commented 5 years ago

Hi @ZawZawThein can i close this issue now? as per checking the page is currently active as of the moment. Kindly find attached file for your reference. Stock Count Test- Working(WebErp)

zeusa2000 commented 5 years ago

Issue moved to a2000-erp-team/Job-Order-PMS #18 via ZenHub