1 PACKAGE BODY rcv_roi_header_common
2 /* $Header: RCVOIHCB.pls 120.24.12020000.2 2012/07/10 09:27:13 ptkumar ship $ */
3 AS
4 from_org_record rcv_shipment_object_sv.organization_id_record_type;
5 ship_to_org_record rcv_shipment_object_sv.organization_id_record_type;
6 loc_record rcv_shipment_object_sv.location_id_record_type;
7 emp_record rcv_shipment_object_sv.employee_id_record_type;
8 pay_record rcv_shipment_header_sv.payrectype;
9 freight_record rcv_shipment_header_sv.freightrectype;
10 lookup_record rcv_shipment_header_sv.lookuprectype;
11 currency_record rcv_shipment_header_sv.currectype;
12 invoice_record rcv_shipment_header_sv.invrectype;
13 tax_record rcv_shipment_header_sv.taxrectype;
14 -- Read the profile option that enables/disables the debug log
15 g_asn_debug VARCHAR2(1) := asn_debug.is_debug_on; -- Bug 9152790: rcv debug enhancement
16 x_sysdate DATE := SYSDATE;
17 x_count NUMBER := 0;
18 x_location_id NUMBER;
19 e_validation_error EXCEPTION;
20
21 PROCEDURE derive_ship_to_org_info(
22 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
23 ) IS
24 BEGIN
25 /* Derive Ship To Organization Information
26 * organization_id is uk. org_organization_definitions is a view */
27 IF p_header_record.error_record.error_status IN('S', 'W') THEN
28 /*
29 ** If the shipment header ship to organization code is null then try
30 ** to pull it off the rcv_transactions_interface to_organization_code or
31 ** the ship_to_location_code.
32 */
33 IF ( p_header_record.header_record.ship_to_organization_code IS NULL
34 AND p_header_record.header_record.ship_to_organization_id IS NULL) THEN
35 derive_ship_to_org_from_rti(p_header_record);
36 END IF;
37
38 ship_to_org_record.organization_code := p_header_record.header_record.ship_to_organization_code;
39 ship_to_org_record.organization_id := p_header_record.header_record.ship_to_organization_id;
40 ship_to_org_record.error_record.error_status := p_header_record.error_record.error_status;
41 ship_to_org_record.error_record.error_message := p_header_record.error_record.error_message;
42
43 IF (g_asn_debug = 'Y') THEN
44 asn_debug.put_line('In Ship to Organization Procedure');
45 END IF;
46
47 po_orgs_sv.derive_org_info(ship_to_org_record);
48
49 IF (g_asn_debug = 'Y') THEN
50 asn_debug.put_line(ship_to_org_record.organization_code);
51 asn_debug.put_line(TO_CHAR(ship_to_org_record.organization_id));
52 asn_debug.put_line(ship_to_org_record.error_record.error_status);
53 END IF;
54
55 p_header_record.header_record.ship_to_organization_code := ship_to_org_record.organization_code;
56 p_header_record.header_record.ship_to_organization_id := ship_to_org_record.organization_id;
57 p_header_record.error_record.error_status := ship_to_org_record.error_record.error_status;
58 p_header_record.error_record.error_message := ship_to_org_record.error_record.error_message;
59 END IF;
60 END derive_ship_to_org_info;
61
62 PROCEDURE derive_from_org_info(
63 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
64 ) IS
65 BEGIN
66 /* derive from organization information */
67 IF p_header_record.error_record.error_status IN('S', 'W')
68 AND p_header_record.header_record.transaction_type <> 'CANCEL' THEN -- added for support of cancel
69 from_org_record.organization_code := p_header_record.header_record.from_organization_code;
70 from_org_record.organization_id := p_header_record.header_record.from_organization_id;
71 from_org_record.error_record.error_status := p_header_record.error_record.error_status;
72 from_org_record.error_record.error_message := p_header_record.error_record.error_message;
73
74 IF (g_asn_debug = 'Y') THEN
75 asn_debug.put_line('In From Organization Procedure');
76 END IF;
77
78 po_orgs_sv.derive_org_info(from_org_record);
79
80 IF (g_asn_debug = 'Y') THEN
81 asn_debug.put_line(from_org_record.organization_code);
82 asn_debug.put_line(TO_CHAR(from_org_record.organization_id));
83 asn_debug.put_line(from_org_record.error_record.error_status);
84 END IF;
85
86 p_header_record.header_record.from_organization_code := from_org_record.organization_code;
87 p_header_record.header_record.from_organization_id := from_org_record.organization_id;
88 p_header_record.error_record.error_status := from_org_record.error_record.error_status;
89 p_header_record.error_record.error_message := from_org_record.error_record.error_message;
90 END IF;
91 END derive_from_org_info;
92
93 PROCEDURE derive_location_info(
94 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
95 ) IS
96 BEGIN
97 /* Derive Location Information */
98 /* HR_LOCATION has 2 unique indexes
99 1 -> location_id
100 2 -> location_code */
101 IF ( p_header_record.error_record.error_status IN('S', 'W')
102 AND ( p_header_record.header_record.location_code IS NOT NULL
103 OR p_header_record.header_record.location_id IS NOT NULL)) THEN
104 loc_record.location_code := p_header_record.header_record.location_code;
105 loc_record.location_id := p_header_record.header_record.location_id;
106 loc_record.error_record.error_status := p_header_record.error_record.error_status;
107 loc_record.error_record.error_message := p_header_record.error_record.error_message;
108
109 IF (g_asn_debug = 'Y') THEN
110 asn_debug.put_line('In Location Code Procedure');
111 END IF;
112
113 po_locations_s.derive_location_info(loc_record);
114
115 IF (g_asn_debug = 'Y') THEN
116 asn_debug.put_line(loc_record.location_code);
117 asn_debug.put_line(TO_CHAR(loc_record.location_id));
118 asn_debug.put_line(loc_record.error_record.error_status);
119 END IF;
120
121 p_header_record.header_record.location_code := loc_record.location_code;
122 p_header_record.header_record.location_id := loc_record.location_id;
123 p_header_record.error_record.error_status := loc_record.error_record.error_status;
124 p_header_record.error_record.error_message := loc_record.error_record.error_message;
125 END IF;
126 END derive_location_info;
127
128 PROCEDURE derive_payment_terms_info(
129 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
130 ) IS
131 BEGIN
132 /* Derive Payment Terms Information */
133 IF p_header_record.error_record.error_status IN('S', 'W')
134 AND p_header_record.header_record.transaction_type <> 'CANCEL'
135 AND -- added for support of cancel
136 ( p_header_record.header_record.payment_terms_id IS NOT NULL
137 OR p_header_record.header_record.payment_terms_name IS NOT NULL) THEN
138 pay_record.payment_term_id := p_header_record.header_record.payment_terms_id;
139 pay_record.payment_term_name := p_header_record.header_record.payment_terms_name;
140 pay_record.error_record.error_status := p_header_record.error_record.error_status;
141 pay_record.error_record.error_message := p_header_record.error_record.error_message;
142
143 IF (g_asn_debug = 'Y') THEN
144 asn_debug.put_line('In Derive Payment Terms ');
145 END IF;
146
147 po_terms_sv.derive_payment_terms_info(pay_record);
148
149 IF (g_asn_debug = 'Y') THEN
150 asn_debug.put_line(pay_record.payment_term_name);
151 asn_debug.put_line(TO_CHAR(pay_record.payment_term_id));
152 asn_debug.put_line(pay_record.error_record.error_status);
153 END IF;
154
155 p_header_record.header_record.payment_terms_id := pay_record.payment_term_id;
156 p_header_record.header_record.payment_terms_name := pay_record.payment_term_name;
157 p_header_record.error_record.error_status := pay_record.error_record.error_status;
158 p_header_record.error_record.error_message := pay_record.error_record.error_message;
159 END IF;
160 END derive_payment_terms_info;
161
162 PROCEDURE derive_receiver_info(
163 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
164 ) IS
165 BEGIN
166 IF p_header_record.error_record.error_status IN('S', 'W')
167 AND p_header_record.header_record.transaction_type <> 'CANCEL'
168 AND -- added for support of cancel
169 ( p_header_record.header_record.employee_name IS NOT NULL
170 OR p_header_record.header_record.employee_id IS NOT NULL) THEN
171 emp_record.employee_name := p_header_record.header_record.employee_name;
172 emp_record.employee_id := p_header_record.header_record.employee_id;
173 emp_record.error_record.error_status := p_header_record.error_record.error_status;
174 emp_record.error_record.error_message := p_header_record.error_record.error_message;
175
176 IF (g_asn_debug = 'Y') THEN
177 asn_debug.put_line('In Derive Receiver Information');
178 END IF;
179
180 po_employees_sv.derive_employee_info(emp_record);
181
182 IF (g_asn_debug = 'Y') THEN
183 asn_debug.put_line(emp_record.employee_name);
184 asn_debug.put_line(TO_CHAR(emp_record.employee_id));
185 asn_debug.put_line(emp_record.error_record.error_status);
186 END IF;
187
188 p_header_record.header_record.employee_name := emp_record.employee_name;
189 p_header_record.header_record.employee_id := emp_record.employee_id;
190 p_header_record.error_record.error_status := emp_record.error_record.error_status;
191 p_header_record.error_record.error_message := emp_record.error_record.error_message;
192 END IF;
193 END derive_receiver_info;
194
195 PROCEDURE derive_shipment_header_id(
196 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
197 ) IS
198 BEGIN
199 /* Derive shipment_header_id if transaction type = CANCEL */
200
201 -- added for support of cancel
202
203 IF p_header_record.error_record.error_status IN('S', 'W')
204 AND p_header_record.header_record.transaction_type = 'CANCEL'
205 AND p_header_record.header_record.shipment_num IS NOT NULL THEN
206 IF (g_asn_debug = 'Y') THEN
207 asn_debug.put_line('Derive shipment info');
208 END IF;
209
210 --rcv_core_s.derive_shipment_info(p_header_record);
211 /* block from rcv_core_s.derive_shipment_info */
212 IF p_header_record.header_record.receipt_header_id IS NULL THEN
213 BEGIN
214 SELECT MAX(shipment_header_id) -- if we ever have 2 shipments with the same combo
215 INTO p_header_record.header_record.receipt_header_id
216 FROM rcv_shipment_headers
217 WHERE NVL(vendor_site_id, -9999) = NVL(p_header_record.header_record.vendor_site_id, -9999)
218 AND vendor_id = p_header_record.header_record.vendor_id
219 AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id
220 AND shipment_num = p_header_record.header_record.shipment_num
221 AND shipped_date >= ADD_MONTHS(p_header_record.header_record.shipped_date, -12);
222 EXCEPTION
223 WHEN OTHERS THEN
224 IF (g_asn_debug = 'Y') THEN
225 asn_debug.put_line(SQLERRM);
226 END IF;
227 END;
228 ELSE
229 IF (g_asn_debug = 'Y') THEN
230 asn_debug.put_line('Need to put a cursor to retrieve other values');
231 asn_debug.put_line('Shipment header Id has been provided');
232 END IF;
233 END IF;
234
235 RETURN;
236 -- end of the block
237
238 END IF;
239 END derive_shipment_header_id;
240
241 PROCEDURE derive_ship_to_org_from_rti(
242 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
243 ) IS
244 x_header_interface_id NUMBER;
245 x_to_organization_code VARCHAR2(3);
246 x_to_organization_id NUMBER; /* Bug#3909973 - (1) */
247 x_shipment_header_id RCV_TRANSACTIONS_INTERFACE.SHIPMENT_HEADER_ID%TYPE;
248 x_shipment_num RCV_TRANSACTIONS_INTERFACE.SHIPMENT_NUM%TYPE;
249 x_document_num RCV_TRANSACTIONS_INTERFACE.DOCUMENT_NUM%TYPE;
250 BEGIN
251 x_header_interface_id := p_header_record.header_record.header_interface_id;
252
253 IF (g_asn_debug = 'Y') THEN
254 asn_debug.put_line('No ship to org specified at the header');
255 asn_debug.put_line('Trying to retrieve from lines');
256 END IF;
257
258 SELECT MAX(rti.to_organization_code)
259 INTO x_to_organization_code
260 FROM rcv_transactions_interface rti
261 WHERE rti.header_interface_id = x_header_interface_id;
262
263 /* Bug# 1465730 - If Ship To Organization Code is not specified at lines
264 * then derive it from the To Organization Id and if this is also not
265 * specified then derive it from Ship To Location Code/Id which ever is
266 * specified. */
267 IF (x_to_organization_code IS NULL) THEN
268 IF (g_asn_debug = 'Y') THEN
269 asn_debug.put_line('No ship to org specified at the lines either');
270 asn_debug.put_line('Trying to retrieve from to_organization_id');
271 END IF;
272
273 /* ksareddy RVCTP performance fix 2481798 - select from mtl_parameters instead
274 SELECT MAX(ORG.ORGANIZATION_CODE)
275 INTO X_TO_ORGANIZATION_CODE
276 FROM RCV_TRANSACTIONS_INTERFACE RTI,
277 ORG_ORGANIZATION_DEFINITIONS ORG
278 WHERE RTI.HEADER_INTERFACE_ID = X_HEADER_INTERFACE_ID
279 AND ORG.ORGANIZATION_ID = RTI.TO_ORGANIZATION_ID;
280 */
281 SELECT MAX(mtl.organization_code)
282 INTO x_to_organization_code
283 FROM rcv_transactions_interface rti,
284 mtl_parameters mtl
285 WHERE rti.header_interface_id = x_header_interface_id
286 AND mtl.organization_id = rti.to_organization_id;
287 END IF;
288
289 IF (x_to_organization_code IS NULL) THEN
290 IF (g_asn_debug = 'Y') THEN
291 asn_debug.put_line('Trying to retrieve from ship to location');
292 END IF;
293
294 SELECT MAX(org.organization_code)
295 INTO x_to_organization_code
296 FROM rcv_transactions_interface rti,
297 hr_locations hl,
298 mtl_parameters org
299 -- BugFix 5219284, replaced org_organization_definitions with mtl_parameters for better performance.
300 WHERE rti.header_interface_id = x_header_interface_id
301 AND ( rti.ship_to_location_code = hl.location_code
302 OR rti.ship_to_location_id = hl.location_id)
303 AND hl.inventory_organization_id = org.organization_id;
304 END IF;
305
306 /* Bug 3695855 - need to default org form shipping header */
307 IF (x_to_organization_code IS NULL) THEN
308 IF (g_asn_debug = 'Y') THEN
309 asn_debug.put_line('Trying to retrieve from shipment header id');
310 END IF;
311
312 SELECT MAX(rti.shipment_header_id),MAX(rti.shipment_num),MAX(rti.document_num)
313 INTO x_shipment_header_id,x_shipment_num,x_document_num
314 FROM rcv_transactions_interface rti
315 WHERE rti.header_interface_id = x_header_interface_id;
316
317 x_shipment_num := nvl(x_shipment_num,p_header_record.header_record.shipment_num);
318
319 IF (x_shipment_header_id IS NULL and x_shipment_num IS NOT NULL) THEN
320 SELECT MAX(rsh.shipment_header_id)
321 INTO x_shipment_header_id
322 FROM rcv_shipment_headers rsh
323 WHERE rsh.shipment_num = x_shipment_num;
324 END IF;
325
326 IF (x_shipment_header_id IS NOT NULL) THEN
327 SELECT MAX(rsl.to_organization_id)
328 INTO x_to_organization_id /* Bug#3909973 - (2) */
329 FROM rcv_shipment_lines rsl
330 WHERE rsl.shipment_header_id = x_shipment_header_id
331 AND (x_document_num is null or x_document_num = rsl.line_num);
332 END IF;
333 END IF;
334 /* End bug 3695855 */
335
336 IF ( p_header_record.header_record.ship_to_organization_code IS NULL
337 AND p_header_record.header_record.ship_to_organization_id IS NULL) THEN
338 IF (x_to_organization_code IS NOT NULL) THEN
339 IF (g_asn_debug = 'Y') THEN
340 asn_debug.put_line('A ship to location relating to an org was found');
341 END IF;
342
343 p_header_record.header_record.ship_to_organization_code := x_to_organization_code;
344 ELSIF (x_to_organization_id IS NOT NULL) THEN /* Bug#3909973 - (3) */
345 IF (g_asn_debug = 'Y') THEN
346 asn_debug.put_line('A ship to location relating to an org was found');
347 END IF;
348
349 p_header_record.header_record.ship_to_organization_id := x_to_organization_id;
350 ELSE
351 IF (g_asn_debug = 'Y') THEN
352 asn_debug.put_line('A ship to location relating to an org was NOT found');
353 asn_debug.put_line('This will cause an ERROR later');
354 END IF;
355 END IF;
356 END IF;
357 EXCEPTION
358 WHEN OTHERS THEN
359 p_header_record.error_record.error_status := 'U';
360 p_header_record.error_record.error_message := SQLERRM;
361 END derive_ship_to_org_from_rti;
362
363 PROCEDURE derive_uom_info(
364 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
365 n IN BINARY_INTEGER
366 ) IS
367 BEGIN
368 asn_debug.put_line('inside derive_uom_info');
369
370 IF (x_cascaded_table(n).error_status IN('S', 'W'))
371 AND x_cascaded_table(n).item_id IS NOT NULL
372 AND x_cascaded_table(n).primary_unit_of_measure IS NULL THEN
373 BEGIN
374 /* BUG 608353 */
375 /*Commenting defaulting of use_mtl_lot and use_mtl_serial
376 BUG 4735484
377 */
378 SELECT primary_unit_of_measure
379 --NVL(x_cascaded_table(n).use_mtl_lot, lot_control_code),
380 --NVL(x_cascaded_table(n).use_mtl_serial, serial_number_control_code)
381 INTO x_cascaded_table(n).primary_unit_of_measure
382 --x_cascaded_table(n).use_mtl_lot,
383 --x_cascaded_table(n).use_mtl_serial
384 FROM mtl_system_items
385 WHERE mtl_system_items.inventory_item_id = x_cascaded_table(n).item_id
386 AND mtl_system_items.organization_id = x_cascaded_table(n).to_organization_id;
387
388 IF (g_asn_debug = 'Y') THEN
389 asn_debug.put_line('Primary UOM: ' || x_cascaded_table(n).primary_unit_of_measure);
390 END IF;
391 EXCEPTION
392 WHEN NO_DATA_FOUND THEN
393 x_cascaded_table(n).error_status := 'W';
394 x_cascaded_table(n).error_message := 'Need an error message';
395
396 IF (g_asn_debug = 'Y') THEN
397 asn_debug.put_line('Primary UOM error');
398 END IF;
399 END;
400 END IF; -- set primary_uom
401
402 /* Bug 2020269 : uom_code needs to be derived from unit_of_measure
403 entered in rcv_transactions_interface.
404 */
405 IF (x_cascaded_table(n).unit_of_measure IS NOT NULL) THEN
406 IF (g_asn_debug = 'Y') THEN
407 asn_debug.put_line('deriving uom_code from unit_of_measure');
408 END IF;
409
410 SELECT muom.uom_code
411 INTO x_cascaded_table(n).uom_code
412 FROM mtl_units_of_measure muom
413 WHERE muom.unit_of_measure = x_cascaded_table(n).unit_of_measure;
414 ELSE
415 IF (g_asn_debug = 'Y') THEN
416 asn_debug.put_line('uom_code not dereived as unit_of_measure is null');
417 END IF;
418 END IF; -- set uom_code
419 END derive_uom_info;
420
421 PROCEDURE genreceiptnum(
422 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
423 ) IS
424 l_count NUMBER;
425 PRAGMA AUTONOMOUS_TRANSACTION;
426 BEGIN
427 BEGIN
428 SELECT (next_receipt_num + 1)
429 INTO p_header_record.header_record.receipt_num
430 FROM rcv_parameters
431 WHERE organization_id = p_header_record.header_record.ship_to_organization_id
432 FOR UPDATE OF next_receipt_num;
433
434 LOOP
435 SELECT COUNT(*)
436 INTO l_count
437 FROM rcv_shipment_headers
438 WHERE receipt_num = p_header_record.header_record.receipt_num
439 AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id;
440
441 IF l_count = 0 THEN
442 UPDATE rcv_parameters
443 SET next_receipt_num = p_header_record.header_record.receipt_num
444 WHERE organization_id = p_header_record.header_record.ship_to_organization_id;
445
446 EXIT;
447 ELSE
448 p_header_record.header_record.receipt_num := TO_CHAR(TO_NUMBER(p_header_record.header_record.receipt_num) + 1);
449 END IF;
450 END LOOP;
451
452 COMMIT;
453 EXCEPTION
454 WHEN OTHERS THEN
455 ROLLBACK;
456 END;
457 END genreceiptnum;
458
459 PROCEDURE commondefaultcode(
460 p_trx_record IN OUT NOCOPY rcv_roi_header_common.common_default_record_type
461 ) IS
462 BEGIN
463 IF p_trx_record.destination_type_code IS NULL
464 OR (p_trx_record.transaction_type = 'TRANSFER')
465 OR -- TRANSFER
466 ( p_trx_record.destination_type_code = 'INVENTORY'
467 AND p_trx_record.auto_transact_code = 'RECEIVE') THEN
468 p_trx_record.destination_type_code := 'RECEIVING';
469
470 IF (g_asn_debug = 'Y') THEN
471 asn_debug.put_line('Defaulting DESTINATION_TYPE_CODE ' || p_trx_record.destination_type_code);
472 END IF;
473 END IF;
474
475 IF p_trx_record.transaction_type IS NULL THEN
476 p_trx_record.transaction_type := 'SHIP';
477
478 IF (g_asn_debug = 'Y') THEN
479 asn_debug.put_line('Defaulting TRANSACTION_TYPE ' || p_trx_record.transaction_type);
480 END IF;
481 END IF;
482
483 IF p_trx_record.processing_mode_code IS NULL THEN
484 p_trx_record.processing_mode_code := 'BATCH';
485
486 IF (g_asn_debug = 'Y') THEN
487 asn_debug.put_line('Defaulting PROCESSING_MODE_CODE ' || p_trx_record.processing_mode_code);
488 END IF;
489 END IF;
490
491 p_trx_record.processing_status_code := 'RUNNING';
492
493 IF p_trx_record.processing_status_code IS NULL THEN
494 -- This has to be set to running otherwise C code in rvtbm
495 -- will not pick it up
496 p_trx_record.processing_status_code := 'RUNNING';
497
498 IF (g_asn_debug = 'Y') THEN
499 asn_debug.put_line('Defaulting PROCESSING_STATUS_CODE ' || p_trx_record.processing_status_code);
500 END IF;
501 END IF;
502
503 IF p_trx_record.transaction_status_code IS NULL THEN
504 p_trx_record.transaction_status_code := 'PENDING';
505
506 IF (g_asn_debug = 'Y') THEN
507 asn_debug.put_line('Defaulting TRANSACTION_STATUS_CODE ' || p_trx_record.transaction_status_code);
508 END IF;
509 END IF;
510 -- Default auto_transact_code if it is null
511 END commondefaultcode;
512
513 PROCEDURE default_last_update_info(
514 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
515 ) IS
516 BEGIN
517 /* last_update_date */
518 IF p_header_record.header_record.last_update_date IS NULL THEN
519 p_header_record.header_record.last_update_date := x_sysdate;
520
521 IF (g_asn_debug = 'Y') THEN
522 asn_debug.put_line('defaulting last update date');
523 END IF;
524 END IF;
525
526 /* last_updated_by */
527 IF p_header_record.header_record.last_updated_by IS NULL THEN
528 p_header_record.header_record.last_updated_by := fnd_global.user_id;
529
530 IF (g_asn_debug = 'Y') THEN
531 asn_debug.put_line('defaulting last update by');
532 END IF;
533 END IF;
534
535 /* last_update_login */
536 IF p_header_record.header_record.last_update_login IS NULL THEN
537 p_header_record.header_record.last_update_login := fnd_global.login_id;
538
539 IF (g_asn_debug = 'Y') THEN
540 asn_debug.put_line('defaulting last update login');
541 END IF;
542 END IF;
543 END default_last_update_info;
544
545 PROCEDURE default_creation_info(
546 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
547 ) IS
548 BEGIN
549 /* creation_date */
550 IF p_header_record.header_record.creation_date IS NULL THEN
551 p_header_record.header_record.creation_date := x_sysdate;
552
553 IF (g_asn_debug = 'Y') THEN
554 asn_debug.put_line('defaulting creation date');
555 END IF;
556 END IF;
557
558 /* created_by */
559 IF p_header_record.header_record.created_by IS NULL THEN
560 p_header_record.header_record.created_by := fnd_global.user_id;
561
562 IF (g_asn_debug = 'Y') THEN
563 asn_debug.put_line('defaulting created by ');
564 END IF;
565 END IF;
566 END default_creation_info;
567
568 PROCEDURE default_asn_type(
569 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
570 ) IS
571 BEGIN
572 /* Default STD into asn_type for null asn_type */
573 IF p_header_record.header_record.asn_type IS NULL THEN
574 p_header_record.header_record.asn_type := 'STD';
575
576 IF (g_asn_debug = 'Y') THEN
577 asn_debug.put_line('defaulting asn type to STD');
578 END IF;
579 END IF;
580 END default_asn_type;
581
582 PROCEDURE default_shipment_header_id(
583 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
584 ) IS
585 BEGIN
586 /* generate the shipment_header_id */
587 /* shipment_header_id - receipt_header_id is the same */
588 IF p_header_record.header_record.receipt_header_id IS NULL
589 AND p_header_record.header_record.transaction_type <> 'CANCEL' THEN -- added for support of cancel
590 SELECT rcv_shipment_headers_s.NEXTVAL
591 INTO p_header_record.header_record.receipt_header_id
592 FROM SYS.DUAL;
593
594 /* Bug#4523892 */
595 IF p_header_record.header_record.receipt_source_code = 'VENDOR' THEN
596 rcv_roi_header.g_txn_against_asn := 'N';
597 IF (g_asn_debug = 'Y') THEN
598 asn_debug.put_line('g_txn_against_asn in default_shipment_header_id:' || rcv_roi_header.g_txn_against_asn);
599 END IF;
600 END IF;
601
602 IF (g_asn_debug = 'Y') THEN
603 asn_debug.put_line('defaulted receipt_id');
604 END IF;
605 END IF;
606 END default_shipment_header_id;
607
608 PROCEDURE default_receipt_info(
609 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
610 ) IS
611 v_rcv_type po_system_parameters.user_defined_receipt_num_code%TYPE;
612 v_count NUMBER := 0;
613 BEGIN
614 /* receipt_num */
615
616 -- If Receipt Generation is set to Manual then we need to default it based
617 -- on the Shipment number. If shipment_num is also null then we will use the
618 -- shipment_header_id. We need a Receipt num in case of RECEIVE/DELIVER as
619 -- some of the views of the receiving form have the condition of receipt_num not
620 -- null added to it.
621
622 -- IF the transaction type is CANCEL then no need to generate a receipt num
623
624 IF p_header_record.header_record.receipt_num IS NULL
625 AND p_header_record.header_record.transaction_type <> 'CANCEL' THEN -- added for support of cancel
626 SELECT COUNT(*)
627 INTO v_count
628 FROM rcv_transactions_interface rti
629 WHERE rti.header_interface_id = p_header_record.header_record.header_interface_id
630 AND ( rti.auto_transact_code IN('RECEIVE', 'DELIVER')
631 OR rti.transaction_type IN('RECEIVE', 'DELIVER'));
632
633 IF v_count > 0 THEN -- We need to generate a receipt_num
634 BEGIN
635 SELECT user_defined_receipt_num_code
636 INTO v_rcv_type
637 FROM rcv_parameters
638 WHERE organization_id = p_header_record.header_record.ship_to_organization_id;
639
640 /* assuming that the ship_to_organization_id is populated at the header level of
641 rcv_headers_interface */
642 IF (g_asn_debug = 'Y') THEN
643 asn_debug.put_line(v_rcv_type || ' Generation ');
644 END IF;
645
646 IF v_rcv_type = 'AUTOMATIC' THEN
647 --bug 2506961
648 rcv_roi_header_common.genreceiptnum(p_header_record);
649 ELSE -- MANUAL
650 IF p_header_record.header_record.shipment_num IS NOT NULL THEN
651 p_header_record.header_record.receipt_num := p_header_record.header_record.shipment_num;
652 END IF;
653
654 /* If receipt_num is still null then use the shipment_header_id */
655 IF p_header_record.header_record.receipt_num IS NULL THEN
656 p_header_record.header_record.receipt_num := TO_CHAR(p_header_record.header_record.receipt_header_id);
657 END IF;
658 END IF; -- v_rcv_type
659 EXCEPTION
660 -- Added following NO_DATA_FOUND condition for bugfix #4070516
661 WHEN NO_DATA_FOUND
662 THEN
663 IF (g_asn_debug = 'Y') THEN
664 asn_debug.put_line('NO_DATA_FOUND exception occured. Receiving options are not defined for organization = ' || p_header_record.header_record.ship_to_organization_id);
665 END IF;
666 p_header_record.error_record.error_status := 'E';
667 rcv_error_pkg.set_error_message('RCV_NO_OPTION', p_header_record.error_record.error_message);
668 rcv_error_pkg.set_token('ORG', p_header_record.header_record.ship_to_organization_id);
669 -- End of code for bugfix #4070516
670 WHEN OTHERS THEN
671 p_header_record.error_record.error_status := 'E';
672 p_header_record.error_record.error_message := SQLERRM;
673 END;
674 ELSE -- of v_count
675 IF (g_asn_debug = 'Y') THEN
676 asn_debug.put_line('No need to generate a receipt_number');
677 END IF;
678 END IF; -- of v_count
679
680 IF (g_asn_debug = 'Y') THEN
681 asn_debug.put_line('defaulted receipt_num ' || p_header_record.header_record.receipt_num);
682 END IF;
683 END IF;
684 END default_receipt_info;
685
686 PROCEDURE default_ship_to_location_info(
687 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
688 ) IS
689 temp_count NUMBER;
690 x_po_header_id NUMBER;
691 x_document_num VARCHAR2(20);
692 BEGIN
693 /* ship_to_location_id mtl_org_organizations.default */
694 IF p_header_record.header_record.location_code IS NULL
695 AND p_header_record.header_record.location_id IS NULL
696 AND p_header_record.header_record.transaction_type <> 'CANCEL'
697 AND -- added for support of cancel
698 p_header_record.header_record.ship_to_organization_id IS NOT NULL THEN
699 /* Changed hr_locations to hr_locations_all since we are searching
700 * using inventory_organization_id and for drop ship POs inventory
701 * orgid does not have any meaning.
702 */
703 SELECT MAX(hr_locations_all.location_id),
704 COUNT(*)
705 INTO x_location_id,
706 x_count
707 FROM hr_locations_all
708 WHERE hr_locations_all.inventory_organization_id = p_header_record.header_record.ship_to_organization_id
709 AND NVL(hr_locations_all.inactive_date, x_sysdate + 1) > x_sysdate
710 AND NVL(hr_locations_all.receiving_site_flag, 'N') = 'Y';
711
712 IF (g_asn_debug = 'Y') THEN
713 asn_debug.put_line('count in hr_locations_all ' || x_count);
714 END IF;
715
716 IF x_count = 1 THEN
717 p_header_record.header_record.location_id := x_location_id;
718
719 /* Bug 3250435 : The check for drop ship should be made only
720 if the receipt is against a PO. Added the following IF
721 condition so that we do not attempt to populate the
722 po_header_id when the document_num does not contain
723 a PO Number.
724 */
725 IF p_header_record.header_record.receipt_source_code = 'VENDOR' THEN
726 /* Bug 1904996. If this is a drop ship PO, then we dont want
727 * to default this value since this is the location for the
728 * inventory org id in which the drop ship PO for created and
729 * not the drop ship location.
730 */
731 SELECT MAX(rti.po_header_id),
732 MAX(document_num)
733 INTO x_po_header_id,
734 x_document_num
735 FROM rcv_transactions_interface rti
736 WHERE rti.header_interface_id = p_header_record.header_record.header_interface_id;
737
738 IF ( x_po_header_id IS NULL
739 AND x_document_num IS NOT NULL) THEN
740 BEGIN -- bugfix 4070516
741 SELECT po_header_id
742 INTO x_po_header_id
743 FROM po_headers
744 WHERE segment1 = x_document_num
745 AND type_lookup_code IN('STANDARD', 'BLANKET', 'PLANNED');
746 -- Following exception handling block is added for bugfix 4070516
747 EXCEPTION
748 WHEN NO_DATA_FOUND
749 THEN
750 NULL;
751 WHEN OTHERS
752 THEN
753 NULL;
754 END;
755 -- End of code bugfix 4070516
756 END IF;
757
758 IF (x_po_header_id IS NOT NULL) THEN
759 SELECT COUNT(*)
760 INTO temp_count
761 FROM oe_drop_ship_sources
762 WHERE po_header_id = x_po_header_id;
763
764 IF (temp_count <> 0) THEN -- this is a drop ship
765 IF (g_asn_debug = 'Y') THEN
766 asn_debug.put_line('drop ship PO');
767 END IF;
768
769 p_header_record.header_record.location_id := NULL;
770 END IF;
771 END IF;
772 END IF;
773 END IF;
774
775 IF (g_asn_debug = 'Y') THEN
776 asn_debug.put_line('defaulted location info');
777 END IF;
778 END IF;
779 END default_ship_to_location_info;
780
781 PROCEDURE default_ship_from_loc_info(
782 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
783 ) IS
784 BEGIN
785 /* This is now handled by the defaulting package. No need to do it here */
786 NULL;
787 END default_ship_from_loc_info;
788
789 PROCEDURE validate_trx_type(
790 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
791 ) IS
792 BEGIN
793 IF (p_header_record.error_record.error_status NOT IN('S', 'W')) THEN
794 RETURN;
795 END IF;
796
797 /* Validate Transaction Type */
798 IF (g_asn_debug = 'Y') THEN
799 asn_debug.put_line('In validate routine');
800 END IF;
801
802 lookup_record.lookup_code := p_header_record.header_record.transaction_type;
803 lookup_record.lookup_type := 'TRANSACTION_TYPE';
804 lookup_record.error_record.error_status := 'S'; --p_header_record.error_record.error_status;
805 lookup_record.error_record.error_message := NULL; --p_header_record.error_record.error_message;
806 po_core_s.validate_lookup_info(lookup_record);
807
808 IF (lookup_record.error_record.error_status <> 'S') THEN
809 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
810 rcv_error_pkg.set_error_message('RCV_TRX_TYPE_INVALID', p_header_record.error_record.error_message);
811 rcv_error_pkg.set_token('TYPE', lookup_record.lookup_code);
812 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'TRANSACTION_TYPE');
813 END IF;
814
815 IF (g_asn_debug = 'Y') THEN
816 asn_debug.put_line('validated transaction type');
817 END IF;
818 EXCEPTION
819 WHEN rcv_error_pkg.e_fatal_error THEN
820 NULL;
821 END validate_trx_type;
822
823 PROCEDURE validate_expected_receipt_date(
824 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
825 ) IS
826 BEGIN
827 IF (p_header_record.error_record.error_status NOT IN('S', 'W')) THEN
828 RETURN;
829 END IF;
830
831 /* Validation expected_receipt_date is not missing BUG 628316 */
832
833 /* R12 Complex Work.
834 * There is no concept of expected_receipt_date for Work Confirmations.
835 * So expected_receipt_date can be null.
836 */
837 IF (p_header_record.header_record.transaction_type <> 'CANCEL') THEN
838 IF (p_header_record.header_record.expected_receipt_date IS NULL and
839 p_header_record.header_record.asn_type <> 'WC') THEN
840 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
841 rcv_error_pkg.set_error_message('RCV_ASN_EXPECTED_RECEIPT_DATE', p_header_record.error_record.error_message);
842 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'EXPECTED_RECEIPT_DATE');
843 END IF;
844 END IF;
845
846 IF (g_asn_debug = 'Y') THEN
847 asn_debug.put_line('validated expected_receipt_date is not missing');
848 END IF;
849 EXCEPTION
850 WHEN rcv_error_pkg.e_fatal_error THEN
851 NULL;
852 END validate_expected_receipt_date;
853
854 PROCEDURE validate_receipt_num(
855 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
856 ) IS
857 x_new_receipt VARCHAR2(1) := 'Y'; --Bug 12719212
858 x_rhi_count NUMBER := 0; --Bug 9126513
859 BEGIN
860 /* Validate Receipt Number */
861 IF (p_header_record.error_record.error_status NOT IN('S', 'W')) THEN
862 RETURN;
863 END IF;
864
865 IF p_header_record.header_record.receipt_num IS NULL
866 AND p_header_record.header_record.asn_type = 'STD'
867 AND p_header_record.header_record.transaction_type <> 'CANCEL' THEN -- added for support of cancel
868 IF (g_asn_debug = 'Y') THEN
869 asn_debug.put_line('Receipt Number is mandatory for STD');
870 END IF;
871
872 /* Bug 3590735.
873 * When we error out with receipt number mandatory error,
874 * we need to set this error in po_interface_errors.
875 */
876 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
877 rcv_error_pkg.set_error_message('RCV_RECEIPT_NUM_REQ', p_header_record.error_record.error_message);
878 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'RECEIPT_NUM');
879 END IF;
880
881 /* Bug 12719212
882 * get the value if the shipment is new shipment (has not been received),
883 * we validate duplicate receipt_num only when it is first receive against the shipment
884 */
885 SELECT DECODE(COUNT(*),
886 0, 'Y',
887 'N'
888 )
889 INTO x_new_receipt
890 FROM rcv_shipment_lines
891 WHERE quantity_received > 0
892 AND shipment_header_id IN
893 (SELECT shipment_header_id
894 FROM rcv_shipment_headers
895 WHERE shipment_num = p_header_record.header_record.shipment_num
896 AND ( vendor_site_id =
897 NVL(p_header_record.header_record.vendor_site_id, vendor_site_id)
898 OR vendor_site_id IS NULL)
899 AND (vendor_id =
900 NVL(p_header_record.header_record.vendor_id, vendor_id)
901 OR vendor_id IS NULL)
902 AND ship_to_org_id =
903 NVL(p_header_record.header_record.ship_to_organization_id, ship_to_org_id)
904 AND shipped_date >=
905 ADD_MONTHS(NVL(p_header_record.header_record.shipped_date, SYSDATE), -12)
906 AND receipt_source_code = p_header_record.header_record.receipt_source_code);
907
908 IF (g_asn_debug = 'Y') THEN
909 asn_debug.put_line('shipment x_new_receipt '||x_new_receipt);
910 END IF;
911
912 /*End Bug 12719212 */
913
914
915 IF x_new_receipt = 'Y' --Bug 12719212
916 --p_header_record.header_record.receipt_header_id IS NULL --Bug 12719212
917 AND -- bug 3508507: only check receipt_num uniqueness for new reciepts
918 -- X_new_receipt is populated in default_receipt_info()
919 p_header_record.header_record.receipt_num IS NOT NULL
920 AND p_header_record.header_record.transaction_type <> 'CANCEL' THEN -- added for support of cancel
921 SELECT COUNT(*)
922 INTO x_count
923 FROM rcv_shipment_headers
924 WHERE rcv_shipment_headers.receipt_num = p_header_record.header_record.receipt_num
925 AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id;
926 /* Bug 9126513 In case of concurrency issues with multiple RTP sessions running at the same time, multiple
927 RSH records were getting created due to simultaneous execution of the RSH validation above. Now, we
928 validate the Receipt Number/(Ship To Organization Id OR Ship To Organization Code) combination in RHI also,
929 and throw an exception when there are Duplicate Records in RHI with the same combination.*/
930 IF x_count = 0 THEN
931 SELECT Count(*)
932 INTO x_rhi_count
933 FROM rcv_headers_interface rhi
934 WHERE rhi.receipt_num = p_header_record.header_record.receipt_num
935 AND (rhi.ship_to_organization_id = p_header_record.header_record.ship_to_organization_id
936 OR rhi.ship_to_organization_code = p_header_record.header_record.ship_to_organization_code)
937 AND rhi.processing_status_code IN ('PENDING','RUNNING');
938
939 IF x_rhi_count > 1 THEN
940 IF (g_asn_debug = 'Y') THEN
941 asn_debug.put_line('Multiple RHI Records exist with Duplicate Receipt Numbers for the same Ship To Organization Id.');
942 END IF;
943 END IF;
944 END IF;
945
946 IF (x_count > 0 OR x_rhi_count > 1) THEN
947 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
948 rcv_error_pkg.set_error_message('PO_PDOI_RECEIPT_NUM_UNIQUE', p_header_record.error_record.error_message);
949 rcv_error_pkg.set_token('VALUE', p_header_record.header_record.receipt_num);
950 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'RECEIPT_NUM');
951 END IF;
952 /* End of fix for Bug 9126513 */
953
954 IF (g_asn_debug = 'Y') THEN
955 asn_debug.put_line('validated receipt number');
956 END IF;
957 END IF;
958 EXCEPTION
959 WHEN rcv_error_pkg.e_fatal_error THEN
960 NULL;
961 END validate_receipt_num;
962
963 PROCEDURE validate_ship_to_org_info(
964 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
965 ) IS
966 BEGIN
967 /* Validate Ship To Organization Information */
968 IF (p_header_record.error_record.error_status NOT IN('S', 'W')) THEN
969 RETURN;
970 END IF;
971
972 IF p_header_record.header_record.asn_type IN('ASN', 'ASBN', 'STD', 'LCM') THEN /* lcm changes */
973 ship_to_org_record.organization_code := p_header_record.header_record.ship_to_organization_code;
974 ship_to_org_record.organization_id := p_header_record.header_record.ship_to_organization_id;
975 ship_to_org_record.error_record.error_status := rcv_error_pkg.g_ret_sts_success;
976 ship_to_org_record.error_record.error_message := NULL;
977
978 IF (g_asn_debug = 'Y') THEN
979 asn_debug.put_line('In Validate Ship to Organization Procedure');
980 END IF;
981
982 po_orgs_sv.validate_org_info(ship_to_org_record);
983
984 IF (ship_to_org_record.error_record.error_status <> 'S') THEN
985 IF ship_to_org_record.error_record.error_message = 'ORG_DISABLED' THEN
986 IF p_header_record.header_record.transaction_type <> 'CANCEL' THEN
987 IF (g_asn_debug = 'Y') THEN
988 asn_debug.put_line('Error with RCV_SHIPTO_ORG_DISABLED');
989 END IF;
990
991 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
992 rcv_error_pkg.set_error_message('RCV_SHIPTO_ORG_DISABLED', p_header_record.error_record.error_message);
993 rcv_error_pkg.set_token('ORGANIZATION', ship_to_org_record.organization_id);
994 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'SHIP_TO_ORGANIZATION_ID');
995 END IF;
996 ELSE
997 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
998 rcv_error_pkg.set_error_message('PO_PDOI_INVALID_SHIP_TO_ORG_ID', p_header_record.error_record.error_message);
999 rcv_error_pkg.set_token('VALUE', ship_to_org_record.organization_id);
1000 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'SHIP_TO_ORGANIZATION_ID');
1001 END IF;
1002 END IF;
1003
1004 IF (g_asn_debug = 'Y') THEN
1005 asn_debug.put_line('ship_to_org_record.error_status ' || ship_to_org_record.error_record.error_status);
1006 asn_debug.put_line('validated ship to organization info');
1007 END IF;
1008 END IF;
1009
1010 /* Bug# 3662698.
1011 Verify if any of the lines tied to the header have destination organization
1012 different to that of the header's org (which is either populated or derived).
1013 */
1014 IF ( p_header_record.header_record.asn_type IN('ASN', 'ASBN', 'STD', 'LCM') /* lcm changes */
1015 AND p_header_record.header_record.transaction_type <> 'CANCEL') THEN
1016 /* Check if there is atleast one RTI record of this header with a
1017 different org than the header's org. Here we consider those
1018 RTI records which have to_organization_code or to_organization_id
1019 as not null. Later below we check for those RTI records which have
1020 to_organization_code and to_organization_id as null.
1021 This logic is followed keeping in view of the performance problems.
1022 */
1023 IF (p_header_record.header_record.ship_to_organization_code IS NOT NULL) THEN
1024 IF (g_asn_debug = 'Y') THEN
1025 asn_debug.put_line('Checking if any RTI has different destn org than that of the header');
1026 END IF;
1027
1028 SELECT COUNT(*)
1029 INTO x_count
1030 FROM rcv_transactions_interface rti,
1031 rcv_headers_interface rhi
1032 WHERE rti.header_interface_id = p_header_record.header_record.header_interface_id
1033 AND rhi.header_interface_id = rti.header_interface_id
1034 AND ( ( rti.to_organization_code IS NOT NULL
1035 AND rti.to_organization_code <> p_header_record.header_record.ship_to_organization_code)
1036 OR ( rti.to_organization_id IS NOT NULL
1037 AND rti.to_organization_id <> p_header_record.header_record.ship_to_organization_id)
1038 );
1039
1040 IF x_count >= 1 THEN
1041 IF (g_asn_debug = 'Y') THEN
1042 asn_debug.put_line('Atleast one of the RTIs has a different org id/code than that of the header');
1043 END IF;
1044
1045 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1046 rcv_error_pkg.set_error_message('RCV_MUL_DESTN_ORGS_FOR_LINES', p_header_record.error_record.error_message);
1047 rcv_error_pkg.set_token('VALUE', p_header_record.header_record.ship_to_organization_id);
1048 rcv_error_pkg.log_interface_error('SHIP_TO_ORGANIZATION_ID');
1049 ELSE
1050 IF (g_asn_debug = 'Y') THEN
1051 asn_debug.put_line('In the ELSE part');
1052 END IF;
1053
1054 /* Check if there is atleast one RTI record in this header with a different
1055 ship to org than the header's org. Here we consider those RTI records
1056 which have to_organization_code and to_rganization_id as null and
1057 ship_to_location_id as not null. Records with all the above four columns
1058 as null need not be checked as header's org will be set to the line's org
1059 during the line level organization derivation.
1060 */
1061 SELECT COUNT(*)
1062 INTO x_count
1063 FROM rcv_transactions_interface rti,
1064 hr_locations hl,
1065 mtl_parameters org
1066 -- BugFix 5219284, replaced org_organization_definitions with mtl_parameters for better performance.
1067 WHERE rti.header_interface_id = p_header_record.header_record.header_interface_id
1068 AND rti.to_organization_code IS NULL
1069 AND rti.to_organization_id IS NULL
1070 AND rti.ship_to_location_id IS NOT NULL
1071 AND rti.ship_to_location_id = hl.location_id
1072 AND hl.inventory_organization_id = org.organization_id
1073 AND org.organization_code <> p_header_record.header_record.ship_to_organization_code;
1074
1075 IF (g_asn_debug = 'Y') THEN
1076 asn_debug.put_line('Count is ' || TO_CHAR(x_count));
1077 END IF;
1078
1079 /* Check if there is atleast one RTI record in this header with a different
1080 ship to org than the header's org. Here we consider those RTI records
1081 which have to_organization_code and to_rganization_id as null and
1082 ship_to_location_code as not null. A seperate sql is written using
1083 ship_location_code instead of adding it to the the WHERE caluse of the
1084 above sql to avoid full table scans on hr_locations.
1085 */
1086 IF x_count = 0 THEN
1087 SELECT COUNT(*)
1088 INTO x_count
1089 FROM rcv_transactions_interface rti,
1090 hr_locations hl,
1091 mtl_parameters org
1092 -- BugFix 5219284, replaced org_organization_definitions with mtl_parameters for better performance.
1093 WHERE rti.header_interface_id = p_header_record.header_record.header_interface_id
1094 AND rti.to_organization_code IS NULL
1095 AND rti.to_organization_id IS NULL
1096 AND rti.ship_to_location_code IS NOT NULL
1097 AND rti.ship_to_location_code = hl.location_code
1098 AND hl.inventory_organization_id = org.organization_id
1099 AND org.organization_code <> p_header_record.header_record.ship_to_organization_code;
1100 END IF;
1101
1102 IF x_count >= 1 THEN
1103 IF (g_asn_debug = 'Y') THEN
1104 asn_debug.put_line('For one of the RTI records a different org id/code is derived');
1105 END IF;
1106
1107 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1108 rcv_error_pkg.set_error_message('RCV_MUL_DESTN_ORGS_FOR_LINES', p_header_record.error_record.error_message);
1109 rcv_error_pkg.set_token('VALUE', p_header_record.header_record.ship_to_organization_id);
1110 rcv_error_pkg.log_interface_error('SHIP_TO_ORGANIZATION_ID');
1111 END IF;
1112 END IF;
1113
1114 IF (g_asn_debug = 'Y') THEN
1115 asn_debug.put_line('Validated ship to org of all the RTIs tied to the header');
1116 END IF;
1117 END IF;
1118 END IF; --End of bug# 3662698.
1119
1120 EXCEPTION
1121 WHEN rcv_error_pkg.e_fatal_error THEN
1122 NULL;
1123 END validate_ship_to_org_info;
1124
1125 PROCEDURE validate_from_org_info(
1126 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
1127 ) IS
1128 BEGIN
1129 /* validate from organization information */
1130 IF (p_header_record.error_record.error_status NOT IN('S', 'W')) THEN
1131 RETURN;
1132 END IF;
1133
1134 IF p_header_record.header_record.transaction_type <> 'CANCEL' THEN
1135 IF from_org_record.organization_code IS NOT NULL
1136 OR from_org_record.organization_id IS NOT NULL THEN
1137 from_org_record.organization_code := p_header_record.header_record.from_organization_code;
1138 from_org_record.organization_id := p_header_record.header_record.from_organization_id;
1139 from_org_record.error_record.error_status := rcv_error_pkg.g_ret_sts_success;
1140 from_org_record.error_record.error_message := NULL;
1141
1142 IF (g_asn_debug = 'Y') THEN
1143 asn_debug.put_line('In Validate From Organization Procedure');
1144 END IF;
1145
1146 po_orgs_sv.validate_org_info(from_org_record);
1147
1148 IF (from_org_record.error_record.error_status <> 'S') THEN
1149 IF from_org_record.error_record.error_message = 'ORG_DISABLED' THEN
1150 IF p_header_record.header_record.transaction_type <> 'CANCEL' THEN
1151 IF (g_asn_debug = 'Y') THEN
1152 asn_debug.put_line('Error with RCV_SHIPTO_ORG_DISABLED');
1153 END IF;
1154
1155 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1156 rcv_error_pkg.set_error_message('RCV_FROM_ORG_DISABLED', p_header_record.error_record.error_message);
1157 rcv_error_pkg.set_token('ORGANIZATION', from_org_record.organization_code);
1158 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'FROM_ORGANIZATION_ID');
1159 END IF;
1160 ELSE
1161 IF (g_asn_debug = 'Y') THEN
1162 asn_debug.put_line('Error with from ORG_ID');
1163 END IF;
1164
1165 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1166 rcv_error_pkg.set_error_message('RCV_FROM_ORG_ID', p_header_record.error_record.error_message);
1167 rcv_error_pkg.set_token('ORGANIZATION', from_org_record.organization_code);
1168 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'FROM_ORGANIZATION_ID');
1169 END IF;
1170 END IF;
1171
1172 IF (g_asn_debug = 'Y') THEN
1173 asn_debug.put_line('validated from organization info');
1174 END IF;
1175 END IF;
1176 END IF;
1177 EXCEPTION
1178 WHEN rcv_error_pkg.e_fatal_error THEN
1179 NULL;
1180 END validate_from_org_info;
1181
1182 PROCEDURE validate_location_info(
1183 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
1184 ) IS
1185 BEGIN
1186 /* Validate Location Information */
1187 IF (p_header_record.error_record.error_status NOT IN('S', 'W')) THEN
1188 RETURN;
1189 END IF;
1190
1191 IF p_header_record.header_record.transaction_type <> 'CANCEL'
1192 AND p_header_record.header_record.asn_type IN('ASN', 'ASBN', 'STD', 'LCM') /* lcm changes */
1193 AND ( p_header_record.header_record.location_code IS NOT NULL
1194 OR p_header_record.header_record.location_id IS NOT NULL) THEN
1195 loc_record.location_code := p_header_record.header_record.location_code;
1196 loc_record.location_id := p_header_record.header_record.location_id;
1197 loc_record.organization_id := p_header_record.header_record.ship_to_organization_id;
1198 loc_record.error_record.error_status := rcv_error_pkg.g_ret_sts_success;
1199 loc_record.error_record.error_message := NULL;
1200
1201 IF (g_asn_debug = 'Y') THEN
1202 asn_debug.put_line('In Validate Location Code Procedure');
1203 END IF;
1204
1205 po_locations_s.validate_location_info(loc_record);
1206
1207 IF loc_record.error_record.error_status <> 'S' THEN
1208 IF loc_record.error_record.error_message = 'LOC_NOT_IN_ORG' THEN
1209 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1210 rcv_error_pkg.set_error_message('RCV_LOC_NOT_IN_ORG', p_header_record.error_record.error_message);
1211 rcv_error_pkg.set_token('LOCATION', loc_record.location_id);
1212 rcv_error_pkg.set_token('ORGANIZATION', loc_record.organization_id);
1213 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'LOCATION_ID');
1214 ELSE
1215 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1216 rcv_error_pkg.set_error_message('PO_PDOI_INVALID_SHIP_TO_LOC_ID', p_header_record.error_record.error_message);
1217 rcv_error_pkg.set_token('VALUE', loc_record.location_id);
1218 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'LOCATION_ID');
1219 END IF;
1220 END IF;
1221
1222 IF (g_asn_debug = 'Y') THEN
1223 asn_debug.put_line(loc_record.error_record.error_status);
1224 asn_debug.put_line(loc_record.error_record.error_message);
1225 asn_debug.put_line('Validated location info');
1226 END IF;
1227 END IF;
1228 EXCEPTION
1229 WHEN rcv_error_pkg.e_fatal_error THEN
1230 NULL;
1231 END validate_location_info;
1232
1233 PROCEDURE validate_ship_from_loc_info(
1234 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
1235 ) IS
1236 x_dummy NUMBER;
1237 p_in_rec wsh_po_integration_grp.validatesf_in_rec_type;
1238 x_out_rec wsh_po_integration_grp.validatesf_out_rec_type;
1239 x_return_status VARCHAR2(3);
1240 x_msg_count NUMBER;
1241 x_msg_data VARCHAR2(2000);
1242 l_shipping_control VARCHAR2(30); --Bug 5263268
1243
1244 CURSOR get_lines IS
1245 SELECT po_line_id,
1246 po_line_location_id po_shipment_line_id
1247 FROM rcv_transactions_interface
1248 WHERE header_interface_id = p_header_record.header_record.header_interface_id;
1249
1250 --Bug5263268:Cursor to fetch the value of "Shipping_control" from po_headers table.
1251 --Note:-ASN or ASBN can be created for multiple PO's provided they have the same
1252 --value for shing control.It is not possible to create a single ASN or ASBN with one PO
1253 --having shipping control as 'buyer' and another PO with shipping control as 'supplier' or
1254 --shippign control is null.
1255 --So there is no need to loop through the records fetched by the cursor.
1256
1257 /*CURSOR c_get_shipping_control is
1258 select shipping_control
1259 from po_headers_all
1260 where po_header_id = (select po_header_id
1261 from rcv_transactions_interface
1262 where header_interface_id = p_header_record.header_record.header_interface_id
1263 and rownum=1);*/ --Bugfix 5844039
1264 BEGIN
1265 /* Validate Location Information */
1266 IF (p_header_record.error_record.error_status NOT IN('S', 'W')) THEN
1267 RETURN;
1268 END IF;
1269
1270 --Bug 5263268:If the shipping_control is 'BUYER' and ship_from_location_id is NULL
1271 --the the transaction should error out.
1272 /*open c_get_shipping_control;
1273 fetch c_get_shipping_control into l_shipping_control;
1274 close c_get_shipping_control;
1275
1276 /* Bug 8314708 : Added extra condition to check the asn_type so that the validation
1277 ** for ship_from_location_id happens only in case of ASN's and ASBN's
1278
1279 IF (nvl(l_shipping_control,'@@@') = 'BUYER' AND p_header_record.header_record.ship_from_location_id IS NULL
1280 AND p_header_record.header_record.asn_type in ('ASN', 'ASBN') ) THEN
1281 IF (g_asn_debug = 'Y') THEN
1282 asn_debug.put_line('Ship from location id cannot be null if shipping_control is BUYER');
1283 END IF;
1284 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1285 rcv_error_pkg.set_error_message('RCV_INVALID_ROI_VALUE_NE');
1286 rcv_error_pkg.set_token('ROI_VALUE',p_header_record.header_record.ship_from_location_id);
1287 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'SHIP_FROM_LOCATION_ID');
1288
1289 END IF;*/
1290 --End Bug 5263268
1291
1292 IF p_header_record.header_record.ship_from_location_id IS NOT NULL THEN
1293 p_in_rec.ship_from_location_id := p_header_record.header_record.ship_from_location_id;
1294 open get_lines;
1295 fetch get_lines bulk collect into p_in_rec.po_line_id_tbl,p_in_rec.po_shipment_line_id_tbl;
1296 close get_lines;
1297
1298 wsh_po_integration_grp.validateasnreceiptshipfrom(1.0,
1299 fnd_api.g_false,
1300 p_in_rec,
1301 fnd_api.g_false,
1302 x_return_status,
1303 x_out_rec,
1304 x_msg_count,
1305 x_msg_data
1306 );
1307
1308 IF (x_out_rec.is_valid = FALSE) THEN
1309 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1310 rcv_error_pkg.set_error_message('RCV_LOC_NOT_IN_ORG', p_header_record.error_record.error_message);
1311 rcv_error_pkg.set_token('LOCATION', p_header_record.header_record.ship_from_location_id);
1312 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'SHIP_FROM_LOCATION_ID');
1313 END IF;
1314
1315 IF (g_asn_debug = 'Y') THEN
1316 asn_debug.put_line('Validated location info with status=' || p_header_record.error_record.error_status);
1317 END IF;
1318 END IF;
1319 EXCEPTION
1320 WHEN rcv_error_pkg.e_fatal_error THEN
1321 NULL;
1322 END validate_ship_from_loc_info;
1323
1324 PROCEDURE validate_payment_terms_info(
1325 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
1326 ) IS
1327 BEGIN
1328 /* Validate Payment Terms Information */
1329 IF (p_header_record.error_record.error_status NOT IN('S', 'W')) THEN
1330 RETURN;
1331 END IF;
1332
1333 IF ( p_header_record.header_record.payment_terms_name IS NOT NULL
1334 OR p_header_record.header_record.payment_terms_id IS NOT NULL)
1335 AND p_header_record.header_record.transaction_type <> 'CANCEL' THEN
1336 pay_record.payment_term_id := p_header_record.header_record.payment_terms_id;
1337 pay_record.payment_term_name := p_header_record.header_record.payment_terms_name;
1338 pay_record.error_record.error_status := rcv_error_pkg.g_ret_sts_success;
1339 pay_record.error_record.error_message := NULL;
1340
1341 IF (g_asn_debug = 'Y') THEN
1342 asn_debug.put_line('In Validate Payment Terms ');
1343 END IF;
1344
1345 po_terms_sv.validate_payment_terms_info(pay_record);
1346
1347 IF ( pay_record.error_record.error_message = 'PAY_TERMS_DISABLED'
1348 AND NVL(p_header_record.header_record.asn_type, 'ASN') <> 'ASBN') THEN
1349 pay_record.error_record.error_status := 'S';
1350 END IF;
1351
1352 IF pay_record.error_record.error_status <> 'S' THEN
1353 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1354 rcv_error_pkg.set_error_message('PO_PDOI_INVALID_PAY_TERMS', p_header_record.error_record.error_message);
1355 rcv_error_pkg.set_token('VALUE', pay_record.payment_term_id);
1356 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'PAYMENT_TERMS_ID');
1357 END IF;
1358
1359 IF (g_asn_debug = 'Y') THEN
1360 asn_debug.put_line(pay_record.error_record.error_status);
1361 END IF;
1362
1363 IF (g_asn_debug = 'Y') THEN
1364 asn_debug.put_line('Validated payment info');
1365 END IF;
1366 END IF;
1367 EXCEPTION
1368 WHEN rcv_error_pkg.e_fatal_error THEN
1369 NULL;
1370 END validate_payment_terms_info;
1371
1372 PROCEDURE validate_receiver_info(
1373 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
1374 ) IS
1375 BEGIN
1376 /* validate receiver information */
1377 IF (p_header_record.error_record.error_status NOT IN('S', 'W')) THEN
1378 RETURN;
1379 END IF;
1380
1381 IF p_header_record.header_record.transaction_type <> 'CANCEL'
1382 AND p_header_record.header_record.auto_transact_code = 'RECEIVE'
1383 AND ( p_header_record.header_record.employee_name IS NOT NULL
1384 OR p_header_record.header_record.employee_id IS NOT NULL) THEN
1385 emp_record.employee_name := p_header_record.header_record.employee_name;
1386 emp_record.employee_id := p_header_record.header_record.employee_id;
1387 emp_record.error_record.error_status := rcv_error_pkg.g_ret_sts_success;
1388 emp_record.error_record.error_message := NULL;
1389
1390 IF (g_asn_debug = 'Y') THEN
1391 asn_debug.put_line('In Validate Receiver Information');
1392 END IF;
1393
1394 po_employees_sv.validate_employee_info(emp_record);
1395
1396 IF (g_asn_debug = 'Y') THEN
1397 asn_debug.put_line(emp_record.error_record.error_status);
1398 END IF;
1399
1400 IF emp_record.error_record.error_status <> 'S' THEN
1401 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1402 rcv_error_pkg.set_error_message('RCV_RECEIVER_ID', p_header_record.error_record.error_message);
1403 rcv_error_pkg.set_token('NAME', emp_record.employee_name);
1404 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'EMPLOYEE_ID');
1405 END IF;
1406
1407 IF (g_asn_debug = 'Y') THEN
1408 asn_debug.put_line('Validated receiver info');
1409 END IF;
1410 END IF;
1411 EXCEPTION
1412 WHEN rcv_error_pkg.e_fatal_error THEN
1413 NULL;
1414 END validate_receiver_info;
1415
1416 PROCEDURE validate_freight_carrier_info(
1417 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
1418 ) IS
1419
1420 X_rsh_freight_carrier_code rcv_shipment_headers.freight_carrier_code%TYPE := '-999999'; /* Bug 8366230 */
1421
1422 BEGIN
1423 /* validate freight carrier information */
1424 /* ASN and ASBN, al transaction_types except CANCEL */
1425 /* Carrier is specified */
1426 IF (p_header_record.error_record.error_status NOT IN('S', 'W')) THEN
1427 RETURN;
1428 END IF;
1429
1430 IF p_header_record.header_record.transaction_type <> 'CANCEL'
1431 AND p_header_record.header_record.freight_carrier_code IS NOT NULL THEN
1432 freight_record.freight_carrier_code := p_header_record.header_record.freight_carrier_code;
1433 freight_record.organization_id := p_header_record.header_record.ship_to_organization_id;
1434 freight_record.error_record.error_status := rcv_error_pkg.g_ret_sts_success;
1435 freight_record.error_record.error_message := NULL;
1436
1437 IF (g_asn_debug = 'Y') THEN
1438 asn_debug.put_line('In Validate Freight Carrier Information');
1439 END IF;
1440
1441 /*Bug 8366230
1442 Adding IF conditions to ensure that the validation call for freight carriers is not made for
1443 Internal Orders and Inter-org transfers when rcv_shipment_headers.freight_carrier_code is
1444 already populated.
1445 */
1446 BEGIN
1447 IF (p_header_record.header_record.receipt_source_code IN ('INTERNAL ORDER','INVENTORY')) THEN
1448 SELECT Nvl(rsh.freight_carrier_code,'-999')
1449 INTO X_rsh_freight_carrier_code
1450 FROM rcv_shipment_headers rsh
1451 WHERE rsh.shipment_num = p_header_record.header_record.shipment_num
1452 AND rsh.ship_to_org_id = p_header_record.header_record.ship_to_organization_id
1453 AND rsh.receipt_source_code IN ('INVENTORY','INTERNAL ORDER');
1454
1455 IF (X_rsh_freight_carrier_code = p_header_record.header_record.freight_carrier_code) THEN
1456 RETURN;
1457 END IF;
1458 END IF;
1459
1460 EXCEPTION
1461 WHEN OTHERS THEN
1462 asn_debug.put_line('Erroring out in rcv_roi_header_common.validate_freight_carrier_info');
1463 asn_debug.put_line(SQLERRM);
1464 END;
1465 /* End of fix for Bug 8366230 */
1466
1467 po_terms_sv.validate_freight_carrier_info(freight_record);
1468
1469 IF freight_record.error_record.error_status <> 'S' THEN
1470 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1471 rcv_error_pkg.set_error_message('RCV_CARRIER_DISABLED', p_header_record.error_record.error_message);
1472 rcv_error_pkg.set_token('CARRIER', freight_record.freight_carrier_code);
1473 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'FREIGHT_CARRIER_CODE');
1474 END IF;
1475
1476 IF (g_asn_debug = 'Y') THEN
1477 asn_debug.put_line(freight_record.error_record.error_status);
1478 asn_debug.put_line('Validated freight carrier info');
1479 END IF;
1480 END IF;
1481 EXCEPTION
1482 WHEN rcv_error_pkg.e_fatal_error THEN
1483 NULL;
1484 END validate_freight_carrier_info;
1485
1486 PROCEDURE validate_shipment_date(
1487 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
1488 ) IS
1489 x_sysdate DATE := SYSDATE;
1490 BEGIN
1491 /* Validation for Shipment Date > System Date and not NULL,blank,zero */
1492 IF (p_header_record.error_record.error_status NOT IN('S', 'W')) THEN
1493 RETURN;
1494 END IF;
1495
1496 IF NVL(p_header_record.header_record.shipped_date, x_sysdate + 1) > x_sysdate THEN
1497 /* R12 Complex Work.
1498 * There is no concept of shipped_date for Work Confirmations.
1499 * So shipped_date can be null.
1500 */
1501 IF (g_asn_debug = 'Y') THEN
1502 asn_debug.put_line('asn_type ' || p_header_record.header_record.asn_type);
1503 END IF;
1504 IF p_header_record.header_record.shipped_date IS NULL
1505 AND p_header_record.header_record.asn_type IN ('WC', 'STD') THEN
1506 IF (g_asn_debug = 'Y') THEN
1507 asn_debug.put_line('Shipped date can be blank for STD '||
1508 'or Work Confirmations ');
1509 END IF;
1510 ELSE
1511 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1512 rcv_error_pkg.set_error_message('RCV_SHIP_DATE_INVALID', p_header_record.error_record.error_message);
1513 rcv_error_pkg.set_token('SHIP_DATE', fnd_date.date_to_chardate(p_header_record.header_record.shipped_date));
1514 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'SHIPPED_DATE');
1515 END IF;
1516 END IF;
1517
1518 IF (g_asn_debug = 'Y') THEN
1519 asn_debug.put_line('validated for shipment_date > system date');
1520 END IF;
1521 EXCEPTION
1522 WHEN rcv_error_pkg.e_fatal_error THEN
1523 NULL;
1524 END validate_shipment_date;
1525
1526 PROCEDURE validate_item(
1527 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1528 n IN BINARY_INTEGER
1529 ) IS -- bug 608353
1530 x_progress VARCHAR2(3);
1531 l_stock_enabled_flag mtl_system_items.stock_enabled_flag%TYPE; -- Bugfix 5735599
1532 l_inventory_item_flag mtl_system_items.inventory_item_flag%TYPE; -- Bugfix 5735599
1533 x_inventory_item mtl_system_items.inventory_item_id%TYPE := NULL;
1534 x_organization_id mtl_system_items.organization_id%TYPE := NULL;
1535 x_item_id_po po_lines.item_id%TYPE := NULL;
1536 x_error_status VARCHAR2(1);
1537 BEGIN
1538 asn_debug.put_line('inside validate item : receipt_source_code = ' || x_cascaded_table(n).receipt_source_code);
1539 x_error_status := rcv_error_pkg.g_ret_sts_error;
1540
1541 SELECT NVL(MAX(inventory_item_id), -9999)
1542 INTO x_inventory_item
1543 FROM mtl_system_items
1544 WHERE inventory_item_id = x_cascaded_table(n).item_id;
1545
1546 IF (x_inventory_item = -9999) THEN
1547 rcv_error_pkg.set_error_message('RCV_ITEM_ID');
1548 RAISE e_validation_error;
1549 END IF;
1550
1551 SELECT NVL(MAX(inventory_item_id), -9999)
1552 INTO x_inventory_item
1553 FROM mtl_system_items
1554 WHERE SYSDATE BETWEEN NVL(start_date_active, SYSDATE - 1) AND NVL(end_date_active, SYSDATE + 1)
1555 AND inventory_item_id = x_cascaded_table(n).item_id
1556 AND organization_id = NVL(x_cascaded_table(n).to_organization_id,organization_id); -- Bug 12985791
1557
1558 IF (x_inventory_item = -9999) THEN
1559 rcv_error_pkg.set_error_message('RCV_ITEM_NOT_ACTIVE');
1560 RAISE e_validation_error;
1561 END IF;
1562
1563 -- Bugfix 5735599
1564 -- When item status is changed to INACTIVE all the flags are unchecked.
1565 -- Hence to check inactive item we should check for STOCK_ENABLED_FLAG.
1566
1567 IF (g_asn_debug = 'Y') THEN
1568 asn_debug.put_line('x_cascaded_table(n).auto_transact_code: ' || x_cascaded_table(n).auto_transact_code);
1569 asn_debug.put_line('x_cascaded_table(n).TO_ORGANIZATION_ID: ' || x_cascaded_table(n).to_organization_id);
1570 asn_debug.put_line('x_cascaded_table(n).item_id: ' || x_cascaded_table(n).item_id);
1571 asn_debug.put_line('x_cascaded_table(n).TRANSACTION_TYPE ' || x_cascaded_table(n).transaction_type );
1572 END IF;
1573
1574 BEGIN
1575 SELECT stock_enabled_flag,
1576 inventory_item_flag
1577 INTO l_stock_enabled_flag,
1578 l_inventory_item_flag
1579 FROM mtl_system_items
1580 WHERE organization_id = x_cascaded_table(n).to_organization_id
1581 AND inventory_item_id = x_cascaded_table(n).item_id;
1582 EXCEPTION
1583 WHEN OTHERS
1584 THEN
1585 IF (g_asn_debug = 'Y') THEN
1586 asn_debug.put_line('Error occured while checking inactive item in rcv_roi_header_common procedure. Error :: ' || SQLERRM );
1587 END IF;
1588
1589 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1590 rcv_error_pkg.set_sql_error_message('rcv_roi_header_common.validate_item', '000');
1591 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
1592 rcv_error_pkg.log_interface_error('ITEM', TRUE);
1593
1594 RETURN;
1595 END;
1596
1597 -- If the item is inactive and routing is DIRECT then we should allow the first receipt as well.
1598
1599 IF l_inventory_item_flag = 'Y' AND l_stock_enabled_flag = 'N' AND
1600 (x_cascaded_table(n).auto_transact_code = 'DELIVER' OR x_cascaded_table(n).transaction_type = 'DELIVER')
1601 AND (x_cascaded_table(n).destination_type_code = 'INVENTORY') -- Bug 8433870
1602 THEN
1603 rcv_error_pkg.set_error_message('RCV_ITEM_NOT_ACTIVE');
1604 RAISE e_validation_error;
1605 END IF;
1606
1607 -- End of code for Bugfix 5735599
1608
1609 /* Bug 2160314.
1610 * We used to have nvl(max(organization_id),0) here before. But if the
1611 * organization_id is itself 0, then this will give us a problem in
1612 * the next step when we check if x_organization_id = 0. So changed
1613 * the statement to nvl(max(organization_id),-9999) and also the
1614 * check below. Similarly changed the select statement and the
1615 * check for nvl(max(item_id),0).
1616 */
1617 SELECT NVL(MAX(organization_id), -9999)
1618 INTO x_organization_id
1619 FROM mtl_system_items
1620 WHERE inventory_item_id = x_cascaded_table(n).item_id
1621 AND organization_id = NVL(x_cascaded_table(n).to_organization_id, organization_id);
1622
1623 IF (x_organization_id = -9999) THEN
1624 rcv_error_pkg.set_error_message('RCV_ITEM_NOT_IN_ORG');
1625 RAISE e_validation_error;
1626 END IF;
1627
1628 -- do these checks only for PO based transactions
1629 IF x_cascaded_table(n).receipt_source_code = 'VENDOR' THEN --{
1630 SELECT NVL(MAX(item_id), -9999)
1631 INTO x_item_id_po
1632 FROM po_lines
1633 WHERE po_line_id = x_cascaded_table(n).po_line_id
1634 AND item_id = x_cascaded_table(n).item_id;
1635
1636 IF (x_item_id_po = -9999) THEN
1637 rcv_error_pkg.set_error_message('RCV_ITEM_NOT_ON_PO');
1638 RAISE e_validation_error;
1639 END IF;
1640
1641 SELECT NVL(MAX(item_id), -9999)
1642 INTO x_item_id_po
1643 FROM po_lines
1644 WHERE po_line_id = x_cascaded_table(n).po_line_id
1645 AND item_id = x_cascaded_table(n).item_id;
1646
1647 IF (x_item_id_po <> x_cascaded_table(n).item_id) THEN
1648 rcv_error_pkg.set_error_message('RCV_NOT_PO_LINE_NUM');
1649 RAISE e_validation_error;
1650 END IF;
1651 END IF; --}
1652
1653 /* bug 608353, do not support lot and serial control if DELIVER is used */
1654 IF (g_asn_debug = 'Y') THEN
1655 asn_debug.put_line('Validating Item: ' || x_cascaded_table(n).auto_transact_code);
1656 asn_debug.put_line('Validating Item: ' || x_cascaded_table(n).use_mtl_lot);
1657 asn_debug.put_line('Validating Item: ' || x_cascaded_table(n).use_mtl_serial);
1658 END IF;
1659 EXCEPTION
1660 WHEN e_validation_error THEN
1661 x_cascaded_table(n).error_status := x_error_status;
1662 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
1663
1664 IF x_cascaded_table(n).error_message = 'RCV_ITEM_ID' THEN
1665 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).item_id);
1666 ELSIF x_cascaded_table(n).error_message = 'RCV_ITEM_NOT_ACTIVE' THEN
1667 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).item_id);
1668 ELSIF x_cascaded_table(n).error_message = 'RCV_ITEM_NOT_IN_ORG' THEN
1669 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).item_id);
1670 rcv_error_pkg.set_token('ORGANIZATION', x_cascaded_table(n).to_organization_id);
1671 ELSIF x_cascaded_table(n).error_message = 'RCV_ITEM_NOT_ON_PO' THEN
1672 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).item_id);
1673 rcv_error_pkg.set_token('ORGANIZATION', x_cascaded_table(n).to_organization_id);
1674 ELSIF x_cascaded_table(n).error_message = 'RCV_NOT_PO_LINE_NUM' THEN
1675 rcv_error_pkg.set_token('PO_ITEM', x_item_id_po);
1676 rcv_error_pkg.set_token('SHIPMENT_ITEM', x_cascaded_table(n).item_id);
1677 END IF;
1678 END validate_item;
1679
1680 PROCEDURE validate_substitute_item(
1681 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1682 n IN BINARY_INTEGER
1683 ) IS
1684 x_inventory_item mtl_system_items.inventory_item_id%TYPE := NULL;
1685 x_progress VARCHAR2(3);
1686 x_vendor_id po_vendors.vendor_id%TYPE := NULL;
1687 x_error_status VARCHAR2(1);
1688 x_allow_sub_flag VARCHAR2(1);
1689 BEGIN
1690 x_error_status := rcv_error_pkg.g_ret_sts_error;
1691
1692 SELECT NVL(MAX(inventory_item_id), 0)
1693 INTO x_inventory_item
1694 FROM mtl_system_items
1695 WHERE inventory_item_id = x_cascaded_table(n).substitute_item_id
1696 AND organization_id = NVL(x_cascaded_table(n).to_organization_id, organization_id);
1697
1698 IF (x_inventory_item = 0) THEN
1699 rcv_error_pkg.set_error_message('RCV_ITEM_SUB_ID');
1700 RAISE e_validation_error;
1701 END IF;
1702
1703 SELECT NVL(MAX(inventory_item_id), 0)
1704 INTO x_inventory_item
1705 FROM mtl_system_items
1706 WHERE SYSDATE BETWEEN NVL(start_date_active, SYSDATE - 1) AND NVL(end_date_active, SYSDATE + 1)
1707 AND inventory_item_id = x_cascaded_table(n).substitute_item_id
1708 AND organization_id = NVL(x_cascaded_table(n).to_organization_id, organization_id);
1709
1710 IF (x_inventory_item = 0) THEN
1711 rcv_error_pkg.set_error_message('RCV_ITEM_SUB_NOT_ACTIVE');
1712 RAISE e_validation_error;
1713 END IF;
1714
1715 -- do these checks only for PO based transactions
1716 IF x_cascaded_table(n).receipt_source_code = 'VENDOR' THEN --{
1717 --bug 3825246, need to check the allow_substitute_flag at both the
1718 --item level and on the po shipment lines level
1719 --the MIN gives No a priority over Yes, and the NVL covers the case where they are both null
1720 /* SELECT NVL(MIN(allow_substitute_receipts_flag),'N')
1721 INTO x_allow_sub_flag
1722 FROM (SELECT allow_substitute_receipts_flag
1723 FROM mtl_system_items
1724 WHERE inventory_item_id = (SELECT item_id
1725 FROM po_lines
1726 WHERE po_line_id = x_cascaded_table(n).po_line_id)
1727 AND organization_id = NVL(x_cascaded_table(n).to_organization_id, organization_id)
1728 UNION ALL
1729 SELECT allow_substitute_receipts_flag
1730 FROM po_line_locations
1731 WHERE line_location_id = x_cascaded_table(n).po_line_location_id);
1732 */
1733 -- Bugfix 5219284, Abobe query is replaced with following for performance reason.
1734
1735 -- Bug 13926508: Only validate allow_substitute_receipts_flag at PO shipment level during ROI Receipts
1736
1737 SELECT NVL(allow_substitute_receipts_flag, 'N')
1738 INTO x_allow_sub_flag
1739 FROM po_line_locations
1740 WHERE line_location_id = x_cascaded_table(n).po_line_location_id;
1741
1742 IF (x_allow_sub_flag = 'N') THEN
1743 rcv_error_pkg.set_error_message('RCV_ITEM_SUB_NOT_ALLOWED');
1744 RAISE e_validation_error;
1745 END IF;
1746
1747 SELECT NVL(MAX(inventory_item_id), 0)
1748 INTO x_inventory_item
1749 FROM mtl_system_items
1750 WHERE inventory_item_id = x_cascaded_table(n).substitute_item_id
1751 AND organization_id = NVL(x_cascaded_table(n).to_organization_id, organization_id);
1752
1753 IF (x_inventory_item = 0) THEN
1754 rcv_error_pkg.set_error_message('RCV_ITEM_SUB_NOT_IN_ORG');
1755 RAISE e_validation_error;
1756 END IF;
1757
1758 -- Bug 13926508: Commenting Supplier level validation since it is not required for substitute item receipts.
1759
1760 /* SELECT NVL(MAX(vendor_id), 0)
1761 INTO x_vendor_id
1762 FROM po_vendors
1763 WHERE vendor_id = x_cascaded_table(n).vendor_id
1764 AND allow_substitute_receipts_flag = 'Y';
1765
1766 IF (x_vendor_id = 0) THEN
1767 rcv_error_pkg.set_error_message('RCV_ITEM_SUB_VEN_NOT_ALLOWED');
1768 RAISE e_validation_error;
1769 END IF; */
1770
1771 -- Need to check for related items if reciprocal_flag is set
1772 -- Thus need to use union as user may not have set up both
1773 -- the items to substitute for each other and just used
1774 -- reciprocal_flag for this
1775 -- relationship_type_id = 2 for substitute items
1776 -- = 1 for related items
1777
1778 /* SELECT NVL(MAX(inventory_item_id), 0)
1779 INTO x_inventory_item
1780 FROM mtl_related_items
1781 WHERE inventory_item_id = (SELECT item_id
1782 FROM po_lines
1783 WHERE po_line_id = x_cascaded_table(n).po_line_id)
1784 AND related_item_id = x_cascaded_table(n).substitute_item_id
1785 AND relationship_type_id = 2; -- substitute items
1786 -- and organization_id = nvl(x_cascaded_table(n).to_organization_id,organization_id)
1787 */
1788
1789 -- Bugfix 5219284, Above query is replaced by following query for performance issues.
1790
1791 SELECT NVL(MAX(inventory_item_id), 0)
1792 INTO x_inventory_item
1793 FROM mtl_related_items mri,
1794 po_lines_all pl
1795 WHERE mri.inventory_item_id = pl.item_id
1796 AND pl.po_line_id = x_cascaded_table(n).po_line_id
1797 AND mri.related_item_id = x_cascaded_table(n).substitute_item_id
1798 AND mri.relationship_type_id = 2; -- substitute items
1799 -- and organization_id = nvl(x_cascaded_table(n).to_organization_id,organization_id)
1800
1801
1802 IF x_inventory_item = 0 THEN
1803 -- Try the reciprocal relationship
1804
1805 /* SELECT NVL(MAX(inventory_item_id), 0)
1806 INTO x_inventory_item
1807 FROM mtl_related_items
1808 WHERE related_item_id = (SELECT item_id
1809 FROM po_lines
1810 WHERE po_line_id = x_cascaded_table(n).po_line_id)
1811 AND inventory_item_id = x_cascaded_table(n).substitute_item_id
1812 AND reciprocal_flag = 'Y'
1813 AND relationship_type_id = 2;
1814 -- and organization_id = nvl(x_cascaded_table(n).to_organization_id,organization_id)
1815 */
1816 -- Bugfix 5219284, Above query is replaced by following query for performance issues.
1817
1818 SELECT NVL(MAX(inventory_item_id), 0)
1819 INTO x_inventory_item
1820 FROM mtl_related_items mri,
1821 po_lines_all pl
1822 WHERE mri.related_item_id = pl.item_id
1823 AND pl.po_line_id = x_cascaded_table(n).po_line_id
1824 AND mri.inventory_item_id = x_cascaded_table(n).substitute_item_id
1825 AND mri.reciprocal_flag = 'Y'
1826 AND mri.relationship_type_id = 2;
1827
1828 END IF;
1829
1830 IF (x_inventory_item = 0) THEN
1831 rcv_error_pkg.set_error_message('RCV_ITEM_SUB_NOT_RELATED');
1832 RAISE e_validation_error;
1833 END IF;
1834 END IF; --}
1835 EXCEPTION
1836 WHEN e_validation_error THEN
1837 x_cascaded_table(n).error_status := x_error_status;
1838 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
1839
1840 IF x_cascaded_table(n).error_message = 'RCV_ITEM_SUB_ID' THEN
1841 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).substitute_item_id);
1842 ELSIF x_cascaded_table(n).error_message = 'RCV_ITEM_SUB_NOT_ACTIVE' THEN
1843 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).substitute_item_id);
1844 ELSIF x_cascaded_table(n).error_message = 'RCV_ITEM_SUB_NOT_ALLOWED' THEN
1845 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).substitute_item_id);
1846 ELSIF x_cascaded_table(n).error_message = 'RCV_ITEM_SUB_NOT_IN_ORG' THEN
1847 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).substitute_item_id);
1848 rcv_error_pkg.set_token('ORGANIZATION', x_cascaded_table(n).to_organization_id);
1849 ELSIF x_cascaded_table(n).error_message = 'RCV_ITEM_SUB_VEN_NOT_ALLOWED' THEN
1850 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).substitute_item_id);
1851 rcv_error_pkg.set_token('SUPPLIER', x_cascaded_table(n).vendor_id);
1852 ELSIF x_cascaded_table(n).error_message = 'RCV_ITEM_SUB_NOT_RELATED' THEN
1853 rcv_error_pkg.set_token('SUB_ITEM', x_cascaded_table(n).substitute_item_id);
1854 rcv_error_pkg.set_token('ITEM', x_inventory_item);
1855 END IF;
1856 END validate_substitute_item;
1857
1858 PROCEDURE validate_item_revision(
1859 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1860 n IN BINARY_INTEGER
1861 ) IS
1862 x_inventory_item mtl_system_items.inventory_item_id%TYPE := NULL;
1863 x_progress VARCHAR2(3);
1864 x_revision_control_flag VARCHAR2(1);
1865 x_item_revision po_lines.item_revision%TYPE;
1866 x_error_status VARCHAR2(1);
1867
1868 /* Bug 5339860
1869 * Added support for substitute item revision validation
1870 * */
1871
1872 l_substitute_item BOOLEAN;
1873 l_active_item_id NUMBER;
1874 BEGIN
1875
1876 /** Bug 6055435
1877 * 1) Removed the validation of item revision mentioned in PO and the one
1878 * stamped in RTI, as the revision mentioned in PO can be changed at any
1879 * point of time. And moreover through forms we are allowing to receive/deliver
1880 * different item rev than the one mentioned in PO.
1881 * 2) Removed all the commented piece of codes, as the code looks clumsy.
1882 * 3) Removed the unnecessary error code part from the 'WHEN e_validation_error'
1883 * exception handler block.
1884 */
1885 x_error_status := rcv_error_pkg.g_ret_sts_error;
1886
1887 IF x_cascaded_table(n).substitute_item_id IS NOT NULL THEN
1888 l_substitute_item := TRUE;
1889 l_active_item_id := x_cascaded_table(n).substitute_item_id;
1890 ELSE
1891 l_substitute_item := FALSE;
1892 l_active_item_id := x_cascaded_table(n).item_id;
1893 END IF;
1894
1895 -- check whether the item is under revision control
1896 -- If it is not then item should not have any revisions
1897
1898 SELECT DECODE(msi.revision_qty_control_code,
1899 1, 'N',
1900 2, 'Y',
1901 'N'
1902 )
1903 INTO x_revision_control_flag
1904 FROM mtl_system_items msi
1905 WHERE inventory_item_id = l_active_item_id
1906 AND organization_id = x_cascaded_table(n).to_organization_id;
1907
1908 IF x_revision_control_flag = 'N' THEN --BUG: 5975270
1909 RETURN;
1910 END IF;
1911
1912 -- Check whether the revision number exists
1913 IF (g_asn_debug = 'Y') THEN
1914 asn_debug.put_line('Revision number : ' || x_cascaded_table(n).item_revision);
1915 END IF;
1916
1917 SELECT NVL(MAX(inventory_item_id), 0)
1918 INTO x_inventory_item
1919 FROM mtl_item_revisions
1920 WHERE inventory_item_id = l_active_item_id
1921 AND organization_id = NVL(x_cascaded_table(n).to_organization_id, organization_id)
1922 AND revision = x_cascaded_table(n).item_revision;
1923
1924 IF (x_inventory_item = 0) THEN
1925 rcv_error_pkg.set_error_message('PO_RI_INVALID_ITEM_REVISION');
1926 RAISE e_validation_error;
1927 END IF;
1928
1929 EXCEPTION
1930 WHEN e_validation_error THEN --Bug 6055435
1931 x_cascaded_table(n).error_status := x_error_status;
1932 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
1933
1934 IF x_cascaded_table(n).error_message = 'PO_RI_INVALID_ITEM_REVISION' THEN
1935 NULL;
1936 END IF;
1937 when others then
1938 IF (g_asn_debug = 'Y') THEN
1939 asn_debug.put_line('exception in valid_item_revision');
1940 asn_debug.put_line(SQLERRM);
1941 END IF;
1942 x_cascaded_table(n).error_status := 'E';
1943 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
1944
1945 END validate_item_revision;
1946
1947 /* lcm changes */
1948 PROCEDURE validate_lcm_info(p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type)
1949 IS
1950 l_lcm_org_flag VARCHAR2(1);
1951 l_pre_rcv_flag VARCHAR2(1);
1952 BEGIN
1953 IF (g_asn_debug = 'Y') THEN
1954 asn_debug.put_line('In Validate LCM Info');
1955 asn_debug.put_line('p_header_record.error_record.error_status ' || p_header_record.error_record.error_status);
1956 asn_debug.put_line('p_header_record.header_record.asn_type ' || p_header_record.header_record.asn_type);
1957 END IF;
1958
1959 IF (p_header_record.error_record.error_status NOT IN('S', 'W')) THEN
1960 RETURN;
1961 END IF;
1962
1963 IF (nvl(p_header_record.header_record.asn_type,'STD') = 'LCM') THEN
1964
1965 l_lcm_org_flag := rcv_table_functions.is_lcm_org(p_header_record.header_record.ship_to_organization_id);
1966 l_pre_rcv_flag := rcv_table_functions.is_pre_rcv_org(p_header_record.header_record.ship_to_organization_id);
1967
1968 IF (g_asn_debug = 'Y') THEN
1969 asn_debug.put_line('p_header_record.header_record.ship_to_organization_id ' || p_header_record.header_record.ship_to_organization_id);
1970 asn_debug.put_line('l_lcm_org_flag => ' || l_lcm_org_flag);
1971 asn_debug.put_line('l_pre_rcv_flag => ' || l_pre_rcv_flag);
1972 END IF;
1973
1974 IF (l_lcm_org_flag = 'Y') THEN
1975 IF ( l_pre_rcv_flag = 'N') THEN
1976 --
1977 /* LCM import is not supported in blackbox scenario */
1978 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1979 rcv_error_pkg.set_error_message('RCV_LCM_IMPORT_NOT_ALLOWED', p_header_record.error_record.error_message);
1980 rcv_error_pkg.set_token('ORG_ID', p_header_record.header_record.ship_to_organization_id);
1981 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'ASN_TYPE');
1982 --
1983 ELSE
1984 --
1985 IF (g_asn_debug = 'Y') THEN
1986 asn_debug.put_line('p_header_record.header_record.transaction_type ' || p_header_record.header_record.transaction_type , NULL, 11);
1987 END IF;
1988 IF (p_header_record.header_record.transaction_type <> 'NEW') THEN
1989 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1990 rcv_error_pkg.set_error_message('RCV_INVALID_ROI_VALUE_NE', p_header_record.error_record.error_message);
1991 rcv_error_pkg.set_token('COLUMN', 'TRANSACTION_TYPE');
1992 rcv_error_pkg.set_token('ROI_VALUE', p_header_record.header_record.transaction_type);
1993 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'TRANSACTION_TYPE');
1994 END IF;
1995 --
1996 END IF;
1997 ELSE
1998 /* LCM import is not supported in a non-lcm org */
1999 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
2000 rcv_error_pkg.set_error_message('RCV_LCM_IMPORT_NOT_ALLOWED', p_header_record.error_record.error_message);
2001 rcv_error_pkg.set_token('ORG_ID', p_header_record.header_record.ship_to_organization_id);
2002 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'ASN_TYPE');
2003 END IF;
2004 END IF;
2005
2006 IF (g_asn_debug = 'Y') THEN
2007 asn_debug.put_line('p_header_record.error_record.error_status' || p_header_record.error_record.error_status);
2008 asn_debug.put_line('Exitting validate_lcm_info');
2009 END IF;
2010
2011 EXCEPTION
2012 WHEN rcv_error_pkg.e_fatal_error THEN
2013 NULL;
2014 END validate_lcm_info;
2015
2016 END rcv_roi_header_common;
[Home] [Help]