[Home] [Help]
155: MSN.ATTRIBUTE14),
156: NVL(MSNI.ATTRIBUTE15,
157: MSN.ATTRIBUTE15)
158: FROM
159: MTL_SERIAL_NUMBERS_INTERFACE MSNI,
160: MTL_SERIAL_NUMBERS MSN
161: WHERE
162: MSNI.TRANSACTION_INTERFACE_ID = :B_PARENT_ID AND
163: MSNI.FM_SERIAL_NUMBER = :B_FM_SERIAL_NUMBER AND
4879: THEN
4880: BEGIN
4881: SELECT 1
4882: into l_tnum
4883: FROM MTL_SERIAL_NUMBERS_INTERFACE
4884: WHERE TRANSACTION_INTERFACE_ID = l_sertempid
4885: AND ROWNUM < 2;
4886: EXCEPTION
4887: WHEN NO_DATA_FOUND THEN
4914: END;
4915:
4916: ELSE
4917: IF (l_sertempid IS NOT NULL) THEN
4918: DELETE FROM MTL_SERIAL_NUMBERS_INTERFACE
4919: WHERE TRANSACTION_INTERFACE_ID = l_sertempid;
4920: END IF;
4921:
4922: END IF;
5964: *
5965: * validate_lot_serial_for_rcpt()
5966: * SDPAUL Bug# 5710830
5967: * This private procedure is used to validate a set of
5968: * MTL_TRANSACTION_LOTS_INTERFACE and MTL_SERIAL_NUMBERS_INTERFACE records
5969: * and inserts them into the corresponding master tables.
5970: * These validations are only needed for Receipt into stores transaction -> 27
5971: * and for the transaction sources -> 3,6 and 13.
5972: *
6077:
6078: CURSOR cur_MSNI(interface_id NUMBER) IS
6079: SELECT FM_SERIAL_NUMBER
6080: , TO_SERIAL_NUMBER
6081: FROM MTL_SERIAL_NUMBERS_INTERFACE
6082: WHERE TRANSACTION_INTERFACE_ID = interface_id;
6083:
6084: -- PL/SQL table to store lot attributes
6085: l_attributes_tbl inv_lot_api_pub.char_tbl;
8044: AND ABS(nvl(mtli.primary_quantity, inv_convert.inv_um_convert
8045: (l_itemid,5,mtli.transaction_quantity,l_trxuom,l_priuom,'','')))
8046: = (SELECT SUM(get_serial_diff_wrp
8047: (msni.fm_serial_number,nvl(msni.to_serial_number,msni.fm_serial_number)))
8048: FROM mtl_serial_numbers_interface msni
8049: WHERE msni.transaction_interface_id
8050: = mtli.serial_transaction_temp_id);
8051: EXCEPTION
8052: WHEN others THEN
8092:
8093: SELECT SUM(get_serial_diff_wrp
8094: (fm_serial_number,NVL(to_serial_number,fm_serial_number)))
8095: INTO l_lot_ser_qty
8096: FROM mtl_serial_numbers_interface msni
8097: WHERE msni.transaction_interface_id =l_intid;
8098: EXCEPTION
8099: WHEN others THEN
8100: IF (l_debug = 1) THEN
8279: ELSE
8280: BEGIN
8281: SELECT 1
8282: into l_tnum
8283: FROM MTL_SERIAL_NUMBERS_INTERFACE
8284: WHERE TRANSACTION_INTERFACE_ID = l_intid
8285: AND ROWNUM < 2;
8286: EXCEPTION
8287: WHEN NO_DATA_FOUND THEN
8293: END;
8294: END IF;--l_intid is null
8295: ELSE
8296: IF (l_intid IS NOT NULL) THEN
8297: DELETE FROM MTL_SERIAL_NUMBERS_INTERFACE
8298: WHERE TRANSACTION_INTERFACE_ID = l_intid;
8299: END IF;
8300: END IF; --actions for serials.
8301: END IF;--if check actions for lots
8917: AND PROCESS_FLAG = 1
8918: AND (
8919: EXISTS
8920: (
8921: select 1 FROM mtl_serial_numbers msn, mtl_serial_numbers_interface msni,
8922: mtl_transactions_interface mti, mtl_material_transactions_temp mmtt
8923: WHERE msn.serial_number BETWEEN msni.fm_serial_number AND msni.to_serial_number
8924: AND Length(msn.serial_number) = Length(msni.fm_serial_number)
8925: AND msn.current_organization_id = mti.organization_id
8934: )
8935: OR EXISTS
8936: (
8937: select msn.serial_number, msn.group_mark_id,mti.transaction_interface_id
8938: FROM mtl_serial_numbers msn, mtl_serial_numbers_interface msni,
8939: mtl_transactions_interface mti,mtl_transaction_lots_interface mtli,
8940: mtl_material_transactions_temp mmtt
8941: WHERE msn.serial_number BETWEEN msni.fm_serial_number AND msni.to_serial_number
8942: AND Length(msn.serial_number) = Length(msni.fm_serial_number)
10915: parent_item_id, --R12 Genealogy enhancements
10916: parent_object_type2, --R12 Genealogy enhancements
10917: parent_object_id2, --R12 Genealogy enhancements
10918: parent_object_number2 --R12 Genealogy enhancements
10919: FROM MTL_SERIAL_NUMBERS_INTERFACE
10920: WHERE (TRANSACTION_INTERFACE_ID IN (
10921: SELECT TRANSACTION_INTERFACE_ID
10922: FROM MTL_TRANSACTIONS_INTERFACE MTI
10923: WHERE TRANSACTION_HEADER_ID = p_header_id
11273: type and length of Serial Attributes, as this is being done in seperate procedure
11274: get_serial_attr_table. */
11275:
11276: /*FOR l_lot_ser_column_csr IN
11277: serial_column_csr ('MTL_SERIAL_NUMBERS_INTERFACE')
11278: LOOP
11279: l_column_idx := l_column_idx + 1;
11280: l_lot_ser_attr_tbl (l_column_idx).column_name :=
11281: l_lot_ser_column_csr.column_name;
11325: -- in the system
11326: /*
11327: l_select_stmt :=
11328: l_select_stmt
11329: || ' from mtl_serial_numbers_interface msni,'
11330: || ' mtl_serial_numbers msn,'
11331: || ' mtl_transaction_lots_interface mtli,'
11332: || ' mtl_transactions_interface mti'
11333: || ' where mti.parent_id = :b_parent_id'
11877: , cycles_since_visit
11878: , time_since_mark
11879: , cycles_since_mark
11880: , number_of_repairs
11881: FROM mtl_serial_numbers_interface msni
11882: WHERE transaction_interface_id = l_serial_transaction_temp_id;
11883:
11884: CURSOR msn_serial_attributes_csr (
11885: l_serial_number IN VARCHAR2
15249: return -1;
15250: ELSE
15251: --delete from mti/mtli/msni
15252:
15253: DELETE FROM MTL_SERIAL_NUMBERS_INTERFACE
15254: WHERE TRANSACTION_INTERFACE_ID
15255: IN(
15256: SELECT TRANSACTION_INTERFACE_ID
15257: FROM MTL_TRANSACTIONS_INTERFACE
16176: ,parent_object_number = l_object_number
16177: ,parent_object_number2 = l_object_number2
16178: WHERE ROWID = p_rowid;
16179: ELSIF p_table = 'MSNI' THEN
16180: UPDATE MTL_SERIAL_NUMBERS_INTERFACE MSNI
16181: SET parent_object_id = l_object_id
16182: ,parent_object_id2 = l_object_id2
16183: ,parent_object_number = l_object_number
16184: ,parent_object_number2 = l_object_number2
16213: ,PARENT_OBJECT_ID2
16214: ,PARENT_OBJECT_NUMBER2
16215: ,PARENT_ITEM_ID
16216: ,ROWID
16217: FROM MTL_SERIAL_NUMBERS_INTERFACE
16218: WHERE TRANSACTION_INTERFACE_ID = p_interface_id;
16219:
16220: BEGIN
16221: x_return_status := lg_ret_sts_success;