AP_OFR_SUPPLIERS

Details

Schema: FUSION
Object owner: AP
Object type: VIEW

Columns

Name
VENDOR_INDEX VENDOR_ID VENDOR_NAME VENDOR_SITE_ID VENDOR_SITE_CODE ADDRESS1 ADDRESS2 ADDRESS3 ADDRESS4 CITY POSTAL_CODE STATE COUNTRY CURRENCY_CODE PHONE_NUMBER VENDOR_TYPE UTILITY_FLAG VENDOR_VAT_REGISTRATION_NUM SITE_VAT_REGISTRATION_NUM VENDOR_ATTRIBUTE_CATEGORY SITE_ATTRIBUTE_CATEGORY VENDOR_ATTRIBUTE1 VENDOR_ATTRIBUTE2 VENDOR_ATTRIBUTE3 VENDOR_ATTRIBUTE4 VENDOR_ATTRIBUTE5 VENDOR_ATTRIBUTE6 VENDOR_ATTRIBUTE7 VENDOR_ATTRIBUTE8 VENDOR_ATTRIBUTE9 VENDOR_ATTRIBUTE10 VENDOR_ATTRIBUTE11 VENDOR_ATTRIBUTE12 VENDOR_ATTRIBUTE13 VENDOR_ATTRIBUTE14 VENDOR_ATTRIBUTE15 VENDOR_ATTRIBUTE16 VENDOR_ATTRIBUTE17 VENDOR_ATTRIBUTE18 VENDOR_ATTRIBUTE19 VENDOR_ATTRIBUTE20 SITE_ATTRIBUTE1 SITE_ATTRIBUTE2 SITE_ATTRIBUTE3 SITE_ATTRIBUTE4 SITE_ATTRIBUTE5 SITE_ATTRIBUTE6 SITE_ATTRIBUTE7 SITE_ATTRIBUTE8 SITE_ATTRIBUTE9 SITE_ATTRIBUTE10 SITE_ATTRIBUTE11 SITE_ATTRIBUTE12 SITE_ATTRIBUTE13 SITE_ATTRIBUTE14 SITE_ATTRIBUTE15 SITE_ATTRIBUTE16 SITE_ATTRIBUTE17 SITE_ATTRIBUTE18 SITE_ATTRIBUTE19 SITE_ATTRIBUTE20 FAX_NUMBER EMAIL_ADDRESS CORPORATE_WEBSITE VENDOR_NUMBER VENDOR_ALT_NAME

Name

VENDOR_INDEX

VENDOR_ID

VENDOR_NAME

VENDOR_SITE_ID

VENDOR_SITE_CODE

ADDRESS1

ADDRESS2

ADDRESS3

ADDRESS4

CITY

POSTAL_CODE

STATE

COUNTRY

CURRENCY_CODE

PHONE_NUMBER

VENDOR_TYPE

UTILITY_FLAG

VENDOR_VAT_REGISTRATION_NUM

SITE_VAT_REGISTRATION_NUM

VENDOR_ATTRIBUTE_CATEGORY

SITE_ATTRIBUTE_CATEGORY

VENDOR_ATTRIBUTE1

VENDOR_ATTRIBUTE2

VENDOR_ATTRIBUTE3

VENDOR_ATTRIBUTE4

VENDOR_ATTRIBUTE5

VENDOR_ATTRIBUTE6

VENDOR_ATTRIBUTE7

VENDOR_ATTRIBUTE8

VENDOR_ATTRIBUTE9

VENDOR_ATTRIBUTE10

VENDOR_ATTRIBUTE11

VENDOR_ATTRIBUTE12

VENDOR_ATTRIBUTE13

VENDOR_ATTRIBUTE14

VENDOR_ATTRIBUTE15

VENDOR_ATTRIBUTE16

VENDOR_ATTRIBUTE17

VENDOR_ATTRIBUTE18

VENDOR_ATTRIBUTE19

VENDOR_ATTRIBUTE20

SITE_ATTRIBUTE1

SITE_ATTRIBUTE2

SITE_ATTRIBUTE3

SITE_ATTRIBUTE4

SITE_ATTRIBUTE5

SITE_ATTRIBUTE6

