1 package body IEU_UWQ_UTIL_PUB as
2 /* $Header: IEUUTILB.pls 120.1 2005/06/23 13:48:36 appldev ship $ */
3
4
5 function to_number_noerr(str VARCHAR2) RETURN NUMBER
6 is
7 begin
8 return to_number(str);
9 exception
10 when others then
11 return null;
12 end to_number_noerr;
13
14
15
16
17
18 -- DETERMINE_SOURCE_APP: Finds the Application defined for the
19 -- "Media Action" Function associated w/ the Responsibility, Media Type
20 -- and Classification mapping.
21 PROCEDURE DETERMINE_SOURCE_APP
22 (P_RESP_ID IN NUMBER
23 ,P_CLASSIFICATION IN VARCHAR2
24 ,P_MEDIA_TYPE_UUID IN VARCHAR2
25 ,X_APP_ID OUT NOCOPY NUMBER)
26 AS
27 l_app_id NUMBER;
28
29 BEGIN
30
31 BEGIN
32 -- 1) look for exact match:
33 SELECT c.application_id
34 INTO l_app_id
35 FROM IEU_UWQ_MACTION_DEFS_B c
36 ,IEU_UWQ_MEDIA_ACTIONS b
37 ,IEU_UWQ_MEDIA_TYPES_B a
38 WHERE B.RESP_ID = P_RESP_ID
39 AND A.MEDIA_TYPE_UUID = P_MEDIA_TYPE_UUID
40 AND B.MEDIA_TYPE_ID = A.MEDIA_TYPE_ID
41 AND B.CLASSIFICATION = P_CLASSIFICATION
42 AND C.MACTION_DEF_ID = B.MACTION_DEF_ID;
43
44 EXCEPTION WHEN NO_DATA_FOUND THEN
45 -- 2) look for matching Resp ID and Media Type:
46 BEGIN
47 SELECT c.application_id
48 INTO l_app_id
49 FROM IEU_UWQ_MACTION_DEFS_B c
50 ,IEU_UWQ_MEDIA_ACTIONS b
51 ,IEU_UWQ_MEDIA_TYPES_B a
52 WHERE B.RESP_ID = P_RESP_ID
53 AND A.MEDIA_TYPE_UUID = P_MEDIA_TYPE_UUID
54 AND B.MEDIA_TYPE_ID = A.MEDIA_TYPE_ID
55 AND B.CLASSIFICATION is null
56 AND C.MACTION_DEF_ID = B.MACTION_DEF_ID;
57
58 EXCEPTION WHEN NO_DATA_FOUND THEN
59 -- 3) look for just matching Resp ID:
60 BEGIN
61 SELECT c.application_id
62 INTO l_app_id
63 FROM IEU_UWQ_MACTION_DEFS_B c
64 ,IEU_UWQ_MEDIA_ACTIONS b
65 WHERE B.RESP_ID = P_RESP_ID
66 AND C.MACTION_DEF_ID = B.MACTION_DEF_ID
67 AND B.MEDIA_TYPE_ID = -1;
68
69 EXCEPTION WHEN NO_DATA_FOUND THEN
70 -- 4) look for (Any) Responsibility (-1) w/ classification
71 BEGIN
72 SELECT c.application_id
73 INTO l_app_id
74 FROM IEU_UWQ_MACTION_DEFS_B c
75 ,IEU_UWQ_MEDIA_ACTIONS b
76 ,IEU_UWQ_MEDIA_TYPES_B a
77 WHERE ( B.RESP_ID = -1
78 OR B.RESP_ID IS NULL )
79 AND A.MEDIA_TYPE_UUID = P_MEDIA_TYPE_UUID
80 AND B.MEDIA_TYPE_ID = A.MEDIA_TYPE_ID
81 AND B.CLASSIFICATION = P_CLASSIFICATION
82 AND C.MACTION_DEF_ID = B.MACTION_DEF_ID;
83
84 EXCEPTION WHEN NO_DATA_FOUND THEN
85 BEGIN
86 -- 5) look for (Any) Responsibility/default classification
87 SELECT c.application_id
88 INTO l_app_id
89 FROM IEU_UWQ_MACTION_DEFS_B c
90 ,IEU_UWQ_MEDIA_ACTIONS b
91 ,IEU_UWQ_MEDIA_TYPES_B a
92 WHERE ( B.RESP_ID = -1
93 OR B.RESP_ID IS NULL )
94 AND A.MEDIA_TYPE_UUID = P_MEDIA_TYPE_UUID
95 AND B.MEDIA_TYPE_ID = A.MEDIA_TYPE_ID
96 AND B.CLASSIFICATION is null
97 AND C.MACTION_DEF_ID = B.MACTION_DEF_ID;
98 EXCEPTION WHEN NO_DATA_FOUND THEN
99 --BEGIN
100 -- 6) look for (Any) Responsibility/default media type
101 SELECT c.application_id
102 INTO l_app_id
103 FROM IEU_UWQ_MACTION_DEFS_B c
104 ,IEU_UWQ_MEDIA_ACTIONS b
105 ,IEU_UWQ_MEDIA_TYPES_B a
106 WHERE ( B.RESP_ID = -1
107 OR B.RESP_ID IS NULL )
108 AND B.MEDIA_TYPE_ID = -1
109 AND B.CLASSIFICATION is null
110 AND C.MACTION_DEF_ID = B.MACTION_DEF_ID;
111
112 END;
113
114 END;
115 END;
116 END;
117 END;
118
119 X_APP_ID := l_app_id;
120
121 EXCEPTION
122 -- WHEN NO_DATA_FOUND THEN
123 WHEN OTHERS THEN
124 NULL;
125
126 END DETERMINE_SOURCE_APP;
127
128 END IEU_UWQ_UTIL_PUB;
[Home] [Help]