I needed a quick way to pull Bill of Materials (BOM) for a group of finished goods to help calculate turns of raw material.
I borrowed this from WIKI
A bill of materials or product structure (sometimes bill of material, BOM or associated list) is a list of the raw materials, sub-assemblies, intermediate assemblies, sub-components, parts, and the quantities of each needed to manufacture an end product.
SQL sample to pull 10 levels of Bill of Materials from XA table PSTDTL.
[cc lang=”php”]
with rpl (level, pinbr, cinbr, qtypr) as
(select 1, root.pinbr, root.cinbr, root.qtypr
from pstdtl root
where root.pinbr = ‘R42-16386’ and
root.pitr = ‘3’
union all
select parent.level+1, child.pinbr, child.cinbr,
child.qtypr
from rpl parent, pstdtl child
where parent.cinbr = child.pinbr and
parent.level <= 10)
select pinbr, level, cinbr, qtypr
from rpl
Example of pulled data
[/cc]
We then took this the next step and added into a procedure to enable this data to be pulled in multiple programs…
[cc lang=”php”]
H NOMAIN EXPROPTS(*RESDECPOS)
*—————————————————————
* PROGRAM – child
* PURPOSE – return a list of children
* WRITTEN – August 6th 2019
* AUTHOR – Jamie Flanary
*—————————————————————
/copy qprcsrc,child_cp
*
* Begin Procedure
*
p GetChildren…
P b export
* Procedure Interface
d GetChildren…
d pi 4100
d InItem 15 const
* Procedure variables
d arrayindex s 10i 0 inz
d ChildNotFound s n inz(‘0’)
d count s 5 0
d i s 10i 0 inz
d TotalChildren…
d s 4 0 inz
d MaxItemLines s 10i 0 Inz(500)
d RowCount s 10i 0 inz
d WorkItem s 15 inz
d WorkRevision s 6 inz
d c1 ds Dim(500) Qualified Inz
d Parent 15
d Level 2 0
d Child 15
d QuantityPer 9 3
/copy qprcsrc,GETITEM_CP
/free
workitem = InItem;
clear ChildListDS;
exsr $GatherChildren;
return ChildListDS.AllMyChildren;
//——————————————————–
// $GatherChildren
//——————————————————–
begsr $GatherChildren;
ItemInfoDS = ReturnItemInformation(WorkItem);
workrevision = ItemInfoDS.CurrentRevision;
exec sql
declare C1 scroll cursor for
WITH RPL (LEVEL, PINBR, CINBR, QTYPR) AS
(SELECT 1, roOT.PINBR, ROOT.CINBR, ROOT.QTYPR
FROM PSTDTL ROOT
WHERE ROOT.PINBR = :WorkItem and
ROOT.PITR = :WorkRevision
UNION ALL
SELECT PARENT.LEVEL+1, CHILD.PINBR, CHILD.CINBR,
CHILD.QTYPR
FROM RPL PARENT, PSTDTL CHILD
WHERE PARENT.CINBR = CHILD.PINBR and
PARENT.LEVEL <= 10)
SELECT PINBR, LEVEL, CINBR , QTYPR
FROM RPL;
exec sql open C1;
exec sql fetch first from C1 for :MaxItemLines rows into :C1;
exec sql get diagnostics :RowCount = ROW_COUNT;
Dow RowCount <> 0;
For I = 1 to RowCount;
childListDS.MyChildCount = RowCount;
ChildListDS.Parent(i) = c1(i).Parent;
ChildListDS.Level(i) = c1(i).Level;
ChildListDS.Child(i) = c1(i).Child;
ChildListDS.QuantityPer(i) = c1(i).QuantityPer;
EndFor;
exec sql fetch next from C1 for :MaxItemLines rows into :C1;
exec sql get diagnostics :RowCount = ROW_COUNT;
EndDo;
exec sql close C1;
endsr;
//——————————————————–
/end-free
p GetChildren…
p e
[/cc]
GETITEM_CP
[cc lang=”php”]
* What is passed in
d GetChildren…
d pr 4100
d InItem 15 const
d ChildListDS ds Qualified Inz
d AllMyChildren…
d 4104
d MyChildCount 4 0 overlay(AllMyChildren:1)
d MyItemSet 41 dim(100)
d overlay(AllMyChildren:*next)
d Parent 15 overlay(MyItemSet:*next)
d Level 2 0 overlay(MyItemSet:*next)
d Child 15 overlay(MyItemSet:*next)
d QuantityPer 9 3 overlay(MyItemSet:*next)
[/cc]
Test program
[cc lang=”php”]
H DFTACTGRP(*NO) OPTION(*SRCSTMT: *NODEBUGIO) BNDDIR(‘UTILITIES’)
/copy qprcsrc,CHILD_CP
d OutItem s 15 inz
/Free
//parent item
OutItem = ‘A107508L20558-1’;
ChildListDS = GetChildren(OutItem) ;
//child item
OutItem = ‘A107508L-VEN’;
ChildListDS = GetChildren(OutItem) ;
*inlr = *on;
[/cc]
Had to make small modification to the SQL — needed to match revision from parent to child and control active dates for both.
[cc lang=”php”]
with rpl (level,pinbr,cinbr,qtypr,pitr,edatm,edato,citr,alts) as
(select 1, root.pinbr, root.cinbr, root.qtypr,root.pitr
,root.edatm, root.edato , root.citr ,root.alts
from pstdtl root
where root.pinbr = ‘R30-17855M’ and
root.pitr = ‘—‘ and
1190806 >= root.EDATM and
( 1190806 <= root.EDATO or root.EDATO = 0)
union all
select parent.level+1, child.pinbr, child.cinbr,
child.qtypr, child.pitr ,child.edatm, child.edato, child.citr,
child.alts
from rpl parent, pstdtl child
where parent.cinbr = child.pinbr and child.Alts = ‘ ‘ and
parent.cITR = child.PITR and
1190806 >= child.EDATM and
( 1190806 <= child.EDATO or child.EDATO = 0) and
parent.level <= 10)
select pinbr, level, cinbr, qtypr, pitr,edatm, edato,citr
from rpl
[/cc]
pulls:
XA:
