1 PACKAGE BODY rcv_transactions_interface_sv1 AS
2 /* $Header: RCVTIS2B.pls 120.4.12020000.4 2012/11/22 10:20:22 liayang ship $*/
3
4 -- Read the profile option that enables/disables the debug log
5 g_asn_debug VARCHAR2(1) := asn_debug.is_debug_on; -- Bug 9152790
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 /* begin changes for bug 7488437*/
1401 IF (x_locator_record.locator_id IS NOT NULL) THEN
1402 IF (g_asn_debug = 'Y') THEN
1403 asn_debug.put_line('Error: Subinventory is not locator controlled, but locator info is given');
1404 END IF;
1405 rcv_error_pkg.set_error_message('RCV_NO_LOCATOR_CONTROL');
1406 RAISE e_validation_error;
1407 END IF;
1408 return;
1409 /* end changes for bug 7488437*/
1410 END IF;
1411
1412 IF (g_asn_debug = 'Y') THEN
1413 asn_debug.put_line('Loc Cont = ' || TO_CHAR(x_locator_record.subinventory_locator_control));
1414 asn_debug.put_line('Rest Cont = ' || x_locator_record.restrict_locator_control);
1415 END IF;
1416
1417 /*
1418 ** bug 724495, the else part now runs only if the
1419 ** subinventory_locator_control is in (2,3)
1420 */
1421
1422 /* FPJ WMS Change. We do not support locator restrictions for receiving
1423 * subinventory and locators. Hence make restrict_locator_control as 2
1424 * (no restriction).
1425 */
1426 IF (x_locator_record.destination_type_code <> 'INVENTORY') THEN
1427 x_locator_record.restrict_locator_control := 2;
1428 END IF;
1429
1430 /*
1431 ** If locator control is 2 or 3 and the item is not under restricted locator
1432 ** control then do simple unrestricted check
1433 */
1434 IF (x_locator_record.subinventory_locator_control IN(2, 3)) THEN
1435 IF (NVL(x_locator_record.restrict_locator_control, 2) = 2) THEN
1436 /* 3017707 - We need to validate the locator in the receiving organization. Added the filter on
1437 organization id */
1438 IF (g_asn_debug = 'Y') THEN
1439 asn_debug.put_line('Sub is not under restricted locator control');
1440 END IF;
1441
1442 SELECT NVL(MAX(ml.concatenated_segments), 'notfound')
1443 INTO x_locator
1444 FROM mtl_item_locations_kfv ml
1445 WHERE ml.inventory_location_id = x_locator_record.locator_id
1446 AND ( ml.disable_date > SYSDATE
1447 OR ml.disable_date IS NULL)
1448 AND NVL(ml.subinventory_code, 'z') = NVL(x_locator_record.subinventory, 'z')
1449 AND ml.organization_id = x_locator_record.to_organization_id;
1450 /*
1451 ** ELSE If locator control is 2 or 3 and the item is under restricted locator
1452 ** control then do restricted check
1453 */
1454 ELSE
1455 IF (g_asn_debug = 'Y') THEN
1456 asn_debug.put_line('Sub is under restricted locator control');
1457 END IF;
1458
1459 SELECT NVL(MAX(ml.concatenated_segments), 'notfound')
1460 INTO x_locator
1461 FROM mtl_item_locations_kfv ml
1462 WHERE ml.inventory_location_id = x_locator_record.locator_id
1463 AND ( ml.disable_date > SYSDATE
1464 OR ml.disable_date IS NULL)
1465 AND NVL(ml.subinventory_code, 'z') = NVL(x_locator_record.subinventory, 'z')
1466 AND ml.inventory_location_id IN(SELECT secondary_locator
1467 FROM mtl_secondary_locators msl
1468 WHERE msl.inventory_item_id = x_locator_record.item_id
1469 AND msl.organization_id = x_locator_record.to_organization_id
1470 AND msl.subinventory_code = x_locator_record.subinventory);
1471 END IF;
1472 END IF;
1473
1474 IF (x_locator = 'notfound') THEN
1475 IF (g_asn_debug = 'Y') THEN
1476 asn_debug.put_line('In locator Errors');
1477 END IF;
1478
1479 rcv_error_pkg.set_error_message('RCV_ALL_REQUIRED_LOCATOR');
1480 RAISE e_validation_error;
1481 END IF;
1482 EXCEPTION
1483 WHEN e_validation_error THEN
1484 x_locator_record.error_record.error_status := x_error_status;
1485 x_locator_record.error_record.error_message := rcv_error_pkg.get_last_message;
1486
1487 IF x_locator_record.error_record.error_message = 'RCV_ALL_REQUIRED_LOCATOR' THEN
1488 NULL;
1489 END IF;
1490 WHEN OTHERS THEN
1491 x_locator_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1492 rcv_error_pkg.set_sql_error_message('validate_locator', x_progress);
1493 x_locator_record.error_record.error_status := rcv_error_pkg.get_last_message;
1494 END validate_locator;
1495
1496
1497 /*===========================================================================
1498
1499 PROCEDURE NAME: validate_project_locator() --Bug13844195
1500
1501 ===========================================================================*/
1502 PROCEDURE VALIDATE_PROJECT_LOCATOR(X_LOCATOR_RECORD IN OUT NOCOPY RCV_SHIPMENT_LINE_SV.LOCATOR_RECORD_TYPE) IS
1503 X_PROGRESS VARCHAR2(3) := NULL;
1504 X_LOCATOR VARCHAR2(81) := NULL;
1505 X_ERROR_STATUS VARCHAR2(1);
1506 --BUG 13844195
1507 VALID_LOCATOR BOOLEAN := TRUE;
1508 X_PROJECT_ID NUMBER := NULL;
1509 X_TASK_ID NUMBER := NULL;
1510 V_PROJECT_ENABLED NUMBER := 0;
1511 X_ORG_ID NUMBER := 0;
1512 L_PREV_OU_ID FINANCIALS_SYSTEM_PARAMS_ALL.ORG_ID%TYPE;
1513 L_PJM_VALIDATION_OU_ID FINANCIALS_SYSTEM_PARAMS_ALL.ORG_ID%TYPE := NULL;
1514 L_RETURN_STATUS VARCHAR2(1);
1515 L_MODE VARCHAR2(10) := 'SPECIFIC'; --BUG 9742249
1516 L_REQUIRED_FLAG VARCHAR2(10) := 'Y'; --BUG 9742249
1517 BEGIN
1518 X_ERROR_STATUS := RCV_ERROR_PKG.G_RET_STS_ERROR;
1519 X_PROGRESS := '005';
1520
1521 IF X_LOCATOR_RECORD.ERROR_RECORD.ERROR_STATUS NOT IN ('S','W') THEN
1522 RETURN;
1523 END IF;
1524
1525 IF (X_LOCATOR_RECORD.RECEIPT_SOURCE_CODE = 'INVENTORY') THEN
1526 L_MODE := 'ANY';
1527 L_REQUIRED_FLAG := 'N';
1528 END IF;
1529
1530 IF ((X_LOCATOR_RECORD.DESTINATION_TYPE_CODE <> 'INVENTORY') AND
1531 (X_LOCATOR_RECORD.SUBINVENTORY IS NULL)) THEN
1532 X_LOCATOR_RECORD.LOCATOR_ID := NULL;
1533 RETURN;
1534 END IF;
1535
1536 IF (G_ASN_DEBUG = 'Y') THEN
1537 ASN_DEBUG.PUT_LINE('BEFORE GET PROJECT TASK LOCATOR');
1538 END IF;
1539
1540
1541 SELECT NVL(PROJECT_REFERENCE_ENABLED, 0)
1542 INTO V_PROJECT_ENABLED
1543 FROM MTL_PARAMETERS
1544 WHERE ORGANIZATION_ID = X_LOCATOR_RECORD.TO_ORGANIZATION_ID;
1545
1546 IF V_PROJECT_ENABLED = 1 THEN
1547 /*
1548 ** GET THE LOCATOR CONTROLS
1549 */
1550 PO_SUBINVENTORIES_S.GET_LOCATOR_CONTROL(X_LOCATOR_RECORD.TO_ORGANIZATION_ID,
1551 X_LOCATOR_RECORD.SUBINVENTORY,
1552 X_LOCATOR_RECORD.ITEM_ID,
1553 X_LOCATOR_RECORD.SUBINVENTORY_LOCATOR_CONTROL,
1554 X_LOCATOR_RECORD.RESTRICT_LOCATOR_CONTROL);
1555
1556 IF NVL(X_LOCATOR_RECORD.LOCATOR_ID, '0') NOT IN (-1, 0) THEN
1557
1558 ASN_DEBUG.PUT_LINE('X_LOCATOR_RECORD.PO_DISTRIBUTION_ID' ||
1559 X_LOCATOR_RECORD.PO_DISTRIBUTION_ID);
1560 ASN_DEBUG.PUT_LINE('X_LOCATOR_RECORD.SOURCE_DOCUMENT_CODE' ||
1561 X_LOCATOR_RECORD.SOURCE_DOCUMENT_CODE);
1562
1563 IF (X_LOCATOR_RECORD.SOURCE_DOCUMENT_CODE = 'PO' AND
1564 X_LOCATOR_RECORD.PO_DISTRIBUTION_ID IS NOT NULL) THEN
1565
1566 SELECT PROJECT_ID, TASK_ID, ORG_ID -- BUG 13709880
1567 INTO X_PROJECT_ID, X_TASK_ID, L_PJM_VALIDATION_OU_ID -- BUG 13709880
1568 FROM PO_DISTRIBUTIONS_ALL
1569 WHERE PO_DISTRIBUTION_ID = X_LOCATOR_RECORD.PO_DISTRIBUTION_ID;
1570
1571 ASN_DEBUG.PUT_LINE('X_LOCATOR_RECORD.PO_DISTRIBUTION_ID' ||
1572 X_LOCATOR_RECORD.PO_DISTRIBUTION_ID);
1573
1574 END IF;
1575
1576 BEGIN
1577
1578 IF (G_ASN_DEBUG = 'Y') THEN
1579 ASN_DEBUG.PUT_LINE('BEFORE PROJECT ENABLED ');
1580 END IF;
1581
1582
1583 --BUG 14538546
1584
1585 FND_PROFILE.PUT('MFG_ORGANIZATION_ID',
1586 X_LOCATOR_RECORD.TO_ORGANIZATION_ID);
1587
1588
1589 VALID_LOCATOR := INV_PROJECTLOCATOR_PUB.CHECK_PROJECT_REFERENCES(X_LOCATOR_RECORD.TO_ORGANIZATION_ID,
1590 X_LOCATOR_RECORD.LOCATOR_ID,
1591 L_MODE,
1592 L_REQUIRED_FLAG,
1593 X_LOCATOR_RECORD.PROJECT_ID,
1594 X_LOCATOR_RECORD.TASK_ID);
1595
1596
1597
1598 IF (NVL(X_LOCATOR_RECORD.PROJECT_ID, X_PROJECT_ID) <> X_PROJECT_ID OR
1599 NVL(X_LOCATOR_RECORD.TASK_ID, X_TASK_ID) <> X_TASK_ID) THEN
1600 RCV_ERROR_PKG.SET_ERROR_MESSAGE('RCV_ALL_INVALID_LOCATOR');
1601 RCV_ERROR_PKG.SET_TOKEN('LOCATOR',X_LOCATOR_RECORD.LOCATOR);
1602 ASN_DEBUG.PUT_LINE('ROI PROJECT TASK ID <> POD PROJECT TASK ID');
1603 ASN_DEBUG.PUT_LINE('INVALID PROJECT TASK ID FOR PROJECT ENABLED LOCATOR');
1604
1605 RAISE E_VALIDATION_ERROR;
1606
1607 END IF;
1608
1609 IF (NOT VALID_LOCATOR) THEN
1610 RCV_ERROR_PKG.SET_ERROR_MESSAGE('RCV_ALL_INVALID_LOCATOR');
1611 RCV_ERROR_PKG.SET_TOKEN('LOCATOR',X_LOCATOR_RECORD.LOCATOR);
1612 ASN_DEBUG.PUT_LINE('INVALID PROJECT TASK LOCATOR FOR PROJECT ENABLED LOCATOR ');
1613 RAISE E_VALIDATION_ERROR;
1614 ELSE
1615 ASN_DEBUG.PUT_LINE('VALID PROJECT TASK LOCATOR ');
1616 END IF;
1617
1618 EXCEPTION
1619 WHEN E_VALIDATION_ERROR THEN
1620 X_LOCATOR_RECORD.ERROR_RECORD.ERROR_STATUS := X_ERROR_STATUS;
1621 X_LOCATOR_RECORD.ERROR_RECORD.ERROR_MESSAGE := RCV_ERROR_PKG.GET_LAST_MESSAGE;
1622
1623 WHEN OTHERS THEN
1624 X_LOCATOR_RECORD.ERROR_RECORD.ERROR_STATUS := RCV_ERROR_PKG.G_RET_STS_UNEXP_ERROR;
1625 RCV_ERROR_PKG.SET_SQL_ERROR_MESSAGE('VALIDATE_PRO_TASK_LOCATOR',
1626 X_PROGRESS);
1627 X_LOCATOR_RECORD.ERROR_RECORD.ERROR_STATUS := RCV_ERROR_PKG.GET_LAST_MESSAGE;
1628
1629 END;
1630
1631 END IF;
1632
1633 END IF;
1634
1635 END VALIDATE_PROJECT_LOCATOR;
1636
1637
1638
1639 /*===========================================================================
1640
1641 PROCEDURE NAME: validate_country_of_origin()
1642
1643 ===========================================================================*/
1644 PROCEDURE validate_country_of_origin(
1645 x_country_of_origin_record IN OUT NOCOPY rcv_shipment_line_sv.country_of_origin_record_type
1646 ) IS
1647 x_code fnd_territories_vl.territory_code%TYPE := NULL;
1648 x_progress VARCHAR2(3);
1649 x_error_status VARCHAR2(1);
1650 BEGIN
1651 x_error_status := rcv_error_pkg.g_ret_sts_error;
1652
1653 SELECT NVL(MAX(territory_code), 'FF')
1654 INTO x_code
1655 FROM fnd_territories_vl
1656 WHERE territory_code = x_country_of_origin_record.country_of_origin_code;
1657
1658 IF (x_code = 'FF') THEN
1659 rcv_error_pkg.set_error_message('RCV_ASN_ORIGIN_COUNTRY_INVALID');
1660 RAISE e_validation_error;
1661 END IF;
1662 EXCEPTION
1663 WHEN e_validation_error THEN
1664 x_country_of_origin_record.error_record.error_status := x_error_status;
1665 x_country_of_origin_record.error_record.error_message := rcv_error_pkg.get_last_message;
1666
1667 IF x_country_of_origin_record.error_record.error_message = 'RCV_ASN_ORIGIN_COUNTRY_INVALID' THEN
1668 rcv_error_pkg.set_token('COUNTRY_OF_ORIGIN_CODE', x_country_of_origin_record.country_of_origin_code);
1669 rcv_error_pkg.set_token('SHIPMENT', '');
1670 END IF;
1671 WHEN OTHERS THEN
1672 x_country_of_origin_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1673 rcv_error_pkg.set_sql_error_message('validate_country_of_origin', x_progress);
1674 x_country_of_origin_record.error_record.error_status := rcv_error_pkg.get_last_message;
1675 END validate_country_of_origin;
1676
1677 /* <Consigned Inventory Pre-Processor FPI START> */
1678
1679 /*===========================================================================
1680 PROCEDURE NAME: validate_consigned_po()
1681 ===========================================================================*/
1682 PROCEDURE validate_consigned_po(
1683 x_consigned_po_rec IN OUT NOCOPY rcv_shipment_line_sv.po_line_location_id_rtype
1684 ) IS
1685 l_consigned_po_flag po_line_locations_all.consigned_flag%TYPE;
1686 x_error_status VARCHAR2(1);
1687 BEGIN
1688 x_error_status := rcv_error_pkg.g_ret_sts_error;
1689
1690 SELECT consigned_flag
1691 INTO l_consigned_po_flag
1692 FROM po_line_locations
1693 WHERE line_location_id = x_consigned_po_rec.po_line_location_id;
1694
1695 IF (l_consigned_po_flag = 'Y') THEN
1696 IF (g_asn_debug = 'Y') THEN
1697 asn_debug.put_line('in RCVTIS2B.pls' || l_consigned_po_flag);
1698 END IF;
1699
1700 rcv_error_pkg.set_error_message('RCV_REJECT_ASBN_CONSIGNED_PO');
1701 RAISE e_validation_error;
1702 END IF;
1703 EXCEPTION
1704 WHEN e_validation_error THEN
1705 x_consigned_po_rec.error_record.error_status := x_error_status;
1706 x_consigned_po_rec.error_record.error_message := rcv_error_pkg.get_last_message;
1707
1708 IF x_consigned_po_rec.error_record.error_message = 'RCV_REJECT_ASBN_CONSIGNED_PO' THEN
1709 NULL;
1710 END IF;
1711 WHEN OTHERS THEN
1712 x_consigned_po_rec.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1713 rcv_error_pkg.set_sql_error_message('validate_consigned_po', '000');
1714 x_consigned_po_rec.error_record.error_status := rcv_error_pkg.get_last_message;
1715 END validate_consigned_po;
1716
1717 /*===========================================================================
1718 PROCEDURE NAME: validate_consumption_po()
1719 ===========================================================================*/
1720 PROCEDURE validate_consumption_po(
1721 x_consumption_po_rec IN OUT NOCOPY rcv_shipment_line_sv.document_num_record_type
1722 ) IS
1723 l_consumption_po_flag po_headers_all.consigned_consumption_flag%TYPE;
1724 x_error_status VARCHAR2(1);
1725 BEGIN
1726 x_error_status := rcv_error_pkg.g_ret_sts_error;
1727
1728 SELECT consigned_consumption_flag
1729 INTO l_consumption_po_flag
1730 FROM po_headers
1731 WHERE po_header_id = x_consumption_po_rec.po_header_id;
1732
1733 IF (l_consumption_po_flag = 'Y') THEN
1734 IF (g_asn_debug = 'Y') THEN
1735 asn_debug.put_line('in RCVTIS2B.pls' || l_consumption_po_flag);
1736 END IF;
1737
1738 rcv_error_pkg.set_error_message('RCV_REJECT_CONSUMPTION_PO');
1739 RAISE e_validation_error;
1740 END IF;
1741 EXCEPTION
1742 WHEN e_validation_error THEN
1743 x_consumption_po_rec.error_record.error_status := x_error_status;
1744 x_consumption_po_rec.error_record.error_message := rcv_error_pkg.get_last_message;
1745
1746 IF x_consumption_po_rec.error_record.error_message = 'RCV_REJECT_CONSUMPTION_PO' THEN
1747 NULL;
1748 END IF;
1749 WHEN OTHERS THEN
1750 x_consumption_po_rec.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1751 rcv_error_pkg.set_sql_error_message('validate_consumption_po', '000');
1752 x_consumption_po_rec.error_record.error_status := rcv_error_pkg.get_last_message;
1753 END validate_consumption_po;
1754
1755 /*===========================================================================
1756 PROCEDURE NAME: validate_consumption_release()
1757 ===========================================================================*/
1758 PROCEDURE validate_consumption_release(
1759 x_consumption_release_rec IN OUT NOCOPY rcv_shipment_line_sv.release_id_record_type
1760 ) IS
1761 l_consumption_release_flag po_releases_all.consigned_consumption_flag%TYPE;
1762 x_error_status VARCHAR2(1);
1763 BEGIN
1764 x_error_status := rcv_error_pkg.g_ret_sts_error;
1765
1766 SELECT consigned_consumption_flag
1767 INTO l_consumption_release_flag
1768 FROM po_releases
1769 WHERE po_release_id = x_consumption_release_rec.po_release_id;
1770
1771 IF (l_consumption_release_flag = 'Y') THEN
1772 IF (g_asn_debug = 'Y') THEN
1773 asn_debug.put_line('in RCVTIS2B.pls, consumption release' || l_consumption_release_flag);
1774 END IF;
1775
1776 rcv_error_pkg.set_error_message('RCV_REJECT_CONSUMPTION_RELEASE');
1777 RAISE e_validation_error;
1778 END IF;
1779 EXCEPTION
1780 WHEN e_validation_error THEN
1781 x_consumption_release_rec.error_record.error_status := x_error_status;
1782 x_consumption_release_rec.error_record.error_message := rcv_error_pkg.get_last_message;
1783
1784 IF x_consumption_release_rec.error_record.error_message = 'RCV_REJECT_CONSUMPTION_RELEASE' THEN
1785 NULL;
1786 END IF;
1787 WHEN OTHERS THEN
1788 x_consumption_release_rec.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1789 rcv_error_pkg.set_sql_error_message('validate_consumption_release', '000');
1790 x_consumption_release_rec.error_record.error_status := rcv_error_pkg.get_last_message;
1791 END validate_consumption_release;
1792
1793
1794 /*##########################################################################
1795 #
1796 # PROCEDURE
1797 # VALIDATE_SECONDARY_PARAMETERS
1798 #
1799 # DESCRIPTION
1800 #
1801 # For Dual UOM controlled items validate the secondary UOM code and
1802 # Secondary UOM. Derive them if either/both are not specified.
1803 # For Receipt if secondary quantity is there then it will validate it
1804 # (will do the deviation check for it )else it will derive it.
1805 #
1806 # Method of logging errors:
1807 #
1808 # 1) If business logic fails:
1809 # Message is set and exception is raised.
1810 # In exception block return status is set to "Error"
1811 # The last message is retrieved and the program ends there.
1812 #
1813 # 2) If an unexpected failure occours:
1814 # Messge is set.
1815 # Last message is retrieved at that place only.
1816 # return status is set to "Unexpected Error" and control is
1817 # returned back to the calling program.
1818 #
1819 # 3) Messages are also added to error stack.
1820 #
1821 # DESIGN REFERENCES:
1822 # INVCONV.
1823 # http://files.oraclecorp.com/content/AllPublic/Workspaces/
1824 # Inventory%20Convergence-Public/Design/Oracle%20Purchasing/TDD/PO_ROI_TDD.zip
1825 #
1826 #
1827 # MODIFICATION HISTORY
1828 # 23-AUG-2004 Punit Kumar Created
1829 #
1830 #########################################################################*/
1831
1832
1833 PROCEDURE VALIDATE_SECONDARY_PARAMETERS(
1834 p_api_version IN NUMBER ,
1835 p_init_msg_lst IN VARCHAR2 ,
1836 x_att_rec IN OUT NOCOPY RCV_TRANSACTIONS_INTERFACE_SV1.attributes_record_type ,
1837 x_return_status OUT NOCOPY VARCHAR2 ,
1838 x_msg_count OUT NOCOPY NUMBER ,
1839 x_msg_data OUT NOCOPY VARCHAR2 ,
1840 p_transaction_id IN NUMBER /*BUG#10380635 */
1841 )
1842 IS
1843
1844 l_api_name VARCHAR2(30) := 'VALIDATE_SECONDARY_PARAMETERS' ;
1845 l_api_version CONSTANT NUMBER := 1.0 ;
1846
1847 l_return_status VARCHAR2(1) ;
1848 l_msg_data VARCHAR2(3000) ;
1849 l_msg_count NUMBER ;
1850
1851 l_check_dev NUMBER ;
1852 l_TRACKING_QUANTITY_IND VARCHAR2(30) ;
1853 l_secondary_default_ind VARCHAR2(10) ;
1854 l_secondary_uom_code VARCHAR2(3) ;
1855 l_secondary_unit_of_measure VARCHAR2(25) ;
1856 l_progress VARCHAR2(10) := '0000' ;
1857 l_lot_number VARCHAR2(80) ; /*BUG#10380635 */
1858 l_conv_exist NUMBER :=0 ; --Bug13934928 initialized --Bug#13401431
1859
1860 /*Bug 13938193*/
1861 l_sec_lot_dev_tqty NUMBER :=0 ;
1862 l_sec_lot_spe_qty NUMBER :=0 ;
1863 l_sec_lot_spe_tqty NUMBER :=0 ;
1864 /*End Bug 13938193*/
1865
1866 CURSOR lot_num_cur(l_transaction_id NUMBER) IS
1867 SELECT LOT_NUMBER FROM mtl_transaction_lots_interface WHERE product_transaction_id=l_transaction_id;
1868
1869 /*Bug 13938193*/
1870 CURSOR lot_num_cur1(l_transaction_id NUMBER) IS
1871 SELECT transaction_quantity, LOT_NUMBER ,SECONDARY_TRANSACTION_QUANTITY FROM mtl_transaction_lots_interface WHERE product_transaction_id=l_transaction_id;
1872 /*End Bug 13938193*/
1873
1874 BEGIN
1875 l_progress :='0001';
1876 IF (g_asn_debug = 'Y') THEN
1877 asn_debug.put_line('VALIDATE_SECONDARY_PARAMETERS: Entering' || l_progress);
1878 END IF;
1879
1880
1881 -- Standard call to check for call compatibility.
1882 IF NOT fnd_api.compatible_api_call(
1883 l_api_version,
1884 p_api_version,
1885 l_api_name,
1886 'PO_VALIDATE_PARAMETERS'
1887 ) THEN
1888 l_progress :='0002';
1889 IF (g_asn_debug = 'Y') THEN
1890 asn_debug.put_line('FND_API not compatible rcv_transactions_interface_sv1.VALIDATE_SECONDARY_PARAMETERS'||l_progress);
1891 END IF;
1892 END IF;
1893
1894 -- Initialize message list if p_init_msg_list is set to TRUE.
1895 IF fnd_api.to_boolean(p_init_msg_lst) THEN
1896 fnd_msg_pub.initialize;
1897 END IF;
1898
1899 --Initialize the return status
1900 x_return_status := FND_API.G_RET_STS_SUCCESS;
1901 x_att_rec.error_record.error_status := FND_API.G_RET_STS_SUCCESS;
1902 x_att_rec.error_record.error_message := NULL;
1903
1904 /*BUG#10380635 : fetching lot number for current transaction--starts here*/
1905
1906
1907 OPEN lot_num_cur(p_transaction_id);
1908 if lot_num_cur%NOTFOUND then
1909 l_lot_number := NULL;
1910 else
1911 FETCH lot_num_cur INTO l_lot_number;
1912 end if;
1913 CLOSE lot_num_cur;
1914 /*BUG#10380635---ends here */
1915
1916
1917
1918 /*Defaulting of origination type to 'Purchasing' to be done
1919 in INV_RCV_INTEGRATION_PVT.MOVE_LOT_SERIAL_INFO */
1920
1921
1922 l_progress := '001';
1923
1924 BEGIN
1925 l_progress :='0003';
1926 IF (g_asn_debug = 'Y') THEN
1927 asn_debug.put_line('Inside rcv_transactions_interface_sv1.VALIDATE_SECONDARY_PARAMETERS:' || l_progress);
1928 END IF;
1929
1930 -------Checking if the item is dual UOM controlled. If not then Return .
1931 SELECT tracking_quantity_ind , secondary_default_ind
1932 INTO l_TRACKING_QUANTITY_IND ,l_secondary_default_ind
1933 FROM mtl_system_items_b
1934 WHERE INVENTORY_ITEM_ID = x_att_rec.inventory_item_id
1935 AND ORGANIZATION_ID = x_att_rec.to_organization_id;
1936
1937 l_progress :='0004';
1938 IF (g_asn_debug = 'Y') THEN
1939 asn_debug.put_line('Value of tracking_quantity_ind is ' ||l_TRACKING_QUANTITY_IND ||':'||l_progress);
1940 END IF;
1941
1942
1943 EXCEPTION
1944 WHEN OTHERS THEN
1945 l_progress :='0005';
1946 IF g_asn_debug = 'Y' THEN
1947 asn_debug.put_line('Dual UOM check failed:' || l_progress);
1948 END IF;
1949
1950 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_unexp_error;
1951 rcv_error_pkg.set_sql_error_message('Unexpected Exception in validate_secondary_parameters', l_progress);
1952 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
1953
1954 RETURN;
1955
1956 END;
1957
1958 /*For non dual items.*/
1959 IF l_TRACKING_QUANTITY_IND <> 'PS' THEN
1960 l_progress :='0006';
1961 IF (g_asn_debug = 'Y') THEN
1962 asn_debug.put_line('Item is not dual UOM controlled.:'|| l_progress);
1963 END IF;
1964
1965 /*Error out if secondary parameters are specified and item is not dual uom controlled*/
1966 IF x_att_rec.secondary_uom_code IS NOT NULL THEN
1967 /*
1968 rcv_error_pkg.set_error_message('PO_SECONDARY_UOM_NOT_REQUIRED');
1969 RAISE e_validation_error;
1970 */
1971 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_error;
1972 rcv_error_pkg.set_error_message('PO_SECONDARY_UOM_NOT_REQUIRED');
1973 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
1974
1975 RETURN;
1976
1977
1978 ELSIF x_att_rec.secondary_unit_of_measure IS NOT NULL THEN
1979 /*
1980 rcv_error_pkg.set_error_message('PO_SECONDARY_UOM_NOT_REQUIRED');
1981 RAISE e_validation_error;
1982 */
1983 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_error;
1984 rcv_error_pkg.set_error_message('PO_SECONDARY_UOM_NOT_REQUIRED');
1985 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
1986
1987 RETURN;
1988
1989
1990 ELSIF x_att_rec.secondary_quantity IS NOT NULL THEN
1991 /*
1992 rcv_error_pkg.set_error_message('PO_SECONDARY_QTY_NOT_REQUIRED');
1993 RAISE e_validation_error;
1994 */
1995 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_error;
1996 rcv_error_pkg.set_error_message('PO_SECONDARY_QTY_NOT_REQUIRED');
1997 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
1998
1999 RETURN;
2000
2001
2002 ELSE
2003 /* Having this return statement so that program exits as successful if no secondary parameter has been input for a non dual item*/
2004 x_att_rec.error_record.error_status := FND_API.G_RET_STS_SUCCESS;
2005 RETURN;
2006 END IF;
2007 END IF;
2008
2009 /* If it is dual UOM controlled then only proceed */
2010
2011
2012 IF x_att_rec.secondary_uom_code IS NOT NULL AND x_att_rec.secondary_unit_of_measure IS NULL THEN
2013
2014 l_progress :='0007';
2015
2016 BEGIN
2017
2018 SELECT SECONDARY_UOM_CODE
2019 INTO l_secondary_uom_code
2020 FROM MTL_SYSTEM_ITEMS_B
2021 WHERE MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID = x_att_rec.inventory_item_id
2022 AND MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID = x_att_rec.to_organization_id;
2023
2024 IF g_asn_debug = 'Y' THEN
2025 asn_debug.put_line('Secondary uom code derived in VALIDATE_SECONDARY_PARAMETERS is ' ||l_secondary_uom_code||':'||l_progress );
2026 END IF;
2027
2028 IF l_secondary_uom_code <> x_att_rec.secondary_uom_code THEN
2029 l_progress :='0008JN';
2030 IF g_asn_debug = 'Y' THEN
2031 asn_debug.put_line('Secondary uom code validation failed in VALIDATE_SECONDARY_PARAMETERS:' || l_progress);
2032 END IF;
2033
2034 /*Log error into po_interface_error*/
2035 /*
2036 rcv_error_pkg.set_error_message('PO_INCORRECT_SECONDARY_UOM');
2037 RAISE e_validation_error;
2038 */
2039 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_error;
2040 rcv_error_pkg.set_error_message('PO_INCORRECT_SECONDARY_UOM');
2041 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
2042
2043 RETURN;
2044
2045 ELSE
2046 Select UNIT_OF_MEASURE
2047 INTO x_att_rec.secondary_unit_of_measure
2048 FROM mtl_units_of_measure
2049 WHERE uom_code = x_att_rec.secondary_uom_code;
2050
2051 l_progress :='0009';
2052 IF g_asn_debug = 'Y' THEN
2053 asn_debug.put_line('Secondary unit of measure derived in VALIDATE_SECONDARY_PARAMETERS is ' ||x_att_rec.secondary_unit_of_measure||':'||l_progress );
2054 END IF;
2055 END IF;
2056
2057 EXCEPTION
2058 WHEN OTHERS THEN
2059 IF g_asn_debug = 'Y' THEN
2060 asn_debug.put_line('SQL in validate_secondary_parameters failed:' || l_progress);
2061 END IF;
2062 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_unexp_error;
2063 rcv_error_pkg.set_sql_error_message('Unexpected Exception:validate_secondary_parameters', l_progress);
2064 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
2065
2066 RETURN;
2067 END;
2068
2069 ELSIF x_att_rec.secondary_uom_code IS NULL AND x_att_rec.secondary_unit_of_measure IS NOT NULL THEN
2070
2071 l_progress :='0010';
2072
2073 BEGIN
2074
2075 SELECT SECONDARY_UOM_CODE
2076 INTO x_att_rec.secondary_uom_code
2077 FROM MTL_SYSTEM_ITEMS_B
2078 WHERE MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID=x_att_rec.inventory_item_id
2079 AND MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID=x_att_rec.to_organization_id;
2080
2081 IF g_asn_debug = 'Y' THEN
2082 asn_debug.put_line('Secondary uom code derived in VALIDATE_SECONDARY_PARAMETERS is ' ||x_att_rec.secondary_uom_code ||':'||l_progress);
2083 END IF;
2084
2085 SELECT UNIT_OF_MEASURE
2086 INTO l_secondary_unit_of_measure
2087 FROM mtl_units_of_measure
2088 WHERE uom_code = x_att_rec.secondary_uom_code;
2089
2090 l_progress :='0011';
2091 IF g_asn_debug = 'Y' THEN
2092 asn_debug.put_line('Secondary unit of measure derived in VALIDATE_SECONDARY_PARAMETERS is ' ||l_secondary_unit_of_measure||':'|| l_progress );
2093 END IF;
2094
2095 IF l_secondary_unit_of_measure <> x_att_rec.secondary_unit_of_measure THEN
2096
2097 l_progress :='0012';
2098 IF g_asn_debug = 'Y' THEN
2099 asn_debug.put_line('Secondary unit of measure validation failed in VALIDATE_SECONDARY_PARAMETERS'||':'||l_progress);
2100 END IF;
2101
2102 /*Log error into po_interface_error*/
2103 /*
2104 rcv_error_pkg.set_error_message('PO_INCORRECT_SECONDARY_UOM');
2105 RAISE e_validation_error;
2106 */
2107 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_error;
2108 rcv_error_pkg.set_error_message('PO_INCORRECT_SECONDARY_UOM');
2109 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
2110
2111 RETURN;
2112
2113 END IF;
2114
2115 EXCEPTION
2116 WHEN OTHERS THEN
2117 IF g_asn_debug = 'Y' THEN
2118 asn_debug.put_line('SQL in validate_secondary_parameters failed:' || l_progress);
2119 END IF;
2120 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_unexp_error;
2121 rcv_error_pkg.set_sql_error_message('Unexpected Exception:validate_secondary_parameters', l_progress);
2122 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
2123
2124 RETURN;
2125 END;
2126
2127 ELSIF x_att_rec.secondary_uom_code IS NULL AND x_att_rec.secondary_unit_of_measure IS NULL THEN
2128
2129 l_progress :='0013';
2130
2131 BEGIN
2132
2133 SELECT SECONDARY_UOM_CODE
2134 INTO x_att_rec.secondary_uom_code
2135 FROM MTL_SYSTEM_ITEMS_B
2136 WHERE MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID=x_att_rec.inventory_item_id
2137 AND MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID=x_att_rec.to_organization_id;
2138
2139 IF g_asn_debug = 'Y' THEN
2140 asn_debug.put_line('Secondary uom code derived in VALIDATE_SECONDARY_PARAMETERS is ' ||x_att_rec.secondary_uom_code||':'||l_progress );
2141 END IF;
2142
2143 Select UNIT_OF_MEASURE
2144 INTO x_att_rec.secondary_unit_of_measure
2145 FROM mtl_units_of_measure
2146 WHERE uom_code = x_att_rec.secondary_uom_code;
2147
2148 l_progress :='0014';
2149 IF g_asn_debug = 'Y' THEN
2150 asn_debug.put_line('Secondary unit of measure derived in VALIDATE_SECONDARY_PARAMETERS is ' ||x_att_rec.secondary_unit_of_measure ||':'||l_progress);
2151 END IF;
2152
2153 EXCEPTION
2154 WHEN OTHERS THEN
2155 IF g_asn_debug = 'Y' THEN
2156 asn_debug.put_line('SQL in validate_secondary_parameters failed:' || l_progress);
2157 END IF;
2158 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_unexp_error;
2159 rcv_error_pkg.set_sql_error_message('Unexpected Exception:validate_secondary_parameters', l_progress);
2160 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
2161
2162 RETURN;
2163 END;
2164
2165 ELSIF x_att_rec.secondary_uom_code IS NOT NULL AND x_att_rec.secondary_unit_of_measure IS NOT NULL THEN
2166
2167 l_progress :='0015';
2168
2169 BEGIN
2170
2171 SELECT SECONDARY_UOM_CODE
2172 INTO l_secondary_uom_code
2173 FROM MTL_SYSTEM_ITEMS_B
2174 WHERE MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID=x_att_rec.inventory_item_id
2175 AND MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID=x_att_rec.to_organization_id;
2176
2177 IF g_asn_debug = 'Y' THEN
2178 asn_debug.put_line('Secondary uom code derived in VALIDATE_SECONDARY_PARAMETERS is ' ||l_secondary_uom_code||':'||l_progress );
2179 END IF;
2180
2181 IF l_secondary_uom_code <>x_att_rec.secondary_uom_code THEN
2182 l_progress :='0016';
2183 IF g_asn_debug = 'Y' THEN
2184 asn_debug.put_line('Secondary uom code validation failed in VALIDATE_SECONDARY_PARAMETERS:'||l_progress);
2185 END IF;
2186
2187 /*Log error into po_interface_error*/
2188 /*
2189 rcv_error_pkg.set_error_message('PO_INCORRECT_SECONDARY_UOM');
2190 RAISE e_validation_error;
2191 */
2192 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_error;
2193 rcv_error_pkg.set_error_message('PO_INCORRECT_SECONDARY_UOM');
2194 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
2195
2196 RETURN;
2197
2198
2199 ELSE
2200 /*Secondary uom code matches so now validate the secondary unit of measure*/
2201 l_progress :='0017';
2202
2203 SELECT UNIT_OF_MEASURE
2204 INTO l_secondary_unit_of_measure
2205 FROM mtl_units_of_measure
2206 WHERE uom_code = x_att_rec.secondary_uom_code;
2207
2208 l_progress :='0171';
2209 IF g_asn_debug = 'Y' THEN
2210 asn_debug.put_line('Secondary unit of measure derived in VALIDATE_SECONDARY_PARAMETERS is ' ||l_secondary_unit_of_measure||':'|| l_progress );
2211 END IF;
2212
2213 IF l_secondary_unit_of_measure <> x_att_rec.secondary_unit_of_measure THEN
2214
2215 l_progress :='0172';
2216
2217 IF g_asn_debug = 'Y' THEN
2218 asn_debug.put_line('Secondary unit of measure validation failed in VALIDATE_SECONDARY_PARAMETERS'||':'||l_progress);
2219 END IF;
2220
2221 /*Log error into po_interface_error*/
2222 /*
2223 rcv_error_pkg.set_error_message('PO_INCORRECT_SECONDARY_UOM');
2224 RAISE e_validation_error;
2225 */
2226 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_error;
2227 rcv_error_pkg.set_error_message('PO_INCORRECT_SECONDARY_UOM');
2228 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
2229
2230 RETURN;
2231
2232
2233 END IF; -----IF l_secondary_unit_of_measure <> x_att_rec.secondary_unit_of_measure THEN
2234
2235 END IF; ------------IF l_secondary_uom_code <>x_att_rec.secondary_uom_code THEN
2236
2237 EXCEPTION
2238 WHEN OTHERS THEN
2239 IF g_asn_debug = 'Y' THEN
2240 asn_debug.put_line('SQL in validate_secondary_parameters failed:' || l_progress);
2241 END IF;
2242 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_unexp_error;
2243 rcv_error_pkg.set_sql_error_message('Unexpected Exception:validate_secondary_parameters', l_progress);
2244 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
2245
2246 RETURN;
2247 END;
2248
2249 END IF; --------IF x_att_rec.secondary_uom_code IS NOT NULL AND x_att_rec.secondary_unit_of_measure IS NULL THEN
2250
2251 IF l_lot_number is null then --Bug 13938193 add this filter
2252
2253 --BUG#13401431 added the l_conv_exist to check lot specific conversion is existing or not.
2254 --if its existing then by pass this check. as already deviation is checked.
2255 IF x_att_rec.secondary_quantity IS NOT NULL AND l_secondary_default_ind IN ('D','N')AND l_conv_exist <>1 THEN
2256 l_progress :='0018';
2257 IF g_asn_debug = 'Y' THEN
2258 asn_debug.put_line('Before calling within deviation check in VALIDATE_SECONDARY_PARAMETERS:' ||l_progress);
2259 END IF;
2260
2261 l_check_dev := INV_CONVERT.Within_deviation(
2262 p_organization_id => X_ATT_REC.to_organization_id ,
2263 p_inventory_item_id => X_ATT_REC.inventory_item_id ,
2264 p_lot_number => NULL , --as no lot is defined
2265 p_precision => 5 ,
2266 p_quantity => X_ATT_REC.transaction_quantity ,
2267 p_uom_code1 => NULL ,
2268 p_quantity2 => X_ATT_REC.secondary_quantity ,
2269 p_uom_code2 => NULL ,
2270 p_unit_of_measure1 => X_ATT_REC.transaction_unit_of_measure ,
2271 p_unit_of_measure2 => X_ATT_REC.Secondary_unit_of_measure
2272 );
2273
2274 /* Returns 1 for True and 0 for False*/
2275
2276 IF l_check_dev=0 THEN
2277 l_progress :='0019';
2278 IF g_asn_debug = 'Y' THEN
2279 asn_debug.put_line('within deviation check failed in VALIDATE_SECONDARY_PARAMETERS:' || l_progress);
2280 END IF;
2281 /*Log error into po_interface_error */
2282 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_error;
2283 rcv_error_pkg.set_error_message('PO_SRCE_ORG_OUT_OF_DEV');
2284 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
2285
2286 RETURN;
2287 END IF;
2288
2289 l_progress :='0020';
2290 IF g_asn_debug = 'Y' THEN
2291 asn_debug.put_line('After successfully calling within deviation check in VALIDATE_SECONDARY_PARAMETERS:' || l_progress);
2292 END IF;
2293
2294
2295 ELSE -------- x_att_rec.secondary_quantity IS NULL OR l_secondary_default_ind = 'F' THEN
2296 /*Calculate the secondary quantity if it is null or the item is of fixed type.
2297 As for fixed type we do not check deviation but the secondary quantity has
2298 to be fixed according to a predefined conversion*/
2299
2300 l_progress :='0021';
2301
2302 IF g_asn_debug = 'Y' THEN
2303 asn_debug.put_line('Befordesc inv_e calling INV_CONVERT.Inv_um_convert for fetching secondary quantity in VALIDATE_SECONDARY_PARAMETERS:'||l_progress);
2304 END IF;
2305
2306 x_att_rec.secondary_quantity:= INV_CONVERT.Inv_um_convert (
2307 item_id => x_att_rec.inventory_item_id ,
2308 lot_number => NULL ,
2309 organization_id => x_att_rec.to_organization_id ,
2310 precision => 5 ,
2311 from_quantity => x_att_rec.transaction_quantity ,
2312 from_unit => NULL ,
2313 to_unit => NULL ,
2314 from_name => x_att_rec.transaction_unit_of_measure ,
2315 to_name => x_att_rec.secondary_unit_of_measure
2316 );
2317
2318
2319
2320 IF x_att_rec.secondary_quantity = -99999 THEN
2321 l_progress :='0022';
2322 IF g_asn_debug = 'Y' THEN
2323 asn_debug.put_line('fetch secondary quantity failed in VALIDATE_SECONDARY_PARAMETERS:' || l_progress);
2324 END IF;
2325 /*Log error into po_interface_error */
2326 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_unexp_error;
2327 rcv_error_pkg.set_sql_error_message('Unexpected exception :fetch secondary quantity failed in validate_secondary_parameters', l_progress);
2328 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
2329
2330 RETURN;
2331 END IF;
2332
2333 l_progress :='0023';
2334 IF g_asn_debug = 'Y' THEN
2335 asn_debug.put_line('After successfully calling INV_CONVERT.Inv_um_convert for fetching secondary quantity in VALIDATE_SECONDARY_PARAMETERS:' || l_progress);
2336 asn_debug.put_line('value of secondary quantity derived by INV_CONVERT.Inv_um_convert is:'|| x_att_rec.secondary_quantity);
2337 END IF;
2338
2339
2340 END IF;-----IF x_att_rec.secondary_quantity IS NOT NULL AND l_secondary_default_ind IN ('D','N') THEN
2341
2342 --Bug 13938193 add below part to handle multiple lots in one transaction of RTI.
2343 ELSE
2344 FOR lot_rec1 IN lot_num_cur1(p_transaction_id)
2345 LOOP
2346 BEGIN
2347 SELECT 1 INTO l_conv_exist
2348 FROM mtl_lot_uom_class_conversions
2349 WHERE organization_id = x_att_rec.to_organization_id
2350 AND lot_number = lot_rec1.LOT_NUMBER
2351 AND inventory_item_id = x_att_rec.inventory_item_id
2352 AND FROM_UNIT_OF_MEASURE=X_ATT_REC.transaction_unit_of_measure
2353 AND TO_UNIT_OF_MEASURE=x_att_rec.Secondary_UNIT_OF_MEASURE;
2354 EXCEPTION
2355 WHEN No_Data_Found THEN
2356 l_conv_exist:=0;
2357 END ;
2358
2359 IF lot_rec1.secondary_transaction_quantity IS NOT NULL AND l_secondary_default_ind IN ('D','N')AND l_conv_exist <>1 THEN
2360 l_progress :='0018';
2361 IF g_asn_debug = 'Y' THEN
2362 asn_debug.put_line('Before calling within deviation check in VALIDATE_SECONDARY_PARAMETERS:' ||l_progress);
2363 END IF;
2364
2365 l_check_dev := INV_CONVERT.Within_deviation(
2366 p_organization_id => X_ATT_REC.to_organization_id ,
2367 p_inventory_item_id => X_ATT_REC.inventory_item_id ,
2368 /* p_lot_number => NULL as no lot is defined */
2369 p_lot_number =>lot_rec1.LOT_NUMBER , /*BUG#13938193*/
2370 p_precision => 5 ,
2371 p_quantity => lot_rec1.transaction_quantity ,
2372 p_uom_code1 => NULL ,
2373 p_quantity2 => lot_rec1.secondary_transaction_quantity,
2374 p_uom_code2 => NULL ,
2375 p_unit_of_measure1 => X_ATT_REC.transaction_unit_of_measure ,
2376 p_unit_of_measure2 => X_ATT_REC.Secondary_unit_of_measure
2377 );
2378
2379
2380
2381 /* Returns 1 for True and 0 for False*/
2382
2383 IF l_check_dev=0 THEN
2384 l_progress :='0019';
2385 IF g_asn_debug = 'Y' THEN
2386 asn_debug.put_line('within deviation check failed in VALIDATE_SECONDARY_PARAMETERS:' || l_progress);
2387 END IF;
2388 /*Log error into po_interface_error */
2389 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_error;
2390 rcv_error_pkg.set_error_message('PO_SRCE_ORG_OUT_OF_DEV');
2391 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
2392
2393 RETURN;
2394 END IF;
2395
2396 l_sec_lot_dev_tqty := lot_rec1.secondary_transaction_quantity + l_sec_lot_dev_tqty ;
2397
2398 l_progress :='0020';
2399 IF g_asn_debug = 'Y' THEN
2400 asn_debug.put_line('After successfully calling within deviation check in VALIDATE_SECONDARY_PARAMETERS:' || l_progress);
2401 END IF;
2402
2403
2404 ELSE -------- x_att_rec.secondary_quantity IS NULL OR l_secondary_default_ind = 'F' THEN
2405 /*Calculate the secondary quantity if it is null or the item is of fixed type.
2406 As for fixed type we do not check deviation but the secondary quantity has
2407 to be fixed according to a predefined conversion*/
2408
2409 l_progress :='0021';
2410
2411 IF g_asn_debug = 'Y' THEN
2412 asn_debug.put_line('Befordesc inv_e calling INV_CONVERT.Inv_um_convert for fetching secondary quantity in VALIDATE_SECONDARY_PARAMETERS:'||l_progress);
2413 END IF;
2414
2415
2416 l_sec_lot_spe_qty := INV_CONVERT.Inv_um_convert (
2417 item_id => X_ATT_REC.inventory_item_id ,
2418 /* lot_number => NULL , */
2419 lot_number =>lot_rec1.LOT_NUMBER ,
2420 organization_id => X_ATT_REC.to_organization_id ,
2421 precision => 5 ,
2422 from_quantity => lot_rec1.transaction_quantity ,
2423 from_unit => NULL ,
2424 to_unit => NULL ,
2425 from_name => X_ATT_REC.transaction_unit_of_measure ,
2426 to_name => X_ATT_REC.secondary_unit_of_measure
2427 );
2428
2429
2430 l_sec_lot_spe_tqty := l_sec_lot_spe_qty + l_sec_lot_spe_tqty;
2431
2432
2433
2434 END IF;-----IF x_att_rec.secondary_quantity IS NOT NULL AND l_secondary_default_ind IN ('D','N') THEN
2435
2436
2437 END LOOP ; --FOR lot_rec1 IN lot_num_cur1(p_transaction_id)
2438
2439 x_att_rec.secondary_quantity := l_sec_lot_dev_tqty + l_sec_lot_spe_tqty;
2440
2441 --End Bug 13938193
2442 END IF ;-- IF l_lot_number is null
2443
2444
2445 l_progress :='0024';
2446 IF g_asn_debug = 'Y' THEN
2447 asn_debug.put_line('End of VALIDATE_SECONDARY_PARAMETERS:' || l_progress);
2448 END IF;
2449
2450
2451 EXCEPTION
2452 WHEN e_validation_error THEN
2453 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_error;
2454 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
2455
2456 WHEN OTHERS THEN
2457 x_att_rec.error_record.error_status := fnd_api.g_ret_sts_unexp_error;
2458 rcv_error_pkg.set_sql_error_message('Unexpected exception:validate_secondary_parameters', l_progress);
2459 x_att_rec.error_record.error_message := rcv_error_pkg.get_last_message;
2460
2461
2462 END VALIDATE_SECONDARY_PARAMETERS;
2463
2464 /* <Consigned Inventory Pre-Processor FPI END> */
2465 END rcv_transactions_interface_sv1;
2466
2467
[Home] [Help]