drop table po_hf_from_2007

create table po_hf_from_2007 as
select pv.vendor_id,pv.vendor_name,pv.segment1 vendor_no,
pll.ORG_ID,pl.item_id,msi.segment1 item,msi.description,
sum(pll.QUANTITY-nvl(pll.QUANTITY_CANCELLED,0)) qty,
max(pll.LAST_UPDATE_DATE) last_date
--sum(pll.PRICE_OVERRIDE*nvl(ph.RATE,1)*(pll.QUANTITY-nvl(pll.QUANTITY_CANCELLED,0))) amt
from po_line_locations_all pll,po_releases_all pr,
po_lines_all pl,po_headers_all ph,
mtl_system_items_b msi,po_vendors pv
where --pll.ORG_ID =2
--and
pll.PO_RELEASE_ID=pr.PO_RELEASE_ID
and pll.PO_LINE_ID=pl.PO_LINE_ID
and pll.PO_HEADER_ID=ph.PO_HEADER_ID
--and to_char(pll.creation_date,'YYYYMM')>='200507'
--and to_char(pll.creation_date,'YYYYMM')<='200512'
and pll.creation_date>=to_date('20070101','yyyymmdd')
and pl.LINE_TYPE_ID=1
and pl.ORG_ID=msi.ORGANIZATION_ID
and pl.ITEM_ID=msi.INVENTORY_ITEM_ID
and ph.VENDOR_ID=pv.VENDOR_ID
and pv.vendor_id=29629 --SC-合鋒衛浴
group by pv.vendor_id,pv.vendor_name,pv.segment1 ,
pll.ORG_ID,pl.item_id,msi.segment1 ,msi.description

select * from po_vendors
where vendor_name like '%合%鋒%'

select * from po_hf_from_2007

/*

desc ff_item_mm

CREATE TABLE FF_ITEM_MM
(
ORGANIZATION_ID NUMBER,
ITEM_ID NUMBER,
ITEM VARCHAR2(20 BYTE),
MM_ITEM_ID NUMBER,
MM_ITEM VARCHAR2(20 BYTE)
)

*/

delete ff_item_mm

declare
cursor cur is

select * from po_hf_from_2007 ;

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(rec.org_id,rec.item_id) loop
insert into ff_item_mm
values(rec.org_id,rec.item_id,rec.item,mmrec.assembly_item_id,mmrec.item);
end loop;
commit;
end loop;
end ;

select * from ff_item_mm

select c.organization_code,c.ORGANIZATION_NAME,
a.*,
b.mm_item_id,b.mm_item
from ff_item_mm b, po_hf_from_2007 a,
org_organization_definitions c
where a.org_id=c.organization_id
and b.organization_id=a.org_id
and b.item_id=a.ITEM_ID
order by organization_code,a.item,mm_item