[Home] [Help]
PACKAGE BODY: APPS.JTF_TASK_CUSTOM_COLORS_PUB
Source
1 PACKAGE BODY JTF_TASK_CUSTOM_COLORS_PUB AS
2 /* $Header: jtfptkfb.pls 115.5 2002/12/06 20:47:34 cjang ship $ */
3 /*======================================================================+
4 | Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME |
8 | jtfptkfb.pls |
9 | |
10 | DESCRIPTION |
11 | This package is used to get the color for a task. |
12 | |
13 | NOTES |
14 | |
15 | |
16 | Date Developer Change |
17 | ----------- --------------- --------------------------------------- |
18 | 22-Oct-2002 cjang Created. |
19 | 30-Oct-2002 cjang Modified escalated_task(): |
20 | Changed the cursor c_reference |
21 | from IN ('L1', 'L2', 'L3') |
22 | to NOT IN ('DE', 'NE') |
23 | 31-Oct-2002 cjang Added get_task_bgcolors() |
24 | Added parameters for the new column |
25 | background_col_dec. |
26 | Defined taskcolor_tbl and |
27 | Changed the parameter on get_task_bgcolors
28 | to the type of get_task_bgcolors |
29 | 03-Dec-2002 Sanjeev K BUG 2667735: Added NOCOPY option |
30 | 06-Dec-2002 Chan-Ik Jang Fixed Bug 2696521: |
31 | Added get_task_dec_bgcolor() |
32 *=======================================================================*/
33
34 PROCEDURE get_custom_color
35 IS
36 CURSOR c_custom_color IS
37 SELECT type_id
38 , priority_id
39 , assignment_status_id
40 , escalated_task
41 , background_col_dec
42 , background_col_rgb
43 FROM jtf_task_custom_colors
44 WHERE active_flag = 'Y'
45 ORDER BY color_determination_priority;
46 i BINARY_INTEGER := 0;
47 BEGIN
48 IF g_custom_color_tbl.COUNT = 0
49 THEN
50 FOR rec IN c_custom_color
51 LOOP
52 i := i + 1;
53 g_custom_color_tbl(i).task_type_id := rec.type_id;
54 g_custom_color_tbl(i).task_priority_id := rec.priority_id;
55 g_custom_color_tbl(i).assignment_status_id := rec.assignment_status_id;
56 g_custom_color_tbl(i).escalated_task := rec.escalated_task;
57 g_custom_color_tbl(i).background_col_dec := rec.background_col_dec;
58 g_custom_color_tbl(i).background_col_rgb := rec.background_col_rgb;
59 END LOOP;
60 END IF;
61 END get_custom_color;
62
63 FUNCTION escalated_task (p_task_id IN NUMBER)
64 RETURN VARCHAR2
65 IS
66 CURSOR c_reference (b_task_id NUMBER)
67 IS
68 SELECT 'Y'
69 FROM jtf_tasks_b esc
70 , jtf_task_references_b r
71 , jtf_task_statuses_b s
72 WHERE r.object_id = b_task_id
73 AND r.reference_code = 'ESC'
74 AND r.object_type_code = 'TASK'
75 AND esc.task_id = r.task_id
76 AND esc.task_type_id = 22
77 AND esc.escalation_level NOT IN ('DE', 'NE')
78 AND NVL(esc.deleted_flag, 'N') <> 'Y'
79 AND s.task_status_id = esc.task_status_id
80 AND NVL(s.closed_flag, 'N') <> 'Y';
81
82 l_escalated_task VARCHAR2(1);
83 BEGIN
84 OPEN c_reference (p_task_id);
85 FETCH c_reference INTO l_escalated_task;
86
87 IF c_reference%NOTFOUND
88 THEN
89 l_escalated_task := 'N';
90 END IF;
91 CLOSE c_reference;
92
93 RETURN l_escalated_task;
94 END escalated_task;
95
96 FUNCTION do_match (
97 p_task_type_id IN NUMBER
98 ,p_task_priority_id IN NUMBER
99 ,p_assignment_status_id IN NUMBER
100 ,p_escalated_task IN VARCHAR2
101 ,x_index OUT NOCOPY NUMBER
102 )
103 RETURN BOOLEAN
104 IS
105 BEGIN
106 FOR i IN g_custom_color_tbl.FIRST..g_custom_color_tbl.LAST
107 LOOP
108 IF NVL(g_custom_color_tbl(i).task_type_id, p_task_type_id) = p_task_type_id AND
109 NVL(g_custom_color_tbl(i).task_priority_id, p_task_priority_id) = p_task_priority_id AND
110 NVL(g_custom_color_tbl(i).assignment_status_id,p_assignment_status_id) = p_assignment_status_id AND
111 NVL(g_custom_color_tbl(i).escalated_task, p_escalated_task) = p_escalated_task
112 THEN
113 x_index := i;
114 RETURN TRUE;
115 END IF;
116 END LOOP;
117
118 RETURN FALSE;
119 END do_match;
120
121 FUNCTION get_task_rgb_bgcolor(p_task_id IN NUMBER
122 ,p_task_type_id IN NUMBER
123 ,p_task_priority_id IN NUMBER
124 ,p_assignment_status_id IN NUMBER
125 )
126 RETURN VARCHAR2
127 IS
128 l_index NUMBER;
129 l_matched BOOLEAN;
130 BEGIN
131 get_custom_color;
132
133 IF g_custom_color_tbl.COUNT = 0
134 THEN
135 RETURN NULL;
136 END IF;
137
138 l_matched := do_match (
139 p_task_type_id => p_task_type_id
140 ,p_task_priority_id => NVL(p_task_priority_id,-9)
141 ,p_assignment_status_id => p_assignment_status_id
142 ,p_escalated_task => escalated_task(p_task_id)
143 ,x_index => l_index
144 );
145 IF l_matched
146 THEN
147 RETURN g_custom_color_tbl(l_index).background_col_rgb;
148 ELSE
149 RETURN NULL;
150 END IF;
151 END get_task_rgb_bgcolor;
152
153 FUNCTION get_task_dec_bgcolor(p_task_id IN NUMBER
154 ,p_task_type_id IN NUMBER
155 ,p_task_priority_id IN NUMBER
156 ,p_assignment_status_id IN NUMBER
157 )
158 RETURN NUMBER
159 IS
160 l_index NUMBER;
161 l_matched BOOLEAN;
162 BEGIN
163 get_custom_color;
164
165 IF g_custom_color_tbl.COUNT = 0
166 THEN
167 RETURN NULL;
168 END IF;
169
170 l_matched := do_match (
171 p_task_type_id => p_task_type_id
172 ,p_task_priority_id => NVL(p_task_priority_id,-9)
173 ,p_assignment_status_id => p_assignment_status_id
174 ,p_escalated_task => escalated_task(p_task_id)
175 ,x_index => l_index
176 );
177 IF l_matched
178 THEN
179 RETURN g_custom_color_tbl(l_index).background_col_dec;
180 ELSE
181 RETURN NULL;
182 END IF;
183 END get_task_dec_bgcolor;
184
185 PROCEDURE get_task_bgcolors(p_taskcolor_tbl IN OUT NOCOPY taskcolor_tbl)
186 IS
187 l_index NUMBER;
188 l_matched BOOLEAN;
189 BEGIN
190 get_custom_color;
191
192 IF g_custom_color_tbl.COUNT = 0 OR
193 p_taskcolor_tbl.COUNT = 0
194 THEN
195 RETURN;
196 END IF;
197
198 FOR i IN p_taskcolor_tbl.FIRST..p_taskcolor_tbl.LAST
199 LOOP
200 l_matched := do_match (
201 p_task_type_id => p_taskcolor_tbl(i).task_type_id
202 ,p_task_priority_id => NVL(p_taskcolor_tbl(i).task_priority_id,-9)
203 ,p_assignment_status_id => p_taskcolor_tbl(i).assignment_status_id
204 ,p_escalated_task => escalated_task(p_taskcolor_tbl(i).task_id)
205 ,x_index => l_index
206 );
207 IF l_matched
208 THEN
209 p_taskcolor_tbl(i).background_col_dec := g_custom_color_tbl(l_index).background_col_dec;
210 p_taskcolor_tbl(i).background_col_rgb := g_custom_color_tbl(l_index).background_col_rgb;
211 END IF;
212 END LOOP;
213 END get_task_bgcolors;
214
215 END JTF_TASK_CUSTOM_COLORS_PUB;