Peoplesoft : Nomenclatures à plat en sql
Posted on Wednesday 04 June 2008, 11:40 - Peoplesoft - Permalink Tweet
- Article
- |
- Comments(0)
- |
- Attachments(1)
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