[Home] [Help]
PACKAGE BODY: APPS.OKE_CHANGE_STATUS_PUB
Source
1 PACKAGE BODY oke_change_status_pub as
2 /* $Header: OKEPCSTB.pls 120.0.12020000.2 2013/02/22 06:46:23 ansraj noship $ */
3
4 ---------------------------------------------------------------------------
5 -- GLOBAL MESSAGE CONSTANTS
6 ---------------------------------------------------------------------------
7 G_FND_APP CONSTANT VARCHAR2(200) := 'OKE';
8 ---------------------------------------------------------------------------
9
10 ------------------------------------------------------------------------------
11 -- GLOBAL CONSTANTS
12 ------------------------------------------------------------------------------
13 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
14 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
15
16 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKE_API.G_REQUIRED_VALUE;
17 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKE_API.G_INVALID_VALUE;
18
19 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKE_CONTRACTS_UNEXPECTED_ERROR';
20 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
21 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
22
23 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKE_API.G_COL_NAME_TOKEN;
24
25 g_api_version constant number :=1;
26 g_init_msg_list varchar2(1) := 'T';
27 g_msg_count NUMBER;
28 g_msg_data varchar2(240);
29 p_count number := 0;
30 g_api_type CONSTANT VARCHAR2(4) := '_PUB';
31 ------------------------------------------------------------------------------
32 -- GLOBAL EXCEPTIONS
33 ------------------------------------------------------------------------------
34 E_Resource_Busy EXCEPTION;
35 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
36
37
38 procedure Update_header_status(p_api_version IN NUMBER,
39 x_return_status OUT NOCOPY VARCHAR2,
40 x_msg_data OUT NOCOPY VARCHAR2,
41 x_msg_count OUT NOCOPY NUMBER,
42 p_init_msg_list in varchar2 default FND_API.G_FALSE,
43 p_chr_sts_tbl in OUT NOCOPY chr_sts_tbl_type
44 ) is
45
46 l_api_name CONSTANT VARCHAR2(30) := 'Update_header_status';
47 l_api_version CONSTANT NUMBER := 1.0;
48 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
49 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
50 i NUMBER:=0;
51 BEGIN
52 -- call START_ACTIVITY to create savepoint, check compatibility
53 -- and initialize message list
54 l_return_status := OKE_API.START_ACTIVITY(
55 p_api_name => l_api_name,
56 p_pkg_name => g_pkg_name,
57 p_init_msg_list => p_init_msg_list,
58 l_api_version => l_api_version,
59 p_api_version => p_api_version,
60 p_api_type => g_api_type,
61 x_return_status => x_return_status);
62
63 -- check if activity started successfully
64 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
65 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
66 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
67 raise OKE_API.G_EXCEPTION_ERROR;
68 End If;
69
70 If (p_chr_sts_tbl.COUNT > 0) Then
71 i := p_chr_sts_tbl.FIRST;
72 LOOP
73 -- call procedure for a record
74 oke_change_status_pUB.Update_header_status(
75 p_api_version => p_api_version,
76 p_init_msg_list => p_init_msg_list,
77 x_return_status => x_return_status,
78 x_msg_count => x_msg_count,
79 x_msg_data => x_msg_data,
80 p_chr_sts_rec => p_chr_sts_tbl(i)
81 );
82
83 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
84 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
85 l_overall_status := x_return_status;
86 End If;
87 End If;
88
89 EXIT WHEN (i = p_chr_sts_tbl.LAST);
90 i := p_chr_sts_tbl.NEXT(i);
91 END LOOP;
92
93 -- return overall status
94 x_return_status := l_overall_status;
95 End If;
96
97 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
98 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
99 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
100 raise OKE_API.G_EXCEPTION_ERROR;
101 End If;
102
103 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
104 x_msg_data => x_msg_data);
105 EXCEPTION
106 when OKE_API.G_EXCEPTION_ERROR then
107 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
108 p_api_name => l_api_name,
109 p_pkg_name => g_pkg_name,
110 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
111 x_msg_count => x_msg_count,
112 x_msg_data => x_msg_data,
113 p_api_type => g_api_type);
114
115 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
116 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
117 p_api_name => l_api_name,
118 p_pkg_name => g_pkg_name,
119 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
120 x_msg_count => x_msg_count,
121 x_msg_data => x_msg_data,
122 p_api_type => g_api_type);
123
124 when OTHERS then
125 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
126 p_api_name => l_api_name,
127 p_pkg_name => g_pkg_name,
128 p_exc_name => 'OTHERS',
129 x_msg_count => x_msg_count,
130 x_msg_data => x_msg_data,
131 p_api_type => g_api_type);
132
133 END Update_header_status;
134
135 PROCEDURE validate_status ( x_return_status OUT NOCOPY VARCHAR2,
136 p_chr_sts_rec in chr_sts_type,
137 p_old_sts_code IN OUT NOCOPY VARCHAR2,
138 p_old_ste_code IN OUT NOCOPY VARCHAR2,
139 p_new_ste_code IN OUT NOCOPY VARCHAR2
140 )IS
141
142
143 cursor get_ste_code(p_code in varchar2) is
144 Select ste_code from okc_statuses_b where code = p_code;
145
146 cursor get_k_hdr_cur (p_id in number) is
147 Select sts_code, start_date, end_date from okc_k_headers_b where id = p_id;
148
149 CURSOR check_valid_status(p_new_sts_code VARCHAR2,
150 p_new_ste_code VARCHAR2,
151 p_old_sts_code VARCHAR2,
152 p_old_ste_code VARCHAR2,
153 p_start_date DATE,
154 p_end_date date
155 ) is
156 select 'Y' from dual where (p_new_sts_code,p_new_ste_code) in
157 (select
158 S.CODE STATUS_CODE
159 ,S.STE_CODE STE_CODE
160 from
161 okc_statuses_v S
162 ,fnd_lookups ST
163 where
164 S.STE_CODE in
165 (
166 NVL(p_old_ste_code,'ENTERED')
167 ,decode(p_old_ste_code,
168 NULL, 'CANCELLED',
169 'ENTERED','CANCELLED',
170 'ACTIVE','HOLD',
171 'SIGNED','HOLD',
172 'HOLD',decode(
173 NVL(sign(months_between
174 (p_start_date,sysdate+1)),1),
175 -1,decode(
176 NVL(sign(months_between(p_end_date,sysdate-1)),
177 1),1,'ACTIVE'
178 ,'EXPIRED'),'SIGNED')))
179 and sysdate between s.start_date and nvl(s.end_date,sysdate)
180 and st.lookup_type='OKC_STATUS_TYPE'
181 and st.lookup_code=s.ste_code
182 and sysdate between st.start_date_active and
183 nvl(st.end_date_active,sysdate)
184 and ST.enabled_flag='Y'
185 and S.code<>NVL(p_old_sts_code,'ENTERED')
186 and p_old_sts_code not like 'QA%HOLD'
187 and S.code not like 'QA%HOLD'
188 AND p_old_ste_code <> 'CANCELLED'
189 UNION ALL
190 SELECT S.CODE STATUS_CODE
191 ,S.STE_CODE STE_CODE1
192 FROM OKC_STATUSES_V S
193 ,FND_LOOKUPS ST
194 WHERE S.STE_CODE in ('ENTERED', 'CANCELLED')
195 AND SYSDATE BETWEEN S.START_DATE AND NVL(S.END_DATE, SYSDATE)
196 AND ST.LOOKUP_TYPE = 'OKC_STATUS_TYPE'
197 AND ST.LOOKUP_CODE=S.STE_CODE
198 AND SYSDATE BETWEEN ST.START_DATE_ACTIVE AND NVL(ST.END_DATE_ACTIVE, SYSDATE)
199 AND ST.ENABLED_FLAG = 'Y'
200 AND S.code <> p_old_sts_code
201 AND p_old_ste_code='CANCELLED');
202
203 l_new_ste_code varchar2(30);
204 l_old_ste_code varchar2(30);
205 l_old_sts_code varchar2(30);
206 l_new_sts_code varchar2(30);
207 l_start_date Date;
208 l_end_date Date;
209
210 l_allowed_flag VARCHAR2(1) := 'N';
211 BEGIN
212 x_return_status := OKE_API.G_RET_STS_SUCCESS;
213
214
215 IF ( p_chr_sts_rec.k_header_id IS NULL OR p_chr_sts_rec.k_header_id=OKC_API.G_MISS_NUM) THEN
216 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
217 p_msg_name =>G_REQUIRED_VALUE,
218 p_token1 =>G_COL_NAME_TOKEN,
219 p_token1_value =>'CONTRACT_HEADER_ID');
220 x_return_status := OKE_API.G_RET_STS_ERROR;
221 END IF;
222
223 IF ( p_chr_sts_rec.new_sts_code IS NULL OR p_chr_sts_rec.new_sts_code=OKC_API.G_MISS_CHAR) THEN
224 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
225 p_msg_name =>G_REQUIRED_VALUE,
226 p_token1 =>G_COL_NAME_TOKEN,
227 p_token1_value =>'NEW_STS_CODE');
228 x_return_status := OKE_API.G_RET_STS_ERROR;
229 END IF;
230
231 IF ( p_chr_sts_rec.reason_code IS NULL OR p_chr_sts_rec.new_sts_code=OKC_API.G_MISS_CHAR) THEN
232 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
233 p_msg_name =>G_REQUIRED_VALUE,
234 p_token1 =>G_COL_NAME_TOKEN,
235 p_token1_value =>'REASON_CODE');
236 x_return_status := OKE_API.G_RET_STS_ERROR;
237 END IF;
238
239 open get_k_hdr_cur(p_chr_sts_rec.k_header_id );
240 fetch get_k_hdr_cur into l_old_sts_code, l_start_date, l_end_date;
241 close get_k_hdr_cur;
242 p_old_sts_code:= l_old_sts_code;
243
244 open get_ste_code (l_old_sts_code);
245 fetch get_ste_code into l_old_ste_code;
246 close get_ste_code;
247 p_old_ste_code:= l_old_ste_code;
248
249 open get_ste_code (p_chr_sts_rec.new_sts_code);
250 fetch get_ste_code into l_new_ste_code;
251 close get_ste_code;
252 p_new_ste_code := l_new_ste_code;
253
254 /* IF p_old_stS_code = p_chr_sts_rec.new_sts_code THEN
255 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
256 p_msg_name =>'Status cannot be changed for the same user status') ;
257 x_return_status := OKE_API.G_RET_STS_ERROR;
258 END IF;
259 */
260 open check_valid_status(p_chr_sts_rec.new_sts_code,
261 p_new_ste_code ,
262 p_old_sts_code ,
263 p_old_ste_code ,
264 l_start_date ,
265 l_end_date );
266 fetch check_valid_status into l_allowed_flag;
267 close check_valid_status;
268
269 IF ( Nvl(l_allowed_flag,'N')<>'Y') THEN
270 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
271 p_msg_name =>G_INVALID_VALUE,
272 p_token1 =>G_COL_NAME_TOKEN,
273 p_token1_value =>'NEW_STS_CODE');
274 x_return_status := OKE_API.G_RET_STS_ERROR;
275
276
277 END IF;
278 exception
279 WHEN OTHERS THEN
280 -- store SQL error message on message stack
281 OKE_API.SET_MESSAGE(
282 p_app_name =>g_app_name,
283 p_msg_name =>G_UNEXPECTED_ERROR,
284 p_token1 =>G_SQLCODE_TOKEN,
285 p_token1_value =>SQLCODE,
286 p_token2 =>G_SQLERRM_TOKEN,
287 p_token2_value =>SQLERRM);
288 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
289
290 END validate_status;
291
292 PROCEDURE populate_table(x_chrv_tbl in out NOCOPY chrv_tbl_type, i in number) IS
293 l_object_version_number NUMBER;
294 l_new_sts_code VARCHAR2(100);
295
296 CURSOR c_obj_ver(p_id NUMBER) IS
297 SELECT object_version_number, decode(
298 NVL(sign(months_between(START_DATE,sysdate+1)),1),-1,decode(
299 NVL(sign(months_between(END_DATE,sysdate-1)),1),1,'ACTIVE','EXPIRED'),'SIGNED' )
300 FROM okc_k_headers_b
301 WHERE id = p_id;
302
303 BEGIN
304 x_chrv_tbl(i).VALIDATE_YN := 'N';
305
306 Open c_obj_ver(x_chrv_tbl(i).id);
307 Fetch c_obj_ver Into l_object_version_number, l_new_sts_code;
308 Close c_obj_ver;
309
310 x_chrv_tbl(1).object_version_number := l_object_version_number;
311 x_chrv_tbl(1).STS_CODE := x_chrv_tbl(i).new_sts_code;
312
313 If p_count = 1 Then
314 x_chrv_tbl(i).STS_CODE := x_chrv_tbl(i).new_sts_code;
315 Else
316 If x_chrv_tbl(i).old_ste_code = 'HOLD' Then -- old sts cdoe
317 If x_chrv_tbl(i).new_ste_code IN ('ACTIVE', 'SIGNED', 'EXPIRED') Then -- new ste code
318 x_chrv_tbl(i).STS_CODE :=l_new_sts_code;
319 End If;
320 End If;
321 End If;
322
323 END;
324
325
326
327 procedure Update_header_status(p_api_version IN NUMBER,
328 x_return_status OUT NOCOPY VARCHAR2,
329 x_msg_data OUT NOCOPY VARCHAR2,
330 x_msg_count OUT NOCOPY NUMBER,
331 p_init_msg_list in varchar2 default FND_API.G_FALSE,
332 p_chr_sts_rec in OUT NOCOPY chr_sts_type
333 ) is
334
335 l_api_name CONSTANT VARCHAR2(30) := 'Update_header_status';
336 l_api_version CONSTANT NUMBER := 1.0;
337 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
338
339 l_new_ste_code varchar2(30);
340 l_old_ste_code varchar2(30);
341 l_old_sts_code varchar2(30);
342 l_new_sts_code varchar2(30);
343 l_chrv_tbl chrv_tbl_type;
344 l_ste_code1 VARCHAR2(30);
345 p_control_rec okc_util.okc_control_rec_type;
346 l_line_return_status BOOLEAN;
347
348 BEGIN
349 -- call START_ACTIVITY to create savepoint, check compatibility
350 -- and initialize message list
351 l_return_status := OKE_API.START_ACTIVITY(
352 p_api_name => l_api_name,
353 p_pkg_name => g_pkg_name,
354 p_init_msg_list => p_init_msg_list,
355 l_api_version => l_api_version,
356 p_api_version => p_api_version,
357 p_api_type => g_api_type,
358 x_return_status => x_return_status);
359
360 -- check if activity started successfully
361 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
362 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
363 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
364 raise OKE_API.G_EXCEPTION_ERROR;
365 End If;
366
367 validate_status( x_return_status =>l_return_status ,
368 p_chr_sts_rec => p_chr_sts_rec,
369 p_old_sts_code=>l_old_sts_code,
370 p_old_ste_code=>l_old_ste_code,
371 p_new_ste_code =>l_new_ste_code
372 );
373
374 --- If any errors happen abort API
375 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
376 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
377 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
378 RAISE OKE_API.G_EXCEPTION_ERROR;
379 END IF;
380
381 l_chrv_tbl(1).id := p_chr_sts_rec.k_header_id;
382 l_chrv_tbl(1).old_sts_code := l_old_sts_code;
383 l_chrv_tbl(1).old_ste_code := l_old_ste_code;
384 l_chrv_tbl(1).new_sts_code := p_chr_sts_rec.new_sts_code;
385 l_chrv_tbl(1).new_ste_code := l_new_ste_code;
386 if (l_new_ste_code = 'CANCELLED') then
387 l_chrv_tbl(1).datetime_cancelled := Nvl(p_chr_sts_rec.datetime_cancelled,SYSDATE);
388 l_chrv_tbl(1).trn_code := p_chr_sts_rec.reason_code;
389 elsif (l_new_ste_code = 'ENTERED') then
390 l_chrv_tbl(1).datetime_cancelled := NULL;
391 l_chrv_tbl(1).trn_code := NULL;
392 end if;
393 populate_table(l_chrv_tbl, 1); -- p_chrv_tbl;
394
395 p_control_rec.flag := 'Y';
396 p_control_rec.code := p_chr_sts_rec.reason_code;
397 p_control_rec.comments := p_chr_sts_rec.comments;
398
399
400 -- Added the following code to place a lock on contract header.
401 okc_contract_pub.lock_contract_header(
402 p_api_version => 1.0,
403 p_init_msg_list => 'T',
404 x_return_status => l_return_status,
405 x_msg_count => x_msg_count,
406 x_msg_data => x_msg_data,
407 p_chrv_rec => l_chrv_tbl(1));
408
409 --- If any errors happen abort API
410 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
411 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
412 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
413 RAISE OKE_API.G_EXCEPTION_ERROR;
414 END IF;
415
416
417
418 OKC_CONTRACT_PUB.update_contract_header(
419 p_api_version => p_api_version,
420 P_INIT_MSG_LIST => 'F',
421 x_return_status => x_return_status,
422 x_msg_count => x_msg_count,
423 x_msg_data => x_msg_data,
424 p_restricted_update => 'T',
425 p_chrv_rec => l_chrv_tbl(1),
426 p_control_rec => p_control_rec,
427 x_chrv_rec => l_chrv_tbl(1));
428
429 --- If any errors happen abort API
430 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
431 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
432 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
433 RAISE OKE_API.G_EXCEPTION_ERROR;
434 END IF;
435
436
437
438
439 If l_new_ste_code = 'CANCELLED' then
440 l_ste_code1 := 'CANCELLED';
441 End if;
442
443 OKC_CONTRACT_PUB.UPDATE_LINES(p_id => p_chr_sts_rec.k_header_id,
444 p_sts_code => p_chr_sts_rec.new_sts_code ,
445 p_new_ste_code => l_new_ste_code,
446 p_old_ste_code => l_old_ste_code ,
447 p_ste_code => l_ste_code1,
448 x_return_status =>l_line_return_status);
449
450
451 --- If any errors happen abort API
452 IF (NOT l_line_return_status) THEN
453 l_return_status := OKE_API.G_RET_STS_ERROR ;
454 RAISE OKE_API.G_EXCEPTION_ERROR;
455 END IF;
456
457 x_return_status:=l_return_status;
458 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
459 x_msg_data => x_msg_data);
460 EXCEPTION
461 when OKE_API.G_EXCEPTION_ERROR then
462 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
463 p_api_name => l_api_name,
464 p_pkg_name => g_pkg_name,
465 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
466 x_msg_count => x_msg_count,
467 x_msg_data => x_msg_data,
468 p_api_type => g_api_type);
469
470 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
471 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
472 p_api_name => l_api_name,
473 p_pkg_name => g_pkg_name,
474 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
475 x_msg_count => x_msg_count,
476 x_msg_data => x_msg_data,
477 p_api_type => g_api_type);
478
479 when OTHERS then
480 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
481 p_api_name => l_api_name,
482 p_pkg_name => g_pkg_name,
483 p_exc_name => 'OTHERS',
484 x_msg_count => x_msg_count,
485 x_msg_data => x_msg_data,
486 p_api_type => g_api_type);
487
488 END Update_header_status;
489
490
491 end oke_change_status_pub;