[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: 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
155: AND mmt.organization_id = wmti.organization_id
356: x_returnStatus => l_returnStatus);
357: END IF;
358: -- Update status of move record to error if components fail inventory
359: -- validation.
360: UPDATE wip_move_txn_interface wmti
361: SET wmti.process_status = WIP_CONSTANTS.ERROR
362: WHERE wmti.group_id = p_group_id
363: AND EXISTS
364: (SELECT 1
369:
370: IF (l_logLevel <= wip_constants.full_logging) THEN
371: fnd_message.set_name('WIP', 'WIP_UPDATED_ROWS');
372: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
373: fnd_message.set_token('ENTITY2', 'WIP_MOVE_TXN_INTERFACE');
374: l_msg := fnd_message.get;
375: wip_logger.log(p_msg => l_msg,
376: x_returnStatus => l_returnStatus);
377: END IF;
567: wop.organization_id,
568: wop.repetitive_schedule_id
569: FROM wip_operations wop,
570: wip_repetitive_schedules wrs,
571: wip_move_txn_interface wmti
572: WHERE wop.organization_id = wmti.organization_id
573: AND wop.wip_entity_id = wmti.wip_entity_id
574: AND wmti.group_id = p_group_id
575: AND TRUNC(wmti.transaction_date) = TRUNC(p_txn_date)
951: END schedule_alloc;
952:
953: /*****************************************************************************
954: * This procedure is equivalent to witoc_insert_alloc_child in wiltps.ppc
955: * This procedure is used to insert child record into WIP_MOVE_TXN_INTERFACE
956: * and WIP_MOVE_TXN_ALLOCATIONS
957: ****************************************************************************/
958: PROCEDURE insert_alloc_child(p_org_id IN NUMBER,
959: p_scheID IN NUMBER,
1356: DECODE(p_gib.application_id, -1, NULL, p_gib.application_id),
1357: DECODE(p_gib.program_id, -1, NULL, p_gib.program_id),
1358: DECODE(p_gib.request_id, -1, NULL, SYSDATE)
1359: FROM wip_operations wop1,
1360: wip_move_txn_interface wmti1,
1361: wip_move_txn_allocations wma1
1362: WHERE wop1.rowid = wop.rowid
1363: -- The WO rows to be updated are identIFied by the rowids.
1364: -- For each such row, go back and sum the quantities from WMTI
1377: -- must be passed.
1378: WHERE wop.rowid =
1379: (SELECT wop2.rowid
1380: FROM wip_operations wop2,
1381: wip_move_txn_interface wmti2,
1382: wip_move_txn_allocations wma2
1383: WHERE wmti2.group_id = p_gib.group_id
1384: AND TRUNC(wmti2.transaction_date) = TRUNC(p_gib.txn_date)
1385: AND wmti2.transaction_id = p_txn_id
1456: WIP_CONSTANTS.child_txn),
1457: WIP_CONSTANTS.parent_txn) oc_txn_type,
1458: transaction_type txn_type,
1459: transaction_date txn_date
1460: FROM wip_move_txn_interface
1461: WHERE group_id = p_group_id
1462: AND TRUNC(transaction_date) = TRUNC(p_txn_date)
1463: AND process_phase = WIP_CONSTANTS.MOVE_PROC
1464: AND process_status = WIP_CONSTANTS.RUNNING
1608:
1609: IF (x_returnStatus <> fnd_api.g_ret_sts_success) THEN
1610:
1611: IF (l_rs_txn.past_timeout = 0) THEN -- not time out yet
1612: UPDATE wip_move_txn_interface
1613: SET process_status = WIP_CONSTANTS.PENDING,
1614: group_id = NULL,
1615: transaction_id = NULL
1616: WHERE transaction_id = l_rs_txn.txn_id
1720: l_proc_status = TVE_NO_MOVE_ALLOC) THEN
1721:
1722: IF(p_gib.move_mode = WIP_CONSTANTS.BACKGROUND) THEN
1723: IF (l_rs_txn.past_timeout = 0) THEN -- not time out yet
1724: UPDATE wip_move_txn_interface
1725: SET process_status = WIP_CONSTANTS.PENDING,
1726: group_id = NULL,
1727: transaction_id = NULL
1728: WHERE transaction_id = l_rs_txn.txn_id
1746:
1747: ELSIF(l_proc_status = WIP_CONSTANTS.ERROR) THEN
1748:
1749: -- update move_interface table
1750: UPDATE wip_move_txn_interface
1751: SET process_status = WIP_CONSTANTS.ERROR
1752: WHERE transaction_id = l_rs_txn.txn_id
1753: AND group_id = p_gib.group_id;
1754:
1766: EXCEPTION
1767: WHEN fnd_api.g_exc_unexpected_error THEN
1768: IF(p_gib.move_mode = WIP_CONSTANTS.BACKGROUND) THEN
1769: IF (l_rs_txn.past_timeout = 0) THEN -- not time out yet
1770: UPDATE wip_move_txn_interface
1771: SET process_status = WIP_CONSTANTS.PENDING,
1772: group_id = NULL,
1773: transaction_id = NULL
1774: WHERE transaction_id = l_rs_txn.txn_id
1773: transaction_id = NULL
1774: WHERE transaction_id = l_rs_txn.txn_id
1775: AND group_id = p_gib.group_id;
1776: ELSE -- already time out
1777: UPDATE wip_move_txn_interface
1778: SET process_status = WIP_CONSTANTS.ERROR
1779: WHERE transaction_id = l_rs_txn.txn_id
1780: AND group_id = p_gib.group_id;
1781:
1812: p_gib.request_id,
1813: p_gib.application_id,
1814: p_gib.program_id,
1815: SYSDATE -- program_update_date
1816: FROM wip_move_txn_interface wmti
1817: WHERE wmti.transaction_id = l_rs_txn.txn_id
1818: AND wmti.group_id = p_gib.group_id;
1819: END IF; -- time out check
1820: ELSE--Online processing
2118: wmti.transaction_type txn_type,
2119: wmti.primary_item_id item_id,
2120: NVL(wdj.serialization_start_op, 0) sn_start_op,
2121: wmti.transaction_date txn_date
2122: FROM wip_move_txn_interface wmti,
2123: wip_discrete_jobs wdj
2124: WHERE wmti.wip_entity_id = wdj.wip_entity_id
2125: AND wmti.group_id = p_group_id
2126: AND TRUNC(wmti.transaction_date) = TRUNC(p_txn_date)
2146: NVL(msn.operation_seq_num, p_start_op) sn_op,
2147: NVL(msn.intraoperation_step_type, WIP_CONSTANTS.QUEUE) sn_step
2148: FROM mtl_serial_numbers msn,
2149: wip_serial_move_interface wsmi,
2150: wip_move_txn_interface wmti
2151: WHERE msn.inventory_item_id = wmti.primary_item_id
2152: AND msn.current_organization_id = wmti.organization_id
2153: -- msn.wip_entity_id will be null after complete to inventory. Need this
2154: -- check to support return transaction.
2317:
2318: IF(l_notenough) THEN
2319: IF(p_gib.move_mode = WIP_CONSTANTS.BACKGROUND) THEN
2320: IF (l_dj_txn.past_timeout = 0) THEN -- not time out yet
2321: UPDATE wip_move_txn_interface
2322: SET process_status = WIP_CONSTANTS.PENDING,
2323: group_id = NULL,
2324: transaction_id = NULL
2325: WHERE transaction_id = l_dj_txn.txn_id
2346: x_returnStatus => x_returnStatus);
2347:
2348: IF (x_returnStatus <> fnd_api.g_ret_sts_success) THEN
2349: IF (l_dj_txn.past_timeout = 0) THEN -- not time out yet
2350: UPDATE wip_move_txn_interface
2351: SET process_status = WIP_CONSTANTS.PENDING,
2352: group_id = NULL,
2353: transaction_id = NULL
2354: WHERE transaction_id = l_dj_txn.txn_id
2445: EXCEPTION
2446: WHEN fnd_api.g_exc_unexpected_error THEN
2447: IF(p_gib.move_mode = WIP_CONSTANTS.BACKGROUND) THEN
2448: IF (l_dj_txn.past_timeout = 0) THEN -- not time out yet
2449: UPDATE wip_move_txn_interface
2450: SET process_status = WIP_CONSTANTS.PENDING,
2451: group_id = NULL,
2452: transaction_id = NULL
2453: WHERE transaction_id = l_dj_txn.txn_id
2452: transaction_id = NULL
2453: WHERE transaction_id = l_dj_txn.txn_id
2454: AND group_id = p_gib.group_id;
2455: ELSE -- already time out
2456: UPDATE wip_move_txn_interface
2457: SET process_status = WIP_CONSTANTS.ERROR
2458: WHERE transaction_id = l_dj_txn.txn_id
2459: AND group_id = p_gib.group_id;
2460: -- Get error from message stack
2490: p_gib.request_id,
2491: p_gib.application_id,
2492: p_gib.program_id,
2493: SYSDATE -- program_update_date
2494: FROM wip_move_txn_interface wmti
2495: WHERE wmti.transaction_id = l_dj_txn.txn_id
2496: AND wmti.group_id = p_gib.group_id;
2497: END IF; -- time out check
2498: ELSE--Online processing
2629: INTO l_mv_item,
2630: l_mv_lot,
2631: l_po_item,
2632: l_po_lot
2633: FROM wip_move_txn_interface wmti,
2634: wip_operation_resources wor
2635: WHERE wmti.transaction_id = p_gib.group_id
2636: AND wmti.group_id = p_gib.group_id
2637: AND wor.organization_id = l_move.org_id
2649: SELECT SUM(DECODE(transaction_type, WIP_CONSTANTS.COMP_TXN, 1, 0)),
2650: SUM(DECODE(transaction_type, WIP_CONSTANTS.RET_TXN, 1, 0))
2651: INTO l_completion,
2652: l_return
2653: FROM wip_move_txn_interface wmti
2654: WHERE wmti.group_id = p_gib.group_id
2655: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
2656: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
2657: AND wmti.process_status = WIP_CONSTANTS.RUNNING;
2681: INTO l_mv_item,
2682: l_mv_lot,
2683: l_po_item,
2684: l_po_lot
2685: FROM wip_move_txn_interface wmti,
2686: wip_operation_resources wor
2687: WHERE wmti.group_id = p_gib.group_id
2688: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
2689: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
2815: wsmi.program_update_date,
2816: -- Add this column for OSFM serial support project.
2817: wsmi.gen_object_id
2818: FROM wip_serial_move_interface wsmi,
2819: wip_move_txn_interface wmti
2820: WHERE wsmi.transaction_id = wmti.transaction_id
2821: AND wmti.group_id = p_gib.group_id
2822: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
2823: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
2954: wdj.start_quantity,
2955: wmti.batch_id,
2956: wmti.employee_id,
2957: wmti.completed_instructions
2958: FROM wip_move_txn_interface wmti,
2959: wip_discrete_jobs wdj
2960: WHERE wmti.group_id = p_gib.group_id
2961: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
2962: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
3088: wmti.qa_collection_id,
3089: wmti.overcompletion_transaction_qty,
3090: wmti.overcompletion_primary_qty,
3091: wmti.overcompletion_transaction_id
3092: FROM wip_move_txn_interface wmti
3093: WHERE wmti.group_id = p_gib.group_id
3094: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
3095: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
3096: AND wmti.process_status = WIP_CONSTANTS.RUNNING
3133: END insert_txn_history;
3134:
3135: /*****************************************************************************
3136: * This procedure is equivalent to witoc_delete_child in wiltps5.ppc
3137: * This procedure is used to delete child record from WIP_MOVE_TXN_INTERFACE
3138: * table where the child rows have fm_op and to_op to be the first operation
3139: * and the step types to be Queue
3140: ****************************************************************************/
3141: PROCEDURE delete_child_txn(p_gib IN group_rec_t,
3159: x_returnStatus => l_returnStatus);
3160: END IF;
3161:
3162: -- delete child records from WIP
3163: DELETE FROM wip_move_txn_interface wmti
3164: WHERE wmti.group_id = p_gib.group_id
3165: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
3166: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
3167: AND wmti.process_status = WIP_CONSTANTS.RUNNING
3260: -- Per item basis type for discrete jobs
3261: IF(l_move.jobTxn = WIP_CONSTANTS.YES AND
3262: l_move.rsrcItem = WIP_CONSTANTS.YES) THEN
3263: /* Fix for bug 4036149: Added leading hint to drive from the table
3264: wip_move_txn_interface to improve performance */
3265: INSERT INTO wip_cost_txn_interface
3266: (transaction_id,
3267: last_update_date,
3268: last_updated_by,
3404: cst_activities ca,
3405: wip_operation_resources wor,
3406: wip_discrete_jobs wdj,
3407: wip_operations wop,
3408: wip_move_txn_interface wmti
3409: WHERE wmti.group_id = p_gib.group_id
3410: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
3411: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
3412: AND wmti.process_status = WIP_CONSTANTS.RUNNING
3616: cst_activities ca,
3617: wip_operation_resources wor,
3618: wip_move_txn_allocations wma,
3619: wip_operations wop,
3620: wip_move_txn_interface wmti
3621: WHERE wmti.group_id = p_gib.group_id
3622: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
3623: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
3624: AND wmti.process_status = WIP_CONSTANTS.RUNNING
3857: cst_activities ca,
3858: wip_operation_resources wor,
3859: wip_discrete_jobs wdj,
3860: wip_operations wop,
3861: wip_move_txn_interface wmti
3862: WHERE wmti.group_id = p_gib.group_id
3863: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
3864: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
3865: AND wmti.process_status = WIP_CONSTANTS.RUNNING
4068: cst_activities ca,
4069: wip_operation_resources wor,
4070: wip_move_txn_allocations wma,
4071: wip_operations wop,
4072: wip_move_txn_interface wmti
4073: WHERE wmti.group_id = p_gib.group_id
4074: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
4075: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
4076: AND wmti.process_status = WIP_CONSTANTS.RUNNING
4279: FROM wip_operation_resources wor,
4280: wip_operations wop,
4281: wip_move_txn_allocations wma,
4282: wip_cost_txn_interface wci,
4283: wip_move_txn_interface wmti
4284: WHERE wmti.group_id = p_gib.group_id
4285: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
4286: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
4287: AND wmti.process_status = WIP_CONSTANTS.RUNNING
4430: 1, 1,
4431: -1,-1) -- primary_quantity
4432: FROM wip_operation_resources wor,
4433: wip_move_txn_allocations wmta,
4434: wip_move_txn_interface wmti,
4435: wip_cost_txn_interface wcti
4436: WHERE wcti.group_id = p_gib.group_id
4437: AND wcti.process_phase = WIP_CONSTANTS.RES_PROC
4438: AND wcti.process_status = WIP_CONSTANTS.PENDING
4693: FROM bom_departments bd,
4694: wip_operations wop,
4695: wip_operation_resources wor,
4696: wip_discrete_jobs wdj,
4697: wip_move_txn_interface wmti,
4698: wip_requirement_operations WRO
4699: WHERE wmti.group_id = p_gib.group_id
4700: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
4701: AND wdj.wip_entity_id = wmti.wip_entity_id
4914: NULL -- repetitive_schedule_id
4915: FROM bom_departments bd,
4916: wip_move_txn_allocations wma,
4917: wip_operations wop,
4918: wip_move_txn_interface wmti,
4919: wip_operation_resources wor
4920: WHERE wmti.group_id = p_gib.group_id
4921: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
4922: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
5152: FROM bom_departments bd,
5153: wip_operations wop,
5154: wip_operation_resources wor,
5155: wip_discrete_jobs wdj,
5156: wip_move_txn_interface wmti
5157: WHERE wmti.group_id = p_gib.group_id
5158: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
5159: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
5160: AND wmti.process_status = WIP_CONSTANTS.RUNNING
5366: wma.repetitive_schedule_id
5367: FROM bom_departments bd,
5368: wip_move_txn_allocations wma,
5369: wip_operations wop,
5370: wip_move_txn_interface wmti,
5371: wip_operation_resources wor
5372: WHERE wmti.group_id = p_gib.group_id
5373: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
5374: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
5561: -1, DECODE(SIGN(MAX(wop.quantity_completed)),1,-1,0))
5562: FROM wip_move_txn_allocations wma,
5563: wip_operations wop,
5564: wip_cost_txn_interface wci,
5565: wip_move_txn_interface wmti
5566: WHERE wmti.group_id = p_gib.group_id
5567: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
5568: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
5569: AND wmti.process_status = WIP_CONSTANTS.RUNNING
5704: wmti.repetitive_schedule_id rep_sched_id,
5705: wmti.organization_id org_id,
5706: wo.operation_seq_num op_seq_num,
5707: wmti.overcompletion_primary_qty oc_qty
5708: FROM wip_move_txn_interface wmti,
5709: wip_operations wo,
5710: wip_operation_resources wor,
5711: wip_discrete_jobs wdj
5712: WHERE wmti.group_id = p_group_id
5733: wmti.repetitive_schedule_id rep_sched_id,
5734: wmti.organization_id org_id,
5735: wo.operation_seq_num op_seq_num,
5736: wmti.overcompletion_primary_qty oc_qty
5737: FROM wip_move_txn_interface wmti,
5738: wip_operations wo,
5739: wip_operation_resources wor,
5740: wip_repetitive_schedules wrs
5741: WHERE wmti.group_id = p_group_id
5920: wdj.project_id,
5921: wdj.task_id,
5922: DECODE(wdj.project_id,NULL,NULL,'Y')-- project_accounting_context
5923: FROM /* bugfix 5129403: modified the order in the from clause */
5924: wip_move_txn_interface wmti,
5925: wip_operation_resources wor,
5926: bom_resources br,
5927: mtl_system_items msi,
5928: mtl_parameters mp,
6141: wip_operation_resources wor,
6142: wip_operations wo1,
6143: wip_operations wo2,
6144: wip_move_txn_allocations wma,
6145: wip_move_txn_interface wmti
6146: WHERE wmti.group_id = p_gib.group_id
6147: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
6148: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
6149: AND wmti.process_status = WIP_CONSTANTS.RUNNING
6330: wdj.project_id,
6331: wdj.task_id,
6332: DECODE(wdj.project_id,NULL,NULL,'Y')-- project_accounting_context
6333: FROM /* bugfix 5129403: modified the order in the from clause */
6334: wip_move_txn_interface wmti,
6335: wip_operation_resources wor,
6336: bom_resources br,
6337: mtl_system_items msi,
6338: mtl_parameters mp,
6569: wip_entities we,
6570: wip_operations wo1,
6571: wip_operations wo2,
6572: wip_move_txn_allocations wma,
6573: wip_move_txn_interface wmti
6574: WHERE wmti.group_id = p_gib.group_id
6575: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
6576: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
6577: AND wmti.process_status = WIP_CONSTANTS.RUNNING
6750: FROM bom_resources br,
6751: mtl_units_of_measure muom,
6752: wip_operation_resources wor,
6753: wip_operations wo,
6754: wip_move_txn_interface wmti
6755: WHERE wmti.group_id = p_group_id
6756: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
6757: AND wmti.process_status = WIP_CONSTANTS.RUNNING
6758: AND TRUNC(wmti.transaction_date)= TRUNC(p_txn_date)
7006: DECODE(p_gib.program_id,-1,NULL,p_gib.program_id),
7007: DECODE(p_gib.request_id,-1,NULL,SYSDATE)-- program_update_date
7008: FROM wip_operations wop1,
7009: wip_move_txn_allocations wma1,
7010: wip_move_txn_interface wmti
7011: WHERE wop1.rowid = wop.rowid
7012: AND wmti.group_id = p_gib.group_id
7013: AND (p_txn_id IS NULL OR wmti.transaction_id = p_txn_id)
7014: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
7054: WHERE wop.rowid IN
7055: (SELECT wop2.rowid
7056: FROM wip_operations wop2,
7057: wip_move_txn_allocations wma2,
7058: wip_move_txn_interface wmti
7059: WHERE wop2.organization_id = wmti.organization_id
7060: AND wmti.group_id = p_gib.group_id
7061: AND (p_txn_id IS NULL OR wmti.transaction_id = p_txn_id)
7062: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
7356: The original condition was treating records inserted from the receiving
7357: transactions for osp item as records inserted from the open interface
7358: process and thus validating against cst_comp_snap_temp table and erroring
7359: out as there is no record. This fix would treat records inserted into
7360: wip_move_txn_interface by receiving transaction as created from the WIP
7361: and thus will create a record in the cst_comp_snap_temp.
7362: */
7363:
7364: CURSOR c_txns IS
7371: 'WIP', -1,
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)
7432:
7433: -- check for snapshot transactions through the interface
7434: SELECT COUNT(*)
7435: INTO l_num_snapshot
7436: FROM wip_move_txn_interface wmti,
7437: mtl_parameters mp
7438: -- wip_parameters wp
7439: WHERE wmti.group_id = p_group_id
7440: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
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,
7465: mtl_parameters mp,
7466: mtl_material_transactions_temp mmtt2
7467: WHERE wmti.group_id = p_group_id
7561: PROCEDURE update_wo_and_snapshot(p_gib IN group_rec_t,
7562: x_returnStatus OUT NOCOPY VARCHAR2) IS
7563: CURSOR c_move_rec IS
7564: SELECT wmti.transaction_id txn_id
7565: FROM wip_move_txn_interface wmti
7566: WHERE wmti.group_id = p_gib.group_id
7567: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
7568: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
7569: AND wmti.process_status = WIP_CONSTANTS.RUNNING
7676: CURSOR c_mtl_alloc 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
7707: -1 * DECODE(wmti.entity_type,
7708: WIP_CONSTANTS.REPETITIVE, wmta.primary_quantity,
7709: wmti.primary_quantity)) changed_qty,
7710: GREATEST(wmti.fm_operation_seq_num, wmti.to_operation_seq_num) fm_op
7711: FROM wip_move_txn_interface wmti,
7712: wip_move_txn_allocations wmta,
7713: wip_parameters mp
7714: WHERE wmti.transaction_id = wmta.transaction_id (+)
7715: AND wmti.organization_id = wmta.organization_id (+)
7793: l_move.scrapTxn <> WIP_CONSTANTS.NO) THEN -- Indeterminate
7794:
7795: SELECT COUNT(1)
7796: INTO l_scrap_flag
7797: FROM wip_move_txn_interface
7798: WHERE group_id = p_gib.group_id
7799: AND TRUNC(transaction_date) = TRUNC(p_gib.txn_date)
7800: AND process_phase = WIP_CONSTANTS.MOVE_PROC
7801: AND process_status = WIP_CONSTANTS.RUNNING
7866: DECODE(p_gib.application_id,-1,NULL,p_gib.application_id),
7867: DECODE(p_gib.program_id,-1,NULL,p_gib.program_id),
7868: DECODE(p_gib.request_id,
7869: -1,NULL,SYSDATE)-- program_update_date
7870: FROM wip_discrete_jobs wdj1, wip_move_txn_interface wmti
7871: WHERE wdj1.rowid = wdj.rowid
7872: AND wmti.group_id = p_gib.group_id
7873: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
7874: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
7882: )
7883: WHERE wdj.rowid IN
7884: (SELECT wdj2.rowid
7885: FROM wip_discrete_jobs wdj2,
7886: wip_move_txn_interface wmti
7887: WHERE wmti.group_id = p_gib.group_id
7888: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
7889: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
7890: AND wmti.process_status = WIP_CONSTANTS.RUNNING
7911: FROM DUAL)
7912: WHERE wdj.rowid IN
7913: (SELECT wdj2.rowid
7914: FROM wip_discrete_jobs wdj2,
7915: wip_move_txn_interface wmti
7916: WHERE wmti.group_id = p_gib.group_id
7917: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
7918: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
7919: AND wmti.process_status = WIP_CONSTANTS.RUNNING
7933: date_completed = null
7934: WHERE wdj.rowid IN
7935: (SELECT wdj2.rowid
7936: FROM wip_discrete_jobs wdj2,
7937: wip_move_txn_interface wmti
7938: WHERE wmti.group_id = p_gib.group_id
7939: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
7940: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
7941: AND wmti.process_status = WIP_CONSTANTS.RUNNING
7983: WIP_CONSTANTS.COMP_CHRG)
7984: AND wrs.rowid IN
7985: (SELECT wrs2.rowid
7986: FROM wip_repetitive_schedules wrs2,
7987: wip_move_txn_interface wmti
7988: WHERE wmti.group_id = p_gib.group_id
7989: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
7990: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
7991: AND wmti.process_status = WIP_CONSTANTS.RUNNING
8131: wdj.task_id,
8132: p_gib.mtl_header_id, -- transaction_batch_id
8133: wmti.qa_collection_id, /*Added for Bug 7136450 (FP of 7126588 )*/
8134: WIP_CONSTANTS.ASSY_BATCH_SEQ -- transaction_batch_seq
8135: FROM wip_move_txn_interface wmti,
8136: mtl_system_items msi,
8137: wip_discrete_jobs wdj
8138: WHERE wmti.group_id = p_gib.group_id
8139: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
8265: 'N', -- lock_flag
8266: 'Y', -- posting_flag
8267: p_gib.mtl_header_id, -- transaction_batch_id
8268: WIP_CONSTANTS.ASSY_BATCH_SEQ -- transaction_batch_seq
8269: FROM wip_move_txn_interface wmti,
8270: mtl_system_items msi,
8271: wip_repetitive_schedules wrs
8272: WHERE wmti.group_id = p_gib.group_id
8273: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
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,
8633: mtl_item_locations mil,
8634: wip_operations wop,
8635: mtl_system_items msi,
8636: wip_discrete_jobs wdj
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,
8767: wip_operations wop,
8768: mtl_system_items msi,
8769: wip_repetitive_schedules wrs,
8770: wip_repetitive_items wri
9221: DECODE(p_gib.application_id,-1,NULL,p_gib.application_id),
9222: DECODE(p_gib.program_id,-1,NULL,p_gib.program_id),
9223: DECODE(p_gib.request_id,-1,NULL,SYSDATE)
9224: FROM wip_requirement_operations wro1,
9225: wip_move_txn_interface wmti1,
9226: wip_move_txn_allocations wma1
9227: WHERE wro1.rowid = wro.rowid
9228: -- The WO rows to be updated are identIFied by the rowids.
9229: -- For each such row, go back and sum the quantities from WMTI
9242: )
9243: WHERE wro.rowid in
9244: (SELECT wro2.rowid
9245: FROM wip_requirement_operations wro2,
9246: wip_move_txn_interface wmti2,
9247: wip_move_txn_allocations wma2
9248: WHERE wmti2.group_id = p_gib.group_id
9249: AND wmti2.process_phase = WIP_CONSTANTS.MOVE_PROC
9250: AND wmti2.process_status = WIP_CONSTANTS.RUNNING
9607: wmti.to_intraoperation_step_type to_step,
9608: wmti.transaction_id txn_id,
9609: wmti.reason_id reason_id,
9610: wmti.reference reference
9611: FROM wip_move_txn_interface wmti
9612: WHERE wmti.group_id = p_group_id
9613: AND TRUNC(wmti.transaction_date)= TRUNC(p_txn_date)
9614: AND wmti.process_phase = WIP_CONSTANTS.BF_SETUP
9615: AND wmti.process_status = WIP_CONSTANTS.RUNNING;
9629: fm_operation_seq_num fm_op,
9630: fm_intraoperation_step_type fm_step,
9631: to_operation_seq_num to_op,
9632: to_intraoperation_step_type to_step
9633: FROM wip_move_txn_interface
9634: WHERE group_id = p_group_id
9635: /* Comment out the check below because UI may need to use validation logic */
9636: -- AND process_phase = WIP_CONSTANTS.MOVE_PROC
9637: AND process_status = WIP_CONSTANTS.RUNNING;
9637: AND process_status = WIP_CONSTANTS.RUNNING;
9638:
9639: CURSOR c_qa_id(p_txn_date DATE) IS
9640: SELECT qa_collection_id
9641: FROM wip_move_txn_interface
9642: WHERE group_id = p_group_id
9643: AND process_phase = WIP_CONSTANTS.MOVE_PROC
9644: AND process_status = WIP_CONSTANTS.RUNNING
9645: AND TRUNC(transaction_date)= TRUNC(p_txn_date)
9649: AND qa_collection_id IS NOT NULL;
9650:
9651: CURSOR c_txn_date IS
9652: SELECT DISTINCT TRUNC(transaction_date)
9653: FROM wip_move_txn_interface
9654: WHERE group_id = p_group_id
9655: AND process_status = WIP_CONSTANTS.RUNNING;
9656:
9657: l_gib group_rec_t;
9778: l_gib.mtl_mode := p_mtl_mode; -- l_mtl_mode;
9779: l_gib.process_phase := WIP_CONSTANTS.MOVE_PROC;
9780: l_gib.process_status := WIP_CONSTANTS.RUNNING;
9781: l_gib.time_out := p_time_out;
9782: l_gib.intf_tbl_name := 'WIP_MOVE_TXN_INTERFACE';
9783: l_gib.user_id := l_move_online.user_id;
9784: l_gib.login_id := l_move_online.login_id;
9785: l_gib.request_id := l_move_online.req_id;
9786: l_gib.application_id := l_move_online.appl_id;
9796: l_gib.mtl_mode := p_mtl_mode; -- l_mtl_mode;
9797: l_gib.process_phase := p_proc_phase;
9798: l_gib.process_status := WIP_CONSTANTS.RUNNING;
9799: l_gib.time_out := p_time_out;
9800: l_gib.intf_tbl_name := 'WIP_MOVE_TXN_INTERFACE';
9801: l_gib.user_id := fnd_global.user_id;
9802: l_gib.login_id := fnd_global.conc_login_id;
9803: l_gib.request_id := fnd_global.conc_request_id;
9804: l_gib.application_id := fnd_global.prog_appl_id;
9815: p_initMsgList => fnd_api.g_true);
9816:
9817: -- There is no return status from this routine. IF some record error out,
9818: -- just neglect it and continue validating other records. The error record
9819: -- will have process_status in WIP_MOVE_TXN_INTERFACE equal to (3) or
9820: -- WIP_CONSTANTS.ERROR, and those error records will not be picked up by
9821: -- move_txns. The error message also show up in WIP_TXN_INTERFACE_ERRORS
9822: END IF; -- Move Validation
9823:
9838: 0)),
9839: SUM(DECODE(entity_type,WIP_CONSTANTS.REPETITIVE,1,0))
9840: INTO l_job,
9841: l_sche
9842: FROM wip_move_txn_interface wmti
9843: WHERE wmti.group_id = l_gib.group_id
9844: AND TRUNC(transaction_date)= TRUNC(l_gib.txn_date)
9845: AND process_phase = WIP_CONSTANTS.MOVE_PROC
9846: AND process_status = WIP_CONSTANTS.RUNNING;
10003: wip_logger.log(p_msg => 'QA enable success',
10004: x_returnStatus => l_returnStatus);
10005: END IF;
10006:
10007: UPDATE wip_move_txn_interface
10008: SET process_phase = WIP_CONSTANTS.BF_SETUP
10009: WHERE group_id = p_group_id
10010: AND process_phase = WIP_CONSTANTS.MOVE_PROC
10011: AND process_status = WIP_CONSTANTS.RUNNING
10014: -- IF debug message level = 2, write statement below to log file
10015: IF (l_logLevel <= wip_constants.full_logging) THEN
10016: fnd_message.set_name('WIP', 'WIP_UPDATED_ROWS');
10017: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
10018: fnd_message.set_token('ENTITY2', 'WIP_MOVE_TXN_INTERFACE');
10019: l_msg := fnd_message.get;
10020: wip_logger.log(p_msg => l_msg,
10021: x_returnStatus => l_returnStatus);
10022: END IF;
10194: l_errMsg := fnd_message.get;
10195: END IF;
10196: IF(p_move_mode = WIP_CONSTANTS.BACKGROUND) THEN
10197: /* Update process status to error */
10198: UPDATE wip_move_txn_interface
10199: SET process_status = WIP_CONSTANTS.ERROR
10200: WHERE group_id = p_group_id
10201: AND process_status = WIP_CONSTANTS.RUNNING
10202: AND transaction_id = l_backflush.txn_id;
10227: l_gib.request_id,
10228: l_gib.application_id,
10229: l_gib.program_id,
10230: SYSDATE -- program_update_date
10231: FROM wip_move_txn_interface wmti
10232: WHERE wmti.group_id = p_group_id
10233: AND transaction_id = l_backflush.txn_id;
10234:
10235: ELSE -- move mode is online, write to log file
10307: raise fnd_api.g_exc_unexpected_error;
10308: END IF;
10309: END IF; -- there is the records in MMTT with this header_id
10310: ELSE -- backflush is background
10311: UPDATE wip_move_txn_interface
10312: SET group_id = NULL,
10313: process_phase = WIP_CONSTANTS.BF_SETUP,
10314: process_status = WIP_CONSTANTS.PENDING
10315: WHERE group_id = p_group_id
10340: IF(p_move_mode = WIP_CONSTANTS.BACKGROUND) THEN
10341:
10342: ROLLBACK TO SAVEPOINT s_move_proc;
10343: /* Update process status to error */
10344: UPDATE wip_move_txn_interface
10345: SET process_status = WIP_CONSTANTS.ERROR
10346: WHERE group_id = p_group_id
10347: AND process_status = WIP_CONSTANTS.RUNNING
10348: AND TRUNC(transaction_date)= TRUNC(l_gib.txn_date);
10381: l_gib.request_id,
10382: l_gib.application_id,
10383: l_gib.program_id,
10384: SYSDATE -- program_update_date
10385: FROM wip_move_txn_interface wmti
10386: WHERE wmti.group_id = p_group_id
10387: AND TRUNC(wmti.transaction_date) = TRUNC(l_gib.txn_date)
10388: AND NOT EXISTS (SELECT 1
10389: FROM wip_txn_interface_errors wtie
10414: ROLLBACK TO SAVEPOINT s_move_proc;
10415: IF(p_move_mode = WIP_CONSTANTS.BACKGROUND) THEN
10416:
10417: /* Update process status to error */
10418: UPDATE wip_move_txn_interface
10419: SET process_status = WIP_CONSTANTS.ERROR
10420: WHERE group_id = p_group_id
10421: AND process_status = WIP_CONSTANTS.RUNNING
10422: AND TRUNC(transaction_date)= TRUNC(l_gib.txn_date);
10449: l_gib.request_id,
10450: l_gib.application_id,
10451: l_gib.program_id,
10452: SYSDATE -- program_update_date
10453: FROM wip_move_txn_interface wmti
10454: WHERE wmti.group_id = p_group_id
10455: AND TRUNC(wmti.transaction_date) = TRUNC(l_gib.txn_date)
10456: AND NOT EXISTS (SELECT 1
10457: FROM wip_txn_interface_errors wtie
10479: UPDATE mtl_serial_numbers msn1
10480: SET msn1.group_mark_id = msn1.wip_entity_id
10481: WHERE msn1.gen_object_id =
10482: (SELECT msn2.gen_object_id
10483: FROM wip_move_txn_interface wmti,
10484: wip_serial_move_interface wsmi,
10485: mtl_serial_numbers msn2
10486: WHERE wmti.transaction_id = wsmi.transaction_id
10487: AND wsmi.assembly_serial_number = msn2.serial_number
10499: +--------------------------------------*/
10500: DELETE FROM wip_serial_move_interface
10501: WHERE transaction_id IN
10502: (SELECT transaction_id
10503: FROM wip_move_txn_interface
10504: WHERE group_id = p_group_id
10505: AND process_phase = WIP_CONSTANTS.BF_SETUP
10506: AND process_status = WIP_CONSTANTS.RUNNING);
10507:
10507:
10508: /*-------------------------------+
10509: | Delete move interface records |
10510: +-------------------------------*/
10511: DELETE FROM wip_move_txn_interface
10512: WHERE group_id = p_group_id
10513: AND process_phase = WIP_CONSTANTS.BF_SETUP
10514: AND process_status = WIP_CONSTANTS.RUNNING;
10515:
10517: | check for failed transactions |
10518: +--------------------------------*/
10519: SELECT COUNT(1)
10520: INTO l_err_record
10521: FROM WIP_MOVE_TXN_INTERFACE
10522: WHERE GROUP_ID = p_group_id
10523: AND process_status in (WIP_CONSTANTS.ERROR, WIP_CONSTANTS.RUNNING);
10524:
10525: IF(l_err_record > 0) THEN
10587: l_returnStatus VARCHAR2(1);
10588: BEGIN
10589: retcode := 0; -- success
10590: /* Set process phase to 'Running' */
10591: UPDATE wip_move_txn_interface
10592: SET process_status = WIP_CONSTANTS.RUNNING,
10593: last_update_date = SYSDATE,
10594: last_update_login = fnd_global.conc_login_id,
10595: request_id = fnd_global.conc_request_id,
10689: wmta.transaction_quantity *
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
10726: END repetitive_scrap;
10727:
10728: /*****************************************************************************
10729: * This procedure will be used by WIP OA Transaction page to insert record
10730: * into WIP_MOVE_TXN_INTERFACE.
10731: ****************************************************************************/
10732: PROCEDURE insert_record(p_transaction_id IN NUMBER,
10733: p_last_update_date IN DATE,
10734: p_last_updated_by IN NUMBER,
10802: p_batch_id IN NUMBER,
10803: p_employee_id IN NUMBER,
10804: p_completed_instructions IN NUMBER) IS
10805: BEGIN
10806: INSERT INTO wip_move_txn_interface
10807: (transaction_id,
10808: last_update_date,
10809: last_updated_by,
10810: last_updated_by_name,
10965: CURSOR c_errors IS
10966: SELECT wtie.error_column,
10967: wtie.error_message
10968: FROM wip_txn_interface_errors wtie,
10969: wip_move_txn_interface wmti
10970: WHERE wtie.transaction_id = wmti.transaction_id
10971: AND wmti.group_id = p_group_id;
10972:
10973: CURSOR c_move_records IS