[Home] [Help]
PACKAGE BODY: APPS.ASP_ALERTS_SVC_CONTRACT
Source
1 PACKAGE BODY ASP_ALERTS_SVC_CONTRACT as
2 /* $Header: aspaescb.pls 120.3 2005/09/13 17:19 axavier noship $ */
3 ---------------------------------------------------------------------------
4 -- Package Name: ASP_ALERTS_SVC_CONTRACT
5 ---------------------------------------------------------------------------
6 -- Description:
7 -- Alerts for expiring service contract is obtained by this
8 -- Concurrent Program, which periodically looks at the transaction tables
9 -- in Oracle Service Contract.
10 --
11 -- Procedures:
12 -- (see the specification for details)
13 --
14 -- History:
15 -- 16-Aug-2005 axavier created.
16 ---------------------------------------------------------------------------
17
18 /*-------------------------------------------------------------------------*
19 | Private Constants
20 *-------------------------------------------------------------------------*/
21 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASP_ALERTS_SVC_CONTRACT';
22 G_FILE_NAME CONSTANT VARCHAR2(12) := 'aspaescb.pls';
23 G_MODULE CONSTANT VARCHAR2(250) := 'asp.plsql.'||G_PKG_NAME||'.';
24
25
26 /*-------------------------------------------------------------------------*
27 | Private Datatypes
28 *-------------------------------------------------------------------------*/
29
30 /*-------------------------------------------------------------------------*
31 | Private Variables
32 *-------------------------------------------------------------------------*/
33
34 /*-------------------------------------------------------------------------*
35 | Private Routines Specification
36 *-------------------------------------------------------------------------*/
37
38 /*-------------------------------------------------------------------------*
39 | Public Routines
40 *-------------------------------------------------------------------------*/
41
42 --------------------------------------------------------------------------------
43 --
44 -- Procedure: Alert_Expiring_SvcContracts
45 -- Finds all the Active Service Contracts that are expiring in X days.
46 --
47 --------------------------------------------------------------------------------
48
49 PROCEDURE Alert_Expiring_SvcContracts(
50 errbuf OUT NOCOPY VARCHAR2,
51 retcode OUT NOCOPY VARCHAR2,
52 p_num_days IN VARCHAR2)
53 IS
54
55 l_api_name varchar2(100);
56 l_program_ref_date date;
57 l_current_run_ref_date date;
58
59 l_event_key number;
60 l_item_key varchar2(240);
61 l_contract_id number;
62
63 l_return_status VARCHAR2(1);
64 l_msg_count NUMBER;
65 l_msg_data VARCHAR2(4000);
66 l_renewal_type VARCHAR2(1000);
67 l_approval_type VARCHAR2(1000);
68 l_threshold_used VARCHAR2(1000);
69
70 l_PROGRAM_START_DATE date;
71 l_REQUEST_ID number;
72 l_PROGRAM_APPLICATION_ID number;
73 l_PROGRAM_ID number;
74 l_PROGRAM_LOGIN_ID number;
75 l_CREATED_BY number;
76 l_LAST_UPDATED_BY number;
77 l_LAST_UPDATE_LOGIN number;
78 save_threshold number;
79 l_debug_runtime number;
80 l_debug_exception number;
81 l_debug_procedure number;
82 l_debug_statment number;
83 p_number_of_days number;
84
85 CURSOR getLastRunDate is
86 SELECT program_ref_date
87 FROM asp_program_run_dates
88 WHERE program_object_code = 'ASPEXPSC' --'SERVICE_CONTRACT'
89 and status_code = 'S'
90 and rownum < 2;
91
92 CURSOR getServiceContractsFreshRun IS
93 select
94 a.id as contract_id
95 from
96 okc_k_headers_all_b a, okc_statuses_b b
97 where application_id = 515
98 and a.sts_code = b.code
99 and b.ste_code in ('ACTIVE', 'EXPIRED', 'SIGNED')
100 and trunc(a.end_date) between trunc(l_current_run_ref_date) and
101 trunc(l_current_run_ref_date + p_number_of_days);
102 --and rownum < 2;
103
104 CURSOR getServiceContractsDeltaRun is
105 select
106 a.id as contract_id
107 from
108 okc_k_headers_all_b a, okc_statuses_b b
109 where application_id = 515
110 and a.sts_code = b.code
111 and b.ste_code in ('ACTIVE', 'EXPIRED', 'SIGNED')
112 and trunc(a.end_date) between trunc(l_program_ref_date+1)
113 and trunc(l_current_run_ref_date + p_number_of_days);
114
115
116
117 BEGIN
118 l_api_name := 'Alert_Expiring_SvcContracts';
119 l_debug_runtime := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
120 l_debug_exception := FND_LOG.LEVEL_EXCEPTION;
121 l_debug_procedure := FND_LOG.LEVEL_PROCEDURE;
122 l_debug_statment := FND_LOG.LEVEL_STATEMENT;
123
124 p_number_of_days := p_num_days;
125 if(l_debug_procedure >= l_debug_runtime) then
126 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'Entered '||G_PKG_NAME||'.'||l_api_name);
127 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'user entered num of days '||to_char(p_number_of_days));
128 end if;
129 --if 1=1 then return; end if;
130 if (p_number_of_days is null) then
131 p_number_of_days := 10;
132 if(l_debug_procedure >= l_debug_runtime) then
133 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'system assigned num of days '||to_char(p_number_of_days));
134 end if;
135 end if;
136
137 save_threshold := wf_engine.threshold;
138 l_PROGRAM_START_DATE := sysdate;
139 l_REQUEST_ID := TO_NUMBER(fnd_profile.value('CONC_REQUEST_ID'));
140 l_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
141 l_PROGRAM_ID := TO_NUMBER(fnd_profile.value('CONC_PROGRAM_ID'));
142 l_PROGRAM_LOGIN_ID := TO_NUMBER(fnd_profile.value('USER_ID'));
143 l_CREATED_BY := l_PROGRAM_LOGIN_ID;
144 l_LAST_UPDATED_BY := l_PROGRAM_LOGIN_ID;
145 l_LAST_UPDATE_LOGIN := TO_NUMBER(fnd_profile.value('CONC_LOGIN_ID'));
146
147 open getLastRunDate;
148 fetch getLastRunDate into l_program_ref_date;
149 close getLastRunDate;
150 if(l_debug_procedure >= l_debug_runtime) then
151 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'After opening cursor getLastRunDate');
152 end if;
153
154 l_current_run_ref_date := sysdate;
155
156 IF(l_program_ref_date is null) THEN
157 if(l_debug_procedure >= l_debug_runtime) then
158 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_program_ref_date is null');
159 end if;
160 mo_global.set_policy_context('A',null);--authoring_ord_id
161 if(l_debug_procedure >= l_debug_runtime) then
162 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'Before opening getServiceContractsFreshRun');
163 end if;
164
165 for svccontract_rec in getServiceContractsFreshRun
166 loop
167 l_contract_id := svccontract_rec.contract_id;
168 if(l_debug_procedure >= l_debug_runtime) then
169 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_contract_id '||l_contract_id);
170 end if;
171
172 oks_renew_util_pub.GET_RENEWAL_TYPE(
173 p_api_version => 1.0,
174 p_init_msg_list => FND_API.G_TRUE,
175 x_return_status => l_return_status,
176 x_msg_count => l_msg_count,
177 x_msg_data => l_msg_data,
178 p_chr_id => l_contract_id,
179 x_renewal_type => l_renewal_type,--EVN (Evergreen), DNR (Do not Renew), ERN (Online), NSR (Manual)
180 x_approval_type => l_approval_type,
181 x_threshold_used => l_threshold_used
182 );
183 if(l_debug_procedure >= l_debug_runtime) then
184 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'After Calling oks_renew_util_pub.GET_RENEWAL_TYPE - x_return_status:'||l_return_status);
185 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_renewal_type '||l_renewal_type);
186 end if;
187
188 if(nvl(l_renewal_type,'X') <> 'EVN') then
189 SELECT l_contract_id ||'-'|| to_char(asp_wf_alerts_s.nextval) INTO l_item_key FROM DUAL;
190 if(l_debug_procedure >= l_debug_runtime) then
191 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_item_key '||l_item_key);
192 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'Before wf_engine.CreateProcess ASP_ALERT_PROCESS');
193 end if;
194 -- Start the ASP Alert Manager Process (ASP_ALERT_PROCESS) with the following info:
195 wf_engine.threshold := -1;
196 wf_engine.CreateProcess( itemtype => 'ASPALERT', itemkey => l_item_key, process => 'ASP_ALERT_PROCESS',user_key=>l_item_key);
197 wf_engine.SetItemAttrText('ASPALERT', l_item_key, 'ALERT_NAME', 'SVCCONTRACT_PRE_EXPIRE_ALERT');
198 wf_engine.SetItemAttrText('ASPALERT', l_item_key, 'ALERT_SOURCE_OBJECT_CODE', 'SERVICE_CONTRACT');
199 wf_engine.SetItemAttrText('ASPALERT', l_item_key, 'ALERT_SOURCE_OBJECT_ID', l_contract_id);
200 wf_engine.StartProcess(itemtype => 'ASPALERT', itemkey => l_item_key);
201 if(l_debug_procedure >= l_debug_runtime) then
202 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_item_key '||l_item_key);
203 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'After wf_engine.CreateProcess ASP_ALERT_PROCESS');
204 end if;
205 commit;
206 end if;
207 end loop;
208 if(l_debug_procedure >= l_debug_runtime) then
209 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'After opening getServiceContractsFreshRun');
210 end if;
211
212 --program_ref_date = current_run_ref_date of this run + X of this run
213 delete asp_program_run_dates where PROGRAM_OBJECT_CODE = 'ASPEXPSC';
214 insert into asp_program_run_dates(
215 PROGRAM_RUN_ID,
216 PROGRAM_OBJECT_CODE,
217 STATUS_CODE,
218 DESCRIPTION,
219 PROGRAM_START_DATE,
220 PROGRAM_END_DATE,
221 PROGRAM_REF_DATE,
222 RELATED_OBJECT_INFO,
223 REQUEST_ID,
224 PROGRAM_APPLICATION_ID,
225 PROGRAM_ID,
226 PROGRAM_LOGIN_ID,
227 OBJECT_VERSION_NUMBER,
228 CREATION_DATE,
229 CREATED_BY,
230 LAST_UPDATE_DATE,
231 LAST_UPDATED_BY,
232 LAST_UPDATE_LOGIN
233 )values(
234 asp_program_run_dates_s.nextval,
235 'ASPEXPSC', --'SERVICE_CONTRACT'
236 'S',
237 null,
238 l_PROGRAM_START_DATE,
239 null,
240 l_current_run_ref_date + p_number_of_days,
241 null,
242 l_REQUEST_ID,
243 l_PROGRAM_APPLICATION_ID,
244 l_PROGRAM_ID,
245 l_PROGRAM_LOGIN_ID,
246 1,
247 sysdate,
248 l_CREATED_BY,
249 sysdate,
250 l_LAST_UPDATED_BY,
251 l_LAST_UPDATE_LOGIN
252 );
253
254 commit;
255 ELSE
256 if(l_debug_procedure >= l_debug_runtime) then
257 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_program_ref_date is not null');
258 end if;
259 mo_global.set_policy_context('A',null);--authoring_ord_id
260 if(l_debug_procedure >= l_debug_runtime) then
261 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'Before opening getServiceContractsDeltaRun');
262 end if;
263
264 for svccontract_rec in getServiceContractsDeltaRun
265 loop
266 l_contract_id := svccontract_rec.contract_id;
267 if(l_debug_procedure >= l_debug_runtime) then
268 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_contract_id '||l_contract_id);
269 end if;
270
271 oks_renew_util_pub.GET_RENEWAL_TYPE(
272 p_api_version => 1.0,
273 p_init_msg_list => FND_API.G_TRUE,
274 x_return_status => l_return_status,
275 x_msg_count => l_msg_count,
276 x_msg_data => l_msg_data,
277 p_chr_id => l_contract_id,
278 x_renewal_type => l_renewal_type,--EVN (Evergreen), DNR (Do not Renew), ERN (Online), NSR (Manual)
279 x_approval_type => l_approval_type,
280 x_threshold_used => l_threshold_used
281 );
282 if(l_debug_procedure >= l_debug_runtime) then
283 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'After Calling oks_renew_util_pub.GET_RENEWAL_TYPE - x_return_status:'||l_return_status);
284 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_renewal_type '||l_renewal_type);
285 end if;
286
287 if(nvl(l_renewal_type,'X') <> 'EVN') then
288 SELECT l_contract_id ||'-'|| to_char(asp_wf_alerts_s.nextval) INTO l_item_key FROM DUAL;
289 if(l_debug_procedure >= l_debug_runtime) then
290 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_item_key '||l_item_key);
291 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'Before wf_engine.CreateProcess ASP_ALERT_PROCESS');
292 end if;
293
294 -- Start the ASP Alert Manager Process (ASP_ALERT_PROCESS) with the following info:
295 wf_engine.threshold := -1;
296 wf_engine.CreateProcess( itemtype => 'ASPALERT', itemkey => l_item_key, process => 'ASP_ALERT_PROCESS',user_key=>l_item_key);
297 wf_engine.SetItemAttrText('ASPALERT', l_item_key, 'ALERT_NAME', 'SVCCONTRACT_PRE_EXPIRE_ALERT');
298 wf_engine.SetItemAttrText('ASPALERT', l_item_key, 'ALERT_SOURCE_OBJECT_CODE', 'SERVICE_CONTRACT');
299 wf_engine.SetItemAttrText('ASPALERT', l_item_key, 'ALERT_SOURCE_OBJECT_ID', l_contract_id);
300 wf_engine.StartProcess(itemtype => 'ASPALERT', itemkey => l_item_key);
301 if(l_debug_procedure >= l_debug_runtime) then
302 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'l_item_key '||l_item_key);
303 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'After wf_engine.CreateProcess ASP_ALERT_PROCESS');
304 end if;
305
306 commit;
307 end if;
308 end loop;
309 if(l_debug_procedure >= l_debug_runtime) then
310 fnd_log.string(l_debug_procedure, G_MODULE||l_api_name, 'After opening getServiceContractsDeltaRun');
311 end if;
312
313 --program_ref_date = current_run_ref_date of this run + X of this run
314 update asp_program_run_dates
315 set program_ref_date = (l_current_run_ref_date + p_number_of_days)
316 where program_object_code = 'ASPEXPSC' --'SERVICE_CONTRACT'
317 and status_code = 'S';
318 commit;
319 END IF;
320 wf_engine.threshold := save_threshold;
321
322 EXCEPTION
323 WHEN others THEN
324 wf_engine.threshold := save_threshold;
325 raise;
326
327 END Alert_Expiring_SvcContracts;
328
329
330 END ASP_ALERTS_SVC_CONTRACT;
331