1 PACKAGE BODY rcv_int_order_pp_pvt AS
2 /* $Header: RCVPPIOB.pls 120.12.12020000.2 2012/07/10 09:27:24 ptkumar ship $ */
3
4 -- GLOBAL VARIABLES
5 g_asn_debug VARCHAR2(1) := asn_debug.is_debug_on; -- Bug 9152790
6 x_progress VARCHAR2(3);
7
8 -- LOCAL PROCEDURES
9
10 PROCEDURE derive_io_rcv_line_qty(
11 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
12 n IN OUT NOCOPY BINARY_INTEGER,
13 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type
14 );
15
16 PROCEDURE derive_trans_del_line_quantity(
17 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
18 n IN OUT NOCOPY BINARY_INTEGER,
19 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type
20 );
21
22 PROCEDURE derive_io_correct_line_qty(
23 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
24 n IN OUT NOCOPY BINARY_INTEGER,
25 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type
26 );
27
28 PROCEDURE derive_io_shipment_info(
29 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
30 n IN OUT NOCOPY BINARY_INTEGER
31 );
32
33 -- GLOBAL PROCEDURES
34
35 PROCEDURE derive_internal_order_header(
36 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
37 ) IS
38 BEGIN
39 rcv_roi_header_common.derive_ship_to_org_info(p_header_record);
40 rcv_roi_header_common.derive_from_org_info(p_header_record);
41 rcv_roi_header_common.derive_location_info(p_header_record);
42 rcv_roi_header_common.derive_receiver_info(p_header_record);
43 -- derive shipment info?
44 END derive_internal_order_header;
45
46 PROCEDURE default_internal_order_header(
47 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
48 ) IS
49 BEGIN
50 rcv_roi_header_common.default_last_update_info(p_header_record);
51 rcv_roi_header_common.default_creation_info(p_header_record);
52 -- default_shipment_num(p_header_record);
53 -- RCV_ROI_HEADER_COMMON.default_shipment_header_id(p_header_record);
54
55 /* Bug3591830 - Uncommented the call to default_receipt_info
56 ** as receipt numbers were not getting generated while
57 ** receiving and delivering Internal Orders through ROI
58 */
59 rcv_roi_header_common.default_receipt_info(p_header_record);
60 rcv_roi_header_common.default_ship_to_location_info(p_header_record);
61
62 -- RCV_ROI_HEADER_COMMON.genReceiptNum(p_header_record);
63
64 -- for CANCEL
65
66 -- IF p_header_record.header_record.transaction_type = 'CANCEL'
67 /* Bug 3314675.
68 * Call default_shipment_info to default the shipment_header_id if the
69 * shipment_num is given for an inter-org/internal req receipts.
70 */
71 IF ( p_header_record.header_record.receipt_header_id IS NULL
72 OR p_header_record.header_record.shipment_num IS NULL) THEN
73 default_shipment_info(p_header_record);
74 END IF;
75 END default_internal_order_header;
76
77 PROCEDURE validate_internal_order_header(
78 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
79 ) IS
80 BEGIN
81 rcv_roi_header_common.validate_trx_type(p_header_record);
82 -- validate_document_type(p_header_record);
83 -- validate_currency_code(p_header_record);
84 -- RCV_ROI_HEADER_COMMON.validate_shipment_date(p_header_record);
85 -- validate_receipt_date(p_header_record);
86 rcv_roi_header_common.validate_expected_receipt_date(p_header_record);
87 -- RCV_ROI_HEADER_COMMON.validate_receipt_num(p_header_record);
88 rcv_roi_header_common.validate_ship_to_org_info(p_header_record);
89 rcv_roi_header_common.validate_from_org_info(p_header_record);
90 rcv_roi_header_common.validate_location_info(p_header_record);
91 -- RCV_ROI_HEADER_COMMON.validate_payment_terms_info(p_header_record);
92 rcv_roi_header_common.validate_receiver_info(p_header_record);
93 rcv_roi_header_common.validate_freight_carrier_info(p_header_record);
94 END validate_internal_order_header;
95
96 /* Bug 3314675.
97 * default_shipment_info defaults the shipment_header_id if the
98 * shipment_num is given for an inter-org/internal req receipts.
99 */
100 PROCEDURE default_shipment_info(
101 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
102 ) IS
103 x_count NUMBER;
104 BEGIN
105 IF p_header_record.header_record.receipt_header_id IS NOT NULL THEN
106 IF (g_asn_debug = 'Y') THEN
107 asn_debug.put_line('Shipment header Id has been provided');
108 END IF;
109
110 RETURN;
111 END IF;
112
113 IF ( p_header_record.header_record.shipment_num IS NULL
114 OR p_header_record.header_record.shipment_num = '0'
115 OR REPLACE(p_header_record.header_record.shipment_num,
116 ' ',
117 ''
118 ) IS NULL) THEN
119 IF (g_asn_debug = 'Y') THEN
120 asn_debug.put_line('Shipment num is still null');
121 /* Bug 4907179 */
122 asn_debug.put_line('Not able to default shipment_header_id, erroring out the transaction');
123 END IF;
124
125 /* Bug 4907179: Logging error in PO_INTERFACE_ERRORS table and erroring out the transaction, as
126 we are not able to default the shipment_header_id.
127 Reason: If shipment_header_id is not defaulted, we won't be able to stamp
128 the receipt number created in RCV_SHIPMENT_HEADERS table, through
129 rcv_int_order_pp_pvt.update_header() procedure.*/
130 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
131 rcv_error_pkg.set_error_message('RCV_NO_SHIPMENT_NUM', p_header_record.error_record.error_message);
132 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'SHIPMENT_NUM', false);/* Bug 4907179 */
133
134 RETURN;
135 END IF;
136
137 SELECT MAX(shipment_header_id)
138 INTO p_header_record.header_record.receipt_header_id
139 FROM rcv_shipment_headers
140 WHERE shipment_num = p_header_record.header_record.shipment_num
141 AND receipt_source_code IN('INVENTORY', 'INTERNAL ORDER');
142 EXCEPTION
143 WHEN OTHERS THEN
144 IF (g_asn_debug = 'Y') THEN
145 asn_debug.put_line('Exception in when others in default_shipment_info ');
146 END IF;
147
148 rcv_error_pkg.set_sql_error_message('default_shipment_info', '000');
149 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
150 p_header_record.error_record.error_message := rcv_error_pkg.get_last_message;
151 END default_shipment_info;
152
153 PROCEDURE update_header(
154 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
155 ) IS
156 BEGIN
157 -- Bug 12591134: Start
158 IF (g_asn_debug = 'Y') THEN
159 asn_debug.put_line('enter update_header');
160 END IF;
161
162 IF NVL(p_header_record.header_record.test_flag, 'N') = 'Y' THEN
163 IF (p_header_record.header_record.receipt_num IS NOT NULL) THEN
164 BEGIN
165 UPDATE rcv_shipment_headers
166 SET receipt_num = p_header_record.header_record.receipt_num,
167 last_update_date = p_header_record.header_record.last_update_date,
168 last_updated_by = p_header_record.header_record.last_updated_by,
169 last_update_login = p_header_record.header_record.last_update_login,
170 request_id = fnd_global.conc_request_id,
171 program_application_id = fnd_global.prog_appl_id,
172 program_id = fnd_global.conc_program_id,
173 program_update_date = SYSDATE
174 WHERE shipment_header_id = p_header_record.header_record.receipt_header_id
175 AND receipt_num IS NULL;
176
177 IF (g_asn_debug = 'Y') THEN
178 asn_debug.put_line('Updated receipt_num in RSH');
179 END IF;
180 EXCEPTION
181 WHEN NO_DATA_FOUND THEN NULL;
182 END;
183 END IF;
184 RETURN;
185 END IF;
186 -- Bug 12591134: End
187 UPDATE rcv_shipment_headers
188 SET shipment_header_id = p_header_record.header_record.receipt_header_id,
189 last_update_date = p_header_record.header_record.last_update_date,
190 last_updated_by = p_header_record.header_record.last_updated_by,
191 creation_date = p_header_record.header_record.creation_date,
192 created_by = p_header_record.header_record.created_by,
193 last_update_login = p_header_record.header_record.last_update_login,
194 receipt_source_code = p_header_record.header_record.receipt_source_code,
195 vendor_id = p_header_record.header_record.vendor_id,
196 vendor_site_id = p_header_record.header_record.vendor_site_id,
197 shipment_num = p_header_record.header_record.shipment_num,
198 receipt_num = NVL(receipt_num, p_header_record.header_record.receipt_num),
199 ship_to_location_id = p_header_record.header_record.location_id,
200 ship_to_org_id = p_header_record.header_record.ship_to_organization_id,
201 bill_of_lading = p_header_record.header_record.bill_of_lading,
202 packing_slip = p_header_record.header_record.packing_slip,
203 shipped_date = Nvl(p_header_record.header_record.shipped_date,shipped_date),--BUG 5087622
204 freight_carrier_code = p_header_record.header_record.freight_carrier_code,
205 expected_receipt_date = p_header_record.header_record.expected_receipt_date,
206 employee_id = p_header_record.header_record.employee_id,
207 num_of_containers = p_header_record.header_record.num_of_containers,
208 waybill_airbill_num = p_header_record.header_record.waybill_airbill_num,
209 comments = p_header_record.header_record.comments,
210 attribute_category = p_header_record.header_record.attribute_category,
211 attribute1 = p_header_record.header_record.attribute1,
212 attribute2 = p_header_record.header_record.attribute2,
213 attribute3 = p_header_record.header_record.attribute3,
214 attribute4 = p_header_record.header_record.attribute4,
215 attribute5 = p_header_record.header_record.attribute5,
216 attribute6 = p_header_record.header_record.attribute6,
217 attribute7 = p_header_record.header_record.attribute7,
218 attribute8 = p_header_record.header_record.attribute8,
219 attribute9 = p_header_record.header_record.attribute9,
220 attribute10 = p_header_record.header_record.attribute10,
221 attribute11 = p_header_record.header_record.attribute11,
222 attribute12 = p_header_record.header_record.attribute12,
223 attribute13 = p_header_record.header_record.attribute13,
224 attribute14 = p_header_record.header_record.attribute14,
225 attribute15 = p_header_record.header_record.attribute15,
226 ussgl_transaction_code = p_header_record.header_record.usggl_transaction_code,
227 request_id = fnd_global.conc_request_id,
228 program_application_id = fnd_global.prog_appl_id,
229 program_id = fnd_global.conc_program_id,
230 program_update_date = SYSDATE,
231 asn_type = p_header_record.header_record.asn_type,
232 edi_control_num = p_header_record.header_record.edi_control_num,
233 notice_creation_date = p_header_record.header_record.notice_creation_date,
234 gross_weight = p_header_record.header_record.gross_weight,
235 gross_weight_uom_code = p_header_record.header_record.gross_weight_uom_code,
236 net_weight = p_header_record.header_record.net_weight,
237 net_weight_uom_code = p_header_record.header_record.net_weight_uom_code,
238 tar_weight = p_header_record.header_record.tar_weight,
239 tar_weight_uom_code = p_header_record.header_record.tar_weight_uom_code,
240 packaging_code = p_header_record.header_record.packaging_code,
241 carrier_method = p_header_record.header_record.carrier_method,
242 carrier_equipment = p_header_record.header_record.carrier_equipment,
243 special_handling_code = p_header_record.header_record.special_handling_code,
244 hazard_code = p_header_record.header_record.hazard_code,
245 hazard_class = p_header_record.header_record.hazard_class,
246 hazard_description = p_header_record.header_record.hazard_description,
247 freight_terms = p_header_record.header_record.freight_terms,
248 freight_bill_number = p_header_record.header_record.freight_bill_number,
249 invoice_date = p_header_record.header_record.invoice_date,
250 invoice_amount = p_header_record.header_record.total_invoice_amount,
251 tax_name = p_header_record.header_record.tax_name,
252 tax_amount = p_header_record.header_record.tax_amount,
253 freight_amount = p_header_record.header_record.freight_amount,
254 invoice_status_code = p_header_record.header_record.invoice_status_code,
255 currency_code = p_header_record.header_record.currency_code,
256 conversion_rate_type = p_header_record.header_record.conversion_rate_type,
257 conversion_rate = p_header_record.header_record.conversion_rate,
258 conversion_date = p_header_record.header_record.conversion_rate_date,
259 payment_terms_id = p_header_record.header_record.payment_terms_id,
260 invoice_num = p_header_record.header_record.invoice_num
261 WHERE shipment_header_id = p_header_record.header_record.receipt_header_id;
262 END update_header;
263
264 PROCEDURE derive_io_receive_line(
265 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
266 n IN OUT NOCOPY BINARY_INTEGER,
267 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
268 x_header_record IN rcv_roi_preprocessor.header_rec_type
269 ) IS
270 BEGIN
271 x_progress := '000';
272
273 IF (g_asn_debug = 'Y') THEN
274 asn_debug.put_line('enter internal order receiving line');
275 END IF;
276
277 -- 1) derive ship to org info
278 rcv_roi_transaction.derive_ship_to_org_info(x_cascaded_table,
279 n,
280 x_header_record
281 );
282 x_progress := '002';
283
284 IF (g_asn_debug = 'Y') THEN
285 asn_debug.put_line('x_progress ' || x_progress);
286 END IF;
287
288 x_progress := '010';
289 -- 5) derive item info
290 rcv_roi_transaction.derive_item_info(x_cascaded_table, n);
291 x_progress := '015';
292 rcv_roi_header_common.derive_uom_info(x_cascaded_table, n);
293 -- 6) derive substitute item info
294 rcv_roi_transaction.derive_substitute_item_info(x_cascaded_table, n);
295 x_progress := '020';
296 -- 8) derive from org info
297 rcv_roi_transaction.derive_from_org_info(x_cascaded_table, n);
298 x_progress := '035';
299 -- 12) derive routing header info
300 rcv_roi_transaction.derive_routing_header_info(x_cascaded_table, n);
301 x_progress := '070';
302 asn_debug.put_line('progress in Internal Orders rcv : x progress = ' || x_progress);
303 -- derive auto transact code
304 rcv_roi_transaction.derive_auto_transact_code(x_cascaded_table, n);
305 -- 13) bug 3379550
306 x_progress := '071';
307 asn_debug.put_line('progress in Internal Orders rcv : x progress = ' || x_progress);
308 /* Bug 3684984.
309 * We are getting all the values we used to get in the foll. code
310 * in the cursor in derive_io_rcv_line_qty.
311 derive_io_shipment_info(x_cascaded_table, n);
312 */
313 asn_debug.put_line('progress in Internal Orders rcv : before derive qty');
314 -- quantity > 0
315 derive_io_rcv_line_qty(x_cascaded_table,
316 n,
317 temp_cascaded_table
318 );
319 /* Bug3591830 - Calling the routine derive_to_locator_id to
320 ** derive the locator_id from locator if locator_id is null
321 ** and locator is specified. If an invalid locator is specified
322 ** then corresponding error message needs to be populated in the
323 ** interface errors table
324 */
325 /* Bug3591830 - START */
326 rcv_roi_transaction.derive_to_locator_id(x_cascaded_table, n);
327 temp_cascaded_table(n).locator_id := x_cascaded_table(n).locator_id;
328 asn_debug.put_line('progress in Internal Orders rcv : after derive_to_locator_id -> locator_id = ' || temp_cascaded_table(n).locator_id);
329 rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
330 'LOCATOR_ID',
331 FALSE
332 );
333 /* Bug3591830 - END */
334 END derive_io_receive_line;
335
336 PROCEDURE derive_io_trans_line(
337 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
338 n IN OUT NOCOPY BINARY_INTEGER,
339 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
340 x_header_record IN rcv_roi_preprocessor.header_rec_type
341 ) IS
342 BEGIN
343 IF (g_asn_debug = 'Y') THEN
344 asn_debug.put_line('enter derive_io_trans_line ');
345 END IF;
346
347 rcv_roi_transaction.derive_parent_id(x_cascaded_table, n);
348 /* Derive the to_org_id */
349 rcv_roi_transaction.derive_ship_to_org_info(x_cascaded_table,
350 n,
351 x_header_record
352 );
353
354 IF (x_cascaded_table(n).unit_of_measure IS NOT NULL) THEN
355 IF (g_asn_debug = 'Y') THEN
356 asn_debug.put_line('X_progress ' || x_progress);
357 END IF;
358
359 SELECT muom.uom_code
360 INTO x_cascaded_table(n).uom_code
361 FROM mtl_units_of_measure muom
362 WHERE muom.unit_of_measure = x_cascaded_table(n).unit_of_measure;
363 ELSE
364 IF (g_asn_debug = 'Y') THEN
365 asn_debug.put_line('uom_code not dereived as unit_of_measure is null');
366 END IF;
367 END IF;
368
369 x_progress := '026';
370
371 /* Locator info derivation is done for the Receiving locators FPJ
372 * project. Need to verify this with karun to see whether this is
373 * needed for Transfer also.
374 */
375 /* Bug#4037821 - START */
376 /* Derive the locator_id even in case of deliver transactions */
377 IF (x_cascaded_table(n).transaction_type IN ('TRANSFER','DELIVER')) THEN
378 IF (x_cascaded_table(n).transaction_type = 'TRANSFER') THEN
379 asn_debug.put_line('doing ship to location /locator derivations ');
380 rcv_roi_transaction.derive_location_info(x_cascaded_table, n);
381 rcv_roi_transaction.derive_from_locator_id(x_cascaded_table, n); -- WMS Change
382 END IF;
383 rcv_roi_transaction.derive_to_locator_id(x_cascaded_table, n); -- WMS Change
384 END IF;
385 /* Bug#4037821 - END */
386
387 x_progress := '071';
388 asn_debug.put_line('progress in Internal Orders rcv : x progress = ' || x_progress);
389 derive_io_shipment_info(x_cascaded_table, n);
390 x_progress := '091';
391 rcv_roi_transaction.derive_reason_info(x_cascaded_table, n);
392 /* Auto_transact_code is null for all these transaction types */
393 x_cascaded_table(n).auto_transact_code := NULL;
394 derive_trans_del_line_quantity(x_cascaded_table,
395 n,
396 temp_cascaded_table
397 );
398 END derive_io_trans_line;
399
400 PROCEDURE derive_io_correct_line(
401 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
402 n IN OUT NOCOPY BINARY_INTEGER,
403 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
404 x_header_record IN rcv_roi_preprocessor.header_rec_type
405 ) IS
406 BEGIN
407 IF (g_asn_debug = 'Y') THEN
408 asn_debug.put_line('enter derive_correction_line ');
409 END IF;
410
411 rcv_roi_transaction.derive_parent_id(x_cascaded_table, n);
412 /* Derive the to_org_id */
413 rcv_roi_transaction.derive_ship_to_org_info(x_cascaded_table,
414 n,
415 x_header_record
416 );
417
418 IF (x_cascaded_table(n).unit_of_measure IS NOT NULL) THEN
419 IF (g_asn_debug = 'Y') THEN
420 asn_debug.put_line('x_progress ' || x_progress);
421 END IF;
422
423 SELECT muom.uom_code
424 INTO x_cascaded_table(n).uom_code
425 FROM mtl_units_of_measure muom
426 WHERE muom.unit_of_measure = x_cascaded_table(n).unit_of_measure;
427 ELSE
428 IF (g_asn_debug = 'Y') THEN
429 asn_debug.put_line('uom_code not dereived as unit_of_measure is null');
430 END IF;
431 END IF;
432
433 x_progress := '071';
434 asn_debug.put_line('progress in Internal Orders rcv : x progress = ' || x_progress);
435 derive_io_shipment_info(x_cascaded_table, n);
436 x_progress := '091';
437 rcv_roi_transaction.derive_reason_info(x_cascaded_table, n);
438 /* Auto_transact_code is null for all these transaction types */
439 x_cascaded_table(n).auto_transact_code := NULL;
440 derive_io_correct_line_qty(x_cascaded_table,
441 n,
442 temp_cascaded_table
443 );
444 END derive_io_correct_line;
445
446 PROCEDURE default_io_receive_line(
447 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
448 n IN BINARY_INTEGER
449 ) IS
450 l_routing_header_id NUMBER;
451 BEGIN
452 -- logic should match int-org transfer
453 rcv_int_org_transfer.default_int_org_rcv_line(x_cascaded_table, n);
454 -- EXCEPT we should get the SO internal Receipt Routing.
455 l_routing_header_id := x_cascaded_table(n).routing_header_id;
456
457 IF (NVL(l_routing_header_id, 0) = 0) THEN
458 BEGIN
459 SELECT NVL(receiving_routing_id, 0)
460 INTO l_routing_header_id
461 FROM mtl_system_items
462 WHERE inventory_item_id = x_cascaded_table(n).item_id
463 AND organization_id = x_cascaded_table(n).to_organization_id;
464 EXCEPTION
465 WHEN NO_DATA_FOUND THEN
466 NULL;
467 WHEN OTHERS THEN
468 RAISE;
469 END;
470
471 IF (l_routing_header_id = 0) THEN
472 BEGIN
473 SELECT NVL(routing_header_id, 0)
474 INTO l_routing_header_id
475 FROM mtl_interorg_parameters
476 WHERE from_organization_id = x_cascaded_table(n).from_organization_id
477 AND to_organization_id = x_cascaded_table(n).to_organization_id;
478 EXCEPTION
479 WHEN NO_DATA_FOUND THEN
480 NULL;
481 WHEN OTHERS THEN
482 RAISE;
483 END;
484 END IF;
485
486 IF (l_routing_header_id = 0) THEN
487 BEGIN
488 SELECT NVL(receiving_routing_id, 0)
489 INTO l_routing_header_id
490 FROM rcv_parameters
491 WHERE organization_id = x_cascaded_table(n).to_organization_id;
492 EXCEPTION
493 WHEN NO_DATA_FOUND THEN
494 NULL;
495 WHEN OTHERS THEN
496 RAISE;
497 END;
498 END IF;
499 END IF;
500
501 l_routing_header_id := x_cascaded_table(n).routing_header_id;
502 x_cascaded_table(n).routing_header_id := l_routing_header_id;
503 END default_io_receive_line;
504
505 PROCEDURE default_io_trans_line(
506 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
507 n IN BINARY_INTEGER
508 ) IS
509 BEGIN
510 -- logic should match int-org transfer
511 rcv_int_org_transfer.default_int_org_trans_del(x_cascaded_table, n);
512 END default_io_trans_line;
513
514 PROCEDURE default_io_correct_line(
515 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
516 n IN BINARY_INTEGER
517 ) IS
518 BEGIN
519 -- logic should match int-org transfer
520 rcv_int_org_transfer.default_int_org_cor_line(x_cascaded_table, n);
521 END default_io_correct_line;
522
523 PROCEDURE validate_io_receive_line(
524 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
525 n IN BINARY_INTEGER,
526 x_header_record IN rcv_roi_preprocessor.header_rec_type
527 ) IS
528 l_parent_deliver_to_person_id NUMBER := null; --Bug#6375015
529 l_skip_validation NUMBER := 0; --Bug#6375015
530 BEGIN
531 IF (g_asn_debug = 'Y') THEN
532 asn_debug.put_line('Enter validate_io_receive_line');
533 END IF;
534
535 x_progress := '000';
536 rcv_roi_transaction.validate_shipment_source(x_cascaded_table, n); /* bug9705269 */
537 rcv_roi_transaction.validate_transaction_date(x_cascaded_table, n);
538 rcv_roi_transaction.validate_transaction_uom(x_cascaded_table, n);
539 rcv_roi_transaction.validate_item_info(x_cascaded_table, n);
540
541 /*Bug 7836753
542 Adding IF condition to ensure that the validation call for freight carriers is not made for
543 Internal Orders and Inter-org transfers when the values are same at the header and transaction levels.
544 */
545 IF (x_cascaded_table(n).freight_carrier_code = x_header_record.header_record.freight_carrier_code) THEN
546 NULL;
547 ELSE
548 rcv_roi_transaction.validate_freight_carrier_code(x_cascaded_table, n);
549 END IF;
550 /* End of fix for Bug 7836753 */
551
552 rcv_roi_transaction.validate_dest_type(x_cascaded_table, n);
553
554 IF (x_cascaded_table(n).ship_to_location_id IS NOT NULL) THEN
555 rcv_roi_transaction.validate_ship_to_loc(x_cascaded_table, n);
556 END IF;
557 /* Bug:6375015
558 There is no defaulting done for the deliver_to_person_id or validation against the parent_txn's
559 deliver_to_person_id in case of ROI transaction. But in case of forms we are defaluting the
560 deliver_to_person_id, if there is no validation failure on the deliver_to_person_id in Receipts/
561 Receiving Transaction forms. If there is validation failure, deliver_to_person_id is nulled out
562 and user can enter any deliver_to_person who is active in case of Receipts/Receiving Trasactions
563 form. In case of Returns/Corrections, deliver_to_person_id is not editable and it is defaulted
564 from the parent transaction, even if that person is terminated.
565 So, added the following code to synch up the behaviour of forms and ROI
566 a) if deliver_to_person_id is null, default the deliver_to_person_id from the
567 parent_txn/rsl as done in forms.
568 b) If the deliver_to_person_id is not null, we have to validate against the
569 parent_txn's deliver_to_person.
570 c) Skip the call to validate_deliver_to_person, if deliver_to_person is defaulted
571 from the parent transaction.
572 */
573 /* code fix for the Bug:6375015 starts */
574 IF (x_cascaded_table(n).deliver_to_person_id is null) THEN
575 IF (g_asn_debug = 'Y') THEN
576 asn_debug.put_line('Inside deliver_to_person_id is null...');
577 END IF;
578
579 IF (x_cascaded_table(n).transaction_type = 'DELIVER') THEN
580 --In case of transaction_type DELIVER, we need parent transaction.
581 --If this condition is not added, it will try to defalut the deliver_to_person
582 --of the parent transaction. But form is not behaving like that.
583 --So added this condition to default the deliver_to_person of the source document.
584 IF (g_asn_debug = 'Y') THEN
585 asn_debug.put_line('Transaction type is DELIVER...');
586 asn_debug.put_line('defaulting deliver_to_person_id from RSL...');
587 END IF;
588 get_deliver_to_person_from_rsl(x_cascaded_table,n);
589 IF (g_asn_debug = 'Y') THEN
590 asn_debug.put_line('After call to get_deliver_to_person_from_rsl...');
591 END IF;
592 ELSIF (x_cascaded_table(n).parent_transaction_id is not null) THEN
593 IF (g_asn_debug = 'Y') THEN
594 asn_debug.put_line('Inside parent_transaction_id.is not null and the value is:'||x_cascaded_table(n).parent_transaction_id);
595 END IF;
596 l_parent_deliver_to_person_id := get_deliver_to_person_from_rt(x_cascaded_table,n);
597 --We can safely skip the validate_deliver_to_person call
598 l_skip_validation := 1;
599 x_cascaded_table(n).deliver_to_person_id := l_parent_deliver_to_person_id;
600 IF (g_asn_debug = 'Y') THEN
601 asn_debug.put_line('deliver_to_person_is is set to :'||l_parent_deliver_to_person_id);
602 END IF;
603 ELSIF (x_cascaded_table(n).parent_interface_txn_id is not null) THEN
604 IF (g_asn_debug = 'Y') THEN
605 asn_debug.put_line('Inside parent_interface_transaction_id.is not null and the value is:'||x_cascaded_table(n).parent_interface_txn_id);
606 END IF;
607 l_parent_deliver_to_person_id := get_deliver_to_person_from_rti(x_cascaded_table,n);
608 --We can safely skip the validate_deliver_to_person call
609 l_skip_validation := 1;
610 x_cascaded_table(n).deliver_to_person_id := l_parent_deliver_to_person_id;
611 IF (g_asn_debug = 'Y') THEN
612 asn_debug.put_line('deliver_to_person_is is set to :'||l_parent_deliver_to_person_id);
613 END IF;
614 ELSE--IF (x_cascaded_table(n).transaction_type = 'DELIVER') THEN
615 IF (g_asn_debug = 'Y') THEN
616 asn_debug.put_line('defaulting deliver_to_person_id from RSL...');
617 END IF;
618 get_deliver_to_person_from_rsl(x_cascaded_table,n);
619 IF (g_asn_debug = 'Y') THEN
620 asn_debug.put_line('After call to get_deliver_to_person_from_rsl...');
621 END IF;
622 END IF;--IF (x_cascaded_table(n).transaction_type = 'DELIVER') THEN
623 ELSE--(x_cascaded_table(n).deliver_to_person_id is null)
624 IF (g_asn_debug = 'Y') THEN
625 asn_debug.put_line('Inside deliver_to_person_id is not null and the value is :'||x_cascaded_table(n).deliver_to_person_id);
626 END IF;
627
628 IF (x_cascaded_table(n).transaction_type = 'DELIVER') THEN
629 --In forms user can select any active deliver_to_person for DELIVER transaction.
630 --In case of transaction_type DELIVER, we need parent transaction.
631 --If this condition is not added, it will validate against the deliver_to_person mentioned
632 --the parent RECEIVE transaction. But form is not behaving like that.
633 --So, added this condition to skip deliver_to_person validation against parent txn
634 IF (g_asn_debug = 'Y') THEN
635 asn_debug.put_line('Transaction type is DELIVER...');
636 END IF;
637 ELSIF (x_cascaded_table(n).parent_transaction_id is not null) THEN
638 IF (g_asn_debug = 'Y') THEN
639 asn_debug.put_line('Inside parent_transaction_id.is not null and the value is:'||x_cascaded_table(n).parent_transaction_id);
640 END IF;
641 l_parent_deliver_to_person_id := get_deliver_to_person_from_rt(x_cascaded_table,n);
642 --We can safely skip the validate_deliver_to_person call, validation against
643 --parent transaction is handled here itself.
644 l_skip_validation := 1;
645 IF nvl(l_parent_deliver_to_person_id,-99) <> x_cascaded_table(n).deliver_to_person_id THEN
646 IF (g_asn_debug = 'Y') THEN
647 asn_debug.put_line('RTI.deliver_to_person_id is different to that of parent txn...');
648 END IF;
649 --deliver_to_person_id mismatches with the parent txn's deliver_to_person, so clear it off
650 x_cascaded_table(n).deliver_to_person_id := null;
651 x_cascaded_table(n).deliver_to_person_name := null;
652
653 --set the deliver_to_person_id to that of parent transaction
654 x_cascaded_table(n).deliver_to_person_id := l_parent_deliver_to_person_id;
655 IF (g_asn_debug = 'Y') THEN
656 asn_debug.put_line('deliver_to_person_is is set to that of parent RT txn');
657 END IF;
658 END IF;
659 ELSIF (x_cascaded_table(n).parent_interface_txn_id is not null) THEN
660 IF (g_asn_debug = 'Y') THEN
661 asn_debug.put_line('Inside parent_interface_transaction_id.is not null and the value is:'||x_cascaded_table(n).parent_interface_txn_id);
662 END IF;
663 l_parent_deliver_to_person_id := get_deliver_to_person_from_rti(x_cascaded_table,n);
664 --We can safely skip the validate_deliver_to_person call, validation against
665 --parent transaction is handled here itself.
666 l_skip_validation := 1;
667 IF nvl(l_parent_deliver_to_person_id,-99) <> x_cascaded_table(n).deliver_to_person_id THEN
668 IF (g_asn_debug = 'Y') THEN
669 asn_debug.put_line('RTI.deliver_to_person_id is different to that of parent txn...');
670 END IF;
671 --deliver_to_person_id mismatches with the parent txn's deliver_to_person, so clear it off
672 x_cascaded_table(n).deliver_to_person_id := null;
673 x_cascaded_table(n).deliver_to_person_name := null;
674
675 --set the deliver_to_person_id to that of parent transaction
676 x_cascaded_table(n).deliver_to_person_id := l_parent_deliver_to_person_id;
677
678 IF (g_asn_debug = 'Y') THEN
679 asn_debug.put_line('deliver_to_person_is is set to that of parent RT txn');
680 END IF;
681 END IF;
682 END IF;--IF (x_cascaded_table(n).transaction_type = 'DELIVER') THEN
683 END IF;--(x_cascaded_table(n).deliver_to_person_id is null)
684
685 IF l_skip_validation = 0 THEN
686 IF (g_asn_debug = 'Y') THEN
687 asn_debug.put_line('validate_deliver_to_person is called');
688 END IF;
689 rcv_roi_transaction.validate_deliver_to_person(x_cascaded_table, n);
690 IF (g_asn_debug = 'Y') THEN
691 asn_debug.put_line('After validate_deliver_to_person: ' || x_cascaded_table(n).error_status);
692 END IF;
693 ELSE
694 IF (g_asn_debug = 'Y') THEN
695 asn_debug.put_line('call to validate_deliver_to_person is skipped');
696 END IF;
697 END IF;/* code fix for the Bug:6375015 ends */
698
699 rcv_roi_transaction.validate_routing_record(x_cascaded_table, n);
700 rcv_roi_transaction.validate_deliver_to_loc(x_cascaded_table, n);
701 rcv_roi_transaction.validate_subinventory(x_cascaded_table, n);
702 rcv_roi_transaction.validate_locator(x_cascaded_table, n);
703 rcv_roi_transaction.validate_tax_code(x_cascaded_table,
704 n,
705 x_header_record.header_record.asn_type
706 ); /* Bug3454491 */
707 rcv_roi_transaction.validate_country_of_origin(x_cascaded_table, n);
708 /* Bug 3735972.
709 * We used to call rcv_roi_transaction.validate_ref_integrity that had
710 * code only for PO.
711 * We now have a similar one to validate internal orders and
712 * inter-org shipments in rcv_int_org_transfer package.
713 */
714 rcv_int_org_transfer.validate_ref_integrity(x_cascaded_table,
715 n,
716 x_header_record
717 );
718
719 rcv_roi_transaction.exchange_sub_items(x_cascaded_table, n);
720 /** OPM change Bug# 3061052**/
721 rcv_roi_transaction.validate_opm_attributes(x_cascaded_table, n);
722
723 /* If destination_type_code is inventory then we need to make
724 * sure that we can correct this qty since it might have been
725 * already reserved in inventory.
726 */
727 IF (x_cascaded_table(n).destination_type_code = 'INVENTORY') THEN --{
728 rcv_roi_return.derive_inv_qty(x_cascaded_table, n);
729 END IF; --}
730 EXCEPTION
731 WHEN OTHERS THEN
732 NULL;
733 END validate_io_receive_line;
734
735 PROCEDURE derive_io_rcv_line_qty(
736 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
737 n IN OUT NOCOPY BINARY_INTEGER,
738 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type
739 ) IS
740 /* Bug 3684984.
741 * Added the columns requisition_line_id, employee_id and
742 * po_line_location_id to the cursor. We used to get this before
743 * in derive_io_shipment_info. Now we are not calling it for
744 * the receive transaction.
745 */
746 CURSOR shipments(
747 v_shipment_header_id NUMBER,
748 -- v_shipment_num VARCHAR2, --Bugfix 5201155
749 v_document_line_num NUMBER,
750 v_item_id NUMBER,
751 v_ship_to_org_id NUMBER,
752 v_ship_from_org_id NUMBER,
753 v_shipment_line_id NUMBER -- Bug 8374257
754 ) IS
755 SELECT rsh.shipment_header_id shipment_header_id,
756 rsh.shipment_num shipment_num,
757 rsl.shipment_line_id shipment_line_id,
758 rsl.item_id item_id,
759 rsl.item_description item_description,
760 rsl.to_organization_id to_organization_id,
761 rsl.from_organization_id from_organization_id,
762 rsl.routing_header_id routing_header_id,
763 rsl.category_id category_id,
764 rsh.currency_code currency_code,
765 rsh.conversion_rate currency_conversion_rate,
766 rsh.conversion_rate_type currency_conversion_type,
767 rsh.conversion_date currency_conversion_date,
768 rsl.to_subinventory to_subinventory,
769 rsl.ship_to_location_id ship_to_location_id,
770 rsl.deliver_to_location_id deliver_to_location_id,
771 rsl.deliver_to_person_id deliver_to_person_id,
772 rsl.ussgl_transaction_code ussgl_transaction_code,
773 rsl.destination_type_code destination_type_code,
774 rsl.destination_context destination_context,
775 rsl.unit_of_measure unit_of_measure,
776 rsl.primary_unit_of_measure primary_unit_of_measure,
777 rsl.requisition_line_id requisition_line_id,
778 rsl.po_line_location_id po_line_location_id,
779 rsl.employee_id employee_id
780 FROM rcv_shipment_headers rsh,
781 rcv_shipment_lines rsl,
782 po_requisition_lines_all porl
783 -- Following 2 lines are commented out for Bugfix 5201155
784 -- WHERE rsh.shipment_header_id = NVL(v_shipment_header_id, rsh.shipment_header_id)
785 -- AND NVL(rsh.shipment_num, '0') = NVL(v_shipment_num, NVL(rsh.shipment_num, '0'))
786 WHERE rsh.shipment_header_id = v_shipment_header_id -- Bugfix 5201155
787 AND rsl.shipment_header_id = rsh.shipment_header_id
788 AND NVL(rsl.item_id, 0) = NVL(v_item_id, NVL(rsl.item_id, 0))
789 AND porl.line_num = NVL(v_document_line_num, porl.line_num)--bug 5483231
790 AND porl.requisition_line_id = rsl.requisition_line_id--bug 5483231
791 AND rsl.to_organization_id = NVL(v_ship_to_org_id, rsl.to_organization_id)
792 AND rsl.from_organization_id = NVL(v_ship_from_org_id, rsl.from_organization_id)
793 AND (NVL(rsl.shipment_line_status_code, 'EXPECTED') <> 'FULLY RECEIVED')
794 AND rsh.receipt_source_code = 'INTERNAL ORDER'
795 AND rsl.shipment_line_id = NVL(v_shipment_line_id, rsl.shipment_line_id); -- Bug 8374257
796
797 CURSOR count_shipments(
798 v_shipment_header_id NUMBER,
799 -- v_shipment_num VARCHAR2, -- Bugfix 5201155
800 v_document_line_num VARCHAR,
801 v_item_id NUMBER,
802 v_ship_to_org_id NUMBER,
803 v_ship_from_org_id NUMBER,
804 v_shipment_line_id NUMBER -- Bug 8374257
805 ) IS
806 SELECT COUNT(*) AS line_count
807 FROM rcv_shipment_headers rsh,
808 rcv_shipment_lines rsl,
809 po_requisition_lines_all porl
810 -- Following 2 lines are commented out for Bugfix 5201155
811 -- WHERE rsh.shipment_header_id = NVL(v_shipment_header_id, rsh.shipment_header_id)
812 -- AND NVL(rsh.shipment_num, '0') = NVL(v_shipment_num, NVL(rsh.shipment_num, '0'))
813 WHERE rsh.shipment_header_id = v_shipment_header_id -- Bugfix 5201155
814 AND rsl.shipment_header_id = rsh.shipment_header_id
815 AND NVL(rsl.item_id, 0) = NVL(v_item_id, NVL(rsl.item_id, 0))
816 AND porl.line_num = NVL(v_document_line_num, porl.line_num)--bug 5483231
817 AND porl.requisition_line_id = rsl.requisition_line_id--bug 5483231
818 AND rsl.to_organization_id = NVL(v_ship_to_org_id, rsl.to_organization_id)
819 AND rsl.from_organization_id = NVL(v_ship_from_org_id, rsl.from_organization_id)
820 AND (NVL(rsl.shipment_line_status_code, 'EXPECTED') <> 'FULLY RECEIVED')
821 AND rsh.receipt_source_code = 'INTERNAL ORDER'
822 AND rsl.shipment_line_id = NVL(v_shipment_line_id, rsl.shipment_line_id); -- Bug 8374257
823
824 x_shipmentrec shipments%ROWTYPE;
825 x_countshipmentrec count_shipments%ROWTYPE;
826 x_record_count NUMBER := 0;
827 x_remaining_quantity NUMBER := 0;
828 x_remaining_qty_po_uom NUMBER := 0;
829 x_progress VARCHAR2(3);
830 x_to_organization_code VARCHAR2(5);
831 x_converted_trx_qty NUMBER := 0;
832 transaction_ok BOOLEAN := FALSE;
833 rows_fetched NUMBER := 0;
834 x_tolerable_qty NUMBER := 0;
835 x_first_trans BOOLEAN := TRUE;
836 x_sysdate DATE := SYSDATE;
837 current_n BINARY_INTEGER := 0;
838 insert_into_table BOOLEAN := FALSE;
839 x_qty_rcv_exception_code po_line_locations.qty_rcv_exception_code%TYPE;
840 tax_amount_factor NUMBER;
841 x_temp_already_allocated_qty NUMBER;
842 x_remaining_qty_rsl_uom NUMBER;
843 lastrecord BOOLEAN := FALSE;
844 already_allocated_qty NUMBER := 0;
845 x_item_id NUMBER;
846 x_ship_to_organization_id NUMBER;
847 x_sob_id NUMBER := NULL;
848 x_secondary_available_qty NUMBER := 0;
849 x_full_name VARCHAR2(240) := NULL; -- Bug 2392074
850 l_shipment_header_id rcv_shipment_headers.shipment_header_id%TYPE; -- Bugfix 5201155
851 BEGIN
852 x_progress := '097';
853
854 IF (g_asn_debug = 'Y') THEN
855 asn_debug.put_line('inside line qty calculation of int org rcv');
856 asn_debug.put_line('x_progress ' || x_progress);
857 END IF;
858
859 IF x_cascaded_table(n).error_status NOT IN('S', 'W') THEN
860 RETURN;
861 END IF;
862
863 IF x_cascaded_table(n).quantity <= 0 THEN --{
864 IF (g_asn_debug = 'Y') THEN
865 asn_debug.put_line('quantity is <= zero. cascade will fail');
866 END IF;
867
868 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
869 rcv_error_pkg.set_error_message('RCV_ITEM_NO_SHIP_QTY', x_cascaded_table(n).error_message);
870 rcv_error_pkg.set_token('ITEM', x_cascaded_table(n).item_num);
871 rcv_error_pkg.log_interface_error('QUANTITY');
872 END IF; --} end qty > 0 check
873
874 x_progress := '098';
875
876 IF (g_asn_debug = 'Y') THEN
877 asn_debug.put_line('x_progress ' || x_progress);
878 asn_debug.put_line('the shipment info is = ' || TO_CHAR(x_cascaded_table(n).shipment_header_id) || ' num = ' || x_cascaded_table(n).shipment_num);
879 END IF;
880
881 -- as long as shipment num or shipment header id is specified we can continue
882 IF ( x_cascaded_table(n).shipment_header_id IS NULL
883 AND x_cascaded_table(n).shipment_num IS NULL) THEN
884 -- error_status and error_message are set after validate_quantity_shipped
885 IF (g_asn_debug = 'Y') THEN
886 asn_debug.put_line('no shipment num/shipment header specified ');
887 asn_debug.put_line('status = ' || x_cascaded_table(n).error_status);
888 END IF;
889
890 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
891 rcv_error_pkg.set_error_message('RCV_IOT_NO_SHIP_INFO', x_cascaded_table(n).error_message);
892 rcv_error_pkg.set_token('SHIPMENT_NUM', x_cascaded_table(n).shipment_num);
893 rcv_error_pkg.log_interface_error('SHIPMENT_NUM');
894 END IF; -- } of (asn quantity_shipped was valid)
895
896 -- copy record from main table to temp table
897
898 IF (g_asn_debug = 'Y') THEN
899 asn_debug.put_line('copy record from main table to temp table');
900 END IF;
901
902 current_n := 1;
903 temp_cascaded_table(current_n) := x_cascaded_table(n);
904
905 -- Bugfix 5201155
906 IF ( x_cascaded_table(n).shipment_header_id IS NULL
907 AND x_cascaded_table(n).shipment_num IS NOT NULL) THEN --{
908 IF (g_asn_debug = 'Y') THEN
909 asn_debug.put_line('Shipment header is not provided hence deriving shipment header id for shipment num ' || x_cascaded_table(n).shipment_num );
910 END IF;
911
912 /* Bug:6313315
913 Added where clause condition rsh.receipt_source_code = 'INTERNAL ORDER'.
914 As we can have same shipment number for ISO shipment and Inter org shipment
915 we need to filter the shipment record by receipt_source_code.
916 */
917 BEGIN
918 SELECT distinct rsh.shipment_header_id
919 INTO l_shipment_header_id
920 FROM rcv_shipment_headers rsh,
921 rcv_shipment_lines rsl
922 WHERE shipment_num = temp_cascaded_table(current_n).shipment_num
923 AND rsh.shipment_header_id = rsl.shipment_header_id
924 AND rsl.to_organization_id = NVL(temp_cascaded_table(current_n).to_organization_id, to_organization_id)
925 AND rsl.from_organization_id = NVL(temp_cascaded_table(current_n).from_organization_id, from_organization_id)
926 AND rsh.receipt_source_code = 'INTERNAL ORDER';--Bug: 6313315
927
928 IF (g_asn_debug = 'Y') THEN
929 asn_debug.put_line('Shipment header = ' || l_shipment_header_id );
930 END IF;
931 EXCEPTION
932 WHEN NO_DATA_FOUND
933 THEN
934 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
935 rcv_error_pkg.set_error_message('RCV_IOT_NO_SHIP_INFO', x_cascaded_table(n).error_message);
936 rcv_error_pkg.set_token('SHIPMENT_NUM', temp_cascaded_table(current_n).shipment_num);
937 rcv_error_pkg.log_interface_error('SHIPMENT_NUM');
938
939 IF (g_asn_debug = 'Y') THEN
940 asn_debug.put_line(TO_CHAR(n));
941 asn_debug.put_line('No shipment_header_id found for shipment_num = ' || temp_cascaded_table(current_n).shipment_num );
942 asn_debug.put_line('error ' || x_progress);
943 END IF;
944 WHEN OTHERS
945 THEN
946 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_unexp_error;
947 rcv_error_pkg.set_sql_error_message('derive_int_org_rcv_line_qty', x_progress);
948 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
949 rcv_error_pkg.log_interface_error('INTERFACE_TRANSACTION_ID');
950
951 IF (g_asn_debug = 'Y') THEN
952 asn_debug.put_line(TO_CHAR(n));
953 asn_debug.put_line('Error while selecting shipment_header_id for shipment_num = ' || temp_cascaded_table(current_n).shipment_num );
954 asn_debug.put_line(sqlerrm);
955 asn_debug.put_line('error ' || x_progress);
956 END IF;
957 END;
958 ELSE
959 l_shipment_header_id := temp_cascaded_table(current_n).shipment_header_id;
960 END IF; -- } deriving shipment_header_id if it NULL from shipment_num
961 -- End of code for Bugfix 5201155
962
963 -- get all rows which meet this condition
964 IF (g_asn_debug = 'Y') THEN
965 asn_debug.put_line('get all rows which meet this condition');
966 asn_debug.put_line('transaction type = ' || x_cascaded_table(n).transaction_type);
967 asn_debug.put_line('auto transact code = ' || x_cascaded_table(n).auto_transact_code);
968 END IF;
969
970 --{ open the cursors
971 IF (g_asn_debug = 'Y') THEN
972 asn_debug.put_line('open shipment records');
973 asn_debug.put_line('shipment header id ' || TO_CHAR(temp_cascaded_table(current_n).shipment_header_id));
974 asn_debug.put_line('item id ' || TO_CHAR(temp_cascaded_table(current_n).item_id));
975 asn_debug.put_line('shipment line num ' || TO_CHAR(temp_cascaded_table(current_n).document_line_num));
976 asn_debug.put_line('ship to organization id ' || TO_CHAR(temp_cascaded_table(current_n).to_organization_id));
977 asn_debug.put_line('from org id ' || TO_CHAR(temp_cascaded_table(current_n).from_organization_id));
978 asn_debug.put_line('shipment line id ' || TO_CHAR(temp_cascaded_table(current_n).shipment_line_id)); -- Bug 8374257
979 asn_debug.put_line('proceed to open cursor');
980 END IF;
981
982 OPEN shipments(-- temp_cascaded_table(current_n).shipment_header_id, -- Bugfix 5201155
983 -- temp_cascaded_table(current_n).shipment_num, -- Bugfix 5201155
984 l_shipment_header_id, -- Bugfix 5201155
985 temp_cascaded_table(current_n).document_line_num,
986 temp_cascaded_table(current_n).item_id,
987 temp_cascaded_table(current_n).to_organization_id,
988 temp_cascaded_table(current_n).from_organization_id,
989 temp_cascaded_table(current_n).shipment_line_id -- Bug 8374257
990 );
991 -- count_shipments just gets the count of rows found in shipments
992
993 OPEN count_shipments(-- temp_cascaded_table(current_n).shipment_header_id, -- Bugfix 5201155
994 -- temp_cascaded_table(current_n).shipment_num, -- Bugfix 5201155
995 l_shipment_header_id,
996 temp_cascaded_table(current_n).document_line_num,
997 temp_cascaded_table(current_n).item_id,
998 temp_cascaded_table(current_n).to_organization_id,
999 temp_cascaded_table(current_n).from_organization_id,
1000 temp_cascaded_table(current_n).shipment_line_id -- Bug 8374257
1001 );
1002
1003 -- }
1004
1005 -- assign shipped quantity to remaining quantity
1006 IF (g_asn_debug = 'Y') THEN
1007 asn_debug.put_line('assign shipped quantity to remaining quantity');
1008 asn_debug.put_line('pointer in temp_cascade ' || TO_CHAR(current_n));
1009 END IF;
1010
1011 x_remaining_quantity := temp_cascaded_table(current_n).quantity;
1012 x_remaining_qty_po_uom := 0;
1013
1014 IF (g_asn_debug = 'Y') THEN
1015 asn_debug.put_line('have assigned the quantity');
1016 END IF;
1017
1018 -- calculate tax_amount_factor for calculating tax_amount for
1019 -- each cascaded line
1020
1021 IF NVL(temp_cascaded_table(current_n).tax_amount, 0) <> 0 THEN
1022 tax_amount_factor := temp_cascaded_table(current_n).tax_amount / x_remaining_quantity;
1023 ELSE
1024 tax_amount_factor := 0;
1025 END IF;
1026
1027 IF (g_asn_debug = 'Y') THEN
1028 asn_debug.put_line('tax factor ' || TO_CHAR(tax_amount_factor));
1029 asn_debug.put_line('shipped quantity : ' || TO_CHAR(x_remaining_quantity));
1030 END IF;
1031
1032 x_first_trans := TRUE;
1033 transaction_ok := FALSE;
1034 /*
1035 ** get the count of the number of records depending on the
1036 */
1037 FETCH count_shipments INTO x_countshipmentrec;
1038 x_record_count := x_countshipmentrec.line_count;
1039
1040 IF (g_asn_debug = 'Y') THEN
1041 asn_debug.put_line('before starting cascade');
1042 END IF;
1043
1044 IF (g_asn_debug = 'Y') THEN
1045 asn_debug.put_line('record count = ' || x_record_count);
1046 END IF;
1047
1048 LOOP --{ over the count of shipment records obtained
1049 IF (g_asn_debug = 'Y') THEN
1050 asn_debug.put_line('remaining quantity asn uom ' || TO_CHAR(x_remaining_quantity));
1051 END IF;
1052
1053 IF (g_asn_debug = 'Y') THEN
1054 asn_debug.put_line('open shipments and fetch');
1055 END IF;
1056
1057 /*
1058 ** fetch the cursor
1059 */
1060 --{
1061 IF (g_asn_debug = 'Y') THEN
1062 asn_debug.put_line('fetching shipments cursor');
1063 END IF;
1064
1065 FETCH shipments INTO x_shipmentrec;
1066
1067 /*
1068 ** check if this is the last record
1069 */
1070 IF (shipments%NOTFOUND) THEN
1071 lastrecord := TRUE;
1072 END IF;
1073
1074 rows_fetched := shipments%ROWCOUNT;
1075
1076 IF (g_asn_debug = 'Y') THEN
1077 asn_debug.put_line('shipment rows fetched ' || TO_CHAR(rows_fetched));
1078 END IF;
1079
1080 -- }
1081
1082
1083 IF ( lastrecord
1084 OR x_remaining_quantity <= 0) THEN --{
1085 IF (g_asn_debug = 'Y') THEN
1086 asn_debug.put_line('hit exit condition');
1087 END IF;
1088
1089 IF NOT x_first_trans THEN
1090 -- x_first_trans has been reset which means some cascade has
1091 -- happened. otherwise current_n = 1
1092 current_n := current_n - 1;
1093 END IF;
1094
1095 -- do the tolerance act here
1096 IF (g_asn_debug = 'Y') THEN
1097 asn_debug.put_line('temp table pointer ' || TO_CHAR(current_n));
1098 asn_debug.put_line('check which condition has occured');
1099 END IF;
1100
1101 -- lastrecord...we have run out of rows and we still have quantity to allocate
1102 IF x_remaining_quantity > 0 THEN --{
1103 IF (g_asn_debug = 'Y') THEN
1104 asn_debug.put_line('There is quantity remaining');
1105 asn_debug.put_line('Need to check qty tolerances');
1106 END IF;
1107
1108 IF rows_fetched > 0
1109 AND NOT x_first_trans THEN --{
1110 IF (g_asn_debug = 'Y') THEN
1111 asn_debug.put_line(' in internal order transfer rcv Extra Quantity ' || TO_CHAR(x_remaining_quantity));
1112 END IF;
1113
1114 IF (g_asn_debug = 'Y') THEN
1115 asn_debug.put_line('delete the temp table ');
1116 END IF;
1117
1118 IF temp_cascaded_table.COUNT > 0 THEN
1119 FOR i IN 1 .. temp_cascaded_table.COUNT LOOP
1120 temp_cascaded_table.DELETE(i);
1121 END LOOP;
1122 END IF;
1123
1124 IF (g_asn_debug = 'Y') THEN
1125 asn_debug.put_line('Need to insert a row into po_interface_errors for transfer');
1126 END IF;
1127
1128 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1129 rcv_error_pkg.set_error_message('RCV_SHIP_QTY_OVER_TOLERANCE', x_cascaded_table(n).error_message);
1130 rcv_error_pkg.set_token('QTY_A', x_cascaded_table(n).quantity);
1131 rcv_error_pkg.set_token('QTY_B', x_cascaded_table(n).quantity - x_remaining_quantity);
1132 rcv_error_pkg.log_interface_error('QUANTITY');
1133 ELSE --}{ else for rows fetched = 0 OR x_first_trans = true
1134 IF rows_fetched = 0 THEN
1135 IF (g_asn_debug = 'Y') THEN
1136 asn_debug.put_line('No rows were retrieved from cursor.');
1137 END IF;
1138 ELSIF x_first_trans THEN
1139 IF (g_asn_debug = 'Y') THEN
1140 asn_debug.put_line('No rows were cascaded');
1141 END IF;
1142 END IF;
1143
1144 -- 1) should we check to see why no rows were fetched ??
1145 --2) should we error out the row in rti if another check proved
1146 -- there are rows in rsl for this rti row
1147
1148 -- Delete the temp_cascaded_table just to be sure
1149 IF temp_cascaded_table.COUNT > 0 THEN
1150 FOR i IN 1 .. temp_cascaded_table.COUNT LOOP
1151 temp_cascaded_table.DELETE(i);
1152 END LOOP;
1153 END IF;
1154
1155 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1156 rcv_error_pkg.set_error_message('RCV_TP_INVALID_TRX_TYPE', x_cascaded_table(n).error_message);
1157 rcv_error_pkg.log_interface_error('PARENT_TRANSACTION_ID');
1158 END IF; --} ends row fetched > 0 and not first transaction
1159
1160 -- all the rows in the temp cascaded table
1161 -- will be deleted
1162 -- as we cannot over/under receive against a inter-org transfer receive
1163 ELSE -- }{
1164 IF (g_asn_debug = 'Y') THEN
1165 asn_debug.put_line('Remaining UOM quantity is zero ' || TO_CHAR(x_remaining_quantity));
1166 asn_debug.put_line('Return the cascaded rows back to the calling procedure');
1167 END IF;
1168 END IF; --} ends the check for whether last record has been reached
1169
1170 -- close cursors
1171
1172 IF (g_asn_debug = 'Y') THEN
1173 asn_debug.put_line('close cursors shipments, count_shipments');
1174 END IF;
1175
1176 IF shipments%ISOPEN THEN
1177 CLOSE shipments;
1178 END IF;
1179
1180 IF count_shipments%ISOPEN THEN
1181 CLOSE count_shipments;
1182 END IF;
1183
1184 EXIT;
1185 END IF; --} matches lastrecord or x_remaining_quantity <= 0
1186
1187 -- eliminate the row if it fails the date check
1188
1189 IF (g_asn_debug = 'Y') THEN
1190 asn_debug.put_line('count in temp_cascade_table : ' || TO_CHAR(temp_cascaded_table.COUNT));
1191 asn_debug.put_line('cursor record ' || TO_CHAR(rows_fetched));
1192 asn_debug.put_line('int org rcv : calling get available qty');
1193 END IF;
1194
1195 -- removed rcv_transactions_interface_sv.check_date_tolerance;
1196 -- removed check shipto_location enforcement
1197 -- removed check receipt days exception code
1198
1199 --{
1200 --matches shipmentdistributionrec.receipt_days_exception_code = none
1201 -- we will do it for the first record only. subsequent records in the
1202 -- temp_table are copies of the previous one
1203 IF (x_first_trans)
1204 AND temp_cascaded_table(current_n).item_id IS NULL THEN --{
1205 temp_cascaded_table(current_n).item_id := x_shipmentrec.item_id;
1206 temp_cascaded_table(current_n).primary_unit_of_measure := x_shipmentrec.primary_unit_of_measure;
1207 END IF; --}
1208
1209 insert_into_table := FALSE;
1210 already_allocated_qty := 0;
1211
1212 /*
1213 ** get the available quantity for the shipment line (rsl)
1214 ** that is available for allocation by this interface transaction
1215 ** the available qty can only be found from rsl
1216 ** the else condition should never arise : confirm from priya ??
1217 */
1218 IF (g_asn_debug = 'Y') THEN
1219 asn_debug.put_line('shipment line id : ' || TO_CHAR(x_shipmentrec.shipment_line_id));
1220 asn_debug.put_line('uom ' || x_shipmentrec.unit_of_measure);
1221 asn_debug.put_line('converted trx qty : ' || TO_CHAR(x_converted_trx_qty));
1222 asn_debug.put_line('tolerable qty : ' || TO_CHAR(x_tolerable_qty));
1223 asn_debug.put_line('receipt source code' || x_cascaded_table(n).receipt_source_code);
1224 END IF;
1225
1226 IF ( x_cascaded_table(n).transaction_type = 'RECEIVE'
1227 AND NVL(x_cascaded_table(n).auto_transact_code, 'RECEIVE') IN('RECEIVE', 'DELIVER')) THEN --{
1228 /*bug# 1548597 */
1229 rcv_quantities_s.get_available_quantity('RECEIVE',
1230 x_shipmentrec.shipment_line_id,
1231 x_cascaded_table(n).receipt_source_code, -- ?? specifying the rti receipt source code for now
1232 NULL,
1233 NULL,
1234 NULL,
1235 x_converted_trx_qty,
1236 x_tolerable_qty,
1237 x_shipmentrec.unit_of_measure, -- this is rsl.unit_of_measure
1238 x_secondary_available_qty
1239 );
1240
1241 -- if qtys have already been allocated for this item during
1242 -- a cascade process which has not been written to the db yet, we need to
1243 -- decrement it from the total available quantity
1244 -- we traverse the actual pl/sql table and accumulate the quantity by matching the
1245 -- item_id
1246
1247 IF n > 1 THEN -- we will do this for all rows except the 1st
1248 FOR i IN 1 ..(n - 1) LOOP
1249 IF x_cascaded_table(i).item_id = x_shipmentrec.item_id THEN
1250 x_temp_already_allocated_qty := rcv_roi_transaction.convert_into_correct_qty(x_cascaded_table(i).quantity,
1251 x_cascaded_table(i).unit_of_measure,
1252 x_cascaded_table(i).item_id,
1253 x_shipmentrec.unit_of_measure
1254 );
1255 already_allocated_qty := already_allocated_qty + x_temp_already_allocated_qty;
1256 END IF;
1257 END LOOP;
1258 END IF;
1259 END IF; --}
1260
1261 IF (g_asn_debug = 'Y') THEN
1262 asn_debug.put_line('after call to get_available quantity');
1263 asn_debug.put_line('available quantity ' || TO_CHAR(x_converted_trx_qty));
1264 asn_debug.put_line('tolerable quantity ' || TO_CHAR(x_tolerable_qty));
1265 asn_debug.put_line('pointer to temp table ' || TO_CHAR(current_n));
1266 asn_debug.put_line(' Already allocated qty now in terms of shipment rec uom is ' || already_allocated_qty);
1267 END IF;
1268
1269 -- if qty has already been allocated then reduce available and tolerable
1270 -- qty by the allocated amount
1271
1272 IF NVL(already_allocated_qty, 0) > 0 THEN --{
1273 x_converted_trx_qty := x_converted_trx_qty - already_allocated_qty;
1274 x_tolerable_qty := x_tolerable_qty - already_allocated_qty;
1275
1276 IF x_converted_trx_qty < 0 THEN
1277 x_converted_trx_qty := 0;
1278 END IF;
1279
1280 IF x_tolerable_qty < 0 THEN
1281 x_tolerable_qty := 0;
1282 END IF;
1283
1284 IF (g_asn_debug = 'Y') THEN
1285 asn_debug.put_line('have some allocated quantity. will reduce qty');
1286 asn_debug.put_line('allocated qty ' || TO_CHAR(already_allocated_qty));
1287 asn_debug.put_line('after reducing by allocated qty');
1288 asn_debug.put_line('available quantity ' || TO_CHAR(x_converted_trx_qty));
1289 asn_debug.put_line('tolerable quantity ' || TO_CHAR(x_tolerable_qty));
1290 asn_debug.put_line('pointer to temp table ' || TO_CHAR(current_n));
1291 END IF;
1292 END IF; --}
1293 -- we can use the first record since the item_id and uom are not going to change
1294 -- check that we can convert between asn-> po uom
1295 -- po -> asn uom
1296 -- po -> primary uom
1297 -- if any of the conversions fail then we cannot use that record
1298
1299 x_remaining_qty_rsl_uom := 0; -- initialize
1300 x_remaining_qty_rsl_uom := rcv_roi_transaction.convert_into_correct_qty(x_remaining_quantity,
1301 temp_cascaded_table(1).unit_of_measure,
1302 temp_cascaded_table(1).item_id,
1303 x_shipmentrec.unit_of_measure
1304 );
1305
1306 IF x_remaining_qty_rsl_uom = 0 THEN --{ -- no point continuing
1307 IF (g_asn_debug = 'Y') THEN
1308 asn_debug.put_line('need an error message in the interface tables');
1309 asn_debug.put_line('cannot interconvert between diff uoms');
1310 END IF;
1311 ELSE -- we have converted the qty between uoms succesfully } {
1312 IF (g_asn_debug = 'Y') THEN
1313 asn_debug.put_line('current item id ' || TO_CHAR(temp_cascaded_table(1).item_id));
1314 asn_debug.put_line('current asn quantity ' || TO_CHAR(x_remaining_quantity));
1315 asn_debug.put_line('current asn uom ' || temp_cascaded_table(1).unit_of_measure);
1316 asn_debug.put_line('converted rsl uom quantity ' || TO_CHAR(x_remaining_qty_rsl_uom));
1317 END IF;
1318
1319 IF x_converted_trx_qty > 0 THEN --{
1320 IF (x_converted_trx_qty < x_remaining_qty_rsl_uom) THEN -- compare like uoms {
1321 IF (g_asn_debug = 'Y') THEN
1322 asn_debug.put_line('total qty available to be received is less than remaining qty');
1323 END IF;
1324
1325 x_remaining_qty_rsl_uom := x_remaining_qty_rsl_uom - x_converted_trx_qty;
1326 -- change rsl uom qty to uom of first line in cascaded table so both qtys are in sync
1327 x_remaining_quantity := rcv_roi_transaction.convert_into_correct_qty(x_remaining_qty_rsl_uom,
1328 x_shipmentrec.unit_of_measure,
1329 temp_cascaded_table(1).item_id,
1330 temp_cascaded_table(1).unit_of_measure
1331 );
1332 -- Bug 13744041: Start
1333 IF (x_shipmentrec.unit_of_measure <> temp_cascaded_table(1).unit_of_measure AND
1334 round(x_remaining_quantity,5) < 0.00005) THEN
1335 x_converted_trx_qty := x_remaining_qty_rsl_uom + x_converted_trx_qty;
1336 x_remaining_qty_rsl_uom := 0;
1337 x_remaining_quantity := 0;
1338 END IF;
1339 -- Bug 13744041: End
1340 insert_into_table := TRUE;
1341 ELSE --} {
1342 IF (g_asn_debug = 'Y') THEN
1343 asn_debug.put_line('total qty available to be received is > remaining qty ');
1344 END IF;
1345
1346 x_converted_trx_qty := x_remaining_qty_rsl_uom;
1347 insert_into_table := TRUE;
1348 x_remaining_qty_rsl_uom := 0;
1349 x_remaining_quantity := 0;
1350 END IF; --}
1351 ELSE -- no qty for this record but if last row we need it } {
1352 IF rows_fetched = x_record_count THEN --{ last row needs to be inserted anyway
1353 -- so that the row can be used based on qty tolerance checks
1354 IF (g_asn_debug = 'Y') THEN
1355 asn_debug.put_line('quantity is less then 0 but last record');
1356 END IF;
1357
1358 insert_into_table := TRUE;
1359 x_converted_trx_qty := 0;
1360 ELSE --} {
1361 IF (g_asn_debug = 'Y') THEN
1362 asn_debug.put_line('<= 0 quantity but more records in cursor');
1363 END IF;
1364
1365 x_remaining_qty_po_uom := 0; -- we may have a diff uom on the next iteration
1366
1367 IF (g_asn_debug = 'Y') THEN
1368 asn_debug.put_line('we have to deal with remaining_qty > 0 and x_converted_trx_qty -ve');
1369 END IF;
1370
1371 insert_into_table := FALSE;
1372 END IF; --}
1373 END IF; --}
1374 END IF; --} remaining_qty_po_uom <> 0
1375
1376 IF insert_into_table THEN --{
1377 IF (x_first_trans) THEN --{
1378 IF (g_asn_debug = 'Y') THEN
1379 asn_debug.put_line('first time ' || TO_CHAR(current_n));
1380 END IF;
1381
1382 x_first_trans := FALSE;
1383 ELSE --} { not x_first_trans
1384 IF (g_asn_debug = 'Y') THEN
1385 asn_debug.put_line('next time ' || TO_CHAR(current_n));
1386 END IF;
1387
1388 temp_cascaded_table(current_n) := temp_cascaded_table(current_n - 1);
1389 END IF; --} matches x_first_transfer
1390
1391 -- source_doc_qty should be in rsl's uom
1392
1393 temp_cascaded_table(current_n).source_doc_quantity := x_converted_trx_qty; -- in rsl uom
1394 temp_cascaded_table(current_n).source_doc_unit_of_measure := x_shipmentrec.unit_of_measure;
1395
1396 IF (temp_cascaded_table(current_n).unit_of_measure <> x_shipmentrec.unit_of_measure) THEN
1397 temp_cascaded_table(current_n).quantity := rcv_roi_transaction.convert_into_correct_qty(x_converted_trx_qty,
1398 x_shipmentrec.unit_of_measure,
1399 temp_cascaded_table(current_n).item_id,
1400 temp_cascaded_table(current_n).unit_of_measure
1401 ); -- in asn uom
1402 temp_cascaded_table(current_n).quantity := round (temp_cascaded_table(current_n).quantity,5); -- Bug 13744041
1403 ELSE
1404 temp_cascaded_table(current_n).quantity := round (x_converted_trx_qty,5); -- Bug 13744041
1405 END IF;
1406
1407 IF (g_asn_debug = 'Y') THEN
1408 asn_debug.put_line('Transaction qty in terms of the transaction uom is ' || temp_cascaded_table(current_n).quantity);
1409 END IF;
1410
1411 -- primary qty in primary uom
1412 IF (temp_cascaded_table(current_n).primary_unit_of_measure <> x_shipmentrec.unit_of_measure) THEN
1413 temp_cascaded_table(current_n).primary_quantity := rcv_roi_transaction.convert_into_correct_qty(x_converted_trx_qty,
1414 x_shipmentrec.unit_of_measure,
1415 temp_cascaded_table(current_n).item_id,
1416 temp_cascaded_table(current_n).primary_unit_of_measure
1417 );
1418 temp_cascaded_table(current_n).primary_quantity := round (temp_cascaded_table(current_n).primary_quantity,5); -- Bug 13744041
1419 ELSE
1420 temp_cascaded_table(current_n).primary_quantity := round(x_converted_trx_qty,5); -- Bug 13744041
1421
1422 IF (g_asn_debug = 'Y') THEN
1423 asn_debug.put_line('Transaction qty in terms of the primary uom is ' || temp_cascaded_table(current_n).primary_quantity);
1424 END IF;
1425
1426 temp_cascaded_table(current_n).inspection_status_code := 'NOT INSPECTED';
1427 temp_cascaded_table(current_n).interface_source_code := 'RCV';
1428 -- temp_cascaded_table(current_n).currency_code := x_shipmentrec.currency_code;
1429 temp_cascaded_table(current_n).tax_amount := ROUND(temp_cascaded_table(current_n).quantity * tax_amount_factor, 4);
1430
1431 IF (g_asn_debug = 'Y') THEN
1432 asn_debug.put_line('current tax amount ' || TO_CHAR(temp_cascaded_table(current_n).tax_amount));
1433 END IF;
1434
1435 -- confirm the data in rsh and rsl for the provided info
1436 IF (g_asn_debug = 'Y') THEN
1437 asn_debug.put_line( 'rsl : cat '
1438 || x_shipmentrec.category_id
1439 || ' item desc '
1440 || x_shipmentrec.item_description
1441 || ' header '
1442 || x_shipmentrec.shipment_header_id
1443 || ' ship num '
1444 || x_shipmentrec.shipment_num
1445 || ' line '
1446 || x_shipmentrec.shipment_line_id);
1447 END IF;
1448
1449 temp_cascaded_table(current_n).category_id := x_shipmentrec.category_id;
1450 temp_cascaded_table(current_n).item_description := x_shipmentrec.item_description;
1451
1452 IF temp_cascaded_table(current_n).to_organization_id IS NULL THEN --{
1453 temp_cascaded_table(current_n).to_organization_id := x_shipmentrec.to_organization_id;
1454 END IF; --}
1455
1456 IF temp_cascaded_table(current_n).from_organization_id IS NULL THEN --{
1457 temp_cascaded_table(current_n).from_organization_id := x_shipmentrec.from_organization_id;
1458 END IF; --}
1459
1460 -- set the shipment num/header if
1461
1462 IF temp_cascaded_table(current_n).shipment_header_id IS NULL
1463 OR temp_cascaded_table(current_n).shipment_num IS NULL THEN
1464 temp_cascaded_table(current_n).shipment_header_id := x_shipmentrec.shipment_header_id;
1465 temp_cascaded_table(current_n).shipment_num := x_shipmentrec.shipment_num;
1466 END IF;
1467
1468 -- set the shipment line id
1469 IF temp_cascaded_table(current_n).shipment_line_id IS NULL THEN
1470 temp_cascaded_table(current_n).shipment_line_id := x_shipmentrec.shipment_line_id;
1471 END IF;
1472
1473 /* Bug 3684984.
1474 * We added requisition_line_id, po_line_location_id
1475 * and employee_id to the shipment cursor. Copy that
1476 * to temp_cascaded_table. This used to happen in
1477 * derive_io_shipment_info before.
1478 */
1479 IF temp_cascaded_table(current_n).requisition_line_id IS NULL THEN
1480 temp_cascaded_table(current_n).requisition_line_id := x_shipmentrec.requisition_line_id;
1481 END IF;
1482
1483 IF temp_cascaded_table(current_n).po_line_location_id IS NULL THEN
1484 temp_cascaded_table(current_n).po_line_location_id := x_shipmentrec.po_line_location_id;
1485 END IF;
1486
1487 IF temp_cascaded_table(current_n).employee_id IS NULL THEN
1488 temp_cascaded_table(current_n).employee_id := x_shipmentrec.employee_id;
1489 END IF;
1490
1491 /* End of bug 3684984. */
1492
1493 -- copy the distribution specific information only if this is a direct receipt.
1494 IF ( x_cascaded_table(n).transaction_type = 'DELIVER'
1495 OR NVL(x_cascaded_table(n).auto_transact_code, 'RECEIVE') = 'DELIVER') THEN --{
1496 temp_cascaded_table(current_n).destination_type_code := x_shipmentrec.destination_type_code;
1497 temp_cascaded_table(current_n).destination_context := x_shipmentrec.destination_type_code;
1498
1499 IF (NVL(temp_cascaded_table(current_n).deliver_to_location_id, 0) = 0) THEN
1500 temp_cascaded_table(current_n).deliver_to_location_id := x_shipmentrec.deliver_to_location_id;
1501 END IF;
1502
1503 /* bug 2392074 - if the deliver_to_person mentioned in the po_distributions is
1504 invalid or inactive at the time of receipt we need to clear the deliver to person,
1505 as this is an optional field. */
1506 IF (NVL(temp_cascaded_table(current_n).deliver_to_person_id, 0) = 0) THEN --{
1507 temp_cascaded_table(current_n).deliver_to_person_id := x_shipmentrec.deliver_to_person_id;
1508
1509 IF (temp_cascaded_table(current_n).deliver_to_person_id IS NOT NULL) THEN --{
1510 BEGIN
1511 SELECT NVL(MAX(hre.full_name), 'notfound')
1512 INTO x_full_name
1513 FROM hr_employees_current_v hre
1514 WHERE ( hre.inactive_date IS NULL
1515 OR hre.inactive_date > SYSDATE)
1516 AND hre.employee_id = temp_cascaded_table(current_n).deliver_to_person_id;
1517
1518 IF (x_full_name = 'notfound') THEN
1519 temp_cascaded_table(current_n).deliver_to_person_id := NULL;
1520 END IF;
1521 EXCEPTION
1522 WHEN NO_DATA_FOUND THEN
1523 temp_cascaded_table(current_n).deliver_to_person_id := NULL;
1524
1525 IF (g_asn_debug = 'Y') THEN
1526 asn_debug.put_line('the deliver to person entered in po is currently inactive');
1527 asn_debug.put_line(' so it is cleared off');
1528 END IF;
1529 WHEN OTHERS THEN
1530 temp_cascaded_table(current_n).deliver_to_person_id := NULL;
1531
1532 IF (g_asn_debug = 'Y') THEN
1533 asn_debug.put_line('some exception has occured');
1534 asn_debug.put_line('this exception is due to the po deliver to person');
1535 asn_debug.put_line('the deliver to person is optional');
1536 asn_debug.put_line('so cleared off the deliver to person');
1537 END IF;
1538 END;
1539 END IF; --}
1540 END IF; --}
1541
1542 IF (temp_cascaded_table(current_n).subinventory IS NULL) THEN
1543 temp_cascaded_table(current_n).subinventory := x_shipmentrec.to_subinventory;
1544 END IF;
1545
1546 -- bug 1361786
1547 IF (temp_cascaded_table(current_n).ussgl_transaction_code IS NULL) THEN
1548 temp_cascaded_table(current_n).ussgl_transaction_code := x_shipmentrec.ussgl_transaction_code;
1549 END IF;
1550 END IF; --} matches txn not deliver
1551
1552 current_n := current_n + 1;
1553
1554 IF (g_asn_debug = 'Y') THEN
1555 asn_debug.put_line('increment pointer by 1 ' || TO_CHAR(current_n));
1556 END IF;
1557 END IF; --} matches if insert into table
1558 END IF; --} matches shipmentdistributionrec.receipt_days_exception_code = none
1559 END LOOP; --}
1560
1561 -- OPM change.Bug# 3061052
1562 -- if original receiving transaction line is split and secondary quantity is specified then
1563 -- set secondary quantity for the split lines to NULL.
1564 /* INVCONV , removed opm installed flag . Punit Kumar */
1565 IF /* gml_process_flags.opm_installed = 1
1566 AND */ x_cascaded_table(n).secondary_quantity IS NOT NULL THEN
1567 IF temp_cascaded_table.COUNT > 1 THEN
1568 FOR j IN 1 .. temp_cascaded_table.COUNT LOOP
1569 temp_cascaded_table(j).secondary_quantity := NULL;
1570 END LOOP;
1571 END IF;
1572 END IF;
1573 /* end , INVCONV */
1574
1575 IF shipments%ISOPEN THEN
1576 CLOSE shipments;
1577 END IF;
1578
1579 IF count_shipments%ISOPEN THEN
1580 CLOSE count_shipments;
1581 END IF;
1582
1583 IF (g_asn_debug = 'Y') THEN
1584 asn_debug.put_line('exit derive_io_rcv_line_qty');
1585 END IF;
1586 EXCEPTION
1587 WHEN rcv_error_pkg.e_fatal_error THEN
1588 IF shipments%ISOPEN THEN
1589 CLOSE shipments;
1590 END IF;
1591
1592 IF count_shipments%ISOPEN THEN
1593 CLOSE count_shipments;
1594 END IF;
1595 WHEN OTHERS THEN
1596 IF shipments%ISOPEN THEN
1597 CLOSE shipments;
1598 END IF;
1599
1600 IF count_shipments%ISOPEN THEN
1601 CLOSE count_shipments;
1602 END IF;
1603
1604 rcv_error_pkg.set_sql_error_message('derive_io_rcv_line_qty', x_progress);
1605 x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1606 x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
1607
1608 IF (g_asn_debug = 'Y') THEN
1609 asn_debug.put_line(TO_CHAR(n));
1610 asn_debug.put_line(SQLERRM);
1611 asn_debug.put_line('error ' || x_progress);
1612 END IF;
1613 END derive_io_rcv_line_qty;
1614
1615 PROCEDURE derive_trans_del_line_quantity(
1616 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1617 n IN OUT NOCOPY BINARY_INTEGER,
1618 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type
1619 ) IS
1620 BEGIN
1621 -- logic should match inter-org transfer, so
1622 rcv_int_org_transfer.derive_trans_del_line_quantity(x_cascaded_table,
1623 n,
1624 temp_cascaded_table
1625 );
1626 END derive_trans_del_line_quantity;
1627
1628 PROCEDURE derive_io_correct_line_qty(
1629 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1630 n IN OUT NOCOPY BINARY_INTEGER,
1631 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type
1632 ) IS
1633 BEGIN
1634 -- logic is the same for inter_org xfer, so...
1635 rcv_int_org_transfer.derive_int_org_cor_line_qty(x_cascaded_table,
1636 n,
1637 temp_cascaded_table
1638 );
1639 END derive_io_correct_line_qty;
1640
1641 PROCEDURE derive_io_shipment_info(
1642 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1643 n IN OUT NOCOPY BINARY_INTEGER
1644 ) IS
1645 l_shipment_header_id NUMBER;
1646 l_shipment_line_id NUMBER;
1647 l_requisition_line_id NUMBER;
1648 l_ship_to_location_id NUMBER;
1649 l_subinventory VARCHAR2(255);
1650 l_po_line_location_id NUMBER;
1651 l_destination_type_code VARCHAR2(255);
1652 l_to_organization_id NUMBER;
1653 l_item_id NUMBER;
1654 l_category_id NUMBER;
1655 l_employee_id NUMBER;
1656 BEGIN
1657 IF ( x_cascaded_table(n).parent_transaction_id IS NULL
1658 OR x_cascaded_table(n).parent_transaction_id = 0) THEN
1659 SELECT rsh.shipment_header_id
1660 INTO l_shipment_header_id
1661 FROM rcv_shipment_headers rsh
1662 WHERE shipment_num = x_cascaded_table(n).shipment_num
1663 AND receipt_source_code = 'INTERNAL ORDER';
1664
1665 SELECT rsl.shipment_line_id
1666 INTO l_shipment_line_id
1667 FROM rcv_shipment_lines rsl
1668 WHERE rsl.shipment_header_id = l_shipment_header_id
1669 AND rsl.item_description = x_cascaded_table(n).item_description
1670 AND ROWNUM = 1;
1671 ELSE
1672 SELECT rt.shipment_header_id,
1673 rt.shipment_line_id
1674 INTO l_shipment_header_id,
1675 l_shipment_line_id
1676 FROM rcv_transactions rt
1677 WHERE transaction_id = x_cascaded_table(n).parent_transaction_id;
1678 END IF;
1679
1680 SELECT rsl.requisition_line_id,
1681 rsl.ship_to_location_id,
1682 rsl.to_subinventory,
1683 rsl.po_line_location_id,
1684 rsl.destination_type_code,
1685 rsl.to_organization_id,
1686 rsl.item_id,
1687 rsl.category_id,
1688 rsl.employee_id
1689 INTO l_requisition_line_id,
1690 l_ship_to_location_id,
1691 l_subinventory,
1692 l_po_line_location_id,
1693 l_destination_type_code,
1694 l_to_organization_id,
1695 l_item_id,
1696 l_category_id,
1697 l_employee_id
1698 FROM rcv_shipment_lines rsl
1699 WHERE rsl.shipment_header_id = l_shipment_header_id
1700 AND rsl.shipment_line_id = l_shipment_line_id;
1701
1702 x_cascaded_table(n).requisition_line_id := NVL(x_cascaded_table(n).requisition_line_id, l_requisition_line_id);
1703 x_cascaded_table(n).ship_to_location_id := NVL(x_cascaded_table(n).ship_to_location_id, l_ship_to_location_id);
1704 --bug 13684997: Do not derive subinventory value when transaction_type in ('ACCEPT', 'REJECT') and MOBILE_TXN = 'Y'
1705 IF NOT((x_cascaded_table(n).transaction_type IN('ACCEPT', 'REJECT')) AND x_cascaded_table(n).MOBILE_TXN = 'Y') THEN
1706 x_cascaded_table(n).subinventory := NVL(x_cascaded_table(n).subinventory, l_subinventory);
1707 END IF;
1708 --bug 13684997 end
1709 x_cascaded_table(n).po_line_location_id := NVL(x_cascaded_table(n).po_line_location_id, l_po_line_location_id);
1710 x_cascaded_table(n).destination_type_code := NVL(x_cascaded_table(n).destination_type_code, l_destination_type_code);
1711 x_cascaded_table(n).to_organization_id := NVL(x_cascaded_table(n).to_organization_id, l_to_organization_id);
1712 x_cascaded_table(n).item_id := NVL(x_cascaded_table(n).item_id, l_item_id);
1713 x_cascaded_table(n).category_id := NVL(x_cascaded_table(n).category_id, l_category_id);
1714 x_cascaded_table(n).employee_id := NVL(x_cascaded_table(n).employee_id, l_employee_id);
1715 END derive_io_shipment_info;
1716
1717 /* Procedure get_deliver_to_person_from_rsl() is added as part of Bug#6375015 fix.
1718 This procedure is called from validate_io_rcv_line().
1719 At this point of time, all the defaulting and derivation would have happened,
1720 so rcv_shipment_line_id would be available.
1721 This procedure tries to default the deliver_to_person_id mentioned in the rsl.
1722 If rcv_shipment_line_id is not known, it will try to get the value based on
1723 rcv_shipment_header_id only if the rsh has got only one rsl.
1724 */
1725 PROCEDURE get_deliver_to_person_from_rsl(
1726 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1727 n IN BINARY_INTEGER
1728 ) IS
1729 l_deliver_to_person_id NUMBER;
1730 l_rsl_count NUMBER;
1731 BEGIN
1732 --At this point shipment_line_id would haven been defaulted/derived
1733 IF (g_asn_debug = 'Y') THEN
1734 asn_debug.put_line('Inside get_deliver_to_person_from_rsl...');
1735 END IF;
1736 IF (x_cascaded_table(n).shipment_line_id is not null) THEN
1737 IF (g_asn_debug = 'Y') THEN
1738 asn_debug.put_line('Inside rsl is not null..rsl value :'||x_cascaded_table(n).shipment_line_id);
1739 END IF;
1740
1741 BEGIN
1742 select deliver_to_person_id
1743 into l_deliver_to_person_id
1744 from rcv_shipment_lines
1745 where shipment_line_id = x_cascaded_table(n).shipment_line_id;
1746
1747 x_cascaded_table(n).deliver_to_person_id := l_deliver_to_person_id;
1748 IF (g_asn_debug = 'Y') THEN
1749 asn_debug.put_line('defaulted deliver_to_person_id as:'||l_deliver_to_person_id);
1750 END IF;
1751 EXCEPTION
1752 WHEN OTHERS then
1753 IF (g_asn_debug = 'Y') THEN
1754 asn_debug.put_line('Exception occured while getting deliver_to_person_id thru rsl');
1755 END IF;
1756 END;
1757 ELSIF (x_cascaded_table(n).shipment_header_id is not null) THEN
1758 IF (g_asn_debug = 'Y') THEN
1759 asn_debug.put_line('Inside rsh not null..rsh value :'||x_cascaded_table(n).shipment_header_id);
1760 END IF;
1761
1762 BEGIN
1763 select count(shipment_line_id)
1764 into l_rsl_count
1765 from rcv_shipment_lines
1766 where shipment_header_id = x_cascaded_table(n).shipment_header_id;
1767
1768 IF (g_asn_debug = 'Y') THEN
1769 asn_debug.put_line('Inside rsh not null..l_rsl_count:'||l_rsl_count);
1770 END IF;
1771
1772 IF l_rsl_count = 1 THEN
1773 select deliver_to_person_id
1774 into l_deliver_to_person_id
1775 from rcv_shipment_lines
1776 where shipment_header_id = x_cascaded_table(n).shipment_header_id;
1777 x_cascaded_table(n).deliver_to_person_id := l_deliver_to_person_id;
1778 IF (g_asn_debug = 'Y') THEN
1779 asn_debug.put_line('defaulted deliver_to_person_id as:'||l_deliver_to_person_id);
1780 END IF;
1781 ELSE--l_rsl_count = 1
1782 IF (g_asn_debug = 'Y') THEN
1783 asn_debug.put_line('deliver_to_person_id not defalued as rsh has more than 1 rsl');
1784 END IF;
1785 END IF;--l_rsl_count = 1
1786 EXCEPTION
1787 WHEN OTHERS then
1788 IF (g_asn_debug = 'Y') THEN
1789 asn_debug.put_line('Exception occured while getting deliver_to_person_id thru rsh');
1790 END IF;
1791 END;
1792 END IF;--(x_cascaded_table(n).shipment_line_id is not null)
1793 END get_deliver_to_person_from_rsl;
1794
1795 /* Function get_deliver_to_person_from_rt() is added as part of Bug#6375015 fix.
1796 This function is called from validate_io_rcv_line() and it tries to default
1797 the deliver_to_person_id mentioned in the parent transaction(i.e from rcv_transactions).
1798 */
1799 FUNCTION get_deliver_to_person_from_rt(
1800 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1801 n IN BINARY_INTEGER
1802 ) RETURN NUMBER IS
1803 l_parent_deliver_to_person_id NUMBER := null;
1804 BEGIN
1805 select deliver_to_person_id
1806 into l_parent_deliver_to_person_id
1807 from rcv_transactions
1808 where transaction_id = x_cascaded_table(n).parent_transaction_id;
1809
1810 IF (g_asn_debug = 'Y') THEN
1811 asn_debug.put_line('Got deliver_to_person_id of parent txn as:'||l_parent_deliver_to_person_id);
1812 END IF;
1813 return l_parent_deliver_to_person_id;
1814 EXCEPTION
1815 WHEN OTHERS then
1816 IF (g_asn_debug = 'Y') THEN
1817 asn_debug.put_line('Exception occured in get_deliver_to_person_from_rt');
1818 END IF;
1819 return null;
1820 END get_deliver_to_person_from_rt;
1821
1822 /* Function get_deliver_to_person_from_rti() is added as part of Bug#6375015 fix.
1823 This function is called from validate_io_rcv_line() and it tries to default
1824 the deliver_to_person_id mentioned in the parent transaction(i.e from rcv_transactions_interface).
1825 */
1826
1827 FUNCTION get_deliver_to_person_from_rti(
1828 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
1829 n IN BINARY_INTEGER
1830 ) RETURN NUMBER IS
1831 l_parent_deliver_to_person_id NUMBER := null;
1832 BEGIN
1833 select deliver_to_person_id
1834 into l_parent_deliver_to_person_id
1835 from rcv_transactions_interface
1836 where interface_transaction_id = x_cascaded_table(n).parent_transaction_id;
1837
1838 IF (g_asn_debug = 'Y') THEN
1839 asn_debug.put_line('Got deliver_to_person_id of parent txn as:'||l_parent_deliver_to_person_id);
1840 END IF;
1841 return l_parent_deliver_to_person_id;
1842 EXCEPTION
1843 WHEN OTHERS then
1844 IF (g_asn_debug = 'Y') THEN
1845 asn_debug.put_line('Exception occured in get_deliver_to_person_from_rti');
1846 END IF;
1847 return null;
1848 END get_deliver_to_person_from_rti;
1849
1850 END rcv_int_order_pp_pvt;
[Home] [Help]