[Home] [Help]
10: --
11:
12: g_asn_debug VARCHAR2(1) := asn_debug.is_debug_on; -- Bug 9152790
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:
74: ) IS
75: SELECT rls.rowid,
76: rls.quantity,
77: rls.primary_quantity
78: FROM rcv_lots_supply rls
79: WHERE rls.lot_num = l_lot_num
80: AND rls.supply_type_code = 'SHIPMENT'
81: AND rls.shipment_line_id = l_shipment_line_id;
82:
79: WHERE rls.lot_num = l_lot_num
80: AND rls.supply_type_code = 'SHIPMENT'
81: AND rls.shipment_line_id = l_shipment_line_id;
82:
83: l_lot_num rcv_lots_supply.lot_num%TYPE;
84: l_qty_to_be_updated NUMBER;
85: l_primary_qty_to_be_updated NUMBER;
86: l_ship_id NUMBER;
87: l_ship_line_id NUMBER;
121: IF (g_asn_debug = 'Y') THEN
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.
170: END IF;
171:
172: /* If this is a receive for an internal shipment or interorg transfer
173: * then we need to update the values for the shipment
174: * supply in rcv_lots_supply.
175: */
176: /* INVCONV , update for process transactions also.
177: Remove the process specific restriction. Punit Kumar */
178:
189: * So instead of using shipment_line_id to delete use
190: * shipment_header_id.
191: */
192: IF (g_asn_debug = 'Y') THEN
193: asn_debug.put_line('In insert rcv_lots_supply for source type code INVENTORY or REQ');
194: END IF;
195:
196: OPEN lot_numbers(p_interface_transaction_id);
197:
289: asn_debug.put_line('l_ship_line_id ' || l_ship_line_id);
290: END IF;
291:
292: /* add rowid for bug 9839004 */
293: UPDATE rcv_lots_supply rls
294: SET quantity = quantity - l_qty_to_be_updated,
295: primary_quantity = primary_quantity - l_primary_qty_to_be_updated
296: WHERE rls.lot_num = l_lot_num
297: AND shipment_line_id = l_ship_line_id
309: asn_debug.put_line('l_ship_line_id ' || l_ship_line_id);
310: END IF;
311:
312: /* add rowid for bug 9839004 */
313: UPDATE rcv_lots_supply rls
314: SET quantity = 0,
315: primary_quantity = 0
316: WHERE rls.lot_num = l_lot_num
317: AND shipment_line_id = l_ship_line_id
335: END IF;
336: IF l_primary_qty_to_be_updated <> 0 THEN --Bug 7443786
337: asn_debug.put_line('l_ship_line_id ' || l_ship_line_id);
338: asn_debug.put_line('l_primary_qty_to_be_updated ' || l_primary_qty_to_be_updated);
339: /*Bug 13792458 when ISO or IOT from_organization is not lot controlled, it will not consume rcv_lots_supply quantity */
340: --asn_debug.put_line('SHIPMENT supply for above qty not available to consume..Fail the transaction...');
341: --raise NO_DATA_FOUND;
342: /*End Bug 13792458 */
343: END IF; --Bug 7443786
351: END IF; --}
352: /* END IF; */
353:
354: IF (g_asn_debug = 'Y') THEN
355: asn_debug.put_line('Exit insert_rcv_lots_supply');
356: END IF;
357: EXCEPTION
358: WHEN NO_DATA_FOUND THEN
359: IF (g_asn_debug = 'Y') THEN
356: END IF;
357: EXCEPTION
358: WHEN NO_DATA_FOUND THEN
359: IF (g_asn_debug = 'Y') THEN
360: asn_debug.put_line('no_data_found insert_rcv_lots_supply');
361: END IF;
362:
363: x_return_status := fnd_api.g_ret_sts_error;
364:
393: FROM rcv_transactions_interface rti
394: WHERE rti.interface_transaction_id = p_interface_transaction_id;
395: WHEN OTHERS THEN
396: IF (g_asn_debug = 'Y') THEN
397: asn_debug.put_line('others insert_rcv_lots_supply');
398: END IF;
399:
400: x_return_status := fnd_api.g_ret_sts_unexp_error;
401:
428: rti.program_id,
429: rti.program_update_date
430: FROM rcv_transactions_interface rti
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,
500: IF (g_asn_debug = 'Y') THEN
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.
766: x_number := TO_NUMBER(SUBSTR(p_sequence, l_loop_index + 1));
767: END IF;
768: END split_serial_number;
769:
770: PROCEDURE update_rcv_lots_supply(
771: p_api_version IN NUMBER,
772: p_init_msg_list IN VARCHAR2,
773: x_return_status OUT NOCOPY VARCHAR2,
774: p_interface_transaction_id IN NUMBER,
811: rti.unit_of_measure,
812: rti.to_organization_id;
813:
814: /** Bug 5571740:
815: * Changed the declaration of l_lot_num from varchar2(30) to rcv_lots_supply.lot_num%TYPE
816: */
817: l_lot_num rcv_lots_supply.lot_num%TYPE;
818: l_factor NUMBER;
819: l_parent_trans_type rcv_transactions.transaction_type%TYPE;
813:
814: /** Bug 5571740:
815: * Changed the declaration of l_lot_num from varchar2(30) to rcv_lots_supply.lot_num%TYPE
816: */
817: l_lot_num rcv_lots_supply.lot_num%TYPE;
818: l_factor NUMBER;
819: l_parent_trans_type rcv_transactions.transaction_type%TYPE;
820: l_count NUMBER;
821: l_count1 NUMBER;
839: l_item_id NUMBER
840: ) IS
841: SELECT rls.quantity,
842: rls.primary_quantity
843: FROM rcv_lots_supply rls
844: WHERE rls.lot_num = l_lot_num
845: /* INVCONV*/
846: /*
847: AND ( (rls.sublot_num = l_sublot_num)
861: l_shipment_line_id NUMBER
862: ) IS
863: SELECT rls.quantity,
864: rls.primary_quantity
865: FROM rcv_lots_supply rls
866: WHERE rls.lot_num = l_lot_num
867: AND rls.supply_type_code = 'SHIPMENT'
868: AND rls.shipment_line_id = l_shipment_line_id;
869:
881: l_primary_uom VARCHAR2(25);
882: l_to_org_id NUMBER;
883: BEGIN
884: IF (g_asn_debug = 'Y') THEN
885: asn_debug.put_line('Enter update_rcv_lots_supply ');
886: asn_debug.put_line('p_interface_transaction_id ' || p_interface_transaction_id);
887: asn_debug.put_line('p_transaction_type ' || p_transaction_type);
888: asn_debug.put_line('p_shipment_line_id ' || p_shipment_line_id);
889: asn_debug.put_line('p_source_type_code ' || p_source_type_code);
911: IF (g_asn_debug = 'Y') THEN
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.
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
946: l_factor := -1;
947: ELSE
948: l_factor := 1;
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
953: * row in rcv_supply. If not dont do anything.
954: */
955: SELECT COUNT(*)
956: INTO l_count
957: FROM rcv_lots_supply
958: WHERE transaction_id = p_parent_supply_id
959: AND supply_type_code = 'RECEIVING';
960:
961: IF (l_count = 0) THEN
1037:
1038: /** OPM change Bug# 3061052 added sublot_num check**/
1039: SELECT COUNT(*)
1040: INTO l_count1
1041: FROM rcv_lots_supply
1042: WHERE transaction_id = p_parent_supply_id
1043: AND lot_num = l_lot_num
1044: /* INVCONV */
1045: /*
1071: END IF;
1072: ELSE --}{
1073: /** OPM Change Bug# 3061052 add sublot_num check **/
1074: IF (g_asn_debug = 'Y') THEN
1075: asn_debug.put_line('Else update rcv_lots_supply');
1076: END IF;
1077:
1078: SELECT MAX(primary_unit_of_measure)
1079: INTO l_primary_uom
1112: END IF;
1113:
1114: /* INVCONV , Remove sublot_num. Punit Kumar */
1115:
1116: UPDATE rcv_lots_supply rls
1117: SET quantity = quantity -(l_qty_to_be_updated * l_factor),
1118: primary_quantity = primary_quantity -(l_primary_qty_to_be_updated * l_factor),
1119: secondary_quantity = secondary_quantity -(l_secondary_qty_to_be_updated * l_factor)
1120: WHERE rls.lot_num = l_lot_num
1137:
1138: IF (l_update_shipment_supply = 'Y') THEN --{
1139: SELECT COUNT(*)
1140: INTO l_count2
1141: FROM rcv_lots_supply
1142: WHERE shipment_line_id = p_shipment_line_id
1143: AND supply_type_code = 'SHIPMENT'
1144: AND lot_num = l_lot_num;
1145:
1225: asn_debug.put_line('l_lot_num ' || l_lot_num);
1226: asn_debug.put_line('l_ship_line_id ' || l_ship_line_id);
1227: END IF;
1228:
1229: UPDATE rcv_lots_supply rls
1230: SET quantity = quantity -(l_qty_to_be_updated * l_factor),
1231: primary_quantity = primary_quantity -(l_primary_qty_to_be_updated * l_factor)
1232: WHERE rls.lot_num = l_lot_num
1233: AND shipment_line_id = l_ship_line_id
1243: asn_debug.put_line('l_lot_num ' || l_lot_num);
1244: asn_debug.put_line('l_ship_line_id ' || l_ship_line_id);
1245: END IF;
1246:
1247: UPDATE rcv_lots_supply rls
1248: SET quantity = quantity -(quantity * l_factor),
1249: primary_quantity = primary_quantity -(primary_quantity * l_factor)
1250: WHERE rls.lot_num = l_lot_num
1251: AND shipment_line_id = l_ship_line_id
1264:
1265: IF l_primary_qty_to_be_updated <> 0 THEN --Bug 7443786
1266: asn_debug.put_line('l_ship_line_id ' || l_ship_line_id);
1267: asn_debug.put_line('l_primary_qty_to_be_updated ' || l_primary_qty_to_be_updated);
1268: /*Bug 13792458 when ISO or IOT from_organization is not lot controlled, it will not consume rcv_lots_supply quantity */
1269: --asn_debug.put_line('SHIPMENT supply for above qty not available to consume..Fail the transaction...');
1270: --raise NO_DATA_FOUND;
1271: /*End Bug 13792458 */
1272: END IF; --Bug 7443786
1275: into l_shipment_header_id,l_item_id
1276: from rcv_shipment_lines
1277: where shipment_line_id = p_shipment_line_id;
1278:
1279: update rcv_lots_supply rls
1280: set rls.quantity =
1281: (select rls.quantity +
1282: (sum(mtlt.transaction_quantity) * l_factor)
1283: from mtl_transaction_lots_temp mtlt
1310: asn_debug.put_line('Close lot_cursor ');
1311: END IF;
1312:
1313: IF (g_asn_debug = 'Y') THEN
1314: asn_debug.put_line('Exit update_rcv_lots_supply ');
1315: END IF;
1316: EXCEPTION
1317: WHEN NO_DATA_FOUND THEN
1318: IF (g_asn_debug = 'Y') THEN
1315: END IF;
1316: EXCEPTION
1317: WHEN NO_DATA_FOUND THEN
1318: IF (g_asn_debug = 'Y') THEN
1319: asn_debug.put_line('no_data_found update_rcv_lots_supply ');
1320: END IF;
1321:
1322: x_return_status := fnd_api.g_ret_sts_error;
1323:
1352: FROM rcv_transactions_interface rti
1353: WHERE rti.interface_transaction_id = p_interface_transaction_id;
1354: WHEN OTHERS THEN
1355: IF (g_asn_debug = 'Y') THEN
1356: asn_debug.put_line('others update_rcv_lots_supply ');
1357: END IF;
1358:
1359: x_return_status := fnd_api.g_ret_sts_unexp_error;
1360:
1387: rti.program_id,
1388: rti.program_update_date
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,
1464: IF (g_asn_debug = 'Y') THEN
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.
1754: x_return_status OUT NOCOPY VARCHAR2
1755: ) IS
1756: CURSOR c IS
1757: SELECT rls.ROWID
1758: FROM rcv_lots_supply rls
1759: WHERE rls.transaction_id = p_supply_source_id;
1760:
1761: l_rowid VARCHAR2(255);
1762: l_lot_count NUMBER;
1790: IF (g_asn_debug = 'Y') THEN
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.
1817: asn_debug.put_line('l_lpn_id ' || l_lpn_id);
1818: END IF;
1819: /* INVCONV, Remove sublot_num . Punit Kumar*/
1820:
1821: INSERT INTO rcv_lots_supply
1822: (supply_type_code,
1823: shipment_line_id,
1824: transaction_id,
1825: last_updated_by,
1891: AND mtltview.product_code = 'RCV'
1892: AND rs.supply_source_id = p_supply_source_id);
1893:
1894: IF (g_asn_debug = 'Y') THEN
1895: asn_debug.put_line('After insert into rcv_lots_supply ');
1896: asn_debug.put_line('INVCONV, Subllot_num has been removed in insert_lot_supply1 ');
1897: END IF;
1898:
1899: OPEN c;
1973: IF (g_asn_debug = 'Y') THEN
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.