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

这是俺原创的，速度很快，写个循环就可以导出多个编码的BOM树，使用时把SQL中的“组织ID”换成自己EBS的实际ID。在Oracle EBS 11i 10.0版本下测试通过！ITPUB个人空间Q6h.l[.x*J(Bci&WT }

%?
lYg,t1f0初次写这种语句，好用支持一下！ITPUB个人空间 v7n#i;RK f

SELECT distinctITPUB个人空间 _(~+Q.a-Ly,Q
       bb.BOM层次,ITPUB个人空间zpG-lx,^9L C8Myzr
       bb.装配件,
j3_4OA:|C+[b@0       bb.装配件说明,
8w`U&n.^f0m0       bb.组件序号,ITPUB个人空间{:c5r B\#I!PV
       bb.组件,
o1|,ob Ug[x7[0       bb.组件说明,
#g|$Z;K[A
Hv0       bb.组件用量,ITPUB个人空间&MCe/~-i5^^3VQ-^%a
       bb.单位,ITPUB个人空间^Z*H%wN-nP4IOb
       bb.利用率,
?f~jE0       bb.冲减库房,
q1mF d?j7H+^(qP0       bb.冲减货位,
en5U;AS^ G0       bb.属性,ITPUB个人空间FXJ,|[D%Ph v1f8J#x
       bb.BOM创建时间,
1v@"E#CG"z0       BOR.COMPLETION_SUBINVENTORY 入库,
d.P*B,D8p2},TE0       MSI.WIP_SUPPLY_SUBINVENTORY 冲减,ITPUB个人空间#cm~ ~
T6w&P/FFX
       MSI.MAXIMUM_ORDER_QUANTITY  最大批量,ITPUB个人空间
L2s-}4Z B(J0_:n!s
       MSI.LEAD_TIME_LOT_SIZE      提前期,
-j%h~6f$V o#B0Qy0^0       MSI.FULL_LEAD_TIME          制造周期,
"T^]u M#L'E7s0       MSI.FIXED_LOT_MULTIPLIER    固定增加,ITPUB个人空间 c pq3X {j8?
       MSI.MINIMUM_ORDER_QUANTITY  最小批量,ITPUB个人空间V
p.G4t9?%W
       MSI.FIXED_ORDER_QUANTITY    固定定货量,
%HO.Xei#g0       MSI.FIXED_DAYS_SUPPLY       固定天数,ITPUB个人空间"]/VVzDA^
       MSI.PLANNER_CODE            计划员ITPUB个人空间i^(x!Lk*x%B3}
  FROM MTL_SYSTEM_ITEMS          MSI,
y#]T9Z
x?;G"p I0       BOM_OPERATIONAL_ROUTINGS  BOR,ITPUB个人空间
X7?Qvz {0F
       BOM_OPERATION_SEQUENCES   BOS,
rg6{;?!Z;JA0       (select distinctITPUB个人空间6r!K?6Y@*B7Pj
               aa.lvl                       BOM层次,
A3`$A3Dl:`(J0               msi.segment1                 装配件,
?@0^S3s ^;n0               msi.description              装配件说明,ITPUB个人空间3W%a~
`3p0?
               aa.item_num                  组件序号,
%I2T$_y;b-u3L0               msi1.segment1                组件,
H,g(QoA~l^V0               msi1.description             组件说明,ITPUB个人空间:` _6M)O^wS
tP[4A
               aa.component_quantity        组件用量,
9A0Kuj A~x-l1~0               msi1.primary_unit_of_measure 单位,
_3q:xrHU0               aa.COMPONENT_YIELD_FACTOR    利用率,
f q'W*_}4^lo%k0               msi1.wip_supply_subinventory 冲减库房,ITPUB个人空间0k+n"^q_(v of
               mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 || '.' ||ITPUB个人空间"gn5Z|+D_#X
               mil.segment4                 冲减货位,ITPUB个人空间/lTge2@^+e/f
               decode(msi1.planning_make_buy_code,1,'制造',2,'采购') 属性,
s6[I!b%~ O,Y0               to_char(msi1.creation_date,'yyyy.mm.dd hh24:mm.ss')   BOM创建时间,
W O.h5Urj0wi@0               msi1.inventory_item_id
5Ht7_-\V6aNK%c0                ITPUB个人空间!G(l iAz z2@H&o$e
        from mtl_system_items_b       msi,ITPUB个人空间&k.N$O)h W({2c
             mtl_system_items_b       msi1,
}4Smwq0             bom_bill_of_materials    bom,
5T ^b8K1m-X"A9b0             bom_inventory_components bic,
,v%W$g.x.wr(X%jR0             mtl_item_locations       mil,
#_T K a4UzKNhZ?0             (ITPUB个人空间4l"PxGm+Q
             select level lvl,ITPUB个人空间WZhiH,F1Lz
b
               bic.bill_sequence_id,
?!T"`'M/Al8y0               bic.component_item_id,ITPUB个人空间
D M(L#A?!]H
               bic.component_quantity,
$|y? ULk0               bic.OPERATION_SEQ_NUM,ITPUB个人空间&D\O4m'tB0t
               bic.COMPONENT_YIELD_FACTOR,
3S9CM%}y0               bic.COMPONENT_SEQUENCE_ID,
d(\2v,g5P e0               bic.item_num,ITPUB个人空间&\QD.x/V7}
               bic.wip_supply_type,ITPUB个人空间k"S#k] lj\A.`
               bic.supply_subinventory,ITPUB个人空间Mv2Ue9whk
               bic.effectivity_date
Ysypi W-W7n0          FROM bom_inventory_components bicITPUB个人空间{f\"d]
         where disable_date IS NULL
UIK!I6Y bi]0         start with bic.bill_sequence_id inITPUB个人空间
g Cn1Op E/P
                    (select bill_sequence_idITPUB个人空间G A(bK9wB3s p1S(?
                       from bom_bill_of_materials bom2,
NL2rz A%g0                            inv.mtl_system_items_b    msi
'v}q&oH` O/l0                      where bom2.assembly_item_id = msi.inventory_item_idITPUB个人空间
df.s"OP%Aa
X;os I~
                        and bom2.organization_id = msi.organization_id
lDX9M)Y
u9_AL0---------------------------------------输入要查询的项目---------------------------------------------ITPUB个人空间%c(he$^*o
                        and msi.segment1 ='料号名称'ITPUB个人空间ui5g.TI'{#|.Q
---------------------------------------输入要查询的项目---------------------------------------------ITPUB个人空间;J!P
A b7M\q
                        and msi.organization_id = 组织ID
7bfz.b)dw0                        and bom2.alternate_bom_designator is null)ITPUB个人空间r Y5x*@C S f
        CONNECT BY bic.bill_sequence_id in prior
sXfVYQ0                   (SELECT distinct bill_sequence_idITPUB个人空间9j'_ZH3rO3c9A
                      FROM bom_bill_of_materials BO, inv.mtl_system_items_b msi
1JO \mK&y"_k0                     WHERE BO.assembly_item_id = bic.component_item_id
YFv:F:{K8_
J0                       AND BO.organization_id = 组织ID
4TEM5nj'U0                       and bo.ORGANIZATION_ID = msi.ORGANIZATION_ID

fC0gQ ]0                       and bo.ASSEMBLY_ITEM_ID = msi.INVENTORY_ITEM_ID
y^(mOI.h0                       and bo.alternate_bom_designator is null
)Q5~C4WD?0                       and disable_date IS NULL)) aa
lGk]3w MDUe0        where msi.organization_id=组织IDITPUB个人空间6O$b |'u}(S3S
          and msi1.organization_id=组织IDITPUB个人空间D*\W9Rb ~!DQ6c
          and bom.organization_id=组织ID
+K2x4ij,h%H0l$\uZc6c0          and msi.inventory_item_id=bom.assembly_item_idITPUB个人空间6n*K7O8h}9DV
          and bom.bill_sequence_id=bic.bill_sequence_id
(b5QX]4cuJ0          and bic.component_item_id=msi1.inventory_item_id
W7{#?&ALpY0          and bic.disable_date is nullITPUB个人空间,M*Xyl~ S [:|2l
          and aa.bill_sequence_id=bic.bill_sequence_id
/{Y;z,X9j'`0          and aa.component_item_id=msi1.inventory_item_idITPUB个人空间-G ?)h6X-]A5`g7x9{
          and mil.inventory_location_id(+)=msi1.wip_supply_locator_idITPUB个人空间&n1v8~S6~%BmL
          and mil.organization_id(+)=组织ID
NDnl8m_ ^Om0        order by
B'x3X)l d0          aa.lvl,ITPUB个人空间vR P$K]-q IQas
          aa.item_num) bbITPUB个人空间ZkH cb.|
WHERE BOR.ASSEMBLY_ITEM_ID(+) = MSI.INVENTORY_ITEM_IDITPUB个人空间,^5O XM$q$K
   AND BOS.ROUTING_SEQUENCE_ID(+) = BOR.ROUTING_SEQUENCE_ID
w"b{_W&hORh/A0   AND MSI.ORGANIZATION_ID(+) = 组织IDITPUB个人空间e0XZF.h1L)UF/K
   and bor.alternate_routing_designator is null
(q9iF:DgV
@{3T0   and bb.inventory_item_id=msi.inventory_item_id
Omd*I gV9nY"z0order byITPUB个人空间9YtGtRJ N m!`
       bb.BOM层次,
I$gwFpjX5p0       bb.装配件,ITPUB个人空间~1`0`hu-A8Id/y
       bb.装配件说明,
oO#d,n,{0       bb.组件序号,
,s%X6S:wVI0       bb.组件ITPUB个人空间1j ^']I`j/X