POZ_BUSINESS_CLASSIFICATIONS_V
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 |
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' |