Skip to Content
0
Oct 24, 2019 at 07:53 PM

Query Link from AR Invoice to Delivery to Sales Order

1150 Views

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]