Blog Pro de Jean-Baptiste HEREN

Notes d'un consultant Freelance en Informatique

To content | To menu | To search

Peoplesoft : Nomenclatures à plat en sql

Voici un petit exemple de code sql, permettant d'obtenir la nomenclature à plat telle qu'elle est affichée dans les écrans de consultation des nomenclatures de production.

Voici un petit exemple de code sql, permettant d'obtenir la nomenclature à plat telle qu'elle est affichée dans les écrans de consultation des nomenclatures de production.

Cette requête utilise la fonction coalesce(), qui permet d'obtenir le premier élément non nul d'une liste. Testé avec succès sous SQL server 2000 & 2005.


/******************************************************************/
/* 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
Jean-Baptiste Heren

Author: Jean-Baptiste Heren

Stay in touch with the latest news and subscribe to the RSS Feed about this category

Comments are closed



You Might Also Like

Finding one component location in the peoplesoft portal

Just replace the 'MY_COMPONENT_GBL' parameter in the following sql script. This works for MS SQl Server. You can add more levels by adding Outer Joins. [SQL] /* Where is my component in the portal */...

Continue reading

02_-_new_component_interface.png

Peoplesoft Component interface HOWTO

Here is a tutorial explaining how you can create a function containing one Component INterface, and make use of it from any peolplecode. Following example takes the PO Receipt Component to make...

Continue reading