1 PACKAGE BODY RCV_TRANSACTIONS_SV AS
2 /* $Header: RCVTXTXB.pls 120.1.12010000.3 2010/02/02 09:37:05 honwei ship $*/
3
4 /*===========================================================================
5
6 FUNCTION NAME: val_receiving_controls
7
8
9 1. Receiving controls are only checked for vendor receipts. Intransit
10 shipments cannot be rejected and we have not way to define org->org
11 receiving controls.
12 2. Controls are only checked if the exception level is 'REJECT'
13 3. Quantity tolerances are not checked. It is not possible to
14 over-receive an express receipt.
15 4. Standard receipts will be created for the ship-to location
16 specified on the PO so the 'enforce ship-to location' control
17 is not tested.
18 5. Routing controls are checked for both Vendor sourced and
19 intransit receipts.
20
21 ===========================================================================*/
22
23 FUNCTION val_receiving_controls (
24 X_transaction_type IN VARCHAR2,
25 X_auto_transact_code IN VARCHAR2,
26 X_expected_receipt_date IN DATE,
27 X_transaction_date IN DATE,
28 X_routing_header_id IN NUMBER,
29 X_po_line_location_id IN NUMBER,
30 X_item_id IN NUMBER,
31 X_vendor_id IN NUMBER,
32 X_to_organization_id IN NUMBER)
33 RETURN NUMBER IS
34
35 transaction_ok NUMBER := 1;
36 enforce_ship_to_loc VARCHAR2(20);
37 allow_substitutes VARCHAR2(20);
38 routing_id NUMBER;
39 qty_rcv_tolerance NUMBER;
40 qty_rcv_exception VARCHAR2(20);
41 days_early_receipt NUMBER;
42 days_late_receipt NUMBER;
43 rcv_date_exception VARCHAR2(20);
44 allow_routing_override VARCHAR2(20);
45 expected_date DATE;
46 high_range_date DATE;
47 low_range_date DATE;
48 X_progress VARCHAR2(4) := '000';
49
50 --Bug : 3724862
51 l_drop_ship_flag po_line_locations.drop_ship_flag%type;
52
53
54 BEGIN
55
56 /*
57 ** Get the receiving controls for this transaction.
58 */
59
60 /*
61 ** DEBUG: Will this function work properly on getting the routing control
62 ** for internally sourced shipments
63 */
64 X_progress := '200';
65 rcv_core_s.get_receiving_controls (X_po_line_location_id,
66 X_item_id,
67 X_vendor_id,
68 X_to_organization_id,
69 enforce_ship_to_loc,
70 allow_substitutes,
71 routing_id,
72 qty_rcv_tolerance,
73 qty_rcv_exception,
74 days_early_receipt,
75 days_late_receipt,
76 rcv_date_exception);
77
78 /* -- dbms_output.put_line ('Val Receiving Controls : enforce_ship_to_loc : ' ||
79 -- enforce_ship_to_loc);
80 -- dbms_output.put_line ('Val Receiving Controls : allow_substitutes : ' ||
81 -- allow_substitutes);
82 -- dbms_output.put_line ('Val Receiving Controls : routing_id : ' ||
83 -- to_char(routing_id));
84 -- dbms_output.put_line ('Val Receiving Controls : qty_rcv_tolerance : ' ||
85 -- to_char(qty_rcv_tolerance));
86 -- dbms_output.put_line ('Val Receiving Controls : rcv_date_exception : ' ||
87 -- rcv_date_exception);
88 -- dbms_output.put_line ('Val Receiving Controls : qty_rcv_exception : ' ||
89 -- qty_rcv_exception);*/
90 /* -- dbms_output.put_line ('Val Receiving Controls : days_early_receipt : ' ||
91 -- substr(to_char(days_early_receipt),1,3));
92 -- dbms_output.put_line ('Val Receiving Controls : days_late_receipt : ' ||
93 -- substr(to_char(days_late_receipt),1,3));
94 -- dbms_output.put_line ('Val Receiving Controls : rcv_date_exception : ' ||
95 -- rcv_date_exception);*/
96 /*
97 ** if the days exception is set to reject then verify that the receipt
98 ** falls within the date tolerances
99 */
100 IF (rcv_date_exception='REJECT') THEN
101
102 /*
103 ** Check to see that you have a promised date on the po. If not
104 ** then see if you have an expected date. If not then the trx
105 ** passed date validation
106 ** I have placed either the promised date if it is set or the
107 ** need by date into the expected_receipt date column in the interface
108 */
109 IF (X_expected_receipt_date IS NOT NULL) THEN
110
111 expected_date := X_expected_receipt_date;
112
113 ELSE
114 transaction_ok := 0;
115
116 END IF;
117
118 /*
119 ** If you have a date to compare against then set up the range
120 ** based on the days early and late parameters
121 */
122 IF ( transaction_ok > 0 ) THEN
123
124 low_range_date := expected_date - days_early_receipt;
125 high_range_date := expected_date + days_late_receipt;
126
127 -- dbms_output.put_line ('val_receiving_controls : expected_date : ' ||
128 -- to_char(expected_date));
129 -- dbms_output.put_line ('val_receiving_controls : low_range_date : ' ||
130 -- to_char(low_range_date));
131 -- dbms_output.put_line ('val_receiving_controls : high_range_date : ' ||
132 -- to_char(high_range_date));
133
134 /*
135 ** If the transaction date is between the range then it's okay
136 ** to process.
137 */
138 IF (X_transaction_date >= low_range_date AND
139 X_transaction_date <= high_range_date) THEN
140
141 transaction_ok := 0;
142
143 ELSE
144 /* Transaction_Ok = 1 indicates that
145 ** receipt date tolerance is exceeded. */
146 transaction_ok := 1;
147 END IF;
148
149 END IF; -- (transaction_ok > 0)
150
151 ELSE --(rcv_date_exception <> REJECT)
152 transaction_ok := 0;
153 END IF;
154
155 /* Bug 3724862 : If the Express receipt is against a DropShip PO line then
156 ** the routing can only be 'Direct Delivery'. We will error out if
157 ** the routing is not 'Direct Delivery' in the rti.
158 */
159
160 IF ( X_po_line_location_id is not null and
161 transaction_ok = 0 ) THEN
162
163 select nvl(drop_ship_flag,'N')
164 into l_drop_ship_flag
165 from po_line_locations_all
166 where line_location_id = X_po_line_location_id;
167
168 IF ( l_drop_ship_flag = 'Y' and
169 X_transaction_type = 'RECEIVE' and
170 X_auto_transact_code <> 'DELIVER' ) THEN
171
172 transaction_ok := 3;
173
174 END IF;
175 END IF;
176
177 /*
178 ** Check the routing controls to see if the transaction type matches the
179 ** routing specfied on the po or by the hierarchy for item, vendor for
180 ** internally sourced shipments
181 */
182
183 /*
184 ** This component of the check is a little different thab others since
185 ** we have a carry over of the transaction_ok flag. If the flag is
186 ** already set to false then you don't want to perform any other checks
187 */
188 IF (transaction_ok = 0 ) THEN
189 /*
190 ** Go get the routing override value to see if you need to check the
191 ** routing control. If routing override is set to 'Y' then you don't
192 ** need to perform this check since any routing is allowed
193 */
194 X_progress := '300';
195
196 -- dbms_output.put_line('Getting the Routing Info ');
197
198 allow_routing_override := rcv_setup_s.get_override_routing;
199
200 -- dbms_output.put_line ('val_receiving_controls : allow_routing_override : ' ||
201 -- allow_routing_override);
202 -- dbms_output.put_line ('val_receiving_controls : transaction_type : '||
203 -- X_transaction_type);
204 -- dbms_output.put_line ('val_receiving_controls : routing_id : ' ||
205 -- to_char(routing_id));
206
207 /*
208 ** Check the routing controls. If routing_override is set to Y then you
209 ** don't care about the routing controls. Otherwise check to make sure
210 ** you're express option is in line with the routing id
211 */
212 IF (allow_routing_override = 'N' AND transaction_ok = 0 ) THEN
213
214 /*
215 ** You can only do express direct if routing is set to direct
216 */
217 IF (X_transaction_type = 'RECEIVE' AND
218 X_auto_transact_code = 'DELIVER' AND
219 (routing_id IN (3,0))) THEN
220
221 /*
222 ** Direct delivery is allowed
223 */
224 transaction_ok := 0;
225
226 /*
227 ** You can only do express receipt if routing is set to
228 ** standard receipt or inspection required
229 */
230 ELSIF (X_transaction_type = 'RECEIVE' AND
231 X_auto_transact_code = 'RECEIVE' AND
232 (X_routing_header_id IN (1, 2, 0))) THEN
233 /*
234 ** standard receipt is allowed
235 */
236 transaction_ok := 0;
237
238 ELSE
239 /*
240 ** Routing Control is On and the Routing Definitions
241 ** cannot be overridden.Set the return value to
242 ** flag Routing Information as the cause of Failure.
243 */
244 transaction_ok := 2;
245
246 END IF;
247
248 ELSE
249 transaction_ok := 0;
250
251 END IF;
252
253 END IF;
254
255
256 RETURN(transaction_ok);
257
258
259 EXCEPTION
260 WHEN OTHERS THEN
261 po_message_s.sql_error('val_receiving_controls', x_progress, sqlcode);
262 RAISE;
263
264 END val_receiving_controls;
265
266 /*===========================================================================
267
268 FUNCTION NAME: val_wip_info
269
270 ===========================================================================*/
271 FUNCTION val_wip_info (
272 X_to_organization_id IN NUMBER,
273 X_wip_entity_id IN NUMBER,
274 X_wip_operation_seq_num IN NUMBER,
275 X_wip_resource_seq_num IN NUMBER,
276 X_wip_line_id IN NUMBER,
277 X_wip_repetitive_schedule_id IN NUMBER,
278 p_po_line_id IN NUMBER) -- bug 2619164
279 RETURN NUMBER IS
280
281 valid_open_job NUMBER := 0;
282 X_progress VARCHAR2(4) := '000';
283 l_osp_flag po_line_types_b.outside_operation_flag%TYPE; -- bug 2619164
284
285
286 BEGIN
287
288 /*
289 ** The required info for shop floor is the
290 ** job, the op seq num, the reource seq num, the
291 ** repetive schedule and the wip line
292 */
293
294 /*
295 ** First make sure all the required id elemnts are present. If not
296 ** then these rows cannot be transacted.
297 */
298 IF X_to_organization_id IS NULL THEN
299
300 return 10;
301
302 END IF;
303
304 IF X_wip_entity_id IS NULL THEN
305
306 return 20;
307
308 END IF;
309
310 IF (l_osp_flag = 'Y') AND (X_wip_operation_seq_num IS NULL) THEN
311
312 return 30;
313
314 END IF;
315
316 -- bug 2619164 start
317 -- for direct item, wip_resource_seq_num will always be null
318 -- for outside service, wip_resource_seq_num can not be null
319
320 SELECT plt.outside_operation_flag
321 INTO l_osp_flag
322 FROM po_line_types plt,
323 po_lines pl
324 WHERE plt.line_type_id = pl.line_type_id
325 AND pl.po_line_id = p_po_line_id;
326
327 IF (l_osp_flag = 'Y') AND (X_wip_resource_seq_num IS NULL) THEN
328
329 RETURN 40;
330
331 END IF;
332 -- bug 2619164 end
333
334 /*
335 ** If this is an repetitive job then make sure that the job is
336 ** open. If it is not then this cannot be transacted.
337 */
338 IF (X_wip_repetitive_schedule_id IS NOT NULL AND
339 X_wip_line_id IS NOT NULL) THEN
340
341 /* DEBUG:
342 ** We need a function or a view from mike to determine if a job
343 ** is still open or not
344 */
345 BEGIN
346
347 X_progress := '1250';
348 SELECT 1
349 INTO valid_open_job
350 FROM WIP_REPETITIVE_SCHEDULES WRS
351 WHERE WRS.ORGANIZATION_ID = X_to_organization_id
352 AND WRS.REPETITIVE_SCHEDULE_ID =
353 X_wip_repetitive_schedule_id
354 AND WRS.STATUS_TYPE IN (3,4);
355
356 /* If the status of the job is closed then return */
357 IF (valid_open_job = 0) THEN
358 RETURN 50;
359 END IF;
360
361 /*
362 ** If the status of the job is no longed open then you won't
363 ** return a row and therefore the row cannot be transacted
364 */
365 EXCEPTION
366 WHEN NO_DATA_FOUND THEN
367 RETURN 50;
368 END;
369
370 /*
371 ** If this is an discrete job then make sure that the job is
372 ** open. If it is not then this cannot be transacted.
373 */
374 ELSIF (X_wip_repetitive_schedule_id IS NULL AND
375 X_wip_line_id IS NULL) THEN
376
377 /* DEBUG:
378 ** We need a function or a view from mike to determine if a job
379 ** is still open or not
380 */
381 BEGIN
382
383 X_progress := '1260';
384 SELECT 1
385 INTO valid_open_job
386 FROM WIP_DISCRETE_JOBS WDJ
387 WHERE WDJ.ORGANIZATION_ID = X_to_organization_id
388 AND WDJ.WIP_ENTITY_ID = X_wip_entity_id
389 AND WDJ.STATUS_TYPE IN (3,4);
390
391 /* If the status of the job is closed then return */
392 IF (valid_open_job = 0) THEN
393 RETURN 60;
394 END IF;
395
396 /*
397 ** If the status of the job is no longed open then you won't
398 ** return a row and therefore the row cannot be transacted
399 */
400 EXCEPTION
401 WHEN NO_DATA_FOUND THEN
402 RETURN 60;
403
404 END;
405
406 ELSE
407 /*
408 ** one or the other, but not both, of the repetitive
409 ** schedule and wip line have been specified
410 */
411 RETURN 70;
412
413 END IF;
414
415 RETURN 0;
416
417 EXCEPTION
418 WHEN OTHERS THEN
419 po_message_s.sql_error('val_wip_info', X_progress, sqlcode);
420 RAISE;
421
422 END val_wip_info;
423
424 /*===========================================================================
425
426 PROCEDURE NAME: val_if_inventory_destination
427
428 ===========================================================================*/
429
430 /*
431 ** Check to see if any of the distributions are of type inventory
432 */
433 FUNCTION val_if_inventory_destination (X_line_location_id IN NUMBER,
434 X_shipment_line_id IN NUMBER)
435 RETURN BOOLEAN IS
436
437 X_number_of_inv_dest NUMBER := 0;
438 X_progress VARCHAR2(4) := '000';
439
440 BEGIN
441
442 X_progress := '600';
443 /*
444 ** Check to see which id is set to know which table to check for
445 ** inventory destination_type_code
446 */
447 IF (X_line_location_id IS NOT NULL) THEN
448
449 X_progress := '610';
450
451 SELECT count(1)
452 INTO X_number_of_inv_dest
453 FROM po_distributions pd
454 WHERE pd.line_location_id = X_line_location_id
455 AND pd.destination_type_code = 'INVENTORY';
456
457 ELSE
458
459 X_progress := '620';
460
461 SELECT count(1)
462 INTO X_number_of_inv_dest
463 FROM rcv_shipment_lines rsl
464 WHERE rsl.shipment_line_id = X_shipment_line_id
465 AND rsl.destination_type_code = 'INVENTORY';
466 END IF;
467
468 IF (X_number_of_inv_dest > 0) THEN
469 RETURN TRUE;
470 ELSE
471 RETURN FALSE;
472 END IF;
473
474 EXCEPTION
475 WHEN OTHERS THEN
476 po_message_s.sql_error('val_if_inventory_destination', x_progress, sqlcode);
477 RAISE;
478
479 END val_if_inventory_destination;
480
481 /*===========================================================================
482
483 FUNCTION NAME: val_deliver_destination
484
485 Ensure that all mandatory columns for each destination type are
486 populated. If a mandatory column is not specified, the transaction
487 cannot be processed via express.
488 The exceptions are subinventory and locator. If a sub is provided
489 it will be used otherwise the default receiving subinventory will
490 be used (if available). Locator control is evaluated if a sub is
491 provided or defaulted and if locator control is required, the default
492 locator for the sub will be used.
493
494 =============================================================================*/
495
496 FUNCTION val_deliver_destination (
497 X_to_organization_id IN NUMBER,
498 X_item_id IN NUMBER,
499 X_destination_type_code IN VARCHAR2,
500 X_deliver_to_location_id IN NUMBER,
501 X_subinventory IN VARCHAR2)
502 RETURN NUMBER IS
503
504 under_lot_control BOOLEAN := FALSE;
505 under_serial_control BOOLEAN := FALSE;
506 valid_subinventory NUMBER := 0;
507 location_is_valid NUMBER := 1;
508 X_progress VARCHAR2(4) := '000';
509
510 BEGIN
511
512 /*
513 ** Make sure the destination organization is not null since that
514 ** is a requirement irregardless of the desintation type
515 */
516 IF (X_to_organization_id IS NULL) THEN
517 RETURN (10);
518 END IF;
519
520 IF (X_destination_type_code IN ('EXPENSE', 'SHOP FLOOR')) THEN
521
522 -- dbms_output.put_line ('val_deliver_destination : validating location');
523
524 /*
525 ** The only required info for expense is the
526 ** deliver to location
527 */
528 IF (X_deliver_to_location_id IS NULL) THEN
529 RETURN (20);
530
531 ELSE
532 /*
533 ** Make sure that the location is still valid
534 */
535 location_is_valid := po_locations_s.val_location (
536 X_deliver_to_location_id,
537 X_destination_type_code,
538 X_to_organization_id);
539
540 /*
541 ** if the location is not valid then this transaction cannot be
542 ** processed
543 */
544 IF (location_is_valid <> 0) THEN
545 RETURN (30);
546
547 -- dbms_output.put_line ('val_deliver_destination : invalid location');
548
549 END IF;
550
551 END IF;
552
553 ELSIF (X_destination_type_code = 'INVENTORY') THEN
554
555 -- dbms_output.put_line ('val_deliver_destination : validating sub');
556
557 /*
558 ** Make sure that you have a subinventory defined
559 */
560 IF (X_subinventory IS NULL) THEN
561
562 -- dbms_output.put_line ('val_deliver_destination : invalid sub');
563 RETURN (40);
564
565 END IF;
566
567
568 /*
569 ** Validate that the sub on the po was valid
570 ** Validate sub should only return true or false
571 ** or have predefined values as defines so we don't have to
572 ** know the codes
573 */
574
575 X_progress := '1210';
576 valid_subinventory := po_subinventories_s.val_subinventory (
577 X_subinventory,
578 X_to_organization_id,
579 TRUNC(sysdate),
580 X_item_id,
581 X_destination_type_code);
582
583 IF (valid_subinventory <> 0) THEN
584
585 -- dbms_output.put_line ('val_deliver_destination : invalid sub');
586 RETURN (50);
587
588 END IF;
589
590
591 ELSE
592 /*
593 ** The destination type code is goofed and we cannot therefore
594 ** transact the row.
595 */
596 RETURN (60);
597
598 END IF;
599
600 RETURN (0);
601
602 EXCEPTION
603 WHEN OTHERS THEN
604 -- dbms_output.put_line('Progess is :' || X_Progress);
605 po_message_s.sql_error('val_deliver_destination', X_progress, sqlcode);
606 RAISE;
607
608 END val_deliver_destination;
609
610 /*===========================================================================
611
612 PROCEDURE NAME: val_destination_info
613
614 Ensure that all destination information is still valid at the time of
615 receipt. A po can be created with a ship to location or a deliver to
616 location that could become invalid by the time the receipt is entered.
617 To ensure that the lov's for one of these fields does not come up
618 because the item is not in the valid list, we will not populate the
619 column if it is disabled
620
621 =============================================================================*/
622
623 PROCEDURE val_destination_info (
624 X_to_organization_id IN NUMBER,
625 X_item_id IN NUMBER,
626 X_ship_to_location_id IN NUMBER,
627 X_deliver_to_location_id IN NUMBER,
628 X_deliver_to_person_id IN NUMBER,
629 X_subinventory IN VARCHAR2,
630 X_valid_ship_to_location OUT NOCOPY BOOLEAN,
631 X_valid_deliver_to_location OUT NOCOPY BOOLEAN,
632 X_valid_deliver_to_person OUT NOCOPY BOOLEAN,
633 X_valid_subinventory OUT NOCOPY BOOLEAN) IS
634
635 X_return_val NUMBER := 0;
636 location_is_valid NUMBER := 1;
637 X_progress VARCHAR2(4) := '000';
638 x_del_per_val VARCHAR2(240); -- for bug 2392074
639
640 BEGIN
641
642 X_progress := '000';
643
644 /*
645 ** Make sure that the ship to location is still valid
646 */
647 IF (X_ship_to_location_id IS NOT NULL) THEN
648
649 X_valid_ship_to_location := po_locations_s.val_receipt_site_in_org (
650 X_ship_to_location_id,
651 X_to_organization_id);
652
653 END IF;
654
655 X_progress := '010';
656
657 /*
658 ** Make sure that the deliver to location is still valid
659 */
660 IF (X_deliver_to_location_id IS NOT NULL) THEN
661
662 X_return_val := po_locations_s.val_location (
663 X_deliver_to_location_id,
664 'RECEIVING',
665 X_to_organization_id);
666
667 /*
668 ** The return for this is a number: 0 is valid, anything else is invalid
669 */
670 IF (X_return_val = 0) THEN
671
672 X_valid_deliver_to_location := TRUE;
673
674 ELSE
675
676 X_valid_deliver_to_location := FALSE;
677
678 END IF; --(X_return_val = 0)
679
680 END IF; -- (X_deliver_to_location_id IS NOT NULL)
681
682 X_progress := '020';
683
684 /*
685 ** Make sure that the deliver to person is still valid
686 */
687 IF (X_deliver_to_person_id IS NOT NULL) THEN
688
689 /*
690 ** Debug: Need to add a function to PO_EMPLOYEES_SV to
691 ** ensure this person is still active when the receipt
692 ** is done. We'll set it to true for now
693 */
694
695 /* Added the validation on deliver to person
696 for Bug 2392074.
697 */
698 /* Replace view hr_employees_current_v with view
699 per_workforce_current_x to enable requester from
700 another BG for bug 9157396
701 */
702 SELECT nvl(max(hre.full_name),'notfound')
703 INTO x_del_per_val
704 FROM per_workforce_current_x hre
705 WHERE (hre.termination_date IS NULL
706 OR hre.termination_date > sysdate)
707 AND hre.person_id = x_deliver_to_person_id;
708
709 if (x_del_per_val='notfound') then
710 X_valid_deliver_to_person := FALSE;
711 else
712 X_valid_deliver_to_person :=TRUE;
713 end if;
714
715
716 END IF;
717
718 X_progress := '030';
719
720 /*
721 ** Make sure that the subinventory is still valid
722 */
723 -- Bug 5495768 : Validation os subinventory should be done only if
724 -- item_id is not null.In case of one time items, the item_id is
725 -- nul.So subinventory validation is not needed.
726
727 IF (X_subinventory IS NOT NULL AND x_item_id IS NOT NULL) THEN
728
729 X_return_val := po_subinventories_s.val_subinventory (
730 x_subinventory, x_to_organization_id, sysdate,
731 x_item_id, 'INVENTORY');
732
733 /*
734 ** The return for this is a number: 0 is valid, anything else is invalid
735 */
736 IF (X_return_val = 0) THEN
737
738 X_valid_subinventory := TRUE;
739
740 ELSE
741
742 X_valid_subinventory := FALSE;
743
744 END IF; --(X_return_val = 0)
745
746 END IF; -- (X_subinventory IS NOT NULL)
747
748 RETURN;
749
750 EXCEPTION
751 WHEN OTHERS THEN
752 po_message_s.sql_error('val_destination_info', x_progress, sqlcode);
753 RAISE;
754
755 END val_destination_info;
756
757 /*===========================================================================
758
759 FUNCTION NAME: val_pending_receipt_trx
760
761 ===========================================================================*/
762 /*
763 ** If there are any receipt supply rows that have not been delivered
764 ** and this line location has multiple distributions then it cannot be
765 ** transacted since you don't know how the user will distribute that
766 ** quantity
767 */
768
769 FUNCTION val_pending_receipt_trx (
770 X_po_line_location_id IN NUMBER,
771 X_group_id IN NUMBER)
772 RETURN BOOLEAN IS
773
774 X_num_of_distributions NUMBER := 0;
775 X_num_of_receipts NUMBER := 0;
776 X_progress VARCHAR2(4) := '000';
777
778 BEGIN
779
780 /*
781 ** This is only an issue for vendor sourced express transactions since
782 ** they are the only ones that can have multiple distributions
783 */
784
785 /*
786 ** Get the number of distributions for this line_location.
787 */
788 X_progress := '500';
789
790 SELECT count (1)
791 INTO X_num_of_distributions
792 FROM po_distributions pod
793 WHERE pod.line_location_id = X_po_line_location_id;
794
795 -- dbms_output.put_line ('val_pending_transactions : X_num_of_distribtions : ' ||
796 -- TO_CHAR(X_num_of_distributions));
797
798 IF (X_num_of_distributions > 1) THEN
799
800 SELECT count (1)
801 INTO X_num_of_receipts
802 FROM rcv_supply rs
803 WHERE rs.po_line_location_id = X_po_line_location_id;
804
805 -- dbms_output.put_line ('val_pending_transactions : X_num_of_receipts : ' ||
806 -- TO_CHAR(X_num_of_receipts));
807
808 /*
809 ** If there is any receiving supply for this line location and there
810 ** are multiple distributions then fail the transaction
811 */
812 IF (X_num_of_receipts > 0) THEN
813
814 RETURN FALSE;
815
816 END IF ;
817
818 END IF;
819
820 RETURN TRUE;
821
822 EXCEPTION
823 /*
824 ** If no rows were found then the transaction is ok
825 */
826 WHEN NO_DATA_FOUND THEN
827 RETURN TRUE;
828 WHEN OTHERS THEN
829 po_message_s.sql_error('val_pending_receipt_trx', x_progress, sqlcode);
830 RAISE;
831
832 END val_pending_receipt_trx;
833
834 /*===========================================================================
835
836 PROCEDURE NAME: get_wip_info
837
838 ===========================================================================*/
839 /*
840 ** Go get the outside processing information for a given receipt line
841 */
842
843 PROCEDURE get_wip_info
844 (X_wip_entity_id IN NUMBER,
845 X_wip_repetitive_schedule_id IN NUMBER,
846 X_wip_line_id IN NUMBER,
847 X_wip_operation_seq_num IN NUMBER,
848 X_wip_resource_seq_num IN NUMBER,
849 X_to_organization_id IN NUMBER,
850 X_job IN OUT NOCOPY VARCHAR2,
851 X_line_num IN OUT NOCOPY VARCHAR2,
852 X_sequence IN OUT NOCOPY NUMBER,
853 X_department IN OUT NOCOPY VARCHAR2) IS
854
855 X_progress VARCHAR2(4) := '000';
856
857 BEGIN
858 --Bug# 2000013 togeorge 09/18/2001
859 --Eam: Split the following sql to 3 different sqls because eAM w/o would
860 -- not have resource information and this sql will fail.
861 /*
862 SELECT we.wip_entity_name job,
863 wn.operation_seq_num sequence,
864 bd.department_code department
865 INTO X_job,
866 X_sequence,
867 X_department
868 FROM wip_entities we,
869 bom_departments bd,
870 wip_operation_resources wr,
871 wip_operations wn,
872 wip_operations wo
873 WHERE wo.wip_entity_id = X_wip_entity_id
874 AND wo.organization_id = X_to_organization_id
875 AND nvl(wo.repetitive_schedule_id, -1) =
876 nvl(X_wip_repetitive_schedule_id, -1)
877 AND wo.operation_seq_num = X_wip_operation_seq_num
878 AND wr.wip_entity_id = X_wip_entity_id
879 AND wr.organization_id = X_to_organization_id
880 AND nvl(wr.repetitive_schedule_id, -1) =
881 nvl(X_wip_repetitive_schedule_id, -1)
882 AND wr.operation_seq_num = X_wip_operation_seq_num
883 AND wr.resource_seq_num = X_wip_resource_seq_num
884 AND wn.wip_entity_id = X_wip_entity_id
885 AND wn.organization_id = X_to_organization_id
886 AND nvl(wn.repetitive_schedule_id, -1) =
887 nvl(X_wip_repetitive_schedule_id, -1)
888 AND wn.operation_seq_num =
889 decode(wr.autocharge_type, 4,
890 nvl(wo.next_operation_seq_num, wo.operation_seq_num),
891 wo.operation_seq_num)
892 AND bd.department_id = wn.department_id
893 AND we.wip_entity_id = X_wip_entity_id
894 AND we.organization_id = X_to_organization_id;
895 */
896
897 if X_wip_entity_id is not null then
898 begin
899 SELECT we.wip_entity_name job
900 INTO X_job
901 FROM wip_entities we
902 WHERE we.wip_entity_id = X_wip_entity_id
903 AND we.organization_id = X_to_organization_id;
904 exception
905 when others then
906 X_job := null;
907 end;
908 end if;
909
910 if X_wip_entity_id is not null and X_wip_operation_seq_num is not null then
911 begin
912 SELECT wn.operation_seq_num sequence,
913 bd.department_code department
914 INTO X_sequence, X_department
915 FROM bom_departments bd,
916 wip_operation_resources wr,
917 wip_operations wn,
918 wip_operations wo
919 WHERE wo.wip_entity_id = X_wip_entity_id
920 AND wo.organization_id = X_to_organization_id
921 AND nvl(wo.repetitive_schedule_id, -1) =
922 nvl(X_wip_repetitive_schedule_id, -1)
923 AND wo.operation_seq_num = X_wip_operation_seq_num
924 AND wr.wip_entity_id = X_wip_entity_id
925 AND wr.organization_id = X_to_organization_id
926 AND nvl(wr.repetitive_schedule_id, -1) =
927 nvl(X_wip_repetitive_schedule_id, -1)
928 AND wr.operation_seq_num = X_wip_operation_seq_num
929 AND wr.resource_seq_num = X_wip_resource_seq_num
930 AND wn.wip_entity_id = X_wip_entity_id
931 AND wn.organization_id = X_to_organization_id
932 AND nvl(wn.repetitive_schedule_id, -1) =
933 nvl(X_wip_repetitive_schedule_id, -1)
934 AND wn.operation_seq_num = wo.operation_seq_num
935 -- Bug#2738959 : Removed the decode statement for autocharge_type 4
936 -- Replaced the following statement with the one above.
937 -- Comparing with operation_seq_num instead of next_operation_seq_num
938 -- AND wn.operation_seq_num =
939 -- decode(wr.autocharge_type, 4,
940 -- nvl(wo.next_operation_seq_num, wo.operation_seq_num),
941 -- wo.operation_seq_num)
942 AND bd.department_id = wn.department_id;
943 exception
944 when no_data_found then
945 --for EAM workorders the above sql would raise no_data_found.
946 --find department code and sequence with out touching resource table.
947 begin
948 select bd.department_code department
949 into X_department
950 from bom_departments bd,wip_operations wn
951 where wn.wip_entity_id = X_wip_entity_id
952 and wn.organization_id = X_to_organization_id
953 and nvl(wn.repetitive_schedule_id, -1) =
954 nvl(X_wip_repetitive_schedule_id, -1)
955 and bd.department_id = wn.department_id;
956 exception
957 when others then
958 X_department :=null;
959 end;
960
961 begin
962 SELECT wo.operation_seq_num sequence
963 INTO X_sequence
964 FROM wip_operations wo
965 WHERE wo.wip_entity_id = X_wip_entity_id
966 AND wo.organization_id = X_to_organization_id
967 AND nvl(wo.repetitive_schedule_id, -1) =
968 nvl(X_wip_repetitive_schedule_id, -1)
969 AND wo.operation_seq_num = X_wip_operation_seq_num;
970 exception
971 when others then
972 X_sequence := null;
973 end;
974 when others then
975 X_sequence :=null;
976 X_department :=null;
977 end;
978 end if;
979 --
980 IF (X_wip_line_id IS NOT NULL) THEN
981
982 select wl.line_code
983 into X_line_num
984 from wip_lines wl
985 where wl.organization_id = X_to_organization_id
986 and wl.line_id = X_wip_line_id;
987
988 END IF;
989
990 EXCEPTION
991 WHEN OTHERS THEN
992 po_message_s.sql_error('get_wip_info', x_progress, sqlcode);
993 RAISE;
994
995 END get_wip_info;
996
997 /*===========================================================================
998 *
999 * PROCEDURE NAME: get_rma_dest_info
1000 *
1001 *===========================================================================*/
1002 /*
1003 * ** Get the destination info for the RMA line.
1004 * */
1005
1006 PROCEDURE get_rma_dest_info
1007 (x_oe_order_header_id IN NUMBER,
1008 x_oe_order_line_id IN NUMBER,
1009 x_item_id IN NUMBER,
1010 x_deliver_to_sub IN OUT NOCOPY VARCHAR2,
1011 x_deliver_to_location_id IN OUT NOCOPY NUMBER,
1012 x_deliver_to_location IN OUT NOCOPY VARCHAR2,
1013 x_destination_type_dsp IN OUT NOCOPY VARCHAR2,
1014 x_destination_type_code IN OUT NOCOPY VARCHAR2,
1015 x_to_organization_id IN OUT NOCOPY NUMBER,
1016 x_rate IN OUT NOCOPY NUMBER,
1017 x_rate_date IN OUT NOCOPY DATE) IS
1018
1019 X_progress VARCHAR2(4) := '000';
1020
1021 X_valid_ship_to_location BOOLEAN;
1022 X_valid_deliver_to_location BOOLEAN;
1023 X_valid_deliver_to_person BOOLEAN;
1024 X_valid_subinventory BOOLEAN;
1025
1026 BEGIN
1027
1028 X_progress := '010';
1029
1030 select displayed_field, lookup_code
1031 into x_destination_type_dsp, x_destination_type_code
1032 from po_lookup_codes
1033 where lookup_code = 'RECEIVING'
1034 and lookup_type = 'RCV DESTINATION TYPE';
1035
1036 X_progress := '020';
1037
1038 /* get subinventory, rate, rate_date, item_id, to_organization_id */
1039 select oel.subinventory,
1040 NVL(oel.ship_from_org_id, oeh.ship_from_org_id),
1041 oeh.conversion_rate,
1042 oeh.conversion_rate_date
1043 into x_deliver_to_sub,
1044 x_to_organization_id,
1045 x_rate,
1046 x_rate_date
1047 from oe_order_lines_all oel,
1048 oe_order_headers_all oeh
1049 where oeh.header_id = x_oe_order_header_id
1050 and oel.line_id = x_oe_order_line_id;
1051
1052 X_progress := '030';
1053
1054 /* get location_id and location_code */
1055 select haou.location_id,
1056 hla.location_code
1057 into x_deliver_to_location_id,
1058 x_deliver_to_location
1059 from hr_all_organization_units haou,
1060 hr_locations_all hla
1061 where haou.organization_id = x_to_organization_id
1062 and haou.location_id = hla.location_id;
1063
1064
1065 /*
1066 ** Make sure the dest information is still valid
1067 */
1068 rcv_transactions_sv.val_destination_info (
1069 X_to_organization_id,
1070 X_item_id,
1071 NULL,
1072 X_deliver_to_location_id,
1073 NULL,
1074 X_deliver_to_sub,
1075 X_valid_ship_to_location,
1076 X_valid_deliver_to_location,
1077 X_valid_deliver_to_person,
1078 X_valid_subinventory);
1079
1080 IF (NOT X_valid_deliver_to_location) THEN
1081 X_deliver_to_location_id := NULL;
1082 X_deliver_to_location := NULL;
1083 END IF;
1084
1085 IF (NOT X_valid_subinventory) THEN
1086 X_deliver_to_sub := NULL;
1087 END IF;
1088
1089 EXCEPTION
1090 WHEN OTHERS THEN
1091 po_message_s.sql_error('get_rma_dest_info', x_progress, sqlcode);
1092 RAISE;
1093
1094 END get_rma_dest_info;
1095
1096 END RCV_TRANSACTIONS_SV;
[Home] [Help]