[Home] [Help]
PACKAGE BODY: APPS.CSD_OM_INTERFACE_PVT
Source
1 PACKAGE BODY CSD_OM_INTERFACE_PVT AS
2 /* $Header: csdvomtb.pls 120.4 2005/10/18 16:37:01 mshirkol noship $ */
3
4 /* --------------------------------------*/
5 /* Define global variables */
6 /* --------------------------------------*/
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_OM_INTERFACE_PVT';
9 G_FILE_NAME CONSTANT VARCHAR2(30) := 'csdvomtb.pls';
10
11 -- Global variable for storing the debug level
12 G_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
13
14 /*-------------------------------------------------------------------------------------*/
15 /* Function name: DEBUG */
16 /* Description : Logs the debug message */
17 /* Called from : Called from Update API */
18 /* */
19 /* STANDARD PARAMETERS */
20 /* In Parameters : */
21 /* p_message Required Debug message that needs to be logged */
22 /* p_mod_name Required Module name */
23 /* p_severity_level Required Severity level */
24 /* Output Parameters: */
25 /* NON-STANDARD PARAMETERS */
26 /* In Parameters */
27 /* Out parameters */
28 /* Change Hist : */
29 /* 09/20/03 vlakaman Initial Creation. */
30 /*-------------------------------------------------------------------------------------*/
31
32 Procedure DEBUG
33 (p_message in varchar2,
34 p_mod_name in varchar2,
35 p_severity_level in number
36 ) IS
37
38 -- Variables used in FND Log
39 l_stat_level number := FND_LOG.LEVEL_STATEMENT;
40 l_proc_level number := FND_LOG.LEVEL_PROCEDURE;
41 l_event_level number := FND_LOG.LEVEL_EVENT;
42 l_excep_level number := FND_LOG.LEVEL_EXCEPTION;
43 l_error_level number := FND_LOG.LEVEL_ERROR;
44 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
45
46 BEGIN
47
48 IF p_severity_level = 1 THEN
49 IF ( l_stat_level >= G_debug_level) THEN
50 FND_LOG.STRING(l_stat_level,p_mod_name,p_message);
51 END IF;
52 ELSIF p_severity_level = 2 THEN
53 IF ( l_proc_level >= G_debug_level) THEN
54 FND_LOG.STRING(l_proc_level,p_mod_name,p_message);
55 END IF;
56 ELSIF p_severity_level = 3 THEN
57 IF ( l_event_level >= G_debug_level) THEN
58 FND_LOG.STRING(l_event_level,p_mod_name,p_message);
59 END IF;
60 ELSIF p_severity_level = 4 THEN
61 IF ( l_excep_level >= G_debug_level) THEN
62 FND_LOG.STRING(l_excep_level,p_mod_name,p_message);
63 END IF;
64 ELSIF p_severity_level = 5 THEN
65 IF ( l_error_level >= G_debug_level) THEN
66 FND_LOG.STRING(l_error_level,p_mod_name,p_message);
67 END IF;
68 ELSIF p_severity_level = 6 THEN
69 IF ( l_unexp_level >= G_debug_level) THEN
70 FND_LOG.STRING(l_unexp_level,p_mod_name,p_message);
71 END IF;
72 END IF;
73
74 END DEBUG;
75
76 FUNCTION get_sr_contacts (p_contact_id NUMBER)
77 RETURN CS_SERVICEREQUEST_PUB.CONTACTS_TABLE ;
78
79 /*-------------------------------------------------------------------------------------*/
80 /* Procedure name: Get_Party_site_id */
81 /* Description : Get the bill to/ship to party site use id */
82 /* Called from : Called from Process_Rma */
83 /* */
84 /* STANDARD PARAMETERS */
85 /* In Parameters : */
86 /* p_site_use_type Required Site Use Type */
87 /* p_cust_site_use_id Required Cust site use */
88 /* Output Parameters: */
89 /* x_return_status Return Status */
90 /* NON-STANDARD PARAMETERS */
91 /* In Parameters */
92 /* Out parameters */
93 /* x_party_site_use_id Party Site Use Id */
94 /* Change Hist : */
95 /* 01/26/04 vlakaman Initial Creation. */
96 /*-------------------------------------------------------------------------------------*/
97
98 PROCEDURE Get_Party_site_id
99 ( p_site_use_type IN VARCHAR2,
100 p_cust_site_use_id IN NUMBER ,
101 x_party_site_use_id OUT NOCOPY VARCHAR2,
102 x_return_status OUT NOCOPY VARCHAR2
103 ) IS
104
105 l_party_site_id NUMBER;
106 l_party_site_use_id NUMBER;
107
108 -- Variables used in FND Log
109 l_error_level number := FND_LOG.LEVEL_ERROR;
110 l_mod_name varchar2(2000) := 'csd.plsql.csd_om_interface_pvt.get_party_site_id';
111
112 BEGIN
113 -- Initialize the return status
114 x_return_status := FND_API.G_RET_STS_SUCCESS ;
115
116 -- Api body starts
117 Debug('At the Beginning of Get_Party_site_id',l_mod_name,1);
118 Debug('p_cust_site_use_id '||p_cust_site_use_id ,l_mod_name,1);
119
120 Begin
121 SELECT hcas.party_site_id
122 INTO l_party_site_id
123 FROM hz_cust_acct_sites_all hcas,
124 hz_cust_site_uses_all hcsu
125 WHERE hcas.cust_acct_site_id = hcsu.cust_acct_site_id
126 AND hcsu.site_use_id = p_cust_site_use_id ;
127 Exception
128 When No_Data_found then
129 IF ( l_error_level >= G_debug_level) THEN
130 fnd_message.set_name('CSD','CSD_INV_PTY_SITE_USE_ID');
131 fnd_message.set_token('CUST_SITE_USE_ID',p_cust_site_use_id );
132 FND_LOG.MESSAGE(l_error_level,l_mod_name, FALSE);
133 ELSE
134 fnd_message.set_name('CSD','CSD_INV_PTY_SITE_USE_ID');
135 fnd_message.set_token('CUST_SITE_USE_ID',p_cust_site_use_id );
136 fnd_msg_pub.add;
137 END IF;
138 Debug('Party site does not exist for cust site use ID='||to_char(p_cust_site_use_id) ,l_mod_name,1);
139 Raise FND_API.G_EXC_ERROR ;
140 End;
141
142 IF l_party_site_id is not null THEN
143 Begin
144 SELECT hpsu.party_site_use_id
145 INTO l_party_site_use_id
146 FROM Hz_Party_Sites hps,
147 Hz_Party_Site_uses hpsu,
148 Hz_Locations hl
149 WHERE hps.party_site_id = l_party_site_id
150 AND hpsu.site_use_type = p_site_use_type
151 AND hps.status = 'A'
152 AND hps.location_id = hl.location_id
153 AND hps.party_site_id = hpsu.party_site_id;
154 Exception
155 When No_Data_found then
156 IF ( l_error_level >= G_debug_level) THEN
157 fnd_message.set_name('CSD','CSD_INV_PTY_SITE_ID');
158 fnd_message.set_token('PTY_SITE_ID',l_party_site_id);
159 FND_LOG.MESSAGE(l_error_level,l_mod_name, FALSE);
160 ELSE
161 fnd_message.set_name('CSD','CSD_INV_PTY_SITE_ID');
162 fnd_message.set_token('PTY_SITE_ID',l_party_site_id);
163 fnd_msg_pub.add;
164 END IF;
165 Debug('Party site does not exist for party site id='||to_char(l_party_site_id) ,l_mod_name,1);
166 Raise FND_API.G_EXC_ERROR ;
167 When TOO_MANY_ROWS then
168 IF ( l_error_level >= G_debug_level) THEN
169 fnd_message.set_name('CSD','CSD_INV_PTY_SITE_ID');
170 fnd_message.set_token('PTY_SITE_ID',l_party_site_id);
171 FND_LOG.MESSAGE(l_error_level,l_mod_name, FALSE);
172 ELSE
173 fnd_message.set_name('CSD','CSD_INV_PTY_SITE_ID');
174 fnd_message.set_token('PTY_SITE_ID',l_party_site_id);
175 fnd_msg_pub.add;
176 END IF;
177 Debug('Too many rows found for party site id='||to_char(l_party_site_id) ,l_mod_name,1);
178 Raise FND_API.G_EXC_ERROR ;
179 End;
180 END IF;
181
182 Debug('l_party_site_use_id='||to_char(l_party_site_use_id) ,l_mod_name,1);
183
184 --Return the party site use id
185 x_party_site_use_id := l_party_site_use_id;
186
187 EXCEPTION
188 WHEN FND_API.G_EXC_ERROR THEN
189 x_return_status := FND_API.G_RET_STS_ERROR ;
190 WHEN OTHERS THEN
191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
192 END Get_party_site_id;
193
194
195 /*-------------------------------------------------------------------------------------*/
196 /* Procedure name: PROCESS_RMA */
197 /* Description : Creates SR/RO against RMA */
198 /* Called from : Called from Concurrent Program */
199 /* */
200 /* STANDARD PARAMETERS */
201 /* In Parameters : */
202 /* p_inventory_org_id Required Warehouse */
203 /* p_subinventory_name Required Received Subinventory */
204 /* Output Parameters: */
205 /* errbuf Error Buffer */
206 /* retcode Return Code */
207 /* NON-STANDARD PARAMETERS */
208 /* In Parameters */
209 /* Out parameters */
210 /* Change Hist : */
211 /* 01/26/04 vlakaman Initial Creation. */
212 /*-------------------------------------------------------------------------------------*/
213
214 PROCEDURE PROCESS_RMA
215 (errbuf OUT NOCOPY VARCHAR2,
216 retcode OUT NOCOPY VARCHAR2,
217 p_inventory_org_id IN NUMBER,
218 p_subinventory_name IN VARCHAR2
219 ) IS
220
221 l_service_request_rec CS_SERVICEREQUEST_PUB.service_request_rec_type;
222 l_notes_table CS_ServiceRequest_PUB.notes_table;
223 l_contacts_table CS_ServiceRequest_PUB.contacts_table;
224 l_rep_line_tbl CSD_REPAIRS_PUB.REPLN_TBL_Type;
225 l_inc_type_id NUMBER;
226 l_inc_status_id NUMBER;
227 l_inc_severity_id NUMBER;
228 l_inc_urgency_id NUMBER;
229 l_inc_sr_owner_id NUMBER;
230 l_inc_work_summary VARCHAR2(240);
231 l_ro_owner_id NUMBER;
232 ln_interaction_id NUMBER;
233 ln_workflow_id NUMBER;
234 l_sr_count NUMBER;
235 l_count NUMBER;
236 l_ro_count NUMBER;
237 l_error_count NUMBER;
238 l_incident_id NUMBER;
239 l_incident_number NUMBER;
240 l_skip_ro_flag BOOLEAN;
241 l_skip_sr_flag BOOLEAN;
242 l_approval_flag VARCHAR2(1);
243 l_repair_mode VARCHAR2(30);
244 l_repair_type_id NUMBER;
245 l_repair_number VARCHAR2(30);
246 l_repair_line_id NUMBER;
247 l_msg_count NUMBER;
248 l_msg_data VARCHAR2(2000);
249 l_return_status VARCHAR2(1);
250 l_serialized_flag BOOLEAN;
251 l_dummy VARCHAR2(1);
252 l_customer_id NUMBER;
253 l_caller_type VARCHAR2(80);
254 l_bill_to_party_site_use_id NUMBER;
255 l_ship_to_party_site_use_id NUMBER;
256 l_rep_hist_id NUMBER;
257 l_instance_id NUMBER;
258 l_msg_index_out NUMBER;
259
260 CURSOR c_party_site_id(p_party_site_use_id in number) IS
261 select party_site_id
262 from hz_party_site_uses
263 where party_site_use_id = p_party_site_use_id;
264
265 CURSOR c_party_id(p_party_site_id in number) IS
266 select party_id
267 from hz_party_sites
268 where party_site_id = p_party_site_id;
269
270 -- Exception
271 SKIP_RECORD EXCEPTION;
272 SKIP_RO EXCEPTION;
273
274 CURSOR Get_rcv_lines (p_inv_org_id in NUMBER,
275 p_sub_inv in VARCHAR2) IS
276 SELECT /*+ CHOOSE */
277 oeh.order_number rma_number,
278 oeh.header_id rma_header_id,
279 oeh.order_category_code,
280 oeh.booked_flag,
281 NVL(oeh.invoice_to_org_id,oel.invoice_to_org_id) bill_to_site_use_id,
282 NVL(oeh.ship_to_org_id,oel.ship_to_org_id) ship_to_site_use_id,
283 oeh.sold_to_org_id cust_account_id,
284 oeh.cust_po_number purchase_order_num,
285 oeh.transactional_curr_code,
286 oeh.SOLD_TO_CONTACT_ID CONTACT_ID ,
287 oel.line_id ,
288 oel.line_number rma_line_number,
289 oel.inventory_item_id,
290 oel.item_revision,
291 oel.price_list_id,
292 oel.shipped_quantity,
293 oel.line_type_id,
294 oel.order_quantity_uom,
295 rcv.organization_id,
296 rcv.quantity received_quantity,
297 rcv.subinventory received_subinventory,
298 rcv.transaction_date received_date,
299 rcv.transaction_id,
300 rcv.last_updated_by who_col,
301 rcv.subinventory,
302 hp.party_type,
303 hp.party_id,
304 haou.name org_name
305 FROM RCV_TRANSACTIONS rcv,
306 OE_ORDER_LINES_ALL oel,
307 OE_ORDER_HEADERS_ALL oeh,
308 hz_parties hp,
309 hz_cust_accounts hca,
310 hr_all_organization_units haou
311 WHERE rcv.oe_order_line_id = oel.line_id
312 AND rcv.transaction_type = 'DELIVER'
313 AND rcv.source_document_code = 'RMA'
314 AND oel.header_id = oeh.header_id
315 AND oel.sold_to_org_id = hca.cust_account_id
316 AND hca.party_id = hp.party_id
317 AND rcv.organization_id = haou.organization_id
318 AND rcv.organization_id = p_inv_org_id
319 AND rcv.subinventory = p_sub_inv
320 AND not exists
321 ( select 'X' from csd_repairs cra
322 where cra.original_source_line_id = oel.line_id
323 and cra.original_source_header_id = oel.header_id)
324 AND not exists
325 ( select 'x' from cs_estimate_details ced
326 where ced.order_header_id = oel.header_id
327 and ced.order_line_id = oel.line_id);
328
329 -- Get the serial number for the txn Id
330 CURSOR Get_serial_num (p_txn_id in NUMBER) IS
331 SELECT serial_num
332 from rcv_serial_transactions
333 where transaction_id = p_txn_id;
334
335 -- Variables used in FND Log
336 l_error_level number := FND_LOG.LEVEL_ERROR;
337 l_mod_name varchar2(2000) := 'csd.plsql.csd_om_interface_pvt.process_rma';
338
339 BEGIN
340
341 -- Standard Start of API savepoint
342 SAVEPOINT process_rma;
343
344 --
345 -- MOAC initialization
346 --
347 MO_GLOBAL.init('CS_CHARGES');
348
349 -- Initialize message list if p_init_msg_list is set to TRUE.
350 FND_MSG_PUB.initialize;
351
352 -- Api body starts
353 Debug( 'Begining of concurrent program',l_mod_name,1);
354 Debug( '*************In Parameters***************',l_mod_name,1);
355 Debug( ' p_inventory_org_id ='||to_char(p_inventory_org_id),l_mod_name,1);
356 Debug( ' p_subinventory_name ='||p_subinventory_name,l_mod_name,1);
357 Debug( '*************In Parameters***************',l_mod_name,1);
358
359 IF p_inventory_org_id is NULL OR
360 p_subinventory_name is NULL then
361 IF ( l_error_level >= G_debug_level) THEN
362 fnd_message.set_name('CSD','CSD_PARAMETERS_MISSING');
363 FND_LOG.MESSAGE(l_error_level,l_mod_name, FALSE);
364 ELSE
365 fnd_message.set_name('CSD','CSD_PARAMETERS_MISSING');
366 fnd_msg_pub.add;
367 END IF;
368 Debug( 'One of the IN parameters(Inv Org, Subinventory) is NULL',l_mod_name,1);
369 Raise FND_API.G_EXC_ERROR ;
370 END IF;
371
372 l_approval_flag := FND_PROFILE.value('CSD_CUST_APPROVAL_REQD');
373
374 -- Default values for SR
375 l_inc_type_id := FND_PROFILE.value('CSD_OM_DEFAULT_SR_TYPE');
376 l_inc_status_id := FND_PROFILE.value('CSD_OM_DEFAULT_SR_STATUS');
377 l_inc_severity_id := FND_PROFILE.value('CSD_OM_DEFAULT_SR_SEVERITY');
378 l_inc_urgency_id := FND_PROFILE.value('CSD_OM_DEFAULT_SR_URGENCY');
379 -- To fix bug # 3615720 Defined a message in SEED and Seeded translated message will be used
380 -- when profile is not defined
381 -- l_inc_work_summary := FND_PROFILE.value('CSD_OM_SR_WORK_SUMMARY');
382 FND_PROFILE.Get('CSD_OM_SR_WORK_SUMMARY',l_Inc_Work_Summary);
383 if l_Inc_Work_Summary is Null Then
384 Fnd_Message.Set_Name('CSD','CSD_SR_WORK_SUMMARY_RMA');
385 l_Inc_Work_Summary := Fnd_Message.Get;
386 End If;
387
388 -- Default values for RO
389 l_ro_owner_id := FND_PROFILE.value('CSD_OM_RO_DEFAULT_OWNER');
390
391 Debug( 'Checking if the IN parameters are not null',l_mod_name,1);
392
393 IF l_inc_type_id is NULL OR
394 l_inc_status_id is NULL OR
395 l_inc_severity_id is NULL OR
396 l_inc_urgency_id is NULL OR
397 l_inc_work_summary is NULL THEN
398 IF ( l_error_level >= G_debug_level) THEN
399 fnd_message.set_name('CSD','CSD_PROF_SETUP_MISSING');
400 FND_LOG.MESSAGE(l_error_level,l_mod_name, FALSE);
401 ELSE
402 fnd_message.set_name('CSD','CSD_PROF_SETUP_MISSING');
403 fnd_msg_pub.add;
404 END IF;
405 Debug( 'One of the profile for SR default values is not set',l_mod_name,1);
406 Raise FND_API.G_EXC_ERROR ;
407 END IF;
408
409 Debug( 'Getting Repair Type Id and mode for standard repair',l_mod_name,1);
410
411 -- Get the repair type and repair mode
412 -- for standard repair type
413 Begin
414 Select repair_type_id,
415 repair_mode
416 into l_repair_type_id,
417 l_repair_mode
418 from csd_repair_types_vl
419 where repair_type_ref = 'SR'
420 and seeded_flag = 'Y';
421 Exception
422 When No_Data_found then
423 IF ( l_error_level >= G_debug_level) THEN
424 fnd_message.set_name('CSD','CSD_STD_REP_TYPE_MISSING');
425 FND_LOG.MESSAGE(l_error_level,l_mod_name, FALSE);
426 ELSE
427 fnd_message.set_name('CSD','CSD_STD_REP_TYPE_MISSING');
428 fnd_msg_pub.add;
429 END IF;
430 Debug( 'Standard Repair Type not found',l_mod_name,1);
431 Raise FND_API.G_EXC_ERROR ;
432 When TOO_MANY_ROWS then
433 IF ( l_error_level >= G_debug_level) THEN
434 fnd_message.set_name('CSD','CSD_STD_REP_TYPE_MISSING');
435 FND_LOG.MESSAGE(l_error_level,l_mod_name, FALSE);
436 ELSE
437 fnd_message.set_name('CSD','CSD_STD_REP_TYPE_MISSING');
438 fnd_msg_pub.add;
439 END IF;
440 Debug( 'Too many Standard Repair Types found',l_mod_name,1);
441 Raise FND_API.G_EXC_ERROR ;
442 End;
443
444 Debug( 'Validating the Inv Org',l_mod_name,1);
445 -- Validate if the inv org id is set in inventory
446 Begin
447 Select 'X'
448 into l_dummy
449 from mtl_parameters
450 where organization_id = p_inventory_org_id;
451 Exception
452 When No_Data_found then
453 IF ( l_error_level >= G_debug_level) THEN
454 fnd_message.set_name('CSD','CSD_INVALID_INV_ORG_ID');
455 fnd_message.set_token('INV_ORG_ID',p_inventory_org_id);
456 FND_LOG.MESSAGE(l_error_level,l_mod_name, FALSE);
457 ELSE
458 fnd_message.set_name('CSD','CSD_INVALID_INV_ORG_ID');
459 fnd_message.set_token('INV_ORG_ID',p_inventory_org_id);
460 fnd_msg_pub.add;
461 END IF;
462 Debug( 'Inventory Org not found for INV Org ID='||to_char(p_inventory_org_id),l_mod_name,1);
463 Raise FND_API.G_EXC_ERROR ;
464 When TOO_MANY_ROWS then
465 IF ( l_error_level >= G_debug_level) THEN
466 fnd_message.set_name('CSD','CSD_INVALID_INV_ORG_ID');
467 fnd_message.set_token('INV_ORG_ID',p_inventory_org_id);
468 FND_LOG.MESSAGE(l_error_level,l_mod_name, FALSE);
469 ELSE
470 fnd_message.set_name('CSD','CSD_INVALID_INV_ORG_ID');
471 fnd_message.set_token('INV_ORG_ID',p_inventory_org_id);
472 fnd_msg_pub.add;
473 END IF;
474 Debug( 'Two many rows Inventory Org is defined for Inv Org ID='||to_char(p_inventory_org_id),l_mod_name,1);
475 Raise FND_API.G_EXC_ERROR ;
476 End;
477
478 Debug( 'Validating the Subinventory',l_mod_name,1);
479 -- Validate the subinventory
480 Begin
481 Select 'X'
482 into l_dummy
483 from mtl_secondary_inventories
484 where organization_id = p_inventory_org_id
485 and secondary_inventory_name = p_subinventory_name ;
486 Exception
487 When No_Data_found then
488 IF ( l_error_level >= G_debug_level) THEN
489 fnd_message.set_name('CSD','CSD_INVALID_SUB_INV');
490 fnd_message.set_token('SUB_INV',p_subinventory_name );
491 FND_LOG.MESSAGE(l_error_level,l_mod_name, FALSE);
492 ELSE
493 fnd_message.set_name('CSD','CSD_INVALID_SUB_INV');
494 fnd_message.set_token('SUB_INV',p_subinventory_name );
495 fnd_msg_pub.add;
496 END IF;
497 Debug( 'Subinventory Location not found='||p_subinventory_name,l_mod_name,1 );
498 Raise FND_API.G_EXC_ERROR ;
499 When TOO_MANY_ROWS then
500 IF ( l_error_level >= G_debug_level) THEN
501 fnd_message.set_name('CSD','CSD_INVALID_SUB_INV');
502 fnd_message.set_token('SUB_INV',p_subinventory_name );
503 FND_LOG.MESSAGE(l_error_level,l_mod_name, FALSE);
504 ELSE
505 fnd_message.set_name('CSD','CSD_INVALID_SUB_INV');
506 fnd_message.set_token('SUB_INV',p_subinventory_name );
507 fnd_msg_pub.add;
508 END IF;
509 Debug( 'Too many rows found for subinventory ='||p_subinventory_name,l_mod_name,1 );
510 Raise FND_API.G_EXC_ERROR ;
511 End;
512
513 l_sr_count := 0;
514 l_ro_count := 0;
515 l_error_count := 0;
516
517 FOR C1 in Get_rcv_lines (p_inventory_org_id, p_subinventory_name )
518 LOOP
519 BEGIN
520
521 -- Standard Start of API savepoint
522 SAVEPOINT rcv_lines;
523
524 Debug( 'Inside FOR LOOP....',l_mod_name,1);
525 Debug( 'Order Header Id ='||to_char(c1.rma_header_id),l_mod_name,1);
526 Debug( 'Order line Id ='||to_char(c1.line_id),l_mod_name,1);
527 Debug( 'Checking if SR exists',l_mod_name,1);
528
529 -- Check if SR exists already for the order header Id
530 -- Vijay:3840775: Include order line in the where clause.
531 -- sragunat, rollbacked the bug fix change,
532 -- Removed the order line, as this leads to a new SR being created
533 -- for every order line
534 Begin
535 Select incident_id
536 into l_incident_id
537 from csd_repairs cra
538 where cra.original_source_header_id = c1.rma_header_id
539 -- and cra.original_source_line_id = c1.line_id
540 and rownum = 1;
541 l_skip_sr_flag := TRUE;
542 Debug( 'SR exists for the order line Id :'||to_char(c1.line_id),l_mod_name,1);
543 Exception
544 When No_Data_Found then
545 l_skip_sr_flag := FALSE;
546 Debug( 'NO SR exists for the order line Id :'||to_char(c1.line_id),l_mod_name,1);
547 End;
548
549 -- Only if SR exist then check the status
550 IF l_incident_id is not null and
551 l_skip_sr_flag THEN
552 Begin
553 Select 'X'
554 into l_dummy
555 from cs_incidents_all_b cia,
556 cs_incident_statuses cis
557 where cia.incident_status_id = cis.incident_status_id
558 and cis.status_code = 'OPEN'
559 and incident_id = l_incident_id;
560 Debug( 'SR is open for the incident Id ='||to_char(l_incident_id),l_mod_name,1);
561 Exception
562 When No_Data_Found then
563 Debug( 'SR is not open for the incident Id ='||l_incident_id,l_mod_name,1);
564 RAISE SKIP_RECORD;
565 End;
566 END IF;
567
568 IF NOT(l_skip_sr_flag) THEN
569
570 Debug( 'Deriving SR Values',l_mod_name,1);
571 Debug( 'Deriving Bill to site Use Id',l_mod_name,1);
572 --Get the bill to site use Id
573 Get_Party_site_id
574 ( p_site_use_type => 'BILL_TO',
575 p_cust_site_use_id => C1.bill_to_site_use_id,
576 x_party_site_use_id => l_bill_to_party_site_use_id ,
577 x_return_status => l_return_status);
578
579 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
580 Debug( 'Get_party_site_id failed for bill-to party site use Id ='||to_char(l_bill_to_party_site_use_id ),l_mod_name,1);
581 RAISE SKIP_RECORD;
582 END IF;
583
584 Debug( 'Deriving Ship to site Use Id',l_mod_name,1);
585 --Get the ship to site use Id
586 Get_Party_site_id
587 (p_site_use_type => 'SHIP_TO',
588 p_cust_site_use_id => C1.ship_to_site_use_id,
589 x_party_site_use_id => l_ship_to_party_site_use_id ,
590 x_return_status => l_return_status);
591
592 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
593 Debug( 'Get_party_site_id failed for ship-to party site use Id = '||to_char(l_ship_to_party_site_use_id ),l_mod_name,1);
594 RAISE SKIP_RECORD;
595 END IF;
596
597 -- Initialize the SR record values
598 CS_SERVICEREQUEST_PUB.initialize_rec(l_service_request_rec);
599
600 l_service_request_rec.request_date := sysdate;
601 l_service_request_rec.type_id := l_inc_type_id;
602 l_service_request_rec.status_id := l_inc_status_id;
603 l_service_request_rec.severity_id := l_inc_severity_id;
604 l_service_request_rec.urgency_id := l_inc_urgency_id;
605 -- l_service_request_rec.owner_id := l_inc_sr_owner_id;
606 l_service_request_rec.summary := l_inc_work_summary;
607 l_service_request_rec.caller_type := C1.party_type;
608 l_service_request_rec.customer_id := C1.party_id;
609 l_service_request_rec.inventory_item_id := C1.inventory_item_id;
610 l_service_request_rec.inv_item_revision := C1.item_revision;
611 l_service_request_rec.inventory_org_id := cs_std.get_item_valdn_orgzn_id;
612 --l_service_request_rec.purchase_order_num := C1.purchase_order_num;
613 l_service_request_rec.bill_to_site_use_id := l_bill_to_party_site_use_id;
614 l_service_request_rec.ship_to_site_use_id := l_ship_to_party_site_use_id;
615 l_service_request_rec.account_id := C1.cust_account_id;
616 l_service_request_rec.cust_po_number := C1.purchase_order_num;
617 l_service_request_rec.sr_creation_channel := 'AGENT';
618 -- l_service_request_rec.publish_flag := NVL(FND_PROFILE.value('INC_PUBLISH_FLAG_UPDATE'),'N');
619 l_service_request_rec.creation_program_code := 'CSD_REPAIR_ORDER_FORM';
620 l_service_request_rec.last_update_program_code := 'CSD_REPAIR_ORDER_FORM';
621 l_service_request_rec.group_type := 'RS_GROUP';
622
623 Debug( 'Getting Bill_to fields = ',l_mod_name,1);
624 -- Bill-To fields
625 IF (nvl(l_service_request_rec.bill_to_site_use_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM) THEN
626 OPEN c_party_site_id(l_service_request_rec.bill_to_site_use_id);
627 FETCH c_party_site_id INTO l_service_request_rec.bill_to_site_id;
628 CLOSE c_party_site_id;
629 Debug('l_service_request_rec.bill_to_site_id ' ||l_service_request_rec.bill_to_site_id,l_mod_name,1);
630 IF (nvl(l_service_request_rec.bill_to_site_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM) THEN
631 OPEN c_party_id(l_service_request_rec.bill_to_site_id);
632 FETCH c_party_id INTO l_service_request_rec.bill_to_party_id;
633 CLOSE c_party_id;
634 Debug('l_service_request_rec.bill_to_party_id '||l_service_request_rec.bill_to_party_id,l_mod_name,1);
635 END IF;
636 END IF;
637
638 -- Ship-To fields
639 Debug('GETTING SHIP-TO FIELDS',l_mod_name,1);
640 IF (nvl(l_service_request_rec.ship_to_site_use_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM) THEN
641 OPEN c_party_site_id(l_service_request_rec.ship_to_site_use_id);
642 FETCH c_party_site_id INTO l_service_request_rec.ship_to_site_id;
643 CLOSE c_party_site_id;
644 Debug('l_service_request_rec.ship_to_site_id ' ||l_service_request_rec.ship_to_site_id,l_mod_name,1);
645 IF (nvl(l_service_request_rec.ship_to_site_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM) THEN
646 OPEN c_party_id(l_service_request_rec.ship_to_site_id);
647 FETCH c_party_id INTO l_service_request_rec.ship_to_party_id;
648 CLOSE c_party_id;
649 Debug('l_service_request_rec.ship_to_party_id ' ||l_service_request_rec.ship_to_party_id,l_mod_name,1);
650 END IF;
651 END IF;
652
653 Debug( 'C1.contact_id = '||to_char(C1.contact_id),l_mod_name,1);
654
655 l_contacts_table := Get_sr_contacts(C1.contact_id);
656
657 /*
658 -- thinking Not to create contact for SR as it is optional
659 If (p_service_request_rec.party_id is not null) then
660 l_contacts_table(1).sr_contact_point_id := p_service_request_rec.sr_contact_point_id;
661 l_contacts_table(1).party_id := p_service_request_rec.party_id;
662 l_contacts_table(1).contact_type := p_service_request_rec.contact_type;
663 l_contacts_table(1).contact_point_id := p_service_request_rec.contact_point_id;
664 l_contacts_table(1).contact_point_type := p_service_request_rec.contact_point_type;
665 l_contacts_table(1).primary_flag := p_service_request_rec.primary_flag;
666 end If;
667 */
668
669 Debug( 'l_service_request_rec.request_date = '||to_char(l_service_request_rec.request_date),l_mod_name,1);
670 Debug( 'l_service_request_rec.type_id = '||to_char(l_service_request_rec.type_id),l_mod_name,1);
671 Debug( 'l_service_request_rec.status_id = '||to_char(l_service_request_rec.status_id),l_mod_name,1);
672 Debug( 'l_service_request_rec.severity_id = '||to_char(l_service_request_rec.severity_id),l_mod_name,1);
673 Debug( 'l_service_request_rec.urgency_id = '||to_char(l_service_request_rec.urgency_id),l_mod_name,1);
674 Debug( 'l_service_request_rec.summary = '||l_service_request_rec.summary,l_mod_name,1);
675 Debug( 'l_service_request_rec.request_date = '||to_char(l_service_request_rec.request_date),l_mod_name,1);
676 Debug( 'l_service_request_rec.caller_type = '||l_service_request_rec.caller_type,l_mod_name,1);
677 Debug( 'l_service_request_rec.customer_id = '||to_char(l_service_request_rec.customer_id),l_mod_name,1);
678 Debug( 'l_service_request_rec.inventory_item_id = '||to_char(l_service_request_rec.inventory_item_id),l_mod_name,1);
679 Debug( 'l_service_request_rec.inventory_org_id = '||to_char(l_service_request_rec.inventory_org_id),l_mod_name,1);
680 Debug( 'l_service_request_rec.bill_to_site_use_id = '||to_char(l_service_request_rec.bill_to_site_use_id),l_mod_name,1);
681 Debug( 'l_service_request_rec.ship_to_site_use_id = '||to_char(l_service_request_rec.ship_to_site_use_id),l_mod_name,1);
682 Debug( 'l_service_request_rec.account_id = '||to_char(l_service_request_rec.account_id),l_mod_name,1);
683 Debug( 'l_service_request_rec.cust_po_number = '||l_service_request_rec.cust_po_number,l_mod_name,1);
684
685 Debug( 'Calling CS_SERVICEREQUEST_PUB.Create_ServiceRequest ',l_mod_name,1);
686
687 -- Call to Service Request API
688 CS_SERVICEREQUEST_PUB.Create_ServiceRequest(
689 p_api_version => 2.0,
690 p_init_msg_list => FND_API.G_TRUE,
691 p_commit => FND_API.G_FALSE,
692 x_return_status => l_return_status,
693 x_msg_count => l_msg_count,
694 x_msg_data => l_msg_data,
695 p_resp_appl_id => NULL,
696 p_resp_id => NULL,
697 p_user_id => fnd_global.user_id,
698 p_login_id => fnd_global.conc_login_id,
699 p_org_id => NULL,
700 p_request_id => NULL,
701 p_request_number => NULL,
702 p_service_request_rec => l_service_request_rec,
703 p_notes => l_notes_table,
704 p_contacts => l_contacts_table,
705 x_request_id => l_incident_id,
706 x_request_number => l_incident_number,
707 x_interaction_id => ln_interaction_id,
708 x_workflow_process_id => ln_workflow_id);
709
710 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
711 Debug( 'Create_servicerequest API failed',l_mod_name,1);
712 RAISE SKIP_RECORD;
713 END IF;
714 l_sr_count := l_sr_count + 1;
715 Debug( 'New Incident Number ='||l_incident_number,l_mod_name,1);
716 END IF;
717
718 -- Check if Item is serialized item
719 --
720 Begin
721 Select 'x'
722 into l_dummy
723 from mtl_system_items
724 where inventory_item_id = c1.inventory_item_id
725 and organization_id = c1.organization_id
726 and serial_number_control_code <> 1;
727 l_serialized_flag := TRUE;
728 Debug( 'Inv Item is serialized Inv Item Id='||to_char(c1.inventory_item_id),l_mod_name,1);
729 Exception
730 When No_Data_Found then
731 IF ( l_error_level >= G_debug_level) THEN
732 fnd_message.set_name('CSD','CSD_INV_ITEM_ID');
733 fnd_message.set_token('ITEM_ID',c1.inventory_item_id);
734 FND_LOG.MESSAGE(l_error_level,l_mod_name, FALSE);
735 ELSE
736 fnd_message.set_name('CSD','CSD_INV_ITEM_ID');
737 fnd_message.set_token('ITEM_ID',c1.inventory_item_id);
738 fnd_msg_pub.add;
739 END IF;
740 l_serialized_flag := FALSE;
741 Debug( 'Inv Item is Non-serialized Inv Item Id='||to_char(c1.inventory_item_id),l_mod_name,1);
742 End;
743
744 --Initialize the values
745 l_count := 0;
746 l_rep_line_tbl.delete;
747
748 If NOT(l_serialized_flag) then
749
750 -- Increment the counter
751 l_count := l_count +1;
752
753 l_rep_line_tbl(l_count).INCIDENT_ID := l_incident_id ;
754 l_rep_line_tbl(l_count).INVENTORY_ITEM_ID := C1.inventory_item_id;
755 l_rep_line_tbl(l_count).UNIT_OF_MEASURE := C1.order_quantity_uom;
756 l_rep_line_tbl(l_count).REPAIR_TYPE_ID := l_repair_type_id ;
757 l_rep_line_tbl(l_count).REPAIR_MODE := l_repair_mode ;
758 l_rep_line_tbl(l_count).STATUS := 'O' ;
759 l_rep_line_tbl(l_count).STATUS_REASON_CODE := FND_API.G_MISS_CHAR;
760 l_rep_line_tbl(l_count).DATE_CLOSED := FND_API.G_MISS_DATE;
761 l_rep_line_tbl(l_count).APPROVAL_REQUIRED_FLAG := nvl(l_approval_flag,'N');
762 l_rep_line_tbl(l_count).APPROVAL_STATUS := FND_API.G_MISS_CHAR;
763 l_rep_line_tbl(l_count).QUANTITY := c1.shipped_quantity ;
764 l_rep_line_tbl(l_count).QUANTITY_IN_WIP := FND_API.G_MISS_NUM;
765 l_rep_line_tbl(l_count).QUANTITY_RCVD := c1.shipped_quantity;
766 l_rep_line_tbl(l_count).QUANTITY_SHIPPED := FND_API.G_MISS_NUM;
767 -- Vijay:3840775: change default for group id to null from g_miss_num
768 l_rep_line_tbl(l_count).REPAIR_GROUP_ID := null;
769 l_rep_line_tbl(l_count).RO_TXN_STATUS := 'OM_BOOKED' ;
770 l_rep_line_tbl(l_count).SERIAL_NUMBER := FND_API.G_MISS_CHAR;
771 l_rep_line_tbl(l_count).REPAIR_NUMBER := FND_API.G_MISS_CHAR;
772 l_rep_line_tbl(l_count).original_source_reference := 'RMA';
773 l_rep_line_tbl(l_count).original_source_header_id := c1.rma_header_id;
774 l_rep_line_tbl(l_count).original_source_line_id := c1.line_id;
775 l_rep_line_tbl(l_count).currency_code := c1.transactional_curr_code;
776 l_rep_line_tbl(l_count).price_list_header_id := c1.price_list_id;
777
778 --l_rep_line_tbl(l_count).object_version_number := 1;
779
780 ELSE
781
782 FOR C2 in Get_Serial_Num(c1.transaction_id)
783 LOOP
784
785 Begin
786 Select
787 instance_id
788 into l_instance_id
789 from csi_item_instances
790 where serial_number = C2.serial_num
791 and inventory_item_id = C1.inventory_item_id
792 and owner_party_id = C1.party_id;
793 Exception
794 When No_Data_Found then
795 IF ( l_error_level >= G_debug_level) THEN
796 fnd_message.set_name('CSD','CSD_IB_INSTANCE_MISSING');
797 fnd_message.set_token('SERIAL_NUM', C2.serial_num);
798 FND_LOG.MESSAGE(l_error_level,l_mod_name, FALSE);
799 ELSE
800 fnd_message.set_name('CSD','CSD_IB_INSTANCE_MISSING');
801 fnd_message.set_token('SERIAL_NUM', C2.serial_num);
802 fnd_msg_pub.add;
803 END IF;
804 Debug( 'Instance Id could not be found='||c2.serial_num,l_mod_name,1);
805 l_instance_id := FND_API.G_MISS_NUM;
806 End;
807
808 -- Increment the counter
809 l_count := l_count +1;
810
811 l_rep_line_tbl(l_count).INCIDENT_ID := l_incident_id ;
812 l_rep_line_tbl(l_count).INVENTORY_ITEM_ID := C1.inventory_item_id;
813 l_rep_line_tbl(l_count).UNIT_OF_MEASURE := C1.order_quantity_uom;
814 l_rep_line_tbl(l_count).REPAIR_TYPE_ID := l_repair_type_id ;
815 l_rep_line_tbl(l_count).REPAIR_MODE := l_repair_mode ;
816 l_rep_line_tbl(l_count).STATUS := 'O' ;
817 l_rep_line_tbl(l_count).STATUS_REASON_CODE := FND_API.G_MISS_CHAR;
818 l_rep_line_tbl(l_count).DATE_CLOSED := FND_API.G_MISS_DATE;
819 l_rep_line_tbl(l_count).APPROVAL_REQUIRED_FLAG := nvl(l_approval_flag,'N');
820 l_rep_line_tbl(l_count).APPROVAL_STATUS := FND_API.G_MISS_CHAR;
821 l_rep_line_tbl(l_count).QUANTITY := 1 ;
822 l_rep_line_tbl(l_count).QUANTITY_IN_WIP := FND_API.G_MISS_NUM;
823 l_rep_line_tbl(l_count).QUANTITY_RCVD := 1;
824 l_rep_line_tbl(l_count).QUANTITY_SHIPPED := FND_API.G_MISS_NUM;
825 -- Vijay:3840775: change default for group id to null from g_miss_num
826 l_rep_line_tbl(l_count).REPAIR_GROUP_ID := null;
827 l_rep_line_tbl(l_count).RO_TXN_STATUS := 'OM_BOOKED' ;
828 l_rep_line_tbl(l_count).SERIAL_NUMBER := C2.serial_num;
829 l_rep_line_tbl(l_count).CUSTOMER_PRODUCT_ID := l_instance_id;
830 l_rep_line_tbl(l_count).REPAIR_NUMBER := FND_API.G_MISS_CHAR;
831 l_rep_line_tbl(l_count).original_source_reference := 'RMA';
832 l_rep_line_tbl(l_count).original_source_header_id := c1.rma_header_id;
833 l_rep_line_tbl(l_count).original_source_line_id := c1.line_id;
834 l_rep_line_tbl(l_count).currency_code := c1.transactional_curr_code;
835 l_rep_line_tbl(l_count).price_list_header_id := c1.price_list_id;
836 --l_rep_line_tbl(l_count).object_version_number := 1;
837
838 END LOOP;
839 END IF;
840
841 Debug( 'l_rep_line_tbl.count ='||l_rep_line_tbl.count,l_mod_name,1);
842 FOR i in l_rep_line_tbl.first..l_rep_line_tbl.last
843 LOOP
844 BEGIN
845
846 -- Savepoint
847 Savepoint create_ro;
848
849 l_REPAIR_line_id := NULL;
850
851 Debug( 'Calling CSD_REPAIRS_PVT.Create_Repair_Order',l_mod_name,1);
852 CSD_REPAIRS_PVT.Create_Repair_Order
853 ( P_Api_Version_Number => 1.0,
854 P_Init_Msg_List => FND_API.G_TRUE,
855 P_Commit => FND_API.G_FALSE,
856 p_validation_level => fnd_api.g_valid_level_full,
857 p_Repair_line_id => l_REPAIR_line_id,
858 P_REPLN_Rec => l_rep_line_tbl(i) ,
859 X_REPAIR_LINE_ID => l_REPAIR_LINE_ID,
860 X_REPAIR_NUMBER => l_REPAIR_NUMBER,
861 X_Return_Status => l_return_status,
862 X_Msg_Count => l_msg_count,
863 X_Msg_Data => l_msg_data );
864
865 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
866 Debug( 'Create Repair Order API failed',l_mod_name,1);
867 RAISE SKIP_RO;
868 END IF;
869
870 Debug( 'New Repair Number ='||l_REPAIR_NUMBER ,l_mod_name,1);
871 l_ro_count := l_ro_count + 1;
872
873 Debug( 'Calling CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write ',l_mod_name,1);
874 CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write
875 ( P_Api_Version_Number => 1.0,
876 P_Init_Msg_List => 'T',
877 P_Commit => 'F',
878 p_validation_level => null,
879 p_action_code => 0 ,
880 px_REPAIR_HISTORY_ID => l_rep_hist_id,
881 p_OBJECT_VERSION_NUMBER => null,
882 p_REQUEST_ID => null,
883 p_PROGRAM_ID => null,
884 p_PROGRAM_APPLICATION_ID => null,
885 p_PROGRAM_UPDATE_DATE => null,
886 p_CREATED_BY => FND_GLOBAL.USER_ID,
887 p_CREATION_DATE => sysdate,
888 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
889 p_LAST_UPDATE_DATE => sysdate,
890 p_REPAIR_LINE_ID => l_repair_line_id,
891 p_EVENT_CODE => 'RR',
892 p_EVENT_DATE => C1.received_date,
893 p_QUANTITY => l_rep_line_tbl(i).QUANTITY,
894 p_PARAMN1 => C1.transaction_id,
895 p_PARAMN2 => C1.rma_line_number,
896 p_PARAMN3 => C1.organization_id,
897 p_PARAMN4 => NULL,
898 p_PARAMN5 => NULL,
899 p_PARAMN6 => C1.rma_header_id,
900 p_PARAMN7 => null,
901 p_PARAMN8 => null,
902 p_PARAMN9 => null,
903 p_PARAMN10 => null,
904 p_PARAMC1 => C1.subinventory,
905 p_PARAMC2 => C1.rma_number,
906 p_PARAMC3 => C1.org_name,
907 p_PARAMC4 => null,
908 p_PARAMC5 => null,
909 p_PARAMC6 => null,
910 p_PARAMC7 => null,
911 p_PARAMC8 => null,
912 p_PARAMC9 => null,
913 p_PARAMC10 => null,
914 p_PARAMD1 => null,
915 p_PARAMD2 => null,
916 p_PARAMD3 => null,
917 p_PARAMD4 => null,
918 p_PARAMD5 => null,
919 p_PARAMD6 => null,
920 p_PARAMD7 => null,
921 p_PARAMD8 => null,
922 p_PARAMD9 => null,
923 p_PARAMD10 => null,
924 p_ATTRIBUTE_CATEGORY => null,
925 p_ATTRIBUTE1 => null,
926 p_ATTRIBUTE2 => null,
927 p_ATTRIBUTE3 => null,
928 p_ATTRIBUTE4 => null,
929 p_ATTRIBUTE5 => null,
930 p_ATTRIBUTE6 => null,
931 p_ATTRIBUTE7 => null,
932 p_ATTRIBUTE8 => null,
933 p_ATTRIBUTE9 => null,
934 p_ATTRIBUTE10 => null,
935 p_ATTRIBUTE11 => null,
936 p_ATTRIBUTE12 => null,
937 p_ATTRIBUTE13 => null,
938 p_ATTRIBUTE14 => null,
939 p_ATTRIBUTE15 => null,
940 p_LAST_UPDATE_LOGIN => null,
941 X_Return_Status => l_return_status,
942 X_Msg_Count => l_msg_count,
943 X_Msg_Data => l_msg_data );
944
945 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
946 Debug( 'Validate_And_Write failed ',l_mod_name,1);
947 RAISE SKIP_RO;
948 END IF;
949
950 EXCEPTION
951 WHEN SKIP_RO THEN
952 ROLLBACK TO create_ro;
953 l_error_count := l_error_count + 1;
954 if(fnd_msg_pub.count_msg > 0 ) then
955 for i in 1..fnd_msg_pub.count_msg
956 loop
957 fnd_msg_pub.get(p_msg_index => i,
958 p_encoded => 'F',
959 p_data => l_msg_data,
960 p_msg_index_out => l_msg_index_out);
961 Debug( 'Error Msg='||l_msg_data,l_mod_name,1);
962 end loop;
963 else
964 Debug( 'Error Msg='||l_msg_data,l_mod_name,1);
965 end if;
966 Debug( 'Skipping RO creation =',l_mod_name,1);
967 exit;
968 WHEN OTHERS THEN
969 ROLLBACK TO create_ro;
970 l_error_count := l_error_count + 1;
971 if(fnd_msg_pub.count_msg > 0 ) then
972 for i in 1..fnd_msg_pub.count_msg
973 loop
974 fnd_msg_pub.get(p_msg_index => i,
975 p_encoded => 'F',
976 p_data => l_msg_data,
977 p_msg_index_out => l_msg_index_out);
978 Debug( 'Error Msg='||l_msg_data,l_mod_name,1);
979 end loop;
980 else
981 Debug( 'Error Msg='||l_msg_data,l_mod_name,1);
982 end if;
983 Debug( 'Others exception in RO Craetion ',l_mod_name,1);
984 exit;
985 END;
986 END LOOP; -- End of Repair order creation
987
988 EXCEPTION
989 WHEN SKIP_RECORD THEN
990 ROLLBACK TO rcv_lines;
991 l_error_count := l_error_count + 1;
992 if(fnd_msg_pub.count_msg > 0 ) then
993 for i in 1..fnd_msg_pub.count_msg
994 loop
995 fnd_msg_pub.get(p_msg_index => i,
996 p_encoded => 'F',
997 p_data => l_msg_data,
998 p_msg_index_out => l_msg_index_out);
999 Debug( 'Error Msg='||l_msg_data,l_mod_name,1);
1000 end loop;
1001 else
1002 Debug( 'Error Msg='||l_msg_data,l_mod_name,1);
1003 end if;
1004 Debug( 'Skipping the record for order line_id ='||to_char(c1.line_id),l_mod_name,1);
1005 WHEN OTHERS THEN
1006 ROLLBACK TO rcv_lines;
1007 l_error_count := l_error_count + 1;
1008 if(fnd_msg_pub.count_msg > 0 ) then
1009 for i in 1..fnd_msg_pub.count_msg
1010 loop
1011 fnd_msg_pub.get(p_msg_index => i,
1012 p_encoded => 'F',
1013 p_data => l_msg_data,
1014 p_msg_index_out => l_msg_index_out);
1015 Debug( 'Error Msg='||l_msg_data,l_mod_name,1);
1016 end loop;
1017 else
1018 Debug( 'Error Msg='||l_msg_data,l_mod_name,1);
1019 end if;
1020 Debug( 'In Others exception',l_mod_name,1);
1021 END;
1022 END LOOP; --End of processing all rcv lines
1023
1024 /*
1025 fnd_file.put_line(fnd_file.output, '================================================== ');
1026 fnd_file.put_line(fnd_file.output, '********** SUMMARY OF PROCESSED RECORDS ********** ');
1027 fnd_file.put_line(fnd_file.output, '================================================== ');
1028 fnd_file.put_line(fnd_file.output, ' Total Number of Service Request Created = '||to_char(l_sr_count));
1029 fnd_file.put_line(fnd_file.output, ' Total Number of Repair Orders Created = '||to_char(l_ro_count));
1030 fnd_file.put_line(fnd_file.output, ' Total Number of Order Lines Errored = '||to_char(l_error_count));
1031 fnd_file.put_line(fnd_file.output, '================================================== ');
1032 */
1033
1034 errbuf := '';
1035 retcode := '0';
1036 --- Retcode = 0 is success
1037 --- Retcode = 1 is warning
1038 --- Retcode = 2 is error
1039
1040 -- Commit
1041 COMMIT WORK;
1042
1043 EXCEPTION
1044 WHEN FND_API.G_EXC_ERROR THEN
1045 ROLLBACK TO process_rma;
1046 if(fnd_msg_pub.count_msg > 0 ) then
1047 for i in 1..fnd_msg_pub.count_msg
1048 loop
1049 fnd_msg_pub.get(p_msg_index => i,
1050 p_encoded => 'F',
1051 p_data => l_msg_data,
1052 p_msg_index_out => l_msg_index_out);
1053 Debug( 'Error Msg='||l_msg_data,l_mod_name,1);
1054 end loop;
1055 else
1056 Debug( 'Error Msg='||l_msg_data,l_mod_name,1);
1057 end if;
1058 errbuf := 'Error occurred in Concurrent Program:'||l_msg_data;
1059 retcode := '2';
1060 WHEN OTHERS THEN
1061 ROLLBACK TO process_rma;
1062 if(fnd_msg_pub.count_msg > 0 ) then
1063 for i in 1..fnd_msg_pub.count_msg
1064 loop
1065 fnd_msg_pub.get(p_msg_index => i,
1066 p_encoded => 'F',
1067 p_data => l_msg_data,
1068 p_msg_index_out => l_msg_index_out);
1069 Debug( 'Error Msg='||l_msg_data,l_mod_name,1);
1070 end loop;
1071 else
1072 Debug( 'Error Msg='||l_msg_data,l_mod_name,1);
1073 end if;
1074 errbuf := 'Error occurred in Concurrent Program:'||l_msg_data;
1075 retcode := '2';
1076 END PROCESS_RMA;
1077
1078 /*-------------------------------------------------------------------------------------*/
1079 /* Procedure name: GET_SR_CONTACTS */
1080 /* Description : Creates SR/RO against RMA */
1081 /* Called from : Called from Concurrent Program */
1082 /* */
1083 /* STANDARD PARAMETERS */
1084 /* In Parameters : */
1085 /* p_contact_id Required Contact Id */
1086 /* Output Parameters: */
1087 /* NON-STANDARD PARAMETERS */
1088 /* In Parameters */
1089 /* Out parameters */
1090 /* Change Hist : */
1091 /* vparvath Initial creation */
1092 /* 02/16/04 vlakaman Included in the latest APIs */
1093 /*-------------------------------------------------------------------------------------*/
1094
1095 FUNCTION GET_SR_CONTACTS(p_contact_id NUMBER)
1096 RETURN CS_SERVICEREQUEST_PUB.CONTACTS_TABLE IS
1097
1098 -- cursor definitions
1099 CURSOR CUR_CONTACTS(p_contact_id NUMBER) IS
1100 SELECT acct_role.party_id party_id, C.contact_point_id,
1101 C.contact_point_type,C.primary_flag
1102 FROM hz_contact_points C,
1103 HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
1104 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id
1105 and C.owner_table_name(+)='HZ_PARTIES'
1106 and C.contact_point_type(+)='PHONE'
1107 and C.status(+)='A'
1108 and C.primary_flag(+)='Y'
1109 and acct_role.party_id=C.owner_table_id(+) ;
1110
1111 l_contacts_table CS_ServiceRequest_PUB.contacts_table;
1112 i NUMBER;
1113
1114 -- Variables used in FND Log
1115 l_error_level number := FND_LOG.LEVEL_ERROR;
1116 l_mod_name varchar2(2000) := 'csd.plsql.csd_om_interface_pvt.get_sr_contacts';
1117
1118 BEGIN
1119 Debug( 'At the Beginning of GET_SR_CONTACTS',l_mod_name,1);
1120
1121 i := 1;
1122 FOR ct_point_rec in CUR_CONTACTS(p_contact_id) LOOP
1123 Debug( 'In For Loop building the Contact table ',l_mod_name,1);
1124 If(ct_point_rec.contact_point_id is null) then
1125 l_contacts_table(i).party_id := ct_point_rec.party_id;
1126 l_contacts_table(i).primary_flag := 'Y';
1127 l_contacts_table(i).contact_type := 'PARTY_RELATIONSHIP';
1128 Else
1129 l_contacts_table(i).party_id := ct_point_rec.party_id;
1130 l_contacts_table(i).contact_point_id := ct_point_rec.contact_point_id;
1131 l_contacts_table(i).contact_point_type := ct_point_rec.contact_point_type;
1132 l_contacts_table(i).primary_flag := nvl(ct_point_rec.primary_flag,'N');
1133 l_contacts_table(i).contact_type := 'PARTY_RELATIONSHIP';
1134 End if;
1135 i := i + 1;
1136 END LOOP;
1137 return l_contacts_table;
1138
1139 END GET_SR_CONTACTS;
1140
1141 END CSD_OM_INTERFACE_PVT;