[Home] [Help]
84: mmt.transaction_reference reference,
85: /* Fixed bug 4628893 */
86: mmt.move_transaction_id move_txn_id,
87: wmti.transaction_type txn_type
88: FROM mtl_material_transactions mmt,
89: wip_move_txn_interface wmti
90: /* Bug 4891549 - Modified where clause to improve performance. */
91: WHERE mmt.transaction_source_type_id = TPS_INV_JOB_OR_SCHED
92: AND mmt.transaction_source_id = wmti.wip_entity_id
112: mmt.transaction_reference reference,
113: /* Fixed bug 4628893 */
114: mmt.move_transaction_id move_txn_id,
115: wmti.transaction_type txn_type
116: FROM mtl_material_transactions mmt,
117: wip_move_txn_interface wmti
118: /* Bug 4891549 - Modified where clause to improve performance. */
119: WHERE mmt.transaction_source_type_id = TPS_INV_JOB_OR_SCHED
120: AND mmt.transaction_source_id = wmti.wip_entity_id
146: wmti.fm_intraoperation_step_type fm_step,
147: wmti.to_operation_seq_num to_op,
148: wmti.to_intraoperation_step_type to_step
149: /* End fix of bug 5014211 */
150: FROM mtl_material_transactions mmt,
151: wip_move_txn_interface wmti
152: /* Bug 4891549 - Modified where clause to improve performance. */
153: WHERE mmt.transaction_source_type_id = TPS_INV_JOB_OR_SCHED
154: AND mmt.transaction_source_id = wmti.wip_entity_id
7222: x_returnStatus OUT NOCOPY VARCHAR2) IS
7223:
7224: CURSOR c_mmtt IS
7225: SELECT transaction_temp_id mtl_temp_id
7226: FROM mtl_material_transactions_temp
7227: WHERE transaction_header_id = p_mtl_header_id
7228: AND transaction_action_id = p_txn_action_id
7229: AND transaction_type_id = p_txn_type_id
7230: AND transaction_source_type_id = TPS_INV_JOB_OR_SCHED
7280: -- Fix bug 2369642
7281: --(p_txn_type = WIP_CONSTANTS.MOVE_TXN AND
7282: -- l_mandatory_scrap_flag = WIP_CONSTANTS.YES))) THEN
7283:
7284: UPDATE mtl_material_transactions_temp
7285: SET transaction_temp_id = mtl_material_transactions_s.nextval
7286: WHERE transaction_header_id = p_mtl_header_id
7287: AND transaction_temp_id IS NULL
7288: AND transaction_action_id = p_txn_action_id
7281: --(p_txn_type = WIP_CONSTANTS.MOVE_TXN AND
7282: -- l_mandatory_scrap_flag = WIP_CONSTANTS.YES))) THEN
7283:
7284: UPDATE mtl_material_transactions_temp
7285: SET transaction_temp_id = mtl_material_transactions_s.nextval
7286: WHERE transaction_header_id = p_mtl_header_id
7287: AND transaction_temp_id IS NULL
7288: AND transaction_action_id = p_txn_action_id
7289: AND transaction_type_id = p_txn_type_id
7372: 'RCV', -1,
7373: 1) source_code,
7374: MMTT.PRIMARY_QUANTITY primary_quantity
7375: FROM wip_move_txn_interface wmti,
7376: mtl_material_transactions_temp mmtt,
7377: mtl_parameters mp
7378: WHERE wmti.group_id = p_group_id
7379: AND (p_txn_id IS NULL OR wmti.transaction_id = p_txn_id)
7380: AND TRUNC(wmti.transaction_date) = TRUNC(p_txn_date)
7455: IF(l_num_snapshot = 0) THEN
7456: GOTO END_txn;
7457: END IF;
7458:
7459: UPDATE mtl_material_transactions_temp mmtt1
7460: SET mmtt1.transaction_temp_id = mtl_material_transactions_s.nextval
7461: WHERE mmtt1.rowid IN
7462: (SELECT mmtt1.rowid
7463: FROM wip_move_txn_interface wmti,
7456: GOTO END_txn;
7457: END IF;
7458:
7459: UPDATE mtl_material_transactions_temp mmtt1
7460: SET mmtt1.transaction_temp_id = mtl_material_transactions_s.nextval
7461: WHERE mmtt1.rowid IN
7462: (SELECT mmtt1.rowid
7463: FROM wip_move_txn_interface wmti,
7464: -- wip_parameters wp,
7462: (SELECT mmtt1.rowid
7463: FROM wip_move_txn_interface wmti,
7464: -- wip_parameters wp,
7465: mtl_parameters mp,
7466: mtl_material_transactions_temp mmtt2
7467: WHERE wmti.group_id = p_group_id
7468: AND TRUNC(wmti.transaction_date) = TRUNC(p_txn_date)
7469: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
7470: AND wmti.process_status = WIP_CONSTANTS.RUNNING
7666: * This procedure is equivalent to witpsst_scrap_txns in wiltps5.ppc
7667: * This procedure is used to update scrap quantity in WIP_DISCRETE_JOBS for
7668: * discrete and OSFM jobs, and WIP_REPETITIVE_SCHEDULES for repetitive
7669: * schedules.
7670: * This procedure also insert into MTL_MATERIAL_TRANSACTIONS_TEMP for costing
7671: * purpose IF the user provide scrap account.
7672: ****************************************************************************/
7673: PROCEDURE scrap_txns(p_gib IN OUT NOCOPY group_rec_t,
7674: x_returnStatus OUT NOCOPY VARCHAR2) IS
7677: SELECT wmti.transaction_id txn_id,
7678: wmti.organization_id org_id,
7679: mmtt.material_allocation_temp_id alloc_id
7680: FROM wip_move_txn_interface wmti,
7681: mtl_material_transactions_temp mmtt
7682: WHERE wmti.transaction_id = mmtt.move_transaction_id
7683: /* Improve performance */
7684: AND mmtt.organization_id = wmti.organization_id
7685: AND mmtt.transaction_source_id = wmti.wip_entity_id
8009:
8010: /* Generate a mtl txn header id IF one has not already been generated */
8011: IF(p_gib.mtl_header_id IS NULL OR
8012: p_gib.mtl_header_id = -1) THEN
8013: SELECT mtl_material_transactions_s.nextval
8014: INTO p_gib.mtl_header_id
8015: FROM DUAL;
8016: END IF;
8017:
8026: FOR l_step IN WIP_CONSTANTS.QUEUE..WIP_CONSTANTS.RUN LOOP
8027:
8028: -- Discrete and Lotbased jobs
8029: IF(l_move.jobTxn = WIP_CONSTANTS.YES) THEN
8030: INSERT INTO mtl_material_transactions_temp
8031: (last_updated_by,
8032: last_update_date,
8033: last_update_login,
8034: created_by,
8082: DECODE(p_gib.program_id,-1,NULL,p_gib.program_id),
8083: DECODE(p_gib.request_id,
8084: -1,NULL,SYSDATE), -- program_update_date
8085: p_gib.mtl_header_id, -- transaction_header_id
8086: mtl_material_transactions_s.nextval, -- transaction_temp_id
8087: wmti.source_code,
8088: wmti.source_line_id,
8089: l_transaction_mode, -- transaction_mode
8090: wmti.primary_item_id, -- inventory_item_id
8153: -- IF debug message level = 2, write statement below to log file
8154: IF (l_logLevel <= wip_constants.full_logging) THEN
8155: fnd_message.set_name('WIP', 'WIP_INSERTED_ROWS');
8156: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
8157: fnd_message.set_token('ENTITY2', 'MTL_MATERIAL_TRANSACTIONS_TEMP');
8158: l_msg := fnd_message.get;
8159: wip_logger.log(p_msg => l_msg,
8160: x_returnStatus => l_returnStatus);
8161: END IF;
8164:
8165: -- Repetitive schedules
8166: IF(l_move.scheTxn = WIP_CONSTANTS.YES) THEN
8167:
8168: INSERT INTO mtl_material_transactions_temp
8169: (material_allocation_temp_id,
8170: last_updated_by,
8171: last_update_date,
8172: last_update_login,
8207: posting_flag,
8208: transaction_batch_id,
8209: transaction_batch_seq
8210: )
8211: SELECT mtl_material_transactions_s.nextval, -- material_alloc_id
8212: wmti.last_updated_by, -- last_updated_by --Fix for bug 5195072
8213: SYSDATE, -- last_update_date
8214: DECODE(p_gib.login_id,-1,NULL,p_gib.login_id),
8215: wmti.created_by, -- created_by --Fix for bug 5195072
8219: DECODE(p_gib.program_id,-1,NULL,p_gib.program_id),
8220: DECODE(p_gib.request_id,
8221: -1,NULL,SYSDATE), -- program_update_date
8222: p_gib.mtl_header_id, -- transaction_header_id
8223: mtl_material_transactions_s.nextval, -- transaction_temp_id
8224: wmti.source_code,
8225: wmti.source_line_id,
8226: l_transaction_mode, -- transaction_mode
8227: wmti.primary_item_id, -- inventory_item_id
8288: -- IF debug message level = 2, write statement below to log file
8289: IF (l_logLevel <= wip_constants.full_logging) THEN
8290: fnd_message.set_name('WIP', 'WIP_INSERTED_ROWS');
8291: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
8292: fnd_message.set_token('ENTITY2', 'MTL_MATERIAL_TRANSACTIONS_TEMP');
8293: l_msg := fnd_message.get;
8294: wip_logger.log(p_msg => l_msg,
8295: x_returnStatus => l_returnStatus);
8296: END IF;
8376:
8377: /*****************************************************************************
8378: * This procedure is equivalent to witpscp_completion in wiltps5.ppc
8379: * This procedure is used to do easy completion and easy return
8380: * This procedure insert into MTL_MATERIAL_TRANSACTIONS_TEMP and
8381: * MTL_TRANSACTION_LOTS_TEMP
8382: ****************************************************************************/
8383: PROCEDURE ez_completion(p_gib IN OUT NOCOPY group_rec_t,
8384: p_txn_type IN NUMBER,
8393: transaction_date txn_date,
8394: ABS(primary_quantity) primary_qty,
8395: reason_id reason_id,
8396: transaction_reference reference
8397: FROM mtl_material_transactions_temp
8398: WHERE transaction_header_id = p_header_id
8399: AND wip_entity_type = WIP_CONSTANTS.REPETITIVE
8400: AND transaction_action_id IN (WIP_CONSTANTS.RETASSY_ACTION,
8401: WIP_CONSTANTS.CPLASSY_ACTION);
8474:
8475: /* Generate a mtl txn header id IF one has not already been generated */
8476: IF(p_gib.mtl_header_id IS NULL OR
8477: p_gib.mtl_header_id = -1) THEN
8478: SELECT mtl_material_transactions_s.nextval
8479: INTO p_gib.mtl_header_id
8480: FROM DUAL;
8481: END IF;
8482:
8483: IF(p_gib.assy_header_id IS NULL OR
8484: p_gib.assy_header_id = -1) THEN
8485: -- Generate new header ID for assembly records because we want inventory
8486: -- to process assembly records, but not component records.
8487: SELECT mtl_material_transactions_s.nextval
8488: INTO p_gib.assy_header_id
8489: FROM DUAL;
8490: END IF;
8491:
8577: -1, NULL, SYSDATE), -- program_update_date
8578: p_gib.assy_header_id, -- transaction_header_id
8579: 'WIP Completion', -- source_code
8580: NVL(p_gib.move_profile.cmp_txn_id,-- completion_transaction_id
8581: mtl_material_transactions_s.nextval),
8582: wmti.transaction_id, -- move_transaction_id
8583: wmti.primary_item_id, -- inventory_item_id
8584: wdj.completion_subinventory, -- subinventory_code
8585: wdj.completion_locator_id, -- locator_id
8624: wmti.wip_entity_id, -- source_header_id
8625: wop.operation_seq_num, -- source_line_id
8626: p_gib.assy_header_id, -- transaction_batch_id
8627: WIP_CONSTANTS.ASSY_BATCH_SEQ,-- transaction_batch_seq
8628: mtl_material_transactions_s.nextval, -- transaction_interface_id
8629: -- populate this value for EZ completion/return because
8630: -- material processor need this value to enable quality
8631: wmti.qa_collection_id
8632: FROM wip_move_txn_interface wmti,
8717: -1, NULL, SYSDATE), -- program_update_date
8718: p_gib.assy_header_id, -- transaction_header_id
8719: 'WIP Completion', -- source_code
8720: NVL(p_gib.move_profile.cmp_txn_id, -- completion_transaction_id
8721: mtl_material_transactions_s.nextval),
8722: wmti.transaction_id, -- move_transaction_id
8723: wmti.primary_item_id, -- inventory_item_id
8724: wri.completion_subinventory,-- subinventory_code
8725: wri.completion_locator_id, -- locator_id
8758: wmti.wip_entity_id, -- source_header_id
8759: wop.operation_seq_num, -- source_line_id
8760: p_gib.assy_header_id, -- transaction_batch_id
8761: WIP_CONSTANTS.ASSY_BATCH_SEQ, -- transaction_batch_seq
8762: mtl_material_transactions_s.nextval, -- transaction_interface_id
8763: -- populate this value for EZ completion/return because
8764: -- material processor need this value to enable quality
8765: wmti.qa_collection_id
8766: FROM wip_move_txn_interface wmti,
8865: mti.last_update_date,
8866: mti.last_updated_by,
8867: mti.last_update_login,
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,
8866: mti.last_updated_by,
8867: mti.last_update_login,
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
10027: -- Update lock_flag to 'Y' to prevent inventory worker pick up these
10028: -- records. After we commit, assembly completion record will be in
10029: -- MMTT. There is a slim chance that inventory worker may pick up
10030: -- these records after we commit, and before we call processTemp.
10031: UPDATE mtl_material_transactions_temp
10032: SET lock_flag ='Y'
10033: WHERE transaction_header_id = l_gib.assy_header_id;
10034: END IF;
10035: -- Fixed bug 4361566. Commit to prevent dead lock from calling
10088: IF(l_gib.bf_mode = WIP_CONSTANTS.ONLINE) THEN
10089: IF(p_do_backflush = fnd_api.g_true) THEN
10090: IF(l_gib.mtl_header_id IS NULL OR
10091: l_gib.mtl_header_id = -1) THEN
10092: SELECT mtl_material_transactions_s.nextval
10093: INTO l_gib.mtl_header_id
10094: FROM DUAL;
10095: END IF;
10096:
10284: | Inventory processing |
10285: +---------------------*/
10286: SELECT count(*)
10287: INTO l_MMTT_record
10288: FROM mtl_material_transactions_temp
10289: WHERE transaction_header_id = l_gib.mtl_header_id;
10290:
10291: IF(l_MMTT_record > 0 AND
10292: l_gib.mtl_mode <> WIP_CONSTANTS.NO_PROCESSING) THEN
10690: sign(mmtt.transaction_quantity), -- transaction_quantity
10691: wmti.transaction_date
10692: FROM wip_move_txn_allocations wmta,
10693: wip_move_txn_interface wmti,
10694: mtl_material_transactions_temp mmtt
10695: WHERE wmti.transaction_id = wmta.transaction_id
10696: AND wmti.organization_id = wmta.organization_id
10697: AND wmti.transaction_id = mmtt.move_transaction_id
10698: AND mmtt.transaction_temp_id = p_tmp_id