[Home] [Help]
PACKAGE BODY: APPS.CSD_WF_PROCESS_PVT
Source
1 PACKAGE BODY CSD_WF_PROCESS_PVT AS
2 /* $Header: csdvwfpb.pls 120.3.12020000.3 2013/04/04 09:35:40 subhat ship $ */
3
4 /*-----------------------------------------------------------------*/
5 /* procedure name: get_ro_details_wf */
6 /* description : Derive RO details for the workflow */
7 /* The procedure also checks to see if a role already exists for */
8 /* the user, if not, it will create a ad-hoc role for the user */
9 /*-----------------------------------------------------------------*/
10 PROCEDURE get_ro_details_wf(itemtype in varchar2,
11 itemkey in varchar2,
12 actid in number,
13 funcmode in varchar2,
14 resultout out NOCOPY varchar2) is
15
16 -- local variable declaration
17 l_contact_party_id number;
18 l_incident_number varchar2(64);
19 l_repair_number varchar2(30);
20 l_serial_number varchar2(30);
21 l_item_name varchar2(40);
22 l_repair_line_id number;
23 l_wf_role varchar2(320);
24 l_wf_role_display_name varchar2(360);
25 l_email varchar2(2000);
26 l_contact_name varchar2(360);
27
28 l_msg_text VARCHAR2(2000);
29
30 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.CSD_WF_PROCESS_PVT.get_ro_details_wf';
31
32 -- cursor to get the workorder details to be used in WF notification.
33 Cursor get_ro_attributes (p_repair_line_id in number) is
34 select sr.cont_email,
35 sr.incident_number,
36 ro.repair_number,
37 ro.serial_number,
38 sr.item,
39 decode(sr.contact_type,'EMPLOYEE',sr.first_name||' '||sr.last_name,sr.full_name) contact_name
40 from csd_incidents_v sr,
41 csd_repairs ro
42 where ro.incident_id = sr.incident_id
43 and ro.repair_line_id = p_repair_line_id;
44
45 -- cursor to see if a role exist for the SR contact.
46 Cursor get_wf_role (p_repair_line_id in number) is
47 Select wr.name
48 from wf_roles wr,
49 cs_incidents_v sr,
50 csd_repairs ro
51 where ro.repair_line_id = p_repair_line_id
52 and ro.incident_id = sr.incident_id
53 and wr.orig_system_id = sr.contact_party_id
54 and wr.orig_system = 'HZ_PARTY'
55 and nvl(wr.expiration_date,sysdate) >= sysdate
56 and wr.status = 'ACTIVE';
57
58
59 BEGIN
60
61 --FND_LOG.STRING(Fnd_Log.Level_Procedure,lc_mod_name,'Starting');
62
63 IF funcmode ='RUN' then
64
65 l_repair_line_id := wf_engine.GetItemAttrNumber
66 (itemtype => itemtype,
67 itemkey => itemkey,
68 aname => 'CSD_REPAIR_LINE_ID');
69
70 --
71 -- Derive the wf roles for the Contact id
72 --
73 Open get_wf_role (l_repair_line_id);
74 Fetch get_wf_role into l_wf_role;
75 Close get_wf_role;
76
77 Open get_ro_attributes (l_repair_line_id);
78 Fetch get_ro_attributes into l_email,l_incident_number,l_repair_number,l_serial_number,
79 l_item_name,l_contact_name;
80 Close get_ro_attributes;
81
82 --
83 -- If role does not exist the create adhoc wf role
84 --
85
86 if l_wf_role is null THEN
87
88 l_wf_role := 'NOTIFY_'||l_contact_name;
89 l_wf_role_display_name := 'Depot Notification Role For '||l_contact_name;
90
91 --FND_LOG.STRING(Fnd_Log.Level_Procedure,lc_mod_name,'CreateAdHocRole');
92
93 wf_directory.CreateAdHocRole
94 (role_name => l_wf_role,
95 role_display_name => l_wf_role_display_name,
96 language => 'AMERICAN',
97 territory => 'AMERICA',
98 role_description => 'CSD: Notify RO Details - Adhoc role',
99 notification_preference => 'MAILTEXT',
100 role_users => null,
101 email_address => l_email,
102 fax => null,
103 status => 'ACTIVE',
104 expiration_date => null,
105 parent_orig_system => null,
106 parent_orig_system_id => null,
107 owner_tag => null);
108
109 end if;
110
111 -- Retrieve the notifation message and set the tokens.
112 fnd_message.set_name('CSD','CSD_RMA_RCPT_NOTF_MSG');
113 fnd_message.set_token('CONTACT_NAME',l_contact_name);
114 fnd_message.set_token('SERVICE_REQUEST',l_incident_number);
115 fnd_message.set_token('REPAIR_ORDER',l_repair_number);
116 fnd_message.set_token('ITEM_NAME',l_item_name);
117 fnd_message.set_token('SERIAL_NUMBER',l_serial_number);
118
119 l_msg_text := fnd_message.get;
120
121 IF l_wf_role IS NOT NULL THEN
122
123 --FND_LOG.STRING(Fnd_Log.Level_Procedure,lc_mod_name,'setItemAttrText');
124
125 wf_engine.setItemAttrText
126 (itemtype => itemtype,
127 itemkey => itemkey,
128 aname => 'RECEIVER',
129 avalue => l_wf_role);
130
131 wf_engine.setItemAttrText
132 (itemtype => itemtype,
133 itemkey => itemkey,
134 aname => 'NOTF_MSG',
135 avalue => l_msg_text);
136
137 resultout := 'COMPLETE:SUCCESS';
138 ELSE
139 resultout := 'COMPLETE:WARNING';
140 END IF;
141
142 RETURN;
143 END IF;
144
145 EXCEPTION
146 WHEN OTHERS THEN
147 WF_CORE.CONTEXT ('csd_wf_process_pvt','get_ro_details_wf', itemtype,itemkey, to_char(actid),funcmode);
148 raise;
149 END;
150
151
152 /*-----------------------------------------------------------------*/
153 /* procedure name: create_cu_msg_wf */
154 /* description : Create the message sent to customer */
155 /* yvchen: Bug 12888789 */
156 /*-----------------------------------------------------------------*/
157 PROCEDURE create_cu_msg_wf(itemtype in varchar2,
158 itemkey in varchar2,
159 actid in number,
160 funcmode in varchar2,
161 resultout out NOCOPY varchar2) is
162
163 -- local variable declaration
164 l_contact_party_id number;
165 l_incident_number varchar2(64);
166 l_repair_number varchar2(30);
167 l_serial_number varchar2(30);
168 l_item_name varchar2(40);
169 l_repair_line_id number;
170 l_wf_role varchar2(320);
171 l_wf_role_display_name varchar2(360);
172 l_email varchar2(2000);
173 l_contact_name varchar2(360);
174 l_flow_status varchar2(80);
175
176 l_msg_text VARCHAR2(2000);
177
178 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.CSD_WF_PROCESS_PVT.create_cu_msg_wf';
179
180
181 -- cursor to get the workorder details to be used in WF notification.
182 Cursor get_ro_attributes (p_repair_line_id in number) is
183 select sr.cont_email,
184 sr.incident_number,
185 ro.repair_number,
186 ro.serial_number,
187 ro.item,
188 ro.flow_status,
189 decode(sr.contact_type,'EMPLOYEE',sr.first_name||' '||sr.last_name,sr.full_name) contact_name
190 from csd_incidents_v sr,
191 csd_repairs_v ro
192 where ro.incident_id = sr.incident_id
193 and ro.repair_line_id = p_repair_line_id;
194
195 -- cursor to see if a role exists for the SR contact.
196 Cursor get_wf_role (p_repair_line_id in number) is
197 Select wr.name
198 from wf_roles wr,
199 cs_incidents_v sr,
200 csd_repairs ro
201 where ro.repair_line_id = p_repair_line_id
202 and ro.incident_id = sr.incident_id
203 and wr.orig_system_id = sr.contact_party_id
204 and wr.orig_system = 'HZ_PARTY'
205 and nvl(wr.expiration_date,sysdate) >= sysdate
206 and wr.status = 'ACTIVE';
207
208 BEGIN
209
210 --FND_LOG.STRING(Fnd_Log.Level_Procedure,lc_mod_name,'Starting');
211
212 IF funcmode ='RUN' then
213
214 l_repair_line_id := wf_engine.GetItemAttrNumber
215 (itemtype => itemtype,
216 itemkey => itemkey,
217 aname => 'CSD_REPAIR_LINE_ID');
218
219 --
220 -- Derive the wf roles for the Contact id
221 --
222 Open get_wf_role (l_repair_line_id);
223 Fetch get_wf_role into l_wf_role;
224 Close get_wf_role;
225
226 Open get_ro_attributes (l_repair_line_id);
227 Fetch get_ro_attributes into l_email,l_incident_number,l_repair_number,l_serial_number,l_item_name,
228 l_flow_status,l_contact_name;
229 Close get_ro_attributes;
230
231 --
232 -- If role does not exist the create adhoc wf role
233 --
234
235 if l_wf_role is null THEN
236
237 l_wf_role := 'NOTIFY_'||l_contact_name;
238 l_wf_role_display_name := 'Depot Notification Role For '||l_contact_name;
239
240 --FND_LOG.STRING(Fnd_Log.Level_Procedure,lc_mod_name,'CreateAdHocRole');
241
242 wf_directory.CreateAdHocRole
243 (role_name => l_wf_role,
244 role_display_name => l_wf_role_display_name,
245 language => 'AMERICAN',
246 territory => 'AMERICA',
247 role_description => 'CSD: Notify RO Details - Adhoc role',
248 notification_preference => 'MAILTEXT',
249 role_users => null,
250 email_address => l_email,
251 fax => null,
252 status => 'ACTIVE',
253 expiration_date => null,
254 parent_orig_system => null,
255 parent_orig_system_id => null,
256 owner_tag => null);
257
258 end if;
259
260 -- Retrieve the notifation message and set the tokens.
261 fnd_message.set_name('CSD','CSD_RO_STATUS_NOTF_MSG');
262 fnd_message.set_token('CONTACT_NAME',l_contact_name);
263 fnd_message.set_token('SERVICE_REQUEST',l_incident_number);
264 fnd_message.set_token('REPAIR_ORDER',l_repair_number);
265 fnd_message.set_token('ITEM_NAME',l_item_name);
266 --fnd_message.set_token('SERIAL_NUMBER',l_serial_number);
267 --fnd_message.set_token('CURRENT_RO_STATUS',l_flow_status);
268
269 l_msg_text := fnd_message.get;
270
271 IF l_wf_role IS NOT NULL THEN
272
273 --FND_LOG.STRING(Fnd_Log.Level_Procedure,lc_mod_name,'setItemAttrText');
274
275 wf_engine.setItemAttrText
276 (itemtype => itemtype,
277 itemkey => itemkey,
278 aname => 'RECEIVER',
279 avalue => l_wf_role);
280
281 wf_engine.setItemAttrText
282 (itemtype => itemtype,
283 itemkey => itemkey,
284 aname => 'NOTF_MSG',
285 avalue => l_msg_text);
286
287 resultout := 'COMPLETE:SUCCESS';
288 ELSE
289 resultout := 'COMPLETE:WARNING';
290 END IF;
291
292 RETURN;
293 END IF;
294
295 EXCEPTION
296 WHEN OTHERS THEN
297 WF_CORE.CONTEXT ('csd_wf_process_pvt','create_cu_msg_wf', itemtype,itemkey, to_char(actid),funcmode);
298 raise;
299 END;
300
301
302 /*-------------------------------------------------------------------*/
303 /* procedure name: create_cu_rep_msg_wf */
304 /* Message to the customer service Rep */
305 /* description : Create the message sent to customer service rep */
306 /* yvchen: Bug 12888789 */
307 /*-------------------------------------------------------------------*/
308 PROCEDURE create_cu_rep_msg_wf(itemtype in varchar2,
309 itemkey in varchar2,
310 actid in number,
311 funcmode in varchar2,
312 resultout out NOCOPY varchar2) is
313
314 -- local variable declaration
315 l_contact_party_id number;
316 l_incident_number varchar2(64);
317 l_repair_number varchar2(30);
318 l_serial_number varchar2(30);
319 l_item_name varchar2(40);
320 l_repair_line_id number;
321 l_wf_role varchar2(320);
322 l_wf_role_display_name varchar2(360);
323 l_contact_name varchar2(360);
324 l_flow_status varchar2(80);
325 l_owner_email varchar2(320);
326 l_source_first_name varchar2(360);
327 l_source_last_name varchar2(360);
328
329 l_msg_text VARCHAR2(2000);
330
331 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.CSD_WF_PROCESS_PVT.create_cu_rep_msg_wf';
332
333 -- cursor to get the workorder details to be used in WF notification.
334 Cursor get_ro_attributes (p_repair_line_id in number) is
335 Select wr.name, wr.email_address, sr.incident_number,
336 ro.repair_number, ro.serial_number,
337 ro.flow_status, ro.item,
338 cjr.source_first_name, cjr.source_last_name
339 From wf_roles wr,
340 csd_incidents_v sr,
341 csd_repairs_v ro,
342 CS_JTF_RS_RESOURCE_EXTNS_SEC cjr
343 Where ro.repair_line_id = p_repair_line_id
344 and ro.incident_id = sr.incident_id
345 and wr.orig_system_id = cjr.source_id
346 and cjr.resource_id = sr.incident_owner_id
347 and wr.orig_system = 'PER'
348 and nvl(wr.expiration_date,sysdate) >= sysdate
349 and wr.status = 'ACTIVE';
350
351 BEGIN
352
353 --FND_LOG.STRING(Fnd_Log.Level_Procedure,lc_mod_name,'Starting');
354
355 IF funcmode ='RUN' then
356
357 l_repair_line_id := wf_engine.GetItemAttrNumber
358 (itemtype => itemtype,
359 itemkey => itemkey,
360 aname => 'CSD_REPAIR_LINE_ID');
361
362 Open get_ro_attributes (l_repair_line_id);
363 Fetch get_ro_attributes into l_wf_role, l_owner_email, l_incident_number, l_repair_number, l_serial_number,
364 l_flow_status, l_item_name, l_source_first_name, l_source_last_name;
365 Close get_ro_attributes;
366
367 l_contact_name := l_source_first_name ||' '|| l_source_last_name;
368
369 IF (l_wf_role is not null and l_owner_email is not null) THEN
370
371 -- Retrieve the notifation message and set the tokens.
372 fnd_message.set_name('CSD','CSD_RO_STATUS_NOTF_MSG');
373 fnd_message.set_token('CONTACT_NAME',l_contact_name);
374 fnd_message.set_token('SERVICE_REQUEST',l_incident_number);
375 fnd_message.set_token('REPAIR_ORDER',l_repair_number);
376 fnd_message.set_token('ITEM_NAME',l_item_name);
377 --fnd_message.set_token('SERIAL_NUMBER',l_serial_number);
378 --fnd_message.set_token('CURRENT_RO_STATUS',l_flow_status);
379
380 l_msg_text := fnd_message.get;
381
382 --FND_LOG.STRING(Fnd_Log.Level_Procedure,lc_mod_name,'setItemAttrText');
383
384 wf_engine.setItemAttrText
385 (itemtype => itemtype,
386 itemkey => itemkey,
387 aname => 'RECEIVER',
388 avalue => l_wf_role);
389
390 wf_engine.setItemAttrText
391 (itemtype => itemtype,
392 itemkey => itemkey,
393 aname => 'NOTF_MSG',
394 avalue => l_msg_text);
395
396 resultout := 'COMPLETE:SUCCESS';
397 ELSE
398 resultout := 'COMPLETE:WARNING';
399 END IF;
400
401 RETURN;
402 END IF;
403
404 EXCEPTION
405 WHEN OTHERS THEN
406 WF_CORE.CONTEXT ('csd_wf_process_pvt','create_cu_rep_msg_wf', itemtype,itemkey, to_char(actid),funcmode);
407 raise;
408 END;
409
410
411
412 /*-------------------------------------------------------------------*/
413 /* procedure name: auto_create_repair_orders */
414 /* Create Repair Orders for Recovered Parts */
415 /* description : Create service request and repair orders for */
416 /* recovered parts */
417 /* yvchen: FP bug 13968378 */
418 /*-------------------------------------------------------------------*/
419 PROCEDURE auto_create_repair_orders(itemtype in varchar2,
420 itemkey in varchar2,
421 actid in number,
422 funcmode in varchar2,
423 resultout out NOCOPY varchar2) is
424
425 -- local variable declaration
426 l_return_status Varchar2(1);
427 l_msg_count Number;
428 l_msg_data Varchar2(2000);
429 l_repln_rec CSD_REPAIRS_PUB.REPLN_Rec_Type;
430 l_repln_rec_clear CSD_REPAIRS_PUB.REPLN_Rec_Type;
431 l_repair_line_id Number;
432 l_repair_number Varchar2(30);
433 l_int_party_rec csd_bulk_receive_util.bulk_receive_rec;
434 l_notes_tbl cs_servicerequest_pub.notes_table;
435 l_intr_party_id Number;
436 l_intr_cust_acct_id Number;
437 l_created_sr Boolean := false;
438 l_incident_id Number;
439 l_incident_number Varchar2(64);
440 l_parent_repair_line_id Number;
441 l_transaction_id Number;
442 l_wip_entity_id Number;
443 l_inventory_item_id Number;
444 l_transaction_uom Varchar2(3);
445 l_transaction_quantity Number;
446 l_currency_code Varchar2(10);
447 l_organization_id Number;
448 l_srl_ctl_code Number;
449 l_install_base_flag Varchar2(1);
450 l_serial_number VARCHAR2(30);
451 l_instance_id Number;
452 l_repair_type_id Number;
453 l_repair_type_pl Number;
454 l_repair_mode Varchar2(30);
455 c_ib CONSTANT Varchar2(1) := 'Y';
456
457 -- Cursor to get internal party and account id to create service request
458 Cursor c_get_intr_party is
459 select csi.internal_party_id,
460 hca.cust_account_id
461 from csi_install_parameters csi,
462 hz_cust_accounts hca
463 where csi.internal_party_id = hca.party_id(+)
464 and hca.status(+) = 'A';
465
466 -- Cursor to get transaction attributes to create repair order
467 Cursor c_get_txn_attributes (p_repair_line_id in number) is
468 select MMT.transaction_id,
469 CWTD.wip_entity_id,
470 MMT.inventory_item_id,
471 MMT.transaction_uom,
472 MMT.transaction_quantity,
473 MMT.currency_code,
474 MMT.organization_id,
475 MSI.serial_number_control_code,
476 MSI.comms_nl_trackable_flag,
477 MUT.serial_number
478 from csd_repair_job_xref CRJX,
479 csd_wip_transaction_details CWTD,
480 mtl_material_transactions MMT,
481 mtl_system_items MSI,
482 mtl_unit_transactions MUT
483 where CRJX.repair_line_id = p_repair_line_id
484 and CRJX.wip_entity_id = CWTD.wip_entity_id
485 and CWTD.create_ro_flag ='T'
486 and CWTD.wip_entity_id = MMT.transaction_source_id
487 and MMT.transaction_type_id = 43 --43: WIP Return
488 and MMT.transaction_source_type_id = 5 -- 5: WIP-related txns
489 and CWTD.inventory_item_id = MMT.inventory_item_id
490 and MMT.inventory_item_id = MSI.inventory_item_id
491 and MMT.organization_id = MSI.organization_id
492 and MMT.transaction_id = MUT.transaction_id(+)
493 and NOT EXISTS (select CWT.from_mtl_transaction_id -- check if RO was previously created for this txn
494 from csd_ro_creation_wip_txns CWT
495 where CWT.from_mtl_transaction_id = MMT.transaction_id);
496
497 -- Cursor to derive repair type attribute
498 Cursor c_get_repair_type_attr(p_repair_type_id in number) is
499 select price_list_header_id,
500 repair_mode
501 from csd_repair_types_b
502 where repair_type_id = p_repair_type_id;
503
504 -- Cursor to get IB details
505 Cursor c_get_ib_info (p_inventory_item_id in number, p_serial_number in varchar2) is
506 Select instance_id
507 from csi_item_instances
508 where serial_number = p_serial_number
509 and inventory_item_id = p_inventory_item_id;
510
511 BEGIN
512
513 IF funcmode ='RUN' THEN
514
515 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
516 fnd_log.STRING (fnd_log.level_procedure,
517 'CSD.PLSQL.CSD_WF_PROCESS_PVT.AUTO_CREATE_REPAIR_ORDERS.BEGIN',
518 'Enter - auto_create_repair_orders');
519 END IF;
520
521 --initialize resultout to Error
522 resultout := 'COMPLETE:ERROR';
523
524 --create savepoint in case rollback is needed
525 Savepoint auto_create_ro_savepoint;
526
527 l_parent_repair_line_id := null;
528
529 -- get parent repair_line_id (the RO which changed status to kick off the workflow)
530 l_parent_repair_line_id := wf_engine.GetItemAttrNumber
531 (itemtype => itemtype,
532 itemkey => itemkey,
533 aname => 'CSD_REPAIR_LINE_ID');
534
535 IF NOT c_get_txn_attributes%ISOPEN THEN
536 OPEN c_get_txn_attributes (l_parent_repair_line_id);
537 END IF;
538
539 -- Create Repair Order for each transaction
540 LOOP
541
542 l_transaction_id := null;
543 l_wip_entity_id := null;
544 l_inventory_item_id := null;
545 l_transaction_uom := null;
546 l_transaction_quantity := null;
547 l_currency_code := null;
548 l_organization_id := null;
549 l_srl_ctl_code := null;
550 l_install_base_flag := null;
551 l_serial_number := null;
552
553 FETCH c_get_txn_attributes into
554 l_transaction_id,
555 l_wip_entity_id,
556 l_inventory_item_id,
557 l_transaction_uom,
558 l_transaction_quantity,
559 l_currency_code,
560 l_organization_id,
561 l_srl_ctl_code,
562 l_install_base_flag,
563 l_serial_number;
564
565 EXIT WHEN c_get_txn_attributes%NOTFOUND;
566
567 -------------------------------
568 -- CREATE SERVICE REQUEST
569 -------------------------------
570
571 -- Create SR if no SR has been created
572 IF NOT(l_created_sr) THEN
573
574 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
575 fnd_log.STRING (fnd_log.level_statement,
576 'CSD.PLSQL.CSD_WF_PROCESS_PVT.AUTO_CREATE_REPAIR_ORDERS',
577 'Begin Create Internal SR');
578 End if;
579
580 l_intr_party_id := null;
581 l_intr_cust_acct_id := null;
582
583 --derive internal party id and account id
584 Open c_get_intr_party;
585 Fetch c_get_intr_party into l_intr_party_id, l_intr_cust_acct_id;
586 Close c_get_intr_party;
587
588 l_int_party_rec.party_id := l_intr_party_id;
589 l_int_party_rec.cust_account_id := l_intr_cust_acct_id;
590 l_incident_id := null;
591 l_incident_number := null;
592
593 csd_bulk_receive_util.create_blkrcv_sr
594 (
595 p_bulk_receive_rec => l_int_party_rec,
596 p_sr_notes_tbl => l_notes_tbl,
597 x_incident_id => l_incident_id,
598 x_incident_number => l_incident_number,
599 x_return_status => l_return_status,
600 x_msg_count => l_msg_count,
601 x_msg_data => l_msg_data
602 );
603
604 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
605
606 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
607 fnd_log.STRING (fnd_log.level_statement,
608 'CSD.PLSQL.CSD_WF_PROCESS_PVT.AUTO_CREATE_REPAIR_ORDERS',
609 'Create Internal SR Failed');
610 End if;
611
612 RAISE FND_API.G_EXC_ERROR;
613
614 END IF;
615
616 l_created_sr := true;
617
618 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
619 fnd_log.STRING (fnd_log.level_statement,
620 'CSD.PLSQL.CSD_WF_PROCESS_PVT.AUTO_CREATE_REPAIR_ORDERS',
621 'Created Internal SR: Incident Number = ' || l_incident_number);
622 END IF;
623
624 END IF;
625
626 -------------------------------
627 -- CREATE REPAIR ORDERS
628 -------------------------------
629
630 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
631 fnd_log.STRING (fnd_log.level_statement,
632 'CSD.PLSQL.CSD_WF_PROCESS_PVT.AUTO_CREATE_REPAIR_ORDERS',
633 'Begin Create RO for transaction id = ' || l_transaction_id);
634 END IF;
635
636 -- Derive the Currency code if transaction_currency is null
637 IF (l_currency_code is null) THEN
638
639 -- Derive Repair Type
640 l_repair_type_id := null;
641 l_repair_type_id := csd_bulk_receive_util.get_bulk_rcv_def_repair_type (
642 p_incident_id => l_incident_id,
643 p_ro_inventory_item_id => l_inventory_item_id);
644 l_repair_type_pl := null;
645 l_repair_mode := null;
646
647 Open c_get_repair_type_attr(l_repair_type_id);
648 Fetch c_get_repair_type_attr into l_repair_type_pl, l_repair_mode;
649 Close c_get_repair_type_attr;
650
651 -- Derive the Currency code
652 If ( l_repair_type_pl is null ) then
653 l_currency_code := CSD_CHARGE_LINE_UTIL.GET_PLCURRCODE(fnd_profile.value('CSD_DEFAULT_PRICE_LIST'));
654 Else
655 l_currency_code := CSD_CHARGE_LINE_UTIL.GET_PLCURRCODE(l_repair_type_pl);
656 End if;
657
658 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
659 fnd_log.STRING (fnd_log.level_statement,
660 'CSD.PLSQL.CSD_WF_PROCESS_PVT.AUTO_CREATE_REPAIR_ORDERS',
661 'Derived Currency Code = '|| l_currency_code);
662 End if;
663
664 END IF;
665
666 -- populate required fields for creating RO
667 l_repln_rec.INCIDENT_ID := l_incident_id;
668 l_repln_rec.INVENTORY_ITEM_ID := l_inventory_item_id;
669 l_repln_rec.UNIT_OF_MEASURE := l_transaction_uom;
670 l_repln_rec.QUANTITY := l_transaction_quantity;
671 l_repln_rec.APPROVAL_REQUIRED_FLAG := NVL(fnd_profile.value('CSD_CUST_APPROVAL_REQD'), 'N');
672 l_repln_rec.CURRENCY_CODE := l_currency_code;
673
674 -- optional fields
675 l_repln_rec.INVENTORY_ORG_ID := l_organization_id;
676 l_repln_rec.REPAIR_TYPE_ID := l_repair_type_id;
677 l_repln_rec.AUTO_PROCESS_RMA := 'N';
678 l_repln_rec.REPAIR_MODE := l_repair_mode;
679 l_repln_rec.PARENT_REPAIR_LINE_ID := l_parent_repair_line_id;
680
681 -- populate fields for serialized items
682 IF (l_srl_ctl_code in (2,5,6) AND l_serial_number is not null) THEN
683
684 l_repln_rec.SERIAL_NUMBER := l_serial_number;
685
686 -- Derive the IB Instance ID for serialized, IB trackable item
687 If (l_install_base_flag = c_ib) then
688
689 l_instance_id := null;
690
691 Open c_get_ib_info(l_inventory_item_id, l_serial_number);
692 Fetch c_get_ib_info into l_instance_id;
693 Close c_get_ib_info;
694
695 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
696 fnd_log.STRING (fnd_log.level_statement,
697 'CSD.PLSQL.CSD_WF_PROCESS_PVT.AUTO_CREATE_REPAIR_ORDERS',
698 'Derived Instance Id = '|| l_instance_id || ' for Item Id = '|| l_inventory_item_id ||
699 ' Serial Number = ' || l_serial_number );
700 End if;
701
702 l_repln_rec.CUSTOMER_PRODUCT_ID := l_instance_id;
703
704 End if;
705
706 END IF;
707
708 l_repair_line_id := null;
709 l_repair_number := null;
710
711 csd_repairs_pvt.Create_Repair_Order(
712 P_Api_Version_Number => 1.0,
713 P_Init_Msg_List => Fnd_Api.G_TRUE,
714 P_Commit => Fnd_Api.G_FALSE,
715 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
716 p_REPAIR_LINE_ID => Fnd_Api.G_MISS_NUM,
717 P_REPLN_Rec => l_repln_rec,
718 X_REPAIR_LINE_ID => l_repair_line_id,
719 X_REPAIR_NUMBER => l_repair_number,
720 X_Return_Status => l_return_status,
721 X_Msg_Count => l_msg_count,
722 X_Msg_Data => l_msg_data
723 );
724
725 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
726
727 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
728 fnd_log.STRING (fnd_log.level_statement,
729 'CSD.PLSQL.CSD_WF_PROCESS_PVT.AUTO_CREATE_REPAIR_ORDERS',
730 'Create RO Failed for transaction id = ' || l_transaction_id);
731 End if;
732
733 RAISE FND_API.G_EXC_ERROR;
734
735 END IF;
736
737 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
738 fnd_log.STRING (fnd_log.level_statement,
739 'CSD.PLSQL.CSD_WF_PROCESS_PVT.AUTO_CREATE_REPAIR_ORDERS',
740 'Created RO: Repair Number = ' || l_repair_number || ' for transaction id = ' || l_transaction_id);
741 END IF;
742
743 -- insert new into CSD_RO_CREATION_WIP_TXNS to keep track of transaction ids of created ROs
744 INSERT INTO CSD_RO_CREATION_WIP_TXNS(
745 repair_order_creation_id,
746 repair_line_id,
747 from_mtl_transaction_id,
748 from_wip_entity_id,
749 object_version_number,
750 last_update_login,
751 created_by,
752 creation_date,
753 last_updated_by,
754 last_update_date)
755 VALUES(
756 CSD_RO_CREATION_ID_S1.nextval,
757 l_repair_line_id,
758 l_transaction_id,
759 l_wip_entity_id,
760 1,
761 NVL(fnd_global.LOGIN_ID, -1),
762 NVL(fnd_global.USER_ID, -1),
763 sysdate,
764 NVL(fnd_global.USER_ID, -1),
765 sysdate
766 );
767
768 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
769 fnd_log.STRING (fnd_log.level_statement,
770 'CSD.PLSQL.CSD_WF_PROCESS_PVT.AUTO_CREATE_REPAIR_ORDERS',
771 'Inserted transaction id = ' || l_transaction_id || ' into CSD_RO_CREATION_WIP_TXNS');
772 END IF;
773
774 -- clear/reset values in l_repln_rec
775 l_repln_rec := l_repln_rec_clear;
776
777 END LOOP;
778
779 Savepoint auto_create_ro_savepoint;
780
781 resultout := 'COMPLETE:SUCCESS';
782
783 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
784 fnd_log.STRING (fnd_log.level_procedure,
785 'CSD.PLSQL.CSD_WF_PROCESS_PVT.AUTO_CREATE_REPAIR_ORDERS.END',
786 'Exit - auto_create_repair_orders');
787 END IF;
788
789 RETURN;
790
791 END IF;
792
793 EXCEPTION
794 WHEN OTHERS THEN
795 Rollback To auto_create_ro_savepoint;
796 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
797 fnd_log.STRING (fnd_log.level_procedure,
798 'CSD.PLSQL.CSD_WF_PROCESS_PVT.AUTO_CREATE_REPAIR_ORDERS.END',
799 'Error/Exception occurred, Roll back all SR and ROs created');
800 END IF;
801
802 WF_CORE.CONTEXT ('csd_wf_process_pvt','create_cu_rep_msg_wf', itemtype,itemkey, to_char(actid),funcmode);
803 Raise;
804 END;
805
806 PROCEDURE create_service_warranty_wf
807 (itemtype in varchar2,
808 itemkey in varchar2,
809 actid in number,
810 funcmode in varchar2,
811 resultout out NOCOPY varchar2)
812 IS
813 l_order_line_id NUMBER;
814 l_repair_line_id NUMBER;
815 lc_mod_name CONSTANT VARCHAR2(50) := 'csd_wf_process_pvt.create_service_warranty_wf';
816 x_msg_data varchar2(2000);
817 x_msg_count number;
818 x_return_status varchar2(1);
819 x_contract_number varchar2(30);
820
821 BEGIN
822 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
823 THEN
824 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, lc_mod_name, 'Begin : create_service_warranty_wf');
825 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, lc_mod_name, 'Item key is '||itemkey);
826 END IF;
827 l_order_line_id := to_number(itemkey);
828 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
829 THEN
830 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, lc_mod_name, 'Calling csd_repairs_util.create_repair_warranty for order line id '||l_order_line_id);
831 END IF;
832 csd_repairs_util.create_repair_warranty
833 (p_api_version_number => 1.0,
834 p_init_msg_list => fnd_api.g_true,
835 p_commit => fnd_api.g_false,
836 x_return_status => x_return_status,
837 x_msg_count => x_msg_count,
838 x_msg_data => x_msg_data,
839 x_contract_number => x_contract_number,
840 p_order_line_id => l_order_line_id
841 );
842
843 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
844 THEN
845 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, lc_mod_name, 'Return from csd_repairs_util.create_repair_warranty. The return status is '||x_return_status);
846 END IF;
847
848 IF x_return_status = fnd_api.g_ret_sts_success
849 THEN
850 resultout := 'COMPLETE:SUCCESS';
851 ELSE
852 resultout := 'COMPLETE:ERROR';
853 END IF;
854
855 EXCEPTION
856 WHEN OTHERS THEN
857 WF_CORE.CONTEXT ('csd_wf_process_pvt','create_service_warranty_wf', itemtype,itemkey, to_char(actid),funcmode);
858 RAISE;
859
860 END create_service_warranty_wf;
861
862 END CSD_WF_PROCESS_PVT;