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