AS
select
tuv_tuvdotcom_mast.tuvdotcom as tuvdotcom,
COALESCE(tuv_location_mast_intl.first_name,tuv_location_mast.first_name)
|| ' ' || COALESCE(tuv_location_mast_intl.second_name,tuv_location_mast.second_name) as company_name,
tuv_certificate_mast.cert_id as cert_id,
tuv_certificate_mast.cert_number as certificate_number,
tuv_certificate_mast.cust_id as cust_id,
COALESCE(tuv_zart_mast_intl.description,tuv_zart_mast.description) as description,
tuv_tuvdotcom_mast.tuvdotcom_id as tuvdotcom_id,tuv_tuvdotcom_mast.status_id as status_id,
'' page_valid_from, sysdate page_creation_date,
tuv_tuvdotcom_mast.tuvdotcom||' '||COALESCE(tuv_tuvdotcom_intl.sublease_company_name,tuv_tuvdotcom_mast.sublease_company_name)||' '
||COALESCE(tuv_tuvdotcom_intl.marketing_info,tuv_tuvdotcom_mast.marketing_info)||' '||tuv_certificate_mast.cert_number||' '
||tuv_certificate_mast.scope_english||' '||tuv_certificate_mast.scope_german||' '||tuv_certificate_mast.scope_local||' '
||tuv_zart_mast.zart_name||' '||COALESCE(tuv_zart_mast_intl.description,tuv_zart_mast.description)||' '
||COALESCE(tuv_customer_mast_intl.url,tuv_customer_mast.url)||' '||COALESCE(tuv_customer_mast_intl.email,tuv_customer_mast.email)||' '
||tuv_customer_mast.name_local||' '||tuv_customer_mast.address_local||' '||tuv_customer_mast.building_local||' '
||tuv_customer_mast.city_local||' '||COALESCE(tuv_customer_mast_intl.title,tuv_customer_mast.title)||' '
||COALESCE(tuv_customer_mast_intl.first_name,tuv_customer_mast.first_name)||' '
||COALESCE(tuv_customer_mast_intl.second_name,tuv_customer_mast.second_name)||' '
||COALESCE(tuv_customer_mast_intl.third_name,tuv_customer_mast.third_name)||' '
||COALESCE(tuv_customer_mast_intl.fourth_name,tuv_customer_mast.fourth_name)||' '
||tuv_location_mast.post_code||' '||tuv_location_mast.phone||' '||tuv_location_mast.fax||' '
||COALESCE(tuv_location_mast_intl.title,tuv_location_mast.title)||' '
||COALESCE(tuv_location_mast_intl.first_name,tuv_location_mast.first_name)||' '
||COALESCE(tuv_location_mast_intl.second_name,tuv_location_mast.second_name)||' '
||COALESCE(tuv_location_mast_intl.third_name,tuv_location_mast.third_name)||' '
||COALESCE(tuv_location_mast_intl.fourth_name,tuv_location_mast.fourth_name)||' '
||COALESCE(tuv_location_mast_intl.street_1,tuv_location_mast.street_1)||' '
||COALESCE(tuv_location_mast_intl.street_2,tuv_location_mast.street_2)||' '
||COALESCE(tuv_location_mast_intl.city_1,tuv_location_mast.city_1)||' '
||COALESCE(tuv_location_mast_intl.city_2,tuv_location_mast.city_2)||' '
||COALESCE(tuv_location_mast_intl.state,tuv_location_mast.state)||' '||COALESCE(tuv_location_mast_intl.country,tuv_location_mast.country)
as search_data
from
tuv_tuvdotcom_mast,
tuv_tuvdotcom_intl,
tuv_tuvdotcom_type_mast,
tuv_tuvdotcom_system_certs,
tuv_certificate_mast,
tuv_customer_location,
tuv_location_mast,
tuv_location_mast_intl,
tuv_customer_mast,
tuv_customer_mast_intl,
tuv_zart_mast,
tuv_zart_mast_intl
where
tuv_tuvdotcom_mast.tuvdotcom_id = tuv_tuvdotcom_intl.tuvdotcom_id(+) and
tuv_tuvdotcom_mast.tdc_type_id = tuv_tuvdotcom_type_mast.tdc_type_id and
tuv_certificate_mast.cert_type_id = 2 and
tuv_certificate_mast.validity in (04,14,24,90) and
tuv_tuvdotcom_system_certs.tuvdotcom_id(+) = tuv_tuvdotcom_mast.tuvdotcom_id and
tuv_tuvdotcom_system_certs.cert_id = tuv_certificate_mast.cert_id(+) and
tuv_certificate_mast.cust_id = tuv_customer_location.cust_id and
tuv_certificate_mast.location_id = tuv_location_mast.location_id and
tuv_customer_location.location_id= tuv_location_mast.location_id and
tuv_location_mast.location_id= tuv_location_mast_intl.location_id(+) and
tuv_customer_location.cust_id = tuv_customer_mast.cust_id and
tuv_customer_mast.cust_id = tuv_customer_mast_intl.cust_id(+) and
tuv_certificate_mast.zart_id = tuv_zart_mast.zart_id and
tuv_zart_mast.zart_id = tuv_zart_mast_intl.zart_id(+) and
tuv_tuvdotcom_intl.lang_id (+)= 2 and
tuv_location_mast_intl.lang_id (+)= 2 and
tuv_customer_mast_intl.lang_id (+)= 2 and
tuv_zart_mast_intl.lang_id (+)= 2;
Praveen
Malik
Software Engineer
Extn:1607
===========================================================================
Sobha
Renaissance Information Technology Private Limited
An
SEI-CMM & P-CMM Level 5 Company
World's
1st SSE-CMM Level 5 Company
BS
7799 certified by British Standards Institute
ISO
9001:2000 Certified by TÜV Rheinland/Berlin-Brandenburg
A
Six Sigma Practice Company
Tel:
+ 91 80 51951999; Fax: + 91 80 51523300; Video Conference: + 91 80 51252222
Email : praveen.k@renaissance-it.com; Web: www.renaissance-it.com
===========================================================================
The
information transmitted is intended only for the person or entity to which it is
addressed and may contain confidential and/or privileged material. Any review,
retransmission, dissemination or other use of, or taking of any action in
reliance upon, this information by persons or entities other than the intended
recipient is prohibited. If you received this in error, please contact the
sender and delete all copies from any computer.