[Home] [Help]
PACKAGE BODY: APPS.ASP_ORDER_ALERT_AGENT
Source
1 PACKAGE BODY ASP_ORDER_ALERT_AGENT as
2 /* $Header: aspaorab.pls 120.5 2005/09/28 13:45 axavier noship $ */
3 ---------------------------------------------------------------------------
4 -- Package Name: ASP_ORDER_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_ORDER_ALERT_AGENT';
21 G_FILE_NAME CONSTANT VARCHAR2(12) := 'aspaorab.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_blanket_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_blanket_header_id in number) is
77 SELECT party.party_id as customer_id
78 FROM oe_blanket_headers_all oobha,
79 hz_parties party,
80 hz_cust_accounts_all cust_acct
81 WHERE oobha.header_id = c_blanket_header_id
82 and oobha.sold_to_org_id = cust_acct.cust_account_id(+)
83 and cust_acct.party_id = party.party_id(+)
84 and oobha.sales_document_type_code = 'B'
85 and rownum < 2;
86
87 BEGIN
88 l_api_name := 'Evaluate_Alerts';
89 l_debug_runtime := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
90 l_debug_exception := FND_LOG.LEVEL_EXCEPTION;
91 l_debug_procedure := FND_LOG.LEVEL_PROCEDURE;
92 l_debug_statment := FND_LOG.LEVEL_STATEMENT;
93
94 l_qualified_api_name := G_PKG_NAME||'.'||l_api_name;
95 l_sms_users_count := 0;
96 l_email_users_count := 0;
97
98 if(l_debug_procedure >= l_debug_runtime) then
99 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'Entered '||G_PKG_NAME||'.'||l_api_name);
100 end if;
101
102 If(funcmode = 'RUN') Then
103 l_event_entity := wf_engine.GetItemAttrText( itemtype =>itemtype,
104 itemkey =>itemkey,
105 aname =>'ALERT_SOURCE_OBJECT_CODE');
106 l_alert_code := wf_engine.GetItemAttrText( itemtype =>itemtype,
107 itemkey =>itemkey,
108 aname =>'ALERT_NAME');
109 l_blanket_header_id := wf_engine.GetItemAttrText( itemtype =>itemtype,
110 itemkey =>itemkey,
111 aname =>'ALERT_SOURCE_OBJECT_ID');
112
113 BEGIN
114 OPEN get_customer_id(l_blanket_header_id);
115 FETCH get_customer_id
116 INTO l_customer_id;
117 IF (get_customer_id%NOTFOUND) THEN
118 l_customer_id := -1;
119 END IF;
120 CLOSE get_customer_id;
121
122 EXCEPTION
123 WHEN OTHERS THEN
124 l_customer_id := -1;
125 END;
126
127
128 if(l_debug_procedure >= l_debug_runtime) then
129 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_event_entity '||l_event_entity);
130 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_alert_code '||l_alert_code);
131 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_blanket_header_id '||l_blanket_header_id);
132 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_customer_id '||l_customer_id);
133
134 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'Calling ASP_ALERTS_PUB.Get_Matching_Subscriptions');
135 end if;
136
137
138 If l_event_entity = 'ORDER' Then
139 ASP_ALERTS_PUB.Get_Matching_Subscriptions(
140 p_api_version_number => 1.0,
141 p_init_msg_list => FND_API.G_FALSE,
142 p_alert_code => l_alert_code,
143 p_customer_id => l_customer_id,
144 x_subscriber_list => l_subscriber_list,
145 x_return_status => l_return_status,
146 x_msg_count => l_msg_count,
147 x_msg_data => l_msg_data
148 );
149 if(l_debug_procedure >= l_debug_runtime) then
150 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_return_status '||l_return_status);
151 end if;
152
153 IF l_return_status <> 'S' THEN
154 --Set debug messages
155 wf_engine.SetItemAttrText(itemtype, itemkey, 'FOUND_SUBS', 'NO');
156 resultout := 'COMPLETE:SUCCESS';
157 return;
158 END IF;
159
160 IF l_subscriber_list.count > 0 THEN
161 BEGIN
162 For i in l_subscriber_list.first..l_subscriber_list.last
163 loop
164 if(l_subscriber_list(i).DELIVERY_CHANNEL = 'SMS') then
165 l_sms_users_count := l_sms_users_count+1;
166 if(l_sms_users_count = 1) then
167 l_sms_users := l_sms_users || l_subscriber_list(i).SUBSCRIBER_NAME;
168 l_sms_fnd_users := l_sms_fnd_users || l_subscriber_list(i).USER_ID;
169 else
170 l_sms_users := l_sms_users || ',' || l_subscriber_list(i).SUBSCRIBER_NAME;
171 l_sms_fnd_users := l_sms_fnd_users || ',' || l_subscriber_list(i).USER_ID;
172 end if;
173 elsif(l_subscriber_list(i).DELIVERY_CHANNEL = 'EMAIL') then
174 l_email_users_count := l_email_users_count+1;
175 if(l_email_users_count = 1) then
176 l_email_users := l_email_users || l_subscriber_list(i).SUBSCRIBER_NAME;
177 l_email_fnd_users := l_email_fnd_users || l_subscriber_list(i).USER_ID;
178 else
179 l_email_users := l_email_users || ',' || l_subscriber_list(i).SUBSCRIBER_NAME;
180 l_email_fnd_users := l_email_fnd_users || ',' || l_subscriber_list(i).USER_ID;
181 end if;
182 else
183 null;
184 end if;
185 end loop;
186 exception
187 When Others then
188 wf_engine.SetItemAttrText(itemtype, itemkey, 'FOUND_SUBS', 'NO');
189 end;
190 if(l_sms_users_count > 0) then
191 wf_engine.SetItemAttrText(itemtype, itemkey, 'FOUND_SMS_SUBS', 'YES');
192 if(l_debug_procedure >= l_debug_runtime) then
193 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'FOUND_SMS_SUBS '||'YES');
194 end if;
195 end if;
196 if(l_email_users_count > 0) then
197 wf_engine.SetItemAttrText(itemtype, itemkey, 'FOUND_EMAIL_SUBS', 'YES');
198 if(l_debug_procedure >= l_debug_runtime) then
199 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'FOUND_EMAIL_SUBS '||'YES');
200 end if;
201 end if;
202
203 wf_engine.SetItemAttrText(itemtype, itemkey, 'SMS_USERS', l_sms_users);
204 wf_engine.SetItemAttrText(itemtype, itemkey, 'EMAIL_USERS', l_email_users);
205 wf_engine.SetItemAttrText(itemtype, itemkey, 'SMS_FND_USERS', l_sms_fnd_users);
206 wf_engine.SetItemAttrText(itemtype, itemkey, 'EMAIL_FND_USERS', l_email_fnd_users);
207 wf_engine.SetItemAttrText(itemtype, itemkey, 'FOUND_SUBS', 'YES');
208
209 if(l_debug_procedure >= l_debug_runtime) then
210 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_sms_users_count '||l_sms_users_count);
211 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_email_users_count '||l_email_users_count);
212 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'SMS_USERS '||l_sms_users);
213 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'EMAIL_USERS '||l_email_users);
214 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'FOUND_SUBS '||'YES');
215 end if;
216
217 ELSE
218 wf_engine.SetItemAttrText(itemtype, itemkey, 'FOUND_SUBS', 'NO');
219 END IF;--l_subscriber_list.count > 0
220
221 resultout := 'COMPLETE:SUCCESS';
222 return;
223 Else
224 resultout := 'COMPLETE:NULL';
225 return;
226 End If;
227 End If;
228 --
229 -- Other execution modes may be created in the future. Your
230 -- activity will indicate that it does not implement a mode
231 -- by returning null
232 --
233 resultout := '';
234 return;
235
236
237 EXCEPTION
238 When no_data_found Then
239 wf_core.context(G_PKG_NAME, l_api_name, itemtype, itemkey, to_char(actid), funcmode);
240 l_debug_msg := l_qualified_api_name||':NO_DATA_FOUND:'||to_char(SQLCODE)||':'||substr(SQLERRM,1,500);
241 wf_core.token('ORA_ERROR',l_debug_msg);
242 wf_core.raise('WF_ORA');
243
244 When others 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||':OTHERS:'||to_char(SQLCODE)||':'||substr(SQLERRM,1,500);
247 wf_core.token('ORA_ERROR',l_debug_msg);
248 wf_core.raise('WF_ORA');
249
250 END Evaluate_Alerts;
251
252 End ASP_ORDER_ALERT_AGENT;