VIEW: APPS.MSC_EXCEPTION_THRESHOLDS_V

Source

View Text - Preformatted

SELECT mfglookup1.meaning Exception_Group_Name, mfglookup2.meaning Exception_Name, to_number(mfglookup2.lookup_code ) Exception_type, decode(mfglookup2.lookup_code,9,'N',10,'N',29,'N',30,'N',33,'N',34,'N','Y') Threshold_Flag, decode(mfglookup2.lookup_code,9,null,10,null,29,null,30,null,33,null,34,null, mfglookup3.meaning) THRESHOLD_MEANING, decode(mfglookup2.lookup_code,'1','Y',2,'Y',5,'Y',6,'Y',7,'Y',8,'Y',13,'Y',50,'Y',51,'Y',19,'Y',20,'Y',21,'Y',22,'Y', 23,'Y',24,'Y',25,'Y',26,'Y',27,'Y',28,'Y','N') Horizon_Flag, decode(mfglookup2.lookup_code,'35','Y',36,'Y',37,'Y',38,'Y',39,'Y',40,'Y',41,'Y',42,'Y',43,'Y', 44,'Y',45,'Y',46,'Y',47,'Y',48,'Y', 'N' ) Period_Flag, decode(mfglookup2.lookup_code,1,'C',3,'C',5,'C',7,'C',9,'C',11,'C',12,'C',15,'C',16,'C',19,'C', 20,'C',23,'C',25,'C',27,'C',29,'C',33,'C',34,'C',35,'C',37,'C',38,'C',39,'C', 42,'C',44,'C',46,'C',48,'C',2,'S',4,'S',6,'S',8,'S',10,'S',13,'S',50,'S',51,'S',14,'S',21,'S', 22,'S',24,'S',26,'S',28,'S',30,'S',31,'S',32,'S',36,'S',40,'S',41,'S',43,'S',45,'S', 47,'S','B') Customer_Supplier_Flag from mfg_lookups mfglookup1, mfg_lookups mfglookup2 , mfg_lookups mfglookup3 WHERE mfglookup1.lookup_type = 'MSC_X_EXCEPTION_GROUP' AND mfglookup2.lookup_type = 'MSC_X_EXCEPTION_TYPE' AND mfglookup1.lookup_code = decode(mfglookup2.lookup_code,1,1,2,1,3,1,4,1, 5,2,6,2,7,2,8,2,9,2,10,2, 11,3,12,3,31,3,32,3,13,4,50,4,51,4,14,4,15,4,16,4, 19,5,20,5,21,5,22,5, 23,6,24,6,25,7,26,7,27,7,28,7,29,7,30,7, 33,8,34,8,35,9,36,9,37,9,38,9,39,10,40,10, 41,10,42,10,43,10,44,10,45,10,46,10,47,10,48,10) and mfglookup3.lookup_type = 'MSC_X_EXCEP_THRESHOLDS_TRANS' and mfglookup3.lookup_code = '2' union select mfglookup1.meaning Exception_Group_name, ex.NAME Exception_Name, ex.EXCEPTION_ID Exception_type, 'Y' Threshold_Flag, mfglookup3.meaning THRESHOLD_MEANING, 'N' Horizon_Flag, 'N' Period_Flag, 'B' Customer_Supplier_Flag from mfg_lookups mfglookup1, mfg_lookups mfglookup3, msc_user_exceptions ex where mfglookup1.lookup_type = 'MSC_X_EXCEPTION_GROUP' and mfglookup1.lookup_code = -99 and mfglookup3.lookup_type = 'MSC_X_EXCEP_THRESHOLDS_TRANS' and mfglookup3.lookup_code = '2' and ex.company_id = sys_context('MSC', 'COMPANY_ID')

View Text - HTML Formatted

