POZ_BUSINESS_CLASSIFICATIONS

Details

Schema: FUSION
Object owner: POZ
Object type: VIEW

Columns

Name
SUPPLIER_NAME SEGMENT1 CLASSIFICATION_ID PARTY_ID DISPLAYED_FIELD SUBCLASS STATUS CERTIFICATE_NUMBER CERTIFYING_AGENCY_ID CERTIFYING_AGENCY OTHER_CERTIFYING_AGENCY START_DATE EXPIRATION_DATE PROVIDED_BY_CONTACT_ID CONFIRMED_ON VENDOR_ID NOTES LOOKUP_CODE EXT_ATTR_1 CREATED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_DATE LAST_UPDATE_LOGIN OBJECT_VERSION_NUMBER DELETED

Name

SUPPLIER_NAME

SEGMENT1

CLASSIFICATION_ID

PARTY_ID

DISPLAYED_FIELD

SUBCLASS

STATUS

CERTIFICATE_NUMBER

CERTIFYING_AGENCY_ID

CERTIFYING_AGENCY

OTHER_CERTIFYING_AGENCY

START_DATE

EXPIRATION_DATE

PROVIDED_BY_CONTACT_ID

CONFIRMED_ON

VENDOR_ID

NOTES

LOOKUP_CODE

EXT_ATTR_1

CREATED_BY

CREATION_DATE

LAST_UPDATED_BY

LAST_UPDATE_DATE

LAST_UPDATE_LOGIN

OBJECT_VERSION_NUMBER

DELETED

Query

SQL_Statement
SELECT PARTIES.PARTY_NAME Supplier_Name, SUPP.SEGMENT1, BUSINESSCLASSIFICATIONSPEO.CLASSIFICATION_ID, BUSINESSCLASSIFICATIONSPEO.PARTY_ID, BUSCLASSLKUP.DISPLAYED_FIELD, SUBCLASSLKUP.DISPLAYED_FIELD AS SubClass, BCSTATUSLKUP.DISPLAYED_FIELD AS STATUS, BUSINESSCLASSIFICATIONSPEO.CERTIFICATE_NUMBER, BUSINESSCLASSIFICATIONSPEO.CERTIFYING_AGENCY_ID, POZCERTIFYINGAGENCIESEO.NAME AS CERTIFYING_AGENCY, BUSINESSCLASSIFICATIONSPEO.OTHER_CERTIFYING_AGENCY, BUSINESSCLASSIFICATIONSPEO.START_DATE, BUSINESSCLASSIFICATIONSPEO.EXPIRATION_DATE, BUSINESSCLASSIFICATIONSPEO.PROVIDED_BY_CONTACT_ID, BUSINESSCLASSIFICATIONSPEO.CONFIRMED_ON, BUSINESSCLASSIFICATIONSPEO.VENDOR_ID, BUSINESSCLASSIFICATIONSPEO.NOTES, BUSINESSCLASSIFICATIONSPEO.LOOKUP_CODE, BUSINESSCLASSIFICATIONSPEO.EXT_ATTR_1, BUSINESSCLASSIFICATIONSPEO.CREATED_BY, BUSINESSCLASSIFICATIONSPEO.CREATION_DATE, BUSINESSCLASSIFICATIONSPEO.LAST_UPDATED_BY, BUSINESSCLASSIFICATIONSPEO.LAST_UPDATE_DATE, BUSINESSCLASSIFICATIONSPEO.LAST_UPDATE_LOGIN, BUSINESSCLASSIFICATIONSPEO.OBJECT_VERSION_NUMBER, BUSINESSCLASSIFICATIONSPEO.DELETED FROM POZ_BUS_CLASSIFICATIONS BUSINESSCLASSIFICATIONSPEO, POZ_SUPPLIERS SUPP, HZ_PARTIES PARTIES, POZ_LOOKUP_CODES BUSCLASSLKUP, POZ_LOOKUP_CODES SUBCLASSLKUP, POZ_LOOKUP_CODES BCSTATUSLKUP, POZ_CERTIFYING_AGENCIES POZCERTIFYINGAGENCIESEO WHERE PARTIES.PARTY_ID =SUPP.PARTY_ID AND PARTIES.PARTY_ID =BUSINESSCLASSIFICATIONSPEO.PARTY_ID AND BUSINESSCLASSIFICATIONSPEO.LOOKUP_CODE =BUSCLASSLKUP.LOOKUP_CODE(+) AND BUSCLASSLKUP.LOOKUP_TYPE(+) = 'POZ_BUSINESS_CLASSIFICATIONS' AND SUBCLASSLKUP.LOOKUP_TYPE(+) ='POZ_MINORITY_GROUP' AND BCSTATUSLKUP.LOOKUP_TYPE = 'ORA_POZ_BUS_CLASS_STATUS' AND BCSTATUSLKUP.LOOKUP_CODE = (CASE WHEN (TRUNC(SYSDATE) BETWEEN (NVL(TRUNC(BUSINESSCLASSIFICATIONSPEO.START_DATE), TRUNC(SYSDATE))) AND NVL(TRUNC(BUSINESSCLASSIFICATIONSPEO.EXPIRATION_DATE), TRUNC(SYSDATE)) ) THEN 'CURRENT' WHEN TRUNC(BUSINESSCLASSIFICATIONSPEO.START_DATE) > TRUNC(SYSDATE) THEN 'FUTURE_DATED' WHEN TRUNC(BUSINESSCLASSIFICATIONSPEO.EXPIRATION_DATE) < TRUNC(SYSDATE) THEN 'EXPIRED' END) AND BUSINESSCLASSIFICATIONSPEO.EXT_ATTR_1 =SUBCLASSLKUP.LOOKUP_CODE(+) AND POZCERTIFYINGAGENCIESEO.AGENCY_ID(+) = BUSINESSCLASSIFICATIONSPEO.CERTIFYING_AGENCY_ID AND NVL(BUSINESSCLASSIFICATIONSPEO.DELETED,'N') = 'N'

