1 PACKAGE BODY rcv_core_s AS
2 /* $Header: RCVCOCOB.pls 120.2 2006/05/18 05:21:59 amony noship $*/
3
4 -- Read the profile option that enables/disables the debug log
5 g_asn_debug VARCHAR2(1) := NVL(fnd_profile.VALUE('RCV_DEBUG_MODE'), 'N');
6 g_log_head CONSTANT VARCHAR2(30) := 'po.plsql.RCV_CORE_S.'; -- <BUG 3365446>
7 e_validation_error EXCEPTION;
8
9 /*===========================================================================
10
11 PROCEDURE NAME: val_destination_info
12
13 ===========================================================================*/
14 PROCEDURE val_destination_info(
15 x_receipt_source_code IN VARCHAR2,
16 x_trx_date IN DATE,
17 x_line_loc_id IN NUMBER,
18 x_dist_id IN NUMBER,
19 x_ship_line_id IN NUMBER,
20 x_org_id IN NUMBER,
21 x_item_id IN NUMBER,
22 x_ship_to_loc_id OUT NOCOPY NUMBER,
23 x_ship_to_loc_code OUT NOCOPY VARCHAR2,
24 x_deliver_to_loc_id OUT NOCOPY NUMBER,
25 x_deliver_to_loc_code OUT NOCOPY VARCHAR2,
26 x_dest_subinv OUT NOCOPY VARCHAR2,
27 x_locator_id OUT NOCOPY NUMBER,
28 x_locator OUT NOCOPY VARCHAR2,
29 x_dest_org_id OUT NOCOPY NUMBER,
30 x_dest_org_code OUT NOCOPY VARCHAR2,
31 x_dest_type_code OUT NOCOPY VARCHAR2,
32 x_deliver_to_person_id OUT NOCOPY NUMBER,
33 x_deliver_to_person OUT NOCOPY VARCHAR2
34 ) IS
35 /*
36 ** Procedure validates whether ship-to location, deliver-to location,
37 ** destination subinventory, destination organization and deliver-to person
38 ** specified as defaults on the purchase order are active for the transaction
39 ** date. If they are not active, they are nulled out.
40 ** If the destination of an item is inventory, then look up default locator
41 ** for that subinventory for that line item.
42 */
43 x_progress VARCHAR2(3) := NULL;
44 x_temp VARCHAR2(50) := NULL;
45 ship_to_loc_id NUMBER;
46 deliver_to_loc_id NUMBER;
47 dest_subinv VARCHAR2(10);
48 dest_org_id NUMBER;
49 dest_type_code NUMBER;
50 deliver_to_person_id NUMBER;
51 x_ship_head_id NUMBER;
52 x_active_date DATE;
53 BEGIN
54 IF (x_receipt_source_code = 'VENDOR') THEN
55 x_progress := '010';
56
57 SELECT deliver_to_location_id,
58 deliver_to_person_id,
59 destination_subinventory,
60 destination_organization_id,
61 destination_type_code
62 INTO deliver_to_loc_id,
63 deliver_to_person_id,
64 dest_subinv,
65 dest_org_id,
66 dest_type_code
67 FROM po_distributions
68 WHERE po_distribution_id = x_dist_id;
69
70 x_progress := '020';
71
72 SELECT ship_to_location_id
73 INTO ship_to_loc_id
74 FROM po_line_locations
75 WHERE line_location_id = x_line_loc_id;
76 ELSE
77 x_progress := '030';
78
79 SELECT deliver_to_location_id,
80 deliver_to_person_id,
81 to_subinventory,
82 to_organization_id,
83 destination_type_code,
84 shipment_header_id
85 INTO deliver_to_loc_id,
86 deliver_to_person_id,
87 dest_subinv,
88 dest_org_id,
89 dest_type_code,
90 x_ship_head_id
91 FROM rcv_shipment_lines
92 WHERE shipment_line_id = x_ship_line_id;
93
94 x_progress := '040';
95
96 SELECT ship_to_location_id
97 INTO ship_to_loc_id
98 FROM rcv_shipment_headers
99 WHERE shipment_header_id = x_ship_head_id;
100 END IF;
101
102 -- Validate ship_to_location_id
103
104 x_progress := '040';
105
106 SELECT location_code
107 INTO x_ship_to_loc_code
108 FROM hr_locations
109 WHERE NVL(inventory_organization_id, x_org_id) = x_org_id
110 AND ( inactive_date IS NULL
111 OR inactive_date > x_trx_date)
112 AND location_id = ship_to_loc_id;
113
114 IF (SQL%NOTFOUND) THEN
115 x_ship_to_loc_id := NULL;
116 x_ship_to_loc_code := NULL;
117 ELSE
118 x_ship_to_loc_id := ship_to_loc_id;
119 END IF;
120
121 -- Validate deliver_to_location
122
123 x_progress := '050';
124
125 SELECT location_code
126 INTO x_deliver_to_loc_code
127 FROM hr_locations
128 WHERE NVL(inventory_organization_id, x_org_id) = x_org_id
129 AND ( inactive_date IS NULL
130 OR inactive_date > x_trx_date)
131 AND location_id = deliver_to_loc_id;
132
133 IF (SQL%NOTFOUND) THEN
134 x_deliver_to_loc_id := NULL;
135 x_deliver_to_loc_code := NULL;
136 ELSE
137 x_deliver_to_loc_id := deliver_to_loc_id;
138 END IF;
139
140 -- Validate destination_subinventory
141
142 x_progress := '060';
143
144 SELECT 'Check to see if subinventory is valid'
145 INTO x_temp
146 FROM mtl_secondary_inventories
147 WHERE ( disable_date IS NULL
148 OR disable_date > x_trx_date)
149 AND organization_id = x_org_id
150 AND secondary_inventory_name = dest_subinv
151 AND ( (x_item_id IS NULL)
152 OR ( x_item_id IS NOT NULL
153 AND EXISTS(SELECT 'valid subinventory'
154 FROM mtl_system_items msi
155 WHERE msi.organization_id = x_org_id
156 AND msi.inventory_item_id = x_item_id
157 AND ( msi.restrict_subinventories_code = 2
158 OR ( msi.restrict_subinventories_code = 1
159 AND EXISTS(SELECT 'valid subinventory'
160 FROM mtl_item_sub_inventories mis
161 WHERE mis.organization_id = x_org_id
162 AND mis.inventory_item_id = x_item_id
163 AND mis.secondary_inventory = secondary_inventory_name))))
164 )
165 );
166
167 IF (SQL%NOTFOUND) THEN
168 x_dest_subinv := NULL;
169 ELSE
170 x_dest_subinv := dest_subinv;
171 END IF;
172
173 -- Validate destination_organization
174
175 x_progress := '070';
176
177 --perf bugfix 5217401
178 SELECT mp.organization_code
179 INTO x_dest_org_code
180 FROM HR_ORGANIZATION_UNITS HOU,
181 MTL_PARAMETERS MP
182 WHERE HOU.ORGANIZATION_ID = MP.ORGANIZATION_ID
183 AND HOU.organization_id = dest_org_id
184 AND ( HOU.DATE_TO is NULL OR HOU.DATE_To > x_trx_date);
185
186 IF (SQL%NOTFOUND) THEN
187 x_dest_org_id := NULL;
188 x_dest_org_code := NULL;
189 ELSE
190 x_dest_org_id := dest_org_id;
191 END IF;
192
193 -- Validate deliver_to_person
194
195 x_progress := '080';
196
197 SELECT full_name
198 INTO x_deliver_to_person
199 FROM hr_employees_current_v
200 WHERE ( inactive_date IS NULL
201 OR inactive_date > x_trx_date)
202 AND employee_id = deliver_to_person_id;
203
204 IF (SQL%NOTFOUND) THEN
205 x_deliver_to_person_id := NULL;
206 x_deliver_to_person := NULL;
207 ELSE
208 x_deliver_to_person_id := deliver_to_person_id;
209 END IF;
210
211 -- Validate locator for INVENTORY destination
212
213 x_progress := '090';
214
215 IF (dest_type_code = 'INVENTORY') THEN
216 --call Thomas's get_default_locator, val_locator routines
217 NULL;
218 END IF;
219 EXCEPTION
220 WHEN OTHERS THEN
221 po_message_s.sql_error('val_destination_info',
222 x_progress,
223 SQLCODE
224 );
225 RAISE;
226 END val_destination_info;
227
228 /*===========================================================================
229
230 PROCEDURE NAME: get_receiving_controls
231
232 ===========================================================================*/
233 PROCEDURE get_receiving_controls(
234 x_line_loc_id IN NUMBER,
235 x_item_id IN NUMBER,
236 x_vendor_id IN NUMBER,
237 x_org_id IN NUMBER,
238 x_enforce_ship_to_loc IN OUT NOCOPY VARCHAR2,
239 x_allow_substitutes IN OUT NOCOPY VARCHAR2,
240 x_routing_id IN OUT NOCOPY NUMBER,
241 x_qty_rcv_tolerance IN OUT NOCOPY NUMBER,
242 x_qty_rcv_exception IN OUT NOCOPY VARCHAR2,
243 x_days_early_receipt IN OUT NOCOPY NUMBER,
244 x_days_late_receipt IN OUT NOCOPY NUMBER,
245 x_rcv_date_exception IN OUT NOCOPY VARCHAR2,
246 p_payment_type IN VARCHAR2 DEFAULT NULL
247 ) IS
248 x_progress VARCHAR2(3) := NULL;
249 l_routing_name rcv_routing_headers.routing_name%TYPE; -- <BUG 3365446>
250 BEGIN
251 -- <BUG 3365446 START>
252 --
253 rcv_core_s.get_receiving_controls(p_order_type_lookup_code => NULL,
254 p_purchase_basis => NULL,
255 p_line_location_id => x_line_loc_id,
256 p_item_id => x_item_id,
257 p_org_id => x_org_id,
258 p_vendor_id => x_vendor_id,
259 x_enforce_ship_to_loc_code => x_enforce_ship_to_loc,
260 x_allow_substitute_receipts => x_allow_substitutes,
261 x_routing_id => x_routing_id,
262 x_routing_name => l_routing_name,
263 x_qty_rcv_tolerance => x_qty_rcv_tolerance,
264 x_qty_rcv_exception_code => x_qty_rcv_exception,
265 x_days_early_receipt_allowed => x_days_early_receipt,
266 x_days_late_receipt_allowed => x_days_late_receipt,
267 x_receipt_days_exception_code => x_rcv_date_exception,
268 p_payment_type => p_payment_type
269 );
270 -- <BUG 3365446 END>
271
272 EXCEPTION
273 WHEN OTHERS THEN
274 po_message_s.sql_error('get_receiving_controls',
275 x_progress,
276 SQLCODE
277 );
278 RAISE;
279 END get_receiving_controls;
280
281 ------------------------------------------------------------------<BUG 3365446>
282 -------------------------------------------------------------------------------
283 --Start of Comments
284 --Name: get_receiving_controls
285 --Pre-reqs:
286 -- None.
287 --Modifies:
288 -- None.
289 --Locks:
290 -- None.
291 --Function:
292 --
293 -- (Overloaded procedure)
294 -- Retrieves default Receiving Controls according to the following hierarchy:
295 --
296 -- 1) PO_LINE_LOCATIONS_ALL (...from the PO Shipment)
297 -- 2) MTL_SYSTEM_ITEMS (...from the Item Master definition)
298 -- 3) PO_VENDORS (...from the Supplier defaults)
299 -- 4) RCV_PARAMETERS (...from the Receiving Controls Setup)
300 --
301 -- For each Receiving Control not gotten at the first level, we will try to
302 -- retrieve a value for it from the next level, and so on.
303 --
304 -- For Services lines (i.e. 'FIXED PRICE/SERVICES','*/TEMP LABOR'), we will
305 -- directly assign values for most of the parameters regardless of what are
306 -- specified in the various setups:
307 --
308 -- FIXED PRICE/SERVICES * / TEMP LABOR
309 -- -------------------- --------------------
310 -- x_enforce_ship_to_loc_code 'NONE' 'NONE'
311 -- x_allow_substitute_receipts NULL NULL
312 -- x_routing_id 3 3
313 -- x_routing_name 'Direct Delivery' 'Direct Delivery'
314 -- x_qty_rcv_tolerance <default hierarchy> <default hierarchy>
315 -- x_qty_rcv_exception_code <default hierarchy> <default hierarchy>
316 -- x_days_early_receipt_allowed <default hierarchy> NULL
317 -- x_days_late_receipt_allowed <default hierarchy> NULL
318 -- x_receipt_days_exception_code <default hierarchy> NULL
319 --
320 --Parameters:
321 --IN:
322 --p_order_type_lookup_code
323 -- Value basis of Shipment on which Receiving Controls will be defaulted.
324 -- If NULL, Shipment will be treated as non-Fixed Price line.
325 --p_purchase_basis
326 -- Purchase basis of Shipment on which Receiving Controls will be defaulted.
327 -- If NULL, Shipment will be treated as non-Temp Labor line.
328 --p_line_location_id
329 -- Shipment ID to get values from PO.
330 --p_item_id
331 -- Item ID (used with Org ID) to get default from Item Master definition.
332 --p_org_id
333 -- Org ID (used with Item ID) to get default from Item Master definition.
334 --p_vendor_id
335 -- Supplier ID to get default from Supplier Setup.
336 --p_drop_ship_flag := 'N'
337 -- Drop Ship flag. If the Shipment is Drop Ship, Routing name is set to
338 -- 'Direct Delivery' and x_routing_id = 3.
339 --OUT:
340 --x_routing_id
341 --x_routing_name
342 --x_qty_rcv_tolerance
343 --x_qty_rcv_exception_code
344 --x_days_early_receipt_allowed
345 --x_days_late_receipt_allowed
346 --x_receipt_days_exception_code
347 --Testing:
348 -- None.
349 --End of Comments
350 -------------------------------------------------------------------------------
351 -------------------------------------------------------------------------------
352 PROCEDURE get_receiving_controls(
353 p_order_type_lookup_code IN VARCHAR2,
354 p_purchase_basis IN VARCHAR2,
355 p_line_location_id IN NUMBER,
356 p_item_id IN NUMBER,
357 p_org_id IN NUMBER,
358 p_vendor_id IN NUMBER,
359 p_drop_ship_flag IN VARCHAR2 := 'N',
360 x_enforce_ship_to_loc_code OUT NOCOPY VARCHAR2,
361 x_allow_substitute_receipts OUT NOCOPY VARCHAR2,
362 x_routing_id OUT NOCOPY NUMBER,
363 x_routing_name OUT NOCOPY VARCHAR2,
364 x_qty_rcv_tolerance OUT NOCOPY NUMBER,
365 x_qty_rcv_exception_code OUT NOCOPY VARCHAR2,
366 x_days_early_receipt_allowed OUT NOCOPY NUMBER,
367 x_days_late_receipt_allowed OUT NOCOPY NUMBER,
368 x_receipt_days_exception_code OUT NOCOPY VARCHAR2,
369 p_payment_type IN VARCHAR2 DEFAULT NULL
370 ) IS
371 l_api_name VARCHAR2(30) := 'get_receiving_controls';
372 l_log_head VARCHAR2(100) := g_log_head || l_api_name;
373 l_progress VARCHAR2(3);
374 BEGIN
375 l_progress := '000';
376 po_debug.debug_begin(l_log_head);
377
378 -- PO Shipment ============================================================
379
380 IF (p_line_location_id IS NOT NULL) THEN
381 l_progress := '010';
382 po_debug.debug_stmt(l_log_head,
383 l_progress,
384 'Retrieving Receiving Controls from PO Shipment...'
385 );
386 l_progress := '020';
387 po_debug.debug_var(l_log_head,
388 l_progress,
389 'p_line_location_id',
390 p_line_location_id
391 );
392
393 BEGIN
394 SELECT enforce_ship_to_location_code,
395 allow_substitute_receipts_flag,
396 receiving_routing_id,
397 qty_rcv_tolerance,
398 qty_rcv_exception_code,
399 days_early_receipt_allowed,
400 days_late_receipt_allowed,
401 receipt_days_exception_code
402 INTO x_enforce_ship_to_loc_code,
403 x_allow_substitute_receipts,
404 x_routing_id,
405 x_qty_rcv_tolerance,
406 x_qty_rcv_exception_code,
407 x_days_early_receipt_allowed,
408 x_days_late_receipt_allowed,
409 x_receipt_days_exception_code
410 FROM po_line_locations_all
411 WHERE line_location_id = p_line_location_id;
412 EXCEPTION
413 WHEN NO_DATA_FOUND THEN
414 NULL;
415 END;
416 END IF;
417
418 -- Item Master ============================================================
419
420 IF (p_item_id IS NOT NULL) THEN
421 l_progress := '030';
422 po_debug.debug_stmt(l_log_head,
423 l_progress,
424 'Retrieving Receiving Controls from Item Master...'
425 );
426 l_progress := '040';
427 po_debug.debug_var(l_log_head,
428 l_progress,
429 'p_item_id',
430 TO_NUMBER(p_item_id)
431 );
432 l_progress := '050';
433 po_debug.debug_var(l_log_head,
434 l_progress,
435 'p_org_id',
436 p_org_id
437 );
438
439 BEGIN
440 SELECT NVL(x_enforce_ship_to_loc_code, enforce_ship_to_location_code),
441 NVL(x_allow_substitute_receipts, allow_substitute_receipts_flag),
442 NVL(x_routing_id, receiving_routing_id),
443 NVL(x_qty_rcv_tolerance, qty_rcv_tolerance),
444 NVL(x_qty_rcv_exception_code, qty_rcv_exception_code),
445 NVL(x_days_early_receipt_allowed, days_early_receipt_allowed),
446 NVL(x_days_late_receipt_allowed, days_late_receipt_allowed),
447 NVL(x_receipt_days_exception_code, receipt_days_exception_code)
448 INTO x_enforce_ship_to_loc_code,
449 x_allow_substitute_receipts,
450 x_routing_id,
451 x_qty_rcv_tolerance,
452 x_qty_rcv_exception_code,
453 x_days_early_receipt_allowed,
454 x_days_late_receipt_allowed,
455 x_receipt_days_exception_code
456 FROM mtl_system_items
457 WHERE inventory_item_id = p_item_id
458 AND NVL(organization_id, -99) = NVL(p_org_id, -99);
459 EXCEPTION
460 WHEN NO_DATA_FOUND THEN
461 NULL;
462 END;
463 END IF;
464
465 -- Supplier ===============================================================
466
467 IF (p_vendor_id IS NOT NULL) THEN
468 l_progress := '060';
469 po_debug.debug_stmt(l_log_head,
470 l_progress,
471 'Retrieving Receiving Controls from Supplier Setup...'
472 );
473 l_progress := '070';
474 po_debug.debug_var(l_log_head,
475 l_progress,
476 'p_vendor_id',
477 p_vendor_id
478 );
479
480 BEGIN
481 SELECT NVL(x_enforce_ship_to_loc_code, enforce_ship_to_location_code),
482 NVL(x_allow_substitute_receipts, allow_substitute_receipts_flag),
483 NVL(x_routing_id, receiving_routing_id),
484 NVL(x_qty_rcv_tolerance, qty_rcv_tolerance),
485 NVL(x_qty_rcv_exception_code, qty_rcv_exception_code),
486 NVL(x_days_early_receipt_allowed, days_early_receipt_allowed),
487 NVL(x_days_late_receipt_allowed, days_late_receipt_allowed),
488 NVL(x_receipt_days_exception_code, receipt_days_exception_code)
489 INTO x_enforce_ship_to_loc_code,
490 x_allow_substitute_receipts,
491 x_routing_id,
492 x_qty_rcv_tolerance,
493 x_qty_rcv_exception_code,
494 x_days_early_receipt_allowed,
495 x_days_late_receipt_allowed,
496 x_receipt_days_exception_code
497 FROM po_vendors
498 WHERE vendor_id = p_vendor_id;
499 EXCEPTION
500 WHEN NO_DATA_FOUND THEN
501 NULL;
502 END;
503 END IF;
504
505 -- Receiving Controls Setup ===============================================
506
507 l_progress := '080';
508 po_debug.debug_stmt(l_log_head,
509 l_progress,
510 'Retrieving Receiving Controls from Default Setup...'
511 );
512
513 BEGIN
514 SELECT NVL(x_enforce_ship_to_loc_code, enforce_ship_to_location_code),
515 NVL(x_allow_substitute_receipts, allow_substitute_receipts_flag),
516 NVL(x_routing_id, receiving_routing_id),
517 NVL(x_qty_rcv_tolerance, qty_rcv_tolerance),
518 NVL(x_qty_rcv_exception_code, qty_rcv_exception_code),
519 NVL(x_days_early_receipt_allowed, days_early_receipt_allowed),
520 NVL(x_days_late_receipt_allowed, days_late_receipt_allowed),
521 NVL(x_receipt_days_exception_code, receipt_days_exception_code)
522 INTO x_enforce_ship_to_loc_code,
523 x_allow_substitute_receipts,
524 x_routing_id,
525 x_qty_rcv_tolerance,
526 x_qty_rcv_exception_code,
527 x_days_early_receipt_allowed,
528 x_days_late_receipt_allowed,
529 x_receipt_days_exception_code
530 FROM rcv_parameters
531 WHERE organization_id = p_org_id;
532 EXCEPTION
533 WHEN NO_DATA_FOUND THEN
534 NULL;
535 END;
536
537 -- Default Values =========================================================
538
539 l_progress := '090';
540 po_debug.debug_stmt(l_log_head,
541 l_progress,
542 'Assigning default values to any unassigned values...'
543 );
544 l_progress := '100';
545 po_debug.debug_var(l_log_head,
546 l_progress,
547 'p_order_type_lookup_code',
548 p_order_type_lookup_code
549 );
550 l_progress := '110';
551 po_debug.debug_var(l_log_head,
552 l_progress,
553 'p_purchase_basis',
554 p_purchase_basis
555 );
556
557 -- Fixed Price/Services Line Types
558 --
559 IF ( (p_order_type_lookup_code = 'FIXED PRICE')
560 AND (p_purchase_basis = 'SERVICES')) THEN
561 SELECT 'NONE',
562 NULL,
563 3 -- 'Direct Delivery'
564 ,
565 NVL(x_qty_rcv_tolerance, 0),
566 NVL(x_qty_rcv_exception_code, 'NONE'),
567 NVL(x_days_early_receipt_allowed, 0),
568 NVL(x_days_late_receipt_allowed, 0),
569 NVL(x_receipt_days_exception_code, 'NONE')
570 INTO x_enforce_ship_to_loc_code,
571 x_allow_substitute_receipts,
572 x_routing_id,
573 x_qty_rcv_tolerance,
574 x_qty_rcv_exception_code,
575 x_days_early_receipt_allowed,
576 x_days_late_receipt_allowed,
577 x_receipt_days_exception_code
578 FROM DUAL;
579 -- Temp Labor Line Types
580 --
581 ELSIF(p_purchase_basis = 'TEMP LABOR') THEN
582 SELECT 'NONE',
583 NULL,
584 3 -- 'Direct Delivery'
585 ,
586 NVL(x_qty_rcv_tolerance, 0),
587 NVL(x_qty_rcv_exception_code, 'NONE'),
588 NULL,
589 NULL,
590 NULL
591 INTO x_enforce_ship_to_loc_code,
592 x_allow_substitute_receipts,
593 x_routing_id,
594 x_qty_rcv_tolerance,
595 x_qty_rcv_exception_code,
596 x_days_early_receipt_allowed,
597 x_days_late_receipt_allowed,
598 x_receipt_days_exception_code
599 FROM DUAL;
600 -- All other Line Types
601 --
602 ELSE
603 SELECT NVL(x_enforce_ship_to_loc_code, 'NONE'),
604 NVL(x_allow_substitute_receipts, 'N'),
605 x_routing_id,
606 NVL(x_qty_rcv_tolerance, 0),
607 NVL(x_qty_rcv_exception_code, 'NONE'),
608 NVL(x_days_early_receipt_allowed, 0),
609 NVL(x_days_late_receipt_allowed, 0),
610 NVL(x_receipt_days_exception_code, 'NONE')
611 INTO x_enforce_ship_to_loc_code,
612 x_allow_substitute_receipts,
613 x_routing_id,
614 x_qty_rcv_tolerance,
615 x_qty_rcv_exception_code,
616 x_days_early_receipt_allowed,
617 x_days_late_receipt_allowed,
618 x_receipt_days_exception_code
619 FROM DUAL;
620 END IF;
621
622 -- Drop Shipments
623 --
624 IF (p_drop_ship_flag = 'Y') THEN
625 x_routing_id := 3;
626 END IF;
627
628 /* R12 Complex Work. Bug 4484236.
629 * For Complex work POs receipt_routing is always
630 * direct delivery. In addition,overreceipt tolerance is 0
631 * for the payment_type MILESTONE.
632 */
633
634 If (p_payment_type is not null) then
635 x_routing_id := 3;
636
637 If (p_payment_type = 'MILESTONE') then
638 x_qty_rcv_tolerance := 0;
639 end if;
640 END IF;
641
642
643 -- Derive Routing Name ====================================================
644
645 IF (x_routing_id IS NOT NULL) THEN
646 l_progress := '120';
647 po_debug.debug_stmt(l_log_head,
648 l_progress,
649 'Looking up routing name...'
650 );
651 l_progress := '130';
652 po_debug.debug_var(l_log_head,
653 l_progress,
654 'x_routing_id',
655 x_routing_id
656 );
657
658 BEGIN
659 SELECT routing_name
660 INTO x_routing_name
661 FROM rcv_routing_headers
662 WHERE routing_header_id = x_routing_id;
663 EXCEPTION
664 WHEN NO_DATA_FOUND THEN
665 x_routing_name := NULL;
666 END;
667 END IF;
668
669 --=========================================================================
670
671 l_progress := '140';
672 po_debug.debug_end(l_log_head);
673 EXCEPTION
674 WHEN OTHERS THEN
675 po_debug.debug_exc(l_log_head, l_progress);
676 RAISE;
677 END get_receiving_controls;
678
679 /*===========================================================================
680
681 FUNCTION NAME: val_unique_receipt_num
682
683 ===========================================================================*/
684 FUNCTION val_unique_receipt_num(
685 x_receipt_num IN VARCHAR2
686 )
687 RETURN BOOLEAN IS
688 /*
689 ** Function checks if the receipt number passed in already exists in
690 ** rcv_shipment_headers. If is does, then it returns a value of FALSE. If
691 ** it doesn't, then it checks if it exists in po_history_receipts. If it
692 ** does then it returns a value of FALSE. If the receipt number doesn't
693 ** exist in either table, it returns a value of TRUE.
694 */
695 x_progress VARCHAR2(3) := NULL;
696 dup_count NUMBER := 0;
697 BEGIN
698 x_progress := '010';
699
700 SELECT COUNT(1)
701 INTO dup_count
702 FROM rcv_shipment_headers
703 WHERE receipt_num = x_receipt_num;
704
705 x_progress := '020';
706
707 IF dup_count <> 0 THEN
708 RETURN(FALSE);
709 ELSE
710 SELECT COUNT(1)
711 INTO dup_count
712 FROM po_history_receipts
713 WHERE receipt_num = x_receipt_num;
714
715 IF dup_count <> 0 THEN
716 RETURN(FALSE);
717 END IF;
718 END IF;
719
720 RETURN(TRUE);
721 EXCEPTION
722 WHEN OTHERS THEN
723 po_message_s.sql_error('val_unique_receipt_num',
724 x_progress,
725 SQLCODE
726 );
727 RAISE;
728 END val_unique_receipt_num;
729
730 /*===========================================================================
731
732 FUNCTION NAME: val_unique_shipment_num
733
734 ===========================================================================*/
735 FUNCTION val_unique_shipment_num(
736 x_shipment_num IN VARCHAR2,
737 x_vendor_id IN NUMBER
738 )
739 RETURN BOOLEAN IS
740 /*
741 ** Function checks if the shipment number passed in already exists in
742 ** rcv_shipment_headers for the current vendor. It returns a value of TRUE
743 ** if the shipment number passed in is unique, FALSE if it is a duplicate
744 */
745 x_progress VARCHAR2(3) := NULL;
746 dup_count NUMBER := 0;
747 BEGIN
748 x_progress := '010';
749
750 SELECT COUNT(1)
751 INTO dup_count
752 FROM rcv_shipment_headers
753 WHERE shipment_num = x_shipment_num
754 AND receipt_source_code = 'VENDOR'
755 AND vendor_id = x_vendor_id;
756
757 IF dup_count <> 0 THEN
758 RETURN(FALSE);
759 ELSE
760 RETURN(TRUE);
761 END IF;
762 EXCEPTION
763 WHEN OTHERS THEN
764 po_message_s.sql_error('val_unique_shipment_num',
765 x_progress,
766 SQLCODE
767 );
768 RAISE;
769 END val_unique_shipment_num;
770
771 /*===========================================================================
772
773 PROCEDURE NAME: get_ussgl_info
774
775 ===========================================================================*/
776 PROCEDURE get_ussgl_info(
777 x_line_location_id IN NUMBER,
778 x_ussgl_trx_code OUT NOCOPY VARCHAR2,
779 x_govt_context OUT NOCOPY VARCHAR2
780 ) IS
781 /*
782 ** Procedure gets ussgl_transaction_code and government_context from
783 ** po_line_locations.
784 */
785 x_progress VARCHAR2(3) := NULL;
786 BEGIN
787 x_progress := '010';
788
789 SELECT ussgl_transaction_code,
790 government_context
791 INTO x_ussgl_trx_code,
792 x_govt_context
793 FROM po_line_locations
794 WHERE line_location_id = x_line_location_id;
795 EXCEPTION
796 WHEN OTHERS THEN
797 po_message_s.sql_error('get_ussgl_info',
798 x_progress,
799 SQLCODE
800 );
801 RAISE;
802 END get_ussgl_info;
803
804 /*===========================================================================
805
806 PROCEDURE NAME: val_po_shipment
807
808 ===========================================================================*/
809 PROCEDURE val_po_shipment(
810 x_trx_type IN VARCHAR2,
811 x_parent_id IN NUMBER,
812 x_receipt_source_code IN VARCHAR2,
813 x_parent_trx_type IN VARCHAR2,
814 x_grand_parent_id IN NUMBER,
815 x_correction_type IN VARCHAR2,
816 x_available_quantity IN OUT NOCOPY NUMBER,
817 x_tolerable_qty IN OUT NOCOPY NUMBER,
818 x_uom IN OUT NOCOPY VARCHAR2
819 ) IS
820 x_progress VARCHAR2(3) := NULL;
821 /*Bug 1548597 */
822 x_secondary_available_qty NUMBER := 0;
823 BEGIN
824 x_progress := '010';
825 /*Bug 1548597 */
826 rcv_quantities_s.get_available_quantity(x_trx_type,
827 x_parent_id,
828 x_receipt_source_code,
829 x_parent_trx_type,
830 x_grand_parent_id,
831 x_correction_type,
832 x_available_quantity,
833 x_tolerable_qty,
834 x_uom,
835 x_secondary_available_qty
836 );
837 EXCEPTION
838 WHEN OTHERS THEN
839 po_message_s.sql_error('val_po_shipment',
840 x_progress,
841 SQLCODE
842 );
843 RAISE;
844 END val_po_shipment;
845
846 /*===========================================================================
847
848 PROCEDURE NAME: val_exp_cas_func
849
850 ===========================================================================*/
851 PROCEDURE val_exp_cas_func IS
852 x_progress VARCHAR2(3) := NULL;
853 BEGIN
854 x_progress := '010';
855 EXCEPTION
856 WHEN OTHERS THEN
857 po_message_s.sql_error('val_exp_cas_func',
858 x_progress,
859 SQLCODE
860 );
861 RAISE;
862 END val_exp_cas_func;
863
864 /* ==========================================================================
865
866 PROCEDURE NAME: PO_DIST_INFO
867
868 ===========================================================================*/
869 PROCEDURE po_dist_info(
870 x_po_dist_id IN NUMBER,
871 x_wip_entity_id OUT NOCOPY NUMBER,
872 x_wip_repetitive_schedule_id OUT NOCOPY NUMBER,
873 x_wip_operation_seq_num OUT NOCOPY NUMBER,
874 x_wip_resource_seq_num OUT NOCOPY NUMBER,
875 x_wip_line_id OUT NOCOPY NUMBER,
876 x_bom_resource_id OUT NOCOPY NUMBER
877 ) IS
878 x_progress VARCHAR2(3) := NULL;
879 BEGIN
880 x_progress := 10;
881
882 SELECT wip_entity_id,
883 wip_operation_seq_num,
884 wip_resource_seq_num,
885 wip_repetitive_schedule_id,
886 wip_line_id,
887 bom_resource_id
888 INTO x_wip_entity_id,
889 x_wip_operation_seq_num,
890 x_wip_resource_seq_num,
891 x_wip_repetitive_schedule_id,
892 x_wip_line_id,
893 x_bom_resource_id
894 FROM po_distributions pod
895 WHERE pod.po_distribution_id = x_po_dist_id;
896 EXCEPTION
897 WHEN OTHERS THEN
898 po_message_s.sql_error('PO_DIST_INFO',
899 x_progress,
900 SQLCODE
901 );
902 RAISE;
903 END po_dist_info;
904
905 /* ==========================================================================
906
907 PROCEDURE NAME: get_outside_processing_info
908
909 ===========================================================================*/
910 PROCEDURE get_outside_processing_info(
911 x_po_distribution_id IN NUMBER,
912 x_organization_id IN NUMBER,
913 x_job_schedule OUT NOCOPY VARCHAR2,
914 x_operation_seq_num OUT NOCOPY VARCHAR2,
915 x_department OUT NOCOPY VARCHAR2,
916 x_wip_line OUT NOCOPY VARCHAR2,
917 x_bom_resource_id OUT NOCOPY NUMBER,
918 x_po_operation_seq_num OUT NOCOPY NUMBER,
919 x_po_resource_seq_num OUT NOCOPY NUMBER
920 ) IS
921 x_wip_entity_id NUMBER(10);
922 x_wip_repetitive_schedule_id NUMBER(10);
923 x_wip_operation_seq_num NUMBER(10);
924 x_wip_resource_seq_num NUMBER(10);
925 x_wip_line_id NUMBER(10);
926 x_progress VARCHAR2(3);
927 BEGIN
928 /*
929 ** The po_operation and resource sequence numbers are off the po
930 ** distribution and is used for inserting the transaction rather
931 ** than the operation_seq_num which is derived from the wip tables
932 ** and shows the next operation rather than the current one. This
933 ** value is used for display purposes
934 */
935 x_progress := 10;
936 po_dist_info(x_po_distribution_id,
937 x_wip_entity_id,
938 x_wip_repetitive_schedule_id,
939 x_wip_operation_seq_num,
940 x_wip_resource_seq_num,
941 x_wip_line_id,
942 x_bom_resource_id
943 );
944 x_po_operation_seq_num := x_wip_operation_seq_num;
945 x_po_resource_seq_num := x_wip_resource_seq_num;
946 x_progress := 20;
947 rcv_core_s.out_op_info(x_wip_entity_id,
948 x_organization_id,
949 x_wip_repetitive_schedule_id,
950 x_wip_operation_seq_num,
951 x_wip_resource_seq_num,
952 x_job_schedule,
953 x_operation_seq_num,
954 x_department
955 );
956 x_progress := 30;
957
958 IF NVL(x_wip_line_id, 0) <> 0 THEN
959 rcv_core_s.wip_line_info(x_wip_line_id,
960 x_organization_id,
961 x_wip_line
962 );
963 END IF;
964 EXCEPTION
965 WHEN OTHERS THEN
966 po_message_s.sql_error('OUT_INFO',
967 x_progress,
968 SQLCODE
969 );
970 RAISE;
971 END get_outside_processing_info;
972
973 /* ==========================================================================
974
975 PROCEDURE NAME: OUT_OP_INFO
976
977 ===========================================================================*/
978 PROCEDURE out_op_info(
979 x_wip_entity_id IN NUMBER,
980 x_organization_id IN NUMBER,
981 x_wip_repetitive_schedule_id IN NUMBER,
982 x_wip_operation_seq_num IN NUMBER,
983 x_wip_resource_seq_num IN NUMBER,
984 x_job_schedule_dsp OUT NOCOPY VARCHAR2,
985 x_op_seq_num_dsp OUT NOCOPY VARCHAR2,
986 x_department_code OUT NOCOPY VARCHAR2
987 ) IS
988 x_progress VARCHAR2(3) := NULL;
989 BEGIN
990 x_progress := 10;
991
992 --Bug# 2000013 togeorge 09/18/2001
993 --Eam: Split the following sql to 3 different sqls because eAM w/o would
994 -- not have resource information and this sql will fail.
995 /*
996 select we.wip_entity_name,
997 wn.operation_seq_num,
998 bd.department_code
999 into x_job_schedule_dsp,
1000 x_op_seq_num_dsp,
1001 x_department_code
1002 from wip_entities we,
1003 bom_departments bd,
1004 wip_operation_resources wr,
1005 wip_operations wn,
1006 wip_operations wo
1007 where wo.wip_entity_id = x_wip_entity_id
1008 and wo.organization_id = x_organization_id
1009 and nvl(wo.repetitive_schedule_id, -1) =
1010 nvl(x_wip_repetitive_schedule_id, -1)
1011 and wo.operation_seq_num = x_wip_operation_seq_num
1012 and wr.wip_entity_id = x_wip_entity_id
1013 and wr.organization_id = x_organization_id
1014 and nvl(wr.repetitive_schedule_id, -1) =
1015 nvl(x_wip_repetitive_schedule_id, -1)
1016 and wr.operation_seq_num = x_wip_operation_seq_num
1017 and wr.resource_seq_num = x_wip_resource_seq_num
1018 and wn.wip_entity_id = x_wip_entity_id
1019 and wn.organization_id = x_organization_id
1020 and nvl(wn.repetitive_schedule_id, -1) =
1021 nvl(x_wip_repetitive_schedule_id, -1)
1022 and wn.operation_seq_num = decode(wr.autocharge_type,
1023 4, nvl(wo.next_operation_seq_num,
1024 wo.operation_seq_num),wo.operation_seq_num)
1025 and bd.department_id = wn.department_id
1026 and we.wip_entity_id = x_wip_entity_id
1027 and we.organization_id = x_organization_id ;
1028 */
1029 IF x_wip_entity_id IS NOT NULL THEN
1030 BEGIN
1031 SELECT we.wip_entity_name job
1032 INTO x_job_schedule_dsp
1033 FROM wip_entities we
1034 WHERE we.wip_entity_id = x_wip_entity_id
1035 AND we.organization_id = x_organization_id;
1036 EXCEPTION
1037 WHEN OTHERS THEN
1038 x_job_schedule_dsp := NULL;
1039 END;
1040 END IF;
1041
1042 IF x_wip_entity_id IS NOT NULL
1043 AND x_wip_operation_seq_num IS NOT NULL THEN
1044 BEGIN
1045 SELECT wn.operation_seq_num SEQUENCE,
1046 bd.department_code department
1047 INTO x_op_seq_num_dsp,
1048 x_department_code
1049 FROM bom_departments bd,
1050 wip_operation_resources wr,
1051 wip_operations wn,
1052 wip_operations wo
1053 WHERE wo.wip_entity_id = x_wip_entity_id
1054 AND wo.organization_id = x_organization_id
1055 AND NVL(wo.repetitive_schedule_id, -1) = NVL(x_wip_repetitive_schedule_id, -1)
1056 AND wo.operation_seq_num = x_wip_operation_seq_num
1057 AND wr.wip_entity_id = x_wip_entity_id
1058 AND wr.organization_id = x_organization_id
1059 AND NVL(wr.repetitive_schedule_id, -1) = NVL(x_wip_repetitive_schedule_id, -1)
1060 AND wr.operation_seq_num = x_wip_operation_seq_num
1061 AND wr.resource_seq_num = x_wip_resource_seq_num
1062 AND wn.wip_entity_id = x_wip_entity_id
1063 AND wn.organization_id = x_organization_id
1064 AND NVL(wn.repetitive_schedule_id, -1) = NVL(x_wip_repetitive_schedule_id, -1)
1065 AND wn.operation_seq_num = DECODE(wr.autocharge_type,
1066 4, NVL(wo.next_operation_seq_num, wo.operation_seq_num),
1067 wo.operation_seq_num
1068 )
1069 AND bd.department_id = wn.department_id;
1070 EXCEPTION
1071 WHEN NO_DATA_FOUND THEN
1072 --for EAM workorders the above sql would raise no_data_found.
1073 --find department code and sequence with out touching resource table.
1074 BEGIN
1075 SELECT bd.department_code department
1076 INTO x_department_code
1077 FROM bom_departments bd,
1078 wip_operations wn
1079 WHERE wn.wip_entity_id = x_wip_entity_id
1080 AND wn.organization_id = x_organization_id
1081 AND NVL(wn.repetitive_schedule_id, -1) = NVL(x_wip_repetitive_schedule_id, -1)
1082 AND bd.department_id = wn.department_id;
1083 EXCEPTION
1084 WHEN OTHERS THEN
1085 x_department_code := NULL;
1086 END;
1087
1088 BEGIN
1089 SELECT wo.operation_seq_num SEQUENCE
1090 INTO x_op_seq_num_dsp
1091 FROM wip_operations wo
1092 WHERE wo.wip_entity_id = x_wip_entity_id
1093 AND wo.organization_id = x_organization_id
1094 AND NVL(wo.repetitive_schedule_id, -1) = NVL(x_wip_repetitive_schedule_id, -1)
1095 AND wo.operation_seq_num = x_wip_operation_seq_num;
1096 EXCEPTION
1097 WHEN OTHERS THEN
1098 x_op_seq_num_dsp := NULL;
1099 END;
1100 WHEN OTHERS THEN
1101 x_op_seq_num_dsp := NULL;
1102 x_department_code := NULL;
1103 END;
1104 END IF;
1105 EXCEPTION
1106 WHEN OTHERS THEN
1107 po_message_s.sql_error('OUT_OP_INFO',
1108 x_progress,
1109 SQLCODE
1110 );
1111 RAISE;
1112 END out_op_info;
1113
1114 /* ==========================================================================
1115
1116 PROCEDURE NAME: WIP_LINE_INFO
1117
1118 ===========================================================================*/
1119 PROCEDURE wip_line_info(
1120 x_wip_line_id IN NUMBER,
1121 x_org_id IN NUMBER,
1122 x_wip_line_dsp OUT NOCOPY VARCHAR2
1123 ) IS
1124 x_progress VARCHAR2(3) := NULL;
1125 BEGIN
1126 x_progress := 10;
1127
1128 SELECT wl.line_code
1129 INTO x_wip_line_dsp
1130 FROM wip_lines wl
1131 WHERE wl.organization_id = x_org_id
1132 AND wl.line_id = x_wip_line_id;
1133 EXCEPTION
1134 WHEN OTHERS THEN
1135 po_message_s.sql_error('WIP_LINE_INFO',
1136 x_progress,
1137 SQLCODE
1138 );
1139 RAISE;
1140 END wip_line_info;
1141
1142 /* ==========================================================================
1143
1144 FUNCTION NAME: note_info
1145
1146 ===========================================================================*/
1147 FUNCTION note_info(
1148 x_note_attribute VARCHAR2,
1149 x_note_table_name VARCHAR2,
1150 x_note_column_name VARCHAR2,
1151 x_foreign_id NUMBER
1152 )
1153 RETURN NUMBER IS
1154 x_progress VARCHAR2(3) := NULL;
1155 x_note_count NUMBER;
1156 BEGIN
1157 x_progress := 10;
1158
1159 SELECT COUNT(pon.po_note_id)
1160 INTO x_note_count
1161 FROM po_note_references ponr,
1162 po_notes pon,
1163 po_usage_attributes poua
1164 WHERE ponr.po_note_id = pon.po_note_id
1165 AND pon.usage_id = poua.usage_id
1166 AND poua.note_attribute = x_note_attribute
1167 AND ponr.table_name = x_note_table_name
1168 AND ponr.column_name = x_note_column_name
1169 AND ponr.foreign_id = x_foreign_id;
1170
1171 RETURN(x_note_count);
1172 EXCEPTION
1173 WHEN NO_DATA_FOUND THEN
1174 NULL;
1175 RETURN(0);
1176 WHEN OTHERS THEN
1177 po_message_s.sql_error('NOTE_INFO',
1178 x_progress,
1179 SQLCODE
1180 );
1181 RAISE;
1182 END note_info;
1183
1184 /* ==========================================================================
1185
1186 FUNCTION NAME: get_note_count
1187
1188 ===========================================================================*/
1189 FUNCTION get_note_count(
1190 x_header_id IN NUMBER,
1191 x_line_id IN NUMBER,
1192 x_location_id IN NUMBER,
1193 x_po_line_id IN NUMBER,
1194 x_po_release_id IN NUMBER,
1195 x_po_header_id IN NUMBER,
1196 x_item_id IN NUMBER
1197 )
1198 RETURN NUMBER IS
1199 x_progress VARCHAR2(3) := NULL;
1200 x_ret_note_cnt NUMBER;
1201 BEGIN
1202 x_progress := 10;
1203 x_ret_note_cnt := note_info('RVCRC',
1204 'RCV_SHIPMENT_HEADERS',
1205 'SHIPMENT_HEADER_ID',
1206 x_header_id
1207 );
1208
1209 IF x_ret_note_cnt > 0 THEN
1210 RETURN(x_ret_note_cnt);
1211 END IF;
1212
1213 x_progress := 20;
1214 x_ret_note_cnt := note_info('RVCRC',
1215 'RCV_SHIPMENT_LINES',
1216 'SHIPMENT_LINE_ID',
1217 x_line_id
1218 );
1219
1220 IF x_ret_note_cnt > 0 THEN
1221 RETURN(x_ret_note_cnt);
1222 END IF;
1223
1224 x_progress := 30;
1225 x_ret_note_cnt := note_info('RVCRC',
1226 'PO_LINE_LOCATIONS',
1227 'LINE_LOCATION_ID',
1228 x_location_id
1229 );
1230
1231 IF x_ret_note_cnt > 0 THEN
1232 RETURN(x_ret_note_cnt);
1233 END IF;
1234
1235 x_progress := 40;
1236 x_ret_note_cnt := note_info('RVCRC',
1237 'PO_LINES',
1238 'PO_LINE_ID',
1239 x_po_line_id
1240 );
1241
1242 IF x_ret_note_cnt > 0 THEN
1243 RETURN(x_ret_note_cnt);
1244 END IF;
1245
1246 x_progress := 50;
1247 x_ret_note_cnt := note_info('RVCRC',
1248 'PO_RELEASES',
1249 'PO_RELEASE_ID',
1250 x_po_release_id
1251 );
1252
1253 IF x_ret_note_cnt > 0 THEN
1254 RETURN(x_ret_note_cnt);
1255 END IF;
1256
1257 x_progress := 60;
1258 x_ret_note_cnt := note_info('RVCRC',
1259 'PO_HEADERS',
1260 'PO_HEADER_ID',
1261 x_po_header_id
1262 );
1263
1264 IF x_ret_note_cnt > 0 THEN
1265 RETURN(x_ret_note_cnt);
1266 END IF;
1267
1268 x_progress := 70;
1269 x_ret_note_cnt := note_info('ITMIT',
1270 'MTL_SYSTEM_ITEMS',
1271 'INVENTORY_ITEM_ID',
1272 x_item_id
1273 );
1274 RETURN(x_ret_note_cnt);
1275 EXCEPTION
1276 WHEN NO_DATA_FOUND THEN
1277 NULL;
1278 RETURN(0);
1279 WHEN OTHERS THEN
1280 po_message_s.sql_error('NOTE',
1281 x_progress,
1282 SQLCODE
1283 );
1284 RAISE;
1285 END get_note_count;
1286
1287 /* ==========================================================================
1288
1289 PROCEDURE NAME: DERIVE_SHIPMENT_INFO
1290
1291 ===========================================================================*/
1292 PROCEDURE derive_shipment_info(
1293 x_header_record IN OUT NOCOPY rcv_shipment_header_sv.headerrectype
1294 ) IS
1295 BEGIN
1296 IF x_header_record.header_record.receipt_header_id IS NOT NULL THEN
1297 IF (g_asn_debug = 'Y') THEN
1298 asn_debug.put_line('Need to put a cursor to retrieve other values');
1299 asn_debug.put_line('Shipment header Id has been provided');
1300 END IF;
1301
1302 RETURN;
1303 END IF;
1304
1305 -- Check that the shipment_num is not null
1306
1307 IF ( x_header_record.header_record.shipment_num IS NULL
1308 OR x_header_record.header_record.shipment_num = '0'
1309 OR REPLACE(x_header_record.header_record.shipment_num,
1310 ' ',
1311 ''
1312 ) IS NULL) THEN
1313 IF (g_asn_debug = 'Y') THEN
1314 asn_debug.put_line('Cannot derive the shipment_header_id at this point');
1315 END IF;
1316
1317 RETURN;
1318 END IF;
1319
1320 -- Derive the shipment_header_id only for transaction_type = CANCEL
1321
1322 /*
1323 * BUGNO: 1708017
1324 * The where clause used to have organization_id =
1325 * X_header_record.header_record.ship_to_organization_id
1326 * This used to be populated with ship_to_organization_id.
1327 * Now this is populated as null since it is supposed to
1328 * be from organization_id. So changed it to ship_to_org_id.
1329 */
1330 IF x_header_record.header_record.transaction_type = 'CANCEL'
1331 AND x_header_record.header_record.receipt_header_id IS NULL THEN
1332 BEGIN
1333 SELECT MAX(shipment_header_id) -- if we ever have 2 shipments with the same combo
1334 INTO x_header_record.header_record.receipt_header_id
1335 FROM rcv_shipment_headers
1336 WHERE NVL(vendor_site_id, -9999) = NVL(x_header_record.header_record.vendor_site_id, -9999)
1337 AND vendor_id = x_header_record.header_record.vendor_id
1338 AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
1339 AND shipment_num = x_header_record.header_record.shipment_num
1340 AND shipped_date >= ADD_MONTHS(x_header_record.header_record.shipped_date, -12);
1341 EXCEPTION
1342 WHEN OTHERS THEN
1343 IF (g_asn_debug = 'Y') THEN
1344 asn_debug.put_line(SQLERRM);
1345 END IF;
1346 END;
1347
1348 RETURN;
1349 END IF;
1350 EXCEPTION
1351 WHEN OTHERS THEN
1352 rcv_error_pkg.set_sql_error_message('derive_shipment_info', '000');
1353 x_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1354 x_header_record.error_record.error_message := rcv_error_pkg.get_last_message;
1355 END derive_shipment_info;
1356
1357 /* ==========================================================================
1358
1359 PROCEDURE NAME: DEFAULT_SHIPMENT_INFO
1360
1361 ===========================================================================*/
1362 PROCEDURE default_shipment_info(
1363 x_header_record IN OUT NOCOPY rcv_shipment_header_sv.headerrectype
1364 ) IS
1365 x_count NUMBER;
1366 BEGIN
1367 -- no need to derive shipment_header_id if it is already provided
1368
1369 IF x_header_record.header_record.receipt_header_id IS NOT NULL THEN
1370 IF (g_asn_debug = 'Y') THEN
1371 asn_debug.put_line('Shipment header Id has been provided');
1372 END IF;
1373
1374 RETURN;
1375 END IF;
1376
1377 -- Check for shipment number which is null, blank , zero
1378
1379 IF x_header_record.header_record.asn_type IN('ASN', 'ASBN')
1380 AND ( x_header_record.header_record.shipment_num IS NULL
1381 OR x_header_record.header_record.shipment_num = '0'
1382 OR REPLACE(x_header_record.header_record.shipment_num,
1383 ' ',
1384 ''
1385 ) IS NULL) THEN
1386 IF (g_asn_debug = 'Y') THEN
1387 asn_debug.put_line('Shipment num is still null');
1388 END IF;
1389
1390 RETURN;
1391 END IF;
1392
1393 -- Derive the shipment_header_id based on the shipment_num for transaction_type = CANCEL
1394
1395 /*
1396 * BUGNO: 1708017
1397 * The where clause used to have organization_id =
1398 * X_header_record.header_record.ship_to_organization_id
1399 * This used to be populated with ship_to_organization_id.
1400 * Now this is populated as null since it is supposed to
1401 * be from organization_id. So changed it to ship_to_org_id.
1402 */
1403 IF x_header_record.header_record.transaction_type = 'CANCEL'
1404 AND x_header_record.header_record.receipt_header_id IS NULL THEN
1405 BEGIN
1406 SELECT MAX(shipment_header_id) -- if we ever have 2 shipments with the same combo
1407 INTO x_header_record.header_record.receipt_header_id
1408 FROM rcv_shipment_headers
1409 WHERE NVL(vendor_site_id, -9999) = NVL(x_header_record.header_record.vendor_site_id, -9999)
1410 AND vendor_id = x_header_record.header_record.vendor_id
1411 AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
1412 AND shipment_num = x_header_record.header_record.shipment_num
1413 AND shipped_date >= ADD_MONTHS(x_header_record.header_record.shipped_date, -12);
1414 EXCEPTION
1415 WHEN OTHERS THEN
1416 IF (g_asn_debug = 'Y') THEN
1417 asn_debug.put_line(SQLERRM);
1418 END IF;
1419 END;
1420
1421 RETURN;
1422 END IF;
1423 EXCEPTION
1424 WHEN OTHERS THEN
1425 rcv_error_pkg.set_sql_error_message('default_shipment_info', '000');
1426 x_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1427 x_header_record.error_record.error_message := rcv_error_pkg.get_last_message;
1428 END default_shipment_info;
1429
1430 /* ==========================================================================
1431
1432 PROCEDURE NAME: VALIDATE_SHIPMENT_NUMBER
1433
1434 ===========================================================================*/
1435 PROCEDURE validate_shipment_number(
1436 x_header_record IN OUT NOCOPY rcv_shipment_header_sv.headerrectype
1437 ) IS
1438 x_count NUMBER;
1439 x_shipment_header_id NUMBER; -- added for cancel process
1440 x_sysdate DATE := SYSDATE;
1441 BEGIN
1442 -- Check for shipment number which is null, blank , zero
1443 IF (g_asn_debug = 'Y') THEN
1444 asn_debug.put_line('Check for shipment number which is null, blank , zero ');
1445 END IF;
1446
1447 /*dbms_output.put_line(nvl(X_header_record.header_record.shipment_num,'@@@'));*/
1448 IF x_header_record.header_record.asn_type IN('ASN', 'ASBN')
1449 AND ( x_header_record.header_record.shipment_num IS NULL
1450 OR x_header_record.header_record.shipment_num = '0'
1451 OR REPLACE(x_header_record.header_record.shipment_num,
1452 ' ',
1453 ''
1454 ) IS NULL) THEN
1455 /*dbms_output.put_line(X_header_record.header_record.asn_type);
1456 dbms_output.put_line(X_header_record.header_record.shipment_num);*/
1457 rcv_error_pkg.set_error_message('RCV_NO_SHIPMENT_NUM');
1458 RAISE e_validation_error;
1459 END IF;
1460
1461 -- Check for Receipts before ASN
1462
1463 IF (g_asn_debug = 'Y') THEN
1464 asn_debug.put_line('Check for Receipts before ASN ');
1465 END IF;
1466
1467 /*
1468 * BUGNO: 1708017
1469 * The where clause used to have organization_id =
1470 * X_header_record.header_record.ship_to_organization_id
1471 * This used to be populated with ship_to_organization_id.
1472 * Now this is populated as null since it is supposed to
1473 * be from organization_id. So changed it to ship_to_org_id.
1474 */
1475 /* Bug 2485699- commented the condn trunc(Shipped_date) = trunc(header.record.shipped_date).
1476 Added the shipped date is null since we are not populating the same in rcv_shipment_headers
1477 while receiving thru forms.*/
1478 IF x_header_record.header_record.asn_type IN('ASN', 'ASBN')
1479 AND x_header_record.header_record.transaction_type <> 'CANCEL' THEN -- added this for CANCEL
1480 SELECT COUNT(*)
1481 INTO x_count
1482 FROM rcv_shipment_headers
1483 WHERE NVL(vendor_site_id, -9999) = NVL(x_header_record.header_record.vendor_site_id, -9999)
1484 AND vendor_id = x_header_record.header_record.vendor_id
1485 AND --trunc(shipped_date) = trunc(X_header_record.header_record.shipped_date) and
1486 ( shipped_date IS NULL
1487 OR shipped_date >= ADD_MONTHS(x_sysdate, -12))
1488 AND shipment_num = x_header_record.header_record.shipment_num
1489 AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
1490 AND receipt_num IS NOT NULL;
1491
1492 IF x_count > 0 THEN
1493 rcv_error_pkg.set_error_message('RCV_RCV_BEFORE_ASN');
1494 rcv_error_pkg.set_token('SHIPMENT', x_header_record.header_record.shipment_num);
1495 rcv_error_pkg.set_token('ITEM', ' ');
1496 RAISE e_validation_error;
1497 END IF;
1498 END IF;
1499
1500 -- Change transaction_type to NEW if transaction_type is REPLACE and
1501 -- we cannot locate the shipment notice for the vendor site with the
1502 -- same shipped date
1503 /*
1504 * BUGNO: 1708017
1505 * The where clause used to have organization_id =
1506 * X_header_record.header_record.ship_to_organization_id
1507 * This used to be populated with ship_to_organization_id.
1508 * Now this is populated as null since it is supposed to
1509 * be from organization_id. So changed it to ship_to_org_id.
1510 */
1511 IF x_header_record.header_record.transaction_type = 'REPLACE' THEN
1512 SELECT COUNT(*)
1513 INTO x_count
1514 FROM rcv_shipment_headers
1515 WHERE NVL(vendor_site_id, -9999) = NVL(x_header_record.header_record.vendor_site_id, -9999)
1516 AND vendor_id = x_header_record.header_record.vendor_id
1517 AND TRUNC(shipped_date) = TRUNC(x_header_record.header_record.shipped_date)
1518 AND shipped_date >= ADD_MONTHS(x_sysdate, -12)
1519 AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
1520 AND shipment_num = x_header_record.header_record.shipment_num;
1521
1522 IF x_count = 0 THEN
1523 x_header_record.header_record.transaction_type := 'NEW';
1524 END IF;
1525 END IF;
1526
1527 -- Check for any shipment_num which exist for the same vendor site and within a year
1528 -- of the previous shipment with the same num. This is only done for transaction_type = NEW
1529
1530 /*
1531 * BUGNO: 1708017
1532 * The where clause used to have organization_id =
1533 * X_header_record.header_record.ship_to_organization_id
1534 * This used to be populated with ship_to_organization_id.
1535 * Now this is populated as null since it is supposed to
1536 * be from organization_id. So changed it to ship_to_org_id.
1537 */
1538
1539 /* Fix for bug 2682881.
1540 * No validation on shipment_num was happening if a new ASN
1541 * is created with the same supplier,supplier site, shipment
1542 * num, but with different shipped_date. Shipment_num should
1543 * be unique from the supplier,supplier site for a period of
1544 * one year. Hence commented the condition "trunc(shipped_date)
1545 * = trunc(X_header_record.header_record.shipped_date) and"
1546 * from the following sql which is not required.
1547 */
1548 IF x_header_record.header_record.transaction_type = 'NEW'
1549 AND x_header_record.header_record.asn_type IN('ASN', 'ASBN') THEN
1550 SELECT COUNT(*)
1551 INTO x_count
1552 FROM rcv_shipment_headers
1553 WHERE NVL(vendor_site_id, -9999) = NVL(x_header_record.header_record.vendor_site_id, -9999)
1554 AND vendor_id = x_header_record.header_record.vendor_id
1555 AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
1556 AND shipment_num = x_header_record.header_record.shipment_num
1557 AND --trunc(shipped_date) = trunc(X_header_record.header_record.shipped_date) and
1558 shipped_date >= ADD_MONTHS(x_sysdate, -12);
1559
1560 IF x_count > 0 THEN
1561 /* Bug# 1413880
1562 As per the manual Shipment number should be unique for one year period for
1563 given supplier.Changing Warning to Error. */
1564 rcv_error_pkg.set_error_message('PO_PDOI_SHIPMENT_NUM_UNIQUE');
1565 rcv_error_pkg.set_token('VALUE', x_header_record.header_record.shipment_num);
1566 RAISE e_validation_error;
1567 END IF;
1568 END IF;
1569
1570 /*bug 2123721. bgopired
1571 We were not checking the uniqueness of shipment number incase of
1572 Standard Receipts. Used the same logic of Enter Receipt form to check
1573 the uniqueness */
1574 IF x_header_record.header_record.transaction_type = 'NEW'
1575 AND x_header_record.header_record.asn_type IN('STD') THEN
1576 IF NOT val_unique_shipment_num(x_header_record.header_record.shipment_num, x_header_record.header_record.vendor_id) THEN
1577 rcv_error_pkg.set_error_message('PO_PDOI_SHIPMENT_NUM_UNIQUE');
1578 rcv_error_pkg.set_token('VALUE', x_header_record.header_record.shipment_num);
1579 RAISE e_validation_error;
1580 END IF;
1581 END IF;
1582
1583 -- Check for matching ASN if ADD, CANCEL
1584 IF (g_asn_debug = 'Y') THEN
1585 asn_debug.put_line('Check for matching ASN if ADD, CANCEL');
1586 END IF;
1587
1588 /*
1589 * BUGNO: 1708017
1590 * The where clause used to have organization_id =
1591 * X_header_record.header_record.ship_to_organization_id
1592 * This used to be populated with ship_to_organization_id.
1593 * Now this is populated as null since it is supposed to
1594 * be from organization_id. So changed it to ship_to_org_id.
1595 */
1596 IF x_header_record.header_record.transaction_type IN('ADD', 'CANCEL')
1597 AND x_header_record.header_record.asn_type IN('ASN', 'ASBN') THEN
1598 SELECT COUNT(*)
1599 INTO x_count
1600 FROM rcv_shipment_headers
1601 WHERE NVL(vendor_site_id, -9999) = NVL(x_header_record.header_record.vendor_site_id, -9999)
1602 AND vendor_id = x_header_record.header_record.vendor_id
1603 AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
1604 AND shipment_num = x_header_record.header_record.shipment_num
1605 AND TRUNC(shipped_date) = TRUNC(x_header_record.header_record.shipped_date)
1606 AND shipped_date >= ADD_MONTHS(x_sysdate, -12);
1607
1608 IF x_count = 0 THEN
1609 rcv_error_pkg.set_error_message('RCV_NO_MATCHING_ASN');
1610 rcv_error_pkg.set_token('SHIPMENT', x_header_record.header_record.shipment_num);
1611 RAISE e_validation_error;
1612 END IF;
1613 END IF;
1614
1615 -- Check that there are no receipts against the ASN for ADD, CANCEL
1616 IF (g_asn_debug = 'Y') THEN
1617 asn_debug.put_line('Check that there are no receipts against the ASN for ADD, CANCEL');
1618 END IF;
1619
1620 IF x_header_record.header_record.transaction_type IN('ADD', 'CANCEL')
1621 AND x_header_record.header_record.asn_type IN('ASN', 'ASBN') THEN
1622 IF x_header_record.header_record.receipt_header_id IS NOT NULL THEN
1623 SELECT SUM(quantity_received)
1624 INTO x_count
1625 FROM rcv_shipment_lines
1626 WHERE rcv_shipment_lines.shipment_header_id = x_header_record.header_record.receipt_header_id;
1627 ELSE
1628 /*
1629 * BUGNO: 1708017
1630 * The where clause used to have organization_id =
1631 * X_header_record.header_record.ship_to_organization_id
1632 * This used to be populated with ship_to_organization_id.
1633 * Now this is populated as null since it is supposed to
1634 * be from organization_id. So changed it to ship_to_org_id.
1635 */
1636 SELECT SUM(quantity_received)
1637 INTO x_count
1638 FROM rcv_shipment_lines
1639 WHERE EXISTS(SELECT 'x'
1640 FROM rcv_shipment_headers
1641 WHERE rcv_shipment_headers.shipment_header_id = rcv_shipment_lines.shipment_header_id
1642 AND NVL(vendor_site_id, -9999) = NVL(x_header_record.header_record.vendor_site_id, -9999)
1643 AND vendor_id = x_header_record.header_record.vendor_id
1644 AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
1645 AND shipment_num = x_header_record.header_record.shipment_num
1646 AND TRUNC(shipped_date) = TRUNC(x_header_record.header_record.shipped_date)
1647 AND shipped_date >= ADD_MONTHS(x_sysdate, -12));
1648 END IF;
1649
1650 IF NVL(x_count, 0) > 0 THEN -- Some quantity has been received
1651 IF (g_asn_debug = 'Y') THEN
1652 asn_debug.put_line('There are receipts against the ASN ' || x_header_record.header_record.shipment_num);
1653 END IF;
1654
1655 rcv_error_pkg.set_error_message('RCV_ASN_QTY_RECEIVED');
1656 rcv_error_pkg.set_token('SHIPMENT', x_header_record.header_record.shipment_num);
1657 RAISE e_validation_error;
1658 END IF;
1659 END IF;
1660
1661 -- If we have reached this place that means the shipment exists
1662 -- Make sure we have a shipment header id
1663
1664 IF (g_asn_debug = 'Y') THEN
1665 asn_debug.put_line('Make sure we have a shipment_header_id');
1666 END IF;
1667
1668 /*
1669 * BUGNO: 1708017
1670 * The where clause used to have organization_id =
1671 * X_header_record.header_record.ship_to_organization_id
1672 * This used to be populated with ship_to_organization_id.
1673 * Now this is populated as null since it is supposed to
1674 * be from organization_id. So changed it to ship_to_org_id.
1675 */
1676 IF x_header_record.header_record.transaction_type IN('CANCEL')
1677 AND x_header_record.header_record.receipt_header_id IS NULL THEN
1678 SELECT MAX(shipment_header_id)
1679 INTO x_header_record.header_record.receipt_header_id
1680 FROM rcv_shipment_headers
1681 WHERE NVL(vendor_site_id, -9999) = NVL(x_header_record.header_record.vendor_site_id, -9999)
1682 AND vendor_id = x_header_record.header_record.vendor_id
1683 AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
1684 AND shipment_num = x_header_record.header_record.shipment_num
1685 AND TRUNC(shipped_date) = TRUNC(x_header_record.header_record.shipped_date)
1686 AND shipped_date >= ADD_MONTHS(x_sysdate, -12);
1687 END IF;
1688
1689 -- Verify that the shipment_header_id matches the derived/defaulted shipment_header_id
1690
1691 IF (g_asn_debug = 'Y') THEN
1692 asn_debug.put_line('Verify that the shipment_header_id matches the derived/defaulted shipment_header_id');
1693 END IF;
1694
1695 /*
1696 * BUGNO: 1708017
1697 * The where clause used to have organization_id =
1698 * X_header_record.header_record.ship_to_organization_id
1699 * This used to be populated with ship_to_organization_id.
1700 * Now this is populated as null since it is supposed to
1701 * be from organization_id. So changed it to ship_to_org_id.
1702 */
1703 IF x_header_record.header_record.transaction_type IN('CANCEL')
1704 AND x_header_record.header_record.receipt_header_id IS NOT NULL THEN
1705 SELECT MAX(shipment_header_id)
1706 INTO x_shipment_header_id
1707 FROM rcv_shipment_headers
1708 WHERE NVL(vendor_site_id, -9999) = NVL(x_header_record.header_record.vendor_site_id, -9999)
1709 AND vendor_id = x_header_record.header_record.vendor_id
1710 AND ship_to_org_id = x_header_record.header_record.ship_to_organization_id
1711 AND shipment_num = x_header_record.header_record.shipment_num
1712 AND TRUNC(shipped_date) = TRUNC(x_header_record.header_record.shipped_date)
1713 AND shipped_date >= ADD_MONTHS(x_sysdate, -12);
1714
1715 IF x_shipment_header_id <> x_header_record.header_record.receipt_header_id THEN
1716 IF (g_asn_debug = 'Y') THEN
1717 asn_debug.put_line('The shipment_header_id do not match ');
1718 END IF;
1719
1720 rcv_error_pkg.set_error_message('RCV_ASN_MISMATCH_SHIP_ID');
1721 rcv_error_pkg.set_token('SHIPMENT', x_header_record.header_record.shipment_num);
1722 RAISE e_validation_error;
1723 END IF;
1724 END IF;
1725 EXCEPTION
1726 WHEN e_validation_error THEN
1727 x_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1728 x_header_record.error_record.error_message := rcv_error_pkg.get_last_message;
1729 WHEN OTHERS THEN
1730 rcv_error_pkg.set_sql_error_message('validate_shipment_number', '000');
1731 x_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1732 x_header_record.error_record.error_message := rcv_error_pkg.get_last_message;
1733 END validate_shipment_number;
1734 END rcv_core_s;
[Home] [Help]