POZ_SUPPLIER_ADDRESS

Details

Schema: FUSION
Object owner: POZ
Object type: VIEW

Columns

Name
PARTY_SITE_ID VENDOR_ID PARTY_ID PARTY_SITE_NAME LAST_UPDATE_DATE LAST_UPDATED_BY CREATION_DATE CREATED_BY LAST_UPDATE_LOGIN OBJECT_VERSION_NUMBER LOCATION_LANGUAGE ADDRESSEE GLOBAL_LOCATION_NUMBER ATTRIBUTE_CATEGORY ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 ATTRIBUTE26 ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE30 ATTRIBUTE_NUMBER1 ATTRIBUTE_NUMBER2 ATTRIBUTE_NUMBER3 ATTRIBUTE_NUMBER4 ATTRIBUTE_NUMBER5 ATTRIBUTE_NUMBER6 ATTRIBUTE_NUMBER7 ATTRIBUTE_NUMBER8 ATTRIBUTE_NUMBER9 ATTRIBUTE_NUMBER10 ATTRIBUTE_NUMBER11 ATTRIBUTE_NUMBER12 ATTRIBUTE_DATE1 ATTRIBUTE_DATE2 ATTRIBUTE_DATE3 ATTRIBUTE_DATE4 ATTRIBUTE_DATE5 ATTRIBUTE_DATE6 ATTRIBUTE_DATE7 ATTRIBUTE_DATE8 ATTRIBUTE_DATE9 ATTRIBUTE_DATE10 ATTRIBUTE_DATE11 ATTRIBUTE_DATE12 COUNTRY LOCATION_ID ADDRESS1 ADDRESS2 ADDRESS3 ADDRESS4 CITY COUNTY STATE PROVINCE POSTAL_CODE BUILDING FLOOR_NUMBER ADDR_ELEMENT_ATTRIBUTE1 ADDR_ELEMENT_ATTRIBUTE2 ADDR_ELEMENT_ATTRIBUTE3 ADDR_ELEMENT_ATTRIBUTE4 ADDR_ELEMENT_ATTRIBUTE5 POSTAL_PLUS4_CODE ADDRESS_LINES_PHONETIC INACTIVE_DATE PHONE_CONTACT_POINT_ID PHONE_CONTACT_POINT_TYPE PHONE_COUNTRY_CODE PHONE_AREA_CODE PHONE_NUMBER PHONE_EXTENSION PHONE_PRIMARY_FLAG PHONE_OVERALL_PRIMARY_FLAG FAX_CONTACT_POINT_ID FAX_COUNTRY_CODE FAX_CONTACT_POINT_TYPE FAX_PHONE_AREA_CODE FAX_PHONE_NUMBER FAX_PRIMARY_FLAG FAX_OVERALL_PRIMARY_FLAG EMAIL_CONTACT_POINT_ID EMAIL_ADDRESS EMAIL_CONTACT_POINT_TYPE EMAIL_PRIMARY_FLAG EMAIL_OVERALL_PRIMARY_FLAG ADDRESS_PURPOSE_ORDERING PUR_PARTY_SITE_USE_ID ADDRESS_PURPOSE_REMIT_TO PAY_PARTY_SITE_USE_ID ADDRESS_PURPOSE_RFQ_OR_BIDDING STATUS

Name

PARTY_SITE_ID

VENDOR_ID

PARTY_ID

PARTY_SITE_NAME

LAST_UPDATE_DATE

LAST_UPDATED_BY

CREATION_DATE

CREATED_BY

LAST_UPDATE_LOGIN

OBJECT_VERSION_NUMBER

LOCATION_LANGUAGE

ADDRESSEE

GLOBAL_LOCATION_NUMBER

ATTRIBUTE_CATEGORY

ATTRIBUTE1

ATTRIBUTE2

ATTRIBUTE3

ATTRIBUTE4

ATTRIBUTE5

ATTRIBUTE6

ATTRIBUTE7

ATTRIBUTE8

ATTRIBUTE9

ATTRIBUTE10

ATTRIBUTE11

ATTRIBUTE12

ATTRIBUTE13

ATTRIBUTE14

