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