SELECT
a.*
FROM
(
SELECT
soLine.orno AS NumberOrder,
CAST (soLine.pono AS INT) AS NumberPosition,
CAST (soLine.sqnb AS INT) AS NumberSequence,
soLine.ddta AS ShippingDate,
soLine.stbp AS CodeBusinessPartner,
soLine.stad AS CodeShipTo,
soLine.stcn AS CodeContactPerson,
so.ccur AS Currency,
CAST(soLine.pric AS FLOAT) AS UnitPrice,
CAST (soLine.cuva AS FLOAT) AS CustomsValue,
RTRIM(LTRIM(soLine.item)) AS NumberPart,
CASE WHEN RTRIM(LTRIM(ISNULL(soLine.revi, ''))) = '' THEN '-' ELSE RTRIM(LTRIM(soLine.revi)) END AS RevisionInternal,
so.corn AS NumberCustomerPurchaseOrder,
CAST(soLine.qidl AS FLOAT) AS ShippedQuantity,
CASE WHEN soLine.qidl > 0 THEN CAST (soLine.qbbo AS FLOAT) ELSE CAST((soLine.qoor - soLine.qidl) AS FLOAT) END As BalqtyToShip,
cpn.[aitc] AS CustomerPartNumber
FROM
[dbo].[tdsls401] AS soLine WITH(NOLOCK)
INNER JOIN [dbo].[tdsls400] AS so WITH(NOLOCK)
ON soLine.[compnr] = so.[compnr]
AND soLine.[orno] = so.[orno]
AND soLine.[site] = so.[site]
LEFT JOIN [dbo].[tcibd004] AS cpn WITH(NOLOCK)
ON soLine.[compnr] = cpn.[compnr]
AND so.[ofbp] = cpn.[bpid]
AND cpn.[citt] = 'CUS'
AND soLine.[item] = cpn.[item]
WHERE
soLine.[compnr] = '1000'
AND so.[site] = '100'
AND (
(
1 = CASE WHEN ISNULL('1002132', '') = '' THEN 1 ELSE 0 END
OR
soLine.[orno] = '1002132'
)
AND
(
1 = CASE WHEN ISNULL('10', 0) = '0' THEN 1 ELSE 0 END
OR
soLine.[pono] = '10'
)
AND
(
1 = CASE WHEN ISNULL('-1', -1) = '-1' THEN 1 ELSE 0 END
OR
soLine.[sqnb] = '-1'
)
AND
(
1 = CASE WHEN ISNULL('', '') = '' THEN 1 ELSE 0 END
OR
(
soLine.[item] = ''
OR
soLine.[item] = ''
)
)
AND (
1 = CASE WHEN ISNULL('', '') = '' THEN 1 ELSE 0 END
OR
soLine.[revi] = ''
)
)
OR (
1 = CASE WHEN 0 = 1 THEN 1 ELSE 0 END
AND (
(
soLine.clyn = 2
AND soLine.lseq = 0
AND so.sotp <> 'SC2'
AND soLine.qoor > soLine.qidl
)
OR (
soLine.clyn = 2
AND soLine.lseq = 0
AND so.sotp <> 'SC2'
AND soLine.qoor = soLine.qidl
and month(soLine.odat) = month(
GETDATE()
)
and year(soLine.odat) = year(
GETDATE()
)
and day(soLine.odat) = day(
GETDATE()
)
)
)
)
UNION ALL
SELECT
so.[cmso] AS NumberOrder,
CAST(soLine.[lino] AS INT) AS NumberPosition,
CAST(1 AS INT) AS NumberSequence,
soLine.pdtm AS ShippingDate,
soLine.stbp AS CodeBusinessPartner,
soLine.stad AS CodeShipTo,
soLine.stcn AS CodeContactPerson,
so.ccur AS Currency,
CAST(soLine.pris AS FLOAT) AS UnitPrice,
CAST(soLine.csvl AS FLOAT) AS CustomsValue,
RTRIM(LTRIM(soLine.item)) AS NumberPart,
CASE WHEN RTRIM(LTRIM(ISNULL(soLine.revi, ''))) = '' THEN '-' ELSE RTRIM(LTRIM(soLine.revi)) END AS RevisionInternal,
so.corn AS NumberCustomerPurchaseOrder,
CAST(soLine.dqty AS FLOAT) AS ShippedQuantity,
CAST((soLine.rqty - soLine.dqty) AS FLOAT) AS BalqtyToShip,
cpn.[aitc] AS CustomerPartNumber
FROM
[dbo].[tsmsc100] AS so WITH(NOLOCK)
INNER JOIN [dbo].[tsmsc110] AS soLine WITH(NOLOCK)
ON so.[compnr] = soLine.[compnr]
AND so.[cmso] = soLine.[cmso]
AND
(
soLine.[dste] = '100'
OR soLine.[rste] = '100'
)
LEFT JOIN [dbo].[tcibd004] AS cpn WITH(NOLOCK)
ON so.[compnr] = cpn.[compnr]
AND so.[ofbp] = cpn.[bpid]
AND cpn.[citt] = 'CUS'
AND soLine.[item] = cpn.[item]
WHERE
(
1 = CASE WHEN ISNULL('1002132', '') = '' THEN 1 ELSE 0 END
OR so.[cmso] = '1002132'
)
AND (
1 = CASE WHEN ISNULL('10', 0) = '0' THEN 1 ELSE 0 END
OR soLine.[lino] = '10'
)
AND (
1 = CASE WHEN ISNULL('', '') = '' THEN 1 ELSE 0 END
OR (
soLine.[item] = ''
OR soLine.[item] = ''
)
)
AND (
1 = CASE WHEN ISNULL('', '') = '' THEN 1 ELSE 0 END
OR soLine.[revi] = ''
)
) AS a
WHERE
(
1 = CASE WHEN ISNULL('1002132', '') = '' THEN 1 ELSE 0 END
OR a.NumberOrder = '1002132'
)
AND (
1 = CASE WHEN ISNULL('', '') = '' THEN 1 ELSE 0 END
OR a.NumberPart = LTRIM(RTRIM(''))
)
AND (
1 = CASE WHEN ISNULL('', '') = '' THEN 1 ELSE 0 END
OR a.RevisionInternal = ''
)