/******************************************************************/
/* Get the Flat BOM with simple Query                             */
/* This example get the Current BOM Revision with Deepness of 5   */
/******************************************************************/

SELECT 
BUSINESS_UNIT,
ROOT_ITEM,
-- The coalesce instruction returns the first not null Element from the listed parameters.
COALESCE(Level5,Level4,Level3,Level2,Level1) AS Level,
COALESCE(INV_ITEM_ID5,INV_ITEM_ID4,INV_ITEM_ID3,INV_ITEM_ID2,INV_ITEM_ID1) AS ITEM_ID,
COALESCE(POS_NBR5,POS_NBR4,POS_NBR3,POS_NBR2,POS_NBR1) AS POS_NBR,
COALESCE(QTY_PER5,QTY_PER4,QTY_PER3,QTY_PER2,QTY_PER1) AS QTY_PER
FROM
(
-- Select all Leafs : Level5
SELECT DISTINCT
A.BUSINESS_UNIT,
A.INV_ITEM_ID as ROOT_ITEM,
CASE WHEN A.INV_ITEM_ID is null THEN null ELSE 1 END AS Level1,
A.COMPONENT_ID AS INV_ITEM_ID1,
A.POS_NBR AS POS_NBR1, 
A.QTY_PER AS QTY_PER1,
CASE WHEN B.INV_ITEM_ID is null THEN null ELSE 2 END AS Level2,
B.COMPONENT_ID AS INV_ITEM_ID2,
B.POS_NBR AS POS_NBR2, 
B.QTY_PER AS QTY_PER2,
CASE WHEN C.INV_ITEM_ID is null THEN null ELSE 3 END AS Level3,
C.COMPONENT_ID AS INV_ITEM_ID3,
C.POS_NBR AS POS_NBR3, 
C.QTY_PER AS QTY_PER3,
CASE WHEN D.INV_ITEM_ID is null THEN null ELSE 4 END AS Level4,
D.COMPONENT_ID AS INV_ITEM_ID4,
D.POS_NBR AS POS_NBR4, 
D.QTY_PER AS QTY_PER4,
CASE WHEN E.INV_ITEM_ID is null THEN null ELSE 5 END AS Level5,
E.COMPONENT_ID AS INV_ITEM_ID5,
E.POS_NBR AS POS_NBR5, 
E.QTY_PER AS QTY_PER5
FROM PS_EN_BOM_COMPS A
  LEFT OUTER JOIN PS_EN_BOM_COMPS B ON 
       A.BUSINESS_UNIT = B.BUSINESS_UNIT
   AND A.COMPONENT_ID = B.INV_ITEM_ID
   and A.BOM_STATE    = B.BOM_STATE
   and A.BOM_TYPE     = B.BOM_TYPE
   and A.BOM_CODE     = B.BOM_CODE
  LEFT OUTER JOIN PS_EN_BOM_COMPS C ON 
       C.BUSINESS_UNIT = B.BUSINESS_UNIT
   AND C.INV_ITEM_ID   = B.COMPONENT_ID
   and C.BOM_STATE    = B.BOM_STATE
   and C.BOM_TYPE     = B.BOM_TYPE
   and C.BOM_CODE     = B.BOM_CODE
  LEFT OUTER JOIN PS_EN_BOM_COMPS D ON 
       D.BUSINESS_UNIT = C.BUSINESS_UNIT
   AND D.INV_ITEM_ID   = C.COMPONENT_ID
   and D.BOM_STATE    = C.BOM_STATE
   and D.BOM_TYPE     = C.BOM_TYPE
   and D.BOM_CODE     = C.BOM_CODE
  LEFT OUTER JOIN PS_EN_BOM_COMPS E ON 
       E.BUSINESS_UNIT = D.BUSINESS_UNIT
   AND E.INV_ITEM_ID   = D.COMPONENT_ID
   and E.BOM_STATE    = D.BOM_STATE
   and E.BOM_TYPE     = D.BOM_TYPE
   and E.BOM_CODE     = D.BOM_CODE
WHERE A.BOM_STATE = 'PR'
  and A.BOM_TYPE  = 'PR'
  and A.BOM_CODE  = 1
  AND (getdate() BETWEEN A.DATE_IN_EFFECT AND A.DATE_OBSOLETE)
  AND (getdate() BETWEEN B.DATE_IN_EFFECT AND B.DATE_OBSOLETE)
  AND (getdate() BETWEEN C.DATE_IN_EFFECT AND C.DATE_OBSOLETE)
  AND (getdate() BETWEEN D.DATE_IN_EFFECT AND D.DATE_OBSOLETE)
  AND (getdate() BETWEEN E.DATE_IN_EFFECT AND E.DATE_OBSOLETE)
UNION
-- Select all Leafs : Level4
SELECT DISTINCT
A.BUSINESS_UNIT,
A.INV_ITEM_ID as ROOT_ITEM,
CASE WHEN A.INV_ITEM_ID is null THEN null ELSE 1 END AS Level1,
A.COMPONENT_ID AS INV_ITEM_ID1,
A.POS_NBR AS POS_NBR1, 
A.QTY_PER AS QTY_PER1,
CASE WHEN B.INV_ITEM_ID is null THEN null ELSE 2 END AS Level2,
B.COMPONENT_ID AS INV_ITEM_ID2,
B.POS_NBR AS POS_NBR2, 
B.QTY_PER AS QTY_PER2,
CASE WHEN C.INV_ITEM_ID is null THEN null ELSE 3 END AS Level3,
C.COMPONENT_ID AS INV_ITEM_ID3,
C.POS_NBR AS POS_NBR3, 
C.QTY_PER AS QTY_PER3,
CASE WHEN D.INV_ITEM_ID is null THEN null ELSE 4 END AS Level4,
D.COMPONENT_ID AS INV_ITEM_I4,
D.POS_NBR AS POS_NBR4, 
D.QTY_PER AS QTY_PER4,
NULL AS Level5,
NULL AS INV_ITEM_ID5,
NULL AS POS_NBR5, 
NULL AS QTY_PER5
FROM PS_EN_BOM_COMPS A
  LEFT OUTER JOIN PS_EN_BOM_COMPS B ON 
       A.BUSINESS_UNIT = B.BUSINESS_UNIT
   AND A.COMPONENT_ID = B.INV_ITEM_ID
   and A.BOM_STATE    = B.BOM_STATE
   and A.BOM_TYPE     = B.BOM_TYPE
   and A.BOM_CODE     = B.BOM_CODE
  LEFT OUTER JOIN PS_EN_BOM_COMPS C ON 
       C.BUSINESS_UNIT = B.BUSINESS_UNIT
   AND C.INV_ITEM_ID   = B.COMPONENT_ID
   and C.BOM_STATE    = B.BOM_STATE
   and C.BOM_TYPE     = B.BOM_TYPE
   and C.BOM_CODE     = B.BOM_CODE
  LEFT OUTER JOIN PS_EN_BOM_COMPS D ON 
       D.BUSINESS_UNIT = C.BUSINESS_UNIT
   AND D.INV_ITEM_ID   = C.COMPONENT_ID
   and D.BOM_STATE    = C.BOM_STATE
   and D.BOM_TYPE     = C.BOM_TYPE
   and D.BOM_CODE     = C.BOM_CODE
WHERE A.BOM_STATE = 'PR'
  and A.BOM_TYPE  = 'PR'
  and A.BOM_CODE  = 1
  AND (getdate() BETWEEN A.DATE_IN_EFFECT AND A.DATE_OBSOLETE)
  AND (getdate() BETWEEN B.DATE_IN_EFFECT AND B.DATE_OBSOLETE)
  AND (getdate() BETWEEN C.DATE_IN_EFFECT AND C.DATE_OBSOLETE)
  AND (getdate() BETWEEN D.DATE_IN_EFFECT AND D.DATE_OBSOLETE)
UNION
-- Select all Level3
SELECT DISTINCT
A.BUSINESS_UNIT,
A.INV_ITEM_ID as ROOT_ITEM,
CASE WHEN A.INV_ITEM_ID is null THEN null ELSE 1 END AS Level1,
A.COMPONENT_ID AS INV_ITEM_ID1,
A.POS_NBR AS POS_NBR1, 
A.QTY_PER AS QTY_PER1,
CASE WHEN B.INV_ITEM_ID is null THEN null ELSE 2 END AS Level2,
B.COMPONENT_ID AS INV_ITEM_ID2,
B.POS_NBR AS POS_NBR2, 
B.QTY_PER AS QTY_PER2,
CASE WHEN C.INV_ITEM_ID is null THEN null ELSE 3 END AS Level3,
C.COMPONENT_ID AS INV_ITEM_ID3,
C.POS_NBR AS POS_NBR3, 
C.QTY_PER AS QTY_PER3,
NULL AS Level4,
NULL AS INV_ITEM_ID4,
NULL AS POS_NBR4, 
NULL AS QTY_PER4,
NULL AS Level5,
NULL AS INV_ITEM_ID5,
NULL AS POS_NBR5, 
NULL AS QTY_PER5
FROM PS_EN_BOM_COMPS A
  LEFT OUTER JOIN PS_EN_BOM_COMPS B ON 
       A.BUSINESS_UNIT = B.BUSINESS_UNIT
   AND A.COMPONENT_ID = B.INV_ITEM_ID
   and A.BOM_STATE    = B.BOM_STATE
   and A.BOM_TYPE     = B.BOM_TYPE
   and A.BOM_CODE     = B.BOM_CODE
  LEFT OUTER JOIN PS_EN_BOM_COMPS C ON 
       C.BUSINESS_UNIT = B.BUSINESS_UNIT
   AND C.INV_ITEM_ID   = B.COMPONENT_ID
   and C.BOM_STATE    = B.BOM_STATE
   and C.BOM_TYPE     = B.BOM_TYPE
   and C.BOM_CODE     = B.BOM_CODE
WHERE A.BOM_STATE = 'PR'
  and A.BOM_TYPE  = 'PR'
  and A.BOM_CODE  = 1
  AND (getdate() BETWEEN A.DATE_IN_EFFECT AND A.DATE_OBSOLETE)
  AND (getdate() BETWEEN B.DATE_IN_EFFECT AND B.DATE_OBSOLETE)
  AND (getdate() BETWEEN C.DATE_IN_EFFECT AND C.DATE_OBSOLETE)
UNION
-- Select all Level2
SELECT DISTINCT
A.BUSINESS_UNIT,
A.INV_ITEM_ID as ROOT_ITEM,
CASE WHEN A.INV_ITEM_ID is null THEN null ELSE 1 END AS Level1,
A.COMPONENT_ID AS INV_ITEM_ID1,
A.POS_NBR AS POS_NBR1, 
A.QTY_PER AS QTY_PER1,
CASE WHEN B.INV_ITEM_ID is null THEN null ELSE 2 END AS Level2,
B.COMPONENT_ID AS INV_ITEM_ID2,
B.POS_NBR AS POS_NBR2, 
B.QTY_PER AS QTY_PER2,
NULL AS Level3,
NULL AS INV_ITEM_ID3,
NULL AS POS_NBR3, 
NULL AS QTY_PER3,
NULL AS Level4,
NULL AS INV_ITEM_ID4,
NULL AS POS_NBR4, 
NULL AS QTY_PER4,
NULL AS Level5,
NULL AS INV_ITEM_ID5,
NULL AS POS_NBR5, 
NULL AS QTY_PER5
FROM PS_EN_BOM_COMPS A
  LEFT OUTER JOIN PS_EN_BOM_COMPS B ON 
       A.BUSINESS_UNIT = B.BUSINESS_UNIT
   AND A.COMPONENT_ID = B.INV_ITEM_ID
   and A.BOM_STATE    = B.BOM_STATE
   and A.BOM_TYPE     = B.BOM_TYPE
   and A.BOM_CODE     = B.BOM_CODE
   AND (getdate() BETWEEN A.DATE_IN_EFFECT AND A.DATE_OBSOLETE)
   AND (getdate() BETWEEN B.DATE_IN_EFFECT AND B.DATE_OBSOLETE)
WHERE A.BOM_STATE = 'PR'
  and A.BOM_TYPE  = 'PR'
  and A.BOM_CODE  = 1
UNION
-- Select all Level1
SELECT DISTINCT
A.BUSINESS_UNIT,
A.INV_ITEM_ID as ROOT_ITEM,
CASE WHEN A.INV_ITEM_ID is null THEN null ELSE 1 END AS Level1,
A.COMPONENT_ID AS INV_ITEM_ID1,
A.POS_NBR AS POS_NBR1, 
A.QTY_PER AS QTY_PER1,
NULL AS Level2,
NULL AS INV_ITEM_ID2,
NULL AS POS_NBR2, 
NULL AS QTY_PER2,
NULL AS Level3,
NULL AS INV_ITEM_ID3,
NULL AS POS_NBR3, 
NULL AS QTY_PER3,
NULL AS Level4,
NULL AS INV_ITEM_ID4,
NULL AS POS_NBR4, 
NULL AS QTY_PER4,
NULL AS Level5,
NULL AS INV_ITEM_ID5,
NULL AS POS_NBR5, 
NULL AS QTY_PER5
FROM PS_EN_BOM_COMPS A
WHERE A.BOM_STATE = 'PR'
  and A.BOM_TYPE  = 'PR'
  and A.BOM_CODE  = 1
  AND (getdate() BETWEEN A.DATE_IN_EFFECT AND A.DATE_OBSOLETE)
) Z
WHERE Z.BUSINESS_UNIT = :1
  AND Z.ROOT_ITEM = :2
ORDER BY INV_ITEM_ID1,INV_ITEM_ID2,INV_ITEM_ID3,INV_ITEM_ID4,INV_ITEM_ID5