select
distinct
msr.sourcing_rule_name
名称
,msi.description
说明
,msi.item_type
类型
,msi.inventory_item_status_code
状态
,msr.planning_active
计划生效
,msro.effective_date
有效日期
,msro.disable_date
无效日期
,msro.attribute1 比例月份
,decode(msso.source_type,3,‘采购来源‘,2,‘制造地点‘,‘传送至‘)
来源类型
,pv.vendor_name
供应商名称
,pv.segment1 供应商编码
,pvs.vendor_site_code 地址简称
,msso.allocation_percent
比例
,msso.rank
优先级
from mrp.MRP_SOURCING_RULES msr
,mrp.mrp_sr_receipt_org msro
,mrp.mrp_sr_source_org msso
,inv.mtl_system_items_b
msi
,po.po_vendors
pv
,po.po_vendor_sites_all pvs
where
msi.organization_id = X
and msi.organization_id =
msr.organization_id
and msi.segment1 =
msr.sourcing_rule_name
and msr.organization_id =
msro.receipt_organization_id
and msr.sourcing_rule_id =
msro.sourcing_rule_id
and msro.sr_receipt_id =
msso.sr_receipt_id
and pvs.vendor_site_id =
msso.vendor_site_id
and pvs.vendor_id = pv.vendor_id
and
msso.vendor_id = pv.vendor_id
and
msso.source_type=3
and
msso.allocation_percent >0 --排除比例为0的选项
and
msro.disable_date is null
and not exists ( select null from
PO.PO_APPROVED_SUPPLIER_LIST
avl
where
avl.owning_organization_id=msi.organization_id
and avl.item_id =
msi.inventory_item_id
and avl.vendor_site_id = pvs.vendor_site_id)
order by
msr.sourcing_rule_name