[Home] [Help]
PACKAGE BODY: APPS.CSD_ISUP_DEPOT_WF_PVT
Source
1 PACKAGE BODY CSD_ISUP_DEPOT_WF_PVT AS
2 /* $Header: csdviswb.pls 120.7 2010/08/31 11:12:12 subhat ship $ */
3
4 /*-----------------------------------------------------------------*/
5 /* NOTE - This is the new workflow from isupport to depot integration*/
6 /*-----------------------------------------------------------------*/
7
8
9 /*-----------------------------------------------------------------*/
10 /* procedure name: create_ro_wf */
11 /* description : Create RO and Logistics for a SR */
12 /* */
13 /*-----------------------------------------------------------------*/
14
15
16 g_incident_number VARCHAR2(50);
17 g_business_process_id NUMBER;
18
19 PROCEDURE check_sr_details_wf(itemtype in varchar2,
20 itemkey in varchar2,
21 actid in number,
22 funcmode in varchar2,
23 resultout out NOCOPY varchar2) is
24
25 -- Cursor to get the Business Process id..
26 -- fix for bug#9275129, subhat.
27
28 cursor get_business_process(p_incident_number in varchar2) is
29 SELECT cit.business_process_id
30 FROM cs_incidents_all_b ci,
31 cs_incident_types_b cit,
32 cs_business_processes cbp
33 where ci.incident_number = g_incident_number
34 and ci.incident_type_id = cit.incident_type_id
35 and cbp.business_process_id = cit.business_process_id
36 and cbp.name = 'Depot Repair';
37
38 lc_mod_name varchar2(75) := 'csd_plsql_csd_isup_depot_wf_pvt.check_sr_details_wf';
39
40 BEGIN
41
42 if funcmode = 'RUN' then
43 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
44 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
45 lc_mod_name||'begin',
46 'Entering Private API check_sr_details_wf');
47 END IF;
48 -- This parameter is coming from Workflow event.
49 g_incident_number := wf_engine.GETITEMATTRTEXT
50 (itemtype => itemtype,
51 itemkey => itemkey,
52 aname => 'REQUEST_NUMBER');
53
54 -- Cursors to get SR detials
55 OPEN get_business_process(g_incident_number);
56 FETCH get_business_process INTO g_business_process_id;
57 CLOSE get_business_process;
58
59 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
60 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
61 lc_mod_name||'begin',
62 'Business process retrieved is: '||g_business_process_id);
63 END IF;
64 -- Check if the business process used is depot repair. If it is depot repair then only continue
65 -- with creating the RO and default product transaction lines.
66
67 IF nvl(g_business_process_id,0) <> 0 THEN
68 resultout := 'Y';
69 ELSE
70 resultout := 'N';
71 END IF;
72 END IF;
73 END check_sr_details_wf;
74
75
76 ---------- For creating RO -- Will be called after SR is created for Depot.
77 -- create RO if the SR created for Depot Repair Business process.
78
79 PROCEDURE create_ro_wf ( itemtype IN varchar2,
80 itemkey in varchar2,
81 actid in number,
82 funcmode in varchar2,
83 resultout out NOCOPY varchar2) is
84
85 l_incident_id NUMBER;
86 l_sr_number VARCHAR2(64);
87 l_repair_type_id NUMBER;
88 l_inventory_item_id NUMBER;
89 l_current_serial_number VARCHAR2(30);
90 l_customer_product_id NUMBER;
91 l_inv_organization_id NUMBER;
92 l_unit_of_measure VARCHAR2(10);
93 l_repair_mode VARCHAR2(15);
94 l_org_id NUMBER;
95 l_calc_resptime_flag VARCHAR2(1) := 'Y';
96 l_server_tz_id NUMBER;
97 l_business_process_id NUMBER;
98 l_contract_line_id NUMBER;
99 l_contract_number NUMBER := NULL;
100 l_contract_pl_id NUMBER;
101 l_currency_code VARCHAR2(5);
102 l_profile_pl_id NUMBER;
103 l_contract_id NUMBER :=null;
104 l_customer_id NUMBER;
105 l_bill_to_site_id NUMBER;
106 l_account_id NUMBER;
107 l_creation_date DATE;
108 l_incident_severity_id NUMBER;
109 l_price_list_id NUMBER;
110 l_wf_role VARCHAR2(320);
111 l_wf_role_display_name VARCHAR2(360);
112 l_email VARCHAR2(2000);
113 l_contact_name VARCHAR2(360);
114 l_serial_number VARCHAR2(30);
115 l_item_name VARCHAR2(40);
116 l_resolve_by_date DATE;
117 -- swai: 12.1.1 bug 7176940 service bulletin check
118 l_ro_sc_ids_tbl CSD_RO_BULLETINS_PVT.CSD_RO_SC_IDS_TBL_TYPE;
119 l_rule_input_rec CSD_RULES_ENGINE_PVT.CSD_RULE_INPUT_REC_TYPE;
120 l_ship_to_site_use_id NUMBER;
121 l_problem_code NUMBER;
122 l_default_rule_id NUMBER;
123
124 notes_message VARCHAR2(2000);
125 l_message VARCHAR2(4000);
126 l_transaction_status VARCHAR2(30);
127 l_order_number NUMBER;
128 l_auto_process_rma VARCHAR2(30);
129
130 -- out params.
131 l_ent_contracts OKS_ENTITLEMENTS_PUB.GET_CONTOP_TBL;
132 x_repair_line_id NUMBER;
133 x_repair_number VARCHAR2(15);
134 x_return_status VARCHAR2(1);
135 x_msg_count NUMBER;
136 x_msg_data VARCHAR2(2000);
137 x_JTF_NOTE_ID NUMBER ;
138
139 l_ro_exception EXCEPTION;
140 l_prod_exception EXCEPTION;
141 lc_mod_name CONSTANT VARCHAR2(75) := 'csd_plsql_csd_isup_depot_wf_pvt.create_ro_wf';
142
143 ---- Cursor to get Repair Mode
144
145 CURSOR get_repair_mode(p_repair_type_id IN NUMBER ) IS
146 SELECT REPAIR_MODE
147 FROM csd_repair_types_b
148 WHERE repair_type_id=p_repair_type_id;
149
150 -- cursor to get the item details.
151
152 CURSOR item_details(p_incident_number IN NUMBER) IS
153 SELECT cs.incident_id,
154 cs.org_id,
155 cs.inventory_item_id,
156 cs.current_serial_number,
157 cs.customer_product_id,
158 cs.inv_organization_id,
159 cs.customer_id,
160 cs.bill_to_site_use_id,
161 cs.account_id,
162 cs.incident_date,
163 cs.incident_severity_id,
164 mtl.primary_uom_code,
165 cit.business_process_id,
166 cs.contract_id,
167 cs.contract_service_id,
168 cs.ship_to_site_use_id,
169 cs.problem_code
170 FROM cs_incidents_all_b cs,
171 mtl_system_items_b mtl,
172 cs_incident_types cit
173 WHERE
174 cs.inventory_item_id = mtl.inventory_item_id AND
175 cs.inv_organization_id = mtl.organization_id AND
176 cs.INCIDENT_NUMBER = p_incident_number AND
177 cs.incident_type_id = cit.incident_type_id;
178
179
180 ----- RO Attributes for Notifications
181 CURSOR get_ro_attributes (p_repair_line_id IN NUMBER) IS
182 SELECT sr.cont_email,
183 ro.repair_number,
184 ro.serial_number,
185 ro.item,
186 decode(sr.contact_type,'EMPLOYEE',sr.first_name||' '||sr.last_name,sr.full_name) contact_name
187 FROM csd_incidents_v sr,
188 csd_repairs_v ro
189 WHERE ro.incident_id = sr.incident_id
190 AND ro.repair_line_id = p_repair_line_id;
191
192 CURSOR get_wf_role (p_repair_line_id IN NUMBER) IS
193 SELECT wr.name
194 FROM wf_roles wr,
195 cs_incidents_v sr,
196 csd_repairs ro
197 WHERE ro.repair_line_id = p_repair_line_id
198 AND ro.incident_id = sr.incident_id
199 AND wr.orig_system_id = sr.contact_party_id
200 AND wr.orig_system = 'HZ_PARTY'
201 AND nvl(wr.expiration_date,sysdate) >= sysdate
202 AND wr.status = 'ACTIVE';
203
204 -- Cursor to Check if the line is booked.
205
206 CURSOR get_line_status(p_repair_line_id IN NUMBER) IS
207 SELECT prod_txn_status,
208 order_number
209 FROM csd_product_txns_v
210 WHERE repair_line_id = p_repair_line_id
211 AND action_type='RMA';
212
213 BEGIN
214
215 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
216 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, lc_mod_name||'begin',
217 'Entering Private API create_ro_wf');
218 END IF;
219 -- fetch the item details.
220 OPEN item_details(g_incident_number);
221 FETCH item_details INTO
222 l_incident_id,
223 l_org_id,
224 l_inventory_item_id,
225 l_current_serial_number,
226 l_customer_product_id,
227 l_inv_organization_id,
228 l_customer_id,
229 l_bill_to_site_id,
230 l_account_id,
231 l_creation_date,
232 l_incident_severity_id,
233 l_unit_of_measure,
234 l_business_process_id,
235 l_contract_id,
236 l_contract_line_id,
237 l_ship_to_site_use_id,
238 l_problem_code;
239 CLOSE item_details;
240
241 -- bug#9275129, subhat
242 -- if the instance_id is not there on the SR, check if its present in IB.
243 IF l_current_serial_number IS NOT NULL AND l_customer_product_id IS NULL THEN
244 BEGIN
245 SELECT instance_id
246 INTO l_customer_product_id
247 FROM csi_item_instances
248 WHERE inventory_item_id = l_inventory_item_id
249 AND serial_number = l_current_serial_number
250 AND sysdate BETWEEN trunc(NVL(active_start_date, sysdate -1)) AND
251 trunc(NVL(active_end_date,sysdate+1));
252 EXCEPTION
253 WHEN no_data_found THEN
254 null;
255 END;
256 END IF;
257
258 -- bug#9581916, subhat.
259 -- derive the contract information.
260 fnd_profile.get('SERVER_TIMEZONE_ID', l_server_tz_id);
261
262 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
263 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
264 'Calling CSD_REPAIRS_UTIL.GET_ENTITLEMENTS to get contracts if any.');
265 END IF;
266 CSD_REPAIRS_UTIL.GET_ENTITLEMENTS(
267 p_api_version_number => 1.0,
268 p_init_msg_list => fnd_api.g_false,
269 p_commit => fnd_api.g_false,
270 p_contract_number => null,
271 p_service_line_id => null,
272 p_customer_id => l_customer_id ,
273 p_site_id => l_bill_to_site_id,
274 p_customer_account_id => l_account_id,
275 p_system_id => null,
276 p_inventory_item_id => l_inventory_item_id,
277 p_customer_product_id => l_customer_product_id,
278 p_request_date => trunc(sysdate),
279 p_validate_flag => 'Y',
280 p_business_process_id => l_business_process_id,
281 p_severity_id => l_incident_severity_id,
282 p_time_zone_id => l_server_tz_id,
283 P_CALC_RESPTIME_FLAG => l_calc_resptime_flag,
284 x_ent_contracts => l_ent_contracts,
285 x_return_status => x_return_status,
286 x_msg_count => x_msg_count,
287 x_msg_data => x_msg_data);
288
289 IF l_ent_contracts.COUNT = 0 THEN
290 l_contract_line_id := null;
291 ELSE
292 FOR l_index IN l_ent_contracts.FIRST..l_Ent_contracts.LAST
293 LOOP
294 IF (l_contract_id = l_ent_contracts(l_index).contract_id AND
295 l_contract_line_id = l_ent_contracts(l_index).service_line_id) THEN
296
297 l_contract_line_id := l_ent_contracts(l_index).service_line_id;
298 l_contract_id := l_ent_contracts(l_index).contract_id;
299 EXIT;
300
301 END IF;
302 END LOOP;
303
304 IF l_contract_line_id IS NULL THEN
305 l_contract_line_id := l_ent_contracts(1).service_line_id;
306 l_contract_id := l_ent_contracts(1).contract_id;
307 END IF;
308 END IF;
309
310 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
311 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
312 'Contract derived is:'||nvl(l_contract_line_id,0));
313 END IF;
314
315 -- we have to get the repair type id here. Because without repair type id
316 -- the price list calculation will be incomplete/error out.
317 l_rule_input_rec.sr_customer_id := l_customer_id;
318 l_rule_input_rec.sr_customer_account_id := l_account_id;
319 l_rule_input_rec.sr_bill_to_site_use_id := l_bill_to_site_id;
320 l_rule_input_rec.sr_ship_to_site_use_id := l_ship_to_site_use_id;
321 l_rule_input_rec.sr_problem_code := l_problem_code;
322 l_rule_input_rec.sr_contract_id := l_contract_id;
323
324 l_rule_input_rec.ro_item_id := l_inventory_item_id;
325
326 l_repair_type_id := null;
327 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
328 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
329 'Calling CSD_RULES_ENGINE_PVT.GET_DEFAULT_VALUE_FROM_RULE to get default repair type');
330 END IF;
331
332 CSD_RULES_ENGINE_PVT.GET_DEFAULT_VALUE_FROM_RULE(
333 p_api_version_number => 1.0,
334 p_init_msg_list => fnd_api.g_false,
335 p_commit => fnd_api.g_false,
336 p_validation_level => fnd_api.g_valid_level_full,
337 p_entity_attribute_type => 'CSD_DEF_ENTITY_ATTR_RO',
338 p_entity_attribute_code => 'REPAIR_TYPE',
339 p_rule_input_rec => l_rule_input_rec,
340 x_default_value => l_repair_type_id,
341 x_rule_id => l_default_rule_id,
342 x_return_status => x_return_status,
343 x_msg_count => x_msg_count,
344 x_msg_data => x_msg_data
345 );
346
347 IF l_default_rule_id IS NULL THEN
348 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
349 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
350 'No Default rules set up for repair type. Fetching it from profile');
351 END IF;
352 l_repair_type_id := FND_PROFILE.VALUE('CSD_DEFAULT_REPAIR_TYPE');
353 END IF;
354
355 -- get the auto process rma flag. bug#9581916, subhat.
356 SELECT nvl(auto_process_rma,'N')
357 INTO l_auto_process_rma
358 FROM csd_repair_types_vl
359 WHERE repair_type_id = l_repair_type_id;
360
361 -- get PL and Currency. -- bug#9581916, subhat
362 csd_process_util.get_ro_default_curr_pl
363 ( p_api_version => 1.0,
364 p_init_msg_list => fnd_api.g_false,
365 p_incident_id => l_incident_id,
366 p_repair_type_id => l_repair_type_id,
367 p_ro_contract_line_id => l_contract_line_id,
368 x_contract_pl_id => l_contract_pl_id,
369 x_profile_pl_id => l_profile_pl_id,
370 x_currency_code => l_currency_code,
371 x_return_status => x_return_status,
372 x_msg_count => x_msg_count,
373 x_msg_data => x_msg_data );
374
375 IF ( l_contract_pl_id IS NOT NULL) THEN
376 l_price_list_id := l_contract_pl_id;
377 ELSIF ( l_profile_pl_id IS NOT NULL ) THEN
378 l_price_list_id := l_profile_pl_id;
379 END IF;
380
381 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
382 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
383 'Pricelist derived is:'||nvl(l_price_list_id,0));
384 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
385 'Currency code derived is:'||nvl(l_currency_code,'N'));
386 END IF;
387 -- if the contract line is not null then try to default resolve by date.
388 IF l_contract_line_id IS NOT NULL THEN
389 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
390 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
391 'Deriving resolve by date');
392 END IF;
393 csd_repairs_util.get_contract_resolve_by_date(
394 p_contract_line_id => l_contract_line_id,
395 p_bus_proc_id => l_business_process_id,
396 p_severity_id => l_incident_severity_id,
397 p_request_date => sysdate,
398 x_return_status => x_return_status,
399 x_msg_count => x_msg_count,
400 x_msg_data => x_msg_data,
401 x_resolve_by_date => l_resolve_by_date);
402 IF (x_return_status = Fnd_Api.G_RET_STS_ERROR) THEN
403 l_resolve_by_date := null;
404 ELSIF (x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR) THEN
405 l_resolve_by_date := null;
406 END IF;
407 END IF;
408 -- set if the multi org security context, if its not already set.
409 -- without this all the secured apps data may not be visible.
410
411 IF mo_global.is_mo_init_done = 'N' THEN
412 mo_global.set_policy_context('S',l_org_id);
413 END IF;
414
415 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
416 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,lc_mod_name||'begin',
417 'calling create repair order public API');
418 END IF;
419 -- API call to create the repair orders.
420
421 CSD_REPAIRS_GRP.Create_Repair_Order
422 (p_init_msg_list => 'T',
423 p_commit => 'F',
424 p_repair_line_id =>null,
425 p_repair_number => null,
426 p_incident_id => l_incident_id,
427 p_inventory_item_id => l_inventory_item_id,
428 p_customer_product_id => l_customer_product_id,
429 p_unit_of_measure => l_unit_of_measure,
430 p_repair_type_id =>l_repair_type_id,
431 p_resource_id => fnd_api.g_miss_num,
432 p_resource_group => fnd_api.g_miss_num,
433 p_project_id => null,
434 p_task_id => null,
435 p_unit_number => null,
436 p_contract_line_id => l_contract_line_id,
437 p_auto_process_rma => l_auto_process_rma,
438 p_repair_mode => null,
439 p_object_version_number => null,
440 p_item_revision => null,
441 p_instance_id => null,
442 p_status => 'O',
443 p_status_reason_code => null,
444 p_date_closed => null,
445 p_approval_required_flag => NVL(fnd_profile.value('CSD_CUST_APPROVAL_REQD'),'N'),
446 p_approval_status =>null,
447 p_serial_number => l_current_serial_number,
448 p_promise_date => null,
449 p_attribute_category => null,
450 p_attribute1 => null,
451 p_attribute2 => null,
452 p_attribute3 => null,
453 p_attribute4 => null,
454 p_attribute5 => null,
455 p_attribute6 => null,
456 p_attribute7 => null,
457 p_attribute8 => null,
458 p_attribute9 => null,
459 p_attribute10 => null,
460 p_attribute11 => null,
461 p_attribute12 => null,
462 p_attribute13 => null,
463 p_attribute14 => null,
464 p_attribute15 => null,
465 p_attribute16 => null, -- bug#7497907, 12.1 FP, subhat
466 p_attribute17 => null,
467 p_attribute18 => null,
468 p_attribute19 => null,
469 p_attribute20 => null,
470 p_attribute21 => null,
471 p_attribute22 => null,
472 p_attribute23 => null,
473 p_attribute24 => null,
474 p_attribute25 => null,
475 p_attribute26 => null,
476 p_attribute27 => null,
477 p_attribute28 => null,
478 p_attribute29 => null,
479 p_attribute30 => null,
480 p_quantity => 1,
481 p_quantity_in_wip => null,
482 p_quantity_rcvd =>null,
483 p_quantity_shipped =>null,
484 p_currency_code => l_currency_code,
485 p_default_po_num => null,
486 p_repair_group_id => null,
487 p_ro_txn_status => null,
488 p_order_line_id => null,
489 p_original_source_reference => null,
490 p_original_source_header_id => null,
491 p_original_source_line_id => null,
492 p_price_list_header_id => l_price_list_id,
493 p_inventory_org_id => fnd_api.g_miss_num,
494 p_problem_description => null,
495 p_ro_priority_code => fnd_api.g_miss_char,
496 p_resolve_by_date => l_resolve_by_date,
497 x_repair_line_id => x_repair_line_id,
498 x_repair_number => x_repair_number,
499 x_return_status => x_return_status,
500 x_msg_count => x_msg_count,
501 x_msg_data => x_msg_data);
502
503 IF x_return_status <> 'S' THEN
504 RAISE l_ro_exception;
505 END IF;
506
507 --call create Logistics procedure if repair id is not standard
508
509
510 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
511 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,lc_mod_name||'begin',
512 'calling create default product transactions API');
513 END IF;
514
515 csd_process_pvt.create_default_prod_txn
516 (p_api_version => 1.0,
517 p_commit => fnd_api.g_false,
518 p_init_msg_list => fnd_api.g_true,
519 p_validation_level => fnd_api.g_valid_level_full,
520 p_repair_line_id => x_repair_line_id,
521 x_return_status => x_return_status,
522 x_msg_count => x_msg_count,
523 x_msg_data => x_msg_data);
524
525 IF x_return_status <> 'S' THEN
526 RAISE l_prod_exception;
527 END IF;
528
529 -- swai: 12.1.1 bug 7176940 - check service bulletins after RO creation
530 IF (nvl(fnd_profile.value('CSD_AUTO_CHECK_BULLETINS'),'N') = 'Y') THEN
531 CSD_RO_BULLETINS_PVT.LINK_BULLETINS_TO_RO(
532 p_api_version_number => 1.0,
533 p_init_msg_list => Fnd_Api.G_FALSE,
534 p_commit => Fnd_Api.G_TRUE,
535 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
536 p_repair_line_id => x_repair_line_id,
537 px_ro_sc_ids_tbl => l_ro_sc_ids_tbl,
538 x_return_status => x_return_status,
539 x_msg_count => x_msg_count,
540 x_msg_data => x_msg_data
541 );
542 -- ignore return status for now.
543 END IF;
544
545 OPEN get_line_status(x_repair_line_id);
546 FETCH get_line_status INTO l_transaction_status,l_order_number;
547 CLOSE get_line_status;
548 -- get the notes message and replace the binds.
549 fnd_message.set_name('CSD','CSD_RO_CREATED_WF_NOTE');
550 fnd_message.set_token('SERVICE_REQUEST',g_incident_number);
551 fnd_message.set_token('REPAIR_NUMBER',x_repair_number);
552 IF upper(l_transaction_status)='BOOKED' THEN
553 fnd_message.set_token('RMA_NUMBER',l_order_number);
554 ELSE
555 fnd_message.set_token('RMA_NUMBER','not booked');
556 END IF;
557
558 notes_message := fnd_message.get;
559
560 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
561 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,lc_mod_name||'begin','calling create JTF Notes API');
562 END IF;
563
564 JTF_NOTES_PUB.Create_note
565 (
566 p_api_version => 1.0,
567 p_init_msg_list => FND_API.G_FALSE,
568 p_commit => FND_API.G_FALSE,
569 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
570 x_return_status => x_return_status,
571 x_msg_count => x_msg_count,
572 x_msg_data => x_msg_data,
573 P_NOTE_STATUS => 'E',
574 p_source_object_id => l_incident_id,
575 p_source_object_code => 'SR',
576 p_notes => notes_message,
577 p_entered_date => sysdate,
578 p_note_type => 'GENERAL',
579 x_jtf_note_id => x_JTF_NOTE_ID
580 );
581
582
583 IF x_return_status = 'S' THEN
584
585 -- Derive the wf roles for the Contact id
586 OPEN get_wf_role (x_repair_line_id);
587 FETCH get_wf_role INTO l_wf_role;
588 CLOSE get_wf_role;
589
590 OPEN get_ro_attributes (x_repair_line_id);
591 FETCH get_ro_attributes INTO l_email,x_repair_number,l_serial_number,
592 l_item_name,l_contact_name;
593 CLOSE get_ro_attributes;
594
595 -- derive the notification message.
596 fnd_message.set_name('CSD','CSD_RO_WF_NTF_MSG');
597 fnd_message.set_token('CONTACT_NAME',l_contact_name);
598 fnd_message.set_token('REPAIR_ORDER',x_repair_number);
599 fnd_message.set_token('SERVICE_REQUEST',g_incident_number);
600 fnd_message.set_token('RMA_NUMBER',l_order_number);
601
602 l_message := fnd_message.get;
603
604 -- If role does not exist the create adhoc wf role
605
606 IF ( l_wf_role IS NULL ) THEN
607
608 wf_directory.CreateAdHocRole
609 (role_name => l_wf_role,
610 role_display_name => l_wf_role_display_name,
611 language => 'AMERICAN',
612 territory => 'AMERICA',
613 role_description => 'CSD: Notify RO Details - Adhoc role',
614 notification_preference => 'MAILTEXT',
615 role_users => null,
616 email_address => l_email,
617 fax => null,
618 status => 'ACTIVE',
619 expiration_date => add_months(sysdate,36),
620 parent_orig_system => null,
621 parent_orig_system_id => null,
622 owner_tag => null);
623
624 END IF;
625
626 IF ( l_wf_role IS NOT NULL ) THEN
627
628 wf_engine.setItemAttrText
629 (itemtype => itemtype,
630 itemkey => itemkey,
631 aname => 'RECEIVER',
632 avalue => l_wf_role);
633
634 wf_engine.setItemAttrText
635 (itemtype => itemtype,
636 itemkey => itemkey,
637 aname => 'ITEM_NAME',
638 avalue => l_item_name);
639
640 wf_engine.setItemAttrText
641 (itemtype => itemtype,
642 itemkey => itemkey,
643 aname => 'SERIAL_NUMBER',
644 avalue => l_serial_number);
645
646 wf_engine.setItemAttrText
647 (itemtype => itemtype,
648 itemkey => itemkey,
649 aname => 'MESSAGE',
650 avalue => l_message);
651
652 END IF; -- end if RO created.
653
654 resultout := 'Y';
655
656 ELSE
657
658 resultout := 'N';
659
660 END IF;
661 COMMIT WORK;
662
663 EXCEPTION
664 WHEN l_ro_exception THEN
665 fnd_msg_pub.count_and_get(p_count => x_msg_count,
666 p_data => x_msg_data);
667 IF x_msg_count > 1 THEN
668 FOR i IN 1 ..x_msg_count
669 LOOP
670 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
671 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,lc_mod_name||'exception','Error count('||x_msg_count||') Error msg: '||fnd_msg_pub.get(p_msg_index => i));
672 END IF;
673 END LOOP;
674 ELSE
675 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
676 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,lc_mod_name||'exception','Error '||x_msg_data);
677 END IF;
678 END IF;
679 WF_CORE.CONTEXT ('CSD_ISUP_DEPOT_WF_PVT','create_ro_wf', itemtype,itemkey, to_char(actid),funcmode);
680 ROLLBACK;
681 RAISE;
682 WHEN l_prod_exception THEN
683 fnd_msg_pub.count_and_get(p_count => x_msg_count,
684 p_data => x_msg_data);
685 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
686 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,lc_mod_name||'exception','Error '||x_msg_data);
687 END IF;
688 WF_CORE.CONTEXT ('CSD_ISUP_DEPOT_WF_PVT','create_ro_wf', itemtype,itemkey, to_char(actid),funcmode);
689 ROLLBACK;
690 RAISE;
691 WHEN OTHERS THEN
692 WF_CORE.CONTEXT ('CSD_ISUP_DEPOT_WF_PVT','create_ro_wf', itemtype,itemkey, to_char(actid),funcmode);
693 RAISE;
694 ROLLBACK;
695 END create_ro_wf;
696
697 END CSD_ISUP_DEPOT_WF_PVT;