--管理员查看地址簿 SELECT hps.party_site_id, hps.party_site_name AS address_name, 'CURRENT' AS status, hzl.address1 AS loc_address1, hzl.address2 AS loc_address2, hzl.address3 AS loc_address3, hzl.city AS loc_city, hzl.county AS loc_county, hzl.state AS loc_state, hzl.province AS loc_province, hzl.postal_code AS loc_postal_code, hzl.country AS loc_country, fvl.territory_short_name AS country_name, hzl.address4 AS loc_address4, email.email_address, phone.raw_phone_number AS phone_number, fax.raw_phone_number AS fax_number, decode(pos_util_pkg.is_addr_ccr(1.0, '', hps.party_site_id), 'T', 'removeInActiveImage', 'removeActiveImage') AS remove_image, 'mngSites' AS edit_image, -1 AS address_request_id, decode(pay.site_use_type, 'PAY', 'Y', 'N') AS pay_flag, decode(pur.site_use_type, 'PURCHASING', 'Y', 'N') AS pur_flag, decode(rfq.site_use_type, 'RFQ', 'Y', 'N') AS rfq_flag, 'TCA' AS address_type, hzl.address1 || ' , ' || hzl.address2 || ' , ' || hzl.address3 || ' , ' || hzl.address4 || ' , ' || hzl.city || ' , ' || hzl.county || ' , ' || hzl.state || ' , ' || hzl.province || ' , ' || hzl.postal_code || ' , ' || fvl.territory_short_name AS address_detail_int FROM hz_party_sites hps, hz_locations hzl, fnd_territories_vl fvl, hz_contact_points email, hz_contact_points phone, hz_contact_points fax, hz_party_site_uses pay, hz_party_site_uses pur, hz_party_site_uses rfq WHERE hps.status = 'A' AND hps.party_id = 2540985 --and hps.created_by_module like 'POS%' AND hzl.country = fvl.territory_code AND email.owner_table_id(+) = hps.party_site_id AND email.owner_table_name(+) = 'HZ_PARTY_SITES' AND email.status(+) = 'A' AND email.contact_point_type(+) = 'EMAIL' AND email.primary_flag(+) = 'Y' AND phone.owner_table_id(+) = hps.party_site_id AND phone.owner_table_name(+) = 'HZ_PARTY_SITES' AND phone.status(+) = 'A' AND phone.contact_point_type(+) = 'PHONE' AND phone.phone_line_type(+) = 'GEN' AND phone.primary_flag(+) = 'Y' AND fax.owner_table_id(+) = hps.party_site_id AND fax.owner_table_name(+) = 'HZ_PARTY_SITES' AND fax.status(+) = 'A' AND fax.contact_point_type(+) = 'PHONE' AND fax.phone_line_type(+) = 'FAX' AND hps.location_id = hzl.location_id AND pay.party_site_id(+) = hps.party_site_id AND pur.party_site_id(+) = hps.party_site_id AND rfq.party_site_id(+) = hps.party_site_id AND pay.status(+) = 'A' AND pur.status(+) = 'A' AND rfq.status(+) = 'A' AND nvl(pay.end_date(+), SYSDATE) >= SYSDATE AND nvl(pur.end_date(+), SYSDATE) >= SYSDATE AND nvl(rfq.end_date(+), SYSDATE) >= SYSDATE AND nvl(pay.begin_date(+), SYSDATE) <= SYSDATE AND nvl(pur.begin_date(+), SYSDATE) <= SYSDATE AND nvl(rfq.begin_date(+), SYSDATE) <= SYSDATE AND pay.site_use_type(+) = 'PAY' AND pur.site_use_type(+) = 'PURCHASING' AND rfq.site_use_type(+) = 'RFQ' AND NOT EXISTS (SELECT 1 FROM pos_address_requests par, pos_supplier_mappings psm WHERE psm.party_id = hps.party_id AND psm.mapping_id = par.mapping_id AND party_site_id = hps.party_site_id AND request_status = 'PENDING' AND request_type IN ('UPDATE', 'DELETE')) UNION ALL SELECT hps.party_site_id, hps.party_site_name AS address_name, decode(par.request_type, 'UPDATE', 'CHANGED', 'DELETE', 'CHANGED') AS status, hzl.address1 AS loc_address1, hzl.address2 AS loc_address2, hzl.address3 AS loc_address3, hzl.city AS loc_city, hzl.county AS loc_county, hzl.state AS loc_state, hzl.province AS loc_province, hzl.postal_code AS loc_postal_code, hzl.country AS loc_country, fvl.territory_short_name AS country_name, hzl.address4 AS loc_address4, email.email_address, phone.raw_phone_number AS phone_number, fax.raw_phone_number AS fax_number, 'removeInActiveImage' AS remove_image, decode(par.request_type, 'UPDATE', 'mngSites', 'DELETE', 'mngSitesDisabled') AS edit_image, par.address_request_id AS address_request_id, decode(pay.site_use_type, 'PAY', 'Y', 'N') AS pay_flag, decode(pur.site_use_type, 'PURCHASING', 'Y', 'N') AS pur_flag, decode(rfq.site_use_type, 'RFQ', 'Y', 'N') AS rfq_flag, 'TCA' AS address_type, hzl.address1 || ' , ' || hzl.address2 || ' , ' || hzl.address3 || ' , ' || hzl.address4 || ' , ' || hzl.city || ' , ' || hzl.county || ' , ' || hzl.state || ' , ' || hzl.province || ' , ' || hzl.postal_code || ' , ' || fvl.territory_short_name AS address_detail_int FROM hz_party_sites hps, hz_locations hzl, fnd_territories_vl fvl, hz_contact_points email, hz_contact_points phone, hz_contact_points fax, pos_address_requests par, pos_supplier_mappings psm, hz_party_site_uses pay, hz_party_site_uses pur, hz_party_site_uses rfq WHERE hps.status = 'A' AND hps.party_id = 2540985 --and hps.created_by_module like 'POS%' AND hzl.country = fvl.territory_code AND email.owner_table_id(+) = hps.party_site_id AND email.owner_table_name(+) = 'HZ_PARTY_SITES' AND email.status(+) = 'A' AND email.contact_point_type(+) = 'EMAIL' AND email.primary_flag(+) = 'Y' AND phone.owner_table_id(+) = hps.party_site_id AND phone.owner_table_name(+) = 'HZ_PARTY_SITES' AND phone.status(+) = 'A' AND phone.contact_point_type(+) = 'PHONE' AND phone.phone_line_type(+) = 'GEN' AND phone.primary_flag(+) = 'Y' AND fax.owner_table_id(+) = hps.party_site_id AND fax.owner_table_name(+) = 'HZ_PARTY_SITES' AND fax.status(+) = 'A' AND fax.contact_point_type(+) = 'PHONE' AND fax.phone_line_type(+) = 'FAX' AND hps.location_id = hzl.location_id AND par.party_site_id = hps.party_site_id AND psm.party_id = hps.party_id AND psm.mapping_id = par.mapping_id AND par.request_type IN ('UPDATE', 'DELETE') AND par.request_status = 'PENDING' AND pay.party_site_id(+) = hps.party_site_id AND pur.party_site_id(+) = hps.party_site_id AND rfq.party_site_id(+) = hps.party_site_id AND pay.status(+) = 'A' AND pur.status(+) = 'A' AND rfq.status(+) = 'A' AND nvl(pay.end_date(+), SYSDATE) >= SYSDATE AND nvl(pur.end_date(+), SYSDATE) >= SYSDATE AND nvl(rfq.end_date(+), SYSDATE) >= SYSDATE AND nvl(pay.begin_date(+), SYSDATE) <= SYSDATE AND nvl(pur.begin_date(+), SYSDATE) <= SYSDATE AND nvl(rfq.begin_date(+), SYSDATE) <= SYSDATE AND pay.site_use_type(+) = 'PAY' AND pur.site_use_type(+) = 'PURCHASING' AND rfq.site_use_type(+) = 'RFQ' UNION ALL SELECT par.party_site_id, par.party_site_name AS address_name, decode(par.request_type, 'ADD', 'NEW', 'UPDATE', 'CHANGED', 'UNKNOWN') AS status, par.address_line1 AS loc_address1, par.address_line2 AS loc_address2, par.address_line3 AS loc_address3, par.city AS loc_city, par.county AS loc_county, par.state AS loc_state, par.province AS loc_province, par.postal_code AS loc_postal_code, par.country AS loc_country, fvl.territory_short_name AS country_name, par.address_line4 AS loc_address4, par.email_address, par.phone_area_code || ' ' || par.phone_number || ' ' || par.phone_extension AS phone_number, par.fax_area_code || ' ' || par.fax_number AS fax_number, 'removeInActiveImage' AS remove_image, decode(par.request_type, 'UPDATE', 'mngSites', 'mngSitesDisabled') AS edit_image, par.address_request_id AS address_request_id, decode(par.pay_flag, 'Y', 'Y', 'N') AS pay_flag, decode(par.pur_flag, 'Y', 'Y', 'N') AS pur_flag, decode(par.rfq_flag, 'Y', 'Y', 'N') AS rfq_flag, 'POS' AS address_type, par.address_line1 || ' , ' || par.address_line2 || ' , ' || par.address_line3 || ' , ' || par.address_line4 || ' , ' || par.city || ' , ' || par.county || ' , ' || par.state || ' , ' || par.province || ' , ' || par.postal_code || ' , ' || fvl.territory_short_name AS address_detail_int FROM pos_address_requests par, pos_supplier_mappings psm, fnd_territories_vl fvl WHERE par.mapping_id = psm.mapping_id AND par.country = fvl.territory_code AND psm.party_id = 2540985 AND par.request_status = 'PENDING' AND par.request_type = 'ADD'
--供应商查看地址簿信息 SELECT hps.party_site_id, hps.party_site_name AS address_name --,'CURRENT' AS status , decode('CURRENT', 'NEW', '新建', 'CURRENT', '当前', 'CHANGED', '更改待定', 'INACTIVE', '无效', NULL) AS status, hzl.address1 AS loc_address1, hzl.address2 AS loc_address2, hzl.address3 AS loc_address3, hzl.city AS loc_city, hzl.county AS loc_county, hzl.state AS loc_state, hzl.province AS loc_province, hzl.postal_code AS loc_postal_code, hzl.country AS loc_country, fvl.territory_short_name AS country_name, hzl.address4 AS loc_address4, email.email_address, phone.raw_phone_number AS phone_number, fax.raw_phone_number AS fax_number, 'removeActiveImage' AS remove_image, 'editActiveImage' AS edit_image, -1 AS address_request_id FROM hz_party_sites hps, hz_locations hzl, fnd_territories_vl fvl, hz_contact_points email, hz_contact_points phone, hz_contact_points fax WHERE hps.status = 'A' AND hps.party_id = 2540985 --and hps.created_by_module like 'POS%' AND hzl.country = fvl.territory_code AND email.owner_table_id(+) = hps.party_site_id AND email.owner_table_name(+) = 'HZ_PARTY_SITES' AND email.status(+) = 'A' AND email.contact_point_type(+) = 'EMAIL' AND email.primary_flag(+) = 'Y' AND phone.owner_table_id(+) = hps.party_site_id AND phone.owner_table_name(+) = 'HZ_PARTY_SITES' AND phone.status(+) = 'A' AND phone.contact_point_type(+) = 'PHONE' AND phone.phone_line_type(+) = 'GEN' AND phone.primary_flag(+) = 'Y' AND fax.owner_table_id(+) = hps.party_site_id AND fax.owner_table_name(+) = 'HZ_PARTY_SITES' AND fax.status(+) = 'A' AND fax.contact_point_type(+) = 'PHONE' AND fax.phone_line_type(+) = 'FAX' AND hps.location_id = hzl.location_id AND NOT EXISTS (SELECT 1 FROM pos_address_requests WHERE party_site_id = hps.party_site_id AND request_status = 'PENDING') UNION SELECT par.party_site_id, par.party_site_name AS address_name --,par.request_type AS status , decode(decode(par.request_type, 'ADD', 'NEW', 'UPDATE', 'CHANGED', 'UNKNOWN'), 'NEW', '新建', 'CURRENT', '当前', 'CHANGED', '更改待定', 'INACTIVE', '无效', NULL) AS status, par.address_line1 AS loc_address1, par.address_line2 AS loc_address2, par.address_line3 AS loc_address3, par.city AS loc_city, par.county AS loc_county, par.state AS loc_state, par.province AS loc_province, par.postal_code AS loc_postal_code, par.country AS loc_country, fvl.territory_short_name AS country_name, par.address_line4 AS loc_address4, par.email_address, par.phone_area_code || ' ' || par.phone_number || ' ' || par.phone_extension AS phone_number, par.fax_area_code || ' ' || par.fax_number AS fax_number, 'removeActiveImage' AS remove_image, 'editActiveImage' AS edit_image, par.address_request_id AS address_request_id FROM pos_address_requests par, pos_supplier_mappings psm, fnd_territories_vl fvl WHERE par.mapping_id = psm.mapping_id AND par.country = fvl.territory_code AND psm.party_id = 2540985 AND par.request_status = 'PENDING' AND par.request_type IN ('ADD', 'UPDATE')