[Home] [Help]
PACKAGE BODY: APPS.CSD_BULK_RECEIVE_PVT
Source
1 PACKAGE BODY CSD_BULK_RECEIVE_PVT AS
2 /* $Header: csdvbrvb.pls 120.17.12010000.2 2008/10/31 00:46:44 swai 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 BEGIN
191
192 --
193 -- Logic Summary
194 -- All the following steps are executed for a
195 -- particular Transaction Number.
196 --
197 -- A.If Profile 'CSD_BLKRCV_CHG_IB_OWNER' is 'NO'
198 -- then set the Party id of the Bulk Receive Rec
199 -- to the IB Owner Party
200 -- B.Update IB Owner.
201 -- C.Create Internal SR for warning records.
202 -- D.Reprocess errored Repair Orders and create Logistic
203 -- lines.
204 -- E.Create Service Request,Repair Order, Logistic lines
205 -- for new records.
206 -- F.Auto Receive all the eligible records.
207 --
208
209 savepoint process_bulk_receive_items;
210
211 --
212 -- MOAC initialization
213 --
214 MO_GLOBAL.init('CS_CHARGES');
215
216 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
217 fnd_log.STRING (fnd_log.level_procedure,
218 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS.BEGIN',
219 'Entered Process Bulk Receive Items');
220 End if;
221
222 -- Verify the required parameter - Transaction Number
223 If ( p_transaction_number is null ) then
224
225 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
226 fnd_log.STRING (fnd_log.level_event,
227 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
228 'Validate Transaction Number');
229 End if;
230
231 Fnd_file.put_line(fnd_file.log,'Error: Transaction Number is null');
232 retcode := c_error;
233
234 End if;
235
236 --
237 -- Step - A
238 -- If Profile 'CSD_BLKRCV_CHG_IB_OWNER' is 'NO'
239 -- then set the Party id of the Bulk Receive Rec
240 -- to the IB Owner Party
241 If (fnd_profile.value('CSD_BLK_RCV_CHG_IB_OWNER') = 'N') then
242
243 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
244 fnd_log.STRING (fnd_log.level_event,
245 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
246 'Change IB Owner Profile is No, verify Instance party and Entered Party');
247 End if;
248
249 For c_set_party_rec in c_set_party ( p_transaction_number)
250 Loop
251
252 If ( c_set_party_rec.inventory_item_id is not null ) then
253
254 l_ib_flag := null;
255 l_item_name := null;
256 l_serial_number_control_code := null;
257
258 Open c_get_item_attributes(c_set_party_rec.inventory_item_id);
259 Fetch c_get_item_attributes into l_ib_flag,l_item_name,l_serial_number_control_code;
260 Close c_get_item_attributes;
261
262 -- If Install base item then verify the IB Owner
263 If ( l_ib_flag = 'Y' ) then
264
265 l_ib_owner_id := null;
266 l_ib_owner_acct_id := null;
267
268 Open c_get_ib_owner (c_set_party_rec.inventory_item_id,
269 c_set_party_rec.serial_number);
270 Fetch c_get_ib_owner into l_ib_owner_id,l_ib_owner_acct_id;
271 Close c_get_ib_owner;
272
273 -- If the IB Owner is <> Entered Party then update the
274 -- Bulk Receive Party = IB Owner
275 If ( l_ib_owner_id <> c_set_party_rec.party_id) then
276
277 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
278 fnd_log.STRING (fnd_log.level_statement,
279 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
280 'Update: bulk_receive_id[' || c_set_party_rec.bulk_receive_id ||
281 '] with IB owner party id - '||l_ib_owner_id);
282 End if;
283
284 Update csd_bulk_receive_items_b
285 set party_id = l_ib_owner_id,
286 cust_account_id = l_ib_owner_acct_id
287 where bulk_receive_id = c_set_party_rec.bulk_receive_id;
288 End if;
289
290 End if;
291
292 End if; -- End if of the inventory item id null check
293
294 End Loop;
295
296 End if;
297
298 --
299 -- Step - B
300 -- Change IB owner for records which have IB owner different
301 -- from the entered Party/Account
302 --
303 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
304 fnd_log.STRING (fnd_log.level_event,
305 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
306 'Change IB Owner');
307 End if;
308
309 For c_change_owner_rec in c_change_owner (p_transaction_number)
310 Loop
311
312 Savepoint change_ib_owner;
313
314 csd_bulk_receive_util.change_blkrcv_ib_owner
315 (
316 p_bulk_receive_id => c_change_owner_rec.bulk_receive_id,
317 x_return_status => l_return_status,
318 x_msg_count => l_msg_count,
319 x_msg_data => l_msg_data
320 );
321
322 If (l_return_status = FND_API.G_RET_STS_SUCCESS) then
323
324 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
325 fnd_log.STRING (fnd_log.level_statement,
326 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
327 'Change IB owner,Update : bulk_receive_id['
328 || c_change_owner_rec.bulk_receive_id ||
329 '] party id with orig party id');
330 End if;
331
332 Update csd_bulk_receive_items_b
333 set party_id = orig_party_id
334 ,cust_account_id = orig_cust_account_id
335 ,status = 'NEW'
336 where bulk_receive_id = c_change_owner_rec.bulk_receive_id;
337
338 Else
339
340 Rollback to change_ib_owner;
341
342 -- Write to conc log
343 Fnd_file.put_line(fnd_file.log,'Error: IB Change Owner failed');
344 Fnd_file.put(fnd_file.log,'Serial Number :'||c_change_owner_rec.serial_number||',');
345 Fnd_file.put(fnd_file.log,'Inventory Item id :'||c_change_owner_rec.inventory_item_id||',');
346 Fnd_file.put(fnd_file.log,'Qty :'||c_change_owner_rec.quantity||',');
347 Fnd_file.put_line(fnd_file.log,'New Party Id :'||c_change_owner_rec.orig_party_id);
348
349 csd_bulk_receive_util.write_to_conc_log
350 ( p_msg_count => l_msg_count,
351 p_msg_data => l_msg_data);
352
353 End If;
354
355 End Loop; -- End of c_change_owner_rec loop
356
357
358 --
359 -- Step - C
360 -- Create Internal SR for Warning / Invalid records.
361 -- Note is created for every Warning and is associated with
362 -- the Internal SR.
363 --
364 i := 0;
365 l_create_intr_sr := FALSE;
366
367 fnd_message.set_name('CSD','CSD_BULK_RCV_SERIAL_CONC_LABEL');
368 l_serial_label := fnd_message.get;
369
370 fnd_message.set_name('CSD','CSD_BULK_RCV_ITEM_CONC_LABEL');
371 l_item_label := fnd_message.get;
372
373 fnd_message.set_name('CSD','CSD_BULK_RCV_QTY_CONC_LABEL');
374 l_qty_label := fnd_message.get;
375
376 For c_create_intr_sr_rec in c_create_intr_sr (p_transaction_number)
377 Loop
378
379 i:= i + 1;
380 l_create_intr_sr := TRUE;
381 l_warning_desc := null;
382 l_ib_flag := null;
383 l_item_name := null;
384 l_serial_number_control_code := null;
385
386 Open c_get_warning_desc (c_create_intr_sr_rec.warning_reason_code);
387 Fetch c_get_warning_desc into l_warning_desc;
388 Close c_get_warning_desc;
389
390 Open c_get_item_attributes(c_create_intr_sr_rec.inventory_item_id);
391 Fetch c_get_item_attributes into l_ib_flag,l_item_name,l_serial_number_control_code;
392 Close c_get_item_attributes;
393
394 l_note_details := ' - '||l_serial_label||' : '||c_create_intr_sr_rec.serial_number||','||
395 l_item_label||' : '||l_item_name||','||
396 l_qty_label||' : '||c_create_intr_sr_rec.quantity;
397
398 l_intr_sr_notes_table(i).note := l_warning_desc;
399 l_intr_sr_notes_table(i).note_detail := l_note_details;
400 l_intr_sr_notes_table(i).note_type := 'CS_PROBLEM';
401 l_intr_sr_notes_table(i).note_context_type_01 := 'CS';
402
403 End Loop;
404
405
406 If ( l_create_intr_sr ) then
407 -- Call the Create Service Request API
408 Savepoint create_intr_sr_savepoint;
409
410 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
411 fnd_log.STRING (fnd_log.level_event,
412 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
413 'Create Internal SR');
414 End if;
415
416 l_intr_party_id := null;
417 l_intr_cust_acct_id := null;
418
419 Open c_get_intr_party;
420 Fetch c_get_intr_party into l_intr_party_id,l_intr_cust_acct_id;
421 Close c_get_intr_party;
422
423 l_sr_bulk_receive_rec.party_id := l_intr_party_id;
424 l_sr_bulk_receive_rec.cust_account_id := l_intr_cust_acct_id;
425
426 csd_bulk_receive_util.create_blkrcv_sr
427 (
428 p_bulk_receive_rec => l_sr_bulk_receive_rec,
429 p_sr_notes_tbl => l_intr_sr_notes_table,
430 x_incident_id => l_incident_id,
431 x_incident_number => l_incident_number,
432 x_return_status => l_return_status,
433 x_msg_count => l_msg_count,
434 x_msg_data => l_msg_data
435 );
436
437 If (l_return_status = FND_API.G_RET_STS_SUCCESS) then
438
439 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
440 fnd_log.STRING (fnd_log.level_statement,
441 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
442 'Created Internal SR : Incident id = '
443 ||l_incident_id );
444 End if;
445
446 Update csd_bulk_receive_items_b
447 set incident_id = l_incident_id,
448 status = 'PROCESSED',
449 party_id = l_intr_party_id,
450 cust_account_id = l_intr_cust_acct_id
451 where transaction_number = p_transaction_number
452 and incident_id is null
453 and internal_sr_flag = 'Y';
454
455 Else
456
457 Rollback To create_intr_sr_savepoint;
458
459 Update csd_bulk_receive_items_b
460 set status = 'ERRORED'
461 where transaction_number = p_transaction_number
462 and incident_id is null
463 and internal_sr_flag = 'Y';
464
465 -- Write to Conc Log
466 Fnd_file.put_line(fnd_file.log,'Error: Creation of Internal Service Request failed');
467 Fnd_file.put_line(fnd_file.log,'Internal party id :'||l_intr_party_id);
468
469 csd_bulk_receive_util.write_to_conc_log
470 ( p_msg_count => l_msg_count,
471 p_msg_data => l_msg_data);
472
473 End if;
474 End if;
475
476
477 --
478 -- Step - D
479 -- To Reprocess Errored RO's
480 --
481 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
482 fnd_log.STRING (fnd_log.level_event,
483 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
484 'Check and reprocess Errored Repair Orders');
485 End if;
486
487 For c_reprocess_ro_rec in c_reprocess_ro(p_transaction_number)
488 Loop
489
490 Savepoint reprocess_ro_savepoint;
491
492 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
493 fnd_log.STRING (fnd_log.level_event,
494 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
495 'Reprocess RO - Call Create Repair Orders');
496 End if;
497
498 -- Call Create RO Helper procedure
499 csd_bulk_receive_util.create_blkrcv_ro
500 (
501 p_bulk_receive_id => c_reprocess_ro_rec.bulk_receive_id,
502 x_repair_line_id => l_repair_line_id,
503 x_repair_number => l_repair_number,
504 x_return_status => l_return_status,
505 x_ro_status => l_ro_status,
506 x_msg_count => l_msg_count,
507 x_msg_data => l_msg_data
508 );
509
510 If (l_return_status = FND_API.G_RET_STS_SUCCESS) then
511
512 -- If RO is created in Draft status then
513 -- no Logistic lines are created
514
515 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
516 fnd_log.STRING (fnd_log.level_statement,
517 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
518 'Reprocess RO - Created Repair Order ['||l_repair_line_id||'] in '
519 ||l_ro_status||' status' );
520 End if;
521
522 If ( l_ro_status = 'DRAFT' ) then
523
524 Update csd_bulk_receive_items_b
525 set repair_line_id = l_repair_line_id,
526 status = 'PROCESSED'
527 where bulk_receive_id = c_reprocess_ro_rec.bulk_receive_id;
528
529 Else
530
531 -- Update the Bulk Receive Record
532 Update csd_bulk_receive_items_b
533 set repair_line_id = l_repair_line_id,
534 status = 'NEW'
535 where bulk_receive_id = c_reprocess_ro_rec.bulk_receive_id;
536
537 -- Call the create default product transaction
538
539 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
540 fnd_log.STRING (fnd_log.level_event,
541 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
542 'Reprocess RO - Call Create Product Transactions');
543 End if;
544
545 csd_bulk_receive_util.create_blkrcv_default_prod_txn
546 (
547 p_bulk_receive_id => c_reprocess_ro_rec.bulk_receive_id,
548 x_return_status => l_return_status,
549 x_msg_count => l_msg_count,
550 x_msg_data => l_msg_data
551 );
552
553 If NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) then
554
555 -- If Logistic line creation fails then rollback RO
556 Rollback To reprocess_ro_savepoint;
557
558 Update csd_bulk_receive_items_b
559 set status = 'ERRORED'
560 where bulk_receive_id = c_reprocess_ro_rec.bulk_receive_id;
561
562 -- Write to conc log
563 Fnd_file.put_line(fnd_file.log,'Error : Creation of Default Logistic lines failed');
564 Fnd_file.put(fnd_file.log,'Serial Number :'||c_reprocess_ro_rec.serial_number||',');
565 Fnd_file.put(fnd_file.log,'Inventory Item Id :'||c_reprocess_ro_rec.inventory_item_id||',');
566 Fnd_file.put_line(fnd_file.log,'Qty :'||c_reprocess_ro_rec.quantity);
567
568 csd_bulk_receive_util.write_to_conc_log
569 ( p_msg_count => l_msg_count,
570 p_msg_data => l_msg_data);
571
572 End if;
573
574 -- swai: 12.1.1 bug 7176940 - check service bulletins after RO creation
575 IF (nvl(fnd_profile.value('CSD_AUTO_CHECK_BULLETINS'),'N') = 'Y') THEN
576 CSD_RO_BULLETINS_PVT.LINK_BULLETINS_TO_RO(
577 p_api_version_number => 1.0,
578 p_init_msg_list => Fnd_Api.G_FALSE,
579 p_commit => Fnd_Api.G_FALSE,
580 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
581 p_repair_line_id => l_repair_line_id,
582 px_ro_sc_ids_tbl => l_ro_sc_ids_tbl,
583 x_return_status => l_return_status,
584 x_msg_count => l_msg_count,
585 x_msg_data => l_msg_data
586 );
587 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
588 fnd_log.STRING (fnd_log.level_statement,
589 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
590 'Reprocess RO - After CSD_RO_BULLETINS_PVT.LINK_BULLETINS_TO_RO['
591 || l_repair_line_id || ']');
592 END IF;
593 -- ignore return status for now.
594 END IF;
595
596 End if; -- End if of status = 'DRAFT' if condition
597
598 Else
599
600 Rollback To reprocess_ro_savepoint;
601
602 Update csd_bulk_receive_items_b
603 set status = 'ERRORED'
604 where bulk_receive_id = c_reprocess_ro_rec.bulk_receive_id;
605
606 -- Write to conc log
607 Fnd_file.put_line(fnd_file.log,'Error : Creation of Repair Order failed');
608 Fnd_file.put(fnd_file.log,'Serial Number :'||c_reprocess_ro_rec.serial_number||',');
609 Fnd_file.put(fnd_file.log,'Inventory Item Id :'||c_reprocess_ro_rec.inventory_item_id||',');
610 Fnd_file.put_line(fnd_file.log,'Qty :'||c_reprocess_ro_rec.quantity);
611
612 csd_bulk_receive_util.write_to_conc_log
613 ( p_msg_count => l_msg_count,
614 p_msg_data => l_msg_data);
615
616 End if;
617
618 End Loop; -- End of c_reprocess_ro_rec loop
619
620
621
622 --
623 -- Step - E
624 -- Create SR for every distinct party and then create
625 -- RO and Logistic lines for all the records (in csd_bulk_receive_items_b table)
626 -- having same party.If SR creation fails then RO's are not created.
627 --
628 For c_create_sr_rec in c_create_sr (p_transaction_number)
629 Loop
630 -- SR Savepoint
631 Savepoint create_new_sr_savepoint;
632
633 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
634 fnd_log.STRING (fnd_log.level_event,
635 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
636 'Create SR');
637 End if;
638
639 -- Create SR
640 l_sr_bulk_receive_rec.party_id := c_create_sr_rec.party_id;
641 l_sr_bulk_receive_rec.cust_account_id := c_create_sr_rec.cust_account_id;
642
643 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
644 fnd_log.STRING (fnd_log.level_statement,
645 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
646 'Create SR for Party id - '||c_create_sr_rec.party_id
647 ||',Account Id - '||c_create_sr_rec.cust_account_id);
648 End if;
649
650 csd_bulk_receive_util.create_blkrcv_sr
651 (
652 p_bulk_receive_rec => l_sr_bulk_receive_rec,
653 p_sr_notes_tbl => l_sr_notes_table,
654 x_incident_id => l_incident_id,
655 x_incident_number => l_incident_number,
656 x_return_status => l_return_status,
657 x_msg_count => l_msg_count,
658 x_msg_data => l_msg_data
659 );
660
661 If (l_return_status = FND_API.G_RET_STS_SUCCESS) then
662
663 -- Update the record status
664
665 Update csd_bulk_receive_items_b
666 set incident_id = l_incident_id,
667 status = 'NEW'
668 where transaction_number = p_transaction_number
669 and party_id = c_create_sr_rec.party_id
670 and cust_account_id = c_create_sr_rec.cust_account_id
671 and incident_id is null
672 and internal_sr_flag = 'N';
673
674 Else
675
676 Rollback To create_new_sr_savepoint;
677
678 -- Update the record status
679
680 Update csd_bulk_receive_items_b
681 set status = 'ERRORED'
682 where party_id = c_create_sr_rec.party_id
683 and cust_account_id = c_create_sr_rec.cust_account_id
684 and incident_id is null
685 and internal_sr_flag = 'N';
686
687 -- Write to conc log
688 Fnd_file.put_line(fnd_file.log,'Error: Service Request Creation failed');
689 Fnd_file.put_line(fnd_file.log,'Party id :'||c_create_sr_rec.party_id);
690
691 csd_bulk_receive_util.write_to_conc_log
692 ( p_msg_count => l_msg_count,
693 p_msg_data => l_msg_data);
694
695 End if;
696
697 If (l_return_status = FND_API.G_RET_STS_SUCCESS) then
698
699 -- Initialize the error count
700 l_ro_error_count := 0;
701
702 -- Create RO's
703 For c_create_ro_rec in c_create_ro (p_transaction_number,l_incident_id)
704 Loop
705
706 l_c_create_ro_rowcount := c_create_ro%rowcount;
707
708 Savepoint create_ro_savepoint;
709
710 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
711 fnd_log.STRING (fnd_log.level_event,
712 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
713 'New SR - Call Create Repair Order');
714 End if;
715
716 -- Call Create RO Helper procedure
717 csd_bulk_receive_util.create_blkrcv_ro
718 (
719 p_bulk_receive_id => c_create_ro_rec.bulk_receive_id,
720 x_repair_line_id => l_repair_line_id,
721 x_repair_number => l_repair_number,
722 x_return_status => l_return_status,
723 x_ro_status => l_ro_status,
724 x_msg_count => l_msg_count,
725 x_msg_data => l_msg_data
726 );
727
728 If (l_return_status = FND_API.G_RET_STS_SUCCESS) then
729
730 -- If the RO is created in Draft status then
731 -- no Logistic lines are created.
732
733 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
734 fnd_log.STRING (fnd_log.level_statement,
735 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
736 'New SR - Created Repair Order ['||l_repair_line_id||'] in '
737 ||l_ro_status||' status' );
738 End if;
739
740 If ( l_ro_status = 'DRAFT' ) then
741
742 Update csd_bulk_receive_items_b
743 set repair_line_id = l_repair_line_id,
744 status = 'PROCESSED'
745 where bulk_receive_id = c_create_ro_rec.bulk_receive_id;
746
747 Else
748
749 -- Update the Bulk Receive Record
750 Update csd_bulk_receive_items_b
751 set repair_line_id = l_repair_line_id,
752 status = 'NEW'
753 where bulk_receive_id = c_create_ro_rec.bulk_receive_id;
754
755 -- Call to create default product transaction
756 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
757 fnd_log.STRING (fnd_log.level_event,
758 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
759 'New SR - Call Create Product Transactions');
760 End if;
761
762 csd_bulk_receive_util.create_blkrcv_default_prod_txn
763 (
764 p_bulk_receive_id => c_create_ro_rec.bulk_receive_id,
765 x_return_status => l_return_status,
766 x_msg_count => l_msg_count,
767 x_msg_data => l_msg_data
768 );
769
770 If NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) then
771
772 -- If Logistic line creation fails then rollback RO
773
774 Rollback To create_ro_savepoint;
775
776 l_ro_error_count := l_ro_error_count + 1;
777
778 Update csd_bulk_receive_items_b
779 set status = 'ERRORED'
780 where bulk_receive_id = c_create_ro_rec.bulk_receive_id;
781
782 -- Write to conc log
783 Fnd_file.put_line(fnd_file.log,'Error : Creation of Default Logistic lines failed');
784 Fnd_file.put(fnd_file.log,'Serial Number :'||c_create_ro_rec.serial_number||',');
785 Fnd_file.put(fnd_file.log,'Inventory Item Id :'||c_create_ro_rec.inventory_item_id||',');
786 Fnd_file.put_line(fnd_file.log,'Qty :'||c_create_ro_rec.quantity);
787
788 csd_bulk_receive_util.write_to_conc_log
789 ( p_msg_count => l_msg_count,
790 p_msg_data => l_msg_data);
791
792 End if;
793
794 -- swai: 12.1.1 bug 7176940 - check service bulletins after RO creation
795 IF (nvl(fnd_profile.value('CSD_AUTO_CHECK_BULLETINS'),'N') = 'Y') THEN
796 CSD_RO_BULLETINS_PVT.LINK_BULLETINS_TO_RO(
797 p_api_version_number => 1.0,
798 p_init_msg_list => Fnd_Api.G_FALSE,
799 p_commit => Fnd_Api.G_FALSE,
800 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
801 p_repair_line_id => l_repair_line_id,
802 px_ro_sc_ids_tbl => l_ro_sc_ids_tbl,
803 x_return_status => l_return_status,
804 x_msg_count => l_msg_count,
805 x_msg_data => l_msg_data
806 );
807 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
808 fnd_log.STRING (fnd_log.level_statement,
809 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
810 'New SR - After CSD_RO_BULLETINS_PVT.LINK_BULLETINS_TO_RO['
811 || l_repair_line_id || ']');
812 END IF;
813 -- ignore return status for now.
814 END IF;
815
816 End if;
817
818 Else
819
820 Rollback To create_ro_savepoint;
821
822 l_ro_error_count := l_ro_error_count + 1;
823
824 Update csd_bulk_receive_items_b
825 set status = 'ERRORED'
826 where bulk_receive_id = c_create_ro_rec.bulk_receive_id;
827
828 -- Write to conc log
829 Fnd_file.put_line(fnd_file.log,'Error : Creation of Repair Order failed');
830 Fnd_file.put(fnd_file.log,'Serial Number :'||c_create_ro_rec.serial_number||',');
831 Fnd_file.put(fnd_file.log,'Inventory Item Id :'||c_create_ro_rec.inventory_item_id||',');
832 Fnd_file.put_line(fnd_file.log,'Qty :'||c_create_ro_rec.quantity);
833
834 csd_bulk_receive_util.write_to_conc_log
835 ( p_msg_count => l_msg_count,
836 p_msg_data => l_msg_data);
837
838
839 End if;
840
841 End Loop; --- End of c_create_ro_rec Loop
842
843 -- Verify if any RO is created for the
844 -- SR, if not then rollback the created SR
845 If ( l_ro_error_count = l_c_create_ro_rowcount ) then
846 Rollback To create_new_sr_savepoint;
847 End if;
848
849 End if; -- End if of l_return_status of Service Request
850
851 End Loop; --- End of c_create_sr_rec Loop
852
853
854 -- Commit before Auto Receiving. This is required since Auto Receiving
855 -- is executed as a Autonomous transaction.If explicit commit is not executed
856 -- then new entities (Order etc..) will not be visible.
857 -- Fix for bug#5438074
858 commit;
859
860 --
861 -- Step - F
862 -- Auto Receive
863 --
864 i := 0;
865
866 For c_auto_receive_rec in c_auto_receive(p_transaction_number)
867 Loop
868
869 -- Verify if the Sub Inv is set
870 If ( fnd_profile.value('CSD_BLK_RCV_DEFAULT_SUB_INV') is null) then
871
872 Fnd_file.put_line(fnd_file.log,'Error : Bulk Receive Sub Inventory Profile is Null');
873 Fnd_file.put_line(fnd_file.log,'Error : Unable to Auto Receive');
874
875 -- Exit the loop
876 exit;
877
878 End if;
879
880 l_order_status := null;
881 l_order_line_id := null;
882 l_order_header_id := null;
883 l_source_serial_number := null;
884
885 -- Get Product Txn Details
886 Open c_check_prdtxn_status ( c_auto_receive_rec.repair_line_id);
887 Fetch c_check_prdtxn_status into l_order_status,l_order_line_id,
888 l_order_header_id,l_source_serial_number;
889 Close c_check_prdtxn_status;
890
891 -- Verify if the order line is BOOKED
892 If ( l_order_status = 'BOOKED' ) then
893
894 l_ib_flag := null;
895 l_item_name := null;
896 l_serial_number_control_code := null;
897
898 -- Get Item Attributes
899 Open c_get_item_attributes(c_auto_receive_rec.inventory_item_id);
900 Fetch c_get_item_attributes into l_ib_flag,l_item_name,l_serial_number_control_code;
901 Close c_get_item_attributes;
902
903 -- Verify if Serial number is entered for a Serialized item. This is possible since
904 -- Draft RO is created for a Serialized Item with qty > 1
905 If (l_source_serial_number is null and l_serial_number_control_code <> c_non_serialized ) then
906
907 -- Display the log message;Verify if Serial number is entered
908 Fnd_file.put_line(fnd_file.log,'Warning : Serial Number is not entered for a Serialized Item,unable to Auto receive');
909 Fnd_file.put(fnd_file.log,'Serial Number :'||c_auto_receive_rec.serial_number);
910 Fnd_file.put_line(fnd_file.log,'Item :'||l_item_name);
911
912 Else
913
914 -- Fix for bug#5415850
915 i := i + 1;
916
917 l_bulk_autorcv_tbl(i).bulk_receive_id := c_auto_receive_rec.bulk_receive_id;
918 l_bulk_autorcv_tbl(i).repair_line_id := c_auto_receive_rec.repair_line_id;
919 l_bulk_autorcv_tbl(i).order_header_id := l_order_header_id;
920 l_bulk_autorcv_tbl(i).order_line_id := l_order_line_id;
921
922 End if;
923
924 Else
925 -- Display the log message RO RMA Order is not BOOKED
926 Fnd_file.put_line(fnd_file.log,'Warning : Order is not Booked,unable to Auto receive');
927 Fnd_file.put(fnd_file.log,'Serial Number :'||c_auto_receive_rec.serial_number);
928 Fnd_file.put_line(fnd_file.log,'Inventory Item :'||l_item_name);
929 End if;
930
931 End Loop; -- End of c_auto_receive_rec Loop
932
933 --
934 -- Call the Auto Receive Procedure
935 --
936
937 If ( l_bulk_autorcv_tbl.count > 0 ) then
938
939
940 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
941 fnd_log.STRING (fnd_log.level_event,
942 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS',
943 'Calling Auto Receive API');
944 End if;
945
946 csd_bulk_receive_util.bulk_auto_receive
947 ( p_bulk_autorcv_tbl => l_bulk_autorcv_tbl,
948 x_return_status => l_return_status,
949 x_msg_count => l_msg_count,
950 x_msg_data => l_msg_data
951 );
952
953 End if;
954
955
956 --
957 -- Display the Output
958 --
959 csd_bulk_receive_util.write_to_conc_output
960 ( p_transaction_number => p_transaction_number);
961
962 retcode := c_success;
963
964 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
965 fnd_log.STRING (fnd_log.level_procedure,
966 'CSD.PLSQL.CSD_BULK_RECEIVE_PVT.PROCESS_BULK_RECEIVE_ITEMS.END',
967 'Exit - Process Bulk Receive Items');
968 End if;
969
970 EXCEPTION
971
972 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
973 -- write message to log file indicating the failure of the concurrent program,
974 -- return error retcode
975 errbuf := FND_MESSAGE.GET_STRING('CSD','CSD_BULK_RECEIVE_FAILURE');
976 retcode := c_error;
977
978 WHEN FND_API.G_EXC_ERROR THEN
979 -- write message to log file indicating the failure of the concurrent program,
980 -- return error retcode
981 errbuf := FND_MESSAGE.GET_STRING('CSD','CSD_BULK_RECEIVE_FAILURE');
982 retcode := c_error;
983
984 WHEN OTHERS THEN
985 -- Add Unexpected Error to Message List, here SQLERRM is used for
986 -- getting the error
987
988 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME ,
989 p_procedure_name => l_procedure_name );
990
991 -- Get the count of the Messages from the message list, if the count is 1
992 -- get the message as well
993
994 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
995 p_count => l_msg_count,
996 p_data => l_msg_data);
997
998 IF l_msg_count = 1 THEN
999
1000 fnd_file.put_line( fnd_file.log, l_msg_data);
1001
1002 ELSIF l_msg_count > 1 THEN
1003
1004 -- If the message count is greater than 1, loop through the
1005 -- message list, retrieve the messages and write it to the log file
1006
1007 FOR l_msg_ctr IN 1..l_msg_count
1008 LOOP
1009 l_msg_data := fnd_msg_pub.get(l_msg_ctr, FND_API.G_FALSE );
1010 fnd_file.put_line( fnd_file.log, l_msg_data);
1011 END LOOP;
1012
1013 END IF;
1014
1015 -- write message to log file indicating the failure of the concurrent program,
1016 -- return error retcode
1017
1018 errbuf := FND_MESSAGE.GET_STRING('CSD','CSD_BULK_RECEIVE_FAILURE');
1019 retcode := c_error ;
1020
1021 END process_bulk_receive_items;
1022
1023 END CSD_BULK_RECEIVE_PVT;