[Home] [Help]
PACKAGE BODY: APPS.OKL_CURE_REQ_AMT_PVT
Source
1 PACKAGE BODY OKL_CURE_REQ_AMT_PVT AS
2 /* $Header: OKLRCRKB.pls 120.3 2007/06/18 19:42:13 pdevaraj ship $ */
3
4
5 PROCEDURE update_cure_request(
6 p_api_version IN NUMBER,
7 p_init_msg_list IN VARCHAR2 ,
8 x_return_status OUT NOCOPY VARCHAR2,
9 x_msg_count OUT NOCOPY NUMBER,
10 x_msg_data OUT NOCOPY VARCHAR2,
11 p_cure_req_tbl IN cure_req_tbl_type,
12 x_cure_req_tbl OUT NOCOPY cure_req_tbl_type) IS
13
14 l_api_name VARCHAR2(30) := 'update_cure_request';
15 l_api_version CONSTANT NUMBER := 1.0;
16 i NUMBER;
17 l_obj_vers_no NUMBER;
18 l_selected_on_request okl_cure_amounts.selected_on_request%type := null;
19 lp_camv_tbl okl_cure_amounts_pub.camv_tbl_type;
20 lx_camv_tbl okl_cure_amounts_pub.camv_tbl_type;
21
22 Cursor get_obj_vers_no(l_cure_amount_id NUMBER) is
23 select object_version_number, selected_on_request
24 from okl_cure_amounts
25 where cure_amount_id = l_cure_amount_id;
26
27
28 BEGIN
29
30 okl_debug_pub.logmessage('OKL_CURE_REQ_AMT_PVT: update_cure_request : START');
31
32 x_return_status := OKC_API.START_ACTIVITY(
33 p_api_name => l_api_name,
34 p_pkg_name => g_pkg_name,
35 p_init_msg_list => p_init_msg_list,
36 l_api_version => l_api_version,
37 p_api_version => p_api_version,
38 p_api_type => g_api_type,
39 x_return_status => x_return_status);
40
41 -- check if activity started successfully
42 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
43 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
44 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
45 raise OKC_API.G_EXCEPTION_ERROR;
46 End If;
47
48
49 If (p_cure_req_tbl.COUNT > 0) Then
50 i := p_cure_req_tbl.FIRST;
51
52 LOOP
53
54 lp_camv_tbl(i).cure_amount_id := p_cure_req_tbl(i).cure_amount_id;
55 lp_camv_tbl(i).crt_id := p_cure_req_tbl(i).CURE_REPORT_ID;
56 lp_camv_tbl(i).object_version_number := null;
57
58
59 l_obj_vers_no := null;
60 l_selected_on_request := null;
61 open get_obj_vers_no(lp_camv_tbl(i).cure_amount_id);
62 fetch get_obj_vers_no into l_obj_vers_no, l_selected_on_request;
63 close get_obj_vers_no;
64
65 If ( p_cure_req_tbl(i).CURE_REPORT_ID is null ) Then
66 lp_camv_tbl(i).selected_on_request := 'N';
67 Elsif ( p_cure_req_tbl(i).CURE_REPORT_ID is not null ) Then
68 lp_camv_tbl(i).selected_on_request := 'Y';
69 End If;
70
71 if(l_obj_vers_no is not null) Then
72 lp_camv_tbl(i).object_version_number := l_obj_vers_no;
73 End If;
74
75 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
76 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
77 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
78 raise OKC_API.G_EXCEPTION_ERROR;
79 End If;
80
81 EXIT WHEN (i = p_cure_req_tbl.LAST);
82 i := p_cure_req_tbl.NEXT(i);
83 END LOOP;
84
85 okl_cure_amounts_pub.update_cure_amounts(
86 p_api_version => p_api_version,
87 p_init_msg_list => p_init_msg_list,
88 x_return_status => x_return_status,
89 x_msg_count => x_msg_count,
90 x_msg_data => x_msg_data,
91 p_camv_tbl => lp_camv_tbl,
92 x_camv_tbl => lx_camv_tbl
93 );
94
95 okl_debug_pub.logmessage('OKL_CURE_REQ_AMT_PVT: update_cure_request : okl_cure_amounts_pub.update_cure_amounts : '||x_return_status);
96
97 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
98 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
99 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
100 raise OKC_API.G_EXCEPTION_ERROR;
101 End If;
102
103 End If;
104
105 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
106
107 okl_debug_pub.logmessage('OKL_CURE_REQ_AMT_PVT: update_cure_request : END');
108
109 EXCEPTION
110 when OKC_API.G_EXCEPTION_ERROR then
111 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
112 p_api_name => l_api_name,
113 p_pkg_name => g_pkg_name,
114 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
115 x_msg_count => x_msg_count,
116 x_msg_data => x_msg_data,
117 p_api_type => g_api_type);
118
119 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
120 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
121 p_api_name => l_api_name,
122 p_pkg_name => g_pkg_name,
123 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
124 x_msg_count => x_msg_count,
125 x_msg_data => x_msg_data,
126 p_api_type => g_api_type);
127
128 when OTHERS then
129 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
130 p_api_name => l_api_name,
131 p_pkg_name => g_pkg_name,
132 p_exc_name => 'OTHERS',
133 x_msg_count => x_msg_count,
134 x_msg_data => x_msg_data,
135 p_api_type => g_api_type);
136
137 END;
138
139 PROCEDURE update_cure_request(
140 p_api_version IN NUMBER,
141 p_init_msg_list IN VARCHAR2 ,
142 x_return_status OUT NOCOPY VARCHAR2,
143 x_msg_count OUT NOCOPY NUMBER,
144 x_msg_data OUT NOCOPY VARCHAR2,
145 p_vendor_id IN NUMBER,
146 p_cure_report_id IN NUMBER) IS
147
148 l_api_name VARCHAR2(30) := 'update_cure_request';
149 l_api_version CONSTANT NUMBER := 1.0;
150 i NUMBER := 0;
151 l_cure_amt_id NUMBER;
152 l_obj_vers_no NUMBER;
153
154 lp_crtv_rec okl_cure_reports_pub.crtv_rec_type;
155 lx_crtv_rec okl_cure_reports_pub.crtv_rec_type;
156
157 Cursor upd_cure_amts_csr is
158 select cure_amount_id
159 from okl_cure_amounts ca, okl_cure_reports cr
160 where ca.show_on_request = 'Y'
161 and ca.status = 'CURESINPROGRESS'
162 and cr.vendor_id = p_vendor_id
163 and ca.selected_on_request = 'Y'
164 and cr.cure_report_id = ca.crt_id;
165
166 p_cure_req_tbl cure_req_tbl_type;
167
168 Cursor get_obj_vers_no is
169 select object_version_number
170 from okl_cure_reports
171 where cure_report_id = p_cure_report_id;
172
173 --dkagrawa added following cursor for OA Migration
174 CURSOR c_get_contract_count(c_cure_report_id IN NUMBER) IS
175 SELECT COUNT(chr_id)
176 FROM okl_cure_amounts
177 WHERE crt_id = c_cure_report_id;
178
179 l_count NUMBER;
180
181 BEGIN
182
183 okl_debug_pub.logmessage('OKL_CURE_REQ_AMT_PVT: update_cure_request : START');
184 okl_debug_pub.logmessage('OKL_CURE_REQ_AMT_PVT: update_cure_request : p_vendor_id : '|| p_vendor_id);
185 okl_debug_pub.logmessage('OKL_CURE_REQ_AMT_PVT: update_cure_request : p_cure_report_id : '|| p_cure_report_id);
186
187 x_return_status := OKC_API.START_ACTIVITY(
188 p_api_name => l_api_name,
189 p_pkg_name => g_pkg_name,
190 p_init_msg_list => p_init_msg_list,
191 l_api_version => l_api_version,
192 p_api_version => p_api_version,
193 p_api_type => g_api_type,
194 x_return_status => x_return_status);
195
196 -- check if activity started successfully
197 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
198 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
199 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
200 raise OKC_API.G_EXCEPTION_ERROR;
201 End If;
202 --dkagrawa added the following start
203 OPEN c_get_contract_count(p_cure_report_id);
204 FETCH c_get_contract_count INTO l_count;
205 CLOSE c_get_contract_count;
206 IF (l_count = 0) THEN
207 x_return_status := OKC_API.G_RET_STS_ERROR;
208 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
209 p_msg_name => 'OKL_CURE_REQ_CONTRACTS');
210 RAISE OKC_API.G_EXCEPTION_ERROR;
211 END IF;
212 --dkagrawa end
213 /*
214 lp_crtv_rec.cure_report_id := p_cure_report_id;
215 lp_crtv_rec.approval_status := 'SUBMITTED';
216
217 l_obj_vers_no := null;
218 open get_obj_vers_no;
219 fetch get_obj_vers_no into l_obj_vers_no;
220 close get_obj_vers_no;
221
222 lp_crtv_rec.object_version_number := l_obj_vers_no;
223
224 okl_cure_reports_pub.update_cure_reports(
225 p_api_version => p_api_version,
226 p_init_msg_list => p_init_msg_list,
227 x_return_status => x_return_status,
228 x_msg_count => x_msg_count,
229 x_msg_data => x_msg_data,
230 p_crtv_rec => lp_crtv_rec,
231 x_crtv_rec => lx_crtv_rec
232 );
233 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
234 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
235 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
236 raise OKC_API.G_EXCEPTION_ERROR;
237 End If;
238 */
239 okl_cure_wf.approve_cure_reports
240 ( p_api_version => p_api_version
241 ,p_init_msg_list => OKC_API.G_TRUE
242 ,p_commit => OKC_API.G_FALSE
243 ,p_report_id => p_cure_report_id
244 ,x_return_status => x_return_status
245 ,x_msg_count => x_msg_count
246 ,x_msg_data => x_msg_data );
247
248 okl_debug_pub.logmessage('OKL_CURE_REQ_AMT_PVT: update_cure_request : okl_cure_wf.approve_cure_reports : '|| x_return_status);
249
250 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
251 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
252 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
253 raise OKC_API.G_EXCEPTION_ERROR;
254 End If;
255
256 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
257
258 okl_debug_pub.logmessage('OKL_CURE_REQ_AMT_PVT: update_cure_request : END');
259
260 EXCEPTION
261 when OKC_API.G_EXCEPTION_ERROR then
262 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
263 p_api_name => l_api_name,
264 p_pkg_name => g_pkg_name,
265 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
266 x_msg_count => x_msg_count,
267 x_msg_data => x_msg_data,
268 p_api_type => g_api_type);
269
270 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
271 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
272 p_api_name => l_api_name,
273 p_pkg_name => g_pkg_name,
274 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
275 x_msg_count => x_msg_count,
276 x_msg_data => x_msg_data,
277 p_api_type => g_api_type);
278
279 when OTHERS then
280 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
281 p_api_name => l_api_name,
282 p_pkg_name => g_pkg_name,
283 p_exc_name => 'OTHERS',
284 x_msg_count => x_msg_count,
285 x_msg_data => x_msg_data,
286 p_api_type => g_api_type);
287
288 END;
289
290 END OKL_CURE_REQ_AMT_PVT;