[Home] [Help]
143:
144: dbms_sql.close_cursor(l_epc_cursor);
145: ELSE --Serial
146: l_epc_sql := 'SELECT ' || l_epc_column;
147: l_epc_sql := l_epc_sql || ' FROM mtl_serial_numbers_temp';
148: l_epc_sql := l_epc_sql || ' WHERE rowid = :row_id';
149:
150: IF (l_debug = 1) THEN
151: print_debug('l_epc_sql: '||l_epc_sql,4);
605: ,msnt.time_since_mark time_since_mark
606: ,msnt.cycles_since_mark cycles_since_mark
607: ,msnt.number_of_repairs number_of_repairs
608: from
609: mtl_serial_numbers_temp msnt
610: ,rcv_transactions_interface rti
611: where
612: msnt.product_code = 'RCV'
613: AND msnt.product_transaction_id = p_prod_txn_tmp_id
1077: AND product_transaction_id = p_prod_txn_tmp_id;
1078:
1079: l_progress := 'WMSINB-10883';
1080:
1081: DELETE FROM mtl_serial_numbers_temp
1082: WHERE product_code = 'RCV'
1083: AND product_transaction_id = p_prod_txn_tmp_id;
1084:
1085: IF (l_debug = 1) THEN
1629:
1630: IF l_serials_count > 0 THEN
1631: /* Changed the serial_number with serial_num in inner query for the bug#16068136
1632: With out this fix, status of all the serials of the item are updating to 1*/
1633: UPDATE mtl_serial_numbers
1634: SET current_status = 1,
1635: group_mark_id = NULL,
1636: line_mark_id = NULL,
1637: lot_line_mark_id = NULL
1765: BEGIN
1766: l_progress := 'WMSINB-11324';
1767: SELECT Nvl(SUM(inv_serial_number_pub.get_serial_diff(fm_serial_number,to_serial_number)),0)
1768: INTO l_total_ser_qty
1769: FROM mtl_serial_numbers_temp
1770: WHERE product_code = p_product_code
1771: AND product_transaction_id = p_product_txn_id;
1772: END;
1773: End if;
1793: l_progress := 'WMSINB-11351';
1794: For l_serial_rec in ( select fm_serial_number
1795: , to_serial_number
1796: , inv_serial_number_pub.get_serial_diff(fm_serial_number,to_serial_number) serial_quantity
1797: from mtl_serial_numbers_temp msnt
1798: where msnt.transaction_temp_id = l_lot_rec.serial_transaction_temp_id
1799: and msnt.product_transaction_id = p_product_txn_id
1800: and msnt.product_code = 'RCV'
1801: )
1912: l_progress := 'WMSINB-11453';
1913: For l_serial_rec in ( select fm_serial_number
1914: ,to_serial_number
1915: ,inv_serial_number_pub.get_serial_diff(fm_serial_number,to_serial_number) serial_quantity
1916: from mtl_serial_numbers_temp msnt
1917: where msnt.product_transaction_id = p_product_txn_id
1918: and msnt.product_code = 'RCV'
1919: )
1920: Loop
2259: SELECT
2260: fm_serial_number,
2261: to_serial_number
2262: FROM
2263: mtl_serial_numbers_interface
2264: WHERE l_intf_txn_id = transaction_interface_id;
2265:
2266: l_wlpnci_total_qty NUMBER;
2267: l_msni_total_qty NUMBER := 0;
2728:
2729: -- Now update the product code and product_intf_id
2730: IF (l_msni_total_qty > 0) THEN
2731: BEGIN
2732: UPDATE mtl_serial_numbers_interface
2733: SET product_code = 'RCV',
2734: product_transaction_id=l_rti_rec.interface_transaction_id,
2735: transaction_interface_id = l_serial_txn_intf_id
2736: --WHERE transaction_interface_id = l_wlpnci_rec.interface_transaction_id;
3269:
3270: --BUG 5008139 -- Getting the count of serial records in MSNT
3271: select count(*)
3272: into l_count
3273: from mtl_serial_numbers_temp
3274: where product_code = 'RCV'
3275: and product_transaction_id = p_product_txn_id;
3276:
3277: IF (l_debug = 1) THEN
3335: -- 'Resides in Stores' in the same session.
3336:
3337: -- Bug 7427382 Updating columns owning org and planning org also.
3338:
3339: update mtl_serial_numbers msn
3340: set msn.current_status = p_serial_status
3341: , inspection_status = p_inspection_status
3342: , group_mark_id = NULL
3343: , line_mark_id = decode(l_clear_line_mark_id, 1, NULL, line_mark_id)
3352: -- and msn.current_organization_id = p_organization_id -- this part is not needed as this was causing INTSHIP RECEIVE to fail
3353: -- BUG 5611567
3354: msn.ROWID in (
3355: select msn1.ROWID
3356: from mtl_serial_numbers msn1
3357: , mtl_serial_numbers_temp msnt
3358: where msn1.inventory_item_id = p_item_id
3359: and msnt.product_code = 'RCV'
3360: and msnt.product_transaction_id = p_product_txn_id
3353: -- BUG 5611567
3354: msn.ROWID in (
3355: select msn1.ROWID
3356: from mtl_serial_numbers msn1
3357: , mtl_serial_numbers_temp msnt
3358: where msn1.inventory_item_id = p_item_id
3359: and msnt.product_code = 'RCV'
3360: and msnt.product_transaction_id = p_product_txn_id
3361: and msn1.serial_number between msnt.fm_serial_number and msnt.to_serial_number
3366:
3367: -- --BEGIN Bug: 5524134
3368: -- IF p_serial_status = -9999 THEN
3369: -- IF l_clear_line_mark_id = 1 THEN --{
3370: -- update mtl_serial_numbers msn
3371: -- set
3372: -- group_mark_id = NULL,
3373: -- line_mark_id = NULL,
3374: -- lot_line_mark_id = NULL
3374: -- lot_line_mark_id = NULL
3375: -- where msn.inventory_item_id = p_item_id
3376: -- -- and msn.current_organization_id = p_organization_id -- this part is not needed as this was causing INTSHIP RECEIVE to fail
3377: -- and exists ( select 1
3378: -- from mtl_serial_numbers_temp msnt
3379: -- where msnt.product_code = 'RCV'
3380: -- and msnt.product_transaction_id = p_product_txn_id
3381: -- and msn.serial_number between msnt.fm_serial_number and msnt.to_serial_number
3382: -- AND Length(msn.serial_number) = Length(msnt.fm_serial_number)
3383: -- AND length(msnt.fm_serial_number)=Length(Nvl(msnt.to_serial_number,msnt.fm_serial_number)) --BUG 3818544
3384: -- )
3385: -- ;
3386: -- ELSE --l_clear_line_mark_id = 1 }{
3387: -- update mtl_serial_numbers msn
3388: -- set
3389: -- group_mark_id = NULL
3390: -- where msn.inventory_item_id = p_item_id
3391: -- -- and msn.current_organization_id = p_organization_id -- this part is not needed as this was causing INTSHIP RECEIVE to fail
3389: -- group_mark_id = NULL
3390: -- where msn.inventory_item_id = p_item_id
3391: -- -- and msn.current_organization_id = p_organization_id -- this part is not needed as this was causing INTSHIP RECEIVE to fail
3392: -- and exists ( select 1
3393: -- from mtl_serial_numbers_temp msnt
3394: -- where msnt.product_code = 'RCV'
3395: -- and msnt.product_transaction_id = p_product_txn_id
3396: -- and msn.serial_number between msnt.fm_serial_number and msnt.to_serial_number
3397: -- AND Length(msn.serial_number) = Length(msnt.fm_serial_number)
3401: -- END IF;--l_clear_line_mark_id = 1 }
3402: -- ELSE
3403: -- --END Bug: 5524134
3404: -- IF l_clear_line_mark_id = 1 THEN --{
3405: -- update mtl_serial_numbers msn
3406: -- set msn.current_status = p_serial_status
3407: -- , inspection_status = p_inspection_status
3408: -- , group_mark_id = NULL
3409: -- , line_mark_id = NULL
3415: -- -- msn.inventory_item_id = p_item_id
3416: -- -- and msn.current_organization_id = p_organization_id -- this part is not needed as this was causing INTSHIP RECEIVE to fail
3417: -- -- BUG 5611567
3418: -- msn.ROWID in ( select msn1.ROWID
3419: -- from mtl_serial_numbers msn1 ,
3420: -- mtl_serial_numbers_temp msnt
3421: -- where msn1.inventory_item_id = p_item_id
3422: -- and msnt.product_code = 'RCV'
3423: -- and msnt.product_transaction_id = p_product_txn_id
3416: -- -- and msn.current_organization_id = p_organization_id -- this part is not needed as this was causing INTSHIP RECEIVE to fail
3417: -- -- BUG 5611567
3418: -- msn.ROWID in ( select msn1.ROWID
3419: -- from mtl_serial_numbers msn1 ,
3420: -- mtl_serial_numbers_temp msnt
3421: -- where msn1.inventory_item_id = p_item_id
3422: -- and msnt.product_code = 'RCV'
3423: -- and msnt.product_transaction_id = p_product_txn_id
3424: -- and msn1.serial_number between msnt.fm_serial_number and msnt.to_serial_number
3426: -- AND length(msnt.fm_serial_number)=Length(nvl(msnt.to_serial_number,msnt.fm_serial_number)) --BUG 3818544
3427: -- )
3428: -- ;
3429: -- ELSE--l_clear_line_mark_id = 1 }{
3430: -- update mtl_serial_numbers msn
3431: -- set msn.current_status = p_serial_status
3432: -- , inspection_status = p_inspection_status
3433: -- , group_mark_id = NULL
3434: -- , current_organization_id = p_organization_id
3438: -- -- msn.inventory_item_id = p_item_id
3439: -- -- and msn.current_organization_id = p_organization_id -- this part is not needed as this was causing INTSHIP RECEIVE to fail
3440: -- -- BUG 5611567
3441: -- msn.ROWID in ( select msn1.ROWID
3442: -- from mtl_serial_numbers msn1 ,
3443: -- mtl_serial_numbers_temp msnt
3444: -- where msn1.inventory_item_id = p_item_id
3445: -- and msnt.product_code = 'RCV'
3446: -- and msnt.product_transaction_id = p_product_txn_id
3439: -- -- and msn.current_organization_id = p_organization_id -- this part is not needed as this was causing INTSHIP RECEIVE to fail
3440: -- -- BUG 5611567
3441: -- msn.ROWID in ( select msn1.ROWID
3442: -- from mtl_serial_numbers msn1 ,
3443: -- mtl_serial_numbers_temp msnt
3444: -- where msn1.inventory_item_id = p_item_id
3445: -- and msnt.product_code = 'RCV'
3446: -- and msnt.product_transaction_id = p_product_txn_id
3447: -- and msn1.serial_number between msnt.fm_serial_number and msnt.to_serial_number
5297: -- ELSIF p_serial_number_control_code IN (2,5,6) THEN
5298: -- BEGIN
5299: -- SELECT '1'
5300: -- INTO l_dummy
5301: -- FROM mtl_serial_numbers_temp
5302: -- WHERE product_code = 'RCV'
5303: -- AND product_transaction_id = p_rti_id
5304: -- AND ROWNUM < 2;
5305: -- EXCEPTION
6506: -- ELSIF p_serial_number_control_code IN (2,5,6) THEN
6507: -- BEGIN
6508: -- SELECT '1'
6509: -- INTO l_dummy
6510: -- FROM mtl_serial_numbers_temp
6511: -- WHERE product_code = 'RCV'
6512: -- AND product_transaction_id = p_rti_id
6513: -- AND ROWNUM < 2;
6514: -- EXCEPTION
8806: -- GET the TOTAL SERIAL QTY
8807: l_progress := 'WMSINB-14439';
8808: SELECT Nvl(SUM(inv_serial_number_pub.get_serial_diff(fm_serial_number,to_serial_number)),0)
8809: INTO l_total_serial_qty
8810: FROM mtl_serial_numbers_temp
8811: WHERE product_code = 'RCV'
8812: AND product_transaction_id = l_rti_rec.interface_transaction_id;
8813:
8814: IF (l_total_lot_qty = 0 AND l_total_serial_qty = 0) THEN
8875:
8876: -- We should not be doing full unpack. Instead if one of
8877: -- the conditions used to set l_full_unpack to true is
8878: -- met then we should null out the lot_number on
8879: -- wms_lpn_contents and null out the lpn_id on mtl_serial_numbers
8880:
8881: UPDATE wms_lpn_contents
8882: SET lot_number = NULL,
8883: SERIAL_SUMMARY_ENTRY=2,--15951100
8884: revision = NULL
8885: WHERE parent_lpn_id = l_rti_rec.lpn_id
8886: AND inventory_item_id = l_rti_rec.item_id;
8887:
8888: UPDATE mtl_serial_numbers
8889: SET lpn_id = NULL
8890: WHERE inventory_item_id = l_rti_rec.item_id
8891: AND lpn_id = l_rti_rec.lpn_id
8892: AND current_status <> 7; -- Bug 6902368
9535: l_progress := 'WMSINB-14864';
9536: -- UPDATE SERIAL STATUS to 4 for serial control code at SO ISUUE CASE
9537: if l_serial_control_code = 6 then
9538: l_progress := 'WMSINB-14867';
9539: update mtl_serial_numbers msn
9540: set msn.current_status = 4
9541: where msn.inventory_item_id = l_rti_rec.item_id
9542: and msn.current_organization_id = l_rti_rec.to_organization_id
9543: and exists ( select 1
9540: set msn.current_status = 4
9541: where msn.inventory_item_id = l_rti_rec.item_id
9542: and msn.current_organization_id = l_rti_rec.to_organization_id
9543: and exists ( select 1
9544: from mtl_serial_numbers_temp msnt
9545: where msnt.product_code = 'RCV'
9546: and msnt.product_transaction_id = p_txn_id
9547: and msn.serial_number between msnt.fm_serial_number and msnt.to_serial_number
9548: AND Length(msn.serial_number) = Length(msnt.fm_serial_number)
10529:
10530: l_user_id := fnd_global.user_id;
10531: l_login_id := fnd_global.login_id;
10532:
10533: Insert into MTL_SERIAL_NUMBERS_INTERFACE
10534: (
10535: transaction_interface_id,
10536: Source_Code,
10537: Source_Line_Id,
11513: l_serial_interface_id := null;
11514:
11515: -- Call insert MSNI for linked Serials Here
11516: For l_serial_rec in ( select msn.serial_number
11517: from mtl_serial_numbers msn
11518: where msn.lpn_id = p_lpn_id
11519: and msn.current_organization_id = p_org_id
11520: and msn.lot_number = l_lot_rec.lot_number
11521: and msn.inventory_item_id = p_item_id
11577: -- Cases for JUST SERIAL CONTROLLED
11578: if (l_serial_interface_inserted <> TRUE ) then
11579:
11580: For l_serial_rec in ( select msn.serial_number
11581: from mtl_serial_numbers msn
11582: where msn.lpn_id = p_lpn_id
11583: and msn.current_organization_id = p_org_id
11584: and msn.inventory_item_id = p_item_id
11585: )
13729: l_serial_disc := 1;
13730:
13731: For l_test_serial_rec in (
13732: select serial_number
13733: from mtl_serial_numbers msn
13734: WHERE msn.lpn_id = l_lpn_contents.lpn_id
13735: AND msn.inventory_item_id = l_lpn_contents.inventory_item_id
13736: )
13737: Loop
13737: Loop
13738: BEGIN
13739: SELECT 1
13740: INTO l_serial_disc
13741: FROM mtl_serial_numbers_interface msni
13742: , rcv_transactions_interface rti
13743: where rti.lpn_group_id = p_lpn_group_id
13744: and (rti.lpn_id = l_lpn_contents.lpn_id
13745: OR rti.license_plate_number = l_lpn_contents.license_plate_number)
16908: IF (l_debug = 1) THEN
16909: print_debug('insert_msni_rowid:010: Entry Stamp :' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
16910: END If;
16911:
16912: INSERT INTO mtl_serial_numbers_interface
16913: (TRANSACTION_INTERFACE_ID,
16914: SOURCE_CODE,
16915: SOURCE_LINE_ID,
16916: LAST_UPDATE_DATE,
17095: ATTRIBUTE14,
17096: ATTRIBUTE15,
17097: PRODUCT_CODE,
17098: p_product_txn_id
17099: FROM mtl_serial_numbers_interface
17100: WHERE ROWID = p_rowid);
17101:
17102: IF (l_debug = 1) THEN
17103: print_debug('insert_msni_rowid:020: Exit Stamp :' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
17155: CURSOR msni_recs_with_rsl (l_old_rti_id NUMBER,l_shipment_line_id NUMBER) IS
17156: SELECT msni.fm_serial_number,
17157: msni.to_serial_number,
17158: msni.ROWID
17159: FROM mtl_serial_numbers_interface msni,rcv_serials_supply rss
17160: WHERE msni.product_code = 'RCV'
17161: AND msni.product_transaction_id = l_old_rti_id
17162: AND msni.fm_serial_number = rss.serial_num(+)
17163: AND NVL(rss.shipment_line_id,l_shipment_line_id) = l_shipment_line_id --10248599
17166: CURSOR msni_recs (l_old_rti_id NUMBER) IS
17167: SELECT msni.fm_serial_number,
17168: msni.to_serial_number,
17169: msni.ROWID
17170: FROM mtl_serial_numbers_interface msni
17171: WHERE msni.product_code = 'RCV'
17172: AND msni.product_transaction_id = l_old_rti_id;
17173: --13521315
17174: l_msni_rec msni_recs%ROWTYPE;
17176: CURSOR msni_recs_lotserial(l_serial_transaction_temp_id NUMBER) IS
17177: SELECT fm_serial_number,
17178: to_serial_number,
17179: ROWID
17180: FROM mtl_serial_numbers_interface
17181: WHERE transaction_interface_id = l_serial_transaction_temp_id;
17182:
17183: l_remaining_quantity NUMBER;
17184: l_primary_rem_qty NUMBER;
17258:
17259: BEGIN
17260: SELECT SUM(inv_serial_number_pub.get_serial_diff(fm_serial_number,to_serial_number))
17261: INTO l_total_serial_qty
17262: FROM mtl_serial_numbers_interface
17263: WHERE product_code = 'RCV'
17264: AND product_transaction_id = l_orig_interface_trx_id;
17265: EXCEPTION
17266: WHEN no_data_found THEN
17364: mtli.transaction_quantity)
17365: )
17366: = (SELECT SUM(inv_serial_number_pub.get_serial_diff(msni.fm_serial_number,
17367: msni.to_serial_number))
17368: FROM mtl_serial_numbers_interface msni
17369: WHERE msni.transaction_interface_id = mtli.serial_transaction_temp_id));
17370: EXCEPTION
17371: WHEN no_data_found then
17372: l_lot_ser_qty := 0;
17479:
17480: BEGIN
17481: SELECT SUM(inv_serial_number_pub.get_serial_diff(fm_serial_number,to_serial_number))
17482: INTO l_total_serial_qty
17483: FROM mtl_serial_numbers_interface
17484: WHERE product_code = 'RCV'
17485: AND product_transaction_id = l_orig_interface_trx_id;
17486: EXCEPTION
17487: WHEN no_data_found THEN
17536: get_primary_qty(l_to_organization_id,l_item_id,l_uom_code,
17537: mtli.transaction_quantity)
17538: ) = (SELECT SUM(inv_serial_number_pub.get_serial_diff(msni.fm_serial_number,
17539: msni.to_serial_number))
17540: FROM mtl_serial_numbers_interface msni
17541: WHERE msni.transaction_interface_id = mtli.serial_transaction_temp_id))
17542: AND (p_rti_tb(newrti).lot_number IS NULL OR (p_rti_tb(newrti).lot_number IS NOT NULL AND lot_number=p_rti_tb(newrti).lot_number)); -- for 13972742
17543: EXCEPTION
17544: WHEN no_data_found then
17749: IF (l_debug = 1) THEN
17750: print_debug('split_lot_serial:170: update msni ', 1);
17751: END If;
17752: -- update the msni with new rti id.
17753: UPDATE mtl_serial_numbers_interface
17754: SET product_transaction_id = p_rti_tb(newrti).new_interface_trx_id
17755: WHERE ROWID = l_msni_rec.ROWID;
17756:
17757: END LOOP; -- fetch msni_recs_lotserial
17837: IF (l_debug = 1) THEN
17838: print_debug('split_lot_serial:210: update msni ', 1);
17839: END If;
17840:
17841: UPDATE mtl_serial_numbers_interface
17842: SET product_transaction_id = p_rti_tb(newrti).new_interface_trx_id,
17843: transaction_interface_id = l_new_serial_txn_temp_id
17844: WHERE ROWID = l_msni_rec.ROWID;
17845:
17887: IF (l_debug = 1) THEN
17888: print_debug('split_lot_serial:230: update msni ', 1);
17889: END IF;
17890:
17891: UPDATE mtl_serial_numbers_interface
17892: SET fm_serial_number = l_new_from_ser_num
17893: WHERE ROWID = l_msni_rec.ROWID;
17894:
17895: l_remaining_serial_qty := 0;
17989: print_debug('split_lot_serial:240: update msni ', 1);
17990: print_debug('split_lot_serial:241: new rti '||p_rti_tb(newrti).new_interface_trx_id, 1);
17991: END If;
17992:
17993: UPDATE mtl_serial_numbers_interface
17994: SET product_transaction_id = p_rti_tb(newrti).new_interface_trx_id
17995: WHERE ROWID = l_msni_rec.ROWID;
17996:
17997: l_remaining_quantity := l_remaining_quantity -
18042: IF (l_debug = 1) THEN
18043: print_debug('split_lot_serial:260: update msni ', 1);
18044: END If;
18045:
18046: UPDATE mtl_serial_numbers_interface
18047: SET fm_serial_number = l_new_from_ser_num
18048: WHERE ROWID = l_msni_rec.ROWID;
18049:
18050: l_remaining_quantity := 0;
18176: p_serial_number IN VARCHAR2)
18177: RETURN BOOLEAN
18178: IS
18179: BEGIN
18180: UPDATE mtl_serial_numbers
18181: SET group_mark_id = -7937
18182: WHERE inventory_item_id = p_item_id
18183: AND serial_number = p_serial_number;
18184:
18497: END;
18498:
18499: IF (x_serial_number_control_code <> 1) THEN */ -- Bug 13886318 need to revert bug 12329840
18500:
18501: INSERT INTO mtl_serial_numbers_temp (TRANSACTION_TEMP_ID,
18502: LAST_UPDATE_DATE,
18503: LAST_UPDATED_BY,
18504: CREATION_DATE,
18505: CREATED_BY,
18675: CYCLES_SINCE_MARK,
18676: NUMBER_OF_REPAIRS,
18677: PRODUCT_CODE,
18678: product_transaction_id
18679: FROM mtl_serial_numbers_interface
18680: WHERE product_code = 'RCV'
18681: AND product_transaction_id = p_rti_id;
18682:
18683: IF (l_debug = 1) THEN
18691: /* Added By Bug#9713930 */
18692: UPDATE mtl_transaction_lots_temp mtlt
18693: set mtlt.serial_transaction_temp_id =
18694: (Select msni.transaction_interface_id
18695: from mtl_serial_numbers_interface msni where
18696: msni.product_code = 'RCV'
18697: AND msni.product_transaction_id = p_rti_id
18698: and rownum=1)
18699: where mtlt.product_code = 'RCV'
18704: DELETE FROM mtl_transaction_lots_interface
18705: WHERE product_code = 'RCV'
18706: AND product_transaction_id = p_rti_id;
18707:
18708: DELETE FROM mtl_serial_numbers_interface
18709: WHERE product_code = 'RCV'
18710: AND product_transaction_id = p_rti_id;
18711:
18712: EXCEPTION
18912: , attribute13
18913: , attribute14
18914: , attribute15
18915: , ROWID
18916: FROM MTL_SERIAL_NUMBERS_TEMP
18917: WHERE PRODUCT_CODE = 'RCV'
18918: AND PRODUCT_TRANSACTION_ID = L_RTI_ID;
18919:
18920: L_MSNT_REC C_MSNT%ROWTYPE;
18984: , attribute13
18985: , attribute14
18986: , attribute15
18987: , ROWID
18988: FROM MTL_SERIAL_NUMBERS_TEMP
18989: WHERE PRODUCT_CODE = 'RCV'
18990: AND transaction_temp_id = L_SERIAL_TXN_ID;
18991:
18992: l_rti_id NUMBER;
19418: SELECT '1'
19419: INTO l_dummy
19420: FROM dual
19421: WHERE exists (SELECT 1
19422: FROM mtl_serial_numbers_temp
19423: WHERE product_code = 'RCV'
19424: AND product_transaction_id = l_rti_id);
19425:
19426: --Fail transaction
20534: , l_curr_lot_num
20535: , l_curr_lpn_id
20536: , l_inspection_status
20537: , l_group_mark_id
20538: FROM MTL_SERIAL_NUMBERS
20539: WHERE SERIAL_NUMBER = l_serial_number
20540: AND inventory_item_id = l_item_id;
20541:
20542: l_serial_exists := 1;
21143: SELECT '1'
21144: INTO l_dummy
21145: FROM dual
21146: WHERE exists (SELECT '1'
21147: FROM mtl_serial_numbers
21148: WHERE inventory_item_id = l_item_id
21149: AND current_organization_id = l_org_id
21150: AND current_status IN (1, 4) --Do we need 4 here
21151: AND serial_number = l_serial_number
21632: , l_curr_lpn_id
21633: , l_inspection_status
21634: , l_group_mark_id
21635: , l_last_transaction_id --bug 5168883
21636: FROM MTL_SERIAL_NUMBERS
21637: WHERE SERIAL_NUMBER = l_serial_number
21638: AND inventory_item_id = l_item_id;
21639:
21640: l_serial_exists := 1;
21827: --update the serial row
21828: IF (l_debug = 1) THEN
21829: print_debug('VALIDATE_LOT_SERIAL_INFO: UPDATING LOT_NUMBER to null as destination org is not lot controlled', 1);
21830: END IF;
21831: update mtl_serial_numbers msn
21832: set lot_number = null
21833: where msn.serial_number = l_serial_number
21834: AND inventory_item_id = l_item_id;
21835: end if;
22252: SELECT '1'
22253: INTO l_dummy
22254: FROM dual
22255: WHERE exists (SELECT '1'
22256: FROM mtl_serial_numbers
22257: WHERE inventory_item_id = l_item_id
22258: AND current_organization_id = l_org_id
22259: AND current_status IN (1, 4) --Do we need 4 here
22260: AND serial_number = l_serial_number
23271: , l_curr_lot_num
23272: , l_curr_lpn_id
23273: , l_inspection_status
23274: , l_group_mark_id
23275: FROM MTL_SERIAL_NUMBERS
23276: WHERE SERIAL_NUMBER = l_serial_number
23277: AND inventory_item_id = l_item_id;
23278:
23279: l_serial_exists := 1;
23730: , l_curr_lot_num
23731: , l_curr_lpn_id
23732: , l_inspection_status
23733: , l_group_mark_id
23734: FROM MTL_SERIAL_NUMBERS
23735: WHERE SERIAL_NUMBER = l_serial_number
23736: AND inventory_item_id = l_item_id;
23737:
23738: l_serial_exists := 1;
25271: , l_curr_lot_num
25272: , l_curr_lpn_id
25273: , l_inspection_status
25274: , l_group_mark_id
25275: FROM MTL_SERIAL_NUMBERS
25276: WHERE SERIAL_NUMBER = l_serial_number
25277: AND inventory_item_id = l_item_id;
25278:
25279: l_serial_exists := 1;
26050: , l_curr_lot_num
26051: , l_curr_lpn_id
26052: , l_inspection_status
26053: , l_group_mark_id
26054: FROM MTL_SERIAL_NUMBERS
26055: WHERE SERIAL_NUMBER = l_serial_number
26056: AND inventory_item_id = l_item_id;
26057:
26058: l_serial_exists := 1;
27458: , l_curr_lot_num
27459: , l_curr_lpn_id
27460: , l_inspection_status
27461: , l_group_mark_id
27462: FROM MTL_SERIAL_NUMBERS
27463: WHERE SERIAL_NUMBER = l_serial_number
27464: AND inventory_item_id = l_item_id;
27465:
27466: l_serial_exists := 1;
28398: , l_curr_lot_num
28399: , l_curr_lpn_id
28400: , l_inspection_status
28401: , l_group_mark_id
28402: FROM MTL_SERIAL_NUMBERS
28403: WHERE SERIAL_NUMBER = l_serial_number
28404: AND inventory_item_id = l_item_id;
28405:
28406: l_serial_exists := 1;