Oracle EBS-SQL (PO-8):检查有供货比例无采购员.sql

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,'制造地点','传送至') 来源类型 ,

    msso.vendor_id                       供应商 ,

    msso.vendor_site_id                   地点 ,

    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    

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 msr.planning_active=2   --供货比例不为100%  

    and msso.source_type=3     --采购来源 

 --and msi.item_type in ('SA','OP')

   and msi.buyer_id is null  --无采购员的

   and msso.allocation_percent >0   --排除比例为0的选项

   and msro.disable_date is null

 order by msr.sourcing_rule_name