[Home] [Help]
PACKAGE BODY: APPS.CSD_MASS_RCV_PVT
Source
1 PACKAGE BODY csd_mass_rcv_pvt AS
2 /* $Header: csdvmssb.pls 120.8.12010000.5 2008/11/18 20:45:26 swai ship $ */
3 --
4 -- Purpose: To mass process repair orders
5 --
6 -- MODIFICATION HISTORY
7 -- Person Date Comments
8 -- --------- ------ ------------------------------------------
9 -- vparvath 05/27/03 Created new package body
10 ------------------------------------------------------------------------------------
11 g_pkg_name CONSTANT VARCHAR2 (30) := 'CSD_MASS_RCV_PVT';
12 g_file_name CONSTANT VARCHAR2 (30) := 'csdvmssb.pls';
13
14 TYPE number_arr IS TABLE OF NUMBER
15 INDEX BY BINARY_INTEGER;
16
17 --local proc declarations
18 PROCEDURE validate_product_txn_rec (
19 p_product_txn_rec IN csd_process_pvt.product_txn_rec
20 );
21
22 PROCEDURE log_error_stack;
23
24 FUNCTION is_item_pre_serialized (p_inv_item_id IN NUMBER)
25 RETURN BOOLEAN;
26
27
28 PROCEDURE validate_order (
29 p_est_detail_id IN NUMBER,
30 p_order_rec IN OUT NOCOPY csd_process_pvt.om_interface_rec,
31 x_booked_flag OUT NOCOPY VARCHAR2
32 );
33
34 procedure upd_instance(p_repair_type_ref IN VARCHAR2,
35 p_serial_number IN VARCHAR2,
36 p_instance_id IN NUMBER,
37 x_prod_txn_tbl IN OUT NOCOPY csd_process_pvt.product_txn_tbl
38 ) ;
39
40
41 -- This procedure will be called from the Serial number capture screen, when user clicks the OK button
42 -- It is a wrapper API, which subsequntly calls other API
43 PROCEDURE mass_create_ro (
44 p_api_version IN NUMBER,
45 p_commit IN VARCHAR2,
46 p_init_msg_list IN VARCHAR2,
47 p_validation_level IN NUMBER,
48 x_return_status OUT NOCOPY VARCHAR2,
49 x_msg_count OUT NOCOPY NUMBER,
50 x_msg_data OUT NOCOPY VARCHAR2,
51 p_repair_order_line_id IN NUMBER,
52 p_add_to_order_flag IN VARCHAR2
53 )
54 IS
55 l_api_version_number CONSTANT NUMBER := 1.0;
56 l_api_name CONSTANT VARCHAR2 (30) := 'MASS_CREATE_RO';
57 l_return_status VARCHAR2 (1);
58 l_msg_count NUMBER;
59 l_msg_data VARCHAR2 (2000);
60 l_repair_order_tbl csd_repairs_pub.repln_tbl_type;
61 l_prod_txn_tbl csd_process_pvt.product_txn_tbl;
62 l_incident_id NUMBER;
63 l_count_sn NUMBER;
64 l_ro_qty NUMBER;
65 l_count_sn_success NUMBER;
66 l_count_sn_blank NUMBER;
67 l_new_repln_id NUMBER;
68 l_index_out NUMBER;
69 l_item_id NUMBER;
70 l_repair_line_status VARCHAR2 (10);
71 l_ib_trackable BOOLEAN;
72
73 l_debug_level NUMBER ;
74 l_stmt_level NUMBER ;
75 l_event_level NUMBER ;
76 c_draft_Status VARCHAR2(1);
77
78 --Cursor to get the repair order record data and serial number data.
79 CURSOR cur_sn_rec (p_repln_id NUMBER)
80 IS
81 SELECT instance_id, serial_number,mass_ro_sn_id
82 FROM csd_mass_ro_sn
83 WHERE repair_line_id = p_repln_id;
84
85 CURSOR cur_repair_order( p_repln_id NUMBER)
86 IS
87 SELECT inventory_item_id, status, quantity
88 FROM csd_repairs
89 WHERE repair_line_id = p_repln_id;
90
91 BEGIN
92
93 SAVEPOINT sp_mass_create_ro;
94
95 l_return_status := fnd_api.g_ret_sts_success;
96 l_debug_level := fnd_log.g_current_runtime_level;
97 l_stmt_level := fnd_log.level_statement;
98 l_event_level := fnd_log.level_event;
99 c_draft_Status := 'D';
100
101 IF (fnd_log.level_procedure >= l_debug_level)
102 THEN
103 fnd_log.STRING (fnd_log.level_procedure,
104 'CSD.PLSQL.CSD_MASS_RCV_PVT.MASS_CREATE_RO.BEGIN',
105 '-------------Entered Mass_Create_RO----------------'
106 );
107 END IF;
108
109
110 IF fnd_api.to_boolean (p_init_msg_list)
111 THEN
112 -- initialize message list
113 fnd_msg_pub.initialize;
114 END IF;
115
116 -- Standard call to check for call compatibility.
117 IF NOT fnd_api.compatible_api_call (l_api_version_number,
118 p_api_version,
119 l_api_name,
120 g_pkg_name
121 )
122 THEN
123 RAISE fnd_api.g_exc_unexpected_error;
124 END IF;
125
126 -- initialize return status
127 x_return_status := fnd_api.g_ret_sts_success;
128
129 -- Purge the errors from generic errors table
130 --Delete records from the CSD_GENRIC_ERRMSGS table.
131 IF (l_event_level >= l_debug_level)
132 THEN
133 fnd_log.STRING (l_event_level,
134 'CSD.PLSQL.CSD_MASS_RCV_PVT.MASS_CREATE_RO',
135 '-----purging the previous processing errors----'
136 );
137 END IF;
138
139 csd_gen_errmsgs_pvt.purge_entity_msgs
140 (p_api_version => 1.0,
141 x_return_status => l_return_status,
142 x_msg_count => l_msg_count,
143 x_msg_data => l_msg_data,
144 p_module_code => 'SN',
145 p_source_entity_id1 => p_repair_order_line_id,
146 p_source_entity_type_code => NULL,
147 p_source_entity_id2 => NULL
148 );
149 IF (l_return_status <> fnd_api.g_ret_sts_success)
150 THEN
151 RAISE fnd_api.g_exc_unexpected_error;
152 END IF;
153
154
155 IF (l_event_level >= l_debug_level)
156 THEN
157 fnd_log.STRING
158 (l_event_level,
159 'CSD.PLSQL.CSD_MASS_RCV_PVT.MASS_CREATE_RO',
160 '---after the call to CSD_GEN_ERRMSGS_PVT.PURGE_ENTITY_MSGS-----'
161 );
162 END IF;
163
164
165 -- get the repair order details.
166 OPEN cur_repair_order (p_repair_order_line_id);
167
168 FETCH cur_repair_order
169 INTO l_item_id, l_repair_line_status, l_ro_qty;
170
171 CLOSE cur_repair_order;
172
173 --Validations: If te status of repair order is not 'Draft' then raise error.
174 IF NVL (l_repair_line_status, ' ') <> c_draft_status
175 THEN
176 fnd_message.set_name ('CSD', 'CSD_INVALID_REPAIR_ORDER');
177 fnd_msg_pub.ADD;
178 RAISE fnd_api.g_exc_error;
179 END IF;
180
181 IF (l_event_level >= l_debug_level)
182 THEN
183 fnd_log.STRING (l_event_level,
184 'CSD.PLSQL.CSD_MASS_RCV_PVT.MASS_CREATE_RO',
185 '-----------------Creating product transactions------------'
186 );
187 END IF;
188
189 --Call buil_prod_txn_tbl to create product transactions for each repair order.
190 -- THis call will use serial number as null and instance id as -1. These fields
191 -- are update later.
192 CSD_PROCESS_UTIL.build_prodtxn_tbl_int(p_Repair_line_id => p_repair_order_line_id,
193 p_quantity => 1,
194 p_serial_number => '',
195 p_instance_id => -1,
196 x_prod_txn_tbl => l_prod_txn_tbl,
197 x_return_status => x_return_status);
198
199 IF (x_return_status <> fnd_api.g_ret_sts_success)
200 THEN
201 RAISE fnd_api.g_exc_unexpected_error;
202 END IF;
203
204 IF (l_stmt_level >= l_debug_level)
205 THEN
206 fnd_log.STRING (l_stmt_level,
207 'CSD.PLSQL.CSD_MASS_RCV_PVT.MASS_CREATE_RO',
208 'Count of product transactions created=['
209 || l_prod_txn_tbl.COUNT
210 || ']'
211 );
212 END IF;
213
214 --Loop through CSD_MASS_RO_SN table records for the given repair order line id
215 -- and create repair order lines and product transactions for all the entered
216 -- serial numbers.
217
218 -- l_count indicates the count of saved serial numbers.
219 l_count_sn := 0;
220 l_count_sn_success := 0;
221
222 IF (l_event_level >= l_debug_level)
223 THEN
224 fnd_log.STRING (l_event_level,
225 'CSD.PLSQL.CSD_MASS_RCV_PVT.MASS_CREATE_RO',
226 '---------------Start processing for repair lineid=['
227 || p_repair_order_line_id
228 || ']------------'
229 );
230 END IF;
231
232 FOR l_repair_order_sn_rec IN cur_sn_rec (p_repair_order_line_id)
233 LOOP
234 --Increment the array index
235 l_count_sn := l_count_sn + 1;
236
237 IF (l_stmt_level >= l_debug_level)
238 THEN
239 fnd_log.STRING (l_stmt_level,
240 'CSD.PLSQL.CSD_MASS_RCV_PVT.MASS_CREATE_RO',
241 'Serial Number=['
242 || l_repair_order_sn_rec.serial_number
243 || ']'
244 );
245 fnd_log.STRING (l_stmt_level,
246 'CSD.PLSQL.CSD_MASS_RCV_PVT.MASS_CREATE_RO',
247 'Instance id=['
248 || TO_CHAR
249 (l_repair_order_sn_rec.instance_id
250 )
251 || ']'
252 );
253 END IF;
254 -- This save point is to make sure only the failed serial number is
255 -- rolled back and the processing continues with the next serial number.
256 SAVEPOINT sp_process_ro_start;
257 l_return_status := fnd_api.g_ret_sts_success;
258
259 --Call Process_RO with l_repair_order_tbl(i), Add_to_Order_flag, Mro_Serial_Number
260 process_ro (p_api_version => 1.0,
261 p_commit => fnd_api.g_false,
262 p_init_msg_list => fnd_api.g_false,
263 p_validation_level => fnd_api.g_valid_level_full,
264 x_return_status => l_return_status,
265 x_msg_count => l_msg_count,
266 x_msg_data => l_msg_data,
267 p_repair_line_id => p_repair_order_line_id,
268 p_prod_txn_tbl => l_prod_txn_tbl,
269 p_add_to_order_flag => p_add_to_order_flag,
270 p_mass_ro_sn_id => l_repair_order_sn_rec.mass_ro_sn_id,
271 p_serial_number => l_repair_order_sn_rec.serial_number,
272 p_instance_id => l_repair_order_sn_rec.instance_id,
273 x_new_repln_id => l_new_repln_id
274 );
275
276 --If the return_status <> 'S' then
277 --Insert a record in CSD_MASS_RO_SN_ERRORS with the error message.
278 IF (l_return_status = fnd_api.g_ret_sts_success)
279 THEN
280 IF (l_event_level >= l_debug_level)
281 THEN
282 fnd_log.STRING (l_event_level,
283 'CSD.PLSQL.CSD_MASS_RCV_PVT.MASS_CREATE_RO',
284 'Created the new repair order, repair line id=['
285 || l_new_repln_id
286 || ']'
287 );
288 END IF;
289 l_count_sn_success := l_count_sn_success + 1;
290 csd_repairs_pvt.copy_attachments
291 (p_api_version => 1.0,
292 p_commit => fnd_api.g_false,
293 p_init_msg_list => fnd_api.g_false,
294 p_validation_level => fnd_api.g_valid_level_full,
295 p_original_ro_id => p_repair_order_line_id,
296 p_new_ro_id => l_new_repln_id,
297 x_return_status => l_return_status,
298 x_msg_count => l_msg_count,
299 x_msg_data => l_msg_data
300 );
301 --Error handling TBD
302 ELSE
303 --Rollback to Save point Process_RO
304 ROLLBACK TO sp_process_ro_start;
305 -- Select error messages from stack and insert into CSD_MASS_RO_SN_ERRORS
306 csd_gen_errmsgs_pvt.save_fnd_msgs
307 (p_api_version => 1.0,
308 x_return_status => l_return_status,
309 x_msg_count => l_msg_count,
310 x_msg_data => l_msg_data,
311 p_module_code => 'SN',
312 p_source_entity_id1 => p_repair_order_line_id,
313 p_source_entity_type_code => 'SERIAL_NUMBER',
314 p_source_entity_id2 => l_repair_order_sn_rec.mass_ro_sn_id
315 );
316
317 IF (l_return_status <> fnd_api.g_ret_sts_success)
318 THEN
319 RAISE fnd_api.g_exc_unexpected_error;
320 END IF;
321
322 l_return_status := fnd_api.g_ret_sts_success;
323 END IF;
324
325 END LOOP;
326
327 IF (l_event_level >= l_debug_level)
328 THEN
329 fnd_log.STRING (l_event_level,
330 'CSD.PLSQL.CSD_MASS_RCV_PVT.MASS_CREATE_RO',
331 'No. of serial numbers processed successfully=['
332 || TO_CHAR (l_count_sn_success)
333 || ']'
334 );
335 END IF;
336
337
338
339 --Process rows with blank serial numbes for non Ib items.
340 l_ib_trackable := is_item_ib_trackable (l_item_id);
341
342 IF (NOT l_ib_trackable)
343 THEN
344 l_count_sn_blank := l_ro_qty - l_count_sn;
345 l_count_sn := l_ro_qty;
346
347 IF (l_event_level >= l_debug_level)
348 THEN
349 fnd_log.STRING (l_event_level,
350 'CSD.PLSQL.CSD_MASS_RCV_PVT.MASS_CREATE_RO',
351 'Num of blank sn being added= ['
352 || TO_CHAR (l_count_sn_blank)
353 || ']'
354 );
355 END IF;
356
357 WHILE (l_count_sn_blank > 0)
358 LOOP
359 l_count_sn_blank := l_count_sn_blank - 1;
360 -- This save point is to make sure only the failed serial number is
361 -- rolled back and the processing continues with the next serial number.
362 SAVEPOINT sp_process_ro_start;
363 l_return_status := fnd_api.g_ret_sts_success;
364
365 --Call Process_RO with l_repair_order_tbl(i), Add_to_Order_flag, Mro_Serial_Number
366 process_ro (p_api_version => 1.0,
367 p_commit => fnd_api.g_false,
368 p_init_msg_list => fnd_api.g_false,
369 p_validation_level => fnd_api.g_valid_level_full,
370 x_return_status => l_return_status,
371 x_msg_count => l_msg_count,
372 x_msg_data => l_msg_data,
373 p_repair_line_id => p_repair_order_line_id,
374 p_prod_txn_tbl => l_prod_txn_tbl,
375 p_add_to_order_flag => p_add_to_order_flag,
376 p_mass_ro_sn_id => -1,
377 p_serial_number => null,
378 p_instance_id => null,
379 x_new_repln_id => l_new_repln_id
380 );
381
382 --If the return_status <> 'S' then
383 --Insert a record in CSD_MASS_RO_SN_ERRORS with the error message.
384 IF (l_return_status = fnd_api.g_ret_sts_success)
385 THEN
386 l_count_sn_success := l_count_sn_success + 1;
387 csd_repairs_pvt.copy_attachments
388 (p_api_version => 1.0,
389 p_commit => fnd_api.g_false,
390 p_init_msg_list => fnd_api.g_false,
391 p_validation_level => fnd_api.g_valid_level_full,
392 p_original_ro_id => p_repair_order_line_id,
393 p_new_ro_id => l_new_repln_id,
394 x_return_status => l_return_status,
395 x_msg_count => l_msg_count,
396 x_msg_data => l_msg_data
397 );
398 --Error handling TBD
399 ELSE
400 --Rollback to Save point Process_RO
401 ROLLBACK TO sp_process_ro_start;
402 -- Select error messages from stack and insert into CSD_MASS_RO_SN_ERRORS
403 csd_gen_errmsgs_pvt.save_fnd_msgs
404 (p_api_version => 1.0,
405 x_return_status => l_return_status,
406 x_msg_count => l_msg_count,
407 x_msg_data => l_msg_data,
408 p_module_code => 'SN',
409 p_source_entity_id1 => p_repair_order_line_id,
410 p_source_entity_type_code => 'SERIAL_NUMBER',
411 p_source_entity_id2 => -1
412 );
413
414 IF (l_return_status <> fnd_api.g_ret_sts_success)
415 THEN
416 RAISE fnd_api.g_exc_unexpected_error;
417 END IF;
418
419 l_return_status := fnd_api.g_ret_sts_success;
420 END IF;
421
422 IF (l_event_level >= l_debug_level)
423 THEN
424 fnd_log.STRING (l_event_level,
425 'CSD.PLSQL.CSD_MASS_RCV_PVT.MASS_CREATE_RO',
426 'Created the new repair order, repair line id=['
427 || l_new_repln_id
428 || ']'
429 );
430 END IF;
431 END LOOP;
432 END IF;
433
434 IF (l_count_sn = 0)
435 THEN
436 fnd_message.set_name ('CSD', 'CSD_NO_SERIAL_NUMBERS');
437 fnd_msg_pub.ADD;
438 RAISE fnd_api.g_exc_error;
439 END IF;
440
441
442
443
444 --Delete record from CSD_REPAIRS for the input p_repair_order_line_id.
445 IF (l_event_level >= l_debug_level)
446 THEN
447 fnd_log.STRING (l_event_level,
448 'CSD.PLSQL.CSD_MASS_RCV_PVT.MASS_CREATE_RO',
449 'l_count_sn_success['
450 || l_count_sn_success
451 || ']l_count_sn['
452 || l_count_sn
453 || ']'
454 );
455 END IF;
456
457 IF (l_count_sn_success = l_count_sn)
458 THEN
459 IF (l_event_level >= l_debug_level)
460 THEN
461 fnd_log.STRING (l_event_level,
462 'CSD.PLSQL.CSD_MASS_RCV_PVT.MASS_CREATE_RO',
463 'deleting the draft repair order'
464 );
465 END IF;
466
467 csd_repairs_pvt.delete_repair_order
468 (p_api_version_number => 1.0,
469 p_init_msg_list => fnd_api.g_false,
470 p_commit => fnd_api.g_false,
471 p_validation_level => fnd_api.g_valid_level_full,
472 p_repair_line_id => p_repair_order_line_id,
473 x_return_status => l_return_status,
474 x_msg_count => l_msg_count,
475 x_msg_data => l_msg_data
476 );
477 ELSE
478 -- Update the repair order quantity. This condition occurrs when
479 -- only some of the serial numbers are processed.
480 IF (l_event_level >= l_debug_level)
481 THEN
482 fnd_log.STRING (l_event_level,
483 'CSD.PLSQL.CSD_MASS_RCV_PVT.MASS_CREATE_RO',
484 'Updating the CSD_REPAIRS table quantity'
485 );
486 END IF;
487
488 UPDATE csd_repairs
489 SET quantity = quantity - l_count_sn_success,
490 last_update_date = SYSDATE,
491 last_update_login = fnd_global.login_id,
492 last_updated_by = fnd_global.user_id
493 WHERE repair_line_id = p_repair_order_line_id;
494 END IF;
495
496 -- Api body ends here
497
498 -- Standard check of p_commit.
499 IF fnd_api.to_boolean (p_commit)
500 THEN
501 COMMIT WORK;
502 END IF;
503
504 -- Standard call to get message count and IF count is get message info.
505 fnd_msg_pub.count_and_get (p_count => x_msg_count,
506 p_data => x_msg_data);
507
508 IF (fnd_log.level_procedure >= l_debug_level)
509 THEN
510 fnd_log.STRING (fnd_log.level_procedure,
511 'CSD.PLSQL.CSD_MASS_RCV_PVT.MASS_CREATE_RO.END',
512 'Leaving Mass_Create_RO'
513 );
514 END IF;
515 EXCEPTION
516 WHEN fnd_api.g_exc_error
517 THEN
518 x_return_status := fnd_api.g_ret_sts_error;
519 log_error_stack ();
520 ROLLBACK TO sp_mass_create_ro;
521 fnd_msg_pub.count_and_get (p_count => x_msg_count,
522 p_data => x_msg_data
523 );
524
525 IF (fnd_log.level_error >= l_debug_level)
526 THEN
527 fnd_log.STRING (fnd_log.level_error,
528 'CSD.PLSQL.CSD_MASS_RCV_PVT.MASS_CREATE_RO',
529 'EXC_ERROR[' || x_msg_data || ']'
530 );
531 END IF;
532 WHEN fnd_api.g_exc_unexpected_error
533 THEN
534 x_return_status := fnd_api.g_ret_sts_unexp_error;
535 ROLLBACK TO sp_mass_create_ro;
536 fnd_msg_pub.count_and_get (p_count => x_msg_count,
537 p_data => x_msg_data
538 );
539
540 IF (fnd_log.level_exception >= l_debug_level)
541 THEN
542 fnd_log.STRING (fnd_log.level_exception,
543 'CSD.PLSQL.CSD_MASS_RCV_PVT.MASS_CREATE_RO',
544 'EXC_UNEXP_ERROR[' || x_msg_data || ']'
545 );
546 END IF;
547 WHEN OTHERS
548 THEN
549 x_return_status := fnd_api.g_ret_sts_unexp_error;
550 ROLLBACK TO sp_mass_create_ro;
551
552 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
553 THEN
554 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
555 END IF;
556
557 fnd_msg_pub.count_and_get (p_count => x_msg_count,
558 p_data => x_msg_data
559 );
560
561 IF (fnd_log.level_unexpected >= l_debug_level)
562 THEN
563 fnd_log.STRING (fnd_log.level_unexpected,
564 'CSD.PLSQL.CSD_MASS_RCV_PVT.MASS_CREATE_RO',
565 'SQL MEssage[' || SQLERRM || ']'
566 );
567 END IF;
568 END mass_create_ro;
569
570 PROCEDURE process_ro (
571 p_api_version IN NUMBER,
572 p_commit IN VARCHAR2,
573 p_init_msg_list IN VARCHAR2,
574 p_validation_level IN NUMBER,
575 x_return_status OUT NOCOPY VARCHAR2,
576 x_msg_count OUT NOCOPY NUMBER,
577 x_msg_data OUT NOCOPY VARCHAR2,
578 p_repair_line_id IN NUMBER,
579 p_prod_txn_tbl IN OUT NOCOPY csd_process_pvt.product_txn_tbl,
580 p_add_to_order_flag IN VARCHAR2,
581 p_mass_ro_sn_id IN NUMBER,
582 p_serial_number IN VARCHAR2,
583 p_instance_id IN NUMBER,
584 x_new_repln_id OUT NOCOPY NUMBER
585 )
586 IS
587 l_api_version_number CONSTANT NUMBER := 1.0;
588 l_api_name CONSTANT VARCHAR2 (30) := 'PROCESS_RO';
589 l_return_status VARCHAR2 (1) ;
590 l_msg_count NUMBER;
591 l_msg_data VARCHAR2 (2000);
592 l_repair_number VARCHAR2 (30);
593 l_repair_type_ref VARCHAR2 (30);
594 l_product_txn_rec csd_process_pvt.product_txn_rec;
595 c_refurbishment_type_ref CONSTANT VARCHAR2 (30) := 'RF';
596 l_repair_order_rec csd_repairs_pub.repln_rec_type;
597
598 -- swai: 12.1.1 bug 7176940 service bulletin check
599 l_ro_sc_ids_tbl CSD_RO_BULLETINS_PVT.CSD_RO_SC_IDS_TBL_TYPE;
600
601 --Define cursors
602 CURSOR cur_repair_type_ref (p_repair_type_id NUMBER)
603 IS
604 SELECT repair_type_ref
605 FROM csd_repair_types_vl
606 WHERE repair_type_id = p_repair_type_id;
607
608 --Cursor to get the repair order record data .
609 CURSOR cur_repair_order (p_repln_id NUMBER)
610 IS
611 SELECT incident_id, inventory_item_id,
612 customer_product_id, unit_of_measure, repair_type_id,
613 owning_organization_id, -- swai: bug 7565999
614 resource_id, project_id, task_id, contract_line_id,
615 auto_process_rma, repair_mode, item_revision,
616 NULL instance_id, status_reason_code,
617 approval_required_flag, approval_status, promise_date,
618 1 quantity, currency_code, ro_txn_status,
619 original_source_reference, NULL serial_number, 'O' status,
620 -- Added below cols when dff support is added
621 0 wip_quantity, 0 quantity_rcvd, 0 quantity_shipped,
622 attribute_category, attribute1, attribute2,
623 attribute3, attribute4, attribute5,
624 attribute6, attribute7, attribute8,
625 attribute9, attribute10, attribute11,
626 attribute12, attribute13, attribute14, attribute15,
627 original_source_header_id, original_source_line_id,
628 price_list_header_id, inventory_org_id, --bug#6415265
629 attribute16,attribute17,attribute18, -- bug#7497907, 12.1 FP, subhat
630 attribute19,attribute20,attribute21,attribute22,attribute23,
631 attribute24,attribute25,attribute26,attribute27,attribute28,attribute29,
632 attribute30
633 FROM csd_repairs
634 WHERE repair_line_id = p_repln_id;
635
636 BEGIN
637 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
638 THEN
639 fnd_log.STRING (fnd_log.level_procedure,
640 'CSD.PLSQL.CSD_MASS_RCV_PVT.PROCESS_RO.BEGIN',
641 'Entering Process_RO'
642 );
643 END IF;
644
645 SAVEPOINT sp_process_ro;
646
647 l_return_status := fnd_api.g_ret_sts_success;
648
649 IF fnd_api.to_boolean (p_init_msg_list)
650 THEN
651 -- initialize message list
652 fnd_msg_pub.initialize;
653 END IF;
654
655 -- Standard call to check for call compatibility.
656 IF NOT fnd_api.compatible_api_call (l_api_version_number,
657 p_api_version,
658 l_api_name,
659 g_pkg_name
660 )
661 THEN
662 RAISE fnd_api.g_exc_unexpected_error;
663 END IF;
664
665 -- initialize return status
666 x_return_status := fnd_api.g_ret_sts_success;
667
668 -- Get the repair order line details and populate the repair orde record
669 --
670 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
671 THEN
672 fnd_log.STRING (fnd_log.level_event,
673 'CSD.PLSQL.CSD_MASS_RCV_PVT.Process_RO',
674 '--------------Fetching repair order details for[='
675 || p_repair_line_id
676 || ']---------------'
677 );
678 END IF;
679
680 OPEN cur_repair_order(p_repair_line_id);
681 FETCH cur_repair_order INTO
682 l_repair_order_rec.incident_id,
683 l_repair_order_rec.inventory_item_id,
684 l_repair_order_rec.customer_product_id,
685 l_repair_order_rec.unit_of_measure,
686 l_repair_order_rec.repair_type_id,
687 l_repair_order_rec.resource_group, -- swai: bug 7565999
688 l_repair_order_rec.resource_id,
689 l_repair_order_rec.project_id,
690 l_repair_order_rec.task_id,
691 l_repair_order_rec.contract_line_id,
692 l_repair_order_rec.auto_process_rma,
693 l_repair_order_rec.repair_mode,
694 l_repair_order_rec.item_revision,
695 l_repair_order_rec.instance_id,
696 l_repair_order_rec.status_reason_code,
697 l_repair_order_rec.approval_required_flag,
698 l_repair_order_rec.approval_status,
699 l_repair_order_rec.promise_date,
700 l_repair_order_rec.quantity,
701 l_repair_order_rec.currency_code,
702 l_repair_order_rec.ro_txn_status,
703 l_repair_order_rec.original_source_reference,
704 l_repair_order_rec.serial_number,
705 l_repair_order_rec.status,
706 --- Added below while adding DFF support
707 l_repair_order_rec.quantity_in_wip,
708 l_repair_order_rec.quantity_rcvd,
709 l_repair_order_rec.quantity_shipped,
710 l_repair_order_rec.attribute_category,
711 l_repair_order_rec.attribute1,
712 l_repair_order_rec.attribute2,
713 l_repair_order_rec.attribute3,
714 l_repair_order_rec.attribute4,
715 l_repair_order_rec.attribute5,
716 l_repair_order_rec.attribute6,
717 l_repair_order_rec.attribute7,
718 l_repair_order_rec.attribute8,
719 l_repair_order_rec.attribute9,
720 l_repair_order_rec.attribute10,
721 l_repair_order_rec.attribute11,
722 l_repair_order_rec.attribute12,
723 l_repair_order_rec.attribute13,
724 l_repair_order_rec.attribute14,
725 l_repair_order_rec.attribute15,
726 l_repair_order_rec.original_source_header_id,
727 l_repair_order_rec.original_source_line_id,
728 l_repair_order_rec.price_list_header_id,
729 l_repair_order_rec.inventory_org_id, ---bug#6415265
730 l_repair_order_rec.attribute16, -- bug#7497907, DFF changes, subhat
731 l_repair_order_rec.attribute17,
732 l_repair_order_rec.attribute18,
733 l_repair_order_rec.attribute19,
734 l_repair_order_rec.attribute20,
735 l_repair_order_rec.attribute21,
736 l_repair_order_rec.attribute22,
737 l_repair_order_rec.attribute23,
738 l_repair_order_rec.attribute24,
739 l_repair_order_rec.attribute25,
740 l_repair_order_rec.attribute26,
741 l_repair_order_rec.attribute27,
742 l_repair_order_rec.attribute28,
743 l_repair_order_rec.attribute29,
744 l_repair_order_rec.attribute30;
745
746 IF (cur_repair_order%NOTFOUND)
747 THEN
748 fnd_message.set_name ('CSD', 'CSD_API_INV_REP_LINE_ID');
749 fnd_message.set_token ('REPAIR_LINE_ID',
750 p_repair_line_id
751 );
752 fnd_msg_pub.ADD;
753 CLOSE cur_repair_order;
754 RAISE fnd_api.g_exc_error;
755 END IF;
756
757 CLOSE cur_repair_order;
758
759 --Get the repair type ref and populate product txn table
760 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
761 THEN
762 fnd_log.STRING (fnd_log.level_event,
763 'CSD.PLSQL.CSD_MASS_RCV_PVT.Process_RO',
764 '------------------Fetching repair type ref for[='
765 || l_repair_order_rec.repair_type_id
766 || ']----------------'
767 );
768 END IF;
769
770
771 OPEN cur_repair_type_ref (l_repair_order_rec.repair_type_id);
772
773 FETCH cur_repair_type_ref
774 INTO l_repair_type_ref;
775
776 IF (cur_repair_type_ref%NOTFOUND)
777 THEN
778 FND_MESSAGE.SET_NAME('CSD','CSD_API_REPAIR_TYPE_ID');
779 FND_MESSAGE.SET_TOKEN('REPAIR_TYPE_ID',l_repair_order_rec.repair_type_id);
780 FND_MSG_PUB.Add;
781 CLOSE cur_repair_type_ref;
782 RAISE fnd_api.g_exc_error;
783 END IF;
784
785 CLOSE cur_repair_type_ref;
786
787 ---Copy the serial number and the instance id into repair order rec.
788
789 l_repair_order_rec.instance_id := p_instance_id;
790 l_repair_order_rec.customer_product_id := p_instance_id;
791 l_repair_order_rec.serial_number := p_serial_number;
792 l_repair_order_rec.repair_number := null;
793 l_repair_order_rec.repair_group_id := null;
794
795
796 /*
797 IF(p_repair_order_rec.SERIAL_NUMBER = 'SN_ERR') THEN
798 dbms_output.put_line('Error condition');
799 FND_MESSAGE.SET_NAME('CSD','ERROR_MSG');
800 FND_MSG_PUB.ADD;
801 RAISE FND_API.G_EXC_ERROR;
802 END IF;
803 */
804 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
805 THEN
806 fnd_log.STRING (fnd_log.level_event,
807 'CSD.PLSQL.CSD_MASS_RCV_PVT.Process_RO',
808 '------------------Calling csd_repairs_pvt.create_repair_order'
809 );
810 END IF;
811
812 csd_repairs_pvt.create_repair_order
813 (p_api_version_number => 1.0,
814 p_commit => fnd_api.g_false,
815 p_init_msg_list => fnd_api.g_false,
816 p_validation_level => p_validation_level,
817 p_repair_line_id => NULL,
818 p_repln_rec => l_repair_order_rec,
819 x_repair_line_id => x_new_repln_id,
820 x_repair_number => l_repair_number,
821 x_return_status => x_return_status,
822 x_msg_count => x_msg_count,
823 x_msg_data => x_msg_data
824 );
825
826 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
827 THEN
828 RAISE fnd_api.g_exc_error;
829 END IF;
830
831 --Update Repair order line id in the record p_repair_Order_rec and prod txn table
832 l_repair_order_rec.repair_number := l_repair_number;
833
834 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
835 THEN
836 fnd_log.STRING (fnd_log.level_statement,
837 'CSD.PLSQL.CSD_MASS_RCV_PVT.Process_RO',
838 '------------------New repair order number=[' || l_repair_number || ']'
839 );
840 END IF;
841
842
843 --If Reapir_type_Ref is not 'RF'(Refurbishment).
844 --Call create_product_txn API to create the charge line and product txns.
845 --If the return_status <> 'S' then rollback to savepoint SP_Process_RO, else
846 --proceed to next step.
847 IF (l_repair_type_ref <> c_refurbishment_type_ref)
848 THEN
849
850 -- Fix for bug#4884582
851 -- Commented the call to create_product_txn api.
852 -- csd_process_pvt.create_default_txn api is used to create
853 -- product transactions
854 --
855 /****
856 IF (p_prod_txn_tbl.COUNT > 0)
857 THEN
858 -- THis api will update the product txn records with the serial number
859 -- and the isntance id.
860 upd_instance(p_repair_type_ref => l_Repair_type_ref,
861 p_serial_number => p_serial_number,
862 p_instance_id => p_instance_id,
863 x_prod_txn_tbl => p_prod_txn_tbl);
864
865 FOR i IN p_prod_txn_tbl.FIRST .. p_prod_txn_tbl.LAST
866 LOOP
867 p_prod_txn_tbl (i).repair_line_id := x_new_repln_id;
868 p_prod_txn_tbl (i).product_transaction_id := NULL;
869
870 --l_product_Txn_Rec := p_prod_txn_tbl(i);
871 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level
872 )
873 THEN
874 fnd_log.STRING (fnd_log.level_statement,
875 'CSD.PLSQL.CSD_MASS_RCV_PVT.Process_RO',
876 'Calling Create_Product_Txn for['
877 || p_prod_txn_tbl (i).source_serial_number
878 || ']'
879 );
880 END IF;
881
882 create_product_txn
883 (p_api_version => 1.0,
884 p_commit => fnd_api.g_false,
885 p_init_msg_list => fnd_api.g_false,
886 p_validation_level => fnd_api.g_valid_level_full,
887 x_return_status => l_return_status,
888 x_msg_count => x_msg_count,
889 x_msg_data => x_msg_data,
890 p_product_txn_rec => p_prod_txn_tbl (i),
891 p_add_to_order_flag => p_add_to_order_flag
892 );
893
894 IF (l_return_status <> fnd_api.g_ret_sts_success)
895 THEN
896 --Rollback to Save point Process_RO
897 RAISE fnd_api.g_exc_error;
898 END IF;
899
900 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level
901 )
902 THEN
903 fnd_log.STRING (fnd_log.level_statement,
904 'CSD.PLSQL.CSD_MASS_RCV_PVT.Process_RO',
905 'After Create_product_txn['
906 || l_repair_order_rec.serial_number
907 || ']'
908 );
909 END IF;
910 END LOOP;
911 END IF;
912 *****/
913
914 -- Fix for bug#4884582
915 -- Call Default Product Txn creation
916 --
917 csd_process_pvt.create_default_prod_txn
918 ( p_api_version => 1.0,
919 p_commit => Fnd_Api.g_false,
920 p_init_msg_list => Fnd_Api.g_false,
921 p_validation_level => Fnd_Api.g_valid_level_full,
922 p_repair_line_id => x_new_repln_id,
923 x_return_status => l_return_status,
924 x_msg_count => x_msg_count,
925 x_msg_data => x_msg_data
926 );
927
928 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
929 fnd_log.STRING (fnd_log.level_statement,
930 'CSD.PLSQL.CSD_MASS_RCV_PVT.Process_RO',
931 'After Create_default_product_txn['
932 || l_repair_order_rec.serial_number|| ']');
933 END IF;
934
935 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
936 --Rollback to Save point Process_RO
937 RAISE fnd_api.g_exc_error;
938 END IF;
939
940 -- swai: 12.1.1 bug 7176940 - check service bulletins after RO creation
941 IF (nvl(fnd_profile.value('CSD_AUTO_CHECK_BULLETINS'),'N') = 'Y') THEN
942 CSD_RO_BULLETINS_PVT.LINK_BULLETINS_TO_RO(
943 p_api_version_number => 1.0,
944 p_init_msg_list => Fnd_Api.G_FALSE,
945 p_commit => Fnd_Api.G_FALSE,
946 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
947 p_repair_line_id => x_new_repln_id,
948 px_ro_sc_ids_tbl => l_ro_sc_ids_tbl,
949 x_return_status => l_return_status,
950 x_msg_count => l_msg_count,
951 x_msg_data => l_msg_data
952 );
953 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
954 fnd_log.STRING (fnd_log.level_statement,
955 'CSD.PLSQL.CSD_MASS_RCV_PVT.Process_RO',
956 'After CSD_RO_BULLETINS_PVT.LINK_BULLETINS_TO_RO['
957 || x_new_repln_id || ']');
958 END IF;
959 -- ignore return status for now.
960 END IF;
961
962 END IF;
963
964
965 --Delete the processed serial number from CSD_MASS_RO_SN if exists.
966 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
967 THEN
968 fnd_log.STRING (fnd_log.level_statement,
969 'CSD.PLSQL.CSD_MASS_RCV_PVT.Process_RO',
970 'Deleting CSD_MASS_RO_SN record sn=['
971 || NVL (l_repair_order_rec.serial_number, '')
972 || ']'
973 );
974 END IF;
975
976 IF (NVL (l_repair_order_rec.serial_number, '-') <> '-')
977 THEN
978 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
979 THEN
980 fnd_log.STRING (fnd_log.level_statement,
981 'CSD.PLSQL.CSD_MASS_RCV_PVT.Process_RO',
982 'Deleting CSD_MASS_RO_SN record '
983 );
984 END IF;
985
986 csd_mass_ro_sn_pkg.delete_row (p_mass_ro_sn_id => p_mass_ro_sn_id);
987 END IF;
988
989 -- Api body ends here
990
991 -- Standard check of p_commit.
992 IF fnd_api.to_boolean (p_commit)
993 THEN
994 COMMIT WORK;
995 END IF;
996
997 -- Standard call to get message count and IF count is get message info.
998 fnd_msg_pub.count_and_get (p_count => x_msg_count,
999 p_data => x_msg_data);
1000
1001 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1002 THEN
1003 fnd_log.STRING (fnd_log.level_procedure,
1004 'CSD.PLSQL.CSD_MASS_RCV_PVT.PROCESS_RO.END',
1005 'Leaving Process_RO'
1006 );
1007 END IF;
1008 EXCEPTION
1009 WHEN fnd_api.g_exc_error
1010 THEN
1011 x_return_status := fnd_api.g_ret_sts_error;
1012 ROLLBACK TO sp_process_ro;
1013 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1014 p_data => x_msg_data
1015 );
1016
1017 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
1018 THEN
1019 fnd_log.STRING (fnd_log.level_error,
1020 'CSD.PLSQL.CSD_MASS_RCV_PVT.PROCESS_RO',
1021 'EXC_ERROR[' || x_msg_data || ']'
1022 );
1023 END IF;
1024 WHEN fnd_api.g_exc_unexpected_error
1025 THEN
1026 x_return_status := fnd_api.g_ret_sts_unexp_error;
1027 ROLLBACK TO sp_process_ro;
1028 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1029 p_data => x_msg_data
1030 );
1031
1032 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1033 THEN
1034 fnd_log.STRING (fnd_log.level_exception,
1035 'CSD.PLSQL.CSD_MASS_RCV_PVT.PROCESS_RO',
1036 'EXC_UNEXP_ERROR[' || x_msg_data || ']'
1037 );
1038 END IF;
1039 WHEN OTHERS
1040 THEN
1041 x_return_status := fnd_api.g_ret_sts_unexp_error;
1042 ROLLBACK TO sp_process_ro;
1043
1044 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1045 THEN
1046 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1047 END IF;
1048
1049 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1050 p_data => x_msg_data
1051 );
1052
1053 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1054 THEN
1055 fnd_log.STRING (fnd_log.level_exception,
1056 'CSD.PLSQL.CSD_MASS_RCV_PVT.PROCESS_RO',
1057 'SQL MEssage[' || SQLERRM || ']'
1058 );
1059 END IF;
1060 END process_ro;
1061
1062 --This API is called from the Process_RO API. This
1063 --will create the product transaction, charge line, submit charge line and book the chargeline.
1064 --
1065 --
1066 PROCEDURE create_product_txn (
1067 p_api_version IN NUMBER,
1068 p_commit IN VARCHAR2,
1069 p_init_msg_list IN VARCHAR2,
1070 p_validation_level IN NUMBER,
1071 x_return_status OUT NOCOPY VARCHAR2,
1072 x_msg_count OUT NOCOPY NUMBER,
1073 x_msg_data OUT NOCOPY VARCHAR2,
1074 p_product_txn_rec IN OUT NOCOPY csd_process_pvt.product_txn_rec,
1075 p_add_to_order_flag IN VARCHAR2
1076 )
1077 IS
1078 ----Define Cursors
1079 CURSOR cur_cust_details (p_incident_id IN NUMBER)
1080 IS
1081 SELECT customer_id, account_id
1082 FROM cs_incidents_all_b
1083 WHERE incident_id = p_incident_id;
1084
1085 /*** contract rearch changes for R12
1086
1087 CURSOR cur_coverage_details (p_bus_process_id NUMBER)
1088 IS
1089 SELECT cov.actual_coverage_id,
1090 -- cov.coverage_name, -- commented for bugfix 3617932
1091 ent.txn_group_id
1092 FROM oks_ent_coverages_v cov, oks_ent_txn_groups_v ent
1093 WHERE cov.contract_line_id = p_product_txn_rec.contract_id
1094 AND cov.actual_coverage_id = ent.coverage_id
1095 AND ent.business_process_id = p_bus_process_id;
1096 ****************/
1097
1098 CURSOR cur_ro_details (p_repair_line_id NUMBER)
1099 IS
1100 SELECT incident_id, original_source_reference,
1101 original_source_header_id, original_source_line_id
1102 FROM csd_repairs
1103 WHERE repair_line_id = p_repair_line_id;
1104
1105 CURSOR cur_pricelist_details (p_price_list_id NUMBER)
1106 IS
1107 SELECT currency_code
1108 FROM oe_price_lists
1109 WHERE price_list_id = p_price_list_id;
1110
1111 CURSOR cur_order_category (p_header_id NUMBER)
1112 IS
1113 SELECT oot.order_category_code
1114 FROM oe_order_headers_all ooh, oe_order_types_v oot
1115 WHERE ooh.order_type_id = oot.order_type_id
1116 AND ooh.header_id = p_header_id;
1117
1118 CURSOR cur_po_number_rma (
1119 p_orig_src_header_id NUMBER,
1120 p_po_number VARCHAR2
1121 )
1122 IS
1123 SELECT cust_po_number
1124 FROM oe_order_headers_all
1125 WHERE header_id = p_orig_src_header_id
1126 AND cust_po_number = p_po_number;
1127
1128 CURSOR cur_po_number (
1129 p_repair_line_id NUMBER,
1130 p_order_header_id NUMBER,
1131 p_po_number VARCHAR2
1132 )
1133 IS
1134 SELECT ced.purchase_order_num
1135 FROM csd_product_transactions cpt, cs_estimate_details ced
1136 WHERE cpt.estimate_detail_id = ced.estimate_detail_id
1137 AND cpt.repair_line_id = p_repair_line_id
1138 AND ced.order_header_id = p_order_header_id
1139 AND ced.purchase_order_num = p_po_number;
1140
1141 CURSOR cur_order_header (p_incident_id NUMBER)
1142 IS
1143 SELECT MAX (ced.order_header_id)
1144 FROM csd_repairs cr,
1145 csd_product_transactions cpt,
1146 cs_estimate_details ced
1147 WHERE cr.incident_id = p_incident_id
1148 AND cpt.repair_line_id = cr.repair_line_id
1149 AND ced.estimate_detail_id = cpt.estimate_detail_id
1150 AND ced.order_header_id IS NOT NULL
1151 AND ced.interface_to_oe_flag = 'Y';
1152
1153 CURSOR cur_sub_inv (p_sub_inventory VARCHAR2, p_ship_from_org_id NUMBER)
1154 IS
1155 SELECT 'x'
1156 FROM mtl_secondary_inventories
1157 WHERE secondary_inventory_name = p_sub_inventory
1158 AND organization_id = p_ship_from_org_id;
1159
1160 CURSOR cur_pick_rule (p_picking_rule_id NUMBER)
1161 IS
1162 SELECT picking_rule_id
1163 FROM wsh_picking_rules
1164 WHERE picking_rule_id = p_picking_rule_id;
1165
1166 CURSOR cur_release_status (p_order_line_id NUMBER)
1167 IS
1168 SELECT released_status
1169 FROM wsh_delivery_details
1170 WHERE source_line_id = p_order_line_id;
1171
1172 CURSOR cur_txn_type_id (p_txn_billing_type_id NUMBER)
1173 IS
1174 SELECT transaction_type_id
1175 FROM cs_txn_billing_types
1176 WHERE txn_billing_type_id = p_txn_billing_type_id;
1177
1178 ---------------------------------------------------------------------------0
1179 l_api_version_number CONSTANT NUMBER := 1.0;
1180 l_api_name CONSTANT VARCHAR2 (30) := 'PROCESS_RO';
1181 l_return_status VARCHAR2 (1) ;
1182 l_order_rec csd_process_pvt.om_interface_rec;
1183 l_sn_processed_count NUMBER;
1184 l_index NUMBER;
1185 l_incident_id NUMBER;
1186 l_orig_src_reference VARCHAR2 (30);
1187 l_orig_src_header_id NUMBER;
1188 l_orig_src_line_id NUMBER;
1189 l_bus_process_id NUMBER;
1190 l_coverage_id NUMBER;
1191 -- l_coverage_name VARCHAR2 (150); -- commented for bugfix 3617932
1192 l_txn_group_id NUMBER ;
1193 l_party_id NUMBER ;
1194 l_account_id NUMBER ;
1195 l_order_header_id NUMBER ;
1196 l_curr_code VARCHAR2 (10) ;
1197 l_line_category_code VARCHAR2 (30) ;
1198 l_line_type_id NUMBER ;
1199 l_serial_flag BOOLEAN;
1200 l_charges_rec cs_charge_details_pub.charges_rec_type;
1201 l_ro_txn_status VARCHAR2 (50);
1202 l_prod_txn_status VARCHAR2 (50);
1203 l_add_to_same_order VARCHAR2 (1);
1204 l_order_category_code VARCHAR2 (30);
1205 l_orig_po_num VARCHAR2 (50);
1206 l_estimate_detail_id NUMBER;
1207 l_booked_flag VARCHAR2 (1);
1208 l_transaction_type_id NUMBER;
1209 BEGIN
1210 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1211 THEN
1212 fnd_log.STRING
1213 (fnd_log.level_procedure,
1214 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN.BEGIN',
1215 'Entered CREATE_PRODUCT_TXN'
1216 );
1217 END IF;
1218
1219 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1220 THEN
1221 fnd_log.STRING (fnd_log.level_statement,
1222 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1223 'Processing Serial Number:=['
1224 || p_product_txn_rec.source_serial_number
1225 || ']'
1226 );
1227 END IF;
1228
1229 SAVEPOINT sp_create_product_txn;
1230
1231 l_return_status := fnd_api.g_ret_sts_success;
1232
1233 IF fnd_api.to_boolean (p_init_msg_list)
1234 THEN
1235 -- initialize message list
1236 fnd_msg_pub.initialize;
1237 END IF;
1238
1239 -- Standard call to check for call compatibility.
1240 IF NOT fnd_api.compatible_api_call (l_api_version_number,
1241 p_api_version,
1242 l_api_name,
1243 g_pkg_name
1244 )
1245 THEN
1246 RAISE fnd_api.g_exc_unexpected_error;
1247 END IF;
1248
1249 --Validate input
1250 --Check mandatory parameters Repair_line_id, Action_Code, Action_type, Txn_Billing_Type_Id, Inventory_Item_Id, UOM, Quantity
1251 --and Price_list_Id.
1252 --Validate the parameters, Repair_line_id, action_type, action_code and Prod_Txn_Status
1253
1254 ---------------------------------------------------------------------------------1
1255 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1256 THEN
1257 fnd_log.STRING (fnd_log.level_statement,
1258 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1259 'Before calling validate_product_txn_Rec'
1260 );
1261 END IF;
1262
1263 validate_product_txn_rec (p_product_txn_rec);
1264
1265 ---------------------------------------------------------------------------------2
1266
1267 --Check if the input business_process_id is valid and if it is invalid get the business_process_id from the repair_type.
1268 --Set error if the business process is invalid.
1269
1270 -- Get service request from csd_repairs table
1271 -- using repair order
1272 OPEN cur_ro_details (p_product_txn_rec.repair_line_id);
1273
1274 FETCH cur_ro_details
1275 INTO l_incident_id, l_orig_src_reference, l_orig_src_header_id,
1276 l_orig_src_line_id;
1277
1278 IF (cur_ro_details%NOTFOUND)
1279 THEN
1280 fnd_message.set_name ('CSD', 'CSD_API_INV_REP_LINE_ID');
1281 fnd_message.set_token ('REPAIR_LINE_ID',
1282 p_product_txn_rec.repair_line_id
1283 );
1284 fnd_msg_pub.ADD;
1285
1286 CLOSE cur_ro_details;
1287
1288 RAISE fnd_api.g_exc_error;
1289 END IF;
1290
1291 CLOSE cur_ro_details;
1292
1293 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1294 THEN
1295 fnd_log.STRING (fnd_log.level_statement,
1296 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1297 'l_incident_id =' || l_incident_id
1298 );
1299 END IF;
1300
1301 ---------------------------------------------------------------------------------3
1302 -- Get the business process id
1303 l_bus_process_id :=
1304 csd_process_util.get_bus_process (p_product_txn_rec.repair_line_id);
1305
1306 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1307 THEN
1308 fnd_log.STRING (fnd_log.level_statement,
1309 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1310 'l_bus_process_id =' || l_bus_process_id
1311 );
1312 END IF;
1313
1314 IF l_bus_process_id < 0
1315 THEN
1316 IF NVL (p_product_txn_rec.business_process_id, fnd_api.g_miss_num) <>
1317 fnd_api.g_miss_num
1318 THEN
1319 l_bus_process_id := p_product_txn_rec.business_process_id;
1320 ELSE
1321 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1322 THEN
1323 fnd_log.STRING
1324 (fnd_log.level_statement,
1325 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1326 'Business process Id does not exist '
1327 );
1328 END IF;
1329
1330 RAISE fnd_api.g_exc_error;
1331 END IF;
1332 END IF;
1333
1334 ---------------------------------------------------------------------------------
1335 --Get the transaction type id
1336 IF (p_product_txn_rec.transaction_type_id IS NULL)
1337 OR (p_product_txn_rec.transaction_type_id = fnd_api.g_miss_num)
1338 THEN
1339 OPEN cur_txn_type_id (p_product_txn_rec.txn_billing_type_id);
1340
1341 FETCH cur_txn_type_id
1342 INTO l_transaction_type_id;
1343
1344 IF (cur_txn_type_id%NOTFOUND)
1345 THEN
1346 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1347 THEN
1348 fnd_log.STRING
1349 (fnd_log.level_statement,
1350 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1351 'No Row found for the txn_billing_type_id='
1352 || TO_CHAR (p_product_txn_rec.txn_billing_type_id)
1353 );
1354 END IF;
1355
1356 CLOSE cur_txn_type_id;
1357
1358 RAISE fnd_api.g_exc_error;
1359 END IF;
1360
1361 CLOSE cur_txn_type_id;
1362
1363 p_product_txn_rec.transaction_type_id := l_transaction_type_id;
1364
1365 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1366 THEN
1367 fnd_log.STRING (fnd_log.level_statement,
1368 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1369 'p_product_txn_rec.transaction_type_id :'
1370 || TO_CHAR (p_product_txn_rec.transaction_type_id)
1371 );
1372 END IF;
1373 END IF;
1374
1375 ---------------------------------------------------------------------------------4
1376 --If the contract id is not null, derive the coverage details from oks_ent_coverages_v, oks_ent_txn_groups_v for the
1377 --given contract id and business process id.
1378 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1379 THEN
1380 fnd_log.STRING (fnd_log.level_statement,
1381 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1382 'contract_line_id =' || p_product_txn_rec.contract_id
1383 );
1384 END IF;
1385
1386 -- Get the coverage details from the contract
1387 /*** Contract re arch changes for R12
1388 IF NVL (p_product_txn_rec.contract_id, fnd_api.g_miss_num) <>
1389 fnd_api.g_miss_num
1390 THEN
1391 OPEN cur_coverage_details (l_bus_process_id);
1392
1393 FETCH cur_coverage_details
1394 INTO l_coverage_id,
1395 -- l_coverage_name, -- commented for bugfix 3617932
1396 l_txn_group_id;
1397
1398 IF (cur_coverage_details%NOTFOUND)
1399 THEN
1400 fnd_message.set_name ('CSD', 'CSD_API_CONTRACT_MISSING');
1401 fnd_message.set_token ('CONTRACT_LINE_ID',
1402 p_product_txn_rec.contract_id
1403 );
1404 fnd_msg_pub.ADD;
1405
1406 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1407 THEN
1408 fnd_log.STRING
1409 (fnd_log.level_statement,
1410 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1411 'Contract Line Id missing'
1412 );
1413 END IF;
1414
1415 CLOSE cur_coverage_details;
1416
1417 RAISE fnd_api.g_exc_error;
1418 END IF;
1419
1420 CLOSE cur_coverage_details;
1421
1422 p_product_txn_rec.coverage_id := l_coverage_id;
1423 p_product_txn_rec.coverage_txn_group_id := l_txn_group_id;
1424
1425 END IF;
1426 ****************/
1427
1428 ---------------------------------------------------------------------------------5
1429 --Get Party_ID and Account_ID from cs_incidents_all_b table for the given incident_Id. If the party_id is null raise error.
1430 IF l_incident_id IS NOT NULL
1431 THEN
1432 OPEN cur_cust_details (l_incident_id);
1433
1434 FETCH cur_cust_details
1435 INTO l_party_id, l_account_id;
1436
1437 IF (cur_cust_details%NOTFOUND OR l_party_id IS NULL)
1438 THEN
1439 fnd_message.set_name ('CSD', 'CSD_API_PARTY_MISSING');
1440 fnd_message.set_token ('INCIDENT_ID', l_incident_id);
1441 fnd_msg_pub.ADD;
1442
1443 CLOSE cur_cust_details;
1444
1445 RAISE fnd_api.g_exc_error;
1446 END IF;
1447
1448 CLOSE cur_cust_details;
1449 END IF;
1450
1451 ---------------------------------------------------------------------------------6
1452
1453 --Derive the line_type and line_category from the txn_billing_Type_id and organization_id.
1454 --If line_type or line_Category is null raise error.
1455 csd_process_util.get_line_type
1456 (p_txn_billing_type_id => p_product_txn_rec.txn_billing_type_id,
1457 p_org_id => p_product_txn_rec.organization_id,
1458 x_line_type_id => l_line_type_id,
1459 x_line_category_code => l_line_category_code,
1460 x_return_status => x_return_status
1461 );
1462
1463 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
1464 THEN
1465 RAISE fnd_api.g_exc_error;
1466 END IF;
1467
1468 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1469 THEN
1470 fnd_log.STRING (fnd_log.level_statement,
1471 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1472 'l_line_type_id =' || l_line_type_id
1473 );
1474 fnd_log.STRING (fnd_log.level_statement,
1475 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1476 'l_line_category_code ='
1477 || l_line_category_code
1478 );
1479 fnd_log.STRING (fnd_log.level_statement,
1480 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1481 'p_product_txn_rec.price_list_id ='
1482 || p_product_txn_rec.price_list_id
1483 );
1484 END IF;
1485
1486 -- If line_type_id Or line_category_code is null
1487 -- then raise error
1488 IF (l_line_type_id IS NULL OR l_line_category_code IS NULL)
1489 THEN
1490 fnd_message.set_name ('CSD', 'CSD_API_LINE_TYPE_MISSING');
1491 fnd_message.set_token ('TXN_BILLING_TYPE_ID',
1492 p_product_txn_rec.txn_billing_type_id
1493 );
1494 fnd_msg_pub.ADD;
1495 RAISE fnd_api.g_exc_error;
1496 END IF;
1497
1498 ---------------------------------------------------------------------------------7
1499
1500 --If the item is serialzed check if the serial number is provided.
1501 l_serial_flag :=
1502 is_item_pre_serialized (p_product_txn_rec.inventory_item_id);
1503
1504 -- Serial Number required if the item is serialized
1505 IF l_serial_flag AND p_product_txn_rec.source_serial_number IS NULL
1506 THEN
1507 IF ( p_product_txn_rec.action_type IN ('RMA', 'WALK_IN_RECEIPT')
1508 OR ( p_product_txn_rec.ship_sales_order_flag = 'Y'
1509 AND p_product_txn_rec.process_txn_flag = 'Y'
1510 )
1511 )
1512 THEN
1513 fnd_message.set_name ('CSD', 'CSD_API_SERIAL_NUM_MISSING');
1514 fnd_message.set_token ('INVENTORY_ITEM_ID',
1515 p_product_txn_rec.inventory_item_id
1516 );
1517 fnd_msg_pub.ADD;
1518
1519 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1520 THEN
1521 fnd_log.STRING
1522 (fnd_log.level_statement,
1523 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1524 'Serial Number missing for inventory_item_id='
1525 || p_product_txn_rec.inventory_item_id
1526 );
1527 END IF;
1528
1529 RAISE fnd_api.g_exc_error;
1530 END IF;
1531 END IF;
1532
1533 ---------------------------------------------------------------------------------8
1534 --Derive currency_Code from the oe_price_lists_all for the given price_list_id
1535 -- Get the currency code from the price list if it is null or g_miss
1536 IF NVL (p_product_txn_rec.price_list_id, fnd_api.g_miss_num) <>
1537 fnd_api.g_miss_num
1538 THEN
1539 OPEN cur_pricelist_details (p_product_txn_rec.price_list_id);
1540
1541 FETCH cur_pricelist_details
1542 INTO l_curr_code;
1543
1544 IF (cur_pricelist_details%NOTFOUND)
1545 THEN
1546 fnd_message.set_name ('CSD', 'CSD_API_INV_PRICE_LIST_ID');
1547 fnd_message.set_token ('PRICE_LIST_ID',
1548 p_product_txn_rec.price_list_id
1549 );
1550 fnd_msg_pub.ADD;
1551
1552 CLOSE cur_pricelist_details;
1553
1554 RAISE fnd_api.g_exc_error;
1555 END IF;
1556
1557 CLOSE cur_pricelist_details;
1558
1559 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1560 THEN
1561 fnd_log.STRING (fnd_log.level_statement,
1562 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1563 'l_curr_code =' || l_curr_code
1564 );
1565 END IF;
1566 END IF;
1567
1568 ---------------------------------------------------------------------------------9
1569
1570 l_add_to_same_order := p_add_to_order_flag;
1571 l_order_header_id := null;
1572
1573 -- If the source is RMA then process differently
1574 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1575 THEN
1576 fnd_log.STRING (fnd_log.level_statement,
1577 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1578 'orignal source ref=[' || l_orig_src_reference || ']'
1579 );
1580 fnd_log.STRING (fnd_log.level_statement,
1581 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1582 'l_add_to_same_order=[' || l_add_to_same_order || ']'
1583 );
1584 END IF;
1585
1586 IF l_orig_src_reference = 'RMA'
1587 THEN -------------IF A
1588 l_order_header_id := l_orig_src_header_id;
1589 ELSE
1590 OPEN cur_order_header (l_incident_id);
1591
1592 FETCH cur_order_header
1593 INTO l_order_header_id;
1594
1595 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1596 THEN
1597 IF (cur_order_header%NOTFOUND)
1598 THEN
1599 fnd_log.STRING
1600 (fnd_log.level_statement,
1601 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1602 'Getting max order header id failed '
1603 );
1604 ELSE
1605 fnd_log.STRING
1606 (fnd_log.level_statement,
1607 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1608 'Order header id[' || l_order_header_id || ']'
1609 );
1610 END IF;
1611 END IF;
1612
1613 CLOSE cur_order_header;
1614 END IF; --------------IF A
1615
1616 --Fixed for bug#4631642
1617 /* IF p_product_txn_rec.new_order_flag <> 'Y'
1618 THEN */ ----------IF B
1619 IF ( p_product_txn_rec.process_txn_flag = 'Y'
1620 AND p_product_txn_rec.interface_to_om_flag = 'Y'
1621 )
1622 THEN ---------IF C
1623
1624 IF (l_add_to_same_order = 'Y' and l_order_header_id is not null)
1625 THEN
1626 p_product_txn_rec.add_to_order_flag := 'Y';
1627 p_product_txn_rec.order_header_id := l_order_header_id;
1628 ELSE
1629 p_product_txn_rec.add_to_order_flag := 'F';
1630 p_product_txn_rec.order_header_id := fnd_api.g_miss_num;
1631 END IF;
1632 END IF; ----------------IF C
1633 /* END IF; */ ----------IF B
1634
1635 -- assigning values for the charge record
1636 p_product_txn_rec.incident_id := l_incident_id;
1637 p_product_txn_rec.business_process_id := l_bus_process_id;
1638 p_product_txn_rec.line_type_id := l_line_type_id;
1639 p_product_txn_rec.currency_code := l_curr_code;
1640 p_product_txn_rec.line_category_code := l_line_category_code;
1641
1642 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1643 THEN
1644 fnd_log.STRING (fnd_log.level_statement,
1645 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1646 'Convert product txn rec to charges rec'
1647 );
1648 END IF;
1649
1650 ---------------------------------------------------------------------------------10
1651
1652 --Create Charge line
1653 --Call CSD_PROCESS_UTIL.CONVERT_TO_CHG_REC to populate charges record.
1654 -- Convert the product txn record to
1655 -- charge record
1656 csd_process_util.convert_to_chg_rec
1657 (p_prod_txn_rec => p_product_txn_rec,
1658 x_charges_rec => l_charges_rec,
1659 x_return_status => x_return_status
1660 );
1661
1662 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
1663 THEN
1664 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1665 THEN
1666 fnd_log.STRING (fnd_log.level_statement,
1667 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1668 'sql error[' || SQLERRM || ']'
1669 );
1670 END IF;
1671
1672 RAISE fnd_api.g_exc_error;
1673 END IF;
1674
1675 ----------Temp code
1676 l_charges_rec.charge_line_type := 'ACTUAL';
1677
1678 ---------------------------------------------------------------------------------11
1679 --Call CSD_PROCESS_PVT.PROCESS_CHARGE_LINES with 'CREATE' as input parameter to create charge line.
1680 --Update estimate_Detail_id in the Product_Txn_rec.
1681 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1682 THEN
1683 fnd_log.STRING
1684 (fnd_log.level_statement,
1685 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1686 'Calling process_charge_lines to create charge lines '
1687 );
1688 END IF;
1689
1690 csd_process_pvt.process_charge_lines
1691 (p_api_version => 1.0,
1692 p_commit => fnd_api.g_false,
1693 p_init_msg_list => fnd_api.g_false,
1694 p_validation_level => fnd_api.g_valid_level_full,
1695 p_action => 'CREATE',
1696 p_charges_rec => l_charges_rec,
1697 x_estimate_detail_id => l_estimate_detail_id,
1698 x_return_status => x_return_status,
1699 x_msg_count => x_msg_count,
1700 x_msg_data => x_msg_data
1701 );
1702
1703 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
1704 THEN
1705 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1706 THEN
1707 fnd_log.STRING (fnd_log.level_statement,
1708 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1709 'Error[' || SUBSTR (x_msg_data, 1, 200) || ']'
1710 );
1711 END IF;
1712
1713 RAISE fnd_api.g_exc_error;
1714 END IF;
1715
1716 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1717 THEN
1718 fnd_log.STRING (fnd_log.level_statement,
1719 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1720 'Created charge line[' || l_estimate_detail_id || ']'
1721 );
1722 END IF;
1723
1724 ---------------------------------------------------------------------------------12
1725 --Call CSD_PRODUCT_TRANSACTIONS_PKG.INSERT_ROW to add a product transaction record.
1726 --Update product_transaction_id in the Product_Txn_Rec.
1727 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1728 THEN
1729 fnd_log.STRING (fnd_log.level_statement,
1730 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1731 'Creating product txn rec ..'
1732 );
1733 END IF;
1734
1735 csd_product_transactions_pkg.insert_row
1736 (px_product_transaction_id => p_product_txn_rec.product_transaction_id,
1737 p_repair_line_id => p_product_txn_rec.repair_line_id,
1738 p_estimate_detail_id => l_estimate_detail_id,
1739 p_action_type => p_product_txn_rec.action_type,
1740 p_action_code => p_product_txn_rec.action_code,
1741 p_lot_number => p_product_txn_rec.lot_number,
1742 -- Following parameter is not used from 11.5.10 release
1743 -- p_SHIPPED_SERIAL_NUMBER => p_product_txn_rec.SHIPPED_SERIAL_NUMBER,
1744 p_sub_inventory => p_product_txn_rec.sub_inventory,
1745 p_interface_to_om_flag => p_product_txn_rec.interface_to_om_flag,
1746 p_book_sales_order_flag => p_product_txn_rec.book_sales_order_flag,
1747 p_release_sales_order_flag => p_product_txn_rec.release_sales_order_flag,
1748 p_ship_sales_order_flag => p_product_txn_rec.ship_sales_order_flag,
1749 p_prod_txn_status => p_product_txn_rec.prod_txn_status,
1750 p_prod_txn_code => p_product_txn_rec.prod_txn_code,
1751 p_last_update_date => SYSDATE,
1752 p_creation_date => SYSDATE,
1753 p_last_updated_by => fnd_global.user_id,
1754 p_created_by => fnd_global.user_id,
1755 p_last_update_login => fnd_global.user_id,
1756 p_attribute1 => p_product_txn_rec.attribute1,
1757 p_attribute2 => p_product_txn_rec.attribute2,
1758 p_attribute3 => p_product_txn_rec.attribute3,
1759 p_attribute4 => p_product_txn_rec.attribute4,
1760 p_attribute5 => p_product_txn_rec.attribute5,
1761 p_attribute6 => p_product_txn_rec.attribute6,
1762 p_attribute7 => p_product_txn_rec.attribute7,
1763 p_attribute8 => p_product_txn_rec.attribute8,
1764 p_attribute9 => p_product_txn_rec.attribute9,
1765 p_attribute10 => p_product_txn_rec.attribute10,
1766 p_attribute11 => p_product_txn_rec.attribute11,
1767 p_attribute12 => p_product_txn_rec.attribute12,
1768 p_attribute13 => p_product_txn_rec.attribute13,
1769 p_attribute14 => p_product_txn_rec.attribute14,
1770 p_attribute15 => p_product_txn_rec.attribute15,
1771 p_context => p_product_txn_rec.CONTEXT,
1772 p_object_version_number => 1,
1773 p_req_header_id => p_product_txn_rec.req_header_id,
1774 p_req_line_id => p_product_txn_rec.req_line_id,
1775 p_order_header_id => p_product_txn_rec.order_header_id,
1776 p_order_line_id => p_product_txn_rec.order_line_id,
1777 p_prd_txn_qty_received => p_product_txn_rec.prd_txn_qty_received,
1778 p_prd_txn_qty_shipped => p_product_txn_rec.prd_txn_qty_shipped,
1779 p_source_serial_number => p_product_txn_rec.source_serial_number,
1780 p_source_instance_id => p_product_txn_rec.source_instance_id,
1781 p_non_source_serial_number => p_product_txn_rec.non_source_serial_number,
1782 p_non_source_instance_id => p_product_txn_rec.non_source_instance_id,
1783 p_locator_id => p_product_txn_rec.locator_id,
1784 p_sub_inventory_rcvd => p_product_txn_rec.sub_inventory_rcvd,
1785 p_lot_number_rcvd => p_product_txn_rec.lot_number_rcvd,
1786 p_picking_rule_id => p_product_txn_rec.picking_rule_id,
1787 p_project_id => p_product_txn_rec.project_id,
1788 p_task_id => p_product_txn_rec.task_id,
1789 p_unit_number => p_product_txn_rec.unit_number);
1790
1791 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
1792 THEN
1793 RAISE fnd_api.g_exc_error;
1794 END IF;
1795
1796 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1797 THEN
1798 fnd_log.STRING (fnd_log.level_statement,
1799 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1800 'PRODUCT_TRANSACTION_ID='
1801 || p_product_txn_rec.product_transaction_id
1802 );
1803 END IF;
1804
1805 ---------------------------------------------------------------------------------13
1806 --If Auto_RMA_Flag is 'Y' then create and book order.
1807
1808 --Call CS_EST_APPLY_CONTRACT_PKG.APPLY_CONTRACT with incident_id as parameter.
1809 --Call CS_Charge_Create_Order_PUB.Submit_Order with Incident_Id, party_id and
1810 --account_id. For 11.5.8 use Process_Sales_orderSet book_order_flag = 'Y' if the
1811 --Book_order_flag in Product_Txn_Rec is 'Y' otherwise 'N'.
1812 --If the return_status <> 'S' then rollback to savepoint Create_product_txn and
1813 --raise exception, else proceed to next step.
1814 ------------------------------------------------------------------------------------
1815 IF (p_product_txn_rec.interface_to_om_flag = 'Y')
1816 THEN
1817 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1818 THEN
1819 fnd_log.STRING
1820 (fnd_log.level_statement,
1821 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1822 'Call CSD_PROCESS_PVT.APPLY_CONTRACT to apply contract '
1823 );
1824 END IF;
1825
1826 csd_process_pvt.apply_contract
1827 (p_api_version => 1.0,
1828 p_commit => fnd_api.g_false,
1829 p_init_msg_list => fnd_api.g_false,
1830 p_validation_level => fnd_api.g_valid_level_full,
1831 p_incident_id => l_incident_id,
1832 x_return_status => x_return_status,
1833 x_msg_count => x_msg_count,
1834 x_msg_data => x_msg_data
1835 );
1836
1837 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
1838 THEN
1839 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1840 THEN
1841 fnd_log.STRING
1842 (fnd_log.level_statement,
1843 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1844 'apply_contract failed['
1845 || SUBSTR (x_msg_data, 1, 200)
1846 || ']'
1847 );
1848 END IF;
1849
1850 RAISE fnd_api.g_exc_error;
1851 END IF;
1852
1853 -- THis api will interface and book the charge line
1854 ------------------------------------------------------------------------------------
1855 --Update the status in CSD_PRODUCT_TXNS table and CSD_REPAIRS table. If the
1856 --interface_to_oe_flag is 'Y' and book_order_flag is 'Y' then --set the
1857 --prod_txn_status to 'BOOKED' and ro_txn_status to 'OM_BOOKED'. If
1858 --interface_to_oe_flag is 'Y' and book_order_flag is 'N' set
1859 --the prod_txn_status to 'SUBMITTED' and ro_txn_status to 'OM_SUBMITTED'. If
1860 --interface_to_oe_flag is 'N', set the prod_txn_status
1861 --to 'ENTERED' and ro_txn_status to 'CHARGE_ENTERED'.
1862 ----------------------------------------------------------------------------
1863 IF p_product_txn_rec.book_sales_order_flag = 'Y'
1864 THEN
1865 l_ro_txn_status := 'OM_BOOKED';
1866 l_prod_txn_status := 'BOOKED';
1867 l_booked_flag := 'Y';
1868 ELSE
1869 l_ro_txn_status := 'OM_SUBMITTED';
1870 l_prod_txn_status := 'SUBMITTED';
1871 l_booked_flag := 'N';
1872 END IF;
1873
1874 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1875 THEN
1876 fnd_log.STRING (fnd_log.level_statement,
1877 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1878 'calling CSD_PROCESS_PVT.PROCESS_SALES_ORDER to interface '
1879 || 'Charge line without booking'
1880 );
1881 END IF;
1882
1883 ---Bug fix 3308435 [
1884 -- Assigning values for the order record
1885 l_order_rec.incident_id := l_incident_id;
1886 l_order_rec.party_id := l_party_id ;
1887 l_order_rec.account_id := l_account_id ;
1888 l_order_rec.org_id := p_product_txn_rec.organization_id ;
1889
1890 CSD_PROCESS_PVT.PROCESS_SALES_ORDER
1891 ( p_api_version => 1.0 ,
1892 p_commit => fnd_api.g_false,
1893 p_init_msg_list => fnd_api.g_false,
1894 p_validation_level => fnd_api.g_valid_level_full,
1895 p_action => 'CREATE',
1896 p_order_rec => l_order_rec,
1897 x_return_status => x_return_status,
1898 x_msg_count => x_msg_count,
1899 x_msg_data => x_msg_data );
1900
1901 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1902 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1903 THEN
1904 fnd_log.STRING (fnd_log.level_statement,
1905 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1906 'process_sales_order failed['||x_msg_data||']');
1907 END IF;
1908 RAISE FND_API.G_EXC_ERROR;
1909 END IF;
1910
1911 Validate_order(p_est_detail_id => l_estimate_detail_id,
1912 p_order_rec => l_order_rec,
1913 x_booked_flag => l_booked_flag
1914 );
1915
1916 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1917 THEN
1918 fnd_log.STRING (fnd_log.level_statement,
1919 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1920 'Booking charge line with CSD_PROCESS_PVT.PROCESS_SALES_ORDER API');
1921 END IF;
1922
1923
1924 IF(l_booked_flag <> 'Y') THEN
1925 CSD_PROCESS_PVT.PROCESS_SALES_ORDER
1926 ( p_api_version => 1.0 ,
1927 p_commit => fnd_api.g_false,
1928 p_init_msg_list => fnd_api.g_false,
1929 p_validation_level => fnd_api.g_valid_level_full,
1930 p_action => 'BOOK',
1931 p_order_rec => l_order_rec,
1932 x_return_status => x_return_status,
1933 x_msg_count => x_msg_count,
1934 x_msg_data => x_msg_data );
1935
1936 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1937 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1938 THEN
1939 fnd_log.STRING (fnd_log.level_statement,
1940 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1941 'process_sales_order failed ['||x_msg_Data||']');
1942 END IF;
1943 RAISE FND_API.G_EXC_ERROR;
1944 END IF;
1945 END IF;
1946 /****************
1947 cs_charge_create_order_pub.submit_order
1948 (p_api_version => 1.0,
1949 p_init_msg_list => p_init_msg_list,
1950 p_commit => p_commit,
1951 p_validation_level => p_validation_level,
1952 p_incident_id => l_incident_id,
1953 p_party_id => l_party_id,
1954 p_account_id => l_account_id,
1955 p_book_order_flag => l_booked_flag,
1956 x_return_status => x_return_status,
1957 x_msg_count => x_msg_count,
1958 x_msg_data => x_msg_data
1959 );
1960 *************/
1961
1962 --] 3308535
1963 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
1964 THEN
1965 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1966 THEN
1967 fnd_log.STRING
1968 (fnd_log.level_statement,
1969 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1970 'submit_Order failed ['
1971 || SUBSTR (x_msg_data, 1, 200)
1972 || ']'
1973 );
1974 END IF;
1975
1976 RAISE fnd_api.g_exc_error;
1977 END IF;
1978 ELSE
1979 l_ro_txn_status := 'CHARGE_ENTERED';
1980 l_prod_txn_status := 'ENTERED';
1981 END IF;
1982
1983 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1984 THEN
1985 fnd_log.STRING (fnd_log.level_statement,
1986 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
1987 'Updating repair status[' || l_ro_txn_status || ']'
1988 );
1989 END IF;
1990
1991 UPDATE csd_repairs
1992 SET ro_txn_status = l_ro_txn_status
1993 WHERE repair_line_id = p_product_txn_rec.repair_line_id;
1994
1995 IF SQL%NOTFOUND
1996 THEN
1997 fnd_message.set_name ('CSD', 'CSD_ERR_REPAIRS_UPDATE');
1998 fnd_message.set_token ('REPAIR_LINE_ID',
1999 p_product_txn_rec.repair_line_id
2000 );
2001 fnd_msg_pub.ADD;
2002 RAISE fnd_api.g_exc_error;
2003 END IF;
2004
2005 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2006 THEN
2007 fnd_log.STRING (fnd_log.level_statement,
2008 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
2009 'Updating prod txn status[' || l_prod_txn_status
2010 || ']'
2011 );
2012 END IF;
2013
2014 UPDATE csd_product_transactions
2015 SET prod_txn_status = l_prod_txn_status
2016 WHERE product_transaction_id = p_product_txn_rec.product_transaction_id;
2017
2018 IF SQL%NOTFOUND
2019 THEN
2020 fnd_message.set_name ('CSD', 'CSD_ERR_PRD_TXN_UPDATE');
2021 fnd_message.set_token ('PRODUCT_TRANSACTION_ID',
2022 p_product_txn_rec.product_transaction_id
2023 );
2024 fnd_msg_pub.ADD;
2025 RAISE fnd_api.g_exc_error;
2026 END IF;
2027
2028 ----------------------------------------------------------------------------
2029
2030 -- Api body ends here
2031
2032 -- Standard check of p_commit.
2033 IF fnd_api.to_boolean (p_commit)
2034 THEN
2035 COMMIT WORK;
2036 END IF;
2037
2038 -- Standard call to get message count and IF count is get message info.
2039 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2040 p_data => x_msg_data);
2041
2042 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2043 THEN
2044 fnd_log.STRING (fnd_log.level_procedure,
2045 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN.END',
2046 'Leaving CREATE_PRODUCT_TXN'
2047 );
2048 END IF;
2049 EXCEPTION
2050 WHEN fnd_api.g_exc_error
2051 THEN
2052 x_return_status := fnd_api.g_ret_sts_error;
2053 log_error_stack ();
2054 ROLLBACK TO sp_create_product_txn;
2055 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2056 p_data => x_msg_data
2057 );
2058
2059 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
2060 THEN
2061 fnd_log.STRING (fnd_log.level_error,
2062 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
2063 'EXC_ERROR[' || x_msg_data || ']'
2064 );
2065 END IF;
2066 WHEN fnd_api.g_exc_unexpected_error
2067 THEN
2068 x_return_status := fnd_api.g_ret_sts_unexp_error;
2069 ROLLBACK TO sp_create_product_txn;
2070 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2071 p_data => x_msg_data
2072 );
2073
2074 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2075 THEN
2076 fnd_log.STRING (fnd_log.level_exception,
2077 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
2078 'EXC_UNEXPECTED_ERROR[' || x_msg_data || ']'
2079 );
2080 END IF;
2081 WHEN OTHERS
2082 THEN
2083 x_return_status := fnd_api.g_ret_sts_unexp_error;
2084 ROLLBACK TO sp_create_product_txn;
2085
2086 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2087 THEN
2088 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2089 END IF;
2090
2091 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2092 p_data => x_msg_data
2093 );
2094
2095 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
2096 THEN
2097 fnd_log.STRING (fnd_log.level_unexpected,
2098 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_PRODUCT_TXN',
2099 'SQL Error[' || SQLERRM || ']'
2100 );
2101 END IF;
2102 END create_product_txn;
2103
2104 -- This api would be called from the serial number capture screen.
2105 -- If user enters serialized and ib trackable item,
2106 -- and the serial number does not exist in IB, then message pops .
2107 -- If users clicks OK button then this API would be called to create a new instance.
2108 PROCEDURE create_item_instance (
2109 p_api_version IN NUMBER,
2110 p_init_msg_list IN VARCHAR2,
2111 p_commit IN VARCHAR2,
2112 p_validation_level IN NUMBER,
2113 x_return_status OUT NOCOPY VARCHAR2,
2114 x_msg_count OUT NOCOPY NUMBER,
2115 x_msg_data OUT NOCOPY VARCHAR2,
2116 px_instance_rec IN OUT NOCOPY instance_rec_type,
2117 x_instance_id OUT NOCOPY NUMBER
2118 )
2119 IS
2120 l_api_name CONSTANT VARCHAR2 (30) := 'Create_Item_Instance';
2121 l_api_version CONSTANT NUMBER := 1.0;
2122 l_return_status VARCHAR2 (1);
2123 l_msg_data VARCHAR2 (2000);
2124 l_msg_count NUMBER;
2125 --l_item_rec Item_Rec_Type;
2126 --l_defaulted_item_rec Item_Rec_Type;
2127 l_instance_rec csi_datastructures_pub.instance_rec;
2128 --csd_process_util.ui_instance_rec;
2129 l_parties_tbl csi_datastructures_pub.party_tbl;
2130 ---csd_process_util.ui_party_tbl;
2131 l_pty_accts_tbl csi_datastructures_pub.party_account_tbl;
2132 -- := csd_process_util.ui_party_account_tbl;
2133 l_org_units_tbl csi_datastructures_pub.organization_units_tbl;
2134 --:= csd_process_util.ui_organization_units_tbl;
2135 l_ea_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
2136 --:= csd_process_util.ui_extend_attrib_values_tbl;
2137 l_pricing_tbl csi_datastructures_pub.pricing_attribs_tbl;
2138 -- := csd_process_util.ui_pricing_attribs_tbl;
2139 l_assets_tbl csi_datastructures_pub.instance_asset_tbl;
2140 --:= csd_process_util.ui_instance_asset_tbl;
2141 l_txn_rec csi_datastructures_pub.transaction_rec;
2142 -- := csd_process_util.ui_transaction_rec;
2143 l_party_site_id NUMBER;
2144 BEGIN
2145 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2146 THEN
2147 fnd_log.STRING
2148 (fnd_log.level_procedure,
2149 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_ITEM_INSTANCE.BEGIN',
2150 'Entered CREATE_ITEM_INSTANCE'
2151 );
2152 END IF;
2153
2154 -- Standard Start of API savepoint
2155 SAVEPOINT create_item_instance;
2156
2157 -- Standard call to check for call compatibility.
2158 IF NOT fnd_api.compatible_api_call (l_api_version,
2159 p_api_version,
2160 -- l_api_name ,
2161 g_pkg_name,
2162 g_file_name
2163 )
2164 THEN
2165 RAISE fnd_api.g_exc_unexpected_error;
2166 END IF;
2167
2168 -- Initialize message list if p_init_msg_list is set to TRUE.
2169 IF fnd_api.to_boolean (p_init_msg_list)
2170 THEN
2171 fnd_msg_pub.initialize;
2172 END IF;
2173
2174 -- Initialize API return status to success
2175 x_return_status := fnd_api.g_ret_sts_success;
2176
2177 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2178 THEN
2179 fnd_log.STRING (fnd_log.level_statement,
2180 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_ITEM_INSTANCE',
2181 'Finding party_site_Id for party_site_use_Id=['
2182 || px_instance_rec.party_site_use_id
2183 || ']'
2184 );
2185 END IF;
2186
2187 BEGIN
2188 SELECT party_site_id
2189 INTO l_party_site_id
2190 FROM hz_party_site_uses
2191 WHERE party_site_use_id = px_instance_rec.party_site_use_id;
2192 --px_instance_rec.bill_to_site_use_id;
2193 EXCEPTION
2194 WHEN NO_DATA_FOUND
2195 THEN
2196 fnd_message.set_name ('CSD', 'CSD_INVALID_SITE_USED_ID');
2197 fnd_message.set_token ('BILL_TO_SITE_USE_ID',
2198 px_instance_rec.party_site_use_id
2199 );
2200 --px_instance_rec.bill_to_site_use_id);
2201 RAISE fnd_api.g_exc_error;
2202 END;
2203
2204 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2205 THEN
2206 fnd_log.STRING (fnd_log.level_statement,
2207 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_ITEM_INSTANCE',
2208 'party_site_Id=[' || l_party_site_id || ']'
2209 );
2210 END IF;
2211
2212 l_instance_rec.inventory_item_id := px_instance_rec.inventory_item_id;
2213 l_instance_rec.inventory_revision := px_instance_rec.item_revision;
2214 l_instance_rec.serial_number := px_instance_rec.serial_number;
2215 --IF nvl(l_instance_rec.serial_number, 'AAA') <> 'AAA' THEN
2216 -- l_instance_rec.mfg_serial_number_flag := 'Y';
2217 --ELSE
2218 l_instance_rec.mfg_serial_number_flag := 'N';
2219 --END IF;
2220
2221 --l_instance_rec.mfg_serial_number_flag :=
2222 --px_instance_rec.mfg_serial_number_flag;
2223 l_instance_rec.lot_number := px_instance_rec.lot_number;
2224 l_instance_rec.quantity := px_instance_rec.quantity;
2225 l_instance_rec.active_start_date := SYSDATE;
2226 l_instance_rec.active_end_date := NULL;
2227 l_instance_rec.unit_of_measure := px_instance_rec.uom;
2228 l_instance_rec.location_type_code := 'HZ_PARTY_SITES';
2229 l_instance_rec.location_id := l_party_site_id;
2230 l_instance_rec.instance_usage_code := 'OUT_OF_ENTERPRISE';
2231 --l_instance_rec.inv_master_organization_id :=
2232 --cs_std.get_item_valdn_orgzn_id;
2233 l_instance_rec.vld_organization_id := cs_std.get_item_valdn_orgzn_id;
2234 l_instance_rec.customer_view_flag := 'N';
2235 l_instance_rec.merchant_view_flag := 'Y';
2236 l_instance_rec.object_version_number := 1;
2237 l_parties_tbl (1).party_source_table := 'HZ_PARTIES';
2238 l_parties_tbl (1).party_id := px_instance_rec.party_id;
2239 l_parties_tbl (1).relationship_type_code := 'OWNER';
2240 l_parties_tbl (1).contact_flag := 'N';
2241 l_pty_accts_tbl (1).parent_tbl_index := 1;
2242 l_pty_accts_tbl (1).party_account_id := px_instance_rec.account_id;
2243 l_pty_accts_tbl (1).relationship_type_code := 'OWNER';
2244 l_pty_accts_tbl (1).active_start_date := SYSDATE;
2245 l_txn_rec.transaction_id := NULL;
2246 l_txn_rec.transaction_date := SYSDATE;
2247 l_txn_rec.source_transaction_date := SYSDATE;
2248 l_txn_rec.transaction_type_id := 1;
2249 l_txn_rec.txn_sub_type_id := NULL;
2250 l_txn_rec.source_group_ref_id := NULL;
2251 l_txn_rec.source_group_ref := '';
2252 l_txn_rec.source_header_ref_id := NULL;
2253 l_txn_rec.source_header_ref := '';
2254 l_txn_rec.source_line_ref_id := NULL;
2255 l_txn_rec.source_line_ref := '';
2256 l_txn_rec.source_dist_ref_id1 := NULL;
2257 l_txn_rec.source_dist_ref_id2 := NULL;
2258 l_txn_rec.inv_material_transaction_id := NULL;
2259 l_txn_rec.transaction_quantity := NULL;
2260 l_txn_rec.transaction_uom_code := '';
2261 l_txn_rec.transacted_by := NULL;
2262 l_txn_rec.transaction_status_code := '';
2263 l_txn_rec.transaction_action_code := '';
2264 l_txn_rec.message_id := NULL;
2265 l_txn_rec.object_version_number := NULL;
2266 l_txn_rec.split_reason_code := '';
2267
2268 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2269 THEN
2270 fnd_log.STRING (fnd_log.level_statement,
2271 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_ITEM_INSTANCE',
2272 'Calling csi_item_instance_pub.create_item_instance'
2273 );
2274 END IF;
2275
2276 csi_item_instance_pub.create_item_instance
2277 (p_api_version => 1.0,
2278 p_commit => csd_process_util.g_false,
2279 p_init_msg_list => csd_process_util.g_false,
2280 p_validation_level => csd_process_util.g_valid_level_full,
2281 p_instance_rec => l_instance_rec,
2282 p_party_tbl => l_parties_tbl,
2283 p_account_tbl => l_pty_accts_tbl,
2284 p_org_assignments_tbl => l_org_units_tbl,
2285 p_ext_attrib_values_tbl => l_ea_values_tbl,
2286 p_pricing_attrib_tbl => l_pricing_tbl,
2287 p_asset_assignment_tbl => l_assets_tbl,
2288 p_txn_rec => l_txn_rec,
2289 x_return_status => l_return_status,
2290 x_msg_count => l_msg_count,
2291 x_msg_data => l_msg_data
2292 );
2293
2294 log_error_stack();
2295 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
2296 THEN
2297 RAISE fnd_api.g_exc_error;
2298 END IF;
2299
2300 x_instance_id := l_instance_rec.instance_id;
2301 px_instance_rec.instance_id := l_instance_rec.instance_id;
2302 px_instance_rec.instance_number := l_instance_rec.instance_number;
2303
2304 -- Standard check of p_commit.
2305 IF fnd_api.to_boolean (p_commit)
2306 THEN
2307 COMMIT WORK;
2308 END IF;
2309
2310 -- Standard call to get message count and if count is 1, get message info.
2311 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2312 p_data => x_msg_data);
2313
2314 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2315 THEN
2316 fnd_log.STRING
2317 (fnd_log.level_procedure,
2318 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_ITEM_INSTANCE.END',
2319 'Leaving CREATE_ITEM_INSTANCE'
2320 );
2321 END IF;
2322 EXCEPTION
2323 WHEN fnd_api.g_exc_error
2324 THEN
2325 ROLLBACK TO create_item_instance;
2326 x_return_status := fnd_api.g_ret_sts_error;
2327 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2328 p_data => x_msg_data
2329 );
2330
2331 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
2332 THEN
2333 fnd_log.STRING
2334 (fnd_log.level_error,
2335 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_ITEM_INSTANCE',
2336 'EXC_ERROR[' || x_msg_data || ']'
2337 );
2338 END IF;
2339 WHEN fnd_api.g_exc_unexpected_error
2340 THEN
2341 ROLLBACK TO create_item_instance;
2342 x_return_status := fnd_api.g_ret_sts_unexp_error;
2343 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2344 p_data => x_msg_data
2345 );
2346
2347 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2348 THEN
2349 fnd_log.STRING
2350 (fnd_log.level_exception,
2351 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_ITEM_INSTANCE',
2352 'EXC_UNEXPECTED_ERROR[' || x_msg_data || ']'
2353 );
2354 END IF;
2355 WHEN OTHERS
2356 THEN
2357 ROLLBACK TO create_item_instance;
2358 x_return_status := fnd_api.g_ret_sts_unexp_error;
2359
2360 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2361 THEN
2362 fnd_msg_pub.add_exc_msg (g_file_name, g_pkg_name, l_api_name);
2363 END IF;
2364
2365 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2366 p_data => x_msg_data
2367 );
2368
2369 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
2370 THEN
2371 fnd_log.STRING
2372 (fnd_log.level_unexpected,
2373 'CSD.PLSQL.CSD_MASS_RCV_PVT.CREATE_ITEM_INSTANCE',
2374 'SQL MEssage[' || SQLERRM || ']'
2375 );
2376 END IF;
2377 END create_item_instance;
2378
2379
2380 ------------------------------------------------------------------------
2381 PROCEDURE validate_product_txn_rec (
2382 p_product_txn_rec IN csd_process_pvt.product_txn_rec
2383 )
2384 IS
2385 l_api_version_number CONSTANT NUMBER := 1.0;
2386 l_api_name CONSTANT VARCHAR2 (30) := 'PROCESS_RO';
2387 l_return_status VARCHAR2 (1) ;
2388 l_check VARCHAR2 (1);
2389 BEGIN
2390 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2391 THEN
2392 fnd_log.STRING
2393 (fnd_log.level_procedure,
2394 'CSD.PLSQL.CSD_MASS_RCV_PVT.VALIDATE_PRODUCT_TXN_REC.BEGIN',
2395 'Entered Validate_Product_Txn_Rec'
2396 );
2397 END IF;
2398
2399 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2400 THEN
2401 fnd_log.STRING
2402 (fnd_log.level_statement,
2403 'CSD.PLSQL.CSD_MASS_RCV_PVT.VALIDATE_PRODUCT_TXN_REC.BEGIN',
2404 'Checking mandatory parameters'
2405 );
2406 END IF;
2407
2408 l_return_status := fnd_api.g_ret_sts_success;
2409
2410 csd_process_util.check_reqd_param
2411 (p_param_value => p_product_txn_rec.repair_line_id,
2412 p_param_name => 'REPAIR_LINE_ID',
2413 p_api_name => l_api_name
2414 );
2415 -- Check the required parameter(action_code)
2416 csd_process_util.check_reqd_param
2417 (p_param_value => p_product_txn_rec.action_code,
2418 p_param_name => 'ACTION_CODE',
2419 p_api_name => l_api_name
2420 );
2421 -- Check the required parameter(action_type)
2422 csd_process_util.check_reqd_param
2423 (p_param_value => p_product_txn_rec.action_type,
2424 p_param_name => 'ACTION_TYPE',
2425 p_api_name => l_api_name
2426 );
2427 -- Check the required parameter(txn_billing_type_id)
2428 csd_process_util.check_reqd_param
2429 (p_param_value => p_product_txn_rec.txn_billing_type_id,
2430 p_param_name => 'TXN_BILLING_TYPE_ID',
2431 p_api_name => l_api_name
2432 );
2433 -- Check the required parameter(inventory_item_id)
2434 csd_process_util.check_reqd_param
2435 (p_param_value => p_product_txn_rec.inventory_item_id,
2436 p_param_name => 'INVENTORY_ITEM_ID',
2437 p_api_name => l_api_name
2438 );
2439 -- Check the required parameter(unit_of_measure_code)
2440 csd_process_util.check_reqd_param
2441 (p_param_value => p_product_txn_rec.unit_of_measure_code,
2442 p_param_name => 'UNIT_OF_MEASURE_CODE',
2443 p_api_name => l_api_name
2444 );
2445 -- Check the required parameter(quantity)
2446 csd_process_util.check_reqd_param
2447 (p_param_value => p_product_txn_rec.quantity,
2448 p_param_name => 'QUANTITY',
2449 p_api_name => l_api_name
2450 );
2451 -- Check the required parameter(price_list_id)
2452 csd_process_util.check_reqd_param
2453 (p_param_value => p_product_txn_rec.price_list_id,
2454 p_param_name => 'PRICE_LIST_ID',
2455 p_api_name => l_api_name
2456 );
2457
2458 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2459 THEN
2460 fnd_log.STRING
2461 (fnd_log.level_statement,
2462 'CSD.PLSQL.CSD_MASS_RCV_PVT.VALIDATE_PRODUCT_TXN_REC',
2463 'Validate repair line id'
2464 );
2465 END IF;
2466
2467 -- Validate the repair line ID if it exists in csd_repairs
2468 IF NOT (csd_process_util.validate_rep_line_id
2469 (p_repair_line_id => p_product_txn_rec.repair_line_id)
2470 )
2471 THEN
2472 RAISE fnd_api.g_exc_error;
2473 END IF;
2474
2475 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2476 THEN
2477 fnd_log.STRING
2478 (fnd_log.level_statement,
2479 'CSD.PLSQL.CSD_MASS_RCV_PVT.VALIDATE_PRODUCT_TXN_REC',
2480 'Validate action type'
2481 );
2482 END IF;
2483
2484 -- Validate the Action Type if it exists in fnd_lookups
2485 IF NOT (csd_process_util.validate_action_type
2486 (p_action_type => p_product_txn_rec.action_type)
2487 )
2488 THEN
2489 RAISE fnd_api.g_exc_error;
2490 END IF;
2491
2492 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2493 THEN
2494 fnd_log.STRING
2495 (fnd_log.level_statement,
2496 'CSD.PLSQL.CSD_MASS_RCV_PVT.VALIDATE_PRODUCT_TXN_REC',
2497 'Validate action code'
2498 );
2499 END IF;
2500
2501 -- Validate the repair line ID if it exists in fnd_lookups
2502 IF NOT (csd_process_util.validate_action_code
2503 (p_action_code => p_product_txn_rec.action_code)
2504 )
2505 THEN
2506 RAISE fnd_api.g_exc_error;
2507 END IF;
2508
2509 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2510 THEN
2511 fnd_log.STRING
2512 (fnd_log.level_statement,
2513 'CSD.PLSQL.CSD_MASS_RCV_PVT.VALIDATE_PRODUCT_TXN_REC',
2514 'Validate product txn qty'
2515 );
2516 fnd_log.STRING
2517 (fnd_log.level_statement,
2518 'CSD.PLSQL.CSD_MASS_RCV_PVT.VALIDATE_PRODUCT_TXN_REC.BEGIN',
2519 'p_product_txn_rec.quantity =' || p_product_txn_rec.quantity
2520 );
2521 END IF;
2522
2523 -- Validate if the product txn quantity (customer product only)
2524 -- is not exceeding the repair order quantity
2525 IF p_product_txn_rec.action_code = 'CUST_PROD'
2526 THEN
2527 csd_process_util.validate_quantity
2528 (p_action_type => p_product_txn_rec.action_type,
2529 p_repair_line_id => p_product_txn_rec.repair_line_id,
2530 p_prod_txn_qty => p_product_txn_rec.quantity,
2531 x_return_status => l_return_status
2532 );
2533
2534 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
2535 THEN
2536 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2537 THEN
2538 fnd_log.STRING
2539 (fnd_log.level_statement,
2540 'CSD.PLSQL.CSD_MASS_RCV_PVT.VALIDATE_PRODUCT_TXN_REC',
2541 'Validate_Quantity failed '
2542 );
2543 END IF;
2544
2545 RAISE fnd_api.g_exc_error;
2546 END IF;
2547 END IF;
2548
2549 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2550 THEN
2551 fnd_log.STRING
2552 (fnd_log.level_statement,
2553 'CSD.PLSQL.CSD_MASS_RCV_PVT.VALIDATE_PRODUCT_TXN_REC',
2554 'Validate product txn status'
2555 );
2556 fnd_log.STRING
2557 (fnd_log.level_statement,
2558 'CSD.PLSQL.CSD_MASS_RCV_PVT.VALIDATE_PRODUCT_TXN_REC.BEGIN',
2559 'p_product_txn_rec.PROD_TXN_STATUS ='
2560 || p_product_txn_rec.prod_txn_status
2561 );
2562 END IF;
2563
2564 -- Validate the PROD_TXN_STATUS if it exists in fnd_lookups
2565 IF (p_product_txn_rec.prod_txn_status IS NOT NULL)
2566 AND (p_product_txn_rec.prod_txn_status <> fnd_api.g_miss_char)
2567 THEN
2568 BEGIN
2569 SELECT 'X'
2570 INTO l_check
2571 FROM fnd_lookups
2572 WHERE lookup_type = 'CSD_PRODUCT_TXN_STATUS'
2573 AND lookup_code = p_product_txn_rec.prod_txn_status;
2574 EXCEPTION
2575 WHEN OTHERS
2576 THEN
2577 fnd_message.set_name ('CSD', 'CSD_ERR_PROD_TXN_STATUS');
2578 fnd_msg_pub.ADD;
2579 RAISE fnd_api.g_exc_error;
2580 END;
2581 END IF;
2582
2583 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2584 THEN
2585 fnd_log.STRING
2586 (fnd_log.level_procedure,
2587 'CSD.PLSQL.CSD_MASS_RCV_PVT.VALIDATE_PRODUCT_TXN_REC.END',
2588 'Leaving Validate_Product_Txn_Rec'
2589 );
2590 END IF;
2591 END validate_product_txn_rec;
2592
2593 ------------------------------------------------------------------------------
2594 -------------------------------------------------------------------------------
2595 FUNCTION is_item_pre_serialized (p_inv_item_id IN NUMBER)
2596 RETURN BOOLEAN
2597 IS
2598 l_serial_code NUMBER ;
2599 BEGIN
2600 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2601 THEN
2602 fnd_log.STRING
2603 (fnd_log.level_procedure,
2604 'CSD.PLSQL.CSD_MASS_RCV_PVT.Is_Item_Pre_serialized.BEGIN',
2605 'Entered Is_Item_Pre_serialized'
2606 );
2607 END IF;
2608
2609 SELECT serial_number_control_code
2610 INTO l_serial_code
2611 FROM mtl_system_items
2612 WHERE inventory_item_id = p_inv_item_id
2613 AND organization_id = cs_std.get_item_valdn_orgzn_id;
2614
2615 IF l_serial_code = 2
2616 THEN -- 2 ==> predefined serial numbers
2617 RETURN TRUE;
2618 ELSE
2619 RETURN FALSE;
2620 END IF;
2621
2622 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2623 THEN
2624 fnd_log.STRING
2625 (fnd_log.level_procedure,
2626 'CSD.PLSQL.CSD_MASS_RCV_PVT.Is_Item_Pre_serialized.END',
2627 'Leaving Is_Item_Pre_serialized'
2628 );
2629 END IF;
2630 EXCEPTION
2631 WHEN NO_DATA_FOUND
2632 THEN
2633 fnd_message.set_name ('CSD', 'CSD_API_INV_ITEM_ID');
2634 fnd_message.set_token ('INVENTORY_ITEM_ID', p_inv_item_id);
2635 fnd_msg_pub.ADD;
2636 RAISE fnd_api.g_exc_error;
2637 END is_item_pre_serialized;
2638
2639 ------------------------------------------------------------------------------
2640 -------------------------------------------------------------------------------
2641 FUNCTION is_item_ib_trackable (p_inv_item_id IN NUMBER)
2642 RETURN BOOLEAN
2643 IS
2644 l_ib_trackable_flag VARCHAR2 (1) ;
2645 BEGIN
2646 SELECT NVL (comms_nl_trackable_flag, 'N')
2647 INTO l_ib_trackable_flag
2648 FROM mtl_system_items
2649 WHERE inventory_item_id = p_inv_item_id
2650 AND organization_id = cs_std.get_item_valdn_orgzn_id;
2651
2652 IF l_ib_trackable_flag = 'Y'
2653 THEN --
2654 RETURN TRUE;
2655 ELSE
2656 RETURN FALSE;
2657 END IF;
2658 EXCEPTION
2659 WHEN NO_DATA_FOUND
2660 THEN
2661 fnd_message.set_name ('CSD', 'CSD_API_INV_ITEM_ID');
2662 fnd_message.set_token ('INVENTORY_ITEM_ID', p_inv_item_id);
2663 fnd_msg_pub.ADD;
2664 RAISE fnd_api.g_exc_error;
2665 END is_item_ib_trackable;
2666
2667 ----------------------------------------------------------------------
2668 -- Funciton to validate the order
2669 ----------------------------------------------------------------------
2670 PROCEDURE validate_order (
2671 p_est_detail_id IN NUMBER,
2672 p_order_rec IN OUT NOCOPY csd_process_pvt.om_interface_rec,
2673 x_booked_flag OUT NOCOPY VARCHAR2
2674 )
2675 IS
2676 l_order_header_id NUMBER;
2677 l_order_line_id NUMBER;
2678 l_ship_from_org_id NUMBER;
2679 l_unit_selling_price NUMBER;
2680
2681 --Cursors
2682 CURSOR cur_ord_hdr (p_header_id NUMBER)
2683 IS
2684 SELECT booked_flag
2685 FROM oe_order_headers_all
2686 WHERE header_id = p_header_id;
2687
2688 --
2689 CURSOR cur_ord_details (p_est_detial_id NUMBER)
2690 IS
2691 SELECT a.order_header_id, a.order_line_id
2692 FROM cs_estimate_details a
2693 WHERE a.estimate_detail_id = p_est_detial_id
2694 AND a.order_header_id IS NOT NULL;
2695
2696 CURSOR cur_ord_line (p_order_line_id NUMBER)
2697 IS
2698 SELECT ship_from_org_id, unit_selling_price, org_id
2699 FROM oe_order_lines_all
2700 WHERE line_id = p_order_line_id;
2701
2702 /*FP Fixed for bug#5368306
2703 OM does not require sales rep at line to book it.
2704 Depot should not check sales rep at line since oe
2705 allows to book an order without a sales rep at
2706 the line.
2707 Following condition which checks sales rep at
2708 order line has been commented.
2709 */
2710 /*AND salesrep_id IS NOT NULL;*/
2711
2712 BEGIN
2713 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2714 THEN
2715 fnd_log.STRING (fnd_log.level_procedure,
2716 'CSD.PLSQL.CSD_MASS_RCV_PVT.VALIDATE_ORDER.BEGIN',
2717 'Entered Validate_order'
2718 );
2719 END IF;
2720
2721 -- Get the order header id
2722 -- from the charge line
2723 OPEN cur_ord_details (p_est_detail_id);
2724
2725 FETCH cur_ord_details
2726 INTO p_order_rec.order_header_id, l_order_line_id;
2727
2728 IF (cur_ord_details%NOTFOUND)
2729 THEN
2730 fnd_message.set_name ('CSD','CSD_API_BOOKING_FAILED'); /*FP Fixed for bug#5147030 message changed*/
2731 /*
2732 fnd_message.set_name ('CSD', 'CSD_API_INV_EST_DETAIL_ID');
2733 fnd_message.set_token ('ESTIMATE_DETAIL_ID', p_est_detail_id);
2734 */
2735 fnd_msg_pub.ADD;
2736
2737 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2738 THEN
2739 fnd_log.STRING (fnd_log.level_statement,
2740 'CSD.PLSQL.CSD_MASS_RCV_PVT.VALIDATE_ORDER',
2741 'Sales Order missing for estimate_detail_id ='
2742 || p_est_detail_id
2743 );
2744 END IF;
2745
2746 CLOSE cur_ord_details;
2747
2748 RAISE fnd_api.g_exc_error;
2749 END IF;
2750
2751 IF cur_ord_details%ROWCOUNT > 1
2752 THEN
2753 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2754 THEN
2755 fnd_log.STRING (fnd_log.level_statement,
2756 'CSD.PLSQL.CSD_MASS_RCV_PVT.VALIDATE_ORDER',
2757 'Too many order header ids'
2758 );
2759 END IF;
2760 END IF;
2761
2762 CLOSE cur_ord_details;
2763
2764 OPEN cur_ord_line (l_order_line_id);
2765
2766 FETCH cur_ord_line
2767 INTO l_ship_from_org_id, l_unit_selling_price, p_order_rec.org_id;
2768
2769 IF (cur_ord_line%NOTFOUND)
2770 THEN
2771 fnd_message.set_name ('CSD', 'CSD_API_SALES_REP_MISSING');
2772 fnd_message.set_token ('ORDER_LINE_ID', l_order_line_id);
2773 fnd_msg_pub.ADD;
2774
2775 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2776 THEN
2777 fnd_log.STRING (fnd_log.level_statement,
2778 'CSD.PLSQL.CSD_MASS_RCV_PVT.VALIDATE_ORDER',
2779 'Sales rep missing for Line_id='
2780 || l_order_line_id
2781 );
2782 END IF;
2783
2784 CLOSE cur_ord_line;
2785
2786 RAISE fnd_api.g_exc_error;
2787 END IF;
2788
2789 IF cur_ord_line%ROWCOUNT > 1
2790 THEN
2791 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2792 THEN
2793 fnd_log.STRING (fnd_log.level_statement,
2794 'CSD.PLSQL.CSD_MASS_RCV_PVT.VALIDATE_ORDER',
2795 'Too many order ship_from_org_id'
2796 );
2797 END IF;
2798 END IF;
2799
2800 CLOSE cur_ord_line;
2801
2802 IF l_ship_from_org_id IS NULL
2803 THEN
2804 fnd_message.set_name ('CSD', 'CSD_API_SHIP_FROM_ORG_MISSING');
2805 fnd_message.set_token ('ORDER_LINE_ID', l_order_line_id);
2806 fnd_msg_pub.ADD;
2807
2808 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2809 THEN
2810 fnd_log.STRING (fnd_log.level_statement,
2811 'CSD.PLSQL.CSD_MASS_RCV_PVT.VALIDATE_ORDER',
2812 'Ship from Org Id missing for Line_id='
2813 || l_order_line_id
2814 );
2815 END IF;
2816
2817 RAISE fnd_api.g_exc_error;
2818 END IF;
2819
2820 IF l_unit_selling_price IS NULL
2821 THEN
2822 fnd_message.set_name ('CSD', 'CSD_API_PRICE_MISSING');
2823 fnd_message.set_token ('ORDER_LINE_ID', l_order_line_id);
2824 fnd_msg_pub.ADD;
2825
2826 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2827 THEN
2828 fnd_log.STRING (fnd_log.level_statement,
2829 'CSD.PLSQL.CSD_MASS_RCV_PVT.VALIDATE_ORDER',
2830 'Unit Selling price missing for Line_id='
2831 || l_order_line_id
2832 );
2833 END IF;
2834
2835 RAISE fnd_api.g_exc_error;
2836 END IF;
2837
2838 OPEN cur_ord_hdr (p_order_rec.order_header_id);
2839
2840 FETCH cur_ord_hdr
2841 INTO x_booked_flag;
2842
2843 IF (cur_ord_hdr%NOTFOUND)
2844 THEN
2845 fnd_message.set_name ('CSD', 'CSD_INV_ORDER_HEADER_ID');
2846 fnd_message.set_token ('ORDER_HEADER_ID',
2847 p_order_rec.order_header_id
2848 );
2849 fnd_msg_pub.ADD;
2850
2851 CLOSE cur_ord_hdr;
2852
2853 RAISE fnd_api.g_exc_error;
2854 END IF;
2855
2856 CLOSE cur_ord_hdr;
2857
2858 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2859 THEN
2860 fnd_log.STRING (fnd_log.level_procedure,
2861 'CSD.PLSQL.CSD_MASS_RCV_PVT.VALIDATE_ORDER.END',
2862 'Leaving Validate_order'
2863 );
2864 END IF;
2865 END validate_order;
2866
2867 /*************procedure to log the error stack..........
2868 ****************/
2869 PROCEDURE log_error_stack
2870 IS
2871 l_count NUMBER;
2872 l_msg VARCHAR2 (2000);
2873 l_index_out NUMBER;
2874 BEGIN
2875 l_count := fnd_msg_pub.count_msg ();
2876
2877 IF (l_count > 0)
2878 THEN
2879 FOR i IN 1 .. l_count
2880 LOOP
2881 fnd_msg_pub.get (p_msg_index => i,
2882 p_encoded => 'F',
2883 p_data => l_msg,
2884 p_msg_index_out => l_index_out
2885 );
2886
2887 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
2888 THEN
2889 fnd_log.STRING (fnd_log.level_error,
2890 'CSD.PLSQL.CSD_MASS_RCV_PVT.log_error_stack',
2891 'error[' || l_msg || ']'
2892 );
2893 END IF;
2894 END LOOP;
2895 END IF;
2896 END log_error_stack;
2897
2898
2899 procedure upd_instance(p_repair_type_ref IN VARCHAR2,
2900 p_serial_number IN VARCHAR2,
2901 p_instance_id IN NUMBER,
2902 x_prod_txn_tbl IN OUT NOCOPY csd_process_pvt.product_txn_tbl
2903 ) IS
2904 BEGIN
2905 IF p_repair_type_ref = 'R' THEN
2906 -- in 11.5.10 we have place holder for non source item attributes
2907 -- like non_source_serial_number non_source_instance_id etc
2908 -- Shipping customer product txn line
2909 x_prod_txn_tbl(1).non_source_serial_number := p_serial_number ; -- 11.5.10
2910 x_prod_txn_tbl(1).non_source_instance_id := p_instance_id; -- 11.5.10
2911 x_prod_txn_tbl(1).source_serial_number := FND_API.G_MISS_CHAR;
2912 x_prod_txn_tbl(1).source_instance_id := FND_API.G_MISS_NUM;
2913 ELSIF p_repair_type_ref in ('RR','WR','E' ) THEN
2914 x_prod_txn_tbl(1).source_serial_number := p_serial_number ;
2915 x_prod_txn_tbl(1).source_instance_id := p_instance_id ;
2916 x_prod_txn_tbl(1).non_source_serial_number := FND_API.G_MISS_CHAR;
2917 x_prod_txn_tbl(1).non_source_instance_id := FND_API.G_MISS_NUM;
2918 IF p_repair_type_ref = 'E' THEN
2919 x_prod_txn_tbl(2).non_source_instance_id := p_instance_id;
2920 x_prod_txn_tbl(2).non_source_serial_number := p_serial_number;
2921 x_prod_txn_tbl(2).source_instance_id := FND_API.G_MISS_NUM;
2922 x_prod_txn_tbl(2).source_serial_number := FND_API.G_MISS_CHAR;
2923 ELSE
2924 x_prod_txn_tbl(2).non_source_instance_id := FND_API.G_MISS_NUM;
2925 x_prod_txn_tbl(2).non_source_serial_number := FND_API.G_MISS_CHAR;
2926 x_prod_txn_tbl(2).source_instance_id := p_instance_id;
2927 x_prod_txn_tbl(2).source_serial_number := p_serial_number;
2928 END IF;
2929
2930
2931 ELSIF (p_repair_type_ref = 'AL') THEN
2932 x_prod_txn_tbl(1).source_serial_number := FND_API.G_MISS_CHAR;
2933 x_prod_txn_tbl(1).non_source_serial_number := FND_API.G_MISS_CHAR;
2934 x_prod_txn_tbl(1).source_instance_id := FND_API.G_MISS_NUM;
2935 x_prod_txn_tbl(1).non_source_instance_id := FND_API.G_MISS_NUM;
2936 x_prod_txn_tbl(2).source_serial_number := p_serial_number;
2937 x_prod_txn_tbl(2).non_source_serial_number := FND_API.G_MISS_CHAR;
2938 x_prod_txn_tbl(2).source_instance_id := p_instance_id ;
2939 x_prod_txn_tbl(2).non_source_instance_id := FND_API.G_MISS_NUM;
2940
2941 ELSIF ( p_repair_type_ref = 'AE' ) THEN
2942
2943 x_prod_txn_tbl(1).source_serial_number := FND_API.G_MISS_CHAR;
2944 x_prod_txn_tbl(1).non_source_serial_number := p_serial_number ;
2945 x_prod_txn_tbl(1).source_instance_id := FND_API.G_MISS_NUM;
2946 x_prod_txn_tbl(1).non_source_instance_id := p_instance_id ;
2947 x_prod_txn_tbl(2).source_serial_number := p_serial_number ;
2948 x_prod_txn_tbl(2).non_source_serial_number := FND_API.G_MISS_CHAR;
2949 x_prod_txn_tbl(2).source_instance_id := p_instance_id ;
2950 x_prod_txn_tbl(2).non_source_instance_id := FND_API.G_MISS_NUM;
2951 ELSIF p_repair_type_ref in ('ARR','WRL') THEN
2952
2953 x_prod_txn_tbl(1).source_serial_number := FND_API.G_MISS_CHAR;
2954 x_prod_txn_tbl(1).non_source_serial_number := FND_API.G_MISS_CHAR;
2955 x_prod_txn_tbl(1).source_instance_id := FND_API.G_MISS_NUM;
2956 x_prod_txn_tbl(1).non_source_instance_id := FND_API.G_MISS_NUM;
2957
2958 x_prod_txn_tbl(2).source_serial_number := p_serial_number;
2959 x_prod_txn_tbl(2).non_source_serial_number := FND_API.G_MISS_CHAR;
2960 x_prod_txn_tbl(2).source_instance_id := p_instance_id ;
2961 x_prod_txn_tbl(2).non_source_instance_id := FND_API.G_MISS_NUM;
2962 x_prod_txn_tbl(3).source_serial_number := p_serial_number ;
2963 x_prod_txn_tbl(3).non_source_serial_number := FND_API.G_MISS_CHAR;
2964 x_prod_txn_tbl(3).source_instance_id := p_instance_id ;
2965 x_prod_txn_tbl(3).non_source_instance_id := FND_API.G_MISS_NUM;
2966 x_prod_txn_tbl(4).source_serial_number := p_serial_number;
2967 x_prod_txn_tbl(4).non_source_serial_number := FND_API.G_MISS_CHAR;
2968 x_prod_txn_tbl(4).source_instance_id := p_instance_id ;
2969 x_prod_txn_tbl(4).non_source_instance_id := FND_API.G_MISS_NUM;
2970
2971 END IF;
2972
2973 END upd_instance;
2974
2975
2976 END csd_mass_rcv_pvt;