1 PACKAGE BODY PJM_UNIT_EFF AS
2 /* $Header: PJMUEFFB.pls 120.1.12010000.2 2008/09/17 10:32:53 ybabulal ship $ */
3 -- ---------------------------------------------------------------------
4 -- Global Variables
5 -- ---------------------------------------------------------------------
6 G_Unit_Number PJM_UNIT_NUMBERS.UNIT_NUMBER%TYPE;
7 G_Enabled VARCHAR2(1) := NULL;
8 G_Org_ID NUMBER := NULL;
9 G_Item_ID NUMBER := NULL;
10 G_Unit_Eff_Item VARCHAR2(1) := NULL;
11 G_WIP_Entity_ID NUMBER := NULL;
12 G_WIP_Org_ID NUMBER := NULL;
13 G_WIP_Unit_Num PJM_UNIT_NUMBERS.UNIT_NUMBER%TYPE := NULL;
14 G_RCV_Txn_ID NUMBER := NULL;
15 G_RCV_Unit_Num PJM_UNIT_NUMBERS.UNIT_NUMBER%TYPE := NULL;
16 G_RMA_Txn_ID NUMBER := NULL;
17 G_RMA_Unit_Num PJM_UNIT_NUMBERS.UNIT_NUMBER%TYPE := NULL;
18 G_OE_Line_ID NUMBER := NULL;
19 G_OE_Unit_Num PJM_UNIT_NUMBERS.UNIT_NUMBER%TYPE := NULL;
20
21 -- ---------------------------------------------------------------------
22 -- Private Functions / Procedures
23 -- ---------------------------------------------------------------------
24
25 --
26 -- Name : Item_Serial_Control
27 -- Pre-reqs : None
28 -- Function : This function checks the serial control for
29 -- the item
30 --
31 --
32 -- Parameters :
33 -- IN : X_item_id NUMBER
34 -- X_organization_id NUMBER
35 --
36 -- OUT : None
37 --
38 -- Returns : Serial Number Control Code
39 -- 1 - No serial number control
40 -- 2 - Predefined serial numbers
41 -- 5 - Dynamic entry at inventory receipt
42 -- 6 - Dynamic entry at sales order issue
43 --
44 FUNCTION Item_Serial_Control
45 ( X_item_id IN NUMBER
46 , X_organization_id IN NUMBER
47 ) RETURN NUMBER IS
48 L_ser_control NUMBER;
49 BEGIN
50
51 if ( X_item_id is NULL ) then
52 return ( NULL );
53 end if;
54
55 SELECT serial_number_control_code
56 INTO L_ser_control
57 FROM mtl_system_items
58 WHERE inventory_item_id = X_item_id
59 AND organization_id = X_organization_id;
60
61 return ( L_ser_control );
62
63 EXCEPTION
64 WHEN NO_DATA_FOUND THEN
65 RETURN ( NULL );
66 WHEN OTHERS THEN
67 RETURN ( NULL );
68
69 END Item_Serial_Control;
70
71
72 -- ---------------------------------------------------------------------
73 -- Public Functions / Procedures
74 -- ---------------------------------------------------------------------
75
76 --
77 -- Name : Enabled
78 -- Pre-reqs : None
79 -- Function : This function returns a Y/N indicator whether
80 -- Model/Unit effectivity has been enabled or not
81 --
82 --
83 -- Parameters :
84 -- IN : None
85 --
86 -- Returns : Y/N
87 --
88 FUNCTION Enabled
89 RETURN VARCHAR2 IS
90
91 BEGIN
92
93 --
94 -- The result is cached into global variable to speed up repeated
95 -- Initially the cache is NULL. Once the cache is populated, the
96 -- cached result will be used instead of hitting the DB again.
97 --
98 if ( G_Enabled IS NULL ) then
99 --
100 -- A Law 06/07/2002
101 --
102 -- Check is now based on whether PJM is implemented (existence
103 -- of rows in pjm_org_parameters), not whether PJM is installed
104 --
105 if ( PJM_INSTALL.Check_Implementation_Status ) then
106 G_Enabled := 'Y';
107 else
108 G_Enabled := 'N';
109 end if;
110 end if;
111 RETURN ( G_Enabled );
112
113 END Enabled;
114
115
116 --
117 -- Name : Allow_Cross_UnitNum_Issues
118 -- Pre-reqs : None
119 -- Function : This function returns a Y/N indicator whether
120 -- Cross-Unit Number WIP Issues are allowed
121 --
122 --
123 -- Parameters :
124 -- IN : None
125 --
126 -- Returns : Y/N
127 --
128 FUNCTION Allow_Cross_UnitNum_Issues
129 ( X_organization_id IN NUMBER
130 ) RETURN VARCHAR2 IS
131 L_allowed VARCHAR2(1);
132 L_org_id number;
133 BEGIN
134
135 if ( X_organization_id is null ) then
136 L_org_id := Fnd_Profile.Value_WNPS('MFG_ORGANIZATION_ID');
137 else
138 L_org_id := X_organization_id;
139 end if;
140
141 SELECT nvl(allow_cross_unitnum_issues,'N')
142 INTO L_allowed
143 FROM pjm_org_parameters
144 WHERE organization_id = L_org_id;
145
146 if (L_allowed = 'Y') then
147 RETURN( 'Y' );
148 else
149 RETURN( 'N' );
150 end if;
151
152 EXCEPTION
153 WHEN NO_DATA_FOUND THEN
154 RETURN( 'N' );
155 WHEN OTHERS THEN
156 RETURN( 'N' );
157
158 END Allow_Cross_UnitNum_Issues;
159
160
161 --
162 -- Name : Unit_Effective_Item
163 -- Pre-reqs : None
164 -- Function : This function checks the effectivity control for
165 -- the item
166 --
167 --
168 -- Parameters :
169 -- IN : X_item_id NUMBER
170 -- X_organization_id NUMBER
171 --
172 -- OUT : None
173 --
174 -- Returns : Y/N
175 --
176 FUNCTION Unit_Effective_Item
177 ( X_item_id IN NUMBER
178 , X_organization_id IN NUMBER
179 ) RETURN VARCHAR2 IS
180
181 l_item number; -- add for R12 EAM request, bug#4521664
182 BEGIN
183
184 if ( X_item_id is NULL ) then
185 return ( NULL );
186 end if;
187
188 --
189 -- The input and output are all cached into global variables to
190 -- speed up repeated lookups. If the input parameters of the
191 -- current lookup matches the input parameters of the previous
192 -- lookup, the cached result will be used instead of hitting the DB
193 -- again.
194 --
195 if ( G_Unit_Eff_Item IS NULL
196 OR G_Item_ID <> X_Item_ID
197 OR G_Org_ID <> X_organization_ID ) then
198 --
199 -- No cache output or input parameters have changed; recache
200 --
201 G_Org_ID := X_Organization_ID;
202 G_Item_ID := X_Item_ID;
203
204 /* bug 4521664 for EAM - Ignore Unit effectivity Attribute for Asset Group
205 Items (eam_item_type = 2 in MSI table) */
206
207 select nvl(eam_item_type, -2)
208 into l_item
209 from mtl_system_items_b
210 where inventory_item_id =X_ITEM_ID
211 and organization_id = X_organization_id;
212
213 if l_item = 1 then
214 G_Unit_Eff_Item := 'N'; --Always return N for Asset Group Item
215 else
216 /* end of change for EAM, process all other type of items */
217 SELECT decode(effectivity_control , 2 , 'Y' , 'N')
218 INTO G_Unit_Eff_Item
219 FROM mtl_system_items
220 WHERE inventory_item_id = X_item_id
221 AND organization_id = X_organization_id;
222 end if;
223 end if;
224
225 RETURN ( G_Unit_Eff_Item );
226
227 EXCEPTION
228 WHEN NO_DATA_FOUND THEN
229 G_Unit_Eff_Item := 'N';
230 RETURN ( G_Unit_Eff_Item );
231 WHEN OTHERS THEN
232 RETURN ( NULL );
233
234 END Unit_Effective_Item;
235
236
237 --
238 -- Name : Set_Unit_Number
239 -- Pre-reqs : None
240 -- Function : This procedure sets the global variable
241 -- G_Unit_Number
242 --
243 --
244 -- Parameters :
245 -- IN : X_Unit_Number NUMBER
246 --
247 -- Returns : None
248 --
249 PROCEDURE Set_Unit_Number
250 ( X_Unit_Number IN VARCHAR2
251 ) IS
252 BEGIN
253
254 G_Unit_Number := X_Unit_Number;
255
256 END Set_Unit_Number;
257
258
259 --
260 -- Name : Current_Unit_Number
261 -- Pre-reqs : None
262 -- Function : This procedure gets the value in global variable
263 -- G_Unit_Number
264 --
265 --
266 -- Parameters :
267 -- IN : None
268 --
269 -- Returns : VARCHAR2
270 --
271 FUNCTION Current_Unit_Number
272 RETURN VARCHAR2 IS
273 BEGIN
274
275 return ( G_Unit_Number );
276
277 END Current_Unit_Number;
278
279
280 --
281 -- Name : Prev_Unit_Number
282 -- Pre-reqs : None
283 -- Function : This function returns the previous unit number in
284 -- ascending order for the same end item
285 --
286 --
287 -- Parameters :
288 -- IN : X_Unit_Number NUMBER
289 --
290 -- Returns : VARCHAR2
291 --
292 FUNCTION Prev_Unit_Number
293 ( X_Unit_Number IN VARCHAR2
294 ) RETURN VARCHAR2 IS
295 L_unit_number VARCHAR2(30) := NULL;
296
297 CURSOR c IS
298 SELECT N2.unit_number
299 FROM pjm_unit_numbers N1
300 , pjm_unit_numbers N2
301 WHERE N1.unit_number = X_unit_number
302 AND N2.end_item_id = N1.end_item_id
303 AND N2.master_organization_id = N1.master_organization_id
304 AND N2.unit_number < N1.unit_number
305 ORDER BY N2.unit_number desc;
306
307 BEGIN
308
309 OPEN c;
310 FETCH c INTO L_unit_number;
311 RETURN ( L_unit_number );
312
313 EXCEPTION
314 WHEN OTHERS THEN
315 RAISE;
316 END Prev_Unit_Number;
317
318
319 --
320 -- Name : Next_Unit_Number
321 -- Pre-reqs : None
322 -- Function : This function returns the next unit number in
323 -- ascending order for the same end item
324 --
325 --
326 -- Parameters :
327 -- IN : X_Unit_Number NUMBER
328 --
329 -- Returns : VARCHAR2
330 --
331 FUNCTION Next_Unit_Number
332 ( X_Unit_Number IN VARCHAR2
333 ) RETURN VARCHAR2 IS
334 L_unit_number VARCHAR2(30) := NULL;
335
336 CURSOR c IS
337 SELECT N2.unit_number
338 FROM pjm_unit_numbers N1
339 , pjm_unit_numbers N2
340 WHERE N1.unit_number = X_unit_number
341 AND N2.end_item_id = N1.end_item_id
342 AND N2.master_organization_id = N1.master_organization_id
343 AND N2.unit_number > N1.unit_number
344 ORDER BY N2.unit_number asc;
345
346 BEGIN
347
348 OPEN c;
349 FETCH c INTO L_unit_number;
350 RETURN ( L_unit_number );
351
352 EXCEPTION
353 WHEN OTHERS THEN
354 RAISE;
355 END Next_Unit_Number;
356
357
358 --
359 -- Name : WIP_Unit_Number
360 -- Pre-reqs : None
361 -- Function : This function returns the unit number on a discrete
362 -- job or flow schedule
363 --
364 --
365 -- Parameters :
366 -- IN : X_wip_entity_id NUMBER
367 -- X_organization_id NUMBER
368 --
369 -- OUT : None
370 --
371 -- Returns : VARCHAR2
372 --
373 FUNCTION WIP_Unit_Number
374 ( X_wip_entity_id IN NUMBER
375 , X_organization_id IN NUMBER
376 ) RETURN VARCHAR2 IS
377
378 BEGIN
379
380 SELECT DECODE(e.entity_type, 1, dj.end_item_unit_number,
381 4, fs.end_item_unit_number)
382 INTO G_WIP_Unit_Num
383 FROM wip_flow_schedules fs
384 , wip_discrete_jobs dj
385 , wip_entities e
386 WHERE e.wip_entity_id = X_wip_entity_id
387 AND e.organization_id = X_organization_id
388 AND fs.organization_id (+) = e.organization_id
389 AND fs.wip_entity_id (+) = e.wip_entity_id
390 AND dj.organization_id (+) = e.organization_id
391 AND dj.wip_entity_id (+) = e.wip_entity_id;
392
393 G_Wip_Entity_ID := X_wip_entity_id;
394 G_WIP_Org_ID := X_organization_id;
395
396 RETURN ( G_WIP_Unit_Num );
397
398 EXCEPTION
399 WHEN OTHERS THEN
400 RETURN ( NULL );
401
402 END WIP_Unit_Number;
403
404
405 FUNCTION WIP_Unit_Number_Cached
406 ( X_wip_entity_id IN NUMBER
407 , X_organization_id IN NUMBER
408 ) RETURN VARCHAR2 IS
409
410 BEGIN
411
412 IF ( G_WIP_Unit_Num IS NULL
413 OR G_WIP_Entity_ID <> X_wip_entity_id
414 OR G_WIP_Org_ID <> X_organization_id ) THEN
415
416 SELECT DECODE(e.entity_type, 1, dj.end_item_unit_number,
417 4, fs.end_item_unit_number)
418 INTO G_WIP_Unit_Num
419 FROM wip_flow_schedules fs
420 , wip_discrete_jobs dj
421 , wip_entities e
422 WHERE e.wip_entity_id = X_wip_entity_id
423 AND e.organization_id = X_organization_id
424 AND fs.organization_id (+) = e.organization_id
425 AND fs.wip_entity_id (+) = e.wip_entity_id
426 AND dj.organization_id (+) = e.organization_id
427 AND dj.wip_entity_id (+) = e.wip_entity_id;
428
429 G_Wip_Entity_ID := X_wip_entity_id;
430 G_WIP_Org_ID := X_organization_id;
431
432 END IF;
433
434 RETURN ( G_WIP_Unit_Num );
435
436 EXCEPTION
437 WHEN OTHERS THEN
438 RETURN ( NULL );
439
440 END WIP_Unit_Number_Cached;
441
442
443 --
444 -- Name : RCV_Unit_Number
445 -- Pre-reqs : None
446 -- Function : This function returns the unit number on a PO
447 -- distribution or Internal Req distribution based on the
448 -- receiving transaction
449 --
450 --
451 -- Parameters :
452 -- IN : X_rcv_transaction_id NUMBER
453 --
454 -- OUT : None
455 --
456 -- Returns : VARCHAR2
457 --
458 FUNCTION RCV_Unit_Number
459 ( X_rcv_transaction_id IN NUMBER
460 ) RETURN VARCHAR2 IS
461
462 L_po_distribution_id NUMBER;
463 L_req_line_id NUMBER;
464
465 BEGIN
466
467 IF ( G_RCV_Unit_Num IS NULL
468 OR G_RCV_Txn_ID <> X_rcv_transaction_id ) THEN
469
470 SELECT po_distribution_id
471 , requisition_line_id
472 INTO L_po_distribution_id
473 , L_req_line_id
474 FROM rcv_transactions
475 WHERE transaction_id = X_rcv_transaction_id;
476
477 IF L_po_distribution_id IS NOT NULL THEN
478
479 SELECT end_item_unit_number
480 INTO G_RCV_Unit_Num
481 FROM po_distributions_all
482 WHERE po_distribution_id = L_po_distribution_id;
483
484 ELSIF L_req_line_id IS NOT NULL THEN
485
486 SELECT end_item_unit_number
487 INTO G_RCV_Unit_Num
488 FROM po_req_distributions_all
489 WHERE requisition_line_id = L_req_line_id;
490
491 ELSE
492
493 G_RCV_Unit_Num := null;
494
495 END IF;
496
497 G_RCV_Txn_ID := X_rcv_transaction_id;
498
499 END IF;
500
501 RETURN ( G_RCV_Unit_Num );
502
503 EXCEPTION
504 WHEN OTHERS THEN
505 RETURN ( NULL );
506
507 END RCV_Unit_Number;
508
509
510 --
511 -- Name : OE_Line_Unit_Number
512 -- Pre-reqs : None
513 -- Function : This function returns the unit number on a sales order
514 -- line
515 --
516 --
517 -- Parameters :
518 -- IN : X_so_line_id NUMBER
519 --
520 -- OUT : None
521 --
522 -- Returns : VARCHAR2
523 --
524 FUNCTION OE_Line_Unit_Number
525 ( X_so_line_id IN NUMBER
526 ) RETURN VARCHAR2 IS
527
528 BEGIN
529
530 SELECT end_item_unit_number
531 INTO G_OE_Unit_Num
532 FROM oe_order_lines_all
533 WHERE line_id = X_so_line_id;
534
535 G_OE_Line_ID := X_so_line_id;
536
537 RETURN( G_OE_Unit_Num );
538
539 EXCEPTION
540 WHEN OTHERS THEN
541 RETURN ( NULL );
542
543 END OE_Line_Unit_Number;
544
545
546 FUNCTION OE_Line_Unit_Number_Cached
547 ( X_so_line_id IN NUMBER
548 ) RETURN VARCHAR2 IS
549
550 BEGIN
551
552 IF ( G_OE_Unit_Num IS NULL
553 OR G_OE_Line_ID <> X_so_line_id ) THEN
554
555 SELECT end_item_unit_number
556 INTO G_OE_Unit_Num
557 FROM oe_order_lines_all
558 WHERE line_id = X_so_line_id;
559
560 G_OE_Line_ID := X_so_line_id;
561
562 END IF;
563
564 RETURN( G_OE_Unit_Num );
565
566 EXCEPTION
567 WHEN OTHERS THEN
568 RETURN ( NULL );
569
570 END OE_Line_Unit_Number_Cached;
571
572
573 --
574 -- Name : RMA_Rcpt_Unit_Number
575 -- Pre-reqs : None
576 -- Function : This function returns the unit number on a RMA
577 -- order line based on the receiving transaction
578 --
579 --
580 -- Parameters :
581 -- IN : X_rcv_transaction_id NUMBER
582 --
583 -- OUT : None
584 --
585 -- Returns : VARCHAR2
586 --
587 FUNCTION RMA_Rcpt_Unit_Number
588 ( X_rcv_transaction_id IN NUMBER
589 ) RETURN VARCHAR2 IS
590 L_oe_order_line_id NUMBER;
591 BEGIN
592
593 IF ( G_RMA_Unit_Num IS NULL
594 OR G_RMA_Txn_ID <> X_rcv_transaction_id ) THEN
595
596 SELECT oe_order_line_id
597 INTO L_oe_order_line_id
598 FROM rcv_transactions
599 WHERE transaction_id = X_rcv_transaction_id;
600
601 IF L_oe_order_line_id IS NOT NULL THEN
602 G_RMA_Unit_Num := OE_Line_Unit_Number( L_oe_order_line_id );
603 ELSE
604 G_RMA_Unit_Num := NULL;
605 END IF;
606
607 G_RMA_Txn_ID := X_rcv_transaction_id;
608
609 END IF;
610
611 RETURN ( G_RMA_Unit_Num );
612
613 EXCEPTION
614 WHEN OTHERS THEN
615 RETURN ( NULL );
616
617 END RMA_Rcpt_Unit_Number;
618
619
620 --
621 -- Name : Validate_Serial
622 -- Pre-reqs : None
623 -- Function : This function validates the transaction serial numbers
624 -- against the unit number on the transaction entity
625 -- (e.g. WIP job)
626 --
627 --
628 -- Parameters :
629 -- IN : X_trx_source_type_id NUMBER
630 -- X_trx_action_id NUMBER
631 -- X_item_id NUMBER
632 -- X_organization_id NUMBER
633 -- X_serial_number VARCHAR2
634 -- X_unit_number VARCHAR2
635 --
636 -- OUT : X_error_code VARCHAR2
637 --
638 -- Returns : Boolean
639 --
640 -- Algorithm :
641 --
642 -- If Model/Unit Effectivity is not enabled then
643 -- Return "TRUE"
644 --
645 -- If transaction type is not
646 -- "WIP Component Issue" or
647 -- "WIP Assembly Return" or
648 -- "RMA Receipt" or
649 -- "Direct Interorg transfer" or
650 -- "Sales order staging transfer" or
651 -- "Intransit shipment" then
652 -- Return "TRUE"
653 --
654 -- If transaction type is "RMA Receipt" and item serial control is
655 -- Dynamic Entry at Receipt
656 -- Return "TRUE"
657 --
658 -- Get Effectivity Control attribute from item
659 --
660 -- Fetch the Unit Number from the serial number
661 --
662 -- If item is date effective and serial has unit number link then
663 -- Get error message (UEFF-Item Not Unit Effective)
664 -- populate ERROR_CODE column
665 -- Return "FALSE"
666 --
667 -- If item is unit effective and upstream unit number is null then
668 -- Get error message (UEFF-Item Unit Effective)
669 -- populate ERROR_CODE column
670 -- Return "FALSE"
671 --
672 -- If the two Unit Numbers match then
673 -- Return "TRUE"
674 -- Else
675 -- If Cross-Unit Number Issue is enabled then
676 -- Get warning message (UEFF-Cross Unit Number Issue)
677 -- populate ERROR_CODE column
678 -- Return "TRUE"
679 -- Else
680 -- Get error message (UEFF-Unit Number Mismatch)
681 -- populate ERROR_CODE column
682 -- Return "FALSE"
683 --
684
685 FUNCTION Validate_Serial
686 ( X_trx_source_type_id IN NUMBER
687 , X_trx_action_id IN NUMBER
688 , X_item_id IN NUMBER
689 , X_organization_id IN NUMBER
690 , X_serial_number IN VARCHAR2
691 , X_unit_number IN VARCHAR2
692 , X_error_code OUT NOCOPY VARCHAR2
693 ) RETURN BOOLEAN IS
694 L_serial_unitnum PJM_UNIT_NUMBERS.UNIT_NUMBER%TYPE;
695 L_unit_eff_item VARCHAR2(1);
696 BEGIN
697
698 --
699 -- If Model/Unit Effectivity is not enabled, there is no need for
700 -- further processing
701 --
702 if ( enabled = 'N' ) then
703 return ( TRUE );
704 end if;
705
706 --
707 -- If transaction type is not "WIP Component Issue" or "WIP Assembly Return"
708 -- or "Direct Interorg transfer" or "Intransit shipment"
709 -- there is no need for further processing
710 --
711 -- 11.08.1999
712 -- Added support for RMA receipts
713 --
714 -- 01.29.2003
715 -- Added support for sales orders staging transfers
716 --
717 if not ( ( X_trx_source_type_id = 5 -- Job or Schedule
718 AND ( X_trx_action_id = 1 -- Issue from stores
719 OR X_trx_action_id = 32 -- Assembly return
720 )
721 ) OR
722 ( X_trx_source_type_id = 12 -- RMA
723 AND ( X_trx_action_id = 27 -- Receipt into stores
724 )
725 ) OR
726 ( ( X_trx_source_type_id = 2 -- Sales order
727 OR X_trx_source_type_id = 8 -- Internal sales order
728 )
729 AND ( X_trx_action_id = 28 -- Staging transfer
730 )
731 ) OR
732 ( X_trx_source_type_id = 13 -- Inventory
733 AND ( X_trx_action_id = 3 -- Direct organization transfer
734 OR X_trx_action_id = 21 -- Intransit shipment
735 )
736 )
737 ) then
738 return ( TRUE );
739 end if;
740
741 --
742 -- A Law 01/31/2003
743 --
744 -- If transaction type is sales order staging transfer and the
745 -- input unit number is NULL, do not perform validation.
746 --
747 -- This is put in as a kludge but should be removed with a future fix
748 -- in the transaction processor.
749 --
750 if ( ( ( X_trx_source_type_id = 2 -- Sales order
751 OR X_trx_source_type_id = 8 -- Internal sales order
752 )
753 AND ( X_trx_action_id = 28 -- Staging transfer
754 )
755 ) AND
756 X_unit_number is null
757 ) then
758 return ( TRUE );
759 end if;
760
761 --
762 -- A Law 02/17/2000
763 --
764 -- Bug 1200761:
765 --
766 -- If transaction type is RMA Receipt and item serial control is
767 -- Dynamic Entry at Receipt, there is no need for further
768 -- processing
769 --
770 if ( ( X_trx_source_type_id = 12 -- RMA
771 AND ( X_trx_action_id = 27 -- Receipt into stores
772 )
773 ) AND
774 Item_Serial_Control( X_item_id
775 , X_organization_id ) = 5
776 ) then
777 return ( TRUE );
778 end if;
779
780 L_unit_eff_item := Unit_Effective_Item( X_item_id
781 , X_organization_id );
782
783 SELECT end_item_unit_number
784 INTO L_serial_unitnum
785 FROM mtl_serial_numbers
786 WHERE serial_number = X_serial_number
787 AND inventory_item_id = X_item_id;
788
789 --
790 -- If item is not under Unit Effective control but serial is link to
791 -- to a unit number, raise error
792 --
793 if ( L_unit_eff_item = 'N' ) then
794 if ( L_serial_unitnum is null ) then
795
796 return ( TRUE );
797
798 else
799
800 X_error_code := 'UEFF-Item Not Unit Effective';
801 fnd_message.set_name('PJM','UEFF-Item Not Unit Effective');
802 return ( FALSE );
803
804 end if;
805 end if;
806
807 --
808 -- If item is under Unit Effective control but upstream unit number
809 -- is NULL, raise error
810 --
811 if ( X_unit_number is null ) then
812
813 X_error_code := 'UEFF-Item Unit Effective';
814 fnd_message.set_name('PJM','UEFF-Item Unit Effective');
815 return ( FALSE );
816
817 end if;
818
819 --
820 -- Now the validation logic
821 --
822 if ( X_unit_number = L_serial_unitnum ) then
823
824 return ( TRUE );
825
826 else
827 if ( Allow_Cross_UnitNum_Issues(X_organization_id) = 'Y' ) then
828
829 X_error_code := 'UEFF-Cross Unit Number Issue';
830 fnd_message.set_name('PJM','UEFF-Cross Unit Number Issue');
831 fnd_message.set_token('SERIAL', X_serial_number);
832 return ( TRUE );
833
834 else
835
836 X_error_code := 'UEFF-Unit Number Mismatch';
837 fnd_message.set_name('PJM','UEFF-Unit Number Mismatch');
838 fnd_message.set_token('SERIAL', X_serial_number);
839 fnd_message.set_token('UNIT1', L_serial_unitnum);
840 fnd_message.set_token('UNIT2', X_unit_number);
841 return ( FALSE );
842
843 end if;
844 end if;
845
846 END Validate_Serial;
847
848
849 --
850 -- Name : Serial_UnitNum_Link
851 -- Pre-reqs : None
852 -- Function : This function links the transaction serial numbers
853 -- to the unit number on the transaction entity
854 -- (e.g. WIP job)
855 --
856 --
857 -- Parameters :
858 -- IN : X_transaction_id NUMBER
859 --
860 -- OUT : X_error_code VARCHAR2
861 --
862 -- Returns : Boolean
863 --
864 FUNCTION Serial_UnitNum_Link
865 ( X_transaction_id IN NUMBER
866 , X_error_code OUT NOCOPY VARCHAR2
867 ) RETURN BOOLEAN IS
868 L_organization_id NUMBER;
869 L_item_id NUMBER;
870 L_src_type_id NUMBER;
871 L_trx_action_Id NUMBER;
872 L_trx_src_id NUMBER;
873 L_rcv_trx_id NUMBER;
874 L_direction NUMBER;
875 L_unit_number PJM_UNIT_NUMBERS.UNIT_NUMBER%TYPE;
876 BEGIN
877
878 --
879 -- If Model/Unit Effectivity is not enabled, there is no need for
880 -- further processing
881 --
882 if ( enabled = 'N' ) then
883 return ( TRUE );
884 end if;
885
886 SELECT organization_id
887 , inventory_item_id
888 , transaction_source_type_id
889 , transaction_action_id
890 , transaction_source_id
891 , rcv_transaction_id
892 , SIGN(primary_quantity)
893 INTO L_organization_id
894 , L_item_id
895 , L_src_type_id
896 , L_trx_action_id
897 , L_trx_src_id
898 , L_rcv_trx_id
899 , L_direction
900 FROM mtl_material_transactions
901 WHERE transaction_id = X_transaction_id;
902
903 --
904 -- If item is not under Unit Effective control, there is no need for
905 -- further processing
906 --
907 if ( Unit_Effective_Item( L_item_id, L_organization_id ) = 'N' ) then
908 return ( TRUE );
909 end if;
910
911 --
912 -- If transaction type is not any one of the following, there is no
913 -- need for further processing
914 --
915 -- 11.08.1999
916 -- Added support for RMA receipts
917 --
918 if not (
919 ( L_src_type_id = 1 -- Purchase Order
920 AND
921 ( L_trx_action_id = 27 -- Receipt into stores
922 OR L_trx_action_id = 1 ) -- Issue from stores
923 )
924 OR ( L_src_type_id = 5 -- Job or schedule
925 AND
926 ( L_trx_action_id = 31 -- Assembly completion
927 OR L_trx_action_id = 32 -- Assembly return
928 OR L_trx_action_id = 33 -- Negative component issue
929 OR L_trx_action_id = 34 ) -- Negative component return
930 )
931 OR ( L_src_type_id = 12 -- RMA
932 AND
933 ( L_trx_action_id = 27 -- Receipt into stores
934 OR L_trx_action_id = 1 ) -- Issue from stores
935 )
936 ) then
937 return ( TRUE );
938 end if;
939
940 if ( L_src_type_id = 5 ) then
941 L_unit_number := WIP_Unit_Number(L_trx_src_id, L_organization_id);
942 elsif ( L_src_type_id = 1 ) then
943 L_unit_number := RCV_Unit_Number(L_rcv_trx_id);
944 else
945 L_unit_number := RMA_Rcpt_Unit_Number(L_rcv_trx_id);
946 end if;
947
948 UPDATE mtl_serial_numbers
949 SET end_item_unit_number =
950 DECODE(L_direction, 1, L_unit_number, NULL)
951 WHERE (inventory_item_id, serial_number) in (
952 SELECT inventory_item_id, serial_number
953 FROM mtl_unit_transactions
954 WHERE transaction_id = X_transaction_id
955 UNION ALL /*Bug 7207502 (FP of 6391634): Added union part to select serial when assembly is lot serial controlled.*/
956 SELECT mut.inventory_item_id, mut.serial_number
957 FROM mtl_transaction_lot_numbers mtln, mtl_unit_transactions mut
958 WHERE mtln.transaction_id = X_transaction_id
959 AND mtln.serial_transaction_id = mut.transaction_id
960 );
961
962 return ( TRUE );
963
964 EXCEPTION
965 when others then
966 X_error_code := sqlerrm;
967 return ( FALSE );
968 END Serial_UnitNum_Link;
969
970
971 --
972 -- Name : Unit_Serial_History
973 -- Pre-reqs : None
974 -- Function : This function creates audit trail information for
975 -- unit number changes to serial numbers
976 --
977 --
978 -- Parameters :
979 -- IN : X_serial_number VARCHAR2
980 -- X_item_id NUMBER
981 -- X_old_unit_number VARCHAR2
982 -- X_new_unit_number VARCHAR2
983 -- X_start_num NUMBER
984 -- X_counts NUMBER
985 --
986 -- OUT : X_error_code VARCHAR2
987 --
988 -- Returns : Boolean
989 --
990 FUNCTION Unit_Serial_History
991 ( X_serial_number IN VARCHAR2
992 , X_item_id IN NUMBER
993 , X_organization_id IN NUMBER
994 , X_old_unit_number IN VARCHAR2
995 , X_new_unit_number IN VARCHAR2
996 , X_error_code OUT NOCOPY VARCHAR2
997 ) return BOOLEAN IS
998 L_user_id NUMBER;
999 L_login_id NUMBER;
1000 BEGIN
1001
1002 L_user_id := fnd_global.user_id;
1003 L_login_id := fnd_global.conc_login_id;
1004
1005 INSERT INTO pjm_unit_serial_history
1006 ( serial_number
1007 , inventory_item_id
1008 , organization_id
1009 , old_unit_number
1010 , new_unit_number
1011 , creation_date
1012 , created_by
1013 , last_update_date
1014 , last_updated_by
1015 , last_update_login )
1016 SELECT X_serial_number
1017 , X_item_id
1018 , X_organization_id
1019 , X_old_unit_number
1020 , X_new_unit_number
1021 , sysdate
1022 , L_user_id
1023 , sysdate
1024 , L_user_id
1025 , L_login_id
1026 FROM dual;
1027
1028 return ( TRUE );
1029
1030 EXCEPTION
1031 when others then
1032 X_error_code := sqlerrm;
1033 return ( FALSE );
1034
1035 END Unit_Serial_History;
1036
1037
1038 --
1039 -- Name : OE_Attribute
1040 -- Pre-reqs : None
1041 -- Function : This function returns the attribute column in the
1042 -- SO_LINES descriptive flexfield that stores the unit
1043 -- number value. The column name is captured in the
1044 -- profile PJM_UEFF_OE_ATTRIBUTE.
1045 --
1046 --
1047 -- Parameters :
1048 -- IN : None
1049 --
1050 -- Returns : Boolean
1051 --
1052 FUNCTION OE_Attribute
1053 RETURN VARCHAR2 IS
1054 BEGIN
1055
1056 RETURN( Fnd_Profile.Value_WNPS('PJM_UEFF_OE_ATTRIBUTE') );
1057
1058 END OE_Attribute;
1059
1060
1061 END PJM_UNIT_EFF;
[Home] [Help]