[原创]Oracle ERP展开BOM结构树的SQL

这是俺原创的，速度很快，写个循环就可以导出多个编码的BOM树，使用时把SQL中的“组织ID”换成自己EBS的实际ID。在Oracle EBS 11i 10.0版本下测试通过！ITPUB个人空间J Mq7z-eSF
ITPUB个人空间*fRW\C_(l^af7P
初次写这种语句，好用支持一下！
M m~6u+Q4jP"fG0

SELECT distinct
wdw}y VR0G0       bb.BOM层次,
PNc*RzI9Q3F0       bb.装配件,
N}/^1}2W;M!Kjc0       bb.装配件说明,
4F
J9WL)iY@2q5k
w\'^0       bb.组件序号,ITPUB个人空间Z3W6H1yM w,g`!@ r.f
       bb.组件,
`%Y0W"U"jWn6s0       bb.组件说明,
4OTi([\2z8C:z!@0       bb.组件用量,ITPUB个人空间AV.q j7e
       bb.单位,ITPUB个人空间4` nr-Al+_`R3V
       bb.利用率,
4btAV$A0       bb.冲减库房,ITPUB个人空间Bh0y4S*FJ2j!W
       bb.冲减货位,ITPUB个人空间W*v|8N(S0Kml0S
       bb.属性,
u h@ M_$~9b_A| ]0       bb.BOM创建时间,
Cy9JT:J[0       BOR.COMPLETION_SUBINVENTORY 入库,
*yU/Gv)r9~0R0       MSI.WIP_SUPPLY_SUBINVENTORY 冲减,
7|Dxj PfZS7E0       MSI.MAXIMUM_ORDER_QUANTITY  最大批量,ITPUB个人空间JDE&f&J?6s#|
       MSI.LEAD_TIME_LOT_SIZE      提前期,
6X4{ bPW:M0       MSI.FULL_LEAD_TIME          制造周期,
C
pI6b.@7qc/F
Z0       MSI.FIXED_LOT_MULTIPLIER    固定增加,
M:Y2cL8V:TP0       MSI.MINIMUM_ORDER_QUANTITY  最小批量,ITPUB个人空间2r2O!t*A"x;?fq;fLV
       MSI.FIXED_ORDER_QUANTITY    固定定货量,
O8QVPN0       MSI.FIXED_DAYS_SUPPLY       固定天数,
Z!|)c0l3@+h7i(U0       MSI.PLANNER_CODE            计划员ITPUB个人空间9A"I*a8]r"^ K
  FROM MTL_SYSTEM_ITEMS          MSI,ITPUB个人空间1S*g;P+f+^.KI3X+k.|6mU z-m
       BOM_OPERATIONAL_ROUTINGS  BOR,ITPUB个人空间 T,I0B+_si0z/d
       BOM_OPERATION_SEQUENCES   BOS,ITPUB个人空间.N4D1t4K"`,?!vt"M,d
       (select distinctITPUB个人空间7Su,h+x$W9pb#b'K
               aa.lvl                       BOM层次,
1T3_}"o4nS,{0               msi.segment1                 装配件,ITPUB个人空间!\)M+O}.m.q
               msi.description              装配件说明,ITPUB个人空间&k'] pjU0P{H
               aa.item_num                  组件序号,ITPUB个人空间@\m)Ces&b
               msi1.segment1                组件,ITPUB个人空间TF#t KI.u1G#CF/X
               msi1.description             组件说明,
