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