[Home] [Help]
| Product: | ENG - Engineering |
|---|---|
| Description: | This view holds product group information |
| Implementation/DBA Data: | Not implemented in this database |
SELECT SCD.INTEREST_CODE_ID || '-' || INST.INSTANCE_CODE || '-SECN_CODE' PRODUCT_GROUP_PK
, PCD.INTEREST_CODE_ID || '-' || INST.INSTANCE_CODE || '-PRIM_CODE' PRODUCT_CATEG_FK
, SCD.CODE ||'(' || PCD.CODE || ')' || '(' || INTYP.INTEREST_TYPE || ')' PRODUCT_GROUP_DP
, SCD.CODE NAME
, SCD.DESCRIPTION DESCRIPTION
, SCD.INTEREST_CODE_ID SECONDARY_CODE_ID
, SCD.MASTER_ENABLED_FLAG ENABLED_FLAG
, INST.INSTANCE_CODE INSTANCE_CODE
, SCD.CREATION_DATE CREATION_DATE
, GREATEST(SCD.LAST_UPDATE_DATE
, PCD.LAST_UPDATE_DATE
, INTYP.LAST_UPDATE_DATE ) LAST_UPDATE_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
, TO_DATE(NULL) DELETION_DATE
FROM AS_INTEREST_CODES_V SCD
, AS_INTEREST_CODES_V PCD
, AS_INTEREST_TYPES_V INTYP
, EDW_LOCAL_INSTANCE INST
WHERE SCD.PARENT_INTEREST_CODE_ID = PCD.INTEREST_CODE_ID
AND PCD.PARENT_INTEREST_CODE_ID IS NULL
AND SCD.INTEREST_TYPE_ID = PCD.INTEREST_TYPE_ID
AND PCD.INTEREST_TYPE_ID = INTYP.INTEREST_TYPE_ID
AND INTYP.EXPECTED_PURCHASE_FLAG = 'Y'
AND ( ( SCD.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD') ) OR ( PCD.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD') ) OR (INTYP.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD') ) )