Skip to Content

Query Link from AR Invoice to Delivery to Sales Order

Hello,

I have started a query (in the Query generator) for a report I need that pulls most of the info from OINV and INV1. I also need to grab a date from both the Sales Order (ORDR) and the Delivery (ODLN). Every attempt that I make to link to these two files is unsuccessful. A second problem I have is in selecting records by date. Whenever I include a '%' in the query I get the following error message:

"[Microsoft][SQL Server Native Client 11.0][SQL Server]Conversion failed when converting date and/or time from character string. 'Blanket Agreement' (OOAT)"

Any help in linking the files and solving the selection issue would be appreciated. The query follows:

SELECT

---> Fields 1-9 - Seller Info:
'MFR223' '1-Seller Number', T4.[PrintHeadr] '2-Seller Name', T5.[Street] '3-Seller Add Line1', T5.[Block] '4-Seller Add Line 2', T5.[City] '5-Seller City', T5.[State] '6-Seller State/Prov', T5.[ZipCode] '7-Seller Postal Code',
CASE WHEN T5.[Country]='US' THEN 'USA' WHEN T5.[Country]='CA' THEN 'CAN' ELSE T5.[Country] END '8-Seller Country',T5.[GlblLocNum] '9-Seller GLN',

---> Fields 10-16 - Shipped From Info:
T3.[Street] '10-Seller Ship From Add1', T3.[Block] '11-Seller Ship From Add2', T3.[City] '12-Seller Ship From City', T3.[State] '13-Seller Ship From State/Province', T3.[ZipCode] '14-Seller Ship From Postal Code',
CASE WHEN T3.[Country]='US' THEN 'USA' WHEN T3.[Country]='CA' THEN 'CAN' ELSE T3.[Country] END '15-Seller Ship From Country',T3.[GlblLocNum] '16-Seller Ship From GLN',

---> Fields 17-25 - Shipped To Info:
T1.[CardCode] '17-Buyer No)', T1.[ShipToCode] '18-Buyer Ship to Name', T2.[StreetS] '19-Buyer Ship to Addr1', T2.[BlockS] '20-Buyer Ship to Add2', T2.[CityS] '21-Buyer Ship to City', T2.[StateS] '22-Buyer Ship to State/Prov.', T2.[ZipCodeS] '23-Buyer Ship to Postal Code',
CASE WHEN T2.[CountryS]='US' THEN 'USA' WHEN T2.[CountryS]='CA' THEN 'CAN' ELSE T2.[CountryS] END '24-Buyer Ship to Country', T2.[GlbLocNumS] '25-Buyer Ship to GLN',

---> Fields 26-31 - Billed To Info:
T2.[StreetB] '26-Buyer Bill to Addr1', T2.[BlockB] '27-Buyer Bill to Addr2', T2.[CityB] '28-Buyer Bill to City', T2.[StateB] '29-Buyer Bill to State/Prov.', T2.[ZipCodeB] '30-Buyer Bill to Postal Code',
CASE WHEN T2.[CountryB]='US' THEN 'USA' WHEN T2.[CountryB]='CA' THEN 'CAN' ELSE T2.[CountryB] END '31-Buyer Bill to Country',

---> Fields 32-38 - Invoice Header Info:
T1.[DocNum] '32-Invoice Number', CONVERT(varchar, T1.[TaxDate],101) '33-Invoice Date', CONVERT(varchar, T1.[DocDueDate],101) as '*34-Need ODLN>DocDueDate (Ship Date)',
T1.[NumAtCard] AS '35-PO Number', 'xx/xx/20xx' AS '*36-Need ORDR>TaxDate (PO Date)',
CASE WHEN T0.[Currency]='$' THEN 'USD' WHEN T0.[Currency]='CAD' THEN 'CAN' ELSE T0.[Currency] END '37-Currency' ,
CASE WHEN T1.[U_acDeliveryMode]='H' THEN 'Y' ELSE 'N' END '38-Backhaul Flag',

