[Home] [Help]
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
93: AND mmt.organization_id = wmti.organization_id
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
121: AND mmt.organization_id = wmti.organization_id
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
155: AND mmtt.organization_id = wmti.organization_id
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
188: AND mmt.organization_id = wmti.organization_id
438: x_returnStatus => l_returnStatus);
439: END IF;
440: -- Update status of move record to error if components fail inventory
441: -- validation.
442: UPDATE wip_move_txn_interface wmti
443: SET wmti.process_status = WIP_CONSTANTS.ERROR
444: WHERE wmti.group_id = p_group_id
445: AND EXISTS
446: (SELECT 1
451:
452: IF (l_logLevel <= wip_constants.full_logging) THEN
453: fnd_message.set_name('WIP', 'WIP_UPDATED_ROWS');
454: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
455: fnd_message.set_token('ENTITY2', 'WIP_MOVE_TXN_INTERFACE');
456: l_msg := fnd_message.get;
457: wip_logger.log(p_msg => l_msg,
458: x_returnStatus => l_returnStatus);
459: END IF;
688: wop.organization_id,
689: wop.repetitive_schedule_id
690: FROM wip_operations wop,
691: wip_repetitive_schedules wrs,
692: wip_move_txn_interface wmti
693: WHERE wop.organization_id = wmti.organization_id
694: AND wop.wip_entity_id = wmti.wip_entity_id
695: AND wmti.group_id = p_group_id
696: AND TRUNC(wmti.transaction_date) = TRUNC(p_txn_date)
1072: END schedule_alloc;
1073:
1074: /*****************************************************************************
1075: * This procedure is equivalent to witoc_insert_alloc_child in wiltps.ppc
1076: * This procedure is used to insert child record into WIP_MOVE_TXN_INTERFACE
1077: * and WIP_MOVE_TXN_ALLOCATIONS
1078: ****************************************************************************/
1079: PROCEDURE insert_alloc_child(p_org_id IN NUMBER,
1080: p_scheID IN NUMBER,
1477: DECODE(p_gib.application_id, -1, NULL, p_gib.application_id),
1478: DECODE(p_gib.program_id, -1, NULL, p_gib.program_id),
1479: DECODE(p_gib.request_id, -1, NULL, SYSDATE)
1480: FROM wip_operations wop1,
1481: wip_move_txn_interface wmti1,
1482: wip_move_txn_allocations wma1
1483: WHERE wop1.rowid = wop.rowid
1484: -- The WO rows to be updated are identIFied by the rowids.
1485: -- For each such row, go back and sum the quantities from WMTI
1498: -- must be passed.
1499: WHERE wop.rowid =
1500: (SELECT wop2.rowid
1501: FROM wip_operations wop2,
1502: wip_move_txn_interface wmti2,
1503: wip_move_txn_allocations wma2
1504: WHERE wmti2.group_id = p_gib.group_id
1505: AND TRUNC(wmti2.transaction_date) = TRUNC(p_gib.txn_date)
1506: AND wmti2.transaction_id = p_txn_id
1577: WIP_CONSTANTS.child_txn),
1578: WIP_CONSTANTS.parent_txn) oc_txn_type,
1579: transaction_type txn_type,
1580: transaction_date txn_date
1581: FROM wip_move_txn_interface
1582: WHERE group_id = p_group_id
1583: AND TRUNC(transaction_date) = TRUNC(p_txn_date)
1584: AND process_phase = WIP_CONSTANTS.MOVE_PROC
1585: AND process_status = WIP_CONSTANTS.RUNNING
1729:
1730: IF (x_returnStatus <> fnd_api.g_ret_sts_success) THEN
1731:
1732: IF (l_rs_txn.past_timeout = 0) THEN -- not time out yet
1733: UPDATE wip_move_txn_interface
1734: SET process_status = WIP_CONSTANTS.PENDING,
1735: group_id = NULL,
1736: transaction_id = NULL
1737: WHERE transaction_id = l_rs_txn.txn_id
1841: l_proc_status = TVE_NO_MOVE_ALLOC) THEN
1842:
1843: IF(p_gib.move_mode = WIP_CONSTANTS.BACKGROUND) THEN
1844: IF (l_rs_txn.past_timeout = 0) THEN -- not time out yet
1845: UPDATE wip_move_txn_interface
1846: SET process_status = WIP_CONSTANTS.PENDING,
1847: group_id = NULL,
1848: transaction_id = NULL
1849: WHERE transaction_id = l_rs_txn.txn_id
1867:
1868: ELSIF(l_proc_status = WIP_CONSTANTS.ERROR) THEN
1869:
1870: -- update move_interface table
1871: UPDATE wip_move_txn_interface
1872: SET process_status = WIP_CONSTANTS.ERROR
1873: WHERE transaction_id = l_rs_txn.txn_id
1874: AND group_id = p_gib.group_id;
1875:
1887: EXCEPTION
1888: WHEN fnd_api.g_exc_unexpected_error THEN
1889: IF(p_gib.move_mode = WIP_CONSTANTS.BACKGROUND) THEN
1890: IF (l_rs_txn.past_timeout = 0) THEN -- not time out yet
1891: UPDATE wip_move_txn_interface
1892: SET process_status = WIP_CONSTANTS.PENDING,
1893: group_id = NULL,
1894: transaction_id = NULL
1895: WHERE transaction_id = l_rs_txn.txn_id
1894: transaction_id = NULL
1895: WHERE transaction_id = l_rs_txn.txn_id
1896: AND group_id = p_gib.group_id;
1897: ELSE -- already time out
1898: UPDATE wip_move_txn_interface
1899: SET process_status = WIP_CONSTANTS.ERROR
1900: WHERE transaction_id = l_rs_txn.txn_id
1901: AND group_id = p_gib.group_id;
1902:
1933: p_gib.request_id,
1934: p_gib.application_id,
1935: p_gib.program_id,
1936: SYSDATE -- program_update_date
1937: FROM wip_move_txn_interface wmti
1938: WHERE wmti.transaction_id = l_rs_txn.txn_id
1939: AND wmti.group_id = p_gib.group_id;
1940: END IF; -- time out check
1941: ELSE--Online processing
2239: wmti.transaction_type txn_type,
2240: wmti.primary_item_id item_id,
2241: NVL(wdj.serialization_start_op, 0) sn_start_op,
2242: wmti.transaction_date txn_date
2243: FROM wip_move_txn_interface wmti,
2244: wip_discrete_jobs wdj
2245: WHERE wmti.wip_entity_id = wdj.wip_entity_id
2246: AND wmti.group_id = p_group_id
2247: AND TRUNC(wmti.transaction_date) = TRUNC(p_txn_date)
2267: NVL(msn.operation_seq_num, p_start_op) sn_op,
2268: NVL(msn.intraoperation_step_type, WIP_CONSTANTS.QUEUE) sn_step
2269: FROM mtl_serial_numbers msn,
2270: wip_serial_move_interface wsmi,
2271: wip_move_txn_interface wmti
2272: WHERE msn.inventory_item_id = wmti.primary_item_id
2273: AND msn.current_organization_id = wmti.organization_id
2274: -- msn.wip_entity_id will be null after complete to inventory. Need this
2275: -- check to support return transaction.
2442:
2443: IF(l_notenough) THEN
2444: IF(p_gib.move_mode = WIP_CONSTANTS.BACKGROUND) THEN
2445: IF (l_dj_txn.past_timeout = 0) THEN -- not time out yet
2446: UPDATE wip_move_txn_interface
2447: SET process_status = WIP_CONSTANTS.PENDING,
2448: group_id = NULL,
2449: transaction_id = NULL
2450: WHERE transaction_id = l_dj_txn.txn_id
2471: x_returnStatus => x_returnStatus);
2472:
2473: IF (x_returnStatus <> fnd_api.g_ret_sts_success) THEN
2474: IF (l_dj_txn.past_timeout = 0) THEN -- not time out yet
2475: UPDATE wip_move_txn_interface
2476: SET process_status = WIP_CONSTANTS.PENDING,
2477: group_id = NULL,
2478: transaction_id = NULL
2479: WHERE transaction_id = l_dj_txn.txn_id
2574: to this savepoint check_qty_dj_savepoint to avoid serial number being updated for failed move transaction*/
2575: rollback to check_qty_dj_savepoint;
2576:
2577: IF (l_dj_txn.past_timeout = 0) THEN -- not time out yet
2578: UPDATE wip_move_txn_interface
2579: SET process_status = WIP_CONSTANTS.PENDING,
2580: group_id = NULL,
2581: transaction_id = NULL
2582: WHERE transaction_id = l_dj_txn.txn_id
2581: transaction_id = NULL
2582: WHERE transaction_id = l_dj_txn.txn_id
2583: AND group_id = p_gib.group_id;
2584: ELSE -- already time out
2585: UPDATE wip_move_txn_interface
2586: SET process_status = WIP_CONSTANTS.ERROR
2587: WHERE transaction_id = l_dj_txn.txn_id
2588: AND group_id = p_gib.group_id;
2589: -- Get error from message stack
2619: p_gib.request_id,
2620: p_gib.application_id,
2621: p_gib.program_id,
2622: SYSDATE -- program_update_date
2623: FROM wip_move_txn_interface wmti
2624: WHERE wmti.transaction_id = l_dj_txn.txn_id
2625: AND wmti.group_id = p_gib.group_id;
2626: END IF; -- time out check
2627: ELSE--Online processing
2758: INTO l_mv_item,
2759: l_mv_lot,
2760: l_po_item,
2761: l_po_lot
2762: FROM wip_move_txn_interface wmti,
2763: wip_operation_resources wor
2764: WHERE wmti.transaction_id = p_gib.group_id
2765: AND wmti.group_id = p_gib.group_id
2766: AND wor.organization_id = l_move.org_id
2778: SELECT SUM(DECODE(transaction_type, WIP_CONSTANTS.COMP_TXN, 1, 0)),
2779: SUM(DECODE(transaction_type, WIP_CONSTANTS.RET_TXN, 1, 0))
2780: INTO l_completion,
2781: l_return
2782: FROM wip_move_txn_interface wmti
2783: WHERE wmti.group_id = p_gib.group_id
2784: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
2785: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
2786: AND wmti.process_status = WIP_CONSTANTS.RUNNING;
2810: INTO l_mv_item,
2811: l_mv_lot,
2812: l_po_item,
2813: l_po_lot
2814: FROM wip_move_txn_interface wmti,
2815: wip_operation_resources wor
2816: WHERE wmti.group_id = p_gib.group_id
2817: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
2818: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
2944: wsmi.program_update_date,
2945: -- Add this column for OSFM serial support project.
2946: wsmi.gen_object_id
2947: FROM wip_serial_move_interface wsmi,
2948: wip_move_txn_interface wmti
2949: WHERE wsmi.transaction_id = wmti.transaction_id
2950: AND wmti.group_id = p_gib.group_id
2951: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
2952: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
3083: wdj.start_quantity,
3084: wmti.batch_id,
3085: wmti.employee_id,
3086: wmti.completed_instructions
3087: FROM wip_move_txn_interface wmti,
3088: wip_discrete_jobs wdj
3089: WHERE wmti.group_id = p_gib.group_id
3090: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
3091: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
3217: wmti.qa_collection_id,
3218: wmti.overcompletion_transaction_qty,
3219: wmti.overcompletion_primary_qty,
3220: wmti.overcompletion_transaction_id
3221: FROM wip_move_txn_interface wmti
3222: WHERE wmti.group_id = p_gib.group_id
3223: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
3224: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
3225: AND wmti.process_status = WIP_CONSTANTS.RUNNING
3262: END insert_txn_history;
3263:
3264: /*****************************************************************************
3265: * This procedure is equivalent to witoc_delete_child in wiltps5.ppc
3266: * This procedure is used to delete child record from WIP_MOVE_TXN_INTERFACE
3267: * table where the child rows have fm_op and to_op to be the first operation
3268: * and the step types to be Queue
3269: ****************************************************************************/
3270: PROCEDURE delete_child_txn(p_gib IN group_rec_t,
3288: x_returnStatus => l_returnStatus);
3289: END IF;
3290:
3291: -- delete child records from WIP
3292: DELETE FROM wip_move_txn_interface wmti
3293: WHERE wmti.group_id = p_gib.group_id
3294: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
3295: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
3296: AND wmti.process_status = WIP_CONSTANTS.RUNNING
3389: -- Per item basis type for discrete jobs
3390: IF(l_move.jobTxn = WIP_CONSTANTS.YES AND
3391: l_move.rsrcItem = WIP_CONSTANTS.YES) THEN
3392: /* Fix for bug 4036149: Added leading hint to drive from the table
3393: wip_move_txn_interface to improve performance */
3394: INSERT INTO wip_cost_txn_interface
3395: (transaction_id,
3396: last_update_date,
3397: last_updated_by,
3533: cst_activities ca,
3534: wip_operation_resources wor,
3535: wip_discrete_jobs wdj,
3536: wip_operations wop,
3537: wip_move_txn_interface wmti
3538: WHERE wmti.group_id = p_gib.group_id
3539: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
3540: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
3541: AND wmti.process_status = WIP_CONSTANTS.RUNNING
3745: cst_activities ca,
3746: wip_operation_resources wor,
3747: wip_move_txn_allocations wma,
3748: wip_operations wop,
3749: wip_move_txn_interface wmti
3750: WHERE wmti.group_id = p_gib.group_id
3751: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
3752: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
3753: AND wmti.process_status = WIP_CONSTANTS.RUNNING
3986: cst_activities ca,
3987: wip_operation_resources wor,
3988: wip_discrete_jobs wdj,
3989: wip_operations wop,
3990: wip_move_txn_interface wmti
3991: WHERE wmti.group_id = p_gib.group_id
3992: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
3993: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
3994: AND wmti.process_status = WIP_CONSTANTS.RUNNING
4197: cst_activities ca,
4198: wip_operation_resources wor,
4199: wip_move_txn_allocations wma,
4200: wip_operations wop,
4201: wip_move_txn_interface wmti
4202: WHERE wmti.group_id = p_gib.group_id
4203: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
4204: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
4205: AND wmti.process_status = WIP_CONSTANTS.RUNNING
4408: FROM wip_operation_resources wor,
4409: wip_operations wop,
4410: wip_move_txn_allocations wma,
4411: wip_cost_txn_interface wci,
4412: wip_move_txn_interface wmti
4413: WHERE wmti.group_id = p_gib.group_id
4414: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
4415: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
4416: AND wmti.process_status = WIP_CONSTANTS.RUNNING
4559: 1, 1,
4560: -1,-1) -- primary_quantity
4561: FROM wip_operation_resources wor,
4562: wip_move_txn_allocations wmta,
4563: wip_move_txn_interface wmti,
4564: wip_cost_txn_interface wcti
4565: WHERE wcti.group_id = p_gib.group_id
4566: AND wcti.process_phase = WIP_CONSTANTS.RES_PROC
4567: AND wcti.process_status = WIP_CONSTANTS.PENDING
4822: FROM bom_departments bd,
4823: wip_operations wop,
4824: wip_operation_resources wor,
4825: wip_discrete_jobs wdj,
4826: wip_move_txn_interface wmti,
4827: wip_requirement_operations WRO
4828: WHERE wmti.group_id = p_gib.group_id
4829: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
4830: AND wdj.wip_entity_id = wmti.wip_entity_id
5043: NULL -- repetitive_schedule_id
5044: FROM bom_departments bd,
5045: wip_move_txn_allocations wma,
5046: wip_operations wop,
5047: wip_move_txn_interface wmti,
5048: wip_operation_resources wor
5049: WHERE wmti.group_id = p_gib.group_id
5050: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
5051: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
5281: FROM bom_departments bd,
5282: wip_operations wop,
5283: wip_operation_resources wor,
5284: wip_discrete_jobs wdj,
5285: wip_move_txn_interface wmti
5286: WHERE wmti.group_id = p_gib.group_id
5287: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
5288: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
5289: AND wmti.process_status = WIP_CONSTANTS.RUNNING
5495: wma.repetitive_schedule_id
5496: FROM bom_departments bd,
5497: wip_move_txn_allocations wma,
5498: wip_operations wop,
5499: wip_move_txn_interface wmti,
5500: wip_operation_resources wor
5501: WHERE wmti.group_id = p_gib.group_id
5502: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
5503: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
5690: -1, DECODE(SIGN(MAX(wop.quantity_completed)),1,-1,0))
5691: FROM wip_move_txn_allocations wma,
5692: wip_operations wop,
5693: wip_cost_txn_interface wci,
5694: wip_move_txn_interface wmti
5695: WHERE wmti.group_id = p_gib.group_id
5696: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
5697: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
5698: AND wmti.process_status = WIP_CONSTANTS.RUNNING
5833: wmti.repetitive_schedule_id rep_sched_id,
5834: wmti.organization_id org_id,
5835: wo.operation_seq_num op_seq_num,
5836: wmti.overcompletion_primary_qty oc_qty
5837: FROM wip_move_txn_interface wmti,
5838: wip_operations wo,
5839: wip_operation_resources wor,
5840: wip_discrete_jobs wdj
5841: WHERE wmti.group_id = p_group_id
5862: wmti.repetitive_schedule_id rep_sched_id,
5863: wmti.organization_id org_id,
5864: wo.operation_seq_num op_seq_num,
5865: wmti.overcompletion_primary_qty oc_qty
5866: FROM wip_move_txn_interface wmti,
5867: wip_operations wo,
5868: wip_operation_resources wor,
5869: wip_repetitive_schedules wrs
5870: WHERE wmti.group_id = p_group_id
6054: wdj.project_id,
6055: wdj.task_id,
6056: DECODE(wdj.project_id,NULL,NULL,'Y')-- project_accounting_context
6057: FROM /* bugfix 5129403: modified the order in the from clause */
6058: wip_move_txn_interface wmti,
6059: wip_operation_resources wor,
6060: bom_resources br,
6061: mtl_system_items msi,
6062: mtl_parameters mp,
6282: wip_operation_resources wor,
6283: wip_operations wo1,
6284: wip_operations wo2,
6285: wip_move_txn_allocations wma,
6286: wip_move_txn_interface wmti,
6287: wip_parameters wp /*ER 4276433*/
6288: WHERE wmti.group_id = p_gib.group_id
6289: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
6290: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
6478: wdj.project_id,
6479: wdj.task_id,
6480: DECODE(wdj.project_id,NULL,NULL,'Y')-- project_accounting_context
6481: FROM /* bugfix 5129403: modified the order in the from clause */
6482: wip_move_txn_interface wmti,
6483: wip_operation_resources wor,
6484: bom_resources br,
6485: mtl_system_items msi,
6486: mtl_parameters mp,
6724: wip_entities we,
6725: wip_operations wo1,
6726: wip_operations wo2,
6727: wip_move_txn_allocations wma,
6728: wip_move_txn_interface wmti,
6729: wip_parameters wp /*ER 4276433*/
6730: WHERE wmti.group_id = p_gib.group_id
6731: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
6732: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
6907: FROM bom_resources br,
6908: mtl_units_of_measure muom,
6909: wip_operation_resources wor,
6910: wip_operations wo,
6911: wip_move_txn_interface wmti
6912: WHERE wmti.group_id = p_group_id
6913: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
6914: AND wmti.process_status = WIP_CONSTANTS.RUNNING
6915: AND TRUNC(wmti.transaction_date)= TRUNC(p_txn_date)
7205: DECODE(p_gib.program_id,-1,NULL,p_gib.program_id),
7206: DECODE(p_gib.request_id,-1,NULL,SYSDATE)-- program_update_date
7207: FROM wip_operations wop1,
7208: wip_move_txn_allocations wma1,
7209: wip_move_txn_interface wmti
7210: WHERE wop1.rowid = wop.rowid
7211: AND wmti.group_id = p_gib.group_id
7212: AND (p_txn_id IS NULL OR wmti.transaction_id = p_txn_id)
7213: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
7254: WHERE wop.rowid IN
7255: (SELECT wop2.rowid
7256: FROM wip_operations wop2,
7257: wip_move_txn_allocations wma2,
7258: wip_move_txn_interface wmti
7259: WHERE wop2.organization_id = wmti.organization_id
7260: AND wmti.group_id = p_gib.group_id
7261: AND (p_txn_id IS NULL OR wmti.transaction_id = p_txn_id)
7262: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
7558: The original condition was treating records inserted from the receiving
7559: transactions for osp item as records inserted from the open interface
7560: process and thus validating against cst_comp_snap_temp table and erroring
7561: out as there is no record. This fix would treat records inserted into
7562: wip_move_txn_interface by receiving transaction as created from the WIP
7563: and thus will create a record in the cst_comp_snap_temp.
7564: */
7565:
7566: CURSOR c_txns IS
7573: 'WIP', -1,
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)
7634:
7635: -- check for snapshot transactions through the interface
7636: SELECT COUNT(*)
7637: INTO l_num_snapshot
7638: FROM wip_move_txn_interface wmti,
7639: mtl_parameters mp
7640: -- wip_parameters wp
7641: WHERE wmti.group_id = p_group_id
7642: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
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
7669: WHERE wmti.group_id = p_group_id
7670: AND TRUNC(wmti.transaction_date) = TRUNC(p_txn_date)
7762: PROCEDURE update_wo_and_snapshot(p_gib IN group_rec_t,
7763: x_returnStatus OUT NOCOPY VARCHAR2) IS
7764: CURSOR c_move_rec IS
7765: SELECT wmti.transaction_id txn_id
7766: FROM wip_move_txn_interface wmti
7767: WHERE wmti.group_id = p_gib.group_id
7768: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
7769: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
7770: AND wmti.process_status = WIP_CONSTANTS.RUNNING
7877: CURSOR c_mtl_alloc 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
7908: -1 * DECODE(wmti.entity_type,
7909: WIP_CONSTANTS.REPETITIVE, wmta.primary_quantity,
7910: wmti.primary_quantity)) changed_qty,
7911: GREATEST(wmti.fm_operation_seq_num, wmti.to_operation_seq_num) fm_op
7912: FROM wip_move_txn_interface wmti,
7913: wip_move_txn_allocations wmta,
7914: wip_parameters mp
7915: WHERE wmti.transaction_id = wmta.transaction_id (+)
7916: AND wmti.organization_id = wmta.organization_id (+)
7996: l_move.scrapTxn <> WIP_CONSTANTS.NO) THEN -- Indeterminate
7997:
7998: SELECT COUNT(1)
7999: INTO l_scrap_flag
8000: FROM wip_move_txn_interface
8001: WHERE group_id = p_gib.group_id
8002: AND TRUNC(transaction_date) = TRUNC(p_gib.txn_date)
8003: AND process_phase = WIP_CONSTANTS.MOVE_PROC
8004: AND process_status = WIP_CONSTANTS.RUNNING
8069: DECODE(p_gib.application_id,-1,NULL,p_gib.application_id),
8070: DECODE(p_gib.program_id,-1,NULL,p_gib.program_id),
8071: DECODE(p_gib.request_id,
8072: -1,NULL,SYSDATE)-- program_update_date
8073: FROM wip_discrete_jobs wdj1, wip_move_txn_interface wmti
8074: WHERE wdj1.rowid = wdj.rowid
8075: AND wmti.group_id = p_gib.group_id
8076: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
8077: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
8085: )
8086: WHERE wdj.rowid IN
8087: (SELECT wdj2.rowid
8088: FROM wip_discrete_jobs wdj2,
8089: wip_move_txn_interface wmti
8090: WHERE wmti.group_id = p_gib.group_id
8091: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
8092: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
8093: AND wmti.process_status = WIP_CONSTANTS.RUNNING
8114: FROM DUAL)
8115: WHERE wdj.rowid IN
8116: (SELECT wdj2.rowid
8117: FROM wip_discrete_jobs wdj2,
8118: wip_move_txn_interface wmti
8119: WHERE wmti.group_id = p_gib.group_id
8120: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
8121: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
8122: AND wmti.process_status = WIP_CONSTANTS.RUNNING
8144: date_completed = null
8145: WHERE wdj.rowid IN
8146: (SELECT wdj2.rowid
8147: FROM wip_discrete_jobs wdj2,
8148: wip_move_txn_interface wmti
8149: WHERE wmti.group_id = p_gib.group_id
8150: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
8151: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
8152: AND wmti.process_status = WIP_CONSTANTS.RUNNING
8194: WIP_CONSTANTS.COMP_CHRG)
8195: AND wrs.rowid IN
8196: (SELECT wrs2.rowid
8197: FROM wip_repetitive_schedules wrs2,
8198: wip_move_txn_interface wmti
8199: WHERE wmti.group_id = p_gib.group_id
8200: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
8201: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
8202: AND wmti.process_status = WIP_CONSTANTS.RUNNING
8342: wdj.task_id,
8343: p_gib.mtl_header_id, -- transaction_batch_id
8344: wmti.qa_collection_id, /*Added for Bug 7136450 (FP of 7126588 )*/
8345: WIP_CONSTANTS.ASSY_BATCH_SEQ -- transaction_batch_seq
8346: FROM wip_move_txn_interface wmti,
8347: mtl_system_items msi,
8348: wip_discrete_jobs wdj
8349: WHERE wmti.group_id = p_gib.group_id
8350: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
8476: 'N', -- lock_flag
8477: 'Y', -- posting_flag
8478: p_gib.mtl_header_id, -- transaction_batch_id
8479: WIP_CONSTANTS.ASSY_BATCH_SEQ -- transaction_batch_seq
8480: FROM wip_move_txn_interface wmti,
8481: mtl_system_items msi,
8482: wip_repetitive_schedules wrs
8483: WHERE wmti.group_id = p_gib.group_id
8484: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
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
8881: FROM wip_move_txn_interface wmti,
8882: mtl_item_locations mil,
8883: wip_operations wop,
8884: mtl_system_items msi,
8885: wip_discrete_jobs wdj
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,
9022: wip_operations wop,
9023: mtl_system_items msi,
9024: wip_repetitive_schedules wrs,
9025: wip_repetitive_items wri
9494: DECODE(p_gib.application_id,-1,NULL,p_gib.application_id),
9495: DECODE(p_gib.program_id,-1,NULL,p_gib.program_id),
9496: DECODE(p_gib.request_id,-1,NULL,SYSDATE)
9497: FROM wip_requirement_operations wro1,
9498: wip_move_txn_interface wmti1,
9499: wip_move_txn_allocations wma1
9500: WHERE wro1.rowid = wro.rowid
9501: -- The WO rows to be updated are identIFied by the rowids.
9502: -- For each such row, go back and sum the quantities from WMTI
9515: )
9516: WHERE wro.rowid in
9517: (SELECT wro2.rowid
9518: FROM wip_requirement_operations wro2,
9519: wip_move_txn_interface wmti2,
9520: wip_move_txn_allocations wma2
9521: WHERE wmti2.group_id = p_gib.group_id
9522: AND wmti2.process_phase = WIP_CONSTANTS.MOVE_PROC
9523: AND wmti2.process_status = WIP_CONSTANTS.RUNNING
9880: wmti.to_intraoperation_step_type to_step,
9881: wmti.transaction_id txn_id,
9882: wmti.reason_id reason_id,
9883: wmti.reference reference
9884: FROM wip_move_txn_interface wmti
9885: WHERE wmti.group_id = p_group_id
9886: AND TRUNC(wmti.transaction_date)= TRUNC(p_txn_date)
9887: AND wmti.process_phase = WIP_CONSTANTS.BF_SETUP
9888: AND wmti.process_status = WIP_CONSTANTS.RUNNING;
9902: fm_operation_seq_num fm_op,
9903: fm_intraoperation_step_type fm_step,
9904: to_operation_seq_num to_op,
9905: to_intraoperation_step_type to_step
9906: FROM wip_move_txn_interface
9907: WHERE group_id = p_group_id
9908: /* Comment out the check below because UI may need to use validation logic */
9909: -- AND process_phase = WIP_CONSTANTS.MOVE_PROC
9910: AND process_status = WIP_CONSTANTS.RUNNING;
9910: AND process_status = WIP_CONSTANTS.RUNNING;
9911:
9912: CURSOR c_qa_id(p_txn_date DATE) IS
9913: SELECT qa_collection_id
9914: FROM wip_move_txn_interface
9915: WHERE group_id = p_group_id
9916: AND process_phase = WIP_CONSTANTS.MOVE_PROC
9917: AND process_status = WIP_CONSTANTS.RUNNING
9918: AND TRUNC(transaction_date)= TRUNC(p_txn_date)
9922: AND qa_collection_id IS NOT NULL;
9923:
9924: CURSOR c_txn_date IS
9925: SELECT DISTINCT TRUNC(transaction_date)
9926: FROM wip_move_txn_interface
9927: WHERE group_id = p_group_id
9928: AND process_status = WIP_CONSTANTS.RUNNING
9929: order by 1; /*Bug 7314913: Added order by clause*/
9930:
9931: /*Bug 13992087: This cursor returns jobs submitted for EZ_Completion (Move & Complete)*/
9932: /*Bug 16483751: Added the check so that EZ completions for repetetive schedules do not error out */
9933: CURSOR c_jobs IS
9934: select wmti.wip_entity_id, wmti.transaction_id txn_id, wmti.transaction_type txn_type
9935: from wip_move_txn_interface wmti,wip_entities we
9936: where wmti.group_id = p_group_id
9937: AND wmti.process_status = WIP_CONSTANTS.RUNNING
9938: AND wmti.transaction_type = WIP_CONSTANTS.COMP_TXN
9939: AND we.wip_entity_id=wmti.wip_entity_id
10066: l_gib.mtl_mode := p_mtl_mode; -- l_mtl_mode;
10067: l_gib.process_phase := WIP_CONSTANTS.MOVE_PROC;
10068: l_gib.process_status := WIP_CONSTANTS.RUNNING;
10069: l_gib.time_out := p_time_out;
10070: l_gib.intf_tbl_name := 'WIP_MOVE_TXN_INTERFACE';
10071: l_gib.user_id := l_move_online.user_id;
10072: l_gib.login_id := l_move_online.login_id;
10073: l_gib.request_id := l_move_online.req_id;
10074: l_gib.application_id := l_move_online.appl_id;
10084: l_gib.mtl_mode := p_mtl_mode; -- l_mtl_mode;
10085: l_gib.process_phase := p_proc_phase;
10086: l_gib.process_status := WIP_CONSTANTS.RUNNING;
10087: l_gib.time_out := p_time_out;
10088: l_gib.intf_tbl_name := 'WIP_MOVE_TXN_INTERFACE';
10089: l_gib.user_id := fnd_global.user_id;
10090: l_gib.login_id := fnd_global.conc_login_id;
10091: l_gib.request_id := fnd_global.conc_request_id;
10092: l_gib.application_id := fnd_global.prog_appl_id;
10109: wip_logger.log('Job is locked', l_returnStatus);
10110: IF(p_move_mode = WIP_CONSTANTS.BACKGROUND) THEN
10111: wip_logger.log('background mode is ON', l_returnStatus);
10112: /* Update process status to error */
10113: UPDATE wip_move_txn_interface
10114: SET process_status = WIP_CONSTANTS.ERROR
10115: WHERE group_id = p_group_id
10116: AND process_status = WIP_CONSTANTS.RUNNING
10117: AND transaction_id = l_jobs.txn_id;
10148: l_gib.request_id,
10149: l_gib.application_id,
10150: l_gib.program_id,
10151: SYSDATE -- program_update_date
10152: FROM wip_move_txn_interface wmti
10153: WHERE wmti.group_id = p_group_id
10154: AND transaction_id = l_jobs.txn_id;
10155: wip_logger.log('Inserted error message into WTIE', l_returnStatus);
10156: END IF;
10168: p_initMsgList => fnd_api.g_true);
10169:
10170: -- There is no return status from this routine. IF some record error out,
10171: -- just neglect it and continue validating other records. The error record
10172: -- will have process_status in WIP_MOVE_TXN_INTERFACE equal to (3) or
10173: -- WIP_CONSTANTS.ERROR, and those error records will not be picked up by
10174: -- move_txns. The error message also show up in WIP_TXN_INTERFACE_ERRORS
10175: END IF; -- Move Validation
10176:
10191: 0)),
10192: SUM(DECODE(entity_type,WIP_CONSTANTS.REPETITIVE,1,0))
10193: INTO l_job,
10194: l_sche
10195: FROM wip_move_txn_interface wmti
10196: WHERE wmti.group_id = l_gib.group_id
10197: AND TRUNC(transaction_date)= TRUNC(l_gib.txn_date)
10198: AND process_phase = WIP_CONSTANTS.MOVE_PROC
10199: AND process_status = WIP_CONSTANTS.RUNNING;
10356: wip_logger.log(p_msg => 'QA enable success',
10357: x_returnStatus => l_returnStatus);
10358: END IF;
10359:
10360: UPDATE wip_move_txn_interface
10361: SET process_phase = WIP_CONSTANTS.BF_SETUP
10362: WHERE group_id = p_group_id
10363: AND process_phase = WIP_CONSTANTS.MOVE_PROC
10364: AND process_status = WIP_CONSTANTS.RUNNING
10367: -- IF debug message level = 2, write statement below to log file
10368: IF (l_logLevel <= wip_constants.full_logging) THEN
10369: fnd_message.set_name('WIP', 'WIP_UPDATED_ROWS');
10370: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
10371: fnd_message.set_token('ENTITY2', 'WIP_MOVE_TXN_INTERFACE');
10372: l_msg := fnd_message.get;
10373: wip_logger.log(p_msg => l_msg,
10374: x_returnStatus => l_returnStatus);
10375: END IF;
10547: l_errMsg := fnd_message.get;
10548: END IF;
10549: IF(p_move_mode = WIP_CONSTANTS.BACKGROUND) THEN
10550: /* Update process status to error */
10551: UPDATE wip_move_txn_interface
10552: SET process_status = WIP_CONSTANTS.ERROR
10553: WHERE group_id = p_group_id
10554: AND process_status = WIP_CONSTANTS.RUNNING
10555: AND transaction_id = l_backflush.txn_id;
10580: l_gib.request_id,
10581: l_gib.application_id,
10582: l_gib.program_id,
10583: SYSDATE -- program_update_date
10584: FROM wip_move_txn_interface wmti
10585: WHERE wmti.group_id = p_group_id
10586: AND transaction_id = l_backflush.txn_id;
10587:
10588: ELSE -- move mode is online, write to log file
10660: raise fnd_api.g_exc_unexpected_error;
10661: END IF;
10662: END IF; -- there is the records in MMTT with this header_id
10663: ELSE -- backflush is background
10664: UPDATE wip_move_txn_interface
10665: SET group_id = NULL,
10666: process_phase = WIP_CONSTANTS.BF_SETUP,
10667: process_status = WIP_CONSTANTS.PENDING
10668: WHERE group_id = p_group_id
10697: /*Update the lock_flag in MMTT to be 'N' if failed in Move Processing Phase*/
10698: clean_up(p_assy_header_id => l_gib.assy_header_id);
10699:
10700: /* Update process status to error */
10701: UPDATE wip_move_txn_interface
10702: SET process_status = WIP_CONSTANTS.ERROR
10703: WHERE group_id = p_group_id
10704: AND process_status = WIP_CONSTANTS.RUNNING
10705: AND TRUNC(transaction_date)= TRUNC(l_gib.txn_date);
10738: l_gib.request_id,
10739: l_gib.application_id,
10740: l_gib.program_id,
10741: SYSDATE -- program_update_date
10742: FROM wip_move_txn_interface wmti
10743: WHERE wmti.group_id = p_group_id
10744: AND TRUNC(wmti.transaction_date) = TRUNC(l_gib.txn_date)
10745: AND NOT EXISTS (SELECT 1
10746: FROM wip_txn_interface_errors wtie
10771: ROLLBACK TO SAVEPOINT s_move_proc;
10772: IF(p_move_mode = WIP_CONSTANTS.BACKGROUND) THEN
10773:
10774: /* Update process status to error */
10775: UPDATE wip_move_txn_interface
10776: SET process_status = WIP_CONSTANTS.ERROR
10777: WHERE group_id = p_group_id
10778: AND process_status = WIP_CONSTANTS.RUNNING
10779: AND TRUNC(transaction_date)= TRUNC(l_gib.txn_date);
10806: l_gib.request_id,
10807: l_gib.application_id,
10808: l_gib.program_id,
10809: SYSDATE -- program_update_date
10810: FROM wip_move_txn_interface wmti
10811: WHERE wmti.group_id = p_group_id
10812: AND TRUNC(wmti.transaction_date) = TRUNC(l_gib.txn_date)
10813: AND NOT EXISTS (SELECT 1
10814: FROM wip_txn_interface_errors wtie
10836: UPDATE mtl_serial_numbers msn1
10837: SET msn1.group_mark_id = msn1.wip_entity_id
10838: WHERE msn1.gen_object_id =
10839: (SELECT msn2.gen_object_id
10840: FROM wip_move_txn_interface wmti,
10841: wip_serial_move_interface wsmi,
10842: mtl_serial_numbers msn2
10843: WHERE wmti.transaction_id = wsmi.transaction_id
10844: AND wsmi.assembly_serial_number = msn2.serial_number
10856: +--------------------------------------*/
10857: DELETE FROM wip_serial_move_interface
10858: WHERE transaction_id IN
10859: (SELECT transaction_id
10860: FROM wip_move_txn_interface
10861: WHERE group_id = p_group_id
10862: AND process_phase = WIP_CONSTANTS.BF_SETUP
10863: AND process_status = WIP_CONSTANTS.RUNNING);
10864:
10864:
10865: /*-------------------------------+
10866: | Delete move interface records |
10867: +-------------------------------*/
10868: DELETE FROM wip_move_txn_interface
10869: WHERE group_id = p_group_id
10870: AND process_phase = WIP_CONSTANTS.BF_SETUP
10871: AND process_status = WIP_CONSTANTS.RUNNING;
10872:
10874: | check for failed transactions |
10875: +--------------------------------*/
10876: SELECT COUNT(1)
10877: INTO l_err_record
10878: FROM WIP_MOVE_TXN_INTERFACE
10879: WHERE GROUP_ID = p_group_id
10880: AND process_status in (WIP_CONSTANTS.ERROR, WIP_CONSTANTS.RUNNING);
10881:
10882: IF(l_err_record > 0) THEN
10944: l_returnStatus VARCHAR2(1);
10945: BEGIN
10946: retcode := 0; -- success
10947: /* Set process phase to 'Running' */
10948: UPDATE wip_move_txn_interface
10949: SET process_status = WIP_CONSTANTS.RUNNING,
10950: last_update_date = SYSDATE,
10951: last_update_login = fnd_global.conc_login_id,
10952: request_id = fnd_global.conc_request_id,
11046: wmta.transaction_quantity *
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
11083: END repetitive_scrap;
11084:
11085: /*****************************************************************************
11086: * This procedure will be used by WIP OA Transaction page to insert record
11087: * into WIP_MOVE_TXN_INTERFACE.
11088: ****************************************************************************/
11089: PROCEDURE insert_record(p_transaction_id IN NUMBER,
11090: p_last_update_date IN DATE,
11091: p_last_updated_by IN NUMBER,
11159: p_batch_id IN NUMBER,
11160: p_employee_id IN NUMBER,
11161: p_completed_instructions IN NUMBER) IS
11162: BEGIN
11163: INSERT INTO wip_move_txn_interface
11164: (transaction_id,
11165: last_update_date,
11166: last_updated_by,
11167: last_updated_by_name,
11323: CURSOR c_errors IS
11324: SELECT wtie.error_column,
11325: wtie.error_message
11326: FROM wip_txn_interface_errors wtie,
11327: wip_move_txn_interface wmti
11328: WHERE wtie.transaction_id = wmti.transaction_id
11329: AND wmti.group_id = p_group_id;
11330:
11331: CURSOR c_move_records IS