SITE_ATTRIBUTE7

SITE_ATTRIBUTE8

SITE_ATTRIBUTE9

SITE_ATTRIBUTE10

SITE_ATTRIBUTE11

SITE_ATTRIBUTE12

SITE_ATTRIBUTE13

SITE_ATTRIBUTE14

SITE_ATTRIBUTE15

SITE_ATTRIBUTE16

SITE_ATTRIBUTE17

SITE_ATTRIBUTE18

SITE_ATTRIBUTE19

SITE_ATTRIBUTE20

FAX_NUMBER

EMAIL_ADDRESS

CORPORATE_WEBSITE

VENDOR_NUMBER

VENDOR_ALT_NAME

Query

SQL_Statement
SELECT TO_CHAR(PVS.VENDOR_ID) \|\| CHR(126) \|\| TO_CHAR(PVS.VENDOR_SITE_ID) VENDOR_INDEX, PVS.VENDOR_ID, PV.PARTY_NAME VENDOR_NAME, PVS.VENDOR_SITE_ID, PVS.VENDOR_SITE_CODE, PVS.ADDRESS_LINE1 ADDRESS1, PVS.ADDRESS_LINE2 ADDRESS2, PVS.ADDRESS_LINE2 ADDRESS3, PVS.ADDRESS_LINE2 ADDRESS4, PVS.CITY, PVS.ZIP POSTAL_CODE, PVS.STATE, PVS.COUNTRY, PVS.INVOICE_CURRENCY_CODE CURRENCY_CODE, (SELECT HCP.PHONE_COUNTRY_CODE \|\| HCP.PHONE_AREA_CODE \|\| HCP.PHONE_NUMBER FROM HZ_CONTACT_POINTS HCP WHERE HCP.OWNER_TABLE_ID = PVS.PARTY_SITE_ID AND HCP.OWNER_TABLE_NAME = 'HZ_PARTY_SITES' AND HCP.STATUS = 'A' AND HCP.CONTACT_POINT_TYPE = 'PHONE' AND HCP.PHONE_LINE_TYPE ='GEN' AND rownum = 1 ) PHONE_NUMBER, PS.VENDOR_TYPE_LOOKUP_CODE VENDOR_TYPE, DECODE(UPPER(PS.VENDOR_TYPE_LOOKUP_CODE), 'UTILITY', 'YES', 'NO') UTILITY_FLAG, ZP.REP_REGISTRATION_NUMBER VENDOR_VAT_REGISTRATION_NUM, ZPS.REP_REGISTRATION_NUMBER SITE_VAT_REGISTRATION_NUM, PS.ATTRIBUTE_CATEGORY VENDOR_ATTRIBUTE_CATEGORY, PVS.ATTRIBUTE_CATEGORY SITE_ATTRIBUTE_CATEGORY, PS.ATTRIBUTE1 VENDOR_ATTRIBUTE1 , PS.ATTRIBUTE2 VENDOR_ATTRIBUTE2 , PS.ATTRIBUTE3 VENDOR_ATTRIBUTE3 , PS.ATTRIBUTE4 VENDOR_ATTRIBUTE4 , PS.ATTRIBUTE5 VENDOR_ATTRIBUTE5 , PS.ATTRIBUTE6 VENDOR_ATTRIBUTE6 , PS.ATTRIBUTE7 VENDOR_ATTRIBUTE7 , PS.ATTRIBUTE8 VENDOR_ATTRIBUTE8 , PS.ATTRIBUTE9 VENDOR_ATTRIBUTE9 , PS.ATTRIBUTE10 VENDOR_ATTRIBUTE10, PS.ATTRIBUTE11 VENDOR_ATTRIBUTE11, PS.ATTRIBUTE12 VENDOR_ATTRIBUTE12, PS.ATTRIBUTE13 VENDOR_ATTRIBUTE13, PS.ATTRIBUTE14 VENDOR_ATTRIBUTE14, PS.ATTRIBUTE15 VENDOR_ATTRIBUTE15, PS.ATTRIBUTE16 VENDOR_ATTRIBUTE16, PS.ATTRIBUTE17 VENDOR_ATTRIBUTE17, PS.ATTRIBUTE18 VENDOR_ATTRIBUTE18, PS.ATTRIBUTE19 VENDOR_ATTRIBUTE19, PS.ATTRIBUTE20 VENDOR_ATTRIBUTE20, PVS.ATTRIBUTE1 SITE_ATTRIBUTE1 , PVS.ATTRIBUTE2 SITE_ATTRIBUTE2 , PVS.ATTRIBUTE3 SITE_ATTRIBUTE3 , PVS.ATTRIBUTE4 SITE_ATTRIBUTE4 , PVS.ATTRIBUTE5 SITE_ATTRIBUTE5 , PVS.ATTRIBUTE6 SITE_ATTRIBUTE6 , PVS.ATTRIBUTE7 SITE_ATTRIBUTE7 , PVS.ATTRIBUTE8 SITE_ATTRIBUTE8 , PVS.ATTRIBUTE9 SITE_ATTRIBUTE9 , PVS.ATTRIBUTE10 SITE_ATTRIBUTE10, PVS.ATTRIBUTE11 SITE_ATTRIBUTE11, PVS.ATTRIBUTE12 SITE_ATTRIBUTE12, PVS.ATTRIBUTE13 SITE_ATTRIBUTE13, PVS.ATTRIBUTE14 SITE_ATTRIBUTE14, PVS.ATTRIBUTE15 SITE_ATTRIBUTE15, PVS.ATTRIBUTE16 SITE_ATTRIBUTE16, PVS.ATTRIBUTE17 SITE_ATTRIBUTE17, PVS.ATTRIBUTE18 SITE_ATTRIBUTE18, PVS.ATTRIBUTE19 SITE_ATTRIBUTE19, PVS.ATTRIBUTE20 SITE_ATTRIBUTE20, (SELECT HCP.PHONE_COUNTRY_CODE \|\| HCP.PHONE_AREA_CODE \|\| HCP.PHONE_NUMBER FROM HZ_CONTACT_POINTS HCP WHERE HCP.OWNER_TABLE_ID = PVS.PARTY_SITE_ID AND HCP.OWNER_TABLE_NAME = 'HZ_PARTY_SITES' AND HCP.STATUS = 'A' AND HCP.CONTACT_POINT_TYPE = 'PHONE' AND HCP.PHONE_LINE_TYPE ='FAX' AND rownum = 1 ) FAX_NUMBER, (SELECT HCP.EMAIL_ADDRESS FROM HZ_CONTACT_POINTS HCP WHERE HCP.OWNER_TABLE_ID = PVS.PARTY_SITE_ID AND HCP.OWNER_TABLE_NAME = 'HZ_PARTY_SITES' AND HCP.STATUS = 'A' AND HCP.CONTACT_POINT_TYPE = 'EMAIL' AND HCP.PRIMARY_FLAG = 'Y' AND rownum = 1 ) EMAIL_ADDRESS, PS.CORPORATE_WEBSITE, PS.SEGMENT1 VENDOR_NUMBER, (select PARTY_NAME from HZ_ADDTNL_PARTY_NAMES where status_flag='A' and party_name_type= 'PHONETIC' and party_id=PS.PARTY_ID and rownum = 1) AS VENDOR_ALT_NAME FROM HZ_PARTIES PV, POZ_SUPPLIERS PS, POZ_SUPPLIER_SITES_V PVS, ZX_PARTY_TAX_PROFILE zp, ZX_PARTY_TAX_PROFILE zps WHERE PV.PARTY_ID = PS.PARTY_ID AND PS.VENDOR_ID = PVS.VENDOR_ID AND PVS.PAY_SITE_FLAG = 'Y' AND TRUNC(NVL(PVS.INACTIVE_DATE, SYSDATE+1)) > TRUNC(SYSDATE) AND TRUNC(NVL(PS.START_DATE_ACTIVE, SYSDATE)) <= TRUNC(SYSDATE) AND TRUNC(NVL(PS.END_DATE_ACTIVE, SYSDATE+1)) > TRUNC(SYSDATE) AND PS.BUSINESS_RELATIONSHIP = 'SPEND_AUTHORIZED' AND zp.party_id(+) = pv.party_id AND zp.party_type_code(+) = 'THIRD_PARTY' AND zps.party_id(+) = pvs.party_site_id AND zps.party_type_code(+) = 'THIRD_PARTY_SITE'

