[Home] [Help]
PACKAGE BODY: APPS.OKC_DATE_ASSEMBLER_PUB
Source
1 PACKAGE BODY OKC_DATE_ASSEMBLER_PUB AS
2 /* $Header: OKCPDASB.pls 120.0 2005/05/25 22:35:33 appldev noship $ */
3 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
4
5 --
6 g_package varchar2(33) := ' OKC_DATE_ASSEMBLER_PUB.';
7
8
9
10 PROCEDURE conc_mgr(errbuf OUT NOCOPY VARCHAR2,
11 retcode OUT NOCOPY VARCHAR2) IS
12
13 l_api_name CONSTANT VARCHAR2(30) := 'conc_mgr';
14 l_api_version CONSTANT VARCHAR2(30) := 1.0;
15 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
16 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
17 l_msg_count NUMBER;
18 l_msg_data VARCHAR2(1000);
19 l_init_msg_list VARCHAR2(3) := 'F';
20 E_Resource_Busy EXCEPTION;
21 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
22
23 --
24 l_proc varchar2(72) := g_package||'conc_mgr';
25 --
26
27 BEGIN
28
29 IF (l_debug = 'Y') THEN
30 okc_debug.Set_Indentation(l_proc);
31 okc_debug.Log('10: Entering ',2);
32 END IF;
33
34 -- call start_activity to create savepoint, check comptability
35 -- and initialize message list
36 l_return_status := OKC_API.START_ACTIVITY(l_api_name
37 ,l_init_msg_list
38 ,'_PUBLIC'
39 ,x_return_status
40 );
41 --Initialize the return code
42 retcode := 0;
43 OKC_DATE_ASSEMBLER_PUB.date_assemble(
44 p_api_version => l_api_version
45 ,p_init_msg_list => l_init_msg_list
46 ,x_return_status => l_return_status
47 ,x_msg_count => l_msg_count
48 ,x_msg_data => l_msg_data);
49
50
51 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
52 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
53 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
54 RAISE OKC_API.G_EXCEPTION_ERROR;
55 END IF;
56
57 OKC_API.END_ACTIVITY(l_msg_count, l_msg_data);
58
59 IF (l_debug = 'Y') THEN
60 okc_debug.Log('1000: Leaving ',2);
61 okc_debug.Reset_Indentation;
62 END IF;
63
64 EXCEPTION
65 WHEN E_Resource_Busy THEN
66 l_return_status := okc_api.g_ret_sts_error;
67 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
68 IF (l_debug = 'Y') THEN
69 okc_debug.Log('2000: Leaving ',2);
70 okc_debug.Reset_Indentation;
71 END IF;
72 WHEN OKC_API.G_EXCEPTION_ERROR THEN
73 retcode := 2;
74 errbuf := substr(sqlerrm,1,200);
75 l_return_status := OKC_API.HANDLE_EXCEPTIONS
76 (l_api_name,
77 G_PKG_NAME,
78 'OKC_API.G_RET_STS_ERROR',
79 l_msg_count,
80 l_msg_data,
81 '_PUBLIC');
82 IF (l_debug = 'Y') THEN
83 okc_debug.Log('3000: Leaving ',2);
84 okc_debug.Reset_Indentation;
85 END IF;
86 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
87 retcode := 2;
88 errbuf := substr(sqlerrm,1,200);
89 l_return_status := OKC_API.HANDLE_EXCEPTIONS
90 (l_api_name,
91 G_PKG_NAME,
92 'OKC_API.G_RET_STS_UNEXP_ERROR',
93 l_msg_count,
94 l_msg_data,
95 '_PUBLIC');
96 IF (l_debug = 'Y') THEN
97 okc_debug.Log('4000: Leaving ',2);
98 okc_debug.Reset_Indentation;
99 END IF;
100 WHEN OTHERS THEN
101 retcode := 2;
102 errbuf := substr(sqlerrm,1,200);
103 l_return_status := OKC_API.HANDLE_EXCEPTIONS
104 (l_api_name,
105 G_PKG_NAME,
106 'OTHERS',
107 l_msg_count,
108 l_msg_data,
109 '_PUBLIC');
110 IF (l_debug = 'Y') THEN
111 okc_debug.Log('5000: Leaving ',2);
112 okc_debug.Reset_Indentation;
113 END IF;
114 END conc_mgr;
115
116 PROCEDURE date_assemble(
117 p_api_version IN NUMBER ,
118 p_init_msg_list IN VARCHAR2 ,
119 x_return_status OUT NOCOPY VARCHAR2,
120 x_msg_count OUT NOCOPY NUMBER,
121 x_msg_data OUT NOCOPY VARCHAR2) IS
122
123 l_api_name CONSTANT VARCHAR2(30) := 'date_assemble';
124 --l_api_version CONSTANT NUMBER := 1.0;
125 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
126 --l_msg_count NUMBER;
127 --l_msg_data VARCHAR2(1000);
128 l_cnhv_rec OKC_CNH_PVT.cnhv_rec_type;
129 i_cnhv_rec OKC_CNH_PVT.cnhv_rec_type;
130
131
132 --Get all the action and condition details
133 --Bug#4033775 check if action is enabled 12/09/2004
134 CURSOR acn_csr IS
135 SELECT acn.correlation, acn.acn_type, cnh.name,cnh.id, cnh.dnz_chr_id,
136 cnh.cnh_variance, cnh.before_after,
137 nvl(cnh.last_rundate,sysdate-1) last_rundate,
138 acn.enabled_yn
139 FROM okc_condition_headers_v cnh, okc_actions_v acn
140 WHERE cnh.acn_id = acn.id
141 AND acn.acn_type = 'DBA'
142 AND condition_valid_yn = 'Y'
143 AND template_yn = 'N'
144 AND sysdate between date_active and nvl(date_inactive,sysdate)
145 ORDER BY cnh.name;
146
147 acn_rec acn_csr%ROWTYPE;
148
149 --
150 l_proc varchar2(72) := g_package||'date_assemble';
151 --
152
153 BEGIN
154
155 IF (l_debug = 'Y') THEN
156 okc_debug.Set_Indentation(l_proc);
157 okc_debug.Log('10: Entering ',2);
158 END IF;
159
160 -- call start_activity to create savepoint, check comptability
161 -- and initialize message list
162 l_return_status := OKC_API.START_ACTIVITY(l_api_name
163 ,p_init_msg_list
164 ,'_PUBLIC'
165 ,x_return_status
166 );
167 -- check if activity started successfully
168 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
169 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
170 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
171 RAISE OKC_API.G_EXCEPTION_ERROR;
172 END IF;
173
174 fnd_message.set_name('OKC','OKC_EXPIRY_DATE_CONTRACTS');
175 fnd_file.put_line(fnd_file.log,fnd_message.get);
176
177 --If the action type is date based action and the correlation is Contract expiry date
178 --then call the date assembler to process all the contracts expiring before or after
179 --the given variance
180
181 FOR acn_rec IN acn_csr LOOP
182
183 --Bug#4033775 check if action is enabled 12/09/2004
184 IF acn_rec.acn_type = 'DBA' and acn_rec.correlation = 'KEXPIRE'
185 and acn_rec.enabled_yn = 'Y' THEN
186
187 fnd_message.set_name('OKC','OKC_EDA_CONDITIONS');
188 fnd_message.set_token('NAME',acn_rec.name);
189 fnd_file.put_line(fnd_file.log,fnd_message.get);
190
191 --Call the date assembler for contract expiry date
192 OKC_EXP_DATE_ASMBLR_PVT.exp_date_assemble(
193 p_api_version => 1
194 ,p_init_msg_list => 'F'
195 ,x_return_status => l_return_status
196 ,x_msg_count => x_msg_count
197 ,x_msg_data => x_msg_data
198 ,p_cnh_id => acn_rec.id
199 ,p_dnz_chr_id => acn_rec.dnz_chr_id
200 ,p_cnh_variance => acn_rec.cnh_variance
201 ,p_before_after => acn_rec.before_after
202 ,p_last_rundate => acn_rec.last_rundate);
203
204 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
205 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
206 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
207 RAISE OKC_API.G_EXCEPTION_ERROR;
208 ELSIF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
209 l_cnhv_rec.id := acn_rec.id;
210 l_cnhv_rec.last_rundate := sysdate;
211
212 --
213 -- Update the Last Run date for the picked condition
214 --
215 OKC_CONDITIONS_PUB.update_cond_hdrs(p_api_version => 1,
216 p_init_msg_list => okc_api.g_false,
217 x_return_status => l_return_status,
218 x_msg_count => x_msg_count,
219 x_msg_data => x_msg_data,
220 p_cnhv_rec => l_cnhv_rec,
221 x_cnhv_rec => i_cnhv_rec);
222
223 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
224 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
225 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
226 RAISE OKC_API.G_EXCEPTION_ERROR;
227 END IF; -- Update the Last Run date
228
229 COMMIT;
230
231 END IF; -- Call the date assembler for contract expiry date
232
233
234
235 --Bug#4033775 check if action is enabled 12/09/2004
236 ELSIF acn_rec.acn_type = 'DBA' and acn_rec.correlation = 'KLEXPIRE'
237 and acn_rec.enabled_yn = 'Y' THEN
238 -- for contract LINES
239
240 fnd_message.set_name('OKC','OKC_EDA_CONDITIONS');
241 fnd_message.set_token('NAME',acn_rec.name);
242 fnd_file.put_line(fnd_file.log,fnd_message.get);
243
244 --Call the date assembler for contract LINES expiry date
245 OKC_EXP_DATE_ASMBLR_PVT.exp_lines_date_assemble(
246 p_api_version => 1
247 ,p_init_msg_list => 'F'
248 ,x_return_status => l_return_status
249 ,x_msg_count => x_msg_count
250 ,x_msg_data => x_msg_data
251 ,p_cnh_id => acn_rec.id
252 ,p_dnz_chr_id => acn_rec.dnz_chr_id
253 ,p_cnh_variance => acn_rec.cnh_variance
254 ,p_before_after => acn_rec.before_after
255 ,p_last_rundate => acn_rec.last_rundate);
256
257 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
258 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
259 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
260 RAISE OKC_API.G_EXCEPTION_ERROR;
261 ELSIF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
262 l_cnhv_rec.id := acn_rec.id;
263 l_cnhv_rec.last_rundate := sysdate;
264
265 --
266 -- Update the Last Run date for the picked condition
267 --
268 OKC_CONDITIONS_PUB.update_cond_hdrs(p_api_version => 1,
269 p_init_msg_list => okc_api.g_false,
270 x_return_status => l_return_status,
271 x_msg_count => x_msg_count,
272 x_msg_data => x_msg_data,
273 p_cnhv_rec => l_cnhv_rec,
274 x_cnhv_rec => i_cnhv_rec);
275
276 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
277 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
278 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
279 RAISE OKC_API.G_EXCEPTION_ERROR;
280 END IF; -- Update the Last Run date
281 COMMIT;
282
283 END IF; -- Call the date assembler for contract LINES expiry date
284
285 END IF; -- acn_rec.acn_type = 'DBA' and acn_rec.correlation = 'KEXPIRE'
286
287 END LOOP;
288
289 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
290
291 IF (l_debug = 'Y') THEN
292 okc_debug.Log('1000: Leaving ',2);
293 okc_debug.Reset_Indentation;
294 END IF;
295
296 EXCEPTION
297 WHEN OKC_API.G_EXCEPTION_ERROR THEN
298 x_return_status := OKC_API.HANDLE_EXCEPTIONS
299 (l_api_name,
300 G_PKG_NAME,
301 'OKC_API.G_RET_STS_ERROR',
302 x_msg_count,
303 x_msg_data,
304 '_PUBLIC');
305 IF (l_debug = 'Y') THEN
306 okc_debug.Log('2000: Leaving ',2);
307 okc_debug.Reset_Indentation;
308 END IF;
309 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
310 x_return_status := OKC_API.HANDLE_EXCEPTIONS
311 (l_api_name,
312 G_PKG_NAME,
313 'OKC_API.G_RET_STS_UNEXP_ERROR',
314 x_msg_count,
315 x_msg_data,
316 '_PUBLIC');
317 IF (l_debug = 'Y') THEN
318 okc_debug.Log('3000: Leaving ',2);
319 okc_debug.Reset_Indentation;
320 END IF;
321 WHEN OTHERS THEN
322 x_return_status := OKC_API.HANDLE_EXCEPTIONS
323 (l_api_name,
324 G_PKG_NAME,
325 'OTHERS',
326 x_msg_count,
327 x_msg_data,
328 '_PUBLIC');
329 IF (l_debug = 'Y') THEN
330 okc_debug.Log('4000: Leaving ',2);
331 okc_debug.Reset_Indentation;
332 END IF;
333
334 END date_assemble;
335
336 END OKC_DATE_ASSEMBLER_PUB;