[Home] [Help]
PACKAGE BODY: APPS.CSI_INV_DISCREPANCY_PKG
Source
1 PACKAGE BODY CSI_INV_DISCREPANCY_PKG AS
2 /* $Header: csiinvdb.pls 120.2 2011/11/16 06:17:18 sjawaji ship $ */
3
4 -- ------------------------------------------------------------
5 -- Define global variables
6 -- ------------------------------------------------------------
7 g_no_lot constant number := 1;
8 g_lot constant number := 2;
9 --
10 -- Procedure to debug the discrepancies
11 --
12 Procedure Debug(
13 p_message IN VARCHAR2)
14 Is
15 Begin
16 fnd_file.put_line(fnd_file.log, p_message);
17 End Debug;
18 --
19 PROCEDURE get_schema_name(
20 p_product_short_name IN varchar2,
21 x_schema_name OUT nocopy varchar2,
22 x_return_status OUT nocopy varchar2)
23 IS
24 l_status varchar2(1);
25 l_industry varchar2(1);
26 l_oracle_schema varchar2(30);
27 l_return boolean;
28 BEGIN
29
30 x_return_status := fnd_api.g_ret_sts_success;
31
32 l_return := fnd_installation.get_app_info(
33 application_short_name => p_product_short_name,
34 status => l_status,
35 industry => l_industry,
36 oracle_schema => l_oracle_schema);
37
38 IF NOT l_return THEN
39 fnd_message.set_name('CSI', 'CSI_FND_INVALID_SCHEMA_ERROR');
40 fnd_msg_pub.add;
41 RAISE fnd_api.g_exc_error;
42 END IF;
43
44 x_schema_name := l_oracle_schema;
45
46 EXCEPTION
47 WHEN fnd_api.g_exc_error THEN
48 x_return_status := fnd_api.g_ret_sts_error;
49 END get_schema_name;
50 --
51 -- Truncate the discrepancy table before each run
52 --
53 Procedure Truncate_table(
54 p_table_name IN VARCHAR2)
55 Is
56 l_num_of_rows NUMBER;
57 l_truncate_handle PLS_INTEGER := dbms_sql.open_cursor;
58 l_statement VARCHAR2(200);
59 Begin
60 l_statement := 'truncate table '||p_table_name;
61 dbms_sql.parse(l_truncate_handle, l_statement, dbms_sql.native);
62 l_num_of_rows := dbms_sql.execute(l_truncate_handle);
63 dbms_sql.close_cursor(l_truncate_handle);
64 Exception
65 When Others Then
66 Null;
67 End truncate_table;
68 --
69 --
70 -- Procedure that gets all the messages that are stuck in the
71 -- SFM queue
72 --
73 Procedure decode_queue
74 Is
75
76 Cursor msg_cur IS
77 Select msg_id,
78 msg_code,
79 msg_status,
80 body_text,
81 creation_date,
82 description
83 From xnp_msgs
84 Where (msg_code Like 'CSI%' OR msg_code Like 'CSE%')
85 And nvl(msg_status, 'READY') <> 'PROCESSED'
86 And recipient_name Is Null;
87
88 l_amount INTEGER;
89 l_msg_text VARCHAR2(32767);
90 l_source_id VARCHAR2(200);
91 l_source_type VARCHAR2(30);
92
93 l_schema_name varchar2(30);
94 l_object_name varchar2(80);
95 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
96
97 Begin
98
99 get_schema_name(
100 p_product_short_name => 'CSI',
101 x_schema_name => l_schema_name,
102 x_return_status => l_return_status);
103
104 IF l_return_status <> fnd_api.g_ret_sts_success THEN
105 RAISE fnd_api.g_exc_error;
106 END IF;
107
108 l_object_name := l_schema_name||'.csi_xnp_msgs_temp';
109
110 -- truncate the temporary table before each run
111 truncate_table(l_object_name);
112
113 For msg_rec in msg_cur
114 Loop
115
116 l_amount := Null;
117 l_amount := dbms_lob.getlength(msg_rec.body_text);
118 l_msg_text := Null;
119
120 dbms_lob.read(
121 lob_loc => msg_rec.body_text,
122 amount => l_amount,
123 offset => 1,
124 buffer => l_msg_text );
125
126 l_source_id := Null;
127
128 If msg_rec.msg_code in ('CSISOFUL', 'CSIRMAFL') Then
129 xnp_xml_utils.decode(l_msg_text, 'ORDER_LINE_ID', l_source_id);
130 l_source_type := 'ORDER_LINE_ID';
131 Else
132 xnp_xml_utils.decode(l_msg_text, 'MTL_TRANSACTION_ID', l_source_id);
133 l_source_type := 'MTL_TRANSACTION_ID';
134 End If;
135
136 Insert Into csi_xnp_msgs_temp(
137 msg_id,
138 msg_code,
139 msg_text,
140 msg_status,
141 source_id,
142 source_type,
143 creation_date,
144 description,
145 process_flag
146 )
147 Values
148 (
149 msg_rec.msg_id,
150 msg_rec.msg_code,
151 msg_rec.body_text, --l_msg_text, --Changed for Bug 13384784
152 msg_rec.msg_status,
153 l_source_id,
154 l_source_type,
155 msg_rec.creation_date,
156 msg_rec.description,
157 'Y'
158 );
159
160 If mod(msg_cur%rowcount, 100) = 0 THEN
161 commit;
162 End If;
163 End Loop; -- msg_rec in msg_cur
164 End decode_queue;
165 --
166 --
167 -- This procedure gets all the discrepancies in the data between the IB and INV
168 -- for Non-Serialized items
169 --
170 Procedure IB_INV_Disc_Non_srl
171 IS
172 Cursor INV_ONH_BAL_CUR IS
173 Select moq.organization_id organization_id
174 , moq.inventory_item_id inventory_item_id
175 , moq.revision revision
176 , moq.subinventory_code subinventory_code
177 , moq.locator_id locator_id
178 , moq.lot_number lot_number
179 , msi.primary_uom_code primary_uom_code
180 , SUM(moq.transaction_quantity) onhand_qty
181 From
182 mtl_system_items msi
183 , mtl_onhand_quantities moq
184 Where msi.inventory_item_id = moq.inventory_item_id
185 And msi.organization_id = moq.organization_id
186 And msi.serial_number_control_code in (1,6) -- No Serial control and at SO Issue Items
187 Group By
188 moq.organization_id
189 , moq.inventory_item_id
190 , moq.revision
191 , moq.subinventory_code
192 , moq.locator_id
193 , moq.lot_number
194 , msi.primary_uom_code;
195
196
197 v_inst_id NUMBER;
198 v_inv_item_id NUMBER;
199 v_inv_rev VARCHAR2(30);
200 v_inv_srl_num VARCHAR2(30);
201 v_inv_lot_num VARCHAR2(80);
202 v_inst_qty NUMBER;
203 v_inv_org_id NUMBER;
204 v_inv_subinv_name VARCHAR2(30);
205 v_inv_locator_id NUMBER;
206 v_loc_type VARCHAR2(30);
207 v_inst_usage VARCHAR2(30);
208 v_freeze_date DATE;
209 v_mast_org_id NUMBER;
210 v_nl_trackable VARCHAR2(1);
211 v_ins_status_id NUMBER;
212 v_instance_id NUMBER;
213 v_end_date DATE;
214 v_party_id NUMBER;
215 v_err_msg VARCHAR2(2000);
216 v_exists VARCHAR2(1);
217 v_ins_obj_nbr NUMBER;
218 v_commit_count NUMBER := 0;
219 l_error_count NUMBER := 0;
220 l_count NUMBER := 0;
221 inst_exists VARCHAR2(1);
222 Error_text VARCHAR2(200);
223 l_inst_count NUMBER := 0;
224 --
225 x_return_status VARCHAR2(1);
226 --
227 process_next Exception;
228 comp_error Exception;
229
230
231 Type NumTabType is VARRAY(10000) of NUMBER;
232 organization_id_mig NumTabType;
233 inventory_item_id_mig NumTabType;
234 locator_id_mig NumTabType;
235 quantity_mig NumTabType;
236 --
237 Type V3Type is VARRAY(10000) of VARCHAR2(3);
238 uom_code_mig V3Type;
239 revision_mig V3Type;
240 --
241 Type V10Type is VARRAY(10000) of VARCHAR2(10);
242 subinv_mig V10Type;
243 --
244 Type V80Type is VARRAY(10000) of VARCHAR2(80); --bnarayan for inventory convergence
245 lot_mig V80Type;
246 --
247 MAX_BUFFER_SIZE NUMBER := 1000;
248
249 Begin
250
251 debug('start of the IB_INV_Disc program for Non-serialized items..');
252
253 -- Load the csi_xnp_msgs_temp table with the records pending in the SFM queue
254 decode_queue;
255 --
256 --
257 Open INV_ONH_BAL_CUR;
258 Loop
259 Fetch INV_ONH_BAL_CUR BULK COLLECT INTO
260 organization_id_mig,
261 inventory_item_id_mig,
262 revision_mig,
263 subinv_mig,
264 locator_id_mig,
265 lot_mig,
266 uom_code_mig,
267 quantity_mig
268 LIMIT MAX_BUFFER_SIZE;
269 --
270 For i in 1 .. organization_id_mig.count
271 Loop
272 Begin
273
274 -- Get the Master Organization ID
275 Begin
276 Select master_organization_id
277 Into v_mast_org_id
278 From MTL_PARAMETERS
279 Where organization_id = organization_id_mig(i);
280 Exception
281 When no_data_found Then
282 Raise Process_next;
283 End;
284 --
285 -- Check for IB trackable
286 --
287 v_nl_trackable := 'N';
288 Begin
289 Select comms_nl_trackable_flag
290 Into v_nl_trackable
291 From MTL_SYSTEM_ITEMS
292 Where inventory_item_id = inventory_item_id_mig(i)
293 And organization_id = v_mast_org_id; -- check should it be org. id
294 Exception
295 When No_Data_Found Then
296 Raise Process_next;
297 End;
298 --
299 -- Check if there are any errors in CSI_TXN_ERRORS
300 --
301 l_error_count := 0;
302 Begin
303 Select Count(*)
304 Into l_error_count
305 From CSI_TXN_ERRORS csi,
306 MTL_MATERIAL_TRANSACTIONS mmt
307 Where csi.inv_material_transaction_id Is Not Null
308 And csi.inv_material_transaction_id = mmt.transaction_id
309 And csi.processed_flag IN ('E','R')
310 And mmt.inventory_item_id = inventory_item_id_mig(i)
311 And mmt.organization_id = organization_id_mig(i);
312 End;
313 --
314 IF nvl(l_error_count,0) > 0 THEN
315 v_err_msg := 'Unable to Synch Item ID '||to_char(inventory_item_id_mig(i))||
316 ' Under Organization '||to_char(organization_id_mig(i))||'pending in CSI_TXN_ERRORS';
317 --debug(v_err_msg);
318 Raise Process_next;
319 End If;
320 --
321 -- Check whether there are any pending transactions in SFM Queue
322 --
323 If l_error_count = 0 Then
324
325 Begin
326 Select Count(*)
327 Into l_error_count
328 From CSI_XNP_MSGS_TEMP xnp,
329 MTL_MATERIAL_TRANSACTIONS mmt
330 Where xnp.source_id = mmt.transaction_id
331 And xnp.source_type = 'MTL_TRANSACTION_ID'
332 And mmt.inventory_item_id = inventory_item_id_mig(i)
333 And mmt.organization_id = organization_id_mig(i)
334 And nvl(xnp.msg_status, 'READY') <> 'PROCESSED';
335 End;
336 End If;
337 --
338 If nvl(l_error_count, 0) > 0 Then
339 v_err_msg := 'Unable to Synch Item ID '||to_char(inventory_item_id_mig(i))||
340 ' Under Organization '||to_char(organization_id_mig(i))||'pending in SFM queue';
341 --debug(v_err_msg);
342 Raise Process_next;
343 End If;
344 --
345 -- Select the IB data
346 --
347 v_exists := Null;
348 Begin
349 Select instance_id
350 , inventory_item_id
351 , inventory_revision
352 , lot_number
353 , quantity
354 , active_end_date
355 , location_type_code
356 , inv_organization_id
357 , inv_subinventory_name
358 , inv_locator_id
359 , instance_usage_code
360 Into v_inst_id
361 , v_inv_item_id
362 , v_inv_rev
363 , v_inv_lot_num
364 , v_inst_qty
365 , v_end_date
366 , v_loc_type
367 , v_inv_org_id
368 , v_inv_subinv_name
369 , v_inv_locator_id
370 , v_inst_usage
371 From csi_item_instances
372 Where inventory_item_id = inventory_item_id_mig(i)
373 And last_vld_organization_id = organization_id_mig(i)
374 And location_type_code = 'INVENTORY'
375 And instance_usage_code = 'IN_INVENTORY'
376 And inv_subinventory_name = subinv_mig(i)
377 And nvl(inv_locator_id,-999) = nvl(locator_id_mig(i),-999)
378 And nvl(inventory_revision,'$#$') = nvl(revision_mig(i),'$#$')
379 And nvl(lot_number,'$#$') = nvl(lot_mig(i),'$#$')
380 And ((active_end_date IS NULL) OR (active_end_date > SYSDATE));
381 v_exists := 'Y';
382 Exception
383 When No_Data_Found Then
384 v_exists := 'N';
385 When Too_Many_Rows Then
386 Raise Process_next;
387 END;
388 --
389 -- if the on-hand quantity in the INV doesn't match with the quantity in IB or if
390 -- there are any active item instances for which comms_nl_trackable_flag is 'NULL' OR 'N'
391 -- the then dump the difference into a temp table
392 --
393 If v_exists = 'Y'
394 Then
395 If v_inst_qty <> quantity_mig(i)
396 OR NVL(v_nl_trackable, 'N') <> 'Y'
397 Then
398
399 Begin
400 Insert Into CSI_INV_DISCREPANCY_TEMP
401 (
402 discrepancy_id
403 ,inventory_item_id
404 ,serial_number
405 ,inv_revision
406 ,inv_lot_number
407 ,inv_quantity
408 ,inv_organization_id
409 ,inv_subinventory_name
410 ,inv_locator_id
411 ,instance_id
412 ,ii_revision
413 ,ii_lot_number
414 ,ii_quantity
415 ,ii_organization_id
416 ,ii_subinventory_name
417 ,ii_locator_id
418 ,ii_location_type_code
419 ,instance_usage_code
420 ,master_org_trackable_flag
421 ,child_org_trackable_flag
422 )
423 Values
424 (
425 csi_inv_discrepency_temp_s.Nextval
426 ,inventory_item_id_mig(i)
427 ,Null
428 ,revision_mig(i)
429 ,lot_mig(i)
430 ,quantity_mig(i)
431 ,organization_id_mig(i)
432 ,subinv_mig(i)
433 ,locator_id_mig(i)
434 ,v_inst_id
435 ,v_inv_rev
436 ,v_inv_lot_num
437 ,v_inst_qty
438 ,v_inv_org_id
439 ,v_inv_subinv_name
440 ,v_inv_locator_id
441 ,v_loc_type
442 ,v_inst_usage
443 ,v_nl_trackable
444 ,v_nl_trackable
445 );
446
447 Exception
448 When Others Then
449 Null;
450 v_err_msg := 'Unable to Insert a record into the IB_INV_SYNC table'||SUBSTR(sqlerrm,1,1000);
451 debug(v_err_msg);
452
453 End; -- end of insert into ib_inv_sync table
454
455 End If; -- v_onhand_qty <> v_inst_qty
456 End If; -- v_exists = 'Y'
457 --
458 Exception
459 When process_next Then
460 Null;
461 When Others Then
462 Null;
463 End;
464 --
465 End Loop; -- for loop
466 Commit;
467 Exit When INV_ONH_BAL_CUR%NOTFOUND;
468 End Loop; -- Open loop
469 Commit;
470 Close INV_ONH_BAL_CUR;
471 --
472 Exception
473 When COMP_ERROR Then
474 Null;
475 When Others Then
476 Null;
477
478 End IB_INV_Disc_Non_srl;
479 --
480
481 --
482 -- This function checks whether there are any differences between the INV and IB, if so then returns FALSE
483 --
484 FUNCTION not_the_same(
485 p_instance_rec in csi_datastructures_pub.instance_rec)
486 RETURN boolean
487 IS
488
489 l_not_the_same BOOLEAN := TRUE;
490
491 l_vld_organization_id number;
492 l_inv_organization_id number;
493 l_inv_subinventory_name varchar2(30);
494 l_inventory_revision varchar2(8);
495 l_inv_locator_id number;
496 l_location_type_code varchar(30);
497 l_instance_usage_code varchar(30);
498 l_lot_number varchar2(80);
499 l_location_id number;
500
501 BEGIN
502 debug('p_instance_rec.instance_id:'||p_instance_rec.instance_id);
503 SELECT last_vld_organization_id,
504 inv_organization_id,
505 inv_subinventory_name,
506 inventory_revision,
507 inv_locator_id,
508 location_type_code,
509 instance_usage_code,
510 location_id,
511 lot_number
512 INTO l_vld_organization_id,
513 l_inv_organization_id,
514 l_inv_subinventory_name,
515 l_inventory_revision,
516 l_inv_locator_id,
517 l_location_type_code,
518 l_instance_usage_code,
519 l_location_id,
520 l_lot_number
521 FROM csi_item_instances
522 WHERE instance_id = p_instance_rec.instance_id;
523
524 IF (nvl(p_instance_rec.vld_organization_id, fnd_api.g_miss_num) =
525 nvl(l_vld_organization_id, fnd_api.g_miss_num))
526 AND
527 (nvl(p_instance_rec.inv_organization_id,fnd_api.g_miss_num) =
528 nvl(l_inv_organization_id, fnd_api.g_miss_num))
529 AND
530 (nvl(p_instance_rec.inv_subinventory_name, fnd_api.g_miss_char) =
531 nvl(l_inv_subinventory_name, fnd_api.g_miss_char))
532 AND
533 (nvl(p_instance_rec.inventory_revision, fnd_api.g_miss_char) =
534 nvl(l_inventory_revision, fnd_api.g_miss_char))
535 AND
536 (nvl(p_instance_rec.inv_locator_id, fnd_api.g_miss_num) =
537 nvl(l_inv_locator_id, fnd_api.g_miss_num))
538 AND
539 (nvl(p_instance_rec.location_type_code, fnd_api.g_miss_char) =
540 nvl(l_location_type_code, fnd_api.g_miss_char))
541 AND
542 (nvl(p_instance_rec.instance_usage_code, fnd_api.g_miss_char) =
543 nvl(l_instance_usage_code, fnd_api.g_miss_char))
544 AND
545 (nvl(p_instance_rec.lot_number, fnd_api.g_miss_char) =
546 nvl(l_lot_number, fnd_api.g_miss_char))
547 THEN
548 l_not_the_same := FALSE;
549
550 --debug('IB and INV attributes for ser. item are not same');
551 END IF;
552
553 RETURN l_not_the_same;
554
555 END not_the_same;
556 --
557 --
558 -- This procedure gets all the discrepancies between the IB and INV for Serialized items
559 --
560 PROCEDURE IB_INV_Disc_serials IS
561
562 CURSOR srl_cur
563 IS
564 SELECT msn.serial_number serial_number,
565 msn.inventory_item_id inventory_item_id,
566 msk.concatenated_segments item_name,
567 msn.current_organization_id organization_id,
568 msn.revision revision,
569 msn.current_subinventory_code subinventory_code,
570 msn.current_locator_id locator_id,
571 msn.lot_number lot_number,
572 msi.primary_uom_code uom_code,
573 msi.serial_number_control_code serial_code,
574 msi.lot_control_code lot_code
575 FROM mtl_system_items msi,
576 mtl_serial_numbers msn,
577 mtl_system_items_kfv msk
578 WHERE msi.inventory_item_id = msn.inventory_item_id
579 AND msi.organization_id = msn.current_organization_id
580 AND msi.inventory_item_id = msk.inventory_item_id
581 AND msi.organization_id = msk.organization_id
582 AND msi.serial_number_control_code IN (2,5)
583 AND msn.current_status = 3
584 AND EXISTS (
585 SELECT '1'
586 FROM mtl_parameters mp,
587 mtl_system_items msi_mast
588 WHERE mp.organization_id = msi.organization_id
589 AND msi_mast.inventory_item_id = msi.inventory_item_id
590 AND msi_mast.organization_id = mp.master_organization_id)
591 -- AND nvl(msi_mast.comms_nl_trackable_flag,'N') = 'Y') --commented to query all the non-trackable items
592 AND EXISTS (
593 SELECT '1'
594 FROM mtl_onhand_quantities moq
595 WHERE moq.inventory_item_id = msn.inventory_item_id
596 AND moq.organization_id = msn.current_organization_id
597 AND moq.subinventory_code = msn.current_subinventory_code
598 AND nvl(moq.locator_id,-999) = nvl(msn.current_locator_id,-999)
599 AND nvl(moq.lot_number,'$#$') = nvl(msn.lot_number,'$#$')
600 AND nvl(moq.revision,'$#$') = nvl(msn.revision,'$#$') );
601
602 CURSOR all_txn_cur(
603 p_serial_number in varchar2,
604 p_item_id in number,
605 p_lot_code in number )
606 IS
607 SELECT /*+ parallel(mut) parallel(mmt) parallel(mtt) */
608 mmt.transaction_id mtl_txn_id,
609 mmt.transaction_date mtl_txn_date,
610 mmt.inventory_item_id item_id,
611 mmt.organization_id organization_id,
612 mmt.transaction_type_id mtl_type_id,
613 mtt.transaction_type_name mtl_txn_name,
614 mmt.transaction_action_id mtl_action_id,
615 mmt.transaction_source_type_id mtl_source_type_id,
616 mmt.transaction_source_id mtl_source_id,
617 mmt.trx_source_line_id mtl_source_line_id,
618 mmt.transaction_quantity mtl_txn_qty,
619 mtt.type_class mtl_type_class,
620 mmt.transfer_transaction_id mtl_xfer_txn_id,
621 to_char(null) lot_number,
622 to_char(mmt.transaction_date,'dd-mm-yy hh24:mi:ss') mtl_txn_char_date
623 FROM mtl_unit_transactions mut,
624 mtl_material_transactions mmt,
625 mtl_transaction_types mtt
626 WHERE p_lot_code = g_no_lot
627 AND mut.serial_number = p_serial_number
628 AND mut.inventory_item_id = p_item_id
629 AND mmt.transaction_id = mut.transaction_id
630 AND mtt.transaction_type_id = mmt.transaction_type_id
631 UNION
632 SELECT /*+ parallel(mut) parallel(mtln) parallel(mmt) parallel(mtt) */
633 mmt.transaction_id mtl_txn_id,
634 mmt.transaction_date mtl_txn_date,
635 mmt.inventory_item_id item_id,
636 mmt.organization_id organization_id,
637 mmt.transaction_type_id mtl_type_id,
638 mtt.transaction_type_name mtl_txn_name,
639 mmt.transaction_action_id mtl_action_id,
640 mmt.transaction_source_type_id mtl_source_type_id,
641 mmt.transaction_source_id mtl_source_id,
642 mmt.trx_source_line_id mtl_source_line_id,
643 mmt.transaction_quantity mtl_txn_qty,
644 mtt.type_class mtl_type_class,
645 mmt.transfer_transaction_id mtl_xfer_txn_id,
646 mtln.lot_number lot_number,
647 to_char(mmt.transaction_date,'dd-mm-yy hh24:mi:ss') mtl_txn_char_date
648 FROM mtl_unit_transactions mut,
649 mtl_transaction_lot_numbers mtln,
650 mtl_material_transactions mmt,
651 mtl_transaction_types mtt
652 WHERE p_lot_code = g_lot
653 AND mut.serial_number = p_serial_number
654 AND mut.inventory_item_id = p_item_id
655 AND mtln.serial_transaction_id = mut.transaction_id
656 AND mmt.transaction_id = mtln.transaction_id
657 AND mtt.transaction_type_id = mmt.transaction_type_id
658 ORDER BY 1 desc;
659
660 l_vld_organization_id NUMBER;
661 l_inventory_item_id NUMBER;
662 v_item_name VARCHAR2(240);
663 l_inv_organization_id NUMBER;
664 l_inv_subinventory_name VARCHAR2(30);
665 l_inventory_revision VARCHAR2(8);
666 l_inv_locator_id NUMBER;
667 l_location_type_code VARCHAR(30);
668 l_instance_usage_code VARCHAR(30);
669 l_lot_number VARCHAR2(80);
670 l_serial_number VARCHAR2(30);
671 l_quantity NUMBER;
672 l_location_id NUMBER;
673 l_instance VARCHAR2(30);
674 v_err_msg VARCHAR2(2000);
675 l_instance_found BOOLEAN := TRUE;
676 l_not_the_same BOOLEAN := TRUE;
677 --
678 skip_serial EXCEPTION;
679 skip_txn EXCEPTION;
680 comp_error EXCEPTION;
681 process_next EXCEPTION;
682 --
683 l_error_message VARCHAR2(2000);
684 l_msg_data VARCHAR2(2000);
685 l_msg_count NUMBER;
686 l_return_status VARCHAR2(1);
687 --
688 l_skip_error VARCHAR2(2000);
689 l_instance_rec csi_datastructures_pub.instance_rec;
690 l_exists VARCHAR2(1);
691 serial_exists VARCHAR2(1);
692 l_error_count NUMBER := 0;
693 l_count NUMBER := 0;
694 inst_exists VARCHAR2(1);
695 v_mast_org_id NUMBER;
696 v_nl_trackable VARCHAR2(1);
697 Error_text VARCHAR2(200);
698 l_inst_count NUMBER := 0;
699 --
700
701 BEGIN
702
703 debug('start of the IB_INV_Disc program for Serialized items..');
704
705 --
706 FOR srl_rec IN srl_cur
707 LOOP
708
709 BEGIN
710
711 l_instance_rec.instance_id := fnd_api.g_miss_num;
712 l_instance_rec.inventory_item_id := srl_rec.inventory_item_id;
713 l_instance_rec.serial_number := srl_rec.serial_number;
714 l_instance_rec.lot_number := srl_rec.lot_number;
715 l_instance_rec.mfg_serial_number_flag := 'Y';
716 l_instance_rec.quantity := 1;
717 l_instance_rec.unit_of_measure := srl_rec.uom_code;
718
719 l_instance_rec.vld_organization_id := srl_rec.organization_id;
720 l_instance_rec.inv_organization_id := srl_rec.organization_id;
721 l_instance_rec.inv_subinventory_name := srl_rec.subinventory_code;
722 l_instance_rec.inv_locator_id := srl_rec.locator_id;
723 l_instance_rec.inventory_revision := srl_rec.revision;
724
725 l_instance_rec.location_type_code := 'INVENTORY';
726 l_instance_rec.instance_usage_code := 'IN_INVENTORY';
727
728 -- Get the Master Organization ID
729 BEGIN
730 SELECT master_organization_id
731 INTO v_mast_org_id
732 FROM MTL_PARAMETERS
733 WHERE organization_id = srl_rec.organization_id;
734 EXCEPTION
735 WHEN no_data_found THEN
736 RAISE Skip_serial;
737 END;
738
739 -- Check for IB trackable
740 v_nl_trackable := 'N';
741 BEGIN
742 SELECT comms_nl_trackable_flag
743 INTO v_nl_trackable
744 FROM MTL_SYSTEM_ITEMS
745 WHERE inventory_item_id = srl_rec.inventory_item_id
746 AND organization_id = v_mast_org_id;
747 EXCEPTION
748 WHEN no_data_found THEN
749 RAISE Skip_serial;
750 END;
751 /*
752 -- If trackable_flag is 'N' in INV, check whether you have any corresponding
753 -- instance in IB
754 IF v_nl_trackable = 'N' THEN
755
756 BEGIN
757 SELECT count(1)
758 INTO l_inst_count
759 FROM CSI_ITEM_INSTANCES
760 WHERE inventory_item_id = srl_rec.inventory_item_id
761 AND ((active_end_date IS NULL) OR (active_end_date > 'SYSDATE'))
762 AND ROWNUM < 2;
763 EXCEPTION
764 WHEN no_data_found THEN
765 v_nl_trackable := 'N';
766 END;
767 END IF;
768 --
769 --
770 IF nvl(l_inst_count,0) > 0
771 THEN
772 Error_text := 'IB has instances having comms_nl_trackable_flag set to N in item definition';
773 ELSE
774 Error_text := '';
775 END IF;
776 */ --
777
778 -- Ignore if not Trackable
779 -- IF NVL(v_nl_trackable,'N') <> 'Y' THEN
780 -- Raise Skip_serial;
781 -- END IF; -- nl_trackable check
782 --
783 -- Check if there are any instances for item-serial combination
784 --
785 BEGIN
786 SELECT instance_id ,
787 object_version_number
788 INTO l_instance_rec.instance_id,
789 l_instance_rec.object_version_number
790 FROM CSI_ITEM_INSTANCES
791 WHERE inventory_item_id = srl_rec.inventory_item_id
792 AND serial_number = srl_rec.serial_number;
793
794 l_instance := to_char(l_instance_rec.instance_id);
795 l_instance_found := TRUE;
796 EXCEPTION
797 WHEN no_data_found THEN
798 l_instance := 'NONE';
799 l_instance_found := FALSE;
800 WHEN too_many_rows THEN
801 l_instance_found := TRUE;
802 l_skip_error := ' Too Many Instances for this serial number';
803 Raise skip_serial;
804 END;
805 --
806 -- get the item name
807 --
808 BEGIN
809 SELECT concatenated_segments
810 INTO v_item_name
811 FROM MTL_SYSTEM_ITEMS_KFV
812 WHERE inventory_item_id = srl_rec.inventory_item_id
813 AND organization_id = srl_rec.organization_id;
814 EXCEPTION
815 WHEN no_data_found THEN
816 NULL;
817 WHEN Others THEN
818 Raise skip_serial;
819 END;
820 --
821 --
822 -- Initialize the IB variables
823 --
824 l_vld_organization_id := NULL;
825 l_inventory_item_id := NULL;
826 l_inv_organization_id := NULL;
827 l_inv_subinventory_name := NULL;
828 l_inventory_revision := NULL;
829 l_inv_locator_id := NULL;
830 l_location_type_code := NULL;
831 l_instance_usage_code := NULL;
832 l_location_id := NULL;
833 l_lot_number := NULL;
834 l_serial_number := NULL;
835 l_quantity := NULL;
836 --
837 --
838 IF l_instance_found THEN
839 -- check if the instance inv location attributes are the same as the serial attribute
840 l_not_the_same := not_the_same(l_instance_rec);
841 END IF;
842
843 -- fixable candidates (no serial found or not the same)
844 IF l_not_the_same --OR NOT(l_instance_found)
845 THEN
846
847 IF l_instance_rec.instance_id IS NOT NULL
848 THEN
849 --dbms_output.put_line('instance_id'||l_instance_rec.instance_id);
850 SELECT last_vld_organization_id,
851 inventory_item_id,
852 inv_organization_id,
853 inv_subinventory_name,
854 inventory_revision,
855 inv_locator_id,
856 location_type_code,
857 instance_usage_code,
858 location_id,
859 lot_number,
860 serial_number,
861 quantity
862 INTO l_vld_organization_id,
863 l_inventory_item_id,
864 l_inv_organization_id,
865 l_inv_subinventory_name,
866 l_inventory_revision,
867 l_inv_locator_id,
868 l_location_type_code,
869 l_instance_usage_code,
870 l_location_id,
871 l_lot_number,
872 l_serial_number,
873 l_quantity
874 FROM CSI_ITEM_INSTANCES
875 WHERE instance_id = l_instance_rec.instance_id;
876 END IF;
877
878 -- Check for any inv material transactions in csi_txn_errors with status of ('E','R')
879 FOR all_txn IN ALL_TXN_CUR(srl_rec.serial_number,
880 srl_rec.inventory_item_id,
881 srl_rec.lot_code)
882 LOOP
883 --dbms_output.put_line(substr('Value of all_txn.mtl_txn_id='||all_txn.mtl_txn_id,1,255));
884 l_error_count := 0;
885 -- Check if there are any errors in CSI_TXN_ERRORS
886 BEGIN
887 SELECT COUNT(*)
888 INTO l_error_count
889 FROM CSI_TXN_ERRORS csi
890 WHERE csi.inv_material_transaction_id IS NOT NULL
891 AND csi.inv_material_transaction_id = all_txn.mtl_txn_id
892 AND csi.processed_flag IN ('E','R');
893 END;
894 --
895 IF nvl(l_error_count,0) > 0 THEN
896 Exit;
897 END IF;
898
899 -- Check whether there are any pending transactions in SFM Queue
900 IF l_error_count = 0 THEN
901 debug('there are no error txns in csi_txn_errors');
902 l_count := 0;
903 BEGIN
904 SELECT COUNT(*)
905 INTO l_error_count
906 FROM CSI_XNP_MSGS_TEMP xnp
907 WHERE xnp.source_id IS NOT NULL
908 AND xnp.source_id = all_txn.mtl_txn_id
909 AND xnp.source_type = 'MTL_TRANSACTION_ID'
910 AND nvl(xnp.msg_status, 'READY') <> 'PROCESSED';
911 END;
912 END IF;
913 --
914 IF nvl(l_error_count, 0) > 0 THEN
915 Exit;
916 END IF;
917 --
918 END LOOP; -- end loop for FOR all_txn IN ALL_TXN_CUR
919 --
920
921 -- Dump the discrepancies into a temporary table
922 IF l_error_count = 0
923 THEN
924 IF NVL(v_nl_trackable, 'N') <> 'Y'
925 THEN
926
927 BEGIN
928
929 INSERT INTO CSI_INV_DISCREPANCY_TEMP
930 (
931 discrepancy_id
932 ,inventory_item_id
933 ,serial_number
934 ,inv_revision
935 ,inv_lot_number
936 ,inv_quantity
937 ,inv_organization_id
938 ,inv_subinventory_name
939 ,inv_locator_id
940 ,instance_id
941 ,ii_revision
942 ,ii_lot_number
943 ,ii_quantity
944 ,ii_organization_id
945 ,ii_subinventory_name
946 ,ii_locator_id
947 ,ii_location_type_code
948 ,instance_usage_code
949 ,master_org_trackable_flag
950 ,child_org_trackable_flag
951 )
952 VALUES
953 (
954 csi_inv_discrepency_temp_s.Nextval
955 ,l_instance_rec.inventory_item_id
956 ,l_instance_rec.serial_number
957 ,l_instance_rec.inventory_revision
958 ,l_instance_rec.lot_number
959 ,l_instance_rec.quantity
960 ,l_instance_rec.inv_organization_id
961 ,l_instance_rec.inv_subinventory_name
962 ,l_instance_rec.inv_locator_id
963 ,l_instance_rec.instance_id
964 ,l_inventory_revision
965 ,l_lot_number
966 ,l_quantity
967 ,l_inv_organization_id
968 ,l_inv_subinventory_name
969 ,l_inv_locator_id
970 ,l_location_type_code
971 ,l_instance_usage_code
972 ,v_nl_trackable
973 ,v_nl_trackable
974 );
975
976 EXCEPTION
977 WHEN OTHERS THEN
978 v_err_msg := 'Unable to Insert a record into the CSI_INV_DISCREPENCY_TEMP table'||SUBSTR(sqlerrm,1,1000);
979 NULL;
980 END; -- end of insert into ib_inv_sync table
981 --
982 END IF;
983 --
984 END IF; -- end if for l_error_count = 0
985 --
986
987 END IF; -- end if for l_not_the_same
988 --
989 EXCEPTION
990 WHEN skip_serial THEN
991 NULL;
992 WHEN OTHERS THEN
993 NULL;
994 END;
995 --
996 IF mod(srl_cur%rowcount,100) = 0 THEN
997 COMMIT;
998 END IF;
999 --
1000 END LOOP; -- end loop for FOR srl_rec IN srl_cur
1001 EXCEPTION
1002 WHEN comp_error THEN
1003 NULL;
1004 END IB_INV_Disc_serials;
1005 --
1006 --
1007 --
1008 --
1009 -- PROCEDURE GET_REPORT_CLOB
1010 -- This procedure will retrieve the Report output file from the OS and
1011 -- embed it into the body of an email.
1012 -- This requires creating a DOCUMENT Message Attribute with the default value:
1013 --
1014 -- plsqlclob:RM_SEND_REPORT.GET_REPORT_CLOB/REQ_ID
1015 --
1016 -- where REQ_ID is the internal name of the Message Attribute which points
1017 -- to the item attribute containing the Request Id.
1018 --
1019 -- This function would be used in conjunction with the
1020 -- FND_WF_STANDARD.EXECUTECONCPROG which will execute a concurrent request
1021 -- and return the Result of 'Normal', 'Cancelled', 'Warning', 'Terminated',
1022 -- or 'Error'
1023 --
1024 -- We will query the request and embed the OUTPUT if it is 'Normal', otherwise
1025 -- we output the request LOG.
1026 --
1027 -- To get the access of the file you must create a db directory for this with the
1028 -- following commands where the REPORT_OUT directory is $APPLCSF/$APPLOUT and
1029 -- REPORT_LOG directory = $APPLCSF/$APPLLOG or applicable directories:
1030 --
1031 -- create directory REPORT_OUT as '/vis11i/common/admin/out';
1032 -- create directory REPORT_LOG as '/vis11i/common/admin/log';
1033 -- connect sys/<syspwd>@<SID>
1034 -- grant read on directory REPORT_OUT to APPS;
1035 -- grant read on directory REPORT_LOG to APPS;
1036
1037 --
1038 -- Procedure that gets the report output file located on the middle tier/or from the server
1039 --
1040 PROCEDURE GET_REPORT_CLOB ( document_id in varchar2,
1041 display_type in varchar2,
1042 document in out nocopy clob,
1043 document_type in out nocopy varchar2)
1044 IS
1045
1046 l_item_type wf_items.item_type%TYPE;
1047 l_item_key wf_items.item_key%TYPE;
1048
1049 l_document_type VARCHAR2(25);
1050 l_document VARCHAR2(32000) := '';
1051
1052 NL VARCHAR2(1) := fnd_global.newline;
1053 l_header_id number;
1054 l_file varchar2(100);
1055 l_amt number;
1056 l_pos number;
1057
1058 p_filedir varchar2(100);
1059 p_filename varchar2(100);
1060 l_theBFILE BFILE;
1061 l_theCLOB CLOB;
1062 l_total_bytes number;
1063 l_exists INTEGER;
1064 l_open INTEGER;
1065 l_req_id varchar2(100);
1066 l_comp_stat varchar2(1);
1067
1068 v_Buffer VARCHAR2(80);
1069 v_Offset INTEGER := 1;
1070 v_Amount INTEGER := 80;
1071
1072
1073
1074 BEGIN
1075
1076 -- l_file := oe_debug_pub.set_debug_mode('FILE');
1077 --dbms_output.put_line('inside get_report_clob');
1078
1079 dbms_lob.createtemporary(l_theCLOB, TRUE, DBMS_LOB.session);
1080
1081 debug('Inside CSI_SEND_REPORT.GET_REPORT_CLOB');
1082 debug('document_id = ' || document_id);
1083
1084 -- Get the current status of the Report concurrent request
1085 select STATUS_CODE
1086 into l_comp_stat
1087 from fnd_concurrent_requests
1088 where request_id = to_number(document_id);
1089
1090 debug('l_comp_stat = ' || l_comp_stat);
1091 --dbms_output.put_line('Value of l_comp_stat='||l_comp_stat);
1092 -- If completion status = C then attempt to get the OUTPUT of report
1093 if l_comp_stat = 'C' then
1094 p_filedir := 'REPORT_OUT';
1095 p_filename := 'o'||document_id|| '.out';
1096 --dbms_output.put_line('Value of p_filename='||TO_CHAR(p_filename));
1097 --debug('p_filedir = ' || p_filedir);
1098 debug('p_filename = ' || p_filename);
1099
1100 -- Set the BFILE Directory and FileName
1101 l_theBFILE := BFileName(p_filedir,p_filename);
1102
1103 -- First make certain the requested file exists
1104 l_exists := dbms_lob.fileexists(l_theBFILE);
1105
1106 -- If the OUTPUT file does not exist then Try the Log File
1107 if l_exists = 0 then
1108
1109 -- reset the l_comp_stat to 'F' so we will look for the log file in the next if statement
1110 l_comp_stat := 'F';
1111 l_document := 'OUTPUT file does not exists for request id, '|| document_id ||'. Attempting to retrieve log file.';
1112 l_pos := dbms_lob.getlength(document) + 1;
1113 l_amt := length(l_document);
1114 dbms_lob.write(document,l_amt,l_pos,l_document);
1115
1116 end if; --l_exists = 0 then (OUTPUT FILE)
1117 end if; -- l_comp_stat = 'C'
1118
1119 -- If completion status <> C then attempt to get the LOG of report
1120 if l_comp_stat <> 'C' then
1121 p_filedir := 'REPORT_LOG';
1122 p_filename := 'l'||document_id|| '.req';
1123
1124 --debug('p_filedir = ' || p_filedir);
1125 debug('p_filename = ' || p_filename);
1126
1127 -- Set the BFILE Directory and FileName
1128 l_theBFILE := BFileName(p_filedir,p_filename);
1129
1130 -- First make certain the requested file exists
1131 l_exists := dbms_lob.fileexists(l_theBFILE);
1132
1133 -- If the OUTPUT file does not exist then Try the Log File
1134 if l_exists = 0 then
1135
1136 l_document := 'Neither an OUTPUT or LOG file exists for request id, '|| document_id;
1137 l_pos := dbms_lob.getlength(document) + 1;
1138 l_amt := length(l_document);
1139 dbms_lob.write(document,l_amt,l_pos,l_document);
1140
1141 end if; --l_exists = 0 then (LOG FILE)
1142 end if; -- l_comp_stat <> 'C'
1143
1144 -- Send the OUTPUT or LOG to the notification body if the file exists
1145 if l_exists = 1 then
1146
1147 -- to see if the file was opened using the input BFILE locators
1148 l_open := dbms_lob.FILEISOPEN(l_theBFILE);
1149
1150 if l_open = 1 then
1151 -- File is already open so ignore
1152 null;
1153 debug('File already opened');
1154 else
1155 -- Open the file
1156 dbms_lob.fileOpen(l_theBFILE);
1157 debug('Opened LOG File');
1158 end if; -- l_open = 1
1159 -- Read the LOG file into the CLOB;
1160 dbms_lob.loadFromFile(dest_lob => l_theCLOB,
1161 src_lob => l_theBFILE,
1162 amount => dbms_lob.getLength(l_theBFILE));
1163
1164 l_total_bytes:=dbms_lob.getLength(l_theCLOB);
1165
1166 if (display_type = 'text/html') then
1167 debug('Inside the text/html');
1168
1169 l_document := '<br>' || '<br> <pre>' ;
1170 l_document := l_document || '<font face="courier new" size=2>';
1171
1172 l_pos := dbms_lob.getlength(document) + 1;
1173 l_amt := length(l_document);
1174 dbms_lob.write(document,l_amt,l_pos,l_document);
1175
1176 -- Now append l_theCLOB (report) to the document;
1177 dbms_lob.append(document, l_theCLOB);
1178
1179 -- Append a few breaks on the end;
1180 l_document := '<br>' || '<br> </pre>';
1181
1182 l_pos := dbms_lob.getlength(document) + 1;
1183 l_amt := length(l_document);
1184 dbms_lob.write(document,l_amt,l_pos,l_document);
1185
1186 --Close the file handle
1187 dbms_lob.fileClose(l_theBFILE);
1188
1189 elsif (display_type = 'text/plain') then
1190
1191 debug('Inside the text/plain');
1192 -- Append l_theCLOB (report) to the document;
1193 dbms_lob.APPEND(document, l_theCLOB);
1194
1195 --Close the file handle
1196 dbms_lob.fileClose(l_theBFILE);
1197
1198 end if; --(display_type = 'text/html')
1199
1200 end if; -- File exists(l_exists = 1)
1201
1202 END GET_REPORT_CLOB;
1203 --
1204 --
1205 --This procedure is to launch a workflow process
1206 --
1207 PROCEDURE Launch_Workflow (p_msg IN VARCHAR2,
1208 p_req_id IN NUMBER)
1209 IS
1210
1211 --Workflow attributes
1212 l_itemtype Varchar2(40) := 'CSIINVWF';
1213 l_itemkey Varchar2(240) := 'CSI-'||to_char(sysdate,'MMDDYYYYHH24MISS');
1214 l_process Varchar2(40) := 'CSIIBINVWF';
1215 l_notify Varchar2(10) := 'Y';
1216 l_receiver Varchar2(100);
1217 l_itemkey_seq Integer ;
1218
1219 BEGIN
1220 -- Recipient is derived from profile option
1221 -- l_receiver := Fnd_Profile.Value ('CSI_INTEGRATION_NOTIFY_TO');
1222
1223 -- IF l_receiver IS NULL THEN
1224 -- l_receiver := 'Oracle Installed Base Admin';
1225 -- END IF;
1226
1227 -- Sequence generation which will be used for item uniqueness
1228 Select CSI_WF_ITEM_KEY_NUMBER_S.Nextval
1229 Into l_itemkey_seq
1230 From dual;
1231
1232 l_itemkey := 'CSI-'||l_itemkey_seq;
1233
1234 WF_ENGINE.CreateProcess
1235 (
1236 itemtype => l_itemtype,
1237 itemkey => l_itemkey,
1238 process => l_process
1239 );
1240
1241 WF_ENGINE.SetItemAttrText
1242 (
1243 itemtype => l_itemtype,
1244 itemkey => l_itemkey,
1245 aname => '#FROM_ROLE',
1246 avalue => 'Oracle Installed Base Admin'
1247 );
1248
1249 WF_ENGINE.SetItemAttrText
1250 (
1251 itemtype => l_itemtype,
1252 itemkey => l_itemkey,
1253 aname => 'CSI_TEXT',
1254 avalue => p_msg
1255 );
1256
1257 WF_ENGINE.SetItemAttrText
1258 (
1259 itemtype => l_itemtype,
1260 itemkey => l_itemkey,
1261 aname => 'REQ_ID',
1262 avalue => p_req_id
1263 );
1264
1265 WF_ENGINE.SetItemAttrText
1266 (
1267 itemtype => l_itemtype,
1268 itemkey => l_itemkey,
1269 aname => 'CSI_RECV',
1270 avalue => 'Oracle Installed Base Admin'
1271 );
1272
1273 WF_ENGINE.StartProcess
1274 (
1275 itemtype => l_itemtype,
1276 itemkey => l_itemkey
1277 );
1278
1279
1280 END;
1281
1282 --
1283 -- This procedure is called from the concurrent prog.
1284 --
1285 PROCEDURE IB_INV_DISCREPANCY( errbuf OUT NOCOPY VARCHAR2,
1286 retcode OUT NOCOPY NUMBER ) IS
1287
1288 l_count NUMBER;
1289 l_disc_count NUMBER;
1290 l_exists VARCHAR2(1);
1291 l_errbuf VARCHAR2(2000);
1292 NL VARCHAR2(1) := fnd_global.newline;
1293 l_request_id NUMBER;
1294
1295 l_schema_name varchar2(30);
1296 l_object_name varchar2(80);
1297 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1298
1299 BEGIN
1300 --
1301 -- Empty the discrepancy table before each run
1302 --
1303 get_schema_name(
1304 p_product_short_name => 'CSI',
1305 x_schema_name => l_schema_name,
1306 x_return_status => l_return_status);
1307
1308 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1309 RAISE fnd_api.g_exc_error;
1310 END IF;
1311
1312 l_object_name := l_schema_name||'.csi_inv_discrepancy_temp';
1313
1314 -- Empty the discrepancy table before each run
1315 truncate_table(l_object_name);
1316 --
1317 -- Call the Non-Serialized discrepancy procedure
1318 --
1319 debug( 'Calling IB_INV_Disc_Non_srl : '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
1320 CSI_INV_DISCREPANCY_PKG.IB_Inv_Disc_Non_srl;
1321 --
1322 -- Call the Serialized discrepancy procedure
1323 --
1324 debug( 'Calling IB_INV_Disc_serials : '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
1325 CSI_INV_DISCREPANCY_PKG.IB_Inv_Disc_serials;
1326 --
1327 --Check if there are any discrepancies recorded in the discrepancy table
1328 --
1329 BEGIN
1330 SELECT count(*)
1331 INTO l_disc_count
1332 FROM CSI_INV_DISCREPANCY_TEMP;
1333 END;
1334 --
1335 -- If there are any data discrepancies between IB and INV then display the report
1336 --
1337
1338 IF l_disc_count > 0
1339 THEN
1340 l_request_id := fnd_request.submit_request (
1341 application => 'CSI',
1342 program => 'CSIINVDR',
1343 start_time => NULL,
1344 sub_request => FALSE);
1345
1346 debug('Calling Install Base and Inventory Discrepancy Report...');
1347 debug('Request ID: '||l_request_id||' has been submitted');
1348 debug('');
1349 COMMIT; -- this commit is the concurrent request in the fnd_concurrent_requests for parallel
1350
1351 IF l_request_id = 0
1352 THEN
1353 FND_MESSAGE.RETRIEVE(l_errbuf);
1354 debug('IB - INV Discrepancy Report has errored');
1355 debug('Error message :'||substr(l_errbuf,1,75));
1356 debug(' :'||substr(l_errbuf,76,150));
1357 debug(' :'||substr(l_errbuf,151,225));
1358 debug(' :'||substr(l_errbuf,226,300));
1359 ELSE
1360 debug('IB - INV Discrepancy Report completed successfully');
1361 END IF;
1362
1363 --If there are any data discrepancies between IB and INV then send a
1364 --notification to the concerned personnel
1365 --
1366 --Launch the workflow to send a notification to the end user in case of discrepancies
1367 --between IB and INV
1368
1369 Launch_Workflow( ''|| fnd_global.local_chr(10) ||
1370 'Hello Install Base User, '|| fnd_global.local_chr(10)||
1371 ''||fnd_global.local_chr(10)||
1372 'Installed Base has detected that there are some discrepancies in the data between ' || --fnd_global.local_chr(10) ||
1373 'Installed Base and Inventory. For more information on these discrepancies please '|| --fnd_global.local_chr(10) ||
1374 'see the IB - INV Discrepancy Report.'||fnd_global.local_chr(10)||
1375 ''||fnd_global.local_chr(10)||
1376 'The details of the report output can be viewed in the concurrent request output' ||
1377 'under the Oracle Installed Base Admin responsibility. Query for the following' ||
1378 'Concurrent Request Id: '||l_request_id ||
1379 ''||fnd_global.local_chr(10)||
1380 ''||fnd_global.local_chr(10)||
1381 ''||fnd_global.local_chr(10)||
1382 'Thank You'|| fnd_global.local_chr(10) ||
1383 ''||fnd_global.local_chr(10)||
1384 'Oracle Install Base', l_request_id);
1385
1386 END IF;
1387
1388 --
1389 END IB_INV_DISCREPANCY;
1390 --
1391
1392
1393
1394
1395 END CSI_INV_DISCREPANCY_PKG;
1396 --