SQL_Statement

SELECT PARTIES.PARTY_NAME Supplier_Name,

SUPP.SEGMENT1,

BUSINESSCLASSIFICATIONSPEO.CLASSIFICATION_ID,

BUSINESSCLASSIFICATIONSPEO.PARTY_ID,

BUSCLASSLKUP.DISPLAYED_FIELD,

SUBCLASSLKUP.DISPLAYED_FIELD AS SubClass,

BCSTATUSLKUP.DISPLAYED_FIELD AS STATUS,

BUSINESSCLASSIFICATIONSPEO.CERTIFICATE_NUMBER,

BUSINESSCLASSIFICATIONSPEO.CERTIFYING_AGENCY_ID,

POZCERTIFYINGAGENCIESEO.NAME AS CERTIFYING_AGENCY,

BUSINESSCLASSIFICATIONSPEO.OTHER_CERTIFYING_AGENCY,

BUSINESSCLASSIFICATIONSPEO.START_DATE,

BUSINESSCLASSIFICATIONSPEO.EXPIRATION_DATE,

BUSINESSCLASSIFICATIONSPEO.PROVIDED_BY_CONTACT_ID,

BUSINESSCLASSIFICATIONSPEO.CONFIRMED_ON,

BUSINESSCLASSIFICATIONSPEO.VENDOR_ID,

BUSINESSCLASSIFICATIONSPEO.NOTES,

BUSINESSCLASSIFICATIONSPEO.LOOKUP_CODE,

BUSINESSCLASSIFICATIONSPEO.EXT_ATTR_1,

BUSINESSCLASSIFICATIONSPEO.CREATED_BY,

BUSINESSCLASSIFICATIONSPEO.CREATION_DATE,

BUSINESSCLASSIFICATIONSPEO.LAST_UPDATED_BY,

BUSINESSCLASSIFICATIONSPEO.LAST_UPDATE_DATE,

BUSINESSCLASSIFICATIONSPEO.LAST_UPDATE_LOGIN,

BUSINESSCLASSIFICATIONSPEO.OBJECT_VERSION_NUMBER,

BUSINESSCLASSIFICATIONSPEO.DELETED

FROM

POZ_BUS_CLASSIFICATIONS BUSINESSCLASSIFICATIONSPEO,

POZ_SUPPLIERS SUPP,

HZ_PARTIES PARTIES,

POZ_LOOKUP_CODES BUSCLASSLKUP,

POZ_LOOKUP_CODES SUBCLASSLKUP,

POZ_LOOKUP_CODES BCSTATUSLKUP,

POZ_CERTIFYING_AGENCIES POZCERTIFYINGAGENCIESEO

WHERE

PARTIES.PARTY_ID =SUPP.PARTY_ID

AND PARTIES.PARTY_ID =BUSINESSCLASSIFICATIONSPEO.PARTY_ID

AND BUSINESSCLASSIFICATIONSPEO.LOOKUP_CODE =BUSCLASSLKUP.LOOKUP_CODE(+)

AND BUSCLASSLKUP.LOOKUP_TYPE(+) =

'POZ_BUSINESS_CLASSIFICATIONS'

AND SUBCLASSLKUP.LOOKUP_TYPE(+) ='POZ_MINORITY_GROUP'

AND BCSTATUSLKUP.LOOKUP_TYPE = 'ORA_POZ_BUS_CLASS_STATUS'

AND BCSTATUSLKUP.LOOKUP_CODE = (CASE

WHEN (TRUNC(SYSDATE) BETWEEN (NVL(TRUNC(BUSINESSCLASSIFICATIONSPEO.START_DATE), TRUNC(SYSDATE))) AND NVL(TRUNC(BUSINESSCLASSIFICATIONSPEO.EXPIRATION_DATE), TRUNC(SYSDATE)) ) THEN

'CURRENT'

WHEN TRUNC(BUSINESSCLASSIFICATIONSPEO.START_DATE) > TRUNC(SYSDATE) THEN

'FUTURE_DATED'

WHEN TRUNC(BUSINESSCLASSIFICATIONSPEO.EXPIRATION_DATE) < TRUNC(SYSDATE) THEN

'EXPIRED'

END)

AND BUSINESSCLASSIFICATIONSPEO.EXT_ATTR_1 =SUBCLASSLKUP.LOOKUP_CODE(+)

AND POZCERTIFYINGAGENCIESEO.AGENCY_ID(+) = BUSINESSCLASSIFICATIONSPEO.CERTIFYING_AGENCY_ID

AND NVL(BUSINESSCLASSIFICATIONSPEO.DELETED,'N') = 'N'