[Home] [Help]
PACKAGE BODY: APPS.INV_CONSIGNED_DIAGNOSTICS_PROC
Source
1 PACKAGE BODY INV_CONSIGNED_DIAGNOSTICS_PROC AS
2 -- $Header: INVRCIDB.pls 115.7 2004/02/05 04:00:23 rajkrish noship $
3 --+=======================================================================+
4 --| Copyright (c) 2002 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVRCIDB.pls
10 --|INV_CONSIGNED_INV_PREVAL_PROC
11 --| DESCRIPTION |
12 --| consigned inv Diagnostics/Pre-validation conc pgm
13 --| HISTORY |
14 --| Sep-16
15 --+======================================================================--
16
17 ------------------
18 --- constants
19 -------------------
20 g_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
21 g_user_id NUMBER :=
22 FND_GLOBAL.user_id ;
23 g_request_id NUMBER :=
24 TO_NUMBER(FND_PROFILE.Value('CONC_REQUEST_ID'));
25 g_program_id NUMBER :=
26 fnd_global.conc_program_id ;
27 g_program_application_id NUMBER :=
28 fnd_global.prog_appl_id ;
29 g_login_id NUMBER :=
30 fnd_global.login_id ;
31 g_revalidate_error_code VARCHAR2(40) ;
32
33 --===================
34 -- PROCEDURES AND FUNCTIONS
35 --===================
36
37
38 -------------------
39 --- Retrieve UOM code
40 ----------------------------
41 FUNCTION get_UOM_CODE(p_uom IN VARCHAR2)
42 RETURN VARCHAR2
43
44 IS
45
46
47 l_uom_code VARCHAR2(30) ;
48
49 BEGIN
50
51 IF g_debug = 1
52 THEN
53 INV_LOG_UTIL.trace
54 ( 'Into return from get_uom_code ' || p_uom ,null,9);
55 END IF;
56 l_uom_code := NULL ;
57
58 IF p_uom is not NULL
59 THEN
60 BEGIN
61 SELECT uom_code
62 INTO l_uom_code
63 FROM MTL_units_of_measure
64 WHERE unit_of_measure = p_uom ;
65
66 EXCEPTION
67 WHEN NO_DATA_FOUND
68 THEN
69 l_uom_code := p_uom ;
70
71 WHEN TOO_MANY_ROWS
72 THEN
73 l_uom_code := p_uom ;
74 END ;
75
76 END IF;
77
78 IF g_debug = 1
79 THEN
80 INV_LOG_UTIL.trace
81 ( 'about to return from get_uom_code ' || l_uom_code ,null,9);
82 END IF;
83
84 RETURN( l_uom_code );
85
86
87 END get_uom_code ;
88
89 ---------------------------------------------------------
90 --- Update consumption date
91 -----------------------------------------------------------
92 PROCEDURE Update_Consumption_Date
93 IS
94
95
96 BEGIN
97
98 IF g_debug = 1
99 THEN
100 INV_LOG_UTIL.trace
101 ( '>> Update_Consumption_Date',null,9);
102 END IF;
103
104 UPDATE mtl_consigned_diag_errors mcde
105 SET mcde.consumption_date =
106 ( select MIN(mmt.transaction_date)
107 FROM mtl_material_transactions mmt
108 WHERE mmt.inventory_item_id = mcde.inventory_item_id
109 and NVL(mmt.revision, -980980) = NVL(mcde.revision,-980980)
110 and mmt.organization_id = mcde.organization_id
111 and mmt.owning_organization_id = mcde.owning_organization_id
112 )
113 WHERE mcde.consumption_date is NULL
114 and mcde.record_type = 2 ;
115
116
117 IF g_debug = 1
118 THEN
119 INV_LOG_UTIL.trace
120 ( '>> Update_Consumption_Date',null,9);
121 END IF;
122
123
124
125 EXCEPTION
126
127 WHEN OTHERS THEN
128 IF g_debug = 1
129 THEN
130 INV_LOG_UTIL.trace
131 ( 'SQLERRM: '|| SQLERRM,9 );
132
133 INV_LOG_UTIL.trace
134 ( 'OTHERS exception ',
135 'Update_Consumption_Date',9);
136 END IF;
137
138 RAISE ;
139
140 END Update_Consumption_Date ;
141
142
143 ------------------------------------------------------------
144 ---- Purge_diagnostics_passed_rec
145 ------------------------------------------------------------
146 PROCEDURE Purge_diagnostics_passed_rec
147 IS
148
149 BEGIN
150
151 IF g_debug = 1
152 THEN
153 INV_LOG_UTIL.trace
154 ( '>> Purge_diagnostics_passed_rec' ,9);
155 END IF;
156
157 DELETE FROM
158 mtl_consigned_diag_errors
159 WHERE ( request_id <> g_request_id) OR (
160 request_id is NULL ) ;
161
162
163 IF g_debug = 1
164 THEN
165 INV_LOG_UTIL.trace
166 ( '<<< Purge_diagnostics_passed_rec' ,9);
167 END IF;
168
169
170
171 EXCEPTION
172
173 WHEN OTHERS THEN
174 IF g_debug = 1
175 THEN
176 INV_LOG_UTIL.trace
177 ( 'SQLERRM: '|| SQLERRM,9 );
178
179 INV_LOG_UTIL.trace
180 ( 'OTHERS exception ',
181 'Purge_diagnostics_passed_rec' ,9);
182 END IF;
183
184 RAISE ;
185
186 END Purge_diagnostics_passed_rec ;
187
188
189 -------------------------------------------------------------
190 --- Process_Validated_Record
191 ------------------------------------------------------------
192 PROCEDURE Process_Validated_Record
193 ( p_record_type IN NUMBER )
194 IS
195
196 BEGIN
197 IF g_debug = 1
198 THEN
199 INV_LOG_UTIL.trace
200 ( 'Start Move records ','Process_Validated_Record',9);
201 INV_LOG_UTIL.trace
202 ( 'p_record_type => '|| p_record_type,9);
203 END IF;
204
205 BEGIN
206 IF p_record_type = 1
207 THEN
208 INSERT INTO
209 mtl_consigned_diag_errors
210 ( RECORD_ID
211 , ORGANIZATION_ID
212 , INVENTORY_ITEM_ID
213 , REVISION
214 , OWNING_TP_TYPE
215 , OWNING_ORGANIZATION_ID
216 , PLANNING_TP_TYPE
217 , PLANNING_ORGANIZATION_ID
218 , PO_HEADER_ID
219 , AGENT_ID
220 , RECORD_TYPE
221 , ERROR_CODE
222 , ACTION_CODE
223 , LAST_NOTIFICATION_DATE
224 , NOTIFICATION_ID
225 , CREATION_DATE
226 , CREATED_BY
227 , LAST_UPDATE_DATE
228 , LAST_UPDATED_BY
229 , LAST_UPDATE_LOGIN
230 , REQUEST_ID
231 , PROGRAM_APPLICATION_ID
232 , PROGRAM_ID
233 , PROGRAM_UPDATE_DATE
234 , error_type
235 , primary_uom
236 , purchasing_uom
237 ) SELECT
238 mtl_consigned_diag_errors_s.NEXTVAL
239 , mcdet.ORGANIZATION_ID
240 , mcdet.INVENTORY_ITEM_ID
241 , mcdet.REVISION
242 , 1
243 , mcdet.OWNING_ORGANIZATION_ID
244 , null --- PLANNING_ORGANIZATION_TYPE
245 , null --- PLANNING_ORGANIZATION_ID
246 , mcdet.PO_HEADER_ID
247 , mcdet.AGENT_ID
248 , mcdet.RECORD_TYPE
249 , mcdet.mcde_ERROR_CODE
250 , mcdet.mcde_ACTION_CODE
251 , null -- LAST_NOTIFICATION_DATE
252 , null -- NOTIFICATION_ID
253 , sysdate -- CREATION_DATE
254 , g_user_id -- Created_by
255 , sysdate -- LAST_UPDATE_DATE
256 , g_user_id
257 , g_user_id
258 , g_request_id
259 , g_program_application_id
260 , g_program_id
261 , sysdate
262 , error_type
263 , primary_uom
264 , purchasing_uom
265 FROM mtl_consigned_diag_temp mcdet
266 WHERE mcdet.error_code is not null
267 AND NOT EXISTS
268 ( SELECT 1
269 FROM mtl_consigned_diag_errors mcde
270 WHERE mcde.organization_id =
271 mcdet.organization_id
272 and mcde.owning_organization_id =
273 mcdet.owning_organization_id
274 and mcde.error_code = mcdet.mcde_error_code
275 and mcde.inventory_item_id = mcdet.inventory_item_id
276 and mcde.record_type = mcdet.record_type
277 and NVL(mcde.revision,-9876321)
278 = NVL(mcdet.revision,-9876321)
279 );
280
281 IF g_debug = 1
282 THEN
283 INV_LOG_UTIL.trace
284 ( 'Phase 2',9);
285 END IF;
286
287 UPDATE mtl_consigned_diag_errors mcde
288 SET mcde.request_id =
289 ( SELECT g_request_id
290 FROM mtl_consigned_diag_temp mcdet
291 WHERE MCDE.organization_id = mcdet.organization_id
292 AND MCDE.owning_organization_id =
293 mcdet.owning_organization_id
294 AND MCDE.error_code = mcdet.mcde_error_code
295 AND MCDE.inventory_item_id = mcdet.inventory_item_id
296 AND mcde.record_type = mcdet.record_type
297 AND NVL(mcde.revision,-98763245 )
298 = NVL(mcdet.revision , -98763245 )
299 )
300 WHERE ( MCDE.request_id <> g_request_id) OR
301 ( MCDE.request_id IS NULL ) ;
302
303
304 ELSE
305 INSERT INTO
306 mtl_consigned_diag_errors
307 ( RECORD_ID
308 , ORGANIZATION_ID
309 , INVENTORY_ITEM_ID
310 , REVISION
311 , OWNING_TP_TYPE
312 , OWNING_ORGANIZATION_ID
313 , PLANNING_TP_TYPE
314 , PLANNING_ORGANIZATION_ID
315 , PO_HEADER_ID
316 , AGENT_ID
317 , RECORD_TYPE
318 , ERROR_CODE
319 , ACTION_CODE
320 , LAST_NOTIFICATION_DATE
321 , NOTIFICATION_ID
322 , CREATION_DATE
323 , CREATED_BY
324 , LAST_UPDATE_DATE
325 , LAST_UPDATED_BY
326 , LAST_UPDATE_LOGIN
327 , REQUEST_ID
328 , PROGRAM_APPLICATION_ID
329 , PROGRAM_ID
330 , PROGRAM_UPDATE_DATE
331 , error_type
332 , primary_uom
333 , purchasing_uom
334 ) SELECT
335 mtl_consigned_diag_errors_s.NEXTVAL
336 , mcdet.ORGANIZATION_ID
337 , mcdet.INVENTORY_ITEM_ID
338 , mcdet.REVISION
339 , 1
340 , mcdet.OWNING_ORGANIZATION_ID
341 , null --- PLANNING_ORGANIZATION_TYPE
342 , null --- PLANNING_ORGANIZATION_ID
343 , mcdet.PO_HEADER_ID
344 , mcdet.AGENT_ID
345 , mcdet.RECORD_TYPE
346 , mcdet.mcde_ERROR_CODE
347 , mcdet.mcde_ACTION_CODE
348 , null -- LAST_NOTIFICATION_DATE
349 , null -- NOTIFICATION_ID
350 , sysdate -- CREATION_DATE
351 , g_user_id -- Created_by
352 , sysdate -- LAST_UPDATE_DATE
353 , g_user_id
354 , g_user_id
355 , g_request_id
356 , g_program_application_id
357 , g_program_id
358 , sysdate
359 , error_type
360 , primary_uom
361 , purchasing_uom
362 FROM mtl_consigned_diag_temp mcdet
363 WHERE mcdet.error_code is not null
364 AND NOT EXISTS
365 ( SELECT 1
366 FROM mtl_consigned_diag_errors mcde
367 WHERE mcde.organization_id =
368 mcdet.organization_id
369 and mcde.owning_organization_id =
370 mcdet.owning_organization_id
371 and mcde.error_code = mcdet.mcde_error_code
372 and mcde.inventory_item_id = mcdet.inventory_item_id
373 and mcde.record_type = mcdet.record_type
374 and mcde.po_header_id = mcdet.po_header_id
375 and NVL(mcde.revision,-9876321)
376 = NVL(mcdet.revision,-9876321)
377 )
378 and mcdet.record_type = 2;
379
380 IF g_debug = 1
381 THEN
382 INV_LOG_UTIL.trace
383 ( 'Phase 2 ',9);
384 END IF;
385
386
387 UPDATE mtl_consigned_diag_errors mcde
388 SET mcde.request_id =
389 ( SELECT g_request_id
390 FROM mtl_consigned_diag_temp mcdet
391 WHERE MCDE.organization_id = mcdet.organization_id
392 AND MCDE.owning_organization_id =
393 mcdet.owning_organization_id
394 AND MCDE.error_code = mcdet.mcde_error_code
395 AND MCDE.inventory_item_id = mcdet.inventory_item_id
396 AND mcde.record_type = mcdet.record_type
397 AND mcde.po_header_id = mcdet.po_header_id
398 AND NVL(mcde.revision,-98763245 )
399 = NVL(mcdet.revision , -98763245 )
400 )
401 WHERE ( MCDE.request_id <> g_request_id) OR
402 ( MCDE.request_id IS NULL ) ;
403
404 END IF;
405
406 EXCEPTION
407 WHEN NO_DATA_FOUND
408 THEN
409 IF g_debug = 1
410 THEN
411 INV_LOG_UTIL.trace
412 ( 'No data Found Exception ','Process_Validated_Record',9);
413 END IF;
414 END ;
415
416 IF g_debug = 1
417 THEN
418 INV_LOG_UTIL.trace
419 ( '<< OUT Moving records ','Process_Validated_Record',9);
420 END IF;
421
422 EXCEPTION
423
424 WHEN OTHERS THEN
425 IF g_debug = 1
426 THEN
427 INV_LOG_UTIL.trace
428 ( 'SQLERRM: '|| SQLERRM,9 );
429
430 INV_LOG_UTIL.trace
431 ( 'OTHERS exception ',
432 'Process_Validated_Record' ,9);
433 END IF;
434
435 RAISE ;
436
437 END Process_Validated_Record ;
438
439 ---------------------------------------------------------
440 --PROCEDURE set_error_action_code
441 ----------------------------------------------------------
442 PROCEDURE set_error_action_code
443 ( p_error_code IN VARCHAR2
444 , x_mcde_error_code OUT NOCOPY VARCHAR2
445 , x_mcde_action_code OUT NOCOPY VARCHAR2
446 , x_error_type OUT NOCOPY VARCHAR2
447 )
448
449 IS
450
451 BEGIN
452
453 IF p_error_code = 'INV_CONS_SUP_GL_API_NO_RATE'
454 THEN
455 x_mcde_error_code := 'INV_CONS_SUP_E_GL_API_NO_RATE' ;
456 x_mcde_action_code := 'INV_CONS_SUP_A_GL_API_NO_RATE' ;
457 x_error_type := 2;
458
459 ELSIF p_error_code = 'INV_CONS_SUP_NO_TAX_SETUP'
460 THEN
461 x_mcde_error_code := 'INV_CONS_SUP_E_NO_TAX_SETUP' ;
462 x_mcde_action_code := 'INV_CONS_SUP_A_NO_TAX_SETUP' ;
463 x_error_type := 4 ;
464
465 ELSIF p_error_code = 'INV_CONS_SUP_MANUAL_NUM_CODE'
466 THEN
467 x_mcde_error_code := 'INV_CONS_SUP_E_MAN_NUM_CODE' ;
468 x_mcde_action_code := 'INV_CONS_SUP_A_MAN_NUM_CODE' ;
469 x_error_type := 3 ;
470
471 ELSIF p_error_code = 'INV_CONS_SUP_NO_BPO_EXISTS'
472 THEN
473 x_mcde_error_code := 'INV_CONS_SUP_E_NO_BPO_EXISTS' ;
474 x_mcde_action_code := 'INV_CONS_SUP_A_NO_BPO_EXISTS' ;
475 x_error_type := 1;
476
477 ELSIF p_error_code = 'INV_CONS_SUP_NO_UOM_CONV'
478 THEN
479 x_mcde_error_code := 'INV_CONS_SUP_E_NO_UOM_CONV' ;
480 x_mcde_action_code := 'INV_CONS_SUP_A_NO_UOM_CONV' ;
481 x_error_type := 1 ;
482
483 ELSIF p_error_code = 'INV_CONS_SUP_NO_RATE_SETUP'
484 THEN
485 x_mcde_error_code := 'INV_CONS_SUP_E_NO_RATE_SETUP' ;
486 x_mcde_action_code := 'INV_CONS_SUP_A_NO_RATE_SETUP' ;
487 x_error_type := 2 ;
488
489 ELSIF p_error_code = 'INV_CONS_SUP_AMT_AGREED_FAIL'
490 THEN
491 x_mcde_error_code := 'INV_CONS_SUP_E_AMT_AGREED_FAIL' ;
492 x_mcde_action_code := 'INV_CONS_SUP_A_AMT_AGREED_FAIL' ;
493 x_error_type := 1 ;
494
495 ELSIF p_error_code = 'INV_CONS_SUP_INVALID_BPO'
496 THEN
497 x_mcde_error_code := 'INV_CONS_SUP_E_INVALID_BPO' ;
498 x_mcde_action_code := 'INV_CONS_SUP_A_INVALID_BPO' ;
499 x_error_type := 1 ;
500
501 ELSIF p_error_code = 'INV_CONS_SUP_AMT_AGREED_FAIL'
502 THEN
503 x_mcde_error_code := 'INV_CONS_SUP_E_AMT_AGREED_FAIL' ;
504 x_mcde_action_code := 'INV_CONS_SUP_A_AMT_AGREED_FAIL' ;
505 x_error_type := 1 ;
506
507 ELSIF p_error_code = 'INV_CONS_SUP_GEN_ACCT'
508 THEN
509 x_mcde_error_code := 'INV_CONS_SUP_E_GEN_ACCT' ;
510 x_mcde_action_code := 'INV_CONS_SUP_A_GEN_ACCT' ;
511 x_error_type := 4 ;
512
513 ELSE
514 x_mcde_error_code := 'INV_CONS_SUP_E_NO_BPO_EXISTS' ;
515 x_mcde_action_code := 'INV_CONS_SUP_A_NO_BPO_EXISTS' ;
516 x_error_type := 1;
517 END IF;
518
519
520
521
522
523 END set_error_action_code ;
524
525 FUNCTION get_buyer
526 ( p_po_header_id IN NUMBER
527 , p_inventory_item_id IN NUMBER
528 , p_vendor_site_id IN NUMBER
529 , p_organization_id IN NUMBER
530 , p_revision IN VARCHAR2
531 )
532 RETURN NUMBER
533 IS
534 l_po_header_id NUMBER;
535 l_vendor_id NUMBER ;
536
537 CURSOR C_buyer IS
538 SELECT
539 poh.PO_HEADER_ID
540 , poh.AGENT_ID
541 , poh.SEGMENT1
542 FROM
543 po_headers_all poh
544 , po_lines_all pol
545 , po_line_locations_all poll
546 WHERE poh.po_header_id = pol.po_header_id
547 AND poh.po_header_id = poll.po_header_id
548 AND pol.po_header_id = poll.po_header_id
549 AND pol.po_line_id = poll.PO_LINE_ID
550 AND poll.CONSIGNED_FLAG = 'Y'
551 AND pol.ITEM_ID = p_inventory_item_id
552 AND poh.vendor_id = l_vendor_id
553 AND poh.vendor_site_id = p_vendor_site_id
554 AND ( pol.item_revision = p_revision
555 OR pol.item_revision IS NULL ) ;
556
557 l_buyer_rec C_buyer%ROWTYPE ;
558 l_buyer_id NUMBER ;
559 l_segment1 VARCHAR2(300);
560
561 BEGIN
562
563 BEGIN
564 IF p_po_header_id IS NOT NULL
565 THEN
566 SELECT agent_id
567 INTO l_buyer_id
568 FROM po_headers_all
569 WHERE po_header_id = p_po_header_id ;
570
571 ELSE
572 SELECT vendor_id
573 INTO l_vendor_id
574 FROM po_vendor_sites_all
575 WHERE vendor_site_id = p_vendor_site_id ;
576
577 OPEN C_buyer ;
578 FETCH C_buyer INTO
579 l_po_header_id
580 , l_buyer_id
581 , l_segment1 ;
582
583 IF C_buyer%NOTFOUND
584 THEN
585 l_buyer_id := NULL;
586 IF g_debug = 1
587 THEN
588 INV_LOG_UTIL.trace
589 ( 'C_buyer NO result ',null,9);
590 END IF;
591 END IF ;
592
593 CLOSE C_buyer ;
594
595 END IF;
596
597 EXCEPTION
598 WHEN NO_DATA_FOUND
599 THEN
600 l_buyer_id := NULL ;
601 END ;
602
603 IF g_debug = 1
604 THEN
605 INV_LOG_UTIL.trace
606 ( 'l_buyer_id => '|| l_buyer_id ,9);
607 END IF;
608
609
610 RETURN( l_buyer_id );
611
612 EXCEPTION
613
614 WHEN OTHERS THEN
615 IF g_debug = 1
616 THEN
617
618 INV_LOG_UTIL.trace
619 ( 'SQLERRM: '|| SQLERRM,9 );
620
621 INV_LOG_UTIL.trace
622 ( 'OTHERS exception ',
623 'get_buyer',9);
624 END IF;
625
626 RAISE ;
627
628 END get_buyer ;
629
630
631
632 --------------------------------------------------------
633 --- validate_consumption_txn
634 ---------------------------------------------------------
635 PROCEDURE validate_consumption_txn
636 ( p_organization_id IN NUMBER
637 , p_inventory_item_id IN NUMBER
638 , p_owning_organization_id IN NUMBER
639 , p_revision IN VARCHAR2
640 , p_po_header_id IN NUMBER
641 , x_error_code OUT NOCOPY VARCHAR2
642 )
643
644 IS
645
646 CURSOR C_VALIDATE_PO
647 IS SELECT
648 Poh.blanket_total_amount
649 FROM
650 po_headers_all poh
651 , po_lines_all pol
652 WHERE poh.po_header_id = pol.po_header_id
653 AND NVL(poh.approved_flag,'Y') = 'Y'
654 AND (TRUNC(NVL(poh.start_date,sysdate -1)) <= TRUNC(sysdate))
655 AND (TRUNC(NVL(poh.end_date,sysdate +1)) >= TRUNC(sysdate))
656 AND (TRUNC(NVL(pol.expiration_date,sysdate )) >= TRUNC(sysdate))
657 AND (NVL(poh.cancel_flag,'N') = 'N'
658 OR NVL(pol.cancel_flag,'N') = 'N')
659 AND (NVL(poh.cancel_flag,'N') = 'N'
660 OR NVL(pol.cancel_flag,'N') = 'N')
661 AND NVL(pol.closed_code,'OPEN') = 'OPEN'
662 AND poh.po_header_id = p_po_header_id
663 AND pol.item_id = p_inventory_item_id
664 AND ( pol.item_revision = p_revision
665 OR pol.item_revision IS NULL );
666
667
668 l_blanket_total_amount NUMBER ;
669
670 BEGIN
671
672 x_error_code := NULL;
673 IF g_debug = 1
674 THEN
675 INV_LOG_UTIL.trace
676 ( '>> IN validate_consumption_txn' ,
677 'INV_CONSIGNED_DIAGNOSTICS_PROC',9);
678
679 INV_LOG_UTIL.trace
680 ( 'p_organization_id => '|| p_organization_id ,
681 'INV_CONSIGNED_DIAGNOSTICS_PROC',9);
682
683 INV_LOG_UTIL.trace
684 ( 'p_inventory_item_id => '|| p_inventory_item_id ,
685 'INV_CONSIGNED_DIAGNOSTICS_PROC',9);
686
687 INV_LOG_UTIL.trace
688 ( 'p_owning_organization_id => '|| p_owning_organization_id ,
689 'INV_CONSIGNED_DIAGNOSTICS_PROC',9);
690
691 INV_LOG_UTIL.trace
692 ( 'p_revision => '|| p_revision ,
693 'INV_CONSIGNED_DIAGNOSTICS_PROC',9);
694 INV_LOG_UTIL.trace
695 ( 'p_po_header_id => '|| p_po_header_id ,
696 'INV_CONSIGNED_DIAGNOSTICS_PROC',9);
697 END IF;
698
699 BEGIN
700 OPEN C_VALIDATE_PO ;
701 FETCH C_VALIDATE_PO
702 INTO
703 l_blanket_total_amount ;
704
705 IF C_VALIDATE_PO%NOTFOUND
706 THEN
707 x_error_code := 'INV_CONS_SUP_INVALID_BPO' ;
708 END IF;
709 CLOSE C_VALIDATE_PO ;
710
711 END ;
712
713 IF x_error_code is NULL
714 THEN
715 IF NVL(l_blanket_total_amount,0) > 0
716 THEN
717 x_error_code := 'INV_CONS_SUP_AMT_AGREED_FAIL' ;
718 ELSE
719 x_error_code := NULL;
720 END IF;
721 END IF;
722
723 l_blanket_total_amount := NULL;
724
725 IF g_debug = 1
726 THEN
727 INV_LOG_UTIL.trace
728 ( 'x_error_code => '|| x_error_code ,
729 'INV_CONSIGNED_DIAGNOSTICS_PROC',9);
730 INV_LOG_UTIL.trace
731 ( 'l_blanket_total_amount => '|| l_blanket_total_amount ,
732 'INV_CONSIGNED_DIAGNOSTICS_PROC',9);
733 INV_LOG_UTIL.trace
734 ( '<< OUT validate_consumption_txn' ,
735 'INV_CONSIGNED_DIAGNOSTICS_PROC',9);
736 END IF;
737
738
739 EXCEPTION
740
741 WHEN OTHERS THEN
742 IF g_debug = 1
743 THEN
744
745 INV_LOG_UTIL.trace
746 ( 'SQLERRM: '|| SQLERRM,9 );
747
748 INV_LOG_UTIL.trace
749 ( 'OTHERS exception ',
750 'get_buyer',9);
751 END IF;
752
753 RAISE ;
754
755 END validate_consumption_txn ;
756
757 ---------------------------------------------------
758 ---Consumption_Advice_diagnostics
759 ----------------------------------------------------
760 PROCEDURE Consumption_Advice_diagnostics
761 (p_error_record_id IN NUMBER )
762 IS
763
764
765 CURSOR
766 C_cad_temp IS
767 SELECT
768 ORGANIZATION_ID
769 , INVENTORY_ITEM_ID
770 , REVISION
771 , OWNING_ORGANIZATION_ID
772 , po_header_id
773 FROM mtl_consigned_diag_temp ;
774
775 l_cad_temp_rec C_cad_temp%ROWTYPE ;
776
777
778 l_po_id NUMBER;
779 l_error_code VARCHAR2(40);
780 l_buyer_id number;
781 l_error_type VARCHAR2(1);
782
783 l_cad_error_code VARCHAR2(40);
784 l_cad_action_code VARCHAR2(40);
785
786 BEGIN
787 IF g_debug = 1
788 THEN
789 INV_LOG_UTIL.trace
790 ( '******************************************** ' ,
791 'INV_CONSIGNED_DIAGNOSTICS_PROC',9);
792 INV_LOG_UTIL.trace
793 ( '>>> IN Consumption_Advice_diagnostics' ,
794 'INV_CONSIGNED_DIAGNOSTICS_PROC',9);
795 INV_LOG_UTIL.trace
796 ( 'p_error_record_id => '|| p_error_record_id ,
797 'Consumption_Advice_diagnostics ',9);
798 END IF;
799
800 DELETE FROM mtl_consigned_diag_temp ;
801
802 IF p_error_record_id IS NULL
803 THEN
804 IF g_debug = 1
805 THEN
806 INV_LOG_UTIL.trace
807 ( 'REGULAR - Before Insert into mtl_consigned_diag_temp' ,
808 'Consumption_Advice_diagnostics',9);
809 END IF;
810
811 INSERT into mtl_consigned_diag_temp (
812 PO_header_id
813 , INVENTORY_ITEM_ID
814 , REVISION
815 , organization_id
816 , OWNING_ORGANIZATION_ID
817 , RECORD_TYPE )
818 SELECT
819 DISTINCT
820 mmt.transaction_source_id
821 , mmt.inventory_item_id
822 , mmt.revision
823 , mmt.organization_id
824 , mmt.owning_organization_id
825 , 2
826 FROM
827 mtl_consumption_transactions mct
828 , mtl_material_transactions mmt
829 WHERE mct.transaction_id = mmt.transaction_id
830 AND mct.consumption_processed_flag <> 'Y'
831 AND mmt.transaction_type_id = 74 ;
832
833 ELSE
834 IF g_debug = 1
835 THEN
836 INV_LOG_UTIL.trace
837 ( 'REVALIDATE - Before Insert into mtl_consigned_diag_temp' ,
838 'Consumption_Advice_diagnostics',9);
839 END IF;
840
841 INSERT into mtl_consigned_diag_temp (
842 PO_header_id
843 , INVENTORY_ITEM_ID
844 , REVISION
845 , organization_id
846 , OWNING_ORGANIZATION_ID
847 , RECORD_TYPE )
848 SELECT
849 PO_header_id
850 , inventory_item_id
851 , revision
852 , organization_id
853 , owning_organization_id
854 , 2
855 FROM
856 MTL_CONSIGNED_DIAG_ERRORS
857 WHERE record_id = p_error_record_id ;
858
859 END IF;
860
861 IF g_debug = 1
862 THEN
863 INV_LOG_UTIL.trace
864 ( 'after Insert into mtl_consigned_diag_temp' ,
865 'Consumption_Advice_diagnostics',9);
866 END IF;
867
868
869 FOR l_cad_temp_rec IN C_cad_temp
870 LOOP
871 IF g_debug = 1
872 THEN
873 INV_LOG_UTIL.trace
874 ( 'ORGANIZATION_ID => '|| l_cad_temp_rec.ORGANIZATION_ID ,null
875 ,9);
876 INV_LOG_UTIL.trace
877 ( 'INVENTORY_ITEM_ID => '|| l_cad_temp_rec.INVENTORY_ITEM_ID
878 ,null
879 ,9);
880 INV_LOG_UTIL.trace
881 ( 'OWNING_ORGANIZATION_ID => '|| l_cad_temp_rec.OWNING_ORGANIZATION_ID
882 ,null
883 ,9);
884
885 INV_LOG_UTIL.trace
886 ( 'po_header_id => '|| l_cad_temp_rec.po_header_id
887 ,null
888 ,9);
889
890 INV_LOG_UTIL.trace
891 ( 'Calling validate_consumption_txn ' ,
892 'Consumption_Advice_diagnostics',9);
893 END IF;
894
895 l_error_code := NULL;
896 l_cad_error_code := NULL;
897 l_cad_action_code := NULL;
898 l_po_id := NULL;
899 l_error_type := NULL ;
900 l_buyer_id := NULL;
901
902 validate_consumption_txn
903 ( p_organization_id => l_cad_temp_rec.ORGANIZATION_ID
904 , p_inventory_item_id => l_cad_temp_rec.INVENTORY_ITEM_ID
905 , p_owning_organization_id => l_cad_temp_rec.OWNING_ORGANIZATION_ID
906 , p_revision => l_cad_temp_rec.revision
907 , p_po_header_id => l_cad_temp_rec.po_header_id
908 , x_error_code => l_error_code
909 );
910
911 IF (g_debug = 1)
912 THEN
913 INV_LOG_UTIL.trace
914 ( 'Out validate_consumption_txn ',
915 'Consumption_Advice_diagnostics'
916 , 9
917 );
918
919 INV_LOG_UTIL.trace
920 ( 'l_error_code => '|| l_error_code
921 ,'Consumption_Advice_diagnostics'
922 , 9);
923 END IF;
924
925 IF l_error_code IS NOT NULL
926 THEN
927 l_buyer_id := get_buyer
928 ( p_po_header_id => l_cad_temp_rec.po_header_id
929 , p_inventory_item_id => l_cad_temp_rec.INVENTORY_ITEM_ID
930 , p_vendor_site_id => l_cad_temp_rec.OWNING_ORGANIZATION_ID
931 , p_organization_id => l_cad_temp_rec.ORGANIZATION_ID
932 , p_revision => l_cad_temp_rec.revision
933 );
934
935 set_error_action_code
936 ( p_error_code => l_error_code
937 , x_mcde_error_code => l_cad_error_code
938 , x_mcde_action_code => l_cad_action_code
939 , x_error_type => l_error_type
940 );
941
942
943 UPDATE mtl_consigned_diag_temp
944 SET error_code = l_error_code
945 , mcde_error_code = l_cad_error_code
946 , mcde_action_code = l_cad_action_code
947 , agent_id = l_buyer_id
948 , error_type = l_error_type
949 WHERE ORGANIZATION_ID = l_cad_temp_rec.ORGANIZATION_ID
950 AND INVENTORY_ITEM_ID = l_cad_temp_rec.INVENTORY_ITEM_ID
951 AND OWNING_ORGANIZATION_ID = l_cad_temp_rec.OWNING_ORGANIZATION_ID
952 AND nvl(revision,-98765432) = nvl(l_cad_temp_rec.revision, -98765432 )
953 AND po_header_id = l_cad_temp_rec.po_header_id ;
954
955 g_revalidate_error_code := l_cad_error_code ;
956
957 l_error_code := NULL;
958 l_cad_error_code := NULL;
959 l_cad_action_code := NULL;
960 l_po_id := NULL;
961 l_error_type := NULL ;
962
963 IF (g_debug = 1)
964 THEN
965 INV_LOG_UTIL.trace
966 ( 'after UPDATE mtl_consigned_diag_temp '
967 ,'Consumption_Advice_diagnostics'
968 , 9
969 );
970 END IF;
971 ELSE
972 g_revalidate_error_code := NULL ;
973 END IF;
974 END LOOP;
975
976 IF (g_debug = 1)
977 THEN
978 INV_LOG_UTIL.trace
979 ( 'after the temp table LOOP',
980 'Consumption_Advice_diagnostics'
981 , 9
982 );
983 END IF;
984
985 l_error_code := NULL;
986 l_cad_error_code := NULL;
987 l_cad_action_code := NULL;
988 l_po_id := NULL;
989 l_error_type := NULL ;
990 l_buyer_id := NULL;
991
992
993 Process_Validated_Record
994 (p_record_type => 2 );
995
996
997
998 IF g_debug = 1
999 THEN
1000 INV_LOG_UTIL.trace
1001 ( '******************************************** ' ,
1002 'INV_CONSIGNED_DIAGNOSTICS_PROC',9);
1003 INV_LOG_UTIL.trace
1004 ( '<<< OUT Consumption_Advice_diagnostics' ,
1005 'INV_CONSIGNED_DIAGNOSTICS_PROC',9);
1006 END IF;
1007
1008 EXCEPTION
1009
1010 WHEN OTHERS THEN
1011 IF g_debug = 1
1012 THEN
1013
1014 INV_LOG_UTIL.trace
1015 ( 'SQLERRM: '|| SQLERRM,9 );
1016
1017 INV_LOG_UTIL.trace
1018 ( 'Error in Consumption_Advice_diagnostics' ,
1019 'INV_CONSIGNED_DIAGNOSTICS_PROC',9);
1020 END IF;
1021
1022 RAISE ;
1023
1024 END Consumption_Advice_diagnostics ;
1025
1026 ------------------------------------------------------------
1027 --- Ownership_transfer_diagnostics
1028 ------------------------------------------------------------
1029 PROCEDURE Ownership_transfer_diagnostics
1030 (p_error_record_id IN NUMBER )
1031 IS
1032
1033 CURSOR
1034 C_moqd_temp IS
1035 SELECT
1036 ORGANIZATION_ID
1037 , INVENTORY_ITEM_ID
1038 , REVISION
1039 , OWNING_ORGANIZATION_ID
1040 FROM mtl_consigned_diag_temp ;
1041
1042 l_moqd_temp_rec C_moqd_temp%ROWTYPE ;
1043
1044
1045 l_po_id NUMBER;
1046 l_return_status VARCHAR2(10);
1047 l_error_code VARCHAR2(40);
1048
1049 l_mcde_error_code VARCHAR2(40);
1050 l_mcde_action_code VARCHAR2(40);
1051
1052 l_po_price NUMBER;
1053 l_account_id NUMBER;
1054 l_rate NUMBER;
1055 l_rate_type VARCHAR2(30);
1056 l_rate_date date;
1057 l_currency_code VARCHAR2(30);
1058 l_msg_count number;
1059 l_msg_data VARCHAR2(300);
1060 l_buyer_id number;
1061 l_error_type VARCHAR2(2);
1062 l_error_po_id number ;
1063 l_primary_uom VARCHAR2(30) ;
1064 l_purchasing_uom VARCHAR2(30) ;
1065
1066 l_primary_uom_code VARCHAR2(30) ;
1067 l_purchasing_uom_code VARCHAR2(30) ;
1068
1069 BEGIN
1070 IF g_debug = 1
1071 THEN
1072 INV_LOG_UTIL.trace
1073 ( '++++++++++++++++++++++++++++++++++++++++' ,
1074 'INV_CONSIGNED_DIAGNOSTICS_PROC',9);
1075 INV_LOG_UTIL.trace
1076 ( '>>> IN Ownership_transfer_diagnostics',
1077 'INV_CONSIGNED_DIAGNOSTICS_PROC',9);
1078 INV_LOG_UTIL.trace
1079 ( 'p_error_record_id => '|| p_error_record_id ,
1080 'Ownership_transfer_diagnostics' ,9);
1081 END IF;
1082
1083
1084 DELETE FROM mtl_consigned_diag_temp ;
1085
1086 IF p_error_record_id is NULL
1087 THEN
1088 IF g_debug = 1
1089 THEN
1090 INV_LOG_UTIL.trace
1091 ( ' REgular - Before Insert into Temp ',
1092 'Ownership_transfer_diagnostics' ,9);
1093 END IF;
1094
1095 INSERT INTO mtl_consigned_diag_temp (
1096 ORGANIZATION_ID
1097 , INVENTORY_ITEM_ID
1098 , REVISION
1099 , OWNING_ORGANIZATION_ID
1100 , RECORD_TYPE
1101 ) SELECT DISTINCT
1102 ORGANIZATION_ID
1103 , INVENTORY_ITEM_ID
1104 , REVISION
1105 , OWNING_ORGANIZATION_ID
1106 , 1
1107 FROM MTL_ONHAND_QUANTITIES_DETAIL
1108 WHERE OWNING_TP_TYPE = 1 ;
1109
1110 ELSE
1111 IF g_debug = 1
1112 THEN
1113 INV_LOG_UTIL.trace
1114 ( ' REvalidate - Before Insert into Temp ',
1115 'Ownership_transfer_diagnostics' ,9);
1116 END IF;
1117
1118 INSERT INTO mtl_consigned_diag_temp (
1119 ORGANIZATION_ID
1120 , INVENTORY_ITEM_ID
1121 , REVISION
1122 , OWNING_ORGANIZATION_ID
1123 , RECORD_TYPE
1124 ) SELECT
1125 ORGANIZATION_ID
1126 , INVENTORY_ITEM_ID
1127 , REVISION
1128 , OWNING_ORGANIZATION_ID
1129 , 1
1130 FROM
1131 MTL_consigned_diag_errors
1132 WHERE record_id = p_error_record_id ;
1133
1134 END IF;
1135
1136 IF g_debug = 1
1137 THEN
1138 INV_LOG_UTIL.trace
1139 ( 'after Insert into MCDET TEMP table ',
1140 'Ownership_transfer_diagnostics' ,9);
1141 END IF;
1142
1143 FOR l_moqd_temp_rec IN C_moqd_temp
1144 LOOP
1145 IF g_debug = 1
1146 THEN
1147
1148 INV_LOG_UTIL.trace
1149 ( 'ORGANIZATION_ID => '|| l_moqd_temp_rec.ORGANIZATION_ID ,null
1150 ,9);
1151 INV_LOG_UTIL.trace
1152 ( 'INVENTORY_ITEM_ID => '|| l_moqd_temp_rec.INVENTORY_ITEM_ID
1153 ,null
1154 ,9);
1155 INV_LOG_UTIL.trace
1156 ( 'OWNING_ORGANIZATION_ID => '|| l_moqd_temp_rec.OWNING_ORGANIZATION_ID
1157 ,null
1158 ,9);
1159 INV_LOG_UTIL.trace
1160 ( 'Calling Process_Financial_Info ' ,
1161 'Ownership_transfer_diagnostics' ,9);
1162 END IF;
1163
1164 l_error_code := NULL;
1165 l_mcde_error_code := NULL;
1166 l_mcde_action_code := NULL;
1167 l_po_id := NULL;
1168 l_error_type := NULL ;
1169 l_po_price := NULL;
1170 l_account_id := NULL;
1171 l_rate := NULL;
1172 l_rate_type := NULL;
1173 l_rate_date := NULL;
1174 l_currency_code := NULL;
1175 l_return_status := NULL;
1176 l_buyer_id := NULL;
1177 l_error_po_id := NULL ;
1178 l_primary_uom := NULL;
1179 l_purchasing_uom := NULL ;
1180 l_primary_uom_code := NULL;
1181 l_purchasing_uom_code := NULL ;
1182
1183
1184 INV_THIRD_PARTY_STOCK_PVT.Process_Financial_Info
1185 ( p_mtl_transaction_id => 99999999
1186 , p_rct_transaction_id => 999999991
1187 , p_transaction_source_type_id => 1
1188 , p_transaction_action_id => 6
1189 , p_inventory_item_id => l_moqd_temp_rec.INVENTORY_ITEM_ID
1190 , p_owning_organization_id => l_moqd_temp_rec.OWNING_ORGANIZATION_ID
1191 , p_xfr_owning_organization_id => l_moqd_temp_rec.ORGANIZATION_ID
1192 , p_organization_id => l_moqd_temp_rec.ORGANIZATION_ID
1193 , p_transaction_quantity => 1
1194 , p_transaction_date => sysdate
1195 , p_transaction_source_id => l_po_id
1196 , p_item_revision => l_moqd_temp_rec.revision
1197 , p_calling_action => 'D'
1198 , x_po_price => l_po_price
1199 , x_account_id => l_account_id
1200 , x_rate => l_rate
1201 , x_rate_type => l_rate_type
1202 , x_rate_date => l_rate_date
1203 , x_currency_code => l_currency_code
1204 , x_msg_count => l_msg_count
1205 , x_msg_data => l_msg_data
1206 , x_return_status => l_return_status
1207 , x_error_code => l_error_code
1208 , x_po_header_id => l_error_po_id
1209 , x_primary_uom => l_primary_uom
1210 , x_purchasing_uom => l_purchasing_uom
1211 );
1212
1213 l_primary_uom_code := get_uom_code( p_uom => l_primary_uom);
1214 l_purchasing_uom_code := get_uom_code( p_uom => l_purchasing_uom);
1215
1216 IF (g_debug = 1)
1217 THEN
1218 INV_LOG_UTIL.trace
1219 ( 'Out of Process_Financial_Info - Diagnostics ',
1220 'Ownership_transfer_diagnostics'
1221 , 9
1222 );
1223 INV_LOG_UTIL.trace
1224 ( 'l_error_code =>' || l_error_code ,'INV_THIRD_PARTY_STOCK_PVT'
1225 , 9);
1226 INV_LOG_UTIL.trace
1227 ( 'po_header_id => '|| l_po_id ,null
1228 , 9);
1229 INV_LOG_UTIL.trace
1230 ( 'l_error_po_id => '|| l_error_po_id, null
1231 , 9);
1232 INV_LOG_UTIL.trace
1233 ( 'l_account_id => '|| l_account_id, null
1234 , 9);
1235 INV_LOG_UTIL.trace
1236 ( 'l_return_status => '|| l_return_status ,'INV_THIRD_PARTY_STOCK_PVT'
1237 , 9);
1238 INV_LOG_UTIL.trace
1239 ( 'l_primary_uom =>' || l_primary_uom ,'INV_THIRD_PARTY_STOCK_PVT'
1240 , 9);
1241
1242 INV_LOG_UTIL.trace
1243 ( 'l_purchasing_uom =>' || l_purchasing_uom ,'INV_THIRD_PARTY_STOCK_PVT'
1244 , 9);
1245 INV_LOG_UTIL.trace
1246 ( 'l_primary_uom_code =>' || l_primary_uom_code ,
1247 'INV_THIRD_PARTY_STOCK_PVT'
1248 , 9);
1249
1250 INV_LOG_UTIL.trace
1251 ( 'l_purchasing_uom_code =>' || l_purchasing_uom_code ,
1252 'INV_THIRD_PARTY_STOCK_PVT'
1253 , 9);
1254 END IF;
1255
1256 IF l_error_code is NULL
1257 THEN
1258 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1259 THEN
1260 l_error_code := l_return_status ;
1261
1262 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS
1263 THEN
1264 g_revalidate_error_code := NULL ;
1265 l_error_code := NULL ;
1266 END IF;
1267
1268 END IF;
1269
1270 IF l_error_code IS NOT NULL
1271 THEN
1272 l_buyer_id := get_buyer
1273 ( p_po_header_id => l_error_po_id
1274 , p_inventory_item_id => l_moqd_temp_rec.INVENTORY_ITEM_ID
1275 , p_vendor_site_id => l_moqd_temp_rec.OWNING_ORGANIZATION_ID
1276 , p_organization_id => l_moqd_temp_rec.ORGANIZATION_ID
1277 , p_revision => l_moqd_temp_rec.revision
1278 );
1279
1280 set_error_action_code
1281 ( p_error_code => l_error_code
1282 , x_mcde_error_code => l_mcde_error_code
1283 , x_mcde_action_code => l_mcde_action_code
1284 , x_error_type => l_error_type
1285 );
1286
1287
1288 UPDATE mtl_consigned_diag_temp
1289 SET error_code = l_error_code
1290 , mcde_error_code = l_mcde_error_code
1291 , mcde_action_code = l_mcde_action_code
1292 , agent_id = l_buyer_id
1293 , po_header_id = l_error_po_id
1294 , error_type = l_error_type
1295 , primary_uom = l_primary_uom_code
1296 , purchasing_uom = l_purchasing_uom_code
1297 WHERE ORGANIZATION_ID = l_moqd_temp_rec.ORGANIZATION_ID
1298 AND INVENTORY_ITEM_ID = l_moqd_temp_rec.INVENTORY_ITEM_ID
1299 AND OWNING_ORGANIZATION_ID = l_moqd_temp_rec.OWNING_ORGANIZATION_ID
1300 AND nvl(revision,-98765439) = nvl(l_moqd_temp_rec.revision, -98765439 );
1301
1302 g_revalidate_error_code := l_mcde_error_code ;
1303
1304 l_error_code := NULL;
1305 l_mcde_error_code := NULL;
1306 l_mcde_action_code := NULL;
1307 l_po_id := NULL;
1308 l_error_po_id := NULL ;
1309 l_error_type := NULL ;
1310 l_primary_uom := NULL;
1311 l_purchasing_uom := NULL ;
1312 l_primary_uom_code := NULL;
1313 l_purchasing_uom_code := NULL ;
1314
1315 IF (g_debug = 1)
1316 THEN
1317 INV_LOG_UTIL.trace
1318 ( 'after UPDATE mtl_consigned_diag_temp '
1319 ,'Ownership_transfer_diagnostics'
1320 , 9
1321 );
1322 END IF;
1323 END IF;
1324 END LOOP;
1325
1326 IF (g_debug = 1)
1327 THEN
1328 INV_LOG_UTIL.trace
1329 ( 'after the temp table LOOP',
1330 'Ownership_transfer_diagnostics'
1331 , 9
1332 );
1333 END IF;
1334
1335 l_error_code := NULL;
1336 l_primary_uom := NULL;
1337 l_purchasing_uom := NULL ;
1338 l_primary_uom_code := NULL;
1339 l_purchasing_uom_code := NULL ;
1340 l_mcde_error_code := NULL;
1341 l_mcde_action_code := NULL;
1342 l_po_id := NULL;
1343 l_error_po_id := NULL ;
1344 l_error_type := NULL ;
1345 l_po_price := NULL;
1346 l_account_id := NULL;
1347 l_rate := NULL;
1348 l_rate_type := NULL;
1349 l_rate_date := NULL;
1350 l_currency_code := NULL;
1351 l_return_status := NULL;
1352 l_buyer_id := NULL;
1353
1354
1355 Process_Validated_Record
1356 (p_record_type => 1 );
1357
1358
1359 IF g_debug = 1
1360 THEN
1361 INV_LOG_UTIL.trace
1362 ( '++++++++++++++++++++++++++++++++++++++++' ,
1363 'INV_CONSIGNED_DIAGNOSTICS_PROC',9);
1364 INV_LOG_UTIL.trace
1365 ( '<<< OUT Ownership_transfer_diagnostics',
1366 'INV_CONSIGNED_DIAGNOSTICS_PROC',9);
1367 END IF;
1368
1369
1370
1371 EXCEPTION
1372
1373 WHEN OTHERS THEN
1374 IF g_debug = 1
1375 THEN
1376
1377 INV_LOG_UTIL.trace
1378 ( 'SQLERRM: '|| SQLERRM,9 );
1379
1380 INV_LOG_UTIL.trace
1381 ( 'Error in Ownership_transfer_diagnostics',
1382 'INV_CONSIGNED_DIAGNOSTICS_PROC',9);
1383 END IF;
1384
1385 RAISE ;
1386
1387 END Ownership_transfer_diagnostics ;
1388
1389
1390
1391 ---------------------------------------------------------
1392 --Revalidate Record
1393 -------------------------------------------------------------
1394 PROCEDURE Revalidate_error_record
1395 ( p_error_record_id IN NUMBER
1396 , x_result_out OUT NOCOPY VARCHAR2
1397 )
1398 IS
1399
1400 l_record_type NUMBER ;
1401 l_request_id NUMBER;
1402 l_error_code VARCHAR2(40);
1403
1404 BEGIN
1405
1406 IF g_debug = 1
1407 THEN
1408 INV_LOG_UTIL.trace
1409 ( '>> Revalidate_error_record' ,'INV_CONSIGNED_DIAGNOSTICS_PROC',9);
1410 INV_LOG_UTIL.trace
1411 ( 'p_error_record_id => '|| p_error_record_id,null,9);
1412 END IF;
1413
1414 g_revalidate_error_code := NULL ;
1415
1416 BEGIN
1417 SELECT
1418 record_type
1419 , request_id
1420 , error_code
1421 INTO
1422 l_record_type
1423 , l_request_id
1424 , l_error_code
1425 FROM
1426 mtl_consigned_diag_errors
1427 WHERE record_id = p_error_record_id ;
1428
1429 EXCEPTION
1430 WHEN NO_DATA_FOUND
1431 THEN
1432 x_result_out := 'FAIL' ;
1433 l_record_type := NULL;
1434 l_request_id := NULL;
1435
1436 IF g_debug = 1
1437 THEN
1438 INV_LOG_UTIL.trace
1439 ( '<< out Revalidate_error_record'
1440 ,'INV_CONSIGNED_DIAGNOSTICS_PROC',9);
1441 END IF;
1442 END ;
1443
1444 IF x_result_out IS NULL
1445 THEN
1446 g_request_id := l_request_id ;
1447
1448 IF g_debug = 1
1449 THEN
1450 INV_LOG_UTIL.trace
1451 ( 'l_record_type => '|| l_record_type,null,9) ;
1452 INV_LOG_UTIL.trace
1453 ( 'l_request_id => '|| l_request_id,null,9);
1454 INV_LOG_UTIL.trace
1455 ( 'g_request_id => '|| g_request_id,null,9);
1456 END IF;
1457
1458
1459 IF l_record_type = 1
1460 THEN
1461 INV_CONSIGNED_DIAGNOSTICS_PROC.Ownership_transfer_diagnostics
1462 (p_error_record_id => p_error_record_id );
1463
1464 ELSIF l_record_type = 2
1465 THEN
1466 INV_CONSIGNED_DIAGNOSTICS_PROC.Consumption_Advice_diagnostics
1467 (p_error_record_id => p_error_record_id);
1468 END IF;
1469
1470 IF g_debug = 1
1471 THEN
1472 INV_LOG_UTIL.trace
1473 ( 'g_revalidate_error_code => '|| g_revalidate_error_code,null,9);
1474 END IF;
1475
1476 BEGIN
1477 IF g_revalidate_error_code IS NULL
1478 THEN
1479 x_result_out := 'PASS' ;
1480 DELETE from mtl_consigned_diag_errors
1481 WHERE record_id = p_error_record_id ;
1482 ELSE
1483 x_result_out := 'FAIL' ;
1484 IF g_revalidate_error_code <> l_error_code
1485 THEN
1486 DELETE from mtl_consigned_diag_errors
1487 WHERE record_id = p_error_record_id ;
1488
1489 END IF;
1490 INV_CONSIGNED_DIAGNOSTICS_PROC.Update_Consumption_Date ;
1491 END IF;
1492 END ;
1493
1494 COMMIT;
1495
1496 IF g_debug = 1
1497 THEN
1498 INV_LOG_UTIL.trace
1499 ( 'Out of validation ',null,9);
1500 END IF;
1501 END IF;
1502
1503 g_revalidate_error_code := NULL;
1504
1505 IF g_debug = 1
1506 THEN
1507 INV_LOG_UTIL.trace
1508 ( 'x_result_out => '|| x_result_out ,
1509 'Revalidate_error_record',9);
1510 INV_LOG_UTIL.trace
1511 ( '<< out Revalidate_error_record' ,'INV_CONSIGNED_DIAGNOSTICS_PROC',9);
1512 END IF;
1513
1514
1515
1516 EXCEPTION
1517
1518 WHEN OTHERS THEN
1519 INV_LOG_UTIL.trace
1520 ( 'SQLERRM: '|| SQLERRM,9 );
1521
1522 IF g_debug = 1
1523 THEN
1524 INV_LOG_UTIL.trace
1525 ( 'OTHERS exception ',
1526 'Revalidate_error_record',9);
1527 END IF;
1528
1529 RAISE ;
1530
1531 END Revalidate_error_record ;
1532
1533 /*========================================================================
1534 -- PROCEDURE : Consigned_diagnostics
1535 -- PARAMETERS:
1536 --
1537 -- p_send_notification IN VARCHAR2
1538 -- to indicate if workflow notifications needs to be
1539 -- send to the Buyer
1540 -- p_notification_resend_days IN NUMBER
1541 -- to indicate to send notification only if
1542 -- las_notification sent date for the same combination
1543 -- of org/item/supplier/site/error + p_notification_resend_days
1544 -- >= sysdate
1545 --
1546 -- COMMENT : This is the main concurrent program procedure
1547 -- that is directly invoked by the conc program
1548 -- " INV Consigned Inventory Diagnostics"
1549 -- This program does not accept any specific ORG
1550 -- as Input as the logic is to validate all
1551 -- eligible consigned transactions
1552 -- 1) Ownership transfer to regulat stock and
1553 -- 2) Consumption Advice pre-validation
1554 -- and insert into a new errors table
1555 -- The results of the concurrent program can be
1556 -- viewed from a separate HTML UI under INV
1557 --=======================================================================*/
1558
1559 --=======================================================================*/
1560 PROCEDURE Consigned_diagnostics
1561 ( p_send_notification IN VARCHAR2
1562 , p_notification_resend_days IN NUMBER
1563 )
1564 IS
1565
1566 l_return_status VARCHAR2(1) ;
1567 l_msg_data VARCHAR2(3000) ;
1568 l_msg_count NUMBER ;
1569 BEGIN
1570
1571 l_return_status := NULL;
1572 l_msg_data := NULL;
1573 l_msg_count := NULL;
1574
1575 IF g_debug = 1
1576 THEN
1577 INV_LOG_UTIL.trace
1578 ( '>> INVRCIDB: IN Consigned_diagnostics ',
1579 'INV_CONSIGNED_DIAGNOSTICS_PROC' , 9);
1580
1581 END IF;
1582
1583 INV_CONSIGNED_DIAGNOSTICS_PROC.Ownership_transfer_diagnostics
1584 (p_error_record_id => NULL );
1585
1586 INV_CONSIGNED_DIAGNOSTICS_PROC.Consumption_Advice_diagnostics
1587 (p_error_record_id => NULL);
1588
1589
1590 INV_CONSIGNED_DIAGNOSTICS_PROC.Purge_diagnostics_passed_rec ;
1591 INV_CONSIGNED_DIAGNOSTICS_PROC.Update_Consumption_Date ;
1592
1593 COMMIT;
1594
1595 IF g_debug = 1
1596 THEN
1597 INV_LOG_UTIL.trace
1598 ( 'Completed the conc program diagnostics ' ,
1599 'INV_CONSIGNED_DIAGNOSTICS_PROC' , 9);
1600 INV_LOG_UTIL.trace
1601 ( 'p_send_notification => '|| p_send_notification ,
1602 'INV_CONSIGNED_DIAGNOSTICS_PROC' , 9);
1603
1604 INV_LOG_UTIL.trace
1605 ( 'p_notification_resend_days => '|| p_notification_resend_days ,
1606 'INV_CONSIGNED_DIAGNOSTICS_PROC' , 9);
1607 END IF;
1608
1609 IF TO_NUMBER(p_send_notification) = 1
1610 THEN
1611 INV_CONSIGN_NOTIF_UTL.Send_Notification
1612 ( p_api_version => 1.0
1613 , p_init_msg_list => FND_API.G_FALSE
1614 , p_commit => FND_API.G_TRUE
1615 , x_return_status => l_return_status
1616 , x_msg_count => l_msg_count
1617 , x_msg_data => l_msg_data
1618 , p_notification_resend_days =>
1619 NVL(p_notification_resend_days ,0)
1620 );
1621
1622 IF g_debug = 1
1623 THEN
1624 INV_LOG_UTIL.trace
1625 ( 'Completed the Notification process' ,
1626 'INV_CONSIGNED_DIAGNOSTICS_PROC' , 9);
1627
1628 INV_LOG_UTIL.trace
1629 ( 'l_return_status => '|| l_return_status ,
1630 'INV_CONSIGNED_DIAGNOSTICS_PROC' , 9);
1631
1632 INV_LOG_UTIL.trace
1633 ( 'x_msg_count => '|| l_msg_count ,
1634 'INV_CONSIGNED_DIAGNOSTICS_PROC' , 9);
1635
1636 INV_LOG_UTIL.trace( 'Error=> '||
1637 substrb(FND_MSG_PUB.Get(p_encoded =>
1638 FND_API.G_FALSE),1,500),
1639 9 );
1640 END IF;
1641 END IF;
1642
1643 IF g_debug = 1
1644 THEN
1645 INV_LOG_UTIL.trace
1646 ( '<< INVRCIDB: OUT Consigned_diagnostics',
1647 'INV_CONSIGNED_DIAGNOSTICS_PROC' ,9);
1648
1649 END IF;
1650
1651
1652 EXCEPTION
1653
1654 WHEN OTHERS THEN
1655 INV_LOG_UTIL.trace
1656 ( 'SQLERRM: '|| SQLERRM,9 );
1657
1658 INV_LOG_UTIL.trace
1659 ( 'Error in Consigned_diagnostics' ,'INVRCIDB', 9);
1660
1661 INV_LOG_UTIL.trace( 'Error=> '||
1662 substrb(FND_MSG_PUB.Get(p_encoded =>
1663 FND_API.G_FALSE),1,500),9 );
1664
1665 rollback;
1666
1667 RAISE ;
1668
1669 END Consigned_diagnostics ;
1670
1671 END INV_CONSIGNED_DIAGNOSTICS_PROC ;