ITPub博客

首页 > 数据库 > Oracle > 抓專用物料(即專供某些客戶[機型後綴識別]用的料件)

抓專用物料(即專供某些客戶[機型後綴識別]用的料件)

原创 Oracle 作者:longwansheng 时间:2011-12-15 16:00:50 0 删除 编辑

1,抓出某後綴機型明細

2,對1的機型進行正展BOM

3,取得所有下階

4,對ls20111215c這些下階進行逆展

5 取得專用物料

6,取得供應商

[@more@]

--抓專用物料

--1,抓出某後綴機型明細

create table ls20111215a as
select inventory_item_id,segment1 item,description
from mtl_system_items_b b
where organization_id=803
and (segment1 like '%-RE%'
or
segment1 like '%-GT%')

select * from ls20111215a

select * from ls20111215b


--2,對1的機型進行正展BOM

create table ls20111215b as
SELECT distinct
bb.BOMcc,
bb.zhpeijian,
bb.zhpeijiandesc,
bb.SUB_SEQUENCE,
bb.SUB_ITEM,
bb.SUB_ITEMDESC,
bb.component_quantity,
bb.UOM,
bb.LYL_RATE,
bb.SUBINV,
bb.LOCATION,
bb.ITEM_TYPE,
bb.BOMtimes,
BOR.COMPLETION_SUBINVENTORY ,--入庫,
MSI.WIP_SUPPLY_SUBINVENTORY,-- 沖減,
MSI.MAXIMUM_ORDER_QUANTITY ,-- 最大批量,
MSI.LEAD_TIME_LOT_SIZE ,-- 提前期,
MSI.FULL_LEAD_TIME ,-- 製造週期,
MSI.FIXED_LOT_MULTIPLIER ,-- 固定增加,
MSI.MINIMUM_ORDER_QUANTITY ,--最小批量,
MSI.FIXED_ORDER_QUANTITY ,-- 固定定貨量,
MSI.FIXED_DAYS_SUPPLY ,-- 固定天數,
MSI.PLANNER_CODE -- 計畫員
FROM MTL_SYSTEM_ITEMS MSI,
BOM_OPERATIONAL_ROUTINGS BOR,
BOM_OPERATION_SEQUENCES BOS,
(select distinct
aa.lvl BOMcc,
msi.segment1 zhpeijian,
msi.description zhpeijiandesc,
aa.item_num SUB_SEQUENCE,
msi1.segment1 SUB_ITEM,
msi1.description SUB_ITEMDESC,
aa.component_quantity ,
msi1.primary_unit_of_measure UOM,
aa.COMPONENT_YIELD_FACTOR LYL_RATE,
msi1.wip_supply_subinventory SUBINV,
mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 || '.' ||
mil.segment4 LOCATION,
decode(msi1.planning_make_buy_code,1,'SUB',2,'PUR') ITEM_TYPE,
to_char(msi1.creation_date,'yyyy.mm.dd hh24:mm.ss') BOMtimes,
msi1.inventory_item_id
from mtl_system_items_b msi,
mtl_system_items_b msi1,
bom_bill_of_materials bom,
bom_inventory_components bic,
mtl_item_locations mil,
(
select level lvl,
bic.bill_sequence_id,
bic.component_item_id,
bic.component_quantity,
bic.OPERATION_SEQ_NUM,
bic.COMPONENT_YIELD_FACTOR,
bic.COMPONENT_SEQUENCE_ID,
bic.item_num,
bic.wip_supply_type,
bic.supply_subinventory,
bic.effectivity_date
FROM bom_inventory_components bic
where disable_date IS NULL
start with bic.bill_sequence_id in
(select bill_sequence_id
from bom_bill_of_materials bom2,
inv.mtl_system_items_b msi,
ls20111215a a
where bom2.assembly_item_id = msi.inventory_item_id
and bom2.organization_id = msi.organization_id
--and msi.segment1 like 'A006503CP-GTG1'
and msi.inventory_item_id=a.inventory_item_id
and msi.organization_id =803 -- 組織ID
and bom2.alternate_bom_designator is null)
CONNECT BY bic.bill_sequence_id in prior
(SELECT distinct bill_sequence_id
FROM bom_bill_of_materials BO, inv.mtl_system_items_b msi
WHERE BO.assembly_item_id = bic.component_item_id
AND BO.organization_id = 803 --組織ID
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)) aa
where msi.organization_id=803 --組織ID
and msi1.organization_id=803 --組織ID
and bom.organization_id=803 --組織ID
and msi.inventory_item_id=bom.assembly_item_id
and bom.bill_sequence_id=bic.bill_sequence_id
and bic.component_item_id=msi1.inventory_item_id
and bic.disable_date is null
and aa.bill_sequence_id=bic.bill_sequence_id
and aa.component_item_id=msi1.inventory_item_id
and mil.inventory_location_id(+)=msi1.wip_supply_locator_id
and mil.organization_id(+)=803 --組織ID
order by
aa.lvl,
aa.item_num) bb
WHERE BOR.ASSEMBLY_ITEM_ID(+) = MSI.INVENTORY_ITEM_ID
AND BOS.ROUTING_SEQUENCE_ID(+) = BOR.ROUTING_SEQUENCE_ID
AND MSI.ORGANIZATION_ID(+) =803 -- 組織ID
and bor.alternate_routing_designator is null
and bb.inventory_item_id=msi.inventory_item_id
order by
bb.BOMcc,
bb.zhpeijian,
bb.zhpeijiandesc,
bb.sub_SEQUENCE,
bb.sub_item

