VIEW: APPS.MST_WSH_FTE_COMP_CONST_V

Source

View Text - Preformatted

SELECT c.COMPATIBILITY_CLASS_CODE , decode(c.COMPATIBILITY_CLASS_CODE, 'ITM_CAR', s.CONSTRAINT_OBJECT1_ID, 'ITM_MOD',s.CONSTRAINT_OBJECT1_ID, 'ITM_FAC',s.CONSTRAINT_OBJECT1_ID, 'ITM_ITM',s.CONSTRAINT_OBJECT1_ID, 'ITM_VEH', s.CONSTRAINT_OBJECT1_ID, NULL) COMMODITY_ITEM1, decode(c.COMPATIBILITY_CLASS_CODE, 'ITM_ITM', s.CONSTRAINT_OBJECT2_ID, NULL) COMMODITY_ITEM2, decode(c.COMPATIBILITY_CLASS_CODE, 'ITM_VEH',decode(CONSTRAINT_OBJECT2_TYPE, 'VHT',s.CONSTRAINT_OBJECT2_ID, NULL), 'FAC_VEH',decode(CONSTRAINT_OBJECT2_TYPE, 'VHT',s.CONSTRAINT_OBJECT2_ID, NULL), NULL ) VEHICLE_TYPE_ID, decode(c.COMPATIBILITY_CLASS_CODE, 'ITM_VEH',decode(CONSTRAINT_OBJECT2_TYPE, 'CLS',s.CONSTRAINT_OBJECT2_VALUE_CHAR, NULL), 'FAC_VEH',decode(CONSTRAINT_OBJECT2_TYPE, 'CLS',s.CONSTRAINT_OBJECT2_VALUE_CHAR, NULL), NULL ) VEHICLE_CLASS, decode(c.COMPATIBILITY_CLASS_CODE, 'ITM_VEHF', CONSTRAINT_OBJECT2_VALUE_CHAR, 'FAC__VEHF',CONSTRAINT_OBJECT2_VALUE_CHAR, NULL) VEHICLE_FEATURE, decode(c.COMPATIBILITY_CLASS_CODE, 'CUS_FAC', decode(CONSTRAINT_OBJECT1_TYPE,'FAC',CONSTRAINT_OBJECT1_ID, 'CUS', null), 'FAC_CAR', decode(CONSTRAINT_OBJECT1_TYPE,'FAC',CONSTRAINT_OBJECT1_ID,null), 'FAC_MOD',decode(CONSTRAINT_OBJECT1_TYPE,'FAC',CONSTRAINT_OBJECT1_ID, null), 'ORG_FAC', CONSTRAINT_OBJECT1_ID, 'FAC_VEH', decode(CONSTRAINT_OBJECT1_TYPE,'FAC',CONSTRAINT_OBJECT1_ID, null), 'FAC_VEHF',decode(CONSTRAINT_OBJECT1_TYPE,'FAC',CONSTRAINT_OBJECT1_ID, null), 'ITM_FAC',decode(CONSTRAINT_OBJECT2_TYPE,'FAC',CONSTRAINT_OBJECT2_ID, null), 'SUP_FAC', decode(CONSTRAINT_OBJECT1_TYPE, 'FAC', CONSTRAINT_OBJECT1_ID, 'SUP', null), NULL ) Facility_Id1, decode(c.COMPATIBILITY_CLASS_CODE, 'CUS_FAC',decode(CONSTRAINT_OBJECT2_TYPE,'FAC',CONSTRAINT_OBJECT2_ID, null), 'ORG_FAC',decode(CONSTRAINT_OBJECT2_TYPE,'FAC',CONSTRAINT_OBJECT2_ID, null), 'SUP_FAC',decode(CONSTRAINT_OBJECT2_TYPE,'FAC',CONSTRAINT_OBJECT2_ID, null), 'REG_FAC', decode(CONSTRAINT_OBJECT2_TYPE,'FAC',CONSTRAINT_OBJECT2_ID, null), NULL ) Facility_Id2, decode(c.COMPATIBILITY_CLASS_CODE, 'CUS_CUS', CONSTRAINT_OBJECT1_ID, 'FAC_CAR', decode(CONSTRAINT_OBJECT1_TYPE,'FAC',null, CONSTRAINT_OBJECT1_ID), 'FAC_MOD',decode(CONSTRAINT_OBJECT1_TYPE,'FAC',null,CONSTRAINT_OBJECT1_ID), 'CUS_FAC', decode(CONSTRAINT_OBJECT1_TYPE,'CUS',CONSTRAINT_OBJECT1_ID,NULL), 'ORG_FAC', CONSTRAINT_OBJECT1_ID, 'SUP_SUP', CONSTRAINT_OBJECT1_ID, 'ITM_CAR', CONSTRAINT_OBJECT2_ID, 'ITM_FAC',decode(CONSTRAINT_OBJECT2_TYPE,'FAC',null, CONSTRAINT_OBJECT2_ID), 'FAC_VEH', decode(CONSTRAINT_OBJECT1_TYPE,'FAC',null,CONSTRAINT_OBJECT1_ID), 'FAC_VEHF',decode(CONSTRAINT_OBJECT1_TYPE,'FAC',null,CONSTRAINT_OBJECT1_ID), 'SUP_FAC',decode(CONSTRAINT_OBJECT1_TYPE,'SUP',CONSTRAINT_OBJECT1_ID,NULL), 'REG_FAC', decode(CONSTRAINT_OBJECT1_TYPE,'REG', CONSTRAINT_OBJECT1_ID, 'ZON', CONSTRAINT_OBJECT1_ID, NULL), NULL ) TP1_ID, decode(c.COMPATIBILITY_CLASS_CODE, 'CUS_CUS', 2, 'FAC_CAR', decode(CONSTRAINT_OBJECT1_TYPE,'ORG',1, 'CUS',2,'CAR',3,'SUP',4,null), 'FAC_MOD',decode(CONSTRAINT_OBJECT1_TYPE,'ORG',1, 'CUS',2,'CAR',3,'SUP',4,null), 'CUS_FAC', decode(CONSTRAINT_OBJECT1_TYPE,'CUS',2,NULL), 'ORG_FAC', 1, 'SUP_SUP',4, 'ITM_CAR', 3, 'ITM_FAC',decode(CONSTRAINT_OBJECT2_TYPE,'ORG',1, 'CUS',2,'CAR',3,'SUP',4,null), 'FAC_VEH', decode(CONSTRAINT_OBJECT1_TYPE,'ORG',1, 'CUS',2,'CAR',3,'SUP',4,null), 'FAC_VEHF',decode(CONSTRAINT_OBJECT1_TYPE,'ORG',1, 'CUS',2,'CAR',3,'SUP',4,null), 'SUP_FAC',decode(CONSTRAINT_OBJECT1_TYPE,'SUP',4,NULL), 'REG_FAC', decode(CONSTRAINT_OBJECT1_TYPE,'REG', 5, 'ZON', 6, NULL), NULL ) TP1_TYPE, decode(c.COMPATIBILITY_CLASS_CODE, 'CUS_CUS', CONSTRAINT_OBJECT2_ID, 'SUP_SUP', CONSTRAINT_OBJECT2_ID, 'FAC_CAR', CONSTRAINT_OBJECT2_ID, 'CUS_FAC', decode(CONSTRAINT_OBJECT2_TYPE,'FAC',null,CONSTRAINT_OBJECT2_ID), 'ORG_FAC', decode(CONSTRAINT_OBJECT2_TYPE,'FAC',null,CONSTRAINT_OBJECT2_ID), 'SUP_FAC', decode(CONSTRAINT_OBJECT2_TYPE,'FAC',null,CONSTRAINT_OBJECT2_ID), NULL ) TP2_ID, decode( c.COMPATIBILITY_CLASS_CODE, 'CUS_CUS',decode(CONSTRAINT_OBJECT2_ID,null,null, 2), 'SUP_SUP',decode(CONSTRAINT_OBJECT2_ID,null,null,4), 'FAC_CAR', decode(CONSTRAINT_OBJECT2_ID,null,null,3), 'CUS_FAC', decode(CONSTRAINT_OBJECT2_TYPE,'ORG',1, 'CUS',2,'CAR',3,'SUP',4,null), 'ORG_FAC', decode(CONSTRAINT_OBJECT2_TYPE,'ORG',1, 'CUS',2,'CAR',3,'SUP',4,null), 'SUP_FAC', decode(CONSTRAINT_OBJECT2_TYPE ,'ORG',1, 'CUS',2,'CAR',3,'SUP',4,null), NULL ) TP2_TYPE, decode(c.COMPATIBILITY_CLASS_CODE, 'FAC_MOD',CONSTRAINT_OBJECT2_VALUE_CHAR, 'ITM_MOD',CONSTRAINT_OBJECT2_VALUE_CHAR, NULL) TP_MODE, s.CONSTRAINT_TYPE, s.EFFECTIVE_DATE_FROM, s.EFFECTIVE_DATE_TO from WSH_FTE_COMP_CONSTRAINTS s, WSH_FTE_COMP_CLASSES c WHERE s.COMPATIBILITY_CLASS_ID = c.COMPATIBILITY_CLASS_ID

