[Home] [Help]
75: acct_period_id,
76: completion_transaction_id,
77: transaction_batch_id,
78: transaction_batch_seq
79: from mtl_transactions_interface
80: where transaction_header_id = p_txnHeaderID
81: and transaction_source_type_id = 5
82: and process_flag = 1
83: and upper(nvl(flow_schedule, 'N')) = 'Y'
115: for flow_rec in flow_c loop
116: begin
117: select count(*)
118: into l_fromUI
119: from mtl_transactions_interface
120: where parent_id is not null
121: and parent_id = flow_rec.transaction_interface_id
122: and substitution_type_id is null;
123:
145: flow_rec.subinventory_code,
146: flow_rec.locator_id,
147: flow_rec.accounting_class);
148: if ( l_returnStatus = fnd_api.g_ret_sts_success ) then
149: update mtl_transactions_interface
150: set schedule_number = flow_rec.schedule_number,
151: source_project_id = flow_rec.source_project_id,
152: project_id = flow_rec.project_id,
153: source_task_id = flow_rec.source_task_id,
200: --Bug 5181899, for schedules submitted from oracle ui, if user has submitted the txn
201: --with no component, then we should not re-explode the bill
202: select source_code
203: into l_src_code
204: from mtl_transactions_interface
205: where rowid = flow_rec.rowid;
206:
207: if(l_src_code = 'WIP_FLOW_SCHEDULES_OA' OR l_src_code = 'WIP_FLOW_SCHEDULES')
208: then l_fromUI := 1;
252: l_lot_ser_count := 0 ;
253:
254: SELECT COUNT(*)
255: INTO l_bf_count
256: FROM mtl_transactions_interface
257: WHERE transaction_header_id = p_txnHeaderID
258: AND completion_transaction_id = flow_rec.completion_transaction_id
259: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
260: WIP_CONSTANTS.RETCOMP_ACTION,
261: WIP_CONSTANTS.ISSNEGC_ACTION,
262: WIP_CONSTANTS.RETNEGC_ACTION);
263: SELECT COUNT(*)
264: INTO l_lot_ser_count
265: FROM mtl_transactions_interface mti,
266: mtl_system_items msi
267: WHERE mti.organization_id = msi.organization_id
268: AND mti.inventory_item_id = msi.inventory_item_id
269: AND (msi.lot_control_code = WIP_CONSTANTS.LOT
308:
309: -- update the schedule number column of MTI to the schedule number of the parent record
310: -- this way, if it erorred out in MMTT, the user can query up all the records for a completion
311: -- transaction
312: update mtl_transactions_interface
313: set schedule_number = flow_rec.schedule_number
314: where completion_transaction_id = flow_rec.completion_transaction_id
315: and organization_id = flow_rec.organization_id --fix for bug 4890147, add more criteria so no FTS is done
316: and parent_id = flow_rec.transaction_interface_id;
468: select schedule_number,
469: transaction_interface_id
470: into l_scheduleNumber,
471: l_interfaceID
472: from mtl_transactions_interface
473: where rowid = p_rowid;
474:
475: if ( wip_flow_validation.primary_item_id(p_rowid => p_rowid) = 0 ) then
476: fnd_message.set_name('WIP', 'WIP_CANNOT_BUILD_ITEM');
556: organization_id,
557: substitution_item_id,
558: substitution_type_id,
559: operation_seq_num
560: from mtl_transactions_interface
561: where parent_id = p_parentID
562: and process_flag = 1;
563:
564: l_result number := 0;
572: -- 4: Lot/Serial
573: begin
574: select count(*)
575: into l_result
576: from mtl_transactions_interface
577: where parent_id = p_parentID
578: and process_flag = 1
579: and nvl(substitution_type_id, -1) not in (1, 2, 3, 4);
580:
587: end if;
588:
589: select count(*)
590: into l_result
591: from mtl_transactions_interface
592: where parent_id = p_parentID
593: and process_flag = 1
594: and nvl(flow_schedule, 'Y') <> 'Y';
595:
729: ;
730:
731: cursor wip_entity_id_cursor(txn_header_id number) is
732: select transaction_source_id
733: from mtl_transactions_interface
734: where transaction_header_id = txn_header_id
735: and rownum < 2
736: ;
737:
875: l_childTxnActionID := l_compTbl(l_count).transaction_action_id;
876: l_childTxnTypeID := getTypeFromAction(l_childTxnActionID);
877:
878: -- if it is phantom, we insert it with negative op seq num
879: insert into mtl_transactions_interface(
880: transaction_header_id,
881: transaction_interface_id,
882: transaction_mode,
883: parent_id,
1045: substitution_item_id,
1046: transaction_uom,
1047: subinventory_code,
1048: locator_id
1049: from mtl_transactions_interface
1050: where parent_id = p_parentID
1051: and process_flag = 1
1052: and substitution_type_id is not null
1053: order by substitution_type_id;
1055: cursor bf_c(p_opSeq number,
1056: p_orgID number,
1057: p_itemID number) is
1058: select transaction_interface_id
1059: from mtl_transactions_interface
1060: where parent_id = p_parentID
1061: and process_flag = 1
1062: and substitution_type_id is null
1063: and operation_seq_num = p_opSeq
1089: fetch bf_c into l_bfInterfaceID;
1090:
1091: if ( bf_c%FOUND) then
1092: -- if found, then delete the bf record and set substitute record right
1093: delete from mtl_transactions_interface
1094: where transaction_interface_id = l_bfInterfaceID;
1095:
1096: -- we don't need to worry transaction qty, uom, type, etc. since those
1097: -- are not null columns and don't have to be the same as the backflush record
1096: -- we don't need to worry transaction qty, uom, type, etc. since those
1097: -- are not null columns and don't have to be the same as the backflush record
1098: -- we also don't need to worry about lot/serial since those info should go
1099: -- with the substitution record
1100: update mtl_transactions_interface
1101: set inventory_item_id = subs_rec.substitution_item_id,
1102: substitution_item_id = null,
1103: substitution_type_id = null
1104: where transaction_interface_id = subs_rec.transaction_interface_id;
1122: fetch bf_c into l_bfInterfaceID;
1123:
1124: if ( bf_c%FOUND) then
1125: -- if found, then delete the bf record
1126: delete from mtl_transactions_interface
1127: where transaction_interface_id = l_bfInterfaceID;
1128:
1129: -- delete the substitution record as well
1130: delete from mtl_transactions_interface
1126: delete from mtl_transactions_interface
1127: where transaction_interface_id = l_bfInterfaceID;
1128:
1129: -- delete the substitution record as well
1130: delete from mtl_transactions_interface
1131: where transaction_interface_id = subs_rec.transaction_interface_id;
1132:
1133: close bf_c;
1134: else
1144: -- If not exist, we will just let go trough, as decided on the meeting.
1145: -- We don't merge the additions into one transaction. They have to be in
1146: -- the same UOM, transaction action, etc. It's decided to have it as 2
1147: -- separate txns(decided by jgu and dsoosai).
1148: update mtl_transactions_interface
1149: set substitution_type_id = null,
1150: inventory_item_id = subs_rec.substitution_item_id,
1151: substitution_item_id = null
1152: where transaction_interface_id = subs_rec.transaction_interface_id;
1170: fetch bf_c into l_bfInterfaceID;
1171:
1172: if ( bf_c%FOUND) then
1173: -- if found, then delete the substitution record
1174: delete from mtl_transactions_interface
1175: where transaction_interface_id = subs_rec.transaction_interface_id;
1176:
1177: -- build the link and update the subinventory and locator id
1178: update mtl_transactions_interface
1174: delete from mtl_transactions_interface
1175: where transaction_interface_id = subs_rec.transaction_interface_id;
1176:
1177: -- build the link and update the subinventory and locator id
1178: update mtl_transactions_interface
1179: set transaction_interface_id = subs_rec.transaction_interface_id,
1180: subinventory_code = subs_rec.subinventory_code,
1181: locator_id = subs_rec.locator_id
1182: where transaction_interface_id = l_bfInterfaceID;
1204: select transaction_source_id,
1205: transaction_date
1206: into l_wipEntityID,
1207: l_txnDate
1208: from mtl_transactions_interface
1209: where transaction_interface_id = p_parentID;
1210:
1211: if ( l_wipEntityID is not null ) then
1212: select project_id, task_id
1215: where wip_entity_id = l_wipEntityID;
1216: end if;
1217:
1218:
1219: update mtl_transactions_interface
1220: set transaction_source_type_id = nvl(transaction_source_type_id, 5),
1221: flow_schedule = nvl(flow_schedule, 'Y'),
1222: source_project_id = l_srcProjID,
1223: source_task_id = l_srcTaskID,
1358: end_item_unit_number,
1359: transaction_header_id,
1360: completion_transaction_id,
1361: rowid
1362: from mtl_transactions_interface
1363: where transaction_interface_id = p_txnInterfaceID
1364: and transaction_source_type_id = 5
1365: and transaction_source_id is null
1366: and flow_schedule = 'Y'
1450: flow_schedule,
1451: transaction_action_id,
1452: scheduled_flag,
1453: process_flag
1454: from mtl_transactions_interface
1455: where transaction_interface_id = p_txnInterfaceID;
1456:
1457: cursor c_MMTTparams is
1458: select transaction_source_type_id,
1460: flow_schedule,
1461: transaction_action_id,
1462: scheduled_flag,
1463: process_flag
1464: from mtl_transactions_interface
1465: where transaction_interface_id = p_txnInterfaceID;
1466:
1467: begin
1468:
1485: begin
1486: if(p_txnInterfaceID is not null) then
1487: select count(*)
1488: into l_count
1489: from mtl_transactions_interface
1490: where transaction_interface_id = p_txnInterfaceID;
1491: wip_logger.log('MTI rowcount is ' || l_count, l_returnStatus);
1492:
1493: for l_paramRec in c_MTIparams loop
1730: end if;
1731:
1732: -- set the transaction_source_id for the assembly record (if from MTI)
1733: --and its components
1734: update mtl_transactions_interface
1735: set last_update_date = sysdate,
1736: last_updated_by = fnd_global.user_id,
1737: last_update_login = fnd_global.login_id,
1738: program_application_id = fnd_global.prog_appl_id,