[Home] [Help]
PACKAGE BODY: APPS.ASP_SERVICE_CONTENT_PROVIDER
Source
1 PACKAGE BODY ASP_SERVICE_CONTENT_PROVIDER as
2 /* $Header: aspasvcb.pls 120.1 2005/09/06 14:45 axavier noship $ */
3 ---------------------------------------------------------------------------
4 -- Package Name: ASP_SERVICE_CONTENT_PROVIDER
5 ---------------------------------------------------------------------------
6 -- Description:
7 -- Provides content for the Service Alert.
8 --
9 -- Procedures:
10 -- (see the specification for details)
11 --
12 -- History:
13 -- 10-Aug-2005 axavier created.
14 ---------------------------------------------------------------------------
15
16 /*-------------------------------------------------------------------------*
17 | Private Constants
18 *-------------------------------------------------------------------------*/
19 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASP_SERVICE_CONTENT_PROVIDER';
20 G_FILE_NAME CONSTANT VARCHAR2(12) := 'aspasvcb.pls';
21 G_MODULE CONSTANT VARCHAR2(250) := 'asp.plsql.'||G_PKG_NAME||'.';
22
23
24 /*-------------------------------------------------------------------------*
25 | Private Datatypes
26 *-------------------------------------------------------------------------*/
27
28 /*-------------------------------------------------------------------------*
29 | Private Variables
30 *-------------------------------------------------------------------------*/
31
32 /*-------------------------------------------------------------------------*
33 | Private Routines Specification
34 *-------------------------------------------------------------------------*/
35
36 /*-------------------------------------------------------------------------*
37 | Public Routines
38 *-------------------------------------------------------------------------*/
39
40 --------------------------------------------------------------------------------
41 -- Procedure: Create_Content
42 -- Provides content for the Service Alert.
43 --
44 --------------------------------------------------------------------------------
45
46 PROCEDURE Create_Content(
47 itemtype in VARCHAR2,
48 itemkey in VARCHAR2,
49 actid in NUMBER,
50 funcmode in VARCHAR2,
51 resultout in out NOCOPY VARCHAR2)
52 IS
53 l_event_entity varchar2(100);
54 l_api_name varchar2(100);
55 l_qualified_api_name varchar2(200);
56 l_debug_msg varchar2(1000);
57 l_alert_code varchar2(30);
58 l_sms_text varchar2(32767);
59 l_email_sub varchar2(32767);
60 l_incident_id number;
61 l_task_id number;
62
63 l_incident_number varchar2(240);
64 l_customer_id number;
65 l_customer_name varchar2(360);
66 l_sr_owner varchar2(360);
67 l_sr_status varchar2(30);
68 l_esc_level varchar2(30);
69 l_email_content varchar2(240);
70
71 l_debug_runtime number;
72 l_debug_exception number;
73 l_debug_procedure number;
74 l_debug_statment number;
75
76
77 CURSOR get_sr_details(c_incident_id in number, c_task_id in number) is
78 SELECT sr.incident_number,
79 p.party_id as customer_id,
80 decode(sr.caller_type, 'PERSON', p.person_pre_name_adjunct || p.party_name,
81 p.party_name) as customer_name,
82 rs.resource_name as sr_owner,
83 sr_sts.name as sr_status,
84 lk_esc.meaning as esc_level
85 FROM cs_incidents_all_b sr,
86 hz_parties p,
87 jtf_rs_resource_extns_vl rs,
88 cs_incident_statuses_vl sr_sts,
89 jtf_tasks_vl t, jtf_task_references_vl r,fnd_lookups lk_esc
90 WHERE sr.incident_id = c_incident_id
91 and sr.customer_id = p.party_id
92 and sr.incident_owner_id = rs.resource_id (+)
93 and sr.incident_status_id = sr_sts.incident_status_id
94 and sr_sts.incident_subtype = 'INC'
95 and t.task_id = r.task_id
96 and r.reference_code = 'ESC'
97 and r.object_type_code = 'SR'
98 and r.object_id = sr.incident_id
99 and lk_esc.lookup_type = 'JTF_TASK_ESC_LEVEL'
100 and lk_esc.lookup_code = t.escalation_level
101 and t.task_id = c_task_id
102 and rownum < 2;
103
104 BEGIN
105 l_api_name := 'Create_Content';
106 l_debug_runtime := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
107 l_debug_exception := FND_LOG.LEVEL_EXCEPTION;
108 l_debug_procedure := FND_LOG.LEVEL_PROCEDURE;
109 l_debug_statment := FND_LOG.LEVEL_STATEMENT;
110
111 l_qualified_api_name := G_PKG_NAME||'.'||l_api_name;
112 if(l_debug_procedure >= l_debug_runtime) then
113 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'Entered '||G_PKG_NAME||'.'||l_api_name);
114 end if;
115
116 If(funcmode = 'RUN') Then
117 l_event_entity := wf_engine.GetItemAttrText( itemtype =>itemtype,
118 itemkey =>itemkey,
119 aname =>'ALERT_SOURCE_OBJECT_CODE');
120
121 l_alert_code := wf_engine.GetItemAttrText( itemtype =>itemtype,
122 itemkey =>itemkey,
123 aname =>'ALERT_NAME');--SVCREQUEST_ESCALATED_ALERT
124
125 l_incident_id := wf_engine.GetItemAttrText( itemtype =>itemtype,
126 itemkey =>itemkey,
127 aname =>'ALERT_SOURCE_OBJECT_ID');
128
129 l_task_id := wf_engine.GetItemAttrText( itemtype =>itemtype,
130 itemkey =>itemkey,
131 aname =>'ALERT_SOURCE_TASK_ID');
132
133 if(l_debug_procedure >= l_debug_runtime) then
134 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_event_entity '||l_event_entity);
135 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_alert_code '||l_alert_code);
136 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_incident_id '||l_incident_id);
137 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_task_id '||l_task_id);
138 end if;
139
140 If l_event_entity = 'SERVICE_REQUEST' Then
141 ----------
142 l_sms_text := '';
143 l_email_sub := '';
144 --ASP_WF_SERVICE_MSG_RN ASP_SERVICE_WFRN
145 l_email_content := 'JSP:/OA_HTML/OA.jsp?OAFunc=ASP_WF_SERVICE_MSG_RN' ||
146 '&' || 'IncidentId=-'||'&'|| 'MSG_INCIDENTID-'||'&'||'TaskId=-'||'&'||'MSG_TASKID-';
147
148 --***SMS Content
149 BEGIN
150 OPEN get_sr_details(l_incident_id,l_task_id);
151 FETCH get_sr_details
152 INTO l_incident_number,
153 l_customer_id,
154 l_customer_name,
155 l_sr_owner,
156 l_sr_status,
157 l_esc_level;
158 IF (get_sr_details%NOTFOUND) THEN
159 l_sms_text := 'INVALID SERVICE REQUEST!';
160 l_email_sub := 'INVALID SERVICE REQUEST!';
161 END IF;
162 CLOSE get_sr_details;
163
164 EXCEPTION
165 WHEN OTHERS THEN
166 l_sms_text := 'INVALID SERVICE REQUEST!';
167 l_email_sub := 'INVALID SERVICE REQUEST!';
168 END;
169
170 --Message ASP_SERVICE_SMS_TEXT - Service Request - # <SRNUM> for customer '<CUSTNAME>' has been escalated. Currently assigned to '<REPNAME>' with status '<STATUS>' and escalation level: '<ESCLEVEL>'.
171 fnd_message.set_name('ASP', 'ASP_SERVICE_SMS_TEXT');
172 fnd_message.set_token('SRNUM', l_incident_number);
173 fnd_message.set_token('CUSTNAME', l_customer_name);
174 fnd_message.set_token('REPNAME', l_sr_owner);
175 fnd_message.set_token('STATUS', l_sr_status);
176 fnd_message.set_token('ESCLEVEL', l_esc_level);
177 --fnd_message.set_token('CUSTID', to_char(l_customer_id));
178 l_sms_text := fnd_message.get;
179
180 wf_engine.SetItemAttrText(itemtype, itemkey, 'SMS_TEXT', l_sms_text);
181
182 --***Done SMS Content
183
184 if(l_debug_procedure >= l_debug_runtime) then
185 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'SMS_TEXT '||l_sms_text);
186 end if;
187
188 --***EMAIL Content
189 --Message ASP_SERVICE_EMAIL_SUB - Service Request #<SRNUM> for customer '<CUSTNAME>' escalated.
190
191 fnd_message.set_name('ASP', 'ASP_SERVICE_EMAIL_SUB');
192 fnd_message.set_token('SRNUM', l_incident_number);
193 fnd_message.set_token('CUSTNAME', l_customer_name);
194 l_email_sub := fnd_message.get;
195
196
197
198 wf_engine.SetItemAttrText(itemtype, itemkey, 'INCIDENTID', l_incident_id);
199 --WF_Attr INCIDENTID => Msg_Attr MSG_INCIDENTID
200 wf_engine.SetItemAttrText(itemtype, itemkey, 'TASKID', l_task_id);
201 --WF_Attr TASKID => Msg_Attr MSG_TASKID
202
203 wf_engine.SetItemAttrText(itemtype, itemkey, 'EMAIL_SUB_TEXT', l_email_sub);
204 --WF_Attr EMAIL_SUB_TEXT => Msg_Attr MSG_SUBJECT_EMAIL
205 wf_engine.SetItemAttrText(itemtype, itemkey, 'EMAIL_CONTENT', l_email_content);
206 --WF_Attr EMAIL_CONTENT => Msg_Attr MSG_BODY_EMAIL
207 --***Done EMAIL Content
208 if(l_debug_procedure >= l_debug_runtime) then
209 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'INCIDENTID '||l_incident_id);
210 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'TASKID '||l_task_id);
211 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'EMAIL_SUB_TEXT '||l_email_sub);
212 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'EMAIL_CONTENT '||l_email_content);
213 end if;
214
215 ----------
216 resultout := 'COMPLETE:SUCCESS';
217 return;
218 Else
219 resultout := 'COMPLETE:NULL';
220 return;
221 End If;
222 End If;
223 --
224 -- Other execution modes may be created in the future. Your
225 -- activity will indicate that it does not implement a mode
226 -- by returning null
227 --
228 resultout := '';
229 return;
230
231
232 EXCEPTION
233 When no_data_found Then
234 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, to_char(actid), funcmode);
235 l_debug_msg := l_qualified_api_name||':NO_DATA_FOUND:'||to_char(SQLCODE)||':'||substr(SQLERRM,1,500);
236 wf_core.token('ORA_ERROR',l_debug_msg);
237 wf_core.raise('WF_ORA');
238
239 When others Then
240 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, to_char(actid), funcmode);
241 l_debug_msg := l_qualified_api_name||':OTHERS:'||to_char(SQLCODE)||':'||substr(SQLERRM,1,500);
242 wf_core.token('ORA_ERROR',l_debug_msg);
243 wf_core.raise('WF_ORA');
244
245 END Create_Content;
246
247 End ASP_SERVICE_CONTENT_PROVIDER;