[Home] [Help]
102: TYPE line_tab IS TABLE OF WIP_LINES.LINE_CODE%TYPE INDEX BY BINARY_INTEGER;
103: TYPE dept_tab IS TABLE OF BOM_DEPARTMENTS.DEPARTMENT_CODE%TYPE INDEX BY BINARY_INTEGER;
104: TYPE schedule_tab IS TABLE OF WIP_ENTITIES.WIP_ENTITY_NAME%TYPE INDEX BY BINARY_INTEGER;
105:
106: TYPE ser_num_tab IS TABLE OF mtl_serial_numbers.serial_number%TYPE
107: INDEX BY BINARY_INTEGER;
108: TYPE ser_mark_tab IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
109:
110: TYPE mol_in_rec IS RECORD(
9501:
9502: BEGIN
9503: SELECT serial_number
9504: bulk collect INTO l_serial_numbers
9505: FROM mtl_serial_numbers
9506: WHERE lpn_id = p_from_lpn_id
9507: AND inventory_item_id = l_wlpnc_rec.inventory_item_id
9508: AND Nvl(lot_number, '@@@') = Nvl(l_wlpnc_rec.lot_number, '@@@')
9509: AND Nvl(revision, '@@@') = Nvl(l_wlpnc_rec.revision,'@@@');
9509: AND Nvl(revision, '@@@') = Nvl(l_wlpnc_rec.revision,'@@@');
9510: EXCEPTION
9511: WHEN OTHERS THEN
9512: IF (l_debug = 1) THEN
9513: debug('Error retrieving entries from MTL_SERIAL_NUMBERS',
9514: 'transfer_contents', 9);
9515: END IF;
9516: RAISE fnd_api.g_exc_error;
9517: END;
9944:
9945: BEGIN
9946: SELECT serial_number
9947: bulk collect INTO l_serial_numbers
9948: FROM mtl_serial_numbers
9949: WHERE lpn_id = p_from_lpn_id
9950: AND inventory_item_id = l_wlpnc_rec.inventory_item_id
9951: AND Nvl(lot_number, '@@@') = Nvl(l_wlpnc_rec.lot_number, '@@@')
9952: AND Nvl(revision, '@@@') = Nvl(l_wlpnc_rec.revision,'@@@');
9952: AND Nvl(revision, '@@@') = Nvl(l_wlpnc_rec.revision,'@@@');
9953: EXCEPTION
9954: WHEN OTHERS THEN
9955: IF (l_debug = 1) THEN
9956: debug('Error retrieving entries from MTL_SERIAL_NUMBERS',
9957: 'transfer_contents', 9);
9958: END IF;
9959: RAISE fnd_api.g_exc_error;
9960: END;
10294: SELECT msn.serial_number
10295: , 'N'
10296: , msn.inspection_status
10297: , msn.lot_number
10298: FROM mtl_serial_numbers_interface msni
10299: , mtl_serial_numbers msn
10300: , rcv_serials_supply rss
10301: WHERE msni.transaction_interface_id = p_msni_txn_interface_id
10302: AND msn.serial_number BETWEEN msni.fm_serial_number AND Nvl(msni.to_serial_number, msni.fm_serial_number)
10295: , 'N'
10296: , msn.inspection_status
10297: , msn.lot_number
10298: FROM mtl_serial_numbers_interface msni
10299: , mtl_serial_numbers msn
10300: , rcv_serials_supply rss
10301: WHERE msni.transaction_interface_id = p_msni_txn_interface_id
10302: AND msn.serial_number BETWEEN msni.fm_serial_number AND Nvl(msni.to_serial_number, msni.fm_serial_number)
10303: AND Length(msn.serial_number) = Length(msni.fm_serial_number)
10313: CURSOR msnt_ser_csr(v_lpn_id NUMBER
10314: , v_item_id NUMBER) IS
10315: SELECT serial_number
10316: , 'N'
10317: FROM mtl_serial_numbers
10318: WHERE inventory_item_id = v_item_id
10319: AND lpn_id = v_lpn_id
10320: AND (
10321: (p_revision IS NOT NULL and revision = p_revision)
10363: l_lot_number mtl_lot_numbers.lot_number%type; --p_lot for complete_putaway
10364: l_mmtt_prm_qty NUMBER; --Lot quantity in primary uom
10365: l_lot_expiration_date DATE; --Lot expiration date
10366: l_lot_status_id NUMBER; --Lot Status
10367: l_cur_ser_number mtl_serial_numbers.serial_number%TYPE;
10368: l_mmtt_temp_id NUMBER; --Variable to store p_temp_id of complete_putaway
10369: l_mmtt_temp_id_tbl num_tab; --Transaction_Temp_ids for the current group
10370: l_mmtt_item_id_tbl num_tab; --Item ID for the current group
10371: l_mmtt_rev_tbl rev_tab; --Revision for the current group
10737: --code is dynamic at SO issue
10738: IF (l_serial_control_code = 6) THEN
10739: SELECT count(1)
10740: INTO l_lpn_serials_cnt
10741: FROM mtl_serial_numbers
10742: WHERE lpn_id = p_lpn_id
10743: AND inventory_item_id = p_item_id;
10744: END IF;
10745: IF ((l_serial_control_code IN (2, 5)) OR
10781: --R12: Find out the serials entered by the user in the UI,
10782: --grouping by their inspection_status and lot_number
10783: SELECT count(1), Nvl(msn.inspection_status,-1), Nvl(msn.lot_number,'&*_')
10784: bulk collect INTO l_msni_qty_tbl, l_msni_inspect_status_tbl, l_msni_lot_tbl
10785: FROM mtl_serial_numbers_interface msni, mtl_serial_numbers msn
10786: WHERE msni.transaction_interface_id = p_msni_txn_interface_id
10787: AND msn.serial_number BETWEEN msni.fm_serial_number AND Nvl(msni.to_serial_number,msni.fm_serial_number)
10788: AND Length(msn.serial_number) = Length(msni.fm_serial_number)
10789: GROUP BY msn.inspection_status, msn.lot_number;
11190: OPEN rcv_serials_csr;
11191: FETCH rcv_serials_csr
11192: BULK COLLECT INTO l_ser_num_tbl, l_ser_mark_tbl,l_ser_inspect_status_tbl,l_ser_lot_num_tbl;
11193:
11194: DELETE FROM mtl_serial_numbers_interface
11195: WHERE transaction_interface_id = p_msni_txn_interface_id;
11196:
11197: IF (l_debug = 1) THEN
11198: debug('Number of MSNI deleted: '||SQL%rowcount,l_proc_name,9);
13889:
13890: --Delete MSNI
13891: DELETE
13892: FROM
13893: mtl_serial_numbers_interface
13894: WHERE
13895: product_transaction_id IN
13896: (
13897: SELECT
13919: );
13920: --Delete MSNT
13921: DELETE
13922: FROM
13923: mtl_serial_numbers_temp
13924: WHERE
13925: transaction_temp_id IN
13926: (
13927:
13956: --Delete MSNT based on Serial_transaction temp of MTLT
13957:
13958: DELETE
13959: FROM
13960: mtl_serial_numbers_temp
13961: WHERE
13962: transaction_temp_id IN
13963: (
13964: SELECT
14144: WHERE
14145: parent_lpn_id IN (p_lpn_id);
14146: --update msn
14147: UPDATE
14148: mtl_serial_numbers
14149: SET
14150: group_mark_id = NULL,
14151: line_mark_id = NULL
14152: WHERE
14154: (
14155: SELECT
14156: serial_number
14157: FROM
14158: mtl_serial_numbers
14159: WHERE
14160: lpn_id IN (p_lpn_id)
14161: );
14162: