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