Sage 100 ProvideX SQL: AR_InvoiceHistoryDetail Load Replace Example
AR_InvoiceHistoryDetail
Join with AR_InvoiceHistoryHeader
to get InvoiceDate
.
SQL
-- SQL for Extract SQL Statement panel
SELECT
D.[AliasItemNo],
D.[AlternateTaxIdentifier],
D.[APDivisionNo],
D.[AppliedCustomerPONo],
D.[AppliedSalesOrderNo],
D.[BillOption1],
D.[BillOption2],
D.[BillOption3],
D.[BillOption4],
D.[BillOption5],
D.[BillOption6],
D.[BillOption7],
D.[BillOption8],
D.[BillOption9],
D.[CommentText],
D.[Commissionable],
D.[CommissionAmt],
D.[CommodityCode],
D.[CostCode],
D.[CostOfGoodsSoldAcctKey],
D.[CostType],
D.[CustomerAction],
D.[DebitCreditIndicator],
D.[DetailSeqNo],
D.[Discount],
D.[DropShip],
D.[EncryptedVals],
D.[ExpirationDate],
D.[ExpirationOverridden],
D.[ExplodedKitItem],
D.[ExtendedDescriptionKey],
D.[ExtensionAmt],
D.[HeaderSeqNo],
D.[InventoryAcctKey],
D.[InvoiceNo],
D.[ItemAction],
D.[ItemCode],
D.[ItemCodeDesc],
D.[ItemType],
D.[KitItem],
D.[LineDiscountPercent],
D.[NetGrossIndicator],
D.[OrderWarehouse],
D.[PriceLevel],
D.[PriceOverridden],
D.[ProductLine],
D.[PromiseDate],
D.[PurchaseOrderNo],
D.[PurchaseOrderRequiredDate],
D.[QuantityBackordered],
D.[QuantityOrdered],
D.[QuantityPerBill],
D.[QuantityShipped],
D.[Revision],
D.[SalesAcctKey],
D.[SkipPrintCompLine],
D.[StandardKitBill],
D.[SubjectToExemption],
D.[TaxAmt],
D.[TaxClass],
D.[TaxRate],
D.[TaxTypeApplied],
D.[UnitCost],
D.[UnitOfMeasure],
D.[UnitOfMeasureConvFactor],
D.[UnitPrice],
D.[Valuation],
D.[VendorNo],
D.[WarehouseCode],
D.[WarrantyCode],
H.InvoiceDate as InvoiceDate,
H.TransactionDate as TransactionDate,
H.InvoiceDate as Target_Date
FROM
{ IJ AR_InvoiceHistoryHeader H INNER JOIN AR_InvoiceHistoryDetail D ON
H.HeaderSeqNo = D.HeaderSeqNo AND H.InvoiceNo = D.InvoiceNo }
Load Replace Settings
Source Field =
InvoiceDate
Target Value or Constant =
DATEADD(DAY,-365,CONVERT(DATE, GetDate() ))