BoM report in QV

Mon, 02 Jun 2014 20:41:58 GMT

Hi.

I have created a recursive sql query for creating a BoM report.

Tryied to create a Hierarchy but dident succeeded

Any tipps ?

WITH SumBoM(LEVEL,PRODUCT, PART,SUBPART,QTY,SEQ, OP) AS

(

SELECT 1, ROOT.PMPRNO, ROOT.PMPRNO, ROOT.PMMTNO,ROOT.PMCNQT,

CAST(ROOT.PMMSEQ AS VARCHAR(70)) AS PMMSEQ, ROOT.PMOPNO

FROM MPDMAT ROOT

WHERE ROOT.PMCONO=999 AND ROOT.PMFACI='AAA' AND ROOT.PMPRNO='1111'

AND ROOT.PMSTRT='999'

UNION ALL

SELECT (PARENT.LEVEL + 1), PARENT.PRODUCT, CHILD.PMPRNO, CHILD.PMMTNO,

CHILD.PMCNQT,CAST((CAST (PARENT.SEQ as VARCHAR(30))+ '.' + cast(CHILD.PMMSEQ as VARCHAR(30)) ) AS VARCHAR(70)), CHILD.PMOPNO

FROM SumBoM PARENT, MPDMAT CHILD

WHERE PARENT.SUBPART = CHILD.PMPRNO AND CHILD.PMCONO=999 AND

CHILD.PMFACI='AAA' AND CHILD.PMSTRT='999' AND PARENT.LEVEL < 20

)

SELECT (ROW_NUMBER() OVER(ORDER BY SEQ)) AS SEQNO,

--RIGHT('......' + LEVEL, LEVEL) AS LEVEL ,PRODUCT,

RIGHT( LEVEL, LEVEL) AS LEVEL ,PRODUCT,

PRODITM.MMITDS AS PRODNAME,PART, OP,

PARTITM.MMITDS AS PARTNAME, SUBPART, SUBITM.MMITDS AS SUBNAME, QTY

FROM SumBoM

Re: BoM report in QV

Mon, 02 Jun 2014 21:21:10 GMT

Mikael.

have you tryed Hierarchy() and HierarchyBelongsTo() clauses?

reg

wgonzalez — Fri, 02 Sep 2022 17:38:43 GMT

Where you able to solve it? If so, could you share it?