[Home] [Help]
PACKAGE BODY: APPS.CSD_BULK_RECEIVE_PVT
Source
1 PACKAGE BODY CSD_BULK_RECEIVE_PVT AS
2 /* $Header: csdvbrvb.pls 120.24.12020000.2 2013/04/03 07:00:22 subhat ship $ */
3
4 /*-----------------------------------------------------------------*/
5 /* procedure name: process_bulk_receive_items */
6 /* description : Concurrent program to Bulk Receive Items */
7 /* */
8 /*-----------------------------------------------------------------*/
9
10 PROCEDURE process_bulk_receive_items
11 (
12 errbuf OUT NOCOPY VARCHAR2,
13 retcode OUT NOCOPY VARCHAR2,
14 p_transaction_number IN NUMBER
15 )
16
17 IS
18
19 -- Cursor to validate IB Owner and the Bulk Receive Party
20 Cursor c_set_party(p_transaction_number in number) is
21 Select *
22 from csd_bulk_receive_items_b
23 where transaction_number = p_transaction_number
24 and incident_id is null
25 and repair_line_id is null
26 and internal_sr_flag = 'N'
27 and change_owner_flag = 'N'
28 and status = 'NEW';
29
30 -- Cursor to Change owner
31 Cursor c_change_owner(p_transaction_number in number) is
32 select *
33 from csd_bulk_receive_items_b
34 where transaction_number = p_transaction_number
35 and status in ('NEW','ERRORED')
36 and change_owner_flag = 'Y'
37 and party_id is null
38 and internal_sr_flag = 'N';
39
40 -- Cursor to Create Internal SR's
41 Cursor c_create_intr_sr(p_transaction_number in number) is
42 select *
43 from csd_bulk_receive_items_b
44 where transaction_number = p_transaction_number
45 and status in ('NEW','ERRORED')
46 and incident_id is null
47 and internal_sr_flag = 'Y';
48
49 -- Cursor to get internal party
50 Cursor c_get_intr_party is
51 select csi.internal_party_id,
52 hca.cust_account_id
53 from csi_install_parameters csi,
54 hz_cust_accounts hca
55 where csi.internal_party_id = hca.party_id(+)
56 and hca.status(+) = 'A';
57
58 -- Cursor to Create SR
59 Cursor c_create_sr (p_transaction_number in number) is
60 select distinct party_id,cust_account_id
61 from csd_bulk_receive_items_b
62 where transaction_number = p_transaction_number
63 and status in ('NEW','ERRORED')
64 and incident_id is null
65 and party_id is not null
66 and internal_sr_flag = 'N';
67
68 -- Cursor to Create New RO only
69 Cursor c_create_ro (p_transaction_number in number,p_incident_id in number) is
70 select *
71 from csd_bulk_receive_items_b
72 where transaction_number = p_transaction_number
73 and incident_id = p_incident_id
74 and repair_line_id is null
75 and internal_sr_flag = 'N';
76
77 -- Cursor to reprocess the errored RO's
78 Cursor c_reprocess_ro (p_transaction_number in number) is
79 select *
80 from csd_bulk_receive_items_b
81 where transaction_number = p_transaction_number
82 and status = 'ERRORED'
83 and incident_id is not null
84 and repair_line_id is null
85 and internal_sr_flag = 'N';
86
87 -- Cursor to Auto Receive
88 Cursor c_auto_receive (p_transaction in number) is
89 select *
90 from csd_bulk_receive_items_b
91 where transaction_number = p_transaction_number
92 and status in ('NEW','ERRORED')
93 and repair_line_id is not null
94 and internal_sr_flag = 'N';
95
96 -- Cursor to check the order status
97 Cursor c_check_prdtxn_status(p_repair_line_id in number) is
98 select dpt.prod_txn_status,
99 edt.order_line_id,
100 edt.order_header_id,
101 dpt.source_serial_number
102 from csd_product_transactions dpt,
103 cs_estimate_details edt
104 where dpt.repair_line_id = p_repair_line_id
105 and dpt.action_type = 'RMA'
106 and dpt.prod_txn_status = 'BOOKED'
107 and dpt.estimate_detail_id = edt.estimate_detail_id
108 and edt.source_code = 'DR';
109 -- commented out old query due to performance bug 4997501
110 -- select prod_txn_status,
111 -- order_line_id,
112 -- order_header_id,
113 -- source_serial_number
114 -- from csd_product_txns_v
115 -- where repair_line_id = p_repair_line_id
116 -- and action_type = 'RMA'
117 -- and prod_txn_status = 'BOOKED';
118
119 -- Cursor to get item attributes
120 Cursor c_get_item_attributes (p_inventory_item_id in number) is
121 Select comms_nl_trackable_flag,
122 concatenated_segments,
123 serial_number_control_code
124 from mtl_system_items_kfv
125 where inventory_item_id = p_inventory_item_id
126 and organization_id = cs_std.get_item_valdn_orgzn_id;
127
128 -- Cursor to get owner
129 Cursor c_get_ib_owner ( p_inventory_item_id in number,p_serial_number in varchar2) is
130 Select owner_party_id,
131 owner_party_account_id
132 from csi_item_instances
133 where inventory_item_id = p_inventory_item_id
134 and serial_number = p_serial_number;
135
136 -- Cursor to get Warning Reason Desc
137 Cursor c_get_warning_desc( p_warning_code in varchar2) is
138 Select description
139 from fnd_lookup_values_vl
140 where lookup_type = 'CSD_BULK_RECEIVE_WARNINGS'
141 and lookup_code = p_warning_code
142 and enabled_flag = 'Y'
143 and trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
144 and trunc(nvl(end_date_active,sysdate));
145
146 -- Used for standard concurrent program parameter 'retcode' value
147 c_success CONSTANT NUMBER := 0;
148 c_warning CONSTANT NUMBER := 1;
149 c_error CONSTANT NUMBER := 2;
150
151
152 -- Local variables
153 l_incident_id Number;
154 l_incident_number Varchar2(64);
155 l_repair_line_id Number;
156 l_repair_number Varchar2(30);
157 l_return_status Varchar2(1);
158 l_ro_error_count Number;
159 l_msg_count Number;
160 l_msg_data Varchar2(2000);
161 l_sr_bulk_receive_rec csd_bulk_receive_util.bulk_receive_rec;
162 l_ro_status Varchar2(30);
163 l_c_create_ro_rowcount Number;
164 l_order_status Varchar2(30);
165 l_intr_party_id Number;
166 i Number;
167 l_ib_owner_id Number;
168 l_ib_owner_acct_id Number;
169 l_ib_flag Varchar2(1);
170 l_intr_sr_notes_table cs_servicerequest_pub.notes_table;
171 l_sr_notes_table cs_servicerequest_pub.notes_table;
172 l_bulk_autorcv_tbl csd_bulk_receive_util.bulk_autorcv_tbl;
173 l_procedure_name Varchar2(30) := 'csd_bulk_receive_items_pvt';
174 l_create_intr_sr Boolean;
175 l_intr_cust_acct_id Number;
176 l_warning_desc Varchar2(240);
177 l_order_line_id Number;
178 l_order_header_id Number;
179 l_serial_label Varchar2(30);
180 l_item_label Varchar2(30);
181 l_qty_label Varchar2(30);
182 l_note_details Varchar2(2000);
183 l_item_name Varchar2(40);
184 l_source_serial_number Varchar2(30);
185 c_non_serialized CONSTANT Number := 1;
186 l_serial_number_control_code Number;
187 -- swai: 12.1.1 bug 7176940 service bulletin check
188 l_ro_sc_ids_tbl CSD_RO_BULLETINS_PVT.CSD_RO_SC_IDS_TBL_TYPE;
189
190 -- subhat: 12.1.2 BR ER FP changes
191 x_sr_ro_rma_tbl CSD_BULK_RECEIVE_UTIL.sr_ro_rma_tbl;
192 l_create_sr_flag VARCHAR2(3) := 'Y';
193 l_counter number := 0;
194 g_ret_sts_success varchar2(3) := FND_API.G_RET_STS_SUCCESS;
195 l_prod_txn_rec csd_process_pvt.product_txn_rec;
196 l_msg_index_out NUMBER;
197 l_req_data varchar2(30);
198 x number;
199 l_continue_further boolean := true;
200
201 BEGIN
202
203 -- check if the program run is the first one or not. If the program run is not first run
204 -- then exit immediately - Child Request submission changes, subhat.
205 -- before exiting, we also need to make sure that the child request is offcourse completed,
206 -- and then need to run the post receive steps.
207
208 l_req_data := fnd_conc_global.request_data;
209
210 if l_req_data is not null then
211 x := to_number(l_req_data);
212 if x > 0 then
213 errbuf := 'Done';
214 retcode := 0;
215 -- carry out post receipt processing.
216 csd_bulk_receive_util.after_receipt(p_request_group_id => x,
217 p_transaction_number => p_transaction_number);
218 -- lets write the output and then return.
219 csd_bulk_receive_util.write_to_conc_output
220 ( p_transaction_number => p_transaction_number);
221 return;
222 end if;
223 else
224 x := 1;
225 end if;
226
227 --
228 -- Logic Summary
229 -- All the following steps are executed for a
230 -- particular Transaction Number.
231 --
232 -- A.If Profile 'CSD_BLKRCV_CHG_IB_OWNER' is 'NO'
233 -- then set the Party id of the Bulk Receive Rec
234 -- to the IB Owner Party
235 -- B.Update IB Owner.
236 -- C.Create Internal SR for warning records.
237 -- D.Reprocess errored Repair Orders and create Logistic
238 -- lines.
239 -- E.Create Service Request,Repair Order, Logistic lines
240 -- for new records.
241 -- F.Auto Receive all the eligible records.
242 --
243
244 savepoint process_bulk_receive_items;
245
246 --
247 -- MOAC initialization
248 --
249 MO_GLOBAL.init('CS_CHARGES');
250
251 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
252 fnd_log.STRING (fnd_log.level_procedure,
253 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS.BEGIN',
254 'Entered Process Bulk Receive Items');
255 End if;
256
257 -- Verify the required parameter - Transaction Number
258 If ( p_transaction_number is null ) then
259
260 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
261 fnd_log.STRING (fnd_log.level_event,
262 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
263 'Validate Transaction Number');
264 End if;
265
266 Fnd_file.put_line(fnd_file.log,'Error: Transaction Number is null');
267 retcode := c_error;
268
269 End if;
270
271 --
272 -- Step - A
273 -- If Profile 'CSD_BLKRCV_CHG_IB_OWNER' is 'NO'
274 -- then set the Party id of the Bulk Receive Rec
275 -- to the IB Owner Party
276 If (fnd_profile.value('CSD_BLK_RCV_CHG_IB_OWNER') = 'N') then
277
278 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
279 fnd_log.STRING (fnd_log.level_event,
280 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
281 'Change IB Owner Profile is No, verify Instance party and Entered Party');
282 End if;
283
284 For c_set_party_rec in c_set_party ( p_transaction_number)
285 Loop
286
287 If ( c_set_party_rec.inventory_item_id is not null ) then
288
289 l_ib_flag := null;
290 l_item_name := null;
291 l_serial_number_control_code := null;
292
293 Open c_get_item_attributes(c_set_party_rec.inventory_item_id);
294 Fetch c_get_item_attributes into l_ib_flag,l_item_name,l_serial_number_control_code;
295 Close c_get_item_attributes;
296
297 -- If Install base item then verify the IB Owner
298 If ( l_ib_flag = 'Y' ) then
299
300 l_ib_owner_id := null;
301 l_ib_owner_acct_id := null;
302
303 Open c_get_ib_owner (c_set_party_rec.inventory_item_id,
304 c_set_party_rec.serial_number);
305 Fetch c_get_ib_owner into l_ib_owner_id,l_ib_owner_acct_id;
306 Close c_get_ib_owner;
307
308 -- If the IB Owner is <> Entered Party then update the
309 -- Bulk Receive Party = IB Owner
310 If ( l_ib_owner_id <> c_set_party_rec.party_id) then
311
312 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
313 fnd_log.STRING (fnd_log.level_statement,
314 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
315 'Update: bulk_receive_id[' || c_set_party_rec.bulk_receive_id ||
316 '] with IB owner party id - '||l_ib_owner_id);
317 End if;
318
319 Update csd_bulk_receive_items_b
320 set party_id = l_ib_owner_id,
321 cust_account_id = l_ib_owner_acct_id
322 where bulk_receive_id = c_set_party_rec.bulk_receive_id;
323 End if;
324
325 End if;
326
327 End if; -- End if of the inventory item id null check
328
329 End Loop;
330
331 End if;
332
333 --
334 -- Step - B
335 -- Change IB owner for records which have IB owner different
336 -- from the entered Party/Account
337 --
338 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
339 fnd_log.STRING (fnd_log.level_event,
340 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
341 'Change IB Owner');
342 End if;
343
344 For c_change_owner_rec in c_change_owner (p_transaction_number)
345 Loop
346
347 Savepoint change_ib_owner;
348
349 csd_bulk_receive_util.change_blkrcv_ib_owner
350 (
351 p_bulk_receive_id => c_change_owner_rec.bulk_receive_id,
352 x_return_status => l_return_status,
353 x_msg_count => l_msg_count,
354 x_msg_data => l_msg_data
355 );
356
357 If (l_return_status = FND_API.G_RET_STS_SUCCESS) then
358
359 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
360 fnd_log.STRING (fnd_log.level_statement,
361 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
362 'Change IB owner,Update : bulk_receive_id['
363 || c_change_owner_rec.bulk_receive_id ||
364 '] party id with orig party id');
365 End if;
366
367 Update csd_bulk_receive_items_b
368 set party_id = orig_party_id
369 ,cust_account_id = orig_cust_account_id
370 ,status = 'NEW'
371 where bulk_receive_id = c_change_owner_rec.bulk_receive_id;
372
373 Else
374
375 Rollback to change_ib_owner;
376
377 -- Write to conc log
378 Fnd_file.put_line(fnd_file.log,'Error: IB Change Owner failed');
379 Fnd_file.put(fnd_file.log,'Serial Number :'||c_change_owner_rec.serial_number||',');
380 Fnd_file.put(fnd_file.log,'Inventory Item id :'||c_change_owner_rec.inventory_item_id||',');
381 Fnd_file.put(fnd_file.log,'Qty :'||c_change_owner_rec.quantity||',');
382 Fnd_file.put_line(fnd_file.log,'New Party Id :'||c_change_owner_rec.orig_party_id);
383
384 csd_bulk_receive_util.write_to_conc_log
385 ( p_msg_count => l_msg_count,
386 p_msg_data => l_msg_data);
387
388 End If;
389
390 End Loop; -- End of c_change_owner_rec loop
391
392
393 --
394 -- Step - C
395 -- Create Internal SR for Warning / Invalid records.
396 -- Note is created for every Warning and is associated with
397 -- the Internal SR.
398 --
399 i := 0;
400 l_create_intr_sr := FALSE;
401
402 fnd_message.set_name('CSD','CSD_BULK_RCV_SERIAL_CONC_LABEL');
403 l_serial_label := fnd_message.get;
404
405 fnd_message.set_name('CSD','CSD_BULK_RCV_ITEM_CONC_LABEL');
406 l_item_label := fnd_message.get;
407
408 fnd_message.set_name('CSD','CSD_BULK_RCV_QTY_CONC_LABEL');
409 l_qty_label := fnd_message.get;
410
411 For c_create_intr_sr_rec in c_create_intr_sr (p_transaction_number)
412 Loop
413
414 i:= i + 1;
415 l_create_intr_sr := TRUE;
416 l_warning_desc := null;
417 l_ib_flag := null;
418 l_item_name := null;
419 l_serial_number_control_code := null;
420
421 Open c_get_warning_desc (c_create_intr_sr_rec.warning_reason_code);
422 Fetch c_get_warning_desc into l_warning_desc;
423 Close c_get_warning_desc;
424
425 Open c_get_item_attributes(c_create_intr_sr_rec.inventory_item_id);
426 Fetch c_get_item_attributes into l_ib_flag,l_item_name,l_serial_number_control_code;
427 Close c_get_item_attributes;
428
429 l_note_details := ' - '||l_serial_label||' : '||c_create_intr_sr_rec.serial_number||','||
430 l_item_label||' : '||l_item_name||','||
431 l_qty_label||' : '||c_create_intr_sr_rec.quantity;
432
433 l_intr_sr_notes_table(i).note := l_warning_desc;
434 l_intr_sr_notes_table(i).note_detail := l_note_details;
435 l_intr_sr_notes_table(i).note_type := 'CS_PROBLEM';
436 l_intr_sr_notes_table(i).note_context_type_01 := 'CS';
437
438 End Loop;
439
440
441 If ( l_create_intr_sr ) then
442 -- Call the Create Service Request API
443 Savepoint create_intr_sr_savepoint;
444
445 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
446 fnd_log.STRING (fnd_log.level_event,
447 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
448 'Create Internal SR');
449 End if;
450
451 l_intr_party_id := null;
452 l_intr_cust_acct_id := null;
453
454 Open c_get_intr_party;
455 Fetch c_get_intr_party into l_intr_party_id,l_intr_cust_acct_id;
456 Close c_get_intr_party;
457
458 l_sr_bulk_receive_rec.party_id := l_intr_party_id;
459 l_sr_bulk_receive_rec.cust_account_id := l_intr_cust_acct_id;
460
461 csd_bulk_receive_util.create_blkrcv_sr
462 (
463 p_bulk_receive_rec => l_sr_bulk_receive_rec,
464 p_sr_notes_tbl => l_intr_sr_notes_table,
465 x_incident_id => l_incident_id,
466 x_incident_number => l_incident_number,
467 x_return_status => l_return_status,
468 x_msg_count => l_msg_count,
469 x_msg_data => l_msg_data
470 );
471
472 If (l_return_status = FND_API.G_RET_STS_SUCCESS) then
473
474 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
475 fnd_log.STRING (fnd_log.level_statement,
476 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
477 'Created Internal SR : Incident id = '
478 ||l_incident_id );
479 End if;
480
481 Update csd_bulk_receive_items_b
482 set incident_id = l_incident_id,
483 status = 'PROCESSED',
484 party_id = l_intr_party_id,
485 cust_account_id = l_intr_cust_acct_id
486 where transaction_number = p_transaction_number
487 and incident_id is null
488 and internal_sr_flag = 'Y';
489
490 Else
491
492 Rollback To create_intr_sr_savepoint;
493
494 Update csd_bulk_receive_items_b
495 set status = 'ERRORED'
496 where transaction_number = p_transaction_number
497 and incident_id is null
498 and internal_sr_flag = 'Y';
499
500 -- Write to Conc Log
501 Fnd_file.put_line(fnd_file.log,'Error: Creation of Internal Service Request failed');
502 Fnd_file.put_line(fnd_file.log,'Internal party id :'||l_intr_party_id);
503
504 csd_bulk_receive_util.write_to_conc_log
505 ( p_msg_count => l_msg_count,
506 p_msg_data => l_msg_data);
507
508 End if;
509 End if;
510
511
512 --
513 -- Step - D
514 -- To Reprocess Errored RO's
515 --
516 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
517 fnd_log.STRING (fnd_log.level_event,
518 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
519 'Check and reprocess Errored Repair Orders');
520 End if;
521
522 For c_reprocess_ro_rec in c_reprocess_ro(p_transaction_number)
523 Loop
524
525 Savepoint reprocess_ro_savepoint;
526
527 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
528 fnd_log.STRING (fnd_log.level_event,
529 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
530 'Reprocess RO - Call Create Repair Orders');
531 End if;
532
533 -- Call Create RO Helper procedure
534 csd_bulk_receive_util.create_blkrcv_ro
535 (
536 p_bulk_receive_id => c_reprocess_ro_rec.bulk_receive_id,
537 x_repair_line_id => l_repair_line_id,
538 x_repair_number => l_repair_number,
539 x_return_status => l_return_status,
540 x_ro_status => l_ro_status,
541 x_msg_count => l_msg_count,
542 x_msg_data => l_msg_data
543 );
544
545 If (l_return_status = FND_API.G_RET_STS_SUCCESS) then
546
547 -- If RO is created in Draft status then
548 -- no Logistic lines are created
549
550 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
551 fnd_log.STRING (fnd_log.level_statement,
552 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
553 'Reprocess RO - Created Repair Order ['||l_repair_line_id||'] in '
554 ||l_ro_status||' status' );
555 End if;
556
557 If ( l_ro_status = 'DRAFT' ) then
558
559 Update csd_bulk_receive_items_b
560 set repair_line_id = l_repair_line_id,
561 status = 'PROCESSED'
562 where bulk_receive_id = c_reprocess_ro_rec.bulk_receive_id;
563
564 Else
565
566 -- Update the Bulk Receive Record
567 Update csd_bulk_receive_items_b
568 set repair_line_id = l_repair_line_id,
569 status = 'NEW'
570 where bulk_receive_id = c_reprocess_ro_rec.bulk_receive_id;
571
572 -- Call the create default product transaction
573
574 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
575 fnd_log.STRING (fnd_log.level_event,
576 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
577 'Reprocess RO - Call Create Product Transactions');
578 End if;
579
580 csd_bulk_receive_util.create_blkrcv_default_prod_txn
581 (
582 p_bulk_receive_id => c_reprocess_ro_rec.bulk_receive_id,
583 x_return_status => l_return_status,
584 x_msg_count => l_msg_count,
585 x_msg_data => l_msg_data
586 );
587
588 If NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) then
589
590 -- If Logistic line creation fails then rollback RO
591 Rollback To reprocess_ro_savepoint;
592
593 Update csd_bulk_receive_items_b
594 set status = 'ERRORED'
595 where bulk_receive_id = c_reprocess_ro_rec.bulk_receive_id;
596
597 -- Write to conc log
598 Fnd_file.put_line(fnd_file.log,'Error : Creation of Default Logistic lines failed');
599 Fnd_file.put(fnd_file.log,'Serial Number :'||c_reprocess_ro_rec.serial_number||',');
600 Fnd_file.put(fnd_file.log,'Inventory Item Id :'||c_reprocess_ro_rec.inventory_item_id||',');
601 Fnd_file.put_line(fnd_file.log,'Qty :'||c_reprocess_ro_rec.quantity);
602
603 csd_bulk_receive_util.write_to_conc_log
604 ( p_msg_count => l_msg_count,
605 p_msg_data => l_msg_data);
606
607 End if;
608
609 -- swai: 12.1.1 bug 7176940 - check service bulletins after RO creation
610 IF (nvl(fnd_profile.value('CSD_AUTO_CHECK_BULLETINS'),'N') = 'Y') THEN
611 CSD_RO_BULLETINS_PVT.LINK_BULLETINS_TO_RO(
612 p_api_version_number => 1.0,
613 p_init_msg_list => Fnd_Api.G_FALSE,
614 p_commit => Fnd_Api.G_FALSE,
615 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
616 p_repair_line_id => l_repair_line_id,
617 px_ro_sc_ids_tbl => l_ro_sc_ids_tbl,
618 x_return_status => l_return_status,
619 x_msg_count => l_msg_count,
620 x_msg_data => l_msg_data
621 );
622 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
623 fnd_log.STRING (fnd_log.level_statement,
624 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
625 'Reprocess RO - After CSD_RO_BULLETINS_PVT.LINK_BULLETINS_TO_RO['
626 || l_repair_line_id || ']');
627 END IF;
628 -- ignore return status for now.
629 END IF;
630
631 End if; -- End if of status = 'DRAFT' if condition
632
633 Else
634
635 Rollback To reprocess_ro_savepoint;
636
637 Update csd_bulk_receive_items_b
638 set status = 'ERRORED'
639 where bulk_receive_id = c_reprocess_ro_rec.bulk_receive_id;
640
641 -- Write to conc log
642 Fnd_file.put_line(fnd_file.log,'Error : Creation of Repair Order failed');
643 Fnd_file.put(fnd_file.log,'Serial Number :'||c_reprocess_ro_rec.serial_number||',');
644 Fnd_file.put(fnd_file.log,'Inventory Item Id :'||c_reprocess_ro_rec.inventory_item_id||',');
645 Fnd_file.put_line(fnd_file.log,'Qty :'||c_reprocess_ro_rec.quantity);
646
647 csd_bulk_receive_util.write_to_conc_log
648 ( p_msg_count => l_msg_count,
649 p_msg_data => l_msg_data);
650
651 End if;
652
653 End Loop; -- End of c_reprocess_ro_rec loop
654
655 -- 12.2 subhat
656 if NVL(FND_PROFILE.value('CSD_EXP_RCPT_FOR_BLKRCV'),'N') = 'Y' THEN
657 --
658 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
659 fnd_log.STRING (fnd_log.level_procedure,
660 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
661 'Entered program unit to receive the matched lines');
662 End if;
663 l_create_sr_flag := 'N';
664 -- loop through the records and populate the record for the receiving.
665
666 for bk_rcv_rec in (select * from csd_bulk_receive_items_b
667 where transaction_number = p_transaction_number)
668 loop
669 -- find out if the line is ready to be received.
670 l_continue_further := true;
671 l_counter := l_counter + 1;
672 if bk_rcv_rec.order_header_id is not null and bk_rcv_rec.order_line_id is not null then
673 -- external ref# handling.
674 if bk_rcv_rec.instance_id is not null and bk_rcv_rec.external_reference is not null
675 then
676 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
677 fnd_log.STRING (fnd_log.level_procedure,
678 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
679 'Calling csd_bulk_receive_util.update_external_reference');
680 End if;
681 csd_repair_manager_util.update_external_reference(p_external_reference => bk_rcv_rec.external_reference,
682 p_instance_id => bk_rcv_rec.instance_id,
683 x_return_status => l_return_status,
684 x_msg_count => l_msg_count,
685 x_msg_data => l_msg_data);
686 elsif bk_rcv_rec.instance_id is null and bk_rcv_rec.external_reference is not null
687 then
688 -- try to get the instance from the repair order.
689 begin
690 select instance_id
691 into bk_rcv_rec.instance_id
692 from csd_repairs
693 where repair_line_id = bk_rcv_rec.repair_line_id;
694 exception
695 when no_data_found then
696 null;
697 end;
698
699 if bk_rcv_rec.instance_id is null then
700 declare
701 l_instance_rec csd_mass_rcv_pvt.instance_rec_type;
702 l_instance_id number;
703 begin
704 Select ship_to_site_use_id
705 into l_instance_rec.party_site_use_id
706 from cs_incidents_all_b
707 where incident_id = bk_rcv_rec.incident_id;
708
709 l_instance_rec.inventory_item_id := bk_rcv_rec.inventory_item_id;
710 l_instance_rec.instance_id := null;
711 l_instance_rec.instance_number := null;
712 l_instance_rec.serial_number := bk_rcv_rec.serial_number;
713 l_instance_rec.lot_number := null;
714 l_instance_rec.quantity := 1;
715 l_instance_rec.uom := bk_rcv_rec.uom_code;
716 l_instance_rec.party_id := bk_rcv_rec.party_id;
717 l_instance_rec.account_id := bk_rcv_rec.cust_account_id;
718 l_instance_rec.mfg_serial_number_flag := 'N';
719 l_instance_rec.external_reference := bk_rcv_rec.external_reference;
720
721 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
722 fnd_log.STRING (fnd_log.level_procedure,
723 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
724 'Calling csd_mass_rcv_pvt.create_item_instance');
725 End if;
726 -- call the API to create the instance
727 csd_mass_rcv_pvt.create_item_instance (
728 p_api_version => 1.0,
729 p_init_msg_list => fnd_api.g_false,
730 p_commit => fnd_api.g_false,
731 p_validation_level => fnd_api.g_valid_level_full,
732 x_return_status => l_return_status,
733 x_msg_count => l_msg_count,
734 x_msg_data => l_msg_data,
735 px_instance_rec => l_instance_rec,
736 x_instance_id => l_instance_id );
737 if not (l_return_status = fnd_api.g_ret_sts_success) then
738 raise fnd_api.g_exc_error;
739 end if;
740 exception
741 when FND_API.G_EXC_ERROR then
742 null;
743 end;
744 else
745 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
746 fnd_log.STRING (fnd_log.level_procedure,
747 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
748 'Calling csd_mass_rcv_pvt.update_external_reference');
749 End if;
750 csd_repair_manager_util.update_external_reference(p_external_reference => bk_rcv_rec.external_reference,
751 p_instance_id => bk_rcv_rec.instance_id,
752 x_return_status => l_return_status,
753 x_msg_count => l_msg_count,
754 x_msg_data => l_msg_data);
755 end if;
756
757 end if;
758 -- we need to make sure that the RMA is booked.
759 -- RMA can be in submitted status too.
760 csd_bulk_receive_util.pre_process_rma
761 ( p_repair_line_id => bk_rcv_rec.repair_line_id,
762 px_order_header_id => bk_rcv_rec.order_header_id,
763 px_order_line_id => bk_rcv_rec.order_line_id,
764 x_msg_count => l_msg_count,
765 x_msg_data => l_msg_data,
766 x_return_status => l_return_status
767 );
768 if l_return_status <> fnd_api.g_ret_sts_success then
769 -- the order is not booked and an attempt to book it has failed.
770 -- log it to concurrent log.
771 csd_bulk_receive_util.write_to_conc_log(l_msg_count,l_msg_data);
772 -- update the bulk receive record as errored.
773 update csd_bulk_receive_items_b
774 set status = 'ERRORED'
775 where bulk_receive_id = bk_rcv_rec.bulk_receive_id;
776
777 -- proceed for the next record.
778 -- bug#8805130, continue is 11G keyword. Use if construct to achieve the same
779 -- functionality.
780 --continue;
781 l_continue_further := false;
782 end if;
783 if l_continue_further then
784 if nvl(bk_rcv_rec.under_receipt_flag,'N') = 'Y' then
785 -- split the existing line.
786 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
787 fnd_log.STRING(fnd_log.level_procedure,
788 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
789 'Under-receipt: Under receipt for repair_line_id '||bk_rcv_rec.repair_line_id);
790 End if;
791 l_bulk_autorcv_tbl(l_counter).bulk_receive_id := bk_rcv_rec.bulk_receive_id;
792 l_bulk_autorcv_tbl(l_counter).repair_line_id := bk_rcv_rec.repair_line_id;
793 l_bulk_autorcv_tbl(l_counter).order_header_id := bk_rcv_rec.order_header_id;
794 l_bulk_autorcv_tbl(l_counter).order_line_id := bk_rcv_rec.order_line_id;
795 l_bulk_autorcv_tbl(l_counter).under_receipt_flag := 'Y';
796 l_bulk_autorcv_tbl(l_counter).receipt_qty := bk_rcv_rec.quantity;
797 l_bulk_autorcv_tbl(l_counter).locator_id := bk_rcv_rec.locator_id;
798 l_bulk_autorcv_tbl(l_counter).subinventory := bk_rcv_rec.subinventory;
799 l_bulk_autorcv_tbl(l_counter).item_revision := bk_rcv_rec.item_revision;
800 l_bulk_autorcv_tbl(l_counter).lot_number := bk_rcv_rec.lot_number;
801 l_bulk_autorcv_tbl(l_counter).serial_number := bk_rcv_rec.serial_number;
802
803 l_bulk_autorcv_tbl(l_counter).rcv_attribute_category := bk_rcv_rec.rcv_attribute_category;
804 l_bulk_autorcv_tbl(l_counter).rcv_attribute1 := bk_rcv_rec.rcv_attribute1;
805 l_bulk_autorcv_tbl(l_counter).rcv_attribute2 := bk_rcv_rec.rcv_attribute2;
806 l_bulk_autorcv_tbl(l_counter).rcv_attribute3 := bk_rcv_rec.rcv_attribute3;
807 l_bulk_autorcv_tbl(l_counter).rcv_attribute4 := bk_rcv_rec.rcv_attribute4;
808 l_bulk_autorcv_tbl(l_counter).rcv_attribute5 := bk_rcv_rec.rcv_attribute5;
809 l_bulk_autorcv_tbl(l_counter).rcv_attribute6 := bk_rcv_rec.rcv_attribute6;
810 l_bulk_autorcv_tbl(l_counter).rcv_attribute7 := bk_rcv_rec.rcv_attribute7;
811 l_bulk_autorcv_tbl(l_counter).rcv_attribute8 := bk_rcv_rec.rcv_attribute8;
812 l_bulk_autorcv_tbl(l_counter).rcv_attribute9 := bk_rcv_rec.rcv_attribute9;
813 l_bulk_autorcv_tbl(l_counter).rcv_attribute10 := bk_rcv_rec.rcv_attribute10;
814 l_bulk_autorcv_tbl(l_counter).rcv_attribute11 := bk_rcv_rec.rcv_attribute11;
815 l_bulk_autorcv_tbl(l_counter).rcv_attribute12 := bk_rcv_rec.rcv_attribute12;
816 l_bulk_autorcv_tbl(l_counter).rcv_attribute13 := bk_rcv_rec.rcv_attribute13;
817 l_bulk_autorcv_tbl(l_counter).rcv_attribute14 := bk_rcv_rec.rcv_attribute14;
818 l_bulk_autorcv_tbl(l_counter).rcv_attribute15 := bk_rcv_rec.rcv_attribute15;
819
820 elsif nvl(bk_rcv_rec.over_receipt_flag,'N') = 'Y' then
821 -- create a new RMA for the over receipt quantity
822 -- call the util procedure to create the new RMA line.
823 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
824 fnd_log.STRING (fnd_log.level_procedure,
825 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
826 'Over-receipt: Before calling csd_bulk_receive_util.create_new_rma');
827 End if;
828 csd_bulk_receive_util.create_new_rma(
829 p_api_version => 1,
830 p_init_msg_list => FND_API.G_FALSE,
831 p_commit => FND_API.G_FALSE,
832 p_order_header_id => bk_rcv_rec.order_header_id,
833 p_new_rma_qty => bk_rcv_rec.over_receipt_qty,
834 p_repair_line_id => bk_rcv_rec.repair_line_id,
835 p_incident_id => bk_rcv_rec.incident_id,
836 p_rma_quantity => (bk_rcv_rec.quantity - bk_rcv_rec.over_receipt_qty),
837 x_return_status => l_return_status,
838 x_msg_count => l_msg_count,
839 x_msg_data => l_msg_data,
840 x_order_line_id => l_order_line_id,
841 x_order_header_id => l_order_header_id
842 );
843 if l_return_status <> g_ret_sts_success then
844 -- error during rma creation.
845 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
846 fnd_log.STRING (fnd_log.level_procedure,
847 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
848 'Error during new RMA creation '||l_msg_data);
849 End if;
850 csd_bulk_receive_util.write_to_conc_log(l_msg_count,l_msg_data);
851
852 update csd_bulk_receive_items_b
853 set status = 'ERRORED'
854 where bulk_receive_id = bk_rcv_rec.bulk_receive_id;
855
856 else
857 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
858 fnd_log.STRING (fnd_log.level_procedure,
859 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
860 'Populate the bulk receive table '||bk_rcv_rec.order_header_id||'['||bk_rcv_rec.order_line_id||']');
861 End if;
862 -- auto create the ship lines for the over-receipt quantity.
863 csd_bulk_receive_util.create_new_ship_line(
864 p_api_version => 1,
865 p_init_msg_list => FND_API.G_FALSE,
866 p_commit => FND_API.G_FALSE,
867 p_order_header_id => bk_rcv_rec.order_header_id,
868 p_new_ship_qty => bk_rcv_rec.over_receipt_qty,
869 p_repair_line_id => bk_rcv_rec.repair_line_id,
870 p_incident_id => bk_rcv_rec.incident_id,
871 x_return_status => l_return_status,
872 x_msg_count => l_msg_count,
873 x_msg_data => l_msg_data);
874 -- if there is any error during creation new ship line, we wont stall the rest
875 -- of the processing.csd_bulk_receive_util.create_new_ship_line does rollback
876 -- to savepoint when it hits an error.
877
878 l_bulk_autorcv_tbl(l_counter).bulk_receive_id := bk_rcv_rec.bulk_receive_id;
879 l_bulk_autorcv_tbl(l_counter).repair_line_id := bk_rcv_rec.repair_line_id;
880 l_bulk_autorcv_tbl(l_counter).order_header_id := bk_rcv_rec.order_header_id;
881 l_bulk_autorcv_tbl(l_counter).order_line_id := bk_rcv_rec.order_line_id;
882 l_bulk_autorcv_tbl(l_counter).locator_id := bk_rcv_rec.locator_id;
883 l_bulk_autorcv_tbl(l_counter).subinventory := bk_rcv_rec.subinventory;
884 l_bulk_autorcv_tbl(l_counter).item_revision := bk_rcv_rec.item_revision;
885 l_bulk_autorcv_tbl(l_counter).lot_number := bk_rcv_rec.lot_number;
886 l_bulk_autorcv_tbl(l_counter).serial_number := bk_rcv_rec.serial_number;
887
888 l_bulk_autorcv_tbl(l_counter).rcv_attribute_category := bk_rcv_rec.rcv_attribute_category;
889 l_bulk_autorcv_tbl(l_counter).rcv_attribute1 := bk_rcv_rec.rcv_attribute1;
890 l_bulk_autorcv_tbl(l_counter).rcv_attribute2 := bk_rcv_rec.rcv_attribute2;
891 l_bulk_autorcv_tbl(l_counter).rcv_attribute3 := bk_rcv_rec.rcv_attribute3;
892 l_bulk_autorcv_tbl(l_counter).rcv_attribute4 := bk_rcv_rec.rcv_attribute4;
893 l_bulk_autorcv_tbl(l_counter).rcv_attribute5 := bk_rcv_rec.rcv_attribute5;
894 l_bulk_autorcv_tbl(l_counter).rcv_attribute6 := bk_rcv_rec.rcv_attribute6;
895 l_bulk_autorcv_tbl(l_counter).rcv_attribute7 := bk_rcv_rec.rcv_attribute7;
896 l_bulk_autorcv_tbl(l_counter).rcv_attribute8 := bk_rcv_rec.rcv_attribute8;
897 l_bulk_autorcv_tbl(l_counter).rcv_attribute9 := bk_rcv_rec.rcv_attribute9;
898 l_bulk_autorcv_tbl(l_counter).rcv_attribute10 := bk_rcv_rec.rcv_attribute10;
899 l_bulk_autorcv_tbl(l_counter).rcv_attribute11 := bk_rcv_rec.rcv_attribute11;
900 l_bulk_autorcv_tbl(l_counter).rcv_attribute12 := bk_rcv_rec.rcv_attribute12;
901 l_bulk_autorcv_tbl(l_counter).rcv_attribute13 := bk_rcv_rec.rcv_attribute13;
902 l_bulk_autorcv_tbl(l_counter).rcv_attribute14 := bk_rcv_rec.rcv_attribute14;
903 l_bulk_autorcv_tbl(l_counter).rcv_attribute15 := bk_rcv_rec.rcv_attribute15;
904
905 l_counter := l_counter + 1;
906 l_bulk_autorcv_tbl(l_counter).bulk_receive_id := bk_rcv_rec.bulk_receive_id;
907 l_bulk_autorcv_tbl(l_counter).repair_line_id := bk_rcv_rec.repair_line_id;
908 l_bulk_autorcv_tbl(l_counter).order_header_id := l_order_header_id;--bk_rcv_rec.order_header_id;
909 l_bulk_autorcv_tbl(l_counter).order_line_id := l_order_line_id; --bk_rcv_rec.order_line_id;
910 l_bulk_autorcv_tbl(l_counter).locator_id := bk_rcv_rec.locator_id;
911 l_bulk_autorcv_tbl(l_counter).subinventory := bk_rcv_rec.subinventory;
912 l_bulk_autorcv_tbl(l_counter).item_revision := bk_rcv_rec.item_revision;
913 l_bulk_autorcv_tbl(l_counter).lot_number := bk_rcv_rec.lot_number;
914 l_bulk_autorcv_tbl(l_counter).serial_number := bk_rcv_rec.serial_number;
915
916 l_bulk_autorcv_tbl(l_counter).rcv_attribute_category := bk_rcv_rec.rcv_attribute_category;
917 l_bulk_autorcv_tbl(l_counter).rcv_attribute1 := bk_rcv_rec.rcv_attribute1;
918 l_bulk_autorcv_tbl(l_counter).rcv_attribute2 := bk_rcv_rec.rcv_attribute2;
919 l_bulk_autorcv_tbl(l_counter).rcv_attribute3 := bk_rcv_rec.rcv_attribute3;
920 l_bulk_autorcv_tbl(l_counter).rcv_attribute4 := bk_rcv_rec.rcv_attribute4;
921 l_bulk_autorcv_tbl(l_counter).rcv_attribute5 := bk_rcv_rec.rcv_attribute5;
922 l_bulk_autorcv_tbl(l_counter).rcv_attribute6 := bk_rcv_rec.rcv_attribute6;
923 l_bulk_autorcv_tbl(l_counter).rcv_attribute7 := bk_rcv_rec.rcv_attribute7;
924 l_bulk_autorcv_tbl(l_counter).rcv_attribute8 := bk_rcv_rec.rcv_attribute8;
925 l_bulk_autorcv_tbl(l_counter).rcv_attribute9 := bk_rcv_rec.rcv_attribute9;
926 l_bulk_autorcv_tbl(l_counter).rcv_attribute10 := bk_rcv_rec.rcv_attribute10;
927 l_bulk_autorcv_tbl(l_counter).rcv_attribute11 := bk_rcv_rec.rcv_attribute11;
928 l_bulk_autorcv_tbl(l_counter).rcv_attribute12 := bk_rcv_rec.rcv_attribute12;
929 l_bulk_autorcv_tbl(l_counter).rcv_attribute13 := bk_rcv_rec.rcv_attribute13;
930 l_bulk_autorcv_tbl(l_counter).rcv_attribute14 := bk_rcv_rec.rcv_attribute14;
931 l_bulk_autorcv_tbl(l_counter).rcv_attribute15 := bk_rcv_rec.rcv_attribute15;
932 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
933 fnd_log.STRING (fnd_log.level_procedure,
934 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
935 'Populated bulk receive rec for over receipt '||l_order_header_id||'['||l_order_line_id||']');
936 End if;
937 end if;
938
939 else -- regular expected receipt
940 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
941 fnd_log.STRING (fnd_log.level_procedure,
942 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
943 'Populating the bulk receive table for receipt against existing RMAs');
944 End if;
945
946 l_bulk_autorcv_tbl(l_counter).bulk_receive_id := bk_rcv_rec.bulk_receive_id;
947 l_bulk_autorcv_tbl(l_counter).repair_line_id := bk_rcv_rec.repair_line_id;
948 l_bulk_autorcv_tbl(l_counter).order_header_id := bk_rcv_rec.order_header_id;
949 l_bulk_autorcv_tbl(l_counter).order_line_id := bk_rcv_rec.order_line_id;
950 l_bulk_autorcv_tbl(l_counter).locator_id := bk_rcv_rec.locator_id;
951 l_bulk_autorcv_tbl(l_counter).subinventory := bk_rcv_rec.subinventory;
952 l_bulk_autorcv_tbl(l_counter).item_revision := bk_rcv_rec.item_revision;
953 l_bulk_autorcv_tbl(l_counter).lot_number := bk_rcv_rec.lot_number;
954 l_bulk_autorcv_tbl(l_counter).serial_number := bk_rcv_rec.serial_number;
955 l_bulk_autorcv_tbl(l_counter).rcv_attribute_category := bk_rcv_rec.rcv_attribute_category;
956 l_bulk_autorcv_tbl(l_counter).rcv_attribute1 := bk_rcv_rec.rcv_attribute1;
957 l_bulk_autorcv_tbl(l_counter).rcv_attribute2 := bk_rcv_rec.rcv_attribute2;
958 l_bulk_autorcv_tbl(l_counter).rcv_attribute3 := bk_rcv_rec.rcv_attribute3;
959 l_bulk_autorcv_tbl(l_counter).rcv_attribute4 := bk_rcv_rec.rcv_attribute4;
960 l_bulk_autorcv_tbl(l_counter).rcv_attribute5 := bk_rcv_rec.rcv_attribute5;
961 l_bulk_autorcv_tbl(l_counter).rcv_attribute6 := bk_rcv_rec.rcv_attribute6;
962 l_bulk_autorcv_tbl(l_counter).rcv_attribute7 := bk_rcv_rec.rcv_attribute7;
963 l_bulk_autorcv_tbl(l_counter).rcv_attribute8 := bk_rcv_rec.rcv_attribute8;
964 l_bulk_autorcv_tbl(l_counter).rcv_attribute9 := bk_rcv_rec.rcv_attribute9;
965 l_bulk_autorcv_tbl(l_counter).rcv_attribute10 := bk_rcv_rec.rcv_attribute10;
966 l_bulk_autorcv_tbl(l_counter).rcv_attribute11 := bk_rcv_rec.rcv_attribute11;
967 l_bulk_autorcv_tbl(l_counter).rcv_attribute12 := bk_rcv_rec.rcv_attribute12;
968 l_bulk_autorcv_tbl(l_counter).rcv_attribute13 := bk_rcv_rec.rcv_attribute13;
969 l_bulk_autorcv_tbl(l_counter).rcv_attribute14 := bk_rcv_rec.rcv_attribute14;
970 l_bulk_autorcv_tbl(l_counter).rcv_attribute15 := bk_rcv_rec.rcv_attribute15;
971 end if;
972
973 end if; -- end of l_continue_further check.
974 -- process unplanned receipts
975
976 -- Unplanned Receipt = 'N' but no order header or line id.
977 -- The RMA line is not yet interfaced to OM. Book the order and process it.
978 elsif bk_rcv_rec.order_line_id is null and bk_rcv_rec.unplanned_receipt_flag = 'N'
979 then
980 csd_bulk_receive_util.pre_process_rma
981 ( p_repair_line_id => bk_rcv_rec.repair_line_id,
982 px_order_header_id => bk_rcv_rec.order_header_id,
983 px_order_line_id => bk_rcv_rec.order_line_id,
984 x_msg_count => l_msg_count,
985 x_msg_data => l_msg_data,
986 x_return_status => l_return_status
987 );
988
989 if l_return_status <> fnd_api.g_ret_sts_success then
990 -- the order is not booked and an attempt to book it has failed.
991 -- log it to concurrent log.
992 csd_bulk_receive_util.write_to_conc_log(l_msg_count,l_msg_data);
993 -- update the bulk receive record as errored.
994 update csd_bulk_receive_items_b
995 set status = 'ERRORED'
996 where bulk_receive_id = bk_rcv_rec.bulk_receive_id;
997 -- proceed for the next record.
998 -- bug#8805130, continue is 11G keyword. Use if construct to achieve the same
999 -- functionality.
1000 -- continue;
1001 l_continue_further := false;
1002 end if;
1003 if l_continue_further then
1004 l_bulk_autorcv_tbl(l_counter).bulk_receive_id := bk_rcv_rec.bulk_receive_id;
1005 l_bulk_autorcv_tbl(l_counter).repair_line_id := bk_rcv_rec.repair_line_id;
1006 l_bulk_autorcv_tbl(l_counter).order_header_id := bk_rcv_rec.order_header_id;
1007 l_bulk_autorcv_tbl(l_counter).order_line_id := bk_rcv_rec.order_line_id;
1008 l_bulk_autorcv_tbl(l_counter).locator_id := bk_rcv_rec.locator_id;
1009 l_bulk_autorcv_tbl(l_counter).subinventory := bk_rcv_rec.subinventory;
1010 l_bulk_autorcv_tbl(l_counter).item_revision := bk_rcv_rec.item_revision;
1011 l_bulk_autorcv_tbl(l_counter).lot_number := bk_rcv_rec.lot_number;
1012 l_bulk_autorcv_tbl(l_counter).serial_number := bk_rcv_rec.serial_number;
1013
1014 l_bulk_autorcv_tbl(l_counter).rcv_attribute_category := bk_rcv_rec.rcv_attribute_category;
1015 l_bulk_autorcv_tbl(l_counter).rcv_attribute1 := bk_rcv_rec.rcv_attribute1;
1016 l_bulk_autorcv_tbl(l_counter).rcv_attribute2 := bk_rcv_rec.rcv_attribute2;
1017 l_bulk_autorcv_tbl(l_counter).rcv_attribute3 := bk_rcv_rec.rcv_attribute3;
1018 l_bulk_autorcv_tbl(l_counter).rcv_attribute4 := bk_rcv_rec.rcv_attribute4;
1019 l_bulk_autorcv_tbl(l_counter).rcv_attribute5 := bk_rcv_rec.rcv_attribute5;
1020 l_bulk_autorcv_tbl(l_counter).rcv_attribute6 := bk_rcv_rec.rcv_attribute6;
1021 l_bulk_autorcv_tbl(l_counter).rcv_attribute7 := bk_rcv_rec.rcv_attribute7;
1022 l_bulk_autorcv_tbl(l_counter).rcv_attribute8 := bk_rcv_rec.rcv_attribute8;
1023 l_bulk_autorcv_tbl(l_counter).rcv_attribute9 := bk_rcv_rec.rcv_attribute9;
1024 l_bulk_autorcv_tbl(l_counter).rcv_attribute10 := bk_rcv_rec.rcv_attribute10;
1025 l_bulk_autorcv_tbl(l_counter).rcv_attribute11 := bk_rcv_rec.rcv_attribute11;
1026 l_bulk_autorcv_tbl(l_counter).rcv_attribute12 := bk_rcv_rec.rcv_attribute12;
1027 l_bulk_autorcv_tbl(l_counter).rcv_attribute13 := bk_rcv_rec.rcv_attribute13;
1028 l_bulk_autorcv_tbl(l_counter).rcv_attribute14 := bk_rcv_rec.rcv_attribute14;
1029 l_bulk_autorcv_tbl(l_counter).rcv_attribute15 := bk_rcv_rec.rcv_attribute15;
1030 end if; -- end of l_continue_further check.
1031
1032 elsif bk_rcv_rec.unplanned_receipt_flag = 'Y' then
1033 -- entire unplanned receipt scenario should be treated as a transaction.
1034 -- an error in any step should result in processing to stop for this record.
1035 begin
1036 -- case1.
1037 -- SR, RO exists but no RMA. Create the RMA, book it and receive the item against it.
1038 if bk_rcv_rec.incident_id is not null and bk_rcv_rec.repair_line_id is not null then
1039 -- no RMA is created. Try to create a new rma line and book it.
1040 -- call the create default product txn helper.
1041 -- external ref# handling.
1042 if bk_rcv_rec.instance_id is not null and bk_rcv_rec.external_reference is not null
1043 then
1044 csd_repair_manager_util.update_external_reference(p_external_reference => bk_rcv_rec.external_reference,
1045 p_instance_id => bk_rcv_rec.instance_id,
1046 x_return_status => l_return_status,
1047 x_msg_count => l_msg_count,
1048 x_msg_data => l_msg_data);
1049 elsif bk_rcv_rec.instance_id is null and bk_rcv_rec.external_reference is not null
1050 then
1051 -- try to get the instance from the repair order.
1052 begin
1053 select instance_id
1054 into bk_rcv_rec.instance_id
1055 from csd_repairs
1056 where repair_line_id = bk_rcv_rec.repair_line_id;
1057 exception
1058 when no_data_found then
1059 null;
1060 end;
1061
1062 if bk_rcv_rec.instance_id is null then
1063 declare
1064 l_instance_rec csd_mass_rcv_pvt.instance_rec_type;
1065 l_instance_id number;
1066 begin
1067 Select ship_to_site_use_id
1068 into l_instance_rec.party_site_use_id
1069 from cs_incidents_all_b
1070 where incident_id = bk_rcv_rec.incident_id;
1071
1072 l_instance_rec.inventory_item_id := bk_rcv_rec.inventory_item_id;
1073 l_instance_rec.instance_id := null;
1074 l_instance_rec.instance_number := null;
1075 l_instance_rec.serial_number := bk_rcv_rec.serial_number;
1076 l_instance_rec.lot_number := null;
1077 l_instance_rec.quantity := 1;
1078 l_instance_rec.uom := bk_rcv_rec.uom_code;
1079 --l_instance_rec.party_site_use_id := l_party_site_use_id;
1080 l_instance_rec.party_id := bk_rcv_rec.party_id;
1081 l_instance_rec.account_id := bk_rcv_rec.cust_account_id;
1082 l_instance_rec.mfg_serial_number_flag := 'N';
1083 l_instance_rec.external_reference := bk_rcv_rec.external_reference;
1084
1085 -- call the API to create the instance
1086 csd_mass_rcv_pvt.create_item_instance (
1087 p_api_version => 1.0,
1088 p_init_msg_list => fnd_api.g_false,
1089 p_commit => fnd_api.g_false,
1090 p_validation_level => fnd_api.g_valid_level_full,
1091 x_return_status => l_return_status,
1092 x_msg_count => l_msg_count,
1093 x_msg_data => l_msg_data,
1094 px_instance_rec => l_instance_rec,
1095 x_instance_id => l_instance_id );
1096 if not (l_return_status = fnd_api.g_ret_sts_success) then
1097 raise fnd_api.g_exc_error;
1098 end if;
1099 exception
1100 when FND_API.G_EXC_ERROR then
1101 null;
1102 end;
1103 else
1104 csd_repair_manager_util.update_external_reference(p_external_reference => bk_rcv_rec.external_reference,
1105 p_instance_id => bk_rcv_rec.instance_id,
1106 x_return_status => l_return_status,
1107 x_msg_count => l_msg_count,
1108 x_msg_data => l_msg_data);
1109 end if;
1110
1111 end if;
1112 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1113 fnd_log.STRING (fnd_log.level_procedure,
1114 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
1115 'calling create default product transaction with bulk_receive_id '
1116 ||bk_rcv_rec.bulk_receive_id);
1117 End if;
1118 csd_bulk_receive_util.create_blkrcv_default_prod_txn
1119 (
1120 p_bulk_receive_id => bk_rcv_rec.bulk_receive_id,
1121 x_return_status => l_return_status,
1122 x_msg_count => l_msg_count,
1123 x_msg_data => l_msg_data
1124 );
1125
1126 if l_return_status <> g_ret_sts_success then
1127
1128 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1129 fnd_log.STRING (fnd_log.level_procedure,
1130 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
1131 'Error while creating default product txn lines '||l_msg_data);
1132 End if;
1133 csd_bulk_receive_util.write_to_conc_log(l_msg_count,l_msg_data);
1134
1135 update csd_bulk_receive_items_b
1136 set status = 'ERRORED'
1137 where bulk_receive_id = bk_rcv_rec.bulk_receive_id;
1138
1139 raise fnd_api.g_exc_error;
1140 end if;
1141
1142 -- update the csd_product_transactions table to mark it as unplanned.
1143
1144 update csd_product_transactions
1145 set unplanned_receipt_flag = 'Y'
1146 where repair_line_id = bk_rcv_rec.repair_line_id
1147 and action_type = 'RMA';
1148 -- no matching RO's found, create new RO and product lines.
1149 elsif bk_rcv_rec.incident_id is not null then
1150 -- call the create ro util api to create a new repair order.
1151
1152 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1153 fnd_log.STRING (fnd_log.level_procedure,
1154 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
1155 'Unplanned receipt: Before calling csd_bulk_receive_util.create_blkrcv_ro');
1156 End if;
1157
1158 csd_bulk_receive_util.create_blkrcv_ro(
1159 p_bulk_receive_id => bk_rcv_rec.bulk_receive_id,
1160 x_repair_line_id => l_repair_line_id,
1161 x_repair_number => l_repair_number,
1162 x_ro_status => l_ro_status,
1163 x_return_status => l_return_status,
1164 x_msg_count => l_msg_count,
1165 x_msg_data => l_msg_data
1166 );
1167 if l_return_status <> g_ret_sts_success then
1168 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1169 fnd_log.STRING (fnd_log.level_procedure,
1170 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
1171 'Error occured while creating RO '||l_msg_data);
1172 End if;
1173
1174 fnd_file.put_line(fnd_file.log,'Error occured in repair order creation '||l_msg_data);
1175
1176 update csd_bulk_receive_items_b
1177 set status = 'ERRORED'
1178 where bulk_receive_id = bk_rcv_rec.bulk_receive_id;
1179
1180 csd_bulk_receive_util.write_to_conc_log(l_msg_count,l_msg_data);
1181
1182 raise fnd_api.g_exc_error;
1183 end if;
1184
1185 update csd_bulk_receive_items_b
1186 set repair_line_id = l_repair_line_id
1187 where bulk_receive_id = bk_rcv_rec.bulk_receive_id;
1188
1189 if l_ro_status = 'DRAFT' then
1190 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1191 fnd_log.STRING (fnd_log.level_procedure,
1192 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
1193 'Draft RO: The repair order is created in draft status. Set bulk receive status to processed');
1194 End if;
1195
1196 update csd_bulk_receive_items_b
1197 set status = 'PROCESSED'
1198 where bulk_receive_id = bk_rcv_rec.bulk_receive_id;
1199
1200 else
1201 -- call the create default product txn helper to create the RMA's.
1202 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1203 fnd_log.STRING (fnd_log.level_procedure,
1204 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
1205 'Unplanned Receipt: Before calling csd_bulk_receive_util.create_blkrcv_default_prod_txn');
1206 End if;
1207 csd_bulk_receive_util.create_blkrcv_default_prod_txn
1208 (
1209 p_bulk_receive_id => bk_rcv_rec.bulk_receive_id,
1210 x_return_status => l_return_status,
1211 x_msg_count => l_msg_count,
1212 x_msg_data => l_msg_data
1213 );
1214
1215 if l_return_status <> g_ret_sts_success then
1216 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1217 fnd_log.STRING (fnd_log.level_procedure,
1218 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
1219 'Error occured while creating default product lines '||l_msg_data);
1220 End if;
1221 fnd_file.put_line(fnd_file.log,'Error occured during creation of default logistics lines '||l_msg_data);
1222 csd_bulk_receive_util.write_to_conc_log(l_msg_count,l_msg_data);
1223
1224 update csd_bulk_receive_items_b
1225 set status = 'ERRORED'
1226 where bulk_receive_id = bk_rcv_rec.bulk_receive_id;
1227
1228 raise fnd_api.g_exc_error;
1229 end if;
1230 -- get the order header and line id for the RMA just created.
1231 begin
1232 select ced.order_header_id,ced.order_line_id
1233 into bk_rcv_rec.order_header_id,bk_rcv_rec.order_line_id
1234 from csd_product_transactions cpt,
1235 cs_estimate_details ced
1236 where cpt.repair_line_id = l_repair_line_id
1237 and ced.estimate_detail_id = cpt.estimate_detail_id
1238 and cpt.action_type = 'RMA';
1239 exception
1240 when no_data_found then
1241 null;
1242 end;
1243 l_bulk_autorcv_tbl(l_counter).bulk_receive_id := bk_rcv_rec.bulk_receive_id;
1244 l_bulk_autorcv_tbl(l_counter).repair_line_id := l_repair_line_id;
1245 l_bulk_autorcv_tbl(l_counter).order_header_id := bk_rcv_rec.order_header_id;
1246 l_bulk_autorcv_tbl(l_counter).order_line_id := bk_rcv_rec.order_line_id;
1247 l_bulk_autorcv_tbl(l_counter).locator_id := bk_rcv_rec.locator_id;
1248 l_bulk_autorcv_tbl(l_counter).subinventory := bk_rcv_rec.subinventory;
1249 l_bulk_autorcv_tbl(l_counter).item_revision := bk_rcv_rec.item_revision;
1250 l_bulk_autorcv_tbl(l_counter).lot_number := bk_rcv_rec.lot_number;
1251
1252 l_bulk_autorcv_tbl(l_counter).rcv_attribute_category := bk_rcv_rec.rcv_attribute_category;
1253 l_bulk_autorcv_tbl(l_counter).rcv_attribute1 := bk_rcv_rec.rcv_attribute1;
1254 l_bulk_autorcv_tbl(l_counter).rcv_attribute2 := bk_rcv_rec.rcv_attribute2;
1255 l_bulk_autorcv_tbl(l_counter).rcv_attribute3 := bk_rcv_rec.rcv_attribute3;
1256 l_bulk_autorcv_tbl(l_counter).rcv_attribute4 := bk_rcv_rec.rcv_attribute4;
1257 l_bulk_autorcv_tbl(l_counter).rcv_attribute5 := bk_rcv_rec.rcv_attribute5;
1258 l_bulk_autorcv_tbl(l_counter).rcv_attribute6 := bk_rcv_rec.rcv_attribute6;
1259 l_bulk_autorcv_tbl(l_counter).rcv_attribute7 := bk_rcv_rec.rcv_attribute7;
1260 l_bulk_autorcv_tbl(l_counter).rcv_attribute8 := bk_rcv_rec.rcv_attribute8;
1261 l_bulk_autorcv_tbl(l_counter).rcv_attribute9 := bk_rcv_rec.rcv_attribute9;
1262 l_bulk_autorcv_tbl(l_counter).rcv_attribute10 := bk_rcv_rec.rcv_attribute10;
1263 l_bulk_autorcv_tbl(l_counter).rcv_attribute11 := bk_rcv_rec.rcv_attribute11;
1264 l_bulk_autorcv_tbl(l_counter).rcv_attribute12 := bk_rcv_rec.rcv_attribute12;
1265 l_bulk_autorcv_tbl(l_counter).rcv_attribute13 := bk_rcv_rec.rcv_attribute13;
1266 l_bulk_autorcv_tbl(l_counter).rcv_attribute14 := bk_rcv_rec.rcv_attribute14;
1267 l_bulk_autorcv_tbl(l_counter).rcv_attribute15 := bk_rcv_rec.rcv_attribute15;
1268 -- update product transactions and mark it as unplanned.
1269 update csd_product_transactions
1270 set unplanned_receipt_flag = 'Y'
1271 where repair_line_id = l_repair_line_id
1272 and action_type = 'RMA';
1273
1274 end if; -- Else part of draft status check.
1275 -- create new SR, RO and RMA (use the old code)
1276 else
1277 l_create_sr_flag := 'Y';
1278
1279 end if;
1280 exception
1281 when FND_API.G_EXC_ERROR then
1282 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1283 fnd_log.STRING (fnd_log.level_procedure,
1284 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
1285 'Error occured during unplanned receipt processing '||l_msg_data);
1286 End if;
1287 when others then
1288 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1289 fnd_log.STRING (fnd_log.level_procedure,
1290 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
1291 'In when others: '||SQLERRM);
1292 End if;
1293 raise;
1294 end;
1295 end if;
1296
1297 end loop;
1298 commit;
1299
1300 if l_bulk_autorcv_tbl.COUNT >= 1 THEN
1301 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1302 fnd_log.STRING (fnd_log.level_procedure,
1303 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
1304 'Calling csd_bulk_receive_util.bulk_auto_receive');
1305 End if;
1306
1307 csd_bulk_receive_util.bulk_auto_receive
1308 ( p_bulk_autorcv_tbl => l_bulk_autorcv_tbl,
1309 x_return_status => l_return_status,
1310 x_msg_count => l_msg_count,
1311 x_msg_data => l_msg_data
1312 );
1313 end if;
1314 if l_return_status <> g_ret_sts_success then
1315 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1316 fnd_log.STRING (fnd_log.level_procedure,
1317 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
1318 'Error in csd_bulk_receive_util.bulk_auto_receive '||l_msg_data);
1319 End if;
1320 csd_bulk_receive_util.write_to_conc_log(l_msg_count,l_msg_data);
1321 end if;
1322
1323 end if;
1324
1325 if l_create_sr_flag = 'Y' then
1326 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1327 fnd_log.STRING (fnd_log.level_procedure,
1328 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
1329 'Into create new SR, RO and RMA');
1330 End if;
1331
1332
1333
1334 --
1335 -- Step - E
1336 -- Create SR for every distinct party and then create
1337 -- RO and Logistic lines for all the records (in csd_bulk_receive_items_b table)
1338 -- having same party.If SR creation fails then RO's are not created.
1339 --
1340 For c_create_sr_rec in c_create_sr (p_transaction_number)
1341 Loop
1342 -- SR Savepoint
1343 Savepoint create_new_sr_savepoint;
1344
1345 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
1346 fnd_log.STRING (fnd_log.level_event,
1347 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
1348 'Create SR');
1349 End if;
1350
1351 -- Create SR
1352 l_sr_bulk_receive_rec.party_id := c_create_sr_rec.party_id;
1353 l_sr_bulk_receive_rec.cust_account_id := c_create_sr_rec.cust_account_id;
1354
1355 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1356 fnd_log.STRING (fnd_log.level_statement,
1357 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
1358 'Create SR for Party id - '||c_create_sr_rec.party_id
1359 ||',Account Id - '||c_create_sr_rec.cust_account_id);
1360 End if;
1361
1362 csd_bulk_receive_util.create_blkrcv_sr
1363 (
1364 p_bulk_receive_rec => l_sr_bulk_receive_rec,
1365 p_sr_notes_tbl => l_sr_notes_table,
1366 x_incident_id => l_incident_id,
1367 x_incident_number => l_incident_number,
1368 x_return_status => l_return_status,
1369 x_msg_count => l_msg_count,
1370 x_msg_data => l_msg_data
1371 );
1372
1373 If (l_return_status = FND_API.G_RET_STS_SUCCESS) then
1374
1375 -- Update the record status
1376
1377 Update csd_bulk_receive_items_b
1378 set incident_id = l_incident_id,
1379 status = 'NEW'
1380 where transaction_number = p_transaction_number
1381 and party_id = c_create_sr_rec.party_id
1382 and cust_account_id = c_create_sr_rec.cust_account_id
1383 and incident_id is null
1384 and internal_sr_flag = 'N';
1385
1386 Else
1387
1388 Rollback To create_new_sr_savepoint;
1389
1390 -- Update the record status
1391
1392 Update csd_bulk_receive_items_b
1393 set status = 'ERRORED'
1394 where party_id = c_create_sr_rec.party_id
1395 and cust_account_id = c_create_sr_rec.cust_account_id
1396 and incident_id is null
1397 and internal_sr_flag = 'N';
1398
1399 -- Write to conc log
1400 Fnd_file.put_line(fnd_file.log,'Error: Service Request Creation failed');
1401 Fnd_file.put_line(fnd_file.log,'Party id :'||c_create_sr_rec.party_id);
1402
1403 csd_bulk_receive_util.write_to_conc_log
1404 ( p_msg_count => l_msg_count,
1405 p_msg_data => l_msg_data);
1406
1407 End if;
1408
1409 If (l_return_status = FND_API.G_RET_STS_SUCCESS) then
1410
1411 -- Initialize the error count
1412 l_ro_error_count := 0;
1413
1414 -- Create RO's
1415 For c_create_ro_rec in c_create_ro (p_transaction_number,l_incident_id)
1416 Loop
1417
1418 l_c_create_ro_rowcount := c_create_ro%rowcount;
1419
1420 Savepoint create_ro_savepoint;
1421
1422 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
1423 fnd_log.STRING (fnd_log.level_event,
1424 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
1425 'New SR - Call Create Repair Order');
1426 End if;
1427
1428 -- Call Create RO Helper procedure
1429 csd_bulk_receive_util.create_blkrcv_ro
1430 (
1431 p_bulk_receive_id => c_create_ro_rec.bulk_receive_id,
1432 x_repair_line_id => l_repair_line_id,
1433 x_repair_number => l_repair_number,
1434 x_return_status => l_return_status,
1435 x_ro_status => l_ro_status,
1436 x_msg_count => l_msg_count,
1437 x_msg_data => l_msg_data
1438 );
1439
1440 If (l_return_status = FND_API.G_RET_STS_SUCCESS) then
1441
1442 -- If the RO is created in Draft status then
1443 -- no Logistic lines are created.
1444
1445 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1446 fnd_log.STRING (fnd_log.level_statement,
1447 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
1448 'New SR - Created Repair Order ['||l_repair_line_id||'] in '
1449 ||l_ro_status||' status' );
1450 End if;
1451
1452 If ( l_ro_status = 'DRAFT' ) then
1453
1454 Update csd_bulk_receive_items_b
1455 set repair_line_id = l_repair_line_id,
1456 status = 'PROCESSED'
1457 where bulk_receive_id = c_create_ro_rec.bulk_receive_id;
1458
1459 Else
1460
1461 -- Update the Bulk Receive Record
1462 Update csd_bulk_receive_items_b
1463 set repair_line_id = l_repair_line_id,
1464 status = 'NEW'
1465 where bulk_receive_id = c_create_ro_rec.bulk_receive_id;
1466
1467 -- Call to create default product transaction
1468 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
1469 fnd_log.STRING (fnd_log.level_event,
1470 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
1471 'New SR - Call Create Product Transactions');
1472 End if;
1473
1474 csd_bulk_receive_util.create_blkrcv_default_prod_txn
1475 (
1476 p_bulk_receive_id => c_create_ro_rec.bulk_receive_id,
1477 x_return_status => l_return_status,
1478 x_msg_count => l_msg_count,
1479 x_msg_data => l_msg_data
1480 );
1481
1482 If NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) then
1483
1484 -- If Logistic line creation fails then rollback RO
1485
1486 Rollback To create_ro_savepoint;
1487
1488 l_ro_error_count := l_ro_error_count + 1;
1489
1490 Update csd_bulk_receive_items_b
1491 set status = 'ERRORED'
1492 where bulk_receive_id = c_create_ro_rec.bulk_receive_id;
1493
1494 -- Write to conc log
1495 Fnd_file.put_line(fnd_file.log,'Error : Creation of Default Logistic lines failed');
1496 Fnd_file.put(fnd_file.log,'Serial Number :'||c_create_ro_rec.serial_number||',');
1497 Fnd_file.put(fnd_file.log,'Inventory Item Id :'||c_create_ro_rec.inventory_item_id||',');
1498 Fnd_file.put_line(fnd_file.log,'Qty :'||c_create_ro_rec.quantity);
1499
1500 csd_bulk_receive_util.write_to_conc_log
1501 ( p_msg_count => l_msg_count,
1502 p_msg_data => l_msg_data);
1503
1504 End if;
1505
1506 -- swai: 12.1.1 bug 7176940 - check service bulletins after RO creation
1507 IF (nvl(fnd_profile.value('CSD_AUTO_CHECK_BULLETINS'),'N') = 'Y') THEN
1508 CSD_RO_BULLETINS_PVT.LINK_BULLETINS_TO_RO(
1509 p_api_version_number => 1.0,
1510 p_init_msg_list => Fnd_Api.G_FALSE,
1511 p_commit => Fnd_Api.G_FALSE,
1512 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
1513 p_repair_line_id => l_repair_line_id,
1514 px_ro_sc_ids_tbl => l_ro_sc_ids_tbl,
1515 x_return_status => l_return_status,
1516 x_msg_count => l_msg_count,
1517 x_msg_data => l_msg_data
1518 );
1519 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1520 fnd_log.STRING (fnd_log.level_statement,
1521 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
1522 'New SR - After CSD_RO_BULLETINS_PVT.LINK_BULLETINS_TO_RO['
1523 || l_repair_line_id || ']');
1524 END IF;
1525 -- ignore return status for now.
1526 END IF;
1527
1528 End if;
1529
1530 Else
1531
1532 Rollback To create_ro_savepoint;
1533
1534 l_ro_error_count := l_ro_error_count + 1;
1535
1536 Update csd_bulk_receive_items_b
1537 set status = 'ERRORED'
1538 where bulk_receive_id = c_create_ro_rec.bulk_receive_id;
1539
1540 -- Write to conc log
1541 Fnd_file.put_line(fnd_file.log,'Error : Creation of Repair Order failed');
1542 Fnd_file.put(fnd_file.log,'Serial Number :'||c_create_ro_rec.serial_number||',');
1543 Fnd_file.put(fnd_file.log,'Inventory Item Id :'||c_create_ro_rec.inventory_item_id||',');
1544 Fnd_file.put_line(fnd_file.log,'Qty :'||c_create_ro_rec.quantity);
1545
1546 csd_bulk_receive_util.write_to_conc_log
1547 ( p_msg_count => l_msg_count,
1548 p_msg_data => l_msg_data);
1549
1550 -- 12.1.2 BR ER FP changes subhat
1551 if NVL(FND_PROFILE.value('CSD_MATCH_SR_RO_RMA_FOR_BLKRCV'),'Y') = 'Y' then
1552 update csd_product_transactions
1553 set unplanned_receipt_flag = 'Y'
1554 where repair_line_id = l_repair_line_id
1555 and action_type = 'RMA';
1556 end if;
1557
1558 End if;
1559
1560 End Loop; --- End of c_create_ro_rec Loop
1561
1562 -- Verify if any RO is created for the
1563 -- SR, if not then rollback the created SR
1564 If ( l_ro_error_count = l_c_create_ro_rowcount ) then
1565 Rollback To create_new_sr_savepoint;
1566 End if;
1567
1568 End if; -- End if of l_return_status of Service Request
1569
1570 End Loop; --- End of c_create_sr_rec Loop
1571
1572
1573 -- Commit before Auto Receiving. This is required since Auto Receiving
1574 -- is executed as a Autonomous transaction.If explicit commit is not executed
1575 -- then new entities (Order etc..) will not be visible.
1576 -- Fix for bug#5438074
1577 commit;
1578
1579 --
1580 -- Step - F
1581 -- Auto Receive
1582 --
1583 i := 0;
1584
1585 For c_auto_receive_rec in c_auto_receive(p_transaction_number)
1586 Loop
1587
1588 -- Verify if the Sub Inv is set
1589 If ( fnd_profile.value('CSD_BLK_RCV_DEFAULT_SUB_INV') is null) then
1590
1591 Fnd_file.put_line(fnd_file.log,'Error : Bulk Receive Sub Inventory Profile is Null');
1592 Fnd_file.put_line(fnd_file.log,'Error : Unable to Auto Receive');
1593
1594 -- Exit the loop
1595 exit;
1596
1597 End if;
1598
1599 l_order_status := null;
1600 l_order_line_id := null;
1601 l_order_header_id := null;
1602 l_source_serial_number := null;
1603
1604 -- Get Product Txn Details
1605 Open c_check_prdtxn_status ( c_auto_receive_rec.repair_line_id);
1606 Fetch c_check_prdtxn_status into l_order_status,l_order_line_id,
1607 l_order_header_id,l_source_serial_number;
1608 Close c_check_prdtxn_status;
1609
1610 -- Verify if the order line is BOOKED
1611 If ( l_order_status = 'BOOKED' ) then
1612
1613 l_ib_flag := null;
1614 l_item_name := null;
1615 l_serial_number_control_code := null;
1616
1617 -- Get Item Attributes
1618 Open c_get_item_attributes(c_auto_receive_rec.inventory_item_id);
1619 Fetch c_get_item_attributes into l_ib_flag,l_item_name,l_serial_number_control_code;
1620 Close c_get_item_attributes;
1621
1622 -- Verify if Serial number is entered for a Serialized item. This is possible since
1623 -- Draft RO is created for a Serialized Item with qty > 1
1624 If (l_source_serial_number is null and l_serial_number_control_code <> c_non_serialized ) then
1625
1626 -- Display the log message;Verify if Serial number is entered
1627 Fnd_file.put_line(fnd_file.log,'Warning : Serial Number is not entered for a Serialized Item,unable to Auto receive');
1628 Fnd_file.put(fnd_file.log,'Serial Number :'||c_auto_receive_rec.serial_number);
1629 Fnd_file.put_line(fnd_file.log,'Item :'||l_item_name);
1630
1631 Else
1632
1633 -- Fix for bug#5415850
1634 i := i + 1;
1635
1636 l_bulk_autorcv_tbl(i).bulk_receive_id := c_auto_receive_rec.bulk_receive_id;
1637 l_bulk_autorcv_tbl(i).repair_line_id := c_auto_receive_rec.repair_line_id;
1638 l_bulk_autorcv_tbl(i).order_header_id := l_order_header_id;
1639 l_bulk_autorcv_tbl(i).order_line_id := l_order_line_id;
1640 -- 12.1.2 BR ER FP changes, subhat.
1641 l_bulk_autorcv_tbl(i).subinventory := c_auto_receive_rec.subinventory;
1642 l_bulk_autorcv_tbl(i).item_revision := c_auto_receive_rec.item_revision;
1643 l_bulk_autorcv_tbl(i).locator_id := c_auto_receive_rec.locator_id;
1644 l_bulk_autorcv_tbl(i).lot_number := c_auto_receive_rec.lot_number;
1645
1646 l_bulk_autorcv_tbl(i).rcv_attribute_category := c_auto_receive_rec.rcv_attribute_category;
1647 l_bulk_autorcv_tbl(i).rcv_attribute1 := c_auto_receive_rec.rcv_attribute1;
1648 l_bulk_autorcv_tbl(i).rcv_attribute2 := c_auto_receive_rec.rcv_attribute2;
1649 l_bulk_autorcv_tbl(i).rcv_attribute3 := c_auto_receive_rec.rcv_attribute3;
1650 l_bulk_autorcv_tbl(i).rcv_attribute4 := c_auto_receive_rec.rcv_attribute4;
1651 l_bulk_autorcv_tbl(i).rcv_attribute5 := c_auto_receive_rec.rcv_attribute5;
1652 l_bulk_autorcv_tbl(i).rcv_attribute6 := c_auto_receive_rec.rcv_attribute6;
1653 l_bulk_autorcv_tbl(i).rcv_attribute7 := c_auto_receive_rec.rcv_attribute7;
1654 l_bulk_autorcv_tbl(i).rcv_attribute8 := c_auto_receive_rec.rcv_attribute8;
1655 l_bulk_autorcv_tbl(i).rcv_attribute9 := c_auto_receive_rec.rcv_attribute9;
1656 l_bulk_autorcv_tbl(i).rcv_attribute10 := c_auto_receive_rec.rcv_attribute10;
1657 l_bulk_autorcv_tbl(i).rcv_attribute11 := c_auto_receive_rec.rcv_attribute11;
1658 l_bulk_autorcv_tbl(i).rcv_attribute12 := c_auto_receive_rec.rcv_attribute12;
1659 l_bulk_autorcv_tbl(i).rcv_attribute13 := c_auto_receive_rec.rcv_attribute13;
1660 l_bulk_autorcv_tbl(i).rcv_attribute14 := c_auto_receive_rec.rcv_attribute14;
1661 l_bulk_autorcv_tbl(i).rcv_attribute15 := c_auto_receive_rec.rcv_attribute15;
1662
1663 End if;
1664
1665 Else
1666 -- Display the log message RO RMA Order is not BOOKED
1667 Fnd_file.put_line(fnd_file.log,'Warning : Order is not Booked,unable to Auto receive');
1668 Fnd_file.put(fnd_file.log,'Serial Number :'||c_auto_receive_rec.serial_number);
1669 Fnd_file.put_line(fnd_file.log,'Inventory Item :'||l_item_name);
1670 End if;
1671
1672 End Loop; -- End of c_auto_receive_rec Loop
1673
1674 --
1675 -- Call the Auto Receive Procedure
1676 --
1677
1678 If ( l_bulk_autorcv_tbl.count > 0 ) then
1679
1680
1681 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
1682 fnd_log.STRING (fnd_log.level_event,
1683 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
1684 'Calling Auto Receive API');
1685 End if;
1686
1687 csd_bulk_receive_util.bulk_auto_receive
1688 ( p_bulk_autorcv_tbl => l_bulk_autorcv_tbl,
1689 x_return_status => l_return_status,
1690 x_msg_count => l_msg_count,
1691 x_msg_data => l_msg_data
1692 );
1693
1694 End if;
1695 end if; -- 12.1.2 BR ER FP Changes subhat. End of else part (original blkrcv code)
1696
1697 --
1698 -- Display the Output
1699 --
1700 -- we need to write the output here only if conc req was not submitted. Which is not the case
1701 -- post 12.1.2. Thus commenting.
1702 -- bug#8968918, subhat.
1703 -- when this is commented out, if the repair order was created in draft status, the output
1704 -- is not written. Uncommenting the earlier commented code.
1705 if fnd_conc_global.request_data is null then
1706 csd_bulk_receive_util.write_to_conc_output
1707 ( p_transaction_number => p_transaction_number);
1708 end if;
1709 retcode := c_success;
1710
1711 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1712 fnd_log.STRING (fnd_log.level_procedure,
1713 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS.END',
1714 'Exit - Process Bulk Receive Items');
1715 End if;
1716
1717 EXCEPTION
1718
1719 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1720 -- write message to log file indicating the failure of the concurrent program,
1721 -- return error retcode
1722 errbuf := FND_MESSAGE.GET_STRING('CSD','CSD_BULK_RECEIVE_FAILURE');
1723 retcode := c_error;
1724
1725 WHEN FND_API.G_EXC_ERROR THEN
1726 -- write message to log file indicating the failure of the concurrent program,
1727 -- return error retcode
1728 errbuf := FND_MESSAGE.GET_STRING('CSD','CSD_BULK_RECEIVE_FAILURE');
1729 retcode := c_error;
1730
1731 WHEN OTHERS THEN
1732 -- Add Unexpected Error to Message List, here SQLERRM is used for
1733 -- getting the error
1734
1735 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME ,
1736 p_procedure_name => l_procedure_name );
1737
1738 -- Get the count of the Messages from the message list, if the count is 1
1739 -- get the message as well
1740
1741 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1742 p_count => l_msg_count,
1743 p_data => l_msg_data);
1744
1745 IF l_msg_count = 1 THEN
1746
1747 fnd_file.put_line( fnd_file.log, l_msg_data);
1748
1749 ELSIF l_msg_count > 1 THEN
1750
1751 -- If the message count is greater than 1, loop through the
1752 -- message list, retrieve the messages and write it to the log file
1753
1754 FOR l_msg_ctr IN 1..l_msg_count
1755 LOOP
1756 l_msg_data := fnd_msg_pub.get(l_msg_ctr, FND_API.G_FALSE );
1757 fnd_file.put_line( fnd_file.log, l_msg_data);
1758 END LOOP;
1759
1760 END IF;
1761
1762 -- write message to log file indicating the failure of the concurrent program,
1763 -- return error retcode
1764
1765 errbuf := FND_MESSAGE.GET_STRING('CSD','CSD_BULK_RECEIVE_FAILURE');
1766 retcode := c_error ;
1767
1768 END process_bulk_receive_items;
1769
1770 END CSD_BULK_RECEIVE_PVT;