[Home] [Help]
PACKAGE BODY: APPS.ASP_SVCCONTRACT_ALERT_AGENT
Source
1 PACKAGE BODY ASP_SVCCONTRACT_ALERT_AGENT as
2 /* $Header: aspascab.pls 120.4 2005/09/13 17:20 axavier noship $ */
3 ---------------------------------------------------------------------------
4 -- Package Name: ASP_SVCCONTRACT_ALERT_AGENT
5 ---------------------------------------------------------------------------
6 -- Description:
7 -- Package contains methods for evaluating the alert condition and
8 -- finds the subscribers for various alerts.
9 --
10 -- Procedures:
11 -- (see the specification for details)
12 --
13 -- History:
14 -- 10-Aug-2005 axavier created.
15 ---------------------------------------------------------------------------
16
17 /*-------------------------------------------------------------------------*
18 | Private Constants
19 *-------------------------------------------------------------------------*/
20 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASP_SVCCONTRACT_ALERT_AGENT';
21 G_FILE_NAME CONSTANT VARCHAR2(12) := 'aspascab.pls';
22 G_MODULE CONSTANT VARCHAR2(250) := 'asp.plsql.'||G_PKG_NAME||'.';
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: Evaluate_Alerts
42 -- Finds all the subscribers of this alert for SMS and EMAIL Channels.
43 --
44 --------------------------------------------------------------------------------
45
46 PROCEDURE Evaluate_Alerts(
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_subscriber_list ASP_ALERTS_PUB.SUBSCRIBER_TBL_TYPE;
59 l_return_status VARCHAR2(30);
60 l_msg_count NUMBER ;
61 l_msg_data VARCHAR2(2000) ;
62 l_sms_users varchar2(32767);
63 l_email_users varchar2(32767);
64 l_sms_fnd_users varchar2(32767);
65 l_email_fnd_users varchar2(32767);
66 l_sms_users_count NUMBER ;
67 l_email_users_count NUMBER ;
68 l_contract_header_id number;
69 l_customer_id number;
70
71 l_debug_runtime number;
72 l_debug_exception number;
73 l_debug_procedure number;
74 l_debug_statment number;
75
76 CURSOR get_customer_id(c_contract_header_id in number) is
77 SELECT HZP.party_id customer_id
78 FROM okc_k_headers_all_b CHRB
79 ,oks_k_headers_b KHRB
80 ,okc_subclasses_v SCSV
81 ,okc_k_party_roles_b CPL
82 ,hz_parties HZP
83 WHERE CHRB.id = c_contract_header_id --<contract header id>
84 AND CHRB.id = CPL.dnz_chr_id
85 AND SCSV.code = CHRB.scs_code
86 AND SCSV.cls_code = 'SERVICE'
87 AND CHRB.id = KHRB.chr_id
88 AND CPL.cle_id IS NULL
89 AND CPL.rle_code in('CUSTOMER','SUBSCRIBER')
90 AND CPL.jtot_object1_code = 'OKX_PARTY'
91 AND CPL.object1_id1 = TO_CHAR(HZP.party_id)
92 and rownum < 2;
93
94
95 BEGIN
96 l_api_name := 'Evaluate_Alerts';
97 l_debug_runtime := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
98 l_debug_exception := FND_LOG.LEVEL_EXCEPTION;
99 l_debug_procedure := FND_LOG.LEVEL_PROCEDURE;
100 l_debug_statment := FND_LOG.LEVEL_STATEMENT;
101
102 l_qualified_api_name := G_PKG_NAME||'.'||l_api_name;
103 l_sms_users_count := 0;
104 l_email_users_count := 0;
105
106 if(l_debug_procedure >= l_debug_runtime) then
107 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'Entered '||G_PKG_NAME||'.'||l_api_name);
108 end if;
109
110 If(funcmode = 'RUN') Then
111 l_event_entity := wf_engine.GetItemAttrText( itemtype =>itemtype,
112 itemkey =>itemkey,
113 aname =>'ALERT_SOURCE_OBJECT_CODE');
114 l_alert_code := wf_engine.GetItemAttrText( itemtype =>itemtype,
115 itemkey =>itemkey,
116 aname =>'ALERT_NAME');
117 l_contract_header_id := wf_engine.GetItemAttrText( itemtype =>itemtype,
118 itemkey =>itemkey,
119 aname =>'ALERT_SOURCE_OBJECT_ID');
120
121 BEGIN
122 OPEN get_customer_id(l_contract_header_id);
123 FETCH get_customer_id
124 INTO l_customer_id;
125 IF (get_customer_id%NOTFOUND) THEN
126 l_customer_id := -1;
127 END IF;
128 CLOSE get_customer_id;
129
130 EXCEPTION
131 WHEN OTHERS THEN
132 l_customer_id := -1;
133 END;
134
135
136 if(l_debug_procedure >= l_debug_runtime) then
137 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_event_entity '||l_event_entity);
138 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_alert_code '||l_alert_code);
139 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_contract_header_id '||l_contract_header_id);
140 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_customer_id '||l_customer_id);
141 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'Calling ASP_ALERTS_PUB.Get_Matching_Subscriptions');
142 end if;
143
144 If l_event_entity = 'SERVICE_CONTRACT' Then
145 ASP_ALERTS_PUB.Get_Matching_Subscriptions(
146 p_api_version_number => 1.0,
147 p_init_msg_list => FND_API.G_FALSE,
148 p_alert_code => l_alert_code,
149 p_customer_id => l_customer_id,
150 x_subscriber_list => l_subscriber_list,
151 x_return_status => l_return_status,
152 x_msg_count => l_msg_count,
153 x_msg_data => l_msg_data
154 );
155 if(l_debug_procedure >= l_debug_runtime) then
156 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_return_status '||l_return_status);
157 end if;
158
159 IF l_return_status <> 'S' THEN
160 --Set debug messages
161 wf_engine.SetItemAttrText(itemtype, itemkey, 'FOUND_SUBS', 'NO');
162 resultout := 'COMPLETE:SUCCESS';
163 return;
164 END IF;
165
166 IF l_subscriber_list.count > 0 THEN
167 begin
168 For i in l_subscriber_list.first..l_subscriber_list.last
169 loop
170 if(l_subscriber_list(i).DELIVERY_CHANNEL = 'SMS') then
171 l_sms_users_count := l_sms_users_count+1;
172 if(l_sms_users_count = 1) then
173 l_sms_users := l_sms_users || l_subscriber_list(i).SUBSCRIBER_NAME;
174 l_sms_fnd_users := l_sms_fnd_users || l_subscriber_list(i).USER_ID;
175 else
176 l_sms_users := l_sms_users || ',' || l_subscriber_list(i).SUBSCRIBER_NAME;
177 l_sms_fnd_users := l_sms_fnd_users || ',' || l_subscriber_list(i).USER_ID;
178 end if;
179 elsif(l_subscriber_list(i).DELIVERY_CHANNEL = 'EMAIL') then
180 l_email_users_count := l_email_users_count+1;
181 if(l_email_users_count = 1) then
182 l_email_users := l_email_users || l_subscriber_list(i).SUBSCRIBER_NAME;
183 l_email_fnd_users := l_email_fnd_users || l_subscriber_list(i).USER_ID;
184 else
185 l_email_users := l_email_users || ',' || l_subscriber_list(i).SUBSCRIBER_NAME;
186 l_email_fnd_users := l_email_fnd_users || ',' || l_subscriber_list(i).USER_ID;
187 end if;
188 else
189 null;
190 end if;
191 end loop;
192 exception
193 When Others then
194 wf_engine.SetItemAttrText(itemtype, itemkey, 'FOUND_SUBS', 'NO');
195 end;
196
197 if(l_sms_users_count > 0) then
198 wf_engine.SetItemAttrText(itemtype, itemkey, 'FOUND_SMS_SUBS', 'YES');
199 if(l_debug_procedure >= l_debug_runtime) then
200 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'FOUND_SMS_SUBS '||'YES');
201 end if;
202 end if;
203 if(l_email_users_count > 0) then
204 wf_engine.SetItemAttrText(itemtype, itemkey, 'FOUND_EMAIL_SUBS', 'YES');
205 if(l_debug_procedure >= l_debug_runtime) then
206 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'FOUND_EMAIL_SUBS '||'YES');
207 end if;
208 end if;
209
210 wf_engine.SetItemAttrText(itemtype, itemkey, 'SMS_USERS', l_sms_users);
211 wf_engine.SetItemAttrText(itemtype, itemkey, 'EMAIL_USERS', l_email_users);
212 wf_engine.SetItemAttrText(itemtype, itemkey, 'SMS_FND_USERS', l_sms_fnd_users);
213 wf_engine.SetItemAttrText(itemtype, itemkey, 'EMAIL_FND_USERS', l_email_fnd_users);
214 wf_engine.SetItemAttrText(itemtype, itemkey, 'FOUND_SUBS', 'YES');
215
216 if(l_debug_procedure >= l_debug_runtime) then
217 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_sms_users_count '||l_sms_users_count);
218 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_email_users_count '||l_email_users_count);
219 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'SMS_USERS '||l_sms_users);
220 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'EMAIL_USERS '||l_email_users);
221 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'FOUND_SUBS '||'YES');
222 end if;
223
224 ELSE
225 wf_engine.SetItemAttrText(itemtype, itemkey, 'FOUND_SUBS', 'NO');
226 END IF;--l_subscriber_list.count > 0
227
228 resultout := 'COMPLETE:SUCCESS';
229 return;
230 Else -- not SERVICE_CONTRACT
231 resultout := 'COMPLETE:NULL';
232 return;
233 End If;
234 End If;--RUN Mode
235 --
236 -- Other execution modes may be created in the future. Your
237 -- activity will indicate that it does not implement a mode
238 -- by returning null
239 --
240 resultout := '';
241 return;
242
243 EXCEPTION
244 When no_data_found Then
245 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, to_char(actid), funcmode);
246 l_debug_msg := l_qualified_api_name||':NO_DATA_FOUND:'||to_char(SQLCODE)||':'||substr(SQLERRM,1,500);
247 wf_core.token('ORA_ERROR',l_debug_msg);
248 wf_core.raise('WF_ORA');
249
250 When others Then
251 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, to_char(actid), funcmode);
252 l_debug_msg := l_qualified_api_name||':OTHERS:'||to_char(SQLCODE)||':'||substr(SQLERRM,1,500);
253 wf_core.token('ORA_ERROR',l_debug_msg);
254 wf_core.raise('WF_ORA');
255
256 END Evaluate_Alerts;
257
258 End ASP_SVCCONTRACT_ALERT_AGENT;