[Home] [Help]
78: request_id,
79: program_application_id,
80: program_id,
81: program_update_date
82: FROM wip_move_txn_interface
83: WHERE transaction_id = current_errors(error_no).transaction_id
84: AND group_id = g_group_id;
85:
86: error_no := error_no + 1;
104:
105: PROCEDURE organization_id(p_count_of_errored OUT NOCOPY NUMBER) IS
106: BEGIN
107: -- Derive ORGANIZATIOIN_ID if user provided only ORGANIZATION_CODE
108: UPDATE wip_move_txn_interface wmti
109: SET wmti.organization_id =
110: (SELECT mp.organization_id
111: FROM mtl_parameters mp
112: WHERE mp.organization_code = UPPER(wmti.organization_code))
119: enums.delete;
120:
121: -- If cannot derive ORGANIZATION_ID or ORGANIZATION_ID not corresponding to
122: -- ORGANIZATION_CODE provided, error out.
123: UPDATE wip_move_txn_interface wmti
124: SET wmti.process_status = WIP_CONSTANTS.ERROR
125: WHERE wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
126: AND wmti.process_status = WIP_CONSTANTS.PENDING
127: AND ((wmti.organization_id IS NULL) -- cannot derive ORGANIZATION_ID
171: -- transaction for background.
172: PROCEDURE wip_entity_id IS
173: BEGIN
174: -- Derive WIP_ENTITY_ID if user provided only WIP_ENTITY_NAME
175: UPDATE wip_move_txn_interface wmti
176: SET wmti.wip_entity_id =
177: (SELECT we.wip_entity_id
178: FROM wip_entities we
179: WHERE we.wip_entity_name = wmti.wip_entity_name
190: -- reset enums table
191: enums.delete;
192: -- If unable to derive WIP_ENTITY_ID or WIP_ENTITY_ID not conresponding
193: -- to WIP_ENTITY_NAME specified, error out.
194: UPDATE wip_move_txn_interface wmti
195: SET wmti.process_status = WIP_CONSTANTS.ERROR
196: WHERE wmti.group_id = g_group_id
197: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
198: AND wmti.process_status = WIP_CONSTANTS.RUNNING
218: p_err_col => 'WIP_ENTITY_ID/NAME',
219: p_err_msg => fnd_message.get);
220:
221: -- Derive ENTITY_TYPE and PRIMARY_ITEM_ID from WIP_ENTITY_ID
222: UPDATE wip_move_txn_interface wmti
223: SET (wmti.entity_type, wmti.primary_item_id) =
224: (SELECT we.entity_type,
225: we.primary_item_id
226: FROM wip_entities we
231:
232: -- reset enums table
233: enums.delete;
234: -- If non-standard job and no assembly defined, error out
235: UPDATE wip_move_txn_interface wmti
236: SET wmti.process_status = WIP_CONSTANTS.ERROR
237: WHERE wmti.group_id = g_group_id
238: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
239: AND wmti.process_status = WIP_CONSTANTS.RUNNING
248: /************************
249: * Start Repetitive Check
250: ************************/
251: -- Derive LINE_ID if user provided only LINE_CODE.
252: UPDATE wip_move_txn_interface wmti
253: SET wmti.line_id =
254: (SELECT wl.line_id
255: FROM wip_lines wl
256: WHERE wl.line_code = wmti.line_code
266: -- reset enums table
267: enums.delete;
268: -- If unable to derive LINE_ID or LINE_ID not conresponding to LINE_CODE
269: -- specified, error out.
270: UPDATE wip_move_txn_interface wmti
271: SET wmti.process_status = WIP_CONSTANTS.ERROR
272: WHERE wmti.group_id = g_group_id
273: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
274: AND wmti.process_status = WIP_CONSTANTS.RUNNING
291: p_err_col => 'LINE_ID/CODE',
292: p_err_msg => fnd_message.get);
293:
294: -- derive the first transactable schedule if REPETITIVE_SCHEDULE_ID is null
295: UPDATE wip_move_txn_interface wmti
296: SET wmti.repetitive_schedule_id =
297: (SELECT wrs1.repetitive_schedule_id
298: FROM wip_repetitive_schedules wrs1
299: WHERE wrs1.wip_entity_id = wmti.wip_entity_id
318: -- reset enums table
319: enums.delete;
320: -- By this time, all repetive transaction should have REPETITIVE_SCHEDULE_ID
321: -- Otherwise, error out
322: UPDATE wip_move_txn_interface wmti
323: SET wmti.process_status = WIP_CONSTANTS.ERROR
324: WHERE wmti.group_id = g_group_id
325: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
326: AND wmti.process_status = WIP_CONSTANTS.RUNNING
342: ************************/
343: -- reset enums table
344: enums.delete;
345: -- For Discrete and Lotbased, user should not provide these 3 values
346: UPDATE wip_move_txn_interface wmti
347: SET wmti.process_status = WIP_CONSTANTS.ERROR
348: WHERE wmti.group_id = g_group_id
349: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
350: AND wmti.process_status = WIP_CONSTANTS.RUNNING
365: ************************/
366: -- reset enums table
367: enums.delete;
368: -- Check job status not either Complete or Release, error out.
369: UPDATE wip_move_txn_interface wmti
370: SET wmti.process_status = WIP_CONSTANTS.ERROR
371: WHERE wmti.group_id = g_group_id
372: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
373: AND wmti.process_status = WIP_CONSTANTS.RUNNING
399:
400: -- reset enums table
401: enums.delete;
402: -- If job/schedule specified has no routing, error out
403: UPDATE wip_move_txn_interface wmti
404: SET wmti.process_status = WIP_CONSTANTS.ERROR
405: WHERE wmti.group_id = g_group_id
406: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
407: AND wmti.process_status = WIP_CONSTANTS.RUNNING
431: -- For repetitive, if the assembly is under lot control, it will error out.
432: PROCEDURE transaction_type IS
433: BEGIN
434: -- Default TRANSACTION_TYPE to Move if users do not provide one
435: UPDATE wip_move_txn_interface wmti
436: SET wmti.transaction_type = WIP_CONSTANTS.MOVE_TXN
437: WHERE wmti.group_id = g_group_id
438: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
439: AND wmti.process_status = WIP_CONSTANTS.RUNNING
442: -- reset enums table
443: enums.delete;
444: -- Errot out, if transaction type not either Move or EZ Complete or
445: -- EZ Return
446: UPDATE wip_move_txn_interface wmti
447: SET wmti.process_status = WIP_CONSTANTS.ERROR
448: WHERE wmti.group_id = g_group_id
449: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
450: AND wmti.process_status = WIP_CONSTANTS.RUNNING
463: enums.delete;
464: -- Error out if easy completion/return and the assembly is under serial
465: -- control because we cannot gather or derive serial number for background
466: -- txns
467: UPDATE wip_move_txn_interface wmti
468: SET wmti.process_status = WIP_CONSTANTS.ERROR
469: WHERE wmti.group_id = g_group_id
470: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
471: AND wmti.process_status = WIP_CONSTANTS.RUNNING
491: -- reset enums table
492: enums.delete;
493: -- Error out if easy completion/return and no default completion subinventory
494: -- locator defined for both Discrete and Repetitive Schedule
495: UPDATE wip_move_txn_interface wmti
496: SET wmti.process_status = WIP_CONSTANTS.ERROR
497: WHERE wmti.group_id = g_group_id
498: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
499: AND wmti.process_status = WIP_CONSTANTS.RUNNING
525:
526: -- reset enums table
527: enums.delete;
528: -- Error out if item revision does not exist as a BOM revision
529: UPDATE wip_move_txn_interface wmti
530: SET wmti.process_status = WIP_CONSTANTS.ERROR
531: WHERE wmti.group_id = g_group_id
532: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
533: AND wmti.process_status = WIP_CONSTANTS.RUNNING
576: -- reset enums table
577: enums.delete;
578: -- Error out if easy completion /return for repetitive schedule
579: -- and the assembly is under lot control
580: UPDATE wip_move_txn_interface wmti
581: SET wmti.process_status = WIP_CONSTANTS.ERROR
582: WHERE wmti.group_id = g_group_id
583: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
584: AND wmti.process_status = WIP_CONSTANTS.RUNNING
601: -- reset enums table
602: enums.delete;
603: -- Error out if easy completion /return for Discrete job and the assembly
604: -- is under lot control and there is no default completion lot defined
605: UPDATE wip_move_txn_interface wmti
606: SET wmti.process_status = WIP_CONSTANTS.ERROR
607: WHERE wmti.group_id = g_group_id
608: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
609: AND wmti.process_status = WIP_CONSTANTS.RUNNING
633: -- reset enums table
634: enums.delete;
635: -- Error out if easy completion to the new lot number and and either this
636: -- item or this item category requires "Lot Attributes".
637: UPDATE wip_move_txn_interface wmti
638: SET wmti.process_status = WIP_CONSTANTS.ERROR
639: WHERE wmti.group_id = g_group_id
640: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
641: AND wmti.process_status = WIP_CONSTANTS.RUNNING
673: -- reset enums table
674: enums.delete;
675: -- Error out if easy completion to the new lot number and lot expiration date
676: -- was set to user-defined
677: UPDATE wip_move_txn_interface wmti
678: SET wmti.process_status = WIP_CONSTANTS.ERROR
679: WHERE wmti.group_id = g_group_id
680: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
681: AND wmti.process_status = WIP_CONSTANTS.RUNNING
714: BEGIN
715: -- reset enums table
716: enums.delete;
717: -- Error out if TRANSACTION_DATE is the future date
718: UPDATE wip_move_txn_interface wmti
719: SET wmti.process_status = WIP_CONSTANTS.ERROR
720: WHERE wmti.group_id = g_group_id
721: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
722: AND wmti.process_status = WIP_CONSTANTS.RUNNING
731: /* Fix for bug 5685099 : Validate if TRANSACTION_DATE falls in open accounting period. */
732: -- reset enums table
733: enums.delete;
734: -- Error out if TRANSACTION_DATE does not fall in open period
735: UPDATE wip_move_txn_interface wmti
736: SET wmti.process_status = WIP_CONSTANTS.ERROR
737: WHERE wmti.group_id = g_group_id
738: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
739: AND wmti.process_status = WIP_CONSTANTS.RUNNING
759:
760: -- reset enums table
761: enums.delete;
762: -- Error out if TRANSACTION_DATE is before released date
763: UPDATE wip_move_txn_interface wmti
764: SET wmti.process_status = WIP_CONSTANTS.ERROR
765: WHERE wmti.group_id = g_group_id
766: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
767: AND wmti.process_status = WIP_CONSTANTS.RUNNING
788: p_err_col => 'TRANSACTION_DATE',
789: p_err_msg => fnd_message.get);
790:
791: -- Derive ACCT_PERIOD_ID from TRANSACTION_DATE
792: UPDATE wip_move_txn_interface wmti
793: SET wmti.acct_period_id =
794: (SELECT oap.acct_period_id
795: FROM org_acct_periods oap
796: WHERE oap.organization_id = wmti.organization_id
807: -- reset enums table
808: enums.delete;
809: -- Error out if there is no open accout period for the TRANSACTION_DATE
810: -- specified or there is no WIP_PERIOD_BALANCES
811: UPDATE wip_move_txn_interface wmti
812: SET wmti.process_status = WIP_CONSTANTS.ERROR
813: WHERE wmti.group_id = g_group_id
814: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
815: AND wmti.process_status = WIP_CONSTANTS.RUNNING
849: l_last_op NUMBER;
850: BEGIN
851: -- Set FM_OPERATION_SEQ_NUM to last_operation if TRANSACTION_TYPE is
852: -- EZ Return and FM_OPERATION_SEQ_NUM is null
853: UPDATE wip_move_txn_interface wmti
854: SET wmti.fm_operation_seq_num =
855: (SELECT wo.operation_seq_num
856: FROM wip_operations wo
857: WHERE wo.wip_entity_id = wmti.wip_entity_id
868: -- reset enums table
869: enums.delete;
870: -- Error out if FM_OPERATION_SEQ_NUM is null or FM_OPERATION_SEQ_NUM
871: -- is invalid
872: UPDATE wip_move_txn_interface wmti
873: SET wmti.process_status = WIP_CONSTANTS.ERROR
874: WHERE wmti.group_id = g_group_id
875: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
876: AND wmti.process_status = WIP_CONSTANTS.RUNNING
894: -- reset enums table
895: enums.delete;
896: -- Error out if TRANSACTION_TYPE is EZ Return and FM_OPERATION_SEQ_NUM
897: -- is not equal to the last operation.
898: UPDATE wip_move_txn_interface wmti
899: SET wmti.process_status = WIP_CONSTANTS.ERROR
900: WHERE wmti.group_id = g_group_id
901: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
902: AND wmti.process_status = WIP_CONSTANTS.RUNNING
926: PROCEDURE fm_step IS
927: BEGIN
928: -- Set FM_INTRAOPERATION_STEP_TYPE to Tomove if TRANSACTION_TYPE is
929: -- EZ Return and FM_INTRAOPERATION_STEP_TYPE is null
930: UPDATE wip_move_txn_interface wmti
931: SET wmti.fm_intraoperation_step_type = WIP_CONSTANTS.TOMOVE
932: WHERE wmti.group_id = g_group_id
933: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
934: AND wmti.process_status = WIP_CONSTANTS.RUNNING
937:
938: -- reset enums table
939: enums.delete;
940: -- Error out if FM_INTRAOPERATION_STEP_TYPE is null or invalid
941: UPDATE wip_move_txn_interface wmti
942: SET wmti.process_status = WIP_CONSTANTS.ERROR
943: WHERE wmti.group_id = g_group_id
944: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
945: AND wmti.process_status = WIP_CONSTANTS.RUNNING
972: -- reset enums table
973: enums.delete;
974: -- Error out if FM_OPERATION_SEQ_NUM/FM_INTRAOPERATION_STEP_TYPE has
975: -- no move shop floor status attached
976: UPDATE wip_move_txn_interface wmti
977: SET wmti.process_status = WIP_CONSTANTS.ERROR
978: WHERE wmti.group_id = g_group_id
979: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
980: AND wmti.process_status = WIP_CONSTANTS.RUNNING
1010: -- reset enums table
1011: enums.delete;
1012: -- Error out if users try to perform easy completion from Tomove of the
1013: -- last operation
1014: UPDATE wip_move_txn_interface wmti
1015: SET wmti.process_status = WIP_CONSTANTS.ERROR
1016: WHERE wmti.group_id = g_group_id
1017: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1018: AND wmti.process_status = WIP_CONSTANTS.RUNNING
1036: -- reset enums table
1037: enums.delete;
1038: -- Error out if TRANSACTION_TYPE is EZ Return and
1039: -- FM_INTRAOPERATION_STEP_TYPE not equal to Tomove
1040: UPDATE wip_move_txn_interface wmti
1041: SET wmti.process_status = WIP_CONSTANTS.ERROR
1042: WHERE wmti.group_id = g_group_id
1043: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1044: AND wmti.process_status = WIP_CONSTANTS.RUNNING
1060: PROCEDURE to_operation IS
1061: BEGIN
1062: -- Set TO_OPERATION_SEQ_NUM to last_operation if TRANSACTION_TYPE is
1063: -- EZ Completion and TO_OPERATION_SEQ_NUM is null
1064: UPDATE wip_move_txn_interface wmti
1065: SET wmti.to_operation_seq_num =
1066: (SELECT wo.operation_seq_num
1067: FROM wip_operations wo
1068: WHERE wo.wip_entity_id = wmti.wip_entity_id
1077: AND wmti.to_operation_seq_num IS NULL;
1078:
1079: /*Bug 4421485->Even for plain moves we will derive to_operation as
1080: next count point operation */
1081: UPDATE wip_move_txn_interface wmti
1082: SET wmti.to_operation_seq_num =
1083: (SELECT MIN(wo.operation_seq_num)
1084: FROM wip_operations wo
1085: WHERE wo.organization_id = wmti.organization_id
1097: -- reset enums table
1098: enums.delete;
1099: -- Error out if TO_OPERATION_SEQ_NUM is null or TO_OPERATION_SEQ_NUM
1100: -- is invalid
1101: UPDATE wip_move_txn_interface wmti
1102: SET wmti.process_status = WIP_CONSTANTS.ERROR
1103: WHERE wmti.group_id = g_group_id
1104: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1105: AND wmti.process_status = WIP_CONSTANTS.RUNNING
1123: -- reset enums table
1124: enums.delete;
1125: -- Error out if TRANSACTION_TYPE is EZ Ccmplete and TO_OPERATION_SEQ_NUM
1126: -- is not equal to the last operation.
1127: UPDATE wip_move_txn_interface wmti
1128: SET wmti.process_status = WIP_CONSTANTS.ERROR
1129: WHERE wmti.group_id = g_group_id
1130: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1131: AND wmti.process_status = WIP_CONSTANTS.RUNNING
1154: PROCEDURE to_step IS
1155: BEGIN
1156: -- Set TO_INTRAOPERATION_STEP_TYPE to Tomove if TRANSACTION_TYPE is
1157: -- EZ Complete and TO_INTRAOPERATION_STEP_TYPE is null
1158: UPDATE wip_move_txn_interface wmti
1159: /*Bug Bug 4421485*/
1160: SET wmti.to_intraoperation_step_type =
1161: DECODE(wmti.transaction_type,
1162: WIP_CONSTANTS.COMP_TXN,WIP_CONSTANTS.TOMOVE, WIP_CONSTANTS.QUEUE)
1170:
1171: -- reset enums table
1172: enums.delete;
1173: -- Error out if TO_INTRAOPERATION_STEP_TYPE is null or invalid
1174: UPDATE wip_move_txn_interface wmti
1175: SET wmti.process_status = WIP_CONSTANTS.ERROR
1176: WHERE wmti.group_id = g_group_id
1177: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1178: AND wmti.process_status = WIP_CONSTANTS.RUNNING
1220: -- reset enums table
1221: enums.delete;
1222: -- Error out if users try to move to the same operation and step as the
1223: -- FM_OPERATION_SEQ_NUM and FM_INTRAOPERATION_STEP_TYPE
1224: UPDATE wip_move_txn_interface wmti
1225: SET wmti.process_status = WIP_CONSTANTS.ERROR
1226: WHERE wmti.group_id = g_group_id
1227: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1228: AND wmti.process_status = WIP_CONSTANTS.RUNNING
1238: -- reset enums table
1239: enums.delete;
1240: -- Error out if TRANSACTION_TYPE is EZ Complete and
1241: -- TO_INTRAOPERATION_STEP_TYPE not equal to Tomove
1242: UPDATE wip_move_txn_interface wmti
1243: SET wmti.process_status = WIP_CONSTANTS.ERROR
1244: WHERE wmti.group_id = g_group_id
1245: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1246: AND wmti.process_status = WIP_CONSTANTS.RUNNING
1256: -- reset enums table
1257: enums.delete;
1258: -- Error out if user try to easy complete job/schedule that has No Move shop
1259: -- floor status attached to Tomove of the last operation
1260: UPDATE wip_move_txn_interface wmti
1261: SET wmti.process_status = WIP_CONSTANTS.ERROR
1262: WHERE wmti.group_id = g_group_id
1263: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1264: AND wmti.process_status = WIP_CONSTANTS.RUNNING
1286: -- reset enums table
1287: enums.delete;
1288: -- Error out if wip_parameter do not allow move over no_move shop floor
1289: -- status, and there are no_move status in between
1290: UPDATE wip_move_txn_interface wmti
1291: SET wmti.process_status = WIP_CONSTANTS.ERROR
1292: WHERE wmti.group_id = g_group_id
1293: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1294: AND wmti.process_status = WIP_CONSTANTS.RUNNING
1317: -- reset enums table
1318: enums.delete;
1319:
1320: -- Error out if TRANSACTION_QUANTITY is negative or zero
1321: UPDATE wip_move_txn_interface wmti
1322: SET wmti.process_status = WIP_CONSTANTS.ERROR
1323: WHERE wmti.group_id = g_group_id
1324: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1325: AND wmti.process_status = WIP_CONSTANTS.RUNNING
1341: BEGIN
1342: -- reset enums table
1343: enums.delete;
1344: -- Error out if TRANSACTION_UOM is invalid
1345: UPDATE wip_move_txn_interface wmti
1346: SET wmti.process_status = WIP_CONSTANTS.ERROR
1347: WHERE wmti.group_id = g_group_id
1348: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1349: AND wmti.process_status = WIP_CONSTANTS.RUNNING
1373: -- reset enums table
1374: enums.delete;
1375:
1376: -- Error out if OVERCOMPLETION_TRANSACTION_QTY is negative or zero
1377: UPDATE wip_move_txn_interface wmti
1378: SET wmti.process_status = WIP_CONSTANTS.ERROR
1379: WHERE wmti.group_id = g_group_id
1380: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1381: AND wmti.process_status = WIP_CONSTANTS.RUNNING
1393: -- reset enums table
1394: enums.delete;
1395: -- Error out if OVERCOMPLETION_TRANSACTION_QTY is greater than
1396: -- TRANSACTION_QUANTITY
1397: UPDATE wip_move_txn_interface wmti
1398: SET wmti.process_status = WIP_CONSTANTS.ERROR
1399: WHERE wmti.group_id = g_group_id
1400: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1401: AND wmti.process_status = WIP_CONSTANTS.RUNNING
1412:
1413: -- reset enums table
1414: enums.delete;
1415: -- Error out if user try to do over Return
1416: UPDATE wip_move_txn_interface wmti
1417: SET wmti.process_status = WIP_CONSTANTS.ERROR
1418: WHERE wmti.group_id = g_group_id
1419: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1420: AND wmti.process_status = WIP_CONSTANTS.RUNNING
1429:
1430: -- reset enums table
1431: enums.delete;
1432: -- Error out if user try to do over Return from Scrap/Return from Reject
1433: UPDATE wip_move_txn_interface wmti
1434: SET wmti.process_status = WIP_CONSTANTS.ERROR
1435: WHERE wmti.group_id = g_group_id
1436: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1437: AND wmti.process_status = WIP_CONSTANTS.RUNNING
1448: -- reset enums table
1449: enums.delete;
1450: -- Error out if OVERCOMPLETION_TRANSACTION_QTY is specified for backward
1451: -- move txns
1452: UPDATE wip_move_txn_interface wmti
1453: SET wmti.process_status = WIP_CONSTANTS.ERROR
1454: WHERE wmti.group_id = g_group_id
1455: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1456: AND wmti.process_status = WIP_CONSTANTS.RUNNING
1468:
1469: END ocpl_txn_qty;
1470:
1471: -- validate transaction_id against the one in WIP_MOVE_TRANSACTIONS, and
1472: -- WIP_MOVE_TXN_INTERFACE. This value need to be unique.
1473: PROCEDURE transaction_id IS
1474: l_errMsg VARCHAR2(240);
1475: BEGIN
1476: -- Generate TRANSACTION_ID if user does not provide this value
1473: PROCEDURE transaction_id IS
1474: l_errMsg VARCHAR2(240);
1475: BEGIN
1476: -- Generate TRANSACTION_ID if user does not provide this value
1477: UPDATE wip_move_txn_interface wmti
1478: SET wmti.transaction_id = wip_transactions_s.nextval
1479: WHERE wmti.group_id = g_group_id
1480: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1481: AND wmti.process_status = WIP_CONSTANTS.RUNNING
1511: wmti1.request_id,
1512: wmti1.program_application_id,
1513: wmti1.program_id,
1514: wmti1.program_update_date
1515: FROM wip_move_txn_interface wmti1
1516: WHERE wmti1.group_id = g_group_id
1517: AND wmti1.process_phase = WIP_CONSTANTS.MOVE_VAL
1518: AND wmti1.process_status = WIP_CONSTANTS.RUNNING
1519: AND (EXISTS
1522: WHERE wmt.transaction_id = wmti1.transaction_id)
1523: OR
1524: (1 <>
1525: (SELECT count(*)
1526: FROM wip_move_txn_interface wmti2
1527: WHERE wmti2.transaction_id = wmti1.transaction_id)));
1528:
1529: END transaction_id;
1530:
1566: msik.concatenated_segments assembly_name
1567: FROM wip_discrete_jobs wdj,
1568: mtl_system_items_kfv msik,
1569: mtl_parameters mp,
1570: wip_move_txn_interface wmti
1571: WHERE wdj.primary_item_id = msik.inventory_item_id
1572: AND wdj.organization_id = msik.organization_id
1573: AND wdj.organization_id = mp.organization_id
1574: AND wmti.wip_entity_id = wdj.wip_entity_id
1614: FROM wip_repetitive_schedules wrs,
1615: wip_repetitive_items wri,
1616: mtl_system_items_kfv msik,
1617: mtl_parameters mp,
1618: wip_move_txn_interface wmti
1619: WHERE wmti.primary_item_id = msik.inventory_item_id
1620: AND wmti.organization_id = msik.organization_id
1621: AND wmti.organization_id = mp.organization_id
1622: AND wrs.wip_entity_id = wmti.wip_entity_id
1651: /** Bug fix 5000113. primary_quantity should be updated in sync with
1652: * transaction_quantity, and not just when primary_quantity is null.
1653: */
1654:
1655: UPDATE wip_move_txn_interface wmti
1656: SET wmti.primary_quantity =
1657: (SELECT ROUND(wmti.transaction_quantity * mucv.conversion_rate,
1658: WIP_CONSTANTS.INV_MAX_PRECISION)
1659: FROM mtl_uom_conversions_view mucv
1702: wmti.request_id,
1703: wmti.program_application_id,
1704: wmti.program_id,
1705: wmti.program_update_date
1706: FROM wip_move_txn_interface wmti
1707: WHERE wmti.group_id = g_group_id
1708: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1709: AND wmti.process_status = WIP_CONSTANTS.RUNNING
1710: AND wmti.primary_quantity = 0;
1744: wmti.request_id,
1745: wmti.program_application_id,
1746: wmti.program_id,
1747: wmti.program_update_date
1748: FROM wip_move_txn_interface wmti,
1749: mtl_uom_conversions_view mucv
1750: WHERE mucv.organization_id = wmti.organization_id
1751: AND mucv.inventory_item_id = wmti.primary_item_id
1752: AND mucv.uom_code = wmti.transaction_uom
1795: wmti.request_id,
1796: wmti.program_application_id,
1797: wmti.program_id,
1798: wmti.program_update_date
1799: FROM wip_move_txn_interface wmti,
1800: wip_operations wo
1801: WHERE wo.organization_id = wmti.organization_id
1802: AND wo.wip_entity_id = wmti.wip_entity_id
1803: AND NVL(wo.repetitive_schedule_id, -1) =
1911: PROCEDURE primary_uom IS
1912: l_errMsg VARCHAR2(240);
1913: BEGIN
1914: -- Derive PRIMARY_UOM from PRIMARY_ITEM_ID provided if PRIMARY_UOM is null
1915: UPDATE wip_move_txn_interface wmti
1916: SET wmti.primary_uom =
1917: (SELECT msi.primary_uom_code
1918: FROM mtl_system_items msi
1919: WHERE msi.organization_id = wmti.organization_id
1956: wmti.request_id,
1957: wmti.program_application_id,
1958: wmti.program_id,
1959: wmti.program_update_date
1960: FROM wip_move_txn_interface wmti,
1961: mtl_system_items msi
1962: WHERE msi.organization_id = wmti.organization_id
1963: AND msi.inventory_item_id = wmti.primary_item_id
1964: AND wmti.group_id = g_group_id
1979: /** Bug fix 5000113. overcompletion_primary_qty should be updated in sync with
1980: * transaction_quantity, and not just when overcompletion_quantity is null.
1981: */
1982:
1983: UPDATE wip_move_txn_interface wmti
1984: SET wmti.overcompletion_primary_qty =
1985: (SELECT ROUND(wmti.overcompletion_transaction_qty *
1986: mucv.conversion_rate, WIP_CONSTANTS.INV_MAX_PRECISION)
1987: FROM mtl_uom_conversions_view mucv
2031: wmti.request_id,
2032: wmti.program_application_id,
2033: wmti.program_id,
2034: wmti.program_update_date
2035: FROM wip_move_txn_interface wmti
2036: WHERE wmti.group_id = g_group_id
2037: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2038: AND wmti.process_status = WIP_CONSTANTS.RUNNING
2039: AND wmti.overcompletion_primary_qty = 0;
2071: wmti.request_id,
2072: wmti.program_application_id,
2073: wmti.program_id,
2074: wmti.program_update_date
2075: FROM wip_move_txn_interface wmti,
2076: mtl_uom_conversions_view mucv
2077: WHERE mucv.organization_id = wmti.organization_id
2078: AND mucv.inventory_item_id = wmti.primary_item_id
2079: AND mucv.uom_code = wmti.transaction_uom
2126: wmti.request_id,
2127: wmti.program_application_id,
2128: wmti.program_id,
2129: wmti.program_update_date
2130: FROM wip_move_txn_interface wmti
2131: WHERE wmti.group_id = g_group_id
2132: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2133: AND wmti.process_status = WIP_CONSTANTS.RUNNING
2134: AND wmti.overcompletion_transaction_id IS NOT NULL;
2142: PROCEDURE reason_id IS
2143: l_errMsg VARCHAR2(240);
2144: BEGIN
2145: -- Derive REASON_ID from REASON_NAME provided
2146: UPDATE wip_move_txn_interface wmti
2147: SET wmti.reason_id =
2148: (SELECT mtr.reason_id
2149: FROM mtl_transaction_reasons mtr
2150: WHERE mtr.reason_name = wmti.reason_name
2188: wmti.request_id,
2189: wmti.program_application_id,
2190: wmti.program_id,
2191: wmti.program_update_date
2192: FROM wip_move_txn_interface wmti
2193: WHERE wmti.group_id = g_group_id
2194: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2195: AND wmti.process_status = WIP_CONSTANTS.RUNNING
2196: AND (wmti.reason_id IS NOT NULL OR wmti.reason_name IS NOT NULL)
2241: wmti.request_id,
2242: wmti.program_application_id,
2243: wmti.program_id,
2244: wmti.program_update_date
2245: FROM wip_move_txn_interface wmti,
2246: wip_parameters wp
2247: WHERE wp.organization_id = wmti.organization_id
2248: AND wmti.group_id = g_group_id
2249: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2290: PROCEDURE last_updated_by IS
2291: l_errMsg VARCHAR2(240);
2292: BEGIN
2293: -- Derive LAST_UPDATED_BY if user provided only LAST_UPDATED_BY_NAME
2294: UPDATE wip_move_txn_interface wmti
2295: SET wmti.last_updated_by =
2296: (SELECT fu.user_id
2297: FROM fnd_user fu
2298: WHERE fu.user_name = wmti.last_updated_by_name
2335: wmti.request_id,
2336: wmti.program_application_id,
2337: wmti.program_id,
2338: wmti.program_update_date
2339: FROM wip_move_txn_interface wmti
2340: WHERE wmti.group_id = g_group_id
2341: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2342: AND wmti.process_status = WIP_CONSTANTS.RUNNING
2343: AND wmti.last_updated_by IS NULL; -- cannot derive LAST_UPDATED_BY
2352: PROCEDURE created_by IS
2353: l_errMsg VARCHAR2(240);
2354: BEGIN
2355: -- Derive CREATED_BY if user provided only CREATED_BY_NAME
2356: UPDATE wip_move_txn_interface wmti
2357: SET wmti.created_by =
2358: (SELECT fu.user_id
2359: FROM fnd_user fu
2360: WHERE fu.user_name = wmti.created_by_name
2397: wmti.request_id,
2398: wmti.program_application_id,
2399: wmti.program_id,
2400: wmti.program_update_date
2401: FROM wip_move_txn_interface wmti
2402: WHERE wmti.group_id = g_group_id
2403: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2404: AND wmti.process_status = WIP_CONSTANTS.RUNNING
2405: AND ((wmti.created_by IS NULL) -- cannot derive LAST_UPDATED_BY
2457: wmti.request_id,
2458: wmti.program_application_id,
2459: wmti.program_id,
2460: wmti.program_update_date
2461: FROM wip_move_txn_interface wmti
2462: WHERE wmti.group_id = g_group_id
2463: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2464: AND wmti.process_status = WIP_CONSTANTS.RUNNING
2465: AND EXISTS
2537: wmti.request_id,
2538: wmti.program_application_id,
2539: wmti.program_id,
2540: wmti.program_update_date
2541: FROM wip_move_txn_interface wmti
2542: WHERE wmti.group_id = g_group_id
2543: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2544: AND wmti.process_status = WIP_CONSTANTS.RUNNING
2545: AND EXISTS
2581: enums.delete;
2582: -- Users cannot move cross 'Queue' of serialization start op. User need to
2583: -- move 2 step. The first time move to Queue of serialization start op, then
2584: -- serial move. For backward move, do serial move first.
2585: UPDATE wip_move_txn_interface wmti
2586: SET wmti.process_status = WIP_CONSTANTS.ERROR
2587: WHERE wmti.group_id = g_group_id
2588: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2589: AND wmti.process_status = WIP_CONSTANTS.RUNNING
2620: enums.delete;
2621: -- If user try to do serialized transaction, primary_quantity must be 1.
2622: -- This validation is only for serialized discrete job. For serialized
2623: -- OSFM job, primary_quantity can be more than 1.
2624: UPDATE wip_move_txn_interface wmti
2625: SET wmti.process_status = WIP_CONSTANTS.ERROR
2626: WHERE wmti.group_id = g_group_id
2627: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2628: AND wmti.process_status = WIP_CONSTANTS.RUNNING
2648: -- reset enums table
2649: enums.delete;
2650: -- if user provide serial number information for non-serialized job, or
2651: -- serialized job with non-serialized move, error out.
2652: UPDATE wip_move_txn_interface wmti
2653: SET wmti.process_status = WIP_CONSTANTS.ERROR
2654: WHERE wmti.group_id = g_group_id
2655: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2656: AND wmti.process_status = WIP_CONSTANTS.RUNNING
2685: -- reset enums table
2686: enums.delete;
2687: -- if user try to do serialized transaction, number of serial records must be
2688: -- equal to wmti.primary_quantity
2689: UPDATE wip_move_txn_interface wmti
2690: SET wmti.process_status = WIP_CONSTANTS.ERROR
2691: WHERE wmti.group_id = g_group_id
2692: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2693: AND wmti.process_status = WIP_CONSTANTS.RUNNING
2738: -- reset enums table
2739: enums.delete;
2740: -- if user try to do serialized transaction, the status of the serial
2741: -- must correspond to the transaction type.
2742: UPDATE wip_move_txn_interface wmti
2743: SET wmti.process_status = WIP_CONSTANTS.ERROR
2744: WHERE wmti.group_id = g_group_id
2745: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2746: AND wmti.process_status = WIP_CONSTANTS.RUNNING
2788: PROCEDURE update_interface_tbl IS
2789: BEGIN
2790: -- there are some errors occur, so set the process_status to error so that
2791: -- move processor will not pick up this record
2792: UPDATE wip_move_txn_interface wmti
2793: SET wmti.process_status = WIP_CONSTANTS.ERROR
2794: WHERE wmti.group_id = g_group_id
2795: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2796: AND wmti.process_status = WIP_CONSTANTS.RUNNING
2815: -- reset enums table
2816: enums.delete;
2817: -- Users cannot do EZ Completion/EZ Return if an assembly is not transactable
2818: -- or an assembly is not an inventory item.
2819: UPDATE wip_move_txn_interface wmti
2820: SET wmti.process_status = WIP_CONSTANTS.ERROR
2821: WHERE wmti.group_id = g_group_id
2822: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2823: AND wmti.process_status = WIP_CONSTANTS.RUNNING
2851: l_NextOpSeq NUMBER;
2852: l_OpExists BOOLEAN;
2853: BEGIN
2854:
2855: UPDATE wip_move_txn_interface wmti
2856: SET (wmti.fm_operation_code,
2857: wmti.fm_department_id,
2858: wmti.fm_department_code,
2859: wmti.to_operation_code,
3092: g_group_id := p_group_id;
3093: enums.delete;
3094:
3095: -- Derive ACCT_PERIOD_ID from TRANSACTION_DATE
3096: UPDATE wip_move_txn_interface wmti
3097: SET wmti.acct_period_id =
3098: (SELECT oap.acct_period_id
3099: FROM org_acct_periods oap
3100: WHERE oap.organization_id = wmti.organization_id
3111: -- reset enums table
3112: enums.delete;
3113: -- Error out if there is no open accout period for the TRANSACTION_DATE
3114: -- specified or there is no WIP_PERIOD_BALANCES
3115: UPDATE wip_move_txn_interface wmti
3116: SET wmti.process_status = WIP_CONSTANTS.ERROR
3117: WHERE wmti.group_id = g_group_id
3118: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
3119: AND wmti.process_status = WIP_CONSTANTS.RUNNING
3136: -- reset enums table
3137: enums.delete;
3138: -- Error out if FM_OPERATION_SEQ_NUM/FM_INTRAOPERATION_STEP_TYPE has
3139: -- no move shop floor status attached
3140: UPDATE wip_move_txn_interface wmti
3141: SET wmti.process_status = WIP_CONSTANTS.ERROR
3142: WHERE wmti.group_id = g_group_id
3143: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
3144: AND wmti.process_status = WIP_CONSTANTS.RUNNING
3174: -- reset enums table
3175: enums.delete;
3176: -- Error out if user try to easy complete job/schedule that has No Move shop
3177: -- floor status attached to Tomove of the last operation
3178: UPDATE wip_move_txn_interface wmti
3179: SET wmti.process_status = WIP_CONSTANTS.ERROR
3180: WHERE wmti.group_id = g_group_id
3181: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
3182: AND wmti.process_status = WIP_CONSTANTS.RUNNING
3206: -- reset enums table
3207: enums.delete;
3208: -- Error out if wip_parameter do not allow move over no_move shop floor
3209: -- status, and there are no_move status in between
3210: UPDATE wip_move_txn_interface wmti
3211: SET wmti.process_status = WIP_CONSTANTS.ERROR
3212: WHERE wmti.group_id = g_group_id
3213: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
3214: AND wmti.process_status = WIP_CONSTANTS.RUNNING
3238: update_interface_tbl;
3239:
3240: -- Set WMTI.PROCESS_PHASE to WIP_CONSTANTS.MOVE_PROC so that move processing
3241: -- code can process these records.
3242: UPDATE wip_move_txn_interface wmti
3243: SET process_phase = WIP_CONSTANTS.MOVE_PROC
3244: WHERE wmti.group_id = g_group_id
3245: AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
3246: AND wmti.process_status = WIP_CONSTANTS.RUNNING;