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 |
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:
XA: