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