[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:
38: CURSOR lot_numbers(
39: l_interface_id NUMBER
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: */
131: SELECT NVL(validation_flag, 'N')
132: INTO l_validation_flag
134: WHERE interface_transaction_id = p_interface_transaction_id;
135:
136: SELECT NVL(lpn_id, -999)
137: INTO l_lpn_id
138: FROM rcv_supply
139: WHERE supply_source_id = p_supply_source_id;
140:
141: IF ( l_validation_flag = 'N'
142: AND l_lpn_id = -999) THEN
466: l_serial_suffix_length NUMBER;
467: l_delete_shipment_supply VARCHAR2(1) := 'N';
468: l_transaction_id rcv_transactions.transaction_id%TYPE;
469: l_serial_count NUMBER;
470: l_lpn_id rcv_supply.lpn_id%TYPE;
471: l_validation_flag rcv_transactions_interface.validation_flag%TYPE;
472: l_shipment_header_id rcv_shipment_headers.shipment_header_id%TYPE;
473: l_item_id rcv_transactions_interface.item_id%TYPE;
474: BEGIN
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: */
510: SELECT NVL(validation_flag, 'N')
511: INTO l_validation_flag
513: WHERE interface_transaction_id = p_interface_transaction_id;
514:
515: SELECT NVL(lpn_id, -999)
516: INTO l_lpn_id
517: FROM rcv_supply
518: WHERE supply_source_id = p_supply_source_id;
519:
520: IF ( l_validation_flag = 'N'
521: AND l_lpn_id = -999) THEN
826: l_organization_id NUMBER;
827: /* INVCONV*/
828: /* l_sublot_num VARCHAR2(32); */
829: /*end , INVCONV*/
830: l_lpn_id rcv_supply.lpn_id%TYPE;
831: l_validation_flag rcv_transactions_interface.validation_flag%TYPE;
832:
833: CURSOR supply_quantity(
834: l_lot_num VARCHAR2,
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: */
921: SELECT NVL(validation_flag, 'N')
922: INTO l_validation_flag
924: WHERE interface_transaction_id = p_interface_transaction_id;
925:
926: SELECT NVL(lpn_id, -999)
927: INTO l_lpn_id
928: FROM rcv_supply
929: WHERE supply_source_id = p_parent_supply_id;
930:
931: IF ( l_validation_flag = 'N'
932: AND l_lpn_id = -999) THEN
937: asn_debug.put_line('l_validation_flag ' || l_validation_flag);
938: asn_debug.put_line('l_lpn_id ' || l_lpn_id);
939: END IF;
940:
941: /* When we update rcv_supply, we call this procedure and set
942: * the p_correction_type depending upon whether we need to add
943: * or subtract supply from rcv_lots_supply.
944: */
945: IF (p_correction_type = 'POSITIVE') THEN
949: END IF;
950:
951: /* We need to insert or update rcv_lot_supply only when there is
952: * already a row existing in rcv_lots_supply for a corresponding
953: * row in rcv_supply. If not dont do anything.
954: */
955: SELECT COUNT(*)
956: INTO l_count
957: FROM rcv_lots_supply
1428: l_range_numbers NUMBER;
1429: l_serial_count NUMBER;
1430: l_update_shipment_supply VARCHAR2(1) := 'N';
1431: l_parent_trans_type rcv_transactions.transaction_type%TYPE;
1432: l_lpn_id rcv_supply.lpn_id%TYPE;
1433: l_validation_flag rcv_transactions_interface.validation_flag%TYPE;
1434: l_shipment_header_id rcv_shipment_headers.shipment_header_id%TYPE;
1435: l_item_id rcv_transactions_interface.item_id%TYPE;
1436: BEGIN
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: */
1474: SELECT NVL(validation_flag, 'N')
1475: INTO l_validation_flag
1477: WHERE interface_transaction_id = p_interface_transaction_id;
1478:
1479: SELECT NVL(lpn_id, -999)
1480: INTO l_lpn_id
1481: FROM rcv_supply
1482: WHERE supply_source_id = p_parent_supply_id;
1483:
1484: IF ( l_validation_flag = 'N'
1485: AND l_lpn_id = -999) THEN
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
1759: WHERE rls.transaction_id = p_supply_source_id;
1760:
1761: l_rowid VARCHAR2(255);
1762: l_lot_count NUMBER;
1763: l_lpn_id rcv_supply.lpn_id%TYPE;
1764: l_validation_flag rcv_transactions_interface.validation_flag%TYPE;
1765: BEGIN
1766: IF (g_asn_debug = 'Y') THEN
1767: asn_debug.put_line('Enter insert_lots_supply');
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: */
1800: SELECT NVL(validation_flag, 'N')
1801: INTO l_validation_flag
1803: WHERE interface_transaction_id = p_interface_transaction_id;
1804:
1805: SELECT NVL(lpn_id, -999)
1806: INTO l_lpn_id
1807: FROM rcv_supply
1808: WHERE supply_source_id = p_supply_source_id;
1809:
1810: IF ( l_validation_flag = 'N'
1811: AND l_lpn_id = -999) THEN
1865: /*INVCONV*/
1866: /* mtltview.sublot_num, */
1867: /*end , INVCONV*/
1868: mtltview.reason_code
1869: FROM rcv_supply rs,
1870: (SELECT SUM(mtlt.transaction_quantity) qty,
1871: SUM(mtlt.primary_quantity) primary_qty,
1872: SUM(mtlt.secondary_quantity) secondary_qty,
1873: mtlt.lot_number,
1940: WHERE rss.transaction_id = p_supply_source_id;
1941:
1942: l_rowid VARCHAR2(255);
1943: l_serial_count NUMBER;
1944: l_lpn_id rcv_supply.lpn_id%TYPE;
1945: l_validation_flag rcv_transactions_interface.validation_flag%TYPE;
1946: BEGIN
1947: IF (g_asn_debug = 'Y') THEN
1948: asn_debug.put_line('Enter insert_serial_supply ');
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: */
1983: SELECT NVL(validation_flag, 'N')
1984: INTO l_validation_flag
1986: WHERE interface_transaction_id = p_interface_transaction_id;
1987:
1988: SELECT NVL(lpn_id, -999)
1989: INTO l_lpn_id
1990: FROM rcv_supply
1991: WHERE supply_source_id = p_supply_source_id;
1992:
1993: IF ( l_validation_flag = 'N'
1994: AND l_lpn_id = -999) THEN
2001: END IF;
2002:
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,
2036: rs.request_id,
2037: rs.program_application_id,
2038: rs.program_id,
2039: rs.program_update_date
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');
2083: p_negate_qty IN VARCHAR2,
2084: x_return_status OUT NOCOPY VARCHAR2
2085: ) IS
2086: l_lot_count NUMBER;
2087: l_lpn_id rcv_supply.lpn_id%TYPE;
2088: l_validation_flag rcv_transactions_interface.validation_flag%TYPE;
2089: BEGIN
2090: IF (g_asn_debug = 'Y') THEN
2091: asn_debug.put_line(' enter insert_lot_transactions');