[Home] [Help]
1: PACKAGE BODY rcv_rma_transactions
2: /* $Header: RCVRMATB.pls 120.19.12020000.3 2013/03/19 11:00:10 xiameng ship $*/
3: AS
4: g_asn_debug VARCHAR2(1) := asn_debug.is_debug_on; -- Bug 9152790: rcv debug enhancement
5: x_progress VARCHAR2(3);
6: TYPE t_used_rma_line_amounts is table of number index by binary_integer;
7: g_used_rma_line_amounts t_used_rma_line_amounts;
8:
280: temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
281: x_header_record IN rcv_roi_preprocessor.header_rec_type
282: ) IS
283: BEGIN
284: IF (g_asn_debug = 'Y') THEN
285: asn_debug.put_line('Enter derive_rma_line');
286: asn_debug.put_line('Current pointer in actual table ' || TO_CHAR(n));
287: asn_debug.put_line('Current error status ' || x_cascaded_table(n).error_status);
288: asn_debug.put_line('To Organization Id ' || NVL(TO_CHAR(x_cascaded_table(n).to_organization_id), 'NULL'));
281: x_header_record IN rcv_roi_preprocessor.header_rec_type
282: ) IS
283: BEGIN
284: IF (g_asn_debug = 'Y') THEN
285: asn_debug.put_line('Enter derive_rma_line');
286: asn_debug.put_line('Current pointer in actual table ' || TO_CHAR(n));
287: asn_debug.put_line('Current error status ' || x_cascaded_table(n).error_status);
288: asn_debug.put_line('To Organization Id ' || NVL(TO_CHAR(x_cascaded_table(n).to_organization_id), 'NULL'));
289: asn_debug.put_line('To Organization Code ' || NVL(x_cascaded_table(n).to_organization_code, 'NULL'));
282: ) IS
283: BEGIN
284: IF (g_asn_debug = 'Y') THEN
285: asn_debug.put_line('Enter derive_rma_line');
286: asn_debug.put_line('Current pointer in actual table ' || TO_CHAR(n));
287: asn_debug.put_line('Current error status ' || x_cascaded_table(n).error_status);
288: asn_debug.put_line('To Organization Id ' || NVL(TO_CHAR(x_cascaded_table(n).to_organization_id), 'NULL'));
289: asn_debug.put_line('To Organization Code ' || NVL(x_cascaded_table(n).to_organization_code, 'NULL'));
290: END IF;
283: BEGIN
284: IF (g_asn_debug = 'Y') THEN
285: asn_debug.put_line('Enter derive_rma_line');
286: asn_debug.put_line('Current pointer in actual table ' || TO_CHAR(n));
287: asn_debug.put_line('Current error status ' || x_cascaded_table(n).error_status);
288: asn_debug.put_line('To Organization Id ' || NVL(TO_CHAR(x_cascaded_table(n).to_organization_id), 'NULL'));
289: asn_debug.put_line('To Organization Code ' || NVL(x_cascaded_table(n).to_organization_code, 'NULL'));
290: END IF;
291:
284: IF (g_asn_debug = 'Y') THEN
285: asn_debug.put_line('Enter derive_rma_line');
286: asn_debug.put_line('Current pointer in actual table ' || TO_CHAR(n));
287: asn_debug.put_line('Current error status ' || x_cascaded_table(n).error_status);
288: asn_debug.put_line('To Organization Id ' || NVL(TO_CHAR(x_cascaded_table(n).to_organization_id), 'NULL'));
289: asn_debug.put_line('To Organization Code ' || NVL(x_cascaded_table(n).to_organization_code, 'NULL'));
290: END IF;
291:
292: x_progress := '000';
285: asn_debug.put_line('Enter derive_rma_line');
286: asn_debug.put_line('Current pointer in actual table ' || TO_CHAR(n));
287: asn_debug.put_line('Current error status ' || x_cascaded_table(n).error_status);
288: asn_debug.put_line('To Organization Id ' || NVL(TO_CHAR(x_cascaded_table(n).to_organization_id), 'NULL'));
289: asn_debug.put_line('To Organization Code ' || NVL(x_cascaded_table(n).to_organization_code, 'NULL'));
290: END IF;
291:
292: x_progress := '000';
293: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
289: asn_debug.put_line('To Organization Code ' || NVL(x_cascaded_table(n).to_organization_code, 'NULL'));
290: END IF;
291:
292: x_progress := '000';
293: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
294: -- derive_location_info(x_cascaded_table, n);
295: rcv_roi_transaction.derive_location_info(x_cascaded_table, n);
296: rcv_roi_transaction.derive_ship_to_location_info(x_cascaded_table, n);
297: x_progress := '010';
294: -- derive_location_info(x_cascaded_table, n);
295: rcv_roi_transaction.derive_location_info(x_cascaded_table, n);
296: rcv_roi_transaction.derive_ship_to_location_info(x_cascaded_table, n);
297: x_progress := '010';
298: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
299: --derive_org_info(x_cascaded_table, n, x_header_record);
300: rcv_roi_transaction.derive_ship_to_org_info(x_cascaded_table,
301: n,
302: x_header_record
301: n,
302: x_header_record
303: );
304: x_progress := '020';
305: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
306: derive_customer_info(x_cascaded_table, n);
307: x_progress := '030';
308: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
309: derive_order_header_info(x_cascaded_table, n);
304: x_progress := '020';
305: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
306: derive_customer_info(x_cascaded_table, n);
307: x_progress := '030';
308: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
309: derive_order_header_info(x_cascaded_table, n);
310: x_progress := '040';
311: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
312: -- derive_item_info(x_cascaded_table, n);
307: x_progress := '030';
308: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
309: derive_order_header_info(x_cascaded_table, n);
310: x_progress := '040';
311: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
312: -- derive_item_info(x_cascaded_table, n);
313: rcv_roi_transaction.derive_item_info(x_cascaded_table, n);
314: x_progress := '050';
315: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
311: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
312: -- derive_item_info(x_cascaded_table, n);
313: rcv_roi_transaction.derive_item_info(x_cascaded_table, n);
314: x_progress := '050';
315: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
316: derive_order_line_info(x_cascaded_table, n);
317: x_progress := '060';
318: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
319: derive_document_line_info(x_cascaded_table, n);
314: x_progress := '050';
315: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
316: derive_order_line_info(x_cascaded_table, n);
317: x_progress := '060';
318: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
319: derive_document_line_info(x_cascaded_table, n);
320: x_progress := '070';
321: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
322: derive_uom_info(x_cascaded_table, n);
317: x_progress := '060';
318: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
319: derive_document_line_info(x_cascaded_table, n);
320: x_progress := '070';
321: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
322: derive_uom_info(x_cascaded_table, n);
323: x_progress := '080';
324: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
325: derive_transit_org_info(x_cascaded_table, n);
320: x_progress := '070';
321: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
322: derive_uom_info(x_cascaded_table, n);
323: x_progress := '080';
324: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
325: derive_transit_org_info(x_cascaded_table, n);
326: x_progress := '090';
327: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
328: -- derive_routing_info(x_cascaded_table, n);
323: x_progress := '080';
324: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
325: derive_transit_org_info(x_cascaded_table, n);
326: x_progress := '090';
327: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
328: -- derive_routing_info(x_cascaded_table, n);
329: rcv_roi_transaction.derive_routing_header_info(x_cascaded_table, n);
330: rcv_roi_transaction.derive_routing_step_info(x_cascaded_table, n);
331: x_progress := '100';
328: -- derive_routing_info(x_cascaded_table, n);
329: rcv_roi_transaction.derive_routing_header_info(x_cascaded_table, n);
330: rcv_roi_transaction.derive_routing_step_info(x_cascaded_table, n);
331: x_progress := '100';
332: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
333: derive_deliver_to_info(x_cascaded_table, n);
334: x_progress := '120';
335: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
336: -- derive_locator_info(x_cascaded_table, n);
331: x_progress := '100';
332: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
333: derive_deliver_to_info(x_cascaded_table, n);
334: x_progress := '120';
335: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
336: -- derive_locator_info(x_cascaded_table, n);
337: rcv_roi_transaction.derive_to_locator_id(x_cascaded_table, n);
338: x_progress := '130';
339: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
335: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
336: -- derive_locator_info(x_cascaded_table, n);
337: rcv_roi_transaction.derive_to_locator_id(x_cascaded_table, n);
338: x_progress := '130';
339: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
340: -- derive_reason_info(x_cascaded_table, n);
341: rcv_roi_transaction.derive_reason_info(x_cascaded_table, n);
342: x_progress := '140';
343: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
339: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
340: -- derive_reason_info(x_cascaded_table, n);
341: rcv_roi_transaction.derive_reason_info(x_cascaded_table, n);
342: x_progress := '140';
343: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
344: derive_auto_transact_info(x_cascaded_table, n);
345: x_progress := '150';
346: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
347: explode_line_quantity(x_cascaded_table,
342: x_progress := '140';
343: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
344: derive_auto_transact_info(x_cascaded_table, n);
345: x_progress := '150';
346: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
347: explode_line_quantity(x_cascaded_table,
348: n,
349: temp_cascaded_table,
350: x_header_record
349: temp_cascaded_table,
350: x_header_record
351: );
352: x_progress := '170';
353: asn_debug.put_line('RMA derive ' || x_progress || ' error status: ' || NVL(x_cascaded_table(n).error_status, 'NULL'));
354: END derive_rma_line;
355:
356: PROCEDURE derive_rma_trans_del(
357: x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
359: temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
360: x_header_record IN rcv_roi_preprocessor.header_rec_type
361: ) IS
362: BEGIN
363: IF (g_asn_debug = 'Y') THEN
364: asn_debug.put_line('enter derive_cust_trans_del ');
365: END IF;
366:
367: /* Derive the to_org_id */
360: x_header_record IN rcv_roi_preprocessor.header_rec_type
361: ) IS
362: BEGIN
363: IF (g_asn_debug = 'Y') THEN
364: asn_debug.put_line('enter derive_cust_trans_del ');
365: END IF;
366:
367: /* Derive the to_org_id */
368: derive_org_info(x_cascaded_table,
370: x_header_record
371: );
372:
373: IF (x_cascaded_table(n).unit_of_measure IS NOT NULL) THEN
374: IF (g_asn_debug = 'Y') THEN
375: asn_debug.put_line('X_progress ' || x_progress);
376: END IF;
377:
378: SELECT muom.uom_code
371: );
372:
373: IF (x_cascaded_table(n).unit_of_measure IS NOT NULL) THEN
374: IF (g_asn_debug = 'Y') THEN
375: asn_debug.put_line('X_progress ' || x_progress);
376: END IF;
377:
378: SELECT muom.uom_code
379: INTO x_cascaded_table(n).uom_code
379: INTO x_cascaded_table(n).uom_code
380: FROM mtl_units_of_measure muom
381: WHERE muom.unit_of_measure = x_cascaded_table(n).unit_of_measure;
382: ELSE
383: IF (g_asn_debug = 'Y') THEN
384: asn_debug.put_line('uom_code not derived as unit_of_measure is null');
385: END IF;
386: END IF;
387:
380: FROM mtl_units_of_measure muom
381: WHERE muom.unit_of_measure = x_cascaded_table(n).unit_of_measure;
382: ELSE
383: IF (g_asn_debug = 'Y') THEN
384: asn_debug.put_line('uom_code not derived as unit_of_measure is null');
385: END IF;
386: END IF;
387:
388: x_progress := '026';
415: temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
416: x_header_record IN rcv_roi_preprocessor.header_rec_type
417: ) IS
418: BEGIN
419: IF (g_asn_debug = 'Y') THEN
420: asn_debug.put_line('enter derive_correction_line ');
421: END IF;
422:
423: /* Derive the to_org_id */
416: x_header_record IN rcv_roi_preprocessor.header_rec_type
417: ) IS
418: BEGIN
419: IF (g_asn_debug = 'Y') THEN
420: asn_debug.put_line('enter derive_correction_line ');
421: END IF;
422:
423: /* Derive the to_org_id */
424: derive_org_info(x_cascaded_table,
426: x_header_record
427: );
428:
429: IF (x_cascaded_table(n).unit_of_measure IS NOT NULL) THEN
430: IF (g_asn_debug = 'Y') THEN
431: asn_debug.put_line('X_progress ' || x_progress);
432: END IF;
433:
434: SELECT muom.uom_code
427: );
428:
429: IF (x_cascaded_table(n).unit_of_measure IS NOT NULL) THEN
430: IF (g_asn_debug = 'Y') THEN
431: asn_debug.put_line('X_progress ' || x_progress);
432: END IF;
433:
434: SELECT muom.uom_code
435: INTO x_cascaded_table(n).uom_code
435: INTO x_cascaded_table(n).uom_code
436: FROM mtl_units_of_measure muom
437: WHERE muom.unit_of_measure = x_cascaded_table(n).unit_of_measure;
438: ELSE
439: IF (g_asn_debug = 'Y') THEN
440: asn_debug.put_line('uom_code not dereived as unit_of_measure is null');
441: END IF;
442: END IF;
443:
436: FROM mtl_units_of_measure muom
437: WHERE muom.unit_of_measure = x_cascaded_table(n).unit_of_measure;
438: ELSE
439: IF (g_asn_debug = 'Y') THEN
440: asn_debug.put_line('uom_code not dereived as unit_of_measure is null');
441: END IF;
442: END IF;
443:
444: x_progress := '091';
463: x_default_locator_id NUMBER;
464: x_success BOOLEAN;
465: x_tax_name VARCHAR2(50); -- Bug 6331613
466: BEGIN
467: IF (g_asn_debug = 'Y') THEN
468: asn_debug.put_line('In default_rma_line');
469: END IF;
470:
471: x_progress := '000';
464: x_success BOOLEAN;
465: x_tax_name VARCHAR2(50); -- Bug 6331613
466: BEGIN
467: IF (g_asn_debug = 'Y') THEN
468: asn_debug.put_line('In default_rma_line');
469: END IF;
470:
471: x_progress := '000';
472: -- set default_rma values
498: END IF;
499:
500: x_progress := '010';
501:
502: IF (g_asn_debug = 'Y') THEN
503: asn_debug.put_line('Exit default_rma_line');
504: END IF;
505: END default_rma_line;
506:
499:
500: x_progress := '010';
501:
502: IF (g_asn_debug = 'Y') THEN
503: asn_debug.put_line('Exit default_rma_line');
504: END IF;
505: END default_rma_line;
506:
507: /*===========================================================================
514: n IN BINARY_INTEGER,
515: x_header_record IN rcv_roi_preprocessor.header_rec_type
516: ) IS
517: BEGIN
518: IF (g_asn_debug = 'Y') THEN
519: asn_debug.put_line('Enter validate_rma_line');
520: END IF;
521:
522: x_progress := '000';
515: x_header_record IN rcv_roi_preprocessor.header_rec_type
516: ) IS
517: BEGIN
518: IF (g_asn_debug = 'Y') THEN
519: asn_debug.put_line('Enter validate_rma_line');
520: END IF;
521:
522: x_progress := '000';
523:
554: x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_unexp_error;
555: rcv_error_pkg.set_sql_error_message('validate_rma_line', x_progress);
556: x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
557:
558: IF (g_asn_debug = 'Y') THEN
559: asn_debug.put_line('I have hit an exception');
560: asn_debug.put_line(SQLERRM);
561: asn_debug.put_line('Exit validate_rma_line');
562: END IF;
555: rcv_error_pkg.set_sql_error_message('validate_rma_line', x_progress);
556: x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
557:
558: IF (g_asn_debug = 'Y') THEN
559: asn_debug.put_line('I have hit an exception');
560: asn_debug.put_line(SQLERRM);
561: asn_debug.put_line('Exit validate_rma_line');
562: END IF;
563: END validate_rma_line;
556: x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
557:
558: IF (g_asn_debug = 'Y') THEN
559: asn_debug.put_line('I have hit an exception');
560: asn_debug.put_line(SQLERRM);
561: asn_debug.put_line('Exit validate_rma_line');
562: END IF;
563: END validate_rma_line;
564:
557:
558: IF (g_asn_debug = 'Y') THEN
559: asn_debug.put_line('I have hit an exception');
560: asn_debug.put_line(SQLERRM);
561: asn_debug.put_line('Exit validate_rma_line');
562: END IF;
563: END validate_rma_line;
564:
565: /**
588: FROM mtl_system_items
589: WHERE mtl_system_items.inventory_item_id = x_cascaded_table(n).item_id
590: AND mtl_system_items.organization_id = x_cascaded_table(n).to_organization_id;
591:
592: IF (g_asn_debug = 'Y') THEN
593: asn_debug.put_line('Primary UOM: ' || x_cascaded_table(n).primary_unit_of_measure);
594: END IF;
595: EXCEPTION
596: WHEN NO_DATA_FOUND THEN
589: WHERE mtl_system_items.inventory_item_id = x_cascaded_table(n).item_id
590: AND mtl_system_items.organization_id = x_cascaded_table(n).to_organization_id;
591:
592: IF (g_asn_debug = 'Y') THEN
593: asn_debug.put_line('Primary UOM: ' || x_cascaded_table(n).primary_unit_of_measure);
594: END IF;
595: EXCEPTION
596: WHEN NO_DATA_FOUND THEN
597: x_cascaded_table(n).error_status := 'W';
596: WHEN NO_DATA_FOUND THEN
597: x_cascaded_table(n).error_status := 'W';
598: x_cascaded_table(n).error_message := 'Need an error message';
599:
600: IF (g_asn_debug = 'Y') THEN
601: asn_debug.put_line('Primary UOM error');
602: END IF;
603: END;
604: END IF;
597: x_cascaded_table(n).error_status := 'W';
598: x_cascaded_table(n).error_message := 'Need an error message';
599:
600: IF (g_asn_debug = 'Y') THEN
601: asn_debug.put_line('Primary UOM error');
602: END IF;
603: END;
604: END IF;
605:
604: END IF;
605:
606: -- uom_code
607: IF (x_cascaded_table(n).unit_of_measure IS NOT NULL) THEN
608: IF (g_asn_debug = 'Y') THEN
609: asn_debug.put_line('X_progress ' || x_progress);
610: END IF;
611:
612: SELECT muom.uom_code
605:
606: -- uom_code
607: IF (x_cascaded_table(n).unit_of_measure IS NOT NULL) THEN
608: IF (g_asn_debug = 'Y') THEN
609: asn_debug.put_line('X_progress ' || x_progress);
610: END IF;
611:
612: SELECT muom.uom_code
613: INTO x_cascaded_table(n).uom_code
613: INTO x_cascaded_table(n).uom_code
614: FROM mtl_units_of_measure muom
615: WHERE muom.unit_of_measure = x_cascaded_table(n).unit_of_measure;
616: ELSE
617: IF (g_asn_debug = 'Y') THEN
618: asn_debug.put_line('uom_code not derived as unit_of_measure is null');
619: END IF;
620: END IF;
621: END derive_uom_info;
614: FROM mtl_units_of_measure muom
615: WHERE muom.unit_of_measure = x_cascaded_table(n).unit_of_measure;
616: ELSE
617: IF (g_asn_debug = 'Y') THEN
618: asn_debug.put_line('uom_code not derived as unit_of_measure is null');
619: END IF;
620: END IF;
621: END derive_uom_info;
622:
636: IF x_cascaded_table(n).error_status IN('S', 'W')
637: AND x_cascaded_table(n).oe_order_header_id IS NULL
638: AND x_cascaded_table(n).oe_order_num IS NOT NULL
639: AND x_cascaded_table(n).to_organization_id IS NOT NULL THEN
640: IF (g_asn_debug = 'Y') THEN
641: asn_debug.put_line('Deriving order_header_id');
642: END IF;
643:
644: -- bug 3224001: change query to get the correct header for the given order_num
637: AND x_cascaded_table(n).oe_order_header_id IS NULL
638: AND x_cascaded_table(n).oe_order_num IS NOT NULL
639: AND x_cascaded_table(n).to_organization_id IS NOT NULL THEN
640: IF (g_asn_debug = 'Y') THEN
641: asn_debug.put_line('Deriving order_header_id');
642: END IF;
643:
644: -- bug 3224001: change query to get the correct header for the given order_num
645:
661: FROM oe_order_lines_all oela
662: WHERE oela.header_id = oeh.header_id
663: AND oela.ship_from_org_id = x_cascaded_table(n).to_organization_id));
664:
665: IF (g_asn_debug = 'Y') THEN
666: asn_debug.put_line('Derived oe_order_header_id ' || x_cascaded_table(n).oe_order_header_id);
667: END IF;
668: END IF;
669: EXCEPTION
662: WHERE oela.header_id = oeh.header_id
663: AND oela.ship_from_org_id = x_cascaded_table(n).to_organization_id));
664:
665: IF (g_asn_debug = 'Y') THEN
666: asn_debug.put_line('Derived oe_order_header_id ' || x_cascaded_table(n).oe_order_header_id);
667: END IF;
668: END IF;
669: EXCEPTION
670: WHEN NO_DATA_FOUND THEN
667: END IF;
668: END IF;
669: EXCEPTION
670: WHEN NO_DATA_FOUND THEN
671: IF (g_asn_debug = 'Y') THEN
672: asn_debug.put_line('Cannot derive order_header_id - no data found');
673: END IF;
674:
675: x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
668: END IF;
669: EXCEPTION
670: WHEN NO_DATA_FOUND THEN
671: IF (g_asn_debug = 'Y') THEN
672: asn_debug.put_line('Cannot derive order_header_id - no data found');
673: END IF;
674:
675: x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
676: rcv_error_pkg.set_error_message('RCV_ITEM_ORDER_HEADER_ID', x_cascaded_table(n).error_message);
676: rcv_error_pkg.set_error_message('RCV_ITEM_ORDER_HEADER_ID', x_cascaded_table(n).error_message);
677: rcv_error_pkg.set_token('NUMBER', x_cascaded_table(n).oe_order_num);
678: rcv_error_pkg.log_interface_error('OE_ORDER_NUM', FALSE);
679: WHEN TOO_MANY_ROWS THEN
680: IF (g_asn_debug = 'Y') THEN
681: asn_debug.put_line('Cannot derive order_header_id - too many rows');
682: END IF;
683:
684: x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
677: rcv_error_pkg.set_token('NUMBER', x_cascaded_table(n).oe_order_num);
678: rcv_error_pkg.log_interface_error('OE_ORDER_NUM', FALSE);
679: WHEN TOO_MANY_ROWS THEN
680: IF (g_asn_debug = 'Y') THEN
681: asn_debug.put_line('Cannot derive order_header_id - too many rows');
682: END IF;
683:
684: x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
685: rcv_error_pkg.set_error_message('RCV_ITEM_ORDER_HEADER_ID', x_cascaded_table(n).error_message);
714: AND flow_status_code = 'AWAITING_RETURN';
715:
716: x_cascaded_table(n).oe_order_line_id := my_line_id;
717:
718: IF g_asn_debug = 'Y' THEN
719: asn_debug.put_line('Derived oe_order_line_id ' || TO_CHAR(x_cascaded_table(n).oe_order_line_id));
720: END IF;
721:
722: IF x_cascaded_table(n).item_id IS NULL THEN
715:
716: x_cascaded_table(n).oe_order_line_id := my_line_id;
717:
718: IF g_asn_debug = 'Y' THEN
719: asn_debug.put_line('Derived oe_order_line_id ' || TO_CHAR(x_cascaded_table(n).oe_order_line_id));
720: END IF;
721:
722: IF x_cascaded_table(n).item_id IS NULL THEN
723: x_cascaded_table(n).item_id := my_item_id;
721:
722: IF x_cascaded_table(n).item_id IS NULL THEN
723: x_cascaded_table(n).item_id := my_item_id;
724:
725: IF g_asn_debug = 'Y' THEN
726: asn_debug.put_line('Derived item_id ' || TO_CHAR(x_cascaded_table(n).item_id));
727: END IF;
728: END IF;
729: END IF;
722: IF x_cascaded_table(n).item_id IS NULL THEN
723: x_cascaded_table(n).item_id := my_item_id;
724:
725: IF g_asn_debug = 'Y' THEN
726: asn_debug.put_line('Derived item_id ' || TO_CHAR(x_cascaded_table(n).item_id));
727: END IF;
728: END IF;
729: END IF;
730: EXCEPTION
763: BEGIN
764: -- derive customer_id from customer_account_number
765: IF x_cascaded_table(n).customer_id IS NULL
766: AND x_cascaded_table(n).customer_account_number IS NOT NULL THEN
767: IF (g_asn_debug = 'Y') THEN
768: asn_debug.put_line('Deriving customer_id from customer_account_number');
769: END IF;
770:
771: SELECT acct.cust_account_id
764: -- derive customer_id from customer_account_number
765: IF x_cascaded_table(n).customer_id IS NULL
766: AND x_cascaded_table(n).customer_account_number IS NOT NULL THEN
767: IF (g_asn_debug = 'Y') THEN
768: asn_debug.put_line('Deriving customer_id from customer_account_number');
769: END IF;
770:
771: SELECT acct.cust_account_id
772: INTO x_cascaded_table(n).customer_id
772: INTO x_cascaded_table(n).customer_id
773: FROM hz_cust_accounts acct
774: WHERE acct.account_number = x_cascaded_table(n).customer_account_number;
775:
776: IF (g_asn_debug = 'Y') THEN
777: asn_debug.put_line('Derived customer_id ' || x_cascaded_table(n).customer_id);
778: END IF;
779: END IF;
780:
773: FROM hz_cust_accounts acct
774: WHERE acct.account_number = x_cascaded_table(n).customer_account_number;
775:
776: IF (g_asn_debug = 'Y') THEN
777: asn_debug.put_line('Derived customer_id ' || x_cascaded_table(n).customer_id);
778: END IF;
779: END IF;
780:
781: -- derive customer_id from customer_party_name if name is unique
780:
781: -- derive customer_id from customer_party_name if name is unique
782: IF x_cascaded_table(n).customer_id IS NULL
783: AND x_cascaded_table(n).customer_party_name IS NOT NULL THEN
784: IF (g_asn_debug = 'Y') THEN
785: asn_debug.put_line('Deriving customer_id from customer_account_number');
786: END IF;
787:
788: BEGIN
781: -- derive customer_id from customer_party_name if name is unique
782: IF x_cascaded_table(n).customer_id IS NULL
783: AND x_cascaded_table(n).customer_party_name IS NOT NULL THEN
784: IF (g_asn_debug = 'Y') THEN
785: asn_debug.put_line('Deriving customer_id from customer_account_number');
786: END IF;
787:
788: BEGIN
789: SELECT acct.cust_account_id
796: WHEN TOO_MANY_ROWS THEN
797: NULL;
798: END;
799:
800: IF (g_asn_debug = 'Y') THEN
801: asn_debug.put_line('Derived customer_id ' || x_cascaded_table(n).customer_id);
802: END IF;
803: END IF;
804:
797: NULL;
798: END;
799:
800: IF (g_asn_debug = 'Y') THEN
801: asn_debug.put_line('Derived customer_id ' || x_cascaded_table(n).customer_id);
802: END IF;
803: END IF;
804:
805: -- derive customer_site_id from from_organization_id
804:
805: -- derive customer_site_id from from_organization_id
806: IF x_cascaded_table(n).customer_site_id IS NULL
807: AND x_cascaded_table(n).from_organization_id IS NOT NULL THEN
808: IF (g_asn_debug = 'Y') THEN
809: asn_debug.put_line('Deriving customer_site_id from from_organization_id');
810: END IF;
811:
812: x_cascaded_table(n).customer_account_number := x_cascaded_table(n).from_organization_id;
805: -- derive customer_site_id from from_organization_id
806: IF x_cascaded_table(n).customer_site_id IS NULL
807: AND x_cascaded_table(n).from_organization_id IS NOT NULL THEN
808: IF (g_asn_debug = 'Y') THEN
809: asn_debug.put_line('Deriving customer_site_id from from_organization_id');
810: END IF;
811:
812: x_cascaded_table(n).customer_account_number := x_cascaded_table(n).from_organization_id;
813:
810: END IF;
811:
812: x_cascaded_table(n).customer_account_number := x_cascaded_table(n).from_organization_id;
813:
814: IF (g_asn_debug = 'Y') THEN
815: asn_debug.put_line('Derived customer_site_id ' || x_cascaded_table(n).customer_site_id);
816: END IF;
817: END IF;
818: END derive_customer_info;
811:
812: x_cascaded_table(n).customer_account_number := x_cascaded_table(n).from_organization_id;
813:
814: IF (g_asn_debug = 'Y') THEN
815: asn_debug.put_line('Derived customer_site_id ' || x_cascaded_table(n).customer_site_id);
816: END IF;
817: END IF;
818: END derive_customer_info;
819:
827: -- derive from location_info
828: IF x_cascaded_table(n).to_organization_id IS NULL
829: AND x_cascaded_table(n).to_organization_code IS NULL
830: AND x_cascaded_table(n).error_status IN('S', 'W') THEN
831: IF (g_asn_debug = 'Y') THEN
832: asn_debug.put_line('Attempting to derive the org from the ship to location');
833: END IF;
834:
835: IF (x_cascaded_table(n).ship_to_location_id IS NOT NULL) THEN
828: IF x_cascaded_table(n).to_organization_id IS NULL
829: AND x_cascaded_table(n).to_organization_code IS NULL
830: AND x_cascaded_table(n).error_status IN('S', 'W') THEN
831: IF (g_asn_debug = 'Y') THEN
832: asn_debug.put_line('Attempting to derive the org from the ship to location');
833: END IF;
834:
835: IF (x_cascaded_table(n).ship_to_location_id IS NOT NULL) THEN
836: SELECT MAX(org.organization_id)
839: HR_ALL_ORGANIZATION_UNITS org --Bug 5217526. Earlier used org_organization_definitions
840: WHERE x_cascaded_table(n).ship_to_location_id = hl.location_id
841: AND hl.inventory_organization_id = org.organization_id;
842:
843: IF (g_asn_debug = 'Y') THEN
844: asn_debug.put_line('Set Org using location id ');
845: END IF;
846: ELSIF(x_cascaded_table(n).ship_to_location_code IS NOT NULL) THEN
847: SELECT MAX(org.organization_id)
840: WHERE x_cascaded_table(n).ship_to_location_id = hl.location_id
841: AND hl.inventory_organization_id = org.organization_id;
842:
843: IF (g_asn_debug = 'Y') THEN
844: asn_debug.put_line('Set Org using location id ');
845: END IF;
846: ELSIF(x_cascaded_table(n).ship_to_location_code IS NOT NULL) THEN
847: SELECT MAX(org.organization_id)
848: INTO x_cascaded_table(n).to_organization_code
850: HR_ALL_ORGANIZATION_UNITS org --Bug 5217526. Earlier used org_organization_definitions
851: WHERE x_cascaded_table(n).ship_to_location_code = hl.location_code
852: AND hl.inventory_organization_id = org.organization_id;
853:
854: IF (g_asn_debug = 'Y') THEN
855: asn_debug.put_line('Set Org using location code ');
856: END IF;
857: END IF;
858: END IF;
851: WHERE x_cascaded_table(n).ship_to_location_code = hl.location_code
852: AND hl.inventory_organization_id = org.organization_id;
853:
854: IF (g_asn_debug = 'Y') THEN
855: asn_debug.put_line('Set Org using location code ');
856: END IF;
857: END IF;
858: END IF;
859:
860: -- derive from to_organization_code
861: IF x_cascaded_table(n).error_status IN('S', 'W')
862: AND x_cascaded_table(n).to_organization_id IS NULL
863: AND x_cascaded_table(n).to_organization_code IS NOT NULL THEN
864: IF (g_asn_debug = 'Y') THEN
865: asn_debug.put_line('X_Progress ' || x_progress);
866: END IF;
867:
868: ship_to_org_record.organization_code := x_cascaded_table(n).to_organization_code;
861: IF x_cascaded_table(n).error_status IN('S', 'W')
862: AND x_cascaded_table(n).to_organization_id IS NULL
863: AND x_cascaded_table(n).to_organization_code IS NOT NULL THEN
864: IF (g_asn_debug = 'Y') THEN
865: asn_debug.put_line('X_Progress ' || x_progress);
866: END IF;
867:
868: ship_to_org_record.organization_code := x_cascaded_table(n).to_organization_code;
869: ship_to_org_record.organization_id := x_cascaded_table(n).to_organization_id;
869: ship_to_org_record.organization_id := x_cascaded_table(n).to_organization_id;
870: ship_to_org_record.error_record.error_status := 'S';
871: ship_to_org_record.error_record.error_message := NULL;
872:
873: IF (g_asn_debug = 'Y') THEN
874: asn_debug.put_line('Into Derive Organization Record Procedure');
875: END IF;
876:
877: po_orgs_sv.derive_org_info(ship_to_org_record);
870: ship_to_org_record.error_record.error_status := 'S';
871: ship_to_org_record.error_record.error_message := NULL;
872:
873: IF (g_asn_debug = 'Y') THEN
874: asn_debug.put_line('Into Derive Organization Record Procedure');
875: END IF;
876:
877: po_orgs_sv.derive_org_info(ship_to_org_record);
878:
875: END IF;
876:
877: po_orgs_sv.derive_org_info(ship_to_org_record);
878:
879: IF (g_asn_debug = 'Y') THEN
880: asn_debug.put_line('Debug Output after organization procedure');
881: asn_debug.put_line(ship_to_org_record.organization_code);
882: asn_debug.put_line(TO_CHAR(ship_to_org_record.organization_id));
883: asn_debug.put_line(ship_to_org_record.error_record.error_status);
876:
877: po_orgs_sv.derive_org_info(ship_to_org_record);
878:
879: IF (g_asn_debug = 'Y') THEN
880: asn_debug.put_line('Debug Output after organization procedure');
881: asn_debug.put_line(ship_to_org_record.organization_code);
882: asn_debug.put_line(TO_CHAR(ship_to_org_record.organization_id));
883: asn_debug.put_line(ship_to_org_record.error_record.error_status);
884: asn_debug.put_line('Debug organization output over');
877: po_orgs_sv.derive_org_info(ship_to_org_record);
878:
879: IF (g_asn_debug = 'Y') THEN
880: asn_debug.put_line('Debug Output after organization procedure');
881: asn_debug.put_line(ship_to_org_record.organization_code);
882: asn_debug.put_line(TO_CHAR(ship_to_org_record.organization_id));
883: asn_debug.put_line(ship_to_org_record.error_record.error_status);
884: asn_debug.put_line('Debug organization output over');
885: END IF;
878:
879: IF (g_asn_debug = 'Y') THEN
880: asn_debug.put_line('Debug Output after organization procedure');
881: asn_debug.put_line(ship_to_org_record.organization_code);
882: asn_debug.put_line(TO_CHAR(ship_to_org_record.organization_id));
883: asn_debug.put_line(ship_to_org_record.error_record.error_status);
884: asn_debug.put_line('Debug organization output over');
885: END IF;
886:
879: IF (g_asn_debug = 'Y') THEN
880: asn_debug.put_line('Debug Output after organization procedure');
881: asn_debug.put_line(ship_to_org_record.organization_code);
882: asn_debug.put_line(TO_CHAR(ship_to_org_record.organization_id));
883: asn_debug.put_line(ship_to_org_record.error_record.error_status);
884: asn_debug.put_line('Debug organization output over');
885: END IF;
886:
887: x_cascaded_table(n).to_organization_code := ship_to_org_record.organization_code;
880: asn_debug.put_line('Debug Output after organization procedure');
881: asn_debug.put_line(ship_to_org_record.organization_code);
882: asn_debug.put_line(TO_CHAR(ship_to_org_record.organization_id));
883: asn_debug.put_line(ship_to_org_record.error_record.error_status);
884: asn_debug.put_line('Debug organization output over');
885: END IF;
886:
887: x_cascaded_table(n).to_organization_code := ship_to_org_record.organization_code;
888: x_cascaded_table(n).to_organization_id := ship_to_org_record.organization_id;
892:
893: -- couldn't derive, default from header instead
894: IF x_cascaded_table(n).error_status IN('S', 'W')
895: AND x_cascaded_table(n).to_organization_id IS NULL THEN
896: IF (g_asn_debug = 'Y') THEN
897: asn_debug.put_line('Will default org id from header ' || x_header_record.header_record.ship_to_organization_id);
898: END IF;
899:
900: x_cascaded_table(n).to_organization_id := x_header_record.header_record.ship_to_organization_id;
893: -- couldn't derive, default from header instead
894: IF x_cascaded_table(n).error_status IN('S', 'W')
895: AND x_cascaded_table(n).to_organization_id IS NULL THEN
896: IF (g_asn_debug = 'Y') THEN
897: asn_debug.put_line('Will default org id from header ' || x_header_record.header_record.ship_to_organization_id);
898: END IF;
899:
900: x_cascaded_table(n).to_organization_id := x_header_record.header_record.ship_to_organization_id;
901: END IF;
899:
900: x_cascaded_table(n).to_organization_id := x_header_record.header_record.ship_to_organization_id;
901: END IF;
902:
903: IF (g_asn_debug = 'Y') THEN
904: asn_debug.put_line('Derived to_organization_id ' || x_cascaded_table(n).to_organization_id);
905: END IF;
906: END derive_org_info;
907:
900: x_cascaded_table(n).to_organization_id := x_header_record.header_record.ship_to_organization_id;
901: END IF;
902:
903: IF (g_asn_debug = 'Y') THEN
904: asn_debug.put_line('Derived to_organization_id ' || x_cascaded_table(n).to_organization_id);
905: END IF;
906: END derive_org_info;
907:
908: PROCEDURE derive_transit_org_info(
913: BEGIN
914: IF (x_cascaded_table(n).error_status IN('S', 'W'))
915: AND ( x_cascaded_table(n).intransit_owning_org_id IS NULL
916: AND x_cascaded_table(n).intransit_owning_org_code IS NOT NULL) THEN
917: IF (g_asn_debug = 'Y') THEN
918: asn_debug.put_line('X_progress ' || x_progress);
919: END IF;
920:
921: transit_org_record.organization_code := x_cascaded_table(n).intransit_owning_org_code;
914: IF (x_cascaded_table(n).error_status IN('S', 'W'))
915: AND ( x_cascaded_table(n).intransit_owning_org_id IS NULL
916: AND x_cascaded_table(n).intransit_owning_org_code IS NOT NULL) THEN
917: IF (g_asn_debug = 'Y') THEN
918: asn_debug.put_line('X_progress ' || x_progress);
919: END IF;
920:
921: transit_org_record.organization_code := x_cascaded_table(n).intransit_owning_org_code;
922: transit_org_record.organization_id := x_cascaded_table(n).intransit_owning_org_id;
922: transit_org_record.organization_id := x_cascaded_table(n).intransit_owning_org_id;
923: transit_org_record.error_record.error_status := 'S';
924: transit_org_record.error_record.error_message := NULL;
925:
926: IF (g_asn_debug = 'Y') THEN
927: asn_debug.put_line('In Intransit Owning Org Record Procedure');
928: END IF;
929:
930: po_orgs_sv.derive_org_info(transit_org_record);
923: transit_org_record.error_record.error_status := 'S';
924: transit_org_record.error_record.error_message := NULL;
925:
926: IF (g_asn_debug = 'Y') THEN
927: asn_debug.put_line('In Intransit Owning Org Record Procedure');
928: END IF;
929:
930: po_orgs_sv.derive_org_info(transit_org_record);
931:
928: END IF;
929:
930: po_orgs_sv.derive_org_info(transit_org_record);
931:
932: IF (g_asn_debug = 'Y') THEN
933: asn_debug.put_line('Intransit organization code ' || transit_org_record.organization_code);
934: asn_debug.put_line('Intransit organization id ' || TO_CHAR(transit_org_record.organization_id));
935: asn_debug.put_line('Intransit error status ' || transit_org_record.error_record.error_status);
936: END IF;
929:
930: po_orgs_sv.derive_org_info(transit_org_record);
931:
932: IF (g_asn_debug = 'Y') THEN
933: asn_debug.put_line('Intransit organization code ' || transit_org_record.organization_code);
934: asn_debug.put_line('Intransit organization id ' || TO_CHAR(transit_org_record.organization_id));
935: asn_debug.put_line('Intransit error status ' || transit_org_record.error_record.error_status);
936: END IF;
937:
930: po_orgs_sv.derive_org_info(transit_org_record);
931:
932: IF (g_asn_debug = 'Y') THEN
933: asn_debug.put_line('Intransit organization code ' || transit_org_record.organization_code);
934: asn_debug.put_line('Intransit organization id ' || TO_CHAR(transit_org_record.organization_id));
935: asn_debug.put_line('Intransit error status ' || transit_org_record.error_record.error_status);
936: END IF;
937:
938: x_cascaded_table(n).intransit_owning_org_code := transit_org_record.organization_code;
931:
932: IF (g_asn_debug = 'Y') THEN
933: asn_debug.put_line('Intransit organization code ' || transit_org_record.organization_code);
934: asn_debug.put_line('Intransit organization id ' || TO_CHAR(transit_org_record.organization_id));
935: asn_debug.put_line('Intransit error status ' || transit_org_record.error_record.error_status);
936: END IF;
937:
938: x_cascaded_table(n).intransit_owning_org_code := transit_org_record.organization_code;
939: x_cascaded_table(n).intransit_owning_org_id := transit_org_record.organization_id;
952: -- deliver_to_person_id
953: IF (x_cascaded_table(n).error_status IN('S', 'W'))
954: AND ( x_cascaded_table(n).deliver_to_person_id IS NULL
955: AND x_cascaded_table(n).deliver_to_person_name IS NOT NULL) THEN
956: IF (g_asn_debug = 'Y') THEN
957: asn_debug.put_line('X_progress ' || x_progress);
958: END IF;
959:
960: employee_id_record.employee_name := x_cascaded_table(n).deliver_to_person_name;
953: IF (x_cascaded_table(n).error_status IN('S', 'W'))
954: AND ( x_cascaded_table(n).deliver_to_person_id IS NULL
955: AND x_cascaded_table(n).deliver_to_person_name IS NOT NULL) THEN
956: IF (g_asn_debug = 'Y') THEN
957: asn_debug.put_line('X_progress ' || x_progress);
958: END IF;
959:
960: employee_id_record.employee_name := x_cascaded_table(n).deliver_to_person_name;
961: employee_id_record.employee_id := x_cascaded_table(n).deliver_to_person_id;
961: employee_id_record.employee_id := x_cascaded_table(n).deliver_to_person_id;
962: employee_id_record.error_record.error_status := 'S';
963: employee_id_record.error_record.error_message := NULL;
964:
965: IF (g_asn_debug = 'Y') THEN
966: asn_debug.put_line('In Derive deliver_to_person_id Information');
967: END IF;
968:
969: po_employees_sv.derive_employee_info(employee_id_record);
962: employee_id_record.error_record.error_status := 'S';
963: employee_id_record.error_record.error_message := NULL;
964:
965: IF (g_asn_debug = 'Y') THEN
966: asn_debug.put_line('In Derive deliver_to_person_id Information');
967: END IF;
968:
969: po_employees_sv.derive_employee_info(employee_id_record);
970:
967: END IF;
968:
969: po_employees_sv.derive_employee_info(employee_id_record);
970:
971: IF (g_asn_debug = 'Y') THEN
972: asn_debug.put_line('Employee name ' || employee_id_record.employee_name);
973: asn_debug.put_line('Employee id ' || TO_CHAR(employee_id_record.employee_id));
974: asn_debug.put_line('Employee error status ' || employee_id_record.error_record.error_status);
975: END IF;
968:
969: po_employees_sv.derive_employee_info(employee_id_record);
970:
971: IF (g_asn_debug = 'Y') THEN
972: asn_debug.put_line('Employee name ' || employee_id_record.employee_name);
973: asn_debug.put_line('Employee id ' || TO_CHAR(employee_id_record.employee_id));
974: asn_debug.put_line('Employee error status ' || employee_id_record.error_record.error_status);
975: END IF;
976:
969: po_employees_sv.derive_employee_info(employee_id_record);
970:
971: IF (g_asn_debug = 'Y') THEN
972: asn_debug.put_line('Employee name ' || employee_id_record.employee_name);
973: asn_debug.put_line('Employee id ' || TO_CHAR(employee_id_record.employee_id));
974: asn_debug.put_line('Employee error status ' || employee_id_record.error_record.error_status);
975: END IF;
976:
977: x_cascaded_table(n).deliver_to_person_name := employee_id_record.employee_name;
970:
971: IF (g_asn_debug = 'Y') THEN
972: asn_debug.put_line('Employee name ' || employee_id_record.employee_name);
973: asn_debug.put_line('Employee id ' || TO_CHAR(employee_id_record.employee_id));
974: asn_debug.put_line('Employee error status ' || employee_id_record.error_record.error_status);
975: END IF;
976:
977: x_cascaded_table(n).deliver_to_person_name := employee_id_record.employee_name;
978: x_cascaded_table(n).deliver_to_person_id := employee_id_record.employee_id;
983: -- deliver_to_location
984: IF (x_cascaded_table(n).error_status IN('S', 'W'))
985: AND ( x_cascaded_table(n).deliver_to_location_id IS NULL
986: AND x_cascaded_table(n).deliver_to_location_code IS NOT NULL) THEN
987: IF (g_asn_debug = 'Y') THEN
988: asn_debug.put_line('X_progress ' || x_progress);
989: END IF;
990:
991: location_id_record.location_code := x_cascaded_table(n).deliver_to_location_code;
984: IF (x_cascaded_table(n).error_status IN('S', 'W'))
985: AND ( x_cascaded_table(n).deliver_to_location_id IS NULL
986: AND x_cascaded_table(n).deliver_to_location_code IS NOT NULL) THEN
987: IF (g_asn_debug = 'Y') THEN
988: asn_debug.put_line('X_progress ' || x_progress);
989: END IF;
990:
991: location_id_record.location_code := x_cascaded_table(n).deliver_to_location_code;
992: location_id_record.error_record.error_status := 'S';
991: location_id_record.location_code := x_cascaded_table(n).deliver_to_location_code;
992: location_id_record.error_record.error_status := 'S';
993: location_id_record.error_record.error_message := NULL;
994:
995: IF (g_asn_debug = 'Y') THEN
996: asn_debug.put_line('Derive deliver_to_location_id');
997: END IF;
998:
999: rcv_transactions_interface_sv.get_location_id(location_id_record);
992: location_id_record.error_record.error_status := 'S';
993: location_id_record.error_record.error_message := NULL;
994:
995: IF (g_asn_debug = 'Y') THEN
996: asn_debug.put_line('Derive deliver_to_location_id');
997: END IF;
998:
999: rcv_transactions_interface_sv.get_location_id(location_id_record);
1000: x_cascaded_table(n).deliver_to_location_id := location_id_record.location_id;
1009: ) IS
1010: BEGIN
1011: IF (x_cascaded_table(n).error_status IN('S', 'W'))
1012: AND x_cascaded_table(n).auto_transact_code IS NULL THEN
1013: IF (g_asn_debug = 'Y') THEN
1014: asn_debug.put_line('X_progress ' || x_progress);
1015: asn_debug.put_line('Setting auto_transact_code to transaction_type ' || x_cascaded_table(n).transaction_type);
1016: END IF;
1017:
1010: BEGIN
1011: IF (x_cascaded_table(n).error_status IN('S', 'W'))
1012: AND x_cascaded_table(n).auto_transact_code IS NULL THEN
1013: IF (g_asn_debug = 'Y') THEN
1014: asn_debug.put_line('X_progress ' || x_progress);
1015: asn_debug.put_line('Setting auto_transact_code to transaction_type ' || x_cascaded_table(n).transaction_type);
1016: END IF;
1017:
1018: x_cascaded_table(n).auto_transact_code := x_cascaded_table(n).transaction_type;
1011: IF (x_cascaded_table(n).error_status IN('S', 'W'))
1012: AND x_cascaded_table(n).auto_transact_code IS NULL THEN
1013: IF (g_asn_debug = 'Y') THEN
1014: asn_debug.put_line('X_progress ' || x_progress);
1015: asn_debug.put_line('Setting auto_transact_code to transaction_type ' || x_cascaded_table(n).transaction_type);
1016: END IF;
1017:
1018: x_cascaded_table(n).auto_transact_code := x_cascaded_table(n).transaction_type;
1019: END IF;
1133: BEGIN
1134: --check line quanity > 0
1135: x_progress := '097';
1136:
1137: IF (g_asn_debug = 'Y') THEN
1138: asn_debug.put_line('X_progress ' || x_progress);
1139: END IF;
1140:
1141: IF x_cascaded_table(n).error_status NOT IN('S', 'W')
1134: --check line quanity > 0
1135: x_progress := '097';
1136:
1137: IF (g_asn_debug = 'Y') THEN
1138: asn_debug.put_line('X_progress ' || x_progress);
1139: END IF;
1140:
1141: IF x_cascaded_table(n).error_status NOT IN('S', 'W')
1142: OR x_cascaded_table(n).quantity <= 0 THEN --{
1139: END IF;
1140:
1141: IF x_cascaded_table(n).error_status NOT IN('S', 'W')
1142: OR x_cascaded_table(n).quantity <= 0 THEN --{
1143: IF (g_asn_debug = 'Y') THEN
1144: asn_debug.put_line('Quantity is <= zero. Cascade will fail');
1145: END IF;
1146:
1147: x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1140:
1141: IF x_cascaded_table(n).error_status NOT IN('S', 'W')
1142: OR x_cascaded_table(n).quantity <= 0 THEN --{
1143: IF (g_asn_debug = 'Y') THEN
1144: asn_debug.put_line('Quantity is <= zero. Cascade will fail');
1145: END IF;
1146:
1147: x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1148: rcv_error_pkg.set_error_message('RCV_ITEM_NO_SHIP_QTY', x_cascaded_table(n).error_message);
1157: * the rma is consumed. (Cascade)
1158: */
1159: x_progress := '098';
1160:
1161: IF (g_asn_debug = 'Y') THEN
1162: asn_debug.put_line('X_progress ' || x_progress);
1163: END IF;
1164:
1165: -- check order info
1158: */
1159: x_progress := '098';
1160:
1161: IF (g_asn_debug = 'Y') THEN
1162: asn_debug.put_line('X_progress ' || x_progress);
1163: END IF;
1164:
1165: -- check order info
1166: IF ( x_cascaded_table(n).oe_order_header_id IS NULL
1167: OR ( x_cascaded_table(n).item_id IS NULL
1168: AND x_cascaded_table(n).customer_item_num IS NULL
1169: AND x_cascaded_table(n).oe_order_line_id IS NULL
1170: AND x_cascaded_table(n).document_line_num IS NULL)) THEN --{
1171: IF (g_asn_debug = 'Y') THEN
1172: asn_debug.put_line('No oe_order_header_id/item_id ');
1173: asn_debug.put_line('Status = ' || x_cascaded_table(n).error_status);
1174: END IF;
1175:
1168: AND x_cascaded_table(n).customer_item_num IS NULL
1169: AND x_cascaded_table(n).oe_order_line_id IS NULL
1170: AND x_cascaded_table(n).document_line_num IS NULL)) THEN --{
1171: IF (g_asn_debug = 'Y') THEN
1172: asn_debug.put_line('No oe_order_header_id/item_id ');
1173: asn_debug.put_line('Status = ' || x_cascaded_table(n).error_status);
1174: END IF;
1175:
1176: -- only set error if not already set
1169: AND x_cascaded_table(n).oe_order_line_id IS NULL
1170: AND x_cascaded_table(n).document_line_num IS NULL)) THEN --{
1171: IF (g_asn_debug = 'Y') THEN
1172: asn_debug.put_line('No oe_order_header_id/item_id ');
1173: asn_debug.put_line('Status = ' || x_cascaded_table(n).error_status);
1174: END IF;
1175:
1176: -- only set error if not already set
1177: IF x_cascaded_table(n).error_status IN('S', 'W', 'F') THEN --{
1185:
1186: -- }
1187:
1188: -- Assign shipped quantity to remaining quantity
1189: IF (g_asn_debug = 'Y') THEN
1190: asn_debug.put_line('Assign txn quantity to remaining quantity');
1191: END IF;
1192:
1193: txn_remaining_qty := x_cascaded_table(n).quantity;
1186: -- }
1187:
1188: -- Assign shipped quantity to remaining quantity
1189: IF (g_asn_debug = 'Y') THEN
1190: asn_debug.put_line('Assign txn quantity to remaining quantity');
1191: END IF;
1192:
1193: txn_remaining_qty := x_cascaded_table(n).quantity;
1194:
1191: END IF;
1192:
1193: txn_remaining_qty := x_cascaded_table(n).quantity;
1194:
1195: IF (g_asn_debug = 'Y') THEN
1196: asn_debug.put_line('Have assigned the quantity');
1197: END IF;
1198:
1199: -- Calculate tax_amount_factor for calculating tax_amount for
1192:
1193: txn_remaining_qty := x_cascaded_table(n).quantity;
1194:
1195: IF (g_asn_debug = 'Y') THEN
1196: asn_debug.put_line('Have assigned the quantity');
1197: END IF;
1198:
1199: -- Calculate tax_amount_factor for calculating tax_amount for
1200: -- each cascaded line
1199: -- Calculate tax_amount_factor for calculating tax_amount for
1200: -- each cascaded line
1201: tax_amount_factor := NVL(x_cascaded_table(n).tax_amount, 0) / txn_remaining_qty;
1202:
1203: IF (g_asn_debug = 'Y') THEN
1204: asn_debug.put_line('Tax Factor ' || TO_CHAR(tax_amount_factor));
1205: asn_debug.put_line('Txn Quantity : ' || TO_CHAR(txn_remaining_qty));
1206: asn_debug.put_line('Before starting Cascade');
1207: END IF;
1200: -- each cascaded line
1201: tax_amount_factor := NVL(x_cascaded_table(n).tax_amount, 0) / txn_remaining_qty;
1202:
1203: IF (g_asn_debug = 'Y') THEN
1204: asn_debug.put_line('Tax Factor ' || TO_CHAR(tax_amount_factor));
1205: asn_debug.put_line('Txn Quantity : ' || TO_CHAR(txn_remaining_qty));
1206: asn_debug.put_line('Before starting Cascade');
1207: END IF;
1208:
1201: tax_amount_factor := NVL(x_cascaded_table(n).tax_amount, 0) / txn_remaining_qty;
1202:
1203: IF (g_asn_debug = 'Y') THEN
1204: asn_debug.put_line('Tax Factor ' || TO_CHAR(tax_amount_factor));
1205: asn_debug.put_line('Txn Quantity : ' || TO_CHAR(txn_remaining_qty));
1206: asn_debug.put_line('Before starting Cascade');
1207: END IF;
1208:
1209: -- make sure the temp table is clean before we start using it
1202:
1203: IF (g_asn_debug = 'Y') THEN
1204: asn_debug.put_line('Tax Factor ' || TO_CHAR(tax_amount_factor));
1205: asn_debug.put_line('Txn Quantity : ' || TO_CHAR(txn_remaining_qty));
1206: asn_debug.put_line('Before starting Cascade');
1207: END IF;
1208:
1209: -- make sure the temp table is clean before we start using it
1210: temp_cascaded_table.DELETE;
1208:
1209: -- make sure the temp table is clean before we start using it
1210: temp_cascaded_table.DELETE;
1211:
1212: IF (g_asn_debug = 'Y') THEN
1213: asn_debug.put_line('Executing RMA Lines cursor with:');
1214: asn_debug.put_line('oe_order_header_id: ' || x_cascaded_table(n).oe_order_header_id);
1215: asn_debug.put_line('item_id: ' || x_cascaded_table(n).item_id);
1216: asn_debug.put_line('oe_order_line_num: ' || x_cascaded_table(n).oe_order_line_num);
1209: -- make sure the temp table is clean before we start using it
1210: temp_cascaded_table.DELETE;
1211:
1212: IF (g_asn_debug = 'Y') THEN
1213: asn_debug.put_line('Executing RMA Lines cursor with:');
1214: asn_debug.put_line('oe_order_header_id: ' || x_cascaded_table(n).oe_order_header_id);
1215: asn_debug.put_line('item_id: ' || x_cascaded_table(n).item_id);
1216: asn_debug.put_line('oe_order_line_num: ' || x_cascaded_table(n).oe_order_line_num);
1217: asn_debug.put_line('to_organization_id: ' || x_cascaded_table(n).to_organization_id);
1210: temp_cascaded_table.DELETE;
1211:
1212: IF (g_asn_debug = 'Y') THEN
1213: asn_debug.put_line('Executing RMA Lines cursor with:');
1214: asn_debug.put_line('oe_order_header_id: ' || x_cascaded_table(n).oe_order_header_id);
1215: asn_debug.put_line('item_id: ' || x_cascaded_table(n).item_id);
1216: asn_debug.put_line('oe_order_line_num: ' || x_cascaded_table(n).oe_order_line_num);
1217: asn_debug.put_line('to_organization_id: ' || x_cascaded_table(n).to_organization_id);
1218: asn_debug.put_line('customer_item_num: ' || x_cascaded_table(n).customer_item_num);
1211:
1212: IF (g_asn_debug = 'Y') THEN
1213: asn_debug.put_line('Executing RMA Lines cursor with:');
1214: asn_debug.put_line('oe_order_header_id: ' || x_cascaded_table(n).oe_order_header_id);
1215: asn_debug.put_line('item_id: ' || x_cascaded_table(n).item_id);
1216: asn_debug.put_line('oe_order_line_num: ' || x_cascaded_table(n).oe_order_line_num);
1217: asn_debug.put_line('to_organization_id: ' || x_cascaded_table(n).to_organization_id);
1218: asn_debug.put_line('customer_item_num: ' || x_cascaded_table(n).customer_item_num);
1219: END IF;
1212: IF (g_asn_debug = 'Y') THEN
1213: asn_debug.put_line('Executing RMA Lines cursor with:');
1214: asn_debug.put_line('oe_order_header_id: ' || x_cascaded_table(n).oe_order_header_id);
1215: asn_debug.put_line('item_id: ' || x_cascaded_table(n).item_id);
1216: asn_debug.put_line('oe_order_line_num: ' || x_cascaded_table(n).oe_order_line_num);
1217: asn_debug.put_line('to_organization_id: ' || x_cascaded_table(n).to_organization_id);
1218: asn_debug.put_line('customer_item_num: ' || x_cascaded_table(n).customer_item_num);
1219: END IF;
1220:
1213: asn_debug.put_line('Executing RMA Lines cursor with:');
1214: asn_debug.put_line('oe_order_header_id: ' || x_cascaded_table(n).oe_order_header_id);
1215: asn_debug.put_line('item_id: ' || x_cascaded_table(n).item_id);
1216: asn_debug.put_line('oe_order_line_num: ' || x_cascaded_table(n).oe_order_line_num);
1217: asn_debug.put_line('to_organization_id: ' || x_cascaded_table(n).to_organization_id);
1218: asn_debug.put_line('customer_item_num: ' || x_cascaded_table(n).customer_item_num);
1219: END IF;
1220:
1221: FOR x_rma_line_record IN rma_lines(x_cascaded_table(n).oe_order_header_id,
1214: asn_debug.put_line('oe_order_header_id: ' || x_cascaded_table(n).oe_order_header_id);
1215: asn_debug.put_line('item_id: ' || x_cascaded_table(n).item_id);
1216: asn_debug.put_line('oe_order_line_num: ' || x_cascaded_table(n).oe_order_line_num);
1217: asn_debug.put_line('to_organization_id: ' || x_cascaded_table(n).to_organization_id);
1218: asn_debug.put_line('customer_item_num: ' || x_cascaded_table(n).customer_item_num);
1219: END IF;
1220:
1221: FOR x_rma_line_record IN rma_lines(x_cascaded_table(n).oe_order_header_id,
1222: x_cascaded_table(n).oe_order_line_id, --bug 4740567
1227: ) LOOP --{
1228: -- preserve a count to use after cursor is closed
1229: rma_lines_fetched := rma_lines%ROWCOUNT;
1230:
1231: IF (g_asn_debug = 'Y') THEN
1232: asn_debug.put_line('RMA Lines fetched ' || TO_CHAR(rma_lines_fetched));
1233: asn_debug.put_line('Remaining Quantity ' || TO_CHAR(txn_remaining_qty));
1234: asn_debug.put_line('Fetched order header id ' || x_rma_line_record.oe_order_header_id);
1235: asn_debug.put_line('Fetched order line id ' || x_rma_line_record.oe_order_line_id);
1228: -- preserve a count to use after cursor is closed
1229: rma_lines_fetched := rma_lines%ROWCOUNT;
1230:
1231: IF (g_asn_debug = 'Y') THEN
1232: asn_debug.put_line('RMA Lines fetched ' || TO_CHAR(rma_lines_fetched));
1233: asn_debug.put_line('Remaining Quantity ' || TO_CHAR(txn_remaining_qty));
1234: asn_debug.put_line('Fetched order header id ' || x_rma_line_record.oe_order_header_id);
1235: asn_debug.put_line('Fetched order line id ' || x_rma_line_record.oe_order_line_id);
1236: asn_debug.put_line('Fetched order number ' || x_rma_line_record.oe_order_num);
1229: rma_lines_fetched := rma_lines%ROWCOUNT;
1230:
1231: IF (g_asn_debug = 'Y') THEN
1232: asn_debug.put_line('RMA Lines fetched ' || TO_CHAR(rma_lines_fetched));
1233: asn_debug.put_line('Remaining Quantity ' || TO_CHAR(txn_remaining_qty));
1234: asn_debug.put_line('Fetched order header id ' || x_rma_line_record.oe_order_header_id);
1235: asn_debug.put_line('Fetched order line id ' || x_rma_line_record.oe_order_line_id);
1236: asn_debug.put_line('Fetched order number ' || x_rma_line_record.oe_order_num);
1237: END IF;
1230:
1231: IF (g_asn_debug = 'Y') THEN
1232: asn_debug.put_line('RMA Lines fetched ' || TO_CHAR(rma_lines_fetched));
1233: asn_debug.put_line('Remaining Quantity ' || TO_CHAR(txn_remaining_qty));
1234: asn_debug.put_line('Fetched order header id ' || x_rma_line_record.oe_order_header_id);
1235: asn_debug.put_line('Fetched order line id ' || x_rma_line_record.oe_order_line_id);
1236: asn_debug.put_line('Fetched order number ' || x_rma_line_record.oe_order_num);
1237: END IF;
1238:
1231: IF (g_asn_debug = 'Y') THEN
1232: asn_debug.put_line('RMA Lines fetched ' || TO_CHAR(rma_lines_fetched));
1233: asn_debug.put_line('Remaining Quantity ' || TO_CHAR(txn_remaining_qty));
1234: asn_debug.put_line('Fetched order header id ' || x_rma_line_record.oe_order_header_id);
1235: asn_debug.put_line('Fetched order line id ' || x_rma_line_record.oe_order_line_id);
1236: asn_debug.put_line('Fetched order number ' || x_rma_line_record.oe_order_num);
1237: END IF;
1238:
1239: -- done allocating transaction quantity
1232: asn_debug.put_line('RMA Lines fetched ' || TO_CHAR(rma_lines_fetched));
1233: asn_debug.put_line('Remaining Quantity ' || TO_CHAR(txn_remaining_qty));
1234: asn_debug.put_line('Fetched order header id ' || x_rma_line_record.oe_order_header_id);
1235: asn_debug.put_line('Fetched order line id ' || x_rma_line_record.oe_order_line_id);
1236: asn_debug.put_line('Fetched order number ' || x_rma_line_record.oe_order_num);
1237: END IF;
1238:
1239: -- done allocating transaction quantity
1240: IF txn_remaining_qty <= 0 THEN
1237: END IF;
1238:
1239: -- done allocating transaction quantity
1240: IF txn_remaining_qty <= 0 THEN
1241: asn_debug.put_line('Done allocating transaction quantity');
1242: EXIT;
1243: END IF;
1244:
1245: -- maintain a dense table
1250: -- copy from previous row
1251: temp_cascaded_table(temp_cascaded_table.COUNT + 1) := temp_cascaded_table(temp_cascaded_table.LAST);
1252: END IF;
1253:
1254: IF (g_asn_debug = 'Y') THEN
1255: asn_debug.put_line('Count in temp_cascade_table : ' || TO_CHAR(temp_cascaded_table.COUNT));
1256: asn_debug.put_line('Cursor record ' || TO_CHAR(rma_lines%ROWCOUNT));
1257: asn_debug.put_line('Check date tolerance');
1258: END IF;
1251: temp_cascaded_table(temp_cascaded_table.COUNT + 1) := temp_cascaded_table(temp_cascaded_table.LAST);
1252: END IF;
1253:
1254: IF (g_asn_debug = 'Y') THEN
1255: asn_debug.put_line('Count in temp_cascade_table : ' || TO_CHAR(temp_cascaded_table.COUNT));
1256: asn_debug.put_line('Cursor record ' || TO_CHAR(rma_lines%ROWCOUNT));
1257: asn_debug.put_line('Check date tolerance');
1258: END IF;
1259:
1252: END IF;
1253:
1254: IF (g_asn_debug = 'Y') THEN
1255: asn_debug.put_line('Count in temp_cascade_table : ' || TO_CHAR(temp_cascaded_table.COUNT));
1256: asn_debug.put_line('Cursor record ' || TO_CHAR(rma_lines%ROWCOUNT));
1257: asn_debug.put_line('Check date tolerance');
1258: END IF;
1259:
1260: -- default to successful matching to current line
1253:
1254: IF (g_asn_debug = 'Y') THEN
1255: asn_debug.put_line('Count in temp_cascade_table : ' || TO_CHAR(temp_cascaded_table.COUNT));
1256: asn_debug.put_line('Cursor record ' || TO_CHAR(rma_lines%ROWCOUNT));
1257: asn_debug.put_line('Check date tolerance');
1258: END IF;
1259:
1260: -- default to successful matching to current line
1261: insert_into_table := TRUE;
1288: rcv_error_pkg.log_interface_error('DOCUMENT_NUM', FALSE);
1289: insert_into_table := FALSE;
1290: END IF; --}
1291:
1292: IF (g_asn_debug = 'Y') THEN
1293: asn_debug.put_line('Days exception Code ' || NVL(x_rcv_date_exception, 'NONE'));
1294: END IF;
1295:
1296: /*
1289: insert_into_table := FALSE;
1290: END IF; --}
1291:
1292: IF (g_asn_debug = 'Y') THEN
1293: asn_debug.put_line('Days exception Code ' || NVL(x_rcv_date_exception, 'NONE'));
1294: END IF;
1295:
1296: /*
1297: ** Get the available quantity for the line
1316:
1317: --Bug 8494868 When oe_line_id crossed 2^31 the pl/sql table should not throw any exception.
1318: IF g_used_rma_line_amounts.exists(mod(x_rma_line_record.oe_order_line_id,2147483648)) THEN
1319: already_allocated_qty := g_used_rma_line_amounts(mod(x_rma_line_record.oe_order_line_id,2147483648));
1320: asn_debug.put_line('amount '||already_allocated_qty||' already allocated for order line '||x_rma_line_record.oe_order_line_id);
1321: END IF;
1322:
1323: END IF;
1324:
1321: END IF;
1322:
1323: END IF;
1324:
1325: IF (g_asn_debug = 'Y') THEN
1326: asn_debug.put_line('Available Quantity ' || TO_CHAR(rma_line_qty));
1327: END IF;
1328:
1329: -- if qty has already been allocated then reduce available and tolerable
1322:
1323: END IF;
1324:
1325: IF (g_asn_debug = 'Y') THEN
1326: asn_debug.put_line('Available Quantity ' || TO_CHAR(rma_line_qty));
1327: END IF;
1328:
1329: -- if qty has already been allocated then reduce available and tolerable
1330: -- qty by the allocated amount
1334: IF rma_line_qty < 0 THEN
1335: rma_line_qty := 0;
1336: END IF;
1337:
1338: IF (g_asn_debug = 'Y') THEN
1339: asn_debug.put_line('Have some allocated quantity. Will reduce qty');
1340: asn_debug.put_line('Allocated Qty ' || TO_CHAR(already_allocated_qty));
1341: asn_debug.put_line('After reducing by allocated qty');
1342: asn_debug.put_line('Available Quantity ' || TO_CHAR(rma_line_qty));
1335: rma_line_qty := 0;
1336: END IF;
1337:
1338: IF (g_asn_debug = 'Y') THEN
1339: asn_debug.put_line('Have some allocated quantity. Will reduce qty');
1340: asn_debug.put_line('Allocated Qty ' || TO_CHAR(already_allocated_qty));
1341: asn_debug.put_line('After reducing by allocated qty');
1342: asn_debug.put_line('Available Quantity ' || TO_CHAR(rma_line_qty));
1343: END IF;
1336: END IF;
1337:
1338: IF (g_asn_debug = 'Y') THEN
1339: asn_debug.put_line('Have some allocated quantity. Will reduce qty');
1340: asn_debug.put_line('Allocated Qty ' || TO_CHAR(already_allocated_qty));
1341: asn_debug.put_line('After reducing by allocated qty');
1342: asn_debug.put_line('Available Quantity ' || TO_CHAR(rma_line_qty));
1343: END IF;
1344: END IF;
1337:
1338: IF (g_asn_debug = 'Y') THEN
1339: asn_debug.put_line('Have some allocated quantity. Will reduce qty');
1340: asn_debug.put_line('Allocated Qty ' || TO_CHAR(already_allocated_qty));
1341: asn_debug.put_line('After reducing by allocated qty');
1342: asn_debug.put_line('Available Quantity ' || TO_CHAR(rma_line_qty));
1343: END IF;
1344: END IF;
1345:
1338: IF (g_asn_debug = 'Y') THEN
1339: asn_debug.put_line('Have some allocated quantity. Will reduce qty');
1340: asn_debug.put_line('Allocated Qty ' || TO_CHAR(already_allocated_qty));
1341: asn_debug.put_line('After reducing by allocated qty');
1342: asn_debug.put_line('Available Quantity ' || TO_CHAR(rma_line_qty));
1343: END IF;
1344: END IF;
1345:
1346: --}
1381: OR rma_primary_uom_qty = 0 THEN
1382: --{ PO -> ASN uom, PO -> PRIMARY UOM
1383: -- no point in going further for this record
1384: -- as we cannot convert between the ASN -> PO uoms
1385: IF (g_asn_debug = 'Y') THEN
1386: asn_debug.put_line('Need an error message in the interface tables');
1387: asn_debug.put_line('Cannot interconvert between diff UOMs');
1388: asn_debug.put_line('This RMA line cannot be used as the uoms ');
1389: asn_debug.put_line(temp_cascaded_table(1).unit_of_measure || ' ' || x_rma_line_record.unit_of_measure);
1382: --{ PO -> ASN uom, PO -> PRIMARY UOM
1383: -- no point in going further for this record
1384: -- as we cannot convert between the ASN -> PO uoms
1385: IF (g_asn_debug = 'Y') THEN
1386: asn_debug.put_line('Need an error message in the interface tables');
1387: asn_debug.put_line('Cannot interconvert between diff UOMs');
1388: asn_debug.put_line('This RMA line cannot be used as the uoms ');
1389: asn_debug.put_line(temp_cascaded_table(1).unit_of_measure || ' ' || x_rma_line_record.unit_of_measure);
1390: asn_debug.put_line('cannot be converted for item ' || TO_CHAR(temp_cascaded_table(1).item_id));
1383: -- no point in going further for this record
1384: -- as we cannot convert between the ASN -> PO uoms
1385: IF (g_asn_debug = 'Y') THEN
1386: asn_debug.put_line('Need an error message in the interface tables');
1387: asn_debug.put_line('Cannot interconvert between diff UOMs');
1388: asn_debug.put_line('This RMA line cannot be used as the uoms ');
1389: asn_debug.put_line(temp_cascaded_table(1).unit_of_measure || ' ' || x_rma_line_record.unit_of_measure);
1390: asn_debug.put_line('cannot be converted for item ' || TO_CHAR(temp_cascaded_table(1).item_id));
1391: insert_into_table := FALSE;
1384: -- as we cannot convert between the ASN -> PO uoms
1385: IF (g_asn_debug = 'Y') THEN
1386: asn_debug.put_line('Need an error message in the interface tables');
1387: asn_debug.put_line('Cannot interconvert between diff UOMs');
1388: asn_debug.put_line('This RMA line cannot be used as the uoms ');
1389: asn_debug.put_line(temp_cascaded_table(1).unit_of_measure || ' ' || x_rma_line_record.unit_of_measure);
1390: asn_debug.put_line('cannot be converted for item ' || TO_CHAR(temp_cascaded_table(1).item_id));
1391: insert_into_table := FALSE;
1392: END IF;
1385: IF (g_asn_debug = 'Y') THEN
1386: asn_debug.put_line('Need an error message in the interface tables');
1387: asn_debug.put_line('Cannot interconvert between diff UOMs');
1388: asn_debug.put_line('This RMA line cannot be used as the uoms ');
1389: asn_debug.put_line(temp_cascaded_table(1).unit_of_measure || ' ' || x_rma_line_record.unit_of_measure);
1390: asn_debug.put_line('cannot be converted for item ' || TO_CHAR(temp_cascaded_table(1).item_id));
1391: insert_into_table := FALSE;
1392: END IF;
1393: ELSE --}{
1386: asn_debug.put_line('Need an error message in the interface tables');
1387: asn_debug.put_line('Cannot interconvert between diff UOMs');
1388: asn_debug.put_line('This RMA line cannot be used as the uoms ');
1389: asn_debug.put_line(temp_cascaded_table(1).unit_of_measure || ' ' || x_rma_line_record.unit_of_measure);
1390: asn_debug.put_line('cannot be converted for item ' || TO_CHAR(temp_cascaded_table(1).item_id));
1391: insert_into_table := FALSE;
1392: END IF;
1393: ELSE --}{
1394: IF (g_asn_debug = 'Y') THEN
1390: asn_debug.put_line('cannot be converted for item ' || TO_CHAR(temp_cascaded_table(1).item_id));
1391: insert_into_table := FALSE;
1392: END IF;
1393: ELSE --}{
1394: IF (g_asn_debug = 'Y') THEN
1395: asn_debug.put_line('Current Item Id ' || TO_CHAR(temp_cascaded_table(1).item_id));
1396: asn_debug.put_line('Current Txn Quantity ' || TO_CHAR(txn_remaining_qty));
1397: asn_debug.put_line('Current Txn UOM ' || temp_cascaded_table(1).unit_of_measure);
1398: asn_debug.put_line('Converted RMA UOM Quantity ' || TO_CHAR(txn_remaining_qty_rma_uom));
1391: insert_into_table := FALSE;
1392: END IF;
1393: ELSE --}{
1394: IF (g_asn_debug = 'Y') THEN
1395: asn_debug.put_line('Current Item Id ' || TO_CHAR(temp_cascaded_table(1).item_id));
1396: asn_debug.put_line('Current Txn Quantity ' || TO_CHAR(txn_remaining_qty));
1397: asn_debug.put_line('Current Txn UOM ' || temp_cascaded_table(1).unit_of_measure);
1398: asn_debug.put_line('Converted RMA UOM Quantity ' || TO_CHAR(txn_remaining_qty_rma_uom));
1399: asn_debug.put_line('RMA UOM ' || x_rma_line_record.unit_of_measure);
1392: END IF;
1393: ELSE --}{
1394: IF (g_asn_debug = 'Y') THEN
1395: asn_debug.put_line('Current Item Id ' || TO_CHAR(temp_cascaded_table(1).item_id));
1396: asn_debug.put_line('Current Txn Quantity ' || TO_CHAR(txn_remaining_qty));
1397: asn_debug.put_line('Current Txn UOM ' || temp_cascaded_table(1).unit_of_measure);
1398: asn_debug.put_line('Converted RMA UOM Quantity ' || TO_CHAR(txn_remaining_qty_rma_uom));
1399: asn_debug.put_line('RMA UOM ' || x_rma_line_record.unit_of_measure);
1400: END IF;
1393: ELSE --}{
1394: IF (g_asn_debug = 'Y') THEN
1395: asn_debug.put_line('Current Item Id ' || TO_CHAR(temp_cascaded_table(1).item_id));
1396: asn_debug.put_line('Current Txn Quantity ' || TO_CHAR(txn_remaining_qty));
1397: asn_debug.put_line('Current Txn UOM ' || temp_cascaded_table(1).unit_of_measure);
1398: asn_debug.put_line('Converted RMA UOM Quantity ' || TO_CHAR(txn_remaining_qty_rma_uom));
1399: asn_debug.put_line('RMA UOM ' || x_rma_line_record.unit_of_measure);
1400: END IF;
1401: END IF; --}
1394: IF (g_asn_debug = 'Y') THEN
1395: asn_debug.put_line('Current Item Id ' || TO_CHAR(temp_cascaded_table(1).item_id));
1396: asn_debug.put_line('Current Txn Quantity ' || TO_CHAR(txn_remaining_qty));
1397: asn_debug.put_line('Current Txn UOM ' || temp_cascaded_table(1).unit_of_measure);
1398: asn_debug.put_line('Converted RMA UOM Quantity ' || TO_CHAR(txn_remaining_qty_rma_uom));
1399: asn_debug.put_line('RMA UOM ' || x_rma_line_record.unit_of_measure);
1400: END IF;
1401: END IF; --}
1402:
1395: asn_debug.put_line('Current Item Id ' || TO_CHAR(temp_cascaded_table(1).item_id));
1396: asn_debug.put_line('Current Txn Quantity ' || TO_CHAR(txn_remaining_qty));
1397: asn_debug.put_line('Current Txn UOM ' || temp_cascaded_table(1).unit_of_measure);
1398: asn_debug.put_line('Converted RMA UOM Quantity ' || TO_CHAR(txn_remaining_qty_rma_uom));
1399: asn_debug.put_line('RMA UOM ' || x_rma_line_record.unit_of_measure);
1400: END IF;
1401: END IF; --}
1402:
1403: IF insert_into_table THEN --{ allocate part of the txn qty to this line
1417: ELSE
1418: allocate_qty := txn_remaining_qty_rma_uom;
1419: END IF;
1420:
1421: IF (g_asn_debug = 'Y') THEN
1422: asn_debug.put_line('Quantity to allocate to this line: ' || allocate_qty);
1423: END IF;
1424:
1425: --bug 4505906, record the used quantity in this session
1418: allocate_qty := txn_remaining_qty_rma_uom;
1419: END IF;
1420:
1421: IF (g_asn_debug = 'Y') THEN
1422: asn_debug.put_line('Quantity to allocate to this line: ' || allocate_qty);
1423: END IF;
1424:
1425: --bug 4505906, record the used quantity in this session
1426: --Bug 8494868 When oe_line_id crossed 2^31 the pl/sql table should not throw any exception.
1452: temp_cascaded_table(temp_cascaded_table.LAST).primary_unit_of_measure
1453: );
1454: temp_cascaded_table(temp_cascaded_table.LAST).tax_amount := ROUND(temp_cascaded_table(temp_cascaded_table.LAST).quantity * tax_amount_factor, 4);
1455:
1456: IF (g_asn_debug = 'Y') THEN
1457: asn_debug.put_line('Current Tax Amount ' || TO_CHAR(temp_cascaded_table(temp_cascaded_table.LAST).tax_amount));
1458: END IF;
1459:
1460: -- update the remaining quantity
1453: );
1454: temp_cascaded_table(temp_cascaded_table.LAST).tax_amount := ROUND(temp_cascaded_table(temp_cascaded_table.LAST).quantity * tax_amount_factor, 4);
1455:
1456: IF (g_asn_debug = 'Y') THEN
1457: asn_debug.put_line('Current Tax Amount ' || TO_CHAR(temp_cascaded_table(temp_cascaded_table.LAST).tax_amount));
1458: END IF;
1459:
1460: -- update the remaining quantity
1461: txn_remaining_qty_rma_uom := txn_remaining_qty_rma_uom - allocate_qty;
1472:
1473: --}
1474:
1475: -- finished processing all lines
1476: IF (g_asn_debug = 'Y') THEN
1477: asn_debug.put_line('Hit exit condition');
1478: asn_debug.put_line('Temp table size ' || TO_CHAR(temp_cascaded_table.COUNT));
1479: asn_debug.put_line('Rows fetched ' || TO_CHAR(rma_lines_fetched));
1480: END IF;
1473: --}
1474:
1475: -- finished processing all lines
1476: IF (g_asn_debug = 'Y') THEN
1477: asn_debug.put_line('Hit exit condition');
1478: asn_debug.put_line('Temp table size ' || TO_CHAR(temp_cascaded_table.COUNT));
1479: asn_debug.put_line('Rows fetched ' || TO_CHAR(rma_lines_fetched));
1480: END IF;
1481:
1474:
1475: -- finished processing all lines
1476: IF (g_asn_debug = 'Y') THEN
1477: asn_debug.put_line('Hit exit condition');
1478: asn_debug.put_line('Temp table size ' || TO_CHAR(temp_cascaded_table.COUNT));
1479: asn_debug.put_line('Rows fetched ' || TO_CHAR(rma_lines_fetched));
1480: END IF;
1481:
1482: -- if nothing was processed, find out why, and quit
1475: -- finished processing all lines
1476: IF (g_asn_debug = 'Y') THEN
1477: asn_debug.put_line('Hit exit condition');
1478: asn_debug.put_line('Temp table size ' || TO_CHAR(temp_cascaded_table.COUNT));
1479: asn_debug.put_line('Rows fetched ' || TO_CHAR(rma_lines_fetched));
1480: END IF;
1481:
1482: -- if nothing was processed, find out why, and quit
1483: IF x_cascaded_table(n).quantity > 0
1482: -- if nothing was processed, find out why, and quit
1483: IF x_cascaded_table(n).quantity > 0
1484: AND txn_remaining_qty = x_cascaded_table(n).quantity THEN --{
1485: IF rma_lines_fetched = 0 THEN
1486: IF (g_asn_debug = 'Y') THEN
1487: asn_debug.put_line('No rows were retrieved from cursor.');
1488: END IF;
1489: ELSE
1490: IF (g_asn_debug = 'Y') THEN
1483: IF x_cascaded_table(n).quantity > 0
1484: AND txn_remaining_qty = x_cascaded_table(n).quantity THEN --{
1485: IF rma_lines_fetched = 0 THEN
1486: IF (g_asn_debug = 'Y') THEN
1487: asn_debug.put_line('No rows were retrieved from cursor.');
1488: END IF;
1489: ELSE
1490: IF (g_asn_debug = 'Y') THEN
1491: asn_debug.put_line('No rows were cascaded');
1486: IF (g_asn_debug = 'Y') THEN
1487: asn_debug.put_line('No rows were retrieved from cursor.');
1488: END IF;
1489: ELSE
1490: IF (g_asn_debug = 'Y') THEN
1491: asn_debug.put_line('No rows were cascaded');
1492: END IF;
1493: END IF;
1494:
1487: asn_debug.put_line('No rows were retrieved from cursor.');
1488: END IF;
1489: ELSE
1490: IF (g_asn_debug = 'Y') THEN
1491: asn_debug.put_line('No rows were cascaded');
1492: END IF;
1493: END IF;
1494:
1495: BEGIN
1620:
1621: -- check remaining qty vs tolerance qty using the last rma line's tolerance
1622: IF (txn_remaining_qty_rma_uom > rma_line_qty * x_qty_rcv_tolerance / 100) THEN
1623: -- the txn qty exceeds the tolerable qty
1624: IF (g_asn_debug = 'Y') THEN
1625: asn_debug.put_line('Extra Txn UOM Quantity ' || TO_CHAR(txn_remaining_qty));
1626: asn_debug.put_line('Extra RMA UOM Quantity ' || TO_CHAR(txn_remaining_qty_rma_uom));
1627: asn_debug.put_line('delete the temp table ');
1628: END IF;
1621: -- check remaining qty vs tolerance qty using the last rma line's tolerance
1622: IF (txn_remaining_qty_rma_uom > rma_line_qty * x_qty_rcv_tolerance / 100) THEN
1623: -- the txn qty exceeds the tolerable qty
1624: IF (g_asn_debug = 'Y') THEN
1625: asn_debug.put_line('Extra Txn UOM Quantity ' || TO_CHAR(txn_remaining_qty));
1626: asn_debug.put_line('Extra RMA UOM Quantity ' || TO_CHAR(txn_remaining_qty_rma_uom));
1627: asn_debug.put_line('delete the temp table ');
1628: END IF;
1629:
1622: IF (txn_remaining_qty_rma_uom > rma_line_qty * x_qty_rcv_tolerance / 100) THEN
1623: -- the txn qty exceeds the tolerable qty
1624: IF (g_asn_debug = 'Y') THEN
1625: asn_debug.put_line('Extra Txn UOM Quantity ' || TO_CHAR(txn_remaining_qty));
1626: asn_debug.put_line('Extra RMA UOM Quantity ' || TO_CHAR(txn_remaining_qty_rma_uom));
1627: asn_debug.put_line('delete the temp table ');
1628: END IF;
1629:
1630: x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1623: -- the txn qty exceeds the tolerable qty
1624: IF (g_asn_debug = 'Y') THEN
1625: asn_debug.put_line('Extra Txn UOM Quantity ' || TO_CHAR(txn_remaining_qty));
1626: asn_debug.put_line('Extra RMA UOM Quantity ' || TO_CHAR(txn_remaining_qty_rma_uom));
1627: asn_debug.put_line('delete the temp table ');
1628: END IF;
1629:
1630: x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
1631: rcv_error_pkg.set_error_message('RCV_SHIP_QTY_OVER_TOLERANCE', x_cascaded_table(n).error_message);
1633: rcv_error_pkg.set_token('QTY_B', x_cascaded_table(n).quantity - txn_remaining_qty);
1634: rcv_error_pkg.log_interface_error('QUANTITY', FALSE);
1635: temp_cascaded_table.DELETE;
1636:
1637: IF (g_asn_debug = 'Y') THEN
1638: asn_debug.put_line('mark the actual table with error status');
1639: asn_debug.put_line('Error Status ' || x_cascaded_table(n).error_status);
1640: asn_debug.put_line('Error message ' || x_cascaded_table(n).error_message);
1641: END IF;
1634: rcv_error_pkg.log_interface_error('QUANTITY', FALSE);
1635: temp_cascaded_table.DELETE;
1636:
1637: IF (g_asn_debug = 'Y') THEN
1638: asn_debug.put_line('mark the actual table with error status');
1639: asn_debug.put_line('Error Status ' || x_cascaded_table(n).error_status);
1640: asn_debug.put_line('Error message ' || x_cascaded_table(n).error_message);
1641: END IF;
1642: ELSE
1635: temp_cascaded_table.DELETE;
1636:
1637: IF (g_asn_debug = 'Y') THEN
1638: asn_debug.put_line('mark the actual table with error status');
1639: asn_debug.put_line('Error Status ' || x_cascaded_table(n).error_status);
1640: asn_debug.put_line('Error message ' || x_cascaded_table(n).error_message);
1641: END IF;
1642: ELSE
1643: -- }{ the txn qty does not exceed tolerance, allocate remaining to last row
1636:
1637: IF (g_asn_debug = 'Y') THEN
1638: asn_debug.put_line('mark the actual table with error status');
1639: asn_debug.put_line('Error Status ' || x_cascaded_table(n).error_status);
1640: asn_debug.put_line('Error message ' || x_cascaded_table(n).error_message);
1641: END IF;
1642: ELSE
1643: -- }{ the txn qty does not exceed tolerance, allocate remaining to last row
1644: IF (g_asn_debug = 'Y') THEN
1640: asn_debug.put_line('Error message ' || x_cascaded_table(n).error_message);
1641: END IF;
1642: ELSE
1643: -- }{ the txn qty does not exceed tolerance, allocate remaining to last row
1644: IF (g_asn_debug = 'Y') THEN
1645: asn_debug.put_line('txn qty does not exceed tolerance');
1646: END IF;
1647:
1648: /** Bug 5408054:
1641: END IF;
1642: ELSE
1643: -- }{ the txn qty does not exceed tolerance, allocate remaining to last row
1644: IF (g_asn_debug = 'Y') THEN
1645: asn_debug.put_line('txn qty does not exceed tolerance');
1646: END IF;
1647:
1648: /** Bug 5408054:
1649: * When the cursor 'rma_lines' fetches only 1 record, then transaction qty is not getting
1676:
1677: -- successful execution
1678:
1679: IF txn_remaining_qty = 0 THEN
1680: IF (g_asn_debug = 'Y') THEN
1681: asn_debug.put_line('Remaining Txn UOM quantity is zero ' || TO_CHAR(txn_remaining_qty));
1682: asn_debug.put_line('Remaining RMA UOM quantity is zero ' || TO_CHAR(txn_remaining_qty_rma_uom));
1683: asn_debug.put_line('Return the cascaded rows back to the calling procedure');
1684: END IF;
1677: -- successful execution
1678:
1679: IF txn_remaining_qty = 0 THEN
1680: IF (g_asn_debug = 'Y') THEN
1681: asn_debug.put_line('Remaining Txn UOM quantity is zero ' || TO_CHAR(txn_remaining_qty));
1682: asn_debug.put_line('Remaining RMA UOM quantity is zero ' || TO_CHAR(txn_remaining_qty_rma_uom));
1683: asn_debug.put_line('Return the cascaded rows back to the calling procedure');
1684: END IF;
1685: END IF;
1678:
1679: IF txn_remaining_qty = 0 THEN
1680: IF (g_asn_debug = 'Y') THEN
1681: asn_debug.put_line('Remaining Txn UOM quantity is zero ' || TO_CHAR(txn_remaining_qty));
1682: asn_debug.put_line('Remaining RMA UOM quantity is zero ' || TO_CHAR(txn_remaining_qty_rma_uom));
1683: asn_debug.put_line('Return the cascaded rows back to the calling procedure');
1684: END IF;
1685: END IF;
1686:
1679: IF txn_remaining_qty = 0 THEN
1680: IF (g_asn_debug = 'Y') THEN
1681: asn_debug.put_line('Remaining Txn UOM quantity is zero ' || TO_CHAR(txn_remaining_qty));
1682: asn_debug.put_line('Remaining RMA UOM quantity is zero ' || TO_CHAR(txn_remaining_qty_rma_uom));
1683: asn_debug.put_line('Return the cascaded rows back to the calling procedure');
1684: END IF;
1685: END IF;
1686:
1687: -- OPM change.Bug# 3061052
1698: END LOOP;
1699: END IF;
1700: END IF;
1701: /* end , INVCONV*/
1702: IF (g_asn_debug = 'Y') THEN
1703: asn_debug.put_line('Exit explode_line_quantity');
1704: END IF;
1705: EXCEPTION
1706: WHEN OTHERS THEN
1699: END IF;
1700: END IF;
1701: /* end , INVCONV*/
1702: IF (g_asn_debug = 'Y') THEN
1703: asn_debug.put_line('Exit explode_line_quantity');
1704: END IF;
1705: EXCEPTION
1706: WHEN OTHERS THEN
1707: x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1707: x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1708: rcv_error_pkg.set_sql_error_message('explode_line_quantity', x_progress);
1709: x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
1710:
1711: IF (g_asn_debug = 'Y') THEN
1712: asn_debug.put_line(TO_CHAR(n));
1713: asn_debug.put_line(SQLERRM);
1714: asn_debug.put_line('error ' || x_progress);
1715: END IF;
1708: rcv_error_pkg.set_sql_error_message('explode_line_quantity', x_progress);
1709: x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
1710:
1711: IF (g_asn_debug = 'Y') THEN
1712: asn_debug.put_line(TO_CHAR(n));
1713: asn_debug.put_line(SQLERRM);
1714: asn_debug.put_line('error ' || x_progress);
1715: END IF;
1716: END explode_line_quantity;
1709: x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
1710:
1711: IF (g_asn_debug = 'Y') THEN
1712: asn_debug.put_line(TO_CHAR(n));
1713: asn_debug.put_line(SQLERRM);
1714: asn_debug.put_line('error ' || x_progress);
1715: END IF;
1716: END explode_line_quantity;
1717:
1710:
1711: IF (g_asn_debug = 'Y') THEN
1712: asn_debug.put_line(TO_CHAR(n));
1713: asn_debug.put_line(SQLERRM);
1714: asn_debug.put_line('error ' || x_progress);
1715: END IF;
1716: END explode_line_quantity;
1717:
1718: PROCEDURE default_source_info(
1727:
1728: IF x_cascaded_table(n).source_document_code IS NULL THEN
1729: x_cascaded_table(n).source_document_code := 'RMA';
1730:
1731: IF (g_asn_debug = 'Y') THEN
1732: asn_debug.put_line('Defaulting SOURCE_DOCUMENT_CODE ' || x_cascaded_table(n).source_document_code);
1733: END IF;
1734: END IF;
1735:
1728: IF x_cascaded_table(n).source_document_code IS NULL THEN
1729: x_cascaded_table(n).source_document_code := 'RMA';
1730:
1731: IF (g_asn_debug = 'Y') THEN
1732: asn_debug.put_line('Defaulting SOURCE_DOCUMENT_CODE ' || x_cascaded_table(n).source_document_code);
1733: END IF;
1734: END IF;
1735:
1736: /* Bug3593237 - START */
1747: IF ( NVL(x_cascaded_table(n).auto_transact_code, 'RECEIVE') = 'DELIVER'
1748: OR x_cascaded_table(n).transaction_type = 'DELIVER') THEN
1749: x_cascaded_table(n).location_id := x_cascaded_table(n).deliver_to_location_id;
1750:
1751: IF (g_asn_debug = 'Y') THEN
1752: asn_debug.put_line('Defaulting LOCATION_ID ' || x_cascaded_table(n).deliver_to_location_id);
1753: END IF;
1754: ELSE
1755: x_cascaded_table(n).location_id := x_cascaded_table(n).ship_to_location_id;
1748: OR x_cascaded_table(n).transaction_type = 'DELIVER') THEN
1749: x_cascaded_table(n).location_id := x_cascaded_table(n).deliver_to_location_id;
1750:
1751: IF (g_asn_debug = 'Y') THEN
1752: asn_debug.put_line('Defaulting LOCATION_ID ' || x_cascaded_table(n).deliver_to_location_id);
1753: END IF;
1754: ELSE
1755: x_cascaded_table(n).location_id := x_cascaded_table(n).ship_to_location_id;
1756:
1753: END IF;
1754: ELSE
1755: x_cascaded_table(n).location_id := x_cascaded_table(n).ship_to_location_id;
1756:
1757: IF (g_asn_debug = 'Y') THEN
1758: asn_debug.put_line('Defaulting LOCATION_ID ' || x_cascaded_table(n).ship_to_location_id);
1759: END IF;
1760: END IF;
1761: END IF;
1754: ELSE
1755: x_cascaded_table(n).location_id := x_cascaded_table(n).ship_to_location_id;
1756:
1757: IF (g_asn_debug = 'Y') THEN
1758: asn_debug.put_line('Defaulting LOCATION_ID ' || x_cascaded_table(n).ship_to_location_id);
1759: END IF;
1760: END IF;
1761: END IF;
1762: /* Bug3593237 - END */
1771: OR ( x_cascaded_table(n).destination_type_code = 'INVENTORY'
1772: AND x_cascaded_table(n).auto_transact_code = 'RECEIVE') THEN
1773: x_cascaded_table(n).destination_type_code := 'RECEIVING';
1774:
1775: IF (g_asn_debug = 'Y') THEN
1776: asn_debug.put_line('Defaulting DESTINATION_TYPE_CODE ' || x_cascaded_table(n).destination_type_code);
1777: END IF;
1778: END IF;
1779:
1772: AND x_cascaded_table(n).auto_transact_code = 'RECEIVE') THEN
1773: x_cascaded_table(n).destination_type_code := 'RECEIVING';
1774:
1775: IF (g_asn_debug = 'Y') THEN
1776: asn_debug.put_line('Defaulting DESTINATION_TYPE_CODE ' || x_cascaded_table(n).destination_type_code);
1777: END IF;
1778: END IF;
1779:
1780: /* Bug 3592340.
1786: IF ( x_cascaded_table(n).transaction_type = 'RECEIVE'
1787: AND x_cascaded_table(n).auto_transact_code = 'DELIVER') THEN
1788: x_cascaded_table(n).destination_type_code := 'INVENTORY';
1789:
1790: IF (g_asn_debug = 'Y') THEN
1791: asn_debug.put_line('Defaulting DESTINATION_TYPE_CODE for direct delivery ' || x_cascaded_table(n).destination_type_code);
1792: END IF;
1793: END IF;
1794:
1787: AND x_cascaded_table(n).auto_transact_code = 'DELIVER') THEN
1788: x_cascaded_table(n).destination_type_code := 'INVENTORY';
1789:
1790: IF (g_asn_debug = 'Y') THEN
1791: asn_debug.put_line('Defaulting DESTINATION_TYPE_CODE for direct delivery ' || x_cascaded_table(n).destination_type_code);
1792: END IF;
1793: END IF;
1794:
1795: IF x_cascaded_table(n).destination_context IS NULL THEN
1794:
1795: IF x_cascaded_table(n).destination_context IS NULL THEN
1796: x_cascaded_table(n).destination_context := x_cascaded_table(n).destination_type_code;
1797:
1798: IF (g_asn_debug = 'Y') THEN
1799: asn_debug.put_line('Defaulting DESTINATION_CONTEXT ' || x_cascaded_table(n).destination_context);
1800: END IF;
1801: END IF;
1802: END default_destination_info;
1795: IF x_cascaded_table(n).destination_context IS NULL THEN
1796: x_cascaded_table(n).destination_context := x_cascaded_table(n).destination_type_code;
1797:
1798: IF (g_asn_debug = 'Y') THEN
1799: asn_debug.put_line('Defaulting DESTINATION_CONTEXT ' || x_cascaded_table(n).destination_context);
1800: END IF;
1801: END IF;
1802: END default_destination_info;
1803:
1808: BEGIN
1809: IF x_cascaded_table(n).transaction_type IS NULL THEN
1810: x_cascaded_table(n).transaction_type := 'RECEIVE';
1811:
1812: IF (g_asn_debug = 'Y') THEN
1813: asn_debug.put_line('Defaulting TRANSACTION_TYPE ' || x_cascaded_table(n).transaction_type);
1814: END IF;
1815: END IF;
1816: END default_transaction_info;
1809: IF x_cascaded_table(n).transaction_type IS NULL THEN
1810: x_cascaded_table(n).transaction_type := 'RECEIVE';
1811:
1812: IF (g_asn_debug = 'Y') THEN
1813: asn_debug.put_line('Defaulting TRANSACTION_TYPE ' || x_cascaded_table(n).transaction_type);
1814: END IF;
1815: END IF;
1816: END default_transaction_info;
1817:
1822: BEGIN
1823: IF x_cascaded_table(n).processing_mode_code IS NULL THEN
1824: x_cascaded_table(n).processing_mode_code := 'BATCH';
1825:
1826: IF (g_asn_debug = 'Y') THEN
1827: asn_debug.put_line('Defaulting PROCESSING_MODE_CODE ' || x_cascaded_table(n).processing_mode_code);
1828: END IF;
1829: END IF;
1830:
1823: IF x_cascaded_table(n).processing_mode_code IS NULL THEN
1824: x_cascaded_table(n).processing_mode_code := 'BATCH';
1825:
1826: IF (g_asn_debug = 'Y') THEN
1827: asn_debug.put_line('Defaulting PROCESSING_MODE_CODE ' || x_cascaded_table(n).processing_mode_code);
1828: END IF;
1829: END IF;
1830:
1831: x_cascaded_table(n).processing_status_code := 'RUNNING';
1834: -- This has to be set to running otherwise C code in rvtbm
1835: -- will not pick it up
1836: x_cascaded_table(n).processing_status_code := 'RUNNING';
1837:
1838: IF (g_asn_debug = 'Y') THEN
1839: asn_debug.put_line('Defaulting PROCESSING_STATUS_CODE ' || x_cascaded_table(n).processing_status_code);
1840: END IF;
1841: END IF;
1842: END default_processing_info;
1835: -- will not pick it up
1836: x_cascaded_table(n).processing_status_code := 'RUNNING';
1837:
1838: IF (g_asn_debug = 'Y') THEN
1839: asn_debug.put_line('Defaulting PROCESSING_STATUS_CODE ' || x_cascaded_table(n).processing_status_code);
1840: END IF;
1841: END IF;
1842: END default_processing_info;
1843:
1888: WHERE organization_id = x_cascaded_table(n).to_organization_id;
1889:
1890: END IF;
1891:
1892: IF (g_asn_debug = 'Y') THEN
1893: asn_debug.put_line('Defaulted routing_header_id ' || x_cascaded_table(n).routing_header_id);
1894: END IF;
1895:
1896: IF x_cascaded_table(n).routing_step_id IS NULL THEN
1889:
1890: END IF;
1891:
1892: IF (g_asn_debug = 'Y') THEN
1893: asn_debug.put_line('Defaulted routing_header_id ' || x_cascaded_table(n).routing_header_id);
1894: END IF;
1895:
1896: IF x_cascaded_table(n).routing_step_id IS NULL THEN
1897: x_cascaded_table(n).routing_step_id := 1;
1895:
1896: IF x_cascaded_table(n).routing_step_id IS NULL THEN
1897: x_cascaded_table(n).routing_step_id := 1;
1898:
1899: IF (g_asn_debug = 'Y') THEN
1900: asn_debug.put_line('Defaulting routing_step_id ' || x_cascaded_table(n).routing_step_id);
1901: END IF;
1902: END IF;
1903:
1896: IF x_cascaded_table(n).routing_step_id IS NULL THEN
1897: x_cascaded_table(n).routing_step_id := 1;
1898:
1899: IF (g_asn_debug = 'Y') THEN
1900: asn_debug.put_line('Defaulting routing_step_id ' || x_cascaded_table(n).routing_step_id);
1901: END IF;
1902: END IF;
1903:
1904: END IF;
1989: */
1990: IF x_cascaded_table(n).item_revision IS NULL
1991: AND (NVL(x_cascaded_table(n).mobile_txn, 'N') = 'N')
1992: AND x_cascaded_table(n).error_status IN('S', 'W') THEN
1993: IF (g_asn_debug = 'Y') THEN
1994: asn_debug.put_line('Defaulting item revision');
1995: END IF;
1996:
1997: item_id_record.item_id := x_cascaded_table(n).item_id;
1990: IF x_cascaded_table(n).item_revision IS NULL
1991: AND (NVL(x_cascaded_table(n).mobile_txn, 'N') = 'N')
1992: AND x_cascaded_table(n).error_status IN('S', 'W') THEN
1993: IF (g_asn_debug = 'Y') THEN
1994: asn_debug.put_line('Defaulting item revision');
1995: END IF;
1996:
1997: item_id_record.item_id := x_cascaded_table(n).item_id;
1998: item_id_record.po_line_id := x_cascaded_table(n).oe_order_line_id;
2002: item_id_record.error_record.error_message := NULL;
2003: default_item_revision(item_id_record);
2004: x_cascaded_table(n).item_revision := item_id_record.item_revision;
2005:
2006: IF (g_asn_debug = 'Y') THEN
2007: asn_debug.put_line(NVL(item_id_record.item_revision, 'Item Revision is null'));
2008: END IF;
2009:
2010: x_cascaded_table(n).error_status := item_id_record.error_record.error_status;
2003: default_item_revision(item_id_record);
2004: x_cascaded_table(n).item_revision := item_id_record.item_revision;
2005:
2006: IF (g_asn_debug = 'Y') THEN
2007: asn_debug.put_line(NVL(item_id_record.item_revision, 'Item Revision is null'));
2008: END IF;
2009:
2010: x_cascaded_table(n).error_status := item_id_record.error_record.error_status;
2011: x_cascaded_table(n).error_message := item_id_record.error_record.error_message;
2013:
2014: -- default the category_id
2015: IF x_cascaded_table(n).error_status IN('S', 'W')
2016: AND x_cascaded_table(n).category_id IS NULL THEN
2017: IF (g_asn_debug = 'Y') THEN
2018: asn_debug.put_line('Defaulting item category id');
2019: END IF;
2020:
2021: -- get the default category_set_id for PO
2014: -- default the category_id
2015: IF x_cascaded_table(n).error_status IN('S', 'W')
2016: AND x_cascaded_table(n).category_id IS NULL THEN
2017: IF (g_asn_debug = 'Y') THEN
2018: asn_debug.put_line('Defaulting item category id');
2019: END IF;
2020:
2021: -- get the default category_set_id for PO
2022: -- refer to INIT_RCV_CONTROL_BLOCK in POXCOSEU.pld, which eventually calls PO_CORE_S.get_item_category_structure
2033: WHERE inventory_item_id = x_cascaded_table(n).item_id
2034: AND organization_id = x_cascaded_table(n).to_organization_id
2035: AND category_set_id = l_category_set_id;
2036:
2037: IF (g_asn_debug = 'Y') THEN
2038: asn_debug.put_line('Defaulted category_id ' || x_cascaded_table(n).category_id);
2039: END IF;
2040: END IF;
2041: EXCEPTION
2034: AND organization_id = x_cascaded_table(n).to_organization_id
2035: AND category_set_id = l_category_set_id;
2036:
2037: IF (g_asn_debug = 'Y') THEN
2038: asn_debug.put_line('Defaulted category_id ' || x_cascaded_table(n).category_id);
2039: END IF;
2040: END IF;
2041: EXCEPTION
2042: WHEN OTHERS THEN
2129: -- ship_to_org
2130: IF ( x_cascaded_table(n).error_status IN('S', 'W')
2131: AND x_cascaded_table(n).to_organization_id IS NULL
2132: AND default_rma_record.to_organization_id IS NOT NULL) THEN
2133: IF (g_asn_debug = 'Y') THEN
2134: asn_debug.put_line('Defaulting org id from default RMA');
2135: END IF;
2136:
2137: x_cascaded_table(n).to_organization_id := default_rma_record.to_organization_id;
2130: IF ( x_cascaded_table(n).error_status IN('S', 'W')
2131: AND x_cascaded_table(n).to_organization_id IS NULL
2132: AND default_rma_record.to_organization_id IS NOT NULL) THEN
2133: IF (g_asn_debug = 'Y') THEN
2134: asn_debug.put_line('Defaulting org id from default RMA');
2135: END IF;
2136:
2137: x_cascaded_table(n).to_organization_id := default_rma_record.to_organization_id;
2138: END IF;
2146: BEGIN
2147: IF ( x_cascaded_table(n).error_status IN('S', 'W')
2148: AND x_cascaded_table(n).customer_id IS NULL) THEN
2149: IF (x_header_record.header_record.customer_id IS NOT NULL) THEN
2150: IF (g_asn_debug = 'Y') THEN
2151: asn_debug.put_line('Defaulting customer info from header');
2152: END IF;
2153:
2154: x_cascaded_table(n).customer_id := x_header_record.header_record.customer_id;
2147: IF ( x_cascaded_table(n).error_status IN('S', 'W')
2148: AND x_cascaded_table(n).customer_id IS NULL) THEN
2149: IF (x_header_record.header_record.customer_id IS NOT NULL) THEN
2150: IF (g_asn_debug = 'Y') THEN
2151: asn_debug.put_line('Defaulting customer info from header');
2152: END IF;
2153:
2154: x_cascaded_table(n).customer_id := x_header_record.header_record.customer_id;
2155: ELSE
2152: END IF;
2153:
2154: x_cascaded_table(n).customer_id := x_header_record.header_record.customer_id;
2155: ELSE
2156: IF (g_asn_debug = 'Y') THEN
2157: asn_debug.put_line('No customer info available');
2158: END IF;
2159:
2160: x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
2153:
2154: x_cascaded_table(n).customer_id := x_header_record.header_record.customer_id;
2155: ELSE
2156: IF (g_asn_debug = 'Y') THEN
2157: asn_debug.put_line('No customer info available');
2158: END IF;
2159:
2160: x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
2161: rcv_error_pkg.set_error_message('PO_PDOI_DERV_ERROR', x_cascaded_table(n).error_message);
2174: BEGIN
2175: IF ( x_cascaded_table(n).error_status IN('S', 'W')
2176: AND x_cascaded_table(n).customer_site_id IS NULL) THEN
2177: IF (x_header_record.header_record.customer_site_id IS NOT NULL) THEN
2178: IF (g_asn_debug = 'Y') THEN
2179: asn_debug.put_line('Defaulting customer site info from header');
2180: END IF;
2181:
2182: x_cascaded_table(n).customer_site_id := x_header_record.header_record.customer_site_id;
2175: IF ( x_cascaded_table(n).error_status IN('S', 'W')
2176: AND x_cascaded_table(n).customer_site_id IS NULL) THEN
2177: IF (x_header_record.header_record.customer_site_id IS NOT NULL) THEN
2178: IF (g_asn_debug = 'Y') THEN
2179: asn_debug.put_line('Defaulting customer site info from header');
2180: END IF;
2181:
2182: x_cascaded_table(n).customer_site_id := x_header_record.header_record.customer_site_id;
2183: ELSE
2180: END IF;
2181:
2182: x_cascaded_table(n).customer_site_id := x_header_record.header_record.customer_site_id;
2183: ELSE
2184: IF (g_asn_debug = 'Y') THEN
2185: asn_debug.put_line('No customer site info available');
2186: END IF;
2187:
2188: x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
2181:
2182: x_cascaded_table(n).customer_site_id := x_header_record.header_record.customer_site_id;
2183: ELSE
2184: IF (g_asn_debug = 'Y') THEN
2185: asn_debug.put_line('No customer site info available');
2186: END IF;
2187:
2188: x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
2189: rcv_error_pkg.set_error_message('PO_PDOI_DERV_ERROR', x_cascaded_table(n).error_message);
2204: AND x_cascaded_table(n).from_organization_code IS NULL THEN
2205: x_cascaded_table(n).from_organization_id := x_header_record.header_record.from_organization_id;
2206: x_cascaded_table(n).from_organization_code := x_header_record.header_record.from_organization_code;
2207:
2208: IF (g_asn_debug = 'Y') THEN
2209: asn_debug.put_line('Defaulting from HEADER FROM_ORGANIZATION_ID ' || TO_CHAR(x_cascaded_table(n).from_organization_id));
2210: asn_debug.put_line('Defaulting from HEADER FROM_ORGANIZATION_CODE ' || x_cascaded_table(n).from_organization_code);
2211: END IF;
2212: END IF;
2205: x_cascaded_table(n).from_organization_id := x_header_record.header_record.from_organization_id;
2206: x_cascaded_table(n).from_organization_code := x_header_record.header_record.from_organization_code;
2207:
2208: IF (g_asn_debug = 'Y') THEN
2209: asn_debug.put_line('Defaulting from HEADER FROM_ORGANIZATION_ID ' || TO_CHAR(x_cascaded_table(n).from_organization_id));
2210: asn_debug.put_line('Defaulting from HEADER FROM_ORGANIZATION_CODE ' || x_cascaded_table(n).from_organization_code);
2211: END IF;
2212: END IF;
2213: END default_from_org_header;
2206: x_cascaded_table(n).from_organization_code := x_header_record.header_record.from_organization_code;
2207:
2208: IF (g_asn_debug = 'Y') THEN
2209: asn_debug.put_line('Defaulting from HEADER FROM_ORGANIZATION_ID ' || TO_CHAR(x_cascaded_table(n).from_organization_id));
2210: asn_debug.put_line('Defaulting from HEADER FROM_ORGANIZATION_CODE ' || x_cascaded_table(n).from_organization_code);
2211: END IF;
2212: END IF;
2213: END default_from_org_header;
2214:
2223: AND x_cascaded_table(n).to_organization_code IS NULL THEN
2224: x_cascaded_table(n).to_organization_id := x_header_record.header_record.ship_to_organization_id;
2225: x_cascaded_table(n).to_organization_code := x_header_record.header_record.ship_to_organization_code;
2226:
2227: IF (g_asn_debug = 'Y') THEN
2228: asn_debug.put_line('Defaulting from HEADER TO_ORGANIZATION_ID ' || TO_CHAR(x_cascaded_table(n).to_organization_id));
2229: asn_debug.put_line('Defaulting from HEADER TO_ORGANIZATION_CODE ' || x_cascaded_table(n).to_organization_code);
2230: END IF;
2231: END IF;
2224: x_cascaded_table(n).to_organization_id := x_header_record.header_record.ship_to_organization_id;
2225: x_cascaded_table(n).to_organization_code := x_header_record.header_record.ship_to_organization_code;
2226:
2227: IF (g_asn_debug = 'Y') THEN
2228: asn_debug.put_line('Defaulting from HEADER TO_ORGANIZATION_ID ' || TO_CHAR(x_cascaded_table(n).to_organization_id));
2229: asn_debug.put_line('Defaulting from HEADER TO_ORGANIZATION_CODE ' || x_cascaded_table(n).to_organization_code);
2230: END IF;
2231: END IF;
2232:
2225: x_cascaded_table(n).to_organization_code := x_header_record.header_record.ship_to_organization_code;
2226:
2227: IF (g_asn_debug = 'Y') THEN
2228: asn_debug.put_line('Defaulting from HEADER TO_ORGANIZATION_ID ' || TO_CHAR(x_cascaded_table(n).to_organization_id));
2229: asn_debug.put_line('Defaulting from HEADER TO_ORGANIZATION_CODE ' || x_cascaded_table(n).to_organization_code);
2230: END IF;
2231: END IF;
2232:
2233: -- ship_to_location
2235: AND x_cascaded_table(n).ship_to_location_code IS NULL) THEN -- Check this with George
2236: x_cascaded_table(n).ship_to_location_code := x_header_record.header_record.location_code;
2237: x_cascaded_table(n).ship_to_location_id := x_header_record.header_record.location_id;
2238:
2239: IF (g_asn_debug = 'Y') THEN
2240: asn_debug.put_line('Defaulting from HEADER LOCATION_ID ' || TO_CHAR(x_cascaded_table(n).location_id));
2241: END IF;
2242: END IF;
2243: END default_ship_to_header;
2236: x_cascaded_table(n).ship_to_location_code := x_header_record.header_record.location_code;
2237: x_cascaded_table(n).ship_to_location_id := x_header_record.header_record.location_id;
2238:
2239: IF (g_asn_debug = 'Y') THEN
2240: asn_debug.put_line('Defaulting from HEADER LOCATION_ID ' || TO_CHAR(x_cascaded_table(n).location_id));
2241: END IF;
2242: END IF;
2243: END default_ship_to_header;
2244:
2256: x_cascaded_table(n).currency_conversion_type := x_header_record.header_record.conversion_rate_type;
2257: x_cascaded_table(n).currency_conversion_rate := x_header_record.header_record.conversion_rate;
2258: x_cascaded_table(n).currency_conversion_date := x_header_record.header_record.conversion_rate_date;
2259:
2260: IF (g_asn_debug = 'Y') THEN
2261: asn_debug.put_line('Defaulting from HEADER CURRENCY_CODE ' || x_cascaded_table(n).currency_code);
2262: asn_debug.put_line('Defaulting from HEADER CURRENCY_CONVERSION_TYPE ' || x_cascaded_table(n).currency_conversion_type);
2263: asn_debug.put_line('Defaulting from HEADER CURRENCY_CONVERSION_RATE ' || TO_CHAR(x_cascaded_table(n).currency_conversion_rate));
2264: asn_debug.put_line('Defaulting from HEADER CURRENCY_CONVERSION_DATE ' || TO_CHAR(x_cascaded_table(n).currency_conversion_date, 'DD/MM/YYYY'));
2257: x_cascaded_table(n).currency_conversion_rate := x_header_record.header_record.conversion_rate;
2258: x_cascaded_table(n).currency_conversion_date := x_header_record.header_record.conversion_rate_date;
2259:
2260: IF (g_asn_debug = 'Y') THEN
2261: asn_debug.put_line('Defaulting from HEADER CURRENCY_CODE ' || x_cascaded_table(n).currency_code);
2262: asn_debug.put_line('Defaulting from HEADER CURRENCY_CONVERSION_TYPE ' || x_cascaded_table(n).currency_conversion_type);
2263: asn_debug.put_line('Defaulting from HEADER CURRENCY_CONVERSION_RATE ' || TO_CHAR(x_cascaded_table(n).currency_conversion_rate));
2264: asn_debug.put_line('Defaulting from HEADER CURRENCY_CONVERSION_DATE ' || TO_CHAR(x_cascaded_table(n).currency_conversion_date, 'DD/MM/YYYY'));
2265: END IF;
2258: x_cascaded_table(n).currency_conversion_date := x_header_record.header_record.conversion_rate_date;
2259:
2260: IF (g_asn_debug = 'Y') THEN
2261: asn_debug.put_line('Defaulting from HEADER CURRENCY_CODE ' || x_cascaded_table(n).currency_code);
2262: asn_debug.put_line('Defaulting from HEADER CURRENCY_CONVERSION_TYPE ' || x_cascaded_table(n).currency_conversion_type);
2263: asn_debug.put_line('Defaulting from HEADER CURRENCY_CONVERSION_RATE ' || TO_CHAR(x_cascaded_table(n).currency_conversion_rate));
2264: asn_debug.put_line('Defaulting from HEADER CURRENCY_CONVERSION_DATE ' || TO_CHAR(x_cascaded_table(n).currency_conversion_date, 'DD/MM/YYYY'));
2265: END IF;
2266: END IF;
2259:
2260: IF (g_asn_debug = 'Y') THEN
2261: asn_debug.put_line('Defaulting from HEADER CURRENCY_CODE ' || x_cascaded_table(n).currency_code);
2262: asn_debug.put_line('Defaulting from HEADER CURRENCY_CONVERSION_TYPE ' || x_cascaded_table(n).currency_conversion_type);
2263: asn_debug.put_line('Defaulting from HEADER CURRENCY_CONVERSION_RATE ' || TO_CHAR(x_cascaded_table(n).currency_conversion_rate));
2264: asn_debug.put_line('Defaulting from HEADER CURRENCY_CONVERSION_DATE ' || TO_CHAR(x_cascaded_table(n).currency_conversion_date, 'DD/MM/YYYY'));
2265: END IF;
2266: END IF;
2267: END default_currency_info_header;
2260: IF (g_asn_debug = 'Y') THEN
2261: asn_debug.put_line('Defaulting from HEADER CURRENCY_CODE ' || x_cascaded_table(n).currency_code);
2262: asn_debug.put_line('Defaulting from HEADER CURRENCY_CONVERSION_TYPE ' || x_cascaded_table(n).currency_conversion_type);
2263: asn_debug.put_line('Defaulting from HEADER CURRENCY_CONVERSION_RATE ' || TO_CHAR(x_cascaded_table(n).currency_conversion_rate));
2264: asn_debug.put_line('Defaulting from HEADER CURRENCY_CONVERSION_DATE ' || TO_CHAR(x_cascaded_table(n).currency_conversion_date, 'DD/MM/YYYY'));
2265: END IF;
2266: END IF;
2267: END default_currency_info_header;
2268:
2274: BEGIN
2275: IF x_cascaded_table(n).shipment_num IS NULL THEN
2276: x_cascaded_table(n).shipment_num := x_header_record.header_record.shipment_num;
2277:
2278: IF (g_asn_debug = 'Y') THEN
2279: asn_debug.put_line('Defaulting from HEADER SHIPMENT_NUM ' || x_cascaded_table(n).shipment_num);
2280: END IF;
2281: END IF;
2282: END default_shipment_num_header;
2275: IF x_cascaded_table(n).shipment_num IS NULL THEN
2276: x_cascaded_table(n).shipment_num := x_header_record.header_record.shipment_num;
2277:
2278: IF (g_asn_debug = 'Y') THEN
2279: asn_debug.put_line('Defaulting from HEADER SHIPMENT_NUM ' || x_cascaded_table(n).shipment_num);
2280: END IF;
2281: END IF;
2282: END default_shipment_num_header;
2283:
2289: BEGIN
2290: IF x_cascaded_table(n).freight_carrier_code IS NULL THEN
2291: x_cascaded_table(n).freight_carrier_code := x_header_record.header_record.freight_carrier_code;
2292:
2293: IF (g_asn_debug = 'Y') THEN
2294: asn_debug.put_line('Defaulting from HEADER FREIGHT_CARRIER_CODE ' || x_cascaded_table(n).freight_carrier_code);
2295: END IF;
2296: END IF;
2297: END default_freight_carrier_header;
2290: IF x_cascaded_table(n).freight_carrier_code IS NULL THEN
2291: x_cascaded_table(n).freight_carrier_code := x_header_record.header_record.freight_carrier_code;
2292:
2293: IF (g_asn_debug = 'Y') THEN
2294: asn_debug.put_line('Defaulting from HEADER FREIGHT_CARRIER_CODE ' || x_cascaded_table(n).freight_carrier_code);
2295: END IF;
2296: END IF;
2297: END default_freight_carrier_header;
2298:
2304: BEGIN
2305: IF x_cascaded_table(n).bill_of_lading IS NULL THEN
2306: x_cascaded_table(n).bill_of_lading := x_header_record.header_record.bill_of_lading;
2307:
2308: IF (g_asn_debug = 'Y') THEN
2309: asn_debug.put_line('Defaulting from HEADER BILL_OF_LADING ' || x_cascaded_table(n).bill_of_lading);
2310: END IF;
2311: END IF;
2312: END default_bill_of_lading_header;
2305: IF x_cascaded_table(n).bill_of_lading IS NULL THEN
2306: x_cascaded_table(n).bill_of_lading := x_header_record.header_record.bill_of_lading;
2307:
2308: IF (g_asn_debug = 'Y') THEN
2309: asn_debug.put_line('Defaulting from HEADER BILL_OF_LADING ' || x_cascaded_table(n).bill_of_lading);
2310: END IF;
2311: END IF;
2312: END default_bill_of_lading_header;
2313:
2319: BEGIN
2320: IF x_cascaded_table(n).packing_slip IS NULL THEN
2321: x_cascaded_table(n).packing_slip := x_header_record.header_record.packing_slip;
2322:
2323: IF (g_asn_debug = 'Y') THEN
2324: asn_debug.put_line('Defaulting from HEADER PACKING_SLIP ' || x_cascaded_table(n).packing_slip);
2325: END IF;
2326: END IF;
2327: END default_packing_slip_header;
2320: IF x_cascaded_table(n).packing_slip IS NULL THEN
2321: x_cascaded_table(n).packing_slip := x_header_record.header_record.packing_slip;
2322:
2323: IF (g_asn_debug = 'Y') THEN
2324: asn_debug.put_line('Defaulting from HEADER PACKING_SLIP ' || x_cascaded_table(n).packing_slip);
2325: END IF;
2326: END IF;
2327: END default_packing_slip_header;
2328:
2334: BEGIN
2335: IF x_cascaded_table(n).shipped_date IS NULL THEN
2336: x_cascaded_table(n).shipped_date := x_header_record.header_record.shipped_date;
2337:
2338: IF (g_asn_debug = 'Y') THEN
2339: asn_debug.put_line('Defaulting from HEADER SHIPPED_DATE ' || TO_CHAR(x_cascaded_table(n).shipped_date, 'DD/MM/YYYY'));
2340: END IF;
2341: END IF;
2342: END default_ship_date_header;
2335: IF x_cascaded_table(n).shipped_date IS NULL THEN
2336: x_cascaded_table(n).shipped_date := x_header_record.header_record.shipped_date;
2337:
2338: IF (g_asn_debug = 'Y') THEN
2339: asn_debug.put_line('Defaulting from HEADER SHIPPED_DATE ' || TO_CHAR(x_cascaded_table(n).shipped_date, 'DD/MM/YYYY'));
2340: END IF;
2341: END IF;
2342: END default_ship_date_header;
2343:
2349: BEGIN
2350: IF x_cascaded_table(n).expected_receipt_date IS NULL THEN
2351: x_cascaded_table(n).expected_receipt_date := x_header_record.header_record.expected_receipt_date;
2352:
2353: IF (g_asn_debug = 'Y') THEN
2354: asn_debug.put_line('Defaulting from HEADER EXPECTED_RECEIPT_DATE ' || TO_CHAR(x_cascaded_table(n).expected_receipt_date, 'DD/MM/YYYY'));
2355: END IF;
2356: END IF;
2357: END default_receipt_date_header;
2350: IF x_cascaded_table(n).expected_receipt_date IS NULL THEN
2351: x_cascaded_table(n).expected_receipt_date := x_header_record.header_record.expected_receipt_date;
2352:
2353: IF (g_asn_debug = 'Y') THEN
2354: asn_debug.put_line('Defaulting from HEADER EXPECTED_RECEIPT_DATE ' || TO_CHAR(x_cascaded_table(n).expected_receipt_date, 'DD/MM/YYYY'));
2355: END IF;
2356: END IF;
2357: END default_receipt_date_header;
2358:
2364: BEGIN
2365: IF x_cascaded_table(n).num_of_containers IS NULL THEN
2366: x_cascaded_table(n).num_of_containers := x_header_record.header_record.num_of_containers;
2367:
2368: IF (g_asn_debug = 'Y') THEN
2369: asn_debug.put_line('Defaulting from HEADER NUM_OF_CONTAINERS ' || TO_CHAR(x_cascaded_table(n).num_of_containers));
2370: END IF;
2371: END IF;
2372: END default_num_containers_header;
2365: IF x_cascaded_table(n).num_of_containers IS NULL THEN
2366: x_cascaded_table(n).num_of_containers := x_header_record.header_record.num_of_containers;
2367:
2368: IF (g_asn_debug = 'Y') THEN
2369: asn_debug.put_line('Defaulting from HEADER NUM_OF_CONTAINERS ' || TO_CHAR(x_cascaded_table(n).num_of_containers));
2370: END IF;
2371: END IF;
2372: END default_num_containers_header;
2373:
2379: BEGIN
2380: IF x_cascaded_table(n).waybill_airbill_num IS NULL THEN
2381: x_cascaded_table(n).waybill_airbill_num := x_header_record.header_record.waybill_airbill_num;
2382:
2383: IF (g_asn_debug = 'Y') THEN
2384: asn_debug.put_line('Defaulting from HEADER WAYBILL_AIRBILL_NUM ' || x_cascaded_table(n).waybill_airbill_num);
2385: END IF;
2386: END IF;
2387: END default_waybill_header;
2380: IF x_cascaded_table(n).waybill_airbill_num IS NULL THEN
2381: x_cascaded_table(n).waybill_airbill_num := x_header_record.header_record.waybill_airbill_num;
2382:
2383: IF (g_asn_debug = 'Y') THEN
2384: asn_debug.put_line('Defaulting from HEADER WAYBILL_AIRBILL_NUM ' || x_cascaded_table(n).waybill_airbill_num);
2385: END IF;
2386: END IF;
2387: END default_waybill_header;
2388:
2394: BEGIN
2395: IF x_cascaded_table(n).tax_name IS NULL THEN
2396: x_cascaded_table(n).tax_name := x_header_record.header_record.tax_name;
2397:
2398: IF (g_asn_debug = 'Y') THEN
2399: asn_debug.put_line('Defaulting from HEADER TAX_NAME ' || x_cascaded_table(n).tax_name);
2400: END IF;
2401: END IF;
2402: END default_tax_name_header;
2395: IF x_cascaded_table(n).tax_name IS NULL THEN
2396: x_cascaded_table(n).tax_name := x_header_record.header_record.tax_name;
2397:
2398: IF (g_asn_debug = 'Y') THEN
2399: asn_debug.put_line('Defaulting from HEADER TAX_NAME ' || x_cascaded_table(n).tax_name);
2400: END IF;
2401: END IF;
2402: END default_tax_name_header;
2403:
2426: IF (x_cascaded_table(n).error_status NOT IN('S', 'W')) THEN
2427: RETURN;
2428: END IF;
2429:
2430: IF (g_asn_debug = 'Y') THEN
2431: asn_debug.put_line('Before call to validate UOM');
2432: asn_debug.put_line('Quantity ' || TO_CHAR(x_cascaded_table(n).quantity));
2433: END IF;
2434:
2427: RETURN;
2428: END IF;
2429:
2430: IF (g_asn_debug = 'Y') THEN
2431: asn_debug.put_line('Before call to validate UOM');
2432: asn_debug.put_line('Quantity ' || TO_CHAR(x_cascaded_table(n).quantity));
2433: END IF;
2434:
2435: /* Commenting the following line because OE stores UOM differently
2428: END IF;
2429:
2430: IF (g_asn_debug = 'Y') THEN
2431: asn_debug.put_line('Before call to validate UOM');
2432: asn_debug.put_line('Quantity ' || TO_CHAR(x_cascaded_table(n).quantity));
2433: END IF;
2434:
2435: /* Commenting the following line because OE stores UOM differently
2436: * from PO, causing conversion problems.
2446: uom_record.primary_unit_of_measure := x_cascaded_table(n).primary_unit_of_measure;
2447: uom_record.error_record.error_status := 'S';
2448: uom_record.error_record.error_message := NULL;
2449:
2450: IF (g_asn_debug = 'Y') THEN
2451: asn_debug.put_line('Validating UOM');
2452: END IF;
2453:
2454: validate_uom(uom_record);
2447: uom_record.error_record.error_status := 'S';
2448: uom_record.error_record.error_message := NULL;
2449:
2450: IF (g_asn_debug = 'Y') THEN
2451: asn_debug.put_line('Validating UOM');
2452: END IF;
2453:
2454: validate_uom(uom_record);
2455: x_cascaded_table(n).error_status := uom_record.error_record.error_status;
2483: item_id_record.use_mtl_serial := x_cascaded_table(n).use_mtl_serial;
2484: item_id_record.error_record.error_status := 'S';
2485: item_id_record.error_record.error_message := NULL;
2486:
2487: IF (g_asn_debug = 'Y') THEN
2488: asn_debug.put_line('Validating Item');
2489: asn_debug.put_line(TO_CHAR(x_cascaded_table(n).item_id));
2490: END IF;
2491:
2484: item_id_record.error_record.error_status := 'S';
2485: item_id_record.error_record.error_message := NULL;
2486:
2487: IF (g_asn_debug = 'Y') THEN
2488: asn_debug.put_line('Validating Item');
2489: asn_debug.put_line(TO_CHAR(x_cascaded_table(n).item_id));
2490: END IF;
2491:
2492: /*
2485: item_id_record.error_record.error_message := NULL;
2486:
2487: IF (g_asn_debug = 'Y') THEN
2488: asn_debug.put_line('Validating Item');
2489: asn_debug.put_line(TO_CHAR(x_cascaded_table(n).item_id));
2490: END IF;
2491:
2492: /*
2493: ** If this is a one time item shipment and you've matched up based on a
2509: item_id_record.item_description := x_cascaded_table(n).item_description;
2510: item_id_record.error_record.error_status := 'S';
2511: item_id_record.error_record.error_message := NULL;
2512:
2513: IF (g_asn_debug = 'Y') THEN
2514: asn_debug.put_line('Validating Item Description ' || item_id_record.item_description);
2515: END IF;
2516:
2517: rcv_transactions_interface_sv1.validate_item_description(item_id_record);
2510: item_id_record.error_record.error_status := 'S';
2511: item_id_record.error_record.error_message := NULL;
2512:
2513: IF (g_asn_debug = 'Y') THEN
2514: asn_debug.put_line('Validating Item Description ' || item_id_record.item_description);
2515: END IF;
2516:
2517: rcv_transactions_interface_sv1.validate_item_description(item_id_record);
2518: x_cascaded_table(n).error_status := item_id_record.error_record.error_status;
2518: x_cascaded_table(n).error_status := item_id_record.error_record.error_status;
2519: x_cascaded_table(n).error_message := item_id_record.error_record.error_message;
2520: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'ITEM_DESCRIPTION');
2521:
2522: IF (g_asn_debug = 'Y') THEN
2523: asn_debug.put_line('Error status after validate item description ' || x_cascaded_table(n).error_status);
2524: END IF;
2525:
2526: -- item_revision
2519: x_cascaded_table(n).error_message := item_id_record.error_record.error_message;
2520: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status, 'ITEM_DESCRIPTION');
2521:
2522: IF (g_asn_debug = 'Y') THEN
2523: asn_debug.put_line('Error status after validate item description ' || x_cascaded_table(n).error_status);
2524: END IF;
2525:
2526: -- item_revision
2527: IF (x_cascaded_table(n).item_revision IS NOT NULL) THEN
2531: item_revision_record.item_id := x_cascaded_table(n).item_id;
2532: item_revision_record.error_record.error_status := 'S';
2533: item_revision_record.error_record.error_message := NULL;
2534:
2535: IF (g_asn_debug = 'Y') THEN
2536: asn_debug.put_line('Validating Item Revision');
2537: END IF;
2538:
2539: validate_item_revision(item_revision_record);
2532: item_revision_record.error_record.error_status := 'S';
2533: item_revision_record.error_record.error_message := NULL;
2534:
2535: IF (g_asn_debug = 'Y') THEN
2536: asn_debug.put_line('Validating Item Revision');
2537: END IF;
2538:
2539: validate_item_revision(item_revision_record);
2540: x_cascaded_table(n).error_status := item_revision_record.error_record.error_status;
2589: FROM org_organization_definitions
2590: WHERE organization_id = x_cascaded_table(n).to_organization_id ;
2591: EXCEPTION
2592: WHEN NO_DATA_FOUND THEN
2593: IF (g_asn_debug = 'Y') THEN
2594: asn_debug.put_line('Set of books id not defined');
2595: END IF;
2596: END;
2597:
2590: WHERE organization_id = x_cascaded_table(n).to_organization_id ;
2591: EXCEPTION
2592: WHEN NO_DATA_FOUND THEN
2593: IF (g_asn_debug = 'Y') THEN
2594: asn_debug.put_line('Set of books id not defined');
2595: END IF;
2596: END;
2597:
2598: BEGIN
2759: freight_carrier_record.po_header_id := x_cascaded_table(n).po_header_id;
2760: freight_carrier_record.error_record.error_status := 'S';
2761: freight_carrier_record.error_record.error_message := NULL;
2762:
2763: IF (g_asn_debug = 'Y') THEN
2764: asn_debug.put_line('Validating Freight Carrier');
2765: END IF;
2766:
2767: rcv_transactions_interface_sv1.validate_freight_carrier(freight_carrier_record);
2760: freight_carrier_record.error_record.error_status := 'S';
2761: freight_carrier_record.error_record.error_message := NULL;
2762:
2763: IF (g_asn_debug = 'Y') THEN
2764: asn_debug.put_line('Validating Freight Carrier');
2765: END IF;
2766:
2767: rcv_transactions_interface_sv1.validate_freight_carrier(freight_carrier_record);
2768: x_cascaded_table(n).error_status := freight_carrier_record.error_record.error_status;
2791: po_lookup_code_record.lookup_type := 'RCV DESTINATION TYPE';
2792: po_lookup_code_record.error_record.error_status := 'S';
2793: po_lookup_code_record.error_record.error_message := NULL;
2794:
2795: IF (g_asn_debug = 'Y') THEN
2796: asn_debug.put_line('Validating Destination Type Code');
2797: END IF;
2798:
2799: rcv_transactions_interface_sv1.validate_po_lookup_code(po_lookup_code_record);
2792: po_lookup_code_record.error_record.error_status := 'S';
2793: po_lookup_code_record.error_record.error_message := NULL;
2794:
2795: IF (g_asn_debug = 'Y') THEN
2796: asn_debug.put_line('Validating Destination Type Code');
2797: END IF;
2798:
2799: rcv_transactions_interface_sv1.validate_po_lookup_code(po_lookup_code_record);
2800: x_cascaded_table(n).error_status := po_lookup_code_record.error_record.error_status;
2824: employee_record.transaction_date := x_cascaded_table(n).transaction_date;
2825: employee_record.error_record.error_status := 'S';
2826: employee_record.error_record.error_message := NULL;
2827:
2828: IF (g_asn_debug = 'Y') THEN
2829: asn_debug.put_line('Validating Deliver to Person');
2830: END IF;
2831:
2832: rcv_transactions_interface_sv1.validate_employee(employee_record);
2825: employee_record.error_record.error_status := 'S';
2826: employee_record.error_record.error_message := NULL;
2827:
2828: IF (g_asn_debug = 'Y') THEN
2829: asn_debug.put_line('Validating Deliver to Person');
2830: END IF;
2831:
2832: rcv_transactions_interface_sv1.validate_employee(employee_record);
2833: x_cascaded_table(n).error_status := employee_record.error_record.error_status;
2846: IF (x_cascaded_table(n).error_status NOT IN('S', 'W')) THEN
2847: RETURN;
2848: END IF;
2849:
2850: IF (g_asn_debug = 'Y') THEN
2851: asn_debug.put_line('Validating tax_name: ' || x_cascaded_table(n).tax_name);
2852: END IF;
2853: END validate_tax_name;
2854:
2847: RETURN;
2848: END IF;
2849:
2850: IF (g_asn_debug = 'Y') THEN
2851: asn_debug.put_line('Validating tax_name: ' || x_cascaded_table(n).tax_name);
2852: END IF;
2853: END validate_tax_name;
2854:
2855: PROCEDURE validate_country_of_origin(
2861: IF (x_cascaded_table(n).error_status NOT IN('S', 'W')) THEN
2862: RETURN;
2863: END IF;
2864:
2865: IF (g_asn_debug = 'Y') THEN
2866: asn_debug.put_line('Validating country_of_origin_code: ' || x_cascaded_table(n).country_of_origin_code);
2867: END IF;
2868:
2869: IF (x_cascaded_table(n).country_of_origin_code IS NOT NULL) THEN
2862: RETURN;
2863: END IF;
2864:
2865: IF (g_asn_debug = 'Y') THEN
2866: asn_debug.put_line('Validating country_of_origin_code: ' || x_cascaded_table(n).country_of_origin_code);
2867: END IF;
2868:
2869: IF (x_cascaded_table(n).country_of_origin_code IS NOT NULL) THEN
2870: country_of_origin_record.country_of_origin_code := x_cascaded_table(n).country_of_origin_code;
2870: country_of_origin_record.country_of_origin_code := x_cascaded_table(n).country_of_origin_code;
2871: country_of_origin_record.error_record.error_status := 'S';
2872: country_of_origin_record.error_record.error_message := NULL;
2873:
2874: IF (g_asn_debug = 'Y') THEN
2875: asn_debug.put_line('Validating Country of Origin Code');
2876: END IF;
2877:
2878: rcv_transactions_interface_sv1.validate_country_of_origin(country_of_origin_record);
2871: country_of_origin_record.error_record.error_status := 'S';
2872: country_of_origin_record.error_record.error_message := NULL;
2873:
2874: IF (g_asn_debug = 'Y') THEN
2875: asn_debug.put_line('Validating Country of Origin Code');
2876: END IF;
2877:
2878: rcv_transactions_interface_sv1.validate_country_of_origin(country_of_origin_record);
2879: x_cascaded_table(n).error_status := country_of_origin_record.error_record.error_status;
2982: RAISE rcv_error_pkg.e_fatal_error;
2983: END IF;
2984:
2985: /* bug 608353, do not support lot and serial control if DELIVER is used */
2986: IF (g_asn_debug = 'Y') THEN
2987: asn_debug.put_line('Validating Item: ' || x_auto_transact_code);
2988: asn_debug.put_line('Validating Item: ' || x_item_id_record.use_mtl_lot);
2989: asn_debug.put_line('Validating Item: ' || x_item_id_record.use_mtl_serial);
2990: END IF;
2983: END IF;
2984:
2985: /* bug 608353, do not support lot and serial control if DELIVER is used */
2986: IF (g_asn_debug = 'Y') THEN
2987: asn_debug.put_line('Validating Item: ' || x_auto_transact_code);
2988: asn_debug.put_line('Validating Item: ' || x_item_id_record.use_mtl_lot);
2989: asn_debug.put_line('Validating Item: ' || x_item_id_record.use_mtl_serial);
2990: END IF;
2991: /* We now support Lot-serial Transactions. Hence removed the code that
2984:
2985: /* bug 608353, do not support lot and serial control if DELIVER is used */
2986: IF (g_asn_debug = 'Y') THEN
2987: asn_debug.put_line('Validating Item: ' || x_auto_transact_code);
2988: asn_debug.put_line('Validating Item: ' || x_item_id_record.use_mtl_lot);
2989: asn_debug.put_line('Validating Item: ' || x_item_id_record.use_mtl_serial);
2990: END IF;
2991: /* We now support Lot-serial Transactions. Hence removed the code that
2992: * sets error message to RCV_LOT_SERIAL_NOT_SUPPORTED.
2985: /* bug 608353, do not support lot and serial control if DELIVER is used */
2986: IF (g_asn_debug = 'Y') THEN
2987: asn_debug.put_line('Validating Item: ' || x_auto_transact_code);
2988: asn_debug.put_line('Validating Item: ' || x_item_id_record.use_mtl_lot);
2989: asn_debug.put_line('Validating Item: ' || x_item_id_record.use_mtl_serial);
2990: END IF;
2991: /* We now support Lot-serial Transactions. Hence removed the code that
2992: * sets error message to RCV_LOT_SERIAL_NOT_SUPPORTED.
2993: */
3056: IF (x_inventory_item <> 0) THEN
3057: RETURN;
3058: END IF;
3059:
3060: IF (g_asn_debug = 'Y') THEN
3061: asn_debug.put_line('Item is not under revision control');
3062: END IF;
3063:
3064: rcv_error_pkg.set_error_message('RCV_ITEM_REV_NOT_ALLOWED');
3057: RETURN;
3058: END IF;
3059:
3060: IF (g_asn_debug = 'Y') THEN
3061: asn_debug.put_line('Item is not under revision control');
3062: END IF;
3063:
3064: rcv_error_pkg.set_error_message('RCV_ITEM_REV_NOT_ALLOWED');
3065: RAISE rcv_error_pkg.e_fatal_error;
3066: END IF;
3067:
3068: -- Check whether the revision number exists
3069:
3070: IF (g_asn_debug = 'Y') THEN
3071: asn_debug.put_line('Revision number : ' || x_item_revision_record.item_revision);
3072: END IF;
3073:
3074: SELECT NVL(MAX(inventory_item_id), 0)
3067:
3068: -- Check whether the revision number exists
3069:
3070: IF (g_asn_debug = 'Y') THEN
3071: asn_debug.put_line('Revision number : ' || x_item_revision_record.item_revision);
3072: END IF;
3073:
3074: SELECT NVL(MAX(inventory_item_id), 0)
3075: INTO x_inventory_item
3134: IF (x_cascaded_table(n).error_status NOT IN('S', 'W')) THEN
3135: RETURN;
3136: END IF;
3137:
3138: IF (g_asn_debug = 'Y') THEN
3139: asn_debug.put_line('Validating ref integrity');
3140: END IF;
3141:
3142: -- check customer item number
3135: RETURN;
3136: END IF;
3137:
3138: IF (g_asn_debug = 'Y') THEN
3139: asn_debug.put_line('Validating ref integrity');
3140: END IF;
3141:
3142: -- check customer item number
3143: IF (x_cascaded_table(n).customer_item_num IS NOT NULL) THEN
3224: END IF;
3225:
3226: x_new_conversion := 0;
3227:
3228: IF (g_asn_debug = 'Y') THEN
3229: asn_debug.put_line(TO_CHAR(x_uom_record.quantity_shipped));
3230: asn_debug.put_line(x_uom_record.unit_of_measure);
3231: asn_debug.put_line(TO_CHAR(x_uom_record.item_id));
3232: asn_debug.put_line(x_primary_unit_of_measure);
3225:
3226: x_new_conversion := 0;
3227:
3228: IF (g_asn_debug = 'Y') THEN
3229: asn_debug.put_line(TO_CHAR(x_uom_record.quantity_shipped));
3230: asn_debug.put_line(x_uom_record.unit_of_measure);
3231: asn_debug.put_line(TO_CHAR(x_uom_record.item_id));
3232: asn_debug.put_line(x_primary_unit_of_measure);
3233: asn_debug.put_line(x_uom_record.primary_unit_of_measure);
3226: x_new_conversion := 0;
3227:
3228: IF (g_asn_debug = 'Y') THEN
3229: asn_debug.put_line(TO_CHAR(x_uom_record.quantity_shipped));
3230: asn_debug.put_line(x_uom_record.unit_of_measure);
3231: asn_debug.put_line(TO_CHAR(x_uom_record.item_id));
3232: asn_debug.put_line(x_primary_unit_of_measure);
3233: asn_debug.put_line(x_uom_record.primary_unit_of_measure);
3234: END IF;
3227:
3228: IF (g_asn_debug = 'Y') THEN
3229: asn_debug.put_line(TO_CHAR(x_uom_record.quantity_shipped));
3230: asn_debug.put_line(x_uom_record.unit_of_measure);
3231: asn_debug.put_line(TO_CHAR(x_uom_record.item_id));
3232: asn_debug.put_line(x_primary_unit_of_measure);
3233: asn_debug.put_line(x_uom_record.primary_unit_of_measure);
3234: END IF;
3235:
3228: IF (g_asn_debug = 'Y') THEN
3229: asn_debug.put_line(TO_CHAR(x_uom_record.quantity_shipped));
3230: asn_debug.put_line(x_uom_record.unit_of_measure);
3231: asn_debug.put_line(TO_CHAR(x_uom_record.item_id));
3232: asn_debug.put_line(x_primary_unit_of_measure);
3233: asn_debug.put_line(x_uom_record.primary_unit_of_measure);
3234: END IF;
3235:
3236: po_uom_s.uom_convert(x_uom_record.quantity_shipped,
3229: asn_debug.put_line(TO_CHAR(x_uom_record.quantity_shipped));
3230: asn_debug.put_line(x_uom_record.unit_of_measure);
3231: asn_debug.put_line(TO_CHAR(x_uom_record.item_id));
3232: asn_debug.put_line(x_primary_unit_of_measure);
3233: asn_debug.put_line(x_uom_record.primary_unit_of_measure);
3234: END IF;
3235:
3236: po_uom_s.uom_convert(x_uom_record.quantity_shipped,
3237: x_uom_record.unit_of_measure,
3315: end if
3316: else
3317: item should not have any revisions which we will validate in the validation phase */
3318: IF x_revision_control_flag = 'Y' THEN
3319: IF (g_asn_debug = 'Y') THEN
3320: asn_debug.put_line('Item is under revision control');
3321: END IF;
3322:
3323: IF x_item_revision_record.item_revision IS NULL THEN -- pick up revision from source document
3316: else
3317: item should not have any revisions which we will validate in the validation phase */
3318: IF x_revision_control_flag = 'Y' THEN
3319: IF (g_asn_debug = 'Y') THEN
3320: asn_debug.put_line('Item is under revision control');
3321: END IF;
3322:
3323: IF x_item_revision_record.item_revision IS NULL THEN -- pick up revision from source document
3324: IF (g_asn_debug = 'Y') THEN
3320: asn_debug.put_line('Item is under revision control');
3321: END IF;
3322:
3323: IF x_item_revision_record.item_revision IS NULL THEN -- pick up revision from source document
3324: IF (g_asn_debug = 'Y') THEN
3325: asn_debug.put_line('Picking up from source document');
3326: END IF;
3327:
3328: SELECT item_revision
3321: END IF;
3322:
3323: IF x_item_revision_record.item_revision IS NULL THEN -- pick up revision from source document
3324: IF (g_asn_debug = 'Y') THEN
3325: asn_debug.put_line('Picking up from source document');
3326: END IF;
3327:
3328: SELECT item_revision
3329: INTO x_item_revision_record.item_revision
3331: WHERE oe_order_lines_all.line_id = x_item_revision_record.po_line_id;
3332: END IF;
3333:
3334: IF x_item_revision_record.item_revision IS NULL THEN
3335: IF (g_asn_debug = 'Y') THEN
3336: asn_debug.put_line('Picking up latest implementation since source doc is null');
3337: END IF;
3338:
3339: po_items_sv2.get_latest_item_rev(x_item_revision_record.item_id,
3332: END IF;
3333:
3334: IF x_item_revision_record.item_revision IS NULL THEN
3335: IF (g_asn_debug = 'Y') THEN
3336: asn_debug.put_line('Picking up latest implementation since source doc is null');
3337: END IF;
3338:
3339: po_items_sv2.get_latest_item_rev(x_item_revision_record.item_id,
3340: x_item_revision_record.to_organization_id,
3344: END IF;
3345: END IF;
3346: EXCEPTION
3347: WHEN OTHERS THEN
3348: IF (g_asn_debug = 'Y') THEN
3349: asn_debug.put_line('Exception in procedure default_item_revision');
3350: END IF;
3351: END default_item_revision;
3352:
3345: END IF;
3346: EXCEPTION
3347: WHEN OTHERS THEN
3348: IF (g_asn_debug = 'Y') THEN
3349: asn_debug.put_line('Exception in procedure default_item_revision');
3350: END IF;
3351: END default_item_revision;
3352:
3353: /*===========================================================================
3365: x_sysdate DATE := SYSDATE;
3366: high_range_date DATE;
3367: low_range_date DATE;
3368: BEGIN
3369: IF (g_asn_debug = 'Y') THEN
3370: asn_debug.put_line('Check date tolerance');
3371: END IF;
3372:
3373: IF (expected_receipt_date IS NOT NULL) THEN
3366: high_range_date DATE;
3367: low_range_date DATE;
3368: BEGIN
3369: IF (g_asn_debug = 'Y') THEN
3370: asn_debug.put_line('Check date tolerance');
3371: END IF;
3372:
3373: IF (expected_receipt_date IS NOT NULL) THEN
3374: IF (promised_date IS NOT NULL) THEN
3393: receipt_days_exception_code := 'NONE';
3394: END IF;
3395:
3396: IF receipt_days_exception_code IS NULL THEN
3397: IF (g_asn_debug = 'Y') THEN
3398: asn_debug.put_line('In null days exception code');
3399: END IF;
3400:
3401: receipt_days_exception_code := 'NONE';
3394: END IF;
3395:
3396: IF receipt_days_exception_code IS NULL THEN
3397: IF (g_asn_debug = 'Y') THEN
3398: asn_debug.put_line('In null days exception code');
3399: END IF;
3400:
3401: receipt_days_exception_code := 'NONE';
3402: END IF;
3424:
3425: RETURN(correct_qty);
3426: EXCEPTION
3427: WHEN OTHERS THEN
3428: IF (g_asn_debug = 'Y') THEN
3429: asn_debug.put_line('Could not convert between UOMs');
3430: asn_debug.put_line('Will return 0');
3431: END IF;
3432:
3425: RETURN(correct_qty);
3426: EXCEPTION
3427: WHEN OTHERS THEN
3428: IF (g_asn_debug = 'Y') THEN
3429: asn_debug.put_line('Could not convert between UOMs');
3430: asn_debug.put_line('Will return 0');
3431: END IF;
3432:
3433: correct_qty := 0;
3426: EXCEPTION
3427: WHEN OTHERS THEN
3428: IF (g_asn_debug = 'Y') THEN
3429: asn_debug.put_line('Could not convert between UOMs');
3430: asn_debug.put_line('Will return 0');
3431: END IF;
3432:
3433: correct_qty := 0;
3434: RETURN(correct_qty);
3467: PROCEDURE get_locator_id(
3468: x_locator_id_record IN OUT NOCOPY rcv_shipment_line_sv.locator_id_record_type
3469: ) IS
3470: BEGIN
3471: IF (g_asn_debug = 'Y') THEN
3472: asn_debug.put_line('inside get_locator_id');
3473: END IF;
3474:
3475: IF (x_locator_id_record.subinventory IS NULL) THEN
3468: x_locator_id_record IN OUT NOCOPY rcv_shipment_line_sv.locator_id_record_type
3469: ) IS
3470: BEGIN
3471: IF (g_asn_debug = 'Y') THEN
3472: asn_debug.put_line('inside get_locator_id');
3473: END IF;
3474:
3475: IF (x_locator_id_record.subinventory IS NULL) THEN
3476: SELECT MAX(ml.inventory_location_id)