ATTRIBUTE15

ATTRIBUTE16

ATTRIBUTE17

ATTRIBUTE18

ATTRIBUTE19

ATTRIBUTE20

ATTRIBUTE21

ATTRIBUTE22

ATTRIBUTE23

ATTRIBUTE24

ATTRIBUTE25

ATTRIBUTE26

ATTRIBUTE27

ATTRIBUTE28

ATTRIBUTE29

ATTRIBUTE30

ATTRIBUTE_NUMBER1

ATTRIBUTE_NUMBER2

ATTRIBUTE_NUMBER3

ATTRIBUTE_NUMBER4

ATTRIBUTE_NUMBER5

ATTRIBUTE_NUMBER6

ATTRIBUTE_NUMBER7

ATTRIBUTE_NUMBER8

ATTRIBUTE_NUMBER9

ATTRIBUTE_NUMBER10

ATTRIBUTE_NUMBER11

ATTRIBUTE_NUMBER12

ATTRIBUTE_DATE1

ATTRIBUTE_DATE2

ATTRIBUTE_DATE3

ATTRIBUTE_DATE4

ATTRIBUTE_DATE5

ATTRIBUTE_DATE6

ATTRIBUTE_DATE7

ATTRIBUTE_DATE8

ATTRIBUTE_DATE9

ATTRIBUTE_DATE10

ATTRIBUTE_DATE11

ATTRIBUTE_DATE12

COUNTRY

LOCATION_ID

ADDRESS1

ADDRESS2

ADDRESS3

ADDRESS4

CITY

COUNTY

STATE

PROVINCE

POSTAL_CODE

BUILDING

FLOOR_NUMBER

ADDR_ELEMENT_ATTRIBUTE1

ADDR_ELEMENT_ATTRIBUTE2

ADDR_ELEMENT_ATTRIBUTE3

ADDR_ELEMENT_ATTRIBUTE4

ADDR_ELEMENT_ATTRIBUTE5

POSTAL_PLUS4_CODE

ADDRESS_LINES_PHONETIC

INACTIVE_DATE

PHONE_CONTACT_POINT_ID

PHONE_CONTACT_POINT_TYPE

PHONE_COUNTRY_CODE

PHONE_AREA_CODE

PHONE_NUMBER

PHONE_EXTENSION

PHONE_PRIMARY_FLAG

PHONE_OVERALL_PRIMARY_FLAG

FAX_CONTACT_POINT_ID

FAX_COUNTRY_CODE

FAX_CONTACT_POINT_TYPE

FAX_PHONE_AREA_CODE

FAX_PHONE_NUMBER

FAX_PRIMARY_FLAG

FAX_OVERALL_PRIMARY_FLAG

EMAIL_CONTACT_POINT_ID

EMAIL_ADDRESS

EMAIL_CONTACT_POINT_TYPE

EMAIL_PRIMARY_FLAG

EMAIL_OVERALL_PRIMARY_FLAG

ADDRESS_PURPOSE_ORDERING

PUR_PARTY_SITE_USE_ID

ADDRESS_PURPOSE_REMIT_TO

PAY_PARTY_SITE_USE_ID

ADDRESS_PURPOSE_RFQ_OR_BIDDING

STATUS

Query