--3,取得所有下階

create table ls20111215c as
select distinct sub_item,inventory_item_id,description
from ls20111215b a,
mtl_system_items_b b
where b.organization_id=803
and b.SEGMENT1=a.SUB_ITEM

--4,對ls20111215c這些下階進行逆展

select * from ls20111215c

create table ls20111215d (organization_id number,item_id number,item varchar2(20),mm_item_id number,mm_item varchar2(20))

delete ls20111215d


declare
cursor cur is

select * from ls20111215c ;

cursor mm_cur(orgid in number,itemid in number) is
select * from (
select bom.organization_id,msi.segment1 item, bom.ASSEMBLY_ITEM_ID, bic.*
from bom_inventory_components bic ,
bom_bill_of_materials bom,
mtl_system_items_b msi
where bom.ORGANIZATION_ID=orgid
and bom.ASSEMBLY_ITEM_ID=msi.inventory_item_id
and msi.organization_id=1
and bom.bill_sequence_id=bic.bill_sequence_id
and bic.COMPONENT_ITEM_ID=itemid
) aa
start with aa.COMPONENT_ITEM_ID=itemid
connect by prior aa.ASSEMBLY_ITEM_ID=aa.COMPONENT_ITEM_ID;


begin
for rec in cur loop
for mmrec in mm_cur(803,rec.inventory_item_id) loop
insert into ls20111215d
values(803,rec.inventory_item_id,rec.sub_item,mmrec.assembly_item_id,mmrec.item);
end loop;
commit;
end loop;
end ;

--5 取得專用物料
create table ls20111215e as
select distinct item,item_id
from ls20111215d a
where not exists
(select 1 from ls20111215d b
where b.ITEM_ID=a.item_id
and instr(mm_item,'-')>0
and mm_item not like '%-GB%'
and mm_item not like '%-RE%'
)


and item='029528CP'

select *
from ls20111215e

--6,取得供應商

SELECT 'CS2' org,a.ITEM,pv.VENDOR_NAME,msi.description
FROM ls20111215e a,
po_lines_all pl,
mtl_system_items_b msi,
po_headers_all ph,
po_vendors pv
WHERE a.ITEM_ID=pl.ITEM_ID
AND 801=pl.ORG_ID
and msi.inventory_item_id=a.item_id
and msi.organization_id=803
AND pl.PO_HEADER_ID=ph.PO_HEADER_ID
AND ph.VENDOR_ID=pv.VENDOR_ID


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/92289/viewspace-1056846/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-12

  • 博文量
    162
  • 访问量
    741195