[Home] [Help]
PACKAGE BODY: APPS.CSL_MTL_SYSTEM_ITEMS_ACC_PKG
Source
1 PACKAGE BODY CSL_MTL_SYSTEM_ITEMS_ACC_PKG AS
2 /* $Header: cslsiacb.pls 120.1 2011/06/16 07:35:37 saradhak ship $ */
3
4 /*** Globals ***/
5 g_acc_table_name CONSTANT VARCHAR2(30) := 'JTM_MTL_SYSTEM_ITEMS_ACC';
6 g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
7 JTM_HOOK_UTIL_PKG.t_publication_item_list('MTL_SYSTEM_ITEMS_VL');
8
9 --Bug 3746689
10 g_explab_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
11 JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_MTL_EXPENSE_LABOR_ITEM');
12
13 g_table_name CONSTANT VARCHAR2(30) := 'MTL_SYSTEM_ITEMS_B';
14 g_pk1_name CONSTANT VARCHAR2(30) := 'INVENTORY_ITEM_ID';
15 g_pk2_name CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
16 g_debug_level NUMBER; -- debug level
17
18 /*** Function that checks if user should be replicated. Returns TRUE if
19 it should ***/
20 FUNCTION Replicate_Record
21 ( p_organization_id NUMBER
22 )
23 RETURN BOOLEAN
24 IS
25
26 BEGIN
27 /*** get debug level ***/
28 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
29
30 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
31 jtm_message_log_pkg.Log_Msg
32 ( p_organization_id
33 , g_table_name
34 , 'Entering Replicate_Record'
35 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
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_organization_id
41 , g_table_name
42 , 'Replicate_Record returned TRUE'
43 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
44 END IF;
45
46 /** Record matched criteria -> return true ***/
47 RETURN TRUE;
48 END Replicate_Record;
49
50
51 /*
52 Private procedure that inserts/updates Expense/Labor items for an org
53 and calls markdirty for all inserted records. Bug 3724165
54 */
55 PROCEDURE INSERT_ACC_REC_MARKDIRTY_EXP( p_organization_id IN NUMBER
56 , p_resource_id IN NUMBER
57 , p_old_org_id IN NUMBER)
58 IS
59
60 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
61 l_tab_resource_id ASG_DOWNLOAD.USER_LIST;
62
63 TYPE item_Tab IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
64 TYPE org_Tab IS TABLE OF mtl_system_items_b.organization_id%TYPE INDEX BY BINARY_INTEGER;
65 items item_Tab;
66 organizations org_Tab;
67
68 l_dummy BOOLEAN;
69 l_stmt VARCHAR2 (4000);
70
71 BEGIN
72
73 IF ( p_organization_id <> p_old_org_id ) THEN
74 UPDATE jtm_mtl_system_items_acc
75 SET counter = counter + 1
76 , last_update_date = SYSDATE
77 , last_updated_by = 1
78 WHERE resource_id = p_resource_id
79 AND (inventory_item_id, organization_id)
80 IN (SELECT inventory_item_id, organization_id
81 FROM mtl_system_items_b msi, cs_billing_type_categories cbtc
82 WHERE organization_id = p_organization_id
83 and msi.material_billable_flag = cbtc.billing_type (+)
84 AND cbtc.billing_category IN ('E','L'));
85 END IF;
86
87 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
88 jtm_message_log_pkg.Log_Msg
89 ( p_organization_id
90 , g_table_name
91 , 'Entering INSERT_ACC_REC_MARKDIRTY_EXP'
92 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
93 );
94 END IF;
95
96 /*Block insert every item from given org not yet in acc table*/
97 SELECT jtm_acc_table_s.NEXTVAL, inventory_item_id, organization_id,
98 p_resource_id
99 BULK COLLECT INTO
100 l_tab_access_id, items, organizations, l_tab_resource_id
101 FROM mtl_system_items_b msi, cs_billing_type_categories cbtc
102 WHERE ( inventory_item_id, organization_id ) NOT IN (
103 SELECT inventory_item_id, organization_id
104 FROM jtm_mtl_system_items_acc
105 WHERE resource_id = p_resource_id )
106 AND msi.material_billable_flag = cbtc.billing_type (+)
107 AND cbtc.billing_category IN ('E', 'L')
108 AND organization_id = p_organization_id;
109
110 IF l_tab_access_id.COUNT > 0 THEN
111 /*** 1 or more acc rows retrieved -> push to resource ***/
112 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
113 jtm_message_log_pkg.Log_Msg
114 ( p_organization_id
115 , g_table_name
116 , 'Pushing ' || l_tab_access_id.COUNT ||
117 ' inserted record(s) to resource: '||p_resource_id
118 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
119 );
120 END IF;
121
122 FORALL i IN l_tab_access_id.FIRST..l_tab_access_id.LAST
123 INSERT INTO jtm_mtl_system_items_acc(
124 access_id, last_update_date, last_updated_by, creation_date,
125 created_by , counter, resource_id, inventory_item_id, organization_id)
126 VALUES (
127 l_tab_access_id(i), SYSDATE, 1, SYSDATE, 1, 1, p_resource_id,
128 items(i), organizations(i));
129
130 IF l_tab_access_id.COUNT > 0 THEN -- For Expense/Labor Items
131 l_dummy := asg_download.markdirty(
132 P_PUB_ITEM => g_explab_publication_item_name(1)
133 , P_ACCESSLIST => l_tab_access_id
134 , P_RESOURCELIST => l_tab_resource_id
135 , P_DML_TYPE => 'I'
136 , P_TIMESTAMP => SYSDATE
137 );
138 END IF;
139
140 END IF; -- End of Insert of Expense and Labor items
141
142 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
143 jtm_message_log_pkg.Log_Msg
144 ( p_organization_id
145 , g_table_name
146 , 'Leaving INSERT_ACC_REC_MARKDIRTY_EXP'
147 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
148 );
149 END IF;
150
151 EXCEPTION
152 WHEN OTHERS THEN
153 jtm_message_log_pkg.Log_Msg
154 ( p_organization_id
155 , g_table_name
156 , 'INSERT_ACC_REC_MARKDIRTY_EXP'||fnd_global.local_chr(10)||
157 'Error: '||sqlerrm
158 , JTM_HOOK_UTIL_PKG.g_debug_level_error);
159 RAISE;
160 END INSERT_ACC_REC_MARKDIRTY_EXP;
161
162
163
164
165 /*** Private procedure that inserts given item related data for resource ***/
166 PROCEDURE Insert_ACC_Record
167 ( p_inventory_item_id IN NUMBER
168 , p_organization_id IN NUMBER
169 , p_resource_id IN NUMBER
170 )
171 IS
172
173 --Bug 3908277 - Static Query converted into Cursor.
174 CURSOR c_billCat(b_inventory_item_id NUMBER, b_organization_id NUMBER)
175 IS
176 SELECT billing_category
177 FROM MTL_SYSTEM_ITEMS_B msi, CS_BILLING_TYPE_CATEGORIES cbtc
178 WHERE msi.material_billable_flag = cbtc.billing_type (+)
179 AND inventory_item_id = p_inventory_item_id
180 AND organization_id = p_organization_id;
181
182 --Added by UTEKUMAL on 16-Feb-2004 to segregate the Item by Billing Category
183 --Bug 3746689
184 l_billCat CS_BILLING_TYPE_CATEGORIES.BILLING_CATEGORY%TYPE;
185
186 BEGIN
187 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
188 THEN
189 jtm_message_log_pkg.Log_Msg
190 ( p_organization_id
191 , g_table_name
192 , 'Entering Insert_ACC_Record'
193 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
194 );
195 END IF;
196
197 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
198 THEN
199 jtm_message_log_pkg.Log_Msg
200 ( p_organization_id
201 , g_table_name
202 , 'Inserting ACC record for resource_id = ' || p_resource_id
203 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
204 END IF;
205
206 --Added by UTEKUMAL on 16-Feb-2004 to segregate the Item by Billing Category
207 --Bug 3908277 - Static Query converted into Cursor.
208 OPEN c_billCat(p_inventory_item_id, p_organization_id);
209 FETCH c_billCat into l_billCat;
210 CLOSE c_billCat;
211
212 /*** Insert item ACC record ***/
213 IF l_billCat = 'E' OR l_billCat = 'L' THEN
214 JTM_HOOK_UTIL_PKG.Insert_Acc
215 ( P_PUBLICATION_ITEM_NAMES => g_explab_publication_item_name
216 , P_ACC_TABLE_NAME => g_acc_table_name
217 , P_RESOURCE_ID => p_resource_id
218 , P_PK1_NAME => g_pk1_name
219 , P_PK1_NUM_VALUE => p_inventory_item_id
220 , P_PK2_NAME => g_pk2_name
221 , P_PK2_NUM_VALUE => p_organization_id
222 );
223 ELSE
224 JTM_HOOK_UTIL_PKG.Insert_Acc
225 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
226 , P_ACC_TABLE_NAME => g_acc_table_name
227 , P_RESOURCE_ID => p_resource_id
228 , P_PK1_NAME => g_pk1_name
229 , P_PK1_NUM_VALUE => p_inventory_item_id
230 , P_PK2_NAME => g_pk2_name
231 , P_PK2_NUM_VALUE => p_organization_id
232 );
233 END IF;
234
235 CSL_MTL_SEC_LOCATORS_ACC_PKG.Insert_Secondary_Locators
236 ( p_inventory_item_id => p_inventory_item_id
237 , p_organization_id => p_organization_id
238 , p_resource_id => p_resource_id
239 );
240
241 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
242 THEN
243 jtm_message_log_pkg.Log_Msg
244 ( p_organization_id
245 , g_table_name
246 , 'Leaving Insert_ACC_Record'
247 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
248 );
249 END IF;
250 END Insert_ACC_Record;
251
252 /*** Private procedure that deletes given item related data for resource ***/
253 PROCEDURE Delete_ACC_Record
254 ( p_inventory_item_id IN NUMBER
255 , p_organization_id IN NUMBER
256 , p_resource_id IN NUMBER
257 )
258 IS
259
260 --Bug 3908277 - Static Query converted into Cursor.
261 CURSOR c_billCat(b_inventory_item_id NUMBER, b_organization_id NUMBER)
262 IS
263 SELECT billing_category
264 FROM MTL_SYSTEM_ITEMS_B msi, CS_BILLING_TYPE_CATEGORIES cbtc
265 WHERE msi.material_billable_flag = cbtc.billing_type (+)
266 AND inventory_item_id = p_inventory_item_id
267 AND organization_id = p_organization_id;
268
269 --Bug 3746689
270 l_billCat CS_BILLING_TYPE_CATEGORIES.BILLING_CATEGORY%TYPE;
271
272 BEGIN
273 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
274 THEN
275 jtm_message_log_pkg.Log_Msg
276 ( p_organization_id
277 , g_table_name
278 , 'Entering Delete_ACC_Record'
279 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
280 );
281 END IF;
282
283 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
284 THEN
285 jtm_message_log_pkg.Log_Msg
286 ( p_organization_id
287 , g_table_name
288 , 'Deleting ACC record for resource_id = ' || p_resource_id
289 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
290 );
291 END IF;
292
293 --Added by UTEKUMAL on 16-Feb-2004 to segregate the Item by Billing Category
294 --Bug 3908277 - Static Query converted into Cursor.
295 OPEN c_billCat(p_inventory_item_id, p_organization_id);
296 FETCH c_billCat into l_billCat;
297 CLOSE c_billCat;
298
299 /*** Delete item ACC record ***/
300 IF l_billCat = 'E' OR l_billCat = 'L' THEN
301 JTM_HOOK_UTIL_PKG.Delete_Acc
302 ( P_PUBLICATION_ITEM_NAMES => g_explab_publication_item_name
303 , P_ACC_TABLE_NAME => g_acc_table_name
304 , P_RESOURCE_ID => p_resource_id
305 , P_PK1_NAME => g_pk1_name
306 , P_PK1_NUM_VALUE => p_inventory_item_id
307 , P_PK2_NAME => g_pk2_name
308 , P_PK2_NUM_VALUE => p_organization_id
309 );
310 ELSE
311 JTM_HOOK_UTIL_PKG.Delete_Acc
312 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
313 , P_ACC_TABLE_NAME => g_acc_table_name
314 , P_RESOURCE_ID => p_resource_id
315 , P_PK1_NAME => g_pk1_name
316 , P_PK1_NUM_VALUE => p_inventory_item_id
317 , P_PK2_NAME => g_pk2_name
318 , P_PK2_NUM_VALUE => p_organization_id
319 );
320 END IF;
321
322
323 CSL_MTL_SEC_LOCATORS_ACC_PKG.Delete_Secondary_Locators
324 ( p_inventory_item_id => p_inventory_item_id
325 , p_organization_id => p_organization_id
326 , p_resource_id => p_resource_id
327 );
328
329 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
330 THEN
331 jtm_message_log_pkg.Log_Msg
332 ( p_organization_id
333 , g_table_name
334 , 'Leaving Delete_ACC_Record'
335 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
336 );
337 END IF;
338 END Delete_ACC_Record;
339
340 /***
341 Public function that gets called when a system item needs to be inserted into ACC table.
342 ***/
343 PROCEDURE Pre_Insert_Child
344 ( p_inventory_item_id IN NUMBER
345 , p_organization_id IN NUMBER
346 , p_resource_id IN NUMBER
347 )
348 IS
349 BEGIN
350 /*** get debug level ***/
351 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
352
353 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
354 THEN
355 jtm_message_log_pkg.Log_Msg
356 ( p_organization_id
357 , g_table_name
358 , 'Entering Pre_Insert_Child'
359 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
360 );
361 END IF;
362
363 /*** no -> does record match criteria? ***/
364 IF Replicate_Record( p_organization_id )
365 THEN
366 /*** yes -> insert system item in acc record ***/
367 Insert_ACC_Record
368 ( p_inventory_item_id
369 , p_organization_id
370 , p_resource_id
371 );
372 END IF;
373
374 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
375 THEN
376 jtm_message_log_pkg.Log_Msg
377 ( p_organization_id
378 , g_table_name
379 , 'Leaving Pre_Insert_Child'
380 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
381 );
382 END IF;
383
384 END Pre_Insert_Child;
385
386 /***
387 Public function that gets called when a system item needs
388 to be deleted from ACC table.
389 ***/
390 PROCEDURE Post_Delete_Child
391 ( p_inventory_item_id IN NUMBER
392 , p_organization_id IN NUMBER
393 , p_resource_id IN NUMBER
394 )
395 IS
396 l_acc_id NUMBER;
397 BEGIN
398 /*** get debug level ***/
399 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
400
401 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
402 THEN
403 jtm_message_log_pkg.Log_Msg
404 ( p_organization_id
405 , g_table_name
406 , 'Entering Post_Delete_Child'
407 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
408 );
409 END IF;
410
411 l_acc_id := JTM_HOOK_UTIL_PKG.Get_Acc_Id
412 ( P_ACC_TABLE_NAME => g_acc_table_name
413 , P_RESOURCE_ID => p_resource_id
414 , P_PK1_NAME => g_pk1_name
415 , P_PK1_NUM_VALUE => p_inventory_item_id
416 , P_PK2_NAME => g_pk2_name
417 , P_PK2_NUM_VALUE => p_organization_id
418 );
419
420 /*** is record already in ACC table? ***/
421 IF l_acc_id <> -1
422 THEN
423 Delete_ACC_Record
424 ( p_inventory_item_id
425 , p_organization_id
426 , p_resource_id
427 );
428 END IF;
429
430 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
431 THEN
432 jtm_message_log_pkg.Log_Msg
433 ( p_organization_id
434 , g_table_name
435 , 'Leaving Post_Delete_Child'
436 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
437 );
438 END IF;
439
440 END Post_Delete_Child;
441
442 /*Private procedure that retrieves inventory org and category profiles */
443 PROCEDURE GET_PROFILES( p_user_id IN NUMBER
444 , p_responsibility_id IN NUMBER
445 , p_application_id IN NUMBER
446 , x_organization_id OUT NOCOPY NUMBER
447 , x_category_set_id OUT NOCOPY NUMBER
448 , x_category_id OUT NOCOPY NUMBER )
449 IS
450 BEGIN
451 /*Set the environment*/
452 FND_GLOBAL.APPS_INITIALIZE( USER_ID => p_user_id
453 , RESP_ID => p_responsibility_id
454 , RESP_APPL_ID => p_application_id
455 );
456 /*Get the profile values*/
457 -- Bug 3724123
458 x_organization_id := TO_NUMBER( fnd_profile.value('CS_INV_VALIDATION_ORG') );
459 x_category_set_id := TO_NUMBER( fnd_profile.value('CSL_ITEM_CATEGORY_SET_FILTER') );
460 x_category_id := TO_NUMBER( fnd_profile.value('CSL_ITEM_CATEGORY_FILTER') );
461 END GET_PROFILES;
462
463 /*Private procedure that inserts a record into CSL_RESOURCE_INVENTORY_ORG*/
464 PROCEDURE INSERT_RESOURCE_PROFILE_REC( p_resource_id IN NUMBER
465 , p_organization_id IN NUMBER
466 , p_category_set_id IN NUMBER
467 , p_category_id IN NUMBER )
468 IS
469 BEGIN
470 INSERT INTO CSL_RESOURCE_INVENTORY_ORG ( RESOURCE_ID, ORGANIZATION_ID, LAST_UPDATE_DATE
471 , LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, CATEGORY_SET_ID, CATEGORY_ID )
472 VALUES ( p_resource_id, p_organization_id
473 , SYSDATE, 1, SYSDATE, 1, p_category_set_id, p_category_id );
474 END INSERT_RESOURCE_PROFILE_REC;
475
476 /*Private procedure that updates a record in CSL_RESOURCE_INVENTORY_ORG*/
477 PROCEDURE UPDATE_RESOURCE_PROFILE_REC( p_resource_id IN NUMBER
478 , p_organization_id IN NUMBER
479 , p_category_set_id IN NUMBER
480 , p_category_id IN NUMBER )
481 IS
482 BEGIN
483 UPDATE CSL_RESOURCE_INVENTORY_ORG
484 SET ORGANIZATION_ID = p_organization_id
485 , CATEGORY_SET_ID = p_category_set_id
486 , CATEGORY_ID = p_category_id
487 , LAST_UPDATE_DATE = SYSDATE
488 WHERE RESOURCE_ID = p_resource_id;
489 END UPDATE_RESOURCE_PROFILE_REC;
490
491 /*Private procedure that deletes a record from CSL_RESOURCE_INVENTORY_ORG*/
492 PROCEDURE DELETE_RESOURCE_PROFILE_REC( p_resource_id IN NUMBER )
493 IS
494 BEGIN
495 DELETE CSL_RESOURCE_INVENTORY_ORG
496 WHERE RESOURCE_ID = p_resource_id;
497 EXCEPTION
498 WHEN NO_DATA_FOUND THEN
499 NULL;
500 WHEN OTHERS THEN
501 RAISE;
502 END DELETE_RESOURCE_PROFILE_REC;
503
504 /*Delete all acc records for resource without markdirty */
505 PROCEDURE DELETE_ALL_ACC_RECORDS( p_resource_id IN NUMBER
506 , x_return_status OUT NOCOPY VARCHAR2 )
507 IS
508 BEGIN
509 /*** get debug level ***/
510 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
511 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
512 jtm_message_log_pkg.Log_Msg
513 ( p_resource_id
514 , g_table_name
515 , 'Entering DELETE_ALL_ACC_RECORDS'
516 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
517 );
518 END IF;
519
520 /*Do the actual delete*/
521 DELETE JTM_MTL_SYSTEM_ITEMS_ACC
522 WHERE RESOURCE_ID = p_resource_id;
523
524 DELETE CSL_MTL_SECONDARY_LOCATORS_ACC
525 WHERE RESOURCE_ID = p_resource_id;
526
527 /*Reduce rollback segments*/
528 COMMIT;
529
530 /*Delete the resource from CSL_RESOURCE_INVENTORY_ORG*/
531 DELETE_RESOURCE_PROFILE_REC( p_resource_id => p_resource_id );
532
533 /*Reduce rollback segments*/
534 COMMIT;
535
536 x_return_status := FND_API.G_RET_STS_SUCCESS;
537
538 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
539 jtm_message_log_pkg.Log_Msg
540 ( p_resource_id
541 , g_table_name
542 , 'Leaving DELETE_ALL_ACC_RECORDS'
543 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
544 );
545 END IF;
546 EXCEPTION
547 WHEN OTHERS THEN
548 jtm_message_log_pkg.Log_Msg
549 ( p_resource_id
550 , g_table_name
551 , 'DELETE_ALL_ACC_RECORDS'||fnd_global.local_chr(10)||
552 'Error: '||sqlerrm
553 , JTM_HOOK_UTIL_PKG.g_debug_level_error);
554 x_return_status := FND_API.G_RET_STS_ERROR;
555 /*Reduce rollback segments*/
556 ROLLBACK;
557 RAISE;
558 END DELETE_ALL_ACC_RECORDS;
559
560 PROCEDURE INSERT_ALL_ACC_RECORDS( p_resource_id IN NUMBER, x_return_status OUT NOCOPY VARCHAR2 )
561 IS
562 CURSOR c_mobile_resp ( b_resource_id NUMBER ) IS
563 SELECT usr.user_id
564 , usrresp.responsibility_id
565 , usrresp.responsibility_application_id
566 FROM asg_pub pub
567 , asg_pub_responsibility pubresp
568 , fnd_user_resp_groups usrresp
569 , fnd_user usr
570 , jtf_rs_resource_extns res
571 , asg_user au
572 WHERE res.resource_id = b_resource_id
573 AND pub.name = 'SERVICEL'
574 AND pub.enabled='Y'
575 AND pub.status='Y'
576 AND pub.pub_id = pubresp.pub_id
577 AND pubresp.responsibility_id = usrresp.responsibility_id
578 AND TRUNC(sysdate) BETWEEN TRUNC(NVL(usrresp.start_date,sysdate))
579 AND TRUNC(NVL(usrresp.end_date,sysdate))
580 AND usrresp.user_id = usr.user_id
581 AND TRUNC(sysdate) BETWEEN TRUNC(NVL(usr.start_date,sysdate))
582 AND TRUNC(NVL(usr.end_date,sysdate))
583 AND usr.user_id = res.user_id
584 AND TRUNC(sysdate) BETWEEN TRUNC(NVL(res.start_date_active,sysdate))
585 AND TRUNC(NVL(res.end_date_active,sysdate));
586 r_mobile_resp c_mobile_resp%ROWTYPE;
587
588 l_profile_org_id NUMBER;
589 l_profile_category_id NUMBER;
590 l_profile_category_set_id NUMBER;
591
592 l_stmt VARCHAR2(4000);
593 l_stmt1 VARCHAR2(4000);
594
595 BEGIN
596 /*** get debug level ***/
597 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
598 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
599 jtm_message_log_pkg.Log_Msg
600 ( p_resource_id
601 , g_table_name
602 , 'Entering INSERT_ALL_ACC_RECORDS'
603 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
604 );
605 END IF;
606
607 /*** get user_id and mobile responsibility_id ***/
608 OPEN c_mobile_resp( p_resource_id );
609 FETCH c_mobile_resp INTO r_mobile_resp;
610 IF c_mobile_resp%NOTFOUND THEN
611 /*** no active mobile responsibility found -> log error ***/
612 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
613 jtm_message_log_pkg.Log_Msg
614 ( p_resource_id
615 , g_table_name
616 , 'Resource_id = ' || p_resource_id || ' does not have any active mobile responsibilities'
617 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
618 );
619 END IF;
620 x_return_status := FND_API.G_RET_STS_ERROR;
621 ELSE
622 /*** Found active mobile responsibility for user ***/
623
624 /*Get inventory org and category profiles */
625 GET_PROFILES( p_user_id => r_mobile_resp.user_id
626 , p_responsibility_id => r_mobile_resp.responsibility_id
627 , p_application_id => r_mobile_resp.responsibility_application_id
628 , x_organization_id => l_profile_org_id
629 , x_category_set_id => l_profile_category_set_id
630 , x_category_id => l_profile_category_id );
631
632 /*Bug 3929942 - Removed the code to update existing SIs in the ACC table,
633 as this proc is called only during user creation, and during user creation
634 the ACC table will be empty for the mobile resource being created*/
635
636
637
638 --Bug 3724165 - Get only Material Items when applying Category Set Filter.
639
640 /*Block insert every item from given org not yet in acc table - Material*/
641
642 --Bug 3929942 - Added Hints and use bind variables
643 l_stmt := 'INSERT INTO JTM_MTL_SYSTEM_ITEMS_ACC (';
644 l_stmt := l_stmt || ' access_id, last_update_date, last_updated_by, ';
645 l_stmt := l_stmt || ' creation_date, created_by, counter, resource_id, ';
646 l_stmt := l_stmt || ' inventory_item_id, organization_id )';
647 l_stmt := l_stmt || ' SELECT /*+ index (msi MTL_SYSTEM_ITEMS_B_N4)*/ ';
648 l_stmt := l_stmt || ' jtm_acc_table_s.NEXTVAL, SYSDATE, 1, ';
649 l_stmt := l_stmt || ' SYSDATE, 1, 1,'||p_resource_id ||', ';
650 l_stmt := l_stmt || ' inventory_item_id, organization_id ';
651 l_stmt := l_stmt || ' FROM mtl_system_items_b msi, cs_billing_type_categories cbtc';
652 l_stmt := l_stmt || ' WHERE organization_id = :1 ';
653 l_stmt := l_stmt || ' AND msi.material_billable_flag = cbtc.billing_type (+) ';
654 l_stmt := l_stmt || ' AND NVL(cbtc.billing_category, ''M'') = ''M''';
655 l_stmt := l_stmt || ' AND ( INVENTORY_ITEM_ID, ORGANIZATION_ID ) ';
656 l_stmt := l_stmt || ' NOT IN (';
657 l_stmt := l_stmt || ' SELECT /*+ index (acc JTM_MTL_SYSTEM_ITEMS_ACC_U1)*/ INVENTORY_ITEM_ID, ORGANIZATION_ID';
658 l_stmt := l_stmt || ' FROM JTM_MTL_SYSTEM_ITEMS_ACC acc ';
659 l_stmt := l_stmt || ' WHERE RESOURCE_ID = :2 )';
660
661 IF (l_profile_category_id IS NOT NULL) THEN
662 l_stmt1 := 'itemcat.category_id = ' || l_profile_category_id;
663 END IF;
664
665 IF (l_profile_category_set_id IS NOT NULL) THEN
666 IF (l_stmt1 IS NOT NULL) THEN
667 l_stmt1 := l_stmt1 || 'AND itemcat.category_set_id = '
668 || l_profile_category_set_id;
669 ELSE
670 l_stmt1 := 'itemcat.category_set_id = ' || l_profile_category_set_id;
671 END IF;
672 END IF;
673
674 IF (l_stmt1 IS NOT NULL) THEN
675 l_stmt := l_stmt || ' AND ';
676 l_stmt := l_stmt || ' inventory_item_id IN';
677 l_stmt := l_stmt || ' (SELECT inventory_item_id';
678 l_stmt := l_stmt || ' FROM mtl_item_categories itemcat';
679 l_stmt := l_stmt || ' WHERE ' || l_stmt1;
680 l_stmt := l_stmt || ' AND itemcat.organization_id = :3 )';
681 END IF;
682
683 IF (l_stmt1 IS NOT NULL) THEN
684 EXECUTE IMMEDIATE l_stmt USING l_profile_org_id, p_resource_id, l_profile_org_id;
685 ELSE
686 EXECUTE IMMEDIATE l_stmt USING l_profile_org_id, p_resource_id;
687 END IF;
688
689
690 --Bug 3724165 - Get Expense and Labor Items without applying Category
691 -- Set Filter.
692
693 /*Block insert every item from given org not yet in acc table - Expense and Labor*/
694
695 --Bug 3929942 - Added Hints
696 INSERT INTO JTM_MTL_SYSTEM_ITEMS_ACC(access_id, last_update_date, last_updated_by,
697 creation_date, created_by, counter, resource_id,inventory_item_id, organization_id )
698 SELECT /*+ index (msi MTL_SYSTEM_ITEMS_B_N4)*/ jtm_acc_table_s.NEXTVAL, SYSDATE, 1,
699 SYSDATE, 1, 1, p_resource_id, inventory_item_id, organization_id
700 FROM mtl_system_items_b msi, cs_billing_type_categories cbtc
701 WHERE organization_id = l_profile_org_id
702 AND msi.material_billable_flag = cbtc.billing_type (+)
703 AND cbtc.billing_category IN ('E','L')
704 AND ( inventory_item_id, organization_id )
705 NOT IN (
706 SELECT /*+ index (acc JTM_MTL_SYSTEM_ITEMS_ACC_U1)*/ inventory_item_id,
707 organization_id
708 FROM jtm_mtl_system_items_acc acc
709 WHERE resource_id = p_resource_id );
710
711 /*REDUCE ROLLBACK SEGMENTS*/
712 COMMIT;
713 CSL_MTL_SEC_LOCATORS_ACC_PKG.POPULATE_SEC_LOCATORS_ACC;
714 COMMIT;
715
716 /*Delete any old record for resource from CSL_RESOURCE_INVENTORY_ORG*/
717 DELETE_RESOURCE_PROFILE_REC( p_resource_id => p_resource_id );
718 /*Insert resource org record*/
719 INSERT_RESOURCE_PROFILE_REC( p_resource_id => p_resource_id
720 , p_organization_id => l_profile_org_id
721 , p_category_set_id => l_profile_category_set_id
722 , p_category_id => l_profile_category_id );
723 COMMIT;
724 END IF;
725 CLOSE c_mobile_resp;
726
727 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
728 jtm_message_log_pkg.Log_Msg
729 ( p_resource_id
730 , g_table_name
731 , 'Leaving INSERT_ALL_ACC_RECORDS'
732 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
733 );
734 END IF;
735
736 x_return_status := FND_API.G_RET_STS_SUCCESS;
737 EXCEPTION
738 WHEN OTHERS THEN
739 ROLLBACK;
740 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
741 jtm_message_log_pkg.Log_Msg
742 ( p_resource_id
743 , g_table_name
744 , 'INSERT_ALL_ACC_RECORDS'||fnd_global.local_chr(10)||
745 'Error: '||sqlerrm
746 , JTM_HOOK_UTIL_PKG.g_debug_level_error);
747 END IF;
748 x_return_status := FND_API.G_RET_STS_ERROR;
749 RAISE;
750 END INSERT_ALL_ACC_RECORDS;
751
752 /*
753 Private procedure that inserts (new) system items for an org/category
754 and calls markdirty for all inserted records.
755 */
756 PROCEDURE INSERT_ACC_REC_MARKDIRTY( p_organization_id IN NUMBER
757 , p_category_set_id IN NUMBER
758 , p_category_id IN NUMBER
759 , p_resource_id IN NUMBER
760 , p_last_run_date IN DATE
761 , p_changed IN VARCHAR2
762 , p_old_org_id IN NUMBER )
763 IS
764
765 --Bug 3724165 - To take care of this bug, this procedure will only take
766 --care of Material Items. Expense and Labor items will be taken care of
767 --by the procedure INSERT_ACC_REC_MARKDIRTY_EXP
768 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
769 l_tab_resource_id ASG_DOWNLOAD.USER_LIST;
770
771 TYPE item_Tab IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
772 TYPE org_Tab IS TABLE OF mtl_system_items_b.organization_id%TYPE INDEX BY BINARY_INTEGER;
773
774 items item_Tab;
775 organizations org_Tab;
776
777 l_dummy BOOLEAN;
778
779 --Bug 3746689
780 TYPE billCat_Tab IS TABLE OF cs_billing_type_categories.billing_category%TYPE INDEX BY BINARY_INTEGER;
781 billCat billCat_Tab;
782
783 accessId_Exp_Lab_Tab ASG_DOWNLOAD.ACCESS_LIST;
784 accessId_Mat_Tab ASG_DOWNLOAD.ACCESS_LIST;
785
786 resourceId_Exp_Lab_Tab ASG_DOWNLOAD.USER_LIST;
787 resourceId_Mat_Tab ASG_DOWNLOAD.USER_LIST;
788
789 el_ctr NUMBER;
790 m_ctr NUMBER;
791
792 l_stmt VARCHAR2(4000);
793 l_stmt1 VARCHAR2(4000);
794
795 --Bug 3929942 - Static SQL converted to cursors so as to be able to use LIMIT clause.
796 -- Both category and cat set are null
797 CURSOR c_items (b_resource_id NUMBER, b_organization_id NUMBER,
798 b_changed VARCHAR2, b_last_run_date DATE)
799 IS
800 SELECT JTM_ACC_TABLE_S.NEXTVAL, INVENTORY_ITEM_ID, ORGANIZATION_ID, p_resource_id, billing_category
801 BULK COLLECT INTO l_tab_access_id, items, organizations, l_tab_resource_id, billCat
802 FROM MTL_SYSTEM_ITEMS_B msi, CS_BILLING_TYPE_CATEGORIES cbtc
803 WHERE ( INVENTORY_ITEM_ID, ORGANIZATION_ID ) NOT IN (
804 SELECT INVENTORY_ITEM_ID, ORGANIZATION_ID
805 FROM JTM_MTL_SYSTEM_ITEMS_ACC
806 WHERE RESOURCE_ID = b_resource_id )
807 AND ORGANIZATION_ID = b_organization_id
808 AND material_billable_flag = billing_type (+)
809 AND NVL(cbtc.billing_category, 'M') = 'M'
810 AND (b_changed = 'Y'
811 OR msi.CREATION_DATE >= NVL(b_last_run_date, msi.CREATION_DATE ));
812
813
814 -- Category is not null and Cat set is null
815 CURSOR c_items_Cat (b_resource_id NUMBER, b_organization_id NUMBER,
816 b_changed VARCHAR2, b_last_run_date DATE, b_category_id NUMBER)
817 IS
818 SELECT JTM_ACC_TABLE_S.NEXTVAL, INVENTORY_ITEM_ID, ORGANIZATION_ID, p_resource_id, billing_category
819 BULK COLLECT INTO l_tab_access_id, items, organizations, l_tab_resource_id, billCat
820 FROM MTL_SYSTEM_ITEMS_B msi, CS_BILLING_TYPE_CATEGORIES cbtc
821 WHERE ( INVENTORY_ITEM_ID, ORGANIZATION_ID ) NOT IN (
822 SELECT INVENTORY_ITEM_ID, ORGANIZATION_ID
823 FROM JTM_MTL_SYSTEM_ITEMS_ACC
824 WHERE RESOURCE_ID = b_resource_id )
825 AND ORGANIZATION_ID = b_organization_id
826 AND material_billable_flag = billing_type (+)
827 AND NVL(cbtc.billing_category, 'M') = 'M'
828 AND inventory_item_id IN
829 (SELECT inventory_item_id
830 FROM mtl_item_categories itemcat
831 WHERE itemcat.category_id = b_category_id
832 AND itemcat.organization_id = b_organization_id
833 AND (b_changed = 'Y'
834 OR itemcat.creation_date >= NVL(b_last_run_date, itemcat.CREATION_DATE)));
835
836
837 -- Category is null and Cat Set is not null
838 CURSOR c_items_Cat_Set (b_resource_id NUMBER, b_organization_id NUMBER,
839 b_changed VARCHAR2, b_last_run_date DATE, b_category_set_id NUMBER)
840 IS
841 SELECT JTM_ACC_TABLE_S.NEXTVAL, INVENTORY_ITEM_ID, ORGANIZATION_ID,
842 p_resource_id, material_billable_flag
843 FROM MTL_SYSTEM_ITEMS_B msi
844 WHERE NOT EXISTS (
845 SELECT 1
846 FROM JTM_MTL_SYSTEM_ITEMS_ACC acc
847 WHERE RESOURCE_ID = b_resource_id
848 AND msi.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID
849 AND msi.ORGANIZATION_ID = acc.ORGANIZATION_ID)
850 AND NVL(MATERIAL_BILLABLE_FLAG, 'M') = 'M'
851 AND ORGANIZATION_ID = b_organization_id
852 AND inventory_item_id IN
853 (SELECT inventory_item_id
854 FROM mtl_item_categories itemcat
855 WHERE itemcat.category_set_id = b_category_set_id
856 AND itemcat.organization_id = b_organization_id
857 AND (b_changed = 'Y'
858 OR itemcat.creation_date >= NVL(b_last_run_date, itemcat.CREATION_DATE)));
859
860
861 -- Both Category and Category set are null
862 CURSOR c_items_Cat_Set_Cat (b_resource_id NUMBER, b_organization_id NUMBER,
863 b_changed VARCHAR2, b_last_run_date DATE, b_category_id NUMBER, b_category_set_id NUMBER)
864 IS
865 SELECT JTM_ACC_TABLE_S.NEXTVAL, INVENTORY_ITEM_ID, ORGANIZATION_ID, p_resource_id, billing_category
866 BULK COLLECT INTO l_tab_access_id, items, organizations, l_tab_resource_id, billCat
867 FROM MTL_SYSTEM_ITEMS_B msi, CS_BILLING_TYPE_CATEGORIES cbtc
868 WHERE ( INVENTORY_ITEM_ID, ORGANIZATION_ID ) NOT IN (
869 SELECT INVENTORY_ITEM_ID, ORGANIZATION_ID
870 FROM JTM_MTL_SYSTEM_ITEMS_ACC
871 WHERE RESOURCE_ID = b_resource_id )
872 AND ORGANIZATION_ID = b_organization_id
873 AND material_billable_flag = billing_type (+)
874 AND NVL(cbtc.billing_category, 'M') = 'M'
875 AND inventory_item_id IN
876 (SELECT inventory_item_id
877 FROM mtl_item_categories itemcat
878 WHERE itemcat.category_id = b_category_id
879 AND itemcat.category_set_id = b_category_set_id
880 AND itemcat.organization_id = b_organization_id
881 AND (b_changed = 'Y'
882 OR itemcat.creation_date >= NVL(b_last_run_date, itemcat.CREATION_DATE)));
883
884
885 BEGIN
886
887 el_ctr := 1;
888 m_ctr := 1;
889
890 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
891 jtm_message_log_pkg.Log_Msg
892 ( p_organization_id
893 , g_table_name
894 , 'Entering INSERT_ACC_REC_MARKDIRTY'
895 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
896 );
897 END IF;
898
899 IF p_changed = 'Y' THEN
900 /*Raise counter for items from given org already in acc table ( e.g. system item of SR )*/
901 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
902 jtm_message_log_pkg.Log_Msg
903 ( p_organization_id
904 , g_table_name
905 , 'Updating '||g_acc_table_name
906 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
907 END IF;
908
909 --Bug 3724165
910 --Bug 3929942 - Use Bind variables
911 l_stmt := 'UPDATE jtm_mtl_system_items_acc';
912 l_stmt := l_stmt || ' SET counter = counter + 1';
913 l_stmt := l_stmt || ' , last_update_date = SYSDATE';
914 l_stmt := l_stmt || ' , last_updated_by = 1';
915 l_stmt := l_stmt || ' WHERE resource_id = :1 ';
916
917 IF ( (p_category_id IS NULL) AND (p_category_set_id IS NULL) ) THEN
918 -- Check material billable flag
919
920 l_stmt := l_stmt || ' AND (inventory_item_id, organization_id) IN ';
921 l_stmt := l_stmt || ' (SELECT inventory_item_id, organization_id ';
922 l_stmt := l_stmt || ' FROM mtl_system_items_b msi, cs_billing_type_categories cbtc';
923 l_stmt := l_stmt || ' WHERE organization_id = :2 ';
924 l_stmt := l_stmt || ' and msi.material_billable_flag = cbtc.billing_type (+) ';
925 l_stmt := l_stmt || ' AND NVL(cbtc.billing_category, ''M'') = ''M'')';
926
927 ELSE -- category would ensure material items
928 l_stmt := l_stmt || ' AND ORGANIZATION_ID = :2 ';
929 END IF;
930
931 IF (p_category_id IS NOT NULL) THEN
932 l_stmt1 := ' itemcat.category_id = ' || p_category_id;
933 END IF;
934
935 IF (p_category_set_id IS NOT NULL) THEN
936 IF (l_stmt1 IS NOT NULL) THEN
937 l_stmt1 := l_stmt1 || ' AND itemcat.category_set_id = '
938 || p_category_set_id;
939 ELSE
940 l_stmt1 := ' itemcat.category_set_id = ' || p_category_set_id;
941 END IF;
942 END IF;
943
944 IF (l_stmt1 IS NOT NULL) THEN
945 l_stmt := l_stmt || ' AND ';
946 l_stmt := l_stmt || ' inventory_item_id IN';
947 l_stmt := l_stmt || ' (SELECT inventory_item_id';
948 l_stmt := l_stmt || ' FROM mtl_item_categories itemcat';
949 l_stmt := l_stmt || ' WHERE ' || l_stmt1;
950 l_stmt := l_stmt || ' AND itemcat.organization_id = :3 )';
951 END IF;
952
953 IF (l_stmt1 IS NOT NULL) THEN
954 EXECUTE IMMEDIATE l_stmt USING p_resource_id, p_organization_id, p_organization_id;
955 ELSE
956 EXECUTE IMMEDIATE l_stmt USING p_resource_id, p_organization_id;
957 END IF;
958
959 END IF; -- End of Existing Items in SI ACC
960
961 --Bug 3746689
962 --Bug 3929942 - Split the Select into 4 parts. Convert to cursor and use LIMIT clause.
963 -- Both category and cat set are null
964 IF (p_category_id IS NULL AND p_category_set_id IS NULL) THEN
965 OPEN c_items(p_resource_id, p_organization_id, p_changed, p_last_run_date);
966 LOOP
967 l_tab_access_id.DELETE;
968 items.DELETE;
969 organizations.DELETE;
970 l_tab_resource_id.DELETE;
971
972 FETCH c_items BULK COLLECT INTO l_tab_access_id, items,
973 organizations, l_tab_resource_id, billCat LIMIT 1000;
974 EXIT WHEN l_tab_access_id.COUNT = 0;
975
976 IF l_tab_access_id.COUNT > 0 THEN
977
978 /*** 1 or more acc rows retrieved -> push to resource ***/
979 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
980 jtm_message_log_pkg.Log_Msg
981 ( p_organization_id
982 , g_table_name
983 , 'Pushing ' || l_tab_access_id.COUNT
984 || ' inserted record(s) to resource: '||p_resource_id
985 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
986 );
987 END IF;
988
989 FORALL i IN l_tab_access_id.FIRST..l_tab_access_id.LAST
990 INSERT INTO jtm_mtl_system_items_acc(
991 access_id, last_update_date, last_updated_by, creation_date,
992 created_by, counter, resource_id, inventory_item_id, organization_id )
993 VALUES (
994 l_tab_access_id(i), SYSDATE, 1, SYSDATE, 1, 1, p_resource_id,
995 items(i), organizations(i));
996
997 /*** push to oLite using asg_download ***/
998 IF l_tab_access_id.COUNT > 0 THEN -- For Material Items
999 l_dummy := asg_download.markdirty(
1000 P_PUB_ITEM => g_publication_item_name(1)
1001 , P_ACCESSLIST => l_tab_access_id
1002 , P_RESOURCELIST => l_tab_resource_id
1003 , P_DML_TYPE => 'I'
1004 , P_TIMESTAMP => SYSDATE
1005 );
1006 END IF;
1007
1008 END IF;--IF l_tab_access_id.COUNT > 0
1009
1010 END LOOP;
1011 CLOSE c_items;
1012
1013 -- Category is not null and Cat set is null
1014 ELSIF (p_category_id IS NOT NULL AND p_category_set_id IS NULL) THEN
1015 OPEN c_items_Cat(p_resource_id, p_organization_id, p_changed, p_last_run_date,
1016 p_category_id);
1017 LOOP
1018 l_tab_access_id.DELETE;
1019 items.DELETE;
1020 organizations.DELETE;
1021 l_tab_resource_id.DELETE;
1022
1023 FETCH c_items_Cat BULK COLLECT INTO l_tab_access_id, items,
1024 organizations, l_tab_resource_id, billCat LIMIT 1000;
1025 EXIT WHEN l_tab_access_id.COUNT = 0;
1026
1027 IF l_tab_access_id.COUNT > 0 THEN
1028
1029 /*** 1 or more acc rows retrieved -> push to resource ***/
1030 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1031 jtm_message_log_pkg.Log_Msg
1032 ( p_organization_id
1033 , g_table_name
1034 , 'Pushing ' || l_tab_access_id.COUNT
1035 || ' inserted record(s) to resource: '||p_resource_id
1036 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
1037 );
1038 END IF;
1039
1040 FORALL i IN l_tab_access_id.FIRST..l_tab_access_id.LAST
1041 INSERT INTO jtm_mtl_system_items_acc(
1042 access_id, last_update_date, last_updated_by, creation_date,
1043 created_by, counter, resource_id, inventory_item_id, organization_id )
1044 VALUES (
1045 l_tab_access_id(i), SYSDATE, 1, SYSDATE, 1, 1, p_resource_id,
1046 items(i), organizations(i));
1047
1048 /*** push to oLite using asg_download ***/
1049 IF l_tab_access_id.COUNT > 0 THEN -- For Material Items
1050 l_dummy := asg_download.markdirty(
1051 P_PUB_ITEM => g_publication_item_name(1)
1052 , P_ACCESSLIST => l_tab_access_id
1053 , P_RESOURCELIST => l_tab_resource_id
1054 , P_DML_TYPE => 'I'
1055 , P_TIMESTAMP => SYSDATE
1056 );
1057 END IF;
1058
1059 END IF;--IF l_tab_access_id.COUNT > 0
1060
1061 END LOOP;
1062 CLOSE c_items_Cat;
1063
1064 -- Category is null and Cat Set is not null
1065 ELSIF (p_category_id IS NULL AND p_category_set_id IS NOT NULL) THEN
1066 OPEN c_items_Cat_Set(p_resource_id, p_organization_id, p_changed, p_last_run_date,
1067 p_category_set_id);
1068 LOOP
1069 l_tab_access_id.DELETE;
1070 items.DELETE;
1071 organizations.DELETE;
1072 l_tab_resource_id.DELETE;
1073
1074 FETCH c_items_Cat_Set BULK COLLECT INTO l_tab_access_id, items,
1075 organizations, l_tab_resource_id, billCat LIMIT 1000;
1076 EXIT WHEN l_tab_access_id.COUNT = 0;
1077
1078 IF l_tab_access_id.COUNT > 0 THEN
1079
1080 /*** 1 or more acc rows retrieved -> push to resource ***/
1081 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1082 jtm_message_log_pkg.Log_Msg
1083 ( p_organization_id
1084 , g_table_name
1085 , 'Pushing ' || l_tab_access_id.COUNT
1086 || ' inserted record(s) to resource: '||p_resource_id
1087 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
1088 );
1089 END IF;
1090
1091 FORALL i IN l_tab_access_id.FIRST..l_tab_access_id.LAST
1092 INSERT INTO jtm_mtl_system_items_acc(
1093 access_id, last_update_date, last_updated_by, creation_date,
1094 created_by, counter, resource_id, inventory_item_id, organization_id )
1095 VALUES (
1096 l_tab_access_id(i), SYSDATE, 1, SYSDATE, 1, 1, p_resource_id,
1097 items(i), organizations(i));
1098
1099 /*** push to oLite using asg_download ***/
1100 IF l_tab_access_id.COUNT > 0 THEN -- For Material Items
1101 l_dummy := asg_download.markdirty(
1102 P_PUB_ITEM => g_publication_item_name(1)
1103 , P_ACCESSLIST => l_tab_access_id
1104 , P_RESOURCELIST => l_tab_resource_id
1105 , P_DML_TYPE => 'I'
1106 , P_TIMESTAMP => SYSDATE
1107 );
1108 END IF;
1109
1110 END IF;--IF l_tab_access_id.COUNT > 0
1111
1112 END LOOP;
1113 CLOSE c_items_Cat_Set;
1114
1115 -- Both Category and Category set are null
1116 ELSIF (p_category_id IS NOT NULL AND p_category_set_id IS NOT NULL) THEN
1117 OPEN c_items_Cat_Set_Cat(p_resource_id, p_organization_id, p_changed, p_last_run_date,
1118 p_category_id, p_category_set_id);
1119 LOOP
1120 l_tab_access_id.DELETE;
1121 items.DELETE;
1122 organizations.DELETE;
1123 l_tab_resource_id.DELETE;
1124
1125 FETCH c_items_Cat_Set_Cat BULK COLLECT INTO l_tab_access_id, items,
1126 organizations, l_tab_resource_id, billCat LIMIT 1000;
1127 EXIT WHEN l_tab_access_id.COUNT = 0;
1128
1129 IF l_tab_access_id.COUNT > 0 THEN
1130
1131 /*** 1 or more acc rows retrieved -> push to resource ***/
1132 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1133 jtm_message_log_pkg.Log_Msg
1134 ( p_organization_id
1135 , g_table_name
1136 , 'Pushing ' || l_tab_access_id.COUNT
1137 || ' inserted record(s) to resource: '||p_resource_id
1138 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
1139 );
1140 END IF;
1141
1142 FORALL i IN l_tab_access_id.FIRST..l_tab_access_id.LAST
1143 INSERT INTO jtm_mtl_system_items_acc(
1144 access_id, last_update_date, last_updated_by, creation_date,
1145 created_by, counter, resource_id, inventory_item_id, organization_id )
1146 VALUES (
1147 l_tab_access_id(i), SYSDATE, 1, SYSDATE, 1, 1, p_resource_id,
1148 items(i), organizations(i));
1149
1150 /*** push to oLite using asg_download ***/
1151 IF l_tab_access_id.COUNT > 0 THEN -- For Material Items
1152 l_dummy := asg_download.markdirty(
1153 P_PUB_ITEM => g_publication_item_name(1)
1154 , P_ACCESSLIST => l_tab_access_id
1155 , P_RESOURCELIST => l_tab_resource_id
1156 , P_DML_TYPE => 'I'
1157 , P_TIMESTAMP => SYSDATE
1158 );
1159 END IF;
1160
1161 END IF;--IF l_tab_access_id.COUNT > 0
1162
1163 END LOOP;
1164 CLOSE c_items_Cat_Set_Cat;
1165
1166 END IF;
1167
1168
1169 INSERT_ACC_REC_MARKDIRTY_EXP(p_organization_id, p_resource_id, p_old_org_id);
1170
1171
1172 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1173 jtm_message_log_pkg.Log_Msg
1174 ( p_organization_id
1175 , g_table_name
1176 , 'Leaving INSERT_ACC_REC_MARKDIRTY'
1177 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
1178 );
1179 END IF;
1180 EXCEPTION
1181 WHEN OTHERS THEN
1182 jtm_message_log_pkg.Log_Msg
1183 ( p_organization_id
1184 , g_table_name
1185 , 'INSERT_ACC_REC_MARKDIRTY'||fnd_global.local_chr(10)||
1186 'Error: '||sqlerrm
1187 , JTM_HOOK_UTIL_PKG.g_debug_level_error);
1188 RAISE;
1189 END INSERT_ACC_REC_MARKDIRTY;
1190
1191 /*
1192 Private procedure that re-pushes replicated system items
1193 that were updated since the last time the concurrent program ran.
1194 */
1195 PROCEDURE UPDATE_ACC_REC_MARKDIRTY( p_last_run_date IN DATE )
1196 IS
1197 --Bug 3929942 - Modified the query to remove UNION
1198 CURSOR c_changed( b_last_date DATE ) IS
1199 SELECT /*+ INDEX (acc JTM_MTL_SYSTEM_ITEMS_ACC_U1) index (msi MTL_SYSTEM_ITEMS_B_U1) */
1200 acc.ACCESS_ID, acc.RESOURCE_ID, cbtc.BILLING_CATEGORY
1201 FROM JTM_MTL_SYSTEM_ITEMS_ACC acc, MTL_SYSTEM_ITEMS_B msi
1202 , CS_BILLING_TYPE_CATEGORIES cbtc
1203 WHERE msi.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID
1204 AND msi.ORGANIZATION_ID = acc.ORGANIZATION_ID
1205 AND msi.material_billable_flag = cbtc.billing_type (+)
1206 AND msi.LAST_UPDATE_DATE >= b_last_date;
1207
1208 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
1209 l_tab_resource_id ASG_DOWNLOAD.USER_LIST;
1210 l_dummy BOOLEAN;
1211
1212 --Bug 3746689
1213 TYPE billCat_Tab IS TABLE OF cs_billing_type_categories.billing_category%TYPE INDEX BY BINARY_INTEGER;
1214 billCat billCat_Tab;
1215 l_billCat cs_billing_type_categories.billing_category%TYPE;
1216
1217 TYPE sourceType_Tab IS TABLE OF VARCHAR(1) INDEX BY BINARY_INTEGER;
1218 sourceType sourceType_Tab;
1219
1220 accessId_Exp_Lab_Tab ASG_DOWNLOAD.ACCESS_LIST;
1221 accessId_Mat_Tab ASG_DOWNLOAD.ACCESS_LIST;
1222
1223 resourceId_Exp_Lab_Tab ASG_DOWNLOAD.USER_LIST;
1224 resourceId_Mat_Tab ASG_DOWNLOAD.USER_LIST;
1225
1226 newPI BOOLEAN;
1227 el_ctr NUMBER;
1228 m_ctr NUMBER;
1229
1230 --Bug 3929942
1231 l_max_last_update_date_b DATE;
1232 l_max_last_update_date_tl DATE;
1233
1234 BEGIN
1235 --Bug 3746689
1236 el_ctr := 1;
1237 m_ctr := 1;
1238
1239 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1240 jtm_message_log_pkg.Log_Msg
1241 ( 0
1242 , g_table_name
1243 , 'Entering UPDATE_ACC_REC_MARKDIRTY'
1244 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
1245 );
1246 END IF;
1247
1248 --Bug 3929942
1249 /* This portion of code assumes indexes on last_update_date on MTL_SYSTEM_ITEMS_B */
1250 /* , MTL_SYSTEM_ITEMS_TL which were custom created */
1251 SELECT MAX(LAST_UPDATE_DATE) into l_max_last_update_date_b
1252 FROM MTL_SYSTEM_ITEMS_B;
1253 IF( l_max_last_update_date_b < p_last_run_date) THEN
1254 SELECT MAX(LAST_UPDATE_DATE) into l_max_last_update_date_tl
1255 FROM MTL_SYSTEM_ITEMS_TL;
1256 IF(l_max_last_update_date_tl < p_last_run_date) THEN
1257 -- No updates
1258 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1259 jtm_message_log_pkg.Log_Msg
1260 ( 0
1261 , g_table_name
1262 , 'Leaving UPDATE_ACC_REC_MARKDIRTY'
1263 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
1264 );
1265 END IF;
1266 RETURN;
1267 END IF;
1268 END IF;
1269
1270 /*Fetch all changed system items that are in the acc table*/
1271 OPEN c_changed( p_last_run_date );
1272 --Bug 3746689
1273 --Bug 3929942
1274 LOOP
1275 /* Set the table to empty before each fetch */
1276 l_tab_access_id.DELETE;
1277 l_tab_resource_id.DELETE;
1278 billCat.DELETE;
1279 accessId_Exp_Lab_Tab.DELETE;
1280 accessId_Mat_Tab.DELETE;
1281 resourceId_Exp_Lab_Tab.DELETE;
1282 resourceId_Mat_Tab.DELETE;
1283
1284 el_ctr := 1;
1285 m_ctr := 1;
1286
1287
1288 FETCH c_changed BULK COLLECT INTO
1289 l_tab_access_id, l_tab_resource_id, billCat limit 1000;
1290 EXIT when l_tab_access_id.COUNT = 0;
1291
1292 /*Call oracle lite*/
1293 IF l_tab_access_id.COUNT > 0 THEN
1294 /*** 1 or more acc rows retrieved -> push to resource ***/
1295 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1296 jtm_message_log_pkg.Log_Msg
1297 ( 0
1298 , g_table_name
1299 , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
1300 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
1301 );
1302 END IF;
1303
1304 --Bug 3746689
1305 FOR i IN l_tab_access_id.FIRST..l_tab_access_id.LAST
1306 LOOP
1307 IF (billCat(i) = 'E' OR billCat(i) = 'L') THEN
1308 newPI := TRUE;
1309 ELSE
1310 newPI := FALSE;
1311 END IF;
1312
1313 IF newPI THEN
1314 accessId_Exp_Lab_Tab(el_ctr) := l_tab_access_id(i);
1315 resourceId_Exp_Lab_Tab(el_ctr) := l_tab_resource_id(i);
1316 el_ctr := el_ctr + 1;
1317 ELSE
1318 accessId_Mat_Tab(m_ctr) := l_tab_access_id(i);
1319 resourceId_Mat_Tab(m_ctr) := l_tab_resource_id(i);
1320 m_ctr := m_ctr + 1;
1321 END IF;
1322 END LOOP;
1323
1324 /*** push to oLite using asg_download ***/
1325 -- send the segregated data to their resp PIs.
1326 --Bug 3746689
1327 IF accessId_Exp_Lab_Tab.COUNT > 0 THEN
1328 l_dummy := asg_download.markdirty(
1329 P_PUB_ITEM => g_explab_publication_item_name(1) --New PI for Expense and Labor items
1330 , P_ACCESSLIST => accessId_Exp_Lab_Tab
1331 , P_RESOURCELIST => resourceId_Exp_Lab_Tab
1332 , P_DML_TYPE => 'U'
1333 , P_TIMESTAMP => SYSDATE
1334 );
1335 END IF;
1336
1337 IF accessId_Mat_Tab.COUNT > 0 THEN
1338 l_dummy := asg_download.markdirty(
1339 P_PUB_ITEM => g_publication_item_name(1) --PI for Material Items
1340 , P_ACCESSLIST => accessId_Mat_Tab
1341 , P_RESOURCELIST => resourceId_Mat_Tab
1342 , P_DML_TYPE => 'U'
1343 , P_TIMESTAMP => SYSDATE
1344 );
1345 END IF;
1346
1347 END IF; -- end of tab_access_id count
1348 END LOOP;
1349 CLOSE c_changed;
1350
1351 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1352 jtm_message_log_pkg.Log_Msg
1353 ( 0
1354 , g_table_name
1355 , 'Leaving UPDATE_ACC_REC_MARKDIRTY'
1356 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
1357 );
1358 END IF;
1359 EXCEPTION
1360 WHEN OTHERS THEN
1361 IF c_changed%ISOPEN THEN
1362 CLOSE c_changed;
1363 END IF;
1364
1365 jtm_message_log_pkg.Log_Msg
1366 ( 0
1367 , g_table_name
1368 , 'UPDATE_ACC_REC_MARKDIRTY'||fnd_global.local_chr(10)||
1369 'Error: '||sqlerrm
1370 , JTM_HOOK_UTIL_PKG.g_debug_level_error);
1371 RAISE;
1372 END UPDATE_ACC_REC_MARKDIRTY;
1373
1374 /*
1375 Private procedure that
1376 1) deletes system items for an old org/category from the client
1377 and calls markdirty for all deleted records.
1378 2) deletes system items from the client that are no longer present in a category
1379
1380 If parameter p_changed = 'Y', then scenario (1) is performed.
1381 If parameter p_changed = 'N', then scenario (2) is performed.
1382 */
1383 PROCEDURE DELETE_ALL_ACC_REC_MARKDIRTY( p_resource_id IN NUMBER
1384 , p_organization_id IN NUMBER
1385 , p_category_set_id IN NUMBER
1386 , p_category_id IN NUMBER
1387 , p_profile_org_id IN NUMBER
1388 )
1389 IS
1390 BEGIN
1391 --Obsoleted // Bug 12659742
1392 RETURN;
1393 END DELETE_ALL_ACC_REC_MARKDIRTY;
1394
1395 /*Private procedure that processes system item changes for a given resource */
1396 PROCEDURE CONCURRENT_PROCESS_USER( p_resource_id IN NUMBER
1397 , p_user_id IN NUMBER
1398 , p_responsibility_id IN NUMBER
1399 , p_application_id IN NUMBER
1400 , p_last_run_date IN DATE )
1401 IS
1402 l_status VARCHAR2(1);
1403 l_profile_org_id NUMBER;
1404 l_profile_category_set_id NUMBER;
1405 l_profile_category_id NUMBER;
1406 l_pre_cat_filter BOOLEAN; -- TRUE when category filter was active previously
1407 l_post_cat_filter BOOLEAN; -- TRUE when category filter is active now
1408 l_cat_filter_changed BOOLEAN; -- TRUE when category filter changed
1409
1410 CURSOR c_org ( b_resource_id NUMBER ) IS
1411 SELECT organization_id, category_set_id, category_id
1412 FROM csl_resource_inventory_org
1413 WHERE resource_id = b_resource_id;
1414 r_org c_org%ROWTYPE;
1415 BEGIN
1416
1417 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1418 jtm_message_log_pkg.Log_Msg
1419 ( 0
1420 , g_table_name
1421 , 'Entering CONCURRENT_PROCESS_USER'
1422 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
1423 );
1424 END IF;
1425
1426 /*Get inventory org and category profiles */
1427 GET_PROFILES( p_user_id => p_user_id
1428 , p_responsibility_id => p_responsibility_id
1429 , p_application_id => p_application_id
1430 , x_organization_id => l_profile_org_id
1431 , x_category_set_id => l_profile_category_set_id
1432 , x_category_id => l_profile_category_id );
1433
1434 /*Get previous org and category profile setting*/
1435 OPEN c_org( p_resource_id );
1436 FETCH c_org INTO r_org;
1437
1438 IF c_org%NOTFOUND THEN
1439 /*
1440 Record containing previous org and category not found ->
1441 insert all items without calling markdirty
1442 Note that this normally should never happen since resource org
1443 record should have been inserted during user creation (even
1444 when the profile doesn't have a value yet)
1445 */
1446 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1447 jtm_message_log_pkg.Log_Msg
1448 ( 0
1449 , g_table_name
1450 , 'Resource profile record not found in csl_resource_inventory_org.' || fnd_global.local_chr(10)||
1451 'Inserting all system item records without calling markdirty.'
1452 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
1453 );
1454 END IF;
1455
1456 INSERT_ALL_ACC_RECORDS( p_resource_id => p_resource_id
1457 , x_return_status => l_status );
1458
1459 ELSE -- c_org%FOUND
1460
1461 l_pre_cat_filter := FALSE;
1462 l_post_cat_filter := FALSE;
1463
1464 --AND cond changed to OR as part of fix for Bug 3724165
1465 IF (( r_org.category_set_id IS NOT NULL ) OR
1466 ( r_org.category_id IS NOT NULL)) THEN
1467 l_pre_cat_filter := TRUE;
1468 END IF;
1469
1470 --AND cond changed to OR as part of fix for Bug 3724165
1471 IF (( l_profile_category_set_id IS NOT NULL ) OR
1472 ( l_profile_category_id IS NOT NULL)) THEN
1473 l_post_cat_filter := TRUE;
1474 END IF;
1475
1476
1477 /*** did category filter change from active -> inactive or vice versa ***/
1478 l_cat_filter_changed := FALSE;
1479 IF l_pre_cat_filter <> l_post_cat_filter THEN
1480 /*** yes -> set boolean ***/
1481 l_cat_filter_changed := TRUE;
1482 ELSE
1483 /*** no -> is filter active ***/
1484 IF l_post_cat_filter THEN
1485 /*** yes -> did category or category set change? ***/
1486 IF NVL(r_org.category_set_id, 0) <> NVL(l_profile_category_set_id, 0)
1487 OR NVL(r_org.category_id, 0) <> NVL(l_profile_category_id, 0) THEN
1488 l_cat_filter_changed := TRUE;
1489 END IF;
1490 END IF;
1491 END IF;
1492
1493 /*** did system item org or category filter change? ***/
1494 IF NVL( l_profile_org_id, FND_API.G_MISS_NUM ) <>
1495 NVL( r_org.organization_id, FND_API.G_MISS_NUM )
1496 OR l_cat_filter_changed THEN
1497 /*
1498 organization or category profile changed ->
1499 delete all old system items and insert new items with markdirty
1500 */
1501 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1502 jtm_message_log_pkg.Log_Msg
1503 ( 0
1504 , g_table_name
1505 , 'Inventory organization or category profiles changed.'
1506 || fnd_global.local_chr(10)||
1507 'original organization_id = ' || r_org.organization_id
1508 || ', new organization_id = ' || l_profile_org_id
1509 || fnd_global.local_chr(10)||
1510 'original category_set_id = ' || r_org.category_set_id
1511 || ', new category_set_id = ' || l_profile_category_set_id
1512 || fnd_global.local_chr(10)||
1513 'original category_id = ' || r_org.category_id
1514 || ', new category_id = ' || l_profile_category_id
1515 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
1516 );
1517 END IF;
1518
1519 --Bug 3841633
1520 IF (r_org.organization_id IS NOT NULL) THEN
1521 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1522 jtm_message_log_pkg.Log_Msg
1523 ( p_resource_id
1524 , g_table_name
1525 , 'Deleting records for old profile settings'
1526 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1527 END IF;
1528
1529 DELETE_ALL_ACC_REC_MARKDIRTY( p_resource_id => p_resource_id
1530 , p_organization_id => r_org.organization_id
1531 , p_category_set_id => r_org.category_set_id
1532 , p_category_id => r_org.category_id
1533 , p_profile_org_id => l_profile_org_id
1534 );
1535 END IF;
1536
1537 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1538 jtm_message_log_pkg.Log_Msg
1539 ( p_resource_id
1540 , g_table_name
1541 , 'Inserting records for new profile settings'
1542 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1543 END IF;
1544
1545 INSERT_ACC_REC_MARKDIRTY( p_organization_id => l_profile_org_id
1546 , p_category_set_id => l_profile_category_set_id
1547 , p_category_id => l_profile_category_id
1548 , p_resource_id => p_resource_id
1549 , p_last_run_date => NULL
1550 , p_changed => 'Y'
1551 , p_old_org_id => r_org.organization_id );
1552
1553 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1554 jtm_message_log_pkg.Log_Msg
1555 ( p_resource_id
1556 , g_table_name
1557 , 'Updating resource profile table with new profile settings'
1558 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1559 END IF;
1560
1561 UPDATE_RESOURCE_PROFILE_REC( p_resource_id => p_resource_id
1562 , p_organization_id => l_profile_org_id
1563 , p_category_set_id => l_profile_category_set_id
1564 , p_category_id => l_profile_category_id );
1565 ELSE
1566 /*
1567 organization and category profiles remained the same
1568 -> push any inserted items to resource (updates are pushed
1569 in main concurrent procedure in non-resource-specific call)
1570 */
1571 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1572 jtm_message_log_pkg.Log_Msg
1573 ( p_resource_id
1574 , g_table_name
1575 , 'Pushing inserted records for'||fnd_global.local_chr(10)||
1576 'organization_id = ' || l_profile_org_id||fnd_global.local_chr(10)||
1577 'category_set_id = ' || l_profile_category_set_id||fnd_global.local_chr(10)||
1578 'category_id = ' || l_profile_category_id
1579 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1580 END IF;
1581
1582 INSERT_ACC_REC_MARKDIRTY( p_organization_id => l_profile_org_id
1583 , p_category_set_id => l_profile_category_set_id
1584 , p_category_id => l_profile_category_id
1585 , p_resource_id => p_resource_id
1586 , p_last_run_date => p_last_run_date
1587 , p_changed => 'N'
1588 , p_old_org_id => r_org.organization_id );
1589 END IF;
1590
1591 END IF; -- c_org%NOTFOUND
1592 CLOSE c_org;
1593
1594 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1595 jtm_message_log_pkg.Log_Msg
1596 ( 0
1597 , g_table_name
1598 , 'Leaving CONCURRENT_PROCESS_USER'
1599 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
1600 );
1601 END IF;
1602
1603 EXCEPTION
1604 WHEN OTHERS THEN
1605 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
1606 jtm_message_log_pkg.Log_Msg
1607 ( 0
1608 , g_table_name
1609 , 'Caught exception in CONCURRENT_PROCESS_USER'||fnd_global.local_chr(10)||
1610 'Error: '||sqlerrm
1611 , JTM_HOOK_UTIL_PKG.g_debug_level_error);
1612 END IF;
1613 ROLLBACK;
1614 RAISE;
1615 END CONCURRENT_PROCESS_USER;
1616
1617 /*** Public procedure that gets called when the concurrent Program run ***/
1618 PROCEDURE CON_REQUEST_MTL_SYSTEM_ITEMS
1619 IS
1620 PRAGMA AUTONOMOUS_TRANSACTION;
1621
1622 /*** get the last run date of the concurent program ***/
1623 CURSOR c_LastRundate
1624 IS
1625 select LAST_RUN_DATE
1626 from JTM_CON_REQUEST_DATA
1627 where package_name = 'CSL_MTL_SYSTEM_ITEMS_ACC_PKG'
1628 AND procedure_name = 'CON_REQUEST_MTL_SYSTEM_ITEMS';
1629 r_LastRundate c_LastRundate%ROWTYPE;
1630
1631 /*** cursor retrieving list of resources subscribed to publication item ***/
1632 CURSOR c_mobile_resp
1633 IS
1634 SELECT res.resource_id
1635 , usr.user_id
1636 , usrresp.responsibility_id
1637 , usrresp.responsibility_application_id
1638 FROM asg_pub pub
1639 , asg_pub_responsibility pubresp
1640 , fnd_user_resp_groups usrresp
1641 , fnd_user usr
1642 , jtf_rs_resource_extns res
1643 , asg_user au
1644 WHERE res.resource_id = au.resource_id --b_resource_id
1645 AND pub.name = 'SERVICEL'
1646 AND pub.enabled='Y'
1647 AND pub.status='Y'
1648 AND pub.pub_id = pubresp.pub_id
1649 AND pubresp.responsibility_id = usrresp.responsibility_id
1650 AND TRUNC(sysdate) BETWEEN TRUNC(NVL(usrresp.start_date,sysdate))
1651 AND TRUNC(NVL(usrresp.end_date,sysdate))
1652 AND usrresp.user_id = usr.user_id
1653 AND TRUNC(sysdate) BETWEEN TRUNC(NVL(usr.start_date,sysdate))
1654 AND TRUNC(NVL(usr.end_date,sysdate))
1655 AND usr.user_id = res.user_id
1656 AND TRUNC(sysdate) BETWEEN TRUNC(NVL(res.start_date_active,sysdate))
1657 AND TRUNC(NVL(res.end_date_active,sysdate));
1658
1659 l_current_run_date DATE;
1660 BEGIN
1661 /*** get debug level ***/
1662 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
1663 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1664 jtm_message_log_pkg.Log_Msg
1665 ( 0
1666 , g_table_name
1667 , 'Entering CON_REQUEST_MTL_SYSTEM_ITEMS'
1668 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
1669 );
1670 END IF;
1671
1672 /*** First retrieve last run date of the conccurent program ***/
1673 OPEN c_LastRundate;
1674 FETCH c_LastRundate INTO r_LastRundate;
1675 CLOSE c_LastRundate;
1676
1677 l_current_run_date := SYSDATE;
1678
1679 /*** Push updated system item records to resources ***/
1680 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1681 jtm_message_log_pkg.Log_Msg
1682 ( 0
1683 , g_table_name
1684 , 'Pushing updated records'
1685 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
1686 );
1687 END IF;
1688 UPDATE_ACC_REC_MARKDIRTY( p_last_run_date => r_LastRundate.last_run_date );
1689 COMMIT;
1690
1691 /*** Get the mobile laptop resources and loop over all of them ***/
1692 FOR r_mobile_resp IN c_mobile_resp LOOP
1693
1694 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1695 jtm_message_log_pkg.Log_Msg
1696 ( 0
1697 , g_table_name
1698 , 'Processing resource_id = ' || r_mobile_resp.resource_id
1699 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
1700 );
1701 END IF;
1702
1703 CONCURRENT_PROCESS_USER( p_resource_id => r_mobile_resp.resource_id
1704 , p_user_id => r_mobile_resp.user_id
1705 , p_responsibility_id => r_mobile_resp.responsibility_id
1706 , p_application_id => r_mobile_resp.responsibility_application_id
1707 , p_last_run_date => r_LastRundate.last_run_date );
1708 -- YLIAO comment out, as this might cause duplicate if the conc program stop
1709 -- where some users processed while others not with LAST_RUN_DATE unchanged.
1710 -- And next time conc program re-start, the processed users will be
1711 -- re-processed again causing the same records with counter++.
1712 -- COMMIT;
1713
1714 END LOOP;
1715
1716 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1717 jtm_message_log_pkg.Log_Msg
1718 ( 0
1719 , g_table_name
1720 , 'Updating LAST_RUN_DATE from '||r_LastRundate.LAST_RUN_DATE||' to '||l_current_run_date
1721 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
1722 );
1723 END IF;
1724
1725 /*Update the last run date*/
1726 UPDATE JTM_CON_REQUEST_DATA
1727 SET LAST_RUN_DATE = l_current_run_date
1728 WHERE package_name = 'CSL_MTL_SYSTEM_ITEMS_ACC_PKG'
1729 AND procedure_name = 'CON_REQUEST_MTL_SYSTEM_ITEMS';
1730
1731 COMMIT;
1732
1733 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1734 jtm_message_log_pkg.Log_Msg
1735 ( 0
1736 , g_table_name
1737 , 'Leaving CON_REQUEST_MTL_SYSTEM_ITEMS'
1738 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
1739 );
1740 END IF;
1741
1742 EXCEPTION
1743 WHEN OTHERS THEN
1744 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
1745 jtm_message_log_pkg.Log_Msg
1746 ( 0
1747 , g_table_name
1748 , 'CON_REQUEST_MTL_SYSTEM_ITEMS'||fnd_global.local_chr(10)||
1749 'Error: '||sqlerrm
1750 , JTM_HOOK_UTIL_PKG.g_debug_level_error);
1751 END IF;
1752 ROLLBACK;
1753 END CON_REQUEST_MTL_SYSTEM_ITEMS;
1754
1755 END CSL_MTL_SYSTEM_ITEMS_ACC_PKG;