1 PACKAGE BODY rcv_int_org_transfer AS
2 /* $Header: RCVIOTFB.pls 120.6.12000000.3 2007/08/15 19:42:41 vthevark ship $*/
3 g_asn_debug VARCHAR2(1) := NVL(fnd_profile.VALUE('RCV_DEBUG_MODE'), 'N');
4 x_progress VARCHAR2(3);
5 e_validation_error EXCEPTION;
6 PROCEDURE derive_int_org_rcv_line(
7 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
8 n IN OUT NOCOPY BINARY_INTEGER,
9 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
10 x_header_record IN rcv_roi_preprocessor.header_rec_type
11 ) IS
12 BEGIN
13 x_progress := '000';
14
15 IF (g_asn_debug = 'Y') THEN
16 asn_debug.put_line('enter int_org_rcv line');
17 END IF;
18
19 -- 1) derive ship to org info
20 rcv_roi_transaction.derive_ship_to_org_info(x_cascaded_table,
21 n,
22 x_header_record
23 );
24 x_progress := '002';
25
26 IF (g_asn_debug = 'Y') THEN
27 asn_debug.put_line('x_progress ' || x_progress);
28 END IF;
29
30 x_progress := '010';
31 -- 5) derive item info
32 rcv_roi_transaction.derive_item_info(x_cascaded_table, n);
33 x_progress := '015';
34 rcv_roi_header_common.derive_uom_info(x_cascaded_table, n);
35 -- 6) derive substitute item info
36 rcv_roi_transaction.derive_substitute_item_info(x_cascaded_table, n);
37 x_progress := '020';
38 -- 8) derive from org info
39 rcv_roi_transaction.derive_from_org_info(x_cascaded_table, n);
40 x_progress := '035';
41 -- 12) derive routing header info
42 rcv_roi_transaction.derive_routing_header_info(x_cascaded_table, n);
43 x_progress := '070';
44 asn_debug.put_line('progress in IOrcv : x_progress = ' || x_progress);
45 -- derive auto transact code
46 rcv_roi_transaction.derive_auto_transact_code(x_cascaded_table, n);
47 asn_debug.put_line('progress in IOrcv : before derive qty');
48 -- quantity > 0
49 derive_int_org_rcv_line_qty(x_cascaded_table,
50 n,
51 temp_cascaded_table
52 );
53 END derive_int_org_rcv_line;
54
55 PROCEDURE derive_int_org_rcv_line_qty(
56 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
57 n IN OUT NOCOPY BINARY_INTEGER,
58 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type
59 ) IS
60 -- x_include_closed_po varchar2(1); -- bug 1887728
61
62 CURSOR shipments(
63 v_shipment_header_id NUMBER,
64 -- v_shipment_num VARCHAR2, --Bugfix 5201151
65 v_document_line_num NUMBER,
66 v_item_id NUMBER,
67 v_ship_to_org_id NUMBER,
68 v_ship_from_org_id NUMBER
69 ) IS
70 SELECT rsh.shipment_header_id shipment_header_id,
71 rsh.shipment_num shipment_num,
72 rsl.shipment_line_id shipment_line_id,
73 rsl.item_id item_id,
74 rsl.item_description item_description,
75 rsl.to_organization_id to_organization_id,
76 rsl.from_organization_id from_organization_id,
77 rsl.routing_header_id routing_header_id,
78 rsl.category_id category_id,
79 rsh.currency_code currency_code,
80 rsh.conversion_rate currency_conversion_rate,
81 rsh.conversion_rate_type currency_conversion_type,
82 rsh.conversion_date currency_conversion_date,
83 rsl.to_subinventory to_subinventory,
84 rsl.ship_to_location_id ship_to_location_id,
85 rsl.deliver_to_location_id deliver_to_location_id,
86 rsl.deliver_to_person_id deliver_to_person_id,
87 rsl.ussgl_transaction_code ussgl_transaction_code,
88 rsl.destination_type_code destination_type_code,
89 rsl.destination_context destination_context,
90 rsl.unit_of_measure unit_of_measure,
91 rsl.primary_unit_of_measure primary_unit_of_measure
92 FROM rcv_shipment_headers rsh,
93 rcv_shipment_lines rsl
94 -- Following 2 lines are commented out for Bugfix 5201151
95 -- WHERE rsh.shipment_header_id = NVL(v_shipment_header_id, rsh.shipment_header_id)
96 -- AND NVL(rsh.shipment_num, '0') = NVL(v_shipment_num, NVL(rsh.shipment_num, '0'))
97 WHERE rsh.shipment_header_id = v_shipment_header_id -- Bugfix 5201151
98 AND rsl.shipment_header_id = rsh.shipment_header_id
99 AND NVL(rsl.item_id, 0) = NVL(v_item_id, NVL(rsl.item_id, 0))
100 AND rsl.line_num = NVL(v_document_line_num, rsl.line_num)
101 AND rsl.to_organization_id = NVL(v_ship_to_org_id, rsl.to_organization_id)
102 AND rsl.from_organization_id = NVL(v_ship_from_org_id, rsl.from_organization_id)
103 AND (NVL(rsl.shipment_line_status_code, 'EXPECTED') <> 'FULLY RECEIVED')
104 AND rsh.receipt_source_code = 'INVENTORY';
105
106 CURSOR count_shipments(
107 v_shipment_header_id NUMBER,
108 -- v_shipment_num VARCHAR2, -- Bugfix 5201151
109 v_document_line_num VARCHAR,
110 v_item_id NUMBER,
111 v_ship_to_org_id NUMBER,
112 v_ship_from_org_id NUMBER
113 ) IS
114 SELECT COUNT(*) AS line_count
115 FROM rcv_shipment_headers rsh,
116 rcv_shipment_lines rsl
117 -- Following 2 lines are commented out for Bugfix 5201151
118 -- WHERE rsh.shipment_header_id = NVL(v_shipment_header_id, rsh.shipment_header_id)
119 -- AND NVL(rsh.shipment_num, '0') = NVL(v_shipment_num, NVL(rsh.shipment_num, '0'))
120 WHERE rsh.shipment_header_id = v_shipment_header_id -- Bugfix 5201151
121 AND rsl.shipment_header_id = rsh.shipment_header_id
122 AND NVL(rsl.item_id, 0) = NVL(v_item_id, NVL(rsl.item_id, 0))
123 AND rsl.line_num = NVL(v_document_line_num, rsl.line_num)
124 AND rsl.to_organization_id = NVL(v_ship_to_org_id, rsl.to_organization_id)
125 AND rsl.from_organization_id = NVL(v_ship_from_org_id, rsl.from_organization_id)
126 AND (NVL(rsl.shipment_line_status_code, 'EXPECTED') <> 'FULLY RECEIVED')
127 AND rsh.receipt_source_code = 'INVENTORY';
128
129 x_shipmentrec shipments%ROWTYPE;
130 x_countshipmentrec count_shipments%ROWTYPE;
131 x_record_count NUMBER := 0;
132 x_remaining_quantity NUMBER := 0;
133 x_remaining_qty_po_uom NUMBER := 0;
134 x_progress VARCHAR2(3);
135 x_to_organization_code VARCHAR2(5);
136 x_converted_trx_qty NUMBER := 0;
137 transaction_ok BOOLEAN := FALSE;
138 rows_fetched NUMBER := 0;
139 x_tolerable_qty NUMBER := 0;
140 x_first_trans BOOLEAN := TRUE;
141 x_sysdate DATE := SYSDATE;
142 current_n BINARY_INTEGER := 0;
143 insert_into_table BOOLEAN := FALSE;
144 x_qty_rcv_exception_code po_line_locations.qty_rcv_exception_code%TYPE;
145 tax_amount_factor NUMBER;
146 x_temp_already_allocated_qty NUMBER;
147 x_remaining_qty_rsl_uom NUMBER;
148 lastrecord BOOLEAN := FALSE;
149 already_allocated_qty NUMBER := 0;
150 x_item_id NUMBER;
151 x_ship_to_organization_id NUMBER;
152 x_sob_id NUMBER := NULL;
153 x_secondary_available_qty NUMBER := 0;
154 x_full_name VARCHAR2(240) := NULL; -- Bug 2392074
155 l_shipment_header_id rcv_shipment_headers.shipment_header_id%TYPE; -- Bugfix 5201151
156 BEGIN
157 --check line quanity > 0
158 x_progress := '097';
159
160 IF (g_asn_debug = 'Y') THEN
161 asn_debug.put_line('inside line qty calculation of int org rcv');
162 asn_debug.put_line('x_progress ' || x_progress);
163 END IF;
164
165 IF x_cascaded_table(n).error_status NOT IN('S', 'W') THEN
166 RETURN;
167 END IF;
168
169 IF x_cascaded_table(n).quantity <= 0 THEN --{
170 IF (g_asn_debug = 'Y') THEN
171 asn_debug.put_line('quantity is <= zero. cascade will fail');
172 END IF;
173
174 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
175 rcv_error_pkg.set_error_message('RCV_ITEM_NO_SHIP_QTY', x_cascaded_table(n).error_message);
176 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).item_num);
177 rcv_error_pkg.log_interface_error('QUANTITY');
178 END IF; --} end qty > 0 check
179
180 x_progress := '098';
181
182 IF (g_asn_debug = 'Y') THEN
183 asn_debug.put_line('x_progress ' || x_progress);
184 asn_debug.put_line('the shipment info is = ' || TO_CHAR(x_cascaded_table(n).shipment_header_id) || ' num = ' || x_cascaded_table(n).shipment_num);
185 END IF;
186
187 -- as long as shipment num or shipment header id is specified we can continue
188 IF ( x_cascaded_table(n).shipment_header_id IS NULL
189 AND x_cascaded_table(n).shipment_num IS NULL) THEN --{
190 IF (g_asn_debug = 'Y') THEN
191 asn_debug.put_line('no shipment num/shipment header specified ');
192 END IF;
193
194 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
195 rcv_error_pkg.set_error_message('RCV_IOT_NO_SHIP_INFO', x_cascaded_table(n).error_message);
196 rcv_error_pkg.set_token('SHIPMENT_NUM', x_cascaded_table(n).shipment_num);
197 rcv_error_pkg.log_interface_error('SHIPMENT_NUM');
198 END IF; -- } of (asn quantity_shipped was valid)
199
200
201 -- copy record from main table to temp table
202
203 IF (g_asn_debug = 'Y') THEN
204 asn_debug.put_line('copy record from main table to temp table');
205 END IF;
206
207 current_n := 1;
208 temp_cascaded_table(current_n) := x_cascaded_table(n);
209
210 -- Bugfix 5201151
211 IF ( x_cascaded_table(n).shipment_header_id IS NULL
212 AND x_cascaded_table(n).shipment_num IS NOT NULL) THEN --{
213 IF (g_asn_debug = 'Y') THEN
214 asn_debug.put_line('Shipment header is not provided hence deriving shipment header id for shipment num ' || x_cascaded_table(n).shipment_num );
215 END IF;
216
217 /* Bug:6313315
218 Added where clause condition rsh.receipt_source_code = 'INVENTORY'.
219 As we can have same shipment number for ISO shipment and Inter org shipment,
220 we need to filter the shipment record by receipt_source_code.
221 */
222 BEGIN
223 SELECT distinct rsh.shipment_header_id
224 INTO l_shipment_header_id
225 FROM rcv_shipment_headers rsh,
226 rcv_shipment_lines rsl
227 WHERE shipment_num = temp_cascaded_table(current_n).shipment_num
228 AND rsh.shipment_header_id = rsl.shipment_header_id
229 AND rsl.to_organization_id = NVL(temp_cascaded_table(current_n).to_organization_id, to_organization_id)
230 AND rsl.from_organization_id = NVL(temp_cascaded_table(current_n).from_organization_id, from_organization_id)
231 AND rsh.receipt_source_code = 'INVENTORY';--Bug: 6313315
232
233 IF (g_asn_debug = 'Y') THEN
234 asn_debug.put_line('Shipment header = ' || l_shipment_header_id );
235 END IF;
236
237 EXCEPTION
238 WHEN NO_DATA_FOUND
239 THEN
240 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
241 rcv_error_pkg.set_error_message('RCV_IOT_NO_SHIP_INFO', x_cascaded_table(n).error_message);
242 rcv_error_pkg.set_token('SHIPMENT_NUM', temp_cascaded_table(current_n).shipment_num);
243 rcv_error_pkg.log_interface_error('SHIPMENT_NUM');
244
245 IF (g_asn_debug = 'Y') THEN
246 asn_debug.put_line(TO_CHAR(n));
247 asn_debug.put_line('No shipment_header_id found for shipment_num = ' || temp_cascaded_table(current_n).shipment_num );
248 asn_debug.put_line('error ' || x_progress);
249 END IF;
250 WHEN OTHERS
251 THEN
252 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_unexp_error;
253 rcv_error_pkg.set_sql_error_message('derive_int_org_rcv_line_qty', x_progress);
254 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
255 rcv_error_pkg.log_interface_error('INTERFACE_TRANSACTION_ID');
256
257 IF (g_asn_debug = 'Y') THEN
258 asn_debug.put_line(TO_CHAR(n));
259 asn_debug.put_line('Error while selecting shipment_header_id for shipment_num = ' || temp_cascaded_table(current_n).shipment_num );
260 asn_debug.put_line(sqlerrm);
261 asn_debug.put_line('error ' || x_progress);
262 END IF;
263 END;
264 ELSE
265 l_shipment_header_id := temp_cascaded_table(current_n).shipment_header_id;
266 END IF; -- } deriving shipment_header_id if it NULL from shipment_num
267 -- End of code for Bugfix 5201151
268
269 -- get all rows which meet this condition
270 IF (g_asn_debug = 'Y') THEN
271 asn_debug.put_line('get all rows which meet this condition');
272 asn_debug.put_line('transaction type = ' || x_cascaded_table(n).transaction_type);
273 asn_debug.put_line('auto transact code = ' || x_cascaded_table(n).auto_transact_code);
274 END IF;
275
276 --{ open the cursors
277 IF (g_asn_debug = 'Y') THEN
278 asn_debug.put_line('open shipment records');
279 asn_debug.put_line('shipment header id ' || TO_CHAR(temp_cascaded_table(current_n).shipment_header_id));
280 asn_debug.put_line('item id ' || TO_CHAR(temp_cascaded_table(current_n).item_id));
281 asn_debug.put_line('shipment line num ' || TO_CHAR(temp_cascaded_table(current_n).document_line_num));
282 asn_debug.put_line('ship to organization id ' || TO_CHAR(temp_cascaded_table(current_n).to_organization_id));
283 asn_debug.put_line('from org id ' || TO_CHAR(temp_cascaded_table(current_n).from_organization_id));
284 asn_debug.put_line('proceed to open cursor');
285 END IF;
286
287 OPEN shipments(-- temp_cascaded_table(current_n).shipment_header_id, -- Bugfix 5201151
288 -- temp_cascaded_table(current_n).shipment_num, -- Bugfix 5201151
289 l_shipment_header_id, -- Bugfix 5201151
290 temp_cascaded_table(current_n).document_line_num,
291 temp_cascaded_table(current_n).item_id,
292 temp_cascaded_table(current_n).to_organization_id,
293 temp_cascaded_table(current_n).from_organization_id
294 );
295 -- count_shipments just gets the count of rows found in shipments
296
297 OPEN count_shipments(-- temp_cascaded_table(current_n).shipment_header_id, -- Bugfix 5201151
298 -- temp_cascaded_table(current_n).shipment_num, -- Bugfix 5201151
299 l_shipment_header_id, -- Bugfix 5201151
300 temp_cascaded_table(current_n).document_line_num,
301 temp_cascaded_table(current_n).item_id,
302 temp_cascaded_table(current_n).to_organization_id,
303 temp_cascaded_table(current_n).from_organization_id
304 );
305
306 -- }
307
308 -- assign shipped quantity to remaining quantity
309 IF (g_asn_debug = 'Y') THEN
310 asn_debug.put_line('assign shipped quantity to remaining quantity');
311 asn_debug.put_line('pointer in temp_cascade ' || TO_CHAR(current_n));
312 END IF;
313
314 x_remaining_quantity := temp_cascaded_table(current_n).quantity;
315 x_remaining_qty_po_uom := 0;
316
317 IF (g_asn_debug = 'Y') THEN
318 asn_debug.put_line('have assigned the quantity');
319 END IF;
320
321 -- calculate tax_amount_factor for calculating tax_amount for
322 -- each cascaded line
323
324 IF NVL(temp_cascaded_table(current_n).tax_amount, 0) <> 0 THEN
325 tax_amount_factor := temp_cascaded_table(current_n).tax_amount / x_remaining_quantity;
326 ELSE
327 tax_amount_factor := 0;
328 END IF;
329
330 IF (g_asn_debug = 'Y') THEN
331 asn_debug.put_line('tax factor ' || TO_CHAR(tax_amount_factor));
332 asn_debug.put_line('shipped quantity : ' || TO_CHAR(x_remaining_quantity));
333 END IF;
334
335 x_first_trans := TRUE;
336 transaction_ok := FALSE;
337 /*
338 ** get the count of the number of records depending on the
339 */
340 FETCH count_shipments INTO x_countshipmentrec;
341 x_record_count := x_countshipmentrec.line_count;
342
343 IF (g_asn_debug = 'Y') THEN
344 asn_debug.put_line('before starting cascade');
345 END IF;
346
347 IF (g_asn_debug = 'Y') THEN
348 asn_debug.put_line('record count = ' || x_record_count);
349 END IF;
350
351 LOOP --{ over the count of shipment records obtained
352 IF (g_asn_debug = 'Y') THEN
353 asn_debug.put_line('remaining quantity asn uom ' || TO_CHAR(x_remaining_quantity));
354 END IF;
355
356 IF (g_asn_debug = 'Y') THEN
357 asn_debug.put_line('open shipments and fetch');
358 END IF;
359
360 /*
361 ** fetch the cursor
362 */
363 --{
364 IF (g_asn_debug = 'Y') THEN
365 asn_debug.put_line('fetching shipments cursor');
366 END IF;
367
368 FETCH shipments INTO x_shipmentrec;
369
370 /*
371 ** check if this is the last record
372 */
373 IF (shipments%NOTFOUND) THEN
374 lastrecord := TRUE;
375 END IF;
376
377 rows_fetched := shipments%ROWCOUNT;
378
379 IF (g_asn_debug = 'Y') THEN
380 asn_debug.put_line('shipment rows fetched ' || TO_CHAR(rows_fetched));
381 END IF;
382
383 -- }
384
385
386 IF ( lastrecord
387 OR x_remaining_quantity <= 0) THEN --{
388 IF (g_asn_debug = 'Y') THEN
389 asn_debug.put_line('hit exit condition');
390 END IF;
391
392 IF NOT x_first_trans THEN
393 -- x_first_trans has been reset which means some cascade has
394 -- happened. otherwise current_n = 1
395 current_n := current_n - 1;
396 END IF;
397
398 -- do the tolerance act here
399 IF (g_asn_debug = 'Y') THEN
400 asn_debug.put_line('temp table pointer ' || TO_CHAR(current_n));
401 asn_debug.put_line('check which condition has occured');
402 END IF;
403
404 -- lastrecord...we have run out of rows and we still have quantity to allocate
405 IF x_remaining_quantity > 0 THEN --{
406 IF (g_asn_debug = 'Y') THEN
407 asn_debug.put_line('There is quantity remaining');
408 asn_debug.put_line('Need to check qty tolerances');
409 END IF;
410
411 IF rows_fetched > 0
412 AND NOT x_first_trans THEN --{
413 IF (g_asn_debug = 'Y') THEN
414 asn_debug.put_line(' in inter org transfer rcv Extra Quantity ' || TO_CHAR(x_remaining_quantity));
415 END IF;
416
417 IF (g_asn_debug = 'Y') THEN
418 asn_debug.put_line('delete the temp table ');
419 END IF;
420
421 IF temp_cascaded_table.COUNT > 0 THEN
422 FOR i IN 1 .. temp_cascaded_table.COUNT LOOP
423 temp_cascaded_table.DELETE(i);
424 END LOOP;
425 END IF;
426
427 IF (g_asn_debug = 'Y') THEN
428 asn_debug.put_line('mark the actual table with error status');
429 asn_debug.put_line('Error Status ' || x_cascaded_table(n).error_status);
430 asn_debug.put_line('Error message ' || x_cascaded_table(n).error_message);
431 END IF;
432
433 IF (g_asn_debug = 'Y') THEN
434 asn_debug.put_line('Need to insert a row into po_interface_errors for transfer');
435 END IF;
436
437 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
438 rcv_error_pkg.set_error_message('RCV_SHIP_QTY_OVER_TOLERANCE', x_cascaded_table(n).error_message);
439 rcv_error_pkg.set_token('QTY_A', x_cascaded_table(n).quantity);
440 rcv_error_pkg.set_token('QTY_B', x_cascaded_table(n).quantity - x_remaining_quantity);
441 rcv_error_pkg.log_interface_error('QUANTITY');
442 ELSE --}{ else for rows fetched = 0 OR x_first_trans = true
443 IF rows_fetched = 0 THEN
444 IF (g_asn_debug = 'Y') THEN
445 asn_debug.put_line('No rows were retrieved from cursor.');
446 END IF;
447 ELSIF x_first_trans THEN
448 IF (g_asn_debug = 'Y') THEN
449 asn_debug.put_line('No rows were cascaded');
450 END IF;
451 END IF;
452
453 -- 1) should we check to see why no rows were fetched ??
454 --2) should we error out the row in rti if another check proved
455 -- there are rows in rsl for this rti row
456
457 -- Delete the temp_cascaded_table just to be sure
458 IF temp_cascaded_table.COUNT > 0 THEN
459 FOR i IN 1 .. temp_cascaded_table.COUNT LOOP
460 temp_cascaded_table.DELETE(i);
461 END LOOP;
462 END IF;
463
464 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
465 rcv_error_pkg.set_error_message('PO_CHNG_WRONG_DOC_TYPE', x_cascaded_table(n).error_message);
466 rcv_error_pkg.log_interface_error('PARENT_TRANSACTION_ID');
467 END IF; --} ends row fetched > 0 and not first transaction
468
469 -- all the rows in the temp cascaded table
470 -- will be deleted
471 -- as we cannot over/under receive against a inter-org transfer receive
472 ELSE -- }{
473 IF (g_asn_debug = 'Y') THEN
474 asn_debug.put_line('Remaining UOM quantity is zero ' || TO_CHAR(x_remaining_quantity));
475 asn_debug.put_line('Return the cascaded rows back to the calling procedure');
476 END IF;
477 END IF; --} ends the check for whether last record has been reached
478
479 -- close cursors
480
481 IF (g_asn_debug = 'Y') THEN
482 asn_debug.put_line('close cursors shipments, count_shipments');
483 END IF;
484
485 IF shipments%ISOPEN THEN
486 CLOSE shipments;
487 END IF;
488
489 IF count_shipments%ISOPEN THEN
490 CLOSE count_shipments;
491 END IF;
492
493 EXIT;
494 END IF; --} matches lastrecord or x_remaining_quantity <= 0
495
496 -- eliminate the row if it fails the date check
497
498 IF (g_asn_debug = 'Y') THEN
499 asn_debug.put_line('count in temp_cascade_table : ' || TO_CHAR(temp_cascaded_table.COUNT));
500 asn_debug.put_line('cursor record ' || TO_CHAR(rows_fetched));
501 asn_debug.put_line('int org rcv : calling get available qty');
502 END IF;
503
504 -- removed rcv_transactions_interface_sv.check_date_tolerance;
505 -- removed check shipto_location enforcement
506 -- removed check receipt days exception code
507
508 --{
509 --matches shipmentdistributionrec.receipt_days_exception_code = none
510 -- we will do it for the first record only. subsequent records in the
514 temp_cascaded_table(current_n).item_id := x_shipmentrec.item_id;
511 -- temp_table are copies of the previous one
512 IF (x_first_trans)
513 AND temp_cascaded_table(current_n).item_id IS NULL THEN --{
515 temp_cascaded_table(current_n).primary_unit_of_measure := x_shipmentrec.primary_unit_of_measure;
516 END IF; --}
517
518 insert_into_table := FALSE;
519 already_allocated_qty := 0;
520
521 /*
522 ** get the available quantity for the shipment line (rsl)
523 ** that is available for allocation by this interface transaction
524 ** the available qty can only be found from rsl
525 ** the else condition should never arise : confirm from priya ??
526 */
527 IF (g_asn_debug = 'Y') THEN
528 asn_debug.put_line('shipment line id : ' || TO_CHAR(x_shipmentrec.shipment_line_id));
529 asn_debug.put_line('uom ' || x_shipmentrec.unit_of_measure);
530 asn_debug.put_line('converted trx qty : ' || TO_CHAR(x_converted_trx_qty));
531 asn_debug.put_line('tolerable qty : ' || TO_CHAR(x_tolerable_qty));
532 asn_debug.put_line('receipt source code' || x_cascaded_table(n).receipt_source_code);
533 END IF;
534
535 IF ( x_cascaded_table(n).transaction_type = 'RECEIVE'
536 AND NVL(x_cascaded_table(n).auto_transact_code, 'RECEIVE') IN('RECEIVE', 'DELIVER')) THEN --{
537 /*bug# 1548597 */
538 rcv_quantities_s.get_available_quantity('RECEIVE',
539 x_shipmentrec.shipment_line_id,
540 x_cascaded_table(n).receipt_source_code, -- ?? specifying the rti receipt source code for now
541 NULL,
542 NULL,
543 NULL,
544 x_converted_trx_qty,
545 x_tolerable_qty,
546 x_shipmentrec.unit_of_measure, -- this is rsl.unit_of_measure
547 x_secondary_available_qty
548 );
549
550 -- if qtys have already been allocated for this item during
551 -- a cascade process which has not been written to the db yet, we need to
552 -- decrement it from the total available quantity
553 -- we traverse the actual pl/sql table and accumulate the quantity by matching the
554 -- item_id
555
556 IF n > 1 THEN -- we will do this for all rows except the 1st
557 FOR i IN 1 ..(n - 1) LOOP
558 IF x_cascaded_table(i).item_id = x_shipmentrec.item_id THEN
559 x_temp_already_allocated_qty := rcv_roi_transaction.convert_into_correct_qty(x_cascaded_table(i).quantity,
560 x_cascaded_table(i).unit_of_measure,
561 x_cascaded_table(i).item_id,
562 x_shipmentrec.unit_of_measure
563 );
564 already_allocated_qty := already_allocated_qty + x_temp_already_allocated_qty;
565 END IF;
566 END LOOP;
567 END IF;
568 END IF; --}
569
570 IF (g_asn_debug = 'Y') THEN
571 asn_debug.put_line('after call to get_available quantity');
572 asn_debug.put_line('available quantity ' || TO_CHAR(x_converted_trx_qty));
573 asn_debug.put_line('tolerable quantity ' || TO_CHAR(x_tolerable_qty));
574 asn_debug.put_line('pointer to temp table ' || TO_CHAR(current_n));
575 asn_debug.put_line(' Already allocated qty now in terms of shipment rec uom is ' || already_allocated_qty);
576 END IF;
577
578 -- if qty has already been allocated then reduce available and tolerable
579 -- qty by the allocated amount
580
581 IF NVL(already_allocated_qty, 0) > 0 THEN --{
582 x_converted_trx_qty := x_converted_trx_qty - already_allocated_qty;
583 x_tolerable_qty := x_tolerable_qty - already_allocated_qty;
584
585 IF x_converted_trx_qty < 0 THEN
586 x_converted_trx_qty := 0;
587 END IF;
588
589 IF x_tolerable_qty < 0 THEN
590 x_tolerable_qty := 0;
591 END IF;
592
593 IF (g_asn_debug = 'Y') THEN
594 asn_debug.put_line('have some allocated quantity. will reduce qty');
595 asn_debug.put_line('allocated qty ' || TO_CHAR(already_allocated_qty));
596 asn_debug.put_line('after reducing by allocated qty');
597 asn_debug.put_line('available quantity ' || TO_CHAR(x_converted_trx_qty));
598 asn_debug.put_line('tolerable quantity ' || TO_CHAR(x_tolerable_qty));
599 asn_debug.put_line('pointer to temp table ' || TO_CHAR(current_n));
600 END IF;
601 END IF; --}
602
603 -- we can use the first record since the item_id and uom are not going to change
607 -- if any of the conversions fail then we cannot use that record
604 -- check that we can convert between asn-> po uom
605 -- po -> asn uom
606 -- po -> primary uom
608
609 x_remaining_qty_rsl_uom := 0; -- initialize
610 x_remaining_qty_rsl_uom := rcv_roi_transaction.convert_into_correct_qty(x_remaining_quantity,
611 temp_cascaded_table(1).unit_of_measure,
612 temp_cascaded_table(1).item_id,
613 x_shipmentrec.unit_of_measure
614 );
615
616 IF x_remaining_qty_rsl_uom = 0 THEN --{ -- no point continuing
617 IF (g_asn_debug = 'Y') THEN
618 asn_debug.put_line('need an error message in the interface tables');
619 asn_debug.put_line('cannot interconvert between diff uoms');
620 END IF;
621 ELSE -- we have converted the qty between uoms succesfully } {
622 IF (g_asn_debug = 'Y') THEN
623 asn_debug.put_line('current item id ' || TO_CHAR(temp_cascaded_table(1).item_id));
624 asn_debug.put_line('current asn quantity ' || TO_CHAR(x_remaining_quantity));
625 asn_debug.put_line('current asn uom ' || temp_cascaded_table(1).unit_of_measure);
626 asn_debug.put_line('converted rsl uom quantity ' || TO_CHAR(x_remaining_qty_rsl_uom));
627 END IF;
628
629 IF x_converted_trx_qty > 0 THEN --{
630 IF (x_converted_trx_qty < x_remaining_qty_rsl_uom) THEN -- compare like uoms {
631 IF (g_asn_debug = 'Y') THEN
632 asn_debug.put_line('total qty available to be received is less than remaining qty');
633 END IF;
634
635 x_remaining_qty_rsl_uom := x_remaining_qty_rsl_uom - x_converted_trx_qty;
636 -- change rsl uom qty to uom of first line in cascaded table so both qtys are in sync
637 x_remaining_quantity := rcv_roi_transaction.convert_into_correct_qty(x_remaining_qty_rsl_uom,
638 x_shipmentrec.unit_of_measure,
639 temp_cascaded_table(1).item_id,
640 temp_cascaded_table(1).unit_of_measure
641 );
642 insert_into_table := TRUE;
643 ELSE --} {
644 IF (g_asn_debug = 'Y') THEN
645 asn_debug.put_line('total qty available to be received is > remaining qty ');
646 END IF;
647
648 x_converted_trx_qty := x_remaining_qty_rsl_uom;
649 insert_into_table := TRUE;
650 x_remaining_qty_rsl_uom := 0;
651 x_remaining_quantity := 0;
652 END IF; --}
653 ELSE -- no qty for this record but if last row we need it } {
654 IF rows_fetched = x_record_count THEN --{ last row needs to be inserted anyway
655 -- so that the row can be used based on qty tolerance checks
656 IF (g_asn_debug = 'Y') THEN
657 asn_debug.put_line('quantity is less then 0 but last record');
658 END IF;
659
660 insert_into_table := TRUE;
661 x_converted_trx_qty := 0;
662 ELSE --} {
663 IF (g_asn_debug = 'Y') THEN
664 asn_debug.put_line('<= 0 quantity but more records in cursor');
665 END IF;
666
667 x_remaining_qty_po_uom := 0; -- we may have a diff uom on the next iteration
668
669 IF (g_asn_debug = 'Y') THEN
670 asn_debug.put_line('we have to deal with remaining_qty > 0 and x_converted_trx_qty -ve');
671 END IF;
672
673 insert_into_table := FALSE;
674 END IF; --}
675 END IF; --}
676 END IF; --} remaining_qty_po_uom <> 0
677
678 IF insert_into_table THEN --{
679 IF (x_first_trans) THEN --{
680 IF (g_asn_debug = 'Y') THEN
681 asn_debug.put_line('first time ' || TO_CHAR(current_n));
682 END IF;
683
684 x_first_trans := FALSE;
685 ELSE --} { not x_first_trans
686 IF (g_asn_debug = 'Y') THEN
687 asn_debug.put_line('next time ' || TO_CHAR(current_n));
688 END IF;
689
690 temp_cascaded_table(current_n) := temp_cascaded_table(current_n - 1);
691 END IF; --} matches x_first_transfer
692
693 -- source_doc_qty should be in rsl's uom
694
695 temp_cascaded_table(current_n).source_doc_quantity := x_converted_trx_qty; -- in rsl uom
696 temp_cascaded_table(current_n).source_doc_unit_of_measure := x_shipmentrec.unit_of_measure;
697
701 temp_cascaded_table(current_n).item_id,
698 IF (temp_cascaded_table(current_n).unit_of_measure <> x_shipmentrec.unit_of_measure) THEN
699 temp_cascaded_table(current_n).quantity := rcv_roi_transaction.convert_into_correct_qty(x_converted_trx_qty,
700 x_shipmentrec.unit_of_measure,
702 temp_cascaded_table(current_n).unit_of_measure
703 ); -- in asn uom
704 ELSE
705 temp_cascaded_table(current_n).quantity := x_converted_trx_qty;
706 END IF;
707
708 IF (g_asn_debug = 'Y') THEN
709 asn_debug.put_line('Transaction qty in terms of the transaction uom is ' || temp_cascaded_table(current_n).quantity);
710 END IF;
711
712 -- primary qty in primary uom
713 IF (temp_cascaded_table(current_n).primary_unit_of_measure <> x_shipmentrec.unit_of_measure) THEN
714 temp_cascaded_table(current_n).primary_quantity := rcv_roi_transaction.convert_into_correct_qty(x_converted_trx_qty,
715 x_shipmentrec.unit_of_measure,
716 temp_cascaded_table(current_n).item_id,
717 temp_cascaded_table(current_n).primary_unit_of_measure
718 );
719 ELSE
720 temp_cascaded_table(current_n).primary_quantity := x_converted_trx_qty;
721
722 IF (g_asn_debug = 'Y') THEN
723 asn_debug.put_line('Transaction qty in terms of the primary uom is ' || temp_cascaded_table(current_n).primary_quantity);
724 END IF;
725
726 temp_cascaded_table(current_n).inspection_status_code := 'NOT INSPECTED';
727 temp_cascaded_table(current_n).interface_source_code := 'RCV';
728 -- temp_cascaded_table(current_n).currency_code := x_shipmentrec.currency_code;
729 temp_cascaded_table(current_n).tax_amount := ROUND(temp_cascaded_table(current_n).quantity * tax_amount_factor, 4);
730
731 IF (g_asn_debug = 'Y') THEN
732 asn_debug.put_line('current tax amount ' || TO_CHAR(temp_cascaded_table(current_n).tax_amount));
733 END IF;
734
735 -- confirm the data in rsh and rsl for the provided info
736 IF (g_asn_debug = 'Y') THEN
737 asn_debug.put_line( 'rsl : cat '
738 || x_shipmentrec.category_id
739 || ' item desc '
740 || x_shipmentrec.item_description
741 || ' header '
742 || x_shipmentrec.shipment_header_id
743 || ' ship num '
744 || x_shipmentrec.shipment_num
745 || ' line '
746 || x_shipmentrec.shipment_line_id);
747 END IF;
748
749 temp_cascaded_table(current_n).category_id := x_shipmentrec.category_id;
750 temp_cascaded_table(current_n).item_description := x_shipmentrec.item_description;
751
752 IF temp_cascaded_table(current_n).to_organization_id IS NULL THEN --{
753 temp_cascaded_table(current_n).to_organization_id := x_shipmentrec.to_organization_id;
754 END IF; --}
755
756 IF temp_cascaded_table(current_n).from_organization_id IS NULL THEN --{
757 temp_cascaded_table(current_n).from_organization_id := x_shipmentrec.from_organization_id;
758 END IF; --}
759
760 -- set the shipment num/header if
761
762 IF temp_cascaded_table(current_n).shipment_header_id IS NULL
763 OR temp_cascaded_table(current_n).shipment_num IS NULL THEN
764 temp_cascaded_table(current_n).shipment_header_id := x_shipmentrec.shipment_header_id;
765 temp_cascaded_table(current_n).shipment_num := x_shipmentrec.shipment_num;
766 END IF;
767
768 -- set the shipment line id
769 IF temp_cascaded_table(current_n).shipment_line_id IS NULL THEN
770 temp_cascaded_table(current_n).shipment_line_id := x_shipmentrec.shipment_line_id;
771 END IF;
772
773 -- copy the distribution specific information only if this is a direct receipt.
774 IF ( x_cascaded_table(n).transaction_type = 'DELIVER'
775 OR NVL(x_cascaded_table(n).auto_transact_code, 'RECEIVE') = 'DELIVER') THEN --{
776 temp_cascaded_table(current_n).destination_type_code := x_shipmentrec.destination_type_code;
777 temp_cascaded_table(current_n).destination_context := x_shipmentrec.destination_type_code;
778
779 IF (NVL(temp_cascaded_table(current_n).deliver_to_location_id, 0) = 0) THEN
783 /* bug 2392074 - if the deliver_to_person mentioned in the po_distributions is
780 temp_cascaded_table(current_n).deliver_to_location_id := x_shipmentrec.deliver_to_location_id;
781 END IF;
782
784 invalid or inactive at the time of receipt we need to clear the deliver to person,
785 as this is an optional field. */
786 IF (NVL(temp_cascaded_table(current_n).deliver_to_person_id, 0) = 0) THEN --{
787 temp_cascaded_table(current_n).deliver_to_person_id := x_shipmentrec.deliver_to_person_id;
788
789 IF (temp_cascaded_table(current_n).deliver_to_person_id IS NOT NULL) THEN --{
790 BEGIN
791 SELECT NVL(MAX(hre.full_name), 'notfound')
792 INTO x_full_name
793 FROM hr_employees_current_v hre
794 WHERE ( hre.inactive_date IS NULL
795 OR hre.inactive_date > SYSDATE)
796 AND hre.employee_id = temp_cascaded_table(current_n).deliver_to_person_id;
797
798 IF (x_full_name = 'notfound') THEN
799 temp_cascaded_table(current_n).deliver_to_person_id := NULL;
800 END IF;
801 EXCEPTION
802 WHEN NO_DATA_FOUND THEN
803 temp_cascaded_table(current_n).deliver_to_person_id := NULL;
804
805 IF (g_asn_debug = 'Y') THEN
806 asn_debug.put_line('the deliver to person entered in po is currently inactive');
807 asn_debug.put_line(' so it is cleared off');
808 END IF;
809 WHEN OTHERS THEN
810 temp_cascaded_table(current_n).deliver_to_person_id := NULL;
811
812 IF (g_asn_debug = 'Y') THEN
813 asn_debug.put_line('some exception has occured');
814 asn_debug.put_line('this exception is due to the po deliver to person');
815 asn_debug.put_line('the deliver to person is optional');
816 asn_debug.put_line('so cleared off the deliver to person');
817 END IF;
818 END;
819 END IF; --}
820 END IF; --}
821
822 IF (temp_cascaded_table(current_n).subinventory IS NULL) THEN
823 temp_cascaded_table(current_n).subinventory := x_shipmentrec.to_subinventory;
824 END IF;
825
826 -- bug 1361786
827 IF (temp_cascaded_table(current_n).ussgl_transaction_code IS NULL) THEN
828 temp_cascaded_table(current_n).ussgl_transaction_code := x_shipmentrec.ussgl_transaction_code;
829 END IF;
830 END IF; --} matches txn not deliver
831
832 current_n := current_n + 1;
833
834 IF (g_asn_debug = 'Y') THEN
835 asn_debug.put_line('increment pointer by 1 ' || TO_CHAR(current_n));
836 END IF;
837 END IF; --} matches if insert into table
838 END IF; --} matches shipmentdistributionrec.receipt_days_exception_code = none
839 END LOOP; --}
840
841 IF shipments%ISOPEN THEN
842 CLOSE shipments;
843 END IF;
844
845 IF count_shipments%ISOPEN THEN
846 CLOSE count_shipments;
847 END IF;
848
849 IF (g_asn_debug = 'Y') THEN
850 asn_debug.put_line('exit derive_int_org_rcv_line_qty');
851 END IF;
852 EXCEPTION
853 WHEN rcv_error_pkg.e_fatal_error THEN
854 IF shipments%ISOPEN THEN
855 CLOSE shipments;
856 END IF;
857
858 IF count_shipments%ISOPEN THEN
859 CLOSE count_shipments;
860 END IF;
861 WHEN OTHERS THEN
862 IF shipments%ISOPEN THEN
863 CLOSE shipments;
864 END IF;
865
866 IF count_shipments%ISOPEN THEN
867 CLOSE count_shipments;
868 END IF;
869
870 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_unexp_error;
871 rcv_error_pkg.set_sql_error_message('derive_int_org_rcv_line_qty', x_progress);
872 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
873 rcv_error_pkg.log_interface_error('INTERFACE_TRANSACTION_ID');
874
875 IF (g_asn_debug = 'Y') THEN
876 asn_debug.put_line(TO_CHAR(n));
877 asn_debug.put_line(SQLERRM);
878 asn_debug.put_line('error ' || x_progress);
879 END IF;
880 END derive_int_org_rcv_line_qty;
881
882 PROCEDURE default_int_org_rcv_line(
883 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
884 n IN BINARY_INTEGER
885 ) IS
886 x_progress VARCHAR2(3);
887 x_locator_control NUMBER;
888 x_success BOOLEAN;
889 p_trx_record rcv_roi_header_common.common_default_record_type;
890
891 /* bug2382337:
892 * Change the name of the parameters passed into the cursor
893 */
897 ) IS
894 CURSOR shipments(
895 v_shipment_header_id NUMBER,
896 v_shipment_line_id NUMBER
898 SELECT rsh.shipment_header_id,
899 rsh.shipment_num,
900 rsl.shipment_line_id,
901 rsl.item_description,
902 rsl.to_organization_id,
903 rsl.from_organization_id,
904 rsl.routing_header_id,
905 rsl.category_id,
906 rsh.currency_code,
907 rsh.conversion_rate currency_conversion_rate,
908 rsh.conversion_rate_type currency_conversion_type,
909 rsh.conversion_date currency_conversion_date,
910 rsl.to_subinventory,
911 rsl.ship_to_location_id
912 FROM rcv_shipment_headers rsh,
913 rcv_shipment_lines rsl
914 WHERE rsh.shipment_header_id = v_shipment_header_id
915 AND rsh.shipment_header_id = rsl.shipment_header_id
916 AND rsl.shipment_line_id = v_shipment_line_id;
917
918 default_shipment_info shipments%ROWTYPE;
919 -- X_success boolean;
920 x_default_subinventory VARCHAR2(10);
921 x_default_locator_id NUMBER;
922 BEGIN
923 p_trx_record.destination_type_code := x_cascaded_table(n).destination_type_code;
924 p_trx_record.transaction_type := x_cascaded_table(n).transaction_type;
925 p_trx_record.processing_mode_code := x_cascaded_table(n).processing_mode_code;
926 p_trx_record.processing_status_code := x_cascaded_table(n).processing_status_code;
927 p_trx_record.transaction_status_code := x_cascaded_table(n).transaction_status_code;
928 p_trx_record.auto_transact_code := x_cascaded_table(n).auto_transact_code;
929 rcv_roi_header_common.commondefaultcode(p_trx_record);
930 x_cascaded_table(n).destination_type_code := p_trx_record.destination_type_code;
931 x_cascaded_table(n).transaction_type := p_trx_record.transaction_type;
932 x_cascaded_table(n).processing_mode_code := p_trx_record.processing_mode_code;
933 x_cascaded_table(n).processing_status_code := p_trx_record.processing_status_code;
934 x_cascaded_table(n).transaction_status_code := p_trx_record.transaction_status_code;
935 x_cascaded_table(n).auto_transact_code := p_trx_record.auto_transact_code;
936 x_cascaded_table(n).destination_context := x_cascaded_table(n).destination_type_code;
937
938 --open the cursor
939 IF (g_asn_debug = 'Y') THEN
940 asn_debug.put_line('default_int_org_rcv : defaulting called with shipment_header = ' || x_cascaded_table(n).shipment_header_id || ' and shipment_line = ' || x_cascaded_table(n).shipment_line_id);
941 END IF;
942
943 OPEN shipments(x_cascaded_table(n).shipment_header_id, x_cascaded_table(n).shipment_line_id);
944
945 IF (shipments%ISOPEN) THEN
946 FETCH shipments INTO default_shipment_info;
947 END IF;
948
949 IF (shipments%FOUND) THEN --{
950 x_cascaded_table(n).shipment_num := default_shipment_info.shipment_num;
951 x_cascaded_table(n).item_description := default_shipment_info.item_description;
952 x_cascaded_table(n).to_organization_id := default_shipment_info.to_organization_id;
953 x_cascaded_table(n).from_organization_id := default_shipment_info.from_organization_id;
954 x_cascaded_table(n).routing_header_id := default_shipment_info.routing_header_id;
955 x_cascaded_table(n).shipment_header_id := default_shipment_info.shipment_header_id;
956 x_cascaded_table(n).shipment_line_id := default_shipment_info.shipment_line_id;
957 x_cascaded_table(n).category_id := default_shipment_info.category_id;
958 --currency defaulting
959 x_cascaded_table(n).currency_code := default_shipment_info.currency_code;
960 x_cascaded_table(n).currency_conversion_rate := default_shipment_info.currency_conversion_rate;
961 x_cascaded_table(n).currency_conversion_type := default_shipment_info.currency_conversion_type;
962 x_cascaded_table(n).currency_conversion_date := default_shipment_info.currency_conversion_date;
963 --- WMS Changes
964 rcv_roi_transaction.default_to_subloc_info(x_cascaded_table, n);
965
966 -- if deliver transaction
967 -- if location not entered on form => location_id and deliver_to_location_id are null
968 -- if location entered on form => location_id and deliver_to_location_id are the same
969 IF ( NVL(x_cascaded_table(n).auto_transact_code, 'RECEIVE') = 'DELIVER'
970 OR x_cascaded_table(n).transaction_type = 'DELIVER') THEN --{
971 IF ( ( x_cascaded_table(n).location_id IS NOT NULL
972 OR x_cascaded_table(n).deliver_to_location_id IS NOT NULL)
973 AND x_cascaded_table(n).deliver_to_location_id <> x_cascaded_table(n).location_id) THEN
974 IF x_cascaded_table(n).location_id IS NULL THEN
975 x_cascaded_table(n).location_id := x_cascaded_table(n).deliver_to_location_id;
976 ELSE
977 x_cascaded_table(n).deliver_to_location_id := x_cascaded_table(n).location_id;
978 END IF;
979 END IF;
980 END IF; --} matches auto transact code = DELIVER
981
982 -- if receive transaction
986 IF (g_asn_debug = 'Y') THEN
983 -- location has to be entered and the location_id is always = ship_to_location_id : cannot be defaulted
984 -- deliver to location id and deliver to person id do not have to be defaulted
985
987 asn_debug.put_line('should have defaulted the location id to the ship to location id');
988 asn_debug.put_line('ship_to_loc ' || x_cascaded_table(n).ship_to_location_id || ' loc ' || NVL(x_cascaded_table(n).location_id, -1) || ' txn ' || x_cascaded_table(n).transaction_type || ' txn code ' || x_cascaded_table(n).auto_transact_code);
989 END IF;
990
991 IF ( x_cascaded_table(n).transaction_type = 'RECEIVE'
992 AND x_cascaded_table(n).auto_transact_code = 'RECEIVE') THEN --{
993 IF (NVL(NVL(x_cascaded_table(n).location_id, x_cascaded_table(n).ship_to_location_id), -1) <> -1) THEN --{
994 IF x_cascaded_table(n).location_id IS NOT NULL
995 AND x_cascaded_table(n).ship_to_location_id IS NOT NULL THEN --{
996 x_cascaded_table(n).ship_to_location_id := x_cascaded_table(n).location_id;
997 ELSE
998 IF x_cascaded_table(n).location_id IS NULL THEN
999 x_cascaded_table(n).location_id := x_cascaded_table(n).ship_to_location_id;
1000 ELSE
1001 x_cascaded_table(n).ship_to_location_id := x_cascaded_table(n).location_id;
1002 END IF;
1003 END IF; --}
1004 END IF; --}
1005
1006 IF ( x_cascaded_table(n).location_id IS NULL
1007 AND x_cascaded_table(n).ship_to_location_id IS NULL
1008 AND default_shipment_info.ship_to_location_id IS NOT NULL) THEN --{
1009 x_cascaded_table(n).ship_to_location_id := default_shipment_info.ship_to_location_id;
1010 x_cascaded_table(n).location_id := x_cascaded_table(n).ship_to_location_id;
1011 END IF; --}
1012 END IF; --}
1013 END IF; --} matches shipment found
1014
1015 /*
1016 BEGIN Comment: Bug: 4735484
1017
1018 SELECT NVL(x_cascaded_table(n).use_mtl_lot, lot_control_code),
1019 NVL(x_cascaded_table(n).use_mtl_serial, serial_number_control_code)
1020 INTO x_cascaded_table(n).use_mtl_lot,
1021 x_cascaded_table(n).use_mtl_serial
1022 FROM mtl_system_items
1023 WHERE mtl_system_items.inventory_item_id = x_cascaded_table(n).item_id
1024 AND mtl_system_items.organization_id = x_cascaded_table(n).to_organization_id;
1025
1026 END Comment: Bug: 4735484
1027 */
1028 x_cascaded_table(n).interface_source_code := 'RCV';
1029
1030 IF (x_cascaded_table(n).source_document_code IS NULL) THEN
1031 x_cascaded_table(n).source_document_code := 'INVENTORY';
1032 END IF;
1033
1034 IF (g_asn_debug = 'Y') THEN
1035 asn_debug.put_line('Exit default_int_org_rcv_line');
1036 END IF;
1037
1038 IF shipments%ISOPEN THEN
1039 CLOSE shipments;
1040 END IF;
1041 EXCEPTION
1042 WHEN OTHERS THEN
1043 NULL;
1044 END default_int_org_rcv_line;
1045
1046 PROCEDURE validate_int_org_rcv_line(
1047 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1048 n IN BINARY_INTEGER,
1049 x_header_record IN rcv_roi_preprocessor.header_rec_type
1050 ) IS
1051 BEGIN
1052 IF (g_asn_debug = 'Y') THEN
1053 asn_debug.put_line('Enter validate_int_org_rcv_line');
1054 END IF;
1055
1056 x_progress := '000';
1057 rcv_roi_transaction.validate_transaction_date(x_cascaded_table, n);
1058 rcv_roi_transaction.validate_transaction_uom(x_cascaded_table, n);
1059 rcv_roi_transaction.validate_item_info(x_cascaded_table, n);
1060 rcv_roi_transaction.validate_freight_carrier_code(x_cascaded_table, n);
1061 rcv_roi_transaction.validate_dest_type(x_cascaded_table, n);
1062
1063 IF (x_cascaded_table(n).ship_to_location_id IS NOT NULL) THEN
1064 rcv_roi_transaction.validate_ship_to_loc(x_cascaded_table, n);
1065 END IF;
1066
1067 rcv_roi_transaction.validate_deliver_to_person(x_cascaded_table, n);
1068 rcv_roi_transaction.validate_routing_record(x_cascaded_table, n);
1069 rcv_roi_transaction.validate_deliver_to_loc(x_cascaded_table, n);
1070 rcv_roi_transaction.validate_subinventory(x_cascaded_table, n);
1071 rcv_roi_transaction.validate_locator(x_cascaded_table, n);
1072 rcv_roi_transaction.validate_tax_code(x_cascaded_table,
1073 n,
1074 x_header_record.header_record.asn_type
1075 ); /* Bug3454491 */
1076 rcv_roi_transaction.validate_country_of_origin(x_cascaded_table, n);
1077 /* Bug 3735972.
1078 * We used to call rcv_roi_transaction.validate_ref_integrity that had
1079 * code only for PO.
1080 * We now have a similar one to validate internal orders and
1081 * inter-org shipments in rcv_int_org_transfer package.
1082 */
1083 rcv_int_org_transfer.validate_ref_integrity(x_cascaded_table,
1084 n,
1085 x_header_record
1086 );
1087 rcv_roi_transaction.exchange_sub_items(x_cascaded_table, n);
1088
1092 /* end , INVCONV */
1089 /* INVCONV , introduced following call . Punit Kumar */
1090 rcv_roi_transaction.validate_opm_attributes(x_cascaded_table, n);
1091
1093
1094
1095 /* If destination_type_code is inventory then we need to make
1096 * sure that we can correct this qty since it might have been
1097 * already reserved in inventory.
1098 */
1099 IF (x_cascaded_table(n).destination_type_code = 'INVENTORY') THEN --{
1100 rcv_roi_return.derive_inv_qty(x_cascaded_table, n);
1101 END IF; --}
1102 EXCEPTION
1103 WHEN OTHERS THEN
1104 NULL;
1105 END validate_int_org_rcv_line;
1106
1107 --=======================================================================================
1108
1109
1110 PROCEDURE derive_int_org_trans_del(
1111 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1112 n IN OUT NOCOPY BINARY_INTEGER,
1113 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1114 x_header_record IN rcv_roi_preprocessor.header_rec_type
1115 ) IS
1116 BEGIN
1117 IF (g_asn_debug = 'Y') THEN
1118 asn_debug.put_line('enter derive_int_org_trans_del ');
1119 END IF;
1120
1121 /* Derive the to_org_id */
1122 rcv_roi_transaction.derive_ship_to_org_info(x_cascaded_table,
1123 n,
1124 x_header_record
1125 );
1126
1127 IF (x_cascaded_table(n).unit_of_measure IS NOT NULL) THEN
1128 IF (g_asn_debug = 'Y') THEN
1129 asn_debug.put_line('X_progress ' || x_progress);
1130 END IF;
1131
1132 SELECT muom.uom_code
1133 INTO x_cascaded_table(n).uom_code
1134 FROM mtl_units_of_measure muom
1135 WHERE muom.unit_of_measure = x_cascaded_table(n).unit_of_measure;
1136 ELSE
1137 IF (g_asn_debug = 'Y') THEN
1138 asn_debug.put_line('uom_code not dereived as unit_of_measure is null');
1139 END IF;
1140 END IF;
1141
1142 x_progress := '026';
1143
1144 /* Locator info derivation is done for the Receiving locators FPJ
1145 * project. Need to verify this with karun to see whether this is
1146 * needed for Transfer also.
1147 */
1148 IF (x_cascaded_table(n).transaction_type = 'TRANSFER') THEN
1149 asn_debug.put_line('doing ship to location /locator derivations ');
1150 rcv_roi_transaction.derive_location_info(x_cascaded_table, n);
1151 rcv_roi_transaction.derive_from_locator_id(x_cascaded_table, n); -- WMS Change
1152 rcv_roi_transaction.derive_to_locator_id(x_cascaded_table, n); -- WMS Change
1153 END IF;
1154
1155 x_progress := '091';
1156 rcv_roi_transaction.derive_reason_info(x_cascaded_table, n);
1157 /* Auto_transact_code is null for all these transaction types */
1158 x_cascaded_table(n).auto_transact_code := NULL;
1159 derive_trans_del_line_quantity(x_cascaded_table,
1160 n,
1161 temp_cascaded_table
1162 );
1163 END derive_int_org_trans_del;
1164
1165 PROCEDURE default_int_org_trans_del(
1166 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1167 n IN BINARY_INTEGER
1168 ) IS
1169 p_trx_record rcv_roi_header_common.common_default_record_type;
1170
1171 /* 3456476.
1172 * We need to default inspection_status_code only for ACCEPT/REJECT
1173 * transactions. For others get it from their parent.
1174 * Removed the code that defaulted inspection_status_code to
1175 * NOT INSPECTED for all the transactions other than ACCEPT/REJECT.
1176 */
1177 CURSOR int_org_transfer(
1178 v_parent_trx_id NUMBER
1179 ) IS
1180 SELECT rt.po_revision_num,
1181 rsl.item_description,
1182 rsup.po_release_id,
1183 rt.location_id loc_id,
1184 rt.organization_id,
1185 rt.inspection_status_code,
1186 rt.routing_header_id,
1187 rt.currency_code,
1188 rt.currency_conversion_rate,
1189 rt.currency_conversion_type,
1190 rt.currency_conversion_date,
1191 rt.location_id,
1192 rsup.shipment_header_id,
1193 rsup.shipment_line_id,
1194 rsl.category_id,
1195 --rt.vendor_id,
1196 --rt.vendor_site_id,
1197 --rt.po_unit_price,
1198 --rt.movement_id,
1199 rt.deliver_to_person_id,
1200 rt.deliver_to_location_id,
1201 rt.subinventory,
1202 rt.transfer_lpn_id
1203 FROM rcv_transactions rt,
1204 rcv_shipment_lines rsl,
1205 rcv_supply rsup
1206 WHERE rt.transaction_id = v_parent_trx_id
1207 AND rt.transaction_id = rsup.rcv_transaction_id
1208 AND rsup.supply_type_code = 'RECEIVING'
1209 AND rsl.shipment_line_id = rsup.shipment_line_id
1213 CURSOR int_org_transfer_rti(
1210 AND rt.transaction_id = rsup.rcv_transaction_id
1211 AND rt.transaction_type <> 'UNORDERED';
1212
1214 v_parent_inter_trx_id NUMBER
1215 ) IS
1216 SELECT rti.po_revision_num,
1217 rti.item_description,
1218 rti.po_release_id,
1219 rti.location_id loc_id,
1220 rti.to_organization_id organization_id,
1221 rti.inspection_status_code,
1222 rti.routing_header_id,
1223 rti.currency_code,
1224 rti.currency_conversion_rate,
1225 rti.currency_conversion_type,
1226 rti.currency_conversion_date,
1227 rti.location_id,
1228 rti.shipment_header_id,
1229 rti.shipment_line_id,
1230 rti.category_id,
1231 --rti.vendor_id,
1232 --rti.vendor_site_id,
1233 --rti.po_unit_price,
1234 --rti.movement_id,
1235 rti.deliver_to_person_id,
1236 rti.deliver_to_location_id,
1237 rti.subinventory,
1238 rti.transfer_lpn_id
1239 FROM rcv_transactions_interface rti
1240 WHERE interface_transaction_id = v_parent_inter_trx_id;
1241
1242 default_int_org_transfer_info int_org_transfer%ROWTYPE;
1243 x_progress VARCHAR2(3);
1244 x_locator_control NUMBER;
1245 x_default_subinventory VARCHAR2(10);
1246 x_default_locator_id NUMBER;
1247 x_success BOOLEAN;
1248 x_tax_name VARCHAR2(50); -- Bug 6331613
1249 /* default variables */
1250 l_project_id NUMBER;
1251 l_task_id NUMBER;
1252 l_locator_id NUMBER;
1253 temp_index NUMBER;
1254 BEGIN
1255 p_trx_record.destination_type_code := x_cascaded_table(n).destination_type_code;
1256 p_trx_record.transaction_type := x_cascaded_table(n).transaction_type;
1257 p_trx_record.processing_mode_code := x_cascaded_table(n).processing_mode_code;
1258 p_trx_record.processing_status_code := x_cascaded_table(n).processing_status_code;
1259 p_trx_record.transaction_status_code := x_cascaded_table(n).transaction_status_code;
1260 p_trx_record.auto_transact_code := x_cascaded_table(n).auto_transact_code;
1261 rcv_roi_header_common.commondefaultcode(p_trx_record);
1262 x_cascaded_table(n).destination_type_code := p_trx_record.destination_type_code;
1263 x_cascaded_table(n).transaction_type := p_trx_record.transaction_type;
1264 x_cascaded_table(n).processing_mode_code := p_trx_record.processing_mode_code;
1265 x_cascaded_table(n).processing_status_code := p_trx_record.processing_status_code;
1266 x_cascaded_table(n).transaction_status_code := p_trx_record.transaction_status_code;
1267 x_cascaded_table(n).auto_transact_code := p_trx_record.auto_transact_code;
1268
1269 IF (g_asn_debug = 'Y') THEN
1270 asn_debug.put_line('Enter Default int org transfer');
1271 END IF;
1272
1273 IF (x_cascaded_table(n).derive = 'Y') THEN --{
1274 IF (x_cascaded_table(n).derive_index <> 0) THEN --{
1275 /* Get the values from pl/sql table */
1276 temp_index := x_cascaded_table(n).derive_index;
1277 x_cascaded_table(n).item_description := x_cascaded_table(temp_index).item_description;
1278 x_cascaded_table(n).to_organization_id := x_cascaded_table(temp_index).to_organization_id;
1279 x_cascaded_table(n).routing_header_id := x_cascaded_table(temp_index).routing_header_id;
1280 x_cascaded_table(n).currency_code := x_cascaded_table(temp_index).currency_code;
1281 x_cascaded_table(n).currency_conversion_rate := x_cascaded_table(temp_index).currency_conversion_rate;
1282 x_cascaded_table(n).currency_conversion_type := x_cascaded_table(temp_index).currency_conversion_type;
1283 x_cascaded_table(n).currency_conversion_date := x_cascaded_table(temp_index).currency_conversion_date;
1284 x_cascaded_table(n).shipment_header_id := x_cascaded_table(temp_index).shipment_header_id;
1285 x_cascaded_table(n).inspection_status_code := x_cascaded_table(temp_index).inspection_status_code;
1286 x_cascaded_table(n).shipment_line_id := x_cascaded_table(temp_index).shipment_line_id;
1287 x_cascaded_table(n).category_id := x_cascaded_table(temp_index).category_id;
1288
1289 -- default the parent's to_lpn into the child's from_lpn
1290 IF (x_cascaded_table(n).lpn_id IS NULL) THEN
1291 x_cascaded_table(n).lpn_id := x_cascaded_table(temp_index).transfer_lpn_id;
1292
1293 --insert warning message into po_interface_errors
1294 IF (x_cascaded_table(n).lpn_id IS NOT NULL) THEN
1295 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_warning;
1296 rcv_error_pkg.set_error_message('RCV_LPN_UNPACK_WARNING', x_cascaded_table(n).error_message);
1297 rcv_error_pkg.set_token('LPN_ID', x_cascaded_table(n).lpn_id);
1298 rcv_error_pkg.log_interface_warning('LPN_ID');
1299 END IF;
1300 END IF;
1301
1302 IF (x_cascaded_table(n).location_id IS NULL) THEN
1306 IF (x_cascaded_table(n).transaction_type = 'DELIVER') THEN --{
1303 x_cascaded_table(n).location_id := x_cascaded_table(temp_index).location_id;
1304 END IF;
1305
1307 IF (NVL(x_cascaded_table(n).deliver_to_person_id, 0) = 0) THEN
1308 x_cascaded_table(n).deliver_to_person_id := x_cascaded_table(temp_index).deliver_to_person_id;
1309 END IF;
1310
1311 IF (NVL(x_cascaded_table(n).deliver_to_location_id, 0) = 0) THEN
1312 x_cascaded_table(n).deliver_to_location_id := x_cascaded_table(temp_index).deliver_to_location_id;
1313 END IF;
1314
1315 IF (x_cascaded_table(n).subinventory IS NULL) THEN
1316 x_cascaded_table(n).subinventory := x_cascaded_table(temp_index).subinventory;
1317 END IF;
1318 END IF; --}
1319 ELSE --} {
1320 IF (g_asn_debug = 'Y') THEN
1321 asn_debug.put_line('open cursor Default');
1322 END IF;
1323
1324 OPEN int_org_transfer_rti(x_cascaded_table(n).parent_interface_txn_id);
1325 END IF; --}
1326 ELSE -- } {
1327 OPEN int_org_transfer(x_cascaded_table(n).parent_transaction_id);
1328 END IF; --}
1329
1330 IF (int_org_transfer%ISOPEN) THEN
1331 IF (g_asn_debug = 'Y') THEN
1332 asn_debug.put_line('fetch cursor Default');
1333 END IF;
1334
1335 FETCH int_org_transfer INTO default_int_org_transfer_info;
1336 ELSIF(int_org_transfer_rti%ISOPEN) THEN
1337 FETCH int_org_transfer_rti INTO default_int_org_transfer_info;
1338 END IF;
1339
1340 IF ( ( int_org_transfer%ISOPEN
1341 AND int_org_transfer%FOUND)
1342 OR ( int_org_transfer_rti%ISOPEN
1343 AND int_org_transfer_rti%FOUND)) THEN --{
1344 IF (g_asn_debug = 'Y') THEN --{
1345 asn_debug.put_line('Defaulting Transfer item_description' || default_int_org_transfer_info.item_description);
1346 asn_debug.put_line('Defaulting Transfer organization_id' || default_int_org_transfer_info.organization_id);
1347 asn_debug.put_line('Defaulting Transfer inspection_status_code' || default_int_org_transfer_info.inspection_status_code);
1348 asn_debug.put_line('Defaulting Transfer routing_header_id' || default_int_org_transfer_info.routing_header_id);
1349 asn_debug.put_line('Defaulting Transfer currency_code' || default_int_org_transfer_info.currency_code);
1350 asn_debug.put_line('Defaulting Transfer currency_conversion_rate' || default_int_org_transfer_info.currency_conversion_rate);
1351 asn_debug.put_line('Defaulting Transfer currency_conversion_type' || default_int_org_transfer_info.currency_conversion_type);
1352 asn_debug.put_line('Defaulting Transfer currency_conversion_date' || default_int_org_transfer_info.currency_conversion_date);
1353 asn_debug.put_line('Defaulting Transfer shipment_header_id' || default_int_org_transfer_info.shipment_header_id);
1354 asn_debug.put_line('Defaulting Transfer shipment_line_id' || default_int_org_transfer_info.shipment_line_id);
1355 asn_debug.put_line('Defaulting Transfer category_id' || default_int_org_transfer_info.category_id);
1356 asn_debug.put_line('Defaulting Transfer DELIVER_TO_PERSON_ID' || default_int_org_transfer_info.deliver_to_person_id);
1357 asn_debug.put_line('Defaulting Transfer DELIVER_TO_LOCATION_ID' || default_int_org_transfer_info.deliver_to_location_id);
1358 asn_debug.put_line('Defaulting Transfer SUBINVENTORY' || default_int_org_transfer_info.subinventory);
1359 END IF; --}
1360
1361 --x_cascaded_table(n).po_revision_num := default_int_org_transfer_info.po_revision_num;
1362
1363 x_cascaded_table(n).item_description := default_int_org_transfer_info.item_description;
1364 --x_cascaded_table(n).po_release_id := default_int_org_transfer_info.po_release_id;
1365 x_cascaded_table(n).to_organization_id := default_int_org_transfer_info.organization_id;
1366 x_cascaded_table(n).inspection_status_code := default_int_org_transfer_info.inspection_status_code;
1367 x_cascaded_table(n).routing_header_id := default_int_org_transfer_info.routing_header_id;
1368 x_cascaded_table(n).currency_code := default_int_org_transfer_info.currency_code;
1369 x_cascaded_table(n).currency_conversion_rate := default_int_org_transfer_info.currency_conversion_rate;
1370 x_cascaded_table(n).currency_conversion_type := default_int_org_transfer_info.currency_conversion_type;
1371 x_cascaded_table(n).currency_conversion_date := default_int_org_transfer_info.currency_conversion_date;
1372 x_cascaded_table(n).shipment_header_id := default_int_org_transfer_info.shipment_header_id;
1373 x_cascaded_table(n).shipment_line_id := default_int_org_transfer_info.shipment_line_id;
1374 x_cascaded_table(n).category_id := default_int_org_transfer_info.category_id;
1375
1376 IF (x_cascaded_table(n).location_id IS NULL) THEN
1377 x_cascaded_table(n).location_id := default_int_org_transfer_info.loc_id;
1378 END IF;
1379
1380 -- default the parent's to_lpn into the child's from_lpn
1381 IF (x_cascaded_table(n).lpn_id IS NULL) THEN
1382 x_cascaded_table(n).lpn_id := default_int_org_transfer_info.transfer_lpn_id;
1383
1384 --insert warning message into po_interface_errors
1385 IF (x_cascaded_table(n).lpn_id IS NOT NULL) THEN
1386 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_warning;
1390 END IF;
1387 rcv_error_pkg.set_error_message('RCV_LPN_UNPACK_WARNING', x_cascaded_table(n).error_message);
1388 rcv_error_pkg.set_token('LPN_ID', x_cascaded_table(n).lpn_id);
1389 rcv_error_pkg.log_interface_warning('LPN_ID');
1391 END IF;
1392
1393 IF (x_cascaded_table(n).transaction_type = 'DELIVER') THEN --{
1394 IF (NVL(x_cascaded_table(n).deliver_to_person_id, 0) = 0) THEN
1395 x_cascaded_table(n).deliver_to_person_id := default_int_org_transfer_info.deliver_to_person_id;
1396 END IF;
1397
1398 IF (NVL(x_cascaded_table(n).deliver_to_location_id, 0) = 0) THEN
1399 x_cascaded_table(n).deliver_to_location_id := default_int_org_transfer_info.deliver_to_location_id;
1400 END IF;
1401
1402 IF (x_cascaded_table(n).subinventory IS NULL) THEN
1403 x_cascaded_table(n).subinventory := default_int_org_transfer_info.subinventory;
1404 END IF;
1405 END IF; --}
1406 END IF; -- if int_org_transfer%found is true }
1407
1408 /*
1409 BEGIN Comment: Bug: 4735484
1410
1411 SELECT NVL(x_cascaded_table(n).use_mtl_lot, lot_control_code),
1412 NVL(x_cascaded_table(n).use_mtl_serial, serial_number_control_code)
1413 INTO x_cascaded_table(n).use_mtl_lot,
1414 x_cascaded_table(n).use_mtl_serial
1415 FROM mtl_system_items
1416 WHERE mtl_system_items.inventory_item_id = x_cascaded_table(n).item_id
1417 AND mtl_system_items.organization_id = x_cascaded_table(n).to_organization_id;
1418
1419 END Comment: Bug: 4735484
1420 */
1421 x_cascaded_table(n).interface_source_code := 'RCV';
1422
1423 /* 3456476.
1424 * We need to default inspection_status_code only for ACCEPT/REJECT
1425 * transactions. For others get it from their parent.
1426 * Removed the code that defaulted inspection_status_code to
1427 * NOT INSPECTED for all the transactions other than ACCEPT/REJECT.
1428 */
1429 IF (x_cascaded_table(n).transaction_type = 'ACCEPT') THEN
1430 x_cascaded_table(n).inspection_status_code := 'ACCEPTED';
1431 x_cascaded_table(n).destination_context := 'RECEIVING';
1432 ELSIF(x_cascaded_table(n).transaction_type = 'REJECT') THEN
1433 x_cascaded_table(n).inspection_status_code := 'REJECTED';
1434 x_cascaded_table(n).destination_context := 'RECEIVING';
1435 END IF;
1436
1437 /* Only for deliver, to_subinventory is a required field. If the user
1438 * has not provided then we will not default for the other transactions.
1439 * from_subinventory will be the to_sub of the parent .
1440 */
1441 ---WMS Changes
1442 rcv_roi_transaction.default_from_subloc_info(x_cascaded_table, n);
1443
1444 IF (x_cascaded_table(n).transaction_type = 'DELIVER') THEN
1445 rcv_roi_transaction.default_to_subloc_info(x_cascaded_table, n);
1446 END IF;
1447
1448 /*
1449 ** Make sure to set the location_id properly
1450 */
1451 IF (x_cascaded_table(n).transaction_type = 'DELIVER') THEN
1452 x_cascaded_table(n).location_id := x_cascaded_table(n).deliver_to_location_id;
1453 END IF;
1454
1455 IF (g_asn_debug = 'Y') THEN
1456 asn_debug.put_line('Set Location_id = ' || TO_CHAR(x_cascaded_table(n).location_id));
1457 END IF;
1458
1459 IF (g_asn_debug = 'Y') THEN
1460 asn_debug.put_line('Exit default_vendor_trans_del');
1461 END IF;
1462
1463 IF int_org_transfer%ISOPEN THEN
1464 CLOSE int_org_transfer;
1465 END IF;
1466
1467 IF int_org_transfer_rti%ISOPEN THEN
1468 CLOSE int_org_transfer_rti;
1469 END IF;
1470 EXCEPTION
1471 WHEN OTHERS THEN
1472 NULL;
1473 END default_int_org_trans_del;
1474
1475 PROCEDURE derive_trans_del_line_quantity(
1476 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1477 n IN OUT NOCOPY BINARY_INTEGER,
1478 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type
1479 ) IS
1480 x_include_closed_po VARCHAR2(1); -- Bug 1887728
1481 /*
1482 ** Might be a compatibility issue between the two record definitions
1483 */
1484 x_record_count NUMBER;
1485 x_remaining_quantity NUMBER := 0;
1486 x_remaining_qty_po_uom NUMBER := 0;
1487 x_progress VARCHAR2(3);
1488 x_to_organization_code VARCHAR2(5);
1489 x_converted_trx_qty NUMBER := 0;
1490 transaction_ok BOOLEAN := FALSE;
1491 rows_fetched NUMBER := 0;
1492 x_tolerable_qty NUMBER := 0;
1493 x_first_trans BOOLEAN := TRUE;
1494 --x_sysdate DATE := sysdate;
1495 current_n BINARY_INTEGER := 0;
1496 insert_into_table BOOLEAN := FALSE;
1497 --x_qty_rcv_exception_code po_line_locations.qty_rcv_exception_code%type;
1498 tax_amount_factor NUMBER;
1502 x_temp_count NUMBER;
1499 lastrecord BOOLEAN := FALSE;
1500 already_allocated_qty NUMBER := 0;
1501 x_item_id NUMBER;
1503 x_full_name VARCHAR2(240) := NULL; -- Bug 2392074
1504 /* Bug# 1548597 */
1505 x_secondary_available_qty NUMBER := 0;
1506
1507 /********************************************************************/
1508 CURSOR int_org_transfer(
1509 v_parent_trx_id NUMBER,
1510 v_to_organization_id NUMBER
1511 ) IS
1512 SELECT rsup.rcv_transaction_id rcv_transaction_id,
1513 rt.transaction_date transaction_date,
1514 rt.transaction_type,
1515 rt.unit_of_measure unit_of_meas,
1516 rt.primary_unit_of_measure,
1517 rt.primary_quantity,
1518 rsup.to_organization_id,
1519 --RT.PO_UNIT_PRICE unit_price,
1520 rsl.category_id,
1521 rsl.item_description,
1522 --RSUP.PO_LINE_ID,
1523 rt.location_id,
1524 rsup.item_id,
1525 rsl.deliver_to_person_id, --pod.DELIVER_TO_PERSON_ID ,
1526 rsl.deliver_to_location_id, --pod.DELIVER_TO_LOCATION_ID ,
1527 rsup.to_subinventory destination_subinventory, --pod.destination_subinventory ,
1528 rt.destination_type_code,
1529 rt.organization_id destination_organization_id, --pod.destination_organization_id,
1530 rt.quantity qty,
1531 0 interface_available_qty
1532 FROM rcv_supply rsup,
1533 rcv_transactions rt,
1534 rcv_shipment_lines rsl
1535 WHERE rt.transaction_id = v_parent_trx_id
1536 AND rsup.to_organization_id = NVL(v_to_organization_id, rsup.to_organization_id)
1537 AND rsup.supply_type_code = 'RECEIVING'
1538 AND rsl.shipment_line_id = rsup.shipment_line_id
1539 AND rt.transaction_id = rsup.rcv_transaction_id
1540 AND rt.transaction_type <> 'UNORDERED'
1541 ORDER BY transaction_date ASC;
1542
1543 CURSOR int_org_transfer_rti(
1544 v_parent_inter_trx_id NUMBER
1545 ) IS
1546 SELECT rti.interface_transaction_id rcv_transaction_id,
1547 rti.transaction_date transaction_date,
1548 rti.transaction_type,
1549 rti.unit_of_measure unit_of_meas,
1550 rti.primary_unit_of_measure,
1551 rti.primary_quantity,
1552 rti.to_organization_id,
1553 rti.category_id,
1554 rti.item_description,
1555 rti.location_id,
1556 rti.item_id,
1557 rti.deliver_to_person_id,
1558 rti.deliver_to_location_id,
1559 rti.subinventory destination_subinventory,
1560 rti.destination_type_code,
1561 rti.to_organization_id destination_organization_id,
1562 rti.quantity qty,
1563 rti.interface_available_qty
1564 FROM rcv_transactions_interface rti
1565 WHERE interface_transaction_id = v_parent_inter_trx_id;
1566
1567 x_int_org_transferrec int_org_transfer%ROWTYPE;
1568 x_temp_parent_trx_qty NUMBER := 0;
1569 x_converted_parent_trx_qty NUMBER := 0;
1570 x_temp_convert_parent_trx_qty NUMBER := 0;
1571 x_remaining_qty_parent_uom NUMBER := 0;
1572 l_to_organization_id NUMBER := 0;
1573 x_temp_already_allocated_qty NUMBER := 0;
1574 derive_values_from_table BOOLEAN := FALSE;
1575 derive_values_from_rti BOOLEAN := FALSE;
1576 already_derived BOOLEAN := FALSE;
1577 cascaded_table_index NUMBER;
1578 temp_index NUMBER;
1579 l_supply_code rcv_supply.supply_type_code%TYPE;
1580 l_transaction_type rcv_transactions.transaction_type%TYPE;
1581 BEGIN
1582 IF (g_asn_debug = 'Y') THEN
1583 asn_debug.put_line('enter derive_quantity ' || x_cascaded_table(n).parent_transaction_id);
1584 END IF;
1585
1586 IF (x_cascaded_table(n).parent_transaction_id IS NOT NULL) THEN --{
1587 IF (g_asn_debug = 'Y') THEN
1588 asn_debug.put_line('open int_org_transfer table ' || x_cascaded_table(n).parent_transaction_id);
1589 END IF;
1590
1591 OPEN int_org_transfer(x_cascaded_table(n).parent_transaction_id, x_cascaded_table(n).to_organization_id);
1592 already_derived := TRUE;
1593 END IF; --}
1594
1595 IF (NOT already_derived) THEN --{
1596 rcv_roi_transaction.derive_parent_id(x_cascaded_table, n);
1597
1598 /* This means that there was no error in derive_parent_id which means that the
1599 * this is a child and need to get the values from the rti and not from the plsql table.
1600 */
1601 IF ( (x_cascaded_table(n).error_status <> 'E')
1605 * at the time when we try to see which cursor is open. We will have x_cascaded_table(n).
1602 AND ( x_cascaded_table(n).derive = 'Y'
1603 AND x_cascaded_table(n).derive_index = 0)) THEN
1604 /* if derive_values_from_table is true, then we derive the values from the pl/sql tables later
1606 * parent_interface_txn_id) populated with the correct value.
1607 */
1608 IF (g_asn_debug = 'Y') THEN
1609 asn_debug.put_line(' open pl/sql table');
1610 END IF;
1611
1612 OPEN int_org_transfer_rti(x_cascaded_table(n).parent_interface_txn_id);
1613 END IF;
1614 END IF; --}
1615
1616 /******************************************************************/
1617 --check line quantity > 0
1618 x_progress := '097';
1619
1620 IF (g_asn_debug = 'Y') THEN
1621 asn_debug.put_line('X_progress ' || x_progress);
1622 END IF;
1623
1624 IF (x_cascaded_table(n).error_status NOT IN('S', 'W')) THEN
1625 RETURN;
1626 END IF;
1627
1628 IF x_cascaded_table(n).quantity <= 0 THEN --{
1629 IF (g_asn_debug = 'Y') THEN
1630 asn_debug.put_line('Quantity is <= zero. Cascade will fail');
1631 END IF;
1632
1633 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1634 rcv_error_pkg.set_error_message('RCV_ITEM_NO_SHIP_QTY', x_cascaded_table(n).error_message);
1635 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).item_num);
1636 rcv_error_pkg.log_interface_error('QUANTITY');
1637 END IF; --} end qty > 0 check
1638
1639 -- the following steps will create a set of rows linking the line_record with
1640 -- its corresponding po_line_location rows until the quantity value from
1641 -- the asn is consumed. (Cascade)
1642
1643 x_progress := '098';
1644
1645 IF (g_asn_debug = 'Y') THEN
1646 asn_debug.put_line('X_progress ' || x_progress);
1647 END IF;
1648
1649 IF ( x_cascaded_table(n).parent_transaction_id IS NULL
1650 AND x_cascaded_table(n).parent_interface_txn_id IS NULL) THEN
1651 IF (g_asn_debug = 'Y') THEN
1652 asn_debug.put_line('No parent transaction found ');
1653 END IF;
1654
1655 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1656 rcv_error_pkg.set_error_message('RCV_NO_PARENT_TRANSACTION', x_cascaded_table(n).error_message);
1657 rcv_error_pkg.log_interface_error('PARENT_TRANSACTION_ID');
1658 END IF;
1659
1660 -- Copy record from main table to temp table
1661 IF (g_asn_debug = 'Y') THEN
1662 asn_debug.put_line('Copy record from main table to temp table');
1663 END IF;
1664
1665 current_n := 1;
1666 temp_cascaded_table(current_n) := x_cascaded_table(n);
1667
1668 -- Get all rows which meet this condition
1669 IF (g_asn_debug = 'Y') THEN
1670 asn_debug.put_line('Get all rows which meet this condition');
1671 asn_debug.put_line('Transaction Type = ' || x_cascaded_table(n).transaction_type);
1672 asn_debug.put_line('Auto Transact Code = ' || x_cascaded_table(n).auto_transact_code);
1673 END IF;
1674
1675 -- Assign shipped quantity to remaining quantity
1676 IF (g_asn_debug = 'Y') THEN
1677 asn_debug.put_line('Assign populated quantity to remaining quantity');
1678 asn_debug.put_line('Pointer in temp_cascade ' || TO_CHAR(current_n));
1679 END IF;
1680
1681 x_remaining_quantity := temp_cascaded_table(current_n).quantity;
1682 x_remaining_qty_po_uom := 0;
1683
1684 IF (g_asn_debug = 'Y') THEN
1685 asn_debug.put_line('Have assigned the quantity');
1686 END IF;
1687
1688 -- Calculate tax_amount_factor for calculating tax_amount for
1689 -- each cascaded line
1690
1691 IF NVL(temp_cascaded_table(current_n).tax_amount, 0) <> 0 THEN
1692 tax_amount_factor := temp_cascaded_table(current_n).tax_amount / x_remaining_quantity;
1693 ELSE
1694 tax_amount_factor := 0;
1695 END IF;
1696
1697 IF (g_asn_debug = 'Y') THEN
1698 asn_debug.put_line('Tax Factor ' || TO_CHAR(tax_amount_factor));
1699 asn_debug.put_line('Shipped Quantity : ' || TO_CHAR(x_remaining_quantity));
1700 END IF;
1701
1702 x_first_trans := TRUE;
1703 transaction_ok := FALSE;
1704
1705 IF (g_asn_debug = 'Y') THEN
1706 asn_debug.put_line('Before starting Cascade');
1707 END IF;
1708
1709 IF (g_asn_debug = 'Y') THEN
1710 asn_debug.put_line('Record Count = ' || x_record_count);
1711 END IF;
1712
1713 LOOP --{
1714 IF (g_asn_debug = 'Y') THEN
1715 asn_debug.put_line('Remaining Quantity ASN UOM ' || TO_CHAR(x_remaining_quantity));
1716 END IF;
1717
1718 /*
1719 ** Fetch the appropriate record
1720 */
1721 IF (g_asn_debug = 'Y') THEN
1722 asn_debug.put_line('DEBUG: transaction_type = ' || x_cascaded_table(n).transaction_type);
1723 END IF;
1724
1725 IF (int_org_transfer%ISOPEN) THEN --{
1726 IF (g_asn_debug = 'Y') THEN
1727 asn_debug.put_line(' fetch int_org_transfer');
1731
1728 END IF;
1729
1730 FETCH int_org_transfer INTO x_int_org_transferrec;
1732 IF (int_org_transfer%NOTFOUND) THEN
1733 lastrecord := TRUE;
1734 END IF;
1735
1736 rows_fetched := int_org_transfer%ROWCOUNT;
1737 ELSIF(int_org_transfer_rti%ISOPEN) THEN --}{
1738 IF (g_asn_debug = 'Y') THEN
1739 asn_debug.put_line(' fetch int_org_transfer_rti');
1740 END IF;
1741
1742 FETCH int_org_transfer_rti INTO x_int_org_transferrec;
1743
1744 IF (int_org_transfer_rti%NOTFOUND) THEN
1745 IF (g_asn_debug = 'Y') THEN
1746 asn_debug.put_line('transfer last row');
1747 END IF;
1748
1749 lastrecord := TRUE;
1750 END IF;
1751
1752 rows_fetched := int_org_transfer_rti%ROWCOUNT;
1753 ELSIF(temp_cascaded_table(current_n).derive = 'Y') THEN --}{
1754 /* GET VALUES FROM THE PLSQL TABLE */
1755 /* Populate x_int_org_transferrec with these values since
1756 * we are using x_int_org_transferrec later.
1757 * We have temp_cascaded_table(current_n).
1758 * parent_interface_txn_id) populated with
1759 * with the correct value. Also we have cascaded_table_index
1760 * with the correct pl/sql table index number;
1761 */
1762 IF (g_asn_debug = 'Y') THEN
1763 asn_debug.put_line(' fetch pl/sql table');
1764 END IF;
1765
1766 temp_index := temp_cascaded_table(current_n).derive_index;
1767 x_int_org_transferrec.unit_of_meas := x_cascaded_table(temp_index).unit_of_measure;
1768 x_int_org_transferrec.transaction_date := x_cascaded_table(temp_index).transaction_date;
1769 x_int_org_transferrec.transaction_type := x_cascaded_table(temp_index).transaction_type;
1770 x_int_org_transferrec.primary_unit_of_measure := x_cascaded_table(temp_index).primary_unit_of_measure;
1771 x_int_org_transferrec.primary_quantity := x_cascaded_table(temp_index).primary_quantity;
1772 x_int_org_transferrec.to_organization_id := x_cascaded_table(temp_index).to_organization_id;
1773 x_int_org_transferrec.category_id := x_cascaded_table(temp_index).category_id;
1774 x_int_org_transferrec.item_description := x_cascaded_table(temp_index).item_description;
1775 x_int_org_transferrec.location_id := x_cascaded_table(temp_index).location_id;
1776 x_int_org_transferrec.item_id := x_cascaded_table(temp_index).item_id;
1777 x_int_org_transferrec.deliver_to_person_id := x_cascaded_table(temp_index).deliver_to_person_id;
1778 x_int_org_transferrec.deliver_to_location_id := x_cascaded_table(temp_index).deliver_to_location_id;
1779 x_int_org_transferrec.destination_subinventory := x_cascaded_table(temp_index).subinventory;
1780 x_int_org_transferrec.destination_type_code := x_cascaded_table(temp_index).destination_type_code;
1781 x_int_org_transferrec.qty := x_cascaded_table(temp_index).quantity;
1782 rows_fetched := 1;
1783 lastrecord := TRUE;
1784 END IF; --}
1785
1786 --x_remaining_quantity:= temp_cascaded_table(current_n).quantity;
1787
1788 IF (g_asn_debug = 'Y') THEN
1789 asn_debug.put_line('Transfer Rows fetched ' || TO_CHAR(rows_fetched));
1790 --asn_debug.put_line('po_line_id ' || to_char(x_int_org_transferrec.po_line_id));
1791 --asn_debug.put_line('po_dist ' || to_char(x_int_org_transferrec.po_distribution_id));
1792 asn_debug.put_line('Transfer remainaing qty ' || x_remaining_quantity);
1793 END IF;
1794
1795 IF ( lastrecord
1796 OR x_remaining_quantity <= 0) THEN --{
1797 IF (g_asn_debug = 'Y') THEN
1798 asn_debug.put_line('Hit exit condition');
1799 END IF;
1800
1801 IF NOT x_first_trans THEN
1802 -- x_first_trans has been reset which means some cascade has
1803 -- happened. Otherwise current_n = 1
1804 asn_debug.put_line('current_n before is ' || current_n);
1805 current_n := current_n - 1;
1806 END IF;
1807
1808 -- do the tolerance act here
1809 IF (g_asn_debug = 'Y') THEN
1810 asn_debug.put_line('Temp table pointer ' || TO_CHAR(current_n));
1811 asn_debug.put_line('Check which condition has occured');
1812 END IF;
1813
1814 -- lastrecord...we have run out of rows and we still have quantity to allocate
1815 IF x_remaining_quantity > 0 THEN --{
1816 IF (g_asn_debug = 'Y') THEN
1817 asn_debug.put_line('There is quantity remaining');
1818 asn_debug.put_line('Need to check qty tolerances');
1819 END IF;
1820
1821 IF rows_fetched > 0
1822 AND NOT x_first_trans THEN --{
1823 NULL;
1824
1825 /* for transfer,accept an reject type we dont have the
1826 * tolerance check. Hence error out.
1827 * We cannot transfer quantities more than that was received.
1828 */
1832 END IF;
1829 IF (g_asn_debug = 'Y') THEN
1830 asn_debug.put_line(' in transfer Extra ASN UOM Quantity ' || TO_CHAR(x_remaining_quantity));
1831 asn_debug.put_line('Extra PO UOM Quantity ' || TO_CHAR(x_remaining_qty_po_uom));
1833
1834 IF (g_asn_debug = 'Y') THEN
1835 asn_debug.put_line('delete the temp table ');
1836 END IF;
1837
1838 IF temp_cascaded_table.COUNT > 0 THEN
1839 FOR i IN 1 .. temp_cascaded_table.COUNT LOOP
1840 temp_cascaded_table.DELETE(i);
1841 END LOOP;
1842 END IF;
1843
1844 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1845 rcv_error_pkg.set_error_message('RCV_SHIP_QTY_OVER_TOLERANCE', x_cascaded_table(n).error_message);
1846 rcv_error_pkg.set_token('QTY_A', x_cascaded_table(n).quantity);
1847 rcv_error_pkg.set_token('QTY_B', x_cascaded_table(n).quantity - x_remaining_quantity);
1848 rcv_error_pkg.log_interface_error('QUANTITY');
1849 ELSE --}{ else for rows fetched = 0 OR x_first_trans = true
1850 IF rows_fetched = 0 THEN
1851 IF (g_asn_debug = 'Y') THEN
1852 asn_debug.put_line('No rows were retrieved from cursor.');
1853 END IF;
1854 ELSIF x_first_trans THEN
1855 IF (g_asn_debug = 'Y') THEN
1856 asn_debug.put_line('No rows were cascaded');
1857 END IF;
1858 END IF;
1859
1860 x_temp_count := 1;
1861
1862 IF (x_cascaded_table(n).parent_transaction_id IS NOT NULL) THEN --{
1863 SELECT MAX(rsup.supply_type_code),
1864 MAX(rt.transaction_type)
1865 INTO l_supply_code,
1866 l_transaction_type
1867 FROM rcv_transactions rt,
1868 rcv_supply rsup
1869 WHERE rt.transaction_id = temp_cascaded_table(current_n).parent_transaction_id
1870 AND rsup.rcv_transaction_id = rt.transaction_id;
1871
1872 IF l_supply_code <> 'RECEIVING' THEN
1873 rcv_error_pkg.set_error_message('RCV_ITEM_NO_SHIP_QTY', x_cascaded_table(n).error_message);
1874 ELSIF l_transaction_type = 'UNORDERED' THEN
1875 rcv_error_pkg.set_error_message('RCV_INVALID_TRANSACTION_TYPE', x_cascaded_table(n).error_message);
1876 END IF;
1877 ELSE
1878 rcv_error_pkg.set_error_message('RCV_INVALID_TRANSACTION_TYPE', x_cascaded_table(n).error_message);
1879 END IF; --}
1880
1881
1882 -- Delete the temp_cascaded_table just to be sure
1883
1884 IF temp_cascaded_table.COUNT > 0 THEN
1885 FOR i IN 1 .. temp_cascaded_table.COUNT LOOP
1886 temp_cascaded_table.DELETE(i);
1887 END LOOP;
1888 END IF;
1889
1890 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1891 rcv_error_pkg.log_interface_error('PARENT_TRANSACTION_ID');
1892 END IF; --} end else for rows fetched = 0 OR x_first_trans = true
1893 -- end x_remaining_qty > 0 => this is the last record
1894 ELSE -- }{
1895 IF (g_asn_debug = 'Y') THEN
1896 asn_debug.put_line('Remaining UOM quantity is zero ' || TO_CHAR(x_remaining_quantity));
1897 asn_debug.put_line('Return the cascaded rows back to the calling procedure');
1898 END IF;
1899 END IF; --} ends the check for whether last record has been reached
1900
1901 -- close cursors
1902
1903 IF (g_asn_debug = 'Y') THEN
1904 asn_debug.put_line('Close cursors shipments, count_shipments, distributions, count_disributions ' || current_n);
1905 END IF;
1906
1907 IF int_org_transfer%ISOPEN THEN
1908 CLOSE int_org_transfer;
1909 END IF;
1910
1911 IF int_org_transfer_rti%ISOPEN THEN
1912 CLOSE int_org_transfer_rti;
1913 END IF;
1914
1915 IF (g_asn_debug = 'Y') THEN
1916 asn_debug.put_line('before exit current_n is ' || current_n);
1917 END IF;
1918
1919 EXIT;
1920 END IF; --} matches lastrecord or x_remaining_quantity <= 0
1921
1922 -- eliminate the row if it fails the date check
1923
1924 IF (g_asn_debug = 'Y') THEN
1925 asn_debug.put_line(' Entering qty calculateion for transfer');
1926 END IF;
1927
1928 IF (x_first_trans)
1929 AND temp_cascaded_table(current_n).item_id IS NULL THEN
1930 temp_cascaded_table(current_n).item_id := x_int_org_transferrec.item_id;
1931 temp_cascaded_table(current_n).primary_unit_of_measure := x_int_org_transferrec.primary_unit_of_measure;
1932 END IF;
1933
1934 insert_into_table := FALSE;
1935 already_allocated_qty := 0;
1936 -- need to find out if the parent is in rti
1937 rcv_roi_transaction.get_interface_available_qty(temp_cascaded_table,
1941
1938 current_n,
1939 x_converted_parent_trx_qty
1940 );
1942 IF (x_converted_parent_trx_qty = 0) THEN --{
1943 IF (g_asn_debug = 'Y') THEN
1944 asn_debug.put_line('calling transfer get_available_qty ');
1945 END IF;
1946
1947 -- the call for transfer/deliver should be the same
1948 rcv_quantities_s.get_available_quantity('TRANSFER',
1949 x_int_org_transferrec.rcv_transaction_id,
1950 temp_cascaded_table(current_n).receipt_source_code,
1951 NULL,
1952 x_int_org_transferrec.rcv_transaction_id,
1953 NULL,
1954 x_converted_parent_trx_qty,
1955 x_tolerable_qty,
1956 x_int_org_transferrec.unit_of_meas,
1957 /*Bug# 1548597 */
1958 x_secondary_available_qty
1959 );
1960 END IF; --}
1961
1962 IF (g_asn_debug = 'Y') THEN
1963 asn_debug.put_line('qty from GET_AVAILABLE_QUANTITY for transfer is ' || x_converted_parent_trx_qty);
1964 END IF;
1965
1966 x_remaining_qty_parent_uom := rcv_roi_transaction.convert_into_correct_qty(x_remaining_quantity,
1967 temp_cascaded_table(1).unit_of_measure,
1968 temp_cascaded_table(1).item_id,
1969 x_int_org_transferrec.unit_of_meas
1970 );
1971
1972 IF (x_remaining_qty_parent_uom = 0) THEN --{
1973 IF (g_asn_debug = 'Y') THEN
1974 asn_debug.put_line(' Transfer Need an error message in the interface tables');
1975 END IF;
1976 ELSE /* Converted qty successfully and we have some quantity on which we can act */
1977 IF (g_asn_debug = 'Y') THEN
1978 asn_debug.put_line('Converted trx qty that is available ' || x_converted_parent_trx_qty);
1979 asn_debug.put_line('Remaining qty in parents uom that is available ' || x_remaining_qty_parent_uom);
1980 END IF;
1981
1982 IF (x_converted_parent_trx_qty > 0) THEN --{
1983 IF (x_converted_parent_trx_qty < x_remaining_qty_parent_uom) THEN --{
1984 /* Total quantity available to transfer is less than the qty
1985 * that the user wants to transfer. Hence we would error out but
1986 * to keep the old code we will get the remaining code here and
1987 * error out later.
1988 */
1989 x_remaining_qty_parent_uom := x_remaining_qty_parent_uom - x_converted_parent_trx_qty;
1990
1991 IF (temp_cascaded_table(current_n).unit_of_measure <> x_int_org_transferrec.unit_of_meas) THEN
1992 x_remaining_quantity := rcv_roi_transaction.convert_into_correct_qty(x_remaining_qty_parent_uom,
1993 x_int_org_transferrec.unit_of_meas,
1994 temp_cascaded_table(1).item_id,
1995 temp_cascaded_table(1).unit_of_measure
1996 );
1997 ELSE
1998 x_remaining_quantity := x_remaining_qty_parent_uom;
1999 END IF;
2000
2001 insert_into_table := TRUE;
2002 ELSE --}{
2003 IF (g_asn_debug = 'Y') THEN
2004 asn_debug.put_line('We are in >= Qty branch ');
2005 END IF;
2006
2007 x_converted_parent_trx_qty := x_remaining_qty_parent_uom;
2008 insert_into_table := TRUE;
2009 x_remaining_qty_parent_uom := 0;
2010 x_remaining_quantity := 0;
2011 END IF; --} /* if (x_converted_parent_trx_qty < x_remaining_qty_parent_uom) then */
2012 ELSE /* x_converted_parent_trx_qty >0 */ --}{
2013 IF rows_fetched = x_record_count THEN -- { last row needs to be inserted anyway
2014 -- so that the row can be used based on qty tolerance checks
2015 IF (g_asn_debug = 'Y') THEN
2016 asn_debug.put_line('Quantity is less then 0 but last record');
2017 END IF;
2018
2019 insert_into_table := TRUE;
2020 x_converted_trx_qty := 0;
2021 ELSE --}{
2022 IF (g_asn_debug = 'Y') THEN
2023 asn_debug.put_line('<= 0 Quantity but more records in cursor');
2024 END IF;
2025
2029 asn_debug.put_line('We have to deal with remaining_qty > 0 and x_converted_trx_qty -ve');
2026 x_remaining_qty_po_uom := 0; -- we may have a diff uom on the next iteration
2027
2028 IF (g_asn_debug = 'Y') THEN
2030 END IF;
2031
2032 insert_into_table := FALSE;
2033 END IF; --}
2034 END IF; /*x_converted_parent_trx_qty >0 */ --}
2035 END IF; -- } Converted qty successfully and we have some quantity on which we can act
2036
2037 IF (g_asn_debug = 'Y') THEN
2038 asn_debug.put_line('Transaction qty in terms of the parents uom is ' || x_converted_parent_trx_qty);
2039 END IF;
2040
2041 IF insert_into_table THEN --{ --start
2042 IF (x_first_trans) THEN --{
2043 IF (g_asn_debug = 'Y') THEN
2044 asn_debug.put_line('First Time ' || TO_CHAR(current_n));
2045 END IF;
2046
2047 x_first_trans := FALSE;
2048 ELSE --}{
2049 IF (g_asn_debug = 'Y') THEN
2050 asn_debug.put_line('Next Time ' || TO_CHAR(current_n));
2051 END IF;
2052
2053 temp_cascaded_table(current_n) := temp_cascaded_table(current_n - 1);
2054 END IF; --}
2055
2056 temp_cascaded_table(current_n).primary_unit_of_measure := x_int_org_transferrec.primary_unit_of_measure;
2057
2058 IF (temp_cascaded_table(current_n).unit_of_measure <> x_int_org_transferrec.unit_of_meas) THEN
2059 temp_cascaded_table(current_n).quantity := rcv_roi_transaction.convert_into_correct_qty(x_converted_parent_trx_qty,
2060 x_int_org_transferrec.unit_of_meas,
2061 temp_cascaded_table(current_n).item_id,
2062 temp_cascaded_table(current_n).unit_of_measure
2063 ); -- in asn uom
2064
2065 IF (g_asn_debug = 'Y') THEN
2066 asn_debug.put_line('Transaction qty in terms of the transaction uom is ' || temp_cascaded_table(current_n).quantity);
2067 END IF;
2068 ELSE
2069 temp_cascaded_table(current_n).quantity := x_converted_parent_trx_qty;
2070 END IF;
2071
2072 IF (temp_cascaded_table(current_n).primary_unit_of_measure <> x_int_org_transferrec.unit_of_meas) THEN
2073 temp_cascaded_table(current_n).primary_quantity := rcv_roi_transaction.convert_into_correct_qty(x_converted_parent_trx_qty,
2074 x_int_org_transferrec.unit_of_meas,
2075 temp_cascaded_table(current_n).item_id,
2076 temp_cascaded_table(current_n).primary_unit_of_measure
2077 );
2078 ELSE
2079 temp_cascaded_table(current_n).primary_quantity := x_converted_parent_trx_qty;
2080 END IF;
2081
2082 IF (g_asn_debug = 'Y') THEN
2083 asn_debug.put_line('Transaction qty in terms of the primary uom is ' || temp_cascaded_table(current_n).primary_quantity);
2084 END IF;
2085
2086 --sugatas put here
2087 --temp_cascaded_table(current_n).inspection_status_code := x_int_org_transferrec.inspection_status_code;
2088 temp_cascaded_table(current_n).interface_source_code := 'RCV';
2089
2090 -- destination type code will be parent's destination type code if txn type not deliver
2091 IF (NVL(x_cascaded_table(n).transaction_type, 'RECEIVE') <> 'DELIVER') THEN --{
2092 temp_cascaded_table(current_n).destination_type_code := x_int_org_transferrec.destination_type_code;
2093 temp_cascaded_table(current_n).destination_context := x_int_org_transferrec.destination_type_code;
2094 ELSIF(NVL(x_cascaded_table(n).transaction_type, 'RECEIVE') = 'DELIVER') THEN --}{
2095 -- temp_cascaded_table(current_n).currency_conversion_date := x_int_org_transferrec.rate_date;
2096 -- temp_cascaded_table(current_n).currency_conversion_rate := x_int_org_transferrec.rate;
2097 temp_cascaded_table(current_n).destination_type_code := 'INVENTORY';
2098
2099 -- temp_cascaded_table(current_n).destination_context := temp_cascaded_table(current_n).destination_type_code;
2100 IF (NVL(temp_cascaded_table(current_n).deliver_to_location_id, 0) = 0) THEN
2101 temp_cascaded_table(current_n).deliver_to_location_id := x_int_org_transferrec.deliver_to_location_id;
2102 END IF;
2103
2104 /* Bug 2392074 - If the deliver_to_person mentioned in the po_distributions is
2105 invalid or inactive at the time of Receipt we need to clear the deliver to person,
2106 as this is an optional field. */
2107 IF (NVL(temp_cascaded_table(current_n).deliver_to_person_id, 0) = 0) THEN --{
2111 BEGIN
2108 temp_cascaded_table(current_n).deliver_to_person_id := x_int_org_transferrec.deliver_to_person_id;
2109
2110 IF (temp_cascaded_table(current_n).deliver_to_person_id IS NOT NULL) THEN --{
2112 SELECT NVL(MAX(hre.full_name), 'notfound')
2113 INTO x_full_name
2114 FROM hr_employees_current_v hre
2115 WHERE ( hre.inactive_date IS NULL
2116 OR hre.inactive_date > SYSDATE)
2117 AND hre.employee_id = temp_cascaded_table(current_n).deliver_to_person_id;
2118
2119 IF (x_full_name = 'notfound') THEN
2120 temp_cascaded_table(current_n).deliver_to_person_id := NULL;
2121 END IF;
2122 EXCEPTION
2123 WHEN NO_DATA_FOUND THEN
2124 temp_cascaded_table(current_n).deliver_to_person_id := NULL;
2125
2126 IF (g_asn_debug = 'Y') THEN
2127 asn_debug.put_line('The deliver to person entered in IOT is currently inactive');
2128 asn_debug.put_line(' So it is cleared off');
2129 END IF;
2130 WHEN OTHERS THEN
2131 temp_cascaded_table(current_n).deliver_to_person_id := NULL;
2132
2133 IF (g_asn_debug = 'Y') THEN
2134 asn_debug.put_line('Some exception has occured');
2135 asn_debug.put_line('This exception is due to the IOT deliver to person');
2136 asn_debug.put_line('The deliver to person is optional');
2137 asn_debug.put_line('So cleared off the deliver to person');
2138 END IF;
2139 END;
2140 END IF; --}
2141 END IF; --}
2142
2143 IF (temp_cascaded_table(current_n).subinventory IS NULL) THEN
2144 temp_cascaded_table(current_n).subinventory := x_int_org_transferrec.destination_subinventory;
2145 END IF;
2146 END IF; --}
2147
2148 current_n := current_n + 1;
2149
2150 IF (g_asn_debug = 'Y') THEN
2151 asn_debug.put_line('Increment pointer by 1 ' || TO_CHAR(current_n));
2152 END IF;
2153 END IF; --}
2154
2155 asn_debug.put_line('finished processing one row in derive_qty for IOT transfers/delivers/accept/reject ');
2156 END LOOP; --}
2157
2158 IF (g_asn_debug = 'Y') THEN
2159 asn_debug.put_line('before closing cursors current_n is ' || temp_cascaded_table.COUNT);
2160 END IF;
2161
2162 IF int_org_transfer%ISOPEN THEN
2163 CLOSE int_org_transfer;
2164 END IF;
2165
2166 IF int_org_transfer_rti%ISOPEN THEN
2167 CLOSE int_org_transfer_rti;
2168 END IF;
2169
2170 IF (g_asn_debug = 'Y') THEN
2171 asn_debug.put_line('Exit derive_trans_del_line_quantity');
2172 END IF;
2173 EXCEPTION
2174 WHEN rcv_error_pkg.e_fatal_error THEN
2175 IF int_org_transfer%ISOPEN THEN
2176 CLOSE int_org_transfer;
2177 END IF;
2178
2179 IF int_org_transfer_rti%ISOPEN THEN
2180 CLOSE int_org_transfer_rti;
2181 END IF;
2182 WHEN OTHERS THEN
2183 IF int_org_transfer%ISOPEN THEN
2184 CLOSE int_org_transfer;
2185 END IF;
2186
2187 IF int_org_transfer_rti%ISOPEN THEN
2188 CLOSE int_org_transfer_rti;
2189 END IF;
2190
2191 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_unexp_error;
2192 rcv_error_pkg.set_sql_error_message('derive_trans_del_line_quantity', x_progress);
2193 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
2194 rcv_error_pkg.log_interface_error('INTERFACE_TRANSACTION_ID');
2195
2196 IF (g_asn_debug = 'Y') THEN
2197 asn_debug.put_line(TO_CHAR(n));
2198 asn_debug.put_line(SQLERRM);
2199 asn_debug.put_line('error ' || x_progress);
2200 END IF;
2201 END derive_trans_del_line_quantity;
2202
2203 /*-------- corrections --------------------------*/
2204 PROCEDURE derive_int_org_cor_line(
2205 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2206 n IN OUT NOCOPY BINARY_INTEGER,
2207 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2208 x_header_record IN rcv_roi_preprocessor.header_rec_type
2209 ) IS
2210 BEGIN
2211 IF (g_asn_debug = 'Y') THEN
2212 asn_debug.put_line('enter derive_correction_line ');
2213 END IF;
2214
2215 /* Derive the to_org_id */
2216 rcv_roi_transaction.derive_ship_to_org_info(x_cascaded_table,
2217 n,
2218 x_header_record
2219 );
2220
2221 IF (x_cascaded_table(n).unit_of_measure IS NOT NULL) THEN
2222 IF (g_asn_debug = 'Y') THEN
2223 asn_debug.put_line('X_progress ' || x_progress);
2224 END IF;
2225
2226 SELECT muom.uom_code
2230 ELSE
2227 INTO x_cascaded_table(n).uom_code
2228 FROM mtl_units_of_measure muom
2229 WHERE muom.unit_of_measure = x_cascaded_table(n).unit_of_measure;
2231 IF (g_asn_debug = 'Y') THEN
2232 asn_debug.put_line('uom_code not dereived as unit_of_measure is null');
2233 END IF;
2234 END IF;
2235
2236 x_progress := '091';
2237 rcv_roi_transaction.derive_reason_info(x_cascaded_table, n);
2238 /* Auto_transact_code is null for all these transaction types */
2239 x_cascaded_table(n).auto_transact_code := NULL;
2240 derive_int_org_cor_line_qty(x_cascaded_table,
2241 n,
2242 temp_cascaded_table
2243 );
2244 END derive_int_org_cor_line;
2245
2246 PROCEDURE derive_int_org_cor_line_qty(
2247 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2248 n IN OUT NOCOPY BINARY_INTEGER,
2249 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type
2250 ) IS
2251 x_record_count NUMBER;
2252 x_remaining_quantity NUMBER := 0;
2253 x_progress VARCHAR2(3);
2254 x_converted_trx_qty NUMBER := 0;
2255 rows_fetched NUMBER := 0;
2256 x_tolerable_qty NUMBER := 0;
2257 x_first_trans BOOLEAN := TRUE;
2258 current_n BINARY_INTEGER := 0;
2259 insert_into_table BOOLEAN := FALSE;
2260 tax_amount_factor NUMBER;
2261 lastrecord BOOLEAN := FALSE;
2262 already_allocated_qty NUMBER := 0;
2263 /* Bug# 1548597 */
2264 x_secondary_available_qty NUMBER := 0;
2265
2266 /********************************************************************/
2267 -- Bug 3584779, expanded int_org_cor_rt to pick up parent transactions of type RETURN TO CUSTOMER as well.
2268 -- also copied the restriction into the RTI cursor as well
2269 CURSOR int_org_cor_rt(
2270 v_parent_trx_id NUMBER,
2271 v_to_organization_id NUMBER
2272 ) IS
2273 SELECT rt.transaction_id rcv_transaction_id,
2274 rt.parent_transaction_id grand_parent_txn_id,
2275 rt.transaction_date transaction_date,
2276 rt.transaction_type parent_transaction_type,
2277 rt.quantity qty,
2278 rt.unit_of_measure unit_of_meas,
2279 rt.primary_unit_of_measure,
2280 rt.primary_quantity,
2281 rt.organization_id,
2282 rsl.category_id,
2283 rsl.item_description,
2284 rsl.shipment_line_id,
2285 rsl.shipment_header_id,
2286 rt.location_id,
2287 rsl.item_id,
2288 rt.deliver_to_person_id,
2289 rt.deliver_to_location_id,
2290 rt.subinventory destination_subinventory,
2291 rt.destination_type_code,
2292 rsl.ussgl_transaction_code,
2293 rt.oe_order_line_id
2294 FROM rcv_transactions rt,
2295 rcv_shipment_lines rsl
2296 WHERE rt.transaction_id = v_parent_trx_id
2297 AND rt.shipment_line_id = rsl.shipment_line_id
2298 AND rt.organization_id = NVL(v_to_organization_id, rt.organization_id)
2299 AND rt.transaction_type IN('RECEIVE', 'TRANSFER', 'ACCEPT', 'REJECT', 'RETURN TO CUSTOMER')
2300 ORDER BY rt.transaction_id;
2301
2302 CURSOR int_org_cor_rti(
2303 v_parent_interface_txn_id NUMBER
2304 ) IS
2305 SELECT rti.interface_transaction_id rcv_transaction_id,
2306 rti.parent_transaction_id grand_parent_txn_id,
2307 rti.transaction_date transaction_date,
2308 rti.transaction_type parent_transaction_type,
2309 rti.quantity qty,
2310 rti.unit_of_measure unit_of_meas,
2311 rti.primary_unit_of_measure,
2312 rti.primary_quantity,
2313 rti.to_organization_id organization_id,
2314 rti.category_id,
2315 rti.item_description,
2316 rti.shipment_line_id,
2317 rti.shipment_header_id,
2318 rti.location_id,
2319 rti.item_id,
2320 rti.deliver_to_person_id,
2321 rti.deliver_to_location_id,
2322 rti.subinventory destination_subinventory,
2323 rti.destination_type_code,
2324 rti.ussgl_transaction_code,
2325 rti.oe_order_line_id
2326 FROM rcv_transactions_interface rti
2327 WHERE interface_transaction_id = v_parent_interface_txn_id
2328 AND rti.transaction_type IN('RECEIVE', 'TRANSFER', 'ACCEPT', 'REJECT', 'RETURN TO CUSTOMER');
2329
2330 int_org_cor_rec int_org_cor_rt%ROWTYPE;
2331 x_converted_parent_trx_qty NUMBER := 0;
2335 x_temp_already_allocated_qty NUMBER := 0;
2332 -- x_remaining_qty_parent_uom NUMBER := 0;
2333 l_to_organization_id NUMBER := 0;
2334 l_transaction_type rcv_transactions.transaction_type%TYPE;
2336 derive_values_from_table BOOLEAN := FALSE;
2337 derive_values_from_rti BOOLEAN := FALSE;
2338 already_derived BOOLEAN := FALSE;
2339 l_grand_parent_trx_id rcv_transactions.parent_transaction_id%TYPE;
2340 temp_index NUMBER;
2341 l_supply_code rcv_supply.supply_type_code%TYPE;
2342
2343 /* Bug#5369121 */
2344 l_primary_uom rcv_transactions_interface.unit_of_measure%TYPE;
2345 l_transaction_uom rcv_transactions.unit_of_measure%TYPE;
2346 l_interface_quantity NUMBER;
2347 l_interface_qty_in_trx_uom NUMBER;
2348 l_item_id NUMBER;
2349 /* Bug#5369121 */
2350
2351 BEGIN
2352 IF (g_asn_debug = 'Y') THEN
2353 asn_debug.put_line('enter derive_quantity ' || x_cascaded_table(n).parent_transaction_id);
2354 END IF;
2355
2356 IF (x_cascaded_table(n).parent_transaction_id IS NOT NULL) THEN --{
2357 already_derived := TRUE;
2358 END IF; --}
2359
2360 IF ( NOT already_derived
2361 AND (x_cascaded_table(n).parent_interface_txn_id IS NULL)
2362 AND (x_cascaded_table(n).parent_source_transaction_num IS NOT NULL)) THEN --{
2363 /* This means that there can be a row in RT with src_txn_id
2364 * populated or it can be a child.
2365 */
2366 BEGIN
2367 SELECT transaction_id
2368 INTO x_cascaded_table(n).parent_transaction_id
2369 FROM rcv_transactions
2370 WHERE source_transaction_num = x_cascaded_table(n).parent_source_transaction_num;
2371
2372 already_derived := TRUE;
2373 EXCEPTION
2374 WHEN NO_DATA_FOUND THEN
2375 -- this is fine since this could be a child.
2376 NULL;
2377 END;
2378 END IF; --}
2379
2380 IF already_derived THEN --{
2381 IF (g_asn_debug = 'Y') THEN
2382 asn_debug.put_line(' open int_org_cor_rt table ' || x_cascaded_table(n).parent_transaction_id);
2383 END IF;
2384
2385 OPEN int_org_cor_rt(x_cascaded_table(n).parent_transaction_id, x_cascaded_table(n).to_organization_id);
2386 END IF; ---}
2387
2388 IF (NOT already_derived) THEN --{
2389 rcv_roi_transaction.derive_parent_id(x_cascaded_table, n);
2390
2391 /* This means that there was no error in
2392 * derive_parent_id which means that the
2393 * this is a child and need to get the values
2394 * from the rti and not from the plsql table.
2395 */
2396 IF ( x_cascaded_table(n).error_status <> 'E'
2397 AND x_cascaded_table(n).derive = 'Y'
2398 AND x_cascaded_table(n).derive_index = 0) THEN
2399 /* if derive_values_from_table is true, then we
2400 * derive the values from the pl/sql tables later
2401 * at the time when we try to see which cursor is open.
2402 * We will have x_cascaded_table(n).
2403 * parent_interface_txn_id) populated with teh
2404 * correct value.
2405 */
2406 IF (g_asn_debug = 'Y') THEN
2407 asn_debug.put_line(' open pl/sql table');
2408 END IF;
2409
2410 OPEN int_org_cor_rti(x_cascaded_table(n).parent_interface_txn_id);
2411 END IF;
2412 END IF; --}
2413
2414 /******************************************************************/
2415 --check line quanity > 0
2416 x_progress := '097';
2417
2418 IF (g_asn_debug = 'Y') THEN
2419 asn_debug.put_line('X_progress ' || x_progress);
2420 END IF;
2421
2422 IF (x_cascaded_table(n).error_status NOT IN('S', 'W')) THEN
2423 RETURN;
2424 END IF;
2425
2426 IF (x_cascaded_table(n).quantity = 0) THEN --{
2427 IF (g_asn_debug = 'Y') THEN
2428 asn_debug.put_line('Quantity is <= zero. Cascade will fail');
2429 END IF;
2430
2431 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
2432 rcv_error_pkg.set_error_message('RCV_ITEM_NO_SHIP_QTY', x_cascaded_table(n).error_message);
2433 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).item_num);
2434 rcv_error_pkg.log_interface_error('QUANTITY');
2435 END IF; --} end qty > 0 check
2436
2437 x_progress := '098';
2438
2439 IF (g_asn_debug = 'Y') THEN
2440 asn_debug.put_line('X_progress ' || x_progress);
2441 END IF;
2442
2443 IF ( x_cascaded_table(n).parent_transaction_id IS NULL
2444 AND x_cascaded_table(n).parent_interface_txn_id IS NULL) THEN
2445 IF (g_asn_debug = 'Y') THEN
2446 asn_debug.put_line('No parent transaction found ');
2447 END IF;
2448
2449 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
2450 rcv_error_pkg.set_error_message('RCV_NO_PARENT_TRANSACTION', x_cascaded_table(n).error_message);
2454 -- Copy record from main table to temp table
2451 rcv_error_pkg.log_interface_error('PARENT_TRANSACTION_ID');
2452 END IF;
2453
2455 IF (g_asn_debug = 'Y') THEN
2456 asn_debug.put_line('Copy record from main table to temp table');
2457 END IF;
2458
2459 current_n := 1;
2460 temp_cascaded_table(current_n) := x_cascaded_table(n);
2461
2462 -- Get all rows which meet this condition
2463 IF (g_asn_debug = 'Y') THEN
2464 asn_debug.put_line('Get all rows which meet this condition');
2465 asn_debug.put_line('Transaction Type = ' || x_cascaded_table(n).transaction_type);
2466 asn_debug.put_line('Auto Transact Code = ' || x_cascaded_table(n).auto_transact_code);
2467 END IF;
2468
2469 -- Assign shipped quantity to remaining quantity
2470 IF (g_asn_debug = 'Y') THEN
2471 asn_debug.put_line('Assign populated quantity to remaining quantity');
2472 asn_debug.put_line('Pointer in temp_cascade ' || TO_CHAR(current_n));
2473 END IF;
2474
2475 x_remaining_quantity := temp_cascaded_table(current_n).quantity;
2476
2477 IF (g_asn_debug = 'Y') THEN
2478 asn_debug.put_line('Have assigned the quantity');
2479 END IF;
2480
2481 -- Calculate tax_amount_factor for calculating tax_amount for
2482 -- each cascaded line
2483
2484 IF NVL(temp_cascaded_table(current_n).tax_amount, 0) <> 0 THEN
2485 tax_amount_factor := temp_cascaded_table(current_n).tax_amount / x_remaining_quantity;
2486 ELSE
2487 tax_amount_factor := 0;
2488 END IF;
2489
2490 IF (g_asn_debug = 'Y') THEN
2491 asn_debug.put_line('Tax Factor ' || TO_CHAR(tax_amount_factor));
2492 asn_debug.put_line('transaction Quantity : ' || TO_CHAR(x_remaining_quantity));
2493 END IF;
2494
2495 x_first_trans := TRUE;
2496
2497 IF (g_asn_debug = 'Y') THEN
2498 asn_debug.put_line('Before starting Cascade');
2499 END IF;
2500
2501 IF (g_asn_debug = 'Y') THEN
2502 asn_debug.put_line('Record Count = ' || x_record_count);
2503 END IF;
2504
2505 LOOP --{
2506 IF (g_asn_debug = 'Y') THEN
2507 asn_debug.put_line('Remaining Quantity ' || TO_CHAR(x_remaining_quantity));
2508 END IF;
2509
2510 /*
2511 ** Fetch the appropriate record
2512 */
2513 IF (int_org_cor_rt%ISOPEN) THEN --{
2514 IF (g_asn_debug = 'Y') THEN
2515 asn_debug.put_line(' fetch int_org_cor_rt');
2516 END IF;
2517
2518 FETCH int_org_cor_rt INTO int_org_cor_rec;
2519
2520 IF (int_org_cor_rt%NOTFOUND) THEN
2521 lastrecord := TRUE;
2522 END IF;
2523
2524 rows_fetched := int_org_cor_rt%ROWCOUNT;
2525 ELSIF(int_org_cor_rti%ISOPEN) THEN --}{
2526 IF (g_asn_debug = 'Y') THEN
2527 asn_debug.put_line(' fetch int_org_cor_rti');
2528 END IF;
2529
2530 FETCH int_org_cor_rti INTO int_org_cor_rec;
2531
2532 IF (int_org_cor_rti%NOTFOUND) THEN
2533 IF (g_asn_debug = 'Y') THEN
2534 asn_debug.put_line('correct last row');
2535 END IF;
2536
2537 lastrecord := TRUE;
2538 END IF;
2539
2540 rows_fetched := int_org_cor_rti%ROWCOUNT;
2541 ELSIF(temp_cascaded_table(current_n).derive = 'Y') THEN --}{
2542 /* GET VALUES FROM THE PLSQL TABLE */
2543 IF (g_asn_debug = 'Y') THEN
2544 asn_debug.put_line(' fetch pl/sql table');
2545 END IF;
2546
2547 temp_index := temp_cascaded_table(current_n).derive_index;
2548 int_org_cor_rec.unit_of_meas := x_cascaded_table(temp_index).unit_of_measure;
2549 int_org_cor_rec.grand_parent_txn_id := x_cascaded_table(temp_index).parent_transaction_id;
2550 int_org_cor_rec.transaction_date := x_cascaded_table(temp_index).transaction_date;
2551 int_org_cor_rec.parent_transaction_type := x_cascaded_table(temp_index).transaction_type;
2552 int_org_cor_rec.qty := x_cascaded_table(temp_index).quantity;
2553 int_org_cor_rec.primary_unit_of_measure := x_cascaded_table(temp_index).primary_unit_of_measure;
2554 int_org_cor_rec.primary_quantity := x_cascaded_table(temp_index).primary_quantity;
2555 int_org_cor_rec.organization_id := x_cascaded_table(temp_index).to_organization_id;
2556 int_org_cor_rec.category_id := x_cascaded_table(temp_index).category_id;
2557 int_org_cor_rec.item_description := x_cascaded_table(temp_index).item_description;
2558 int_org_cor_rec.location_id := x_cascaded_table(temp_index).location_id;
2559 int_org_cor_rec.item_id := x_cascaded_table(temp_index).item_id;
2560 int_org_cor_rec.shipment_line_id := x_cascaded_table(temp_index).shipment_line_id;
2561 int_org_cor_rec.deliver_to_person_id := x_cascaded_table(temp_index).deliver_to_person_id;
2562 int_org_cor_rec.deliver_to_location_id := x_cascaded_table(temp_index).deliver_to_location_id;
2563 int_org_cor_rec.destination_subinventory := x_cascaded_table(temp_index).subinventory;
2567 * the correct variables.
2564 int_org_cor_rec.destination_type_code := x_cascaded_table(temp_index).destination_type_code;
2565 int_org_cor_rec.ussgl_transaction_code := x_cascaded_table(temp_index).ussgl_transaction_code;
2566 /* Also fetch parent transaction type and grand parent trx id into
2568 */
2569 rows_fetched := 1;
2570 lastrecord := TRUE;
2571 END IF; --}
2572 --x_remaining_quantity:= temp_cascaded_table(current_n).quantity;
2573
2574 IF (g_asn_debug = 'Y') THEN
2575 asn_debug.put_line('Correct Rows fetched ' || TO_CHAR(rows_fetched));
2576 asn_debug.put_line('correct remainaing qty ' || x_remaining_quantity);
2577 END IF;
2578
2579 IF (lastrecord) THEN --{
2580 IF (g_asn_debug = 'Y') THEN
2581 asn_debug.put_line('Hit exit condition');
2582 END IF;
2583
2584 IF NOT x_first_trans THEN
2585 -- x_first_trans has been reset which means some cascade has
2586 -- happened. Otherwise current_n = 1
2587 IF (g_asn_debug = 'Y') THEN
2588 asn_debug.put_line('current_n before is ' || current_n);
2589 END IF;
2590
2591 current_n := current_n - 1;
2592 END IF;
2593
2594 -- do the tolerance act here
2595 IF (g_asn_debug = 'Y') THEN
2596 asn_debug.put_line('Temp table pointer ' || TO_CHAR(current_n));
2597 asn_debug.put_line('Check which condition has occured');
2598 END IF;
2599
2600 -- lastrecord...we have run out of rows and we still have quantity to allocate
2601 /* Do abs(x_remaining_quantity) since it can be a negative
2602 * or positive correction.
2603 */
2604 IF ABS(x_remaining_quantity) > 0 THEN --{
2605 IF (g_asn_debug = 'Y') THEN
2606 asn_debug.put_line('There is quantity remaining ');
2607 asn_debug.put_line('tolerable quantity now in plsql table ' || temp_cascaded_table(current_n).quantity);
2608 asn_debug.put_line('rows_fetched ' || rows_fetched);
2609 END IF;
2610
2611 IF NOT x_first_trans THEN
2612 asn_debug.put_line('not first txn');
2613 END IF;
2614
2615 IF rows_fetched > 0
2616 AND NOT x_first_trans THEN --{
2617 IF (g_asn_debug = 'Y') THEN
2618 asn_debug.put_line(' inside transaction_type ' || int_org_cor_rec.parent_transaction_type);
2619 END IF;
2620
2621 IF (SIGN(temp_cascaded_table(current_n).quantity) IN(-1, -1)) THEN --{
2622 /* for correct,accept an reject type we dont have the
2623 * tolerance check. Hence error out.
2624 * We cannot correct quantities more than that was received.
2625 */
2626 IF (g_asn_debug = 'Y') THEN
2627 asn_debug.put_line(' in correct extra Quantity ' || TO_CHAR(x_remaining_quantity));
2628 END IF;
2629
2630 IF (g_asn_debug = 'Y') THEN
2631 asn_debug.put_line('delete the temp table ');
2632 END IF;
2633
2634 IF temp_cascaded_table.COUNT > 0 THEN
2635 FOR i IN 1 .. temp_cascaded_table.COUNT LOOP
2636 temp_cascaded_table.DELETE(i);
2637 END LOOP;
2638 END IF;
2639
2640 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
2641 rcv_error_pkg.set_error_message('RCV_SHIP_QTY_OVER_TOLERANCE', x_cascaded_table(n).error_message);
2642 rcv_error_pkg.set_token('QTY_A', x_cascaded_table(n).quantity);
2643 rcv_error_pkg.set_token('QTY_B', x_cascaded_table(n).quantity - x_remaining_quantity);
2644 rcv_error_pkg.log_interface_error('QUANTITY');
2645 END IF; --}ends check for a -ve correction
2646 ELSE --}{ else for rows fetched = 0 OR x_first_trans = true
2647 IF rows_fetched = 0 THEN
2648 IF (g_asn_debug = 'Y') THEN
2649 asn_debug.put_line('No rows were retrieved from cursor.');
2650 END IF;
2651 ELSIF x_first_trans THEN
2652 IF (g_asn_debug = 'Y') THEN
2653 asn_debug.put_line('No rows were cascaded');
2654 END IF;
2655 END IF;
2656
2657 IF (x_cascaded_table(n).parent_transaction_id IS NOT NULL) THEN --{
2658 /* Give an error just like it is done for the
2659 * the receipt transaction;
2660 */
2661 SELECT MAX(rsup.supply_type_code),
2662 MAX(rt.transaction_type)
2663 INTO l_supply_code,
2664 l_transaction_type
2665 FROM rcv_transactions rt,
2666 rcv_supply rsup
2667 WHERE rt.transaction_id = temp_cascaded_table(current_n).parent_transaction_id
2668 AND rsup.rcv_transaction_id = rt.transaction_id;
2669
2670 IF l_supply_code <> 'RECEIVING' THEN
2674 END IF;
2671 rcv_error_pkg.set_error_message('RCV_ITEM_NO_SHIP_QTY', x_cascaded_table(n).error_message);
2672 ELSIF l_transaction_type = 'UNORDERED' THEN
2673 rcv_error_pkg.set_error_message('RCV_INVALID_TRANSACTION_TYPE', x_cascaded_table(n).error_message);
2675 ELSE
2676 rcv_error_pkg.set_error_message('RCV_INVALID_TRANSACTION_TYPE', x_cascaded_table(n).error_message);
2677 END IF; --}
2678
2679 -- Delete the temp_cascaded_table just to be sure
2680
2681 IF temp_cascaded_table.COUNT > 0 THEN
2682 FOR i IN 1 .. temp_cascaded_table.COUNT LOOP
2683 temp_cascaded_table.DELETE(i);
2684 END LOOP;
2685 END IF;
2686
2687 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
2688 rcv_error_pkg.log_interface_error('PARENT_TRANSACTION_ID');
2689 END IF; --} end else for rows fetched = 0 OR x_first_trans = true
2690 ELSE -- }{ this is the else for whether remaining qty is not 0
2691 IF (g_asn_debug = 'Y') THEN
2692 asn_debug.put_line('Remaining UOM quantity is zero ' || TO_CHAR(x_remaining_quantity));
2693 asn_debug.put_line('Return the cascaded rows back to the calling procedure');
2694 END IF;
2695 END IF; --} ends the check for whether last record has been reached
2696
2697 -- close cursors
2698
2699 IF (g_asn_debug = 'Y') THEN
2700 asn_debug.put_line('Close cursors ' || current_n);
2701 END IF;
2702
2703 IF int_org_cor_rt%ISOPEN THEN
2704 CLOSE int_org_cor_rt;
2705 END IF;
2706
2707 IF int_org_cor_rti%ISOPEN THEN
2708 CLOSE int_org_cor_rti;
2709 END IF;
2710
2711 IF (g_asn_debug = 'Y') THEN
2712 asn_debug.put_line('before exit current_n is ' || current_n);
2713 END IF;
2714
2715 EXIT;
2716 END IF; --} matches the condition of lastrecord has been reached
2717
2718 IF (g_asn_debug = 'Y') THEN
2719 asn_debug.put_line(' the iot rec uom is ' || int_org_cor_rec.unit_of_meas);
2720 asn_debug.put_line(' the iot rec primary uom is ' || int_org_cor_rec.primary_unit_of_measure);
2721 asn_debug.put_line(' Entering qty calculateion for correct');
2722 END IF;
2723
2724 -- bug fix 5269121 :it is wrong to
2725 --copy the parents unit of measure into the childs unit of measure, as this will be the one
2726 --which will be finally populated into RT for the child record (correction record).
2727 --temp_cascaded_table(current_n).unit_of_measure := int_org_cor_rec.unit_of_meas;
2728
2729 IF ( x_first_trans
2730 AND temp_cascaded_table(current_n).item_id IS NULL) THEN
2731 temp_cascaded_table(current_n).item_id := int_org_cor_rec.item_id;
2732 temp_cascaded_table(current_n).primary_unit_of_measure := int_org_cor_rec.primary_unit_of_measure;
2733 END IF;
2734
2735 insert_into_table := FALSE;
2736 already_allocated_qty := 0;
2737 -- need to find out if the parent/grandparent are in rti
2738 rcv_roi_transaction.get_interface_available_qty(temp_cascaded_table,
2739 current_n,
2740 x_converted_parent_trx_qty
2741 );
2742
2743 IF (x_converted_parent_trx_qty = 0) THEN --{
2744 IF (g_asn_debug = 'Y') THEN
2745 asn_debug.put_line('calling Correct get_available_qty ' || int_org_cor_rec.parent_transaction_type);
2746 END IF;
2747
2748 -- this is a negative correction
2749 IF (SIGN(temp_cascaded_table(current_n).quantity) = -1) THEN --{
2750 rcv_quantities_s.get_available_quantity('CORRECT',
2751 int_org_cor_rec.rcv_transaction_id,
2752 temp_cascaded_table(current_n).receipt_source_code,
2753 int_org_cor_rec.parent_transaction_type,
2754 NULL,
2755 'NEGATIVE',
2756 x_converted_parent_trx_qty,
2757 x_tolerable_qty,
2758 int_org_cor_rec.unit_of_meas,
2759 x_secondary_available_qty
2760 );
2761
2762 /* Bug#5369121 - START */
2763 IF (g_asn_debug = 'Y') THEN
2764 asn_debug.put_line('x_converted_parent_trx_qty:' || x_converted_parent_trx_qty);
2765 asn_debug.put_line('transaction_quantity:' || temp_cascaded_table(current_n).quantity);
2766 END IF;
2767
2768 IF (x_converted_parent_trx_qty < ABS(temp_cascaded_table(current_n).quantity)) THEN
2769
2770 SELECT rt.unit_of_measure,
2771 rsl.item_id,
2772 rt.primary_unit_of_measure
2776 FROM rcv_transactions rt,
2773 INTO l_transaction_uom,
2774 l_item_id,
2775 l_primary_uom
2777 rcv_shipment_lines rsl
2778 WHERE rsl.shipment_line_id = rt.shipment_line_id
2779 AND rt.transaction_id = int_org_cor_rec.rcv_transaction_id;
2780
2781 SELECT NVL(SUM(interface_transaction_qty),0)
2782 INTO l_interface_quantity
2783 FROM rcv_transactions_interface
2784 WHERE (transaction_status_code = 'PENDING'
2785 AND processing_status_code <> 'ERROR')
2786 AND group_id = temp_cascaded_table(current_n).group_id
2787 AND transaction_type = 'CORRECT'
2788 AND parent_transaction_id IN ( SELECT transaction_id
2789 FROM rcv_transactions
2790 WHERE parent_transaction_id = int_org_cor_rec.rcv_transaction_id);
2791
2792 IF (l_interface_quantity = 0) THEN
2793
2794 /*
2795 ** There is no unprocessed quantity. Simply set the
2796 ** x_interface_qty_in_trx_uom to 0. There is no need for uom
2797 ** conversion.
2798 */
2799
2800 l_interface_qty_in_trx_uom := 0;
2801
2802 ELSE
2803
2804 /*
2805 ** There is unprocessed quantity. Convert it to the transaction uom
2806 ** so that the available quantity can be calculated in the trx uom
2807 */
2808
2809 IF (g_asn_debug = 'Y') THEN
2810 asn_debug.put_line('Before uom_convert:');
2811 asn_debug.put_line('l_interface_quantity' || l_interface_quantity);
2812 asn_debug.put_line('l_primary_uom' || l_primary_uom);
2813 asn_debug.put_line('l_transaction_uom' || l_transaction_uom);
2814 asn_debug.put_line('l_item_id' || l_item_id);
2815 END IF;
2816
2817 po_uom_s.uom_convert(l_interface_quantity, l_primary_uom, l_item_id,
2818 l_transaction_uom, l_interface_qty_in_trx_uom);
2819
2820 END IF;
2821
2822 x_converted_parent_trx_qty := x_converted_parent_trx_qty - l_interface_qty_in_trx_uom;
2823
2824 IF (g_asn_debug = 'Y') THEN
2825 asn_debug.put_line('x_converted_parent_trx_qty:' || x_converted_parent_trx_qty);
2826 END IF;
2827
2828 END IF;
2829 /* Bug#5369121 - END */
2830 ELSE --}{ this is a positive correction
2831 IF (int_org_cor_rec.parent_transaction_type NOT IN('RECEIVE')) THEN
2832 l_grand_parent_trx_id := int_org_cor_rec.grand_parent_txn_id;
2833 ELSIF(int_org_cor_rec.parent_transaction_type IN('RECEIVE')) THEN
2834 /* If parent is RECEIVE, grand parent is the source doc */
2835 IF (temp_cascaded_table(current_n).parent_transaction_id IS NOT NULL) THEN
2836 IF temp_cascaded_table(current_n).source_document_code = 'RMA' THEN
2837 l_grand_parent_trx_id := int_org_cor_rec.oe_order_line_id;
2838 ELSE
2839 l_grand_parent_trx_id := int_org_cor_rec.shipment_line_id;
2840 END IF;
2841 ELSIF(temp_cascaded_table(current_n).parent_interface_txn_id IS NOT NULL) THEN
2842 l_grand_parent_trx_id := int_org_cor_rec.shipment_line_id;
2843 END IF;
2844 END IF;
2845
2846 IF (g_asn_debug = 'Y') THEN
2847 asn_debug.put_line('grand parent trx id' || l_grand_parent_trx_id);
2848 asn_debug.put_line('rcv_transaction id' || int_org_cor_rec.rcv_transaction_id);
2849 asn_debug.put_line('parent txn type' || int_org_cor_rec.parent_transaction_type);
2850 END IF;
2851
2852 IF (l_grand_parent_trx_id IS NOT NULL) THEN
2853 rcv_quantities_s.get_available_quantity('CORRECT',
2854 int_org_cor_rec.rcv_transaction_id,
2855 temp_cascaded_table(current_n).receipt_source_code,
2856 int_org_cor_rec.parent_transaction_type,
2857 l_grand_parent_trx_id,
2858 'POSITIVE',
2859 x_converted_parent_trx_qty,
2860 x_tolerable_qty,
2861 int_org_cor_rec.unit_of_meas,
2862 x_secondary_available_qty
2863 );
2864 END IF;
2865 END IF; --}
2866
2867 IF (g_asn_debug = 'Y') THEN
2871
2868 asn_debug.put_line('qty from GET_AVAILABLE_QUANTITY for corrections is ' || x_converted_parent_trx_qty);
2869 END IF;
2870 END IF; --} matches the parent trx qty = 0
2872 IF (x_remaining_quantity = 0) THEN
2873 IF (g_asn_debug = 'Y') THEN
2874 asn_debug.put_line(' correct Need an error message in the interface tables');
2875 END IF;
2876 ELSE
2877 /* Converted successfully and have some quantity on which we can act */
2878 IF (g_asn_debug = 'Y') THEN
2879 asn_debug.put_line('Converted trx qty that is available ' || x_converted_parent_trx_qty);
2880 asn_debug.put_line('Remaining qty in parents uom that is available ' || x_remaining_quantity);
2881 END IF;
2882
2883 IF ( (rows_fetched = x_record_count)
2884 AND (SIGN(temp_cascaded_table(current_n).quantity) = 1)) THEN --{
2885 x_converted_trx_qty := x_tolerable_qty;
2886
2887 IF (g_asn_debug = 'Y') THEN
2888 asn_debug.put_line('Last Row : ' || TO_CHAR(x_converted_trx_qty));
2889 END IF;
2890 END IF; --}
2891
2892 IF (x_converted_parent_trx_qty > 0) THEN --{
2893 /* Compare with abs(x_remaining_qty) here since we want
2894 * to make sure that the qty we have is greater than
2895 * the available qty irrespective of whether this is
2896 * positive or negative correction.
2897 */
2898 IF (x_converted_parent_trx_qty < ABS(x_remaining_quantity)) THEN --{
2899 /* Total quantity available to correct is less than the qty
2900 * that the user wants to correct. Hence we would error out but
2901 * to keep the old code we will get the remaining code here and
2902 * error out later.
2903 */
2904 x_remaining_quantity := x_remaining_quantity - SIGN(temp_cascaded_table(current_n).quantity) * x_converted_parent_trx_qty;
2905 x_converted_parent_trx_qty := SIGN(temp_cascaded_table(current_n).quantity) * x_converted_parent_trx_qty;
2906
2907 IF (g_asn_debug = 'Y') THEN
2908 asn_debug.put_line('remaning qty after allocation is : ' || TO_CHAR(x_remaining_quantity));
2909 END IF;
2910
2911 insert_into_table := TRUE;
2912 ELSE --}{
2913 IF (g_asn_debug = 'Y') THEN
2914 asn_debug.put_line('We are in >= Qty branch ');
2915 END IF;
2916
2917 x_converted_parent_trx_qty := x_remaining_quantity;
2918 insert_into_table := TRUE;
2919 x_remaining_quantity := 0;
2920 END IF; --} /* if (x_converted_parent_trx_qty < x_remaining_quantity) then */
2921 ELSE /* x_converted_parent_trx_qty >0 */ --}{
2922 -- so that the row can be used based on qty tolerance checks
2923 IF (g_asn_debug = 'Y') THEN
2924 asn_debug.put_line('Quantity is less then 0 but last record');
2925 END IF;
2926
2927 insert_into_table := TRUE;
2928 x_converted_trx_qty := 0;
2929 END IF; /*x_converted_parent_trx_qty >0 */ --}
2930 END IF;
2931
2932 /* Converted qty successfully and we have some quantity on which we can act */
2933 IF (g_asn_debug = 'Y') THEN
2934 asn_debug.put_line('Txn qty in terms of the parents uom is ' || x_converted_parent_trx_qty);
2935 END IF;
2936
2937 IF insert_into_table THEN --{ --start
2938 IF (x_first_trans) THEN --{
2939 IF (g_asn_debug = 'Y') THEN
2940 asn_debug.put_line('First Time ' || TO_CHAR(current_n));
2941 END IF;
2942
2943 x_first_trans := FALSE;
2944 ELSE --}{
2945 IF (g_asn_debug = 'Y') THEN
2946 asn_debug.put_line('Next Time ' || TO_CHAR(current_n));
2947 END IF;
2948
2949 temp_cascaded_table(current_n) := temp_cascaded_table(current_n - 1);
2950 END IF; --}
2951
2952 IF (g_asn_debug = 'Y') THEN
2953 asn_debug.put_line('primary uom before ' || temp_cascaded_table(current_n).primary_unit_of_measure);
2954 asn_debug.put_line('normal uom is ' || int_org_cor_rec.unit_of_meas);
2955 END IF;
2956
2957 temp_cascaded_table(current_n).primary_unit_of_measure := int_org_cor_rec.primary_unit_of_measure;
2958 temp_cascaded_table(current_n).quantity := x_converted_parent_trx_qty;
2959 temp_cascaded_table(current_n).shipment_line_id := int_org_cor_rec.shipment_line_id;
2960
2961 IF (temp_cascaded_table(current_n).primary_unit_of_measure <> int_org_cor_rec.unit_of_meas) THEN
2962 IF (g_asn_debug = 'Y') THEN
2963 asn_debug.put_line('primary uoms not the same and need to convert quantity');
2964 END IF;
2965
2966 temp_cascaded_table(current_n).primary_quantity := rcv_roi_transaction.convert_into_correct_qty(x_converted_parent_trx_qty,
2967 int_org_cor_rec.unit_of_meas,
2968 temp_cascaded_table(current_n).item_id,
2972 temp_cascaded_table(current_n).primary_quantity := x_converted_parent_trx_qty;
2969 temp_cascaded_table(current_n).primary_unit_of_measure
2970 );
2971 ELSE
2973 END IF;
2974
2975 IF (g_asn_debug = 'Y') THEN
2976 asn_debug.put_line('txn qty in primary uom is ' || temp_cascaded_table(current_n).primary_quantity);
2977 END IF;
2978
2979 current_n := current_n + 1;
2980
2981 IF (g_asn_debug = 'Y') THEN
2982 asn_debug.put_line('Increment pointer by 1 ' || TO_CHAR(current_n));
2983 END IF;
2984 END IF; --}
2985
2986 IF (g_asn_debug = 'Y') THEN
2987 asn_debug.put_line('finished assigning line qty for one row in cursor for IOT corrections ');
2988 END IF;
2989 END LOOP; --}
2990
2991 IF (g_asn_debug = 'Y') THEN
2992 asn_debug.put_line('before closing cursors current_n is ' || temp_cascaded_table.COUNT);
2993 END IF;
2994
2995 IF int_org_cor_rt%ISOPEN THEN
2996 CLOSE int_org_cor_rt;
2997 END IF;
2998
2999 IF int_org_cor_rti%ISOPEN THEN
3000 CLOSE int_org_cor_rti;
3001 END IF;
3002
3003 IF (g_asn_debug = 'Y') THEN
3004 asn_debug.put_line('Exit explode_line_quantity');
3005 END IF;
3006 EXCEPTION
3007 WHEN rcv_error_pkg.e_fatal_error THEN
3008 IF int_org_cor_rt%ISOPEN THEN
3009 CLOSE int_org_cor_rt;
3010 END IF;
3011
3012 IF int_org_cor_rti%ISOPEN THEN
3013 CLOSE int_org_cor_rti;
3014 END IF;
3015 WHEN OTHERS THEN
3016 IF int_org_cor_rt%ISOPEN THEN
3017 CLOSE int_org_cor_rt;
3018 END IF;
3019
3020 IF int_org_cor_rti%ISOPEN THEN
3021 CLOSE int_org_cor_rti;
3022 END IF;
3023
3024 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_unexp_error;
3025 rcv_error_pkg.set_sql_error_message('derive_int_org_cor_line_qty', x_progress);
3026 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
3027 rcv_error_pkg.log_interface_error('INTERFACE_TRANSACTION_ID');
3028
3029 IF (g_asn_debug = 'Y') THEN
3030 asn_debug.put_line(TO_CHAR(n));
3031 asn_debug.put_line(SQLERRM);
3032 asn_debug.put_line('error ' || x_progress);
3033 END IF;
3034 END derive_int_org_cor_line_qty;
3035
3036 PROCEDURE default_int_org_cor_line(
3037 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
3038 n IN BINARY_INTEGER
3039 ) IS
3040 p_trx_record rcv_roi_header_common.common_default_record_type;
3041
3042 CURSOR int_org_cor_rt(
3043 v_parent_trx_id NUMBER
3044 ) IS
3045 SELECT rsl.item_description,
3046 rt.location_id loc_id,
3047 rt.organization_id,
3048 rt.routing_header_id,
3049 rt.destination_type_code,
3050 rt.destination_context,
3051 rt.inspection_status_code,
3052 rt.currency_code,
3053 rt.currency_conversion_rate,
3054 rt.currency_conversion_type,
3055 rt.currency_conversion_date,
3056 rt.location_id,
3057 rt.shipment_header_id,
3058 rt.shipment_line_id,
3059 rsl.category_id,
3060 rt.deliver_to_person_id,
3061 rt.deliver_to_location_id,
3062 rt.subinventory,
3063 rt.lpn_id,
3064 rt.transfer_lpn_id,
3065 rt.transaction_type
3066 FROM rcv_transactions rt,
3067 rcv_shipment_lines rsl
3068 WHERE rt.transaction_id = v_parent_trx_id
3069 AND rt.transaction_type IN('RECEIVE', 'TRANSFER', 'ACCEPT', 'REJECT')
3070 AND rt.shipment_line_id = rsl.shipment_line_id
3071 ORDER BY rt.transaction_id;
3072
3073 CURSOR int_org_cor_rti(
3074 v_parent_inter_trx_id NUMBER
3075 ) IS
3076 SELECT rti.item_description,
3077 rti.location_id loc_id,
3078 rti.to_organization_id organization_id,
3079 rti.routing_header_id,
3080 rti.destination_type_code,
3081 rti.destination_context,
3082 rti.inspection_status_code,
3083 rti.currency_code,
3084 rti.currency_conversion_rate,
3085 rti.currency_conversion_type,
3086 rti.currency_conversion_date,
3087 rti.location_id,
3088 rti.shipment_header_id,
3089 rti.shipment_line_id,
3090 rti.category_id,
3091 rti.deliver_to_person_id,
3092 rti.deliver_to_location_id,
3093 rti.subinventory,
3094 rti.lpn_id,
3095 rti.transfer_lpn_id,
3096 rti.transaction_type
3097 FROM rcv_transactions_interface rti
3101 CURSOR int_org_cor_rsl(
3098 WHERE interface_transaction_id = v_parent_inter_trx_id;
3099
3100 --bug 3704623
3102 p_shipment_line_id rcv_shipment_lines.shipment_line_id%TYPE
3103 ) IS
3104 SELECT to_organization_id,
3105 from_organization_id
3106 FROM rcv_shipment_lines
3107 WHERE shipment_line_id = p_shipment_line_id;
3108
3109 x_from_organization_id rcv_shipment_lines.from_organization_id%TYPE;
3110 x_to_organization_id rcv_shipment_lines.to_organization_id%TYPE;
3111 default_int_org_cor_rt_info int_org_cor_rt%ROWTYPE;
3112 x_progress VARCHAR2(3);
3113 temp_index NUMBER;
3114 lpn_error NUMBER := 0;
3115 BEGIN
3116 p_trx_record.destination_type_code := x_cascaded_table(n).destination_type_code;
3117 p_trx_record.transaction_type := x_cascaded_table(n).transaction_type;
3118 p_trx_record.processing_mode_code := x_cascaded_table(n).processing_mode_code;
3119 p_trx_record.processing_status_code := x_cascaded_table(n).processing_status_code;
3120 p_trx_record.transaction_status_code := x_cascaded_table(n).transaction_status_code;
3121 p_trx_record.auto_transact_code := x_cascaded_table(n).auto_transact_code;
3122 rcv_roi_header_common.commondefaultcode(p_trx_record);
3123 x_cascaded_table(n).destination_type_code := p_trx_record.destination_type_code;
3124 x_cascaded_table(n).transaction_type := p_trx_record.transaction_type;
3125 x_cascaded_table(n).processing_mode_code := p_trx_record.processing_mode_code;
3126 x_cascaded_table(n).processing_status_code := p_trx_record.processing_status_code;
3127 x_cascaded_table(n).transaction_status_code := p_trx_record.transaction_status_code;
3128 x_cascaded_table(n).auto_transact_code := p_trx_record.auto_transact_code;
3129
3130 IF (g_asn_debug = 'Y') THEN
3131 asn_debug.put_line('Enter Default for IOT Corrections');
3132 END IF;
3133
3134 IF (x_cascaded_table(n).derive = 'Y') THEN --{
3135 IF (x_cascaded_table(n).derive_index <> 0) THEN --{
3136 NULL;
3137 /* Get the values from pl/sql table */
3138 temp_index := x_cascaded_table(n).derive_index;
3139 x_cascaded_table(n).item_description := x_cascaded_table(temp_index).item_description;
3140 x_cascaded_table(n).destination_type_code := x_cascaded_table(temp_index).destination_type_code;
3141 x_cascaded_table(n).destination_context := x_cascaded_table(temp_index).destination_context;
3142 x_cascaded_table(n).inspection_status_code := x_cascaded_table(temp_index).inspection_status_code;
3143 x_cascaded_table(n).to_organization_id := x_cascaded_table(temp_index).to_organization_id;
3144 x_cascaded_table(n).from_organization_id := x_cascaded_table(temp_index).from_organization_id;
3145 x_cascaded_table(n).routing_header_id := x_cascaded_table(temp_index).routing_header_id;
3146 x_cascaded_table(n).currency_code := x_cascaded_table(temp_index).currency_code;
3147 x_cascaded_table(n).currency_conversion_rate := x_cascaded_table(temp_index).currency_conversion_rate;
3148 x_cascaded_table(n).currency_conversion_type := x_cascaded_table(temp_index).currency_conversion_type;
3149 x_cascaded_table(n).currency_conversion_date := x_cascaded_table(temp_index).currency_conversion_date;
3150 x_cascaded_table(n).shipment_header_id := x_cascaded_table(temp_index).shipment_header_id;
3151 x_cascaded_table(n).shipment_line_id := x_cascaded_table(temp_index).shipment_line_id;
3152 x_cascaded_table(n).category_id := x_cascaded_table(temp_index).category_id;
3153
3154 -- LPN defaulting
3155 IF (x_cascaded_table(n).quantity > 0) THEN
3156 -- for +ve corrections :
3157 -- we are defaulting the parent's from_lpn to child's from_lpn
3158 -- and parent's to_lpn to child's to_lpn
3159 -- only if those fields are null
3160 IF (x_cascaded_table(n).lpn_id IS NULL) THEN
3161 x_cascaded_table(n).lpn_id := x_cascaded_table(temp_index).lpn_id;
3162
3163 IF (x_cascaded_table(n).lpn_id IS NOT NULL) THEN
3164 lpn_error := 1;
3165 END IF;
3166 END IF;
3167
3168 IF (x_cascaded_table(n).transfer_lpn_id IS NULL) THEN
3169 x_cascaded_table(n).transfer_lpn_id := x_cascaded_table(temp_index).transfer_lpn_id;
3170
3171 IF (x_cascaded_table(n).transfer_lpn_id IS NOT NULL) THEN
3172 lpn_error := 1;
3173 END IF;
3174 END IF;
3175
3176 --insert warning message into po_interface_errors
3177 IF (lpn_error = 1) THEN
3178 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_warning;
3179 rcv_error_pkg.set_error_message('RCV_LPN_UNPACK_WARNING', x_cascaded_table(n).error_message);
3180 rcv_error_pkg.set_token('LPN_ID', x_cascaded_table(n).lpn_id);
3181 rcv_error_pkg.log_interface_warning('LPN_ID');
3182 END IF;
3183 ELSE -- its a -ve correction
3184 -- for -ve corrections :
3188 IF (x_cascaded_table(n).lpn_id IS NULL) THEN
3185 -- we are defaulting the parent's from_lpn to child's to_lpn
3186 -- and parent's to_lpn to child's from_lpn
3187 -- only if those fields are null
3189 x_cascaded_table(n).lpn_id := x_cascaded_table(temp_index).transfer_lpn_id;
3190
3191 IF (x_cascaded_table(n).lpn_id IS NOT NULL) THEN
3192 lpn_error := 1;
3193 END IF;
3194 END IF;
3195
3196 IF (x_cascaded_table(n).transfer_lpn_id IS NULL) THEN
3197 x_cascaded_table(n).transfer_lpn_id := x_cascaded_table(temp_index).lpn_id;
3198
3199 IF (x_cascaded_table(n).transfer_lpn_id IS NOT NULL) THEN
3200 lpn_error := 1;
3201 END IF;
3202 END IF;
3203
3204 --insert warning message into po_interface_errors
3205 IF (lpn_error = 1) THEN
3206 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_warning;
3207 rcv_error_pkg.set_error_message('RCV_LPN_UNPACK_WARNING', x_cascaded_table(n).error_message);
3208 rcv_error_pkg.set_token('LPN_ID', x_cascaded_table(n).lpn_id);
3209 rcv_error_pkg.log_interface_warning('LPN_ID');
3210 END IF;
3211 END IF;
3212
3213 x_cascaded_table(n).location_id := x_cascaded_table(temp_index).location_id;
3214 x_cascaded_table(n).deliver_to_person_id := x_cascaded_table(temp_index).deliver_to_person_id;
3215 x_cascaded_table(n).deliver_to_location_id := x_cascaded_table(temp_index).deliver_to_location_id;
3216 x_cascaded_table(n).subinventory := x_cascaded_table(temp_index).subinventory;
3217 ELSE --} {
3218 IF (g_asn_debug = 'Y') THEN
3219 asn_debug.put_line('open cursor Default');
3220 END IF;
3221
3222 OPEN int_org_cor_rti(x_cascaded_table(n).parent_interface_txn_id);
3223 END IF; --}
3224 ELSE -- } {
3225 OPEN int_org_cor_rt(x_cascaded_table(n).parent_transaction_id);
3226 END IF; --}
3227
3228 IF (int_org_cor_rt%ISOPEN) THEN
3229 IF (g_asn_debug = 'Y') THEN
3230 asn_debug.put_line('fetch cursor Default ' || x_cascaded_table(n).parent_transaction_id);
3231 END IF;
3232
3233 FETCH int_org_cor_rt INTO default_int_org_cor_rt_info;
3234 ELSIF(int_org_cor_rti%ISOPEN) THEN
3235 FETCH int_org_cor_rti INTO default_int_org_cor_rt_info;
3236 END IF;
3237
3238 IF ( ( int_org_cor_rt%ISOPEN
3239 AND int_org_cor_rt%FOUND)
3240 OR ( int_org_cor_rti%ISOPEN
3241 AND int_org_cor_rti%FOUND)) THEN --{
3242 -- bug 3704623, if a shipment line is specified, use the shipment line's to_org and from_org id's
3243 IF ( default_int_org_cor_rt_info.shipment_line_id IS NOT NULL
3244 AND default_int_org_cor_rt_info.transaction_type = 'RECEIVE') THEN
3245 OPEN int_org_cor_rsl(default_int_org_cor_rt_info.shipment_line_id);
3246 FETCH int_org_cor_rsl INTO x_to_organization_id,
3247 x_from_organization_id;
3248 CLOSE int_org_cor_rsl;
3249 END IF;
3250
3251 IF (g_asn_debug = 'Y') THEN --{
3252 asn_debug.put_line('Defaulting Cor item_description' || default_int_org_cor_rt_info.item_description);
3253 asn_debug.put_line('Defaulting Cor location_id' || default_int_org_cor_rt_info.loc_id);
3254 asn_debug.put_line('Defaulting Cor to organization_id' || NVL(x_to_organization_id, default_int_org_cor_rt_info.organization_id));
3255 asn_debug.put_line('Defaulting Cor from organization_id' || NVL(x_from_organization_id, default_int_org_cor_rt_info.organization_id));
3256 asn_debug.put_line('Defaulting Cor routing_header_id' || default_int_org_cor_rt_info.routing_header_id);
3257 asn_debug.put_line('Defaulting Cor currency_code' || default_int_org_cor_rt_info.currency_code);
3258 asn_debug.put_line('Defaulting Cor currency_conversion_rate' || default_int_org_cor_rt_info.currency_conversion_rate);
3259 asn_debug.put_line('Defaulting Cor currency_conversion_type' || default_int_org_cor_rt_info.currency_conversion_type);
3260 asn_debug.put_line('Defaulting cor currency_conversion_date' || default_int_org_cor_rt_info.currency_conversion_date);
3261 asn_debug.put_line('Defaulting cor shipment_header_id' || default_int_org_cor_rt_info.shipment_header_id);
3262 asn_debug.put_line('Defaulting cor shipment_line_id' || default_int_org_cor_rt_info.shipment_line_id);
3263 asn_debug.put_line('Defaulting cor category_id' || default_int_org_cor_rt_info.category_id);
3264 asn_debug.put_line('Defaulting cor DELIVER_TO_PERSON_ID' || default_int_org_cor_rt_info.deliver_to_person_id);
3268
3265 asn_debug.put_line('Defaulting Corr DELIVER_TO_LOCATION_ID' || default_int_org_cor_rt_info.deliver_to_location_id);
3266 asn_debug.put_line('Defaulting Cor subinv' || default_int_org_cor_rt_info.subinventory);
3267 END IF; --}
3269 x_cascaded_table(n).item_description := default_int_org_cor_rt_info.item_description;
3270 x_cascaded_table(n).destination_type_code := default_int_org_cor_rt_info.destination_type_code;
3271 x_cascaded_table(n).destination_context := default_int_org_cor_rt_info.destination_context;
3272 x_cascaded_table(n).inspection_status_code := default_int_org_cor_rt_info.inspection_status_code;
3273 x_cascaded_table(n).to_organization_id := NVL(x_to_organization_id, default_int_org_cor_rt_info.organization_id);
3274 x_cascaded_table(n).from_organization_id := NVL(x_from_organization_id, default_int_org_cor_rt_info.organization_id);
3275 x_cascaded_table(n).routing_header_id := default_int_org_cor_rt_info.routing_header_id;
3276 x_cascaded_table(n).currency_code := default_int_org_cor_rt_info.currency_code;
3277 x_cascaded_table(n).currency_conversion_rate := default_int_org_cor_rt_info.currency_conversion_rate;
3278 x_cascaded_table(n).currency_conversion_type := default_int_org_cor_rt_info.currency_conversion_type;
3279 x_cascaded_table(n).currency_conversion_date := default_int_org_cor_rt_info.currency_conversion_date;
3280 x_cascaded_table(n).shipment_header_id := default_int_org_cor_rt_info.shipment_header_id;
3281 x_cascaded_table(n).shipment_line_id := default_int_org_cor_rt_info.shipment_line_id;
3282 x_cascaded_table(n).category_id := default_int_org_cor_rt_info.category_id;
3283 x_cascaded_table(n).location_id := default_int_org_cor_rt_info.loc_id;
3284 x_cascaded_table(n).deliver_to_person_id := default_int_org_cor_rt_info.deliver_to_person_id;
3285 x_cascaded_table(n).deliver_to_location_id := default_int_org_cor_rt_info.deliver_to_location_id;
3286 x_cascaded_table(n).subinventory := default_int_org_cor_rt_info.subinventory;
3287
3288 -- LPN defaulting
3289 IF (g_asn_debug = 'Y') THEN
3290 asn_debug.put_line(' defaulting lpn_id and transfer_lpn_id for int org cor');
3291 END IF;
3292
3293 IF (x_cascaded_table(n).quantity > 0) THEN --{
3294 -- for +ve corrections :
3295 -- we are defaulting the parent's from_lpn to child's from_lpn
3296 -- and parent's to_lpn to child's to_lpn
3297 -- only if those fields are null
3298 IF (x_cascaded_table(n).lpn_id IS NULL) THEN
3299 x_cascaded_table(n).lpn_id := default_int_org_cor_rt_info.lpn_id;
3300
3301 IF (x_cascaded_table(n).lpn_id IS NOT NULL) THEN
3302 lpn_error := 1;
3303 END IF;
3304 END IF;
3305
3306 IF (x_cascaded_table(n).transfer_lpn_id IS NULL) THEN
3307 x_cascaded_table(n).transfer_lpn_id := default_int_org_cor_rt_info.transfer_lpn_id;
3308
3309 IF (x_cascaded_table(n).transfer_lpn_id IS NOT NULL) THEN
3310 lpn_error := 1;
3311 END IF;
3312 END IF;
3313
3314 --insert warning message into po_interface_errors
3315 IF (lpn_error = 1) THEN
3316 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_warning;
3317 rcv_error_pkg.set_error_message('RCV_LPN_UNPACK_WARNING', x_cascaded_table(n).error_message);
3318 rcv_error_pkg.set_token('LPN_ID', x_cascaded_table(n).lpn_id);
3319 rcv_error_pkg.log_interface_warning('LPN_ID');
3320 END IF;
3321 ELSE -- its a -ve correction
3322 -- for -ve corrections :
3323 -- we are defaulting the parent's from_lpn to child's to_lpn
3324 -- and parent's to_lpn to child's from_lpn
3325 -- only if those fields are null
3326 IF (x_cascaded_table(n).lpn_id IS NULL) THEN
3327 x_cascaded_table(n).lpn_id := default_int_org_cor_rt_info.transfer_lpn_id;
3328
3329 IF (x_cascaded_table(n).lpn_id IS NOT NULL) THEN
3330 lpn_error := 1;
3331 END IF;
3332 END IF;
3333
3334 IF (x_cascaded_table(n).transfer_lpn_id IS NULL) THEN
3335 x_cascaded_table(n).transfer_lpn_id := default_int_org_cor_rt_info.lpn_id;
3336
3337 IF (x_cascaded_table(n).transfer_lpn_id IS NOT NULL) THEN
3338 lpn_error := 1;
3339 END IF;
3340 END IF;
3341
3342 --insert warning message into po_interface_errors
3343 IF (lpn_error = 1) THEN
3344 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_warning;
3345 rcv_error_pkg.set_error_message('RCV_LPN_UNPACK_WARNING', x_cascaded_table(n).error_message);
3346 rcv_error_pkg.set_token('LPN_ID', x_cascaded_table(n).lpn_id);
3347 rcv_error_pkg.log_interface_warning('LPN_ID');
3348 END IF;
3349 END IF; --}
3350 END IF; -- if po_transfer%found is true }
3351
3352 /* Default the from and to subinventory and locator_id */
3353 ----WMS Changes
3354 rcv_roi_transaction.default_from_subloc_info(x_cascaded_table, n);
3358 BEGIN Comment: Bug: 4735484
3355 rcv_roi_transaction.default_to_subloc_info(x_cascaded_table, n);
3356
3357 /*
3359
3360 SELECT NVL(x_cascaded_table(n).use_mtl_lot, lot_control_code),
3361 NVL(x_cascaded_table(n).use_mtl_serial, serial_number_control_code)
3362 INTO x_cascaded_table(n).use_mtl_lot,
3363 x_cascaded_table(n).use_mtl_serial
3364 FROM mtl_system_items
3365 WHERE mtl_system_items.inventory_item_id = x_cascaded_table(n).item_id
3366 AND mtl_system_items.organization_id = x_cascaded_table(n).to_organization_id;
3367
3368 END Comment: Bug: 4735484
3369 */
3370 IF (g_asn_debug = 'Y') THEN
3371 asn_debug.put_line('Set Location_id = ' || TO_CHAR(x_cascaded_table(n).location_id));
3372 END IF;
3373
3374 IF (g_asn_debug = 'Y') THEN
3375 asn_debug.put_line('Exit default_int_org_cor_line');
3376 END IF;
3377
3378 IF int_org_cor_rt%ISOPEN THEN
3379 CLOSE int_org_cor_rt;
3380 END IF;
3381
3382 IF int_org_cor_rti%ISOPEN THEN
3383 CLOSE int_org_cor_rti;
3384 END IF;
3385 EXCEPTION
3386 WHEN OTHERS THEN
3387 NULL;
3388 END default_int_org_cor_line;
3389 ------corrections--------------------------------
3390
3391 /* Bug 3735972.
3392 * We used to call validate_ref_integrity that had code only for PO.
3393 * We need to have a similar one to validate internal orders and
3394 * inter-org shipments.
3395 */
3396
3397 PROCEDURE validate_ref_integrity(
3398 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
3399 n IN BINARY_INTEGER,
3400 x_header_record IN rcv_roi_preprocessor.header_rec_type
3401 ) IS
3402 x_error_status VARCHAR2(1);
3403 x_header_id number;
3404 BEGIN
3405
3406 IF (x_cascaded_table(n).error_status NOT IN('S', 'W')) THEN
3407 RETURN;
3408 END IF;
3409
3410 /* Bug 3883367. Do NOT validate ref integrity if x_header_record.header_record.receipt_header_id */
3411 IF (x_header_record.header_record.receipt_header_id IS NULL) THEN
3412 RETURN;
3413 END IF;
3414
3415 x_error_status := rcv_error_pkg.g_ret_sts_error;
3416
3417 IF (g_asn_debug = 'Y') THEN
3418 asn_debug.put_line('Validating ref integrity');
3419 END IF;
3420
3421 IF (g_asn_debug = 'Y') THEN
3422 asn_debug.put_line('transaction_type '||(x_cascaded_table(n).transaction_type ));
3423 asn_debug.put_line('shipment_header_id '||(x_header_record.header_record.receipt_header_id ));
3424 asn_debug.put_line('shipment_line_id '||(x_cascaded_table(n).shipment_line_id ));
3425 end if;
3426 /* Get the shipment_header_id for that shipment_line_id and the
3427 * shipment_header_id in the header record. If you dont get it then
3428 * this means that you are trying to receive a shipment line that
3429 * does not belong to the shipment header_id (hence shipment num)
3430 * in the header and so error out.
3431 */
3432 If (x_cascaded_table(n).transaction_type = 'RECEIVE') then
3433 select nvl(max(shipment_header_id),0)
3434 into x_header_id
3435 from rcv_shipment_lines
3436 where shipment_header_id =
3437 x_header_record.header_record.receipt_header_id
3438 and shipment_line_id = x_cascaded_table(n).shipment_line_id;
3439
3440 IF (g_asn_debug = 'Y') THEN
3441 asn_debug.put_line('max shipment_header_id '||x_header_id );
3442 end if;
3443
3444 IF (x_header_id = 0) THEN
3445 IF (g_asn_debug = 'Y') THEN
3446 asn_debug.put_line('No header id found. This shipment line does not belong to the same shipment as that of the header' );
3447 end if;
3448 rcv_error_pkg.set_error_message('RCV_INTORD_MISMATCH_SHIPMENTS');
3449 RAISE e_validation_error;
3450
3451 else
3452 IF (g_asn_debug = 'Y') THEN
3453 asn_debug.put_line('Header id found' );
3454 end if;
3455 END IF;
3456
3457 end if;
3458 EXCEPTION
3459 WHEN e_validation_error THEN
3460 x_cascaded_table(n).error_status := x_error_status;
3461 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
3462
3463 IF x_cascaded_table(n).error_message = 'RCV_INTORD_MISMATCH_SHIPMENTS' THEN
3464 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'SHIPMENT_HEADER_ID');
3465
3466 end if;
3467 END validate_ref_integrity;
3468
3469
3470 END rcv_int_org_transfer;
3471
[Home] [Help]