[Home] [Help]
PACKAGE BODY: APPS.INV_MO_LINE_DETAIL_UTIL
Source
1 PACKAGE BODY inv_mo_line_detail_util AS
2 /* $Header: INVUTLDB.pls 120.1 2008/02/08 09:08:51 aysaha ship $ */
3
4 -- Global constant holding the package name
5
6 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_MO_LINE_DETAIL_UTIL';
7
8 PROCEDURE DEBUG(p_message IN VARCHAR2, p_module IN VARCHAR2) IS
9 BEGIN
10 inv_log_util.trace(p_message, p_module, 9);
11 END;
12
13 -- Procedure Update_Row
14
15 PROCEDURE update_row(x_return_status OUT NOCOPY VARCHAR2, p_mo_line_detail_rec IN g_mmtt_rec) IS
16 BEGIN
17 UPDATE mtl_material_transactions_temp
18 SET transaction_header_id = p_mo_line_detail_rec.transaction_header_id
19 , source_code = p_mo_line_detail_rec.source_code
20 , source_line_id = p_mo_line_detail_rec.source_line_id
21 , transaction_mode = p_mo_line_detail_rec.transaction_mode
22 , lock_flag = p_mo_line_detail_rec.lock_flag
23 , last_update_date = p_mo_line_detail_rec.last_update_date
24 , last_updated_by = p_mo_line_detail_rec.last_updated_by
25 , creation_date = p_mo_line_detail_rec.creation_date
26 , created_by = p_mo_line_detail_rec.created_by
27 , last_update_login = p_mo_line_detail_rec.last_update_login
28 , request_id = p_mo_line_detail_rec.request_id
29 , program_application_id = p_mo_line_detail_rec.program_application_id
30 , program_id = p_mo_line_detail_rec.program_id
31 , program_update_date = p_mo_line_detail_rec.program_update_date
32 , inventory_item_id = p_mo_line_detail_rec.inventory_item_id
33 , revision = p_mo_line_detail_rec.revision
34 , organization_id = p_mo_line_detail_rec.organization_id
35 , subinventory_code = p_mo_line_detail_rec.subinventory_code
36 , locator_id = p_mo_line_detail_rec.locator_id
37 , transaction_quantity = p_mo_line_detail_rec.transaction_quantity
38 , primary_quantity = p_mo_line_detail_rec.primary_quantity
39 , transaction_uom = p_mo_line_detail_rec.transaction_uom
40 , transaction_cost = p_mo_line_detail_rec.transaction_cost
41 , transaction_type_id = p_mo_line_detail_rec.transaction_type_id
42 , transaction_action_id = p_mo_line_detail_rec.transaction_action_id
43 , transaction_source_type_id = p_mo_line_detail_rec.transaction_source_type_id
44 , transaction_source_id = p_mo_line_detail_rec.transaction_source_id
45 , transaction_source_name = p_mo_line_detail_rec.transaction_source_name
46 , transaction_date = p_mo_line_detail_rec.transaction_date
47 , acct_period_id = p_mo_line_detail_rec.acct_period_id
48 , distribution_account_id = p_mo_line_detail_rec.distribution_account_id
49 , transaction_reference = p_mo_line_detail_rec.transaction_reference
50 , requisition_line_id = p_mo_line_detail_rec.requisition_line_id
51 , requisition_distribution_id = p_mo_line_detail_rec.requisition_distribution_id
52 , reason_id = p_mo_line_detail_rec.reason_id
53 , lot_number = p_mo_line_detail_rec.lot_number
54 , lot_expiration_date = p_mo_line_detail_rec.lot_expiration_date
55 , serial_number = p_mo_line_detail_rec.serial_number
56 , receiving_document = p_mo_line_detail_rec.receiving_document
57 , demand_id = p_mo_line_detail_rec.demand_id
58 , rcv_transaction_id = p_mo_line_detail_rec.rcv_transaction_id
59 , move_transaction_id = p_mo_line_detail_rec.move_transaction_id
60 , completion_transaction_id = p_mo_line_detail_rec.completion_transaction_id
61 , wip_entity_type = p_mo_line_detail_rec.wip_entity_type
62 , schedule_id = p_mo_line_detail_rec.schedule_id
63 , repetitive_line_id = p_mo_line_detail_rec.repetitive_line_id
64 , employee_code = p_mo_line_detail_rec.employee_code
65 , primary_switch = p_mo_line_detail_rec.primary_switch
66 , schedule_update_code = p_mo_line_detail_rec.schedule_update_code
67 , setup_teardown_code = p_mo_line_detail_rec.setup_teardown_code
68 , item_ordering = p_mo_line_detail_rec.item_ordering
69 , negative_req_flag = p_mo_line_detail_rec.negative_req_flag
70 , operation_seq_num = p_mo_line_detail_rec.operation_seq_num
71 , picking_line_id = p_mo_line_detail_rec.picking_line_id
72 , trx_source_line_id = p_mo_line_detail_rec.trx_source_line_id
73 , trx_source_delivery_id = p_mo_line_detail_rec.trx_source_delivery_id
74 , physical_adjustment_id = p_mo_line_detail_rec.physical_adjustment_id
75 , cycle_count_id = p_mo_line_detail_rec.cycle_count_id
76 , rma_line_id = p_mo_line_detail_rec.rma_line_id
77 , customer_ship_id = p_mo_line_detail_rec.customer_ship_id
78 , currency_code = p_mo_line_detail_rec.currency_code
79 , currency_conversion_rate = p_mo_line_detail_rec.currency_conversion_rate
80 , currency_conversion_type = p_mo_line_detail_rec.currency_conversion_type
81 , currency_conversion_date = p_mo_line_detail_rec.currency_conversion_date
82 , ussgl_transaction_code = p_mo_line_detail_rec.ussgl_transaction_code
83 , vendor_lot_number = p_mo_line_detail_rec.vendor_lot_number
84 , encumbrance_account = p_mo_line_detail_rec.encumbrance_account
85 , encumbrance_amount = p_mo_line_detail_rec.encumbrance_amount
86 , ship_to_location = p_mo_line_detail_rec.ship_to_location
87 , shipment_number = p_mo_line_detail_rec.shipment_number
88 , transfer_cost = p_mo_line_detail_rec.transfer_cost
89 , transportation_cost = p_mo_line_detail_rec.transportation_cost
90 , transportation_account = p_mo_line_detail_rec.transportation_account
91 , freight_code = p_mo_line_detail_rec.freight_code
92 , containers = p_mo_line_detail_rec.containers
93 , waybill_airbill = p_mo_line_detail_rec.waybill_airbill
94 , expected_arrival_date = p_mo_line_detail_rec.expected_arrival_date
95 , transfer_subinventory = p_mo_line_detail_rec.transfer_subinventory
96 , transfer_organization = p_mo_line_detail_rec.transfer_organization
97 , transfer_to_location = p_mo_line_detail_rec.transfer_to_location
98 , new_average_cost = p_mo_line_detail_rec.new_average_cost
99 , value_change = p_mo_line_detail_rec.value_change
100 , percentage_change = p_mo_line_detail_rec.percentage_change
101 , material_allocation_temp_id = p_mo_line_detail_rec.material_allocation_temp_id
102 , demand_source_header_id = p_mo_line_detail_rec.demand_source_header_id
103 , demand_source_line = p_mo_line_detail_rec.demand_source_line
104 , demand_source_delivery = p_mo_line_detail_rec.demand_source_delivery
105 , item_segments = p_mo_line_detail_rec.item_segments
106 , item_description = p_mo_line_detail_rec.item_description
107 , item_trx_enabled_flag = p_mo_line_detail_rec.item_trx_enabled_flag
108 , item_location_control_code = p_mo_line_detail_rec.item_location_control_code
109 , item_restrict_subinv_code = p_mo_line_detail_rec.item_restrict_subinv_code
110 , item_restrict_locators_code = p_mo_line_detail_rec.item_restrict_locators_code
111 , item_revision_qty_control_code = p_mo_line_detail_rec.item_revision_qty_control_code
112 , item_primary_uom_code = p_mo_line_detail_rec.item_primary_uom_code
113 , item_uom_class = p_mo_line_detail_rec.item_uom_class
114 , item_shelf_life_code = p_mo_line_detail_rec.item_shelf_life_code
115 , item_shelf_life_days = p_mo_line_detail_rec.item_shelf_life_days
116 , item_lot_control_code = p_mo_line_detail_rec.item_lot_control_code
117 , item_serial_control_code = p_mo_line_detail_rec.item_serial_control_code
118 , item_inventory_asset_flag = p_mo_line_detail_rec.item_inventory_asset_flag
119 , allowed_units_lookup_code = p_mo_line_detail_rec.allowed_units_lookup_code
120 , department_id = p_mo_line_detail_rec.department_id
121 , department_code = p_mo_line_detail_rec.department_code
122 , wip_supply_type = p_mo_line_detail_rec.wip_supply_type
123 , supply_subinventory = p_mo_line_detail_rec.supply_subinventory
124 , supply_locator_id = p_mo_line_detail_rec.supply_locator_id
125 , valid_subinventory_flag = p_mo_line_detail_rec.valid_subinventory_flag
126 , valid_locator_flag = p_mo_line_detail_rec.valid_locator_flag
127 , locator_segments = p_mo_line_detail_rec.locator_segments
128 , current_locator_control_code = p_mo_line_detail_rec.current_locator_control_code
129 , number_of_lots_entered = p_mo_line_detail_rec.number_of_lots_entered
130 , wip_commit_flag = p_mo_line_detail_rec.wip_commit_flag
131 , next_lot_number = p_mo_line_detail_rec.next_lot_number
132 , lot_alpha_prefix = p_mo_line_detail_rec.lot_alpha_prefix
133 , next_serial_number = p_mo_line_detail_rec.next_serial_number
134 , serial_alpha_prefix = p_mo_line_detail_rec.serial_alpha_prefix
135 , shippable_flag = p_mo_line_detail_rec.shippable_flag
136 , posting_flag = p_mo_line_detail_rec.posting_flag
137 , required_flag = p_mo_line_detail_rec.required_flag
138 , process_flag = p_mo_line_detail_rec.process_flag
139 , ERROR_CODE = p_mo_line_detail_rec.ERROR_CODE
140 , error_explanation = p_mo_line_detail_rec.error_explanation
141 , movement_id = p_mo_line_detail_rec.movement_id
142 , reservation_quantity = p_mo_line_detail_rec.reservation_quantity
143 , shipped_quantity = p_mo_line_detail_rec.shipped_quantity
144 , transaction_line_number = p_mo_line_detail_rec.transaction_line_number
145 , task_id = p_mo_line_detail_rec.task_id
146 , to_task_id = p_mo_line_detail_rec.to_task_id
147 , source_task_id = p_mo_line_detail_rec.source_task_id
148 , project_id = p_mo_line_detail_rec.project_id
149 , source_project_id = p_mo_line_detail_rec.source_project_id
150 , pa_expenditure_org_id = p_mo_line_detail_rec.pa_expenditure_org_id
151 , to_project_id = p_mo_line_detail_rec.to_project_id
152 , expenditure_type = p_mo_line_detail_rec.expenditure_type
153 , final_completion_flag = p_mo_line_detail_rec.final_completion_flag
154 , transfer_percentage = p_mo_line_detail_rec.transfer_percentage
155 , transaction_sequence_id = p_mo_line_detail_rec.transaction_sequence_id
156 , material_account = p_mo_line_detail_rec.material_account
157 , material_overhead_account = p_mo_line_detail_rec.material_overhead_account
158 , resource_account = p_mo_line_detail_rec.resource_account
159 , outside_processing_account = p_mo_line_detail_rec.outside_processing_account
160 , overhead_account = p_mo_line_detail_rec.overhead_account
161 , flow_schedule = p_mo_line_detail_rec.flow_schedule
162 , cost_group_id = p_mo_line_detail_rec.cost_group_id
163 , demand_class = p_mo_line_detail_rec.demand_class
164 , qa_collection_id = p_mo_line_detail_rec.qa_collection_id
165 , kanban_card_id = p_mo_line_detail_rec.kanban_card_id
166 , overcompletion_transaction_id = p_mo_line_detail_rec.overcompletion_transaction_id
167 , overcompletion_primary_qty = p_mo_line_detail_rec.overcompletion_primary_qty
168 , overcompletion_transaction_qty = p_mo_line_detail_rec.overcompletion_transaction_qty
169 , end_item_unit_number = p_mo_line_detail_rec.end_item_unit_number
170 , scheduled_payback_date = p_mo_line_detail_rec.scheduled_payback_date
171 , line_type_code = p_mo_line_detail_rec.line_type_code
172 , parent_transaction_temp_id = p_mo_line_detail_rec.parent_transaction_temp_id
173 , put_away_strategy_id = p_mo_line_detail_rec.put_away_strategy_id
174 , put_away_rule_id = p_mo_line_detail_rec.put_away_rule_id
175 , pick_strategy_id = p_mo_line_detail_rec.pick_strategy_id
176 , pick_rule_id = p_mo_line_detail_rec.pick_rule_id
177 , common_bom_seq_id = p_mo_line_detail_rec.common_bom_seq_id
178 , common_routing_seq_id = p_mo_line_detail_rec.common_routing_seq_id
179 , cost_type_id = p_mo_line_detail_rec.cost_type_id
180 , org_cost_group_id = p_mo_line_detail_rec.org_cost_group_id
181 , move_order_line_id = p_mo_line_detail_rec.move_order_line_id
182 , task_group_id = p_mo_line_detail_rec.task_group_id
183 , pick_slip_number = p_mo_line_detail_rec.pick_slip_number
184 , reservation_id = p_mo_line_detail_rec.reservation_id
185 , transaction_status = p_mo_line_detail_rec.transaction_status
186 , transfer_cost_group_id = p_mo_line_detail_rec.transfer_cost_group_id
187 , lpn_id = p_mo_line_detail_rec.lpn_id
188 , transfer_lpn_id = p_mo_line_detail_rec.transfer_lpn_id
189 , pick_slip_date = p_mo_line_detail_rec.pick_slip_date
190 , content_lpn_id = p_mo_line_detail_rec.content_lpn_id
191 , secondary_transaction_quantity = p_mo_line_detail_rec.secondary_transaction_quantity -- INVCONV change
192 , secondary_uom_code = p_mo_line_detail_rec.secondary_uom_code -- INVCONV change
193 WHERE move_order_line_id = p_mo_line_detail_rec.move_order_line_id
194 AND transaction_temp_id = p_mo_line_detail_rec.transaction_temp_id;
195
196 x_return_status := fnd_api.g_ret_sts_success;
197 EXCEPTION
198 WHEN OTHERS THEN
199 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
200 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Update_Row');
201 END IF;
202
203 x_return_status := fnd_api.g_ret_sts_unexp_error;
204 RAISE fnd_api.g_exc_unexpected_error;
205 END update_row;
206
207 -- Procedure Insert_Row
208 PROCEDURE insert_row(x_return_status OUT NOCOPY VARCHAR2, p_mo_line_detail_rec IN g_mmtt_rec) IS
209 BEGIN
210 x_return_status := fnd_api.g_ret_sts_success;
211
212 INSERT INTO mtl_material_transactions_temp
213 (
214 transaction_header_id
215 , transaction_temp_id
216 , source_code
217 , source_line_id
218 , transaction_mode
219 , lock_flag
220 , last_update_date
221 , last_updated_by
222 , creation_date
223 , created_by
224 , last_update_login
225 , request_id
226 , program_application_id
227 , program_id
228 , program_update_date
229 , inventory_item_id
230 , revision
231 , organization_id
232 , subinventory_code
233 , locator_id
234 , transaction_quantity
235 , primary_quantity
236 , transaction_uom
237 , transaction_cost
238 , transaction_type_id
239 , transaction_action_id
240 , transaction_source_type_id
241 , transaction_source_id
242 , transaction_source_name
243 , transaction_date
244 , acct_period_id
245 , distribution_account_id
246 , transaction_reference
247 , requisition_line_id
248 , requisition_distribution_id
249 , reason_id
250 , lot_number
251 , lot_expiration_date
252 , serial_number
253 , receiving_document
254 , demand_id
255 , rcv_transaction_id
256 , move_transaction_id
257 , completion_transaction_id
258 , wip_entity_type
259 , schedule_id
260 , repetitive_line_id
261 , employee_code
262 , primary_switch
263 , schedule_update_code
264 , setup_teardown_code
265 , item_ordering
266 , negative_req_flag
267 , operation_seq_num
268 , picking_line_id
269 , trx_source_line_id
270 , trx_source_delivery_id
271 , physical_adjustment_id
272 , cycle_count_id
273 , rma_line_id
274 , customer_ship_id
275 , currency_code
276 , currency_conversion_rate
277 , currency_conversion_type
278 , currency_conversion_date
279 , ussgl_transaction_code
280 , vendor_lot_number
281 , encumbrance_account
282 , encumbrance_amount
283 , ship_to_location
284 , shipment_number
285 , transfer_cost
286 , transportation_cost
287 , transportation_account
288 , freight_code
289 , containers
290 , waybill_airbill
291 , expected_arrival_date
292 , transfer_subinventory
293 , transfer_organization
294 , transfer_to_location
295 , new_average_cost
296 , value_change
297 , percentage_change
298 , material_allocation_temp_id
299 , demand_source_header_id
300 , demand_source_line
301 , demand_source_delivery
302 , item_segments
303 , item_description
304 , item_trx_enabled_flag
305 , item_location_control_code
306 , item_restrict_subinv_code
307 , item_restrict_locators_code
308 , item_revision_qty_control_code
309 , item_primary_uom_code
310 , item_uom_class
311 , item_shelf_life_code
312 , item_shelf_life_days
313 , item_lot_control_code
314 , item_serial_control_code
315 , item_inventory_asset_flag
316 , allowed_units_lookup_code
317 , department_id
318 , department_code
319 , wip_supply_type
320 , supply_subinventory
321 , supply_locator_id
322 , valid_subinventory_flag
323 , valid_locator_flag
324 , locator_segments
325 , current_locator_control_code
326 , number_of_lots_entered
327 , wip_commit_flag
328 , next_lot_number
329 , lot_alpha_prefix
330 , next_serial_number
331 , serial_alpha_prefix
332 , shippable_flag
333 , posting_flag
334 , required_flag
335 , process_flag
336 , ERROR_CODE
337 , error_explanation
338 , attribute_category
339 , attribute1
340 , attribute2
341 , attribute3
342 , attribute4
343 , attribute5
344 , attribute6
345 , attribute7
346 , attribute8
347 , attribute9
348 , attribute10
349 , attribute11
350 , attribute12
351 , attribute13
352 , attribute14
353 , attribute15
354 , movement_id
355 , reservation_quantity
356 , shipped_quantity
357 , transaction_line_number
358 , task_id
359 , to_task_id
360 , source_task_id
361 , project_id
362 , source_project_id
363 , pa_expenditure_org_id
364 , to_project_id
365 , expenditure_type
366 , final_completion_flag
367 , transfer_percentage
368 , transaction_sequence_id
369 , material_account
370 , material_overhead_account
371 , resource_account
372 , outside_processing_account
373 , overhead_account
374 , flow_schedule
375 , cost_group_id
376 , demand_class
377 , qa_collection_id
378 , kanban_card_id
379 , overcompletion_transaction_id
380 , overcompletion_primary_qty
381 , overcompletion_transaction_qty
382 , end_item_unit_number
383 , scheduled_payback_date
384 , line_type_code
385 , parent_transaction_temp_id
386 , put_away_strategy_id
387 , put_away_rule_id
388 , pick_strategy_id
389 , pick_rule_id
390 , common_bom_seq_id
391 , common_routing_seq_id
392 , cost_type_id
393 , org_cost_group_id
394 , move_order_line_id
395 , task_group_id
396 , pick_slip_number
397 , reservation_id
398 , transaction_status
399 , transfer_cost_group_id
400 , lpn_id
401 , transfer_lpn_id
402 , pick_slip_date
403 , content_lpn_id
404 , secondary_transaction_quantity -- INVCONV change
405 , secondary_uom_code -- INVCONV change
406 )
407 VALUES (
408 p_mo_line_detail_rec.transaction_header_id
409 , p_mo_line_detail_rec.transaction_temp_id
410 , p_mo_line_detail_rec.source_code
411 , p_mo_line_detail_rec.source_line_id
412 , p_mo_line_detail_rec.transaction_mode
413 , p_mo_line_detail_rec.lock_flag
414 , p_mo_line_detail_rec.last_update_date
415 , p_mo_line_detail_rec.last_updated_by
416 , p_mo_line_detail_rec.creation_date
417 , p_mo_line_detail_rec.created_by
418 , p_mo_line_detail_rec.last_update_login
419 , p_mo_line_detail_rec.request_id
420 , p_mo_line_detail_rec.program_application_id
421 , p_mo_line_detail_rec.program_id
422 , p_mo_line_detail_rec.program_update_date
423 , p_mo_line_detail_rec.inventory_item_id
424 , p_mo_line_detail_rec.revision
425 , p_mo_line_detail_rec.organization_id
426 , p_mo_line_detail_rec.subinventory_code
427 , p_mo_line_detail_rec.locator_id
428 , p_mo_line_detail_rec.transaction_quantity
429 , p_mo_line_detail_rec.primary_quantity
430 , p_mo_line_detail_rec.transaction_uom
431 , p_mo_line_detail_rec.transaction_cost
432 , p_mo_line_detail_rec.transaction_type_id
433 , p_mo_line_detail_rec.transaction_action_id
434 , p_mo_line_detail_rec.transaction_source_type_id
435 , p_mo_line_detail_rec.transaction_source_id
436 , p_mo_line_detail_rec.transaction_source_name
437 , p_mo_line_detail_rec.transaction_date
438 , p_mo_line_detail_rec.acct_period_id
439 , p_mo_line_detail_rec.distribution_account_id
440 , p_mo_line_detail_rec.transaction_reference
441 , p_mo_line_detail_rec.requisition_line_id
442 , p_mo_line_detail_rec.requisition_distribution_id
443 , p_mo_line_detail_rec.reason_id
444 , p_mo_line_detail_rec.lot_number
445 , p_mo_line_detail_rec.lot_expiration_date
446 , p_mo_line_detail_rec.serial_number
447 , p_mo_line_detail_rec.receiving_document
448 , p_mo_line_detail_rec.demand_id
449 , p_mo_line_detail_rec.rcv_transaction_id
450 , p_mo_line_detail_rec.move_transaction_id
451 , p_mo_line_detail_rec.completion_transaction_id
452 , p_mo_line_detail_rec.wip_entity_type
453 , p_mo_line_detail_rec.schedule_id
454 , p_mo_line_detail_rec.repetitive_line_id
455 , p_mo_line_detail_rec.employee_code
456 , p_mo_line_detail_rec.primary_switch
457 , p_mo_line_detail_rec.schedule_update_code
458 , p_mo_line_detail_rec.setup_teardown_code
459 , p_mo_line_detail_rec.item_ordering
460 , p_mo_line_detail_rec.negative_req_flag
461 , p_mo_line_detail_rec.operation_seq_num
462 , p_mo_line_detail_rec.picking_line_id
463 , p_mo_line_detail_rec.trx_source_line_id
464 , p_mo_line_detail_rec.trx_source_delivery_id
465 , p_mo_line_detail_rec.physical_adjustment_id
466 , p_mo_line_detail_rec.cycle_count_id
467 , p_mo_line_detail_rec.rma_line_id
468 , p_mo_line_detail_rec.customer_ship_id
469 , p_mo_line_detail_rec.currency_code
470 , p_mo_line_detail_rec.currency_conversion_rate
471 , p_mo_line_detail_rec.currency_conversion_type
472 , p_mo_line_detail_rec.currency_conversion_date
473 , p_mo_line_detail_rec.ussgl_transaction_code
474 , p_mo_line_detail_rec.vendor_lot_number
475 , p_mo_line_detail_rec.encumbrance_account
476 , p_mo_line_detail_rec.encumbrance_amount
477 , p_mo_line_detail_rec.ship_to_location
478 , p_mo_line_detail_rec.shipment_number
479 , p_mo_line_detail_rec.transfer_cost
480 , p_mo_line_detail_rec.transportation_cost
481 , p_mo_line_detail_rec.transportation_account
482 , p_mo_line_detail_rec.freight_code
483 , p_mo_line_detail_rec.containers
484 , p_mo_line_detail_rec.waybill_airbill
485 , p_mo_line_detail_rec.expected_arrival_date
486 , p_mo_line_detail_rec.transfer_subinventory
487 , p_mo_line_detail_rec.transfer_organization
488 , p_mo_line_detail_rec.transfer_to_location
489 , p_mo_line_detail_rec.new_average_cost
490 , p_mo_line_detail_rec.value_change
491 , p_mo_line_detail_rec.percentage_change
492 , p_mo_line_detail_rec.material_allocation_temp_id
493 , p_mo_line_detail_rec.demand_source_header_id
494 , p_mo_line_detail_rec.demand_source_line
495 , p_mo_line_detail_rec.demand_source_delivery
496 , p_mo_line_detail_rec.item_segments
497 , p_mo_line_detail_rec.item_description
498 , p_mo_line_detail_rec.item_trx_enabled_flag
499 , p_mo_line_detail_rec.item_location_control_code
500 , p_mo_line_detail_rec.item_restrict_subinv_code
501 , p_mo_line_detail_rec.item_restrict_locators_code
502 , p_mo_line_detail_rec.item_revision_qty_control_code
503 , p_mo_line_detail_rec.item_primary_uom_code
504 , p_mo_line_detail_rec.item_uom_class
505 , p_mo_line_detail_rec.item_shelf_life_code
506 , p_mo_line_detail_rec.item_shelf_life_days
507 , p_mo_line_detail_rec.item_lot_control_code
508 , p_mo_line_detail_rec.item_serial_control_code
509 , p_mo_line_detail_rec.item_inventory_asset_flag
510 , p_mo_line_detail_rec.allowed_units_lookup_code
511 , p_mo_line_detail_rec.department_id
512 , p_mo_line_detail_rec.department_code
513 , p_mo_line_detail_rec.wip_supply_type
514 , p_mo_line_detail_rec.supply_subinventory
515 , p_mo_line_detail_rec.supply_locator_id
516 , p_mo_line_detail_rec.valid_subinventory_flag
517 , p_mo_line_detail_rec.valid_locator_flag
518 , p_mo_line_detail_rec.locator_segments
519 , p_mo_line_detail_rec.current_locator_control_code
520 , p_mo_line_detail_rec.number_of_lots_entered
521 , p_mo_line_detail_rec.wip_commit_flag
522 , p_mo_line_detail_rec.next_lot_number
523 , p_mo_line_detail_rec.lot_alpha_prefix
524 , p_mo_line_detail_rec.next_serial_number
525 , p_mo_line_detail_rec.serial_alpha_prefix
526 , p_mo_line_detail_rec.shippable_flag
527 , p_mo_line_detail_rec.posting_flag
528 , p_mo_line_detail_rec.required_flag
529 , p_mo_line_detail_rec.process_flag
530 , p_mo_line_detail_rec.ERROR_CODE
531 , p_mo_line_detail_rec.error_explanation
532 , p_mo_line_detail_rec.attribute_category
533 , p_mo_line_detail_rec.attribute1
534 , p_mo_line_detail_rec.attribute2
535 , p_mo_line_detail_rec.attribute3
536 , p_mo_line_detail_rec.attribute4
537 , p_mo_line_detail_rec.attribute5
538 , p_mo_line_detail_rec.attribute6
539 , p_mo_line_detail_rec.attribute7
540 , p_mo_line_detail_rec.attribute8
541 , p_mo_line_detail_rec.attribute9
542 , p_mo_line_detail_rec.attribute10
543 , p_mo_line_detail_rec.attribute11
544 , p_mo_line_detail_rec.attribute12
545 , p_mo_line_detail_rec.attribute13
546 , p_mo_line_detail_rec.attribute14
547 , p_mo_line_detail_rec.attribute15
548 , p_mo_line_detail_rec.movement_id
549 , p_mo_line_detail_rec.reservation_quantity
550 , p_mo_line_detail_rec.shipped_quantity
551 , p_mo_line_detail_rec.transaction_line_number
552 , p_mo_line_detail_rec.task_id
553 , p_mo_line_detail_rec.to_task_id
554 , p_mo_line_detail_rec.source_task_id
555 , p_mo_line_detail_rec.project_id
556 , p_mo_line_detail_rec.source_project_id
557 , p_mo_line_detail_rec.pa_expenditure_org_id
558 , p_mo_line_detail_rec.to_project_id
559 , p_mo_line_detail_rec.expenditure_type
560 , p_mo_line_detail_rec.final_completion_flag
561 , p_mo_line_detail_rec.transfer_percentage
562 , p_mo_line_detail_rec.transaction_sequence_id
563 , p_mo_line_detail_rec.material_account
564 , p_mo_line_detail_rec.material_overhead_account
565 , p_mo_line_detail_rec.resource_account
566 , p_mo_line_detail_rec.outside_processing_account
567 , p_mo_line_detail_rec.overhead_account
568 , p_mo_line_detail_rec.flow_schedule
569 , p_mo_line_detail_rec.cost_group_id
570 , p_mo_line_detail_rec.demand_class
571 , p_mo_line_detail_rec.qa_collection_id
572 , p_mo_line_detail_rec.kanban_card_id
573 , p_mo_line_detail_rec.overcompletion_transaction_id
574 , p_mo_line_detail_rec.overcompletion_primary_qty
575 , p_mo_line_detail_rec.overcompletion_transaction_qty
576 , p_mo_line_detail_rec.end_item_unit_number
577 , p_mo_line_detail_rec.scheduled_payback_date
578 , p_mo_line_detail_rec.line_type_code
579 , p_mo_line_detail_rec.parent_transaction_temp_id
580 , p_mo_line_detail_rec.put_away_strategy_id
581 , p_mo_line_detail_rec.put_away_rule_id
582 , p_mo_line_detail_rec.pick_strategy_id
583 , p_mo_line_detail_rec.pick_rule_id
584 , p_mo_line_detail_rec.common_bom_seq_id
585 , p_mo_line_detail_rec.common_routing_seq_id
586 , p_mo_line_detail_rec.cost_type_id
587 , p_mo_line_detail_rec.org_cost_group_id
588 , p_mo_line_detail_rec.move_order_line_id
589 , p_mo_line_detail_rec.task_group_id
590 , p_mo_line_detail_rec.pick_slip_number
591 , p_mo_line_detail_rec.reservation_id
592 , p_mo_line_detail_rec.transaction_status
593 , p_mo_line_detail_rec.transfer_cost_group_id
594 , p_mo_line_detail_rec.lpn_id
595 , p_mo_line_detail_rec.transfer_lpn_id
596 , p_mo_line_detail_rec.pick_slip_date
597 , p_mo_line_detail_rec.content_lpn_id
598 , p_mo_line_detail_rec.secondary_transaction_quantity -- INVCONV change
599 , p_mo_line_detail_rec.secondary_uom_code -- INVCONV change
600 );
601 END insert_row;
602
603 -- Procedure Delete_Row
604 PROCEDURE delete_row(x_return_status OUT NOCOPY VARCHAR2, p_line_id IN NUMBER, p_line_detail_id IN NUMBER) IS
605 BEGIN
606 DELETE FROM mtl_material_transactions_temp
607 WHERE move_order_line_id = p_line_id
608 AND transaction_temp_id = p_line_detail_id;
609
610 x_return_status := fnd_api.g_ret_sts_success;
611 EXCEPTION
612 WHEN OTHERS THEN
613 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
614 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Delete_Row');
615 END IF;
616
617 x_return_status := fnd_api.g_ret_sts_unexp_error;
618 RAISE fnd_api.g_exc_unexpected_error;
619 END delete_row;
620
621 -- Procedure lock_Row
622 PROCEDURE lock_row(
623 x_return_status OUT NOCOPY VARCHAR2
624 , p_mo_line_detail_rec IN g_mmtt_rec
625 , x_mo_line_detail_rec OUT NOCOPY g_mmtt_rec
626 ) IS
627 l_mmtt_rec g_mmtt_rec;
628 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
629 BEGIN
630 SELECT transaction_header_id
631 , transaction_temp_id
632 , source_code
633 , source_line_id
634 , transaction_mode
635 , lock_flag
636 , last_update_date
637 , last_updated_by
638 , creation_date
639 , created_by
640 , last_update_login
641 , request_id
642 , program_application_id
643 , program_id
644 , program_update_date
645 , inventory_item_id
646 , revision
647 , organization_id
648 , subinventory_code
649 , locator_id
650 , transaction_quantity
651 , primary_quantity
652 , transaction_uom
653 , transaction_cost
654 , transaction_type_id
655 , transaction_action_id
656 , transaction_source_type_id
657 , transaction_source_id
658 , transaction_source_name
659 , transaction_date
660 , acct_period_id
661 , distribution_account_id
662 , transaction_reference
663 , requisition_line_id
664 , requisition_distribution_id
665 , reason_id
666 , lot_number
667 , lot_expiration_date
668 , serial_number
669 , receiving_document
670 , demand_id
671 , rcv_transaction_id
672 , move_transaction_id
673 , completion_transaction_id
674 , wip_entity_type
675 , schedule_id
676 , repetitive_line_id
677 , employee_code
678 , primary_switch
679 , schedule_update_code
680 , setup_teardown_code
681 , item_ordering
682 , negative_req_flag
683 , operation_seq_num
684 , picking_line_id
685 , trx_source_line_id
686 , trx_source_delivery_id
687 , physical_adjustment_id
688 , cycle_count_id
689 , rma_line_id
690 , customer_ship_id
691 , currency_code
692 , currency_conversion_rate
693 , currency_conversion_type
694 , currency_conversion_date
695 , ussgl_transaction_code
696 , vendor_lot_number
697 , encumbrance_account
698 , encumbrance_amount
699 , ship_to_location
700 , shipment_number
701 , transfer_cost
702 , transportation_cost
703 , transportation_account
704 , freight_code
705 , containers
706 , waybill_airbill
707 , expected_arrival_date
708 , transfer_subinventory
709 , transfer_organization
710 , transfer_to_location
711 , new_average_cost
712 , value_change
713 , percentage_change
714 , material_allocation_temp_id
715 , demand_source_header_id
716 , demand_source_line
717 , demand_source_delivery
718 , item_segments
719 , item_description
720 , item_trx_enabled_flag
721 , item_location_control_code
722 , item_restrict_subinv_code
723 , item_restrict_locators_code
724 , item_revision_qty_control_code
725 , item_primary_uom_code
726 , item_uom_class
727 , item_shelf_life_code
728 , item_shelf_life_days
729 , item_lot_control_code
730 , item_serial_control_code
731 , item_inventory_asset_flag
732 , allowed_units_lookup_code
733 , department_id
734 , department_code
735 , wip_supply_type
736 , supply_subinventory
737 , supply_locator_id
738 , valid_subinventory_flag
739 , valid_locator_flag
740 , locator_segments
741 , current_locator_control_code
742 , number_of_lots_entered
743 , wip_commit_flag
744 , next_lot_number
745 , lot_alpha_prefix
746 , next_serial_number
747 , serial_alpha_prefix
748 , shippable_flag
749 , posting_flag
750 , required_flag
751 , process_flag
752 , ERROR_CODE
753 , error_explanation
754 , attribute_category
755 , attribute1
756 , attribute2
757 , attribute3
758 , attribute4
759 , attribute5
760 , attribute6
761 , attribute7
762 , attribute8
763 , attribute9
764 , attribute10
765 , attribute11
766 , attribute12
767 , attribute13
768 , attribute14
769 , attribute15
770 , movement_id
771 , reservation_quantity
772 , shipped_quantity
773 , transaction_line_number
774 , task_id
775 , to_task_id
776 , source_task_id
777 , project_id
778 , source_project_id
779 , pa_expenditure_org_id
780 , to_project_id
781 , expenditure_type
782 , final_completion_flag
783 , transfer_percentage
784 , transaction_sequence_id
785 , material_account
786 , material_overhead_account
787 , resource_account
788 , outside_processing_account
789 , overhead_account
790 , flow_schedule
791 , cost_group_id
792 , demand_class
793 , qa_collection_id
794 , kanban_card_id
795 , overcompletion_transaction_id
796 , overcompletion_primary_qty
797 , overcompletion_transaction_qty
798 , end_item_unit_number
799 , scheduled_payback_date
800 , line_type_code
801 , parent_transaction_temp_id
802 , put_away_strategy_id
803 , put_away_rule_id
804 , pick_strategy_id
805 , pick_rule_id
806 , common_bom_seq_id
807 , common_routing_seq_id
808 , cost_type_id
809 , org_cost_group_id
810 , move_order_line_id
811 , task_group_id
812 , pick_slip_number
813 , reservation_id
814 , transaction_status
815 , transfer_cost_group_id
816 , lpn_id
817 , transfer_lpn_id
818 , pick_slip_date
819 , content_lpn_id
820 , secondary_transaction_quantity -- INVCONV change
821 , secondary_uom_code -- INVCONV change
822 INTO l_mmtt_rec.transaction_header_id
823 , l_mmtt_rec.transaction_temp_id
824 , l_mmtt_rec.source_code
825 , l_mmtt_rec.source_line_id
826 , l_mmtt_rec.transaction_mode
827 , l_mmtt_rec.lock_flag
828 , l_mmtt_rec.last_update_date
829 , l_mmtt_rec.last_updated_by
830 , l_mmtt_rec.creation_date
831 , l_mmtt_rec.created_by
832 , l_mmtt_rec.last_update_login
833 , l_mmtt_rec.request_id
834 , l_mmtt_rec.program_application_id
835 , l_mmtt_rec.program_id
836 , l_mmtt_rec.program_update_date
837 , l_mmtt_rec.inventory_item_id
838 , l_mmtt_rec.revision
839 , l_mmtt_rec.organization_id
840 , l_mmtt_rec.subinventory_code
841 , l_mmtt_rec.locator_id
842 , l_mmtt_rec.transaction_quantity
843 , l_mmtt_rec.primary_quantity
844 , l_mmtt_rec.transaction_uom
845 , l_mmtt_rec.transaction_cost
846 , l_mmtt_rec.transaction_type_id
847 , l_mmtt_rec.transaction_action_id
848 , l_mmtt_rec.transaction_source_type_id
849 , l_mmtt_rec.transaction_source_id
850 , l_mmtt_rec.transaction_source_name
851 , l_mmtt_rec.transaction_date
852 , l_mmtt_rec.acct_period_id
853 , l_mmtt_rec.distribution_account_id
854 , l_mmtt_rec.transaction_reference
855 , l_mmtt_rec.requisition_line_id
856 , l_mmtt_rec.requisition_distribution_id
857 , l_mmtt_rec.reason_id
858 , l_mmtt_rec.lot_number
859 , l_mmtt_rec.lot_expiration_date
860 , l_mmtt_rec.serial_number
861 , l_mmtt_rec.receiving_document
862 , l_mmtt_rec.demand_id
863 , l_mmtt_rec.rcv_transaction_id
864 , l_mmtt_rec.move_transaction_id
865 , l_mmtt_rec.completion_transaction_id
866 , l_mmtt_rec.wip_entity_type
867 , l_mmtt_rec.schedule_id
868 , l_mmtt_rec.repetitive_line_id
869 , l_mmtt_rec.employee_code
870 , l_mmtt_rec.primary_switch
871 , l_mmtt_rec.schedule_update_code
872 , l_mmtt_rec.setup_teardown_code
873 , l_mmtt_rec.item_ordering
874 , l_mmtt_rec.negative_req_flag
875 , l_mmtt_rec.operation_seq_num
876 , l_mmtt_rec.picking_line_id
877 , l_mmtt_rec.trx_source_line_id
878 , l_mmtt_rec.trx_source_delivery_id
879 , l_mmtt_rec.physical_adjustment_id
880 , l_mmtt_rec.cycle_count_id
881 , l_mmtt_rec.rma_line_id
882 , l_mmtt_rec.customer_ship_id
883 , l_mmtt_rec.currency_code
884 , l_mmtt_rec.currency_conversion_rate
885 , l_mmtt_rec.currency_conversion_type
886 , l_mmtt_rec.currency_conversion_date
887 , l_mmtt_rec.ussgl_transaction_code
888 , l_mmtt_rec.vendor_lot_number
889 , l_mmtt_rec.encumbrance_account
890 , l_mmtt_rec.encumbrance_amount
891 , l_mmtt_rec.ship_to_location
892 , l_mmtt_rec.shipment_number
893 , l_mmtt_rec.transfer_cost
894 , l_mmtt_rec.transportation_cost
895 , l_mmtt_rec.transportation_account
896 , l_mmtt_rec.freight_code
897 , l_mmtt_rec.containers
898 , l_mmtt_rec.waybill_airbill
899 , l_mmtt_rec.expected_arrival_date
900 , l_mmtt_rec.transfer_subinventory
901 , l_mmtt_rec.transfer_organization
902 , l_mmtt_rec.transfer_to_location
903 , l_mmtt_rec.new_average_cost
904 , l_mmtt_rec.value_change
905 , l_mmtt_rec.percentage_change
906 , l_mmtt_rec.material_allocation_temp_id
907 , l_mmtt_rec.demand_source_header_id
908 , l_mmtt_rec.demand_source_line
909 , l_mmtt_rec.demand_source_delivery
910 , l_mmtt_rec.item_segments
911 , l_mmtt_rec.item_description
912 , l_mmtt_rec.item_trx_enabled_flag
913 , l_mmtt_rec.item_location_control_code
914 , l_mmtt_rec.item_restrict_subinv_code
915 , l_mmtt_rec.item_restrict_locators_code
916 , l_mmtt_rec.item_revision_qty_control_code
917 , l_mmtt_rec.item_primary_uom_code
918 , l_mmtt_rec.item_uom_class
919 , l_mmtt_rec.item_shelf_life_code
920 , l_mmtt_rec.item_shelf_life_days
921 , l_mmtt_rec.item_lot_control_code
922 , l_mmtt_rec.item_serial_control_code
923 , l_mmtt_rec.item_inventory_asset_flag
924 , l_mmtt_rec.allowed_units_lookup_code
925 , l_mmtt_rec.department_id
926 , l_mmtt_rec.department_code
927 , l_mmtt_rec.wip_supply_type
928 , l_mmtt_rec.supply_subinventory
929 , l_mmtt_rec.supply_locator_id
930 , l_mmtt_rec.valid_subinventory_flag
931 , l_mmtt_rec.valid_locator_flag
932 , l_mmtt_rec.locator_segments
933 , l_mmtt_rec.current_locator_control_code
934 , l_mmtt_rec.number_of_lots_entered
935 , l_mmtt_rec.wip_commit_flag
936 , l_mmtt_rec.next_lot_number
937 , l_mmtt_rec.lot_alpha_prefix
938 , l_mmtt_rec.next_serial_number
939 , l_mmtt_rec.serial_alpha_prefix
940 , l_mmtt_rec.shippable_flag
941 , l_mmtt_rec.posting_flag
942 , l_mmtt_rec.required_flag
943 , l_mmtt_rec.process_flag
944 , l_mmtt_rec.ERROR_CODE
945 , l_mmtt_rec.error_explanation
946 , l_mmtt_rec.attribute_category
947 , l_mmtt_rec.attribute1
948 , l_mmtt_rec.attribute2
949 , l_mmtt_rec.attribute3
950 , l_mmtt_rec.attribute4
951 , l_mmtt_rec.attribute5
952 , l_mmtt_rec.attribute6
953 , l_mmtt_rec.attribute7
954 , l_mmtt_rec.attribute8
955 , l_mmtt_rec.attribute9
956 , l_mmtt_rec.attribute10
957 , l_mmtt_rec.attribute11
958 , l_mmtt_rec.attribute12
959 , l_mmtt_rec.attribute13
960 , l_mmtt_rec.attribute14
961 , l_mmtt_rec.attribute15
962 , l_mmtt_rec.movement_id
963 , l_mmtt_rec.reservation_quantity
964 , l_mmtt_rec.shipped_quantity
965 , l_mmtt_rec.transaction_line_number
966 , l_mmtt_rec.task_id
967 , l_mmtt_rec.to_task_id
968 , l_mmtt_rec.source_task_id
969 , l_mmtt_rec.project_id
970 , l_mmtt_rec.source_project_id
971 , l_mmtt_rec.pa_expenditure_org_id
972 , l_mmtt_rec.to_project_id
973 , l_mmtt_rec.expenditure_type
974 , l_mmtt_rec.final_completion_flag
975 , l_mmtt_rec.transfer_percentage
976 , l_mmtt_rec.transaction_sequence_id
977 , l_mmtt_rec.material_account
978 , l_mmtt_rec.material_overhead_account
979 , l_mmtt_rec.resource_account
980 , l_mmtt_rec.outside_processing_account
981 , l_mmtt_rec.overhead_account
982 , l_mmtt_rec.flow_schedule
983 , l_mmtt_rec.cost_group_id
984 , l_mmtt_rec.demand_class
985 , l_mmtt_rec.qa_collection_id
986 , l_mmtt_rec.kanban_card_id
987 , l_mmtt_rec.overcompletion_transaction_id
988 , l_mmtt_rec.overcompletion_primary_qty
989 , l_mmtt_rec.overcompletion_transaction_qty
990 , l_mmtt_rec.end_item_unit_number
991 , l_mmtt_rec.scheduled_payback_date
992 , l_mmtt_rec.line_type_code
993 , l_mmtt_rec.parent_transaction_temp_id
994 , l_mmtt_rec.put_away_strategy_id
995 , l_mmtt_rec.put_away_rule_id
996 , l_mmtt_rec.pick_strategy_id
997 , l_mmtt_rec.pick_rule_id
998 , l_mmtt_rec.common_bom_seq_id
999 , l_mmtt_rec.common_routing_seq_id
1000 , l_mmtt_rec.cost_type_id
1001 , l_mmtt_rec.org_cost_group_id
1002 , l_mmtt_rec.move_order_line_id
1003 , l_mmtt_rec.task_group_id
1004 , l_mmtt_rec.pick_slip_number
1005 , l_mmtt_rec.reservation_id
1006 , l_mmtt_rec.transaction_status
1007 , l_mmtt_rec.transfer_cost_group_id
1008 , l_mmtt_rec.lpn_id
1009 , l_mmtt_rec.transfer_lpn_id
1010 , l_mmtt_rec.pick_slip_date
1011 , l_mmtt_rec.content_lpn_id
1012 , l_mmtt_rec.secondary_transaction_quantity -- INVCONV change
1013 , l_mmtt_rec.secondary_uom_code -- INVCONV change
1014 FROM mtl_material_transactions_temp
1015 WHERE move_order_line_id = p_mo_line_detail_rec.move_order_line_id
1016 FOR UPDATE NOWAIT;
1017
1018 -- Row locked. Compare IN attributes to DB attributes.
1019
1020 IF inv_globals.equal(p_mo_line_detail_rec.transaction_header_id, l_mmtt_rec.transaction_header_id)
1021 AND inv_globals.equal(p_mo_line_detail_rec.transaction_temp_id, l_mmtt_rec.transaction_temp_id)
1022 AND inv_globals.equal(p_mo_line_detail_rec.source_code, l_mmtt_rec.source_code)
1023 AND inv_globals.equal(p_mo_line_detail_rec.source_line_id, l_mmtt_rec.source_line_id)
1024 AND inv_globals.equal(p_mo_line_detail_rec.transaction_mode, l_mmtt_rec.transaction_mode)
1025 AND inv_globals.equal(p_mo_line_detail_rec.lock_flag, l_mmtt_rec.lock_flag)
1026 AND inv_globals.equal(p_mo_line_detail_rec.last_update_date, l_mmtt_rec.last_update_date)
1027 AND inv_globals.equal(p_mo_line_detail_rec.last_updated_by, l_mmtt_rec.last_updated_by)
1028 AND inv_globals.equal(p_mo_line_detail_rec.creation_date, l_mmtt_rec.creation_date)
1029 AND inv_globals.equal(p_mo_line_detail_rec.created_by, l_mmtt_rec.created_by)
1030 AND inv_globals.equal(p_mo_line_detail_rec.last_update_login, l_mmtt_rec.last_update_login)
1031 AND inv_globals.equal(p_mo_line_detail_rec.request_id, l_mmtt_rec.request_id)
1032 AND inv_globals.equal(p_mo_line_detail_rec.program_application_id, l_mmtt_rec.program_application_id)
1033 AND inv_globals.equal(p_mo_line_detail_rec.program_id, l_mmtt_rec.program_id)
1034 AND inv_globals.equal(p_mo_line_detail_rec.program_update_date, l_mmtt_rec.program_update_date)
1035 AND inv_globals.equal(p_mo_line_detail_rec.inventory_item_id, l_mmtt_rec.inventory_item_id)
1036 AND inv_globals.equal(p_mo_line_detail_rec.revision, l_mmtt_rec.revision)
1037 AND inv_globals.equal(p_mo_line_detail_rec.organization_id, l_mmtt_rec.organization_id)
1038 AND inv_globals.equal(p_mo_line_detail_rec.subinventory_code, l_mmtt_rec.subinventory_code)
1039 AND inv_globals.equal(p_mo_line_detail_rec.locator_id, l_mmtt_rec.locator_id)
1040 AND inv_globals.equal(p_mo_line_detail_rec.transaction_quantity, l_mmtt_rec.transaction_quantity)
1041 AND inv_globals.equal(p_mo_line_detail_rec.primary_quantity, l_mmtt_rec.primary_quantity)
1042 AND inv_globals.equal(p_mo_line_detail_rec.transaction_uom, l_mmtt_rec.transaction_uom)
1043 AND inv_globals.equal(p_mo_line_detail_rec.transaction_cost, l_mmtt_rec.transaction_cost)
1044 AND inv_globals.equal(p_mo_line_detail_rec.transaction_type_id, l_mmtt_rec.transaction_type_id)
1045 AND inv_globals.equal(p_mo_line_detail_rec.transaction_action_id, l_mmtt_rec.transaction_action_id)
1046 AND inv_globals.equal(p_mo_line_detail_rec.transaction_source_type_id, l_mmtt_rec.transaction_source_type_id)
1047 AND inv_globals.equal(p_mo_line_detail_rec.transaction_source_id, l_mmtt_rec.transaction_source_id)
1048 AND inv_globals.equal(p_mo_line_detail_rec.transaction_source_name, l_mmtt_rec.transaction_source_name)
1049 AND inv_globals.equal(p_mo_line_detail_rec.transaction_date, l_mmtt_rec.transaction_date)
1050 AND inv_globals.equal(p_mo_line_detail_rec.acct_period_id, l_mmtt_rec.acct_period_id)
1051 AND inv_globals.equal(p_mo_line_detail_rec.distribution_account_id, l_mmtt_rec.distribution_account_id)
1052 AND inv_globals.equal(p_mo_line_detail_rec.transaction_reference, l_mmtt_rec.transaction_reference)
1053 AND inv_globals.equal(p_mo_line_detail_rec.requisition_line_id, l_mmtt_rec.requisition_line_id)
1054 AND inv_globals.equal(p_mo_line_detail_rec.requisition_distribution_id, l_mmtt_rec.requisition_distribution_id)
1055 AND inv_globals.equal(p_mo_line_detail_rec.reason_id, l_mmtt_rec.reason_id)
1056 AND inv_globals.equal(p_mo_line_detail_rec.lot_number, l_mmtt_rec.lot_number)
1057 AND inv_globals.equal(p_mo_line_detail_rec.lot_expiration_date, l_mmtt_rec.lot_expiration_date)
1058 AND inv_globals.equal(p_mo_line_detail_rec.serial_number, l_mmtt_rec.serial_number)
1059 AND inv_globals.equal(p_mo_line_detail_rec.receiving_document, l_mmtt_rec.receiving_document)
1060 AND inv_globals.equal(p_mo_line_detail_rec.demand_id, l_mmtt_rec.demand_id)
1061 AND inv_globals.equal(p_mo_line_detail_rec.rcv_transaction_id, l_mmtt_rec.rcv_transaction_id)
1062 AND inv_globals.equal(p_mo_line_detail_rec.move_transaction_id, l_mmtt_rec.move_transaction_id)
1063 AND inv_globals.equal(p_mo_line_detail_rec.completion_transaction_id, l_mmtt_rec.completion_transaction_id)
1064 AND inv_globals.equal(p_mo_line_detail_rec.wip_entity_type, l_mmtt_rec.wip_entity_type)
1065 AND inv_globals.equal(p_mo_line_detail_rec.schedule_id, l_mmtt_rec.schedule_id)
1066 AND inv_globals.equal(p_mo_line_detail_rec.repetitive_line_id, l_mmtt_rec.repetitive_line_id)
1067 AND inv_globals.equal(p_mo_line_detail_rec.employee_code, l_mmtt_rec.employee_code)
1068 AND inv_globals.equal(p_mo_line_detail_rec.primary_switch, l_mmtt_rec.primary_switch)
1069 AND inv_globals.equal(p_mo_line_detail_rec.schedule_update_code, l_mmtt_rec.schedule_update_code)
1070 AND inv_globals.equal(p_mo_line_detail_rec.setup_teardown_code, l_mmtt_rec.setup_teardown_code)
1071 AND inv_globals.equal(p_mo_line_detail_rec.item_ordering, l_mmtt_rec.item_ordering)
1072 AND inv_globals.equal(p_mo_line_detail_rec.negative_req_flag, l_mmtt_rec.negative_req_flag)
1073 AND inv_globals.equal(p_mo_line_detail_rec.operation_seq_num, l_mmtt_rec.operation_seq_num)
1074 AND inv_globals.equal(p_mo_line_detail_rec.picking_line_id, l_mmtt_rec.picking_line_id)
1075 AND inv_globals.equal(p_mo_line_detail_rec.trx_source_line_id, l_mmtt_rec.trx_source_line_id)
1076 AND inv_globals.equal(p_mo_line_detail_rec.trx_source_delivery_id, l_mmtt_rec.trx_source_delivery_id)
1077 AND inv_globals.equal(p_mo_line_detail_rec.physical_adjustment_id, l_mmtt_rec.physical_adjustment_id)
1078 AND inv_globals.equal(p_mo_line_detail_rec.cycle_count_id, l_mmtt_rec.cycle_count_id)
1079 AND inv_globals.equal(p_mo_line_detail_rec.rma_line_id, l_mmtt_rec.rma_line_id)
1080 AND inv_globals.equal(p_mo_line_detail_rec.customer_ship_id, l_mmtt_rec.customer_ship_id)
1081 AND inv_globals.equal(p_mo_line_detail_rec.currency_code, l_mmtt_rec.currency_code)
1082 AND inv_globals.equal(p_mo_line_detail_rec.currency_conversion_rate, l_mmtt_rec.currency_conversion_rate)
1083 AND inv_globals.equal(p_mo_line_detail_rec.currency_conversion_type, l_mmtt_rec.currency_conversion_type)
1084 AND inv_globals.equal(p_mo_line_detail_rec.currency_conversion_date, l_mmtt_rec.currency_conversion_date)
1085 AND inv_globals.equal(p_mo_line_detail_rec.ussgl_transaction_code, l_mmtt_rec.ussgl_transaction_code)
1086 AND inv_globals.equal(p_mo_line_detail_rec.vendor_lot_number, l_mmtt_rec.vendor_lot_number)
1087 AND inv_globals.equal(p_mo_line_detail_rec.encumbrance_account, l_mmtt_rec.encumbrance_account)
1088 AND inv_globals.equal(p_mo_line_detail_rec.ship_to_location, l_mmtt_rec.ship_to_location)
1089 AND inv_globals.equal(p_mo_line_detail_rec.shipment_number, l_mmtt_rec.shipment_number)
1090 AND inv_globals.equal(p_mo_line_detail_rec.transfer_cost, l_mmtt_rec.transfer_cost)
1091 AND inv_globals.equal(p_mo_line_detail_rec.transportation_cost, l_mmtt_rec.transportation_cost)
1092 AND inv_globals.equal(p_mo_line_detail_rec.transportation_account, l_mmtt_rec.transportation_account)
1093 AND inv_globals.equal(p_mo_line_detail_rec.freight_code, l_mmtt_rec.freight_code)
1094 AND inv_globals.equal(p_mo_line_detail_rec.containers, l_mmtt_rec.containers)
1095 AND inv_globals.equal(p_mo_line_detail_rec.waybill_airbill, l_mmtt_rec.waybill_airbill)
1096 AND inv_globals.equal(p_mo_line_detail_rec.expected_arrival_date, l_mmtt_rec.expected_arrival_date)
1097 AND inv_globals.equal(p_mo_line_detail_rec.transfer_subinventory, l_mmtt_rec.transfer_subinventory)
1098 AND inv_globals.equal(p_mo_line_detail_rec.transfer_organization, l_mmtt_rec.transfer_organization)
1099 AND inv_globals.equal(p_mo_line_detail_rec.transfer_to_location, l_mmtt_rec.transfer_to_location)
1100 AND inv_globals.equal(p_mo_line_detail_rec.new_average_cost, l_mmtt_rec.new_average_cost)
1101 AND inv_globals.equal(p_mo_line_detail_rec.value_change, l_mmtt_rec.value_change)
1102 AND inv_globals.equal(p_mo_line_detail_rec.percentage_change, l_mmtt_rec.percentage_change)
1103 AND inv_globals.equal(p_mo_line_detail_rec.material_allocation_temp_id, l_mmtt_rec.material_allocation_temp_id)
1104 AND inv_globals.equal(p_mo_line_detail_rec.demand_source_header_id, l_mmtt_rec.demand_source_header_id)
1105 AND inv_globals.equal(p_mo_line_detail_rec.demand_source_line, l_mmtt_rec.demand_source_line)
1106 AND inv_globals.equal(p_mo_line_detail_rec.demand_source_delivery, l_mmtt_rec.demand_source_delivery)
1107 AND inv_globals.equal(p_mo_line_detail_rec.item_segments, l_mmtt_rec.item_segments)
1108 AND inv_globals.equal(p_mo_line_detail_rec.item_description, l_mmtt_rec.item_description)
1109 AND inv_globals.equal(p_mo_line_detail_rec.item_trx_enabled_flag, l_mmtt_rec.item_trx_enabled_flag)
1110 AND inv_globals.equal(p_mo_line_detail_rec.item_location_control_code, l_mmtt_rec.item_location_control_code)
1111 AND inv_globals.equal(p_mo_line_detail_rec.item_restrict_subinv_code, l_mmtt_rec.item_restrict_subinv_code)
1112 AND inv_globals.equal(p_mo_line_detail_rec.item_restrict_locators_code, l_mmtt_rec.item_restrict_locators_code)
1113 AND inv_globals.equal(p_mo_line_detail_rec.item_revision_qty_control_code
1114 , l_mmtt_rec.item_revision_qty_control_code)
1115 AND inv_globals.equal(p_mo_line_detail_rec.item_primary_uom_code, l_mmtt_rec.item_primary_uom_code)
1116 AND inv_globals.equal(p_mo_line_detail_rec.item_uom_class, l_mmtt_rec.item_uom_class)
1117 AND inv_globals.equal(p_mo_line_detail_rec.item_shelf_life_code, l_mmtt_rec.item_shelf_life_code)
1118 AND inv_globals.equal(p_mo_line_detail_rec.item_shelf_life_days, l_mmtt_rec.item_shelf_life_days)
1119 AND inv_globals.equal(p_mo_line_detail_rec.item_lot_control_code, l_mmtt_rec.item_lot_control_code)
1120 AND inv_globals.equal(p_mo_line_detail_rec.item_serial_control_code, l_mmtt_rec.item_serial_control_code)
1121 AND inv_globals.equal(p_mo_line_detail_rec.item_inventory_asset_flag, l_mmtt_rec.item_inventory_asset_flag)
1122 AND inv_globals.equal(p_mo_line_detail_rec.allowed_units_lookup_code, l_mmtt_rec.allowed_units_lookup_code)
1123 AND inv_globals.equal(p_mo_line_detail_rec.department_id, l_mmtt_rec.department_id)
1124 AND inv_globals.equal(p_mo_line_detail_rec.department_code, l_mmtt_rec.department_code)
1125 AND inv_globals.equal(p_mo_line_detail_rec.wip_supply_type, l_mmtt_rec.wip_supply_type)
1126 AND inv_globals.equal(p_mo_line_detail_rec.supply_subinventory, l_mmtt_rec.supply_subinventory)
1127 AND inv_globals.equal(p_mo_line_detail_rec.supply_locator_id, l_mmtt_rec.supply_locator_id)
1128 AND inv_globals.equal(p_mo_line_detail_rec.valid_subinventory_flag, l_mmtt_rec.valid_subinventory_flag)
1129 AND inv_globals.equal(p_mo_line_detail_rec.locator_segments, l_mmtt_rec.locator_segments)
1130 AND inv_globals.equal(p_mo_line_detail_rec.current_locator_control_code, l_mmtt_rec.current_locator_control_code)
1131 AND inv_globals.equal(p_mo_line_detail_rec.number_of_lots_entered, l_mmtt_rec.number_of_lots_entered)
1132 AND inv_globals.equal(p_mo_line_detail_rec.wip_commit_flag, l_mmtt_rec.wip_commit_flag)
1133 AND inv_globals.equal(p_mo_line_detail_rec.next_lot_number, l_mmtt_rec.next_lot_number)
1134 AND inv_globals.equal(p_mo_line_detail_rec.lot_alpha_prefix, l_mmtt_rec.lot_alpha_prefix)
1135 AND inv_globals.equal(p_mo_line_detail_rec.next_serial_number, l_mmtt_rec.next_serial_number)
1136 AND inv_globals.equal(p_mo_line_detail_rec.serial_alpha_prefix, l_mmtt_rec.serial_alpha_prefix)
1137 AND inv_globals.equal(p_mo_line_detail_rec.shippable_flag, l_mmtt_rec.shippable_flag)
1138 AND inv_globals.equal(p_mo_line_detail_rec.posting_flag, l_mmtt_rec.posting_flag)
1139 AND inv_globals.equal(p_mo_line_detail_rec.required_flag, l_mmtt_rec.required_flag)
1140 AND inv_globals.equal(p_mo_line_detail_rec.process_flag, l_mmtt_rec.process_flag)
1141 AND inv_globals.equal(p_mo_line_detail_rec.ERROR_CODE, l_mmtt_rec.ERROR_CODE)
1142 AND inv_globals.equal(p_mo_line_detail_rec.error_explanation, l_mmtt_rec.error_explanation)
1143 AND inv_globals.equal(p_mo_line_detail_rec.attribute_category, l_mmtt_rec.attribute_category)
1144 AND inv_globals.equal(p_mo_line_detail_rec.attribute1, l_mmtt_rec.attribute1)
1145 AND inv_globals.equal(p_mo_line_detail_rec.attribute2, l_mmtt_rec.attribute2)
1146 AND inv_globals.equal(p_mo_line_detail_rec.attribute3, l_mmtt_rec.attribute3)
1147 AND inv_globals.equal(p_mo_line_detail_rec.attribute4, l_mmtt_rec.attribute4)
1148 AND inv_globals.equal(p_mo_line_detail_rec.attribute5, l_mmtt_rec.attribute5)
1149 AND inv_globals.equal(p_mo_line_detail_rec.attribute6, l_mmtt_rec.attribute6)
1150 AND inv_globals.equal(p_mo_line_detail_rec.attribute7, l_mmtt_rec.attribute7)
1151 AND inv_globals.equal(p_mo_line_detail_rec.attribute8, l_mmtt_rec.attribute8)
1152 AND inv_globals.equal(p_mo_line_detail_rec.attribute9, l_mmtt_rec.attribute9)
1153 AND inv_globals.equal(p_mo_line_detail_rec.attribute10, l_mmtt_rec.attribute10)
1154 AND inv_globals.equal(p_mo_line_detail_rec.attribute11, l_mmtt_rec.attribute11)
1155 AND inv_globals.equal(p_mo_line_detail_rec.attribute12, l_mmtt_rec.attribute12)
1156 AND inv_globals.equal(p_mo_line_detail_rec.attribute13, l_mmtt_rec.attribute13)
1157 AND inv_globals.equal(p_mo_line_detail_rec.attribute14, l_mmtt_rec.attribute14)
1158 AND inv_globals.equal(p_mo_line_detail_rec.attribute15, l_mmtt_rec.attribute15)
1159 AND inv_globals.equal(p_mo_line_detail_rec.movement_id, l_mmtt_rec.movement_id)
1160 AND inv_globals.equal(p_mo_line_detail_rec.reservation_quantity, l_mmtt_rec.reservation_quantity)
1161 AND inv_globals.equal(p_mo_line_detail_rec.shipped_quantity, l_mmtt_rec.shipped_quantity)
1162 AND inv_globals.equal(p_mo_line_detail_rec.transaction_line_number, l_mmtt_rec.transaction_line_number)
1163 AND inv_globals.equal(p_mo_line_detail_rec.task_id, l_mmtt_rec.task_id)
1164 AND inv_globals.equal(p_mo_line_detail_rec.to_task_id, l_mmtt_rec.to_task_id)
1165 AND inv_globals.equal(p_mo_line_detail_rec.source_task_id, l_mmtt_rec.source_task_id)
1166 AND inv_globals.equal(p_mo_line_detail_rec.project_id, l_mmtt_rec.project_id)
1167 AND inv_globals.equal(p_mo_line_detail_rec.source_project_id, l_mmtt_rec.source_project_id)
1168 AND inv_globals.equal(p_mo_line_detail_rec.pa_expenditure_org_id, l_mmtt_rec.pa_expenditure_org_id)
1169 AND inv_globals.equal(p_mo_line_detail_rec.to_project_id, l_mmtt_rec.to_project_id)
1170 AND inv_globals.equal(p_mo_line_detail_rec.expenditure_type, l_mmtt_rec.expenditure_type)
1171 AND inv_globals.equal(p_mo_line_detail_rec.final_completion_flag, l_mmtt_rec.final_completion_flag)
1172 AND inv_globals.equal(p_mo_line_detail_rec.transfer_percentage, l_mmtt_rec.transfer_percentage)
1173 AND inv_globals.equal(p_mo_line_detail_rec.transaction_sequence_id, l_mmtt_rec.transaction_sequence_id)
1174 AND inv_globals.equal(p_mo_line_detail_rec.material_account, l_mmtt_rec.material_account)
1175 AND inv_globals.equal(p_mo_line_detail_rec.material_overhead_account, l_mmtt_rec.material_overhead_account)
1176 AND inv_globals.equal(p_mo_line_detail_rec.resource_account, l_mmtt_rec.resource_account)
1177 AND inv_globals.equal(p_mo_line_detail_rec.outside_processing_account, l_mmtt_rec.outside_processing_account)
1178 AND inv_globals.equal(p_mo_line_detail_rec.overhead_account, l_mmtt_rec.overhead_account)
1179 AND inv_globals.equal(p_mo_line_detail_rec.flow_schedule, l_mmtt_rec.flow_schedule)
1180 AND inv_globals.equal(p_mo_line_detail_rec.cost_group_id, l_mmtt_rec.cost_group_id)
1181 AND inv_globals.equal(p_mo_line_detail_rec.demand_class, l_mmtt_rec.demand_class)
1182 AND inv_globals.equal(p_mo_line_detail_rec.qa_collection_id, l_mmtt_rec.qa_collection_id)
1183 AND inv_globals.equal(p_mo_line_detail_rec.kanban_card_id, l_mmtt_rec.kanban_card_id)
1184 AND inv_globals.equal(p_mo_line_detail_rec.overcompletion_transaction_id
1185 , l_mmtt_rec.overcompletion_transaction_id)
1186 AND inv_globals.equal(p_mo_line_detail_rec.overcompletion_primary_qty, l_mmtt_rec.overcompletion_primary_qty)
1187 AND inv_globals.equal(p_mo_line_detail_rec.overcompletion_transaction_qty
1188 , l_mmtt_rec.overcompletion_transaction_qty)
1189 AND inv_globals.equal(p_mo_line_detail_rec.end_item_unit_number, l_mmtt_rec.end_item_unit_number)
1190 AND inv_globals.equal(p_mo_line_detail_rec.scheduled_payback_date, l_mmtt_rec.scheduled_payback_date)
1191 AND inv_globals.equal(p_mo_line_detail_rec.line_type_code, l_mmtt_rec.line_type_code)
1192 AND inv_globals.equal(p_mo_line_detail_rec.parent_transaction_temp_id, l_mmtt_rec.parent_transaction_temp_id)
1193 AND inv_globals.equal(p_mo_line_detail_rec.put_away_strategy_id, l_mmtt_rec.put_away_strategy_id)
1194 AND inv_globals.equal(p_mo_line_detail_rec.put_away_rule_id, l_mmtt_rec.put_away_rule_id)
1195 AND inv_globals.equal(p_mo_line_detail_rec.pick_strategy_id, l_mmtt_rec.pick_strategy_id)
1196 AND inv_globals.equal(p_mo_line_detail_rec.pick_rule_id, l_mmtt_rec.pick_rule_id)
1197 AND inv_globals.equal(p_mo_line_detail_rec.common_bom_seq_id, l_mmtt_rec.common_bom_seq_id)
1198 AND inv_globals.equal(p_mo_line_detail_rec.common_routing_seq_id, l_mmtt_rec.common_routing_seq_id)
1199 AND inv_globals.equal(p_mo_line_detail_rec.cost_type_id, l_mmtt_rec.cost_type_id)
1200 AND inv_globals.equal(p_mo_line_detail_rec.org_cost_group_id, l_mmtt_rec.org_cost_group_id)
1201 AND inv_globals.equal(p_mo_line_detail_rec.move_order_line_id, l_mmtt_rec.move_order_line_id)
1202 AND inv_globals.equal(p_mo_line_detail_rec.task_group_id, l_mmtt_rec.task_group_id)
1203 AND inv_globals.equal(p_mo_line_detail_rec.pick_slip_number, l_mmtt_rec.pick_slip_number)
1204 AND inv_globals.equal(p_mo_line_detail_rec.reservation_id, l_mmtt_rec.reservation_id)
1205 AND inv_globals.equal(p_mo_line_detail_rec.transaction_status, l_mmtt_rec.transaction_status)
1206 AND inv_globals.equal(p_mo_line_detail_rec.transfer_cost_group_id, l_mmtt_rec.transfer_cost_group_id)
1207 AND inv_globals.equal(p_mo_line_detail_rec.lpn_id, l_mmtt_rec.lpn_id)
1208 AND inv_globals.equal(p_mo_line_detail_rec.transfer_lpn_id, l_mmtt_rec.transfer_lpn_id)
1209 AND inv_globals.equal(p_mo_line_detail_rec.pick_slip_date, l_mmtt_rec.pick_slip_date)
1210 AND inv_globals.equal(p_mo_line_detail_rec.content_lpn_id, l_mmtt_rec.content_lpn_id) THEN
1211 -- Row has not changed. Set out parameter.
1212
1213 x_mo_line_detail_rec := l_mmtt_rec;
1214 -- Set return status
1215
1216 x_return_status := fnd_api.g_ret_sts_success;
1217 ELSE
1218 -- Row has changed by another user.
1219
1220 x_return_status := fnd_api.g_ret_sts_error;
1221
1222 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1223 fnd_message.set_name('INV', 'OE_LOCK_ROW_CHANGED');
1224 fnd_msg_pub.ADD;
1225 END IF;
1226 END IF;
1227
1228 x_return_status := fnd_api.g_ret_sts_success;
1229 EXCEPTION
1230 WHEN NO_DATA_FOUND THEN
1231 x_return_status := fnd_api.g_ret_sts_error;
1232
1233 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1234 fnd_message.set_name('INV', 'OE_LOCK_ROW_DELETED');
1235 fnd_msg_pub.ADD;
1236 END IF;
1237 WHEN app_exceptions.record_lock_exception THEN
1238 x_return_status := fnd_api.g_ret_sts_error;
1239
1240 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1241 fnd_message.set_name('INV', 'OE_LOCK_ROW_ALREADY_LOCKED');
1242 fnd_msg_pub.ADD;
1243 END IF;
1244 WHEN OTHERS THEN
1245 x_return_status := fnd_api.g_ret_sts_unexp_error;
1246
1247 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1248 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Lock_Row');
1249 END IF;
1250 END lock_row;
1251
1252 -- Function Query_Row
1253 FUNCTION query_row(p_line_detail_id IN NUMBER) RETURN g_mmtt_rec IS
1254 BEGIN
1255 RETURN query_rows(p_line_detail_id => p_line_detail_id)(1);
1256 END query_row;
1257
1258 -- Function Query_Rows
1259 --
1260 FUNCTION query_rows(p_line_id IN NUMBER := fnd_api.g_miss_num, p_line_detail_id IN NUMBER := fnd_api.g_miss_num)
1261 RETURN g_mmtt_tbl_type IS
1262 l_mmtt_rec g_mmtt_rec;
1263 l_mmtt_tbl g_mmtt_tbl_type;
1264
1265 CURSOR l_mmtt_csr IS
1266 SELECT transaction_header_id
1267 , transaction_temp_id
1268 , source_code
1269 , source_line_id
1270 , transaction_mode
1271 , lock_flag
1272 , last_update_date
1273 , last_updated_by
1274 , creation_date
1275 , created_by
1276 , last_update_login
1277 , request_id
1278 , program_application_id
1279 , program_id
1280 , program_update_date
1281 , inventory_item_id
1282 , revision
1283 , organization_id
1284 , subinventory_code
1285 , locator_id
1286 , transaction_quantity
1287 , primary_quantity
1288 , transaction_uom
1289 , transaction_cost
1290 , transaction_type_id
1291 , transaction_action_id
1292 , transaction_source_type_id
1293 , transaction_source_id
1294 , transaction_source_name
1295 , transaction_date
1296 , acct_period_id
1297 , distribution_account_id
1298 , transaction_reference
1299 , requisition_line_id
1300 , requisition_distribution_id
1301 , reason_id
1302 , lot_number
1303 , lot_expiration_date
1304 , serial_number
1305 , receiving_document
1306 , demand_id
1307 , rcv_transaction_id
1308 , move_transaction_id
1309 , completion_transaction_id
1310 , wip_entity_type
1311 , schedule_id
1312 , repetitive_line_id
1313 , employee_code
1314 , primary_switch
1315 , schedule_update_code
1316 , setup_teardown_code
1317 , item_ordering
1318 , negative_req_flag
1319 , operation_seq_num
1320 , picking_line_id
1321 , trx_source_line_id
1322 , trx_source_delivery_id
1323 , physical_adjustment_id
1324 , cycle_count_id
1325 , rma_line_id
1326 , customer_ship_id
1327 , currency_code
1328 , currency_conversion_rate
1329 , currency_conversion_type
1330 , currency_conversion_date
1331 , ussgl_transaction_code
1332 , vendor_lot_number
1333 , encumbrance_account
1334 , encumbrance_amount
1335 , ship_to_location
1336 , shipment_number
1337 , transfer_cost
1338 , transportation_cost
1339 , transportation_account
1340 , freight_code
1341 , containers
1342 , waybill_airbill
1343 , expected_arrival_date
1344 , transfer_subinventory
1345 , transfer_organization
1346 , transfer_to_location
1347 , new_average_cost
1348 , value_change
1349 , percentage_change
1350 , material_allocation_temp_id
1351 , demand_source_header_id
1352 , demand_source_line
1353 , demand_source_delivery
1354 , item_segments
1355 , item_description
1356 , item_trx_enabled_flag
1357 , item_location_control_code
1358 , item_restrict_subinv_code
1359 , item_restrict_locators_code
1360 , item_revision_qty_control_code
1361 , item_primary_uom_code
1362 , item_uom_class
1363 , item_shelf_life_code
1364 , item_shelf_life_days
1365 , item_lot_control_code
1366 , item_serial_control_code
1367 , item_inventory_asset_flag
1368 , allowed_units_lookup_code
1369 , department_id
1370 , department_code
1371 , wip_supply_type
1372 , supply_subinventory
1373 , supply_locator_id
1374 , valid_subinventory_flag
1375 , valid_locator_flag
1376 , locator_segments
1377 , current_locator_control_code
1378 , number_of_lots_entered
1379 , wip_commit_flag
1380 , next_lot_number
1381 , lot_alpha_prefix
1382 , next_serial_number
1383 , serial_alpha_prefix
1384 , shippable_flag
1385 , posting_flag
1386 , required_flag
1387 , process_flag
1388 , ERROR_CODE
1389 , error_explanation
1390 , attribute_category
1391 , attribute1
1392 , attribute2
1393 , attribute3
1394 , attribute4
1395 , attribute5
1396 , attribute6
1397 , attribute7
1398 , attribute8
1399 , attribute9
1400 , attribute10
1401 , attribute11
1402 , attribute12
1403 , attribute13
1404 , attribute14
1405 , attribute15
1406 , movement_id
1407 , reservation_quantity
1408 , shipped_quantity
1409 , transaction_line_number
1410 , task_id
1411 , to_task_id
1412 , source_task_id
1413 , project_id
1414 , source_project_id
1415 , pa_expenditure_org_id
1416 , to_project_id
1417 , expenditure_type
1418 , final_completion_flag
1419 , transfer_percentage
1420 , transaction_sequence_id
1421 , material_account
1422 , material_overhead_account
1423 , resource_account
1424 , outside_processing_account
1425 , overhead_account
1426 , flow_schedule
1427 , cost_group_id
1428 , demand_class
1429 , qa_collection_id
1430 , kanban_card_id
1431 , overcompletion_transaction_id
1432 , overcompletion_primary_qty
1433 , overcompletion_transaction_qty
1434 , end_item_unit_number
1435 , scheduled_payback_date
1436 , line_type_code
1437 , parent_transaction_temp_id
1438 , put_away_strategy_id
1439 , put_away_rule_id
1440 , pick_strategy_id
1441 , pick_rule_id
1442 , common_bom_seq_id
1443 , common_routing_seq_id
1444 , cost_type_id
1445 , org_cost_group_id
1446 , move_order_line_id
1447 , task_group_id
1448 , pick_slip_number
1449 , reservation_id
1450 , transaction_status
1451 , transfer_cost_group_id
1452 , lpn_id
1453 , transfer_lpn_id
1454 , pick_slip_date
1455 , content_lpn_id
1456 , secondary_transaction_quantity -- INVCONV change
1457 , secondary_uom_code -- INVCONV change
1458 FROM mtl_material_transactions_temp
1459 WHERE move_order_line_id = p_line_id;
1460
1461 CURSOR l_mmtt_csr_temp IS
1462 SELECT transaction_header_id
1463 , transaction_temp_id
1464 , source_code
1465 , source_line_id
1466 , transaction_mode
1467 , lock_flag
1468 , last_update_date
1469 , last_updated_by
1470 , creation_date
1471 , created_by
1472 , last_update_login
1473 , request_id
1474 , program_application_id
1475 , program_id
1476 , program_update_date
1477 , inventory_item_id
1478 , revision
1479 , organization_id
1480 , subinventory_code
1481 , locator_id
1482 , transaction_quantity
1483 , primary_quantity
1484 , transaction_uom
1485 , transaction_cost
1486 , transaction_type_id
1487 , transaction_action_id
1488 , transaction_source_type_id
1489 , transaction_source_id
1490 , transaction_source_name
1491 , transaction_date
1492 , acct_period_id
1493 , distribution_account_id
1494 , transaction_reference
1495 , requisition_line_id
1496 , requisition_distribution_id
1497 , reason_id
1498 , lot_number
1499 , lot_expiration_date
1500 , serial_number
1501 , receiving_document
1502 , demand_id
1503 , rcv_transaction_id
1504 , move_transaction_id
1505 , completion_transaction_id
1506 , wip_entity_type
1507 , schedule_id
1508 , repetitive_line_id
1509 , employee_code
1510 , primary_switch
1511 , schedule_update_code
1512 , setup_teardown_code
1513 , item_ordering
1514 , negative_req_flag
1515 , operation_seq_num
1516 , picking_line_id
1517 , trx_source_line_id
1518 , trx_source_delivery_id
1519 , physical_adjustment_id
1520 , cycle_count_id
1521 , rma_line_id
1522 , customer_ship_id
1523 , currency_code
1524 , currency_conversion_rate
1525 , currency_conversion_type
1526 , currency_conversion_date
1527 , ussgl_transaction_code
1528 , vendor_lot_number
1529 , encumbrance_account
1530 , encumbrance_amount
1531 , ship_to_location
1532 , shipment_number
1533 , transfer_cost
1534 , transportation_cost
1535 , transportation_account
1536 , freight_code
1537 , containers
1538 , waybill_airbill
1539 , expected_arrival_date
1540 , transfer_subinventory
1541 , transfer_organization
1542 , transfer_to_location
1543 , new_average_cost
1544 , value_change
1545 , percentage_change
1546 , material_allocation_temp_id
1547 , demand_source_header_id
1548 , demand_source_line
1549 , demand_source_delivery
1550 , item_segments
1551 , item_description
1552 , item_trx_enabled_flag
1553 , item_location_control_code
1554 , item_restrict_subinv_code
1555 , item_restrict_locators_code
1556 , item_revision_qty_control_code
1557 , item_primary_uom_code
1558 , item_uom_class
1559 , item_shelf_life_code
1560 , item_shelf_life_days
1561 , item_lot_control_code
1562 , item_serial_control_code
1563 , item_inventory_asset_flag
1564 , allowed_units_lookup_code
1565 , department_id
1566 , department_code
1567 , wip_supply_type
1568 , supply_subinventory
1569 , supply_locator_id
1570 , valid_subinventory_flag
1571 , valid_locator_flag
1572 , locator_segments
1573 , current_locator_control_code
1574 , number_of_lots_entered
1575 , wip_commit_flag
1576 , next_lot_number
1577 , lot_alpha_prefix
1578 , next_serial_number
1579 , serial_alpha_prefix
1580 , shippable_flag
1581 , posting_flag
1582 , required_flag
1583 , process_flag
1584 , ERROR_CODE
1585 , error_explanation
1586 , attribute_category
1587 , attribute1
1588 , attribute2
1589 , attribute3
1590 , attribute4
1591 , attribute5
1592 , attribute6
1593 , attribute7
1594 , attribute8
1595 , attribute9
1596 , attribute10
1597 , attribute11
1598 , attribute12
1599 , attribute13
1600 , attribute14
1601 , attribute15
1602 , movement_id
1603 , reservation_quantity
1604 , shipped_quantity
1605 , transaction_line_number
1606 , task_id
1607 , to_task_id
1608 , source_task_id
1609 , project_id
1610 , source_project_id
1611 , pa_expenditure_org_id
1612 , to_project_id
1613 , expenditure_type
1614 , final_completion_flag
1615 , transfer_percentage
1616 , transaction_sequence_id
1617 , material_account
1618 , material_overhead_account
1619 , resource_account
1620 , outside_processing_account
1621 , overhead_account
1622 , flow_schedule
1623 , cost_group_id
1624 , demand_class
1625 , qa_collection_id
1626 , kanban_card_id
1627 , overcompletion_transaction_id
1628 , overcompletion_primary_qty
1629 , overcompletion_transaction_qty
1630 , end_item_unit_number
1631 , scheduled_payback_date
1632 , line_type_code
1633 , parent_transaction_temp_id
1634 , put_away_strategy_id
1635 , put_away_rule_id
1636 , pick_strategy_id
1637 , pick_rule_id
1638 , common_bom_seq_id
1639 , common_routing_seq_id
1640 , cost_type_id
1641 , org_cost_group_id
1642 , move_order_line_id
1643 , task_group_id
1644 , pick_slip_number
1645 , reservation_id
1646 , transaction_status
1647 , transfer_cost_group_id
1648 , lpn_id
1649 , transfer_lpn_id
1650 , pick_slip_date
1651 , content_lpn_id
1652 , secondary_transaction_quantity -- INVCONV change
1653 , secondary_uom_code -- INVCONV change
1654 FROM mtl_material_transactions_temp
1655 WHERE transaction_temp_id = p_line_detail_id;
1656
1657 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1658 BEGIN
1659 IF (p_line_id IS NOT NULL AND p_line_id <> fnd_api.g_miss_num)
1660 AND(p_line_detail_id IS NOT NULL AND p_line_detail_id <> fnd_api.g_miss_num) THEN
1661 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1662 fnd_msg_pub.add_exc_msg(
1663 g_pkg_name
1664 , 'Query Rows'
1665 , 'Keys are mutually exclusive: line_id = ' || p_line_id || ', line_detail_id = ' || p_line_detail_id
1666 );
1667 END IF;
1668
1669 RAISE fnd_api.g_exc_unexpected_error;
1670 END IF;
1671
1672 IF (p_line_id IS NOT NULL AND p_line_id <> fnd_api.g_miss_num) THEN
1673 FOR l_implicit_rec IN l_mmtt_csr LOOP
1674 l_mmtt_rec.transaction_header_id := l_implicit_rec.transaction_header_id;
1675 l_mmtt_rec.transaction_temp_id := l_implicit_rec.transaction_temp_id;
1676 l_mmtt_rec.source_code := l_implicit_rec.source_code;
1677 l_mmtt_rec.source_line_id := l_implicit_rec.source_line_id;
1678 l_mmtt_rec.transaction_mode := l_implicit_rec.transaction_mode;
1679 l_mmtt_rec.lock_flag := l_implicit_rec.lock_flag;
1680 l_mmtt_rec.last_update_date := l_implicit_rec.last_update_date;
1681 l_mmtt_rec.last_updated_by := l_implicit_rec.last_updated_by;
1682 l_mmtt_rec.creation_date := l_implicit_rec.creation_date;
1683 l_mmtt_rec.created_by := l_implicit_rec.created_by;
1684 l_mmtt_rec.last_update_login := l_implicit_rec.last_update_login;
1685 l_mmtt_rec.request_id := l_implicit_rec.request_id;
1686 l_mmtt_rec.program_application_id := l_implicit_rec.program_application_id;
1687 l_mmtt_rec.program_id := l_implicit_rec.program_id;
1688 l_mmtt_rec.program_update_date := l_implicit_rec.program_update_date;
1689 l_mmtt_rec.inventory_item_id := l_implicit_rec.inventory_item_id;
1690 l_mmtt_rec.revision := l_implicit_rec.revision;
1691 l_mmtt_rec.organization_id := l_implicit_rec.organization_id;
1692 l_mmtt_rec.subinventory_code := l_implicit_rec.subinventory_code;
1693 l_mmtt_rec.locator_id := l_implicit_rec.locator_id;
1694 l_mmtt_rec.transaction_quantity := l_implicit_rec.transaction_quantity;
1695 l_mmtt_rec.primary_quantity := l_implicit_rec.primary_quantity;
1696 l_mmtt_rec.transaction_uom := l_implicit_rec.transaction_uom;
1697 l_mmtt_rec.transaction_cost := l_implicit_rec.transaction_cost;
1698 l_mmtt_rec.transaction_type_id := l_implicit_rec.transaction_type_id;
1699 l_mmtt_rec.transaction_action_id := l_implicit_rec.transaction_action_id;
1700 l_mmtt_rec.transaction_source_type_id := l_implicit_rec.transaction_source_type_id;
1701 l_mmtt_rec.transaction_source_id := l_implicit_rec.transaction_source_id;
1702 l_mmtt_rec.transaction_source_name := l_implicit_rec.transaction_source_name;
1703 l_mmtt_rec.transaction_date := l_implicit_rec.transaction_date;
1704 l_mmtt_rec.acct_period_id := l_implicit_rec.acct_period_id;
1705 l_mmtt_rec.distribution_account_id := l_implicit_rec.distribution_account_id;
1706 l_mmtt_rec.transaction_reference := l_implicit_rec.transaction_reference;
1707 l_mmtt_rec.requisition_line_id := l_implicit_rec.requisition_line_id;
1708 l_mmtt_rec.requisition_distribution_id := l_implicit_rec.requisition_distribution_id;
1709 l_mmtt_rec.reason_id := l_implicit_rec.reason_id;
1710 l_mmtt_rec.lot_number := l_implicit_rec.lot_number;
1711 l_mmtt_rec.lot_expiration_date := l_implicit_rec.lot_expiration_date;
1712 l_mmtt_rec.serial_number := l_implicit_rec.serial_number;
1713 l_mmtt_rec.receiving_document := l_implicit_rec.receiving_document;
1714 l_mmtt_rec.demand_id := l_implicit_rec.demand_id;
1715 l_mmtt_rec.rcv_transaction_id := l_implicit_rec.rcv_transaction_id;
1716 l_mmtt_rec.move_transaction_id := l_implicit_rec.move_transaction_id;
1717 l_mmtt_rec.completion_transaction_id := l_implicit_rec.completion_transaction_id;
1718 l_mmtt_rec.wip_entity_type := l_implicit_rec.wip_entity_type;
1719 l_mmtt_rec.schedule_id := l_implicit_rec.schedule_id;
1720 l_mmtt_rec.repetitive_line_id := l_implicit_rec.repetitive_line_id;
1721 l_mmtt_rec.employee_code := l_implicit_rec.employee_code;
1722 l_mmtt_rec.primary_switch := l_implicit_rec.primary_switch;
1723 l_mmtt_rec.schedule_update_code := l_implicit_rec.schedule_update_code;
1724 l_mmtt_rec.setup_teardown_code := l_implicit_rec.setup_teardown_code;
1725 l_mmtt_rec.item_ordering := l_implicit_rec.item_ordering;
1726 l_mmtt_rec.negative_req_flag := l_implicit_rec.negative_req_flag;
1727 l_mmtt_rec.operation_seq_num := l_implicit_rec.operation_seq_num;
1728 l_mmtt_rec.picking_line_id := l_implicit_rec.picking_line_id;
1729 l_mmtt_rec.trx_source_line_id := l_implicit_rec.trx_source_line_id;
1730 l_mmtt_rec.trx_source_delivery_id := l_implicit_rec.trx_source_delivery_id;
1731 l_mmtt_rec.physical_adjustment_id := l_implicit_rec.physical_adjustment_id;
1732 l_mmtt_rec.cycle_count_id := l_implicit_rec.cycle_count_id;
1733 l_mmtt_rec.rma_line_id := l_implicit_rec.rma_line_id;
1734 l_mmtt_rec.customer_ship_id := l_implicit_rec.customer_ship_id;
1735 l_mmtt_rec.currency_code := l_implicit_rec.currency_code;
1736 l_mmtt_rec.currency_conversion_rate := l_implicit_rec.currency_conversion_rate;
1737 l_mmtt_rec.currency_conversion_type := l_implicit_rec.currency_conversion_type;
1738 l_mmtt_rec.currency_conversion_date := l_implicit_rec.currency_conversion_date;
1739 l_mmtt_rec.ussgl_transaction_code := l_implicit_rec.ussgl_transaction_code;
1740 l_mmtt_rec.vendor_lot_number := l_implicit_rec.vendor_lot_number;
1741 l_mmtt_rec.encumbrance_account := l_implicit_rec.encumbrance_account;
1742 l_mmtt_rec.encumbrance_amount := l_implicit_rec.encumbrance_amount;
1743 l_mmtt_rec.ship_to_location := l_implicit_rec.ship_to_location;
1744 l_mmtt_rec.shipment_number := l_implicit_rec.shipment_number;
1745 l_mmtt_rec.transfer_cost := l_implicit_rec.transfer_cost;
1746 l_mmtt_rec.transportation_cost := l_implicit_rec.transportation_cost;
1747 l_mmtt_rec.transportation_account := l_implicit_rec.transportation_account;
1748 l_mmtt_rec.freight_code := l_implicit_rec.freight_code;
1749 l_mmtt_rec.containers := l_implicit_rec.containers;
1750 l_mmtt_rec.waybill_airbill := l_implicit_rec.waybill_airbill;
1751 l_mmtt_rec.expected_arrival_date := l_implicit_rec.expected_arrival_date;
1752 l_mmtt_rec.transfer_subinventory := l_implicit_rec.transfer_subinventory;
1753 l_mmtt_rec.transfer_organization := l_implicit_rec.transfer_organization;
1754 l_mmtt_rec.transfer_to_location := l_implicit_rec.transfer_to_location;
1755 l_mmtt_rec.new_average_cost := l_implicit_rec.new_average_cost;
1756 l_mmtt_rec.value_change := l_implicit_rec.value_change;
1757 l_mmtt_rec.percentage_change := l_implicit_rec.percentage_change;
1758 l_mmtt_rec.material_allocation_temp_id := l_implicit_rec.material_allocation_temp_id;
1759 l_mmtt_rec.demand_source_header_id := l_implicit_rec.demand_source_header_id;
1760 l_mmtt_rec.demand_source_line := l_implicit_rec.demand_source_line;
1761 l_mmtt_rec.demand_source_delivery := l_implicit_rec.demand_source_delivery;
1762 l_mmtt_rec.item_segments := l_implicit_rec.item_segments;
1763 l_mmtt_rec.item_description := l_implicit_rec.item_description;
1764 l_mmtt_rec.item_trx_enabled_flag := l_implicit_rec.item_trx_enabled_flag;
1765 l_mmtt_rec.item_location_control_code := l_implicit_rec.item_location_control_code;
1766 l_mmtt_rec.item_restrict_subinv_code := l_implicit_rec.item_restrict_subinv_code;
1767 l_mmtt_rec.item_restrict_locators_code := l_implicit_rec.item_restrict_locators_code;
1768 l_mmtt_rec.item_revision_qty_control_code := l_implicit_rec.item_revision_qty_control_code;
1769 l_mmtt_rec.item_primary_uom_code := l_implicit_rec.item_primary_uom_code;
1770 l_mmtt_rec.item_uom_class := l_implicit_rec.item_uom_class;
1771 l_mmtt_rec.item_shelf_life_code := l_implicit_rec.item_shelf_life_code;
1772 l_mmtt_rec.item_shelf_life_days := l_implicit_rec.item_shelf_life_days;
1773 l_mmtt_rec.item_lot_control_code := l_implicit_rec.item_lot_control_code;
1774 l_mmtt_rec.item_serial_control_code := l_implicit_rec.item_serial_control_code;
1775 l_mmtt_rec.item_inventory_asset_flag := l_implicit_rec.item_inventory_asset_flag;
1776 l_mmtt_rec.allowed_units_lookup_code := l_implicit_rec.allowed_units_lookup_code;
1777 l_mmtt_rec.department_id := l_implicit_rec.department_id;
1778 l_mmtt_rec.department_code := l_implicit_rec.department_code;
1779 l_mmtt_rec.wip_supply_type := l_implicit_rec.wip_supply_type;
1780 l_mmtt_rec.supply_subinventory := l_implicit_rec.supply_subinventory;
1781 l_mmtt_rec.supply_locator_id := l_implicit_rec.supply_locator_id;
1782 l_mmtt_rec.valid_subinventory_flag := l_implicit_rec.valid_subinventory_flag;
1783 l_mmtt_rec.valid_locator_flag := l_implicit_rec.valid_locator_flag;
1784 l_mmtt_rec.locator_segments := l_implicit_rec.locator_segments;
1785 l_mmtt_rec.current_locator_control_code := l_implicit_rec.current_locator_control_code;
1786 l_mmtt_rec.number_of_lots_entered := l_implicit_rec.number_of_lots_entered;
1787 l_mmtt_rec.wip_commit_flag := l_implicit_rec.wip_commit_flag;
1788 l_mmtt_rec.next_lot_number := l_implicit_rec.next_lot_number;
1789 l_mmtt_rec.lot_alpha_prefix := l_implicit_rec.lot_alpha_prefix;
1790 l_mmtt_rec.next_serial_number := l_implicit_rec.next_serial_number;
1791 l_mmtt_rec.serial_alpha_prefix := l_implicit_rec.serial_alpha_prefix;
1792 l_mmtt_rec.shippable_flag := l_implicit_rec.shippable_flag;
1793 l_mmtt_rec.posting_flag := l_implicit_rec.posting_flag;
1794 l_mmtt_rec.required_flag := l_implicit_rec.required_flag;
1795 l_mmtt_rec.process_flag := l_implicit_rec.process_flag;
1796 l_mmtt_rec.ERROR_CODE := l_implicit_rec.ERROR_CODE;
1797 l_mmtt_rec.error_explanation := l_implicit_rec.error_explanation;
1798 l_mmtt_rec.attribute_category := l_implicit_rec.attribute_category;
1799 l_mmtt_rec.attribute1 := l_implicit_rec.attribute1;
1800 l_mmtt_rec.attribute2 := l_implicit_rec.attribute2;
1801 l_mmtt_rec.attribute3 := l_implicit_rec.attribute3;
1802 l_mmtt_rec.attribute4 := l_implicit_rec.attribute4;
1803 l_mmtt_rec.attribute5 := l_implicit_rec.attribute5;
1804 l_mmtt_rec.attribute6 := l_implicit_rec.attribute6;
1805 l_mmtt_rec.attribute7 := l_implicit_rec.attribute7;
1806 l_mmtt_rec.attribute8 := l_implicit_rec.attribute8;
1807 l_mmtt_rec.attribute9 := l_implicit_rec.attribute9;
1808 l_mmtt_rec.attribute10 := l_implicit_rec.attribute10;
1809 l_mmtt_rec.attribute11 := l_implicit_rec.attribute11;
1810 l_mmtt_rec.attribute12 := l_implicit_rec.attribute12;
1811 l_mmtt_rec.attribute13 := l_implicit_rec.attribute13;
1812 l_mmtt_rec.attribute14 := l_implicit_rec.attribute14;
1813 l_mmtt_rec.attribute15 := l_implicit_rec.attribute15;
1814 l_mmtt_rec.movement_id := l_implicit_rec.movement_id;
1815 l_mmtt_rec.reservation_quantity := l_implicit_rec.reservation_quantity;
1816 l_mmtt_rec.shipped_quantity := l_implicit_rec.shipped_quantity;
1817 l_mmtt_rec.transaction_line_number := l_implicit_rec.transaction_line_number;
1818 l_mmtt_rec.task_id := l_implicit_rec.task_id;
1819 l_mmtt_rec.to_task_id := l_implicit_rec.to_task_id;
1820 l_mmtt_rec.source_task_id := l_implicit_rec.source_task_id;
1821 l_mmtt_rec.project_id := l_implicit_rec.project_id;
1822 l_mmtt_rec.source_project_id := l_implicit_rec.source_project_id;
1823 l_mmtt_rec.pa_expenditure_org_id := l_implicit_rec.pa_expenditure_org_id;
1824 l_mmtt_rec.to_project_id := l_implicit_rec.to_project_id;
1825 l_mmtt_rec.expenditure_type := l_implicit_rec.expenditure_type;
1826 l_mmtt_rec.final_completion_flag := l_implicit_rec.final_completion_flag;
1827 l_mmtt_rec.transfer_percentage := l_implicit_rec.transfer_percentage;
1828 l_mmtt_rec.transaction_sequence_id := l_implicit_rec.transaction_sequence_id;
1829 l_mmtt_rec.material_account := l_implicit_rec.material_account;
1830 l_mmtt_rec.material_overhead_account := l_implicit_rec.material_overhead_account;
1831 l_mmtt_rec.resource_account := l_implicit_rec.resource_account;
1832 l_mmtt_rec.outside_processing_account := l_implicit_rec.outside_processing_account;
1833 l_mmtt_rec.overhead_account := l_implicit_rec.overhead_account;
1834 l_mmtt_rec.flow_schedule := l_implicit_rec.flow_schedule;
1835 l_mmtt_rec.cost_group_id := l_implicit_rec.cost_group_id;
1836 l_mmtt_rec.demand_class := l_implicit_rec.demand_class;
1837 l_mmtt_rec.qa_collection_id := l_implicit_rec.qa_collection_id;
1838 l_mmtt_rec.kanban_card_id := l_implicit_rec.kanban_card_id;
1839 l_mmtt_rec.overcompletion_transaction_id := l_implicit_rec.overcompletion_transaction_id;
1840 l_mmtt_rec.overcompletion_primary_qty := l_implicit_rec.overcompletion_primary_qty;
1841 l_mmtt_rec.overcompletion_transaction_qty := l_implicit_rec.overcompletion_transaction_qty;
1842 l_mmtt_rec.end_item_unit_number := l_implicit_rec.end_item_unit_number;
1843 l_mmtt_rec.scheduled_payback_date := l_implicit_rec.scheduled_payback_date;
1844 l_mmtt_rec.line_type_code := l_implicit_rec.line_type_code;
1845 l_mmtt_rec.parent_transaction_temp_id := l_implicit_rec.parent_transaction_temp_id;
1846 l_mmtt_rec.put_away_strategy_id := l_implicit_rec.put_away_strategy_id;
1847 l_mmtt_rec.put_away_rule_id := l_implicit_rec.put_away_rule_id;
1848 l_mmtt_rec.pick_strategy_id := l_implicit_rec.pick_strategy_id;
1849 l_mmtt_rec.pick_rule_id := l_implicit_rec.pick_rule_id;
1850 l_mmtt_rec.common_bom_seq_id := l_implicit_rec.common_bom_seq_id;
1851 l_mmtt_rec.common_routing_seq_id := l_implicit_rec.common_routing_seq_id;
1852 l_mmtt_rec.cost_type_id := l_implicit_rec.cost_type_id;
1853 l_mmtt_rec.org_cost_group_id := l_implicit_rec.org_cost_group_id;
1854 l_mmtt_rec.move_order_line_id := l_implicit_rec.move_order_line_id;
1855 l_mmtt_rec.task_group_id := l_implicit_rec.task_group_id;
1856 l_mmtt_rec.pick_slip_number := l_implicit_rec.pick_slip_number;
1857 l_mmtt_rec.reservation_id := l_implicit_rec.reservation_id;
1858 l_mmtt_rec.transaction_status := l_implicit_rec.transaction_status;
1859 l_mmtt_rec.transfer_cost_group_id := l_implicit_rec.transfer_cost_group_id;
1860 l_mmtt_rec.lpn_id := l_implicit_rec.lpn_id;
1861 l_mmtt_rec.transfer_lpn_id := l_implicit_rec.transfer_lpn_id;
1862 l_mmtt_rec.pick_slip_date := l_implicit_rec.pick_slip_date;
1863 l_mmtt_rec.content_lpn_id := l_implicit_rec.content_lpn_id;
1864 l_mmtt_rec.secondary_transaction_quantity := l_implicit_rec.secondary_transaction_quantity; -- INVCONV change
1865 l_mmtt_rec.secondary_uom_code := l_implicit_rec.secondary_uom_code; -- INVCONV change
1866 l_mmtt_tbl(l_mmtt_tbl.COUNT + 1) := l_mmtt_rec;
1867 END LOOP;
1868 ELSE
1869 FOR l_implicit_rec IN l_mmtt_csr_temp LOOP
1870 l_mmtt_rec.transaction_header_id := l_implicit_rec.transaction_header_id;
1871 l_mmtt_rec.transaction_temp_id := l_implicit_rec.transaction_temp_id;
1872 l_mmtt_rec.source_code := l_implicit_rec.source_code;
1873 l_mmtt_rec.source_line_id := l_implicit_rec.source_line_id;
1874 l_mmtt_rec.transaction_mode := l_implicit_rec.transaction_mode;
1875 l_mmtt_rec.lock_flag := l_implicit_rec.lock_flag;
1876 l_mmtt_rec.last_update_date := l_implicit_rec.last_update_date;
1877 l_mmtt_rec.last_updated_by := l_implicit_rec.last_updated_by;
1878 l_mmtt_rec.creation_date := l_implicit_rec.creation_date;
1879 l_mmtt_rec.created_by := l_implicit_rec.created_by;
1880 l_mmtt_rec.last_update_login := l_implicit_rec.last_update_login;
1881 l_mmtt_rec.request_id := l_implicit_rec.request_id;
1882 l_mmtt_rec.program_application_id := l_implicit_rec.program_application_id;
1883 l_mmtt_rec.program_id := l_implicit_rec.program_id;
1884 l_mmtt_rec.program_update_date := l_implicit_rec.program_update_date;
1885 l_mmtt_rec.inventory_item_id := l_implicit_rec.inventory_item_id;
1886 l_mmtt_rec.revision := l_implicit_rec.revision;
1887 l_mmtt_rec.organization_id := l_implicit_rec.organization_id;
1888 l_mmtt_rec.subinventory_code := l_implicit_rec.subinventory_code;
1889 l_mmtt_rec.locator_id := l_implicit_rec.locator_id;
1890 l_mmtt_rec.transaction_quantity := l_implicit_rec.transaction_quantity;
1891 l_mmtt_rec.primary_quantity := l_implicit_rec.primary_quantity;
1892 l_mmtt_rec.transaction_uom := l_implicit_rec.transaction_uom;
1893 l_mmtt_rec.transaction_cost := l_implicit_rec.transaction_cost;
1894 l_mmtt_rec.transaction_type_id := l_implicit_rec.transaction_type_id;
1895 l_mmtt_rec.transaction_action_id := l_implicit_rec.transaction_action_id;
1896 l_mmtt_rec.transaction_source_type_id := l_implicit_rec.transaction_source_type_id;
1897 l_mmtt_rec.transaction_source_id := l_implicit_rec.transaction_source_id;
1898 l_mmtt_rec.transaction_source_name := l_implicit_rec.transaction_source_name;
1899 l_mmtt_rec.transaction_date := l_implicit_rec.transaction_date;
1900 l_mmtt_rec.acct_period_id := l_implicit_rec.acct_period_id;
1901 l_mmtt_rec.distribution_account_id := l_implicit_rec.distribution_account_id;
1902 l_mmtt_rec.transaction_reference := l_implicit_rec.transaction_reference;
1903 l_mmtt_rec.requisition_line_id := l_implicit_rec.requisition_line_id;
1904 l_mmtt_rec.requisition_distribution_id := l_implicit_rec.requisition_distribution_id;
1905 l_mmtt_rec.reason_id := l_implicit_rec.reason_id;
1906 l_mmtt_rec.lot_number := l_implicit_rec.lot_number;
1907 l_mmtt_rec.lot_expiration_date := l_implicit_rec.lot_expiration_date;
1908 l_mmtt_rec.serial_number := l_implicit_rec.serial_number;
1909 l_mmtt_rec.receiving_document := l_implicit_rec.receiving_document;
1910 l_mmtt_rec.demand_id := l_implicit_rec.demand_id;
1911 l_mmtt_rec.rcv_transaction_id := l_implicit_rec.rcv_transaction_id;
1912 l_mmtt_rec.move_transaction_id := l_implicit_rec.move_transaction_id;
1913 l_mmtt_rec.completion_transaction_id := l_implicit_rec.completion_transaction_id;
1914 l_mmtt_rec.wip_entity_type := l_implicit_rec.wip_entity_type;
1915 l_mmtt_rec.schedule_id := l_implicit_rec.schedule_id;
1916 l_mmtt_rec.repetitive_line_id := l_implicit_rec.repetitive_line_id;
1917 l_mmtt_rec.employee_code := l_implicit_rec.employee_code;
1918 l_mmtt_rec.primary_switch := l_implicit_rec.primary_switch;
1919 l_mmtt_rec.schedule_update_code := l_implicit_rec.schedule_update_code;
1920 l_mmtt_rec.setup_teardown_code := l_implicit_rec.setup_teardown_code;
1921 l_mmtt_rec.item_ordering := l_implicit_rec.item_ordering;
1922 l_mmtt_rec.negative_req_flag := l_implicit_rec.negative_req_flag;
1923 l_mmtt_rec.operation_seq_num := l_implicit_rec.operation_seq_num;
1924 l_mmtt_rec.picking_line_id := l_implicit_rec.picking_line_id;
1925 l_mmtt_rec.trx_source_line_id := l_implicit_rec.trx_source_line_id;
1926 l_mmtt_rec.trx_source_delivery_id := l_implicit_rec.trx_source_delivery_id;
1927 l_mmtt_rec.physical_adjustment_id := l_implicit_rec.physical_adjustment_id;
1928 l_mmtt_rec.cycle_count_id := l_implicit_rec.cycle_count_id;
1929 l_mmtt_rec.rma_line_id := l_implicit_rec.rma_line_id;
1930 l_mmtt_rec.customer_ship_id := l_implicit_rec.customer_ship_id;
1931 l_mmtt_rec.currency_code := l_implicit_rec.currency_code;
1932 l_mmtt_rec.currency_conversion_rate := l_implicit_rec.currency_conversion_rate;
1933 l_mmtt_rec.currency_conversion_type := l_implicit_rec.currency_conversion_type;
1934 l_mmtt_rec.currency_conversion_date := l_implicit_rec.currency_conversion_date;
1935 l_mmtt_rec.ussgl_transaction_code := l_implicit_rec.ussgl_transaction_code;
1936 l_mmtt_rec.vendor_lot_number := l_implicit_rec.vendor_lot_number;
1937 l_mmtt_rec.encumbrance_account := l_implicit_rec.encumbrance_account;
1938 l_mmtt_rec.encumbrance_amount := l_implicit_rec.encumbrance_amount;
1939 l_mmtt_rec.ship_to_location := l_implicit_rec.ship_to_location;
1940 l_mmtt_rec.shipment_number := l_implicit_rec.shipment_number;
1941 l_mmtt_rec.transfer_cost := l_implicit_rec.transfer_cost;
1942 l_mmtt_rec.transportation_cost := l_implicit_rec.transportation_cost;
1943 l_mmtt_rec.transportation_account := l_implicit_rec.transportation_account;
1944 l_mmtt_rec.freight_code := l_implicit_rec.freight_code;
1945 l_mmtt_rec.containers := l_implicit_rec.containers;
1946 l_mmtt_rec.waybill_airbill := l_implicit_rec.waybill_airbill;
1947 l_mmtt_rec.expected_arrival_date := l_implicit_rec.expected_arrival_date;
1948 l_mmtt_rec.transfer_subinventory := l_implicit_rec.transfer_subinventory;
1949 l_mmtt_rec.transfer_organization := l_implicit_rec.transfer_organization;
1950 l_mmtt_rec.transfer_to_location := l_implicit_rec.transfer_to_location;
1951 l_mmtt_rec.new_average_cost := l_implicit_rec.new_average_cost;
1952 l_mmtt_rec.value_change := l_implicit_rec.value_change;
1953 l_mmtt_rec.percentage_change := l_implicit_rec.percentage_change;
1954 l_mmtt_rec.material_allocation_temp_id := l_implicit_rec.material_allocation_temp_id;
1955 l_mmtt_rec.demand_source_header_id := l_implicit_rec.demand_source_header_id;
1956 l_mmtt_rec.demand_source_line := l_implicit_rec.demand_source_line;
1957 l_mmtt_rec.demand_source_delivery := l_implicit_rec.demand_source_delivery;
1958 l_mmtt_rec.item_segments := l_implicit_rec.item_segments;
1959 l_mmtt_rec.item_description := l_implicit_rec.item_description;
1960 l_mmtt_rec.item_trx_enabled_flag := l_implicit_rec.item_trx_enabled_flag;
1961 l_mmtt_rec.item_location_control_code := l_implicit_rec.item_location_control_code;
1962 l_mmtt_rec.item_restrict_subinv_code := l_implicit_rec.item_restrict_subinv_code;
1963 l_mmtt_rec.item_restrict_locators_code := l_implicit_rec.item_restrict_locators_code;
1964 l_mmtt_rec.item_revision_qty_control_code := l_implicit_rec.item_revision_qty_control_code;
1965 l_mmtt_rec.item_primary_uom_code := l_implicit_rec.item_primary_uom_code;
1966 l_mmtt_rec.item_uom_class := l_implicit_rec.item_uom_class;
1967 l_mmtt_rec.item_shelf_life_code := l_implicit_rec.item_shelf_life_code;
1968 l_mmtt_rec.item_shelf_life_days := l_implicit_rec.item_shelf_life_days;
1969 l_mmtt_rec.item_lot_control_code := l_implicit_rec.item_lot_control_code;
1970 l_mmtt_rec.item_serial_control_code := l_implicit_rec.item_serial_control_code;
1971 l_mmtt_rec.item_inventory_asset_flag := l_implicit_rec.item_inventory_asset_flag;
1972 l_mmtt_rec.allowed_units_lookup_code := l_implicit_rec.allowed_units_lookup_code;
1973 l_mmtt_rec.department_id := l_implicit_rec.department_id;
1974 l_mmtt_rec.department_code := l_implicit_rec.department_code;
1975 l_mmtt_rec.wip_supply_type := l_implicit_rec.wip_supply_type;
1976 l_mmtt_rec.supply_subinventory := l_implicit_rec.supply_subinventory;
1977 l_mmtt_rec.supply_locator_id := l_implicit_rec.supply_locator_id;
1978 l_mmtt_rec.valid_subinventory_flag := l_implicit_rec.valid_subinventory_flag;
1979 l_mmtt_rec.valid_locator_flag := l_implicit_rec.valid_locator_flag;
1980 l_mmtt_rec.locator_segments := l_implicit_rec.locator_segments;
1981 l_mmtt_rec.current_locator_control_code := l_implicit_rec.current_locator_control_code;
1982 l_mmtt_rec.number_of_lots_entered := l_implicit_rec.number_of_lots_entered;
1983 l_mmtt_rec.wip_commit_flag := l_implicit_rec.wip_commit_flag;
1984 l_mmtt_rec.next_lot_number := l_implicit_rec.next_lot_number;
1985 l_mmtt_rec.lot_alpha_prefix := l_implicit_rec.lot_alpha_prefix;
1986 l_mmtt_rec.next_serial_number := l_implicit_rec.next_serial_number;
1987 l_mmtt_rec.serial_alpha_prefix := l_implicit_rec.serial_alpha_prefix;
1988 l_mmtt_rec.shippable_flag := l_implicit_rec.shippable_flag;
1989 l_mmtt_rec.posting_flag := l_implicit_rec.posting_flag;
1990 l_mmtt_rec.required_flag := l_implicit_rec.required_flag;
1991 l_mmtt_rec.process_flag := l_implicit_rec.process_flag;
1992 l_mmtt_rec.ERROR_CODE := l_implicit_rec.ERROR_CODE;
1993 l_mmtt_rec.error_explanation := l_implicit_rec.error_explanation;
1994 l_mmtt_rec.attribute_category := l_implicit_rec.attribute_category;
1995 l_mmtt_rec.attribute1 := l_implicit_rec.attribute1;
1996 l_mmtt_rec.attribute2 := l_implicit_rec.attribute2;
1997 l_mmtt_rec.attribute3 := l_implicit_rec.attribute3;
1998 l_mmtt_rec.attribute4 := l_implicit_rec.attribute4;
1999 l_mmtt_rec.attribute5 := l_implicit_rec.attribute5;
2000 l_mmtt_rec.attribute6 := l_implicit_rec.attribute6;
2001 l_mmtt_rec.attribute7 := l_implicit_rec.attribute7;
2002 l_mmtt_rec.attribute8 := l_implicit_rec.attribute8;
2003 l_mmtt_rec.attribute9 := l_implicit_rec.attribute9;
2004 l_mmtt_rec.attribute10 := l_implicit_rec.attribute10;
2005 l_mmtt_rec.attribute11 := l_implicit_rec.attribute11;
2006 l_mmtt_rec.attribute12 := l_implicit_rec.attribute12;
2007 l_mmtt_rec.attribute13 := l_implicit_rec.attribute13;
2008 l_mmtt_rec.attribute14 := l_implicit_rec.attribute14;
2009 l_mmtt_rec.attribute15 := l_implicit_rec.attribute15;
2010 l_mmtt_rec.movement_id := l_implicit_rec.movement_id;
2011 l_mmtt_rec.reservation_quantity := l_implicit_rec.reservation_quantity;
2012 l_mmtt_rec.shipped_quantity := l_implicit_rec.shipped_quantity;
2013 l_mmtt_rec.transaction_line_number := l_implicit_rec.transaction_line_number;
2014 l_mmtt_rec.task_id := l_implicit_rec.task_id;
2015 l_mmtt_rec.to_task_id := l_implicit_rec.to_task_id;
2016 l_mmtt_rec.source_task_id := l_implicit_rec.source_task_id;
2017 l_mmtt_rec.project_id := l_implicit_rec.project_id;
2018 l_mmtt_rec.source_project_id := l_implicit_rec.source_project_id;
2019 l_mmtt_rec.pa_expenditure_org_id := l_implicit_rec.pa_expenditure_org_id;
2020 l_mmtt_rec.to_project_id := l_implicit_rec.to_project_id;
2021 l_mmtt_rec.expenditure_type := l_implicit_rec.expenditure_type;
2022 l_mmtt_rec.final_completion_flag := l_implicit_rec.final_completion_flag;
2023 l_mmtt_rec.transfer_percentage := l_implicit_rec.transfer_percentage;
2024 l_mmtt_rec.transaction_sequence_id := l_implicit_rec.transaction_sequence_id;
2025 l_mmtt_rec.material_account := l_implicit_rec.material_account;
2026 l_mmtt_rec.material_overhead_account := l_implicit_rec.material_overhead_account;
2027 l_mmtt_rec.resource_account := l_implicit_rec.resource_account;
2028 l_mmtt_rec.outside_processing_account := l_implicit_rec.outside_processing_account;
2029 l_mmtt_rec.overhead_account := l_implicit_rec.overhead_account;
2030 l_mmtt_rec.flow_schedule := l_implicit_rec.flow_schedule;
2031 l_mmtt_rec.cost_group_id := l_implicit_rec.cost_group_id;
2032 l_mmtt_rec.demand_class := l_implicit_rec.demand_class;
2033 l_mmtt_rec.qa_collection_id := l_implicit_rec.qa_collection_id;
2034 l_mmtt_rec.kanban_card_id := l_implicit_rec.kanban_card_id;
2035 l_mmtt_rec.overcompletion_transaction_id := l_implicit_rec.overcompletion_transaction_id;
2036 l_mmtt_rec.overcompletion_primary_qty := l_implicit_rec.overcompletion_primary_qty;
2037 l_mmtt_rec.overcompletion_transaction_qty := l_implicit_rec.overcompletion_transaction_qty;
2038 l_mmtt_rec.end_item_unit_number := l_implicit_rec.end_item_unit_number;
2039 l_mmtt_rec.scheduled_payback_date := l_implicit_rec.scheduled_payback_date;
2040 l_mmtt_rec.line_type_code := l_implicit_rec.line_type_code;
2041 l_mmtt_rec.parent_transaction_temp_id := l_implicit_rec.parent_transaction_temp_id;
2042 l_mmtt_rec.put_away_strategy_id := l_implicit_rec.put_away_strategy_id;
2043 l_mmtt_rec.put_away_rule_id := l_implicit_rec.put_away_rule_id;
2044 l_mmtt_rec.pick_strategy_id := l_implicit_rec.pick_strategy_id;
2045 l_mmtt_rec.pick_rule_id := l_implicit_rec.pick_rule_id;
2046 l_mmtt_rec.common_bom_seq_id := l_implicit_rec.common_bom_seq_id;
2047 l_mmtt_rec.common_routing_seq_id := l_implicit_rec.common_routing_seq_id;
2048 l_mmtt_rec.cost_type_id := l_implicit_rec.cost_type_id;
2049 l_mmtt_rec.org_cost_group_id := l_implicit_rec.org_cost_group_id;
2050 l_mmtt_rec.move_order_line_id := l_implicit_rec.move_order_line_id;
2051 l_mmtt_rec.task_group_id := l_implicit_rec.task_group_id;
2052 l_mmtt_rec.pick_slip_number := l_implicit_rec.pick_slip_number;
2053 l_mmtt_rec.reservation_id := l_implicit_rec.reservation_id;
2054 l_mmtt_rec.transaction_status := l_implicit_rec.transaction_status;
2055 l_mmtt_rec.transfer_cost_group_id := l_implicit_rec.transfer_cost_group_id;
2056 l_mmtt_rec.lpn_id := l_implicit_rec.lpn_id;
2057 l_mmtt_rec.transfer_lpn_id := l_implicit_rec.transfer_lpn_id;
2058 l_mmtt_rec.pick_slip_date := l_implicit_rec.pick_slip_date;
2059 l_mmtt_rec.content_lpn_id := l_implicit_rec.content_lpn_id;
2060 l_mmtt_rec.secondary_transaction_quantity := l_implicit_rec.secondary_transaction_quantity; -- INVCONV change
2061 l_mmtt_rec.secondary_uom_code := l_implicit_rec.secondary_uom_code; -- INVCONV change
2062 l_mmtt_tbl(l_mmtt_tbl.COUNT + 1) := l_mmtt_rec;
2063 END LOOP;
2064 END IF;
2065
2066 IF (p_line_detail_id IS NOT NULL AND p_line_detail_id <> fnd_api.g_miss_num)
2067 AND(l_mmtt_tbl.COUNT = 0) THEN
2068 RAISE NO_DATA_FOUND;
2069 END IF;
2070
2071 -- Return fetched table
2072
2073 RETURN l_mmtt_tbl;
2074 EXCEPTION
2075 WHEN fnd_api.g_exc_unexpected_error THEN
2076 RAISE fnd_api.g_exc_unexpected_error;
2077 WHEN OTHERS THEN
2078 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2079 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Query_Rows');
2080 END IF;
2081
2082 RAISE fnd_api.g_exc_unexpected_error;
2083 END query_rows;
2084
2085 -- This API currently works only for no control and lot controlled items
2086 PROCEDURE update_quantity_allocations(
2087 p_move_order_line_id IN NUMBER
2088 , p_mold_table IN inv_mo_line_detail_util.g_update_qty_tbl_type
2089 , x_mold_table OUT NOCOPY inv_mo_line_detail_util.g_mmtt_tbl_type
2090 , x_return_status OUT NOCOPY VARCHAR2
2091 , x_msg_count OUT NOCOPY NUMBER
2092 , x_msg_data OUT NOCOPY VARCHAR2
2093 ) IS
2094 i NUMBER := NULL;
2095 l_transaction_temp_id NUMBER;
2096 l_primary_quantity NUMBER;
2097 l_mmtt_primary_quantity NUMBER;
2098 l_mmtt_transaction_quantity NUMBER;
2099 l_mtlt_primary_quantity NUMBER;
2100 l_mtlt_transaction_quantity NUMBER;
2101 l_mtlt_sec_transaction_qty NUMBER; --INVCONV
2102 l_mmtt_sec_transaction_qty NUMBER; --INVCONV
2103 l_lot_control_code NUMBER;
2104 l_serial_control_code NUMBER;
2105 l_primary_uom_code mtl_system_items.primary_uom_code%TYPE;
2106 l_secondary_uom_code mtl_system_items.secondary_uom_code%TYPE; --INVCONV
2107 l_mmtt_rowid ROWID;
2108 l_mtlt_rowid ROWID;
2109
2110 TYPE mmtt_rec IS RECORD(
2111 row_id ROWID
2112 , transaction_quantity NUMBER
2113 , picked_transaction_quantity NUMBER
2114 , sec_transaction_qty NUMBER --INVCONV
2115 , picked_sec_transaction_qty NUMBER --INVCONV
2116 , picked_primary_quantity NUMBER
2117 );
2118
2119 -- Will be indexed by the transaction_temp_id
2120 TYPE mmtt_table_type IS TABLE OF mmtt_rec
2121 INDEX BY BINARY_INTEGER;
2122
2123 l_mmtt_table mmtt_table_type;
2124
2125 TYPE mtlt_table_type IS TABLE OF ROWID
2126 INDEX BY BINARY_INTEGER;
2127
2128 l_mtlt_table mtlt_table_type;
2129 l_mtlt_table_counter NUMBER := 1;
2130 tab_index INTEGER;
2131 l_sql_p INTEGER := NULL;
2132 l_rows_processed INTEGER := NULL;
2133 l_del_mtlt LONG
2134 := 'DELETE mtl_transaction_lots_temp '
2135 || 'WHERE transaction_temp_id in '
2136 || '(select transaction_temp_id '
2137 || ' from mtl_material_transactions_temp '
2138 || ' where move_order_line_id = :b_move_order_line_id) ';
2139 l_del_mmtt LONG
2140 := 'DELETE mtl_material_transactions_temp ' || ' where move_order_line_id = :b_move_order_line_id ';
2141 l_current_table VARCHAR2(30);
2142 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2143 BEGIN
2144 SAVEPOINT update_qty;
2145 x_return_status := fnd_api.g_ret_sts_success;
2146
2147 IF p_move_order_line_id IS NULL THEN
2148 fnd_message.set_name('INV', 'INV_MISSING_REQUIRED_PARAMETER');
2149 fnd_msg_pub.ADD;
2150 RAISE fnd_api.g_exc_unexpected_error;
2151 END IF;
2152
2153 i := p_mold_table.FIRST;
2154
2155 IF i IS NULL THEN
2156 fnd_message.set_name('INV', 'INV_MISSING_REQUIRED_PARAMETER');
2157 fnd_msg_pub.ADD;
2158 RAISE fnd_api.g_exc_unexpected_error;
2159 END IF;
2160
2161 WHILE i IS NOT NULL LOOP
2162 IF p_mold_table(i).organization_id IS NULL
2163 OR p_mold_table(i).organization_id = fnd_api.g_miss_num THEN
2164 fnd_message.set_name('INV', 'INV_MISSING_REQUIRED_PARAMETER');
2165 fnd_msg_pub.ADD;
2166 RAISE fnd_api.g_exc_unexpected_error;
2167 END IF;
2168
2169 IF p_mold_table(i).subinventory_code IS NULL
2170 OR p_mold_table(i).subinventory_code = fnd_api.g_miss_char THEN
2171 fnd_message.set_name('INV', 'INV_MISSING_REQUIRED_PARAMETER');
2172 fnd_msg_pub.ADD;
2173 RAISE fnd_api.g_exc_unexpected_error;
2174 END IF;
2175
2176 IF p_mold_table(i).locator_id = fnd_api.g_miss_num THEN
2177 fnd_message.set_name('INV', 'INV_MISSING_REQUIRED_PARAMETER');
2178 fnd_msg_pub.ADD;
2179 RAISE fnd_api.g_exc_unexpected_error;
2180 END IF;
2181
2182 IF p_mold_table(i).inventory_item_id IS NULL
2183 OR p_mold_table(i).locator_id = fnd_api.g_miss_num THEN
2184 fnd_message.set_name('INV', 'INV_MISSING_REQUIRED_PARAMETER');
2185 fnd_msg_pub.ADD;
2186 RAISE fnd_api.g_exc_unexpected_error;
2187 END IF;
2188
2189 IF p_mold_table(i).revision = fnd_api.g_miss_char THEN
2190 fnd_message.set_name('INV', 'INV_MISSING_REQUIRED_PARAMETER');
2191 fnd_msg_pub.ADD;
2192 RAISE fnd_api.g_exc_unexpected_error;
2193 END IF;
2194
2195 IF p_mold_table(i).lot_number = fnd_api.g_miss_char THEN
2196 fnd_message.set_name('INV', 'INV_MISSING_REQUIRED_PARAMETER');
2197 fnd_msg_pub.ADD;
2198 RAISE fnd_api.g_exc_unexpected_error;
2199 END IF;
2200
2201 IF p_mold_table(i).serial_number = fnd_api.g_miss_char THEN
2202 fnd_message.set_name('INV', 'INV_MISSING_REQUIRED_PARAMETER');
2203 fnd_msg_pub.ADD;
2204 RAISE fnd_api.g_exc_unexpected_error;
2205 END IF;
2206
2207 IF p_mold_table(i).transaction_uom = fnd_api.g_miss_char THEN
2208 fnd_message.set_name('INV', 'INV_MISSING_REQUIRED_PARAMETER');
2209 fnd_msg_pub.ADD;
2210 RAISE fnd_api.g_exc_unexpected_error;
2211 END IF;
2212
2213 IF p_mold_table(i).transaction_quantity IS NULL
2214 OR p_mold_table(i).transaction_quantity = 0
2215 OR p_mold_table(i).locator_id = fnd_api.g_miss_num THEN
2216 fnd_message.set_name('INV', 'INV_MISSING_REQUIRED_PARAMETER');
2217 fnd_msg_pub.ADD;
2218 RAISE fnd_api.g_exc_unexpected_error;
2219 END IF;
2220
2221 BEGIN
2222 SELECT primary_uom_code
2223 , lot_control_code
2224 , serial_number_control_code
2225 , secondary_uom_code
2226 INTO l_primary_uom_code
2227 , l_lot_control_code
2228 , l_serial_control_code
2229 , l_secondary_uom_code
2230 FROM mtl_system_items
2231 WHERE inventory_item_id = p_mold_table(i).inventory_item_id
2232 AND organization_id = p_mold_table(i).organization_id;
2233 EXCEPTION
2234 WHEN NO_DATA_FOUND THEN
2235 fnd_message.set_name('INV', 'INV-NO ITEM RECORD');
2236 fnd_msg_pub.ADD;
2237 RAISE fnd_api.g_exc_error;
2238 WHEN OTHERS THEN
2239 RAISE;
2240 END;
2241
2242 -- API does not handle serial controlled items
2243 IF l_serial_control_code IN(2, 5) THEN
2244 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
2245 fnd_message.set_token('ENTITY1', 'SERIAL_CONTROL', TRUE);
2246 fnd_msg_pub.ADD;
2247 RAISE fnd_api.g_exc_error;
2248 END IF;
2249
2250 -- Get the primary quantity
2251 l_primary_quantity :=
2252 inv_convert.inv_um_convert(
2253 p_mold_table(i).inventory_item_id
2254 , 5
2255 , p_mold_table(i).transaction_quantity
2256 , p_mold_table(i).transaction_uom
2257 , l_primary_uom_code
2258 , NULL
2259 , NULL
2260 );
2261
2262 IF l_primary_quantity = -99999 THEN
2263 fnd_message.set_name('INV', 'INV-CANNOT CONVERT');
2264 fnd_message.set_token('UOM', p_mold_table(i).transaction_uom);
2265 fnd_message.set_token('ROUTINE', 'inv_mo_line_detail_util.update_quantity_allocations');
2266 fnd_msg_pub.ADD;
2267 RAISE fnd_api.g_exc_error;
2268 END IF;
2269
2270 IF l_lot_control_code = 1
2271 AND l_serial_control_code IN(1, 6) THEN
2272 -- No control item
2273 l_current_table := 'MTL_MATERIAL_TRANSACTIONS_TEMP';
2274
2275 SELECT ROWID
2276 , transaction_temp_id
2277 , transaction_quantity
2278 , primary_quantity
2279 , secondary_transaction_quantity
2280 INTO l_mmtt_rowid
2281 , l_transaction_temp_id
2282 , l_mmtt_transaction_quantity
2283 , l_mmtt_primary_quantity
2284 , l_mmtt_sec_transaction_qty
2285 FROM mtl_material_transactions_temp
2286 WHERE organization_id = p_mold_table(i).organization_id
2287 AND subinventory_code = p_mold_table(i).subinventory_code
2288 AND NVL(locator_id, -1) = NVL(p_mold_table(i).locator_id, -1)
2289 AND inventory_item_id = p_mold_table(i).inventory_item_id
2290 AND NVL(revision, '@@@') = NVL(p_mold_table(i).revision, '@@@')
2291 AND move_order_line_id = p_move_order_line_id
2292 FOR UPDATE OF primary_quantity, transaction_quantity;
2293
2294 -- Update the picked quantity in the table
2295 IF NOT(l_mmtt_table.EXISTS(l_transaction_temp_id)) THEN
2296 l_mmtt_table(l_transaction_temp_id).row_id := l_mmtt_rowid;
2297 l_mmtt_table(l_transaction_temp_id).transaction_quantity := l_mmtt_transaction_quantity;
2298 l_mmtt_table(l_transaction_temp_id).picked_transaction_quantity := p_mold_table(i).transaction_quantity;
2299 l_mmtt_table(l_transaction_temp_id).sec_transaction_qty := l_mmtt_sec_transaction_qty;
2300 l_mmtt_table(l_transaction_temp_id).picked_sec_transaction_qty := p_mold_table(i).secondary_transaction_quantity;
2301 ELSE
2302 l_mmtt_table(l_transaction_temp_id).picked_transaction_quantity :=
2303 l_mmtt_table(l_transaction_temp_id).picked_transaction_quantity
2304 + p_mold_table(i).transaction_quantity;
2305 l_mmtt_table(l_transaction_temp_id).picked_sec_transaction_qty :=
2306 l_mmtt_table(l_transaction_temp_id).picked_sec_transaction_qty
2307 + p_mold_table(i).secondary_transaction_quantity;
2308 END IF;
2309
2310 IF l_mmtt_transaction_quantity > p_mold_table(i).transaction_quantity THEN
2311 UPDATE mtl_material_transactions_temp
2312 SET transaction_quantity = p_mold_table(i).transaction_quantity
2313 , secondary_transaction_quantity = p_mold_table(i).secondary_transaction_quantity
2314 , primary_quantity = l_primary_quantity
2315 WHERE ROWID = l_mmtt_rowid;
2316 ELSIF l_mmtt_transaction_quantity < p_mold_table(i).transaction_quantity THEN
2317 fnd_message.set_name('INV', 'INV_QUANTITY_TOO_BIG');
2318 fnd_msg_pub.ADD;
2319 RAISE fnd_api.g_exc_error;
2320 END IF;
2321 ELSIF l_lot_control_code = 2
2322 AND l_serial_control_code IN(1, 6) THEN
2323 -- Lot controlled item
2324 l_current_table := 'MTL_MATERIAL_TRANSACTIONS_TEMP';
2325
2326 SELECT ROWID
2327 , transaction_temp_id
2328 , transaction_quantity
2329 , secondary_transaction_quantity
2330 INTO l_mmtt_rowid
2331 , l_transaction_temp_id
2332 , l_mmtt_transaction_quantity
2333 , l_mmtt_sec_transaction_qty
2334 FROM mtl_material_transactions_temp
2335 WHERE organization_id = p_mold_table(i).organization_id
2336 AND subinventory_code = p_mold_table(i).subinventory_code
2337 AND NVL(locator_id, -1) = NVL(p_mold_table(i).locator_id, -1)
2338 AND inventory_item_id = p_mold_table(i).inventory_item_id
2339 AND NVL(revision, '@@@') = NVL(p_mold_table(i).revision, '@@@')
2340 AND move_order_line_id = p_move_order_line_id;
2341
2342 l_current_table := 'MTL_TRANSACTIONS_LOTS_TEMP';
2343
2344 SELECT ROWID
2345 , primary_quantity
2346 , transaction_quantity
2347 , secondary_quantity
2348 INTO l_mtlt_rowid
2349 , l_mtlt_primary_quantity
2350 , l_mtlt_transaction_quantity
2351 , l_mtlt_sec_transaction_qty
2352 FROM mtl_transaction_lots_temp
2353 WHERE transaction_temp_id = l_transaction_temp_id
2354 AND lot_number = p_mold_table(i).lot_number
2355 FOR UPDATE OF primary_quantity, transaction_quantity;
2356
2357 -- Update the picked quantity in the table
2358 IF NOT(l_mmtt_table.EXISTS(l_transaction_temp_id)) THEN
2359 l_mmtt_table(l_transaction_temp_id).row_id := l_mmtt_rowid;
2360 l_mmtt_table(l_transaction_temp_id).transaction_quantity := l_mmtt_transaction_quantity;
2361 l_mmtt_table(l_transaction_temp_id).picked_transaction_quantity := p_mold_table(i).transaction_quantity;
2362 l_mmtt_table(l_transaction_temp_id).picked_primary_quantity := l_primary_quantity;
2363 l_mmtt_table(l_transaction_temp_id).sec_transaction_qty := l_mmtt_sec_transaction_qty;
2364 l_mmtt_table(l_transaction_temp_id).picked_sec_transaction_qty := p_mold_table(i).secondary_transaction_quantity;
2365
2366 ELSE
2367 l_mmtt_table(l_transaction_temp_id).picked_transaction_quantity :=
2368 l_mmtt_table(l_transaction_temp_id).picked_transaction_quantity
2369 + p_mold_table(i).transaction_quantity;
2370 l_mmtt_table(l_transaction_temp_id).picked_primary_quantity :=
2371 l_mmtt_table(l_transaction_temp_id).picked_primary_quantity
2372 + l_primary_quantity;
2373 l_mmtt_table(l_transaction_temp_id).picked_sec_transaction_qty :=
2374 l_mmtt_table(l_transaction_temp_id).picked_sec_transaction_qty
2375 + p_mold_table(i).secondary_transaction_quantity;
2376
2377 END IF;
2378
2379 -- Put the processed MTLT line rowid in the table
2380 l_mtlt_table(l_mtlt_table_counter) := l_mtlt_rowid;
2381 l_mtlt_table_counter := l_mtlt_table_counter + 1;
2382
2383 UPDATE mtl_material_transactions_temp
2384 SET transaction_quantity = l_mmtt_table(l_transaction_temp_id).picked_transaction_quantity
2385 , secondary_transaction_quantity = l_mmtt_table(l_transaction_temp_id).picked_sec_transaction_qty
2386 , primary_quantity = l_mmtt_table(l_transaction_temp_id).picked_primary_quantity
2387 WHERE ROWID = l_mmtt_rowid;
2388
2389 IF l_mtlt_transaction_quantity > p_mold_table(i).transaction_quantity THEN
2390 UPDATE mtl_transaction_lots_temp
2391 SET transaction_quantity = p_mold_table(i).transaction_quantity
2392 , secondary_quantity = p_mold_table(i).secondary_transaction_quantity
2393 , primary_quantity = l_primary_quantity
2394 WHERE ROWID = l_mtlt_rowid;
2395 ELSIF l_mtlt_transaction_quantity < p_mold_table(i).transaction_quantity THEN
2396 fnd_message.set_name('INV', 'INV_QUANTITY_TOO_BIG');
2397 fnd_msg_pub.ADD;
2398 RAISE fnd_api.g_exc_error;
2399 END IF;
2400 END IF; -- Type of item control
2401
2402 i := p_mold_table.NEXT(i);
2403 END LOOP;
2404
2405 -- after all this is done we need to delete the remaining MMTT, MTLT,
2406 -- MSNT lines which have not been picked
2407 IF l_mtlt_table.COUNT > 0 THEN
2408 FOR i IN 1 .. l_mtlt_table.COUNT LOOP
2409 IF (i = 1) THEN
2410 l_del_mtlt := l_del_mtlt || ' AND ROWID NOT IN (' || '''' || l_mtlt_table(i) || '''';
2411 ELSE
2412 l_del_mtlt := l_del_mtlt || ', ' || '''' || l_mtlt_table(i) || '''';
2413 END IF;
2414
2415 IF i = l_mtlt_table.COUNT THEN
2416 l_del_mtlt := l_del_mtlt || ')';
2417 END IF;
2418 END LOOP;
2419
2420 l_sql_p := DBMS_SQL.open_cursor;
2421 DBMS_SQL.parse(l_sql_p, l_del_mtlt, DBMS_SQL.native);
2422 DBMS_SQL.bind_variable(l_sql_p, 'b_move_order_line_id', p_move_order_line_id);
2423 l_rows_processed := DBMS_SQL.EXECUTE(l_sql_p);
2424 END IF;
2425
2426 IF l_mmtt_table.COUNT > 0 THEN
2427 i := l_mmtt_table.FIRST;
2428
2429 WHILE i IS NOT NULL LOOP
2430 IF (i = l_mmtt_table.FIRST) THEN
2431 l_del_mmtt := l_del_mmtt || ' AND ROWID NOT IN (' || '''' || l_mmtt_table(i).row_id || '''';
2432 ELSE
2433 l_del_mmtt := l_del_mmtt || ', ' || '''' || l_mmtt_table(i).row_id || '''';
2434 END IF;
2435
2436 IF i = l_mmtt_table.LAST THEN
2437 l_del_mmtt := l_del_mmtt || ')';
2438 END IF;
2439
2440 i := l_mmtt_table.NEXT(i);
2441 END LOOP;
2442
2443 l_sql_p := DBMS_SQL.open_cursor;
2444 DBMS_SQL.parse(l_sql_p, l_del_mmtt, DBMS_SQL.native);
2445 DBMS_SQL.bind_variable(l_sql_p, 'b_move_order_line_id', p_move_order_line_id);
2446 l_rows_processed := DBMS_SQL.EXECUTE(l_sql_p);
2447 END IF;
2448
2449 -- Requery the MMTT rows and pass them back as the output parameter
2450 x_mold_table := inv_mo_line_detail_util.query_rows(p_move_order_line_id);
2451 -- Get message count and data
2452 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2453 EXCEPTION
2454 WHEN TOO_MANY_ROWS THEN
2455 x_return_status := fnd_api.g_ret_sts_error;
2456 fnd_message.set_name('INV', 'INV_MGD_MVT_TOO_MANY_TRANS');
2457 fnd_msg_pub.ADD;
2458 ROLLBACK TO update_qty;
2459 WHEN NO_DATA_FOUND THEN
2460 x_return_status := fnd_api.g_ret_sts_error;
2461 fnd_message.set_name('INV', 'INV_NO_RECORDS');
2462 fnd_message.set_token('ENTITY', l_current_table);
2463 fnd_msg_pub.ADD;
2464 -- Get message count and data
2465 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2466 ROLLBACK TO update_qty;
2467 WHEN fnd_api.g_exc_error THEN
2468 x_return_status := fnd_api.g_ret_sts_error;
2469 -- Get message count and data
2470 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2471 ROLLBACK TO update_qty;
2472 WHEN fnd_api.g_exc_unexpected_error THEN
2473 x_return_status := fnd_api.g_ret_sts_unexp_error;
2474 -- Get message count and data
2475 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2476 ROLLBACK TO update_qty;
2477 WHEN OTHERS THEN
2478 x_return_status := fnd_api.g_ret_sts_unexp_error;
2479 -- Get message count and data
2480 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2481 ROLLBACK TO update_qty;
2482 END;
2483
2484 PROCEDURE delete_allocations(
2485 x_return_status OUT NOCOPY VARCHAR2
2486 , x_msg_data OUT NOCOPY VARCHAR2
2487 , x_msg_count OUT NOCOPY NUMBER
2488 , p_mo_line_id IN NUMBER
2489 , p_transaction_temp_id IN NUMBER
2490 ) IS
2491 CURSOR c_mmtt_info IS
2492 SELECT transaction_temp_id
2493 FROM mtl_material_transactions_temp
2494 WHERE (p_mo_line_id IS NOT NULL AND move_order_line_id = p_mo_line_id)
2495 AND (p_transaction_temp_id IS NULL)
2496 UNION ALL
2497 SELECT transaction_temp_id
2498 FROM mtl_material_transactions_temp
2499 WHERE (p_transaction_temp_id IS NOT NULL AND transaction_temp_id = p_transaction_temp_id);
2500 BEGIN
2501 x_return_status := fnd_api.g_ret_sts_success;
2502 IF p_mo_line_id IS NULL AND p_transaction_temp_id IS NULL THEN
2503 debug('Either Move Order Line ID or Transaction Temp ID has to be passed','DELETE_ALLOCATIONS');
2504 fnd_message.set_name('INV','INV_MISSING_REQUIRED_PARAMETER');
2505 fnd_msg_pub.ADD;
2506 RAISE fnd_api.g_exc_error;
2507 END IF;
2508
2509 FOR l_mmtt IN c_mmtt_info LOOP
2510 inv_trx_util_pub.delete_transaction(
2511 x_return_status => x_return_status
2512 , x_msg_data => x_msg_data
2513 , x_msg_count => x_msg_count
2514 , p_transaction_temp_id => l_mmtt.transaction_temp_id
2515 );
2516 END LOOP;
2517 EXCEPTION
2518 WHEN fnd_api.g_exc_error THEN
2519 x_return_status := fnd_api.g_ret_sts_error;
2520 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2521 WHEN fnd_api.g_exc_unexpected_error THEN
2522 x_return_status := fnd_api.g_ret_sts_unexp_error;
2523 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2524 WHEN OTHERS THEN
2525 x_return_status := fnd_api.g_ret_sts_unexp_error;
2526 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2527 END delete_allocations;
2528
2529 PROCEDURE reduce_allocation_quantity(
2530 x_return_status OUT NOCOPY VARCHAR2
2531 , p_transaction_temp_id IN NUMBER
2532 , p_quantity IN NUMBER
2533 , p_secondary_quantity IN NUMBER --INVCONV
2534 ) IS
2535 l_transaction_quantity NUMBER;
2536 l_sec_transaction_quantity NUMBER; --INVCONV
2537 l_organization_id NUMBER;
2538 l_inventory_item_id NUMBER;
2539 l_primary_uom_code VARCHAR2(3);
2540 l_transaction_uom_code VARCHAR2(3);
2541 l_secondary_uom_code VARCHAR2(3); --INVCONV
2542 l_remaining_quantity NUMBER;
2543 l_lot_quantity NUMBER;
2544 l_lot_sec_quantity NUMBER; --INVCONV
2545 l_lot_rowid ROWID;
2546 l_serial_transaction_temp_id NUMBER;
2547 l_from_serial_number VARCHAR2(30);
2548 l_to_serial_number VARCHAR2(30);
2549 l_serial_rowid ROWID;
2550 l_lot_qty_to_delete NUMBER;
2551 l_serial_qty_to_delete NUMBER;
2552 l_serial_quantity NUMBER;
2553 l_new_lot_quantity NUMBER;
2554 l_new_lot_prim_quantity NUMBER;
2555 l_new_lot_sec_quantity NUMBER; --INVCONV
2556 l_new_sec_quantity NUMBER; --INVCONV
2557 l_new_quantity NUMBER;
2558 l_new_prim_quantity NUMBER;
2559 l_last_deleted_serial_number VARCHAR2(30);
2560 l_new_from_serial_number VARCHAR2(30);
2561 l_from_prefix VARCHAR2(30);
2562 l_from_num NUMBER;
2563 l_new_num NUMBER;
2564 l_msg_data VARCHAR2(2000);
2565 l_msg_count NUMBER;
2566
2567 CURSOR c_mmtt_info IS
2568 SELECT transaction_quantity
2569 , secondary_transaction_quantity --INVCONV
2570 , organization_id
2571 , inventory_item_id
2572 , item_primary_uom_code
2573 , transaction_uom
2574 , secondary_uom_code --INVCONV
2575 FROM mtl_material_transactions_temp
2576 WHERE transaction_temp_id = p_transaction_temp_id
2577 FOR UPDATE;
2578
2579 CURSOR c_primary_uom IS
2580 SELECT primary_uom_code, secondary_uom_code --INVCONV
2581 FROM mtl_system_items
2582 WHERE organization_id = l_organization_id
2583 AND inventory_item_id = l_inventory_item_id;
2584
2585 CURSOR c_lot_allocations IS
2586 SELECT transaction_quantity
2587 , secondary_quantity --INVCONV
2588 , serial_transaction_temp_id
2589 , ROWID
2590 FROM mtl_transaction_lots_temp
2591 WHERE transaction_temp_id = p_transaction_temp_id
2592 ORDER BY transaction_quantity ASC;
2593
2594 CURSOR c_serial_allocations IS
2595 SELECT fm_serial_number
2596 , NVL(to_serial_number, fm_serial_number)
2597 , ROWID
2598 FROM mtl_serial_numbers_temp
2599 WHERE transaction_temp_id = l_serial_transaction_temp_id;
2600
2601 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2602 BEGIN
2603 SAVEPOINT reduce_sp;
2604
2605 IF p_quantity IS NULL
2606 OR p_transaction_temp_id IS NULL
2607 OR p_quantity <= 0 THEN
2608 RETURN;
2609 END IF;
2610
2611 OPEN c_mmtt_info;
2612 FETCH c_mmtt_info INTO l_transaction_quantity, l_sec_transaction_quantity, l_organization_id, l_inventory_item_id, l_primary_uom_code
2613 , l_transaction_uom_code, l_secondary_uom_code;
2614
2615 IF c_mmtt_info%NOTFOUND THEN
2616 RETURN;
2617 END IF;
2618
2619 CLOSE c_mmtt_info;
2620
2621 IF p_quantity >= l_transaction_quantity THEN
2622 delete_allocations(x_return_status, l_msg_data, l_msg_count, NULL, p_transaction_temp_id);
2623 RETURN;
2624 END IF;
2625
2626 IF l_primary_uom_code IS NULL THEN
2627 OPEN c_primary_uom;
2628 FETCH c_primary_uom INTO l_primary_uom_code, l_secondary_uom_code;
2629
2630 IF c_primary_uom%NOTFOUND
2631 OR l_primary_uom_code IS NULL THEN
2632 RETURN;
2633 END IF;
2634
2635 CLOSE c_primary_uom;
2636 END IF;
2637
2638 l_remaining_quantity := p_quantity;
2639 OPEN c_lot_allocations;
2640
2641 LOOP
2642 EXIT WHEN l_remaining_quantity <= 0;
2643 FETCH c_lot_allocations INTO l_lot_quantity, l_lot_sec_quantity, l_serial_transaction_temp_id, l_lot_rowid;
2644 EXIT WHEN c_lot_allocations%NOTFOUND;
2645
2646 IF l_lot_quantity <= l_remaining_quantity THEN
2647 l_lot_qty_to_delete := l_lot_quantity;
2648 ELSE
2649 l_lot_qty_to_delete := l_remaining_quantity;
2650 END IF;
2651
2652 l_remaining_quantity := l_remaining_quantity - l_lot_qty_to_delete;
2653 l_serial_qty_to_delete := l_lot_qty_to_delete;
2654 OPEN c_serial_allocations;
2655
2656 LOOP
2657 EXIT WHEN l_serial_qty_to_delete <= 0;
2658 FETCH c_serial_allocations INTO l_from_serial_number, l_to_serial_number, l_serial_rowid;
2659 EXIT WHEN c_serial_allocations%NOTFOUND;
2660
2661 --different processing if the serial record has a single
2662 -- serial number or multiple serial numbers
2663 IF l_to_serial_number <> l_from_serial_number THEN
2664 --determine how many to delete
2665 l_serial_quantity := inv_detail_util_pvt.subtract_serials(l_from_serial_number, l_to_serial_number);
2666 ELSE
2667 l_serial_quantity := 1;
2668 END IF;
2669
2670 IF l_serial_quantity > l_serial_qty_to_delete THEN
2671 --determine last serial number to delete and first
2672 -- serial number to keep
2673 inv_detail_util_pvt.split_prefix_num(p_serial_number => l_from_serial_number, p_prefix => l_from_prefix
2674 , x_num => l_from_num);
2675 l_new_num := l_from_num + l_serial_qty_to_delete - 1;
2676 l_last_deleted_serial_number := l_from_prefix || l_new_num;
2677 l_new_num := l_new_num + 1;
2678 l_new_from_serial_number := l_from_prefix || l_new_num;
2679
2680 UPDATE mtl_serial_numbers
2681 SET group_mark_id = NULL
2682 WHERE serial_number BETWEEN l_from_serial_number AND l_last_deleted_serial_number;
2683
2684 UPDATE mtl_serial_numbers_temp
2685 SET fm_serial_number = l_new_from_serial_number
2686 WHERE ROWID = l_serial_rowid;
2687
2688 l_serial_qty_to_delete := 0;
2689 ELSE --delete the row
2690
2691 --unmark in serial number table
2692 UPDATE mtl_serial_numbers
2693 SET group_mark_id = NULL
2694 WHERE inventory_item_id = l_inventory_item_id
2695 AND serial_number BETWEEN l_from_serial_number AND l_to_serial_number;
2696
2697 --delete records
2698 DELETE FROM mtl_serial_numbers_temp
2699 WHERE ROWID = l_serial_rowid;
2700
2701 --decrement
2702 l_serial_qty_to_delete := l_serial_qty_to_delete - l_serial_quantity;
2703 END IF;
2704 END LOOP;
2705
2706 CLOSE c_serial_allocations;
2707
2708 IF l_lot_qty_to_delete = l_lot_quantity THEN
2709 DELETE FROM mtl_transaction_lots_temp
2710 WHERE ROWID = l_lot_rowid;
2711 ELSE
2712 l_new_lot_quantity := l_lot_quantity - l_lot_qty_to_delete;
2713 --convert quantity
2714 l_new_lot_prim_quantity :=
2715 inv_convert.inv_um_convert(l_inventory_item_id, 5, l_new_lot_quantity, l_transaction_uom_code
2716 , l_primary_uom_code, NULL, NULL);
2717
2718 IF l_new_prim_quantity = -99999 THEN
2719 RAISE fnd_api.g_exc_error;
2720 END IF;
2721
2722 UPDATE mtl_transaction_lots_temp
2723 SET transaction_quantity = l_new_lot_quantity
2724 , primary_quantity = l_new_lot_prim_quantity
2725 WHERE ROWID = l_lot_rowid;
2726 END IF;
2727 END LOOP;
2728
2729 CLOSE c_lot_allocations;
2730 l_new_quantity := l_transaction_quantity - p_quantity;
2731 --convert quantity
2732 l_new_prim_quantity :=
2733 inv_convert.inv_um_convert(l_inventory_item_id, 5, l_new_quantity, l_transaction_uom_code, l_primary_uom_code
2734 , NULL, NULL);
2735
2736 IF l_new_prim_quantity = -99999 THEN
2737 RAISE fnd_api.g_exc_error;
2738 END IF;
2739
2740 UPDATE mtl_material_transactions_temp
2741 SET transaction_quantity = l_new_quantity
2742 , primary_quantity = l_new_prim_quantity
2743 WHERE transaction_temp_id = p_transaction_temp_id;
2744
2745 x_return_status := fnd_api.g_ret_sts_success;
2746 EXCEPTION
2747 WHEN OTHERS THEN
2748 ROLLBACK TO reduce_sp;
2749 x_return_status := fnd_api.g_ret_sts_unexp_error;
2750 END reduce_allocation_quantity;
2751
2752 /* Bug 2427514: To get the Lot Qty for a Given Transaction Temp ID */
2753 PROCEDURE get_lot_quantity(
2754 x_return_status OUT NOCOPY VARCHAR2
2755 , x_msg_count OUT NOCOPY NUMBER
2756 , x_msg_data OUT NOCOPY VARCHAR2
2757 , p_transaction_temp_id IN NUMBER
2758 , x_lot_quantity OUT NOCOPY NUMBER
2759 ) IS
2760 l_procedure_name VARCHAR2(30) := 'GET_LOT_QUANTITY';
2761
2762 CURSOR lot_records IS
2763 SELECT NVL(SUM(mtlt.primary_quantity), 0)
2764 FROM mtl_transaction_lots_temp mtlt
2765 WHERE mtlt.transaction_temp_id = p_transaction_temp_id;
2766
2767 l_lot_quantity NUMBER := 0;
2768 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2769 BEGIN
2770 OPEN lot_records;
2771 FETCH lot_records INTO l_lot_quantity;
2772 CLOSE lot_records;
2773 x_lot_quantity := l_lot_quantity;
2774 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2775 x_return_status := fnd_api.g_ret_sts_success;
2776 EXCEPTION
2777 WHEN fnd_api.g_exc_error THEN
2778 x_lot_quantity := 0;
2779 x_return_status := fnd_api.g_ret_sts_error;
2780 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2781 WHEN fnd_api.g_exc_unexpected_error THEN
2782 x_lot_quantity := 0;
2783 x_return_status := fnd_api.g_ret_sts_unexp_error;
2784 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2785 WHEN OTHERS THEN
2786 x_lot_quantity := 0;
2787 x_return_status := fnd_api.g_ret_sts_unexp_error;
2788
2789 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2790 fnd_msg_pub.add_exc_msg(g_pkg_name, l_procedure_name);
2791 END IF;
2792
2793 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2794 END get_lot_quantity;
2795
2796 /* Bug 2427514: To get the Serial Qty for a Given Transaction Temp ID */
2797 PROCEDURE get_serial_quantity(
2798 x_return_status OUT NOCOPY VARCHAR2
2799 , x_msg_count OUT NOCOPY NUMBER
2800 , x_msg_data OUT NOCOPY VARCHAR2
2801 , p_transaction_temp_id IN NUMBER
2802 , x_serial_quantity OUT NOCOPY NUMBER
2803 ) IS
2804 l_procedure_name VARCHAR2(30) := 'GET_SERIAL_QUANTITY';
2805
2806 CURSOR serial_records IS
2807 SELECT SUM(inv_serial_number_pub.get_serial_diff(msnt.fm_serial_number, msnt.to_serial_number))
2808 FROM mtl_serial_numbers_temp msnt
2809 WHERE msnt.transaction_temp_id = p_transaction_temp_id;
2810
2811 l_serial_quantity NUMBER := 0;
2812 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2813 BEGIN
2814 OPEN serial_records;
2815 FETCH serial_records INTO l_serial_quantity;
2816 CLOSE serial_records;
2817 x_serial_quantity := l_serial_quantity;
2818 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2819 x_return_status := fnd_api.g_ret_sts_success;
2820 EXCEPTION
2821 WHEN fnd_api.g_exc_error THEN
2822 x_serial_quantity := 0;
2823 x_return_status := fnd_api.g_ret_sts_error;
2824 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2825 WHEN fnd_api.g_exc_unexpected_error THEN
2826 x_serial_quantity := 0;
2827 x_return_status := fnd_api.g_ret_sts_unexp_error;
2828 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2829 WHEN OTHERS THEN
2830 x_serial_quantity := 0;
2831 x_return_status := fnd_api.g_ret_sts_unexp_error;
2832
2833 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2834 fnd_msg_pub.add_exc_msg(g_pkg_name, l_procedure_name);
2835 END IF;
2836
2837 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2838 END get_serial_quantity;
2839
2840 /* Bug 2427514: To check the whether records exists in MTLT and MSNT for a
2841 given Transaction Temp ID */
2842 PROCEDURE are_allocations_complete(
2843 x_return_status OUT NOCOPY VARCHAR2
2844 , x_msg_count OUT NOCOPY NUMBER
2845 , x_msg_data OUT NOCOPY VARCHAR2
2846 , p_transaction_temp_id IN NUMBER
2847 ) IS
2848 l_procedure_name VARCHAR2(30) := 'ARE_ALLOCATIONS_COMPLETE';
2849
2850 CURSOR item_properties(c_transaction_temp_id NUMBER) IS
2851 SELECT NVL(lot_control_code, 1) lot_control_code
2852 , NVL(serial_number_control_code, 1) serial_number_control_code
2853 , mmtt.primary_quantity primary_quantity
2854 , mmtt.lot_number
2855 , mmtt.lot_expiration_date
2856 , mmtt.transaction_date
2857 FROM mtl_system_items_b msi, mtl_material_transactions_temp mmtt
2858 WHERE msi.inventory_item_id = mmtt.inventory_item_id
2859 AND msi.organization_id = mmtt.organization_id
2860 AND mmtt.transaction_temp_id = c_transaction_temp_id;
2861
2862 CURSOR lot_records(c_transaction_temp_id NUMBER) IS
2863 SELECT mtlt.serial_transaction_temp_id
2864 , mtlt.primary_quantity
2865 FROM mtl_transaction_lots_temp mtlt
2866 WHERE mtlt.transaction_temp_id = c_transaction_temp_id;
2867
2868 l_quantity NUMBER := 0;
2869 l_lot_quantity NUMBER := 0;
2870 l_serial_quantity NUMBER := 0;
2871 l_temp_quantity NUMBER := 0;
2872 l_lot_control_code NUMBER := 1;
2873 l_serial_number_control_code NUMBER := 1;
2874 l_serial_transaction_temp_id NUMBER;
2875 l_return_status VARCHAR2(10) := fnd_api.g_ret_sts_unexp_error;
2876 l_msg_data VARCHAR2(1000);
2877 l_msg_count NUMBER := 0;
2878 l_lot_number mtl_lot_numbers.lot_number%TYPE;
2879 l_lot_expiration_date DATE;
2880 l_transaction_date DATE;
2881 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2882 BEGIN
2883 OPEN item_properties(p_transaction_temp_id);
2884 FETCH item_properties INTO l_lot_control_code
2885 , l_serial_number_control_code
2886 , l_quantity
2887 , l_lot_number
2888 , l_lot_expiration_date
2889 , l_transaction_date;
2890 CLOSE item_properties;
2891
2892 /* Changed for 2462679 */
2893 IF l_lot_control_code = 1 AND l_serial_number_control_code IN(1, 6) THEN
2894 x_return_status := fnd_api.g_ret_sts_success;
2895 GOTO success;
2896 END IF;
2897
2898 /*
2899 ** Only Lot controlled (and not serial controlled)
2900 ** Allocated Lot quantity must match transaction quantity
2901 ** If lot information is available from MMTT (manual allocations)
2902 ** then allocated quantity would be same as primary quantity
2903 */
2904 /* Changed for 2462679 */
2905 IF l_lot_control_code <> 1 AND l_serial_number_control_code IN(1, 6) THEN
2906 l_lot_expiration_date := TRUNC(l_lot_expiration_date);
2907 l_transaction_date := TRUNC(l_transaction_date);
2908
2909 IF l_lot_number IS NOT NULL THEN --Bug3639464
2910 l_lot_quantity := l_quantity;
2911 ELSE
2912 get_lot_quantity(l_return_status, l_msg_data, l_msg_count, p_transaction_temp_id, l_lot_quantity);
2913
2914 IF l_return_status = fnd_api.g_ret_sts_error THEN
2915 RAISE fnd_api.g_exc_error;
2916 END IF;
2917
2918 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2919 RAISE fnd_api.g_exc_unexpected_error;
2920 END IF;
2921 END IF;
2922
2923 IF l_lot_quantity <> l_quantity THEN
2924 RAISE fnd_api.g_exc_error;
2925 ELSE
2926 GOTO success;
2927 END IF;
2928 END IF;
2929
2930 /*
2931 ** Only Serial controlled (and not lot controlled)
2932 ** Allocated Serials must match transaction quantity
2933 */
2934 /* Changed for 2462679 */
2935 IF l_lot_control_code = 1 AND l_serial_number_control_code NOT IN(1, 6) THEN
2936 get_serial_quantity(l_return_status, l_msg_data, l_msg_count, p_transaction_temp_id, l_serial_quantity);
2937
2938 IF l_return_status = fnd_api.g_ret_sts_error THEN
2939 RAISE fnd_api.g_exc_error;
2940 END IF;
2941
2942 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2943 RAISE fnd_api.g_exc_unexpected_error;
2944 END IF;
2945
2946 /*Bug 6790396 added nvl to l_serial_quantity*/
2947 IF NVL(l_serial_quantity,0) <> l_quantity THEN
2948 RAISE fnd_api.g_exc_error;
2949 ELSE
2950 GOTO success;
2951 END IF;
2952 END IF;
2953
2954 /*
2955 ** Lot and Serial controlled items.
2956 ** Allocated Serials for each Lot must match transaction quantity of each lot
2957 ** Sum of all allocated lots must match transaction quantity
2958 */
2959 IF l_lot_number IS NOT NULL THEN
2960 x_return_status := fnd_api.g_ret_sts_success;
2961 GOTO success;
2962 END IF;
2963
2964 OPEN lot_records(p_transaction_temp_id);
2965
2966 LOOP
2967 FETCH lot_records INTO l_serial_transaction_temp_id, l_lot_quantity;
2968 EXIT WHEN lot_records%NOTFOUND;
2969 get_serial_quantity(l_return_status, l_msg_data, l_msg_count, l_serial_transaction_temp_id, l_serial_quantity);
2970
2971 IF l_return_status = fnd_api.g_ret_sts_error THEN
2972 RAISE fnd_api.g_exc_error;
2973 END IF;
2974
2975 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2976 RAISE fnd_api.g_exc_unexpected_error;
2977 END IF;
2978
2979 /*Bug 6790396 added nvl to l_serial_quantity*/
2980 IF NVL(l_serial_quantity,0) <> l_lot_quantity THEN
2981 RAISE fnd_api.g_exc_error;
2982 ELSE
2983 l_temp_quantity := l_temp_quantity + l_lot_quantity;
2984 END IF;
2985 END LOOP;
2986
2987 CLOSE lot_records;
2988
2989 IF (l_temp_quantity <> l_quantity) THEN
2990 RAISE fnd_api.g_exc_error;
2991 END IF;
2992
2993 <<success>>
2994 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2995 x_return_status := fnd_api.g_ret_sts_success;
2996 EXCEPTION
2997 WHEN fnd_api.g_exc_error THEN
2998 x_return_status := fnd_api.g_ret_sts_error;
2999 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3000 WHEN fnd_api.g_exc_unexpected_error THEN
3001 x_return_status := fnd_api.g_ret_sts_unexp_error;
3002 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3003 WHEN OTHERS THEN
3004 x_return_status := fnd_api.g_ret_sts_unexp_error;
3005
3006 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3007 fnd_msg_pub.add_exc_msg(g_pkg_name, l_procedure_name);
3008 END IF;
3009
3010 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3011 END are_allocations_complete;
3012
3013 /* Bug 2427514: To find out whether a MO Line is correctly allocated */
3014 PROCEDURE is_line_detailed(
3015 x_return_status OUT NOCOPY VARCHAR2
3016 , x_msg_count OUT NOCOPY NUMBER
3017 , x_msg_data OUT NOCOPY VARCHAR2
3018 , p_move_order_line_id IN NUMBER
3019 ) IS
3020 l_procedure_name VARCHAR2(30) := 'IS_LINE_DETAILED';
3021
3022 CURSOR mmtt_records IS
3023 SELECT mmtt.transaction_temp_id
3024 FROM mtl_material_transactions_temp mmtt
3025 WHERE mmtt.move_order_line_id = p_move_order_line_id;
3026
3027 l_return_status VARCHAR2(1);
3028 l_msg_count NUMBER;
3029 l_msg_data VARCHAR2(1000);
3030 l_detail_records_exist BOOLEAN := FALSE;
3031 l_transaction_temp_id NUMBER;
3032 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
3033 BEGIN
3034 OPEN mmtt_records;
3035
3036 LOOP
3037 FETCH mmtt_records INTO l_transaction_temp_id;
3038 EXIT WHEN mmtt_records%NOTFOUND;
3039 l_detail_records_exist := TRUE;
3040 are_allocations_complete(l_return_status, l_msg_count, l_msg_data, l_transaction_temp_id);
3041
3042 IF l_return_status = fnd_api.g_ret_sts_error THEN
3043 RAISE fnd_api.g_exc_error;
3044 END IF;
3045
3046 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3047 RAISE fnd_api.g_exc_unexpected_error;
3048 END IF;
3049 END LOOP;
3050
3051 IF NOT l_detail_records_exist THEN
3052 RAISE fnd_api.g_exc_unexpected_error;
3053 END IF;
3054
3055 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3056 x_return_status := fnd_api.g_ret_sts_success;
3057 EXCEPTION
3058 WHEN fnd_api.g_exc_error THEN
3059 x_return_status := fnd_api.g_ret_sts_error;
3060 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3061 WHEN fnd_api.g_exc_unexpected_error THEN
3062 x_return_status := fnd_api.g_ret_sts_unexp_error;
3063 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3064 WHEN OTHERS THEN
3065 x_return_status := fnd_api.g_ret_sts_unexp_error;
3066
3067 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3068 fnd_msg_pub.add_exc_msg(g_pkg_name, l_procedure_name);
3069 END IF;
3070
3071 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3072 END is_line_detailed;
3073 END inv_mo_line_detail_util;