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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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

BOL data

We then took this the next step and added into a procedure to enable this data to be pulled in multiple programs…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
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

GETITEM_CP

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
* 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)

Test program

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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;

Had to make small modification to the SQL — needed to match revision from parent to child and control active dates for both.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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

pulls:
BOM

XA:

XA (MAPICS) SQL to pull 10 levels BOM

Leave a Reply

Your email address will not be published.