Skip to Content

SAPB1 Import AR Invoices and cancel them via DTW

Hi All,

Is it possible to import AR invoices via DTW and cancel them? One of the customers want legacy sales history to be imported into SAPB1 for MRP. Not sure MRP will work with cancelled invoices anyway.

Thanks all

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Best Answer
    Oct 18, 2016 at 11:11 AM

    To import invoices you use the templates Documents and Documents_Lines under oInvoice object. To import them as paid/closed you also need to import the Incoming Payments and connect the Incoming Payment to the imported Invoice. This is the procedure in Business One and so is also the procedure via the DTW.

    Make sure G/L Account Determination is filled out CORRECTLY. Particularly that the Sales Account tab is filled out and the Checks Received account is setup as a Cash account.

    1. A/R invoices = oInvoices
      1. Documents
        1. Use a doctype of “S” for Service.
        2. Handwritten = tYES if you want to import invoices numbers from the old system. Uses the “Manual” numbering series.
      2. Document_lines
        1. Use the enum for the account code. Recommend setting up an opening balance offset accout. A/R from the BP will be the other half of the entry.
        2. Use item Description for you comments like “Open bal entry”
        3. Other columns used: LineTotal, Price, Quantity, Taxcode (use the exempt one, but one will be required).
    2. Payments = oIncomingPayments
      1. Payments
        1. Payment header file and you can also import cash amounts in the cashsum column ONLY if it is a cash payment.
        2. Leave the Bank account related columns blank if you are going to include a line in the Payment_Checks file for the line.
        3. The example shown in the templates for DTW is an example of Cash payments that are not applied to any invoice.
        4. SAP will not let you use a DocDate before the start of the fiscal year. Also, if you are applying the payment to a document, the payment doc date must be after the a/r invoice date.
        5. The DocNum in the payments file is the internal transaction number, not the Customer’s check number. That goes in the Payment_Checks file. You can put tYES in the handwritten column and then put whatever number you want in the DocNum column, otherwise leave them both blank and SAP will assign #’s to the incoming payment doc headers.
        6. Put a comment in the JournalRemarks if desired.
      2. Payment_Checks
        1. Use if the payments are checks. If they are cash fill in the cashsum line in the payments file and do NOT put a line in this file for that line.
        2. Fill in the BankCode column with the house bank code.
        3. CheckNumber for the customer’s check number they paid with.
        4. CheckSum is the total of the check.
      3. Payment_invoices
        1. Use the DocEntry column to enter the reference of the invoice number to apply the line to. To get this you have to query the OINV table. I imported my invoices and immediately ran the query to get the DocNum with the invoice number matched to the DocEntry and pasted that into Excel. Then used VLOOKUP() to prepare my Payment_invoice template.
        2. You don’t need to have an entry in this file for every payment, only if you want to have a particular payment applied to an open A/R Invoice. These are linked by the RecordKey like normal in DTW. Payment_checks without a corresponding Paymnet_invoice will be imported as payments on account.

    Hope it helps you.



    Add comment
    10|10000 characters needed characters exceeded