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_MTL_SERIAL_NUMBERS_ACC_PKG

Source


1 PACKAGE BODY CSL_MTL_SERIAL_NUMBERS_ACC_PKG AS
2 /* $Header: cslsnacb.pls 115.6 2004/09/30 22:10:00 appldev ship $ */
3 
4 /*** Globals ***/
5 g_debug_level           NUMBER; -- debug level
6 
7 g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
8   JTM_HOOK_UTIL_PKG.t_publication_item_list('MTL_SERIAL_NUMBERS');
9 g_table_name            CONSTANT VARCHAR2(30) := 'MTL_SERIAL_NUMBERS';
10 
11 
12 
13 /**/
14 PROCEDURE INSERT_SERIAL_NUMBERS( p_resource_id IN NUMBER )
15 IS
16  TYPE item_Tab   IS TABLE OF mtl_serial_numbers.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
17  TYPE serial_Tab IS TABLE OF mtl_serial_numbers.serial_number%TYPE INDEX BY BINARY_INTEGER;
18 
19  sequences ASG_DOWNLOAD.ACCESS_LIST;
20  resources ASG_DOWNLOAD.USER_LIST;
21  items     item_Tab;
22  serials   serial_Tab;
23 
24  l_dummy         BOOLEAN;
25 
26 BEGIN
27  /*** get debug level ***/
28  g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
29  IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
30     jtm_message_log_pkg.Log_Msg
31     ( p_resource_id
32     , g_table_name
33     , 'Entering INSERT_SERIAL_NUMBERS'
34     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
35     );
36  END IF;
37 
38  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
39    jtm_message_log_pkg.Log_Msg
40     ( p_resource_id
41     , g_table_name
42     , 'Inserting records for resource: '||p_resource_id
43     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
44  END IF;
45 
46  /*Block insert every item from given subinventory/org not yet in acc table*/
47  SELECT JTM_ACC_TABLE_S.NEXTVAL, INVENTORY_ITEM_ID, SERIAL_NUMBER, p_resource_id
48  BULK COLLECT INTO sequences, items, serials, resources
49  FROM MTL_SERIAL_NUMBERS
50  WHERE CURRENT_STATUS IN (1,3)
51  AND ( CURRENT_SUBINVENTORY_CODE, CURRENT_ORGANIZATION_ID ) IN (
52      SELECT SUBINVENTORY_CODE
53      ,      ORGANIZATION_ID
54      FROM CSP_INV_LOC_ASSIGNMENTS
55      WHERE RESOURCE_ID = p_resource_id
56      AND RESOURCE_TYPE = 'RS_EMPLOYEE'
57      AND SYSDATE BETWEEN NVL( EFFECTIVE_DATE_START, SYSDATE )
58                      AND NVL( EFFECTIVE_DATE_END , SYSDATE ))
59  AND ( INVENTORY_ITEM_ID, SERIAL_NUMBER ) NOT IN (
60        SELECT INVENTORY_ITEM_ID, SERIAL_NUMBER
61        FROM JTM_MTL_SERIAL_NUMBERS_ACC
62        WHERE RESOURCE_ID = p_resource_id );
63 
64  IF sequences.COUNT > 0 THEN
65   FORALL i IN sequences.FIRST..sequences.LAST
66     INSERT INTO JTM_MTL_SERIAL_NUMBERS_ACC(
67                 ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY
68                 , COUNTER, RESOURCE_ID, INVENTORY_ITEM_ID, SERIAL_NUMBER ) VALUES (
69 		sequences(i), sysdate, 1, sysdate, 1, 1, p_resource_id, items(i), serials(i));
70 
71   /*** 1 or more acc rows retrieved -> push to resource ***/
72   IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
73      jtm_message_log_pkg.Log_Msg
74      ( p_resource_id
75       , g_table_name
76       , 'Pushing ' || sequences.COUNT || ' inserted record(s) to resource: '||p_resource_id
77       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
78      );
79   END IF;
80   l_dummy := asg_download.markdirty(
81               P_PUB_ITEM     => g_publication_item_name(1)
82             , P_ACCESSLIST   => sequences
83             , P_RESOURCELIST => resources
84             , P_DML_TYPE     => 'I'
85             , P_TIMESTAMP    => SYSDATE
86   );
87 
88  END IF;
89  IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
90     jtm_message_log_pkg.Log_Msg
91     ( p_resource_id
92     , g_table_name
93     , 'Leaving INSERT_SERIAL_NUMBERS'
94     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
95     );
96  END IF;
97 EXCEPTION
98  WHEN OTHERS THEN
99   jtm_message_log_pkg.Log_Msg
100     ( p_resource_id
101     , g_table_name
102     , 'INSERT_SERIAL_NUMBERS'||fnd_global.local_chr(10)||
103       'Error: '||sqlerrm
104     , JTM_HOOK_UTIL_PKG.g_debug_level_error);
105   RAISE;
106 END INSERT_SERIAL_NUMBERS;
107 
108 /**/
109 PROCEDURE DELETE_SERIAL_NUMBERS
110 IS
111  l_tab_access_id   ASG_DOWNLOAD.ACCESS_LIST;
112  l_tab_resource_id ASG_DOWNLOAD.USER_LIST;
113  l_dummy BOOLEAN;
114 
115  /*Delete all serial numbers from acc table for which the assigned subinventory is no more,
116    or for which the status has changed*/
117  CURSOR c_remove IS
118   /* Performance bug (3920090)fixing */
119  /*
120    SELECT acc.ACCESS_ID, acc.RESOURCE_ID
121    FROM   JTM_MTL_SERIAL_NUMBERS_ACC acc
122    ,      MTL_SERIAL_NUMBERS msn
123    WHERE  msn.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID
124    AND    msn.SERIAL_NUMBER = acc.SERIAL_NUMBER
125    AND ( msn.CURRENT_SUBINVENTORY_CODE, msn.CURRENT_ORGANIZATION_ID ) NOT IN (
126      SELECT SUBINVENTORY_CODE
127      ,      ORGANIZATION_ID
128      FROM CSP_INV_LOC_ASSIGNMENTS
129      WHERE RESOURCE_ID = acc.RESOURCE_ID
130      AND SYSDATE BETWEEN NVL( EFFECTIVE_DATE_START, SYSDATE )
131                      AND NVL( EFFECTIVE_DATE_END , SYSDATE ))
132   UNION
133   SELECT acc.ACCESS_ID, acc.RESOURCE_ID
134   FROM   JTM_MTL_SERIAL_NUMBERS_ACC acc
135   ,      MTL_SERIAL_NUMBERS msn
136   WHERE  msn.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID
137   AND    msn.SERIAL_NUMBER = acc.SERIAL_NUMBER
138   AND    msn.CURRENT_STATUS NOT IN (1,3);
139 */
140     SELECT /*+ INDEX (msn MTL_SERIAL_NUMBERS_U1) */
141     acc.ACCESS_ID, acc.RESOURCE_ID
142     FROM JTM_MTL_SERIAL_NUMBERS_ACC acc
143     , MTL_SERIAL_NUMBERS msn
144     WHERE msn.SERIAL_NUMBER = acc.SERIAL_NUMBER
145     AND msn.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID
146     AND (NOT EXISTS
147     (SELECT 1
148     FROM CSP_INV_LOC_ASSIGNMENTS cila
149     WHERE cila.RESOURCE_ID = acc.RESOURCE_ID
150     AND cila.organization_id = msn.current_organization_id
151     AND cila.subinventory_code = msn.current_subinventory_code
152     AND SYSDATE BETWEEN NVL( cila.EFFECTIVE_DATE_START, SYSDATE )
153     AND NVL( cila.EFFECTIVE_DATE_END , SYSDATE)
154     )
155     OR msn.CURRENT_STATUS NOT IN (1,3)
156     );
157 
158 BEGIN
159  /*** get debug level ***/
160  g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
161  IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
162     jtm_message_log_pkg.Log_Msg
163     ( 0
164     , g_table_name
165     , 'Entering DELETE_SERIAL_NUMBERS'
166     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
167     );
168  END IF;
169 
170  /*Delete not used records*/
171  OPEN c_remove;
172  FETCH c_remove BULK COLLECT INTO l_tab_access_id, l_tab_resource_id;
173  /*Call oracle lite*/
174  IF l_tab_access_id.COUNT > 0 THEN
175   /*** 1 or more acc rows retrieved -> push to resource ***/
176   IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
177      jtm_message_log_pkg.Log_Msg
178      (  0
179       , g_table_name
180       , 'Deleting ' || l_tab_access_id.COUNT || ' invalid record(s)'
181       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
182      );
183    END IF;
184    /*** push to oLite using asg_download ***/
185    l_dummy := asg_download.markdirty(
186               P_PUB_ITEM     => g_publication_item_name(1)
187             , P_ACCESSLIST   => l_tab_access_id
188             , P_RESOURCELIST => l_tab_resource_id
189             , P_DML_TYPE     => 'D'
190             , P_TIMESTAMP    => SYSDATE
191    );
192 
193    /*To avoid a mismatch only delete records which are marked dirty*/
194    FORALL i IN l_tab_access_id.FIRST..l_tab_access_id.LAST
195      DELETE JTM_MTL_SERIAL_NUMBERS_ACC
196      WHERE ACCESS_ID = l_tab_access_id(i);
197  END IF;
198  CLOSE c_remove;
199 
200  IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
201     jtm_message_log_pkg.Log_Msg
202     ( 0
203     , g_table_name
204     , 'Leaving DELETE_SERIAL_NUMBERS'
205     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
206     );
207  END IF;
208 
209 EXCEPTION
210  WHEN OTHERS THEN
211   jtm_message_log_pkg.Log_Msg
212     ( 0
213     , g_table_name
214     , 'DELETE_SERIAL_NUMBERS'||fnd_global.local_chr(10)||
215       'Error: '||sqlerrm
216     , JTM_HOOK_UTIL_PKG.g_debug_level_error);
217   RAISE;
218 END DELETE_SERIAL_NUMBERS;
219 
220 PROCEDURE UPDATE_SERIAL_NUMBERS( p_date IN DATE )
221 IS
222  /*Get all existing and valid records which are changed*/
223  CURSOR c_changed( b_date DATE ) IS
224   SELECT acc.ACCESS_ID, acc.RESOURCE_ID
225   FROM JTM_MTL_SERIAL_NUMBERS_ACC acc
226   ,    MTL_SERIAL_NUMBERS msn
227   WHERE msn.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID
228   AND   msn.SERIAL_NUMBER = acc.SERIAL_NUMBER
229   AND   msn.LAST_UPDATE_DATE  >= p_date
230   AND ( msn.CURRENT_SUBINVENTORY_CODE, msn.CURRENT_ORGANIZATION_ID ) IN (
231      SELECT SUBINVENTORY_CODE
232      ,      ORGANIZATION_ID
233      FROM CSP_INV_LOC_ASSIGNMENTS
234      WHERE RESOURCE_ID = acc.RESOURCE_ID
235      AND SYSDATE BETWEEN NVL( EFFECTIVE_DATE_START, SYSDATE )
236                      AND NVL( EFFECTIVE_DATE_END , SYSDATE ));
237 
238  l_tab_access_id   ASG_DOWNLOAD.ACCESS_LIST;
239  l_tab_resource_id ASG_DOWNLOAD.USER_LIST;
240  l_dummy BOOLEAN;
241 
242 BEGIN
243  /*** get debug level ***/
244  g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
245  IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
246     jtm_message_log_pkg.Log_Msg
247     ( 0
248     , g_table_name
249     , 'Entering UPDATE_SERIAL_NUMBERS'
250     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
251     );
252  END IF;
253 
254  /*Fetch all changed system items that are in the acc table*/
255  OPEN c_changed(  p_date );
256  FETCH c_changed BULK COLLECT INTO l_tab_access_id, l_tab_resource_id;
257  /*Call oracle lite*/
258  IF l_tab_access_id.COUNT > 0 THEN
259   /*** 1 or more acc rows retrieved -> push to resource ***/
260   IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
261      jtm_message_log_pkg.Log_Msg
262      ( 0
263       , g_table_name
264       , 'Updating ' || l_tab_access_id.COUNT || ' changed record(s)'
265       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
266      );
267   END IF;
268   l_dummy := asg_download.markdirty(
269               P_PUB_ITEM     => g_publication_item_name(1)
270             , P_ACCESSLIST   => l_tab_access_id
271             , P_RESOURCELIST => l_tab_resource_id
272             , P_DML_TYPE     => 'U'
273             , P_TIMESTAMP    => SYSDATE
274   );
275 
276  END IF;
277  CLOSE c_changed;
278 
279  IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
280     jtm_message_log_pkg.Log_Msg
281     ( 0
282     , g_table_name
283     , 'Leaving UPDATE_SERIAL_NUMBERS'
284     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
285     );
286  END IF;
287 
288 EXCEPTION
289  WHEN OTHERS THEN
290   jtm_message_log_pkg.Log_Msg
291     ( 0
292     , g_table_name
293     , 'UPDATE_SERIAL_NUMBERS'||fnd_global.local_chr(10)||
294       'Error: '||sqlerrm
295     , JTM_HOOK_UTIL_PKG.g_debug_level_error);
296   RAISE;
297 END UPDATE_SERIAL_NUMBERS;
298 
299 /**/
300 PROCEDURE CON_REQUEST_SERIAL_NUMBERS
301 IS
302  PRAGMA AUTONOMOUS_TRANSACTION;
303  l_date              DATE;
304  l_subinventory_code VARCHAR2(30);
305 
306  /*** cursor retrieving list of resources subscribed to publication item ***/
307   CURSOR c_mobile_users
308    IS
309     SELECT res.resource_id
310     FROM  asg_pub                pub
311     ,     asg_pub_responsibility pubresp
312     ,     fnd_user_resp_groups   usrresp
313     ,     fnd_user               usr
314     ,     jtf_rs_resource_extns  res
315     ,     asg_user               au
316     WHERE res.resource_id = au.resource_id
317     AND   pub.name = 'SERVICEL'
318     AND   pub.enabled='Y'
319     AND   pub.status='Y'
320     AND   pub.pub_id = pubresp.pub_id
321     AND   pubresp.responsibility_id = usrresp.responsibility_id
322     AND   TRUNC(sysdate) BETWEEN TRUNC(NVL(usrresp.start_date,sysdate))
323                              AND TRUNC(NVL(usrresp.end_date,sysdate))
324     AND   usrresp.user_id = usr.user_id
325     AND   TRUNC(sysdate) BETWEEN TRUNC(NVL(usr.start_date,sysdate))
326                              AND TRUNC(NVL(usr.end_date,sysdate))
327     AND   usr.user_id = res.user_id
328     AND   TRUNC(sysdate) BETWEEN TRUNC(NVL(res.start_date_active,sysdate))
329                              AND TRUNC(NVL(res.end_date_active,sysdate));
330 
331   /*** get the last run date of the concurent program ***/
332   CURSOR  c_LastRundate
333   IS
334     select LAST_RUN_DATE
335     from   JTM_CON_REQUEST_DATA
336     where  package_name =  'CSL_MTL_SERIAL_NUMBERS_ACC_PKG'
337     AND    procedure_name = 'CON_REQUEST_SERIAL_NUMBERS';
338 
339 BEGIN
340  /*** get debug level ***/
341  g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
342  IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
343     jtm_message_log_pkg.Log_Msg
344     ( 0
345     , g_table_name
346     , 'Entering CON_REQUEST_SERIAL_NUMBERS'
347     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
348     );
349  END IF;
350 
351  /*** First retrieve last run date of the conccurent program ***/
352  OPEN  c_LastRundate;
353  FETCH c_LastRundate  INTO l_date;
354  IF c_LastRundate%FOUND THEN
355    /*Record is seeded so program should run*/
356    IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
357       jtm_message_log_pkg.Log_Msg
358       ( 0
359       , g_table_name
360       , 'Updating LAST_RUN_DATE from '||l_date||' to '||sysdate
361       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
362       );
363    END IF;
364 
365    /*Update the last run date*/
366    UPDATE JTM_CON_REQUEST_DATA
367    SET LAST_RUN_DATE = SYSDATE
368    WHERE package_name =  'CSL_MTL_SERIAL_NUMBERS_ACC_PKG'
369    AND   procedure_name = 'CON_REQUEST_SERIAL_NUMBERS';
370 
371    COMMIT;
372    /*First remove all records no longer required*/
373    DELETE_SERIAL_NUMBERS;
374    COMMIT;
375    /*Second, check for updated serial numbers*/
376    UPDATE_SERIAL_NUMBERS( l_date);
377    COMMIT;
378    FOR r_mobile_user IN c_mobile_users LOOP
379     /*Third, insert all serial numbers that are not yet in the acc table*/
380     INSERT_SERIAL_NUMBERS( r_mobile_user.resource_id );
381     COMMIT;
382    END LOOP;
383  END IF;--c_LastRundate%FOUND
384  CLOSE c_LastRundate;
385 
386  IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
387     jtm_message_log_pkg.Log_Msg
388     ( 0
389     , g_table_name
390     , 'Leaving CON_REQUEST_SERIAL_NUMBERS'
391     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
392     );
393  END IF;
394  COMMIT;
395 EXCEPTION
396  WHEN OTHERS THEN
397   jtm_message_log_pkg.Log_Msg
398     ( 0
399     , g_table_name
400     , 'CON_REQUEST_SERIAL_NUMBERS'||fnd_global.local_chr(10)||
401       'Error: '||sqlerrm
402     , JTM_HOOK_UTIL_PKG.g_debug_level_error);
403   ROLLBACK;
404   RETURN;
405 END CON_REQUEST_SERIAL_NUMBERS;
406 
407 END CSL_MTL_SERIAL_NUMBERS_ACC_PKG;