[Home] [Help]
146: mmtt.transaction_reference reference,
147: /* Fixed bug 4604021 */
148: mmtt.move_transaction_id move_txn_id,
149: wmti.transaction_type txn_type
150: FROM mtl_material_transactions_temp mmtt,
151: wip_move_txn_interface wmti
152: WHERE mmtt.move_transaction_id = wmti.transaction_id
153: AND mmtt.transaction_source_type_id = 5 /* Bug 4886889 - improve performance fetching from MMT. */
154: AND mmtt.transaction_source_id = wmti.wip_entity_id
465: -- Delete error records from MTLT
466: DELETE FROM mtl_transaction_lots_temp mtlt
467: WHERE mtlt.transaction_temp_id IN
468: (SELECT mmtt.transaction_temp_id
469: FROM mtl_material_transactions_temp mmtt
470: WHERE mmtt.transaction_header_id = p_mtl_header_id
471: AND EXISTS
472: (SELECT *
473: FROM mtl_transactions_interface mti
481: x_returnStatus => l_returnStatus);
482: END IF;
483:
484: -- Delete error records from MTTT
485: DELETE FROM mtl_material_transactions_temp mmtt
486: WHERE mmtt.transaction_header_id = p_mtl_header_id
487: AND EXISTS
488: (SELECT *
489: FROM mtl_transactions_interface mti
491: AND mti.transaction_header_id = p_mtl_header_id);
492:
493: IF (l_logLevel <= wip_constants.full_logging) THEN
494: l_msg := SQL%ROWCOUNT ||
495: ' rows deleted from mtl_material_transactions_temp';
496: wip_logger.log(p_msg => l_msg,
497: x_returnStatus => l_returnStatus);
498: END IF;
499:
7423: x_returnStatus OUT NOCOPY VARCHAR2) IS
7424:
7425: CURSOR c_mmtt IS
7426: SELECT transaction_temp_id mtl_temp_id
7427: FROM mtl_material_transactions_temp
7428: WHERE transaction_header_id = p_mtl_header_id
7429: AND transaction_action_id = p_txn_action_id
7430: AND transaction_type_id = p_txn_type_id
7431: AND transaction_source_type_id = TPS_INV_JOB_OR_SCHED
7482: -- Fix bug 2369642
7483: --(p_txn_type = WIP_CONSTANTS.MOVE_TXN AND
7484: -- l_mandatory_scrap_flag = WIP_CONSTANTS.YES))) THEN
7485:
7486: UPDATE mtl_material_transactions_temp
7487: SET transaction_temp_id = mtl_material_transactions_s.nextval
7488: WHERE transaction_header_id = p_mtl_header_id
7489: AND transaction_temp_id IS NULL
7490: AND transaction_action_id = p_txn_action_id
7574: 'RCV', -1,
7575: 1) source_code,
7576: MMTT.PRIMARY_QUANTITY primary_quantity
7577: FROM wip_move_txn_interface wmti,
7578: mtl_material_transactions_temp mmtt,
7579: mtl_parameters mp
7580: WHERE wmti.group_id = p_group_id
7581: AND (p_txn_id IS NULL OR wmti.transaction_id = p_txn_id)
7582: AND TRUNC(wmti.transaction_date) = TRUNC(p_txn_date)
7659: END IF;
7660:
7661: -- Modified Sql For Performance Bug 13798118. Used Exists instead of In Clause.
7662:
7663: UPDATE mtl_material_transactions_temp mmtt1
7664: SET mmtt1.transaction_temp_id = mtl_material_transactions_s.nextval
7665: WHERE exists ( SELECT 1
7666: FROM wip_move_txn_interface wmti,
7667: mtl_parameters mp,
7664: SET mmtt1.transaction_temp_id = mtl_material_transactions_s.nextval
7665: WHERE exists ( SELECT 1
7666: FROM wip_move_txn_interface wmti,
7667: mtl_parameters mp,
7668: mtl_material_transactions_temp mmtt2
7669: WHERE wmti.group_id = p_group_id
7670: AND TRUNC(wmti.transaction_date) = TRUNC(p_txn_date)
7671: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
7672: AND wmti.process_status = WIP_CONSTANTS.RUNNING
7867: * This procedure is equivalent to witpsst_scrap_txns in wiltps5.ppc
7868: * This procedure is used to update scrap quantity in WIP_DISCRETE_JOBS for
7869: * discrete and OSFM jobs, and WIP_REPETITIVE_SCHEDULES for repetitive
7870: * schedules.
7871: * This procedure also insert into MTL_MATERIAL_TRANSACTIONS_TEMP for costing
7872: * purpose IF the user provide scrap account.
7873: ****************************************************************************/
7874: PROCEDURE scrap_txns(p_gib IN OUT NOCOPY group_rec_t,
7875: x_returnStatus OUT NOCOPY VARCHAR2) IS
7878: SELECT wmti.transaction_id txn_id,
7879: wmti.organization_id org_id,
7880: mmtt.material_allocation_temp_id alloc_id
7881: FROM wip_move_txn_interface wmti,
7882: mtl_material_transactions_temp mmtt
7883: WHERE wmti.transaction_id = mmtt.move_transaction_id
7884: /* Improve performance */
7885: AND mmtt.organization_id = wmti.organization_id
7886: AND mmtt.transaction_source_id = wmti.wip_entity_id
8237: FOR l_step IN WIP_CONSTANTS.QUEUE..WIP_CONSTANTS.RUN LOOP
8238:
8239: -- Discrete and Lotbased jobs
8240: IF(l_move.jobTxn = WIP_CONSTANTS.YES) THEN
8241: INSERT INTO mtl_material_transactions_temp
8242: (last_updated_by,
8243: last_update_date,
8244: last_update_login,
8245: created_by,
8364: -- IF debug message level = 2, write statement below to log file
8365: IF (l_logLevel <= wip_constants.full_logging) THEN
8366: fnd_message.set_name('WIP', 'WIP_INSERTED_ROWS');
8367: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
8368: fnd_message.set_token('ENTITY2', 'MTL_MATERIAL_TRANSACTIONS_TEMP');
8369: l_msg := fnd_message.get;
8370: wip_logger.log(p_msg => l_msg,
8371: x_returnStatus => l_returnStatus);
8372: END IF;
8375:
8376: -- Repetitive schedules
8377: IF(l_move.scheTxn = WIP_CONSTANTS.YES) THEN
8378:
8379: INSERT INTO mtl_material_transactions_temp
8380: (material_allocation_temp_id,
8381: last_updated_by,
8382: last_update_date,
8383: last_update_login,
8499: -- IF debug message level = 2, write statement below to log file
8500: IF (l_logLevel <= wip_constants.full_logging) THEN
8501: fnd_message.set_name('WIP', 'WIP_INSERTED_ROWS');
8502: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
8503: fnd_message.set_token('ENTITY2', 'MTL_MATERIAL_TRANSACTIONS_TEMP');
8504: l_msg := fnd_message.get;
8505: wip_logger.log(p_msg => l_msg,
8506: x_returnStatus => l_returnStatus);
8507: END IF;
8619:
8620: /*****************************************************************************
8621: * This procedure is equivalent to witpscp_completion in wiltps5.ppc
8622: * This procedure is used to do easy completion and easy return
8623: * This procedure insert into MTL_MATERIAL_TRANSACTIONS_TEMP and
8624: * MTL_TRANSACTION_LOTS_TEMP
8625: ****************************************************************************/
8626: PROCEDURE ez_completion(p_gib IN OUT NOCOPY group_rec_t,
8627: p_txn_type IN NUMBER,
8636: transaction_date txn_date,
8637: ABS(primary_quantity) primary_qty,
8638: reason_id reason_id,
8639: transaction_reference reference
8640: FROM mtl_material_transactions_temp
8641: WHERE transaction_header_id = p_header_id
8642: AND wip_entity_type = WIP_CONSTANTS.REPETITIVE
8643: AND transaction_action_id IN (WIP_CONSTANTS.RETASSY_ACTION,
8644: WIP_CONSTANTS.CPLASSY_ACTION);
10380: -- Update lock_flag to 'Y' to prevent inventory worker pick up these
10381: -- records. After we commit, assembly completion record will be in
10382: -- MMTT. There is a slim chance that inventory worker may pick up
10383: -- these records after we commit, and before we call processTemp.
10384: UPDATE mtl_material_transactions_temp
10385: SET lock_flag ='Y'
10386: WHERE transaction_header_id = l_gib.assy_header_id;
10387: END IF;
10388: -- Fixed bug 4361566. Commit to prevent dead lock from calling
10637: | Inventory processing |
10638: +---------------------*/
10639: SELECT count(*)
10640: INTO l_MMTT_record
10641: FROM mtl_material_transactions_temp
10642: WHERE transaction_header_id = l_gib.mtl_header_id;
10643:
10644: IF(l_MMTT_record > 0 AND
10645: l_gib.mtl_mode <> WIP_CONSTANTS.NO_PROCESSING) THEN
11047: sign(mmtt.transaction_quantity), -- transaction_quantity
11048: wmti.transaction_date
11049: FROM wip_move_txn_allocations wmta,
11050: wip_move_txn_interface wmti,
11051: mtl_material_transactions_temp mmtt
11052: WHERE wmti.transaction_id = wmta.transaction_id
11053: AND wmti.organization_id = wmta.organization_id
11054: AND wmti.transaction_id = mmtt.move_transaction_id
11055: AND mmtt.transaction_temp_id = p_tmp_id
11649: ****************************************************************************/
11650: PROCEDURE clean_up(p_assy_header_id IN NUMBER) IS
11651:
11652: BEGIN
11653: UPDATE mtl_material_transactions_temp
11654: SET lock_flag ='N'
11655: WHERE transaction_header_id = p_assy_header_id;
11656:
11657: END clean_up;