[Home] [Help]
31: l_rowid VARCHAR2(255);
32: l_transaction_id rcv_transactions.transaction_id%TYPE;
33: l_lot_count NUMBER;
34: l_organization_id NUMBER;
35: l_lpn_id rcv_supply.lpn_id%TYPE;
36: l_validation_flag rcv_transactions_interface.validation_flag%TYPE;
37: l_shipment_header_id rcv_shipment_headers.shipment_header_id%TYPE;
38:
39: CURSOR lot_numbers(
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: */
130: SELECT NVL(validation_flag, 'N')
131: INTO l_validation_flag
133: WHERE interface_transaction_id = p_interface_transaction_id;
134:
135: SELECT NVL(lpn_id, -999)
136: INTO l_lpn_id
137: FROM rcv_supply
138: WHERE supply_source_id = p_supply_source_id;
139:
140: IF ( l_validation_flag = 'N'
141: AND l_lpn_id = -999) THEN
453: l_serial_suffix_length NUMBER;
454: l_delete_shipment_supply VARCHAR2(1) := 'N';
455: l_transaction_id rcv_transactions.transaction_id%TYPE;
456: l_serial_count NUMBER;
457: l_lpn_id rcv_supply.lpn_id%TYPE;
458: l_validation_flag rcv_transactions_interface.validation_flag%TYPE;
459: l_shipment_header_id rcv_shipment_headers.shipment_header_id%TYPE;
460: l_item_id rcv_transactions_interface.item_id%TYPE;
461: BEGIN
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: */
497: SELECT NVL(validation_flag, 'N')
498: INTO l_validation_flag
500: WHERE interface_transaction_id = p_interface_transaction_id;
501:
502: SELECT NVL(lpn_id, -999)
503: INTO l_lpn_id
504: FROM rcv_supply
505: WHERE supply_source_id = p_supply_source_id;
506:
507: IF ( l_validation_flag = 'N'
508: AND l_lpn_id = -999) THEN
813: l_organization_id NUMBER;
814: /* INVCONV*/
815: /* l_sublot_num VARCHAR2(32); */
816: /*end , INVCONV*/
817: l_lpn_id rcv_supply.lpn_id%TYPE;
818: l_validation_flag rcv_transactions_interface.validation_flag%TYPE;
819: l_shipment_header_id rcv_shipment_headers.shipment_header_id%TYPE;
820:
821: CURSOR supply_quantity(
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: */
915: SELECT NVL(validation_flag, 'N')
916: INTO l_validation_flag
918: WHERE interface_transaction_id = p_interface_transaction_id;
919:
920: SELECT NVL(lpn_id, -999)
921: INTO l_lpn_id
922: FROM rcv_supply
923: WHERE supply_source_id = p_parent_supply_id;
924:
925: IF ( l_validation_flag = 'N'
926: AND l_lpn_id = -999) THEN
931: asn_debug.put_line('l_validation_flag ' || l_validation_flag);
932: asn_debug.put_line('l_lpn_id ' || l_lpn_id);
933: END IF;
934:
935: /* When we update rcv_supply, we call this procedure and set
936: * the p_correction_type depending upon whether we need to add
937: * or subtract supply from rcv_lots_supply.
938: */
939: IF (p_correction_type = 'POSITIVE') THEN
943: END IF;
944:
945: /* We need to insert or update rcv_lot_supply only when there is
946: * already a row existing in rcv_lots_supply for a corresponding
947: * row in rcv_supply. If not dont do anything.
948: */
949: SELECT COUNT(*)
950: INTO l_count
951: FROM rcv_lots_supply
1415: l_range_numbers NUMBER;
1416: l_serial_count NUMBER;
1417: l_update_shipment_supply VARCHAR2(1) := 'N';
1418: l_parent_trans_type rcv_transactions.transaction_type%TYPE;
1419: l_lpn_id rcv_supply.lpn_id%TYPE;
1420: l_validation_flag rcv_transactions_interface.validation_flag%TYPE;
1421: l_shipment_header_id rcv_shipment_headers.shipment_header_id%TYPE;
1422: l_item_id rcv_transactions_interface.item_id%TYPE;
1423: BEGIN
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: */
1461: SELECT NVL(validation_flag, 'N')
1462: INTO l_validation_flag
1464: WHERE interface_transaction_id = p_interface_transaction_id;
1465:
1466: SELECT NVL(lpn_id, -999)
1467: INTO l_lpn_id
1468: FROM rcv_supply
1469: WHERE supply_source_id = p_parent_supply_id;
1470:
1471: IF ( l_validation_flag = 'N'
1472: AND l_lpn_id = -999) THEN
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
1746: WHERE rls.transaction_id = p_supply_source_id;
1747:
1748: l_rowid VARCHAR2(255);
1749: l_lot_count NUMBER;
1750: l_lpn_id rcv_supply.lpn_id%TYPE;
1751: l_validation_flag rcv_transactions_interface.validation_flag%TYPE;
1752: BEGIN
1753: IF (g_asn_debug = 'Y') THEN
1754: asn_debug.put_line('Enter insert_lots_supply');
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: */
1787: SELECT NVL(validation_flag, 'N')
1788: INTO l_validation_flag
1790: WHERE interface_transaction_id = p_interface_transaction_id;
1791:
1792: SELECT NVL(lpn_id, -999)
1793: INTO l_lpn_id
1794: FROM rcv_supply
1795: WHERE supply_source_id = p_supply_source_id;
1796:
1797: IF ( l_validation_flag = 'N'
1798: AND l_lpn_id = -999) THEN
1852: /*INVCONV*/
1853: /* mtltview.sublot_num, */
1854: /*end , INVCONV*/
1855: mtltview.reason_code
1856: FROM rcv_supply rs,
1857: (SELECT SUM(mtlt.transaction_quantity) qty,
1858: SUM(mtlt.primary_quantity) primary_qty,
1859: SUM(mtlt.secondary_quantity) secondary_qty,
1860: mtlt.lot_number,
1927: WHERE rss.transaction_id = p_supply_source_id;
1928:
1929: l_rowid VARCHAR2(255);
1930: l_serial_count NUMBER;
1931: l_lpn_id rcv_supply.lpn_id%TYPE;
1932: l_validation_flag rcv_transactions_interface.validation_flag%TYPE;
1933: BEGIN
1934: IF (g_asn_debug = 'Y') THEN
1935: asn_debug.put_line('Enter insert_serial_supply ');
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: */
1970: SELECT NVL(validation_flag, 'N')
1971: INTO l_validation_flag
1973: WHERE interface_transaction_id = p_interface_transaction_id;
1974:
1975: SELECT NVL(lpn_id, -999)
1976: INTO l_lpn_id
1977: FROM rcv_supply
1978: WHERE supply_source_id = p_supply_source_id;
1979:
1980: IF ( l_validation_flag = 'N'
1981: AND l_lpn_id = -999) THEN
1988: END IF;
1989:
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,
2023: rs.request_id,
2024: rs.program_application_id,
2025: rs.program_id,
2026: rs.program_update_date
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');
2070: p_negate_qty IN VARCHAR2,
2071: x_return_status OUT NOCOPY VARCHAR2
2072: ) IS
2073: l_lot_count NUMBER;
2074: l_lpn_id rcv_supply.lpn_id%TYPE;
2075: l_validation_flag rcv_transactions_interface.validation_flag%TYPE;
2076: BEGIN
2077: IF (g_asn_debug = 'Y') THEN
2078: asn_debug.put_line(' enter insert_lot_transactions');