[Home] [Help]
PACKAGE BODY: APPS.CSD_WF_PROCESS_PVT
Source
1 PACKAGE BODY CSD_WF_PROCESS_PVT AS
2 /* $Header: csdvwfpb.pls 120.0 2008/05/12 07:40:25 subhat noship $ */
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 -- cursor to get the workorder details to be used in WF notification.
31 Cursor get_ro_attributes (p_repair_line_id in number) is
32 select sr.cont_email,
33 sr.incident_number,
34 ro.repair_number,
35 ro.serial_number,
36 sr.item,
37 decode(sr.contact_type,'EMPLOYEE',sr.first_name||' '||sr.last_name,sr.full_name) contact_name
38 from csd_incidents_v sr,
39 csd_repairs ro
40 where ro.incident_id = sr.incident_id
41 and ro.repair_line_id = p_repair_line_id;
42
43 -- cursor to see if a role exist for the SR contact.
44 Cursor get_wf_role (p_repair_line_id in number) is
45 Select wr.name
46 from wf_roles wr,
47 cs_incidents_v sr,
48 csd_repairs ro
49 where ro.repair_line_id = p_repair_line_id
50 and ro.incident_id = sr.incident_id
51 and wr.orig_system_id = sr.contact_party_id
52 and wr.orig_system = 'HZ_PARTY'
53 and nvl(wr.expiration_date,sysdate) >= sysdate
54 and wr.status = 'ACTIVE';
55
56
57 BEGIN
58
59 IF funcmode ='RUN' then
60
61 l_repair_line_id := wf_engine.GetItemAttrNumber
62 (itemtype => itemtype,
63 itemkey => itemkey,
64 aname => 'CSD_REPAIR_LINE_ID');
65
66 --
67 -- Derive the wf roles for the Contact id
68 --
69 Open get_wf_role (l_repair_line_id);
70 Fetch get_wf_role into l_wf_role;
71 Close get_wf_role;
72
73 Open get_ro_attributes (l_repair_line_id);
74 Fetch get_ro_attributes into l_email,l_incident_number,l_repair_number,l_serial_number,
75 l_item_name,l_contact_name;
76 Close get_ro_attributes;
77
78 --
79 -- If role does not exist the create adhoc wf role
80 --
81
82 if l_wf_role is null THEN
83
84 l_wf_role := 'NOTIFY_'||l_contact_name;
85 l_wf_role_display_name := 'Depot Notification Role For '||l_contact_name;
86
87 wf_directory.CreateAdHocRole
88 (role_name => l_wf_role,
89 role_display_name => l_wf_role_display_name,
90 language => 'AMERICAN',
91 territory => 'AMERICA',
92 role_description => 'CSD: Notify RO Details - Adhoc role',
93 notification_preference => 'MAILTEXT',
94 role_users => null,
95 email_address => l_email,
96 fax => null,
97 status => 'ACTIVE',
98 expiration_date => null,
99 parent_orig_system => null,
100 parent_orig_system_id => null,
101 owner_tag => null);
102
103 end if;
104
105 -- Retrieve the notifation message and set the tokens.
106 fnd_message.set_name('CSD','CSD_RMA_RCPT_NOTF_MSG');
107 fnd_message.set_token('CONTACT_NAME',l_contact_name);
108 fnd_message.set_token('SERVICE_REQUEST',l_incident_number);
109 fnd_message.set_token('REPAIR_ORDER',l_repair_number);
110 fnd_message.set_token('ITEM_NAME',l_item_name);
111 fnd_message.set_token('SERIAL_NUMBER',l_serial_number);
112
113 l_msg_text := fnd_message.get;
114
115 IF l_wf_role IS NOT NULL THEN
116
117 wf_engine.setItemAttrText
118 (itemtype => itemtype,
119 itemkey => itemkey,
120 aname => 'RECEIVER',
121 avalue => l_wf_role);
122
123 wf_engine.setItemAttrText
124 (itemtype => itemtype,
125 itemkey => itemkey,
126 aname => 'NOTF_MSG',
127 avalue => l_msg_text);
128
129 resultout := 'COMPLETE:SUCCESS';
130 ELSE
131 resultout := 'COMPLETE:WARNING';
132 END IF;
133
134 RETURN;
135 END IF;
136
137 EXCEPTION
138 WHEN OTHERS THEN
139 WF_CORE.CONTEXT ('csd_wf_process_pvt','get_ro_details_wf', itemtype,itemkey, to_char(actid),funcmode);
140 raise;
141 END;
142
143 END CSD_WF_PROCESS_PVT;