[Home] [Help]
PACKAGE BODY: APPS.EAM_MATERIALISSUE_PVT
Source
1 PACKAGE BODY EAM_MATERIALISSUE_PVT AS
2 /* $Header: EAMMATTB.pls 120.13.12020000.2 2013/01/18 17:46:01 esrodrig ship $*/
3 g_pkg_name CONSTANT VARCHAR2(30):= 'eam_materialissue_pvt';
4 g_debug CONSTANT VARCHAR2(1):=NVL(fnd_profile.value('APPS_DEBUG'),'N');
5 procedure Fork_Logic( p_api_version IN NUMBER ,
6 p_init_msg_list IN VARCHAR2,
7 p_commit IN VARCHAR2 ,
8 p_validation_level IN NUMBER ,
9 x_return_status OUT NOCOPY VARCHAR2 ,
10 x_msg_count OUT NOCOPY NUMBER,
11 x_msg_data OUT NOCOPY VARCHAR2,
12 p_wip_entity_type IN NUMBER,
13 p_organization_id IN NUMBER,
14 p_wip_entity_id IN NUMBER,
15 p_operation_seq_num IN NUMBER ,
16 p_inventory_item_id IN NUMBER ,
17 p_revision IN VARCHAR2 := null,
18 p_requested_quantity IN NUMBER ,
19 p_source_subinventory IN VARCHAR2 ,
20 p_source_locator IN VARCHAR2 ,
21 p_lot_number IN VARCHAR2 ,
22 p_fm_serial IN VARCHAR2 ,
23 p_to_serial IN VARCHAR2,
24 p_reasons IN VARCHAR2 ,
25 p_reference IN VARCHAR2 ,
26 p_date IN date,
27 p_rebuild_item_id IN Number,
28 p_rebuild_item_name IN varchar2,
29 p_rebuild_serial_number IN Varchar2,
30 p_rebuild_job_name IN OUT NOCOPY Varchar2 ,
31 p_rebuild_activity_id IN Number,
32 p_rebuild_activity_name IN varchar2,
33 p_user_id IN Number,
34 p_inventory_item IN varchar2 := null, --Added for bug 8661513
35 p_locator_name IN varchar2 := null) --Added for bug 8661513
36 is
37
38 l_api_name CONSTANT VARCHAR2(30) := 'Fork_Logic';
39 l_api_version CONSTANT NUMBER := 1.0;
40 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
41 l_module constant varchar2(60) := 'eam.plsql.'||l_full_name;
42 l_current_log_level constant number := FND_LOG.G_CURRENT_RUNTIME_LEVEL ;
43 l_log constant boolean := FND_LOG.LEVEL_UNEXPECTED >= l_current_log_level ;
44 l_plog constant boolean := l_log and FND_LOG.LEVEL_PROCEDURE >=l_current_log_level ;
45 l_slog constant boolean := l_plog and FND_LOG.LEVEL_STATEMENT >= l_current_log_level ;
46
47
48 l_material_issue_by_mo varchar2(1);
49 l_project_id number := null;
50 l_task_id number := null;
51 l_issue_by_mo boolean := true;
52 l_inventory_item_name mtl_system_items_b_kfv.concatenated_segments%type := null;
53 l_quantity Number;
54 l_primary_uom_code mtl_system_items_b_kfv.primary_uom_code%type;
55 l_serial_number_control_code Number;
56 l_revision_qty_control_code NUMBER;
57 l_lot_control_code Number;
58 l_onhand_quantity NUMBER;
59 l_is_serial_control BOOLEAN;
60 l_is_lot_control BOOLEAN;
61 l_is_revision_control BOOLEAN;
62 l_reason_id Number;
63 x_err_flag Number;
64 x_error_msg Varchar2(200);
65 x_tmp_id Number;
66 l_inventory_item_id Number;
67 l_organization_id Number;
68 l_source_locator_id Number;
69 l_source_subinventory Varchar2(2000);
70 l_ret_status_qoh VARCHAR2(2000);
71 l_msg_count_qoh NUMBER;
72 l_msg_data_qoh VARCHAR2(2000);
73 l_rqoh NUMBER;
74 l_qr NUMBER;
75 l_qs NUMBER;
76 l_att NUMBER;
77 l_atr NUMBER;
78 x_wip_ret_status Varchar2(200);
79 x_error_mssg1 Varchar2(200);
80 l_eam_one_step_mat_issue varchar2(1);
81 l_rebuild_item_id number := null;
82 l_rebuild_activity_id number := null;
83 l_rebuild_job_name mtl_transactions_interface.rebuild_job_name%type;
84 l_rebuild_job_temp Number;
85 l_prefix wip_eam_parameters.easy_work_order_prefix%type;
86 l_lot_number Varchar2(80);
87 l_fm_serial_number Varchar2(30);
88 l_to_serial_number Varchar2(30);
89 l_tx_hdr_id number := null;
90 l_tx_count number := 0;
91 l_txmgr_ret_code number := -1;
92 l_num_valid_serials number;
93 l_num_range_serials number;
94 l_material mtl_system_items_b_kfv.concatenated_segments%TYPE;
95 l_neg_inv_receipt_code number;
96 l_within_open_period varchar2(1):= 'N';
97 l_inventory_item_id_wl Number; --8667921/8661513 to derive the inventory_item_id for wireless application
98 l_source_locator_wl Number; --8667921/8661513 to derive the inventory_item_id for wireless application
99 l_rebuild_item_id_wl Number; --8667921/8661513 to derive the inventory_item_id for wireless application
100 l_sec_sta_valid boolean;
101 BEGIN
102
103 --derive the inventory_item_id in case of wireless (for bug 8661513)
104 IF (p_inventory_item is not null and p_inventory_item_id is null) THEN
105 select Inventory_item_id into l_inventory_item_id_wl
106 from mtl_system_items
107 where SEGMENT1 = p_inventory_item
108 and ORGANIZATION_ID = p_organization_id;
109 END IF;
110
111 --derive the locator_id (p_source_locator) in case of wireless--for bug 8661513
112 IF(p_locator_name is not null and p_source_locator is null) THEN
113 SELECT inventory_location_id into l_source_locator_wl
114 FROM mtl_item_locations_kfv
115 WHERE organization_id = p_organization_id
116 AND subInventory_code = p_source_subinventory
117 AND NVL(disable_date,TRUNC(sysdate)+1) > TRUNC(sysdate)
118 AND concatenated_segments = p_locator_name;
119 END IF; -- end of deriving locator id
120
121
122 --derive the rebuild_item_id (p_rebuild_item_id) in case of wireless--for bug 8661513
123 IF(p_rebuild_item_name is not null and p_rebuild_item_id is null) THEN
124 select Inventory_item_id into l_rebuild_item_id_wl
125 from mtl_system_items
126 where SEGMENT1 = p_rebuild_item_name
127 and ORGANIZATION_ID = p_organization_id;
128 END IF; -- end of rebuild id
129
130 SAVEPOINT fork_logic;
131 -- Standard call to check for call compatibility.
132 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
133 l_api_name, G_PKG_NAME ) THEN
134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
135 END IF;
136 -- Initialize message list if p_init_msg_list is set to TRUE.
137 IF FND_API.to_Boolean( p_init_msg_list ) THEN
138 FND_MSG_PUB.initialize;
139 END IF;
140 -- Initialize API return status to success
141 x_return_status := FND_API.G_RET_STS_SUCCESS;
142
143 -- API body
144 if (l_pLog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
145 'Start of ' || l_full_name || '('
146 || 'p_commit='|| p_commit ||','
147 || 'p_wip_entity_type='|| p_wip_entity_type || ','
148 || 'p_organization_id='|| p_organization_id || ','
149 || 'p_wip_entity_id='|| p_wip_entity_id || ','
150 || 'p_operation_seq_num='|| p_operation_seq_num || ','
151 || 'p_inventory_item_id='|| p_inventory_item_id || ','
152 || 'p_reasons='|| p_reasons || ','
153 || 'p_reference='|| p_reference || ','
154 || 'p_date='|| p_date || ','
155 || 'p_rebuild_item_id='|| p_rebuild_item_id || ','
156 || 'p_rebuild_item_name='|| p_rebuild_item_name || ','
157 || 'p_rebuild_serial_number='|| p_rebuild_serial_number || ','
158 || 'p_rebuild_job_name='|| p_rebuild_job_name || ','
159 || 'p_rebuild_activity_id='|| p_rebuild_activity_id || ','
160 || 'p_rebuild_activity_name='|| p_rebuild_activity_name || ','
161 || 'p_user_id='|| p_user_id || ','
162 || 'p_requested_quantity='|| p_requested_quantity || ','
163 || 'p_source_subinventory='|| p_source_subinventory ||','
164 || 'p_source_locator='|| p_source_locator ||','
165 || 'p_lot_number='|| p_lot_number ||','
166 || 'p_fm_serial='|| p_fm_serial ||','
167 || 'p_to_serial='|| p_to_serial ||','
168 || ')');
169 end if;
170
171 select nvl(material_issue_by_mo,'Y'), project_id, task_id
172 into l_material_issue_by_mo, l_project_id, l_task_id
173 from wip_discrete_jobs where
174 wip_entity_id=p_wip_entity_id
175 and organization_id=p_organization_id;
176 if (l_sLog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
177 'l_material_issue_by_mo=' || l_material_issue_by_mo);
178 end if;
179 if (l_project_id is null) then l_project_id := fnd_api.G_MISS_NUM;
180 end if;
181 if (l_task_id is null) then l_task_id := fnd_api.G_MISS_NUM;
182 end if;
183 if (l_material_issue_by_mo = 'N') then
184 l_issue_by_mo := false;
185 end if;
186
187 --selecting the item name,UOM,serial_control_code
188 if (l_sLog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
189 'Getting serial, lot control codes');
190 end if;
191 --fix for 3454251.added code to fetch material name
192 select primary_uom_code,
193 serial_number_control_code ,
194 lot_control_code,
195 concatenated_segments,
196 REVISION_QTY_CONTROL_CODE
197 into
198 l_primary_uom_code ,
199 l_serial_number_control_code,
200 l_lot_control_code,
201 l_material,
202 l_revision_qty_control_code
203 from mtl_system_items_b_kfv
204 where inventory_item_id= nvl(p_inventory_item_id,l_inventory_item_id_wl) --bug 8661513
205 and organization_id=p_organization_id;
206
207 l_inventory_item_id:= nvl(p_inventory_item_id,l_inventory_item_id_wl); --bug 8661513
208 l_organization_id:=p_organization_id;
209 l_source_subinventory:=p_source_subinventory;
210 -- Added to fix bug# 3579816
211 l_source_locator_id := to_number(nvl(p_source_locator,l_source_locator_wl));--bug 8661513
212
213 l_lot_number:=p_lot_number;
214 l_fm_serial_number:=p_fm_serial;
215 l_to_serial_number:=p_to_serial;
216
217 --if item is not lot controlled then
218 --set lot number to null value
219 if (l_lot_control_code=1) then --(2)
220 l_lot_number:=null;
221 l_is_lot_control:=FALSE;
222 ELSE
223 l_is_lot_control:=TRUE;
224 end if; ---(2)
225
226 --start for bug 11669073
227 IF(l_serial_number_control_code=1) THEN
228 l_is_serial_control :=FALSE;
229 ELSE
230 l_is_serial_control:= TRUE;
231 END IF;
232
233 IF(l_revision_qty_control_code=1) THEN
234 l_is_revision_control:=FALSE;
235 ELSE
236 l_is_revision_control:=TRUE;
237 END IF;
238
239
240 BEGIN
241
242 inv_quantity_tree_pub.query_quantities(
243 p_api_version_number =>1.0,
244 p_organization_id=>p_organization_id,
245 p_inventory_item_id=>nvl(p_inventory_item_id,l_inventory_item_id_wl),
246 p_tree_mode=>inv_quantity_tree_pub.g_transaction_mode,
247 p_is_revision_control=>l_is_revision_control,
248 p_is_lot_control=>l_is_lot_control,
249 p_is_serial_control=>l_is_serial_control,
250 p_revision=> p_revision,
251 p_lot_number=> p_lot_number,
252 p_subinventory_code=> p_source_subinventory,
253 p_locator_id=> l_source_locator_id,
254 x_qoh=> l_onhand_quantity,
255 x_rqoh=>l_rqoh,
256 x_qr =>l_qr,
257 x_qs =>l_qs,
258 x_att =>l_att,
259 x_atr =>l_atr,
260 x_return_status=> l_ret_status_qoh,
261 x_msg_count=> l_msg_count_qoh,
262 x_msg_data =>l_msg_data_qoh
263 );
264
265 IF(l_ret_status_qoh = FND_API.G_RET_STS_UNEXP_ERROR OR
266 l_ret_status_qoh = FND_API.G_RET_STS_ERROR
267 ) THEN
268
269 x_return_status := FND_API.G_RET_STS_ERROR;
270 fnd_message.set_name('EAM', 'EAM_INV_ONHAND_NOT_DETERMINED');
271 fnd_message.set_token('MATERIAL',l_material);
272 fnd_msg_pub.add;
273 fnd_msg_pub.Count_And_Get(
274 p_count => x_msg_count,
275 p_data => x_msg_data);
276 return;
277
278
279 END IF;
280
281
282 exception
283 when others then
284 x_return_status := fnd_api.g_ret_sts_unexp_error;
285
286 END;
287
288
289
290 IF(l_onhand_quantity IS NULL) THEN
291 l_onhand_quantity:=0;
292 END IF;
293
294
295 select NEGATIVE_INV_RECEIPT_CODE
296 into l_neg_inv_receipt_code
297 from mtl_parameters
298 where organization_id = p_organization_id;
299
300 IF l_neg_inv_receipt_code = 2 THEN
301
302 IF (p_requested_quantity > l_onhand_quantity) THEN
303
304 x_return_status := FND_API.G_RET_STS_ERROR;
305 fnd_message.set_name('EAM', 'EAM_INV_QUANTITY_NEGATIVE');
306 fnd_message.set_token('OPERATION',p_operation_seq_num);
307 fnd_message.set_token('MATERIAL',l_material);
308 fnd_message.set_token('ONHAND',l_onhand_quantity);
309 fnd_msg_pub.add;
310 fnd_msg_pub.Count_And_Get(
311 p_count => x_msg_count,
312 p_data => x_msg_data);
313 return;
314
315 END IF;
316 END IF;
317
318
319 BEGIN
320 select
321 'Y'
322 into l_within_open_period
323 FROM
324 org_acct_periods
325 WHERE
326 organization_id=p_organization_id
327 and period_close_date is null
328 and p_date between
329 period_start_date and schedule_close_date + 1 - (1/(24*3600));
330 EXCEPTION
331 WHEN NO_DATA_FOUND THEN
332 l_within_open_period:='N';
333 END;
334
335 IF (l_within_open_period<>'Y') THEN
336 x_return_status := FND_API.G_RET_STS_ERROR;
337 fnd_message.set_name('EAM', 'EAM_ISSUE_TXN_NOT_OPEN_PERIOD');
338 fnd_message.set_token('OPERATION',p_operation_seq_num);
339 fnd_message.set_token('MATERIAL',l_material);
340 fnd_msg_pub.add;
341 fnd_msg_pub.Count_And_Get(
342 p_count => x_msg_count,
343 p_data => x_msg_data);
344 return;
345 END IF;
346
347 --end for bug 11669073
348
349 --fix for security validation rules..Bug 8649230
350
351 IF(l_source_locator_id IS NOT NULL) THEN
352 l_sec_sta_valid := fnd_flex_keyval.validate_ccid(
353 appl_short_name => 'INV'
354 , key_flex_code => 'MTLL'
355 , structure_number => 101
356 , combination_id => l_source_locator_id--to_number(name_in('MTL_TRX_LINE.LOCATOR_ID'))
357 , displayable => 'ALL'
358 , data_set => p_organization_id--to_number(name_in('PARAMETER.ORG_ID'))
359 , vrule => NULL
360 , security => 'ENFORCE'--'IGNORE'
361 , get_columns => NULL
362 , resp_appl_id => NULL
363 , resp_id => NULL
364 , user_id => NULL
365 );
366
367 IF NOT (l_sec_sta_valid ) THEN
368 x_return_status := FND_API.G_RET_STS_ERROR;
369 fnd_message.set_name('EAM', 'EAM_INV_SEC_LOC_RULE');
370 fnd_message.set_token('OPERATION',p_operation_seq_num);
371 fnd_message.set_token('MATERIAL',l_material);
372 fnd_message.set_token('LOCATOR',l_source_locator_id);
373 fnd_msg_pub.add;
374 fnd_msg_pub.Count_And_Get(
375 p_count => x_msg_count,
376 p_data => x_msg_data);
377 return;
378 END IF;
379 END IF;
380
381 --fix for 3454251.raise an error message if quantity to be issued is negative or zero
382
383 if(p_requested_quantity<=0) then
384 x_return_status := FND_API.G_RET_STS_ERROR;
385 fnd_message.set_name('EAM', 'EAM_REQUESTED_QUAN_NEG_ZERO');
386 fnd_message.set_token('OPERATION',p_operation_seq_num);
387 fnd_message.set_token('MATERIAL',l_material);
388 fnd_msg_pub.add;
389 fnd_msg_pub.Count_And_Get(
390 p_count => x_msg_count,
391 p_data => x_msg_data);
392 return;
393 end if;
394 --if the transaction date is in future raise an error message
395 if(p_date > sysdate) then
396 x_return_status := FND_API.G_RET_STS_ERROR;
397 fnd_message.set_name('EAM', 'EAM_TRANS_DATE_FUTURE');
398 fnd_message.set_token('OPERATION',p_operation_seq_num);
399 fnd_message.set_token('MATERIAL',l_material);
400 fnd_msg_pub.add;
401 fnd_msg_pub.Count_And_Get(
402 p_count => x_msg_count,
403 p_data => x_msg_data);
404 return;
405 end if;
406
407 --if item is not serial controlled then
408 --set serial numbers to be null
409
410 if (l_serial_number_control_code=1) then --(3)
411 l_fm_serial_number:=null;
412 l_to_serial_number:=null;
413 else
414 l_num_range_serials := inv_serial_number_pub.get_serial_diff(
415 l_fm_serial_number, l_to_serial_number);
416 if (l_num_range_serials <> p_requested_quantity) then
417 x_return_status := FND_API.G_RET_STS_ERROR;
418 fnd_message.set_name('EAM', 'EAM_SERIAL_RANGE_QTY_MISMATCH');
419 fnd_message.set_token('OPERATION',p_operation_seq_num);
420 fnd_message.set_token('MATERIAL',l_material);
421 fnd_message.set_token('QTY_RANGE',l_num_range_serials);
422 fnd_message.set_token('FM_SERIAL',l_fm_serial_number);
423 fnd_message.set_token('TO_SERIAL',l_to_serial_number);
424 fnd_message.set_token('QTY_ENTERED',p_requested_quantity);
425 fnd_msg_pub.add;
426 fnd_msg_pub.Count_And_Get(
427 p_count => x_msg_count,
428 p_data => x_msg_data);
429 return;
430 end if;
431
432
433
434 SELECT COUNT(serial_number)
435 INTO l_num_valid_serials
436 FROM mtl_serial_numbers msn
437 WHERE msn.inventory_item_id = l_inventory_item_id
438 and msn.current_organization_id = l_organization_id
439 and msn.current_subinventory_code = l_source_subinventory
440 and (msn.group_mark_id is null or msn.group_mark_id = -1)
441 and (msn.revision is null or msn.revision = p_revision)
442 and (msn.lot_number is null or msn.lot_number = l_lot_number)
443 and msn.current_status=3
444 AND msn.serial_number between l_fm_serial_number and l_to_serial_number
445 AND LENGTH(msn.serial_number) = LENGTH(l_fm_serial_number);
446
447 if (l_num_valid_serials <> p_requested_quantity) then
448 x_return_status := FND_API.G_RET_STS_ERROR;
449 fnd_message.set_name('EAM', 'EAM_N_SERIALS_UNAVAILABLE');
450 fnd_message.set_token('OPERATION',p_operation_seq_num);
451 fnd_message.set_token('MATERIAL',l_material);
452 fnd_message.set_token('NUM_UNAVAILABLE', p_requested_quantity - l_num_valid_serials);
453 fnd_msg_pub.add;
454 fnd_msg_pub.Count_And_Get(
455 p_count => x_msg_count,
456 p_data => x_msg_data);
457 return;
458 end if;
459 end if; --(3) of if (l_serial_number_control_code=1)
460
461
462
463 begin ----------------(1)
464 --quantity signed is reversed when values are inserted in MTI
465 l_quantity:=-1*p_requested_quantity;
466 begin
467 if (l_sLog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
468 'Calling PROCESSMTLTXN');
469 end if;
470 savepoint before_insert_mti;
471 --inserting values into MTI Table
472
473 eam_mtl_txn_process.PROCESSMTLTXN(
474 p_txn_header_id =>NULL,--can be null
475 p_item_id =>l_inventory_item_id,
476 p_item => l_inventory_item_name,--concatenated_segment
477 p_revision => p_revision,
478 p_org_id =>l_organization_id,
479 p_trx_action_id => 1,-- issue from inventory to wip
480 p_subinv_code =>l_source_subinventory ,
481 p_tosubinv_code => NULL,
482 p_locator_id =>l_source_locator_id,
483 p_locator => NULL,
484 p_tolocator_id =>Null,
485 p_trx_type_id =>35,
486 p_trx_src_type_id =>5,
487 p_trx_qty => l_quantity,
488 p_pri_qty => l_quantity ,
489 p_uom => l_primary_uom_code,
490 p_date => p_date,
491 p_reason_id =>l_reason_id,
492 p_reason => p_reasons,
493 p_user_id =>p_user_id ,
494 p_trx_src_id =>p_wip_entity_id,
495 x_trx_temp_id =>x_tmp_id ,
496 p_operation_seq_num =>p_operation_seq_num,
497 p_wip_entity_type =>wip_constants.eam,
498 p_trx_reference =>p_reference,
499 p_negative_req_flag =>1,
500 p_serial_ctrl_code =>l_serial_number_control_code,--1
501 p_lot_ctrl_code => l_lot_control_code,--1
502 p_from_ser_number =>l_fm_serial_number,
503 P_to_ser_number =>l_to_serial_number,
504 p_lot_num =>l_lot_number,
505 p_wip_supply_type =>1,
506 p_subinv_ctrl =>null,
507 p_locator_ctrl =>null,
508 p_wip_process =>0, -- determines to call WIP Transaction API
509 -- 0 -> No call,1 -> Call
510 p_dateNotInFuture =>1, -- 1 --> do check,0 --> no check
511 x_error_flag =>x_err_flag, -- returns 0 if no error , >1 if any error .
512 x_error_mssg =>x_error_msg );
513
514
515 if (x_err_flag = 1) then
516 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => x_error_msg);
517 x_return_status := FND_API.G_RET_STS_ERROR;
518 return;
519 elsif (x_err_flag = 2) then
520 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_CANNOT_DELETE_RESOURCE',
521 p_token1=>'EAM_RET_MAT_PROCESS_MESSAGE', p_value1=>x_error_msg);
522 x_return_status := FND_API.G_RET_STS_ERROR;
523 return;
524 end if; -- end of x_error_flag check
525
526 exception
527 when others then --some unhandled exception occurred. rollback everything.
528 x_return_status := fnd_api.g_ret_sts_unexp_error;
529 rollback to before_insert_mti;
530 return ;
531 end;
532
533 -- dbms_output.put_line('transaction_interface_id ' || x_tmp_id);
534 --dbms_output.put_line('x_error_flag ' || x_err_flag);
535 --dbms_output.put_line('x-error_mssg ' || x_error_msg);
536
537 if ((x_tmp_id is not null)) then
538 begin
539 if ((nvl(p_rebuild_item_id,l_rebuild_item_id_wl) is not null) or (p_rebuild_item_name is not null)) then --changed for bug 8661513
540 begin
541 l_rebuild_item_id := nvl(p_rebuild_item_id,l_rebuild_item_id_wl); --changed for bug 8661513
542 if (nvl(p_rebuild_item_id,l_rebuild_item_id_wl) is null) then --changed for bug 8661513
543 select msi.inventory_item_id into l_rebuild_item_id
544 from mtl_system_items_b_kfv msi, mtl_parameters mp
545 where concatenated_segments = p_rebuild_item_name
546 and msi.organization_id = mp.organization_id
547 and mp.maint_organization_id = p_organization_id
548 and eam_item_type = 3 --3 for rebuild
549 and rownum = 1;
550 end if;
551 if (l_sLog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
552 'Inserting rebuild item id: '|| l_rebuild_item_id);
553 end if;
554 update mtl_transactions_interface
555 set rebuild_item_id=l_rebuild_item_id
556 where transaction_interface_id= x_tmp_id;
557 end;
558 end if;
559
560 if (p_rebuild_serial_number is not null) then
561 begin
562 --dbms_output.put_line('p_rebuild_serial_number ' || p_rebuild_serial_number);
563 update mtl_transactions_interface
564 set rebuild_serial_number=p_rebuild_serial_number
565 where transaction_interface_id= x_tmp_id;
566 end;
567 end if;
568
569 if ((p_rebuild_activity_id is not null) or (p_rebuild_activity_name is not null)) then
570 begin
571 l_rebuild_activity_id := p_rebuild_activity_id;
572 if (p_rebuild_activity_id is null) then -- activity name must be non null
573 select inventory_item_id into l_rebuild_activity_id
574 from mtl_system_items_b_kfv
575 where concatenated_segments = p_rebuild_activity_name
576 and organization_id = p_organization_id
577 and eam_item_type = 2; --2 for activity
578 end if;
579 if (l_sLog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
580 'Inserting rebuild activity id: '|| l_rebuild_activity_id);
581 end if;
582 update mtl_transactions_interface
583 set rebuild_activity_id=l_rebuild_activity_id
584 where transaction_interface_id= x_tmp_id;
585 end;
586 end if;
587
588 if (l_sLog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
589 'inserting rebuild job');
590 end if;
591 if ((p_rebuild_job_name is null) and (l_rebuild_item_id is not null))then
592
593 /* BUG#2988552 wip_job_number_s sequence is to be used for work order name*/
594 SELECT
595 wip_job_number_s.nextval INTO l_rebuild_job_temp
596 FROM
597 DUAL;
598
599 l_rebuild_job_name:= l_rebuild_job_temp ;
600 else
601 l_rebuild_job_name := p_rebuild_job_name;
602 end if;
603
604 p_rebuild_job_name := l_rebuild_job_name;
605
606 --dbms_output.put_line('p_rebuild_job_name ' || l_rebuild_job_name);
607 update mtl_transactions_interface
608 set rebuild_job_name=l_rebuild_job_name
609 where transaction_interface_id= x_tmp_id;
610
611 if (p_reference is not null) then
612 begin
613 update mtl_transactions_interface
614 set transaction_reference=p_reference
615 where transaction_interface_id= x_tmp_id;
616 end;
617 end if;
618
619 if (l_reason_id is not null) then
620 begin
621 --dbms_output.put_line('l_reason_id ' || l_reason_id);
622 update mtl_transactions_interface
623 set reason_id=l_reason_id
624 where transaction_interface_id= x_tmp_id;
625 end;
626 end if;
627
628 select transaction_header_id into l_tx_hdr_id
629 from mtl_transactions_interface
630 where transaction_interface_id = x_tmp_id;
631
632
633 -- only call txn processor if online processing. 4(form level) is treated as 1.
634 if (EAM_MATERIALISSUE_PVT.get_tx_processor_mode() in (1,4)) then
635 if (l_sLog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
636 'Calling INV_TXN_MANAGER_PUB.process_Transactions');
637 end if;
638 l_txmgr_ret_code := INV_TXN_MANAGER_PUB.process_Transactions(
639 p_api_version => 1.0,
640 p_header_id => l_tx_hdr_id,
641 p_table => 1, -- 1 for MTI, 2 for MMTT
642 x_return_status => x_wip_ret_status,
643 x_msg_count => x_msg_count,
644 x_trans_count => l_tx_count,
645 x_msg_data => x_error_mssg1);
646 if (l_sLog) then
647 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
648 'INV_TXN_MANAGER_PUB finished with return code='|| l_txmgr_ret_code);
649 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
650 'INV_TXN_MANAGER_PUB finished with return status='|| x_wip_ret_status);
651 end if;
652
653 if(x_wip_ret_status = FND_API.G_RET_STS_UNEXP_ERROR OR
654 x_wip_ret_status = FND_API.G_RET_STS_ERROR
655 ) then
656
657 BEGIN
658 SELECT error_explanation into x_error_mssg1
659 FROM mtl_transactions_interface
660 WHERE TRANSACTION_header_id = l_tx_hdr_id ;
661 EXCEPTION
662 WHEN others THEN
663 x_error_mssg1:='Error from Inventory transaction manager';
664 END;
665
666 rollback to before_insert_mti;
667 x_return_status := x_wip_ret_status;
668 if x_error_mssg1 is not null then
669 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_WO_API_MESG',
670 p_token1=>'MESG', p_value1=>x_error_mssg1);
671 end if;
672 return;
673 --fix for 3454251.error out even if the return code is -1
674 elsif(l_txmgr_ret_code=-1) then
675 BEGIN
676 SELECT error_explanation into x_error_mssg1
677 FROM mtl_transactions_interface
678 WHERE TRANSACTION_header_id = l_tx_hdr_id ;
679 EXCEPTION
680 WHEN others THEN
681 x_error_mssg1:='Error from Inventory transaction manager';
682 END;
683 rollback to before_insert_mti;
684 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
685 if x_error_mssg1 is not null then
686 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_WO_API_MESG',
687 p_token1=>'MESG', p_value1=>x_error_mssg1);
688 end if;
689 return;
690 end if;
691
692 end if; -- of if tx process mode is 1
693
694
695 end;
696
697 end if;
698
699 end;-----{1}
700
701 if (l_pLog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
702 'End of ' || l_full_name );
703 end if;
704 -- End of API body.
705
706 -- Standard check of p_commit.
707 IF FND_API.To_Boolean( p_commit )
708 AND x_return_status = FND_API.g_RET_STS_SUCCESS THEN
709 COMMIT WORK;
710 END IF;
711 -- Standard call to get message count and if count is 1, get message info.
712 FND_MSG_PUB.Count_And_Get(
713 p_count => x_msg_count,
714 p_data => x_msg_data);
715 EXCEPTION
716 WHEN OTHERS THEN
717 ROLLBACK TO fork_logic;
718 if (l_log) then
719 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module, 'Exception occured' );
720 end if;
721 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
722 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
723 THEN
724 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
725 END IF;
726 FND_MSG_PUB.Count_And_Get(
727 p_count => x_msg_count,
728 p_data => x_msg_data);
729
730 end Fork_Logic;
731
732
733 PROCEDURE process_mmtt(
734 p_api_version IN NUMBER,
735 p_init_msg_list IN VARCHAR2,
736 p_commit IN VARCHAR2,
737 p_validation_level IN NUMBER,
738 x_return_status OUT NOCOPY VARCHAR2,
739 x_msg_count OUT NOCOPY NUMBER,
740 x_msg_data OUT NOCOPY VARCHAR2,
741 p_trx_tmp_id IN NUMBER) IS
742
743 l_api_name CONSTANT VARCHAR2(30) := 'process_mmtt';
744 l_api_version CONSTANT NUMBER := 1.0;
745 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
746 l_module CONSTANT VARCHAR2(60) := 'eam.plsql.'||l_full_name;
747 l_current_log_level constant number := FND_LOG.G_CURRENT_RUNTIME_LEVEL ;
748 l_log boolean := FND_LOG.LEVEL_UNEXPECTED >= l_current_log_level ;
749 l_plog boolean := l_log and FND_LOG.LEVEL_PROCEDURE >= l_current_log_level ;
750 l_slog boolean := l_plog and FND_LOG.LEVEL_STATEMENT >= l_current_log_level ;
751
752 l_msg_data VARCHAR2(50);
753 l_return_status VARCHAR2(2000);
754 l_header_id number := null;
755 NO_ACCT_PERIOD_EXC EXCEPTION; -- Added for bug 4041420
756 l_acct_period_id number := null; -- Added for bug 4041420
757
758 BEGIN
759 if (l_pLog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
760 'Start of ' || l_full_name || '('
761 || 'p_init_msg_list='|| p_init_msg_list ||','
762 || 'p_commit='|| p_commit ||','
763 || 'p_trx_tmp_id='|| to_number(p_trx_tmp_id) || ','
764 || ')');
765 end if;
766 -- Standard Start of API savepoint
767 SAVEPOINT PROCESS_MMTT;
768 -- Standard call to check for call compatibility.
769 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
770 l_api_name, G_PKG_NAME )
771 THEN
772 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
773 END IF;
774 -- Initialize message list if p_init_msg_list is set to TRUE.
775 IF FND_API.to_Boolean( p_init_msg_list ) THEN
776 FND_MSG_PUB.initialize;
777 END IF;
778 -- Initialize API return status to success
779 x_return_status := FND_API.G_RET_STS_SUCCESS;
780
781 -- API body
782 l_msg_data := NULL;
783 l_return_status := NULL;
784
785 /* Added for bug 4041420 - Start */
786 SELECT NVL(max(oap.acct_period_id), -1)
787 INTO l_acct_period_id
788 FROM org_acct_periods oap,
789 mtl_material_transactions_temp mmtt
790 WHERE mmtt.transaction_temp_id = p_trx_tmp_id
791 AND oap.organization_id = mmtt.organization_id
792 AND oap.open_flag = 'Y'
793 AND trunc(SYSDATE)
794 BETWEEN trunc(oap.period_start_date) AND
795 trunc(oap.schedule_close_date);
796
797 IF (l_acct_period_id = -1) THEN
798 raise NO_ACCT_PERIOD_EXC;
799 END IF;
800
801 UPDATE mtl_material_transactions_temp
802 SET transaction_date = SYSDATE,
803 acct_period_id = l_acct_period_id
804 where transaction_temp_id = p_trx_tmp_id;
805 /* Added for bug 4041420 - End */
806
807 select mtl_material_transactions_s.nextval into l_header_id from dual;
808 update mtl_material_transactions_temp
809 set transaction_header_id = l_header_id,
810 transaction_status = null, --Added since WIP is no longer doing this in 11.5.10
811 primary_quantity = -1* primary_quantity,
812 transaction_quantity = -1* transaction_quantity
813 where transaction_temp_id = p_trx_tmp_id;
814 if (l_slog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
815 'Calling wip_mtlTempProc_grp.processTemp. p_txnHdrID=' || l_header_id);
816 end if;
817 wip_mtlTempProc_grp.processTemp(
818 p_initMsgList => fnd_api.G_TRUE,
819 p_processInv => fnd_api.G_TRUE, --whether or not to call inventory TM
820 p_txnHdrID => l_header_id,
821 x_returnStatus => x_return_status,
822 x_errorMsg => x_msg_data);
823 if (l_slog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
824 'wip_mtlTempProc_grp.processTemp returned. x_returnStatus='||x_return_status
825 ||', x_errorMsg=' || REPLACE(x_msg_data, CHR(0), ' '));
826 end if;
827 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
828 ROLLBACK TO PROCESS_MMTT;
829 /* Fix for bug no :2719414 */
830 if(x_msg_data is not null) then
831 x_msg_count := 1 ;
832 if FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) then
833 -- FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
834 FND_MESSAGE.SET_NAME('EAM','EAM_WIP_PROCESSOR_MSG');
835 FND_MESSAGE.SET_TOKEN('WIPMSG',x_msg_data);
836 FND_MSG_PUB.ADD;
837 end if;
838 end if;
839 /* end of fix for bug no:2719414 */
840 FND_MSG_PUB.Count_And_Get('T', x_msg_count, x_msg_data);
841 return;
842 end if;
843 if (l_pLog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'End');
844 end if;
845 -- End of API body.
846
847 -- Standard check of p_commit.
848 IF FND_API.To_Boolean( p_commit ) THEN
849 COMMIT WORK;
850 END IF;
851 -- Standard call to get message count and if count is 1, get message info.
852 FND_MSG_PUB.Count_And_Get(
853 p_count => x_msg_count,
854 p_data => x_msg_data);
855 EXCEPTION
856 /* Added for bug 4041420 - Start */
857 WHEN NO_ACCT_PERIOD_EXC THEN
858 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_TRANSACTION_DATE_INVALID');
859 x_return_status := FND_API.G_RET_STS_ERROR;
860 /* Added for bug 4041420 - End */
861 WHEN OTHERS THEN
862 ROLLBACK TO PROCESS_MMTT;
863 if (l_log) then
864 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module, 'Exception occured');
865 end if;
866 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
867 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
868 THEN
869 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
870 END IF;
871 FND_MSG_PUB.Count_And_Get(
872 p_count => x_msg_count,
873 p_data => x_msg_data);
874 END process_mmtt;
875
876 PROCEDURE insert_ser_trx(p_trx_tmp_id IN VARCHAR2,
877 p_serial_trx_tmp_id IN NUMBER,
878 p_trx_header_id IN NUMBER,
879 p_user_id IN NUMBER,
880 p_fm_ser_num IN VARCHAR2,
881 p_to_ser_num IN VARCHAR2,
882 p_item_id IN NUMBER,
883 p_org_id IN NUMBER,
884 x_err_code OUT NOCOPY NUMBER,
885 x_err_message OUT NOCOPY VARCHAR2) IS
886 BEGIN
887
888 x_err_code := inv_trx_util_pub.insert_ser_trx(
889 p_trx_tmp_id => p_serial_trx_tmp_id,
890 p_user_id => p_user_id,
891 p_fm_ser_num => p_fm_ser_num,
892 p_to_ser_num => p_to_ser_num,
893 x_proc_msg => x_err_message);
894
895 if (x_err_code = 0) then
896 serial_check.inv_mark_serial(
897 from_serial_number => p_fm_ser_num,
898 to_serial_number => p_to_ser_num,
899 item_id => p_item_id,
900 org_id => p_org_id,
901 hdr_id => p_trx_header_id,
902 temp_id => p_trx_tmp_id,
903 lot_temp_id => p_serial_trx_tmp_id,
904 success => x_err_code);
905 end if;
906
907 END insert_ser_trx;
908
909
910 PROCEDURE INSERT_REASON_REF_INTO_MMTT(l_reason_id IN Number,
911 p_reference IN varchar2,
912 p_transaction_temp_id In Number) IS
913
914 begin
915
916 if ((l_reason_id is not null) and (p_reference is not null) ) then
917 update mtl_material_transactions_temp
918 set reason_id=l_reason_id,
919 transaction_reference=p_reference
920 where transaction_temp_id=p_transaction_temp_id;
921
922 elsif ((l_reason_id is null) and (p_reference is not null) ) then
923
924 update mtl_material_transactions_temp
925 set transaction_reference=p_reference
926 where transaction_temp_id=p_transaction_temp_id;
927
928 elsif ((l_reason_id is not null) and (p_reference is null) ) then
929
930 update mtl_material_transactions_temp
931 set reason_id=l_reason_id
932 where transaction_temp_id=p_transaction_temp_id;
933
934 end if;
935 END INSERT_REASON_REF_INTO_MMTT;
936
937 ---Entering the rebuild details
938 PROCEDURE ENTER_REBUILD_DETAILS(p_rebuild_item_id IN Number,
939 p_rebuild_job_name IN OUT NOCOPY Varchar2,
940 p_rebuild_activity_id IN Number,
941 p_rebuild_serial_number IN varchar2,
942 P_transaction_temp_id IN Number,
943 p_organization_id IN Number)
944
945 is
946 l_rebuild_job_name mtl_material_transactions_temp.rebuild_job_name%type;
947 l_rebuild_job_temp Number;
948 begin
949
950 --the program will work if users transact rebuild-item-id
951 --with the transactions qty =1
952 --this program will produce abug when users will is tarsnacting more than one rebuild-item
953
954 if (p_rebuild_job_name is null) then
955
956 /* BUG#2988552 wip_job_number_s sequence is to be used for work order name*/
957 SELECT
958 wip_job_number_s.nextval INTO l_rebuild_job_temp
959 FROM
960 DUAL;
961
962 l_rebuild_job_name:= l_rebuild_job_temp ;
963 else
964 l_rebuild_job_name :=p_rebuild_job_name;
965 end if;
966
967 p_rebuild_job_name := l_rebuild_job_name; --set the output variable
968
969 if ((p_rebuild_activity_id is not null) and (p_rebuild_serial_number is not null))
970 then
971
972 update mtl_material_transactions_temp
973 set rebuild_item_id=p_rebuild_item_id,
974 rebuild_job_name =l_rebuild_job_name,
975 rebuild_activity_id=p_rebuild_activity_id,
976 rebuild_serial_number=p_rebuild_serial_number
977 where transaction_temp_id=p_transaction_temp_id;
978
979 elsif ((p_rebuild_activity_id is null) and (p_rebuild_serial_number is not null)) then
980
981 update mtl_material_transactions_temp
982 set rebuild_item_id=p_rebuild_item_id,
983 rebuild_job_name =l_rebuild_job_name,
984 rebuild_serial_number=p_rebuild_serial_number
985 where transaction_temp_id=p_transaction_temp_id;
986 elsif ((p_rebuild_activity_id is not null) and (p_rebuild_serial_number is null)) then
987
988 update mtl_material_transactions_temp
989 set rebuild_item_id=p_rebuild_item_id,
990 rebuild_job_name =l_rebuild_job_name,
991 rebuild_activity_id=p_rebuild_activity_id
992 where transaction_temp_id=p_transaction_temp_id;
993 elsif ((p_rebuild_activity_id is null) and (p_rebuild_serial_number is null)) then
994
995 update mtl_material_transactions_temp
996 set rebuild_item_id=p_rebuild_item_id,
997 rebuild_job_name =l_rebuild_job_name
998 where transaction_temp_id=p_transaction_temp_id;
999
1000 end if;
1001
1002
1003 end ENTER_REBUILD_DETAILS;
1004
1005
1006 -- Procedure to cancel allocations if a material is deleted
1007 -- Author : amondal
1008
1009 PROCEDURE cancel_alloc_matl_del (p_api_version IN NUMBER,
1010 p_init_msg_list IN VARCHAR2 ,
1011 p_commit IN VARCHAR2,
1012 p_validation_level IN NUMBER,
1013 p_wip_entity_id IN NUMBER,
1014 p_operation_seq_num IN NUMBER,
1015 p_inventory_item_id IN NUMBER,
1016 p_wip_entity_type IN NUMBER,
1017 p_repetitive_schedule_id IN NUMBER DEFAULT NULL,
1018 x_return_status OUT NOCOPY VARCHAR2,
1019 x_msg_data OUT NOCOPY VARCHAR2,
1020 x_msg_count OUT NOCOPY NUMBER) IS
1021
1022 l_api_name CONSTANT VARCHAR2(30) := 'cancel_alloc_matl_del';
1023 l_api_version CONSTANT NUMBER := 1.0;
1024 l_wip_entity_id NUMBER;
1025 l_operation_seq_num NUMBER;
1026 l_inventory_item_id NUMBER;
1027 l_return_status VARCHAR2(1);
1028 l_msg_data VARCHAR2(2000);
1029
1030
1031 BEGIN
1032
1033 -- Standard Start of API savepoint
1034
1035 SAVEPOINT cancel_alloc_matl_del;
1036
1037 -- Standard call to check for call compatibility.
1038 IF NOT fnd_api.compatible_api_call(
1039 l_api_version
1040 ,p_api_version
1041 ,l_api_name
1042 ,g_pkg_name) THEN
1043 RAISE fnd_api.g_exc_unexpected_error;
1044 END IF;
1045
1046 -- Initialize message list if p_init_msg_list is set to TRUE.
1047 IF fnd_api.to_boolean(p_init_msg_list) THEN
1048 fnd_msg_pub.initialize;
1049 END IF;
1050
1051 -- Initialize API return status to success
1052 x_return_status := fnd_api.g_ret_sts_success; -- line 892
1053
1054 -- API body
1055
1056
1057
1058 l_wip_entity_id := p_wip_entity_id;
1059 l_operation_seq_num := p_operation_seq_num;
1060 l_inventory_item_id := p_inventory_item_id;
1061
1062 wip_picking_pub.cancel_comp_allocations (p_wip_entity_id => l_wip_entity_id,
1063 p_operation_seq_num => l_operation_seq_num,
1064 p_inventory_item_id => l_inventory_item_id,
1065 p_wip_entity_type => wip_constants.eam,
1066 p_repetitive_schedule_id => NULL,
1067 x_return_status => l_return_status,
1068 x_msg_data => l_msg_data);
1069
1070 x_msg_data := l_msg_data;
1071 x_return_status := l_return_status;
1072
1073 -- End of API body.
1074 -- Standard check of p_commit.
1075
1076 IF fnd_api.to_boolean(p_commit) THEN
1077 COMMIT WORK;
1078 END IF;
1079
1080 -- l_stmt_num := 999;
1081
1082 -- Standard call to get message count and if count is 1, get message info.
1083 fnd_msg_pub.count_and_get(
1084 p_count => x_msg_count,
1085 p_data => x_msg_data);
1086 EXCEPTION
1087 WHEN fnd_api.g_exc_error THEN
1088 ROLLBACK TO cancel_alloc_matl_del;
1089 x_return_status := fnd_api.g_ret_sts_error;
1090 fnd_msg_pub.count_and_get(
1091 -- p_encoded => FND_API.g_false
1092 p_count => x_msg_count
1093 ,p_data => x_msg_data);
1094 WHEN fnd_api.g_exc_unexpected_error THEN
1095 ROLLBACK TO cancel_alloc_matl_del;
1096 x_return_status := fnd_api.g_ret_sts_unexp_error;
1097
1098 fnd_msg_pub.count_and_get(
1099 p_count => x_msg_count
1100 ,p_data => x_msg_data);
1101 WHEN OTHERS THEN
1102 ROLLBACK TO cancel_alloc_matl_del;
1103 x_return_status := fnd_api.g_ret_sts_unexp_error;
1104 IF fnd_msg_pub.check_msg_level(
1105 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1106 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1107 END IF;
1108
1109 fnd_msg_pub.count_and_get(p_count => x_msg_count
1110 ,p_data => x_msg_data);
1111
1112
1113
1114 END cancel_alloc_matl_del;
1115
1116
1117 -- Procedure to cancel allocations if required quantity for a material is decreased
1118 -- Procedure to create allocations if required quantity for a material is increased
1119 -- Both cases are for Released Work Orders
1120 -- Author : amondal
1121
1122 PROCEDURE comp_alloc_chng_qty(p_api_version IN NUMBER,
1123 p_init_msg_list IN VARCHAR2,
1124 p_commit IN VARCHAR2,
1125 p_validation_level IN NUMBER,
1126 p_wip_entity_id IN NUMBER,
1127 p_organization_id IN NUMBER,
1128 p_operation_seq_num IN NUMBER,
1129 p_inventory_item_id IN NUMBER,
1130 p_qty_required IN NUMBER,
1131 p_supply_subinventory IN VARCHAR2 DEFAULT NULL, --12.1 source sub project
1132 p_supply_locator_id IN NUMBER DEFAULT NULL, --12.1 source sub project
1133 x_return_status OUT NOCOPY VARCHAR2,
1134 x_msg_data OUT NOCOPY VARCHAR2,
1135 x_msg_count OUT NOCOPY NUMBER) IS
1136
1137 l_api_name CONSTANT VARCHAR2(30) := 'comp_alloc_chng_qty';
1138 l_module constant varchar2(200) := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
1139 l_current_log_level constant number := FND_LOG.G_CURRENT_RUNTIME_LEVEL ;
1140 l_log boolean := FND_LOG.LEVEL_UNEXPECTED >= l_current_log_level ;
1141 l_plog boolean := l_log and FND_LOG.LEVEL_PROCEDURE >= l_current_log_level ;
1142 l_slog boolean := l_plog and FND_LOG.LEVEL_STATEMENT >= l_current_log_level ;
1143 l_api_version CONSTANT NUMBER := 1.0;
1144 l_wip_entity_id NUMBER;
1145 l_organization_id NUMBER;
1146 l_operation_seq_num NUMBER;
1147 l_inventory_item_id NUMBER;
1148 l_required_quantity NUMBER;
1149 l_quantity_issued NUMBER;
1150 l_quantity_allocated NUMBER;
1151 l_quantity_available NUMBER;
1152 l_auto_request_material VARCHAR2(1);
1153 l_project_id NUMBER;
1154 l_task_id NUMBER;
1155 l_status_type NUMBER;
1156 l_return_status VARCHAR2(1);
1157 l_msg_data VARCHAR2(2000);
1158
1159 l_msg_count NUMBER;
1160 l_request_number VARCHAR2(30);
1161
1162 l_allocate_comp_red_rec wip_picking_pub.allocate_comp_rec_t;
1163 l_allocate_comp_red_tbl wip_picking_pub.allocate_comp_tbl_t;
1164
1165 l_allocate_comp_inc_rec wip_picking_pub.allocate_comp_rec_t;
1166 l_allocate_comp_inc_tbl wip_picking_pub.allocate_comp_tbl_t;
1167
1168 BEGIN
1169 -- Standard Start of API savepoint
1170 SAVEPOINT comp_alloc_chng_qty;
1171
1172 -- Standard call to check for call compatibility.
1173 IF NOT fnd_api.compatible_api_call(
1174 l_api_version
1175 ,p_api_version
1176 ,l_api_name
1177 ,g_pkg_name) THEN
1178 RAISE fnd_api.g_exc_unexpected_error;
1179 END IF;
1180
1181 -- Initialize message list if p_init_msg_list is set to TRUE.
1182 IF fnd_api.to_boolean(p_init_msg_list) THEN
1183 fnd_msg_pub.initialize;
1184 END IF;
1185
1186 -- Initialize API return status to success
1187 x_return_status := fnd_api.g_ret_sts_success;
1188
1189 -- API body
1190
1191 if (l_plog) then
1192 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1193 'Start of ' || l_module || '('
1194 || 'p_organization_id='|| p_organization_id || ','
1195 || 'p_wip_entity_id='|| p_wip_entity_id || ','
1196 || 'p_operation_seq_num='|| p_operation_seq_num || ','
1197 || 'p_inventory_item_id='|| p_inventory_item_id || ','
1198 || 'p_qty_required='|| p_qty_required ||','
1199 || 'p_commit='|| p_commit
1200 || ')');
1201 end if;
1202
1203 l_wip_entity_id := p_wip_entity_id;
1204 l_operation_seq_num := p_operation_seq_num;
1205 l_inventory_item_id := p_inventory_item_id;
1206 l_organization_id := p_organization_id;
1207
1208 -- Get required, issued, allocated quantity and auto_request_material flag
1209
1210 select required_quantity,
1211 quantity_issued,
1212 eam_material_allocqty_pkg.allocated_quantity(wip_entity_id,operation_seq_num,organization_id,inventory_item_id),
1213 auto_request_material
1214 into l_required_quantity,
1215 l_quantity_issued,
1216 l_quantity_allocated,
1217 l_auto_request_material
1218 from wip_requirement_operations
1219 where inventory_item_id = l_inventory_item_id
1220 and organization_id = l_organization_id
1221 and wip_entity_id = l_wip_entity_id
1222 and operation_seq_num = l_operation_seq_num;
1223
1224 l_quantity_available := l_quantity_issued + nvl(l_quantity_allocated,0);
1225
1226 -- Get project id, task id, work order status and entity type
1227
1228 select wdj.project_id,
1229 wdj.task_id,
1230 wdj.status_type
1231 into l_project_id,
1232 l_task_id,
1233 l_status_type
1234 from wip_discrete_jobs wdj, wip_entities we
1235 where wdj.wip_entity_id = we.wip_entity_id
1236 and wdj.organization_id = we.organization_id
1237 and wdj.organization_id = l_organization_id
1238 and wdj.wip_entity_id = l_wip_entity_id;
1239
1240 if (l_status_type = 3 and p_qty_required <> l_quantity_available) then -- EAM Job in Released Status
1241
1242 if (p_qty_required < l_quantity_available) then -- Reduce required quantity
1243
1244 if (p_qty_required > l_quantity_issued) then -- Reduce quantity lesser that issued quantity
1245
1246 if (p_qty_required <= l_quantity_allocated) then
1247
1248 l_allocate_comp_red_rec.wip_entity_id := l_wip_entity_id;
1249 l_allocate_comp_red_rec.repetitive_schedule_id := null;
1250 l_allocate_comp_red_rec.use_pickset_flag := null;
1251 l_allocate_comp_red_rec.project_id := l_project_id;
1252 l_allocate_comp_red_rec.task_id := l_task_id;
1253 l_allocate_comp_red_rec.operation_seq_num := l_operation_seq_num;
1254 l_allocate_comp_red_rec.inventory_item_id := l_inventory_item_id;
1255 l_allocate_comp_red_rec.requested_quantity := (l_quantity_available - p_qty_required);
1256 l_allocate_comp_red_rec.source_subinventory_code := p_supply_subinventory; --12.1 source sub project
1257 l_allocate_comp_red_rec.source_locator_id := p_supply_locator_id; --12.1 source sub project
1258 l_allocate_comp_red_rec.lot_number := null;
1259 l_allocate_comp_red_rec.start_serial := null;
1260 l_allocate_comp_red_rec.end_serial := null;
1261
1262 l_allocate_comp_red_tbl(1) := l_allocate_comp_red_rec;
1263
1264 wip_picking_pub.reduce_comp_allocations ( p_comp_tbl => l_allocate_comp_red_tbl,
1265 p_wip_entity_type => wip_constants.eam,
1266 p_organization_id => l_organization_id,
1267 x_return_status => l_return_status,
1268 x_msg_data => l_msg_data);
1269
1270
1271 end if; -- End of check for quantity allocated
1272
1273
1274 else
1275
1276 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1277 fnd_message.set_name('EAM', 'EAM_ALLOCATE_QTY_ERROR'); -- Error message to be provided
1278 x_msg_data := fnd_message.get;
1279
1280
1281 end if; -- End of check for quantity issued
1282
1283
1284 else -- If Requested Quantity is increased
1285
1286 if l_auto_request_material = 'Y' then
1287
1288
1289 l_allocate_comp_inc_rec.wip_entity_id := l_wip_entity_id;
1290 l_allocate_comp_inc_rec.repetitive_schedule_id := null;
1291 l_allocate_comp_inc_rec.use_pickset_flag := null;
1292 l_allocate_comp_inc_rec.project_id := l_project_id;
1293 l_allocate_comp_inc_rec.task_id := l_task_id;
1294 l_allocate_comp_inc_rec.operation_seq_num := l_operation_seq_num;
1295 l_allocate_comp_inc_rec.inventory_item_id := l_inventory_item_id;
1296 l_allocate_comp_inc_rec.requested_quantity := (p_qty_required - l_quantity_available);
1297 l_allocate_comp_inc_rec.source_subinventory_code := p_supply_subinventory; --12.1 source sub project
1298 l_allocate_comp_inc_rec.source_locator_id := p_supply_locator_id; -- 12.1 source sub project
1299 l_allocate_comp_inc_rec.lot_number := null;
1300 l_allocate_comp_inc_rec.start_serial := null;
1301 l_allocate_comp_inc_rec.end_serial := null;
1302 l_allocate_comp_inc_tbl(1) := l_allocate_comp_inc_rec;
1303
1304 EAM_MATERIAL_request_PVT.allocate(p_api_version => 1.0,
1305 p_init_msg_list => fnd_api.g_false ,
1306 p_commit => fnd_api.g_false,
1307 p_validation_level => fnd_api.g_valid_level_full,
1308 x_return_status => l_return_status,
1309 x_msg_count => l_msg_count,
1310 x_msg_data => l_msg_data,
1311 x_request_number => l_request_number,
1312 p_wip_entity_type => wip_constants.eam ,
1313 p_organization_id => l_organization_id,
1314 p_wip_entity_id => l_allocate_comp_inc_tbl(1).wip_entity_id,
1315 p_operation_seq_num => l_allocate_comp_inc_tbl(1).operation_seq_num,
1316 p_inventory_item_id => l_allocate_comp_inc_tbl(1).inventory_item_id,
1317 p_requested_quantity => l_allocate_comp_inc_tbl(1).requested_quantity ,
1318 p_source_subinventory => l_allocate_comp_inc_tbl(1).source_subinventory_code, --12.1 source sub project
1319 p_source_locator => l_allocate_comp_inc_tbl(1).source_locator_id); --12.1 source sub project
1320
1321 end if; -- End of Check for Qty
1322
1323
1324 end if; -- End of Check for Released status
1325
1326 end if;
1327
1328 x_msg_data := l_msg_data;
1329 x_return_status := l_return_status;
1330
1331 -- End of API body.
1332 -- Standard check of p_commit.
1333
1334 IF fnd_api.to_boolean(p_commit) THEN
1335 COMMIT WORK;
1336 END IF;
1337
1338 -- l_stmt_num := 999;
1339
1340 -- Standard call to get message count and if count is 1, get message info.
1341 fnd_msg_pub.count_and_get(
1342 p_count => x_msg_count
1343 ,p_data => x_msg_data);
1344 EXCEPTION
1345 WHEN fnd_api.g_exc_error THEN
1346 ROLLBACK TO comp_alloc_chng_qty;
1347 x_return_status := fnd_api.g_ret_sts_error;
1348 fnd_msg_pub.count_and_get(
1349 -- p_encoded => FND_API.g_false
1350 p_count => x_msg_count
1351 ,p_data => x_msg_data);
1352 WHEN fnd_api.g_exc_unexpected_error THEN
1353 ROLLBACK TO comp_alloc_chng_qty;
1354 x_return_status := fnd_api.g_ret_sts_unexp_error;
1355
1356 fnd_msg_pub.count_and_get(
1357 p_count => x_msg_count
1358 ,p_data => x_msg_data);
1359 WHEN OTHERS THEN
1360 ROLLBACK TO comp_alloc_chng_qty;
1361 x_return_status := fnd_api.g_ret_sts_unexp_error;
1362 IF fnd_msg_pub.check_msg_level(
1363 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1364 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1365 END IF;
1366
1367 fnd_msg_pub.count_and_get(p_count => x_msg_count
1368 ,p_data => x_msg_data);
1369
1370
1371
1372 END comp_alloc_chng_qty;
1373
1374
1375 -- Procedure to create new allocations for a newly added material to a Released Work Order
1376 -- Author : amondal
1377
1378 PROCEDURE comp_alloc_new_mat(p_api_version IN NUMBER,
1379 p_init_msg_list IN VARCHAR2,
1380 p_commit IN VARCHAR2,
1381 p_validation_level IN NUMBER,
1382 p_wip_entity_id IN NUMBER,
1383 p_organization_id IN NUMBER,
1384 p_operation_seq_num IN NUMBER,
1385 p_inventory_item_id IN NUMBER,
1386 p_qty_required IN NUMBER,
1387 p_supply_subinventory IN VARCHAR2 DEFAULT NULL, --12.1 source sub project
1388 p_supply_locator_id IN NUMBER DEFAULT NULL, --12.1 source sub project
1389 x_return_status OUT NOCOPY VARCHAR2,
1390 x_msg_data OUT NOCOPY VARCHAR2,
1391 x_msg_count OUT NOCOPY NUMBER) IS
1392
1393 l_api_name CONSTANT VARCHAR2(30) := 'comp_alloc_chng_qty';
1394 l_api_version CONSTANT NUMBER := 1.0;
1395 l_wip_entity_id NUMBER;
1396 l_organization_id NUMBER;
1397 l_operation_seq_num NUMBER;
1398 l_inventory_item_id NUMBER;
1399 l_required_quantity NUMBER;
1400 l_auto_request_material VARCHAR2(1);
1401 l_project_id NUMBER;
1402 l_task_id NUMBER;
1403 l_status_type NUMBER;
1404 l_return_status VARCHAR2(1);
1405 l_msg_data VARCHAR2(2000);
1406
1407 l_msg_count NUMBER;
1408 l_request_number VARCHAR2(80);
1409
1410 l_allocate_comp_inc_rec wip_picking_pub.allocate_comp_rec_t;
1411 l_allocate_comp_inc_tbl wip_picking_pub.allocate_comp_tbl_t;
1412
1413 BEGIN
1414
1415 -- Standard Start of API savepoint
1416
1417 SAVEPOINT comp_alloc_new_mat;
1418
1419 -- Standard call to check for call compatibility.
1420 IF NOT fnd_api.compatible_api_call(
1421 l_api_version
1422 ,p_api_version
1423 ,l_api_name
1424 ,g_pkg_name) THEN
1425 RAISE fnd_api.g_exc_unexpected_error;
1426 END IF;
1427
1428 -- Initialize message list if p_init_msg_list is set to TRUE.
1429 IF fnd_api.to_boolean(p_init_msg_list) THEN
1430 fnd_msg_pub.initialize;
1431 END IF;
1432
1433 -- Initialize API return status to success
1434 x_return_status := fnd_api.g_ret_sts_success;
1435
1436 -- API body
1437
1438 l_wip_entity_id := p_wip_entity_id;
1439 l_operation_seq_num := p_operation_seq_num;
1440 l_inventory_item_id := p_inventory_item_id;
1441 l_organization_id := p_organization_id;
1442
1443 select required_quantity,
1444 auto_request_material
1445 into l_required_quantity,
1446 l_auto_request_material
1447 from wip_requirement_operations
1448 where inventory_item_id = l_inventory_item_id
1449 and organization_id = l_organization_id
1450 and wip_entity_id = l_wip_entity_id
1451 and operation_seq_num = l_operation_seq_num;
1452
1453 -- Get project id, task id, work order status and entity type
1454
1455 select wdj.project_id,
1456 wdj.task_id,
1457 wdj.status_type
1458 into l_project_id,
1459 l_task_id,
1460 l_status_type
1461 from wip_discrete_jobs wdj, wip_entities we
1462 where wdj.wip_entity_id = we.wip_entity_id
1463 and wdj.organization_id = we.organization_id
1464 and wdj.organization_id = l_organization_id
1465 and wdj.wip_entity_id = l_wip_entity_id;
1466
1467
1468 if (l_status_type = 3) then -- Released EAM work order
1469
1470 if l_auto_request_material = 'Y' then
1471
1472
1473 l_allocate_comp_inc_rec.wip_entity_id := l_wip_entity_id;
1474 l_allocate_comp_inc_rec.repetitive_schedule_id := null;
1475 l_allocate_comp_inc_rec.use_pickset_flag := null;
1476 l_allocate_comp_inc_rec.project_id := l_project_id;
1477 l_allocate_comp_inc_rec.task_id := l_task_id;
1478 l_allocate_comp_inc_rec.operation_seq_num := l_operation_seq_num;
1479 l_allocate_comp_inc_rec.inventory_item_id := l_inventory_item_id;
1480 /* Following subtraction expression has been commented as Fix for Issue5 of bug:2755159 */
1481 l_allocate_comp_inc_rec.requested_quantity := p_qty_required; --(p_qty_required - l_required_quantity);
1482 l_allocate_comp_inc_rec.source_subinventory_code := p_supply_subinventory; --12.1 source sub project
1483 l_allocate_comp_inc_rec.source_locator_id := p_supply_locator_id; --12.1 source sub project
1484 l_allocate_comp_inc_rec.lot_number := null;
1485 l_allocate_comp_inc_rec.start_serial := null;
1486 l_allocate_comp_inc_rec.end_serial := null;
1487
1488
1489 l_allocate_comp_inc_tbl(1) := l_allocate_comp_inc_rec;
1490
1491 EAM_MATERIAL_request_PVT.allocate(p_api_version => 1.0,
1492 p_init_msg_list => fnd_api.g_false ,
1493 p_commit => fnd_api.g_false,
1494 p_validation_level => fnd_api.g_valid_level_full,
1495 x_return_status => l_return_status,
1496 x_msg_count => l_msg_count,
1497 x_msg_data => l_msg_data,
1498 x_request_number => l_request_number,
1499 p_wip_entity_type => wip_constants.eam ,
1500 p_organization_id => l_organization_id,
1501 p_wip_entity_id => l_allocate_comp_inc_tbl(1).wip_entity_id,
1502 p_operation_seq_num => l_allocate_comp_inc_tbl(1).operation_seq_num,
1503 p_inventory_item_id => l_allocate_comp_inc_tbl(1).inventory_item_id,
1504 p_requested_quantity => l_allocate_comp_inc_tbl(1).requested_quantity ,
1505 p_source_subinventory => l_allocate_comp_inc_tbl(1).source_subinventory_code, -- 12.1 source sub project
1506 p_source_locator => l_allocate_comp_inc_tbl(1).source_locator_id); --12.1 source sub project
1507
1508 end if;
1509
1510 end if; -- end of Released EAM work order
1511
1512 -- End of API body.
1513 -- Standard check of p_commit.
1514
1515 IF fnd_api.to_boolean(p_commit) THEN
1516 COMMIT WORK;
1517 END IF;
1518
1519 -- l_stmt_num := 999;
1520
1521 -- Standard call to get message count and if count is 1, get message info.
1522 fnd_msg_pub.count_and_get(
1523 p_count => x_msg_count
1524 ,p_data => x_msg_data);
1525 EXCEPTION
1526 WHEN fnd_api.g_exc_error THEN
1527 ROLLBACK TO comp_alloc_new_mat;
1528 x_return_status := fnd_api.g_ret_sts_error;
1529 fnd_msg_pub.count_and_get(
1530 -- p_encoded => FND_API.g_false
1531 p_count => x_msg_count
1532 ,p_data => x_msg_data);
1533 WHEN fnd_api.g_exc_unexpected_error THEN
1534 ROLLBACK TO comp_alloc_new_mat;
1535 x_return_status := fnd_api.g_ret_sts_unexp_error;
1536
1537 fnd_msg_pub.count_and_get(
1538 p_count => x_msg_count
1539 ,p_data => x_msg_data);
1540 WHEN OTHERS THEN
1541 ROLLBACK TO comp_alloc_new_mat;
1542 x_return_status := fnd_api.g_ret_sts_unexp_error;
1543 IF fnd_msg_pub.check_msg_level(
1544 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1545 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1546 END IF;
1547
1548 fnd_msg_pub.count_and_get(p_count => x_msg_count
1549 ,p_data => x_msg_data);
1550
1551 END comp_alloc_new_mat;
1552
1553
1554 -- Procedure to create allocations during Release of a work order
1555 -- Procedure to cancel allocations during Cancel of a work order
1556 -- author : amondal
1557
1558 PROCEDURE alloc_at_release_cancel (
1559 p_api_version IN NUMBER,
1560 p_init_msg_list IN VARCHAR2,
1561 p_commit IN VARCHAR2,
1562 p_validation_level IN NUMBER,
1563 p_wip_entity_id IN NUMBER,
1564 p_organization_id IN NUMBER,
1565 p_status_type IN NUMBER,
1566 x_return_status OUT NOCOPY VARCHAR2, --later on add x_request_number
1567 x_msg_data OUT NOCOPY VARCHAR2,
1568 x_msg_count OUT NOCOPY NUMBER) IS
1569
1570 l_api_name CONSTANT VARCHAR2(30) := 'alloc_at_release_cancel';
1571 l_api_version CONSTANT NUMBER := 1.0;
1572 l_wip_entity_id NUMBER;
1573 l_organization_id NUMBER;
1574 l_return_status VARCHAR2(1);
1575 l_msg_data VARCHAR2(2000);
1576 l_project_id NUMBER;
1577 l_task_id NUMBER;
1578 l_status_type NUMBER;
1579 l_request_number VARCHAR2(30);
1580 l_pickslip_conc_req_id NUMBER := 0;
1581 l_current_log_level constant number := FND_LOG.G_CURRENT_RUNTIME_LEVEL ;
1582 l_pLog BOOLEAN := FND_LOG.LEVEL_PROCEDURE >= l_current_log_level;
1583 l_sLog BOOLEAN := l_pLog AND FND_LOG.LEVEL_STATEMENT >= l_current_log_level ;
1584 l_module CONSTANT VARCHAR2(100):= 'eam.plsql.'||g_pkg_name||'.'||l_api_name;
1585 l_msg VARCHAR2(2000);
1586
1587
1588 l_allocate_rec wip_picking_pub.allocate_rec_t;
1589 l_allocate_tbl wip_picking_pub.allocate_tbl_t;
1590
1591 BEGIN
1592
1593 -- Standard Start of API savepoint
1594
1595 SAVEPOINT alloc_at_release_cancel;
1596
1597 -- Standard call to check for call compatibility.
1598 IF NOT fnd_api.compatible_api_call(
1599 l_api_version
1600 ,p_api_version
1601 ,l_api_name
1602 ,g_pkg_name) THEN
1603 RAISE fnd_api.g_exc_unexpected_error;
1604 END IF;
1605
1606 -- Initialize message list if p_init_msg_list is set to TRUE.
1607 IF fnd_api.to_boolean(p_init_msg_list) THEN
1608 fnd_msg_pub.initialize;
1609 END IF;
1610
1611 -- Initialize API return status to success
1612 x_return_status := fnd_api.g_ret_sts_success;
1613
1614 -- API body
1615 if (l_pLog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Start');
1616 end if;
1617
1618 l_wip_entity_id := p_wip_entity_id;
1619 l_organization_id := p_organization_id;
1620
1621
1622 if (p_status_type = 3) then -- Work Order is Released
1623
1624 select project_id, task_id into l_project_id, l_task_id
1625 from wip_discrete_jobs
1626 where wip_entity_id = p_wip_entity_id
1627 and organization_id=p_organization_id;
1628
1629 l_allocate_rec.wip_entity_id := l_wip_entity_id;
1630 l_allocate_rec.repetitive_schedule_id := null;
1631 l_allocate_rec.use_pickset_flag := null;
1632 l_allocate_rec.project_id := l_project_id;
1633 l_allocate_rec.task_id := l_task_id;
1634 l_allocate_rec.bill_seq_id := null;
1635 l_allocate_rec.bill_org_id := null;
1636 l_allocate_rec.alt_rtg_dsg := null;
1637 l_allocate_tbl(1) := l_allocate_rec;
1638
1639 if (l_sLog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
1640 'Calling wip_picking_pub.allocate');
1641 end if;
1642 wip_picking_pub.allocate(p_alloc_tbl => l_allocate_tbl,
1643 p_cutoff_date => null,
1644 p_wip_entity_type => wip_constants.eam,
1645 p_organization_id => l_organization_id,
1646 x_mo_req_number => l_request_number,
1647 x_conc_req_id => l_pickslip_conc_req_id,
1648 x_return_status => l_return_status,
1649 x_msg_data => l_msg_data);
1650
1651 if(l_return_status<>null) then /*8941280 - return status is null if no material added for WO*/
1652 x_return_status := l_return_status; /*8941280*/
1653 end if;
1654 if(l_msg_data<>null) then
1655 x_msg_data := l_msg_data;
1656 end if;
1657
1658 if (l_sLog) then
1659 l_msg := 'wip_picking_pub.allocate returned:'|| 'x_return_status:'
1660 ||x_return_status||' x_mo_req_number:'||l_request_number;
1661 l_msg := REPLACE(l_msg, CHR(0), ' ');
1662 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,l_msg);
1663 end if;
1664
1665 elsif p_status_type IN (5,7) then --status_type 5 is added for bug 7631627
1666 if (l_sLog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
1667 'Calling wip_picking_pub.cancel_allocations');
1668 end if;
1669 wip_picking_pub.cancel_allocations (p_wip_entity_id => l_wip_entity_id,
1670 p_wip_entity_type => wip_constants.eam,
1671 p_repetitive_schedule_id => NULL,
1672 x_return_status => l_return_status,
1673 x_msg_data => l_msg_data);
1674
1675 if(l_return_status<>null) then /*8941280 - return status is null if no material added for WO*/
1676 x_return_status := l_return_status; /*8941280*/
1677 end if;
1678 if(l_msg_data<>null) then
1679 x_msg_data := l_msg_data;
1680 end if;
1681
1682 if (l_sLog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
1683 'wip_picking_pub.cancel_allocations returned:'|| 'x_return_status:'
1684 ||x_return_status);
1685 end if;
1686 end if;
1687
1688 -- End of API body.
1689 -- Standard check of p_commit.
1690 IF fnd_api.to_boolean(p_commit) THEN
1691 COMMIT WORK;
1692 END IF;
1693
1694 -- Standard call to get message count and if count is 1, get message info.
1695 if (l_pLog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'End');
1696 end if;
1697 fnd_msg_pub.count_and_get(
1698 p_count => x_msg_count
1699 ,p_data => x_msg_data);
1700 EXCEPTION
1701 WHEN fnd_api.g_exc_error THEN
1702 ROLLBACK TO alloc_at_release_cancel;
1703 x_return_status := fnd_api.g_ret_sts_error;
1704 fnd_msg_pub.count_and_get(
1705 -- p_encoded => FND_API.g_false
1706 p_count => x_msg_count
1707 ,p_data => x_msg_data);
1708 WHEN fnd_api.g_exc_unexpected_error THEN
1709 ROLLBACK TO alloc_at_release_cancel;
1710 x_return_status := fnd_api.g_ret_sts_unexp_error;
1711
1712 fnd_msg_pub.count_and_get(
1713 p_count => x_msg_count
1714 ,p_data => x_msg_data);
1715 WHEN OTHERS THEN
1716 ROLLBACK TO alloc_at_release_cancel;
1717 x_return_status := fnd_api.g_ret_sts_unexp_error;
1718 IF fnd_msg_pub.check_msg_level(
1719 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1720 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1721 END IF;
1722
1723 fnd_msg_pub.count_and_get(p_count => x_msg_count
1724 ,p_data => x_msg_data);
1725
1726 END alloc_at_release_cancel;
1727
1728 FUNCTION get_tx_processor_mode(p_dummy IN boolean := false
1729 ) return number IS
1730 l_proc_mode number := 1;
1731 l_api_name CONSTANT VARCHAR2(30) := 'get_tx_processor_mode';
1732 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1733 l_module CONSTANT VARCHAR2(60) := 'eam.plsql.'||l_full_name;
1734 l_slog boolean := FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ;
1735 begin
1736 l_proc_mode := FND_PROFILE.VALUE('TRANSACTION_PROCESS_MODE');
1737 if (l_slog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
1738 'Inventory transaction processor mode (TRANSACTION_PROCESS_MODE) = '||l_proc_mode);
1739 end if;
1740 return l_proc_mode;
1741 end;
1742
1743
1744 END EAM_MATERIALISSUE_PVT;
1745