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