eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.CSL_CSF_DEBRIEF_HDR_ACC_PKG

Source


1 PACKAGE BODY CSL_CSF_DEBRIEF_HDR_ACC_PKG AS
2 /* $Header: csldhacb.pls 115.8 2003/08/28 10:15:24 vekrishn ship $ */
3 
4   /*** Globals ***/
5   g_acc_table_name        CONSTANT VARCHAR2(30)
6                                := 'JTM_CSF_DEBRIEF_HEADERS_ACC';
7   g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
8    JTM_HOOK_UTIL_PKG.t_publication_item_list('CSF_DEBRIEF_HEADERS');
9   g_table_name            CONSTANT VARCHAR2(30) := 'CSF_DEBRIEF_HEADERS';
10   g_pk1_name              CONSTANT VARCHAR2(30) := 'DEBRIEF_HEADER_ID';
11 
12   g_debug_level           NUMBER;  -- debug level
13 
14 
15   /*** Function that retrieves resource_id for a given debrief_line_id.
16    ***/
17 
18   FUNCTION Get_Resource_Id( p_debrief_header_id NUMBER )
19   RETURN NUMBER
20   IS
21     CURSOR c_resource ( b_debrief_header_id NUMBER)
22     IS
23      SELECT resource_id
24      FROM   jtf_task_assignments jta
25      ,      csf_debrief_headers  dbh
26      WHERE  jta.task_assignment_id = dbh.task_assignment_id
27      AND    dbh.debrief_header_id  = b_debrief_header_id;
28      r_resource c_resource%ROWTYPE;
29   BEGIN
30     IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
31       jtm_message_log_pkg.Log_Msg
32       ( p_debrief_header_id
33       , g_table_name
34       , 'Entering Get_Resource_Id function'
35       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
36     END IF;
37 
38     OPEN c_resource( p_debrief_header_id );
39     FETCH c_resource INTO r_resource;
40     CLOSE c_resource;
41 
42     IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
43       jtm_message_log_pkg.Log_Msg
44       ( p_debrief_header_id
45       , g_table_name
46       , 'Leaving Get_Resource_Id function'
47       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
48     END IF;
49     RETURN r_resource.resource_id;
50   END Get_Resource_Id;
51 
52 
53   /*** Function that checks if debrief line should be replicated. Returns
54        TRUE if it should ***/
55 
56   FUNCTION Replicate_Record
57   ( p_debrief_header_id NUMBER
58   )
59   RETURN BOOLEAN
60   IS
61     CURSOR c_debrief_header (b_debrief_header_id NUMBER) IS
62      SELECT *
63      FROM CSF_DEBRIEF_HEADERS
64      WHERE debrief_header_id = b_debrief_header_id;
65     r_debrief_header c_debrief_header%ROWTYPE;
66 
67   BEGIN
68     /*** get debug level ***/
69     g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
70 
71     IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
72       jtm_message_log_pkg.Log_Msg
73       ( p_debrief_header_id
74       , g_table_name
75       , 'Entering Replicate_Record'
76       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
77     END IF;
78 
79     OPEN c_debrief_header( p_debrief_header_id );
80     FETCH c_debrief_header INTO r_debrief_header;
81     IF c_debrief_header%NOTFOUND THEN
82       /*** could not find debrief_header record -> exit ***/
83       IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
84         jtm_message_log_pkg.Log_Msg
85         ( p_debrief_header_id
86         , g_table_name
87         , 'Replicate_Record error: Could not find debrief_header_id ' ||
88           p_debrief_header_id
89         , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
90       END IF;
91 
92       CLOSE c_debrief_header;
93       RETURN FALSE;
94     END IF;
95     CLOSE c_debrief_header;
96 
97     IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
98       jtm_message_log_pkg.Log_Msg
99       ( p_debrief_header_id
100       , g_table_name
101       , 'Replicate_Record returned TRUE'
102       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
103     END IF;
104 
105     /** Record matched criteria -> return true ***/
106     RETURN TRUE;
107   END Replicate_Record;
108 
109 
110   /*** Private procedure that replicates given debrief header related data
111        for resource ***/
112   PROCEDURE Insert_ACC_Record
113     ( p_debrief_header_id   IN NUMBER
114      ,p_resource_id         IN NUMBER
115     )
116   IS
117   BEGIN
118     IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
119       jtm_message_log_pkg.Log_Msg
120       ( p_debrief_header_id
121       , g_table_name
122       , 'Entering Insert_ACC_Record'
123       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
124     END IF;
125 
126     IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
127       jtm_message_log_pkg.Log_Msg
128       ( p_debrief_header_id
129       , g_table_name
130       , 'Inserting ACC record for resource_id = ' || p_resource_id
131       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
132     END IF;
133 
134     /*** Insert debrief header ACC record ***/
135     JTM_HOOK_UTIL_PKG.Insert_Acc
136      ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
137       ,P_ACC_TABLE_NAME         => g_acc_table_name
138       ,P_PK1_NAME               => g_pk1_name
139       ,P_PK1_NUM_VALUE          => p_debrief_header_id
140       ,P_RESOURCE_ID            => p_resource_id
141      );
142 
143     IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
144       jtm_message_log_pkg.Log_Msg
145       ( p_debrief_header_id
146       , g_table_name
147       , 'Leaving Insert_ACC_Record'
148       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
149     END IF;
150   END Insert_ACC_Record;
151 
152 
153   /*** Private procedure that re-sends given debrief header to mobile ***/
154   PROCEDURE Update_ACC_Record
155     ( p_debrief_header_id          IN NUMBER
156      ,p_resource_id                IN NUMBER
157      ,p_acc_id                     IN NUMBER
158     )
159   IS
160   BEGIN
161     IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
162       jtm_message_log_pkg.Log_Msg
163       ( p_debrief_header_id
164       , g_table_name
165       , 'Entering Update_ACC_Record'
166       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
167     END IF;
168 
169     IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
170       jtm_message_log_pkg.Log_Msg
171       ( p_debrief_header_id
172       , g_table_name
173       , 'Updating ACC record for resource_id = ' || p_resource_id
174         || fnd_global.local_chr(10) || 'access_id = ' || p_acc_id
175       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
176     END IF;
177 
178     /*** Update debrief header ACC record ***/
179     JTM_HOOK_UTIL_PKG.Update_Acc
180      ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
181       ,P_ACC_TABLE_NAME         => g_acc_table_name
182       ,P_RESOURCE_ID            => p_resource_id
183       ,P_ACCESS_ID              => p_acc_id
184      );
185 
186     IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
187       jtm_message_log_pkg.Log_Msg
188       ( p_debrief_header_id
189       , g_table_name
190       , 'Leaving Update_ACC_Record'
191       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
192     END IF;
193   END Update_ACC_Record;
194 
195 
196   /*** Private procedure that deletes debrief line for resource from acc
197        table ***/
198 
199   PROCEDURE Delete_ACC_Record
200     ( p_debrief_header_id   IN NUMBER
201      ,p_resource_id         IN NUMBER
202     )
203   IS
204   BEGIN
205     IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
206       jtm_message_log_pkg.Log_Msg
207       ( p_debrief_header_id
208       , g_table_name
209       , 'Entering Delete_ACC_Record'
210       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
211     END IF;
212 
213     IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
214       jtm_message_log_pkg.Log_Msg
215       ( p_debrief_header_id
216       , g_table_name
217       , 'Deleting ACC record for resource_id = ' || p_resource_id
218       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
219     END IF;
220 
221     /*** Delete debrief header ACC record ***/
222     JTM_HOOK_UTIL_PKG.Delete_Acc
223      ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
224       ,P_ACC_TABLE_NAME         => g_acc_table_name
225       ,P_PK1_NAME               => g_pk1_name
226       ,P_PK1_NUM_VALUE          => p_debrief_header_id
227       ,P_RESOURCE_ID            => p_resource_id
228      );
229 
230     IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
231       jtm_message_log_pkg.Log_Msg
232       ( p_debrief_header_id
233       , g_table_name
234       , 'Leaving Delete_ACC_Record'
235       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
236     END IF;
237   END Delete_ACC_Record;
238 
239 
240   /*** Public function that gets called when a debrief header needs to be
241        inserted into ACC table.  ***/
242 
243   PROCEDURE Insert_Debrief_Header
244     ( p_debrief_header_id   IN NUMBER
245      ,p_resource_id         IN NUMBER
246     )
247   IS
248     l_acc_id           NUMBER;
249     l_success          BOOLEAN;
250   BEGIN
251     /*** get debug level ***/
252     g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
253 
254     IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
255       jtm_message_log_pkg.Log_Msg
256       ( p_debrief_header_id
257       , g_table_name
258       , 'Entering Insert_Debrief_Header procedure'
259       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
260     END IF;
261 
262     /*** does record match criteria? ***/
263     IF Replicate_Record( p_debrief_header_id ) THEN
264       /*** yes -> insert debrief header acc record ***/
265       Insert_ACC_Record
266       ( p_debrief_header_id
267        ,p_resource_id
268       );
269 
270     END IF;
271 
272     IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
273       jtm_message_log_pkg.Log_Msg
274       ( p_debrief_header_id
275       , g_table_name
276       , 'Leaving Insert_Debrief_Header procedure'
277       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
278     END IF;
279 
280   END Insert_Debrief_Header;
281 
282 
283   /*** Public function that gets called when a debrief header needs to
284        be updated into ACC table.  ***/
285 
286   PROCEDURE Update_Debrief_Header
287     ( p_debrief_header_id   IN NUMBER
288      ,p_resource_id         IN NUMBER
289     )
290   IS
291     l_acc_id           NUMBER;
292     l_success          BOOLEAN;
293   BEGIN
294     /*** get debug level ***/
295     g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
296 
297     IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
298       jtm_message_log_pkg.Log_Msg
299       ( p_debrief_header_id
300       , g_table_name
301       , 'Entering Update_Debrief_Header procedure'
302       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
303     END IF;
304 
305     l_acc_id := JTM_HOOK_UTIL_PKG.Get_Acc_Id(
306                      P_ACC_TABLE_NAME => g_acc_table_name
307                     ,P_PK1_NAME       => g_pk1_name
308                     ,P_PK1_NUM_VALUE  => p_debrief_header_id
309                     ,P_RESOURCE_ID    => p_resource_id);
310 
311     /*** is record already in ACC table? ***/
312     l_success := FALSE;
313     IF l_acc_id = -1 THEN
314       /*** yes -> return TRUE ***/
315       l_success := TRUE;
316     ELSE
317       /*** no -> does record match criteria? ***/
318       IF Replicate_Record( p_debrief_header_id ) THEN
319         /*** yes -> update debrief header acc record ***/
320         Update_ACC_Record
321         ( p_debrief_header_id
322          ,p_resource_id
323          ,l_acc_id
324         );
325 
326       END IF;
327     END IF;
328 
329     IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
330       jtm_message_log_pkg.Log_Msg
331       ( p_debrief_header_id
332       , g_table_name
333       , 'Leaving Update_Debrief_Header procedure'
334       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
335     END IF;
336 
337   END Update_Debrief_Header;
338 
339 
340   /*** Public procedure that gets called when a debrief header needs to
341        be deleted from ACC table.  ***/
342 
343   PROCEDURE Delete_Debrief_Header
344     ( p_debrief_header_id   IN NUMBER
345      ,p_resource_id         IN NUMBER
346     )
347   IS
348     l_acc_id           NUMBER;
349     l_success          BOOLEAN;
350   BEGIN
351     /*** get debug level ***/
352     g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
353 
354     IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
355       jtm_message_log_pkg.Log_Msg
356       ( p_debrief_header_id
357       , g_table_name
358       , 'Entering Delete_Debrief_Header'
359       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
360     END IF;
361 
362     l_acc_id := JTM_HOOK_UTIL_PKG.Get_Acc_Id(
363                      P_ACC_TABLE_NAME => g_acc_table_name
364                     ,P_PK1_NAME       => g_pk1_name
365                     ,P_PK1_NUM_VALUE  => p_debrief_header_id
366                     ,P_RESOURCE_ID    => p_resource_id);
367 
368 
369     /*** is record already in ACC table? ***/
370     IF l_acc_id <> -1 THEN
371       /*** yes -> delete debrief header acc record ***/
372       Delete_ACC_Record
373       ( p_debrief_header_id
374        ,p_resource_id
375       );
376 
377     END IF;
378 
379     IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
380       jtm_message_log_pkg.Log_Msg
381       ( p_debrief_header_id
382       , g_table_name
383       , 'Leaving Delete_Debrief_Header'
384       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
385     END IF;
386   END Delete_Debrief_Header;
387 
388 
389   /** User Hooks Callout Procedure */
390 
391   /* Called before debrief_header Insert */
392   PROCEDURE PRE_INSERT_DEBRIEF_HEADER
393   ( x_return_status out NOCOPY varchar2
394   )
395   IS
396   BEGIN
397     x_return_status := FND_API.G_RET_STS_SUCCESS;
398   END PRE_INSERT_DEBRIEF_HEADER;
399 
400 
401   /* Called after debrief_header Insert */
402   PROCEDURE POST_INSERT_DEBRIEF_HEADER
403   ( x_return_status out NOCOPY varchar2
404   )
405   IS
406   BEGIN
407     x_return_status := FND_API.G_RET_STS_SUCCESS;
408   END POST_INSERT_DEBRIEF_HEADER;
409 
410 
411   /* Called before debrief_header Update */
412   PROCEDURE PRE_UPDATE_DEBRIEF_HEADER
413   ( x_return_status out NOCOPY varchar2
414   )
415   IS
416   BEGIN
417     x_return_status := FND_API.G_RET_STS_SUCCESS;
418   END PRE_UPDATE_DEBRIEF_HEADER;
419 
420 
421   /* Called after debrief_header Update */
422   PROCEDURE POST_UPDATE_DEBRIEF_HEADER (
423      x_return_status out NOCOPY varchar2
424     ) IS
425 
426     l_enabled_flag      VARCHAR2(30);
427     l_debrief_header_id csf_debrief_headers.debrief_header_id%TYPE;
428     l_resource_id       NUMBER;
429     l_replicate         BOOLEAN;
430 
431   BEGIN
432 
433     l_enabled_flag := JTM_PROFILE_UTL_PKG.GET_ENABLE_FLAG_AT_RESP
434                        ( P_APP_SHORT_NAME => 'CSL' );
435 
436     IF l_enabled_flag <> 'Y' THEN
437       x_return_status := FND_API.G_RET_STS_SUCCESS;
438       RETURN;
439     END IF;
440 
441     /*** get debug level ***/
442     g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
443 
444     /*** get debrief_header record details from public API ***/
445     l_debrief_header_id :=
446              CSF_DEBRIEF_Headers_PKG.user_hooks_rec.debrief_header_id;
447     l_resource_id := Get_Resource_Id( l_debrief_header_id );
448 
449     IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
450       jtm_message_log_pkg.Log_Msg
451       ( l_debrief_header_id
452       , g_table_name
453       , 'Entering POST_UPDATE hook'
454       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
455     END IF;
456 
457     /*** Check if debrief_line after update matches criteria ***/
458     IF JTM_HOOK_UTIL_PKG.isMobileFSresource( l_resource_id ) THEN
459       Update_Debrief_Header(l_debrief_header_id, l_resource_id);
460     END IF;
461 
462     x_return_status := FND_API.G_RET_STS_SUCCESS;
463 
464   END POST_UPDATE_DEBRIEF_HEADER;
465 
466 
467   /* Called before debrief_header delete */
468   PROCEDURE PRE_DELETE_DEBRIEF_HEADER
469   ( x_return_status out NOCOPY varchar2
470   )
471   IS
472   BEGIN
473     x_return_status := FND_API.G_RET_STS_SUCCESS;
474   END PRE_DELETE_DEBRIEF_HEADER;
475 
476 
477 
478 END CSL_CSF_DEBRIEF_HDR_ACC_PKG;