[Home] [Help]
432: serial_number,
433: SUM(transaction_quantity) transaction_quantity,
434: SUM(primary_quantity) primary_quantity,
435: SUM(secondary_quantity) secondary_quantity
436: FROM mtl_allocations_gtmp
437: WHERE (lot_number IS NOT NULL OR serial_number IS NOT NULL)
438: GROUP BY transaction_temp_id, lot_number, serial_number, secondary_quantity
439: ORDER BY transaction_temp_id, lot_number, serial_number;
440:
3505: ,transaction_quantity
3506: ,primary_quantity
3507: ,suggested_quantity
3508: ,secondary_quantity
3509: FROM mtl_allocations_gtmp
3510: ORDER BY
3511: transaction_temp_id
3512: ,lot_number;
3513:
3515: CURSOR cur_ins_mtlt_lot_sub (p_lot_transaction_temp_id NUMBER)
3516: IS
3517: SELECT DISTINCT lot_number, transaction_quantity,
3518: primary_quantity, secondary_quantity --BUG12670785
3519: FROM mtl_allocations_gtmp
3520: WHERE lot_number NOT IN (
3521: SELECT DISTINCT lot_number
3522: FROM mtl_transaction_lots_temp
3523: WHERE transaction_temp_id = p_lot_transaction_temp_id)
3524: ORDER BY lot_number;
3525:
3526: CURSOR cur_mtlts_deleted_ls IS
3527: SELECT mtlt.transaction_temp_id, mtlt.lot_number, mtlt.primary_quantity FROM mtl_transaction_lots_temp mtlt
3528: WHERE mtlt.lot_number NOT IN (SELECT mag.lot_number FROM mtl_allocations_gtmp mag)
3529: AND mtlt.transaction_temp_id = p_transaction_temp_id;
3530:
3531: --/* Bug 9448490 Lot Substitution Project */ end
3532:
3659: CLOSE cur_mtlts_deleted_ls;
3660:
3661:
3662: DELETE FROM mtl_transaction_lots_temp mtlt
3663: WHERE mtlt.lot_number NOT IN (SELECT NVL(lot_number, '@####') FROM mtl_allocations_gtmp) --Added NVL as in some cases p_lot_numbers had ':' extra causing insert with null values
3664: AND mtlt.transaction_temp_id = p_transaction_temp_id;
3665: --Debug stmts
3666: END IF;
3667: END IF;
4061: THEN
4062: L_progress := '410';
4063: mydebug('l_progress: ' || l_progress );
4064: -- update all serial records with the serial_transaction_temp_id to be populated
4065: UPDATE mtl_allocations_gtmp
4066: SET child_transaction_temp_id = l_serial_transaction_temp_id
4067: WHERE lot_number = rec_confirmed_lots_serials.lot_number
4068: AND transaction_temp_id = rec_confirmed_lots_serials.transaction_temp_id ;
4069: IF SQL%NOTFOUND THEN
4180: ,lot_number
4181: ,serial_number
4182: ,transaction_quantity
4183: ,primary_quantity
4184: FROM mtl_allocations_gtmp
4185: WHERE NVL(lot_number,'@@') = nvl(p_serial_lot_number,'@@')
4186: ORDER BY
4187: transaction_temp_id
4188: --,nvl(lot_number,'@@')
4282: , last_updated_by = p_user_id
4283: WHERE transaction_temp_id = l_o_msnt_transaction_temp_id
4284: AND fm_serial_number IN
4285: (SELECT serial_number
4286: FROM mtl_allocations_gtmp
4287: WHERE NVL(lot_number,'@@') = nvl(p_serial_lot_number,'@@'));
4288:
4289: IF SQL%NOTFOUND THEN
4290: mydebug('11 msnt not updateed..');
4300: AND inventory_item_id = p_inventory_item_id
4301: --AND group_mark_id IS NULL
4302: AND serial_number IN
4303: (SELECT serial_number
4304: FROM mtl_allocations_gtmp
4305: WHERE NVL(lot_number,'@@') = nvl(p_serial_lot_number,'@@'));
4306:
4307: IF SQL%NOTFOUND THEN
4308: RAISE fnd_api.G_EXC_ERROR;
5008: mydebug('x_return_status : ' || x_return_status);
5009: RAISE fnd_api.G_EXC_ERROR;
5010: END IF;
5011:
5012: ELSE -- confirmed_serials are no null so get the serials from mtl-allocations_gtmp table
5013:
5014: INSERT INTO mtl_serial_numbers_temp
5015: (transaction_temp_id
5016: ,last_update_date
5172: AND NVL(lot_number,'@@') = nvl(p_serial_lot_number,'@@')
5173: --AND group_mark_id IS NULL
5174: AND msn.serial_number IN
5175: (SELECT serial_number
5176: FROM mtl_allocations_gtmp
5177: WHERE NVL(lot_number,'@@') = nvl(p_serial_lot_number,'@@'))
5178: );
5179: IF SQL%NOTFOUND THEN --- MSN record not found)
5180: RAISE fnd_api.G_EXC_ERROR;
5285: AND NVL(lot_number,'@@') = nvl(p_serial_lot_number,'@@')
5286: --AND group_mark_id IS NULL
5287: AND msn.serial_number IN
5288: (SELECT serial_number
5289: FROM mtl_allocations_gtmp
5290: WHERE NVL(lot_number,'@@') = nvl(p_serial_lot_number,'@@'));
5291:
5292: IF SQL%NOTFOUND THEN
5293: fnd_message.set_name('WMS', 'WMS_ERROR_MARKING_SERIAL'); --NEWMSG
5627: -- update one of the MMTTs(min temp_id) from each group and delete rest from that group
5628: l_progress := 500;
5629: IF l_debug = 1 THEN mydebug('l_progress = ' || l_progress); END IF;
5630:
5631: DELETE mtl_allocations_gtmp ;
5632: IF SQL%NOTFOUND THEN
5633: null;
5634: END IF;
5635:
5660: -- MMTTs should be deleted that belong to the p_transaction-header_id but are not
5661: -- in this list
5662: l_progress := 600;
5663: INSERT
5664: INTO mtl_allocations_gtmp
5665: (transaction_temp_id)
5666: VALUES ( rec_mmtt1.group_temp_id);
5667: mydebug('Inserted temp_id into mtl_allocations_gtmp: ' || rec_mmtt1.group_temp_id);
5668:
5663: INSERT
5664: INTO mtl_allocations_gtmp
5665: (transaction_temp_id)
5666: VALUES ( rec_mmtt1.group_temp_id);
5667: mydebug('Inserted temp_id into mtl_allocations_gtmp: ' || rec_mmtt1.group_temp_id);
5668:
5669: IF p_suggested_uom <> rec_mmtt1.item_primary_uom_code
5670: THEN
5671: IF(p_suggested_uom = rec_mmtt1.SECONDARY_UOM_CODE) THEN
6303: ,last_update_date = SYSDATE
6304: ,last_updated_by = p_user_id
6305: WHERE transaction_temp_id IN
6306: (SELECT transaction_temp_id
6307: FROM mtl_allocations_gtmp);
6308: mydebug('nullify certain columns for all the dispatched tasks for this user');
6309: UPDATE wms_dispatched_tasks
6310: SET device_invoked = null
6311: -- Following two statement are commnet for bug 4560814
6336: FROM mtl_material_transactions_temp
6337: WHERE transaction_header_id = p_transaction_header_id
6338: AND transaction_temp_id NOT IN
6339: (SELECT transaction_temp_id
6340: FROM mtl_allocations_gtmp));
6341: IF SQL%NOTFOUND THEN
6342: mydebug('no extra WDTs to delete :' );
6343: NULL;
6344: END IF;
6356:
6357: DELETE wms_dispatched_tasks
6358: WHERE transaction_temp_id IN
6359: (SELECT transaction_temp_id
6360: FROM mtl_allocations_gtmp)
6361: AND status <> l_g_task_queued;
6362: IF SQL%NOTFOUND THEN
6363: mydebug('no non queued WDTs to delete :' );
6364: NULL;
6370: DELETE mtl_material_transactions_temp
6371: WHERE transaction_header_id = p_transaction_header_id
6372: AND transaction_temp_id NOT IN
6373: (SELECT transaction_temp_id
6374: FROM mtl_allocations_gtmp);
6375: IF SQL%NOTFOUND THEN
6376: mydebug('no extra MMTTs to delete :' );
6377: NULL;
6378: END IF;
7061: L NUMBER :=0;
7062:
7063:
7064: CURSOR tempid_cur (v_transaction_temp_id NUMBER) IS
7065: SELECT transaction_temp_id from mtl_allocations_gtmp
7066: WHERE transaction_temp_id <> v_transaction_temp_id
7067: ORDER BY transaction_temp_id;
7068:
7069:
7076: l_progress := 110;
7077:
7078: l_prev_task_status := wms_picking_pkg.g_previous_task_status(p_transaction_temp_id);
7079:
7080: SELECT count(*) into new_mmtt_count from mtl_allocations_gtmp;
7081: mydebug('Total count in mtl_allocations_gtemp: ' || new_mmtt_count);
7082:
7083: l_first_index := wms_picking_pkg.g_start_over_tempid.first;
7084: l_existing_count := wms_picking_pkg.g_start_over_tempid.count-1;
7241: BEGIN
7242: x_return_status := l_g_ret_sts_success;
7243: mydebug ('In :' || l_proc_name );
7244:
7245: DELETE mtl_allocations_gtmp ;
7246: IF SQL%NOTFOUND THEN
7247: null;
7248: END IF;
7249:
7393: END IF;
7394: mydebug ('l_serial_number:' || l_serial_number);
7395: mydebug ('s:' || s||':t:' || t );
7396: INSERT
7397: INTO mtl_allocations_gtmp
7398: (transaction_temp_id
7399: , lot_number
7400: , serial_number
7401: , transaction_quantity
7413: EXIT;
7414: END LOOP;
7415: ELSIF (l_lot_number IS NOT NULL AND NVL(l_lot_trx_qty, -1) > 0) THEN
7416: INSERT
7417: INTO mtl_allocations_gtmp
7418: (transaction_temp_id
7419: , lot_number
7420: , serial_number
7421: , transaction_quantity
11982:
11983:
11984: -- during the picking process. If the user does not specifies
11985: -- a from lpn, this procedure will figure out if the loose quantity will
11986: -- satisfy the pick in question, the temp table mtl_allocations_gtmp
11987: -- will store the available lot and serial numbers for this pick
11988:
11989: PROCEDURE loose_match(
11990: p_org_id IN NUMBER