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