VIEW: APPS.OKI_GEN_CLASS_CODES_V

Source

View Text - Preformatted

SELECT cc.group_type, cc.class_category, cat.meaning class_category_meaning, cod.lookup_code class_code , cod.meaning class_code_meaning , cod.description class_code_description from ( SELECT decode( h.class_code ,'ORG_GROUP','GEN','IND') group_type, owner_table_key_1 class_category FROM hz_code_assignments h WHERE h.owner_table_name = 'HZ_CLASS_CATEGORIES' AND h.class_category = 'CLASS_CATEGORY_GROUP' AND h.class_code in ('ORG_GROUP', 'INDUSTRIAL_GROUP') UNION ALL SELECT 'GEN' group_type, cc.class_category FROM hz_class_categories cc , hz_class_category_uses cu WHERE cc.CLASS_CATEGORY = cu.class_category AND (UPPER(CU.ADDITIONAL_WHERE_CLAUSE) LIKE decode('ORG_GROUP','ORG_GROUP', '%PARTY_TYPE%ORGANIZATION%', 'PERSON_GROUP', '%PARTY_TYPE%PERSON%', '%') or CU.ADDITIONAL_WHERE_CLAUSE IS NULL or UPPER(CU.ADDITIONAL_WHERE_CLAUSE) LIKE '%WHERE 1=1 %') and cu.owner_table = 'HZ_PARTIES' and cc.class_category not in (SELECT owner_table_key_1 FROM hz_code_assignments WHERE owner_table_name = 'HZ_CLASS_CATEGORIES') and cc.class_category not in ('RELATIONSHIP_TYPE_GROUP','CLASS_CATEGORY_GROUP') ) cc, fnd_lookup_types_tl cat, AR_LOOKUPS cod where cc.class_category = cat.lookup_type and cat.view_application_id = 222 and cat.language = USERENV('LANG') and cod.enabled_flag = 'Y' AND TRUNC(SYSDATE) BETWEEN TRUNC(cod.start_date_active) AND NVL(TRUNC(cod.end_date_active),SYSDATE) AND cc.class_category = cod.lookup_type

View Text - HTML Formatted

SELECT CC.GROUP_TYPE
, CC.CLASS_CATEGORY
, CAT.MEANING CLASS_CATEGORY_MEANING
, COD.LOOKUP_CODE CLASS_CODE
, COD.MEANING CLASS_CODE_MEANING
, COD.DESCRIPTION CLASS_CODE_DESCRIPTION
FROM ( SELECT DECODE( H.CLASS_CODE
, 'ORG_GROUP'
, 'GEN'
, 'IND') GROUP_TYPE
, OWNER_TABLE_KEY_1 CLASS_CATEGORY
FROM HZ_CODE_ASSIGNMENTS H
WHERE H.OWNER_TABLE_NAME = 'HZ_CLASS_CATEGORIES'
AND H.CLASS_CATEGORY = 'CLASS_CATEGORY_GROUP'
AND H.CLASS_CODE IN ('ORG_GROUP'
, 'INDUSTRIAL_GROUP') UNION ALL SELECT 'GEN' GROUP_TYPE
, CC.CLASS_CATEGORY
FROM HZ_CLASS_CATEGORIES CC
, HZ_CLASS_CATEGORY_USES CU
WHERE CC.CLASS_CATEGORY = CU.CLASS_CATEGORY
AND (UPPER(CU.ADDITIONAL_WHERE_CLAUSE) LIKE DECODE('ORG_GROUP'
, 'ORG_GROUP'
, '%PARTY_TYPE%ORGANIZATION%'
, 'PERSON_GROUP'
, '%PARTY_TYPE%PERSON%'
, '%') OR CU.ADDITIONAL_WHERE_CLAUSE IS NULL OR UPPER(CU.ADDITIONAL_WHERE_CLAUSE) LIKE '%WHERE 1=1 %')
AND CU.OWNER_TABLE = 'HZ_PARTIES'
AND CC.CLASS_CATEGORY NOT IN (SELECT OWNER_TABLE_KEY_1
FROM HZ_CODE_ASSIGNMENTS
WHERE OWNER_TABLE_NAME = 'HZ_CLASS_CATEGORIES')
AND CC.CLASS_CATEGORY NOT IN ('RELATIONSHIP_TYPE_GROUP'
, 'CLASS_CATEGORY_GROUP') ) CC
, FND_LOOKUP_TYPES_TL CAT
, AR_LOOKUPS COD
WHERE CC.CLASS_CATEGORY = CAT.LOOKUP_TYPE
AND CAT.VIEW_APPLICATION_ID = 222
AND CAT.LANGUAGE = USERENV('LANG')
AND COD.ENABLED_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(COD.START_DATE_ACTIVE)
AND NVL(TRUNC(COD.END_DATE_ACTIVE)
, SYSDATE)
AND CC.CLASS_CATEGORY = COD.LOOKUP_TYPE