select
pvs.org_id,
pvs.vendor_id,
pvs.vendor_site_id,
hou.name 经营组织,
flv.meaning 供应商类型,
pv.segment1 供应商编码,
pv.vendor_name 供应商名称,
pv.vendor_name_alt 别名,
pvs.vendor_site_code 地点名称,
pvs.purchasing_site_flag 采购属性,
pv.end_date_active 供应商失效日期,
pvs.creation_date 地点创建日期,
pvs.inactive_date 地点失效日期,
asl.concatenated_segments 商品类,
asl.status 状态,
asl.flag 是否禁用,
(select max(ph.creation_date)
from apps.po_headers_all ph
where ph.vendor_id = pvs.vendor_id
and ph.vendor_site_id = pvs.vendor_site_id
and ph.type_lookup_code = 'STANDARD'
and ph.authorization_status = 'APPROVED') 最后采购日期
from apps.po_vendors pv,
apps.fnd_lookup_values_vl flv,
apps.po_vendor_sites_all pvs,
apps.hr_organization_units hou,
apps.hr_organization_information hoi,
(select mc.concatenated_segments ,
pas.status ,
aa.vendor_id,
bb.vendor_site_id,
mc.category_id,
bb.org_id,
NVL(pasl.disable_flag, 'N') flag
from po.po_vendors aa,
po.po_vendor_sites_all bb,
apps.po_approved_supplier_list pasl,
apps.po_asl_statuses pas,
apps.mtl_categories_kfv mc
where aa.vendor_id = bb.vendor_id
and bb.org_id in (1800,1362)
and pasl.vendor_id = aa.vendor_id
and pasl.vendor_site_id = bb.vendor_site_id
and pasl.ASL_STATUS_ID = pas.STATUS_ID
AND pasl.category_id = mc.category_id) asl
where pv.vendor_type_lookup_code = flv.lookup_code(+)
and flv.lookup_type = 'VENDOR TYPE'
and flv.lookup_code = '材料供应商'
and pvs.org_id = hou.organization_id
and hoi.organization_id = pvs.org_id
and hoi.org_information_context = 'Operating Unit Information'
and nvl(hou.date_to, sysdate + 1) > sysdate
and pv.vendor_id = pvs.vendor_id
--and pv.segment1 = 'K.A200053'
and pvs.org_id in(1800,1362)
and pvs.vendor_id = asl.vendor_id(+)
and pvs.org_id = asl.org_id(+)
and pvs.vendor_site_id = asl.vendor_site_id(+)
order by pvs.org_id,pvs.vendor_site_id
--and nvl(pv.end_date_active, sysdate + 1) > sysdate
--and nvl(pvs.inactive_date, sysdate + 1) > sysdate