[Home] [Help]
121: asn_debug.put_line('l_lot_count ' || l_lot_count);
122: END IF;
123:
124: /* We need to insert into rcv_lots_supply and
125: * rcv_serials_supply table only when we come through ROI
126: * or when we come through desktop and have lpn info.
127: * We insert lpn_id in rcv_supply. So return if there is
128: * a value and validation_flag is N.
129: */
418: WHERE rti.interface_transaction_id = p_interface_transaction_id;
419: END insert_rcv_lots_supply;
420:
421: --
422: PROCEDURE insert_rcv_serials_supply(
423: p_api_version IN NUMBER,
424: p_init_msg_list IN VARCHAR2,
425: x_return_status OUT NOCOPY VARCHAR2,
426: p_interface_transaction_id IN NUMBER,
488: asn_debug.put_line('l_serial_count ' || l_serial_count);
489: END IF;
490:
491: /* We need to insert into rcv_lots_supply and
492: * rcv_serials_supply table only when we come through ROI
493: * or when we come through desktop and have lpn info.
494: * We insert lpn_id in rcv_supply. So return if there is
495: * a value and validation_flag is N.
496: */
613: * into multiple shipment_lines (when lpn has 2 lots for eg).
614: * So instead of using shipment_line_id to delete use
615: * shipment_header_id.
616: */
617: DELETE FROM rcv_serials_supply rss
618: WHERE supply_type_code = 'SHIPMENT'
619: AND ( l_select_serials.lot_number IS NULL
620: OR NVL(lot_num, l_select_serials.lot_number) = l_select_serials.lot_number)
621: AND (serial_num BETWEEN(l_serial_prefix || LPAD(TO_CHAR(l_from_serial_number),
635:
636: CLOSE select_serials;
637:
638: IF (g_asn_debug = 'Y') THEN
639: asn_debug.put_line('Exit insert_rcv_serials_supply ');
640: END IF;
641: EXCEPTION
642: WHEN NO_DATA_FOUND THEN
643: IF (g_asn_debug = 'Y') THEN
640: END IF;
641: EXCEPTION
642: WHEN NO_DATA_FOUND THEN
643: IF (g_asn_debug = 'Y') THEN
644: asn_debug.put_line('no_data_found insert_rcv_serials_supply ');
645: END IF;
646:
647: x_return_status := fnd_api.g_ret_sts_error;
648:
677: FROM rcv_transactions_interface rti
678: WHERE rti.interface_transaction_id = p_interface_transaction_id;
679: WHEN OTHERS THEN
680: IF (g_asn_debug = 'Y') THEN
681: asn_debug.put_line('others insert_rcv_serials_supply ');
682: END IF;
683:
684: x_return_status := fnd_api.g_ret_sts_unexp_error;
685:
712: rti.program_id,
713: rti.program_update_date
714: FROM rcv_transactions_interface rti
715: WHERE rti.interface_transaction_id = p_interface_transaction_id;
716: END insert_rcv_serials_supply;
717:
718: PROCEDURE split_serial_number(
719: p_sequence IN VARCHAR2,
720: x_prefix OUT NOCOPY VARCHAR2,
906: asn_debug.put_line('l_lot_count ' || l_lot_count);
907: END IF;
908:
909: /* We need to insert into rcv_lots_supply and
910: * rcv_serials_supply table only when we come through ROI
911: * or when we come through desktop and have lpn info.
912: * We insert lpn_id in rcv_supply. So return if there is
913: * a value and validation_flag is N.
914: */
1376: FROM rcv_transactions_interface rti
1377: WHERE rti.interface_transaction_id = p_interface_transaction_id;
1378: END update_rcv_lots_supply;
1379:
1380: PROCEDURE update_rcv_serials_supply(
1381: p_api_version IN NUMBER,
1382: p_init_msg_list IN VARCHAR2,
1383: x_return_status OUT NOCOPY VARCHAR2,
1384: p_interface_transaction_id IN NUMBER,
1421: l_shipment_header_id rcv_shipment_headers.shipment_header_id%TYPE;
1422: l_item_id rcv_transactions_interface.item_id%TYPE;
1423: BEGIN
1424: IF (g_asn_debug = 'Y') THEN
1425: asn_debug.put_line('Enter update_rcv_serials_supply ');
1426: asn_debug.put_line('p_interface_transaction_id ' || p_interface_transaction_id);
1427: asn_debug.put_line('p_transaction_type ' || p_transaction_type);
1428: asn_debug.put_line('p_shipment_line_id ' || p_shipment_line_id);
1429: asn_debug.put_line('p_source_type_code ' || p_source_type_code);
1452: asn_debug.put_line('l_Serial_count ' || l_serial_count);
1453: END IF;
1454:
1455: /* We need to insert into rcv_lots_supply and
1456: * rcv_serials_supply table only when we come through ROI
1457: * or when we come through desktop and have lpn info.
1458: * We insert lpn_id in rcv_supply. So return if there is
1459: * a value and validation_flag is N.
1460: */
1482:
1483: /* Correction_type is positive when we need to insert new rows and
1484: * and will be negative when we need to delete the existing rows.
1485: * We need to insert new rows only when we already have rows
1486: * in rcv_serials_supply for the corresponding row in rcv_supply.
1487: */
1488: IF (p_correction_type = 'POSITIVE') THEN --{
1489: SELECT COUNT(*)
1490: INTO l_count
1487: */
1488: IF (p_correction_type = 'POSITIVE') THEN --{
1489: SELECT COUNT(*)
1490: INTO l_count
1491: FROM rcv_serials_supply
1492: WHERE transaction_id = p_parent_supply_id
1493: AND supply_type_code = 'RECEIVING';
1494:
1495: IF (l_count > 0) THEN
1553: asn_debug.put_line('l_from_serial_number ' || l_from_serial_number);
1554: asn_debug.put_line('l_to_serial_number ' || l_to_serial_number);
1555: END IF;
1556:
1557: DELETE FROM rcv_serials_supply
1558: WHERE transaction_id = p_parent_supply_id
1559: AND supply_type_code = 'RECEIVING'
1560: AND ( l_select_serials.lot_number IS NULL
1561: OR NVL(lot_num, l_select_serials.lot_number) = l_select_serials.lot_number)
1632: * into multiple shipment_lines (when lpn has 2 lots for eg).
1633: * So instead of using shipment_line_id to delete use
1634: * shipment_header_id.
1635: */
1636: DELETE FROM rcv_serials_supply rss
1637: WHERE supply_type_code = 'SHIPMENT'
1638: AND ( l_select_serials.lot_number IS NULL
1639: OR NVL(lot_num, l_select_serials.lot_number) = l_select_serials.lot_number)
1640: AND (serial_num BETWEEN(l_serial_prefix || LPAD(TO_CHAR(l_from_serial_number),
1659: CLOSE select_serials;
1660: EXCEPTION
1661: WHEN NO_DATA_FOUND THEN
1662: IF (g_asn_debug = 'Y') THEN
1663: asn_debug.put_line('no_data_found update_rcv_serials_supply');
1664: END IF;
1665:
1666: x_return_status := fnd_api.g_ret_sts_error;
1667:
1696: FROM rcv_transactions_interface rti
1697: WHERE rti.interface_transaction_id = p_interface_transaction_id;
1698: WHEN OTHERS THEN
1699: IF (g_asn_debug = 'Y') THEN
1700: asn_debug.put_line('others update_rcv_serials_supply');
1701: END IF;
1702:
1703: x_return_status := fnd_api.g_ret_sts_unexp_error;
1704:
1731: rti.program_id,
1732: rti.program_update_date
1733: FROM rcv_transactions_interface rti
1734: WHERE rti.interface_transaction_id = p_interface_transaction_id;
1735: END update_rcv_serials_supply;
1736:
1737: PROCEDURE insert_lot_supply(
1738: p_interface_transaction_id IN NUMBER,
1739: p_supply_type_code IN VARCHAR2,
1778: asn_debug.put_line('l_lot_count ' || l_lot_count);
1779: END IF;
1780:
1781: /* We need to insert into rcv_lots_supply and
1782: * rcv_serials_supply table only when we come through ROI
1783: * or when we come through desktop and have lpn info.
1784: * We insert lpn_id in rcv_supply. So return if there is
1785: * a value and validation_flag is N.
1786: */
1922: x_return_status OUT NOCOPY VARCHAR2
1923: ) IS
1924: CURSOR c IS
1925: SELECT rss.ROWID
1926: FROM rcv_serials_supply rss
1927: WHERE rss.transaction_id = p_supply_source_id;
1928:
1929: l_rowid VARCHAR2(255);
1930: l_serial_count NUMBER;
1961: asn_debug.put_line('l_serial_count ' || l_serial_count);
1962: END IF;
1963:
1964: /* We need to insert into rcv_lots_supply and
1965: * rcv_serials_supply table only when we come through ROI
1966: * or when we come through desktop and have lpn info.
1967: * We insert lpn_id in rcv_supply. So return if there is
1968: * a value and validation_flag is N.
1969: */
1990: /* We can only use rcv_transactions but in cases of direct deliver
1991: * there will be two rows in rt with same interface_txn_id. Hence
1992: * use rcv_supply and this way we will only use the receiving row.
1993: */
1994: INSERT INTO rcv_serials_supply
1995: (supply_type_code,
1996: shipment_line_id,
1997: transaction_id,
1998: lot_num,
2027: FROM rcv_supply rs
2028: WHERE rs.supply_source_id = p_supply_source_id;
2029:
2030: IF (g_asn_debug = 'Y') THEN
2031: asn_debug.put_line(' After insert into rcv_serials_supply');
2032: END IF;
2033:
2034: OPEN c;
2035: FETCH c INTO l_rowid;