Oracle Ebs-sql (PO-14): Check vendor information SQL

Select

PVS.ORG_ID,

PVS.VENDOR_ID,

PVS.VENDOR_SITE_ID,

Hou.name Business organization,

flv.meaning supplier Type,

PV.SEGMENT1 Supplier Code,

Pv.vendor_name supplier Name,

Pv.vendor_name_alt aliases,

Pvs.vendor_site_code Location name,

Pvs.purchasing_site_flag Purchase attributes,

Pv.end_date_active Supplier Expiration date,

Pvs.creation_date Location Creation date,

Pvs.inactive_date Location Expiration date,

Asl.concatenated_segments Commodity category,

Asl.status State,

Asl.flag is disabled,

(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 ') last purchase date

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 = ' Material supplier '

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