SQL_Statement

SELECT TO_CHAR(PVS.VENDOR_ID)

|| CHR(126)

|| TO_CHAR(PVS.VENDOR_SITE_ID) VENDOR_INDEX,

PVS.VENDOR_ID,

PV.PARTY_NAME VENDOR_NAME,

PVS.VENDOR_SITE_ID,

PVS.VENDOR_SITE_CODE,

PVS.ADDRESS_LINE1 ADDRESS1,

PVS.ADDRESS_LINE2 ADDRESS2,

PVS.ADDRESS_LINE2 ADDRESS3,

PVS.ADDRESS_LINE2 ADDRESS4,

PVS.CITY,

PVS.ZIP POSTAL_CODE,

PVS.STATE,

PVS.COUNTRY,

PVS.INVOICE_CURRENCY_CODE CURRENCY_CODE,

(SELECT HCP.PHONE_COUNTRY_CODE

|| HCP.PHONE_AREA_CODE

|| HCP.PHONE_NUMBER

FROM HZ_CONTACT_POINTS HCP

WHERE HCP.OWNER_TABLE_ID = PVS.PARTY_SITE_ID

AND HCP.OWNER_TABLE_NAME = 'HZ_PARTY_SITES'

AND HCP.STATUS = 'A'

AND HCP.CONTACT_POINT_TYPE = 'PHONE'

AND HCP.PHONE_LINE_TYPE ='GEN'

AND rownum = 1

) PHONE_NUMBER,

