Hello Everybody! I’m looking for help with a custom BAQ. Working in Epicor Kinetic 2023.1.10 A. The data I’m trying to get: return Inventory of ALL purchased parts, include lot number, qty, which warehouse the part is in, and what its bin location is . I need to join PartBin to Part INSTEAD of PartWhse but I don’t know how to. Please Help! This is My Query Phrase:
select
[Part].[PartNum] as [Part_PartNum],
[PartBin].[OnhandQty] as [PartBin_OnhandQty],
[Part].[IUM] as [Part_IUM],
[PartLot].[LotNum] as [PartLot_LotNum],
[PartLot].[ExpirationDate] as [PartLot_ExpirationDate],
[PartWhse].[WarehouseCode] as [PartWhse_WarehouseCode],
[PartBin].[BinNum] as [PartBin_BinNum]
from Erp.Part as [Part]
left outer join Erp.PartBin as [PartBin] on
Part.Company = PartBin.Company
and Part.PartNum = PartBin.PartNum
left outer join Erp.PartWhse as [PartWhse] on
PartBin.Company = PartWhse.Company
**and PartBin.PartNum = PartWhse.PartNum**
** and PartBin.WarehouseCode = PartWhse.WarehouseCode**
left outer join Erp.PartLot as [PartLot] on
Part.Company = PartLot.Company
and Part.PartNum = PartLot.PartNum
where (Part.TypeCode = 'P') and ( PartLot.ExpirationDate is not null )
order by PartLot.ExpirationDate
I cannot get the PDF to print preview. I cannot seem to be able to view the report.
Error Details Expanded: 1 2/8/2024 true Program Ice.Services.Lib.RunTask when executing task 385078 raised an unexpected exception with the following message: RunTask: System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. at System.Data.SqlTypes.SqlDateTime.FromTimeSpan(TimeSpan value) at System.Data.SqlTypes.SqlDateTime.FromDateTime(DateTime value) at System.Data.SqlTypes.SqlDateTime…ctor(DateTime value) at System.Data.SqlClient.MetaType.FromDateTime(DateTime dateTime, Byte cb) at System.Data.SqlClient.TdsParser.WriteUnterminatedValue(Object value, MetaType type, Byte scale, Int32 actualLength, Int32 encodingByteSize, Int32 offset, TdsParserStateObject stateObj, Int32 paramSize, Boolean isDataFeed) at System.Data.SqlClient.TdsParser.TDSExecuteRPCAddParameter(TdsParserStateObject stateObj, SqlParameter param, MetaType mt, Byte options) at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource1 completion, Int32 startRpc, Int32 startParam) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Ice.Core.RptBase.DatabaseReportDataStorage.ExecuteCommand(SqlCommand command) in C:_releases\ICE\ICE4.2.300.10\Source\Server\Internal\Lib\TaskLib\RptBase\DatabaseReportDataStorage.cs:line 161 at Ice.Core.RptBase.SqlRowWriter.WriteRow(IRow row) in C:_releases\ICE\ICE4.2.300.10\Source\Server\Internal\Lib\TaskLib\RptBase\SqlRowWriter.cs:line 36 at Ice.Core.RptBase.DatabaseReportDataStorage.WriteRow(IDataSource dataSource, IRow row) in C:_releases\ICE\ICE4.2.300.10\Source\Server\Internal\Lib\TaskLib\RptBase\DatabaseReportDataStorage.cs:line 114 at Ice.Internal.XA.TableBuilders.BaqReportResultTableBuilder.BuildData(String languageId) in C:_releases\ICE\ICE4.2.300.0\Source\Server\Internal\XA\XABaqRpt\TableBuilders\BaqReportResultTableBuilder.cs:line 27 at Ice.Internal.XA.DataBuilders.DatabaseReportDataBuilder.BuildReportData() in C:_releases\ICE\ICE4.2.300.0\Source\Server\Internal\XA\XABaqRpt\DataBuilders\DatabaseReportDataBuilder.cs:line 99 at Ice.Internal.XA.DataBuilders.DatabaseReportDataBuilder.RunReport(ReportDatabaseConnectionHelper reportDatabaseConnectionHelper) in C:_releases\ICE\ICE4.2.300.0\Source\Server\Internal\XA\XABaqRpt\DataBuilders\DatabaseReportDataBuilder.cs:line 90 at Ice.Internal.XA.DataBuilders.DatabaseReportDataBuilder.RunReportWithErrorHandling(ReportDatabaseConnectionHelper reportDatabaseConnectionHelper) in C:_releases\ICE\ICE4.2.300.0\Source\Server\Internal\XA\XABaqRpt\DataBuilders\DatabaseReportDataBuilder.cs:line 56 at Ice.Internal.XA.DataBuilders.DatabaseReportDataBuilder.WriteData() in C:_releases\ICE\ICE4.2.300.0\Source\Server\Internal\XA\XABaqRpt\DataBuilders\DatabaseReportDataBuilder.cs:line 44 at Ice.Internal.XA.BAQReport.DetermineOutputLocationAndWriteTables(String outputFileName, DataSet baqResultData) in C:_releases\ICE\ICE4.2.300.0\Source\Server\Internal\XA\XABaqRpt\BAQReport.cs:line 102 at Ice.Internal.XA.BAQReport.WriteTables(String outputFileName, DataSet baqResultData) in C:_releases\ICE\ICE4.2.300.0\Source\Server\Internal\XA\XABaqRpt\BAQReport.cs:line 95 at Ice.Internal.XA.BAQReport.RunProcess(Int64 instanceTaskNum, String outputFileName) in C:_releases\ICE\ICE4.2.300.0\Source\Server\Internal\XA\XABaqRpt\BAQReport.cs:line 44 at Ice.Core.TaskBase`1.StartProcess(Int64 instanceTaskNum, String outputFileName) in C:_releases\ICE\ICE4.2.300.10\Source\Server\Internal\Lib\TaskLib\TaskBase\TaskBase.cs:line 81 at Ice.Hosting.TaskCaller.InnerExecuteTask(IceDataContext newContext) in C:_releases\ICE\ICE4.2.300.10\Source\Server\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 68 at Ice.Hosting.TaskCaller.ExecuteTask() in C:_releases\ICE\ICE4.2.300.10\Source\Server\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 59 at Ice.Hosting.TaskCaller.ExecuteTask(IceDataContext dataContext, Boolean suppressTransaction) in C:_releases\ICE\ICE4.2.300.10\Source\Server\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 39 at Ice.Services.Lib.RunTaskSvc.<>c__DisplayClass30_2.b__1() in C:_releases\ICE\ICE4.2.300.0\Source\Server\Services\Lib\RunTask\RunTask.cs:line 453 at Ice.Lib.RunTask.BpmFriendlyTaskLauncher.Run(String sessionIdPrefix, IceContext db, Action taskRunner) in C:_releases\ICE\ICE4.2.300.0\Source\Server\Services\Lib\RunTask\BpmFriendlyTaskLauncher.cs:line 57 at Ice.Services.Lib.RunTaskSvc.InnerRunTask(Int64 ipTaskNum, Boolean suppressTransaction) in C:_releases\ICE\ICE4.2.300.0\Source\Server\Services\Lib\RunTask\RunTask.cs:line 450 ServerError
This error seems to point to a date time field. I see records in your Analyze tab. I also see the expiration date is a combined date/time field. Maybe just casting this into a simple date would work. I can’t say I know for sure, but I would start there.
