[Home] [Help]
PACKAGE BODY: APPS.EAM_MTL_TXN_PROCESS
Source
1 Package Body eam_mtl_txn_process AS
2 /* $Header: EAMMTTXB.pls 120.3 2007/11/29 03:26:59 mashah ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30):= 'eam_mtl_txn_process';
5 Procedure PROCESSMTLTXN(
6 p_txn_header_id IN NUMBER,
7 p_item_id IN OUT NOCOPY NUMBER,
8 p_item IN VARCHAR2 := NULL,
9 p_revision IN VARCHAR2 := NULL,
10 p_org_id IN OUT NOCOPY NUMBER,
11 p_trx_action_id IN NUMBER ,
12 p_subinv_code IN OUT NOCOPY VARCHAR2 ,
13 p_tosubinv_code IN VARCHAR2 := NULL,
14 p_locator_id IN OUT NOCOPY NUMBER,
15 p_locator IN VARCHAR2 := NULL,
16 p_tolocator_id IN NUMBER := NULL,
17 p_trx_type_id IN NUMBER ,
18 p_trx_src_type_id IN NUMBER ,
19 p_trx_qty IN NUMBER ,
20 p_pri_qty IN NUMBER ,
21 p_uom IN VARCHAR2 ,
22 p_date IN DATE := sysdate,
23 p_reason_id IN OUT NOCOPY NUMBER,
24 p_reason IN VARCHAR2 := NULL,
25 p_user_id IN NUMBER ,
26 p_trx_src_id IN NUMBER := NULL,
27 x_trx_temp_id OUT NOCOPY NUMBER ,
28 p_operation_seq_num IN NUMBER := NULL,
29 p_wip_entity_type IN NUMBER := NULL,
30 p_trx_reference IN VARCHAR2 := NULL,
31 p_negative_req_flag IN NUMBER := NULL,
32 p_serial_ctrl_code IN NUMBER := NULL,
33 p_lot_ctrl_code IN NUMBER := NULL,
34 p_from_ser_number IN VARCHAR2 := NULL,
35 P_to_ser_number IN VARCHAR2 := NULL,
36 p_lot_num IN VARCHAR2 := NULL,
37 p_wip_supply_type IN NUMBER := NULL,
38 p_subinv_ctrl IN NUMBER := 0,
39 p_locator_ctrl IN NUMBER := 0,
40 p_wip_process IN NUMBER := NULL, -- determines to call WIP Transaction API
41 -- 0 -> No call,1 -> Call
42 p_dateNotInFuture IN NUMBER := 1, -- 1 --> do check,0 --> no check
43 x_error_flag OUT NOCOPY NUMBER, -- returns 0 if no error , >1 if any error .
44 x_error_mssg OUT NOCOPY VARCHAR2 ) IS
45
46 x_status NUMBER; -- holds the status returned from every transaction API call
47 x_status_flag VARCHAR2(240);
48 x_header_id NUMBER; -- holds the header id from MMTT table
49 x_ser_txn_id NUMBER; -- holds Serial transaction id which will lind MTL_SERIAL_NUMBERS_TEMP
50 -- with MTL_SYSTEM_ITEMS or MTL_TRANSACTION_LOTS_TEMP
51 x_wip_ret_status VARCHAR2(250); -- holds the status returned from WIP Transaction API
52 x_future_date NUMBER;
53
54 x_locator_ctrl NUMBER ; -- Holds the Locator Control information
55
56 x_mssg_count NUMBER;
57 x_trans_count NUMBER;
58 x_inv_ret_status NUMBER;
59 l_proc_mode number := 1;
60 l_api_name CONSTANT VARCHAR2(30) := 'processmtltxn';
61 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
62 l_module CONSTANT VARCHAR2(60) := 'eam.plsql.'||l_full_name;
63 l_current_log_level constant number := FND_LOG.G_CURRENT_RUNTIME_LEVEL ;
64 l_log boolean := FND_LOG.LEVEL_UNEXPECTED >= l_current_log_level ;
65 l_plog boolean := l_log and FND_LOG.LEVEL_PROCEDURE >= l_current_log_level ;
66 l_slog boolean := l_plog and FND_LOG.LEVEL_STATEMENT >= l_current_log_level ;
67 l_mti_qty number;
68 l_qty_issued number;
69 l_remain_qty number;
70 l_tx_mode number;
71 l_tx_mode_mti number := 1;
72 -- x_proc_msg OUT VARCHAR2 ,
73 /*x_item_id NUMBER;
74 x_reason_id NUMBER;
75 x_locator_id NUMBER;
76 */
77
78 Begin
79
80 if (l_plog) then
81 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
82 'Start of ' || l_module || '('
83 || 'p_txn_header_id='|| p_txn_header_id || ',p_item_id='|| p_item_id
84 || ',p_trx_type_id='|| p_trx_type_id || ',p_trx_src_id='|| p_trx_src_id
85 || ',p_operation_seq_num='|| p_operation_seq_num
86 || ',p_item='|| p_item || ',p_revision='|| p_revision
87 || ',p_org_id='|| p_org_id || ',p_subinv_code='|| p_subinv_code
88 || ',p_locator_id='|| p_locator_id || ',p_tolocator_id='|| p_tolocator_id
89 || ',p_trx_type_id='|| p_trx_type_id || ',p_trx_src_id='|| p_trx_src_id
90 || ',p_trx_qty='|| p_trx_qty || ',p_uom='|| p_uom
91 || ',p_date='|| p_date || ',p_reason_id='|| p_reason_id
92 || ',p_trx_reference='|| p_trx_reference || ',p_lot_num='|| p_lot_num
93 || ',p_lot_ctrl_code='|| p_lot_ctrl_code
94 || ',p_serial_ctrl_code='|| p_serial_ctrl_code
95 || ',p_from_ser_number='|| p_from_ser_number
96 || ',p_to_ser_number='|| p_to_ser_number
97 || ')');
98 end if;
99
100 /* initialize the error flag */
101 x_error_flag := 0;
102
103 /* Checking the Transaction date */
104 if(p_dateNotInFuture = 1) then
105 begin
106 select 1 into x_future_date from dual where p_date <= sysdate ;
107 exception
108 when no_data_found then
109 x_error_flag := 1;
110 x_error_mssg := 'EAM_FUTURE_DATE';
111 return;
112 end; -- end of p_item_id check
113 end if;
114
115
116 /* Finding out Item ID from Name */
117 if(p_item_id IS NULL and p_item IS NOT NULL) then
118 begin
119 select inventory_item_id into p_item_id
120 from mtl_system_items_b_kfv
121 where
122 concatenated_segments = p_item
123 and organization_id = p_org_id;
124 exception
125 when no_data_found then
126 x_error_flag := 1;
127 x_error_mssg := 'EAM_NO_ITEM_FOUND';
128 return;
129 end; -- end of p_item_id check
130 end if;
131
132
133 -- Check if unprocessed records for return tx exist in MTI.
134 -- Validate: Qty for unprocessed records + return qty <= Issued Qty
135 if (p_trx_type_id = 43) then
136 --transaction qty is same as primary qty since we always
137 --transact in primary UOM.
138 select sum(transaction_quantity) into l_mti_qty
139 from mtl_transactions_interface
140 where transaction_source_id = p_trx_src_id
141 and transaction_type_id = 43
142 and organization_id = p_org_id
143 and inventory_item_id = p_item_id
144 and process_flag = 1; --1(ready). Do not pick up 3(errored) or 2(not ready)
145
146 if (l_mti_qty > 0) then
147 select quantity_issued into l_qty_issued
148 from wip_requirement_operations
149 where wip_entity_id = p_trx_src_id
150 and inventory_item_id = p_item_id;
151 if (l_slog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
152 'Qty to return in MTI='|| l_mti_qty|| ', Qty to return by user='
153 || p_trx_qty|| ', Qty issued to job=' || l_qty_issued);
154 end if;
155 l_remain_qty := l_qty_issued - l_mti_qty;
156 if (l_remain_qty <= 0) then
157 x_error_flag := 2;
158 fnd_message.set_name('EAM','EAM_NOTHING_TO_RETURN');
159 x_error_mssg := fnd_message.get;
160 return;
161 elsif (l_remain_qty - p_trx_qty < 0) then
162 x_error_flag := 2;
163 fnd_message.set_name('EAM','EAM_REDUCE_RETURN_QTY');
164 fnd_message.set_token('QTY', to_char(l_remain_qty) );
165 x_error_mssg := fnd_message.get;
166 return;
167 end if;
168 end if;
169 end if;
170
171 /* Finding out SubInventory is Correct or Not */
172 if(p_subinv_code IS NOT NULL) then
173 Begin
174
175 if(p_subinv_ctrl <> 1) then
176 select secondary_inventory_name into p_subinv_code
177 from mtl_secondary_inventories
178 where
179 secondary_inventory_name = p_subinv_code
180 and organization_id = p_org_id
181 and nvl(disable_date,trunc(sysdate)+1)>trunc(sysdate)
182 and inv_material_status_grp.is_status_applicable(NULL,NULL,p_trx_type_id,NULL,NULL,
183 p_org_id,p_item_id,secondary_inventory_name,NULL,NULL,NULL,'Z') = 'Y' ;
184
185 elsif(p_subinv_ctrl = 1) then
186 select secondary_inventory_name into p_subinv_code
187 from mtl_secondary_inventories
188 where
189 secondary_inventory_name = p_subinv_code
190 and organization_id = p_org_id
191 and nvl(disable_date,trunc(sysdate)+1)>trunc(sysdate)
192 and inv_material_status_grp.is_status_applicable(NULL,NULL,p_trx_type_id,NULL,NULL,
193 p_org_id,p_item_id,secondary_inventory_name,NULL,NULL,NULL,'Z') = 'Y'
194 and EXISTS (select secondary_inventory from mtl_item_sub_inventories
195 where secondary_inventory = secondary_inventory_name
196 and inventory_item_id = p_item_id
197 and organization_id = p_org_id);
198 end if ; -- end of inner if
199
200 exception
201 when no_data_found then
202 x_error_flag := 1;
203 x_error_mssg := 'EAM_RET_MAT_INVALID_SUBINV';
204 return;
205 when others then
206 x_status := 1;
207 x_error_mssg := 'Other error occured during subinv check ';
208 return;
209 end;
210 end if;
211
212
213
214 /* Finding out Locator ID from Locator */
215 if(p_locator_id IS NULL and p_locator IS NOT NULL) then
216 Begin
217
218 if(p_locator_ctrl <> 1) then
219 select Inventory_Location_ID into p_locator_id
220 from mtl_item_locations_kfv where
221 concatenated_segments = p_locator
222 and subinventory_code = p_subinv_code
223 and organization_id = p_org_id;
224
225 elsif(p_locator_ctrl = 1) then
226 select Inventory_Location_ID into p_locator_id
227 from mtl_item_locations_kfv where
228 concatenated_segments = p_locator
229 and subinventory_code = p_subinv_code
230 and organization_id = p_org_id
231 and EXISTS (select '1' from mtl_secondary_locators
232 where inventory_item_id = p_item_id
233 and organization_id = p_org_id
234 and secondary_locator = inventory_location_id) ;
235
236
237 end if; -- end of inner if
238
239 exception
240 when no_data_found then
241 x_error_flag := 1;
242 x_error_mssg := 'EAM_RET_MAT_INVALID_LOCATOR';
243 return;
244 end;
245 end if;
246
247 /* Check for Locator Control which could be defined
248 at 3 level Organization,Subinventory,Item .
249 */
250 Get_LocatorControl_Code(
251 p_org_id,
252 p_subinv_code,
253 p_item_id,
254 p_trx_action_id,
255 x_locator_ctrl,
256 x_error_flag,
257 x_error_mssg);
258
259 if(x_error_flag <> 0) then
260 return;
261 end if;
262
263 -- if the locator control is Predefined or Dynamic Entry
264 if(x_locator_ctrl = 2 or x_locator_ctrl = 3) then
265 if(p_locator_id IS NULL) then
266 x_error_flag := 1;
267 x_error_mssg := 'EAM_RET_MAT_LOCATOR_NEEDED';
268 return;
269 end if;
270 elsif(x_locator_ctrl = 1) then -- If the locator control is NOControl
271 if(p_locator_id IS NOT NULL) then
272 x_error_flag := 1;
273 x_error_mssg := 'EAM_RET_MAT_LOCATOR_RESTRICTED';
274 return;
275 end if;
276 end if; -- end of locator_control checkif
277
278 /* Finding out Reason ID from Reason Name */
279 if(p_reason_id IS NULL and p_reason IS NOT NULL) then
280 Begin
281 select reason_id into p_reason_id
282 from mtl_transaction_reasons
283 where
284 reason_name = p_reason
285 and nvl(disable_date,sysdate+1) > sysdate ;
286 exception
287 when no_data_found then
288 x_error_flag := 1;
289 x_error_mssg := 'EAM_RET_MAT_INVALID_REASON';
290 return;
291 end;
292 end if;
293
294
295 Savepoint eammttxnsp;
296
297 /* Initializing the Global variables for MTI Transaction API */
298 if(p_txn_header_id is null) then
299 select
300 mtl_material_transactions_s.nextval into x_header_id
301 from dual;
302 x_trx_temp_id := x_header_id;
303 else
304 x_header_id := p_txn_header_id;
305 end if;
306
307
308 INV_TRANSACTIONS.G_Header_ID := x_header_id;
309 INV_TRANSACTIONS.G_Interface_ID := x_header_id;
310
311 if(p_serial_ctrl_code <> 1) then
312 select mtl_material_transactions_s.nextval into INV_TRANSACTIONS.G_Serial_ID
313 from dual;
314 end if;
315
316 if (l_slog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
317 'Inserting into MTI with Tx Header ID=' || x_header_id);
318 end if;
319 /* Calling the Inventory Transaction API to insert data into MTI table .*/
320
321 INV_TRANSACTIONS.Line_Interface_Insert(
322 p_item_id,
323 p_revision,
324 p_org_id,
325 p_trx_src_id,
326 p_trx_action_id,
327 p_subinv_code,
328 p_tosubinv_code,
329 p_locator_id,
330 p_tolocator_id,
331 null,/* Added for bug# 5896548*/
332 /*p_org_id,Commented for bug# 5896548*/
333 p_trx_type_id,
334 p_trx_src_type_id,
335 p_trx_qty,
336 p_uom,
337 p_date,
338 p_reason_id,
339 p_user_id,
340 x_error_mssg,
341 x_status_flag
342 );
343
344 if(x_status_flag <> 'C') then
345 x_error_flag := 1;
346 x_error_mssg := 'EAM_RET_MAT_UNEXPECTED_ERROR';
347 raise fnd_api.g_exc_unexpected_error;
348 end if;
349 /*
350 if(p_txn_header_id is null) then
351 x_header_id := x_trx_temp_id ;
352 else
353 x_header_id := p_txn_header_id;
354 end if;
355 */
356
357 l_tx_mode := EAM_MATERIALISSUE_PVT.get_tx_processor_mode();
358 if (l_tx_mode not in (1,4)) then
359 l_tx_mode_mti := 3; --background mode if not online or form level. form level=online.
360 end if;
361
362 /* Updating MTI data which are not populated by the above API */
363
364 UPDATE MTL_TRANSACTIONS_INTERFACE SET
365 WIP_ENTITY_TYPE = p_wip_entity_type,
366 OPERATION_SEQ_NUM = p_operation_seq_num,
367 TRANSACTION_REFERENCE = p_trx_reference,
368 NEGATIVE_REQ_FLAG = negative_req_flag,
369 TRANSACTION_SOURCE_ID = p_trx_src_id,
370 TRANSACTION_MODE = l_tx_mode_mti --else background transactions wont be picked up
371 where
372 TRANSACTION_HEADER_ID = x_header_id ;
373
374
375
376 -- ------------------------------------------------------------------------------------
377 -- Performing LOT Transaction entries .
378 -- ------------------------------------------------------------------------------------
379
380 if(p_lot_ctrl_code <> 1 and p_lot_num IS NOT NULL) then
381
382 INV_TRANSACTIONS.LOT_INTERFACE_INSERT(
383 p_Transaction_Quantity => p_trx_qty,
384 p_Lot_Number => p_lot_num,
385 p_User_Id => p_user_id,
389 end if; -- end of lot ctrl code check
386 p_serial_number_control_code => p_serial_ctrl_code
387 );
388
390
391
392 -- ------------------------------------------------------------------------------------
393 -- Performing Serial Transaction entries
394 -- ------------------------------------------------------------------------------------
395
396 if(p_serial_ctrl_code <> 1 and p_from_ser_number IS NOT NULL
397 and p_to_ser_number IS NOT NULL and p_serial_ctrl_code IS NOT NULL) then
398
399 INV_TRANSACTIONS.SERIAL_INTERFACE_INSERT(
400 p_From_Serial => p_from_ser_number,
401 p_To_Serial => p_to_ser_number,
402 p_User_Id => p_user_id,
403 p_lot_control_code => p_lot_ctrl_code
404 );
405
406 end if; -- end of ser ctrl code check
407
408
409 -- ------------------------------------------------------------------------------------
410 -- Performing MTI data processing by calling WIP API
411 -- ------------------------------------------------------------------------------------
412
413 if(p_wip_process = 1) then
414 -- only call txn processor if online processing. 4(form level) is treated as 1.
415 if (l_tx_mode in (1,4)) then
416 if (l_slog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
417 'Calling tx processor: INV_TXN_MANAGER_PUB.process_Transactions');
418 end if;
419 x_inv_ret_status := INV_TXN_MANAGER_PUB.process_Transactions(
420 p_api_version => 1.0,
421 p_header_id => x_header_id,
422 p_table => 1, -- meant for process from MTI table.
423 x_return_status => x_wip_ret_status,
424 x_msg_count => x_mssg_count,
425 x_msg_data => x_error_mssg,
426 x_trans_count => x_trans_count
427 );
428 if (l_slog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
429 'INV_TXN_MANAGER_PUB.process_Transactions returned. '||
430 'Return status:'|| x_wip_ret_status);
431 end if;
432 if(x_wip_ret_status = FND_API.G_RET_STS_UNEXP_ERROR OR
433 x_wip_ret_status = FND_API.G_RET_STS_ERROR OR
434 x_inv_ret_status <> 0) then
435 if (l_log) then FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module,
436 'INV_TXN_MANAGER_PUB.process_Transactions returned a error. '||
437 'Return message:'|| x_error_mssg);
438 end if;
439 x_error_flag := 2;
440 -- x_error_mssg := 'EAM_RET_MAT_UNEXPECTED_ERROR';
441 return;
442 end if;
443 end if; -- end of if EAM_MATERIALISSUE_PVT.get_tx_processor_mode() = 1
444 end if; -- end of check for p_wip_process
445
446 Exception
447 When fnd_api.g_exc_unexpected_error then
448 rollback to eammttxnsp;
449 End PROCESSMTLTXN;
450
451
452 Procedure Get_LocatorControl_Code(
453 p_org IN NUMBER,
454 p_subinv IN VARCHAR2,
455 p_item_id IN NUMBER,
456 p_action IN NUMBER,
457 x_locator_ctrl OUT NOCOPY NUMBER,
458 x_error_flag OUT NOCOPY NUMBER, -- returns 0 if no error ,1 if any error .
459 x_error_mssg OUT NOCOPY VARCHAR2
460 ) IS
461 x_org_ctrl NUMBER;
462 x_sub_ctrl NUMBER;
463 x_item_ctrl NUMBER;
464 x_neg_flag NUMBER;
465 x_restrict_flag NUMBER;
466 BEGIN
467
468 -- initialize the output .
469 x_error_flag := 0;
470 x_error_mssg := '';
471
472 -- retrive organization level control information
473 Begin
474 SELECT
475 negative_inv_receipt_code,stock_locator_control_code into
476 x_neg_flag,x_org_ctrl FROM MTL_PARAMETERS
477 WHERE
478 organization_id = p_org;
479 Exception
480 When no_data_found then
481 x_error_flag := 1;
482 x_error_mssg := 'EAM_INVALID_ORGANIZATION';
483 End;
484
485 -- retrive subinventory level control information
486 Begin
487 SELECT
488 locator_type into x_sub_ctrl
489 FROM MTL_SECONDARY_INVENTORIES
490 WHERE
491 organization_id = p_org and
492 secondary_inventory_name = p_subinv ;
493 Exception
494 When no_data_found then
495 x_error_flag := 1;
496 x_error_mssg := 'EAM_RET_MAT_INVALID_SUBINV1';
497 End;
498
499 -- retrive Item level control information
500 Begin
501 SELECT
502 location_control_code,restrict_locators_code into
503 x_item_ctrl,x_restrict_flag
504 FROM MTL_SYSTEM_ITEMS
505 WHERE
506 inventory_item_id = p_item_id and
507 organization_id = p_org;
508 Exception
509 When no_data_found then
510 x_error_flag := 1;
511 x_error_mssg := 'EAM_NO_ITEM_FOUND';
512 End;
513
514 if(x_org_ctrl = 1) then
515 x_locator_ctrl := 1;
516 elsif(x_org_ctrl = 2) then
517 x_locator_ctrl := 2;
518 elsif(x_org_ctrl = 3) then
519 x_locator_ctrl := 3;
520 if(dynamic_entry_not_allowed(x_restrict_flag,
521 x_neg_flag,p_action)) then
522 x_locator_ctrl := 2;
523 end if;
524 elsif(x_org_ctrl = 4) then
525 if(x_sub_ctrl = 1) then
526 x_locator_ctrl := 1;
527 elsif(x_sub_ctrl = 2) then
528 x_locator_ctrl := 2;
529 elsif(x_sub_ctrl = 3) then
533 x_locator_ctrl := 2;
530 x_locator_ctrl := 3;
531 if(dynamic_entry_not_allowed(x_restrict_flag,
532 x_neg_flag,p_action)) then
534 end if;
535 elsif(x_sub_ctrl = 5) then
536 if(x_item_ctrl = 1) then
537 x_locator_ctrl := 1;
538 elsif(x_item_ctrl = 2) then
539 x_locator_ctrl := 2;
540 elsif(x_item_ctrl = 3) then
541 x_locator_ctrl := 3;
542 if(dynamic_entry_not_allowed(x_restrict_flag,
543 x_neg_flag,p_action)) then
544 x_locator_ctrl := 2;
545 end if;
546 elsif(x_item_ctrl IS NULL) then
547 x_locator_ctrl := x_sub_ctrl;
548 else
549 x_error_flag := 1;
550 x_error_mssg := 'EAM_RET_MAT_INVALID_LOCATOR';
551 return ;
552 end if;
553 else
554 x_error_flag := 1;
555 x_error_mssg := 'EAM_RET_MAT_INVALID_SUBINV';
556 return ;
557 end if;
558 else
559 x_error_flag := 1;
560 x_error_mssg := 'EAM_RET_MAT_INVALID_ORG';
561 return ;
562 end if;
563
564 END Get_LocatorControl_Code; -- end of get_locatorcontrol_code procedure
565
566 PROCEDURE MoreMaterial_Add
567 (
568 p_api_version IN NUMBER,
569 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
570 p_commit IN VARCHAR2 := FND_API.G_FALSE,
571 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
572
573 p_work_object_id IN NUMBER,
574 p_organization_id IN NUMBER,
575 p_operation_seq_num IN NUMBER,
576 p_item_id IN NUMBER,
577 p_required_quantity IN NUMBER,
578 p_requested_quantity IN NUMBER,
579 p_supply_subinventory IN VARCHAR2, --12.1 source sub project
580 p_supply_locator_id IN NUMBER, --12.1 source sub project
581 x_return_status OUT NOCOPY VARCHAR2,
582 x_msg_count OUT NOCOPY NUMBER,
583 x_msg_data OUT NOCOPY VARCHAR2
584
585 )
586
587
588 IS
589 l_api_name CONSTANT VARCHAR2(30) := 'MoreMaterial_Add';
590 l_api_version CONSTANT NUMBER := 1.0;
591
592 l_stmt_num NUMBER;
593 l_work_object_id NUMBER;
594 l_organization_id NUMBER;
595 l_operation_seq_num NUMBER;
596 l_item_id NUMBER;
597 l_required_quantity NUMBER;
598
599
600 l_return_status VARCHAR2(1);
601 l_msg_count NUMBER;
602 l_msg_data VARCHAR2(1000);
603 l_error_message VARCHAR2(1000);
604 l_output_dir VARCHAR2(512);
605
606 l_eam_mat_req_rec EAM_PROCESS_WO_PUB.eam_mat_req_rec_type;
607
608 l_eam_wo_relations_tbl EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
609 l_eam_wo_tbl EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
610 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
611 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
612 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
613 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
614 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
615 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
616 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
617 l_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
618 l_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
619 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
620 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
621 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
622 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
623 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
624 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
625 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
626
627 l_out_eam_wo_relations_tbl EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
628 l_out_eam_wo_tbl EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
629 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
630 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
631 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
632 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
633 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
634 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
635 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
636 l_out_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
637 l_out_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
638 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
639 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
640 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
641 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
642 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
646 BEGIN
643 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
644 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
645
647 -- Standard Start of API savepoint
648 SAVEPOINT EAM_WO_MATERIAL_UTIL_PUB;
649 -- Standard call to check for call compatibility.
650 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
651 p_api_version ,
652 l_api_name ,
653 G_PKG_NAME )
654 THEN
655 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
656 END IF;
657 -- Initialize message list if p_init_msg_list is set to TRUE.
658 IF FND_API.to_Boolean( p_init_msg_list ) THEN
659 FND_MSG_PUB.initialize;
660 END IF;
661 -- Initialize API return status to success
662 x_return_status := FND_API.G_RET_STS_SUCCESS;
663 -- API body
664
665 /* Initialize the local variables */
666 l_stmt_num := 10;
667
668 l_work_object_id := p_work_object_id;
669 l_organization_id := p_organization_id;
670 l_operation_seq_num := p_operation_seq_num;
671 l_item_id := p_item_id;
672 l_required_quantity := p_required_quantity;
673 l_return_status := FND_API.G_RET_STS_SUCCESS;
674
675 /* get output directory path from database */
676 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
677
678
679 /* set the required quantity for the corresponding inventory item */
680
681 l_eam_mat_req_rec.batch_id := 1;
682 l_eam_mat_req_rec.header_id := l_work_object_id;
683 l_eam_mat_req_rec.wip_entity_id := l_work_object_id;
684 l_eam_mat_req_rec.organization_id := l_organization_id;
685 l_eam_mat_req_rec.operation_seq_num := l_operation_seq_num;
686 l_eam_mat_req_rec.inventory_item_id := l_item_id;
687 l_eam_mat_req_rec.required_quantity := l_required_quantity;
688 l_eam_mat_req_rec.supply_subinventory := p_supply_subinventory;
689 l_eam_mat_req_rec.supply_locator_id := p_supply_locator_id;
690
691 --fix for 3405115.populate the requested_quantity
692 l_eam_mat_req_rec.requested_quantity := p_requested_quantity;
693 l_eam_mat_req_rec.auto_request_material := 'Y';
694 l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
695
696 l_eam_mat_req_tbl(1) := l_eam_mat_req_rec;
697
698 l_out_eam_op_tbl := l_eam_op_tbl;
699 l_out_eam_op_network_tbl := l_eam_op_network_tbl;
700 l_out_eam_res_tbl := l_eam_res_tbl;
701 l_out_eam_res_inst_tbl := l_eam_res_inst_tbl;
702 l_out_eam_sub_res_tbl := l_eam_sub_res_tbl;
703 l_out_eam_res_usage_tbl := l_eam_res_usage_tbl;
704 l_out_eam_mat_req_tbl := l_eam_mat_req_tbl;
705
706 /* Call Work Order API to perform the operations */
707
708 eam_process_wo_pub.PROCESS_MASTER_CHILD_WO
709 ( p_bo_identifier => 'EAM'
710 , p_init_msg_list => TRUE
711 , p_api_version_number => 1.0
712 , p_eam_wo_relations_tbl => l_eam_wo_relations_tbl
713 , p_eam_wo_tbl => l_eam_wo_tbl
714 , p_eam_op_tbl => l_eam_op_tbl
715 , p_eam_op_network_tbl => l_eam_op_network_tbl
716 , p_eam_res_tbl => l_eam_res_tbl
717 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
718 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
719 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
720 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
721 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
722 , x_eam_wo_relations_tbl => l_eam_wo_relations_tbl
723 , p_eam_wo_comp_tbl => l_eam_wo_comp_tbl
724 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
725 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
726 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
727 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
728 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
729 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
730 , p_eam_request_tbl => l_eam_request_tbl
731 , x_eam_wo_tbl => l_out_eam_wo_tbl
732 , x_eam_op_tbl => l_out_eam_op_tbl
733 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
734 , x_eam_res_tbl => l_out_eam_res_tbl
735 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
736 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
737 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
738 , x_eam_direct_items_tbl => l_out_eam_direct_items_tbl
739 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
740 , x_eam_wo_comp_tbl => l_out_eam_wo_comp_tbl
741 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
742 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
743 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
744 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
745 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
746 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
747 , x_eam_request_tbl => l_out_eam_request_tbl
748 , p_commit => 'N'
749 , x_return_status => l_return_status
750 , x_msg_count => l_msg_count
754 , p_output_dir => l_output_dir
751 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
752 , p_debug_filename => 'eamwomii.log'
753 , p_debug_file_mode => 'w'
755 );
756
757
758
759 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
760 x_return_status := FND_API.G_RET_STS_ERROR;
761 RETURN;
762
763 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
764 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
765 RETURN;
766
767 END IF;
768
769 -- End of API body.
770 -- Standard check of p_commit.
771 IF FND_API.To_Boolean( p_commit ) THEN
772 --dbms_output.put_line('committing');
773 COMMIT WORK;
774 END IF;
775 -- Standard call to get message count and if count is 1, get message info.
776 FND_MSG_PUB.Count_And_Get
777 ( p_count => x_msg_count ,
778 p_data => x_msg_data
779 );
780 EXCEPTION
781 WHEN FND_API.G_EXC_ERROR THEN
782 x_return_status := FND_API.G_RET_STS_ERROR ;
783 FND_MSG_PUB.Count_And_Get
784 ( p_count => x_msg_count ,
785 p_data => x_msg_data
786 );
787
788
789 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
790 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
791 FND_MSG_PUB.Count_And_Get
792 (
793 p_count => x_msg_count,
794 p_data => x_msg_data
795 );
796
797 WHEN OTHERS THEN
798 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
799 IF FND_MSG_PUB.Check_Msg_Level
800 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
801 THEN
802 FND_MSG_PUB.Add_Exc_Msg
803 ( G_PKG_NAME,
804 l_api_name||'('||l_stmt_num||')'
805 );
806 END IF;
807 FND_MSG_PUB.Count_And_Get
808 ( p_count => x_msg_count,
809 p_data => x_msg_data
810 );
811
812
813 END MoreMaterial_Add;
814
815 PROCEDURE MoreDirectItem_Add
816 (
817 p_api_version IN NUMBER,
818 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
819 p_commit IN VARCHAR2 := FND_API.G_FALSE,
820 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
821
822 p_work_object_id IN NUMBER,
823 p_organization_id IN NUMBER,
824 p_operation_seq_num IN NUMBER,
825 p_direct_item_type IN NUMBER,
826 p_item_id IN NUMBER,
827 p_need_by_date IN DATE,
828 p_required_quantity IN NUMBER,
829 p_requested_quantity IN NUMBER,
830 x_return_status OUT NOCOPY VARCHAR2,
831 x_msg_count OUT NOCOPY NUMBER,
832 x_msg_data OUT NOCOPY VARCHAR2
833
834 )
835
836
837 IS
838 l_api_name CONSTANT VARCHAR2(30) := 'MoreDirectItem_Add';
839 l_api_version CONSTANT NUMBER := 1.0;
840
841 l_stmt_num NUMBER;
842 l_work_object_id NUMBER;
843 l_organization_id NUMBER;
844 l_operation_seq_num NUMBER;
845 l_item_id NUMBER;
846 l_need_by_date DATE;
847 l_required_quantity NUMBER;
848
849
850 l_return_status VARCHAR2(1);
851 l_msg_count NUMBER;
852 l_msg_data VARCHAR2(1000);
853 l_error_message VARCHAR2(1000);
854 l_output_dir VARCHAR2(512);
855
856 l_eam_direct_items_rec EAM_PROCESS_WO_PUB.eam_direct_items_rec_type;
857
858 l_eam_wo_relations_tbl EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
859 l_eam_wo_tbl EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
860 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
861 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
862 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
863 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
864 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
865 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
866 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
867 l_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
868 l_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
869 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
870 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
871 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
872 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
873 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
874 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
875 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
876
877 l_out_eam_wo_relations_tbl EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
878 l_out_eam_wo_tbl EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
882 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
879 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
880 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
881 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
883 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
884 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
885 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
886 l_out_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
887 l_out_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
888 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
889 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
890 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
891 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
892 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
893 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
894 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
895
896 l_eam_mat_req_rec EAM_PROCESS_WO_PUB.eam_mat_req_rec_type;
897
898
899 BEGIN
900 -- Standard Start of API savepoint
901 SAVEPOINT EAM_WO_MATERIAL_UTIL_PUB;
902 -- Standard call to check for call compatibility.
903 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
904 p_api_version ,
905 l_api_name ,
906 G_PKG_NAME )
907 THEN
908 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
909 END IF;
910 -- Initialize message list if p_init_msg_list is set to TRUE.
911 IF FND_API.to_Boolean( p_init_msg_list ) THEN
912 FND_MSG_PUB.initialize;
913 END IF;
914 -- Initialize API return status to success
915 x_return_status := FND_API.G_RET_STS_SUCCESS;
916 -- API body
917
918 /* Initialize the local variables */
919 l_stmt_num := 10;
920
921 l_work_object_id := p_work_object_id;
922 l_organization_id := p_organization_id;
923 l_operation_seq_num := p_operation_seq_num;
924 l_item_id := p_item_id;
925 l_need_by_date := p_need_by_date;
926 l_required_quantity := p_required_quantity;
927 l_return_status := FND_API.G_RET_STS_SUCCESS;
928
929 /* get output directory path from database */
930 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
931
932
933 if(p_direct_item_type =1) then
934
935 /* set the required quantity for the corresponding direct item */
936
937 l_eam_direct_items_rec.batch_id := 1;
938 l_eam_direct_items_rec.header_id := l_work_object_id;
939
940 l_eam_direct_items_rec.wip_entity_id := l_work_object_id;
941 l_eam_direct_items_rec.organization_id := l_organization_id;
942 l_eam_direct_items_rec.operation_seq_num := l_operation_seq_num;
943 l_eam_direct_items_rec.direct_item_sequence_id := l_item_id;
944 l_eam_direct_items_rec.need_by_date := l_need_by_date;
945 l_eam_direct_items_rec.required_quantity := l_required_quantity;
946
947 --fix for 3405115.populate requested_quantity
948 l_eam_direct_items_rec.requested_quantity := p_requested_quantity;
949 -- l_eam_direct_items_rec.auto_request_material := 'Y';
950 l_eam_direct_items_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
951
952 l_eam_direct_items_tbl(1) := l_eam_direct_items_rec;
953
954
955 else
956
957 l_eam_mat_req_rec.batch_id := 1;
958 l_eam_mat_req_rec.header_id := l_work_object_id;
959 l_eam_mat_req_rec.wip_entity_id := l_work_object_id;
960 l_eam_mat_req_rec.organization_id := l_organization_id;
961 l_eam_mat_req_rec.operation_seq_num := l_operation_seq_num;
962 l_eam_mat_req_rec.inventory_item_id := l_item_id;
963 l_eam_mat_req_rec.required_quantity := l_required_quantity;
964
965 --fix for 3405115.populate requested_quantity
966 l_eam_mat_req_rec.requested_quantity := p_requested_quantity;
967 l_eam_mat_req_rec.auto_request_material := 'Y';
968 l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
969
970 l_eam_mat_req_tbl(1) := l_eam_mat_req_rec;
971
972 end if;
973
974 l_out_eam_op_tbl := l_eam_op_tbl;
975 l_out_eam_op_network_tbl := l_eam_op_network_tbl;
976 l_out_eam_res_tbl := l_eam_res_tbl;
977 l_out_eam_res_inst_tbl := l_eam_res_inst_tbl;
978 l_out_eam_sub_res_tbl := l_eam_sub_res_tbl;
979 l_out_eam_res_usage_tbl := l_eam_res_usage_tbl;
980 l_out_eam_mat_req_tbl := l_eam_mat_req_tbl;
981 l_out_eam_direct_items_tbl := l_eam_direct_items_tbl;
982
983 /* Call Work Order API to perform the operations */
984
985 eam_process_wo_pub.PROCESS_MASTER_CHILD_WO
986 ( p_bo_identifier => 'EAM'
987 , p_init_msg_list => TRUE
988 , p_api_version_number => 1.0
989 , p_eam_wo_relations_tbl => l_eam_wo_relations_tbl
990 , p_eam_wo_tbl => l_eam_wo_tbl
991 , p_eam_op_tbl => l_eam_op_tbl
992 , p_eam_op_network_tbl => l_eam_op_network_tbl
996 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
993 , p_eam_res_tbl => l_eam_res_tbl
994 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
995 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
997 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
998 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
999 , x_eam_wo_relations_tbl => l_eam_wo_relations_tbl
1000 , p_eam_wo_comp_tbl => l_eam_wo_comp_tbl
1001 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
1002 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
1003 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
1004 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
1005 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
1006 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
1007 , p_eam_request_tbl => l_eam_request_tbl
1008 , x_eam_wo_tbl => l_out_eam_wo_tbl
1009 , x_eam_op_tbl => l_out_eam_op_tbl
1010 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
1011 , x_eam_res_tbl => l_out_eam_res_tbl
1012 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
1013 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
1014 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
1015 , x_eam_direct_items_tbl => l_out_eam_direct_items_tbl
1016 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
1017 , x_eam_wo_comp_tbl => l_out_eam_wo_comp_tbl
1018 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
1019 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
1020 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
1021 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
1022 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
1023 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
1024 , x_eam_request_tbl => l_out_eam_request_tbl
1025 , p_commit => 'N'
1026 , x_return_status => l_return_status
1027 , x_msg_count => l_msg_count
1028 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
1029 , p_debug_filename => 'eamwomdi.log'
1030 , p_debug_file_mode => 'w'
1031 , p_output_dir => l_output_dir
1032 );
1033
1034
1035 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1036 x_return_status := FND_API.G_RET_STS_ERROR;
1037 RETURN;
1038
1039 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1040 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1041 RETURN;
1042
1043 END IF;
1044
1045 -- End of API body.
1046 -- Standard check of p_commit.
1047 IF FND_API.To_Boolean( p_commit ) THEN
1048 --dbms_output.put_line('committing');
1049 COMMIT WORK;
1050 END IF;
1051 -- Standard call to get message count and if count is 1, get message info.
1052 FND_MSG_PUB.Count_And_Get
1053 ( p_count => x_msg_count ,
1054 p_data => x_msg_data
1055 );
1056 EXCEPTION
1057 WHEN FND_API.G_EXC_ERROR THEN
1058 x_return_status := FND_API.G_RET_STS_ERROR ;
1059 FND_MSG_PUB.Count_And_Get
1060 ( p_count => x_msg_count ,
1061 p_data => x_msg_data
1062 );
1063
1064
1065 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1066 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1067 FND_MSG_PUB.Count_And_Get
1068 (
1069 p_count => x_msg_count,
1070 p_data => x_msg_data
1071 );
1072
1073 WHEN OTHERS THEN
1074 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1075 IF FND_MSG_PUB.Check_Msg_Level
1076 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1077 THEN
1078 FND_MSG_PUB.Add_Exc_Msg
1079 ( G_PKG_NAME,
1080 l_api_name||'('||l_stmt_num||')'
1081 );
1082 END IF;
1083 FND_MSG_PUB.Count_And_Get
1084 ( p_count => x_msg_count,
1085 p_data => x_msg_data
1086 );
1087
1088
1089 END MoreDirectItem_Add;
1090
1091
1092 PROCEDURE insert_into_wro(
1093 p_api_version IN NUMBER
1094 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1095 ,p_commit IN VARCHAR2 := fnd_api.g_false
1096 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1097 ,p_wip_entity_id IN NUMBER
1098 ,p_organization_id IN NUMBER
1099 ,p_inventory_item_id IN NUMBER
1100 ,p_description IN VARCHAR2
1101 ,p_operation_seq_num IN NUMBER
1102 ,p_supply IN NUMBER
1103 ,p_mode IN VARCHAR2 := 'INSERT'
1104 ,p_required_date IN DATE
1105 ,p_quantity IN NUMBER
1106 ,p_comments IN VARCHAR2
1107 ,p_supply_subinventory IN VARCHAR2
1108 ,p_locator IN VARCHAR2
1109 ,p_mrp_net_flag IN VARCHAR2
1110 ,p_material_release IN VARCHAR2
1111 ,x_return_status OUT NOCOPY VARCHAR2
1112 ,x_msg_count OUT NOCOPY NUMBER
1116 IS
1113 ,x_msg_data OUT NOCOPY VARCHAR2
1114 )
1115
1117 l_api_name CONSTANT VARCHAR2(30) := 'insert_into_wro';
1118 l_api_version CONSTANT NUMBER := 1.0;
1119 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1120
1121 l_stmt_num NUMBER;
1122 l_wip_entity_id NUMBER;
1123 l_inventory_item_id NUMBER;
1124 l_department_id NUMBER;
1125 l_supply NUMBER;
1126 l_locator NUMBER;
1127 l_mrp_net_flag NUMBER;
1128 l_material_release VARCHAR2(1);
1129 l_material_exists NUMBER := 0;
1130 l_existing_operation NUMBER;
1131 l_existing_department NUMBER;
1132 l_existing_description VARCHAR2(240);
1133 l_req_qty NUMBER := 0;
1134 l_status_type NUMBER := 0;
1135 l_material_issue_by_mo VARCHAR2(1);
1136 l_auto_request_material VARCHAR2(1);
1137 invalid_update_operation NUMBER := 0;
1138 invalid_update_department NUMBER := 0;
1139 invalid_update_description NUMBER := 0;
1140 l_update_status NUMBER := 0;
1141 l_return_status NUMBER := 0;
1142 l_msg_count NUMBER := 0;
1143 l_msg_data VARCHAR2(2000) := '';
1144 l_return_status1 VARCHAR2(30) := '';
1145 l_output_dir VARCHAR2(512);
1146
1147
1148 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
1149 l_eam_mat_req_rec EAM_PROCESS_WO_PUB.eam_mat_req_rec_type;
1150 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
1151 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
1152 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
1153 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
1154 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
1155 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
1156 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
1157 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
1158 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
1159 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
1160 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
1161 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
1162 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
1163 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
1164 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
1165 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
1166
1167 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
1168 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
1169 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
1170 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
1171 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
1172 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
1173 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
1174 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
1175 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
1176 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
1177 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
1178 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
1179 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
1180 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
1181 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
1182 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
1183 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
1184
1185 BEGIN
1186 -- Standard Start of API savepoint
1187 l_stmt_num := 10;
1188 SAVEPOINT get_insert_into_wro_pvt;
1189
1190 l_stmt_num := 20;
1191 -- Standard call to check for call compatibility.
1192 IF NOT fnd_api.compatible_api_call(
1193 l_api_version
1194 ,p_api_version
1195 ,l_api_name
1196 ,g_pkg_name) THEN
1197 RAISE fnd_api.g_exc_unexpected_error;
1198 END IF;
1199
1200 l_stmt_num := 30;
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 l_stmt_num := 40;
1207 -- Initialize API return status to success
1208 x_return_status := fnd_api.g_ret_sts_success;
1209
1210 l_stmt_num := 50;
1211 -- API body
1212
1213 /* get output directory path from database */
1214 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
1215
1219
1216
1217 l_wip_entity_id := p_wip_entity_id ;
1218
1220 -- Get Department Id
1221
1222 begin
1223 select department_id
1224 into l_department_id
1225 from wip_operations
1226 where wip_entity_id = l_wip_entity_id
1227 and operation_seq_num = p_operation_seq_num
1228 and organization_id = p_organization_id;
1229 exception
1230 when no_data_found then
1231 l_department_id:=null;
1232 end;
1233
1234
1235 -- Get Locator Id
1236
1237 if (p_locator is not null) then
1238
1239 select inventory_location_id
1240 into l_locator
1241 from mtl_item_locations_kfv
1242 where organization_id = p_organization_id
1243 and concatenated_segments = p_locator
1244 and subinventory_code = p_supply_subinventory ;
1245
1246 end if;
1247
1248 -- Get MRP Net Flag
1249
1250 if (p_mrp_net_flag is not null) then
1251 l_mrp_net_flag := 1;
1252 else
1253 l_mrp_net_flag := 2;
1254 end if;
1255
1256
1257
1258
1259 if(p_mode='INSERT') then
1260 -- entry into WIP_REQUIREMENT_OPERATIONS
1261
1262 l_eam_mat_req_rec.batch_id := 1;
1263 l_eam_mat_req_rec.header_id := p_wip_entity_id;
1264 l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
1265 l_eam_mat_req_rec.wip_entity_id := p_wip_entity_id;
1266 l_eam_mat_req_rec.organization_id := p_organization_id;
1267 l_eam_mat_req_rec.operation_seq_num := p_operation_seq_num;
1268 l_eam_mat_req_rec.inventory_item_id := p_inventory_item_id;
1269 l_eam_mat_req_rec.quantity_per_assembly := p_quantity;
1270 l_eam_mat_req_rec.department_id := l_department_id;
1271 l_eam_mat_req_rec.wip_supply_type := p_supply;
1272 l_eam_mat_req_rec.date_required := p_required_date;
1273 l_eam_mat_req_rec.required_quantity := p_quantity;
1274 l_eam_mat_req_rec.supply_subinventory := p_supply_subinventory;
1275 l_eam_mat_req_rec.supply_locator_id := l_locator;
1276 l_eam_mat_req_rec.mrp_net_flag := l_mrp_net_flag;
1277 l_eam_mat_req_rec.comments := p_comments;
1278 l_eam_mat_req_rec.auto_request_material := p_material_release;
1279
1280 l_eam_mat_req_tbl(1) := l_eam_mat_req_rec;
1281
1282
1283 EAM_PROCESS_WO_PUB.Process_WO
1284 ( p_bo_identifier => 'EAM'
1285 , p_init_msg_list => TRUE
1286 , p_api_version_number => 1.0
1287 , p_commit => 'N'
1288 , p_eam_wo_rec => l_eam_wo_rec
1289 , p_eam_op_tbl => l_eam_op_tbl
1290 , p_eam_op_network_tbl => l_eam_op_network_tbl
1291 , p_eam_res_tbl => l_eam_res_tbl
1292 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
1293 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
1294 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
1295 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
1296 , p_eam_direct_items_tbl => l_eam_di_tbl
1297 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
1298 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
1299 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
1300 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
1301 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
1302 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
1303 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
1304 , p_eam_request_tbl => l_eam_request_tbl
1305 , x_eam_wo_rec => l_out_eam_wo_rec
1306 , x_eam_op_tbl => l_out_eam_op_tbl
1307 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
1308 , x_eam_res_tbl => l_out_eam_res_tbl
1309 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
1310 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
1311 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
1312 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
1313 , x_eam_direct_items_tbl => l_out_eam_di_tbl
1314 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
1315 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
1316 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
1317 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
1318 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
1319 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
1320 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
1321 , x_eam_request_tbl => l_out_eam_request_tbl
1322 , x_return_status => x_return_status
1323 , x_msg_count => x_msg_count
1324 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
1325 , p_debug_filename => 'insertwro.log'
1326 , p_output_dir => l_output_dir
1327 , p_debug_file_mode => 'w'
1328 );
1329
1330 else
1331
1332 l_eam_mat_req_rec.batch_id := 1;
1333 l_eam_mat_req_rec.header_id := p_wip_entity_id;
1334 l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
1338 l_eam_mat_req_rec.inventory_item_id := p_inventory_item_id;
1335 l_eam_mat_req_rec.wip_entity_id := p_wip_entity_id;
1336 l_eam_mat_req_rec.organization_id := p_organization_id;
1337 l_eam_mat_req_rec.operation_seq_num := p_operation_seq_num;
1339 l_eam_mat_req_rec.quantity_per_assembly := p_quantity;
1340 l_eam_mat_req_rec.department_id := l_department_id;
1341 l_eam_mat_req_rec.wip_supply_type := p_supply;
1342 l_eam_mat_req_rec.date_required := p_required_date;
1343 l_eam_mat_req_rec.required_quantity := p_quantity;
1344 l_eam_mat_req_rec.supply_subinventory := p_supply_subinventory;
1345 l_eam_mat_req_rec.supply_locator_id := l_locator;
1346 l_eam_mat_req_rec.mrp_net_flag := l_mrp_net_flag;
1347 l_eam_mat_req_rec.comments := p_comments;
1348 l_eam_mat_req_rec.auto_request_material := p_material_release;
1349
1350 l_eam_mat_req_tbl(1) := l_eam_mat_req_rec;
1351
1352
1353 EAM_PROCESS_WO_PUB.Process_WO
1354 ( p_bo_identifier => 'EAM'
1355 , p_init_msg_list => TRUE
1356 , p_api_version_number => 1.0
1357 , p_commit => 'N'
1358 , p_eam_wo_rec => l_eam_wo_rec
1359 , p_eam_op_tbl => l_eam_op_tbl
1360 , p_eam_op_network_tbl => l_eam_op_network_tbl
1361 , p_eam_res_tbl => l_eam_res_tbl
1362 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
1363 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
1364 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
1365 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
1366 , p_eam_direct_items_tbl => l_eam_di_tbl
1367 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
1368 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
1369 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
1370 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
1371 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
1372 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
1373 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
1374 , p_eam_request_tbl => l_eam_request_tbl
1375 , x_eam_wo_rec => l_out_eam_wo_rec
1376 , x_eam_op_tbl => l_out_eam_op_tbl
1377 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
1378 , x_eam_res_tbl => l_out_eam_res_tbl
1379 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
1380 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
1381 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
1382 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
1383 , x_eam_direct_items_tbl => l_out_eam_di_tbl
1384 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
1385 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
1386 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
1387 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
1388 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
1389 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
1390 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
1391 , x_eam_request_tbl => l_out_eam_request_tbl
1392 , x_return_status => x_return_status
1393 , x_msg_count => x_msg_count
1394 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
1395 , p_debug_filename => 'updatewro.log'
1396 , p_output_dir => l_output_dir
1397 , p_debug_file_mode => 'w'
1398 );
1399
1400
1401 end if;
1402
1403 IF(x_return_status<>'S') THEN
1404 ROLLBACK TO get_insert_into_wro_pvt;
1405 END IF;
1406
1407 -- End of API body.
1408 -- Standard check of p_commit.
1409 IF fnd_api.to_boolean(p_commit)
1410 and x_return_status = 'S' THEN
1411 COMMIT WORK;
1412 END IF;
1413
1414 l_stmt_num := 999;
1415 -- Standard call to get message count and if count is 1, get message info.
1416 fnd_msg_pub.count_and_get(
1417 p_count => x_msg_count
1418 ,p_data => x_msg_data);
1419 EXCEPTION
1420 WHEN fnd_api.g_exc_error THEN
1421 ROLLBACK TO get_insert_into_wro_pvt;
1422 x_return_status := fnd_api.g_ret_sts_error;
1423 fnd_msg_pub.count_and_get(
1424 -- p_encoded => FND_API.g_false
1425 p_count => x_msg_count
1426 ,p_data => x_msg_data);
1427 WHEN fnd_api.g_exc_unexpected_error THEN
1428 ROLLBACK TO get_insert_into_wro_pvt;
1429 x_return_status := fnd_api.g_ret_sts_unexp_error;
1430
1431 fnd_msg_pub.count_and_get(
1432 p_count => x_msg_count
1433 ,p_data => x_msg_data);
1434 WHEN OTHERS THEN
1435 ROLLBACK TO get_insert_into_wro_pvt;
1436 x_return_status := fnd_api.g_ret_sts_unexp_error;
1437 IF fnd_msg_pub.check_msg_level(
1438 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1442 fnd_msg_pub.count_and_get(
1439 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1440 END IF;
1441
1443 p_count => x_msg_count
1444 ,p_data => x_msg_data);
1445
1446 END insert_into_wro;
1447
1448
1449 PROCEDURE insert_into_wdi(
1450 p_api_version IN NUMBER
1451 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1452 ,p_commit IN VARCHAR2 := fnd_api.g_false
1453 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1454 ,p_wip_entity_id IN NUMBER
1455 ,p_organization_id IN NUMBER
1456 ,p_direct_item_seq_id IN NUMBER := NULL
1457 ,p_description IN VARCHAR2
1458 ,p_operation_seq_num IN NUMBER
1459 ,p_mode IN VARCHAR2 := 'INSERT'
1460 ,p_direct_item_type IN VARCHAR2 :='1'
1461 ,p_purchasing_category_id NUMBER :=null
1462 ,p_suggested_vendor_id NUMBER :=null
1463 ,p_suggested_vendor_name VARCHAR2 :=null
1464 ,p_suggested_vendor_site VARCHAR2 :=null
1465 ,p_suggested_vendor_contact VARCHAR2 :=null
1466 ,p_suggested_vendor_phone VARCHAR2 :=null,
1467 p_suggested_vendor_item_num VARCHAR2 :=null,
1468 p_unit_price NUMBER :=null,
1469 p_auto_request_material VARCHAR2 :=null,
1470 p_required_quantity NUMBER :=null,
1471 p_uom VARCHAR2 :=null,
1472 p_need_by_date DATE :=null
1473 ,x_return_status OUT NOCOPY VARCHAR2
1474 ,x_msg_count OUT NOCOPY NUMBER
1475 ,x_msg_data OUT NOCOPY VARCHAR2
1476 )
1477 IS
1478 l_api_name CONSTANT VARCHAR2(30) := 'insert_into_wro';
1479 l_api_version CONSTANT NUMBER := 1.0;
1480 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1481
1482 l_stmt_num NUMBER;
1483 l_wip_entity_id NUMBER;
1484 l_inventory_item_id NUMBER;
1485 l_department_id NUMBER;
1486 l_supply NUMBER;
1487 l_locator NUMBER;
1488 l_mrp_net_flag NUMBER;
1489 l_material_release VARCHAR2(1);
1490 l_material_exists NUMBER := 0;
1491 l_existing_operation NUMBER;
1492 l_existing_department NUMBER;
1493 l_existing_description VARCHAR2(240);
1494 l_req_qty NUMBER := 0;
1495 l_status_type NUMBER := 0;
1496 l_material_issue_by_mo VARCHAR2(1);
1497 l_auto_request_material VARCHAR2(1);
1498 invalid_update_operation NUMBER := 0;
1499 invalid_update_department NUMBER := 0;
1500 invalid_update_description NUMBER := 0;
1501 l_update_status NUMBER := 0;
1502 l_return_status NUMBER := 0;
1503 l_msg_count NUMBER := 0;
1504 l_msg_data VARCHAR2(2000) := '';
1505 l_return_status1 VARCHAR2(30) := '';
1506 l_purchasing_category_id NUMBER :=0;
1507 l_site_id NUMBER :=0;
1508 l_contact_id NUMBER :=0;
1509
1510 l_seq_id NUMBER;
1511 l_output_dir VARCHAR2(512);
1512
1513 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
1514 l_eam_mat_req_rec EAM_PROCESS_WO_PUB.eam_mat_req_rec_type;
1515 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
1516 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
1517 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
1518 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
1519 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
1520 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
1521 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
1522 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
1523 l_eam_di_req_rec EAM_PROCESS_WO_PUB.eam_direct_items_rec_type;
1524 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
1525 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
1526 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
1527 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
1528 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
1529 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
1530 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
1531 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
1532
1533 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
1534 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
1535 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
1536 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
1537 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
1538 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
1539 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
1543 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
1540 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
1541 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
1542 l_eam_di_req_rec1 EAM_PROCESS_WO_PUB.eam_direct_items_rec_type;
1544 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
1545 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
1546 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
1547 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
1548 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
1549 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
1550 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
1551 BEGIN
1552 -- Standard Start of API savepoint
1553 l_stmt_num := 10;
1554 SAVEPOINT get_insert_into_wro_pvt;
1555
1556 l_stmt_num := 20;
1557 -- Standard call to check for call compatibility.
1558 IF NOT fnd_api.compatible_api_call(
1559 l_api_version
1560 ,p_api_version
1561 ,l_api_name
1562 ,g_pkg_name) THEN
1563 RAISE fnd_api.g_exc_unexpected_error;
1564 END IF;
1565
1566 l_stmt_num := 30;
1567 -- Initialize message list if p_init_msg_list is set to TRUE.
1568 IF fnd_api.to_boolean(p_init_msg_list) THEN
1569 fnd_msg_pub.initialize;
1570 END IF;
1571
1572 l_stmt_num := 40;
1573 -- Initialize API return status to success
1574 x_return_status := fnd_api.g_ret_sts_success;
1575
1576 l_stmt_num := 50;
1577 -- API body
1578
1579 l_wip_entity_id := p_wip_entity_id ;
1580
1581 -- Get Inventory Item Id
1582
1583 /* get output directory path from database */
1584 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
1585
1586
1587 -- Get Department Id
1588
1589 begin
1590 select department_id
1591 into l_department_id
1592 from wip_operations
1593 where wip_entity_id = l_wip_entity_id
1594 and operation_seq_num = p_operation_seq_num
1595 and organization_id = p_organization_id;
1596 exception
1597 when no_data_found then
1598 l_department_id:=null;
1599 end;
1600
1601
1602 l_seq_id := p_direct_item_seq_id;
1603
1604
1605 if(p_direct_item_type='1') then
1606
1607
1608 if(p_mode='INSERT') then
1609 l_eam_di_req_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
1610 else
1611 l_eam_di_req_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
1612 end if;
1613
1614 l_eam_di_req_rec.wip_entity_id := p_wip_entity_id;
1615 l_eam_di_req_rec.organization_id := p_organization_id;
1616 l_eam_di_req_rec.operation_seq_num := p_operation_seq_num;
1617 l_eam_di_req_rec.direct_item_sequence_id :=l_seq_id;
1618 l_eam_di_req_rec.required_quantity := p_required_quantity;
1619 l_eam_di_req_rec.department_id := l_department_id;
1620 l_eam_di_req_rec.description := p_description;
1621 l_eam_di_req_rec.need_by_date := p_need_by_date;
1622 l_eam_di_req_rec.purchasing_category_id := p_purchasing_category_id ;
1623 l_eam_di_req_rec.suggested_vendor_id := p_suggested_vendor_id;
1624 l_eam_di_req_rec.suggested_vendor_name := p_suggested_vendor_name;
1625 l_eam_di_req_rec.suggested_vendor_site := p_suggested_vendor_site;
1626 l_eam_di_req_rec.suggested_vendor_site_id := l_site_id;
1627 l_eam_di_req_rec.suggested_vendor_contact := p_suggested_vendor_contact;
1628 l_eam_di_req_rec.suggested_vendor_contact_id := l_contact_id;
1629 l_eam_di_req_rec.suggested_vendor_phone := p_suggested_vendor_phone;
1630 l_eam_di_req_rec.suggested_vendor_item_num := p_suggested_vendor_item_num;
1631 l_eam_di_req_rec.unit_price := p_unit_price;
1632 l_eam_di_req_rec.uom := p_uom;
1633 l_eam_di_req_rec.auto_request_material := p_auto_request_material;
1634
1635
1636 l_eam_di_tbl(1) := l_eam_di_req_rec;
1637
1638
1639 EAM_PROCESS_WO_PUB.Process_WO
1640 ( p_bo_identifier => 'EAM'
1641 , p_init_msg_list => TRUE
1642 , p_api_version_number => 1.0
1643 , p_commit => 'N'
1644 , p_eam_wo_rec => l_eam_wo_rec
1645 , p_eam_op_tbl => l_eam_op_tbl
1646 , p_eam_op_network_tbl => l_eam_op_network_tbl
1647 , p_eam_res_tbl => l_eam_res_tbl
1648 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
1649 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
1650 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
1654 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
1651 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
1652 , p_eam_direct_items_tbl => l_eam_di_tbl
1653 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
1655 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
1656 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
1657 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
1658 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
1659 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
1660 , p_eam_request_tbl => l_eam_request_tbl
1661 , x_eam_wo_rec => l_out_eam_wo_rec
1662 , x_eam_op_tbl => l_out_eam_op_tbl
1663 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
1664 , x_eam_res_tbl => l_out_eam_res_tbl
1665 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
1666 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
1667 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
1668 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
1669 , x_eam_direct_items_tbl => l_out_eam_di_tbl
1670 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
1671 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
1672 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
1673 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
1674 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
1675 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
1676 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
1677 , x_eam_request_tbl => l_out_eam_request_tbl
1678 , x_return_status => x_return_status
1679 , x_msg_count => x_msg_count
1680 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
1681 , p_debug_filename => 'insertwdi.log'
1682 , p_output_dir => l_output_dir
1683 , p_debug_file_mode => 'w'
1684 );
1685
1686
1687 else
1688 if(p_mode='INSERT') then
1689 l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
1690 else
1691 l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
1692 end if;
1693
1694 l_eam_mat_req_rec.batch_id := 1;
1695 l_eam_mat_req_rec.header_id :=p_wip_entity_id;
1696
1697 l_eam_mat_req_rec.wip_entity_id := p_wip_entity_id;
1698 l_eam_mat_req_rec.organization_id := p_organization_id;
1699 l_eam_mat_req_rec.operation_seq_num := p_operation_seq_num;
1700 l_eam_mat_req_rec.inventory_item_id := l_seq_id;
1701 l_eam_mat_req_rec.quantity_per_assembly := p_required_quantity;
1702 l_eam_mat_req_rec.department_id := l_department_id;
1703 l_eam_mat_req_rec.date_required := p_need_by_date;
1704 l_eam_mat_req_rec.required_quantity := p_required_quantity;
1705 l_eam_mat_req_rec.auto_request_material := p_auto_request_material;
1706 l_eam_mat_req_rec.unit_price := p_unit_price;
1707 l_eam_mat_req_rec.suggested_vendor_name := p_suggested_vendor_name;
1708 l_eam_mat_req_rec.vendor_id := p_suggested_vendor_id;
1709
1710
1711 l_eam_mat_req_tbl(1) := l_eam_mat_req_rec;
1712
1713
1714 EAM_PROCESS_WO_PUB.Process_WO
1715 ( p_bo_identifier => 'EAM'
1716 , p_init_msg_list => TRUE
1717 , p_api_version_number => 1.0
1718 , p_commit => 'N'
1719 , p_eam_wo_rec => l_eam_wo_rec
1720 , p_eam_op_tbl => l_eam_op_tbl
1721 , p_eam_op_network_tbl => l_eam_op_network_tbl
1722 , p_eam_res_tbl => l_eam_res_tbl
1723 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
1724 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
1725 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
1726 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
1727 , p_eam_direct_items_tbl => l_eam_di_tbl
1728 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
1729 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
1730 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
1731 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
1732 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
1733 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
1734 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
1735 , p_eam_request_tbl => l_eam_request_tbl
1736 , x_eam_wo_rec => l_out_eam_wo_rec
1737 , x_eam_op_tbl => l_out_eam_op_tbl
1738 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
1739 , x_eam_res_tbl => l_out_eam_res_tbl
1740 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
1741 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
1742 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
1743 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
1744 , x_eam_direct_items_tbl => l_out_eam_di_tbl
1748 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
1745 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
1746 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
1747 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
1749 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
1750 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
1751 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
1752 , x_eam_request_tbl => l_out_eam_request_tbl
1753 , x_return_status => x_return_status
1754 , x_msg_count => x_msg_count
1755 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
1756 , p_debug_filename => 'insertwdi.log'
1757 , p_output_dir => l_output_dir
1758 , p_debug_file_mode => 'w'
1759 );
1760
1761
1762
1763 end if;
1764
1765 IF(x_return_status<>'S') THEN
1766 ROLLBACK TO get_insert_into_wro_pvt;
1767 END IF;
1768
1769 -- End of API body.
1770 -- Standard check of p_commit.
1771 IF fnd_api.to_boolean(p_commit)
1772 and x_return_status = 'S' THEN
1773 COMMIT WORK;
1774 END IF;
1775
1776 l_stmt_num := 999;
1777 -- Standard call to get message count and if count is 1, get message info.
1778 fnd_msg_pub.count_and_get(
1779 p_count => x_msg_count
1780 ,p_data => x_msg_data);
1781 EXCEPTION
1782 WHEN fnd_api.g_exc_error THEN
1783 ROLLBACK TO get_insert_into_wro_pvt;
1784 x_return_status := fnd_api.g_ret_sts_error;
1785 fnd_msg_pub.count_and_get(
1786 -- p_encoded => FND_API.g_false
1787 p_count => x_msg_count
1788 ,p_data => x_msg_data);
1789 WHEN fnd_api.g_exc_unexpected_error THEN
1790 ROLLBACK TO get_insert_into_wro_pvt;
1791 x_return_status := fnd_api.g_ret_sts_unexp_error;
1792
1793 fnd_msg_pub.count_and_get(
1794 p_count => x_msg_count
1795 ,p_data => x_msg_data);
1796 WHEN OTHERS THEN
1797 ROLLBACK TO get_insert_into_wro_pvt;
1798 x_return_status := fnd_api.g_ret_sts_unexp_error;
1799 IF fnd_msg_pub.check_msg_level(
1800 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1801 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1802 END IF;
1803
1804 fnd_msg_pub.count_and_get(
1805 p_count => x_msg_count
1806 ,p_data => x_msg_data);
1807
1808 END insert_into_wdi;
1809
1810
1811
1812
1813 Function Dynamic_Entry_Not_Allowed(
1814 p_restrict_flag IN NUMBER,
1815 p_neg_flag IN NUMBER,
1816 p_action IN NUMBER) return Boolean IS
1817 Begin
1818 if(p_restrict_flag = 2 or p_restrict_flag = null) then
1819 if(p_neg_flag = 2) then
1820 if(p_action = 1 or p_action = 2 or p_action = 3 or
1821 p_action = 21 or p_action = 30 or p_action = 32) then
1822 return TRUE;
1823 end if;
1824 else
1825 return FALSE;
1826 end if; -- end of neg_flag check
1827 elsif(p_restrict_flag = 1) then
1828 return TRUE;
1829 end if;
1830 return TRUE;
1831 End Dynamic_Entry_Not_Allowed ;
1832
1833 Function Is_LocatorControlled(
1834 p_org IN NUMBER,
1835 p_subinv IN VARCHAR2,
1836 p_item_id IN NUMBER,
1837 p_action IN NUMBER
1838 ) return VARCHAR2 IS
1839 x_locator_ctrl NUMBER;
1840 x_error_flag NUMBER;
1841 x_error_mssg VARCHAR2(250);
1842 Begin
1843 Get_LocatorControl_Code(
1844 p_org,
1845 p_subinv,
1846 p_item_id,
1847 p_action,
1848 x_locator_ctrl,
1849 x_error_flag,
1850 x_error_mssg);
1851
1852 if(x_locator_ctrl IN (2,3)) then
1853 return 'Y';
1854 else
1855 return 'N';
1856 end if;
1857 End Is_LocatorControlled;
1858
1859
1860 End eam_mtl_txn_process; -- end of eam_mtl_txt_process package
1861