[Home] [Help]
361: SET wmti.process_status = WIP_CONSTANTS.ERROR
362: WHERE wmti.group_id = p_group_id
363: AND EXISTS
364: (SELECT 1
365: FROM mtl_transactions_interface mti
366: WHERE mti.transaction_header_id = p_mtl_header_id
367: AND mti.move_transaction_id = wmti.transaction_id
368: AND mti.error_explanation IS NOT NULL);
369:
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);
385:
386: IF (l_logLevel <= wip_constants.full_logging) THEN
387: l_msg := SQL%ROWCOUNT ||
390: x_returnStatus => l_returnStatus);
391: END IF;
392:
393: -- Delete error records from MTI
394: DELETE FROM mtl_transactions_interface
395: WHERE transaction_header_id = p_mtl_header_id;
396:
397: IF (l_logLevel <= wip_constants.full_logging) THEN
398: l_msg := SQL%ROWCOUNT ||
395: WHERE transaction_header_id = p_mtl_header_id;
396:
397: IF (l_logLevel <= wip_constants.full_logging) THEN
398: l_msg := SQL%ROWCOUNT ||
399: ' rows deleted from mtl_transactions_interface';
400: wip_logger.log(p_msg => l_msg,
401: x_returnStatus => l_returnStatus);
402: END IF;
403:
463: END write_mtl_error;
464:
465: /*****************************************************************************
466: * This procedure will be used to record error messages from
467: * MTL_TRANSACTIONS_INTERFACE into WIP_TXN_INTERFACE_ERRORS
468: ****************************************************************************/
469: PROCEDURE write_mtl_errors(p_mtl_header_id IN NUMBER) IS
470:
471: CURSOR c_mtl_errors IS
480: mti.request_id,
481: mti.program_application_id,
482: mti.program_id,
483: mti.program_update_date
484: FROM mtl_transactions_interface mti,
485: mtl_system_items_kfv msik
486: WHERE mti.transaction_header_id = p_mtl_header_id
487: AND mti.inventory_item_id = msik.inventory_item_id
488: AND mti.organization_id = msik.organization_id
8511: /*------------------------------------------------------------+
8512: | Insert completion transaction record |
8513: +------------------------------------------------------------*/
8514: IF(l_move.jobTxn = WIP_CONSTANTS.YES) THEN
8515: INSERT INTO mtl_transactions_interface
8516: (last_updated_by,
8517: last_update_date,
8518: last_update_login,
8519: created_by,
8656: END IF; -- discrete jobs
8657:
8658: IF(l_move.scheTxn = WIP_CONSTANTS.YES) THEN
8659:
8660: INSERT INTO mtl_transactions_interface
8661: (last_updated_by,
8662: last_update_date,
8663: last_update_login,
8664: created_by,
8793: -- IF debug message level = 2, write statement below to log file
8794: IF (l_logLevel <= wip_constants.full_logging) THEN
8795: fnd_message.set_name('WIP', 'WIP_INSERTED_ROWS');
8796: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
8797: fnd_message.set_token('ENTITY2', 'MTL_TRANSACTIONS_INTERFACE');
8798: l_msg := fnd_message.get;
8799: wip_logger.log(p_msg => l_msg,
8800: x_returnStatus => l_returnStatus);
8801: END IF;
8804: IF(l_move.jobTxn = WIP_CONSTANTS.YES) THEN
8805:
8806: SELECT COUNT(*)
8807: INTO l_mti_lot_rec
8808: FROM mtl_transactions_interface mti,
8809: mtl_system_items msi
8810: WHERE mti.organization_id = msi.organization_id
8811: AND mti.inventory_item_id = msi.inventory_item_id
8812: AND mti.transaction_header_id = p_gib.assy_header_id
8868: DECODE(msi.serial_number_control_code,
8869: WIP_CONSTANTS.FULL_SN, mtl_material_transactions_s.nextval,
8870: WIP_CONSTANTS.DYN_RCV_SN,mtl_material_transactions_s.nextval,
8871: NULL)
8872: FROM mtl_transactions_interface mti,
8873: mtl_system_items msi,
8874: wip_discrete_jobs wdj
8875: WHERE mti.organization_id = msi.organization_id
8876: AND mti.inventory_item_id = msi.inventory_item_id
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,
8911: mtl_lot_numbers mln
8912: WHERE mti.organization_id = msi.organization_id
8913: AND mti.inventory_item_id = msi.inventory_item_id
8922: AND mln.inventory_item_id = mti.inventory_item_id
8923: AND mln.organization_id = mti.organization_id)
8924: WHERE mtli.transaction_interface_id IN
8925: (SELECT mti.transaction_interface_id
8926: FROM mtl_transactions_interface mti,
8927: mtl_system_items msi
8928: WHERE mti.organization_id = msi.organization_id
8929: AND mti.inventory_item_id = msi.inventory_item_id
8930: AND mti.transaction_header_id = p_gib.assy_header_id
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
8957: WHERE mti.transaction_header_id = p_gib.assy_header_id
8958: AND mti.transaction_action_id = l_txn_action_id
8959: AND mti.transaction_type_id = l_txn_type_id
8968: mti.transaction_interface_id)
8969: WHERE mtli.lot_expiration_date IS NULL
8970: AND mtli.transaction_interface_id IN
8971: (SELECT mti.transaction_interface_id
8972: FROM mtl_transactions_interface mti,
8973: mtl_system_items msi
8974: WHERE mti.organization_id = msi.organization_id
8975: AND mti.inventory_item_id = msi.inventory_item_id
8976: AND mti.transaction_header_id = p_gib.assy_header_id
8995: END IF; -- Discrete and Lotbased jobs
8996:
8997: SELECT COUNT(*)
8998: INTO l_mti_ser_rec
8999: FROM mtl_transactions_interface mti,
9000: mtl_system_items msi
9001: WHERE mti.organization_id = msi.organization_id
9002: AND mti.inventory_item_id = msi.inventory_item_id
9003: AND mti.transaction_header_id = p_gib.assy_header_id
9040: mti.last_updated_by,
9041: mti.last_update_login,
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