SQL_Statement
SELECT PartySitePEO.party_site_id, SupplierPEO.VENDOR_ID, PartySitePEO.PARTY_ID, PartySitePEO.PARTY_SITE_NAME, PartySitePEO.LAST_UPDATE_DATE, PartySitePEO.LAST_UPDATED_BY, PartySitePEO.CREATION_DATE, PartySitePEO.CREATED_BY, PartySitePEO.LAST_UPDATE_LOGIN, PartySitePEO.OBJECT_VERSION_NUMBER, PartySitePEO.PARTY_SITE_LANGUAGE as LOCATION_LANGUAGE, PartySitePEO.ADDRESSEE, PartySitePEO.GLOBAL_LOCATION_NUMBER, PartySitePEO.ATTRIBUTE_CATEGORY, PartySitePEO.ATTRIBUTE1, PartySitePEO.ATTRIBUTE2, PartySitePEO.ATTRIBUTE3, PartySitePEO.ATTRIBUTE4, PartySitePEO.ATTRIBUTE5, PartySitePEO.ATTRIBUTE6, PartySitePEO.ATTRIBUTE7, PartySitePEO.ATTRIBUTE8, PartySitePEO.ATTRIBUTE9, PartySitePEO.ATTRIBUTE10, PartySitePEO.ATTRIBUTE11, PartySitePEO.ATTRIBUTE12, PartySitePEO.ATTRIBUTE13, PartySitePEO.ATTRIBUTE14, PartySitePEO.ATTRIBUTE15, PartySitePEO.ATTRIBUTE16, PartySitePEO.ATTRIBUTE17, PartySitePEO.ATTRIBUTE18, PartySitePEO.ATTRIBUTE19, PartySitePEO.ATTRIBUTE20, PartySitePEO.ATTRIBUTE21, PartySitePEO.ATTRIBUTE22, PartySitePEO.ATTRIBUTE23, PartySitePEO.ATTRIBUTE24, PartySitePEO.ATTRIBUTE25, PartySitePEO.ATTRIBUTE26, PartySitePEO.ATTRIBUTE27, PartySitePEO.ATTRIBUTE28, PartySitePEO.ATTRIBUTE29, PartySitePEO.ATTRIBUTE30, PartySitePEO.ATTRIBUTE_NUMBER1, PartySitePEO.ATTRIBUTE_NUMBER2, PartySitePEO.ATTRIBUTE_NUMBER3, PartySitePEO.ATTRIBUTE_NUMBER4, PartySitePEO.ATTRIBUTE_NUMBER5, PartySitePEO.ATTRIBUTE_NUMBER6, PartySitePEO.ATTRIBUTE_NUMBER7, PartySitePEO.ATTRIBUTE_NUMBER8, PartySitePEO.ATTRIBUTE_NUMBER9, PartySitePEO.ATTRIBUTE_NUMBER10, PartySitePEO.ATTRIBUTE_NUMBER11, PartySitePEO.ATTRIBUTE_NUMBER12, PartySitePEO.ATTRIBUTE_DATE1, PartySitePEO.ATTRIBUTE_DATE2, PartySitePEO.ATTRIBUTE_DATE3, PartySitePEO.ATTRIBUTE_DATE4, PartySitePEO.ATTRIBUTE_DATE5, PartySitePEO.ATTRIBUTE_DATE6, PartySitePEO.ATTRIBUTE_DATE7, PartySitePEO.ATTRIBUTE_DATE8, PartySitePEO.ATTRIBUTE_DATE9, PartySitePEO.ATTRIBUTE_DATE10, PartySitePEO.ATTRIBUTE_DATE11, PartySitePEO.ATTRIBUTE_DATE12, LocationPEO.COUNTRY, LocationPEO.LOCATION_ID, LocationPEO.ADDRESS1, LocationPEO.ADDRESS2, LocationPEO.ADDRESS3, LocationPEO.ADDRESS4, LocationPEO.CITY, LocationPEO.COUNTY, LocationPEO.STATE, LocationPEO.PROVINCE, LocationPEO.POSTAL_CODE, LocationPEO.BUILDING, LocationPEO.FLOOR_NUMBER, LocationPEO.ADDR_ELEMENT_ATTRIBUTE1, LocationPEO.ADDR_ELEMENT_ATTRIBUTE2, LocationPEO.ADDR_ELEMENT_ATTRIBUTE3, LocationPEO.ADDR_ELEMENT_ATTRIBUTE4, LocationPEO.ADDR_ELEMENT_ATTRIBUTE5, LocationPEO.POSTAL_PLUS4_CODE, LocationPEO.ADDRESS_LINES_PHONETIC, ( CASE WHEN TRUNC(PartySitePEO.END_DATE_ACTIVE) = TRUNC(to_date('31-12-4712','DD-MM-YYYY')) THEN NULL ELSE PartySitePEO.end_date_active END) AS INACTIVE_DATE, PhonePEO.CONTACT_POINT_ID AS PHONE_CONTACT_POINT_ID, PhonePEO.CONTACT_POINT_TYPE AS PHONE_CONTACT_POINT_TYPE, PhonePEO.PHONE_COUNTRY_CODE, PhonePEO.PHONE_AREA_CODE, PhonePEO.PHONE_NUMBER, PhonePEO.PHONE_EXTENSION, PhonePEO.PRIMARY_FLAG PHONE_PRIMARY_FLAG, PhonePEO.OVERALL_PRIMARY_FLAG PHONE_OVERALL_PRIMARY_FLAG, FaxPEO.CONTACT_POINT_ID AS FAX_CONTACT_POINT_ID, FaxPEO.PHONE_COUNTRY_CODE AS FAX_COUNTRY_CODE, FaxPEO.CONTACT_POINT_TYPE AS FAX_CONTACT_POINT_TYPE, FaxPEO.PHONE_AREA_CODE AS FAX_PHONE_AREA_CODE, FaxPEO.PHONE_NUMBER AS FAX_PHONE_NUMBER, FaxPEO.PRIMARY_FLAG FAX_PRIMARY_FLAG, FaxPEO.OVERALL_PRIMARY_FLAG FAX_OVERALL_PRIMARY_FLAG, EmailPEO.CONTACT_POINT_ID AS EMAIL_CONTACT_POINT_ID, EmailPEO.EMAIL_ADDRESS, EmailPEO.CONTACT_POINT_TYPE AS EMAIL_CONTACT_POINT_TYPE, EmailPEO.PRIMARY_FLAG EMAIL_PRIMARY_FLAG, EmailPEO.OVERALL_PRIMARY_FLAG EMAIL_OVERALL_PRIMARY_FLAG, DECODE(PurUsePEO.SITE_USE_TYPE,NULL,'N','Y') AS ADDRESS_PURPOSE_ORDERING, PurUsePEO.PARTY_SITE_USE_ID AS PUR_PARTY_SITE_USE_ID, DECODE(PayUsePEO.SITE_USE_TYPE,NULL,'N','Y') AS ADDRESS_PURPOSE_REMIT_TO, PayUsePEO.PARTY_SITE_USE_ID AS PAY_PARTY_SITE_USE_ID, DECODE(RfqUsePEO.SITE_USE_TYPE,NULL,'N','Y') AS ADDRESS_PURPOSE_RFQ_OR_BIDDING, StatusLookup.displayed_field AS STATUS FROM HZ_PARTY_SITES PartySitePEO, POZ_SUPPLIERS SupplierPEO, HZ_LOCATIONS LocationPEO, HZ_CONTACT_POINTS PhonePEO, HZ_CONTACT_POINTS FaxPEO, HZ_CONTACT_POINTS EmailPEO, HZ_PARTY_SITE_USES PayUsePEO, HZ_PARTY_SITE_USES PurUsePEO, HZ_PARTY_SITE_USES RfqUsePEO, POZ_LOOKUP_CODES StatusLookup WHERE PartySitePEO.STATUS = 'A' AND LocationPEO.LOCATION_ID =PartySitePEO.LOCATION_ID AND SupplierPEO.party_id = PartySitePEO.party_id AND(PayUsePEO.PARTY_SITE_ID(+) =PartySitePEO.PARTY_SITE_ID AND PayUsePEO.STATUS(+) ='A' AND PayUsePEO.SITE_USE_TYPE(+) ='PAY') AND(PurUsePEO.PARTY_SITE_ID(+) =PartySitePEO.PARTY_SITE_ID AND PurUsePEO.STATUS(+) ='A' AND PurUsePEO.SITE_USE_TYPE(+) ='PURCHASING') AND(RfqUsePEO.PARTY_SITE_ID(+) =PartySitePEO.PARTY_SITE_ID AND RfqUsePEO.STATUS(+) ='A' AND RfqUsePEO.SITE_USE_TYPE(+) ='RFQ') AND(PhonePEO.OWNER_TABLE_ID(+) =PartySitePEO.PARTY_SITE_ID AND PhonePEO.OWNER_TABLE_NAME(+) ='HZ_PARTY_SITES' AND PhonePEO.STATUS(+) ='A' AND PhonePEO.CONTACT_POINT_TYPE(+)='PHONE' AND PhonePEO.PHONE_LINE_TYPE(+) ='GEN') AND(FaxPEO.OWNER_TABLE_ID(+) =PartySitePEO.PARTY_SITE_ID AND FaxPEO.OWNER_TABLE_NAME(+) ='HZ_PARTY_SITES' AND FaxPEO.STATUS(+) ='A' AND FaxPEO.CONTACT_POINT_TYPE(+) ='PHONE' AND FaxPEO.PHONE_LINE_TYPE(+) ='FAX') AND (EmailPEO.OWNER_TABLE_ID(+) =PartySitePEO.PARTY_SITE_ID AND EmailPEO.OWNER_TABLE_NAME(+) ='HZ_PARTY_SITES' AND EmailPEO.STATUS(+) ='A' AND EMAILPEO.CONTACT_POINT_TYPE(+)='EMAIL' AND EmailPEO.PRIMARY_FLAG(+) ='Y') AND StatusLookup.lookup_type(+) = 'POZ_STATUS' AND ( CASE WHEN sysdate >= NVL(PartySitePEO.start_date_active, sysdate-1) AND sysdate < NVL(PartySitePEO.end_date_active, sysdate +1) THEN 'ACTIVE' ELSE 'INACTIVE' END) = StatusLookup.lookup_code(+)

