DECLARE
@p_NumberInternal AS NVARCHAR(50) = ' 12354',
@p_RevisionInternal AS NVARCHAR(255) = 'E00',
@p_BomNameLn AS NVARCHAR(255) = '',
@p_SiteCode AS NVARCHAR(10) = '100',
@p_RevisionLatest AS NVARCHAR(10) = '',
@p_bmdlLatest AS NVARCHAR(10) = '',
@p_bmrvLatest AS NVARCHAR(10) = '',
@p_IsLatestRevision AS BIT = 0,
@p_SystemDateCurrent AS DATETIME2(3) = GETDATE(),
@p_BomEffectiveIn AS DATETIME2(3) = GETDATE(),
@p_BomEffectiveOut AS DATETIME2(3) = GETDATE(),
@p_bmdl AS NVARCHAR(9),
@p_bmrv AS NVARCHAR(6),
@p_compnr AS BIGINT = 1000,
@p_IsEngineeringRevision AS BIT = 1;
SELECT
TOP 1 @p_BomEffectiveIn = DATEADD(SECOND, 1, item_rev.efdt),
@p_BomEffectiveOut = CASE WHEN item_rev.[exdt] = '1970-01-01 00:00:00.000' OR item_rev.[exdt] = '9999-12-31' THEN @p_SystemDateCurrent ELSE item_rev.[exdt] END,
@p_bmdl = item_rev.[bmdl],
@p_bmrv = item_rev.[bmrv],
@p_compnr = item_rev.[compnr]
FROM
[dbo].[tiedm100] AS eBom WITH(NOLOCK)
INNER JOIN [dbo].[timfc300] AS pBom WITH(NOLOCK)
ON pBom.[compnr] = eBom.[compnr]
AND pBom.[scei] = eBom.[eitm]
AND pBom.[scer] = eBom.[revi]
AND pBom.[bmsc_kw] = 'tibmsc.edm'
AND pBom.[bmsc] = '20'
INNER JOIN [dbo].[tibom300] AS item_rev WITH(NOLOCK)
ON item_rev.[compnr] = pBom.[compnr]
AND item_rev.[mitm] = pBom.[mitm]
AND item_rev.[scmi] = pBom.[mitm]
AND item_rev.[scrv] = pBom.[bmrv]
AND item_rev.[site] = @p_SiteCode
AND item_rev.[bmst] IN (20, 30)
WHERE
eBom.[compnr] = @p_compnr
AND
(
eBom.[eitm] = @p_NumberInternal
OR
eBom.[eitm] = RTRIM(LTRIM(@p_NumberInternal))
)
AND eBOM.[revi] = @p_RevisionInternal
ORDER BY
item_rev.[bmst];
SELECT
@p_BomEffectiveOut = CASE WHEN YEAR(@p_BomEffectiveOut) = 9999 THEN '9999-12-01 00:00:00' ELSE @p_BomEffectiveOut END
SELECT
@p_IsLatestRevision = CASE WHEN @p_RevisionLatest = 'E00' THEN 1 ELSE 0 END;
WITH RecursiveBOM(
ProductInternalPartNumber, ProductItem,
bmdl, bmrv, item, PartInternalPartNumber,
ProductCustomerRevision, QtyPer,
OperationSequenceNumber, RefDes,
Level, bom_seq, Phantom, ScrapFactor,
PositionSequenceNumber, ParentRowNumber,
ParentList, EngineeringRevision
) AS (
SELECT
LTRIM(RTRIM(item_rev.[mitm])) AS [ProductInternalPartNumber],
item_rev.[mitm] AS ProductItem,
item_rev.[bmdl] AS bmdl,
item_rev.[bmrv] AS bmrv,
bom.[sitm] AS item,
LTRIM(RTRIM(bom.[sitm])) AS PartInternalPartNumber,
eBom.[dsca] AS ProductCustomerRevision,
bom.[qana] AS QtyPer,
bom.[opno] AS OperationSequenceNumber,
'-' AS RefDes,
1 AS Level,
bom.pono AS bom_seq,
bom.[cpha] AS Phantom,
bom.[scpf] AS ScrapFactor,
bom.[pono] AS PositionSequenceNumber,
CAST((10000 + ROW_NUMBER() OVER (ORDER BY bom.[pono], bom.[sitm])) AS NVARCHAR(50)) AS ParentRowNumber,
CAST(LTRIM(RTRIM(item_rev.[mitm])) AS NVARCHAR(255)) AS ParentList,
eBom.[revi] AS EngineeringRevision
FROM
[dbo].[tibom300] AS item_rev WITH(NOLOCK)
LEFT JOIN [dbo].[timfc300] AS pBom WITH(NOLOCK)
ON item_rev.[compnr] = pBom.[compnr]
AND item_rev.[mitm] = pBom.[mitm]
AND item_rev.[scmi] = pBom.[mitm]
AND item_rev.[scrv] = pBom.[bmrv]
AND pBom.[bmsc_kw] = 'tibmsc.edm'
AND pBom.[bmsc] IN ('20', '30')
LEFT JOIN [dbo].[tiedm100] AS eBom WITH(NOLOCK)
ON pBom.[compnr] = eBom.[compnr]
AND pBom.[scei] = eBom.[eitm]
AND pBom.[scer] = eBom.[revi]
INNER JOIN [dbo].[tibom310] AS bom WITH(NOLOCK)
ON item_rev.[compnr] = bom.[compnr]
AND item_rev.[site] = bom.[site]
AND item_rev.[mitm] = bom.[mitm]
AND item_rev.[bmdl] = bom.[bmdl]
AND item_rev.[bmrv] = bom.[bmrv]
WHERE
item_rev.[compnr] = @p_compnr
AND
(
item_rev.[mitm] = @p_NumberInternal
OR
item_rev.[mitm] = RTRIM(LTRIM(@p_NumberInternal))
)
AND item_rev.[site] = @p_SiteCode
AND item_rev.[bmdl] = @p_bmdl
AND item_rev.[bmrv] = @p_bmrv
UNION ALL
SELECT
LTRIM(RTRIM(bom.[mitm])) AS [ProductInternalPartNumber],
bom.[mitm] AS [ProductItem],
bom.[bmdl] AS bmdl,
bom.[bmrv] AS bmrv,
bom.[sitm] AS item,
LTRIM(RTRIM(bom.[sitm])) AS PartInternalPartNumber,
bomParent.[dsca] AS ProductCustomerRevision,
bom.[qana] AS QtyPer,
bom.[opno] AS OperationSequenceNumber,
'-' AS RefDes,
Level + 1 AS Level,
bom.pono AS bom_seq,
bom.[cpha] AS Phantom,
bom.[scpf] AS ScrapFactor,
bom.[pono] AS PositionSequenceNumber,
CAST(bomParent.ParentRowNumber AS NVARCHAR(43)) + CAST('..' AS NVARCHAR(2)) + CAST((10000 + ROW_NUMBER() OVER (ORDER BY bom.[pono], bom.[sitm])) AS NVARCHAR(5)) AS ParentRowNumber,
CAST(ParentList + '..' + LTRIM(RTRIM(bom.[mitm])) AS NVARCHAR(255)) AS ParentList,
bomParent.[revi] AS EngineeringRevision
FROM
(
SELECT
item_rev.[compnr],
item_rev.[site],
item_rev.[mitm],
item_rev.[bmdl],
item_rev.[bmrv],
parent.[Level],
parent.[ParentRowNumber],
parent.[ParentList],
item_rev.[scmi],
item_rev.[scrv],
eBom.[revi],
eBom.[dsca],
ROW_NUMBER() OVER (
PARTITION BY item_rev.[compnr],
item_rev.[site],
item_rev.[mitm]
ORDER BY
item_rev.[compnr],
item_rev.[site],
item_rev.[mitm],
item_rev.[bmdl],
item_rev.[bmrv]
) AS RowNumber
FROM
RecursiveBOM AS parent
INNER JOIN [dbo].[tibom300] AS item_rev WITH(NOLOCK)
ON parent.[item] = item_rev.[mitm]
AND item_rev.[compnr] = @p_compnr
AND item_rev.[site] = @p_SiteCode
OUTER APPLY (
SELECT
eBom.[revi],
eBom.[dsca]
FROM
[dbo].[timfc300] AS pBom WITH(NOLOCK)
INNER JOIN [dbo].[tiedm100] AS eBom WITH(NOLOCK)
ON pBom.[compnr] = eBom.[compnr]
AND pBom.[scei] = eBom.[eitm]
AND pBom.[scer] = eBom.[revi]
WHERE
item_rev.[compnr] = pBom.[compnr]
AND item_rev.[mitm] = pBom.[mitm]
AND item_rev.[scmi] = pBom.[mitm]
AND item_rev.[scrv] = pBom.[bmrv]
AND pBom.[bmsc_kw] = 'tibmsc.edm'
AND pBom.[bmsc] = '20'
) AS eBom
WHERE
item_rev.[site] = @p_SiteCode
AND item_rev.[bmst] IN (20, 30)
AND
(
@p_BomEffectiveOut BETWEEN item_rev.[efdt]
AND
ISNULL(item_rev.[exdt], GETDATE())
)
) AS bomParent
INNER JOIN [dbo].[tibom310] AS bom WITH(NOLOCK)
ON bomParent.[compnr] = bom.[compnr]
AND bomParent.[site] = bom.[site]
AND bomParent.[mitm] = bom.[mitm]
AND bomParent.[bmdl] = bom.[bmdl]
AND bomParent.[bmrv] = bom.[bmrv]
WHERE
bomParent.[RowNumber] = 1
)
SELECT
bom.ProductInternalPartNumber,
CASE WHEN ISNULL(bom.bmrv, '') = '' THEN '-' ELSE LTRIM(RTRIM(bom.bmrv)) END AS [ProductInternalRevision],
bom.EngineeringRevision,
assy_cpn.aitc AS ProductCustomerPartNumber,
bom.ProductCustomerRevision AS ProductCustomerPartRevision,
bom.PartInternalPartNumber AS NumberInternal,
CASE WHEN ISNULL(part_rev.bmrv, '') = '' THEN '-' ELSE LTRIM(RTRIM(part_rev.bmrv)) END AS RevisionInternal,
part_rev.revi AS RevisionInternalEngineering,
bom.PartInternalPartNumber AS NumberCustomer,
ISNULL(part_rev.dsca, '-') AS RevisionCustomer,
CAST(bom.Level AS TINYINT) AS NodeLevel,
item.dsca AS Note,
CASE WHEN LTRIM(RTRIM(ISNULL(citg.dsca, ''))) = '' THEN '-' ELSE CONCAT(LTRIM(RTRIM(citg.citg)), ' - ', LTRIM(RTRIM(ISNULL(citg.dsca, '')))) END AS CustomCode,
'' AS PriceCode,
CASE WHEN (CASE WHEN ISNULL(CAST(item_master.envc_kw AS nvarchar(50)), '') = '' THEN '' ELSE REPLACE(item_master.envc_kw, 'tcenvc.', '') END) IN ('PASS', 'compliant') THEN 'ROHS' ELSE 'NA' END AS EnvironmentalSpecification,
CASE WHEN item.kitm = '30' THEN 'Manufactured Assembly' ELSE 'Part' END AS PartType,
ctype.dsca AS MaterialType,
sig.dsca AS PartReachCode,
bom.[QtyPer] AS Quantity,
bom.[QtyPer] AS QuantityFinal,
bom.[OperationSequenceNumber],
bom.[ParentRowNumber] AS BomSequenceNumber,
'*' AS AlternatePriority,
'' AS WorkCenter,
bom.ParentList AS ParentList,
CASE WHEN LTRIM(RTRIM(ISNULL(cpcp.dsca, ''))) = '' THEN '-' ELSE LTRIM(RTRIM(ISNULL(cpcp.dsca, ''))) END AS PartGroup,
bom.ProductItem,
bom.[item],
bom.[item] AS ItemChild,
bom.[bom_seq],
bom.[PositionSequenceNumber],
bom.[Phantom],
item.[cuni] AS Uom,
bom.bmdl,
bom.bmrv,
@p_compnr AS compnr
INTO #Bom
FROM
RecursiveBOM AS bom
LEFT JOIN [dbo].[tcibd001] AS item WITH(NOLOCK)
ON bom.[item] = item.[item]
AND item.[compnr] = @p_compnr
LEFT JOIN [dbo].[tcibd150] AS item_master WITH(NOLOCK)
ON item.[item] = item_master.[item]
AND item.[compnr] = item_master.[compnr]
AND item_master.[site] = @p_SiteCode
OUTER APPLY (
SELECT
TOP 1
part_rev.*,
eBom.[revi],
eBom.[dsca]
FROM
[dbo].[tibom300] AS part_rev WITH(NOLOCK)
LEFT JOIN [dbo].[timfc300] AS pBom WITH(NOLOCK)
ON part_rev.[compnr] = pBom.[compnr]
AND part_rev.[mitm] = pBom.[mitm]
AND part_rev.[scmi] = pBom.[mitm]
AND part_rev.[scrv] = pBom.[bmrv]
AND pBom.[bmsc_kw] = 'tibmsc.edm'
AND pBom.[bmsc] = '20'
LEFT JOIN [dbo].[tiedm100] AS eBom WITH(NOLOCK)
ON pBom.[compnr] = eBom.[compnr]
AND pBom.[scei] = eBom.[eitm]
AND pBom.[scer] = eBom.[revi]
WHERE
item.[compnr] = part_rev.[compnr]
AND item.[item] = part_rev.[mitm]
AND part_rev.[site] = @p_SiteCode
AND part_rev.[bmst] IN (20, 30)
AND
(
(0 = @p_IsLatestRevision AND @p_BomEffectiveOut BETWEEN part_rev.[efdt] AND (CASE WHEN ISNULL(part_rev.exdt, '1970-01-01 00:00:00.000') = '1970-01-01 00:00:00.000' THEN @p_SystemDateCurrent ELSE part_rev.exdt END))
OR
(1 = @p_IsLatestRevision AND CASE WHEN ISNULL(part_rev.exdt, '1970-01-01 00:00:00.000') = '1970-01-01 00:00:00.000' THEN @p_BomEffectiveOut ELSE part_rev.exdt END = @p_BomEffectiveOut)
)
ORDER BY
part_rev.[bmdl] DESC
,part_rev.[bmrv] DESC
) AS part_rev
LEFT JOIN [dbo].[tcmcs018] AS sig WITH(NOLOCK)
ON item_master.[compnr] = sig.[compnr]
AND item_master.csig = sig.[csig]
LEFT JOIN [dbo].[tcmcs023] AS citg WITH(NOLOCK)
ON item.[compnr] = citg.[compnr]
AND item.citg = citg.citg
LEFT JOIN [dbo].[tcmcs015] AS ctype WITH(NOLOCK)
ON item_master.[compnr] = ctype.[compnr]
AND item_master.ctyp = ctype.ctyp
LEFT JOIN [dbo].[tcmcs048] AS cpcp WITH(NOLOCK)
ON item.[compnr] = cpcp.[compnr]
AND item.cpcp = cpcp.cpcp
LEFT JOIN [dbo].[tcibd004] AS assy_cpn WITH(NOLOCK)
ON assy_cpn.[compnr] = @p_compnr
AND bom.[ProductItem] = assy_cpn.[item]
AND assy_cpn.[citt] = 'CUS'
ORDER BY
bom.[ParentRowNumber];
INSERT INTO #Bom (
ProductInternalPartNumber,
[ProductInternalRevision],
[EngineeringRevision],
ProductCustomerPartNumber,
ProductCustomerPartRevision,
NumberInternal,
RevisionInternal,
RevisionInternalEngineering,
NumberCustomer,
RevisionCustomer,
NodeLevel,
Note,
CustomCode,
PriceCode,
EnvironmentalSpecification,
PartType,
MaterialType,
PartReachCode,
Quantity,
QuantityFinal,
[OperationSequenceNumber],
BomSequenceNumber,
AlternatePriority,
WorkCenter,
ParentList,
PartGroup,
ProductItem,
[item],
[ItemChild],
[bom_seq],
[PositionSequenceNumber],
[Phantom],
[Uom])
SELECT
b.ProductInternalPartNumber,
b.[ProductInternalRevision],
b.[EngineeringRevision],
b.ProductCustomerPartNumber,
b.ProductCustomerPartRevision,
LTRIM(RTRIM(bomAlternate.aitm)) AS NumberInternal,
CASE WHEN ISNULL(part_rev.bmrv, '') = '' THEN '-' ELSE LTRIM(RTRIM(part_rev.bmrv)) END AS RevisionInternal,
part_rev.revi,
LTRIM(RTRIM(bomAlternate.aitm)) AS NumberCustomer,
ISNULL(part_rev.dsca, '-') AS RevisionCustomer,
b.NodeLevel,
b.Note,
b.CustomCode,
b.PriceCode,
b.EnvironmentalSpecification,
b.PartType,
b.MaterialType,
b.PartReachCode,
b.Quantity,
b.QuantityFinal,
b.[OperationSequenceNumber],
b.BomSequenceNumber,
bomAlternate.prio AS AlternatePriority,
b.WorkCenter,
b.ParentList,
b.PartGroup,
b.ProductItem,
b.[item],
bomAlternate.aitm,
b.[bom_seq],
b.[PositionSequenceNumber],
b.[Phantom],
b.[Uom]
FROM
#Bom AS b
INNER JOIN [dbo].[tibom320] AS bomAlternate WITH(NOLOCK)
ON b.compnr = bomAlternate.[compnr]
AND bomAlternate.[site] = @p_SiteCode
AND b.ProductItem = bomAlternate.[mitm]
AND b.[bom_seq] = bomAlternate.[pono]
AND b.[bmdl] = bomAlternate.[bmdl]
AND b.[bmrv] = bomAlternate.[bmrv]
OUTER APPLY (
SELECT
TOP 1
part_rev.*,
eBom.[revi],
eBom.[dsca]
FROM
[dbo].[tibom300] AS part_rev WITH(NOLOCK)
LEFT JOIN [dbo].[timfc300] AS pBom WITH(NOLOCK)
ON part_rev.[compnr] = pBom.[compnr]
AND part_rev.[mitm] = pBom.[mitm]
AND part_rev.[scmi] = pBom.[mitm]
AND part_rev.[scrv] = pBom.[bmrv]
AND pBom.[bmsc_kw] = 'tibmsc.edm'
AND pBom.[bmsc] = '20'
LEFT JOIN [dbo].[tiedm100] AS eBom WITH(NOLOCK)
ON pBom.[compnr] = eBom.[compnr]
AND pBom.[scei] = eBom.[eitm]
AND pBom.[scer] = eBom.[revi]
WHERE
bomAlternate.[compnr] = part_rev.[compnr]
AND bomAlternate.[aitm] = part_rev.[mitm]
AND part_rev.[site] = @p_SiteCode
AND part_rev.[bmst] IN (20, 30)
AND
(
(0 = @p_IsLatestRevision AND @p_BomEffectiveOut BETWEEN part_rev.[efdt] AND (CASE WHEN ISNULL(part_rev.exdt, '1970-01-01 00:00:00.000') = '1970-01-01 00:00:00.000' THEN @p_SystemDateCurrent ELSE part_rev.exdt END))
OR
( 1 = @p_IsLatestRevision AND CASE WHEN ISNULL(part_rev.exdt, '1970-01-01 00:00:00.000') = '1970-01-01 00:00:00.000' THEN @p_BomEffectiveOut ELSE part_rev.exdt END = @p_BomEffectiveOut)
)
ORDER BY
part_rev.[bmdl] DESC,
part_rev.[bmrv] DESC
) AS part_rev
SELECT
refDes.[compnr],
refDes.[site],
refDes.[bmdl],
refDes.[bmrv],
refDes.[mitm],
refDes.[pono],
refDes.[pono] AS seqn,
refDes.[lcid],
refDes.[qana]
INTO
#RefDes
FROM
(
SELECT
DISTINCT [ProductItem],
[bmdl],
[bmrv],
@p_SiteCode AS site,
@p_compnr AS compnr
FROM
#Bom
) AS bomMaster
INNER JOIN [dbo].[tibom330] AS refDes WITH(NOLOCK)
ON bomMaster.[site] = refDes.[site]
AND bomMaster.[ProductItem] = refDes.[mitm]
AND bomMaster.[bmdl] = refDes.[bmdl]
AND bomMaster.[bmrv] = refDes.[bmrv]
SELECT
DISTINCT
A.[compnr],
A.[site],
A.[mitm],
A.[bmdl],
A.[bmrv],
A.[pono],
A.[seqn],
STUFF(
(SELECT
',' + b.[lcid]
FROM
#RefDes AS b
WHERE
A.[compnr] = b.[compnr]
AND A.[site] = b.[site]
AND A.[bmdl] = b.[bmdl]
AND A.[bmrv] = b.[bmrv]
AND A.[mitm] = b.[mitm]
AND A.[pono] = b.[pono]
AND A.[seqn] = b.[seqn]
FOR XML PATH('')
),1,1,''
) AS ReferenceDesignator
INTO
#RefDesConcat
FROM
#RefDes AS A
SELECT
DISTINCT
mpn.[item] AS ItemChild,
LTRIM(RTRIM(mpn.[mpnr])) AS mpnr,
LTRIM(RTRIM(mpn.[cmnf])) AS cmnf
INTO
#MpnList
FROM
[dbo].[tdipu049] AS mpn
INNER JOIN [dbo].[tdipu045] AS mpn_stat WITH(NOLOCK)
ON mpn.[compnr] = mpn_stat.[compnr]
AND mpn.[cmnf] = mpn_stat.[cmnf]
AND mpn.[mpnr] = mpn_stat.[mpnr]
AND mpn_stat.[stat] = 1
AND
(
mpn_stat.[exdt] = '1970-01-01 00:00:00.000'
OR
mpn_stat.[exdt] > GETDATE()
)
WHERE
mpn.[compnr] = @p_compnr
AND mpn.[item] IN (SELECT
DISTINCT
ItemChild
FROM
#Bom)
SELECT
DISTINCT
A.[ItemChild],
STUFF(
(SELECT
'^^' + b.[cmnf] + '||' + b.[mpnr]
FROM
#MpnList AS b
WHERE
A.[ItemChild] = b.[ItemChild]
FOR XML PATH('')),1,2, '') AS MpnList
INTO
#MpnListConcat
FROM
#MpnList AS A
SELECT
A.ProductInternalPartNumber,
CASE WHEN @p_IsEngineeringRevision = 1 THEN A.EngineeringRevision ELSE A.ProductInternalRevision END AS ProductInternalRevision,
CASE WHEN ISNULL(A.ProductCustomerPartNumber, '') = '' THEN A.ProductInternalPartNumber ELSE A.ProductCustomerPartNumber END AS ProductCustomerPartNumber,
CASE WHEN ISNULL(A.ProductCustomerPartRevision, '') = '' THEN '-' ELSE A.ProductCustomerPartRevision END AS ProductCustomerPartRevision,
A.NumberInternal,
CASE WHEN @p_IsEngineeringRevision = 1 THEN A.RevisionInternalEngineering ELSE A.RevisionInternal END RevisionInternal,
A.NodeLevel,
A.Note,
A.CustomCode,
A.PriceCode,
A.EnvironmentalSpecification,
A.PartType,
A.MaterialType,
A.PartReachCode,
CAST(A.Quantity AS NVARCHAR(50)) AS Quantity,
CAST(A.QuantityFinal AS DECIMAL(10,3)) AS QuantityFinal,
A.BomSequenceNumber,
A.OperationSequenceNumber,
A.AlternatePriority,
A.WorkCenter,
A.ParentList,
A.PartGroup,
C.MpnList AS ManufacturerPartNumber,
B.ReferenceDesignator,
',' AS ReferenceDesignatorSeparator,
CAST(A.Phantom AS NVARCHAR(10)) AS Phantom,
CAST(A.PositionSequenceNumber AS INT) AS PositionSequenceNumber,
A.Uom AS UOM
FROM
#Bom AS A
LEFT JOIN #RefDesConcat AS B
ON A.[ProductItem] = B.[mitm]
AND A.[bom_seq] = B.[pono]
AND A.[PositionSequenceNumber] = B.[seqn]
LEFT JOIN #MpnListConcat AS C
ON A.[ItemChild] = C.[ItemChild]
ORDER BY
A.[BomSequenceNumber],A.[AlternatePriority],A.[NumberInternal]
IF OBJECT_ID('tempdb..#Bom') IS NOT NULL
DROP TABLE #Bom
IF OBJECT_ID('tempdb..#RefDes') IS NOT NULL
DROP TABLE #RefDes
IF OBJECT_ID('tempdb..#RefDesConcat') IS NOT NULL
DROP TABLE #RefDesConcat
IF OBJECT_ID('tempdb..#MpnList') IS NOT NULL
DROP TABLE #MpnList
IF OBJECT_ID('tempdb..#MpnListConcat') IS NOT NULL
DROP TABLE #MpnListConcat;