Km*}W4X\X2O7n0               aa.component_quantity        组件用量,
+w K7hu_+v{t0               msi1.primary_unit_of_measure 单位,ITPUB个人空间3Y(P|vz3E/`
               aa.COMPONENT_YIELD_FACTOR    利用率,
B,E$Q)B!g)gOS.H0               msi1.wip_supply_subinventory 冲减库房,
BC5Zwy E0               mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 || '.' ||
2O$Q%k.QS'ng0               mil.segment4                 冲减货位,
~z9a R5z&{%B0               decode(msi1.planning_make_buy_code,1,'制造',2,'采购') 属性,ITPUB个人空间.JM?Q3c
               to_char(msi1.creation_date,'yyyy.mm.dd hh24:mm.ss')   BOM创建时间,ITPUB个人空间#{ja&X$`/]W
               msi1.inventory_item_id
2wF D
W }~ ]^.^0                ITPUB个人空间L:T;K6Q2{7i;QpJA
        from mtl_system_items_b       msi,
kf9B3OGrs0             mtl_system_items_b       msi1,ITPUB个人空间u9D
_8ti]:i
             bom_bill_of_materials    bom,ITPUB个人空间*i"gQ*n7Zh`Ld_
             bom_inventory_components bic,ITPUB个人空间UF;v@$ZJ~(f
             mtl_item_locations       mil,ITPUB个人空间O/xR-S\V(@5w8Xv[
             (
?#b5M
Jo9A1h J0             select level lvl,
0X#_;`Nt0               bic.bill_sequence_id,ITPUB个人空间Ax.fs&]8f~,j0M
               bic.component_item_id,ITPUB个人空间9t!`^7[}^h'b'`
               bic.component_quantity,ITPUB个人空间-cH@'I}'s6r6UY
               bic.OPERATION_SEQ_NUM,ITPUB个人空间h.j1dgH
               bic.COMPONENT_YIELD_FACTOR,
+x
}5I,o{G0               bic.COMPONENT_SEQUENCE_ID,ITPUB个人空间[!S-o&g7YE
               bic.item_num,
FEL9[IDdO#Dw6o0               bic.wip_supply_type,ITPUB个人空间#xl^fI#qF$o
               bic.supply_subinventory,ITPUB个人空间^dK1gC+@0SEU
               bic.effectivity_date
0S7iAa*u0          FROM bom_inventory_components bic
%f,af:W%WEt0         where disable_date IS NULLITPUB个人空间;?+Iv:aw^
F,A
         start with bic.bill_sequence_id in
H7el5}3S0                    (select bill_sequence_idITPUB个人空间#z zk&p.?Q
                       from bom_bill_of_materials bom2,ITPUB个人空间"X&F!J4~A2Mi+w
                            inv.mtl_system_items_b    msiITPUB个人空间u;H Fb%gH
                      where bom2.assembly_item_id = msi.inventory_item_id
(X"S.p,}p!fRW~0                        and bom2.organization_id = msi.organization_idITPUB个人空间z0eQd(K)~
---------------------------------------输入要查询的项目---------------------------------------------ITPUB个人空间3\,j"J0Fr,KW3}
                        and msi.segment1 ='料号名称'
$d&ubYe{%T-Bi0---------------------------------------输入要查询的项目---------------------------------------------ITPUB个人空间a8H$b;\[.Aq/G`
                        and msi.organization_id = 组织IDITPUB个人空间k-n#B jx5T?b
                        and bom2.alternate_bom_designator is null)ITPUB个人空间k8v-Q c@
        CONNECT BY bic.bill_sequence_id in priorITPUB个人空间8z l5I,R{T0?6x&wE
                   (SELECT distinct bill_sequence_idITPUB个人空间(T6iNcXU-t
                      FROM bom_bill_of_materials BO, inv.mtl_system_items_b msiITPUB个人空间
^*j#R0VJ
                     WHERE BO.assembly_item_id = bic.component_item_idITPUB个人空间O1Y$Y4|0K
                       AND BO.organization_id = 组织IDITPUB个人空间2D:Dvw1u#_
                       and bo.ORGANIZATION_ID = msi.ORGANIZATION_ID
#Sgb*ua(`$k0                       and bo.ASSEMBLY_ITEM_ID = msi.INVENTORY_ITEM_ID
PrUYl0                       and bo.alternate_bom_designator is null
L5tmx&x0                       and disable_date IS NULL)) aa
Q*YV%X4b0        where msi.organization_id=组织IDITPUB个人空间&c?;toahh c
          and msi1.organization_id=组织IDITPUB个人空间(K a'yiS&Pp
          and bom.organization_id=组织ID
#l)` Vw\yQ
O0          and msi.inventory_item_id=bom.assembly_item_idITPUB个人空间pF'Ab bv0Vv
          and bom.bill_sequence_id=bic.bill_sequence_id
sND%x3y/aG2k0          and bic.component_item_id=msi1.inventory_item_idITPUB个人空间~Z*Q*z#M8a X)qy%T
          and bic.disable_date is null
#]1u? l(lwv0          and aa.bill_sequence_id=bic.bill_sequence_idITPUB个人空间x:G[G7{%Z(bk4Lt3be
          and aa.component_item_id=msi1.inventory_item_id
X!}|$v(V(~;H g9d0          and mil.inventory_location_id(+)=msi1.wip_supply_locator_idITPUB个人空间6q+[H"~Bl+WI8x
          and mil.organization_id(+)=组织IDITPUB个人空间2f/]yoJ,W
        order byITPUB个人空间:L+_Fk5n%c/N&KzFN%X
          aa.lvl,
(^y'B.yoc4Ts;@0          aa.item_num) bb
6d }.U+~Qm\G!b!N0WHERE BOR.ASSEMBLY_ITEM_ID(+) = MSI.INVENTORY_ITEM_IDITPUB个人空间[)[H0mM h$Om#x
   AND BOS.ROUTING_SEQUENCE_ID(+) = BOR.ROUTING_SEQUENCE_IDITPUB个人空间0L&Z3] k1d.?-}o:b
   AND MSI.ORGANIZATION_ID(+) = 组织IDITPUB个人空间KI5[0S'Vs+O%R2F
   and bor.alternate_routing_designator is null
'B Z0D!`~|M i0   and bb.inventory_item_id=msi.inventory_item_idITPUB个人空间"az&A2k!iX e.Wq
order by
p.j]"]a#S0       bb.BOM层次,
W;l:{^9{&o"I0       bb.装配件,
$LRx8I9t4z*JP@8{0       bb.装配件说明,
p|'k`7?{8l7I6A0       bb.组件序号,ITPUB个人空间)iS$Zp(`%p
       bb.组件ITPUB个人空间fv~f~ O6F3w.rTE