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