SQL_Statement

SELECT PartySitePEO.party_site_id,

SupplierPEO.VENDOR_ID,

PartySitePEO.PARTY_ID,

PartySitePEO.PARTY_SITE_NAME,

PartySitePEO.LAST_UPDATE_DATE,

PartySitePEO.LAST_UPDATED_BY,

PartySitePEO.CREATION_DATE,

PartySitePEO.CREATED_BY,

PartySitePEO.LAST_UPDATE_LOGIN,

PartySitePEO.OBJECT_VERSION_NUMBER,

PartySitePEO.PARTY_SITE_LANGUAGE as LOCATION_LANGUAGE,

PartySitePEO.ADDRESSEE,

PartySitePEO.GLOBAL_LOCATION_NUMBER,

PartySitePEO.ATTRIBUTE_CATEGORY,

PartySitePEO.ATTRIBUTE1,

PartySitePEO.ATTRIBUTE2,

PartySitePEO.ATTRIBUTE3,

PartySitePEO.ATTRIBUTE4,

PartySitePEO.ATTRIBUTE5,

PartySitePEO.ATTRIBUTE6,

PartySitePEO.ATTRIBUTE7,

PartySitePEO.ATTRIBUTE8,

PartySitePEO.ATTRIBUTE9,

PartySitePEO.ATTRIBUTE10,