View Text - HTML Formatted

SELECT C.COMPATIBILITY_CLASS_CODE
, DECODE(C.COMPATIBILITY_CLASS_CODE
, 'ITM_CAR'
, S.CONSTRAINT_OBJECT1_ID
, 'ITM_MOD'
, S.CONSTRAINT_OBJECT1_ID
, 'ITM_FAC'
, S.CONSTRAINT_OBJECT1_ID
, 'ITM_ITM'
, S.CONSTRAINT_OBJECT1_ID
, 'ITM_VEH'
, S.CONSTRAINT_OBJECT1_ID
, NULL) COMMODITY_ITEM1
, DECODE(C.COMPATIBILITY_CLASS_CODE
, 'ITM_ITM'
, S.CONSTRAINT_OBJECT2_ID
, NULL) COMMODITY_ITEM2
, DECODE(C.COMPATIBILITY_CLASS_CODE
, 'ITM_VEH'
, DECODE(CONSTRAINT_OBJECT2_TYPE
, 'VHT'
, S.CONSTRAINT_OBJECT2_ID
, NULL)
, 'FAC_VEH'
, DECODE(CONSTRAINT_OBJECT2_TYPE
, 'VHT'
, S.CONSTRAINT_OBJECT2_ID
, NULL)
, NULL ) VEHICLE_TYPE_ID
, DECODE(C.COMPATIBILITY_CLASS_CODE
, 'ITM_VEH'
, DECODE(CONSTRAINT_OBJECT2_TYPE
, 'CLS'
, S.CONSTRAINT_OBJECT2_VALUE_CHAR
, NULL)
, 'FAC_VEH'
, DECODE(CONSTRAINT_OBJECT2_TYPE
, 'CLS'
, S.CONSTRAINT_OBJECT2_VALUE_CHAR
, NULL)
, NULL ) VEHICLE_CLASS
, DECODE(C.COMPATIBILITY_CLASS_CODE
, 'ITM_VEHF'
, CONSTRAINT_OBJECT2_VALUE_CHAR
, 'FAC__VEHF'
, CONSTRAINT_OBJECT2_VALUE_CHAR
, NULL) VEHICLE_FEATURE
, DECODE(C.COMPATIBILITY_CLASS_CODE
, 'CUS_FAC'
, DECODE(CONSTRAINT_OBJECT1_TYPE
, 'FAC'
, CONSTRAINT_OBJECT1_ID
, 'CUS'
, NULL)
, 'FAC_CAR'
, DECODE(CONSTRAINT_OBJECT1_TYPE
, 'FAC'
, CONSTRAINT_OBJECT1_ID
, NULL)
, 'FAC_MOD'
, DECODE(CONSTRAINT_OBJECT1_TYPE
, 'FAC'
, CONSTRAINT_OBJECT1_ID
, NULL)
, 'ORG_FAC'
, CONSTRAINT_OBJECT1_ID
, 'FAC_VEH'
, DECODE(CONSTRAINT_OBJECT1_TYPE
, 'FAC'
, CONSTRAINT_OBJECT1_ID
, NULL)
, 'FAC_VEHF'
, DECODE(CONSTRAINT_OBJECT1_TYPE
, 'FAC'
, CONSTRAINT_OBJECT1_ID
, NULL)
, 'ITM_FAC'
, DECODE(CONSTRAINT_OBJECT2_TYPE
, 'FAC'
, CONSTRAINT_OBJECT2_ID
, NULL)
, 'SUP_FAC'
, DECODE(CONSTRAINT_OBJECT1_TYPE
, 'FAC'
, CONSTRAINT_OBJECT1_ID
, 'SUP'
, NULL)
, NULL ) FACILITY_ID1
, DECODE(C.COMPATIBILITY_CLASS_CODE
, 'CUS_FAC'
, DECODE(CONSTRAINT_OBJECT2_TYPE
, 'FAC'
, CONSTRAINT_OBJECT2_ID
, NULL)
, 'ORG_FAC'
, DECODE(CONSTRAINT_OBJECT2_TYPE
, 'FAC'
, CONSTRAINT_OBJECT2_ID
, NULL)
, 'SUP_FAC'
, DECODE(CONSTRAINT_OBJECT2_TYPE
, 'FAC'
, CONSTRAINT_OBJECT2_ID
, NULL)
, 'REG_FAC'
, DECODE(CONSTRAINT_OBJECT2_TYPE
, 'FAC'
, CONSTRAINT_OBJECT2_ID
, NULL)
, NULL ) FACILITY_ID2
, DECODE(C.COMPATIBILITY_CLASS_CODE
, 'CUS_CUS'
, CONSTRAINT_OBJECT1_ID
, 'FAC_CAR'
, DECODE(CONSTRAINT_OBJECT1_TYPE
, 'FAC'
, NULL
, CONSTRAINT_OBJECT1_ID)
, 'FAC_MOD'
, DECODE(CONSTRAINT_OBJECT1_TYPE
, 'FAC'
, NULL
, CONSTRAINT_OBJECT1_ID)
, 'CUS_FAC'
, DECODE(CONSTRAINT_OBJECT1_TYPE
, 'CUS'
, CONSTRAINT_OBJECT1_ID
, NULL)
, 'ORG_FAC'
, CONSTRAINT_OBJECT1_ID
, 'SUP_SUP'
, CONSTRAINT_OBJECT1_ID
, 'ITM_CAR'
, CONSTRAINT_OBJECT2_ID
, 'ITM_FAC'
, DECODE(CONSTRAINT_OBJECT2_TYPE
, 'FAC'
, NULL
, CONSTRAINT_OBJECT2_ID)
, 'FAC_VEH'
, DECODE(CONSTRAINT_OBJECT1_TYPE
, 'FAC'
, NULL
, CONSTRAINT_OBJECT1_ID)
, 'FAC_VEHF'
, DECODE(CONSTRAINT_OBJECT1_TYPE
, 'FAC'
, NULL
, CONSTRAINT_OBJECT1_ID)
, 'SUP_FAC'
, DECODE(CONSTRAINT_OBJECT1_TYPE
, 'SUP'
, CONSTRAINT_OBJECT1_ID
, NULL)
, 'REG_FAC'
, DECODE(CONSTRAINT_OBJECT1_TYPE
, 'REG'
, CONSTRAINT_OBJECT1_ID
, 'ZON'
, CONSTRAINT_OBJECT1_ID
, NULL)
, NULL ) TP1_ID
, DECODE(C.COMPATIBILITY_CLASS_CODE
, 'CUS_CUS'
, 2
, 'FAC_CAR'
, DECODE(CONSTRAINT_OBJECT1_TYPE
, 'ORG'
, 1
, 'CUS'
, 2
, 'CAR'
, 3
, 'SUP'
, 4
, NULL)
, 'FAC_MOD'
, DECODE(CONSTRAINT_OBJECT1_TYPE
, 'ORG'
, 1
, 'CUS'
, 2
, 'CAR'
, 3
, 'SUP'
, 4
, NULL)
, 'CUS_FAC'
, DECODE(CONSTRAINT_OBJECT1_TYPE
, 'CUS'
, 2
, NULL)
, 'ORG_FAC'
, 1
, 'SUP_SUP'
, 4
, 'ITM_CAR'
, 3
, 'ITM_FAC'
, DECODE(CONSTRAINT_OBJECT2_TYPE
, 'ORG'
, 1
, 'CUS'
, 2
, 'CAR'
, 3
, 'SUP'
, 4
, NULL)
, 'FAC_VEH'
, DECODE(CONSTRAINT_OBJECT1_TYPE
, 'ORG'
, 1
, 'CUS'
, 2
, 'CAR'
, 3
, 'SUP'
, 4
, NULL)
, 'FAC_VEHF'
, DECODE(CONSTRAINT_OBJECT1_TYPE
, 'ORG'
, 1
, 'CUS'
, 2
, 'CAR'
, 3
, 'SUP'
, 4
, NULL)
, 'SUP_FAC'
, DECODE(CONSTRAINT_OBJECT1_TYPE
, 'SUP'
, 4
, NULL)
, 'REG_FAC'
, DECODE(CONSTRAINT_OBJECT1_TYPE
, 'REG'
, 5
, 'ZON'
, 6
, NULL)
, NULL ) TP1_TYPE
, DECODE(C.COMPATIBILITY_CLASS_CODE
, 'CUS_CUS'
, CONSTRAINT_OBJECT2_ID
, 'SUP_SUP'
, CONSTRAINT_OBJECT2_ID
, 'FAC_CAR'
, CONSTRAINT_OBJECT2_ID
, 'CUS_FAC'
, DECODE(CONSTRAINT_OBJECT2_TYPE
, 'FAC'
, NULL
, CONSTRAINT_OBJECT2_ID)
, 'ORG_FAC'
, DECODE(CONSTRAINT_OBJECT2_TYPE
, 'FAC'
, NULL
, CONSTRAINT_OBJECT2_ID)
, 'SUP_FAC'
, DECODE(CONSTRAINT_OBJECT2_TYPE
, 'FAC'
, NULL
, CONSTRAINT_OBJECT2_ID)
, NULL ) TP2_ID
, DECODE( C.COMPATIBILITY_CLASS_CODE
, 'CUS_CUS'
, DECODE(CONSTRAINT_OBJECT2_ID
, NULL
, NULL
, 2)
, 'SUP_SUP'
, DECODE(CONSTRAINT_OBJECT2_ID
, NULL
, NULL
, 4)
, 'FAC_CAR'
, DECODE(CONSTRAINT_OBJECT2_ID
, NULL
, NULL
, 3)
, 'CUS_FAC'
, DECODE(CONSTRAINT_OBJECT2_TYPE
, 'ORG'
, 1
, 'CUS'
, 2
, 'CAR'
, 3
, 'SUP'
, 4
, NULL)
, 'ORG_FAC'
, DECODE(CONSTRAINT_OBJECT2_TYPE
, 'ORG'
, 1
, 'CUS'
, 2
, 'CAR'
, 3
, 'SUP'
, 4
, NULL)
, 'SUP_FAC'
, DECODE(CONSTRAINT_OBJECT2_TYPE
, 'ORG'
, 1
, 'CUS'
, 2
, 'CAR'
, 3
, 'SUP'
, 4
, NULL)
, NULL ) TP2_TYPE
, DECODE(C.COMPATIBILITY_CLASS_CODE
, 'FAC_MOD'
, CONSTRAINT_OBJECT2_VALUE_CHAR
, 'ITM_MOD'
, CONSTRAINT_OBJECT2_VALUE_CHAR
, NULL) TP_MODE
, S.CONSTRAINT_TYPE
, S.EFFECTIVE_DATE_FROM
, S.EFFECTIVE_DATE_TO
FROM WSH_FTE_COMP_CONSTRAINTS S
, WSH_FTE_COMP_CLASSES C
WHERE S.COMPATIBILITY_CLASS_ID = C.COMPATIBILITY_CLASS_ID