---> Fields 39-56 - Invoice Detail Info:
T0.[LineNum]+1 '39-Line No', T0.[Dscription] '40-Product Name', T0.[ItemCode] '41-Product Code', T0.[U_acGTIN] '42-GTIN', '8' '43-Pack Quantity', '80 OZ' '44-Pack Size', 'Subway' '45-Product Brand', 'Mr. Chips' '46-Manufacturer', T0.[ItemCode] '47-Mfg Product Code ',
CONCAT(T3.[Address2],'/',T3.[City],',',T3.[State]) '48-Mfg.Supply Loc.', T3.[GlblLocNum] '49-Supply Plant GLN',
FORMAT(T0.[Quantity],'#####.####') '50-Quantity', T0.[UomCode] '51-Quantity UOM', ' ' '52-Del Rate', ' ' '53-Frt. Allow',FORMAT(T0.[Price]+T0.U_acSOUMItemFrt,'###.0000') '*54-Net Price?', T0.[UomCode] '55-Net Price UOM',
CASE WHEN T0.[Currency]='$' THEN FORMAT(T0.GTotal+T0.U_acSOItemFrt,'#.00') ELSE FORMAT(T0.GTotalFC+T0.U_acSOItemFrt,'#.00') END '*56-Extended Cost***?'

---> Data Source:
FROM INV1 T0 INNER JOIN OINV T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN INV12 T2 ON T1.[DocEntry] = T2.[DocEntry] INNER JOIN OWHS T3 ON T0.[WhsCode] = T3.[WhsCode], OADM T4, ADM1 T5

---> Conditions:
WHERE
T1.[DocType] = 'I' AND (T1.[CardCode] = 'C0072' OR T1.[CardCode] = 'C0073') AND
CANCELED = 'N' AND
(T1.[DocDate] >= '01/01/2018' AND T1.[DocDate] <= '12/31/2019' )
-- This does not work-->> can't even include the percent sign in this comment.>>> (T1.[DocDate] >= '[pct0]' AND T1.[DocDate] <= '[pct1]' )

---> Sort Order
ORDER BY T1.[DocNum], T0.[LineNum]

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

