[Home] [Help]
PACKAGE BODY: APPS.CSL_JTF_TASK_REFS_ACC_PKG
Source
1 PACKAGE BODY CSL_JTF_TASK_REFS_ACC_PKG AS
2 /* $Header: cslteacb.pls 115.6 2002/11/08 14:01:15 asiegers ship $ */
3
4 /*** Globals ***/
5 g_acc_table_name CONSTANT VARCHAR2(30) := 'JTM_JTF_TASK_REFERENCES_ACC';
6 g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
7 JTM_HOOK_UTIL_PKG.t_publication_item_list('JTF_TASK_REFERENCES');
8 g_table_name CONSTANT VARCHAR2(30) := 'JTF_TASK_REFERENCES_B';
9 g_pk1_name CONSTANT VARCHAR2(30) := 'TASK_REFERENCE_ID';
10 g_debug_level NUMBER; -- debug level
11
12
13 PROCEDURE CON_REQUEST_TASK_REFERENCES
14 IS
15 PRAGMA AUTONOMOUS_TRANSACTION;
16 CURSOR c_esc(b_task_id NUMBER, b_resource_id NUMBER, b_last_run_date DATE ) IS
17 SELECT jtr.task_reference_id
18 FROM jtf_tasks_b jtb,
19 jtf_task_references_b jtr,
20 jtf_task_statuses_b jts
21 WHERE jtb.task_id = jtr.task_id
22 AND jtb.task_type_id = 22
23 AND jtb.task_status_id = jts.task_status_id
24 AND NVL(jts.closed_flag, 'N') <> 'Y'
25 AND NVL(jts.completed_flag, 'N') <> 'Y'
26 AND NVL(jts.cancelled_flag, 'N') <> 'Y'
27 AND jtr.reference_code = 'ESC'
28 AND jtr.object_type_code = 'TASK'
29 AND jtb.last_update_date >= NVL(b_last_run_date, jtb.LAST_UPDATE_DATE )
30 AND jtr.object_id = b_task_id
31 AND jtr.task_reference_id NOT IN(
32 SELECT task_reference_id
33 FROM jtm_jtf_task_references_acc
34 WHERE resource_id = b_resource_id );
35 r_esc c_esc%ROWTYPE;
36
37 CURSOR c_task IS
38 SELECT task_id
39 , resource_id
40 from csl_jtf_tasks_acc;
41
42 CURSOR c_esc_res IS
43 SELECT DISTINCT resource_id
44 FROM jtm_jtf_task_references_acc;
45
46 /*** get the last run date of the concurent program ***/
47 CURSOR c_LastRundate
48 IS
49 select LAST_RUN_DATE
50 from JTM_CON_REQUEST_DATA
51 where package_name = 'CSL_JTF_TASK_REFS_ACC_PKG'
52 AND procedure_name = 'CON_REQUEST_TASK_REFERENCES';
53 r_LastRundate c_LastRundate%ROWTYPE;
54 l_last_rundate DATE;
55 l_return_status VARCHAR2(2000);
56 BEGIN
57 /*** get debug level ***/
58 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
59 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
60 jtm_message_log_pkg.Log_Msg
61 ( 0
62 , g_table_name
63 , 'Entering CON_REQUEST_TASK_REFERENCES'
64 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
65 );
66 END IF;
67
68 /*** First retrieve last run date of the conccurent program ***/
69 OPEN c_LastRundate;
70 FETCH c_LastRundate INTO r_LastRundate;
71 IF c_LastRundate%NOTFOUND THEN
72 /*ERROR package is not seeded*/
73 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
74 jtm_message_log_pkg.Log_Msg
75 ( 0
76 , g_table_name
77 , 'CON_REQUEST_TASK_REFERENCES called but not seeded'
78 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
79 );
80 END IF;
81 CLOSE c_LastRundate;
82 ROLLBACK;
83 RETURN;
84 ELSE
85 l_last_rundate := r_LastRundate.last_run_date;
86 END IF;
87 CLOSE c_LastRundate;
88
89 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
90 jtm_message_log_pkg.Log_Msg
91 ( 0
92 , g_table_name
93 , 'Updating LAST_RUN_DATE from '||r_LastRundate.LAST_RUN_DATE||' to '||sysdate
94 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
95 );
96 END IF;
97
98 /*Update the last run date*/
99 UPDATE JTM_CON_REQUEST_DATA
100 SET LAST_RUN_DATE = SYSDATE
101 WHERE package_name = 'CSL_JTF_TASK_REFS_ACC_PKG'
102 AND procedure_name = 'CON_REQUEST_TASK_REFERENCES';
103
104 FOR r_task IN c_task LOOP
105 /*Get all escaleted tasks not yet in the acc table*/
106 OPEN c_esc( r_task.task_id, r_task.resource_id, l_last_rundate );
107 FETCH c_esc INTO r_esc;
108 IF c_esc%FOUND THEN
109 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
110 jtm_message_log_pkg.Log_Msg
111 ( r_esc.task_reference_id
112 , g_table_name
113 , 'Inserting escalation '||r_esc.task_reference_id||' for resource '||r_task.resource_id
114 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
115 );
116 END IF;
117
118 JTM_HOOK_UTIL_PKG.Insert_Acc
119 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
120 ,P_ACC_TABLE_NAME => g_acc_table_name
121 ,P_RESOURCE_ID => r_task.resource_id
122 ,P_PK1_NAME => g_pk1_name
123 ,P_PK1_NUM_VALUE => r_esc.task_reference_id
124 );
125 END IF;
126 CLOSE c_esc;
127 END LOOP;
128
129 /*Delete all escaltions that are closed or no longer assigned to a mobile resource*/
130 FOR r_esc_res IN c_esc_res LOOP
131 DELETE_ALL_ACC_RECORDS( r_esc_res.resource_id, l_return_status );
132 END LOOP;
133
134 COMMIT;
135
136 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
137 jtm_message_log_pkg.Log_Msg
138 ( 0
139 , g_table_name
140 , 'Leaving CON_REQUEST_TASK_REFERENCES'
141 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
142 );
143 END IF;
144 EXCEPTION
145 WHEN OTHERS THEN
146 jtm_message_log_pkg.Log_Msg
147 ( 0
148 , g_table_name
149 , 'CON_REQUEST_TASK_REFERENCES'||fnd_global.local_chr(10)||
150 'Error: '||sqlerrm
151 , JTM_HOOK_UTIL_PKG.g_debug_level_error);
152 ROLLBACK;
153 RETURN;
154 END CON_REQUEST_TASK_REFERENCES;
155
156 PROCEDURE DELETE_ALL_ACC_RECORDS( p_resource_id IN NUMBER
157 , x_return_status OUT NOCOPY VARCHAR2 )
158 IS
159 CURSOR c_no_esc ( b_resource_id NUMBER ) IS
160 SELECT task_reference_id
161 FROM jtm_jtf_task_references_acc
162 WHERE resource_id = b_resource_id
163 AND task_reference_id NOT IN(
164 SELECT jtr.task_reference_id
165 FROM jtf_tasks_b jtb,
166 jtf_task_references_b jtr,
167 jtf_task_statuses_b jts
168 WHERE jtb.task_id = jtr.task_id
169 AND jtb.task_type_id = 22
170 AND jtb.task_status_id = jts.task_status_id
171 AND NVL(jts.closed_flag, 'N') <> 'Y'
172 AND NVL(jts.completed_flag, 'N') <> 'Y'
173 AND NVL(jts.cancelled_flag, 'N') <> 'Y'
174 AND jtr.reference_code = 'ESC'
175 AND jtr.object_type_code = 'TASK'
176 AND jtr.object_id IN (
177 SELECT task_id
178 from csl_jtf_tasks_acc));
179 BEGIN
180 /*** get debug level ***/
181 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
182 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
183 jtm_message_log_pkg.Log_Msg
184 ( p_resource_id
185 , g_table_name
186 , 'Entering DELETE_ALL_ACC_RECORDS'
187 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
188 );
189 END IF;
190
191 FOR r_no_esc IN c_no_esc( p_resource_id ) LOOP
192 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
193 jtm_message_log_pkg.Log_Msg
194 ( r_no_esc.task_reference_id
195 , g_table_name
196 , 'Removing escalation '||r_no_esc.task_reference_id||' for resource '||p_resource_id
197 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
198 );
199 END IF;
200 JTM_HOOK_UTIL_PKG.Delete_Acc
201 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
202 ,P_ACC_TABLE_NAME => g_acc_table_name
203 ,P_RESOURCE_ID => p_resource_id
204 ,P_PK1_NAME => g_pk1_name
205 ,P_PK1_NUM_VALUE => r_no_esc.task_reference_id
206 );
207 END LOOP;
208
209 x_return_status := FND_API.G_RET_STS_SUCCESS;
210
211 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
212 jtm_message_log_pkg.Log_Msg
213 ( p_resource_id
214 , g_table_name
215 , 'Leaving DELETE_ALL_ACC_RECORDS'
216 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
217 );
218 END IF;
219 EXCEPTION
220 WHEN OTHERS THEN
221 -- x_return_status := FND_API.G_RET_STS_ERROR;
222 x_return_status := FND_API.G_RET_STS_SUCCESS;
223 jtm_message_log_pkg.Log_Msg
224 ( p_resource_id
225 , g_table_name
226 , 'CON_REQUEST_TASK_REFERENCES'||fnd_global.local_chr(10)||
227 'Error: '||sqlerrm
228 , JTM_HOOK_UTIL_PKG.g_debug_level_error);
229 ROLLBACK;
230 RAISE;
231 END DELETE_ALL_ACC_RECORDS;
232
233 END CSL_JTF_TASK_REFS_ACC_PKG;