[Home] [Help]
PACKAGE BODY: APPS.CSD_BULK_RECEIVE_UTIL
Source
1 PACKAGE BODY CSD_BULK_RECEIVE_UTIL AS
2 /* $Header: csdubrub.pls 120.24.12010000.3 2008/12/22 21:37:30 swai ship $ */
3 /*-----------------------------------------------------------------*/
4 /* procedure name: validate_bulk_receive_rec */
5 /* description : Validate Bulk Receive record definition */
6 /* */
7 /*-----------------------------------------------------------------*/
8
9 PROCEDURE validate_bulk_receive_rec
10 (
11 p_party_id IN NUMBER,
12 p_quantity IN NUMBER,
13 p_serial_number IN VARCHAR2,
14 p_inventory_item_id IN NUMBER,
15 x_warning_flag OUT NOCOPY VARCHAR2,
16 x_warning_reason_code OUT NOCOPY VARCHAR2,
17 x_change_owner_flag OUT NOCOPY VARCHAR2,
18 x_internal_sr_flag OUT NOCOPY VARCHAR2)
19 IS
20
21 -- Cursor to get item attributes
22 Cursor c_get_item_attributes (p_inventory_item_id in Number) IS
23 select serial_number_control_code,
24 comms_nl_trackable_flag
25 from mtl_system_items_kfv
26 where inventory_item_id = p_inventory_item_id
27 and organization_id = cs_std.get_item_valdn_orgzn_id;
28
29 -- Cursor to derive the Instance and IB Owner
30 Cursor c_get_ib_info ( p_inventory_item_id in Number,p_serial_number in Varchar2) is
31 Select
32 owner_party_id,
33 instance_id
34 from csi_item_instances
35 where serial_number = p_serial_number
36 and inventory_item_id = p_inventory_item_id;
37
38 -- Cursor to verify the Serial number against the Item
39 Cursor c_validate_sn_item (p_inventory_item_id in Number,p_serial_number in Varchar2) is
40 Select
41 inventory_item_id
42 from mtl_serial_numbers
43 where serial_number = p_serial_number
44 and inventory_item_id = p_inventory_item_id;
45
46 -- Local variables
47 l_serial_num_control_code Number;
48 l_install_base_flag Varchar2(1);
49 l_owner_party_id Number;
50 l_instance_id Number;
51 l_inventory_item_id Number;
52 c_serialized_predefined CONSTANT Number := 2;
53 c_non_serialized CONSTANT Number := 1;
54 c_ib CONSTANT Varchar2(1) := 'Y';
55 c_non_ib CONSTANT Varchar2(1) := 'N';
56
57 BEGIN
58
59 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
60 fnd_log.STRING (fnd_log.level_procedure,
61 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.VALIDATE_BULK_RECEIVE_REC.BEGIN',
62 'Enter - Validate bulk receive rec');
63 End if;
64 --
65 -- Initialize the flags
66 --
67 x_warning_flag := 'N';
68 x_warning_reason_code := Null;
69 x_internal_sr_flag := 'N';
70 x_change_owner_flag := 'N';
71
72 --
73 -- Derive the Item attributes
74 --
75 If ( p_inventory_item_id is not null ) then
76
77 l_serial_num_control_code := null;
78 l_install_base_flag := null;
79
80 Open c_get_item_attributes (p_inventory_item_id);
81 Fetch c_get_item_attributes into
82 l_serial_num_control_code,
83 l_install_base_flag;
84
85 If c_get_item_attributes%ISOPEN THEN
86 Close c_get_item_attributes;
87 End If;
88 End if;
89
90 -- If Inventory_item_id is NULL
91 If ( p_inventory_item_id is null) then
92
93 x_warning_flag := 'Y';
94 x_warning_reason_code := 'ITEM_NOT_ENTERED';
95 x_internal_sr_flag := 'Y';
96 x_change_owner_flag := 'N';
97
98 End if;
99
100
101 -- If Inventory_item_id is NOT NULL and Serial_Number is NULL
102 If ( p_inventory_item_id is not null and p_serial_number is null ) then
103
104 If ( l_serial_num_control_code <> c_non_serialized and p_quantity > 1) then
105 x_warning_flag := 'Y';
106 x_warning_reason_code := 'CREATE_DRAFT_RO';
107 x_internal_sr_flag := 'N';
108 x_change_owner_flag := 'N';
109 End if;
110
111 End if;
112
113
114 -- If Serial_Number is NOT NULL and Inventory_item_id is NOT NULL
115 If ( p_inventory_item_id is not null and p_serial_number is not null ) then
116
117 -- For NON Serialized Item
118 If ( l_serial_num_control_code = c_non_serialized ) then
119 x_warning_flag := 'Y';
120 x_warning_reason_code := 'NON_SN_ITEM';
121 x_internal_sr_flag := 'Y';
122 x_change_owner_flag := 'N';
123 End if;
124
125 -- For Serialized IB Item
126 -- Verify if Instance exists,if not then Create new Instance Else
127 -- Verify if Change IB Owner is required
128 If ( l_serial_num_control_code <> c_non_serialized and l_install_base_flag = c_ib ) then
129
130 l_owner_party_id := null;
131 l_instance_id := null;
132
133 Open c_get_ib_info(p_inventory_item_id,p_serial_number);
134 Fetch c_get_ib_info into l_owner_party_id,l_instance_id;
135
136 If ( c_get_ib_info%NOTFOUND) then
137
138 x_warning_flag := 'Y';
139 x_warning_reason_code := 'CREATE_IB_INSTANCE';
140 x_internal_sr_flag := 'N';
141 x_change_owner_flag := 'N';
142
143 Else
144 -- If the Owner party <> Entered Party and if the
145 -- Change IB Owner profile is set to Yes then
146 -- Change the IB Owner.
147 If ( l_owner_party_id <> p_party_id ) then
148 If ( fnd_profile.value('CSD_BLK_RCV_CHG_IB_OWNER') = 'Y') then
149 x_warning_flag := 'Y';
150 x_warning_reason_code := 'CHANGE_IB_OWNER';
151 x_internal_sr_flag := 'N';
152 x_change_owner_flag := 'Y';
153 End if;
154 End if;
155
156 End if;
157
158 Close c_get_ib_info;
159
160 End if;
161
162 If ( l_serial_num_control_code <> c_non_serialized and p_serial_number is not null) then
163
164 -- check the SN status ( @receipt,@pre-defined,@So issue )
165 l_inventory_item_id := null;
166
167 Open c_validate_sn_item(p_inventory_item_id,p_serial_number);
168 Fetch c_validate_sn_item into l_inventory_item_id;
169
170 If ( c_validate_sn_item%NOTFOUND )then
171
172 If ( l_serial_num_control_code = c_serialized_predefined )then
173
174 x_warning_flag := 'Y';
175 x_warning_reason_code := 'CANNOT_CREATE_PRE_DEFINED_SN';
176 x_internal_sr_flag := 'Y';
177 x_change_owner_flag := 'N';
178
179 Else
180
181 If ( nvl(l_install_base_flag,c_non_ib) = c_non_ib ) then
182 x_warning_flag := 'Y';
183 x_warning_reason_code := 'CREATE_SN';
184 x_internal_sr_flag := 'N';
185 x_change_owner_flag := 'N';
186 End if;
187
188 End if;
189
190 End if;
191
192 Close c_validate_sn_item;
193
194 End if;
195
196 End if;
197
198 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
199 fnd_log.STRING (fnd_log.level_statement,
200 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.VALIDATE_BULK_RECEIVE_REC',
201 'Warning Flag - '||x_warning_flag||
202 ',Warning Reason Code - '||x_warning_reason_code||
203 ',Internal SR Flag - '||x_internal_sr_flag||
204 ',Change Owner Flag - '||x_change_owner_flag);
205 End if;
206
207 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
208 fnd_log.STRING (fnd_log.level_procedure,
209 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.VALIDATE_BULK_RECEIVE_REC.END',
210 'Exit - Validate bulk receive rec');
211 End if;
212
213 END validate_bulk_receive_rec;
214
215
216 /*-----------------------------------------------------------------*/
217 /* procedure name: create_blkrcv_sr */
218 /* description : Procedure to create Service Request */
219 /* */
220 /*-----------------------------------------------------------------*/
221
222 PROCEDURE create_blkrcv_sr
223 (
224 p_bulk_receive_rec IN csd_bulk_receive_util.bulk_receive_rec,
225 p_sr_notes_tbl IN cs_servicerequest_pub.notes_table,
226 x_incident_id OUT NOCOPY NUMBER,
227 x_incident_number OUT NOCOPY VARCHAR2,
228 x_return_status OUT NOCOPY VARCHAR2,
229 x_msg_count OUT NOCOPY NUMBER,
230 x_msg_data OUT NOCOPY VARCHAR2
231
232 )
233 IS
234
235 -- Cursor to derive party type
236 Cursor c_sr_party(p_party_id number) is
237 select party_type from hz_parties
238 where party_id = p_party_id;
239
240 -- Cursor to derive primary bill to site id
241 Cursor c_bill_to_site(p_party_id number) is
242 Select hpu.party_site_use_id
243 from hz_party_sites hps,
244 hz_party_site_uses hpu
245 where
246 hps.party_id = p_party_id
247 and hps.party_site_id = hpu.party_site_id
248 and hpu.site_use_type = 'BILL_TO'
249 and hpu.primary_per_type = 'Y';
250
251 -- Cursor to derive primary ship to site id
252 Cursor c_ship_to_site(p_party_id number) is
253 Select hpu.party_site_use_id
254 from hz_party_sites hps,
255 hz_party_site_uses hpu
256 where
257 hps.party_id = p_party_id
258 and hps.party_site_id = hpu.party_site_id
259 and hpu.site_use_type = 'SHIP_TO'
260 and hpu.primary_per_type = 'Y';
261
262 -- Local variables
263 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_BLKRCV_SR';
264 l_api_version CONSTANT NUMBER := 1.0;
265 l_party_type Varchar2(30);
266 l_bill_to_site_use_id Number;
267 l_ship_to_site_use_id Number;
268 l_service_request_rec CSD_PROCESS_PVT.SERVICE_REQUEST_REC := CSD_PROCESS_UTIL.SR_REC;
269
270 BEGIN
271
272 savepoint create_blkrcv_sr;
273
274 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
275 fnd_log.STRING (fnd_log.level_procedure,
276 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CREATE_BULKRCV_SR.BEGIN',
277 'Enter - Create Blkrcv SR');
278 End if;
279
280 -- Derive the party type
281 l_party_type := null;
282
283 Open c_sr_party (p_bulk_receive_rec.party_id);
284 Fetch c_sr_party into l_party_type;
285 Close c_sr_party;
286
287 -- Derive the Primary Bill To Site Use Id
288 l_bill_to_site_use_id := null;
289
290 Open c_bill_to_site (p_bulk_receive_rec.party_id);
291 Fetch c_bill_to_site into l_bill_to_site_use_id;
292 Close c_bill_to_site;
293
294 -- Derive the Primary Ship To Site Use Id
295 l_ship_to_site_use_id := null;
296
297 Open c_ship_to_site (p_bulk_receive_rec.party_id);
298 Fetch c_ship_to_site into l_ship_to_site_use_id;
299 Close c_ship_to_site;
300
301 -- Assign / Initialize the Service request Rec
302 l_service_request_rec.request_date := sysdate;
303 l_service_request_rec.type_id := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_TYPE');
304 l_service_request_rec.status_id := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_STATUS');
305 l_service_request_rec.severity_id := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_SEVERITY');
306 l_service_request_rec.urgency_id := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_URGENCY');
307 l_service_request_rec.closed_date := null;
308 l_service_request_rec.owner_id := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_OWNER');
309 l_service_request_rec.owner_group_id := NULL;
310 l_service_request_rec.publish_flag := '';
311 l_service_request_rec.summary := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_SUMMARY');
312 l_service_request_rec.caller_type := l_party_type;
313 l_service_request_rec.customer_id := p_bulk_receive_rec.party_id;
314 l_service_request_rec.customer_number := null;
315 l_service_request_rec.customer_product_id := null;
316 l_service_request_rec.cp_ref_number := null;
317 l_service_request_rec.inv_item_revision := null;
318 l_service_request_rec.inventory_item_id := null;
319 l_service_request_rec.inventory_org_id := null;
320 l_service_request_rec.current_serial_number := null;
321 l_service_request_rec.original_order_number := null;
322 l_service_request_rec.purchase_order_num := null;
323 l_service_request_rec.problem_code := null;
324 l_service_request_rec.exp_resolution_date := null;
325 l_service_request_rec.bill_to_site_use_id := l_bill_to_site_use_id;
326 l_service_request_rec.ship_to_site_use_id := l_ship_to_site_use_id;
327 l_service_request_rec.contract_id := null;
328 l_service_request_rec.account_id := p_bulk_receive_rec.cust_account_id;
329 l_service_request_rec.cust_po_number := null;
330 l_service_request_rec.cp_revision_id := null;
331 l_service_request_rec.sr_contact_point_id := null;
332 l_service_request_rec.party_id := null;
333 l_service_request_rec.contact_point_id := null;
334 l_service_request_rec.contact_point_type := null;
335 l_service_request_rec.primary_flag := null;
336 l_service_request_rec.contact_type := null;
337 l_service_request_rec.sr_creation_channel := 'PHONE';
338 l_service_request_rec.resource_type := FND_PROFILE.value('CS_SR_DEFAULT_OWNER_TYPE');
339
340
341 -- Call the Service Request API
342 CSD_PROCESS_PVT.process_service_request
343 ( p_api_version => 1.0,
344 p_commit => fnd_api.g_false,
345 p_init_msg_list => fnd_api.g_true,
346 p_validation_level => fnd_api.g_valid_level_full,
347 p_action => 'CREATE',
348 p_incident_id => NULL,
349 p_service_request_rec => l_service_request_rec,
350 p_notes_tbl => p_sr_notes_tbl,
351 x_incident_id => x_incident_id,
352 x_incident_number => x_incident_number,
353 x_return_status => x_return_status,
354 x_msg_count => x_msg_count,
355 x_msg_data => x_msg_data
356 );
357
358 If NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) then
359 RAISE FND_API.G_EXC_ERROR;
360 End If;
361
362 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
363 fnd_log.STRING (fnd_log.level_procedure,
364 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CREATE_BULKRCV_SR.END',
365 'Exit - Create Blkrcv SR');
366 End if;
367
368 EXCEPTION
369 When FND_API.G_EXC_ERROR then
370 Rollback To create_blkrcv_sr;
371 x_return_status := FND_API.G_RET_STS_ERROR ;
372 FND_MSG_PUB.Count_And_Get
373 (p_count => x_msg_count,
374 p_data => x_msg_data );
375
376 When FND_API.G_EXC_UNEXPECTED_ERROR then
377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
378 ROLLBACK TO create_blkrcv_sr;
379 FND_MSG_PUB.Count_And_Get
380 ( p_count => x_msg_count,
381 p_data => x_msg_data );
382
383 When OTHERS then
384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
385 Rollback To create_blkrcv_sr;
386 If FND_MSG_PUB.Check_Msg_Level
387 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)then
388 FND_MSG_PUB.Add_Exc_Msg
389 (G_PKG_NAME,
390 l_api_name );
391 End If;
392 FND_MSG_PUB.Count_And_Get
393 (p_count => x_msg_count,
394 p_data => x_msg_data );
395
396 END create_blkrcv_sr;
397
398
399 /*-----------------------------------------------------------------*/
400 /* procedure name: create_blkrcv_ro */
401 /* description : Procedure to create a Repair Order */
405 PROCEDURE create_blkrcv_ro
402 /* */
403 /*-----------------------------------------------------------------*/
404
406 (
407 p_bulk_receive_id IN NUMBER,
408 x_repair_line_id OUT NOCOPY NUMBER,
409 x_repair_number OUT NOCOPY VARCHAR2,
410 x_ro_status OUT NOCOPY VARCHAR2,
411 x_return_status OUT NOCOPY VARCHAR2,
412 x_msg_count OUT NOCOPY NUMBER,
413 x_msg_data OUT NOCOPY VARCHAR2
414
415 )
416 IS
417
418 -- Cursor to derive Bulk Receive record
419 Cursor c_create_blkrcv_ro(p_bulk_receive_id Number) IS
420 Select * from csd_bulk_receive_items_b
421 where bulk_receive_id = p_bulk_receive_id;
422
423 -- Cursor to derive item attributes
424 Cursor c_get_item_attributes(p_inventory_item_id number) IS
425 Select serial_number_control_code,
426 comms_nl_trackable_flag,
427 revision_qty_control_code
428 from mtl_system_items_kfv
429 where inventory_item_id = p_inventory_item_id
430 and organization_id = cs_std.get_item_valdn_orgzn_id;
431
432 -- Fix for bug#6082836
433 -- Added business_process_id
434 -- Cursor to derive repair type attribute
435 Cursor c_get_repair_type_attr(p_repair_type_id number) is
436 Select price_list_header_id,
437 repair_mode,
438 business_process_id
439 from csd_repair_types_b
440 where repair_type_id = p_repair_type_id;
441
442 -- Cursor to get IB details
443 Cursor c_get_ib_info ( p_inventory_item_id in Number,p_serial_number in Varchar2) is
444 Select
445 owner_party_id,
446 instance_id
447 from csi_item_instances
448 where serial_number = p_serial_number
449 and inventory_item_id = p_inventory_item_id;
450
451 -- Cursor to get Item Revision
452 Cursor c_get_item_revision ( p_inventory_item_id in Number,p_serial_number in Varchar2) is
453 Select
454 revision
455 from mtl_serial_numbers
456 where serial_number = p_serial_number
457 and inventory_item_id = p_inventory_item_id;
458
459 -- Cursor to get Party site use id
460 Cursor c_get_party_site_use_id (p_incident_id number )is
461 Select ship_to_site_use_id
462 from cs_incidents_all_b
463 where incident_id = p_incident_id;
464
465 -- Cursor to derive Primary UOM code
466 Cursor c_get_item_uom_code (p_inventory_item_id number) is
467 Select primary_uom_code
468 from mtl_system_items_kfv
469 where inventory_item_id = p_inventory_item_id
470 and organization_id = cs_std.get_item_valdn_orgzn_id;
471
472 -- Fix for bug#6082836
473 -- Cursor to get sr details
474 Cursor c_get_sr_details (p_incident_id in number) is
475 Select customer_id,account_id,incident_date,
476 incident_severity_id,contract_id,contract_service_id
477 from csd_incidents_v
478 where incident_id = p_incident_id;
479
480 Cursor c_get_install_site_use_id(p_instance_id in number) is
481 Select location_id
482 from csi_item_instances
483 where instance_id = p_instance_id;
484
485 l_ent_contracts OKS_ENTITLEMENTS_PUB.GET_CONTOP_TBL;
486 l_calc_resptime_flag Varchar2(1) := 'Y';
487 l_server_tz_id Number;
488 l_customer_id Number;
489 l_account_id Number;
490 l_incident_date Date;
491 l_severity_id Number;
492 l_sr_contract_id Number;
493 l_sr_contract_service_id Number;
494 l_contract_pl_id Number;
495 l_profile_pl_id Number;
496 l_install_site_use_id Number;
497 l_currency_code Varchar2(30);
498 l_business_process_id Number;
499
500 -- Local variables
501 l_api_name CONSTANT Varchar2(30) := 'CREATE_BLKRCV_RO';
502 l_api_version CONSTANT Number := 1.0;
503 l_repair_type_pl Number;
504 l_serial_number_control_code Number;
505 l_owner_party_id Number;
506 l_instance_id Number;
507 l_revision_qty_control_code Number;
508 l_install_base_flag Varchar2(1);
509 l_repln_rec csd_repairs_pub.repln_rec_type;
510 l_blkrcv_rec csd_bulk_receive_items_b%ROWTYPE;
511 c_non_serialized CONSTANT Number := 1;
512 l_instance_rec csd_mass_rcv_pvt.instance_rec_type;
513 l_revision Varchar2(30);
514 l_party_site_use_id Number;
515 l_repair_mode Varchar2(30);
516 l_repair_type_id Number;
517 l_uom_code Varchar2(3);
518 c_ib CONSTANT Varchar2(1) := 'Y';
519
520 BEGIN
521
522 savepoint create_blkrcv_ro;
523
524 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
525 fnd_log.STRING (fnd_log.level_procedure,
526 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CREATE_BULKRCV_RO.BEGIN',
527 'Enter - Create Blkrcv RO');
528 End if;
529
530 Open c_create_blkrcv_ro (p_bulk_receive_id);
531 Fetch c_create_blkrcv_ro into l_blkrcv_rec;
532 Close c_create_blkrcv_ro;
533
534 l_serial_number_control_code := null;
535 l_install_base_flag := null;
536 l_revision_qty_control_code := null;
537
538 Open c_get_item_attributes (l_blkrcv_rec.inventory_item_id);
542 Close c_get_item_attributes;
539 Fetch c_get_item_attributes into l_serial_number_control_code,
540 l_install_base_flag,
541 l_revision_qty_control_code;
543
544
545 -- If the item is Revision control
546 -- derive the Revision from the entered serial number
547 If ( l_revision_qty_control_code <> 1) then
548
549 l_revision := null;
550
551 Open c_get_item_revision(l_blkrcv_rec.inventory_item_id,
552 l_blkrcv_rec.serial_number);
553 Fetch c_get_item_revision into l_revision;
554 Close c_get_item_revision;
555
556 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
557 fnd_log.STRING (fnd_log.level_statement,
558 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CREATE_BULKRCV_RO',
559 'Derived Revision - '||l_revision||
560 'for Item id - '||l_blkrcv_rec.inventory_item_id);
561 End if;
562
563 End if;
564
565
566 -- Derive the IB Instance ID
567 -- for a IB item
568 If (l_blkrcv_rec.instance_id is null and l_install_base_flag = c_ib
569 and l_blkrcv_rec.serial_number is not null) then
570
571 l_owner_party_id := null;
572 l_instance_id := null;
573
574 Open c_get_ib_info(l_blkrcv_rec.inventory_item_id,
575 l_blkrcv_rec.serial_number);
576 Fetch c_get_ib_info into l_owner_party_id,
577 l_instance_id;
578 Close c_get_ib_info;
579
580 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
581 fnd_log.STRING (fnd_log.level_statement,
582 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CREATE_BULKRCV_RO',
583 'Derived Instance Id - '||l_instance_id||
584 'for Item id - '||l_blkrcv_rec.inventory_item_id||
585 ',Serial number - '||l_blkrcv_rec.serial_number);
586 End if;
587
588 Else
589 l_instance_id := l_blkrcv_rec.instance_id;
590 End if;
591
592
593 -- Derive the Primary UOM code if UOM is null
594 If ( l_blkrcv_rec.uom_code is null ) then
595
596 l_uom_code := null;
597
598 Open c_get_item_uom_code (l_blkrcv_rec.inventory_item_id);
599 Fetch c_get_item_uom_code into l_uom_code;
600 Close c_get_item_uom_code;
601
602 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
603 fnd_log.STRING (fnd_log.level_statement,
604 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CREATE_BULKRCV_RO',
605 'Derived Uom code - '||l_uom_code||
606 'for Item id - '||l_blkrcv_rec.inventory_item_id);
607 End if;
608
609 Else
610 l_uom_code := l_blkrcv_rec.uom_code;
611 End if;
612
613 -- If instance id is null then call create IB
614 -- for a IB item
615 If ( l_instance_id is null and l_install_base_flag = c_ib) then
616
617 l_party_site_use_id := null;
618
619 Open c_get_party_site_use_id(l_blkrcv_rec.incident_id);
620 Fetch c_get_party_site_use_id into l_party_site_use_id;
621 Close c_get_party_site_use_id;
622
623 l_instance_rec.inventory_item_id := l_blkrcv_rec.inventory_item_id;
624 l_instance_rec.instance_id := null;
625 l_instance_rec.instance_number := null;
626 l_instance_rec.serial_number := l_blkrcv_rec.serial_number;
627 l_instance_rec.lot_number := null;
628 l_instance_rec.quantity := 1;
629 l_instance_rec.uom := l_uom_code;
630 l_instance_rec.party_site_use_id := l_party_site_use_id;
631 l_instance_rec.party_id := l_blkrcv_rec.party_id;
632 l_instance_rec.account_id := l_blkrcv_rec.cust_account_id;
633 l_instance_rec.mfg_serial_number_flag := 'N';
634
635 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
636 fnd_log.STRING (fnd_log.level_event,
637 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CREATE_BULKRCV_RO',
638 'Calling create item instance api');
639 End if;
640
641
642 csd_mass_rcv_pvt.create_item_instance (
643 p_api_version => 1.0,
644 p_init_msg_list => fnd_api.g_false,
645 p_commit => fnd_api.g_false,
646 p_validation_level => fnd_api.g_valid_level_full,
647 x_return_status => x_return_status,
648 x_msg_count => x_msg_count,
649 x_msg_data => x_msg_data,
650 px_instance_rec => l_instance_rec,
651 x_instance_id => l_instance_id );
652
653 If NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) then
654 RAISE FND_API.G_EXC_ERROR;
655 End if;
656
657 End if;
658
659 -- swai: bug 7657379 use defaulting rules to get the repair type
660 --l_repair_type_id := fnd_profile.value('CSD_BLK_RCV_DEFAULT_REPAIR_TYPE');
661 l_repair_type_id := get_bulk_rcv_def_repair_type (
662 p_incident_id => l_blkrcv_rec.incident_id,
663 p_ro_inventory_item_id => l_blkrcv_rec.inventory_item_id);
664
665 l_repair_type_pl := null;
666 l_repair_mode := null;
667
671
668 Open c_get_repair_type_attr(l_repair_type_id);
669 Fetch c_get_repair_type_attr into l_repair_type_pl,l_repair_mode,l_business_process_id;
670 Close c_get_repair_type_attr;
672 -- Fix for bug#6082836
673 -- Derive the Currency code
674 --If ( l_repair_type_pl is null ) then
675 -- l_repln_rec.currency_code := CSD_CHARGE_LINE_UTIL.GET_PLCURRCODE(fnd_profile.value('CSD_DEFAULT_PRICE_LIST'));
676 --Else
677 -- l_repln_rec.currency_code := CSD_CHARGE_LINE_UTIL.GET_PLCURRCODE(l_repair_type_pl);
678 --End if;
679
680 -- Fix for bug#6082836
681 -- Default Contract, Price list and Currency
682
683 Open c_get_sr_details(l_blkrcv_rec.incident_id);
684 Fetch c_get_sr_details into l_customer_id,l_account_id,l_incident_date,
685 l_severity_id,l_sr_contract_id,l_sr_contract_service_id;
686 Close c_get_sr_details;
687
688 Open c_get_install_site_use_id(l_instance_id);
689 Fetch c_get_install_site_use_id into l_install_site_use_id;
690 Close c_get_install_site_use_id;
691
692 fnd_profile.get('SERVER_TIMEZONE_ID', l_server_tz_id);
693
694 CSD_REPAIRS_UTIL.GET_ENTITLEMENTS(
695 p_api_version_number => 1.0,
696 p_init_msg_list => fnd_api.g_false,
697 p_commit => fnd_api.g_false,
698 p_contract_number => null,
699 p_service_line_id => null,
700 p_customer_id => l_customer_id,
701 p_site_id => l_install_site_use_id,
702 p_customer_account_id => l_account_id,
703 p_system_id => null,
704 p_inventory_item_id => l_blkrcv_rec.inventory_item_id,
705 p_customer_product_id => l_instance_id,
706 p_request_date => trunc(l_incident_date),
707 p_validate_flag => 'Y',
708 p_business_process_id => l_business_process_id,
709 p_severity_id => l_severity_id,
710 p_time_zone_id => l_server_tz_id,
711 P_CALC_RESPTIME_FLAG => l_calc_resptime_flag,
712 x_ent_contracts => l_ent_contracts,
713 x_return_status => x_return_status,
714 x_msg_count => x_msg_count,
715 x_msg_data => x_msg_data);
716
717 If NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) then
718 RAISE FND_API.G_EXC_ERROR;
719 End if;
720
721 If (l_ent_contracts.count = 0 ) then
722
723 l_repln_rec.contract_line_id := null;
724
725 Else
726
727 For l_index in l_ent_contracts.FIRST..l_Ent_contracts.LAST Loop
728 if (l_sr_contract_id = l_ent_contracts(l_index).contract_id and
729 l_sr_contract_service_id = l_ent_contracts(l_index).service_line_id) then
730
731 l_repln_rec.contract_line_id := l_ent_contracts(l_index).service_line_id;
732 exit;
733
734 end if;
735 End Loop;
736
737 If (l_repln_rec.contract_line_id is null or
738 l_repln_rec.contract_line_id = fnd_api.g_miss_num) then
739 l_repln_rec.contract_line_id := l_ent_contracts(1).service_line_id;
740 End if;
741
742 End if;
743
744 --
745 -- Default PL and Currency
746 --
747 csd_process_util.get_ro_default_curr_pl
748 ( p_api_version => 1.0,
749 p_init_msg_list => fnd_api.g_false,
750 p_incident_id => l_blkrcv_rec.incident_id,
751 p_repair_type_id => l_repair_type_id,
752 p_ro_contract_line_id => l_repln_rec.contract_line_id,
753 x_contract_pl_id => l_contract_pl_id,
754 x_profile_pl_id => l_profile_pl_id,
755 x_currency_code => l_currency_code,
756 x_return_status => x_return_status,
757 x_msg_count => x_msg_count,
758 x_msg_data => x_msg_data );
759
760 If NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) then
761 RAISE FND_API.G_EXC_ERROR;
762 End if;
763
764 If ( l_contract_pl_id is not null) then
765 l_repln_rec.price_list_header_id := l_contract_pl_id;
766 Elsif ( l_profile_pl_id is not null ) then
767 l_repln_rec.price_list_header_id := l_profile_pl_id;
768 End if;
769
770 l_repln_rec.currency_code := l_currency_code;
771
772 -- Set the Repair Order Status
773 If ((l_blkrcv_rec.serial_number is null) and (l_serial_number_control_code <> c_non_serialized)
774 and (l_blkrcv_rec.quantity > 1)) then
775 l_repln_rec.status := 'D';
776 x_ro_status := 'DRAFT';
777 Else
778 l_repln_rec.status := 'O';
779 x_ro_status := 'OPEN';
780 End if;
781
782 --
783 -- Inventory org id
784 --
785 -- swai: bug 7657379 - use defaulting rules to get the inventory org
786 -- l_repln_rec.inventory_org_id := fnd_profile.value('CSD_DEF_REP_INV_ORG');
787 l_repln_rec.inventory_org_id := fnd_api.g_miss_num;
788
789 --
790 -- Initialize / Assign the values to Repair Rec type
791 --
792 l_repln_rec.incident_id := l_blkrcv_rec.incident_id;
793 l_repln_rec.inventory_item_id := l_blkrcv_rec.inventory_item_id;
794 l_repln_rec.customer_product_id := l_instance_id;
798 l_repln_rec.auto_process_rma := 'Y';
795 l_repln_rec.unit_of_measure := l_uom_code;
796 l_repln_rec.serial_number := l_blkrcv_rec.serial_number;
797 l_repln_rec.quantity := l_blkrcv_rec.quantity;
799 l_repln_rec.approval_required_flag := 'Y';
800 l_repln_rec.repair_type_id := l_repair_type_id; -- swai: bug 7657379
801 -- l_repln_rec.repair_type_id := fnd_profile.value('CSD_BLK_RCV_DEFAULT_REPAIR_TYPE');
802 l_repln_rec.repair_group_id := null;
803 l_repln_rec.item_revision := l_revision;
804 l_repln_rec.repair_mode := l_repair_mode;
805
806 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
807 fnd_log.STRING (fnd_log.level_event,
808 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CREATE_BULKRCV_RO',
809 'Calling create repair order api');
810 End if;
811
812
813 -- Call the Repairs private API
814 CSD_REPAIRS_PVT.Create_Repair_Order
815 (p_api_version_number => 1.0,
816 p_commit => fnd_api.g_false,
817 p_init_msg_list => fnd_api.g_true,
818 p_validation_level => fnd_api.g_valid_level_full,
819 p_repair_line_id => x_repair_line_id,
820 p_Repln_Rec => l_repln_rec,
821 x_repair_line_id => x_repair_line_id,
822 x_repair_number => x_repair_number,
823 x_return_status => x_return_status,
824 x_msg_count => x_msg_count,
825 x_msg_data => x_msg_data
826 );
827
828 If NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) then
829 RAISE FND_API.G_EXC_ERROR;
830 End If;
831
832 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
833 fnd_log.STRING (fnd_log.level_procedure,
834 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CREATE_BULKRCV_RO.END',
835 'Exit - Create Blkrcv RO');
836 End if;
837
838 EXCEPTION
839 When FND_API.G_EXC_ERROR then
840 Rollback To create_blkrcv_ro;
841 x_return_status := FND_API.G_RET_STS_ERROR ;
842 FND_MSG_PUB.Count_And_Get
843 (p_count => x_msg_count,
844 p_data => x_msg_data );
845
846 When FND_API.G_EXC_UNEXPECTED_ERROR then
847 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
848 ROLLBACK TO create_blkrcv_ro;
849 FND_MSG_PUB.Count_And_Get
850 ( p_count => x_msg_count,
851 p_data => x_msg_data );
852
853 When OTHERS then
854 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
855 Rollback To create_blkrcv_ro;
856 If FND_MSG_PUB.Check_Msg_Level
857 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)then
858 FND_MSG_PUB.Add_Exc_Msg
859 (G_PKG_NAME,
860 l_api_name );
861 End If;
862 FND_MSG_PUB.Count_And_Get
863 (p_count => x_msg_count,
864 p_data => x_msg_data );
865
866 END create_blkrcv_ro;
867
868
869 /*-----------------------------------------------------------------*/
870 /* procedure name: create_blkrcv_default_prod_txn */
871 /* description : Procedure to create Default product txn */
872 /* for a Repair Order */
873 /* */
874 /*-----------------------------------------------------------------*/
875
876 PROCEDURE create_blkrcv_default_prod_txn
877 (
878 p_bulk_receive_id IN NUMBER,
879 x_return_status OUT NOCOPY VARCHAR2,
880 x_msg_count OUT NOCOPY NUMBER,
881 x_msg_data OUT NOCOPY VARCHAR2
882 )
883 IS
884
885 -- Cursor to derive Bulk Receive record
886 Cursor c_create_blkrcv_prod_txn(p_bulk_receive_id Number) IS
887 select * from csd_bulk_receive_items_b
888 where bulk_receive_id = p_bulk_receive_id;
889
890 -- Local variables
891 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_BLKRCV_DEFAULT_PROD_TXN';
892 l_api_version CONSTANT NUMBER := 1.0;
893 l_blkrcv_rec csd_bulk_receive_items_b%ROWTYPE;
894
895 BEGIN
896
897 savepoint create_blkrcv_default_prod_txn;
898
899 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
900 fnd_log.STRING (fnd_log.level_procedure,
901 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CREATE_BULKRCV_DEFAULT_PROD_TXN.BEGIN',
902 'Enter - Create Blkrcv Default Prod Txn');
903 End if;
904
905 Open c_create_blkrcv_prod_txn(p_bulk_receive_id );
906 Fetch c_create_blkrcv_prod_txn into l_blkrcv_rec;
907 Close c_create_blkrcv_prod_txn;
908
909 -- Call the Create default prod txn api
910 csd_process_pvt.create_default_prod_txn
911 (p_api_version => 1.0,
912 p_commit => fnd_api.g_false,
913 p_init_msg_list => fnd_api.g_true,
914 p_validation_level => fnd_api.g_valid_level_full,
915 p_repair_line_id => l_blkrcv_rec.repair_line_id,
916 x_return_status => x_return_status,
917 x_msg_count => x_msg_count,
918 x_msg_data => x_msg_data);
919
920 If NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) then
921 RAISE FND_API.G_EXC_ERROR;
922 End If;
923
924 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
928 End if;
925 fnd_log.STRING (fnd_log.level_procedure,
926 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CREATE_BULKRCV_DEFAULT_PROD_TXN.END',
927 'Exit - Create Blkrcv Default Prod Txn');
929
930 EXCEPTION
931 When FND_API.G_EXC_ERROR then
932 Rollback To create_blkrcv_default_prod_txn;
933 x_return_status := FND_API.G_RET_STS_ERROR ;
934 FND_MSG_PUB.Count_And_Get
935 (p_count => x_msg_count,
936 p_data => x_msg_data );
937
938 When FND_API.G_EXC_UNEXPECTED_ERROR then
939 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
940 ROLLBACK TO create_blkrcv_default_prod_txn;
941 FND_MSG_PUB.Count_And_Get
942 ( p_count => x_msg_count,
943 p_data => x_msg_data );
944
945 When OTHERS then
946 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
947 Rollback To create_blkrcv_default_prod_txn;
948 If FND_MSG_PUB.Check_Msg_Level
949 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)then
950 FND_MSG_PUB.Add_Exc_Msg
951 (G_PKG_NAME,
952 l_api_name );
953 End If;
954 FND_MSG_PUB.Count_And_Get
955 (p_count => x_msg_count,
956 p_data => x_msg_data );
957
958 END create_blkrcv_default_prod_txn;
959
960
961 /*-----------------------------------------------------------------*/
962 /* procedure name: change_blkrcv_ib_owner */
963 /* description : Procedure to Change the Install Base Owner */
964 /* */
965 /*-----------------------------------------------------------------*/
966
967 PROCEDURE change_blkrcv_ib_owner
968 (
969 p_bulk_receive_id IN NUMBER,
970 x_return_status OUT NOCOPY VARCHAR2,
971 x_msg_count OUT NOCOPY NUMBER,
972 x_msg_data OUT NOCOPY VARCHAR2
973 )
974 IS
975
976 -- Local variables
977 l_instance_rec csi_datastructures_pub.instance_rec;
978 l_ext_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
979 l_party_tbl csi_datastructures_pub.party_tbl;
980 l_account_tbl csi_datastructures_pub.party_account_tbl;
981 l_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
982 l_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
983 l_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
984 l_txn_rec csi_datastructures_pub.transaction_rec;
985 x_instance_id_lst csi_datastructures_pub.id_tbl;
986 l_instance_party_id Number;
987 l_object_version_number Number;
988 l_api_name CONSTANT Varchar(30) := 'CHANGE_BLKRCV_IB_OWNER';
989 l_api_version CONSTANT Number := 1.0;
990 l_blkrcv_rec csd_bulk_receive_items_b%ROWTYPE;
991 l_instance_account_id Number;
992 l_inst_party_obj_ver_num Number;
993 l_inst_acct_obj_ver_num Number;
994 l_instance_id Number;
995
996 -- Cursor to select the Instance party id
997 Cursor c_instance_party(p_instance_id number) IS
998 Select instance_party_id,
999 object_version_number
1000 from csi_i_parties
1001 where instance_id = p_instance_id
1002 and relationship_type_code = 'OWNER';
1003
1004 -- Cursor to derive the Instance details
1005 Cursor c_instance_details(p_instance_id number) IS
1006 Select object_version_number from csi_item_instances
1007 where instance_id = p_instance_id;
1008
1009 -- Cursor to derive the Bulk Receive rec
1010 Cursor c_bulk_receive_items(p_bulk_receive_id Number) IS
1011 select * from csd_bulk_receive_items_b
1012 where bulk_receive_id = p_bulk_receive_id;
1013
1014 -- Cursor to derive the Instance Account Id
1015 Cursor c_instance_account(p_instance_party_id number) is
1016 Select ip_account_id,
1017 object_version_number
1018 from csi_ip_accounts
1019 where instance_party_id = p_instance_party_id;
1020
1021 -- Cursor to get IB details
1022 Cursor c_get_ib_info ( p_inventory_item_id in Number,p_serial_number in Varchar2) is
1023 Select instance_id
1024 from csi_item_instances
1025 where serial_number = p_serial_number
1026 and inventory_item_id = p_inventory_item_id;
1027
1028
1029 BEGIN
1030
1031 savepoint change_blkrcv_ib_owner;
1032
1033 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1034 fnd_log.STRING (fnd_log.level_procedure,
1035 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CHANGE_BULKRCV_IB_OWNER.BEGIN',
1036 'Enter - Change Blkrcv IB Owner');
1037 End if;
1038
1039 Open c_bulk_receive_items(p_bulk_receive_id);
1040 Fetch c_bulk_receive_items into l_blkrcv_rec;
1041 Close c_bulk_receive_items;
1042
1043 -- Derive the IB Instance ID
1044 -- for a IB item
1045 If (l_blkrcv_rec.instance_id is null
1046 and l_blkrcv_rec.serial_number is not null) then
1047
1048 l_instance_id := null;
1049
1050 Open c_get_ib_info(l_blkrcv_rec.inventory_item_id,
1051 l_blkrcv_rec.serial_number);
1052 Fetch c_get_ib_info into l_instance_id;
1053 Close c_get_ib_info;
1054 Else
1055 l_instance_id := l_blkrcv_rec.instance_id;
1056 End if;
1057
1058 l_instance_party_id := null;
1059 l_inst_party_obj_ver_num := null;
1060
1064 Close c_instance_party;
1061 Open c_instance_party(l_instance_id);
1062 Fetch c_instance_party into l_instance_party_id,
1063 l_inst_party_obj_ver_num;
1065
1066 l_instance_account_id := null;
1067 l_inst_acct_obj_ver_num := null;
1068
1069 Open c_instance_account(l_instance_party_id);
1070 Fetch c_instance_account into l_instance_account_id,
1071 l_inst_acct_obj_ver_num;
1072 Close c_instance_account;
1073
1074 l_object_version_number := null;
1075
1076 Open c_instance_details(l_instance_id);
1077 Fetch c_instance_details into l_object_version_number;
1078 Close c_instance_details;
1079
1080
1081 -- Assign / Initialize values to the IB Rec type
1082 l_instance_rec.instance_id := l_instance_id;
1083 l_instance_rec.object_version_number := l_object_version_number;
1084
1085 l_party_tbl(1).instance_party_id := l_instance_party_id;
1086 l_party_tbl(1).instance_id := l_instance_id;
1087 l_party_tbl(1).party_source_table := 'HZ_PARTIES';
1088 l_party_tbl(1).party_id := l_blkrcv_rec.orig_party_id;
1089 l_party_tbl(1).relationship_type_code := 'OWNER';
1090 l_party_tbl(1).contact_flag := 'N';
1091 l_party_tbl(1).object_version_number := l_inst_party_obj_ver_num;
1092
1093 l_account_tbl(1).ip_account_id := l_instance_account_id;
1094 l_account_tbl(1).parent_tbl_index := 1;
1095 l_account_tbl(1).instance_party_id := l_instance_party_id;
1096 l_account_tbl(1).party_account_id := l_blkrcv_rec.orig_cust_account_id;
1097 l_account_tbl(1).relationship_type_code := 'OWNER';
1098 l_account_tbl(1).object_version_number := l_inst_acct_obj_ver_num;
1099
1100 l_txn_rec.transaction_date := sysdate;
1101 l_txn_rec.source_transaction_date := sysdate;
1102 l_txn_rec.transaction_type_id := 1;
1103
1104 -- Call the Update item instance API
1105 csi_item_instance_pub.update_item_instance
1106 (
1107 p_api_version => 1.0,
1108 p_commit => fnd_api.g_false,
1109 p_init_msg_list => fnd_api.g_true,
1110 p_validation_level => fnd_api.g_valid_level_full,
1111 p_instance_rec => l_instance_rec,
1112 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
1113 p_party_tbl => l_party_tbl,
1114 p_account_tbl => l_account_tbl,
1115 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
1116 p_org_assignments_tbl => l_org_assignments_tbl,
1117 p_asset_assignment_tbl => l_asset_assignment_tbl,
1118 p_txn_rec => l_txn_rec,
1119 x_instance_id_lst => x_instance_id_lst,
1120 x_return_status => x_return_status,
1121 x_msg_count => x_msg_count,
1122 x_msg_data => x_msg_data
1123 );
1124
1125 If NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) then
1126 RAISE FND_API.G_EXC_ERROR;
1127 End If;
1128
1129 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1130 fnd_log.STRING (fnd_log.level_procedure,
1131 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CHANGE_BULKRCV_IB_OWNER.END',
1132 'Exit - Change Blkrcv IB Owner');
1133 End if;
1134
1135 EXCEPTION
1136 When FND_API.G_EXC_ERROR then
1137 Rollback To change_blkrcv_ib_owner;
1138 x_return_status := FND_API.G_RET_STS_ERROR ;
1139 FND_MSG_PUB.Count_And_Get
1140 (p_count => x_msg_count,
1141 p_data => x_msg_data );
1142
1143 When FND_API.G_EXC_UNEXPECTED_ERROR then
1144 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1145 ROLLBACK TO change_blkrcv_ib_owner;
1146 FND_MSG_PUB.Count_And_Get
1147 ( p_count => x_msg_count,
1148 p_data => x_msg_data );
1149
1150 When OTHERS then
1151 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1152 Rollback To change_blkrcv_ib_owner;
1153 If FND_MSG_PUB.Check_Msg_Level
1154 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)then
1155 FND_MSG_PUB.Add_Exc_Msg
1156 (G_PKG_NAME,
1157 l_api_name );
1158 End If;
1159 FND_MSG_PUB.Count_And_Get
1160 (p_count => x_msg_count,
1161 p_data => x_msg_data );
1162
1163 END;
1164
1165
1166 /*-----------------------------------------------------------------*/
1167 /* procedure name: bulk_auto_receive */
1168 /* description : Procedure to Auto Receive */
1169 /* */
1170 /*-----------------------------------------------------------------*/
1171 PROCEDURE bulk_auto_receive
1172 (
1173 p_bulk_autorcv_tbl IN OUT NOCOPY csd_bulk_receive_util.bulk_autorcv_tbl,
1174 x_return_status OUT NOCOPY VARCHAR2,
1175 x_msg_count OUT NOCOPY NUMBER,
1176 x_msg_data OUT NOCOPY VARCHAR2
1177 )
1178
1179 IS
1180
1181 -- Local variables
1182 l_msg_count Number;
1183 l_rcv_rec_tbl csd_receive_util.rcv_tbl_type;
1184 l_msg_data Varchar2(2000);
1185 i Number;
1186 l_org_id Number;
1187 l_api_name CONSTANT Varchar(30) := 'BULK_AUTO_RECEIVE';
1188 l_api_version CONSTANT Number := 1.0;
1189 l_header_error Boolean;
1190 l_errored Boolean;
1194 l_account_id Number;
1191 l_rcv_error_msg_tbl csd_receive_util.rcv_error_msg_tbl;
1192 l_item_name Varchar2(40);
1193 l_customer_id Number;
1195 l_estimate_quantity Number;
1196 l_unit_of_measure Varchar2(3);
1197 l_inventory_item_id Number;
1198 l_order_header_id Number;
1199 l_order_line_id Number;
1200 l_order_number Number;
1201 l_serial_number Varchar2(40);
1202 l_return_status Varchar2(3);
1203 l_prod_txn_status Varchar2(30);
1204
1205 Cursor c_ro_prodtxn(p_repair_line_id number,
1206 p_order_header_id number,
1207 p_order_line_id number) is
1208 select
1209 cib.customer_id,
1210 cib.account_id, -- Fix for bug#5848406
1211 cpt.estimate_quantity,
1212 cpt.unit_of_measure,
1213 cpt.inventory_item_id,
1214 cpt.order_header_id,
1215 cpt.order_line_id,
1216 cpt.order_number,
1217 cpt.serial_number,
1218 mtl.concatenated_segments item_name
1219 from
1220 csd_product_txns_v cpt,
1221 cs_incidents_all_b cib,
1222 csd_repairs cr,
1223 mtl_system_items_kfv mtl
1224 where cpt.repair_line_id = p_repair_line_id
1225 and cr.repair_line_id = cpt.repair_line_id
1226 and cib.incident_id = cr.incident_id
1227 and cpt.order_header_id = p_order_header_id
1228 and cpt.order_line_id = p_order_line_id
1229 and mtl.inventory_item_id = cpt.inventory_item_id
1230 and mtl.organization_id = cs_std.get_item_valdn_orgzn_id;
1231
1232 Cursor c_get_org (p_order_header_id number) is
1233 Select nvl(b.ship_from_org_id,a.ship_from_org_id)
1234 from oe_order_headers_all a,
1235 oe_order_lines_all b
1236 where a.header_id = b.header_id
1237 and a.header_id = p_order_header_id;
1238
1239 Cursor c_get_prod_txn_status ( p_repair_line_id number ) is
1240 Select prod_txn_status
1241 from csd_product_transactions
1242 where repair_line_id = p_repair_line_id
1243 and action_type = 'RMA';
1244
1245 BEGIN
1246
1247 Savepoint bulk_auto_receive;
1248
1249 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1250 fnd_log.STRING (fnd_log.level_procedure,
1251 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.BULK_AUTO_RECEIVE.BEGIN',
1252 'Enter - Bulk auto receive');
1253 End if;
1254
1255 i := 0;
1256
1257 For l_tbl_id in 1..p_bulk_autorcv_tbl.count
1258 Loop
1259
1260 -- Assign values to the Auto Receive rec table
1261
1262 For c_ro_prdtxn_rec in c_ro_prodtxn( p_bulk_autorcv_tbl(l_tbl_id).repair_line_id,
1263 p_bulk_autorcv_tbl(l_tbl_id).order_header_id,
1264 p_bulk_autorcv_tbl(l_tbl_id).order_line_id)
1265 Loop
1266
1267 -- Derive the Org id
1268 l_org_id := null;
1269
1270 Open c_get_org (c_ro_prdtxn_rec.order_header_id);
1271 Fetch c_get_org into l_org_id;
1272 Close c_get_org;
1273
1274 i := i + 1;
1275 -- l_rcv_rec_tbl(i).customer_id := c_ro_prdtxn_rec.customer_id;
1276 -- Fix for bug#5848406
1277 l_rcv_rec_tbl(i).customer_id := c_ro_prdtxn_rec.account_id;
1278 l_rcv_rec_tbl(i).customer_site_id := null;
1279 l_rcv_rec_tbl(i).employee_id := null;
1280 l_rcv_rec_tbl(i).quantity := abs(c_ro_prdtxn_rec.estimate_quantity);
1281 l_rcv_rec_tbl(i).uom_code := c_ro_prdtxn_rec.unit_of_measure;
1282 l_rcv_rec_tbl(i).inventory_item_id := c_ro_prdtxn_rec.inventory_item_id;
1283 l_rcv_rec_tbl(i).item_revision := null;
1284 l_rcv_rec_tbl(i).to_organization_id := l_org_id;
1285 l_rcv_rec_tbl(i).destination_type_code := null;
1286 -- swai: bug 7663674
1287 --l_rcv_rec_tbl(i).subinventory := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SUB_INV');
1288 l_rcv_rec_tbl(i).subinventory := get_bulk_rcv_def_sub_inv( p_bulk_autorcv_tbl(l_tbl_id).repair_line_id);
1289 l_rcv_rec_tbl(i).locator_id := null;
1290 l_rcv_rec_tbl(i).deliver_to_location_id := null;
1291 l_rcv_rec_tbl(i).requisition_number := null;
1292 l_rcv_rec_tbl(i).order_header_id := c_ro_prdtxn_rec.order_header_id;
1293 l_rcv_rec_tbl(i).order_line_id := c_ro_prdtxn_rec.order_line_id;
1294 l_rcv_rec_tbl(i).order_number := c_ro_prdtxn_rec.order_number;
1295 l_rcv_rec_tbl(i).doc_number := c_ro_prdtxn_rec.order_number;
1296 l_rcv_rec_tbl(i).internal_order_flag := 'N';
1297 l_rcv_rec_tbl(i).from_organization_id := null;
1298 l_rcv_rec_tbl(i).expected_receipt_date := sysdate;
1299 l_rcv_rec_tbl(i).transaction_date := sysdate;
1300 l_rcv_rec_tbl(i).ship_to_location_id := null;
1301 l_rcv_rec_tbl(i).serial_number := c_ro_prdtxn_rec.serial_number;
1302
1303 End Loop;
1304
1305 End Loop;
1306
1307 -- Call the Receive API
1308 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
1309 fnd_log.STRING (fnd_log.level_event,
1310 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.BULK_AUTO_RECEIVE.BEGIN',
1311 'Call receive item api');
1312 End if;
1313
1314 If(l_rcv_rec_tbl.count > 0 ) then
1315
1316 csd_receive_pvt.receive_item ( p_api_version => 1.0,
1320 x_return_status => x_return_status,
1317 p_init_msg_list => csd_process_util.g_false,
1318 p_commit => csd_process_util.g_false,
1319 p_validation_level => csd_process_util.g_valid_level_full,
1321 x_msg_count => l_msg_count,
1322 x_msg_data => l_msg_data,
1323 x_rcv_error_msg_tbl => l_rcv_error_msg_tbl,
1324 p_receive_tbl => l_rcv_rec_tbl);
1325
1326
1327
1328 If NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) then -- Status check If statement
1329
1330 -- Verify if there are any errors in header
1331
1332 csd_bulk_receive_util.write_to_conc_log
1333 ( p_msg_count => l_msg_count,
1334 p_msg_data => l_msg_data);
1335
1336
1337 If ( l_rcv_error_msg_tbl.count > 0 ) then
1338
1339 l_header_error := FALSE;
1340
1341 For i in 1..l_rcv_error_msg_tbl.count
1342 Loop
1343 If ( l_rcv_error_msg_tbl(i).header_interface_id is not null and
1344 l_rcv_error_msg_tbl(i).interface_transaction_id is null ) then
1345
1346 l_header_error := TRUE;
1347
1348 -- Display the message
1349 Fnd_file.put_line(fnd_file.log,'Error:Auto Receive failed - Header');
1350 Fnd_file.put(fnd_file.log,'Column name:');
1351 Fnd_file.put_line(fnd_file.log,l_rcv_error_msg_tbl(i).column_name);
1352 Fnd_file.put(fnd_file.log,'Error Message:');
1353 Fnd_file.put_line(fnd_file.log,l_rcv_error_msg_tbl(i).error_message);
1354
1355 End if;
1356 End Loop;
1357
1358 -- If there is header error the update all the Auto Receive lines
1359 -- in Bulk Rcv table to Errored
1360 If (l_header_error) then
1361
1362 -- Update all the auto receive records to error
1363 For i in 1..p_bulk_autorcv_tbl.count
1364 Loop
1365
1366 Update csd_bulk_receive_items_b
1367 set status = 'ERRORED'
1368 where bulk_receive_id = p_bulk_autorcv_tbl(i).bulk_receive_id;
1369
1370 End loop;
1371
1372 Else
1373
1374 -- If there are no header errors then check for
1375 -- line errors and update the records
1376 For i in 1..p_bulk_autorcv_tbl.count
1377 Loop
1378
1379 l_errored := FALSE;
1380
1381 For j in 1..l_rcv_error_msg_tbl.count
1382 Loop
1383
1384 If ( p_bulk_autorcv_tbl(i).order_header_id = l_rcv_error_msg_tbl(j).order_header_id and
1385 p_bulk_autorcv_tbl(i).order_line_id = l_rcv_error_msg_tbl(j).order_line_id ) then
1386
1387 l_errored := TRUE;
1388
1389 -- Display the error message
1390
1391 l_customer_id := null;
1392 l_estimate_quantity := null;
1393 l_unit_of_measure := null;
1394 l_inventory_item_id := null;
1395 l_order_header_id := null;
1396 l_order_line_id := null;
1397 l_order_number := null;
1398 l_serial_number := null;
1399 l_item_name := null;
1400
1401 Open c_ro_prodtxn(p_bulk_autorcv_tbl(i).repair_line_id,
1402 p_bulk_autorcv_tbl(i).order_header_id,
1403 p_bulk_autorcv_tbl(i).order_line_id);
1404
1405 Fetch c_ro_prodtxn into l_customer_id,l_account_id,l_estimate_quantity,
1406 l_unit_of_measure,l_inventory_item_id,l_order_header_id,
1407 l_order_line_id,l_order_number,l_serial_number,l_item_name;
1408 Close c_ro_prodtxn;
1409
1410 Fnd_file.put_line(fnd_file.log,'Error:Auto Receive failed - Line');
1411 Fnd_file.put(fnd_file.log,'Serial Number :'||l_serial_number||',');
1412 Fnd_file.put(fnd_file.log,'Inventory Item :'||l_item_name||',');
1413 Fnd_file.put_line(fnd_file.log,'Qty :'||l_estimate_quantity);
1414 Fnd_file.put(fnd_file.log,'Column name:');
1415 Fnd_file.put_line(fnd_file.log,l_rcv_error_msg_tbl(i).column_name);
1416 Fnd_file.put(fnd_file.log,'Error Message:');
1417 Fnd_file.put_line(fnd_file.log,l_rcv_error_msg_tbl(i).error_message);
1418
1419 End If;
1420
1421 End Loop;
1422
1423 If (l_errored) then
1424
1425 Update csd_bulk_receive_items_b
1426 set status = 'ERRORED'
1427 where bulk_receive_id = p_bulk_autorcv_tbl(i).bulk_receive_id;
1428
1429 Else
1430
1431 -- fix for bug 5227347
1432 -- Update csd_bulk_receive_items_b
1433 -- set status = 'PROCESSED'
1434 -- where bulk_receive_id = p_bulk_autorcv_tbl(i).bulk_receive_id;
1435
1436 -- Call Update receipts program
1437 CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE
1438 ( p_api_version => 1.0,
1442 x_return_status => l_return_status,
1439 p_commit => fnd_api.g_false,
1440 p_init_msg_list => fnd_api.g_true,
1441 p_validation_level => 0,
1443 x_msg_count => l_msg_count,
1444 x_msg_data => l_msg_data,
1445 p_internal_order_flag => 'N',
1446 p_order_header_id => null,
1447 p_repair_line_id => p_bulk_autorcv_tbl(i).repair_line_id);
1448
1449 -- fix for bug 5227347
1450 If NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) then
1451
1452 Fnd_file.put_line(fnd_file.log,'Error : CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE failed');
1453 csd_bulk_receive_util.write_to_conc_log
1454 ( p_msg_count => l_msg_count,
1455 p_msg_data => l_msg_data);
1456
1457 Else
1458
1459 -- Get Product Txn Status
1460 Open c_get_prod_txn_status ( p_bulk_autorcv_tbl(i).repair_line_id );
1461 Fetch c_get_prod_txn_status into l_prod_txn_status;
1462 Close c_get_prod_txn_status;
1463
1464 If ( l_prod_txn_status = 'RECEIVED' ) then
1465
1466 Update csd_bulk_receive_items_b
1467 set status = 'PROCESSED'
1468 where bulk_receive_id = p_bulk_autorcv_tbl(i).bulk_receive_id;
1469
1470 End if;
1471
1472 End if;
1473
1474 End if;
1475 End Loop;
1476
1477 End if; -- End if of l_header_error
1478
1479 Else
1480 -- Unexpected/Internal Error
1481 For i in 1..p_bulk_autorcv_tbl.count
1482 Loop
1483
1484 Update csd_bulk_receive_items_b
1485 set status = 'ERRORED'
1486 where bulk_receive_id = p_bulk_autorcv_tbl(i).bulk_receive_id;
1487
1488 End loop;
1489
1490 End if; -- End if of the l_rcv_error_msg_tbl.count > 0
1491
1492 Else
1493
1494 -- Update all the auto receive records to processed
1495 For i in 1..p_bulk_autorcv_tbl.count
1496 Loop
1497
1498 -- fix for bug 5227347
1499 -- Update csd_bulk_receive_items_b
1500 -- set status = 'PROCESSED'
1501 -- where bulk_receive_id = p_bulk_autorcv_tbl(i).bulk_receive_id;
1502
1503
1504 -- Call Update receipts program
1505 CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE
1506 ( p_api_version => 1.0,
1507 p_commit => fnd_api.g_false,
1508 p_init_msg_list => fnd_api.g_true,
1509 p_validation_level => 0,
1510 x_return_status => l_return_status,
1511 x_msg_count => l_msg_count,
1512 x_msg_data => l_msg_data,
1513 p_internal_order_flag => 'N',
1514 p_order_header_id => null,
1515 p_repair_line_id => p_bulk_autorcv_tbl(i).repair_line_id);
1516
1517 -- fix for bug 5227347
1518 If NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) then
1519
1520 Fnd_file.put_line(fnd_file.log,'Error : CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE failed');
1521 csd_bulk_receive_util.write_to_conc_log
1522 ( p_msg_count => l_msg_count,
1523 p_msg_data => l_msg_data);
1524
1525 Else
1526
1527 -- Get Product Txn Status
1528 Open c_get_prod_txn_status ( p_bulk_autorcv_tbl(i).repair_line_id );
1529 Fetch c_get_prod_txn_status into l_prod_txn_status;
1530 Close c_get_prod_txn_status;
1531
1532 If ( l_prod_txn_status = 'RECEIVED' ) then
1533
1534 Update csd_bulk_receive_items_b
1535 set status = 'PROCESSED'
1536 where bulk_receive_id = p_bulk_autorcv_tbl(i).bulk_receive_id;
1537
1538 End if;
1539
1540 End if;
1541
1542 End loop;
1543
1544 End if; -- End if of the Status check
1545
1546 End if; -- End if of the l_rcv_rec_tbl.count > 0
1547
1548 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1549 fnd_log.STRING (fnd_log.level_procedure,
1550 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.BULK_AUTO_RECEIVE.END',
1551 'Exit - Bulk auto receive');
1552 End if;
1553
1554 EXCEPTION
1558 FND_MSG_PUB.Count_And_Get
1555 When FND_API.G_EXC_ERROR then
1556 Rollback To bulk_auto_receive;
1557 x_return_status := FND_API.G_RET_STS_ERROR ;
1559 (p_count => x_msg_count,
1560 p_data => x_msg_data );
1561
1562 When FND_API.G_EXC_UNEXPECTED_ERROR then
1563 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1564 ROLLBACK TO bulk_auto_receive;
1565 FND_MSG_PUB.Count_And_Get
1566 ( p_count => x_msg_count,
1567 p_data => x_msg_data );
1568
1569 When OTHERS then
1570 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1571 Rollback To bulk_auto_receive;
1572 If FND_MSG_PUB.Check_Msg_Level
1573 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)then
1574 FND_MSG_PUB.Add_Exc_Msg
1575 (G_PKG_NAME,
1576 l_api_name );
1577 End If;
1578 FND_MSG_PUB.Count_And_Get
1579 (p_count => x_msg_count,
1580 p_data => x_msg_data );
1581
1582 END;
1583
1584
1585 /*-----------------------------------------------------------------*/
1586 /* procedure name: write_to_conc_log */
1587 /* description : Procedure to write into Concurrent log */
1588 /* It reads the message from the stack and writes */
1589 /* to Concurrent log. */
1590 /*-----------------------------------------------------------------*/
1591 PROCEDURE write_to_conc_log
1592 (
1593 p_msg_count IN NUMBER,
1594 p_msg_data IN VARCHAR2
1595 )
1596 IS
1597
1598 l_msg Varchar2(2000);
1599
1600 BEGIN
1601
1602 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1603 fnd_log.STRING (fnd_log.level_procedure,
1604 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.WRITE_TO_CONC_LOG.BEGIN',
1605 'Enter - Write to conc log');
1606 End if;
1607
1608 If p_msg_count is not null then
1609
1610 If p_msg_count = 1 then
1611
1612 l_msg := fnd_msg_pub.get(p_msg_index => 1,
1613 p_encoded => 'F' );
1614 Fnd_file.put_line(fnd_file.log,l_msg);
1615
1616 Elsif p_msg_count > 1 then
1617
1618 For i in 1..p_msg_count
1619
1620 Loop
1621 l_msg := fnd_msg_pub.get(p_msg_index => i,
1622 p_encoded => 'F' );
1623 Fnd_file.put_line(fnd_file.log,l_msg);
1624 End loop;
1625
1626 End If;
1627
1628 End If;
1629
1630 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1631 fnd_log.STRING (fnd_log.level_procedure,
1632 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.WRITE_TO_CONC_LOG.END',
1633 'Exit - Write to conc log');
1634 End if;
1635
1636 END;
1637
1638 /*-----------------------------------------------------------------*/
1639 /* procedure name: write_to_conc_output */
1640 /* description : Procedure to write the output to the Concurrent */
1641 /* Output */
1642 /*-----------------------------------------------------------------*/
1643 PROCEDURE write_to_conc_output
1644 (
1645 p_transaction_number IN NUMBER
1646 )
1647 IS
1648
1649 -- Local variables
1650 l_item_desc Varchar2(40);
1651 l_repair_number Varchar2(30);
1652 l_ro_status Varchar2(30);
1653 l_incident_number Varchar2(64);
1654 l_status Varchar2(60);
1655 l_serial_label Varchar2(30);
1656 l_txn_label Varchar2(30);
1657 l_item_label Varchar2(30);
1658 l_qty_label Varchar2(30);
1659 l_sr_label Varchar2(30);
1660 l_ro_label Varchar2(30);
1661 l_status_label Varchar2(30);
1662
1663 -- Cursor to get the Bulk Receive record
1664 Cursor c_get_bulk_receive(p_transaction_number in number) is
1665 Select *
1666 from csd_bulk_receive_items_b
1667 where transaction_number = p_transaction_number;
1668
1669 -- Cursor to get Incident number
1670 Cursor c_get_sr_details(p_incident_id in number) is
1671 Select incident_number
1672 from cs_incidents_all_b
1673 where incident_id = p_incident_id;
1674
1675 -- Cursor to get Repair Order number
1676 Cursor c_get_ro_details(p_repair_line_id in number) is
1677 Select repair_number
1678 ,status
1679 from csd_repairs
1680 where repair_line_id = p_repair_line_id;
1681
1682 -- Cursor to get Item description
1683 Cursor c_get_item_desc(p_inventory_item_id in number) is
1684 Select concatenated_segments
1685 from mtl_system_items_kfv
1686 where inventory_item_id = p_inventory_item_id;
1687
1688
1689 BEGIN
1690
1691 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1692 fnd_log.STRING (fnd_log.level_procedure,
1693 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.WRITE_TO_CONC_OUTPUT.BEGIN',
1694 'Enter - Write to conc output');
1695 End if;
1696
1697 fnd_message.set_name('CSD','CSD_BULK_RCV_SERIAL_CONC_LABEL');
1698 l_serial_label := fnd_message.get;
1699
1700 fnd_message.set_name('CSD','CSD_BULK_RCV_TXN_CONC_LABEL');
1701 l_txn_label := fnd_message.get;
1702
1703 fnd_message.set_name('CSD','CSD_BULK_RCV_ITEM_CONC_LABEL');
1704 l_item_label := fnd_message.get;
1705
1706 fnd_message.set_name('CSD','CSD_BULK_RCV_QTY_CONC_LABEL');
1707 l_qty_label := fnd_message.get;
1708
1709 fnd_message.set_name('CSD','CSD_BULK_RCV_SR_CONC_LABEL');
1710 l_sr_label := fnd_message.get;
1711
1712 fnd_message.set_name('CSD','CSD_BULK_RCV_RO_CONC_LABEL');
1713 l_ro_label := fnd_message.get;
1714
1715 fnd_message.set_name('CSD','CSD_BULK_RCV_STATUS_CONC_LABEL');
1716 l_status_label := fnd_message.get;
1717
1718 Fnd_file.put_line(fnd_file.output,rtrim(l_txn_label)||' : '||p_transaction_number);
1719 Fnd_file.put_line(fnd_file.output,'');
1720 Fnd_file.put(fnd_file.output,rpad(rtrim(l_serial_label),18,' '));
1721 Fnd_file.put(fnd_file.output,rpad(rtrim(l_item_label),14,' '));
1722 Fnd_file.put(fnd_file.output,rpad(rtrim(l_qty_label),13,' '));
1723 Fnd_file.put(fnd_file.output,rpad(rtrim(l_sr_label),25,' '));
1724 Fnd_file.put(fnd_file.output,rpad(rtrim(l_ro_label),25,' '));
1725 Fnd_file.put_line(fnd_file.output,rpad(rtrim(l_status_label),28,' '));
1726 Fnd_file.put_line(fnd_file.output,rpad('-',110,'-'));
1727
1728
1729 For c_get_bulk_receive_rec in c_get_bulk_receive( p_transaction_number)
1730 Loop
1731
1732 -- Reinitialize the variable
1733 l_incident_number := null;
1734 l_repair_number := null;
1735 l_ro_status := null;
1736 l_item_desc := null;
1737
1738 Open c_get_sr_details(c_get_bulk_receive_rec.incident_id);
1739 Fetch c_get_sr_details into l_incident_number;
1740 Close c_get_sr_details;
1741
1742 Open c_get_ro_details(c_get_bulk_receive_rec.repair_line_id);
1743 Fetch c_get_ro_details into l_repair_number,l_ro_status;
1744 Close c_get_ro_details;
1745
1746 Open c_get_item_desc(c_get_bulk_receive_rec.inventory_item_id);
1747 Fetch c_get_item_desc into l_item_desc;
1748 Close c_get_item_desc;
1749
1750
1751 Fnd_file.put(fnd_file.output,rpad(nvl(c_get_bulk_receive_rec.serial_number,' '),18));
1752 Fnd_file.put(fnd_file.output,rpad(nvl(l_item_desc,' '),14,' '));
1753 Fnd_file.put(fnd_file.output,rpad(nvl(to_char(c_get_bulk_receive_rec.quantity),' '),13,' '));
1754 Fnd_file.put(fnd_file.output,rpad(nvl(l_incident_number,' '),25,' '));
1755 Fnd_file.put(fnd_file.output,rpad(nvl(l_repair_number,' '),25,' '));
1756
1757
1758 If ( c_get_bulk_receive_rec.status = 'ERRORED' ) then
1759
1760 fnd_message.set_name('CSD','CSD_BULK_RCV_ERROR_STATUS');
1761 l_status := fnd_message.get;
1762
1763 Elsif ( c_get_bulk_receive_rec.status = 'PROCESSED' ) then
1764
1765 If ( c_get_bulk_receive_rec.internal_sr_flag = 'Y') then
1766 fnd_message.set_name('CSD','CSD_BULK_RCV_INTR_SR_STATUS');
1767 l_status := fnd_message.get;
1768 Elsif (l_ro_status = 'D' ) then
1769 fnd_message.set_name('CSD','CSD_BULK_RCV_DRAFT_RO_STATUS');
1770 l_status := fnd_message.get;
1771 Else
1772 fnd_message.set_name('CSD','CSD_BULK_RCV_RECEIVED_STATUS');
1773 l_status := fnd_message.get;
1774 End if;
1775
1776 Elsif ( c_get_bulk_receive_rec.status = 'NEW' ) then
1777
1778 fnd_message.set_name('CSD','CSD_BULK_RCV_NEW_STATUS');
1779 l_status := fnd_message.get;
1780
1781 End if;
1782
1783 Fnd_file.put_line(fnd_file.output,rpad(rtrim(l_status),28,' '));
1784
1785 End Loop;
1786
1787 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1788 fnd_log.STRING (fnd_log.level_procedure,
1789 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.WRITE_TO_CONC_OUTPUT.END',
1790 'Exit - Write to conc output');
1791 End if;
1792
1793 END;
1794
1795 FUNCTION get_bulk_rcv_def_repair_type
1796 (
1797 p_incident_id IN NUMBER,
1798 p_ro_inventory_item_id IN NUMBER
1799 )
1800 return NUMBER
1801 IS
1802 CURSOR c_get_sr_info(p_incident_id number) is
1803 select customer_id,
1804 account_id,
1805 bill_to_site_use_id,
1806 ship_to_site_use_id,
1807 inventory_item_id,
1808 category_id,
1809 contract_id,
1810 problem_code,
1811 customer_product_id
1812 from CS_INCIDENTS_ALL_VL
1813 where incident_id = p_incident_id;
1814
1815 l_return_status VARCHAR2(1);
1816 l_msg_count NUMBER;
1817 l_msg_data VARCHAR2(2000);
1818 l_rule_input_rec CSD_RULES_ENGINE_PVT.CSD_RULE_INPUT_REC_TYPE;
1819 l_default_val_num NUMBER;
1820 l_default_rule_id NUMBER;
1821 l_repair_org NUMBER; -- repair org id
1822 l_repair_type_id NUMBER := null; -- repair type id
1823
1824 BEGIN
1825 -- Assume SR Incident Id is available to get info for defaulting RO attributes
1826 open c_get_sr_info(p_incident_id);
1827 fetch c_get_sr_info into
1828 l_rule_input_rec.SR_CUSTOMER_ID,
1829 l_rule_input_rec.SR_CUSTOMER_ACCOUNT_ID,
1830 l_rule_input_rec.SR_BILL_TO_SITE_USE_ID,
1831 l_rule_input_rec.SR_SHIP_TO_SITE_USE_ID,
1832 l_rule_input_rec.SR_ITEM_ID,
1833 l_rule_input_rec.SR_ITEM_CATEGORY_ID,
1834 l_rule_input_rec.SR_CONTRACT_ID,
1835 l_rule_input_rec.SR_PROBLEM_CODE,
1836 l_rule_input_rec.SR_INSTANCE_ID;
1837 close c_get_sr_info;
1838
1839 l_rule_input_rec.RO_ITEM_ID := p_ro_inventory_item_id;
1840
1841 l_default_val_num := null;
1842 CSD_RULES_ENGINE_PVT.GET_DEFAULT_VALUE_FROM_RULE(
1843 p_api_version_number => 1.0,
1844 p_init_msg_list => fnd_api.g_false,
1845 p_commit => fnd_api.g_false,
1846 p_validation_level => fnd_api.g_valid_level_full,
1847 p_entity_attribute_type => 'CSD_DEF_ENTITY_ATTR_RO',
1848 p_entity_attribute_code => 'REPAIR_TYPE',
1849 p_rule_input_rec => l_rule_input_rec,
1850 x_default_value => l_default_val_num,
1851 x_rule_id => l_default_rule_id,
1852 x_return_status => l_return_status,
1853 x_msg_count => l_msg_count,
1854 x_msg_data => l_msg_data
1855 );
1856
1857 -- if default rule id is null, then no defaulting rule was found, and the
1858 -- profile for regular repair types was returned. We want the bulk receive
1859 -- profile option, so need to check default rule id.
1860 if (l_return_status = fnd_api.g_ret_sts_success) and
1861 (l_default_val_num is not null) and
1862 (l_default_rule_id is not null)
1863 then
1864 l_repair_type_id := l_default_val_num;
1865 else
1866 l_repair_type_id := to_number(fnd_profile.value('CSD_BLK_RCV_DEFAULT_REPAIR_TYPE'));
1867 end if;
1868
1869 return l_repair_type_id;
1870
1871 END get_bulk_rcv_def_repair_type;
1872
1873 -- swai: bug 7663674
1874 -- added function to get default rma subinv and use bulk receiving
1875 -- profile option value as backup default value.
1876 /*-----------------------------------------------------------------*/
1877 /* function name: get_bulk_rcv_def_sub_inv */
1878 /* description : Function to get the default rma subinv for */
1879 /* bulk receiving, based on defaulting rules and */
1880 /* bulk receiving profile option. */
1881 /* Output RMA Subinventory Code */
1882 /*-----------------------------------------------------------------*/
1883 FUNCTION get_bulk_rcv_def_sub_inv
1884 (
1885 p_repair_line_id IN NUMBER
1886 )
1887 return VARCHAR2
1888 IS
1889 l_return_status VARCHAR2(1);
1890 l_msg_count NUMBER;
1891 l_msg_data VARCHAR2(2000);
1892 l_rule_input_rec CSD_RULES_ENGINE_PVT.CSD_RULE_INPUT_REC_TYPE;
1893 l_default_val_str VARCHAR2(30);
1894 l_default_rule_id NUMBER := null;
1895 l_rma_subinv VARCHAR2(30) := null; -- repair type id
1896
1897 BEGIN
1898 l_rule_input_rec.repair_line_id := p_repair_line_id;
1899 l_default_val_str := null;
1900 CSD_RULES_ENGINE_PVT.GET_DEFAULT_VALUE_FROM_RULE(
1901 p_api_version_number => 1.0,
1902 p_init_msg_list => fnd_api.g_false,
1903 p_commit => fnd_api.g_false,
1904 p_validation_level => fnd_api.g_valid_level_full,
1905 p_entity_attribute_type => 'CSD_DEF_ENTITY_ATTR_RO',
1906 p_entity_attribute_code => 'RMA_RCV_SUBINV',
1907 p_rule_input_rec => l_rule_input_rec,
1908 x_default_value => l_default_val_str,
1909 x_rule_id => l_default_rule_id,
1910 x_return_status => l_return_status,
1911 x_msg_count => l_msg_count,
1912 x_msg_data => l_msg_data
1913 );
1914
1915 -- if default rule id is null, then no defaulting rule was found, and the
1916 -- profile for regular rma subinv was returned. We want the bulk receive
1917 -- profile option, so need to check default rule id.
1918 if (l_return_status = fnd_api.g_ret_sts_success) and
1919 (l_default_val_str is not null) and
1920 (l_default_rule_id is not null)
1921 then
1922 l_rma_subinv := l_default_val_str;
1923 else
1924 l_rma_subinv := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SUB_INV');
1925 end if;
1926
1927 return l_rma_subinv;
1928
1929 END get_bulk_rcv_def_sub_inv;
1930
1931 END CSD_BULK_RECEIVE_UTIL;