[Home] [Help]
PACKAGE BODY: APPS.OKL_CONTRACT_APPROVAL_PVT
Source
1 Package Body OKL_CONTRACT_APPROVAL_PVT AS
2 /* $Header: OKLRCAVB.pls 120.2 2006/07/14 12:58:27 cdubey noship $ */
3
4 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
5
6
7
8 -- Start of comments
9 -- Procedure Name : reference_exists
10 -- Description : This function checks if there already exists a Program
11 -- Template with the same Reference Program Agreement
12 -- number.
13 -- Business Rules :
14 -- Parameters :
15 -- Version : 1.0
16 -- End of comments
17
18 -- Fix Bug 3159867
19
20 FUNCTION REFERENCE_EXISTS(p_contract_id IN NUMBER) RETURN VARCHAR2 IS
21 l_id NUMBER;
22 l_khr_id NUMBER;
23 CURSOR khr_id_cur IS
24 SELECT khr_id FROM okl_k_headers_full_v
25 WHERE id = p_contract_id;
26
27 CURSOR id_cur(p_khr_id IN NUMBER) IS
28 SELECT id FROM okl_k_headers_full_v
29 WHERE khr_id = p_khr_id
30 AND sts_code = 'ACTIVE';
31
32 l_return_status VARCHAR2(2000) := 'N';
33 BEGIN
34
35 OPEN khr_id_cur;
36 FETCH khr_id_cur INTO l_khr_id;
37 IF khr_id_cur%found THEN
38
39 OPEN id_cur(l_khr_id);
40 FETCH id_cur INTO l_id;
41
42 IF id_cur%found THEN
43 l_return_status := 'Y';
44 END IF;
45
46 CLOSE id_cur;
47 END IF;
48 CLOSE khr_id_cur;
49
50 return l_return_status;
51
52 EXCEPTION
53 WHEN OTHERS THEN
54 return 'N';
55
56 END;
57
58
59 -- Start of comments
60 --
61 -- Procedure Name : start_process
62 -- Description :
63 -- Business Rules :
64 -- Parameters :
65 -- Version : 1.0
66 -- End of comments
67 PROCEDURE START_PROCESS(p_api_version IN NUMBER,
68 p_init_msg_list IN VARCHAR2,
69 x_return_status OUT NOCOPY VARCHAR2,
70 x_msg_count OUT NOCOPY NUMBER,
71 x_msg_data OUT NOCOPY VARCHAR2,
72 p_contract_id IN NUMBER,
73 p_status IN VARCHAR2,
74 p_do_commit IN VARCHAR2) IS
75 cursor pdf_cur is select pdf_id from okc_k_processes where chr_id = p_contract_id;
76 l_pdf_id varchar2(200);
77 SUBTYPE chrv_rec_type IS OKC_CONTRACT_PUB.chrv_rec_type;
78 l1_header_rec chrv_rec_type;
79 l2_header_rec chrv_rec_type;
80
81 l_authoring_org_id NUMBER; --CDUBEY l_authoring_org_id added for MOAC
82
83 -- Fix Bug 3159867
84
85 CURSOR khr_num IS SELECT contract_number , authoring_org_id FROM okc_k_headers_b
86 WHERE id = (SELECT khr_id FROM okl_k_headers_full_v
87 WHERE id = p_contract_id);
88 l_khr_number okc_k_headers_b.contract_number%TYPE;
89
90 BEGIN
91 -- CHECK_FOR_NULL_CONTRACT_ID;
92 -- If status type is not 'PASSED', do not proceed
93 If (p_status <> 'PASSED') Then
94 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_QA_NOT_PASSED');
95 RAISE OKL_API.G_EXCEPTION_ERROR;
96 End If;
97 open pdf_cur;
98 loop
99 fetch pdf_cur into l_pdf_id;
100 exit when pdf_cur%notfound;
101 null;
102 end loop;
103 /*OKC_CONTRACT_APPROVAL_PUB.K_APPROVAL_START(p_api_version,
104 p_init_msg_list,
105 x_return_status,
106 x_msg_count,
107 x_msg_data,
108 p_contract_id,
109 l_pdf_id,
110 p_do_commit
111 );
112 */
113
114
115 OPEN khr_num;
116 FETCH khr_num INTO l_khr_number,l_authoring_org_id; --CDUBEY l_authoring_org_id added for MOAC
117 CLOSE khr_num;
118
119 -- Temporary Fix for making Contract active without contract approval process
120 l1_header_rec.id := p_contract_id;
121 l1_header_rec.sts_code := 'ACTIVE';
122 l1_header_rec.org_id :=l_authoring_org_id; --CDUBEY added for MOAC
123
124 -- Fix Bug 3159867
125 IF (reference_exists(p_contract_id) = 'Y') THEN
126 OKC_API.set_message(p_app_name => g_app_name,
127 p_msg_name => 'OKL_VP_REFERENCE_EXISTS',
128 p_token1 => 'NUMBER',
129 p_token1_value => l_khr_number);
130
131 x_return_status := okc_api.g_ret_sts_error;
132 RAISE OKL_API.G_EXCEPTION_ERROR;
133 END IF;
134
135 OKC_CONTRACT_PUB.update_contract_header(
136 p_api_version => p_api_version,
137 x_return_status => x_return_status,
138 p_init_msg_list => OKL_API.G_TRUE,
139 x_msg_count => x_msg_count,
140 x_msg_data => x_msg_data,
141 p_restricted_update => OKL_API.G_FALSE,
142 p_chrv_rec => l1_header_rec,
143 x_chrv_rec => l2_header_rec);
144 IF (x_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
145 NULL;
146 ELSE
147 IF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
148 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
149 ELSIF x_return_status = OKL_API.G_RET_STS_ERROR THEN
150 RAISE OKL_API.G_EXCEPTION_ERROR;
151 END IF;
152 END IF;
153
154 EXCEPTION
155 WHEN OKL_API.G_EXCEPTION_ERROR THEN
156 x_return_status := OKL_API.G_RET_STS_ERROR;
157 /*
158 x_return_status := OKL_API.HANDLE_EXCEPTIONS
159 (p_api_name => l_api_name
160 ,p_pkg_name => G_PKG_NAME
161 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
162 ,x_msg_count => x_msg_count
163 ,x_msg_data => x_msg_data
164 ,p_api_type => '_PVT'
165 );
166
167 */
168 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
169 x_return_status := OKL_API.G_RET_STS_ERROR;
170 /* x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
171 ,g_pkg_name
172 ,'OKL_API.G_RET_STS_ERROR'
173 ,x_msg_count
174 ,x_msg_data
175 ,'_PVT'
176 );
177
178 */
179 WHEN OTHERS THEN
180 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
181 /* x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
182 ,g_pkg_name
183 ,'OTHERS'
184 ,x_msg_count
185 ,x_msg_data
186 ,'_PVT'
187 );
188 */
189 END;
190 -- Start of comments
191 --
192 -- Procedure Name : stop_process
193 -- Description :
194 -- Business Rules :
195 -- Parameters :
196 -- Version : 1.0
197 -- End of comments
198 PROCEDURE STOP_PROCESS(p_api_version IN NUMBER,
199 p_init_msg_list IN VARCHAR2,
200 x_return_status OUT NOCOPY VARCHAR2,
201 x_msg_count OUT NOCOPY NUMBER,
202 x_msg_data OUT NOCOPY VARCHAR2,
203 p_contract_id IN NUMBER,
204 p_do_commit IN VARCHAR2) IS
205 begin
206 -- CHECK_FOR_NULL_CONTRACT_ID;
207 okc_contract_approval_pub.k_approval_stop(p_api_version,
208 p_init_msg_list,
209 x_return_status,
210 x_msg_count,
211 x_msg_data,
212 p_contract_id,
213 p_do_commit);
214 end;
215 -- Start of comments
216 --
217 -- Procedure Name : monitor_process
218 -- Description :
219 -- Business Rules :
220 -- Parameters :
221 -- Version : 1.0
222 -- End of comments
223 FUNCTION MONITOR_PROCESS(p_api_version IN number,
224 p_init_msg_list IN varchar2,
225 x_return_status OUT NOCOPY varchar2,
226 x_msg_count OUT NOCOPY number,
227 x_msg_data OUT NOCOPY varchar2,
228 p_contract_id IN NUMBER,
229 p_pdf_id IN NUMBER) RETURN VARCHAR2 IS
230 l_id NUMBER := p_contract_id;
231 l_pdf_id NUMBER := p_pdf_id;
232 l_return_status VARCHAR2(2000);
233 BEGIN
234 -- CHECK_FOR_NULL_CONTRACT_ID;
235 If (okc_contract_approval_pub.wf_monitor_url(l_id,l_pdf_id,'USER') is null) Then
236 -- MAIN.Show_Error_Message('OKC_URL_CANNOT_OPEN');
237 return 'http://';
238 End If;
239 -- FND_UTILITIES.open_url(okc_contract_approval_pub.wf_monitor_url(l_id,l_pdf_id,'USER'));
240 l_return_status := okc_contract_approval_pub.WF_MONITOR_URL(l_id, l_pdf_id, 'USER');
241 return l_return_status;
242 EXCEPTION
243 WHEN OTHERS THEN
244 NULL;
245 return 'http://';
246 --Show_Error_Message('OKC_URL_CANNOT_OPEN');
247 END;
248 -- Start of comments
249 --
250 -- Procedure Name : populate_active_process
251 -- Description :
252 -- Business Rules :
253 -- Parameters :
254 -- Version : 1.0
255 -- End of comments
256 PROCEDURE populate_active_process(p_api_version IN number,
257 p_init_msg_list IN varchar2,
258 x_return_status OUT NOCOPY varchar2,
259 x_msg_count OUT NOCOPY number,
260 x_msg_data OUT NOCOPY varchar2,
261 p_contract_number IN VARCHAR2,
262 p_contract_number_modifier IN VARCHAR2,
263 x_wf_name OUT NOCOPY VARCHAR2,
264 x_wf_process_name OUT NOCOPY VARCHAR2,
265 x_package_name OUT NOCOPY VARCHAR2,
266 x_procedure_name OUT NOCOPY VARCHAR2,
267 x_usage OUT NOCOPY VARCHAR2,
268 x_activeyn OUT NOCOPY VARCHAR2) IS
269 l_name VARCHAR2(150);
270 l_wf_name VARCHAR(200);
271 CURSOR cur_contract_status IS
272 SELECT sts_code FROM okc_k_headers_v WHERE contract_number = p_contract_number
273 AND contract_number_modifier = p_contract_number_modifier;
274 l_sts_code VARCHAR2(200);
275
276 BEGIN
277 /*OKC_CONTRACT_PUB.Get_Active_Process (p_api_version,
278 p_init_msg_list,
279 x_return_status,
280 x_msg_count,
281 x_msg_data,
282 p_contract_number,
283 p_contract_number_modifier,
284 x_wf_name,
285 x_wf_process_name,
286 x_package_name,
287 x_procedure_name,
288 x_usage);
289 l_wf_name := x_wf_name;
290 If (x_return_status ='S' and l_wf_name is not null) Then
291 x_activeyn := 'Y';
292 Else
293 x_activeyn := 'N';
294 END IF;
295 EXCEPTION
296 When OTHERS Then
297 NULL;
298 END;
299 */
300
301 OPEN cur_contract_status;
302 FETCH cur_contract_status INTO l_sts_code;
303 IF(cur_contract_status%found) THEN
304 NULL;
305 CLOSE cur_contract_status;
306 ELSE
307 CLOSE cur_contract_status;
308 RAISE OKL_API.G_EXCEPTION_ERROR;
309 END IF;
310
311 IF (l_sts_code = 'ACTIVE') THEN
312 x_activeyn := 'Y';
313 ELSE
314 x_activeyn := 'N';
315 END IF;
316
317 END;
318
319 END; -- Package Body OKL_CONTRACT_APPROVAL_PVT