1 PACKAGE BODY rcv_transactions_interface_sv1 AS
2 /* $Header: RCVTIS2B.pls 120.0 2005/06/01 16:48:29 appldev noship $*/
3
4 -- Read the profile option that enables/disables the debug log
5 g_asn_debug VARCHAR2(1) := NVL(fnd_profile.VALUE('RCV_DEBUG_MODE'), 'N');
6 e_validation_error EXCEPTION;
7
8 /*===========================================================================
9
10 PROCEDURE NAME: validate_quantity_shipped()
11
12 ===========================================================================*/
13 PROCEDURE validate_quantity_shipped(
14 x_quantity_shipped_record IN OUT NOCOPY rcv_shipment_line_sv.quantity_shipped_record_type
15 ) IS
16 x_progress VARCHAR2(3);
17 x_available_qty NUMBER := 0;
18 x_tolerable_qty NUMBER := 0;
19 /* Bug# 1548597 */
20 x_secondary_available_qty NUMBER := 0;
21 x_error_status VARCHAR2(1);
22 BEGIN
23 x_error_status := rcv_error_pkg.g_ret_sts_error;
24
25 IF (x_quantity_shipped_record.quantity_shipped IS NULL) THEN
26 rcv_error_pkg.set_error_message('RCV_ITEM_NO_SHIP_QTY');
27 RAISE e_validation_error;
28 END IF;
29
30 IF (x_quantity_shipped_record.quantity_shipped < 0) THEN
31 rcv_error_pkg.set_error_message('RCV_ITEM_SUBZERO_SHIP_QTY');
32 RAISE e_validation_error;
33 END IF;
34
35 -- get the tolerable quantity first
36 /* Bug# 1548597 */
37 rcv_quantities_s.get_available_quantity('RECEIVE',
38 x_quantity_shipped_record.po_line_location_id,
39 'VENDOR',
40 NULL,
41 NULL,
42 NULL,
43 x_available_qty,
44 x_tolerable_qty,
45 x_quantity_shipped_record.unit_of_measure,
46 x_secondary_available_qty
47 );
48
49 IF (x_quantity_shipped_record.quantity_shipped > x_tolerable_qty) THEN
50 x_error_status := rcv_error_pkg.g_ret_sts_warning;
51 rcv_error_pkg.set_error_message('RCV_ALL_QTY_OVER_TOLERANCE');
52 RAISE e_validation_error;
53 END IF;
54 EXCEPTION
55 WHEN e_validation_error THEN
56 x_quantity_shipped_record.error_record.error_status := x_error_status;
57 x_quantity_shipped_record.error_record.error_message := rcv_error_pkg.get_last_message;
58
59 IF x_quantity_shipped_record.error_record.error_message = 'RCV_ITEM_NO_SHIP_QTY' THEN
60 rcv_error_pkg.set_token('ITEM', x_quantity_shipped_record.item_id);
61 ELSIF x_quantity_shipped_record.error_record.error_message = 'RCV_ITEM_SUBZERO_SHIP_QTY' THEN
62 rcv_error_pkg.set_token('QTY_SHIPPED', x_quantity_shipped_record.quantity_shipped);
63 ELSIF x_quantity_shipped_record.error_record.error_message = 'RCV_ALL_QTY_OVER_TOLERANCE' THEN
64 rcv_error_pkg.set_token('QTY_A', x_quantity_shipped_record.quantity_shipped);
65 rcv_error_pkg.set_token('QTY_B', x_tolerable_qty);
66 END IF;
67 END validate_quantity_shipped;
68
69 /*===========================================================================
70
71 PROCEDURE NAME: validate_expected_receipt_date()
72
73 ===========================================================================*/
74 PROCEDURE validate_expected_receipt_date(
75 x_expected_receipt_rec IN OUT NOCOPY rcv_shipment_line_sv.expected_receipt_record_type
76 ) IS
77 x_progress VARCHAR2(3);
78 x_error_status VARCHAR2(1);
79 BEGIN
80 x_error_status := rcv_error_pkg.g_ret_sts_error;
81
82 -- in RCVTXDAB.pls
83 IF NOT(rcv_dates_s.val_receipt_date_tolerance(x_expected_receipt_rec.line_location_id, x_expected_receipt_rec.expected_receipt_date)) THEN
84 x_error_status := rcv_error_pkg.g_ret_sts_warning;
85 rcv_error_pkg.set_error_message('RCV_ASN_DATE_OUT_TOL');
86 RAISE e_validation_error;
87 END IF;
88 EXCEPTION
89 WHEN e_validation_error THEN
90 x_expected_receipt_rec.error_record.error_status := x_error_status;
91 x_expected_receipt_rec.error_record.error_message := rcv_error_pkg.get_last_message;
92
93 IF x_expected_receipt_rec.error_record.error_message = 'RCV_ASN_DATE_OUT_TOL' THEN
94 rcv_error_pkg.set_token('DELIVERY DATE', x_expected_receipt_rec.expected_receipt_date);
95 END IF;
96 END validate_expected_receipt_date;
97
98 /*===========================================================================
99
100 PROCEDURE NAME: validate_quantity_invoiced (ASBN only)
101
102 ===========================================================================*/
103 PROCEDURE validate_quantity_invoiced(
104 x_quantity_invoiced_record IN OUT NOCOPY rcv_shipment_line_sv.quantity_invoiced_record_type
105 ) IS
106 x_progress VARCHAR2(3);
107 x_error_status VARCHAR2(1);
108 BEGIN
109 x_error_status := rcv_error_pkg.g_ret_sts_error;
110
111 IF (x_quantity_invoiced_record.quantity_invoiced < 0) THEN
112 rcv_error_pkg.set_error_message('RCV_ITEM_INVOICE_QTY');
113 RAISE e_validation_error;
114 END IF;
115 EXCEPTION
116 WHEN e_validation_error THEN
117 x_quantity_invoiced_record.error_record.error_status := x_error_status;
118 x_quantity_invoiced_record.error_record.error_message := rcv_error_pkg.get_last_message;
119
120 IF x_quantity_invoiced_record.error_record.error_message = 'RCV_ITEM_INVOICE_QTY' THEN
121 NULL;
122 END IF;
123 END validate_quantity_invoiced;
124
125 /*===========================================================================
126
127 PROCEDURE NAME: validate_uom()
128
129 ===========================================================================*/
130 PROCEDURE validate_uom(
131 x_uom_record IN OUT NOCOPY rcv_shipment_line_sv.quantity_shipped_record_type
132 ) IS
133 x_unit_of_measure rcv_transactions_interface.unit_of_measure%TYPE := NULL;
134 x_unit_meas_lookup_code_lines po_lines.unit_meas_lookup_code%TYPE := NULL;
135 x_progress VARCHAR2(3);
136 x_new_conversion NUMBER := 0;
137 x_cum_enabled chv_org_options.enable_cum_flag%TYPE := NULL;
138 x_supply_agreement_flag po_headers.supply_agreement_flag%TYPE := NULL;
139 -- x_asl_uom chv_cum_period_items.purchasing_unit_of_measure%type := null;
140 x_asl_uom VARCHAR2(80) := NULL;
141 x_primary_unit_of_measure mtl_system_items.primary_unit_of_measure%TYPE := NULL;
142 x_error_status VARCHAR2(1);
143 BEGIN
144 -- check that the uom is valid
145 x_error_status := rcv_error_pkg.g_ret_sts_error;
146
147 SELECT NVL(MAX(unit_of_measure), 'notfound')
148 INTO x_unit_of_measure
149 FROM mtl_units_of_measure
150 WHERE unit_of_measure = x_uom_record.unit_of_measure;
151
152 IF (x_unit_of_measure = 'notfound') THEN
153 rcv_error_pkg.set_error_message('PO_PDOI_INVALID_UOM_CODE');
154 RAISE e_validation_error;
155 END IF;
156
157 -- check that system date is less than the disabled_date
158
159 IF (NOT(po_uom_s.val_unit_of_measure(x_uom_record.unit_of_measure))) THEN
160 rcv_error_pkg.set_error_message('PO_PDOI_INVALID_UOM_CODE');
161 RAISE e_validation_error;
162 END IF;
163
164 -- one-time purchase item
165
166 IF (x_uom_record.item_id IS NOT NULL) THEN
167 -- must have a primary uom at this point since the first select stmt succeeded
168
169 SELECT primary_unit_of_measure
170 INTO x_primary_unit_of_measure
171 FROM mtl_system_items_kfv
172 WHERE inventory_item_id = x_uom_record.item_id
173 AND organization_id = NVL(x_uom_record.to_organization_id, organization_id); -- Raj added as org_id is part of uk
174
175 IF (NVL(x_uom_record.primary_unit_of_measure, x_primary_unit_of_measure) <> x_primary_unit_of_measure) THEN
176 x_uom_record.error_record.error_status := 'W';
177 rcv_error_pkg.set_error_message('RCV_UOM_NOT_PRIMARY');
178 RAISE e_validation_error;
179 END IF;
180
181 x_new_conversion := 0;
182
183 IF (g_asn_debug = 'Y') THEN
184 asn_debug.put_line(TO_CHAR(x_uom_record.quantity_shipped));
185 asn_debug.put_line(x_uom_record.unit_of_measure);
186 asn_debug.put_line(TO_CHAR(x_uom_record.item_id));
187 asn_debug.put_line(x_primary_unit_of_measure);
188 asn_debug.put_line(x_uom_record.primary_unit_of_measure);
189 END IF;
190
191 po_uom_s.uom_convert(x_uom_record.quantity_shipped,
192 x_uom_record.unit_of_measure,
193 x_uom_record.item_id,
194 x_primary_unit_of_measure,
195 x_new_conversion
196 );
197
198 IF (x_new_conversion = 0) THEN
199 rcv_error_pkg.set_error_message('RCV_UOM_NO_CONV_PRIMARY');
200 RAISE e_validation_error;
201 ELSIF(x_new_conversion <> x_uom_record.primary_quantity) THEN
202 rcv_error_pkg.set_error_message('RCV_QTY_NOT_PRIMARY');
203 RAISE e_validation_error;
204 END IF;
205 END IF;
206
207 SELECT NVL(MAX(unit_meas_lookup_code), 'notfound')
208 INTO x_unit_meas_lookup_code_lines
209 FROM po_lines
210 WHERE po_line_id = x_uom_record.po_line_id;
211
212 IF (x_unit_meas_lookup_code_lines <> 'notfound')
213 AND (x_unit_meas_lookup_code_lines <> x_uom_record.unit_of_measure) THEN
214 x_new_conversion := 0;
215 po_uom_s.uom_convert(x_uom_record.quantity_shipped,
216 x_uom_record.unit_of_measure,
217 x_uom_record.item_id,
218 x_unit_meas_lookup_code_lines,
219 x_new_conversion
220 );
221
222 IF (x_new_conversion = 0) THEN
223 rcv_error_pkg.set_error_message('RCV_UOM_NO_CONV_PO');
224 RAISE e_validation_error;
225 END IF;
226 END IF;
227
228 SELECT NVL(MAX(enable_cum_flag), 'F')
229 INTO x_cum_enabled
230 FROM chv_org_options
231 WHERE organization_id = NVL(x_uom_record.to_organization_id, organization_id);
232
233 SELECT NVL(MAX(supply_agreement_flag), 'N')
234 INTO x_supply_agreement_flag
235 FROM po_headers
236 WHERE po_header_id = x_uom_record.po_header_id
237 AND type_lookup_code = 'BLANKET'
238 AND supply_agreement_flag = 'Y';
239
240 IF ( x_cum_enabled = 'Y'
241 AND x_supply_agreement_flag = 'Y') THEN
242 SELECT NVL(MAX(NULL), 'notfound') -- purchasing_unit_of_measure doesn't exist!!
243 INTO x_asl_uom
244 FROM chv_cum_period_items
245 WHERE organization_id = NVL(x_uom_record.to_organization_id, organization_id);
246
247 IF (x_asl_uom <> 'notfound') THEN
248 x_new_conversion := 0;
249 po_uom_s.uom_convert(x_uom_record.quantity_shipped,
250 x_uom_record.unit_of_measure,
251 x_uom_record.item_id,
252 x_asl_uom,
253 x_new_conversion
254 );
255
256 IF (x_new_conversion = 0) THEN
257 rcv_error_pkg.set_error_message('RCV_UOM_NO_CONV_ASL');
258 RAISE e_validation_error;
259 END IF;
260 END IF;
261 END IF;
262 EXCEPTION
263 WHEN e_validation_error THEN
264 x_uom_record.error_record.error_status := x_error_status;
265 x_uom_record.error_record.error_message := rcv_error_pkg.get_last_message;
266
267 IF x_uom_record.error_record.error_message = 'PO_PDOI_INVALID_UOM_CODE' THEN
268 rcv_error_pkg.set_token('VALUE', x_uom_record.unit_of_measure);
269 ELSIF x_uom_record.error_record.error_message = 'RCV_UOM_NOT_PRIMARY' THEN
270 NULL;
271 ELSIF x_uom_record.error_record.error_message = 'RCV_UOM_NO_CONV_PRIMARY' THEN
272 rcv_error_pkg.set_token('SHIPMENT_UNIT', x_uom_record.unit_of_measure);
273 rcv_error_pkg.set_token('PRIMARY_UNIT', x_primary_unit_of_measure);
274 ELSIF x_uom_record.error_record.error_message = 'RCV_QTY_NOT_PRIMARY' THEN
275 NULL;
276 ELSIF x_uom_record.error_record.error_message = 'RCV_UOM_NO_CONV_PO' THEN
277 rcv_error_pkg.set_token('SHIPMENT_UNIT', x_uom_record.unit_of_measure);
278 rcv_error_pkg.set_token('PO_UNIT', x_unit_meas_lookup_code_lines);
279 ELSIF x_uom_record.error_record.error_message = 'RCV_UOM_NO_CONV_ASL' THEN
280 rcv_error_pkg.set_token('UNIT', x_uom_record.unit_of_measure);
281 END IF;
282 END validate_uom;
283
284 /*===========================================================================
285
286 PROCEDURE NAME: validate_item()
287
288 ===========================================================================*/
289 PROCEDURE validate_item(
290 x_item_id_record IN OUT NOCOPY rcv_shipment_line_sv.item_id_record_type,
291 x_auto_transact_code IN rcv_transactions_interface.auto_transact_code%TYPE
292 ) IS -- bug 608353
293 x_progress VARCHAR2(3);
294 x_inventory_item mtl_system_items.inventory_item_id%TYPE := NULL;
295 x_organization_id mtl_system_items.organization_id%TYPE := NULL;
296 x_item_id_po po_lines.item_id%TYPE := NULL;
297 x_error_status VARCHAR2(1);
298 BEGIN
299 x_error_status := rcv_error_pkg.g_ret_sts_error;
300
301 SELECT NVL(MAX(inventory_item_id), -9999)
302 INTO x_inventory_item
303 FROM mtl_system_items
304 WHERE inventory_item_id = x_item_id_record.item_id;
305
306 IF (x_inventory_item = -9999) THEN
307 rcv_error_pkg.set_error_message('RCV_ITEM_ID');
308 RAISE e_validation_error;
309 END IF;
310
311 SELECT NVL(MAX(inventory_item_id), -9999)
312 INTO x_inventory_item
313 FROM mtl_system_items
314 WHERE SYSDATE BETWEEN NVL(start_date_active, SYSDATE - 1) AND NVL(end_date_active, SYSDATE + 1)
315 AND inventory_item_id = x_item_id_record.item_id;
316
317 IF (x_inventory_item = -9999) THEN
318 rcv_error_pkg.set_error_message('RCV_ITEM_NOT_ACTIVE');
319 RAISE e_validation_error;
320 END IF;
321
322 /* Bug 2160314.
323 * We used to have nvl(max(organization_id),0) here before. But if the
324 * organization_id is itself 0, then this will give us a problem in
325 * the next step when we check if x_organization_id = 0. So changed
326 * the statement to nvl(max(organization_id),-9999) and also the
327 * check below. Similarly changed the select statement and the
328 * check for nvl(max(item_id),0).
329 */
330 SELECT NVL(MAX(organization_id), -9999)
331 INTO x_organization_id
332 FROM mtl_system_items
333 WHERE inventory_item_id = x_item_id_record.item_id
334 AND organization_id = NVL(x_item_id_record.to_organization_id, organization_id);
335
336 IF (x_organization_id = -9999) THEN
337 rcv_error_pkg.set_error_message('RCV_ITEM_NOT_IN_ORG');
338 RAISE e_validation_error;
339 END IF;
340
341 SELECT NVL(MAX(item_id), -9999)
342 INTO x_item_id_po
343 FROM po_lines
344 WHERE po_line_id = x_item_id_record.po_line_id
345 AND item_id = x_item_id_record.item_id;
346
347 IF (x_item_id_po = -9999) THEN
348 rcv_error_pkg.set_error_message('RCV_ITEM_NOT_ON_PO');
349 RAISE e_validation_error;
350 END IF;
351
352 /* Bug 2898324 The non-purchasable items were allowed to be
353 received thru ROI. The validation on purchasable flag
354 is not based on the receving org. Added a filter condition
355 based on organization id.
356 */
357
358 /* Fix for bug 2989299.
359 Commenting the following sql as we should not validate an item
360 based on it's purchasing flags at the time of receipt creation.
361 Only at the time of creating the Purchase Order this flag has
362 to be checked upon. Please see bug 2706571 for more details.
363 For the time being we are not checking on item's stockable flag
364 thru ROI. If required we will incorporate later.
365 */
366 SELECT NVL(MAX(item_id), -9999)
367 INTO x_item_id_po
368 FROM po_lines
369 WHERE po_line_id = x_item_id_record.po_line_id
370 AND item_id = x_item_id_record.item_id;
371
372 IF (x_item_id_po <> x_item_id_record.item_id) THEN
373 rcv_error_pkg.set_error_message('RCV_NOT_PO_LINE_NUM');
374 RAISE e_validation_error;
375 END IF;
376
377 IF (g_asn_debug = 'Y') THEN
378 asn_debug.put_line('Validating Item: ' || x_auto_transact_code);
379 asn_debug.put_line('Validating Item: ' || x_item_id_record.use_mtl_lot);
380 asn_debug.put_line('Validating Item: ' || x_item_id_record.use_mtl_serial);
381 END IF;
382 EXCEPTION
383 WHEN e_validation_error THEN
384 x_item_id_record.error_record.error_status := x_error_status;
385 x_item_id_record.error_record.error_message := rcv_error_pkg.get_last_message;
386
387 IF x_item_id_record.error_record.error_message = 'RCV_ITEM_ID' THEN
388 rcv_error_pkg.set_token('ITEM', x_item_id_record.item_id);
389 ELSIF x_item_id_record.error_record.error_message = 'RCV_ITEM_NOT_ACTIVE' THEN
390 rcv_error_pkg.set_token('ITEM', x_item_id_record.item_id);
391 ELSIF x_item_id_record.error_record.error_message = 'RCV_UOM_NO_CONV_PRIMARY' THEN
392 rcv_error_pkg.set_token('ITEM', x_item_id_record.item_id);
393 rcv_error_pkg.set_token('ORGANIZATION', x_item_id_record.to_organization_id);
394 ELSIF x_item_id_record.error_record.error_message = 'RCV_ITEM_NOT_ON_PO' THEN
395 rcv_error_pkg.set_token('ITEM', x_item_id_record.item_id);
396 rcv_error_pkg.set_token('PO_NUMBER', x_item_id_record.po_line_id);
397 ELSIF x_item_id_record.error_record.error_message = 'RCV_NOT_PO_LINE_NUM' THEN
398 rcv_error_pkg.set_token('PO_ITEM', x_item_id_po);
399 rcv_error_pkg.set_token('SHIPMENT_ITEM', x_item_id_record.item_id);
400 END IF;
401 END validate_item;
402
403 /*===========================================================================
404
405 PROCEDURE NAME: validate_item_description()
406
407 ===========================================================================*/
408 PROCEDURE validate_item_description(
409 x_item_id_record IN OUT NOCOPY rcv_shipment_line_sv.item_id_record_type
410 ) IS
411 x_progress VARCHAR2(3);
412 x_error_status VARCHAR2(1);
413 BEGIN
414 x_error_status := rcv_error_pkg.g_ret_sts_error;
415
416 IF (x_item_id_record.item_description IS NULL) THEN
417 rcv_error_pkg.set_error_message('RCV_ITEM_NO_DESCRIPTION');
418 RAISE e_validation_error;
419 END IF;
420 EXCEPTION
421 WHEN e_validation_error THEN
422 x_item_id_record.error_record.error_status := x_error_status;
423 x_item_id_record.error_record.error_message := rcv_error_pkg.get_last_message;
424
425 IF x_item_id_record.error_record.error_message = 'RCV_ITEM_NO_DESCRIPTION' THEN
426 rcv_error_pkg.set_token('ITEM', x_item_id_record.item_id);
427 END IF;
428 END validate_item_description;
429
430 /*===========================================================================
431
432 PROCEDURE NAME: validate_substitute_item()
433
434 ===========================================================================*/
435 PROCEDURE validate_substitute_item(
436 x_sub_item_id_record IN OUT NOCOPY rcv_shipment_line_sv.sub_item_id_record_type
437 ) IS
438 x_inventory_item mtl_system_items.inventory_item_id%TYPE := NULL;
439 x_progress VARCHAR2(3);
440 x_vendor_id po_vendors.vendor_id%TYPE := NULL;
441 x_error_status VARCHAR2(1);
442 BEGIN
443 x_error_status := rcv_error_pkg.g_ret_sts_error;
444
445 SELECT NVL(MAX(inventory_item_id), 0)
446 INTO x_inventory_item
447 FROM mtl_system_items
448 WHERE inventory_item_id = x_sub_item_id_record.substitute_item_id
449 AND organization_id = NVL(x_sub_item_id_record.to_organization_id, organization_id);
450
451 IF (x_inventory_item = 0) THEN
452 rcv_error_pkg.set_error_message('RCV_ITEM_SUB_ID');
453 RAISE e_validation_error;
454 END IF;
455
456 SELECT NVL(MAX(inventory_item_id), 0)
457 INTO x_inventory_item
458 FROM mtl_system_items
459 WHERE SYSDATE BETWEEN NVL(start_date_active, SYSDATE - 1) AND NVL(end_date_active, SYSDATE + 1)
460 AND inventory_item_id = x_sub_item_id_record.substitute_item_id
461 AND organization_id = NVL(x_sub_item_id_record.to_organization_id, organization_id);
462
463 IF (x_inventory_item = 0) THEN
464 rcv_error_pkg.set_error_message('RCV_ITEM_SUB_NOT_ACTIVE');
465 RAISE e_validation_error;
466 END IF;
467
468 /* Bug 3009663- Commented the check on allow_substitute_receipts_flag at the item level.
469 only the value at PO shipments need to be checked instead of the values at the item
470 level or the supplier level. */
471 SELECT NVL(MAX(inventory_item_id), 0)
472 INTO x_inventory_item
473 FROM mtl_system_items
474 WHERE inventory_item_id = x_sub_item_id_record.substitute_item_id
475 AND organization_id = NVL(x_sub_item_id_record.to_organization_id, organization_id);
476
477 IF (x_inventory_item = 0) THEN
478 rcv_error_pkg.set_error_message('RCV_ITEM_SUB_NOT_IN_ORG');
479 RAISE e_validation_error;
480 END IF;
481
482 SELECT NVL(MAX(inventory_item_id), 0)
483 INTO x_inventory_item
484 FROM mtl_system_items
485 WHERE inventory_item_id = x_sub_item_id_record.substitute_item_id
486 AND organization_id = NVL(x_sub_item_id_record.to_organization_id, organization_id)
487 AND purchasing_item_flag = 'Y';
488
489 IF (x_inventory_item = 0) THEN
490 rcv_error_pkg.set_error_message('RCV_ITEM_SUB_NOT_PO_ENABLED');
491 RAISE e_validation_error;
492 END IF;
493
494 SELECT NVL(MAX(inventory_item_id), 0)
495 INTO x_inventory_item
496 FROM mtl_system_items
497 WHERE inventory_item_id = x_sub_item_id_record.substitute_item_id
498 AND organization_id = NVL(x_sub_item_id_record.to_organization_id, organization_id)
499 AND purchasing_enabled_flag = 'Y';
500
501 IF (x_inventory_item = 0) THEN
502 rcv_error_pkg.set_error_message('RCV_ITEM_SUB_NOT_PO_ENABLED');
503 RAISE e_validation_error;
504 END IF;
505
506 /* Bug 3009663- Commented the check on the allow_substitute_receipt flag at the supplier level */
507
508 -- Need to check for related items if reciprocal_flag is set
509 -- Thus need to use union as user may not have set up both
510 -- the items to substitute for each other and just used
511 -- reciprocal_flag for this
512 -- relationship_type_id = 2 for substitute items
513 -- = 1 for related items
514
515 SELECT NVL(MAX(inventory_item_id), 0)
516 INTO x_inventory_item
517 FROM mtl_related_items
518 WHERE inventory_item_id = (SELECT item_id
519 FROM po_lines
520 WHERE po_line_id = x_sub_item_id_record.po_line_id)
521 AND related_item_id = x_sub_item_id_record.substitute_item_id
522 AND relationship_type_id = 2; -- substitute items
523 -- and organization_id = nvl(x_sub_item_id_record.to_organization_id,organization_id)
524
525 IF x_inventory_item = 0 THEN
526 -- Try the reciprocal relationship
527
528 SELECT NVL(MAX(inventory_item_id), 0)
529 INTO x_inventory_item
530 FROM mtl_related_items
531 WHERE related_item_id = (SELECT item_id
532 FROM po_lines
533 WHERE po_line_id = x_sub_item_id_record.po_line_id)
534 AND inventory_item_id = x_sub_item_id_record.substitute_item_id
535 AND reciprocal_flag = 'Y'
536 AND relationship_type_id = 2;
537 -- and organization_id = nvl(x_sub_item_id_record.to_organization_id,organization_id)
538
539 END IF;
540
541 IF (x_inventory_item = 0) THEN
542 rcv_error_pkg.set_error_message('RCV_ITEM_SUB_NOT_RELATED');
543 RAISE e_validation_error;
544 END IF;
545 EXCEPTION
546 WHEN e_validation_error THEN
547 x_sub_item_id_record.error_record.error_status := x_error_status;
548 x_sub_item_id_record.error_record.error_message := rcv_error_pkg.get_last_message;
549
550 IF x_sub_item_id_record.error_record.error_message = 'RCV_ITEM_SUB_ID' THEN
551 rcv_error_pkg.set_token('ITEM', x_sub_item_id_record.substitute_item_id);
552 ELSIF x_sub_item_id_record.error_record.error_message = 'RCV_ITEM_SUB_NOT_ACTIVE' THEN
553 rcv_error_pkg.set_token('ITEM', x_sub_item_id_record.substitute_item_id);
554 ELSIF x_sub_item_id_record.error_record.error_message = 'RCV_ITEM_SUB_NOT_ALLOWED' THEN
555 rcv_error_pkg.set_token('ITEM', x_sub_item_id_record.substitute_item_id);
556 ELSIF x_sub_item_id_record.error_record.error_message = 'RCV_ITEM_SUB_NOT_IN_ORG' THEN
557 rcv_error_pkg.set_token('ITEM', x_sub_item_id_record.substitute_item_id);
558 rcv_error_pkg.set_token('ORGANIZATION', x_sub_item_id_record.to_organization_id);
559 ELSIF x_sub_item_id_record.error_record.error_message = 'RCV_ITEM_SUB_VEN_NOT_ALLOWED' THEN
560 rcv_error_pkg.set_token('ITEM', x_sub_item_id_record.substitute_item_id);
561 rcv_error_pkg.set_token('SUPPLIER', x_sub_item_id_record.vendor_id);
562 ELSIF x_sub_item_id_record.error_record.error_message = 'RCV_ITEM_SUB_NOT_PO_ENABLED' THEN
563 rcv_error_pkg.set_token('ITEM', x_sub_item_id_record.substitute_item_id);
564 ELSIF x_sub_item_id_record.error_record.error_message = 'RCV_ITEM_SUB_NOT_RELATED' THEN
565 rcv_error_pkg.set_token('SUB_ITEM', x_sub_item_id_record.substitute_item_id);
566 rcv_error_pkg.set_token('ITEM', x_inventory_item);
567 END IF;
568 END validate_substitute_item;
569
570 /*===========================================================================
571
572 PROCEDURE NAME: validate_item_revision()
573
574 ===========================================================================*/
575 PROCEDURE validate_item_revision(
576 x_item_revision_record IN OUT NOCOPY rcv_shipment_line_sv.item_id_record_type
577 ) IS
578 x_inventory_item mtl_system_items.inventory_item_id%TYPE := NULL;
579 x_progress VARCHAR2(3);
580 x_revision_control_flag VARCHAR2(1);
581 x_error_status VARCHAR2(1);
582 BEGIN
583 x_error_status := rcv_error_pkg.g_ret_sts_error;
584
585 -- check whether the item is under revision control
586 -- If it is not then item should not have any revisions
587
588 SELECT DECODE(msi.revision_qty_control_code,
589 1, 'N',
590 2, 'Y',
591 'N'
592 )
593 INTO x_revision_control_flag
594 FROM mtl_system_items msi
595 WHERE inventory_item_id = x_item_revision_record.item_id
596 AND organization_id = x_item_revision_record.to_organization_id;
597
598 IF x_revision_control_flag = 'N' THEN
599 /* Bug 1913887 : Check if the item is Non-revision controlled
600 and the revision entered matches with the one in PO, then
601 return without any error, else return with error
602 */
603 SELECT NVL(MAX(po_line_id), 0)
604 INTO x_inventory_item
605 FROM po_lines
606 WHERE po_line_id = x_item_revision_record.po_line_id
607 AND NVL(item_revision, x_item_revision_record.item_revision) = x_item_revision_record.item_revision;
608
609 IF (x_inventory_item <> 0) THEN
610 RETURN;
611 END IF;
612
613 IF (g_asn_debug = 'Y') THEN
614 asn_debug.put_line('Item is not under revision control');
615 END IF;
616
617 rcv_error_pkg.set_error_message('RCV_ITEM_REV_NOT_ALLOWED');
618 RAISE e_validation_error;
619 END IF;
620
621 -- Check whether the revision number exists
622
623 IF (g_asn_debug = 'Y') THEN
624 asn_debug.put_line('Revision number : ' || x_item_revision_record.item_revision);
625 END IF;
626
627 SELECT NVL(MAX(inventory_item_id), 0)
628 INTO x_inventory_item
629 FROM mtl_item_revisions
630 WHERE inventory_item_id = x_item_revision_record.item_id
631 AND organization_id = NVL(x_item_revision_record.to_organization_id, organization_id)
632 AND revision = x_item_revision_record.item_revision;
633
634 IF (x_inventory_item = 0) THEN
635 rcv_error_pkg.set_error_message('PO_RI_INVALID_ITEM_REVISION');
636 RAISE e_validation_error;
637 END IF;
638
639 -- Check whether revision is still active
640
641 SELECT NVL(MAX(inventory_item_id), 0) -- does this accurately check for active revisions??
642 INTO x_inventory_item
643 FROM mtl_item_revisions_org_val_v mir
644 WHERE mir.inventory_item_id = x_item_revision_record.item_id
645 AND mir.organization_id = NVL(x_item_revision_record.to_organization_id, mir.organization_id)
646 AND mir.revision = x_item_revision_record.item_revision;
647
648 IF (x_inventory_item = 0) THEN
649 rcv_error_pkg.set_error_message('PO_RI_INVALID_ITEM_REVISION');
650 RAISE e_validation_error;
651 END IF;
652
653 -- Check whether po_revision matches this revision if po_revision is not null
654
655 SELECT NVL(MAX(po_line_id), 0)
656 INTO x_inventory_item
657 FROM po_lines
658 WHERE po_line_id = x_item_revision_record.po_line_id
659 AND NVL(item_revision, x_item_revision_record.item_revision) = x_item_revision_record.item_revision;
660
661 IF (x_inventory_item = 0) THEN
662 x_error_status := rcv_error_pkg.g_ret_sts_warning;
663 rcv_error_pkg.set_error_message('RCV_NOT_PO_REVISION');
664 RAISE e_validation_error;
665 END IF;
666 EXCEPTION
667 WHEN e_validation_error THEN
668 x_item_revision_record.error_record.error_status := x_error_status;
669 x_item_revision_record.error_record.error_message := rcv_error_pkg.get_last_message;
670
671 IF x_item_revision_record.error_record.error_message = 'RCV_ITEM_REV_NOT_ALLOWED' THEN
672 rcv_error_pkg.set_token('ITEM', x_item_revision_record.item_id);
673 ELSIF x_item_revision_record.error_record.error_message = 'PO_RI_INVALID_ITEM_REVISION' THEN
674 NULL;
675 ELSIF x_item_revision_record.error_record.error_message = 'RCV_NOT_PO_REVISION' THEN
676 rcv_error_pkg.set_token('PO_REV', '');
677 rcv_error_pkg.set_token('SHIPMENT_REV', x_item_revision_record.item_revision);
678 END IF;
679 END validate_item_revision;
680
681 /*===========================================================================
682
683 PROCEDURE NAME: validate_ref_integ()
684
685 ===========================================================================*/
686 PROCEDURE validate_ref_integ(
687 x_ref_integrity_rec IN OUT NOCOPY rcv_shipment_line_sv.ref_integrity_record_type,
688 v_header_record IN rcv_shipment_header_sv.headerrectype
689 ) IS
690 x_po_vendor_id po_headers.vendor_id%TYPE := NULL;
691 x_po_line_id po_lines.po_line_id%TYPE;
692 x_po_vendor_site_id po_headers.vendor_site_id%TYPE := NULL;
693 x_progress VARCHAR2(3);
694 x_error_status VARCHAR2(1);
695 BEGIN
696 x_error_status := rcv_error_pkg.g_ret_sts_error;
697
698 IF (x_ref_integrity_rec.vendor_item_num IS NOT NULL) THEN
699 SELECT NVL(MAX(po_line_id), 0)
700 INTO x_po_line_id
701 FROM po_lines
702 WHERE po_line_id = x_ref_integrity_rec.po_line_id
703 AND vendor_product_num = x_ref_integrity_rec.vendor_item_num;
704
705 IF (x_po_line_id = 0) THEN
706 rcv_error_pkg.set_error_message('RCV_NOT_PO_VEN_ITEM');
707 RAISE e_validation_error;
708 END IF;
709 END IF;
710
711 IF (g_asn_debug = 'Y') THEN
712 asn_debug.put_line('Validating vendor id in PO ' || TO_CHAR(x_ref_integrity_rec.vendor_id));
713 asn_debug.put_line('PO Header Id ' || TO_CHAR(x_ref_integrity_rec.po_header_id));
714 END IF;
715
716 IF x_ref_integrity_rec.vendor_id IS NOT NULL THEN
717 SELECT NVL(MAX(vendor_id), 0)
718 INTO x_po_vendor_id
719 FROM po_headers
720 WHERE po_header_id = x_ref_integrity_rec.po_header_id
721 AND vendor_id = x_ref_integrity_rec.vendor_id;
722
723 IF (x_po_vendor_id = 0) THEN
724 rcv_error_pkg.set_error_message('RCV_NOT_PO_VEN');
725 RAISE e_validation_error;
726 END IF;
727 END IF;
728
729 -- Check for header.vendor = lines.vendor
730
731 IF v_header_record.header_record.vendor_id IS NOT NULL THEN
732 IF v_header_record.header_record.vendor_id <> NVL(x_ref_integrity_rec.vendor_id, v_header_record.header_record.vendor_id) THEN
733 rcv_error_pkg.set_error_message('RCV_ERC_MISMATCH_PO_VENDOR');
734 RAISE e_validation_error;
735 END IF;
736 END IF;
737
738 IF x_ref_integrity_rec.vendor_site_id IS NOT NULL THEN
739 SELECT NVL(MAX(vendor_site_id), 0)
740 INTO x_po_vendor_site_id
741 FROM po_headers
742 WHERE po_header_id = x_ref_integrity_rec.po_header_id
743 AND vendor_site_id = x_ref_integrity_rec.vendor_site_id;
744
745 IF (x_po_vendor_site_id = 0) THEN
746 rcv_error_pkg.set_error_message('RCV_NOT_PO_VEN_SITE');
747 RAISE e_validation_error;
748 END IF;
749 END IF;
750
751 IF x_ref_integrity_rec.po_revision_num IS NOT NULL THEN
752 SELECT NVL(MAX(vendor_site_id), 0)
753 INTO x_po_vendor_site_id
754 FROM po_headers
755 WHERE po_header_id = x_ref_integrity_rec.po_header_id
756 AND revision_num = x_ref_integrity_rec.po_revision_num;
757
758 IF (x_po_vendor_site_id = 0) THEN
759 rcv_error_pkg.set_error_message('RCV_NOT_PO_REVISION');
760 RAISE e_validation_error;
761 END IF;
762 END IF;
763 EXCEPTION
764 WHEN e_validation_error THEN
765 x_ref_integrity_rec.error_record.error_status := x_error_status;
766 x_ref_integrity_rec.error_record.error_message := rcv_error_pkg.get_last_message;
767
768 IF x_ref_integrity_rec.error_record.error_message = 'RCV_NOT_PO_VEN_ITEM' THEN
769 rcv_error_pkg.set_token('PO_SUPPLIER_ITEM', '');
770 rcv_error_pkg.set_token('SHIPMENT_SUPPLIER_ITEM', x_ref_integrity_rec.vendor_item_num);
771 ELSIF x_ref_integrity_rec.error_record.error_message = 'RCV_NOT_PO_VEN' THEN
772 rcv_error_pkg.set_token('PO_SUPPLIER', x_po_vendor_id);
773 rcv_error_pkg.set_token('SHIPMENT_SUPPLIER', x_ref_integrity_rec.vendor_id);
774 ELSIF x_ref_integrity_rec.error_record.error_message = 'RCV_ERC_MISMATCH_PO_VENDOR' THEN
775 NULL;
776 ELSIF x_ref_integrity_rec.error_record.error_message = 'RCV_NOT_PO_REVISION' THEN
777 rcv_error_pkg.set_token('PO_REV', '');
778 rcv_error_pkg.set_token('SHIPMENT_REV', x_ref_integrity_rec.po_revision_num);
779 END IF;
780 END validate_ref_integ;
781
782 /*===========================================================================
783
784 PROCEDURE NAME: validate_freight_carrier()
785
786 ===========================================================================*/
787 PROCEDURE validate_freight_carrier(
788 x_freight_carrier_record IN OUT NOCOPY rcv_shipment_line_sv.freight_carrier_record_type
789 ) IS
790 x_freight_code org_freight_code_val_v.freight_code%TYPE;
791 x_progress VARCHAR2(3);
792 x_error_status VARCHAR2(1);
793 BEGIN
794 x_error_status := rcv_error_pkg.g_ret_sts_error;
795
796 SELECT NVL(MAX(freight_code), 'notfound')
797 INTO x_freight_code
798 FROM org_freight_code_val_v
799 WHERE freight_code = x_freight_carrier_record.freight_carrier_code
800 AND organization_id = NVL(x_freight_carrier_record.to_organization_id, organization_id);
801
802 IF (x_freight_code = 'notfound') THEN
803 rcv_error_pkg.set_error_message('RCV_CARRIER_DISABLED');
804 RAISE e_validation_error;
805 END IF;
806
807 SELECT NVL(MAX(freight_code), 'notfound')
808 INTO x_freight_code
809 FROM org_freight
810 WHERE organization_id = NVL(x_freight_carrier_record.to_organization_id, organization_id)
811 AND freight_code = x_freight_carrier_record.freight_carrier_code
812 AND NVL(disable_date, SYSDATE + 1) > SYSDATE;
813
814 IF (x_freight_code = 'notfound') THEN
815 rcv_error_pkg.set_error_message('RCV_CARRIER_DISABLED');
816 RAISE e_validation_error;
817 END IF;
818 EXCEPTION
819 WHEN e_validation_error THEN
820 x_freight_carrier_record.error_record.error_status := x_error_status;
821 x_freight_carrier_record.error_record.error_message := rcv_error_pkg.get_last_message;
822
823 IF x_freight_carrier_record.error_record.error_message = 'RCV_NOT_PO_VEN_ITEM' THEN
824 rcv_error_pkg.set_token('CARRIER', x_freight_carrier_record.freight_carrier_code);
825 END IF;
826 END validate_freight_carrier;
827
828 /*===========================================================================
829
830 PROCEDURE NAME: validate_tax_code() (ASBN only)
831
832 ===========================================================================*/
833 PROCEDURE validate_tax_code(
834 x_tax_name_record IN OUT NOCOPY rcv_shipment_line_sv.tax_name_record_type
835 ) IS
836 x_name ap_tax_codes.NAME%TYPE := NULL;
837 x_progress VARCHAR2(3);
838 x_error_status VARCHAR2(1);
839 BEGIN
840 x_error_status := rcv_error_pkg.g_ret_sts_error;
841
842 SELECT NVL(MAX(NAME), 'notfound')
843 INTO x_name
844 FROM ap_tax_codes
845 WHERE NAME = x_tax_name_record.tax_name;
846
847 IF (x_name = 'notfound') THEN
848 rcv_error_pkg.set_error_message('RCV_ASBN_ITEM_TAX_CODE_DISABLE');
849 RAISE e_validation_error;
850 END IF;
851
852 SELECT NVL(MAX(NAME), 'notfound')
853 INTO x_name
854 FROM ap_tax_codes
855 WHERE NAME = x_tax_name_record.tax_name
856 AND NVL(inactive_date, SYSDATE + 1) > SYSDATE;
857
858 IF (x_name = 'notfound') THEN
859 rcv_error_pkg.set_error_message('RCV_ASBN_ITEM_TAX_CODE_DISABLE');
860 RAISE e_validation_error;
861 END IF;
862 EXCEPTION
863 WHEN e_validation_error THEN
864 x_tax_name_record.error_record.error_status := x_error_status;
865 x_tax_name_record.error_record.error_message := rcv_error_pkg.get_last_message;
866
867 IF x_tax_name_record.error_record.error_message = 'RCV_ASBN_ITEM_TAX_CODE_DISABLE' THEN
868 rcv_error_pkg.set_token('TAX_CODE', x_tax_name_record.tax_name);
869 END IF;
870 WHEN OTHERS THEN
871 x_tax_name_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
872 rcv_error_pkg.set_sql_error_message('validate_tax_code', 000);
873 x_tax_name_record.error_record.error_status := rcv_error_pkg.get_last_message;
874 END validate_tax_code;
875
876 /*===========================================================================
877
878 PROCEDURE NAME: validate_asl()
879
880 ===========================================================================*/
881 PROCEDURE validate_asl(
882 x_asl_record IN OUT NOCOPY rcv_shipment_line_sv.ref_integrity_record_type
883 ) IS
884 x_supply_agreement_flag po_headers.supply_agreement_flag%TYPE := 'Y';
885 x_success VARCHAR2(10) := NULL;
886 x_progress VARCHAR2(3);
887 x_error_status VARCHAR2(1);
888 BEGIN
889 x_error_status := rcv_error_pkg.g_ret_sts_error;
890
891 -- first check if the po from the shipment is a supply agreement blanket purchase
892
893 IF (g_asn_debug = 'Y') THEN
894 asn_debug.put_line('In validate ASL');
895 END IF;
896
897 SELECT NVL(MAX(supply_agreement_flag), 'N')
898 INTO x_supply_agreement_flag
899 FROM po_headers
900 WHERE po_header_id = x_asl_record.po_header_id
901 AND type_lookup_code = 'BLANKET'
902 AND supply_agreement_flag = 'Y';
903
904 IF (g_asn_debug = 'Y') THEN
905 asn_debug.put_line('Supply agreement Flag ' || x_supply_agreement_flag);
906 END IF;
907
908 IF (x_supply_agreement_flag <> 'N') THEN
909 SELECT NVL(MAX('found'), 'notfound')
910 INTO x_success
911 FROM po_approved_supplier_lis_val_v
912 WHERE vendor_id = x_asl_record.vendor_id
913 AND vendor_site_id = x_asl_record.vendor_site_id
914 AND item_id = x_asl_record.item_id
915 AND ( using_organization_id = NVL(x_asl_record.to_organization_id, using_organization_id)
916 OR using_organization_id = -1); -- per discussion with cindy
917 END IF;
918
919 IF (g_asn_debug = 'Y') THEN
920 asn_debug.put_line('PO Approved supplier list ' || x_success);
921 END IF;
922
923 IF (x_success = 'notfound') THEN
924 rcv_error_pkg.set_error_message('RCV_ASL_NOT_FOUND');
925 RAISE e_validation_error;
926 END IF;
927 EXCEPTION
928 WHEN e_validation_error THEN
929 x_asl_record.error_record.error_status := x_error_status;
930 x_asl_record.error_record.error_message := rcv_error_pkg.get_last_message;
931
932 IF x_asl_record.error_record.error_message = 'RCV_ASL_NOT_FOUND' THEN
933 rcv_error_pkg.set_token('ITEM', x_asl_record.item_id);
934 END IF;
935 END validate_asl;
936
937 /*===========================================================================
938
939 PROCEDURE NAME: validate_cum_quantity_shipped()
940
941 ===========================================================================*/
942 PROCEDURE validate_cum_quantity_shipped(
943 x_cum_quantity_record IN OUT NOCOPY rcv_shipment_line_sv.cum_quantity_record_type
944 ) IS
945 x_supply_agreement_flag po_headers.supply_agreement_flag%TYPE := 'Y';
946 x_success VARCHAR2(1) := NULL;
947 x_progress VARCHAR2(3);
948 x_rtv_update_cum_flag chv_org_options.rtv_update_cum_flag%TYPE;
949 x_cum_period_start_date chv_cum_periods.cum_period_start_date%TYPE;
950 x_cum_period_end_date chv_cum_periods.cum_period_end_date%TYPE;
951 x_continue BOOLEAN := TRUE;
952 x_qty_received_primary NUMBER;
953 x_qty_received_purchasing NUMBER;
954 x_total_cum_shipped NUMBER;
955 x_new_conversion NUMBER := 0;
956 x_error_status VARCHAR2(1);
957 BEGIN
958 x_error_status := rcv_error_pkg.g_ret_sts_error;
959
960 -- first check if the po from the shipment is a supply agreement blanket purchase
961
962 IF (g_asn_debug = 'Y') THEN
963 asn_debug.put_line('Validating cum quantity ');
964 END IF;
965
966 SELECT NVL(MAX(supply_agreement_flag), 'N')
967 INTO x_supply_agreement_flag
968 FROM po_headers
969 WHERE po_header_id = x_cum_quantity_record.po_header_id
970 AND type_lookup_code = 'BLANKET'
971 AND supply_agreement_flag = 'Y';
972
973 IF (g_asn_debug = 'Y') THEN
974 asn_debug.put_line('Check for supply agreement flag ' || x_supply_agreement_flag);
975 END IF;
976
977 IF (x_supply_agreement_flag <> 'N') THEN
978 SELECT MAX(enable_cum_flag)
979 INTO x_success
980 FROM chv_org_options
981 WHERE organization_id = NVL(x_cum_quantity_record.to_organization_id, organization_id);
982
983 IF (g_asn_debug = 'Y') THEN
984 asn_debug.put_line('Enable cum flag ' || x_success);
985 END IF;
986
987 IF (x_success = 'Y') THEN
988 IF (x_cum_quantity_record.vendor_cum_shipped_qty < 0) THEN
989 rcv_error_pkg.set_error_message('RCV_ASL_NO_CUM_QTY');
990 RAISE e_validation_error;
991 END IF;
992 END IF;
993 END IF;
994
995 -- check that the cum quantity from the vendor matches our cum quantity
996 -- first get the extra params you need to call get_cum_qty_received
997
998 SELECT NVL(MAX(rtv_update_cum_flag), 'N')
999 INTO x_rtv_update_cum_flag
1000 FROM chv_org_options
1001 WHERE organization_id = x_cum_quantity_record.to_organization_id;
1002
1003 IF (g_asn_debug = 'Y') THEN
1004 asn_debug.put_line('RTV update cum flag ' || x_rtv_update_cum_flag);
1005 END IF;
1006
1007 IF (x_rtv_update_cum_flag = 'Y') THEN
1008 BEGIN
1009 IF (g_asn_debug = 'Y') THEN
1010 asn_debug.put_line('Org Id ' || TO_CHAR(x_cum_quantity_record.to_organization_id));
1011 asn_debug.put_line('Transaction date ' || TO_CHAR(x_cum_quantity_record.transaction_date, 'DDMONYY'));
1012 END IF;
1013
1014 SELECT cum_period_start_date,
1015 cum_period_end_date
1016 INTO x_cum_period_start_date,
1017 x_cum_period_end_date
1018 FROM chv_cum_periods
1019 WHERE organization_id = x_cum_quantity_record.to_organization_id
1020 AND x_cum_quantity_record.transaction_date BETWEEN cum_period_start_date AND cum_period_end_date;
1021
1022 IF (g_asn_debug = 'Y') THEN
1023 asn_debug.put_line('Cum period start date ' || TO_CHAR(x_cum_period_start_date, 'DDMONYY'));
1024 asn_debug.put_line('Cum Period End date ' || TO_CHAR(x_cum_period_end_date, 'DDMONYY'));
1025 END IF;
1026 EXCEPTION
1027 WHEN NO_DATA_FOUND THEN
1028 x_continue := FALSE;
1029 WHEN OTHERS THEN
1030 RAISE;
1031 END;
1032 END IF;
1033
1034 -- what if item_id is null ????
1035
1036 IF (x_cum_quantity_record.item_id IS NOT NULL) THEN
1037 IF (x_continue) THEN
1038 IF (g_asn_debug = 'Y') THEN
1039 asn_debug.put_line('Call to chv_cum_periods_s1.get_cum_qty_received');
1040 END IF;
1041
1042 chv_cum_periods_s1.get_cum_qty_received(x_cum_quantity_record.vendor_id,
1043 x_cum_quantity_record.vendor_site_id,
1044 x_cum_quantity_record.item_id,
1045 x_cum_quantity_record.to_organization_id,
1046 x_rtv_update_cum_flag,
1047 x_cum_period_start_date,
1048 x_cum_period_end_date,
1049 x_cum_quantity_record.primary_unit_of_measure,
1050 x_qty_received_primary,
1051 x_qty_received_purchasing
1052 );
1053
1054 IF (g_asn_debug = 'Y') THEN
1055 asn_debug.put_line('Primary Quantity Received ' || TO_CHAR(NVL(x_qty_received_primary, 0)));
1056 asn_debug.put_line('Purchasing Quantity Received ' || TO_CHAR(NVL(x_qty_received_purchasing, 0)));
1057 END IF;
1058
1059 po_uom_s.uom_convert(x_cum_quantity_record.quantity_shipped,
1060 x_cum_quantity_record.unit_of_measure,
1061 x_cum_quantity_record.item_id,
1062 x_cum_quantity_record.primary_unit_of_measure,
1063 x_new_conversion
1064 );
1065 x_total_cum_shipped := x_qty_received_primary + x_new_conversion;
1066
1067 -- assumption: the vendor_cum_shipped_qty is in the primary uom
1068
1069 IF (g_asn_debug = 'Y') THEN
1070 asn_debug.put_line('Check for quantity discrepancy ');
1071 END IF;
1072
1073 IF (g_asn_debug = 'Y') THEN
1074 asn_debug.put_line('Vendor Cum quantity ' || TO_CHAR(NVL(x_cum_quantity_record.vendor_cum_shipped_qty, 0)));
1075 asn_debug.put_line('Derived Cum Quantity ' || TO_CHAR(NVL(x_total_cum_shipped, -999)));
1076 END IF;
1077
1078 IF (x_total_cum_shipped <> NVL(x_cum_quantity_record.vendor_cum_shipped_qty, 0)) THEN
1079 rcv_error_pkg.set_error_message('RCV_RCV_NO_MATCH_ASN_CUM');
1080 RAISE e_validation_error;
1081 END IF;
1082 END IF;
1083 END IF;
1084 EXCEPTION
1085 WHEN e_validation_error THEN
1086 x_cum_quantity_record.error_record.error_status := x_error_status;
1087 x_cum_quantity_record.error_record.error_message := rcv_error_pkg.get_last_message;
1088
1089 IF x_cum_quantity_record.error_record.error_message = 'RCV_ASL_NO_CUM_QTY' THEN
1090 rcv_error_pkg.set_token('ITEM', x_cum_quantity_record.item_id);
1091 ELSIF x_cum_quantity_record.error_record.error_message = 'RCV_RCV_NO_MATCH_ASN_CUM' THEN
1092 rcv_error_pkg.set_token('SHIPMENT', '');
1093 rcv_error_pkg.set_token('ITEM', x_cum_quantity_record.item_id);
1094 END IF;
1095 END validate_cum_quantity_shipped;
1096
1097 /*===========================================================================
1098
1099 PROCEDURE NAME: validate_po_lookup_code()
1100
1101 ===========================================================================*/
1102 PROCEDURE validate_po_lookup_code(
1103 x_po_lookup_code_record IN OUT NOCOPY rcv_shipment_line_sv.po_lookup_code_record_type
1104 ) IS
1105 x_progress VARCHAR2(3) := NULL;
1106 x_lookup_code VARCHAR2(25) := NULL;
1107 x_error_status VARCHAR2(1);
1108 BEGIN
1109 x_error_status := rcv_error_pkg.g_ret_sts_error;
1110 x_progress := '005';
1111
1112 SELECT NVL(MAX(pol.lookup_code), 'notfound')
1113 INTO x_lookup_code
1114 FROM po_lookup_codes pol
1115 WHERE pol.lookup_code = x_po_lookup_code_record.lookup_code
1116 AND pol.lookup_type = x_po_lookup_code_record.lookup_type;
1117
1118 IF ( x_lookup_code = 'notfound'
1119 AND x_po_lookup_code_record.lookup_type = 'RCV DESTINATION TYPE') THEN
1120 rcv_error_pkg.set_error_message('RCV_DEST_TYPE_CODE_INVALID');
1121 RAISE e_validation_error;
1122 END IF;
1123 EXCEPTION
1124 WHEN e_validation_error THEN
1125 x_po_lookup_code_record.error_record.error_status := x_error_status;
1126 x_po_lookup_code_record.error_record.error_message := rcv_error_pkg.get_last_message;
1127
1128 IF x_po_lookup_code_record.error_record.error_message = 'RCV_DEST_TYPE_CODE_INVALID' THEN
1129 NULL;
1130 END IF;
1131 WHEN OTHERS THEN
1132 x_po_lookup_code_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1133 rcv_error_pkg.set_sql_error_message('validate_po_lookup_code', 000);
1134 x_po_lookup_code_record.error_record.error_status := rcv_error_pkg.get_last_message;
1135 END validate_po_lookup_code;
1136
1137 /*===========================================================================
1138
1139 PROCEDURE NAME: validate_subinventory()
1140
1141 ===========================================================================*/
1142 PROCEDURE validate_subinventory(
1143 x_subinventory_record IN OUT NOCOPY rcv_shipment_line_sv.subinventory_record_type
1144 ) IS
1145 x_progress VARCHAR2(3) := NULL;
1146 x_subinventory VARCHAR2(10) := NULL;
1147 x_error_status VARCHAR2(1);
1148 BEGIN
1149 x_error_status := rcv_error_pkg.g_ret_sts_error;
1150 x_progress := '005';
1151
1152 /*
1153 ** Only go through these validation routines if the destination type
1154 ** is inventory
1155 */
1156 IF (x_subinventory_record.destination_type_code <> 'INVENTORY') THEN
1157 x_subinventory_record.subinventory := NULL;
1158 RETURN;
1159 END IF;
1160
1161 IF (x_subinventory_record.subinventory IS NULL) THEN
1162 rcv_error_pkg.set_error_message('RCV_DEST_SUB_NA');
1163 RAISE e_validation_error;
1164 END IF;
1165
1166 /*
1167 ** Validate the subinventory
1168 */
1169 x_progress := '010';
1170
1171 SELECT NVL(MAX(secondary_inventory_name), 'notfound')
1172 INTO x_subinventory
1173 FROM mtl_secondary_inventories msub,
1174 mtl_system_items msi
1175 WHERE msub.secondary_inventory_name = x_subinventory_record.subinventory
1176 AND msub.organization_id = x_subinventory_record.to_organization_id
1177 AND x_subinventory_record.transaction_date < NVL(msub.disable_date, x_subinventory_record.transaction_date + 1)
1178 AND msi.inventory_item_id = x_subinventory_record.item_id
1179 AND msi.organization_id = x_subinventory_record.to_organization_id
1180 AND ( msi.restrict_subinventories_code = 2
1181 OR ( msi.restrict_subinventories_code = 1
1182 AND EXISTS(SELECT NULL
1183 FROM mtl_item_sub_inventories mis
1184 WHERE mis.organization_id = x_subinventory_record.to_organization_id
1185 AND mis.inventory_item_id = x_subinventory_record.item_id
1186 AND mis.secondary_inventory = x_subinventory_record.subinventory)
1187 )
1188 );
1189
1190 IF (x_subinventory = 'notfound') THEN
1191 rcv_error_pkg.set_error_message('RCV_DEST_SUB_INVALID');
1192 RAISE e_validation_error;
1193 END IF;
1194 EXCEPTION
1195 WHEN e_validation_error THEN
1196 x_subinventory_record.error_record.error_status := x_error_status;
1197 x_subinventory_record.error_record.error_message := rcv_error_pkg.get_last_message;
1198
1199 IF x_subinventory_record.error_record.error_message = 'RCV_DEST_SUB_NA' THEN
1200 NULL;
1201 ELSIF x_subinventory_record.error_record.error_message = 'RCV_DEST_SUB_INVALID' THEN
1202 NULL;
1203 END IF;
1204 WHEN OTHERS THEN
1205 x_subinventory_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1206 rcv_error_pkg.set_sql_error_message('validate_subinventory', x_progress);
1207 x_subinventory_record.error_record.error_status := rcv_error_pkg.get_last_message;
1208 END validate_subinventory;
1209
1210 /*===========================================================================
1211
1212 PROCEDURE NAME: validate_location()
1213
1214 ===========================================================================*/
1215 PROCEDURE validate_location(
1216 x_location_record IN OUT NOCOPY rcv_shipment_line_sv.location_record_type
1217 ) IS
1218 x_progress VARCHAR2(3) := NULL;
1219 x_location NUMBER;
1220 x_error_status VARCHAR2(1);
1221 BEGIN
1222 x_error_status := rcv_error_pkg.g_ret_sts_error;
1223 x_progress := '005';
1224
1225 /*
1226 ** The location id must be set if you're delivering items to either an
1227 ** expense or shop floor location. If must also be set if you're validating
1228 ** a ship-to location since it's a required field. If it's not one of these
1229 ** two cases then just return if the value is null
1230 */
1231 IF ( x_location_record.location_id IS NULL
1232 AND x_location_record.destination_type_code IN('EXPENSE', 'SHOP FLOOR')
1233 AND x_location_record.location_type_code = 'DELIVER_TO') THEN
1234 rcv_error_pkg.set_error_message('RCV_DELIVER_TO_LOC_NA');
1235 RAISE e_validation_error;
1236 ELSIF( x_location_record.location_id IS NULL
1237 AND x_location_record.location_type_code = 'SHIP_TO') THEN
1238 rcv_error_pkg.set_error_message('RCV_SHIP_TO_LOC_NA');
1239 RAISE e_validation_error;
1240 ELSIF(x_location_record.location_id IS NULL) THEN
1241 RETURN;
1242 END IF;
1243
1244 /*
1245 ** Validate the location
1246 */
1247 x_progress := '010';
1248
1249 /* Bug 1904631
1250 * Since location_code for drop ship locations are null in hr_locations,
1251 * max(location_code) will give an incorrect value even if the location_id
1252 * exists in hr_locations. Now select from hr_locations_all
1253 */
1254 BEGIN
1255 SELECT location_id
1256 INTO x_location
1257 FROM hr_locations_all hrl --1942696
1258 WHERE ( hrl.inventory_organization_id = x_location_record.to_organization_id
1259 OR NVL(hrl.inventory_organization_id, 0) = 0)
1260 AND ( hrl.inactive_date IS NULL
1261 OR hrl.inactive_date > SYSDATE)
1262 AND (hrl.location_id = x_location_record.location_id);
1263 EXCEPTION
1264 WHEN NO_DATA_FOUND THEN
1265 BEGIN
1266 SELECT location_id
1267 INTO x_location
1268 FROM hz_locations hz
1269 WHERE ( hz.address_expiration_date IS NULL
1270 OR hz.address_expiration_date > SYSDATE)
1271 AND (hz.location_id = x_location_record.location_id);
1272 EXCEPTION
1273 WHEN NO_DATA_FOUND THEN
1274 IF (x_location_record.location_type_code = 'DELIVER_TO') THEN
1275 rcv_error_pkg.set_error_message('RCV_DELIVER_TO_LOC_INVALID');
1276 RAISE e_validation_error;
1277 ELSE
1278 rcv_error_pkg.set_error_message('RCV_SHIP_TO_LOC_NA');
1279 RAISE e_validation_error;
1280 END IF;
1281 END;
1282 END;
1283 EXCEPTION
1284 WHEN e_validation_error THEN
1285 x_location_record.error_record.error_status := x_error_status;
1286 x_location_record.error_record.error_message := rcv_error_pkg.get_last_message;
1287
1288 IF x_location_record.error_record.error_message = 'RCV_DELIVER_TO_LOC_NA' THEN
1289 NULL;
1290 ELSIF x_location_record.error_record.error_message = 'RCV_SHIP_TO_LOC_NA' THEN
1291 NULL;
1292 ELSIF x_location_record.error_record.error_message = 'RCV_DELIVER_TO_LOC_INVALID' THEN
1293 NULL;
1294 END IF;
1295 WHEN OTHERS THEN
1296 x_location_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1297 rcv_error_pkg.set_sql_error_message('validate_location', x_progress);
1298 x_location_record.error_record.error_status := rcv_error_pkg.get_last_message;
1299 END validate_location;
1300
1301 /*===========================================================================
1302
1303 PROCEDURE NAME: validate_employee()
1304
1305 ===========================================================================*/
1306 PROCEDURE validate_employee(
1307 x_employee_record IN OUT NOCOPY rcv_shipment_line_sv.employee_record_type
1308 ) IS
1309 x_progress VARCHAR2(3) := NULL;
1310 x_full_name VARCHAR2(240) := NULL;
1311 x_error_status VARCHAR2(1);
1312 BEGIN
1313 x_error_status := rcv_error_pkg.g_ret_sts_error;
1314 x_progress := '005';
1315
1316 IF (x_employee_record.employee_id IS NULL) THEN
1317 RETURN;
1318 END IF;
1319
1320 /*
1321 ** Validate the employee
1322 */
1323 x_progress := '010';
1324
1325 SELECT NVL(MAX(hre.full_name), 'notfound')
1326 INTO x_full_name
1327 FROM hr_employees_current_v hre
1328 WHERE ( hre.inactive_date IS NULL
1329 OR hre.inactive_date > SYSDATE)
1330 AND hre.employee_id = x_employee_record.employee_id;
1331
1332 IF (x_full_name = 'notfound') THEN
1333 /*
1334 ** DEBUG: Need another message for an invalid person
1335 */
1336 rcv_error_pkg.set_error_message('RCV_ALL_MISSING_DELIVER_PERSON');
1337 RAISE e_validation_error;
1338 END IF;
1339 EXCEPTION
1340 WHEN e_validation_error THEN
1341 x_employee_record.error_record.error_status := x_error_status;
1342 x_employee_record.error_record.error_message := rcv_error_pkg.get_last_message;
1343
1344 IF x_employee_record.error_record.error_message = 'RCV_ALL_MISSING_DELIVER_PERSON' THEN
1345 NULL;
1346 END IF;
1347 WHEN OTHERS THEN
1348 x_employee_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1349 rcv_error_pkg.set_sql_error_message('validate_employee', x_progress);
1350 x_employee_record.error_record.error_status := rcv_error_pkg.get_last_message;
1351 END validate_employee;
1352
1353 /*===========================================================================
1354
1355 PROCEDURE NAME: validate_locator()
1356
1357 ===========================================================================*/
1358 PROCEDURE validate_locator(
1359 x_locator_record IN OUT NOCOPY rcv_shipment_line_sv.locator_record_type
1360 ) IS
1361 x_progress VARCHAR2(3) := NULL;
1362 x_locator VARCHAR2(81) := NULL;
1363 x_error_status VARCHAR2(1);
1364 BEGIN
1365 x_error_status := rcv_error_pkg.g_ret_sts_error;
1366 x_progress := '005';
1367
1368 /*
1369 ** Only go through these validation routines if the destination type
1370 ** is inventory
1371 */
1372 /* FPJ WMS Change.
1373 * Changed the code to support receiving subinventory and locator.
1374 */
1375 IF ( (x_locator_record.destination_type_code <> 'INVENTORY')
1376 AND (x_locator_record.subinventory IS NULL)) THEN
1377 x_locator_record.locator_id := NULL;
1378 RETURN;
1379 END IF;
1380
1381 IF (g_asn_debug = 'Y') THEN
1382 asn_debug.put_line('Before Get Locator');
1383 END IF;
1384
1385 /*
1386 ** Get the locator controls
1387 */
1388 po_subinventories_s.get_locator_control(x_locator_record.to_organization_id,
1389 x_locator_record.subinventory,
1390 x_locator_record.item_id,
1391 x_locator_record.subinventory_locator_control,
1392 x_locator_record.restrict_locator_control
1393 );
1394
1395 /*
1396 ** If this org/item/sub is not under locator control
1397 ** then simply clear the locator and return
1398 */
1399 IF (x_locator_record.subinventory_locator_control = 1) THEN
1400 x_locator_record.locator_id := NULL;
1401 RETURN;
1402 END IF;
1403
1404 IF (g_asn_debug = 'Y') THEN
1405 asn_debug.put_line('Loc Cont = ' || TO_CHAR(x_locator_record.subinventory_locator_control));
1406 asn_debug.put_line('Rest Cont = ' || x_locator_record.restrict_locator_control);
1407 END IF;
1408
1409 /*
1410 ** bug 724495, the else part now runs only if the
1411 ** subinventory_locator_control is in (2,3)
1412 */
1413
1414 /* FPJ WMS Change. We do not support locator restrictions for receiving
1415 * subinventory and locators. Hence make restrict_locator_control as 2
1416 * (no restriction).
1417 */
1418 IF (x_locator_record.destination_type_code <> 'INVENTORY') THEN
1419 x_locator_record.restrict_locator_control := 2;
1420 END IF;
1421
1422 /*
1423 ** If locator control is 2 or 3 and the item is not under restricted locator
1424 ** control then do simple unrestricted check
1425 */
1426 IF (x_locator_record.subinventory_locator_control IN(2, 3)) THEN
1427 IF (NVL(x_locator_record.restrict_locator_control, 2) = 2) THEN
1428 /* 3017707 - We need to validate the locator in the receiving organization. Added the filter on
1429 organization id */
1430 IF (g_asn_debug = 'Y') THEN
1431 asn_debug.put_line('Sub is not under restricted locator control');
1432 END IF;
1433
1434 SELECT NVL(MAX(ml.concatenated_segments), 'notfound')
1435 INTO x_locator
1436 FROM mtl_item_locations_kfv ml
1437 WHERE ml.inventory_location_id = x_locator_record.locator_id
1438 AND ( ml.disable_date > SYSDATE
1439 OR ml.disable_date IS NULL)
1440 AND NVL(ml.subinventory_code, 'z') = NVL(x_locator_record.subinventory, 'z')
1441 AND ml.organization_id = x_locator_record.to_organization_id;
1442 /*
1443 ** ELSE If locator control is 2 or 3 and the item is under restricted locator
1444 ** control then do restricted check
1445 */
1446 ELSE
1447 IF (g_asn_debug = 'Y') THEN
1448 asn_debug.put_line('Sub is under restricted locator control');
1449 END IF;
1450
1451 SELECT NVL(MAX(ml.concatenated_segments), 'notfound')
1452 INTO x_locator
1453 FROM mtl_item_locations_kfv ml
1454 WHERE ml.inventory_location_id = x_locator_record.locator_id
1455 AND ( ml.disable_date > SYSDATE
1456 OR ml.disable_date IS NULL)
1457 AND NVL(ml.subinventory_code, 'z') = NVL(x_locator_record.subinventory, 'z')
1458 AND ml.inventory_location_id IN(SELECT secondary_locator
1459 FROM mtl_secondary_locators msl
1460 WHERE msl.inventory_item_id = x_locator_record.item_id
1461 AND msl.organization_id = x_locator_record.to_organization_id
1462 AND msl.subinventory_code = x_locator_record.subinventory);
1463 END IF;
1464 END IF;
1465
1466 IF (x_locator = 'notfound') THEN
1467 IF (g_asn_debug = 'Y') THEN
1468 asn_debug.put_line('In locator Errors');
1469 END IF;
1470
1471 rcv_error_pkg.set_error_message('RCV_ALL_REQUIRED_LOCATOR');
1472 RAISE e_validation_error;
1473 END IF;
1474 EXCEPTION
1475 WHEN e_validation_error THEN
1476 x_locator_record.error_record.error_status := x_error_status;
1477 x_locator_record.error_record.error_message := rcv_error_pkg.get_last_message;
1478
1479 IF x_locator_record.error_record.error_message = 'RCV_ALL_REQUIRED_LOCATOR' THEN
1480 NULL;
1481 END IF;
1482 WHEN OTHERS THEN
1483 x_locator_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1484 rcv_error_pkg.set_sql_error_message('validate_locator', x_progress);
1485 x_locator_record.error_record.error_status := rcv_error_pkg.get_last_message;
1486 END validate_locator;
1487
1488 /*===========================================================================
1489
1490 PROCEDURE NAME: validate_country_of_origin()
1491
1492 ===========================================================================*/
1493 PROCEDURE validate_country_of_origin(
1494 x_country_of_origin_record IN OUT NOCOPY rcv_shipment_line_sv.country_of_origin_record_type
1495 ) IS
1496 x_code fnd_territories_vl.territory_code%TYPE := NULL;
1497 x_progress VARCHAR2(3);
1498 x_error_status VARCHAR2(1);
1499 BEGIN
1500 x_error_status := rcv_error_pkg.g_ret_sts_error;
1501
1502 SELECT NVL(MAX(territory_code), 'FF')
1503 INTO x_code
1504 FROM fnd_territories_vl
1505 WHERE territory_code = x_country_of_origin_record.country_of_origin_code;
1506
1507 IF (x_code = 'FF') THEN
1508 rcv_error_pkg.set_error_message('RCV_ASN_ORIGIN_COUNTRY_INVALID');
1509 RAISE e_validation_error;
1510 END IF;
1511 EXCEPTION
1512 WHEN e_validation_error THEN
1513 x_country_of_origin_record.error_record.error_status := x_error_status;
1514 x_country_of_origin_record.error_record.error_message := rcv_error_pkg.get_last_message;
1515
1516 IF x_country_of_origin_record.error_record.error_message = 'RCV_ASN_ORIGIN_COUNTRY_INVALID' THEN
1517 rcv_error_pkg.set_token('COUNTRY_OF_ORIGIN_CODE', x_country_of_origin_record.country_of_origin_code);
1518 rcv_error_pkg.set_token('SHIPMENT', '');
1519 END IF;
1520 WHEN OTHERS THEN
1521 x_country_of_origin_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1522 rcv_error_pkg.set_sql_error_message('validate_country_of_origin', x_progress);
1523 x_country_of_origin_record.error_record.error_status := rcv_error_pkg.get_last_message;
1524 END validate_country_of_origin;
1525
1526 /* <Consigned Inventory Pre-Processor FPI START> */
1527
1528 /*===========================================================================
1529 PROCEDURE NAME: validate_consigned_po()
1530 ===========================================================================*/
1531 PROCEDURE validate_consigned_po(
1532 x_consigned_po_rec IN OUT NOCOPY rcv_shipment_line_sv.po_line_location_id_rtype
1533 ) IS
1534 l_consigned_po_flag po_line_locations_all.consigned_flag%TYPE;
1535 x_error_status VARCHAR2(1);
1536 BEGIN
1537 x_error_status := rcv_error_pkg.g_ret_sts_error;
1538
1539 SELECT consigned_flag
1540 INTO l_consigned_po_flag
1541 FROM po_line_locations
1542 WHERE line_location_id = x_consigned_po_rec.po_line_location_id;
1543
1544 IF (l_consigned_po_flag = 'Y') THEN
1545 IF (g_asn_debug = 'Y') THEN
1546 asn_debug.put_line('in RCVTIS2B.pls' || l_consigned_po_flag);
1547 END IF;
1548
1549 rcv_error_pkg.set_error_message('RCV_REJECT_ASBN_CONSIGNED_PO');
1550 RAISE e_validation_error;
1551 END IF;
1552 EXCEPTION
1553 WHEN e_validation_error THEN
1554 x_consigned_po_rec.error_record.error_status := x_error_status;
1555 x_consigned_po_rec.error_record.error_message := rcv_error_pkg.get_last_message;
1556
1557 IF x_consigned_po_rec.error_record.error_message = 'RCV_REJECT_ASBN_CONSIGNED_PO' THEN
1558 NULL;
1559 END IF;
1560 WHEN OTHERS THEN
1561 x_consigned_po_rec.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1562 rcv_error_pkg.set_sql_error_message('validate_consigned_po', '000');
1563 x_consigned_po_rec.error_record.error_status := rcv_error_pkg.get_last_message;
1564 END validate_consigned_po;
1565
1566 /*===========================================================================
1567 PROCEDURE NAME: validate_consumption_po()
1568 ===========================================================================*/
1569 PROCEDURE validate_consumption_po(
1570 x_consumption_po_rec IN OUT NOCOPY rcv_shipment_line_sv.document_num_record_type
1571 ) IS
1572 l_consumption_po_flag po_headers_all.consigned_consumption_flag%TYPE;
1573 x_error_status VARCHAR2(1);
1574 BEGIN
1575 x_error_status := rcv_error_pkg.g_ret_sts_error;
1576
1577 SELECT consigned_consumption_flag
1578 INTO l_consumption_po_flag
1579 FROM po_headers
1580 WHERE po_header_id = x_consumption_po_rec.po_header_id;
1581
1582 IF (l_consumption_po_flag = 'Y') THEN
1583 IF (g_asn_debug = 'Y') THEN
1584 asn_debug.put_line('in RCVTIS2B.pls' || l_consumption_po_flag);
1585 END IF;
1586
1587 rcv_error_pkg.set_error_message('RCV_REJECT_CONSUMPTION_PO');
1588 RAISE e_validation_error;
1589 END IF;
1590 EXCEPTION
1591 WHEN e_validation_error THEN
1592 x_consumption_po_rec.error_record.error_status := x_error_status;
1593 x_consumption_po_rec.error_record.error_message := rcv_error_pkg.get_last_message;
1594
1595 IF x_consumption_po_rec.error_record.error_message = 'RCV_REJECT_CONSUMPTION_PO' THEN
1596 NULL;
1597 END IF;
1598 WHEN OTHERS THEN
1599 x_consumption_po_rec.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1600 rcv_error_pkg.set_sql_error_message('validate_consumption_po', '000');
1601 x_consumption_po_rec.error_record.error_status := rcv_error_pkg.get_last_message;
1602 END validate_consumption_po;
1603
1604 /*===========================================================================
1605 PROCEDURE NAME: validate_consumption_release()
1606 ===========================================================================*/
1607 PROCEDURE validate_consumption_release(
1608 x_consumption_release_rec IN OUT NOCOPY rcv_shipment_line_sv.release_id_record_type
1609 ) IS
1610 l_consumption_release_flag po_releases_all.consigned_consumption_flag%TYPE;
1611 x_error_status VARCHAR2(1);
1612 BEGIN
1613 x_error_status := rcv_error_pkg.g_ret_sts_error;
1614
1615 SELECT consigned_consumption_flag
1616 INTO l_consumption_release_flag
1617 FROM po_releases
1618 WHERE po_release_id = x_consumption_release_rec.po_release_id;
1619
1620 IF (l_consumption_release_flag = 'Y') THEN
1621 IF (g_asn_debug = 'Y') THEN
1622 asn_debug.put_line('in RCVTIS2B.pls, consumption release' || l_consumption_release_flag);
1623 END IF;
1624
1625 rcv_error_pkg.set_error_message('RCV_REJECT_CONSUMPTION_RELEASE');
1626 RAISE e_validation_error;
1627 END IF;
1628 EXCEPTION
1629 WHEN e_validation_error THEN
1630 x_consumption_release_rec.error_record.error_status := x_error_status;
1631 x_consumption_release_rec.error_record.error_message := rcv_error_pkg.get_last_message;
1632
1633 IF x_consumption_release_rec.error_record.error_message = 'RCV_REJECT_CONSUMPTION_RELEASE' THEN
1634 NULL;
1635 END IF;
1636 WHEN OTHERS THEN
1637 x_consumption_release_rec.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1638 rcv_error_pkg.set_sql_error_message('validate_consumption_release', '000');
1639 x_consumption_release_rec.error_record.error_status := rcv_error_pkg.get_last_message;
1640 END validate_consumption_release;
1641
1642
1643 /*##########################################################################
1644 #
1645 # PROCEDURE
1646 # VALIDATE_SECONDARY_PARAMETERS
1647 #
1648 # DESCRIPTION
1649 #
1650 # For Dual UOM controlled items validate the secondary UOM code and
1651 # Secondary UOM. Derive them if either/both are not specified.
1652 # For Receipt if secondary quantity is there then it will validate it
1653 # (will do the deviation check for it )else it will derive it.
1654 #
1655 # Method of logging errors:
1656 #
1657 # 1) If business logic fails:
1658 # Message is set and exception is raised.
1659 # In exception block return status is set to "Error"
1660 # The last message is retrieved and the program ends there.
1661 #
1662 # 2) If an unexpected failure occours:
1663 # Messge is set.
1664 # Last message is retrieved at that place only.
1665 # return status is set to "Unexpected Error" and control is
1666 # returned back to the calling program.
1667 #
1668 # 3) Messages are also added to error stack.
1669 #
1670 # DESIGN REFERENCES:
1671 # INVCONV.
1672 # http://files.oraclecorp.com/content/AllPublic/Workspaces/
1673 # Inventory%20Convergence-Public/Design/Oracle%20Purchasing/TDD/PO_ROI_TDD.zip
1674 #
1675 #
1676 # MODIFICATION HISTORY
1677 # 23-AUG-2004 Punit Kumar Created
1678 #
1679 #########################################################################*/
1680
1681
1682 PROCEDURE VALIDATE_SECONDARY_PARAMETERS(
1683 p_api_version IN NUMBER ,
1684 p_init_msg_lst IN VARCHAR2 ,
1685 x_att_rec IN OUT NOCOPY RCV_TRANSACTIONS_INTERFACE_SV1.attributes_record_type ,
1686 x_return_status OUT NOCOPY VARCHAR2 ,
1687 x_msg_count OUT NOCOPY NUMBER ,
1688 x_msg_data OUT NOCOPY VARCHAR2
1689 )
1690 IS
1691
1692 l_api_name VARCHAR2(30) := 'VALIDATE_SECONDARY_PARAMETERS' ;
1693 l_api_version CONSTANT NUMBER := 1.0 ;
1694
1695 l_return_status VARCHAR2(1) ;
1696 l_msg_data VARCHAR2(3000) ;
1697 l_msg_count NUMBER ;
1698
1699 l_check_dev NUMBER ;
1700 l_TRACKING_QUANTITY_IND VARCHAR2(30) ;
1701 l_secondary_default_ind VARCHAR2(10) ;
1702 l_secondary_uom_code VARCHAR2(3) ;
1703 l_secondary_unit_of_measure VARCHAR2(25) ;
1704 l_progress VARCHAR2(10) := '0000' ;
1705
1706
1707 BEGIN
1708 l_progress :='0001';
1709 IF (g_asn_debug = 'Y') THEN
1710 asn_debug.put_line('VALIDATE_SECONDARY_PARAMETERS: Entering' || l_progress);
1711 END IF;
1712
1713
1714 -- Standard call to check for call compatibility.
1715 IF NOT fnd_api.compatible_api_call(
1716 l_api_version,
1717 p_api_version,
1718 l_api_name,
1719 'PO_VALIDATE_PARAMETERS'
1720 ) THEN
1721 l_progress :='0002';
1722 IF (g_asn_debug = 'Y') THEN
1723 asn_debug.put_line('FND_API not compatible rcv_transactions_interface_sv1.VALIDATE_SECONDARY_PARAMETERS'||l_progress);
1724 END IF;
1725 END IF;
1726
1727 -- Initialize message list if p_init_msg_list is set to TRUE.
1728 IF fnd_api.to_boolean(p_init_msg_lst) THEN
1729 fnd_msg_pub.initialize;
1730 END IF;
1731
1732 --Initialize the return status
1733 x_return_status := FND_API.G_RET_STS_SUCCESS;
1734 x_att_rec.error_record.error_status := FND_API.G_RET_STS_SUCCESS;
1735 x_att_rec.error_record.error_message := NULL;
1736
1737 /*Defaulting of origination type to 'Purchasing' to be done
1738 in INV_RCV_INTEGRATION_PVT.MOVE_LOT_SERIAL_INFO */
1739
1740
1741 l_progress := '001';
1742
1743 BEGIN
1744 l_progress :='0003';
1745 IF (g_asn_debug = 'Y') THEN
1746 asn_debug.put_line('Inside rcv_transactions_interface_sv1.VALIDATE_SECONDARY_PARAMETERS:' || l_progress);
1747 END IF;
1748
1749 -------Checking if the item is dual UOM controlled. If not then Return .
1750 SELECT tracking_quantity_ind , secondary_default_ind
1751 INTO l_TRACKING_QUANTITY_IND ,l_secondary_default_ind
1752 FROM mtl_system_items_b
1753 WHERE INVENTORY_ITEM_ID = x_att_rec.inventory_item_id
1754 AND ORGANIZATION_ID = x_att_rec.to_organization_id;
1755
1756 l_progress :='0004';
1757 IF (g_asn_debug = 'Y') THEN
1758 asn_debug.put_line('Value of tracking_quantity_ind is ' ||l_TRACKING_QUANTITY_IND ||':'||l_progress);
1759 END IF;
1760
1761
1762 EXCEPTION
1763 WHEN OTHERS THEN
1764 l_progress :='0005';
1765 IF g_asn_debug = 'Y' THEN
1766 asn_debug.put_line('Dual UOM check failed:' || l_progress);
1767 END IF;
1768
1769 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_unexp_error;
1770 rcv_error_pkg.set_sql_error_message('Unexpected Exception in validate_secondary_parameters', l_progress);
1771 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
1772
1773 RETURN;
1774
1775 END;
1776
1777 /*For non dual items.*/
1778 IF l_TRACKING_QUANTITY_IND <> 'PS' THEN
1779 l_progress :='0006';
1780 IF (g_asn_debug = 'Y') THEN
1781 asn_debug.put_line('Item is not dual UOM controlled.:'|| l_progress);
1782 END IF;
1783
1784 /*Error out if secondary parameters are specified and item is not dual uom controlled*/
1785 IF x_att_rec.secondary_uom_code IS NOT NULL THEN
1786 /*
1787 rcv_error_pkg.set_error_message('PO_SECONDARY_UOM_NOT_REQUIRED');
1788 RAISE e_validation_error;
1789 */
1790 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_error;
1791 rcv_error_pkg.set_error_message('PO_SECONDARY_UOM_NOT_REQUIRED');
1792 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
1793
1794 RETURN;
1795
1796
1797 ELSIF x_att_rec.secondary_unit_of_measure IS NOT NULL THEN
1798 /*
1799 rcv_error_pkg.set_error_message('PO_SECONDARY_UOM_NOT_REQUIRED');
1800 RAISE e_validation_error;
1801 */
1802 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_error;
1803 rcv_error_pkg.set_error_message('PO_SECONDARY_UOM_NOT_REQUIRED');
1804 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
1805
1806 RETURN;
1807
1808
1809 ELSIF x_att_rec.secondary_quantity IS NOT NULL THEN
1810 /*
1811 rcv_error_pkg.set_error_message('PO_SECONDARY_QTY_NOT_REQUIRED');
1812 RAISE e_validation_error;
1813 */
1814 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_error;
1815 rcv_error_pkg.set_error_message('PO_SECONDARY_QTY_NOT_REQUIRED');
1816 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
1817
1818 RETURN;
1819
1820
1821 ELSE
1822 /* Having this return statement so that program exits as successful if no secondary parameter has been input for a non dual item*/
1823 x_att_rec.error_record.error_status := FND_API.G_RET_STS_SUCCESS;
1824 RETURN;
1825 END IF;
1826 END IF;
1827
1828 /* If it is dual UOM controlled then only proceed */
1829
1830
1831 IF x_att_rec.secondary_uom_code IS NOT NULL AND x_att_rec.secondary_unit_of_measure IS NULL THEN
1832
1833 l_progress :='0007';
1834
1835 BEGIN
1836
1837 SELECT SECONDARY_UOM_CODE
1838 INTO l_secondary_uom_code
1839 FROM MTL_SYSTEM_ITEMS_B
1840 WHERE MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID = x_att_rec.inventory_item_id
1841 AND MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID = x_att_rec.to_organization_id;
1842
1843 IF g_asn_debug = 'Y' THEN
1844 asn_debug.put_line('Secondary uom code derived in VALIDATE_SECONDARY_PARAMETERS is ' ||l_secondary_uom_code||':'||l_progress );
1845 END IF;
1846
1847 IF l_secondary_uom_code <> x_att_rec.secondary_uom_code THEN
1848 l_progress :='0008JN';
1849 IF g_asn_debug = 'Y' THEN
1850 asn_debug.put_line('Secondary uom code validation failed in VALIDATE_SECONDARY_PARAMETERS:' || l_progress);
1851 END IF;
1852
1853 /*Log error into po_interface_error*/
1854 /*
1855 rcv_error_pkg.set_error_message('PO_INCORRECT_SECONDARY_UOM');
1856 RAISE e_validation_error;
1857 */
1858 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_error;
1859 rcv_error_pkg.set_error_message('PO_INCORRECT_SECONDARY_UOM');
1860 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
1861
1862 RETURN;
1863
1864 ELSE
1865 Select UNIT_OF_MEASURE
1866 INTO x_att_rec.secondary_unit_of_measure
1867 FROM mtl_units_of_measure
1868 WHERE uom_code = x_att_rec.secondary_uom_code;
1869
1870 l_progress :='0009';
1871 IF g_asn_debug = 'Y' THEN
1872 asn_debug.put_line('Secondary unit of measure derived in VALIDATE_SECONDARY_PARAMETERS is ' ||x_att_rec.secondary_unit_of_measure||':'||l_progress );
1873 END IF;
1874 END IF;
1875
1876 EXCEPTION
1877 WHEN OTHERS THEN
1878 IF g_asn_debug = 'Y' THEN
1879 asn_debug.put_line('SQL in validate_secondary_parameters failed:' || l_progress);
1880 END IF;
1881 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_unexp_error;
1882 rcv_error_pkg.set_sql_error_message('Unexpected Exception:validate_secondary_parameters', l_progress);
1883 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
1884
1885 RETURN;
1886 END;
1887
1888 ELSIF x_att_rec.secondary_uom_code IS NULL AND x_att_rec.secondary_unit_of_measure IS NOT NULL THEN
1889
1890 l_progress :='0010';
1891
1892 BEGIN
1893
1894 SELECT SECONDARY_UOM_CODE
1895 INTO x_att_rec.secondary_uom_code
1896 FROM MTL_SYSTEM_ITEMS_B
1897 WHERE MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID=x_att_rec.inventory_item_id
1898 AND MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID=x_att_rec.to_organization_id;
1899
1900 IF g_asn_debug = 'Y' THEN
1901 asn_debug.put_line('Secondary uom code derived in VALIDATE_SECONDARY_PARAMETERS is ' ||x_att_rec.secondary_uom_code ||':'||l_progress);
1902 END IF;
1903
1904 SELECT UNIT_OF_MEASURE
1905 INTO l_secondary_unit_of_measure
1906 FROM mtl_units_of_measure
1907 WHERE uom_code = x_att_rec.secondary_uom_code;
1908
1909 l_progress :='0011';
1910 IF g_asn_debug = 'Y' THEN
1911 asn_debug.put_line('Secondary unit of measure derived in VALIDATE_SECONDARY_PARAMETERS is ' ||l_secondary_unit_of_measure||':'|| l_progress );
1912 END IF;
1913
1914 IF l_secondary_unit_of_measure <> x_att_rec.secondary_unit_of_measure THEN
1915
1916 l_progress :='0012';
1917 IF g_asn_debug = 'Y' THEN
1918 asn_debug.put_line('Secondary unit of measure validation failed in VALIDATE_SECONDARY_PARAMETERS'||':'||l_progress);
1919 END IF;
1920
1921 /*Log error into po_interface_error*/
1922 /*
1923 rcv_error_pkg.set_error_message('PO_INCORRECT_SECONDARY_UOM');
1924 RAISE e_validation_error;
1925 */
1926 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_error;
1927 rcv_error_pkg.set_error_message('PO_INCORRECT_SECONDARY_UOM');
1928 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
1929
1930 RETURN;
1931
1932 END IF;
1933
1934 EXCEPTION
1935 WHEN OTHERS THEN
1936 IF g_asn_debug = 'Y' THEN
1937 asn_debug.put_line('SQL in validate_secondary_parameters failed:' || l_progress);
1938 END IF;
1939 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_unexp_error;
1940 rcv_error_pkg.set_sql_error_message('Unexpected Exception:validate_secondary_parameters', l_progress);
1941 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
1942
1943 RETURN;
1944 END;
1945
1946 ELSIF x_att_rec.secondary_uom_code IS NULL AND x_att_rec.secondary_unit_of_measure IS NULL THEN
1947
1948 l_progress :='0013';
1949
1950 BEGIN
1951
1952 SELECT SECONDARY_UOM_CODE
1953 INTO x_att_rec.secondary_uom_code
1954 FROM MTL_SYSTEM_ITEMS_B
1955 WHERE MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID=x_att_rec.inventory_item_id
1956 AND MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID=x_att_rec.to_organization_id;
1957
1958 IF g_asn_debug = 'Y' THEN
1959 asn_debug.put_line('Secondary uom code derived in VALIDATE_SECONDARY_PARAMETERS is ' ||x_att_rec.secondary_uom_code||':'||l_progress );
1960 END IF;
1961
1962 Select UNIT_OF_MEASURE
1963 INTO x_att_rec.secondary_unit_of_measure
1964 FROM mtl_units_of_measure
1965 WHERE uom_code = x_att_rec.secondary_uom_code;
1966
1967 l_progress :='0014';
1968 IF g_asn_debug = 'Y' THEN
1969 asn_debug.put_line('Secondary unit of measure derived in VALIDATE_SECONDARY_PARAMETERS is ' ||x_att_rec.secondary_unit_of_measure ||':'||l_progress);
1970 END IF;
1971
1972 EXCEPTION
1973 WHEN OTHERS THEN
1974 IF g_asn_debug = 'Y' THEN
1975 asn_debug.put_line('SQL in validate_secondary_parameters failed:' || l_progress);
1976 END IF;
1977 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_unexp_error;
1978 rcv_error_pkg.set_sql_error_message('Unexpected Exception:validate_secondary_parameters', l_progress);
1979 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
1980
1981 RETURN;
1982 END;
1983
1984 ELSIF x_att_rec.secondary_uom_code IS NOT NULL AND x_att_rec.secondary_unit_of_measure IS NOT NULL THEN
1985
1986 l_progress :='0015';
1987
1988 BEGIN
1989
1990 SELECT SECONDARY_UOM_CODE
1991 INTO l_secondary_uom_code
1992 FROM MTL_SYSTEM_ITEMS_B
1993 WHERE MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID=x_att_rec.inventory_item_id
1994 AND MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID=x_att_rec.to_organization_id;
1995
1996 IF g_asn_debug = 'Y' THEN
1997 asn_debug.put_line('Secondary uom code derived in VALIDATE_SECONDARY_PARAMETERS is ' ||l_secondary_uom_code||':'||l_progress );
1998 END IF;
1999
2000 IF l_secondary_uom_code <>x_att_rec.secondary_uom_code THEN
2001 l_progress :='0016';
2002 IF g_asn_debug = 'Y' THEN
2003 asn_debug.put_line('Secondary uom code validation failed in VALIDATE_SECONDARY_PARAMETERS:'||l_progress);
2004 END IF;
2005
2006 /*Log error into po_interface_error*/
2007 /*
2008 rcv_error_pkg.set_error_message('PO_INCORRECT_SECONDARY_UOM');
2009 RAISE e_validation_error;
2010 */
2011 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_error;
2012 rcv_error_pkg.set_error_message('PO_INCORRECT_SECONDARY_UOM');
2013 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
2014
2015 RETURN;
2016
2017
2018 ELSE
2019 /*Secondary uom code matches so now validate the secondary unit of measure*/
2020 l_progress :='0017';
2021
2022 SELECT UNIT_OF_MEASURE
2023 INTO l_secondary_unit_of_measure
2024 FROM mtl_units_of_measure
2025 WHERE uom_code = x_att_rec.secondary_uom_code;
2026
2027 l_progress :='0171';
2028 IF g_asn_debug = 'Y' THEN
2029 asn_debug.put_line('Secondary unit of measure derived in VALIDATE_SECONDARY_PARAMETERS is ' ||l_secondary_unit_of_measure||':'|| l_progress );
2030 END IF;
2031
2032 IF l_secondary_unit_of_measure <> x_att_rec.secondary_unit_of_measure THEN
2033
2034 l_progress :='0172';
2035
2036 IF g_asn_debug = 'Y' THEN
2037 asn_debug.put_line('Secondary unit of measure validation failed in VALIDATE_SECONDARY_PARAMETERS'||':'||l_progress);
2038 END IF;
2039
2040 /*Log error into po_interface_error*/
2041 /*
2042 rcv_error_pkg.set_error_message('PO_INCORRECT_SECONDARY_UOM');
2043 RAISE e_validation_error;
2044 */
2045 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_error;
2046 rcv_error_pkg.set_error_message('PO_INCORRECT_SECONDARY_UOM');
2047 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
2048
2049 RETURN;
2050
2051
2052 END IF; -----IF l_secondary_unit_of_measure <> x_att_rec.secondary_unit_of_measure THEN
2053
2054 END IF; ------------IF l_secondary_uom_code <>x_att_rec.secondary_uom_code THEN
2055
2056 EXCEPTION
2057 WHEN OTHERS THEN
2058 IF g_asn_debug = 'Y' THEN
2059 asn_debug.put_line('SQL in validate_secondary_parameters failed:' || l_progress);
2060 END IF;
2061 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_unexp_error;
2062 rcv_error_pkg.set_sql_error_message('Unexpected Exception:validate_secondary_parameters', l_progress);
2063 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
2064
2065 RETURN;
2066 END;
2067
2068 END IF; --------IF x_att_rec.secondary_uom_code IS NOT NULL AND x_att_rec.secondary_unit_of_measure IS NULL THEN
2069
2070
2071 IF x_att_rec.secondary_quantity IS NOT NULL AND l_secondary_default_ind IN ('D','N') THEN
2072 l_progress :='0018';
2073 IF g_asn_debug = 'Y' THEN
2074 asn_debug.put_line('Before calling within deviation check in VALIDATE_SECONDARY_PARAMETERS:' ||l_progress);
2075 END IF;
2076
2077 l_check_dev := INV_CONVERT.Within_deviation(
2078 p_organization_id => X_ATT_REC.to_organization_id ,
2079 p_inventory_item_id => X_ATT_REC.inventory_item_id ,
2080 p_lot_number => NULL /* as no lot is defined */ ,
2081 p_precision => 5 ,
2082 p_quantity => X_ATT_REC.transaction_quantity ,
2083 p_uom_code1 => NULL ,
2084 p_quantity2 => X_ATT_REC.secondary_quantity ,
2085 p_uom_code2 => NULL ,
2086 p_unit_of_measure1 => X_ATT_REC.transaction_unit_of_measure ,
2087 p_unit_of_measure2 => X_ATT_REC.Secondary_unit_of_measure
2088 );
2089
2090 /* Returns 1 for True and 0 for False*/
2091
2092 IF l_check_dev=0 THEN
2093 l_progress :='0019';
2094 IF g_asn_debug = 'Y' THEN
2095 asn_debug.put_line('within deviation check failed in VALIDATE_SECONDARY_PARAMETERS:' || l_progress);
2096 END IF;
2097 /*Log error into po_interface_error */
2098 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_error;
2099 rcv_error_pkg.set_error_message('PO_SRCE_ORG_OUT_OF_DEV');
2100 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
2101
2102 RETURN;
2103 END IF;
2104
2105 l_progress :='0020';
2106 IF g_asn_debug = 'Y' THEN
2107 asn_debug.put_line('After successfully calling within deviation check in VALIDATE_SECONDARY_PARAMETERS:' || l_progress);
2108 END IF;
2109
2110
2111 ELSE -------- x_att_rec.secondary_quantity IS NULL OR l_secondary_default_ind = 'F' THEN
2112 /*Calculate the secondary quantity if it is null or the item is of fixed type.
2113 As for fixed type we do not check deviation but the secondary quantity has
2114 to be fixed according to a predefined conversion*/
2115
2116 l_progress :='0021';
2117
2118 IF g_asn_debug = 'Y' THEN
2119 asn_debug.put_line('Befordesc inv_e calling INV_CONVERT.Inv_um_convert for fetching secondary quantity in VALIDATE_SECONDARY_PARAMETERS:'||l_progress);
2120 END IF;
2121
2122 x_att_rec.secondary_quantity:= INV_CONVERT.Inv_um_convert (
2123 item_id => x_att_rec.inventory_item_id ,
2124 lot_number => NULL ,
2125 organization_id => x_att_rec.to_organization_id ,
2126 precision => 5 ,
2127 from_quantity => x_att_rec.transaction_quantity ,
2128 from_unit => NULL ,
2129 to_unit => NULL ,
2130 from_name => x_att_rec.transaction_unit_of_measure ,
2131 to_name => x_att_rec.secondary_unit_of_measure
2132 );
2133
2134
2135
2136 IF x_att_rec.secondary_quantity = -99999 THEN
2137 l_progress :='0022';
2138 IF g_asn_debug = 'Y' THEN
2139 asn_debug.put_line('fetch secondary quantity failed in VALIDATE_SECONDARY_PARAMETERS:' || l_progress);
2140 END IF;
2141 /*Log error into po_interface_error */
2142 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_unexp_error;
2143 rcv_error_pkg.set_sql_error_message('Unexpected exception :fetch secondary quantity failed in validate_secondary_parameters', l_progress);
2144 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
2145
2146 RETURN;
2147 END IF;
2148
2149 l_progress :='0023';
2150 IF g_asn_debug = 'Y' THEN
2151 asn_debug.put_line('After successfully calling INV_CONVERT.Inv_um_convert for fetching secondary quantity in VALIDATE_SECONDARY_PARAMETERS:' || l_progress);
2152 asn_debug.put_line('value of secondary quantity derived by INV_CONVERT.Inv_um_convert is:'|| x_att_rec.secondary_quantity);
2153 END IF;
2154
2155
2156 END IF;-----IF x_att_rec.secondary_quantity IS NOT NULL AND l_secondary_default_ind IN ('D','N') THEN
2157
2158 l_progress :='0024';
2159 IF g_asn_debug = 'Y' THEN
2160 asn_debug.put_line('End of VALIDATE_SECONDARY_PARAMETERS:' || l_progress);
2161 END IF;
2162
2163
2164 EXCEPTION
2165 WHEN e_validation_error THEN
2166 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_error;
2167 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
2168
2169 WHEN OTHERS THEN
2170 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_unexp_error;
2171 rcv_error_pkg.set_sql_error_message('Unexpected exception:validate_secondary_parameters', l_progress);
2172 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
2173
2174
2175 END VALIDATE_SECONDARY_PARAMETERS;
2176
2177 /* <Consigned Inventory Pre-Processor FPI END> */
2178 END rcv_transactions_interface_sv1;
2179
2180
[Home] [Help]