PartySitePEO.ATTRIBUTE11,

PartySitePEO.ATTRIBUTE12,

PartySitePEO.ATTRIBUTE13,

PartySitePEO.ATTRIBUTE14,

PartySitePEO.ATTRIBUTE15,

PartySitePEO.ATTRIBUTE16,

PartySitePEO.ATTRIBUTE17,

PartySitePEO.ATTRIBUTE18,

PartySitePEO.ATTRIBUTE19,

PartySitePEO.ATTRIBUTE20,

PartySitePEO.ATTRIBUTE21,

PartySitePEO.ATTRIBUTE22,

PartySitePEO.ATTRIBUTE23,

PartySitePEO.ATTRIBUTE24,

PartySitePEO.ATTRIBUTE25,

PartySitePEO.ATTRIBUTE26,

PartySitePEO.ATTRIBUTE27,

PartySitePEO.ATTRIBUTE28,

PartySitePEO.ATTRIBUTE29,

PartySitePEO.ATTRIBUTE30,

PartySitePEO.ATTRIBUTE_NUMBER1,

PartySitePEO.ATTRIBUTE_NUMBER2,

PartySitePEO.ATTRIBUTE_NUMBER3,

PartySitePEO.ATTRIBUTE_NUMBER4,

PartySitePEO.ATTRIBUTE_NUMBER5,

PartySitePEO.ATTRIBUTE_NUMBER6,

