[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: 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
179: wmti.fm_intraoperation_step_type fm_step,
180: wmti.to_operation_seq_num to_op,
181: wmti.to_intraoperation_step_type to_step
182: /* End fix of bug 5014211 */
183: FROM mtl_material_transactions mmt,
184: wip_move_txn_interface wmti
185: /* Bug 4891549 - Modified where clause to improve performance. */
186: WHERE mmt.transaction_source_type_id = TPS_INV_JOB_OR_SCHED
187: AND mmt.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
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
7491: AND transaction_type_id = p_txn_type_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,
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,
7668: mtl_material_transactions_temp mmtt2
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
8220:
8221: /* Generate a mtl txn header id IF one has not already been generated */
8222: IF(p_gib.mtl_header_id IS NULL OR
8223: p_gib.mtl_header_id = -1) THEN
8224: SELECT mtl_material_transactions_s.nextval
8225: INTO p_gib.mtl_header_id
8226: FROM DUAL;
8227: END IF;
8228:
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,
8293: DECODE(p_gib.program_id,-1,NULL,p_gib.program_id),
8294: DECODE(p_gib.request_id,
8295: -1,NULL,SYSDATE), -- program_update_date
8296: p_gib.mtl_header_id, -- transaction_header_id
8297: mtl_material_transactions_s.nextval, -- transaction_temp_id
8298: wmti.source_code,
8299: wmti.source_line_id,
8300: l_transaction_mode, -- transaction_mode
8301: wmti.primary_item_id, -- inventory_item_id
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,
8418: posting_flag,
8419: transaction_batch_id,
8420: transaction_batch_seq
8421: )
8422: SELECT mtl_material_transactions_s.nextval, -- material_alloc_id
8423: wmti.last_updated_by, -- last_updated_by --Fix for bug 5195072
8424: SYSDATE, -- last_update_date
8425: DECODE(p_gib.login_id,-1,NULL,p_gib.login_id),
8426: wmti.created_by, -- created_by --Fix for bug 5195072
8430: DECODE(p_gib.program_id,-1,NULL,p_gib.program_id),
8431: DECODE(p_gib.request_id,
8432: -1,NULL,SYSDATE), -- program_update_date
8433: p_gib.mtl_header_id, -- transaction_header_id
8434: mtl_material_transactions_s.nextval, -- transaction_temp_id
8435: wmti.source_code,
8436: wmti.source_line_id,
8437: l_transaction_mode, -- transaction_mode
8438: wmti.primary_item_id, -- inventory_item_id
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);
8717:
8718: /* Generate a mtl txn header id IF one has not already been generated */
8719: IF(p_gib.mtl_header_id IS NULL OR
8720: p_gib.mtl_header_id = -1) THEN
8721: SELECT mtl_material_transactions_s.nextval
8722: INTO p_gib.mtl_header_id
8723: FROM DUAL;
8724: END IF;
8725:
8726: IF(p_gib.assy_header_id IS NULL OR
8727: p_gib.assy_header_id = -1) THEN
8728: -- Generate new header ID for assembly records because we want inventory
8729: -- to process assembly records, but not component records.
8730: SELECT mtl_material_transactions_s.nextval
8731: INTO p_gib.assy_header_id
8732: FROM DUAL;
8733: END IF;
8734:
8811: qa_collection_id,
8812: FINAL_COMPLETION_FLAG -- FP bug 7315664 for bug 7281164
8813: )/*7314913: Inserting into MTI based on order of transaction_date*/
8814: /*Sequence and order by cannot exist in the same level of select, thus re-writing the select statement as sub-query*/
8815: select NVL(p_gib.move_profile.cmp_txn_id,mtl_material_transactions_s.nextval), mtl_material_transactions_s.nextval, t.*
8816: from
8817: (SELECT wmti.last_updated_by last_updated_by, -- last_update_by --Fix for bug 5195072
8818: SYSDATE last_update_date, -- last_update_date
8819: DECODE(p_gib.login_id, -1, NULL, p_gib.login_id) login_id,
8825: DECODE(p_gib.request_id, -1, NULL, SYSDATE) program_update_date,
8826: p_gib.assy_header_id transaction_header_id,
8827: 'WIP Completion' source_code,
8828: -- NVL(p_gib.move_profile.cmp_txn_id,-- completion_transaction_id
8829: -- mtl_material_transactions_s.nextval),
8830: wmti.transaction_id move_transaction_id,
8831: wmti.primary_item_id inventory_item_id,
8832: wdj.completion_subinventory subinventory_code,
8833: wdj.completion_locator_id locator_id,
8872: wmti.wip_entity_id source_header_id,
8873: wop.operation_seq_num source_line_id,
8874: p_gib.assy_header_id transaction_batch_id,
8875: WIP_CONSTANTS.ASSY_BATCH_SEQ transaction_batch_seq,
8876: -- mtl_material_transactions_s.nextval, -- transaction_interface_id
8877: -- populate this value for EZ completion/return because
8878: -- material processor need this value to enable quality
8879: wmti.qa_collection_id qa_collection_id,
8880: Decode(wmti.entity_type,5,'Y') -- FP bug 7315664 for bug 7281164
8958: -- processor need this value to enable quality
8959: qa_collection_id
8960: )/*7314913: Inserting into MTI based on order of transaction_date*/
8961: /*Sequence and order by cannot exist in the same level of select, thus re-writing the select statement as sub-query*/
8962: select NVL(p_gib.move_profile.cmp_txn_id,mtl_material_transactions_s.nextval), mtl_material_transactions_s.nextval, t.*
8963: from
8964: (SELECT wmti.last_updated_by last_update_by, --Fix for bug 5195072
8965: SYSDATE last_update_date,
8966: DECODE(p_gib.login_id, -1, NULL, p_gib.login_id) login_id,
8972: DECODE(p_gib.request_id, -1, NULL, SYSDATE) program_update_date,
8973: p_gib.assy_header_id transaction_header_id,
8974: 'WIP Completion' source_code,
8975: -- NVL(p_gib.move_profile.cmp_txn_id, -- completion_transaction_id
8976: -- mtl_material_transactions_s.nextval),
8977: wmti.transaction_id move_transaction_id,
8978: wmti.primary_item_id inventory_item_id,
8979: wri.completion_subinventory subinventory_code,
8980: wri.completion_locator_id locator_id,
9013: wmti.wip_entity_id source_header_id,
9014: wop.operation_seq_num source_line_id,
9015: p_gib.assy_header_id transaction_batch_id,
9016: WIP_CONSTANTS.ASSY_BATCH_SEQ transaction_batch_seq,
9017: -- mtl_material_transactions_s.nextval, -- transaction_interface_id
9018: -- populate this value for EZ completion/return because
9019: -- material processor need this value to enable quality
9020: wmti.qa_collection_id qa_collection_id
9021: FROM wip_move_txn_interface wmti,
9124: mti.last_update_date,
9125: mti.last_updated_by,
9126: mti.last_update_login,
9127: DECODE(msi.serial_number_control_code,
9128: WIP_CONSTANTS.FULL_SN, mtl_material_transactions_s.nextval,
9129: WIP_CONSTANTS.DYN_RCV_SN,mtl_material_transactions_s.nextval,
9130: NULL),
9131: 1, -- Bug 13734082 ,FP Bug for Populating orignation type as 'Production'
9132: mti.transaction_date
9125: mti.last_updated_by,
9126: mti.last_update_login,
9127: DECODE(msi.serial_number_control_code,
9128: WIP_CONSTANTS.FULL_SN, mtl_material_transactions_s.nextval,
9129: WIP_CONSTANTS.DYN_RCV_SN,mtl_material_transactions_s.nextval,
9130: NULL),
9131: 1, -- Bug 13734082 ,FP Bug for Populating orignation type as 'Production'
9132: mti.transaction_date
9133: FROM mtl_transactions_interface mti,
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
10441: IF(l_gib.bf_mode = WIP_CONSTANTS.ONLINE) THEN
10442: IF(p_do_backflush = fnd_api.g_true) THEN
10443: IF(l_gib.mtl_header_id IS NULL OR
10444: l_gib.mtl_header_id = -1) THEN
10445: SELECT mtl_material_transactions_s.nextval
10446: INTO l_gib.mtl_header_id
10447: FROM DUAL;
10448: END IF;
10449:
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;