PS.VENDOR_TYPE_LOOKUP_CODE VENDOR_TYPE,

DECODE(UPPER(PS.VENDOR_TYPE_LOOKUP_CODE), 'UTILITY', 'YES', 'NO') UTILITY_FLAG,

ZP.REP_REGISTRATION_NUMBER VENDOR_VAT_REGISTRATION_NUM,

ZPS.REP_REGISTRATION_NUMBER SITE_VAT_REGISTRATION_NUM,

PS.ATTRIBUTE_CATEGORY VENDOR_ATTRIBUTE_CATEGORY,

PVS.ATTRIBUTE_CATEGORY SITE_ATTRIBUTE_CATEGORY,

PS.ATTRIBUTE1 VENDOR_ATTRIBUTE1 ,

PS.ATTRIBUTE2 VENDOR_ATTRIBUTE2 ,

PS.ATTRIBUTE3 VENDOR_ATTRIBUTE3 ,

PS.ATTRIBUTE4 VENDOR_ATTRIBUTE4 ,

PS.ATTRIBUTE5 VENDOR_ATTRIBUTE5 ,

PS.ATTRIBUTE6 VENDOR_ATTRIBUTE6 ,

PS.ATTRIBUTE7 VENDOR_ATTRIBUTE7 ,

PS.ATTRIBUTE8 VENDOR_ATTRIBUTE8 ,

PS.ATTRIBUTE9 VENDOR_ATTRIBUTE9 ,

PS.ATTRIBUTE10 VENDOR_ATTRIBUTE10,

PS.ATTRIBUTE11 VENDOR_ATTRIBUTE11,

PS.ATTRIBUTE12 VENDOR_ATTRIBUTE12,

PS.ATTRIBUTE13 VENDOR_ATTRIBUTE13,

PS.ATTRIBUTE14 VENDOR_ATTRIBUTE14,

PS.ATTRIBUTE15 VENDOR_ATTRIBUTE15,

PS.ATTRIBUTE16 VENDOR_ATTRIBUTE16,

PS.ATTRIBUTE17 VENDOR_ATTRIBUTE17,

PS.ATTRIBUTE18 VENDOR_ATTRIBUTE18,

PS.ATTRIBUTE19 VENDOR_ATTRIBUTE19,

PS.ATTRIBUTE20 VENDOR_ATTRIBUTE20,

PVS.ATTRIBUTE1 SITE_ATTRIBUTE1 ,

PVS.ATTRIBUTE2 SITE_ATTRIBUTE2 ,

