[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,
209: --Bug 5181899, for schedules submitted from oracle ui, if user has submitted the txn
210: --with no component, then we should not re-explode the bill
211: select source_code
212: into l_src_code
213: from mtl_transactions_interface
214: where rowid = flow_rec.rowid;
215:
216: if(l_src_code = 'WIP_FLOW_SCHEDULES_OA' OR l_src_code = 'WIP_FLOW_SCHEDULES')
217: then l_fromUI := 1;
261: l_lot_ser_count := 0 ;
262:
263: SELECT COUNT(*)
264: INTO l_bf_count
265: FROM mtl_transactions_interface
266: WHERE transaction_header_id = p_txnHeaderID
267: AND completion_transaction_id = flow_rec.completion_transaction_id
268: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
269: WIP_CONSTANTS.RETCOMP_ACTION,
273: /* Bug 9907143: modified for serial tagging enhancement for SUN
274:
275: SELECT COUNT(*)
276: INTO l_lot_ser_count
277: FROM mtl_transactions_interface mti,
278: mtl_system_items msi
279: WHERE mti.organization_id = msi.organization_id
280: AND mti.inventory_item_id = msi.inventory_item_id
281: AND (msi.lot_control_code = WIP_CONSTANTS.LOT
290: WIP_CONSTANTS.RETNEGC_ACTION);
291: */
292: SELECT COUNT(*)
293: INTO l_lot_ser_count
294: FROM mtl_transactions_interface mti,
295: mtl_system_items msi
296: WHERE mti.organization_id = msi.organization_id
297: AND mti.inventory_item_id = msi.inventory_item_id
298: AND (msi.lot_control_code = WIP_CONSTANTS.LOT
339:
340: -- update the schedule number column of MTI to the schedule number of the parent record
341: -- this way, if it erorred out in MMTT, the user can query up all the records for a completion
342: -- transaction
343: update mtl_transactions_interface
344: set schedule_number = flow_rec.schedule_number
345: where completion_transaction_id = flow_rec.completion_transaction_id
346: and organization_id = flow_rec.organization_id --fix for bug 4890147, add more criteria so no FTS is done
347: and parent_id = flow_rec.transaction_interface_id;
499: select schedule_number,
500: transaction_interface_id
501: into l_scheduleNumber,
502: l_interfaceID
503: from mtl_transactions_interface
504: where rowid = p_rowid;
505:
506: if ( wip_flow_validation.primary_item_id(p_rowid => p_rowid) = 0 ) then
507: fnd_message.set_name('WIP', 'WIP_CANNOT_BUILD_ITEM');
587: organization_id,
588: substitution_item_id,
589: substitution_type_id,
590: operation_seq_num
591: from mtl_transactions_interface
592: where parent_id = p_parentID
593: and process_flag = 1;
594:
595: l_result number := 0;
603: -- 4: Lot/Serial
604: begin
605: select count(*)
606: into l_result
607: from mtl_transactions_interface
608: where parent_id = p_parentID
609: and process_flag = 1
610: and nvl(substitution_type_id, -1) not in (1, 2, 3, 4);
611:
618: end if;
619:
620: select count(*)
621: into l_result
622: from mtl_transactions_interface
623: where parent_id = p_parentID
624: and process_flag = 1
625: and nvl(flow_schedule, 'Y') <> 'Y';
626:
760: ;
761:
762: cursor wip_entity_id_cursor(txn_header_id number) is
763: select transaction_source_id
764: from mtl_transactions_interface
765: where transaction_header_id = txn_header_id
766: and rownum < 2
767: ;
768:
906: l_childTxnActionID := l_compTbl(l_count).transaction_action_id;
907: l_childTxnTypeID := getTypeFromAction(l_childTxnActionID);
908:
909: -- if it is phantom, we insert it with negative op seq num
910: insert into mtl_transactions_interface(
911: transaction_header_id,
912: transaction_interface_id,
913: transaction_mode,
914: parent_id,
1076: substitution_item_id,
1077: transaction_uom,
1078: subinventory_code,
1079: locator_id
1080: from mtl_transactions_interface
1081: where parent_id = p_parentID
1082: and process_flag = 1
1083: and substitution_type_id is not null
1084: order by substitution_type_id;
1086: cursor bf_c(p_opSeq number,
1087: p_orgID number,
1088: p_itemID number) is
1089: select transaction_interface_id
1090: from mtl_transactions_interface
1091: where parent_id = p_parentID
1092: and process_flag = 1
1093: and substitution_type_id is null
1094: and operation_seq_num = p_opSeq
1120: fetch bf_c into l_bfInterfaceID;
1121:
1122: if ( bf_c%FOUND) then
1123: -- if found, then delete the bf record and set substitute record right
1124: delete from mtl_transactions_interface
1125: where transaction_interface_id = l_bfInterfaceID;
1126:
1127: -- we don't need to worry transaction qty, uom, type, etc. since those
1128: -- are not null columns and don't have to be the same as the backflush record
1127: -- we don't need to worry transaction qty, uom, type, etc. since those
1128: -- are not null columns and don't have to be the same as the backflush record
1129: -- we also don't need to worry about lot/serial since those info should go
1130: -- with the substitution record
1131: update mtl_transactions_interface
1132: set inventory_item_id = subs_rec.substitution_item_id,
1133: substitution_item_id = null,
1134: substitution_type_id = null
1135: where transaction_interface_id = subs_rec.transaction_interface_id;
1153: fetch bf_c into l_bfInterfaceID;
1154:
1155: if ( bf_c%FOUND) then
1156: -- if found, then delete the bf record
1157: delete from mtl_transactions_interface
1158: where transaction_interface_id = l_bfInterfaceID;
1159:
1160: -- delete the substitution record as well
1161: delete from mtl_transactions_interface
1157: delete from mtl_transactions_interface
1158: where transaction_interface_id = l_bfInterfaceID;
1159:
1160: -- delete the substitution record as well
1161: delete from mtl_transactions_interface
1162: where transaction_interface_id = subs_rec.transaction_interface_id;
1163:
1164: close bf_c;
1165: else
1175: -- If not exist, we will just let go trough, as decided on the meeting.
1176: -- We don't merge the additions into one transaction. They have to be in
1177: -- the same UOM, transaction action, etc. It's decided to have it as 2
1178: -- separate txns(decided by jgu and dsoosai).
1179: update mtl_transactions_interface
1180: set substitution_type_id = null,
1181: inventory_item_id = subs_rec.substitution_item_id,
1182: substitution_item_id = null
1183: where transaction_interface_id = subs_rec.transaction_interface_id;
1201: fetch bf_c into l_bfInterfaceID;
1202:
1203: if ( bf_c%FOUND) then
1204: -- if found, then delete the substitution record
1205: delete from mtl_transactions_interface
1206: where transaction_interface_id = subs_rec.transaction_interface_id;
1207:
1208: -- build the link and update the subinventory and locator id
1209: update mtl_transactions_interface
1205: delete from mtl_transactions_interface
1206: where transaction_interface_id = subs_rec.transaction_interface_id;
1207:
1208: -- build the link and update the subinventory and locator id
1209: update mtl_transactions_interface
1210: set transaction_interface_id = subs_rec.transaction_interface_id,
1211: subinventory_code = subs_rec.subinventory_code,
1212: locator_id = subs_rec.locator_id
1213: where transaction_interface_id = l_bfInterfaceID;
1235: select transaction_source_id,
1236: transaction_date
1237: into l_wipEntityID,
1238: l_txnDate
1239: from mtl_transactions_interface
1240: where transaction_interface_id = p_parentID;
1241:
1242: if ( l_wipEntityID is not null ) then
1243: select project_id, task_id
1246: where wip_entity_id = l_wipEntityID;
1247: end if;
1248:
1249:
1250: update mtl_transactions_interface
1251: set transaction_source_type_id = nvl(transaction_source_type_id, 5),
1252: flow_schedule = nvl(flow_schedule, 'Y'),
1253: source_project_id = l_srcProjID,
1254: source_task_id = l_srcTaskID,
1389: end_item_unit_number,
1390: transaction_header_id,
1391: completion_transaction_id,
1392: rowid
1393: from mtl_transactions_interface
1394: where transaction_interface_id = p_txnInterfaceID
1395: and transaction_source_type_id = 5
1396: and transaction_source_id is null
1397: and flow_schedule = 'Y'
1481: flow_schedule,
1482: transaction_action_id,
1483: scheduled_flag,
1484: process_flag
1485: from mtl_transactions_interface
1486: where transaction_interface_id = p_txnInterfaceID;
1487:
1488: cursor c_MMTTparams is
1489: select transaction_source_type_id,
1491: flow_schedule,
1492: transaction_action_id,
1493: scheduled_flag,
1494: process_flag
1495: from mtl_transactions_interface
1496: where transaction_interface_id = p_txnInterfaceID;
1497:
1498: begin
1499:
1516: begin
1517: if(p_txnInterfaceID is not null) then
1518: select count(*)
1519: into l_count
1520: from mtl_transactions_interface
1521: where transaction_interface_id = p_txnInterfaceID;
1522: wip_logger.log('MTI rowcount is ' || l_count, l_returnStatus);
1523:
1524: for l_paramRec in c_MTIparams loop
1761: end if;
1762:
1763: -- set the transaction_source_id for the assembly record (if from MTI)
1764: --and its components
1765: update mtl_transactions_interface
1766: set last_update_date = sysdate,
1767: last_updated_by = fnd_global.user_id,
1768: last_update_login = fnd_global.login_id,
1769: program_application_id = fnd_global.prog_appl_id,