1 PACKAGE BODY rcv_roi_header_common
2 /* $Header: RCVOIHCB.pls 120.14.12010000.3 2008/10/09 19:28:53 vthevark 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) := NVL(fnd_profile.VALUE('RCV_DEBUG_MODE'), 'N');
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 BEGIN
858 /* Validate Receipt Number */
859 IF (p_header_record.error_record.error_status NOT IN('S', 'W')) THEN
860 RETURN;
861 END IF;
862
863 IF p_header_record.header_record.receipt_num IS NULL
864 AND p_header_record.header_record.asn_type = 'STD'
865 AND p_header_record.header_record.transaction_type <> 'CANCEL' THEN -- added for support of cancel
866 IF (g_asn_debug = 'Y') THEN
867 asn_debug.put_line('Receipt Number is mandatory for STD');
868 END IF;
869
870 /* Bug 3590735.
871 * When we error out with receipt number mandatory error,
872 * we need to set this error in po_interface_errors.
873 */
874 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
875 rcv_error_pkg.set_error_message('RCV_RECEIPT_NUM_REQ', p_header_record.error_record.error_message);
876 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'RECEIPT_NUM');
877 END IF;
878
879 IF p_header_record.header_record.receipt_header_id IS NULL
880 AND -- bug 3508507: only check receipt_num uniqueness for new reciepts
881 -- X_new_receipt is populated in default_receipt_info()
882 p_header_record.header_record.receipt_num IS NOT NULL
883 AND p_header_record.header_record.transaction_type <> 'CANCEL' THEN -- added for support of cancel
884 SELECT COUNT(*)
885 INTO x_count
886 FROM rcv_shipment_headers
887 WHERE rcv_shipment_headers.receipt_num = p_header_record.header_record.receipt_num
888 AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id;
889
890 IF x_count > 0 THEN
891 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
892 rcv_error_pkg.set_error_message('PO_PDOI_RECEIPT_NUM_UNIQUE', p_header_record.error_record.error_message);
893 rcv_error_pkg.set_token('VALUE', p_header_record.header_record.receipt_num);
894 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'RECEIPT_NUM');
895 END IF;
896
897 IF (g_asn_debug = 'Y') THEN
898 asn_debug.put_line('validated receipt number');
899 END IF;
900 END IF;
901 EXCEPTION
902 WHEN rcv_error_pkg.e_fatal_error THEN
903 NULL;
904 END validate_receipt_num;
905
906 PROCEDURE validate_ship_to_org_info(
907 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
908 ) IS
909 BEGIN
910 /* Validate Ship To Organization Information */
911 IF (p_header_record.error_record.error_status NOT IN('S', 'W')) THEN
912 RETURN;
913 END IF;
914
915 IF p_header_record.header_record.asn_type IN('ASN', 'ASBN', 'STD', 'LCM') THEN /* lcm changes */
916 ship_to_org_record.organization_code := p_header_record.header_record.ship_to_organization_code;
917 ship_to_org_record.organization_id := p_header_record.header_record.ship_to_organization_id;
918 ship_to_org_record.error_record.error_status := rcv_error_pkg.g_ret_sts_success;
919 ship_to_org_record.error_record.error_message := NULL;
920
921 IF (g_asn_debug = 'Y') THEN
922 asn_debug.put_line('In Validate Ship to Organization Procedure');
923 END IF;
924
925 po_orgs_sv.validate_org_info(ship_to_org_record);
926
927 IF (ship_to_org_record.error_record.error_status <> 'S') THEN
928 IF ship_to_org_record.error_record.error_message = 'ORG_DISABLED' THEN
929 IF p_header_record.header_record.transaction_type <> 'CANCEL' THEN
930 IF (g_asn_debug = 'Y') THEN
931 asn_debug.put_line('Error with RCV_SHIPTO_ORG_DISABLED');
932 END IF;
933
934 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
935 rcv_error_pkg.set_error_message('RCV_SHIPTO_ORG_DISABLED', p_header_record.error_record.error_message);
936 rcv_error_pkg.set_token('ORGANIZATION', ship_to_org_record.organization_id);
937 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'SHIP_TO_ORGANIZATION_ID');
938 END IF;
939 ELSE
940 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
941 rcv_error_pkg.set_error_message('PO_PDOI_INVALID_SHIP_TO_ORG_ID', p_header_record.error_record.error_message);
942 rcv_error_pkg.set_token('VALUE', ship_to_org_record.organization_id);
943 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'SHIP_TO_ORGANIZATION_ID');
944 END IF;
945 END IF;
946
947 IF (g_asn_debug = 'Y') THEN
948 asn_debug.put_line('ship_to_org_record.error_status ' || ship_to_org_record.error_record.error_status);
949 asn_debug.put_line('validated ship to organization info');
950 END IF;
951 END IF;
952
953 /* Bug# 3662698.
954 Verify if any of the lines tied to the header have destination organization
955 different to that of the header's org (which is either populated or derived).
956 */
957 IF ( p_header_record.header_record.asn_type IN('ASN', 'ASBN', 'STD', 'LCM') /* lcm changes */
958 AND p_header_record.header_record.transaction_type <> 'CANCEL') THEN
959 /* Check if there is atleast one RTI record of this header with a
960 different org than the header's org. Here we consider those
961 RTI records which have to_organization_code or to_organization_id
962 as not null. Later below we check for those RTI records which have
963 to_organization_code and to_organization_id as null.
964 This logic is followed keeping in view of the performance problems.
965 */
966 IF (p_header_record.header_record.ship_to_organization_code IS NOT NULL) THEN
967 IF (g_asn_debug = 'Y') THEN
968 asn_debug.put_line('Checking if any RTI has different destn org than that of the header');
969 END IF;
970
971 SELECT COUNT(*)
972 INTO x_count
973 FROM rcv_transactions_interface rti,
974 rcv_headers_interface rhi
975 WHERE rti.header_interface_id = p_header_record.header_record.header_interface_id
976 AND rhi.header_interface_id = rti.header_interface_id
977 AND ( ( rti.to_organization_code IS NOT NULL
978 AND rti.to_organization_code <> p_header_record.header_record.ship_to_organization_code)
979 OR ( rti.to_organization_id IS NOT NULL
980 AND rti.to_organization_id <> p_header_record.header_record.ship_to_organization_id)
981 );
982
983 IF x_count >= 1 THEN
984 IF (g_asn_debug = 'Y') THEN
985 asn_debug.put_line('Atleast one of the RTIs has a different org id/code than that of the header');
986 END IF;
987
988 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
989 rcv_error_pkg.set_error_message('RCV_MUL_DESTN_ORGS_FOR_LINES', p_header_record.error_record.error_message);
990 rcv_error_pkg.set_token('VALUE', p_header_record.header_record.ship_to_organization_id);
991 rcv_error_pkg.log_interface_error('SHIP_TO_ORGANIZATION_ID');
992 ELSE
993 IF (g_asn_debug = 'Y') THEN
994 asn_debug.put_line('In the ELSE part');
995 END IF;
996
997 /* Check if there is atleast one RTI record in this header with a different
998 ship to org than the header's org. Here we consider those RTI records
999 which have to_organization_code and to_rganization_id as null and
1000 ship_to_location_id as not null. Records with all the above four columns
1001 as null need not be checked as header's org will be set to the line's org
1002 during the line level organization derivation.
1003 */
1004 SELECT COUNT(*)
1005 INTO x_count
1006 FROM rcv_transactions_interface rti,
1007 hr_locations hl,
1008 mtl_parameters org
1009 -- BugFix 5219284, replaced org_organization_definitions with mtl_parameters for better performance.
1010 WHERE rti.header_interface_id = p_header_record.header_record.header_interface_id
1011 AND rti.to_organization_code IS NULL
1012 AND rti.to_organization_id IS NULL
1013 AND rti.ship_to_location_id IS NOT NULL
1014 AND rti.ship_to_location_id = hl.location_id
1015 AND hl.inventory_organization_id = org.organization_id
1016 AND org.organization_code <> p_header_record.header_record.ship_to_organization_code;
1017
1018 IF (g_asn_debug = 'Y') THEN
1019 asn_debug.put_line('Count is ' || TO_CHAR(x_count));
1020 END IF;
1021
1022 /* Check if there is atleast one RTI record in this header with a different
1023 ship to org than the header's org. Here we consider those RTI records
1024 which have to_organization_code and to_rganization_id as null and
1025 ship_to_location_code as not null. A seperate sql is written using
1026 ship_location_code instead of adding it to the the WHERE caluse of the
1027 above sql to avoid full table scans on hr_locations.
1028 */
1029 IF x_count = 0 THEN
1030 SELECT COUNT(*)
1031 INTO x_count
1032 FROM rcv_transactions_interface rti,
1033 hr_locations hl,
1034 mtl_parameters org
1035 -- BugFix 5219284, replaced org_organization_definitions with mtl_parameters for better performance.
1036 WHERE rti.header_interface_id = p_header_record.header_record.header_interface_id
1037 AND rti.to_organization_code IS NULL
1038 AND rti.to_organization_id IS NULL
1039 AND rti.ship_to_location_code IS NOT NULL
1040 AND rti.ship_to_location_code = hl.location_code
1041 AND hl.inventory_organization_id = org.organization_id
1042 AND org.organization_code <> p_header_record.header_record.ship_to_organization_code;
1043 END IF;
1044
1045 IF x_count >= 1 THEN
1046 IF (g_asn_debug = 'Y') THEN
1047 asn_debug.put_line('For one of the RTI records a different org id/code is derived');
1048 END IF;
1049
1050 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1051 rcv_error_pkg.set_error_message('RCV_MUL_DESTN_ORGS_FOR_LINES', p_header_record.error_record.error_message);
1052 rcv_error_pkg.set_token('VALUE', p_header_record.header_record.ship_to_organization_id);
1053 rcv_error_pkg.log_interface_error('SHIP_TO_ORGANIZATION_ID');
1054 END IF;
1055 END IF;
1056
1057 IF (g_asn_debug = 'Y') THEN
1058 asn_debug.put_line('Validated ship to org of all the RTIs tied to the header');
1059 END IF;
1060 END IF;
1061 END IF; --End of bug# 3662698.
1062
1063 EXCEPTION
1064 WHEN rcv_error_pkg.e_fatal_error THEN
1065 NULL;
1066 END validate_ship_to_org_info;
1067
1068 PROCEDURE validate_from_org_info(
1069 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
1070 ) IS
1071 BEGIN
1072 /* validate from organization information */
1073 IF (p_header_record.error_record.error_status NOT IN('S', 'W')) THEN
1074 RETURN;
1075 END IF;
1076
1077 IF p_header_record.header_record.transaction_type <> 'CANCEL' THEN
1078 IF from_org_record.organization_code IS NOT NULL
1079 OR from_org_record.organization_id IS NOT NULL THEN
1080 from_org_record.organization_code := p_header_record.header_record.from_organization_code;
1081 from_org_record.organization_id := p_header_record.header_record.from_organization_id;
1082 from_org_record.error_record.error_status := rcv_error_pkg.g_ret_sts_success;
1083 from_org_record.error_record.error_message := NULL;
1084
1085 IF (g_asn_debug = 'Y') THEN
1086 asn_debug.put_line('In Validate From Organization Procedure');
1087 END IF;
1088
1089 po_orgs_sv.validate_org_info(from_org_record);
1090
1091 IF (from_org_record.error_record.error_status <> 'S') THEN
1092 IF from_org_record.error_record.error_message = 'ORG_DISABLED' THEN
1093 IF p_header_record.header_record.transaction_type <> 'CANCEL' THEN
1094 IF (g_asn_debug = 'Y') THEN
1095 asn_debug.put_line('Error with RCV_SHIPTO_ORG_DISABLED');
1096 END IF;
1097
1098 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1099 rcv_error_pkg.set_error_message('RCV_FROM_ORG_DISABLED', p_header_record.error_record.error_message);
1100 rcv_error_pkg.set_token('ORGANIZATION', from_org_record.organization_code);
1101 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'FROM_ORGANIZATION_ID');
1102 END IF;
1103 ELSE
1104 IF (g_asn_debug = 'Y') THEN
1105 asn_debug.put_line('Error with from ORG_ID');
1106 END IF;
1107
1108 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1109 rcv_error_pkg.set_error_message('RCV_FROM_ORG_ID', p_header_record.error_record.error_message);
1110 rcv_error_pkg.set_token('ORGANIZATION', from_org_record.organization_code);
1111 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'FROM_ORGANIZATION_ID');
1112 END IF;
1113 END IF;
1114
1115 IF (g_asn_debug = 'Y') THEN
1116 asn_debug.put_line('validated from organization info');
1117 END IF;
1118 END IF;
1119 END IF;
1120 EXCEPTION
1121 WHEN rcv_error_pkg.e_fatal_error THEN
1122 NULL;
1123 END validate_from_org_info;
1124
1125 PROCEDURE validate_location_info(
1126 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
1127 ) IS
1128 BEGIN
1129 /* Validate Location 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'
1135 AND p_header_record.header_record.asn_type IN('ASN', 'ASBN', 'STD', 'LCM') /* lcm changes */
1136 AND ( p_header_record.header_record.location_code IS NOT NULL
1137 OR p_header_record.header_record.location_id IS NOT NULL) THEN
1138 loc_record.location_code := p_header_record.header_record.location_code;
1139 loc_record.location_id := p_header_record.header_record.location_id;
1140 loc_record.organization_id := p_header_record.header_record.ship_to_organization_id;
1141 loc_record.error_record.error_status := rcv_error_pkg.g_ret_sts_success;
1142 loc_record.error_record.error_message := NULL;
1143
1144 IF (g_asn_debug = 'Y') THEN
1145 asn_debug.put_line('In Validate Location Code Procedure');
1146 END IF;
1147
1148 po_locations_s.validate_location_info(loc_record);
1149
1150 IF loc_record.error_record.error_status <> 'S' THEN
1151 IF loc_record.error_record.error_message = 'LOC_NOT_IN_ORG' THEN
1152 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1153 rcv_error_pkg.set_error_message('RCV_LOC_NOT_IN_ORG', p_header_record.error_record.error_message);
1154 rcv_error_pkg.set_token('LOCATION', loc_record.location_id);
1155 rcv_error_pkg.set_token('ORGANIZATION', loc_record.organization_id);
1156 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'LOCATION_ID');
1157 ELSE
1158 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1159 rcv_error_pkg.set_error_message('PO_PDOI_INVALID_SHIP_TO_LOC_ID', p_header_record.error_record.error_message);
1160 rcv_error_pkg.set_token('VALUE', loc_record.location_id);
1161 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'LOCATION_ID');
1162 END IF;
1163 END IF;
1164
1165 IF (g_asn_debug = 'Y') THEN
1166 asn_debug.put_line(loc_record.error_record.error_status);
1167 asn_debug.put_line(loc_record.error_record.error_message);
1168 asn_debug.put_line('Validated location info');
1169 END IF;
1170 END IF;
1171 EXCEPTION
1172 WHEN rcv_error_pkg.e_fatal_error THEN
1173 NULL;
1174 END validate_location_info;
1175
1176 PROCEDURE validate_ship_from_loc_info(
1177 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
1178 ) IS
1179 x_dummy NUMBER;
1180 p_in_rec wsh_po_integration_grp.validatesf_in_rec_type;
1181 x_out_rec wsh_po_integration_grp.validatesf_out_rec_type;
1182 x_return_status VARCHAR2(3);
1183 x_msg_count NUMBER;
1184 x_msg_data VARCHAR2(2000);
1185 l_shipping_control VARCHAR2(30); --Bug 5263268
1186
1187 CURSOR get_lines IS
1188 SELECT po_line_id,
1189 po_line_location_id po_shipment_line_id
1190 FROM rcv_transactions_interface
1191 WHERE header_interface_id = p_header_record.header_record.header_interface_id;
1192
1193 --Bug5263268:Cursor to fetch the value of "Shipping_control" from po_headers table.
1194 --Note:-ASN or ASBN can be created for multiple PO's provided they have the same
1195 --value for shing control.It is not possible to create a single ASN or ASBN with one PO
1196 --having shipping control as 'buyer' and another PO with shipping control as 'supplier' or
1197 --shippign control is null.
1198 --So there is no need to loop through the records fetched by the cursor.
1199
1200 CURSOR c_get_shipping_control is
1201 select shipping_control
1202 from po_headers_all
1203 where po_header_id = (select po_header_id
1204 from rcv_transactions_interface
1205 where header_interface_id = p_header_record.header_record.header_interface_id
1206 and rownum=1); --Bugfix 5844039
1207 BEGIN
1208 /* Validate Location Information */
1209 IF (p_header_record.error_record.error_status NOT IN('S', 'W')) THEN
1210 RETURN;
1211 END IF;
1212
1213 --Bug 5263268:If the shipping_control is 'BUYER' and ship_from_location_id is NULL
1214 --the the transaction should error out.
1215 open c_get_shipping_control;
1216 fetch c_get_shipping_control into l_shipping_control;
1217 close c_get_shipping_control;
1218
1219 IF (nvl(l_shipping_control,'@@@') = 'BUYER' AND p_header_record.header_record.ship_from_location_id IS NULL) THEN
1220 IF (g_asn_debug = 'Y') THEN
1221 asn_debug.put_line('Ship from location id cannot be null if shipping_control is BUYER');
1222 END IF;
1223 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1224 rcv_error_pkg.set_error_message('RCV_INVALID_ROI_VALUE_NE');
1225 rcv_error_pkg.set_token('ROI_VALUE',p_header_record.header_record.ship_from_location_id);
1226 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'SHIP_FROM_LOCATION_ID');
1227
1228 END IF;
1229 --End Bug 5263268
1230
1231 IF p_header_record.header_record.ship_from_location_id IS NOT NULL THEN
1232 p_in_rec.ship_from_location_id := p_header_record.header_record.ship_from_location_id;
1233 open get_lines;
1234 fetch get_lines bulk collect into p_in_rec.po_line_id_tbl,p_in_rec.po_shipment_line_id_tbl;
1235 close get_lines;
1236
1237 wsh_po_integration_grp.validateasnreceiptshipfrom(1.0,
1238 fnd_api.g_false,
1239 p_in_rec,
1240 fnd_api.g_false,
1241 x_return_status,
1242 x_out_rec,
1243 x_msg_count,
1244 x_msg_data
1245 );
1246
1247 IF (x_out_rec.is_valid = FALSE) THEN
1248 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1249 rcv_error_pkg.set_error_message('RCV_LOC_NOT_IN_ORG', p_header_record.error_record.error_message);
1250 rcv_error_pkg.set_token('LOCATION', p_header_record.header_record.ship_from_location_id);
1251 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'SHIP_FROM_LOCATION_ID');
1252 END IF;
1253
1254 IF (g_asn_debug = 'Y') THEN
1255 asn_debug.put_line('Validated location info with status=' || p_header_record.error_record.error_status);
1256 END IF;
1257 END IF;
1258 EXCEPTION
1259 WHEN rcv_error_pkg.e_fatal_error THEN
1260 NULL;
1261 END validate_ship_from_loc_info;
1262
1263 PROCEDURE validate_payment_terms_info(
1264 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
1265 ) IS
1266 BEGIN
1267 /* Validate Payment Terms Information */
1268 IF (p_header_record.error_record.error_status NOT IN('S', 'W')) THEN
1269 RETURN;
1270 END IF;
1271
1272 IF ( p_header_record.header_record.payment_terms_name IS NOT NULL
1273 OR p_header_record.header_record.payment_terms_id IS NOT NULL)
1274 AND p_header_record.header_record.transaction_type <> 'CANCEL' THEN
1275 pay_record.payment_term_id := p_header_record.header_record.payment_terms_id;
1276 pay_record.payment_term_name := p_header_record.header_record.payment_terms_name;
1277 pay_record.error_record.error_status := rcv_error_pkg.g_ret_sts_success;
1278 pay_record.error_record.error_message := NULL;
1279
1280 IF (g_asn_debug = 'Y') THEN
1281 asn_debug.put_line('In Validate Payment Terms ');
1282 END IF;
1283
1284 po_terms_sv.validate_payment_terms_info(pay_record);
1285
1286 IF ( pay_record.error_record.error_message = 'PAY_TERMS_DISABLED'
1287 AND NVL(p_header_record.header_record.asn_type, 'ASN') <> 'ASBN') THEN
1288 pay_record.error_record.error_status := 'S';
1289 END IF;
1290
1291 IF pay_record.error_record.error_status <> 'S' THEN
1292 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1293 rcv_error_pkg.set_error_message('PO_PDOI_INVALID_PAY_TERMS', p_header_record.error_record.error_message);
1294 rcv_error_pkg.set_token('VALUE', pay_record.payment_term_id);
1295 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'PAYMENT_TERMS_ID');
1296 END IF;
1297
1298 IF (g_asn_debug = 'Y') THEN
1299 asn_debug.put_line(pay_record.error_record.error_status);
1300 END IF;
1301
1302 IF (g_asn_debug = 'Y') THEN
1303 asn_debug.put_line('Validated payment info');
1304 END IF;
1305 END IF;
1306 EXCEPTION
1307 WHEN rcv_error_pkg.e_fatal_error THEN
1308 NULL;
1309 END validate_payment_terms_info;
1310
1311 PROCEDURE validate_receiver_info(
1312 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
1313 ) IS
1314 BEGIN
1315 /* validate receiver information */
1316 IF (p_header_record.error_record.error_status NOT IN('S', 'W')) THEN
1317 RETURN;
1318 END IF;
1319
1320 IF p_header_record.header_record.transaction_type <> 'CANCEL'
1321 AND p_header_record.header_record.auto_transact_code = 'RECEIVE'
1322 AND ( p_header_record.header_record.employee_name IS NOT NULL
1323 OR p_header_record.header_record.employee_id IS NOT NULL) THEN
1324 emp_record.employee_name := p_header_record.header_record.employee_name;
1325 emp_record.employee_id := p_header_record.header_record.employee_id;
1326 emp_record.error_record.error_status := rcv_error_pkg.g_ret_sts_success;
1327 emp_record.error_record.error_message := NULL;
1328
1329 IF (g_asn_debug = 'Y') THEN
1330 asn_debug.put_line('In Validate Receiver Information');
1331 END IF;
1332
1333 po_employees_sv.validate_employee_info(emp_record);
1334
1335 IF (g_asn_debug = 'Y') THEN
1336 asn_debug.put_line(emp_record.error_record.error_status);
1337 END IF;
1338
1339 IF emp_record.error_record.error_status <> 'S' THEN
1340 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1341 rcv_error_pkg.set_error_message('RCV_RECEIVER_ID', p_header_record.error_record.error_message);
1342 rcv_error_pkg.set_token('NAME', emp_record.employee_name);
1343 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'EMPLOYEE_ID');
1344 END IF;
1345
1346 IF (g_asn_debug = 'Y') THEN
1347 asn_debug.put_line('Validated receiver info');
1348 END IF;
1349 END IF;
1350 EXCEPTION
1351 WHEN rcv_error_pkg.e_fatal_error THEN
1352 NULL;
1353 END validate_receiver_info;
1354
1355 PROCEDURE validate_freight_carrier_info(
1356 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
1357 ) IS
1358 BEGIN
1359 /* validate freight carrier information */
1360 /* ASN and ASBN, al transaction_types except CANCEL */
1361 /* Carrier is specified */
1362 IF (p_header_record.error_record.error_status NOT IN('S', 'W')) THEN
1363 RETURN;
1364 END IF;
1365
1366 IF p_header_record.header_record.transaction_type <> 'CANCEL'
1367 AND p_header_record.header_record.freight_carrier_code IS NOT NULL THEN
1368 freight_record.freight_carrier_code := p_header_record.header_record.freight_carrier_code;
1369 freight_record.organization_id := p_header_record.header_record.ship_to_organization_id;
1370 freight_record.error_record.error_status := rcv_error_pkg.g_ret_sts_success;
1371 freight_record.error_record.error_message := NULL;
1372
1373 IF (g_asn_debug = 'Y') THEN
1374 asn_debug.put_line('In Validate Freight Carrier Information');
1375 END IF;
1376
1377 po_terms_sv.validate_freight_carrier_info(freight_record);
1378
1379 IF freight_record.error_record.error_status <> 'S' THEN
1380 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1381 rcv_error_pkg.set_error_message('RCV_CARRIER_DISABLED', p_header_record.error_record.error_message);
1382 rcv_error_pkg.set_token('CARRIER', freight_record.freight_carrier_code);
1383 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'FREIGHT_CARRIER_CODE');
1384 END IF;
1385
1386 IF (g_asn_debug = 'Y') THEN
1387 asn_debug.put_line(freight_record.error_record.error_status);
1388 asn_debug.put_line('Validated freight carrier info');
1389 END IF;
1390 END IF;
1391 EXCEPTION
1392 WHEN rcv_error_pkg.e_fatal_error THEN
1393 NULL;
1394 END validate_freight_carrier_info;
1395
1396 PROCEDURE validate_shipment_date(
1397 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
1398 ) IS
1399 x_sysdate DATE := SYSDATE;
1400 BEGIN
1401 /* Validation for Shipment Date > System Date and not NULL,blank,zero */
1402 IF (p_header_record.error_record.error_status NOT IN('S', 'W')) THEN
1403 RETURN;
1404 END IF;
1405
1406 IF NVL(p_header_record.header_record.shipped_date, x_sysdate + 1) > x_sysdate THEN
1407 /* R12 Complex Work.
1408 * There is no concept of shipped_date for Work Confirmations.
1409 * So shipped_date can be null.
1410 */
1411 IF (g_asn_debug = 'Y') THEN
1412 asn_debug.put_line('asn_type ' || p_header_record.header_record.asn_type);
1413 END IF;
1414 IF p_header_record.header_record.shipped_date IS NULL
1415 AND p_header_record.header_record.asn_type IN ('WC', 'STD') THEN
1416 IF (g_asn_debug = 'Y') THEN
1417 asn_debug.put_line('Shipped date can be blank for STD '||
1418 'or Work Confirmations ');
1419 END IF;
1420 ELSE
1421 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1422 rcv_error_pkg.set_error_message('RCV_SHIP_DATE_INVALID', p_header_record.error_record.error_message);
1423 rcv_error_pkg.set_token('SHIP_DATE', fnd_date.date_to_chardate(p_header_record.header_record.shipped_date));
1424 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'SHIPPED_DATE');
1425 END IF;
1426 END IF;
1427
1428 IF (g_asn_debug = 'Y') THEN
1429 asn_debug.put_line('validated for shipment_date > system date');
1430 END IF;
1431 EXCEPTION
1432 WHEN rcv_error_pkg.e_fatal_error THEN
1433 NULL;
1434 END validate_shipment_date;
1435
1436 PROCEDURE validate_item(
1437 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1438 n IN BINARY_INTEGER
1439 ) IS -- bug 608353
1440 x_progress VARCHAR2(3);
1441 l_stock_enabled_flag mtl_system_items.stock_enabled_flag%TYPE; -- Bugfix 5735599
1442 l_inventory_item_flag mtl_system_items.inventory_item_flag%TYPE; -- Bugfix 5735599
1443 x_inventory_item mtl_system_items.inventory_item_id%TYPE := NULL;
1444 x_organization_id mtl_system_items.organization_id%TYPE := NULL;
1445 x_item_id_po po_lines.item_id%TYPE := NULL;
1446 x_error_status VARCHAR2(1);
1447 BEGIN
1448 asn_debug.put_line('inside validate item : receipt_source_code = ' || x_cascaded_table(n).receipt_source_code);
1449 x_error_status := rcv_error_pkg.g_ret_sts_error;
1450
1451 SELECT NVL(MAX(inventory_item_id), -9999)
1452 INTO x_inventory_item
1453 FROM mtl_system_items
1454 WHERE inventory_item_id = x_cascaded_table(n).item_id;
1455
1456 IF (x_inventory_item = -9999) THEN
1457 rcv_error_pkg.set_error_message('RCV_ITEM_ID');
1458 RAISE e_validation_error;
1459 END IF;
1460
1461 SELECT NVL(MAX(inventory_item_id), -9999)
1462 INTO x_inventory_item
1463 FROM mtl_system_items
1464 WHERE SYSDATE BETWEEN NVL(start_date_active, SYSDATE - 1) AND NVL(end_date_active, SYSDATE + 1)
1465 AND inventory_item_id = x_cascaded_table(n).item_id;
1466
1467 IF (x_inventory_item = -9999) THEN
1468 rcv_error_pkg.set_error_message('RCV_ITEM_NOT_ACTIVE');
1469 RAISE e_validation_error;
1470 END IF;
1471
1472 -- Bugfix 5735599
1473 -- When item status is changed to INACTIVE all the flags are unchecked.
1474 -- Hence to check inactive item we should check for STOCK_ENABLED_FLAG.
1475
1476 IF (g_asn_debug = 'Y') THEN
1477 asn_debug.put_line('x_cascaded_table(n).auto_transact_code: ' || x_cascaded_table(n).auto_transact_code);
1478 asn_debug.put_line('x_cascaded_table(n).TO_ORGANIZATION_ID: ' || x_cascaded_table(n).to_organization_id);
1479 asn_debug.put_line('x_cascaded_table(n).item_id: ' || x_cascaded_table(n).item_id);
1480 asn_debug.put_line('x_cascaded_table(n).TRANSACTION_TYPE ' || x_cascaded_table(n).transaction_type );
1481 END IF;
1482
1483 BEGIN
1484 SELECT stock_enabled_flag,
1485 inventory_item_flag
1486 INTO l_stock_enabled_flag,
1487 l_inventory_item_flag
1488 FROM mtl_system_items
1489 WHERE organization_id = x_cascaded_table(n).to_organization_id
1490 AND inventory_item_id = x_cascaded_table(n).item_id;
1491 EXCEPTION
1492 WHEN OTHERS
1493 THEN
1494 IF (g_asn_debug = 'Y') THEN
1495 asn_debug.put_line('Error occured while checking inactive item in rcv_roi_header_common procedure. Error :: ' || SQLERRM );
1496 END IF;
1497
1498 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1499 rcv_error_pkg.set_sql_error_message('rcv_roi_header_common.validate_item', '000');
1500 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
1501 rcv_error_pkg.log_interface_error('ITEM', TRUE);
1502
1503 RETURN;
1504 END;
1505
1506 -- If the item is inactive and routing is DIRECT then we should allow the first receipt as well.
1507
1508 IF l_inventory_item_flag = 'Y' AND l_stock_enabled_flag = 'N' AND
1509 (x_cascaded_table(n).auto_transact_code = 'DELIVER' OR x_cascaded_table(n).transaction_type = 'DELIVER')
1510 THEN
1511 rcv_error_pkg.set_error_message('RCV_ITEM_NOT_ACTIVE');
1512 RAISE e_validation_error;
1513 END IF;
1514
1515 -- End of code for Bugfix 5735599
1516
1517 /* Bug 2160314.
1518 * We used to have nvl(max(organization_id),0) here before. But if the
1519 * organization_id is itself 0, then this will give us a problem in
1520 * the next step when we check if x_organization_id = 0. So changed
1521 * the statement to nvl(max(organization_id),-9999) and also the
1522 * check below. Similarly changed the select statement and the
1523 * check for nvl(max(item_id),0).
1524 */
1525 SELECT NVL(MAX(organization_id), -9999)
1526 INTO x_organization_id
1527 FROM mtl_system_items
1528 WHERE inventory_item_id = x_cascaded_table(n).item_id
1529 AND organization_id = NVL(x_cascaded_table(n).to_organization_id, organization_id);
1530
1531 IF (x_organization_id = -9999) THEN
1532 rcv_error_pkg.set_error_message('RCV_ITEM_NOT_IN_ORG');
1533 RAISE e_validation_error;
1534 END IF;
1535
1536 -- do these checks only for PO based transactions
1537 IF x_cascaded_table(n).receipt_source_code = 'VENDOR' THEN --{
1538 SELECT NVL(MAX(item_id), -9999)
1539 INTO x_item_id_po
1540 FROM po_lines
1541 WHERE po_line_id = x_cascaded_table(n).po_line_id
1542 AND item_id = x_cascaded_table(n).item_id;
1543
1544 IF (x_item_id_po = -9999) THEN
1545 rcv_error_pkg.set_error_message('RCV_ITEM_NOT_ON_PO');
1546 RAISE e_validation_error;
1547 END IF;
1548
1549 SELECT NVL(MAX(item_id), -9999)
1550 INTO x_item_id_po
1551 FROM po_lines
1552 WHERE po_line_id = x_cascaded_table(n).po_line_id
1553 AND item_id = x_cascaded_table(n).item_id;
1554
1555 IF (x_item_id_po <> x_cascaded_table(n).item_id) THEN
1556 rcv_error_pkg.set_error_message('RCV_NOT_PO_LINE_NUM');
1557 RAISE e_validation_error;
1558 END IF;
1559 END IF; --}
1560
1561 /* bug 608353, do not support lot and serial control if DELIVER is used */
1562 IF (g_asn_debug = 'Y') THEN
1563 asn_debug.put_line('Validating Item: ' || x_cascaded_table(n).auto_transact_code);
1564 asn_debug.put_line('Validating Item: ' || x_cascaded_table(n).use_mtl_lot);
1565 asn_debug.put_line('Validating Item: ' || x_cascaded_table(n).use_mtl_serial);
1566 END IF;
1567 EXCEPTION
1568 WHEN e_validation_error THEN
1569 x_cascaded_table(n).error_status := x_error_status;
1570 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
1571
1572 IF x_cascaded_table(n).error_message = 'RCV_ITEM_ID' THEN
1573 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).item_id);
1574 ELSIF x_cascaded_table(n).error_message = 'RCV_ITEM_NOT_ACTIVE' THEN
1575 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).item_id);
1576 ELSIF x_cascaded_table(n).error_message = 'RCV_ITEM_NOT_IN_ORG' THEN
1577 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).item_id);
1578 rcv_error_pkg.set_token('ORGANIZATION', x_cascaded_table(n).to_organization_id);
1579 ELSIF x_cascaded_table(n).error_message = 'RCV_ITEM_NOT_ON_PO' THEN
1580 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).item_id);
1581 rcv_error_pkg.set_token('ORGANIZATION', x_cascaded_table(n).to_organization_id);
1582 ELSIF x_cascaded_table(n).error_message = 'RCV_NOT_PO_LINE_NUM' THEN
1583 rcv_error_pkg.set_token('PO_ITEM', x_item_id_po);
1584 rcv_error_pkg.set_token('SHIPMENT_ITEM', x_cascaded_table(n).item_id);
1585 END IF;
1586 END validate_item;
1587
1588 PROCEDURE validate_substitute_item(
1589 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1590 n IN BINARY_INTEGER
1591 ) IS
1592 x_inventory_item mtl_system_items.inventory_item_id%TYPE := NULL;
1593 x_progress VARCHAR2(3);
1594 x_vendor_id po_vendors.vendor_id%TYPE := NULL;
1595 x_error_status VARCHAR2(1);
1596 x_allow_sub_flag VARCHAR2(1);
1597 BEGIN
1598 x_error_status := rcv_error_pkg.g_ret_sts_error;
1599
1600 SELECT NVL(MAX(inventory_item_id), 0)
1601 INTO x_inventory_item
1602 FROM mtl_system_items
1603 WHERE inventory_item_id = x_cascaded_table(n).substitute_item_id
1604 AND organization_id = NVL(x_cascaded_table(n).to_organization_id, organization_id);
1605
1606 IF (x_inventory_item = 0) THEN
1607 rcv_error_pkg.set_error_message('RCV_ITEM_SUB_ID');
1608 RAISE e_validation_error;
1609 END IF;
1610
1611 SELECT NVL(MAX(inventory_item_id), 0)
1612 INTO x_inventory_item
1613 FROM mtl_system_items
1614 WHERE SYSDATE BETWEEN NVL(start_date_active, SYSDATE - 1) AND NVL(end_date_active, SYSDATE + 1)
1615 AND inventory_item_id = x_cascaded_table(n).substitute_item_id
1616 AND organization_id = NVL(x_cascaded_table(n).to_organization_id, organization_id);
1617
1618 IF (x_inventory_item = 0) THEN
1619 rcv_error_pkg.set_error_message('RCV_ITEM_SUB_NOT_ACTIVE');
1620 RAISE e_validation_error;
1621 END IF;
1622
1623 -- do these checks only for PO based transactions
1624 IF x_cascaded_table(n).receipt_source_code = 'VENDOR' THEN --{
1625 --bug 3825246, need to check the allow_substitute_flag at both the
1626 --item level and on the po shipment lines level
1627 --the MIN gives No a priority over Yes, and the NVL covers the case where they are both null
1628 /* SELECT NVL(MIN(allow_substitute_receipts_flag),'N')
1629 INTO x_allow_sub_flag
1630 FROM (SELECT allow_substitute_receipts_flag
1631 FROM mtl_system_items
1632 WHERE inventory_item_id = (SELECT item_id
1633 FROM po_lines
1634 WHERE po_line_id = x_cascaded_table(n).po_line_id)
1635 AND organization_id = NVL(x_cascaded_table(n).to_organization_id, organization_id)
1636 UNION ALL
1637 SELECT allow_substitute_receipts_flag
1638 FROM po_line_locations
1639 WHERE line_location_id = x_cascaded_table(n).po_line_location_id);
1640 */
1641 -- Bugfix 5219284, Abobe query is replaced with following for performance reason.
1642
1643 SELECT NVL(MIN(allow_substitute_receipts_flag),'N')
1644 INTO x_allow_sub_flag
1645 FROM (SELECT msi.allow_substitute_receipts_flag
1646 FROM mtl_system_items msi,
1647 po_lines_all pl
1648 WHERE msi.inventory_item_id = pl.item_id
1649 AND pl.po_line_id = x_cascaded_table(n).po_line_id
1650 AND msi.organization_id = NVL(x_cascaded_table(n).to_organization_id, organization_id)
1651 UNION ALL
1652 SELECT allow_substitute_receipts_flag
1653 FROM po_line_locations
1654 WHERE line_location_id = x_cascaded_table(n).po_line_location_id);
1655 IF (x_allow_sub_flag = 'N') THEN
1656 rcv_error_pkg.set_error_message('RCV_ITEM_SUB_NOT_ALLOWED');
1657 RAISE e_validation_error;
1658 END IF;
1659
1660 SELECT NVL(MAX(inventory_item_id), 0)
1661 INTO x_inventory_item
1662 FROM mtl_system_items
1663 WHERE inventory_item_id = x_cascaded_table(n).substitute_item_id
1664 AND organization_id = NVL(x_cascaded_table(n).to_organization_id, organization_id);
1665
1666 IF (x_inventory_item = 0) THEN
1667 rcv_error_pkg.set_error_message('RCV_ITEM_SUB_NOT_IN_ORG');
1668 RAISE e_validation_error;
1669 END IF;
1670
1671 SELECT NVL(MAX(vendor_id), 0)
1672 INTO x_vendor_id
1673 FROM po_vendors
1674 WHERE vendor_id = x_cascaded_table(n).vendor_id
1675 AND allow_substitute_receipts_flag = 'Y';
1676
1677 IF (x_vendor_id = 0) THEN
1678 rcv_error_pkg.set_error_message('RCV_ITEM_SUB_VEN_NOT_ALLOWED');
1679 RAISE e_validation_error;
1680 END IF;
1681
1682 -- Need to check for related items if reciprocal_flag is set
1683 -- Thus need to use union as user may not have set up both
1684 -- the items to substitute for each other and just used
1685 -- reciprocal_flag for this
1686 -- relationship_type_id = 2 for substitute items
1687 -- = 1 for related items
1688
1689 /* SELECT NVL(MAX(inventory_item_id), 0)
1690 INTO x_inventory_item
1691 FROM mtl_related_items
1692 WHERE inventory_item_id = (SELECT item_id
1693 FROM po_lines
1694 WHERE po_line_id = x_cascaded_table(n).po_line_id)
1695 AND related_item_id = x_cascaded_table(n).substitute_item_id
1696 AND relationship_type_id = 2; -- substitute items
1697 -- and organization_id = nvl(x_cascaded_table(n).to_organization_id,organization_id)
1698 */
1699
1700 -- Bugfix 5219284, Above query is replaced by following query for performance issues.
1701
1702 SELECT NVL(MAX(inventory_item_id), 0)
1703 INTO x_inventory_item
1704 FROM mtl_related_items mri,
1705 po_lines_all pl
1706 WHERE mri.inventory_item_id = pl.item_id
1707 AND pl.po_line_id = x_cascaded_table(n).po_line_id
1708 AND mri.related_item_id = x_cascaded_table(n).substitute_item_id
1709 AND mri.relationship_type_id = 2; -- substitute items
1710 -- and organization_id = nvl(x_cascaded_table(n).to_organization_id,organization_id)
1711
1712
1713 IF x_inventory_item = 0 THEN
1714 -- Try the reciprocal relationship
1715
1716 /* SELECT NVL(MAX(inventory_item_id), 0)
1717 INTO x_inventory_item
1718 FROM mtl_related_items
1719 WHERE related_item_id = (SELECT item_id
1720 FROM po_lines
1721 WHERE po_line_id = x_cascaded_table(n).po_line_id)
1722 AND inventory_item_id = x_cascaded_table(n).substitute_item_id
1723 AND reciprocal_flag = 'Y'
1724 AND relationship_type_id = 2;
1725 -- and organization_id = nvl(x_cascaded_table(n).to_organization_id,organization_id)
1726 */
1727 -- Bugfix 5219284, Above query is replaced by following query for performance issues.
1728
1729 SELECT NVL(MAX(inventory_item_id), 0)
1730 INTO x_inventory_item
1731 FROM mtl_related_items mri,
1732 po_lines_all pl
1733 WHERE mri.related_item_id = pl.item_id
1734 AND pl.po_line_id = x_cascaded_table(n).po_line_id
1735 AND mri.inventory_item_id = x_cascaded_table(n).substitute_item_id
1736 AND mri.reciprocal_flag = 'Y'
1737 AND mri.relationship_type_id = 2;
1738
1739 END IF;
1740
1741 IF (x_inventory_item = 0) THEN
1742 rcv_error_pkg.set_error_message('RCV_ITEM_SUB_NOT_RELATED');
1743 RAISE e_validation_error;
1744 END IF;
1745 END IF; --}
1746 EXCEPTION
1747 WHEN e_validation_error THEN
1748 x_cascaded_table(n).error_status := x_error_status;
1749 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
1750
1751 IF x_cascaded_table(n).error_message = 'RCV_ITEM_SUB_ID' THEN
1752 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).substitute_item_id);
1753 ELSIF x_cascaded_table(n).error_message = 'RCV_ITEM_SUB_NOT_ACTIVE' THEN
1754 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).substitute_item_id);
1755 ELSIF x_cascaded_table(n).error_message = 'RCV_ITEM_SUB_NOT_ALLOWED' THEN
1756 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).substitute_item_id);
1757 ELSIF x_cascaded_table(n).error_message = 'RCV_ITEM_SUB_NOT_IN_ORG' THEN
1758 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).substitute_item_id);
1759 rcv_error_pkg.set_token('ORGANIZATION', x_cascaded_table(n).to_organization_id);
1760 ELSIF x_cascaded_table(n).error_message = 'RCV_ITEM_SUB_VEN_NOT_ALLOWED' THEN
1761 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).substitute_item_id);
1762 rcv_error_pkg.set_token('SUPPLIER', x_cascaded_table(n).vendor_id);
1763 ELSIF x_cascaded_table(n).error_message = 'RCV_ITEM_SUB_NOT_RELATED' THEN
1764 rcv_error_pkg.set_token('SUB_ITEM', x_cascaded_table(n).substitute_item_id);
1765 rcv_error_pkg.set_token('ITEM', x_inventory_item);
1766 END IF;
1767 END validate_substitute_item;
1768
1769 PROCEDURE validate_item_revision(
1770 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1771 n IN BINARY_INTEGER
1772 ) IS
1773 x_inventory_item mtl_system_items.inventory_item_id%TYPE := NULL;
1774 x_progress VARCHAR2(3);
1775 x_revision_control_flag VARCHAR2(1);
1776 x_item_revision po_lines.item_revision%TYPE;
1777 x_error_status VARCHAR2(1);
1778
1779 /* Bug 5339860
1780 * Added support for substitute item revision validation
1781 * */
1782
1783 l_substitute_item BOOLEAN;
1784 l_active_item_id NUMBER;
1785 BEGIN
1786
1787 /** Bug 6055435
1788 * 1) Removed the validation of item revision mentioned in PO and the one
1789 * stamped in RTI, as the revision mentioned in PO can be changed at any
1790 * point of time. And moreover through forms we are allowing to receive/deliver
1791 * different item rev than the one mentioned in PO.
1792 * 2) Removed all the commented piece of codes, as the code looks clumsy.
1793 * 3) Removed the unnecessary error code part from the 'WHEN e_validation_error'
1794 * exception handler block.
1795 */
1796 x_error_status := rcv_error_pkg.g_ret_sts_error;
1797
1798 IF x_cascaded_table(n).substitute_item_id IS NOT NULL THEN
1799 l_substitute_item := TRUE;
1800 l_active_item_id := x_cascaded_table(n).substitute_item_id;
1801 ELSE
1802 l_substitute_item := FALSE;
1803 l_active_item_id := x_cascaded_table(n).item_id;
1804 END IF;
1805
1806 -- check whether the item is under revision control
1807 -- If it is not then item should not have any revisions
1808
1809 SELECT DECODE(msi.revision_qty_control_code,
1810 1, 'N',
1811 2, 'Y',
1812 'N'
1813 )
1814 INTO x_revision_control_flag
1815 FROM mtl_system_items msi
1816 WHERE inventory_item_id = l_active_item_id
1817 AND organization_id = x_cascaded_table(n).to_organization_id;
1818
1819 IF x_revision_control_flag = 'N' THEN --BUG: 5975270
1820 RETURN;
1821 END IF;
1822
1823 -- Check whether the revision number exists
1824 IF (g_asn_debug = 'Y') THEN
1825 asn_debug.put_line('Revision number : ' || x_cascaded_table(n).item_revision);
1826 END IF;
1827
1828 SELECT NVL(MAX(inventory_item_id), 0)
1829 INTO x_inventory_item
1830 FROM mtl_item_revisions
1831 WHERE inventory_item_id = l_active_item_id
1832 AND organization_id = NVL(x_cascaded_table(n).to_organization_id, organization_id)
1833 AND revision = x_cascaded_table(n).item_revision;
1834
1835 IF (x_inventory_item = 0) THEN
1836 rcv_error_pkg.set_error_message('PO_RI_INVALID_ITEM_REVISION');
1837 RAISE e_validation_error;
1838 END IF;
1839
1840 EXCEPTION
1841 WHEN e_validation_error THEN --Bug 6055435
1842 x_cascaded_table(n).error_status := x_error_status;
1843 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
1844
1845 IF x_cascaded_table(n).error_message = 'PO_RI_INVALID_ITEM_REVISION' THEN
1846 NULL;
1847 END IF;
1848 when others then
1849 IF (g_asn_debug = 'Y') THEN
1850 asn_debug.put_line('exception in valid_item_revision');
1851 asn_debug.put_line(SQLERRM);
1852 END IF;
1853 x_cascaded_table(n).error_status := 'E';
1854 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
1855
1856 END validate_item_revision;
1857
1858 /* lcm changes */
1859 PROCEDURE validate_lcm_info(p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type)
1860 IS
1861 l_lcm_org_flag VARCHAR2(1);
1862 l_pre_rcv_flag VARCHAR2(1);
1863 BEGIN
1864 IF (g_asn_debug = 'Y') THEN
1865 asn_debug.put_line('In Validate LCM Info');
1866 asn_debug.put_line('p_header_record.error_record.error_status ' || p_header_record.error_record.error_status);
1867 asn_debug.put_line('p_header_record.header_record.asn_type ' || p_header_record.header_record.asn_type);
1868 END IF;
1869
1870 IF (p_header_record.error_record.error_status NOT IN('S', 'W')) THEN
1871 RETURN;
1872 END IF;
1873
1874 IF (nvl(p_header_record.header_record.asn_type,'STD') = 'LCM') THEN
1875
1876 l_lcm_org_flag := rcv_table_functions.is_lcm_org(p_header_record.header_record.ship_to_organization_id);
1877 l_pre_rcv_flag := rcv_table_functions.is_pre_rcv_org(p_header_record.header_record.ship_to_organization_id);
1878
1879 IF (g_asn_debug = 'Y') THEN
1880 asn_debug.put_line('p_header_record.header_record.ship_to_organization_id ' || p_header_record.header_record.ship_to_organization_id);
1881 asn_debug.put_line('l_lcm_org_flag => ' || l_lcm_org_flag);
1882 asn_debug.put_line('l_pre_rcv_flag => ' || l_pre_rcv_flag);
1883 END IF;
1884
1885 IF (l_lcm_org_flag = 'Y') THEN
1886 IF ( l_pre_rcv_flag = 'N') THEN
1887 --
1888 /* LCM import is not supported in blackbox scenario */
1889 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1890 rcv_error_pkg.set_error_message('RCV_LCM_IMPORT_NOT_ALLOWED', p_header_record.error_record.error_message);
1891 rcv_error_pkg.set_token('ORG_ID', p_header_record.header_record.ship_to_organization_id);
1892 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'ASN_TYPE');
1893 --
1894 ELSE
1895 --
1896 IF (g_asn_debug = 'Y') THEN
1897 asn_debug.put_line('p_header_record.header_record.transaction_type ' || p_header_record.header_record.transaction_type , NULL, 11);
1898 END IF;
1899 IF (p_header_record.header_record.transaction_type <> 'NEW') THEN
1900 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1901 rcv_error_pkg.set_error_message('RCV_INVALID_ROI_VALUE_NE', p_header_record.error_record.error_message);
1902 rcv_error_pkg.set_token('COLUMN', 'TRANSACTION_TYPE');
1903 rcv_error_pkg.set_token('ROI_VALUE', p_header_record.header_record.transaction_type);
1904 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'TRANSACTION_TYPE');
1905 END IF;
1906 --
1907 END IF;
1908 ELSE
1909 /* LCM import is not supported in a non-lcm org */
1910 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1911 rcv_error_pkg.set_error_message('RCV_LCM_IMPORT_NOT_ALLOWED', p_header_record.error_record.error_message);
1912 rcv_error_pkg.set_token('ORG_ID', p_header_record.header_record.ship_to_organization_id);
1913 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'ASN_TYPE');
1914 END IF;
1915 END IF;
1916
1917 IF (g_asn_debug = 'Y') THEN
1918 asn_debug.put_line('p_header_record.error_record.error_status' || p_header_record.error_record.error_status);
1919 asn_debug.put_line('Exitting validate_lcm_info');
1920 END IF;
1921
1922 EXCEPTION
1923 WHEN rcv_error_pkg.e_fatal_error THEN
1924 NULL;
1925 END validate_lcm_info;
1926
1927 END rcv_roi_header_common;
[Home] [Help]