[Home] [Help]
2758: FOR i in 1..p_serial_number_tbl.count LOOP
2759: BEGIN
2760: SELECT reservation_id, serial_number
2761: INTO x_serial_number_tbl(l_index).reservation_id, x_serial_number_tbl(l_index).serial_number
2762: FROM mtl_serial_numbers
2763: WHERE serial_number = p_serial_number_tbl(i).serial_number
2764: AND inventory_item_id = p_serial_number_tbl(i).inventory_item_id
2765: AND reservation_id is not null;
2766: l_index := l_index + 1;
2908: FOR i in 1..p_serial_number_tbl.count LOOP
2909: BEGIN
2910: SELECT reservation_id, serial_number
2911: INTO x_serial_number_tbl(l_index).reservation_id, x_serial_number_tbl(l_index).serial_number
2912: FROM mtl_serial_numbers
2913: WHERE serial_number = p_serial_number_tbl(i).serial_number
2914: AND inventory_item_id = p_serial_number_tbl(i).inventory_item_id
2915: AND reservation_id is not null;
2916:
7930: IF (l_debug=1) THEN
7931: debug_print('Count. i: '|| i);
7932: END IF;
7933: SELECT reservation_id, serial_number bulk collect INTO
7934: -- l_serial_number_table FROM mtl_serial_numbers
7935: /* Bug# 13479815: replaced l_serial_number_table with l_serial_number_table_temp */
7936: l_serial_number_table_temp FROM mtl_serial_numbers
7937: WHERE reservation_id = l_mtl_reservation_tbl(i).reservation_id;
7938:
7932: END IF;
7933: SELECT reservation_id, serial_number bulk collect INTO
7934: -- l_serial_number_table FROM mtl_serial_numbers
7935: /* Bug# 13479815: replaced l_serial_number_table with l_serial_number_table_temp */
7936: l_serial_number_table_temp FROM mtl_serial_numbers
7937: WHERE reservation_id = l_mtl_reservation_tbl(i).reservation_id;
7938:
7939: IF l_serial_number_table_temp.Count > 0 THEN --Bug# 13557393
7940: -- Bug# 13479815 Start : populating l_serial_number_table with all the serial number details
7973: debug_print('Count. Serial index: '|| l_serial_table_index);
7974: END IF;
7975:
7976: SELECT reservation_id INTO l_reservation_id FROM
7977: mtl_serial_numbers WHERE serial_number = l_serial_number AND
7978: current_organization_id = l_mtl_reservation_tbl(l_serial_table_index).organization_id AND
7979: inventory_item_id = l_mtl_reservation_tbl(l_serial_table_index).inventory_item_id;
7980:
7981: EXCEPTION
9582: END IF;
9583:
9584: BEGIN
9585: SELECT group_mark_id INTO l_group_mark_id FROM
9586: mtl_serial_numbers WHERE
9587: serial_number = l_serial_number(l_serial_index).serial_number AND
9588: inventory_item_id = l_rsv_rec.inventory_item_id AND
9589: current_organization_id = l_rsv_rec.organization_id;
9590: EXCEPTION
9607: RAISE fnd_api.g_exc_error;
9608: END IF;
9609:
9610: BEGIN
9611: UPDATE mtl_serial_numbers SET reservation_id =
9612: l_reservation_id, group_mark_id =
9613: l_reservation_id WHERE
9614: serial_number = l_serial_number(l_serial_index).serial_number AND
9615: inventory_item_id = l_rsv_rec.inventory_item_id AND
11210: END IF;
11211: BEGIN
11212: SELECT inventory_item_id, serial_number bulk collect INTO
11213: l_serial_number_table FROM
11214: mtl_serial_numbers WHERE reservation_id =
11215: l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
11216: l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
11217: EXCEPTION
11218: WHEN no_data_found THEN
11266:
11267: FOR i IN 1..l_serials_tobe_unreserved
11268: LOOP
11269: BEGIN
11270: UPDATE mtl_serial_numbers SET reservation_id = NULL,
11271: group_mark_id = NULL, line_mark_id = NULL,
11272: lot_line_mark_id = NULL WHERE
11273: serial_number = l_serial_number_table(i).serial_number AND
11274: current_organization_id = l_orig_rsv_tbl(1).organization_id AND
11309: debug_print('item' ||
11310: l_orig_rsv_tbl(1).inventory_item_id);
11311: END IF;
11312: BEGIN
11313: UPDATE mtl_serial_numbers SET reservation_id = l_orig_rsv_tbl(1).reservation_id,
11314: group_mark_id = l_orig_rsv_tbl(1).reservation_id WHERE
11315: serial_number = l_serial_number_table(l_serial_index).serial_number
11316: AND current_organization_id = l_orig_rsv_tbl(1).organization_id
11317: AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
11348: LOOP
11349: BEGIN
11350: SELECT reservation_id, group_mark_id INTO
11351: l_reservation_id,l_group_mark_id
11352: FROM mtl_serial_numbers WHERE
11353: serial_number = p_to_serial_number(i).serial_number AND
11354: current_organization_id = l_orig_rsv_tbl(1).organization_id AND
11355: inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
11356: EXCEPTION
11394: FOR i IN
11395: p_original_serial_number.first..p_original_serial_number.last
11396: LOOP
11397: BEGIN
11398: UPDATE mtl_serial_numbers SET reservation_id = NULL,
11399: group_mark_id = NULL, line_mark_id = NULL,
11400: lot_line_mark_id = NULL WHERE
11401: serial_number = p_original_serial_number(i).serial_number AND
11402: current_organization_id = l_orig_rsv_tbl(1).organization_id AND
11425: l_orig_rsv_tbl(1).organization_id);
11426: debug_print('inventory_item_id = ' || l_orig_rsv_tbl(1).inventory_item_id);
11427: END IF;
11428: BEGIN
11429: UPDATE mtl_serial_numbers SET reservation_id = l_orig_rsv_tbl(1).reservation_id,
11430: group_mark_id = l_orig_rsv_tbl(1).reservation_id WHERE
11431: serial_number = p_to_serial_number(i).serial_number AND
11432: current_organization_id = l_orig_rsv_tbl(1).organization_id AND
11433: inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
11452: debug_print('inventory_item_id = ' || l_orig_rsv_tbl(1).inventory_item_id);
11453: END IF;
11454: BEGIN
11455: SELECT COUNT(1) INTO l_total_serials_reserved FROM
11456: mtl_serial_numbers WHERE reservation_id =
11457: l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
11458: l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
11459: EXCEPTION
11460: WHEN no_data_found THEN
11478:
11479: BEGIN
11480: SELECT inventory_item_id, serial_number bulk collect INTO
11481: l_serial_number_table FROM
11482: mtl_serial_numbers WHERE reservation_id =
11483: l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
11484: l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
11485: EXCEPTION
11486: WHEN no_data_found THEN
11495: IF (l_debug = 1) THEN
11496: debug_print('Serial being unreserved' || l_serial_number_table(i).serial_number);
11497: END IF;
11498: BEGIN
11499: UPDATE mtl_serial_numbers SET reservation_id = NULL,
11500: group_mark_id = NULL, line_mark_id = NULL,
11501: lot_line_mark_id = NULL WHERE
11502: serial_number = l_serial_number_table(i).serial_number AND
11503: current_organization_id = l_orig_rsv_tbl(1).organization_id AND
12202: END IF;
12203: -- End changes for bug 3827307
12204:
12205: /*** {{ R12 Enhanced reservations code changes ***/
12206: -- update the mtl_serial_numbers, null out the reservation_id
12207: -- and group_mark_id of the reserved serials before delete the
12208: -- reservation. There is no need to update the serial_reservation_quantity
12209: -- because the reservation record will be deleted.
12210: BEGIN
12207: -- and group_mark_id of the reserved serials before delete the
12208: -- reservation. There is no need to update the serial_reservation_quantity
12209: -- because the reservation record will be deleted.
12210: BEGIN
12211: update mtl_serial_numbers
12212: set reservation_id = NULL,
12213: group_mark_id = NULL,
12214: line_mark_id = NULL,
12215: lot_line_mark_id = NULL
12332: -- INVCONV END
12333:
12334: /*** {{ R12 Enhanced reservations code changes ***/
12335: -- if serial record is not empty, then unmark the group_mark_id and
12336: -- reservation_id in mtl_serial_numbers of the serial numbers pass in the record.
12337: -- also update the serial_reservation_quantity in the mtl_reservations.
12338: -- if serial record is empty, need to get the serials with reservation_id and
12339: -- unmark the serial until the serial_reservation_quantity = primary_reservation_quantity
12340: IF (p_original_serial_number.COUNT > 0) THEN
12348: p_original_serial_number(i).serial_number);
12349: debug_print('item ' || l_tmp_rsv_tbl(1).inventory_item_id);
12350: END IF;
12351: BEGIN
12352: UPDATE mtl_serial_numbers
12353: SET reservation_id = NULL,
12354: group_mark_id = NULL, line_mark_id = NULL,
12355: lot_line_mark_id = NULL
12356: WHERE serial_number = p_original_serial_number(i).serial_number
12400:
12401: BEGIN
12402: SELECT inventory_item_id,serial_number BULK COLLECT
12403: INTO l_serial_number_table
12404: FROM mtl_serial_numbers
12405: WHERE reservation_id = l_reservation_id;
12406: EXCEPTION
12407: WHEN no_data_found THEN
12408: IF (l_debug = 1) THEN
12435: debug_print('inside relieve. serial ' ||
12436: l_serial_number_table(i).serial_number);
12437: debug_print('inside relieve. item ' || l_serial_number_table(i).serial_number);
12438: END IF;
12439: UPDATE mtl_serial_numbers
12440: SET reservation_id = NULL,
12441: group_mark_id = NULL, line_mark_id = NULL,
12442: lot_line_mark_id = NULL
12443: WHERE serial_number = l_serial_number_table(i).serial_number
12480:
12481: BEGIN
12482: SELECT inventory_item_id, serial_number BULK COLLECT
12483: INTO l_serial_number_table
12484: FROM mtl_serial_numbers
12485: WHERE reservation_id = l_reservation_id;
12486: EXCEPTION
12487: WHEN no_data_found THEN
12488: IF (l_debug = 1) THEN
12503: END IF;
12504:
12505: FOR i in 1..l_count_to_unrsv_serials LOOP
12506: BEGIN
12507: UPDATE mtl_serial_numbers
12508: SET reservation_id = NULL,
12509: group_mark_id = NULL, line_mark_id = NULL,
12510: lot_line_mark_id = NULL
12511: WHERE serial_number = l_serial_number_table(i).serial_number
12880: RAISE fnd_api.g_exc_unexpected_error;
12881: END IF;
12882:
12883: /*** {{ R12 Enhanced reservations code changes ***/
12884: -- update the mtl_serial_numbers, null out the reservation_id
12885: -- and group_mark_id of the reserved serials before delete the
12886: -- reservation. There is no need to update the serial_reservation_quantity
12887: -- because the reservation record will be deleted.
12888: BEGIN
12885: -- and group_mark_id of the reserved serials before delete the
12886: -- reservation. There is no need to update the serial_reservation_quantity
12887: -- because the reservation record will be deleted.
12888: BEGIN
12889: update mtl_serial_numbers
12890: set reservation_id = NULL,
12891: group_mark_id = NULL, line_mark_id = NULL,
12892: lot_line_mark_id = NULL
12893: where reservation_id = l_reservation_id;
15311:
15312: BEGIN
15313: SELECT inventory_item_id, serial_number bulk collect INTO
15314: l_serial_number_table FROM
15315: mtl_serial_numbers WHERE reservation_id =
15316: l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
15317: l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
15318: EXCEPTION
15319: WHEN no_data_found THEN
15448: -- reservation id are populated.
15449: FOR l_serial_index IN l_serial_number_table.first..l_serial_number_table.last
15450: LOOP
15451: BEGIN
15452: UPDATE mtl_serial_numbers SET reservation_id = l_to_reservation_id,
15453: group_mark_id = l_to_reservation_id WHERE
15454: serial_number = l_serial_number_table(l_serial_index).serial_number AND
15455: inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id AND
15456: current_organization_id = l_orig_rsv_tbl(1).organization_id;
15507:
15508: FOR i IN 1..l_serials_tobe_unreserved
15509: LOOP
15510: BEGIN
15511: UPDATE mtl_serial_numbers SET reservation_id = l_to_reservation_id,
15512: group_mark_id = l_to_reservation_id WHERE
15513: serial_number = l_serial_number_table(i).serial_number AND
15514: inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id AND
15515: current_organization_id = l_orig_rsv_tbl(1).organization_id;
15574: -- check to see if we are transferring more than the
15575: -- reserved qty
15576: BEGIN
15577: SELECT COUNT(1) INTO l_total_to_serials_reserved FROM
15578: mtl_serial_numbers WHERE reservation_id =
15579: l_to_reservation_id AND current_organization_id =
15580: l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
15581: EXCEPTION
15582: WHEN no_data_found THEN
15621: -- The serial has to be reserved to the from record
15622: FOR i IN p_original_serial_number.first..p_original_serial_number.last
15623: LOOP
15624: BEGIN
15625: SELECT reservation_id INTO l_from_reservation_id FROM mtl_serial_numbers WHERE
15626: serial_number = p_original_serial_number(i).serial_number AND
15627: current_organization_id = l_orig_rsv_tbl(1).organization_id AND
15628: inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
15629: EXCEPTION
15644:
15645: IF (l_total_serials_reserved > 0) THEN
15646: -- unreserve the passed serials.
15647: BEGIN
15648: UPDATE mtl_serial_numbers SET reservation_id = NULL,
15649: group_mark_id = NULL, line_mark_id = NULL,
15650: lot_line_mark_id = NULL WHERE
15651: serial_number = p_original_serial_number(i).serial_number AND
15652: current_organization_id = l_orig_rsv_tbl(1).organization_id AND
15672:
15673: BEGIN
15674: SELECT inventory_item_id, serial_number bulk collect INTO
15675: l_validate_serial_number_table FROM
15676: mtl_serial_numbers WHERE reservation_id =
15677: l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
15678: l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
15679: EXCEPTION
15680: WHEN no_data_found THEN
15717: END IF;
15718: END IF;
15719:
15720: BEGIN
15721: UPDATE mtl_serial_numbers SET reservation_id = l_to_reservation_id,
15722: group_mark_id = l_to_reservation_id WHERE
15723: reservation_id = l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
15724: l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
15725:
15735:
15736: -- check to see if we have reserved more than the to_record
15737: BEGIN
15738: SELECT COUNT(1) INTO l_total_to_serials_reserved FROM
15739: mtl_serial_numbers WHERE reservation_id =
15740: l_to_reservation_id AND current_organization_id =
15741: l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
15742: EXCEPTION
15743: WHEN no_data_found THEN
15782: END IF;
15783: BEGIN
15784: SELECT inventory_item_id, serial_number bulk collect INTO
15785: l_validate_serial_number_table FROM
15786: mtl_serial_numbers WHERE reservation_id =
15787: l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
15788: l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
15789: EXCEPTION
15790: WHEN no_data_found THEN
15834:
15835: FOR i IN 1..l_serials_tobe_unreserved
15836: LOOP
15837: BEGIN
15838: UPDATE mtl_serial_numbers SET reservation_id = l_to_reservation_id,
15839: group_mark_id = l_to_reservation_id WHERE
15840: serial_number = l_validate_serial_number_table(i).serial_number AND
15841: inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id AND
15842: current_organization_id = l_orig_rsv_tbl(1).organization_id;
15857: END LOOP;
15858:
15859: BEGIN
15860: SELECT COUNT(1) INTO l_total_to_serials_reserved FROM
15861: mtl_serial_numbers WHERE reservation_id =
15862: l_to_reservation_id AND current_organization_id =
15863: l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
15864: EXCEPTION
15865: WHEN no_data_found THEN
15933: END IF;
15934: BEGIN
15935: SELECT inventory_item_id, serial_number bulk collect INTO
15936: l_validate_serial_number_table FROM
15937: mtl_serial_numbers WHERE reservation_id =
15938: l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
15939: l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
15940: EXCEPTION
15941: WHEN no_data_found THEN
16032: debug_print('Processing serial number' || p_to_serial_number(i).serial_number);
16033: END IF;
16034:
16035: SELECT reservation_id, group_mark_id INTO
16036: l_from_reservation_id, l_group_mark_id FROM mtl_serial_numbers WHERE
16037: serial_number = p_to_serial_number(i).serial_number AND
16038: current_organization_id = l_orig_rsv_tbl(1).organization_id AND
16039: inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
16040: EXCEPTION
16068: RAISE fnd_api.g_exc_error;
16069: END IF;
16070:
16071: BEGIN
16072: UPDATE mtl_serial_numbers SET reservation_id = l_to_reservation_id,
16073: group_mark_id = l_to_reservation_id WHERE
16074: serial_number = p_to_serial_number(i).serial_number AND
16075: current_organization_id = l_orig_rsv_tbl(1).organization_id AND
16076: inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
16126: -- reservation id are populated.
16127: FOR l_serial_index IN l_serial_number_table.first..l_serial_number_table.last
16128: LOOP
16129: BEGIN
16130: UPDATE mtl_serial_numbers SET reservation_id = l_to_reservation_id,
16131: group_mark_id = l_to_reservation_id WHERE
16132: serial_number = l_serial_number_table(l_serial_index).serial_number AND
16133: inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id AND
16134: current_organization_id = l_orig_rsv_tbl(1).organization_id;
16149: END LOOP;
16150:
16151: BEGIN
16152: SELECT COUNT(1) INTO l_total_to_serials_reserved FROM
16153: mtl_serial_numbers WHERE reservation_id =
16154: l_to_reservation_id AND current_organization_id =
16155: l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
16156: EXCEPTION
16157: WHEN no_data_found THEN
16238: -- the serial reservation quantity. check to see if the
16239: -- count exceeds
16240: BEGIN
16241: SELECT COUNT(1) INTO l_total_to_serials_reserved FROM
16242: mtl_serial_numbers WHERE reservation_id =
16243: l_orig_rsv_tbl(1).reservation_id AND current_organization_id = l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
16244: EXCEPTION
16245: WHEN no_data_found THEN
16246: IF l_debug=1 THEN
16295: IF (l_total_serials_reserved > 0) THEN
16296: BEGIN
16297: SELECT inventory_item_id, serial_number bulk collect INTO
16298: l_validate_serial_number_table FROM
16299: mtl_serial_numbers WHERE reservation_id =
16300: l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
16301: l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
16302: EXCEPTION
16303: WHEN no_data_found THEN
16347:
16348: FOR i IN 1..l_serials_tobe_unreserved
16349: LOOP
16350: BEGIN
16351: UPDATE mtl_serial_numbers SET
16352: reservation_id = l_to_reservation_id,
16353: group_mark_id = l_to_reservation_id WHERE
16354: serial_number = l_validate_serial_number_table(i).serial_number AND
16355: inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id AND
16373: END IF;
16374:
16375: BEGIN
16376: SELECT COUNT(1) INTO l_total_to_serials_reserved FROM
16377: mtl_serial_numbers WHERE reservation_id =
16378: l_to_reservation_id AND current_organization_id =
16379: l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
16380: EXCEPTION
16381: WHEN no_data_found THEN
16406: END;
16407:
16408: BEGIN
16409: SELECT COUNT(1) INTO l_total_from_serials_reserved FROM
16410: mtl_serial_numbers WHERE reservation_id =
16411: l_orig_rsv_tbl(1).reservation_id AND
16412: current_organization_id = l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
16413: EXCEPTION
16414: WHEN no_data_found THEN
16452: -- The serial has to be reserved to the from record
16453: FOR i IN p_original_serial_number.first..p_original_serial_number.last
16454: LOOP
16455: BEGIN
16456: SELECT reservation_id INTO l_from_reservation_id FROM mtl_serial_numbers WHERE
16457: serial_number = p_original_serial_number(i).serial_number AND
16458: current_organization_id = l_orig_rsv_tbl(1).organization_id AND
16459: inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
16460: EXCEPTION
16474: END IF;
16475:
16476: -- unreserve the passed serials.
16477: BEGIN
16478: UPDATE mtl_serial_numbers SET reservation_id = NULL,
16479: group_mark_id = NULL, line_mark_id = NULL,
16480: lot_line_mark_id = NULL WHERE
16481: serial_number = p_original_serial_number(i).serial_number AND
16482: current_organization_id = l_orig_rsv_tbl(1).organization_id AND
16498: FOR i IN p_to_serial_number.first..p_to_serial_number.last
16499: LOOP
16500: BEGIN
16501: SELECT reservation_id, group_mark_id INTO
16502: l_from_reservation_id, l_group_mark_id FROM mtl_serial_numbers WHERE
16503: serial_number = p_to_serial_number(i).serial_number AND
16504: current_organization_id = l_orig_rsv_tbl(1).organization_id AND
16505: inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
16506: EXCEPTION
16529: RAISE fnd_api.g_exc_error;
16530: END IF;
16531:
16532: BEGIN
16533: UPDATE mtl_serial_numbers SET reservation_id = l_to_reservation_id,
16534: group_mark_id = l_to_reservation_id WHERE
16535: serial_number = p_to_serial_number(i).serial_number AND
16536: current_organization_id = l_orig_rsv_tbl(1).organization_id AND
16537: inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
16555: IF (l_total_serials_reserved > 0) THEN
16556: BEGIN
16557: SELECT inventory_item_id, serial_number bulk collect INTO
16558: l_validate_serial_number_table FROM
16559: mtl_serial_numbers WHERE reservation_id =
16560: l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
16561: l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
16562: EXCEPTION
16563: WHEN no_data_found THEN
16596: RAISE fnd_api.g_exc_unexpected_error;
16597: END IF;
16598:
16599: BEGIN
16600: UPDATE mtl_serial_numbers SET reservation_id = l_to_reservation_id,
16601: group_mark_id = l_to_reservation_id WHERE
16602: reservation_id = l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
16603: l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
16604:
16615: END IF;
16616:
16617: BEGIN
16618: SELECT COUNT(1) INTO l_total_to_serials_reserved FROM
16619: mtl_serial_numbers WHERE reservation_id =
16620: l_to_reservation_id AND current_organization_id =
16621: l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
16622: EXCEPTION
16623: WHEN no_data_found THEN
16660: IF (l_total_serials_reserved > 0) THEN
16661: BEGIN
16662: SELECT inventory_item_id, serial_number bulk collect INTO
16663: l_validate_serial_number_table FROM
16664: mtl_serial_numbers WHERE reservation_id =
16665: l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
16666: l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
16667: EXCEPTION
16668: WHEN no_data_found THEN
16704: END IF;
16705:
16706: BEGIN
16707: SELECT COUNT(1) INTO l_total_to_serials_reserved FROM
16708: mtl_serial_numbers WHERE reservation_id =
16709: l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
16710: l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
16711: EXCEPTION
16712: WHEN no_data_found THEN
16747: IF (l_total_serials_reserved > 0) THEN
16748: BEGIN
16749: SELECT inventory_item_id, serial_number bulk collect INTO
16750: l_validate_serial_number_table FROM
16751: mtl_serial_numbers WHERE reservation_id =
16752: l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
16753: l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
16754: EXCEPTION
16755: WHEN no_data_found THEN
16799:
16800: FOR i IN 1..l_serials_tobe_unreserved
16801: LOOP
16802: BEGIN
16803: UPDATE mtl_serial_numbers SET reservation_id = l_to_reservation_id,
16804: group_mark_id = l_to_reservation_id WHERE
16805: serial_number = l_validate_serial_number_table(i).serial_number AND
16806: inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id AND
16807: current_organization_id = l_orig_rsv_tbl(1).organization_id;
16825: END IF;
16826:
16827: BEGIN
16828: SELECT COUNT(1) INTO l_total_to_serials_reserved FROM
16829: mtl_serial_numbers WHERE reservation_id =
16830: l_to_reservation_id AND current_organization_id =
16831: l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
16832: EXCEPTION
16833: WHEN no_data_found THEN
16857: END;
16858:
16859: BEGIN
16860: SELECT COUNT(1) INTO l_total_from_serials_reserved FROM
16861: mtl_serial_numbers WHERE reservation_id =
16862: l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
16863: l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
16864: EXCEPTION
16865: WHEN no_data_found THEN
18359: ,v_Inventory_Item_Id Number
18360: ,v_Organization_Id Number ) IS
18361:
18362: Select Reservation_ID
18363: From Mtl_Serial_Numbers
18364: Where Inventory_Item_Id = v_Inventory_Item_Id
18365: and current_organization_id = v_Organization_id
18366: and Serial_Number = v_Serial_Number;
18367: BEGIN
18378: END IF;
18379:
18380: l_debug := g_debug;
18381:
18382: -- Check if reservation id parameter has any value if not then get value from mtl_serial_numbers table.
18383: If p_original_Reservation_Id IS Null THEN
18384: IF (l_debug = 1) THEN
18385: debug_print('reservation_id is not passed');
18386: END IF;
18518: SELECT msn.reservation_id,
18519: msn.serial_number,
18520: msn.inventory_item_id
18521: FROM mtl_reservations mr,
18522: mtl_serial_numbers msn
18523: WHERE mr.organization_id = p_organization_id
18524: AND mr.inventory_item_id = p_inventory_item_id
18525: AND mr.reservation_id = msn.reservation_id
18526: AND mr.lpn_id = null
18536: SELECT msn.reservation_id,
18537: msn.serial_number,
18538: msn.inventory_item_id
18539: FROM mtl_reservations mr,
18540: mtl_serial_numbers msn
18541: WHERE mr.organization_id = p_organization_id
18542: AND mr.reservation_id = msn.reservation_id
18543: AND mr.lpn_id = null
18544: AND msn.lpn_id IN (SELECT lpn_id
18553: SELECT msn.reservation_id,
18554: msn.serial_number,
18555: msn.inventory_item_id
18556: FROM mtl_reservations mr,
18557: mtl_serial_numbers msn
18558: WHERE mr.organization_id = p_organization_id
18559: AND mr.inventory_item_id = p_inventory_item_id
18560: AND mr.reservation_id = msn.reservation_id
18561: AND mr.lpn_id = null
18569: SELECT msn.reservation_id,
18570: msn.serial_number,
18571: msn.inventory_item_id
18572: FROM mtl_reservations mr,
18573: mtl_serial_numbers msn
18574: WHERE mr.organization_id = p_organization_id
18575: AND mr.reservation_id = msn.reservation_id
18576: AND mr.lpn_id = null
18577: AND msn.lpn_id = p_lpn_id