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
16
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
97
98
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
16
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

This site uses Akismet to reduce spam. Learn how your comment data is processed.