PVS.ATTRIBUTE3 SITE_ATTRIBUTE3 ,

PVS.ATTRIBUTE4 SITE_ATTRIBUTE4 ,

PVS.ATTRIBUTE5 SITE_ATTRIBUTE5 ,

PVS.ATTRIBUTE6 SITE_ATTRIBUTE6 ,

PVS.ATTRIBUTE7 SITE_ATTRIBUTE7 ,

PVS.ATTRIBUTE8 SITE_ATTRIBUTE8 ,

PVS.ATTRIBUTE9 SITE_ATTRIBUTE9 ,

PVS.ATTRIBUTE10 SITE_ATTRIBUTE10,

PVS.ATTRIBUTE11 SITE_ATTRIBUTE11,

PVS.ATTRIBUTE12 SITE_ATTRIBUTE12,

PVS.ATTRIBUTE13 SITE_ATTRIBUTE13,

PVS.ATTRIBUTE14 SITE_ATTRIBUTE14,

PVS.ATTRIBUTE15 SITE_ATTRIBUTE15,

PVS.ATTRIBUTE16 SITE_ATTRIBUTE16,

PVS.ATTRIBUTE17 SITE_ATTRIBUTE17,

PVS.ATTRIBUTE18 SITE_ATTRIBUTE18,

PVS.ATTRIBUTE19 SITE_ATTRIBUTE19,

PVS.ATTRIBUTE20 SITE_ATTRIBUTE20,

(SELECT HCP.PHONE_COUNTRY_CODE

|| HCP.PHONE_AREA_CODE

|| HCP.PHONE_NUMBER

FROM HZ_CONTACT_POINTS HCP

WHERE HCP.OWNER_TABLE_ID = PVS.PARTY_SITE_ID

AND HCP.OWNER_TABLE_NAME = 'HZ_PARTY_SITES'

AND HCP.STATUS = 'A'

AND HCP.CONTACT_POINT_TYPE = 'PHONE'

AND HCP.PHONE_LINE_TYPE ='FAX'

AND rownum = 1

) FAX_NUMBER,

(SELECT HCP.EMAIL_ADDRESS

FROM HZ_CONTACT_POINTS HCP

WHERE HCP.OWNER_TABLE_ID = PVS.PARTY_SITE_ID

AND HCP.OWNER_TABLE_NAME = 'HZ_PARTY_SITES'

AND HCP.STATUS = 'A'

AND HCP.CONTACT_POINT_TYPE = 'EMAIL'

AND HCP.PRIMARY_FLAG = 'Y'

AND rownum = 1

) EMAIL_ADDRESS,

PS.CORPORATE_WEBSITE,

PS.SEGMENT1 VENDOR_NUMBER,

(select PARTY_NAME from HZ_ADDTNL_PARTY_NAMES where status_flag='A' and party_name_type= 'PHONETIC' and party_id=PS.PARTY_ID and rownum = 1) AS VENDOR_ALT_NAME

FROM HZ_PARTIES PV,

POZ_SUPPLIERS PS,

POZ_SUPPLIER_SITES_V PVS,

ZX_PARTY_TAX_PROFILE zp,

ZX_PARTY_TAX_PROFILE zps

WHERE PV.PARTY_ID = PS.PARTY_ID

AND PS.VENDOR_ID = PVS.VENDOR_ID

AND PVS.PAY_SITE_FLAG = 'Y'

AND TRUNC(NVL(PVS.INACTIVE_DATE, SYSDATE+1)) > TRUNC(SYSDATE)

AND TRUNC(NVL(PS.START_DATE_ACTIVE, SYSDATE)) <= TRUNC(SYSDATE)

AND TRUNC(NVL(PS.END_DATE_ACTIVE, SYSDATE+1)) > TRUNC(SYSDATE)

AND PS.BUSINESS_RELATIONSHIP = 'SPEND_AUTHORIZED'

AND zp.party_id(+) = pv.party_id

AND zp.party_type_code(+) = 'THIRD_PARTY'

AND zps.party_id(+) = pvs.party_site_id

AND zps.party_type_code(+) = 'THIRD_PARTY_SITE'