6 Answers

  • Posted on Oct 25, 2019 at 02:56 AM

    Hi David Goldstein,

    I had a quick look at your entire query. Few changes are suggested, as described under.

    • Referring to ---> Fields 32-38 - Invoice Header Info: Please use `convert 101` format for all date fields consistently and re-try. (TaxDate, DocDueDate, etc.)
    • Referring to --> Conditions Replace "T1.[DocDate] >= '[pct0]' AND T1.[DocDate] <= '[pct1]'" with "T1.[DocDate] >=[%0] AND T1.[DocDate] <=[%1]". Remove double quotes when adding within your query.

    Please keep us posted of any new updates, so we can address your question.

    Best Wishes & Regards,

    Sam

    Add a comment
    10|10000 characters needed characters exceeded

    • Thanks Sam, but did not work.

      1st section - Fields 32-38 - both date fields referenced are using the 'convert 101' format. The other date reference is a comments until I can figure out how to link to the Delivery to retrieve it and link to the sales order to retrieve that date as well.

      2nd Section - Conditions - I've tried single quotes, no quotes and double quotes. I get the error message every time I have the % symbol in the query - that's why I have the characters "pct", so that I could run the query without the date selection causing the error message.

  • Posted on Oct 29, 2019 at 04:20 PM

    HI,

    Until now I have never used [pct1] in the query, I only used it in a comment line as a reminder to myself as to what I was attempting to do. I will test that and report results below.

    To date, we have not used any of the blanket agreement functions in SAP, which makes it more confusing as to why the error message would refer to Blanket Agreement. It is unlikely that Blanket Agreement agreement refers to any of our UDT's, but I will also test that.

    Test results:

    (1) - Hard Coded dates - Code:

    ---> Conditions section:

    WHERE T1.[DocType] = 'I' AND (T1.[CardCode] = 'C0072' OR T1.[CardCode] = 'C0073') AND CANCELED = 'N' AND (T1.[DocDate] >= '01/01/2018' AND T1.[DocDate] <= '12/31/2019' )

    Results from System Messages Log: Operation completed successfully [Message 200-48]

    ------------------------------

    (2) - used [%0] and [%1] in query instead of hard coded dates - Code:

    ---> Conditions section:

    WHERE T1.[DocType] = 'I' AND (T1.[CardCode] = 'C0072' OR T1.[CardCode] = 'C0073') AND CANCELED = 'N' AND (T1.[DocDate] >= [%0] AND T1.[DocDate] <= [%1] )

    Results from System Messages Log: 1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'T1'. 2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement 'Blanket Agreement' (OOAT) (s) could not be prepared.

    ------------------------------

    (3) - used [pct0] and [pct1] in query instead of [%0] anf [%1] instead of hard coded dates- Code:

    ---> Conditions Section:

    WHERE T1.[DocType] = 'I' AND (T1.[CardCode] = 'C0072' OR T1.[CardCode] = 'C0073') AND CANCELED = 'N' AND (T1.[DocDate] >= [pct0] AND T1.[DocDate] <= [pct1] )

    Results from System Messages Log: 1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'pct0'. 2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'pct1'. 3). [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement 'Blanket Agre

    ------------------------------

    With further testing and changes, I was able to get *most* of the query to work, but some of the changes are not acceptable.

    There is some issue with references to the file OINV. Here are the changes I made to the query:

    ------------------------------

    (1) To make it easier to test by removing one field at a time, after determining that OINV was the culprit, I placed each reference to OINV (T1.) on a line by itself.

    ------------------------------

    (2) In the section marked Fields 17-25, I had to delete the blank space to the left of T1.[ShipToCode] (Field #18)

    ------------------------------

    (3) I removed the CONVERT command from T1.TaxDate (Field #33) (This is not acceptable as I need the date formatted as "mm/dd/yyyy"

    ------------------------------

    (4) I removed the CONVERT command from T1.DocDueDate (Field #34) (This may be irrelevant as I need DocDueDate from file ODLN, which I don't yet know how to retrieve. It also must be formatted as "mm/dd/yyyy")

    ------------------------------

    (5) I removed the CASE command from T1.[U_acDeliveryMode] (Field #38) (This field is a UDF)

    ------------------------------

    Testing involved removing fields one at a time until the query would work, then adding them back until I found the lines that were causing the errors.

    ------------------ See the Before & After screenshots------------------


    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 28, 2019 at 12:40 AM

    Hi David Goldstein,

    Regarding your comments in 1st section: noted.

    Regarding your comments in 2nd section: Please refer screenshots below that illustrates the use of % symbol within a query in SAP Business One. You could try same query in your Business One and check if you get any error message.

    Query: SELECT T0.[DocDate] FROM OINV T0 WHERE T0.[DocDate] =[%0]

    Screenshots:

    If you could share details of error messages you are receiving it will be helpful to understand root cause.

    Best Wishes,

    Sam


    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 29, 2019 at 03:02 AM

    Hi David Goldstein,

    Thank you for detailed screenshots; it helps to understand current issue at hand.

    Do you have a screenshot when you use [pct1] in your query? Just wish to know if using [pct1] generates Blanket Agreement error or any other error.

    Could Blanket Agreement error relate to any of UDTs used in your query? It might be worth trying to remove UDTs and then check if query executes without any error.

    It is good to know your query executes successfully if you have fixed values hardcoded within query.

    I think, we are moving in right direction although there are several trouble-shooting stages in between.

    Best wishes,

    Sam

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 31, 2019 at 12:55 AM

    Hi,

    Thank you for your sharing your test results in detail. Some of your test hacks are good.

    About blanket agreements in SAP Business One (Business One), it seems if a Sales or Purchasing blanket agreement is existing then Business One links it automatically with any Sales or Purchase documents created for vendors, customers described within blanket agreement.

    Even though Draft blanket agreements exist Business One tends to link them automatically with any Sales or Purchase documents.

    My next thought was, what if all blanket agreements are removed from system? That way, Business One can bypass them automatically and thus error related to OOAT table (for Blanket Agreement) may not appear.

    'Find' mode within Sales or Purchasing Blanket Agreement may or may not show any of Blanket Agreements created within Business One.

    (Tip for DB Experts: 'Select * from OOAT' does show any Draft or Open Blanket Agreements existing within SAP Business One. This operation is Not recommended for users as it may violate SAP TOC and any Maintenance Agreement).

    I was hoping to disable/deactivate Blanket Agreements for this test and bypass automatic linking of blanket agreements with sales or purchasing documents. I am not sure whether Limiting user-specific General Authorizations to blanket agreements may or may not help in bypassing blanket agreements.

    It seems, we are moving forward one step at a time, which helps to get clarity at each stage.

    Thank you again for your testing efforts and sharing with all of us here, Much appreciated.

    Best Wishes,

    Sam

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 31, 2019 at 02:45 AM

    Thanks for continuing to trouble shoot with me. I can confirm that there are no blanket agreements (Sales or Purchase) in the system. I can't remove auth for them for myself, as I am set up as superuser. I am however, NOT a DB expert.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.