[Home] [Help]
10: --
11:
12: g_asn_debug VARCHAR2(1) := NVL(fnd_profile.VALUE('RCV_DEBUG_MODE'), 'N');
13:
14: PROCEDURE insert_rcv_lots_supply(
15: p_api_version IN NUMBER,
16: p_init_msg_list IN VARCHAR2,
17: x_return_status OUT NOCOPY VARCHAR2,
18: p_interface_transaction_id IN NUMBER,
22: p_transaction_type IN VARCHAR2
23: ) IS
24: CURSOR c IS
25: SELECT rls.ROWID
26: FROM rcv_lots_supply rls,
27: rcv_transactions rt
28: WHERE rt.interface_transaction_id = p_interface_transaction_id
29: AND rls.transaction_id = rt.transaction_id;
30:
69: ) IS
70: SELECT rls.quantity,
71: rls.primary_quantity,
72: rls.shipment_line_id
73: FROM rcv_lots_supply rls
74: WHERE rls.lot_num = l_lot_num
75: AND rls.supply_type_code = 'SHIPMENT'
76: AND EXISTS(SELECT 1
77: FROM rcv_shipment_lines rsl
78: WHERE rsl.shipment_header_id = l_shipment_header_id
79: AND rsl.item_id = l_item_id
80: AND rsl.shipment_line_id = rls.shipment_line_id);
81:
82: l_lot_num rcv_lots_supply.lot_num%TYPE;
83: l_qty_to_be_updated NUMBER;
84: l_primary_qty_to_be_updated NUMBER;
85: l_ship_id NUMBER;
86: l_ship_line_id NUMBER;
120: IF (g_asn_debug = 'Y') THEN
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.
169: END IF;
170:
171: /* If this is a receive for an internal shipment or interorg transfer
172: * then we need to update the values for the shipment
173: * supply in rcv_lots_supply.
174: */
175: /* INVCONV , update for process transactions also.
176: Remove the process specific restriction. Punit Kumar */
177:
188: * So instead of using shipment_line_id to delete use
189: * shipment_header_id.
190: */
191: IF (g_asn_debug = 'Y') THEN
192: asn_debug.put_line('In insert rcv_lots_supply for source type code INVENTORY or REQ');
193: END IF;
194:
195: OPEN lot_numbers(p_interface_transaction_id);
196:
287: asn_debug.put_line('l_lot_num ' || l_lot_num);
288: asn_debug.put_line('l_ship_line_id ' || l_ship_line_id);
289: END IF;
290:
291: UPDATE rcv_lots_supply rls
292: SET quantity = quantity - l_qty_to_be_updated,
293: primary_quantity = primary_quantity - l_primary_qty_to_be_updated
294: WHERE rls.lot_num = l_lot_num
295: AND shipment_line_id = l_ship_line_id
305: asn_debug.put_line('l_lot_num ' || l_lot_num);
306: asn_debug.put_line('l_ship_line_id ' || l_ship_line_id);
307: END IF;
308:
309: UPDATE rcv_lots_supply rls
310: SET quantity = 0,
311: primary_quantity = 0
312: WHERE rls.lot_num = l_lot_num
313: AND shipment_line_id = l_ship_line_id
338: END IF; --}
339: /* END IF; */
340:
341: IF (g_asn_debug = 'Y') THEN
342: asn_debug.put_line('Exit insert_rcv_lots_supply');
343: END IF;
344: EXCEPTION
345: WHEN NO_DATA_FOUND THEN
346: IF (g_asn_debug = 'Y') THEN
343: END IF;
344: EXCEPTION
345: WHEN NO_DATA_FOUND THEN
346: IF (g_asn_debug = 'Y') THEN
347: asn_debug.put_line('no_data_found insert_rcv_lots_supply');
348: END IF;
349:
350: x_return_status := fnd_api.g_ret_sts_error;
351:
380: FROM rcv_transactions_interface rti
381: WHERE rti.interface_transaction_id = p_interface_transaction_id;
382: WHEN OTHERS THEN
383: IF (g_asn_debug = 'Y') THEN
384: asn_debug.put_line('others insert_rcv_lots_supply');
385: END IF;
386:
387: x_return_status := fnd_api.g_ret_sts_unexp_error;
388:
415: rti.program_id,
416: rti.program_update_date
417: FROM rcv_transactions_interface rti
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,
487: IF (g_asn_debug = 'Y') THEN
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.
753: x_number := TO_NUMBER(SUBSTR(p_sequence, l_loop_index + 1));
754: END IF;
755: END split_serial_number;
756:
757: PROCEDURE update_rcv_lots_supply(
758: p_api_version IN NUMBER,
759: p_init_msg_list IN VARCHAR2,
760: x_return_status OUT NOCOPY VARCHAR2,
761: p_interface_transaction_id IN NUMBER,
798: rti.unit_of_measure,
799: rti.to_organization_id;
800:
801: /** Bug 5571740:
802: * Changed the declaration of l_lot_num from varchar2(30) to rcv_lots_supply.lot_num%TYPE
803: */
804: l_lot_num rcv_lots_supply.lot_num%TYPE;
805: l_factor NUMBER;
806: l_parent_trans_type rcv_transactions.transaction_type%TYPE;
800:
801: /** Bug 5571740:
802: * Changed the declaration of l_lot_num from varchar2(30) to rcv_lots_supply.lot_num%TYPE
803: */
804: l_lot_num rcv_lots_supply.lot_num%TYPE;
805: l_factor NUMBER;
806: l_parent_trans_type rcv_transactions.transaction_type%TYPE;
807: l_count NUMBER;
808: l_count1 NUMBER;
827: l_item_id NUMBER
828: ) IS
829: SELECT rls.quantity,
830: rls.primary_quantity
831: FROM rcv_lots_supply rls
832: WHERE rls.lot_num = l_lot_num
833: /* INVCONV*/
834: /*
835: AND ( (rls.sublot_num = l_sublot_num)
851: ) IS
852: SELECT rls.quantity,
853: rls.primary_quantity,
854: rls.shipment_line_id
855: FROM rcv_lots_supply rls
856: WHERE rls.lot_num = l_lot_num
857: AND rls.supply_type_code = 'SHIPMENT'
858: AND EXISTS(SELECT 1
859: FROM rcv_shipment_lines rsl
875: l_primary_uom VARCHAR2(25);
876: l_to_org_id NUMBER;
877: BEGIN
878: IF (g_asn_debug = 'Y') THEN
879: asn_debug.put_line('Enter update_rcv_lots_supply ');
880: asn_debug.put_line('p_interface_transaction_id ' || p_interface_transaction_id);
881: asn_debug.put_line('p_transaction_type ' || p_transaction_type);
882: asn_debug.put_line('p_shipment_line_id ' || p_shipment_line_id);
883: asn_debug.put_line('p_source_type_code ' || p_source_type_code);
905: IF (g_asn_debug = 'Y') THEN
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.
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
940: l_factor := -1;
941: ELSE
942: l_factor := 1;
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
947: * row in rcv_supply. If not dont do anything.
948: */
949: SELECT COUNT(*)
950: INTO l_count
951: FROM rcv_lots_supply
952: WHERE transaction_id = p_parent_supply_id
953: AND supply_type_code = 'RECEIVING';
954:
955: IF (l_count = 0) THEN
1031:
1032: /** OPM change Bug# 3061052 added sublot_num check**/
1033: SELECT COUNT(*)
1034: INTO l_count1
1035: FROM rcv_lots_supply
1036: WHERE transaction_id = p_parent_supply_id
1037: AND lot_num = l_lot_num
1038: /* INVCONV */
1039: /*
1065: END IF;
1066: ELSE --}{
1067: /** OPM Change Bug# 3061052 add sublot_num check **/
1068: IF (g_asn_debug = 'Y') THEN
1069: asn_debug.put_line('Else update rcv_lots_supply');
1070: END IF;
1071:
1072: SELECT MAX(primary_unit_of_measure)
1073: INTO l_primary_uom
1106: END IF;
1107:
1108: /* INVCONV , Remove sublot_num. Punit Kumar */
1109:
1110: UPDATE rcv_lots_supply rls
1111: SET quantity = quantity -(l_qty_to_be_updated * l_factor),
1112: primary_quantity = primary_quantity -(l_primary_qty_to_be_updated * l_factor),
1113: secondary_quantity = secondary_quantity -(l_secondary_qty_to_be_updated * l_factor)
1114: WHERE rls.lot_num = l_lot_num
1131:
1132: IF (l_update_shipment_supply = 'Y') THEN --{
1133: SELECT COUNT(*)
1134: INTO l_count2
1135: FROM rcv_lots_supply
1136: WHERE shipment_line_id = p_shipment_line_id
1137: AND supply_type_code = 'SHIPMENT'
1138: AND lot_num = l_lot_num;
1139:
1221: asn_debug.put_line('l_lot_num ' || l_lot_num);
1222: asn_debug.put_line('l_ship_line_id ' || l_ship_line_id);
1223: END IF;
1224:
1225: UPDATE rcv_lots_supply rls
1226: SET quantity = quantity -(l_qty_to_be_updated * l_factor),
1227: primary_quantity = primary_quantity -(l_primary_qty_to_be_updated * l_factor)
1228: WHERE rls.lot_num = l_lot_num
1229: AND shipment_line_id = l_ship_line_id
1239: asn_debug.put_line('l_lot_num ' || l_lot_num);
1240: asn_debug.put_line('l_ship_line_id ' || l_ship_line_id);
1241: END IF;
1242:
1243: UPDATE rcv_lots_supply rls
1244: SET quantity = quantity -(quantity * l_factor),
1245: primary_quantity = primary_quantity -(primary_quantity * l_factor)
1246: WHERE rls.lot_num = l_lot_num
1247: AND shipment_line_id = l_ship_line_id
1262: into l_shipment_header_id,l_item_id
1263: from rcv_shipment_lines
1264: where shipment_line_id = p_shipment_line_id;
1265:
1266: update rcv_lots_supply rls
1267: set rls.quantity =
1268: (select rls.quantity +
1269: (sum(mtlt.transaction_quantity) * l_factor)
1270: from mtl_transaction_lots_temp mtlt
1297: asn_debug.put_line('Close lot_cursor ');
1298: END IF;
1299:
1300: IF (g_asn_debug = 'Y') THEN
1301: asn_debug.put_line('Exit update_rcv_lots_supply ');
1302: END IF;
1303: EXCEPTION
1304: WHEN NO_DATA_FOUND THEN
1305: IF (g_asn_debug = 'Y') THEN
1302: END IF;
1303: EXCEPTION
1304: WHEN NO_DATA_FOUND THEN
1305: IF (g_asn_debug = 'Y') THEN
1306: asn_debug.put_line('no_data_found update_rcv_lots_supply ');
1307: END IF;
1308:
1309: x_return_status := fnd_api.g_ret_sts_error;
1310:
1339: FROM rcv_transactions_interface rti
1340: WHERE rti.interface_transaction_id = p_interface_transaction_id;
1341: WHEN OTHERS THEN
1342: IF (g_asn_debug = 'Y') THEN
1343: asn_debug.put_line('others update_rcv_lots_supply ');
1344: END IF;
1345:
1346: x_return_status := fnd_api.g_ret_sts_unexp_error;
1347:
1374: rti.program_id,
1375: rti.program_update_date
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,
1451: IF (g_asn_debug = 'Y') THEN
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.
1741: x_return_status OUT NOCOPY VARCHAR2
1742: ) IS
1743: CURSOR c IS
1744: SELECT rls.ROWID
1745: FROM rcv_lots_supply rls
1746: WHERE rls.transaction_id = p_supply_source_id;
1747:
1748: l_rowid VARCHAR2(255);
1749: l_lot_count NUMBER;
1777: IF (g_asn_debug = 'Y') THEN
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.
1804: asn_debug.put_line('l_lpn_id ' || l_lpn_id);
1805: END IF;
1806: /* INVCONV, Remove sublot_num . Punit Kumar*/
1807:
1808: INSERT INTO rcv_lots_supply
1809: (supply_type_code,
1810: shipment_line_id,
1811: transaction_id,
1812: last_updated_by,
1878: AND mtltview.product_code = 'RCV'
1879: AND rs.supply_source_id = p_supply_source_id);
1880:
1881: IF (g_asn_debug = 'Y') THEN
1882: asn_debug.put_line('After insert into rcv_lots_supply ');
1883: asn_debug.put_line('INVCONV, Subllot_num has been removed in insert_lot_supply1 ');
1884: END IF;
1885:
1886: OPEN c;
1960: IF (g_asn_debug = 'Y') THEN
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.