[Home] [Help]
PACKAGE BODY: APPS.OKL_VP_STS_PVT
Source
1 PACKAGE BODY OKL_VP_STS_PVT AS
2 /*$Header: OKLRSSCB.pls 120.2 2006/07/14 12:27:26 cdubey noship $*/
3
4
5 PROCEDURE get_listof_new_statuses(
6 p_api_version IN NUMBER,
7 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
8 x_return_status OUT NOCOPY VARCHAR2,
9 x_msg_count OUT NOCOPY NUMBER,
10 x_msg_data OUT NOCOPY VARCHAR2,
11 p_ste_code IN VARCHAR2,
12 p_sts_code IN VARCHAR2,
13 p_start_date IN DATE,
14 p_end_date IN DATE,
15 x_sts_tbl OUT NOCOPY sts_tbl_type) IS
16
17
18 CURSOR cur_get_new_statuses(ste_code VARCHAR2,sts_code VARCHAR2,start_date DATE,end_date DATE) IS
19 SELECT
20 S.meaning STATUS,
21 S.code STATUS_CODE,
22 decode(S.DEFAULT_YN,'Y','*',' ') DEF,
23 ST.meaning STATUS_TYPE,
24 S.ste_code STE_CODE1
25 FROM
26 okc_statuses_v S, fnd_lookups ST
27 WHERE
28 S.ste_code IN (NVL(p_ste_code,'ENTERED'),decode(p_ste_code,NULL, 'CANCELLED','ENTERED','CANCELLED',
29 'ACTIVE','HOLD', 'SIGNED','HOLD', 'HOLD',DECODE(NVL(sign(months_between
30 (p_start_date,SYSDATE+1)),1), -1,
31 DECODE( NVL(sign(months_between(p_end_date,SYSDATE-1)),
32 1),1,'ACTIVE' ,'EXPIRED'),'SIGNED')))
33 AND SYSDATE BETWEEN S.start_date AND NVL(S.end_date,SYSDATE)
34 AND ST.lookup_type='OKC_STATUS_TYPE'
35 AND ST.lookup_code=s.ste_code
36 AND SYSDATE BETWEEN ST.start_date_active AND
37 NVL(ST.end_date_active,SYSDATE)
38 AND ST.enabled_flag='Y'
39 AND S.code<>NVL(p_sts_code,'ENTERED')
40 AND p_sts_code NOT LIKE 'QA%HOLD'
41 AND S.code NOT LIKE 'QA%HOLD'
42 AND p_ste_code <> 'CANCELLED'
43 UNION ALL
44 SELECT S.meaning STATUS
45 ,S.code STATUS_CODE
46 ,DECODE(S.DEFAULT_YN, 'Y', '*', '') DEF
47 ,ST.meaning STATUS_TYPE
48 ,S.ste_code STE_CODE1
49 FROM okc_statuses_v S
50 ,fnd_lookups ST
51 WHERE S.ste_code IN ('ENTERED', 'CANCELLED')
52 AND SYSDATE BETWEEN S.start_date AND NVL(S.end_date, SYSDATE)
53 AND ST.lookup_type = 'OKC_STATUS_TYPE'
54 AND ST.lookup_code=S.ste_code
55 AND SYSDATE BETWEEN ST.start_date_active AND NVL(ST.end_date_active, SYSDATE)
56 AND ST.enabled_flag = 'Y'
57 AND S.code <> p_sts_code
58 AND p_ste_code ='CANCELLED';
59
60 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
61 l_msg_count NUMBER;
62 l_msg_data VARCHAR2(2000);
63 l_sts_count NUMBER := 0;
64
65 BEGIN
66
67 x_return_status := OKL_API.G_RET_STS_SUCCESS;
68
69 FOR l_sts_rec IN cur_get_new_statuses(p_ste_code,p_sts_code,p_start_date,p_end_date)
70 LOOP
71 l_sts_count := l_sts_count + 1 ;
72 x_sts_tbl(l_sts_count).status := l_sts_rec.status;
73 x_sts_tbl(l_sts_count).status_code :=l_sts_rec.status_code;
74 END LOOP;
75
76 EXCEPTION
77 WHEN G_EXCEPTION_HALT_VALIDATION THEN
78 NULL;
79 WHEN OTHERS THEN
80 -- store SQL error message on message stack
81 OKC_API.SET_MESSAGE(
82 p_app_name => G_APP_NAME,
83 p_msg_name => G_UNEXPECTED_ERROR,
84 p_token1 => G_SQLCODE_TOKEN,
85 p_token1_value => SQLCODE,
86 p_token2 => G_SQLERRM_TOKEN,
87 p_token2_value => SQLERRM);
88 -- notify caller of an error as UNEXPETED error
89 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
90
91 IF cur_get_new_statuses%ISOPEN THEN
92 CLOSE cur_get_new_statuses;
93 END IF;
94
95 END get_listof_new_statuses;
96
97
98 PROCEDURE change_agreement_status(
99 p_api_version IN NUMBER,
100 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
101 x_return_status OUT NOCOPY VARCHAR2,
102 x_msg_count OUT NOCOPY NUMBER,
103 x_msg_data OUT NOCOPY VARCHAR2,
104 p_chr_id IN NUMBER,
105 p_current_sts_code IN VARCHAR2,
106 p_new_sts_code IN VARCHAR2) IS
107
108 CURSOR cur_get_status(p_id number) IS
109 SELECT sts_code , authoring_org_id FROM okc_k_headers_v --CDUBEY authoring_org_id added for MOAC
110 WHERE id=p_id;
111
112
113 l_return_status VARCHAR2(3);
114 l1_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
115 l_msg_count NUMBER;
116 l_msg_data VARCHAR2(2000);
117 l_api_version NUMBER := 1.0;
118 l_api_name CONSTANT VARCHAR2(30) := 'change_agreement_status';
119 l_database_sts_code VARCHAR2(30);
120 l_authoring_org_id NUMBER; --CDUBEY l_authoring_org_id added for MOAC
121
122 SUBTYPE chrv_rec_type IS OKC_CONTRACT_PUB.chrv_rec_type;
123
124 l1_header_rec chrv_rec_type;
125 l2_header_rec chrv_rec_type;
126
127 BEGIN
128
129 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name
130 ,p_init_msg_list => p_init_msg_list
131 ,p_api_type => '_PVT'
132 ,x_return_status => x_return_status
133 );
134 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
135 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
136 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
137 RAISE OKL_API.G_EXCEPTION_ERROR;
138 END IF;
139
140 IF (OKL_OKC_MIGRATION_A_PVT.update_allowed(p_chr_id) <> 'Y') THEN
141
142 l1_return_status :=OKL_API.G_RET_STS_ERROR;
143
144 OKC_API.set_message(p_app_name => g_app_name,
145 p_msg_name => 'OKL_VP_UPDATE_NOT_ALLOWED'
146 );
147
148 RAISE OKL_API.G_EXCEPTION_ERROR;
149
150 END IF;
151 IF (p_new_sts_code NOT IN ('ABANDONED','PASSED','INCOMPLETE') OR
152 -- Manu 11-Jul-2005. Added Status INCOMPLETE --
153 (p_current_sts_code NOT IN ('NEW','PENDING','PASSED','INCOMPLETE'))) THEN
154 OKC_API.set_message(p_app_name => g_app_name,
155 p_msg_name => 'OKL_INVALID_CHANGE_STS'
156 );
157 RAISE OKL_API.G_EXCEPTION_ERROR;
158 ELSE
159 IF (p_current_sts_code = 'PENDING') THEN
160 OKL_CONTRACT_APPROVAL_PUB.STOP_PROCESS(p_api_version,
161 p_init_msg_list,
162 x_return_status,
163 x_msg_count,
164 x_msg_data,
165 p_chr_id,
166 'Y');
167 IF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
168 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
169 ELSIF x_return_status = OKL_API.G_RET_STS_ERROR THEN
170 RAISE OKL_API.G_EXCEPTION_ERROR;
171 END IF;
172 END IF;
173 END IF;
174
175 IF (OKL_VENDOR_PROGRAM_PUB.is_process_active(p_chr_id) <> 'N') THEN
176
177 OKC_API.set_message(p_app_name => g_app_name,
178 p_msg_name => 'OKL_VP_APPROVAL_PROCESS_ACTV'
179 );
180 RAISE OKL_API.G_EXCEPTION_ERROR;
181
182 END IF;
183
184 x_return_status := OKL_API.G_RET_STS_SUCCESS;
185
186 OPEN cur_get_status(p_chr_id);
187 FETCH cur_get_status INTO l_database_sts_code,l_authoring_org_id; --CDUBEY l_authoring_org_id added for MOAC
188
189 IF(cur_get_status%FOUND) THEN
190 CLOSE cur_get_status;
191 ELSE
192 l1_return_status :=okl_api.g_ret_sts_error;
193 close cur_get_status;
194 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_VP_AGREEMENT_NOT_FOUND');
195 RAISE OKL_API.G_EXCEPTION_ERROR;
196 END IF;
197
198
199 l1_header_rec.id :=p_chr_id;
200 l1_header_rec.sts_code :=p_new_sts_code;
201 l1_header_rec.org_id :=l_authoring_org_id; --CDUBEY added for MOAC
202
203
204 IF(p_current_sts_code=l_database_sts_code) THEN
205 OKC_CONTRACT_PUB.update_contract_header(p_api_version => l_api_version,
206 x_return_status => l_return_status,
207 p_init_msg_list => OKL_API.G_TRUE,
208 x_msg_count => l_msg_count,
209 x_msg_data => l_msg_data,
210 p_restricted_update => OKL_API.G_FALSE,
211 p_chrv_rec => l1_header_rec,
212 x_chrv_rec => l2_header_rec);
213
214 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
215 NULL;
216 ELSE
217 l1_return_status := l_return_status;
218 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
219 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
220 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
221 RAISE OKL_API.G_EXCEPTION_ERROR;
222 END IF;
223 END IF;
224 ELSE
225
226 l1_return_status :=okl_api.g_ret_sts_error;
227
228 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
229 p_msg_name => 'OKL_VP_INVALID_CURRENT_STATUS');
230 RAISE OKL_API.G_EXCEPTION_ERROR;
231 END IF;
232
233 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count
234 ,x_msg_data => x_msg_data
235 );
236
237 EXCEPTION
238
239 WHEN OKL_API.G_EXCEPTION_ERROR THEN
240
241 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
242 ,g_pkg_name
243 ,'OKL_API.G_RET_STS_ERROR'
244 ,x_msg_count
245 ,x_msg_data
246 ,'_PVT'
247 );
248
249
250 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
251
252 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
253 ,g_pkg_name
254 ,'OKL_API.G_RET_STS_ERROR'
255 ,x_msg_count
256 ,x_msg_data
257 ,'_PVT'
258 );
259
260 WHEN OTHERS THEN
261
262 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
263 ,g_pkg_name
264 ,'OTHERS'
265 ,x_msg_count
266 ,x_msg_data
267 ,'_PVT'
268 );
269
270
271 END change_agreement_status;
272
273
274 END OKL_VP_STS_PVT;