1 PACKAGE BODY rcv_rma_transactions
2 /* $Header: RCVRMATB.pls 120.7.12000000.4 2007/08/15 19:47:10 vthevark ship $*/
3 AS
4 g_asn_debug VARCHAR2(1) := NVL(fnd_profile.VALUE('RCV_DEBUG_MODE'), 'N');
5 x_progress VARCHAR2(3);
6 TYPE t_used_rma_line_amounts is table of number index by binary_integer;
7 g_used_rma_line_amounts t_used_rma_line_amounts;
8
9 CURSOR default_rma(
10 v_line_id NUMBER
11 ) IS
12 SELECT oel.line_number oe_order_line_num,
13 msi.description item_description,
14 oel.sold_to_org_id customer_id,
15 oel.ship_to_org_id customer_site_id,
16 oel.ship_to_org_id from_organization_id,
17 oel.ship_from_org_id to_organization_id,
18 oel.unit_selling_price unit_price,
19 oeh.transactional_curr_code currency_code,
20 oeh.conversion_type_code currency_conversion_type,
21 oeh.conversion_rate_date currency_conversion_date,
22 oeh.conversion_rate currency_conversion_rate,
23 oel.subinventory subinventory,
24 oel.ship_from_org_id deliver_to_location_id
25 FROM oe_order_headers oeh,
26 oe_order_lines oel,
27 mtl_system_items msi
28 WHERE oel.line_id = v_line_id
29 AND oel.header_id = oeh.header_id
30 AND oel.booked_flag = 'Y'
31 AND oel.ordered_quantity > NVL(oel.shipped_quantity, 0)
32 AND oeh.open_flag = 'Y'
33 AND oel.line_category_code = 'RETURN'
34 AND oel.open_flag = 'Y'
35 AND oel.flow_status_code = 'AWAITING_RETURN'
36 AND msi.organization_id = oe_sys_parameters.VALUE('MASTER_ORGANIZATION_ID', oel.org_id)
37 AND msi.inventory_item_id = oel.inventory_item_id;
38
39 -- specs for package level procedures
40 -- helpers for derive_rma_line
41 PROCEDURE derive_order_header_info(
42 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
43 n IN BINARY_INTEGER
44 );
45
46 PROCEDURE derive_order_line_info(
47 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
48 n IN BINARY_INTEGER
49 );
50
51 PROCEDURE derive_document_line_info(
52 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
53 n IN BINARY_INTEGER
54 );
55
56 PROCEDURE derive_customer_info(
57 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
58 n IN BINARY_INTEGER
59 );
60
61 PROCEDURE derive_transit_org_info(
62 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
63 n IN BINARY_INTEGER
64 );
65
66 PROCEDURE derive_uom_info(
67 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
68 n IN BINARY_INTEGER
69 );
70
71 PROCEDURE derive_org_info(
72 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
73 n IN BINARY_INTEGER,
74 x_header_record IN rcv_roi_preprocessor.header_rec_type
75 );
76
77 PROCEDURE derive_deliver_to_info(
78 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
79 n IN BINARY_INTEGER
80 );
81
82 PROCEDURE derive_auto_transact_info(
83 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
84 n IN BINARY_INTEGER
85 );
86
87 PROCEDURE explode_line_quantity(
88 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
89 n IN OUT NOCOPY BINARY_INTEGER,
90 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
91 x_header_record IN rcv_roi_preprocessor.header_rec_type
92 );
93
94 -- helpers for default_rma_line
95 PROCEDURE default_source_info(
96 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
97 n IN BINARY_INTEGER,
98 x_header_id IN rcv_headers_interface.header_interface_id%TYPE
99 );
100
101 PROCEDURE default_destination_info(
102 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
103 n IN BINARY_INTEGER
104 );
105
106 PROCEDURE default_item_info(
107 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
108 n IN BINARY_INTEGER
109 );
110
111 PROCEDURE default_transaction_info(
112 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
113 n IN BINARY_INTEGER
114 );
115
116 PROCEDURE default_processing_info(
117 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
118 n IN BINARY_INTEGER
119 );
120
121 PROCEDURE default_from_header(
122 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
123 n IN BINARY_INTEGER,
124 x_header_record IN rcv_roi_preprocessor.header_rec_type
125 );
126
127 PROCEDURE default_from_rma(
128 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
129 n IN BINARY_INTEGER
130 );
131
132 PROCEDURE default_customer_header(
133 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
134 n IN BINARY_INTEGER,
135 x_header_record IN rcv_roi_preprocessor.header_rec_type
136 );
137
138 PROCEDURE default_customer_site_header(
139 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
140 n IN BINARY_INTEGER,
141 x_header_record IN rcv_roi_preprocessor.header_rec_type
142 );
143
144 PROCEDURE default_from_org_header(
145 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
146 n IN BINARY_INTEGER,
147 x_header_record IN rcv_roi_preprocessor.header_rec_type
148 );
149
150 PROCEDURE default_ship_to_header(
151 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
152 n IN BINARY_INTEGER,
153 x_header_record IN rcv_roi_preprocessor.header_rec_type
154 );
155
156 PROCEDURE default_currency_info_header(
157 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
158 n IN BINARY_INTEGER,
159 x_header_record IN rcv_roi_preprocessor.header_rec_type
160 );
161
162 PROCEDURE default_shipment_num_header(
163 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
164 n IN BINARY_INTEGER,
165 x_header_record IN rcv_roi_preprocessor.header_rec_type
166 );
167
168 PROCEDURE default_freight_carrier_header(
169 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
170 n IN BINARY_INTEGER,
171 x_header_record IN rcv_roi_preprocessor.header_rec_type
172 );
173
174 PROCEDURE default_bill_of_lading_header(
175 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
176 n IN BINARY_INTEGER,
177 x_header_record IN rcv_roi_preprocessor.header_rec_type
178 );
179
180 PROCEDURE default_packing_slip_header(
181 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
182 n IN BINARY_INTEGER,
183 x_header_record IN rcv_roi_preprocessor.header_rec_type
184 );
185
186 PROCEDURE default_ship_date_header(
187 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
188 n IN BINARY_INTEGER,
189 x_header_record IN rcv_roi_preprocessor.header_rec_type
190 );
191
192 PROCEDURE default_receipt_date_header(
193 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
194 n IN BINARY_INTEGER,
195 x_header_record IN rcv_roi_preprocessor.header_rec_type
196 );
197
198 PROCEDURE default_num_containers_header(
199 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
200 n IN BINARY_INTEGER,
201 x_header_record IN rcv_roi_preprocessor.header_rec_type
202 );
203
204 PROCEDURE default_waybill_header(
205 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
206 n IN BINARY_INTEGER,
207 x_header_record IN rcv_roi_preprocessor.header_rec_type
208 );
209
210 PROCEDURE default_tax_name_header(
211 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
212 n IN BINARY_INTEGER,
213 x_header_record IN rcv_roi_preprocessor.header_rec_type
214 );
215
216 PROCEDURE default_routing_info(
217 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
218 n IN BINARY_INTEGER
219 );
220
221 -- helpers for validate_rma_line
222 PROCEDURE validate_txn_date(
223 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
224 n IN BINARY_INTEGER
225 );
226
227 PROCEDURE validate_qty_invoiced(
228 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
229 n IN BINARY_INTEGER
230 );
231
232 PROCEDURE validate_uom_info(
233 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
234 n IN BINARY_INTEGER
235 );
236
237 PROCEDURE validate_item_info(
238 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
239 n IN BINARY_INTEGER
240 );
241
242 PROCEDURE validate_freight_carrier_info(
243 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
244 n IN BINARY_INTEGER
245 );
246
247 PROCEDURE validate_destination_type(
248 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
249 n IN BINARY_INTEGER
250 );
251
252 PROCEDURE validate_tax_name(
253 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
254 n IN BINARY_INTEGER
255 );
256
257 PROCEDURE validate_country_of_origin(
258 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
259 n IN BINARY_INTEGER
260 );
261
262 PROCEDURE validate_ref_integrity(
263 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
264 n IN BINARY_INTEGER
265 );
266
267 PROCEDURE validate_uom(
268 x_uom_record IN OUT NOCOPY rcv_shipment_line_sv.quantity_shipped_record_type
269 );
270
271 PROCEDURE validate_item(
272 x_item_id_record IN OUT NOCOPY rcv_shipment_line_sv.item_id_record_type,
273 x_auto_transact_code IN rcv_transactions_interface.auto_transact_code%TYPE
274 );
275
276 -- main public procedures
277 PROCEDURE derive_rma_line(
278 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
279 n IN OUT NOCOPY BINARY_INTEGER,
280 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
281 x_header_record IN rcv_roi_preprocessor.header_rec_type
282 ) IS
283 BEGIN
284 IF (g_asn_debug = 'Y') THEN
285 asn_debug.put_line('Enter derive_rma_line');
286 asn_debug.put_line('Current pointer in actual table ' || TO_CHAR(n));
287 asn_debug.put_line('Current error status ' || x_cascaded_table(n).error_status);
288 asn_debug.put_line('To Organization Id ' || NVL(TO_CHAR(x_cascaded_table(n).to_organization_id), 'NULL'));
289 asn_debug.put_line('To Organization Code ' || NVL(x_cascaded_table(n).to_organization_code, 'NULL'));
290 END IF;
291
292 x_progress := '000';
293 asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
294 -- derive_location_info(x_cascaded_table, n);
295 rcv_roi_transaction.derive_location_info(x_cascaded_table, n);
296 rcv_roi_transaction.derive_ship_to_location_info(x_cascaded_table, n);
297 x_progress := '010';
298 asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
299 --derive_org_info(x_cascaded_table, n, x_header_record);
300 rcv_roi_transaction.derive_ship_to_org_info(x_cascaded_table,
301 n,
302 x_header_record
303 );
304 x_progress := '020';
305 asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
306 derive_customer_info(x_cascaded_table, n);
307 x_progress := '030';
308 asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
309 derive_order_header_info(x_cascaded_table, n);
310 x_progress := '040';
311 asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
312 -- derive_item_info(x_cascaded_table, n);
313 rcv_roi_transaction.derive_item_info(x_cascaded_table, n);
314 x_progress := '050';
315 asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
316 derive_order_line_info(x_cascaded_table, n);
317 x_progress := '060';
318 asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
319 derive_document_line_info(x_cascaded_table, n);
320 x_progress := '070';
321 asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
322 derive_uom_info(x_cascaded_table, n);
323 x_progress := '080';
324 asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
325 derive_transit_org_info(x_cascaded_table, n);
326 x_progress := '090';
327 asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
328 -- derive_routing_info(x_cascaded_table, n);
329 rcv_roi_transaction.derive_routing_header_info(x_cascaded_table, n);
330 rcv_roi_transaction.derive_routing_step_info(x_cascaded_table, n);
331 x_progress := '100';
332 asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
333 derive_deliver_to_info(x_cascaded_table, n);
334 x_progress := '120';
335 asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
336 -- derive_locator_info(x_cascaded_table, n);
337 rcv_roi_transaction.derive_to_locator_id(x_cascaded_table, n);
338 x_progress := '130';
339 asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
340 -- derive_reason_info(x_cascaded_table, n);
341 rcv_roi_transaction.derive_reason_info(x_cascaded_table, n);
342 x_progress := '140';
343 asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
344 derive_auto_transact_info(x_cascaded_table, n);
345 x_progress := '150';
346 asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
347 explode_line_quantity(x_cascaded_table,
348 n,
349 temp_cascaded_table,
350 x_header_record
351 );
352 x_progress := '170';
353 asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
354 END derive_rma_line;
355
356 PROCEDURE derive_rma_trans_del(
357 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
358 n IN OUT NOCOPY BINARY_INTEGER,
359 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
360 x_header_record IN rcv_roi_preprocessor.header_rec_type
361 ) IS
362 BEGIN
363 IF (g_asn_debug = 'Y') THEN
364 asn_debug.put_line('enter derive_cust_trans_del ');
365 END IF;
366
367 /* Derive the to_org_id */
368 derive_org_info(x_cascaded_table,
369 n,
370 x_header_record
371 );
372
373 IF (x_cascaded_table(n).unit_of_measure IS NOT NULL) THEN
374 IF (g_asn_debug = 'Y') THEN
375 asn_debug.put_line('X_progress ' || x_progress);
376 END IF;
377
378 SELECT muom.uom_code
379 INTO x_cascaded_table(n).uom_code
380 FROM mtl_units_of_measure muom
381 WHERE muom.unit_of_measure = x_cascaded_table(n).unit_of_measure;
382 ELSE
383 IF (g_asn_debug = 'Y') THEN
384 asn_debug.put_line('uom_code not derived as unit_of_measure is null');
385 END IF;
386 END IF;
387
388 x_progress := '026';
389
390 /* Locator info derivation is done for the Receiving locators FPJ
391 * project. Need to verify this with karun to see whether this is
392 * needed for Transfer also.
393 */
394 IF (x_cascaded_table(n).transaction_type = 'TRANSFER') THEN
395 rcv_roi_transaction.derive_location_info(x_cascaded_table, n);
396 rcv_roi_transaction.derive_from_locator_id(x_cascaded_table, n); -- WMS Change
397 rcv_roi_transaction.derive_to_locator_id(x_cascaded_table, n); -- WMS Change
398 END IF;
399
400 x_progress := '091';
401 -- derive_reason_info(x_cascaded_table, n);
402 rcv_roi_transaction.derive_reason_info(x_cascaded_table, n);
403 /* Auto_transact_code is null for all these transaction types */
404 x_cascaded_table(n).auto_transact_code := NULL;
405 /* quantity derivation is the same as interorg transfers */
406 rcv_int_org_transfer.derive_trans_del_line_quantity(x_cascaded_table,
407 n,
408 temp_cascaded_table
409 );
410 END derive_rma_trans_del;
411
412 PROCEDURE derive_rma_correction_line(
413 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
414 n IN OUT NOCOPY BINARY_INTEGER,
415 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
416 x_header_record IN rcv_roi_preprocessor.header_rec_type
417 ) IS
418 BEGIN
419 IF (g_asn_debug = 'Y') THEN
420 asn_debug.put_line('enter derive_correction_line ');
421 END IF;
422
423 /* Derive the to_org_id */
424 derive_org_info(x_cascaded_table,
425 n,
426 x_header_record
427 );
428
429 IF (x_cascaded_table(n).unit_of_measure IS NOT NULL) THEN
430 IF (g_asn_debug = 'Y') THEN
431 asn_debug.put_line('X_progress ' || x_progress);
432 END IF;
433
434 SELECT muom.uom_code
435 INTO x_cascaded_table(n).uom_code
436 FROM mtl_units_of_measure muom
437 WHERE muom.unit_of_measure = x_cascaded_table(n).unit_of_measure;
438 ELSE
439 IF (g_asn_debug = 'Y') THEN
440 asn_debug.put_line('uom_code not dereived as unit_of_measure is null');
441 END IF;
442 END IF;
443
444 x_progress := '091';
445 rcv_roi_transaction.derive_reason_info(x_cascaded_table, n);
446 /* Auto_transact_code is null for all these transaction types */
447 x_cascaded_table(n).auto_transact_code := NULL;
448 /* Quantity calculation is the same as for interorg transfer */
449 rcv_int_org_transfer.derive_int_org_cor_line_qty(x_cascaded_table,
450 n,
451 temp_cascaded_table
452 );
453 END derive_rma_correction_line;
454
455 PROCEDURE default_rma_line(
456 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
457 n IN BINARY_INTEGER,
458 x_header_id IN rcv_headers_interface.header_interface_id%TYPE,
459 x_header_record IN rcv_roi_preprocessor.header_rec_type
460 ) IS
461 x_locator_control NUMBER;
462 x_default_subinventory VARCHAR2(10);
463 x_default_locator_id NUMBER;
464 x_success BOOLEAN;
465 x_tax_name VARCHAR2(50); -- Bug 6331613
466 BEGIN
467 IF (g_asn_debug = 'Y') THEN
468 asn_debug.put_line('In default_rma_line');
469 END IF;
470
471 x_progress := '000';
472 -- set default_rma values
473 x_cascaded_table(n).header_interface_id := x_header_id;
474 x_cascaded_table(n).inspection_status_code := 'NOT INSPECTED';
475 x_cascaded_table(n).interface_source_code := 'RCV';
476 -- default columns based on the rma
477 default_from_rma(x_cascaded_table, n);
478 default_source_info(x_cascaded_table,
479 n,
480 x_header_id
481 );
482 default_destination_info(x_cascaded_table, n);
483 default_transaction_info(x_cascaded_table, n);
484 default_processing_info(x_cascaded_table, n);
485 default_item_info(x_cascaded_table, n);
486 default_routing_info(x_cascaded_table, n);
487 -- default columns based on the header
488 default_from_header(x_cascaded_table,
489 n,
490 x_header_record
491 );
492 /** bug 3609664, default subinventory and locator info.
493 * This is needed for direct deliver since rcv_roi_transaction.
494 * default_vendor_tran_del() will not be called.
495 */
496 IF x_cascaded_table(n).auto_transact_code = 'DELIVER' THEN
497 rcv_roi_transaction.default_to_subloc_info(x_cascaded_table, n);
498 END IF;
499
500 x_progress := '010';
501
502 IF (g_asn_debug = 'Y') THEN
503 asn_debug.put_line('Exit default_rma_line');
504 END IF;
505 END default_rma_line;
506
507 /*===========================================================================
508
509 PROCEDURE NAME: validate_rma_line()
510
511 ===========================================================================*/
512 PROCEDURE validate_rma_line(
513 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
514 n IN BINARY_INTEGER,
515 x_header_record IN rcv_roi_preprocessor.header_rec_type
516 ) IS
517 BEGIN
518 IF (g_asn_debug = 'Y') THEN
519 asn_debug.put_line('Enter validate_rma_line');
520 END IF;
521
522 x_progress := '000';
523
524 -- Bug 3219200: don't require ship_to_location_id for direct delivery
525 IF x_cascaded_table(n).transaction_type = 'RECEIVE'
526 AND x_cascaded_table(n).auto_transact_code <> 'DELIVER' THEN
527 rcv_roi_transaction.validate_ship_to_loc(x_cascaded_table, n);
528 END IF;
529
530 validate_txn_date(x_cascaded_table, n);
531 validate_qty_invoiced(x_cascaded_table, n);
532 validate_uom_info(x_cascaded_table, n);
533 validate_item_info(x_cascaded_table, n);
534 validate_freight_carrier_info(x_cascaded_table, n);
535 rcv_roi_transaction.validate_subinventory(x_cascaded_table, n);
536 validate_destination_type(x_cascaded_table, n);
537 rcv_roi_transaction.validate_routing_record(x_cascaded_table, n);
538 validate_tax_name(x_cascaded_table, n);
539 validate_country_of_origin(x_cascaded_table, n);
540 validate_ref_integrity(x_cascaded_table, n);
541 /** OPM change Bug# 3061052**/
542 rcv_roi_transaction.validate_opm_attributes(x_cascaded_table, n);
543
544 /* If destination_type_code is inventory then we need to make
545 * sure that we can correct this qty since it might have been
546 * already reserved in inventory.
547 */
548 IF (x_cascaded_table(n).destination_type_code = 'INVENTORY') THEN --{
549 rcv_roi_return.derive_inv_qty(x_cascaded_table, n);
550 END IF; --}
551 EXCEPTION
552 WHEN OTHERS THEN
553 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_unexp_error;
554 rcv_error_pkg.set_sql_error_message('validate_rma_line', x_progress);
555 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
556
557 IF (g_asn_debug = 'Y') THEN
558 asn_debug.put_line('I have hit an exception');
559 asn_debug.put_line(SQLERRM);
560 asn_debug.put_line('Exit validate_rma_line');
561 END IF;
562 END validate_rma_line;
563
564 /**
565 * Helper procedures for derive_rma_lines
566 */
567 PROCEDURE derive_uom_info(
568 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
569 n IN BINARY_INTEGER
570 ) IS
571 BEGIN
572 -- primary uom
573 IF x_cascaded_table(n).error_status IN('S', 'W')
574 AND x_cascaded_table(n).item_id IS NOT NULL
575 AND x_cascaded_table(n).primary_unit_of_measure IS NULL THEN
576 BEGIN
577 /* BUG 608353 */
578 /*Commenting defaulting of use_mtl_lot and use_mtl_serial
579 BUG 4735484
580 */
581 SELECT primary_unit_of_measure
582 --NVL(x_cascaded_table(n).use_mtl_lot, lot_control_code),
583 --NVL(x_cascaded_table(n).use_mtl_serial, serial_number_control_code)
584 INTO x_cascaded_table(n).primary_unit_of_measure
585 --x_cascaded_table(n).use_mtl_lot,
586 --x_cascaded_table(n).use_mtl_serial
587 FROM mtl_system_items
588 WHERE mtl_system_items.inventory_item_id = x_cascaded_table(n).item_id
589 AND mtl_system_items.organization_id = x_cascaded_table(n).to_organization_id;
590
591 IF (g_asn_debug = 'Y') THEN
592 asn_debug.put_line('Primary UOM: ' || x_cascaded_table(n).primary_unit_of_measure);
593 END IF;
594 EXCEPTION
595 WHEN NO_DATA_FOUND THEN
596 x_cascaded_table(n).error_status := 'W';
597 x_cascaded_table(n).error_message := 'Need an error message';
598
599 IF (g_asn_debug = 'Y') THEN
600 asn_debug.put_line('Primary UOM error');
601 END IF;
602 END;
603 END IF;
604
605 -- uom_code
606 IF (x_cascaded_table(n).unit_of_measure IS NOT NULL) THEN
607 IF (g_asn_debug = 'Y') THEN
608 asn_debug.put_line('X_progress ' || x_progress);
609 END IF;
610
611 SELECT muom.uom_code
612 INTO x_cascaded_table(n).uom_code
613 FROM mtl_units_of_measure muom
614 WHERE muom.unit_of_measure = x_cascaded_table(n).unit_of_measure;
615 ELSE
616 IF (g_asn_debug = 'Y') THEN
617 asn_debug.put_line('uom_code not derived as unit_of_measure is null');
618 END IF;
619 END IF;
620 END derive_uom_info;
621
622 PROCEDURE derive_order_header_info(
623 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
624 n IN BINARY_INTEGER
625 ) IS
626 BEGIN
627 -- bug 3223993 - derive oe_order_num and document num from each other
628 IF (x_cascaded_table(n).oe_order_num IS NOT NULL) THEN
629 x_cascaded_table(n).document_num := x_cascaded_table(n).oe_order_num;
630 ELSIF(x_cascaded_table(n).document_num IS NOT NULL) THEN
631 x_cascaded_table(n).oe_order_num := x_cascaded_table(n).document_num;
632 END IF;
633
634 -- We need order num and org_id since we can receive RMAs created in other OU
635 IF x_cascaded_table(n).error_status IN('S', 'W')
636 AND x_cascaded_table(n).oe_order_header_id IS NULL
637 AND x_cascaded_table(n).oe_order_num IS NOT NULL
638 AND x_cascaded_table(n).to_organization_id IS NOT NULL THEN
639 IF (g_asn_debug = 'Y') THEN
640 asn_debug.put_line('Deriving order_header_id');
641 END IF;
642
643 -- bug 3224001: change query to get the correct header for the given order_num
644
645 SELECT DISTINCT oeh.header_id
646 INTO x_cascaded_table(n).oe_order_header_id
647 FROM oe_order_headers_all oeh,
648 oe_order_lines_all oel,
649 oe_transaction_types_all oett
650 WHERE oeh.order_number = x_cascaded_table(n).oe_order_num
651 AND oeh.header_id = oel.header_id
652 AND oel.line_category_code = 'RETURN'
653 AND oel.line_type_id = oett.transaction_type_id
654 AND oett.order_category_code IN('MIXED', 'RETURN')
655 AND oel.open_flag = 'Y'
656 AND oeh.booked_flag = 'Y'
657 AND ( ( oeh.ship_from_org_id IS NOT NULL
658 AND oeh.ship_from_org_id = x_cascaded_table(n).to_organization_id)
659 OR EXISTS(SELECT 1
660 FROM oe_order_lines_all oela
661 WHERE oela.header_id = oeh.header_id
662 AND oela.ship_from_org_id = x_cascaded_table(n).to_organization_id));
663
664 IF (g_asn_debug = 'Y') THEN
665 asn_debug.put_line('Derived oe_order_header_id ' || x_cascaded_table(n).oe_order_header_id);
666 END IF;
667 END IF;
668 EXCEPTION
669 WHEN NO_DATA_FOUND THEN
670 IF (g_asn_debug = 'Y') THEN
671 asn_debug.put_line('Cannot derive order_header_id - no data found');
672 END IF;
673
674 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
675 rcv_error_pkg.set_error_message('RCV_ITEM_ORDER_HEADER_ID', x_cascaded_table(n).error_message);
676 rcv_error_pkg.set_token('NUMBER', x_cascaded_table(n).oe_order_num);
677 rcv_error_pkg.log_interface_error('OE_ORDER_NUM', FALSE);
678 WHEN TOO_MANY_ROWS THEN
679 IF (g_asn_debug = 'Y') THEN
680 asn_debug.put_line('Cannot derive order_header_id - too many rows');
681 END IF;
682
683 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
684 rcv_error_pkg.set_error_message('RCV_ITEM_ORDER_HEADER_ID', x_cascaded_table(n).error_message);
685 rcv_error_pkg.set_token('NUMBER', x_cascaded_table(n).oe_order_num);
686 rcv_error_pkg.log_interface_error('OE_ORDER_NUM', FALSE);
687 WHEN OTHERS THEN
688 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_unexp_error;
689 rcv_error_pkg.set_sql_error_message('derive_order_header_info', '000');
690 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
691 rcv_error_pkg.log_interface_error('OE_ORDER_NUM', FALSE);
692 END derive_order_header_info;
693
694 PROCEDURE derive_order_line_info(
695 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
696 n IN BINARY_INTEGER
697 ) IS
698 my_line_id NUMBER;
699 my_item_id NUMBER;
700 BEGIN
701 IF x_cascaded_table(n).error_status IN('S', 'W')
702 AND x_cascaded_table(n).oe_order_line_id IS NULL
703 AND x_cascaded_table(n).oe_order_header_id IS NOT NULL
704 AND x_cascaded_table(n).document_line_num IS NOT NULL THEN
705 SELECT line_id,
706 inventory_item_id
707 INTO my_line_id,
708 my_item_id
709 FROM oe_order_lines_all
710 WHERE header_id = x_cascaded_table(n).oe_order_header_id
711 AND line_number = x_cascaded_table(n).document_line_num
712 -- pjiang: extra filter for oe line split
713 AND flow_status_code = 'AWAITING_RETURN';
714
715 x_cascaded_table(n).oe_order_line_id := my_line_id;
716
717 IF g_asn_debug = 'Y' THEN
718 asn_debug.put_line('Derived oe_order_line_id ' || TO_CHAR(x_cascaded_table(n).oe_order_line_id));
719 END IF;
720
721 IF x_cascaded_table(n).item_id IS NULL THEN
722 x_cascaded_table(n).item_id := my_item_id;
723
724 IF g_asn_debug = 'Y' THEN
725 asn_debug.put_line('Derived item_id ' || TO_CHAR(x_cascaded_table(n).item_id));
726 END IF;
727 END IF;
728 END IF;
729 EXCEPTION
730 WHEN NO_DATA_FOUND THEN
731 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
732 rcv_error_pkg.set_error_message('RCV_ITEM_ORDER_HEADER_ID', x_cascaded_table(n).error_message);
733 rcv_error_pkg.set_token('NUMBER', x_cascaded_table(n).oe_order_num);
734 rcv_error_pkg.log_interface_error('OE_ORDER_NUM', FALSE);
735 WHEN OTHERS THEN
736 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_unexp_error;
737 rcv_error_pkg.set_sql_error_message('derive_order_header_info', '000');
738 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
739 rcv_error_pkg.log_interface_error('OE_ORDER_NUM', FALSE);
740 END derive_order_line_info;
741
742 PROCEDURE derive_document_line_info(
743 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
744 n IN BINARY_INTEGER
745 ) IS
746 BEGIN
747 IF x_cascaded_table(n).error_status IN('S', 'W')
748 AND x_cascaded_table(n).document_line_num IS NULL
749 AND x_cascaded_table(n).oe_order_line_id IS NOT NULL
750 AND x_cascaded_table(n).oe_order_header_id IS NOT NULL THEN
751 SELECT line_number
752 INTO x_cascaded_table(n).document_line_num
753 FROM oe_order_lines_all
754 WHERE line_id = x_cascaded_table(n).oe_order_line_id;
755 END IF;
756 END derive_document_line_info;
757
758 PROCEDURE derive_customer_info(
759 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
760 n IN BINARY_INTEGER
761 ) IS
762 BEGIN
763 -- derive customer_id from customer_account_number
764 IF x_cascaded_table(n).customer_id IS NULL
765 AND x_cascaded_table(n).customer_account_number IS NOT NULL THEN
766 IF (g_asn_debug = 'Y') THEN
767 asn_debug.put_line('Deriving customer_id from customer_account_number');
768 END IF;
769
770 SELECT acct.cust_account_id
771 INTO x_cascaded_table(n).customer_id
772 FROM hz_cust_accounts acct
773 WHERE acct.account_number = x_cascaded_table(n).customer_account_number;
774
775 IF (g_asn_debug = 'Y') THEN
776 asn_debug.put_line('Derived customer_id ' || x_cascaded_table(n).customer_id);
777 END IF;
778 END IF;
779
780 -- derive customer_id from customer_party_name if name is unique
781 IF x_cascaded_table(n).customer_id IS NULL
782 AND x_cascaded_table(n).customer_party_name IS NOT NULL THEN
783 IF (g_asn_debug = 'Y') THEN
784 asn_debug.put_line('Deriving customer_id from customer_account_number');
785 END IF;
786
787 BEGIN
788 SELECT acct.cust_account_id
789 INTO x_cascaded_table(n).customer_id
790 FROM hz_parties party,
791 hz_cust_accounts acct
792 WHERE acct.party_id = party.party_id
793 AND party.party_name = x_cascaded_table(n).customer_party_name;
794 EXCEPTION
795 WHEN TOO_MANY_ROWS THEN
796 NULL;
797 END;
798
799 IF (g_asn_debug = 'Y') THEN
800 asn_debug.put_line('Derived customer_id ' || x_cascaded_table(n).customer_id);
801 END IF;
802 END IF;
803
804 -- derive customer_site_id from from_organization_id
805 IF x_cascaded_table(n).customer_site_id IS NULL
806 AND x_cascaded_table(n).from_organization_id IS NOT NULL THEN
807 IF (g_asn_debug = 'Y') THEN
808 asn_debug.put_line('Deriving customer_site_id from from_organization_id');
809 END IF;
810
811 x_cascaded_table(n).customer_account_number := x_cascaded_table(n).from_organization_id;
812
813 IF (g_asn_debug = 'Y') THEN
814 asn_debug.put_line('Derived customer_site_id ' || x_cascaded_table(n).customer_site_id);
815 END IF;
816 END IF;
817 END derive_customer_info;
818
819 PROCEDURE derive_org_info(
820 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
821 n IN BINARY_INTEGER,
822 x_header_record IN rcv_roi_preprocessor.header_rec_type
823 ) IS
824 ship_to_org_record rcv_shipment_object_sv.organization_id_record_type;
825 BEGIN
826 -- derive from location_info
827 IF x_cascaded_table(n).to_organization_id IS NULL
828 AND x_cascaded_table(n).to_organization_code IS NULL
829 AND x_cascaded_table(n).error_status IN('S', 'W') THEN
830 IF (g_asn_debug = 'Y') THEN
831 asn_debug.put_line('Attempting to derive the org from the ship to location');
832 END IF;
833
834 IF (x_cascaded_table(n).ship_to_location_id IS NOT NULL) THEN
835 SELECT MAX(org.organization_id)
836 INTO x_cascaded_table(n).to_organization_code
837 FROM hr_locations hl,
838 HR_ALL_ORGANIZATION_UNITS org --Bug 5217526. Earlier used org_organization_definitions
839 WHERE x_cascaded_table(n).ship_to_location_id = hl.location_id
840 AND hl.inventory_organization_id = org.organization_id;
841
842 IF (g_asn_debug = 'Y') THEN
843 asn_debug.put_line('Set Org using location id ');
844 END IF;
845 ELSIF(x_cascaded_table(n).ship_to_location_code IS NOT NULL) THEN
846 SELECT MAX(org.organization_id)
847 INTO x_cascaded_table(n).to_organization_code
848 FROM hr_locations hl,
849 HR_ALL_ORGANIZATION_UNITS org --Bug 5217526. Earlier used org_organization_definitions
850 WHERE x_cascaded_table(n).ship_to_location_code = hl.location_code
851 AND hl.inventory_organization_id = org.organization_id;
852
853 IF (g_asn_debug = 'Y') THEN
854 asn_debug.put_line('Set Org using location code ');
855 END IF;
856 END IF;
857 END IF;
858
859 -- derive from to_organization_code
860 IF x_cascaded_table(n).error_status IN('S', 'W')
861 AND x_cascaded_table(n).to_organization_id IS NULL
862 AND x_cascaded_table(n).to_organization_code IS NOT NULL THEN
863 IF (g_asn_debug = 'Y') THEN
864 asn_debug.put_line('X_Progress ' || x_progress);
865 END IF;
866
867 ship_to_org_record.organization_code := x_cascaded_table(n).to_organization_code;
868 ship_to_org_record.organization_id := x_cascaded_table(n).to_organization_id;
869 ship_to_org_record.error_record.error_status := 'S';
870 ship_to_org_record.error_record.error_message := NULL;
871
872 IF (g_asn_debug = 'Y') THEN
873 asn_debug.put_line('Into Derive Organization Record Procedure');
874 END IF;
875
876 po_orgs_sv.derive_org_info(ship_to_org_record);
877
878 IF (g_asn_debug = 'Y') THEN
879 asn_debug.put_line('Debug Output after organization procedure');
880 asn_debug.put_line(ship_to_org_record.organization_code);
881 asn_debug.put_line(TO_CHAR(ship_to_org_record.organization_id));
882 asn_debug.put_line(ship_to_org_record.error_record.error_status);
883 asn_debug.put_line('Debug organization output over');
884 END IF;
885
886 x_cascaded_table(n).to_organization_code := ship_to_org_record.organization_code;
887 x_cascaded_table(n).to_organization_id := ship_to_org_record.organization_id;
888 x_cascaded_table(n).error_status := ship_to_org_record.error_record.error_status;
889 x_cascaded_table(n).error_message := ship_to_org_record.error_record.error_message;
890 END IF;
891
892 -- couldn't derive, default from header instead
893 IF x_cascaded_table(n).error_status IN('S', 'W')
894 AND x_cascaded_table(n).to_organization_id IS NULL THEN
895 IF (g_asn_debug = 'Y') THEN
896 asn_debug.put_line('Will default org id from header ' || x_header_record.header_record.ship_to_organization_id);
897 END IF;
898
899 x_cascaded_table(n).to_organization_id := x_header_record.header_record.ship_to_organization_id;
900 END IF;
901
902 IF (g_asn_debug = 'Y') THEN
903 asn_debug.put_line('Derived to_organization_id ' || x_cascaded_table(n).to_organization_id);
904 END IF;
905 END derive_org_info;
906
907 PROCEDURE derive_transit_org_info(
908 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
909 n IN BINARY_INTEGER
910 ) IS
911 transit_org_record rcv_shipment_object_sv.organization_id_record_type;
912 BEGIN
913 IF (x_cascaded_table(n).error_status IN('S', 'W'))
914 AND ( x_cascaded_table(n).intransit_owning_org_id IS NULL
915 AND x_cascaded_table(n).intransit_owning_org_code IS NOT NULL) THEN
916 IF (g_asn_debug = 'Y') THEN
917 asn_debug.put_line('X_progress ' || x_progress);
918 END IF;
919
920 transit_org_record.organization_code := x_cascaded_table(n).intransit_owning_org_code;
921 transit_org_record.organization_id := x_cascaded_table(n).intransit_owning_org_id;
922 transit_org_record.error_record.error_status := 'S';
923 transit_org_record.error_record.error_message := NULL;
924
925 IF (g_asn_debug = 'Y') THEN
926 asn_debug.put_line('In Intransit Owning Org Record Procedure');
927 END IF;
928
929 po_orgs_sv.derive_org_info(transit_org_record);
930
931 IF (g_asn_debug = 'Y') THEN
932 asn_debug.put_line('Intransit organization code ' || transit_org_record.organization_code);
933 asn_debug.put_line('Intransit organization id ' || TO_CHAR(transit_org_record.organization_id));
934 asn_debug.put_line('Intransit error status ' || transit_org_record.error_record.error_status);
935 END IF;
936
937 x_cascaded_table(n).intransit_owning_org_code := transit_org_record.organization_code;
938 x_cascaded_table(n).intransit_owning_org_id := transit_org_record.organization_id;
939 x_cascaded_table(n).error_status := transit_org_record.error_record.error_status;
940 x_cascaded_table(n).error_message := transit_org_record.error_record.error_message;
941 END IF;
942 END derive_transit_org_info;
943
944 PROCEDURE derive_deliver_to_info(
945 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
946 n IN BINARY_INTEGER
947 ) IS
948 employee_id_record rcv_shipment_object_sv.employee_id_record_type;
949 location_id_record rcv_shipment_object_sv.location_id_record_type;
950 BEGIN
951 -- deliver_to_person_id
952 IF (x_cascaded_table(n).error_status IN('S', 'W'))
953 AND ( x_cascaded_table(n).deliver_to_person_id IS NULL
954 AND x_cascaded_table(n).deliver_to_person_name IS NOT NULL) THEN
955 IF (g_asn_debug = 'Y') THEN
956 asn_debug.put_line('X_progress ' || x_progress);
957 END IF;
958
959 employee_id_record.employee_name := x_cascaded_table(n).deliver_to_person_name;
960 employee_id_record.employee_id := x_cascaded_table(n).deliver_to_person_id;
961 employee_id_record.error_record.error_status := 'S';
962 employee_id_record.error_record.error_message := NULL;
963
964 IF (g_asn_debug = 'Y') THEN
965 asn_debug.put_line('In Derive deliver_to_person_id Information');
966 END IF;
967
968 po_employees_sv.derive_employee_info(employee_id_record);
969
970 IF (g_asn_debug = 'Y') THEN
971 asn_debug.put_line('Employee name ' || employee_id_record.employee_name);
972 asn_debug.put_line('Employee id ' || TO_CHAR(employee_id_record.employee_id));
973 asn_debug.put_line('Employee error status ' || employee_id_record.error_record.error_status);
974 END IF;
975
976 x_cascaded_table(n).deliver_to_person_name := employee_id_record.employee_name;
977 x_cascaded_table(n).deliver_to_person_id := employee_id_record.employee_id;
978 x_cascaded_table(n).error_status := employee_id_record.error_record.error_status;
979 x_cascaded_table(n).error_message := employee_id_record.error_record.error_message;
980 END IF;
981
982 -- deliver_to_location
983 IF (x_cascaded_table(n).error_status IN('S', 'W'))
984 AND ( x_cascaded_table(n).deliver_to_location_id IS NULL
985 AND x_cascaded_table(n).deliver_to_location_code IS NOT NULL) THEN
986 IF (g_asn_debug = 'Y') THEN
987 asn_debug.put_line('X_progress ' || x_progress);
988 END IF;
989
990 location_id_record.location_code := x_cascaded_table(n).deliver_to_location_code;
991 location_id_record.error_record.error_status := 'S';
992 location_id_record.error_record.error_message := NULL;
993
994 IF (g_asn_debug = 'Y') THEN
995 asn_debug.put_line('Derive deliver_to_location_id');
996 END IF;
997
998 rcv_transactions_interface_sv.get_location_id(location_id_record);
999 x_cascaded_table(n).deliver_to_location_id := location_id_record.location_id;
1000 x_cascaded_table(n).error_status := location_id_record.error_record.error_status;
1001 x_cascaded_table(n).error_message := location_id_record.error_record.error_message;
1002 END IF;
1003 END derive_deliver_to_info;
1004
1005 PROCEDURE derive_auto_transact_info(
1006 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1007 n IN BINARY_INTEGER
1008 ) IS
1009 BEGIN
1010 IF (x_cascaded_table(n).error_status IN('S', 'W'))
1011 AND x_cascaded_table(n).auto_transact_code IS NULL THEN
1012 IF (g_asn_debug = 'Y') THEN
1013 asn_debug.put_line('X_progress ' || x_progress);
1014 asn_debug.put_line('Setting auto_transact_code to transaction_type ' || x_cascaded_table(n).transaction_type);
1015 END IF;
1016
1017 x_cascaded_table(n).auto_transact_code := x_cascaded_table(n).transaction_type;
1018 END IF;
1019 END derive_auto_transact_info;
1020
1021 PROCEDURE explode_line_quantity(
1022 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1023 n IN OUT NOCOPY BINARY_INTEGER,
1024 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1025 x_header_record IN rcv_roi_preprocessor.header_rec_type
1026 ) IS
1027 -- declare the line cursor
1028 /* Bug 4740567
1029 * Include oe order line_id also in addition to line_num since
1030 * when line_num is null and the rma lines have same rti id,
1031 * the cursor was fetching the first line_id itself and overriding
1032 * the value provided by the mobile txn.
1033 */
1034 CURSOR rma_lines(
1035 v_header_id NUMBER,
1036 v_line_id NUMBER, --bug 4740567
1037 v_item_id NUMBER,
1038 v_rma_line_num NUMBER,
1039 v_ship_to_org_id NUMBER, -- rcv.to_organization_id == oel.ship_from_org_id
1040 v_customer_item_id NUMBER
1041 ) IS
1042 SELECT NVL(oel.ship_to_org_id, oeh.ship_to_org_id) customer_site_id,
1043 NVL(oel.ship_from_org_id, oeh.ship_from_org_id) to_organization_id,
1044 NVL(oel.sold_to_org_id, oeh.sold_to_org_id) customer_id,
1045 NVL(oel.promise_date, oel.request_date) expected_receipt_date,
1046 oel.ordered_quantity ordered_qty,
1047 'N' enforce_ship_to_location_code,
1048 oel.deliver_to_contact_id deliver_to_person_id,
1049 oel.deliver_to_org_id deliver_to_location_id,
1050 oel.header_id oe_order_header_id,
1051 oel.line_id oe_order_line_id,
1052 oeh.order_number oe_order_num,
1053 oel.line_number oe_order_line_num,
1054 oel.inventory_item_id item_id,
1055 mum.unit_of_measure,
1056 msi.description description
1057 FROM oe_order_headers_all oeh,
1058 oe_order_lines_all oel,
1059 oe_transaction_types_all olt,
1060 oe_transaction_types_tl t,
1061 mtl_units_of_measure_tl mum,
1062 mtl_system_items msi
1063 WHERE oeh.header_id = v_header_id
1064 AND oeh.header_id = oel.header_id
1065 AND oel.line_id = NVL(v_line_id, oel.line_id)-- bug 4740567
1066 AND oel.line_number = NVL(v_rma_line_num, oel.line_number)
1067 AND oeh.open_flag = 'Y'
1068 AND oel.line_category_code = 'RETURN'
1069 AND oel.open_flag = 'Y'
1070 AND oel.inventory_item_id = NVL(v_item_id, oel.inventory_item_id)
1071 AND oel.ship_from_org_id = NVL(v_ship_to_org_id, oel.ship_from_org_id)
1072 AND oel.line_type_id = olt.transaction_type_id
1073 AND olt.transaction_type_code = 'LINE'
1074 AND olt.transaction_type_id = t.transaction_type_id
1075 AND t.LANGUAGE = USERENV('LANG')
1076 AND msi.organization_id = oe_sys_parameters.VALUE('MASTER_ORGANIZATION_ID', oel.org_id)
1077 AND msi.inventory_item_id = oel.inventory_item_id
1078 AND ( oel.ordered_item_id = NVL(v_customer_item_id, oel.ordered_item_id)
1079 OR oel.ordered_item_id IS NULL)
1080 AND oel.booked_flag = 'Y'
1081 AND oel.ordered_quantity > NVL(oel.shipped_quantity, 0)
1082 AND oel.flow_status_code = 'AWAITING_RETURN'
1083 AND oel.order_quantity_uom = mum.uom_code
1084 AND mum.LANGUAGE = USERENV('LANG')
1085 ORDER BY expected_receipt_date;
1086
1087 -- declare variables
1088 x_rma_line_record rma_lines%ROWTYPE;
1089 txn_remaining_qty NUMBER := 0;
1090 txn_remaining_qty_rma_uom NUMBER := 0;
1091 rma_line_qty NUMBER := 0;
1092 allocate_qty NUMBER := 0;
1093 rma_lines_fetched NUMBER := 0;
1094 transaction_ok BOOLEAN := FALSE;
1095 high_range_date DATE;
1096 low_range_date DATE;
1097 valid_date BOOLEAN;
1098 insert_into_table BOOLEAN := FALSE;
1099 tax_amount_factor NUMBER;
1100 rma_txn_uom_qty NUMBER;
1101 rma_primary_uom_qty NUMBER;
1102 already_allocated_qty NUMBER := 0;
1103 x_item_id NUMBER;
1104 x_routing_id NUMBER;
1105 x_rcv_date_exception VARCHAR2(20);
1106 x_allow_substitutes VARCHAR2(1) := 'N';
1107 x_qty_rcv_tolerance NUMBER;
1108 x_qty_rcv_exception VARCHAR2(80);
1109 x_days_early_receipt NUMBER;
1110 x_days_late_receipt NUMBER;
1111 x_enforce_ship_to_loc VARCHAR2(25) := 'N';
1112 x_line_category_code VARCHAR2(30);
1113 x_customer_item_num rcv_transactions_interface.customer_item_num%TYPE;
1114 x_ship_to_organization_id NUMBER;
1115 x_ship_to_location_id NUMBER;
1116 x_full_name VARCHAR2(240);
1117 x_sob_id NUMBER;
1118 x_header_open_flag VARCHAR2(1);
1119 x_line_open_flag VARCHAR2(1);
1120 x_oe_msg_count NUMBER;
1121 x_oe_msg_data VARCHAR2(240);
1122 x_under_return_tolerance NUMBER;
1123 x_oe_return_status VARCHAR2(30);
1124 x_shipped_quantity NUMBER;
1125 x_booked_flag VARCHAR2(1);
1126 x_flow_status_code VARCHAR2(30);
1127 x_ordered_quantity NUMBER;
1128 rma_uom_qty NUMBER;
1129 primary_uom_qty NUMBER;
1130 rma_receipt_uom_qty NUMBER;
1131 defined BOOLEAN;
1132 BEGIN
1133 --check line quanity > 0
1134 x_progress := '097';
1135
1136 IF (g_asn_debug = 'Y') THEN
1137 asn_debug.put_line('X_progress ' || x_progress);
1138 END IF;
1139
1140 IF x_cascaded_table(n).error_status NOT IN('S', 'W')
1141 OR x_cascaded_table(n).quantity <= 0 THEN --{
1142 IF (g_asn_debug = 'Y') THEN
1143 asn_debug.put_line('Quantity is <= zero. Cascade will fail');
1144 END IF;
1145
1146 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1147 rcv_error_pkg.set_error_message('RCV_ITEM_NO_SHIP_QTY', x_cascaded_table(n).error_message);
1148 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).item_num);
1149 rcv_error_pkg.log_interface_error('ITEM_NUM', FALSE);
1150 RETURN;
1151 END IF; --} end qty > 0 check
1152
1153 /**
1154 * The following steps will create a set of rows linking the line record with
1155 * its corresponding shipment rows until the quantity value from
1156 * the rma is consumed. (Cascade)
1157 */
1158 x_progress := '098';
1159
1160 IF (g_asn_debug = 'Y') THEN
1161 asn_debug.put_line('X_progress ' || x_progress);
1162 END IF;
1163
1164 -- check order info
1165 IF ( x_cascaded_table(n).oe_order_header_id IS NULL
1166 OR ( x_cascaded_table(n).item_id IS NULL
1167 AND x_cascaded_table(n).customer_item_num IS NULL
1168 AND x_cascaded_table(n).oe_order_line_id IS NULL
1169 AND x_cascaded_table(n).document_line_num IS NULL)) THEN --{
1170 IF (g_asn_debug = 'Y') THEN
1171 asn_debug.put_line('No oe_order_header_id/item_id ');
1172 asn_debug.put_line('Status = ' || x_cascaded_table(n).error_status);
1173 END IF;
1174
1175 -- only set error if not already set
1176 IF x_cascaded_table(n).error_status IN('S', 'W', 'F') THEN --{
1177 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1178 rcv_error_pkg.set_error_message('RCV_ITEM_NO_SHIP_QTY', x_cascaded_table(n).error_message);
1179 rcv_error_pkg.log_interface_error('ITEM_NUM', FALSE);
1180 END IF; --}
1181
1182 RETURN;
1183 END IF;
1184
1185 -- }
1186
1187 -- Assign shipped quantity to remaining quantity
1188 IF (g_asn_debug = 'Y') THEN
1189 asn_debug.put_line('Assign txn quantity to remaining quantity');
1190 END IF;
1191
1192 txn_remaining_qty := x_cascaded_table(n).quantity;
1193
1194 IF (g_asn_debug = 'Y') THEN
1195 asn_debug.put_line('Have assigned the quantity');
1196 END IF;
1197
1198 -- Calculate tax_amount_factor for calculating tax_amount for
1199 -- each cascaded line
1200 tax_amount_factor := NVL(x_cascaded_table(n).tax_amount, 0) / txn_remaining_qty;
1201
1202 IF (g_asn_debug = 'Y') THEN
1203 asn_debug.put_line('Tax Factor ' || TO_CHAR(tax_amount_factor));
1204 asn_debug.put_line('Txn Quantity : ' || TO_CHAR(txn_remaining_qty));
1205 asn_debug.put_line('Before starting Cascade');
1206 END IF;
1207
1208 -- make sure the temp table is clean before we start using it
1209 temp_cascaded_table.DELETE;
1210
1211 IF (g_asn_debug = 'Y') THEN
1212 asn_debug.put_line('Executing RMA Lines cursor with:');
1213 asn_debug.put_line('oe_order_header_id: ' || x_cascaded_table(n).oe_order_header_id);
1214 asn_debug.put_line('item_id: ' || x_cascaded_table(n).item_id);
1215 asn_debug.put_line('oe_order_line_num: ' || x_cascaded_table(n).oe_order_line_num);
1216 asn_debug.put_line('to_organization_id: ' || x_cascaded_table(n).to_organization_id);
1217 asn_debug.put_line('customer_item_num: ' || x_cascaded_table(n).customer_item_num);
1218 END IF;
1219
1220 FOR x_rma_line_record IN rma_lines(x_cascaded_table(n).oe_order_header_id,
1221 x_cascaded_table(n).oe_order_line_id, --bug 4740567
1222 x_cascaded_table(n).item_id,
1223 x_cascaded_table(n).oe_order_line_num,
1224 x_cascaded_table(n).to_organization_id,
1225 x_cascaded_table(n).customer_item_id
1226 ) LOOP --{
1227 -- preserve a count to use after cursor is closed
1228 rma_lines_fetched := rma_lines%ROWCOUNT;
1229
1230 IF (g_asn_debug = 'Y') THEN
1231 asn_debug.put_line('RMA Lines fetched ' || TO_CHAR(rma_lines_fetched));
1232 asn_debug.put_line('Remaining Quantity ' || TO_CHAR(txn_remaining_qty));
1233 asn_debug.put_line('Fetched order header id ' || x_rma_line_record.oe_order_header_id);
1234 asn_debug.put_line('Fetched order line id ' || x_rma_line_record.oe_order_line_id);
1235 asn_debug.put_line('Fetched order number ' || x_rma_line_record.oe_order_num);
1236 END IF;
1237
1238 -- done allocating transaction quantity
1239 IF txn_remaining_qty <= 0 THEN
1240 asn_debug.put_line('Done allocating transaction quantity');
1241 EXIT;
1242 END IF;
1243
1244 -- maintain a dense table
1245 IF temp_cascaded_table.COUNT = 0 THEN
1246 -- copy txn from main table to temp table
1247 temp_cascaded_table(temp_cascaded_table.COUNT + 1) := x_cascaded_table(n);
1248 ELSE
1249 -- copy from previous row
1250 temp_cascaded_table(temp_cascaded_table.COUNT + 1) := temp_cascaded_table(temp_cascaded_table.LAST);
1251 END IF;
1252
1253 IF (g_asn_debug = 'Y') THEN
1254 asn_debug.put_line('Count in temp_cascade_table : ' || TO_CHAR(temp_cascaded_table.COUNT));
1255 asn_debug.put_line('Cursor record ' || TO_CHAR(rma_lines%ROWCOUNT));
1256 asn_debug.put_line('Check date tolerance');
1257 END IF;
1258
1259 -- default to successful matching to current line
1260 insert_into_table := TRUE;
1261 -- check for date tolerance
1262 -- Call rcv_core_s.get_receiving_controls to get the values of days early, days late receipt values
1263 rcv_core_s.get_receiving_controls(NULL,
1264 x_rma_line_record.item_id,
1265 NULL,
1266 x_rma_line_record.to_organization_id,
1267 x_enforce_ship_to_loc,
1268 x_allow_substitutes,
1269 x_routing_id,
1270 x_qty_rcv_tolerance,
1271 x_qty_rcv_exception,
1272 x_days_early_receipt,
1273 x_days_late_receipt,
1274 x_rcv_date_exception
1275 );
1276 valid_date := rcv_oe_rma_receipts_sv.rma_val_receipt_date_tolerance(x_rma_line_record.oe_order_header_id,
1277 x_rma_line_record.oe_order_line_id,
1278 NVL(temp_cascaded_table(1).expected_receipt_date, x_header_record.header_record.expected_receipt_date)
1279 );
1280
1281 /* bug 1060261 - added error message to be shown when the expected date is outside tolerance range */
1282 IF ( x_rcv_date_exception = 'REJECT'
1283 AND NOT valid_date) THEN
1284 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1285 rcv_error_pkg.set_error_message('RCV_ASN_DATE_OUT_TOL', x_cascaded_table(n).error_message);
1286 rcv_error_pkg.set_token('DELIVERY DATE', NVL(temp_cascaded_table(1).expected_receipt_date, x_header_record.header_record.expected_receipt_date));
1287 rcv_error_pkg.log_interface_error('DOCUMENT_NUM', FALSE);
1288 insert_into_table := FALSE;
1289 END IF; --}
1290
1291 IF (g_asn_debug = 'Y') THEN
1292 asn_debug.put_line('Days exception Code ' || NVL(x_rcv_date_exception, 'NONE'));
1293 END IF;
1294
1295 /*
1296 ** Get the available quantity for the line
1297 ** that is available for allocation by this interface transaction
1298 */
1299 rma_line_qty := x_rma_line_record.ordered_qty;
1300
1301 /* If there are other rows in rti before for this line id then we need to reduce
1302 * the available qty for this line to be less by that qty */
1303 IF insert_into_table THEN
1304 already_allocated_qty := 0;
1305
1306 /* bug 4505906, this looks like it should work, EXCEPT that the cascaded table applies to
1307 only the current RTI row, not the previous RTI rows. major failure!
1308 So the fix is to keep track of the used rows in this session with a binary indexed table
1309 FOR i IN 1 ..(n - 1) LOOP
1310 IF x_cascaded_table(i).oe_order_line_id = x_rma_line_record.oe_order_line_id THEN
1311 already_allocated_qty := already_allocated_qty + x_cascaded_table(i).source_doc_quantity;
1312 END IF;
1313 END LOOP;
1314 */
1315
1316 IF g_used_rma_line_amounts.exists(x_rma_line_record.oe_order_line_id) THEN
1317 already_allocated_qty := g_used_rma_line_amounts(x_rma_line_record.oe_order_line_id);
1318 asn_debug.put_line('amount '||already_allocated_qty||' already allocated for order line '||x_rma_line_record.oe_order_line_id);
1319 END IF;
1320
1321 END IF;
1322
1323 IF (g_asn_debug = 'Y') THEN
1324 asn_debug.put_line('Available Quantity ' || TO_CHAR(rma_line_qty));
1325 END IF;
1326
1327 -- if qty has already been allocated then reduce available and tolerable
1328 -- qty by the allocated amount
1329 IF NVL(already_allocated_qty, 0) > 0 THEN --{
1330 rma_line_qty := rma_line_qty - already_allocated_qty;
1331
1332 IF rma_line_qty < 0 THEN
1333 rma_line_qty := 0;
1334 END IF;
1335
1336 IF (g_asn_debug = 'Y') THEN
1337 asn_debug.put_line('Have some allocated quantity. Will reduce qty');
1338 asn_debug.put_line('Allocated Qty ' || TO_CHAR(already_allocated_qty));
1339 asn_debug.put_line('After reducing by allocated qty');
1340 asn_debug.put_line('Available Quantity ' || TO_CHAR(rma_line_qty));
1341 END IF;
1342 END IF;
1343
1344 --}
1345
1346 -- if this line has no more quantity available to allocate, skip to the next one
1347 insert_into_table := insert_into_table
1348 AND (rma_line_qty > 0);
1349 -- We can use the first record since the item_id and uom are not going to change
1350 -- Check that we can convert between ASN-> PO uom
1351 -- PO -> ASN uom
1352 -- PO -> PRIMARY uom
1353 -- If any of the conversions fail then we cannot use that record
1354 txn_remaining_qty_rma_uom := 0; -- initialize
1355 rma_uom_qty := 0; -- initialize
1356 primary_uom_qty := 0; -- initialize
1357
1358 IF insert_into_table THEN
1359 txn_remaining_qty_rma_uom := rcv_transactions_interface_sv.convert_into_correct_qty(txn_remaining_qty,
1360 temp_cascaded_table(1).unit_of_measure,
1361 temp_cascaded_table(1).item_id,
1362 x_rma_line_record.unit_of_measure
1363 );
1364 -- using arbit qty for RMA->Receipt UOM, RMA->Primary UOM conversion as this is just a check
1365 rma_receipt_uom_qty := rcv_transactions_interface_sv.convert_into_correct_qty(1000,
1366 x_rma_line_record.unit_of_measure,
1367 temp_cascaded_table(1).item_id,
1368 temp_cascaded_table(1).unit_of_measure
1369 );
1370 rma_primary_uom_qty := rcv_transactions_interface_sv.convert_into_correct_qty(1000,
1371 x_rma_line_record.unit_of_measure,
1372 temp_cascaded_table(1).item_id,
1373 temp_cascaded_table(1).primary_unit_of_measure
1374 );
1375 END IF;
1376
1377 IF txn_remaining_qty_rma_uom = 0
1378 OR rma_receipt_uom_qty = 0
1379 OR rma_primary_uom_qty = 0 THEN
1380 --{ PO -> ASN uom, PO -> PRIMARY UOM
1381 -- no point in going further for this record
1382 -- as we cannot convert between the ASN -> PO uoms
1383 IF (g_asn_debug = 'Y') THEN
1384 asn_debug.put_line('Need an error message in the interface tables');
1385 asn_debug.put_line('Cannot interconvert between diff UOMs');
1386 asn_debug.put_line('This RMA line cannot be used as the uoms ');
1387 asn_debug.put_line(temp_cascaded_table(1).unit_of_measure || ' ' || x_rma_line_record.unit_of_measure);
1388 asn_debug.put_line('cannot be converted for item ' || TO_CHAR(temp_cascaded_table(1).item_id));
1389 insert_into_table := FALSE;
1390 END IF;
1391 ELSE --}{
1392 IF (g_asn_debug = 'Y') THEN
1393 asn_debug.put_line('Current Item Id ' || TO_CHAR(temp_cascaded_table(1).item_id));
1394 asn_debug.put_line('Current Txn Quantity ' || TO_CHAR(txn_remaining_qty));
1395 asn_debug.put_line('Current Txn UOM ' || temp_cascaded_table(1).unit_of_measure);
1396 asn_debug.put_line('Converted RMA UOM Quantity ' || TO_CHAR(txn_remaining_qty_rma_uom));
1397 asn_debug.put_line('RMA UOM ' || x_rma_line_record.unit_of_measure);
1398 END IF;
1399 END IF; --}
1400
1401 IF insert_into_table THEN --{ allocate part of the txn qty to this line
1402 -- record where we are allocating the qty from
1403 temp_cascaded_table(temp_cascaded_table.LAST).oe_order_line_id := x_rma_line_record.oe_order_line_id;
1404
1405 -- allocate as much of the txn qty to this line as possible
1406 /* Bug 3423602.
1407 * rma_line_qty is in terms of rma uom. We need to
1408 * compare it with txn_remaining_qty_rma_uom and not with
1409 * txn_remaining_qty which was what we were doing before.
1410 * Changed the code in this procedure to change txn_remaining_qty
1411 * to txn_remaining_qty_rma_uom wherever necessary.
1412 */
1413 IF rma_line_qty < txn_remaining_qty_rma_uom THEN
1414 allocate_qty := rma_line_qty;
1415 ELSE
1416 allocate_qty := txn_remaining_qty_rma_uom;
1417 END IF;
1418
1419 IF (g_asn_debug = 'Y') THEN
1420 asn_debug.put_line('Quantity to allocate to this line: ' || allocate_qty);
1421 END IF;
1422
1423 --bug 4505906, record the used quantity in this session
1424 g_used_rma_line_amounts(x_rma_line_record.oe_order_line_id) := already_allocated_qty + allocate_qty;
1425
1426 /* source_doc_quantity -> in rma_uom
1427 primary_quantity -> in primary_uom
1428 quantity -> in txn uom */
1429 temp_cascaded_table(temp_cascaded_table.LAST).source_doc_quantity := allocate_qty; -- in rma uom
1430 temp_cascaded_table(temp_cascaded_table.LAST).source_doc_unit_of_measure := x_rma_line_record.unit_of_measure;
1431
1432 -- bug 1363369 fix carried forward FROM bug# 1337314
1433 -- No need to do the following conversion if the cursor returns one row
1434 -- for a corresponding record in the interface, as the quantity is already in asn uom.
1435 -- If the cursor fetches more than one row then the quantity in the interface will be
1436 -- distributed accross the fetched rows and hence need to do the following conversion.
1437 IF rma_lines%ROWCOUNT > 1 THEN
1438 temp_cascaded_table(temp_cascaded_table.LAST).quantity := rcv_transactions_interface_sv.convert_into_correct_qty(allocate_qty,
1439 x_rma_line_record.unit_of_measure,
1440 temp_cascaded_table(temp_cascaded_table.LAST).item_id,
1441 temp_cascaded_table(temp_cascaded_table.LAST).unit_of_measure
1442 );
1443 END IF;
1444
1445 -- Primary qty in Primary UOM
1446 temp_cascaded_table(temp_cascaded_table.LAST).primary_quantity := rcv_transactions_interface_sv.convert_into_correct_qty(allocate_qty,
1447 x_rma_line_record.unit_of_measure,
1448 temp_cascaded_table(temp_cascaded_table.LAST).item_id,
1449 temp_cascaded_table(temp_cascaded_table.LAST).primary_unit_of_measure
1450 );
1451 temp_cascaded_table(temp_cascaded_table.LAST).tax_amount := ROUND(temp_cascaded_table(temp_cascaded_table.LAST).quantity * tax_amount_factor, 4);
1452
1453 IF (g_asn_debug = 'Y') THEN
1454 asn_debug.put_line('Current Tax Amount ' || TO_CHAR(temp_cascaded_table(temp_cascaded_table.LAST).tax_amount));
1455 END IF;
1456
1457 -- update the remaining quantity
1458 txn_remaining_qty_rma_uom := txn_remaining_qty_rma_uom - allocate_qty;
1459 txn_remaining_qty := rcv_transactions_interface_sv.convert_into_correct_qty(txn_remaining_qty_rma_uom,
1460 x_rma_line_record.unit_of_measure,
1461 temp_cascaded_table(1).item_id,
1462 temp_cascaded_table(1).unit_of_measure
1463 );
1464 ELSE -- }{ matches if insert_into_table
1465 -- remove the row if the current line is not matched to the txn
1466 temp_cascaded_table.DELETE(temp_cascaded_table.COUNT);
1467 END IF; --} matches if insert_into_table
1468 END LOOP;
1469
1470 --}
1471
1472 -- finished processing all lines
1473 IF (g_asn_debug = 'Y') THEN
1474 asn_debug.put_line('Hit exit condition');
1475 asn_debug.put_line('Temp table size ' || TO_CHAR(temp_cascaded_table.COUNT));
1476 asn_debug.put_line('Rows fetched ' || TO_CHAR(rma_lines_fetched));
1477 END IF;
1478
1479 -- if nothing was processed, find out why, and quit
1480 IF x_cascaded_table(n).quantity > 0
1481 AND txn_remaining_qty = x_cascaded_table(n).quantity THEN --{
1482 IF rma_lines_fetched = 0 THEN
1483 IF (g_asn_debug = 'Y') THEN
1484 asn_debug.put_line('No rows were retrieved from cursor.');
1485 END IF;
1486 ELSE
1487 IF (g_asn_debug = 'Y') THEN
1488 asn_debug.put_line('No rows were cascaded');
1489 END IF;
1490 END IF;
1491
1492 BEGIN
1493 SELECT NVL(oeh.open_flag, 'N'),
1494 NVL(oel.line_category_code, 'N'),
1495 NVL(oel.open_flag, 'N'),
1496 NVL(oel.inventory_item_id, 0),
1497 NVL(mci.customer_item_number, 'N'),
1498 NVL(oel.booked_flag, 'N'),
1499 NVL(oel.flow_status_code, 'N'),
1500 oel.ordered_quantity,
1501 NVL(oel.shipped_quantity, 0)
1502 INTO x_header_open_flag,
1503 x_line_category_code,
1504 x_line_open_flag,
1505 x_item_id,
1506 x_customer_item_num,
1507 x_booked_flag,
1508 x_flow_status_code,
1509 x_ordered_quantity,
1510 x_shipped_quantity
1511 FROM oe_order_headers_all oeh,
1512 oe_order_lines_all oel,
1513 mtl_customer_items mci
1514 WHERE oeh.header_id = x_cascaded_table(n).oe_order_header_id
1515 AND oeh.header_id = oel.header_id
1516 AND oel.line_number = NVL(x_cascaded_table(n).oe_order_line_num, oel.line_number)
1517 AND oel.inventory_item_id = NVL(x_cascaded_table(n).item_id, oel.inventory_item_id)
1518 AND oel.ordered_item_id = mci.customer_item_id(+);
1519
1520 IF x_item_id <> NVL(temp_cascaded_table(temp_cascaded_table.COUNT).item_id, x_item_id) THEN
1521 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1522 rcv_error_pkg.set_error_message('PO_PDOI_DERV_ERROR', x_cascaded_table(n).error_message);
1523 rcv_error_pkg.set_token('COLUMN', 'ITEM_NUM');
1524 rcv_error_pkg.set_token('VALUE', temp_cascaded_table(temp_cascaded_table.COUNT).item_num);
1525 rcv_error_pkg.log_interface_error('ITEM_NUM', FALSE);
1526 ELSIF x_ship_to_organization_id <> NVL(temp_cascaded_table(temp_cascaded_table.COUNT).to_organization_id, x_ship_to_organization_id) THEN
1527 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1528 rcv_error_pkg.set_error_message('PO_PDOI_DERV_ERROR', x_cascaded_table(n).error_message);
1529 rcv_error_pkg.set_token('COLUMN', 'TO_ORGANIZATION_CODE');
1530 rcv_error_pkg.set_token('VALUE', temp_cascaded_table(temp_cascaded_table.COUNT).to_organization_code);
1531 rcv_error_pkg.log_interface_error('TO_ORGANIZATION_CODE', FALSE);
1532 ELSIF x_ship_to_location_id <> NVL(NVL(temp_cascaded_table(temp_cascaded_table.COUNT).ship_to_location_id, x_header_record.header_record.location_id), x_ship_to_location_id) THEN
1533 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1534 rcv_error_pkg.set_error_message('PO_PDOI_DERV_ERROR', x_cascaded_table(n).error_message);
1535 rcv_error_pkg.set_token('COLUMN', 'SHIP_TO_LOCATION_CODE');
1536 rcv_error_pkg.set_token('VALUE', temp_cascaded_table(temp_cascaded_table.COUNT).ship_to_location_code);
1537 rcv_error_pkg.log_interface_error('SHIP_TO_LOCATION_CODE', FALSE);
1538 ELSIF x_header_open_flag <> 'Y' THEN
1539 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1540 rcv_error_pkg.set_error_message('PO_PDOI_DERV_ERROR', x_cascaded_table(n).error_message);
1541 rcv_error_pkg.set_token('COLUMN', 'OPEN_FLAG');
1542 rcv_error_pkg.set_token('VALUE', x_header_open_flag);
1543 rcv_error_pkg.log_interface_error('OE_ORDER_HEADER_ID', FALSE);
1544 ELSIF x_line_category_code <> 'RETURN' THEN
1545 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1546 rcv_error_pkg.set_error_message('PO_PDOI_DERV_ERROR', x_cascaded_table(n).error_message);
1547 rcv_error_pkg.set_token('COLUMN', 'LINE_CATEGORY_CODE');
1548 rcv_error_pkg.set_token('VALUE', x_line_category_code);
1549 rcv_error_pkg.log_interface_error('OE_ORDER_HEADER_ID', FALSE);
1550 ELSIF x_line_open_flag <> 'Y' THEN
1551 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1552 rcv_error_pkg.set_error_message('PO_PDOI_DERV_ERROR', x_cascaded_table(n).error_message);
1553 rcv_error_pkg.set_token('COLUMN', 'OPEN_FLAG');
1554 rcv_error_pkg.set_token('VALUE', x_header_open_flag);
1555 rcv_error_pkg.log_interface_error('OE_ORDER_HEADER_ID', FALSE);
1556 ELSIF x_customer_item_num <> NVL(temp_cascaded_table(temp_cascaded_table.COUNT).customer_item_num, x_customer_item_num) THEN
1557 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1558 rcv_error_pkg.set_error_message('PO_PDOI_DERV_ERROR', x_cascaded_table(n).error_message);
1559 rcv_error_pkg.set_token('COLUMN', 'CUSTOMER_ITEM_NUM');
1560 rcv_error_pkg.set_token('VALUE', temp_cascaded_table(temp_cascaded_table.COUNT).customer_item_num);
1561 rcv_error_pkg.log_interface_error('CUSTOMER_ITEM_NUM', FALSE);
1562 ELSIF x_booked_flag <> 'Y' THEN
1563 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1564 rcv_error_pkg.set_error_message('PO_PDOI_DERV_ERROR', x_cascaded_table(n).error_message);
1565 rcv_error_pkg.set_token('COLUMN', 'BOOKED_FLAG');
1566 rcv_error_pkg.set_token('VALUE', x_booked_flag);
1567 rcv_error_pkg.log_interface_error('OE_ORDER_HEADER_ID', FALSE);
1568 ELSIF x_flow_status_code <> 'AWAITING RETURN' THEN
1569 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1570 rcv_error_pkg.set_error_message('PO_PDOI_DERV_ERROR', x_cascaded_table(n).error_message);
1571 rcv_error_pkg.set_token('COLUMN', 'FLOW_STATUS_CODE');
1572 rcv_error_pkg.set_token('VALUE', x_flow_status_code);
1573 rcv_error_pkg.log_interface_error('OE_ORDER_HEADER_ID', FALSE);
1574 ELSIF x_ordered_quantity < x_shipped_quantity THEN
1575 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1576 rcv_error_pkg.set_error_message('PO_PDOI_DERV_ERROR', x_cascaded_table(n).error_message);
1577 rcv_error_pkg.set_token('COLUMN', 'SHIPPED_QUANTITY');
1578 rcv_error_pkg.set_token('VALUE', x_shipped_quantity);
1579 rcv_error_pkg.log_interface_error('OE_ORDER_HEADER_ID', FALSE);
1580 ELSE
1581 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1582 rcv_error_pkg.set_error_message('PO_PDOI_DERV_ERROR', x_cascaded_table(n).error_message);
1583 rcv_error_pkg.set_token('COLUMN', 'OE_ORDER_HEADER_ID');
1584 rcv_error_pkg.set_token('VALUE', x_cascaded_table(n).oe_order_header_id);
1585 rcv_error_pkg.log_interface_error('OE_ORDER_HEADER_ID', FALSE);
1586 END IF;
1587 EXCEPTION
1588 WHEN NO_DATA_FOUND THEN
1589 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1590 rcv_error_pkg.set_error_message('PO_PDOI_DERV_ERROR', x_cascaded_table(n).error_message);
1591 rcv_error_pkg.set_token('COLUMN', 'OE_ORDER_HEADER_ID');
1592 rcv_error_pkg.set_token('VALUE', x_cascaded_table(n).oe_order_header_id);
1593 rcv_error_pkg.log_interface_error('OE_ORDER_HEADER_ID', FALSE);
1594 END;
1595
1596 -- Delete the temp_cascaded_table and return
1597 temp_cascaded_table.DELETE;
1598 ELSIF txn_remaining_qty > 0 THEN
1599 -- }{
1600 -- something was processed, check for overtolerance
1601
1602 -- get tolerable qty
1603
1604 --<R12 MOAC>
1605 /* get_rma_tolerances procedure is not operating unit context sensitive.
1606 Removed the call to fnd_global.apps_initialize */
1607
1608 /* Bug 5660538: Removed references to x_rma_line_record */
1609
1610 oe_rma_receiving.get_rma_tolerances(temp_cascaded_table(temp_cascaded_table.LAST).oe_order_line_id,
1611 x_under_return_tolerance,
1612 x_qty_rcv_tolerance,
1613 x_oe_return_status,
1614 x_oe_msg_count,
1615 x_oe_msg_data
1616 );
1617
1618 -- check remaining qty vs tolerance qty using the last rma line's tolerance
1619 IF (txn_remaining_qty_rma_uom > rma_line_qty * x_qty_rcv_tolerance / 100) THEN
1620 -- the txn qty exceeds the tolerable qty
1621 IF (g_asn_debug = 'Y') THEN
1622 asn_debug.put_line('Extra Txn UOM Quantity ' || TO_CHAR(txn_remaining_qty));
1623 asn_debug.put_line('Extra RMA UOM Quantity ' || TO_CHAR(txn_remaining_qty_rma_uom));
1624 asn_debug.put_line('delete the temp table ');
1625 END IF;
1626
1627 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1628 rcv_error_pkg.set_error_message('RCV_SHIP_QTY_OVER_TOLERANCE', x_cascaded_table(n).error_message);
1629 rcv_error_pkg.set_token('QTY_A', x_cascaded_table(n).quantity);
1630 rcv_error_pkg.set_token('QTY_B', x_cascaded_table(n).quantity - txn_remaining_qty);
1631 rcv_error_pkg.log_interface_error('QUANTITY', FALSE);
1632 temp_cascaded_table.DELETE;
1633
1634 IF (g_asn_debug = 'Y') THEN
1635 asn_debug.put_line('mark the actual table with error status');
1636 asn_debug.put_line('Error Status ' || x_cascaded_table(n).error_status);
1637 asn_debug.put_line('Error message ' || x_cascaded_table(n).error_message);
1638 END IF;
1639 ELSE
1640 -- }{ the txn qty does not exceed tolerance, allocate remaining to last row
1641 IF (g_asn_debug = 'Y') THEN
1642 asn_debug.put_line('txn qty does not exceed tolerance');
1643 END IF;
1644
1645 /** Bug 5408054:
1646 * When the cursor 'rma_lines' fetches only 1 record, then transaction qty is not getting
1647 * modified, so we should not add again the remaining quanity with the transaction
1648 * quanity, as it will result in exceeding the transaction quantity entered by the
1649 * the user and also transaction will fail due to exceeding the over tolerance limit.
1650 * When the cursor 'rma_lines' fetches more than 1 record, then only transaction
1651 * quantity( ordered quantity + tolerance qty) is set to ordered quanity, in that
1652 * case we have to sum the remaining quantity.
1653 * So, we have to add the remaining qty with transaction qty, only when the
1654 * the number of records fetched by the cursor 'rma_lines' is greater than 1.
1655 */
1656 IF rma_lines_fetched > 1 THEN
1657 temp_cascaded_table(temp_cascaded_table.LAST).quantity := temp_cascaded_table(temp_cascaded_table.LAST).quantity + txn_remaining_qty;
1658 END IF;
1659 temp_cascaded_table(temp_cascaded_table.LAST).primary_quantity := temp_cascaded_table(temp_cascaded_table.LAST).primary_quantity
1660 + rcv_transactions_interface_sv.convert_into_correct_qty(txn_remaining_qty,
1661 temp_cascaded_table(temp_cascaded_table.LAST).unit_of_measure,
1662 temp_cascaded_table(temp_cascaded_table.LAST).item_id,
1663 temp_cascaded_table(temp_cascaded_table.LAST).primary_unit_of_measure
1664 );
1665 temp_cascaded_table(temp_cascaded_table.LAST).source_doc_quantity := temp_cascaded_table(temp_cascaded_table.LAST).source_doc_quantity
1666 + rcv_transactions_interface_sv.convert_into_correct_qty(txn_remaining_qty,
1667 temp_cascaded_table(temp_cascaded_table.LAST).unit_of_measure,
1668 temp_cascaded_table(temp_cascaded_table.LAST).item_id,
1669 temp_cascaded_table(temp_cascaded_table.LAST).source_doc_unit_of_measure
1670 );
1671 END IF; -- } end if remaining > tolerance
1672 END IF; --} end if remaining > 0
1673
1674 -- successful execution
1675
1676 IF txn_remaining_qty = 0 THEN
1677 IF (g_asn_debug = 'Y') THEN
1678 asn_debug.put_line('Remaining Txn UOM quantity is zero ' || TO_CHAR(txn_remaining_qty));
1679 asn_debug.put_line('Remaining RMA UOM quantity is zero ' || TO_CHAR(txn_remaining_qty_rma_uom));
1680 asn_debug.put_line('Return the cascaded rows back to the calling procedure');
1681 END IF;
1682 END IF;
1683
1684 -- OPM change.Bug# 3061052
1685 -- if original receiving transaction line is split and secondary quantity is specified then
1686 -- set secondary quantity for the split lines to NULL.
1687
1688 /* INVCONV , remove OPM installation checks */
1689 IF x_cascaded_table(n).error_status IN('S', 'W')
1690 /* AND gml_process_flags.opm_installed = 1 */
1691 AND x_cascaded_table(n).secondary_quantity IS NOT NULL THEN
1692 IF temp_cascaded_table.COUNT > 1 THEN
1693 FOR j IN 1 .. temp_cascaded_table.COUNT LOOP
1694 temp_cascaded_table(j).secondary_quantity := NULL;
1695 END LOOP;
1696 END IF;
1697 END IF;
1698 /* end , INVCONV*/
1699 IF (g_asn_debug = 'Y') THEN
1700 asn_debug.put_line('Exit explode_line_quantity');
1701 END IF;
1702 EXCEPTION
1703 WHEN OTHERS THEN
1704 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1705 rcv_error_pkg.set_sql_error_message('explode_line_quantity', x_progress);
1706 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
1707
1708 IF (g_asn_debug = 'Y') THEN
1709 asn_debug.put_line(TO_CHAR(n));
1710 asn_debug.put_line(SQLERRM);
1711 asn_debug.put_line('error ' || x_progress);
1712 END IF;
1713 END explode_line_quantity;
1714
1715 PROCEDURE default_source_info(
1716 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1717 n IN BINARY_INTEGER,
1718 x_header_id IN rcv_headers_interface.header_interface_id%TYPE
1719 ) IS
1720 BEGIN
1721 x_cascaded_table(n).header_interface_id := x_header_id;
1722
1723 --x_cascaded_table(n).shipment_line_status_code := 'OPEN';
1724
1725 IF x_cascaded_table(n).source_document_code IS NULL THEN
1726 x_cascaded_table(n).source_document_code := 'RMA';
1727
1728 IF (g_asn_debug = 'Y') THEN
1729 asn_debug.put_line('Defaulting SOURCE_DOCUMENT_CODE ' || x_cascaded_table(n).source_document_code);
1730 END IF;
1731 END IF;
1732
1733 /* Bug3593237 - START */
1734 /*
1735 Current Location was not getting displayed in Receiving Transactions
1736 form since location_id was not defaulted when it was null.
1737 Defaulting location_id from deliver_to_location_id
1738 incase of deliver transaction and from ship_to_location_id for all other
1739 transactions because in any case other than deliver transaction
1740 ship_to_location_id should get displayed as the current location in
1741 Receiving Transactions form.
1742 */
1743 IF (x_cascaded_table(n).location_id IS NULL) THEN
1744 IF ( NVL(x_cascaded_table(n).auto_transact_code, 'RECEIVE') = 'DELIVER'
1745 OR x_cascaded_table(n).transaction_type = 'DELIVER') THEN
1746 x_cascaded_table(n).location_id := x_cascaded_table(n).deliver_to_location_id;
1747
1748 IF (g_asn_debug = 'Y') THEN
1749 asn_debug.put_line('Defaulting LOCATION_ID ' || x_cascaded_table(n).deliver_to_location_id);
1750 END IF;
1751 ELSE
1752 x_cascaded_table(n).location_id := x_cascaded_table(n).ship_to_location_id;
1753
1754 IF (g_asn_debug = 'Y') THEN
1755 asn_debug.put_line('Defaulting LOCATION_ID ' || x_cascaded_table(n).ship_to_location_id);
1756 END IF;
1757 END IF;
1758 END IF;
1759 /* Bug3593237 - END */
1760 END default_source_info;
1761
1762 PROCEDURE default_destination_info(
1763 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1764 n IN BINARY_INTEGER
1765 ) IS
1766 BEGIN
1767 IF x_cascaded_table(n).destination_type_code IS NULL
1768 OR ( x_cascaded_table(n).destination_type_code = 'INVENTORY'
1769 AND x_cascaded_table(n).auto_transact_code = 'RECEIVE') THEN
1770 x_cascaded_table(n).destination_type_code := 'RECEIVING';
1771
1772 IF (g_asn_debug = 'Y') THEN
1773 asn_debug.put_line('Defaulting DESTINATION_TYPE_CODE ' || x_cascaded_table(n).destination_type_code);
1774 END IF;
1775 END IF;
1776
1777 /* Bug 3592340.
1778 * If auto_transact_code is DELIVER and the transaction type is
1779 * RECEIVE, then this means we need to do direct delivery and hence
1780 * the destination_type_code needs to be INVENTORY and not
1781 * RECEIVING. So default it to INVENTORY.
1782 */
1783 IF ( x_cascaded_table(n).transaction_type = 'RECEIVE'
1784 AND x_cascaded_table(n).auto_transact_code = 'DELIVER') THEN
1785 x_cascaded_table(n).destination_type_code := 'INVENTORY';
1786
1787 IF (g_asn_debug = 'Y') THEN
1788 asn_debug.put_line('Defaulting DESTINATION_TYPE_CODE for direct delivery ' || x_cascaded_table(n).destination_type_code);
1789 END IF;
1790 END IF;
1791
1792 IF x_cascaded_table(n).destination_context IS NULL THEN
1793 x_cascaded_table(n).destination_context := x_cascaded_table(n).destination_type_code;
1794
1795 IF (g_asn_debug = 'Y') THEN
1796 asn_debug.put_line('Defaulting DESTINATION_CONTEXT ' || x_cascaded_table(n).destination_context);
1797 END IF;
1798 END IF;
1799 END default_destination_info;
1800
1801 PROCEDURE default_transaction_info(
1802 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1803 n IN BINARY_INTEGER
1804 ) IS
1805 BEGIN
1806 IF x_cascaded_table(n).transaction_type IS NULL THEN
1807 x_cascaded_table(n).transaction_type := 'RECEIVE';
1808
1809 IF (g_asn_debug = 'Y') THEN
1810 asn_debug.put_line('Defaulting TRANSACTION_TYPE ' || x_cascaded_table(n).transaction_type);
1811 END IF;
1812 END IF;
1813 END default_transaction_info;
1814
1815 PROCEDURE default_processing_info(
1816 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1817 n IN BINARY_INTEGER
1818 ) IS
1819 BEGIN
1820 IF x_cascaded_table(n).processing_mode_code IS NULL THEN
1821 x_cascaded_table(n).processing_mode_code := 'BATCH';
1822
1823 IF (g_asn_debug = 'Y') THEN
1824 asn_debug.put_line('Defaulting PROCESSING_MODE_CODE ' || x_cascaded_table(n).processing_mode_code);
1825 END IF;
1826 END IF;
1827
1828 x_cascaded_table(n).processing_status_code := 'RUNNING';
1829
1830 IF x_cascaded_table(n).processing_status_code IS NULL THEN
1831 -- This has to be set to running otherwise C code in rvtbm
1832 -- will not pick it up
1833 x_cascaded_table(n).processing_status_code := 'RUNNING';
1834
1835 IF (g_asn_debug = 'Y') THEN
1836 asn_debug.put_line('Defaulting PROCESSING_STATUS_CODE ' || x_cascaded_table(n).processing_status_code);
1837 END IF;
1838 END IF;
1839 END default_processing_info;
1840
1841 PROCEDURE default_routing_info(
1842 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1843 n IN BINARY_INTEGER
1844 ) IS
1845 x_inspection_required_flag VARCHAR2(1);
1846 BEGIN
1847 /* Bug 3228851 - get the default rma routing from rcv_parameters */
1848 SELECT NVL(MIN(inspection_required_flag), 'N')
1849 INTO x_inspection_required_flag
1850 FROM oe_po_enter_receipts_v
1851 WHERE oe_order_header_id = x_cascaded_table(n).oe_order_header_id
1852 AND item_id = x_cascaded_table(n).item_id;
1853
1854 IF (x_inspection_required_flag = 'Y') THEN
1855 x_cascaded_table(n).routing_header_id := 2;
1856 ELSIF x_cascaded_table(n).routing_header_id IS NULL THEN
1857 SELECT NVL(MIN(rma_receipt_routing_id), 1)
1858 INTO x_cascaded_table(n).routing_header_id
1859 FROM rcv_parameters
1860 WHERE organization_id = x_cascaded_table(n).to_organization_id;
1861
1862 IF (g_asn_debug = 'Y') THEN
1863 asn_debug.put_line('Defaulted routing_header_id ' || x_cascaded_table(n).routing_header_id);
1864 END IF;
1865 END IF;
1866
1867 IF x_cascaded_table(n).routing_step_id IS NULL THEN
1868 x_cascaded_table(n).routing_step_id := 1;
1869
1870 IF (g_asn_debug = 'Y') THEN
1871 asn_debug.put_line('Defaulting routing_step_id ' || x_cascaded_table(n).routing_step_id);
1872 END IF;
1873 END IF;
1874 END default_routing_info;
1875
1876 PROCEDURE default_from_header(
1877 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1878 n IN BINARY_INTEGER,
1879 x_header_record IN rcv_roi_preprocessor.header_rec_type
1880 ) IS
1881 BEGIN
1882 default_customer_header(x_cascaded_table,
1883 n,
1884 x_header_record
1885 );
1886 default_customer_site_header(x_cascaded_table,
1887 n,
1888 x_header_record
1889 );
1890 default_from_org_header(x_cascaded_table,
1891 n,
1892 x_header_record
1893 );
1894 -- default_to_org_header(x_cascaded_table, n, x_header_record);
1895 default_ship_to_header(x_cascaded_table,
1896 n,
1897 x_header_record
1898 );
1899 default_currency_info_header(x_cascaded_table,
1900 n,
1901 x_header_record
1902 );
1903 default_shipment_num_header(x_cascaded_table,
1904 n,
1905 x_header_record
1906 );
1907 default_freight_carrier_header(x_cascaded_table,
1908 n,
1909 x_header_record
1910 );
1911 default_bill_of_lading_header(x_cascaded_table,
1912 n,
1913 x_header_record
1914 );
1915 default_packing_slip_header(x_cascaded_table,
1916 n,
1917 x_header_record
1918 );
1919 default_ship_date_header(x_cascaded_table,
1920 n,
1921 x_header_record
1922 );
1923 default_receipt_date_header(x_cascaded_table,
1924 n,
1925 x_header_record
1926 );
1927 default_num_containers_header(x_cascaded_table,
1928 n,
1929 x_header_record
1930 );
1931 default_waybill_header(x_cascaded_table,
1932 n,
1933 x_header_record
1934 );
1935 default_tax_name_header(x_cascaded_table,
1936 n,
1937 x_header_record
1938 );
1939 END default_from_header;
1940
1941 PROCEDURE default_item_info(
1942 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1943 n IN BINARY_INTEGER
1944 ) IS
1945 item_id_record rcv_shipment_line_sv.item_id_record_type;
1946 l_category_set_id mtl_category_sets_b.category_set_id%TYPE;
1947 BEGIN
1948 -- default the item_revision
1949
1950 /* Bug 3299421 : WMS Mobile applications do not need the item_revision to
1951 be defaulted during preprocessing for transactions other than
1952 "Deliver". Added the condition in the If clause where we
1953 check if the transaction is from mobile and if so do not
1954 default the item revision.
1955 */
1956 IF x_cascaded_table(n).item_revision IS NULL
1957 AND (NVL(x_cascaded_table(n).mobile_txn, 'N') = 'N')
1958 AND x_cascaded_table(n).error_status IN('S', 'W') THEN
1959 IF (g_asn_debug = 'Y') THEN
1960 asn_debug.put_line('Defaulting item revision');
1961 END IF;
1962
1963 item_id_record.item_id := x_cascaded_table(n).item_id;
1964 item_id_record.po_line_id := x_cascaded_table(n).oe_order_line_id;
1965 item_id_record.to_organization_id := x_cascaded_table(n).to_organization_id;
1966 item_id_record.item_revision := x_cascaded_table(n).item_revision;
1967 item_id_record.error_record.error_status := 'S';
1968 item_id_record.error_record.error_message := NULL;
1969 default_item_revision(item_id_record);
1970 x_cascaded_table(n).item_revision := item_id_record.item_revision;
1971
1972 IF (g_asn_debug = 'Y') THEN
1973 asn_debug.put_line(NVL(item_id_record.item_revision, 'Item Revision is null'));
1974 END IF;
1975
1976 x_cascaded_table(n).error_status := item_id_record.error_record.error_status;
1977 x_cascaded_table(n).error_message := item_id_record.error_record.error_message;
1978 END IF;
1979
1980 -- default the category_id
1981 IF x_cascaded_table(n).error_status IN('S', 'W')
1982 AND x_cascaded_table(n).category_id IS NULL THEN
1983 IF (g_asn_debug = 'Y') THEN
1984 asn_debug.put_line('Defaulting item category id');
1985 END IF;
1986
1987 -- get the default category_set_id for PO
1988 -- refer to INIT_RCV_CONTROL_BLOCK in POXCOSEU.pld, which eventually calls PO_CORE_S.get_item_category_structure
1989 SELECT category_set_id
1990 INTO l_category_set_id
1991 FROM mtl_default_category_sets
1992 WHERE functional_area_id = 2;
1993
1994 -- get the category_id for this item, org, and category_set
1995 -- based on RCV_RECEIPTS_EH.event('POST-QUERY')
1996 SELECT MAX(category_id)
1997 INTO x_cascaded_table(n).category_id
1998 FROM mtl_item_categories
1999 WHERE inventory_item_id = x_cascaded_table(n).item_id
2000 AND organization_id = x_cascaded_table(n).to_organization_id
2001 AND category_set_id = l_category_set_id;
2002
2003 IF (g_asn_debug = 'Y') THEN
2004 asn_debug.put_line('Defaulted category_id ' || x_cascaded_table(n).category_id);
2005 END IF;
2006 END IF;
2007 EXCEPTION
2008 WHEN OTHERS THEN
2009 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_unexp_error;
2010 rcv_error_pkg.set_sql_error_message('default_item_info', '000');
2011 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
2012 END default_item_info;
2013
2014 PROCEDURE default_from_rma(
2015 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2016 n IN BINARY_INTEGER
2017 ) IS
2018 default_rma_record default_rma%ROWTYPE;
2019 BEGIN
2020 IF x_cascaded_table(n).error_status IN('S', 'W')
2021 AND x_cascaded_table(n).oe_order_line_id IS NOT NULL THEN
2022 OPEN default_rma(x_cascaded_table(n).oe_order_line_id);
2023 FETCH default_rma INTO default_rma_record;
2024
2025 -- default the receiving org info
2026 IF x_cascaded_table(n).to_organization_id IS NULL THEN
2027 x_cascaded_table(n).to_organization_id := default_rma_record.to_organization_id;
2028 END IF;
2029
2030 -- default the customer info
2031 IF x_cascaded_table(n).customer_id IS NULL THEN
2032 x_cascaded_table(n).customer_id := default_rma_record.customer_id;
2033 END IF;
2034
2035 IF x_cascaded_table(n).customer_site_id IS NULL THEN
2036 x_cascaded_table(n).customer_site_id := default_rma_record.customer_site_id;
2037 END IF;
2038
2039 -- default currency info
2040 IF x_cascaded_table(n).currency_code IS NULL THEN
2041 x_cascaded_table(n).currency_code := default_rma_record.currency_code;
2042 x_cascaded_table(n).currency_conversion_type := default_rma_record.currency_conversion_type;
2043 x_cascaded_table(n).currency_conversion_rate := default_rma_record.currency_conversion_rate;
2044 x_cascaded_table(n).currency_conversion_date := default_rma_record.currency_conversion_date;
2045 END IF;
2046
2047 -- default pricing info
2048 IF x_cascaded_table(n).po_unit_price IS NULL THEN
2049 x_cascaded_table(n).po_unit_price := default_rma_record.unit_price;
2050 END IF;
2051
2052 -- default item description
2053 IF x_cascaded_table(n).item_description IS NULL THEN
2054 x_cascaded_table(n).item_description := default_rma_record.item_description;
2055 END IF;
2056
2057 -- default destination_info
2058 IF x_cascaded_table(n).destination_type_code IS NULL THEN
2059 x_cascaded_table(n).destination_type_code := 'RECEIVING';
2060 END IF;
2061
2062 IF x_cascaded_table(n).destination_context IS NULL THEN
2063 x_cascaded_table(n).destination_context := x_cascaded_table(n).destination_type_code;
2064 END IF;
2065
2066 -- bug 3592327
2067 IF x_cascaded_table(n).subinventory IS NULL THEN
2068 x_cascaded_table(n).subinventory := default_rma_record.subinventory;
2069 END IF;
2070
2071 -- bug 3592327
2072 IF (x_cascaded_table(n).deliver_to_location_id IS NULL) THEN
2073 IF ( NVL(x_cascaded_table(n).auto_transact_code, 'RECEIVE') = 'DELIVER'
2074 OR x_cascaded_table(n).transaction_type = 'DELIVER') THEN
2075 x_cascaded_table(n).deliver_to_location_id := default_rma_record.deliver_to_location_id;
2076 END IF;
2077 END IF;
2078
2079 CLOSE default_rma;
2080 END IF;
2081 EXCEPTION
2082 WHEN OTHERS THEN
2083 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_unexp_error;
2084 rcv_error_pkg.set_sql_error_message('default_from_rma', '000');
2085 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
2086 CLOSE default_rma;
2087 END default_from_rma;
2088
2089 PROCEDURE default_ship_to_info_rma(
2090 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2091 n IN BINARY_INTEGER,
2092 default_rma_record IN default_rma%ROWTYPE
2093 ) IS
2094 BEGIN
2095 -- ship_to_org
2096 IF ( x_cascaded_table(n).error_status IN('S', 'W')
2097 AND x_cascaded_table(n).to_organization_id IS NULL
2098 AND default_rma_record.to_organization_id IS NOT NULL) THEN
2099 IF (g_asn_debug = 'Y') THEN
2100 asn_debug.put_line('Defaulting org id from default RMA');
2101 END IF;
2102
2103 x_cascaded_table(n).to_organization_id := default_rma_record.to_organization_id;
2104 END IF;
2105 END default_ship_to_info_rma;
2106
2107 PROCEDURE default_customer_header(
2108 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2109 n IN BINARY_INTEGER,
2110 x_header_record IN rcv_roi_preprocessor.header_rec_type
2111 ) IS
2112 BEGIN
2113 IF ( x_cascaded_table(n).error_status IN('S', 'W')
2114 AND x_cascaded_table(n).customer_id IS NULL) THEN
2115 IF (x_header_record.header_record.customer_id IS NOT NULL) THEN
2116 IF (g_asn_debug = 'Y') THEN
2117 asn_debug.put_line('Defaulting customer info from header');
2118 END IF;
2119
2120 x_cascaded_table(n).customer_id := x_header_record.header_record.customer_id;
2121 ELSE
2122 IF (g_asn_debug = 'Y') THEN
2123 asn_debug.put_line('No customer info available');
2124 END IF;
2125
2126 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
2127 rcv_error_pkg.set_error_message('PO_PDOI_DERV_ERROR', x_cascaded_table(n).error_message);
2128 rcv_error_pkg.set_token('COLUMN', 'CUSTOMER_ID');
2129 rcv_error_pkg.set_token('VALUE', x_header_record.header_record.customer_id);
2130 rcv_error_pkg.log_interface_error('CUSTOMER_ID', FALSE);
2131 END IF;
2132 END IF;
2133 END default_customer_header;
2134
2135 PROCEDURE default_customer_site_header(
2136 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2137 n IN BINARY_INTEGER,
2138 x_header_record IN rcv_roi_preprocessor.header_rec_type
2139 ) IS
2140 BEGIN
2141 IF ( x_cascaded_table(n).error_status IN('S', 'W')
2142 AND x_cascaded_table(n).customer_site_id IS NULL) THEN
2143 IF (x_header_record.header_record.customer_site_id IS NOT NULL) THEN
2144 IF (g_asn_debug = 'Y') THEN
2145 asn_debug.put_line('Defaulting customer site info from header');
2146 END IF;
2147
2148 x_cascaded_table(n).customer_site_id := x_header_record.header_record.customer_site_id;
2149 ELSE
2150 IF (g_asn_debug = 'Y') THEN
2151 asn_debug.put_line('No customer site info available');
2152 END IF;
2153
2154 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
2155 rcv_error_pkg.set_error_message('PO_PDOI_DERV_ERROR', x_cascaded_table(n).error_message);
2156 rcv_error_pkg.set_token('COLUMN', 'CUSTOMER_SITE_ID');
2157 rcv_error_pkg.set_token('VALUE', x_header_record.header_record.customer_site_id);
2158 rcv_error_pkg.log_interface_error('CUSTOMER_SITE_ID', FALSE);
2159 END IF;
2160 END IF;
2161 END default_customer_site_header;
2162
2163 PROCEDURE default_from_org_header(
2164 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2165 n IN BINARY_INTEGER,
2166 x_header_record IN rcv_roi_preprocessor.header_rec_type
2167 ) IS
2168 BEGIN
2169 IF x_cascaded_table(n).from_organization_id IS NULL
2170 AND x_cascaded_table(n).from_organization_code IS NULL THEN
2171 x_cascaded_table(n).from_organization_id := x_header_record.header_record.from_organization_id;
2172 x_cascaded_table(n).from_organization_code := x_header_record.header_record.from_organization_code;
2173
2174 IF (g_asn_debug = 'Y') THEN
2175 asn_debug.put_line('Defaulting from HEADER FROM_ORGANIZATION_ID ' || TO_CHAR(x_cascaded_table(n).from_organization_id));
2176 asn_debug.put_line('Defaulting from HEADER FROM_ORGANIZATION_CODE ' || x_cascaded_table(n).from_organization_code);
2177 END IF;
2178 END IF;
2179 END default_from_org_header;
2180
2181 PROCEDURE default_ship_to_header(
2182 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2183 n IN BINARY_INTEGER,
2184 x_header_record IN rcv_roi_preprocessor.header_rec_type
2185 ) IS
2186 BEGIN
2187 -- ship_to_org
2188 IF x_cascaded_table(n).to_organization_id IS NULL
2189 AND x_cascaded_table(n).to_organization_code IS NULL THEN
2190 x_cascaded_table(n).to_organization_id := x_header_record.header_record.ship_to_organization_id;
2191 x_cascaded_table(n).to_organization_code := x_header_record.header_record.ship_to_organization_code;
2192
2193 IF (g_asn_debug = 'Y') THEN
2194 asn_debug.put_line('Defaulting from HEADER TO_ORGANIZATION_ID ' || TO_CHAR(x_cascaded_table(n).to_organization_id));
2195 asn_debug.put_line('Defaulting from HEADER TO_ORGANIZATION_CODE ' || x_cascaded_table(n).to_organization_code);
2196 END IF;
2197 END IF;
2198
2199 -- ship_to_location
2200 IF ( x_cascaded_table(n).ship_to_location_id IS NULL
2201 AND x_cascaded_table(n).ship_to_location_code IS NULL) THEN -- Check this with George
2202 x_cascaded_table(n).ship_to_location_code := x_header_record.header_record.location_code;
2203 x_cascaded_table(n).ship_to_location_id := x_header_record.header_record.location_id;
2204
2205 IF (g_asn_debug = 'Y') THEN
2206 asn_debug.put_line('Defaulting from HEADER LOCATION_ID ' || TO_CHAR(x_cascaded_table(n).location_id));
2207 END IF;
2208 END IF;
2209 END default_ship_to_header;
2210
2211 PROCEDURE default_currency_info_header(
2212 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2213 n IN BINARY_INTEGER,
2214 x_header_record IN rcv_roi_preprocessor.header_rec_type
2215 ) IS
2216 BEGIN
2217 IF x_cascaded_table(n).currency_code IS NULL
2218 AND x_cascaded_table(n).currency_conversion_type IS NULL
2219 AND x_cascaded_table(n).currency_conversion_rate IS NULL
2220 AND x_cascaded_table(n).currency_conversion_date IS NULL THEN
2221 x_cascaded_table(n).currency_code := x_header_record.header_record.currency_code;
2222 x_cascaded_table(n).currency_conversion_type := x_header_record.header_record.conversion_rate_type;
2223 x_cascaded_table(n).currency_conversion_rate := x_header_record.header_record.conversion_rate;
2224 x_cascaded_table(n).currency_conversion_date := x_header_record.header_record.conversion_rate_date;
2225
2226 IF (g_asn_debug = 'Y') THEN
2227 asn_debug.put_line('Defaulting from HEADER CURRENCY_CODE ' || x_cascaded_table(n).currency_code);
2228 asn_debug.put_line('Defaulting from HEADER CURRENCY_CONVERSION_TYPE ' || x_cascaded_table(n).currency_conversion_type);
2229 asn_debug.put_line('Defaulting from HEADER CURRENCY_CONVERSION_RATE ' || TO_CHAR(x_cascaded_table(n).currency_conversion_rate));
2230 asn_debug.put_line('Defaulting from HEADER CURRENCY_CONVERSION_DATE ' || TO_CHAR(x_cascaded_table(n).currency_conversion_date, 'DD/MM/YYYY'));
2231 END IF;
2232 END IF;
2233 END default_currency_info_header;
2234
2235 PROCEDURE default_shipment_num_header(
2236 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2237 n IN BINARY_INTEGER,
2238 x_header_record IN rcv_roi_preprocessor.header_rec_type
2239 ) IS
2240 BEGIN
2241 IF x_cascaded_table(n).shipment_num IS NULL THEN
2242 x_cascaded_table(n).shipment_num := x_header_record.header_record.shipment_num;
2243
2244 IF (g_asn_debug = 'Y') THEN
2245 asn_debug.put_line('Defaulting from HEADER SHIPMENT_NUM ' || x_cascaded_table(n).shipment_num);
2246 END IF;
2247 END IF;
2248 END default_shipment_num_header;
2249
2250 PROCEDURE default_freight_carrier_header(
2251 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2252 n IN BINARY_INTEGER,
2253 x_header_record IN rcv_roi_preprocessor.header_rec_type
2254 ) IS
2255 BEGIN
2256 IF x_cascaded_table(n).freight_carrier_code IS NULL THEN
2257 x_cascaded_table(n).freight_carrier_code := x_header_record.header_record.freight_carrier_code;
2258
2259 IF (g_asn_debug = 'Y') THEN
2260 asn_debug.put_line('Defaulting from HEADER FREIGHT_CARRIER_CODE ' || x_cascaded_table(n).freight_carrier_code);
2261 END IF;
2262 END IF;
2263 END default_freight_carrier_header;
2264
2265 PROCEDURE default_bill_of_lading_header(
2266 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2267 n IN BINARY_INTEGER,
2268 x_header_record IN rcv_roi_preprocessor.header_rec_type
2269 ) IS
2270 BEGIN
2271 IF x_cascaded_table(n).bill_of_lading IS NULL THEN
2272 x_cascaded_table(n).bill_of_lading := x_header_record.header_record.bill_of_lading;
2273
2274 IF (g_asn_debug = 'Y') THEN
2275 asn_debug.put_line('Defaulting from HEADER BILL_OF_LADING ' || x_cascaded_table(n).bill_of_lading);
2276 END IF;
2277 END IF;
2278 END default_bill_of_lading_header;
2279
2280 PROCEDURE default_packing_slip_header(
2281 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2282 n IN BINARY_INTEGER,
2283 x_header_record IN rcv_roi_preprocessor.header_rec_type
2284 ) IS
2285 BEGIN
2286 IF x_cascaded_table(n).packing_slip IS NULL THEN
2287 x_cascaded_table(n).packing_slip := x_header_record.header_record.packing_slip;
2288
2289 IF (g_asn_debug = 'Y') THEN
2290 asn_debug.put_line('Defaulting from HEADER PACKING_SLIP ' || x_cascaded_table(n).packing_slip);
2291 END IF;
2292 END IF;
2293 END default_packing_slip_header;
2294
2295 PROCEDURE default_ship_date_header(
2296 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2297 n IN BINARY_INTEGER,
2298 x_header_record IN rcv_roi_preprocessor.header_rec_type
2299 ) IS
2300 BEGIN
2301 IF x_cascaded_table(n).shipped_date IS NULL THEN
2302 x_cascaded_table(n).shipped_date := x_header_record.header_record.shipped_date;
2303
2304 IF (g_asn_debug = 'Y') THEN
2305 asn_debug.put_line('Defaulting from HEADER SHIPPED_DATE ' || TO_CHAR(x_cascaded_table(n).shipped_date, 'DD/MM/YYYY'));
2306 END IF;
2307 END IF;
2308 END default_ship_date_header;
2309
2310 PROCEDURE default_receipt_date_header(
2311 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2312 n IN BINARY_INTEGER,
2313 x_header_record IN rcv_roi_preprocessor.header_rec_type
2314 ) IS
2315 BEGIN
2316 IF x_cascaded_table(n).expected_receipt_date IS NULL THEN
2317 x_cascaded_table(n).expected_receipt_date := x_header_record.header_record.expected_receipt_date;
2318
2319 IF (g_asn_debug = 'Y') THEN
2320 asn_debug.put_line('Defaulting from HEADER EXPECTED_RECEIPT_DATE ' || TO_CHAR(x_cascaded_table(n).expected_receipt_date, 'DD/MM/YYYY'));
2321 END IF;
2322 END IF;
2323 END default_receipt_date_header;
2324
2325 PROCEDURE default_num_containers_header(
2326 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2327 n IN BINARY_INTEGER,
2328 x_header_record IN rcv_roi_preprocessor.header_rec_type
2329 ) IS
2330 BEGIN
2331 IF x_cascaded_table(n).num_of_containers IS NULL THEN
2332 x_cascaded_table(n).num_of_containers := x_header_record.header_record.num_of_containers;
2333
2334 IF (g_asn_debug = 'Y') THEN
2335 asn_debug.put_line('Defaulting from HEADER NUM_OF_CONTAINERS ' || TO_CHAR(x_cascaded_table(n).num_of_containers));
2336 END IF;
2337 END IF;
2338 END default_num_containers_header;
2339
2340 PROCEDURE default_waybill_header(
2341 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2342 n IN BINARY_INTEGER,
2343 x_header_record IN rcv_roi_preprocessor.header_rec_type
2344 ) IS
2345 BEGIN
2346 IF x_cascaded_table(n).waybill_airbill_num IS NULL THEN
2347 x_cascaded_table(n).waybill_airbill_num := x_header_record.header_record.waybill_airbill_num;
2348
2349 IF (g_asn_debug = 'Y') THEN
2350 asn_debug.put_line('Defaulting from HEADER WAYBILL_AIRBILL_NUM ' || x_cascaded_table(n).waybill_airbill_num);
2351 END IF;
2352 END IF;
2353 END default_waybill_header;
2354
2355 PROCEDURE default_tax_name_header(
2356 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2357 n IN BINARY_INTEGER,
2358 x_header_record IN rcv_roi_preprocessor.header_rec_type
2359 ) IS
2360 BEGIN
2361 IF x_cascaded_table(n).tax_name IS NULL THEN
2362 x_cascaded_table(n).tax_name := x_header_record.header_record.tax_name;
2363
2364 IF (g_asn_debug = 'Y') THEN
2365 asn_debug.put_line('Defaulting from HEADER TAX_NAME ' || x_cascaded_table(n).tax_name);
2366 END IF;
2367 END IF;
2368 END default_tax_name_header;
2369
2370 PROCEDURE validate_freight_carrier_info(
2371 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2372 n IN BINARY_INTEGER
2373 ) IS
2374 BEGIN
2375 NULL;
2376 END validate_freight_carrier_info;
2377
2378 PROCEDURE validate_qty_invoiced(
2379 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2380 n IN BINARY_INTEGER
2381 ) IS
2382 BEGIN
2383 NULL;
2384 END validate_qty_invoiced;
2385
2386 PROCEDURE validate_uom_info(
2387 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2388 n IN BINARY_INTEGER
2389 ) IS
2390 uom_record rcv_shipment_line_sv.quantity_shipped_record_type;
2391 BEGIN
2392 IF (x_cascaded_table(n).error_status NOT IN('S', 'W')) THEN
2393 RETURN;
2394 END IF;
2395
2396 IF (g_asn_debug = 'Y') THEN
2397 asn_debug.put_line('Before call to validate UOM');
2398 asn_debug.put_line('Quantity ' || TO_CHAR(x_cascaded_table(n).quantity));
2399 END IF;
2400
2401 /* Commenting the following line because OE stores UOM differently
2402 * from PO, causing conversion problems.
2403 * Conversion check is already done in derive quantities anyway.
2404 */
2405 -- uom_record.po_line_id := x_cascaded_table(n).oe_order_line_id;
2406
2407 uom_record.quantity_shipped := x_cascaded_table(n).quantity;
2408 uom_record.unit_of_measure := x_cascaded_table(n).unit_of_measure;
2409 uom_record.item_id := x_cascaded_table(n).item_id;
2410 uom_record.to_organization_id := x_cascaded_table(n).to_organization_id;
2411 uom_record.po_header_id := x_cascaded_table(n).oe_order_header_id;
2412 uom_record.primary_unit_of_measure := x_cascaded_table(n).primary_unit_of_measure;
2413 uom_record.error_record.error_status := 'S';
2414 uom_record.error_record.error_message := NULL;
2415
2416 IF (g_asn_debug = 'Y') THEN
2417 asn_debug.put_line('Validating UOM');
2418 END IF;
2419
2420 validate_uom(uom_record);
2421 x_cascaded_table(n).error_status := uom_record.error_record.error_status;
2422 x_cascaded_table(n).error_message := uom_record.error_record.error_message;
2423 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
2424 'UNIT_OF_MEASURE',
2425 FALSE
2426 );
2427 END validate_uom_info;
2428
2429 PROCEDURE validate_item_info(
2430 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2431 n IN BINARY_INTEGER
2432 ) IS
2433 item_revision_record rcv_shipment_line_sv.item_id_record_type;
2434 item_id_record rcv_shipment_line_sv.item_id_record_type;
2435 BEGIN
2436 -- item_id
2437 IF (x_cascaded_table(n).error_status NOT IN('S', 'W')) THEN
2438 RETURN;
2439 END IF;
2440
2441 item_id_record.item_id := x_cascaded_table(n).item_id;
2442 item_id_record.po_line_id := x_cascaded_table(n).oe_order_line_id;
2443 item_id_record.to_organization_id := x_cascaded_table(n).to_organization_id;
2444 item_id_record.item_description := x_cascaded_table(n).item_description;
2445 item_id_record.item_num := x_cascaded_table(n).item_num;
2446 item_id_record.vendor_item_num := NULL; -- x_cascaded_table(n).vendor_item_num;
2447 /* bug 608353 */
2448 item_id_record.use_mtl_lot := x_cascaded_table(n).use_mtl_lot;
2449 item_id_record.use_mtl_serial := x_cascaded_table(n).use_mtl_serial;
2450 item_id_record.error_record.error_status := 'S';
2451 item_id_record.error_record.error_message := NULL;
2452
2453 IF (g_asn_debug = 'Y') THEN
2454 asn_debug.put_line('Validating Item');
2455 asn_debug.put_line(TO_CHAR(x_cascaded_table(n).item_id));
2456 END IF;
2457
2458 /*
2459 ** If this is a one time item shipment and you've matched up based on a
2460 ** document line num then skip the processing based on setting the validation
2461 ** for the item to be the same as what is set on the line.
2462 */
2463 IF ( x_cascaded_table(n).item_id IS NULL
2464 AND x_cascaded_table(n).oe_order_line_id IS NOT NULL) THEN
2465 item_id_record.error_record.error_status := x_cascaded_table(n).error_status;
2466 item_id_record.error_record.error_message := x_cascaded_table(n).error_message;
2467 ELSE
2468 validate_item(item_id_record, x_cascaded_table(n).auto_transact_code); -- bug 608353
2469 END IF;
2470
2471 x_cascaded_table(n).error_status := item_id_record.error_record.error_status;
2472 x_cascaded_table(n).error_message := item_id_record.error_record.error_message;
2473 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'ITEM_NUM');
2474 -- item_description
2475 item_id_record.item_description := x_cascaded_table(n).item_description;
2476 item_id_record.error_record.error_status := 'S';
2477 item_id_record.error_record.error_message := NULL;
2478
2479 IF (g_asn_debug = 'Y') THEN
2480 asn_debug.put_line('Validating Item Description ' || item_id_record.item_description);
2481 END IF;
2482
2483 rcv_transactions_interface_sv1.validate_item_description(item_id_record);
2484 x_cascaded_table(n).error_status := item_id_record.error_record.error_status;
2485 x_cascaded_table(n).error_message := item_id_record.error_record.error_message;
2486 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'ITEM_DESCRIPTION');
2487
2488 IF (g_asn_debug = 'Y') THEN
2489 asn_debug.put_line('Error status after validate item description ' || x_cascaded_table(n).error_status);
2490 END IF;
2491
2492 -- item_revision
2493 IF (x_cascaded_table(n).item_revision IS NOT NULL) THEN
2494 item_revision_record.item_revision := x_cascaded_table(n).item_revision;
2495 item_revision_record.po_line_id := x_cascaded_table(n).oe_order_line_id;
2496 item_revision_record.to_organization_id := x_cascaded_table(n).to_organization_id;
2497 item_revision_record.item_id := x_cascaded_table(n).item_id;
2498 item_revision_record.error_record.error_status := 'S';
2499 item_revision_record.error_record.error_message := NULL;
2500
2501 IF (g_asn_debug = 'Y') THEN
2502 asn_debug.put_line('Validating Item Revision');
2503 END IF;
2504
2505 validate_item_revision(item_revision_record);
2506 x_cascaded_table(n).error_status := item_revision_record.error_record.error_status;
2507 x_cascaded_table(n).error_message := item_revision_record.error_record.error_message;
2508 x_cascaded_table(n).item_revision := item_revision_record.item_revision;
2509 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'ITEM_REVISION');
2510 END IF;
2511 EXCEPTION
2512 WHEN rcv_error_pkg.e_fatal_error THEN
2513 NULL;
2514 END validate_item_info;
2515
2516 PROCEDURE validate_txn_date(
2517 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2518 n IN BINARY_INTEGER
2519 ) IS
2520 x_sob_id NUMBER;
2521 x_val_open_ok BOOLEAN;
2522 BEGIN
2523 /*Bug 2327318 Implemented the validation Transaction date should not be greater than
2524 sysdate */
2525 IF (x_cascaded_table(n).error_status NOT IN('S', 'W')) THEN
2526 RETURN;
2527 END IF;
2528
2529 IF (x_cascaded_table(n).transaction_date > SYSDATE) THEN
2530 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
2531 rcv_error_pkg.set_error_message('RCV_TRX_FUTURE_DATE_NA', x_cascaded_table(n).error_message);
2532 rcv_error_pkg.log_interface_error('TRANSACTION_DATE');
2533 END IF;
2534
2535 -- bug 642624 validate if PO and GL periods are open in pre-processor
2536
2537 /* Bug 2653229 - To check if the transaction date falls in the open period only
2538 when the auto transact code is not SHIP. */
2539 IF (x_cascaded_table(n).auto_transact_code <> 'SHIP') THEN
2540 BEGIN
2541 SELECT set_of_books_id
2542 INTO x_sob_id
2543 FROM financials_system_parameters;
2544 EXCEPTION
2545 WHEN NO_DATA_FOUND THEN
2546 IF (g_asn_debug = 'Y') THEN
2547 asn_debug.put_line('Set of books id not defined');
2548 END IF;
2549 END;
2550
2551 BEGIN
2552 x_val_open_ok := po_dates_s.val_open_period(x_cascaded_table(n).transaction_date,
2553 x_sob_id,
2554 'SQLGL',
2555 x_cascaded_table(n).to_organization_id
2556 );
2557 EXCEPTION
2558 WHEN OTHERS THEN
2559 x_val_open_ok := FALSE;
2560 END;
2561
2562 IF NOT(x_val_open_ok) THEN
2563 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
2564 rcv_error_pkg.set_error_message('PO_CNL_NO_PERIOD', x_cascaded_table(n).error_message);
2565 rcv_error_pkg.log_interface_error('TRANSACTION_DATE');
2566 END IF;
2567
2568 BEGIN
2569 x_val_open_ok := po_dates_s.val_open_period(x_cascaded_table(n).transaction_date,
2570 x_sob_id,
2571 'INV',
2572 x_cascaded_table(n).to_organization_id
2573 );
2574 EXCEPTION
2575 WHEN OTHERS THEN
2576 x_val_open_ok := FALSE;
2577 END;
2578
2579 IF NOT(x_val_open_ok) THEN
2580 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
2581 rcv_error_pkg.set_error_message('PO_INV_NO_OPEN_PERIOD', x_cascaded_table(n).error_message);
2582 rcv_error_pkg.log_interface_error('TRANSACTION_DATE');
2583 END IF;
2584
2585 BEGIN
2586 x_val_open_ok := po_dates_s.val_open_period(x_cascaded_table(n).transaction_date,
2587 x_sob_id,
2588 'PO',
2589 x_cascaded_table(n).to_organization_id
2590 );
2591 EXCEPTION
2592 WHEN OTHERS THEN
2593 x_val_open_ok := FALSE;
2594 END;
2595
2596 IF NOT(x_val_open_ok) THEN
2597 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
2598 rcv_error_pkg.set_error_message('PO_PO_ENTER_OPEN_GL_DATE', x_cascaded_table(n).error_message);
2599 rcv_error_pkg.log_interface_error('TRANSACTION_DATE');
2600 END IF; /* End of Bug# 2379848 */
2601 END IF; -- auto transact code = SHIP
2602 EXCEPTION
2603 WHEN rcv_error_pkg.e_fatal_error THEN
2604 NULL;
2605 END validate_txn_date;
2606
2607 PROCEDURE validate_freight_carrier_code(
2608 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2609 n IN BINARY_INTEGER
2610 ) IS
2611 freight_carrier_record rcv_shipment_line_sv.freight_carrier_record_type;
2612 BEGIN
2613 IF (x_cascaded_table(n).error_status NOT IN('S', 'W')) THEN
2614 RETURN;
2615 END IF;
2616
2617 IF (x_cascaded_table(n).freight_carrier_code IS NOT NULL) THEN
2618 freight_carrier_record.to_organization_id := x_cascaded_table(n).to_organization_id;
2619 freight_carrier_record.freight_carrier_code := x_cascaded_table(n).freight_carrier_code;
2620 freight_carrier_record.po_header_id := x_cascaded_table(n).po_header_id;
2621 freight_carrier_record.error_record.error_status := 'S';
2622 freight_carrier_record.error_record.error_message := NULL;
2623
2624 IF (g_asn_debug = 'Y') THEN
2625 asn_debug.put_line('Validating Freight Carrier');
2626 END IF;
2627
2628 rcv_transactions_interface_sv1.validate_freight_carrier(freight_carrier_record);
2629 x_cascaded_table(n).error_status := freight_carrier_record.error_record.error_status;
2630 x_cascaded_table(n).error_message := freight_carrier_record.error_record.error_message;
2631 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
2632 'FREIGHT_CARRIER_CODE',
2633 FALSE
2634 );
2635 END IF;
2636 END validate_freight_carrier_code;
2637
2638 PROCEDURE validate_destination_type(
2639 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2640 n IN BINARY_INTEGER
2641 ) IS
2642 po_lookup_code_record rcv_shipment_line_sv.po_lookup_code_record_type;
2643 BEGIN
2644 /*
2645 ** Validate Destination Type. This value is always required
2646 */
2647 IF (x_cascaded_table(n).error_status NOT IN('S', 'W')) THEN
2648 RETURN;
2649 END IF;
2650
2651 po_lookup_code_record.lookup_code := x_cascaded_table(n).destination_type_code;
2652 po_lookup_code_record.lookup_type := 'RCV DESTINATION TYPE';
2653 po_lookup_code_record.error_record.error_status := 'S';
2654 po_lookup_code_record.error_record.error_message := NULL;
2655
2656 IF (g_asn_debug = 'Y') THEN
2657 asn_debug.put_line('Validating Destination Type Code');
2658 END IF;
2659
2660 rcv_transactions_interface_sv1.validate_po_lookup_code(po_lookup_code_record);
2661 x_cascaded_table(n).error_status := po_lookup_code_record.error_record.error_status;
2662 x_cascaded_table(n).error_message := po_lookup_code_record.error_record.error_message;
2663 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
2664 'DESTINATION_TYPE_CODE',
2665 FALSE
2666 );
2667 END validate_destination_type;
2668
2669 PROCEDURE validate_deliver_to_info(
2670 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2671 n IN BINARY_INTEGER
2672 ) IS
2673 employee_record rcv_shipment_line_sv.employee_record_type;
2674 BEGIN
2675 /*
2676 ** Validate deliver to person. This value is always optional
2677 */
2678 IF (x_cascaded_table(n).error_status NOT IN('S', 'W')) THEN
2679 RETURN;
2680 END IF;
2681
2682 employee_record.employee_id := x_cascaded_table(n).deliver_to_person_id;
2683 employee_record.to_organization_id := x_cascaded_table(n).to_organization_id;
2684 employee_record.destination_type_code := x_cascaded_table(n).destination_type_code;
2685 employee_record.transaction_date := x_cascaded_table(n).transaction_date;
2686 employee_record.error_record.error_status := 'S';
2687 employee_record.error_record.error_message := NULL;
2688
2689 IF (g_asn_debug = 'Y') THEN
2690 asn_debug.put_line('Validating Deliver to Person');
2691 END IF;
2692
2693 rcv_transactions_interface_sv1.validate_employee(employee_record);
2694 x_cascaded_table(n).error_status := employee_record.error_record.error_status;
2695 x_cascaded_table(n).error_message := employee_record.error_record.error_message;
2696 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
2697 'DELIVER_TO_PERSON_ID',
2698 FALSE
2699 );
2700 END validate_deliver_to_info;
2701
2702 PROCEDURE validate_tax_name(
2703 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2704 n IN BINARY_INTEGER
2705 ) IS
2706 BEGIN
2707 IF (x_cascaded_table(n).error_status NOT IN('S', 'W')) THEN
2708 RETURN;
2709 END IF;
2710
2711 IF (g_asn_debug = 'Y') THEN
2712 asn_debug.put_line('Validating tax_name: ' || x_cascaded_table(n).tax_name);
2713 END IF;
2714 END validate_tax_name;
2715
2716 PROCEDURE validate_country_of_origin(
2717 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2718 n IN BINARY_INTEGER
2719 ) IS
2720 country_of_origin_record rcv_shipment_line_sv.country_of_origin_record_type;
2721 BEGIN
2722 IF (x_cascaded_table(n).error_status NOT IN('S', 'W')) THEN
2723 RETURN;
2724 END IF;
2725
2726 IF (g_asn_debug = 'Y') THEN
2727 asn_debug.put_line('Validating country_of_origin_code: ' || x_cascaded_table(n).country_of_origin_code);
2728 END IF;
2729
2730 IF (x_cascaded_table(n).country_of_origin_code IS NOT NULL) THEN
2731 country_of_origin_record.country_of_origin_code := x_cascaded_table(n).country_of_origin_code;
2732 country_of_origin_record.error_record.error_status := 'S';
2733 country_of_origin_record.error_record.error_message := NULL;
2734
2735 IF (g_asn_debug = 'Y') THEN
2736 asn_debug.put_line('Validating Country of Origin Code');
2737 END IF;
2738
2739 rcv_transactions_interface_sv1.validate_country_of_origin(country_of_origin_record);
2740 x_cascaded_table(n).error_status := country_of_origin_record.error_record.error_status;
2741 x_cascaded_table(n).error_message := country_of_origin_record.error_record.error_message;
2742 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
2743 'COUNTRY_OF_ORIGIN_CODE',
2744 FALSE
2745 );
2746 END IF;
2747 END validate_country_of_origin;
2748
2749 /*===========================================================================
2750
2751 PROCEDURE NAME: validate_item()
2752
2753 Copied from rcv_transactions_interface_sv1 and modified for RMA use
2754
2755 ===========================================================================*/
2756 PROCEDURE validate_item(
2757 x_item_id_record IN OUT NOCOPY rcv_shipment_line_sv.item_id_record_type,
2758 x_auto_transact_code IN rcv_transactions_interface.auto_transact_code%TYPE
2759 ) IS -- bug 608353
2760 x_progress VARCHAR2(3);
2761 x_inventory_item mtl_system_items.inventory_item_id%TYPE;
2762 x_organization_id mtl_system_items.organization_id%TYPE;
2763 x_item_id_po oe_order_lines_all.inventory_item_id%TYPE;
2764 x_error_status VARCHAR2(1);
2765 BEGIN
2766 x_error_status := rcv_error_pkg.g_ret_sts_error;
2767 x_progress := '000';
2768
2769 SELECT NVL(MAX(inventory_item_id), -9999)
2770 INTO x_inventory_item
2771 FROM mtl_system_items
2772 WHERE inventory_item_id = x_item_id_record.item_id;
2773
2774 IF (x_inventory_item = -9999) THEN
2775 rcv_error_pkg.set_error_message('RCV_ITEM_ID');
2776 RAISE rcv_error_pkg.e_fatal_error;
2777 END IF;
2778
2779 SELECT NVL(MAX(inventory_item_id), -9999)
2780 INTO x_inventory_item
2781 FROM mtl_system_items
2782 WHERE SYSDATE BETWEEN NVL(start_date_active, SYSDATE - 1) AND NVL(end_date_active, SYSDATE + 1)
2783 AND inventory_item_id = x_item_id_record.item_id;
2784
2785 IF (x_inventory_item = -9999) THEN
2786 rcv_error_pkg.set_error_message('RCV_ITEM_NOT_ACTIVE');
2787 RAISE rcv_error_pkg.e_fatal_error;
2788 END IF;
2789
2790 /* Bug 2160314.
2791 * We used to have nvl(max(organization_id),0) here before. But if the
2792 * organization_id is itself 0, then this will give us a problem in
2793 * the next step when we check if x_organization_id = 0. So changed
2794 * the statement to nvl(max(organization_id),-9999) and also the
2795 * check below. Similarly changed the select statement and the
2796 * check for nvl(max(item_id),0).
2797 */
2798 SELECT NVL(MAX(organization_id), -9999)
2799 INTO x_organization_id
2800 FROM mtl_system_items
2801 WHERE inventory_item_id = x_item_id_record.item_id
2802 AND organization_id = NVL(x_item_id_record.to_organization_id, organization_id);
2803
2804 IF (x_organization_id = -9999) THEN
2805 rcv_error_pkg.set_error_message('RCV_ITEM_NOT_IN_ORG');
2806 RAISE rcv_error_pkg.e_fatal_error;
2807 END IF;
2808
2809 SELECT NVL(MAX(inventory_item_id), -9999)
2810 INTO x_item_id_po
2811 FROM oe_order_lines_all
2812 WHERE line_id = x_item_id_record.po_line_id
2813 AND inventory_item_id = x_item_id_record.item_id;
2814
2815 IF (x_item_id_po = -9999) THEN
2816 rcv_error_pkg.set_error_message('RCV_ITEM_NOT_ON_PO');
2817 RAISE rcv_error_pkg.e_fatal_error;
2818 END IF;
2819
2820 /* Bug 2898324 The non-purchasable items were allowed to be
2821 received thru ROI. The validation on purchasable flag
2822 is not based on the receving org. Added a filter condition
2823 based on organization id.
2824 */
2825
2826 /* Fix for bug 2989299.
2827 Commenting the following sql as we should not validate an item
2828 based on it's purchasing flags at the time of receipt creation.
2829 Only at the time of creating the Purchase Order this flag has
2830 to be checked upon. Please see bug 2706571 for more details.
2831 For the time being we are not checking on item's stockable flag
2832 thru ROI. If required we will incorporate later.
2833 */
2834 SELECT NVL(MAX(inventory_item_id), -9999)
2835 INTO x_item_id_po
2836 FROM oe_order_lines_all
2837 WHERE line_id = x_item_id_record.po_line_id
2838 AND inventory_item_id = x_item_id_record.item_id;
2839
2840 IF (x_item_id_po <> x_item_id_record.item_id) THEN
2841 rcv_error_pkg.set_error_message('RCV_NOT_PO_LINE_NUM');
2842 RAISE rcv_error_pkg.e_fatal_error;
2843 END IF;
2844
2845 /* bug 608353, do not support lot and serial control if DELIVER is used */
2846 IF (g_asn_debug = 'Y') THEN
2847 asn_debug.put_line('Validating Item: ' || x_auto_transact_code);
2848 asn_debug.put_line('Validating Item: ' || x_item_id_record.use_mtl_lot);
2849 asn_debug.put_line('Validating Item: ' || x_item_id_record.use_mtl_serial);
2850 END IF;
2851 /* We now support Lot-serial Transactions. Hence removed the code that
2852 * sets error message to RCV_LOT_SERIAL_NOT_SUPPORTED.
2853 */
2854 EXCEPTION
2855 WHEN rcv_error_pkg.e_fatal_error THEN
2856 x_item_id_record.error_record.error_status := x_error_status;
2857 x_item_id_record.error_record.error_message := rcv_error_pkg.get_last_message;
2858
2859 IF (x_item_id_record.error_record.error_message = 'RCV_ITEM_ID') THEN
2860 rcv_error_pkg.set_token('ITEM', x_item_id_record.item_id);
2861 ELSIF(x_item_id_record.error_record.error_message = 'RCV_ITEM_NOT_ACTIVE') THEN
2862 rcv_error_pkg.set_token('ITEM', x_item_id_record.item_id);
2863 ELSIF(x_item_id_record.error_record.error_message = 'RCV_ITEM_NOT_IN_ORG') THEN
2864 rcv_error_pkg.set_token('ITEM', x_item_id_record.item_id);
2865 rcv_error_pkg.set_token('ORGANIZATION', x_item_id_record.to_organization_id);
2866 ELSIF(x_item_id_record.error_record.error_message = 'RCV_ITEM_NOT_ON_PO') THEN
2867 rcv_error_pkg.set_token('ITEM', x_item_id_record.item_id);
2868 rcv_error_pkg.set_token('PO_NUMBER', x_item_id_record.po_line_id);
2869 ELSIF(x_item_id_record.error_record.error_message = 'RCV_NOT_PO_LINE_NUM') THEN
2870 rcv_error_pkg.set_token('PO_ITEM', x_item_id_po);
2871 rcv_error_pkg.set_token('SHIPMENT_ITEM', x_item_id_record.item_id);
2872 END IF;
2873 END validate_item;
2874
2875 /*===========================================================================
2876
2877 PROCEDURE NAME: validate_item_revision()
2878
2879 Copied from rcv_transactions_interface_sv1 and modified for RMA use
2880
2881 ===========================================================================*/
2882 PROCEDURE validate_item_revision(
2883 x_item_revision_record IN OUT NOCOPY rcv_shipment_line_sv.item_id_record_type
2884 ) IS
2885 x_inventory_item mtl_system_items.inventory_item_id%TYPE;
2886 x_progress VARCHAR2(3);
2887 x_revision_control_flag VARCHAR2(1);
2888 x_error_status VARCHAR2(1);
2889 BEGIN
2890 x_error_status := rcv_error_pkg.g_ret_sts_error;
2891
2892 -- check whether the item is under revision control
2893 -- If it is not then item should not have any revisions
2894
2895 SELECT DECODE(msi.revision_qty_control_code,
2896 1, 'N',
2897 2, 'Y',
2898 'N'
2899 )
2900 INTO x_revision_control_flag
2901 FROM mtl_system_items msi
2902 WHERE inventory_item_id = x_item_revision_record.item_id
2903 AND organization_id = x_item_revision_record.to_organization_id;
2904
2905 IF x_revision_control_flag = 'N' THEN
2906 /* Bug 1913887 : Check if the item is Non-revision controlled
2907 and the revision entered matches with the one in PO, then
2908 return without any error, else return with error
2909 */
2910 SELECT NVL(MAX(line_id), 0)
2911 INTO x_inventory_item
2912 FROM oe_order_lines_all
2913 WHERE line_id = x_item_revision_record.po_line_id
2914 AND NVL(item_revision, x_item_revision_record.item_revision) = x_item_revision_record.item_revision;
2915
2916 IF (x_inventory_item <> 0) THEN
2917 RETURN;
2918 END IF;
2919
2920 IF (g_asn_debug = 'Y') THEN
2921 asn_debug.put_line('Item is not under revision control');
2922 END IF;
2923
2924 rcv_error_pkg.set_error_message('RCV_ITEM_REV_NOT_ALLOWED');
2925 RAISE rcv_error_pkg.e_fatal_error;
2926 END IF;
2927
2928 -- Check whether the revision number exists
2929
2930 IF (g_asn_debug = 'Y') THEN
2931 asn_debug.put_line('Revision number : ' || x_item_revision_record.item_revision);
2932 END IF;
2933
2934 SELECT NVL(MAX(inventory_item_id), 0)
2935 INTO x_inventory_item
2936 FROM mtl_item_revisions
2937 WHERE inventory_item_id = x_item_revision_record.item_id
2938 AND organization_id = NVL(x_item_revision_record.to_organization_id, organization_id)
2939 AND revision = x_item_revision_record.item_revision;
2940
2941 IF (x_inventory_item = 0) THEN
2942 rcv_error_pkg.set_error_message('PO_RI_INVALID_ITEM_REVISION');
2943 RAISE rcv_error_pkg.e_fatal_error;
2944 END IF;
2945
2946 -- Check whether revision is still active
2947
2948 SELECT NVL(MAX(inventory_item_id), 0) -- does this accurately check for active revisions??
2949 INTO x_inventory_item
2950 FROM MTL_ITEM_REVISIONS_B mir --Bug 5217526. Earlier using mtl_item_revisions_org_val_v
2951 WHERE mir.inventory_item_id = x_item_revision_record.item_id
2952 AND mir.organization_id = NVL(x_item_revision_record.to_organization_id, mir.organization_id)
2953 AND mir.revision = x_item_revision_record.item_revision;
2954
2955 IF (x_inventory_item = 0) THEN
2956 rcv_error_pkg.set_error_message('PO_RI_INVALID_ITEM_REVISION');
2957 RAISE rcv_error_pkg.e_fatal_error;
2958 END IF;
2959
2960 -- Check whether rma revision matches this revision if rma revision is not null
2961
2962 SELECT NVL(MAX(line_id), 0)
2963 INTO x_inventory_item
2964 FROM oe_order_lines_all
2965 WHERE line_id = x_item_revision_record.po_line_id
2966 AND NVL(item_revision, x_item_revision_record.item_revision) = x_item_revision_record.item_revision;
2967
2968 IF (x_inventory_item = 0) THEN
2969 x_error_status := rcv_error_pkg.g_ret_sts_warning;
2970 rcv_error_pkg.set_error_message('RCV_NOT_PO_REVISION');
2971 RAISE rcv_error_pkg.e_fatal_error;
2972 END IF;
2973 EXCEPTION
2974 WHEN rcv_error_pkg.e_fatal_error THEN
2975 x_item_revision_record.error_record.error_status := x_error_status;
2976 x_item_revision_record.error_record.error_message := rcv_error_pkg.get_last_message;
2977
2978 IF (x_item_revision_record.error_record.error_message = 'RCV_ITEM_REV_NOT_ALLOWED') THEN
2979 rcv_error_pkg.set_token('ITEM', x_item_revision_record.item_id);
2980 ELSIF(x_item_revision_record.error_record.error_message = 'RCV_NOT_PO_REVISION') THEN
2981 rcv_error_pkg.set_token('PO_REV', x_inventory_item);
2982 rcv_error_pkg.set_token('SHIPMENT_REV', x_item_revision_record.item_revision);
2983 END IF;
2984 END validate_item_revision;
2985
2986 PROCEDURE validate_ref_integrity(
2987 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2988 n IN BINARY_INTEGER
2989 ) IS
2990 x_customer_item_num rcv_transactions_interface.customer_item_num%TYPE;
2991 x_customer_id rcv_transactions_interface.customer_id%TYPE;
2992 x_order_line_id oe_order_lines_all.line_id%TYPE;
2993 BEGIN
2994 IF (x_cascaded_table(n).error_status NOT IN('S', 'W')) THEN
2995 RETURN;
2996 END IF;
2997
2998 IF (g_asn_debug = 'Y') THEN
2999 asn_debug.put_line('Validating ref integrity');
3000 END IF;
3001
3002 -- check customer item number
3003 IF (x_cascaded_table(n).customer_item_num IS NOT NULL) THEN
3004 SELECT NVL(MAX(oel.line_id), 0)
3005 INTO x_order_line_id
3006 FROM oe_order_lines_all oel,
3007 mtl_customer_items mci
3008 WHERE oel.line_id = x_cascaded_table(n).oe_order_line_id
3009 AND oel.ordered_item_id = mci.customer_item_id
3010 AND mci.customer_item_number = x_cascaded_table(n).customer_item_num;
3011
3012 IF (x_order_line_id = 0) THEN
3013 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
3014 rcv_error_pkg.set_error_message('RCV_NOT_CUST_ITEM', x_cascaded_table(n).error_message);
3015 rcv_error_pkg.set_token('TXN_CUSTOMER_ITEM', x_cascaded_table(n).customer_item_num);
3016 rcv_error_pkg.set_token('RMA_CUSTOMER_ITEM', x_order_line_id);
3017 rcv_error_pkg.log_interface_error('CUSTOMER_ITEM_NUM');
3018 END IF;
3019 END IF;
3020
3021 -- check customer id
3022 IF (x_cascaded_table(n).customer_id IS NOT NULL) THEN
3023 SELECT (NVL(oeh.sold_to_org_id, 0))
3024 INTO x_customer_id
3025 FROM oe_order_headers_all oeh
3026 WHERE oeh.header_id = x_cascaded_table(n).oe_order_header_id;
3027
3028 IF (x_customer_id = 0) THEN
3029 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
3030 rcv_error_pkg.set_error_message('RCV_ERC_MISMATCH_RMA_CUST', x_cascaded_table(n).error_message);
3031 rcv_error_pkg.log_interface_error('CUSTOMER_ID');
3032 END IF;
3033 END IF;
3034 EXCEPTION
3035 WHEN rcv_error_pkg.e_fatal_error THEN
3036 NULL;
3037 END validate_ref_integrity;
3038
3039 -- Copied from rcv_transactions_interface_sv1.validate_uom and modified for RMAs
3040 PROCEDURE validate_uom(
3041 x_uom_record IN OUT NOCOPY rcv_shipment_line_sv.quantity_shipped_record_type
3042 ) IS
3043 x_unit_of_measure rcv_transactions_interface.unit_of_measure%TYPE := NULL;
3044 x_unit_meas_lookup_code_lines po_lines_all.unit_meas_lookup_code%TYPE := NULL;
3045 x_progress VARCHAR2(3);
3046 x_new_conversion NUMBER := 0;
3047 x_primary_unit_of_measure mtl_system_items.primary_unit_of_measure%TYPE := NULL;
3048 x_error_status VARCHAR2(1);
3049 BEGIN
3050 x_error_status := rcv_error_pkg.g_ret_sts_error;
3051 x_progress := '000';
3052
3053 -- check that the uom is valid
3054 SELECT NVL(MAX(unit_of_measure), 'notfound')
3055 INTO x_unit_of_measure
3056 FROM mtl_units_of_measure
3057 WHERE unit_of_measure = x_uom_record.unit_of_measure;
3058
3059 IF (x_unit_of_measure = 'notfound') THEN
3060 rcv_error_pkg.set_error_message('PO_PDOI_INVALID_UOM_CODE');
3061 RAISE rcv_error_pkg.e_fatal_error;
3062 END IF;
3063
3064 -- check that system date is less than the disabled_date
3065 IF NOT po_uom_s.val_unit_of_measure(x_uom_record.unit_of_measure) THEN
3066 rcv_error_pkg.set_error_message('PO_PDOI_INVALID_UOM_CODE');
3067 RAISE rcv_error_pkg.e_fatal_error;
3068 END IF;
3069
3070 -- one-time purchase item
3071 IF (x_uom_record.item_id IS NOT NULL) THEN
3072 -- must have a primary uom at this point since the first select stmt succeeded
3073
3074 SELECT primary_unit_of_measure
3075 INTO x_primary_unit_of_measure
3076 FROM mtl_system_items_kfv
3077 WHERE inventory_item_id = x_uom_record.item_id
3078 AND organization_id = NVL(x_uom_record.to_organization_id, organization_id); -- Raj added as org_id is part of uk
3079
3080 IF (NVL(x_uom_record.primary_unit_of_measure, x_primary_unit_of_measure) <> x_primary_unit_of_measure) THEN
3081 x_error_status := rcv_error_pkg.g_ret_sts_warning;
3082 rcv_error_pkg.set_error_message('RCV_UOM_NOT_PRIMARY');
3083 RAISE rcv_error_pkg.e_fatal_error;
3084 END IF;
3085
3086 x_new_conversion := 0;
3087
3088 IF (g_asn_debug = 'Y') THEN
3089 asn_debug.put_line(TO_CHAR(x_uom_record.quantity_shipped));
3090 asn_debug.put_line(x_uom_record.unit_of_measure);
3091 asn_debug.put_line(TO_CHAR(x_uom_record.item_id));
3092 asn_debug.put_line(x_primary_unit_of_measure);
3093 asn_debug.put_line(x_uom_record.primary_unit_of_measure);
3094 END IF;
3095
3096 po_uom_s.uom_convert(x_uom_record.quantity_shipped,
3097 x_uom_record.unit_of_measure,
3098 x_uom_record.item_id,
3099 x_primary_unit_of_measure,
3100 x_new_conversion
3101 );
3102
3103 IF (x_new_conversion = 0) THEN
3104 rcv_error_pkg.set_error_message('RCV_UOM_NO_CONV_PRIMARY');
3105 RAISE rcv_error_pkg.e_fatal_error;
3106 ELSIF(x_new_conversion <> x_uom_record.primary_quantity) THEN
3107 rcv_error_pkg.set_error_message('RCV_QTY_NOT_PRIMARY');
3108 RAISE rcv_error_pkg.e_fatal_error;
3109 END IF;
3110 END IF;
3111
3112 SELECT NVL(MAX(order_quantity_uom), 'notfound')
3113 INTO x_unit_meas_lookup_code_lines
3114 FROM oe_order_lines_all
3115 WHERE line_id = x_uom_record.po_line_id;
3116
3117 IF (x_unit_meas_lookup_code_lines <> 'notfound')
3118 AND (x_unit_meas_lookup_code_lines <> x_uom_record.unit_of_measure) THEN
3119 x_new_conversion := 0;
3120 po_uom_s.uom_convert(x_uom_record.quantity_shipped,
3121 x_uom_record.unit_of_measure,
3122 x_uom_record.item_id,
3123 x_unit_meas_lookup_code_lines,
3124 x_new_conversion
3125 );
3126
3127 IF (x_new_conversion = 0) THEN
3128 rcv_error_pkg.set_error_message('RCV_UOM_NO_CONV_PO');
3129 RAISE rcv_error_pkg.e_fatal_error;
3130 END IF;
3131 END IF;
3132 EXCEPTION
3133 WHEN rcv_error_pkg.e_fatal_error THEN
3134 x_uom_record.error_record.error_status := x_error_status;
3135 x_uom_record.error_record.error_message := rcv_error_pkg.get_last_message;
3136
3137 IF (x_uom_record.error_record.error_message = 'PO_PDOI_INVALID_UOM_CODE') THEN
3138 rcv_error_pkg.set_token('VALUE', x_uom_record.unit_of_measure);
3139 ELSIF(x_uom_record.error_record.error_message = 'RCV_UOM_NO_CONV_PRIMARY') THEN
3140 rcv_error_pkg.set_token('SHIPMENT_UNIT', x_new_conversion);
3141 rcv_error_pkg.set_token('PRIMARY_UNIT', x_uom_record.primary_quantity);
3142 ELSIF(x_uom_record.error_record.error_message = 'RCV_UOM_NO_CONV_PRIMARY') THEN
3143 rcv_error_pkg.set_token('SHIPMENT_UNIT', x_new_conversion);
3144 rcv_error_pkg.set_token('PO_UNIT', x_uom_record.primary_quantity);
3145 END IF;
3146 END validate_uom;
3147
3148 PROCEDURE default_item_revision(
3149 x_item_revision_record IN OUT NOCOPY rcv_shipment_line_sv.item_id_record_type
3150 ) IS
3151 x_revision_control_flag VARCHAR2(1);
3152 x_number_of_inv_dest NUMBER;
3153 x_item_rev_exists BOOLEAN;
3154 BEGIN
3155 /* Check whether item is under revision control */
3156 SELECT DECODE(msi.revision_qty_control_code,
3157 1, 'N',
3158 2, 'Y',
3159 'N'
3160 )
3161 INTO x_revision_control_flag
3162 FROM mtl_system_items msi
3163 WHERE inventory_item_id = x_item_revision_record.item_id
3164 AND organization_id = x_item_revision_record.to_organization_id;
3165
3166 /* If item is under revision control
3167
3168 if revision is null then try to pick up item_revision from oe_order_lines
3169
3170 if revision is still null and
3171 there are any destination_type=INVENTORY then
3172
3173 try to pick up latest revision from mtl_item_revisions
3174
3175 end if
3176 else
3177 item should not have any revisions which we will validate in the validation phase */
3178 IF x_revision_control_flag = 'Y' THEN
3179 IF (g_asn_debug = 'Y') THEN
3180 asn_debug.put_line('Item is under revision control');
3181 END IF;
3182
3183 IF x_item_revision_record.item_revision IS NULL THEN -- pick up revision from source document
3184 IF (g_asn_debug = 'Y') THEN
3185 asn_debug.put_line('Picking up from source document');
3186 END IF;
3187
3188 SELECT item_revision
3189 INTO x_item_revision_record.item_revision
3190 FROM oe_order_lines_all
3191 WHERE oe_order_lines_all.line_id = x_item_revision_record.po_line_id;
3192 END IF;
3193
3194 IF x_item_revision_record.item_revision IS NULL THEN
3195 IF (g_asn_debug = 'Y') THEN
3196 asn_debug.put_line('Picking up latest implementation since source doc is null');
3197 END IF;
3198
3199 po_items_sv2.get_latest_item_rev(x_item_revision_record.item_id,
3200 x_item_revision_record.to_organization_id,
3201 x_item_revision_record.item_revision,
3202 x_item_rev_exists
3203 );
3204 END IF;
3205 END IF;
3206 EXCEPTION
3207 WHEN OTHERS THEN
3208 IF (g_asn_debug = 'Y') THEN
3209 asn_debug.put_line('Exception in procedure default_item_revision');
3210 END IF;
3211 END default_item_revision;
3212
3213 /*===========================================================================
3214
3215 PROCEDURE NAME: check_date_tolerance()
3216
3217 ===========================================================================*/
3218 PROCEDURE check_date_tolerance(
3219 expected_receipt_date IN DATE,
3220 promised_date IN DATE,
3221 days_early_receipt_allowed IN NUMBER,
3222 days_late_receipt_allowed IN NUMBER,
3223 receipt_days_exception_code IN OUT NOCOPY VARCHAR2
3224 ) IS
3225 x_sysdate DATE := SYSDATE;
3226 high_range_date DATE;
3227 low_range_date DATE;
3228 BEGIN
3229 IF (g_asn_debug = 'Y') THEN
3230 asn_debug.put_line('Check date tolerance');
3231 END IF;
3232
3233 IF (expected_receipt_date IS NOT NULL) THEN
3234 IF (promised_date IS NOT NULL) THEN
3235 low_range_date := promised_date - NVL(days_early_receipt_allowed, 0);
3236 high_range_date := promised_date + NVL(days_late_receipt_allowed, 0);
3237 ELSE
3238 low_range_date := x_sysdate - NVL(days_early_receipt_allowed, 0);
3239 high_range_date := x_sysdate + NVL(days_late_receipt_allowed, 0);
3240 END IF;
3241
3242 IF ( expected_receipt_date >= low_range_date
3243 AND expected_receipt_date <= high_range_date) THEN
3244 receipt_days_exception_code := 'NONE';
3245 ELSE
3246 IF receipt_days_exception_code = 'REJECT' THEN
3247 receipt_days_exception_code := 'REJECT';
3248 ELSIF receipt_days_exception_code = 'WARNING' THEN
3249 receipt_days_exception_code := 'NONE';
3250 END IF;
3251 END IF;
3252 ELSE
3253 receipt_days_exception_code := 'NONE';
3254 END IF;
3255
3256 IF receipt_days_exception_code IS NULL THEN
3257 IF (g_asn_debug = 'Y') THEN
3258 asn_debug.put_line('In null days exception code');
3259 END IF;
3260
3261 receipt_days_exception_code := 'NONE';
3262 END IF;
3263 END check_date_tolerance;
3264
3265 FUNCTION convert_into_correct_qty(
3266 source_qty IN NUMBER,
3267 source_uom IN VARCHAR2,
3268 item_id IN NUMBER,
3269 dest_uom IN VARCHAR2
3270 )
3271 RETURN NUMBER IS
3272 correct_qty NUMBER;
3273 BEGIN
3274 IF source_uom <> dest_uom THEN
3275 po_uom_s.uom_convert(source_qty,
3276 source_uom,
3277 item_id,
3278 dest_uom,
3279 correct_qty
3280 );
3281 ELSE
3282 correct_qty := source_qty;
3283 END IF;
3284
3285 RETURN(correct_qty);
3286 EXCEPTION
3287 WHEN OTHERS THEN
3288 IF (g_asn_debug = 'Y') THEN
3289 asn_debug.put_line('Could not convert between UOMs');
3290 asn_debug.put_line('Will return 0');
3291 END IF;
3292
3293 correct_qty := 0;
3294 RETURN(correct_qty);
3295 END convert_into_correct_qty;
3296
3297 /*===========================================================================
3298
3299 PROCEDURE NAME: get_location_id()
3300
3301 ===========================================================================*/
3302 PROCEDURE get_location_id(
3303 x_location_id_record IN OUT NOCOPY rcv_shipment_object_sv.location_id_record_type
3304 ) IS
3305 BEGIN
3306 SELECT MAX(location_id)
3307 INTO x_location_id_record.location_id
3308 FROM hr_locations
3309 WHERE location_code = x_location_id_record.location_code;
3310
3311 IF (x_location_id_record.location_id IS NULL) THEN
3312 x_location_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
3313 rcv_error_pkg.set_error_message('RCV_ASN_LOCATION_ID', x_location_id_record.error_record.error_message);
3314 END IF;
3315 EXCEPTION
3316 WHEN OTHERS THEN
3317 x_location_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
3318 rcv_error_pkg.set_sql_error_message('get_location_id', '000');
3319 x_location_id_record.error_record.error_message := rcv_error_pkg.get_last_message;
3320 END get_location_id;
3321
3322 /*===========================================================================
3323
3324 PROCEDURE NAME: get_locator_id()
3325
3326 ===========================================================================*/
3327 PROCEDURE get_locator_id(
3328 x_locator_id_record IN OUT NOCOPY rcv_shipment_line_sv.locator_id_record_type
3329 ) IS
3330 BEGIN
3331 IF (g_asn_debug = 'Y') THEN
3332 asn_debug.put_line('inside get_locator_id');
3333 END IF;
3334
3335 IF (x_locator_id_record.subinventory IS NULL) THEN
3336 SELECT MAX(ml.inventory_location_id)
3337 INTO x_locator_id_record.locator_id
3338 FROM mtl_item_locations_kfv ml
3339 WHERE ml.concatenated_segments = x_locator_id_record.LOCATOR
3340 AND ( ml.disable_date > SYSDATE
3341 OR ml.disable_date IS NULL)
3342 AND ml.subinventory_code IS NULL;
3343 ELSE
3344 SELECT MAX(ml.inventory_location_id)
3345 INTO x_locator_id_record.locator_id
3346 FROM mtl_item_locations_kfv ml
3347 WHERE ml.concatenated_segments = x_locator_id_record.LOCATOR
3348 AND ( ml.disable_date > SYSDATE
3349 OR ml.disable_date IS NULL)
3350 AND ml.subinventory_code = x_locator_id_record.subinventory;
3351 END IF;
3352
3353 IF (x_locator_id_record.locator_id IS NULL) THEN
3354 x_locator_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
3355 rcv_error_pkg.set_error_message('RCV_ALL_INVALID_LOCATOR', x_locator_id_record.error_record.error_message);
3356 /* Bug 3591830 Changed the error message name from RCV_ASN_LOCATOR_ID
3357 ** to RCV_ALL_INVALID_LOCATOR since there was no error message by name
3358 ** RCV_ASN_LOCATOR_ID in the application.
3359 */
3360 END IF;
3361 EXCEPTION
3362 WHEN OTHERS THEN
3363 x_locator_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
3364 rcv_error_pkg.set_sql_error_message('get_locator_id', '000');
3365 x_locator_id_record.error_record.error_message := rcv_error_pkg.get_last_message;
3366 END get_locator_id;
3367
3368 /*===========================================================================
3369
3370 PROCEDURE NAME: get_routing_header_id()
3371
3372 ===========================================================================*/
3373 PROCEDURE get_routing_header_id(
3374 x_routing_header_id_record IN OUT NOCOPY rcv_shipment_line_sv.routing_header_id_rec_type
3375 ) IS
3376 BEGIN
3377 SELECT MAX(routing_header_id)
3378 INTO x_routing_header_id_record.routing_header_id
3379 FROM rcv_routing_headers
3380 WHERE routing_name = x_routing_header_id_record.routing_code;
3381
3382 IF (x_routing_header_id_record.routing_header_id IS NULL) THEN
3383 x_routing_header_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
3384 rcv_error_pkg.set_error_message('RCV_ASN_ROUTING_HEADER_ID', x_routing_header_id_record.error_record.error_message);
3385 END IF;
3386 EXCEPTION
3387 WHEN OTHERS THEN
3388 x_routing_header_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
3389 rcv_error_pkg.set_sql_error_message('get_routing_header_id', '000');
3390 x_routing_header_id_record.error_record.error_message := rcv_error_pkg.get_last_message;
3391 END get_routing_header_id;
3392
3393 /*===========================================================================
3394
3395 PROCEDURE NAME: get_routing_step_id()
3396
3397 ===========================================================================*/
3398 PROCEDURE get_routing_step_id(
3399 x_routing_step_id_record IN OUT NOCOPY rcv_shipment_line_sv.routing_step_id_rec_type
3400 ) IS
3401 BEGIN
3402 SELECT MAX(routing_step_id)
3403 INTO x_routing_step_id_record.routing_step_id
3404 FROM rcv_routing_steps
3405 WHERE step_name = x_routing_step_id_record.routing_step;
3406
3407 IF (x_routing_step_id_record.routing_step_id IS NULL) THEN
3408 x_routing_step_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
3409 rcv_error_pkg.set_error_message('RCV_ASN_ROUTING_STEP_ID', x_routing_step_id_record.error_record.error_message);
3410 END IF;
3411 EXCEPTION
3412 WHEN OTHERS THEN
3413 x_routing_step_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
3414 rcv_error_pkg.set_sql_error_message('get_routing_step_id', '000');
3415 x_routing_step_id_record.error_record.error_message := rcv_error_pkg.get_last_message;
3416 END get_routing_step_id;
3417
3418 /*===========================================================================
3419
3420 PROCEDURE NAME: get_reason_id()
3421
3422 ===========================================================================*/
3423 PROCEDURE get_reason_id(
3424 x_reason_id_record IN OUT NOCOPY rcv_shipment_line_sv.reason_id_record_type
3425 ) IS
3426 BEGIN
3427 SELECT MAX(reason_id)
3428 INTO x_reason_id_record.reason_id
3429 FROM mtl_transaction_reasons
3430 WHERE reason_name = x_reason_id_record.reason_name;
3431
3432 IF (x_reason_id_record.reason_id IS NULL) THEN
3433 x_reason_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
3434 rcv_error_pkg.set_error_message('RCV_ASN_REASON_ID', x_reason_id_record.error_record.error_message);
3435 END IF;
3436 EXCEPTION
3437 WHEN OTHERS THEN
3438 x_reason_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
3439 rcv_error_pkg.set_sql_error_message('get_reason_id', '000');
3440 x_reason_id_record.error_record.error_message := rcv_error_pkg.get_last_message;
3441 END get_reason_id;
3442
3443 /*===========================================================================
3444
3445 PROCEDURE NAME: get_item_id()
3446
3447 ===========================================================================*/
3448 PROCEDURE get_item_id(
3449 x_item_id_record IN OUT NOCOPY rcv_shipment_line_sv.item_id_record_type
3450 ) IS
3451 BEGIN
3452 IF (x_item_id_record.item_num IS NOT NULL) THEN
3453 SELECT MIN(inventory_item_id),
3454 MIN(primary_unit_of_measure),
3455 MIN(lot_control_code), -- bug 608353
3456 MIN(serial_number_control_code)
3457 INTO x_item_id_record.item_id,
3458 x_item_id_record.primary_unit_of_measure,
3459 x_item_id_record.use_mtl_lot, -- bug 608353
3460 x_item_id_record.use_mtl_serial
3461 FROM mtl_item_flexfields
3462 WHERE item_number = x_item_id_record.item_num
3463 AND organization_id = x_item_id_record.to_organization_id;
3464
3465 IF (x_item_id_record.item_id IS NULL) THEN
3466 SELECT MIN(inventory_item_id),
3467 MIN(primary_unit_of_measure),
3468 MIN(lot_control_code), -- bug 608353
3469 MIN(serial_number_control_code)
3470 INTO x_item_id_record.item_id,
3471 x_item_id_record.primary_unit_of_measure,
3472 x_item_id_record.use_mtl_lot,
3473 x_item_id_record.use_mtl_serial
3474 FROM mtl_item_flexfields
3475 WHERE item_number = x_item_id_record.vendor_item_num
3476 AND organization_id = x_item_id_record.to_organization_id;
3477 END IF;
3478 END IF;
3479
3480 IF (x_item_id_record.item_id IS NULL) THEN
3481 x_item_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_warning;
3482 rcv_error_pkg.set_error_message('RCV_ITEM_ID', x_item_id_record.error_record.error_message);
3483 rcv_error_pkg.set_token('ITEM', x_item_id_record.item_num);
3484 END IF;
3485 EXCEPTION
3486 WHEN OTHERS THEN
3487 x_item_id_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
3488 rcv_error_pkg.set_sql_error_message('get_item_id', '000');
3489 x_item_id_record.error_record.error_message := rcv_error_pkg.get_last_message;
3490 END get_item_id;
3491
3492 /*===========================================================================
3493
3494 PROCEDURE NAME: get_org_id()
3495
3496 This call is done by EDI to obtain the org_id give the location id
3497
3498 ===========================================================================*/
3499 PROCEDURE get_org_id_from_hr_loc_id(
3500 p_hr_location_id IN NUMBER,
3501 x_organization_id OUT NOCOPY NUMBER
3502 ) IS
3503 BEGIN
3504 SELECT inventory_organization_id
3505 INTO x_organization_id
3506 FROM hr_locations
3507 WHERE location_id = p_hr_location_id;
3508 EXCEPTION
3509 WHEN OTHERS THEN
3510 x_organization_id := NULL;
3511 END get_org_id_from_hr_loc_id;
3512 END rcv_rma_transactions;
[Home] [Help]