BOM Field Description:

id: BOM table PK. Each time a change order modifies a BOM task, a record will be added instead of directly modifying the original record

Item: parent item id, associated with item table

component: sub item id, associated item table

item_number: sub material code

flag: bom subitem status (currently only the 4th and 5th digits are valid). Bit 4: 0 means invalid, 1 means valid. Bit 5: 0 represents outdated and 1 represents latest.

prior_bom: if it is not empty, it means that the data in this row has changed another row of data, and the value is the BOM ID in another row

Change_in: the change order ID to which the sub material is added. If it is 0, it means it is added to the BOM before the change (initial state).

Change_out: the change order ID of the sub material to be deleted. When the value of this field is > 0, it means that the current sub line has been invalidated by this change order.

Quantity: sub material quantity, text field. Scores can be supported, such as 1 / 6

BOM comments: by id and agile_ Row of flex table_ The id field is associated with the query, and attid=1036 and class="10000"

find_number: find the number, which is not used at present

Scenario Description:

1. Change order C0095696, status: new, affected object: 90.01.002271
   1. Add a row of sub material 03.10.000488 in the parent material, and a row of record will be added in the BOM, flag=00000, change_in = ID of the change order, change_out = empty, prior_bom = empty
   2. Delete a row of sub material 06.06.034701 in the parent material, and a row of record will be added in the BOM, flag=00000, change_in = ID of the change order, change_out = ID of the change order, prior_bom = deleted data ID
   3. If one line of sub material 03.15.030311 is modified to 03.15.010001R in the parent material, a new line of record will be added to the BOM, flag=00000, change_in = ID of the change order, change_out = empty, prior_bom = deleted data ID
   4. If the quantity of one row of sub material 02.80.603667 is changed from 6 to 5 in the parent material, a new row of record will be added in the BOM, flag=00000, change_in = ID of the change order, change_out = empty, prior_bom = modified data ID
2. The status of change order C0095696 changes to: approved, and the affected object is 90.01.002271
   1. There is no change in the above data
3. Change order C0095696, the status changes to issued, and the affected objects are 90.01.002271
   1. Add a row of sub material 03.10.000488 in the parent material, then a row of record will be added in the BOM, and the flag will change to 00011, change_in is the change order ID, change_out is null, prior_bom is empty
   2. Delete a row of sub material 06.06.034701 from the parent material, and a new row of record will be added to the BOM. The flag is still 00000, change_in is the change order ID, change_out is the change order ID, prior_bom points to the deleted data ID; The deleted data flag changes from 00011 to 00010, change_out becomes the current change order ID
   3. If one line of sub material 03.15.030311 is modified to 03.15.010001R in the parent material, a new line of record will be added to the BOM, and the flag will change to 00011, change_in is the change order ID, change_out is null, prior_bom points to the deleted data ID; The deleted data flag changes from 00011 to 00010, change_out becomes the current change order ID
   4. If the quantity of one row of sub material 02.80.603667 is changed from 6 to 5 in the parent material, a new row of record will be added to the BOM, and the flag will change to 00011, change_in is the change order ID, change_out is null, prior_bom points to the modified data ID; The deleted data flag changes from 00011 to 00010, change_out becomes the current change order ID

Query the SQL of BOM change content according to the change order or affected item number

with e as (
 2     select c.change_number
 3     ,i.item_number as parent_item_number,i.description as parent_item_name
 4     ,case when (change_out > 0 and prior_bom > 0) then 2 else 1 end as op_type
 5     ,ci.description as item_name
 6     ,b.item_number,b.id,b.prior_bom,b.quantity as qty,b.FLAGS,b.created,b.last_upd,b.change_in,b.change_out
 7     ,f.text as remark
 8     ,decode(b.prior_bom,0,b.id,b.prior_bom) as order_id
 9     from AGILE.Change C
10     INNER JOIN AGILE.REV R ON R.CHANGE = C.ID
11     INNER JOIN AGILE.bom b on r.item = b.item and b.change_in = c.id
12     inner join AGILE.item i on r.item = i.id 
13     inner join AGILE.item ci on b.component = ci.id
14     left join agile.agile_flex f on b.id = f.row_id and f.class = 10000 and f.attid = 1036
15     LEFT JOIN AGILE.LISTENTRY LDELAY ON R.LIST01 = LDELAY.ENTRYID AND LDELAY.LANGID = 4
16     where 1=1 
17     ${if(trim(changeNumber) == '',""," and c.change_number = '"+changeNumber+"'")}
18     ${if(trim(pitemNumber) == '',""," and i.item_number = '"+pitemNumber+"'")}
19 )
20 select * from (
21     -- operation BOM
22     select e.* from e
23     union all
24     -- Manipulated BOM
25     select e.change_number
26     ,e.parent_item_number,e.parent_item_name
27     ,2 as op_type
28     ,ci.description as item_name
29     ,b.item_number,b.id,b.prior_bom,b.quantity as qty,b.FLAGS,b.created,b.last_upd,b.change_in,b.change_out
30     ,f.text as remark
31     ,decode(b.prior_bom,0,b.id,b.prior_bom) as order_id
32     from agile.bom b
33     inner join e on b.id = e.prior_bom 
34     inner join AGILE.item ci on b.component = ci.id
35     left join agile.agile_flex f on b.id = f.row_id and f.class = 10000 and f.attid = 1036
36     where e.change_in != e.change_out
37 ) t order by t.parent_item_number,t.change_number,t.order_id

Put the above SQL into the FineReport report report tool and develop the final presentation result of the report as shown in the figure below (important information has been mosaic):