[Home] [Help]
PACKAGE BODY: APPS.OKL_VP_AGRMNT_APPROVAL_PVT
Source
1 PACKAGE BODY okl_vp_agrmnt_approval_pvt AS
2 /* $Header: OKLRVAAB.pls 120.0 2005/07/28 11:44:04 sjalasut noship $ */
3
4 G_AGR_NOT_PASSED_FOR_APPROVE CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_VN_AGR_NOT_PASS_APPROVE';
5
6 G_PENDING_STS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'PENDING_APPROVAL';
7 G_REJECTED_STS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'REJECTED';
8 G_PASSED_STS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'PASSED';
9 G_ACTIVE_STS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'ACTIVE';
10
11 PROCEDURE submit_oa_for_approval(p_api_version IN NUMBER
12 ,p_init_msg_list IN VARCHAR2
13 ,x_return_status OUT NOCOPY VARCHAR2
14 ,x_msg_count OUT NOCOPY NUMBER
15 ,x_msg_data OUT NOCOPY VARCHAR2
16 ,p_chr_id IN okc_k_headers_b.id%TYPE
17 ,x_status_code OUT NOCOPY okc_k_headers_b.scs_code%TYPE
18 ) IS
19 CURSOR c_get_oa_sts_csr(cp_chr_id okc_k_headers_b.id%TYPE)IS
20 SELECT sts_code
21 ,contract_number
22 FROM okc_k_headers_b
23 WHERE id = cp_chr_id;
24 lv_oa_sts_code okc_k_headers_b.sts_code%TYPE;
25 lv_agreement_number okc_k_headers_b.contract_number%TYPE;
26 l_approval_process VARCHAR2(30);
27 l_api_version CONSTANT NUMBER DEFAULT 1.0;
28 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SUBMIT_OA_FOR_APPROVAL';
29 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_AGRMNT_APPROVAL_PVT.SUBMIT_OA_FOR_APPROVAL';
30 l_debug_enabled VARCHAR2(10);
31
32 BEGIN
33 x_return_status := OKL_API.G_RET_STS_SUCCESS;
34
35 l_debug_enabled := okl_debug_pub.check_log_enabled;
36
37 IF(l_debug_enabled='Y') THEN
38 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVAAB.pls call submit_oa_for_approval');
39 END IF;
40
41 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
42 x_return_status := OKL_API.START_ACTIVITY(
43 p_api_name => l_api_name
44 ,p_pkg_name => G_PKG_NAME
45 ,p_init_msg_list => p_init_msg_list
46 ,l_api_version => l_api_version
47 ,p_api_version => p_api_version
48 ,p_api_type => g_api_type
49 ,x_return_status => x_return_status);
50 -- check if activity started successfully
51 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
52 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
53 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
54 RAISE OKL_API.G_EXCEPTION_ERROR;
55 END IF;
56
57 -- basic validation. oa should be in status passed before it can be submitted for approval
58 OPEN c_get_oa_sts_csr (p_chr_id); FETCH c_get_oa_sts_csr INTO lv_oa_sts_code, lv_agreement_number;
59 CLOSE c_get_oa_sts_csr;
60 IF(lv_oa_sts_code <> G_PASSED_STS_CODE)THEN
61 OKL_API.set_message(G_APP_NAME, G_AGR_NOT_PASSED_FOR_APPROVE,'AGR_NUMBER',lv_agreement_number);
62 RAISE OKL_API.G_EXCEPTION_ERROR;
63 END IF;
64 -- now that the validation has passed, set the OUT status as PENDING, this is the default case
65 x_status_code := G_PENDING_STS_CODE;
66 -- read the profile OKL: Operating Agreement Approval Process
67 l_approval_process := fnd_profile.value('OKL_VP_OA_APPROVAL_PROCESS');
68 IF(NVL(l_approval_process,'NONE')='NONE')THEN
69 -- since no option is set at the profile, approve the operating agreement by default
70 okl_contract_status_pub.update_contract_status(p_api_version => p_api_version
71 ,p_init_msg_list => p_init_msg_list
72 ,x_return_status => x_return_status
73 ,x_msg_count => x_msg_count
74 ,x_msg_data => x_msg_data
75 ,p_khr_status => G_ACTIVE_STS_CODE
76 ,p_chr_id => p_chr_id
77 );
78 -- write to log
79 IF(NVL(l_debug_enabled,'N')='Y') THEN
80 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
81 l_module,
82 'okl_contract_status_pub.update_contract_status G_ACTIVE_STS_CODE returned with status '||x_return_status||' x_msg_data '||x_msg_data
83 );
84 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
85 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
86 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
87 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
88 RAISE OKL_API.G_EXCEPTION_ERROR;
89 END IF;
90 -- the approval process profile option is not set, since we are activating the agreement in this case,
91 -- the OUT variable should be populated accordingly.
92 x_status_code := G_ACTIVE_STS_CODE;
93 ELSIF(l_approval_process IN ('AME','WF'))THEN
94 -- for the case of workflow or Approvals Management profile option, the agreement is set to status
95 -- G_PENDING_STS_CODE
96 okl_contract_status_pub.update_contract_status(p_api_version => p_api_version
97 ,p_init_msg_list => p_init_msg_list
98 ,x_return_status => x_return_status
99 ,x_msg_count => x_msg_count
100 ,x_msg_data => x_msg_data
101 ,p_khr_status => G_PENDING_STS_CODE
102 ,p_chr_id => p_chr_id
103 );
104 -- write to log
105 IF(NVL(l_debug_enabled,'N')='Y') THEN
106 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
107 l_module,
108 'okl_contract_status_pub.update_contract_status G_PENDING_STS_CODE returned with status '||x_return_status||' x_msg_data '||x_msg_data
109 );
110 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
111 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
112 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
113 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
114 RAISE OKL_API.G_EXCEPTION_ERROR;
115 END IF;
116 okl_vp_oa_wf.raise_oa_event_approval(p_api_version => p_api_version
117 ,p_init_msg_list => p_init_msg_list
118 ,x_return_status => x_return_status
119 ,x_msg_count => x_msg_count
120 ,x_msg_data => x_msg_data
121 ,p_chr_id => p_chr_id);
122 -- write to log
123 IF(NVL(l_debug_enabled,'N')='Y') THEN
124 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
125 l_module,
126 'okl_vp_oa_wf.raise_oa_event_approval returned with status '||x_return_status||' x_msg_data '||x_msg_data
127 );
128 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
129 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
130 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
131 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
132 RAISE OKL_API.G_EXCEPTION_ERROR;
133 END IF;
134 END IF; -- end of NVL(l_approval_process,'NONE')='NONE'
135
136 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
137
138 IF(l_debug_enabled='Y') THEN
139 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVAAB.pls call submit_oa_for_approval');
140 END IF;
141
142 EXCEPTION
143 WHEN OKL_API.G_EXCEPTION_ERROR THEN
144 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
145 p_api_name => l_api_name,
146 p_pkg_name => G_PKG_NAME,
147 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
148 x_msg_count => x_msg_count,
149 x_msg_data => x_msg_data,
150 p_api_type => g_api_type);
151
152 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
153 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
154 p_api_name => l_api_name,
155 p_pkg_name => G_PKG_NAME,
156 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
157 x_msg_count => x_msg_count,
158 x_msg_data => x_msg_data,
159 p_api_type => g_api_type);
160
161 WHEN OTHERS THEN
162 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
163 p_api_name => l_api_name,
164 p_pkg_name => G_PKG_NAME,
165 p_exc_name => 'OTHERS',
166 x_msg_count => x_msg_count,
167 x_msg_data => x_msg_data,
168 p_api_type => g_api_type);
169 END submit_oa_for_approval;
170
171 PROCEDURE submit_pa_for_approval(p_api_version IN NUMBER
172 ,p_init_msg_list IN VARCHAR2
173 ,x_return_status OUT NOCOPY VARCHAR2
174 ,x_msg_count OUT NOCOPY NUMBER
175 ,x_msg_data OUT NOCOPY VARCHAR2
176 ,p_chr_id IN okc_k_headers_b.id%TYPE
177 ,x_status_code OUT NOCOPY okc_k_headers_b.scs_code%TYPE
178 ) IS
179 CURSOR c_get_pa_sts_csr(cp_chr_id okc_k_headers_b.id%TYPE)IS
180 SELECT sts_code
181 ,contract_number
182 FROM okc_k_headers_b
183 WHERE id = cp_chr_id;
184 lv_pa_sts_code okc_k_headers_b.sts_code%TYPE;
185 lv_agreement_number okc_k_headers_b.contract_number%TYPE;
186 l_approval_process fnd_lookups.lookup_code%TYPE;
187 l_api_version CONSTANT NUMBER DEFAULT 1.0;
188 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SUBMIT_PA_FOR_APPROVAL';
189 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_AGRMNT_APPROVAL_PVT.SUBMIT_PA_FOR_APPROVAL';
190 l_debug_enabled VARCHAR2(10);
191
192 BEGIN
193 x_return_status := OKL_API.G_RET_STS_SUCCESS;
194
195 l_debug_enabled := okl_debug_pub.check_log_enabled;
196
197 IF(l_debug_enabled='Y') THEN
198 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRVAAB.pls call submit_pa_for_approval');
199 END IF;
200
201 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
202 x_return_status := OKL_API.START_ACTIVITY(
203 p_api_name => l_api_name
204 ,p_pkg_name => G_PKG_NAME
205 ,p_init_msg_list => p_init_msg_list
206 ,l_api_version => l_api_version
207 ,p_api_version => p_api_version
208 ,p_api_type => g_api_type
209 ,x_return_status => x_return_status);
210 -- check if activity started successfully
211 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
212 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
213 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
214 RAISE OKL_API.G_EXCEPTION_ERROR;
215 END IF;
216
217 -- basic validation. oa should be in status passed before it can be submitted for approval
218 OPEN c_get_pa_sts_csr (p_chr_id); FETCH c_get_pa_sts_csr INTO lv_pa_sts_code, lv_agreement_number;
219 CLOSE c_get_pa_sts_csr;
220 IF(lv_pa_sts_code <> G_PASSED_STS_CODE)THEN
221 OKL_API.set_message(G_APP_NAME, G_AGR_NOT_PASSED_FOR_APPROVE,'AGR_NUMBER',lv_agreement_number);
222 RAISE OKL_API.G_EXCEPTION_ERROR;
223 END IF;
224 -- now that the validation has passed, set the OUT status as PENDING, this is the default case
225 x_status_code := G_PENDING_STS_CODE;
226 -- read the profile OKL: Program Agreement Approval Process
227 l_approval_process := fnd_profile.value('OKL_VP_PA_APPROVAL_PROCESS');
228 IF(NVL(l_approval_process,'NONE')='NONE')THEN
229 -- since no option is set at the profile, approve the operating agreement by default
230 okl_contract_status_pub.update_contract_status(p_api_version => p_api_version
231 ,p_init_msg_list => p_init_msg_list
232 ,x_return_status => x_return_status
233 ,x_msg_count => x_msg_count
234 ,x_msg_data => x_msg_data
235 ,p_khr_status => G_ACTIVE_STS_CODE
236 ,p_chr_id => p_chr_id
237 );
238 -- write to log
239 IF(NVL(l_debug_enabled,'N')='Y') THEN
240 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
241 l_module,
242 'okl_contract_status_pub.update_contract_status returned with status '||x_return_status||' x_msg_data '||x_msg_data
243 );
244 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
245 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
246 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
247 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
248 RAISE OKL_API.G_EXCEPTION_ERROR;
249 END IF;
250 -- the approval process profile option is not set, since we are activating the agreement in this case,
251 -- the OUT variable should be populated accordingly.
252 x_status_code := G_ACTIVE_STS_CODE;
253 ELSIF(l_approval_process IN ('AME','WF'))THEN
254 -- for the case of workflow or Approvals Management profile option, the agreement is set to status
255 -- G_PENDING_STS_CODE
256 okl_contract_status_pub.update_contract_status(p_api_version => p_api_version
257 ,p_init_msg_list => p_init_msg_list
258 ,x_return_status => x_return_status
259 ,x_msg_count => x_msg_count
260 ,x_msg_data => x_msg_data
261 ,p_khr_status => G_PENDING_STS_CODE
262 ,p_chr_id => p_chr_id
263 );
264 -- write to log
265 IF(NVL(l_debug_enabled,'N')='Y') THEN
266 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
267 l_module,
268 'okl_contract_status_pub.update_contract_status G_PENDING_STS_CODE returned with status '||x_return_status||' x_msg_data '||x_msg_data
269 );
270 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
271 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
272 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
273 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
274 RAISE OKL_API.G_EXCEPTION_ERROR;
275 END IF;
276 x_status_code := G_PENDING_STS_CODE;
277 okl_vp_pa_wf.raise_pa_event_approval(p_api_version => p_api_version
278 ,p_init_msg_list => p_init_msg_list
279 ,x_return_status => x_return_status
280 ,x_msg_count => x_msg_count
281 ,x_msg_data => x_msg_data
282 ,p_chr_id => p_chr_id);
283 -- write to log
284 IF(NVL(l_debug_enabled,'N')='Y') THEN
285 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
286 l_module,
287 ' okl_vp_pa_wf.raise_pa_event_approval returned with status '||x_return_status||' x_msg_data '||x_msg_data
288 );
289 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
290
291 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
292 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
293 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
294 RAISE OKL_API.G_EXCEPTION_ERROR;
295 END IF;
296 END IF; -- end of NVL(l_approval_process,'NONE')='NONE'
297
298 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
299
300 IF(l_debug_enabled='Y') THEN
301 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRVAAB.pls call submit_pa_for_approval');
302 END IF;
303
304 EXCEPTION
305 WHEN OKL_API.G_EXCEPTION_ERROR THEN
306 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
307 p_api_name => l_api_name,
308 p_pkg_name => G_PKG_NAME,
309 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
310 x_msg_count => x_msg_count,
311 x_msg_data => x_msg_data,
312 p_api_type => g_api_type);
313
314 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
315 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
316 p_api_name => l_api_name,
317 p_pkg_name => G_PKG_NAME,
318 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
319 x_msg_count => x_msg_count,
320 x_msg_data => x_msg_data,
321 p_api_type => g_api_type);
322
323 WHEN OTHERS THEN
324 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
325 p_api_name => l_api_name,
326 p_pkg_name => G_PKG_NAME,
327 p_exc_name => 'OTHERS',
328 x_msg_count => x_msg_count,
329 x_msg_data => x_msg_data,
330 p_api_type => g_api_type);
331 END submit_pa_for_approval;
332
333 END okl_vp_agrmnt_approval_pvt;