PartySitePEO.ATTRIBUTE_NUMBER7,

PartySitePEO.ATTRIBUTE_NUMBER8,

PartySitePEO.ATTRIBUTE_NUMBER9,

PartySitePEO.ATTRIBUTE_NUMBER10,

PartySitePEO.ATTRIBUTE_NUMBER11,

PartySitePEO.ATTRIBUTE_NUMBER12,

PartySitePEO.ATTRIBUTE_DATE1,

PartySitePEO.ATTRIBUTE_DATE2,

PartySitePEO.ATTRIBUTE_DATE3,

PartySitePEO.ATTRIBUTE_DATE4,

PartySitePEO.ATTRIBUTE_DATE5,

PartySitePEO.ATTRIBUTE_DATE6,

PartySitePEO.ATTRIBUTE_DATE7,

PartySitePEO.ATTRIBUTE_DATE8,

PartySitePEO.ATTRIBUTE_DATE9,

PartySitePEO.ATTRIBUTE_DATE10,

PartySitePEO.ATTRIBUTE_DATE11,

PartySitePEO.ATTRIBUTE_DATE12,

LocationPEO.COUNTRY,

LocationPEO.LOCATION_ID,

LocationPEO.ADDRESS1,

LocationPEO.ADDRESS2,

LocationPEO.ADDRESS3,

LocationPEO.ADDRESS4,

LocationPEO.CITY,

LocationPEO.COUNTY,

LocationPEO.STATE,

LocationPEO.PROVINCE,

LocationPEO.POSTAL_CODE,

LocationPEO.BUILDING,

LocationPEO.FLOOR_NUMBER,

LocationPEO.ADDR_ELEMENT_ATTRIBUTE1,

LocationPEO.ADDR_ELEMENT_ATTRIBUTE2,

LocationPEO.ADDR_ELEMENT_ATTRIBUTE3,

LocationPEO.ADDR_ELEMENT_ATTRIBUTE4,

LocationPEO.ADDR_ELEMENT_ATTRIBUTE5,

LocationPEO.POSTAL_PLUS4_CODE,

LocationPEO.ADDRESS_LINES_PHONETIC,

(

CASE

WHEN TRUNC(PartySitePEO.END_DATE_ACTIVE) = TRUNC(to_date('31-12-4712','DD-MM-YYYY'))

THEN NULL

ELSE PartySitePEO.end_date_active

END) AS INACTIVE_DATE,

PhonePEO.CONTACT_POINT_ID AS PHONE_CONTACT_POINT_ID,

PhonePEO.CONTACT_POINT_TYPE AS PHONE_CONTACT_POINT_TYPE,

PhonePEO.PHONE_COUNTRY_CODE,

PhonePEO.PHONE_AREA_CODE,

PhonePEO.PHONE_NUMBER,

PhonePEO.PHONE_EXTENSION,

PhonePEO.PRIMARY_FLAG PHONE_PRIMARY_FLAG,

PhonePEO.OVERALL_PRIMARY_FLAG PHONE_OVERALL_PRIMARY_FLAG,

FaxPEO.CONTACT_POINT_ID AS FAX_CONTACT_POINT_ID,

FaxPEO.PHONE_COUNTRY_CODE AS FAX_COUNTRY_CODE,

FaxPEO.CONTACT_POINT_TYPE AS FAX_CONTACT_POINT_TYPE,

FaxPEO.PHONE_AREA_CODE AS FAX_PHONE_AREA_CODE,

FaxPEO.PHONE_NUMBER AS FAX_PHONE_NUMBER,

FaxPEO.PRIMARY_FLAG FAX_PRIMARY_FLAG,

FaxPEO.OVERALL_PRIMARY_FLAG FAX_OVERALL_PRIMARY_FLAG,

EmailPEO.CONTACT_POINT_ID AS EMAIL_CONTACT_POINT_ID,

EmailPEO.EMAIL_ADDRESS,

EmailPEO.CONTACT_POINT_TYPE AS EMAIL_CONTACT_POINT_TYPE,

EmailPEO.PRIMARY_FLAG EMAIL_PRIMARY_FLAG,

