SELECT count(*) into v_asl_site_count
FROM po_lookup_codes plc,
hr_org_units_no_join hou,
hr_org_units_no_join hou1,
hr_all_organization_units_tl hout,
hr_all_organization_units_tl hout1,
po_vendors pv,
po_vendor_sites_all pvs,
mtl_manufacturers mm,
mtl_manufacturers mm1,
mtl_parameters mp,
mtl_parameters mp1,
po_asl_statuses past,
po_approved_supplier_list pasl1,
po_asl_attributes paa,
po_approved_supplier_list pasl
WHERE pasl.vendor_business_type = plc.lookup_code
AND plc.lookup_type = ‘ASL_VENDOR_BUSINESS_TYPE’
AND pasl.asl_status_id = past.status_id
AND pasl.owning_organization_id = mp.organization_id
AND pasl.using_organization_id = mp1.organization_id(+)
AND pasl.owning_organization_id = hou.organization_id
AND pasl.using_organization_id = hou1.organization_id(+)
AND pasl.vendor_site_id = pvs.vendor_site_id(+)
AND ( ( pasl.vendor_site_id IS NOT NULL
AND pvs.vendor_site_code IS NOT NULL
)
OR (pasl.vendor_site_id IS NULL AND pvs.vendor_site_code IS NULL)
)
AND pasl.vendor_id = pv.vendor_id(+)
AND pasl.manufacturer_id = mm.manufacturer_id(+)
AND pasl.manufacturer_asl_id = pasl1.asl_id(+)
AND pasl1.manufacturer_id = mm1.manufacturer_id(+)
AND pasl.asl_id = paa.asl_id
AND pasl.using_organization_id = paa.using_organization_id
AND hou.organization_id = hout.organization_id(+)
AND DECODE (hout.organization_id, NULL, ‘1’, hout.LANGUAGE) =
DECODE (hout.organization_id,
NULL, ‘1’,
USERENV (‘LANG’)
)
AND hou1.organization_id = hout1.organization_id(+)
AND DECODE (hout1.organization_id, NULL, ‘1’, hout1.LANGUAGE) =
DECODE (hout1.organization_id,
NULL, ‘1’,
USERENV (‘LANG’)
)
and paa.item_id =8313
and pv.vendor_id=2692
and (pasl.USING_ORGANIZATION_ID =90 or pasl.USING_ORGANIZATION_ID=-1)
and nvl(pasl.DISABLE_FLAG,’N’)’Y’
group by VENDOR_SITE_CODE
having count(*)>1;
Recent Posts