[Home] [Help]
376: x_returnStatus => l_returnStatus);
377: END IF;
378:
379: -- Delete error records from MTLI
380: DELETE FROM mtl_transaction_lots_interface mtli
381: WHERE mtli.transaction_interface_id IN
382: (SELECT mti.transaction_interface_id
383: FROM mtl_transactions_interface mti
384: WHERE mti.transaction_header_id = p_mtl_header_id);
384: WHERE mti.transaction_header_id = p_mtl_header_id);
385:
386: IF (l_logLevel <= wip_constants.full_logging) THEN
387: l_msg := SQL%ROWCOUNT ||
388: ' rows deleted from mtl_transaction_lots_interface';
389: wip_logger.log(p_msg => l_msg,
390: x_returnStatus => l_returnStatus);
391: END IF;
392:
8841: | in mtlt is already inserted for record in mtt and the fact that
8842: | we only insert one row into mtlt here for one row in mtt, we could
8843: | add a NOT EXISTS statment to prevent duplicate record being inserted
8844: +---------------------------------------------------------------------*/
8845: INSERT INTO mtl_transaction_lots_interface
8846: (transaction_interface_id,
8847: lot_number,
8848: primary_quantity,
8849: transaction_quantity,
8884: AND mti.organization_id = wdj.organization_id
8885: AND mti.transaction_source_id = wdj.wip_entity_id
8886: AND NOT EXISTS
8887: (SELECT 1
8888: FROM mtl_transaction_lots_interface mtli
8889: WHERE mtli.transaction_interface_id =
8890: mti.transaction_interface_id);
8891:
8892: -- IF debug message level = 2, write statement below to log file
8892: -- IF debug message level = 2, write statement below to log file
8893: IF (l_logLevel <= wip_constants.full_logging) THEN
8894: fnd_message.set_name('WIP', 'WIP_INSERTED_ROWS');
8895: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
8896: fnd_message.set_token('ENTITY2', 'MTL_TRANSACTION_LOTS_INTERFACE');
8897: l_msg := fnd_message.get;
8898: wip_logger.log(p_msg => l_msg,
8899: x_returnStatus => l_returnStatus);
8900: END IF;
8902: /* Update lot records with an expiration date */
8903: /* Use expiration date in MTL_LOT_NUMBERS. */
8904: /* IF no expiration date THEN calculate based on SHELF_LIFE_DAYS */
8905:
8906: UPDATE mtl_transaction_lots_interface mtli
8907: SET lot_expiration_date =
8908: (SELECT MIN(mln.expiration_date)
8909: FROM mtl_transactions_interface mti,
8910: mtl_system_items msi,
8938: -- IF debug message level = 2, write statement below to log file
8939: IF (l_logLevel <= wip_constants.full_logging) THEN
8940: fnd_message.set_name('WIP', 'WIP_UPDATED_ROWS');
8941: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
8942: fnd_message.set_token('ENTITY2', 'MTL_TRANSACTION_LOTS_INTERFACE');
8943: l_msg := fnd_message.get;
8944: wip_logger.log(p_msg => l_msg,
8945: x_returnStatus => l_returnStatus);
8946: END IF;
8948: /* IF Exp date null in MTL_LOT_NUMBERS should I leave it null */
8949: /* Or should I just decode based on exp date null in the temp table? */
8950: /* Removed group by and modIFied select and where conditions to avoid
8951: oracle error 1427. See bugs 866408 and 938422. */
8952: UPDATE mtl_transaction_lots_interface mtli
8953: SET lot_expiration_date =
8954: (SELECT mti.transaction_date + NVL(msi.shelf_life_days,0)
8955: FROM mtl_transactions_interface mti,
8956: mtl_system_items msi
8984: -- IF debug message level = 2, write statement below to log file
8985: IF (l_logLevel <= wip_constants.full_logging) THEN
8986: fnd_message.set_name('WIP', 'WIP_UPDATED_ROWS');
8987: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
8988: fnd_message.set_token('ENTITY2', 'MTL_TRANSACTION_LOTS_INTERFACE');
8989: l_msg := fnd_message.get;
8990: wip_logger.log(p_msg => l_msg,
8991: x_returnStatus => l_returnStatus);
8992: END IF;
9042: wsmi.assembly_serial_number,
9043: wsmi.assembly_serial_number
9044: FROM mtl_transactions_interface mti,
9045: mtl_system_items msi,
9046: mtl_transaction_lots_interface mtli,
9047: wip_serial_move_interface wsmi
9048: WHERE mti.organization_id = msi.organization_id
9049: AND mti.inventory_item_id = msi.inventory_item_id
9050: AND mti.transaction_header_id = p_gib.assy_header_id