[Home] [Help]
122: asn_debug.put_line('l_lot_count ' || l_lot_count);
123: END IF;
124:
125: /* We need to insert into rcv_lots_supply and
126: * rcv_serials_supply table only when we come through ROI
127: * or when we come through desktop and have lpn info.
128: * We insert lpn_id in rcv_supply. So return if there is
129: * a value and validation_flag is N.
130: */
431: WHERE rti.interface_transaction_id = p_interface_transaction_id;
432: END insert_rcv_lots_supply;
433:
434: --
435: PROCEDURE insert_rcv_serials_supply(
436: p_api_version IN NUMBER,
437: p_init_msg_list IN VARCHAR2,
438: x_return_status OUT NOCOPY VARCHAR2,
439: p_interface_transaction_id IN NUMBER,
501: asn_debug.put_line('l_serial_count ' || l_serial_count);
502: END IF;
503:
504: /* We need to insert into rcv_lots_supply and
505: * rcv_serials_supply table only when we come through ROI
506: * or when we come through desktop and have lpn info.
507: * We insert lpn_id in rcv_supply. So return if there is
508: * a value and validation_flag is N.
509: */
626: * into multiple shipment_lines (when lpn has 2 lots for eg).
627: * So instead of using shipment_line_id to delete use
628: * shipment_header_id.
629: */
630: DELETE FROM rcv_serials_supply rss
631: WHERE supply_type_code = 'SHIPMENT'
632: AND ( l_select_serials.lot_number IS NULL
633: OR NVL(lot_num, l_select_serials.lot_number) = l_select_serials.lot_number)
634: AND (serial_num BETWEEN(l_serial_prefix || LPAD(TO_CHAR(l_from_serial_number),
648:
649: CLOSE select_serials;
650:
651: IF (g_asn_debug = 'Y') THEN
652: asn_debug.put_line('Exit insert_rcv_serials_supply ');
653: END IF;
654: EXCEPTION
655: WHEN NO_DATA_FOUND THEN
656: IF (g_asn_debug = 'Y') THEN
653: END IF;
654: EXCEPTION
655: WHEN NO_DATA_FOUND THEN
656: IF (g_asn_debug = 'Y') THEN
657: asn_debug.put_line('no_data_found insert_rcv_serials_supply ');
658: END IF;
659:
660: x_return_status := fnd_api.g_ret_sts_error;
661:
690: FROM rcv_transactions_interface rti
691: WHERE rti.interface_transaction_id = p_interface_transaction_id;
692: WHEN OTHERS THEN
693: IF (g_asn_debug = 'Y') THEN
694: asn_debug.put_line('others insert_rcv_serials_supply ');
695: END IF;
696:
697: x_return_status := fnd_api.g_ret_sts_unexp_error;
698:
725: rti.program_id,
726: rti.program_update_date
727: FROM rcv_transactions_interface rti
728: WHERE rti.interface_transaction_id = p_interface_transaction_id;
729: END insert_rcv_serials_supply;
730:
731: PROCEDURE split_serial_number(
732: p_sequence IN VARCHAR2,
733: x_prefix OUT NOCOPY VARCHAR2,
912: asn_debug.put_line('l_lot_count ' || l_lot_count);
913: END IF;
914:
915: /* We need to insert into rcv_lots_supply and
916: * rcv_serials_supply table only when we come through ROI
917: * or when we come through desktop and have lpn info.
918: * We insert lpn_id in rcv_supply. So return if there is
919: * a value and validation_flag is N.
920: */
1389: FROM rcv_transactions_interface rti
1390: WHERE rti.interface_transaction_id = p_interface_transaction_id;
1391: END update_rcv_lots_supply;
1392:
1393: PROCEDURE update_rcv_serials_supply(
1394: p_api_version IN NUMBER,
1395: p_init_msg_list IN VARCHAR2,
1396: x_return_status OUT NOCOPY VARCHAR2,
1397: p_interface_transaction_id IN NUMBER,
1434: l_shipment_header_id rcv_shipment_headers.shipment_header_id%TYPE;
1435: l_item_id rcv_transactions_interface.item_id%TYPE;
1436: BEGIN
1437: IF (g_asn_debug = 'Y') THEN
1438: asn_debug.put_line('Enter update_rcv_serials_supply ');
1439: asn_debug.put_line('p_interface_transaction_id ' || p_interface_transaction_id);
1440: asn_debug.put_line('p_transaction_type ' || p_transaction_type);
1441: asn_debug.put_line('p_shipment_line_id ' || p_shipment_line_id);
1442: asn_debug.put_line('p_source_type_code ' || p_source_type_code);
1465: asn_debug.put_line('l_Serial_count ' || l_serial_count);
1466: END IF;
1467:
1468: /* We need to insert into rcv_lots_supply and
1469: * rcv_serials_supply table only when we come through ROI
1470: * or when we come through desktop and have lpn info.
1471: * We insert lpn_id in rcv_supply. So return if there is
1472: * a value and validation_flag is N.
1473: */
1495:
1496: /* Correction_type is positive when we need to insert new rows and
1497: * and will be negative when we need to delete the existing rows.
1498: * We need to insert new rows only when we already have rows
1499: * in rcv_serials_supply for the corresponding row in rcv_supply.
1500: */
1501: IF (p_correction_type = 'POSITIVE') THEN --{
1502: SELECT COUNT(*)
1503: INTO l_count
1500: */
1501: IF (p_correction_type = 'POSITIVE') THEN --{
1502: SELECT COUNT(*)
1503: INTO l_count
1504: FROM rcv_serials_supply
1505: WHERE transaction_id = p_parent_supply_id
1506: AND supply_type_code = 'RECEIVING';
1507:
1508: IF (l_count > 0) THEN
1566: asn_debug.put_line('l_from_serial_number ' || l_from_serial_number);
1567: asn_debug.put_line('l_to_serial_number ' || l_to_serial_number);
1568: END IF;
1569:
1570: DELETE FROM rcv_serials_supply
1571: WHERE transaction_id = p_parent_supply_id
1572: AND supply_type_code = 'RECEIVING'
1573: AND ( l_select_serials.lot_number IS NULL
1574: OR NVL(lot_num, l_select_serials.lot_number) = l_select_serials.lot_number)
1645: * into multiple shipment_lines (when lpn has 2 lots for eg).
1646: * So instead of using shipment_line_id to delete use
1647: * shipment_header_id.
1648: */
1649: DELETE FROM rcv_serials_supply rss
1650: WHERE supply_type_code = 'SHIPMENT'
1651: AND ( l_select_serials.lot_number IS NULL
1652: OR NVL(lot_num, l_select_serials.lot_number) = l_select_serials.lot_number)
1653: AND (serial_num BETWEEN(l_serial_prefix || LPAD(TO_CHAR(l_from_serial_number),
1672: CLOSE select_serials;
1673: EXCEPTION
1674: WHEN NO_DATA_FOUND THEN
1675: IF (g_asn_debug = 'Y') THEN
1676: asn_debug.put_line('no_data_found update_rcv_serials_supply');
1677: END IF;
1678:
1679: x_return_status := fnd_api.g_ret_sts_error;
1680:
1709: FROM rcv_transactions_interface rti
1710: WHERE rti.interface_transaction_id = p_interface_transaction_id;
1711: WHEN OTHERS THEN
1712: IF (g_asn_debug = 'Y') THEN
1713: asn_debug.put_line('others update_rcv_serials_supply');
1714: END IF;
1715:
1716: x_return_status := fnd_api.g_ret_sts_unexp_error;
1717:
1744: rti.program_id,
1745: rti.program_update_date
1746: FROM rcv_transactions_interface rti
1747: WHERE rti.interface_transaction_id = p_interface_transaction_id;
1748: END update_rcv_serials_supply;
1749:
1750: PROCEDURE insert_lot_supply(
1751: p_interface_transaction_id IN NUMBER,
1752: p_supply_type_code IN VARCHAR2,
1791: asn_debug.put_line('l_lot_count ' || l_lot_count);
1792: END IF;
1793:
1794: /* We need to insert into rcv_lots_supply and
1795: * rcv_serials_supply table only when we come through ROI
1796: * or when we come through desktop and have lpn info.
1797: * We insert lpn_id in rcv_supply. So return if there is
1798: * a value and validation_flag is N.
1799: */
1935: x_return_status OUT NOCOPY VARCHAR2
1936: ) IS
1937: CURSOR c IS
1938: SELECT rss.ROWID
1939: FROM rcv_serials_supply rss
1940: WHERE rss.transaction_id = p_supply_source_id;
1941:
1942: l_rowid VARCHAR2(255);
1943: l_serial_count NUMBER;
1974: asn_debug.put_line('l_serial_count ' || l_serial_count);
1975: END IF;
1976:
1977: /* We need to insert into rcv_lots_supply and
1978: * rcv_serials_supply table only when we come through ROI
1979: * or when we come through desktop and have lpn info.
1980: * We insert lpn_id in rcv_supply. So return if there is
1981: * a value and validation_flag is N.
1982: */
2003: /* We can only use rcv_transactions but in cases of direct deliver
2004: * there will be two rows in rt with same interface_txn_id. Hence
2005: * use rcv_supply and this way we will only use the receiving row.
2006: */
2007: INSERT INTO rcv_serials_supply
2008: (supply_type_code,
2009: shipment_line_id,
2010: transaction_id,
2011: lot_num,
2040: FROM rcv_supply rs
2041: WHERE rs.supply_source_id = p_supply_source_id;
2042:
2043: IF (g_asn_debug = 'Y') THEN
2044: asn_debug.put_line(' After insert into rcv_serials_supply');
2045: END IF;
2046:
2047: OPEN c;
2048: FETCH c INTO l_rowid;