Skip to main content
Skip table of contents

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() ))


image-20240724-014833.png

Extract SQL Statement and Load panels for example.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.