I would really like to know where a particular report pulls it data from in terms of Erp tables. It’s the Advanced Payment Report.
The dll is Erp.Contracts.Rpt.AdvPayBal.dll
Should I be looking in the server assemblies folder, or client folder when looking using ILSpy. Assuming that it will be this dll that contains the code to pull the data, or am I looking in the wrong place?
The RDD (Report Data Definition) will get you close. It doesn’t explicitly specify the tables - some of the data is in views. But you can usually figure out the source table and field.
It would get tricky for some calculated fields, like the email address field that the OrderAck RDD calculates. It can pull from many places, and has logic to determine which address to use.
Run you report and look in System Monitor to determine the exact name of the report. You will need to look at the underlying RDD as well as the query inside the RDL. As Calvin says, it can get a little complicated if fields are calculated and aliases are created. Sometimes, it is not at all obvious (especially since Epicor can use multiple fields for monetary amounts in the same table).
You don’t need to look inside any DLLs, there are a few custom functions that make use of a DLL but not the data itself.
It’s the Advanced Payment report for AP. The RDD is giving literally no clues, so was after input from an experienced hacker such as @Chris_Conn or @josecgomez.
RDD shows tables as being Company, PODetail, UOM, Vendor, ReportSelCriteria.
Considering the way that items get onto this report is by putting on an AP Invoice, and using Line Type = A (Advanced Billing) then it’s strange that no AP tables are listed in the RDD. Which leads me to think that the EpiMagic is happening within the dll used to built the dataset for SSRS - hidden behind a magic curtain.
You can browse through Erp.Internal.AP.AdvPayBalReport.dll to get some more answers but it looks at APInvHed, APInvDtl, Vendor, and PODetail to list a few
where invDtlRow.get_Company() == company_ex && invHedRow.get_Posted() && invDtlRow.get_LineType() == "A" && ((selectBy_ex == "Date" && (DateTime)invHedRow.get_InvoiceDate() <= (DateTime)asOfDate_ex) || (selectBy_ex == "ApplyDate" && (DateTime)invHedRow.get_ApplyDate() <= (DateTime)asOfDate_ex))If you run SQL Profiler and filter on the DB you can run the report and find they actual SQL that is generated from the EF code.
exec sp_executesql N'SELECT
[Project2].[Length_c] AS [Length_c],
[Project2].[Company] AS [Company],
[Project2].[OpenLine] AS [OpenLine],
[Project2].[VoidLine] AS [VoidLine],
[Project2].[PONUM] AS [PONUM],
[Project2].[POLine] AS [POLine],
[Project2].[LineDesc] AS [LineDesc],
[Project2].[IUM] AS [IUM],
[Project2].[UnitCost] AS [UnitCost],
[Project2].[DocUnitCost] AS [DocUnitCost],
[Project2].[OrderQty] AS [OrderQty],
[Project2].[XOrderQty] AS [XOrderQty],
[Project2].[Taxable] AS [Taxable],
[Project2].[PUM] AS [PUM],
[Project2].[CostPerCode] AS [CostPerCode],
[Project2].[PartNum] AS [PartNum],
[Project2].[VenPartNum] AS [VenPartNum],
[Project2].[CommentText] AS [CommentText],
[Project2].[ClassID] AS [ClassID],
[Project2].[RevisionNum] AS [RevisionNum],
[Project2].[RcvInspectionReq] AS [RcvInspectionReq],
[Project2].[VendorNum] AS [VendorNum],
[Project2].[AdvancePayBal] AS [AdvancePayBal],
[Project2].[DocAdvancePayBal] AS [DocAdvancePayBal],
[Project2].[Confirmed] AS [Confirmed],
[Project2].[DateChgReq] AS [DateChgReq],
[Project2].[QtyChgReq] AS [QtyChgReq],
[Project2].[PartNumChgReq] AS [PartNumChgReq],
[Project2].[RevisionNumChgReq] AS [RevisionNumChgReq],
[Project2].[ConfirmDate] AS [ConfirmDate],
[Project2].[ConfirmVia] AS [ConfirmVia],
[Project2].[PrcChgReq] AS [PrcChgReq],
[Project2].[PurchCode] AS [PurchCode],
[Project2].[OrderNum] AS [OrderNum],
[Project2].[OrderLine] AS [OrderLine],
[Project2].[Linked] AS [Linked],
[Project2].[ExtCompany] AS [ExtCompany],
[Project2].[GlbCompany] AS [GlbCompany],
[Project2].[ContractActive] AS [ContractActive],
[Project2].[ContractQty] AS [ContractQty],
[Project2].[ContractUnitCost] AS [ContractUnitCost],
[Project2].[ContractDocUnitCost] AS [ContractDocUnitCost],
[Project2].[Rpt1AdvancePayBal] AS [Rpt1AdvancePayBal],
[Project2].[Rpt2AdvancePayBal] AS [Rpt2AdvancePayBal],
[Project2].[Rpt3AdvancePayBal] AS [Rpt3AdvancePayBal],
[Project2].[Rpt1UnitCost] AS [Rpt1UnitCost],
[Project2].[Rpt2UnitCost] AS [Rpt2UnitCost],
[Project2].[Rpt3UnitCost] AS [Rpt3UnitCost],
[Project2].[ContractQtyUOM] AS [ContractQtyUOM],
[Project2].[Rpt1ContractUnitCost] AS [Rpt1ContractUnitCost],
[Project2].[Rpt2ContractUnitCost] AS [Rpt2ContractUnitCost],
[Project2].[Rpt3ContractUnitCost] AS [Rpt3ContractUnitCost],
[Project2].[BaseQty] AS [BaseQty],
[Project2].[BaseUOM] AS [BaseUOM],
[Project2].[BTOOrderNum] AS [BTOOrderNum],
[Project2].[BTOOrderLine] AS [BTOOrderLine],
[Project2].[VendorPartOpts] AS [VendorPartOpts],
[Project2].[MfgPartOpts] AS [MfgPartOpts],
[Project2].[SubPartOpts] AS [SubPartOpts],
[Project2].[MfgNum] AS [MfgNum],
[Project2].[MfgPartNum] AS [MfgPartNum],
[Project2].[SubPartNum] AS [SubPartNum],
[Project2].[SubPartType] AS [SubPartType],
[Project2].[ConfigUnitCost] AS [ConfigUnitCost],
[Project2].[ConfigBaseUnitCost] AS [ConfigBaseUnitCost],
[Project2].[ConvOverRide] AS [ConvOverRide],
[Project2].[BasePartNum] AS [BasePartNum],
[Project2].[BaseRevisionNum] AS [BaseRevisionNum],
[Project2].[Direction] AS [Direction],
[Project2].[Per] AS [Per],
[Project2].[MaintainPricingUnits] AS [MaintainPricingUnits],
[Project2].[OverrideConversion] AS [OverrideConversion],
[Project2].[RowsManualFactor] AS [RowsManualFactor],
[Project2].[KeepRowsManualFactorTmp] AS [KeepRowsManualFactorTmp],
[Project2].[ShipToSupplierDate] AS [ShipToSupplierDate],
[Project2].[Factor] AS [Factor],
[Project2].[PricingQty] AS [PricingQty],
[Project2].[PricingUnitPrice] AS [PricingUnitPrice],
[Project2].[UOM] AS [UOM],
[Project2].[SysRevID] AS [SysRevID],
[Project2].[ForeignSysRowID] AS [ForeignSysRowID],
[Project2].[GroupSeq] AS [GroupSeq],
[Project2].[DocPricingUnitPrice] AS [DocPricingUnitPrice],
[Project2].[OverridePriceList] AS [OverridePriceList],
[Project2].[QtyOption] AS [QtyOption],
[Project2].[OrigComment] AS [OrigComment],
[Project2].[SmartString] AS [SmartString],
[Project2].[SmartStringProcessed] AS [SmartStringProcessed],
[Project2].[DueDate] AS [DueDate],
[Project2].[ContractID] AS [ContractID],
[Project2].[LinkToContract] AS [LinkToContract],
[Project2].[SelCurrPricingUnitPrice] AS [SelCurrPricingUnitPrice],
[Project2].[ChangedBy] AS [ChangedBy],
[Project2].[ChangeDate] AS [ChangeDate],
[Project2].[PCLinkRemoved] AS [PCLinkRemoved],
[Project2].[TaxCatID] AS [TaxCatID],
[Project2].[NoTaxRecalc] AS [NoTaxRecalc],
[Project2].[InUnitCost] AS [InUnitCost],
[Project2].[DocInUnitCost] AS [DocInUnitCost],
[Project2].[Rpt1InUnitCost] AS [Rpt1InUnitCost],
[Project2].[Rpt2InUnitCost] AS [Rpt2InUnitCost],
[Project2].[Rpt3InUnitCost] AS [Rpt3InUnitCost],
[Project2].[InAdvancePayBal] AS [InAdvancePayBal],
[Project2].[DocInAdvancePayBal] AS [DocInAdvancePayBal],
[Project2].[Rpt1InAdvancePayBal] AS [Rpt1InAdvancePayBal],
[Project2].[Rpt2InAdvancePayBal] AS [Rpt2InAdvancePayBal],
[Project2].[Rpt3InAdvancePayBal] AS [Rpt3InAdvancePayBal],
[Project2].[InContractUnitCost] AS [InContractUnitCost],
[Project2].[DocInContractUnitCost] AS [DocInContractUnitCost],
[Project2].[Rpt1InContractUnitCost] AS [Rpt1InContractUnitCost],
[Project2].[Rpt2InContractUnitCost] AS [Rpt2InContractUnitCost],
[Project2].[Rpt3InContractUnitCost] AS [Rpt3InContractUnitCost],
[Project2].[DocExtCost] AS [DocExtCost],
[Project2].[ExtCost] AS [ExtCost],
[Project2].[Rpt1ExtCost] AS [Rpt1ExtCost],
[Project2].[Rpt2ExtCost] AS [Rpt2ExtCost],
[Project2].[Rpt3ExtCost] AS [Rpt3ExtCost],
[Project2].[DocMiscCost] AS [DocMiscCost],
[Project2].[MiscCost] AS [MiscCost],
[Project2].[Rpt1MiscCost] AS [Rpt1MiscCost],
[Project2].[Rpt2MiscCost] AS [Rpt2MiscCost],
[Project2].[Rpt3MiscCost] AS [Rpt3MiscCost],
[Project2].[TotalTax] AS [TotalTax],
[Project2].[DocTotalTax] AS [DocTotalTax],
[Project2].[Rpt1TotalTax] AS [Rpt1TotalTax],
[Project2].[Rpt2TotalTax] AS [Rpt2TotalTax],
[Project2].[Rpt3TotalTax] AS [Rpt3TotalTax],
[Project2].[TotalSATax] AS [TotalSATax],
[Project2].[DocTotalSATax] AS [DocTotalSATax],
[Project2].[Rpt1TotalSATax] AS [Rpt1TotalSATax],
[Project2].[Rpt2TotalSATax] AS [Rpt2TotalSATax],
[Project2].[Rpt3TotalSATax] AS [Rpt3TotalSATax],
[Project2].[TotalDedTax] AS [TotalDedTax],
[Project2].[DocTotalDedTax] AS [DocTotalDedTax],
[Project2].[Rpt1TotalDedTax] AS [Rpt1TotalDedTax],
[Project2].[Rpt2TotalDedTax] AS [Rpt2TotalDedTax],
[Project2].[Rpt3TotalDedTax] AS [Rpt3TotalDedTax],
[Project2].[CommodityCode] AS [CommodityCode],
[Project2].[UD_SysRevID] AS [UD_SysRevID],
[Project2].[Metric_c] AS [Metric_c],
[Project2].[Width_c] AS [Width_c],
[Project2].[Inactive] AS [Inactive],
[Project2].[Company1] AS [Company1],
[Project2].[VendorID] AS [VendorID],
[Project2].[Name] AS [Name],
[Project2].[VendorNum1] AS [VendorNum1],
[Project2].[Address1] AS [Address1],
[Project2].[Address2] AS [Address2],
[Project2].[Address3] AS [Address3],
[Project2].[City] AS [City],
[Project2].[State] AS [State],
[Project2].[ZIP] AS [ZIP],
[Project2].[Country] AS [Country],
[Project2].[TaxPayerID] AS [TaxPayerID],
[Project2].[PurPoint] AS [PurPoint],
[Project2].[TermsCode] AS [TermsCode],
[Project2].[GroupCode] AS [GroupCode],
[Project2].[Print1099] AS [Print1099],
[Project2].[OneCheck] AS [OneCheck],
[Project2].[PrintLabels] AS [PrintLabels],
[Project2].[FaxNum] AS [FaxNum],
[Project2].[PhoneNum] AS [PhoneNum],
[Project2].[Comment] AS [Comment],
[Project2].[PayHold] AS [PayHold],
[Project2].[PrimPCon] AS [PrimPCon],
[Project2].[AccountRef] AS [AccountRef],
[Project2].[DefaultFOB] AS [DefaultFOB],
[Project2].[RcvInspectionReq1] AS [RcvInspectionReq1],
[Project2].[CurrencyCode] AS [CurrencyCode],
[Project2].[TaxRegionCode] AS [TaxRegionCode],
[Project2].[CountryNum] AS [CountryNum],
[Project2].[LangNameID] AS [LangNameID],
[Project2].[BorderCrossing] AS [BorderCrossing],
[Project2].[FormatStr] AS [FormatStr],
[Project2].[ElecPayment] AS [ElecPayment],
[Project2].[PrimaryBankID] AS [PrimaryBankID],
[Project2].[Approved] AS [Approved],
[Project2].[ICVend] AS [ICVend],
[Project2].[EMailAddress] AS [EMailAddress],
[Project2].[WebVendor] AS [WebVendor],
[Project2].[VendURL] AS [VendURL],
[Project2].[EarlyBuffer] AS [EarlyBuffer],
[Project2].[LateBuffer] AS [LateBuffer],
[Project2].[OnTimeRating] AS [OnTimeRating],
[Project2].[QualityRating] AS [QualityRating],
[Project2].[PriceRating] AS [PriceRating],
[Project2].[ServiceRating] AS [ServiceRating],
[Project2].[ExternalId] AS [ExternalId],
[Project2].[VendPILimit] AS [VendPILimit],
[Project2].[GlobalVendor] AS [GlobalVendor],
[Project2].[ICTrader] AS [ICTrader],
[Project2].[TaxAuthorityCode] AS [TaxAuthorityCode],
[Project2].[GlobalLock] AS [GlobalLock],
[Project2].[MinOrderValue] AS [MinOrderValue],
[Project2].[CalendarID] AS [CalendarID],
[Project2].[EDICode] AS [EDICode],
[Project2].[ConsolidatedPurchasing] AS [ConsolidatedPurchasing],
[Project2].[LocalPurchasing] AS [LocalPurchasing],
[Project2].[ResDelivery] AS [ResDelivery],
[Project2].[SatDelivery] AS [SatDelivery],
[Project2].[SatPickup] AS [SatPickup],
[Project2].[Hazmat] AS [Hazmat],
[Project2].[DocOnly] AS [DocOnly],
[Project2].[RefNotes] AS [RefNotes],
[Project2].[ApplyChrg] AS [ApplyChrg],
[Project2].[ChrgAmount] AS [ChrgAmount],
[Project2].[COD] AS [COD],
[Project2].[CODFreight] AS [CODFreight],
[Project2].[CODCheck] AS [CODCheck],
[Project2].[CODAmount] AS [CODAmount],
[Project2].[GroundType] AS [GroundType],
[Project2].[NotifyFlag] AS [NotifyFlag],
[Project2].[NotifyEMail] AS [NotifyEMail],
[Project2].[DeclaredIns] AS [DeclaredIns],
[Project2].[DeclaredAmt] AS [DeclaredAmt],
[Project2].[ServSignature] AS [ServSignature],
[Project2].[ServAlert] AS [ServAlert],
[Project2].[ServHomeDel] AS [ServHomeDel],
[Project2].[DeliveryType] AS [DeliveryType],
[Project2].[ServDeliveryDate] AS [ServDeliveryDate],
[Project2].[ServPhone] AS [ServPhone],
[Project2].[ServInstruct] AS [ServInstruct],
[Project2].[ServRelease] AS [ServRelease],
[Project2].[ServAuthNum] AS [ServAuthNum],
[Project2].[ServRef1] AS [ServRef1],
[Project2].[ServRef2] AS [ServRef2],
[Project2].[ServRef3] AS [ServRef3],
[Project2].[ServRef4] AS [ServRef4],
[Project2].[ServRef5] AS [ServRef5],
[Project2].[CPay] AS [CPay],
[Project2].[IndividualPackIDs] AS [IndividualPackIDs],
[Project2].[IntrntlShip] AS [IntrntlShip],
[Project2].[CertOfOrigin] AS [CertOfOrigin],
[Project2].[CommercialInvoice] AS [CommercialInvoice],
[Project2].[ShipExprtDeclartn] AS [ShipExprtDeclartn],
[Project2].[LetterOfInstr] AS [LetterOfInstr],
[Project2].[FFID] AS [FFID],
[Project2].[FFCompName] AS [FFCompName],
[Project2].[FFContact] AS [FFContact],
[Project2].[FFAddress1] AS [FFAddress1],
[Project2].[FFAddress2] AS [FFAddress2],
[Project2].[FFAddress3] AS [FFAddress3],
[Project2].[FFCity] AS [FFCity],
[Project2].[FFState] AS [FFState],
[Project2].[FFZip] AS [FFZip],
[Project2].[FFCountry] AS [FFCountry],
[Project2].[NonStdPkg] AS [NonStdPkg],
[Project2].[DeliveryConf] AS [DeliveryConf],
[Project2].[AddlHdlgFlag] AS [AddlHdlgFlag],
[Project2].[UPSQuantumView] AS [UPSQuantumView],
[Project2].[UPSQVShipFromName] AS [UPSQVShipFromName],
[Project2].[UPSQVMemo] AS [UPSQVMemo],
[Project2].[FFPhoneNum] AS [FFPhoneNum],
[Project2].[FFCountryNum] AS [FFCountryNum],
[Project2].[RevChargeMethod] AS [RevChargeMethod],
[Project2].[ManagedCust] AS [ManagedCust],
[Project2].[ManagedCustID] AS [ManagedCustID],
[Project2].[ManagedCustNum] AS [ManagedCustNum],
[Project2].[PMUID] AS [PMUID],
[Project2].[HasBank] AS [HasBank],
[Project2].[PmtAcctRef] AS [PmtAcctRef],
[Project2].[APInvoiceITCode] AS [APInvoiceITCode],
[Project2].[DebitMemoITCode] AS [DebitMemoITCode],
[Project2].[MiscPayITCode] AS [MiscPayITCode],
[Project2].[APInvoiceAdjITCode] AS [APInvoiceAdjITCode],
[Project2].[LegalName] AS [LegalName],
[Project2].[TaxRegReason] AS [TaxRegReason],
[Project2].[OrgRegCode] AS [OrgRegCode],
[Project2].[AdvTaxInv] AS [AdvTaxInv],
[Project2].[AllowAsAltRemitTo] AS [AllowAsAltRemitTo],
[Project2].[SysRevID1] AS [SysRevID1],
[Project2].[SysRowID] AS [SysRowID],
[Project2].[THBranchID] AS [THBranchID],
[Project2].[ParamCode] AS [ParamCode],
[Project2].[AGAFIPResponsibilityCode] AS [AGAFIPResponsibilityCode],
[Project2].[AGGrossIncomeTaxID] AS [AGGrossIncomeTaxID],
[Project2].[AGIDDocumentTypeCode] AS [AGIDDocumentTypeCode],
[Project2].[AGProvinceCode] AS [AGProvinceCode],
[Project2].[AGUseGoodDefaultMark] AS [AGUseGoodDefaultMark],
[Project2].[AGApartment] AS [AGApartment],
[Project2].[AGExtraStreetNumber] AS [AGExtraStreetNumber],
[Project2].[AGFloor] AS [AGFloor],
[Project2].[AGLocationCode] AS [AGLocationCode],
[Project2].[AGNeighborhood] AS [AGNeighborhood],
[Project2].[AGStreet] AS [AGStreet],
[Project2].[AGStreetNumber] AS [AGStreetNumber],
[Project2].[COOneTimeID] AS [COOneTimeID],
[Project2].[NoBankingReference] AS [NoBankingReference],
[Project2].[PEGoodsContributor] AS [PEGoodsContributor],
[Project2].[PEWithholdAgent] AS [PEWithholdAgent],
[Project2].[PECollectionAgent] AS [PECollectionAgent],
[Project2].[PENotFound] AS [PENotFound],
[Project2].[PENoAddress] AS [PENoAddress],
[Project2].[PEIdentityDocType] AS [PEIdentityDocType],
[Project2].[COIsOneTimeVend] AS [COIsOneTimeVend],
[Project2].[PEDocumentID] AS [PEDocumentID],
[Project2].[MaxLateDaysPORel] AS [MaxLateDaysPORel],
[Project2].[Code1099ID] AS [Code1099ID],
[Project2].[TIN] AS [TIN],
[Project2].[TINType] AS [TINType],
[Project2].[SecondTINNotice] AS [SecondTINNotice],
[Project2].[NameControl] AS [NameControl],
[Project2].[ShipViaCode] AS [ShipViaCode],
[Project2].[NonUS] AS [NonUS],
[Project2].[FormTypeID] AS [FormTypeID],
[Project2].[INSupplierType] AS [INSupplierType],
[Project2].[INCSTNumber] AS [INCSTNumber],
[Project2].[INPANNumber] AS [INPANNumber],
[Project2].[DEOrgType] AS [DEOrgType],
[Project2].[PaymentReporting] AS [PaymentReporting],
[Project2].[ExternalPurchasing] AS [ExternalPurchasing],
[Project2].[MXRetentionCode] AS [MXRetentionCode],
[Project2].[Reporting1099Name] AS [Reporting1099Name],
[Project2].[Reporting1099Name2] AS [Reporting1099Name2],
[Project2].[FATCA] AS [FATCA],
[Project2].[AccountNum] AS [AccountNum],
[Project2].[TWGUIRegNum] AS [TWGUIRegNum],
[Project2].[MXTARCode] AS [MXTARCode],
[Project2].[PEAddressID] AS [PEAddressID],
[Project2].[PERetentionRegime] AS [PERetentionRegime],
[Project2].[TaxEntityType] AS [TaxEntityType],
[Project2].[INGSTComplianceRate] AS [INGSTComplianceRate],
[Project2].[INTaxRegistrationID] AS [INTaxRegistrationID],
[Project2].[TINValidationStatus] AS [TINValidationStatus],
[Project2].[ImporterOfRecord] AS [ImporterOfRecord],
[Project2].[PLAutomaticAPInvoiceNum] AS [PLAutomaticAPInvoiceNum],
[Project2].[SEC] AS [SEC],
[Project2].[Company2] AS [Company2],
[Project2].[UOMCode] AS [UOMCode],
[Project2].[UOMDesc] AS [UOMDesc],
[Project2].[Active] AS [Active],
[Project2].[UOMSymbol] AS [UOMSymbol],
[Project2].[AllowDecimals] AS [AllowDecimals],
[Project2].[NumOfDec] AS [NumOfDec],
[Project2].[Rounding] AS [Rounding],
[Project2].[GlobalUOM] AS [GlobalUOM],
[Project2].[GlobalLock1] AS [GlobalLock1],
[Project2].[SysRevID2] AS [SysRevID2],
[Project2].[SysRowID1] AS [SysRowID1],
[Project2].[AGAFIPCode] AS [AGAFIPCode],
[Project2].[AGCOTCode] AS [AGCOTCode],
[Project2].[PESUNATCode] AS [PESUNATCode],
[Project2].[MXCustomsUOM] AS [MXCustomsUOM],
[Project2].[MXSATCode] AS [MXSATCode]
FROM ( SELECT
[Extent1].[ForeignSysRowID] AS [ForeignSysRowID],
[Extent1].[UD_SysRevID] AS [UD_SysRevID],
[Extent1].[Length_c] AS [Length_c],
[Extent1].[Metric_c] AS [Metric_c],
[Extent1].[Width_c] AS [Width_c],
[Extent2].[Company] AS [Company],
[Extent2].[OpenLine] AS [OpenLine],
[Extent2].[VoidLine] AS [VoidLine],
[Extent2].[PONUM] AS [PONUM],
[Extent2].[POLine] AS [POLine],
[Extent2].[LineDesc] AS [LineDesc],
[Extent2].[IUM] AS [IUM],
[Extent2].[UnitCost] AS [UnitCost],
[Extent2].[DocUnitCost] AS [DocUnitCost],
[Extent2].[OrderQty] AS [OrderQty],
[Extent2].[XOrderQty] AS [XOrderQty],
[Extent2].[Taxable] AS [Taxable],
[Extent2].[PUM] AS [PUM],
[Extent2].[CostPerCode] AS [CostPerCode],
[Extent2].[PartNum] AS [PartNum],
[Extent2].[VenPartNum] AS [VenPartNum],
[Extent2].[CommentText] AS [CommentText],
[Extent2].[ClassID] AS [ClassID],
[Extent2].[RevisionNum] AS [RevisionNum],
[Extent2].[RcvInspectionReq] AS [RcvInspectionReq],
[Extent2].[VendorNum] AS [VendorNum],
[Extent2].[AdvancePayBal] AS [AdvancePayBal],
[Extent2].[DocAdvancePayBal] AS [DocAdvancePayBal],
[Extent2].[Confirmed] AS [Confirmed],
[Extent2].[DateChgReq] AS [DateChgReq],
[Extent2].[QtyChgReq] AS [QtyChgReq],
[Extent2].[PartNumChgReq] AS [PartNumChgReq],
[Extent2].[RevisionNumChgReq] AS [RevisionNumChgReq],
[Extent2].[ConfirmDate] AS [ConfirmDate],
[Extent2].[ConfirmVia] AS [ConfirmVia],
[Extent2].[PrcChgReq] AS [PrcChgReq],
[Extent2].[PurchCode] AS [PurchCode],
[Extent2].[OrderNum] AS [OrderNum],
[Extent2].[OrderLine] AS [OrderLine],
[Extent2].[Linked] AS [Linked],
[Extent2].[ExtCompany] AS [ExtCompany],
[Extent2].[GlbCompany] AS [GlbCompany],
[Extent2].[ContractActive] AS [ContractActive],
[Extent2].[ContractQty] AS [ContractQty],
[Extent2].[ContractUnitCost] AS [ContractUnitCost],
[Extent2].[ContractDocUnitCost] AS [ContractDocUnitCost],
[Extent2].[Rpt1AdvancePayBal] AS [Rpt1AdvancePayBal],
[Extent2].[Rpt2AdvancePayBal] AS [Rpt2AdvancePayBal],
[Extent2].[Rpt3AdvancePayBal] AS [Rpt3AdvancePayBal],
[Extent2].[Rpt1UnitCost] AS [Rpt1UnitCost],
[Extent2].[Rpt2UnitCost] AS [Rpt2UnitCost],
[Extent2].[Rpt3UnitCost] AS [Rpt3UnitCost],
[Extent2].[ContractQtyUOM] AS [ContractQtyUOM],
[Extent2].[Rpt1ContractUnitCost] AS [Rpt1ContractUnitCost],
[Extent2].[Rpt2ContractUnitCost] AS [Rpt2ContractUnitCost],
[Extent2].[Rpt3ContractUnitCost] AS [Rpt3ContractUnitCost],
[Extent2].[BaseQty] AS [BaseQty],
[Extent2].[BaseUOM] AS [BaseUOM],
[Extent2].[BTOOrderNum] AS [BTOOrderNum],
[Extent2].[BTOOrderLine] AS [BTOOrderLine],
[Extent2].[VendorPartOpts] AS [VendorPartOpts],
[Extent2].[MfgPartOpts] AS [MfgPartOpts],
[Extent2].[SubPartOpts] AS [SubPartOpts],
[Extent2].[MfgNum] AS [MfgNum],
[Extent2].[MfgPartNum] AS [MfgPartNum],
[Extent2].[SubPartNum] AS [SubPartNum],
[Extent2].[SubPartType] AS [SubPartType],
[Extent2].[ConfigUnitCost] AS [ConfigUnitCost],
[Extent2].[ConfigBaseUnitCost] AS [ConfigBaseUnitCost],
[Extent2].[ConvOverRide] AS [ConvOverRide],
[Extent2].[BasePartNum] AS [BasePartNum],
[Extent2].[BaseRevisionNum] AS [BaseRevisionNum],
[Extent2].[Direction] AS [Direction],
[Extent2].[Per] AS [Per],
[Extent2].[MaintainPricingUnits] AS [MaintainPricingUnits],
[Extent2].[OverrideConversion] AS [OverrideConversion],
[Extent2].[RowsManualFactor] AS [RowsManualFactor],
[Extent2].[KeepRowsManualFactorTmp] AS [KeepRowsManualFactorTmp],
[Extent2].[ShipToSupplierDate] AS [ShipToSupplierDate],
[Extent2].[Factor] AS [Factor],
[Extent2].[PricingQty] AS [PricingQty],
[Extent2].[PricingUnitPrice] AS [PricingUnitPrice],
[Extent2].[UOM] AS [UOM],
[Extent2].[SysRevID] AS [SysRevID],
[Extent2].[GroupSeq] AS [GroupSeq],
[Extent2].[DocPricingUnitPrice] AS [DocPricingUnitPrice],
[Extent2].[OverridePriceList] AS [OverridePriceList],
[Extent2].[QtyOption] AS [QtyOption],
[Extent2].[OrigComment] AS [OrigComment],
[Extent2].[SmartString] AS [SmartString],
[Extent2].[SmartStringProcessed] AS [SmartStringProcessed],
[Extent2].[DueDate] AS [DueDate],
[Extent2].[ContractID] AS [ContractID],
[Extent2].[LinkToContract] AS [LinkToContract],
[Extent2].[SelCurrPricingUnitPrice] AS [SelCurrPricingUnitPrice],
[Extent2].[ChangedBy] AS [ChangedBy],
[Extent2].[ChangeDate] AS [ChangeDate],
[Extent2].[PCLinkRemoved] AS [PCLinkRemoved],
[Extent2].[TaxCatID] AS [TaxCatID],
[Extent2].[NoTaxRecalc] AS [NoTaxRecalc],
[Extent2].[InUnitCost] AS [InUnitCost],
[Extent2].[DocInUnitCost] AS [DocInUnitCost],
[Extent2].[Rpt1InUnitCost] AS [Rpt1InUnitCost],
[Extent2].[Rpt2InUnitCost] AS [Rpt2InUnitCost],
[Extent2].[Rpt3InUnitCost] AS [Rpt3InUnitCost],
[Extent2].[InAdvancePayBal] AS [InAdvancePayBal],
[Extent2].[DocInAdvancePayBal] AS [DocInAdvancePayBal],
[Extent2].[Rpt1InAdvancePayBal] AS [Rpt1InAdvancePayBal],
[Extent2].[Rpt2InAdvancePayBal] AS [Rpt2InAdvancePayBal],
[Extent2].[Rpt3InAdvancePayBal] AS [Rpt3InAdvancePayBal],
[Extent2].[InContractUnitCost] AS [InContractUnitCost],
[Extent2].[DocInContractUnitCost] AS [DocInContractUnitCost],
[Extent2].[Rpt1InContractUnitCost] AS [Rpt1InContractUnitCost],
[Extent2].[Rpt2InContractUnitCost] AS [Rpt2InContractUnitCost],
[Extent2].[Rpt3InContractUnitCost] AS [Rpt3InContractUnitCost],
[Extent2].[DocExtCost] AS [DocExtCost],
[Extent2].[ExtCost] AS [ExtCost],
[Extent2].[Rpt1ExtCost] AS [Rpt1ExtCost],
[Extent2].[Rpt2ExtCost] AS [Rpt2ExtCost],
[Extent2].[Rpt3ExtCost] AS [Rpt3ExtCost],
[Extent2].[DocMiscCost] AS [DocMiscCost],
[Extent2].[MiscCost] AS [MiscCost],
[Extent2].[Rpt1MiscCost] AS [Rpt1MiscCost],
[Extent2].[Rpt2MiscCost] AS [Rpt2MiscCost],
[Extent2].[Rpt3MiscCost] AS [Rpt3MiscCost],
[Extent2].[TotalTax] AS [TotalTax],
[Extent2].[DocTotalTax] AS [DocTotalTax],
[Extent2].[Rpt1TotalTax] AS [Rpt1TotalTax],
[Extent2].[Rpt2TotalTax] AS [Rpt2TotalTax],
[Extent2].[Rpt3TotalTax] AS [Rpt3TotalTax],
[Extent2].[TotalSATax] AS [TotalSATax],
[Extent2].[DocTotalSATax] AS [DocTotalSATax],
[Extent2].[Rpt1TotalSATax] AS [Rpt1TotalSATax],
[Extent2].[Rpt2TotalSATax] AS [Rpt2TotalSATax],
[Extent2].[Rpt3TotalSATax] AS [Rpt3TotalSATax],
[Extent2].[TotalDedTax] AS [TotalDedTax],
[Extent2].[DocTotalDedTax] AS [DocTotalDedTax],
[Extent2].[Rpt1TotalDedTax] AS [Rpt1TotalDedTax],
[Extent2].[Rpt2TotalDedTax] AS [Rpt2TotalDedTax],
[Extent2].[Rpt3TotalDedTax] AS [Rpt3TotalDedTax],
[Extent2].[CommodityCode] AS [CommodityCode],
[Extent3].[Inactive] AS [Inactive],
[Extent3].[Company] AS [Company1],
[Extent3].[VendorID] AS [VendorID],
[Extent3].[Name] AS [Name],
[Extent3].[VendorNum] AS [VendorNum1],
[Extent3].[Address1] AS [Address1],
[Extent3].[Address2] AS [Address2],
[Extent3].[Address3] AS [Address3],
[Extent3].[City] AS [City],
[Extent3].[State] AS [State],
[Extent3].[ZIP] AS [ZIP],
[Extent3].[Country] AS [Country],
[Extent3].[TaxPayerID] AS [TaxPayerID],
[Extent3].[PurPoint] AS [PurPoint],
[Extent3].[TermsCode] AS [TermsCode],
[Extent3].[GroupCode] AS [GroupCode],
[Extent3].[Print1099] AS [Print1099],
[Extent3].[OneCheck] AS [OneCheck],
[Extent3].[PrintLabels] AS [PrintLabels],
[Extent3].[FaxNum] AS [FaxNum],
[Extent3].[PhoneNum] AS [PhoneNum],
[Extent3].[Comment] AS [Comment],
[Extent3].[PayHold] AS [PayHold],
[Extent3].[PrimPCon] AS [PrimPCon],
[Extent3].[AccountRef] AS [AccountRef],
[Extent3].[DefaultFOB] AS [DefaultFOB],
[Extent3].[RcvInspectionReq] AS [RcvInspectionReq1],
[Extent3].[CurrencyCode] AS [CurrencyCode],
[Extent3].[TaxRegionCode] AS [TaxRegionCode],
[Extent3].[CountryNum] AS [CountryNum],
[Extent3].[LangNameID] AS [LangNameID],
[Extent3].[BorderCrossing] AS [BorderCrossing],
[Extent3].[FormatStr] AS [FormatStr],
[Extent3].[ElecPayment] AS [ElecPayment],
[Extent3].[PrimaryBankID] AS [PrimaryBankID],
[Extent3].[Approved] AS [Approved],
[Extent3].[ICVend] AS [ICVend],
[Extent3].[EMailAddress] AS [EMailAddress],
[Extent3].[WebVendor] AS [WebVendor],
[Extent3].[VendURL] AS [VendURL],
[Extent3].[EarlyBuffer] AS [EarlyBuffer],
[Extent3].[LateBuffer] AS [LateBuffer],
[Extent3].[OnTimeRating] AS [OnTimeRating],
[Extent3].[QualityRating] AS [QualityRating],
[Extent3].[PriceRating] AS [PriceRating],
[Extent3].[ServiceRating] AS [ServiceRating],
[Extent3].[ExternalId] AS [ExternalId],
[Extent3].[VendPILimit] AS [VendPILimit],
[Extent3].[GlobalVendor] AS [GlobalVendor],
[Extent3].[ICTrader] AS [ICTrader],
[Extent3].[TaxAuthorityCode] AS [TaxAuthorityCode],
[Extent3].[GlobalLock] AS [GlobalLock],
[Extent3].[MinOrderValue] AS [MinOrderValue],
[Extent3].[CalendarID] AS [CalendarID],
[Extent3].[EDICode] AS [EDICode],
[Extent3].[ConsolidatedPurchasing] AS [ConsolidatedPurchasing],
[Extent3].[LocalPurchasing] AS [LocalPurchasing],
[Extent3].[ResDelivery] AS [ResDelivery],
[Extent3].[SatDelivery] AS [SatDelivery],
[Extent3].[SatPickup] AS [SatPickup],
[Extent3].[Hazmat] AS [Hazmat],
[Extent3].[DocOnly] AS [DocOnly],
[Extent3].[RefNotes] AS [RefNotes],
[Extent3].[ApplyChrg] AS [ApplyChrg],
[Extent3].[ChrgAmount] AS [ChrgAmount],
[Extent3].[COD] AS [COD],
[Extent3].[CODFreight] AS [CODFreight],
[Extent3].[CODCheck] AS [CODCheck],
[Extent3].[CODAmount] AS [CODAmount],
[Extent3].[GroundType] AS [GroundType],
[Extent3].[NotifyFlag] AS [NotifyFlag],
[Extent3].[NotifyEMail] AS [NotifyEMail],
[Extent3].[DeclaredIns] AS [DeclaredIns],
[Extent3].[DeclaredAmt] AS [DeclaredAmt],
[Extent3].[ServSignature] AS [ServSignature],
[Extent3].[ServAlert] AS [ServAlert],
[Extent3].[ServHomeDel] AS [ServHomeDel],
[Extent3].[DeliveryType] AS [DeliveryType],
[Extent3].[ServDeliveryDate] AS [ServDeliveryDate],
[Extent3].[ServPhone] AS [ServPhone],
[Extent3].[ServInstruct] AS [ServInstruct],
[Extent3].[ServRelease] AS [ServRelease],
[Extent3].[ServAuthNum] AS [ServAuthNum],
[Extent3].[ServRef1] AS [ServRef1],
[Extent3].[ServRef2] AS [ServRef2],
[Extent3].[ServRef3] AS [ServRef3],
[Extent3].[ServRef4] AS [ServRef4],
[Extent3].[ServRef5] AS [ServRef5],
[Extent3].[CPay] AS [CPay],
[Extent3].[IndividualPackIDs] AS [IndividualPackIDs],
[Extent3].[IntrntlShip] AS [IntrntlShip],
[Extent3].[CertOfOrigin] AS [CertOfOrigin],
[Extent3].[CommercialInvoice] AS [CommercialInvoice],
[Extent3].[ShipExprtDeclartn] AS [ShipExprtDeclartn],
[Extent3].[LetterOfInstr] AS [LetterOfInstr],
[Extent3].[FFID] AS [FFID],
[Extent3].[FFCompName] AS [FFCompName],
[Extent3].[FFContact] AS [FFContact],
[Extent3].[FFAddress1] AS [FFAddress1],
[Extent3].[FFAddress2] AS [FFAddress2],
[Extent3].[FFAddress3] AS [FFAddress3],
[Extent3].[FFCity] AS [FFCity],
[Extent3].[FFState] AS [FFState],
[Extent3].[FFZip] AS [FFZip],
[Extent3].[FFCountry] AS [FFCountry],
[Extent3].[NonStdPkg] AS [NonStdPkg],
[Extent3].[DeliveryConf] AS [DeliveryConf],
[Extent3].[AddlHdlgFlag] AS [AddlHdlgFlag],
[Extent3].[UPSQuantumView] AS [UPSQuantumView],
[Extent3].[UPSQVShipFromName] AS [UPSQVShipFromName],
[Extent3].[UPSQVMemo] AS [UPSQVMemo],
[Extent3].[FFPhoneNum] AS [FFPhoneNum],
[Extent3].[FFCountryNum] AS [FFCountryNum],
[Extent3].[RevChargeMethod] AS [RevChargeMethod],
[Extent3].[ManagedCust] AS [ManagedCust],
[Extent3].[ManagedCustID] AS [ManagedCustID],
[Extent3].[ManagedCustNum] AS [ManagedCustNum],
[Extent3].[PMUID] AS [PMUID],
[Extent3].[HasBank] AS [HasBank],
[Extent3].[PmtAcctRef] AS [PmtAcctRef],
[Extent3].[APInvoiceITCode] AS [APInvoiceITCode],
[Extent3].[DebitMemoITCode] AS [DebitMemoITCode],
[Extent3].[MiscPayITCode] AS [MiscPayITCode],
[Extent3].[APInvoiceAdjITCode] AS [APInvoiceAdjITCode],
[Extent3].[LegalName] AS [LegalName],
[Extent3].[TaxRegReason] AS [TaxRegReason],
[Extent3].[OrgRegCode] AS [OrgRegCode],
[Extent3].[AdvTaxInv] AS [AdvTaxInv],
[Extent3].[AllowAsAltRemitTo] AS [AllowAsAltRemitTo],
[Extent3].[SysRevID] AS [SysRevID1],
[Extent3].[SysRowID] AS [SysRowID],
[Extent3].[THBranchID] AS [THBranchID],
[Extent3].[ParamCode] AS [ParamCode],
[Extent3].[AGAFIPResponsibilityCode] AS [AGAFIPResponsibilityCode],
[Extent3].[AGGrossIncomeTaxID] AS [AGGrossIncomeTaxID],
[Extent3].[AGIDDocumentTypeCode] AS [AGIDDocumentTypeCode],
[Extent3].[AGProvinceCode] AS [AGProvinceCode],
[Extent3].[AGUseGoodDefaultMark] AS [AGUseGoodDefaultMark],
[Extent3].[AGApartment] AS [AGApartment],
[Extent3].[AGExtraStreetNumber] AS [AGExtraStreetNumber],
[Extent3].[AGFloor] AS [AGFloor],
[Extent3].[AGLocationCode] AS [AGLocationCode],
[Extent3].[AGNeighborhood] AS [AGNeighborhood],
[Extent3].[AGStreet] AS [AGStreet],
[Extent3].[AGStreetNumber] AS [AGStreetNumber],
[Extent3].[COOneTimeID] AS [COOneTimeID],
[Extent3].[NoBankingReference] AS [NoBankingReference],
[Extent3].[PEGoodsContributor] AS [PEGoodsContributor],
[Extent3].[PEWithholdAgent] AS [PEWithholdAgent],
[Extent3].[PECollectionAgent] AS [PECollectionAgent],
[Extent3].[PENotFound] AS [PENotFound],
[Extent3].[PENoAddress] AS [PENoAddress],
[Extent3].[PEIdentityDocType] AS [PEIdentityDocType],
[Extent3].[COIsOneTimeVend] AS [COIsOneTimeVend],
[Extent3].[PEDocumentID] AS [PEDocumentID],
[Extent3].[MaxLateDaysPORel] AS [MaxLateDaysPORel],
[Extent3].[Code1099ID] AS [Code1099ID],
[Extent3].[TIN] AS [TIN],
[Extent3].[TINType] AS [TINType],
[Extent3].[SecondTINNotice] AS [SecondTINNotice],
[Extent3].[NameControl] AS [NameControl],
[Extent3].[ShipViaCode] AS [ShipViaCode],
[Extent3].[NonUS] AS [NonUS],
[Extent3].[FormTypeID] AS [FormTypeID],
[Extent3].[INSupplierType] AS [INSupplierType],
[Extent3].[INCSTNumber] AS [INCSTNumber],
[Extent3].[INPANNumber] AS [INPANNumber],
[Extent3].[DEOrgType] AS [DEOrgType],
[Extent3].[PaymentReporting] AS [PaymentReporting],
[Extent3].[ExternalPurchasing] AS [ExternalPurchasing],
[Extent3].[MXRetentionCode] AS [MXRetentionCode],
[Extent3].[Reporting1099Name] AS [Reporting1099Name],
[Extent3].[Reporting1099Name2] AS [Reporting1099Name2],
[Extent3].[FATCA] AS [FATCA],
[Extent3].[AccountNum] AS [AccountNum],
[Extent3].[TWGUIRegNum] AS [TWGUIRegNum],
[Extent3].[MXTARCode] AS [MXTARCode],
[Extent3].[PEAddressID] AS [PEAddressID],
[Extent3].[PERetentionRegime] AS [PERetentionRegime],
[Extent3].[TaxEntityType] AS [TaxEntityType],
[Extent3].[INGSTComplianceRate] AS [INGSTComplianceRate],
[Extent3].[INTaxRegistrationID] AS [INTaxRegistrationID],
[Extent3].[TINValidationStatus] AS [TINValidationStatus],
[Extent3].[ImporterOfRecord] AS [ImporterOfRecord],
[Extent3].[PLAutomaticAPInvoiceNum] AS [PLAutomaticAPInvoiceNum],
[Extent3].[SEC] AS [SEC],
[Extent4].[Company] AS [Company2],
[Extent4].[UOMCode] AS [UOMCode],
[Extent4].[UOMDesc] AS [UOMDesc],
[Extent4].[Active] AS [Active],
[Extent4].[UOMSymbol] AS [UOMSymbol],
[Extent4].[AllowDecimals] AS [AllowDecimals],
[Extent4].[NumOfDec] AS [NumOfDec],
[Extent4].[Rounding] AS [Rounding],
[Extent4].[GlobalUOM] AS [GlobalUOM],
[Extent4].[GlobalLock] AS [GlobalLock1],
[Extent4].[SysRevID] AS [SysRevID2],
[Extent4].[SysRowID] AS [SysRowID1],
[Extent4].[AGAFIPCode] AS [AGAFIPCode],
[Extent4].[AGCOTCode] AS [AGCOTCode],
[Extent4].[PESUNATCode] AS [PESUNATCode],
[Extent4].[MXCustomsUOM] AS [MXCustomsUOM],
[Extent4].[MXSATCode] AS [MXSATCode]
FROM [Erp].[PODetail_UD] AS [Extent1]
INNER JOIN [Erp].[PODetail] AS [Extent2] ON [Extent1].[ForeignSysRowID] = [Extent2].[SysRowID]
INNER JOIN [Erp].[Vendor] AS [Extent3] ON ([Extent2].[Company] = [Extent3].[Company]) AND ([Extent2].[VendorNum] = [Extent3].[VendorNum])
INNER JOIN [Erp].[UOM] AS [Extent4] ON ([Extent2].[Company] = [Extent4].[Company]) AND ([Extent2].[PUM] = [Extent4].[UOMCode])
WHERE ([Extent2].[Company] = @p__linq__0) AND ( EXISTS (SELECT
1 AS [C1]
FROM [Erp].[PODetail_UD] AS [Extent5]
INNER JOIN [Erp].[PODetail] AS [Extent6] ON [Extent5].[ForeignSysRowID] = [Extent6].[SysRowID]
LEFT OUTER JOIN (SELECT
[Filter1].[K1] AS [K1],
[Filter1].[K2] AS [K2],
[Filter1].[K3] AS [K3],
SUM([Filter1].[A1]) AS [A1]
FROM ( SELECT
[Extent7].[Company] AS [K1],
[Extent7].[PONum] AS [K2],
[Extent7].[POLine] AS [K3],
[Extent7].[ExtCost] - [Extent7].[AdvGainLoss] AS [A1]
FROM [Erp].[APInvDtl] AS [Extent7]
INNER JOIN [Erp].[APInvHed] AS [Extent8] ON ([Extent7].[Company] = [Extent8].[Company]) AND ([Extent7].[VendorNum] = [Extent8].[VendorNum]) AND ([Extent7].[InvoiceNum] = [Extent8].[InvoiceNum])
WHERE ([Extent7].[Company] = @p__linq__1) AND ([Extent8].[Posted] = 1) AND (N''A'' = [Extent7].[LineType]) AND (((N''Date'' = @p__linq__2) AND ([Extent8].[InvoiceDate] <= @p__linq__3)) OR ((N''ApplyDate'' = @p__linq__4) AND ([Extent8].[ApplyDate] <= @p__linq__5)))
) AS [Filter1]
GROUP BY [K1], [K2], [K3] ) AS [GroupBy1] ON ([Extent6].[Company] = [GroupBy1].[K1]) AND ([Extent6].[PONUM] = [GroupBy1].[K2]) AND ([Extent6].[POLine] = [GroupBy1].[K3])
LEFT OUTER JOIN (SELECT
[Filter2].[K1] AS [K1],
[Filter2].[K2] AS [K2],
[Filter2].[K3] AS [K3],
SUM([Filter2].[A1]) AS [A1]
FROM ( SELECT
[Extent9].[Company] AS [K1],
[Extent9].[PONum] AS [K2],
[Extent9].[POLine] AS [K3],
[Extent9].[AdvancePayAmt] + [Extent9].[AdvGainLoss] AS [A1]
FROM [Erp].[APInvDtl] AS [Extent9]
INNER JOIN [Erp].[APInvHed] AS [Extent10] ON ([Extent9].[Company] = [Extent10].[Company]) AND ([Extent9].[VendorNum] = [Extent10].[VendorNum]) AND ([Extent9].[InvoiceNum] = [Extent10].[InvoiceNum])
WHERE ([Extent9].[Company] = @p__linq__6) AND ([Extent10].[Posted] = 1) AND ([Extent9].[LineType] IN (N''U'',N''R'')) AND (((N''Date'' = @p__linq__7) AND ([Extent10].[InvoiceDate] <= @p__linq__8)) OR ((N''ApplyDate'' = @p__linq__9) AND ([Extent10].[ApplyDate] <= @p__linq__10)))
) AS [Filter2]
GROUP BY [K1], [K2], [K3] ) AS [GroupBy2] ON ([Extent6].[Company] = [GroupBy2].[K1]) AND ([Extent6].[PONUM] = [GroupBy2].[K2]) AND ([Extent6].[POLine] = [GroupBy2].[K3])
WHERE ([Extent6].[Company] = @p__linq__11) AND ( NOT ((cast(0 as decimal(18)) = ((CASE WHEN ([GroupBy1].[A1] IS NULL) THEN cast(0 as decimal(18)) ELSE [GroupBy1].[A1] END) - (CASE WHEN ([GroupBy2].[A1] IS NULL) THEN cast(0 as decimal(18)) ELSE [GroupBy2].[A1] END))) AND ((CASE WHEN ([GroupBy1].[A1] IS NULL) THEN cast(0 as decimal(18)) ELSE [GroupBy1].[A1] END) - (CASE WHEN ([GroupBy2].[A1] IS NULL) THEN cast(0 as decimal(18)) ELSE [GroupBy2].[A1] END) IS NOT NULL))) AND ([Extent6].[Company] = [Extent2].[Company]) AND ([Extent6].[POLine] = [Extent2].[POLine]) AND ([Extent6].[PONUM] = [Extent2].[PONUM])
))
) AS [Project2]
ORDER BY [Project2].[Company] ASC, [Project2].[PONUM] ASC, [Project2].[POLine] ASC',N'@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000),@p__linq__2 nvarchar(4000),@p__linq__3 datetime2(7),@p__linq__4 nvarchar(4000),@p__linq__5 datetime2(7),@p__linq__6 nvarchar(4000),@p__linq__7 nvarchar(4000),@p__linq__8 datetime2(7),@p__linq__9 nvarchar(4000),@p__linq__10 datetime2(7),@p__linq__11 nvarchar(4000)',@p__linq__0=N'02',@p__linq__1=N'02',@p__linq__2=N'Date',@p__linq__3='2019-04-01 00:00:00',@p__linq__4=N'Date',@p__linq__5='2019-04-01 00:00:00',@p__linq__6=N'02',@p__linq__7=N'Date',@p__linq__8='2019-04-01 00:00:00',@p__linq__9=N'Date',@p__linq__10='2019-04-01 00:00:00',@p__linq__11=N'02'Thanks! I had a quick browse through in ILSpy. Which branch of the tree did you find this?
(This is exactly the type of thing I was looking for!)
John, that’s golden thank you Again, I had done exactly that but was browsing quickly through the trace on a live environment, and even with only having it active for the time period when the report ran (5-10 seconds) there were thousands of lines. I think the lesson here is to slow down and take more time searching, but I was looking in the rights places.
Now to work out which tables those are, based on the fields being referenced!
Hi @markdamen,
have you looked within the Customization/Data Tools option, usually it lists all the Form parameters, and called Db fields
@John_Mitchell solved it for me, I’ll mark your answer. I did the same SQL trace myself, as mine was different from yours because I didn’t have UD fields on the PO Detail table. Great to know that it’s there in the trace, but it is messy and hard to read with all of the alias names to Extents, Project etc etc.
Is that query hard baked into Epicor, or is that Entity Framework doing the magic to convert what the application is asking for into SQL query? I guess the later, because it is dynamically changing to allow UD fields in your environment and not in mine. Very cool.
