[Home] [Help]
PACKAGE BODY: APPS.WIP_UTILITIES
Source
1 PACKAGE BODY WIP_UTILITIES AS
2 /* $Header: wiputilb.pls 120.11.12010000.2 2008/10/06 17:00:28 hliew ship $ */
3
4 procedure do_sql(p_sql_stmt in varchar2) is
5 cursor_id integer;
6 return_val integer;
7 sql_stmt varchar2(8192);
8 begin
9 -- set sql statement
10 sql_stmt := p_sql_stmt;
11
12 -- open a cursor
13 cursor_id := dbms_sql.open_cursor;
14
15 -- parse sql statement
16 dbms_sql.parse(cursor_id, sql_stmt, DBMS_SQL.V7);
17
18 -- execute statement
19 return_val := dbms_sql.execute(cursor_id);
20
21 -- close cursor
22 dbms_sql.close_cursor(cursor_id);
23 end do_sql;
24
25 Function is_status_applicable(p_trx_status_enabled IN NUMBER :=NULL,
26 p_trx_type_id IN NUMBER :=NULL,
27 p_lot_status_enabled IN VARCHAR2 :=NULL,
28 p_serial_status_enabled IN VARCHAR2 :=NULL,
29 p_organization_id IN NUMBER :=NULL,
30 p_inventory_item_id IN NUMBER :=NULL,
31 p_sub_code IN VARCHAR2 :=NULL,
32 p_locator_id IN NUMBER :=NULL,
33 p_lot_number IN VARCHAR2 :=NULL,
34 p_serial_number IN VARCHAR2 :=NULL,
35 p_object_type IN VARCHAR2 :=NULL)
36 return varchar2 is
37 l_return_status VARCHAR2(1);
38 l_msg_count NUMBER;
39 l_msg_data VARCHAR2(256);
40
41 BEGIN
42 IF not wms_install.check_install(l_return_status,
43 l_msg_count,
44 l_msg_data,
45 NULL ) then
46 return 'Y';
47 END IF;
48
49 return INV_MATERIAL_STATUS_GRP.is_status_applicable('TRUE', p_trx_status_enabled, p_trx_type_id,
50 p_lot_status_enabled, p_serial_status_enabled, p_organization_id, p_inventory_item_id,
51 p_sub_code, p_locator_id, p_lot_number, p_serial_number, p_object_type);
52
53 exception
54 when others then
55 return 'Y';
56 END is_status_applicable;
57
58 /*******************************************************************
59 * This is the wrapper to call the WMS label printing routine
60 * This one should be used instead of the print_label if called from
61 * java. The p_err_msg basically is a concatenated version of all
62 * the error message on the stack if an error is returned from WMS.
63 ******************************************************************/
64 procedure print_label_java(p_txn_id IN NUMBER,
65 p_table_type IN NUMBER, -- 1 MTI, 2 MMTT
66 p_ret_status OUT NOCOPY VARCHAR2,
67 p_err_msg OUT NOCOPY VARCHAR2,
68 p_business_flow_code IN NUMBER) IS
69 l_msg_count number;
70 l_label_status varchar2(30);
71 l_msg_data varchar2(240);
72 l_params wip_logger.param_tbl_t;
73 l_returnStatus varchar2(1);
74 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
75 begin
76 p_ret_status := fnd_api.g_ret_sts_success;
77
78 if (l_logLevel <= wip_constants.trace_logging) then
79 l_params(1).paramName := 'p_txn_id';
80 l_params(1).paramValue := p_txn_id;
81 l_params(2).paramName := 'p_table_type';
82 l_params(2).paramValue := p_table_type;
83 l_params(3).paramName := 'p_business_flow_code';
84 l_params(3).paramValue := p_business_flow_code;
85 wip_logger.entryPoint(p_procName => 'wip_utils.print_label_java',
86 p_params => l_params,
87 x_returnStatus => p_ret_status);
88 if(p_ret_status <> fnd_api.g_ret_sts_success) then
89 raise fnd_api.g_exc_unexpected_error;
90 end if;
91 end if;
92
93 print_label(p_txn_id => p_txn_id,
94 p_table_type => p_table_type,
95 p_ret_status => p_ret_status,
96 p_msg_count => l_msg_count,
97 p_msg_data => l_msg_data,
98 p_label_status => l_label_status,
99 p_business_flow_code => p_business_flow_code);
100
101 -- if error, pack the message into p_err_msg for java
102 if (p_ret_status <> 'S') then
103 get_message_stack(p_msg => p_err_msg);
104 end if;
105
106 if (l_logLevel <= wip_constants.trace_logging) then
107 wip_logger.exitPoint(p_procName => 'wip_utils.print_label_java',
108 p_procReturnStatus => p_ret_status,
109 p_msg => p_err_msg,
110 x_returnStatus => l_returnStatus); --discard logging return status
111 end if;
112 end print_label_java;
113
114 /*******************************************************************
115 * This is the wrapper to call the WMS label printing routine
116 ******************************************************************/
117 procedure print_label(p_txn_id IN NUMBER,
118 p_table_type IN NUMBER, -- 1 MTI, 2 MMTT
119 p_ret_status OUT NOCOPY VARCHAR2,
120 p_msg_count OUT NOCOPY NUMBER,
121 p_msg_data OUT NOCOPY VARCHAR2,
122 p_label_status OUT NOCOPY VARCHAR2,
123 p_business_flow_code IN NUMBER) IS
124 -- only want to retrieve the assembly completion records
125 -- no label printing for return
126 cursor get_mmtt(x_txn_header_id number) is
127 select transaction_temp_id, rowid
128 from mtl_material_transactions_temp
129 where transaction_header_id = x_txn_header_id
130 and transaction_source_type_id = 5
131 and transaction_action_id = WIP_CONSTANTS.CPLASSY_ACTION;
132 cursor get_mti(x_txn_header_id number) is
133 select transaction_interface_id, rowid
134 from mtl_transactions_interface
135 where transaction_header_id = x_txn_header_id
136 and transaction_source_type_id = 5
137 and transaction_action_id = WIP_CONSTANTS.CPLASSY_ACTION;
138 l_temp_id number;
139
140 l_params wip_logger.param_tbl_t;
141 l_returnStatus varchar2(1);
142 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
143 begin
144 p_ret_status := fnd_api.g_ret_sts_success;
145
146 if (l_logLevel <= wip_constants.trace_logging) then
147 l_params(1).paramName := 'p_txn_id';
148 l_params(1).paramValue := p_txn_id;
149 l_params(2).paramName := 'p_table_type';
150 l_params(2).paramValue := p_table_type;
151 l_params(3).paramName := 'p_business_flow_code';
152 l_params(3).paramValue := p_business_flow_code;
153 wip_logger.entryPoint(p_procName => 'wip_utils.print_label',
154 p_params => l_params,
155 x_returnStatus => p_ret_status);
156 if(p_ret_status <> fnd_api.g_ret_sts_success) then
157 raise fnd_api.g_exc_unexpected_error;
158 end if;
159 end if;
160
161 if (p_table_type = 1) then -- mti
162 for inv_rec in get_mti(p_txn_id) loop
163 if (inv_rec.transaction_interface_id is null) then
164 -- transaction interface id not assigned to mti, probably no lot/serial
165 -- generate one
166 select mtl_material_transactions_s.nextval into l_temp_id
167 from sys.dual;
168 update mtl_transactions_interface
169 set transaction_interface_id = l_temp_id
170 where rowid = inv_rec.rowid;
171 else
172 l_temp_id := inv_rec.transaction_interface_id;
173 end if;
174
175 -- the p_txn_identifier is defined in INVLA10B.pls
176 -- 1 is MMTT, 2 is MTI, 3 MTRL, 4 WFS
177 INV_LABEL.print_label_wrap(x_return_status => p_ret_status,
178 x_msg_count => p_msg_count,
179 x_msg_data => p_msg_data,
180 x_label_status => p_label_status,
181 p_business_flow_code => p_business_flow_code,
182 p_transaction_id => l_temp_id,
183 p_transaction_identifier => 2); -- interface
184 end loop;
185 elsif (p_table_type = 2) then -- mmtt
186 for inv_rec in get_mmtt(p_txn_id) loop
187 if (inv_rec.transaction_temp_id is null) then
188 -- temp id not assigned to mmtt record (probably no lot/serial),
189 -- generate one
190 select mtl_material_transactions_s.nextval into l_temp_id
191 from sys.dual;
192 update mtl_material_transactions_temp
193 set transaction_temp_id = l_temp_id
194 where rowid = inv_rec.rowid;
195 else
196 l_temp_id := inv_rec.transaction_temp_id;
197 end if;
198
199 INV_LABEL.print_label_wrap(x_return_status => p_ret_status,
200 x_msg_count => p_msg_count,
201 x_msg_data => p_msg_data,
202 x_label_status => p_label_status,
203 p_business_flow_code => p_business_flow_code,
204 p_transaction_id => l_temp_id,
205 p_transaction_identifier => 1); -- MMTT
206 end loop;
207 end if;
208
209 if (l_logLevel <= wip_constants.trace_logging) then
210 wip_logger.exitPoint(p_procName => 'wip_utils.print_label',
211 p_procReturnStatus => p_ret_status,
212 p_msg => p_msg_data,
213 x_returnStatus => l_returnStatus); --discard logging return status
214 end if;
215
216 END print_label;
217
218 procedure print_label(p_business_flow_code IN NUMBER := NULL,
219 p_label_type_id IN NUMBER := NULL,
220 p_organization_id IN NUMBER := NULL,
221 p_inventory_item_id IN NUMBER := NULL,
222 p_revision IN VARCHAR2 := NULL,
223 p_lot_number IN VARCHAR2 := NULL,
224 p_fm_serial_number IN VARCHAR2 := NULL,
225 p_to_serial_number IN VARCHAR2 := NULL,
226 p_lpn_id IN NUMBER := NULL,
227 p_subinventory_code IN VARCHAR2 := NULL,
228 p_locator_id IN NUMBER := NULL,
229 p_delivery_id IN NUMBER := NULL,
230 p_quantity IN NUMBER := NULL,
231 p_uom IN VARCHAR2 := NULL,
232 p_no_of_copies IN NUMBER := NULL,
233 p_ret_status OUT NOCOPY VARCHAR2,
234 p_msg_count OUT NOCOPY NUMBER,
235 p_msg_data OUT NOCOPY VARCHAR2,
236 p_label_status OUT NOCOPY VARCHAR2) is
237 l_params wip_logger.param_tbl_t;
238 l_returnStatus varchar2(1);
239 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
240 begin
241 p_ret_status := fnd_api.g_ret_sts_success;
242
243 if (l_logLevel <= wip_constants.trace_logging) then
244 l_params(1).paramName := 'p_business_flow_code';
245 l_params(1).paramValue := p_business_flow_code;
246 l_params(2).paramName := 'p_label_type_id';
247 l_params(2).paramValue := p_label_type_id;
248 l_params(3).paramName := 'p_organization_id';
249 l_params(3).paramValue := p_organization_id;
250 l_params(4).paramName := 'p_inventory_item_id';
251 l_params(4).paramValue := p_inventory_item_id;
252 l_params(5).paramName := 'p_revision';
253 l_params(5).paramValue := p_revision;
254 l_params(6).paramName := 'p_lot_number';
255 l_params(6).paramValue := p_lot_number;
256 l_params(7).paramName := 'p_fm_serial_number';
257 l_params(7).paramValue := p_fm_serial_number;
258 l_params(8).paramName := 'p_to_serial_number';
259 l_params(8).paramValue := p_to_serial_number;
260 l_params(9).paramName := 'p_lpn_id';
261 l_params(9).paramValue := p_lpn_id;
262 l_params(10).paramName := 'p_subinventory_code';
263 l_params(10).paramValue := p_subinventory_code;
264 l_params(11).paramName := 'p_locator_id';
265 l_params(11).paramValue := p_locator_id;
266 l_params(12).paramName := 'p_delivery_id';
267 l_params(12).paramValue := p_delivery_id;
268 l_params(13).paramName := 'p_quantity';
269 l_params(13).paramValue := p_quantity;
270 l_params(14).paramName := 'p_uom';
271 l_params(14).paramValue := p_uom;
272 l_params(15).paramName := 'p_no_of_copies';
273 l_params(15).paramValue := p_no_of_copies;
274 wip_logger.entryPoint(p_procName => 'wip_utils.print_label',
275 p_params => l_params,
276 x_returnStatus => p_ret_status);
277 if(p_ret_status <> fnd_api.g_ret_sts_success) then
278 raise fnd_api.g_exc_unexpected_error;
279 end if;
280 end if;
281
282 inv_label.print_label_manual_wrap(p_business_flow_code => p_business_flow_code,
283 p_label_type => p_label_type_id,
284 p_organization_id => p_organization_id,
285 p_inventory_item_id => p_inventory_item_id,
286 p_revision => p_revision,
287 p_lot_number => p_lot_number,
288 p_fm_serial_number => p_fm_serial_number,
289 p_to_serial_number => p_to_serial_number,
290 p_lpn_id => p_lpn_id,
291 p_subinventory_code => p_subinventory_code,
292 p_locator_id => p_locator_id,
293 p_delivery_id => p_delivery_id,
294 p_quantity => p_quantity,
295 p_uom => p_uom,
296 p_no_of_copies => p_no_of_copies,
297 x_return_status => p_ret_status,
298 x_msg_count => p_msg_count,
299 x_msg_data => p_msg_data,
300 x_label_status => p_label_status);
301
302 if (l_logLevel <= wip_constants.trace_logging) then
303 wip_logger.exitPoint(p_procName => 'wip_utils.print_label',
304 p_procReturnStatus => p_ret_status,
305 p_msg => p_msg_data,
306 x_returnStatus => l_returnStatus); --discard logging return status
307 end if;
308 end print_label;
309
310 procedure get_message_stack(p_delete_stack in varchar2 := null,
311 p_separator in varchar2 := null,
312 p_msg OUT NOCOPY VARCHAR2) is
313 l_curMsg VARCHAR2(2000) := '';
314 l_msgCount NUMBER;
315 l_separator VARCHAR2(30) := nvl(p_separator,' ');
316 begin
317 fnd_msg_pub.Count_And_Get(p_encoded => fnd_api.g_false,
318 p_count => l_msgCount,
319 p_data => p_msg);
320
321 IF(l_msgCount > 1) THEN
322 FOR i IN 1..l_msgCount LOOP
323 l_curMsg := fnd_msg_pub.get(p_msg_index => l_msgCount - i + 1,
324 p_encoded => FND_API.g_false);
325 if(nvl(length(p_msg), 0) + length(l_curMsg) + length(l_separator) < 2000) then
326 p_msg := p_msg || l_separator || l_curMsg;
327 end if;
328 END LOOP;
329 END IF;
330
331 if(fnd_api.to_boolean(nvl(p_delete_stack,fnd_api.g_true))) then
332 fnd_msg_pub.delete_msg;
333 end if;
334
335 EXCEPTION
336 WHEN OTHERS THEN
337 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_utilities',
338 p_procedure_name => 'get_message_stack',
339 p_error_text => SQLERRM);
340 p_msg := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
341 if(fnd_api.to_boolean(nvl(p_delete_stack,fnd_api.g_true))) then
342 fnd_msg_pub.delete_msg;
343 end if;
344 END get_message_stack;
345
346 /* Deletes transaction records from the mtl temp tables */
347 procedure delete_temp_records(p_header_id IN NUMBER) is
348 begin
349 -- Delete all serial numbers tied to lots
350 delete from mtl_serial_numbers_temp
351 where transaction_temp_id in
352 ( select msnt.transaction_temp_id
353 from mtl_serial_numbers_temp msnt,
354 mtl_transaction_lots_temp mtlt,
355 mtl_material_transactions_temp mmtt
356 where mmtt.transaction_header_id = p_header_id
357 and mtlt.transaction_temp_id = mmtt.transaction_temp_id
358 and mtlt.serial_transaction_temp_id = msnt.transaction_temp_id
359 );
360
361 -- Delete all serial numbers (sn control only)
362 delete from mtl_serial_numbers_temp
363 where transaction_temp_id in
364 ( select msnt.transaction_temp_id
365 from mtl_serial_numbers_temp msnt,
366 mtl_material_transactions_temp mmtt
367 where mmtt.transaction_header_id = p_header_id
368 and mmtt.transaction_temp_id = msnt.transaction_temp_id
369 );
370
371 -- Delete all lots
372 delete from mtl_transaction_lots_temp
373 where transaction_temp_id in
374 ( select mtlt.transaction_temp_id
375 from mtl_material_transactions_temp mmtt,
376 mtl_transaction_lots_temp mtlt
377 where mmtt.transaction_header_id = p_header_id
378 and mtlt.transaction_temp_id = mmtt.transaction_temp_id
379 );
380
381 -- Finally, delete all records in mmtt for this transaction
382 delete from mtl_material_transactions_temp
383 where transaction_header_id = p_header_id;
384
385 end delete_temp_records;
386
387 /* Deletes transaction records from the mtl temp tables */
388 procedure delete_temp_records(p_temp_id IN NUMBER) is
389 begin
390 -- Delete all serial numbers tied to lots
391 delete from mtl_serial_numbers_temp msnt
392 where transaction_temp_id in
393 (select mtlt.serial_transaction_temp_id
394 from mtl_transaction_lots_temp mtlt
395 where mtlt.transaction_temp_id = p_temp_id
396 );
397
398 -- Delete all serial numbers (sn control only)
399 delete from mtl_serial_numbers_temp
400 where transaction_temp_id = p_temp_id;
401
402 -- Delete all lots
403 delete from mtl_transaction_lots_temp
404 where transaction_temp_id = p_temp_id;
405
406 -- Finally, delete all records in mmtt for this transaction
407 delete from mtl_material_transactions_temp
408 where transaction_header_id = p_temp_id;
409 end delete_temp_records;
410
411 procedure update_serial(p_serial_number in VARCHAR2,
412 p_inventory_item_id in number,
413 p_organization_id in number,
414 p_wip_entity_id in number,
415 p_line_mark_id in number := null,
416 p_operation_seq_num in number,
417 p_intraoperation_step_type in number,
418 x_return_status OUT NOCOPY VARCHAR2) is
419 l_objID NUMBER;
420 l_msg_data VARCHAR2(2000);
421 l_msg_count VARCHAR2(2000);
422 l_current_status NUMBER;
423 l_initialization_date DATE;
424 l_completion_date DATE;
425 l_ship_date DATE;
426 l_revision VARCHAR2(3);
427 /* ER 4378835: Increased length of lot_number from 30 to 80 to support OPM Lot-model changes */
428 l_lot_number VARCHAR2(80);
429 l_group_mark_id NUMBER;
430 l_lot_line_mark_id NUMBER;
431 l_current_organization_id NUMBER;
432 l_current_locator_id NUMBER;
433 l_current_subinventory_code VARCHAR2(30);
434 l_original_wip_entity_id NUMBER;
435 l_original_unit_vendor_id NUMBER;
436 l_vendor_lot_number VARCHAR2(80);
437 l_vendor_serial_number VARCHAR2(30);
438 l_last_receipt_issue_type NUMBER;
439 l_last_txn_source_id NUMBER;
440 l_last_txn_source_type_id NUMBER;
441 l_last_txn_source_name VARCHAR2(30);
442 l_parent_item_id NUMBER;
443 l_parent_serial_number VARCHAR2(30);
444 l_dummy VARCHAR2(1);
445 l_last_status NUMBER;
446 begin
447 savepoint wipupdserial0;
448 --check for existence of serial number while locking it
449 select current_status,
450 initialization_date,
451 completion_date,
452 ship_date,
453 revision,
454 lot_number,
455 group_mark_id,
456 lot_line_mark_id,
457 current_organization_id,
458 current_locator_id,--10
459 current_subinventory_code,
460 original_wip_entity_id,
461 original_unit_vendor_id,
462 vendor_lot_number,
463 vendor_serial_number,
464 last_receipt_issue_type,
465 last_txn_source_id,
466 last_txn_source_type_id,
467 last_txn_source_name,
468 parent_item_id,--20
469 parent_serial_number
470 into l_current_status,
471 l_initialization_date,
472 l_completion_date,
473 l_ship_date,
474 l_revision,
475 l_lot_number,
476 l_group_mark_id,
477 l_lot_line_mark_id,
478 l_current_organization_id,
479 l_current_locator_id,--10
480 l_current_subinventory_code,
481 l_original_wip_entity_id,
482 l_original_unit_vendor_id,
483 l_vendor_lot_number,
484 l_vendor_serial_number,
485 l_last_receipt_issue_type,
486 l_last_txn_source_id,
487 l_last_txn_source_type_id,
488 l_last_txn_source_name,
489 l_parent_item_id,--20
490 l_parent_serial_number
491 from mtl_serial_numbers
492 where serial_number = p_serial_number
493 and inventory_item_id = p_inventory_item_id
494 and current_organization_id = p_organization_id
495 for update nowait;
496
497 if(l_current_status = 6) then
498 l_last_status := 1;
499 else
500 l_last_status := l_current_status;
501 end if;
502
503 inv_serial_number_pub.updateserial(p_api_version => 1.0,
504 p_inventory_item_id => p_inventory_item_id,
505 p_organization_id => p_organization_id,
506 p_serial_number => p_serial_number,
507 p_initialization_date => l_initialization_date,
508 p_completion_date => l_completion_date,
509 p_ship_date => l_ship_date,
510 p_revision => l_revision,
511 p_lot_number => l_lot_number,
512 p_current_locator_id => l_current_locator_id,
513 p_subinventory_code => l_current_subinventory_code,
514 p_trx_src_id => l_original_wip_entity_id,
515 p_unit_vendor_id => l_original_unit_vendor_id,
516 p_vendor_lot_number => l_vendor_lot_number,
517 p_vendor_serial_number => l_vendor_serial_number,
518 p_receipt_issue_type => l_last_receipt_issue_type,
519 p_txn_src_id => l_last_txn_source_id,
520 p_txn_src_name => l_last_txn_source_name,
521 p_txn_src_type_id => l_last_txn_source_type_id,
522 p_current_status => l_current_status,
523 p_parent_item_id => l_parent_item_id,
524 p_parent_serial_number => l_parent_serial_number,
525 p_serial_temp_id => null,
526 p_last_status => l_last_status,
527 p_status_id => null,
528 x_object_id => l_objID,
529 x_return_status => x_return_status,
530 x_msg_count => l_msg_count,
531 x_msg_data => l_msg_data,
532 p_wip_entity_id => p_wip_entity_id,
533 p_operation_seq_num => p_operation_seq_num,
534 p_intraoperation_step_type => p_intraoperation_step_type,
535 p_line_mark_id => p_line_mark_id);
536
537 if(x_return_status <> fnd_api.g_ret_sts_success) then
538 raise fnd_api.g_exc_unexpected_error;
539 end if;
540 exception
541 when wip_constants.records_locked then
542 rollback to wipupdserial0;
543 x_return_status := fnd_api.g_ret_sts_unexp_error;
544 fnd_message.set_name('WIP', 'SERIAL_NUMBERS_LOCKED');
545 fnd_msg_pub.add;
546 when fnd_api.g_exc_unexpected_error then
547 rollback to wipupdserial0;
548 --status and message should have been set by inv api. set status
549 --just in case.
550 x_return_status := fnd_api.g_ret_sts_unexp_error;
551
552 when others then
553 rollback to wipupdserial0;
554 x_return_status := fnd_api.g_ret_sts_unexp_error;
555 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_utilities',
556 p_procedure_name => 'update_serial',
557 p_error_text => SQLERRM);
558 end update_serial;
559
560 procedure generate_serials(p_org_id in NUMBER,
561 p_item_id in NUMBER,
562 p_qty IN NUMBER,
563 p_wip_entity_id IN NUMBER,
564 p_revision in VARCHAR2,
565 p_lot in varchar2,
566 x_start_serial IN OUT NOCOPY VARCHAR2,
567 x_end_serial OUT NOCOPY VARCHAR2,
568 x_return_status OUT NOCOPY VARCHAR2,
569 x_err_msg OUT NOCOPY VARCHAR2) is
570 l_status NUMBER;
571 l_qty NUMBER := p_qty;
572 begin
573 x_return_status := fnd_api.g_ret_sts_success;
574 if(x_start_serial is null) then
575 l_status := inv_serial_number_pub.generate_serials(p_org_id => p_org_id,
576 p_item_id => p_item_id,
577 p_qty => p_qty,
578 p_wip_id => p_wip_entity_id,
579 p_group_mark_id => p_wip_entity_id,
580 p_line_mark_id => null,
581 p_rev => p_revision,
582 p_lot => p_lot,
583 p_skip_serial => wip_constants.yes,
584 x_start_ser => x_start_serial,
585 x_end_ser => x_end_serial,
586 x_proc_msg => x_err_msg);
587 else
588 l_qty := p_qty;
589 l_status := inv_serial_number_pub.validate_serials(p_org_id => p_org_id,
590 p_item_id => p_item_id,
591 p_qty => l_qty,
592 p_wip_entity_id => p_wip_entity_id,
593 p_group_mark_id => p_wip_entity_id,
594 p_line_mark_id => null,
595 p_rev => p_revision,
596 p_lot => p_lot,
597 p_start_ser => x_start_serial,
598 p_trx_src_id => null,
599 p_trx_action_id => null,
600 p_subinventory_code => null,
601 p_locator_id => null,
602 x_end_ser => x_end_serial,
603 x_proc_msg => x_err_msg);
604 if(l_qty <> p_qty AND
605 l_status = 0) then
606 fnd_message.set_name('INV', 'INVALID_SERIAL_NUMBER');
607 fnd_msg_pub.add;
608 get_message_stack(p_msg => x_err_msg,
609 p_delete_stack => fnd_api.g_false,
610 p_separator => ' ');
611 end if;
612 end if;
613 /* For Bug 5860709 : Returning 'W' for status 2-Warning*/
614 if(l_status = 2 ) then
615 x_return_status := WIP_CONSTANTS.WARN;
616 --if(l_status <> 0) then
617 elsif(l_status <> 0) then
618 x_return_status := fnd_api.g_ret_sts_error;
619 end if;
620 exception
621 when others then
622 x_return_status := fnd_api.g_ret_sts_unexp_error;
623 x_err_msg := SQLERRM;
624 end generate_serials;
625
626 FUNCTION require_lot_attributes(p_org_id IN NUMBER,
627 p_item_id IN NUMBER,
628 p_lot_number IN VARCHAR2)
629 RETURN NUMBER IS
630
631 l_require_lot_attr NUMBER;
632 BEGIN
633 SELECT inv_lot_sel_attr.is_enabled(
634 'Lot Attributes',
635 p_org_id,
636 p_item_id)
637 INTO l_require_lot_attr
638 FROM dual
639 WHERE NOT EXISTS -- new lot
640 (SELECT 'X'
641 FROM mtl_lot_numbers mln
642 WHERE mln.organization_id = p_org_id
643 AND mln.inventory_item_id = p_item_id
644 AND mln.lot_number = p_lot_number);
645
646 IF(l_require_lot_attr = 2) THEN
647 RETURN WIP_CONSTANTS.YES;
648 ELSE
649 RETURN WIP_CONSTANTS.NO;
650 END IF;
651
652 EXCEPTION
653 WHEN others THEN -- include NO_DATA_FOUND exception too
654 RETURN WIP_CONSTANTS.NO;
655 END require_lot_attributes;
656
657 PROCEDURE get_locator(p_locator_id IN NUMBER,
658 p_org_id IN NUMBER,
659 p_locator OUT NOCOPY VARCHAR2)
660 IS
661 BEGIN
662 p_locator := inv_project.get_locator(p_locator_id, p_org_id);
663 END get_locator;
664
665 FUNCTION is_user_defined_lot_exp(p_org_id IN NUMBER,
666 p_item_id IN NUMBER,
667 p_lot_number IN VARCHAR2)
668 RETURN NUMBER IS
669
670 l_shelf_life_code NUMBER;
671 BEGIN
672 SELECT msi.shelf_life_code
673 INTO l_shelf_life_code
674 FROM mtl_system_items msi
675 WHERE msi.inventory_item_id = p_item_id
676 AND msi.organization_id = p_org_id
677 AND NOT EXISTS -- new lot
678 (SELECT 'X'
679 FROM mtl_lot_numbers mln
680 WHERE mln.organization_id = p_org_id
681 AND mln.inventory_item_id = p_item_id
682 AND mln.lot_number = p_lot_number);
683
684 IF(l_shelf_life_code = WIP_CONSTANTS.USER_DEFINED_EXP) THEN
685 RETURN WIP_CONSTANTS.YES;
686 ELSE
687 RETURN WIP_CONSTANTS.NO;
688 END IF;
689
690 EXCEPTION
691 WHEN others THEN -- include NO_DATA_FOUND exception too
692 RETURN WIP_CONSTANTS.NO;
693 END is_user_defined_lot_exp;
694
695 FUNCTION is_dff_required(p_application_id IN NUMBER,
696 p_dff_name IN VARCHAR2)
697 RETURN VARCHAR2 IS
698
699 l_dff_required BOOLEAN;
700 BEGIN
701 l_dff_required := fnd_flex_apis.is_descr_required(
702 x_application_id => p_application_id,
703 x_desc_flex_name => p_dff_name);
704
705 IF(l_dff_required) THEN
706 RETURN fnd_api.g_true;
707 ELSE
708 RETURN fnd_api.g_false;
709 END IF;
710 END is_dff_required;
711
712 FUNCTION is_dff_setup(p_application_id IN NUMBER,
713 p_dff_name IN VARCHAR2)
714 RETURN VARCHAR2 IS
715
716 l_dff_setup BOOLEAN;
717 BEGIN
718 l_dff_setup := fnd_flex_apis.is_descr_setup(
719 x_application_id => p_application_id,
720 x_desc_flex_name => p_dff_name);
721
722 IF(l_dff_setup) THEN
723 RETURN fnd_api.g_true;
724 ELSE
725 RETURN fnd_api.g_false;
726 END IF;
727 END is_dff_setup;
728
729 /*Added the following function for bug 7138983(FP 7028072)*/
730 FUNCTION validate_scrap_account_id ( scrap_account_id IN NUMBER,
731 chart_of_accounts_id IN NUMBER )
732 RETURN VARCHAR2 IS
733 x_flex_result boolean;
734 BEGIN
735
736 x_flex_result := fnd_flex_keyval.validate_ccid(appl_short_name => 'SQLGL',
737 key_flex_code => 'GL#',
738 structure_number => chart_of_accounts_id,
739 combination_id => scrap_account_id,
740 security => 'ENFORCE'
741 );
742
743 if x_flex_result then
744 return 'Y';
745 else
746 return 'N';
747 end if;
748 EXCEPTION
749 WHEN OTHERS THEN
750 RETURN 'N';
751 END validate_scrap_account_id;
752
753
754
755
756
757
758 /**************************************************************************/
759 --VJ: Label Printing - Start
760
761 PROCEDURE print_job_labels(p_wip_entity_id IN NUMBER,
762 -- p_op_seq_num IN NUMBER,
763 x_status IN OUT NOCOPY VARCHAR2,
764 x_msg_count IN OUT NOCOPY NUMBER,
765 x_msg IN OUT NOCOPY VARCHAR2
766 )
767 IS
768 l_org_id NUMBER;
769 l_item_id NUMBER;
770 l_label_status VARCHAR2(30);
771 l_returnStatus VARCHAR2(1);
772 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
773 l_params wip_logger.param_tbl_t;
774 l_msg_data VARCHAR2(100);
775
776 CURSOR job_serials (p_org_id NUMBER, p_we_id NUMBER, p_item_id NUMBER)
777 IS
778 SELECT serial_number
779 FROM mtl_serial_numbers
780 WHERE current_organization_id = p_org_id
781 and wip_entity_id = p_we_id
782 AND inventory_item_id = p_item_id;
783
784 BEGIN
785 x_status := fnd_api.g_ret_sts_success;
786
787 if (l_logLevel <= wip_constants.trace_logging) then
788 l_params(1).paramName := 'p_wip_entity_id';
789 l_params(1).paramValue := p_wip_entity_id;
790 -- l_params(2).paramName := 'p_op_seq_num';
791 -- l_params(2).paramValue := p_op_seq_num;
792 l_params(2).paramName := 'x_status';
793 l_params(2).paramValue := x_status;
794 l_params(3).paramName := 'x_msg_count';
795 l_params(3).paramValue := x_msg_count;
796 l_params(4).paramName := 'x_msg';
797 l_params(4).paramValue := x_msg;
798 wip_logger.entryPoint(p_procName => 'wip_utils.print_job_labels',
799 p_params => l_params,
800 x_returnStatus => x_status);
801 if(x_status <> fnd_api.g_ret_sts_success) then
802 raise fnd_api.g_exc_unexpected_error;
803 end if;
804 end if;
805
806 select organization_id, primary_item_id
807 into l_org_id, l_item_id
808 from wip_discrete_jobs
809 where wip_entity_id = p_wip_entity_id;
810
811 INV_LABEL.PRINT_LABEL_MANUAL_WRAP (
812 P_BUSINESS_FLOW_CODE => NULL,
813 P_LABEL_TYPE => 9,
814 P_ORGANIZATION_ID => l_org_id,
815 P_INVENTORY_ITEM_ID => NULL,
816 P_REVISION => NULL,
817 P_LOT_NUMBER => NULL,
818 P_FM_SERIAL_NUMBER => NULL,
819 P_TO_SERIAL_NUMBER => NULL,
820 P_LPN_ID => NULL,
821 P_SUBINVENTORY_CODE => NULL,
822 P_LOCATOR_ID => NULL,
823 P_DELIVERY_ID => NULL,
824 P_QUANTITY => NULL,
825 P_UOM => NULL,
826 P_WIP_ENTITY_ID => p_wip_entity_id,
827 P_NO_OF_COPIES => NULL,
828 X_RETURN_STATUS => x_status,
829 X_MSG_COUNT => x_msg_count,
830 X_MSG_DATA => l_msg_data,
831 X_LABEL_STATUS => l_label_status);
832 if (l_logLevel <= wip_constants.trace_logging) then
833 wip_logger.log(p_msg => 'INV_LABEL.PRINT_LABEL_MANUAL_WRAP results',
834 x_returnStatus => l_returnStatus); --discard logging return status
835 wip_logger.log(p_msg => x_status,
836 x_returnStatus => l_returnStatus); --discard logging return status
837 wip_logger.log(p_msg => l_msg_data,
838 x_returnStatus => l_returnStatus); --discard logging return status
839 end if;
840
841
842 FOR serial_num in job_serials(l_org_id,p_wip_entity_id,l_item_id) LOOP
843
844 -- Start : Changes to fix bug #6860138 --
845 /***********
846 INV_LABEL.PRINT_LABEL_MANUAL_WRAP (
847 P_BUSINESS_FLOW_CODE => NULL,
848 P_LABEL_TYPE => 2,
849 P_ORGANIZATION_ID => l_org_id,
850 P_INVENTORY_ITEM_ID => l_item_id,
851 P_REVISION => NULL,
852 P_LOT_NUMBER => NULL,
853 P_FM_SERIAL_NUMBER => serial_num.serial_number,
854 P_TO_SERIAL_NUMBER => serial_num.serial_number,
855 P_LPN_ID => NULL,
856 P_SUBINVENTORY_CODE => NULL,
857 P_LOCATOR_ID => NULL,
858 P_DELIVERY_ID => NULL,
859 P_QUANTITY => NULL,
860 P_UOM => NULL,
861 P_WIP_ENTITY_ID => p_wip_entity_id,
862 P_NO_OF_COPIES => NULL,
863 X_RETURN_STATUS => x_status,
864 X_MSG_COUNT => x_msg_count,
865 X_MSG_DATA => l_msg_data,
866 X_LABEL_STATUS => l_label_status);
867 ***********/
868
869 print_serial_label(p_org_id => l_org_id,
870 p_serial_number => serial_num.serial_number,
871 p_item_id => l_item_id,
872 x_status => x_status,
873 x_msg_count => x_msg_count,
874 x_msg => l_msg_data
875 );
876 -- End : Changes to fix bug #6860138 --
877
878 if (l_logLevel <= wip_constants.trace_logging) then
879 wip_logger.log(p_msg => 'INV_LABEL.PRINT_LABEL_MANUAL_WRAP results for serial '||serial_num.serial_number,
880 x_returnStatus => l_returnStatus); --discard logging return status
881 wip_logger.log(p_msg => x_status,
882 x_returnStatus => l_returnStatus); --discard logging return status
883 wip_logger.log(p_msg => l_msg_data,
884 x_returnStatus => l_returnStatus); --discard logging return status
885 end if;
886
887 END LOOP;
888
889 if (l_logLevel <= wip_constants.trace_logging) then
890 wip_logger.exitPoint(p_procName => 'wip_utils.print_job_labels',
891 p_procReturnStatus => x_status,
892 p_msg => l_msg_data,
893 x_returnStatus => l_returnStatus); --discard logging return status
894 end if;
895 END print_job_labels;
896
897 /**************************************************************************/
898
899 PROCEDURE print_serial_label(p_org_id IN NUMBER,
900 p_serial_number IN VARCHAR2,
901 p_item_id IN NUMBER,
902 x_status IN OUT NOCOPY VARCHAR2,
903 x_msg_count IN OUT NOCOPY NUMBER,
904 x_msg IN OUT NOCOPY VARCHAR2
905 )
906 IS
907 l_wip_entity_id NUMBER;
908 l_item_id NUMBER;
909 l_label_status VARCHAR2(30);
910 l_msg_data VARCHAR2(100);
911 l_returnStatus VARCHAR2(1);
912 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
913 l_params wip_logger.param_tbl_t;
914
915 -- Start : Changes to fix bug #6860138 --
916 l_lot_number VARCHAR2(80);
917 l_sch_st_date DATE;
918 l_item_rev VARCHAR2(3);
919 -- End : Changes to fix bug #6860138 --
920
921 BEGIN
922 x_status := fnd_api.g_ret_sts_success;
923
924 if (l_logLevel <= wip_constants.trace_logging) then
925 l_params(1).paramName := 'p_org_id';
926 l_params(1).paramValue := p_org_id;
927 l_params(2).paramName := 'p_serial_number';
928 l_params(2).paramValue := p_serial_number;
929 l_params(3).paramName := 'p_item_id';
930 l_params(3).paramValue := p_item_id;
931 l_params(4).paramName := 'x_status';
932 l_params(4).paramValue := x_status;
933 l_params(5).paramName := 'x_msg_count';
934 l_params(5).paramValue := x_msg_count;
935 l_params(6).paramName := 'x_msg';
936 l_params(6).paramValue := x_msg;
937 wip_logger.entryPoint(p_procName => 'wip_utils.print_serial_label',
938 p_params => l_params,
939 x_returnStatus => x_status);
940 if(x_status <> fnd_api.g_ret_sts_success) then
941 raise fnd_api.g_exc_unexpected_error;
942 end if;
943 end if;
944
945 l_item_id := p_item_id;
946
947 SELECT wip_entity_id, inventory_item_id
948 INTO l_wip_entity_id, l_item_id
949 FROM mtl_serial_numbers
950 WHERE current_organization_id = p_org_id
951 AND serial_number = p_serial_number
952 AND inventory_item_id = nvl(l_item_id, inventory_item_id);
953
954 -- For completed serial number, wip_entity_id in mtl_serial_numbers will
955 -- be null. Link mtl_object_genealogy to get wip_entity_id
956 if(l_wip_entity_id is null) then
957 SELECT we.wip_entity_id
958 INTO l_wip_entity_id
959 FROM mtl_serial_numbers msn,
960 wip_entities we,
961 mtl_object_genealogy mog
962 WHERE
963 ((mog.genealogy_origin = 1 and
964 mog.parent_object_id = we.gen_object_id and
965 mog.object_id = msn.gen_object_id)
966 or
967 (mog.genealogy_origin = 2 and
968 mog.parent_object_id = msn.gen_object_id and
969 mog.object_id = we.gen_object_id))
970 and mog.end_date_active is null
971 and msn.serial_number = p_serial_number
972 and msn.current_organization_id = p_org_id;
973 end if;
974
975 -- Start : Changes to fix bug #6860138 --
976 SELECT wdj.lot_number,
977 wdj.scheduled_start_date,
978 DECODE(msi.revision_qty_control_code,
979 WIP_CONSTANTS.REV,
980 NVL(wdj.bom_revision,
981 BOM_revisions.GET_ITEM_REVISION_FN
982 ('EXCLUDE_OPEN_HOLD',-- eco_status
983 'ALL', -- examine_type
984 p_org_id, -- org_id
985 l_item_id, -- item_id
986 l_sch_st_date) -- rev_date
987 ),
988 NULL
989 )
990 INTO l_lot_number,
991 l_sch_st_date,
992 l_item_rev
993 FROM mtl_system_items msi,
994 wip_discrete_jobs wdj
995 WHERE wdj.wip_entity_id = l_wip_entity_id
996 AND msi.organization_id = wdj.organization_id
997 AND msi.inventory_item_id = wdj.primary_item_id;
998 -- End : Changes to fix bug #6860138 --
999
1000 INV_LABEL.PRINT_LABEL_MANUAL_WRAP (
1001 P_BUSINESS_FLOW_CODE => NULL,
1002 P_LABEL_TYPE => 2,
1003 P_ORGANIZATION_ID => p_org_id,
1004 P_INVENTORY_ITEM_ID => l_item_id,
1005 P_REVISION => l_item_rev, --Fixed bug#6860138 --NULL,
1006 P_LOT_NUMBER => l_lot_number, --Fixed bug#6860138 --NULL,
1007 P_FM_SERIAL_NUMBER => p_serial_number,
1008 P_TO_SERIAL_NUMBER => p_serial_number,
1009 P_LPN_ID => NULL,
1010 P_SUBINVENTORY_CODE => NULL,
1011 P_LOCATOR_ID => NULL,
1012 P_DELIVERY_ID => NULL,
1013 P_QUANTITY => NULL,
1014 P_UOM => NULL,
1015 P_WIP_ENTITY_ID => l_wip_entity_id,
1016 P_NO_OF_COPIES => NULL,
1017 X_RETURN_STATUS => x_status,
1018 X_MSG_COUNT => x_msg_count,
1019 X_MSG_DATA => l_msg_data,
1020 X_LABEL_STATUS => l_label_status);
1021
1022 if (l_logLevel <= wip_constants.trace_logging) then
1023 wip_logger.exitPoint(p_procName => 'wip_utils.print_serial_label',
1024 p_procReturnStatus => x_status,
1025 p_msg => l_msg_data,
1026 x_returnStatus => l_returnStatus); --discard logging return status
1027 end if;
1028 END print_serial_label;
1029
1030 /**************************************************************************/
1031
1032 PROCEDURE print_move_txn_label(p_txn_id IN NUMBER,
1033 x_status IN OUT NOCOPY VARCHAR2,
1034 x_msg_count IN OUT NOCOPY NUMBER,
1035 x_msg IN OUT NOCOPY VARCHAR2
1036 )
1037 IS
1038 l_label_status VARCHAR2(30);
1039 l_returnStatus VARCHAR2(1);
1040 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1041 l_params wip_logger.param_tbl_t;
1042 l_msg_data VARCHAR2(100);
1043
1044 BEGIN
1045 x_status := fnd_api.g_ret_sts_success;
1046
1047 if (l_logLevel <= wip_constants.trace_logging) then
1048 l_params(1).paramName := 'p_txn_id';
1049 l_params(1).paramValue := p_txn_id;
1050 l_params(2).paramName := 'x_status';
1051 l_params(2).paramValue := x_status;
1052 l_params(3).paramName := 'x_msg_count';
1053 l_params(3).paramValue := x_msg_count;
1054 l_params(4).paramName := 'x_msg';
1055 l_params(4).paramValue := x_msg;
1056 wip_logger.entryPoint(p_procName => 'wip_utils.print_move_txn_label',
1057 p_params => l_params,
1058 x_returnStatus => x_status);
1059 if(x_status <> fnd_api.g_ret_sts_success) then
1060 raise fnd_api.g_exc_unexpected_error;
1061 end if;
1062 end if;
1063
1064 INV_LABEL.PRINT_LABEL_WRAP (
1065 X_RETURN_STATUS => x_status,
1066 X_MSG_COUNT => x_msg_count,
1067 X_MSG_DATA => x_msg,
1068 X_LABEL_STATUS => l_label_status,
1069 P_BUSINESS_FLOW_CODE => 41,
1070 P_TRANSACTION_ID => p_txn_id, -- from WMT
1071 P_TRANSACTION_IDENTIFIER => 9);
1072
1073 if (l_logLevel <= wip_constants.trace_logging) then
1074 wip_logger.exitPoint(p_procName => 'wip_utils.print_move_txn_label',
1075 p_procReturnStatus => x_status,
1076 p_msg => l_msg_data,
1077 x_returnStatus => l_returnStatus); --discard logging return status
1078 end if;
1079 END print_move_txn_label;
1080
1081
1082
1083 --VJ: Label Printing - End
1084 /**************************************************************************/
1085
1086
1087
1088
1089 END WIP_UTILITIES;