SELECT MFGLOOKUP1.MEANING EXCEPTION_GROUP_NAME
, MFGLOOKUP2.MEANING EXCEPTION_NAME
, TO_NUMBER(MFGLOOKUP2.LOOKUP_CODE ) EXCEPTION_TYPE
, DECODE(MFGLOOKUP2.LOOKUP_CODE
, 9
, 'N'
, 10
, 'N'
, 29
, 'N'
, 30
, 'N'
, 33
, 'N'
, 34
, 'N'
, 'Y') THRESHOLD_FLAG
, DECODE(MFGLOOKUP2.LOOKUP_CODE
, 9
, NULL
, 10
, NULL
, 29
, NULL
, 30
, NULL
, 33
, NULL
, 34
, NULL
, MFGLOOKUP3.MEANING) THRESHOLD_MEANING
, DECODE(MFGLOOKUP2.LOOKUP_CODE
, '1'
, 'Y'
, 2
, 'Y'
, 5
, 'Y'
, 6
, 'Y'
, 7
, 'Y'
, 8
, 'Y'
, 13
, 'Y'
, 50
, 'Y'
, 51
, 'Y'
, 19
, 'Y'
, 20
, 'Y'
, 21
, 'Y'
, 22
, 'Y'
, 23
, 'Y'
, 24
, 'Y'
, 25
, 'Y'
, 26
, 'Y'
, 27
, 'Y'
, 28
, 'Y'
, 'N') HORIZON_FLAG
, DECODE(MFGLOOKUP2.LOOKUP_CODE
, '35'
, 'Y'
, 36
, 'Y'
, 37
, 'Y'
, 38
, 'Y'
, 39
, 'Y'
, 40
, 'Y'
, 41
, 'Y'
, 42
, 'Y'
, 43
, 'Y'
, 44
, 'Y'
, 45
, 'Y'
, 46
, 'Y'
, 47
, 'Y'
, 48
, 'Y'
, 'N' ) PERIOD_FLAG
, DECODE(MFGLOOKUP2.LOOKUP_CODE
, 1
, 'C'
, 3
, 'C'
, 5
, 'C'
, 7
, 'C'
, 9
, 'C'
, 11
, 'C'
, 12
, 'C'
, 15
, 'C'
, 16
, 'C'
, 19
, 'C'
, 20
, 'C'
, 23
, 'C'
, 25
, 'C'
, 27
, 'C'
, 29
, 'C'
, 33
, 'C'
, 34
, 'C'
, 35
, 'C'
, 37
, 'C'
, 38
, 'C'
, 39
, 'C'
, 42
, 'C'
, 44
, 'C'
, 46
, 'C'
, 48
, 'C'
, 2
, 'S'
, 4
, 'S'
, 6
, 'S'
, 8
, 'S'
, 10
, 'S'
, 13
, 'S'
, 50
, 'S'
, 51
, 'S'
, 14
, 'S'
, 21
, 'S'
, 22
, 'S'
, 24
, 'S'
, 26
, 'S'
, 28
, 'S'
, 30
, 'S'
, 31
, 'S'
, 32
, 'S'
, 36
, 'S'
, 40
, 'S'
, 41
, 'S'
, 43
, 'S'
, 45
, 'S'
, 47
, 'S'
, 'B') CUSTOMER_SUPPLIER_FLAG
FROM MFG_LOOKUPS MFGLOOKUP1
, MFG_LOOKUPS MFGLOOKUP2
, MFG_LOOKUPS MFGLOOKUP3
WHERE MFGLOOKUP1.LOOKUP_TYPE = 'MSC_X_EXCEPTION_GROUP'
AND MFGLOOKUP2.LOOKUP_TYPE = 'MSC_X_EXCEPTION_TYPE'
AND MFGLOOKUP1.LOOKUP_CODE = DECODE(MFGLOOKUP2.LOOKUP_CODE
, 1
, 1
, 2
, 1
, 3
, 1
, 4
, 1
, 5
, 2
, 6
, 2
, 7
, 2
, 8
, 2
, 9
, 2
, 10
, 2
, 11
, 3
, 12
, 3
, 31
, 3
, 32
, 3
, 13
, 4
, 50
, 4
, 51
, 4
, 14
, 4
, 15
, 4
, 16
, 4
, 19
, 5
, 20
, 5
, 21
, 5
, 22
, 5
, 23
, 6
, 24
, 6
, 25
, 7
, 26
, 7
, 27
, 7
, 28
, 7
, 29
, 7
, 30
, 7
, 33
, 8
, 34
, 8
, 35
, 9
, 36
, 9
, 37
, 9
, 38
, 9
, 39
, 10
, 40
, 10
, 41
, 10
, 42
, 10
, 43
, 10
, 44
, 10
, 45
, 10
, 46
, 10
, 47
, 10
, 48
, 10)
AND MFGLOOKUP3.LOOKUP_TYPE = 'MSC_X_EXCEP_THRESHOLDS_TRANS'
AND MFGLOOKUP3.LOOKUP_CODE = '2' UNION SELECT MFGLOOKUP1.MEANING EXCEPTION_GROUP_NAME
, EX.NAME EXCEPTION_NAME
, EX.EXCEPTION_ID EXCEPTION_TYPE
, 'Y' THRESHOLD_FLAG
, MFGLOOKUP3.MEANING THRESHOLD_MEANING
, 'N' HORIZON_FLAG
, 'N' PERIOD_FLAG
, 'B' CUSTOMER_SUPPLIER_FLAG
FROM MFG_LOOKUPS MFGLOOKUP1
, MFG_LOOKUPS MFGLOOKUP3
, MSC_USER_EXCEPTIONS EX
WHERE MFGLOOKUP1.LOOKUP_TYPE = 'MSC_X_EXCEPTION_GROUP'
AND MFGLOOKUP1.LOOKUP_CODE = -99
AND MFGLOOKUP3.LOOKUP_TYPE = 'MSC_X_EXCEP_THRESHOLDS_TRANS'
AND MFGLOOKUP3.LOOKUP_CODE = '2'
AND EX.COMPANY_ID = SYS_CONTEXT('MSC'
, 'COMPANY_ID')