1 PACKAGE BODY rcv_roi_return
2 /* $Header: RCVPRERB.pls 120.4 2006/05/17 02:38:33 samanna noship $*/
3 AS
4 g_asn_debug VARCHAR2(1) := NVL(fnd_profile.VALUE('RCV_DEBUG_MODE'), 'N');
5 x_progress VARCHAR2(3);
6 p_trx_record rcv_roi_header_common.common_default_record_type;
7 default_return_info default_return%ROWTYPE;
8
9 PROCEDURE derive_return_line(
10 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
11 n IN OUT NOCOPY BINARY_INTEGER,
12 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
13 x_header_record IN rcv_roi_preprocessor.header_rec_type
14 ) IS
15 BEGIN
16 x_progress := '010';
17 asn_debug.put_line('enter derive_return_line ');
18 /* Derive the to_org_id */
19 derive_ship_to_org_info(x_cascaded_table,
20 n,
21 x_header_record
22 );
23
24 IF (x_cascaded_table(n).unit_of_measure IS NOT NULL) THEN
25 IF (g_asn_debug = 'Y') THEN
26 asn_debug.put_line('X_progress ' || x_progress);
27 END IF;
28
29 SELECT muom.uom_code
30 INTO x_cascaded_table(n).uom_code
31 FROM mtl_units_of_measure muom
32 WHERE muom.unit_of_measure = x_cascaded_table(n).unit_of_measure;
33 ELSE
34 IF (g_asn_debug = 'Y') THEN
35 asn_debug.put_line('uom_code not dereived as unit_of_measure is null');
36 END IF;
37 END IF;
38
39 x_progress := '020';
40 derive_reason_info(x_cascaded_table, n);
41 /* Auto_transact_code is null for all these transaction types */
42 x_cascaded_table(n).auto_transact_code := NULL;
43 rcv_roi_transaction.derive_from_locator_id(x_cascaded_table, n); -- WMS Change
44 rcv_roi_transaction.derive_to_locator_id(x_cascaded_table, n); -- WMS Change
45 derive_return_line_qty(x_cascaded_table,
46 n,
47 temp_cascaded_table
48 );
49 END derive_return_line;
50
51 PROCEDURE default_return_line(
52 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
53 n IN BINARY_INTEGER
54 ) IS
55 BEGIN
56 asn_debug.put_line('Enter Default');
57 default_common_lines(x_cascaded_table, n);
58
59 IF (x_cascaded_table(n).derive = 'Y') THEN --{
60 IF (x_cascaded_table(n).derive_index <> 0) THEN --{
61 NULL;
62 /* Get the values from pl/sql table */
63 ELSE --} {
64 asn_debug.put_line('open cursor Default');
65 OPEN default_return_rti(x_cascaded_table(n).parent_interface_txn_id);
66 END IF; --}
67 ELSE -- } {
68 OPEN default_return(x_cascaded_table(n).parent_transaction_id);
69 END IF; --}
70
71 IF (default_return%ISOPEN) THEN
72 asn_debug.put_line('fetch cursor Default ' || x_cascaded_table(n).parent_transaction_id);
73 FETCH default_return INTO default_return_info;
74 ELSIF(default_return_rti%ISOPEN) THEN
75 FETCH default_return_rti INTO default_return_info;
76 END IF;
77
78 IF ( ( default_return%ISOPEN
79 AND default_return%FOUND)
80 OR ( default_return_rti%ISOPEN
81 AND default_return_rti%FOUND)) THEN --{
82 IF (x_cascaded_table(n).transaction_type = 'RETURN TO VENDOR')
83 OR ( x_cascaded_table(n).transaction_type = 'RETURN TO RECEIVING'
84 AND x_cascaded_table(n).source_document_code = 'PO') THEN
85
86 default_po_info(x_cascaded_table,
87 n,
88 default_return_info
89 );
90 default_vendor_info(x_cascaded_table,
91 n,
92 default_return_info
93 );
94 ELSIF (x_cascaded_table(n).transaction_type = 'RETURN TO CUSTOMER')
95 OR ( x_cascaded_table(n).transaction_type = 'RETURN TO RECEIVING'
96 AND x_cascaded_table(n).source_document_code = 'RMA') THEN
97 default_oe_info(x_cascaded_table,
98 n,
99 default_return_info
100 );
101 default_customer_info(x_cascaded_table,
102 n,
103 default_return_info
104 );
105 END IF;
106
107 default_shipment_info(x_cascaded_table,
108 n,
109 default_return_info
110 );
111 default_wip_info(x_cascaded_table,
112 n,
113 default_return_info
114 );
115 default_currency_info(x_cascaded_table,
116 n,
117 default_return_info
118 );
119 default_deliver_to_info(x_cascaded_table,
120 n,
121 default_return_info
122 );
123 default_source_info(x_cascaded_table,
124 n,
125 default_return_info
126 );
127 default_item_info(x_cascaded_table,
128 n,
129 default_return_info
130 );
131 default_destination_info(x_cascaded_table,
132 n,
133 default_return_info
134 );
135 default_location_info(x_cascaded_table,
136 n,
137 default_return_info
138 );
139 default_movement_id(x_cascaded_table,
140 n,
141 default_return_info
142 );
143 default_bom_resource_id(x_cascaded_table,
144 n,
145 default_return_info
146 );
147 -- default the parent's to_lpn into the child's from_lpn
148 END IF; -- if default_return%FOUND is true }
149
150 /* FPJ WMS CHANGES START */
151 IF (x_cascaded_table(n).from_subinventory IS NULL) THEN
152 rcv_roi_transaction.default_from_subloc_info(x_cascaded_table, n);
153 END IF;
154
155 rcv_roi_transaction.default_to_subloc_info(x_cascaded_table, n);
156 /* FPJ WMS CHANGES END */
157
158 IF (g_asn_debug = 'Y') THEN
159 asn_debug.put_line('Set Location_id = ' || TO_CHAR(x_cascaded_table(n).location_id));
160 END IF;
161
162 IF (g_asn_debug = 'Y') THEN
163 asn_debug.put_line('Exit default_vendor_correct');
164 END IF;
165
166 IF default_return%ISOPEN THEN
167 CLOSE default_return;
168 END IF;
169
170 IF default_return_rti%ISOPEN THEN
171 CLOSE default_return_rti;
172 END IF;
173 EXCEPTION
174 WHEN OTHERS THEN
175 NULL;
176 END default_return_line;
177
178 PROCEDURE derive_reason_info(
179 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
180 n IN OUT NOCOPY BINARY_INTEGER
181 ) IS
182 reason_id_record rcv_shipment_line_sv.reason_id_record_type;
183 BEGIN
184 IF (x_cascaded_table(n).error_status IN('S', 'W'))
185 AND ( x_cascaded_table(n).reason_id IS NULL
186 AND x_cascaded_table(n).reason_name IS NOT NULL) THEN
187 IF (g_asn_debug = 'Y') THEN
188 asn_debug.put_line('X_progress ' || x_progress);
189 END IF;
190
191 reason_id_record.reason_name := x_cascaded_table(n).reason_name;
192 reason_id_record.error_record.error_status := 'S';
193 reason_id_record.error_record.error_message := NULL;
194
195 IF (g_asn_debug = 'Y') THEN
196 asn_debug.put_line('Derive Reason_id');
197 END IF;
198
199 rcv_transactions_interface_sv.get_reason_id(reason_id_record);
200 x_cascaded_table(n).reason_id := reason_id_record.reason_id;
201 x_cascaded_table(n).error_status := reason_id_record.error_record.error_status;
202 x_cascaded_table(n).error_message := reason_id_record.error_record.error_message;
203 END IF;
204 END derive_reason_info;
205
206 PROCEDURE derive_ship_to_org_info(
207 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
208 n IN OUT NOCOPY BINARY_INTEGER,
209 x_header_record IN rcv_roi_preprocessor.header_rec_type
210 ) IS
211 ship_to_org_record rcv_shipment_object_sv.organization_id_record_type;
212 x_to_organization_code VARCHAR2(5);
213 BEGIN
214 -- default org from header in case it is null at the line level
215
216 IF x_cascaded_table(n).to_organization_code IS NULL
217 AND x_cascaded_table(n).error_status IN('S', 'W') THEN
218 IF (g_asn_debug = 'Y') THEN
219 asn_debug.put_line('Attempting to default the org from the ship to location');
220 END IF;
221
222 IF (x_cascaded_table(n).ship_to_location_code IS NOT NULL) THEN
223 SELECT max(mp.organization_code)
224 INTO x_to_organization_code
225 FROM hr_locations hl,
226 mtl_parameters mp
227 WHERE x_cascaded_table(n).ship_to_location_code = hl.location_code
228 AND hl.inventory_organization_id = mp.organization_id;
229
230 x_cascaded_table(n).to_organization_code := x_to_organization_code;
231
232 IF (g_asn_debug = 'Y') THEN
233 asn_debug.put_line('Set Org Code using location code = ' || x_cascaded_table(n).to_organization_code);
234 END IF;
235 END IF;
236
237 IF ( x_cascaded_table(n).to_organization_code IS NULL
238 AND x_header_record.header_record.ship_to_organization_code IS NOT NULL) THEN
239 IF (g_asn_debug = 'Y') THEN
240 asn_debug.put_line('Will default org change DUH to ' || x_header_record.header_record.ship_to_organization_code);
241 END IF;
242
243 x_cascaded_table(n).to_organization_code := x_header_record.header_record.ship_to_organization_code;
244 END IF;
245 END IF;
246
247 -- call derivation procedures if conditions are met
248
249 IF (x_cascaded_table(n).error_status IN('S', 'W'))
250 AND ( x_cascaded_table(n).to_organization_id IS NULL
251 AND x_cascaded_table(n).to_organization_code IS NOT NULL) THEN
252 IF (g_asn_debug = 'Y') THEN
253 asn_debug.put_line('X_Progress ' || x_progress);
254 END IF;
255
256 ship_to_org_record.organization_code := x_cascaded_table(n).to_organization_code;
257 ship_to_org_record.organization_id := x_cascaded_table(n).to_organization_id;
258 ship_to_org_record.error_record.error_status := 'S';
259 ship_to_org_record.error_record.error_message := NULL;
260
261 IF (g_asn_debug = 'Y') THEN
262 asn_debug.put_line('Into Derive Organization Record Procedure');
263 END IF;
264
265 po_orgs_sv.derive_org_info(ship_to_org_record);
266
267 IF (g_asn_debug = 'Y') THEN
268 asn_debug.put_line('Debug Output after organization procedure');
269 asn_debug.put_line(ship_to_org_record.organization_code);
270 asn_debug.put_line(TO_CHAR(ship_to_org_record.organization_id));
271 asn_debug.put_line(ship_to_org_record.error_record.error_status);
272 asn_debug.put_line('Debug organization output over');
273 END IF;
274
275 x_cascaded_table(n).to_organization_code := ship_to_org_record.organization_code;
276 x_cascaded_table(n).to_organization_id := ship_to_org_record.organization_id;
277 x_cascaded_table(n).error_status := ship_to_org_record.error_record.error_status;
278 x_cascaded_table(n).error_message := ship_to_org_record.error_record.error_message;
279 END IF;
280 END derive_ship_to_org_info;
281
282 PROCEDURE derive_return_line_qty(
283 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
284 n IN OUT NOCOPY BINARY_INTEGER,
285 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type
286 ) IS
287 quantity_not_postive_exception EXCEPTION;
288 no_parent_line_exception EXCEPTION;
289 reject_exception EXCEPTION;
290 x_record_count NUMBER;
291 x_remaining_quantity NUMBER := 0;
292 x_remaining_qty_po_uom NUMBER := 0;
293 x_bkp_qty NUMBER := 0;
294 x_progress VARCHAR2(3);
295 x_to_organization_code VARCHAR2(5);
296 x_converted_trx_qty NUMBER := 0;
297 transaction_ok BOOLEAN := FALSE;
298 rows_fetched NUMBER := 0;
299 x_tolerable_qty NUMBER := 0;
300 x_first_trans BOOLEAN := TRUE;
301 x_sysdate DATE := SYSDATE;
302 current_n BINARY_INTEGER := 0;
303 insert_into_table BOOLEAN := FALSE;
304 x_qty_rcv_exception_code po_line_locations.qty_rcv_exception_code%TYPE;
305 tax_amount_factor NUMBER;
306 po_asn_uom_qty NUMBER;
307 po_primary_uom_qty NUMBER;
308 already_allocated_qty NUMBER := 0;
309 x_item_id NUMBER;
310 x_approved_flag VARCHAR(1);
311 x_cancel_flag VARCHAR(1);
312 x_closed_code VARCHAR(25);
313 x_shipment_type VARCHAR(25);
314 x_ship_to_organization_id NUMBER;
315 x_ship_to_location_id NUMBER;
316 x_vendor_product_num VARCHAR(25);
317 x_temp_count NUMBER;
318 x_full_name VARCHAR2(240) := NULL; -- Bug 2392074
319 x_secondary_available_qty NUMBER := 0;
320
321 /********************************************************************/
322 CURSOR derive_return(
323 v_parent_trx_id NUMBER
324 ) IS
325 SELECT rsl.item_id,
326 rt.po_line_id,
327 rt.transaction_type,
328 rt.po_header_id,
329 rt.po_line_location_id,
330 rt.parent_transaction_id,
331 rt.primary_unit_of_measure,
332 rt.quantity,
333 rt.transaction_id,
334 rt.unit_of_measure
335 FROM rcv_transactions rt,
336 rcv_shipment_lines rsl
337 WHERE transaction_id = v_parent_trx_id
338 AND rt.shipment_line_id = rsl.shipment_line_id;
339
340 CURSOR derive_return_rti(
341 v_parent_interface_txn_id NUMBER
342 ) IS
343 SELECT rti.item_id,
344 rti.po_line_id,
345 rti.transaction_type,
346 rti.po_header_id,
347 rti.po_line_location_id,
348 rti.parent_transaction_id,
349 rti.primary_unit_of_measure,
350 rti.quantity,
351 rti.interface_transaction_id transaction_id,
352 rti.unit_of_measure
353 FROM rcv_transactions_interface rti
354 WHERE interface_transaction_id = v_parent_interface_txn_id;
355
356 x_derive_returnrec derive_return%ROWTYPE;
357 x_temp_parent_trx_qty NUMBER := 0;
358 x_converted_parent_trx_qty NUMBER := 0;
359 x_temp_convert_parent_trx_qty NUMBER := 0;
360 x_remaining_qty_parent_uom NUMBER := 0;
361 l_to_organization_id NUMBER := 0;
362 l_supply_type_code rcv_supply.supply_type_code%TYPE;
363 l_transaction_type rcv_transactions.transaction_type%TYPE;
364 x_temp_already_allocated_qty NUMBER := 0;
365 derive_values_from_table BOOLEAN := FALSE;
366 derive_values_from_rti BOOLEAN := FALSE;
367 already_derived BOOLEAN := FALSE;
368 cascaded_table_index NUMBER;
369 l_parent_transaction_type rcv_transactions.transaction_type%TYPE;
370 l_grand_parent_trx_id rcv_transactions.parent_transaction_id%TYPE;
371 temp_index NUMBER;
372 l_po_header_id po_headers_all.po_header_id%type;
373 l_return_status VARCHAR2(1) :='S';
374 l_complex_flag varchar2(1);
375
376 BEGIN
377 asn_debug.put_line('enter derive_quantity ' || x_cascaded_table(n).parent_transaction_id);
378
379 -- try to derive the parent_trx_id from rt. if the parent line is not in rt,
380 -- try get it from rti or plsql table.
381 IF (x_cascaded_table(n).parent_transaction_id IS NULL) THEN
382 rcv_roi_transaction.derive_parent_id(x_cascaded_table, n);
383 END IF;
384
385 IF (x_cascaded_table(n).parent_transaction_id IS NOT NULL) THEN --{
386 asn_debug.put_line('open rt cursor with parent_trx_id: ' || x_cascaded_table(n).parent_transaction_id);
387 OPEN derive_return(x_cascaded_table(n).parent_transaction_id);
388 ELSE
389 IF --{
390 ( (x_cascaded_table(n).error_status <> 'E')
391 AND (x_cascaded_table(n).derive_index = 0)) THEN
392 /** This means that there was no error in derive_parent_id()
393 * but the parent_trx_id is not populated. The line is a child
394 * and need to get the values from the rti or the plsql table.
395 * If derive_values_from_table is true we will
396 * derive the values from the pl/sql tables later
397 * at the time when we try to see which cursor is open.
398 */
399 asn_debug.put_line('open rti cursor');
400 OPEN derive_return_rti(x_cascaded_table(n).parent_interface_txn_id);
401 END IF;
402 END IF; --}
403
404 /******************************************************************/
405 --check line quanity > 0
406 x_progress := '097';
407
408 IF (g_asn_debug = 'Y') THEN
409 asn_debug.put_line('X_progress ' || x_progress);
410 END IF;
411
412 IF x_cascaded_table(n).error_status IN('S', 'W')
413 AND x_cascaded_table(n).quantity <= 0 THEN --{
414 IF (g_asn_debug = 'Y') THEN
415 asn_debug.put_line('Quantity is <= zero. quantity derive will fail');
416 END IF;
417
418 RAISE quantity_not_postive_exception;
419 END IF; --} end qty > 0 check
420
421 -- the following steps will create a set of rows linking the line_record with
422 -- its corresponding po_line_location rows until the quantity value from
423 -- the asn is consumed. (Cascade)
424 -- For return, there will be no cascade since there's only 1 parent txn per
425 -- return, the temp_cascade_table will be deprecated in phase 2 -pjiang
426
427 /* 2119137 : If the user populates rcv_transactions_interface
428 with po_line_id, then ROI errors out with
429 RCV_ASN_NO_PO_LINE_LOCATION_ID when the document_line_num
430 is not provided for one time items. Modified the "if" criteria in
431 such a way that the ROI validation does'nt error out when
432 po_line_id is populated for one time items. */
433 x_progress := '098';
434
435 IF (g_asn_debug = 'Y') THEN
436 asn_debug.put_line('X_progress ' || x_progress);
437 END IF;
438
439 IF ( ( x_cascaded_table(n).parent_transaction_id IS NOT NULL
440 OR x_cascaded_table(n).parent_interface_txn_id IS NOT NULL)
441 AND x_cascaded_table(n).error_status IN('S', 'W')) THEN --{
442 -- Copy record from main table to temp table
443 IF (g_asn_debug = 'Y') THEN
444 asn_debug.put_line('Copy record from main table to temp table');
445 END IF;
446
447 current_n := 1;
448 temp_cascaded_table(current_n) := x_cascaded_table(n);
449
450 -- Get all rows which meet this condition
451 IF (g_asn_debug = 'Y') THEN
452 asn_debug.put_line('Get all rows which meet this condition');
453 asn_debug.put_line('Transaction Type = ' || x_cascaded_table(n).transaction_type);
454 asn_debug.put_line('Auto Transact Code = ' || x_cascaded_table(n).auto_transact_code);
455 END IF;
456
457 -- Assign shipped quantity to remaining quantity
458 IF (g_asn_debug = 'Y') THEN
459 asn_debug.put_line('Assign populated quantity to remaining quantity');
460 asn_debug.put_line('Pointer in temp_cascade ' || TO_CHAR(current_n));
461 END IF;
462
463 x_remaining_quantity := temp_cascaded_table(current_n).quantity;
464 x_bkp_qty := x_remaining_quantity; -- used for decrementing cum qty for first record
465 x_remaining_qty_po_uom := 0;
466
467 IF (g_asn_debug = 'Y') THEN
468 asn_debug.put_line('Have assigned the quantity');
469 END IF;
470
471 -- Calculate tax_amount_factor for calculating tax_amount for
472 -- each cascaded line
473
474 tax_amount_factor := NVL(temp_cascaded_table(current_n).tax_amount, 0) / x_remaining_quantity;
475
476 IF (g_asn_debug = 'Y') THEN
477 asn_debug.put_line('Tax Factor ' || TO_CHAR(tax_amount_factor));
478 asn_debug.put_line('transaction Quantity : ' || TO_CHAR(x_remaining_quantity));
479 END IF;
480
481 x_first_trans := TRUE;
482 transaction_ok := FALSE;
483
484 IF (g_asn_debug = 'Y') THEN
485 asn_debug.put_line('Before starting Cascade');
486 END IF;
487
488 IF (g_asn_debug = 'Y') THEN
489 asn_debug.put_line('Record Count = ' || x_record_count);
490 END IF;
491
492 LOOP --{ The loop will be removed since there is no cascading.
493 IF (g_asn_debug = 'Y') THEN
494 asn_debug.put_line('Backup Qty ' || TO_CHAR(x_bkp_qty));
495 asn_debug.put_line('Remaining Quantity ' || TO_CHAR(x_remaining_quantity));
496 END IF;
497
498 /*
499 ** Fetch the appropriate record
500 */
501 IF (g_asn_debug = 'Y') THEN
502 asn_debug.put_line('DEBUG: transaction_type = ' || x_cascaded_table(n).transaction_type);
503 END IF;
504
505 IF (derive_return%ISOPEN) THEN --{
506 asn_debug.put_line('fetch derive_return');
507 FETCH derive_return INTO x_derive_returnrec;
508
509 IF (derive_return%NOTFOUND) THEN
510 EXIT;
511 END IF;
512
513 rows_fetched := derive_return%ROWCOUNT;
514 ELSIF(derive_return_rti%ISOPEN) THEN --}{
515 asn_debug.put_line('pjiang fetch derive_return_rti');
516 FETCH derive_return_rti INTO x_derive_returnrec;
517
518 IF (derive_return_rti%NOTFOUND) THEN
519 EXIT;
520 END IF;
521
522 rows_fetched := derive_return_rti%ROWCOUNT;
523 ELSIF(temp_cascaded_table(current_n).derive = 'Y') THEN --}{
524 /* GET VALUES FROM THE PLSQL TABLE */
525 asn_debug.put_line('pjiang: fetch pl/sql table');
526 temp_index := temp_cascaded_table(current_n).derive_index;
527 x_derive_returnrec.po_line_location_id := x_cascaded_table(temp_index).po_line_location_id;
528 x_derive_returnrec.po_header_id := x_cascaded_table(temp_index).po_header_id;
529 x_derive_returnrec.po_line_id := x_cascaded_table(temp_index).po_line_id;
530 x_derive_returnrec.unit_of_measure := x_cascaded_table(temp_index).unit_of_measure;
531 x_derive_returnrec.parent_transaction_id := x_cascaded_table(temp_index).parent_transaction_id;
532 x_derive_returnrec.transaction_type := x_cascaded_table(temp_index).transaction_type;
533 x_derive_returnrec.quantity := x_cascaded_table(temp_index).quantity;
534 x_derive_returnrec.primary_unit_of_measure := x_cascaded_table(temp_index).primary_unit_of_measure;
535 x_derive_returnrec.item_id := x_cascaded_table(temp_index).item_id;
536 x_derive_returnrec.transaction_id := NULL;
537 rows_fetched := 1;
538 END IF; --}
539
540
541 /* R12 Complex work.
542 * We do not support any other receiving transactions other
543 * than direct receipt. Error out if it is complex work PO.
544 */
545
546 PO_COMPLEX_WORK_GRP.is_complex_work_po(
547 1.0,
548 x_cascaded_table(n).po_header_id,
549 l_return_status,
550 l_complex_flag);
551
552 IF (l_return_status IS NOT NULL AND
553 l_return_status = FND_API.g_ret_sts_success) THEN
554 IF( g_asn_debug = 'Y' ) THEN
555 asn_debug.put_line('l_return_status ' || l_return_status);
556 asn_debug.put_line('l_po_header_id ' || l_po_header_id);
557 END IF;
558 ELSE
559 IF( g_asn_debug = 'Y') THEN
560 asn_debug.put_line('l_return_status ' || l_return_status);
561 END IF;
562 END IF;
563
564 IF (l_complex_flag = 'Y') THEN
565 asn_debug.put_line('We do not support transaction type ' || x_cascaded_table(n).transaction_type || ' for complex work POs');
566 rcv_error_pkg.set_error_message('RCV_INVALID_TRANSACTION_TYPE');
567 rcv_error_pkg.log_interface_error('TRANSACTION_TYPE');
568 End if;
569
570 l_parent_transaction_type := x_derive_returnrec.transaction_type;
571 l_grand_parent_trx_id := x_derive_returnrec.parent_transaction_id;
572
573 IF (g_asn_debug = 'Y') THEN
574 asn_debug.put_line('Parent transaction rows fetched: (should always be 1 for return) ' || TO_CHAR(rows_fetched));
575 asn_debug.put_line('po_line_id ' || TO_CHAR(x_derive_returnrec.po_line_id));
576 asn_debug.put_line('po_line_location_id ' || TO_CHAR(x_derive_returnrec.po_line_location_id));
577 -- since there can only be one parent trx, there will be only one allocating
578 asn_debug.put_line('the quantity available for return in parent txn: ' || x_remaining_quantity);
579 END IF;
580
581 temp_cascaded_table(current_n).unit_of_measure := x_derive_returnrec.unit_of_measure;
582 asn_debug.put_line(' Entering qty calculation for return');
583
584 IF (x_first_trans)
585 AND temp_cascaded_table(current_n).item_id IS NULL THEN
586 temp_cascaded_table(current_n).item_id := x_derive_returnrec.item_id;
587 temp_cascaded_table(current_n).primary_unit_of_measure := x_derive_returnrec.primary_unit_of_measure;
588 END IF;
589
590 insert_into_table := FALSE;
591 already_allocated_qty := 0;
592 rcv_roi_transaction.get_interface_available_qty(temp_cascaded_table,
593 current_n,
594 x_converted_parent_trx_qty
595 );
596
597 IF (g_asn_debug = 'Y') THEN
598 asn_debug.put_line('return interface qty ' || x_converted_parent_trx_qty);
599 END IF;
600
601 IF (x_converted_parent_trx_qty = 0) THEN --{
602 IF (temp_cascaded_table(current_n).derive = 'Y') -- derive from plsql table or rti
603 THEN --{
604 x_converted_parent_trx_qty := x_derive_returnrec.quantity;
605 asn_debug.put_line(' derive parent ' || temp_cascaded_table(current_n).parent_interface_txn_id);
606 asn_debug.put_line(' from derive quantity ' || TO_CHAR(x_converted_parent_trx_qty));
607 /* Get x_converted_parent_trx_qty from the pl/sql table or cascaded_table as needed.*/
608 ELSE --}{
609 IF (g_asn_debug = 'Y') THEN --{_
610 asn_debug.put_line('calling get_available_qty for return (Negative Correct)' || x_derive_returnrec.transaction_type);
611 asn_debug.put_line('parameters passed in ............');
612 asn_debug.put_line('1. transaction_type: ' || x_cascaded_table(n).transaction_type);
613 asn_debug.put_line('2. parent id: ' || x_derive_returnrec.transaction_id);
614 asn_debug.put_line('3. receipt source code: ' || x_cascaded_table(n).receipt_source_code);
615 asn_debug.put_line('4. parent transaction_type: ' || x_derive_returnrec.transaction_type);
616 asn_debug.put_line('5. grand parent id: ' || 'NULL');
617 asn_debug.put_line('6. correction type: ' || 'NEGATIVE');
618 asn_debug.put_line('7. available quantity: ' || TO_CHAR(x_converted_parent_trx_qty));
619 asn_debug.put_line('8. tolerable_quantity: ' || TO_CHAR(x_tolerable_qty));
620 asn_debug.put_line('9. unit of measure: ' || x_derive_returnrec.unit_of_measure);
621 asn_debug.put_line('10. 2nd available quantity : ' || TO_CHAR(x_secondary_available_qty));
622 END IF; --}
623
624 rcv_quantities_s.get_available_quantity(x_cascaded_table(n).transaction_type,
625 x_derive_returnrec.transaction_id,
626 x_cascaded_table(n).receipt_source_code,
627 x_derive_returnrec.transaction_type,
628 NULL,
629 'NEGATIVE',
630 x_converted_parent_trx_qty,
631 x_tolerable_qty,
632 x_derive_returnrec.unit_of_measure,
633 x_secondary_available_qty
634 );
635 --}
636
637 asn_debug.put_line('qty from GET_AVAILABLE_QUANTITY for return is ' || x_converted_parent_trx_qty);
638
639 IF already_allocated_qty > 0 --?????what if <0 caused by positive return?
640 THEN --{
641 x_converted_parent_trx_qty := x_converted_parent_trx_qty - already_allocated_qty;
642 x_tolerable_qty := x_tolerable_qty - already_allocated_qty;
643
644 IF (g_asn_debug = 'Y') THEN
645 asn_debug.put_line(' For return some allocated quantity. Will reduce qty');
646 asn_debug.put_line('Allocated Qty ' || TO_CHAR(already_allocated_qty));
647 asn_debug.put_line('After reducing by allocated qty');
648 asn_debug.put_line('Available Quantity ' || TO_CHAR(x_converted_parent_trx_qty));
649 asn_debug.put_line('Tolerable Quantity ' || TO_CHAR(x_tolerable_qty));
650 asn_debug.put_line('Pointer to temp table ' || TO_CHAR(current_n));
651 END IF;
652 END IF; /* if already_allocated_qty is >0 for return */ --}
653 END IF; /*derive_values_from_rti is false */--}
654 END IF; --} interface_available_qty is 0.
655
656 x_remaining_qty_parent_uom := rcv_transactions_interface_sv.convert_into_correct_qty(x_remaining_quantity,
657 temp_cascaded_table(1).unit_of_measure,
658 temp_cascaded_table(1).item_id,
659 x_derive_returnrec.unit_of_measure
660 );
661
662 IF (x_remaining_qty_parent_uom <= 0) -- this is redundant with the qty check????
663 THEN
664 IF (g_asn_debug = 'Y') THEN
665 asn_debug.put_line(' Return Needs an error message in the interface tables');
666 RAISE quantity_not_postive_exception;
667 END IF;
668 ELSE
669 /* Converted successfully and have some quantity on which we can act */
670 IF (g_asn_debug = 'Y') THEN
671 asn_debug.put_line('Converted trx qty that is available ' || x_converted_parent_trx_qty);
672 asn_debug.put_line('Remaining qty in parents uom that is available ' || x_remaining_qty_parent_uom);
673 END IF;
674
675 IF (x_converted_parent_trx_qty > 0) THEN --{
676 IF (x_converted_parent_trx_qty < x_remaining_qty_parent_uom) THEN --{
677 /* Total quantity available to return is less than the qty
678 * that the user wants to return. Hence we would error out but
679 * to keep the old code we will get the remaining code here and
680 * error out later.
681 */
682 x_remaining_qty_parent_uom := x_remaining_qty_parent_uom - x_converted_parent_trx_qty;
683
684 IF (temp_cascaded_table(current_n).unit_of_measure <> x_derive_returnrec.unit_of_measure) THEN
685 x_remaining_quantity := rcv_transactions_interface_sv.convert_into_correct_qty(x_remaining_qty_parent_uom,
686 x_derive_returnrec.unit_of_measure,
687 temp_cascaded_table(1).item_id,
688 temp_cascaded_table(1).unit_of_measure
689 );
690 ELSE
691 x_remaining_quantity := x_remaining_qty_parent_uom;
692 END IF;
693
694 insert_into_table := TRUE;
695 ELSE --}{
696 IF (g_asn_debug = 'Y') THEN
697 asn_debug.put_line('We are in >= Qty branch ');
698 END IF;
699
700 x_converted_parent_trx_qty := x_remaining_qty_parent_uom;
701 insert_into_table := TRUE;
702 x_remaining_qty_parent_uom := 0;
703 x_remaining_quantity := 0;
704 END IF; --} /* if (x_converted_parent_trx_qty < x_remaining_quantity) then */
705 ELSE /* x_converted_parent_trx_qty >0 */ --}{
706 IF rows_fetched = x_record_count THEN -- {
707 -- last row needs to be inserted anyway
708 -- so that the row can be used based on qty tolerance checks
709 IF (g_asn_debug = 'Y') THEN
710 asn_debug.put_line('Quantity is less then 0 but last record');
711 END IF;
712
713 insert_into_table := TRUE;
714 x_converted_trx_qty := 0;
715 ELSE --}{
716 IF (g_asn_debug = 'Y') THEN
717 asn_debug.put_line('<= 0 Quantity but more records in cursor');
718 END IF;
719
720 x_remaining_qty_po_uom := 0; -- we may have a diff uom on the next iteration
721
722 IF (g_asn_debug = 'Y') THEN
723 asn_debug.put_line('We have to deal with remaining_qty > 0 and x_converted_trx_qty -ve');
724 END IF;
725
726 insert_into_table := FALSE;
727 END IF; --}
728 END IF; /*x_converted_parent_trx_qty >0 */ --}
729 END IF;
730
731 /* Converted qty successfully and we have some quantity on which we can act */
732 asn_debug.put_line('Transaction qty in terms of the parents uom is ' || x_converted_parent_trx_qty);
733
734 IF insert_into_table THEN --{ --start pjiang
735 IF (x_first_trans) THEN --{
736 IF (g_asn_debug = 'Y') THEN
737 asn_debug.put_line('First Time ' || TO_CHAR(current_n));
738 END IF;
739
740 x_first_trans := FALSE;
741 ELSE --}{
742 IF (g_asn_debug = 'Y') THEN
743 asn_debug.put_line('Next Time ' || TO_CHAR(current_n));
744 END IF;
745
746 temp_cascaded_table(current_n) := temp_cascaded_table(current_n - 1);
747 END IF; --}
748
749 temp_cascaded_table(current_n).po_header_id := x_derive_returnrec.po_header_id;
750 temp_cascaded_table(current_n).po_line_id := x_derive_returnrec.po_line_id;
751 temp_cascaded_table(current_n).po_line_location_id := x_derive_returnrec.po_line_location_id;
752 temp_cascaded_table(current_n).primary_unit_of_measure := x_derive_returnrec.primary_unit_of_measure;
753 temp_cascaded_table(current_n).quantity := x_converted_parent_trx_qty;
754
755 IF (temp_cascaded_table(current_n).primary_unit_of_measure <> x_derive_returnrec.unit_of_measure) THEN
756 temp_cascaded_table(current_n).primary_quantity := rcv_transactions_interface_sv.convert_into_correct_qty(x_converted_parent_trx_qty,
757 x_derive_returnrec.unit_of_measure,
758 temp_cascaded_table(current_n).item_id,
759 temp_cascaded_table(current_n).primary_unit_of_measure
760 );
761 ELSE
762 temp_cascaded_table(current_n).primary_quantity := x_converted_parent_trx_qty;
763 END IF;
764
765 asn_debug.put_line('Transaction qty in terms of the primary uom is ' || temp_cascaded_table(current_n).primary_quantity);
766 current_n := current_n + 1;
767 END IF; --}
768 /* Get the available qty in PRIMARY UOM */
769 /*
770 PO_UOM_S.UOM_CONVERT (x_converted_trx_qty,
771 l_uom,
772 x_item_id,
773 x_primary_uom,
774 l_primary_available_qty );
775 */
776 END LOOP; --}
777
778 -- post_fetch_action (x_cascaded_table, n, temp_cascaded_table);
779
780 /* WMS CHANGE.
781 * If derive_inv_quantity returns error, then we set error_status
782 * to E. Close the cursors and return.
783 */
784 IF (x_cascaded_table(n).error_status = 'E') THEN
785 IF derive_return%ISOPEN THEN
786 CLOSE derive_return;
787 END IF;
788
789 IF derive_return_rti%ISOPEN THEN
790 CLOSE derive_return_rti;
791 END IF;
792
793 RETURN;
794 END IF;
795
796 IF (g_asn_debug = 'Y') THEN
797 asn_debug.put_line('Out of the loop');
798 END IF;
799
800 IF NOT x_first_trans THEN
801 -- x_first_trans has been reset which means some cascade has
802 -- happened. Otherwise current_n = 1
803 asn_debug.put_line('current_n before is ' || current_n);
804 current_n := current_n - 1;
805 END IF;
806
807 -- do the tolerance act here
808 IF (g_asn_debug = 'Y') THEN
809 asn_debug.put_line('Temp table pointer ' || TO_CHAR(current_n));
810 asn_debug.put_line('Check which condition has occured');
811 END IF;
812
813 -- lastrecord...we have run out of rows and we still have quantity to allocate
814 -- for return, this means we are trying to return more than the quantity of parent.
815 -- We should simply error out this situation.
816 IF x_remaining_quantity > 0 THEN --{
817 -- reject the transaction if this is the case. Can't return more than what we have.
818 RAISE reject_exception;
819
820 IF (g_asn_debug = 'Y') THEN
821 asn_debug.put_line('There is quantity remaining ');
822 asn_debug.put_line('tolerable quantity now in plsql table ' || temp_cascaded_table(current_n).quantity);
823 END IF;
824 ELSE -- }{
825 IF (g_asn_debug = 'Y') THEN
826 asn_debug.put_line('Remaining UOM quantity is zero ' || TO_CHAR(x_remaining_quantity));
827 asn_debug.put_line('Return the cascaded rows back to the calling procedure');
828 END IF;
829 END IF; --} ends the check for whether last record has been reached
830
831 asn_debug.put_line('before exit current_n is ' || current_n);
832 ELSE --} {
833 -- error_status and error_message are set after validate_quantity_shipped
834 IF (g_asn_debug = 'Y') THEN
835 asn_debug.put_line('No parent_transaction_id/parent_interface_trx_id ');
836 END IF;
837
838 IF (g_asn_debug = 'Y') THEN
839 asn_debug.put_line('Status = ' || x_cascaded_table(n).error_status);
840 END IF;
841
842 IF x_cascaded_table(n).error_status IN('S', 'W', 'F') THEN --{
843 RAISE no_parent_line_exception;
844 END IF; --}
845
846 RETURN;
847 END IF; -- } of (asn quantity_shipped was valid)
848
849 asn_debug.put_line('before closing cursors current_n is ' || temp_cascaded_table.COUNT);
850
851 IF derive_return%ISOPEN THEN
852 CLOSE derive_return;
853 END IF;
854
855 IF derive_return_rti%ISOPEN THEN
856 CLOSE derive_return_rti;
857 END IF;
858
859 IF (g_asn_debug = 'Y') THEN
860 asn_debug.put_line('Exit explode_line_quantity');
861 END IF;
862 -- handle errors and warnings in exception block
863 EXCEPTION
864 WHEN quantity_not_postive_exception THEN
865 x_cascaded_table(n).error_status := 'F';
866 /* Bug 3250532 : Changed the error message from 'TBD' to 'RCV_ENTER_QTY_GT_ZERO'.
867 */
868 x_cascaded_table(n).error_message := 'RCV_ENTER_QTY_GT_ZERO';
869 rcv_error_pkg.set_error_message(x_cascaded_table(n).error_message);
870 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).item_num);
871 rcv_error_pkg.log_interface_error('QUANTITY', FALSE);
872 WHEN no_parent_line_exception THEN
873 x_cascaded_table(n).error_status := 'E';
874
875 IF (x_cascaded_table(n).error_message IS NULL) THEN
876 /* Bug 3250532 : Changed the error message from 'TBD' to 'RCV_NO_PARENT_TRANSACTION'.
877 */
878 x_cascaded_table(n).error_message := 'RCV_NO_PARENT_TRANSACTION';
879 END IF;
880
881 rcv_error_pkg.set_error_message(x_cascaded_table(n).error_message);
882 rcv_error_pkg.set_token('DOCUMENT_NUM', x_cascaded_table(n).document_num);
883 rcv_error_pkg.log_interface_error('NUMBER', FALSE);
884 WHEN reject_exception THEN
885 x_cascaded_table(n).error_status := 'E';
886 /* Bug 3250532 : Changed the error message from 'TBD' to 'RCV_TRX_QTY_EXCEEDS_AVAILABLE'.
887 */
888 x_cascaded_table(n).error_message := 'RCV_TRX_QTY_EXCEEDS_AVAILABLE';
889 rcv_error_pkg.set_error_message(x_cascaded_table(n).error_message);
890 rcv_error_pkg.set_token('QTY_A', x_cascaded_table(n).quantity);
891 rcv_error_pkg.set_token('QTY_B', x_cascaded_table(n).quantity - x_remaining_quantity);
892 rcv_error_pkg.log_interface_error('QUANTITY', FALSE);
893 temp_cascaded_table.DELETE;
894 WHEN OTHERS THEN
895 IF derive_return%ISOPEN THEN
896 CLOSE derive_return;
897 END IF;
898
899 IF derive_return_rti%ISOPEN THEN
900 CLOSE derive_return_rti;
901 END IF;
902
903 x_cascaded_table(n).error_status := 'F';
904
905 IF (g_asn_debug = 'Y') THEN
906 asn_debug.put_line(TO_CHAR(n));
907 asn_debug.put_line(SQLERRM);
908 asn_debug.put_line('error ' || x_progress);
909 END IF;
910 END derive_return_line_qty;
911
912 PROCEDURE default_common_lines(
913 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
914 n IN BINARY_INTEGER
915 ) IS
916 BEGIN
917 p_trx_record.destination_type_code := x_cascaded_table(n).destination_type_code;
918 p_trx_record.transaction_type := x_cascaded_table(n).transaction_type;
919 p_trx_record.processing_mode_code := x_cascaded_table(n).processing_mode_code;
920 p_trx_record.processing_status_code := x_cascaded_table(n).processing_status_code;
921 p_trx_record.transaction_status_code := x_cascaded_table(n).transaction_status_code;
922 p_trx_record.auto_transact_code := x_cascaded_table(n).auto_transact_code;
923 rcv_roi_header_common.commondefaultcode(p_trx_record);
924 x_cascaded_table(n).destination_type_code := p_trx_record.destination_type_code;
925 x_cascaded_table(n).transaction_type := p_trx_record.transaction_type;
926 x_cascaded_table(n).processing_mode_code := p_trx_record.processing_mode_code;
927 x_cascaded_table(n).processing_status_code := p_trx_record.processing_status_code;
928 x_cascaded_table(n).transaction_status_code := p_trx_record.transaction_status_code;
929 x_cascaded_table(n).auto_transact_code := p_trx_record.auto_transact_code;
930 END default_common_lines;
931
932 PROCEDURE default_po_info(
933 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
934 n IN BINARY_INTEGER,
935 default_return_rec IN default_return%ROWTYPE
936 ) IS
937 BEGIN
938 IF (g_asn_debug = 'Y') THEN
939 asn_debug.put_line('Defaulting return po_revision_num ' || default_return_rec.po_revision_num);
940 asn_debug.put_line('Defaulting return po_header_id ' || default_return_rec.po_header_id);
941 asn_debug.put_line('Defaulting return po_release_id ' || default_return_rec.po_release_id);
942 asn_debug.put_line('Defaulting return po_unit_price ' || default_return_rec.po_unit_price);
943 asn_debug.put_line('Defaulting return po_line_id ' || default_return_rec.po_line_id);
944 asn_debug.put_line('Defaulting return po_line_location_id ' || default_return_rec.po_line_location_id);
945 asn_debug.put_line('Defaulting return po_distribution_id ' || default_return_rec.po_distribution_id);
946 END IF;
947
948 x_cascaded_table(n).po_revision_num := default_return_rec.po_revision_num;
949 x_cascaded_table(n).po_header_id := default_return_rec.po_header_id;
950 x_cascaded_table(n).po_release_id := default_return_rec.po_release_id;
951
952 /* We used to get the unit_price from the cursor where it picks
953 * up from the parent. But since PO unit_price can be change
954 * retroactively, we need to pick up the unit_price from PO
955 * directly. Since we would have derived line_location_id
956 * and po_line_id at this point, use the values here.
957 * x_cascaded_table (n).po_unit_price :=
958 * default_return_rec.po_unit_price;
959 */
960 SELECT NVL(pll.price_override, pl.unit_price)
961 INTO x_cascaded_table(n).po_unit_price
962 FROM po_line_locations pll,
963 po_lines pl
964 WHERE pll.line_location_id = x_cascaded_table(n).po_line_location_id
965 AND pl.po_line_id = x_cascaded_table(n).po_line_id
966 AND pl.po_line_id = pll.po_line_id;
967
968 IF (g_asn_debug = 'Y') THEN
969 asn_debug.put_line('Defaulting Return po_unit_price' || x_cascaded_table(n).po_unit_price);
970 END IF;
971
972 x_cascaded_table(n).po_line_id := default_return_rec.po_line_id;
973 x_cascaded_table(n).po_line_location_id := default_return_rec.po_line_location_id;
974 x_cascaded_table(n).po_distribution_id := default_return_rec.po_distribution_id;
975 END default_po_info;
976
977 PROCEDURE default_shipment_info(
978 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
979 n IN BINARY_INTEGER,
980 default_return_rec IN default_return%ROWTYPE
981 ) IS
982 BEGIN
983 IF (g_asn_debug = 'Y') THEN
984 asn_debug.put_line('Defaulting Return shipment_header_id ' || default_return_rec.shipment_header_id);
985 asn_debug.put_line('Defaulting Return shipment_line_id ' || default_return_rec.shipment_line_id);
986 END IF;
987
988 x_cascaded_table(n).shipment_header_id := default_return_rec.shipment_header_id;
989 x_cascaded_table(n).shipment_line_id := default_return_rec.shipment_line_id;
990 END default_shipment_info;
991
992 PROCEDURE default_wip_info(
993 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
994 n IN BINARY_INTEGER,
995 default_return_rec IN default_return%ROWTYPE
996 ) IS
997 BEGIN
998 IF (g_asn_debug = 'Y') THEN
999 asn_debug.put_line('Defaulting return wip_entity_id ' || default_return_rec.wip_entity_id);
1000 asn_debug.put_line('Defaulting return wip_line_id ' || default_return_rec.wip_line_id);
1001 asn_debug.put_line('Defaulting return wip_repetitive_schedule_id ' || default_return_rec.wip_repetitive_schedule_id);
1002 asn_debug.put_line('Defaulting return wip_operation_seq_num ' || default_return_rec.wip_operation_seq_num);
1003 asn_debug.put_line('Defaulting return wip_resource_seq_num ' || default_return_rec.wip_resource_seq_num);
1004 END IF;
1005
1006 x_cascaded_table(n).wip_entity_id := default_return_rec.wip_entity_id;
1007 x_cascaded_table(n).wip_line_id := default_return_rec.wip_line_id;
1008 x_cascaded_table(n).wip_repetitive_schedule_id := default_return_rec.wip_repetitive_schedule_id;
1009 x_cascaded_table(n).wip_operation_seq_num := default_return_rec.wip_operation_seq_num;
1010 x_cascaded_table(n).wip_resource_seq_num := default_return_rec.wip_resource_seq_num;
1011 END default_wip_info;
1012
1013 PROCEDURE default_oe_info(
1014 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1015 n IN BINARY_INTEGER,
1016 default_return_rec IN default_return%ROWTYPE
1017 ) IS
1018 BEGIN
1019 IF (g_asn_debug = 'Y') THEN
1020 asn_debug.put_line('Defaulting return oe_order_header_id ' || default_return_rec.oe_order_header_id);
1021 asn_debug.put_line('Defaulting return oe_order_line_id ' || default_return_rec.oe_order_line_id);
1022 END IF;
1023
1024 x_cascaded_table(n).oe_order_header_id := default_return_rec.oe_order_header_id;
1025 x_cascaded_table(n).oe_order_line_id := default_return_rec.oe_order_line_id;
1026 END default_oe_info;
1027
1028 PROCEDURE default_currency_info(
1029 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1030 n IN BINARY_INTEGER,
1031 default_return_rec IN default_return%ROWTYPE
1032 ) IS
1033 BEGIN
1034 IF (g_asn_debug = 'Y') THEN
1035 asn_debug.put_line('Defaulting return currency_code ' || default_return_rec.currency_code);
1036 asn_debug.put_line('Defaulting return currency_conversion_type ' || default_return_rec.currency_conversion_type);
1037 asn_debug.put_line('Defaulting return currency_conversion_rate ' || default_return_rec.currency_conversion_rate);
1038 asn_debug.put_line('Defaulting return currency_conversion_date ' || default_return_rec.currency_conversion_date);
1039 END IF;
1040
1041 x_cascaded_table(n).currency_code := default_return_rec.currency_code;
1042 x_cascaded_table(n).currency_conversion_type := default_return_rec.currency_conversion_type;
1043 x_cascaded_table(n).currency_conversion_rate := default_return_rec.currency_conversion_rate;
1044 x_cascaded_table(n).currency_conversion_date := default_return_rec.currency_conversion_date;
1045 END default_currency_info;
1046
1047 PROCEDURE default_vendor_info(
1048 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1049 n IN BINARY_INTEGER,
1050 default_return_rec IN default_return%ROWTYPE
1051 ) IS
1052 BEGIN
1053 IF (g_asn_debug = 'Y') THEN
1054 asn_debug.put_line('Defaulting Return vendor_id ' || default_return_rec.vendor_id);
1055 asn_debug.put_line('Defaulting Return vendor_site_id ' || default_return_rec.vendor_site_id);
1056 END IF;
1057
1058 x_cascaded_table(n).vendor_id := default_return_rec.vendor_id;
1059 x_cascaded_table(n).vendor_site_id := default_return_rec.vendor_site_id;
1060 END default_vendor_info;
1061
1062 PROCEDURE default_customer_info(
1063 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1064 n IN BINARY_INTEGER,
1065 default_return_rec IN default_return%ROWTYPE
1066 ) IS
1067 BEGIN
1068 IF (g_asn_debug = 'Y') THEN
1069 asn_debug.put_line('Defaulting Return customer_id ' || default_return_rec.customer_id);
1070 asn_debug.put_line('Defaulting Return customer_site_id ' || default_return_rec.customer_site_id);
1071 END IF;
1072
1073 x_cascaded_table(n).customer_id := default_return_rec.customer_id;
1074 x_cascaded_table(n).customer_site_id := default_return_rec.customer_site_id;
1075 END default_customer_info;
1076
1077 PROCEDURE default_deliver_to_info(
1078 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1079 n IN BINARY_INTEGER,
1080 default_return_rec IN default_return%ROWTYPE
1081 ) IS
1082 BEGIN
1083 IF (g_asn_debug = 'Y') THEN
1084 asn_debug.put_line('Defaulting Return deliver_to_person_id ' || default_return_rec.deliver_to_person_id);
1085 asn_debug.put_line('Defaulting Return deliver_to_location_id ' || default_return_rec.deliver_to_location_id);
1086 END IF;
1087
1088 x_cascaded_table(n).deliver_to_person_id := default_return_rec.deliver_to_person_id;
1089 x_cascaded_table(n).deliver_to_location_id := default_return_rec.deliver_to_location_id;
1090 END default_deliver_to_info;
1091
1092 PROCEDURE default_source_info(
1093 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1094 n IN BINARY_INTEGER,
1095 default_return_rec IN default_return%ROWTYPE
1096 ) IS
1097 BEGIN
1098 IF (g_asn_debug = 'Y') THEN
1099 asn_debug.put_line('Defaulting return receipt_source_code ' || default_return_rec.receipt_source_code);
1100 asn_debug.put_line('Defaulting return source_document_code ' || default_return_rec.source_document_code);
1101 END IF;
1102
1103 x_cascaded_table(n).receipt_source_code := default_return_rec.receipt_source_code;
1104 x_cascaded_table(n).source_document_code := default_return_rec.source_document_code;
1105 END default_source_info;
1106
1107 PROCEDURE default_item_info(
1108 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1109 n IN BINARY_INTEGER,
1110 default_return_rec IN default_return%ROWTYPE
1111 ) IS
1112 BEGIN
1113 IF (g_asn_debug = 'Y') THEN
1114 asn_debug.put_line('Defaulting return primary_unit_of_measure ' || default_return_rec.primary_unit_of_measure);
1115 asn_debug.put_line('Defaulting return item_description ' || default_return_rec.item_description);
1116 asn_debug.put_line('Defaulting return category_id ' || default_return_rec.category_id);
1117 asn_debug.put_line('Defaulting return department_code ' || default_return_rec.department_code);
1118 asn_debug.put_line('Defaulting return inspection_status_code ' || default_return_rec.inspection_status_code);
1119 asn_debug.put_line('Defaulting return subinventory ' || default_return_rec.subinventory);
1120 END IF;
1121
1122 x_cascaded_table(n).primary_unit_of_measure := default_return_rec.primary_unit_of_measure;
1123 x_cascaded_table(n).item_description := default_return_rec.item_description;
1124 x_cascaded_table(n).category_id := default_return_rec.category_id;
1125 x_cascaded_table(n).department_code := default_return_rec.department_code;
1126 x_cascaded_table(n).inspection_status_code := default_return_rec.inspection_status_code;
1127 x_cascaded_table(n).subinventory := default_return_rec.subinventory;
1128 END default_item_info;
1129
1130 PROCEDURE default_destination_info(
1131 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1132 n IN BINARY_INTEGER,
1133 default_return_rec IN default_return%ROWTYPE
1134 ) IS
1135 BEGIN
1136 IF (g_asn_debug = 'Y') THEN
1137 asn_debug.put_line('Defaulting return destination_context ' || default_return_rec.destination_context);
1138 asn_debug.put_line('Defaulting return to_organization_id ' || default_return_rec.OID);
1139 END IF;
1140
1141 x_cascaded_table(n).destination_context := default_return_rec.destination_context;
1142 x_cascaded_table(n).to_organization_id := default_return_rec.OID;
1143 END default_destination_info;
1144
1145 PROCEDURE default_location_info(
1146 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1147 n IN BINARY_INTEGER,
1148 default_return_rec IN default_return%ROWTYPE
1149 ) IS
1150 BEGIN
1151 IF (g_asn_debug = 'Y') THEN
1152 asn_debug.put_line('Defaulting return location_id ' || default_return_rec.location_id);
1153 asn_debug.put_line('Defaulting return locator_id ' || default_return_rec.locator_id);
1154 END IF;
1155
1156 x_cascaded_table(n).location_id := default_return_rec.location_id;
1157 x_cascaded_table(n).locator_id := default_return_rec.locator_id;
1158 END default_location_info;
1159
1160 PROCEDURE default_movement_id(
1161 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1162 n IN BINARY_INTEGER,
1163 default_return_rec IN default_return%ROWTYPE
1164 ) IS
1165 BEGIN
1166 IF (g_asn_debug = 'Y') THEN
1167 asn_debug.put_line('Defaulting return movement_id ' || default_return_rec.movement_id);
1168 END IF;
1169
1170 x_cascaded_table(n).movement_id := default_return_rec.movement_id;
1171 END default_movement_id;
1172
1173 PROCEDURE default_bom_resource_id(
1174 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1175 n IN BINARY_INTEGER,
1176 default_return_rec IN default_return%ROWTYPE
1177 ) IS
1178 BEGIN
1179 IF (g_asn_debug = 'Y') THEN
1180 asn_debug.put_line('Defaulting return bom_resource_id ' || default_return_rec.bom_resource_id);
1181 END IF;
1182
1183 x_cascaded_table(n).bom_resource_id := default_return_rec.bom_resource_id;
1184 END default_bom_resource_id;
1185
1186 /* WMS Changes Start */
1187 PROCEDURE derive_inv_qty(
1188 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1189 n IN BINARY_INTEGER
1190 ) IS
1191 BEGIN
1192 /* Bug 3639667.
1193 * We are calling the new procedure instead of changing the calls
1194 * in all the other files where we call the original procedure
1195 * derive_inv_qty.
1196 */
1197 derive_inv_qty_1(x_cascaded_table(n).destination_type_code,
1198 x_cascaded_table(n).transaction_type,
1199 x_cascaded_table(n).quantity,
1200 x_cascaded_table(n).interface_transaction_id,
1201 x_cascaded_table(n).to_organization_id,
1202 x_cascaded_table(n).item_id,
1203 x_cascaded_table(n).item_revision,
1204 x_cascaded_table(n).receipt_source_code,
1205 x_cascaded_table(n).po_header_id,
1206 x_cascaded_table(n).unit_of_measure,
1207 x_cascaded_table(n).primary_unit_of_measure,
1208 x_cascaded_table(n).subinventory,
1209 x_cascaded_table(n).locator_id,
1210 x_cascaded_table(n).transfer_lpn_id,
1211 x_cascaded_table(n).lpn_id,
1212 x_cascaded_table(n).error_status,
1213 x_cascaded_table(n).error_message
1214 );
1215 END derive_inv_qty;
1216
1217 /* Bug 3639667.
1218 * The code here was originally in derive_inv_qty. But we have changed
1219 * the parameters here so that this can be called from the client side
1220 * library for the Enter returns and Enter corrections forms also.
1221 */
1222 PROCEDURE derive_inv_qty_1(
1223 p_destination_type_code IN rcv_transactions_interface.destination_type_code%TYPE,
1224 p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
1225 p_quantity IN rcv_transactions_interface.quantity%TYPE,
1226 p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%TYPE,
1227 p_to_organization_id IN rcv_transactions_interface.to_organization_id%TYPE,
1228 p_item_id IN rcv_transactions_interface.item_id%TYPE,
1229 p_item_revision IN rcv_transactions_interface.item_revision%TYPE,
1230 p_receipt_source_code IN rcv_transactions_interface.receipt_source_code%TYPE,
1231 p_po_header_id IN rcv_transactions_interface.po_header_id%TYPE,
1232 p_unit_of_measure IN rcv_transactions_interface.unit_of_measure%TYPE,
1233 p_primary_unit_of_measure IN rcv_transactions_interface.primary_unit_of_measure%TYPE,
1234 p_subinventory IN rcv_transactions_interface.subinventory%TYPE,
1235 p_locator_id IN rcv_transactions_interface.locator_id%TYPE,
1236 p_transfer_lpn_id IN rcv_transactions_interface.transfer_lpn_id%TYPE,
1237 p_lpn_id IN rcv_transactions_interface.lpn_id%TYPE,
1238 x_error_status IN OUT NOCOPY VARCHAR2,
1239 x_error_message IN OUT NOCOPY VARCHAR2
1240 ) IS
1241 CURSOR lot_rows(
1242 l_interface_id NUMBER
1243 ) IS
1244 SELECT lot_number,
1245 transaction_quantity
1246 FROM mtl_transaction_lots_interface
1247 WHERE product_transaction_id = l_interface_id;
1248
1249 l_lot lot_rows%ROWTYPE;
1250
1251 CURSOR rti_rows(
1252 l_interface_id NUMBER
1253 ) IS
1254 SELECT quantity
1255 FROM rcv_transactions_interface
1256 WHERE interface_transaction_id = l_interface_id;
1257
1258 l_rti rti_rows%ROWTYPE;
1259 l_negative_inv_receipt_code NUMBER;
1260 l_interface_id NUMBER;
1261 l_return_status VARCHAR2(10);
1262 l_msg_count NUMBER;
1263 l_msg_data VARCHAR2(2000);
1264 l_tree_mode NUMBER;
1265 l_is_serial_control BOOLEAN := FALSE;
1266 l_is_lot_control BOOLEAN := FALSE;
1267 l_is_revision_control BOOLEAN := FALSE;
1268 l_demand_source_type_id NUMBER;
1269 l_demand_source_header_id NUMBER;
1270 l_lot_number VARCHAR2(30);
1271 l_update_quantity NUMBER;
1272 l_qoh NUMBER;
1273 l_att NUMBER;
1274 l_rqoh NUMBER;
1275 l_qr NUMBER;
1276 l_qs NUMBER;
1277 l_atr NUMBER;
1278 l_lot_control_code mtl_system_items.lot_control_code%TYPE;
1279 l_serial_number_control_code mtl_system_items.serial_number_control_code%TYPE;
1280 l_revision_qty_control_code mtl_system_items.revision_qty_control_code%TYPE;
1281 l_revision rcv_transactions_interface.item_revision%TYPE;
1282 l_primary_qty NUMBER;
1283 l_table_name po_interface_errors.table_name%TYPE;
1284 BEGIN
1285 /* We need to do this only for returns and -ve corrections.
1286 * So return if it is not any of this transaction type.
1287 */
1288 IF ( (p_destination_type_code = 'INVENTORY')
1289 AND (p_transaction_type NOT IN('RETURN TO RECEIVING', 'RETURN TO VENDOR', 'RETURN TO CUSTOMER', 'CORRECT'))) THEN --{
1290 RETURN;
1291 END IF; --}
1292
1293 /* We should return if it is a +ve correction. */
1294 IF ( p_transaction_type = 'CORRECT'
1295 AND p_quantity > 0) THEN
1296 RETURN;
1297 END IF;
1298
1299 l_interface_id := p_interface_transaction_id;
1300
1301 SELECT NVL(mp.negative_inv_receipt_code, -999)
1302 INTO l_negative_inv_receipt_code
1303 FROM mtl_system_items msi,
1304 mtl_parameters mp
1305 WHERE mp.organization_id = p_to_organization_id
1306 AND msi.organization_id = mp.organization_id
1307 AND msi.inventory_item_id = p_item_id;
1308
1309 IF (l_negative_inv_receipt_code = -999) THEN
1310 SELECT negative_inv_receipt_code
1311 INTO l_negative_inv_receipt_code
1312 FROM mtl_parameters
1313 WHERE organization_id = p_to_organization_id;
1314 END IF;
1315
1316 l_tree_mode := inv_quantity_tree_pub.g_transaction_mode;
1317
1318 SELECT msi.revision_qty_control_code,
1319 msi.lot_control_code,
1320 msi.serial_number_control_code
1321 INTO l_revision_qty_control_code,
1322 l_lot_control_code,
1323 l_serial_number_control_code
1324 FROM mtl_system_items msi
1325 WHERE inventory_item_id = p_item_id
1326 AND NVL(msi.organization_id, p_to_organization_id) = p_to_organization_id;
1327
1328 IF l_revision_qty_control_code <> '2' THEN
1329 l_is_revision_control := FALSE;
1330 ELSE
1331 l_is_revision_control := TRUE;
1332 l_revision := p_item_revision;
1333 END IF;
1334
1335 IF NVL(l_lot_control_code, 0) = 2 THEN
1336 l_is_lot_control := TRUE;
1337 END IF;
1338
1339 IF (NVL(l_serial_number_control_code, 1) <> 1) THEN
1340 l_is_serial_control := TRUE;
1341 END IF;
1342
1343 /* WE can return only a PO or RMA */
1344 IF (p_receipt_source_code = 'VENDOR') THEN
1345 l_demand_source_type_id := 1;
1346 l_demand_source_header_id := p_po_header_id;
1347 ELSE
1348 l_demand_source_type_id := -9999;
1349 l_demand_source_header_id := -9999;
1350 END IF;
1351
1352 IF (l_negative_inv_receipt_code = 2) THEN --{
1353 IF (l_is_lot_control) THEN
1354 OPEN lot_rows(l_interface_id);
1355 ELSE /* Serial control or not a lot/serial control */
1356 OPEN rti_rows(l_interface_id);
1357 END IF;
1358
1359 LOOP --{
1360 /* Get the primary quantity.*/
1361 IF (lot_rows%ISOPEN) THEN --{
1362 FETCH lot_rows INTO l_lot;
1363 EXIT WHEN lot_rows%NOTFOUND;
1364
1365 IF (g_asn_debug = 'Y') THEN
1366 asn_debug.put_line('lot_number ' || l_lot.lot_number);
1367 END IF;
1368
1369 l_lot_number := l_lot.lot_number;
1370 l_primary_qty := ABS(rcv_transactions_interface_sv.convert_into_correct_qty(l_lot.transaction_quantity,
1371 p_unit_of_measure,
1372 p_item_id,
1373 p_primary_unit_of_measure
1374 ));
1375 ELSIF(rti_rows%ISOPEN) THEN --}{
1376 FETCH rti_rows INTO l_rti;
1377 EXIT WHEN rti_rows%NOTFOUND;
1378 l_lot_number := NULL;
1379 l_primary_qty := ABS(rcv_transactions_interface_sv.convert_into_correct_qty(l_rti.quantity,
1380 p_unit_of_measure,
1381 p_item_id,
1382 p_primary_unit_of_measure
1383 ));
1384 END IF; --}
1385
1386 inv_quantity_tree_pub.query_quantities(p_api_version_number => 1.0,
1387 p_init_msg_lst => fnd_api.g_false,
1388 x_return_status => l_return_status,
1389 x_msg_count => l_msg_count,
1390 x_msg_data => l_msg_data,
1391 p_organization_id => p_to_organization_id,
1392 p_inventory_item_id => p_item_id,
1393 p_tree_mode => l_tree_mode,
1394 p_is_revision_control => l_is_revision_control,
1395 p_is_lot_control => l_is_lot_control,
1396 p_is_serial_control => l_is_serial_control,
1397 p_demand_source_type_id => l_demand_source_type_id,
1398 p_demand_source_header_id => l_demand_source_header_id,
1399 p_demand_source_line_id => NULL,
1400 p_demand_source_name => NULL,
1401 p_lot_expiration_date => NULL,
1402 p_revision => p_item_revision,
1403 p_lot_number => l_lot_number,
1404 p_subinventory_code => p_subinventory,
1405 p_locator_id => p_locator_id,
1406 p_onhand_source => 3,
1407 x_qoh => l_qoh,
1408 x_rqoh => l_rqoh,
1409 x_qr => l_qr,
1410 x_qs => l_qs,
1411 x_att => l_att,
1412 x_atr => l_atr,
1413 p_transfer_subinventory_code => NULL,
1414 p_cost_group_id => NULL,
1415 p_lpn_id => p_transfer_lpn_id,
1416 p_transfer_locator_id => NULL
1417 );
1418
1419 IF (lot_rows%ISOPEN) THEN
1420 l_table_name := 'MTL_TRANSACTION_LOTS_INTERFACE';
1421 ELSE
1422 l_table_name := 'RCV_TRANSACTIONS_INTERFACE';
1423 END IF;
1424
1425 IF (l_return_status = 'S') THEN --{
1426 x_error_status := 'S';
1427
1428 IF (l_primary_qty > NVL(l_att, 0)) THEN --{
1429 x_error_status := 'E';
1430 x_error_message := 'RCV_TRX_QTY_EXCEEDS_INV_AVAIL';
1431 rcv_error_pkg.set_error_message(x_error_message);
1432 rcv_error_pkg.set_token('PRIMARY', l_primary_qty);
1433 rcv_error_pkg.set_token('SUB', l_att);
1434 rcv_error_pkg.log_interface_error('PRIMARY_QUANTITY', FALSE);
1435 EXIT;
1436 ELSE -- }{
1437 x_error_status := 'S';
1438 END IF; --}
1439 ELSE --}{
1440 x_error_status := 'E';
1441 x_error_message := 'RCV_TRX_QTY_EXCEEDS_INV_AVAIL';
1442 rcv_error_pkg.set_error_message(x_error_message);
1443 rcv_error_pkg.set_token('PRIMARY', l_primary_qty);
1444 rcv_error_pkg.set_token('SUB', l_att);
1445 rcv_error_pkg.log_interface_error('PRIMARY_QUANTITY', FALSE);
1446 EXIT;
1447 END IF; --}
1448
1449 IF (p_transaction_type = 'CORRECT') THEN --{
1450 IF (SIGN(p_quantity) = 1) THEN
1451 l_update_quantity := l_primary_qty;
1452 ELSIF(SIGN(p_quantity) = -1) THEN
1453 l_update_quantity := -l_primary_qty;
1454 END IF;
1455 ELSE --}{
1456 l_update_quantity := -l_primary_qty;
1457 END IF; --}
1458
1459 inv_quantity_tree_pub.update_quantities(p_api_version_number => 1.0,
1460 p_init_msg_lst => fnd_api.g_false,
1461 x_return_status => l_return_status,
1462 x_msg_count => l_msg_count,
1463 x_msg_data => l_msg_data,
1464 p_organization_id => p_to_organization_id,
1465 p_inventory_item_id => p_item_id,
1466 p_tree_mode => l_tree_mode,
1467 p_is_revision_control => l_is_revision_control,
1468 p_is_lot_control => l_is_lot_control,
1469 p_is_serial_control => l_is_serial_control,
1470 p_demand_source_type_id => l_demand_source_type_id,
1471 p_demand_source_header_id => l_demand_source_header_id,
1472 p_revision => l_revision,
1473 p_lot_number => l_lot_number,
1474 p_subinventory_code => p_subinventory,
1475 p_locator_id => p_locator_id,
1476 p_primary_quantity => l_update_quantity,
1477 p_quantity_type => inv_quantity_tree_pub.g_qoh,
1478 x_qoh => l_qoh,
1479 x_rqoh => l_rqoh,
1480 x_qr => l_qr,
1481 x_qs => l_qs,
1482 x_att => l_att,
1483 x_atr => l_atr,
1484 p_lpn_id => p_lpn_id
1485 );
1486 END LOOP; --}
1487
1488 IF (lot_rows%ISOPEN) THEN
1489 CLOSE lot_rows;
1490 ELSIF(rti_rows%ISOPEN) THEN
1491 CLOSE rti_rows;
1492 END IF;
1493 END IF; --}
1494 END derive_inv_qty_1;
1495 /* WMS Changes End */
1496 END rcv_roi_return;
[Home] [Help]