EmailPEO.OVERALL_PRIMARY_FLAG EMAIL_OVERALL_PRIMARY_FLAG,

DECODE(PurUsePEO.SITE_USE_TYPE,NULL,'N','Y') AS ADDRESS_PURPOSE_ORDERING,

PurUsePEO.PARTY_SITE_USE_ID AS PUR_PARTY_SITE_USE_ID,

DECODE(PayUsePEO.SITE_USE_TYPE,NULL,'N','Y') AS ADDRESS_PURPOSE_REMIT_TO,

PayUsePEO.PARTY_SITE_USE_ID AS PAY_PARTY_SITE_USE_ID,

DECODE(RfqUsePEO.SITE_USE_TYPE,NULL,'N','Y') AS ADDRESS_PURPOSE_RFQ_OR_BIDDING,

StatusLookup.displayed_field AS STATUS

FROM HZ_PARTY_SITES PartySitePEO,

POZ_SUPPLIERS SupplierPEO,

HZ_LOCATIONS LocationPEO,

HZ_CONTACT_POINTS PhonePEO,

HZ_CONTACT_POINTS FaxPEO,

HZ_CONTACT_POINTS EmailPEO,

HZ_PARTY_SITE_USES PayUsePEO,

HZ_PARTY_SITE_USES PurUsePEO,

HZ_PARTY_SITE_USES RfqUsePEO,

POZ_LOOKUP_CODES StatusLookup

WHERE PartySitePEO.STATUS = 'A'

AND LocationPEO.LOCATION_ID =PartySitePEO.LOCATION_ID

AND SupplierPEO.party_id = PartySitePEO.party_id

AND(PayUsePEO.PARTY_SITE_ID(+) =PartySitePEO.PARTY_SITE_ID

AND PayUsePEO.STATUS(+) ='A'

AND PayUsePEO.SITE_USE_TYPE(+) ='PAY')

AND(PurUsePEO.PARTY_SITE_ID(+) =PartySitePEO.PARTY_SITE_ID

AND PurUsePEO.STATUS(+) ='A'

AND PurUsePEO.SITE_USE_TYPE(+) ='PURCHASING')

AND(RfqUsePEO.PARTY_SITE_ID(+) =PartySitePEO.PARTY_SITE_ID

AND RfqUsePEO.STATUS(+) ='A'

AND RfqUsePEO.SITE_USE_TYPE(+) ='RFQ')

AND(PhonePEO.OWNER_TABLE_ID(+) =PartySitePEO.PARTY_SITE_ID

AND PhonePEO.OWNER_TABLE_NAME(+) ='HZ_PARTY_SITES'

AND PhonePEO.STATUS(+) ='A'

AND PhonePEO.CONTACT_POINT_TYPE(+)='PHONE'

AND PhonePEO.PHONE_LINE_TYPE(+) ='GEN')

AND(FaxPEO.OWNER_TABLE_ID(+) =PartySitePEO.PARTY_SITE_ID

AND FaxPEO.OWNER_TABLE_NAME(+) ='HZ_PARTY_SITES'

AND FaxPEO.STATUS(+) ='A'

AND FaxPEO.CONTACT_POINT_TYPE(+) ='PHONE'

AND FaxPEO.PHONE_LINE_TYPE(+) ='FAX')

AND (EmailPEO.OWNER_TABLE_ID(+) =PartySitePEO.PARTY_SITE_ID

AND EmailPEO.OWNER_TABLE_NAME(+) ='HZ_PARTY_SITES'

AND EmailPEO.STATUS(+) ='A'

AND EMAILPEO.CONTACT_POINT_TYPE(+)='EMAIL'

AND EmailPEO.PRIMARY_FLAG(+) ='Y')

AND StatusLookup.lookup_type(+) = 'POZ_STATUS'

AND (

CASE

WHEN sysdate >= NVL(PartySitePEO.start_date_active, sysdate-1)

AND sysdate < NVL(PartySitePEO.end_date_active, sysdate +1)

THEN 'ACTIVE'

ELSE 'INACTIVE'

END) = StatusLookup.lookup_code(+)