求BOM清單SQL語句

hotwish

最初由 hotwish 发布
[B]我的意思是以上的SQL SCRIPT有问题,可否修正一下,谢谢 [/B]

pongstar

好資料先收藏再研究

catherin

單純的sql只能一個一個執行.必須寫成procedure.利用指針取得成品料號.當作參數傳給它.就可以執行.
以下程式供你參考.
--建立PROCEDURE
CREATE OR REPLACE PROCEDURE BLIST IS
P_item_id number(10);
LV  number(2);
m_iid number(10);
Component_Qty number(9,5);
edate date;
ddate date;
cursor c_mmt is
select msi.inventory_item_id
from inv.mtl_system_items_b msi,
bom.bom_bill_of_materials bom
where msi.inventory_item_id=bom.assembly_item_id
and bom.ORGANIZATION_ID IN('89','147')
and bom.ORGANIZATION_ID = MSI.ORGANIZATION_ID
and bom.ALTERNATE_BOM_DESIGNATOR is null
and msi.inventory_item_id in(select DISTINCT mmt.inventory_item_id from mtl_material_transactions mmt
where mmt.transaction_type_id=33);
begin
for h_iid in c_mmt loop
for c_rev in (select revision
from aw_bom_rev_v
where inventory_item_id=h_iid.inventory_item_id) loop
for c_bom in (
select  
msi1.inventory_item_id P_item_id,
to_char(b.lvl) LV,
msi2.inventory_item_id m_iid,
b.component_quantity Component_Qty,
b.effectivity_date edate,
b.disable_date ddate
from inv.mtl_system_items_b msi1,
inv.mtl_system_items_b msi2,
bom.bom_bill_of_materials bom,
(select level lvl,
bic.bill_sequence_id,
bic.component_item_id,
bic.component_quantity,
bic.COMPONENT_YIELD_FACTOR,
bic.item_num,
bic.wip_supply_type,
bic.supply_subinventory,
bic.effectivity_date,
bic.disable_date
FROM bom.bom_inventory_components bic
start with bic.bill_sequence_id in
( select bill_sequence_id
from bom.bom_bill_of_materials bom2,
inv.mtl_system_items_b msi
where bom2.assembly_item_id = msi.inventory_item_id
and bom2.organization_id = msi.organization_id
and  msi.inventory_item_id=h_iid.inventory_item_id
and msi.organization_id IN('89','147')
and bom2.alternate_bom_designator is null
)
CONNECT BY bic.bill_sequence_id =
prior (SELECT distinct bill_sequence_id
FROM bom.bom_bill_of_materials BO,
inv.mtl_system_items_b msi,
apps.aw_bom_rev_v rev
WHERE BO.assembly_item_id = bic.component_item_id
And msi. PLANNING_MAKE_BUY_CODE=1
and rev.inventory_item_id=h_iid.inventory_item_id
and rev.revision=c_rev.revision
AND BO.organization_id in('89','147')
and ((to_char(bic.effectivity_date,'yyyy/mm/dd')<=rev.disable_date and bic.disable_date is null)
or (to_char(bic.disable_date,'yyyy/mm/dd')>rev.disable_date
and to_char(bic.effectivity_date,'yyyy/mm/dd')<=rev.disable_date
and  bic.disable_date is not null))
and bo.ORGANIZATION_ID = msi.ORGANIZATION_ID
and bo.ASSEMBLY_ITEM_ID = msi.INVENTORY_ITEM_ID
and bo.alternate_bom_designator is null
--and disable_date IS NULL
)
) b,
apps.aw_bom_rev_v rev1
where b.bill_sequence_id = bom.bill_sequence_id
and rev1.inventory_item_id=h_iid.inventory_item_id
and rev1.revision=c_rev.revision
and bom.ORGANIZATION_ID IN('89','147')
and ((to_char(b.effectivity_date,'yyyy/mm/dd')<=rev1.disable_date and b.disable_date is null)
or (to_char(b.disable_date,'yyyy/mm/dd')>rev1.disable_date
and to_char(b.effectivity_date,'yyyy/mm/dd')<=rev1.disable_date
and  b.disable_date is not null))
and bom.ORGANIZATION_ID = msi1.ORGANIZATION_ID
and bom.ASSEMBLY_ITEM_ID = msi1.INVENTORY_ITEM_ID
and bom.ORGANIZATION_ID = MSI2.ORGANIZATION_ID
AND b.component_item_id = MSI2.INVENTORY_ITEM_ID
AND bom.alternate_bom_designator is null
and (MSI2.INVENTORY_ITEM_ID NOT IN (SELECT ASSEMBLY_ITEM_ID FROM  bom_bill_of_materials) or
msi2. PLANNING_MAKE_BUY_CODE=2)
) loop
INSERT INTO AW_BOM_BLIST VALUES(h_iid.inventory_item_id,c_bom.p_item_id, C_bom.LV,
c_bom.m_iid,c_bom.Component_Qty,c_bom.edate,c_bom.ddate,c_rev.revision);
commit;
END LOOP;
END LOOP;
END LOOP;
END BLIST;
/

chinaoracles

很久没做ODM，先收藏，认真研究！

cjman2008

lveamy 发表于 2007-8-8 11:01
肯定有的
select DISTINCT
msi1.segment1 P_item,

不错 我这边用上了 谢谢lveamy

box

恭喜！您刚拣到ITPUB送出的 复活蛋 。